Query -To set status to Revoked for any resource for any user

update oiu set ost_key=(select ost_key from ost where obj_key=(select obj_key from obj where obj.obj_name = 'AD User') and ost_status='Revoked') where   oiu.orc_key in (
SELECT orc_key  FROM ud_aduser WHERE  ud_aduser_uid in ('test1')) ;
commit;

Query to find the rejected task in a package(Resource) for a particular task

select usr.usr_login, pkg.pkg_name, mil.mil_name, oti.sta_bucket
from oti, orc, oiu, usr, mil, tos, pkg
where oti.orc_key = orc.orc_key
and   orc.orc_key = oiu.orc_key
and   oiu.usr_key = usr.usr_key
and   oti.mil_key = mil.mil_key
and   mil.tos_key = tos.tos_key
and   tos.pkg_key = pkg.pkg_key
and   pkg.pkg_name = 'AD User'
and   mil.mil_name = 'Create User';

Courtesy : Amitabh Gangwar