Search This Blog

Friday, July 28, 2017

What are the Tables that Store the Calculations from the Calculate Payroll Process

There will be not any direct table to get the Net Amount and Gross Amount.
Please use the RUN RESULTS tables to see the all the run results for the employee.  Also you can use Balance functions to get the Balance value for particular person on given date.

For Employee information
PER_ALL_PEOPLE_F
PAY_PAY_RELATIONSHIPS_DN

For run results
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES

For Payment Method
PAY_PERSON_PAY_METHODS_F

For all HCM table information - please use below URL
http://docs.oracle.com/cloud/latest/globalcs_gs/OEDMH/toc.htm
To join these tables, the query will fetch requested information

select papf.person_number person_number
  ,pppmf.name payment_method_name
  ,PPA.PAYROLL_ACTION_ID payroll_action_id
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Net Payment',PRRV.RESULT_VALUE),0)) NET_PAYMENT
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Gross Pay',PRRV.RESULT_VALUE),0)) GROSS_PAY

from
PER_ALL_ASSIGNMENTS_m paam
,per_all_people_f papf
,per_periods_of_service ppos
,PAY_REL_GROUPS_DN prgd


,PAY_RUN_RESULTS PRR
,PAY_RUN_RESULT_VALUES PRRV
,PAY_BALANCE_FEEDS_F PBFF
,PAY_BALANCE_TYPES_TL PBTT
,PAY_PAYROLL_REL_ACTIONS PPRA
,PAY_PAYROLL_ACTIONS PPA
,pay_person_pay_methods_f pppmf


where paam.period_of_service_id = ppos.period_of_service_id
and paam.assignment_type='E'
and papf.person_id=paam.person_id
and prgd.GROUP_TYPE = 'A'
and prgd.payroll_relationship_id =PPRA.payroll_relationship_id
and prgd.assignment_id =paam.assignment_id
and ppos.date_start=(select max(pos1.date_start)
  from per_periods_of_service pos1
,per_all_assignments_f asg1
,pay_payroll_assignments ppa1
where PPA.date_earned >= pos1.date_start
  and pos1.person_id = asg1.person_id
  and asg1.assignment_id = ppa1.hr_assignment_id
  and asg1.period_of_service_id = pos1.period_of_service_id
  and ppa1.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID
and pos1.person_id = paam.person_id
and pos1.period_type = 'E')
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE

and papf.person_number=<PERSON_NUMBER>

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
AND PPRA.SOURCE_ID IS NULL
AND PPRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBFF.INPUT_VALUE_ID
AND PBFF.BALANCE_TYPE_ID = PBTT.BALANCE_TYPE_ID
AND PBTT.LANGUAGE = 'US'
and PBTT.BALANCE_NAME in ('Net Payment','Gross Pay')
and pppmf.payroll_relationship_id = prgd.payroll_relationship_id
GROUP BY
  papf.person_number
,pppmf.name
,PPA.PAYROLL_ACTION_ID

No comments:

Post a Comment