APPS.CSL_JTF_TASK_ASS_ACC_PKG SQL Statements

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

Line: 10

g_old_resource_id       NUMBER; -- variable containing old resource_id; populated in Pre_Update hook

Line: 23

   SELECT *
   FROM JTF_TASK_ASSIGNMENTS -- don't use synonym as that one filters on OWNER records
   WHERE task_assignment_id = b_task_assignment_id
   AND resource_type_code = 'RS_EMPLOYEE';

Line: 31

   SELECT null
   FROM   JTF_TASK_STATUSES_B
   WHERE  TASK_STATUS_ID = b_assignment_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: 43

   SELECT tk.TASK_ID
   FROM JTF_TASK_ASSIGNMENTS ta
   ,    JTF_TASKS_B tk
   WHERE   tk.TASK_ID = ta.TASK_ID
   AND   tk.SOURCE_OBJECT_TYPE_CODE = 'TASK'
   AND   ta.TASK_ASSIGNMENT_ID = b_task_assignment_id;

Line: 151

PROCEDURE Insert_ACC_Record
  ( p_task_assignment_id IN NUMBER
   ,p_resource_id        IN NUMBER
  )
IS
   --Bug 3724142
   CURSOR c_debrief_header ( b_task_assignment_id NUMBER)
   IS  SELECT debrief_header_id FROM csf_debrief_headers
      WHERE task_assignment_id = b_task_assignment_id;

Line: 168

    , 'Entering Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 176

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

Line: 180

  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_assignment_id
   ,P_RESOURCE_ID            => p_resource_id
  );

Line: 188

  /* insert debrief */
  CSL_CSF_DEBRIEF_LINE_ACC_PKG.Pre_Insert_Children
  ( p_task_assignment_id
   ,p_resource_id
  );

Line: 199

  /* insert attachment - signature record */
  --Bug 3724142 - changed p_task_assignment_id to l_debrief_header_id
  CSL_LOBS_ACC_PKG.insert_acc_record
  ( l_debrief_header_id
   ,p_resource_id
  );

Line: 211

    , 'Leaving Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 214

END Insert_ACC_Record;

Line: 217

PROCEDURE Update_ACC_Record
  ( p_task_assignment_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_assignment_id
    , g_table_name
    , 'Entering Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 240

  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: 251

    , 'Leaving Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 254

END Update_ACC_Record;

Line: 256

/*** Private procedure that deletes assignment for resource from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_task_assignment_id IN NUMBER
   ,p_resource_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_assignment_id
    , g_table_name
    , 'Entering Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 271

  /*  Delete Attachments - Signature */
  CSL_LOBS_ACC_PKG.delete_acc_record
  ( p_task_assignment_id
   ,p_resource_id
  );

Line: 278

  /* delete debrief header, lines */
  CSL_CSF_DEBRIEF_LINE_ACC_PKG.Post_Delete_Children
  ( p_task_assignment_id,
    p_resource_id
  );

Line: 292

  /*** Delete task assignment 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_assignment_id
    ,P_RESOURCE_ID            => p_resource_id
   );

Line: 305

    , 'Leaving Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 308

END Delete_ACC_Record;

Line: 316

   SELECT task_id
   FROM   jtf_task_assignments -- don't use synonym as that one filters on OWNER records
   WHERE  task_assignment_id = b_task_assignment_id;

Line: 330

      , 'Post_Delete_Child error: Could not find task_assignment_id ' || p_task_assignment_id
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);

Line: 342

  Public function that gets called when an assignment 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_assignment_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_success BOOLEAN;

Line: 361

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

Line: 370

      /*** yes -> is insert task acc record successful? ***/
      IF CSL_JTF_TASKS_ACC_PKG.Pre_Insert_Child( GetParentId( p_task_assignment_id), p_resource_id) THEN
        /*** yes -> insert assignment acc record ***/
        Insert_ACC_Record
        ( p_task_assignment_id
         ,p_resource_id
        );

Line: 380

      Insert_ACC_Record
      ( p_task_assignment_id
       ,p_resource_id
      );

Line: 392

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

Line: 397

END Pre_Insert_Child;

Line: 400

  Public procedure that gets called when an assignment needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
  ( p_task_assignment_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_acc_id NUMBER;

Line: 417

    , 'Entering Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 429

    /*** yes -> delete assignment record from ACC ***/
    Delete_ACC_Record
    ( p_task_assignment_id
     ,p_resource_id);

Line: 434

    /*** call delete task ***/
    IF g_debug_level = JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL THEN
      jtm_message_log_pkg.Log_Msg
      ( p_task_assignment_id
      , g_table_name
      , 'Calling CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 444

      CSL_JTF_TASKS_ACC_PKG.Post_Delete_Child( GetParentId( p_task_assignment_id), p_resource_id );

Line: 452

    , 'Leaving Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 455

END Post_Delete_Child;

Line: 459

PROCEDURE INSERT_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 ) IS
 CURSOR c_task_assignment( b_resource_id NUMBER ) IS
  SELECT TASK_ASSIGNMENT_ID
  FROM   JTF_TASK_ASSIGNMENTS
  WHERE  ASSIGNEE_ROLE = 'ASSIGNEE'
  AND    RESOURCE_ID = b_resource_id;

Line: 468

 l_return_status := Pre_Insert_Child
  ( r_task_assignment.task_assignment_id
   ,p_resource_id);

Line: 477

END INSERT_ALL_ACC_RECORDS;

Line: 480

PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2 ) IS
 CURSOR c_task_assignment( b_resource_id NUMBER ) IS
  SELECT TASK_ASSIGNMENT_ID
  FROM   JTF_TASK_ASSIGNMENTS
  WHERE  ASSIGNEE_ROLE = 'ASSIGNEE'
  AND    RESOURCE_ID = b_resource_id;

Line: 488

 Post_Delete_Child
  ( r_task_assignment.task_assignment_id
   ,p_resource_id);

Line: 497

END DELETE_ALL_ACC_RECORDS;

Line: 500

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

Line: 507

END PRE_INSERT_TASK_ASSIGNMENT;

Line: 509

/*** Called after assignment Insert ***/
PROCEDURE POST_INSERT_TASK_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_resource_id        NUMBER;

Line: 535

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

Line: 539

  /*** Insert record if applicable ***/
  l_dummy := Pre_Insert_Child
    (  l_task_assignment_id
      ,l_resource_id
    );

Line: 549

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

Line: 562

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

Line: 565

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','POST_INSERT_TASK_ASSIGNMENT',sqlerrm);

Line: 568

END POST_INSERT_TASK_ASSIGNMENT;

Line: 570

/* Called before assignment Update */
PROCEDURE PRE_UPDATE_TASK_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  CURSOR c_task_assignment( b_task_assignment_id NUMBER ) IS
   SELECT resource_id
   FROM   jtf_task_assignments -- don't use synonym as that one filters on OWNER records
   WHERE  task_assignment_id = b_task_assignment_id;

Line: 599

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

Line: 613

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

Line: 625

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

Line: 628

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','PRE_UPDATE_TASK_ASSIGNMENT',sqlerrm);

Line: 631

END PRE_UPDATE_TASK_ASSIGNMENT;

Line: 633

/* Called after assignment Update */
PROCEDURE POST_UPDATE_TASK_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_resource_id        NUMBER;

Line: 661

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

Line: 667

    /*** yes -> do cascading delete for old resource_id ***/
    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
      jtm_message_log_pkg.Log_Msg
      ( l_task_assignment_id
      , g_table_name
      , 'Task assignment resource_id changed from ' || g_old_resource_id || ' to ' || l_resource_id || '.' || fnd_global.local_chr(10) ||
        'Deleting old assignment ACC record (if exists).'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 677

    Post_Delete_Child
    ( l_task_assignment_id
     ,g_old_resource_id);

Line: 684

    /*** resource_id is same as before the update -> check if it already exists on mobile ***/
    l_acc_id := JTM_HOOK_UTIL_PKG.Get_Acc_Id
                   ( P_ACC_TABLE_NAME => g_acc_table_name
                    ,P_PK1_NAME       => g_pk1_name
                    ,P_PK1_NUM_VALUE  => l_task_assignment_id
                    ,P_RESOURCE_ID    => l_resource_id);

Line: 692

  /*** check if updated record needs to be replicated ***/
  l_replicate := Replicate_Record( l_task_assignment_id );

Line: 697

      /*** record doesn't exist on mobile but should be replicated -> Insert ***/
      l_dummy := Pre_Insert_Child
        (  l_task_assignment_id
          ,l_resource_id);

Line: 703

      Update_ACC_Record
        ( l_task_assignment_id
         ,l_resource_id
         ,l_acc_id);

Line: 711

      /*** record exists on mobile but should not be replicated anymore -> delete from mobile ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( l_task_assignment_id
        , g_table_name
        , 'Task assignment was replicated before update, but should not be replicated anymore.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 720

      Post_Delete_Child
      ( l_task_assignment_id
       ,l_resource_id);

Line: 730

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

Line: 741

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

Line: 744

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','POST_UPDATE_TASK_ASSIGNMENT',sqlerrm);

Line: 747

END POST_UPDATE_TASK_ASSIGNMENT;

Line: 749

/* Called before assignment Delete */
PROCEDURE PRE_DELETE_TASK_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_resource_id        NUMBER;

Line: 774

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

Line: 778

  /*** yes -> delete assignment related data from the ACC tables ***/
  Post_Delete_Child
  ( l_task_assignment_id
   ,l_resource_id);

Line: 789

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

Line: 799

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

Line: 802

  fnd_msg_pub.Add_Exc_Msg('CSL_JTF_TASK_ASS_ACC_PKG','PRE_DELETE_TASK_ASSIGNMENT',sqlerrm);

Line: 805

END PRE_DELETE_TASK_ASSIGNMENT;

Line: 807

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

Line: 814

END POST_DELETE_TASK_ASSIGNMENT;

Line: 823

SELECT  acc.task_assignment_id, acc.resource_id
FROM    csl_jtf_task_ass_acc acc,
        jtf_task_assignments jta,
        jtf_tasks_b jt,
        jtf_task_statuses_b jts,
        jtf_task_statuses_b jta_jts
WHERE   acc.task_assignment_id = jta.task_assignment_id
AND     jt.task_id = jta.task_id
AND     jts.task_status_id = jt.task_status_id
AND     jta_jts.task_status_id = jta.assignment_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'
        OR NVL(jta_jts.cancelled_flag,'N') = 'Y'
        OR NVL(jta_jts.closed_flag,'N') = 'Y'
        OR NVL(jta_jts.completed_flag,'N') = 'Y'
        OR NVL(jta_jts.rejected_flag, 'N') = 'Y')
AND     NOT EXISTS (SELECT 'x' FROM CSL_SERVICE_HISTORY hist
                    WHERE hist.history_incident_id = jt.source_object_id
                    AND jt.source_object_type_code = 'SR'
                    AND hist.resource_id = acc.resource_id);

Line: 855

         Post_Delete_Child(rcur_purge_task_assignments.task_assignment_id, rcur_purge_task_assignments.resource_id);