APPS.PAY_IP_RETRO_UPGRADE SQL Statements

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

Line: 40

 PROCEDURE insert_retro_comp_usages
                  (p_business_group_id    in        number,
                   p_legislation_code     in        varchar2,
                   p_retro_component_id   in        number,
                   p_creator_id           in        number,
                   p_retro_comp_usage_id out nocopy number)
 IS

   ln_retro_component_usage_id NUMBER;

Line: 52

   lv_procedure_name := '.insert_retro_comp_usages';

Line: 55

   select pay_retro_component_usages_s.nextval
     into ln_retro_component_usage_id
     from dual;

Line: 59

   insert into pay_retro_component_usages
   (retro_component_usage_id, retro_component_id, creator_id, creator_type,
    default_component, reprocess_type, business_group_id, legislation_code,
    creation_date, created_by, last_update_date, last_updated_by,
    last_update_login, object_version_number)
    select ln_retro_component_usage_id, p_retro_component_id, p_creator_id,
    'ET', 'Y', 'R', p_business_group_id, p_legislation_code,
    sysdate, 2, sysdate, 2, 2, 1
    from dual
    WHERE NOT EXISTS ( SELECT 1 FROM pay_retro_component_usages
    WHERE retro_component_id = p_retro_component_id
    AND creator_id = p_creator_id
    AND creator_type = 'ET');

Line: 82

 END insert_retro_comp_usages;

Line: 85

 PROCEDURE insert_element_span_usages
                  (p_business_group_id     in number,
                   p_retro_element_type_id in number,
                   p_legislation_code      in varchar2,
                   p_time_span_id          in number,
                   p_retro_comp_usage_id   in  number)
 IS

   lv_procedure_name           VARCHAR2(100);

Line: 96

   lv_procedure_name := '.insert_element_span_usages';

Line: 104

   insert into pay_element_span_usages
   (element_span_usage_id, business_group_id, time_span_id,
    retro_component_usage_id, retro_element_type_id,
    creation_date, created_by, last_update_date, last_updated_by,
    last_update_login, object_version_number)
    select pay_element_span_usages_s.nextval, p_business_group_id, p_time_span_id,
    p_retro_comp_usage_id, p_retro_element_type_id,
    sysdate, 2, sysdate, 2, 2, 1
    from dual
    WHERE not exists ( SELECT 1 FROM pay_element_span_usages pesu
                       WHERE pesu.business_group_id = p_business_group_id
                       AND   pesu.legislation_code IS NULL
                       AND   pesu.time_span_id = p_time_span_id
                       AND   retro_component_usage_id = p_retro_comp_usage_id);

Line: 126

 END insert_element_span_usages;

Line: 142

      select classification_id, element_name, legislation_code, business_group_id
        from pay_element_types_f
       where element_type_id = cp_element_type_id;

Line: 147

     select legislation_code
     from per_business_groups
     where business_group_id = cp_business_group_id;

Line: 154

     select petr.element_set_id
       from pay_element_type_rules petr
      where petr.element_type_id = cp_element_type_id
        and petr.include_or_exclude = 'I'
     union all
     select pes.element_set_id
       from pay_ele_classification_rules pecr,
            pay_element_types_f pet,
            pay_element_sets pes
      where pet.classification_id = pecr.classification_id
        and pes.element_set_id = pecr.element_set_id
        and (pes.business_group_id = pet.business_group_id
             or pet.legislation_code = cp_legislation_code)
        and pet.element_type_id = cp_element_type_id
        and pecr.classification_id = cp_classification_id
     minus
     select petr.element_set_id
       from pay_element_type_rules petr
      where petr.element_type_id = cp_element_type_id
        and petr.include_or_exclude = 'E';

Line: 176

     select 1
       from pay_payroll_actions ppa
      where ppa.action_type = 'L'
        and ppa.element_set_id = cp_element_set_id;

Line: 260

     select business_group_id, legislation_code, classification_id,
            nvl(retro_summ_ele_id, pet.element_type_id),
            element_name
       from pay_element_types_f pet
      where pet.element_type_id = cp_element_type_id
    order by pet.effective_start_date desc;

Line: 268

     select legislation_code
     from per_business_groups
     where business_group_id = cp_business_group_id;

Line: 275

     select petr.element_set_id
       from pay_element_type_rules petr
      where petr.element_type_id = cp_element_type_id
        and petr.include_or_exclude = 'I'
     union all
     select pes.element_set_id
       from pay_ele_classification_rules pecr,
            pay_element_types_f pet,
            pay_element_sets pes
      where pet.classification_id = pecr.classification_id
        and pes.element_set_id = pecr.element_set_id
        and (pes.business_group_id = pet.business_group_id
             or pet.legislation_code = cp_legislation_code)
        and pet.element_type_id = cp_element_type_id
        and pecr.classification_id = cp_classification_id
     minus
     select petr.element_set_id
       from pay_element_type_rules petr
      where petr.element_type_id = cp_element_type_id
        and petr.include_or_exclude = 'E';

Line: 298

     select hoi.organization_id
       from hr_organization_information hoi,
            hr_organization_information hoi2
     where hoi.org_information_context = 'CLASS'
       and hoi.org_information1 = 'HR_BG'
       and hoi.organization_id = hoi2.organization_id
       and hoi2.org_information_context = 'Business Group Information'
       and hoi2.org_information9 = cp_legislation_code
       and exists (select 1 from pay_payroll_actions ppa
                    where ppa.business_group_id = hoi.organization_id
                      and ppa.action_type = 'L'
                      and ppa.element_set_id = cp_element_set_id
                      );

Line: 313

     select retro_component_id, pts.time_span_id
       from pay_retro_components prc,
            pay_time_spans pts
      where pts.creator_id = prc.retro_component_id
        and prc.legislation_code = cp_legislation_code
       and prc.short_name = upper(cp_legislation_code)||'_BACKDATES';

Line: 380

      insert_retro_comp_usages
                  (p_business_group_id   => null
                  ,p_legislation_code    => ln_legislation_code
                  ,p_retro_component_id  => gn_retro_component_id
                  ,p_creator_id          => p_element_type_id
                  ,p_retro_comp_usage_id => ln_retro_comp_usage_id);

Line: 407

            insert_element_span_usages
               (p_business_group_id   => ln_business_group_id
               ,p_retro_element_type_id => ln_retro_element_type_id
               ,p_legislation_code    => ln_legislation_code
               ,p_time_span_id        => gn_time_span_id
               ,p_retro_comp_usage_id => ln_retro_comp_usage_id);

Line: 427

      insert_retro_comp_usages
                  (p_business_group_id   => ln_ele_business_group_id
                  ,p_legislation_code    => null
                  ,p_retro_component_id  => gn_retro_component_id
                  ,p_creator_id          => p_element_type_id
                  ,p_retro_comp_usage_id => ln_retro_comp_usage_id);

Line: 434

      insert_element_span_usages
                  (p_business_group_id   => ln_ele_business_group_id
                  ,p_retro_element_type_id => ln_retro_element_type_id
                  ,p_legislation_code    => null
                  ,p_time_span_id        => gn_time_span_id
                  ,p_retro_comp_usage_id => ln_retro_comp_usage_id);