Payroll Tables

select * from fnd_lookup_types where lookup_type = 'LEAV_REAS'

select * from fnd_lookup_values where lookup_type = 'LEAV_REAS'

select * from per_organization_structures

select * from per_org_structure_versions

select * from per_org_structure_elements

select * from PER_ABSENCE_ATTENDANCE_TYPES

select * from PER_ABSENCE_ATTENDANCES where person_id = 14049

select * from  PER_PAY_BASES

select * from PAY_ACCRUAL_PLANS

select * from pay_accrual_bands

select * from pay_org_payment_methods_f

select * from PAY_PERSONAL_PAYMENT_METHODS_F

select * from PAY_CONSOLIDATION_SETS where business_group_id = 0

select * from pay_payrolls_f

select * from per_time_periods where payroll_id = 61

select * from PAY_ORG_PAY_METHOD_USAGES_F where payroll_id = 61

select * from pay_element_types_f where element_name like 'Regular Salary'

select * from pay_input_values_f where element_type_id =  58091

select * from PAY_BALANCE_FEEDS_F where input_value_id = 153816

select * from PAY_BALANCE_TYPES

select * from pay_element_links_f where element_type_id =  58091

select * from PAY_ELEMENT_ENTRIES_F where assignment_id = 32148

select * from PAY_ELEMENT_ENTRY_VALUES_F where element_entry_id = 3083282

select * from HR_ASSIGNMENT_SETS

select * from HR_ASSIGNMENT_SET_AMENDMENTS

select * from PAY_ELEMENT_SETS

select * from pay_action_classifications where CLASSIFICATION_NAME = 'COSTED'

select * from PAY_PURGE_ACTION_TYPES

select * from pay_payroll_actions order by 1 desc --where payroll_action_id = 33138000

select * from pay_assignment_actions where assignment_action_id = 78245--payroll_action_id = 14199 and SOURCE_ACTION_ID IS NOT NULL

select * from pay_run_results where assignment_action_id = 93640

select * from pay_run_result_values where run_result_id = 312710

select * from PAY_PRE_PAYMENTS where assignment_action_id = 78243

select * from PAY_COSTS

select * from PAY_GL_INTERFACE

select * from pay_action_interlocks where locked_action_id = 33018734

select * from FF_DATABASE_ITEMS where user_name like 'ASG%PAYROLL%'-- To get Select Clause

select * from FF_USER_ENTITIES where user_entity_id = 1208

select * from FF_ROUTES where route_id = 248-- To get FROM and WHERE Clause

PAY_BALANCE_PKG

select * from PAY_USER_TABLES

select * from PAY_USER_COLUMNS

select * from PAY_USER_ROWS_F

select * from PAY_USER_COLUMN_INSTANCES_F

select * from HXC_TIME_BUILDING_BLOCKS

********************** Payroll Run Results *****************************
SELECT b.assignment_number,
b.full_name,
d.original_date_of_hire,
d.date_of_birth,
a.ELEMENT_NAME,
a.CLASSIFICATION_NAME,
a.OUTPUT_CURRENCY_CODE,
MODIFIED,
UNITS,
a.ASSIGNMENT_ACTION_ID,
a.RUN_RESULT_ID,
a.RESULT_VALUE,
a.UOM,
a.INPUT_VALUE_ID
FROM PAY_RUN_RESULTS_V a ,
PAY_ASSIGNMENT_ACTIONS_V b,
per_all_assignments_f c,
per_all_people_f d
where
a.assignment_action_id = b.assignment_action_id
and PAYROLL_ACTION_ID = :p_action_id
and b.assignment_id = c.assignment_id
and d.person_id = c.person_id
and sysdate between c.effective_start_date and c.effective_end_date
and sysdate between d.effective_start_date and d.effective_end_date
order by  b.full_name,a.ELEMENT_NAME;

OR

SELECT
PPF.employee_number "Employee Number",  
                  ele.element_name,
                 decode(cla.CLASSIFICATION_name,'Earnings',1,'Allowances',1,2) class,
         sum(to_number(RUN_RES_val.result_value)) amount
FROM        per_people_f PPF,
            pay_element_classifications CLA,
            per_assignments_f PAF,
            pay_element_types_f ELE,
            pay_input_values_f IP,
            pay_run_results RUN_RES,
            pay_run_result_values RUN_RES_VAL,
            pay_payroll_actions PAY_ACT,
            pay_assignment_actions ASSIGN_ACT,
            --hr_assignment_sets ASSIGN_SET,
            pay_payrolls_f PAYROLL
WHERE  PPF.person_id=PAF.person_id
--AND        PAY_ACT.assignment_set_id=ASSIGN_SET.assignment_set_id
AND        PAY_ACT.PAYROLL_action_id=ASSIGN_ACT.payroll_action_id
AND        RUN_RES.assignment_action_id=ASSIGN_ACT.assignment_action_id
AND        RUN_RES_VAL.input_value_id=IP.input_value_id
AND        ELE.element_type_id=RUN_RES.element_type_id
AND        ASSIGN_ACT.assignment_id=PAF.assignment_id
AND       RUN_RES.run_result_id=RUN_RES_val.run_result_id
AND       PAY_ACT.effective_date  between PPF.effective_start_date AND PPF.effective_end_date
AND       PAY_ACT.effective_date  between PAF.effective_start_date AND PAF.effective_end_date
AND       IP.name ='Pay Value'
--AND       CLA.classification_name in ('Earnings','Allowances','Advances')
AND       PPF.business_group_id=:p_business_group_id
AND       PAY_ACT.payroll_id=:p_payroll_id
AND       CLA.classification_id=ELE.classification_id
AND       to_char(PAY_ACT.date_earned,'yyyymm')=:p_period
and pay_act.payroll_action_id=:p_payroll_action_id
and ppf.employee_number like decode(:p_employee_number,'','%',:p_employee_number)
--AND       ELE.element_name ='Form16 Income Tax Information'
AND      RUN_RES_val.result_value NOT LIKE '0'
AND       PAY_ACT.payroll_id=PAYROLL.payroll_id
--AND     PAY_ACT.payroll_action_id  like decode(:p_payroll_action_id,'','%',:p_payroll_action_id)
AND       ELE.effective_start_date=(SELECT max(effective_start_date) FROM pay_element_types_f
WHERE ELEment_type_id=ELE.element_type_id)
AND       IP.effective_start_date=(SELECT max(effective_start_date) FROM pay_input_values_f
WHERE input_value_id=IP.input_value_id)
--AND       ASSIGN_SET.assignment_set_name=:p_assignment_set_name
and cla.CLASSIFICATION_NAME not in ('Information','Employer Charges')
group by
PPF.employee_number,  
                  ele.element_name,
                 decode(cla.CLASSIFICATION_name,'Earnings',1,'Allowances',1,2)
order by  PPF.employee_number,                 decode(cla.CLASSIFICATION_name,'Earnings',1,'Allowances',1,2),
ele.ELEMENT_NAME

select * from fnd_tables where table_name like 'PAY%ACTION%TYPE%'