PAY Tax Payments Listing

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