DBA Data[Home] [Help]

APPS.CSL_CSF_DEBRIEF_LINE_ACC_PKG SQL Statements

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

Line: 14

/*** cache variables used by pre/post update ***/
CURSOR c_update_cache_rec( b_debrief_line_id NUMBER)
IS
 SELECT inventory_item_id
 , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
        , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
 FROM   csf_debrief_lines
 WHERE  debrief_line_id = b_debrief_line_id;
Line: 22

g_pre_update_rec c_update_cache_rec%ROWTYPE;
Line: 31

   SELECT debrief_header_id
   FROM CSF_DEBRIEF_LINES
   WHERE debrief_line_id = b_debrief_line_id;
Line: 85

   SELECT *
   FROM CSF_DEBRIEF_LINES
   WHERE debrief_line_id = b_debrief_line_id;
Line: 133

PROCEDURE Insert_ACC_Record
  ( p_debrief_line_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_debrief_line_id
    , g_table_name
    , 'Entering Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 151

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

  /*** Insert debrief_line 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_debrief_line_id
    ,P_RESOURCE_ID            => p_resource_id
   );
Line: 168

    , 'Leaving Insert_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 171

END Insert_ACC_Record;
Line: 174

PROCEDURE Update_ACC_Record
  ( p_debrief_line_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_debrief_line_id
    , g_table_name
    , 'Entering Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 197

  /*** Update debrief_line ACC record ***/
  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: 209

    , 'Leaving Update_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 212

END Update_ACC_Record;
Line: 214

/*** Private procedure that deletes debrief_line for resource from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_debrief_line_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_debrief_line_id
    , g_table_name
    , 'Entering Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 237

  /*** Delete debrief_line 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_debrief_line_id
    ,P_RESOURCE_ID            => p_resource_id
   );
Line: 250

    , 'Leaving Delete_ACC_Record'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 253

END Delete_ACC_Record;
Line: 256

  Public function that gets called when a debrief_line 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_debrief_line_id     IN NUMBER
   ,p_resource_id         IN NUMBER
  )
RETURN BOOLEAN
IS
  CURSOR c_debrief_line( b_debrief_line_id NUMBER)
  IS
   SELECT inventory_item_id
   , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
          , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
   FROM   csf_debrief_lines
   WHERE  debrief_line_id = b_debrief_line_id;
Line: 285

    , 'Entering Pre_Insert_Child procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 294

    /*** Insert the debrief header ***/
    CSL_CSF_DEBRIEF_HDR_ACC_PKG.Insert_Debrief_Header
    ( l_debrief_header_id
     ,p_resource_id
    );
Line: 300

    /*** insert the system item used by the debrief line ***/
    OPEN c_debrief_line( p_debrief_line_id );
Line: 306

        CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
         p_inventory_item_id => r_debrief_line.inventory_item_id
        ,p_organization_id   => r_debrief_line.organization_id
        ,p_resource_id       => p_resource_id
       );
Line: 315

    /*** yes -> insert debrief_line acc record ***/
    Insert_ACC_Record
    ( p_debrief_line_id
     ,p_resource_id
    );
Line: 328

    , 'Leaving Pre_Insert_Child procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 333

END Pre_Insert_Child;
Line: 336

  Public procedure that gets called when debrief lines need to be inserted into ACC table.
***/
PROCEDURE Pre_Insert_Children
  ( p_task_assignment_id  IN NUMBER
   ,p_resource_id         IN NUMBER
  )
IS
  CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
   SELECT CDL.debrief_line_id
   FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
   WHERE CDH.task_assignment_id = b_task_assignment_id
   AND   CDH.debrief_header_id = CDL.debrief_header_id;
Line: 359

    , 'Entering Pre_Insert_Children procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 365

    /*** Insert record if applicable ***/
    l_dummy := Pre_Insert_Child
      ( r_debrief_line.debrief_line_id
        ,p_resource_id
      );
Line: 376

        , 'Pre_Insert_Children:  debrief line was not insertable.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
Line: 386

    , 'Leaving Pre_Insert_Children procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 390

END Pre_Insert_Children;
Line: 393

  Public procedure that gets called when a debrief_line needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
  ( p_debrief_line_id     IN NUMBER
   ,p_resource_id         IN NUMBER
  )
IS
  CURSOR c_debrief_line( b_debrief_line_id NUMBER)
  IS
   SELECT inventory_item_id
   , NVL( NVL(issuing_inventory_org_id, receiving_inventory_org_id)
          , FND_PROFILE.VALUE('CS_INV_VALIDATION_ORG')) AS organization_id
   FROM   csf_debrief_lines
   WHERE  debrief_line_id = b_debrief_line_id;
Line: 420

    , 'Entering Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 424

  /*** no -> delete debrief_line record from ACC ***/
  Delete_ACC_Record
  ( p_debrief_line_id
   ,p_resource_id);
Line: 429

  /*** delete the system item used by the debrief line ***/
  OPEN c_debrief_line( p_debrief_line_id );
Line: 435

      CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
       p_inventory_item_id => r_debrief_line.inventory_item_id
      ,p_organization_id   => r_debrief_line.organization_id
      ,p_resource_id       => p_resource_id
     );
Line: 447

  /*** Delete the debrief header ***/
  CSL_CSF_DEBRIEF_HDR_ACC_PKG.Delete_Debrief_Header
  ( l_debrief_header_id
   ,p_resource_id
  );
Line: 457

    , 'Leaving Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 460

END Post_Delete_Child;
Line: 463

  Public procedure that gets called when debrief lines need to be deleted into ACC table.
***/
PROCEDURE Post_Delete_Children
  ( p_task_assignment_id  IN NUMBER
   ,p_resource_id         IN NUMBER
  )
IS
  CURSOR c_debrief_line (b_task_assignment_id NUMBER) IS
   SELECT CDL.debrief_line_id
   FROM CSF_DEBRIEF_HEADERS CDH, CSF_DEBRIEF_LINES CDL
   WHERE CDH.task_assignment_id = b_task_assignment_id
   AND   CDH.debrief_header_id = CDL.debrief_header_id;
Line: 485

    , 'Entering Post_Delete_Children procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 491

      /*** Delete record if applicable ***/
      Post_Delete_Child
      (  r_debrief_line.debrief_line_id
        ,p_resource_id
      );
Line: 502

    , 'Leaving Post_Delete_Children procedure'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 506

END Post_Delete_Children;
Line: 516

   SELECT resource_id
   FROM   jtf_task_assignments jta
   ,      csf_debrief_headers  dbh
   ,      csf_debrief_lines    dbl
   WHERE  jta.task_assignment_id = dbh.task_assignment_id
   AND    dbh.debrief_header_id  = dbl.debrief_header_id
   AND    dbl.debrief_line_id = b_debrief_line_id;
Line: 547

/* Called before debrief_line Insert */
PROCEDURE PRE_INSERT_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;
Line: 554

END PRE_INSERT_DEBRIEF_LINE;
Line: 556

/* Called after debrief_line Insert */
PROCEDURE POST_INSERT_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_debrief_line_id  NUMBER;
Line: 578

    , 'Entering POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 593

      , 'POST_INSERT_DEBRIEF_LINE' || fnd_global.local_chr(10) ||
        'Resource_id ' || l_resource_id || ' is not a mobile user.'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 599

    /*** Insert record if applicable ***/
    l_dummy := Pre_Insert_Child
      (  l_debrief_line_id
        ,l_resource_id
      );
Line: 610

    , 'Leaving POST_INSERT hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 623

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

  fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','POST_INSERT_DEBRIEF_LINE',sqlerrm);
Line: 629

END POST_INSERT_DEBRIEF_LINE;
Line: 631

/* Called before debrief_line Update */
PROCEDURE PRE_UPDATE_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  l_debrief_line_id   NUMBER;
Line: 655

    , 'Entering PRE_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 663

  /*** Check if debrief_line before update matches criteria ***/
  IF JTM_HOOK_UTIL_PKG.isMobileFSresource( l_resource_id ) THEN
    g_pre_replicate := Replicate_Record( l_debrief_line_id );
Line: 673

        , 'Debrief line was replicated before update.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 678

    OPEN c_update_cache_rec( l_debrief_line_id );
Line: 679

    FETCH c_update_cache_rec INTO g_pre_update_rec;
Line: 680

    CLOSE c_update_cache_rec;
Line: 686

    , 'Debrief line was not replicated before update'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 696

    , 'Leaving PRE_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 706

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

  fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','PRE_UPDATE_DEBRIEF_LINE',sqlerrm);
Line: 713

END PRE_UPDATE_DEBRIEF_LINE;
Line: 715

/* Called after debrief_line Update */
PROCEDURE POST_UPDATE_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS

  l_debrief_line_id   NUMBER;
Line: 729

  l_post_update_rec   c_update_cache_rec%ROWTYPE;
Line: 743

    , 'Entering POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 751

  /*** Check if debrief_line after update matches criteria ***/
  l_replicate := FALSE;
Line: 762

        , 'Debrief line should be replicated after update.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 769

    , 'Debrief line should not be replicated after update'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 773

  /*** Check results from pre update ***/
  IF g_pre_replicate THEN

    /*** replicate record after update? ***/
    IF NOT l_replicate THEN

      /*** No -> Delete the record ***/
      Post_Delete_Child
      ( l_debrief_line_id
      , l_resource_id );
Line: 788

        , 'Debrief line was deleted during post update.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 794

      /*** yes -> re-send updated debrief record to resource ***/
      IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
        jtm_message_log_pkg.Log_Msg
        ( l_debrief_line_id
        , g_table_name
        , 'Debrief line being re-sent to mobile user.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 810

      /* Update the debrief line */
      Update_ACC_Record
      ( l_debrief_line_id
       ,l_resource_id
       ,l_access_id
      );
Line: 818

      OPEN c_update_cache_rec( l_debrief_line_id );
Line: 819

      FETCH c_update_cache_rec INTO l_post_update_rec;
Line: 820

      IF (g_pre_update_rec.inventory_item_id <> l_post_update_rec.inventory_item_id
       OR g_pre_update_rec.organization_id <> l_post_update_rec.organization_id) THEN
        -- yes -> remove old item and insert new item
        IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
          jtm_message_log_pkg.Log_Msg
          ( l_debrief_line_id
          , g_table_name
          , 'System item changed -> deleting old item and inserting new item.'
          , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 831

        CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Post_Delete_Child (
         p_inventory_item_id => g_pre_update_rec.inventory_item_id
        ,p_organization_id   => g_pre_update_rec.organization_id
        ,p_resource_id       => l_resource_id
        );
Line: 837

        CSL_MTL_SYSTEM_ITEMS_ACC_PKG.Pre_Insert_Child (
         p_inventory_item_id => l_post_update_rec.inventory_item_id
        ,p_organization_id   => l_post_update_rec.organization_id
        ,p_resource_id       => l_resource_id
        );
Line: 843

      CLOSE c_update_cache_rec;
Line: 847

    /*** record was not replicated before update -> replicate now ***/
    IF g_debug_level >= JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM THEN
      jtm_message_log_pkg.Log_Msg
      ( l_debrief_line_id
      , g_table_name
      , 'Debrief line was inserted during post update.'
      , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);
Line: 856

    /*** Insert record if applicable ***/
    l_dummy := Pre_Insert_Child
      (  l_debrief_line_id
        ,l_resource_id
      );
Line: 867

    , 'Leaving POST_UPDATE hook'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 879

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

  fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','POST_UPDATE_DEBRIEF_LINE',sqlerrm);
Line: 885

END POST_UPDATE_DEBRIEF_LINE;
Line: 887

/* Called before debrief_line Delete */
PROCEDURE PRE_DELETE_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS

  l_debrief_line_id NUMBER;
Line: 905

    , v_message     => 'Entering PRE_DELETE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 913

  /*** Delete debrief line from ACC table. This also deletes its Debrief Header ***/
  Post_Delete_Child ( l_debrief_line_id
                    , l_resource_id );
Line: 923

    , v_message     => 'Leaving PRE_DELETE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);
Line: 934

    , v_message     => 'Caught exception in PRE_DELETE hook:' || fnd_global.local_chr(10) || sqlerrm
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_ERROR);
Line: 938

  fnd_msg_pub.Add_Exc_Msg('CSL_CSF_DEBRIEF_LINE_ACC_PKG','PRE_DELETE_DEBRIEF_LINE',sqlerrm);
Line: 941

END PRE_DELETE_DEBRIEF_LINE;
Line: 943

/* Called after debrief_line Delete */
PROCEDURE POST_DELETE_DEBRIEF_LINE
  ( x_return_status OUT NOCOPY varchar2
  )
IS
BEGIN
  x_return_status := FND_API.G_RET_STS_SUCCESS;
Line: 950

END POST_DELETE_DEBRIEF_LINE;