Hi,
These are the following tables that will be hit when you run the Payroll RUN process from front End.
Following Query will return all the Earning Details for the Payroll Period.
select dt_main.ASSIGNMENT_ACTION_ID, employee_number, full_name, dep_name, designation, doj, date_earned, petv.REPORT_NAME,
petv.BASE_CLASSIFICATION_NAME, petv.RESULT_VALUE from
(
SELECT paa.ASSIGNMENT_ID,
paa.ASSIGNMENT_ACTION_ID ,
pass.LOCATION_ID,
pp.name Designation,
ppa.DATE_EARNED,
paa.PAYROLL_ACTION_ID,
paa.PAYROLL_ACTION_ID,
ppa.TIME_PERIOD_ID,
ppa.PAYROLL_ID,
ppa.PAY_ADVICE_DATE,
PAP.EMPLOYEE_NUMBER,
pap.full_name,
hr.name dep_name,
PAP.Original_date_Of_Hire "DOJ"
FROM pay_assignment_actions paa,
pay_payroll_actions ppa,
per_all_assignments_f pass,
per_all_people_f pap,
hr_all_organization_units hr,
per_all_assignments_f paaf,
per_all_positions pp
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND pap.person_id=paaf.person_id
AND paaf.position_id=pp.position_id(+)
AND paa.assignment_id = pass.assignment_id
AND pass.person_id = pap.person_id
AND pap.business_group_id =
_Business_group
AND pass.organization_id=hr.organization_id
AND ppa.action_type in ('R')
AND ppa.action_status = 'C'
AND pass.assignment_type = 'E'
AND pass.primary_flag = 'Y'
AND ppa.date_earned BETWEEN pass.effective_start_date AND pass.effective_end_date
AND ppa.date_earned BETWEEN pap.effective_start_date AND pap.effective_end_date
AND ppa.date_earned BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND ppa.date_earned =
ayroll_Date
AND paaf.payroll_id=:Payroll_id
--and petv.BASE_CLASSIFICATION_NAME = 'Earnings'
ORDER BY PAP.employee_number
)
dt_main, PAY_ELEMENT_TYPES_V1 petv
where dt_main.ASSIGNMENT_ACTION_ID = petv.ASSIGNMENT_ACTION_ID
and petv.BASE_CLASSIFICATION_NAME = 'Earnings'
order by employee_number, base_classification_name;
Note: Run in the TOAD
Parameters Details as follow:
_BUSINESS_GROUP = 101
AYROLL_DATE = 3/31/2009
AYROLL_ID = 71
Organization ID and Payroll_ID will be changed as per your initial setup.
Let me know if you need more details:
Regards,
Muzamil