View: GHR_SS_POS_CHG_DATES_V

Product:	GHR - US Federal Human Resources
Description:	This view is used by Fed SS Views
Implementation/DBA Data:	APPS.GHR_SS_POS_CHG_DATES_V

View Text

SELECT PERSON_ID
, ASSIGNMENT_ID
, ASSIGNMENT_NUMBER
, PRIMARY_FLAG
, POSITION_ID
, EFFECTIVE_DATE
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_POSITION_EXTRA_INFO PEI
WHERE PEI.POSITION_ID = POS_CHG.POSITION_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_POS_GRP1'
AND GPH.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= POS_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID))) POS_GRP1_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_POSITION_EXTRA_INFO PEI
WHERE PEI.POSITION_ID = POS_CHG.POSITION_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_POS_GRP2'
AND GPH.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= POS_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID))) POS_GRP2_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_POSITION_EXTRA_INFO PEI
WHERE PEI.POSITION_ID = POS_CHG.POSITION_ID
AND PEI.INFORMATION_TYPE = 'GHR_US_POS_VALID_GRADE'
AND GPH.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= POS_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(PEI.POSITION_EXTRA_INFO_ID))) POS_VALID_GRADE_ID
FROM ( SELECT PAF.PERSON_ID PERSON_ID
, PAF.ASSIGNMENT_ID ASSIGNMENT_ID
, PAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAF.PRIMARY_FLAG PRIMARY_FLAG
, PAF.POSITION_ID POSITION_ID
, PAF.EFFECTIVE_START_DATE EFFECTIVE_DATE
FROM PER_ASSIGNMENTS_F2 PAF
WHERE PAF.POSITION_ID IS NOT NULL
AND PAF.POSITION_ID NOT IN (SELECT NVL(POSITION_ID
, 0)
FROM PER_ASSIGNMENTS_F2 ASG
WHERE ASG.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF.EFFECTIVE_START_DATE - 1 BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE )
AND PAF.ASSIGNMENT_TYPE IN ('E'
, 'C') UNION SELECT ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.PRIMARY_FLAG PRIMARY_FLAG
, POS.POSITION_ID
, POS.EFFECTIVE_START_DATE EFFECTIVE_DATE
FROM HR_ALL_POSITIONS_F POS
, PER_ASSIGNMENTS_F2 ASG
WHERE ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND POS.POSITION_ID = ASG.POSITION_ID
AND POS.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE UNION SELECT /*+ ORDERED */ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASG.PRIMARY_FLAG PRIMARY_FLAG
, ASG.POSITION_ID POSITION_ID
, HIST.EFFECTIVE_DATE EFFECTIVE_DATE
FROM PER_ASSIGNMENTS_F2 ASG
, PER_POSITION_EXTRA_INFO POEI
, GHR_PA_HISTORY HIST
WHERE ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND POEI.POSITION_ID = ASG.POSITION_ID
AND POEI.INFORMATION_TYPE IN ('GHR_US_POS_GRP1'
, 'GHR_US_POS_GRP2'
, 'GHR_US_POS_VALID_GRADE')
AND HIST.TABLE_NAME = 'PER_POSITION_EXTRA_INFO'
AND TO_CHAR(POEI.POSITION_EXTRA_INFO_ID) = HIST.INFORMATION1
AND HIST.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE ) POS_CHG

Columns