PAY Inland Revenue Remittance Certificate

select
 o.NAME EMPLOYER_NAME
,l.ADDRESS_LINE_1
,l.ADDRESS_LINE_2
,l.ADDRESS_LINE_3
,l.TOWN_OR_CITY
,l.POSTAL_CODE
,l.country   country
,i.ORG_INFORMATION1 ER_IRD_NUMBER
,sum(decode
         (pbt.balance_name
         , 'PAYE Tax Deductions'
             , target.result_value * feed.scale
         , 'Withholding Tax Deductions Record'
             , target.result_value * feed.scale
        , 'Retro PAYE Tax Deductions'
            , target.result_value * feed.scale
         , 0
         )
       )
       PAYE_DEDUCTIONS
,sum(decode
         (pbt.balance_name
          , 'Child Support Deductions'
              , target.result_value * feed.scale
          , 0
         )
       )
       CHILD_SUPPORT_DEDUCTIONS
,sum(decode
         (pbt.balance_name
          , 'Student Loan Deductions'
                 , target.result_value * feed.scale
          , 'Retro Student Loan Deductions'
                , target.result_value * feed.scale
          , 0
         )
       )
       STUDENT_LOAN_DEDUCTIONS
,sum(decode
         (pbt.balance_name
         , 'SSCWT Deductions'
                , target.result_value * feed.scale
         , 0
         )
       )
       SSCWT_DEDUCTIONS, 
	PAY_PYNZ345_XMLP_PKG.cf_total_deductionsformula(sum ( decode ( pbt.balance_name , 'PAYE Tax Deductions' , target.result_value * feed.scale , 'Withholding Tax Deductions Record' , target.result_value * feed.scale , 'Retro PAYE Tax Deductions' , target.result_value * feed.scale , 0 ) ), sum ( decode ( pbt.balance_name , 'Child Support Deductions' , target.result_value * feed.scale , 0 ) ), sum ( decode ( pbt.balance_name , 'Student Loan Deductions' , target.result_value * feed.scale , 'Retro Student Loan Deductions' , target.result_value * feed.scale , 0 ) ), sum ( decode ( pbt.balance_name , 'SSCWT Deductions' , target.result_value * feed.scale , 0 ) )) CF_total_deductions
from
 HR_ORGANIZATION_UNITS       o
,HR_ORGANIZATION_INFORMATION i
,HR_LOCATIONS                l
,hr_soft_coding_keyflex      scl
,per_assignments_f       asg 
,pay_balance_types           pbt
,pay_run_result_values       target
,pay_run_results             rr
,pay_balance_feeds_f         feed
,pay_assignment_actions      assact
,pay_payroll_actions         pact
,per_time_periods            pptp
where
 o.business_group_id = :p_business_group_id
and
 o.ORGANIZATION_ID   = :p_registered_employer_id
and
 i.organization_id = o.organization_id
and
 i.org_information_context = 'NZ_IRD_EMPLOYER' 
and
 l.location_id = o.location_id
and
 FEED.input_value_id = TARGET.input_value_id
and
 feed.balance_type_id = pbt.balance_type_id
and
 nvl(TARGET.result_value,'0') != '0'
and
 TARGET.run_result_id = RR.run_result_id
and
 RR.assignment_action_id = ASSACT.assignment_action_id
and
 ASSACT.payroll_action_id = PACT.payroll_action_id
and
 PACT.effective_date
  between FEED.effective_start_date and FEED.effective_end_date
and
 RR.status in ('P','PA')
and
 PACT.payroll_id = PPTP.payroll_id
and
 PACT.date_earned
  between PPTP.start_date and PPTP.end_date
and
 pptp.regular_payment_date
  between :cp_period_start_date and :cp_period_end_date
and
 asg.assignment_id = assact.assignment_id
and
 asg.effective_start_date <= :cp_period_end_date
and
 asg.effective_end_date >= :cp_period_start_date
and
 asg.effective_start_date = 
   (select max(asg_d.effective_start_date)
    from   per_assignments_f asg_d 
    where asg_d.assignment_id = asg.assignment_id
    and asg_d.effective_start_date <= :cp_period_end_date
  )
and
 scl.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
and
 pbt.balance_name in
  ('PAYE Tax Deductions'
 ,'Child Support Deductions'
 ,'Student Loan Deductions'
 ,'SSCWT Deductions'
 ,'Withholding Tax Deductions Record'
 ,'Retro PAYE Tax Deductions'
 ,'Retro Student Loan Deductions'
 )
 and
 asg.business_group_id = o.business_group_id
and
 (scl.segment1) = to_char(o.ORGANIZATION_ID)
group by
 o.NAME
,l.ADDRESS_LINE_1
,l.ADDRESS_LINE_2
,l.ADDRESS_LINE_3
,l.TOWN_OR_CITY
,l.POSTAL_CODE
,l.COUNTRY
,i.ORG_INFORMATION1