APPS.PQP_NL_PGGM_TEMPLATE SQL Statements

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

Line: 32

     pay_element_links_pkg.insert_row(
                     p_rowid                        => l_link_rowid,
                     p_element_link_id              => l_element_link_id ,
                     p_effective_start_date         => p_effective_start_date,
                     p_effective_end_date           => l_eff_end_dt,
                     p_payroll_id                   => NULL,
                     p_job_id                       => NULL,
                     p_position_id                  => NULL,
                     p_people_group_id              => NULL,
                     p_cost_allocation_keyflex_id   => NULL,
                     p_organization_id              => NULL,
                     p_element_type_id              => p_element_type_id,
                     p_location_id                  => NULL,
                     p_grade_id                     => NULL,
                     p_balancing_keyflex_id         => NULL,
                     p_business_group_id            => p_business_group_id,
                     p_legislation_code             => NULL,
                     p_element_set_id               => NULL,
                     p_pay_basis_id                 => NULL,
                     p_costable_type                => 'N',
                     p_link_to_all_payrolls_flag    => 'N',
                     p_multiply_value_flag          => 'N',
                     p_standard_link_flag           => 'N',
                     p_transfer_to_gl_flag          => 'N',
                     p_comment_id                   => NULL,
                     p_employment_category          => NULL,
                     p_qualifying_age               => NULL,
                     p_qualifying_length_of_service => NULL,
                     p_qualifying_units             => NULL,
                     p_attribute_category           => NULL,
                     p_attribute1                   => NULL,
                     p_attribute2                   => NULL,
                     p_attribute3                   => NULL,
                     p_attribute4                   => NULL,
                     p_attribute5                   => NULL,
                     p_attribute6                   => NULL,
                     p_attribute7                   => NULL,
                     p_attribute8                   => NULL,
                     p_attribute9                   => NULL,
                     p_attribute10                  => NULL,
                     p_attribute11                  => NULL,
                     p_attribute12                  => NULL,
                     p_attribute13                  => NULL,
                     p_attribute14                  => NULL,
                     p_attribute15                  => NULL,
                     p_attribute16                  => NULL,
                     p_attribute17                  => NULL,
                     p_attribute18                  => NULL,
                     p_attribute19                  => NULL,
                     p_attribute20                  => NULL ) ;

Line: 257

   SELECT  element_type_id
          ,object_version_NUMBER
     FROM  pay_shadow_element_types
    WHERE  template_id    = l_template_id
      AND  element_name   = c_ele_name;

Line: 264

   SELECT  balance_type_id
          ,object_version_NUMBER
     FROM  pay_shadow_balance_types
    WHERE  template_id  = l_template_id
      AND  balance_name = c_bal_name;

Line: 271

   SELECT element_type_id, object_version_NUMBER
     FROM pay_shadow_element_types
    WHERE template_id    = l_template_id
      AND element_name   = c_shd_elename;

Line: 278

   SELECT input_value_id
     FROM pay_input_values_f
    WHERE element_type_id   = c_ele_typeid
      AND business_group_id = p_business_group_id
      AND NAME              = 'Pay Value'
      AND c_effective_date BETWEEN effective_start_date
                               AND effective_end_date;

Line: 288

   SELECT *
     FROM pqp_pension_types_f
    WHERE pension_type_id   = c_pension_type_id
      AND business_group_id = p_business_group_id
      AND c_effective_date BETWEEN effective_start_date
                               AND effective_end_date;

Line: 298

     SELECT formula_text
       FROM pay_shadow_formulas
      WHERE formula_id  = c_formula_id
        AND template_type = 'U';

Line: 304

     SELECT user_name
       FROM ff_database_items dbi
           ,ff_route_parameter_values rpv
           ,ff_route_parameters rp
           ,pay_balance_dimensions pbd
           ,pay_defined_balances pdb
      WHERE dbi.user_entity_id = rpv.user_entity_id
        AND rpv.route_parameter_id = rp.route_parameter_id
        AND rp.route_id = pbd.route_id
        AND pbd.database_item_suffix =  '_PER_YTD'
         and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
         and pdb.balance_type_id = to_char(c_bal_type_id)
        AND pbd.legislation_code = 'NL'
         AND rpv.value = pdb.DEFINED_BALANCE_ID;

Line: 324

     SELECT user_name
       FROM ff_database_items dbi
           ,ff_route_parameter_values rpv
           ,ff_route_parameters rp
           ,pay_balance_dimensions pbd
           ,pay_defined_balances pdb
      WHERE dbi.user_entity_id = rpv.user_entity_id
        AND rpv.route_parameter_id = rp.route_parameter_id
        AND rp.route_id = pbd.route_id
         AND pbd.database_item_suffix = '_ASG_RUN'
         and pdb.BALANCE_DIMENSION_ID = pbd.BALANCE_DIMENSION_ID
         and pdb.balance_type_id = to_char(c_bal_type_id)
        AND pbd.legislation_code = 'NL'
        AND rpv.value = pdb.DEFINED_BALANCE_ID ;

Line: 342

        SELECT balance_name
        FROM pay_balance_types
           WHERE balance_type_id = c_bal_type_id
                 AND (business_group_id = p_business_group_id
                      OR business_group_id IS NULL
                      OR legislation_code = 'NL');

Line: 351

    SELECT 'x'
      FROM pay_element_type_extra_info
     WHERE eei_information_category = 'PQP_NL_PGGM_DEDUCTION'
       AND eei_information1         = p_scheme_description
       AND rownum                   = 1;

Line: 359

    SELECT balance_type_id
      FROM pay_balance_types_tl
    WHERE  balance_name = 'Retro '||c_subcat||' '
                          ||c_ee_er||' Contribution'
      AND  LANGUAGE = 'US';

Line: 410

           SELECT DISTINCT element_type_id
             INTO l_creator_id
             FROM pay_element_types_f
            WHERE element_name = p_creator_name
              AND business_group_id = p_business_group_id;

Line: 416

           SELECT classification_id
             INTO l_creator_id
             FROM pay_element_classifications
            WHERE classification_name = p_creator_name
              AND business_group_id = p_business_group_id;

Line: 437

        SELECT rc.retro_component_id,rc.component_name, rc.retro_type
          INTO l_comp_id, l_comp_name, l_comp_type
          FROM pay_retro_definitions     rd,
               pay_retro_defn_components rdc,
               pay_retro_components      rc
        WHERE  rdc.retro_component_id = rc.retro_component_id
          AND  rc.legislation_code    = g_legislation_code
          AND  rdc.priority           = p_retro_component_priority
          AND  rd.retro_definition_id = rdc.retro_definition_id
          AND  rd.legislation_code    = g_legislation_code
          AND  rd.definition_name     = g_retro_def_name;

Line: 459

       SELECT Retro_Component_Usage_Id
         INTO l_rc_usage_id
         FROM pay_retro_component_usages
        WHERE retro_component_id = l_comp_id
          AND creator_id         = l_creator_id
          AND creator_type       = p_creator_type;

Line: 466

       SELECT pay_retro_component_usages_s.NEXTVAL
         INTO l_rc_usage_id
         FROM dual;

Line: 471

          hr_utility.set_location('Insert Retro Comp Usgs '||l_proc_name, 20);

Line: 474

       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)
       VALUES(l_rc_usage_id
             ,l_comp_id
             ,l_creator_id
             ,p_creator_type
             ,p_default_component
             ,p_reprocess_type
             ,p_business_group_id
             ,NULL
             ,SYSDATE
             ,-1
             ,SYSDATE
             ,-1
             ,-1
             ,1);

Line: 514

         SELECT ts.time_span_id
           INTO l_time_span_id
           FROM pay_time_definitions s,
                pay_time_definitions e,
                pay_time_spans       ts
          WHERE ts.creator_id = l_comp_id
            AND ts.creator_type = 'RC'
            AND ts.start_time_def_id = s.time_definition_id
            AND ts.end_time_def_id = e.time_definition_id
            AND s.legislation_code = 'NL'
            AND s.definition_name = p_start_time_def_name
            AND e.legislation_code = 'NL'
            AND e.definition_name = p_end_time_def_name;

Line: 535

       SELECT DISTINCT element_type_id
         INTO l_retro_ele_id
         FROM pay_element_types_f
        WHERE element_name = p_retro_element_name
          AND business_group_id = p_business_group_id;

Line: 542

         SELECT element_span_usage_id
           INTO l_es_usage_id
           FROM pay_element_span_usages
          WHERE time_span_id             = l_time_span_id
            AND retro_component_usage_id = l_rc_usage_id
            AND adjustment_type   IS NULL;

Line: 550

         SELECT pay_element_span_usages_s.NEXTVAL
           INTO l_es_usage_id
           FROM dual;

Line: 555

            hr_utility.set_location('Insert Element Span Usgs '||l_proc_name, 30);

Line: 558

         INSERT INTO pay_element_span_usages(
           ELEMENT_SPAN_USAGE_ID,
           BUSINESS_GROUP_ID,
           LEGISLATION_CODE,
           TIME_SPAN_ID,
           RETRO_COMPONENT_USAGE_ID,
           ADJUSTMENT_TYPE,
           RETRO_ELEMENT_TYPE_ID,
           CREATION_DATE,
           CREATED_BY,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           LAST_UPDATE_LOGIN,
           OBJECT_VERSION_NUMBER)
         VALUES(l_es_usage_id
               ,p_business_group_id
               ,NULL
               ,l_time_span_id
               ,l_rc_usage_id
               ,NULL
               ,l_retro_ele_id
               ,SYSDATE
               ,-1
               ,SYSDATE
               ,-1
               ,-1
               ,1);

Line: 600

PROCEDURE Update_Event_Group
  (p_element_name             VARCHAR2,
   p_business_group_id        NUMBER)
IS

CURSOR c_get_retro_evg_id IS
SELECT event_group_id
  FROM pay_event_groups
WHERE  event_group_name = 'PQP_NL_RETRO_EVG'
  AND  legislation_code = 'NL';

Line: 617

  l_proc_name := g_proc_name||'Update_Event_Group';

Line: 631

      UPDATE pay_element_types_f
         SET recalc_event_group_id = l_retro_evg_id
      WHERE  element_name = p_element_name
         AND business_group_id = p_business_group_id;

Line: 648

END Update_Event_Group;

Line: 660

     SELECT template_id
       FROM pay_element_templates
      WHERE template_name     = l_template_name
        AND legislation_code  = p_legislation_code
        AND template_type     = 'T'
        AND business_group_id IS NULL;

Line: 1156

        pay_shadow_element_api.update_shadow_element
          (p_validate               => FALSE
          ,p_effective_date         => p_effective_start_date
          ,p_element_type_id        => l_shadow_element(i).element_type_id
          ,p_description            => l_shadow_element(i).description
          ,p_reporting_name         => l_shadow_element(i).reporting_name
          ,p_post_termination_rule  => p_termination_rule
          ,p_object_version_number  => l_shadow_element(i).object_version_number
          );

Line: 1202

             UPDATE pay_shadow_formulas
                SET formula_text      = l_formula_text
              WHERE formula_id        = l_shad_formula_id
                AND business_group_id = p_business_group_id;

Line: 1230

       UPDATE pay_shadow_formulas
          SET formula_text = l_formula_text
        WHERE formula_id = l_shad_formula_id
          AND business_group_id = p_business_group_id;

Line: 1273

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Pension Deduction'
                   ,'Pension Type Id'
                   ,TO_CHAR(p_pension_type_id)
                   ,p_business_group_id);

Line: 1279

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Pension Deduction'
                   ,'Basis Rounding'
                   ,p_basis_rounding
                   ,p_business_group_id);

Line: 1285

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Pension Deduction'
                   ,'Contribution Rounding'
                   ,p_contrib_rounding
                   ,p_business_group_id);

Line: 1291

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Employer Pension Contribution'
                    ,'Pension Type Id'
                    ,TO_CHAR(p_pension_type_id)
                    ,p_business_group_id);

Line: 1297

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Employer Pension Contribution'
                   ,'Basis Rounding'
                   ,p_basis_rounding
                   ,p_business_group_id);

Line: 1303

   pqp_nl_pension_template.Update_Ipval_Defval(
                    p_scheme_prefix||' PGGM Employer Pension Contribution'
                   ,'Contribution Rounding'
                   ,p_contrib_rounding
                   ,p_business_group_id);

Line: 1800

   Update_Event_Group
   (p_element_name => p_scheme_prefix||' PGGM Pension Deduction'
    ,p_business_group_id => p_business_group_id);

Line: 1804

   Update_Event_Group
   (p_element_name => p_scheme_prefix||' PGGM Employer Pension Contribution'
    ,p_business_group_id => p_business_group_id);

Line: 1808

   Update_Event_Group
   (p_element_name => p_scheme_prefix||' PGGM Extra Pensions'
    ,p_business_group_id => p_business_group_id);

Line: 2001

PROCEDURE Delete_User_Template
           (p_business_group_id            IN NUMBER
           ,p_pension_dedn_ele_name        IN VARCHAR2
           ,p_pension_dedn_ele_type_id     IN NUMBER
           ,p_security_group_id            IN NUMBER
           ,p_effective_date               IN DATE
           ) IS
  --
  CURSOR c1 IS
   SELECT template_id
          ,base_name
     FROM pay_element_templates
    WHERE base_name||' PGGM Pension Deduction'  = p_pension_dedn_ele_name
      AND business_group_id                     = p_business_group_id
      AND template_type                         = 'U';

Line: 2018

  SELECT element_type_extra_info_id
        ,object_version_number ovn
    FROM pay_element_type_extra_info
   WHERE eei_information_category = 'PQP_NL_PGGM_DEDUCTION'
     AND element_type_id          = p_pension_dedn_ele_type_id;

Line: 2029

  SELECT 1
    FROM dual
   WHERE EXISTS ( SELECT 1
                    FROM pay_run_results prr
                   WHERE prr.element_type_id = c_element_type_id) ;

Line: 2040

   SELECT retro_component_usage_id
     FROM pay_retro_component_usages
    WHERE creator_id = c_element_type_id
      AND creator_type = 'ET'
      AND business_group_id = p_business_group_id;

Line: 2050

   SELECT element_span_usage_id
     FROM pay_element_span_usages
    WHERE retro_component_usage_id = c_retro_comp_usage_id
      AND business_group_id = p_business_group_id;

Line: 2056

   SELECT element_type_id
     FROM pay_element_types_f
    WHERE element_name = c_base_name||' PGGM Employer Pension Contribution'
      AND business_group_id = p_business_group_id
      AND trunc(p_effective_date) BETWEEN effective_start_date AND
                                          effective_end_date ;

Line: 2066

  l_proc                 VARCHAR2(60) := g_proc_name||'Delete_User_Template';

Line: 2116

   pay_element_template_api.delete_user_structure
     (p_validate                =>   FALSE
     ,p_drop_formula_packages   =>   TRUE
     ,p_template_id             =>   l_template_id);

Line: 2127

       pay_element_extra_info_api.delete_element_extra_info
       (p_element_type_extra_info_id => temp_rec.element_type_extra_info_id
       ,p_object_version_number      => temp_rec.ovn);

Line: 2141

END Delete_User_Template;

Line: 2148

PROCEDURE Delete_User_Template_Swi
           (p_business_group_id            IN NUMBER
           ,p_pension_dedn_ele_name        IN VARCHAR2
           ,p_pension_dedn_ele_type_id     IN NUMBER
           ,p_security_group_id            IN NUMBER
           ,p_effective_date               IN DATE
           ) IS

  --
  -- Variables for API Boolean parameters
  l_validate                      BOOLEAN;

Line: 2164

  l_proc            VARCHAR2(72) := 'Delete_User_Template_Swi';

Line: 2172

  SAVEPOINT Delete_User_Template_Swi;

Line: 2192

   Delete_User_Template
           (p_business_group_id         =>   p_business_group_id
           ,p_pension_dedn_ele_name     =>   p_pension_dedn_ele_name
           ,p_pension_dedn_ele_type_id  =>   p_pension_dedn_ele_type_id
           ,p_security_group_id         =>   p_security_group_id
           ,p_effective_date            =>   p_effective_date
           );

Line: 2224

    ROLLBACK TO Delete_User_Template_Swi;

Line: 2240

    ROLLBACK TO Delete_User_Template_Swi;

Line: 2255

END delete_user_template_swi;