SQL Script To Insert Creditor Bank Details From CSV

Microsoft Dynamics GPIntegration Manager is a very good tool, but it doesn’t allow the import of all record types into Microsoft Dynamics GP. One of the main types of record I need to import when implementing a new client is the Creditor (Vendor to the American readers) EFT details.

While the EFT data is stored in one table and can therefore be imported using Table Import, I find that configuring Table Import for each client is a bit of a annoyance, so I developed a SQL Script a while ago which I figured I might as well post here for easy access.

As always when using SQL to update tables in Microsoft Dynamics GP, make sure you have a good backup of the database before you begin and check the imported data afterwards. Read on for the script…

Continue reading

Script To Copy Segments To All Companies

Microsoft Dynamics GPFollowing the last post I did, on copying segments to a new company, I did some thinking and realised that with a little more work on the script I could make it even more useful when setting up clients with multiple companies and the same chart of account structure.

I have added a cursor to the script which selects all company databases from the DYNAMICS System Database (change the highlighted DYNAMICS if you’re using a named system database) and then loops though them doing the insert from the SourceDatabase.

As before, the script checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'

DECLARE
	cursor_InterID Cursor 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	WHERE
		INTERID <> @SourceCompany
	
	Open cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@DestinationCompany
	While (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
				(SGMTNUMB
				,SGMNTID
				,DSCRIPTN
				,SEGCOUNT
				,NOTEINDX)
				
				(SELECT
					SGMTNUMB
					,SGMNTID
					,Left(DSCRIPTN, 30)
					,0
					,0
				FROM
					' + @SourceCompany + '..GL40200 AS SD
				WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
					WHERE GL.SGMTNUMB = SD.SGMTNUMB AND GL.SGMNTID = SD.SGMNTID) = 0)'

			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@DestinationCompany
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

Script To Copy Segments To A New Company

Microsoft Dynamics GPI have been doing some work with a client recently where we were creating a number of new companies which were to share the same chart of accounts (with only the first segment representing the company being different).

So we planned to use Integration Manager to to integrate a file containing the new chart of accounts. However, before we could load the accounts themselves we needed to get the Segments loaded.

Tis could be done by creating a SmartList in SmartList Designer to get the Segment Number, Segment ID and Segment Description and then use File Import to load the segments into the new company; or I could knock together a quick SQL script to do the job.

Obviously, as I am writing this post, I opted to go the route of writing an SQL Script. The highlighted pieces at the top are the Source and Destination Company databases; change these to the relevant databases.

The script also checks to make sure the Segments don’t already exist in the destination before doing the insert.

DECLARE @SourceCompany AS VARCHAR(5)
DECLARE @DestinationCompany AS VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

SET @SourceCompany = 'TWO'
SET @DestinationCompany = 'TWOA'

SET @SQLStatement = 'INSERT INTO ' + @DestinationCompany + '..GL40200
	(SGMTNUMB
	,SGMNTID
	,DSCRIPTN
	,SEGCOUNT
	,NOTEINDX)
	
	(SELECT
		SGMTNUMB
		,SGMNTID
		,Left(DSCRIPTN, 30)
		,0
		,0
	FROM
		' + @SourceCompany + '..GL40200 AS SM
	WHERE (SELECT COUNT(GL.SGMNTID) FROM ' + @DestinationCompany + '..GL40200 GL
		WHERE GL.SGMTNUMB = SM.SGMTNUMB AND GL.SGMNTID = SM.SGMNTID) = 0)'

EXEC (@SQLStatement)
GO

If you run this script, please be careful and ensure you have a good backup before running the script (as I don’t supply a warranty with any script I post here; that said I am happy to talk to people if they have questions or would like the script extending).

SQL Query To Determine Quantity To Order

I recently had cause to knock together a fairly simple SQL script to determine the quantity to order based on Sales transactions in the system taking into account the Order Point Quantity and Quantity To Order for the Item/Site combination in the Inventory module:

SELECT
   SOP10200.LOCNCODE AS 'Site'
   ,SOP10200.ITEMNMBR AS 'Item Number'
   ,SUM(SOP10200.QUANTITY) AS 'Quantity Required'
   ,IV00102.ORDRUPTOLVL - SUM(SOP10200.QTYTORDR) AS 'Quantity To Order'
FROM
   SOP10200 (NOLOCK)
LEFT OUTER JOIN
   IV00102 (NOLOCK)
      ON IV00102.ITEMNMBR = SOP10200.ITEMNMBR AND IV00102.LOCNCODE = SOP10200.LOCNCODE
WHERE
   SOP10200.QTYTORDR <= ORDRPNTQTY AND SOP10200.QTYTORDR <> 0
GROUP BY
   SOP10200.ITEMNMBR, SOP10200.LOCNCODE, IV00102.ORDRUPTOLVL

Extract ABR Transactions For Import As Statement

this is a simple script, but I’ve written it about four times now so I figured I’d post it so I can find it easily next time I lose my local copy.

Perfect Image are resellers of the Advanced Bank Reconciliation module from Nolan Business Solutions (along with the other add-ons they’ve written for Dynamics GP) and I often need to demo this replacement for the standard Bank Rec module.

One item I typically show is the auto-Propose function which matches transactions against statement lines in the Reconcile Bank Transactions window (Transactions >> Financial >> Advanced Bank Reconciliation >> Reconcile Bank Transactions).

To do this I need to be able to import statement lines which match the transactions in Dynamics GP; the easiest way of doing this is to extract the transactions.

This can be done with a very simple SQL script:

SELECT
   ORPSTDDT
   ,ORDOCNUM
   ,TRXAMNT
   ,SOURCDOC

FROM
   NCABR012

Once the data has been extracted it can be imported during the demo using the standard ABR Import Statement routine.

SQL Script To Locate Columns With Different Collation

A while ago I posted about a problem with a collation conflict on a couple of columns in the Tax table. It seems I posted about how to fix the problem, but it seems I didn’t post how I found the problem columns.

I did this with a fairly simple SQL script:

DECLARE @Collation SYSNAME SET @Collation = 'SQL_Latin1_General_CP1_CI_AS'

SELECT
   TABLE_NAME AS 'Table'
   ,COLUMN_NAME AS 'Column'
   ,DATA_TYPE AS 'Data Type'
   ,COLLATION_NAME AS 'Collation Name'
FROM
   INFORMATION_SCHEMA.COLUMNS
WHERE
   DATA_TYPE IN ('varchar','char','nvarchar','nchar','text','ntext')
AND
   COLLATION_NAME <> @Collation

Deactivate All Reports (Except PM EFT Payment Register) In All Companies

Microsoft Dynamics GP includes a large number of reports which will automatically print off when a transaction or batch is posted; and some postings produce many different reports.

When I implement a new client I typically leave them on during training with an instruction to identify which ones they want to use after go-live as I will switch off any I’m not told to leave on.

I used to do it the other way and tell people to let me know which ones they wanted switched off; however, this lead to all reports being left on and then it being mentioned a year or so down the line that they get all these reports printing that they don’t want.

So now, all get switched off unless I am specifically told to leave them on. As I’ve dealt with clients with many companies (I think the largest is 180 companies) this is not something I want to do manually.

Fortunately, the settings for whether the reports should print is stored in SQL table which means a SQL script can be written to switch them off in bulk.

Continue reading

Management Reporter Incorrect Figures Caused By Lower Case

A client reported a problem with Management Reporter returning incorrect figures when a link to the Financial Dimensions on the Row Definition had been entered in lower case.

The reports in question had been created in FRx which was rather more forgiving that Management Reporter is for these things. The client had quite a large number of reports which may, or may not, have had segments entered in lower case so the client didn;t want to have to check and update manually.

I again delved into the Management Reporter SQL database and updated the ControlRowCriteria table to capitalise the Low and High values of the link to financial dimensions using the script below:

UPDATE
   ControlRowCriteria
SET
   Low = UPPER(Low)
WHERE
   LEN(Low) > 0
GO

UPDATE
   ControlRowCriteria
SET
   High = UPPER(High)
WHERE
   LEN(High) > 0
GO

Once the update had been run, the client checked their reports and confirmed that they were now returning the correct figures.

Management Reporter Link To Financial Dimension Range Problem

Management Reporter again, folks, after a short delay; I finally completed on a house purchase, in rural Northumberland, two years after I started work at Perfect Image.

I received another call that data on a report was not coming out correctly a short time ago (I’ve had the screenshots stored for a while until I had time to write this post); the numbers didn’t match those in Microsoft Dynamics GP or those on the old FRx report (which did match the numbers in GP.

I started by checking the Row Definition and the Link to Financial Dimension column to see what information was being brought through. The setup with ranges for the excludes struck me as a little odd with the ranges of 1:1, 10:10 and 100:100:

Dimensions

Continue reading

Area Page Error – Value Cannot be Null

A client who upgraded to Microsoft Dynamics GP 2013 a few weeks ago began encountering a problem when switching between modules. In particular when they loaded the Purchasing area page they received the following message;

Value cannot be null. Parameter  name: contentValueValue cannot be null. Parameter name: contentValue

Fortunately, there are two solutions to this problem outlined in the Microsoft Support KB Article 2843273. The first is to upgrade to Microsoft Dynamics GP 2013 which at very short notice isn’t really an option, or to use the script in the KB article to both remove the corrupt entry in SY07140 and add a trigger to the table to prevent another corrupt entry being created.

As we needed the client up and running without the error quickly, we opted for the second approach and will look to schedule in the upgrade from 2013 RTM to SP1.