SQL view for SOP email setup in GP 2010

I am starting to work with emailing SOP documents using Dynamics GP 2010 and new functionality that we are going to be releasing in an upcoming build of GP Reports Viewer. It’s pretty cool stuff, however, as I am working with customers to set this up, we’re finding that out-of-the box GP does not have any easy way to check all of the email setup.

To help with this, I have added the email tables to my Company/System Tables page and have created the view below that lists all customers, the email addresses for their default Bill To Address and what has been set up for emailing SOP documents for each of them.

create view view_Customer_SOP_Email_Setup
as

--**************************************************************
-- created 10/20/2011 by Victoria Yudin - Flexible Solutions Inc 
-- for updates please see https://victoriayudin.com/gp-reports/ 
-- only shows email addresses for the default bill to address
-- *************************************************************

select
CM.CUSTNMBR Customer_ID,
CM.CUSTNAME Customer_Name,
CM.PRBTADCD Default_Bill_To_Address_ID,
coalesce(II.EmailToAddress,'') Email_To,
coalesce(II.EmailCcAddress,'') Email_Cc,
coalesce(II.EmailBccAddress,'') Email_Bcc,
case E1.EmailDocumentEnabled
	when 1 then case E1.EmailDocumentFormat
		when 1 then 'DOCX'
		when 2 then 'HTML'
		when 3 then 'PDF'
		when 4 then 'XPS'
		else ''
		end
	else 'Not enabled'
	end Email_SOP_Quote,
case E2.EmailDocumentEnabled
	when 1 then case E2.EmailDocumentFormat
		when 1 then 'DOCX'
		when 2 then 'HTML'
		when 3 then 'PDF'
		when 4 then 'XPS'
		else ''
		end
	else 'Not enabled'
	end Email_SOP_Order,
case E3.EmailDocumentEnabled
	when 1 then case E3.EmailDocumentFormat
		when 1 then 'DOCX'
		when 2 then 'HTML'
		when 3 then 'PDF'
		when 4 then 'XPS'
		else ''
		end
	else 'Not enabled'
	end Email_SOP_Invoice,
case E4.EmailDocumentEnabled
	when 1 then case E4.EmailDocumentFormat
		when 1 then 'DOCX'
		when 2 then 'HTML'
		when 3 then 'PDF'
		when 4 then 'XPS'
		else ''
		end
	else 'Not enabled'
	end Email_SOP_Return,
case E6.EmailDocumentEnabled
	when 1 then case E6.EmailDocumentFormat
		when 1 then 'DOCX'
		when 2 then 'HTML'
		when 3 then 'PDF'
		when 4 then 'XPS'
		else ''
		end
	else 'Not enabled'
	end Email_SOP_Fulfillment_Order

FROM RM00101 CM  --customer master 
LEFT OUTER JOIN RM00102 CA --customer addresses
ON CM.CUSTNMBR = CA.CUSTNMBR and CM.PRBTADCD = CA.ADRSCODE

LEFT OUTER JOIN SY01200 II --internet information
ON CM.CUSTNMBR = II.Master_ID and II.Master_Type = 'CUS'
     and CM.PRBTADCD = II.ADRSCODE

LEFT OUTER JOIN SY04905 E1 --email setup for SOP Quote 
ON CM.CUSTNMBR = E1.EmailCardID AND E1.MODULE1 = 11
     AND E1.EmailDocumentID = 1

LEFT OUTER JOIN SY04905 E2 --email setup for SOP Order 
ON CM.CUSTNMBR = E2.EmailCardID AND E2.MODULE1 = 11
     AND E2.EmailDocumentID = 2

LEFT OUTER JOIN SY04905 E3 --email setup for SOP Invoice
ON CM.CUSTNMBR = E3.EmailCardID AND E3.MODULE1 = 11
     AND E3.EmailDocumentID = 3

LEFT OUTER JOIN SY04905 E4 --email setup for SOP Return
ON CM.CUSTNMBR = E4.EmailCardID AND E4.MODULE1 = 11
     AND E4.EmailDocumentID = 4

LEFT OUTER JOIN SY04905 E6 --email setup - SOP Fulfillment Order 
ON CM.CUSTNMBR = E6.EmailCardID AND E6.MODULE1 = 11
     AND E6.EmailDocumentID = 6

GO
GRANT SELECT ON view_Customer_SOP_Email_Setup 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.