Amazon Recomendations

Thursday, July 4, 2013

Reservations Processing in R12.1.3 from OAF by using INV_RESERVATION_PUB API

hi All,

Lately I been tasked by our client to develop OAF page to manage Inventory Reservations from Delivery Details screen. Delivery Detail Id is passed as parameter to all regions displayed one OAF page.

I made a simple Form Personalization to have the custom page opened from Action Menu:













The only thing worth mentioning regarding the personalization is the syntax for Form Function submit:

















Following OAF form displays various unique information to support reservations processing that is relevant only for this particular client:


Back to our topic: by manipulating values in Qty column on any line at Lots Available for Reservation table user would be able to Create, Update or Delete reservations of the corresponding Lot for Delivery Detail Line passed to this page as a parameter.

I decided to make this post for two reasons:
First: Most of the similar post tend to mention only Reservation creation from Inventory source and rarely from Order Management.
Second: It took me a while to have a CORRECT minimum number of  the parameters in place to them running successfully. (none of the posts I encountered had it right!) 

Bellow are the short scripts for every one of the main operations from INV_RESERVATION_PUB API.

* make sure to provide mtl_sales_orders.sales_order_id as demand_source_header_id to "glue" the reservation to corresponding Sales Order:

select mso.sales_order_id 
into l_order_header_id       
from mtl_sales_orders mso
     ,oe_order_headers_all ooha
     ,oe_transaction_types_tl ttl
where ooha.order_number      = <corresponding sales order number>
and   mso.segment1           = ooha.order_number
and   mso.segment2           = ttl.name
and   ttl.language           = 'US'
and   ooha.order_type_id     = ttl.transaction_type_id;     

1. CREATE_RESERVATION

DECLARE
   p_rsv                     inv_reservation_global.mtl_reservation_rec_type;
   p_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2(240);
   x_rsv_id                  NUMBER;
   x_dummy_sn                inv_reservation_global.serial_number_tbl_type;
   x_status                  VARCHAR2(1);
   x_qty                     NUMBER;
BEGIN
   fnd_global.apps_initialize(2116,50641,660);
   p_rsv.requirement_date            := Sysdate;
   p_rsv.organization_id             := 101; --mtl_parameters.organization id
   p_rsv.inventory_item_id           := 258;--mtl_system_items.Inventory_item_id;
   p_rsv.demand_source_type_id       := inv_reservation_global.g_source_type_oe; -- which is 2
   p_rsv.demand_source_name          := NULL;
   p_rsv.demand_source_header_id     := l_order_header_id;--1334166 ; --mtl_sales_orders.sales_order_id
   p_rsv.demand_source_line_id       := 48067;--4912468 ; -- oe_order_lines.line_id
   p_rsv.primary_uom_code            := 'SLB';
   p_rsv.primary_uom_id              := NULL;
   p_rsv.reservation_uom_code        := 'SLB';
   p_rsv.reservation_uom_id          := NULL;
   p_rsv.reservation_quantity        := 1;
   p_rsv.primary_reservation_quantity := 1;
   p_rsv.lot_number                  := '1187341A';--p_lot_number;
   p_rsv.locator_id                  :=   null;
   p_rsv.supply_source_type_id       := inv_reservation_global.g_source_type_inv;
   p_rsv.ship_ready_flag             := NULL;
   p_rsv.primary_uom_id              := NULL;
   p_rsv.reservation_uom_id          := NULL;
   p_rsv.subinventory_code           :=  '102';
   p_rsv.subinventory_id             :=  NULL;
   p_rsv.attribute15                 :=   NULL ;
   p_rsv.attribute14                 :=   NULL ;       
   p_rsv.attribute13                 :=   NULL ;
   p_rsv.attribute12                 :=   NULL ;
   p_rsv.attribute11                 :=   NULL ; 
   p_rsv.attribute10                 :=   NULL ;
   p_rsv.attribute9                  :=   NULL ;     
   p_rsv.attribute8                  :=   NULL ; 
   p_rsv.attribute7                  :=   NULL ;
   p_rsv.attribute6                  :=   NULL ; 
   p_rsv.attribute5                  :=   NULL ;      
   p_rsv.attribute4                  :=   NULL ;  
   p_rsv.attribute3                  :=   NULL ;
   p_rsv.attribute2                  :=   NULL ;  
   p_rsv.attribute1                  :=   NULL ;  
   p_rsv.attribute_category          :=   NULL ;
   p_rsv.lpn_id                      :=   NULL ;
   p_rsv.pick_slip_number            :=   NULL ;
   p_rsv.lot_number_id               :=   NULL ;
   p_rsv.revision                    :=   NULL ;
   p_rsv.external_source_line_id     :=   NULL ;     
   p_rsv.external_source_code        :=   NULL ;     
   p_rsv.autodetail_group_id         :=   NULL ;    
   p_rsv.reservation_uom_id          :=   NULL ;    
   p_rsv.primary_uom_id              :=   NULL ;   
   p_rsv.demand_source_delivery      :=   NULL ;
   p_rsv.supply_source_line_detail   :=   NULL;
   p_rsv.supply_source_name          :=   NULL;
   p_rsv.supply_source_header_id     :=   NULL; 
   p_rsv.supply_source_line_id       :=   NULL;   
     
   inv_reservation_pub.create_reservation
   (
        p_api_version_number       =>       1.0
      , x_return_status            =>       x_status
      , x_msg_count                =>       x_msg_count
      , x_msg_data                 =>       x_msg_data
      , p_rsv_rec                  =>       p_rsv
      , p_serial_number            =>       p_dummy_sn
      , x_serial_number            =>       x_dummy_sn
      , x_quantity_reserved        =>       x_qty
      , x_reservation_id           =>       x_rsv_id
      
   );
   dbms_output.put_line('Return status    = '||x_status);
   dbms_output.put_line('msg count        = '||to_char(x_msg_count));
   dbms_output.put_line('msg data         = '||x_msg_data);
   dbms_output.put_line('Quantity reserved = '||to_char(x_qty));
   dbms_output.put_line('Reservation id   = '||to_char(x_rsv_id));
   IF x_msg_count >=1 THEN
     FOR I IN 1..x_msg_count
     LOOP
       dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
       fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
     END LOOP;
     
   END IF;
COMMIT;
END;
/

2. UPDATE_RESERVATION

DECLARE
     l_rsv_old   inv_reservation_global.mtl_reservation_rec_type;
     l_rsv_new   inv_reservation_global.mtl_reservation_rec_type;
      l_msg_count NUMBER;
      l_msg_data  VARCHAR2(240);
      l_rsv_id    NUMBER;
     l_dummy_sn  inv_reservation_global.serial_number_tbl_type;
     l_status    VARCHAR2(1);
      l_quantity_reserved NUMBER;
   BEGIN
      fnd_global.apps_initialize(2116,50641,660);
 -- could be "located" by order_header_id, order_line_id, item and warehouse - in that case several reservations could be updated
      l_rsv_old.reservation_id               := 69956;

      -- specify the new values
      l_rsv_new.reservation_id               := 69956;
      l_rsv_new.primary_reservation_quantity := 10;
      l_rsv_new.requirement_date             := Sysdate+2;
      
      inv_reservation_pub.update_reservation(      
          p_api_version_number        => 1.0
         , p_init_msg_lst              => fnd_api.g_true
         , x_return_status             => l_status
         , x_msg_count                 => l_msg_count
         , x_msg_data                  => l_msg_data
         , p_original_rsv_rec          => l_rsv_old
         , p_to_rsv_rec                => l_rsv_new
         , p_original_serial_number    => l_dummy_sn -- no serial contorl
         , p_to_serial_number         => l_dummy_sn -- no serial control
         , p_validation_flag           => fnd_api.g_true
         , p_check_availability        => fnd_api.g_false
         , p_over_reservation_flag     => 0
         );
      IF l_status = fnd_api.g_ret_sts_success THEN
         dbms_output.put_line('reservation updated');
     ELSE
        IF l_msg_count >=1 THEN
           FOR I IN 1..l_msg_count    LOOP
             dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
             --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
           END LOOP; 
        END IF;
     END IF;
COMMIT;
END;
/
3. DELETE_RESERVATION (decided not to use relieve_reservation since secondary quantity is not used )

DECLARE
      l_rsv       inv_reservation_global.mtl_reservation_rec_type;
      l_msg_count NUMBER;
      l_msg_data  VARCHAR2(240);
      l_rsv_id    NUMBER;
      l_dummy_sn  inv_reservation_global.serial_number_tbl_type;
      l_status    VARCHAR2(1);
BEGIN
      fnd_global.apps_initialize(2116,50641,660);
      l_rsv.reservation_id               := 95377;
      /*l_rsv.organization_id            := 101;
      l_rsv.inventory_item_id            := 258;
      l_rsv.demand_source_type_id        := inv_reservation_global.g_source_type_oe; -- order entry
      l_rsv.demand_source_header_id      := 76069; --oe_order_headers.header_id
      l_rsv.demand_source_line_id        := 48067; --oe_order_lines.line_id
      l_rsv.supply_source_type_id        := inv_reservation_global.g_source_type_inv;
      l_rsv.subinventory_code            := '102';*/

     inv_reservation_pub.delete_reservation
       (
          p_api_version_number        => 1.0
         , p_init_msg_lst              => fnd_api.g_true
         , x_return_status             => l_status
         , x_msg_count                 => l_msg_count
         , x_msg_data                  => l_msg_data
         , p_rsv_rec                   => l_rsv
         , p_serial_number => l_dummy_sn
         );

     IF l_status = fnd_api.g_ret_sts_success THEN
         dbms_output.put_line('reservation deleted');
     ELSE
        IF l_msg_count >=1 THEN
           FOR I IN 1..l_msg_count    LOOP
             dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
             --fnd_file.put_line(fnd_file.log,I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ),1, 255));
           END LOOP; 
        END IF;
     END IF;
     COMMIT;
END;
/

enjoy,
Felix

1 comment:

  1. Appreciate your article buddy. It has a very good info on reservations

    ReplyDelete