APPS.HR_H2PI_VALIDATE SQL Statements

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

Line: 18

   SELECT * FROM hr_h2pi_bg_and_gre
   WHERE  client_id = p_from_client_id;

Line: 22

   SELECT organization_id
   FROM   hr_h2pi_bg_and_gre_v
   WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 27

  SELECT organization_id
  FROM   hr_h2pi_bg_and_gre_v bg
  WHERE  bg.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    bg.organization_id = p_organization_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_bg_and_gre bg1
                  WHERE  him.to_id = bg.organization_id
                  AND    bg1.organization_id = him.from_id
                  AND    him.table_name = 'HR_ALL_ORGANIZATION_UNITS'
                  AND    him.to_business_group_id = bg.business_group_id);

Line: 40

  SELECT organization_id
  FROM   hr_h2pi_bg_and_gre_v bg
  WHERE  bg.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    bg.organization_id = p_organization_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = bg.organization_id
                  AND    him.table_name = 'HR_ALL_ORGANIZATION_UNITS'
                  AND    him.to_business_group_id =
                                    hr_h2pi_upload.g_to_business_group_id);

Line: 64

    SELECT method_of_generation_emp_num
    INTO   l_method_of_gen_emp_num
    FROM   per_business_groups
    WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 98

        SELECT DISTINCT *
        INTO   l_ed_rec
        FROM   hr_h2pi_bg_and_gre_v
        WHERE  organization_id = l_organization_to_id;

Line: 197

  SELECT * FROM hr_h2pi_pay_bases
  WHERE  client_id = p_from_client_id;

Line: 201

  SELECT pay_basis_id
  FROM   hr_h2pi_pay_bases_v
  WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 206

  SELECT pay_basis_id
  FROM   hr_h2pi_pay_bases_v pay
  WHERE  pay.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    pay.pay_basis_id = p_pay_basis_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_pay_bases pay1
                  WHERE  him.to_id = pay.pay_basis_id
                  AND    pay1.pay_basis_id = him.from_id
                  AND    him.table_name = 'PER_PAY_BASES'
                  AND    him.to_business_group_id = pay.business_group_id);

Line: 219

  SELECT pay_basis_id
  FROM   hr_h2pi_pay_bases_v pay
  WHERE  pay.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    pay.pay_basis_id = p_pay_basis_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = pay.pay_basis_id
                  AND    him.table_name = 'PER_PAY_BASES'
                  AND    him.to_business_group_id =
                                 hr_h2pi_upload.g_to_business_group_id);

Line: 259

        SELECT *
        INTO   l_ed_rec
        FROM   hr_h2pi_pay_bases_v
        WHERE  pay_basis_id = l_pay_basis_to_id;

Line: 352

  SELECT * FROM hr_h2pi_payrolls
  WHERE  client_id = p_from_client_id;

Line: 356

  SELECT payroll_id
  FROM   hr_h2pi_payrolls_v
  WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 361

  SELECT payroll_id
  FROM   hr_h2pi_payrolls_v pay
  WHERE  pay.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    pay.payroll_id = p_payroll_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_payrolls pay1
                  WHERE  him.to_id = pay.payroll_id
                  AND    pay1.payroll_id = him.from_id
                  AND    him.table_name = 'PAY_ALL_PAYROLLS_F'
                  AND    him.to_business_group_id = pay.business_group_id);

Line: 374

  SELECT payroll_id
  FROM   hr_h2pi_payrolls_v pay
  WHERE  pay.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    pay.payroll_id = p_payroll_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = pay.payroll_id
                  AND    him.table_name = 'PAY_ALL_PAYROLLS_F'
                  AND    him.to_business_group_id =
                                   hr_h2pi_upload.g_to_business_group_id);

Line: 414

        SELECT *
        INTO   l_ed_rec
        FROM   hr_h2pi_payrolls_v
        WHERE  payroll_id = l_payroll_to_id
          AND  effective_start_date = v_ud_rec.effective_start_date
          AND  effective_end_date   = v_ud_rec.effective_end_date;

Line: 513

  SELECT * FROM hr_h2pi_element_types
  WHERE  client_id = p_from_client_id
  AND    legislation_code IS NULL;

Line: 518

  SELECT element_type_id
  FROM   hr_h2pi_element_types_v
  WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 523

  SELECT element_type_id
  FROM   hr_h2pi_element_types_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    ele.element_type_id = p_element_type_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_element_types ele1
                  WHERE  him.to_id = ele.element_type_id
                  AND    ele1.element_type_id = him.from_id
                  AND    him.table_name = 'PAY_ELEMENT_TYPES_F'
                  AND    him.to_business_group_id = ele.business_group_id);

Line: 536

  SELECT element_type_id
  FROM   hr_h2pi_element_types_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    ele.element_type_id = p_element_type_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = ele.element_type_id
                  AND    him.table_name = 'PAY_ELEMENT_TYPES_F'
                  AND    him.to_business_group_id =
                                hr_h2pi_upload.g_to_business_group_id);

Line: 576

        SELECT *
        INTO   l_ed_rec
        FROM   hr_h2pi_element_types_v
        WHERE  element_type_id = l_element_type_to_id
          AND  effective_start_date = v_ud_rec.effective_start_date
          AND  effective_end_date   = v_ud_rec.effective_end_date;

Line: 672

  SELECT * FROM hr_h2pi_org_payment_methods
  WHERE  client_id = p_from_client_id;

Line: 676

  SELECT org_payment_method_id
  FROM   hr_h2pi_org_payment_methods_v
  WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 681

  SELECT org_payment_method_id
  FROM   hr_h2pi_org_payment_methods_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    ele.org_payment_method_id = p_org_payment_method_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_org_payment_methods ele1
                  WHERE  him.to_id = ele.org_payment_method_id
                  AND    ele1.org_payment_method_id = him.from_id
                  AND    him.table_name = 'PAY_ORG_PAYMENT_METHODS_F'
                  AND    him.to_business_group_id = ele.business_group_id);

Line: 694

  SELECT org_payment_method_id
  FROM   hr_h2pi_org_payment_methods_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    ele.org_payment_method_id = p_org_payment_method_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = ele.org_payment_method_id
                  AND    him.table_name = 'PAY_ORG_PAYMENT_METHODS_F'
                  AND    him.to_business_group_id =
                                hr_h2pi_upload.g_to_business_group_id);

Line: 736

        SELECT *
        INTO   l_ed_rec
        FROM   hr_h2pi_org_payment_methods_v
        WHERE  org_payment_method_id = l_org_payment_method_to_id
          AND  effective_start_date  = v_ud_rec.effective_start_date
          AND  effective_end_date    = v_ud_rec.effective_end_date;

Line: 836

  SELECT el.* FROM hr_h2pi_element_links el,
                   hr_h2pi_element_types et
  WHERE  el.client_id = p_from_client_id
  AND    el.element_type_id = et.element_type_id
  AND    el.effective_start_date BETWEEN et.effective_start_date
                                     AND et.effective_end_date
  AND    et.legislation_code IS NULL
  AND    et.client_id = p_from_client_id;

Line: 846

   SELECT element_link_id
   FROM   hr_h2pi_element_links_v
   WHERE  business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 851

  SELECT element_link_id
  FROM   hr_h2pi_element_links_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_Id
  AND    ele.element_link_id = p_element_link_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him,
                         hr_h2pi_element_links ele1
                  WHERE  him.to_id = ele.element_link_id
                  AND    ele1.element_link_id = him.from_id
                  AND    him.table_name = 'PAY_ELEMENT_LINKS_F'
                  AND    him.to_business_group_id = ele.business_group_id);

Line: 864

  SELECT element_link_id
  FROM   hr_h2pi_element_links_v ele
  WHERE  ele.business_group_id = hr_h2pi_upload.g_to_business_group_id
  AND    ele.element_link_id = p_element_link_id
  AND NOT EXISTS (SELECT 'X'
                  FROM   hr_h2pi_id_mapping him
                  WHERE  him.to_id = ele.element_link_id
                  AND    him.table_name = 'PAY_ELEMENT_LINKS_F'
                  AND    him.to_business_group_id =
                                hr_h2pi_upload.g_to_business_group_id);

Line: 907

        SELECT *
        INTO   l_ed_rec
        FROM   hr_h2pi_element_links_v
        WHERE  element_link_id = l_element_link_to_id
          AND  effective_start_date  = v_ud_rec.effective_start_date
          AND  effective_end_date    = v_ud_rec.effective_end_date;

Line: 1024

  SELECT NVL(MAX(patch_name),'GEOCODE_1900_Q1') FROM hr_h2pi_patch_status
  WHERE  client_id = p_from_client_id;

Line: 1027

  SELECT NVL(MAX(patch_name),'GEOCODE_1900_Q1') FROM hr_h2pi_patch_status_v;

Line: 1048

  SELECT iv.input_value_id
  FROM   pay_input_values_f  iv,
         pay_element_types_f et
  WHERE  et.element_name IN ('VERTEX','Workers Compensation')
  AND    iv.name = 'Jurisdiction'
  AND    iv.element_type_id = et.element_type_id;

Line: 1056

  SELECT  jurisdiction_code jurisdiction_code
  FROM    hr_h2pi_city_tax_rules
  WHERE   client_id = p_from_client_id
  UNION ALL
  SELECT screen_entry_value jurisdiction_code
  FROM   hr_h2pi_element_entry_values
  WHERE  input_value_id in ( l_input_value1, l_input_value2)
  AND    screen_entry_value IS NOT NULL
  AND    client_id = p_from_client_id;

Line: 1069

  SELECT old_city_code
  FROM  hr_h2pi_us_modified_geocodes
  WHERE state_code    = p_state_code
  AND   county_code   = p_county_code
  AND   new_city_code = p_city_code;

Line: 1076

  SELECT jurisdiction_code jurisdiction_code
  FROM  hr_h2pi_city_tax_rules_v
  WHERE business_group_id = hr_h2pi_upload.g_to_business_group_id
  UNION ALL
  SELECT screen_entry_value jurisdiction_code
  FROM   hr_h2pi_element_entry_values_v eev,
         pay_input_values_f  iv,
         pay_element_types_f    et
  WHERE  screen_entry_value is NOT NULL
  AND    iv.element_type_id = et.element_type_id
  AND    eev.input_value_id = iv.input_value_id
  AND    et.element_name IN ('VERTEX','Workers Compensation')
  AND    iv.name = 'Jurisdiction'
  AND    eev.business_group_id = hr_h2pi_upload.g_to_business_group_id;

Line: 1094

  SELECT new_city_code
  FROM   hr_h2pi_us_modified_geocodes_v
  WHERE  state_code    = p_state_code
  AND    county_code   = p_county_code
  AND    new_city_code = p_city_code;

Line: 1114

  SELECT count(*)
  INTO   l_ed_more_then_two_ver_ahead
  FROM   hr_h2pi_patch_status_v
  WHERE  patch_name > l_ud_geocode;

Line: 1125

  SELECT count(*)
  INTO   l_ud_more_then_two_ver_ahead
  FROM   hr_h2pi_patch_status
  WHERE  patch_name > l_ed_geocode
  AND    client_id = p_from_client_id;