出入库实绩对账

总数对账

with v_1 as(
         -- 入库总数 与SCM 对账
        SELECT SUM(A.NOTICEQTY) NOTICEQTY , A.GOODSID,A.LOTNO FROM WMSIFSTOCKINACTDETAILTBL A WHERE 1=1
         AND A.GOODSID ='1000544755' 
         AND A.LOTNO ='230906' 
         AND A.OWNERID='61'
         AND A.QUALITYFLG = 0
         group by A.GOODSID,A.LOTNO
),v_2 as(
          --出库 与SCM 对账
          SELECT SUM(A.NOTICEQTY) NOTICEQTY , A.GOODSID,A.LOTNO FROM WMSIFSTOCKOUTACTDETAILTBL A WHERE 1=1
          AND A.GOODSID ='1000544755' 
          AND A.LOTNO ='230906' 
          AND A.OWNERID='61'
          AND A.QUALITYFLG = 0
          group by A.GOODSID,A.LOTNO
)
select v_1.NOTICEQTY 入库实绩总数,v_2.NOTICEQTY 出库实绩总数，v_1.GOODSID,v_1.LOTNO  from v_1,v_2
          where v_1.LOTNO = v_2.LOTNO;

细单与SCM对账

    -- 入库总数 与SCM 对账 明细列表 
        SELECT A.NOTICEQTY , A.GOODSID,A.LOTNO,(SELECT DISTINCT B.TRANSTIME FROM WMSIFSTOCKINACTTBL B WHERE A.STOCKINNO = B.STOCKINNO AND ROWNUM=1) FROM WMSIFSTOCKINACTDETAILTBL A WHERE 1=1
         AND A.GOODSID ='1100028785' 
         AND A.LOTNO ='20079701' 
        -- AND A.OWNERID='61'
         AND A.QUALITYFLG = 2

     --出库 与SCM 对账 明细列表 
      SELECT A.NOTICEQTY, A.GOODSID,A.LOTNO ,(SELECT DISTINCT B.TRANSTIME FROM WMSIFSTOCKoutACTTBL B WHERE A.STOCKOUTNO = B.STOCKOUTNO AND ROWNUM=1) FROM WMSIFSTOCKOUTACTDETAILTBL A WHERE 1=1
          AND A.GOODSID ='1100028785' 
          AND A.LOTNO ='20079701' 
        --  AND A.OWNERID='61'
          AND A.QUALITYFLG = 2