Oracle Payroll | R12 | Simple view for employee paid salaries

 

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

0046

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

[sourcecode language=”sql” gutter=”false” wraplines=”true”]
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;

[/sourcecode]

Sample Query

[sourcecode language=”sql” gutter=”false” wraplines=”true”]
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
[/sourcecode]

Enjoy another quality post from us guys :)

for Windows7bugs

rajesh

IDS Systems Pvt Ltd, a place where you should learn Oracle technologies

 

Ok guys, we are doing a promotion for IDS Systems PVT Ltd, Bangalore, India (details here http://www.idsin.net/)

We just had a supply chain management functional (followed by technical briefing) from IDS Systems PVT Ltd and confirm this institution provide anybody a high quality learning experience and exceed the expectations at any point of time.

IDS Systems PVT Ltd is located in the heart of Bangalore city, off Richmond road, Brigade area. Trainings could be arranged through Mr. Satish T.S. Please check IDS Systems PVT Ltd’s website for contact details http://www.idsin.net

IDS Systems PVT Ltd has classrooms equipped with right setups, providing the participants adequate and excellent exposure towards whichever the training they are undertaking (Please visit their website for testimonials)

Few images from the institution

 

2012-06-07-1832012-06-07-184

This is the first time we are promoting any institution in particular through our blog and doing the same in order to express our gratitude towards the excellent experience provided by IDS.

 

for Windows7bugs

Admin