Q -Can someone help me how to upload orders into oracle order management release 12. Is it possible to upload data from excel sheet to create order entry. Can I use APIs for this?
A:
You can use Oe_Order_Pub.Process_Order API
I have sample code for Order booking. With some modifications same can be used for Order creation also.
CREATE OR REPLACE PROCEDURE APPS.XX_Order_book( p_org_id in number
, p_operating_unit in varchar2
, p_header_id in number
, p_return_status out varchar2)
AS
CURSOR cur_line IS
SELECT line_id,
ordered_quantity,
schedule_ship_date
FROM oe_order_lines_all
WHERE header_id = p_header_id;
i NUMBER:=0;
X_DEBUG_FILE VARCHAR2(100);
--IN Parameters
l_header_rec OE_ORDER_PUB.Header_Rec_Type;
l_action_request_tbl OE_ORDER_PUB.Request_Tbl_Type;
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
--OUT Parameters
x_return_status VARCHAR2(10);
x_msg_count NUMBER;
x_msg_data VARCHAR2(200);
x_header_rec OE_ORDER_PUB.Header_Rec_Type;
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_tbl OE_ORDER_PUB.Line_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_Line_Payment_tbl OE_ORDER_PUB.Line_payment_tbl_type;
x_Line_Payment_val_tbl OE_ORDER_PUB.Line_Payment_val_tbl_type;
x_Header_Payment_tbl OE_ORDER_PUB.Header_payment_tbl_type;
x_Header_Payment_val_tbl OE_ORDER_PUB.Header_Payment_val_tbl_type;
BEGIN
oe_debug_pub.initialize;
X_DEBUG_FILE := OE_DEBUG_PUB.Set_Debug_Mode('TABLE');
oe_debug_pub.SetDebugLevel(1);
oe_msg_pub.initialize;
-- fnd_file.put_line(fnd_file.log, '3915!' || 3915);
fnd_global.apps_initialize( 1090, 50560, 660,NULL);
l_header_rec := Oe_Order_Pub.G_Miss_Header_Rec;
l_header_rec.header_id := p_header_id;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_action_request_tbl(1).request_type := oe_globals.g_book_order;
l_action_request_tbl(1).entity_code := oe_globals.g_entity_header;
l_action_request_tbl(1).entity_id := p_header_id;
i := 0;
FOR row_line IN cur_line
LOOP
i := i+1;
l_line_tbl(i) := oe_order_pub.g_miss_line_rec;
l_line_tbl(i).line_id := row_line.line_id;
l_line_tbl(i).shipped_quantity := row_line.ordered_quantity;
l_line_tbl(i).schedule_ship_date := row_line.schedule_ship_date;--sysdate;
l_line_tbl(i).operation := oe_globals.g_opr_update;
END LOOP;
--==============================================
Oe_Order_Pub.Process_Order
( p_org_id => p_org_id --MOAC
, p_operating_unit => p_operating_unit -- MOAC
, p_api_version_number => 1.0
, p_init_msg_list => FND_API.G_FALSE
, p_return_values => FND_API.G_TRUE
, p_action_commit => FND_API.G_FALSE
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_header_rec => l_header_rec
, p_line_tbl => l_line_tbl
, p_action_request_tbl => l_action_request_tbl
, x_header_rec => x_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_Header_Payment_tbl => x_Header_Payment_tbl
, x_Header_Payment_val_tbl => x_Header_Payment_val_tbl
, x_line_tbl => x_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_Line_Payment_tbl => x_Line_Payment_tbl
, x_Line_Payment_val_tbl => x_Line_Payment_val_tbl
, x_Lot_Serial_tbl => x_Lot_Serial_tbl
, x_Lot_Serial_val_tbl => x_Lot_Serial_val_tbl
, x_action_request_tbl => x_action_request_tbl
--For bug 3390458
, p_rtrim_data =>'N'
, p_validate_desc_flex => 'Y');
--==============================================
fnd_file.put_line(fnd_file.log, 'Success: Booked');
dbms_output.put_line( 'Success: Booked'||'Message - '||x_msg_data);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
fnd_file.put_line(fnd_file.log, 'Failed: Booked');
x_return_status := SQLERRM;
p_return_status := x_return_status;
END XX_Order_book;
No comments:
Post a Comment