APPS.PAY_AE_ELEMENT_TEMPLATE_PKG SQL Statements

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

Line: 16

    SELECT grade_id
    FROM   per_all_assignments_f
    WHERE  assignment_id = p_assignment_id
    AND    p_date_earned BETWEEN effective_start_date AND effective_end_date;

Line: 23

    SELECT pg.value
    FROM   pay_rates pr
           ,pay_grade_rules_f pg
    WHERE  pr.rate_type= 'G'
    AND    pr.rate_id = p_rate_id
    AND    pr.rate_id = pg.rate_id
    AND    pg.grade_or_spinal_point_id = l_grade_id
    AND    pg.rate_type = 'G'
    AND    pg.business_group_id = p_business_group_id
    AND    pr.business_group_id = p_business_group_id
    AND    p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;

Line: 64

    SELECT grade_id
    FROM   per_all_assignments_f
    WHERE  assignment_id = p_assignment_id
    AND    p_date_earned BETWEEN effective_start_date AND effective_end_date;

Line: 71

    SELECT 'Y'
    FROM    pay_rates pr
    WHERE  pr.rate_type = 'G'
    AND       pr.name =p_rate_table
    AND       pr.business_group_id = p_business_group_id;

Line: 79

    SELECT pg.value
    FROM   pay_rates pr
           ,pay_grade_rules_f pg
    WHERE  pr.rate_type= 'G'
    AND    pr.name = p_rate_table
    AND    pr.rate_id = pg.rate_id
    AND    pg.grade_or_spinal_point_id = l_grade_id
    AND    pg.rate_type = 'G'
    AND    pg.business_group_id = p_business_group_id
    AND    pr.business_group_id = p_business_group_id
    AND    p_date_earned BETWEEN pg.effective_start_date AND pg.effective_end_date;

Line: 140

    SELECT paa.date_start start_date
           ,paa.date_end end_date
    FROM   per_absence_attendances paa
           ,per_absence_attendance_types paat
           ,per_all_assignments_f asg
    WHERE  paat.absence_category ='UL'
    AND    paat.business_group_id = paa.business_group_id
    AND    paat.business_group_id = p_business_group_id
    AND    paat.absence_attendance_type_id = paa.absence_attendance_type_id
    AND    paa.person_id = asg.person_id
    AND    asg.assignment_id = p_assignment_id
    AND    (paa.date_start between p_start_date AND p_end_date
           AND paa.date_end between p_start_date AND p_end_date)
    UNION
    SELECT paa.date_start start_date
           ,p_end_date end_date
    FROM   per_absence_attendances paa
           ,per_absence_attendance_types paat
           ,per_all_assignments_f asg
    WHERE  paat.absence_category ='UL'
    AND    paat.business_group_id = paa.business_group_id
    AND    paat.business_group_id = p_business_group_id
    AND    paat.absence_attendance_type_id = paa.absence_attendance_type_id
    AND    paa.person_id = asg.person_id
    AND    asg.assignment_id = p_assignment_id
    AND    (paa.date_start between p_start_date AND p_end_date
           AND paa.date_end > p_end_date)
    UNION
    SELECT p_start_date start_date
           ,paa.date_end end_date
    FROM   per_absence_attendances paa
           ,per_absence_attendance_types paat
           ,per_all_assignments_f asg
    WHERE  paat.absence_category ='UL'
    AND    paat.business_group_id = paa.business_group_id
    AND    paat.business_group_id = p_business_group_id
    AND    paat.absence_attendance_type_id = paa.absence_attendance_type_id
    AND    paa.person_id = asg.person_id
    AND    asg.assignment_id = p_assignment_id
    AND    (paa.date_start < p_start_date
           AND paa.date_end between p_start_date AND p_end_date)
    UNION
    SELECT p_start_date start_date
           ,p_end_date end_date
    FROM   per_absence_attendances paa
           ,per_absence_attendance_types paat
           ,per_all_assignments_f asg
    WHERE  paat.absence_category ='UL'
    AND    paat.business_group_id = paa.business_group_id
    AND    paat.business_group_id = p_business_group_id
    AND    paat.absence_attendance_type_id = paa.absence_attendance_type_id
    AND    paa.person_id = asg.person_id
    AND    asg.assignment_id = p_assignment_id
    AND    (paa.date_start < p_start_date
           AND paa.date_end > p_end_date);

Line: 242

    SELECT marital_status
    FROM   per_all_people_f ppl
           ,per_all_assignments_f asg
    WHERE  asg.assignment_id = p_assignment_id
    AND    ppl.person_id = asg.person_id
    AND    p_date_earned BETWEEN asg.effective_start_date AND asg.effective_end_date
    AND    p_date_earned BETWEEN ppl.effective_start_date AND ppl.effective_end_date;

Line: 251

    SELECT COUNT(DISTINCT contact_person_id)
    FROM   per_contact_relationships pcr
           ,per_all_assignments_f asg
    WHERE  asg.person_id = pcr.person_id
    AND    asg.assignment_id = p_assignment_id
    AND    pcr.contact_type = 'C'
    AND    nvl(pcr.dependent_flag, 'N') = 'Y'
    AND    p_date_earned BETWEEN NVL(pcr.date_start,p_date_earned) AND NVL(pcr.date_end, TO_DATE('4712/12/31','YYYY/MM/DD'));

Line: 300

    SELECT base_name
           ,business_group_id
           ,template_name
    FROM   pay_element_templates
    WHERE  template_id   = p_template_id
    AND    template_type = 'U';

Line: 311

    SELECT element_type_id
    FROM   pay_element_types_f
    WHERE  business_group_id = cp_business_group_id
    AND    element_name      = cp_element_name;

Line: 318

    SELECT input_value_id
           ,effective_start_date
    FROM   pay_input_values_f
    WHERE  element_type_id = cp_element_type_id
    AND    name = cp_name;

Line: 326

    SELECT flex_value_set_id
    FROM   fnd_flex_value_sets
    WHERE  flex_value_set_name = 'HR_AE_RATE_NAME';

Line: 333

    SELECT pet.element_type_id
    FROM   pay_element_types_f pet
           ,pay_sub_classification_rules_f psc
           ,pay_element_classifications pec
    WHERE  pet.business_group_id = cp_business_group_id
    AND    pet.element_name      = cp_element_name
    AND    pet.element_type_id = psc.element_type_id
    AND    pet.business_group_id = psc.business_group_id
    AND    pec.classification_name = 'Subject to Social Insurance : Earnings'
    AND    pec.legislation_code = 'AE'
    AND    psc.classification_id = pec.classification_id;

Line: 348

    SELECT element_type_id
           ,effective_start_date
           ,effective_end_date
    FROM   pay_element_types_f
    WHERE  business_group_id = cp_business_group_id
    AND    element_name      = cp_element_name;

Line: 357

    SELECT classification_id
    FROM   pay_element_classifications pec
    WHERE  classification_name  = 'Subject to Social Insurance : Information'
    AND    legislation_code = 'AE';

Line: 413

          SELECT object_version_number
          FROM   pay_input_values_f
          WHERE  input_value_id = l_input_value_id;

Line: 422

        pay_input_value_api.update_input_value
          (p_validate                => FALSE
          ,p_effective_date          => l_effective_date
          ,p_datetrack_mode          => 'CORRECTION'
          ,p_input_value_id          => l_input_value_id
          ,p_object_version_number   => l_ov_number
          ,p_value_set_id            => l_value_set_id
          ,p_effective_start_date    => l_effective_start_date
          ,p_effective_end_date      => l_effective_end_date
          ,p_default_val_warning     => l_default_warning
          ,p_min_max_warning         => l_min_max_warning
          ,p_link_inp_val_warning    => l_link_inp_val_warning
          ,p_pay_basis_warning       => l_pay_basis_warning
          ,p_formula_warning         => l_formula_warning
          ,p_assignment_id_warning   => l_assignment_id_warning
          ,p_formula_message         => l_formula_message
          );

Line: 468

            SELECT pay_sub_classification_rules_s.nextval
            INTO   l_seq
            FROM   dual;

Line: 471

            pay_sub_class_rules_pkg.insert_row
              ( p_rowid                     => l_row_id
              ,p_sub_classification_rule_Id => l_seq
              ,p_effective_start_date       => l_el_effective_start_date
              ,p_effective_end_date         => l_el_effective_end_date
              ,p_element_type_id            => l_info_element_type_id
              ,p_classification_id          => l_classification_id
              ,p_business_group_id          => l_business_group_id
              ,p_legislation_code           => NULL
              ,p_last_update_date           => SYSDATE
              ,p_last_updated_by            => -1
              ,p_last_update_login          => -1
              ,p_created_by                 => -1
              ,p_creation_date              => SYSDATE);

Line: 504

          SELECT object_version_number
          FROM   pay_input_values_f
          WHERE  input_value_id = l_input_value_id;

Line: 513

        pay_input_value_api.update_input_value
          (p_validate                => FALSE
          ,p_effective_date          => l_effective_date
          ,p_datetrack_mode          => 'CORRECTION'
          ,p_input_value_id          => l_input_value_id
          ,p_object_version_number   => l_ov_number
         -- ,p_max_value                        => '0'
          ,p_min_value                        => '0'
          ,p_warning_or_error        => 'E'
          ,p_effective_start_date    => l_effective_start_date
          ,p_effective_end_date      => l_effective_end_date
          ,p_default_val_warning     => l_default_warning
          ,p_min_max_warning         => l_min_max_warning
          ,p_link_inp_val_warning    => l_link_inp_val_warning
          ,p_pay_basis_warning       => l_pay_basis_warning
          ,p_formula_warning         => l_formula_warning
          ,p_assignment_id_warning   => l_assignment_id_warning
          ,p_formula_message         => l_formula_message
          );

Line: 550

          SELECT object_version_number
          FROM   pay_input_values_f
          WHERE  input_value_id = l_input_value_id;

Line: 559

        pay_input_value_api.update_input_value
          (p_validate                => FALSE
          ,p_effective_date          => l_effective_date
          ,p_datetrack_mode          => 'CORRECTION'
          ,p_input_value_id          => l_input_value_id
          ,p_object_version_number   => l_ov_number
          ,p_max_value                        => '0'
          ,p_warning_or_error        => 'E'
          ,p_effective_start_date    => l_effective_start_date
          ,p_effective_end_date      => l_effective_end_date
          ,p_default_val_warning     => l_default_warning
          ,p_min_max_warning         => l_min_max_warning
          ,p_link_inp_val_warning    => l_link_inp_val_warning
          ,p_pay_basis_warning       => l_pay_basis_warning
          ,p_formula_warning         => l_formula_warning
          ,p_assignment_id_warning   => l_assignment_id_warning
          ,p_formula_message         => l_formula_message
          );

Line: 610

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Flat Amount Template'
    AND  template_type = 'T';

Line: 622

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 625

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 792

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 803

       INSERT INTO pay_ele_tmplt_class_usages
         (ele_template_classification_id
         ,classification_id
         ,template_id
         ,display_process_mode
         ,display_arrearage)
       VALUES
         (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
         ,l_classification_id
         ,l_template_id
         ,NULL
         ,NULL);

Line: 853

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Percentage of Basic Salary Template'
    AND  template_type = 'T';

Line: 865

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 868

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 1043

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 1054

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 1095

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Grade Allowance Template'
    AND  template_type = 'T';

Line: 1107

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 1110

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 1296

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 1307

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 1355

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Housing Allowance Template'
    AND  template_type = 'T';

Line: 1367

        DELETE FROM pay_ele_tmplt_class_usages
        WHERE  template_id = l_template_id;

Line: 1370

        pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 1779

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 1790

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 1833

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Transport Allowance Template'
    AND  template_type = 'T';

Line: 1845

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 1848

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 2168

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 2179

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 2222

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Cost of Living Allowance Template'
    AND  template_type = 'T';

Line: 2234

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 2237

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 2479

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 2490

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 2532

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Children Social Allowance Template'
    AND  template_type = 'T';

Line: 2544

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 2547

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 2788

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 2799

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 2839

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Social Allowance Template'
    AND  template_type = 'T';

Line: 2851

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 2854

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 3048

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 3059

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 3101

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Shift Allowance Template'
    AND  template_type = 'T';

Line: 3113

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 3116

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 3370

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 3381

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 3423

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Hourly Salary Template'
    AND  template_type = 'T';

Line: 3435

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 3438

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 3641

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 3652

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 3696

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Overtime Allowance Template'
    AND  template_type = 'T';

Line: 3708

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 3711

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 3994

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Earnings';

Line: 4005

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 4052

    SELECT template_id
    FROM   pay_element_templates
    WHERE  template_name = 'Unpaid Leave Template'
    AND  template_type = 'T';

Line: 4064

      DELETE FROM pay_ele_tmplt_class_usages
      WHERE  template_id = l_template_id;

Line: 4067

      pay_element_template_api.delete_user_structure
             (p_validate              =>     false
             ,p_drop_formula_packages =>     true
             ,p_template_id           =>     l_template_id);

Line: 4408

        SELECT classification_id
        FROM   pay_element_classifications
        WHERE  legislation_code = 'AE'
        AND    classification_name = 'Absence';

Line: 4419

        INSERT INTO pay_ele_tmplt_class_usages
          (ele_template_classification_id
          ,classification_id
          ,template_id
          ,display_process_mode
          ,display_arrearage)
        VALUES
          (PAY_ELE_TMPLT_CLASS_USG_S.NEXTVAL
          ,l_classification_id
          ,l_template_id
          ,NULL
          ,NULL);

Line: 4446

    SELECT enabled_flag
    FROM   fnd_currencies
    WHERE  currency_code = 'AED';

Line: 4456

    UPDATE fnd_currencies
    SET enabled_flag = 'Y'
    WHERE currency_code = 'AED'
    AND   enabled_flag <> 'Y';

Line: 4485

    UPDATE fnd_currencies
    SET enabled_flag = l_enabled_flag
    WHERE currency_code = 'AED';