WITH EXT_ID_EMP as
(select peai.person_id
,peai.EXT_IDENTIFIER_NUMBER EBS_EMP_NUMBER
,peai1.EXT_IDENTIFIER_NUMBER EBS_PERSON_ID
from PER_EXT_APP_IDENTIFIERS peai
,PER_EXT_APP_IDENTIFIERS peai1
where 1=1
and trunc(sysdate) between peai.DATE_FROM and NVL(peai.DATE_TO,to_date('31-12-
4712','dd-mm-yyyy'))
and peai.EXT_IDENTIFIER_TYPE = 'PRFT_EBS'
and peai.person_id = peai1.person_id
and peai1.EXT_IDENTIFIER_TYPE = 'PRFT_EBS_PERSON_ID'
AND peai1.DATE_FROM = peai.DATE_FROM
and trunc(sysdate) between peai1.DATE_FROM and NVL(peai1.DATE_TO,to_date('31-12-
4712','dd-mm-yyyy'))
and peai.DATE_FROM = (select max(pei2.DATE_FROM) from PER_EXT_APP_IDENTIFIERS pei2
where pei2.person_id = peai.person_id and peai.EXT_IDENTIFIER_TYPE =
pei2.EXT_IDENTIFIER_TYPE)
)
,EXT_ID_NPW as
(select peai.person_id
,peai.EXT_IDENTIFIER_NUMBER EBS_NPW_NUMBER
,peai1.EXT_IDENTIFIER_NUMBER EBS_NPW_PERSON_ID
from PER_EXT_APP_IDENTIFIERS peai
,PER_EXT_APP_IDENTIFIERS peai1
where 1=1
and trunc(sysdate) between peai.DATE_FROM and NVL(peai.DATE_TO,to_date('31-12-
4712','dd-mm-yyyy'))
and peai.EXT_IDENTIFIER_TYPE = 'PRFT_EBS_NPW'
and peai.person_id = peai1.person_id
and peai1.EXT_IDENTIFIER_TYPE = 'PRFT_EBS_PERSON_ID'
AND peai1.DATE_FROM = peai.DATE_FROM
and trunc(sysdate) between peai1.DATE_FROM and NVL(peai1.DATE_TO,to_date('31-12-
4712','dd-mm-yyyy'))
and peai.DATE_FROM = (select max(pei2.DATE_FROM) from PER_EXT_APP_IDENTIFIERS pei2
where pei2.person_id = peai.person_id and peai.EXT_IDENTIFIER_TYPE =
pei2.EXT_IDENTIFIER_TYPE)
)
,RBS_ELEMENTS as
(
select
pre.person_id
,pre.RBS_ELEMENT_ID
,pre.ALIAS RBS_ELEMENT_NAME
,pre.rbs_version_id
,prentl.NAME rbs_ele_names_tl
,prvtl.NAME rbs_version_name
,restl.name rbs_resource_type_name
,resftl.name rbs_res_format_type_name
,exptl.EXPENDITURE_TYPE_NAME
,rvb.RBS_HEADER_ID
,rvb.STATUS_CODE VERSION_STATUS_CODE
,RHB.RBS_TYPE
,RHB.CENTRALLY_CONTROL_FLAG
,rHTL.NAME RBS_HEADER_NAME
FROM
PJF_RBS_ELEMENTS pre
,PJF_RBS_ELEMENT_NAMES_TL prentl
,PJF_RBS_VERSIONS_TL prvtl
,PJF_RES_TYPES_TL restl
,PJF_RES_FORMATS_TL resftl
,PJF_EXP_TYPES_TL exptl
,PJF_RBS_VERSIONS_B rvb
,PJF_RBS_HEADERS_B RHB
,PJF_RBS_HEADERS_TL RHTL
Where 1=1
AND PRE.RESOURCE_CLASS_CODE = 'PEOPLE'
and pre.RBS_ELEMENT_NAME_ID = prentl.RBS_ELEMENT_NAME_ID
AND prentl.LANGUAGE=USERENV('LANG')
and pre.RBS_VERSION_ID = prvtl.RBS_VERSION_ID
AND prvtl.LANGUAGE=USERENV('LANG')
and pre.RESOURCE_TYPE_ID = restl.RES_TYPE_ID
AND restl.LANGUAGE=USERENV('LANG')
and pre.RES_FORMAT_ID = resftl.RES_FORMAT_ID
AND resftl.LANGUAGE=USERENV('LANG')
and pre.EXPENDITURE_TYPE_ID = exptl.EXPENDITURE_TYPE_ID
AND exptl.LANGUAGE = USERENV('LANG')
and pre.RBS_VERSION_ID = rvb.RBS_VERSION_ID
and rvb.RBS_HEADER_ID = rHb.RBS_HEADER_ID
and rHb.RBS_HEADER_ID = rHTL.RBS_HEADER_ID
AND rHTL.LANGUAGE=USERENV('LANG')
AND pre.person_id is not null
--and pre.person_id = 300000060871967
-- criteria provided by Jeff Bisson
and pre.EXPENDITURE_TYPE_ID is not null
and (pre.rbs_version_id is null or pre.rbs_version_id in (select distinct
pasg.RBS_VERSION_ID
from PJF_PROJECTS_ALL_B PP
,PJF_PR
OJECT_STATUSES_TL ppst
,PJF_RBS_PRJ_ASSIGNMENTS pasg
where 1=1
and pp.PROJECT_STATUS_CODE =
ppst.PROJECT_STATUS_CODE
AND ppst.LANGUAGE=USERENV('LANG')
and ppst.PROJECT_STATUS_NAME in
('CONV-Approved','Approved')
and PP.PROJECT_ID =
pasg.PROJECT_ID)
)
-- Criteria provided by Julie
and rHTL.NAME = 'Billable Planning Resources'
,complete_data as
(
SELECT papf.person_id
,papf.person_number
,ppnf.first_name
,ppnf.last_name
,ppnf.display_name
,paam.business_unit_id business_unit_id
,paam.organization_id Department_id
,ppos.DATE_START Hire_Date
,ppos.ACTUAL_TERMINATION_DATE Termination_Date
,email.email_address
,ppei.PEI_INFORMATION_DATE1 Adjusted_Service_Date
,GREATEST(ppos.LAST_UPDATE_DATE,paam.LAST_UPDATE_DATE,email.LAST_UPDATE_DATE,ppnf.L
AST_UPDATE_DATE) Last_Modified_Date
,pjftl.NAME job_title
,ppos.PERIOD_TYPE employee_type
,EXT_ID_EMP.EBS_EMP_NUMBER
,EXT_ID_EMP.EBS_PERSON_ID
,EXT_ID_NPW.EBS_NPW_NUMBER
,EXT_ID_NPW.EBS_NPW_PERSON_ID
,(NVL((select 'Y' from dual
where exists (select 1 from PJF_RBS_ELEMENTS PRE
,PJF_RBS_VERSIONS_B PRVB
where 1=1
AND PRE.RESOURCE_CLASS_CODE = 'PEOPLE'
AND PRE.RBS_VERSION_ID = PRVB.RBS_VERSION_ID
AND PRVB.STATUS_CODE = 'FROZEN'
AND PRVB.CURRENT_REPORTING_FLAG = 'Y'
and pre.person_id = ppos.person_id)),'N')) BILLABLE_FLAG
,RBS_ELEMENTS.RBS_ELEMENT_ID
,RBS_ELEMENTS.RBS_ELEMENT_NAME
,RBS_ELEMENTS.rbs_ele_names_tl
,RBS_ELEMENTS.rbs_version_name
,RBS_ELEMENTS.rbs_resource_type_name
,RBS_ELEMENTS.rbs_res_format_type_name
,RBS_ELEMENTS.EXPENDITURE_TYPE_NAME
,RBS_ELEMENTS.RBS_HEADER_ID
,RBS_ELEMENTS.VERSION_STATUS_CODE
,RBS_ELEMENTS.RBS_TYPE
,RBS_ELEMENTS.CENTRALLY_CONTROL_FLAG
,RBS_ELEMENTS.RBS_HEADER_NAME
FROM
per_all_people_f papf
,per_person_names_f ppnf
,per_periods_of_service ppos
,per_all_assignments_m paam
,per_email_addresses email
,PER_PEOPLE_EXTRA_INFO_F ppei
,PER_JOBS_F_TL pjftl
,EXT_ID_EMP
,EXT_ID_NPW
,RBS_ELEMENTS
where 1=1
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
and papf.person_id = ppos.person_id
and ppos.PERIOD_TYPE in ('C','E')
and ppos.date_start = (select max(ppos1.date_start)
from per_periods_of_service ppos1
where ppos.person_id = ppos1.person_id
and ppos1.period_type in ('C','E' ))
and ppos.PERIOD_OF_SERVICE_ID = paam.PERIOD_OF_SERVICE_ID
and paam.effective_latest_change = 'Y'
and paam.PRIMARY_FLAG = 'Y'
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and papf.person_id = email.person_id (+)
and email.email_type (+) = 'W1'
and papf.person_id = ppei.person_id (+)
and ppei.PEI_INFORMATION_CATEGORY (+) = 'PRFT_ADJ_SDATE'
and trunc(sysdate) between ppei.effective_start_date (+) and
ppei.effective_end_date (+)
and paam.JOB_ID = pjftl.JOB_ID (+)
and pjftl.language(+) =USERENV('LANG')
and trunc(sysdate) between pjftl.effective_start_date (+) and
pjftl.effective_end_date (+)
AND
GREATEST(ppos.LAST_UPDATE_DATE,paam.LAST_UPDATE_DATE,email.LAST_UPDATE_DATE,ppnf.LA
ST_UPDATE_DATE) >=
NVL(:LastUpdateDateFrom,GREATEST(ppos.LAST_UPDATE_DATE,paam.LAST_UPDATE_DATE,email.
LAST_UPDATE_DATE,ppnf.LAST_UPDATE_DATE))
and papf.person_id = EXT_ID_EMP.person_id(+)
and papf.person_id = EXT_ID_NPW.person_id(+)
and ppos.person_id = rbs_elements.person_id(+)
)
select
person_id
,person_number
,first_name
,last_name
,display_name
,business_unit_id
,Department_id
,Hire_Date
,Termination_Date
,email_address
,Adjusted_Service_Date
,Last_Modified_Date
,job_title
,employee_type
,EBS_EMP_NUMBER
,EBS_PERSON_ID
,EBS_NPW_NUMBER
,EBS_NPW_PERSON_ID
,BILLABLE_FLAG
,RBS_ELEMENT_ID
,RBS_ELEMENT_NAME
,rbs_ele_names_tl
,rbs_version_name
,rbs_resource_type_name
,rbs_res_format_type_name
,EXPENDITURE_TYPE_NAME
,RBS_HEADER_ID
,VERSION_STATUS_CODE
,RBS_TYPE
,CENTRALLY_CONTROL_FLAG
,RBS_HEADER_NAME
FROM
complete_data
where 1=1
--and complete_data.person_id in (select cd1.person_id from complete_data cd1 group
by cd1.person_id having count(*) > 1)
order by person_number
