出库指示单
头单
SELECT A.STATUS, I.SNAME STATUSNAME, A.OUTSTOCKINDICATIONID, A.OUTSTOCKTYPE, A.ORDERNO,A.ISORDERMERGED,
J.SNAME OUTSTOCKTYPENAME, BB.DELIVERAREAID, BB.DELIVERAREANAME,
BB.DELIVERROUTEID, BB.DELIVERROUTENAME, D.CUSTTYPE,
K.SNAME CUSTTYPENAME, A.ORDERDATE, BB.ARRIVALDATE,
A.CONFIRMDATE FACTOUTSTOCKDATE, AI.ARRIVALDATE AS FACTARRIVEDATE,A.OWNERID,A.COMPANYID,
C.SNAME COMPANYNAME,A.RELATEDORDERID, A.CUSTID, D.SNAME CUSTNAME, BB.SHIPPERID,
BB.SHIPPERNAME, BB.TRANSFERTYPE, BB.TRANSFERTYPENAME, A.ISURGENCY,A.TMSTRANSFLG,A.TMSTRANSMSG,A.TMSTRANSDATE,
A.MFLAG, A.REMARK, A.RELATEDORDERID,
N.OUTSTOCKWAVEID, N.PICKWAVEID,A.OUTSTOCKINFORMID,
A.THISNUM,SUM(O.CACHENUM) CACHENUM,SUM(O.PREPICKNUM) PREPICKNUM, SUM (O.PICKNUM) PICKNUM, SUM (O.STAGINGNUM) STAGINGNUM,
SUM (O.LOADNUM) LOADNUM, SUM (O.SHIPPINGNUM) SHIPPINGNUM,
SUM (O.ARRIVENUM) ARRIVENUM, SUM (O.BACKNUM) BACKNUM
FROM OUTSTOCKINDICATIONTBL A,
OUTSTOCKINFORMDTTBL ZZ,
OUTWAVEDETAILTBL N,
OUTSTOCKPROCESSDTTBL O,
GOODSMST S,
COMPANYMST C,
CUSTOMERMST D,
USERMST H,
( SELECT MAX(A.ARRIVALDATE) AS ARRIVALDATE ,A.OUTSTOCKINDICATIONID FROM ARRIVALINFOTBL A GROUP BY A.OUTSTOCKINDICATIONID) AI,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_OUTSTOCKINDI_STATUS') I,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_OUTSTCTYPE') J,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_CUSTOMERTYPE') K,
(SELECT B.OUTSTOCKINFORMID, B.ARRIVALDATE, B.SHIPPERID,
E.SNAME SHIPPERNAME, B.TRANSFERTYPE,
R.SNAME TRANSFERTYPENAME, B.DELIVERAREAID,
F.SNAME DELIVERAREANAME, B.DELIVERROUTEID,
G.SNAME DELIVERROUTENAME
FROM DELIVERTBL B,
SHIPPERMST E,
DELIVERAREAMST F,
DELIVERROUTEMST G,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_TRANCFERTYPE') R
WHERE E.SHIPPERID = B.SHIPPERID
AND F.DELIVERAREAID = B.DELIVERAREAID
AND G.DELIVERROUTEID = B.DELIVERROUTEID
AND G.DELIVERAREAID = B.DELIVERAREAID
AND R.CODEVALUE = B.TRANSFERTYPE) BB
WHERE 1=1
AND N.OUTSTOCKINDICATIONID(+) = A.OUTSTOCKINDICATIONID
AND C.OWNERID = A.OWNERID
AND C.COMPANYID = A.COMPANYID
AND D.CUSTID = A.CUSTID
AND H.USERID(+) = A.CREATOR
AND I.CODEVALUE = A.STATUS
AND J.CODEVALUE = A.OUTSTOCKTYPE
AND K.CODEVALUE = D.CUSTTYPE
AND O.OUTSTOCKINDICATIONID = A.OUTSTOCKINDICATIONID
AND S.GOODSID=O.GOODSID
AND A.OUTSTOCKINFORMID = BB.OUTSTOCKINFORMID
AND ZZ.OUTSTOCKINFORMID = A.OUTSTOCKINFORMID
AND ZZ.GOODSID = O.GOODSID
AND A.OUTSTOCKINDICATIONID=AI.OUTSTOCKINDICATIONID(+)
AND A.OUTSTOCKINDICATIONID='2808827957'
GROUP BY A.STATUS,A.ISORDERMERGED,
I.SNAME,
A.OUTSTOCKINDICATIONID,
A.OUTSTOCKTYPE,
A.ORDERNO,
J.SNAME,
BB.DELIVERAREAID,
BB.DELIVERAREANAME,
BB.DELIVERROUTEID,
BB.DELIVERROUTENAME,
D.CUSTTYPE,
K.SNAME,
A.ORDERDATE,
BB.ARRIVALDATE,
A.CONFIRMDATE ,
AI.ARRIVALDATE,
A.OWNERID,
A.COMPANYID,
C.SNAME,
A.CUSTID,
D.SNAME,
BB.SHIPPERID,
BB.SHIPPERNAME,
BB.TRANSFERTYPE,
BB.TRANSFERTYPENAME,
A.ISURGENCY,
A.MFLAG,
A.REMARK,
A.RELATEDORDERID,
N.OUTSTOCKWAVEID,
N.PICKWAVEID,A.TMSTRANSFLG,A.TMSTRANSMSG,A.TMSTRANSDATE,
A.THISNUM,
A.OUTSTOCKINFORMID
ORDER BY A.ORDERDATE DESC细单
SELECT A.GOODSID, B.NAME, B.TRADEMARK, B.MODEL, A.MID1, A.MID2,A.GOODSBATCH,A.COMPANYID,D.SNAME COMPANYNAME,C.RELATEDORDERID,
A.QUALITYFLG, E.SNAME QUALITYFLGNAME,F.THISNUM,A.CACHENUM,A.PREPICKNUM,
F.STANDUNITNUM,A.PICKNUM, A.STAGINGNUM, A.LOADNUM, A.SHIPPINGNUM, A.ARRIVENUM,
A.BACKNUM,A.OUTSTOCKINDICATIONID,G.ME_LNO,G.ME_EXTENDNO,G.ME_GOODSCODE
FROM OUTSTOCKPROCESSDTTBL A,
GOODSMST B,
OUTSTOCKINDICATIONTBL C,
OUTSTOCKINDICATIONDTTBL F,
OUTSTOCKINFORMDTTBL G,
COMPANYMST D,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_QUALITYFLAG') E
WHERE A.OUTSTOCKINDICATIONID = '2808827957'
AND A.GOODSID = B.GOODSID
AND A.OUTSTOCKINDICATIONID = C.OUTSTOCKINDICATIONID
AND A.OUTSTOCKINDICATIONID = F.OUTSTOCKINDICATIONID
AND A.OUTSTOCKINDICATIONDTID = F.OUTSTOCKINDICATIONDTID
AND A.OUTSTOCKINFORMID = G.OUTSTOCKINFORMID
AND F.GOODSID = G.GOODSID AND F.GOODSBATCH=G.GOODSBATCH
AND A.QUALITYFLG = E.CODEVALUE
AND A.COMPANYID = D.COMPANYID
GROUP BY A.GOODSID,B.NAME,B.TRADEMARK,B.MODEL,A.MID1,A.MID2,A.GOODSBATCH,A.COMPANYID,D.SNAME,C.RELATEDORDERID,
A.QUALITYFLG,E.SNAME,F.THISNUM,A.CACHENUM,A.PREPICKNUM,F.STANDUNITNUM,A.PICKNUM,A.STAGINGNUM,A.LOADNUM,A.SHIPPINGNUM,
A.ARRIVENUM,A.BACKNUM,A.OUTSTOCKINDICATIONID,G.ME_LNO,G.ME_EXTENDNO,G.ME_GOODSCODE出库指示明细
检索台头数据
--检索台头数据
SELECT A.OUTSTOCKINDICATIONID, A.OUTSTOCKTYPE, A.ORDERNO, A.RELATEDORDERID,
A.COMPANYID, C.SNAME OWNERNAME, A.CUSTID, D.SNAME CUSTNAME, A.ISURGENCY,
A.MFLAG, A.STATUS, B.PICKWAVEID, B.OUTSTOCKWAVEID, B.SORTERID,
B.STAGINGZONEID, A.REMARK, A.OUTSTOCKINFORMID,A.RELATEDORDERID
FROM OUTSTOCKINDICATIONTBL A, OUTWAVEDETAILTBL B, OWNERMST C, CUSTOMERMST D
WHERE A.OUTSTOCKINDICATIONID = '{0}'
AND A.OUTSTOCKINDICATIONID = B.OUTSTOCKINDICATIONID(+)
AND A.OWNERID = C.OWNERID
AND A.CUSTID = D.CUSTID明细部数据(出库指示数据)
SELECT B.GOODSID, C.NAME GOODSNAME, C.MODEL, C.TRADEMARK, B.MID1, B.MID2,
B.GOODSBATCH, B.THISNUM, B.QUALITYFLG,
F.SNAME QUALITYFLGNAME, G.PRODUCTDATE, G.EXPIREDATE, A.RETURNFLG,D.SNAME AS COMPANYNAME,A.RELATEDORDERID
FROM OUTSTOCKINDICATIONTBL A,
OUTSTOCKINDICATIONDTTBL B,
GOODSMST C,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_QUALITYFLAG') F,
GOODSBATCHMST G,
COMPANYMST D
WHERE A.OUTSTOCKINDICATIONID = '{0}'
AND A.OUTSTOCKINDICATIONID = B.OUTSTOCKINDICATIONID
AND B.GOODSID = C.GOODSID
AND F.CODEVALUE = B.QUALITYFLG
AND B.GOODSID = G.GOODSID(+)
AND B.GOODSBATCH = G.GOODSBATCH(+)
AND A.COMPANYID=D.COMPANYID
ORDER BY B.OUTSTOCKINDICATIONDTID明细部数据(出库实绩数据)
SELECT B.GOODSID, C.NAME GOODSNAME, C.MODEL, C.TRADEMARK,B.MID1, B.MID2,
B.GOODSBATCH, B.SHIPPINGNUM THISNUM, B.QUALITYFLG,
F.SNAME QUALITYFLGNAME, G.PRODUCTDATE, G.EXPIREDATE,D.SNAME AS COMPANYNAME,A.RELATEDORDERID
FROM OUTSTOCKINDICATIONTBL A,
(SELECT OUTSTOCKINDICATIONID, GOODSID, GOODSBATCH, QUALITYFLG, MID1,
MID2, SUM (SHIPPINGNUM) SHIPPINGNUM
FROM OUTSTOCKINDICATIONSHIPDTTBL
WHERE OUTSTOCKINDICATIONID = '{0}'
HAVING SUM (SHIPPINGNUM) > 0
GROUP BY OUTSTOCKINDICATIONID,
GOODSID,
GOODSBATCH,
QUALITYFLG,
MID1,
MID2) B,
GOODSMST C,
(SELECT CODEVALUE, SNAME
FROM BASEDETAILMST
WHERE CODEID = 'SYS_QUALITYFLAG') F,
GOODSBATCHMST G,
COMPANYMST D
WHERE A.OUTSTOCKINDICATIONID = '{0}'
AND A.OUTSTOCKINDICATIONID = B.OUTSTOCKINDICATIONID
AND B.GOODSID = C.GOODSID
AND F.CODEVALUE = B.QUALITYFLG
AND B.GOODSID = G.GOODSID
AND B.GOODSBATCH = G.GOODSBATCH
AND A.COMPANYID=D.COMPANYID文档更新时间: 2023-06-28 10:30 作者：周骏
