VIEW: APPS.HRFV_GRADE_RATE_ASGS_BG

Source

View Text - Preformatted

SELECT peo.full_name person_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 ,peo.effective_start_date person_start_date ,peo.effective_end_date person_end_date ,asg.effective_start_date assignment_start_date ,asg.effective_end_date assignment_end_date ,grl.effective_start_date grade_rule_start_date ,grl.effective_end_date grade_rule_end_date ,'_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 ,asg.business_group_id business_group_id ,grl.rate_id rate_id ,ppb.pay_basis_id pay_basis_id FROM pay_grade_rules_f grl ,pay_rates rat ,per_grades_tl gdt ,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 grl.rate_id = NVL(ppb.rate_id, grl.rate_id) AND grl.rate_type = 'G' AND asg.assignment_type = 'E' AND ( (asg.effective_start_date <= peo.effective_start_date AND asg.effective_end_date >= peo.effective_start_date) OR (asg.effective_start_date <= peo.effective_end_date AND asg.effective_end_date >= peo.effective_end_date) OR (asg.effective_start_date >= peo.effective_start_date AND asg.effective_end_date <= peo.effective_end_date) ) AND ( (grl.effective_start_date <= asg.effective_start_date AND grl.effective_end_date >= asg.effective_start_date) OR (grl.effective_start_date <= asg.effective_end_date AND grl.effective_end_date >= asg.effective_end_date) OR (grl.effective_start_date >= asg.effective_start_date AND grl.effective_end_date <= asg.effective_end_date) ) AND asg.business_group_id = NVL(hr_bis.get_sec_profile_bg_id, asg.business_group_id) UNION ALL SELECT peo.full_name person_name ,gdt.name grade_name ,ppb.name salary_basis ,ppb.rate_basis grade_rate_basis ,to_char(null) grade_rate_name ,to_char(null) grade_maximum ,to_char(null) grade_mid_value ,to_char(null) grade_minimum ,to_char(null) grade_rate_value ,ppb.grade_annualization_factor grade_annualization_factor ,to_char(null) unit_of_measure ,peo.effective_start_date person_start_date ,peo.effective_end_date person_end_date ,asg.effective_start_date assignment_start_date ,asg.effective_end_date assignment_end_date ,asg.effective_start_date grade_rule_start_date ,asg.effective_end_date grade_rule_end_date ,'_DF:_DUMMY: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 ,to_number(null) grade_rule_id ,asg.business_group_id business_group_id ,to_number(null) rate_id ,ppb.pay_basis_id pay_basis_id FROM per_grades_tl gdt ,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 = gdt.grade_id AND gdt.language = userenv('LANG') AND NOT EXISTS (select 1 from pay_grade_rules_f grl where grl.grade_or_spinal_point_id = asg.grade_id and asg.effective_start_date between grl.effective_start_date and grl.effective_end_date) AND asg.assignment_type = 'E' AND ( (asg.effective_start_date <= peo.effective_start_date AND asg.effective_end_date >= peo.effective_start_date) OR (asg.effective_start_date <= peo.effective_end_date AND asg.effective_end_date >= peo.effective_end_date) OR (asg.effective_start_date >= peo.effective_start_date AND asg.effective_end_date <= peo.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 PEO.FULL_NAME PERSON_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
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, GRL.EFFECTIVE_START_DATE GRADE_RULE_START_DATE
, GRL.EFFECTIVE_END_DATE GRADE_RULE_END_DATE
, '_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
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, GRL.RATE_ID RATE_ID
, PPB.PAY_BASIS_ID PAY_BASIS_ID
FROM PAY_GRADE_RULES_F GRL
, PAY_RATES RAT
, PER_GRADES_TL GDT
, 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 GRL.RATE_ID = NVL(PPB.RATE_ID
, GRL.RATE_ID)
AND GRL.RATE_TYPE = 'G'
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND ( (GRL.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_START_DATE) OR (GRL.EFFECTIVE_START_DATE <= ASG.EFFECTIVE_END_DATE
AND GRL.EFFECTIVE_END_DATE >= ASG.EFFECTIVE_END_DATE) OR (GRL.EFFECTIVE_START_DATE >= ASG.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE <= ASG.EFFECTIVE_END_DATE) )
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) UNION ALL SELECT PEO.FULL_NAME PERSON_NAME
, GDT.NAME GRADE_NAME
, PPB.NAME SALARY_BASIS
, PPB.RATE_BASIS GRADE_RATE_BASIS
, TO_CHAR(NULL) GRADE_RATE_NAME
, TO_CHAR(NULL) GRADE_MAXIMUM
, TO_CHAR(NULL) GRADE_MID_VALUE
, TO_CHAR(NULL) GRADE_MINIMUM
, TO_CHAR(NULL) GRADE_RATE_VALUE
, PPB.GRADE_ANNUALIZATION_FACTOR GRADE_ANNUALIZATION_FACTOR
, TO_CHAR(NULL) UNIT_OF_MEASURE
, PEO.EFFECTIVE_START_DATE PERSON_START_DATE
, PEO.EFFECTIVE_END_DATE PERSON_END_DATE
, ASG.EFFECTIVE_START_DATE ASSIGNMENT_START_DATE
, ASG.EFFECTIVE_END_DATE ASSIGNMENT_END_DATE
, ASG.EFFECTIVE_START_DATE GRADE_RULE_START_DATE
, ASG.EFFECTIVE_END_DATE GRADE_RULE_END_DATE
, '_DF:_DUMMY: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
, TO_NUMBER(NULL) GRADE_RULE_ID
, ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, TO_NUMBER(NULL) RATE_ID
, PPB.PAY_BASIS_ID PAY_BASIS_ID
FROM PER_GRADES_TL GDT
, 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 = GDT.GRADE_ID
AND GDT.LANGUAGE = USERENV('LANG')
AND NOT EXISTS (SELECT 1
FROM PAY_GRADE_RULES_F GRL
WHERE GRL.GRADE_OR_SPINAL_POINT_ID = ASG.GRADE_ID
AND ASG.EFFECTIVE_START_DATE BETWEEN GRL.EFFECTIVE_START_DATE
AND GRL.EFFECTIVE_END_DATE)
AND ASG.ASSIGNMENT_TYPE = 'E'
AND ( (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_START_DATE) OR (ASG.EFFECTIVE_START_DATE <= PEO.EFFECTIVE_END_DATE
AND ASG.EFFECTIVE_END_DATE >= PEO.EFFECTIVE_END_DATE) OR (ASG.EFFECTIVE_START_DATE >= PEO.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE <= PEO.EFFECTIVE_END_DATE) )
AND ASG.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, ASG.BUSINESS_GROUP_ID) WITH READ ONLY