VIEW: APPS.GHR_SS_EMP_PRSNL_CHG_DATES_V

Source

View Text - Preformatted

SELECT per_chg.person_id, per_chg.effective_date, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_GROUP1' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_grp1_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_SF52' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_sf52_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_SCD_INFORMATION' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_scd_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_PROBATIONS' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_prob_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_UNIFORMED_SERVICES' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_unfrm_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_CONVERSIONS' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_conv_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_SEPARATE_RETIRE' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_retire_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_ETHNICITY_RACE' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_eth_race_id, (select max(gph.pa_history_id) from ghr_pa_history gph, PER_PEOPLE_EXTRA_INFO pei where pei.person_id = per_chg.person_id and pei.information_type = 'GHR_US_PER_GROUP2' and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) and gph.effective_date = (select max(gph2.effective_date) from ghr_pa_history gph2 where gph2.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph2.effective_date <= per_chg.effective_date and gph2.information1 = to_char(pei.person_extra_info_id))) per_grp2_id from (select ppf.person_id person_id, ppf.effective_start_date effective_date from per_all_people_f ppf where nvl(ppf.current_employee_flag,nvl(ppf.current_npw_flag,'N')) = 'Y' union select /*+ ORDERED */ pei.person_id person_id, gph.effective_date effective_date from PER_PEOPLE_EXTRA_INFO pei, ghr_pa_history gph where pei.information_type in ('GHR_US_PER_GROUP1', 'GHR_US_PER_SF52', 'GHR_US_PER_SCD_INFORMATION', 'GHR_US_PER_PROBATIONS', 'GHR_US_PER_UNIFORMED_SERVICES', 'GHR_US_PER_CONVERSIONS', 'GHR_US_PER_SEPARATE_RETIRE','GHR_US_PER_ETHNICITY_RACE', 'GHR_US_PER_GROUP2') and gph.table_name = 'PER_PEOPLE_EXTRA_INFO' and gph.information1 = to_char(pei.person_extra_info_id) ) per_chg

View Text - HTML Formatted

SELECT PER_CHG.PERSON_ID
, PER_CHG.EFFECTIVE_DATE
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_GROUP1'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_GRP1_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_SF52'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_SF52_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_SCD_INFORMATION'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_SCD_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_PROBATIONS'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_PROB_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_UNIFORMED_SERVICES'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_UNFRM_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_CONVERSIONS'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_CONV_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_SEPARATE_RETIRE'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_RETIRE_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_ETHNICITY_RACE'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_ETH_RACE_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_PEOPLE_EXTRA_INFO PEI
WHERE PEI.PERSON_ID = PER_CHG.PERSON_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_PER_GROUP2'
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= PER_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID))) PER_GRP2_ID
FROM (SELECT PPF.PERSON_ID PERSON_ID
, PPF.EFFECTIVE_START_DATE EFFECTIVE_DATE
FROM PER_ALL_PEOPLE_F PPF
WHERE NVL(PPF.CURRENT_EMPLOYEE_FLAG
, NVL(PPF.CURRENT_NPW_FLAG
, 'N')) = 'Y' UNION SELECT /*+ ORDERED */ PEI.PERSON_ID PERSON_ID
, GPH.EFFECTIVE_DATE EFFECTIVE_DATE
FROM PER_PEOPLE_EXTRA_INFO PEI
, GHR_PA_HISTORY GPH
WHERE PEI.INFORMATION_TYPE IN ('GHR_US_PER_GROUP1'
, 'GHR_US_PER_SF52'
, 'GHR_US_PER_SCD_INFORMATION'
, 'GHR_US_PER_PROBATIONS'
, 'GHR_US_PER_UNIFORMED_SERVICES'
, 'GHR_US_PER_CONVERSIONS'
, 'GHR_US_PER_SEPARATE_RETIRE'
, 'GHR_US_PER_ETHNICITY_RACE'
, 'GHR_US_PER_GROUP2')
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID) ) PER_CHG