In this post, I have discussed an API named OE_ORDER_PUB.GET_ORDER. The purpose of this API is to collect complete details about a sales order in R12.
Test Environment: R12.1.1
Script:
SET serveroutput ON;
DECLARE
P_API_VERSION_NUMBER NUMBER;
P_INIT_MSG_LIST VARCHAR2(200);
P_RETURN_VALUES VARCHAR2(200);
X_RETURN_STATUS VARCHAR2(200);
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2(200);
P_HEADER_ID NUMBER;
P_HEADER VARCHAR2(200);
P_ORG_ID NUMBER;
P_OPERATING_UNIT VARCHAR2(200);
X_HEADER_REC APPS.OE_ORDER_PUB.HEADER_REC_TYPE;
X_HEADER_VAL_REC APPS.OE_ORDER_PUB.HEADER_VAL_REC_TYPE;
X_HEADER_ADJ_TBL APPS.OE_ORDER_PUB.HEADER_ADJ_TBL_TYPE;
X_HEADER_ADJ_VAL_TBL APPS.OE_ORDER_PUB.HEADER_ADJ_VAL_TBL_TYPE;
X_HEADER_PRICE_ATT_TBL APPS.OE_ORDER_PUB.HEADER_PRICE_ATT_TBL_TYPE;
X_HEADER_ADJ_ATT_TBL APPS.OE_ORDER_PUB.HEADER_ADJ_ATT_TBL_TYPE;
X_HEADER_ADJ_ASSOC_TBL APPS.OE_ORDER_PUB.HEADER_ADJ_ASSOC_TBL_TYPE;
X_HEADER_SCREDIT_TBL APPS.OE_ORDER_PUB.HEADER_SCREDIT_TBL_TYPE;
X_HEADER_SCREDIT_VAL_TBL APPS.OE_ORDER_PUB.HEADER_SCREDIT_VAL_TBL_TYPE;
X_HEADER_PAYMENT_TBL APPS.OE_ORDER_PUB.HEADER_PAYMENT_TBL_TYPE;
X_HEADER_PAYMENT_VAL_TBL APPS.OE_ORDER_PUB.HEADER_PAYMENT_VAL_TBL_TYPE;
X_LINE_TBL APPS.OE_ORDER_PUB.LINE_TBL_TYPE;
X_LINE_VAL_TBL APPS.OE_ORDER_PUB.LINE_VAL_TBL_TYPE;
X_LINE_ADJ_TBL APPS.OE_ORDER_PUB.LINE_ADJ_TBL_TYPE;
X_LINE_ADJ_VAL_TBL APPS.OE_ORDER_PUB.LINE_ADJ_VAL_TBL_TYPE;
X_LINE_PRICE_ATT_TBL APPS.OE_ORDER_PUB.LINE_PRICE_ATT_TBL_TYPE;
X_LINE_ADJ_ATT_TBL APPS.OE_ORDER_PUB.LINE_ADJ_ATT_TBL_TYPE;
X_LINE_ADJ_ASSOC_TBL APPS.OE_ORDER_PUB.LINE_ADJ_ASSOC_TBL_TYPE;
X_LINE_SCREDIT_TBL APPS.OE_ORDER_PUB.LINE_SCREDIT_TBL_TYPE;
X_LINE_SCREDIT_VAL_TBL APPS.OE_ORDER_PUB.LINE_SCREDIT_VAL_TBL_TYPE;
X_LINE_PAYMENT_TBL APPS.OE_ORDER_PUB.LINE_PAYMENT_TBL_TYPE;
X_LINE_PAYMENT_VAL_TBL APPS.OE_ORDER_PUB.LINE_PAYMENT_VAL_TBL_TYPE;
X_LOT_SERIAL_TBL APPS.OE_ORDER_PUB.LOT_SERIAL_TBL_TYPE;
X_LOT_SERIAL_VAL_TBL APPS.OE_ORDER_PUB.LOT_SERIAL_VAL_TBL_TYPE;
BEGIN
-- Initialize the environment --
fnd_global.apps_initialize ( user_id => 2585
,resp_id => 50864
,resp_appl_id => 660);
mo_global.set_policy_context('S',83);
mo_global.init('ONT');
-- Api Parameters --
P_API_VERSION_NUMBER := 1;
P_INIT_MSG_LIST := FND_API.G_FALSE;
P_RETURN_VALUES := NULL;
P_HEADER_ID := 6220;
P_HEADER := NULL;
P_ORG_ID := 83;
P_OPERATING_UNIT := NULL;
OE_ORDER_PUB.GET_ORDER(
P_API_VERSION_NUMBER => P_API_VERSION_NUMBER,
P_INIT_MSG_LIST => P_INIT_MSG_LIST,
P_RETURN_VALUES => P_RETURN_VALUES,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_HEADER_ID => P_HEADER_ID,
P_ORG_ID => P_ORG_ID,
P_OPERATING_UNIT => P_OPERATING_UNIT,
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
);
DBMS_OUTPUT.PUT_LINE('X_RETURN_STATUS = ' || X_RETURN_STATUS);
DBMS_OUTPUT.PUT_LINE('X_MSG_COUNT = ' || X_MSG_COUNT);
DBMS_OUTPUT.PUT_LINE('X_MSG_DATA = ' || X_MSG_DATA);
DBMS_OUTPUT.PUT_LINE('Order number = ' || X_HEADER_REC.order_number);
DBMS_OUTPUT.PUT_LINE('Ordered Quantity = ' || X_LINE_TBL(1).ordered_quantity);
DBMS_OUTPUT.PUT_LINE('Shipped Quantity = ' || X_LINE_TBL(1).shipped_quantity);
IF x_return_status = fnd_api.g_ret_sts_success THEN
COMMIT;
DBMS_OUTPUT.put_line ('Sucessfull : ');
ELSE
DBMS_OUTPUT.put_line ('Failed with the error :');
ROLLBACK;
FOR i IN 1 .. x_msg_count
LOOP
x_msg_data := oe_msg_pub.get( p_msg_index => i, p_encoded => 'F');
dbms_output.put_line( i|| ') '|| x_msg_data);
END LOOP;
END IF;
END;
1 Responses to “OE_ORDER_PUB.GET_ORDER -- API to collect existing sales order data in R12”
May 15, 2014 at 5:00 AM
Thanks for the scripts and can you please post same way for LOCK_ORDER
-Sudhakar
Post a Comment