Recently I were requested to build a report by the HR/Payroll team, running which they can generate the salary paid details for employees. Ie, a tabular listing with paid month, and total salary earned, grouped by year factor

I found the request being one of the toughest, as my exposure to Payroll module and base tables was limited almost none, other than knowing the person and assignment tables and views!

Gradually I started going through the custom reports developed by our implementer and restructured few of their custom functions into a best possible view what meets our current requirements. As we are not using customized packages for the salary calculations, you should able to alter the below SQL and create your own with almost no efforts. We hope you will enjoy the solution!

Script for view

CREATE OR REPLACE VIEW XXEMPLOYEE_SALARIES_MONTHLY
AS
SELECT pap.person_id, pap.employee_number,to_char(ppa.date_earned,'Mon-YYYY') earned_month,
TO_NUMBER(to_char(ppa.date_earned,'MM')) MONTH_NUMBER,
TO_NUMBER(to_char(ppa.date_earned,'YYYY')) YEAR_FACTOR,
sum(to_number(prrv.result_value)) PAID_AMOUNT
FROM PAY_ELEMENT_TYPES_F petf
,PAY_INPUT_VALUES_F pivf
,PAY_PAYROLL_ACTIONS ppa
,PAY_ASSIGNMENT_ACTIONS paa
,PAY_RUN_RESULTS prr
,PAY_RUN_RESULT_VALUES prrv
,PER_ALL_ASSIGNMENTS_F paaf
,PER_ALL_PEOPLE_F pap
,PAY_ELEMENT_CLASSIFICATIONS pec
WHERE 1=1
AND pec.classification_id = petf.classification_id
and prrv.input_value_id = pivf.input_value_id
AND CLASSIFICATION_NAME IN ('Earnings','Supplemental Earnings')–Add in more based on your setup
and pivf.name in ('Pay Value')
AND petf.element_type_id = prr.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND petf.business_group_id = 81
AND ppa.business_group_id = pap.business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND SYSDATE BETWEEN TRUNC(petf.effective_start_date) AND TRUNC(petf.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(pap.effective_start_date) AND TRUNC(pap.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = pap.person_id
–and prrv.result_value > '0'
AND paaf.business_group_id = pap.business_group_id
AND pap.business_group_id = 81–double check
GROUP BY pap.person_id,pap.employee_number, to_char(ppa.date_earned,'Mon-YYYY'),to_char(ppa.date_earned,'MM'),to_char(ppa.date_earned,'YYYY')
UNION ALL
SELECT pap.person_id, pap.employee_number,to_char(ppa.date_earned,'Mon-YYYY') earned_month,
TO_NUMBER(to_char(ppa.date_earned,'MM')) MONTH_NUMBER,
TO_NUMBER(to_char(ppa.date_earned,'YYYY')) YEAR_FACTOR,
nvl(sum(to_number(prrv.result_value)),0)*-1 PAID_AMOUNT
FROM PAY_ELEMENT_TYPES_F petf
,PAY_INPUT_VALUES_F pivf
,PAY_PAYROLL_ACTIONS ppa
,PAY_ASSIGNMENT_ACTIONS paa
,PAY_RUN_RESULTS prr
,PAY_RUN_RESULT_VALUES prrv
,PER_ALL_ASSIGNMENTS_F paaf
,PER_ALL_PEOPLE_F pap
,PAY_ELEMENT_CLASSIFICATIONS pec
WHERE 1=1
AND pec.classification_id = petf.classification_id
and prrv.input_value_id = pivf.input_value_id
AND CLASSIFICATION_NAME IN ('Voluntary Deductions','Involuntary Deductions','Social Insurance')–Add in more based on your setup
and pivf.name in ('Pay Value')
AND petf.element_type_id = prr.element_type_id
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND ppa.business_group_id = pap.business_group_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND SYSDATE BETWEEN TRUNC(petf.effective_start_date) AND TRUNC(petf.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(pap.effective_start_date) AND TRUNC(pap.effective_end_date)
AND last_day(ppa.date_earned) BETWEEN TRUNC(paaf.effective_start_date) AND TRUNC(paaf.effective_end_date)
AND paaf.assignment_id = paa.assignment_id
AND paaf.person_id = pap.person_id
— and prrv.result_value > '0.00'
AND paaf.business_group_id = pap.business_group_id
AND pap.business_group_id = 81–double check
GROUP BY pap.person_id,pap.employee_number, to_char(ppa.date_earned,'Mon-YYYY'),to_char(ppa.date_earned,'MM'),to_char(ppa.date_earned,'YYYY')
order by 2,5,4;

Sample Query

SELECT PERSON_ID, EMPLOYEE_NUMBER,earned_month,year_factor,
SUM(PAID_AMOUNT) PAID_SALARY
FROM XXEMPLOYEE_SALARIES_MONTHLY
WHERE
1=1
AND EMPLOYEE_NUMBER =:P_EMPLOYEE_NUMBER
AND YEAR_FACTOR BETWEEN NVL(:P_START_YEAR,YEAR_FACTOR) AND NVL(:P_END_YEAR,YEAR_FACTOR)
GROUP BY PERSON_ID,EMPLOYEE_NUMBER,earned_month,YEAR_FACTOR, MONTH_NUMBER
ORDER BY YEAR_FACTOR, MONTH_NUMBER

Enjoy another quality post from us guys :)

for Windows7bugs

rajesh