APPS.INV_3PL_LOC_PVT SQL Statements

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

Line: 7

PROCEDURE update_locator_capacity
  ( x_return_status             OUT NOCOPY VARCHAR2, -- return status (success/error/unexpected_error)
    x_msg_count                 OUT NOCOPY NUMBER,   -- number of messages in the message queue
    x_msg_data                  OUT NOCOPY VARCHAR2, -- message text when x_msg_count>0
    p_inventory_location_id     IN         NUMBER,   -- identifier of locator
    p_organization_id           IN         NUMBER,   -- org of locator whose capacity is to be determined
    p_client_code               IN         VARCHAR2,   -- identifier of item
    p_transaction_action_id     IN            NUMBER,   -- transaction action id for pack,unpack,issue,receive,transfer
    p_quantity                  IN         NUMBER,
    p_transaction_date          IN         DATE
  )
IS
    l_return_status             VARCHAR2(1);

Line: 39

            inv_trx_util_pub.TRACE('In update locator capcity ', 'update_current_capacity', 4);

Line: 40

            inv_trx_util_pub.TRACE('Locator ID' || p_inventory_location_id , 'update_current_capacity',4);

Line: 41

            inv_trx_util_pub.TRACE('Client Code ' || p_client_code, 'update_current_capacity', 4);

Line: 45

          Select *
          INTO l_old_Record
          FROM mtl_3pl_locator_occupancy
          WHERE locator_id = p_inventory_location_id
          and organization_id = p_organization_id
          AND client_code = p_client_code;

Line: 53

          inv_trx_util_pub.TRACE('Locator Record Exists in Occupancy table', 'update_current_capacity', 4);

Line: 59

    inv_trx_util_pub.TRACE('Locator Record Does not exist , creating a new record in Occupancy table', 'update_current_capacity', 4);

Line: 62

        inv_trx_util_pub.TRACE('p_transaction_date => ' ||p_transaction_date, 'update_current_capacity', 4);

Line: 63

        inv_trx_util_pub.TRACE('p_quantity => ' || p_quantity, 'update_current_capacity', 4);

Line: 64

        inv_trx_util_pub.TRACE('p_inventory_location_id => ' ||p_inventory_location_id, 'update_current_capacity', 4);

Line: 65

        inv_trx_util_pub.TRACE('p_transaction_date => ' ||p_transaction_date, 'update_current_capacity', 4);

Line: 66

        inv_trx_util_pub.TRACE('p_transaction_action_id => ' ||p_transaction_action_id, 'update_current_capacity', 4);

Line: 67

        inv_trx_util_pub.TRACE('p_organization_id => ' ||p_organization_id, 'update_current_capacity', 4);

Line: 68

        inv_trx_util_pub.TRACE('l_client_code => ' ||p_client_code, 'update_current_capacity', 4);

Line: 70

        l_success := insert_3pl_loc_occupancy(
                                  p_transaction_date ,
                                  p_quantity ,
                                  p_inventory_location_id ,
                                  p_transaction_date ,
                                  p_transaction_action_id ,
                                  p_quantity ,
                                  p_organization_id ,
                                  p_client_code,
                                  1
                                  );

Line: 91

    inv_trx_util_pub.TRACE('Updating The current record ', 'update_current_capacity', 4);

Line: 92

    inv_trx_util_pub.TRACE('Transaction action ID' || p_transaction_action_id, 'update_current_capacity', 4);

Line: 98

                      inv_trx_util_pub.TRACE('Just before adding days .... :', 'update_current_capacity', 4);

Line: 99

                      inv_trx_util_pub.TRACE('Transaction_date :' || p_transaction_date , 'update_current_capacity', 4);

Line: 100

                      inv_trx_util_pub.TRACE('l_old_Record.Last_Receipt_Date :' || l_old_Record.Last_Receipt_Date , 'update_current_capacity', 4);

Line: 101

                      inv_trx_util_pub.TRACE('l_number_of_days :' || l_number_of_days , 'update_current_capacity', 4);

Line: 112

                inv_trx_util_pub.TRACE('Inside 2nd if' || l_number_of_days , 'update_current_capacity', 4);

Line: 121

          inv_trx_util_pub.TRACE('New number of days' || l_number_of_days , 'update_current_capacity', 4);

Line: 135

          inv_trx_util_pub.TRACE(' Calling update_3pl_loc_occupancy ', 'update_current_capacity', 4);

Line: 138

          l_success := update_3pl_loc_occupancy(
                                      l_Last_Receipt_Date ,
                                      l_current_onhand ,
                                      l_locator_id ,
                                      l_transaction_date ,
                                      l_transaction_action ,
                                      l_transaction_quantity ,
                                      l_organization_id,
                                      l_client_code ,
                                      l_number_of_days
                                      );

Line: 153

                inv_trx_util_pub.TRACE('For issue transactions', 'update_current_capacity', 4);

Line: 154

                inv_trx_util_pub.TRACE('Just before adding days .... :', 'update_current_capacity', 4);

Line: 155

                inv_trx_util_pub.TRACE('p_transaction_date :' || p_transaction_date , 'update_current_capacity', 4);

Line: 156

                inv_trx_util_pub.TRACE('l_old_Record.Last_Receipt_Date :' || l_old_Record.Last_Receipt_Date , 'update_current_capacity', 4);

Line: 157

                inv_trx_util_pub.TRACE('l_number_of_days :' || l_number_of_days , 'update_current_capacity', 4);

Line: 158

                inv_trx_util_pub.TRACE('p_quantity :' || p_quantity , 'update_current_capacity', 4);

Line: 182

                  inv_trx_util_pub.TRACE('Before calling update_3pl_loc_occupancy ', 'update_current_capacity', 4);

Line: 185

                  l_success := update_3pl_loc_occupancy(
                                              l_Last_Receipt_Date ,
                                              l_current_onhand ,
                                              l_locator_id ,
                                              l_transaction_date ,
                                              l_transaction_action ,
                                              l_transaction_quantity ,
                                              l_organization_id,
                                              l_client_code,
                                              l_number_of_days
                                              );

Line: 208

            inv_trx_util_pub.TRACE(' Exception in update_locator_capacity =>  '||sqlerrm, 'update_current_capacity', 4);

Line: 211

  END update_locator_capacity;

Line: 213

Function update_3pl_loc_occupancy (
                                l_Last_Receipt_Date date,
                                l_current_onhand NUMBER ,
                                l_locator_id NUMBER ,
                                l_transaction_date DATE ,
                                l_transaction_action NUMBER ,
                                l_transaction_quantity NUMBER  ,
                                l_organization_id NUMBER,
                                l_client_code VARCHAR2 ,
                                l_number_of_days  number
                                ) RETURN VARCHAR2
IS

cursor c is select client_code , locator_id , organization_id
              FROM mtl_3pl_locator_occupancy
              WHERE client_code = l_client_code
              AND locator_id = l_locator_id
              and organization_id = l_organization_id
              FOR UPDATE nowait;

Line: 236

  inv_trx_util_pub.TRACE('In update_3pl_loc_occupancy ', 'update_current_capacity', 4);

Line: 242

     inv_trx_util_pub.TRACE('recinfo.locator_id ' || recinfo.locator_id , 'update_current_capacity', 4);

Line: 243

     inv_trx_util_pub.TRACE('recinfo.client_code ' || recinfo.client_code , 'update_current_capacity', 4);

Line: 246

      Update mtl_3pl_locator_occupancy
      set Last_Receipt_Date  =  l_Last_Receipt_Date,
          current_onhand     = l_current_onhand ,
          transaction_date   = l_transaction_date ,
          transaction_action_id  =  l_transaction_action ,
          transaction_quantity   = l_transaction_quantity ,
          number_of_days     = l_number_of_days
      where locator_id = recinfo.locator_id
      and client_code = recinfo.client_code
      and organization_id = recinfo.organization_id;

Line: 263

            inv_trx_util_pub.TRACE(' Exception in update_3pl_loc_occupancy =>  '||sqlerrm, 'update_current_capacity', 4);

Line: 267

    END update_3pl_loc_occupancy;

Line: 269

Function insert_3pl_loc_occupancy (
                                l_Last_Receipt_Date date,
                                l_current_onhand NUMBER ,
                                l_locator_id NUMBER ,
                                l_transaction_date DATE ,
                                l_transaction_action NUMBER ,
                                l_transaction_quantity NUMBER  ,
                                l_organization_id NUMBER,
                                l_client_code VARCHAR2 ,
                                l_number_of_days  number
                                ) RETURN VARCHAR2
IS
 BEGIN

   IF (g_debug = 1) THEN
  inv_trx_util_pub.TRACE('IN insert_3pl_loc_occupancy ','update_current_capacity', 4);

Line: 285

  inv_trx_util_pub.TRACE('l_locator_id ' || l_locator_id,'update_current_capacity', 4);

Line: 286

  inv_trx_util_pub.TRACE('IN l_client_code ' || l_client_code ,'update_current_capacity', 4);

Line: 289

      INSERT INTO  mtl_3pl_locator_occupancy
            ( Last_Receipt_Date,
              current_onhand    ,
              transaction_date    ,
              transaction_action_id   ,
              transaction_quantity               ,
              last_invoiced_date   ,
              number_of_days         ,
              locator_id,
              organization_id ,
              client_code
               )
          VALUES
          (  l_Last_Receipt_Date,
             l_current_onhand ,
             l_transaction_date ,
             l_transaction_action ,
             l_transaction_quantity ,
             NULL  ,
             l_number_of_days   ,
             l_locator_id,
             l_organization_id ,
             l_client_code
          );

Line: 319

                inv_trx_util_pub.TRACE(' Exception in insert_3pl_loc_occupancy =>  '||sqlerrm, 'update_current_capacity', 4);

Line: 322

    END insert_3pl_loc_occupancy;