Adding Resp Via API / Deleting Executable and concurrent Program
DECLARE
lv_user_name VARCHAR2 (20) := 'SENTHIL';
lv_req_resp_key VARCHAR2 (50); -- := 'APPLICATION_DEVELOPER';
lv_description VARCHAR2 (100) := 'Via script added';
lv_req_resp_name VARCHAR2 (200);
lv_appl_shrt_name VARCHAR2 (20);
lv_appl_name VARCHAR2 (50);
lv_resp_key VARCHAR2 (50);
cursor c1 is
SELECT fav.application_short_name,
fav.application_name,
upper(frv.responsibility_name) responsibility_name,
frv.responsibility_key
--INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name
FROM fnd_application_vl fav, fnd_responsibility_vl frv
WHERE frv.application_id = fav.application_id
and upper(frv.responsibility_name) in
(
'FUNCTIONAL ADMINISTRATOR'
,'AP PAYABLES SUPER USER'
);
BEGIN
DBMS_OUTPUT.put_line ('Below Responsibility Added for this user '|| lv_user_name);
DBMS_OUTPUT.put_line ('--------------------------------------------------------------');
for rec in c1
loop
/*SELECT fav.application_short_name,
fav.application_name,
frv.responsibility_name
INTO lv_appl_shrt_name, lv_appl_name, lv_req_resp_name
FROM fnd_application_vl fav, fnd_responsibility_vl frv
WHERE frv.application_id = fav.application_id
AND frv.responsibility_key = lv_req_resp_key; */
fnd_user_pkg.addresp (username => lv_user_name,
resp_app => rec.application_short_name, --lv_appl_shrt_name,
resp_key => rec.responsibility_key , --lv_req_resp_key,
security_group => 'STANDARD',
description => lv_description,
start_date => SYSDATE,
end_date => NULL
);
COMMIT;
DBMS_OUTPUT.put_line (rec.responsibility_name);
end loop;
DBMS_OUTPUT.put_line ('--------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line ('Responsibility IS NOT added due to ' || SQLCODE || '; ' || SUBSTR (SQLERRM, 1, 250));
ROLLBACK;
END;
/* Deleting Executable and concurrent Program*/
SET SERVEROUTPUT ON;
DECLARE
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_del_prog_flag VARCHAR2 (1) := 'Y'; --Set flag whether to delete Concurrent program or not
l_del_exec_flag VARCHAR2 (1) := 'Y'; --Set flag whether to delete executable or not
BEGIN
--
-- set concurrent program and executable short name
--
l_prog_short_name := 'XX_TEST_CP'; -- Concurrent program short name
l_exec_short_name := 'XX_TEST_EXEC'; -- Executable short name
l_appl_full_name := 'XX Custom Application'; -- Application full name
l_appl_short_name := 'XXCUST'; -- Application Short name
--
-- Check if the program exists. if found, delete the program
--
IF 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
IF l_del_prog_flag = 'Y'
THEN
--
--API call to delete Concurrent Program
--
fnd_program.delete_program (l_prog_short_name, l_appl_full_name);
--
END IF;
--
IF l_del_exec_flag = 'Y'
THEN
--
--API call to delete Executable
--
fnd_program.delete_executable (l_exec_short_name, l_appl_full_name);
--
END IF;
COMMIT;
--
DBMS_OUTPUT.put_line ('Concurrent Program '||l_prog_short_name || ' deleted successfully');
DBMS_OUTPUT.put_line ('Executable '||l_exec_short_name || ' deleted successfully');
--
-- if the program does not exist in the system
--
ELSE
DBMS_OUTPUT.put_line (l_prog_short_name ||' or '||l_exec_short_name|| ' not found');
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error while deleting: ' || SQLERRM);
END;
/* Delete XML Publisher Definition and Template*/
SET SERVEROUTPUT ON
DECLARE
-- Change the following two parameters
var_templateCode VARCHAR2 (100) := 'XX_US_BEN_NOT_EXP_ELEC'; -- Template Code
boo_deleteDataDef BOOLEAN := TRUE; -- delete the associated Data Def.
BEGIN
FOR RS
IN (SELECT T1.APPLICATION_SHORT_NAME TEMPLATE_APP_NAME,
T1.DATA_SOURCE_CODE,
T2.APPLICATION_SHORT_NAME DEF_APP_NAME
FROM XDO_TEMPLATES_B T1, XDO_DS_DEFINITIONS_B T2
WHERE T1.TEMPLATE_CODE = var_templateCode
AND T1.DATA_SOURCE_CODE = T2.DATA_SOURCE_CODE)
LOOP
XDO_TEMPLATES_PKG.DELETE_ROW (RS.TEMPLATE_APP_NAME, var_templateCode);
DELETE FROM XDO_LOBS
WHERE LOB_CODE = var_templateCode
AND APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND LOB_TYPE IN ('TEMPLATE_SOURCE', 'TEMPLATE');
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.TEMPLATE_APP_NAME
AND TEMPLATE_CODE = var_templateCode
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 50;
DBMS_OUTPUT.PUT_LINE ('Template ' || var_templateCode || ' deleted.');
IF boo_deleteDataDef
THEN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (RS.DEF_APP_NAME,
RS.DATA_SOURCE_CODE);
DELETE FROM XDO_LOBS
WHERE LOB_CODE = RS.DATA_SOURCE_CODE
AND APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND LOB_TYPE IN
('XML_SCHEMA',
'DATA_TEMPLATE',
'XML_SAMPLE',
'BURSTING_FILE');
DELETE FROM XDO_CONFIG_VALUES
WHERE APPLICATION_SHORT_NAME = RS.DEF_APP_NAME
AND DATA_SOURCE_CODE = RS.DATA_SOURCE_CODE
AND CONFIG_LEVEL = 30;
DBMS_OUTPUT.PUT_LINE (
'Data Defintion ' || RS.DATA_SOURCE_CODE || ' deleted.');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE (
'Issue a COMMIT to make the changes or ROLLBACK to revert.');
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (
'Unable to delete XML Publisher Template ' || var_templateCode);
DBMS_OUTPUT.PUT_LINE (SUBSTR (SQLERRM, 1, 200));
END;