API to Create Responsibilities

CREATE RESPONSIBILITY

/* Formatted on 2/9/2016 10:11:52 AM (QP5 v5.149.1003.31008) */
CREATE OR REPLACE PROCEDURE XX_CREATE_RESP_PRC
IS
   /*
   CREATE TABLE APPS.XX_CREATE_RESP_STG
   (
     USER_NAME            VARCHAR2(100 BYTE),
     RESPONSIBILITY_NAME  VARCHAR2(300 BYTE),
     START_DATE           DATE,
     END_DATE             DATE,
     STATUS               VARCHAR2(100 BYTE),
     ERROR                VARCHAR2(1000 BYTE)
   ) */

   CURSOR c1
   IS
      SELECT ROWID, t.*
        FROM XX_CREATE_RESP_STG t;

   l_user_name              VARCHAR2 (100);
   l_resp_appl_short_name   VARCHAR2 (100);
   l_responsibility_key     VARCHAR2 (100);
   l_responsibility_name    VARCHAR2 (111) := NULL;
   security_group_key       VARCHAR2 (100);
   v_status                 VARCHAR2 (111);
   l_ERROR                  VARCHAR2 (1000);
   v_error                  VARCHAR2 (111) := NULL;
BEGIN
   FOR i IN c1
   LOOP
      BEGIN
         SELECT user_name
           INTO l_user_name
           FROM fnd_user
          WHERE user_name = i.user_name;

         DBMS_OUTPUT.put_line ('User name  :=' || l_user_name);

         v_status := 'S';
      EXCEPTION
         WHEN OTHERS
         THEN
            v_status := NULL;
      END;

      BEGIN
         SELECT res.RESPONSIBILITY_NAME
           INTO l_responsibility_name
           FROM apps.FND_USER usr,
                apps.FND_RESPONSIBILITY_TL res,
                apps.FND_USER_RESP_GROUPS grp
          WHERE     grp.responsibility_id = res.responsibility_id
                AND grp.user_id = usr.user_id
                AND usr.USER_NAME = i.user_name
                AND res.RESPONSIBILITY_NAME = i.RESPONSIBILITY_NAME;

         DBMS_OUTPUT.
          put_line ('responsibility_name  :=   ' || l_responsibility_name);
      EXCEPTION
         WHEN OTHERS
         THEN
            l_responsibility_name := NULL;
      END;

      BEGIN
         SELECT res.responsibility_key, app.application_short_name
           INTO l_responsibility_key, l_resp_appl_short_name
           FROM fnd_responsibility_vl res, fnd_application_vl app
          WHERE res.responsibility_name = i.RESPONSIBILITY_NAME
                AND res.application_id = app.application_id;

         DBMS_OUTPUT.
          put_line (
               'responsibility_key :-  '
            || l_responsibility_key
            || '  '
            || 'short name :-  '
            || l_resp_appl_short_name);
      END;

      BEGIN
         IF v_status = 'S' AND l_responsibility_name is NULL
         THEN
            fnd_user_pkg.addresp (username         => l_user_name,
                                  resp_app         => l_resp_appl_short_name,
                                  resp_key         => l_responsibility_key,
                                  security_group   => 'STANDARD',
                                  description      => NULL,
                                  start_date       => i.start_date,
                                  end_date         => NULL);

            UPDATE APPS.XX_CREATE_RESP_STG T
               SET T.status = 'V', T.ERROR = 'Success'
             WHERE ROWID = i.ROWID;

            DBMS_OUTPUT.put_line ('IN API CREATED');

            COMMIT;
         ELSE
            UPDATE APPS.XX_CREATE_RESP_STG T
               SET T.status = 'E', T.ERROR = 'Already Available'
             WHERE ROWID = i.ROWID;

            DBMS_OUTPUT.
             put_line ('User Responsibility Already Available---------');
            COMMIT;
         END IF;
      END;

      v_status := NULL;
      v_error := NULL;
      l_user_name := NULL;
      l_resp_appl_short_name := NULL;
      l_responsibility_key := NULL;
      l_responsibility_name := NULL;
      security_group_key := NULL;
   END LOOP;
END;

exec XX_CREATE_RESP_PRC;

SELECT * FROM XX_CREATE_RESP_STG