Audit Trail Issues For GMD_RECIPE_VALIDITY_RULES (Doc ID 1558221.1)

Symptoms

On : 12.1.3 version, Other Issues

When attempting to Run concurrent program AuditTrail Update Tables
the following error occurs.

ERROR
-----------------------
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

FNDATUPD module: AuditTrail Update Tables
+---------------------------------------------------------------------------+

Current system time is 06-MAY-2013 16:59:13

+---------------------------------------------------------------------------+

----->Expand Rows
commit

DELETE FROM FND_AUDIT_TABLES WHERE STATE = 'X'

DELETE FROM FND_AUDIT_COLUMNS C WHERE C.STATE = 'P' AND NOT EXISTS (SELECT NULL FROM FND_AUDIT_TABLES T WHERE T.TABLE_APP_ID = C.TABLE_APP_ID AND T.TABLE_ID = C.TABLE_ID AND T.STATE !='X')

----->Update Pending Columns
commit

----->Drop Triggers
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AI
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AI
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AI' does not exist
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_AIP
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_AIP
ORA-04043: object GMD_RECIPE_VALIDITY_RULE_AIP does not exist
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AD
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AD
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AD' does not exist
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_ADP
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_ADP
ORA-04043: object GMD_RECIPE_VALIDITY_RULE_ADP does not exist
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AU
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AU
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AU' does not exist
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_AUP
DROP PROCEDURE GMD_RECIPE_VALIDITY_RULE_AUP
ORA-04043: object GMD_RECIPE_VALIDITY_RULE_AUP does not exist
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AC
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AC
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AC' does not exist
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AH
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AH
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AH' does not exist
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AT
DROP TRIGGER GMD_RECIPE_VALIDITY_RULE_AT
ORA-04080: trigger 'GMD_RECIPE_VALIDITY_RULE_AT' does not exist
----->Create Standard Triggers
CREATE OR REPLACE PROCEDURE
GMD_RECIPE_VALIDITY_RULE_AIP(A0 IN NUMBER,JB IN DATE,JC IN NUMBER,JD IN NUMBER,JF IN NUMBER,JG IN VARCHAR2,
JH IN VARCHAR2,JJ IN DATE,JK IN NUMBER,JL IN VARCHAR2,JM IN NUMBER,
JN IN NUMBER,E0 IN NUMBER,RB IN DATE,RC IN NUMBER,RD IN NUMBER,RF IN NUMBER,RG IN VARCHAR2,
RH IN VARCHAR2,RJ IN DATE,RK IN NUMBER,RL IN VARCHAR2,RM IN NUMBER,
RN IN NUMBER )
AS
ROWKEY number;
NXT number;
CMT number;
NUSER varchar2(100);
nls_date_fmt VARCHAR2(40);
BEGIN
select value into nls_date_fmt from v$NLS_PARAMETERS where parameter='NLS_DATE_FORMAT';
execute IMMEDIATE 'alter session set nls_date_format="MM/DD/YYYY HH24:MI:SS"';
NXT:=FND_AUDIT_SEQ_PKG.NXT;
CMT:=FND_AUDIT_SEQ_PKG.CMT;
ROWKEY:=(TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))*100000 +MOD(NXT,100000)) * 100000 + USERENV('SESSIONID');
NUSER:=FND_AUDIT_SEQ_PKG.USER_NAME;
INSERT INTO GMD_RECIPE_VALIDITY_RULE_A
 VALUES(SYSDATE,'I',NUSER,NULL,USERENV('SESSIONID'),NXT,CMT,ROWKEY,E0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
);
execute IMMEDIATE 'alter session set nls_date_format="'||nls_date_fmt||'"';

END GMD_RECIPE_VALIDITY_RULE_AIP;

Fatal error in fdasql, quitting...
ORA-24344: success with compilation error
Fatal error in fdactr, quitting...
Fatal error in fdactr, quitting...
commit

commit

----->Delete Invalid Columns
commit

update fnd_audit_groups g set g.state='E' where g.state = 'R' and not exists (select NULL from fnd_audit_tables t where t.audit_group_id = g.audit_group_id and t.audit_group_app_id = g.application_id and t.state != 'E')

commit

----->Update Triggers
commit





STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. - Have auditing enabled and working in r11
- Upgrade to r12
- Run concurrent program AuditTrail Update Tables (program completes with Normal outcome but log file contains many errors and auditing does not work for gmd_recipe_validity_rules)

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.