到货确认

头单

SELECT   OSI.OUTSTOCKINDICATIONID, LO.LOADINGDOCID,
         SUM (LO.SHIPPEDNUM) SHIPPEDNUM, SUM (LO.ARRIVENUM) ARRIVENUM,
         OSI.OUTSTOCKTYPE, C.CUSTTYPE, P.SNAME DELIVERAREAID, Q.SNAME DELIVERROUTEID,
         D.DELIVERID, LD.SHIPPINGCONFIRMTIME FACTARRIVALDATE, OSI.ARRIVALDATE, C.SNAME CUSTID,
           CASE WHEN  NVL(A.ARRIVALSTATUS,'0')='1'  THEN '0' ELSE CASE WHEN LD.SHIPPINGCONFIRMTIME<=TO_CHAR(SYSDATE-2, 'YYYY-MM-DD HH24:MI:SS') THEN '1'  ELSE '0' END  END  TIMEOUTFLAG  ,
          MAX( CASE WHEN G.TEMPTYPEID IN  ('1','4') THEN 1  ELSE 0 END ) ISCOLD, 
         O.SNAME OWNERID, S.SNAME SHIPPERID, D.TRANSFERTYPE, OSI.ISURGENCY, OSI.MFLAG,NVL(OSI.PRINTSEQ,0) PRINTSEQ,
         A.ARRVIVALREMARK AS REMARK,NVL(CP.SNAME,'-') AS SNAME,OSI.UNUSUALARRIVALREMARK,U.SHORTNAME AS UNUSUALARRIVALMAN,OSI.UNUSUALARRIVALDATE,MAX(LO.SENDDOCID) AS SENDDOCID,
         LD.vehiclelicense,VE.MANAGEGROUP,LD.SENDMAN,A.ARRIVALSTATUS 
    FROM OUTSTOCKINDICATIONTBL OSI,
         DELIVERTBL D,
         OUTSTOCKINDICATIONDTTBL OSID,
         LOADEDORDERTBL LO,
         CUSTOMERMST C,
         LOADINGDOCTBL LD,
         ARRIVALINFOTBL A,
         SHIPPERMST S,
         OWNERMST O,
         deliverareamst P,
         deliverroutemst Q,
         COMPANYMST   CP, 
          GOODSMST   G,VECHILEMST VE,
         USERMST U
   WHERE OSI.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID
     AND OSI.OUTSTOCKINDICATIONID = OSID.OUTSTOCKINDICATIONID
     AND OSID.OUTSTOCKINDICATIONID = LO.OUTSTOCKINDICATIONID
     AND OSID.OUTSTOCKINDICATIONDTID = LO.OUTSTOCKINDICATIONDTID
     AND C.CUSTID = OSI.CUSTID
     AND O.OWNERID = OSI.Ownerid
     AND LO.SENDDOCID IS NOT NULL
     AND LO.OUTSTOCKINDICATIONID = A.OUTSTOCKINDICATIONID(+)
     AND LO.LOADINGDOCID = A.LOADINGDOCID(+)
     AND LD.LOADINGDOCID = LO.LOADINGDOCID
     AND LD.SHIPPERID = S.SHIPPERID(+)
     AND D.DELIVERAREAID = P.DELIVERAREAID(+) 
     AND D.DELIVERROUTEID = Q.DELIVERROUTEID(+) 
     AND D.DELIVERAREAID = Q.DELIVERAREAID(+) 
     AND OSI.COMPANYID=CP.COMPANYID(+)  
     AND OSI.UNUSUALARRIVALMAN = U.USERID(+)
     AND OSID.GOODSID=G.GOODSID(+) AND LD.VEHICLELICENSE=VE.VECHILEMARK(+)
     AND OSI.OUTSTOCKINDICATIONID LIKE  '%43051824469%'


      GROUP BY OSI.OUTSTOCKINDICATIONID,
         LO.LOADINGDOCID,
         OSI.OUTSTOCKTYPE,
         C.CUSTTYPE,
         P.SNAME,
         Q.SNAME,
         D.DELIVERID,
            LD.SHIPPINGCONFIRMTIME,
         OSI.ARRIVALDATE,
         C.SNAME,
         O.SNAME,
         S.SNAME,
         D.TRANSFERTYPE,
         OSI.ISURGENCY,
         OSI.MFLAG,
         A.ARRVIVALREMARK,NVL(A.ARRIVALSTATUS,'0'),VE.MANAGEGROUP, LD.SENDMAN,OSI.PRINTSEQ,
         CP.SNAME,OSI.UNUSUALARRIVALREMARK,OSI.UNUSUALARRIVALDATE,U.SHORTNAME,LD.vehiclelicense,A.ARRIVALSTATUS
         ORDER BY OSI.ARRIVALDATE DESC

明细

SELECT LD.GOODSID, G.NAME, G.MODEL, G.TRADEMARK, O.SNAME MID1, LD.Goodsbatch, G.TEMPTYPEID, G.TEMPERATURE,
       Q.SNAME QUALITYFLG, LD.SHIPPEDNUM, (LD.SHIPPEDNUM - NVL(LD.BACKNUM,0)) ARRIVENUM, LD.BACKNUM ,LD.BACKREASON, LD.LOADINGDOCID , LD.LOADINGDOCDTID
      , LD.INNERCODE1,LD.INNERCODE2,LD.SENDDOCID,NVL(CP.SNAME,'-') AS SNAME,CM.CUSTID,CM.SNAME  AS CUSTNAME,OI.OUTSTOCKINDICATIONID,CP.COMPANYID,LD.SENDDOCID||'_'||LD.OUTSTOCKINDICATIONID TRANSINFO
      ,decode(AR.report,null,null,'http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'||replace(AR.report,',',';http://yjbg.cq-p.com.cn:11000/CQP_SHTXD/'))  REPORT
  FROM LOADEDORDERTBL LD, GOODSMST G,COMPANYMST CP
     ,(SELECT CODEVALUE,SNAME FROM BASEDETAILMST
         WHERE CODEID = 'SYS_QUALITYFLAG'
             AND ACTIONFLG = '1') Q
     ,OWNERMST O,OUTSTOCKINDICATIONTBL  OI,CUSTOMERMST CM
    ,ARRIVALAUTOINFOTBL AR
 WHERE LD.GOODSID = G.GOODSID
   AND LD.QUALITYFLG = Q.CODEVALUE
   AND O.OWNERID(+) = LD.MID1 
   AND LD.OUTSTOCKINDICATIONID = '43051824469'
   AND LD.LOADINGDOCID = 'LD8020241114000348'
   AND LD.SENDDOCID IS NOT NULL
   AND OI.COMPANYID=CP.COMPANYID
   AND LD.OUTSTOCKINDICATIONID=OI.OUTSTOCKINDICATIONID
   AND OI.CUSTID=CM.CUSTID
   AND LD.LOADINGDOCID=AR.LOADINGDOCID(+) AND LD.INNERCODE1=AR.INNERCODE1(+) AND LD.INNERCODE2=AR.INNERCODE2(+) AND LD.OUTSTOCKINDICATIONID=AR.OUTSTOCKINDICATIONID(+)
 ORDER BY LD.GOODSID, LD.Goodsbatch