APPS.PAY_GB_ENH_RETRO_PAY SQL Statements

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

Line: 10

    SELECT DISTINCT
      pet.element_type_id
    FROM
      pay_element_types_f pet,
      per_business_groups_perf pbg1,
      per_business_groups_perf pbg
    WHERE
      pbg1.legislation_code = pbg.legislation_code
    AND
      (
        pet.business_group_id = pbg1.business_group_id
      OR pet.legislation_code = pbg1.legislation_code
      )
    AND pbg.legislation_code ='GB'
    ORDER BY
      pet.element_type_id;

Line: 28

    SELECT
      STATUS
    FROM
      pay_upgrade_status
    WHERE
      UPGRADE_DEFINITION_ID IN
      (
        SELECT
          UPGRADE_DEFINITION_ID
        FROM
          pay_upgrade_definitions
        WHERE
          legislation_code='GB'
        AND short_name    ='GB_ENHANCED_RETROPAY'
      );

Line: 45

    SELECT
      UPGRADE_DEFINITION_ID
    FROM
      pay_upgrade_definitions
    WHERE
      legislation_code='GB'
    AND short_name    ='GB_ENHANCED_RETROPAY';

Line: 87

      INSERT
      INTO
        pay_upgrade_status
        (
          UPGRADE_DEFINITION_ID,
          STATUS,
          LEGISLATION_CODE
        )
        VALUES
        (
          l_definition_id,
          'C',
          'GB'
        );

Line: 102

      UPDATE
        pay_upgrade_status
      SET
        status = 'C'
      WHERE
        UPGRADE_DEFINITION_ID IN
        (
          SELECT
            UPGRADE_DEFINITION_ID
          FROM
            pay_upgrade_definitions
          WHERE
            legislation_code='GB'
          AND short_name    ='GB_ENHANCED_RETROPAY'
        );

Line: 127

    SELECT
      pet.classification_id,
      pet.element_name,
      pet.legislation_code,
      pet.business_group_id,
      pec.classification_name
    FROM
      pay_element_types_f pet,
      pay_element_classifications pec
    WHERE
      pet.element_type_id                         = cp_element_type_id
    AND pet.classification_id                     = pec.classification_id;

Line: 141

    SELECT
      legislation_code
    FROM
      per_business_groups
    WHERE
      business_group_id                    = cp_business_group_id;

Line: 150

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

    SELECT
      1
    FROM
      pay_payroll_actions ppa
    WHERE
      ppa.action_type      = 'L'
    AND ppa.element_set_id = cp_element_set_id;

Line: 270

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

    SELECT
      legislation_code
    FROM
      per_business_groups
    WHERE
      business_group_id                    = cp_business_group_id;

Line: 292

    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                     = 'UK_Enh_Retro';

Line: 305

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

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

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

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

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

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

Line: 450

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

  SELECT
    pay_retro_component_usages_s.nextval
  INTO
    ln_retro_component_usage_id
  FROM
    dual;

Line: 465

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

END insert_retro_comp_usages;

Line: 518

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
BEGIN
  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
    )
  --values
  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: 573

END insert_element_span_usages;