Monday 29 December 2014

How to attach the Request Group for Responsibility in oracle apps



DECLARE
   CURSOR c1
   IS
      SELECT   fr.responsibility_id,
               fr.application_id,
               fr.data_group_application_id,
               fr.data_group_id,
               fr.menu_id,
               fr.web_host_name,
               fr.web_agent_name,
               fr.group_application_id,
               frt.responsibility_name,
               frt.description,
               fr.start_date,
               fr.VERSION,
               fr.responsibility_key
        FROM   fnd_responsibility_tl frt, fnd_responsibility fr
       WHERE   UPPER (frt.responsibility_name) IN
                     ( Responsibility Name)
               AND frt.LANGUAGE = 'US'
               AND frt.application_id = fr.application_id
               AND fr.responsibility_id = frt.responsibility_id;

   l_reg_group_id   NUMBER;
BEGIN
   SELECT   request_group_id
     INTO   l_reg_group_id
     FROM   fnd_request_groups frg
    WHERE   1 = 1 AND request_group_name = (Request Group Name);

   FOR i IN c1
   LOOP
      BEGIN
         fnd_responsibility_pkg.update_row (
            x_responsibility_id           => i.responsibility_id,
            x_application_id              => i.application_id,
            x_web_host_name               => i.web_host_name,
            x_web_agent_name              => i.web_agent_name,
            x_data_group_application_id   => i.data_group_application_id,
            x_data_group_id               => i.data_group_id,
            x_menu_id                     => i.menu_id,
            x_start_date                  => i.start_date,
            x_end_date                    => SYSDATE - 1,
            x_group_application_id        => i.group_application_id,
            x_request_group_id            => l_reg_group_id,
            x_version                     => i.VERSION,
            x_responsibility_key          => i.responsibility_key,
            x_responsibility_name         => i.responsibility_name,
            x_description                 => i.description,
            x_last_update_date            => SYSDATE,
            x_last_updated_by             => -1,
            x_last_update_login           => 0
         );
         COMMIT;
         DBMS_OUTPUT.put_line (
            i.responsibility_name || ' has been updated !!!'
         );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
      END;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;

No comments:

Post a Comment