在库变更查询

在库变革查询 （品质变更查询）

头单

 SELECT DISTINCT A.CHANGEID, A.STOCKID, A.CHANGETIME, A.CHANGETYPE, 
                A.CHANGEREASON, A.STATUS, E.SHORTNAME AS CREATOR, 
                A.CREATEDATE, A.UPDATEMAN, A.UPDATEDATE, A.ACTIONFLG, 
                A.TRANSFLG, A.CYHTRANSFLG, A.REMARK, A.CONFIRMMAN, 
                A.CONFIRMDATE, B.SNAME AS CHANGETYPENM, 
                C.SNAME AS CHANGEREASONNM 
           FROM CHANGESTOCKTBL A, 
                BASEDETAILMST B, 
                BASEDETAILMST C, 
                USERMST E, 
                CHANGESTOCKOLDDTTBL D 
          WHERE A.CHANGEID = D.CHANGEID 
            AND A.CHANGETYPE = B.CODEVALUE 
            AND B.CODEID = 'SYS_CHANGETYPE' 
            AND A.CHANGEREASON = C.CODEVALUE(+) 
            AND C.CODEID(+) = 'SYS_CHANGEREASON' 
            AND A.CREATOR = E.USERID(+) 
            AND B.ACTIONFLG(+) = '1' 
            AND C.ACTIONFLG(+) = '1' 
            AND D.GOODSID = '1000284603'
            AND D.GOODSBATCH='12007079'
            ORDER BY  A.CREATEDATE DESC

变更前在库表

 SELECT   (  SUM (A.BIGUNITNUM) * C.BIGPERNUM 
          + SUM (A.MIDUNITNUM) * C.MIDPERNUM 
          + SUM (A.STANDUNITNUM) 
         ) AS TOTOLNUM, 
         A.OWNERID, A.GOODSID, A.MID1, A.MID2, A.MID3, A.MID4, A.GOODSPOSID,
         A.MID5, A.STANDUNITNUM, A.MIDUNITNUM, A.BIGUNITNUM, A.PURCHASENO, 
         A.GOODSBATCH, D.NAME AS QUALITYFLGNM, A.ISALLOWOUT, B.NAME AS SNAME, B.MODEL, 
         B.TRADEMARK, C.PRODUCTDATE, C.EXPIREDATE ,A.COMPANYID,E.SNAME AS COMPANYNAME
    FROM CHANGESTOCKOLDDTTBL A, GOODSMST B, GOODSBATCHMST C , BASEDETAILMST D,COMPANYMST E
   WHERE A.CHANGEID = 'H80202306290001' 
     AND A.GOODSID = B.GOODSID 
     AND D.CODEID(+) = 'SYS_QUALITYFLAG' 
     AND A.QUALITYFLG = D.CODEVALUE 
     AND A.GOODSID = C.GOODSID(+) 
     AND A.GOODSBATCH = C.GOODSBATCH(+) 
     AND A.COMPANYID = E.COMPANYID(+) 
     AND A.OWNERID = E.OWNERID(+) 
     AND (A.BIGUNITNUM <> '0' OR A.MIDUNITNUM <> '0' OR A.STANDUNITNUM <> '0' 
         ) 
     AND B.ACTIONFLG(+) = '1' 
     AND C.ACTIONFLG(+) = '1' 
GROUP BY A.OWNERID, 
         A.OWNERID, 
         C.BIGPERNUM, 
         C.MIDPERNUM, 
         A.GOODSID, 
         A.MID1, 
         A.MID2, 
         A.MID3, 
         A.MID4, 
         A.GOODSPOSID, 
         A.MID5, 
         A.STANDUNITNUM, 
         A.MIDUNITNUM, 
         A.BIGUNITNUM, 
         A.PURCHASENO, 
         A.GOODSBATCH, 
         A.QUALITYFLG, 
         A.ISALLOWOUT, 
         B.NAME, 
         B.MODEL, 
         B.TRADEMARK, 
         C.PRODUCTDATE, 
         C.EXPIREDATE, 
         D.NAME, 
         A.COMPANYID, 
         E.SNAME 

变更后在库表

SELECT   (  SUM (A.BIGUNITNUM) * C.BIGPERNUM 
          + SUM (A.MIDUNITNUM) * C.MIDPERNUM 
          + SUM (A.STANDUNITNUM) 
         ) AS TOTOLNUM, 
         A.OWNERID, A.OWNERID, A.GOODSID, A.MID1, A.MID2, A.MID3, A.MID4, A.GOODSPOSID,
         A.MID5, A.STANDUNITNUM, A.MIDUNITNUM, A.BIGUNITNUM, A.PURCHASENO, 
         A.GOODSBATCH, D.NAME AS QUALITYFLGNM, A.ISALLOWOUT, B.NAME AS SNAME, B.MODEL, 
         B.TRADEMARK, C.PRODUCTDATE, C.EXPIREDATE ,A.COMPANYID,E.SNAME AS COMPANYNAME
    FROM CHANGESTOCKNEWDTTBL A, GOODSMST B, GOODSBATCHMST C, BASEDETAILMST D ,COMPANYMST E
   WHERE A.CHANGEID = 'H80202306290001'
     AND A.GOODSID = B.GOODSID 
     AND D.CODEID(+) = 'SYS_QUALITYFLAG' 
     AND A.QUALITYFLG = D.CODEVALUE 
     AND A.GOODSID = C.GOODSID(+) 
     AND A.GOODSBATCH = C.GOODSBATCH(+) 
     AND A.COMPANYID = E.COMPANYID(+) 
     AND A.OWNERID = E.OWNERID(+) 
     AND (A.BIGUNITNUM <> '0' OR A.MIDUNITNUM <> '0' OR A.STANDUNITNUM <> '0' 
         ) 
     AND B.ACTIONFLG(+) = '1' 
     AND C.ACTIONFLG(+) = '1' 
GROUP BY A.OWNERID, 
         A.OWNERID, 
         C.BIGPERNUM, 
         C.MIDPERNUM, 
         A.GOODSID, 
         A.MID1, 
         A.MID2, 
         A.MID3, 
         A.MID4, 
         A.MID5, 
         A.GOODSPOSID, 
         A.STANDUNITNUM, 
         A.MIDUNITNUM, 
         A.BIGUNITNUM, 
         A.PURCHASENO, 
         A.GOODSBATCH, 
         A.QUALITYFLG, 
         A.ISALLOWOUT, 
         B.NAME, 
         B.MODEL, 
         B.TRADEMARK, 
         C.PRODUCTDATE, 
         C.EXPIREDATE, 
         D.NAME, 
         A.COMPANYID, 
         E.SNAME