ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4714951A9E05362FB09050512 SELECT /*+ ordered */ Q2.bal_assact_id, Q2.max_assact, Q2.cat_code, SUBSTR (hr_general.decode_lookup ('GB_SCON', DECODE (Q2.cat_code, 'F', DECODE(ev_cat.screen_entry_value, 'F',ev_scon.screen_entry_value, pay_gb_eoy_archive.get_nearest_scon( ev_scon.element_entry_id,:asg_action_id, 'F', pact2.effective_date) ), 'G', DECODE(ev_cat.screen_entry_value, 'G',ev_scon.screen_entry_value, pay_gb_eoy_archive.get_nearest_scon( ev_scon.element_entry_id,:asg_action_id, 'G', pact2.effective_date) ), 'S', DECODE(ev_cat.screen_entry_value, 'S',ev_scon.screen_entry_value, pay_gb_eoy_archive.get_nearest_scon( ev_scon.element_entry_id,:asg_action_id, 'S', pact2.effective_date) ), NULL)), 1, 9) max_screen_entry, ev_scon.element_entry_id max_element_entry, Q2.max_effective_date, DECODE (Q2.balance_dimension_id, :c_ptd_bal_dim_id, 'PTD',:c_ytd_bal_dim_id, 'YTD',NULL) dim, NVL(hr_dirbal.get_balance(:asg_action_id,Q2.able_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0) able ,NVL(hr_dirbal.get_balance(:asg_action_id, Q2.employee_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0) employee ,NVL(hr_dirbal.get_balance(:asg_action_id, Q2.employer_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0) employer ,NVL(hr_dirbal.get_balance(:asg_action_id, Q2.total_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0) total ,NVL(hr_dirbal.get_balance(:asg_action_id, Q2.rebate_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0) rebate, PAY_PAYGBTPL_XMLP_PKG.c_calculate_totalsformula(q2.CAT_CODE,DECODE (Q2.balance_dimension_id, :c_ptd_bal_dim_id, 'PTD',:c_ytd_bal_dim_id, 'YTD',NULL), NVL(hr_dirbal.get_balance(:asg_action_id, Q2.employer_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0), NVL(hr_dirbal.get_balance(:asg_action_id,Q2.able_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0), NVL(hr_dirbal.get_balance(:asg_action_id, Q2.employee_id,Q2.balance_dimension_id,:p_payroll_period_id,:c_ptd_bal_dim_id),0)) C_calculate_totals FROM ( SELECT /*+ ordered use_nl(bal_assact bact bptp assact pact pptp rr target feed bal defbal et) */ bal_assact.assignment_action_id bal_assact_id, to_number(substr(max(lpad(assact.action_sequence,15,'0')||assact.assignment_action_id),16)) max_assact, SUBSTR (bal.balance_name, 4, 1) cat_code, MAX (pact.effective_date) max_effective_date ,defbal.balance_dimension_id balance_dimension_id ,MAX(DECODE(SUBSTR (bal.balance_name, 6),'Able', defbal.defined_balance_id) ) able_id ,MAX(DECODE(SUBSTR (bal.balance_name, 6),'Employee', defbal.defined_balance_id) ) employee_id ,MAX(DECODE(SUBSTR (bal.balance_name, 6),'Employer', defbal.defined_balance_id) ) employer_id ,MAX(DECODE(SUBSTR (bal.balance_name, 6),'Total', defbal.defined_balance_id) ) total_id ,MAX(DECODE(SUBSTR (bal.balance_name, 6),'Rebate', defbal.defined_balance_id) ) rebate_id FROM pay_assignment_actions bal_assact, pay_payroll_actions bact, per_time_periods bptp, pay_assignment_actions assact, pay_payroll_actions pact, per_time_periods pptp, pay_run_results rr, pay_run_result_values target, pay_balance_feeds_f feed, pay_balance_types bal, pay_defined_balances defbal, pay_element_types_f et WHERE bal_assact.assignment_action_id = :asg_action_id AND bact.payroll_action_id = bal_assact.payroll_action_id AND bptp.time_period_id = :p_payroll_period_id AND pptp.time_period_id = pact.time_period_id AND bal.balance_name LIKE 'NI%' AND bal.balance_name NOT IN ('NI Employee', 'NI Employer') AND bal.legislation_code = 'GB' AND SUBSTR(bal.balance_name, 4, 1) IN ('A','B','C','D','E','F','G','J','L','S','X','M','Z','I','K') AND defbal.balance_type_id = bal.balance_type_id AND defbal.balance_dimension_id IN (:c_ytd_bal_dim_id, :c_ptd_bal_dim_id) AND feed.balance_type_id = bal.balance_type_id AND feed.input_value_id = target.input_value_id AND target.run_result_id = rr.run_result_id AND target.result_value <> '0' AND rr.assignment_action_id = assact.assignment_action_id and et.element_type_id = rr.element_type_id and (et.element_name like 'NI%' or et.element_name like 'Setup NI Balance%') AND et.legislation_code = 'GB' AND assact.payroll_action_id = pact.payroll_action_id AND pact.action_type IN ('Q', 'R', 'B', 'I','V') AND pact.effective_date BETWEEN feed.effective_start_date AND feed.effective_end_date AND rr.status IN ('P', 'PA') AND pptp.regular_payment_date >= (TO_DATE ('06-04-'|| TO_CHAR (TO_NUMBER (TO_CHAR(bptp.regular_payment_date, 'YYYY')) + DECODE (SIGN (bptp.regular_payment_date - TO_DATE ('06-04-'|| TO_CHAR(bptp.regular_payment_date,'YYYY'),'DD-MM-YYYY')), -1, -1, 0)), 'DD-MM-YYYY')) AND pact.effective_date >= (SELECT /*+ ordered use_nl(ass pass nroll flex proll pflex) index(nroll PAY_ALL_PAYROLLS_F_PK) index(proll PAY_ALL_PAYROLLS_F_PK) */ NVL (MAX (ass.effective_start_date), TO_DATE ('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')) FROM per_all_assignments_f ass, per_all_assignments_f pass, pay_all_payrolls_f nroll, hr_soft_coding_keyflex flex, pay_all_payrolls_f proll, hr_soft_coding_keyflex pflex WHERE ass.assignment_id = bal_assact.assignment_id AND nroll.payroll_id = ass.payroll_id AND ass.effective_start_date BETWEEN nroll.effective_start_date AND nroll.effective_end_date AND nroll.soft_coding_keyflex_id = flex.soft_coding_keyflex_id AND ass.assignment_id = pass.assignment_id AND pass.effective_end_date = (ass.effective_start_date - 1) AND ass.effective_start_date <= bact.effective_date AND proll.payroll_id = pass.payroll_id AND ass.effective_start_date BETWEEN proll.effective_start_date AND proll.effective_end_date AND proll.soft_coding_keyflex_id = pflex.soft_coding_keyflex_id AND ass.payroll_id != pass.payroll_id AND flex.segment1 != pflex.segment1) AND assact.action_sequence <= bal_assact.action_sequence AND assact.assignment_id = bal_assact.assignment_id and ((assact.source_action_id is not null) OR (pact.action_type in ('I','B','V') and assact.source_action_id is null)) GROUP BY bal_assact.assignment_action_id, SUBSTR (bal.balance_name, 4, 1) ,defbal.balance_dimension_id ORDER BY MAX (DECODE (SUBSTR (bal.balance_name, 4, 1), :c_current_cat, '1' || SUBSTR (bal.balance_name, 4, 1), '2' || SUBSTR (bal.balance_name, 4, 1))), 3, DECODE (defbal.balance_dimension_id, :c_ptd_bal_dim_id, 'PTD', :c_ytd_bal_dim_id, 'YTD',NULL) ) Q2 , pay_assignment_actions assact2, pay_payroll_actions pact2, pay_element_entries_f ee_ni, pay_element_entry_values_f ev_scon, pay_element_entry_values_f ev_cat WHERE assact2.assignment_action_id= Q2.max_assact AND pact2.payroll_action_id = assact2.payroll_action_id AND ee_ni.assignment_id = assact2.assignment_id AND ev_scon.element_entry_id= ee_ni.element_entry_id AND ev_cat.element_entry_id = ee_ni.element_entry_id AND ev_scon.input_value_id = :c_scon_input_value_id AND ev_cat.input_value_id = :c_cat_input_value_id AND ee_ni.element_type_id =:c_ni_element_type_id AND pact2.effective_date BETWEEN ee_ni.effective_start_date AND ee_ni.effective_end_date AND pact2.effective_date BETWEEN ev_scon.effective_start_date AND ev_scon.effective_end_date AND pact2.effective_date BETWEEN ev_cat.effective_start_date AND ev_cat.effective_end_date Y PAY PAYRPTPL_XML US PAY Tax Payments Listing Imported from BI Publisher Description: Tax Payments Listing Application: Payroll Source: Tax Payments Listing (XML) Short Name: PAYRPTPL_XML DB package: PAY_PAYGBTPL_XMLP_PKG BI Publisher :asg_action_id :c_asppa_ptd_dbid :c_asppa_ptd_tot :c_asppa_ptd_value :c_asppa_ytd_dbid :c_asppa_ytd_tot :c_asppa_ytd_value :c_asppb_ptd_dbid :c_asppb_ptd_tot :c_asppb_ptd_value :c_asppb_ytd_dbid :c_asppb_ytd_tot :c_asppb_ytd_value :c_basis :c_business_group_name :c_cat_input_value_id :c_consolidation_set :c_current_cat :c_ees_reb_ptd_tot :c_ees_reb_ytd_tot :c_ers_reb_ptd_tot :c_ers_reb_ytd_tot :c_gross_nic :c_gross_pay_ptd_dbid :c_gross_pay_ptd_tot :c_gross_pay_ptd_value :c_gross_pay_ytd_dbid :c_gross_pay_ytd_tot :c_gross_pay_ytd_value :c_net_income_tax :c_net_ni :c_ni_earnings_ptd_tot :c_ni_earnings_ytd_tot :c_ni_ees_ptd_tot :c_ni_ees_ytd_tot :c_ni_element_type_id :c_ni_ers_ptd_tot :c_ni_ers_ytd_tot :c_ni_total :c_nic_rebate :c_output :c_paid :c_paye_ptd_dbid :c_paye_ptd_tot :c_paye_ptd_value :c_paye_ytd_dbid :c_paye_ytd_tot :c_paye_ytd_value :c_payroll_name :c_payroll_period :c_pension_ptd_dbid :c_pension_ptd_tot :c_pension_ptd_value :c_pension_ytd_dbid :c_pension_ytd_tot :c_pension_ytd_value :c_period_date :c_pgl_ptd_dbid :c_pgl_ptd_tot :c_pgl_ptd_value :c_pgl_ytd_dbid :c_pgl_ytd_tot :c_pgl_ytd_value :c_prev_taxable :c_previous_gross_tot :c_previous_tax_tot :c_ptd_bal_dim_id :c_sap_ptd_dbid :c_sap_ptd_tot :c_sap_ptd_value :c_sap_ytd_dbid :c_sap_ytd_tot :c_sap_ytd_value :c_scon_input_value_id :c_shppa_ptd_dbid :c_shppa_ptd_tot :c_shppa_ptd_value :c_shppa_ytd_dbid :c_shppa_ytd_tot :c_shppa_ytd_value :c_shppb_ptd_dbid :c_shppb_ptd_tot :c_shppb_ptd_value :c_shppb_ytd_dbid :c_shppb_ytd_tot :c_shppb_ytd_value :c_smp_ptd_dbid :c_smp_ptd_tot :c_smp_ptd_value :c_smp_ytd_dbid :c_smp_ytd_tot :c_smp_ytd_value :c_sort_order :c_sppa_ptd_dbid :c_sppa_ptd_tot :c_sppa_ptd_value :c_sppa_ytd_dbid :c_sppa_ytd_tot :c_sppa_ytd_value :c_sppb_ptd_dbid :c_sppb_ptd_tot :c_sppb_ptd_value :c_sppb_ytd_dbid :c_sppb_ytd_tot :c_sppb_ytd_value :c_ssp_ptd_dbid :c_ssp_ptd_tot :c_ssp_ptd_value :c_ssp_ytd_dbid :c_ssp_ytd_tot :c_ssp_ytd_value :c_stuln_ptd_dbid :c_stuln_ptd_tot :c_stuln_ptd_value :c_stuln_ytd_dbid :c_stuln_ytd_tot :c_stuln_ytd_value :c_summary_only :c_tax_code :c_taxable_pay_ptd_dbid :c_taxable_pay_ptd_tot :c_taxable_pay_ptd_value :c_taxable_pay_ytd_dbid :c_taxable_pay_ytd_tot :c_taxable_pay_ytd_value :c_taxcr_ptd_dbid :c_taxcr_ptd_tot :c_taxcr_ptd_value :c_taxcr_ytd_dbid :c_taxcr_ytd_tot :c_taxcr_ytd_value :c_total_amount_due :c_ty_start :c_working_taxcr_ptd_dbid :c_working_taxcr_ptd_value :c_working_taxcr_ytd_dbid :c_working_taxcr_ytd_value :c_ytd_bal_dim_id :p_business_group_id :p_conc_request_id :p_consolidation_set_id :p_consolidation_set_where :p_payroll_id :p_payroll_period_id :p_reversal_consolidation_set :p_sort :p_sort_order :p_summary_only 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Business Group ID 2 10 :p_payroll_id LOV Oracle PY_SRS_PAYROLL Y select pay.payroll_id id, pay.payroll_name value, null description from pay_payrolls_f pay,fnd_sessions fnd where pay.business_group_id= fnd_global.per_business_group_id and fnd.session_id=userenv('sessionid') and fnd.effective_date between pay.effective_start_date and pay.effective_end_date order by pay.payroll_name Y US Payroll PAYROLL 3 20 :p_payroll_period_id LOV Oracle PY_SRS_PAY_PERIOD_ID Y select time_period_id id, period_name value, null description from per_time_periods p,fnd_sessions f where p.payroll_id=:$flex$.py_srs_payroll and p.start_date<=f.effective_date and f.session_id= userenv('sessionid') order by p.start_date desc select p.period_name from per_time_periods p,fnd_sessions f where p.payroll_id = :$flex$.payroll and f.session_id = userenv('sessionid') and f.effective_date between p.start_date and p.end_date Y US Payroll Period PAYROLL PERIOD 4 30 :p_consolidation_set_id LOV Oracle PY_SRS_CONSOLIDATION_SET_PAYROLL Y select con.consolidation_set_id id, con.consolidation_set_name value, null description from pay_consolidation_sets con where con.business_group_id =fnd_global.per_business_group_id and exists ( select 1 from pay_payroll_actions pact, per_time_periods ptp where pact.consolidation_set_id=con.consolidation_set_id and ptp.time_period_id=:$flex$.py_srs_pay_period_id and pact.payroll_id=:$flex$.py_srs_payroll and pact.effective_date between ptp.start_date and ptp.end_date and pact.action_type in ('R','Q','P','U','V') ) order by con.consolidation_set_name US Consolidation Set CONSOLIDATION SET 5 40 :p_sort LOV Oracle PY_SRS_TAX_ORD_SORT Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='TAX_PAYMENTS_REPORT_ORDER' order by value,description ASG Y US Sort Order SORT ORDER 6 50 :p_summary_only LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Summary Only SUMMARY ONLY