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_JOBH SQL Statements

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

Line: 72


  SELECT 'KEY'
  FROM fnd_id_flex_segments_vl
  WHERE application_id = 800
  AND id_flex_code = 'JOB'
  AND flex_value_set_id = p_value_set_id
  AND rownum = 1;

Line: 80


  SELECT 'DESCRIPTIVE'
  FROM
   fnd_descr_flex_col_usage_vl  dfcu
  WHERE dfcu.descriptive_flexfield_name = 'PER_JOBS'
  AND dfcu.application_id = 800
  AND dfcu.flex_value_set_id = p_value_set_id
  AND rownum = 1;

Line: 89


  SELECT
   flexfield_type
  FROM
   hri_cnfg_jobh_flex_cols
  WHERE job_fmly_column IS NOT NULL
  AND rownum = 1;

Line: 97


  SELECT
   flexfield_type
  FROM
   hri_cnfg_jobh_flex_cols
  WHERE job_fnctn_column IS NOT NULL
  AND rownum = 1;

Line: 190


  SELECT
   to_char(fsg.id_flex_num)          job_flex_code
  ,MIN(fsg.application_column_name)  flex_column
  FROM
   fnd_id_flex_segments_vl  fsg
  WHERE fsg.application_id = 800
  AND fsg.id_flex_code = 'JOB'
  AND fsg.flex_value_set_id = v_valueset_id
  GROUP BY fsg.id_flex_num;

Line: 201


  SELECT
   DECODE(ctxt.global_flag,
            'Y',  'NA_EDW',
          dfcu.descriptive_flex_context_code)  job_flex_code
  ,dfcu.application_column_name                flex_column
  ,ctxt.global_flag                            global_flag
  FROM
   fnd_descr_flex_col_usage_vl  dfcu
  ,fnd_descr_flex_contexts_vl   ctxt
  WHERE dfcu.descriptive_flexfield_name = 'PER_JOBS'
  AND dfcu.application_id = 800
  AND dfcu.flex_value_set_id = v_valueset_id
  AND ctxt.application_id = dfcu.application_id
  AND ctxt.descriptive_flexfield_name = dfcu.descriptive_flexfield_name
  AND ctxt.descriptive_flex_context_code = dfcu.descriptive_flex_context_code;

Line: 313


    INSERT INTO hri_cnfg_jobh_flex_cols
      (flexfield_type
      ,job_flex_code
      ,job_fnctn_column
      ,job_fmly_column)
      VALUES
       (l_index_type
       ,l_index_code
       ,job_flex_cache(l_index).job_fnctn_column
       ,job_flex_cache(l_index).job_fmly_column);

Line: 446


      l_fmly_sql := 'SELECT ' || g_rtn
                 ||  'id_char id' ||g_rtn
                 || ',hri_oltp_view_message.get_unassigned_msg value' || g_rtn
                 || ',hr_general.start_of_time start_date' || g_rtn
                 || ',hr_general.end_of_time end_date' || g_rtn
                 || ',''2'' order_by' || g_rtn
                 || 'FROM hri_unassigned';

Line: 464


'SELECT
 id_char
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time
,hr_general.end_of_time
,''2''
FROM hri_unassigned';

Line: 497


'SELECT
 id_char
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time
,hr_general.end_of_time
,''2''
FROM hri_unassigned';

Line: 540


     l_fnctn_sql := 'SELECT ' || g_rtn
                ||  'id_char id' ||g_rtn
                ||  ',hri_oltp_view_message.get_unassigned_msg value' || g_rtn
                ||  ',hr_general.start_of_time start_date' || g_rtn
                ||  ',hr_general.end_of_time end_date' || g_rtn
                ||  ',''2'' order_by' || g_rtn
                ||  'FROM hri_unassigned';

Line: 558


'SELECT
 id_char
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time
,hr_general.end_of_time
,''2''
FROM hri_unassigned';

Line: 591


'SELECT
 id_char
,hri_oltp_view_message.get_unassigned_msg value
,hr_general.start_of_time
,hr_general.end_of_time
,''2''
FROM hri_unassigned';

Line: 620


       'SELECT DISTINCT' || g_rtn
    ||   'job.job_fmly_code || '' ('' || job.job_fnctn_code || '')'' fmfn_fmlyfnct_pk' || g_rtn
    ||   ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
    || '' ('' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || '')'' fmfn_fmlyfnct_name' || g_rtn
    ||   ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg)
    || '' ('' || NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) || '')'' fmfn_fmlyfnct_name_unq' || g_rtn
    ||   ',job.job_fmly_code  fmfn_family_fk' || g_rtn
    ||   ',NVL(jfm.value,hri_oltp_view_message.get_unassigned_msg) fmfn_family_name' || g_rtn
    ||   ',job.job_fnctn_code  fmfn_function_fk' || g_rtn
    ||   ',NVL(jfn.value,hri_oltp_view_message.get_unassigned_msg) fmfn_function_name' || g_rtn
    ||   ',(CASE' || g_rtn
    ||   '    WHEN jfm.value IS NULL THEN' || g_rtn
    ||   '      CASE WHEN jfn.value IS NULL THEN NULL' || g_rtn
    ||   '           WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
    ||   '           ELSE jfn.value' || g_rtn
    ||   '      END'  || g_rtn
    ||   '    WHEN jfn.value IS NULL THEN' || g_rtn
    ||   '      CASE WHEN jfm.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
    ||   '           ELSE jfm.value' || g_rtn
    ||   '      END'  || g_rtn
    ||   '    WHEN  jfm.value  = hri_oltp_view_message.get_unassigned_msg THEN' || g_rtn
    ||   '      CASE WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN NULL' || g_rtn
    ||   '           ELSE jfn.value' || g_rtn
    ||   '      END' || g_rtn
    ||   '    WHEN jfn.value = hri_oltp_view_message.get_unassigned_msg THEN jfm.value' || g_rtn
    ||   '    ELSE jfm.value || '' ('' || jfn.value || '')''' || g_rtn
    ||   '  END) fmfn_order_by'  || g_rtn
    ||   ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
    ||   '(' || l_fmly_sql || ') jfm,' || g_rtn
    ||    '(' || l_fnctn_sql || ') jfn' || g_rtn
    || 'WHERE job.job_fmly_code = jfm.id(+)' || g_rtn
    || 'AND job.job_fnctn_code = jfn.id(+)';

Line: 658


     ||   ' FROM (SELECT distinct job_fmly_code,job_fnctn_code FROM hri_cs_jobh_ct) job,' || g_rtn
*/

    l_view_sql :=
'CREATE OR REPLACE FORCE VIEW hri_obi_cl_job_fmlyfnct_v
 (fmfn_fmlyfnct_pk
 ,fmfn_fmlyfnct_name
 ,fmfn_fmlyfnct_name_unq
 ,fmfn_family_fk
 ,fmfn_family_name
 ,fmfn_function_fk
 ,fmfn_function_name
 ,fmfn_order_by)
 AS
' || l_vset_lov_sql;

Line: 709


  SELECT
   job_fmly_column
  ,job_fnctn_column
  FROM
   hri_cnfg_jobh_flex_cols
  WHERE flexfield_type = p_flex_type
  AND job_flex_code = p_flex_code;

Line: 1030


'INSERT INTO hri_cs_jobh_ct
 (job_id
 ,job_fmly_code
 ,job_fnctn_code
 ,last_update_date
 ,last_update_login
 ,last_updated_by
 ,created_by
 ,creation_date )
SELECT
 job.job_id       job_id
,' || l_job_fmly_column  || '
,' || l_job_fnctn_column || '
,:l_current_time
,:l_user_id
,:l_user_id
,:l_user_id
,:l_current_time
FROM
 per_jobs                 job
,per_job_definitions      pjd
WHERE job.job_definition_id = pjd.job_definition_id
UNION ALL
SELECT
 -1          job_id
,''NA_EDW''  job_fmly_code
,''NA_EDW''  job_fnctn_code
,:l_current_time
,:l_user_id
,:l_user_id
,:l_user_id
,:l_current_time
FROM dual';

Line: 1092


PROCEDURE update_hierarchy_table IS

  -- Values to populate WHO columns
  l_current_time       DATE    := SYSDATE;

Line: 1119


'INSERT INTO hri_cs_jobh_ct
  (job_id
  ,job_fmly_code
  ,job_fnctn_code
  ,last_update_date
  ,last_update_login
  ,last_updated_by
  ,created_by
  ,creation_date )
 SELECT
   job.job_id       job_id
  ,' || l_job_fmly_column  || '
  ,' || l_job_fnctn_column || '
  ,:l_current_time
  ,:l_user_id
  ,:l_user_id
  ,:l_user_id
  ,:l_current_time
  FROM
   per_jobs             job
  ,per_job_definitions  pjd
  WHERE  job.job_definition_id = pjd.job_definition_id
  AND NOT EXISTS
   (SELECT null
    FROM hri_cs_jobh_ct jobh
    WHERE jobh.job_id = job.job_id)';

Line: 1158


 DELETE FROM hri_cs_jobh_ct  jobh
 WHERE NOT EXISTS
  (SELECT null
   FROM per_jobs job
   WHERE job.job_id = jobh.job_id)
 AND jobh.job_id <> -1;

Line: 1169


'UPDATE hri_cs_jobh_ct jobh
 SET (job_fmly_code
     ,job_fnctn_code) =
     (SELECT
       ' || l_job_fmly_column  || '
      ,' || l_job_fnctn_column || '
      FROM
       per_jobs             job
      ,per_job_definitions  pjd
      WHERE job.job_definition_id = pjd.job_definition_id
      AND job.job_id = jobh.job_id)
 WHERE EXISTS
  (SELECT null
   FROM
    per_jobs            job
   ,per_job_definitions pjd
   WHERE job.job_definition_id = pjd.job_definition_id
   AND job.job_id = jobh.job_id
   AND (jobh.job_fmly_code <> ' || l_job_fmly_column  || '
     OR jobh.job_fnctn_code <> ' || l_job_fnctn_column || ')
  )
 RETURNING jobh.job_id INTO :l_upd_job_ids';

Line: 1208


        INSERT INTO HRI_EQ_ASG_SUP_WRFC
         (SOURCE_TYPE,
          SOURCE_ID)
      VALUES
         ('JOB',
          l_upd_job_ids(i));

Line: 1226


END update_hierarchy_table;

Line: 1302


  update_hierarchy_table;