APPS.CSL_JTF_TASK_REFS_ACC_PKG SQL Statements

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

Line: 17

  SELECT jtr.task_reference_id
    FROM jtf_tasks_b           jtb,
         jtf_task_references_b jtr,
         jtf_task_statuses_b   jts
   WHERE jtb.task_id = jtr.task_id
     AND jtb.task_type_id   = 22
     AND jtb.task_status_id = jts.task_status_id
     AND NVL(jts.closed_flag,    'N') <> 'Y'
     AND NVL(jts.completed_flag, 'N') <> 'Y'
     AND NVL(jts.cancelled_flag, 'N') <> 'Y'
     AND jtr.reference_code   = 'ESC'
     AND jtr.object_type_code = 'TASK'
     AND jtb.last_update_date >= NVL(b_last_run_date, jtb.LAST_UPDATE_DATE )
     AND jtr.object_id        = b_task_id
     AND jtr.task_reference_id NOT IN(
         SELECT task_reference_id
	 FROM   jtm_jtf_task_references_acc
	 WHERE  resource_id = b_resource_id );

Line: 38

  SELECT task_id
  ,      resource_id
  from   csl_jtf_tasks_acc;

Line: 43

  SELECT DISTINCT resource_id
  FROM   jtm_jtf_task_references_acc;

Line: 49

    select LAST_RUN_DATE
    from   JTM_CON_REQUEST_DATA
    where  package_name =  'CSL_JTF_TASK_REFS_ACC_PKG'
    AND    procedure_name = 'CON_REQUEST_TASK_REFERENCES';

Line: 98

  /*Update the last run date*/
  UPDATE JTM_CON_REQUEST_DATA
  SET LAST_RUN_DATE = SYSDATE
  WHERE package_name =  'CSL_JTF_TASK_REFS_ACC_PKG'
  AND   procedure_name = 'CON_REQUEST_TASK_REFERENCES';

Line: 113

        , 'Inserting escalation '||r_esc.task_reference_id||' for resource '||r_task.resource_id
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM
        );

Line: 118

      JTM_HOOK_UTIL_PKG.Insert_Acc
       ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
        ,P_ACC_TABLE_NAME         => g_acc_table_name
        ,P_RESOURCE_ID            => r_task.resource_id
        ,P_PK1_NAME               => g_pk1_name
        ,P_PK1_NUM_VALUE          => r_esc.task_reference_id
       );

Line: 129

  /*Delete all escaltions that are closed or no longer assigned to a mobile resource*/
  FOR r_esc_res IN c_esc_res LOOP
    DELETE_ALL_ACC_RECORDS( r_esc_res.resource_id, l_return_status );

Line: 156

PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id   IN NUMBER
                                , x_return_status OUT NOCOPY VARCHAR2 )
IS
 CURSOR c_no_esc ( b_resource_id NUMBER ) IS
  SELECT task_reference_id
  FROM   jtm_jtf_task_references_acc
  WHERE  resource_id = b_resource_id
  AND    task_reference_id NOT IN(
   SELECT jtr.task_reference_id
    FROM jtf_tasks_b           jtb,
         jtf_task_references_b jtr,
         jtf_task_statuses_b   jts
   WHERE jtb.task_id = jtr.task_id
     AND jtb.task_type_id   = 22
     AND jtb.task_status_id = jts.task_status_id
     AND NVL(jts.closed_flag,    'N') <> 'Y'
     AND NVL(jts.completed_flag, 'N') <> 'Y'
     AND NVL(jts.cancelled_flag, 'N') <> 'Y'
     AND jtr.reference_code   = 'ESC'
     AND jtr.object_type_code = 'TASK'
     AND jtr.object_id        IN (
      SELECT task_id
      from   csl_jtf_tasks_acc));

Line: 186

    , 'Entering DELETE_ALL_ACC_RECORDS'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
    );

Line: 200

    JTM_HOOK_UTIL_PKG.Delete_Acc
     ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
      ,P_ACC_TABLE_NAME         => g_acc_table_name
      ,P_RESOURCE_ID            => p_resource_id
      ,P_PK1_NAME               => g_pk1_name
      ,P_PK1_NUM_VALUE          => r_no_esc.task_reference_id
     );

Line: 215

    , 'Leaving DELETE_ALL_ACC_RECORDS'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
    );

Line: 231

END DELETE_ALL_ACC_RECORDS;