ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716651A9E05362FB09050512 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 Y PAY PYNZ345_XML US PAY Inland Revenue Remittance Certificate Imported from BI Publisher Description: Inland Revenue Remittance Certificate (New Zealand) Application: Payroll Source: Inland Revenue Remittance Certificate (XML) Short Name: PYNZ345_XML DB package: PAY_PYNZ345_XMLP_PKG BI Publisher :cp_currency_code :cp_currency_format :cp_period_end_date :cp_period_start_date :p_business_group_id :p_conc_request_id :p_period_end_date :p_registered_employer_id 1 -20 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group ID 2 10 :p_registered_employer_id LOV Oracle HR_NZ_REGISTERED_EMPLOYER Y select tu.tax_unit_id id, substr(tu.name,1,150) value, null description from hr_nz_tax_unit_v tu where tu.business_group_id=fnd_global.per_business_group_id order by tu.name Y US Registered Employer 3 20 :p_period_end_date Date SELECT fnd_date.date_to_displaydate(PAY_NZ_TAX.HALF_MONTH_END(effective_date)) from fnd_sessions where session_id = userenv('SESSIONID') Y US Period End Date