Creating and booking order(s) using API

Here post Oracle Order Management related topics.
Post Reply
admin
Posts: 2062
Joined: Fri Mar 31, 2006 12:59 am
Location: Pakistan
Contact:

Creating and booking order(s) using API

Post by admin »

CREATE TABLE CRM.ORDER_MAIN
(
ORDER_MAIN_ID NUMBER PRIMARY KEY,
TRANSACTIONAL_CURR_CODE VARCHAR2 (50 BYTE) DEFAULT 'PKR',
PRICING_DATE DATE DEFAULT SYSDATE,
CUST_PO_NUMBER VARCHAR2 (100 BYTE),
CUSTOMER_CODE VARCHAR2 (20 BYTE),
PRICE_LIST_ID NUMBER DEFAULT 10057,
ORDERED_DATE DATE DEFAULT SYSDATE,
SOLD_FROM_ORG_ID NUMBER DEFAULT 81,
SALESREP_ID VARCHAR2 (20 BYTE),
ORDER_TYPE_ID NUMBER DEFAULT 1038,
OE_ORDER_NUMBER VARCHAR2 (100 BYTE),
STATUS VARCHAR2 (10 BYTE),
ERROR VARCHAR2 (500 BYTE)
);

CREATE TABLE CRM.ORDER_DETAILS
(
ORDER_DETAIL_ID NUMBER PRIMARY KEY,
ORDER_MAIN_ID NUMBER,
PRODUCT_CODE NUMBER,
ORDERED_QUANTITY NUMBER,
SHIP_FROM_ORG_ID NUMBER DEFAULT 178,
SUBINVENTORY NVARCHAR2 (100) DEFAULT 'STOREOLL',
STATUS VARCHAR2 (100 BYTE)
);

SELECT s.SALESREP_ID, CATEGORY, resource_name AS salesperson_name
FROM jtf_rs_defresources_v j, jtf_rs_salesreps s
WHERE j.resource_id = s.resource_id AND resource_name LIKE '%SIKANDAR%';

DECLARE

/* This code is only for learning purposes and if you want to use it for your business then you must study it properly. First test on your DEV environment and once UAT completed then move to PROD.
ERPstuff will not take any responsibility as you need to update and test as per your requirements.
Sikandar Hayat - info@erpstuff.com
www.erpstuff.com
*/

l_header_rec OE_ORDER_PUB.Header_Rec_Type;
v_header_rec OE_ORDER_PUB.Header_Rec_Type;
l_line_tbl OE_ORDER_PUB.Line_Tbl_Type;
v_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_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);
p_action_request_tbl_index NUMBER;
l_msg_index_out NUMBER (10);
l_line_tbl_index NUMBER;
l_action_tbl_index NUMBER;
l_file_val VARCHAR2 (60);
BEGIN
fnd_global.apps_initialize (1183, 21623, 660); -- pass in user_id,responsibility_id, and application_id
mo_global.init ('ONT');
-- uncomment the following for R12 if not passing the parameter p_org_id
MO_GLOBAL.SET_POLICY_CONTEXT ('S', 81); -- this may not be needed since passing org_id to the API

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, its a lot of data

FOR c1 IN (SELECT ORDER_MAIN_ID,
TRANSACTIONAL_CURR_CODE,
PRICING_DATE,
CUST_PO_NUMBER,
(SELECT CUST_ACCOUNT_ID
FROM hz_cust_accounts
WHERE ACCOUNT_NUMBER = customer_code)
SOLD_TO_ORG_ID,
PRICE_LIST_ID,
ORDERED_DATE,
SOLD_FROM_ORG_ID,
SALESREP_ID,
ORDER_TYPE_ID,
OE_ORDER_NUMBER
FROM ORDER_MAIN)
LOOP
--Create Header record
--Initialize header record to missing
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
l_header_rec.TRANSACTIONAL_CURR_CODE := c1.TRANSACTIONAL_CURR_CODE;
l_header_rec.pricing_date := c1.pricing_date;
l_header_rec.cust_po_number := c1.cust_po_number;
l_header_rec.sold_to_org_id := c1.sold_to_org_id;
l_header_rec.price_list_id := c1.price_list_id;
l_header_rec.ordered_date := c1.ordered_date;
--l_header_rec.shipping_method_code := 'DHL';
l_header_rec.sold_from_org_id := c1.sold_from_org_id;
l_header_rec.salesrep_id := c1.salesrep_id;
l_header_rec.order_type_id := c1.order_type_id;
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.booked_flag := 'Y';

l_action_tbl_index := 1;

l_action_request_tbl (l_action_tbl_index).request_type :=
oe_globals.g_book_order;
l_action_request_tbl (l_action_tbl_index).entity_code :=
oe_globals.g_entity_header;


l_line_tbl_index := 1;

FOR c2 IN (SELECT ORDER_DETAIL_ID,
ORDER_MAIN_ID,
product_code,
ORDERED_QUANTITY,
SHIP_FROM_ORG_ID,
SUBINVENTORY
FROM ORDER_DETAILS
WHERE ORDER_MAIN_ID = c1.order_main_id)
LOOP
l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
-- Line attributes
l_line_tbl (l_line_tbl_index).inventory_item_id :=
c2.product_code;

l_line_tbl (l_line_tbl_index).ordered_quantity :=
c2.ordered_quantity;
l_line_tbl (l_line_tbl_index).ship_from_org_id :=
c2.ship_from_org_id;
l_line_tbl (l_line_tbl_index).subinventory := c2.subinventory;
l_line_tbl (l_line_tbl_index).operation :=
OE_GLOBALS.G_OPR_CREATE;

l_line_tbl_index := l_line_tbl_index + 1; -- if more than one order line
END LOOP;

-- CALL TO PROCESS ORDER Check the return status and then commit.

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 => v_header_rec, -- HEADER
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 => v_line_tbl, -- LINE
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 => x_action_request_tbl);

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 (
'Order No :' || TO_CHAR (v_header_rec.order_number));

UPDATE order_main
SET OE_ORDER_NUMBER = TO_CHAR (v_header_rec.order_number),
status = 'BOOKED'
WHERE ORDER_MAIN_ID = c1.ORDER_MAIN_ID;

DBMS_OUTPUT.put_line ('Process Order Success');
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Failed');
ROLLBACK;
END IF;

-- debug output
DBMS_OUTPUT.put_line ('Debug Output');

FOR i IN 1 .. OE_DEBUG_PUB.g_debug_count
LOOP
DBMS_OUTPUT.put_line (OE_DEBUG_PUB.G_debug_tbl (i));
END LOOP;
END LOOP;
END;
/
Post Reply

Who is online

Users browsing this forum: Bing [Bot] and 0 guests