APPS.CSM_MTL_MATERIAL_TXN_ACC_PKG SQL Statements

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

Line: 12

PROCEDURE Insert_MTL_Mat_Transaction(
                                      p_user_id         NUMBER,
                                      p_transaction_id  NUMBER
                                    )
IS

BEGIN
  CSM_UTIL_PKG.LOG
  ( module => g_table_name
  , message     =>  'Entering Insert_MTL_Mat_Transaction Procedure to process TRAN ID : ' || p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 24

      /*** Call common package to insert record into ACC table ***/
      CSM_ACC_PKG.Insert_Acc
      ( p_publication_item_names => g_publication_item_name
       ,p_acc_table_name         => g_acc_table_name
       ,p_seq_name               => g_tasks_seq_name
       ,p_user_id                => p_user_id
       ,p_pk1_name               => g_pk1_name
       ,p_pk1_num_value          => p_transaction_id
      );

Line: 34

      /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
      CSM_MTL_TXN_LOT_NUM_ACC_PKG.Insert_MTL_trans_lot_num(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 39

      CSM_MTL_UNIT_TXN_ACC_PKG.Insert_MTL_Unit_Trans(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 45

  , message     => ' Leaving Insert_MTL_Mat_Transaction procedure for TRAN ID :' || p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 48

END Insert_MTL_Mat_Transaction;

Line: 51

PROCEDURE Update_MTL_Mat_Transaction(
                                      p_user_id         NUMBER,
                                      p_transaction_id  NUMBER
                                    )
IS
  l_acc_id    NUMBER;

Line: 61

  , message     => ' Entering Update_CSP_Sec_Inventory procedure to process TRAN ID :'|| p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 64

      /*** Call common package to insert record into ACC table ***/
      l_acc_id := CSM_ACC_PKG.Get_Acc_Id
                 ( P_ACC_TABLE_NAME => g_acc_table_name
                  ,P_PK1_NAME       => g_pk1_name
                  ,P_PK1_NUM_VALUE  => p_transaction_id
                  ,p_user_id        => p_user_id);

Line: 72

      /*** Record is not yet in ACC tables. Insert has to be done ***/
      CSM_ACC_PKG.Insert_Acc
      ( p_publication_item_names => g_publication_item_name
       ,p_acc_table_name         => g_acc_table_name
       ,p_seq_name               => g_tasks_seq_name
       ,p_user_id                => p_user_id
       ,p_pk1_name               => g_pk1_name
       ,p_pk1_num_value          => p_transaction_id
      );

Line: 82

      /*** Record is already in ACC. Only an update is required for re-sending ***/
        CSM_ACC_PKG.Update_Acc
                         ( p_publication_item_names => g_publication_item_name
                         ,p_acc_table_name          => g_acc_table_name
                         ,p_user_id                 => p_user_id
                         ,p_access_id               => l_acc_id
                         );

Line: 91

      /*** Also Update Lot numebers and serial numbers into their respective acc tables ***/
      CSM_MTL_TXN_LOT_NUM_ACC_PKG.Update_MTL_trans_lot_num(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 96

      CSM_MTL_UNIT_TXN_ACC_PKG.Update_MTL_Unit_Trans(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 102

  , message     => ' Leaving Update_MTL_Mat_Transaction Procedure for TRAN ID :' || p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 105

END Update_MTL_Mat_Transaction;

Line: 108

PROCEDURE Delete_MTL_Mat_Transaction(
                                      p_user_id         NUMBER,
                                      p_transaction_id  NUMBER
                                    )
IS
BEGIN

  CSM_UTIL_PKG.LOG
  ( module => g_table_name
  , message     =>' Entering Delete_MTL_Mat_Transaction Procedure for TRAN ID :'|| p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 120

      /*** Call common package to insert record into ACC table ***/
      CSM_ACC_PKG.Delete_Acc
      ( p_publication_item_names => g_publication_item_name
       ,p_acc_table_name         => g_acc_table_name
       ,p_user_id                => p_user_id
       ,p_pk1_name               => g_pk1_name
       ,p_pk1_num_value          => p_transaction_id
      );

Line: 129

      /*** Also insert Lot numebers and serial numbers into their respective acc tables ***/
      CSM_MTL_TXN_LOT_NUM_ACC_PKG.Delete_MTL_trans_lot_num(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 134

      CSM_MTL_UNIT_TXN_ACC_PKG.Delete_MTL_Unit_Trans(
                                      p_user_id         => p_user_id,
                                      p_transaction_id  => p_transaction_id
                                      );

Line: 140

  , message     =>' Leaving Delete_MTL_Mat_Transaction Procedure for TRAN ID :'|| p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 143

END Delete_MTL_Mat_Transaction;

Line: 146

/*Delete all records for non-existing user ( e.g user was deleted )*/
PROCEDURE DELETE_ALL_ACC_RECORDS( p_user_id IN NUMBER
                                , x_return_status OUT NOCOPY VARCHAR2 )
IS
BEGIN
  CSM_UTIL_PKG.LOG
    ( module => g_table_name
    , message     => 'Entering DELETE_ALL_ACC_RECORDS'
    , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 156

  DELETE CSM_MTL_MATERIAL_TXN_ACC
  WHERE  user_id = p_user_id;

Line: 163

    , message     => 'Leaving DELETE_ALL_ACC_RECORDS'
    , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 172

  , message     => 'Caught exception in DELETE_ALL_ACC_RECORDS hook:' || fnd_global.local_chr(10) || sqlerrm
  , log_level    => FND_LOG.LEVEL_ERROR);

Line: 175

END DELETE_ALL_ACC_RECORDS;

Line: 184

  SELECT NVL(last_run_date, TO_DATE('1','J'))
  FROM jtm_con_request_data
  WHERE package_name = 'CSM_MTL_MATERIAL_TXN_ACC_PKG'
  AND procedure_name = 'Refresh_Mat_Txn_Acc';

Line: 191

CURSOR l_mat_delete_csr
IS
    SELECT ACC.user_id
    ,      ACC.TRANSACTION_ID
    FROM  CSM_MTL_MATERIAL_TXN_ACC ACC,
          MTL_MATERIAL_TRANSACTIONS B
    WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
    AND   B.TRANSACTION_ACTION_ID = 2 --Subinventory transfer
    AND   (NOT EXISTS (SELECT 1
      FROM csm_inv_loc_ass_acc cilaa,
           csp_inv_loc_assignments cila
      WHERE cilaa.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
      AND cilaa.user_id = ACC.user_id
      AND cila.subinventory_code = B.subinventory_code
      AND cila.organization_id = B.organization_id)
    OR
      NOT EXISTS (SELECT 1
      FROM csm_inv_loc_ass_acc cilaa,
           csp_inv_loc_assignments cila
      WHERE cilaa.csp_inv_loc_assignment_id = cila.csp_inv_loc_assignment_id
      AND cilaa.user_id = ACC.user_id
      AND cila.subinventory_code = B.transfer_subinventory
      AND cila.organization_id = B.transfer_organization_id)
    OR
      B.transaction_date < (sysdate - CSM_PROFILE_PKG.get_task_history_days(acc.user_id))
    );

Line: 219

CURSOR l_mat_update_csr (b_last_run_date IN DATE) IS
    SELECT ACC.user_id
    ,      ACC.TRANSACTION_ID
    FROM  CSM_MTL_MATERIAL_TXN_ACC ACC,
          MTL_MATERIAL_TRANSACTIONS B
    WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
    AND B.last_update_date > b_last_run_date;

Line: 228

CURSOR l_mat_insert_csr
IS
    SELECT cilaa.user_id
    ,      B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B,
          csm_inv_loc_ass_acc cilaa,
          csp_inv_loc_assignments cila_from
    WHERE cilaa.csp_inv_loc_assignment_id = cila_from.csp_inv_loc_assignment_id
      AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
      AND cila_from.subinventory_code = B.subinventory_code
      AND cila_from.organization_id = B.organization_id
      AND NVL(cila_from.locator_id,0) = NVL(B.locator_id,0)
      AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
                      WHERE   B.TRANSACTION_ID = ACC.TRANSACTION_ID
                      AND     ACC.USER_ID      = cilaa.USER_ID)
      AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
    UNION ALL
    SELECT cilaa.user_id
    ,      B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B,
          csm_inv_loc_ass_acc cilaa,
          csp_inv_loc_assignments cila_to
    WHERE cilaa.csp_inv_loc_assignment_id = cila_to.csp_inv_loc_assignment_id
      AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
      AND cila_to.subinventory_code = B.transfer_subinventory
      AND cila_to.organization_id = B.transfer_organization_id
      AND NVL(cila_to.locator_id,0) = NVL(B.transfer_locator_id,0)
      AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
      AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
                      WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID
                      AND     ACC.USER_ID    = cilaa.USER_ID);

Line: 285

    l_user_id_lst.DELETE;

Line: 288

    l_tran_id_lst.DELETE;

Line: 291

     CSM_UTIL_PKG.LOG('Processing Material Trasaction Delete',
  'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);

Line: 295

  OPEN l_mat_delete_csr;

Line: 297

  FETCH l_mat_delete_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 1000;

Line: 301

        Delete_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));

Line: 305

      l_user_id_lst.DELETE;

Line: 308

      l_tran_id_lst.DELETE;

Line: 312

  CLOSE l_mat_delete_csr;

Line: 316

      l_user_id_lst.DELETE;

Line: 319

      l_tran_id_lst.DELETE;

Line: 322

  CSM_UTIL_PKG.LOG('Processing Material Trasaction Update',
  'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);

Line: 324

  OPEN l_mat_update_csr (l_last_run_date);

Line: 326

  FETCH l_mat_update_csr BULK COLLECT INTO l_user_id_lst , l_tran_id_lst LIMIT 1000;

Line: 330

        Update_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));

Line: 334

      l_user_id_lst.DELETE;

Line: 337

      l_tran_id_lst.DELETE;

Line: 341

  CLOSE l_mat_update_csr;

Line: 346

      l_user_id_lst.DELETE;

Line: 349

      l_tran_id_lst.DELETE;

Line: 352

  CSM_UTIL_PKG.LOG('Processing Material Trasaction Insert',
  'CSM_MTL_MATERIAL_TXN_ACC_PKG.Refresh_Mat_Txn_Acc',FND_LOG.LEVEL_STATEMENT);

Line: 355

  OPEN l_mat_insert_csr;

Line: 357

  FETCH l_mat_insert_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 1000;

Line: 361

        Insert_MTL_Mat_Transaction (l_user_id_lst(i), l_tran_id_lst(i));

Line: 365

      l_user_id_lst.DELETE;

Line: 368

      l_tran_id_lst.DELETE;

Line: 372

  CLOSE l_mat_insert_csr;

Line: 376

 UPDATE jtm_con_request_data
 SET last_run_date = l_current_date
 WHERE package_name = 'CSM_MTL_MATERIAL_TXN_ACC_PKG'
 AND procedure_name = 'REFRESH_MAT_TXN_ACC';

Line: 403

CURSOR l_mat_insert_csr (b_user_id IN NUMBER) IS
    SELECT B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B,
          csm_inv_loc_ass_acc cilaa,
          csp_inv_loc_assignments cila_from
    WHERE cilaa.csp_inv_loc_assignment_id = cila_from.csp_inv_loc_assignment_id
      AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
      AND cila_from.subinventory_code = B.subinventory_code
      AND cila_from.organization_id = B.organization_id
      AND NVL(cila_from.locator_id,0) = NVL(B.locator_id,0)
      AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
                    WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID)
      AND cilaa.user_id = b_user_id
      AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id))
    UNION ALL
    SELECT B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B,
          csm_inv_loc_ass_acc cilaa,
          csp_inv_loc_assignments cila_to
    WHERE cilaa.csp_inv_loc_assignment_id = cila_to.csp_inv_loc_assignment_id
      AND TRANSACTION_ACTION_ID = 2 --Subinventory transfer
      AND cila_to.subinventory_code = B.transfer_subinventory
      AND cila_to.organization_id = B.transfer_organization_id
      AND NVL(cila_to.locator_id,0) = NVL(B.transfer_locator_id,0)
      AND NOT EXISTS (SELECT 1 FROM CSM_MTL_MATERIAL_TXN_ACC ACC
                    WHERE B.TRANSACTION_ID = ACC.TRANSACTION_ID)
      AND cilaa.user_id = b_user_id
      AND B.transaction_date > (sysdate -CSM_PROFILE_PKG.get_task_history_days(cilaa.user_id));

Line: 445

  OPEN l_mat_insert_csr(p_user_id);

Line: 448

      l_tran_id_lst.DELETE;

Line: 450

  FETCH l_mat_insert_csr BULK COLLECT INTO l_tran_id_lst LIMIT 1000;

Line: 454

        Insert_MTL_Mat_Transaction (p_user_id, l_tran_id_lst(i));

Line: 458

  CLOSE l_mat_insert_csr;