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