APPS.PAY_NZ_EDF SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 26

                                               for 2012 NZ statutory updates
*** MINGYHUA     01-MAR-2013  115.8   16239379 Added start date to confine the start and
***                                            end date of report; Excluded ESCT deductions

Line: 42

                 select a range of assignments eligible for archival.
  --------------------------------------------------------------------
*/

PROCEDURE range_code(p_payroll_action_id IN         pay_payroll_actions.payroll_action_id%TYPE,
                     p_sql               OUT NOCOPY VARCHAR2)
IS

  l_proc_name            VARCHAR2(100) := g_proc_name ||'range_code';

Line: 60

    p_sql := ' select distinct p.person_id'                         ||
             ' from   per_people_f p,'                              ||
                    ' pay_payroll_actions pa'                       ||
             ' where  pa.payroll_action_id = :payroll_action_id'    ||
             ' and    p.business_group_id = pa.business_group_id'   ||
             ' order by p.person_id';

Line: 258

   SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
          pay_core_utils.get_parameter('REGISTERED_EMPLOYER',ppa.legislative_parameters),
          pay_core_utils.get_parameter('START_DATE',ppa.legislative_parameters),
          pay_core_utils.get_parameter('END_DATE',ppa.legislative_parameters)
   FROM  pay_payroll_actions ppa
   WHERE ppa.payroll_action_id = c_payroll_action_id;

Line: 268

   SELECT name
   FROM   hr_nz_tax_unit_v
   WHERE  business_group_id = c_business_group_id
   AND    tax_unit_id = c_registered_employer_id;

Line: 275

   SELECT name
   FROM   per_business_groups
   WHERE  business_group_id = c_business_group_id
   AND    legislation_code = 'NZ';

Line: 284

   SELECT TRUNC(effective_date)
   FROM   fnd_sessions
   WHERE  session_id = userenv('SESSIONID');

Line: 296

   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))
     + SUM(decode(pbt.balance_name,'Payroll Giving Tax Credits',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))
     + SUM(decode(pbt.balance_name,'SLCIR Deduction',target.result_value * feed.scale,0))
     + SUM(decode(pbt.balance_name,'SLBOR Deduction',target.result_value * feed.scale,0))) student_loan_deductions,
     SUM(decode(pbt.balance_name,'SSCWT Deductions'  ,target.result_value * feed.scale,0)) sscwt_deductions,
     SUM(decode(pbt.balance_name,'KiwiSaver Employee Contributions',target.result_value * feed.scale,0)) kiwisaver_employee_deductions,
     SUM(decode(pbt.balance_name,'KiwiSaver Employer Contributions',target.result_value * feed.scale,0)) kiwisaver_employer_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
   WHERE o.business_group_id = c_business_group_id
     AND o.organization_id = c_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.effective_date BETWEEN c_period_start_date AND c_period_end_date
     AND asg.assignment_id = assact.assignment_id
     AND asg.effective_start_date <= c_period_end_date
     AND asg.effective_end_date >= c_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 <= c_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' , 'KiwiSaver Employee Contributions' ,
                               'KiwiSaver Employer Contributions','Payroll Giving Tax Credits' ,'SLCIR Deduction','SLBOR Deduction')
     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;