Change Web Services for Microsoft Dynamics GP Web Server Location In All Companies

Microsoft Dynamics GPIn the last post, I posted a script to find which Microsoft Dynamics GP company databases had web services enabled. Todays script is a related one; it can be used to change the web services server location in all companies.

This script was created so once a live system was copied to a standalone test system, the web services server can easily be updated to the test server.

The highlighted section is the new server location which should be set:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DECLARE @SQL NVARCHAR(MAX) SET @SQL = STUFF(( SELECT CHAR(13) + 'UPDATE WF SET Web_Service_Server = ''test.example.co.uk'' FROM ' + INTERID + '.dbo.WF00100 AS WF' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') EXEC sys.sp_executesql @SQL

Find Microsoft Dynamics GP Databases With Workflow Email Actions Enabled

Microsoft Dynamics GPI was doing an upgrade for a client the other day and ran some scripts to remove the web services SQL objects as part of the upgrade (I always find that an upgrade of the Web Services for Microsoft Dynamics GP doesn’t work very well, so it’s a quicker and better option to remove and reinstall.

As part of the reinstall I needed to deploy them to the companies to which they had previously been deployed; I wasn’t sure which companies this was, but I did know that the client had web services for workflow actions enabled in all of them. The script below was my way of finding which databases had the email actions enabled.

The script will is run against the system database and will select from all company databases returning the INTERID and web services server field.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DECLARE @SQL NVARCHAR(MAX) SET @SQL = STUFF(( SELECT CHAR(13) + 'SELECT ''' + INTERID + ''' AS INTERID, Web_Service_Server FROM ' + INTERID + '.dbo.WF00100 AS WF WHERE EnableWFNotifService = 1' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') EXEC sys.sp_executesql @SQL

Update Microsoft Dynamics GP Account Categories From Text File

Microsoft Dynamics GPI’ve recently been working with a client to implement Jet Reports as a replacement for Management Reporter. As part of this implementation, it was necessary to change the account categories in Microsoft Dynamics GP.

This particular client has over 1 million account strings in their chart of account which meant any update could not be done manually. While it could technically be done through Integration Manager this would have meant integrating a 1 million plus line file, which again wasn’t really feasible.

Instead what we did was have the client compile a list of the natural segment (the third segment) along with the new category and I created an update script to use this file to update Account Category Master (GL00102).

The below script creates a temporary table, imports the text file and then updates all the account category on all accounts in Breakdown Account Master (GL00100) based on the third segment.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
CREATE TABLE #IMPORT ( ACTNUMBR_3 VARCHAR(4) ,ACCATDSC VARCHAR(50) ) GO BULK INSERT #IMPORT FROM 'C:\Temp\Categories.txt' WITH ( FIELDTERMINATOR = '\t' ,ROWTERMINATOR = '\n' ,FIRSTROW = 1 ) GO [sqlpinkUPDATE[/sqlpink] ['Account Index Master'] SET ACCATNUM = ['Account Category Master'].ACCATNUM FROM GL00100 AS ['Account Index Master'] INNER JOIN #IMPORT ON #IMPORT.ACTNUMBR_3 = ['Account Index Master'].ACTNUMBR_3 INNER JOIN GL00102 AS ['Account Category Master'] ON ['Account Category Master'].ACCATDSC = #IMPORT.ACCATDSC GO DROP TABLE #IMPORT GO

As always when running a script which does updates, make sure you have a good backup, test the script in a test company and verify the update before repeating on live.

SQL Snippets: Create and Use Database Role to Restrict Access

Microsoft SQL ServerThis post is part of the series on SQL Snippets.

I create a lot of SQL objects such as views, tables, stored procedures and so on. If they will be used from within Microsoft Dynamics then they can simply be assigned to the DYNSA role, but if they are for use in custom reporting or integrations then separate security will be required.

Usually for reporting, and especially for integrations, security will be as locked down as possible to minimise any possible attack vector. This can be done in SQL through a security role with only limited security granted to the necessary users.

The first step is to create the role itself; I always do this using a role with a name prefixed with urpt_ so that it can easily be identified as a custom user role:

-- creatr security role on database
CREATE ROLE [urpt_Role] AUTHORIZATION [dbo]
GO

Then we grant the relevant permissions to the new role. For a report this would mean only granting select permissions on the relevant views and tables; for an integration there may be inserts and updates granted.

-- grant permission to role on object
GRANT SELECT ON [dbo].[uv_SQLView] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL20000] TO [urpt_Role]
GRANT SELECT ON [dbo].[GL30000] TO [urpt_Role]
GO

For a report, you may need to create a database user for the user or AD group:

-- create user on database
CREATE USER [DOMAIN\user]
GO

The final step is to assign the role to the relevant users or AD groups:

-- assign role to user on database
ALTER ROLE [urpt_Role] ADD MEMBER [DOMAIN\user]
GO

SQL Script to Assign All Bins to All Items in Microsoft Dynamics GP

Microsoft Dynamics GPI try to do as much through the front-end of Microsoft Dynamics GP as possible in order that the correct business logic be applied to the changes as possible. However, sometimes doing it that way takes far too much time. Recently when working with a particular client, we needed to assign all bins to all items.

Bins were being being introduced so there were no bins currently assigned, which meant a simple SQL script could be created using CROSS JOIN which produces a resultset where each item umber in the Item Master (IV00101) table was paired with the location code/bin number combination in the Site Bin Master (IV40701) table and inserted into Item Site Bin Priorities (IV00117).

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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 IV00117 --Item Site Bin Priorities (IV00117) ( ITEMNMBR ,LOCNCODE ,Priority ,BIN ,MINSTOCKQTY ,MAXSTOCKQTY ) --VALUES ( SELECT ['Item Number'].ITEMNMBR ,['Site Bin Master'].LOCNCODE ,ROW_NUMBER() OVER(PARTITION BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE ORDER BY ['Item Number'].ITEMNMBR, ['Site Bin Master'].LOCNCODE, ['Site Bin Master'].BIN) AS Priority ,['Site Bin Master'].BIN ,0 AS MINSTOCKQTY ,0 AS MAXSTOCKQTY FROM IV00101 AS ['Item Number'] --Item Master (IV00101) CROSS JOIN IV40701 AS ['Site Bin Master']--Site Bin Master (IV40701) ) GO

If any assignments exist in the IV00117 table the script will fail with a duplicate key error; the script also assigns priority based on the alphabetical order of the site/bin number combinations which may not be suitable in all contexts.

SQL Query to get First Level Items from Microsoft Dynamics GP Manufacturing BOM

Microsoft Dynamics GPWe’re currently doing some work for a client using the Manufacturing module of Microsoft Dynamics GP and I’ve been involved in the periphery of the manufacturing element while focusing on the financial and distribution parts, but have been assisting with some reporting items. One of them was to help create a report on the Mfg BOM showing only the first level items.

The below script, against the client data, gave the result which was required:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
SELECT ['Mfg Order Master'].MANUFACTUREORDER_I ,['Mfg Order Master'].ITEMNMBR ,['Item Master'].ITEMDESC ,['Mfg Order Master'].ENDQTY_I ,['Inventory U of M Schedule Setup'].BASEUOFM ,['Bill Of Material Line File'].CPN_I FROM WO010032 AS ['Mfg Order Master'] --WO010032 INNER JOIN IV00101 AS ['Item Master'] --Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Mfg Order Master'].ITEMNMBR INNER JOIN IV40201 AS ['Inventory U of M Schedule Setup'] --Inventory U of M Schedule Setup (IV40201) ON ['Inventory U of M Schedule Setup'].UOMSCHDL = ['Item Master'].UOMSCHDL INNER JOIN BM010115 AS ['Bill Of Material Line File'] --BM010115 ON ['Bill Of Material Line File'].PPN_I = ['Mfg Order Master'].ITEMNMBR

Find All Microsoft Dynamics GP Companies With Web Services Enabled

Microsoft Dynamics GPI’ve recently been doing some work with a client which necessitated the backup of all databases using the Web Services for Microsoft Dynamics GP. The easiest way to determine which databases had the web services enabled, was to run a script checking the Workflow Setup (WF00100) table.

I took a copy of my return functional currency for all companies script and amended it to look at the web services.

If the web services has never been enabled, a company won’t be returned at all other wise a 1 for active or 0 for inactive will be returned.

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
CREATE TABLE #Workflow( INTERID VARCHAR(5) ,CMPNYNAM VARCHAR(200) ,WEBSERVICESACTIVE VARCHAR(20) ) GO DECLARE @SQL NVARCHAR(MAX) SELECT @SQL = STUFF(( SELECT CHAR(13) + 'SELECT ''' + INTERID + ''' ,''' + CMPNYNAM + ''' ,EnableWFNotifService FROM ' + INTERID + '.dbo.WF00100' FROM DYNAMICS.dbo.SY01500 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') INSERT INTO #Workflow EXEC sys.sp_executesql @SQL GO SELECT * FROM #Workflow ORDER BY INTERID GO DROP TABLE #Workflow GO

Update Microsoft Dynamics GP Customer Emails on Test Including Sent Emails

Microsoft Dynamics GPAfter creating a new script for updating vendor emails on test which includes the emails of sent items, I figured that a similar script will be needed to update the email address on sent emails in the Sales Series.

The script below, includes the tables holding sent email information for the Sales Series emails (both Receivables Management and Sales Order Processing) as well as the usual Internet Addresses table; the highlighted email address can be changed to whatever email address you’re using for testing your Sales emails:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DECLARE @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' -- UPDATE existing email addresses UPDATE SY01200 --Internet Addresses (SY01200) SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'CUS' -- UPDATE Email Details of previously sent emails UPDATE SY04910 --Email Details (SY04910) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing -- UPDATE Email Details of previously sent emails UPDATE SY04915 --Email History (SY04915) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing GO

Update Microsoft Dynamics GP Vendor Emails on Test Including Sent Emails

Microsoft Dynamics GPAbout 10 years ago I did a post on changing emails on the test system so they didn’t go outside the organisation. This has worked well for a long time and no-one has mentioned any issues, until this week when I was working with a client and we made some changes to a remittance format and tested by emailing.

The remittance itself emailed to the test address fine, but the reprint remittance went to the original email address of the vendor used on the live system.

After doing a little exploring I found there were two additional tables which held this data for sent emails and which were used by the reprint remittance function.

The script below, includes the Internet Addresses table as well as these two tables for the Purchasing Series emails (both Purchase Order Processing and Payables Management); the highlighted email address can be changed to whatever email address you’re using for testing your Purchasing emails:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
DECLARE @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' -- UPDATE existing email addresses UPDATE SY01200 --Internet Addresses (SY01200) SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'VEN' -- UPDATE Email Details of previously sent emails UPDATE SY04910 --Email Details (SY04910) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management -- UPDATE Email Details of previously sent emails UPDATE SY04915 --Email History (SY04915) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management GO

Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items

Microsoft Dynamics GPThis post as been added as part of the series on creating a simple audit for Microsoft Dynamics GP, but wsn;t part of the original series.

I recently used the simple audit to add an audit to the Sales Customer Item Cross Reference (SOP60300) table to allow a client to keep an audit of changes to customer items. They wanted to keep track of all changes so this means three triggers are required on:

  1. INSERT
  2. UPDATE
  3. DELETE

These triggers will record all customer items which are added, amended or removed. The Customer Items window contains a few fields, but the only ones with sensitive dta which needs to be audited are:

  1. Customer Item Number
  2. Customer Item Description

The first trigger creates the trigger which runs when data is inserted:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (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). */
CREATE TRIGGER utr_AZRCRV_SOP60300_AuditInsert ON SOP60300 AFTER INSERT AS INSERT INTO ut_AZRCRV_Audit SELECT 'Sales Customer Item Cross Reference' ,CAST(RTRIM(I.ITEMNMBR) AS VARCHAR(30)) + '|' + CAST(RTRIM(I.CUSTNMBR) AS VARCHAR(15)) ,'Insert' ,SYSTEM_USER ,GETDATE() ,'' ,'Customer Item Number = ' + CAST(RTRIM(i.CUSTITEMNMBR) AS VARCHAR(30)) + ' | ' + 'Customer Item Description = ' + CAST(RTRIM(i.CUSTITEMDESC) AS VARCHAR(30)) FROM inserted AS i GO

Continue reading “Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items”