View: GHR_SS_ASG_CHG_DATES_V

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

View Text

SELECT ASG_CHG.PERSON_ID
, ASG_CHG.ASSIGNMENT_ID
, ASG_CHG.EFFECTIVE_DATE
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = ASG_CHG.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'GHR_US_ASG_SF52'
AND GPH.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= ASG_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)) ) SF52_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = ASG_CHG.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'GHR_US_ASG_NON_SF52'
AND GPH.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= ASG_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)) ) SFN52_ID
, (SELECT MAX(GPH.PA_HISTORY_ID)
FROM GHR_PA_HISTORY GPH
, PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE AEI.ASSIGNMENT_ID = ASG_CHG.ASSIGNMENT_ID
AND AEI.INFORMATION_TYPE = 'GHR_US_ASG_NTE_DATES'
AND GPH.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)
AND GPH.EFFECTIVE_DATE = (SELECT MAX(GPH2.EFFECTIVE_DATE)
FROM GHR_PA_HISTORY GPH2
WHERE GPH2.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND GPH2.EFFECTIVE_DATE <= ASG_CHG.EFFECTIVE_DATE
AND GPH2.INFORMATION1 = TO_CHAR(AEI.ASSIGNMENT_EXTRA_INFO_ID)) ) NTE_ID
FROM (SELECT ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.EFFECTIVE_START_DATE EFFECTIVE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
WHERE ASG.ASSIGNMENT_TYPE IN ('E'
, 'C') UNION SELECT HIST.PERSON_ID PERSON_ID
, HIST.ASSIGNMENT_ID ASSIGNMENT_ID
, HIST.EFFECTIVE_DATE EFFECTIVE_DATE
FROM GHR_PA_HISTORY HIST
, PER_ASSIGNMENT_EXTRA_INFO AEI
WHERE HIST.TABLE_NAME = 'PER_ASSIGNMENT_EXTRA_INFO'
AND HIST.INFORMATION5 IN ('GHR_US_ASG_SF52'
, 'GHR_US_ASG_NON_SF52'
, 'GHR_US_ASG_NTE_DATES' )
AND AEI.ASSIGNMENT_EXTRA_INFO_ID = HIST.INFORMATION1 UNION SELECT ASG.PERSON_ID PERSON_ID
, EEF.ASSIGNMENT_ID ASSIGNMENT_ID
, EEF.EFFECTIVE_START_DATE EFFECTIVE_DATE
FROM PAY_ELEMENT_ENTRIES_F EEF
, PER_ALL_ASSIGNMENTS_F ASG
, PAY_ELEMENT_TYPES_F ELT
WHERE ASG.ASSIGNMENT_TYPE IN ('E'
, 'C')
AND EEF.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ELT.ELEMENT_TYPE_ID = EEF.ELEMENT_TYPE_ID
AND EEF.EFFECTIVE_START_DATE BETWEEN ELT.EFFECTIVE_START_DATE
AND ELT.EFFECTIVE_END_DATE
AND EEF.EFFECTIVE_START_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND UPPER(PQP_FEDHR_USPAY_INT_UTILS.RETURN_OLD_ELEMENT_NAME(ELT.ELEMENT_NAME
, ASG.BUSINESS_GROUP_ID
, EEF.EFFECTIVE_START_DATE)) = 'WITHIN GRADE INCREASE' ) ASG_CHG

Columns