Create an RMA order that references an existing order
DECLARE
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2(2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2(2000);
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************
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;
/*****************OUT VARI ABLE S FOR PROCESS_ORDER API***************************
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2(2000);
l_loop_count NUMBER;
l_debug_file VARCHAR2(200);
BEGIN
/*****************INITIALIZE DEBUG INFO *************************************/
l_debug_file := OE_DEBUG_PUB.Set_Debug_Mode('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel(5);
Oe_Msg_Pub.initialize;
/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize(user_id,responsibility_id ,application_id); -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := OE_ORDER_PUB.G_MISS_HEADER_REC;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := OE_GLOBALS.G_OPR_CREATE;
l_header_rec.order_type_id := ;
l_header_rec.sold_to_org_id := ;
l_header_rec.price_list_id := ;
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;
/*****************INITIALIZE LINE RECORD********************************
l_line_tbl(1) := OE_ORDER_PUB.G_MISS_LINE_REC;
/*****************CALLTO PROCESS ORDER API*********************************/
OE_Order_PUB.Process_Order( p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => l_line_tbl,
p_action_request_tbl => l_action_request_tbl,
-- OUT variables
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
/*****************CHECK RETURN STATUS***********************************/
if l_return_status = FND_API.G_RET_STS_SUCCESS then
dbms_output.put_line('success');
commit;
else
dbms_output.put_line('failure');
rollback;
end if;
/*****************DISPLAY RETURN STATUS FLAGS******************************
DBMS_OUTPUT.PUT_LINE ('process ORDER ret status IS: ' || l_return_status);
DBMS_OUTPUT.PUT_LINE ('process ORDER msg data IS: ' || l_msg_data);
DBMS_OUTPUT.PUT_LINE ('process ORDER msg COUNT IS: ' || l_msg_count);
DBMS_OUTPUT.PUT_LINE ('header.order_number IS: ' || to_char(l_header_rec_out.order_number));
DBMS_OUTPUT.PUT_LINE ('header.return_status IS: ' || l_header_rec_out.return_status);
DBMS_OUTPUT.PUT_LINE ('header.booked_flag IS: ' || l_header_rec_out.booked_flag);
DBMS_OUTPUT.PUT_LINE ('header.header_id IS: ' || l_header_rec_out.header_id);
DBMS_OUTPUT.PUT_LINE ('header.order_source_id IS: ' || l_header_rec_out.order_source_id);
DBMS_OUTPUT.PUT_LINE ('header.flow_status_code IS: ' || l_header_rec_out.flow_status_code);
/*****************DISPLAY ERROR MSGS*************************************/
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_data
,p_msg_index_out => l_msg_index);
DBMS_OUTPUT.PUT_LINE ('message is: ' || l_data);
DBMS_OUTPUT.PUT_LINE ('message index is: ' || l_msg_index);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Debug = ' || OE_DEBUG_PUB.G_DEBUG);
DBMS_OUTPUT.PUT_LINE ('Debug Level = ' || to_char(OE_DEBUG_PUB.G_DEBUG_LEVEL));
DBMS_OUTPUT.PUT_LINE ('Debug File = ' || OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);
DBMS_OUTPUT.PUT_LINE ('****************************************************');
OE_DEBUG_PUB.DEBUG_OFF;
END; To create an RMA order that references an existing order:
Please use the script below to insert the header and lines by populating the order import interface
INSERT INTO OE_HEADERS_IFACE_ALL (
order_source_id
,orig_sys_document_ref
,creation_date
,created_by
,last_update_date
,last_updated_by
,operation_code
,sold_to_org_id
,order_type_id
,booked_flag
)
VALUES (
1227 --order_source_id
,'123456' --orig_sys_document_ref
,sysdate --creation_date
,-1 --created_by
,sysdate --last_update_date
,-1 --last_updated_by
,'INSERT' --operation_code
,1005 --sold_to_org_id
,1436 --order_type_id
,'Y' --booked_flag
);
INSERT INTO OE_LINES_IFACE_ALL (
order_source_id
,orig_sys_document_ref
,orig_sys_line_ref
,inventory_item_id
,ordered_quantity
,operation_code
,created_by
,creation_date
,last_updated_by
,last_update_date
,return_reason_code
,return_context
,return_attribute1
,return_attribute2
)
VALUES (
1227 --order_source_id
,'123456' --orig_sys_document_ref
,'1' --orig_sys_line_ref
,249 --inventory_item_id
,10 --ordered_quantity
,'INSERT' --operation_code
,-1 --created_by
,sysdate --creation_date
,-1 --last_updated_by
,sysdate --last_update_date
,'CANCELLATION' --return_reason_code
,'ORDER' --referencing a sales order
,'157638' --header_id of referenced order
,'256619' --line_id of referenced line
);
commit;
