APPS.CSL_JTF_TASKS_ACC_PKG SQL Statements

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

Line: 12

g_replicate_pre_update  BOOLEAN; -- true when task was replicated before the update

Line: 23

   SELECT *
   FROM JTF_TASKS_B
   WHERE task_id = b_task_id;

Line: 29

   SELECT null
   FROM   JTF_TASK_STATUSES_B
   WHERE  TASK_STATUS_ID = b_task_status_id
   AND (
     NVL(ASSIGNED_FLAG,  'N') = 'Y'
     OR     NVL(CANCELLED_FLAG, 'N') = 'Y'
     OR     NVL(COMPLETED_FLAG, 'N') = 'Y'
     OR     NVL(CLOSED_FLAG,    'N') = 'Y'
   );

Line: 41

   SELECT null
   FROM   jtf_task_types_b
   WHERE  task_type_id = b_task_type_id
   AND    rule = 'DISPATCH';

Line: 48

    SELECT null
    FROM   jtf_task_types_b
    WHERE  task_type_id = b_task_type_id
    AND    private_flag = 'Y';

Line: 192

   SELECT source_object_id
   FROM   jtf_tasks_b
   WHERE  source_object_type_code = 'SR'
   AND    task_id = b_task_id;

Line: 209

PROCEDURE Insert_ACC_Record
  ( p_task_id     IN NUMBER
   ,p_resource_id IN NUMBER
   ,p_flow_type   IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
IS
  l_return BOOLEAN;

Line: 217

    SELECT *
    FROM JTF_TASKS_B
    WHERE TASK_ID = b_task_id;

Line: 228

    , 'Entering Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 236

    , 'Inserting ACC record for resource_id = ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 240

  /*** Insert task ACC record ***/
  JTM_HOOK_UTIL_PKG.Insert_Acc
   ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    ,P_ACC_TABLE_NAME         => g_acc_table_name
    ,P_PK1_NAME               => g_pk1_name
    ,P_PK1_NUM_VALUE          => p_task_id
    ,P_RESOURCE_ID            => p_resource_id
   );

Line: 249

  /*Insert the non critical dependant record*/
 IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
    jtm_message_log_pkg.Log_Msg
    ( p_task_id
    , g_table_name
    , 'Inserting non-critical dependant records'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 261

   l_return := CSL_JTF_NOTES_ACC_PKG.PRE_INSERT_CHILDREN
                                     ( P_SOURCE_OBJ_ID   => p_task_id
 				     , P_SOURCE_OBJ_CODE => 'TASK'
				     , P_RESOURCE_ID     => p_resource_id );

Line: 273

      CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( p_party_site_id => r_task.address_id
                                                  , p_resource_id => p_resource_id );

Line: 290

    , 'Leaving Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 293

END Insert_ACC_Record;

Line: 296

PROCEDURE Update_ACC_Record
  ( p_task_id            IN NUMBER
   ,p_resource_id        IN NUMBER
   ,p_acc_id             IN NUMBER
  )
IS
BEGIN
  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
    jtm_message_log_pkg.Log_Msg
    ( p_task_id
    , g_table_name
    , 'Entering Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 320

  JTM_HOOK_UTIL_PKG.Update_Acc
   ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    ,P_ACC_TABLE_NAME         => g_acc_table_name
    ,P_RESOURCE_ID            => p_resource_id
    ,P_ACCESS_ID              => p_acc_id
   );

Line: 331

    , 'Leaving Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 334

END Update_ACC_Record;

Line: 336

/*** Private procedure that deletes task for resource from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_task_id     IN NUMBER
   ,p_resource_id IN NUMBER
   ,p_flow_type   IN NUMBER
  )
IS
  CURSOR c_task( b_task_id NUMBER ) IS
    SELECT *
    FROM JTF_TASKS_B
    WHERE TASK_ID = b_task_id;

Line: 355

    , 'Entering Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 367

  /*** Delete task ACC record ***/
  JTM_HOOK_UTIL_PKG.Delete_Acc
   ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name
    ,P_ACC_TABLE_NAME         => g_acc_table_name
    ,P_PK1_NAME               => g_pk1_name
    ,P_PK1_NUM_VALUE          => p_task_id
    ,P_RESOURCE_ID            => p_resource_id
   );

Line: 384

  /*Notes are not replicated for history so we do not need to delete them*/
  IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
    --NOTES
    CSL_JTF_NOTES_ACC_PKG.POST_DELETE_CHILDREN( P_SOURCE_OBJ_ID   => p_task_id
                                              , P_SOURCE_OBJ_CODE => 'TASK'
  					      , P_RESOURCE_ID     => p_resource_id );

Line: 397

      CSL_HZ_PARTY_SITES_ACC_PKG.DELETE_PARTY_SITE( p_party_site_id => r_task.ADDRESS_ID
                                                  , p_resource_id   => p_resource_id );

Line: 403

    /*CSL_LOBS_ACC_PKG.DELETE_ATTACHMENTS ( p_entity_name => 'JTF_TASKS_B',
                              p_primary_key => p_task_id,
                              p_resource_id => p_resource_id);*/

Line: 414

    , 'Leaving Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 417

END Delete_ACC_Record;

Line: 420

  Public function that gets called when a task needs to be inserted into ACC table.
  Returns TRUE when record already was or has been inserted into ACC table.
***/
FUNCTION Pre_Insert_Child
  ( p_task_id     IN NUMBER
   ,p_resource_id IN NUMBER
   ,p_flow_type   IN NUMBER ) --DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
RETURN BOOLEAN
IS
  l_acc_id  NUMBER;

Line: 440

    , 'Entering Pre_Insert_Child procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 459

        /*** yes -> insert parent SR ***/
        IF CSL_CS_INCIDENTS_ALL_ACC_PKG.Pre_Insert_Child( l_incident_id, p_resource_id, p_flow_type) THEN
          /*** yes -> replicate task ***/
          l_success := TRUE;

Line: 469

    /*** successful -> insert task acc record ***/
    Insert_ACC_Record
    ( p_task_id
    , p_resource_id
    , p_flow_type
    );

Line: 481

    , 'Leaving Pre_Insert_Child procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 486

END Pre_Insert_Child;

Line: 489

  Public procedure that gets called when a task needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
  ( p_task_id     IN NUMBER
   ,p_resource_id IN NUMBER
   ,p_flow_type   IN NUMBER )--DEFAULT CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
IS
  l_incident_id NUMBER;

Line: 505

    , 'Entering Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 509

  /*** delete task record from ACC ***/
  Delete_ACC_Record
  ( p_task_id
  , p_resource_id
  , p_flow_type);

Line: 515

  /*** call delete service request ***/
  IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
    jtm_message_log_pkg.Log_Msg
    ( p_task_id
    , g_table_name
    , 'Calling CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 524

  /*Do not delete sr when history, flow is sr -> task -> assignment */
  IF p_flow_type <> CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_HISTORY THEN
    /* delete the dependant SR */
    l_incident_id := GetParentId( p_task_id);

Line: 529

      CSL_CS_INCIDENTS_ALL_ACC_PKG.Post_Delete_Child( l_incident_id, p_resource_id );

Line: 537

    , 'Leaving Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 540

END Post_Delete_Child;

Line: 543

/* Called before task Insert */
PROCEDURE PRE_INSERT_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 550

END PRE_INSERT_TASK;

Line: 552

/* Called after task Insert */
PROCEDURE POST_INSERT_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
 l_task_id NUMBER;

Line: 559

  SELECT tk.SOURCE_OBJECT_TYPE_CODE
  ,      au.RESOURCE_ID
  FROM JTF_TASKS_B tk
  ,    ASG_USER au
  WHERE tk.TASK_ID = b_task_id
  AND   tk.CREATED_BY = au.USER_ID;

Line: 580

    , 'Entering POST_INSERT_TASK hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 590

     Insert_ACC_Record
      ( l_task_id
      , r_task.RESOURCE_ID
      , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
      );

Line: 603

    , 'Leaving POST_INSERT_TASK hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 614

    , 'Caught exception in POST_INSERT_TASK hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 617

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_INSERT_TASK',sqlerrm);

Line: 620

END POST_INSERT_TASK;

Line: 622

/* Called before task Update */
PROCEDURE PRE_UPDATE_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_task_id NUMBER;

Line: 630

   SELECT ADDRESS_ID
   FROM JTF_TASKS_B
   WHERE TASK_ID = b_task_id;

Line: 649

    , 'Entering PRE_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 653

  /*** Check if task before update matched criteria ***/
  g_replicate_pre_update := Replicate_Record( l_task_id );

Line: 666

    , 'Leaving PRE_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 676

    , 'Caught exception in PRE_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 679

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_TASK',sqlerrm);

Line: 682

END PRE_UPDATE_TASK;

Line: 684

/* Called after task Update */
PROCEDURE POST_UPDATE_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  CURSOR c_task_assignment( b_task_id NUMBER )
  IS
   SELECT task_assignment_id, resource_id
   FROM   jtf_task_assignments
   WHERE  task_id = b_task_id;

Line: 697

   SELECT ADDRESS_ID
   FROM JTF_TASKS_B
   WHERE TASK_ID = b_task_id;

Line: 726

    , 'Entering POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 730

  /*** Check if task after update matches criteria ***/
  l_replicate := Replicate_Record( l_task_id );

Line: 733

  /*** replicate record after update? ***/
  IF l_replicate THEN
    /*** yes -> was record already replicated? ***/
    IF g_replicate_pre_update THEN
      /*** yes -> re-send updated task record to all resources ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( l_task_id
        , g_table_name
        , 'Task was replicateable before and after update. Re-sending task record to mobile users.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 766

          Update_ACC_Record
          ( l_task_id
           ,l_tab_resource_id(i)
           ,l_tab_access_id(i)
          );

Line: 778

  	      CSL_HZ_PARTY_SITES_ACC_PKG.INSERT_PARTY_SITE( l_address_id
   		                                          , l_tab_resource_id(i));

Line: 786

        record was not replicated before update but should be replicated now ->
        send record related data to all resources
      ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( l_task_id
        , g_table_name
        , 'Task was not replicated before update, but should be replicated now.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 809

        /*** insert task assignment data ***/
        l_dummy := CSL_JTF_TASK_ASS_ACC_PKG.Pre_Insert_Child
        ( r_task_assignment.task_assignment_id
         ,r_task_assignment.resource_id
         );

Line: 818

    IF g_replicate_pre_update THEN
      /*** yes -> delete record related data for all resources ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( l_task_id
        , g_table_name
        , 'Task was replicated before update, but should no longer be replicated.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 839

        /*** delete task assignment data ***/
        CSL_JTF_TASK_ASS_ACC_PKG.Post_Delete_Child
          ( r_task_assignment.task_assignment_id
           ,r_task_assignment.resource_id
          );

Line: 854

    , 'Leaving POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 864

    , 'Caught exception in POST_UPDATE hook:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 867

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASKS_ACC_PKG','POST_UPDATE_TASK',sqlerrm);

Line: 870

END POST_UPDATE_TASK;

Line: 872

/* Called before task Delete */
PROCEDURE PRE_DELETE_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 879

END PRE_DELETE_TASK;

Line: 881

/* Called after task Delete */
PROCEDURE POST_DELETE_TASK
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;

Line: 888

END POST_DELETE_TASK;

Line: 890

PROCEDURE INSERT_ALL_ACC_RECORDS
  ( p_resource_id   IN  NUMBER
  , x_return_status OUT NOCOPY VARCHAR2 ) IS

 CURSOR c_task( b_resource_id NUMBER ) IS
  SELECT tk.task_id
  FROM JTF_TASKS_B tk
  ,    ASG_USER au
  WHERE tk.SOURCE_OBJECT_TYPE_CODE = 'SR'
  AND   tk.CREATED_BY = au.USER_ID
  AND   au.RESOURCE_ID = b_resource_id;

Line: 907

    , 'Entering POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 911

  /*** insert all tasks created by resource ***/
  FOR r_task IN c_task( p_resource_id ) LOOP
     Insert_ACC_Record
      ( r_task.task_id
      , p_resource_id
      , CSL_CS_INCIDENTS_ALL_ACC_PKG.G_FLOW_NORMAL
      );

Line: 924

    , 'Leaving POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 936

    , 'Caught exception in INSERT_ALL_ACC_RECORDS:' || fnd_global.local_chr(10) || sqlerrm
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 940

END INSERT_ALL_ACC_RECORDS;

Line: 948

SELECT  acc.task_id, acc.resource_id
FROM    csl_jtf_tasks_acc acc,
        jtf_tasks_b jt,
        jtf_task_statuses_b jts
WHERE   acc.task_id = jt.task_id
AND     jts.task_status_id = jt.task_status_id
AND     (NVL(jt.scheduled_start_date, SYSDATE) < (SYSDATE - TO_NUMBER(FND_PROFILE.Value('CSL_APPL_HISTORY_IN_DAYS'))))
AND     (NVL(jts.cancelled_flag,'N') = 'Y'
        OR NVL(jts.closed_flag, 'N') = 'Y'
        OR NVL(jts.completed_flag, 'N') = 'Y'
        OR NVL(jts.rejected_flag, 'N') = 'Y')
AND     source_object_type_code = 'TASK';

Line: 968

         Post_Delete_Child(rcur_purge_tasks.task_id, rcur_purge_tasks.resource_id);