ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4714351A9E05362FB09050512Y select A.FULL_NAME,A.ASSIGNMENT_ID,A.assignment_number EMPLOYEE_NUMBER, sum(decode(A.BALANCE_NAME,'CEC Complying',A.BAL_VALUE,0)) "CEC_Complying", sum(decode(A.BALANCE_NAME,'CEC KiwiSaver',A.BAL_VALUE,0)) "CEC_Kiwisaver", sum(decode(A.BALANCE_NAME,'ETC Complying',A.BAL_VALUE,0)) "ETC_Complying", sum(decode(A.BALANCE_NAME,'ETC KiwiSaver',A.BAL_VALUE,0)) "ETC_Kiwisaver", sum(decode(A.BALANCE_NAME,'Ordinary Taxable Earnings',A.BAL_VALUE,0)) "Ordinary_Tax_Ear" from ( select ppf.full_name FULL_NAME,paf.assignment_id ASSIGNMENT_ID,paf.assignment_number,pbt.balance_name BALANCE_NAME,pay_nz_cec_report_pkg.PAY_NZ_GET_BAL_VALUE(paf.assignment_id,pdb.defined_balance_id,:p_start_date,:p_end_date) BAL_VALUE from per_people_f ppf, per_assignments_f paf, pay_balance_types pbt, pay_defined_balances pdb, pay_balance_dimensions pbd where (paf.assignment_id=:p_assignment_id or :p_assignment_id is null) and paf.business_group_id=:per_business_group_id and pbt.balance_name in ('CEC Complying','CEC KiwiSaver','ETC Complying','ETC KiwiSaver','Ordinary Taxable Earnings') and pbd.dimension_name ='_ASG_RUN' and paf.assignment_id in (select paa.assignment_id from pay_assignment_actions paa, pay_payroll_actions ppa where ppa.action_status ='C' and (paa.tax_unit_id = :p_registered_employer_id) and ppa.action_type in ('R','Q','I','B','V') and ppa.effective_date between :p_start_date and :p_end_date and paa.payroll_action_id=ppa.payroll_action_id and paf.assignment_id=paa.assignment_id ) and ppf.person_id = paf.person_id and pbt.legislation_code='NZ' and pbt.legislation_code=pbd.legislation_code and pbt.balance_type_id=pdb.balance_type_id and pbd.balance_dimension_id=pdb.balance_dimension_id and :p_end_date between ppf.effective_start_date and ppf.effective_end_date and paf.effective_end_date = ( select max(effective_end_date) from per_assignments_f WHERE assignment_id = paf.assignment_id and effective_end_date >= :p_start_date and effective_start_date <= :p_end_date) ) A group by A.FULL_NAME,A.ASSIGNMENT_ID,A.assignment_number PAY PAYNZCECETC US PAY New Zealand CEC Contributions Imported from BI Publisher Application: Payroll Source: New Zealand CEC Contributions Report Short Name: PAYNZCECETC DB package: pay_nz_cec_report_pkg BI Publisher :p_assignment_id :p_end_date :p_registered_emp_name :p_registered_employer_id :p_start_date :per_business_group_id 1 -20 :p_registered_emp_name Char select substr(TU.NAME,1,150) from HR_NZ_TAX_UNIT_V TU WHERE TU.BUSINESS_GROUP_ID =fnd_global.PER_BUSINESS_GROUP_ID AND TU.TAX_UNIT_ID =:$FLEX$.P_REGISTERED_EMPLOYER_ID ORDER BY TU.NAME US Registered Employer Name 2 -10 :per_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') US PER_BUSINESS_GROUP_ID 3 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 select substr(TU.NAME,1,150) from HR_NZ_TAX_UNIT_V TU WHERE TU.BUSINESS_GROUP_ID =fnd_global.PER_BUSINESS_GROUP_ID Y US P_REGISTERED_EMPLOYER_ID Registered Employer 4 20 :p_start_date Date Y US P_START_DATE 5 30 :p_end_date Date Y US P_END_DATE 6 40 :p_assignment_id LOV Oracle PY_NZ_CEC_ASS_ID Y select paa.assignment_id id, paa.assignment_number value, ppf.full_name description from per_people_f ppf,per_assignments_f paa,hr_organization_units hou,hr_organization_information hoi,hr_soft_coding_keyflex hsc where ppf.business_group_id=fnd_global.per_business_group_id and ppf.person_id=paa.person_id and org_information_context='NZ_IRD_EMPLOYER' and to_char(hoi.organization_id)=hsc.segment1 and hsc.soft_coding_keyflex_id=paa.soft_coding_keyflex_id and hou.organization_id=:$flex$.p_registered_employer_id and hou.business_group_id=ppf.business_group_id and hou.organization_id=hoi.organization_id and paa.effective_start_date=(select max(paa1.effective_start_date) from per_assignments_f paa1, hr_soft_coding_keyflex hsck where paa1.assignment_id=paa.assignment_id and hsck.soft_coding_keyflex_id=paa1.soft_coding_keyflex_id and hsck.segment1=:$flex$.p_registered_employer_id and paa1.payroll_id is not null and paa1.effective_end_date>=to_date(:$flex$.p_start_date,'YYYY/MM/DD HH24:MI:SS') and paa1.effective_start_date<=to_date(:$flex$.p_end_date,'YYYY/MM/DD HH24:MI:SS') ) and ppf.effective_end_date=to_date('31-12-4712','DD-MM-YYYY') order by value,description US Assignment Number