APPS.PAY_CA_EOY_RL1_AMEND_ARCH SQL Statements

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

Line: 67

      select  to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
                          'PRE_ORGANIZATION_ID',ppa.legislative_parameters)),
              to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
                          'PER_ID',ppa.legislative_parameters)),
              to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
                          'SSN',ppa.legislative_parameters)),
              to_number(pay_ca_eoy_rl1_amend_arch.get_parameter(
                          'ASG_SET',ppa.legislative_parameters)),
             to_char(effective_date,'YYYY') ,
             effective_date,
             start_date,
             business_group_id
        from pay_payroll_actions ppa
       where ppa.payroll_action_id = cp_payroll_action_id;

Line: 134

   Purpose   : This returns the select statement that is
               used to create the range rows for the
               Provincial YE Amendment Pre-Process.
   Arguments :
   Notes     : Calls procedure - get_payroll_action_info
  ******************************************************************/
  PROCEDURE eoy_range_cursor(
                    p_payroll_action_id in number
                   ,p_sqlstr           out nocopy  varchar2)
  IS

    ld_end_date          DATE;

Line: 175

         'select distinct asg.person_id person_id
            from per_all_assignments_f asg
           where person_id = ' || to_char(ln_person_id) ||
         ' and :p_payroll_action_id > 0';

Line: 185

           'select distinct paf.person_id
             from hr_assignment_set_amendments asgset,
                  per_all_assignments_f paf
            where assignment_set_id = ' || to_char(ln_asg_set) || '
              and asgset.assignment_id = paf.assignment_id
              and asgset.include_or_exclude = ''I''
              and :payroll_action_id > 0
            order by paf.person_id';

Line: 208

         'select distinct asg.person_id person_id
          from per_all_assignments_f asg
          where person_id =  0
           and :p_payroll_action_id > 0
          order by asg.person_id ';

Line: 214

        hr_utility.trace('No person is selected as ln_person_id and ln_asg_set are null');

Line: 275

   CURSOR c_selected_asg_set(cp_start_person in number
                            ,cp_end_person in number
                            ,cp_asg_set in number) is
      select distinct paf.person_id
      from hr_assignment_set_amendments asgset,
           per_all_assignments_f  paf,
           pay_assignment_actions paa,
           pay_payroll_actions    ppa
      where asgset.assignment_set_id = cp_asg_set
      and asgset.include_or_exclude = 'I'
      and paf.assignment_id = asgset.assignment_id
      and paf.person_id between cp_start_person
                            and cp_end_person
      and ppa.report_type = 'RL1'
      and to_char(ppa.effective_date,'YYYY') = lv_year
      and ppa.business_group_id+0 = ln_business_group_id
      and pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
                     ppa.legislative_parameters) = to_char(ln_pre_org_id)
      and paa.payroll_action_id = ppa.payroll_action_id
      and paa.action_status = 'C'
      and paf.person_id = to_number(paa.serial_number);

Line: 303

     select ppa.report_type,
            paa.assignment_id,
            paa.assignment_action_id
      from pay_payroll_actions ppa,
           pay_assignment_actions paa
      where to_number(paa.serial_number) = cp_person_id
      and pay_ca_eoy_rl1_amend_arch.get_parameter('PRE_ORGANIZATION_ID',
                     ppa.legislative_parameters) = to_char(cp_pre_org_id)
      and paa.action_status = 'C'
      and ppa.business_group_id+0 = ln_business_group_id
      and ppa.payroll_action_id = paa.payroll_action_id
      and ppa.effective_date = cp_effective_date
      and ppa.report_type in ('RL1', 'CAEOY_RL1_AMEND_PP')
      order by paa.assignment_action_id desc;

Line: 319

      select substr(full_name,1,31), substr(national_identifier,1,11)
        from per_all_people_f
       where person_id = cp_person_id
       ORDER BY effective_end_date desc;

Line: 383

               select pay_assignment_actions_s.nextval
                 into ln_rl1amend_asg_action
                 from dual;

Line: 389

               /* Insert into pay_assignment_actions. */
               -- hr_utility.trace('creating asg. action');

Line: 397

               /* Update the serial number column with the person id
                  so that the RL1 Amendment report will not have
                  to do an additional checking against the assignment
                  table */

               -- hr_utility.trace('updating asg. action');

Line: 404

               update pay_assignment_actions aa
                  set aa.serial_number = to_char(p_person_id)
                where  aa.assignment_action_id = ln_rl1amend_asg_action;

Line: 459

        open c_selected_asg_set  (p_start_person_id
                                 ,p_end_person_id
                                 ,ln_asg_set);

Line: 462

        hr_utility.trace('Opened cusor c_selected_asg_set');

Line: 464

           fetch c_selected_asg_set into ln_person_id_sel ;

Line: 465

           if c_selected_asg_set%notfound then
              hr_utility.trace('No Person found for reporting in this chunk');

Line: 470

        hr_utility.trace('ln_person_id after c_selected_asg_set = '||to_char(ln_person_id_sel));

Line: 476

        close c_selected_asg_set;