Here is a view to show all Sales Order Processing line items with the SOP-POP link in Dynamics GP. You can use this with either SmartList Builder Crystal Reports, as I have made sure not to put any spaces in the column names. I maintain a list of all the SQL code I’ve published on my GP Reports page so if you’re looking for other scripts, take a look there.
CREATE VIEW view_SOP_POP_Link AS /** view_SOP_POP_Link Created Feb. 4 2009 by Victoria Yudin - Flexible Solutions, Inc. For updates see https://victoriayudin.com/gp-reports/ Shows all SOP line items with SOP - POP link present **/ SELECT SH.CUSTNMBR CustomerID, SH.CUSTNAME CustomerName, S.SOPNUMBE OrderNumber, S.ITEMNMBR Item, S.ITEMDESC ItemDescription, L.PONUMBER PONumber, PH.VENDORID VendorID, PH.VENDNAME VendorName FROM (SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC FROM SOP10200 UNION ALL SELECT SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC FROM SOP30300) S RIGHT OUTER JOIN SOP60100 L ON S.SOPTYPE = L.SOPTYPE AND S.SOPNUMBE = L.SOPNUMBE AND S.LNITMSEQ = L.LNITMSEQ LEFT OUTER JOIN (SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME FROM SOP10100 UNION ALL SELECT SOPTYPE, SOPNUMBE, DOCDATE, CUSTNMBR, CUSTNAME FROM SOP30200) SH ON SH.SOPTYPE = S.SOPTYPE AND SH.SOPNUMBE = S.SOPNUMBE LEFT OUTER JOIN (SELECT PONUMBER, VENDORID, VENDNAME FROM POP10100 UNION ALL SELECT PONUMBER, VENDORID, VENDNAME FROM POP30100) PH ON PH.PONUMBER = L.PONUMBER /** the following will grant permissions to this view to DYNGRP, leave this section off if you do not want to grant permissions **/ GO GRANT SELECT ON view_SOP_POP_Link TO DYNGRP
Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.