Entity Table Name
Order Header OE_ORDER_HEADERS_ALL
Order Price Adjustments OE_PRICE_ADJUSTMENTS
Order Sales Credits OE_SALES_CREDITS
Order Line OE_ORDER_LINES_ALL
Order Pricing Attributes OE_ORDER_PRICE_ATTRIBS
Order Adjustment Attributes OE_PRICE_ADJ_ATTRIBS
Order Adjustment Associations OE_PRICE_ADJ_ASSOCS
Line Sales Credits OE_SALES_CREDITS
Line Price Adjustments OE_PRICE_ADJUSTMENTS
Line Pricing Attributes OE_ORDER_PRICE_ATTRIBS
Line Adjustment Attributes OE_PRICE_ADJ_ATTRIBS
Line Adjustment Associations OE_PRICE_ADJ_ASSOCS
Lot Serial Numbers OE_LOT_SERIAL_NUMBERS
Process Order API:
PL/SQL Block to call the Process Order API
The process order API can be called by a pl/sql block, which can be executed in sqlplus.
This section describes and helps to understand how the pl/sql block can be written.
First, specify the variables in the declare section on the block.
l_header_rec oe_order_pub.Header_Rec_Type;
l_line_tbl oe_order_pub.Line_Tbl_Type;
The variable, l_header_rec is declared as a record datatype referring
to Header_Rec_Type in the package, oe_order_pub.
p_api_version_number NUMBER :=1.0;
The API version number needs to be initialized as version 1.0 and this
would get validated by the API. The process order API will not proceed
if the version number is other than 1.0
x_return_status VARCHAR2(1);
Returns FND_API.G_RET_STS_STATUS (success), FND_API.G_RET_STS_ERROR
(error) or FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
p_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;
The parameter, p_header_rec is declared with the datatype as Header_Rec_Type in
oe_order_pub. It is initialized to the value returned by the function,
G_MISS_HEADER_REC. This is done so that the parameter does not contain a null
value. Further, it is pertinent to note that there is also a parameter, p_old_header_rec that
is used for retaining the old value in case the API is updating a record. The API would
compare the old and new values in the respective parameters and then update the tables if
the values are different.
x_header_val_rec Oe_Order_Pub.Header_Val_Rec_Type;
x_Header_Adj_tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
. . . (etc)
These are variables, which will hold the value that is returned by the API.
BEGIN section of the pl/sql block contains the following statements.
dbms_output.enable(1000000);
Sets the buffer size as specified so that all the messages are written
to the debug file.
fnd_global.apps_initialize(4096,21623,660);
Need to pass in user_id, responsibility_id, and application_id here, as
the system would need to information while setting the who columns for
updating the data in the tables. Also required to set the
organization/operating unit context for the system has to see the data in views.
oe_debug_pub.SetDebugLevel(5)
Sets the debug level to 5. This is the maximum debug level in the sense that all messages would be written to the debug file.
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
This is the header record and is intialized to missing. Initially,
there would not be a header_id generated for the record and hence it is
set to missing. Once the header_id id generated by the API, the
l_header_rec will take the value of the header_id.
l_header_rec.order_type_id := 1437;
l_header_rec.sold_to_org_id := 1000;
The order type and customer(sold_to_org_id) are required attributes and
must be specified.
l_header_rec.ship_to_org_id := 1001;
l_header_rec.ship_from_org_id := 207;
l_header_rec.orig_sys_document_ref := 'TEST9';
l_header_rec.price_list_id := 1000;
These are the other attributes that can be specified.
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
The above statement indicates to the process order API that a new
header has to be created.
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
Initializing the line record to missing as we have done earlier for the header record.
l_line_tbl(1).inventory_item_id := 149;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).orig_sys_document_ref := 'TEST9';
l_line_tbl(1).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(1).calculate_price_flag := 'N';
l_line_tbl(1).line_type_id := 1427;
All the above belong to the first line record as it is indicated by (1)
in l_line_tbl(1). For the second record, it will be l_line_tbl(2).
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
Indicates that this is a create operation for the line record.
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
Indicates that this is an update operation for the line record.
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_DELETE;
Indicates that this is a delete operation for the line record.
All lines in the table l_line_tbl must belong to the same order.
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
The above action request indicates to the process order that the order
has to be booked. User need not specifically indicate the header_id if
the order has to be booked while creation or updation. Otherwise, if it
is separate action, then the header_id has to be provided. The action
request statements can be commented out if you do not require the order to be booked.
oe_order_pub.process_order(.........
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;
The purpose of the above statement is to collect the messages that have
been generated by the process order API when it had run and then
display them accordingly.
The number of messages is stored in l_msg_count. Each time the process
order encounters a message, it increases the count in x_msg_count and
then is finally passed to l_msg_count. The Oe_Msg_Pub gets the messages
as per the message index.
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Header_id is ' || l_header_rec.header_id);
else
dbms_output.put_line('Failed');
end if;
If the process order is successful, it prints the header_id, otherwise
it indicates that the process order has failed.
ANNEXURE - I
Creating a Sales Order using the Process Order API
Run the following pl/sql block in sqlplus in vision database and ensure that the procedure
executes successfully.
For debugging purposes, the debug level has been set to 5 in the following pl/sql block
through the statement, “oe_debug_pub.SetDebugLevel(5)”. This means that all OM
debug messages would get written to the OM debug file. The pl/sql block also prints out
the location where the debug file was written to and the user can retrieve the debug file
from this location for analysis. This is achieved by placing the following statement after
the call to the process order: dbms_output.put_line('OM Debug file:'
||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
--- Start of script -------
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_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;
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 e_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;
p_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);
BEGIN
dbms_output.enable(1000000);
fnd_global.apps_initialize(4096,21623,660); -- pass in user_id,
responsibility_id, and application_id
-------------------------------
oe_msg_pub.initialize;
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output,
I warn you its a lot of data
dbms_output.put_line('START OF NEW DEBUG');
-------------------------------
-- SETTING UP THE HEADER RECORD
-- Initialize record to missing
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
l_header_rec.order_type_id := 1437;
l_header_rec.sold_to_org_id := 1000;
l_header_rec.ship_to_org_id := 1001;
l_header_rec.ship_from_org_id := 207;
l_header_rec.orig_sys_document_ref := 'TEST9';
l_header_rec.price_list_id := 1000;
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.cust_po_number := 'TESTxxx';
-- First line record
-- Initialize record to missing
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).inventory_item_id := 149;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).orig_sys_document_ref := 'TEST9';
l_line_tbl(1).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(1).calculate_price_flag := 'Y';
l_line_tbl(1).line_type_id := 1427;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
-- dbms_output.put_line('Line operation before is
'||l_line_tbl(1).operation);
-- Second line record
l_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(2).inventory_item_id := 155;
l_line_tbl(2).ordered_quantity := 1;
l_line_tbl(2).orig_sys_document_ref := 'TEST9';
l_line_tbl(2).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(2).calculate_price_flag := 'Y';
l_line_tbl(2).line_type_id := 1427;
l_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
-- FIRST LINE RECORD
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 => l_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 => l_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 => l_action_request_tbl
);
dbms_output.put_line('OM Debug file: '
||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
dbms_output.put_line('Line operation after is
'||l_line_tbl(1).operation);
dbms_output.put_line('Header_id is ' || l_header_rec.header_id);
-- Retrieve messages
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('Header_id is ' || l_header_rec.header_id);
else
dbms_output.put_line('Failed');
end if;
end;
---End of script----------
Order Header OE_ORDER_HEADERS_ALL
Order Price Adjustments OE_PRICE_ADJUSTMENTS
Order Sales Credits OE_SALES_CREDITS
Order Line OE_ORDER_LINES_ALL
Order Pricing Attributes OE_ORDER_PRICE_ATTRIBS
Order Adjustment Attributes OE_PRICE_ADJ_ATTRIBS
Order Adjustment Associations OE_PRICE_ADJ_ASSOCS
Line Sales Credits OE_SALES_CREDITS
Line Price Adjustments OE_PRICE_ADJUSTMENTS
Line Pricing Attributes OE_ORDER_PRICE_ATTRIBS
Line Adjustment Attributes OE_PRICE_ADJ_ATTRIBS
Line Adjustment Associations OE_PRICE_ADJ_ASSOCS
Lot Serial Numbers OE_LOT_SERIAL_NUMBERS
Process Order API:
PL/SQL Block to call the Process Order API
The process order API can be called by a pl/sql block, which can be executed in sqlplus.
This section describes and helps to understand how the pl/sql block can be written.
First, specify the variables in the declare section on the block.
l_header_rec oe_order_pub.Header_Rec_Type;
l_line_tbl oe_order_pub.Line_Tbl_Type;
The variable, l_header_rec is declared as a record datatype referring
to Header_Rec_Type in the package, oe_order_pub.
p_api_version_number NUMBER :=1.0;
The API version number needs to be initialized as version 1.0 and this
would get validated by the API. The process order API will not proceed
if the version number is other than 1.0
x_return_status VARCHAR2(1);
Returns FND_API.G_RET_STS_STATUS (success), FND_API.G_RET_STS_ERROR
(error) or FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
p_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;
The parameter, p_header_rec is declared with the datatype as Header_Rec_Type in
oe_order_pub. It is initialized to the value returned by the function,
G_MISS_HEADER_REC. This is done so that the parameter does not contain a null
value. Further, it is pertinent to note that there is also a parameter, p_old_header_rec that
is used for retaining the old value in case the API is updating a record. The API would
compare the old and new values in the respective parameters and then update the tables if
the values are different.
x_header_val_rec Oe_Order_Pub.Header_Val_Rec_Type;
x_Header_Adj_tbl Oe_Order_Pub.Header_Adj_Tbl_Type;
. . . (etc)
These are variables, which will hold the value that is returned by the API.
BEGIN section of the pl/sql block contains the following statements.
dbms_output.enable(1000000);
Sets the buffer size as specified so that all the messages are written
to the debug file.
fnd_global.apps_initialize(4096,21623,660);
Need to pass in user_id, responsibility_id, and application_id here, as
the system would need to information while setting the who columns for
updating the data in the tables. Also required to set the
organization/operating unit context for the system has to see the data in views.
oe_debug_pub.SetDebugLevel(5)
Sets the debug level to 5. This is the maximum debug level in the sense that all messages would be written to the debug file.
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
This is the header record and is intialized to missing. Initially,
there would not be a header_id generated for the record and hence it is
set to missing. Once the header_id id generated by the API, the
l_header_rec will take the value of the header_id.
l_header_rec.order_type_id := 1437;
l_header_rec.sold_to_org_id := 1000;
The order type and customer(sold_to_org_id) are required attributes and
must be specified.
l_header_rec.ship_to_org_id := 1001;
l_header_rec.ship_from_org_id := 207;
l_header_rec.orig_sys_document_ref := 'TEST9';
l_header_rec.price_list_id := 1000;
These are the other attributes that can be specified.
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
The above statement indicates to the process order API that a new
header has to be created.
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
Initializing the line record to missing as we have done earlier for the header record.
l_line_tbl(1).inventory_item_id := 149;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).orig_sys_document_ref := 'TEST9';
l_line_tbl(1).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(1).calculate_price_flag := 'N';
l_line_tbl(1).line_type_id := 1427;
All the above belong to the first line record as it is indicated by (1)
in l_line_tbl(1). For the second record, it will be l_line_tbl(2).
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
Indicates that this is a create operation for the line record.
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_UPDATE;
Indicates that this is an update operation for the line record.
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_DELETE;
Indicates that this is a delete operation for the line record.
All lines in the table l_line_tbl must belong to the same order.
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
The above action request indicates to the process order that the order
has to be booked. User need not specifically indicate the header_id if
the order has to be booked while creation or updation. Otherwise, if it
is separate action, then the header_id has to be provided. The action
request statements can be commented out if you do not require the order to be booked.
oe_order_pub.process_order(.........
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;
The purpose of the above statement is to collect the messages that have
been generated by the process order API when it had run and then
display them accordingly.
The number of messages is stored in l_msg_count. Each time the process
order encounters a message, it increases the count in x_msg_count and
then is finally passed to l_msg_count. The Oe_Msg_Pub gets the messages
as per the message index.
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('Header_id is ' || l_header_rec.header_id);
else
dbms_output.put_line('Failed');
end if;
If the process order is successful, it prints the header_id, otherwise
it indicates that the process order has failed.
ANNEXURE - I
Creating a Sales Order using the Process Order API
Run the following pl/sql block in sqlplus in vision database and ensure that the procedure
executes successfully.
For debugging purposes, the debug level has been set to 5 in the following pl/sql block
through the statement, “oe_debug_pub.SetDebugLevel(5)”. This means that all OM
debug messages would get written to the OM debug file. The pl/sql block also prints out
the location where the debug file was written to and the user can retrieve the debug file
from this location for analysis. This is achieved by placing the following statement after
the call to the process order: dbms_output.put_line('OM Debug file:'
||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
--- Start of script -------
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_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;
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 e_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;
p_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);
BEGIN
dbms_output.enable(1000000);
fnd_global.apps_initialize(4096,21623,660); -- pass in user_id,
responsibility_id, and application_id
-------------------------------
oe_msg_pub.initialize;
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.SetDebugLevel(5); -- Use 5 for the most debuging output,
I warn you its a lot of data
dbms_output.put_line('START OF NEW DEBUG');
-------------------------------
-- SETTING UP THE HEADER RECORD
-- Initialize record to missing
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
l_header_rec.order_type_id := 1437;
l_header_rec.sold_to_org_id := 1000;
l_header_rec.ship_to_org_id := 1001;
l_header_rec.ship_from_org_id := 207;
l_header_rec.orig_sys_document_ref := 'TEST9';
l_header_rec.price_list_id := 1000;
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.cust_po_number := 'TESTxxx';
-- First line record
-- Initialize record to missing
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(1).inventory_item_id := 149;
l_line_tbl(1).ordered_quantity := 1;
l_line_tbl(1).orig_sys_document_ref := 'TEST9';
l_line_tbl(1).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(1).calculate_price_flag := 'Y';
l_line_tbl(1).line_type_id := 1427;
l_line_tbl(1).operation := OE_GLOBALS.G_OPR_CREATE;
-- dbms_output.put_line('Line operation before is
'||l_line_tbl(1).operation);
-- Second line record
l_line_tbl(2) := OE_ORDER_PUB.G_MISS_LINE_REC;
l_line_tbl(2).inventory_item_id := 155;
l_line_tbl(2).ordered_quantity := 1;
l_line_tbl(2).orig_sys_document_ref := 'TEST9';
l_line_tbl(2).orig_sys_line_ref := 'TEST9-1';
l_line_tbl(2).calculate_price_flag := 'Y';
l_line_tbl(2).line_type_id := 1427;
l_line_tbl(2).operation := OE_GLOBALS.G_OPR_CREATE;
-- FIRST LINE RECORD
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 => l_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 => l_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 => l_action_request_tbl
);
dbms_output.put_line('OM Debug file: '
||oe_debug_pub.G_DIR||'/'||oe_debug_pub.G_FILE);
dbms_output.put_line('Line operation after is
'||l_line_tbl(1).operation);
dbms_output.put_line('Header_id is ' || l_header_rec.header_id);
-- Retrieve messages
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('Header_id is ' || l_header_rec.header_id);
else
dbms_output.put_line('Failed');
end if;
end;
---End of script----------
No comments:
Post a Comment