Skip to content

Instantly share code, notes, and snippets.

@arbo-hacker
Created January 23, 2016 05:41
Show Gist options
  • Save arbo-hacker/78754d0834e8d944c17c to your computer and use it in GitHub Desktop.
Save arbo-hacker/78754d0834e8d944c17c to your computer and use it in GitHub Desktop.
Como eliminar un concurrente de Oracle Financials / EBS / E-Business Suite
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;
/
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment