SELECT CASE WHEN OS.COMPANYID IN('S1','S2') THEN '1' ELSE '0' END SHOWREMARK, CASE WHEN OS.COMPANYID IN('S1','S2') THEN F_GET_CUSTSNAME(OSID.MID3) ELSE '' END MID3, CASE WHEN OS.COMPANYID='94' THEN '1' ELSE '0' END lbl94printtime, CASE WHEN OS.COMPANYID='94' THEN '1' ELSE '0' END lbl94time, CASE WHEN OS.COMPANYID='94' THEN '1' ELSE '0' END lbl94, CASE WHEN OS.COMPANYID='94' THEN '1' ELSE '0' END TITLENAME, CASE WHEN OS.COMPANYID='94' AND G.GOODSTOPCATEGORYID='2' THEN '0' ELSE '1' END LBLCOMPANYNAME, CASE WHEN LO.GOODSID='1000468749' AND C.CUSTID='010021' AND OS.COMPANYID='94' THEN '1' ELSE '0' END remark94, CASE WHEN C.CUSTID='010090' AND INSTR(OS.REMARK,'线下采购')>0 THEN '0' WHEN C.CUSTID='010090' AND INSTR(OS.REMARK,'线下采购')=0 THEN '' WHEN C.CUSTID='010092' AND SUBSTR(NVL(OSID.ME_EXTENDNO,'XX'),1,2) ='22' THEN OSID.ME_EXTENDNO WHEN C.CUSTID='010092' AND SUBSTR(NVL(OSID.ME_EXTENDNO,'XX'),1,2)<>'22' THEN '' ELSE '' END LBLONLINE, C.ISPRINTINVOICEID,max( lo.randomcheakid) || '-' || (max(nvl(lo.PRINTCOUNT,0)) + 1) randomcheakid,OS.OUTSTOCKINDICATIONID AS INVOICENOID, OS.OUTSTOCKTYPE,D.ORDERTYPE,OS.COMPANYID,DECODE(OS.OUTSTOCKTYPE,'4', OS.OUTSTOCKINDICATIONID,
'9', OS.OUTSTOCKINDICATIONID,
'5', OS.OUTSTOCKINDICATIONID,
DECODE (CO.PRINTTYPE,
'0', LO.INNERCODE2,
'1', LO.INNERCODE1 ||'-'|| SUBSTR (INNERCODE2, 5,6)
)
)||'$'||OS.COMPANYID OUTSTOCKINDICATIONID,
DECODE(OS.OUTSTOCKTYPE,'4', OS.OUTSTOCKINDICATIONID,
'9', OS.OUTSTOCKINDICATIONID,
'5', OS.OUTSTOCKINDICATIONID,
DECODE (CO.PRINTTYPE,
'0', LO.INNERCODE2,
'1', LO.INNERCODE1 ||'-'|| SUBSTR (INNERCODE2, 5,6)
)
)||'$'||OS.COMPANYID BARCODE,
LO.SENDDOCID,LO.INNERCODE1,LO.INNERCODE2,SUBSTR(LO.OUTLABELNO,-5) AS OUTLABELNO,
DECODE (CO.PRINTTYPE, '0',G.GOODSCORRCODE || G.NAME ,
'1', G.NAME,
''
) NAME,
G.MODEL||' '||NVL(P.NAME,'') MODEL,G.MODEL MODEL1,
CASE WHEN G.TEMPTYPEID IN(
'1'， '4' )
THEN F_GET_BASEDATA_NAME('SYS_TEMPERATURE',G.TEMPERATURE) ELSE '' END TEMPERATURE4NEW, CASE WHEN G.GOODSTOPCATEGORYID='12' THEN G.FULLMARK ELSE CASE WHEN G.MAH=G.FULLMARK THEN G.FULLMARK ELSE G.FULLMARK || DECODE(NVL(G.MAH, G.FACTORYNAME),NULL,NULL,'/'||TO_CHAR(NVL(G.MAH, G.FACTORYNAME))) END END TRADEMARK, NVL(P.NAME,'') JX,G.SUMTYPE, LO.GOODSID, LO.GOODSBATCH,
GB.EXPIREDATE,GB.PRODUCTDATE||'/'||chr(10)||chr(13)||DECODE(GB.EXPIREDATE,'2888-01-01','-',GB.EXPIREDATE) AS PREX,NVL(Gb.LICENSENUMBER,G.LICENSENUMBER) LICENSENUMBER,GB.PRODUCTDATE,
SUM (NVL (LO.SHIPPEDNUM, 0)) PICKINGQTY,
CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(MAX(NVL(OSD.PRICE,0)),'fm9999990.0999') ELSE '-' END PRICE,
CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(MAX(NVL(OSID.OUTSYSUNITPRICE,0)),'fm9999990.0999') ELSE '-' END DISCOUNTPRICE,
CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(MAX(NVL(OSD.PRICENOTAX, 0)),'fm9999990.0999') ELSE '-' END PRICENOTAX,
CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(MIN(NVL(OSD.TAXRATE, 0)),'fm9999990.0999') ELSE '-' END TAXRATE, CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(ROUND(SUM(NVL(LO.SHIPPEDNUM, 0)) * MAX(NVL(OSD.PRICE, 0)), 2),'fm9999999990.0999') ELSE '-' END SUMMARY,
CASE WHEN NVL(CO.ISSHOWPRICE,'0')='1' THEN TO_CHAR(ROUND(SUM(NVL(LO.SHIPPEDNUM, 0)) * MAX(NVL(OSD.PRICENOTAX, 0)), 2),'fm9999999990.0999') ELSE '-' END PriceNoTaxSummary,
V.FAKEFLAG, NVL(F_GET_BASEDATA_NAME('SYS_TRANCFERTYPE',D.TRANSFERTYPE),'') || ' ' || NVL(F_GET_BASEDATA_NAME('SYS_ORDERTYPE_PS',D.ordertype),'') TRANSFERTYPE,
'' AS ORDERTYPE,
OS.ISINVOICENEED, G.STANDUNIT,FLOOR(GB.BIGPERNUM)BIGPERNUM ,NVL(OS.ISPAYNEED,'')ISPAYNEED,NVL(GB.NEEDCHE,'0') NEEDCHE,
CASE WHEN C.ISOTHER='01' THEN 0 WHEN C.ISOTHER ='02' AND G.IMPORTFLG ='1' THEN 1 WHEN C.ISOTHER ='02' AND G.IMPORTFLG ='0' THEN 0 WHEN C.ISOTHER='03' THEN 1 ELSE 0 END AS REPORTCOUNT, nvl(D.CONTACTOR,'') RELATOR,nvl(D.CUSTTELEPHONE,'') CONTACTTEL,G.GOODSTOPCATEGORYID,CO.PRINTROW,C.ISOTHER,
NVL(C.ISOTHER,'0')||NVL(GB.NEEDREPORTLOCK,'0') NEEDREPORTLOCK,
NVL(F_GET_BASEDATA_NAME('SYS_OUTSTCTYPE',OS.OUTSTOCKTYPE),'') OUTSTOCKTYPENAME,
LO.LOADBIGUNITNUM,LO.LOADSTANDUNITNUM
,CASE WHEN OS.OWNERID='CQP' THEN OSID.ME_EXTENDNO ELSE CASE WHEN OS.COMPANYID IN('G0','G2') THEN OSIT.EXTERNALSOID ELSE DECODE(OS.REMARK,NULL,OSID.ME_EXTENDNO,OSID.ME_EXTENDNO ||'-'|| OS.REMARK) END END ORDERID
,NVL(OSID.ISLPZ,'0') AS ISLPZ,C.CUSTID
,F_GETBUSINESSSCOPE(G.BUSINESSSCOPE) BUSINESSSCOPE
,CASE WHEN NVL(OSID.DLCG,'非带量采购')='非带量采购' THEN NULL ELSE '带量' END || CASE NVL(C.ISOTHER,'')
WHEN '01' THEN ''
WHEN '02' THEN
CASE WHEN NVL(G.IMPORTFLG,'0')='1' OR NVL(G.BIOLOGYFLG,'0')='1' THEN '*' ELSE '' END
WHEN '03' THEN
CASE WHEN NVL(G.IMPORTFLG,'0')='1' OR NVL(G.BIOLOGYFLG,'0')='1' THEN '*'
WHEN NVL(G.IMPORTFLG,'0') <>'1'AND NVL(G.BIOLOGYFLG,'0')<>'1' AND NVL(G.CERTIFIED,'0') ='1' THEN '#'
WHEN NVL(G.IMPORTFLG,'0') <>'1'AND NVL(G.BIOLOGYFLG,'0')<>'1' AND NVL(G.CERTIFIED,'0') ='0' AND NVL(GB.REPORTCOUNT,0) >0 THEN '#'
ELSE ''
End
ELSE ''
END REMARK
,NVL(F_GET_BASEDATA_NAME('SYS_TEMPERATURE',G.TEMPERATURE),'') TEMPERATURENAME
, Case When c.Isprintinvoiceid='1' then case when os.ownerid='SFWL' then nvl(OSID.SFINVOICENO,'-') else Nvl(Max(LPZ.INVOICENO),'') end Else '-' End INVOICENO ,MAX(PRINTCOUNT) PRINTCOUNT
FROM LOADINGDOCTBL LD,
GOODSMST G,
LOADEDORDERTBL LO,
OUTSTOCKINDICATIONDTTBL OSD,
OUTSTOCKINFORMDTTBL OSID,
VECHILEMST V,
DELIVERTBL D,
OUTSTOCKINDICATIONTBL OS,
GOODSBATCHMST GB,
OWNERMST O ,COMPANYMST CO,
CUSTOMERMST C,(SELECT * FROM BASEDETAILMST WHERE CODEID='SYS_GSPFORM') P
,LPZRELATEINFO LPZ ,OUTSTOCKINFORMTBL OSIT WHERE LO.GOODSID = G.GOODSID
AND OSIT.OUTSTOCKINFORMID=OSID.OUTSTOCKINFORMID AND OS.COMPANYID = CO.COMPANYID
AND NVL(CO.ISPRINT,'1')='1' AND OS.OWNERID = O.OWNERID
AND OS.OWNERID = 'CQP'
AND LD.STOCKID = '80'
AND OS.CUSTID = C.CUSTID
AND LO.OUTSTOCKINDICATIONID = OSD.OUTSTOCKINDICATIONID
AND LO.OUTSTOCKINDICATIONDTID = OSD.OUTSTOCKINDICATIONDTID
AND LO.OUTSTOCKINDICATIONID = OSID.OUTSTOCKINFORMID
AND CASE WHEN OS.OUTSTOCKTYPE='3' then LO.OUTSTOCKINDICATIONDTID else OSID.OUTSTOCKINFORMDTID end =LO.OUTSTOCKINDICATIONDTID
AND OS.OUTSTOCKINDICATIONID = OSD.OUTSTOCKINDICATIONID
AND OS.OUTSTOCKINFORMID = D.OUTSTOCKINFORMID
AND LD.VEHICLELICENSE = V.VECHILEMARK(+)
AND LD.LOADINGDOCID = LO.LOADINGDOCID
AND OSID.OUTSTOCKINFORMID = OS.OUTSTOCKINFORMID
AND OSID.GOODSID= LO.GOODSID
AND (OSID.GOODSBATCH = '-' OR LO.GOODSBATCH = OSID.GOODSBATCH)
AND OSID.QUALITYFLG = LO.QUALITYFLG AND G.FORM=P.CODEVALUE(+) AND OSD.GOODSBATCH = LO.GOODSBATCH
AND GB.GOODSID = LO.GOODSID
AND GB.GOODSBATCH = LO.GOODSBATCH
AND (C.CUSTTYPE <> '3' AND OS.COMPANYID<>'O3')
AND LO.LOADINGDOCID = 'LD8020231018000356'
AND (CASE WHEN LO.INNERCODE1='-' THEN LO.INNERCODE2 ELSE LO.INNERCODE1||'-'||SUBSTR(LO.INNERCODE2,5,6) end ) = '2310800764-800879'
AND G.TEMPTYPEID IN('2',
'1'， '4'， '0')
AND G.GOODSTOPCATEGORYID <> '12'
And osd.outstockindicationid=lpz.outstockinformid(+) And osd.goodsid=lpz.goodsid(+) And osd.goodsbatch=lpz.goodsbatch(+) GROUP BY OSIT.EXTERNALSOID,OSID.DLCG,G.PRODSITE,G.TEMPTYPEID, OSD.Outstockindicationdtid,OSID.MID3,G.MAH,G.FACTORYNAME,NVL(OSID.SFINVOICENO,'-'),OSID.ISLPZ,C.CUSTID,C.ISPRINTINVOICEID,LO.INNERCODE2,OS.OWNERID,OS.REMARK,CO.ISSHOWPRICE,
LO.INNERCODE1,LO.OUTLABELNO,
OS.OUTSTOCKTYPE,OS.COMPANYID,
CO.PRINTTYPE,
G.GOODSCORRCODE,
G.NAME, GB.NEEDCHE,OSID.Me_Extendno,
G.MODEL, G.FORM,G.GSPFORM,
G.FULLMARK,G.SUMTYPE,
LO.GOODSBATCH,
GB.EXPIREDATE,G.LICENSENUMBER,GB.LICENSENUMBER,GB.PRODUCTDATE,
LO.SENDDOCID,
V.FAKEFLAG,
D.TRANSFERTYPE,
D.ordertype,
OS.ISINVOICENEED,
G.STANDUNIT ,GB.BIGPERNUM, LO.GOODSID,OS.ISPAYNEED, GB.REPORTCOUNT,OS.OUTSTOCKINDICATIONID,
D.CONTACTOR,D.CUSTTELEPHONE,G.GOODSTOPCATEGORYID,CO.PRINTROW,C.ISOTHER,GB.NEEDREPORTLOCK,G.SUMTYPE,
LO.LOADBIGUNITNUM,LO.LOADSTANDUNITNUM,G.TEMPERATURE
,G.BUSINESSSCOPE,G.IMPORTFLG,G.BIOLOGYFLG,GB.REPORTCOUNT,G.CERTIFIED,P.NAME
ORDER BY OS.OUTSTOCKTYPE,OUTSTOCKINDICATIONID
文档更新时间: 2023-10-19 20:58 作者：周骏
