Oracle Apps Order Management (OM) Process Order API

Oracle Apps Order Management (OM) Process Order API

This blog post will explain the use of this API for the following:

1.   Creating Order Lines

2.   Deleting Shipment Lines

3.   Splitting Shipment Lines

4.   Reserving a shipment Line

5.   Updating a shipment line

6.   Scheduling a shipment line

7.   Applying Hold on an Order Line

Process Orders API can process multiple records in one call, this is achieved by accepting parameters in the form of a table, and each row corresponds to one record. 

Any program using this API will require 2 phases, 1^st phase involves inserting data into the table which will in turn be used as an input parameter to the API, and the second would be the actual API call. The 1^st phase begins with the creation of a new row in the table this is done as follows:

<table name> (<row number>):= OE_ORDER_PUB.G_MISS_LINE_REC;

after creating the line all the mandatory and  possibly some of the optional parameters are written into the newly created row, for example for a  shipment line to be deleted the row would have been filled as follows.

<table name>(<row number>).line_id := < give the line ID of the line to be deleted>;

<table name>(<row number>).change_reason := '<give the reason for deleting the line>';

<table name> (<row number>).operation := OE_GLOBALS.G_OPR_DELETE;

please note that by incrementing the row number, and following similar procedure the programmer can actually create a number of such rows spanning  possibly multiple operations. The second phase involves the actual call of the API here the programmer passes the above table as the input parameter to the API, One of the ways of doing this is as follows:

OE_ORDER_PUB.Process_Order

            (   p_api_version_number               =>  1.00

          ,   p_init_msg_list                           =>  'T'

          ,   p_return_values                         =>  'T'

          ,   p_action_commit                        =>  'F'

          ,   x_return_status                         =>  l_chr_return_status

          ,   x_msg_count                             =>  l_num_msg_cnt

          ,   x_msg_data                               =>  l_chr_msg

          ,   p_header_rec                            =>  l_header_rec

          ,   p_line_tbl                                  =>  l_line_tbl

          ,   p_old_line_tbl                            =>  l_old_Line_Tbl

          ,   p_action_request_tbl                  =>  l_Request_Tbl

          ,   x_header_rec                            =>  l_Header_Rec

          ,   x_header_val_rec                       =>  l_Header_Val_Rec

          ,   x_Header_Adj_tbl                      =>  l_Header_Adj_Tbl

          ,   x_Header_Adj_val_tbl               =>  l_Header_Adj_Val_Tbl

          ,   x_Header_price_Att_tbl             =>  l_Header_Price_Att_Tbl

          ,   x_Header_Adj_Att_tbl               =>  l_Header_Adj_Att_Tbl

          ,   x_Header_Adj_Assoc_tbl           =>  l_Header_Adj_Assoc_Tbl

          ,   x_Header_Scredit_tbl                 =>  l_Header_Scredit_Tbl

          ,   x_Header_Scredit_val_tbl          =>  l_Header_Scredit_Val_Tbl

          ,   x_line_tbl                                    =>  <table name>

          ,   x_line_val_tbl                             =>  l_Line_Val_Tbl

          ,   x_Line_Adj_tbl                           =>  l_Line_Adj_Tbl

          ,   x_Line_Adj_val_tbl                    =>  l_Line_Adj_Val_Tbl

          ,   x_Line_price_Att_tbl                  =>  l_Line_Price_Att_Tbl

          ,   x_Line_Adj_Att_tbl                    =>  l_Line_Adj_Att_Tbl

          ,   x_Line_Adj_Assoc_tbl               =>  l_Line_Adj_Assoc_Tbl

          ,   x_Line_Scredit_tbl                      =>  l_Line_Scredit_Tbl

          ,   x_Line_Scredit_val_tbl               =>  l_Line_Scredit_Val_Tbl

          ,   x_Lot_Serial_tbl                         =>  l_Lot_Serial_Tbl

          ,   x_Lot_Serial_val_tbl                  =>  l_Lot_Serial_Val_Tbl

          ,   x_action_request_tbl                   =>  l_Request_Tbl

     );

please note the above code for the API call is pretty much independent of the functionality the API is being used for, the functionality is a field already written in the input parameter ( the table).     

In the following  i = the current line number in the table

Creating Order Lines

l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i).header_id := <header_id>;

l_line_tbl(i).inventory_item_id := <inventory_item_id>;

l_line_tbl(i).ordered_quantity :=<ordered quantity>;

l_line_tbl(i).operation := OE_GLOBALS.G_OPR_CREATE;

Deleting Shipment Lines 

l_line_tbl(i) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i).line_id :=<line_id>;

l_line_tbl(i).operation := OE_GLOBALS.G_OPR_DELETE;

Splitting Shipment Lines

l_line_tbl(1):=OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i).split_action_code := 'SPLIT';

l_line_tbl(i).split_by := 'USER';

l_line_tbl(i).line_id :=<line_id>;

l_line_tbl(i).inventory_item_id := <inventory_item_id>;

l_line_tbl(i).ordered_quantity := <ordered_quantity>;

l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;

l_line_tbl(i+1):=OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i+1).split_action_code := 'SPLIT';

l_line_tbl(i+1).split_by := 'USER';

l_line_tbl(i+1).inventory_item_id := <inventory_item_id>;

l_line_tbl(i+1).ordered_quantity := <ordered_quantity>;

l_line_tbl(i+1).split_from_line_id := l_line_tbl(i).line_id;

l_line_tbl(i+1).operation := OE_GLOBALS.G_OPR_CREATE;

Reserving a shipment Line 

l_line_tbl(i):=OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i).line_id := <line_id>;

l_line_tbl(i).reserved_quantity := <reserved_qty>;

l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;

Updating a shipment line 

l_line_tbl(1):=OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(1).ordered_quantity:=<ordered_quantity>;

l_line_tbl(1).line_id :=<line_id>;

l_line_tbl(1).operation :=  OE_GLOBALS.G_OPR_UPDATE;

Scheduling a shipment line 

l_line_tbl(i):=OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl(i).line_id := <line_id>;

l_line_tbl(i).delivery_lead_time := <transit_time>;

l_line_tbl(i).schedule_ship_date := <schedule_ship_date>;

l_line_tbl(i).operation := OE_GLOBALS.G_OPR_UPDATE;

                 

Applying Hold on an Order Line 

l_request_rec.entity := OE_GLOBALS.G_ENTITY_LINE;

l_request_rec.entity_id := <line_id>;

-- request record parameters

-- defective product hold (hold_id)

l_request_rec.param1 := <request_rec_param1>;

-- indicator that it is an item hold (hold_entity_code)

l_request_rec.param2 = <request_rec_param2>;

-- Id of the item (hold_entity_id)

l_request_rec.param3 := <request_rec_param3>;

-- inserting request record into the action request table

l_action_request_tbl := <request_rec>;

l_request_rec.request_name := OE_GLOBALS.G_APPLY_HOLD;

REUSABLE CODE

Once the programmer has built the table for the API call, the programmer may optionally write the code for calling the API or he may pass the table created above to this procedure which will in turn do the actual API call.

PROCEDURE poapi_call( l_line_tbl  IN  OE_ORDER_PUB.Line_Tbl_Type) IS

   l_return_status               VARCHAR2(1);

   l_num_msg_cnt               NUMBER;

   l_header_rec                  OE_ORDER_PUB.Header_Rec_Type;

   l_header_val_rec             OE_ORDER_PUB.Header_Val_Rec_Type;

   l_line_rec                OE_ORDER_PUB.Line_Rec_Type;

   l_Split_line_rec          OE_ORDER_PUB.Line_Rec_Type;

   l_Header_Adj_tbl                 OE_ORDER_PUB.Header_Adj_Tbl_Type;

   l_Header_Adj_val_tbl             OE_ORDER_PUB.Header_Adj_Val_Tbl_Type;

   l_Header_price_Att_tbl        OE_ORDER_PUB.Header_Price_Att_Tbl_Type ;

   l_Header_Adj_Att_tbl          OE_ORDER_PUB.Header_Adj_Att_Tbl_Type ;

   l_Header_Adj_Assoc_tbl        OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type ;

   l_Header_Scredit_tbl          OE_ORDER_PUB.Header_Scredit_Tbl_Type;

   l_Header_Scredit_val_tbl      OE_ORDER_PUB.Header_Scredit_Val_Tbl_Type;

   l_line_val_tbl                OE_ORDER_PUB.Line_Val_Tbl_Type;

   l_Line_Adj_tbl                OE_ORDER_PUB.Line_Adj_Tbl_Type;

   l_Line_Adj_val_tbl            OE_ORDER_PUB.Line_Adj_Val_Tbl_Type;

   l_Line_price_Att_tbl          OE_ORDER_PUB.Line_Price_Att_Tbl_Type ;

   l_Line_Adj_Att_tbl            OE_ORDER_PUB.Line_Adj_Att_Tbl_Type ;

   l_Line_Adj_Assoc_tbl          OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type ;

   l_Line_Scredit_tbl            OE_ORDER_PUB.Line_Scredit_Tbl_Type;

   l_Line_Scredit_val_tbl        OE_ORDER_PUB.Line_Scredit_Val_Tbl_Type;

   l_Lot_Serial_tbl              OE_ORDER_PUB.Lot_Serial_Tbl_Type;

   l_Lot_Serial_val_tbl          OE_ORDER_PUB.Lot_Serial_Val_Tbl_Type;

   l_old_header_rec              OE_ORDER_PUB.Header_Rec_Type;

   l_old_Header_Adj_tbl          OE_ORDER_PUB.Header_Adj_Tbl_Type;

   l_old_header_Val_rec           OE_ORDER_PUB.Header_Rec_Type;

   l_old_Header_Adj_Val_tbl       OE_ORDER_PUB.Header_Adj_Tbl_Type;

   l_old_Header_price_Att_tbl    OE_ORDER_PUB.Header_Price_Att_Tbl_Type ;

   l_old_Header_Adj_Att_tbl      OE_ORDER_PUB.Header_Adj_Att_Tbl_Type ;

   l_old_Header_Adj_Assoc_tbl    OE_ORDER_PUB.Header_Adj_Assoc_Tbl_Type ;

   l_old_Header_Scredit_tbl      OE_ORDER_PUB.Header_Scredit_Tbl_Type;

   l_old_Header_Scredit_Val_tbl  OE_ORDER_PUB.Header_Scredit_Tbl_Type;

   l_old_line_tbl                OE_ORDER_PUB.Line_Tbl_Type;

   l_old_Line_Val_Tbl             OE_ORDER_PUB.Line_Tbl_Type;

   l_old_Line_Adj_tbl            OE_ORDER_PUB.Line_Adj_Tbl_Type;

   l_old_Line_Adj_Val_tbl        OE_ORDER_PUB.Line_Adj_Tbl_Type;

   l_old_Line_price_Att_tbl      OE_ORDER_PUB.Line_Price_Att_Tbl_Type ;

   l_old_Line_Adj_Att_tbl        OE_ORDER_PUB.Line_Adj_Att_Tbl_Type ;

   l_old_Line_Adj_Assoc_tbl      OE_ORDER_PUB.Line_Adj_Assoc_Tbl_Type ;

   l_old_Line_Scredit_tbl        OE_ORDER_PUB.Line_Scredit_Tbl_Type;

   l_old_Line_Scredit_Val_tbl    OE_ORDER_PUB.Line_Scredit_Tbl_Type;

   l_old_Lot_Serial_tbl          OE_ORDER_PUB.Lot_Serial_Tbl_Type;

   l_old_Lot_Serial_Val_tbl      OE_ORDER_PUB.Lot_Serial_Tbl_Type;

   l_Request_Tbl                  OE_ORDER_PUB.Request_Tbl_Type;

   l_returnline_tbl              OE_ORDER_PUB.Line_Tbl_Type;

   l_returnheader_rec             OE_ORDER_PUB.Header_Rec_Type;

   l_pls_line                PLS_INTEGER;

   l_num_LnCnt               NUMBER := 0;

   l_chr_return_status            VARCHAR2(30);

   l_chr_msg                 VARCHAR2(2000);

   l_message                 VARCHAR2(2000);

   l_msg_index_out           NUMBER;

   l_chr_action                   VARCHAR2(10) := NULL;

   iTempCnt                       NUMBER := 1;

   l_msg_data                VARCHAR2(2000);

BEGIN

OE_ORDER_PUB.Process_Order

     (   p_api_version_number            =>  1.00

          ,   p_init_msg_list                   =>  'T'

          ,   p_return_values                  =>  'T'

          ,   p_action_commit                =>  'F'

          ,   x_return_status                   =>  l_chr_return_status

          ,   x_msg_count                      =>  l_num_msg_cnt

          ,   x_msg_data                           =>  l_chr_msg

          ,   p_header_rec                         =>  l_header_rec

          ,   p_line_tbl                              =>  l_line_tbl

          ,   p_old_line_tbl                       =>  l_old_Line_Tbl

          ,   p_action_request_tbl             =>  l_Request_Tbl

          ,   x_header_rec                         =>  l_Header_Rec

          ,   x_header_val_rec                  =>  l_Header_Val_Rec

          ,   x_Header_Adj_tbl                 =>  l_Header_Adj_Tbl

          ,   x_Header_Adj_val_tbl          =>  l_Header_Adj_Val_Tbl

          ,   x_Header_price_Att_tbl        =>  l_Header_Price_Att_Tbl

          ,   x_Header_Adj_Att_tbl          =>  l_Header_Adj_Att_Tbl

          ,   x_Header_Adj_Assoc_tbl      =>  l_Header_Adj_Assoc_Tbl

          ,   x_Header_Scredit_tbl            =>  l_Header_Scredit_Tbl

          ,   x_Header_Scredit_val_tbl     =>  l_Header_Scredit_Val_Tbl

          ,   x_line_tbl                               =>  l_Line_Tbl

          ,   x_line_val_tbl                        =>  l_Line_Val_Tbl

          ,   x_Line_Adj_tbl                      =>  l_Line_Adj_Tbl

          ,   x_Line_Adj_val_tbl               =>  l_Line_Adj_Val_Tbl

          ,   x_Line_price_Att_tbl             =>  l_Line_Price_Att_Tbl

          ,   x_Line_Adj_Att_tbl               =>  l_Line_Adj_Att_Tbl

          ,   x_Line_Adj_Assoc_tbl          =>  l_Line_Adj_Assoc_Tbl

          ,   x_Line_Scredit_tbl                =>  l_Line_Scredit_Tbl

          ,   x_Line_Scredit_val_tbl          =>  l_Line_Scredit_Val_Tbl

          ,   x_Lot_Serial_tbl                    =>  l_Lot_Serial_Tbl

          ,   x_Lot_Serial_val_tbl             =>  l_Lot_Serial_Val_Tbl

          ,   x_action_request_tbl             =>  l_Request_Tbl

     );

EXCEPTION

  WHEN OTHERS THEN

     fnd_file.put_line (fnd_file.LOG, 'POAPI_call went in to an unknown

   exception');

     fnd_file.put_line(fnd_file.LOG, 'Error message is '|| SQLERRM);

    IF l_num_msg_cnt > 0 THEN

          FOR l_index IN 1..l_num_msg_cnt LOOP

l_msg_data := oe_msg_pub.get(p_msg_index => l_index, p_encoded => 'F');

             fnd_file.put_line(2,l_msg_data);

             fnd_file.put_line(fnd_file.LOG, 'POAPI went into

               exception');

          END LOOP;

    END IF;

END poapi_call;