APPS.HRI_OPL_JOB_JOB_ROLE SQL Statements

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

Line: 72

  dbg('Inserting into hri_cs_job_job_role_ct.');

Line: 74

  INSERT INTO HRI_CS_JOB_JOB_ROLE_CT
    (job_id
    ,job_role_code
    ,primary_role_for_job_flag
    ,created_by
    ,creation_date
    ,last_update_date
    ,last_updated_by
    ,last_update_login)
  SELECT
  job_id
  --
  -- Job Role is determined through a fast formula
  --
  ,job_role_code
  --
  -- Currently only primary job role is implemented
  --
  ,'Y'
  --
  -- WHO columns
  --
  ,l_user_id
  ,l_current_time
  ,l_current_time
  ,l_user_id
  ,l_user_id
  FROM hri_cs_job_job_role_v;

Line: 103

  dbg('Inserted '||sql%rowcount||' records.');

Line: 122

PROCEDURE update_job_job_roles IS
--
l_current_time                    DATE    := SYSDATE;

Line: 139

  dbg('Incrementaly inserting into hri_cs_job_job_role_ct.');

Line: 141

  INSERT INTO hri_cs_job_job_role_ct
    (job_id
    ,job_role_code
    ,primary_role_for_job_flag
    ,created_by
    ,creation_date
    ,last_update_date
    ,last_updated_by
    ,last_update_login)
  SELECT
     job_id
    --
    -- Job Role is determined through a fast formula
    --
    ,job_role_code
    --
    -- Currently only primary job role is implemented
    --
    ,'Y'
    --
    -- WHO columns
    --
    ,l_user_id
    ,l_current_time
    ,l_current_time
    ,l_user_id
    ,l_user_id
  FROM hri_cs_job_job_role_v jbrlv
  WHERE NOT EXISTS
    (SELECT null
     FROM hri_cs_job_job_role_ct jbrl
     WHERE jbrlv.job_id = jbrl.job_id);

Line: 176

  dbg('Inserted '||sql%rowcount||' records.');

Line: 180

  DELETE FROM hri_cs_job_job_role_ct jbrl
  WHERE NOT EXISTS
    (SELECT null
     FROM hri_cs_job_job_role_v jbrlv
     WHERE jbrlv.job_id = jbrl.job_id);

Line: 186

  dbg('Deleted '||sql%rowcount||' records.');

Line: 192

  UPDATE hri_cs_job_job_role_ct jbrl
  SET (jbrl.job_role_code
      ,primary_role_for_job_flag) =
      (SELECT
        jbrlv.job_role_code
       ,primary_role_for_job_flag
       FROM hri_cs_job_job_role_v jbrlv
       WHERE jbrlv.job_id = jbrl.job_id)
  WHERE EXISTS
    (SELECT NULL
     FROM   hri_cs_job_job_role_v jbrlv
     WHERE  jbrlv.job_id = jbrl.job_id
     AND    (
             (jbrlv.job_role_code <> jbrl.job_role_code)
              OR
             (jbrlv.primary_role_for_job_flag <> jbrl.primary_role_for_job_flag)
            )
     )
  RETURNING jbrl.job_id BULK COLLECT INTO l_upd_job_ids;

Line: 212

  dbg('Updated '||sql%rowcount||' records.');

Line: 228

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

Line: 252

    dbg('Error encountered in update_job_job_roles.');

Line: 256

END update_job_job_roles;

Line: 282

  update_job_job_roles;