APPS.HR_RO_PERSON_VALIDATE SQL Statements

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

Line: 45

  SELECT 1
  FROM dual
  WHERE p_locality IN
    (SELECT pucif.value
    FROM pay_user_column_instances_f pucif,
      pay_user_columns puc,
      pay_user_tables put,
      pay_user_rows_f purf
    WHERE puc.user_column_name      = 'LOCALITY'
    AND puc.legislation_code        = 'RO'
    AND puc.business_group_id      IS NULL
    AND puc.user_table_id           = put.user_table_id
    AND pucif.user_column_id        = puc.user_column_id
    AND pucif.business_group_id     = p_business_group_id
    AND put.user_table_name         = 'RO_LOCALITY_DETAILS'
    AND put.legislation_code        = 'RO'
    AND put.business_group_id      IS NULL
    AND purf.user_table_id          = put.user_table_id
    AND pucif.user_row_id           = purf.user_row_id
    AND purf.business_group_id      = p_business_group_id
    AND purf.row_low_range_or_name IN
      (SELECT purf1.ROW_LOW_RANGE_OR_NAME
      FROM pay_user_rows_f purf1,
        pay_user_column_instances_f pucif1,
        pay_user_columns puc1,
        pay_user_column_instances_f pucif2,
        pay_user_columns puc2,
        pay_user_rows_f purf2
      WHERE puc1.user_column_name  = 'COUNTY CODE'
      AND puc1.legislation_code    = 'RO'
      AND puc1.business_group_id  IS NULL
      AND puc1.user_table_id       = put.user_table_id
      AND pucif1.user_column_id    = puc1.user_column_id
      AND pucif1.business_group_id = p_business_group_id
      AND pucif1.value             =
        (SELECT lookup_code
        FROM hr_lookups
        WHERE lookup_type = 'RO_PER_COUNTIES'
        AND enabled_flag  ='Y'
        AND meaning       = p_county
        )
    AND pucif1.user_row_id          = purf1.user_row_id
    AND purf1.user_table_id         = put.user_table_id
    AND purf1.business_group_id     = p_business_group_id
    AND purf1.row_low_range_or_name = purf2.row_low_range_or_name
    AND purf2.user_table_id         = put.user_table_id
    AND puc2.user_column_name       = 'LEVEL'
    AND puc2.legislation_code       = 'RO'
    AND puc2.business_group_id     IS NULL
    AND puc2.user_table_id          = put.user_table_id
    AND pucif2.user_column_id       = puc2.user_column_id
    AND pucif2.business_group_id    = p_business_group_id
    AND pucif2.value                = '3'
    AND purf2.user_row_id           = pucif2.user_row_id
    AND purf2.business_group_id     = p_business_group_id
      )
);

Line: 238

PROCEDURE validate_ro_employee_update(
    p_person_id NUMBER,
    --p_business_group_id  Number,
    p_first_name VARCHAR2,
    --p_hire_date           DATE,
    p_date_of_birth       DATE,
    p_sex                 VARCHAR2, -- gender
    p_national_identifier VARCHAR2,
    p_country_of_birth    VARCHAR2,
    p_region_of_birth     VARCHAR2,
    p_town_of_birth       VARCHAR2,
    p_per_information1    VARCHAR2, -- residence
    p_per_information2    VARCHAR2, -- citizenship
    p_per_information4    VARCHAR2, -- previous npc/frn
    p_effective_date      DATE )
AS
  l_business_group_id NUMBER;

Line: 269

    SELECT BUSINESS_GROUP_ID,
      ORIGINAL_DATE_OF_HIRE
    INTO l_BUSINESS_GROUP_ID,
      l_HIRE_DATE
    FROM PER_ALL_PEOPLE_F
    WHERE PERSON_ID = P_PERSON_ID
    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 284

	    SELECT national_identifier
	    INTO l_national_identifier
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 298

	    SELECT sex
	    INTO l_sex
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 312

	    SELECT country_of_birth
	    INTO l_country_of_birth
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 326

	    SELECT region_of_birth
	    INTO l_region_of_birth
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 340

	    SELECT per_information1
	    INTO l_per_information1
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 354

	    SELECT per_information2
	    INTO l_per_information2
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 368

	    SELECT per_information4
	    INTO l_per_information4
	    FROM PER_ALL_PEOPLE_F
	    WHERE PERSON_ID = P_PERSON_ID
	    AND P_EFFECTIVE_DATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;

Line: 394

END validate_ro_employee_update;