script to set profile option using API. MO: Operating unit

We used to set profile options from front end. If we have so many responsibilities and we need to set profile option from backend. We can use following script to set profile option.

-->

CREATE OR REPLACE PROCEDURE APPS.xxran_profile_opt_org

AS

   v_responsibility_id   NUMBER;

   v_application_id      NUMBER;

   v_org_id              NUMBER;

   lc_status             VARCHAR2 (1);

   lc_error_msg          VARCHAR2 (240);

   v_resp_key            VARCHAR2 (32);

   v_operating_unit      VARCHAR2 (50);

/*

CREATE TABLE APPS.XXRAN_PROFILE_ORG

(

  OPERATING_UNIT      VARCHAR2(50 BYTE),

  RESPONSIBILITY_KEY  VARCHAR2(33 BYTE)

)

*/

   CURSOR xxcur

   IS

      SELECT operating_unit, responsibility_key

        FROM xxran_profile_org;

stat                  BOOLEAN;

BEGIN

   FOR c IN xxcur

   LOOP

      lc_status := 'Y';

      stat := null;

      BEGIN

         v_resp_key := c.responsibility_key;

         v_operating_unit := c.operating_unit;

         BEGIN

            SELECT application_id, responsibility_id

              INTO v_application_id, v_responsibility_id

              FROM fnd_responsibility

             WHERE responsibility_key = v_resp_key;

            SELECT organization_id

              INTO v_org_id

              FROM hr_operating_units

             WHERE NAME = v_operating_unit;

             DBMS_OUTPUT.put_line ('ID fetched successfully.');

         EXCEPTION

            WHEN OTHERS

            THEN

               DBMS_OUTPUT.put_line ('failed to fetch ids' || SQLERRM);

               lc_status := 'E';

         END;

            DBMS_OUTPUT.put_line ('lc_status is: '||lc_status);

         IF lc_status = 'Y'

         THEN

            stat :=

               fnd_profile.SAVE ('ORG_ID',

                                 v_org_id,

                                 'RESP',

                                 v_responsibility_id,

                                 v_application_id

                                );

            IF stat

            THEN

               DBMS_OUTPUT.put_line (   'Stat = TRUE - profile '

                                     || v_resp_key

                                     || ' updated'

                                    );

            ELSE

               DBMS_OUTPUT.put_line (   'Stat = TRUE - profile '

                                     || v_resp_key

                                     || ' NOT updated'

                                    );

            END IF;

         END IF;

      EXCEPTION

         WHEN OTHERS

         THEN

            DBMS_OUTPUT.put_line (   'failed to add profile option for '

                                  || v_resp_key

                                  || ' : '

                                  || SQLERRM

                                 );

      END;

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('failed to main ' || SQLERRM);

END;

/