SELECT
'[' || Table5.OPTNO || ']' || Table5.OPTNAME Expr4,
Table1.PLUNO PLUNO,
Table1.PLUNAME PLUNAME,
NVL(A.QTY,0) AS QTY,
Table1.SPEC SPEC,
Table1.PKUNIT PKUNIT,
Table1.PKQTY PKQTY,
Table1.COMNO COMNO,
Table4.BCDBCD,
Case When NVL(Table13.RSLPRC,0)Table3.SLPRCAnd NVL(Table13.RSLPRC,0) >0 ThenTable13.RSLPRC
When NVL(Table13.MMSLPRC,0)Table3.SLPRC And NVL(Table13.MMSLPRC,0)>0 Then Table13.MMSLPRC
Else 0.00 End MRSLPRC,
Table3.SLPRC SLPRC,
Table3.WHSLPRC WHSLPRC,
Table3.ASLPRC ASLPRC,
TABLE1.DISRATE,
Table3.VLDPSN VLDPSN,
Table3.VLDDAT VLDDAT,
TABLE1.CONRAT CONRAT,
Table3.STRPRC STRPRC,
TABLE8.BLCNO BLCNO,
Table4.PROD PROD,
'['||Table6.STANO||']'||Table6.STANAME As STANO,
case whenTABLE3.OPNPRC=1 then 'Y' else 'N' endASOPNPRC ,
'[' || Table2.DPTNO || ']' || Table2.DPTNAME Expr1,
'[' || Table89.DPTNO || ']' || Table89.DPTNAME SXDPT,
Table1.FSHNO FSHNO,
Table3.VALID VALID,
Table1.PLUID PLUID,
Table1.PLUID PLUIDBAK,
Table1.PID PID,
trunc(case when table3.slprc = 0 then 0 else (table3.slprc-table14.csprc)*100 / table3.slprc end,2)||'%' as slntamt
FROMBASPLUPRC Table3, BASDEPT Table2,BSASTA Table6,
BASPLUMAIN Table1
Left Outer Join (select pluid,max(rslprc) as rslprc ,max(mmslprc) as mmslprc from vwmmxplu_SLPRC group by pluid) Table13
On(Table1.PLUID=Table13.PLUID)
left outer join(SELECT PLUID,SUM(QTY) AS QTY FROM FINSTOCKSHPQTY GROUP BY PLUID )AON TABLE1.PLUID = A.PLUID
left outer join BASCOMMAIN CC ON Table1.COMNO = CC.COMNO
left join vwdoc70plu table14 on table1.pluid=table14.pluid and table1.doc70no=table14.docno,
(select OPTID, OPTNO, OPTNAME from BASOPT where OPTID in (4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,
27,28,29,30,102,31,61,33,41,121,1 )) Table5,
(selectPLUID,MAX(BCD) AS BCD,MAX(PROD) AS PROD from BASPLUBCD where 1=1 Group By PLUID) Table4,
BASPLUEXT TABLE8 Left Outer Join VWSXDPTTable89 On Table8.MAXORDQTY=Table89.DPTID
WHERE(Table1.DPTID = Table2.DPTID AND Table3.PLUID = Table1.PLUID
AND Table1.PLUID = Table4.PLUID AND Table1.OPTID = Table5.OPTIDAND TABLE1.PLUID = TABLE8.PLUID
And Table1.STAID=Table6.Staid ) And( table1.staid != 5 And cc.COMID = 3341)
ORDER BY PLUNO
|
