Wednesday 25 November 2015

Add a Concurrent Program to a Responsibility using PL/SQL Script


Input:
l_program_name: Give concurrent program name you want to add to a responsibility               
l_responsibility_name: Give responsibility name for which you want add the above program.

Script:
DECLARE
   -- Provide concurrent program name and Responsibility name here:
   l_program_name                VARCHAR2 (240) := '<&>';
   l_responsibility_name         VARCHAR2 (240) := 'XX OKS INQUIRY';

   ---- Private variables to program ----
   l_prog_short_name             VARCHAR2 (240);
   l_prog_appl_short_name        VARCHAR2 (240);

   l_req_group_name              VARCHAR2 (240);
   l_req_group_appl_short_name   VARCHAR2 (240);
BEGIN
   -- Fetch Request group details from Responsibility
   BEGIN
      SELECT rga.application_short_name rg_appl_short_name,
             rg.request_group_name
        INTO l_req_group_appl_short_name, l_req_group_name
        FROM fnd_responsibility_vl resp,
             fnd_request_groups rg,
             fnd_application rga
       WHERE     UPPER (resp.responsibility_name) LIKE
                    UPPER (l_responsibility_name || '%')
             AND resp.group_application_id = rg.application_id
             AND resp.request_group_id = rg.request_group_id
             AND rga.application_id = rg.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching request group details:' || SQLERRM);
         RETURN;
   END;

   -- Fetch Program details from Conc program name
   BEGIN
      SELECT cp.concurrent_program_name, cpa.application_short_name
        INTO l_prog_short_name, l_prog_appl_short_name
        FROM fnd_concurrent_programs_vl cp, fnd_application cpa
       WHERE     1 = 1
             AND cp.user_concurrent_program_name LIKE l_program_name || '%'
             AND cp.application_id = cpa.application_id;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'Exception when fetching program details:' || SQLERRM);
         RETURN;
   END;


   FND_PROGRAM.add_to_group (l_prog_short_name           -- program_short_name
                                              ,
                             l_prog_appl_short_name -- application name/ application short name
                                                   ,
                             l_req_group_name            -- Request Group Name
                                             ,
                             l_req_group_appl_short_name); --Request Group Application
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Exception :' || SQLERRM);
      ROLLBACK;
END;
/

No comments:

Post a Comment