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
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, 1st 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 1st 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;
