查询语句

--同品同批货品统计
SELECT POID, goodsid, lotno, COUNT(*) as count_records
FROM ERPIFINFORMDTTBL
WHERE POID in('PLAI_2516432')
GROUP BY POID, goodsid, lotno
HAVING COUNT(*) > 1;


--入库实绩表
SELECT  A.*,A.rowid   FROM WMSIFSTOCKINACTTBL A WHERE  A.PURCHASENO in('PLAI_2516432');
SELECT  A.*,A.rowid   FROM WMSIFSTOCKINACTDETAILTBL A WHERE  A.PURCHASENO in('PLAI_2516432');


--接口表与收货实绩                 
SELECT A.LINE_NUM, A.GOODSID,A.LOTNO,A.NOTICEQTY,A.INSTOCKQTY,A.*,A.rowid FROM ERPIFINFORMDTTBL A WHERE POID = 'PLAI_2516432' ORDER BY A.GOODSID;
SELECT A.GOODSID,A.GOODSBATCH,A.INSTOCKSMALLQTY,A.*,A.rowid FROM INSTOCKFACTDTTBL A WHERE 
    INSTOCKID in (SELECT DISTINCT C.INSTOCKID
                      FROM INFORMTBL A, INSTOCKINFORMDTTBL B, INSTOCKTBL C
                     WHERE A.PURCHASENO = B.PURCHASENO
                       AND B.ASNNO = C.ASNNO
                       AND A.PURCHASENO = 'PLAI_2516432') ORDER BY INSTOCKID,A.GOODSID;
--ERPIFINFORMDTTBL 表传过的，在零售系统不显示的，就将 NOTICEQTY, INSTOCKQTY 设为相等
--INSTOCKFACTDTTBL 表中的INSTOCKSMALLQTY 需要将 ERPIFINFORMDTTBL 中的SUM(NOTICEQTY);



--入库单统计                 
SELECT DISTINCT C.INSTOCKID
          FROM INFORMTBL A, INSTOCKINFORMDTTBL B, INSTOCKTBL C
         WHERE A.PURCHASENO = B.PURCHASENO
           AND B.ASNNO = C.ASNNO
           AND A.PURCHASENO in('PLAI_2516432') ORDER BY C.INSTOCKID;

--更新收货数量化到0
UPDATE ERPIFINFORMDTTBL SET INSTOCKQTY=0 WHERE POID in(      
'PLAI_2516432'
);

SELECT A.*,A.rowid FROM INSTOCKTBL A WHERE 1=1 AND A.INSTOCKID='ANA92024051500043-01' ;--收货清单

`




--同品同批货品统计
SELECT SOID, goodsid, lotno, COUNT(*) as count_records
FROM ERPIFOUTFORMDETAILTBL
WHERE SOID in('LSY_146723')
GROUP BY SOID, goodsid, lotno
HAVING COUNT(*) > 1

SELECT A.*,A.rowid FROM ERPIFOUTFORMTBL A WHERE A.SOID IN ('LSY_146723');
SELECT A.*,A.rowid FROM ERPIFOUTFORMDETAILTBL A WHERE A.SOID IN ('LSY_146723');

--出库实绩表
SELECT  A.*,A.rowid FROM WMSIFSTOCKOUTACTTBL A WHERE  A.STOCKOUTNO in('54DLSY_146723');
SELECT  A.*,A.rowid  FROM WMSIFSTOCKOUTACTDETAILTBL A WHERE  A.STOCKOUTNO in('54DLSY_146723');



`

从 零售系统查询 11023

自定义查询条件
goodsqty-nvl(executeqty,0)<>0

右键导出EXCEL
需要保留的字段有

将EXCEL转化为文本

正则表达式批量替换

--包含整数的小数
(0|([1-9][0-9]*))(\.[\d]+)?)
--查找目标
(\d{7})\t(\d{7})\t(\d{10})\t(-?(0|([1-9][0-9]*))(\.[\d]+)?)

--替换为
UPDATE ERPIFINFORMDTTBL SET NOTICEQTY = \4,INSTOCKQTY= 0 WHERE  POID ='PLAI_\1' AND GOODSID ='\3'  AND LINE_NUM='\2';UPDATE INSTOCKFACTDTTBL SET INSTOCKSMALLQTY = \4  WHERE GOODSID ='\3'  AND QUALITYFLG = 0 AND INSTOCKID IN \( SELECT DISTINCT C.INSTOCKID FROM INFORMTBL A ,INSTOCKINFORMDTTBL B,INSTOCKTBL C WHERE A.PURCHASENO=B.PURCHASENO AND B.ASNNO= C.ASNNO AND A.PURCHASENO='PLAI_\1' \);

入库实绩生成是修改 ERPIFINFORMDTTBL 的INSTOCKQTY 字段值，删除WMSIFSTOCKINACTDETAILTBL表的 同品同批行

出库实绩生成是修改ERPIFOUTFORMDETAILTBL 的NOTICEQTY 值，删除WMSIFSTOCKOUTACTDETAILTBL表的 同品同批行

生成出库实绩