分享一下我老师大神的人工智能教程!零基础,通俗易懂!http://blog.csdn.net/jiangjunshow
也欢迎大家转载本篇文章。分享知识,造福人民,实现我们中华民族伟大复兴!
Creating Move Order
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, SUM (a.primary_transaction_quantity) total_onhand FROM mtl_onhand_quantities_detail a, mtl_system_items_kfv b, org_organization_definitions c WHERE a.inventory_item_id = b.inventory_item_id AND a.organization_id = b.organization_id AND a.organization_id = c.organization_id AND b.concatenated_segments = 'AS54888' AND c.organization_code = 'M1' GROUP BY a.organization_id, c.operating_unit, a.inventory_item_id, b.concatenated_segments, a.subinventory_code;BEGIN SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG'; SELECT fr.responsibility_id, fr.application_id INTO l_resp_id, l_appl_id FROM fnd_responsibility fr, fnd_responsibility_tl frt WHERE fr.responsibility_id = frt.responsibility_id AND frt.responsibility_name = 'Manufacturing and Distribution Manager'; --Responsibility fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id); DBMS_OUTPUT.put_line ('Creating MO'); 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 = '01-520-5250-0000-000'; 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; --Type:Move 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 := 'PTIAN Move Order Test'; 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 := 11; 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 ('==================================='); DBMS_OUTPUT.put_line ('Calling INV_MOVE_ORDER_PUB to Create MO'); 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 :=> '||x_hdr_rec.request_number); DBMS_OUTPUT.put_line ('==================================='); ELSE ROLLBACK; DBMS_OUTPUT.put_line ('Move Order Creation Failed Due to Following Reasons'); DBMS_OUTPUT.put_line ('==================================='); 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;
Allocating Move Order
DECLARE l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2 (2) := fnd_api.g_true; l_return_values VARCHAR2 (2) := fnd_api.g_false; l_commit VARCHAR2 (2) := fnd_api.g_false; x_return_status VARCHAR2 (2); x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (255); l_user_id NUMBER ; l_resp_id NUMBER ; l_appl_id NUMBER ; l_row_cnt NUMBER := 1; l_trohdr_rec inv_move_order_pub.trohdr_rec_type; l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type; x_trohdr_rec inv_move_order_pub.trohdr_rec_type; x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type; l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes; l_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type; x_trolin_tbl inv_move_order_pub.trolin_tbl_type; x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type; x_number_of_rows NUMBER ; x_transfer_to_location NUMBER ; x_expiration_date DATE; x_transaction_temp_id NUMBER ; CURSOR c_mo_details IS SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type, mtrh.organization_id, mtrl.line_id, mtrl.line_number, mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity, revision, mtrl.from_locator_id, (SELECT DISTINCT operating_unit FROM org_organization_definitions WHERE organization_id = mtrh.organization_id ) org_id FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl WHERE mtrh.header_id = mtrl.header_id AND mtrh.request_number = '4076052' AND mtrh.organization_id = 207;BEGIN SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG'; SELECT responsibility_id, application_id INTO l_resp_id, l_appl_id FROM fnd_responsibility_vl WHERE responsibility_name = 'Manufacturing and Distribution Manager'; fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id); FOR i IN c_mo_details LOOP mo_global.set_policy_context ('S', i.org_id); inv_globals.set_org_id (i.organization_id); mo_global.init ('INV'); SELECT COUNT (*) INTO x_number_of_rows FROM mtl_txn_request_lines WHERE header_id = i.header_id; DBMS_OUTPUT.put_line ('=========================================================='); DBMS_OUTPUT.put_line ('Calling INV_REPLENISH_DETAIL_PUB to Allocate MO'); -- Allocate each line of the Move Order inv_replenish_detail_pub.line_details_pub (p_line_id => i.line_id, x_number_of_rows => x_number_of_rows, x_detailed_qty => i.quantity, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, x_revision => i.revision, x_locator_id => i.from_locator_id, x_transfer_to_location => x_transfer_to_location, x_lot_number => i.lot_number, x_expiration_date => x_expiration_date, x_transaction_temp_id => x_transaction_temp_id, p_transaction_header_id => NULL, p_transaction_mode => NULL, p_move_order_type => i.move_order_type, p_serial_flag => fnd_api.g_false, p_plan_tasks => FALSE, p_auto_pick_confirm => FALSE, p_commit => FALSE ); DBMS_OUTPUT.put_line ('return status:'||x_return_status); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line (x_msg_count); IF (x_return_status <> fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line (x_msg_data); END IF; IF (x_return_status = fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line ('Trx temp ID: '||x_transaction_temp_id); END IF; DBMS_OUTPUT.put_line ('=========================================================='); END LOOP;EXCEPTIONWHEN OTHERS THEN DBMS_OUTPUT.put_line ('Exception Occured :'); DBMS_OUTPUT.put_line ('SQLCODE :'|| SQLERRM); DBMS_OUTPUT.put_line ('=======================================================');END;
Transacting Move Order
DECLARE l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2 (2) := fnd_api.g_true; l_commit VARCHAR2 (2) := fnd_api.g_false; x_return_status VARCHAR2 (2); x_msg_count NUMBER := 0; x_msg_data VARCHAR2 (255); l_move_order_type NUMBER := 1; l_transaction_mode NUMBER := 1; l_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type; x_trolin_tbl inv_move_order_pub.trolin_tbl_type; l_transaction_date DATE := SYSDATE; l_user_id NUMBER; l_resp_id NUMBER; l_appl_id NUMBER; CURSOR c_mo_details IS SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type, mtrh.organization_id, mtrl.line_id, mtrl.line_number, mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity, revision, mtrl.from_locator_id, (SELECT DISTINCT operating_unit FROM org_organization_definitions WHERE organization_id = mtrh.organization_id ) org_id FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl WHERE mtrh.header_id = mtrl.header_id AND mtrh.request_number = '4076052' AND mtrh.organization_id = 207;BEGIN SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = 'MFG'; SELECT responsibility_id, application_id INTO l_resp_id, l_appl_id FROM fnd_responsibility_vl WHERE responsibility_name = 'Manufacturing and Distribution Manager'; fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id); FOR i IN c_mo_details LOOP mo_global.set_policy_context ('S', i.org_id); inv_globals.set_org_id (i.organization_id); mo_global.init ('INV'); l_trolin_tbl (1).line_id := i.line_id; -- call API to create move order header DBMS_OUTPUT.put_line ('======================================================='); DBMS_OUTPUT.put_line ('Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API'); inv_pick_wave_pick_confirm_pub.pick_confirm (p_api_version_number => l_api_version, p_init_msg_list => l_init_msg_list, p_commit => l_commit, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data, p_move_order_type => i.move_order_type, p_transaction_mode => l_transaction_mode, p_trolin_tbl => l_trolin_tbl, p_mold_tbl => l_mold_tbl, x_mmtt_tbl => x_mmtt_tbl, x_trolin_tbl => x_trolin_tbl, p_transaction_date => l_transaction_date ); DBMS_OUTPUT.put_line ('======================================================='); DBMS_OUTPUT.put_line ('Return Status:'||x_return_status); DBMS_OUTPUT.put_line (x_msg_data); DBMS_OUTPUT.put_line (x_msg_count); IF (x_return_status <> fnd_api.g_ret_sts_success) THEN DBMS_OUTPUT.put_line (x_msg_data); END IF; DBMS_OUTPUT.put_line ('======================================================='); END LOOP;EXCEPTIONWHEN OTHERS THEN DBMS_OUTPUT.put_line ('Exception Occured :'); DBMS_OUTPUT.put_line ('SQLCODE :'|| SQLERRM); DBMS_OUTPUT.put_line ('=======================================================');END;
Reference Note:
Note 729998.1:Oracle Inventory Management Application Program Interface (APIs)
Note 729265.1:How To Process Move Orders Using INV_MOVE_ORDER_PUB.
Note 729513.1 How to use APIs: This demonstration walks the user through the API specifics, creating the PL/SQL procedure for using an API and also debugging guidelines.
Note 729261.1 How To Create A Move Order Header Using INV_MOVE_ORDER_PUB.Create_Move_Order_Header API
Note 729263.1 How To Create Move Order Lines Using INV_MOVE_ORDER_PUB.Create_Move_Order_Lines API
Note 729265.1 How To Process Move Orders Using INV_MOVE_ORDER_PUB.Process_Move_Order API