到货通知单一览

到货通知单一览

 SELECT B.SNAME AS ASNSTATUSNAME, A.ASNNO, D.SNAME AS COMPANYNAME, A.DDATE,
        C.SNAME AS INSTOCKTYPENAME, A.SHIPPERID  AS SHIPPERNAME, 
        F.SNAME AS SUPPLIERNAME, AD.INSTOCKINFORMNUM, AD.INNUM,AD.INFORMNUM, A.REMARK, A.CREATOR, G.SHORTNAME AS CREATORNAME,
        A.CREATEDATE, A.UPDATEMAN,H.SHORTNAME AS UPDATEMANNAME, A.UPDATEDATE,A.ASNSTATUS,A.INSTOCKTYPE,
        A.STOCKID,A.SHIPPERID ,A.SUPPLIERID,A.ISCROSS,A.SDATE,A.ACTIONFLG,
        A.OWNERID,A.ISBATCH,A.TRANSFLG,A.TREATOR,A.COMPANYID,A.BLINDREASON,S.SNAME AS STOCKNAME
   FROM INSTOCKINFORMTBL A,STOCKMST S,
        (SELECT   ID.ASNNO, SUM (ID.INSTOCKINFORMNUM) AS INSTOCKINFORMNUM, SUM (ID.INNUM) AS INNUM,SUM(M.INFORMNUM) AS INFORMNUM
             FROM INSTOCKINFORMDTTBL ID,INFORMDTTBL M
             WHERE ID.INFORMNO = M.INFORMNO(+) AND ID.GOODSID = M.GOODSID(+)
         GROUP BY ID.ASNNO) AD,
        BASEDETAILMST B,
        BASEDETAILMST C,
        CompanyMst D,
        CUSTOMERMST F,
        USERMST G,
        USERMST H
  WHERE A.ASNNO = AD.ASNNO
    AND B.CODEID = 'SYS_ASNSTATUS'
    AND A.ASNSTATUS = B.CODEVALUE
    AND C.CODEID = 'SYS_INSTCTYPE'
    AND A.INSTOCKTYPE = C.CODEVALUE
    AND A.OWNERID = D.OWNERID(+)
    AND A.COMPANYID = D.COMPANYID(+)
    AND A.SUPPLIERID = F.CUSTID(+)
    AND A.CREATOR = G.USERID
    AND A.UPDATEMAN = H.USERID(+)
    AND A.STOCKID=S.STOCKID

     AND A.ASNNO LIKE '%-%'
 ORDER BY A.UPDATEDATE DESC, A.ASNNO

CQP 细单 头

SELECT TT.TRANSFERTYPE,
       B.SNAME             AS ASNSTATUSNAME,
       A.ASNNO,
       D.SNAME             AS COMPANYNAME,
       A.DDATE,
       C.SNAME             AS INSTOCKTYPENAME,
       A.SHIPPERID         AS SHIPPERNAME,
       F.SNAME             AS SUPPLIERNAME,
       F.NAME              AS SUPPLIERFULLNAME,
       AD.INSTOCKINFORMNUM,
       AD.INNUM,
       AD.INFORMNUM,
       A.REMARK,
       A.CREATOR,
       G.SHORTNAME         AS CREATORNAME,
       A.CREATEDATE,
       A.UPDATEMAN,
       H.SHORTNAME         AS UPDATEMANNAME,
       A.UPDATEDATE,
       A.ASNSTATUS,
       A.INSTOCKTYPE,
       A.STOCKID,
       A.SHIPPERID,
       A.SUPPLIERID,
       A.ISCROSS,
       A.SDATE,
       A.ACTIONFLG,
       A.OWNERID,
       A.ISBATCH,
       A.TRANSFLG,
       A.TREATOR,
       A.COMPANYID,
       A.BLINDREASON,
       S.SNAME             AS STOCKNAME
  FROM INSTOCKINFORMTBL A,
       STOCKMST S,
       (SELECT ID.ASNNO,
               SUM(ID.INSTOCKINFORMNUM) AS INSTOCKINFORMNUM,
               SUM(ID.INNUM) AS INNUM,
               SUM(M.INFORMNUM) AS INFORMNUM
          FROM INSTOCKINFORMDTTBL ID, INFORMDTTBL M
         WHERE ID.INFORMNO = M.INFORMNO(+)
           AND ID.GOODSID = M.GOODSID(+)
         GROUP BY ID.ASNNO) AD,
       (select distinct A.ASNNO, C.TRANSFERTYPE
          from INSTOCKINFORMTBL A, INSTOCKINFORMDTTBL B, INFORMTBL C
         WHERE (A.ASNNO = B.ASNNO)
           AND B.INFORMNO = C.INFORMNO
           AND C.TRANSFERTYPE IS NOT NULL) TT,
       BASEDETAILMST B,
       BASEDETAILMST C,
       CompanyMst D,
       CUSTOMERMST F,
       USERMST G,
       USERMST H
 WHERE A.ASNNO = AD.ASNNO
   AND TT.ASNNO(+) = A.ASNNO
   AND B.CODEID = 'SYS_ASNSTATUS'
   AND A.ASNSTATUS = B.CODEVALUE
   AND C.CODEID = 'SYS_INSTCTYPE'
   AND A.INSTOCKTYPE = C.CODEVALUE
   AND A.OWNERID = D.OWNERID(+)
   AND A.COMPANYID = D.COMPANYID(+)
   AND A.SUPPLIERID = F.CUSTID(+)
   AND A.CREATOR = G.USERID
   AND A.UPDATEMAN = H.USERID
   AND A.STOCKID = S.STOCKID
   AND A.ASNNO = 'ANA92024041900056'
 ORDER BY A.UPDATEDATE DESC,
          A.ASNNO;

列表

           SELECT A.ASNNO,
                  A.INSTOCKINFORMDTID,
                  A.INFORMNO,
                  A.INFORMDTID,
                  A.PURCHASENO,
                  A.BILLTYPE,
                  A.GOODSID,
                  A.GOODSBATCH,
                  A.QUALITYFLG,
                  A.MID1,
                  A.INSTOCKINFORMNUM,
                  A.INNUM,
                  A.TRANSFLG,
                  A.CUSTGOODSID,
                  A.BIGUNITNUM,
                  A.MIDUNITNUM,
                  A.STANDUNITNUM,
                  A.VOLUME,
                  A.NETWEIGHT,
                  A.GROSSWEIGHT,
                  A.INBIGUNITNUM,
                  A.INMIDUNITNUM,
                  A.INSTANDUNITNUM,
                  A.PICKNUM,
                  A.INSTOCKQTY,
                  A.MID2,
                  A.MID2 AS OTHERNO,
                  A.MID3,
                  A.MID4,
                  A.MID5,
                  B.NAME AS GOODSNAME,
                  B.MODEL,
                  B.FULLMARK AS TRADEMARK,
                  b.Bigunit,
                  b.Standunit,
                  b.Form,
                  B.GOODSCATEGORYID,
                  B.GOODSTOPCATEGORYID,
                  C.SNAME AS QUALITYFLGNAME,
                  D.PRODUCTDATE,
                  D.EXPIREDATE,
                  IF.INFORMNUM,
                  IF.LEAVENUM,
                  A.OWNERID,
                  A.COMPANYID,
                  IF.PODTID,
                  CASE
                    WHEN B.TEMPTYPEID = '1' or B.TEMPTYPEID = '4' THEN
                     1
                    ELSE
                     0
                  END ISCOLD,
                  B.MAH
             FROM INSTOCKINFORMDTTBL A,
                  INFORMDTTBL        IF,
                  GOODSMST           B,
                  BASEDETAILMST      C,
                  GOODSBATCHMST      D
            WHERE A.GOODSID = B.GOODSID
              AND C.CODEID(+) = 'SYS_QUALITYFLAG'
              AND A.QUALITYFLG = C.CODEVALUE(+)
              AND A.GOODSID = D.GOODSID(+)
              AND A.GOODSBATCH = D.GOODSBATCH(+)
              AND A.INFORMNO = IF.INFORMNO(+)
              AND A.INFORMDTID = IF.INFORMDTID(+)
              AND A.ASNNo = 'ANA92024041900056'
            ORDER BY A.ASNNO, TO_NUMBER(A.INSTOCKINFORMDTID)