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: 194

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
               );

Line: 215

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: 273

     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: 293

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: 308

   select  'B',rg.thread_level
   into l_upg_level, l_thread_level
   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: 317

  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: 329

   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: 343

       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: 353

       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: 367

       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: 386

       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: 396

       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: 411

       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: 520

     select pay_assignment_actions_s.nextval
       into l_action_id
       from dual;

Line: 549

  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: 561

  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: 573

  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: 585

  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: 601

  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: 617

  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: 635

  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: 644

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

Line: 653

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

Line: 669

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=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: 683

   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: 695

  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: 701

   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: 868

  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: 878

   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: 921

     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: 940

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

Line: 950

     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: 957

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