Thursday, September 3, 2015

AuditTrail on Oracle Application OE_ORDER_LINES_ALL Table to keep the History


1. Logon to the Applications and select the System Administrator responsibility.

2. Make sure APPLSYS has select privileges on SYS.DBA_TABLES.
For example, if you are auditing the OE_ORDER_LINES_ALL table, use the following SQL statement to verify this:
SELECT TABLE_NAME
  FROM SYS.DBA_TABLES
 WHERE TABLE_NAME LIKE 'OE_ORDER_LINES_ALL'

3. Enable the AuditTrail:Activate system profile value as Yes at site level

4. Make sure that Audit on the application is Enabled.
Navigation: System Administrator à Security -> AuditTrail Install
Oracle Username       : ONT
Description                 : Oracle Order Management Account

5. Define Audit Groups:
It is required to group the tables that you need to audit since auditing is enabled on an audit groups basis.
Navigation: System Administrator à Security -> AuditTrail -> Groups
Application Name     :           Order Management
Audit Group               :           XXAA OE_ORDER_LINES_ALL
Group State                :           Enabled
-->Audit Tables:
User Table Name       :           OE_ORDER_LINES_ALL

6.         Add further columns for Audit Trail
By default Oracle will Audit Trail on all columns that are a part of first available Unique Index on OE_ORDER_LINES_ALL. However further columns can be added to the Audit Trail.
Navigation: System Administrator à Security -> AuditTrail -> Tables
User Table Name       :           OE_ORDER_LINES_ALL
Table Name                :           OE_ORDER_LINES_ALL
Application                :           Order Management
Description                 :           OE_ORDER_LINES_ALL stores information for all order lines in Oracle Order Management.
-->Audit Columns:
Column Name          
LINE_ID                     :           Primary key Enabled
HEADER_ID
CREATION_DATE
CREATED_BY
Etc…

7. Run the "AuditTrail Update Tables" concurrent program from the submit request form to enable auditing.
Navigation: System Administrator ->Requests -> Run -> select a single request

8. Once completed the program check the Log file and database objects are created or not
SELECT OBJECT_NAME, OBJECT_TYPE, STATUS
  FROM ALL_OBJECTS
 WHERE OBJECT_NAME LIKE 'OE_ORDER_LINES_ALL_A%'
ORDER BY STATUS,OBJECT_TYPE,OBJECT_NAME

+---------------------------------------------------------------------------+
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 02-SEP-2015 20:11:54
+---------------------------------------------------------------------------+
----->Expand Rows
INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414950, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414997, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 415017, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 415064, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 509232, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 415062, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414951, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414954, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 415068, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414952, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 414953, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 415076, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID, TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID, SCHEMA_ID, LAST_UPDATE_DATE,LAST_UPDATED_BY, LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (  660, 71211, 420810, 'P', -1, 900, SYSDATE,0,0,0,SYSDATE)

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'P'  WHERE T.TABLE_APP_ID =  660   AND T.TABLE_ID =  71211
commit
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')

----->Create New Table
CREATE TABLE OE_ORDER_LINES_ALL_A (AUDIT_TIMESTAMP DATE NOT NULL, AUDIT_TRANSACTION_TYPE VARCHAR(1) NOT NULL, AUDIT_USER_NAME VARCHAR2(100) NOT NULL, AUDIT_TRUE_NULLS VARCHAR2(250), AUDIT_SESSION_ID NUMBER NOT NULL, AUDIT_SEQUENCE_ID NUMBER NOT NULL,AUDIT_COMMIT_ID NUMBER NOT NULL, ROW_KEY NUMBER )
commit

GRANT ALL ON OE_ORDER_LINES_ALL_A to APPS
GRANT ALL ON OE_ORDER_LINES_ALL_A to APPS
commit

CREATE SYNONYM OE_ORDER_LINES_ALL_A for ONT.OE_ORDER_LINES_ALL_A
CREATE SYNONYM OE_ORDER_LINES_ALL_A for ONT.OE_ORDER_LINES_ALL_A
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:16
**Ends**02-SEP-2015 20:13:17
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LINE_ID NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LINE_ID NUMBER )
commit
commit

DELETE FROM FND_AUDIT_COLUMNS C  WHERE C.TABLE_APP_ID= 660 AND C.TABLE_ID =  71211 AND C.COLUMN_ID =  415062 AND C.SCHEMA_ID =  900

INSERT INTO FND_AUDIT_COLUMNS (TABLE_APP_ID,TABLE_ID,COLUMN_ID,STATE,SEQUENCE_ID,SCHEMA_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES (      660,  71211,  415062, 'K',  0,  900, SYSDATE,0,0,0,SYSDATE) 
commit

----->Update Pending Columns
----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:18
**Ends**02-SEP-2015 20:13:18
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (CREATION_DATE DATE)
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (CREATION_DATE DATE)
commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 1 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414950 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:18
**Ends**02-SEP-2015 20:13:18
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (CREATED_BY NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (CREATED_BY NUMBER )
commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 2 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414951 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:18
**Ends**02-SEP-2015 20:13:18
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATE_DATE DATE)
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATE_DATE DATE)
commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 3 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414952 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:18
**Ends**02-SEP-2015 20:13:18
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATED_BY NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATED_BY NUMBER )
commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 4 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414953 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:19
**Ends**02-SEP-2015 20:13:19
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATE_LOGIN NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (LAST_UPDATE_LOGIN NUMBER )

commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 5 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414954 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:19
**Ends**02-SEP-2015 20:13:19
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (SCHEDULE_ARRIVAL_DATE DATE)
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (SCHEDULE_ARRIVAL_DATE DATE)
commit
commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 6 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 414997 AND STATE = 'P'
commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:19
**Ends**02-SEP-2015 20:13:19
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ATTRIBUTE5 VARCHAR2(240))
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ATTRIBUTE5 VARCHAR2(240))

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 7 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 415017 AND STATE = 'P'

commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:19
**Ends**02-SEP-2015 20:13:19
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (HEADER_ID NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (HEADER_ID NUMBER )

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 8 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 415064 AND STATE = 'P'

commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:19
**Ends**02-SEP-2015 20:13:20
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (REQUEST_DATE DATE)
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (REQUEST_DATE DATE)

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 9 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 415068 AND STATE = 'P'

commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:20
**Ends**02-SEP-2015 20:13:20
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ORDERED_QUANTITY NUMBER )
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ORDERED_QUANTITY NUMBER )

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 10 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 415076 AND STATE = 'P'

commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:20
**Ends**02-SEP-2015 20:13:20
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (SCHEDULE_SHIP_DATE DATE)
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (SCHEDULE_SHIP_DATE DATE)

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 11 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 420810 AND STATE = 'P'

commit

----->Adding ROW_KEY
**Starts**02-SEP-2015 20:13:20
**Ends**02-SEP-2015 20:13:20
----->Alter Shadow Table
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ORDERED_ITEM VARCHAR2(2000))
ALTER TABLE OE_ORDER_LINES_ALL_A ADD (ORDERED_ITEM VARCHAR2(2000))

commit

commit

UPDATE FND_AUDIT_COLUMNS SET STATE = 'N' , SEQUENCE_ID = 12 WHERE TABLE_APP_ID = 660 AND SCHEMA_ID = 900 AND TABLE_ID = 71211 AND COLUMN_ID = 509232 AND STATE = 'P'

commit

commit

----->Drop Triggers
DROP TRIGGER OE_ORDER_LINES_ALL_AI
DROP TRIGGER OE_ORDER_LINES_ALL_AI
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AI' does not exist
DROP PROCEDURE OE_ORDER_LINES_ALL_AIP
DROP PROCEDURE OE_ORDER_LINES_ALL_AIP
ORA-04043: object OE_ORDER_LINES_ALL_AIP does not exist
DROP TRIGGER OE_ORDER_LINES_ALL_AD
DROP TRIGGER OE_ORDER_LINES_ALL_AD
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AD' does not exist
DROP PROCEDURE OE_ORDER_LINES_ALL_ADP
DROP PROCEDURE OE_ORDER_LINES_ALL_ADP
ORA-04043: object OE_ORDER_LINES_ALL_ADP does not exist
DROP TRIGGER OE_ORDER_LINES_ALL_AU
DROP TRIGGER OE_ORDER_LINES_ALL_AU
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AU' does not exist
DROP PROCEDURE OE_ORDER_LINES_ALL_AUP
DROP PROCEDURE OE_ORDER_LINES_ALL_AUP
ORA-04043: object OE_ORDER_LINES_ALL_AUP does not exist
DROP TRIGGER OE_ORDER_LINES_ALL_AC
DROP TRIGGER OE_ORDER_LINES_ALL_AC
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AC' does not exist
DROP TRIGGER OE_ORDER_LINES_ALL_AH
DROP TRIGGER OE_ORDER_LINES_ALL_AH
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AH' does not exist
DROP TRIGGER OE_ORDER_LINES_ALL_AT
DROP TRIGGER OE_ORDER_LINES_ALL_AT
ORA-04080: trigger 'OE_ORDER_LINES_ALL_AT' does not exist
----->Create Standard Triggers
CREATE OR REPLACE PROCEDURE
OE_ORDER_LINES_ALL_AIP(A0 IN NUMBER,JB IN DATE,JC IN NUMBER,JD IN DATE,JF IN NUMBER,JG IN NUMBER,
JH IN DATE,JJ IN VARCHAR2,JK IN NUMBER,JL IN DATE,JM IN NUMBER,
JN IN DATE,JP IN VARCHAR2,E0 IN NUMBER,RB IN DATE,RC IN NUMBER,RD IN DATE,RF IN NUMBER,RG IN NUMBER,
RH IN DATE,RJ IN VARCHAR2,RK IN NUMBER,RL IN DATE,RM IN NUMBER,
RN IN DATE,RP IN VARCHAR2 )
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 OE_ORDER_LINES_ALL_A
 VALUES(SYSDATE,'I',NUSER,NULL,USERENV('SESSIONID'),NXT,CMT,ROWKEY,E0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL);
execute IMMEDIATE 'alter session set nls_date_format="'||nls_date_fmt||'"';

END OE_ORDER_LINES_ALL_AIP;

commit

CREATE OR REPLACE PROCEDURE
OE_ORDER_LINES_ALL_AUP (A0 IN NUMBER,JB IN DATE,JC IN NUMBER,JD IN DATE,JF IN NUMBER,JG IN NUMBER,
JH IN DATE,JJ IN VARCHAR2,JK IN NUMBER,JL IN DATE,JM IN NUMBER,
JN IN DATE,JP IN VARCHAR2,E0 IN NUMBER,RB IN DATE,RC IN NUMBER,RD IN DATE,RF IN NUMBER,RG IN NUMBER,
RH IN DATE,RJ IN VARCHAR2,RK IN NUMBER,RL IN DATE,RM IN NUMBER,
RN IN DATE,RP IN VARCHAR2)
AS
NXT NUMBER;
CMT NUMBER;
NUSER varchar2(100);
newtransaction_TYPE VARCHAR2(1);
newTRUE_NULLS VARCHAR2(250);
tmpPRIMCHANGE NUMBER;
nls_date_fmt VARCHAR2(40);
I0 NUMBER;
YB DATE;
YC NUMBER;
YD DATE;
YF NUMBER;
YG NUMBER;
YH DATE;
YJ VARCHAR2(240);
YK NUMBER;
YL DATE;
YM NUMBER;
YN DATE;
YP VARCHAR2(2000);
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"';

NUSER:=FND_AUDIT_SEQ_PKG.USER_NAME;
SELECT 0+decode(E0,A0,0,1) into tmpPRIMCHANGE FROM SYS.DUAL;
IF tmpPRIMCHANGE>=1 THEN
SELECT decode(A0,NULL,'Y','N')
||decode(JB,NULL,'Y','N')
||decode(JC,NULL,'Y','N')
||decode(JD,NULL,'Y','N')
||decode(JF,NULL,'Y','N')
||decode(JG,NULL,'Y','N')
||decode(JH,NULL,'Y','N')
||decode(JJ,NULL,'Y','N')
||decode(JK,NULL,'Y','N')
||decode(JL,NULL,'Y','N')
||decode(JM,NULL,'Y','N')
||decode(JN,NULL,'Y','N')
||decode(JP,NULL,'Y','N') INTO newTRUE_NULLS FROM SYS.DUAL;
IF(newTRUE_NULLS='NNNNNNNNNNNNN')THEN
 newTRUE_NULLS:=NULL;END IF;
NXT:=FND_AUDIT_SEQ_PKG.NXT;
CMT:=FND_AUDIT_SEQ_PKG.CMT;
INSERT INTO OE_ORDER_LINES_ALL_A
VALUES(SYSDATE,'D',NUSER,newTRUE_NULLS,
USERENV('SESSIONID'),NXT,CMT,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))*100000+MOD(NXT,100000)) * 100000 + USERENV('SESSIONID'),
A0,JB,JC,JD,JF,JG,
JH,JJ,JK,JL,JM,
JN,JP);
NXT:=FND_AUDIT_SEQ_PKG.NXT;
CMT:=FND_AUDIT_SEQ_PKG.CMT;
NUSER:=FND_AUDIT_SEQ_PKG.USER_NAME;
INSERT INTO OE_ORDER_LINES_ALL_A
VALUES(SYSDATE,'I',NUSER,NULL,USERENV('SESSIONID'),NXT,CMT,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))*100000+MOD(NXT,100000)) * 100000 + USERENV('SESSIONID'),
E0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,NULL);
ELSE
SELECT DECODE(JB,RB,NULL,JB)INTO YB FROM SYS.DUAL;
SELECT DECODE(JC,RC,NULL,JC)INTO YC FROM SYS.DUAL;
SELECT DECODE(JD,RD,NULL,JD)INTO YD FROM SYS.DUAL;
SELECT DECODE(JF,RF,NULL,JF)INTO YF FROM SYS.DUAL;
SELECT DECODE(JG,RG,NULL,JG)INTO YG FROM SYS.DUAL;
SELECT DECODE(JH,RH,NULL,JH)INTO YH FROM SYS.DUAL;
SELECT DECODE(JJ,RJ,NULL,JJ)INTO YJ FROM SYS.DUAL;
SELECT DECODE(JK,RK,NULL,JK)INTO YK FROM SYS.DUAL;
SELECT DECODE(JL,RL,NULL,JL)INTO YL FROM SYS.DUAL;
SELECT DECODE(JM,RM,NULL,JM)INTO YM FROM SYS.DUAL;
SELECT DECODE(JN,RN,NULL,JN)INTO YN FROM SYS.DUAL;
SELECT DECODE(JP,RP,NULL,JP)INTO YP FROM SYS.DUAL;
SELECT decode(A0,NULL,decode(E0,NULL,'N','Y'),'N')
||decode(JB,NULL,decode(RB,NULL,'N','Y'),'N')
||decode(JC,NULL,decode(RC,NULL,'N','Y'),'N')
||decode(JD,NULL,decode(RD,NULL,'N','Y'),'N')
||decode(JF,NULL,decode(RF,NULL,'N','Y'),'N')
||decode(JG,NULL,decode(RG,NULL,'N','Y'),'N')
||decode(JH,NULL,decode(RH,NULL,'N','Y'),'N')
||decode(JJ,NULL,decode(RJ,NULL,'N','Y'),'N')
||decode(JK,NULL,decode(RK,NULL,'N','Y'),'N')
||decode(JL,NULL,decode(RL,NULL,'N','Y'),'N')
||decode(JM,NULL,decode(RM,NULL,'N','Y'),'N')
||decode(JN,NULL,decode(RN,NULL,'N','Y'),'N')
||decode(JP,NULL,decode(RP,NULL,'N','Y'),'N') INTO newTRUE_NULLS FROM SYS.DUAL;
IF(newTRUE_NULLS='NNNNNNNNNNNNN')THEN newTRUE_NULLS:=NULL;END IF;
IF(newTRUE_NULLS is not NULL)OR
YB IS NOT NULL OR
YC IS NOT NULL OR
YD IS NOT NULL OR
YF IS NOT NULL OR
YG IS NOT NULL OR
YH IS NOT NULL OR
YJ IS NOT NULL OR
YK IS NOT NULL OR
YL IS NOT NULL OR
YM IS NOT NULL OR
YN IS NOT NULL OR
YP IS NOT NULL THEN
NXT:=FND_AUDIT_SEQ_PKG.NXT;
CMT:=FND_AUDIT_SEQ_PKG.CMT;
INSERT INTO OE_ORDER_LINES_ALL_A
VALUES(SYSDATE,'U',NUSER,newTRUE_NULLS,USERENV('SESSIONID'),NXT,CMT,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))*100000+MOD(NXT,100000)) * 100000 + USERENV('SESSIONID'),
E0,YB,YC,YD,YF,YG,
YH,YJ,YK,YL,YM,
YN,YP);
END IF;
END IF;

execute IMMEDIATE 'alter session set nls_date_format="'||nls_date_fmt||'"';
END OE_ORDER_LINES_ALL_AUP;

commit

CREATE OR REPLACE PROCEDURE
OE_ORDER_LINES_ALL_ADP(A0 IN NUMBER,JB IN DATE,JC IN NUMBER,JD IN DATE,JF IN NUMBER,JG IN NUMBER,
JH IN DATE,JJ IN VARCHAR2,JK IN NUMBER,JL IN DATE,JM IN NUMBER,
JN IN DATE,JP IN VARCHAR2,E0 IN NUMBER,RB IN DATE,RC IN NUMBER,RD IN DATE,RF IN NUMBER,RG IN NUMBER,
RH IN DATE,RJ IN VARCHAR2,RK IN NUMBER,RL IN DATE,RM IN NUMBER,
RN IN DATE,RP IN VARCHAR2)
AS
NXT NUMBER;
CMT NUMBER;
NUSER varchar2(100);
newTRUE_NULLS VARCHAR2(250);
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;
NUSER:=FND_AUDIT_SEQ_PKG.USER_NAME;
SELECT decode(A0,NULL,'Y','N')
||decode(JB,NULL,'Y','N')
||decode(JC,NULL,'Y','N')
||decode(JD,NULL,'Y','N')
||decode(JF,NULL,'Y','N')
||decode(JG,NULL,'Y','N')
||decode(JH,NULL,'Y','N')
||decode(JJ,NULL,'Y','N')
||decode(JK,NULL,'Y','N')
||decode(JL,NULL,'Y','N')
||decode(JM,NULL,'Y','N')
||decode(JN,NULL,'Y','N')
||decode(JP,NULL,'Y','N') INTO newTRUE_NULLS FROM SYS.DUAL;
IF(newTRUE_NULLS='NNNNNNNNNNNNN') THEN
newTRUE_NULLS:= NULL;
END IF;
INSERT INTO OE_ORDER_LINES_ALL_A
VALUES(SYSDATE,'D',NUSER,newTRUE_NULLS,
USERENV('SESSIONID'),NXT,CMT,(TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'))*100000+MOD(NXT,100000)) * 100000 + USERENV('SESSIONID'),
A0,JB,JC,JD,JF,JG,
JH,JJ,JK,JL,JM,
JN,JP);
execute IMMEDIATE 'alter session set nls_date_format="'||nls_date_fmt||'"';
END OE_ORDER_LINES_ALL_ADP;

commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AH BEFORE INSERT ON OE_ORDER_LINES_ALL BEGIN IF fnd_global.audit_active THEN fnd_audit_pkg.audit_on := TRUE;ELSE fnd_audit_pkg.audit_on := FALSE;END IF;END;
commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AI AFTER INSERT ON OE_ORDER_LINES_ALL FOR EACH ROW BEGIN IF fnd_audit_pkg.audit_on THEN OE_ORDER_LINES_ALL_AIP(:old.LINE_ID,:old.CREATION_DATE,:old.CREATED_BY,:old.LAST_UPDATE_DATE,:old.LAST_UPDATED_BY,:old.LAST_UPDATE_LOGIN,:old.SCHEDULE_ARRIVAL_DATE,:old.ATTRIBUTE5,:old.HEADER_ID,:old.REQUEST_DATE,:old.ORDERED_QUANTITY,:old.SCHEDULE_SHIP_DATE,:old.ORDERED_ITEM,:new.LINE_ID,:new.CREATION_DATE,:new.CREATED_BY,:new.LAST_UPDATE_DATE,:new.LAST_UPDATED_BY,:new.LAST_UPDATE_LOGIN,:new.SCHEDULE_ARRIVAL_DATE,:new.ATTRIBUTE5,:new.HEADER_ID,:new.REQUEST_DATE,:new.ORDERED_QUANTITY,:new.SCHEDULE_SHIP_DATE,:new.ORDERED_ITEM);END IF;END;
commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AT BEFORE UPDATE OF LINE_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,SCHEDULE_ARRIVAL_DATE,ATTRIBUTE5,HEADER_ID,REQUEST_DATE,ORDERED_QUANTITY,SCHEDULE_SHIP_DATE,ORDERED_ITEM ON OE_ORDER_LINES_ALL BEGIN IF fnd_global.audit_active THEN fnd_audit_pkg.audit_on := TRUE;ELSE fnd_audit_pkg.audit_on := FALSE;END IF;END;
commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AU AFTER UPDATE OF LINE_ID,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,SCHEDULE_ARRIVAL_DATE,ATTRIBUTE5,HEADER_ID,REQUEST_DATE,ORDERED_QUANTITY,SCHEDULE_SHIP_DATE,ORDERED_ITEM ON OE_ORDER_LINES_ALL FOR EACH ROW BEGIN IF fnd_audit_pkg.audit_on THEN OE_ORDER_LINES_ALL_AUP(:old.LINE_ID,:old.CREATION_DATE,:old.CREATED_BY,:old.LAST_UPDATE_DATE,:old.LAST_UPDATED_BY,:old.LAST_UPDATE_LOGIN,:old.SCHEDULE_ARRIVAL_DATE,:old.ATTRIBUTE5,:old.HEADER_ID,:old.REQUEST_DATE,:old.ORDERED_QUANTITY,:old.SCHEDULE_SHIP_DATE,:old.ORDERED_ITEM,:new.LINE_ID,:new.CREATION_DATE,:new.CREATED_BY,:new.LAST_UPDATE_DATE,:new.LAST_UPDATED_BY,:new.LAST_UPDATE_LOGIN,:new.SCHEDULE_ARRIVAL_DATE,:new.ATTRIBUTE5,:new.HEADER_ID,:new.REQUEST_DATE,:new.ORDERED_QUANTITY,:new.SCHEDULE_SHIP_DATE,:new.ORDERED_ITEM);END IF;END;
commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AC BEFORE DELETE ON OE_ORDER_LINES_ALL BEGIN IF fnd_global.audit_active THEN fnd_audit_pkg.audit_on := TRUE;ELSE fnd_audit_pkg.audit_on := FALSE;END IF;END;
commit

CREATE OR REPLACE TRIGGER OE_ORDER_LINES_ALL_AD AFTER DELETE ON OE_ORDER_LINES_ALL FOR EACH ROW BEGIN IF fnd_audit_pkg.audit_on THEN OE_ORDER_LINES_ALL_ADP(:old.LINE_ID,:old.CREATION_DATE,:old.CREATED_BY,:old.LAST_UPDATE_DATE,:old.LAST_UPDATED_BY,:old.LAST_UPDATE_LOGIN,:old.SCHEDULE_ARRIVAL_DATE,:old.ATTRIBUTE5,:old.HEADER_ID,:old.REQUEST_DATE,:old.ORDERED_QUANTITY,:old.SCHEDULE_SHIP_DATE,:old.ORDERED_ITEM,:new.LINE_ID,:new.CREATION_DATE,:new.CREATED_BY,:new.LAST_UPDATE_DATE,:new.LAST_UPDATED_BY,:new.LAST_UPDATE_LOGIN,:new.SCHEDULE_ARRIVAL_DATE,:new.ATTRIBUTE5,:new.HEADER_ID,:new.REQUEST_DATE,:new.ORDERED_QUANTITY,:new.SCHEDULE_SHIP_DATE,:new.ORDERED_ITEM);END IF;END;
commit

----->Create Views
VIEW OE_ORDER_LINES_ALL_AV1
commit

VIEW OE_ORDER_LINES_ALL_AV2
commit

VIEW OE_ORDER_LINES_ALL_AV3
commit

VIEW OE_ORDER_LINES_ALL_AV4
commit

VIEW OE_ORDER_LINES_ALL_AV5
commit

VIEW OE_ORDER_LINES_ALL_AV6
commit

VIEW OE_ORDER_LINES_ALL_AV7
commit

VIEW OE_ORDER_LINES_ALL_AV8
commit

VIEW OE_ORDER_LINES_ALL_AV9
commit

VIEW OE_ORDER_LINES_ALL_AV10
commit

VIEW OE_ORDER_LINES_ALL_AV11
commit

VIEW OE_ORDER_LINES_ALL_AV12
commit

VIEW OE_ORDER_LINES_ALL_AV13
commit

VIEW OE_ORDER_LINES_ALL_AC1
-->LINE_ID
-->HEADER_ID
-->CREATION_DATE
-->CREATED_BY
-->LAST_UPDATE_DATE
-->LAST_UPDATED_BY
-->LAST_UPDATE_LOGIN
-->REQUEST_DATE
-->ORDERED_ITEM
-->ORDERED_QUANTITY
-->SCHEDULE_SHIP_DATE
-->SCHEDULE_ARRIVAL_DATE
-->ATTRIBUTE1
-->ATTRIBUTE2
-->ATTRIBUTE3
-->ATTRIBUTE4
-->ATTRIBUTE5
commit

UPDATE FND_AUDIT_TABLES T  SET T.STATE = 'E'  WHERE T.TABLE_ID =  71211 AND T.TABLE_APP_ID =  660 AND NOT EXISTS (  SELECT NULL FROM FND_AUDIT_COLUMNS C  WHERE C.TABLE_APP_ID = T.TABLE_APP_ID  AND C.TABLE_ID = T.TABLE_ID  AND C.STATE = 'P'  AND C.SCHEMA_ID != -1)
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
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
Output file size:
0
Output is not being printed because:
The print option has been disabled for this report.
+------------- 1) PRINT   -------------+
+--------------------------------------+
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 02-SEP-2015 20:13:30
+---------------------------------------------------------------------------+

9. Run Concurrent program 'AuditTrail Report for Audit Group Validation' with parameter as your Audit Group. (As defined in step 5). See the log of this concurrent request.
Navigation: System Administrator ->Requests -> Run -> select a single request
Select the group : XXAA OE_ORDER_LINES_ALL

10. Once completed the Program verify the log file
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0
Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.
FNDATRPT module: AuditTrail Report for Audit Group Validation
+---------------------------------------------------------------------------+
Current system time is 02-SEP-2015 20:27:59
+---------------------------------------------------------------------------+
**Starts**02-SEP-2015 20:28:00
**Ends**02-SEP-2015 20:28:06
+---------------------------------------------------------------------------+
Start of log messages from FND_FILE
+---------------------------------------------------------------------------+
Profile Option AuditTrail:Activate is Y
------------------------------------------------------------------------
Audit Group Name is XXAA OE_ORDER_LINES_ALL Audit Group
- Audit Group Table Name           :  OE_ORDER_LINES_ALL
-  Audit Group Oracle Username     :  ONT is Audit Enabled
-  Audit Group Table               :  OE_ORDER_LINES_ALL is VALID
-  Audit Group Table Synonym       :  OE_ORDER_LINES_ALL is VALID
-  Audit Group Shadow Table        :  OE_ORDER_LINES_ALL_A is VALID
-  Audit Group Shadow Table Synonym:  OE_ORDER_LINES_ALL_A_A is VALID
-  Audit Group Trigger             :  OE_ORDER_LINES_ALL_AI is VALID
-  Audit Group Trigger             :  OE_ORDER_LINES_ALL_AU is VALID
-  Audit Group Trigger             :  OE_ORDER_LINES_ALL_AD is VALID
-  Audit Group Procedure           :  OE_ORDER_LINES_ALL_AIP is VALID
-  Audit Group Procedure           :  OE_ORDER_LINES_ALL_AUP is VALID
-  Audit Group Procedure           :  OE_ORDER_LINES_ALL_ADP is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV1 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV10 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV11 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV12 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV13 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV2 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV3 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV4 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV5 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV6 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV7 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV8 is VALID
-  Audit Group View                :  OE_ORDER_LINES_ALL_AV9 is VALID
-  Audit Group View AV%            :  13 out of 13 View(s) are present in the database.
-  Audit Group View                :  OE_ORDER_LINES_ALL_AC1 is VALID
-  Audit Group Table OE_ORDER_LINES_ALL is not missing any columns present in shadow table OE_ORDER_LINES_ALL_A
-  Audit Group Table OE_ORDER_LINES_ALL is not present in any other Audit Groups
+---------------------------------------------------------------------------+
End of log messages from FND_FILE
+---------------------------------------------------------------------------+
+---------------------------------------------------------------------------+
Executing request completion options...
Output file size:
0
+------------- 1) PRINT   -------------+
Disabling requested Output Post Processing.  Nothing to process.  The output of the request is zero byte.
+--------------------------------------+
Finished executing request completion options.
+---------------------------------------------------------------------------+
Concurrent request completed successfully
Current system time is 02-SEP-2015 20:28:06
+---------------------------------------------------------------------------+

11. Describe one of the tables for which we enabled audit now. Say, OE_ORDER_LINES_ALL. You will find several triggers on this table that have been added by the AuditTrail Update Tables program. These triggers will ensure that the audit data is populated in the audit tables as soon as there is DML operation on the tables on which audit has been turned on.

The program has created these triggers along with other database objects given in the list above.
Note: All audit metadata is stored in the following tables,
1.         FND_AUDIT_SCHEMAS
2.         FND_AUDIT_TABLES
3.         FND_AUDIT_COLUMNS
4.         FND_AUDIT_GROUPS

12. Update the existing order line or Create new order line and check the Audi Tables
Select * from OE_ORDER_LINES_ALL_A

13. Also verify from application
Nav: System Administrator ->Security ->AuditTrail ->Audit Trail Reporting ->Audit Query Navigator
Expand the Audit group and Expand the Audit table select the Line ID

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect