Thursday, May 6, 2010

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;/

1 comment:

Sridevi Koduru said...

I am Sridevi Koduru, Senior Oracle Apps Trainer With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Financial for Indian Localization, Oracle Apps SCM, Oracle Apps HRMS, SQL, PL/SQL and D2K at training@oracleappstechnical.com or sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/