How to Delete Concurrent Program

When you design a Concurrent Program. one of the annoyance is that once the Program Short Name is used by a Concurrent Program, the GUI does not provide anyway to change it.  You can delete Concurrent Program Executable (if it is not being assigned in any Concurrent Program), but you cannot delete Concurrent Program.  Why?

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