Saturday, November 28, 2009

Creating Move Order Using INV_MOVE_ORDER_PUB




Move Order:




Move orders are requests for the movement of material within a single organization. They allow planners and facility managers to request the movement of material within a warehouse or facility for purposes like replenishment, material storage relocations, and quality handling. We can generate move orders either manually or automatically depending on the source type we use. Move orders are restricted to transactions within an organization.


Move Order source types


There are three Move Order source types:



A. Move Order Requisition
Generated by:


* Manually generated request inside Inventory


The requisition is a manually generated request for a Move Order. May optionally utilize the approval processing - the requisition can optionally go through an Oracle Workflow approval process before it becomes a Move Order. If no approval process is used, the requisition becomes a move order immediately.


B. Replenishment Move Order




Generated by:


* Kanban Cards when pull sequence calls for subinventory transfer (intra-org Kanbans)


* Min Max Planning Report planned at the Subinventory Level


* Replenishment Counts


Replenishment Move Orders are pre-approved and ready to be transacted.


C. Wave Pick Move Order




Generated by:


* Sales Order picking


* Internal Sales Order picking


The Order Management pick release process generates move orders to bring material from its source location in Inventory to a staging location, which is defined as a subinventory. In other words, a subinventory transfer. Pick Wave Move Orders are pre-approved and ready to transact. Pick slips can also be generated for these Move Orders from Inventory with the release of the Oracle Order Management module.




Tables used to store the details about move order process




1) MTL_TXN_REQUEST_HEADERS: Move order headers, this stores the move order number in column (REQUEST_NUMBER). It has a status, but this is not used as much as the lines status to drive functionality.
2) MTL_TXN_REQUEST_LINES: Move order lines, this is the one that drives most queries and status checks for the move order as each line can be transacted individually.
3) MTL_MATERIAL_TRANSACTIONS_TEMP: Pending material transactions table also called the transaction temporary table, this holds allocations that act like reservations on inventory. An allocation is where you pick a specific item in inventory down to the lot, locator, serial, revision to move, but you do not actually perform the move yet.
4) MTL_MATERIAL_TRANSACTIONS : Finally the Material Transactions will get into this table.




R12 - Sample Script to Create Move Order Using INV_MOVE_ORDER_PUB.





DECLARE



x_return_status VARCHAR2 (1);
x_msg_data VARCHAR2 (4000);
x_msg_count NUMBER;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_hdr_rec inv_move_order_pub.trohdr_rec_type:= inv_move_order_pub.g_miss_trohdr_rec;
x_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_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;
l_code_combination_id gl_code_combinations.code_combination_id%type;


CURSOR c_itm_onhand IS


SELECT a.organization_id, c.operating_unit org_id,
a.inventory_item_id,b.concatenated_segments,
a.subinventory_code,a.lot_number,
SUM (a.primary_transaction_quantity) total_onhand
FROM mtl_onhand_quantities_detail a,
mtl_system_items_kfv b,
org_organization_definitions c,
mtl_lot_numbers d
WHERE a.inventory_item_id = b.inventory_item_id
AND a.lot_number = d.lot_number
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = b.organization_id
AND a.organization_id = c.organization_id
AND a.organization_id = d.organization_id
AND b.concatenated_segments = 'RC0805FR-0768KL^YAGEO'
AND a.lot_number = 'A6680983'
AND c.organization_code = 'A66'
GROUP BY a.organization_id,c.operating_unit,
a.inventory_item_id, b.concatenated_segments,
a.subinventory_code, a.lot_number;


BEGIN


SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = 'A42485';


SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = 'Inventory';


fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);


FOR i IN c_itm_onhand LOOP



mo_global.set_policy_context ('S', i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init ('INV');


select code_combination_id
into l_code_combination_id
from gl_code_combinations_kfv
where concatenated_segments = '6420-00000-0000-999999-91000-0000-00000-0-0';


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 := i.organization_id;
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 One';
l_hdr_rec.to_account_id := l_code_combination_id;
l_hdr_rec.from_subinventory_code := i.subinventory_code;
l_line_tbl (1).date_required := SYSDATE;
l_line_tbl (1).inventory_item_id := i.inventory_item_id;
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 := i.organization_id;
l_line_tbl (1).quantity := 5000;
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 := i.subinventory_code;
l_line_tbl (1).to_account_id := l_code_combination_id;
l_line_tbl (1).lot_number := i.lot_number;


DBMS_OUTPUT.put_line ('Calling INV_MOVE_ORDER_PUB to Create MO');
DBMS_OUTPUT.put_line ('===================================');


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 (x_return_status);
DBMS_OUTPUT.put_line (x_msg_count);


IF x_return_status = 'S'
THEN
COMMIT;


DBMS_OUTPUT.put_line ('Move Order Successfully Created');
DBMS_OUTPUT.put_line ('Move Order Number is :=> ');
DBMS_OUTPUT.put_line (x_hdr_rec.request_number);


ELSE
ROLLBACK;
DBMS_OUTPUT.put_line ('Move Order Creation Failed Due to Following Reasons');
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);


END LOOP;
END IF;
END LOOP;


END;




Sample DBMS Output: -



Calling INV_MOVE_ORDER_PUB to Create MO
===================================
S
0
Move Order Successfully Created
Move Order Number is :=> 332557


6 comments:

  1. Hi,

    Can I use the API to create Move Order for Wip Issue (same as component Pick Release ) ?


    Erez

    ReplyDelete
  2. Hi
    I am new bee to oracle.thank you for sharing this post.
    Regards
    oracle fussion middleware

    ReplyDelete
  3. Hi,
    When I am runnung the same code, getting error
    Calling INV_MOVE_ORDER_PUB to Create MO
    ===================================
    E
    4
    Move Order Creation Failed Due to Following Reasons
    Lot number is not valid.
    Lot number is not valid.
    Lot number is not valid.
    Lot number is not valid.

    How to proceed ?

    ReplyDelete
  4. Hi All
    Firstly,
    create move order on the application ERP
    and to be get all parameters from on application that to created on the block line
    form help help>>>>Diagnostics>>>Exame
    block : Your block = TOMAI_MAIN_LINES_BLK
    Field : LOT_NUMBER
    Value : me null
    According business
    Thank you


    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Thank you very much for this post. i created MO successfully using this code.

    ReplyDelete