Tuesday, November 26, 2013

How to update a Sales Order Line using OE_ORDER_PUB

-- OE_ORDER_PUB.PROCESS_ORDER will be used to update sales order line details in this example the Bill-to Site of the sales order line is updated.


 DECLARE
   l_header_rec                 oe_order_pub.header_rec_type;
   l_line_tbl                   oe_order_pub.line_tbl_type;
   l_action_request_tbl         oe_order_pub.request_tbl_type;
   l_header_adj_tbl             oe_order_pub.header_adj_tbl_type;
   l_line_adj_tbl               oe_order_pub.line_adj_tbl_type;
   l_header_scr_tbl             oe_order_pub.header_scredit_tbl_type;
   l_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;
   l_request_rec                oe_order_pub.request_rec_type;
   l_return_status              VARCHAR2(1000);
   l_msg_count                  NUMBER;
   l_msg_data                   VARCHAR2(1000);
   p_api_version_number         NUMBER := 1.0;
   p_init_msg_list              VARCHAR2(10) := fnd_api.g_false;
   p_return_values              VARCHAR2(10) := fnd_api.g_false;
   p_action_commit              VARCHAR2(10) := fnd_api.g_false;
   x_return_status              VARCHAR2(1);
   x_msg_count                  NUMBER;
   x_msg_data                   VARCHAR2(100);
   p_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   x_header_rec                 oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   p_old_header_rec             oe_order_pub.header_rec_type := oe_order_pub.g_miss_header_rec;
   p_header_val_rec             oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
   p_old_header_val_rec         oe_order_pub.header_val_rec_type := oe_order_pub.g_miss_header_val_rec;
   p_header_adj_tbl             oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
   p_old_header_adj_tbl         oe_order_pub.header_adj_tbl_type := oe_order_pub.g_miss_header_adj_tbl;
   p_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
   p_old_header_adj_val_tbl     oe_order_pub.header_adj_val_tbl_type := oe_order_pub.g_miss_header_adj_val_tbl;
   p_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
   p_old_header_price_att_tbl   oe_order_pub.header_price_att_tbl_type := oe_order_pub.g_miss_header_price_att_tbl;
   p_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
   p_old_header_adj_att_tbl     oe_order_pub.header_adj_att_tbl_type := oe_order_pub.g_miss_header_adj_att_tbl;
   p_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
   p_old_header_adj_assoc_tbl   oe_order_pub.header_adj_assoc_tbl_type := oe_order_pub.g_miss_header_adj_assoc_tbl;
   p_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
   p_old_header_scredit_tbl     oe_order_pub.header_scredit_tbl_type := oe_order_pub.g_miss_header_scredit_tbl;
   p_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
   p_old_header_scredit_val_tbl oe_order_pub.header_scredit_val_tbl_type := oe_order_pub.g_miss_header_scredit_val_tbl;
   x_line_tbl                   oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
   p_old_line_tbl               oe_order_pub.line_tbl_type := oe_order_pub.g_miss_line_tbl;
   p_line_val_tbl               oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
   p_old_line_val_tbl           oe_order_pub.line_val_tbl_type := oe_order_pub.g_miss_line_val_tbl;
   p_line_adj_tbl               oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
   p_old_line_adj_tbl           oe_order_pub.line_adj_tbl_type := oe_order_pub.g_miss_line_adj_tbl;
   p_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
   p_old_line_adj_val_tbl       oe_order_pub.line_adj_val_tbl_type := oe_order_pub.g_miss_line_adj_val_tbl;
   p_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
   p_old_line_price_att_tbl     oe_order_pub.line_price_att_tbl_type := oe_order_pub.g_miss_line_price_att_tbl;
   p_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
   p_old_line_adj_att_tbl       oe_order_pub.line_adj_att_tbl_type := oe_order_pub.g_miss_line_adj_att_tbl;
   p_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
   p_old_line_adj_assoc_tbl     oe_order_pub.line_adj_assoc_tbl_type := oe_order_pub.g_miss_line_adj_assoc_tbl;
   p_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
   p_old_line_scredit_tbl       oe_order_pub.line_scredit_tbl_type := oe_order_pub.g_miss_line_scredit_tbl;
   p_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
   p_old_line_scredit_val_tbl   oe_order_pub.line_scredit_val_tbl_type := oe_order_pub.g_miss_line_scredit_val_tbl;
   p_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
   p_old_lot_serial_tbl         oe_order_pub.lot_serial_tbl_type := oe_order_pub.g_miss_lot_serial_tbl;
   p_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
   p_old_lot_serial_val_tbl     oe_order_pub.lot_serial_val_tbl_type := oe_order_pub.g_miss_lot_serial_val_tbl;
   p_action_request_tbl         oe_order_pub.request_tbl_type := oe_order_pub.g_miss_request_tbl;
   x_header_val_rec             oe_order_pub.header_val_rec_type;
   x_header_adj_tbl             oe_order_pub.header_adj_tbl_type;
   x_header_adj_val_tbl         oe_order_pub.header_adj_val_tbl_type;
   x_header_price_att_tbl       oe_order_pub.header_price_att_tbl_type;
   x_header_adj_att_tbl         oe_order_pub.header_adj_att_tbl_type;
   x_header_adj_assoc_tbl       oe_order_pub.header_adj_assoc_tbl_type;
   x_header_scredit_tbl         oe_order_pub.header_scredit_tbl_type;
   x_header_scredit_val_tbl     oe_order_pub.header_scredit_val_tbl_type;
   x_line_val_tbl               oe_order_pub.line_val_tbl_type;
   x_line_adj_tbl               oe_order_pub.line_adj_tbl_type;
   x_line_adj_val_tbl           oe_order_pub.line_adj_val_tbl_type;
   x_line_price_att_tbl         oe_order_pub.line_price_att_tbl_type;
   x_line_adj_att_tbl           oe_order_pub.line_adj_att_tbl_type;
   x_line_adj_assoc_tbl         oe_order_pub.line_adj_assoc_tbl_type;
   x_line_scredit_tbl           oe_order_pub.line_scredit_tbl_type;
   x_line_scredit_val_tbl       oe_order_pub.line_scredit_val_tbl_type;
   x_lot_serial_tbl             oe_order_pub.lot_serial_tbl_type;
   x_lot_serial_val_tbl         oe_order_pub.lot_serial_val_tbl_type;
   x_action_request_tbl         oe_order_pub.request_tbl_type;
   x_debug_file                 VARCHAR2(100);
   l_msg_index_out              NUMBER(10);
   l_line_tbl_index             NUMBER;
BEGIN
   dbms_output.ENABLE(1000000);
   fnd_global.Apps_initialize(1318, 21623, 660);
   -- pass in user_id, responsibility_id, and application_id 
   oe_msg_pub.initialize;
   oe_debug_pub.initialize;
   mo_global.Init ('ONT'); -- Required for R12
   fnd_global.Set_nls_context ('AMERICAN');
   mo_global.Set_policy_context ('S', 87); -- Required for R12

   --X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE'); 
   --oe_debug_pub.SetDebugLevel(5); 
   --dbms_output.put_line('START OF NEW DEBUG'); 
   --This is to update a line to an existing order 
   l_line_tbl_index :=1;
   -- Changed attributes
   l_line_tbl(l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
   l_line_tbl(l_line_tbl_index).invoice_to_org_id := 322;
   -- Primary key of the entity i.e. the order line
   l_line_tbl(l_line_tbl_index).line_id := 388459;
   l_line_tbl(l_line_tbl_index).change_reason := 'Not provided';
   -- Indicates to process order that this is an update operation
   l_line_tbl(l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

   -- CALL TO PROCESS ORDER 
   oe_order_pub.Process_order (  p_api_version_number   => 1.0                 ,
                                 p_init_msg_list        => fnd_api.g_false     ,
                                 p_return_values        => fnd_api.g_false     ,
                                 p_action_commit        => fnd_api.g_false     ,
                                 x_return_status        => l_return_status     ,
                                 x_msg_count            => l_msg_count         ,
                                 x_msg_data             => l_msg_data          ,
                                 p_header_rec           => l_header_rec        ,
                                 p_line_tbl             => l_line_tbl          ,
                                 p_action_request_tbl   => l_action_request_tbl
                                 -- OUT PARAMETERS 
                                 , x_header_rec             => x_header_rec
                                 , x_header_val_rec         => x_header_val_rec
                                 , x_header_adj_tbl         => x_header_adj_tbl
                                 , x_header_adj_val_tbl     => x_header_adj_val_tbl
                                 , x_header_price_att_tbl   => x_header_price_att_tbl
                                 , x_header_adj_att_tbl     => x_header_adj_att_tbl
                                 , x_header_adj_assoc_tbl   => x_header_adj_assoc_tbl
                                 , x_header_scredit_tbl     => x_header_scredit_tbl
                                 , x_header_scredit_val_tbl => x_header_scredit_val_tbl
                                 , x_line_tbl               => x_line_tbl
                                 , x_line_val_tbl           => x_line_val_tbl
                                 , x_line_adj_tbl           => x_line_adj_tbl
                                 , x_line_adj_val_tbl       => x_line_adj_val_tbl
                                 , x_line_price_att_tbl     => x_line_price_att_tbl
                                 , x_line_adj_att_tbl       => x_line_adj_att_tbl
                                 , x_line_adj_assoc_tbl     => x_line_adj_assoc_tbl
                                 , x_line_scredit_tbl       => x_line_scredit_tbl
                                 , x_line_scredit_val_tbl   => x_line_scredit_val_tbl
                                 , x_lot_serial_tbl         => x_lot_serial_tbl
                                 , x_lot_serial_val_tbl     => x_lot_serial_val_tbl
                                 , x_action_request_tbl     => x_action_request_tbl
                              );

   --dbms_output.put_line('OM Debug file: ' ||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE); 
   -- Retrieve messages 
   dbms_output.Put_line('Line Id: ' ||x_line_tbl(l_line_tbl_index).line_id);

   FOR i IN 1 .. l_msg_count
   LOOP
      oe_msg_pub.Get (  p_msg_index     => i               ,
                        p_encoded       => fnd_api.g_false ,
                        p_data          => l_msg_data      ,
                        p_msg_index_out => l_msg_index_out
                     );

      dbms_output.Put_line('message is: ' || l_msg_data);
      dbms_output.Put_line('message index is: ' || l_msg_index_out);
   END LOOP;

   -- Check the return status 
   IF l_return_status = fnd_api.g_ret_sts_success
   THEN
     dbms_output.put_line('Line Quantity Update Sucessful');
   ELSE
      dbms_output.put_line('Line Quantity update Failed');
   END IF;
END;

/
COMMIT;


--Note:- In R12 it is very important to have different input and output parameter/s to pass to the oe_order_pub API so as to successfully update the record

1 comment:

  1. Very nice and very informative blog that can be treasured for any immediate reference.
    thanks sirish

    ReplyDelete