Created
January 23, 2016 05:41
-
-
Save arbo-hacker/78754d0834e8d944c17c to your computer and use it in GitHub Desktop.
Como eliminar un concurrente de Oracle Financials / EBS / E-Business Suite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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