SQL Scripts for Microsoft Dynamics GP: Change Vendor Change Approvals Joins and 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 changes the joins for the Vendor Change Approval workflow notification emails and adds some extra bank fields.

/*
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).
*/
-- change existing join from vendor to bank to vendor address to bank
UPDATE
	CO00122 --QueryDesigner_Relationships (CO00122)
SET
	FromTablePhysicalName = 'PM00300'
	,FromTable = 'PM Address MSTR'
WHERE
	FieldsListGuid = '6097E956-33CC-48A1-9B80-5EE50B4DC9B8'
AND
	Group_ID = 6
AND
	FromTablePhysicalName = 'PM00200'
GO

-- insert second join for vendor address to bank 
INSERT INTO CO00122 Query Designer Fields List (CO00121)
	(FieldsListGuid,Group_ID,SEQNUMBR,FromDictID,FromTable,FromTablePhysicalName,FromField,FromFieldPhysicalName,ToDictID,ToTable,ToTablePhysicalName,ToField,ToFieldPhysicalName,JoinType)
VALUES
	('6097E956-33CC-48A1-9B80-5EE50B4DC9B8',6,2,0,'PM Address MSTR','PM00300','Address Code','ADRSCODE',0,'Address Electronic Funds Transfer Master','SY06000','Address Code','ADRSCODE',3)
GO

-- remove existing vendor address bank fields
UPDATE
	WF40202 --Workflow Template Fields (WF40202)
SET
	Workflow_Type_Name = 'xVendor Approval'
WHERE
	Workflow_Type_Name = 'Vendor Approval'
AND
	Email_Message_Type = 2
AND
	TablePhysicalName = 'SY06000'
AND
	TableRelationship = 1
GO

-- insert new vendor address bank fields to document lines
INSERT INTO dbo.WF40202 --Workflow Template Fields (WF40202)
	(Workflow_Type_Name,Email_Message_Type,SEQNUMBR,Ord_Line,WF_Template_Field_Type,TableSeries,TableDictID,RSRCID,TablePhysicalName,FieldPhysicalName,FieldName,FieldDataType,TableRelationship,AdditionalFields)
VALUES
	('Vendor Approval',2,85,0,18,8,0,0,'SY06000','BANKNAME','EFT Bank Name',5,3,0)
	,('Vendor Approval',2,86,0,18,8,0,0,'SY06000','EFTBankCode','EFT Sort Code',5,3,0)
	,('Vendor Approval',2,87,0,18,8,0,0,'SY06000','EFTBankAcct','EFT Account Number',5,3,0)
GO