APPS.HRI_EDW_DIM_PERSON_TYPE SQL Statements

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

Line: 40

  SELECT map_to_type
  FROM hri_edw_user_person_types
  WHERE system_person_type = p_system_person_type
  AND user_person_type = p_user_person_type;

Line: 122

  SELECT ppt.system_person_type, ppt.user_person_type
  FROM per_person_type_usages_f ptu
  ,per_person_types ppt
  WHERE ptu.person_id = p_person_id
  AND TRUNC(p_effective_date)
      BETWEEN ptu.effective_start_date AND ptu.effective_end_date
  AND ptu.person_type_id = ppt.person_type_id;

Line: 132

  SELECT DECODE(student_status,null,null,'Y')
  FROM per_all_people_f
  WHERE TRUNC(p_effective_date)
        BETWEEN effective_start_date AND effective_end_date
  AND p_person_id = person_id;

Line: 218

  SELECT distinct person_id, effective_date
  FROM
  (SELECT person_id   person_id
  ,effective_start_date  effective_date
  FROM per_all_people_f
  UNION
  SELECT person_id
  ,effective_start_date
  FROM per_person_type_usages_f
  UNION
  SELECT person_id
  ,effective_end_date + 1
  FROM per_person_type_usages_f );

Line: 236

  SELECT 1
  FROM hri_person_type_cmbns
  WHERE person_type_pk = v_primary_key;

Line: 262

      INSERT INTO hri_person_type_cmbns
      ( person_type_pk
      , person_id
      , effective_date
      , employee
      , applicant
      , permanent
      , fixed_term_upper
      , fixed_term_lower
      , intern
      , ex_employee
      , ex_applicant
      , dependent
      , beneficiary
      , retiree
      , contact
      , surviving_family_member
      , surviving_spouse
      , former_family_member
      , former_spouse
      , other
      , participant
      , employee_student
      , consultant
      , agency
      , self_employed )
      VALUES
        ( l_person_type_pk
        , l_person_id
        , l_effective_date
        , g_employee
        , g_applicant
        , g_permanent
        , g_fixed_term_upper
        , g_fixed_term_lower
        , g_intern
        , g_ex_employee
        , g_ex_applicant
        , g_dependent
        , g_beneficiary
        , g_retiree
        , g_contact
        , g_srvivng_fmly_mbr
        , g_srvivng_spouse
        , g_former_fmly_mbr
        , g_former_spouse
        , g_other
        , g_participant
        , g_employee_student
        , g_consultant
        , g_agency
        , g_self_employed );

Line: 325

/* Procedure to insert a row into the user person types table                 */
/******************************************************************************/
PROCEDURE insert_user_row( p_system_person_type      IN VARCHAR2,
                           p_user_person_type        IN VARCHAR2,
                           p_map_to_type             IN VARCHAR2 )
IS

  l_map_to_type        VARCHAR2(30);  -- Existing type to map to

Line: 334

/* Selects the map to type for the row if it exists */
  CURSOR row_exists_cur IS
  SELECT map_to_type
  FROM hri_edw_user_person_types
  WHERE system_person_type = p_system_person_type
  AND user_person_type = p_user_person_type;

Line: 359

  /* If no row already exists for the given type combination, insert */
    INSERT INTO hri_edw_user_person_types
      ( system_person_type
      , user_person_type
      , map_to_type )
      VALUES
        ( p_system_person_type
        , p_user_person_type
        , p_map_to_type );

Line: 369

  /* Otherwise, if the map to type is different, update */
    UPDATE hri_edw_user_person_types
    SET map_to_type = p_map_to_type
    WHERE system_person_type = p_system_person_type
    AND user_person_type = p_user_person_type;

Line: 376

END insert_user_row;

Line: 388

/* Delete the row if it exists */
  DELETE FROM hri_edw_user_person_types
  WHERE system_person_type = p_system_person_type
  AND user_person_type = p_user_person_type;

Line: 397

/* The load row procedure calls the insert row API.                           */
/******************************************************************************/
PROCEDURE load_row(  p_system_person_type      IN VARCHAR2,
                     p_user_person_type        IN VARCHAR2,
                     p_map_to_type             IN VARCHAR2,
                     p_owner                   IN VARCHAR2 )
IS

BEGIN

/* Call to the insert row procedure above, which handles updates */
  insert_user_row( p_system_person_type, p_user_person_type, p_map_to_type);