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
