remove concurrent program from its request group & delete concurrent program definition and executable from back-end

-------------------------------------------------------------------------------
--  remove concurrent program from its request group  and then
--  delete concurrent program definition and executable from back-end
-------------------------------------------------------------------------------
-- syntax:
--     delete_program    (program_short_name, application_short_name)
--     delete_executable (program_short_name, application_short_name)
-------------------------------------------------------------------------------

DECLARE
   lv_prog_short_name      VARCHAR2 (240);
   lv_appl_short_name      VARCHAR2 (240);
   lv_request_group_appl   VARCHAR2 (240);
   lv_request_group_name   VARCHAR2 (240);

   CURSOR c1
   IS
      SELECT   cp.concurrent_program_name concurrent_program_short_name
             , cpt.user_concurrent_program_name
             , cp_appl.application_short_name conc_prgm_appl_short_name
             , cp_appl.application_name conc_prgm_appl_name
             , rg.request_group_name
             , cp_req_grp.application_short_name req_group_appl_short_name
             , cp_req_grp.application_name req_group_appl_name
             , DECODE (rgu.request_unit_type,
                       'P', 'Program',
                       'S', 'Set',
                       rgu.request_unit_type)
                  "Unit Type"
        FROM   fnd_request_groups rg
             , fnd_request_group_units rgu
             , fnd_concurrent_programs cp
             , fnd_concurrent_programs_tl cpt
             , fnd_application_vl cp_appl
             , fnd_application_vl cp_req_grp
       WHERE       rg.request_group_id = rgu.request_group_id
               AND rgu.request_unit_id = cp.concurrent_program_id
               AND cp.concurrent_program_id = cpt.concurrent_program_id
               AND cpt.user_concurrent_program_name =
                     'USPS NCMS Reverse Lockbox Import'
               AND cp_req_grp.application_id = rg.application_id
               AND cp_appl.application_id = cp.application_id;
BEGIN
   -- set the variables first

   FOR c1_rec IN c1
   LOOP
      lv_prog_short_name := c1_rec.concurrent_program_short_name;
      -- concurrent program short name
      lv_appl_short_name := c1_rec.conc_prgm_appl_short_name;

      -- application short name

      BEGIN
         fnd_program.remove_from_group (c1_rec.concurrent_program_short_name -- program_short_name
                                      , c1_rec.conc_prgm_appl_name -- application
                                      , c1_rec.request_group_name -- report group name
                                      , c1_rec.req_group_appl_name -- report group application
                                                                  );

         DBMS_OUTPUT.put_line(   'Concurrent Program '
                              || c1_rec.concurrent_program_short_name
                              || ' removed successfully from Request Group '
                              || c1_rec.request_group_name);
         COMMIT;
      END;
   END LOOP;

   -- see if the program exists. if found, delete the program
   IF fnd_program.program_exists (lv_prog_short_name, lv_appl_short_name)
      AND fnd_program.executable_exists (lv_prog_short_name
                                       , lv_appl_short_name)
   THEN
      fnd_program.delete_program (lv_prog_short_name, lv_appl_short_name);
      fnd_program.delete_executable (lv_prog_short_name, lv_appl_short_name);

      COMMIT;

      DBMS_OUTPUT.put_line (lv_prog_short_name || ' deleted successfully');
   -- if the program does not exist in the system
   ELSE
      DBMS_OUTPUT.put_line (lv_prog_short_name || ' not found');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error: ' || SQLERRM);
END;