Hi:
Is there a document outlining the SQL queries that make up SmartLists?
I found a perfect SmartList report that shows only open purchase orders.
I can almost replicate this in a SQL query, except for one thing. I can't get the Quantity Shipped field from POP10500, for me to compare with the Quantity Ordered field from POP10110.
There's a SmartList that has both of these columns combined into one report. That's why I need its SQL query.
Below is my query. If nothing else, can someone please tell me how to modify this query to capture the Quantity Shipped field for open purchase orders in POP10500?
Thanks!
John
select POP10110.PONUMBER, POP10110.VENDORID, POP10100.VENDNAME,
POP10100.CONTACT, POP10100.ADDRESS1, POP10100.ADDRESS2, POP10100.CITY, POP10100.STATE, POP10100.ZIPCODE, POP10100.PHONE1,
POP10100.PYMTRMID,
POP10110.ITEMNMBR, POP10110.ITEMDESC, POP10110.UOFM, POP10110.QTYORDER,
POP10110.UNITCOST, POP10110.EXTDCOST,
POP10110.REQDATE, POP10110.PRMDATE, POP10110.PRMSHPDTE, POP10110.SHIPMTHD--, POP10500.QTYSHPPD
from POP10110
INNER JOIN POP10100 ON
POP10110.PONUMBER = POP10100.PONUMBER AND POP10110.VENDORID = POP10100.VENDORID
--LEFT OUTER JOIN POP10500 ON
--POP10110.PONUMBER = POP10500.PONUMBER
where POP10100.POTYPE = '1' AND
POP10110.POLNESTA = '2' and POP10110.LOCNCODE = 'CVWH' and POP10110.VENDORID <> 'SHURPAC' --AND POP10500.QTYSHPPD <> 0
ORDER BY POP10100.PONUMBER
3,201
Share
Report
All responses (
Answers (
