-------根据STOCK_LOT_STORAGESPACES 生成其他五大库存。只需调STOCK_LOT_STORAGESPACES这个表就行
BEGIN
FOR C IN (SELECT DISTINCT A.SPID, A.YZID
FROM STOCK_LOT_STORAGESPACES A
WHERE spid='SPH00010308') LOOP
DELETE FROM STOCK
WHERE SPID = C.SPID
AND YZID = C.YZID;
/*DELETE FROM STOCK_LOT
DELETE FROM STOCK_STORAGESPACES
DELETE FROM STOCK_STOREROOM
DELETE FROM STOCK_STOREROOM_LOT
AND YZID = C.YZID;*/
INSERT INTO STOCK
(SPID,
SL_KC,
SL_KCK,
SL_CKYFP,
SL_RKYFP,
SL_FHGPCKYFP,
SL_FHGPRKYFP,
YZID)
SELECT SPID,
SUM(SL_KC) SL_KC,
SUM(SL_KCK) SL_KCK,
SUM(SL_CKYFP) SL_CKYFP,
SUM(SL_RKYFP) SL_RKYFP,
SUM(SL_FHGPCKYFP) SL_FHGPCKYFP,
SUM(SL_FHGPRKYFP) SL_FHGPRKYFP,
YZID
FROM STOCK_LOT_STORAGESPACES --HAVING SUM(SL_KC)>0
WHERE SPID = C.SPID
AND YZID = C.YZID
GROUP BY SPID, YZID;
/* INSERT INTO STOCK_STORAGESPACES
HWID,
TPTM,
HWID,
MAX(BZ) TPTM,
FROM STOCK_LOT_STORAGESPACES
GROUP BY SPID, HWID, YZID;
INSERT INTO STOCK_LOT
PHID,
PHID,
GROUP BY SPID, PHID, YZID;
INSERT INTO STOCK_STOREROOM
KB,
SELECT A.SPID,
B.KB,
A.YZID
FROM STOCK_LOT_STORAGESPACES A, JC_HWZD B
WHERE A.HWID = B.HWID
AND A.SPID = C.SPID
AND A.YZID = C.YZID
GROUP BY A.SPID, B.KB, A.YZID;
INSERT INTO STOCK_STOREROOM_LOT
GROUP BY A.SPID, B.KB, PHID, A.YZID;*/
END LOOP;
END;
/
本文转自 qvodnet 51CTO博客,原文链接:http://blog.51cto.com/bks2015/1982995