Responsibility Menu Exclusion Load Script in Oracle Apps

To Load Menu Exclusions Against Responsibility


Custom table to be created to populate responsibility against Menu Exclusion Details



CREATE TABLE APPS.XX_MENU_EXCLU_LOAD
(
  RESPONSIBILITY_ID    NUMBER(15)               NOT NULL,
  RESPONSIBILITY_NAME  VARCHAR2(100 BYTE)       NOT NULL,
  APPLICATION_NAME     VARCHAR2(240 BYTE)       NOT NULL,
  APPLICATION_ID       NUMBER                   NOT NULL,
  RULE_TYPE            VARCHAR2(8 BYTE),
  MENU_EXCLUSION       VARCHAR2(80 BYTE),
  ACTION_ID            NUMBER,
  STATUS               CHAR(1 BYTE),
  ERRORMSG             VARCHAR2(500 BYTE)
)



After loading the data in Custom table , Execute the below script to Load Responsibility Menu Exclusion values



DECLARE
   CURSOR c1
   IS
      SELECT *
        FROM xx_menu_exclu_load
       WHERE rule_type IN ('Function', 'Menu');


   v_resp_id   NUMBER;
BEGIN
   FOR i IN c1
   LOOP
      BEGIN


         DBMS_OUTPUT.put_line ('Processing');


         INSERT INTO fnd_resp_functions
                     (application_id, responsibility_id, action_id,
                      rule_type,
                      last_updated_by, last_update_date, creation_date,
                      created_by, last_update_login
                     )
              VALUES (i.application_id, i.responsibility_id, i.action_id,
                      DECODE (i.rule_type, 'Function', 'F', 'Menu', 'M'),
                      10892, SYSDATE, SYSDATE,
                      10892, 10892
                     );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Completed');
END;