In this article, we will look into how to fetch the employees with Gross Earnings and Net Pay ranges for all employees.
We can use these ranges to identify how many employees fall under each range which in turn can be used for reconciliation purposes.
Gross Earnings and Net Pay are balances and all earnings elements feed into these balances. We will have to fetch the Run Dimension for these balances and then group them into the ranges.
SQL Query to fetch the Gross Earnings and Net Pay ranges for employees for a specific pay period:
SELECT run_type,
date_earned,
person_number,
payroll_relationship_number,
first_name,
middle_names,
last_name,
payroll_name,
net_payment,
gross_earnings,
CASE WHEN gross_earnings BETWEEN 0 AND 2500
THEN 'Between 0 and 2500'
WHEN gross_earnings BETWEEN 2500 AND 10000
THEN 'Between 2500 AND 10000'
WHEN gross_earnings > 10000
THEN 'Greater than 10000'
WHEN gross_earnings IS NULL
THEN 'No gross earnings'
ELSE 'Other gross earnings range'
END gross_pay_range,
CASE WHEN net_payment BETWEEN 0 AND 2500
THEN 'Between 0 and 2500'
WHEN net_payment BETWEEN 2500 AND 10000
THEN 'Between 2500 AND 10000'
WHEN net_payment > 10000
THEN 'Greater than 10000'
WHEN net_payment IS NULL
THEN 'No net pay'
ELSE 'Other net pay range'
END net_payment_range
FROM
(
SELECT run_type,
date_earned,
person_number,
payroll_relationship_number,
payroll_name,
first_name,
middle_names,
last_name,
MAX(net_payment) net_payment,
MAX(gross_earnings) gross_earnings
FROM
(
SELECT run_type,
date_earned,
person_number,
payroll_relationship_number,
payroll_name,
first_name,
middle_names,
last_name,
DECODE(balance_name, 'Net Pay', balance_value) net_payment,
DECODE(balance_name, 'Gross Earnings', balance_value) gross_earnings
FROM
(
SELECT hr_general.decode_lookup ('ACTION_TYPE',ppa.action_type) run_type
,TO_CHAR(ppa.date_earned, 'YYYY-MM-DD') date_earned
,papf.person_number
,pprd.payroll_relationship_number
,payf.payroll_name
,pb.balance_name
,ppnf.first_name
,ppnf.middle_names
,ppnf.last_name
,SUM (NVL (fnd_number.canonical_to_number (prrv.result_value) * pbf.scale
,0)) balance_value
FROM pay_payroll_actions ppa
,pay_payroll_rel_actions ppra
,pay_pay_relationships_dn pprd
,per_all_people_f papf
,per_person_names_f ppnf
,pay_run_results prr
,pay_run_result_values prrv
,pay_balance_feeds_f pbf
,pay_balance_types_tl pb
,pay_all_payrolls_f payf
,PAY_TIME_PERIODS PY_PRD
WHERE ppa.payroll_id IS NOT NULL
AND papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND ppa.payroll_action_id = ppra.payroll_action_id
AND ppra.payroll_relationship_id = pprd.payroll_relationship_id
AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id
AND prr.run_result_id = prrv.run_result_id
AND prrv.input_value_id = pbf.input_value_id
AND pbf.balance_type_id = pb.balance_type_id
AND pb.LANGUAGE = USERENV ('LANG')
AND pb.balance_name IN ( 'Gross Pay', 'Net Pay', 'Gross Earnings')
AND ppa.payroll_id = payf.payroll_id
AND (
ppa.action_type IN ('I','B')
OR ppra.run_type_id IS NOT NULL
)
--AND ppa.date_earned = :p_date_earned
--AND payf.payroll_name = NVL(:p_payroll_name, payf.payroll_name)
--AND PY_PRD.END_DATE BETWEEN :P_PRD_START_DT AND :P_PRD_END_DT
--AND PY_PRD.PERIOD_NAME = '4 2022 Biweekly'
AND ppa.EARN_TIME_PERIOD_ID = PY_PRD.TIME_PERIOD_ID
AND pprd.person_id = papf.person_id
AND ppa.date_earned BETWEEN pbf.effective_start_date AND pbf.effective_end_date
AND ppa.date_earned BETWEEN papf.effective_start_date AND papf.effective_end_date
AND ppa.date_earned BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND ppa.date_earned BETWEEN payf.effective_start_date AND payf.effective_end_date
-- AND papf.person_number IN ('12345678', '1122334455')
GROUP BY papf.person_number
,pprd.payroll_relationship_number
,payf.payroll_name
,pb.balance_name
,TO_CHAR(ppa.date_earned, 'YYYY-MM-DD')
,hr_general.decode_lookup ('ACTION_TYPE',ppa.action_type)
,ppnf.first_name
,ppnf.middle_names
,ppnf.last_name
) a
)
GROUP BY run_type,
date_earned,
person_number,
payroll_relationship_number,
payroll_name,
first_name,
middle_names,
last_name
)We can add the Period Name or Start Date and End Date input parameters to the above query to get the output. If we do a straight forward run, the query will timeout as there would be huge data to fetch.
If you like the content, please follow us on LinkedIn, Facebook, and Twitter to get updated with the latest content.
