To activate audit trail in Oracle for standard tables

DECLARE l_app_id NUMBER; l_fnd_audit_groups NUMBER; l_table_id1 NUMBER; l_table_id2 NUMBER; l_col_ven_id NUMBER; l_col_inv_amt_lmt NUMBER; l_col_ven_nm NUMBER; l_col_bnk_uses_id NUMBER; l_col_bnk_id NUMBER; l_col_bnk_vndr NUMBER; l_col_bnk_site NUMBER; l_col_bnk_org NUMBER; l_schema_id NUMBER; l_oracle_id NUMBER; l_cnt_id NUMBER; l_audit_group_app_id NUMBER; l_col_ven_id1 NUMBER; l_col_ven_id2 NUMBER; l_col_ven_id3 NUMBER; l_num_request_id NUMBER; l_num_user_id NUMBER; l_num_appl_id NUMBER; l_num_resp_id NUMBER; BEGIN/*Enable audit for PO user updating FND_AUDIT_SCHEMAS table for PO username. */
SELECT DECODE(install_group_num, 0, 900, 899 + NVL(install_group_num,1) ) INTO l_schema_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT oracle_id INTO l_oracle_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT application_id INTO l_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT application_id INTO l_audit_group_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT COUNT(oracle_id) INTO l_cnt_id FROM fnd_audit_schemas WHERE oracle_id = l_oracle_id;
IF l_cnt_id <> 0 THENUPDATE fnd_audit_schemas SET state = 'R',last_update_date = SYSDATE,last_updated_by = -1,last_update_login = -1WHERE oracle_id = l_oracle_id ;
DBMS_OUTPUT.PUT_LINE('No Of Records Updated in fnd_audit_schemas : ' SQL%ROWCOUNT ); ELSE INSERT INTO fnd_audit_schemas(schema_id,oracle_id,application_id, state,last_update_date,last_updated_by,last_update_login,created_by,creation_date) VALUES (l_schema_id,l_oracle_id,l_app_id,'R',SYSDATE,-1,-1,-1,SYSDATE);DBMS_OUTPUT.PUT_LINE('No Of Records Inserted in fnd_audit_schemas : ' SQL%ROWCOUNT ); END IF; SELECT audit_group_id INTO l_fnd_audit_groups FROM fnd_audit_groups WHERE group_name = 'Test Audit';
UPDATE fnd_audit_groups set STATE = 'R' where group_name = 'Test Audit';
/*Getting table_id for PO_VENDOR_SITES_ALL table*/ SELECT table_id INTO l_table_id1 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_app_id;
SELECT table_id INTO l_table_id2 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_audit_group_app_id;
/*Insert records into FND_AUDIT_TABLES table*/ INSERT INTO fnd_audit_tables(audit_group_id, audit_group_app_id, table_app_id, table_id, state, last_update_date, last_updated_by, last_update_login, created_by, creation_date) VALUES (l_fnd_audit_groups, l_audit_group_app_id, l_app_id, l_table_id1, 'R', SYSDATE, -1, -1, -1, SYSDATE); DBMS_OUTPUT.put_line ('No of records Inserted into FND_AUDIT_TABLES table : ' SQL%ROWCOUNT); /*Selecting column ID for columns */
SELECT column_id INTO l_col_ven_id3 FROM fnd_columns WHERE column_name = 'VENDOR_SITE_ID' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id1 FROM fnd_columns WHERE column_name = 'PAYMENT_METHOD_LOOKUP_CODE' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id2 FROM fnd_columns WHERE column_name = 'PRIMARY_FLAG' AND application_id = l_audit_group_app_id AND table_id = l_table_id2 ;
/*Insert records into FND_AUDIT_COLUMNS table for AP_BANK_ACCOUNTS_ALL*/
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 (l_app_id, l_table_id1, l_col_ven_id3, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
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 (l_app_id, l_table_id1, l_col_ven_id1, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
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 (l_audit_group_app_id, l_table_id2, l_col_ven_id2, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
BEGINSELECT application_id,responsibility_idINTO l_num_appl_id,l_num_resp_idFROM fnd_responsibility_vlWHERE responsibility_name = 'System Administrator';
EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the Responsibility details :' SQLERRM);END;
BEGINSELECT user_id INTO l_num_user_idFROM fnd_userWHERE user_name = 'FIN_ADMIN';EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the user details :' SQLERRM);END;
fnd_global.apps_initialize(user_id => l_num_user_id,resp_id => l_num_resp_id,resp_appl_id => l_num_appl_id,security_group_id => 0);
l_num_request_id := Fnd_Request.submit_request (application => 'FND',program => 'FNDATUPD');
COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLCODE '-' SQLERRM); ROLLBACK;END;/