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
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