Sunday 29 December 2013

Remove End Date From A User's Access To A Responsibility

DECLARE
   CURSOR respcur
   IS
      SELECT fu.user_id usid
           , fu.user_name
           , frt.responsibility_id resid
           , frt.application_id apid
           , frt.security_group_id sgid
           , furgd.start_date rstart
           , furgd.security_group_id
           , furgd.description info
        FROM applsys.fnd_user fu
           , apps.fnd_user_resp_groups_direct furgd
           , apps.fnd_responsibility_tl frt
       WHERE fu.user_id = furgd.user_id
         AND frt.application_id = furgd.responsibility_application_id
         AND frt.responsibility_id = furgd.responsibility_id
         AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
         AND furgd.end_date = '26-JUN-2005'
         AND furgd.description LIKE '% TEMPORARY ACCESS REMOVAL%';

   myresp respcur%ROWTYPE;
BEGIN
   FOR myresp IN respcur
   LOOP
      apps.fnd_user_resp_groups_api.update_assignment(
         myresp.usid
       , myresp.resid
       , myresp.apid
       , myresp.sgid
       , myresp.rstart
       , NULL                -- removes end-date on the responsibility access
       , myresp.info
      );
   END LOOP;
END;
/
COMMIT ;
EXIT

No comments:

Post a Comment