VIEW: APPS.HR_PAY_INTERFACE_GRADE_RATES_V

Source

View Text - Preformatted

SELECT
  pp.full_name
, pp.employee_number
, asg.assignment_number
, bgr.name
, org.name
, asg.normal_hours
, prl.payroll_name
, ptp.number_per_fiscal_year
, grd.name
, rat.name
, rat.rate_uom
, grr.value
, bgr.organization_id
, org.organization_id
, asg.assignment_id
, grd.grade_id
, rat.rate_id
, pp.person_id
, pp.attribute1
, pp.attribute2
, pp.attribute3
, asg.ass_attribute1
, asg.ass_attribute2
, asg.ass_attribute3
, grd.attribute1
, grd.attribute2
, grd.attribute3
, GREATEST (
  GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD')),
           asg.effective_start_date)
, GREATEST(NVL(grr.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,grr.effective_start_date)
  )
, GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD')),
           asg.effective_start_date)
, GREATEST(NVL(grr.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,grr.effective_start_date)
FROM
  per_all_people_f          pp
, per_all_assignments_f     asg
, hr_all_organization_units bgr
, hr_all_organization_units org
, pay_all_payrolls_f        prl
, per_time_period_types     ptp
, per_grades                grd
, pay_grade_rules_f         grr
, pay_rates                 rat
WHERE
    pp.person_id = asg.person_id
and asg.business_group_id + 0 = bgr.organization_id
and asg.organization_id =  org.organization_id
and asg.payroll_id = prl.payroll_id
and prl.period_type = ptp.period_type
and asg.grade_id = grd.grade_id
and grd.grade_id = grr.grade_or_spinal_point_id
and grr.rate_type = 'G'
and grr.rate_id  = rat.rate_id
and asg.effective_start_date BETWEEN
    prl.effective_start_date and prl.effective_end_date
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN
    pp.effective_start_date AND pp.effective_end_date
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN
    asg.effective_start_date AND asg.effective_end_date
and grd.date_from = (SELECT max(grd2.date_from)
		       FROM per_grades grd2
		      WHERE grd2.grade_id = grd.grade_id
                        AND grd.date_from <=
			  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE )
and grr.effective_start_date = (SELECT max(grr2.effective_start_date)
                         FROM pay_grade_rules_f grr2
                         WHERE  grr2.grade_rule_id = grr.grade_rule_id
                         and grr2.effective_start_date <=
			  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE )

View Text - HTML Formatted

SELECT PP.FULL_NAME
, PP.EMPLOYEE_NUMBER
, ASG.ASSIGNMENT_NUMBER
, BGR.NAME
, ORG.NAME
, ASG.NORMAL_HOURS
, PRL.PAYROLL_NAME
, PTP.NUMBER_PER_FISCAL_YEAR
, GRD.NAME
, RAT.NAME
, RAT.RATE_UOM
, GRR.VALUE
, BGR.ORGANIZATION_ID
, ORG.ORGANIZATION_ID
, ASG.ASSIGNMENT_ID
, GRD.GRADE_ID
, RAT.RATE_ID
, PP.PERSON_ID
, PP.ATTRIBUTE1
, PP.ATTRIBUTE2
, PP.ATTRIBUTE3
, ASG.ASS_ATTRIBUTE1
, ASG.ASS_ATTRIBUTE2
, ASG.ASS_ATTRIBUTE3
, GRD.ATTRIBUTE1
, GRD.ATTRIBUTE2
, GRD.ATTRIBUTE3
, GREATEST ( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(GRR.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GRR.EFFECTIVE_START_DATE) )
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(GRR.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GRR.EFFECTIVE_START_DATE) FROM PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F ASG
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, PAY_ALL_PAYROLLS_F PRL
, PER_TIME_PERIOD_TYPES PTP
, PER_GRADES GRD
, PAY_GRADE_RULES_F GRR
, PAY_RATES RAT WHERE PP.PERSON_ID = ASG.PERSON_ID AND ASG.BUSINESS_GROUP_ID + 0 = BGR.ORGANIZATION_ID AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID AND ASG.PAYROLL_ID = PRL.PAYROLL_ID AND PRL.PERIOD_TYPE = PTP.PERIOD_TYPE AND ASG.GRADE_ID = GRD.GRADE_ID AND GRD.GRADE_ID = GRR.GRADE_OR_SPINAL_POINT_ID AND GRR.RATE_TYPE = 'G' AND GRR.RATE_ID = RAT.RATE_ID AND ASG.EFFECTIVE_START_DATE BETWEEN PRL.EFFECTIVE_START_DATE
AND PRL.EFFECTIVE_END_DATE AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE AND GRD.DATE_FROM = (SELECT MAX(GRD2.DATE_FROM)
FROM PER_GRADES GRD2
WHERE GRD2.GRADE_ID = GRD.GRADE_ID
AND GRD.DATE_FROM <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE ) AND GRR.EFFECTIVE_START_DATE = (SELECT MAX(GRR2.EFFECTIVE_START_DATE)
FROM PAY_GRADE_RULES_F GRR2
WHERE GRR2.GRADE_RULE_ID = GRR.GRADE_RULE_ID
AND GRR2.EFFECTIVE_START_DATE <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE )