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