Sunday 29 December 2013

End Date 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(furgd.end_date, SYSDATE + 1) > SYSDATE
         AND NVL(fu.end_date, SYSDATE + 1) > SYSDATE
         AND frt.responsibility_name = 'Minion Restricted Access';

   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
       , TRUNC(SYSDATE)
       , myresp.info || ' TEMPORARY ACCESS REMOVAL'
      );
   END LOOP;
END;
/
COMMIT;
EXIT

No comments:

Post a Comment