ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4714751A9E05362FB09050512Y 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 PAY PAYRPEAR_XML US PAY Earnings Audit 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 BI Publisher :c_business_group_name :c_consolidation_set_name :c_element_type_name :c_payroll_action :c_payroll_name :c_period_end_date :c_period_start_date :c_tax_unit :c_tax_unit_address2 :c_time_period_name :consolidation_set_id :p_business_group_id :p_conc_request_id :p_consolidation_set_id :p_element_type_id :p_payroll_action_id :p_payroll_id :p_tax_unit_id :p_time_period_id 1 -40 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Name Business Group ID 2 -30 :p_not_consolidation_set_id Char SELECT decode(nvl(:$flex$.consolidation_set_name:NULL,0), 0, 'X', null) from dual US Not_Consolidation_Set_ID 3 -20 :p_not_payroll_id Char SELECT decode(nvl(:$flex$.payroll_name:NULL,0),0, 'X', null) from dual US Not_Payroll_ID 4 10 :p_consolidation_set_id LOV Oracle HR_US_CONSOLIDATION_SET_NAME Y select pcs.consolidation_set_id id, pcs.consolidation_set_name value, null description from pay_consolidation_sets pcs where pcs.business_group_id= fnd_global.per_business_group_id order by pcs.consolidation_set_name US Consolidation Set Name Consolidation Set ID 5 20 :p_payroll_id LOV Oracle HR_US_PAYROLL_NAME Y select ppf.payroll_id id, ppf.payroll_name value, null description from pay_payrolls_f ppf where ppf.business_group_id=fnd_global.per_business_group_id and :$flex$.not_consolidation_set_id is not null and ppf.effective_start_date= (select max(effective_start_date) from pay_payrolls_f ppf1 where ppf.payroll_id=ppf1.payroll_id) order by ppf.payroll_name US Payroll Name Payroll ID 6 30 :p_time_period_id LOV Oracle HR_US_TIME_PERIOD_NAME Y Y select ptp.time_period_id id, ptp.period_name||' ('||to_char(ptp.time_period_id)||')' value, null description from per_time_periods ptp,pay_payrolls_f ppf,pay_consolidation_sets pcs where pcs.consolidation_set_id=ppf.consolidation_set_id and ppf.payroll_id=ptp.payroll_id and ( (:$flex$.hr_us_consolidation_set_name:null is not null and pcs.consolidation_set_id=:$flex$.hr_us_consolidation_set_name:null and :$flex$.not_consolidation_set_id is not null ) or (:$flex$.hr_us_consolidation_set_name:null is null and ppf.effective_start_date=(select max(effective_start_date) from pay_payrolls_f ppf1 where ppf.payroll_id=ppf1.payroll_id and ppf1.payroll_id= :$flex$.hr_us_payroll_name:null) ) ) order by ptp.start_date,ppf.payroll_name Y US Time Period Name Time Period ID 7 40 :p_tax_unit_id LOV Oracle HR_US_TAX_UNIT_NAME Y select tax_unit_id id, name value, null description from hr_tax_units_v where business_group_id=fnd_global.per_business_group_id order by name US Government Reporting Entity Tax Unit ID 8 50 :p_element_type_id LOV Oracle HR_US_EARNINGS_ELEMENT_ALL Y select pet.element_type_id id, pettl.element_name value, null description from pay_element_classifications pec,pay_element_types_f_tl pettl,pay_element_types_f pet where (pet.business_group_id+0=fnd_global.per_business_group_id or pet.business_group_id is null) and pet.element_type_id=pettl.element_type_id and pettl.language=userenv('LANG') and pec.classification_id=pet.classification_id and pec.classification_name like '%Earnings%' and pec.legislation_code='US' order by value,description Y US Earnings Element Earnings Element