APPS.PAY_GENERIC_UPGRADE SQL Statements

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

Line: 23

       select status
         into l_status
         from pay_upgrade_status
        where upgrade_definition_id = p_upg_def_id
          and business_group_id     = p_bus_grp;

Line: 41

       update pay_upgrade_status
          set status = p_status
        where upgrade_definition_id = p_upg_def_id
          and business_group_id     = p_bus_grp;

Line: 49

           insert into pay_upgrade_status
                         (upgrade_definition_id,
                          status,
                          business_group_id)
           values (p_upg_def_id,
                   p_status,
                   p_bus_grp);

Line: 68

       select status
         into l_status
         from pay_upgrade_status
        where upgrade_definition_id = p_upg_def_id
          and legislation_code     = p_leg_code;

Line: 86

       update pay_upgrade_status
          set status = p_status
        where upgrade_definition_id = p_upg_def_id
          and legislation_code     = p_leg_code;

Line: 94

           insert into pay_upgrade_status
                         (upgrade_definition_id,
                          status,
                          legislation_code)
           values (p_upg_def_id,
                   p_status,
                   p_leg_code);

Line: 113

       select status
         into l_status
         from pay_upgrade_status
        where upgrade_definition_id = p_upg_def_id
          and legislation_code is null
          and business_group_id is null;

Line: 132

       update pay_upgrade_status
          set status = p_status
        where upgrade_definition_id = p_upg_def_id
          and legislation_code is null
          and business_group_id is null;

Line: 141

           insert into pay_upgrade_status
                         (upgrade_definition_id,
                          status
                          )
           values (p_upg_def_id,
                   p_status
                   );

Line: 174

select pud.upgrade_definition_id
from pay_upgrade_definitions pud
where pud.upgrade_level = 'B' -- Business Group
and (   pud.legislation_code = p_legislation
     or (    pud.legislation_code is null
         and (    nvl(pud.legislatively_enabled, 'N') = 'N'
               or (    nvl(pud.legislatively_enabled, 'N') = 'Y'
                   and exists (select ''
                                 from pay_upgrade_legislations pul
                                where pul.upgrade_definition_id
                                             = pud.upgrade_definition_id
                                  and pul.legislation_code = p_legislation
                              )
                  )
              )
         )
      );

Line: 196

select pud.upgrade_definition_id
from pay_upgrade_definitions pud
where pud.upgrade_level = 'L' -- Business Group
and (   pud.legislation_code = p_legislation
     or (    pud.legislation_code is null
         and nvl(pud.legislatively_enabled, 'N') = 'Y'
         and exists (select ''
                       from pay_upgrade_legislations pul
                      where pul.upgrade_definition_id
                                        = pud.upgrade_definition_id
                        and pul.legislation_code = p_legislation
                   )
         )
     )
and not exists (select 1
                  from per_business_groups_perf
                where legislation_code = p_legislation
                   and business_group_id <> p_business_group
               )
and not exists (select 1                                       --Bug 7296843
                   from hr_organization_information hoi_1 ,    --'not exists' validation being done against the base table itself.
                        hr_organization_information hoi_2
                where hoi_1.organization_id <> p_business_group
                   and hoi_1.organization_id = hoi_2.organization_id
                   and hoi_1.org_information9 = p_legislation
                   and hoi_2.org_information_context ='CLASS'
                   and hoi_1.org_information_context ='Business Group Information'
                   and hoi_2.org_information1 = 'HR_BG'
                   and hoi_2.org_information2='N'
                );

Line: 230

select pud.upgrade_definition_id
from pay_upgrade_definitions pud
where pud.upgrade_level = 'G'
and nvl(pud.legislatively_enabled, 'N') = 'N'
and not exists (select 1
                  from per_business_groups_perf
                 where business_group_id <> p_business_group
               );

Line: 288

     Purpose   : This returns the select statement that is used to created the
                 range rows.
     Arguments :
     Notes     :
  */
procedure range_cursor (pactid in number, sqlstr out nocopy varchar2) is
l_upg_def_nm pay_upgrade_definitions.short_name%type;

Line: 314

select rf.report_type,rf.report_format_mapping_id
into l_report_type,l_rep_id
from pay_report_format_mappings_f rf,
     pay_payroll_actions pact
where pact.payroll_action_id=pactid
and   rf.report_type =pact.report_type
and   rf.report_qualifier=pact.report_qualifier
and   rf.report_category=pact.report_category
and   pact.effective_date between rf.effective_start_date
                            and rf.effective_end_date;

Line: 329

   select  'B',rg.thread_level,rg.legislation_code
   into l_upg_level, l_thread_level, l_leg_code
   from pay_report_groups rg,pay_payroll_actions ppa
   where rg.report_format_mapping_id=l_rep_id
   and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
  and ppa.payroll_action_id=pactid;

Line: 338

  select pay_core_utils.get_parameter('UPG_DEF_NAME',
                                      ppa.legislative_parameters),
         ppa.business_group_id,
         pbg.legislation_code
    into l_upg_def_nm,
         l_bus_grp_id,
         l_leg_code
    from pay_payroll_actions ppa,
         per_business_groups_perf pbg
   where ppa.payroll_action_id = pactid
     and pbg.business_group_id = ppa.business_group_id;

Line: 350

   select upgrade_level,
          upgrade_definition_id,
          threading_level
     into l_upg_level,
          l_upg_def_id,
          l_thread_level
     from pay_upgrade_definitions
    where short_name = l_upg_def_nm;

Line: 393

         sqlstr := 'select  distinct asg.person_id
                  from
                          per_all_assignments_f      asg,
                          pay_payroll_actions    pa1
                   where  pa1.payroll_action_id    = :payroll_action_id
                   and    asg.business_group_id    = pa1.business_group_id
                  order by asg.person_id';

Line: 404

       sqlstr := 'select  distinct asg.person_id
                  from
                          per_all_assignments_f      asg,
                          pay_payroll_actions    pa1,
                          per_business_groups_perf    pbg1,
                          per_business_groups_perf    pbg
                   where  pa1.payroll_action_id    = :payroll_action_id
                   and    pbg.business_group_id    = pa1.business_group_id
                   and    pbg1.legislation_code    = pbg.legislation_code
                   and    asg.business_group_id    = pbg1.business_group_id
                  order by asg.person_id';

Line: 418

       sqlstr := 'select  distinct asg.person_id
                  from
                          per_all_assignments_f      asg,
                          pay_payroll_actions    pa1
                   where  pa1.payroll_action_id    = :payroll_action_id
                  order by asg.person_id';

Line: 437

       sqlstr := 'select  distinct pet.element_type_id
                  from
                          pay_element_types_f     pet,
                          pay_payroll_actions    pa1
                   where  pa1.payroll_action_id    = :payroll_action_id
                   and    pet.business_group_id    = pa1.business_group_id
                  order by pet.element_type_id';

Line: 447

       sqlstr := 'select  distinct pet.element_type_id
                  from
                          pay_element_types_f      pet,
                          pay_payroll_actions    pa1,
                          per_business_groups_perf    pbg1,
                          per_business_groups_perf    pbg
                   where  pa1.payroll_action_id    = :payroll_action_id
                   and    pbg.business_group_id    = pa1.business_group_id
                   and    pbg1.legislation_code    = pbg.legislation_code
                   and    (pet.business_group_id = pbg1.business_group_id
                           or pet.legislation_code = pbg1.legislation_code)
                  order by pet.element_type_id';

Line: 462

       sqlstr := 'select  distinct pet.element_type_id
                  from
                          pay_element_types_f        pet,
                          pay_payroll_actions    pa1
                   where  pa1.payroll_action_id    = :payroll_action_id
                  order by pet.element_type_id';

Line: 571

     select pay_assignment_actions_s.nextval
       into l_action_id
       from dual;

Line: 600

  select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
         distinct ppf.person_id
    from per_all_people_f ppf,
         pay_payroll_actions ppa
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = ppf.business_group_id
     and ppf.person_id between cp_stperson and cp_endperson;

Line: 612

  select distinct paf.assignment_id
    from
         per_all_assignments_f      paf,
         pay_payroll_actions    ppa
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = paf.business_group_id
     and paf.person_id between cp_stperson and cp_endperson;

Line: 623

  select distinct paf.assignment_id
    from
         per_all_assignments_f      paf,
         pay_payroll_actions    ppa,
         pay_population_ranges ppr
   where ppr.chunk_number = c_chunk
     and ppr.payroll_action_id = ppa.payroll_action_id
     and ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = paf.business_group_id
     and paf.person_id = ppr.person_id;

Line: 638

  select distinct pet.element_type_id
    from
         pay_element_types_f    pet,
         pay_payroll_actions    ppa
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = pet.business_group_id
     and pet.element_type_id between cp_stetid and cp_endetid;

Line: 650

  select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
         distinct ppf.person_id
    from per_all_people_f ppf,
         pay_payroll_actions ppa,
         per_business_groups_perf pbg,
         per_business_groups_perf pbg1
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = pbg.business_group_id
     and pbg.legislation_code = pbg1.legislation_code
     and pbg1.business_group_id = ppf.business_group_id
     and ppf.person_id between cp_stperson and cp_endperson;

Line: 666

  select distinct paf.assignment_id
    from
         per_all_assignments_f      paf,
         pay_payroll_actions    ppa,
         per_business_groups_perf    pbg,
         per_business_groups_perf    pbg1
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = pbg.business_group_id
     and pbg.legislation_code = pbg1.legislation_code
     and pbg1.business_group_id = paf.business_group_id
     and paf.person_id between cp_stperson and cp_endperson;

Line: 682

  select distinct pet.element_type_id
    from
         pay_element_types_f    pet,
         pay_payroll_actions    ppa,
         per_business_groups_perf    pbg,
         per_business_groups_perf    pbg1
   where ppa.payroll_action_id = cp_pactid
     and ppa.business_group_id = pbg.business_group_id
     and pbg.legislation_code = pbg1.legislation_code
     and (   pbg1.business_group_id = pet.business_group_id
          or pet.legislation_code = pbg1.legislation_code
         )
     and pet.element_type_id between cp_stetid and cp_endetid;

Line: 700

  select /*+ INDEX(ppf PER_PEOPLE_F_PK)*/
         distinct ppf.person_id
    from per_all_people_f ppf
   where ppf.person_id between cp_stperson and cp_endperson;

Line: 709

  select distinct paf.assignment_id
    from
         per_all_assignments_f      paf
   where paf.person_id between cp_stperson and cp_endperson;

Line: 718

  select distinct pet.element_type_id
    from pay_element_types_f pet
   where pet.element_type_id between cp_stetid and cp_endetid;

Line: 738

select rf.report_type, rf.report_format, rf.report_qualifier, rf.report_category, rf.report_format_mapping_id
into l_report_type, l_report_format, l_report_qualifier, l_report_category, l_rep_id
from pay_report_format_mappings_f rf,
     pay_payroll_actions pact
where pact.payroll_action_id=p_pactid
and   rf.report_type =pact.report_type
and   rf.report_qualifier=pact.report_qualifier
and   rf.report_category=pact.report_category
and   pact.effective_date between rf.effective_start_date
                            and rf.effective_end_date;

Line: 752

   select  'B',rg.thread_level,
           rg.qualifying_procedure
   into l_upg_level,
	l_thread_level,
        l_qual_proc
   from pay_report_groups rg,pay_payroll_actions ppa
   where rg.report_format_mapping_id=l_rep_id
   and pay_core_utils.get_parameter('REP_GROUP', ppa.legislative_parameters)=rg.short_name
  and ppa.payroll_action_id=p_pactid;

Line: 764

  select pay_core_utils.get_parameter('UPG_DEF_NAME',
                                      ppa.legislative_parameters)
    into l_upg_def_nm
    from pay_payroll_actions ppa
   where payroll_action_id = p_pactid;

Line: 770

   select upgrade_level,
          threading_level,
          upgrade_definition_id,
          qualifying_procedure
     into l_upg_level,
          l_thread_level,
          l_upg_def_id,
          l_qual_proc
     from pay_upgrade_definitions
    where short_name = l_upg_def_nm;

Line: 955

  select pay_core_utils.get_parameter('UPG_DEF_NAME',
                                      ppa.legislative_parameters),
         ptoa.object_id
    into l_upg_def_nm,
         object_id
    from pay_payroll_actions ppa,
         pay_temp_object_actions ptoa
   where ppa.payroll_action_id = ptoa.payroll_action_id
     and ptoa.object_action_id = p_assactid;

Line: 965

   select upgrade_procedure,
          upgrade_definition_id
     into upgrade_proc,
          l_upg_def_id
     from pay_upgrade_definitions
    where short_name = l_upg_def_nm;

Line: 1008

     select pay_core_utils.get_parameter('UPG_DEF_NAME',
                                         ppa.legislative_parameters),
            pay_core_utils.get_parameter('REMOVE_ACT',
                                         ppa.legislative_parameters),
            ppa.business_group_id,
            pbg.legislation_code,
            ppa.report_type
       into l_upg_def_nm,
            l_remove_act,
            l_bus_grp_id,
            l_leg_code,
            l_report_type
       from pay_payroll_actions ppa,
            per_business_groups_perf pbg
      where ppa.payroll_action_id = pactid
        and pbg.business_group_id = ppa.business_group_id;

Line: 1027

     select count(*)
       into cnt_incomplete_actions
       from pay_temp_object_actions
       where payroll_action_id = pactid
       and action_status <> 'C';

Line: 1034

         DELETE FROM pay_file_details pfd
           WHERE EXISTS (SELECT 1
                 FROM pay_temp_object_actions ptoa
                 WHERE ptoa.object_action_id = pfd.source_id
                 AND pfd.source_type = 'PAA') OR
                    (pfd.source_id = pactid
                        AND pfd.source_type = 'PPA') ;

Line: 1044

     select upgrade_level,
            upgrade_definition_id
       into l_upg_level,
            l_upg_def_id
       from pay_upgrade_definitions
      where short_name = l_upg_def_nm;

Line: 1051

     select count(*)
       into cnt_incomplete_actions
       from pay_temp_object_actions
      where payroll_action_id = pactid
        and action_status <> 'C';