LDAP Request SQL Query in Oracle Cloud

LDAP Request SQL Query in Oracle Cloud

LDAP is Lightweight Directory Access Protocol (LDAP) in oracle cloud. LDAP is the Directory Available in the Oracle cloud to handle Oracle Cloud application Users , roles and their Privileges . Fusion Architecture application is to keep the User Account information's details in this separate LDAP Directory.

 

This below sql Query helps to extract the Pending LDAP Requests generated from the Oracle cloud application to the LDAP Directory.

 

REQUEST_STATUS column in this below sql query helps to check the status of the LDAP requests to check whether the LDAP request completed or not.

 

Detail LDAP Request SQL Query in Oracle Cloud

select plr.ldap_request_id, plr.request_status, plu.request_status user_request_status, plu.request_type,
ppn.last_name, ppn.first_name, plu.username requested_username, pu.username, plr.request_id, plr.active_flag,
      plr.error_code, plr.error_description,
      plr.last_update_date,plr.request_date, plr.requesting_reference_id
from fusion.per_ldap_requests plr, fusion.per_ldap_users plu, fusion.per_person_names_f ppn, fusion.per_users pu
where trunc(plr.last_update_date) >= trunc(sysdate-:dayOffset)
and plr.requesting_reference_id = ppn.person_id(+)
and plr.requesting_reference_id = pu.person_id(+)
and plr.ldap_request_id = plu.ldap_request_id
and ppn.name_type(+) = ‘GLOBAL’
and sysdate between ppn.effective_start_date(+) and ppn.effective_end_date(+)
–and plr.request_status = ‘COMPLETE’
–and plr.request_id is not null
–and plu.request_type = ‘TERMINATE’
order by plr.last_update_date desc;