ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4716D51A9E05362FB09050512 /* bug 18114024 begin */ select detail.*, summary.*, emp_details.* from (select distinct pap.full_name, pap.order_name, pap.employee_number, decode(:P_sort_order,'F', pap.full_name, pap.employee_number) Ord, -- ppp.proposed_salary_n Salary, pec.default_priority, pet.processing_priority, decode(instr(pec.classification_name,'Reimbursements'),0,pec.classification_name, 'Non Taxable Allowances') classification_name, nvl(pet.reporting_name, pet.element_name) element_name, sum(pay_nz_rec_pkg.value_ptd(ppa.payroll_action_id, paa.assignment_id, pet.element_type_id, prr.run_result_id)) Value_PTD, pay_nz_rec_pkg.value_ytd(max(ppa.payroll_action_id), max(paa.assignment_id), max(pet.element_type_id)) Value_YTD, --pac.assignment_action_id, paa.assignment_id, -- paa.normal_hours standard_assignment_hours, -- pgd.name grade, -- pou.name organization, sum(pay_nz_rec_pkg.get_element_payment_hours (pac.assignment_action_id, pet.element_type_id, paa.pay_basis_id, prr.run_result_id, ppa.effective_date)) hours, round(sum(pay_nz_rec_pkg.value_ptd(ppa.payroll_action_id, paa.assignment_id, pet.element_type_id, prr.run_result_id))/ decode(sum(pay_nz_rec_pkg.get_element_payment_hours(pac.assignment_action_id, pet.element_type_id, paa.pay_basis_id, prr.run_result_id, ppa.effective_date)), 0, null, sum(pay_nz_rec_pkg.get_element_payment_hours(pac.assignment_action_id, pet.element_type_id, paa.pay_basis_id, prr.run_result_id, ppa.effective_date))), 2) rate from per_all_people_f pap, per_all_assignments_f paa, -- pay_all_payrolls_f pay, -- per_time_periods ptp, pay_payroll_actions ppa, pay_assignment_actions pac, pay_run_results prr, -- pay_run_result_values prrv, pay_element_classifications pec, pay_element_types_f pet, pay_input_values_f piv -- per_pay_proposals ppp -- per_grades pgd, -- hr_all_organization_units pou where paa.business_group_id = :p_business_group_id and piv.name = :cp_input_value_name and piv.uom = :cp_uom and pap.person_id = paa.person_id -- and pay.payroll_id = paa.payroll_id -- and pay.payroll_id = ptp.payroll_id -- and pay.payroll_id = ppa.payroll_id -- and ptp.time_period_id = ppa.time_period_id and ppa.payroll_action_id = pac.payroll_action_id and paa.assignment_id = pac.assignment_id and pac.assignment_action_id = prr.assignment_action_id -- and prr.run_result_id = prrv.run_result_id and pec.legislation_code = :CF_leg_c and pec.classification_id = pet.classification_id and pet.element_type_id = prr.element_type_id and pet.element_type_id = piv.element_type_id and pet.element_type_id <> (select element_type_id from pay_element_types_f where element_name = 'SSCWT on KiwiSaver Employer Contribution') and pet.element_type_id <> (select element_type_id from pay_element_types_f where element_name = 'Retro SSCWT on KiwiSaver Employer Contribution') -- and piv.input_value_id = prrv.input_value_id -- and paa.assignment_id = ppp.assignment_id -- and paa.grade_id = pgd.grade_id(+) -- and paa.organization_id = pou.organization_id(+) and ppa.effective_date between paa.effective_start_date and paa.effective_end_date and ppa.effective_date between pap.effective_start_date and pap.effective_end_date and ppa.effective_date between pet.effective_start_date and pet.effective_end_date and ppa.effective_date between piv.effective_start_date and piv.effective_end_date -- and ppa.effective_date between pay.effective_start_date and pay.effective_end_date &where_clause group by pap.full_name,pap.order_name,pap.employee_number,decode(:P_sort_order,'F', pap.full_name, pap.employee_number),pec.default_priority,pet.processing_priority,decode(instr(pec.classification_name,'Reimbursements'),0,pec.classification_name,'Non Taxable Allowances'),nvl(pet.reporting_name, pet.element_name),paa.assignment_id order by 4,7,2,5,6,8) detail, (select pac.assignment_id, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Gross Income')) Gross_Income, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Tax Deductions')) Tax_Deductions, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Pre Tax Deductions')) Pre_Tax_Deductions, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Other Deductions')) Other_Deductions, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Non Taxable Allowances')) Non_Taxable_Allowances, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Net Payment')) Net_Payment, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Employer Charges')) Employer_Charges, sum(pay_nz_rec_pkg.get_nz_rec_balances (pac.assignment_action_id, 'Employer Specified Super')) Employer_Specified_Super, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Gross Income YTD') Gross_Income_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Tax Deductions YTD') Tax_Deductions_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Pre Tax Deductions YTD') Pre_Tax_Deductions_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Other Deductions YTD') Other_Deductions_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Non Taxable Allowances YTD') Non_Taxable_Allowances_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Net Payment YTD') Net_Payment_YTD, pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Employer Charges YTD') Employer_Charges_YTD , pay_nz_rec_pkg.get_nz_rec_balances (max(pac.assignment_action_id), 'Employer Specified Super YTD') Employer_Specified_Super_YTD from pay_payroll_actions ppa, pay_payrolls_f ppf, pay_assignment_actions pac where ppa.current_task is null and ppa.business_group_id = :p_business_group_id and ppf.payroll_id = ppa.payroll_id and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date &where_clause and pac.payroll_action_id = ppa.payroll_action_id group by pac.assignment_id) summary, (select pay_nz_rec_pkg.get_grade(max(pac.assignment_action_id),pac.assignment_id) grade, pay_nz_rec_pkg.get_normal_hours(max(pac.assignment_action_id),pac.assignment_id) standard_assignment_hours, pay_nz_rec_pkg.get_org_name(max(pac.assignment_action_id),pac.assignment_id) organization, pay_nz_rec_pkg.get_salary(max(pac.assignment_action_id),pac.assignment_id) SALARY, pac.assignment_id from pay_assignment_actions pac,pay_payroll_actions ppa where pac.payroll_action_id = ppa.payroll_action_id and ppa.business_group_id+0 = :p_business_group_id &where_clause group by pac.assignment_id )emp_details where summary.assignment_id = detail.assignment_id and emp_details.assignment_id = detail.assignment_id order by 4,7,2,5,6,8 /* bug 18114024 end */ Y PAY PYNZREC_XML US PAY Payroll Reconciliation (New Zealand) Imported from BI Publisher Description: Payroll Reconciliation (New Zealand) Application: Payroll Source: Payroll Reconciliation (New Zealand) (XML) Short Name: PYNZREC_XML DB package: PAY_PYNZREC_XMLP_PKG BI Publisher &where_clause :cf_leg_c :cp_input_value_name :cp_report_name :cp_uom :p_business_group_id :p_conc_request_id :p_end_date :p_payroll_action_id :p_sort_order :p_start_date :where_clause 1 -40 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group ID 2 -30 :p_dummy_payroll_run Char Select 1 from dual where :$flex$.payroll_run:NULL is null US Dummy_Payroll_Run 3 -10 :p_dummy_check_payroll_run_date Char SELECT decode(:$flex$.payroll_run:NULL,NULL,decode(:$flex$.start_date:NULL,NULL,NULL,decode(:$flex$.end_date:NULL,NULL,NULL,13)) ,13) from dual Y US Payroll Run or Start Date/End Date 4 10 :p_payroll_action_id LOV Oracle PY_NZ_REC_RUN Y select action.payroll_action_id id, fnd_date.date_to_chardate(action.effective_date)|| ' (' ||to_char(action.request_id) || ')' value, hr_payrolls.display_period_name(action.payroll_action_id) description from pay_payroll_actions action,pay_payrolls_f payroll,hr_lookups lookup,hr_assignment_sets asg_set,pay_element_sets_tl estl where action.current_task is null and action.action_type in ('R','Q') and action.business_group_id+0= fnd_global.per_business_group_id and payroll.payroll_id=action.payroll_id and action.effective_date between payroll.effective_start_date and payroll.effective_end_date and lookup.lookup_type='ACTION_STATUS' and lookup.lookup_code=action.action_status and asg_set.assignment_set_id (+)=action.assignment_set_id and estl.element_set_id (+)=action.element_set_id and (estl.language is null or estl.language=userenv('LANG')) order by action.effective_date desc,action.action_sequence desc US Payroll Run 5 20 :p_start_date Date US Start Date 6 30 :p_end_date Date US End Date 7 40 :p_sort_order LOV Oracle PY_NZ_REC_REPORT_SORT_BY Y select lookup_code id, meaning value, null description from hr_lookups where lookup_type='NZ_REC_REPORT_SORT_BY' and enabled_flag='Y' order by meaning Y US Sort Order