您好,登錄后才能下訂單哦!
這篇文章主要講解了“API怎么實(shí)現(xiàn)批次序列號(hào)的銷(xiāo)售出庫(kù)”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“API怎么實(shí)現(xiàn)批次序列號(hào)的銷(xiāo)售出庫(kù)”吧!
隨著制造企業(yè)對(duì)生產(chǎn)銷(xiāo)售的控制越來(lái)越高,越來(lái)越多的制造型企業(yè)增加了對(duì)物料的批次、序列號(hào)的控制。我們可以在庫(kù)存職責(zé)中的:物料 –> 主組織物料/組織產(chǎn)品 -> 庫(kù)存TAB頁(yè):來(lái)這只某個(gè)庫(kù)存組織中是否啟用序列或者是批次。
啟用物料的批次、序列控制后,該無(wú)聊在采購(gòu)入庫(kù)、銷(xiāo)售出庫(kù)都需要填寫(xiě)相應(yīng)有效批次序列。
在Ebs中我們經(jīng)常會(huì)碰到要實(shí)現(xiàn)自動(dòng)化的公司間事務(wù)處理,比如自動(dòng)銷(xiāo)售出庫(kù),自動(dòng)的采購(gòu)入庫(kù)。本文就將講解如何實(shí)現(xiàn)帶序列號(hào)、批次控制的銷(xiāo)售出庫(kù)。
在Ebs的界面上手工的進(jìn)行銷(xiāo)售出庫(kù),我們是去修改物料搬運(yùn)單的屬性,物料搬運(yùn)單 -> 處理物料搬運(yùn)單 -> 處理物料搬運(yùn)單分配。如果啟用序列、批次號(hào)控制,“序列/批次”按鈕將會(huì)亮起來(lái)。
既然實(shí)在處理物料搬運(yùn)單時(shí)修改的批次、序列,我們首先就會(huì)想到物料搬運(yùn)單的API來(lái)實(shí)現(xiàn)這個(gè)功能:(挑庫(kù)確認(rèn)之前)可使用的API有:
? INV_MOVE_ORDER_PUB.Process_Move_Order
? INV_MOVE_ORDER_PUB.Process_Move_Order_Line
該API中的p_trolin_tbl(i).lot_number、p_trolin_tbl(i).serial_number_start、p_trolin_tbl(i).serial_number_end是允許設(shè)置和修改。但是讓人失望的是,雖然修改了但是挑庫(kù)發(fā)運(yùn)卻看不出任何效果,發(fā)出去的物料根本就不是我們指定的批次和序列。
這是因?yàn)椋瑤?kù)存事務(wù)處理是根據(jù)下面三個(gè)表中的數(shù)據(jù)進(jìn)行處理的,而不是根據(jù)物料搬運(yùn)單行。
? MTL_MATERIAL_TRANSACTIONS_TEMP (庫(kù)存事務(wù)處理臨時(shí)表)
? MTL_TRANSACTION_LOTS_TEMP (批次臨時(shí)表)
? MTL_SERIAL_NUMBERS_TEMP (序列號(hào)臨時(shí)表)
然而這三個(gè)表 Oracle 并沒(méi)有提供API來(lái)修改它們。
那么我們想:既然是臨時(shí)表,為何不直接UPDATE它們?那么我們就試一試。
處理步驟:
1. 創(chuàng)建銷(xiāo)售訂單
2. 挑庫(kù)發(fā)放(非自動(dòng)確認(rèn))
3. 使用INV_MOVE_ORDER_PUB.Process_Move_Order_Line修改物料搬運(yùn)單行
4. 更新MMT、MLT、MST表中的相關(guān)數(shù)據(jù)
5. 挑庫(kù)確認(rèn)
6. 發(fā)運(yùn)確認(rèn)
如果你的版本是12.1以后,那么發(fā)運(yùn)過(guò)程中的請(qǐng)求將會(huì)報(bào)錯(cuò),ERROR_CODE:Serial Mssing(序列號(hào)控制的情況下出現(xiàn),批次中不會(huì)出現(xiàn),MetaLink補(bǔ)丁解決這個(gè)BUG)。
但是不管怎么樣,去直接UPDATE一個(gè)表總讓人覺(jué)得不好(而且還有BUG),那么如果辦呢?
我們可以使用一個(gè)Oracle未公開(kāi)的API來(lái)覺(jué)得這個(gè)問(wèn)題:inv_replenish_detail_pub.line_details_pub.
跳出開(kāi)始的固定思維,既然Oracle沒(méi)有API(包括未公開(kāi)的)去修改物料搬運(yùn)單的序列和批次,為什么我們不直接做出來(lái)一個(gè)符合我們要求的物料搬運(yùn)單呢?
下面給出一個(gè)銷(xiāo)售訂單自動(dòng)事務(wù)處理的API方案:
1. oe_order_pub.process_order 創(chuàng)建銷(xiāo)售訂單.
2. wsh_picking_batches_pub.Create_Batch 創(chuàng)建批次號(hào)(自動(dòng)確認(rèn):否/自動(dòng)分配:否).
3. wsh_picking_batches_pub.Release_Batch發(fā)放銷(xiāo)售訂單(并發(fā),ONLINE都行).
4. INV_Trolin_Util.Query_Rows 獲取物料搬運(yùn)單行
5. INV_MOVE_ORDER_PUB.Process_Move_Order_Line
修改物料搬運(yùn)單行(頭上的發(fā)出子庫(kù)無(wú)需修改).
6. inv_replenish_detail_pub.line_details_pub
創(chuàng)建物料搬運(yùn)單分配行(注1)
7. inv_pick_wave_pick_confirm_pub.pick_confirm
挑庫(kù)確認(rèn)
8. wsh_deliveries_pub.delivery_action 交貨號(hào)發(fā)運(yùn)
9. wsh_ship_confirm_actions.interface_all 發(fā)運(yùn)確認(rèn)
注1:如果挑庫(kù)發(fā)放因?yàn)闃I(yè)務(wù)需求的原因不能這是自動(dòng)分配:否,或者由于其他原因在此處已經(jīng)有了物料搬運(yùn)單行的分配行,那么請(qǐng)使用API
inv_mo_line_detail_util.reduce_allocation_quantity
來(lái)刪除配分行(當(dāng)減小數(shù)量等于發(fā)運(yùn)的數(shù)量時(shí),事務(wù)處理行將被刪除)。
下面給出修改物料搬運(yùn)單行,重新生成分配的單行代碼的調(diào)用示例:
[java] view plain copy
DECLARE
-- Common Declarations
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 := ;
x_msg_data VARCHAR2(255);
-- API specific declarations
l_header_id NUMBER := ;
l_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
l_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
l_trolin_tbl INV_MOVE_ORDER_PUB.TROLIN_TBL_TYPE;
o_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_trohdr_rec INV_MOVE_ORDER_PUB.TROHDR_REC_TYPE;
x_trohdr_val_rec INV_MOVE_ORDER_PUB.TROHDR_VAL_REC_TYPE;
-- Cursor to load Move Order Headers
l_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
x_mold_tbl INV_MO_LINE_DETAIL_UTIL.g_mmtt_tbl_type;
l_move_order_type NUMBER := 3;
l_msg_return NUMBER;
x_number_of_rows NUMBER ;
x_detailed_qty NUMBER ;
x_revision VARCHAR2(20) ;
x_locator_id NUMBER ;
x_transfer_to_location NUMBER ;
x_lot_number VARCHAR2(80) ;
x_expiration_date DATE ;
x_transaction_temp_id NUMBER ;
p_transaction_header_id NUMBER ;
p_transaction_mode NUMBER ;
p_move_order_type NUMBER := 3;
p_serial_flag VARCHAR2(1) ;
p_plan_tasks BOOLEAN ;
p_auto_pick_confirm BOOLEAN ;
p_commit BOOLEAN ;
l_t_header_id NUMBER;
l_lot_number VARCHAR2(80) := 'SLT0021'; --mtl_lot_numbers.lot_number;
l_serial_number VARCHAR2(20) := '3.06.S0019'; --mtl_serial_numbers.serial_number
l_subinvetory_code VARCHAR2(10) := 'BJJF_CLK'; --mtl_secondary_inventories.secondary_inventory_name
l_locator_id NUMBER := 42; --mtl_item_locations.inventory_location_id
l_trx_header_id NUMBER := 93023; --mtl_txn_request_headers.header_id
BEGIN
FND_GLOBAL.APPS_INITIALIZE(1371, 50627, 660); -- Suhasini / Mfg Mgr / INV
INV_MOVE_ORDER_PUB.Get_Move_Order(
P_API_VERSION_NUMBER => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_RETURN_VALUES => l_return_values
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
, X_MSG_DATA => x_msg_data
, P_HEADER_ID => l_trx_header_id--93023
, P_HEADER => NULL
, X_TROHDR_REC => l_trohdr_rec
, X_TROHDR_VAL_REC => l_trohdr_val_rec
, X_TROLIN_TBL => l_trolin_tbl
, X_TROLIN_VAL_TBL => l_trolin_val_tbl
);
-- Print the Move Order Header/Lines to be processed
IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
l_trohdr_rec.operation := INV_GLOBALS.G_OPR_UPDATE;
-- FOR i IN 1..l_trolin_tbl.COUNT LOOP
l_trolin_tbl(1).from_subinventory_code := l_subinvetory_code;
l_trolin_tbl(1).from_locator_id := l_locator_id;
l_trolin_tb1(i).lot_number := l_lot_number;
l_trolin_tbl(1).serial_number_start := l_serial_number;
l_trolin_tbl(1).serial_number_end := l_serial_number;
l_trolin_tbl(1).attribute1 := 'update move order test!';
l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_UPDATE;
-- END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('Get_Move_Order error!');
RETURN;
END IF;
INV_MOVE_ORDER_PUB.Process_Move_Order_Line( p_api_version_number => 1.0
,p_init_msg_list => l_init_msg_list
,p_return_values => l_return_values
,p_commit => l_commit
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_trolin_tbl => l_trolin_tbl
,p_trolin_old_tbl => l_trolin_tbl
,x_trolin_tbl => x_trolin_tbl);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
FOR i IN 1..x_msg_count LOOP
fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F'
,p_data => x_msg_data
,p_msg_index_out => l_msg_return);
DBMS_OUTPUT.PUT_LINE(x_msg_data);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE('MODIFY SUCESS!');
END IF;
/* inv_mo_line_detail_util.reduce_allocation_quantity(
x_return_status => x_return_status
,p_transaction_temp_id => 2242994
,p_quantity => 1
,p_secondary_quantity => 1) ;
IF x_return_status <> 'S' THEN
DBMS_OUTPUT.PUT_LINE('EE');
ELSE
DBMS_OUTPUT.PUT_LINE('SS');
END IF;*/
inv_replenish_detail_pub.line_details_pub(
p_line_id => l_trolin_tbl(1).line_id
, x_number_of_rows => x_number_of_rows
, x_detailed_qty => x_detailed_qty
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, x_revision => x_revision
, x_locator_id => x_locator_id
, x_transfer_to_location => x_transfer_to_location
, x_lot_number => x_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 => p_move_order_type
, p_serial_flag => FND_API.G_FALSE
, p_plan_tasks => FALSE
, p_auto_pick_confirm => FALSE
, p_commit => FALSE);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('E');
FOR i IN 1..x_msg_count LOOP
fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F'
,p_data => x_msg_data
,p_msg_index_out => l_msg_return);
DBMS_OUTPUT.PUT_LINE(x_msg_data);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE('ssssss');
DBMS_OUTPUT.PUT_LINE('x_number_of_rows: ' || to_char(x_number_of_rows));
DBMS_OUTPUT.PUT_LINE('x_locator_id: ' || to_char(x_locator_id));
DBMS_OUTPUT.PUT_LINE('x_lot_number: ' || to_char(x_lot_number));
DBMS_OUTPUT.PUT_LINE('x_transfer_to_location: ' || to_char(x_transfer_to_location));
DBMS_OUTPUT.PUT_LINE('x_transaction_temp_id: ' || to_char(x_transaction_temp_id));
END IF;
l_trolin_tbl := INV_Trolin_Util.Query_Rows(p_line_id => l_trolin_tbl(1).line_id);
l_mold_tbl := INV_MO_LINE_DETAIL_UTIL.query_rows(p_line_id => l_trolin_tbl(1).line_id);
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 => l_move_order_type,
p_transaction_mode => 1,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mold_tbl,
x_trolin_tbl => x_trolin_tbl);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('E');
FOR i IN 1..x_msg_count LOOP
fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F'
,p_data => x_msg_data
,p_msg_index_out => l_msg_return);
DBMS_OUTPUT.PUT_LINE(x_msg_data);
END LOOP;
RETURN;
ELSE
DBMS_OUTPUT.PUT_LINE('S');
END IF;
-- END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;
有可能業(yè)務(wù)上只需要在發(fā)運(yùn)出庫(kù)的時(shí)候在交貨號(hào)顯示序列號(hào)和批次號(hào),但是實(shí)際庫(kù)存控制并沒(méi)有這個(gè)需求,也可以在挑庫(kù)確認(rèn)后,通過(guò)API來(lái)修改交貨號(hào)的屬性來(lái)達(dá)到顯示序列號(hào)和批次號(hào)的目的:wsh_delivery_details_pub.Update_Shipping_Attributes
但是這個(gè)并不能達(dá)到實(shí)際出庫(kù)控制的效果
下面也給出一個(gè)示例:
DECLARE
--這只是一個(gè)取數(shù)的例子,修改成別的取數(shù)邏輯,
--cux開(kāi)頭的是客戶化的表
CURSOR dev_header_cur(p_item_key VARCHAR2) IS
SELECT
wdd.source_header_id AS source_header_id
,wdd.source_header_number AS source_header_number
,wdd.source_line_id AS source_line_id
,wda.delivery_id AS delivery_id
,wdd.delivery_detail_id AS delivery_detail_id
,wdd.organization_id AS organization_id
,wdd.source_code AS source_code
,wdd.requested_quantity AS requested_quantity
,tll.batch_number AS lot_number
,tll.sequence_number AS serial_number
,tll.line_number AS line_number
,tll.header_id AS load_header_id
,tll.line_id AS load_line_id
,tll.send_sec_inv_code AS send_subinventory
,tll.send_inv_location_id AS send_locator_id
FROM
cux_tr_load_doc_wf_headers cwh
,cux_tr_load_doc_wf_lines cwl
,cux_tr_load_doc_lines_all tll
,oe_order_headers_all ooh
,oe_order_lines_all ool
,wsh_delivery_details wdd
,wsh_delivery_assignments wda
WHERE
cwh.l_inner_oe_header_id = ooh.header_id
AND cwh.header_id = cwl.header_id
AND cwl.load_doc_line_id = tll.line_id
AND cwl.l_inner_oe_line_id = ool.line_id
AND ool.line_id = wdd.source_line_id --bug fix 2010-08-19
AND wdd.delivery_detail_id = wda.delivery_detail_id
--
-- the value of parameter
--
AND cwh.item_key = 'STH001';
l_index NUMBER;
l_msg_return NUMBER;
x_return_status VARCHAR2(1);
x_msg_count NUMBER;
x_msg_data VARCHAR2(2000);
l_source_code VARCHAR2(40);
l_serialrangetabtype wsh_glbl_var_strct_grp.ddserialrangetabtype;
l_changedattributetabtype wsh_delivery_details_pub.changedattributetabtype;
BEGIN
fnd_global.APPS_INITIALIZE( user_id => -1
,resp_id => -1
,resp_appl_id => -1);
l_index := ;
FOR dev_header_rec IN dev_header_cur('STH001') LOOP
l_index := l_index + 1;
l_source_code := dev_header_rec.source_code;
l_changedattributetabtype(l_index).source_header_id := dev_header_rec.source_header_id;
l_changedattributetabtype(l_index).source_line_id := dev_header_rec.source_line_id;
l_changedattributetabtype(l_index).delivery_detail_id := dev_header_rec.delivery_detail_id;
l_changedattributetabtype(l_index).subinventory := 'CLK_SD';
l_changedattributetabtype(l_index).locator_id := 42;--貨位控制
l_changedattributetabtype(l_index).lot_number := 'LOT_SK001';--批次
IF dev_header_rec.requested_quantity = 1 THEN
l_changedattributetabtype(l_index).serial_number := 'LEOCHEN194';
END IF;
FOR i IN 1..dev_header_rec.requested_quantity LOOP
l_serialrangetabtype(1).delivery_detail_id := dev_header_rec.delivery_detail_id;
l_serialrangetabtype(1).from_serial_number := 'LEOCHEN194';--LEOCHEN165
l_serialrangetabtype(1).to_serial_number := 'LEOCHEN194';
--v_serialRangeTabType(1).quantity := 1; --Dl.ordered_qty;
l_serialrangetabtype(2).delivery_detail_id := dev_header_rec.delivery_detail_id;
l_serialrangetabtype(2).from_serial_number := 'LEOCHEN195';--LEOCHEN165
l_serialrangetabtype(2).to_serial_number := 'LEOCHEN195';
--......在此設(shè)置多個(gè)序列號(hào)在一行的情況
END LOOP;
END LOOP;
wsh_delivery_details_pub.Update_Shipping_Attributes(p_api_version_number => 1.0,
p_init_msg_list => 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_changed_attributes => l_changedattributetabtype,
p_source_code => l_source_code,
p_container_flag => NULL ,
p_serial_range_tab => l_serialrangetabtype );
IF x_return_status <> fnd_api.G_RET_STS_SUCCESS THEN
FOR i IN 1..x_msg_count LOOP
fnd_msg_pub.get(p_msg_index => i
,p_encoded => 'F'
,p_data => x_msg_data
,p_msg_index_out => l_msg_return);
dbms_output.put_line(x_msg_data);
END LOOP;
ELSE
dbms_output.put_line('S');
END IF;
END;
感謝各位的閱讀,以上就是“API怎么實(shí)現(xiàn)批次序列號(hào)的銷(xiāo)售出庫(kù)”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)API怎么實(shí)現(xiàn)批次序列號(hào)的銷(xiāo)售出庫(kù)這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云,小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。