- If this Concurrent Program has been executed and it will leave records in the Concurrent Requests tables (FND_CONCURRENT_PROCESSES, FND_CONCURRENT_REQUESTS, etc)
- This Concurrent Program could be a part of a Concurrent Program Set.
- This Concurrent Program is used in a non-SRS way (e.g. AP format payment, WSH shipment Document Set)
If this Concurrent Program does not referenced or used in anywhere, it is safe to delete it. Oracle does provide API to do this: FND_PROGRAM.DELETE_PROGRAM. You can run the following script to delete such Concurrent Program, and you need to issue a COMMIT to really delete it.
SET SERVEROUTPUT ON DECLARE -- Change the following two parameters to fit your needs p_progShortName VARCHAR2 (100) := '[Program short name to be deleted]'; p_forceDelete BOOLEAN := [TRUE|FALSE]; num_programID NUMBER; var_progName VARCHAR2 (100); num_appID NUMBER; num_appName VARCHAR2 (100); var_sqlStmt VARCHAR2 (1000); num_count NUMBER; boo_proceed BOOLEAN := TRUE; BEGIN SELECT A.CONCURRENT_PROGRAM_ID , B.USER_CONCURRENT_PROGRAM_NAME , C.APPLICATION_ID , C.APPLICATION_NAME INTO num_programID , var_progName , num_appID , num_appName FROM FND_CONCURRENT_PROGRAMS A , FND_CONCURRENT_PROGRAMS_TL B , FND_APPLICATION_TL C WHERE A.CONCURRENT_PROGRAM_NAME = p_progShortName AND A.CONCURRENT_PROGRAM_ID = B.CONCURRENT_PROGRAM_ID AND A.APPLICATION_ID = C.APPLICATION_ID AND B.LANGUAGE = 'US' AND C.LANGUAGE = 'US'; DBMS_OUTPUT.PUT_LINE ('Program Name : ' || var_progName); DBMS_OUTPUT.PUT_LINE ('Program ID : ' || num_programID); DBMS_OUTPUT.PUT_LINE ('Application Name : ' || num_appID); DBMS_OUTPUT.PUT_LINE ('Application ID : ' || num_appName); DBMS_OUTPUT.PUT_LINE ('Scanning CONCURRENT_PROGRAM_ID...'); FOR RS IN ( SELECT b.owner , a.table_name , b.object_type FROM dba_tab_columns a , dba_objects b WHERE a.column_name = 'CONCURRENT_PROGRAM_ID' AND a.table_name = b.object_name AND a.owner = b.owner AND b.object_type NOT IN ('VIEW', 'SYNONYM') AND a.table_name NOT IN ('FND_CONCURRENT_PROGRAMS', 'FND_CONCURRENT_PROGRAMS_TL', 'FND_CONC_PROG_ONSITE_INFO') ORDER BY 2,1,3 ) LOOP var_sqlStmt := 'SELECT COUNT(*) FROM ' || RS.OWNER || '.' || RS.TABLE_NAME || ' WHERE CONCURRENT_PROGRAM_ID = ' || num_programID; EXECUTE IMMEDIATE var_sqlStmt INTO num_count; IF num_count > 0 THEN boo_proceed := FALSE; DBMS_OUTPUT.PUT_LINE (RS.TABLE_NAME || ' (' || num_count || ')'); END IF; END LOOP; DBMS_OUTPUT.PUT_LINE ('FND_REQUEST_GROUP_UNITS :'); FOR RS IN (SELECT a.request_group_name , c.application_name FROM FND_REQUEST_GROUPS A , FND_REQUEST_GROUP_UNITS B , FND_APPLICATION_TL C WHERE A.request_group_id = B.request_group_id AND B.request_unit_id = num_programID AND B.request_unit_type = 'P' AND A.application_id = C.application_id AND C.language = 'US') LOOP boo_proceed := FALSE; DBMS_OUTPUT.PUT_LINE (RS.request_group_name || '(' || RS.application_name || ')'); END LOOP; IF boo_proceed = TRUE OR p_forceDelete = TRUE THEN FND_PROGRAM.DELETE_PROGRAM (p_progShortName , num_appName); DELETE FROM FND_CONCURRENT_REQUESTS WHERE CONCURRENT_PROGRAM_ID = num_programID; DBMS_OUTPUT.PUT_LINE ('Concurrent program deleted. Please issue a commit to save the changes.'); ELSE DBMS_OUTPUT.PUT_LINE ('Cannot delete this concurrent program since it has been referenced.'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('Invalid Concurrent program short name: ' || p_progShortName); END; /
1 comment :
There is no need to delete from fnd_concurrent_requests. This is done by the API.
Post a Comment