Delete the concurrent programs
Prerequisites: If the concurrent program is not in use, the program details need to be entered in the lookup and then this concurrent program needs to be run.
This program will also check if the underlying executable is being used by any other program, in that case this should not be deleted.
create or replace PACKAGE XX_DROP_CP_EXEC_PKG AS
/* To delete the concurrent program and executable given in the lookup XX_DROP_OBSOLETE_CP*/
g_conc_request_id NUMBER := fnd_global.conc_request_id;
g_success NUMBER;
g_error NUMBER := 1;
PROCEDURE write_log(p_message VARCHAR2);
PROCEDURE main (errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
);
end XX_DROP_CP_EXEC_PKG;
create or replace PACKAGE BODY xx_drop_cp_exec_pkg AS
— To delete the concurrent program and executable given in the lookup XX_DROP_OBSOLETE_CP
PROCEDURE write_log( p_message VARCHAR2)
IS
BEGIN
fnd_file.put_line (fnd_file.LOG, g_module||’:’|| p_message );
–dbms_output.put_line( p_message );
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line (fnd_file.LOG, g_module ||’ – ‘ || SUBSTR (SQLERRM, 1, 200));
END write_log;
PROCEDURE main (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2
) AS
l_prog_short_name VARCHAR2(240);
l_exec_short_name VARCHAR2(240);
l_appl_full_name VARCHAR2(240);
l_appl_short_name VARCHAR2(240);
l_exec_count NUMBER := 0;
lv_message VARCHAR2(1000);
–This cursor will fetch the concurrent program and executable details for the program names provided in the lookup XX_DROP_OBSOLETE_CP
— and the change tracker details were available in the tag and is enabled
CURSOR c1 IS SELECT
o.rowid row_id,
a.concurrent_program_id,
ft.application_name,
fa.application_short_name,
o.lookup_code cp_short_name,
o.description cp_name,
o.tag change_tracker,
b.executable_name,
b.executable_id,
upper(b.execution_file_name) package_procedure
FROM
apps.fnd_concurrent_programs_vl a,
apps.fnd_executables b,
apps.fnd_lookup_values o,
apps.fnd_application_tl ft,
apps.fnd_application fa
WHERE
a.executable_id = b.executable_id
AND o.lookup_type = ‘XX_DROP_OBSOLETE_CP’
AND o.lookup_code = a.concurrent_program_name
AND o.language = ‘US’
AND fa.application_id = ft.application_id
AND fa.application_id = a.application_id
AND ft.language = ‘US’
AND o.tag is not null
AND o.enabled_flag = ‘Y’;
BEGIN
— Loop Start – To delete all the eligible executables and concurrent programs
FOR i IN c1 LOOP
l_exec_count :=0;
l_prog_short_name := i.cp_short_name; — Concurrent program short name
l_exec_short_name := i.executable_name; — Executable short name
l_appl_full_name := i.application_name; — Application full name
l_appl_short_name := i.application_short_name;
BEGIN
— Select if any other program is using the same executable and is not marked to be dropped
SELECT
count(1) into l_exec_count
FROM
fnd_concurrent_programs cp
WHERE
executable_id = i.executable_id
AND concurrent_program_id <> i.concurrent_program_id
AND NOT EXISTS (
SELECT
1
FROM
fnd_lookup_values fl
WHERE
fl.lookup_type = ‘XX_DROP_OBSOLETE_CP’
AND fl.lookup_code = cp.concurrent_program_name
AND fl.language = ‘US’
AND fl.tag is not null
);
END;
— If any other program is using same executable – The executable and program should not be deleted
IF l_exec_count <> 0 THEN
write_log(‘Cannot delete the Executable ‘
|| l_exec_short_name
||’ and Concurrent Program ‘
|| l_prog_short_name
|| ‘ as another concurrent program is using the same executable.’);
ELSE
IF
— Check if the program and executable exists in the system
fnd_program.program_exists(l_prog_short_name,l_appl_short_name) AND fnd_program.executable_exists(l_exec_short_name,l_appl_short_name
)
THEN
BEGIN
–API call to delete Concurrent Program
fnd_program.delete_program(l_prog_short_name,l_appl_full_name);
write_log(‘Concurrent Program ‘
|| l_prog_short_name
|| ‘ deleted successfully’);
–API call to delete Executable
fnd_program.delete_executable(l_exec_short_name,l_appl_full_name);
write_log(‘Executable ‘
|| l_exec_short_name
|| ‘ deleted successfully’);
UPDATE fnd_lookup_values
SET
enabled_flag = ‘N’,
end_date_active=sysdate
WHERE
ROWID = i.row_id;
COMMIT;
EXCEPTION WHEN OTHERS THEN
lv_message := ‘Error while deleting Executable ‘
|| l_exec_short_name||’ and Concurrent Program ‘
|| l_prog_short_name||SUBSTR (SQLERRM, 1, 200);
write_log(lv_message);
END;
ELSE
write_log(l_prog_short_name
|| ‘ or ‘
|| l_exec_short_name
|| ‘ not found’);
END IF;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
lv_message := ‘Error while deleting: ‘||SUBSTR (SQLERRM, 1, 200);
write_log(lv_message);
errbuf := lv_message;
retcode := g_error;
END;
END xx_drop_cp_exec_pkg;
/
SHOW ERRORS;
COMMIT;
EXIT;
