PAY Payroll Reconciliation (New Zealand)

/* 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 */