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

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!

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