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;

Query to find Responsibility for the Concurrent Program

 SELECT distinct frt.responsibility_name,

               frg.request_group_name,

               frg.description,fcpt.user_concurrent_program_name

  FROM fnd_request_groups frg

             ,fnd_request_group_units frgu

             ,fnd_concurrent_programs fcp

             ,fnd_concurrent_programs_tl fcpt

             ,fnd_responsibility_tl frt

             ,fnd_responsibility frs

 WHERE frgu.unit_application_id = fcp.application_id

 AND   frgu.request_unit_id = fcp.concurrent_program_id

 AND   frg.request_group_id = frgu.request_group_id

 AND   frg.application_id = frgu.application_id

 AND   fcpt.source_lang = USERENV('LANG')

 AND   fcp.application_id = fcpt.application_id

 AND   fcp.concurrent_program_id = fcpt.concurrent_program_id

 AND   frs.application_id = frt.application_id

 AND   frs.responsibility_id = frt.responsibility_id

 AND   frt.source_lang = USERENV('LANG')

 AND   frs.request_group_id = frg.request_group_id

 AND   frs.application_id = frg.application_id

 --AND   fcp.concurrent_program_name = <shortname>

 AND   fcpt.user_concurrent_program_name LIKE '%XXX%Pre%%%'

 and frt.responsibility_name like '%XX_PO%'