VIEW: APPS.HRFV_GRADE_RATES

Source

View Text - Preformatted

SELECT bgrT.name business_group_name ,peo.full_name person_name ,orgT.name organization_name ,jbt.name job_name ,pft.name position_name ,locT.location_code location_name ,gdt.name grade_name ,ppb.name salary_basis ,ppb.rate_basis grade_rate_basis ,rat.name grade_rate_name ,grl.maximum grade_maximum ,grl.mid_value grade_mid_value ,grl.minimum grade_minimum ,grl.value grade_rate_value ,ppb.grade_annualization_factor grade_annualization_factor ,hr_bis.bis_decode_lookup('UNITS',rat.rate_uom) unit_of_measure ,'_DF:PAY:PAY_RATES:rat' ,asg.person_id person_id ,asg.assignment_id assignment_id ,asg.organization_id organization_id ,asg.job_id job_id ,asg.position_id position_id ,asg.grade_id grade_id ,asg.location_id location_id ,grl.grade_rule_id grade_rule_id ,grl.business_group_id business_group_id ,grl.rate_id rate_id FROM pay_grade_rules_f grl ,pay_rates rat ,hr_all_organization_units_tl bgrT ,hr_all_organization_units_tl orgT ,per_grades_tl gdt ,per_jobs_tl jbt ,hr_all_positions_f_tl pft ,hr_locations_all_tl locT ,per_assignments_f asg ,per_all_people_f peo ,per_pay_bases ppb WHERE peo.person_id = asg.person_id AND asg.pay_basis_id = ppb.pay_basis_id (+) AND asg.grade_id = grl.grade_or_spinal_point_id AND asg.grade_id = gdt.grade_id AND gdt.language = userenv('LANG') AND grl.rate_id = rat.rate_id AND bgrT.organization_id = asg.business_group_id AND grl.rate_id = NVL(ppb.rate_id,grl.rate_id) AND asg.organization_id = orgT.organization_id AND asg.job_id = jbt.job_id (+) AND jbt.language(+) = userenv('LANG') AND asg.position_id = pft.position_id (+) AND pft.language(+) = userenv('LANG') AND asg.location_id = locT.location_id (+) AND bgrT.language = userenv('LANG') AND orgT.language = userenv('LANG') AND locT.language (+) = userenv('LANG') AND grl.rate_type = 'G' AND trunc(SYSDATE) BETWEEN grl.effective_start_date AND grl.effective_end_date AND trunc(SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date AND trunc(SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date AND asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id,asg.business_group_id) WITH READ ONLY

View Text - HTML Formatted

SELECT BGRT.NAME BUSINESS_GROUP_NAME
, PEO.FULL_NAME PERSON_NAME
, ORGT.NAME ORGANIZATION_NAME
, JBT.NAME JOB_NAME
, PFT.NAME POSITION_NAME
, LOCT.LOCATION_CODE LOCATION_NAME
, GDT.NAME GRADE_NAME
, PPB.NAME SALARY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, RAT.NAME GRADE_RATE_NAME
, GRL.MAXIMUM GRADE_MAXIMUM
, GRL.MID_VALUE GRADE_MID_VALUE
, GRL.MINIMUM GRADE_MINIMUM
, GRL.VALUE GRADE_RATE_VALUE
, PPB.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, RAT.RATE_UOM) UNIT_OF_MEASURE
, '_DF:PAY:PAY_RATES:RAT'
, ASG.PERSON_ID PERSON_ID
, ASG.ASSIGNMENT_ID ASSIGNMENT_ID
, ASG.ORGANIZATION_ID ORGANIZATION_ID
, ASG.JOB_ID JOB_ID
, ASG.POSITION_ID POSITION_ID
, ASG.GRADE_ID GRADE_ID
, ASG.LOCATION_ID LOCATION_ID
, GRL.GRADE_RULE_ID GRADE_RULE_ID
, GRL.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRL.RATE_ID RATE_ID
FROM PAY_GRADE_RULES_F GRL
, PAY_RATES RAT
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, HR_ALL_ORGANIZATION_UNITS_TL ORGT
, PER_GRADES_TL GDT
, PER_JOBS_TL JBT
, HR_ALL_POSITIONS_F_TL PFT
, HR_LOCATIONS_ALL_TL LOCT
, PER_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PEO
, PER_PAY_BASES PPB
WHERE PEO.PERSON_ID = ASG.PERSON_ID
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASG.GRADE_ID = GRL.GRADE_OR_SPINAL_POINT_ID
AND ASG.GRADE_ID = GDT.GRADE_ID
AND GDT.LANGUAGE = USERENV('LANG')
AND GRL.RATE_ID = RAT.RATE_ID
AND BGRT.ORGANIZATION_ID = ASG.BUSINESS_GROUP_ID
AND GRL.RATE_ID = NVL(PPB.RATE_ID
, GRL.RATE_ID)
AND ASG.ORGANIZATION_ID = ORGT.ORGANIZATION_ID
AND ASG.JOB_ID = JBT.JOB_ID (+)
AND JBT.LANGUAGE(+) = USERENV('LANG')
AND ASG.POSITION_ID = PFT.POSITION_ID (+)
AND PFT.LANGUAGE(+) = USERENV('LANG')
AND ASG.LOCATION_ID = LOCT.LOCATION_ID (+)
AND BGRT.LANGUAGE = USERENV('LANG')
AND ORGT.LANGUAGE = USERENV('LANG')
AND LOCT.LANGUAGE (+) = USERENV('LANG')
AND GRL.RATE_TYPE = 'G'
AND TRUNC(SYSDATE) BETWEEN GRL.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY