SELECT POP10100.DOCDATE, POP10100.PONUMBER, POP10150.CMMTTEXT AS POComment,
CASE POLNESTA WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END AS POLineStatus,
CASE POP10100.POTYPE WHEN 1 THEN 'Standard' WHEN 2 THEN 'Drop Ship' WHEN 3 THEN 'Blanket' WHEN 4 THEN 'Blanket Drop Ship' END AS POType, POP10110.ITEMNMBR, POP10110.ITEMDESC,
POP10550.CMMTTEXT AS LineComment, POP10100.VENDORID, POP10110.LOCNCODE, POP10110.QTYORDER, derivedtbl_1.QTYINVCD, POP10110.UNITCOST,
CASE POstatus WHEN 1 THEN 'New' WHEN 2 THEN 'Released' WHEN 3 THEN 'Change Order' WHEN 4 THEN 'Received' WHEN 5 THEN 'Closed' WHEN 6 THEN 'Cancelled' END AS DocStatus,
GL00105.ACTNUMST, POP10100.BUYERID, PM00200.VNDCHKNM, POP10100.TAXAMNT, (POP10110.QTYORDER - derivedtbl_1.QTYINVCD) * POP10110.UNITCOST AS RemainingLinetotal,
POP10100.REMSUBTO
FROM POP10100 INNER JOIN
POP10110 ON POP10100.PONUMBER = POP10110.PONUMBER INNER JOIN
PM00200 ON POP10100.VENDORID = PM00200.VENDORID LEFT OUTER JOIN
(SELECT PONUMBER, POLNENUM, POPRCTNM, RCPTLNNM, QTYSHPPD, QTYINVCD, QTYREJ, QTYMATCH, QTYRESERVED, QTYINVRESERVE, Status, UMQTYINB, OLDCUCST, JOBNUMBR, COSTCODE,
COSTTYPE, ORCPTCOST, OSTDCOST, APPYTYPE, POPTYPE, VENDORID, ITEMNMBR, UOFM, TRXLOCTN, DATERECD, RCTSEQNM, SPRCTSEQ, PCHRPTCT, SPRCPTCT, OREXTCST, RUPPVAMT,
ACPURIDX, INVINDX, UPPVIDX, NOTEINDX, CURNCYID, CURRNIDX, XCHGRATE, RATECALC, DENXRATE, RATETPID, EXGTBLID, Capital_Item, Product_Indicator, Total_Landed_Cost_Amount,
QTYTYPE, Posted_LC_PPV_Amount, QTYREPLACED, QTYINVADJ, DEX_ROW_ID
FROM POP10500
WHERE (POPTYPE IN (1, 3))) AS derivedtbl_1 ON POP10110.PONUMBER = derivedtbl_1.PONUMBER AND POP10110.ORD = derivedtbl_1.POLNENUM LEFT OUTER JOIN
GL00105 ON POP10110.INVINDX = GL00105.ACTINDX LEFT OUTER JOIN
POP10550 ON POP10110.DOCTYPE = POP10550.DOCTYPE AND POP10110.PONUMBER = POP10550.POPNUMBE AND POP10110.ORD = POP10550.ORD LEFT OUTER JOIN
POP10150 ON POP10100.PONUMBER = POP10150.POPNUMBE
No comments:
Post a Comment