PROCEDURE Create_Pick_Task(info_ OUT VARCHAR2,
move_no_ IN OUT VARCHAR2,
order_no_ IN VARCHAR2,
line_no_ IN VARCHAR2,
release_no_ IN VARCHAR2,
line_item_no_ IN VARCHAR2,
user_id_ IN VARCHAR2,
order_type_ IN NUMBER) -- -- 1、库内移库 2、仓库间调拨 3、拣货
IS
qty_to_move_ NUMBER;
max_line_no_ NUMBER := 0;
contract_ VARCHAR2(5);
component_part_ VARCHAR(25);
backflush_loc_ VARCHAR2(35);
warehouse_ VARCHAR2(15);
ean_no_ VARCHAR2(35);
lot_batch_no_ VARCHAR2(20);
serial_no_ VARCHAR2(50);
qty_onhand_ NUMBER;
sum_qty_onhand_ NUMBER;
--
CURSOR get_purchase_order_line_comp IS
SELECT (nvl(qty_required,0) - nvl(qty_issued,0)) ,contract,component_part,backflush_loc
FROM purchase_order_line_comp_tab
WHERE order_no = order_no_
AND line_no = line_no_
AND release_no = release_no_
AND line_item_no = line_item_no_;
--
CURSOR get_line_no IS
SELECT MAX(line_no)
FROM m_move_order_line_tab
WHERE move_no = move_no_;
--
CURSOR get_inventory_locaiton IS
SELECT warehouse
FROM inventory_location_tab
WHERE contract = contract_
AND location_no = backflush_loc_ ;
--
CURSOR get_inventory_part IS
SELECT ean_no
FROM inventory_part_tab
WHERE part_no = component_part_
AND contract = contract_;
--
CURSOR get_inventory_part_in_stock IS
SELECT lot_batch_no , serial_no , qty_onhand
FROM inventory_part_in_stock_tab
WHERE contract = contract_
AND part_no = component_part_
AND warehouse = warehouse_ ;
--
CURSOR get_sum_stock IS
SELECT SUM(qty_onhand)
FROM inventory_part_in_stock_tab
WHERE contract = contract_
AND part_no = component_part_
AND warehouse = warehouse_ ;
BEGIN
OPEN get_purchase_order_line_comp ;
FETCH get_purchase_order_line_comp INTO qty_to_move_ ,contract_ , component_part_ , backflush_loc_;
CLOSE get_purchase_order_line_comp;
OPEN get_inventory_locaiton ;
FETCH get_inventory_locaiton INTO warehouse_;
CLOSE get_inventory_locaiton;
OPEN get_inventory_part ;
FETCH get_inventory_part INTO ean_no_;
CLOSE get_inventory_part;
OPEN get_sum_stock ;
FETCH get_sum_stock INTO sum_qty_onhand_;
CLOSE get_sum_stock;
-- 拣货数量大于在库存
IF sum_qty_onhand_ < qty_to_move_ THEN
Error_Sys.Record_General(lu_name_, 'QTYNOTALLOW: 拣货数量 :P1 大于在库库存 :P2 创建委外出库拣货任务失败 !',
qty_to_move_,
sum_qty_onhand_);
END IF;
IF qty_to_move_ >0 THEN
-- 创建移库单头
IF nvl(move_no_ ,'') = '' THEN
move_no_ := M_Move_Order_Api.Get_Move_No( SYSDATE );
INSERT
INTO m_move_order_tab (
move_no,
moved_date,
warehouse,
moved_user,
order_type,
to_warehouse,
rowstate,
created_date,
created_user,
updated_date,
updated_user,
rowversion)
VALUES (
move_no_,
SYSDATE,
warehouse_,
NULL,
order_type_, -- 1、库内移库 2、仓库间调拨 3、拣货
warehouse_,
1, -- 1、已创建 2、拣货中 3、已拣货 4、已完成
SYSDATE,
user_id_,
SYSDATE,
user_id_,
SYSDATE);
END IF;
--Fetch 循环 获取库存批次
OPEN get_inventory_part_in_stock;--必须要明确的打开和关闭游标
LOOP
FETCH get_inventory_part_in_stock INTO lot_batch_no_ ,serial_no_ , qty_onhand_ ;
EXIT WHEN get_inventory_part_in_stock%NOTFOUND;
IF qty_onhand_ >= qty_to_move_ THEN
-- 创建明细行
OPEN get_line_no;
FETCH get_line_no INTO max_line_no_;
CLOSE get_line_no;
INSERT
INTO m_move_order_line_tab (
move_no,
line_no,
warehouse,
part_no,
ean_no,
qty_to_move,
qty_moved,
from_loca_no,
to_loca_no,
lot_batch_no,
barcode_no,
created_date,
created_user,
updated_date,
updated_user,
rowversion)
VALUES (
move_no_,
max_line_no_ + 1,
warehouse_,
component_part_,
ean_no_,
qty_to_move_,
0,
backflush_loc_,
'*',
lot_batch_no_,
'*',
sysdate,
user_id_,
sysdate,
user_id_,
sysdate) ;
EXIT;
ELSE
-- 创建明细行
OPEN get_line_no;
FETCH get_line_no INTO max_line_no_;
CLOSE get_line_no;
INSERT
INTO m_move_order_line_tab (
move_no,
line_no,
warehouse,
part_no,
ean_no,
qty_to_move,
qty_moved,
from_loca_no,
to_loca_no,
lot_batch_no,
barcode_no,
created_date,
created_user,
updated_date,
updated_user,
rowversion)
VALUES (
move_no_,
max_line_no_ + 1,
warehouse_,
component_part_,
ean_no_,
qty_onhand_,
0,
backflush_loc_,
'*',
lot_batch_no_,
'*',
sysdate,
user_id_,
sysdate,
user_id_,
sysdate) ;
qty_to_move_ :=( qty_to_move_ - qty_onhand_ );
END IF;
END LOOP;
CLOSE get_inventory_part_in_stock;
END IF;
END Create_Pick_Task;
-- 完成委外叫料拣货
PROCEDURE Finished_Call_Out_Material(info_ OUT VARCHAR2,
move_no_ IN VARCHAR2,
department_id_ IN VARCHAR2,
user_id_ IN VARCHAR2)
IS
max_line_no_ NUMBER;
barcode_rec_ m_barcode_status_tab%ROWTYPE;
sum_barcode_qty_ NUMBER;
qty_issue_ NUMBER := 0;
qty_remain_ NUMBER := 0;
contract_ VARCHAR2(5) := User_Allowed_Site_Api.Get_Default_Site;
CURSOR get_m_move_order_line IS
SELECT *
FROM M_MOVE_ORDER_LINE_TAB m
WHERE m.move_no = move_no_
AND m.qty_to_move > m.qty_moved ;
CURSOR get_max_line_no IS
SELECT MAX(m.line_no)
FROM M_MOVE_ORDER_LINE_TAB m
WHERE m.move_no = move_no_ ;
BEGIN
-- 获取移库明细行
FOR move_line_rec_ IN get_m_move_order_line LOOP
qty_remain_ := (move_line_rec_.qty_to_move - move_line_rec_.qty_moved) ;
-- 判断标签库存是否足够
DECLARE
CURSOR get_sum_barcode_qty IS
SELECT SUM(m.qty)
FROM m_location_no_barcode_tab m
WHERE m.part_no = move_line_rec_.part_no
AND m.location_no = move_line_rec_.from_loca_no
AND m.contract = contract_;
BEGIN
OPEN get_sum_barcode_qty ;
FETCH get_sum_barcode_qty INTO sum_barcode_qty_ ;
CLOSE get_sum_barcode_qty ;
IF (move_line_rec_.qty_to_move - move_line_rec_.qty_moved) > sum_barcode_qty_ THEN
Error_Sys.Record_General(lu_name_, 'NOTENOUGHTBARCODESTOCK: 标签库存 :P1 在库数量 :P2 小于移库数量 :P3 !',
move_line_rec_.part_no||'-'||move_line_rec_.from_loca_no,
sum_barcode_qty_,
move_line_rec_.move_no||'-'||move_line_rec_.line_no||'-'||move_line_rec_.qty_to_move - move_line_rec_.qty_moved);
END IF ;
END;
DECLARE
CURSOR get_m_location_barcode_tab IS
SELECT *
FROM m_location_no_barcode_tab m
WHERE m.part_no = move_line_rec_.part_no
AND m.location_no = move_line_rec_.from_loca_no
AND m.contract = contract_ ;
BEGIN
-- 获取标签库存行执行移库
FOR loca_barcode_rec_ IN get_m_location_barcode_tab LOOP
qty_issue_ := least(loca_barcode_rec_.qty , qty_remain_);
qty_remain_ := qty_remain_ - qty_issue_ ;
-- 库存下架
m_inv_part_in_stock_util_api.Issue_Part(part_no_ => move_line_rec_.part_no,
location_no_ => move_line_rec_.from_loca_no,
qty_issued_ => qty_issue_,
user_id_ => user_id_) ;
-- 库存上架
m_inv_part_in_stock_util_api.Receive_Part(part_no_ => move_line_rec_.part_no,
location_no_ => move_line_rec_.to_loca_no,
qty_received_ => qty_issue_,
user_id_ => user_id_);
-- barcode 库存下架
m_location_no_barcode_api.Issue_Barcode(barcode_no_ => loca_barcode_rec_.barcode_no,
from_loca_no_ => move_line_rec_.from_loca_no,
qty_issue_ => qty_issue_,
transaction_code_ => 'INVM-ISS',
user_id_ => user_id_ );
-- barcode 库存上架
m_location_no_barcode_api.Receive_Barcode(barcode_no_ => loca_barcode_rec_.barcode_no,
to_loca_no_ => move_line_rec_.to_loca_no,
qty_received_ => qty_issue_,
transaction_code_ => 'INVM-IN',
user_id_ => user_id_);
OPEN get_max_line_no ;
FETCH get_max_line_no INTO max_line_no_ ;
CLOSE get_max_line_no;
max_line_no_ := nvl(max_line_no_ , 0);
DECLARE
CURSOR get_m_barcode_status IS
SELECT *
FROM m_barcode_status_tab m
WHERE m.barcode_no = loca_barcode_rec_.barcode_no
AND m.contract = contract_;
BEGIN
OPEN get_m_barcode_status ;
FETCH get_m_barcode_status INTO barcode_rec_ ;
CLOSE get_m_barcode_status ;
-- 插入新的移库行
INSERT
INTO m_move_order_line_tab (
move_no,
line_no,
warehouse,
part_no,
ean_no,
qty_to_move,
qty_moved,
from_loca_no,
to_loca_no,
to_warehouse,
lot_batch_no,
barcode_no,
created_date,
created_user,
updated_date,
updated_user,
rowversion,
co_order_no,
co_line_no,
co_release_no,
co_line_item_no
)
VALUES (
move_no_,
max_line_no_ + 1,
move_line_rec_.warehouse,
move_line_rec_.part_no,
move_line_rec_.ean_no,
qty_issue_,
qty_issue_,
move_line_rec_.from_loca_no,
move_line_rec_.to_loca_no,
move_line_rec_.to_warehouse,
barcode_rec_.lot_batch_no,
barcode_rec_.barcode_no,
sysdate,
user_id_,
sysdate,
user_id_,
sysdate,
move_line_rec_.co_order_no,
move_line_rec_.co_line_no,
move_line_rec_.co_release_no,
move_line_rec_.co_line_item_no);
-- 完成后,删除原来的行
IF qty_remain_ = 0 THEN
DELETE FROM m_move_order_line_tab
WHERE move_no = move_no_
AND line_no = move_line_rec_.line_no ;
EXIT ;
END IF ;
END;
END LOOP ;
END;
END LOOP ;
-- 跟新移库单状态
UPDATE m_move_order_tab m SET m.rowstate=4 WHERE m.move_no = move_no_ ;
END Finished_Call_Out_Material;
转载于:https://my.oschina.net/u/3556610/blog/3050049
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/100919.html原文链接：https://javaforall.cn
【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛
【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...