Month: January 2021

I recently did a webinar for my employer, ISC Software, on Prepayments and Accruals and as part of the accruals section used a SQL View to extract the accruals to be created; as mentioned in thw webinar, the extract cna be done either using a SmartList or a direct query in SmartConnect.

The SQL view has been created using the EOMONTH function which si available only in later versions of SQL Server; you may need to tweak the script a little to handle getting dates in different ways, if you are running an older version of SQL Server.

This script was written with a specific client in mind for use on a few companies so it has been written to look for a specific account description; you will need to either change the description or join in another way to get the credit line to output unless your accrual account has the exact description of Accrued Purchases. The section which will need changing is highlighted.

IF OBJECT_ID (N'uv_ISC_PayablesAccruals', N'V') IS NOT NULL
    DROP VIEW uv_ISC_PayablesAccruals
GO

CREATE VIEW uv_ISC_PayablesAccruals AS
/*
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).
*/
SELECT
	*
FROM
	(SELECT
		'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
		,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
		,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
		,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()), 'MM/yyyy') AS 'Reference'
		,ISNULL(GL00105_DAIM.ACTNUMST,GL00105.ACTNUMST) AS 'Account'
		,CASE WHEN DOCTYPE = 1 THEN
			CASE WHEN PP000100.PP_Module IS NOT NULL THEN
				SUM(PP000101.TRXAMNT)
			ELSE
				SUM(PM10100.DEBITAMT)
			END
		ELSE
			0
		END AS 'Debit'
		,CASE WHEN DOCTYPE = 1 THEN
			0
		ELSE
			CASE WHEN PP000100.PP_Module IS NOT NULL THEN
				SUM(PP000101.TRXAMNT)
			ELSE
				SUM(PM10100.CRDTAMNT)
			END
		END AS 'Credit'
		,LEFT(RTRIM(CAST(PM10100.VCHRNMBR AS VARCHAR(15))) + ' ' + PM10100.DistRef, 30) AS 'Description'
		,PM10100.VCHRNMBR AS 'Voucher Number'
		,PM10000.BACHNUMB AS 'Batch Number'
		,PM10000.TRXDSCRN AS 'Document Description'
		,PM10000.DOCNUMBR AS 'Document Number'
		,PM10000.PORDNMBR AS 'PO Number'
		,PM10100.DistRef AS 'Distribution Reference'
	FROM
		PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
	INNER JOIN
		PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
			on
				PM10100.CNTRLTYP = PM10000.CNTRLTYP
			AND
				PM10100.VCHRNMBR = PM10000.VCHRNMBR
	INNER JOIN
		GL00105 AS GL00105 --Account Index Master (GL00105)
			on
				GL00105.ACTINDX = PM10100.DSTINDX
	LEFT JOIN -- Join to RED
		PP000100 AS PP000100 --Deferral Header Work (PP000100)
			on
				PP000100.CNTRLTYP = PM10100.CNTRLTYP
			AND
				PP000100.PP_Document_Number = PM10100.VCHRNMBR
			AND
				PP000100.PP_Sequencer = PM10100.DSTSQNUM
	LEFT JOIN
		GL00105 AS GL00105_DAIM --Account Index Master (GL00105)
			on
				GL00105_DAIM.ACTINDX = PP000100.ACTINDX
	LEFT JOIN
		PP000101 AS PP000101 --Deferral Line Work (PP000101)
			on
				PP000101.PP_Module = PP000100.PP_Module
			AND
				PP000101.PP_Record_Type = PP000100.PP_Record_Type
			AND
				PP000101.PP_Document_Number = PP000100.PP_Document_Number
			AND
				PP000101.PP_Sequencer = PP000100.PP_Sequencer
			AND
				PP000101.PPOFFSEQ = PP000100.PPOFFSEQ
			AND
				PP000101.CNTRLTYP = PP000100.CNTRLTYP
			AND
				PP000101.VCHRNMBR = PP000100.VCHRNMBR
			AND
				PP000101.DSTSQNUM = PP000100.DSTSQNUM
			AND
				PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE()))
	WHERE
		PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
	AND
		PM10000.DOCTYPE = 1 --Include only Invoices
	AND
		PM10100.DISTTYPE = 6 --Include only Purchases Distribution
	GROUP BY
		GL00105.ACTNUMST
		,GL00105_DAIM.ACTNUMST
		,PM10000.DOCTYPE
		,PP000100.PP_Module
		,PM10100.DistRef
		,PM10100.VCHRNMBR
		,PM10000.BACHNUMB
		,PM10000.TRXDSCRN
		,PM10000.DOCNUMBR
		,PM10000.PORDNMBR
	HAVING
		SUM(PP000101.TRXAMNT) > 0 --partial deferral
	OR
		PP000100.PP_Module IS NULL --no deferral
	UNION ALL
		SELECT
			'PAC ' + FORMAT(DATEADD(month,-1,GETDATE()), 'yyyyMM') AS 'Accruals Batch Number'
			,FORMAT(EOMONTH(DATEADD(month,-1,GETDATE())), 'dd/MM/yyyy') AS 'Transaction Date'
			,FORMAT(DATEADD(day, 1, EOMONTH(DATEADD(month,-1,GETDATE()))), 'dd/MM/yyyy') AS 'Reverse Date'
			,'Purchase Accruals ' + FORMAT(DATEADD(month,-1,GETDATE()),'MM/yyyy') AS 'Reference'
			,GL00105.ACTNUMST AS 'Account'
			,CASE WHEN PP000100.PP_Module IS NOT NULL THEN
				SUM(PP000101.TRXAMNT)
			ELSE
				SUM(PM10100.CRDTAMNT)
			END AS 'Debit'
			,CASE WHEN PP000100.PP_Module IS NOT NULL THEN
				SUM(PP000101.TRXAMNT)
			ELSE
				SUM(PM10100.DEBITAMT)
			END AS 'Credit'
			,'Accrued Purchases' AS 'Description'
			,'' AS 'Voucher Number'
			,'' AS 'Batch Number'
			,'' AS 'Document Description'
			,'' AS 'Document Number'
			,'' AS 'PO Number'
			,'' AS 'Distribution Reference'
		FROM
			PM10000 AS PM10000 --PM Transaction WORK File (PM10000)
		INNER JOIN
			PM10100 AS PM10100 --PM Distribution WORK OPEN (PM10100)
				on
					PM10100.CNTRLTYP = PM10000.CNTRLTYP
				AND
					PM10100.VCHRNMBR = PM10000.VCHRNMBR
		INNER JOIN
			GL00100 AS GL00100 --Breakdown Account Master (GL00100)
				on
					GL00100.ACTDESCR = 'Accrued Purchases'
		INNER JOIN
			GL00105 AS GL00105 --Account Index Master (GL00105)
				on GL00105.ACTINDX = GL00100.ACTINDX
		LEFT JOIN -- Join to RED
			PP000100 AS PP000100 --Deferral Header Work (PP000100)
				on
					PP000100.CNTRLTYP = PM10100.CNTRLTYP
				AND
					PP000100.PP_Document_Number = PM10100.VCHRNMBR
				AND
					PP000100.PP_Sequencer = PM10100.DSTSQNUM
		LEFT JOIN
			GL00105 AS GL00105_DAIM --Account Index Master (GL00105)
				on
					GL00105_DAIM.ACTINDX = PP000100.ACTINDX
		LEFT JOIN
			PP000101 AS PP000101 --Deferral Line Work (PP000101)
				on
					PP000101.PP_Module = PP000100.PP_Module
				AND
					PP000101.PP_Record_Type = PP000100.PP_Record_Type
				AND
					PP000101.PP_Document_Number = PP000100.PP_Document_Number
				AND
					PP000101.PP_Sequencer = PP000100.PP_Sequencer
				AND
					PP000101.PPOFFSEQ = PP000100.PPOFFSEQ
				AND
					PP000101.CNTRLTYP = PP000100.CNTRLTYP
				AND
					PP000101.VCHRNMBR = PP000100.VCHRNMBR
				AND
					PP000101.DSTSQNUM = PP000100.DSTSQNUM
				AND
					PP000101.GLPOSTDT <= EOMONTH(DATEADD(month,-1,GETDATE()))
		WHERE
			PM10000.BCHSOURC = 'PM_Trxent' --Include only normal transactions
		AND
			PM10000.DOCTYPE = 1 --Include only Invoices
		AND
			PM10100.DISTTYPE = 6 --Include only Purchases Distribution
		GROUP BY
			GL00105.ACTNUMST
			,GL00105_DAIM.ACTNUMST
			,PM10000.DOCTYPE
			,PP000100.PP_Module
		HAVING
			SUM(PP000101.TRXAMNT) > 0 --partial deferral
		OR
			PP000100.PP_Module IS NULL --no deferral
	) AS Accruals
WHERE
	Accruals.Debit > 0
OR
	Accruals.Credit > 0
GO

GRANT SELECT ON uv_ISC_PayablesAccruals TO DYNGRP
GO

Updated 27/05/2022: Added highlight for accrual account join

A client recently logged a support call whereby reports were showing incorrect information, including for transactions which had been deleted. I did some exploring mof data and found that the Sales Transaction Amounts Work (SOP10200) and Sales User-Defined Work History (SOP10106) tables contained rows for transactions which were not in the Sales Transaction Work (SOP10100) table.

From reviewing the data, deleted transactions which had an entry in Sales User-Defined Work History (SOP10106) would also have one in Sales Transaction Amounts Work (SOP10200) making the job of identifying the corrupt ones somewhat easier (Sales User-Defined Work History (SOP10106) contains both Work and History rows).

The first script identifies rows in Sales User-Defined Work History (SOP10106) which are orphaned:

/*
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).
*/
SELECT
	DISTINCT
		SOPL.SOPNUMBE
		,SOPL.SOPTYPE
FROM
	SOP10200 AS SOPL --Sales Transaction Amounts Work (SOP10200)
INNER JOIN
	SOP10106 AS SOPU --Sales User-Defined Work History (SOP10106)
		ON
			SOPU.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPU.SOPTYPE = SOPL.SOPTYPE
LEFT JOIN
	SOP10100 AS SOPH --Sales Transaction Work (SOP10100)
		ON
			SOPH.SOPNUMBE = SOPL.SOPNUMBE
		AND
			SOPH.SOPTYPE = SOPL.SOPTYPE
WHERE
	SOPH.SOPNUMBE IS NULL
GO

Continue reading “Identify and Fix Corrupt SOP Transactions in Microsoft Dynamics GP”

I’ve written this view for a number of different clients over the years, so am posting it here for future reference. It returns only open backorders which have yet top be transferred to order or invoice; it includes PO information so users can easily see if a PO has been raised and if any of it has been receipted.

The SQL below includes the

CREATE VIEW uv_AZRCRV_SOPOpenBackorders AS
/*
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).
*/
SELECT
	['Sales Transaction Work'].SOPNUMBE AS 'SOP Number'
	,['Sales Transaction Work'].SOPTYPE AS 'SOP Type'
	,['Sales Transaction Work'].DOCDATE AS 'Document Date'
	,['Sales Transaction Work'].CSTPONBR AS 'Customer PO Number'
	,['Sales Transaction Work'].CURNCYID AS 'Currency'
	,['Sales Transaction Work'].CUSTNMBR AS 'Customer Number'
	,['Sales Transaction Work'].CUSTNAME AS 'Customer Name'
	,['Sales Transaction Amounts Work'].ITEMNMBR AS 'Item Number'
	,['Sales Transaction Amounts Work'].ITEMDESC AS 'Item Description'
	,['Sales Transaction Amounts Work'].QUANTITY AS 'Original Backorder Quantity'
	,['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV AS 'Current Backorder Quantity'
	,['Sales Transaction Amounts Work'].QUANTITY - (['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) AS 'Quantity Shipped'
	,['Sales Transaction Amounts Work'].UNITPRCE AS 'Unit Price'
	,['Sales Transaction Amounts Work'].XTNDPRCE AS 'Extended Price'
	,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].UNITPRCE AS 'Current Extended Price'
	,['Sales Transaction Amounts Work'].ORUNTPRC AS 'Originating Unit Price'
	,['Sales Transaction Amounts Work'].OXTNDPRC AS 'Originating Extended Price'
	,(['Sales Transaction Amounts Work'].QTYTORDR + ['Sales Transaction Amounts Work'].QTYTOINV) * ['Sales Transaction Amounts Work'].ORUNTPRC AS 'Current Originating Extended Price'
	,['Sales Transaction Amounts Work'].LOCNCODE AS 'Site ID'
	,['Sales Transaction Amounts Work'].ReqShipDate AS 'Requested Ship Date'
	,['SOP_POP Link'].PONUMBER AS 'PO Number'
	,['Purchase Order Lines'].VENDORID AS 'Vendor ID'
	,['SOP_POP Link'].QTYRECVD AS 'Quantity Received'
	,['Purchase Order Lines'].PRMSHPDTE AS 'Promised Ship Date'
	,['Sales Line Comment Work and History'].CMMTTEXT AS 'Comment Text'
FROM
	SOP10100 AS ['Sales Transaction Work'] --Sales Transaction Work (SOP10100)
INNER JOIN
	SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		ON
			['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE
		AND
			['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE
INNER JOIN
	RM00101 AS ['RM Customer Master'] --RM Customer MSTR (RM00101)
		ON
			['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR
LEFT JOIN
	SOP60100 AS ['SOP_POP Link'] --SOP_POPLink (SOP60100)
		ON
			['SOP_POP Link'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE
		AND
			['SOP_POP Link'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE
		AND
			['SOP_POP Link'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ
		AND
			['SOP_POP Link'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ
LEFT JOIN
	SOP10202 AS ['Sales Line Comment Work and History'] --Sales Distribution Work and History (SOP10102)
		ON
			['Sales Line Comment Work and History'].SOPTYPE = ['Sales Transaction Amounts Work'].SOPTYPE
		AND
			['Sales Line Comment Work and History'].SOPNUMBE = ['Sales Transaction Amounts Work'].SOPNUMBE
		AND
			['Sales Line Comment Work and History'].CMPNTSEQ = ['Sales Transaction Amounts Work'].CMPNTSEQ
		AND
			['Sales Line Comment Work and History'].LNITMSEQ = ['Sales Transaction Amounts Work'].LNITMSEQ
LEFT JOIN
	POP10110 AS ['Purchase Order Lines'] --Purchase Order Line (POP10110)
		ON
			['Purchase Order Lines'].PONUMBER = ['SOP_POP Link'].PONUMBER
		AND
			['Purchase Order Lines'].ORD = ['SOP_POP Link'].ORD
WHERE
	['Sales Transaction Work'].SOPTYPE = 5
GO

GRANT SELECT ON uv_AZRCRV_SOPOpenBackorders TO DYNGRP
GO

A client reported an urgent support call a short time ago that they could not post any journals and were receiving this error when they tried:

This error was produced for all users in only one of their 10+ companies; the other companies could have a journal posted by anyone with no issues. My initial question was to ask what had been changed or deployed in that database, but was told nothing. This meant some investigation.

I fairly quickly found a post from Tim Wappat covering how SQL indexed views are incompatible with Dynamics GP and will result in the exact error the client was seeing. this would make sense as the error referenced indexed views.

I used my find all SQL objects script to look for any object containing the SCHEMABINDING keyword, and found two SQL views in that one database.

After passing this information back to the client they again checked around and found a developer had deployed two SQL views for a report to the database first thing that morning; they hadn’t mentioned them as they didn’t expect them to have caused the problem. Once the views were updated to remove the index, the error was no longer produced when journals were posted.

It seems that while I’ve posted about how to update segment descriptions from CSV in Microsoft Dynamics GP and how to copy them to a new company or even all companies, I’ve never actually posted the script I use to insert them.

I needed this script the other day and ended up using the update descriptions one as the basis for a new script to insert segments into Dynamics GP; this script will pick the text file (or CSV file if you change the second highlighted section to a comma), update any existing segments and insert new segments into the Segment Description Master (GL40200) table.

/*
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).
*/
CREATE TABLE #Segments
	(
		SGMTNUMB VARCHAR(100)
		,SGMNTID VARCHAR(100)
		,DSCRIPTN VARCHAR(100)
	)
GO

BULK INSERT
	#Segments
FROM
	'C:\Integrations\COA\Segments.txt'
WITH
	(
		FIELDTERMINATOR = '\t'
		,ROWTERMINATOR = '\n'
		,FIRSTROW = 2
	)
GO

UPDATE
	Segments
SET
	Segments.SGMTNUMB = NewSegments.SGMTNUMB
	,Segments.SGMNTID = NewSegments.SGMNTID
	,Segments.DSCRIPTN = Left(NewSegments.DSCRIPTN, 31)
FROM
	GL40200 AS Segments
INNER JOIN
	#Segments AS NewSegments
		ON
			NewSegments.SGMTNUMB = Segments.SGMTNUMB
		AND
			NewSegments.SGMNTID = Segments.SGMNTID
GO

INSERT INTO GL40200
	(
		SGMTNUMB
		,SGMNTID
		,DSCRIPTN
	)
--VALUES
   (
		SELECT
			SGMTNUMB
			,SGMNTID
			,LEFT(DSCRIPTN, 31)
		FROM
			#Segments AS NewSegments
		WHERE
			(
				SELECT
					COUNT(*)
				FROM
					GL40200 AS Segments
				WHERE
					Segments.SGMTNUMB = NewSegments.SGMTNUMB
				AND
					Segments.SGMNTID = NewSegments.SGMNTID
			) = 0
	)
GO

DROP TABLE #Segments
GO

As always with a SQL script which makes changes, I’d make sure you have a good backup and test the script before running it in case you encounter problems.

I was doing some training on Management Reporter for a client the other week and had an issue to follow up on. The issue was around multicurrency conversion which wasn’t working correctly. When doing some testing around the issue, I was able to reproduce the same currency error the client had encountered:

The currency did exist and on most reports was working fine. It took me a little investigation and research to find that this was an issue with the Data Mart connector; the reports using the legacy connector were working correctly.

This issue is covered in Microsoft KB Article 3058400; when using the Data Mart connector, currency lookups are done using the ISO Code of the Currency rather than the Currency Code itself.