根据STOCK_LOT_STORAGESPACES 生成其他五大库存
--------根据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
WHERE SPID = C.SPID
AND YZID = C.YZID;
DELETE FROM STOCK_STORAGESPACES
WHERE SPID = C.SPID
AND YZID = C.YZID;
DELETE FROM STOCK_STOREROOM
WHERE SPID = C.SPID
AND YZID = C.YZID;
DELETE FROM STOCK_STOREROOM_LOT
WHERE SPID = C.SPID
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
(SPID,
HWID,
SL_KC,
SL_KCK,
SL_CKYFP,
SL_RKYFP,
SL_FHGPCKYFP,
SL_FHGPRKYFP,
TPTM,
YZID)
SELECT SPID,
HWID,
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,
MAX(BZ) TPTM,
YZID
FROM STOCK_LOT_STORAGESPACES
WHERE SPID = C.SPID
AND YZID = C.YZID
GROUP BY SPID, HWID, YZID;
INSERT INTO STOCK_LOT
(SPID,
PHID,
SL_KC,
SL_KCK,
SL_CKYFP,
SL_RKYFP,
SL_FHGPCKYFP,
SL_FHGPRKYFP,
YZID)
SELECT SPID,
PHID,
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
WHERE SPID = C.SPID
AND YZID = C.YZID
GROUP BY SPID, PHID, YZID;
INSERT INTO STOCK_STOREROOM
(SPID,
KB,
SL_KC,
SL_KCK,
SL_CKYFP,
SL_RKYFP,
SL_FHGPCKYFP,
SL_FHGPRKYFP,
YZID)
SELECT A.SPID,
B.KB,
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,
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
(SPID,
KB,
PHID,
SL_KC,
SL_KCK,
SL_CKYFP,
SL_RKYFP,
SL_FHGPCKYFP,
SL_FHGPRKYFP,
YZID)
SELECT A.SPID,
B.KB,
PHID,
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,
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, PHID, A.YZID;*/
END LOOP;
END;
/
