Going Live With Remittances By Email – Transferring Email Addresses

A few days ago I posted about testing email remittances and supplied a SQL script for use on the test system to ensure emails remained internal and didn’t get sent to creditors.

I’ve also written a script which will transfer the email address from the current Internet Information column (with the assumption that the email address is in INET1) across to the new EmailToAddress column and then blank out the INET1 column so data is not being held, and therefore maintained, in two fields.

An email address is also being set in a variable to be loaded into the EmailBccAddress column which I have advised clients to set to an internal mailbox so they have a copy of all email remittances sent to suppliers.

DECLARE @BccEmail VARCHAR(100)
SET @BccEmail = 'email@example.com'

UPDATE
   SY01200
SET
   INET1 = ''
   ,EmailToAddress = INET1
   ,EmailBccAddress = @BccEmail
WHERE
   Master_Type = 'VEN'

It requires some discipline for clients to remember to set the EmailBccAddress on each creditor when entering email address for new ones. An alternative is the below script which will update this column on all creditors which have an EmailToAddress, and which could be scheduled to run on a regular basis;

DECLARE @BccEmail VARCHAR(100)
SET @BccEmail = 'email@example.com'

UPDATE
   SY01200
SET
   EmailBccAddress = @BccEmail
WHERE
   Master_Type = 'VEN' AND EmailToAddress IS NOT NULL