The
Process Orders API is very sensitive to the setup of Oracle apps, as the API
does lots of internal validations before applying any changes, it’s very
important that the user running the API has the requisite permissions, another
possible stumbling block could be the processing constraints, it has to be
ensured that the login from which the API is run does not have processing
constraints attached to it which prevent the API from functioning properly. All
The details about the processing constraints can be viewed by going to the
following menu
Setup
-> Rules -> Security -> Processing Constraints
Process Order API
This
document 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, 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;
