有一个表MATL_MID_TABLE(WO_NO,MATL_NO,ORDER_QTY)对应申请单号(主键),货物编码(主键),预定数量
另有一表WO_MATL(MATL_NO,MATL_NO,QUANTITY,USED_QTY,NEED_QTY,WO_NO)分别是:货物编码(主键),名称,申请数量,可利用数量,需要采购数量,申请单号,
表三MATL_INV_QTY(MATL_NO,SUM(QUANTITY) ) 分别是:货物编码(主键),库存总量.
通过一个过程,现作如下要求:
通过申请单号WO_NO找到对应的货物编码MATL_NO以及所要申请的数量QUANTITY,库存总量SUM(QUANTITY)和预定数ORDER_QTY以及后二者的比较,如果小,则修改可利用数量=库存总量SUM(QUANTITY)-所要申请的数量QUANTITY,同时置需要采购数=0;否则可利用数量=0,需要采购数量=(申请的数量QUANTITY+预定数量ORDER_QTY)-库存总量SUM(QUANTITY),最后可表WO_MATL中MATL_NO,MATL_NO,QUANTITY插入到表MATL_MID_TABLE中.
我写的过程如下,只能完成单行的插入,请高手指教,修改一下.
PROCEDURE GET_NEED_QTY (
wo_no_IN VARCHAR2 )
IS
qty_
NUMBER;
ord_qty_NUMBER;
mt_no_VARCHAR2(50);
quantity_ NUMBER;
need_qty_ NUMBER;
used_qty_ NUMBER;
matl_no VARCHAR2(50);
wo_n_ VARCHAR2(50);
qty_qty_NUMBER;
CURSOR c_wo_matl IS SELECT WO_NO,MATL_NO,NVL(SUM(QUANTITY),0) FROM WO_MATL WHERE WO_NO = wo_no_;
BEGIN
OPEN c_wo_matl;
LOOP
fetch c_wo_matl INTO wo_n_, matl_no,qty_qty_;
EXIT WHEN c_wo_matl%NOTFOUND;
END LOOP;
SELECT MATL_NOINTOmt_no_ FROM WO_MATLWHERE WO_NO = wo_no_;
SELECT SUM(QUANTITY) INTO qty_FROM MATL_INV_QTYWHEREMATL_NO = mt_no_;
SELECT NVL(SUM(ORDER_QTY),0) INTO ord_qty_ FROM MATL_MID_TABLEWHEREMATL_NO = mt_no_;
used_qty_ :=qty_ - ord_qty_;
IF (used_qty_ >0) THEN
NULL;
ELSE
used_qty_:=0;
END IF;
UPDATEWO_MATL SET USED_QTY = used_qty_ WHERE WO_NO=wo_no_ AND MATL_NO=mt_no_;
SELECT QUANTITY INTO quantity_ FROM WO_MATL WHERE WO_NO = wo_no_ AND MATL_NO = mt_no_;
need_qty_:= used_qty_ - quantity_ ;
IF (need_qty_ >0) THEN
need_qty_:=0;
ELSE
need_qty_:= quantity_ - used_qty_;
END IF;
UPDATE WO_MATL
SET NEED_QTY = need_qty_ WHERE WO_NO = wo_no_ AND MATL_NO = mt_no_;
IF(matl_no IS NOT NULL) THEN
INSERT INTO MATL_MID_TABLE(MATL_NO,WO_NO,ORDER_QTY)VALUES(wo_n_, matl_no,qty_qty_);
END IF;
CLOSE c_wo_matl;
|
