APPS.CSM_MTL_TXN_LOT_NUM_ACC_PKG SQL Statements

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

Line: 12

g_old_user_id           NUMBER; -- variable containing old user_id; populated in Pre_Update hook

Line: 19

PROCEDURE Insert_MTL_trans_lot_num
    (   p_user_id         NUMBER,
        p_transaction_id      NUMBER  )
IS

CURSOR c_transaction_lot_number( b_transaction_id NUMBER )
IS
   SELECT TRANSACTION_ID, LOT_NUMBER
   FROM   MTL_TRANSACTION_LOT_NUMBERS
   WHERE  TRANSACTION_ID = b_transaction_id;

Line: 33

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

Line: 37

     l_transaction_id_lst.DELETE;

Line: 41

     l_lot_number_lst.DELETE;

Line: 52

      /*** 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          => l_transaction_id_lst(i)
       ,p_pk2_name               => g_pk2_name
       ,p_pk2_char_value         => l_lot_number_lst(i)
      );

Line: 67

     l_transaction_id_lst.DELETE;

Line: 70

     l_lot_number_lst.DELETE;

Line: 74

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

Line: 76

END Insert_MTL_trans_lot_num;

Line: 79

PROCEDURE Update_MTL_trans_lot_num( p_user_id         NUMBER,
                                    p_transaction_id      NUMBER)
IS
CURSOR c_transaction_lot_number(b_transaction_id NUMBER)
IS
 SELECT TRANSACTION_ID, LOT_NUMBER
 FROM MTL_TRANSACTION_LOT_NUMBERS
 WHERE TRANSACTION_ID = b_transaction_id;

Line: 94

  , message     => ' Entering Update_MTL_trans_lot_num Procedure  forTRAN ID :' || p_transaction_id
  , log_level    => FND_LOG.LEVEL_STATEMENT);

Line: 98

   l_transaction_id_lst.DELETE;

Line: 101

   l_lot_number_lst.DELETE;

Line: 110

      /*** Call common package to insert record into ACC table ***/
    FOR i in 1..l_transaction_id_lst.COUNT LOOP
      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  => l_transaction_id_lst(i)
                  ,p_pk2_name       => g_pk2_name
                  ,p_pk2_char_value => l_lot_number_lst(i)
                  ,p_user_id    => p_user_id);

Line: 121

      /*** 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_pk1_name                => g_pk1_name
                         ,p_pk1_num_value           => l_transaction_id_lst(i)
                         ,p_pk2_name                => g_pk2_name
                         ,p_pk2_char_value          => l_lot_number_lst(i)
                         ,p_user_id             => p_user_id
                         );

Line: 133

      /*** 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: 144

     l_transaction_id_lst.DELETE;

Line: 147

     l_lot_number_lst.DELETE;

Line: 152

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

Line: 155

END Update_MTL_trans_lot_num;

Line: 158

PROCEDURE Delete_MTL_trans_lot_num(   p_user_id         NUMBER,
                                      p_transaction_id  NUMBER )
IS
CURSOR c_transaction_lot_number( b_transaction_id NUMBER, b_user_id NUMBER)
IS
   SELECT TRANSACTION_ID, LOT_NUMBER
   FROM   CSM_MTL_TXN_LOT_NUM_ACC
   WHERE  TRANSACTION_ID = b_transaction_id
   AND    USER_ID        = b_user_id;

Line: 174

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

Line: 178

     l_transaction_id_lst.DELETE;

Line: 181

     l_lot_number_lst.DELETE;

Line: 192

      /*** 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          => l_transaction_id_lst(i)
        ,p_pk2_name               => g_pk2_name
        ,p_pk2_char_value         => l_lot_number_lst(i)
        );

Line: 206

     l_transaction_id_lst.DELETE;

Line: 209

     l_lot_number_lst.DELETE;

Line: 213

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

Line: 216

END Delete_MTL_trans_lot_num;

Line: 218

/*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: 228

  DELETE CSM_MTL_TXN_LOT_NUM_ACC
  WHERE  USER_ID = p_user_id;

Line: 235

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

Line: 244

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

Line: 247

END DELETE_ALL_ACC_RECORDS;