VIEW: APPS.HRFV_GRD_STEP_PLACEMNT_HISTORY

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,per.full_name person_name ,per.employee_number employee_number ,pa.assignment_number assignment_number ,ps.name pay_scale_name ,gdt.name grade_name ,COUNT(*) grade_step ,psp.spinal_point grade_point ,hr_bis.bis_decode_lookup('PLACEMENT_REASON', pspp.reason) placement_reason ,pspp.effective_start_date effective_start_date ,hr_discoverer.check_end_date(pspp.effective_end_date) effective_end_date ,pa.assignment_id assignment_id ,pa.business_group_id business_group_id ,pa.grade_id grade_id ,psp.spinal_point_id pay_scale_id ,per.person_id person_id ,psps.step_id step_id FROM hr_all_organization_units_tl bgrT ,per_grades_tl gdt ,per_parent_spines ps ,per_spinal_points psp ,per_people_x per ,per_assignments_f pa ,per_spinal_point_steps_f psps ,per_spinal_point_steps_f psps2 ,per_spinal_point_placements_f pspp WHERE pspp.assignment_id = pa.assignment_id AND ps.parent_spine_id = psp.parent_spine_id AND pa.person_id = per.person_id AND gdt.grade_id = pa.grade_id AND gdt.language = userenv('LANG') AND pspp.step_id = psps.step_id AND psps.spinal_point_id = psp.spinal_point_id AND psps.grade_spine_id = psps2.grade_spine_id AND psps.sequence >= psps2.sequence AND pa.assignment_type = 'E' AND pspp.effective_start_date BETWEEN pa.effective_start_date AND pa.effective_end_date AND pa.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,pa.business_group_id) AND pa.business_group_id = bgrT.organization_id AND bgrT.language = userenv('LANG') GROUP BY bgrT.name ,per.full_name ,per.employee_number ,pa.assignment_number ,ps.name ,gdt.name ,psp.spinal_point ,pspp.reason ,pspp.effective_start_date ,pspp.effective_end_date ,pa.assignment_id ,pa.business_group_id ,pa.grade_id ,psp.spinal_point_id ,per.person_id ,psps.step_id WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PER.FULL_NAME PERSON_NAME
, PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PS.NAME PAY_SCALE_NAME
, GDT.NAME GRADE_NAME
, COUNT(*) GRADE_STEP
, PSP.SPINAL_POINT GRADE_POINT
, HR_BIS.BIS_DECODE_LOOKUP('PLACEMENT_REASON'
, PSPP.REASON) PLACEMENT_REASON
, PSPP.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, HR_DISCOVERER.CHECK_END_DATE(PSPP.EFFECTIVE_END_DATE) EFFECTIVE_END_DATE
, PA.ASSIGNMENT_ID ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PA.GRADE_ID GRADE_ID
, PSP.SPINAL_POINT_ID PAY_SCALE_ID
, PER.PERSON_ID PERSON_ID
, PSPS.STEP_ID STEP_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PER_GRADES_TL GDT
, PER_PARENT_SPINES PS
, PER_SPINAL_POINTS PSP
, PER_PEOPLE_X PER
, PER_ASSIGNMENTS_F PA
, PER_SPINAL_POINT_STEPS_F PSPS
, PER_SPINAL_POINT_STEPS_F PSPS2
, PER_SPINAL_POINT_PLACEMENTS_F PSPP
WHERE PSPP.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PS.PARENT_SPINE_ID = PSP.PARENT_SPINE_ID
AND PA.PERSON_ID = PER.PERSON_ID
AND GDT.GRADE_ID = PA.GRADE_ID
AND GDT.LANGUAGE = USERENV('LANG')
AND PSPP.STEP_ID = PSPS.STEP_ID
AND PSPS.SPINAL_POINT_ID = PSP.SPINAL_POINT_ID
AND PSPS.GRADE_SPINE_ID = PSPS2.GRADE_SPINE_ID
AND PSPS.SEQUENCE >= PSPS2.SEQUENCE
AND PA.ASSIGNMENT_TYPE = 'E'
AND PSPP.EFFECTIVE_START_DATE BETWEEN PA.EFFECTIVE_START_DATE
AND PA.EFFECTIVE_END_DATE
AND PA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PA.BUSINESS_GROUP_ID)
AND PA.BUSINESS_GROUP_ID = BGRT.ORGANIZATION_ID
AND BGRT.LANGUAGE = USERENV('LANG') GROUP BY BGRT.NAME
, PER.FULL_NAME
, PER.EMPLOYEE_NUMBER
, PA.ASSIGNMENT_NUMBER
, PS.NAME
, GDT.NAME
, PSP.SPINAL_POINT
, PSPP.REASON
, PSPP.EFFECTIVE_START_DATE
, PSPP.EFFECTIVE_END_DATE
, PA.ASSIGNMENT_ID
, PA.BUSINESS_GROUP_ID
, PA.GRADE_ID
, PSP.SPINAL_POINT_ID
, PER.PERSON_ID
, PSPS.STEP_ID WITH READ ONLY