Generate the other five major inventory according to STOCK_LOT_STORAGESPACES
-generate the other five major inventories according to STOCK_LOT_STORAGESPACES. You just need to adjust the STOCK_LOT_STORAGESPACES table.
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.YZIDTING /
END LOOP
END
/
