Sunday, 11 May 2025

Query to extract the user details(Oracle Fusion)

 SELECT 

 pur.username USER

,papf.person_number EMP_NUM

,ppnf.first_name EMP_FIRST_NAME

,ppnf.last_name EMP_LAST_NAME 

,DECODE(SUSPENDED,'Y', 'Inactive User','N', 'Active User') USER_STATUS

,ASSIGNMENT_STATUS_TYPE EMP_STATUS

FROM 

 per_users pur 

,per_person_names_f ppnf

,per_all_people_f papf 

,per_all_assignments_f paaf

WHERE pur.person_id = ppnf.person_id 

AND pur.person_id = papf.person_id

AND paaf.person_id = papf.person_id 

AND name_type = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_Date AND ppnf.effective_end_Date

AND TRUNC(SYSDATE) BETWEEN papf.effective_start_Date AND papf.effective_end_Date

AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_Date AND paaf.effective_end_Date

No comments:

Post a Comment