REM Copyright 2000, All Rights Reserved, Reed-Matthews, Inc. REM REM The Purge Concurrent Requests and/or Manager Data is run nightly to REM remove concurrent manager information from tables and clean up REM the log and out files. REM REM This program adds a delete trigger to the fnd_concurrent_requests table REM Whenever the Purge is run, instead of throwing the records away, REM some of the columns from these records will be added to the REM RMINC_fnd_concurrent_requests table. This allows us to maintain a REM history table so we can run programs like rptsump.sql that track performance. REM REM This table will become very large over time, and should have data REM deleted from it on some periodic basis /* File: RMINC_fnd_concurrent_requests.trg Purpose: Database trigger for fnd_concurrent_requests table. copies inserts/updates into RMINC_fnd_concurrent_requests Owner: applsys Created By: Barb Matthews Suggested Changes By: gpayne@SMTPGwy.dunloptire.com */ connect applsys/password REM If you are upgrading to a new release, you must first REM delete all the records from RMINC_fnd_concurrent_requests that REM match records in fnd_concurrent_requests: REM delete from RMINC_fnd_concurrent_requests REM where RMINC_fnd_concurrent_requests.request_id in REM (select fnd_concurrent_requests.request_id REM from fnd_concurrent_requests,RMINC_fnd_concurrent_requests REM where RMINC_fnd_concurrent_requests.request_id= REM fnd_concurrent_requests.request_id); drop trigger applsys.RMINC_fnd_concurr_requests_del; REM Now you can set up the trigger that will fill the REM RMINC_fnd_concurrent_requests table whenever a purge takes REM place on the fnd_concurrent_requests table. Make REM the purge concurrent requests concurrent program REM incompatible with itself just to make sure we never REM get into a locking battle over these tables accidentally REM Note that as new releases of Oracle Applications come out, REM the fnd_concurrent_requests table may change. To make the REM triggers work, change the trigger below REM to match the fnd_concurrent_requests table changes. connect system/password create or replace trigger applsys.RMINC_fnd_concurr_requests_del before delete on applsys.fnd_concurrent_requests for each row BEGIN insert into applsys.RMINC_fnd_concurrent_requests (REQUEST_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_DATE, REQUESTED_BY, PHASE_CODE, STATUS_CODE, PRIORITY_REQUEST_ID, PRIORITY, REQUESTED_START_DATE, HOLD_FLAG, ENFORCE_SERIALITY_FLAG, SINGLE_THREAD_FLAG, HAS_SUB_REQUEST, IS_SUB_REQUEST, IMPLICIT_CODE, UPDATE_PROTECTED, QUEUE_METHOD_CODE, ARGUMENT_INPUT_METHOD_CODE, ORACLE_ID, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID, RESPONSIBILITY_APPLICATION_ID, RESPONSIBILITY_ID, NUMBER_OF_ARGUMENTS, NUMBER_OF_COPIES, SAVE_OUTPUT_FLAG, PRINTER, PRINT_STYLE, PRINT_GROUP, REQUEST_CLASS_APPLICATION_ID, CONCURRENT_REQUEST_CLASS_ID, PARENT_REQUEST_ID, CONC_LOGIN_ID, LANGUAGE_ID, DESCRIPTION, REQ_INFORMATION, RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE, RESUBMIT_TIME, RESUBMIT_END_DATE, RESUBMITTED, CONTROLLING_MANAGER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, COMPLETION_TEXT, OUTCOME_PRODUCT, OUTCOME_CODE, CPU_SECONDS, LOGICAL_IOS, PHYSICAL_IOS, LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME, OUTFILE_NODE_NAME, ARGUMENT_TEXT, NLS_LANGUAGE, NLS_TERRITORY) values (:old.REQUEST_ID, :old.LAST_UPDATE_DATE, :old.LAST_UPDATED_BY, :old.LAST_UPDATE_LOGIN, :old.REQUEST_DATE, :old.REQUESTED_BY, :old.PHASE_CODE, :old.STATUS_CODE, :old.PRIORITY_REQUEST_ID, :old.PRIORITY, :old.REQUESTED_START_DATE, :old.HOLD_FLAG, :old.ENFORCE_SERIALITY_FLAG, :old.SINGLE_THREAD_FLAG, :old.HAS_SUB_REQUEST, :old.IS_SUB_REQUEST, :old.IMPLICIT_CODE, :old.UPDATE_PROTECTED, :old.QUEUE_METHOD_CODE, :old.ARGUMENT_INPUT_METHOD_CODE, :old.ORACLE_ID, :old.PROGRAM_APPLICATION_ID, :old.CONCURRENT_PROGRAM_ID, :old.RESPONSIBILITY_APPLICATION_ID, :old.RESPONSIBILITY_ID, :old.NUMBER_OF_ARGUMENTS, :old.NUMBER_OF_COPIES, :old.SAVE_OUTPUT_FLAG, :old.PRINTER, :old.PRINT_STYLE, :old.PRINT_GROUP, :old.REQUEST_CLASS_APPLICATION_ID, :old.CONCURRENT_REQUEST_CLASS_ID, :old.PARENT_REQUEST_ID, :old.CONC_LOGIN_ID, :old.LANGUAGE_ID, :old.DESCRIPTION, :old.REQ_INFORMATION, :old.RESUBMIT_INTERVAL, :old.RESUBMIT_INTERVAL_UNIT_CODE, :old.RESUBMIT_INTERVAL_TYPE_CODE, :old.RESUBMIT_TIME, :old.RESUBMIT_END_DATE, :old.RESUBMITTED, :old.CONTROLLING_MANAGER, :old.ACTUAL_START_DATE, :old.ACTUAL_COMPLETION_DATE, :old.COMPLETION_TEXT, :old.OUTCOME_PRODUCT, :old.OUTCOME_CODE, :old.CPU_SECONDS, :old.LOGICAL_IOS, :old.PHYSICAL_IOS, :old.LOGFILE_NAME, :old.LOGFILE_NODE_NAME, :old.OUTFILE_NAME, :old.OUTFILE_NODE_NAME, :old.ARGUMENT_TEXT, :old.NLS_LANGUAGE, :old.NLS_TERRITORY); END; / REM The last step uses a view of the two tables fnd_concurrent_requests and REM RMINC_fnd_concurrent_requests, which is pretty inefficient. What works REM much better is to change the programs that reference the data in these two REM tables to use a UNION. At this point, Report History uses the UNION and REM Report Summary by program uses the view. drop view applsys.RMINC_conc_mgr_history; create view applsys.RMINC_conc_mgr_history as select * from applsys.RMINC_fnd_concurrent_requests union select REQUEST_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_DATE, REQUESTED_BY, PHASE_CODE, STATUS_CODE, PRIORITY_REQUEST_ID, PRIORITY, REQUESTED_START_DATE, HOLD_FLAG, ENFORCE_SERIALITY_FLAG, SINGLE_THREAD_FLAG, HAS_SUB_REQUEST, IS_SUB_REQUEST, IMPLICIT_CODE, UPDATE_PROTECTED, QUEUE_METHOD_CODE, ARGUMENT_INPUT_METHOD_CODE, ORACLE_ID, PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID, RESPONSIBILITY_APPLICATION_ID, RESPONSIBILITY_ID, NUMBER_OF_ARGUMENTS, NUMBER_OF_COPIES, SAVE_OUTPUT_FLAG, PRINTER, PRINT_STYLE, PRINT_GROUP, REQUEST_CLASS_APPLICATION_ID, CONCURRENT_REQUEST_CLASS_ID, PARENT_REQUEST_ID, CONC_LOGIN_ID, LANGUAGE_ID, DESCRIPTION, REQ_INFORMATION, RESUBMIT_INTERVAL, RESUBMIT_INTERVAL_UNIT_CODE, RESUBMIT_INTERVAL_TYPE_CODE, RESUBMIT_TIME, RESUBMIT_END_DATE, RESUBMITTED, CONTROLLING_MANAGER, ACTUAL_START_DATE, ACTUAL_COMPLETION_DATE, COMPLETION_TEXT, OUTCOME_PRODUCT, OUTCOME_CODE, CPU_SECONDS, LOGICAL_IOS, PHYSICAL_IOS, LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME, OUTFILE_NODE_NAME, ARGUMENT_TEXT, NLS_LANGUAGE, NLS_TERRITORY from applsys.fnd_concurrent_requests; REM as user applsys connect applsys/password grant select on RMINC_conc_mgr_history to public;