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.

9 Responses to “SQL view for SOP email setup in GP 2010”

  1. hi Victoria,

    thanks for the information. We are trying to track when RM transactions are unapplied. I can not find any history in GP for this. Is this correct or can you please assist?

    Michelle

    Like

Trackbacks/Pingbacks

  1. Interesting Findings & Knowledge Sharing » Emailing SOP invoices in GP 2010 made easy with GP Reports Viewer - February 29, 2012

    […] individually. There are typically not too many of these and you could prevent this by using my SOP email setup view ahead of time to check for valid email […]

    Like

  2. Emailing SOP invoices in GP 2010 made easy with GP Reports Viewer - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - February 29, 2012

    […] individually. There are typically not too many of these and you could prevent this by using my SOP email setup view ahead of time to check for valid email […]

    Like

  3. Emailing SOP invoices in GP 2010 made easy with GP Reports Viewer | Victoria Yudin - February 29, 2012

    […] individually. There are typically not too many of these and you could prevent this by using my SOP email setup view ahead of time to check for valid email […]

    Like

  4. Everything Dynamics GP #42 | Interesting Findings & Knowledge Sharing - October 27, 2011

    […] SQL view for SOP email setup in GP 2010 (Victoria Yudin) […]

    Like

  5. Everything Dynamics GP #42 - About Dynamics, Development and Life - GP Technical Blogs - Microsoft Dynamics Community - October 27, 2011

    […] SQL view for SOP email setup in GP 2010 (Victoria Yudin) […]

    Like

  6. SQL view for SOP email setup in GP 2010 | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - October 25, 2011

    […] Comments 0 Victoria Yudin delivers a SQL view for SOP email setup in GP 2010 […]

    Like

  7. SQL view for SOP email setup in GP 2010 | Interesting Findings & Knowledge Sharing - October 24, 2011

    […] article: SQL view for SOP email setup in GP 2010 VN:F [1.9.11_1134]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: