PAY NZ Generate EI Payday

select x.ER_IRD_NUMBER
      ,x.ER_CONTACT_NAME
      ,x.ER_PHONE_NUMBER
      ,x.ER_EMAIL_ADDRESS
      ,x.INTERMEDIARY_IRD_NO
      ,y.PAYDATE
      ,y.FRI
      ,y.NRI
      ,y.TOTAL_EMP_LINES
      ,y.TOTAL_PAYE_TAX
      ,y.TOTAL_CS_DEDUCTIONS
      ,y.TOTAL_SL_DEDUCTIONS
      ,y.TOTAL_KS_DEDUCTIONS
      ,y.TOTAL_KS_ER_DEDUCTIONS
      ,y.TOTOAL_ESCT_DEDUCTED
      ,y.TOTAL_DEDUCTIONS
      ,y.TOTAL_TAX_CREDITS
      ,y.TOTAL_GROSS_EARNINGS
      ,y.TOTAL_EARNINGS_NOT_ACC_EP
      ,y.PACKAGE_VER
      ,y.IR_FORM_VER
from 
(SELECT
     lpad(replace(i.org_information1,'-',NULL), 9, '0') er_ird_number,
     replace(substr(i.org_information2, 1, 20),',',' ') er_contact_name,
     substr(trim(leading '0' from trim(replace(replace(i.org_information3,'(',NULL),')',NULL))),1,12) er_phone_number,
     decode(instr(substr(i.org_information5,
                    decode(sign(instr(i.org_information5,'@')),0,
                    length(i.org_information5)+1,instr(i.org_information5,'@'))), '.',1,2), 0, 
            decode(instr(substr(i.org_information5,instr(i.org_information5,'@')),'.'),0,null,i.org_information5),
            null) er_email_address,
     lpad(replace(i.org_information8,'-',NULL), 9, '0') intermediary_ird_no
    FROM
     hr_organization_units oi,
     hr_organization_information i
   WHERE oi.business_group_id = :P_BUSINESS_GROUP_ID
     AND oi.organization_id = :P_LEGAL_EMP_ID
     AND i.organization_id = oi.organization_id
     AND i.org_information_context = 'NZ_IRD_EMPLOYER'
) x,
(select to_char(to_date(pay_core_utils.get_parameter ('END_DATE',ppa.legislative_parameters), 'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD') paydate,
       pay_core_utils.get_parameter ('FRI',ppa.legislative_parameters) fri,
       pay_core_utils.get_parameter ('NRI',ppa.legislative_parameters) nri,
       total_pei.total_emp_lines,
       total_pei.total_paye_tax,
       total_pei.total_cs_deductions,
       total_pei.total_sl_deductions,
       total_pei.total_ks_deductions,
       total_pei.total_ks_er_deductions,
       total_pei.totoal_esct_deducted,
       total_pei.total_deductions,
       total_pei.total_tax_credits,
       total_pei.total_gross_earnings,
       total_pei.total_earnings_not_acc_ep,
       'Oracle HRMS V12' package_ver,
       '0001' ir_form_ver
 from pay_payroll_actions ppa,
      (select  pei.payroll_action_id,
                sum(pei.X_PEI_ARC_LINE_NO) total_emp_lines,
               sum(pei.X_PEI_EMP_PAYE_DEDUCTIONS + pei.X_PEI_EMP_PAYE_DEDUCTIONS_ESS) total_paye_tax,
               sum(pei.X_PEI_EMP_CS_DEDUCTIONS) total_cs_deductions,
               sum(pei.X_PEI_EMP_SL_DEDUTIONS + pei.X_PEI_EMP_SL_DEDUTIONS_ESS + 
                      pei.X_PEI_EMP_SLCIR_DEDUCTION + pei.X_PEI_EMP_SLBOR_DEDUCTION) total_sl_deductions,
               sum(pei.X_PEI_EMP_KS_DEDUCTIONS) total_ks_deductions,
               sum(pei.X_PEI_EMP_KS_ER_DEDUCTIONS) total_ks_er_deductions,
               sum(pei.X_PEI_EMP_ESCT_DEDUCTED) totoal_esct_deducted,
               sum(pei.X_PEI_EMP_PAYE_DEDUCTIONS + pei.X_PEI_EMP_PAYE_DEDUCTIONS_ESS +
                      pei.X_PEI_EMP_CS_DEDUCTIONS + pei.X_PEI_EMP_ESCT_DEDUCTED +
                      pei.X_PEI_EMP_SL_DEDUTIONS + pei.X_PEI_EMP_SL_DEDUTIONS_ESS +
                      pei.X_PEI_EMP_SLCIR_DEDUCTION + pei.X_PEI_EMP_SLBOR_DEDUCTION +
                      pei.X_PEI_EMP_KS_DEDUCTIONS + pei.X_PEI_EMP_KS_ER_DEDUCTIONS - pei.X_PEI_EMP_TAX_CREDITS) total_deductions,
               sum(pei.X_PEI_EMP_TAX_CREDITS) total_tax_credits,
               sum(pei.X_PEI_EMP_GROSS_EARNINGS) total_gross_earnings,
               sum(pei.X_PEI_EMP_EARNINGS_NOT_LIABLE) total_earnings_not_acc_ep
         from pay_nz_pei_archive_v pei
        where pei.payroll_action_id = :P_PAYROLL_ACTION_ID
          and pei.X_PEI_ARC_REPORT_FLAG = 1
         group by pei.payroll_action_id) total_pei
where ppa.payroll_action_id = :P_PAYROLL_ACTION_ID
  and ppa.payroll_action_id = total_pei.payroll_action_id
union all
select to_char(to_date(pay_core_utils.get_parameter ('END_DATE',ppa.legislative_parameters), 'YYYY/MM/DD HH24:MI:SS'), 'YYYYMMDD') paydate,
       pay_core_utils.get_parameter ('FRI',ppa.legislative_parameters) fri,
       pay_core_utils.get_parameter ('NRI',ppa.legislative_parameters) nri,
       0 total_emp_lines,
       0 total_paye_tax,
       0 total_cs_deductions,
       0 total_sl_deductions,
       0 total_ks_deductions,
       0 total_ks_er_deductions,
       0 totoal_esct_deducted,
       0 total_deductions,
       0 total_tax_credits,
       0 total_gross_earnings,
       0 total_earnings_not_acc_ep,
       'Oracle HRMS V12' package_ver,
       '0001' ir_form_ver
 from pay_payroll_actions ppa
 where payroll_action_id = :P_PAYROLL_ACTION_ID
   and pay_core_utils.get_parameter ('NRI',ppa.legislative_parameters) = 'Y'
) y