1. Creating Order Lines
his blog
post will explain the use of this API for the following:
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;
Give me your Feedback.......
No comments:
Post a Comment
Note: only a member of this blog may post a comment.