In this session we are going to see how to fetch Oracle Cloud HCM Payroll Run Results using SQL.
SQL Text:
with people_tbl_vw as (
select person_id
,person_number
from per_all_people_f papf
where 1 = 1
and sysdate between papf.effective_start_date and papf.effective_end_date
and (coalesce(null,:p_pers_num) is null or papf.person_id in (:p_pers_num))
)
,pay_period_tbl_vw as (
select ptp.time_period_id
,ptp.payroll_id
,ptp.end_date
,ptp.regular_earn_date
from pay_time_periods ptp
where 1 = 1
and (ptp.end_date between :p_st_dt and :p_ed_dt
or ptp.start_date between :p_st_dt and :p_ed_dt)
)
,pay_bal_tbl_vw1 as (
select papf.person_number
,trim(replace(replace(replace(pet.element_name,'Earnings',''),'Results',''),'Result','')) element_name
,pecv.classification_name
,loc.location_name
,bu.name business_unit
,job_fam.job_family_name
,to_char(ptp.default_paydate,'yyyy/mm/dd') check_date
,to_char(ptp.start_date,'yyyy/mm/dd') prd_st_dt
,to_char(ptp.end_date,'yyyy/mm/dd') prd_ed_dt
,case when pivf.base_name = 'Hours Calculated' then 'Hours'
else 'Amount' end base_name
,prrv.result_value
,ppnf.full_name person_name
,secondary_classification_flag
,case when pecv.classification_name in ('Standard Earnings','Direct Payments','Absences','Taxable Benefits')
then 'Earnings'
when pecv.classification_name in ('Tax Deductions')
then 'Taxes'
when pecv.classification_name in ('Pre-Statutory Deductions','Voluntary Deductions')
then 'Other Deductions'
when pecv.classification_name in ('Employer Charges')
then 'Company Deductions' end class1
from people_tbl_vw papf
,per_all_assignments_m paam
,per_periods_of_service ppos
,pay_payroll_actions ppa
,pay_time_periods ptp
,pay_pay_relationships_dn prd
,pay_payroll_rel_actions pra
,pay_run_results prr
,pay_run_result_values prrv
,pay_input_values_f pivf
,pay_element_types_vl pet
,pay_ele_classifications_vl pecv
,per_person_names_f ppnf
,hr_locations_all_f_vl loc
,hr_organization_v bu
,per_jobs_f_vl job
,per_job_family_f_vl job_fam
where 1 = 1
and paam.person_id = papf.person_id
and paam.assignment_type = 'E'
and paam.primary_flag = 'Y'
and paam.primary_work_relation_flag = 'Y'
and ppos.period_of_service_id = paam.period_of_service_id
and prd.person_id = papf.person_id
and ppa.earn_time_period_id = ptp.time_period_id
and ppa.action_status = 'C'
and ppa.action_type IN ('R','Q','B','V','I')
and pra.payroll_relationship_id = prd.payroll_relationship_id
and pra.payroll_action_id = ppa.payroll_action_id
and pra.retro_component_id is null
and prr.payroll_rel_action_id = pra.payroll_rel_action_id
and prrv.run_result_id = prr.run_result_id
and pivf.input_value_id = prrv.input_value_id
and pet.element_type_id = prr.element_type_id
and pecv.classification_id = pet.classification_id
and ptp.end_date = '2023-04-08' -- pay period end date
and ppnf.person_id = paam.person_id
and ppnf.name_type = 'GLOBAL'
and loc.location_id(+) = paam.location_id
and loc.active_status(+) = 'A'
and bu.organization_id = paam.business_unit_id
and bu.classification_code = 'FUN_BUSINESS_UNIT'
and bu.status = 'A'
and job.job_id(+) = paam.job_id
and job.active_status(+) = 'A'
and job_fam.job_family_id(+) = job.job_family_id
and job_fam.active_status(+) = 'A'
and pivf.base_name in ('Payment'
,'Pay Value'
,'Tax Calculated'
,'Deduction Calculated'
,'Employer Match Calculated'
,'Hours Calculated')
and ppa.effective_date between paam.effective_start_date and paam.effective_end_date
and ppa.effective_date between pet.effective_start_date and pet.effective_end_date
and ppa.effective_date between ppnf.effective_start_date and ppnf.effective_end_date
and paam.effective_start_date between loc.effective_start_date(+) and loc.effective_end_date(+)
and paam.effective_start_date between bu.effective_start_date and bu.effective_end_date
and paam.effective_start_date between job.effective_start_date(+) and job.effective_end_date(+)
and job.effective_start_date between job_fam.effective_start_date(+) and job_fam.effective_end_date(+)
and (coalesce(null,:p_loc) is null or paam.location_id in (:p_loc))
and (coalesce(null,:p_bu) is null or paam.business_unit_id in (:p_bu))
and (coalesce(:p_job_fam,null) is null or job_fam.job_family_id in (:p_job_fam))
)
,pay_bal_tbl_vw2 as (
select person_number
,person_name
,check_date
,prd_st_dt
,prd_ed_dt
,element_name
,classification_name
,base_name
,class1
,sum(result_value) result_value
from pay_bal_tbl_vw1
group by person_number
,person_name
,check_date
,prd_st_dt
,prd_ed_dt
,element_name
,classification_name
,base_name
,class1
)
select * from pay_bal_tbl_vw2
pivot (max(result_value) for base_name in (
'Hours' hours
,'Amount' amount))
Comments
Post a Comment