Wednesday, April 26, 2017

Anonymous Block to End date All Responsibilities with API


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,
             fr.request_group_id,
             fr.responsibility_key,
             frt.responsibility_name,
             frt.description,
             fr.start_date,
             fr.version
        from fnd_responsibility_tl frt, fnd_responsibility fr
where frt.application_id = 20003
and frt.application_id = fr.application_id
and frt.responsibility_id = fr.responsibility_id
and frt.creation_date < to_date('01-FEB-2017')
--and fr.responsibility_key = 'CSCUS_MANAGER'
;
BEGIN

fnd_global.APPS_INITIALIZE(3918,20420,1);

   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            => i.request_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             => fnd_global.user_id,
            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