SELECT CASE
WHEN NVL(AA.GROUPID, '-') = '-' THEN
0
ELSE
1
END AS EXISTSCUSTGROUP,
AA.*
FROM (SELECT MAX(X.GROUPID) AS GROUPID,
TO_NUMBER(NVL(A.COLDBOXTYPE, '0')) AS COLDBOXTYPE,
F_GET_BASEDATA_NAME('SYS_URGENCYTYPE', A.URGENCYTYPE) AS URGENCYTYPE,
A.OUTSTOCKINDICATIONID,
A.OUTSTOCKINFORMID,
A.STATUS,
I.SNAME STATUSNAME,
A.OUTSTOCKTYPE,
J.SNAME OUTSTOCKTYPENAME,
B.RECEIVETIME INFORMCREATEDATE,
BB.ARRIVALDATE,
a.OWNERID,
A.COMPANYID,
C.SNAME COMPANYname,
A.CUSTID,
D.SNAME CUSTNAME,
D.CUSTTYPE,
K.SNAME CUSTTYPENAME,
BB.SHIPPERID,
BB.SHIPPERNAME,
BB.TRANSFERTYPE,
F_GET_BASEDATA_NAME('SYS_ORDERTYPE_PS', BB.ORDERTYPE) AS ORDERTYPE,
BB.TRANSFERTYPENAME,
BB.DELIVERAREAID,
BB.DELIVERAREANAME,
BB.DELIVERROUTEID,
BB.DELIVERROUTENAME,
A.ISURGENCY,
A.MFLAG,
A.REMARK,
A.CREATOR,
B.CREATEDATE4WMS CREATEDATE,
H.SHORTNAME CREATORNAME,
A.CONFIRMMAN,
A.CONFIRMDATE,
A.STOCKID,
A.UPDATEMAN,
A.UPDATEDATE,
A.INVOICENO,
A.ISCROSS,
A.QUALITYFLG,
A.ACTIONFLG,
A.ISINVOICENEED,
A.ISPAYNEED,
A.ISORDERMERGED,
A.BIGUNITNUM,
A.MIDUNITNUM,
A.STANDARDUNITNUM,
A.THISNUM,
A.BOXNUM,
A.PREOUTDATE,
OSP.PICKWAVEID,
A.CREATEDATE INDICATIONCREATEDATE,
round((TO_DATE(SUBSTR('2024-01-02 18:11:11', 1, 16),
'YYYY-MM-DD HH24:MI') -
TO_DATE(SUBSTR(A.ORDERDATE, 1, 16),
'YYYY-MM-DD HH24:MI')) * 24,
1) timeCount
FROM OUTSTOCKINDICATIONTBL A,
OUTSTOCKINFORMTBL B,
companymst C,
CUSTOMERMST D,
USERMST H,
OUTSTOCKINDICATIONDTTBL T,
GOODSMST S,
(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.ORDERTYPE,
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,
CUSTGROUPDETAIL X,
OUTSTOCKPICKTBL OSP,
OUTSTOCKPICKDTTBL OSPT
WHERE 1 = 1
And A.OwnerID = 'SFWL'
And (A.OutstockInformID LIKE '%HB0HBXS202401020009%')
AND (A.STATUS = '1' or A.STATUS = '2')
AND A.OUTSTOCKTYPE != '3'
AND B.OUTSTOCKINFORMID = A.OUTSTOCKINFORMID
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 A.OUTSTOCKINFORMID = BB.OUTSTOCKINFORMID
AND A.OUTSTOCKINDICATIONID = T.OUTSTOCKINDICATIONID
AND T.GOODSID = S.GOODSID
AND A.CUSTID = X.CUSTID(+)
AND A.OUTSTOCKINDICATIONID = OSPT.OUTSTOCKINDICATIONID(+)
AND OSPT.PICKINGTASKID = OSP.PICKINGTASKID(+)
GROUP BY TO_NUMBER(NVL(A.COLDBOXTYPE, '0')),
A.URGENCYTYPE,
A.OUTSTOCKINDICATIONID,
A.OUTSTOCKINFORMID,
A.STATUS,
I.SNAME,
A.OUTSTOCKTYPE,
J.SNAME,
B.RECEIVETIME,
BB.ARRIVALDATE,
a.OWNERID,
A.COMPANYID,
C.SNAME,
A.CUSTID,
D.SNAME,
D.CUSTTYPE,
K.SNAME,
BB.SHIPPERID,
BB.SHIPPERNAME,
BB.TRANSFERTYPE,
BB.ORDERTYPE,
BB.TRANSFERTYPENAME,
BB.DELIVERAREAID,
BB.DELIVERAREANAME,
BB.DELIVERROUTEID,
BB.DELIVERROUTENAME,
A.ISURGENCY,
A.MFLAG,
A.REMARK,
A.CREATOR,
B.CREATEDATE4WMS,
H.SHORTNAME,
A.CONFIRMMAN,
A.CONFIRMDATE,
A.STOCKID,
A.UPDATEMAN,
A.UPDATEDATE,
A.INVOICENO,
A.ISCROSS,
A.QUALITYFLG,
A.ACTIONFLG,
A.ISINVOICENEED,
A.ISPAYNEED,
A.ISORDERMERGED,
A.BIGUNITNUM,
A.MIDUNITNUM,
A.STANDARDUNITNUM,
A.THISNUM,
A.BOXNUM,
A.PREOUTDATE,
OSP.PICKWAVEID,
A.CREATEDATE,
TO_DATE(SUBSTR(A.ORDERDATE, 1, 16), 'YYYY-MM-DD HH24:MI')) AA
WHERE 1 = 1
ORDER BY AA.INFORMCREATEDATE
文档更新时间: 2024-01-02 18:20 作者：周骏
