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 adds new joins and fields for use in the PM document workflow approvals and notification emails.
/*
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 selected fields
INSERT INTO CO00121 --Query Designer Fields List (CO00121)
(FieldsListGuid,SEQNUMBR,DefaultField,TableDictID,TableSeries,TableTechnicalName,TablePhysicalName,TableDisplayName,FieldPhysicalName,FieldDisplayName,FieldSource,FieldDataType,DecimalDigits,RelativeDecimalPosition,ShowCurrencySymbol,ShowPercentSign,CRNCYSYM,DECPLCUR,Hidden)
VALUES
('C573A05B-4210-4332-81C1-91A4A8175C4F',31,1,0,1,'GL_Account_Index_MSTR','GL00105','Account Index Master','ACTNUMST','Account Number String',1,5,0,0,0,0,'',0,0)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',32,0,0,3,'PM_Distribution_WORK_OPEN','PM10100','PM Distribution WORK OPEN','DistRef','Distribution Reference',1,5,0,0,0,0,'',0,0)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',33,1,0,3,'PM_Transaction_WORK','PM10000','PM Transaction Work','PRCHAMNT','Purchase Amount',1,4,0,1,1,0,'',0,0)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',34,0,0,1,'GL_Account_MSTR','GL00100','Account Master','ACTDESCR','Account Description',1,5,0,0,0,0,'',0,0)
GO
-- insert table join from pm dist work open to account index master
INSERT INTO CO00122 --QueryDesigner_Relationships (CO00122)
(FieldsListGuid,Group_ID,SEQNUMBR,FromDictID,FromTable,FromTablePhysicalName,FromField,FromFieldPhysicalName,ToDictID,ToTable,ToTablePhysicalName,ToField,ToFieldPhysicalName,JoinType)
VALUES
('C573A05B-4210-4332-81C1-91A4A8175C4F',3,0,0,'PM Distribution WORK OPEN','PM10100','','',0,'Account Index Master','GL00105','','',3)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',3,1,0,'PM Distribution WORK OPEN','PM10100','Distribution Account Index','DSTINDX',0,'Account Index Master','GL00105','Account Index','ACTINDX',3)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',4,0,0,'Account Index Master','GL00105','','',0,'Account Master','GL00100','','',3)
,('C573A05B-4210-4332-81C1-91A4A8175C4F',4,1,0,'Account Index Master','GL00105','Account Index','ACTINDX',0,'Account Master','GL00105','Account Index','ACTINDX',3)
GO
-- insert payables trx approval workflow email fields for distributions
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
('Payables Transaction Approval',2,103,0,18,2,0,0,'GL00105','ACTNUMST','Account Number String',5,3,0)
,('Payables Transaction Approval',2,104,0,18,2,0,0,'PM10100','DEBITAMT','Debit Amount',5,3,0)
,('Payables Transaction Approval',2,105,0,18,2,0,0,'PM10100','CRDTAMNT','Credit Amount',5,3,0)
,('Payables Transaction Approval',2,106,0,18,2,0,0,'PM10100','DistRef','Distribution Reference',5,3,0)
,('Payables Transaction Approval',2,107,0,18,2,0,0,'GL00100','ACTDESCR','Account Description',5,3,0)
GO
Click to show/hide the SQL Scripts for Microsoft Dynamics GP Series Index
What should we write about next?
If there is a topic which fits the typical ones of this site, which you would like to see me write about, please use the form, below, to submit your idea.
Looking for support or consultancy with Microsoft Dynamics GP?
I no longer work with Microsoft Dynamics GP, but the last company I worked for was ISC Software in the UK; if you’re looking for support or consultancy services with Microsoft Dynamics GP you can contact them here.