Como eliminar un concurrente de Oracle Financials / EBS / E-Business Suite

eliminar-concurrente.sql

DECLARE
 -- Change the following two parameters to fit your needs
 p_progShortName VARCHAR2 (100) := '&nombre del concurrente'; -- este debe ser obtenido de la tabla fnd_concurrent_programs
 p_forceDelete BOOLEAN := TRUE;
 
num_programID NUMBER;
 var_progName VARCHAR2 (100);
 num_appID NUMBER;
 num_execID NUMBER;
 num_appIDName VARCHAR2 (100);
 num_execName VARCHAR2 (100);
 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,
 A.executable_id
 INTO num_programID,
 var_progName,
 num_appID,
 num_appName,
 num_execID
 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 = 'ESA'
 AND C.LANGUAGE = 'ESA';
 
SELECT executable_name
 INTO num_execName
 FROM fnd_executables
 WHERE executable_id = num_execID;
 
SELECT APPLICATION_SHORT_NAME
 INTO num_appIDName
 FROM fnd_application
 WHERE application_id = num_appID;
 
DBMS_OUTPUT.PUT_LINE ('Program Name : ' || var_progName);
 DBMS_OUTPUT.PUT_LINE ('Program ID : ' || num_programID);
 DBMS_OUTPUT.PUT_LINE ('Application Name : ' || num_appName);
 DBMS_OUTPUT.PUT_LINE ('Application ID : ' || num_appID);
 DBMS_OUTPUT.PUT_LINE ('Executable ID : ' || num_execID);
 DBMS_OUTPUT.PUT_LINE ('Executable Name : ' || num_execName);
 
DBMS_OUTPUT.PUT_LINE ('Scanning CONCURRENT_PROGRAM_ID...');
 
FOR RS
 IN ( SELECT a.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 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 1, 2)
 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 = 'ESA')
 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);
 FND_PROGRAM.DELETE_EXECUTABLE (num_execName, num_appIDName);
 
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;
/