新对账sql总库存与分公司库存总数对账

检查分公司库存小于总库存，需要补库存的情况

--出库指示
SELECT A.*,A.rowid FROM OUTSTOCKINDICATIONDTTBL A WHERE 1=1 AND A.OUTSTOCKINDICATIONID='01048175022' AND GOODSID='1110580607' and  GOODSBATCH='BXK33L1'; 
--出库通知
SELECT A.*,A.rowid FROM OUTSTOCKINFORMDTTBL A WHERE 1=1 AND A.OUTSTOCKINFORMID='01048175022' AND GOODSID='1110580607' and  GOODSBATCH='BXK33L1' ;



--总库存
select a.bigunitnum,
       a.bigunitnum * b.bigpernum bigcount,
       a.standunitnum,
       a.*,
       a.rowid
  from stockgoodstbl a, goodsbatchmst b
 where a.goodsid = b.goodsid
   and a.goodsbatch = b.goodsbatch
   and a.goodsid = '1110580607'
   and a.goodsbatch = 'BXK33L1'
   and a.stockareaid = '2'
   and a.bigunitnum + a.standunitnum > 0;


SELECT A.*,A.rowid FROM STOCKGOODSTBL_COMPANY A WHERE  GOODSID='1110580607' AND GOODSBATCH='BXK33L1'  AND A.TOTALNUM >0; --在线库存


 --------------新对账sql总库存与分公司库存总数对账 -------------------------
with
t1 as (SELECT  GOODSID,GOODSBATCH,QUALITYFLG,SUM(totalnum) AS totalnum  
       FROM (
              SELECT a.GOODSID,a.GOODSBATCH,A.QUALITYFLG,sum(a.bigunitnum*b.bigpernum)+sum(a.standunitnum) totalnum 
                 FROM STOCKGOODSTBL a,goodsbatchmst b
                where a.goodsid = b.goodsid and a.goodsbatch = b.goodsbatch
                  and (a.bigunitnum > 0 or a.standunitnum > 0)
                  and a.goodsid<>'0000000000'
                  AND A.GOODSID='1110580607' AND A.GOODSBATCH='BXK33L1'
                GROUP BY a.GOODSID,a.GOODSBATCH,A.QUALITYFLG
               UNION ALL
               SELECT B.GOODSID,B.GOODSBATCH,B.QUALITYFLG,SUM(B.ACTUALPICKINGNUM) AS totalnum  FROM OUTSTOCKPICKTBL A,OUTSTOCKPICKDTTBL  B 
                WHERE A.PICKINGTASKID=B.PICKINGTASKID
                  AND A.STATUS IN('B','C')
                 AND B.GOODSID='1110580607' AND B.GOODSBATCH='BXK33L1'
                GROUP  BY B.GOODSID,B.GOODSBATCH,B.QUALITYFLG
              )  AA 
        GROUP  BY GOODSID,GOODSBATCH,QUALITYFLG
        ),
t2 as (SELECT GOODSID,GOODSBATCH,QUALITYFLG,sum(totalnum) totalnum FROM STOCKGOODSTBL_COMPANY a
        where totalnum > 0
        and a.goodsid<>'0000000000'
        AND a.GOODSID='1110580607' AND a.GOODSBATCH='BXK33L1'
        GROUP BY GOODSID,GOODSBATCH,QUALITYFLG) 
select t1.*,t2.*
  from t1
  full join t2 on t1.goodsid = t2.goodsid and t1.goodsbatch = t2.goodsbatch  and t1.qualityflg = t2.qualityflg
 where nvl(t1.totalnum,0) != nvl(t2.totalnum,0)
 order  by 1;

WMS物流有问题的库存

with
t1 as (SELECT a.GOODSID,a.GOODSBATCH,a.qualityflg,sum(a.bigunitnum*b.bigpernum)+sum(a.standunitnum) totalnum 
         FROM STOCKGOODSTBL a,goodsbatchmst b
        where a.goodsid = b.goodsid and a.goodsbatch = b.goodsbatch
          and (a.bigunitnum > 0 or a.standunitnum > 0)
          and a.goodsid<>'0000000000'
        GROUP BY a.GOODSID,a.GOODSBATCH,a.qualityflg),
t2 as (SELECT GOODSID,GOODSBATCH,a.qualityflg,sum(totalnum) totalnum FROM STOCKGOODSTBL_COMPANY a
        where totalnum > 0
        and a.goodsid<>'0000000000'
        GROUP BY GOODSID,GOODSBATCH,a.qualityflg) 

select 'select T.COMPANYID,T.QUALITYFLG,T.GOODSID,T.GOODSBATCH,tt.bigpernum*T.BIGUNITNUM+T.STANDUNITNUM totalnum,T.GOODSPOSID '||chr(13)||
       '  from stockgoodstbl t,goodsbatchmst tt '||chr(13)||
       ' where t.goodsid = '''||nvl(t1.goodsid,t2.goodsid)||''' and t.goodsbatch = '''||nvl(t1.goodsbatch,t2.goodsbatch)||''''||chr(13)||
       '   and t.goodsid = tt.goodsid and t.goodsbatch = tt.goodsbatch;'||CHR(13)||
       'select T.COMPANYID,T.QUALITYFLG,T.GOODSID,T.GOODSBATCH,T.TOTALNUM,t.*,t.rowid '||chr(13)||
       '  from stockgoodstbl_company t '||chr(13)||
       ' where t.goodsid = '''||nvl(t1.goodsid,t2.goodsid)||''' and t.goodsbatch = '''||nvl(t1.goodsbatch,t2.goodsbatch)||''';'
        SQL, t1.*,t2.*
  from t1
  full join t2 on t1.goodsid = t2.goodsid and t1.goodsbatch = t2.goodsbatch and t1.qualityflg = t2.qualityflg
 where nvl(t1.totalnum,0) != nvl(t2.totalnum,0)
 ORDER BY NVL(T1.GOODSID,T2.GOODSID);

批次关系与总库存/分公司库存对账

--批次现在由这个表记录关系，批次库存直接取分公司库存
SELECT A.*, A.rowid
  FROM MID1BATCHRELATIONTBL A
 WHERE 1 = 1
   AND A.COMPANYID = '43'
   AND A.GOODSID = '1000416572'
   AND A.GOODSBATCH = '2403453A';

   SELECT A.*, A.rowid
  FROM STOCKGOODSTBL_COMPANY A
 WHERE 1 = 1
   AND a.goodsid='1000416572' and a.goodsbatch='2403453A'; --分公司库存


   SELECT A.GOODSID,
       A.QUALITYFLG,
       A.OWNERID,
       A.BOXID,
       A.GOODSBATCH,
       A.GOODSAREAID,
       A.GOODSPOSID,
       A.Bigunitnum,
       A.BIGUNITNUM * C.BIGPERNUM AS BIGSUM,
       A.STANDUNITNUM,
       A.MID1
  FROM STOCKGOODSTBL A, GOODSBATCHMST C
 WHERE A.GOODSID = C.GOODSID
   AND A.GOODSBATCH = C.GOODSBATCH
   AND a.goodsid='1000416572' and a.goodsbatch='2403453A'
   AND A.BIGUNITNUM + A.STANDUNITNUM > 0
union all
SELECT A.GOODSID,
       A.QUALITYFLG,
       B.OWNERID,
       B.RELACONTAINERNO AS BOXID,
       A.GOODSBATCH,
       B.STAGINGZONEID AS GOODSAREAID,
       B.STAGINGLOCATIONID AS GOODSPOSID,
       A.ACTUALPICKINGBIGUNITNUM AS Bigunitnum,
       A.ACTUALPICKINGBIGUNITNUM * C.BIGPERNUM AS BIGSUM,
       A.ACTUALPICKINGSTANDUNITNUM AS STANDUNITNUM,
       A.MID1
  FROM OUTSTOCKPICKDTTBL A, OUTSTOCKPICKTBL B, GOODSBATCHMST C
 WHERE A.GOODSID = C.GOODSID
   AND A.GOODSBATCH = C.GOODSBATCH
   AND A.PICKINGTASKID = B.PICKINGTASKID
   AND a.goodsid='1000416572' and a.goodsbatch='2403453A'
   AND A.ACTUALPICKINGBIGUNITNUM + A.ACTUALPICKINGSTANDUNITNUM > 0
   AND A.STATUS in ('B', 'C'); --总库存

与零售对账

--零售
SELECT PST.WMSCENTERUCODE 仓库, ENT.MDMCODE 分公司, LST.GOODSID SKU, LOT.LOTNO 批号,LST.batchid, LST.GOODSSTATUSID INCA品质, PZ.WMSGSTATUSCODE WMS品质, BTH.BATCHNO 物流生成批次号, SUM(LST.GOODSQTY) 库存数量
  FROM CQPLSBK.BMS_ST_QTY_LST@DBLK_HPLS LST, CQPLSBK.BMS_BATCH_DEF@DBLK_HPLS BTH, CQPLSBK.BMS_LOT_DEF@DBLK_HPLS LOT, CQPLSBK.BMS_ST_DEF@DBLK_HPLS ST, CQPLSBK.PUB_ENTRY@DBLK_HPLS ENT, CQPLSBK.PUB_STORER@DBLK_HPLS PST, CQPLSBK.PUB_GOODSSTATUS_MATCHCODE@DBLK_HPLS PZ
 WHERE LST.GOODSID = BTH.GOODSID AND LST.GOODSID = LOT.GOODSID AND LST.BATCHID = BTH.BATCHID AND LST.LOTID = LOT.LOTID AND LST.STORAGEID = ST.STORAGEID AND ST.ENTRYID = ENT.ENTRYID AND ST.PHYSTOREID = PST.STORERID AND PST.USEWMS = 1 
   AND PST.WMSCENTERUCODE IN ('80','BJ') AND ST.ENTRYID = PZ.ENTRYID AND LST.GOODSSTATUSID = PZ.GOODSSTATUSID 
   AND LST.GOODSID = '1110559294' AND LOT.LOTNO = '2206003' 
 GROUP BY PST.WMSCENTERUCODE,ENT.MDMCODE,LST.GOODSID,LOT.LOTNO,BTH.BATCHNO,LST.GOODSSTATUSID,PZ.WMSGSTATUSCODE,LST.batchid
 ORDER BY LST.GOODSID, LOT.LOTNO, ENT.MDMCODE, PZ.WMSGSTATUSCODE;

与英克ERP对账

-------英克批发库存查询
SELECT E.STORERNO 仓库编码,F.MDMCODE 分公司编码,A.GOODSID SKU,B.LOTNO 批号,C.BATCHNO 物流生成批次号,A.GOODSSTATUSID 品质,SUM(A.GOODSQTY) 库存数量
  FROM CQYYPROD.BMS_ST_QTY_LST@INCAPF A,
       CQYYPROD.BMS_LOT_DEF@INCAPF    B,
       CQYYPROD.BMS_BATCH_DEF@INCAPF  C,
       CQYYPROD.BMS_ST_DEF@INCAPF     D,
       CQYYPROD.PUB_STORER@INCAPF     E,
       CQYYPROD.PUB_ENTRY@INCAPF      F
 WHERE A.LOTID = B.LOTID
   AND A.BATCHID = C.BATCHID
   AND A.STORAGEID = D.STORAGEID
   AND D.PHYSTOREID = E.STORERID
   AND D.ENTRYID = F.ENTRYID
   AND E.STORERNO = '80'
   --and F.MDMCODE='50'
   AND A.GOODSID = '1110641262'
   AND B.LOTNO = '3031753'
 GROUP BY E.STORERNO,
          F.MDMCODE,
          A.GOODSID, 
          B.LOTNO,
          C.BATCHNO,
          A.GOODSSTATUSID
 ORDER BY A.GOODSID, B.LOTNO, F.MDMCODE;

 ---WMS 出入库实绩对账

select c.ownerid,c.stockid,c.总单ID ,c.GOODSID, c.lotno,sum(c.noticeqty),c.qualityflg，c.inout,c.transtime, c.stocktype from 
((select a.ownerid,a.stockid,a.orderno as 总单ID ,a.GOODSID,  a.lotno,a.noticeqty,a.qualityflg,'出库' as inout ,aa.transtime,aa.outstocktype as stocktype from WMSIFSTOCKOUTACTDETAILTBL A  inner join WMSIFSTOCKOUTACTTBL  aa on a.stockoutno=aa.stockoutno
where a.ownerid='03'  and a.GOODSID='1100028785' and a.lotno='21079701' )
union all
(select b.ownerid,b.stockid, b.purchaseno as 总单ID ,b.GOODSID, b.lotno,b.noticeqty,b.qualityflg,'入库' as inout,bb.transtime,bb.instocktype as stocktype from WMSIFSTOCKINACTDETAILTBL  B   inner join WMSIFSTOCKINACTTBL  bb on b.stockinno=bb.stockinno
where b.ownerid='03'  and b.GOODSID='1100028785' and b.lotno='21079701')
union all
(select d.consignor,d.warehouseid, d.adjustid as 总单ID ,d.itemid, d.lotno,d.quantity,d.quality,'库存调整' as inout,dd.adjustdate,'库存调整' as stocktype from ifstockadjdetailtbl d inner join  ifstockadjtbl dd  on d.adjustid=dd.adjustid
where  d.consignor='03' and d.itemid='1100028785' and d.lotno='21079701')
) C  
group by c.ownerid,c.stockid,c.总单ID ,c.GOODSID, c.lotno,c.qualityflg,c.inout,c.transtime,c.stocktype ORDER BY c.inout,c.transtime,c.总单ID ;

文档更新时间: 2024-09-24 18:04 作者：周骏

新对账sql总库存与分公司库存总数对账

检查分公司库存小于总库存，需要补库存的情况

WMS物流有问题的库存

批次关系 与 总库存/分公司库存对账

与零售对账

与英克ERP对账

批次关系与总库存/分公司库存对账