在创建分类账(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.