Year: 2021

This post is part of the series on Implementing Post Master Enterprise for Microsoft Dynamics GP from Envisage Software.

Post Master Enterprise automates the posting process in Microsoft Dynamics GP which can provide for efficiencies by:

1. Eliminating the need for users to perform the repetitive task of selecting batches to post.
2. Reducing the need for users to check for batches to post.
3. Automatically complete the final step of posting a batch created through an integration.
4. Ensure inventory and account activity is promptly updated.
5. Minimise system load by allowing posting to be scheduled out of hours.
6. Control when different types of batches are posted.
7. Automatically post batches after Workflow approval.

There are three versions of Post master available from Envisage Software with each version building on the capabilities of the lower version:

1. Standard
   • Automatically post batches.
   • Schedule out of hours.
   • Multiple different batch reporting options.
2. Enterprise
   • Runs as Windows service meaning:
     • No Dynamics GP client needs to be running.
     • Automatically starts with Windows.
3. Multi-Instance
   • Parallel post multiple batches for improved performance.

When selling Post Master to clients, I have never sold the Standard version; the Enterprise version is far more useful as it runs as a Windows service and will automatically start with Windows, reducing the need for a user to log onto Windows and into Dynamics GP. For larger clients, I have sold the Multi-Instance version where there was a lot of companies which would require concurrent processing.

With the Enterprise version, you can specify posting of multiple types of batches across multiple companies, but the processing will be strictly sequential.

Whether you opt for Enterprise or Multi-Instance will depend on the number, frequency and time sensitivity of posting. The initial install for both versions is the same, with some additional steps to enable the muli-instance processing.

Over the next posts in this series, I will be installing and configuring the Enterprise version of Post Master.

This post is part of the series on Implementing Post Master Enterprise for Microsoft Dynamics GP.

Envisage Software was started by Andrew Dean, an experienced software development manager with 10 years experience of working for a leading Microsoft Dynamics GP client, in 2007. Their aim is to provide quality ISV tools and customised business software solutions for Microsoft Dynamics GP to both customers and partners.

They have a number of products for Microsoft Dynamics GP available:

• Post Master which is the subject of this series.
• Exchange Rate Feed which imports currency exchange rates into Dynamics GP.
• Search Master which adds additional search capabilities to Dynamics GP.
• InSight — Mobile app which allows out-of-office users access to information from Dynamics GP in real-time.
• VST Controls which extends the power of Visual Studio Tools for Dynamics GP by allowing a .NET developer to add Windows Component Controls to any GP window.

In this series of posts I am going to look at implementing Post Master Enterprise for Microsoft Dynamics GP from Envisage Software, an ISV based in Australia.

Post Master is one of the auto-posting tools available for Microsoft Dynamics GP and is one which I have sold a number of times. I’ll take a look at the capabilities of the application itself, before moving onto the installation and configuration.

This series index will automatically update as posts go-live, but if you’re reading via a syndication, you’ll need to go to the original page for the updates: Implementing Post Master Enterprise

Implementing Post Master Enterprise
Who are Envisage Software?
What is Post Master Enterprise?
What batch types are supported?
Incompatible Modules
Prerequisites
Installation
Configure Windows Service
First Run
Create Auto-Posting Rules
Extending Post Master Enterprise

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.