SQL view to show items with SOP POP link

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 http://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.

Posted in Crystal Reports, Dynamics GP, GP Reports, SmartList Builder, SQL Server   Tagged: Crystal Reports, Dynamics GP, GP Reports, Purchase Order Processing, Sales Order Processing, SmartList Builder, SQL code