SQL Scripts for Microsoft Dynamics GP: Migrate Vendor Emails from Active Docs to Standard Email Fields

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will copy vendor emails from the Active Documents table to the standard Dynamics GP email fields where they weren’t already present. This script was written for a client who was replacing the sending of remittances using Active Docs to using the standard functionality.

The script returns mutiple emai address from the Active Docs tables as a semi-colon delimited ist to insert into one field in the standard tables.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
INSERT INTO SY01200 --Internet Addresses (SY01200)
	(

		Master_Type,Master_ID,ADRSCODE,INET1,INET2,INET3,INET4,INET5,INET6,INET7,INET8,Messenger_Address,INETINFO,EmailToAddress,EmailCcAddress,EmailBccAddress

	)
SELECT DISTINCT

	'VEN' as Master_Type

	,['Active Docs'].VENDORID AS Master_ID

	,['PM Creditor Master'].VADCDTRO AS ADRSCODE

	,'' AS INET1

	,'' AS INET2

	,'' AS INET3

	,'' AS INET4

	,'' AS INET5

	,'' AS INET6

	,'' AS INET7

	,'' AS INET8

	,'' AS Messenger_Address

	,'' AS INETINFO

	,ISNULL((STUFF((

        SELECT

			'; ' + RTRIM(EMail)

        FROM

			EMA00101 AS ['INNER']
        WHERE

			['INNER'].Email_Type = 1

		AND

			['INNER'].VENDORID = ['Active Docs'].VENDORID

        ORDER BY

			VENDORID

			,IntegerValue

        FOR XML PATH('')

        ), 1, 2, '')

    ),'') AS EmailToAddress

	,ISNULL((STUFF((

        SELECT

			'; ' + RTRIM(EMail)

        FROM

			EMA00101 AS ['INNER']
        WHERE

			['INNER'].Email_Type = 2

		AND

			['INNER'].VENDORID = ['Active Docs'].VENDORID

        ORDER BY

			VENDORID

			,IntegerValue

        FOR XML PATH('')

        ), 1, 2, '')

    ), '') AS EmailCcAddress

	,'' AS EmailBccAddress

FROM

	EMA00101 AS ['Active Docs']
INNER JOIN

	PM00200 AS ['PM Creditor Master'] --PM Vendor Master File (PM00200)
		ON

			['PM Creditor Master'].VENDORID = ['Active Docs'].VENDORID

WHERE

	(

		SELECT

			COUNT(*)

		FROM

			SY01200 AS ['Internet Addresses'] --Internet Addresses (SY01200)
		WHERE

			['Internet Addresses'].Master_Type = 'VEN'

		AND

			['Internet Addresses'].Master_ID = ['PM Creditor Master'].VENDORID

		AND

			['Internet Addresses'].ADRSCODE = ['PM Creditor Master'].VADCDTRO

	) = 0

Read original post SQL Scripts for Microsoft Dynamics GP: Migrate Vendor Emails from Active Docs to Standard Email Fields at azurecurve|Ramblings of an IT Professional