DECLARE
V_SQL VARCHAR2(4000);
V_WRH VARCHAR2(4000);
V_SOR VARCHAR2(4000);
V_OTH VARCHAR2(4000);
VBDATE DATE;
VEDATE DATE;
V_ID INT;
V_ID1 INT;
V_CKSEQ VARCHAR2(100);
V_STORE VARCHAR2(100);
----V_ACODE VARCHAR2(100);
BEGIN
VBDATE := TO_DATE('2014-11-01','YYYY.MM.DD');
VEDATE := TO_DATE('2014-11-05','YYYY.MM.DD');
DELETE HD_REPORT.HDTMP_DXD_PHMX;
COMMIT;
----获取有效盘点序号，FILDATE:门店计划盘点日期，FILDATE1：盘入日期
INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,FILDATE1,GDCODE,STCODE,NOTE)
SELECT DISTINCT ROWNUM ID,CKSEQ,FILDATE,FILDATE1,'-',STORE,'0'
FROM (
SELECT DISTINCT A.CKSEQ,TRUNC(B.CKDATE) FILDATE,TRUNC(A.CKTIME) FILDATE1,A.STORE
FROM CKDATAS A,CKDIR B
WHERE A.CKSEQ=B.CKSEQ AND A.STAT='3'
AND A.CKTIME >= VBDATE AND A.CKTIME < VEDATE
) A;
COMMIT;
------------------
V_ID := 1;
----V_ACODE := '0000';
SELECT MAX(NUM) INTO V_ID1 FROM HD_REPORT.HDTMP_DXD_PHMX WHERE NOTE='0';
WHILE V_ID <= V_ID1
LOOP
SELECT DISTINCT CLS INTO V_CKSEQ ---盘点序号
FROM HD_REPORT.HDTMP_DXD_PHMX
WHERE NUM=V_ID;
SELECT STCODE INTO V_STORE ---门店GID
FROM HD_REPORT.HDTMP_DXD_PHMX
WHERE NUM=V_ID;
----------获取仓位范围------
SELECT DISTINCT NVL(B.WRHRANGESQL,'0=0') INTO V_WRH
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID = V_STORE;
---------------
---------获取品类范围
SELECT DISTINCT NVL(SORTRANGESQL,'0=0') INTO V_SOR
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID=V_STORE
--AND A.CKDATE >= '2014-11-01' AND A.CKDATE < '2014-11-05'
;
-------获取其他限制条件
SELECT DISTINCT NVL(OTHERSRANGESQL,'0=0') INTO V_OTH
FROM CKDIR A,CKRANGEDTL B,CKDATAS C,STORE S
WHERE C.CKSEQ=B.NO AND C.STAT='3' AND C.STORE=S.GID AND C.CKSEQ=A.CKSEQ
AND C.CKSEQ=V_CKSEQ AND S.GID = V_STORE;
-----------------------
V_SQL := 'INSERT INTO HD_REPORT.HDTMP_DXD_PHMX
(NUM,CLS,FILDATE,GDCODE,STCODE)
SELECT DISTINCT 1,V_CKSEQ,TRUNC(SYSDATE),nvl(GOODS.CODE,''-''),D.CODE
FROM BUSINVS A,WAREHOUSE WAREHOUSE,SORTNAME SORTNAME,STORE D,GOODS GOODS
WHERE A.GDGID=GOODS.GID AND A.WRH=WAREHOUSE.GID AND A.STORE=D.GID AND GOODS.SORT=SORTNAME.SCODE'
||' AND ('||V_WRH||
') AND ('||V_SOR||
') AND ('||V_OTH||
')AND (D.GID='||V_STORE||');';
EXECUTE IMMEDIATE V_SQL;
COMMIT;
V_ID := V_ID+1;
END LOOP;
COMMIT;
END;
