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_MTL_MAT_TRANS_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: 13


PROCEDURE Insert_MTL_Mat_Transaction(
                                      p_resource_id         NUMBER,
                                      p_subinventory_code  VARCHAR2,
                                      p_organization_id     NUMBER
	                     )
IS

CURSOR c_mtl_mat_transactions (b_subinventory_code VARCHAR2, b_organization_id NUMBER) IS
       SELECT TRANSACTION_ID, INVENTORY_ITEM_ID
       FROM   MTL_MATERIAL_TRANSACTIONS
       WHERE  SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
       AND    ORGANIZATION_ID   = b_organization_id
       AND    TRANSACTION_ACTION_ID = 2 --Subinventory transfer
       AND    SOURCE_CODE = 'CSP';

Line: 37


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

Line: 50


      , v_message     => 'Insert_MTL_Mat_Transaction => no transactions for sub inventory :' ||
                         p_subinventory_code ||', organization '||p_organization_id||
                         ' for resource id ' || p_resource_id|| ' found'
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 63


        , v_message     => 'Inserting ACC record :' || p_subinventory_code || ' , ' || p_organization_id ||
                           ' for resource id ' || p_resource_id
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 70


      /*** 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_resource_id            => p_resource_id
       ,p_pk1_name               => g_pk1_name
       ,p_pk1_num_value          => r_mtl_mat_transactions.TRANSACTION_ID
      );

Line: 79


      /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
      CSL_MTL_TRANS_LOT_NUM_ACC_PKG.Insert_MTL_trans_lot_num(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id
                                      );

Line: 86


      CSL_MTL_UNIT_TRANS_ACC_PKG.Insert_MTL_Unit_Trans(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id,
		        p_subinventory_code   => p_subinventory_code
                                      );

Line: 102


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

Line: 106


END Insert_MTL_Mat_Transaction;

Line: 109


PROCEDURE Update_MTL_Mat_Transaction(
                                      p_resource_id         NUMBER,
                                      p_subinventory_code  VARCHAR2,
                                      p_organization_id     NUMBER
		      )
IS

CURSOR c_mtl_mat_transactions (b_subinventory_code VARCHAR2, b_organization_id NUMBER) IS
       SELECT TRANSACTION_ID, INVENTORY_ITEM_ID
       FROM   MTL_MATERIAL_TRANSACTIONS
       WHERE  SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
       AND    ORGANIZATION_ID   = b_organization_id
       AND    TRANSACTION_ACTION_ID = 2 --Subinventory transfer
       AND    SOURCE_CODE = 'CSP';

Line: 137


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

Line: 150


      , v_message     => 'Update_MTL_Mat_Transaction could not find records for :' || p_subinventory_code || ','
                         || p_organization_id || ' for resource id ' || p_resource_id
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 161


        , v_message     => 'Update ACC record :' || p_subinventory_code || ' , ' || p_organization_id
                           || ' for resource id ' || p_resource_id
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 168


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

Line: 177


      /*** Record is not yet in ACC tables. Insert has to be done ***/
      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            => p_resource_id
       ,p_pk1_name               => g_pk1_name
       ,p_pk1_num_value          => r_mtl_mat_transactions.TRANSACTION_ID
      );

Line: 186


      /*** Record is already in ACC. Only an update is required for re-sending ***/
        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               => l_acc_id
                         );

Line: 195


      /*** Also Update Lot numebers and serial numbers into their respective acc tables ***/
      CSL_MTL_TRANS_LOT_NUM_ACC_PKG.Update_MTL_trans_lot_num(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id
                                      );

Line: 202


      CSL_MTL_UNIT_TRANS_ACC_PKG.Update_MTL_Unit_Trans(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id,
		        p_subinventory_code   => p_subinventory_code
                                      );

Line: 219


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

Line: 225


END Update_MTL_Mat_Transaction;

Line: 228


PROCEDURE Delete_MTL_Mat_Transaction(
                                      p_resource_id         NUMBER,
                                      p_subinventory_code  VARCHAR2,
                                      p_organization_id     NUMBER
		      )
IS

CURSOR c_mtl_mat_transactions (b_subinventory_code VARCHAR2, b_organization_id NUMBER) IS
       SELECT TRANSACTION_ID, INVENTORY_ITEM_ID
       FROM   MTL_MATERIAL_TRANSACTIONS
       WHERE  SUBINVENTORY_CODE = p_SUBINVENTORY_CODE
       AND    ORGANIZATION_ID   = b_organization_id
       AND    TRANSACTION_ACTION_ID = 2 --Subinventory transfer
       AND    SOURCE_CODE = 'CSP';

Line: 254


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

Line: 267


      , v_message     => 'Delete_MTL_Mat_Transaction could not find records for :' || p_subinventory_code ||
                         ' , ' || p_organization_id || ' for resource id ' || p_resource_id
      , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_MEDIUM);

Line: 279


        , v_message     => 'Inserting ACC record :' || p_subinventory_code || ' , ' || p_organization_id ||
                           ' for resource id ' || p_resource_id
        , v_level_id    => JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL);

Line: 286


      /*** Call common package to insert record into ACC table ***/
      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_mtl_mat_transactions.TRANSACTION_ID
      );

Line: 295


      /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
      CSL_MTL_TRANS_LOT_NUM_ACC_PKG.Delete_MTL_trans_lot_num(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id
                                      );

Line: 302


      CSL_MTL_UNIT_TRANS_ACC_PKG.Delete_MTL_Unit_Trans(
                                      p_resource_id         => p_resource_id,
                                      p_transaction_id      => r_mtl_mat_transactions.TRANSACTION_ID,
                                      p_inventory_item_id   => r_mtl_mat_transactions.inventory_item_id,
                                      p_organization_id     => p_organization_id,
		                      p_subinventory_code   => p_subinventory_code
                                      );

Line: 314


END Delete_MTL_Mat_Transaction;

Line: 317


/*Delete all records for non-existing user ( e.g user was deleted )*/
PROCEDURE DELETE_ALL_ACC_RECORDS( p_resource_id IN NUMBER
                                , x_return_status OUT NOCOPY VARCHAR2 )
IS
BEGIN
  /*** get debug level ***/
  g_debug_level := JTM_HOOK_UTIL_PKG.Get_Debug_Level;

Line: 328


    , 'Entering DELETE_ALL_ACC_RECORDS'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
    );

Line: 333


  DELETE JTM_MTL_MAT_TRANS_ACC
  WHERE  RESOURCE_ID = p_resource_id;

Line: 342


    , 'Leaving DELETE_ALL_ACC_RECORDS'
    , JTM_HOOK_UTIL_PKG.G_DEBUG_LEVEL_FULL
    );

Line: 354


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

Line: 358


END DELETE_ALL_ACC_RECORDS;