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