Shulou(Shulou.com)06/01 Report--

-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

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.