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