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
