Create an RMA via Process Order API

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 VARIABLES 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 DEBUGINFO*************************************/

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 HEADERRECORD******************************/

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 REQUESTRECORD*************************************/

l_action_request_tbl(1) := OE_ORDER_PUB.G_MISS_REQUEST_REC;

/*****************INITIALIZE LINERECORD********************************

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;