View: GHR_SS_PRSNL_CHG_DATES_V

Product:	GHR - US Federal Human Resources
Description:
Implementation/DBA Data:	APPS.GHR_SS_PRSNL_CHG_DATES_V

View Text

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
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' )
AND GPH.TABLE_NAME = 'PER_PEOPLE_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.PERSON_EXTRA_INFO_ID) ) PER_CHG

Columns