Move Orders in Oracle provide good functionality for approval based
movement of material with in an inventory organization. Move Order
Approvals use a workflow (INV: Move Order Approval) for approvals and
hence provide flexibility to extend it for approvals.
Move Orders can be created from different sources. These move orders are
identified by Move order type at the header level. Different types of
move orders are:
Move Order Requisitions: These are created from the
move order form. Based on the setup at the organization and item level,
move orders require approval before they can be allocated and
transacted. These requisitions can be created to charge an account or
move the material from one subinventory to another.
Replenishment Move Orders: These are auto-created with
pre approved status. Depending on the planning type you can replenish
the quantity in a subinventory for an item. Three types of replenishment
plans (in relation to move orders) are available:
- Min-max Replenishment Planning
- Kanban Replenishment Planning
- Replenishing Count planning
Based on these sources, when appropriate programs are run, move orders are automatically created.
Pick Wave Move Order Requisitions: These move orders
are specifically for the sales order picking process. When Pick
Releasing program is run move orders are created as preapproved and
allocated. Pick Confirm process transacts these pick wave move orders.
Move orders contain headers and lines. All types of approved move orders
have to be allocated (basically reserved) before they can be
transacted. Depending on the item transaction controls (Subinventory,
Locator, Revision, Lot or Serial number) move order creation and
allocation gets complex.
The process is simple. Create and approve Move orders, allocate move order lines and transact them.
In this article let us review creating, allocating, and transacting a
move order requisition of first type (requisitions) using APIs. These
move orders will be created in pre approved status and hence do not use
workflow. Move Order Issue transaction type is used in this example. An
account is chosen while creating this move order. When successfully
transacted, this account gets debited, crediting inventory account of
the subinventory. From the user interface, to create Move orders, Move
Orders window is used.
The lines are allocated when allocation step is performed. In this
example the logic for allocation and transaction is borrowed from pick
confirmation code. To allocate and transact same form is used: Transact
Move Orders.
If the item is lot and serial number controlled (as in this example),
you have to pass the lot number and serial number information into the
move order lines table parameter. And also make sure to pass another
parameter (p_suggess_serial) as true. This will automatically take care
of allocating the lot and serial numbers into the appropriate table as
mentioned in the process flow. Also when we allocate the move order from
the user interface, the records in the form are created in the
mtl_material_transactions_temp table with transaction_status as 2, which
allows the user to change the values from user interface. But in order
to perform transaction from SQL, you have to change this value to 3 so
that the transaction manager can pick these records up. You find that
logic in the allocating code.
The tables affected in each process step are mentioned in the process
flow. Code for each step as well as complete code to create, allocate
and transact can be downloaded as seen here.
Create Move Order:-
DECLARE
PROCEDURE create_move_order
IS
l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
l_line_tbl inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_line_tbl inv_move_order_pub.trolin_tbl_type;
x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;
v_msg_index_out NUMBER;
l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;
BEGIN
l_line_tbl.DELETE;
x_line_tbl.DELETE;
mo_global.set_policy_context ('S', 204);
inv_globals.set_org_id (207);
fnd_global.apps_initialize (1005902, 50583, 401);
l_hdr_rec.date_required := SYSDATE;
l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
l_hdr_rec.organization_id := 207;
l_hdr_rec.status_date := SYSDATE;
l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
l_hdr_rec.db_flag := fnd_api.g_true;
l_hdr_rec.operation := inv_globals.g_opr_create;
l_hdr_rec.description := 'Test Move Order';
l_hdr_rec.to_account_id := 12831;
l_hdr_rec.from_subinventory_code := 'FGI';
l_line_tbl (1).date_required := SYSDATE;
l_line_tbl (1).inventory_item_id := 167742;
l_line_tbl (1).line_id := fnd_api.g_miss_num;
l_line_tbl (1).line_number := 1;
l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
l_line_tbl (1).transaction_type_id :=
inv_globals.g_type_transfer_order_issue;
l_line_tbl (1).organization_id := 207;
l_line_tbl (1).quantity := 1;
l_line_tbl (1).status_date := SYSDATE;
l_line_tbl (1).uom_code := 'Ea';
l_line_tbl (1).db_flag := fnd_api.g_true;
l_line_tbl (1).operation := inv_globals.g_opr_create;
l_line_tbl (1).from_subinventory_code := 'FGI';
l_line_tbl (1).to_account_id := 12831;
l_line_tbl (1).lot_number := 'A01'; --If the item is lot controlled
l_line_tbl (1).serial_number_start := 'A01-0031'; --If the item is serial number controlled
l_line_tbl (1).serial_number_end := 'A01-0031'; --If the item is serial number controlled
inv_move_order_pub.process_move_order (
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trohdr_rec => l_hdr_rec,
p_trolin_tbl => l_line_tbl,
x_trohdr_rec => x_hdr_rec,
x_trohdr_val_rec => x_hdr_val_rec,
x_trolin_tbl => x_line_tbl,
x_trolin_val_tbl => x_line_val_tbl
);
DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count);
DBMS_OUTPUT.put_line (
'Move Order Number is :' || x_hdr_rec.request_number
);
DBMS_OUTPUT.put_line ('Number of Lines Created are :' || x_line_tbl.COUNT);
IF x_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (
'============================================================'
);
END LOOP;
END IF;
END;
PROCEDURE create_move_order
IS
l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
l_line_tbl inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
x_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec;
x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_line_tbl inv_move_order_pub.trolin_tbl_type;
x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type;
v_msg_index_out NUMBER;
l_rsr_type inv_reservation_global.mtl_reservation_tbl_type;
BEGIN
l_line_tbl.DELETE;
x_line_tbl.DELETE;
mo_global.set_policy_context ('S', 204);
inv_globals.set_org_id (207);
fnd_global.apps_initialize (1005902, 50583, 401);
l_hdr_rec.date_required := SYSDATE;
l_hdr_rec.header_status := inv_globals.g_to_status_preapproved;
l_hdr_rec.organization_id := 207;
l_hdr_rec.status_date := SYSDATE;
l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue;
l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition;
l_hdr_rec.db_flag := fnd_api.g_true;
l_hdr_rec.operation := inv_globals.g_opr_create;
l_hdr_rec.description := 'Test Move Order';
l_hdr_rec.to_account_id := 12831;
l_hdr_rec.from_subinventory_code := 'FGI';
l_line_tbl (1).date_required := SYSDATE;
l_line_tbl (1).inventory_item_id := 167742;
l_line_tbl (1).line_id := fnd_api.g_miss_num;
l_line_tbl (1).line_number := 1;
l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved;
l_line_tbl (1).transaction_type_id :=
inv_globals.g_type_transfer_order_issue;
l_line_tbl (1).organization_id := 207;
l_line_tbl (1).quantity := 1;
l_line_tbl (1).status_date := SYSDATE;
l_line_tbl (1).uom_code := 'Ea';
l_line_tbl (1).db_flag := fnd_api.g_true;
l_line_tbl (1).operation := inv_globals.g_opr_create;
l_line_tbl (1).from_subinventory_code := 'FGI';
l_line_tbl (1).to_account_id := 12831;
l_line_tbl (1).lot_number := 'A01'; --If the item is lot controlled
l_line_tbl (1).serial_number_start := 'A01-0031'; --If the item is serial number controlled
l_line_tbl (1).serial_number_end := 'A01-0031'; --If the item is serial number controlled
inv_move_order_pub.process_move_order (
p_api_version_number => 1.0,
p_init_msg_list => fnd_api.g_false,
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_trohdr_rec => l_hdr_rec,
p_trolin_tbl => l_line_tbl,
x_trohdr_rec => x_hdr_rec,
x_trohdr_val_rec => x_hdr_val_rec,
x_trolin_tbl => x_line_tbl,
x_trolin_val_tbl => x_line_val_tbl
);
DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status);
DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count);
DBMS_OUTPUT.put_line (
'Move Order Number is :' || x_hdr_rec.request_number
);
DBMS_OUTPUT.put_line ('Number of Lines Created are :' || x_line_tbl.COUNT);
IF x_return_status = 'S'
THEN
COMMIT;
ELSE
ROLLBACK;
END IF;
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => v_msg_index_out);
x_msg_data := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (
'============================================================'
);
END LOOP;
END IF;
END;
BEGIN
create_move_order;
END;
Allocate Move Order:
/* Formatted on 6/28/2016 2:51:21 PM (QP5 v5.114.809.3010) */ DECLARE x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count NUMBER; l_line_tbl inv_move_order_pub.trolin_tbl_type; PROCEDURE allocate_move_order ( p_line_tbl IN inv_move_order_pub.trolin_tbl_type, x_return_status OUT VARCHAR2, x_msg_data OUT VARCHAR2, x_msg_count OUT NUMBER ) IS x_line_tbl inv_move_order_pub.trolin_tbl_type; l_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; l_qty_detailed NUMBER; l_qty_delivered NUMBER; l_return_status VARCHAR2 (1); v_msg_index_out NUMBER; l_rsr_type inv_reservation_global.mtl_reservation_tbl_type; i INTEGER; l_trolin_rec inv_move_order_pub.trolin_rec_type; BEGIN x_line_tbl := p_line_tbl; IF x_line_tbl.COUNT > 0 THEN FOR j IN x_line_tbl.FIRST .. x_line_tbl.LAST LOOP DBMS_OUTPUT.put_line (x_line_tbl (j).line_id); BEGIN inv_ppengine_pvt.create_suggestions ( p_api_version => 1.0, p_init_msg_list => fnd_api.g_false, p_commit => fnd_api.g_false, p_validation_level => fnd_api.g_valid_level_none, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_transaction_temp_id => x_line_tbl (j).line_id, p_reservations => l_rsr_type, p_suggest_serial => fnd_api.g_true, p_plan_tasks => FALSE, p_quick_pick_flag => 'N', p_organization_id => 207 ); DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status); DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count); IF x_return_status = 'S' THEN BEGIN l_trolin_tbl := x_line_tbl; IF (l_trolin_tbl.COUNT <> 0) THEN i := l_trolin_tbl.FIRST; WHILE i IS NOT NULL LOOP IF (l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_unexp_error AND l_trolin_tbl (i).return_status <> fnd_api.g_ret_sts_error) THEN l_trolin_rec := inv_trolin_util.query_row ( l_trolin_tbl (i).line_id ); l_trolin_tbl (i) := l_trolin_rec; l_qty_detailed := l_trolin_tbl (i).quantity_detailed; l_qty_delivered := NVL (l_trolin_tbl (i).quantity_delivered, 0); IF NVL (l_qty_detailed, 0) = 0 THEN l_mold_tbl := inv_mo_line_detail_util.query_rows ( p_line_id => l_trolin_tbl (i).line_id ); FOR j IN 1 .. l_mold_tbl.COUNT LOOP l_mold_tbl (j).transaction_status := 3; l_mold_tbl (j).transaction_mode := 1; l_mold_tbl (j).source_line_id := l_trolin_tbl (i).line_id; inv_mo_line_detail_util.update_row ( l_return_status, l_mold_tbl (j) ); END LOOP; SELECT transaction_header_id, transaction_quantity INTO l_trolin_tbl ( i ).transaction_header_id, l_trolin_tbl (i).quantity_detailed FROM mtl_material_transactions_temp WHERE move_order_line_id = l_trolin_tbl (i).line_id; l_trolin_tbl (i).last_update_date := SYSDATE; l_trolin_tbl (i).last_update_login := fnd_global.login_id; IF l_trolin_tbl (i).last_update_login = -1 THEN l_trolin_tbl (i).last_update_login := fnd_global.conc_login_id; END IF; l_trolin_tbl (i).last_updated_by := fnd_global.user_id; l_trolin_tbl (i).program_id := fnd_global.conc_program_id; l_trolin_tbl (i).program_update_date := SYSDATE; l_trolin_tbl (i).request_id := fnd_global.conc_request_id; l_trolin_tbl (i).program_application_id := fnd_global.prog_appl_id; inv_trolin_util.update_row ( l_trolin_tbl (i) ); END IF; END IF; i := l_trolin_tbl.NEXT (i); END LOOP; END IF; END; ELSE ROLLBACK; END IF; IF x_msg_count > 0 THEN FOR v_index IN 1 .. x_msg_count LOOP fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out); x_msg_data := SUBSTR (x_msg_data, 1, 200); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line('============================================================'); END LOOP; END IF; END; END LOOP; END IF; END; BEGIN l_line_tbl (1).line_id := 3929705; allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count); COMMIT; END;Transact Move Order:/* Formatted on 6/28/2016 2:52:14 PM (QP5 v5.114.809.3010) */ DECLARE x_return_status VARCHAR2 (1); PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2) IS l_header_id NUMBER; l_program VARCHAR2 (100); l_func VARCHAR2 (100); l_args VARCHAR2 (100); p_timeout NUMBER; l_old_tm_success BOOLEAN; l_rc_field NUMBER; CURSOR c1 (p_header_id IN NUMBER) IS SELECT transaction_header_id FROM mtl_material_transactions_temp WHERE transaction_source_id = p_header_id; BEGIN mo_global.set_policy_context ('S', 204); inv_globals.set_org_id (207); fnd_global.apps_initialize (1005902, 50583, 401); FOR i IN c1 (p_move_order_id) LOOP l_program := 'INXTPU'; l_func := l_program; l_args := l_program || ' ' || 'TRANS_HEADER_ID=' || TO_CHAR (i.transaction_header_id); p_timeout := 500; COMMIT; l_old_tm_success := inv_pick_wave_pick_confirm_pub.inv_tm_launch ( program => l_program, args => l_args, TIMEOUT => p_timeout, rtval => l_rc_field ); IF l_old_tm_success THEN x_return_status := 'S'; DBMS_OUTPUT.put_line ('Result is :' || 'Success'); ELSE x_return_status := 'E'; DBMS_OUTPUT.put_line ('Result is :' || 'Failed'); END IF; IF x_return_status = 'S' THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END; BEGIN transact_move_order (2055447, x_return_status); COMMIT; END;Move Order Composite:DECLARE l_header_rec inv_move_order_pub.trohdr_rec_type; l_line_tbl inv_move_order_pub.trolin_tbl_type; x_return_status VARCHAR2 (1); x_msg_data VARCHAR2 (4000); x_msg_count NUMBER; PROCEDURE create_move_order ( x_hdr_rec OUT inv_move_order_pub.trohdr_rec_type , x_line_tbl OUT inv_move_order_pub.trolin_tbl_type , x_return_status OUT VARCHAR2 , x_msg_data OUT VARCHAR2 , x_msg_count OUT NUMBER ) IS l_hdr_rec inv_move_order_pub.trohdr_rec_type := inv_move_order_pub.g_miss_trohdr_rec; l_line_tbl inv_move_order_pub.trolin_tbl_type := inv_move_order_pub.g_miss_trolin_tbl; x_hdr_val_rec inv_move_order_pub.trohdr_val_rec_type; x_line_val_tbl inv_move_order_pub.trolin_val_tbl_type; v_msg_index_out NUMBER; l_rsr_type inv_reservation_global.mtl_reservation_tbl_type; BEGIN l_line_tbl.DELETE; x_line_tbl.DELETE; l_hdr_rec.date_required := SYSDATE; l_hdr_rec.header_status := inv_globals.g_to_status_preapproved; l_hdr_rec.organization_id := 207; l_hdr_rec.status_date := SYSDATE; l_hdr_rec.transaction_type_id := inv_globals.g_type_transfer_order_issue ; l_hdr_rec.move_order_type := inv_globals.g_move_order_requisition; l_hdr_rec.db_flag := fnd_api.g_true; l_hdr_rec.operation := inv_globals.g_opr_create; l_hdr_rec.description := 'Test Move Order'; l_hdr_rec.to_account_id := 12831; l_hdr_rec.from_subinventory_code := 'FGI'; l_line_tbl (1).date_required := SYSDATE; l_line_tbl (1).inventory_item_id := 167742; l_line_tbl (1).line_id := fnd_api.g_miss_num; l_line_tbl (1).line_number := 1; l_line_tbl (1).line_status := inv_globals.g_to_status_preapproved; l_line_tbl (1).transaction_type_id := inv_globals. g_type_transfer_order_issue; l_line_tbl (1).organization_id := 207; l_line_tbl (1).quantity := 1; l_line_tbl (1).status_date := SYSDATE; l_line_tbl (1).uom_code := 'Ea'; l_line_tbl (1).db_flag := fnd_api.g_true; l_line_tbl (1).operation := inv_globals.g_opr_create; l_line_tbl (1).from_subinventory_code := 'FGI'; l_line_tbl (1).to_account_id := 12831; l_line_tbl (1).lot_number := 'A01'; --If the item is lot controlled l_line_tbl (1).serial_number_start := 'A01-0039'; --If the item is serial number controlled l_line_tbl (1).serial_number_end := 'A01-0039'; --If the item is serial number controlled inv_move_order_pub.process_move_order (p_api_version_number => 1.0 , p_init_msg_list => fnd_api.g_false , p_return_values => fnd_api.g_false , p_commit => fnd_api.g_false , x_return_status => x_return_status , x_msg_count => x_msg_count , x_msg_data => x_msg_data , p_trohdr_rec => l_hdr_rec , p_trolin_tbl => l_line_tbl , x_trohdr_rec => x_hdr_rec , x_trohdr_val_rec => x_hdr_val_rec , x_trolin_tbl => x_line_tbl , x_trolin_val_tbl => x_line_val_tbl ); DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status); DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count); DBMS_OUTPUT.put_line ('Move Order Number is :' || x_hdr_rec. request_number); DBMS_OUTPUT.put_line ('Move Order ID is :' || x_hdr_rec.header_id); DBMS_OUTPUT.put_line ('Number of Lines Created are :' || x_line_tbl. COUNT); IF x_return_status = 'S' THEN COMMIT; ELSE ROLLBACK; END IF; IF x_msg_count > 0 THEN FOR v_index IN 1 .. x_msg_count LOOP fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out); x_msg_data := SUBSTR (x_msg_data, 1, 200); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line ( '============================================================'); END LOOP; END IF; END;***********************************************************************PROCEDURE allocate_move_order ( p_line_tbl IN inv_move_order_pub.trolin_tbl_type , x_return_status OUT VARCHAR2 , x_msg_data OUT VARCHAR2 , x_msg_count OUT NUMBER ) IS x_line_tbl inv_move_order_pub.trolin_tbl_type; l_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; l_qty_detailed NUMBER; l_qty_delivered NUMBER; l_return_status VARCHAR2 (1); v_msg_index_out NUMBER; l_rsr_type inv_reservation_global.mtl_reservation_tbl_type; i INTEGER; l_trolin_rec inv_move_order_pub.trolin_rec_type; BEGIN x_line_tbl := p_line_tbl; IF x_line_tbl.COUNT > 0 THEN FOR j IN x_line_tbl.FIRST .. x_line_tbl.LAST LOOP DBMS_OUTPUT.put_line (x_line_tbl (j).line_id); BEGIN inv_ppengine_pvt.create_suggestions (p_api_version => 1.0 , p_init_msg_list => fnd_api.g_false , p_commit => fnd_api.g_false , p_validation_level => fnd_api.g_valid_level_none , x_return_status => x_return_status , x_msg_count => x_msg_count , x_msg_data => x_msg_data , p_transaction_temp_id => x_line_tbl (j).line_id , p_reservations => l_rsr_type , p_suggest_serial => fnd_api.g_true , p_plan_tasks => FALSE , p_quick_pick_flag => 'N' , p_organization_id => 207 ); DBMS_OUTPUT.put_line ('Return Status is :' || x_return_status); DBMS_OUTPUT.put_line ('Message Count is :' || x_msg_count); IF x_return_status = 'S' THEN BEGIN l_trolin_tbl := x_line_tbl; IF (l_trolin_tbl.COUNT <> 0) THEN i := l_trolin_tbl.FIRST; WHILE i IS NOT NULL LOOP IF ( l_trolin_tbl (i).return_status <> fnd_api. g_ret_sts_unexp_error AND l_trolin_tbl (i).return_status <> fnd_api. g_ret_sts_error ) THEN l_trolin_rec := inv_trolin_util.query_row ( l_trolin_tbl (i).line_id); l_trolin_tbl (i) := l_trolin_rec; l_qty_detailed := l_trolin_tbl (i). quantity_detailed; l_qty_delivered := NVL (l_trolin_tbl (i). quantity_delivered, 0); IF NVL (l_qty_detailed, 0) = 0 THEN l_mold_tbl := inv_mo_line_detail_util. query_rows (p_line_id => l_trolin_tbl (i).line_id); FOR j IN 1 .. l_mold_tbl.COUNT LOOP l_mold_tbl (j).transaction_status := 3; l_mold_tbl (j).transaction_mode := 1; l_mold_tbl (j).source_line_id := l_trolin_tbl (i).line_id; inv_mo_line_detail_util.update_row ( l_return_status, l_mold_tbl (j)); END LOOP; SELECT transaction_header_id, transaction_quantity INTO l_trolin_tbl (i).transaction_header_id , l_trolin_tbl (i).quantity_detailed FROM mtl_material_transactions_temp WHERE move_order_line_id = l_trolin_tbl (i). line_id; l_trolin_tbl (i).last_update_date := SYSDATE; l_trolin_tbl (i).last_update_login := fnd_global.login_id; IF l_trolin_tbl (i).last_update_login = -1 THEN l_trolin_tbl (i).last_update_login := fnd_global.conc_login_id; END IF; l_trolin_tbl (i).last_updated_by := fnd_global.user_id; l_trolin_tbl (i).program_id := fnd_global. conc_program_id; l_trolin_tbl (i).program_update_date := SYSDATE; l_trolin_tbl (i).request_id := fnd_global. conc_request_id; l_trolin_tbl (i).program_application_id := fnd_global.prog_appl_id; inv_trolin_util.update_row (l_trolin_tbl (i)) ; END IF; END IF; i := l_trolin_tbl.NEXT (i); END LOOP; END IF; END; ELSE ROLLBACK; END IF; IF x_msg_count > 0 THEN FOR v_index IN 1 .. x_msg_count LOOP fnd_msg_pub.get (p_msg_index => v_index , p_encoded => 'F' , p_data => x_msg_data , p_msg_index_out => v_msg_index_out ); x_msg_data := SUBSTR (x_msg_data, 1, 200); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line ( '============================================================'); END LOOP; END IF; END; END LOOP; END IF; END;*********************************************************************************/* Formatted on 6/28/2016 2:54:58 PM (QP5 v5.114.809.3010) */ PROCEDURE transact_move_order (p_move_order_id IN NUMBER, x_return_status OUT VARCHAR2) IS l_header_id NUMBER; l_program VARCHAR2 (100); l_func VARCHAR2 (100); l_args VARCHAR2 (100); p_timeout NUMBER; l_old_tm_success BOOLEAN; l_rc_field NUMBER; CURSOR c1 (p_header_id IN NUMBER) IS SELECT transaction_header_id FROM mtl_material_transactions_temp WHERE transaction_source_id = p_header_id; BEGIN FOR i IN c1 (p_move_order_id) LOOP l_program := 'INXTPU'; l_func := l_program; l_args := l_program || ' ' || 'TRANS_HEADER_ID=' || TO_CHAR (i.transaction_header_id); p_timeout := 500; COMMIT; l_old_tm_success := inv_pick_wave_pick_confirm_pub.inv_tm_launch ( program => l_program, args => l_args, TIMEOUT => p_timeout, rtval => l_rc_field ); IF l_old_tm_success THEN x_return_status := 'S'; DBMS_OUTPUT.put_line ('Result is :' || 'Success'); ELSE x_return_status := 'E'; DBMS_OUTPUT.put_line ('Result is :' || 'Failed'); END IF; IF x_return_status = 'S' THEN COMMIT; ELSE ROLLBACK; END IF; END LOOP; END; BEGIN mo_global.set_policy_context ('S', 204); inv_globals.set_org_id (207); fnd_global.apps_initialize (1005902, 50583, 401); DBMS_OUTPUT.put_line ('Creating Move Order'); create_move_order (l_header_rec, l_line_tbl, x_return_status, x_msg_data, x_msg_count); IF x_return_status = 'S' THEN COMMIT; DBMS_OUTPUT.put_line ('Move Order Created'); IF l_line_tbl.COUNT > 0 THEN x_return_status := NULL; x_msg_data := NULL; x_msg_count := NULL; DBMS_OUTPUT.put_line ('Allocating Move Order'); allocate_move_order (l_line_tbl, x_return_status, x_msg_data, x_msg_count); IF x_return_status = 'S' THEN COMMIT; DBMS_OUTPUT.put_line ('Move Order Allocated'); x_return_status := NULL; DBMS_OUTPUT.put_line ('Transacting Move Order'); DBMS_OUTPUT.put_line ( 'l_header_rec.header_id :' || l_header_rec.header_id ); transact_move_order (l_header_rec.header_id, x_return_status); IF x_return_status = 'S' THEN COMMIT; DBMS_OUTPUT.put_line ('Move Order Transacted'); END IF; END IF; END IF; END IF; END;
