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

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: