ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716B51A9E05362FB09050512 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 Y PAY PYNZEI_XML US PAY NZ Generate EI Payday Imported from BI Publisher Description: The eText output of NZ Payday Report Application: Payroll Source: NZ Generate EI Payday Report Short Name: PYNZEI_XML DB package: BI Publisher :p_business_group_id :p_legal_emp_id :p_payroll_action_id