VIEW: APPS.GHR_SS_ASG_CHG_DATES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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