oralce----PL/SQL实例

CREATE OR REPLACE PACKAGE BODY KF2011_WMS.wms_order AS TYPE cursor_type IS REF CURSOR; --确认收货 PROCEDURE p_confirm_receipt (vv_order_id VARCHAR2, vv_user_id VARCHAR2) AS vr_order wm_order_in%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_in (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.receipt_flag = 1 THEN raise_application_error (-20001, vv_order_id || '订单已经收货！'); END IF; IF vr_order.receipt_loc IS NULL THEN raise_application_error (-20001, vv_order_id || '无收货库位！'); END IF; --生成移动工单计划 INSERT INTO wm_mat_transfer (logi_id, batch_num, quot_id, mat_id, cust_id, src_loc, src_type, tgt_loc, tgt_type, quantity, status, begin_date, finish_date, oper_id, remark, tran_type, order_id, whs_id, src_region, tgt_region) SELECT f_next_logi_id, batch_num, quot_id, mat_id, cust_id, NULL, NULL, vr_order.receipt_loc, wms_const.loc_type_receipt, quantity, wms_const.transfer_status_new, SYSDATE, NULL, vv_user_id, NULL, wms_const.transfer_type_receipt, vv_order_id, vr_order.whs_id, NULL, NULL FROM wm_mat_quotient WHERE batch_num = vr_order.batch_num; --完成移库工单计划 UPDATE wm_mat_transfer SET finish_date = SYSDATE, status = wms_const.transfer_status_finish WHERE order_id = vv_order_id AND tran_type = wms_const.transfer_type_receipt; --更新确认状态 UPDATE wm_order_in SET receiving_time = SYSDATE, receiving_oper = vv_user_id, receipt_flag = 1 WHERE order_id = vv_order_id; END; --取消收货 PROCEDURE p_cancel_receipt (vv_order_id VARCHAR2) AS vr_order wm_order_in%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_in (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.receipt_flag != 1 THEN raise_application_error (-20001, vv_order_id || '订单未收货！'); END IF; --删除移动工单 DELETE wm_mat_transfer WHERE order_id = vv_order_id AND tran_type = wms_const.transfer_type_receipt; --更新收货状态(未确认) UPDATE wm_order_in SET receiving_time = NULL, receiving_oper = NULL, receipt_flag = 0 WHERE order_id = vv_order_id; END; --确认上架 PROCEDURE p_confirm_putaway (vv_order_id VARCHAR2, vv_user_id VARCHAR2) AS vr_order wm_order_in%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_in (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.putway_flag = 1 THEN raise_application_error (-20001, vv_order_id || '订单已经上架！'); END IF; --生成移动工单计划 INSERT INTO wm_mat_transfer (logi_id, batch_num, quot_id, mat_id, cust_id, src_loc, src_type, tgt_loc, tgt_type, quantity, status, begin_date, finish_date, oper_id, remark, tran_type, order_id, whs_id, src_region, tgt_region) SELECT f_next_logi_id, vr_order.batch_num, quot_id, mat_id, vr_order.cust_id, vr_order.receipt_loc, wms_const.loc_type_receipt, loc_id, wms_const.loc_type_storage, quantity, wms_const.transfer_status_new, SYSDATE, NULL, vv_user_id, NULL, wms_const.transfer_type_putaway, vv_order_id, vr_order.whs_id, NULL, NULL FROM wm_order_in_putaway WHERE order_id = vv_order_id; --完成移库工单计划 UPDATE wm_mat_transfer SET finish_date = SYSDATE, status = wms_const.transfer_status_finish WHERE order_id = vv_order_id AND tran_type = wms_const.transfer_type_putaway; --更新确认状态 UPDATE wm_order_in SET putway_time = SYSDATE, putway_oper = vv_user_id, putway_flag = 1 WHERE order_id = vv_order_id; END; --拣货分配(生成拣货清单) PROCEDURE p_picking_manifest (vv_order_id VARCHAR2) AS vr_mat_out wm_order_out_material%ROWTYPE; --订单物料 vr_inv wm_mat_inventory%ROWTYPE; --库存 vn_sum_picking NUMBER (8); --累计拣货数量 vn_picking NUMBER (8); --本次拣货数量 BEGIN --遍历待出库的物料 FOR vr_mat_out IN (SELECT * FROM wm_order_out_material WHERE order_id = vv_order_id) LOOP BEGIN --查找最匹配的一个库位 SELECT * INTO vr_inv FROM wm_mat_inventory i WHERE ROWNUM < 2 AND i.mat_id = vr_mat_out.mat_id AND i.cust_id = vr_mat_out.cust_id AND i.loc_type = wms_const.loc_type_storage AND i.qty_balance = vr_mat_out.quantity AND i.qty_input = 0 AND i.qty_output = 0 ORDER BY i.batch_num; --添加拣货记录 INSERT INTO wm_order_out_picking (mat_serial, quot_id, mat_id, quantity, loc_id, order_id) VALUES ( f_next_mat_serial, vr_inv.quot_id, vr_inv.mat_id, vr_inv.qty_balance, vr_inv.loc_id, vv_order_id); GOTO NEXT; EXCEPTION WHEN OTHERS THEN NULL; END; --初始化拣货数量 vn_sum_picking := 0; vn_picking := 0; --如果没有找到最佳匹配库位 FOR vr_inv IN ( SELECT * FROM wm_mat_inventory i WHERE mat_id = vr_mat_out.mat_id AND i.cust_id = vr_mat_out.cust_id AND i.loc_type = wms_const.loc_type_storage AND i.qty_input = 0 AND i.qty_output = 0 ORDER BY i.qty_balance, i.batch_num) LOOP IF vr_inv.qty_balance >= (vr_mat_out.quantity - vn_sum_picking) THEN --如果库存数量大于等于剩余拣货数量，则当前拣货数量等于剩余拣货数量 vn_picking := vr_mat_out.quantity - vn_sum_picking; ELSE --如果库存数量小于剩余拣货数量，则当前拣货数量等于库存余额 vn_picking := vr_inv.qty_balance; END IF; vn_sum_picking := vn_sum_picking + vn_picking; --添加拣货记录 INSERT INTO wm_order_out_picking (mat_serial, quot_id, mat_id, quantity, loc_id, order_id, batch_num) VALUES ( f_next_mat_serial, vr_inv.quot_id, vr_inv.mat_id, vn_picking, vr_inv.loc_id, vv_order_id, vr_inv.batch_num); EXIT WHEN vn_sum_picking >= vr_mat_out.quantity; END LOOP; <<NEXT>> NULL; END LOOP; END; --确认拣货清单 PROCEDURE p_confirm_manifest (vv_order_id VARCHAR2, vv_user_id VARCHAR2) AS vn_flag NUMBER (8); vr_order wm_order_out%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_out (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.picking_flag = 1 THEN raise_application_error (-20001, vv_order_id || '订单已经拣货！'); END IF; IF vr_order.ship_loc IS NULL THEN raise_application_error (-20001, vv_order_id || '无发货库位！'); END IF; --验证拣货单(拣货物料是否与出库物料数量相等) vn_flag := NULL; SELECT 1 INTO vn_flag FROM DUAL WHERE EXISTS ( SELECT * FROM (SELECT mat_id, SUM (quantity) sum_qty FROM wm_order_out_material m WHERE m.order_id = vv_order_id UNION ALL SELECT mat_id, SUM (quantity) sum_qty FROM wm_order_out_picking p WHERE p.order_id = vv_order_id) GROUP BY mat_id, sum_qty HAVING COUNT (*) < 2); IF vn_flag IS NOT NULL AND vn_flag = 1 THEN raise_application_error (-20001, vv_order_id || '无效的拣货单！'); END IF; --生成移动工单计划 INSERT INTO wm_mat_transfer (logi_id, batch_num, quot_id, mat_id, cust_id, src_loc, src_type, tgt_loc, tgt_type, quantity, status, begin_date, finish_date, oper_id, remark, tran_type, order_id, whs_id, src_region, tgt_region) SELECT f_next_logi_id, p.batch_num, quot_id, mat_id, vr_order.cust_id, p.loc_id, wms_const.loc_type_storage, vr_order.ship_loc, wms_const.loc_type_issue, quantity, wms_const.transfer_status_new, SYSDATE, NULL, vv_user_id, NULL, wms_const.transfer_type_picking, vv_order_id, vr_order.whs_id, NULL, NULL FROM wm_order_out_picking p WHERE order_id = vv_order_id; END; --确认拣货 PROCEDURE p_confirm_picking (vv_order_id VARCHAR2, vv_user_id VARCHAR2) AS vr_order wm_order_out%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_out (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.picking_flag = 1 THEN raise_application_error (-20001, vv_order_id || '订单已经拣货！'); END IF; --完成移库工单计划 UPDATE wm_mat_transfer SET finish_date = SYSDATE, status = wms_const.transfer_status_finish WHERE order_id = vv_order_id AND tran_type = wms_const.transfer_type_picking; --更新确认状态 UPDATE wm_order_out o SET o.picking_time = SYSDATE, o.picking_oper = vv_user_id, o.picking_flag = 1 WHERE order_id = vv_order_id; NULL; END; --确认发货 PROCEDURE p_confirm_shipment (vv_order_id VARCHAR2, vv_user_id VARCHAR2) AS vr_order wm_order_out%ROWTYPE; BEGIN --锁定入库订单 wms_common.p_lock_order_out (vr_order => vr_order, vv_order_id => vv_order_id); IF vr_order.ship_flag = 1 THEN raise_application_error (-20001, vv_order_id || '订单已经发货！'); END IF; IF vr_order.ship_loc IS NULL THEN raise_application_error (-20001, vv_order_id || '无发货库位！'); END IF; --生成移动工单计划 INSERT INTO wm_mat_transfer (logi_id, batch_num, quot_id, mat_id, cust_id, src_loc, src_type, tgt_loc, tgt_type, quantity, status, begin_date, finish_date, oper_id, remark, tran_type, order_id, whs_id, src_region, tgt_region) SELECT f_next_logi_id, p.batch_num, p.quot_id, p.mat_id, vr_order.cust_id, vr_order.ship_loc, wms_const.loc_type_issue, NULL, NULL, p.quantity, wms_const.transfer_status_new, SYSDATE, NULL, vv_user_id, NULL, wms_const.transfer_type_shipments, vv_order_id, vr_order.whs_id, NULL, NULL FROM wm_order_out_picking p WHERE p.order_id = vv_order_id; --完成移库工单计划 UPDATE wm_mat_transfer SET finish_date = SYSDATE, status = wms_const.transfer_status_finish WHERE order_id = vv_order_id AND tran_type = wms_const.transfer_type_shipments; --更新确认状态 UPDATE wm_order_out o SET o.ship_time = SYSDATE, o.ship_oper = vv_user_id, o.ship_flag = 1 WHERE order_id = vv_order_id; END; END wms_order; /