收货单一览

 SELECT
 A.APPOINTMENTTIMEBEGIN,A.APPOINTMENTTIMEEND,TO_NUMBER(A.APPOINTMENTFLG) AS APPOINTMENTFLG,A.FORCEREASON,A.STATION,A.INSTOCKID, A.ASNNO, A.STATUS, A.INSTOCKTYPE, A.PLATFORMNO,ISF.BLINDREASON, 
    A.STOCKID, A.SHIPPERID, A.SUPPLIERID, A.OWNERID, A.ISCROSS, A.DDATE,IMG.ADDRESS,IMG.CONTACTOR,IMG.CUSTTELEPHONE,
    A.SDATE, A.CREATOR, A.CREATETIME, A.UPDATEMAN, A.UPDATEDATE, A.REMARK,
    A.ACTIONFLG, A.ISBATCH, A.TRANSFLG, A.TREATOR, A.INVOICENO,
    A.INFORMDATE, A.COMPANYID, D.SNAME AS COMPANYNAME,O.SNAME AS OWNERNAME,
    A.SHIPPERID AS SHIPPERNAME,F.SNAME AS SUPPLIERNAME,B.SNAME StatusName,C.SNAME InstockTypeName, AD.LEAVENUM,AD.INNUM, 
    AD.GOODSID 
   FROM INSTOCKTBL A,VIEW_INSTOCK_SUPPLIERINFO IMG,
    (SELECT T.INSTOCKID,SUM(T.LEAVENUM) LEAVENUM,SUM(T.INNUM) INNUM,MAX(T.GOODSID) AS GOODSID FROM
    (SELECT ID.INSTOCKID, ID.INSTOCKINFORMNUM - ID.INNUM LEAVENUM,ID.INNUM,ID.INSTOCKDTID||'-'||G.GOODSID  AS GOODSID
       FROM INSTOCKDTTBL ID,GOODSMST G
       WHERE ID.GOODSID = G.GOODSID 
     ) T

     GROUP BY T.INSTOCKID) AD,
     INSTOCKINFORMTBL  ISF,
    CompanyMst D,
    CUSTOMERMST F,
    (SELECT CODEVALUE,SNAME FROM BaseDetailMst WHERE CODEID='SYS_INSTOCKTATUS') B,
    (SELECT CODEVALUE,SNAME FROM BaseDetailMst WHERE CODEID='SYS_INSTCTYPE') C,
    OWNERMST  O
  WHERE A.INSTOCKID = AD.INSTOCKID
  AND A.ASNNO=ISF.ASNNO
  AND A.OWNERID = O.OWNERID
  AND A.COMPANYID = D.COMPANYID(+)

  AND A.SUPPLIERID = F.CUSTID(+)
  AND A.STATUS = B.CODEVALUE(+)
  AND A.INSTOCKTYPE = C.CODEVALUE(+)
  AND A.INSTOCKID =IMG.INSTOCKID(+)
  AND 1 = IMG.GPS_LNO(+)
  AND A.INSTOCKID='AN802023061800001-01'
 ORDER BY A.CREATETIME DESC

收货单明细

  SELECT          B.BZXS,B.FZDW,C.SEQNO AS GOODSBATCHSEQ,A.INSTOCKTYPE,AD.INSTOCKID, AD.INSTOCKDTID, AD.INFORMNO, AD.INFORMDTID, AD.PURCHASENO, AD.BILLTYPE, AD.UPDATEDATE,
        AD.GOODSID, AD.GOODSBATCH, AD.QUALITYFLG, AD.MID1, AD.INSTOCKINFORMNUM, AD.INNUM, AD.TRANSFLG,
        AD.OWNERID, 0 BIGUNITNUM, 0 MIDUNITNUM, AD.INSTOCKINFORMNUM - AD.INNUM STANDUNITNUM, AD.INSTOCKINFORMNUM - AD.INNUM APPLYNUM,
        AD.VOLUME,
        AD.NETWEIGHT, AD.GROSSWEIGHT, AD.INBIGUNITNUM, AD.INMIDUNITNUM,
        AD.INSTANDUNITNUM, AD.PICKNUM, AD.PALLETNO, AD.MID2, AD.MID3, AD.MID4, AD.MID5,
        B.NAME AS GOODSNAME, B.MODEL, B.TRADEMARK,B.TEMPERATURE,C.PRODUCTDATE, C.EXPIREDATE,D.SNAME AS QUALITYFLGNAME, 
        A.ASNNO, TO_CHAR(AD.INNUM)|| '/' || TO_CHAR(AD.INSTOCKINFORMNUM) AS INNUMANDINFORMNUM,
        B.LICENSENUMBER, B.BIGUNIT, B.STANDUNIT, B.REPORTFLG, B.BIOLOGYFLG, B.FULLMARK,B.DOCKLAYER,B.DOCKQTY,B.DOCKSUM,
        TO_CHAR(B.DOCKLAYER) || '-' || TO_CHAR(B.DOCKQTY) || '-' || TO_CHAR(B.DOCKSUM) AS DOCK,
        TO_CHAR(B.LENGTH) || ' X ' || TO_CHAR(B.WIDTH) || ' X ' || TO_CHAR(B.HEIGHT) AS STANDCAPLWH,
        B.MAH,B.FACTORYNAME,B.LICENSENUMBER, C.BIGPERNUM, B.BIGUNIT, B.STANDUNIT,B.REPORTFLG,B.BIOLOGYFLG, B.GOODSCATEGORYID, 
        B.STANDCAP, B.STANDGROSSWEIGHT STANDNETWEIGHT, C.HAVECHE,
        E.SNAME AS GOODSTOPCATEGORYNAME, F.SNAME AS GOODSCATEGORYNAME,B.GOODSTOPCATEGORYID,A.COMPANYID,NVL(G.REMARK,'') INSTOCKAREA, B.REMARK GOODSREMARK,
        TO_NUMBER(NVL(AD.CHECKFLG,0)) CHECKFLG,TO_NUMBER(NVL(AD.UNUSUALFLG,0)) UNUSUALFLG, 
        CASE WHEN B.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_COOL' OR B.TEMPTYPEID='SYS_TEMPTYPESYS_TEMPTYPE_FROZEN' THEN 1 ELSE 0 END ISCOLD, 
        B.ISHSKUBARCODE,B.SKUBARCODE 
   FROM INSTOCKTBL A, 
        INSTOCKDTTBL AD, 
        GOODSMST B,
        GOODSBATCHMST C,
        (SELECT CODEVALUE,SNAME FROM BASEDETAILMST WHERE CODEID='SYS_QUALITYFLAG') D,
        (SELECT CODEVALUE,SNAME FROM BASEDETAILMST WHERE CODEID='SYS_GOODSTOPCATEGORY') E,
        (SELECT CODEVALUE,SNAME FROM BASEDETAILMST WHERE CODEID='SYS_GOODSCATEGORY') F,
        (SELECT DISTINCT A.OWNERID,TO_NUMBER(A.BUSINESSSCOPE) BUSINESSSCOPE,A.REMARK FROM PRM_INSTOCKSPLIT A 
        WHERE A.ACTIONFLG = '1') G
  WHERE A.INSTOCKID = AD.INSTOCKID
    AND AD.GOODSID = B.GOODSID
    AND AD.GOODSID = C.GOODSID(+)
    AND AD.GOODSBATCH = C.GOODSBATCH(+)
    AND AD.QUALITYFLG = D.CODEVALUE(+)
    AND B.GOODSTOPCATEGORYID = E.CODEVALUE
    AND B.GOODSCATEGORYID = F.CODEVALUE
     AND INSTR(B.BUSINESSSCOPE,'1') = G.BUSINESSSCOPE(+) 
     AND B.OWNERID = G.OWNERID(+) 
     AND  AD.INSTOCKID = 'AN802023121900812-01'
 ORDER BY AD.INSTOCKID, TO_NUMBER(AD.INSTOCKDTID)

CQP 分仓版本

SELECT
    A.STOCKID, A.SHIPPERID, A.SUPPLIERID, A.OWNERID, A.ISCROSS, A.DDATE,
    A.SDATE, A.CREATOR, A.CREATETIME, A.UPDATEMAN, A.UPDATEDATE, A.REMARK,
    A.ACTIONFLG, A.ISBATCH, A.TRANSFLG, A.TREATOR, A.INVOICENO,
    A.INFORMDATE, A.COMPANYID, D.SNAME AS COMPANYNAME,O.SNAME AS OWNERNAME,
    A.SHIPPERID AS SHIPPERNAME,F.SNAME AS SUPPLIERNAME,B.SNAME StatusName,C.SNAME InstockTypeName, AD.LEAVENUM,AD.INNUM, 
    AD.GOODSID 
   FROM INSTOCKTBL A,
    (SELECT T.INSTOCKID,SUM(T.LEAVENUM) LEAVENUM,SUM(T.INNUM) INNUM,MAX(T.GOODSID) AS GOODSID FROM
    (SELECT ID.INSTOCKID, ID.INSTOCKINFORMNUM - ID.INNUM LEAVENUM,ID.INNUM,ID.INSTOCKDTID||'-'||G.GOODSID  AS GOODSID
       FROM INSTOCKDTTBL ID,GOODSMST G
       WHERE ID.GOODSID = G.GOODSID 
     ) T

     GROUP BY T.INSTOCKID) AD,
     INSTOCKINFORMTBL  ISF,
    CompanyMst D,
    CUSTOMERMST F,
    (SELECT CODEVALUE,SNAME FROM BaseDetailMst WHERE CODEID='SYS_INSTOCKTATUS') B,
    (SELECT CODEVALUE,SNAME FROM BaseDetailMst WHERE CODEID='SYS_INSTCTYPE') C,
    OWNERMST  O
  WHERE A.INSTOCKID = AD.INSTOCKID
  AND A.ASNNO=ISF.ASNNO
  AND A.OWNERID = O.OWNERID
  AND A.COMPANYID = D.COMPANYID(+)
  AND A.SUPPLIERID = F.CUSTID(+)
  AND A.STATUS = B.CODEVALUE(+)
  AND A.INSTOCKTYPE = C.CODEVALUE(+)
  AND A.INSTOCKID='ANA92024051500036-01'
 ORDER BY A.CREATETIME DESC