装车单一览

列表页


 SELECT LO.LOADINGDOCID,LD.STATUS, E.SNAME NAME, LD.LOADINGDOCID, LD.SHIPPERID,LD.TRANSPORTATIONMODE AS TRANSFERTYPE , S.SNAME SHIPPNAME, 
       LD.TOTALITEMCASEQTY, LD.UNLOADINGPOINTNUMBER, LD.VEHICLELICENSE,NVL(SM.USERNM,LD.SENDMAN) SENDMAN, 
       LD.DRIVERNAME, LD.TELEPHONE, LD.PLATFORMID, LD.REMARK, 
       UL.USERNM LOADINGCONFIRMUSER,LOADINGCONFIRMTIME,US.USERNM SHIPPINGCONFIRMUSER,SHIPPINGCONFIRMTIME,SENDDOCIDGROUP,LN.VECHILEFEE  
  FROM LOADINGDOCTBL LD, 
       LOADEDORDERTBL LO, 
       OUTSTOCKINDICATIONTBL OS, 
       DELIVERTBL D, 
       (SELECT * 
          FROM BASEDETAILMST 
         WHERE CODEID = 'SYS_LOADINGDOC_STATUS') E, 
       SHIPPERMST S, 
       GOODSMST  G, 
       USERMST UL,USERMST SM, 
       USERMST US,LOADINGNOTICETBL LN 
 WHERE LD.LOADINGDOCID = LO.LOADINGDOCID(+) AND LO.GOODSID=G.GOODSID(+) 
   AND LO.OUTSTOCKINDICATIONID = OS.OUTSTOCKINDICATIONID(+) 
   AND D.OUTSTOCKINFORMID(+) = OS.OUTSTOCKINFORMID AND LD.SENDMAN=SM.USERID(+)
   AND LD.STATUS = E.CODEVALUE AND LD.LOADINGNOTICEID= LN.LOADINGNOTICEID(+) 
   AND LD.SHIPPERID = S.SHIPPERID(+) 
   AND LOADINGCONFIRMUSER=UL.USERID(+) 
   AND SHIPPINGCONFIRMUSER=US.USERID(+) 
   AND LD.LOADINGDOCID LIKE '%LD8020230625000001%'
   -- AND OSI.OUTSTOCKINDICATIONID = '030PLAO_6007286'
 GROUP BY LO.LOADINGDOCID,LD.STATUS, E.SNAME, LD.LOADINGDOCID, LD.SHIPPERID,LD.TRANSPORTATIONMODE, S.SNAME,
          LD.TOTALITEMCASEQTY, LD.UNLOADINGPOINTNUMBER, LD.VEHICLELICENSE,
          LD.DRIVERNAME, LD.TELEPHONE, LD.PLATFORMID, LD.REMARK,SM.USERNM,LD.SENDMAN,
       UL.USERNM ,LOADINGCONFIRMTIME,US.USERNM ,SHIPPINGCONFIRMTIME,SENDDOCIDGROUP,LN.VECHILEFEE  
 ORDER BY LD.LOADINGDOCID

装车单明细

头信息

 SELECT LOADINGDOCID, VEHICLELICENSE, DRIVERNAME, TELEPHONE, SHIPPERID,
       TRANSPORTATIONMODE, TO_NUMBER(TOTALITEMCASEQTY) TOTALITEMCASEQTY, TO_NUMBER(UNLOADINGPOINTNUMBER) UNLOADINGPOINTNUMBER, ROUTE,
       MILES, TRANSFERPOINT, PLATFORMID,CREATEDATE, LOADINGCONFIRMTIME,
       SHIPPINGCONFIRMTIME, STATUS, BILLNOS, LOADINGNOTICEID, REMARK
       ,CREATOR,UPDATEMAN,UPDATEDATE, LOADINGCONFIRMUSER, LOADINGCONFIRMTIME,
       SHIPPINGCONFIRMUSER, SHIPPINGCONFIRMTIME, ACTIONFLG,SENDMAN,SENDDOCIDGROUP,RANDOMCHEAKID,
    OWNERID,STOCKID,TRANSFLG,SENDDOCIDGROUP,THERMOMETERID
    ,ACCEPTTIME,HANDOVERTIME,F_GET_USERNAME(MODIFYMAN) AS MODIFYMAN,MODIFYTIME
  FROM LOADINGDOCTBL
 WHERE LOADINGDOCID = 'LD8020230625000001'

装车指示信息



 SELECT   LO.SENDDOCID, C.SNAME, SUM (OSID.THISNUM) THISNUM,
         SUM (LO.SHIPPEDNUM) LOADNUM,D.DELIVERADDRESS, D.DELIVERROUTEID,DR.NAME DELIVERROUTENAME,
         D.DELIVERAREAID,DA.NAME DELIVERAREANAME, D.DELIVERID, OSI.ISURGENCY, OSI.MFLAG, OSI.CUSTID,
         OSI.OUTSTOCKINDICATIONID,C.THERMOMETERTYPE
    FROM LOADEDORDERTBL LO,
         OUTSTOCKINDICATIONTBL OSI,
         DELIVERTBL D,
         OUTSTOCKINDICATIONDTTBL OSID,
         CUSTOMERMST C,
         DELIVERAREAMST DA,
         DELIVERROUTEMST DR
   WHERE LO.OUTSTOCKINDICATIONID = OSI.OUTSTOCKINDICATIONID
     AND LO.OUTSTOCKINDICATIONID = OSID.OUTSTOCKINDICATIONID
     AND LO.OUTSTOCKINDICATIONDTID = OSID.OUTSTOCKINDICATIONDTID
     AND OSI.CUSTID = C.CUSTID
     AND OSI.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID
     AND D.DELIVERAREAID = DA.DELIVERAREAID
     AND D.DELIVERAREAID = DR.DELIVERAREAID AND D.DELIVERROUTEID = DR.DELIVERROUTEID
        AND LO.LOADINGDOCID = 'LD8020230625000001'
 GROUP BY LO.SENDDOCID,C.THERMOMETERTYPE,
         C.SNAME,
         D.DELIVERADDRESS,
         D.DELIVERROUTEID,
         D.DELIVERAREAID,
         D.DELIVERID,
         OSI.ISURGENCY,
         OSI.MFLAG,
         OSI.CUSTID,OSI.OUTSTOCKINDICATIONID,
         DR.NAME,DA.NAME 
 ORDER BY LO.SENDDOCID,OSI.OUTSTOCKINDICATIONID

出库指示商品信息

 SELECT  D.DELIVERADDRESS,LO.OUTSTOCKINDICATIONID, LO.GOODSID,G.NAME GOODSNAME, G.MODEL, G.TRADEMARK,OSID.PURCHASENO,
       OSID.MID1, LO.GOODSBATCH, OSID.THISNUM THISNUM, LO.SHIPPEDNUM, LO.QUALITYFLG,Q.SNAME QUALITYFLGNAME
      ,0 ID,LO.OUTSTOCKINDICATIONDTID, OSID.MID2, OSID.MID3, OSID.MID4, OSID.MID5,OSI.COMPANYID
      ,LO.LOADBIGUNITNUM,LO.LOADMIDUNITNUM,LO.LOADSTANDUNITNUM,O.SNAME COMPANYNAME, LO.SHIPPEDNUM LOADNUM
     , CASE WHEN LO.INNERCODE1='-' THEN LO.INNERCODE2 ELSE LO.INNERCODE1||'-'||SUBSTR(LO.INNERCODE2,5,6) END  INNERCODE 
     ,GB.GOODSBATCH,GB.SCMLOCK,GB.PRODUCTDATE PDATE,GB.EXPIREDATE,OSF.CUSTID,'-' PICKINGTASKID,'-' OUTLABELNO,OSI.OUTSTOCKTYPE 
      ,OSID.PRICE
  FROM LOADEDORDERTBL LO,
       GOODSMST G,
       OUTSTOCKINDICATIONTBL OSI,
       COMPANYMST O,
       OUTSTOCKINDICATIONDTTBL OSID,
       DELIVERTBL D
       ,(SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_QUALITYFLAG') Q,OUTSTOCKINFORMTBL OSF,
      GOODSBATCHMST GB 
 WHERE LO.OUTSTOCKINDICATIONID = OSI.OUTSTOCKINDICATIONID
   AND LO.OUTSTOCKINDICATIONID = OSID.OUTSTOCKINDICATIONID
   AND OSI.OUTSTOCKINFORMID=OSF.OUTSTOCKINFORMID(+)
   AND LO.OUTSTOCKINDICATIONDTID = OSID.OUTSTOCKINDICATIONDTID
   AND OSI.COMPANYID = O.COMPANYID
   AND Q.CODEVALUE =  LO.QUALITYFLG
   AND LO.GOODSID = G.GOODSID
 AND GB.GOODSID = LO.GOODSID
 AND GB.GOODSBATCH = LO.GOODSBATCH
     AND OSI.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID
   AND LO.LOADINGDOCID = 'LD8020230625000001'

装车商品检索

SELECT A.PICKINGTASKID,
       A.OUTLABELNO,
       A.ISJOIN,
       B.OUTSTOCKINDICATIONID,
       B.GOODSID,
       B.status,
       F.NAME GOODSNAME,
       B.GOODSBATCH,
       G.PRODUCTDATE,
       B.QUALITYFLG,
       Q.SNAME QUALITYFLGNAME,
       B.MID1,
       B.MID2,
       B.MID3,
       B.MID4,
       B.MID5,
       F.TEMPTYPEID,
       F.MODEL,
       F.TRADEMARK,
       0 ID,
       0 ShippedNum,
       B.OUTSTOCKINDICATIONDTID,
       b.COMPANYID,
       C.CUSTID,
       E1.SNAME COMPANYNAME,
       E2.SNAME CUSTNAME,
       G.PRODUCTDATE PDATE,
       G.EXPIREDATE,
       '0' SAVED,
       0 SELECTED,
       SUM(nvl(B.ACTUALPICKINGBIGUNITNUM, 0)) LOADBIGUNITNUM,
       SUM(nvl(B.INDIMIDUNITNUM, 0)) LOADMIDUNITNUM,
       SUM(nvl(B.ACTUALPICKINGSTANDUNITNUM, 0)) LOADSTANDUNITNUM,
       SUM(nvl(B.ACTUALPICKINGNUM, 0)) THISNUM,
       SUM(nvl(B.ACTUALPICKINGNUM, 0)) LOADNUM,
       B.PICKAREAID,
       C.LOADINGNOTICEID,
       '-' ISCROSS,
       a.STAGINGZONEID,
       a.STAGINGLOCATIONID,
       '-' BOXID,
       c.OUTSTOCKTYPE,
       nvl(a.COLDPACKFLG, '0') COLDPACKFLG,
       CASE
         WHEN f.TEMPTYPEID = '1' or f.TEMPTYPEID = '4' THEN
          1
         ELSE
          0
       END ISCOLD
  FROM OUTSTOCKPICKTBL A,
       OUTSTOCKPICKDTTBL B,
       OUTSTOCKINDICATIONTBL C,
       DELIVERTBL D,
       COMPANYMST E1,
       CUSTOMERMST E2,
       GOODSMST F,
       GOODSBATCHMST G,
       (SELECT CODEVALUE, SNAME
          FROM BASEDETAILMST
         WHERE CODEID = 'SYS_QUALITYFLAG') Q,
       BASEDETAILMST BD
 WHERE (A.PICKINGTASKID = B.PICKINGTASKID)
   AND B.OUTSTOCKINDICATIONID = C.OUTSTOCKINDICATIONID
   AND C.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID
   AND b.COMPANYID = E1.COMPANYID(+)
   AND C.CUSTID = E2.CUSTID(+)
   AND B.GOODSID = F.GOODSID
   AND B.QUALITYFLG = Q.CODEVALUE
   AND B.GOODSID = G.GOODSID(+)
   AND B.GOODSBATCH = G.GOODSBATCH(+)
   AND A.STATUS = BD.CODEVALUE
   AND BD.CODEID = 'SYS_PICKSTATUS'
   AND A.STATUS != '7'
   AND D.TRANSFERTYPE <> '1'
   AND D.TRANSFERTYPE <> 'O'
   AND A.STATUS = 'B'
   AND B.OUTSTOCKINDICATIONID LIKE '%07930240988%'
   AND C.OWNERID = 'CQP'
   AND C.STOCKID = '80'
 GROUP BY A.PICKINGTASKID,
          A.OUTLABELNO,
          B.OUTSTOCKINDICATIONID,
          B.GOODSID,
          F.NAME,
          B.GOODSBATCH,
          B.QUALITYFLG,
          B.MID1,
          B.MID2,
          B.MID3,
          B.MID4,
          B.MID5,
          A.ISJOIN,
          B.QUALITYFLG,
          Q.SNAME,
          F.TEMPTYPEID,
          F.MODEL,
          F.TRADEMARK,
          B.OUTSTOCKINDICATIONDTID,
          b.COMPANYID,
          C.CUSTID,
          E1.SNAME,
          E2.SNAME,
          G.PRODUCTDATE,
          G.EXPIREDATE,
          A.ISJOIN,
          b.PICKAREAID,
          B.STATUS,
          C.LOADINGNOTICEID,
          a.STAGINGZONEID,
          a.STAGINGLOCATIONID,
          c.OUTSTOCKTYPE,
          nvl(a.COLDPACKFLG, '0'),
          CASE
            WHEN f.TEMPTYPEID = '1' or f.TEMPTYPEID = '4' THEN
             1
            ELSE
             0
          END
文档更新时间: 2023-12-14 15:36   作者:周骏