eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSL_CSP_INV_LOC_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: 20


   SELECT *
   FROM CSP_INV_LOC_ASSIGNMENTS
   WHERE CSP_INV_LOC_ASSIGNMENT_ID = b_csp_inv_loc_assignment_id;

Line: 103


PROCEDURE Insert_ACC_Record
  ( p_csp_inv_loc_assignment_id IN NUMBER
   ,p_resource_id        IN NUMBER
  )
IS
  l_success       BOOLEAN;

Line: 114


    , v_message     => 'Entering Insert_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 122


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

Line: 126


  /*** Call common package to insert record into ACC table ***/
  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_csp_inv_loc_assignment_id
   ,p_resource_id            => p_resource_id
  );

Line: 139


    , v_message     => 'Leaving Insert_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 143


END Insert_ACC_Record;

Line: 146


PROCEDURE Update_ACC_Record
  ( p_csp_inv_loc_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
    ( v_object_id   => p_csp_inv_loc_assignment_id
    , v_object_name => g_table_name
    , v_message     => 'Entering Update_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 171


  /*** Update Inventor Location Assignment 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: 183


    , v_message     => 'Leaving Update_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 186


END Update_ACC_Record;

Line: 188


/*** Private procedure that deletes assignment for resource from acc table ***/
PROCEDURE Delete_ACC_Record
  ( p_csp_inv_loc_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
    ( v_object_id   => p_csp_inv_loc_assignment_id
    , v_object_name => g_table_name
    , v_message     => 'Entering Delete_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 213


  /*** Delete Inventor Location 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_csp_inv_loc_assignment_id
    ,p_resource_id            => p_resource_id
   );

Line: 226


    , v_message     => 'Leaving Delete_ACC_Record'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 229


END Delete_ACC_Record;

Line: 232


  Public function that gets called when a Inventory Location 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_csp_inv_loc_assignment_id     IN NUMBER
   ,p_resource_id                   IN NUMBER
  )
RETURN BOOLEAN
IS
  CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
         SELECT organization_id, subinventory_code
         FROM CSP_INV_LOC_ASSIGNMENTS
         WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;

Line: 257


    , v_message     => 'Entering Pre_Insert_Child procedure'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 269


      IF CSL_CSP_SEC_INV_ACC_PKG.Insert_CSP_Sec_Inventory(
                                 p_resource_id
		  ,r_retreive_org_name.subinventory_code
		  ,r_retreive_org_name.organization_id) THEN

        Insert_ACC_Record
          ( p_csp_inv_loc_assignment_id
          , p_resource_id
          );

Line: 279


        CSL_MTL_MAT_TRANS_ACC_PKG.Insert_MTL_Mat_Transaction(
                                      p_resource_id,
                                      r_retreive_org_name.subinventory_code,
                                      r_retreive_org_name.organization_id
	                     );

Line: 296


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

Line: 301


END Pre_Insert_Child;

Line: 304


  Public procedure that gets called when a Inventory Location Assignment needs to be deleted from ACC table.
***/
PROCEDURE Post_Delete_Child
  ( p_csp_inv_loc_assignment_id IN NUMBER
   ,p_resource_id IN NUMBER
  )
IS

  CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
         SELECT organization_id, subinventory_code
         FROM CSP_INV_LOC_ASSIGNMENTS
         WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;

Line: 329


    , 'Entering Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 333


    /*** no -> delete task record from ACC ***/
  Delete_ACC_Record(
                   p_csp_inv_loc_assignment_id
                  ,p_resource_id);

Line: 342


    l_return_value := CSL_CSP_SEC_INV_ACC_PKG.Delete_CSP_Sec_Inventory(
                                     p_resource_id
                                    ,r_retreive_org_name.subinventory_code
                                    ,r_retreive_org_name.organization_id);

Line: 347


/*    CSL_MTL_MAT_TRANS_ACC_PKG.Delete_MTL_Mat_Transaction(
                                     p_resource_id,
                                     r_retreive_org_name.subinventory_code,
                                     r_retreive_org_name.organization_id); */

Line: 359


    , 'Leaving Post_Delete_Child'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 362


END Post_Delete_Child;

Line: 364


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

Line: 371


END PRE_INSERT_INV_LOC_ASSIGNMENT;

Line: 373


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

Line: 382


   SELECT resource_id
   FROM   csp_inv_loc_assignments
   WHERE  csp_inv_loc_assignment_id = b_csp_inv_loc_assignment_id;

Line: 399


    , v_message     => 'Entering POST_INSERT hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 409


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

Line: 429


    , v_message     => 'Leaving POST_INSERT hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 442


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

Line: 445


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','POST_INSERT_INV_LOC_ASSIGNMENT',sqlerrm);

Line: 448


END POST_INSERT_INV_LOC_ASSIGNMENT;

Line: 450


/* Called before assignment Update */
PROCEDURE PRE_UPDATE_INV_LOC_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  CURSOR c_csp_inv_loc_assignment( b_csp_inv_loc_ass_id NUMBER ) IS
   SELECT resource_id
   FROM   csp_inv_loc_assignments -- don't use synonym as that one filters on OWNER records
   WHERE  csp_inv_loc_assignment_id = b_csp_inv_loc_ass_id;

Line: 479


    , v_message     => 'Entering PRE_UPDATE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 493


    , v_message     => 'Leaving PRE_UPDATE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 505


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

Line: 508


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','PRE_UPDATE_INV_LOC_ASSIGNMENT',sqlerrm);

Line: 511


END PRE_UPDATE_INV_LOC_ASSIGNMENT;

Line: 513


/* Called after assignment Update */
PROCEDURE POST_UPDATE_INV_LOC_ASSIGNMENT
  ( x_return_status OUT NOCOPY varchar2
  )
IS
  CURSOR c_retreive_org_name(b_loc_assignment_id NUMBER) IS
         SELECT organization_id, subinventory_code
         FROM CSP_INV_LOC_ASSIGNMENTS
         WHERE csp_inv_loc_assignment_id = b_loc_assignment_id;

Line: 532


   SELECT resource_id
   FROM   csp_inv_loc_assignments -- don't use synonym as that one filters on OWNER records
   WHERE  csp_inv_loc_assignment_id = b_csp_inv_loc_ass_id;

Line: 552


    , v_message     => 'Entering POST_UPDATE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 568


    /*** 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_csp_inv_loc_assignment
      , g_table_name
      , 'Invntory Location 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: 579


    /*** DELETE SEQUENCE !!!!!! ***/

  ELSE
    /*** 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_csp_inv_loc_assignment
                    ,P_RESOURCE_ID    => l_resource_id);

Line: 590


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

Line: 593


    /*** Check if it is going to be Update or Insert! ***/
    IF l_acc_id = -1 THEN
    /*** Insert ! ***/
      l_dummy := Pre_Insert_Child
        (  l_csp_inv_loc_assignment
          ,l_resource_id
        );

Line: 601


    /*** Update ! ***/
      OPEN c_retreive_org_name( l_csp_inv_loc_assignment );

Line: 605


        CSL_CSP_SEC_INV_ACC_PKG.Update_CSP_Sec_Inventory(
                                   l_resource_id ,
                                   r_retreive_org_name.subinventory_code ,
                                   r_retreive_org_name.organization_id);

Line: 610


        Update_ACC_Record
          ( l_csp_inv_loc_assignment
           , l_resource_id
           , l_acc_id
           );

Line: 624


      /*** 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_csp_inv_loc_assignment
        , g_table_name
        , 'Inventory Location Assignment was replicated before update, but should not be replicated anymore.'
        , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 633


      Post_Delete_Child
      ( l_csp_inv_loc_assignment
       ,l_resource_id);

Line: 643


    , v_message     => 'Leaving POST_UPDATE hook'
    , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 654


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

Line: 657


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','POST_UPDATE_INV_LOC_ASSIGNMENT',sqlerrm);

Line: 660


END POST_UPDATE_INV_LOC_ASSIGNMENT;

Line: 662


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

Line: 687


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

Line: 691


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

Line: 702


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

Line: 712


    , 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: 715


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_INV_LOC_ASS_ACC_PKG','PRE_DELETE_INV_LOC_ASSIGNMENT',sqlerrm);

Line: 718


END PRE_DELETE_INV_LOC_ASSIGNMENT;

Line: 720


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

Line: 727


END POST_DELETE_INV_LOC_ASSIGNMENT;

Line: 730


PROCEDURE Delete_All_ACC_Records
  ( p_resource_id in NUMBER
  , x_return_status OUT NOCOPY varchar2
  )
IS

  CURSOR c_csp_inv_loc_assignment (b_resource_id NUMBER) IS
   SELECT *
   FROM jtm_csp_inv_loc_ass_acc
   WHERE RESOURCE_ID = b_resource_id;

Line: 751


    , 'Entering Delete_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 758


    , 'Delete all Inventory Location Assignemts acc records for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 774


      Post_Delete_Child
          ( r_csp_inv_loc_assignment.csp_inv_loc_assignment_id
          , p_resource_id
          );

Line: 787


    , 'Leaving Delete_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 794


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Delete_All_ACC_Records',sqlerrm);

Line: 797


END Delete_All_ACC_Records;

Line: 800


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

  CURSOR c_csp_inv_loc_assignment (b_resource_id NUMBER) IS
   SELECT *
   FROM CSP_INV_LOC_ASSIGNMENTS
   WHERE RESOURCE_ID = b_resource_id
   AND RESOURCE_TYPE = 'RS_EMPLOYEE' ;

Line: 823


    , 'Entering Insert_All_ACC_Records procedure for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 830


    , 'Insert all Inventory Location Assignments acc records for user: ' || p_resource_id
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 834


  /*** Insert all of the ACC Records of Requirement Lines ***/

  IF JTM_HOOK_UTIL_PKG.isMobileFSresource( p_resource_id ) THEN
    /*** Retreive record assigned by Hook ***/
    OPEN c_csp_inv_loc_assignment( p_resource_id );

Line: 850


        l_dummy := Pre_Insert_Child
             ( r_csp_inv_loc_assignment.csp_inv_loc_assignment_id
             , p_resource_id
             );

Line: 872


  fnd_msg_pub.Add_Exc_Msg('CSL_CSP_REQ_HEADERS_ACC_PKG','Insert_All_ACC_Records',sqlerrm);

Line: 875


END Insert_All_ACC_Records;