eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HRI_OPL_PERSON_TYPE_CTGRY SQL Statements

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

Line: 78


PROCEDURE insert_into_dim_levels(p_full_refresh   IN VARCHAR2) IS

  l_current_time         DATE := SYSDATE;

Line: 94


 (SELECT null
  FROM hri_cs_prsntyp_ct  dim
  WHERE dim.person_type_id = pty.person_type_id
  AND dim.primary_flag_code = pty.primary_flag_code
  AND dim.employment_category_code = pty.employment_category_code
  AND dim.assignment_type_code = pty.assignment_type_code)';

Line: 103


  (SELECT null
   FROM hri_cs_wkth_lvl1_ct  dim
   WHERE dim.wkth_lvl1_sk_pk = tab.wkth_lvl1_sk_pk)';

Line: 109


  (SELECT null
   FROM hri_cs_wkth_lvl2_ct  dim
   WHERE dim.wkth_lvl2_sk_pk = tab.wkth_lvl2_sk_pk)';

Line: 121


'INSERT INTO hri_cs_prsntyp_ct
   (prsntyp_sk_pk
   ,wkth_wktyp_sk_fk
   ,wkth_lvl1_sk_fk
   ,wkth_lvl2_sk_fk
   ,wkth_wktyp_code
   ,wkth_lvl1_code
   ,wkth_lvl2_code
   ,person_type_id
   ,primary_flag_code
   ,assignment_type_code
   ,employment_category_code
   ,include_flag_code
   ,last_update_date
   ,last_updated_by
   ,last_update_login
   ,created_by
   ,creation_date)
  SELECT
   hri_cs_prsntyp_ct_s.nextval         prsntyp_sk_pk
  ,pty.wkth_wktyp_sk_fk
  ,pty.wkth_lvl1_sk_fk
  ,pty.wkth_lvl2_sk_fk
  ,pty.wkth_wktyp_code
  ,pty.wkth_lvl1_code
  ,pty.wkth_lvl2_code
  ,pty.person_type_id
  ,pty.primary_flag_code
  ,pty.assignment_type_code
  ,pty.employment_category_code
  ,pty.include_flag_code
  ,:l_current_time
  ,:l_user_id
  ,:l_user_id
  ,:l_user_id
  ,:l_current_time
  FROM
   hri_cs_prsntyp_v  pty' ||
  l_incr_check;

Line: 171


    INSERT INTO hri_cs_prsntyp_ct
     (prsntyp_sk_pk
     ,wkth_wktyp_sk_fk
     ,wkth_lvl1_sk_fk
     ,wkth_lvl2_sk_fk
     ,wkth_wktyp_code
     ,wkth_lvl1_code
     ,wkth_lvl2_code
     ,person_type_id
     ,primary_flag_code
     ,assignment_type_code
     ,employment_category_code
     ,include_flag_code
     ,last_update_date
     ,last_updated_by
     ,last_update_login
     ,created_by
     ,creation_date)
    SELECT
     -1
    ,'NA_EDW'
    ,'NA_EDW-NA_EDW'
    ,'NA_EDW-NA_EDW-NA_EDW'
    ,'NA_EDW'
    ,'NA_EDW'
    ,'NA_EDW'
    ,-1
    ,'NA_EDW'
    ,'NA_EDW'
    ,'NA_EDW'
    ,'N'
    ,l_current_time
    ,l_user_id
    ,l_user_id
    ,l_user_id
    ,l_current_time
    FROM dual;

Line: 217


'INSERT INTO hri_cs_wkth_lvl2_ct
  (wkth_lvl2_sk_pk
  ,wkth_lvl1_sk_fk
  ,wkth_wktyp_sk_fk
  ,wkth_lvl2_code
  ,wkth_lvl1_code
  ,wkth_wktyp_code
  ,last_update_date
  ,last_updated_by
  ,last_update_login
  ,created_by
  ,creation_date)
 SELECT
  tab.wkth_lvl2_sk_pk
 ,tab.wkth_lvl1_sk_fk
 ,tab.wkth_wktyp_sk_fk
 ,tab.wkth_lvl2_code
 ,tab.wkth_lvl1_code
 ,tab.wkth_wktyp_code
 ,:l_current_time
 ,:l_user_id
 ,:l_user_id
 ,:l_user_id
 ,:l_current_time
 FROM
  (SELECT
    wktyp.lookup_code || ''-'' || lvl1.lookup_code || ''-'' || lvl2.lookup_code
                                                     wkth_lvl2_sk_pk
   ,wktyp.lookup_code || ''-'' || lvl1.lookup_code   wkth_lvl1_sk_fk
   ,wktyp.lookup_code                                wkth_wktyp_sk_fk
   ,lvl2.lookup_code                                 wkth_lvl2_code
   ,lvl1.lookup_code                                 wkth_lvl1_code
   ,wktyp.lookup_code                                wkth_wktyp_code
   FROM
    hr_standard_lookups  lvl2
   ,hr_standard_lookups  lvl1
   ,hr_standard_lookups  wktyp
   WHERE lvl2.lookup_type = ''HRI_CL_WKTH_LVL2''
   AND lvl1.lookup_type = ''HRI_CL_WKTH_LVL1''
   AND wktyp.lookup_type = ''HRI_CL_WKTH_WKTYP''
  )  tab
 WHERE EXISTS
  (SELECT null
   FROM hri_cs_prsntyp_ct  ptyp
   WHERE ptyp.wkth_lvl2_sk_fk = tab.wkth_lvl2_sk_pk
   AND ptyp.wkth_lvl1_sk_fk = tab.wkth_lvl1_sk_fk
   AND ptyp.wkth_wktyp_sk_fk = tab.wkth_wktyp_sk_fk)' ||
 l_lvl2_incr_check;

Line: 279


'INSERT INTO hri_cs_wkth_lvl1_ct
  (wkth_lvl1_sk_pk
  ,wkth_wktyp_sk_fk
  ,wkth_lvl1_code
  ,wkth_wktyp_code
  ,last_update_date
  ,last_updated_by
  ,last_update_login
  ,created_by
  ,creation_date)
 SELECT
  tab.wkth_lvl1_sk_pk
 ,tab.wkth_wktyp_sk_fk
 ,tab.wkth_lvl1_code
 ,tab.wkth_wktyp_code
 ,:l_current_time
 ,:l_user_id
 ,:l_user_id
 ,:l_user_id
 ,:l_current_time
 FROM
  (SELECT
    wktyp.lookup_code || ''-'' || lvl1.lookup_code   wkth_lvl1_sk_pk
   ,wktyp.lookup_code                                wkth_wktyp_sk_fk
   ,lvl1.lookup_code                                 wkth_lvl1_code
   ,wktyp.lookup_code                                wkth_wktyp_code
   FROM
    hr_standard_lookups  lvl1
   ,hr_standard_lookups  wktyp
   WHERE lvl1.lookup_type = ''HRI_CL_WKTH_LVL1''
   AND wktyp.lookup_type = ''HRI_CL_WKTH_WKTYP''
  )  tab
 WHERE EXISTS
  (SELECT null
   FROM hri_cs_wkth_lvl2_ct  ptyp
   WHERE ptyp.wkth_lvl1_sk_fk = tab.wkth_lvl1_sk_pk
   AND ptyp.wkth_wktyp_sk_fk = tab.wkth_wktyp_sk_fk)' ||
 l_lvl1_incr_check;

Line: 325


END insert_into_dim_levels;

Line: 330


PROCEDURE delete_from_dim_levels(p_full_refresh   IN VARCHAR2) IS

  l_hri_schema           VARCHAR2(300);

Line: 341


    DELETE FROM hri_cs_prsntyp_ct  dim
    WHERE dim.prsntyp_sk_pk <> -1
    AND dim.person_type_id NOT IN
     (SELECT ppt.person_type_id
      FROM per_person_types  ppt
      WHERE ppt.system_person_type IN ('EMP','CWK'));

Line: 349


    DELETE FROM hri_cs_prsntyp_ct  dim
    WHERE dim.employment_category_code <> 'NA_EDW'
    AND dim.prsntyp_sk_pk <> -1
    AND dim.employment_category_code NOT IN
     (SELECT hrl.lookup_code
      FROM hr_standard_lookups  hrl
      WHERE hrl.lookup_type IN ('EMP_CAT','CWK_ASG_CATEGORY')
      AND hrl.enabled_flag = 'Y');

Line: 369


END delete_from_dim_levels;

Line: 374


PROCEDURE update_dim_levels IS
 --
 -- PL/SQL table of updated person type records
 --
 TYPE l_number_tab_type IS TABLE OF hri_cs_geo_lochr_ct.location_id%TYPE;

Line: 385


  UPDATE hri_cs_prsntyp_ct dim
  SET
   (wkth_wktyp_sk_fk
   ,wkth_lvl1_sk_fk
   ,wkth_lvl2_sk_fk
   ,wkth_wktyp_code
   ,wkth_lvl1_code
   ,wkth_lvl2_code
   ,include_flag_code) =
  (SELECT
    vw.wkth_wktyp_sk_fk
   ,vw.wkth_lvl1_sk_fk
   ,vw.wkth_lvl2_sk_fk
   ,vw.wkth_wktyp_code
   ,vw.wkth_lvl1_code
   ,vw.wkth_lvl2_code
   ,vw.include_flag_code
   FROM hri_cs_prsntyp_v  vw
   WHERE vw.person_type_id = dim.person_type_id
   AND vw.employment_category_code = dim.employment_category_code
   AND vw.primary_flag_code = dim.primary_flag_code
   AND vw.assignment_type_code = dim.assignment_type_code)
  WHERE EXISTS
   (SELECT null
    FROM hri_cs_prsntyp_v  vw
    WHERE vw.person_type_id = dim.person_type_id
    AND vw.employment_category_code = dim.employment_category_code
    AND vw.primary_flag_code = dim.primary_flag_code
    AND vw.assignment_type_code = dim.assignment_type_code
    AND (vw.wkth_wktyp_code   <> dim.wkth_wktyp_code
      OR vw.wkth_lvl1_code    <> dim.wkth_lvl1_code
      OR vw.wkth_lvl2_code    <> dim.wkth_lvl2_code
      OR vw.include_flag_code <> dim.include_flag_code))
  RETURNING dim.prsntyp_sk_pk BULK COLLECT INTO l_upd_prsntyp_sks;

Line: 431


            INSERT INTO HRI_EQ_ASG_SUP_WRFC
             (SOURCE_TYPE,
              SOURCE_ID)
          VALUES
             ('PERSON_TYPE',
              l_upd_prsntyp_sks(i));

Line: 446


END update_dim_levels;

Line: 459


  delete_from_dim_levels(p_full_refresh => 'Y');

Line: 462


  insert_into_dim_levels(p_full_refresh => 'Y');

Line: 480


  delete_from_dim_levels(p_full_refresh => 'N');

Line: 483


  insert_into_dim_levels(p_full_refresh => 'N');

Line: 486


  update_dim_levels;