Oracle Order Management : Splitting the lines Technical Overview

Business Scenarios:

Order Management allow you to split order lines to meet customer needs. Until the product is shipped, the customer can request to change the shipping address or date for part of the order line. To meet such requests, split the order line into multiple shipments. These are referred to as user initiated splits .

When Order Lines are partially processed at:

Ship Confirmation – When the shipping department finds that stock on hand is less than the ordered quantity, you can ship the available quantity and Order Management will split the line so that the customer can be billed for what was shipped.
Purchase Release Receipt – When a Drop-Ship Line is partially received, Order Management splits the line so that a customer can be invoiced for what was already shipped.

When Return Lines are partially processed at:

Return Receipt – When the customer returns partial quantity on a return, the system splits the return line so that customers can be issued credit for what was returned.

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_request_rec OE_ORDER_PUB.Request_Rec_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_order_number VARCHAR2 (10);

l_header_id NUMBER;

l_line_id NUMBER;

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 OE_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_line_tbl_index NUMBER;

l_msg_index_out NUMBER (10);

BEGIN

DBMS_OUTPUT.enable (1000000);

fnd_global.apps_initialize (1318, 21623, 660); -- pass in user_id, responsibility_id, and application_id

--tables used for user_id ->fund_user,

--USE BELOW CODE

/*SELECT user_id, responsibility_application_id, responsibility_id

FROM fnd_user_resp_groups_all

WHERE responsibility_application_id =

(SELECT application_id

FROM fnd_allications

WHERE application_short_name = 'ONT')*/

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');

--This is to UPDATE order line

l_line_tbl_index := 1;

-- Changed attributes

l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;

--TO GET HEADER_ID,LINE_ID

SELECT ooha.header_id, oola.line_id

INTO l_header_id, l_line_id

FROM oe_order_headers_all ooha, oe_order_lines_all oola

WHERE ooha.header_id = oola.header_id

AND ooha.order_number = p_order_number;

l_header_rec.header_id := l_header_id; -- header_id of the order

l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;

l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl (l_line_tbl_index).operation := OE_GLOBALS.G_OPR_UPDATE;

l_line_tbl (l_line_tbl_index).split_by := 4096; -- user_id

l_line_tbl (l_line_tbl_index).split_action_code := 'SPLIT';

l_line_tbl (l_line_tbl_index).header_id := l_header_id; -- header_id of the order

l_line_tbl (l_line_tbl_index).line_id := l_line_id; -- line_id of the order line

l_line_tbl (l_line_tbl_index).ordered_quantity := 3; -- new ordered quantity

l_line_tbl (l_line_tbl_index).change_reason := 'MISC'; -- change reason code

l_line_tbl_index := 2;

l_line_tbl (l_line_tbl_index) := OE_ORDER_PUB.G_MISS_LINE_REC;

l_line_tbl (l_line_tbl_index).operation := OE_GLOBALS.G_OPR_CREATE;

l_line_tbl (l_line_tbl_index).split_by := 4096; -- user_id

l_line_tbl (l_line_tbl_index).split_action_code := 'SPLIT';

l_line_tbl (l_line_tbl_index).split_from_line_id := l_line_id; -- line_id of original line

l_line_tbl (l_line_tbl_index).inventory_item_id := 149; -- inventory item id(mtl_system_items_b.inventory_item_id)

l_line_tbl (l_line_tbl_index).ordered_quantity := 1; -- ordered quantity

-- CALL TO PROCESS ORDER

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

);

-- 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 ('Line Quantity Update Sucessful');

ELSE

DBMS_OUTPUT.put_line ('Line Quantity update Failed');

END IF;

END;

COMMIT;

More notes on Order Import
Order Import Part 1

Order Import Script
Order Import Script

Notes on Hold Release
Order Hold Realease