APPS.PAY_ELEMENT_TEMPLATE_UTIL SQL Statements

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

Line: 17

  select template_type from pay_element_templates pet
  where  pet.template_id = p_template_id;

Line: 39

  select 'Y'
  from   per_business_groups_perf
  where  business_group_id = p_business_group_id
  and    legislation_code = p_legislation_code;

Line: 149

  p_core_objects.delete;

Line: 150

  p_exclusion_rules.delete;

Line: 151

  p_element_types.delete;

Line: 152

  p_input_values.delete;

Line: 153

  p_formulas.delete;

Line: 154

  p_formula_rules.delete;

Line: 155

  p_balance_types.delete;

Line: 156

  p_balance_feeds.delete;

Line: 157

  p_defined_balances.delete;

Line: 158

  p_balance_classis.delete;

Line: 159

  p_sub_classi_rules.delete;

Line: 160

  p_iterative_rules.delete;

Line: 161

  p_ele_type_usages.delete;

Line: 162

  p_gu_bal_exclusions.delete;

Line: 163

  p_bal_attributes.delete;

Line: 164

  p_template_ff_usages.delete;

Line: 177

    select
      template_id,
      template_type,
      template_name,
      base_processing_priority,
      business_group_id,
      legislation_code,
      version_number,
      base_name,
      max_base_name_length,
      preference_info_category,
      preference_information1,
      preference_information2,
      preference_information3,
      preference_information4,
      preference_information5,
      preference_information6,
      preference_information7,
      preference_information8,
      preference_information9,
      preference_information10,
      preference_information11,
      preference_information12,
      preference_information13,
      preference_information14,
      preference_information15,
      preference_information16,
      preference_information17,
      preference_information18,
      preference_information19,
      preference_information20,
      preference_information21,
      preference_information22,
      preference_information23,
      preference_information24,
      preference_information25,
      preference_information26,
      preference_information27,
      preference_information28,
      preference_information29,
      preference_information30,
      configuration_info_category,
      configuration_information1,
      configuration_information2,
      configuration_information3,
      configuration_information4,
      configuration_information5,
      configuration_information6,
      configuration_information7,
      configuration_information8,
      configuration_information9,
      configuration_information10,
      configuration_information11,
      configuration_information12,
      configuration_information13,
      configuration_information14,
      configuration_information15,
      configuration_information16,
      configuration_information17,
      configuration_information18,
      configuration_information19,
      configuration_information20,
      configuration_information21,
      configuration_information22,
      configuration_information23,
      configuration_information24,
      configuration_information25,
      configuration_information26,
      configuration_information27,
      configuration_information28,
      configuration_information29,
      configuration_information30,
      object_version_number
    from  pay_element_templates
    where template_id = p_template_id
    for update of template_id;

Line: 286

    select
      template_core_object_id,
      template_id,
      core_object_type,
      core_object_id,
      shadow_object_id,
      effective_date,
      object_version_number
    from  pay_template_core_objects
    where template_id = p_template_id
    order by core_object_type
    for update of core_object_id;

Line: 316

    select
      exclusion_rule_id,
      template_id,
      flexfield_column,
      exclusion_value,
      description,
      object_version_number
    from  pay_template_exclusion_rules
    where template_id = p_template_id
    for update of exclusion_rule_id;

Line: 342

    select
      element_type_id,
      template_id,
      classification_name,
      additional_entry_allowed_flag,
      adjustment_only_flag,
      closed_for_entry_flag,
      element_name,
      indirect_only_flag,
      multiple_entries_allowed_flag,
      multiply_value_flag,
      post_termination_rule,
      process_in_run_flag,
      relative_processing_priority,
      processing_type,
      standard_link_flag,
      input_currency_code,
      output_currency_code,
      benefit_classification_name,
      description,
      qualifying_age,
      qualifying_length_of_service,
      qualifying_units,
      reporting_name,
      attribute_category,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      attribute16,
      attribute17,
      attribute18,
      attribute19,
      attribute20,
      element_information_category,
      element_information1,
      element_information2,
      element_information3,
      element_information4,
      element_information5,
      element_information6,
      element_information7,
      element_information8,
      element_information9,
      element_information10,
      element_information11,
      element_information12,
      element_information13,
      element_information14,
      element_information15,
      element_information16,
      element_information17,
      element_information18,
      element_information19,
      element_information20,
      third_party_pay_only_flag,
      skip_formula,
      payroll_formula_id,
      exclusion_rule_id,
      iterative_flag,
      iterative_priority,
      iterative_formula_name,
      process_mode,
      grossup_flag,
      advance_indicator,
      advance_payable,
      advance_deduction,
      process_advance_entry,
      proration_group,
      proration_formula,
      recalc_event_group,
      once_each_period_flag,
      object_version_number
    from  pay_shadow_element_types
    where template_id = p_template_id
    for update of element_type_id;

Line: 444

    select
      balance_type_id,
      template_id,
      assignment_remuneration_flag,
      balance_name,
      balance_uom,
      currency_code,
      comments,
      reporting_name,
      attribute_category,
      attribute1,
      attribute2,
      attribute3,
      attribute4,
      attribute5,
      attribute6,
      attribute7,
      attribute8,
      attribute9,
      attribute10,
      attribute11,
      attribute12,
      attribute13,
      attribute14,
      attribute15,
      attribute16,
      attribute17,
      attribute18,
      attribute19,
      attribute20,
      jurisdiction_level,
      tax_type,
      exclusion_rule_id,
      object_version_number,
      category_name,
      base_balance_type_id,
      base_balance_name,
      input_value_id
    from  pay_shadow_balance_types
    where template_id = p_template_id
    for update of balance_type_id;

Line: 500

  select f.formula_id
  ,      f.template_type
  ,      f.legislation_code
  ,      f.business_group_id
  ,      f.formula_name
  ,      f.description
  ,      f.formula_text
  ,      f.formula_type_name
  ,      f.object_version_number
  from  pay_shadow_formulas f
  ,     pay_shadow_element_types et
  where et.template_id = p_template_id
  and   et.payroll_formula_id is not null
  and   f.formula_id = et.payroll_formula_id
  for update of f.formula_id;

Line: 517

  select f.formula_id
  ,      f.template_type
  ,      f.legislation_code
  ,      f.business_group_id
  ,      f.formula_name
  ,      f.description
  ,      f.formula_text
  ,      f.formula_type_name
  ,      f.object_version_number
  from  pay_shadow_formulas f
  ,     pay_shadow_element_types et
  ,	  pay_shadow_input_values iv
  where et.template_id = p_template_id
  and   et.element_type_id = iv.element_type_id
  and   iv.formula_id is not null
  and   f.formula_id = iv.formula_id
  for update of f.formula_id;

Line: 536

  select f.formula_id
  ,      f.template_type
  ,      f.legislation_code
  ,      f.business_group_id
  ,      f.formula_name
  ,      f.description
  ,      f.formula_text
  ,      f.formula_type_name
  ,      f.object_version_number
  from  pay_shadow_formulas f
  ,     pay_template_ff_usages tfu
  where tfu.template_id = p_template_id
  and   f.formula_id = tfu.formula_id
  for update of f.formula_id;

Line: 602

    select
      balance_feed_id,
      balance_type_id,
      input_value_id,
      scale,
      balance_name,
      exclusion_rule_id,
      object_version_number
    from  pay_shadow_balance_feeds
    where input_value_id = p_input_value_id
    for update of balance_feed_id;

Line: 647

    select
      defined_balance_id,
      balance_type_id,
      dimension_name,
      force_latest_balance_flag,
      grossup_allowed_flag,
      object_version_number,
      exclusion_rule_id
    from  pay_shadow_defined_balances
    where balance_type_id = p_balance_type_id
    for update of defined_balance_id;

Line: 692

    select
      balance_classification_id,
      balance_type_id,
      element_classification,
      scale,
      object_version_number,
      exclusion_rule_id
    from  pay_shadow_balance_classi
    where balance_type_id = p_balance_type_id
    for update of balance_classification_id;

Line: 736

    select
      input_value_id,
      element_type_id,
      display_sequence,
      generate_db_items_flag,
      hot_default_flag,
      mandatory_flag,
      name,
      uom,
      lookup_type,
      default_value,
      max_value,
      min_value,
      warning_or_error,
      default_value_column,
      exclusion_rule_id,
      formula_id,
      input_validation_formula,
      object_version_number
    from  pay_shadow_input_values
    where element_type_id = p_element_type_id
    for update of input_value_id;

Line: 792

    select
      formula_result_rule_id,
      shadow_element_type_id,
      element_type_id,
      result_name,
      result_rule_type,
      severity_level,
      input_value_id,
      exclusion_rule_id,
      object_version_number,
      element_name
    from  pay_shadow_formula_rules
    where shadow_element_type_id = p_element_type_id
    for update of formula_result_rule_id;

Line: 840

    select
      sub_classification_rule_id,
      element_type_id,
      element_classification,
      object_version_number,
      exclusion_rule_id
    from  pay_shadow_sub_classi_rules
    where element_type_id = p_element_type_id
    for update of sub_classification_rule_id;

Line: 884

    select
      iterative_rule_id,
      element_type_id,
      result_name,
      iterative_rule_type,
      input_value_id,
      severity_level,
      exclusion_rule_id,
      object_version_number
    from  pay_shadow_iterative_rules
    where element_type_id = p_element_type_id
    for update of iterative_rule_id;

Line: 930

    select
      element_type_usage_id,
      element_type_id,
      inclusion_flag,
      run_type_name,
      exclusion_rule_id,
      object_version_number
    from  pay_shadow_ele_type_usages
    where element_type_id = p_element_type_id
    for update of element_type_usage_id;

Line: 975

    select
      grossup_balances_id,
      source_id,
      source_type,
      balance_type_name,
      balance_type_id,
      exclusion_rule_id,
      object_version_number
    from  pay_shadow_gu_bal_exclusions
    where source_id = p_element_type_id
    for update of grossup_balances_id;

Line: 1020

    select
      balance_attribute_id,
      attribute_name,
      defined_balance_id,
      object_version_number,
      exclusion_rule_id
    from  pay_shadow_bal_attributes
    where defined_balance_id = p_defined_balance_id
    for update of balance_attribute_id;

Line: 1066

    select
      template_ff_usage_id,
      template_id,
      formula_id,
      object_id,
      object_version_number,
      exclusion_rule_id
    from  pay_template_ff_usages
    where template_id = p_template_id
    for update of template_ff_usage_id;

Line: 1240

  p_defined_balances.delete(p_i);

Line: 1251

      p_bal_attributes.delete(j);

Line: 1291

  p_balance_types.delete(p_i);

Line: 1303

      p_balance_feeds.delete(j);

Line: 1339

      p_balance_classis.delete(j);

Line: 1356

      p_gu_bal_exclusions.delete(j);

Line: 1421

  p_input_values.delete(p_i);

Line: 1433

      p_formula_rules.delete(j);

Line: 1449

      p_balance_feeds.delete(j);

Line: 1465

      p_iterative_rules.delete(j);

Line: 1531

  p_element_types.delete(p_i);

Line: 1544

      p_formula_rules.delete(j);

Line: 1587

      p_sub_classi_rules.delete(j);

Line: 1603

      p_iterative_rules.delete(j);

Line: 1619

      p_ele_type_usages.delete(j);

Line: 1635

      p_gu_bal_exclusions.delete(j);

Line: 1651

      p_template_ff_usages.delete(j);

Line: 1714

      p_formulas.delete(j);

Line: 1769

  p_template_ff_usages.delete;

Line: 1899

          p_formula_rules.delete(k);

Line: 1915

          p_balance_feeds.delete(k);

Line: 1931

          p_balance_classis.delete(k);

Line: 1967

          p_sub_classi_rules.delete(k);

Line: 1983

          p_iterative_rules.delete(k);

Line: 1999

          p_ele_type_usages.delete(k);

Line: 2015

          p_gu_bal_exclusions.delete(k);

Line: 2031

          p_template_ff_usages.delete(k);

Line: 2047

          p_bal_attributes.delete(k);

Line: 2412

procedure insert_balance_type
(p_effective_date  in            date
,p_balance_type_id in            number
,p_template_id     in            number
,p_input_values    in            t_input_values
,p_balance_types   in out nocopy t_balance_types
) is
l_id number;

Line: 2450

      insert_balance_type
      (p_effective_date  => p_effective_date
      ,p_balance_type_id => l_id
      ,p_template_id     => p_template_id
      ,p_input_values    => p_input_values
      ,p_balance_types   => p_balance_types
      );

Line: 2464

end insert_balance_type;

Line: 2567

    insert_balance_type
    (p_effective_date  => p_effective_date
    ,p_balance_type_id => i
    ,p_template_id     => p_element_template.template_id
    ,p_input_values    => p_input_values
    ,p_balance_types   => p_balance_types
    );

Line: 3295

procedure delete_template
  (p_template_id     in number
  ,p_formulas        in t_formulas
  ,p_delete_formulas in boolean default true
  ) is
  l_proc varchar2(72) := g_package||'delete_template';

Line: 3308

  delete from pay_template_core_objects
  where  template_id = p_template_id
  ;

Line: 3317

  delete from pay_shadow_bal_attributes
  where  defined_balance_id in
  (
    select db.defined_balance_id
    from   pay_shadow_defined_balances db
    ,      pay_shadow_balance_types bt
    where  db.balance_type_id = bt.balance_type_id
    and    bt.template_id = p_template_id
  );

Line: 3329

  delete from pay_template_ff_usages
  where  template_id = p_template_id;

Line: 3334

  delete from pay_shadow_gu_bal_exclusions
  where  balance_type_id in
  (
    select balance_type_id
    from   pay_shadow_balance_types
    where  template_id = p_template_id
  );

Line: 3344

  delete from pay_shadow_ele_type_usages
  where  element_type_id in
  (
    select element_type_id
    from   pay_shadow_element_types
    where  template_id = p_template_id
  );

Line: 3354

  delete from pay_shadow_iterative_rules
  where  element_type_id in
  (
    select element_type_id
    from   pay_shadow_element_types
    where  template_id = p_template_id
  );

Line: 3364

  delete from pay_shadow_formula_rules
  where  shadow_element_type_id in
  (
    select element_type_id
    from   pay_shadow_element_types
    where  template_id = p_template_id
  );

Line: 3374

  delete from pay_shadow_balance_feeds
  where  input_value_id in
  (
    select iv.input_value_id
    from   pay_shadow_input_values iv
    ,      pay_shadow_element_types et
    where  et.template_id = p_template_id
    and    iv.element_type_id = et.element_type_id
  );

Line: 3386

  delete from pay_shadow_balance_classi
  where  balance_type_id in
  (
    select balance_type_id
    from   pay_shadow_balance_types
    where  template_id = p_template_id
  );

Line: 3396

  delete from pay_shadow_defined_balances
  where  balance_type_id in
  (
    select balance_type_id
    from   pay_shadow_balance_types
    where  template_id = p_template_id
  );

Line: 3406

  delete from pay_shadow_sub_classi_rules
  where  element_type_id in
  (
    select element_type_id
    from   pay_shadow_element_types
    where  template_id = p_template_id
  );

Line: 3420

  update pay_shadow_balance_types
  set    base_balance_type_id = null
  where  base_balance_type_id is not null
  and    template_id = p_template_id;

Line: 3425

  delete from pay_shadow_balance_types
  where  template_id = p_template_id;

Line: 3430

  delete from pay_shadow_input_values
  where  element_type_id in
  (
    select element_type_id
    from   pay_shadow_element_types
    where  template_id = p_template_id
  );

Line: 3440

  delete from pay_shadow_element_types
  where  template_id = p_template_id;

Line: 3449

  if p_delete_formulas then
    i := p_formulas.first;

Line: 3463

  delete from pay_template_exclusion_rules
  where  template_id = p_template_id;

Line: 3468

  delete from pay_element_templates
  where  template_id = p_template_id;

Line: 3472

end delete_template;

Line: 3481

    SELECT formula_name
    FROM pay_shadow_formulas
    WHERE formula_id = p_formula_id;