装车单一览
列表页
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 作者:周骏