Oracle Fusion - HCM - User and Manager Details Query

 /*

************************************************************************************************************************************

Seq            Date            Version            Developer            Requested By            Comments                

--------------------------------------------------------------------------------------------------------------

1            04-NOV-2020        1.0               Chetan Rajput        Aniket                    Newly Developed            

************************************************************************************************************************************

*/

select 

'KEY' AS "KEY",

ROWNUM SR_NO,

X.*

FROM

(

SELECT

PU.PERSON_ID EMP_PERSON_ID, 

PU.USERNAME EMP_USERNAME,

ppnf.EFFECTIVE_START_DATE EMP_START_DATE, 

ppnf.EFFECTIVE_END_DATE EMP_END_DATE, 

ppnf.FULL_NAME EMP_FULL_NAME,

ppnf.TITLE,

ppnf.FIRST_NAME,

ppnf.LAST_NAME,

email.email_address USER_EMAIL,

PRDV.ROLE_NAME EMP_ROLE_NAME,

haouf.NAME  EMP_RIG_NAME,

haouf.ORGANIZATION_ID  EMP_RIG_ID,

PASF.MANAGER_ID,

T_MANAGER.USERNAME MANAGER_USERNAME,

T_MANAGER.PERSON_ID MANAGER_PERSON_ID,

T_MANAGER.ROLE_NAME MANAGER_ROLE_NAME,

T_MANAGER.RIG_NAME MANAGER_RIG_NAME,

T_MANAGER.RIG_ID MANAGER_RIG_ID,

T_MANAGER.EFFECTIVE_START_DATE MANAGER_START_DATE,

T_MANAGER.EFFECTIVE_END_DATE MANAGER_END_DATE,

T_MANAGER.FULL_NAME MANAGER_FULL_NAME

/*

CASE

                    WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       PU.LAST_UPDATE_DATE

                    WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       ppnf.LAST_UPDATE_DATE

                    WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       FURDA.LAST_UPDATE_DATE

                    WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PUR.LAST_UPDATE_DATE

                    WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PRDV.LAST_UPDATE_DATE

                    WHEN (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PASF.LAST_UPDATE_DATE  

WHEN (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       T_MANAGER.LAST_UPDATE_DATE

                 END

                    AS LAST_UPDATE_DATE

*/

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV,

PER_ASSIGNMENT_SUPERVISORS_F_V PASF,

per_email_addresses email,

PER_ALL_PEOPLE_F B,

(select 

PU.PERSON_ID, 

PU.USERNAME,

ppnf.EFFECTIVE_START_DATE, 

ppnf.EFFECTIVE_END_DATE, 

ppnf.FULL_NAME,

PRDV.ROLE_NAME,

haouf.NAME RIG_NAME,

haouf.ORGANIZATION_ID RIG_ID

/*,

CASE

                    WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       PU.LAST_UPDATE_DATE

                    WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       ppnf.LAST_UPDATE_DATE

                    WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       FURDA.LAST_UPDATE_DATE

                    WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PUR.LAST_UPDATE_DATE

                    WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PRDV.LAST_UPDATE_DATE

                   

                 END

                    AS LAST_UPDATE_DATE

*/

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV

where 

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

) T_MANAGER

where 

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

AND ppnf.PERSON_ID=PASF.person_id(+) 

AND PASF.MANAGER_ID=T_MANAGER.PERSON_ID(+)

AND TRUNC (SYSDATE) BETWEEN TRUNC (b.EFFECTIVE_START_DATE)

                               AND TRUNC (b.EFFECTIVE_END_DATE)

AND b.person_id = ppnf.person_id

AND email.person_id(+) = b.person_id

AND email.email_address_id(+) = b.primary_email_id

AND TRUNC (SYSDATE) BETWEEN NVL (email.date_from(+), SYSDATE)

                           AND NVL (email.date_to(+), SYSDATE)

AND email.email_type = 'W1'      

/*

AND (   (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                      OR (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                      OR (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

  OR (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)    

   )

*/

-- AND PU.USERNAME='5140'

ORDER BY PU.USERNAME

)X