APPS.PAY_ZA_MTC_PKG SQL Statements

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

Line: 22

procedure update_certificate_number
(
   p_errmsg        out nocopy varchar2,
   p_errcode       out nocopy varchar2,
   p_bgid          in  number,
   p_payroll_id    in  number,
   p_tax_year      in  varchar2,
   p_pay_action_id in  varchar2,
   p_asg_id        in  number,
   p_asg_action_id in  number,
   p_tax_cert_no   in  varchar2
)  is

-- Cursor used to update Tax Certificate Numbers
cursor c_tax_cert_no is
select serial_number
from   pay_assignment_actions
where  assignment_action_id = p_asg_action_id;

Line: 44

   select paa.serial_number, paa.assignment_action_id
   from   pay_assignment_actions paa,
          pay_payroll_actions    ppa
   where  ppa.business_group_id = p_bgid
   and    ppa.report_type = 'ZA_IRP5'
   and    ppa.action_type = 'X'
   and    substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
          = p_tax_year
   and    ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
   and    paa.payroll_action_id = ppa.payroll_action_id
   and    paa.assignment_id = p_asg_id
   and    paa.action_sequence =
   (
      select max(paa2.action_sequence)
      from   pay_assignment_actions paa2
      where  paa2.payroll_action_id = ppa.payroll_action_id
      and    paa2.assignment_id = p_asg_id
   );

Line: 66

   select paa.serial_number, paa.assignment_action_id
   from   pay_assignment_actions paa,
          pay_payroll_actions    ppa,
          ff_database_items      dbi,
          ff_archive_items       arc
   where  ppa.business_group_id = p_bgid
   and    ppa.report_type = 'ZA_IRP5'
   and    ppa.action_type = 'X'
   and    substr(ppa.legislative_parameters, instr(ppa.legislative_parameters, 'TAX_YEAR') + 9, 4)
          = p_tax_year
   and    ppa.payroll_action_id <> substr(p_pay_action_id, 28, 9)
   and    paa.payroll_action_id = ppa.payroll_action_id
   and    paa.assignment_id = p_asg_id
   and    dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
   and    arc.user_entity_id = dbi.user_entity_id
   and    arc.context1 = to_char(paa.assignment_action_id)
   and    arc.value = p_period
   and    paa.action_sequence <>
   (
      select max(paa2.action_sequence)
      from   pay_assignment_actions paa2
      where  paa2.payroll_action_id = ppa.payroll_action_id
      and    paa2.assignment_id = p_asg_id
   );

Line: 126

        Select decode(count(*), 0 ,'Y', 'N')
           into   l_lump_sum_ind
            From      pay_payroll_actions    ppa_arch,
              pay_assignment_actions paa_arch
        where paa_arch.assignment_action_id = p_asg_action_id
        and   ppa_arch.payroll_action_id    = paa_arch.payroll_action_id
        and   paa_arch.assignment_action_id =
        (
           select max(paa.assignment_action_id)
           from   pay_assignment_actions paa
           where  paa.payroll_action_id = ppa_arch.payroll_action_id
           and   paa.assignment_id = paa_arch.assignment_id
        ) ;

Line: 183

                        update pay_assignment_actions
                        set    serial_number = '&&' || l_old_num
                        where  assignment_action_id = l_old_aa;

Line: 200

            select nvl(arc.value, '')
            into   l_period
            from   ff_database_items dbi,
                   ff_archive_items  arc
            where  dbi.user_name = 'A_PAY_PROC_PERIOD_ID'
            and    arc.user_entity_id = dbi.user_entity_id
            and    arc.context1 = p_asg_action_id;

Line: 245

                        update pay_assignment_actions
                        set    serial_number = '&&' || l_old_num
                        where  assignment_action_id = l_old_aa;

Line: 262

         update pay_assignment_actions
         set    serial_number        = p_tax_cert_no
         where  assignment_action_id = p_asg_action_id;

Line: 279

end update_certificate_number;