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 (c_history_profile NUMBER)
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   (
          B.transaction_date < (sysdate - c_history_profile)
          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.subinventory_code
          AND cila.organization_id = B.organization_id
         UNION ALL
          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)
    );

Line: 221

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

CURSOR l_mat_insert_csr (c_history_profile NUMBER)
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 - c_history_profile)
    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 - c_history_profile)
      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: 289

    l_user_id_lst.DELETE;

Line: 292

    l_tran_id_lst.DELETE;

Line: 295

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

Line: 299

  OPEN l_mat_delete_csr(l_profile_value);

Line: 301

  FETCH l_mat_delete_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 500;

Line: 305

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

Line: 309

      l_user_id_lst.DELETE;

Line: 312

      l_tran_id_lst.DELETE;

Line: 316

  CLOSE l_mat_delete_csr;

Line: 320

      l_user_id_lst.DELETE;

Line: 323

      l_tran_id_lst.DELETE;

Line: 326

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

Line: 328

  OPEN l_mat_update_csr (l_last_run_date);

Line: 330

  FETCH l_mat_update_csr BULK COLLECT INTO l_user_id_lst , l_tran_id_lst LIMIT 500;

Line: 334

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

Line: 338

      l_user_id_lst.DELETE;

Line: 341

      l_tran_id_lst.DELETE;

Line: 345

  CLOSE l_mat_update_csr;

Line: 350

      l_user_id_lst.DELETE;

Line: 353

      l_tran_id_lst.DELETE;

Line: 356

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

Line: 359

  OPEN l_mat_insert_csr(l_profile_value);

Line: 361

  FETCH l_mat_insert_csr BULK COLLECT INTO l_user_id_lst, l_tran_id_lst LIMIT 500;

Line: 365

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

Line: 369

      l_user_id_lst.DELETE;

Line: 372

      l_tran_id_lst.DELETE;

Line: 376

  CLOSE l_mat_insert_csr;

Line: 380

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

CURSOR l_mat_insert_csr (b_user_id IN NUMBER, b_profile_value NUMBER) IS
    SELECT B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B
    WHERE B.transaction_date > (sysdate - b_profile_value)
    AND   B.TRANSACTION_ACTION_ID = 2 --Subinventory transfer
    AND   EXISTS( SELECT 1
          FROM    csm_inv_loc_ass_acc cilaa1,
                  csp_inv_loc_assignments cila_from
          WHERE cilaa1.csp_inv_loc_assignment_id = cila_from.csp_inv_loc_assignment_id
          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 cilaa1.user_id              = b_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      = b_user_id)
    UNION ALL
    SELECT B.TRANSACTION_ID
    FROM  MTL_MATERIAL_TRANSACTIONS B
    WHERE B.transaction_date > (sysdate - b_profile_value)
    AND   B.TRANSACTION_ACTION_ID = 2 --Subinventory transfer
    AND   EXISTS  (SELECT 1
          FROM    csm_inv_loc_ass_acc cilaa2,
                  csp_inv_loc_assignments cila_to
          WHERE cilaa2.csp_inv_loc_assignment_id = cila_to.csp_inv_loc_assignment_id
          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 cilaa2.user_id            = b_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      = b_user_id);

Line: 456

  OPEN l_mat_insert_csr(p_user_id, l_profile_value);

Line: 459

      l_tran_id_lst.DELETE;

Line: 461

  FETCH l_mat_insert_csr BULK COLLECT INTO l_tran_id_lst LIMIT 1000;

Line: 465

        Insert_MTL_Mat_Transaction (p_user_id, l_tran_id_lst(i));

Line: 469

  CLOSE l_mat_insert_csr;