Audit Trail Issues For GMD_RECIPE_VALIDITY_RULES
(Doc ID 1558221.1)
Last updated on NOVEMBER 01, 2024
Applies to:
Oracle Process Manufacturing Product Development - Version 12.1.3 and laterInformation in this document applies to any platform.
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
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |