在创建分类账(OracleGeneralLedger会计科目设置程序)出错解决办法
在创建分类账(oracle general ledger 会计科目设置程序)出错。
**starts**12-10-2016 02:42:00
**sql error and free**12-10-2016 02:42:01
fdpstp 中存在 oracle 错误 12018
原因:由于 ora-12018: 在创建 "apps"."gl_access_set_ledgers" 的代码时出现以下错误
ora-00600: 内部错误代码, 参数: [kkzdgdefq], [1], [], [], [], [], [], [], [], [], [], []
ora-06512: 在 "sys.db12-10-2016 02:42:01 ora-06512: 在 "sys.db12-10-2016 02:42:01
解决步骤:
please reproduce the issue in your test instance, and implement following steps in test instance firstly:
1. connect as apps user to database
2. drop the existing materialized view
drop materialized view gl_access_set_ledgers;
3. check whether the materialized view is dropped properly or not:
select * from all_objects
where object_name like 'gl_access_set_ledgers';
-- this should give 0 records.
4. change directory to $gl_top/patch/115/sql
5. connect to database as apps user and execute the following at the sql prompt
@glvaslmv.sql
该部分因为glvaslmv.sql文件不能正确建立物化视图(文件不完整)
手工建立物化视图
首先建立预建表
-- create table
create table gl_access_set_ledgers
(
access_set_id number(15),
ledger_id number,
access_privilege_code varchar2(1),
last_update_date date,
last_updated_by number,
creation_date date,
created_by number,
last_update_login number,
start_date date,
end_date date
)
tablespace apps_ts_summary
pctfree 10
initrans 10
maxtrans 255
storage
(
initial 16k
next 128k
minextents 1
maxextents unlimited
);
-- add comments to the table
comment on table gl_access_set_ledgers
is 'snapshot table for snapshot apps.gl_access_set_ledgers';
-- create/recreate indexes
create unique index gl_access_set_ledgers_u1 on gl_access_set_ledgers (access_set_id, ledger_id, access_privilege_code)
tablespace apps_ts_summary
pctfree 10
initrans 11
maxtrans 255
storage
(
initial 16k
next 128k
minextents 1
maxextents unlimited
);
创建物化视图
create materialized view gl_access_set_ledgers
on prebuilt table
refresh force on demand
as
(select "a8"."access_set_id" "access_set_id",decode("a7"."ledger_id",null,"a8"."ledger_id","a7"."ledger_id") "ledger_id",decode(min(decode("a8"."all_segment_value_flag",'y',decode("a8"."access_privilege_code",'b',1,'r',3),decode("a8"."access_privilege_code",'b',2,'r',3))),1,'f',2,'b',3,'r') "access_privilege_code",max("a8"."last_update_date") "last_update_date",0 "last_updated_by",max("a8"."creation_date") "creation_date",0 "created_by",0 "last_update_login",to_date(null) "start_date",to_date(null) "end_date" from "gl"."gl_access_sets" "a9","gl"."gl_access_set_norm_assign" "a8","gl"."gl_ledger_set_assignments" "a7" where "a9"."automatically_created_flag"='n' and "a8"."access_set_id"="a9"."access_set_id" and nvl("a8"."status_code",'x')<>'i' and "a7"."ledger_set_id"(+)="a8"."ledger_id" group by "a8"."access_set_id",decode("a7"."ledger_id",null,"a8"."ledger_id","a7"."ledger_id")) union all (select "a5"."access_set_id" "access_set_id",decode("a6"."object_type_code",'s',"a3"."ledger_id","a4"."ledger_id") "ledger_id",decode(max(decode("a6"."object_type_code",'s',1,decode("a3"."ledger_id",null,decode("a4"."all_segment_value_flag",'y',decode("a4"."access_privilege_code",'r',3,'b',1),decode("a4"."access_privilege_code",'r',3,'b',2)),decode("a2"."all_segment_value_flag",'y',decode("a2"."access_privilege_code",'r',3,'b',1),decode("a2"."access_privilege_code",'r',3,'b',2))))),1,'f',2,'b',3,'r') "access_privilege_code",max("a2"."last_update_date") "last_update_date",0 "last_updated_by",max("a2"."creation_date") "creation_date",0 "created_by",0 "last_update_login",to_date(null) "start_date",to_date(null) "end_date" from "gl"."gl_ledgers" "a6","gl"."gl_access_sets" "a5","gl"."gl_access_set_norm_assign" "a4","gl"."gl_ledger_set_assignments" "a3","gl"."gl_access_set_norm_assign" "a2" where "a5"."access_set_id"="a6"."implicit_access_set_id" and "a5"."automatically_created_flag"='y' and "a4"."access_set_id"="a5"."access_set_id" and nvl("a4"."status_code",'x')<>'i' and "a3"."ledger_set_id"(+)="a4"."ledger_id" and nvl("a3"."status_code"(+),'x')<>'i' and "a2"."access_set_id"=decode("a3"."ledger_set_id",null,"a4"."access_set_id","a4"."access_set_id") and "a2"."ledger_id"=decode("a6"."object_type_code",'s',"a4"."ledger_id",nvl("a3"."ledger_id","a4"."ledger_id")) and nvl("a2"."status_code",'x')<>'i' group by "a5"."access_set_id",decode("a6"."object_type_code",'s',"a3"."ledger_id","a4"."ledger_id"))
6. check whether the materialized view is created properly or not:
select * from all_objects
where object_name like 'gl_access_set_ledgers';
-- this should give 2 records.
7. refresh the materialized view by using any of the following steps:
i) execute the statement "execute dbms_mview.refresh('gl_access_set_ledgers')" in sql plus or any other editor;
ii) implicitly submit general ledger accounting setup program, this will internally refresh the materialized view.
8. check whether the materialized view is refreshed or not:
select owner,mview_name,last_refresh_date from all_mviews
where mview_name='gl_access_set_ledgers';
-- the last_refreshed_date should be either current date or should not be blank
9. retest the issue
10. migrate the solution as appropriate to other environments.