eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.GHR_SS_POS_CHG_DATES_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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