PAY Earnings Audit
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Earnings Audit Report
Application: Payroll
Source: Earnings Audit Report (XML)
Short Name: PAYRPEAR_XML
DB package: PAY_PAYUSEAR_XMLP_PKG
Description: Earnings Audit Report
Application: Payroll
Source: Earnings Audit Report (XML)
Short Name: PAYRPEAR_XML
DB package: PAY_PAYUSEAR_XMLP_PKG
SELECT DISTINCT pcs.consolidation_set_name consolidation_set , pcs.consolidation_set_id consolidation_set_id , ptp.start_date period_start_date , ptp.end_date period_end_date , pap.payroll_name payroll_name , hr_payrolls.display_period_name(ppa.payroll_action_id) period_name , ptp.time_period_id time_period_id1 , pap.payroll_id payroll_id , ppa.effective_date processing_date FROM pay_payrolls_f pap , per_time_periods ptp , pay_payroll_actions ppa , pay_consolidation_sets pcs WHERE ( (:P_PAYROLL_ACTION_ID IS NULL AND :P_CONSOLIDATION_SET_ID IS NULL AND :P_PAYROLL_ID IS NOT NULL AND :P_TIME_PERIOD_ID IS NOT NULL AND pap.payroll_id = :P_PAYROLL_ID AND ppa.payroll_id = pap.payroll_id AND ppa.action_type in ('R','Q') AND ptp.payroll_id = pap.payroll_id AND ppa.date_earned BETWEEN ptp.start_date AND ptp.end_date AND ptp.time_period_id = :P_TIME_PERIOD_ID) OR (((:P_PAYROLL_ACTION_ID IS NOT NULL AND :P_CONSOLIDATION_SET_ID IS NULL AND :P_PAYROLL_ID IS NULL AND :P_TIME_PERIOD_ID IS NULL and ppa.payroll_action_id = :P_PAYROLL_ACTION_ID) OR (:P_PAYROLL_ACTION_ID IS NULL AND :P_CONSOLIDATION_SET_ID IS NOT NULL AND :P_PAYROLL_ID IS NULL AND :P_TIME_PERIOD_ID IS NULL AND ppa.consolidation_set_id = :P_CONSOLIDATION_SET_ID) OR (:P_PAYROLL_ACTION_ID IS NULL AND :P_CONSOLIDATION_SET_ID IS NOT NULL AND :P_PAYROLL_ID IS NULL AND :P_TIME_PERIOD_ID IS NOT NULL AND ptp.time_period_id = :P_TIME_PERIOD_ID AND ppa.consolidation_set_id = :P_CONSOLIDATION_SET_ID)) AND ppa.action_type in ('R','Q') AND pap.payroll_id = ppa.payroll_id AND ptp.payroll_id = pap.payroll_id AND ppa.date_earned BETWEEN ptp.start_date and ptp.end_date )) AND ppa.effective_date between pap.effective_start_date and pap.effective_end_date AND pcs.consolidation_set_id = ppa.consolidation_set_id AND EXISTS (SELECT * FROM pay_run_results prr , pay_element_types_f pet , pay_element_classifications pec , per_assignments_f pas , pay_assignment_actions paa WHERE paa.payroll_action_id = ppa.payroll_action_id AND prr.assignment_action_id = paa.assignment_action_id AND pas.assignment_id = paa.assignment_id AND paa.tax_unit_id = nvl(:P_TAX_UNIT_ID, paa.tax_unit_id) AND ppa.date_earned BETWEEN pas.effective_start_date AND pas.effective_end_date AND pet.element_type_id = prr.element_type_id AND pet.element_type_id = nvl(:P_ELEMENT_TYPE_ID, pet.element_type_id) AND pet.classification_id = pec.classification_id AND pec.classification_name IN ( 'Earnings' ,'Supplemental Earnings' ,'Imputed Earnings' ) AND pec.legislation_code = 'US' AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date) ORDER BY pap.payroll_name, ppa.effective_date |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Consolidation Set Name |
|
LOV Oracle | |
Payroll Name |
|
LOV Oracle | |
Time Period Name |
|
LOV Oracle | |
Government Reporting Entity |
|
LOV Oracle | |
Earnings Element |
|
LOV Oracle |