Category: GP (Great Plains)

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client to list out all tax detail transactions in the system; there is a SmartList which will show this information, but they wanted to build a report outside of Dynamics GP for a user who did not have access to GP.

CREATE VIEW [dbo].[uv_AZRCRV_TaxDetailTransactions] 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
	RTRIM(TX30000.TAXDTLID) AS 'Tax Detail',
	RTRIM(TX00201.TXDTLDSC) AS 'Tax Detail Description',
	CASE WHEN TX00201.TXDTLTYP = 1 THEN
		'Sales'
		
	ELSE
		'Purchases'
	END AS 'Tax Detail Type',
	CASE
		WHEN
			TX30000.TXDTLAMT > 0
		THEN
			TX30000.TXDTLAMT
		ELSE
			TX30000.TXDTLPCT
		END AS 'Tax Detail %',
	RTRIM(TX30000.DOCNUMBR) AS 'Document Number',
	RTRIM(TX30000.CustomerVendor_ID) AS 'Master ID',
	CASE
		WHEN TX30000.SERIES IN (1,2,3) THEN
			RM00101.CUSTNAME
		WHEN TX30000.SERIES IN (4,12) THEN
			PM00200.VENDNAME
		ELSE
			''
		END AS 'Master Name',
	TX30000.DOCDATE AS 'Document Date',
	TX30000.PSTGDATE AS 'Posting Date',
	TX30000.DOCAMNT + TX30000.Taxable_Amount AS 'Gross Amount',
	TX30000.DOCAMNT AS 'Net Amount',
	TX30000.Taxable_Amount AS 'Taxable Amount',
	TX30000.TAXAMNT AS 'Tax Amount',
	CASE WHEN TX30000.SERIES = 1 AND TX30000.DOCTYPE = 3 THEN
		'Invoice'
	WHEN TX30000.SERIES = 1 AND TX30000.DOCTYPE = 5 THEN
		'Return'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 1 THEN
		'Invoice'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 5 THEN
		'Credit Note'
	WHEN TX30000.SERIES = 4 AND TX30000.DOCTYPE = 6 THEN
		'Payment'
	END AS 'Document Type',
	TXGL00105.ACTNUMST AS 'Tax Account',
	TX30000.Tax_Date AS 'Tax Date',
	TX30000.ORTAXAMT AS 'Originating Tax Amount',
	TX30000.Originating_Taxable_Amt AS 'Originating Taxable Amount',
	TX30000.ORDOCAMT AS 'Originating Net Amount',
	CASE WHEN TX30000.VOIDSTTS = 0 THEN
		'No'
	ELSE
		'Yes'
	END AS 'Voided?',
	MC40200.CRNCYDSC AS 'Currency',
	CASE WHEN TX30000.SERIES = 1 THEN
		'Sales'
	ELSE
		'Purchases'
	END AS 'Series',
	CASE WHEN ISNULL(PMCCODE.ECFLAG,ISNULL(PMCCODE.ECFLAG, 0)) = 1 THEN
		'Yes'
	ELSE
		'No'
	END AS 'EU Member?'
	,TX30000.PERIODID
FROM
	TX30000 WITH (NOLOCK) --Tax History (TX30000)
INNER JOIN
	DYNAMICS..MC40200 WITH (NOLOCK) --Currency Setup (MC40200)
		ON
			MC40200.CURRNIDX = TX30000.CURRNIDX
INNER JOIN
	TX00201 WITH (NOLOCK) --Sales/Purchases Tax Master (TX00201)
		ON
			TX30000.TAXDTLID = TX00201.TAXDTLID
LEFT JOIN
	GL00105 AS TXGL00105 WITH (NOLOCK) --Account Index Master (GL00105)
		ON
			TXGL00105.ACTINDX = TX00201.ACTINDX
LEFT JOIN
	RM00101 WITH (NOLOCK) --RM Customer MSTR (RM00101)
		ON
			TX30000.CustomerVendor_ID = RM00101.CUSTNMBR
LEFT JOIN
	VAT10001 AS RMCCODE WITH (NOLOCK) --VAT Country Code MSTR (VAT10001)
		ON
			RMCCODE.CCode = RM00101.CCode
LEFT JOIN
	PM00200 WITH (NOLOCK) --PM Vendor Master File (PM00200)
		ON
			TX30000.CustomerVendor_ID = PM00200.VENDORID
LEFT JOIN
	VAT10001 AS PMCCODE WITH (NOLOCK) --VAT Country Code MSTR (VAT10001)
		ON
			PMCCODE.CCode = PM00200.CCode
GO

GRANT SELECT ON uv_AZRCRV_TaxDetailTransactions TO DYNGRP
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created to allow a client to check combinations of account (segment 2) and category combinations to make sure all accounts with those combinations had the same posting type assigned.

/*
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
	GL100.ACTNUMBR_2 AS 'Cost Account'
	,GL102.ACCATDSC AS 'Category'
	,CASE WHEN GL100.PSTNGTYP = 0 THEN
		'Balance Sheet'
	ELSE
		'Profit and Loss'
	END AS 'Posting Type'
FROM
	GL00100 AS GL100 WITH (NOLOCK) --Breakdown Account Master (GL00100)
INNER JOIN
	GL00102 AS GL102 WITH (NOLOCK) --Account Category Master (GL00102)
		ON GL102.ACCATNUM = GL100.ACCATNUM

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a user who was migrating from producing financial reports using Management Reporter to using Jet Reports.

Their account categories had been simply set to and Income and Expenditure for Management Reporter which could easily use the user-defined fields. However, reporting off the user defined fields in Jet Reports is somewhat more complex so they added new categories into Dynamics GP (using my insert categories from text file script) and needed to update the categories assigned to the G/L Accounts.

Fortunately, the previous entries in UDF2 were the exact values inserted as categories, so we could use a simple script to set the account category to the same as UDF2 by doing a lookup on the Account Category Master (GL00102) 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).
*/
SELECT USERDEF2,ACCATNUM,* FROM GL00100
GO
UPDATE
	['Account Master']
SET
	ACCATNUM = ['Account Category Master'].ACCATNUM
FROM
	GL00100 AS ['Account Master'] --Breakdown Account Master (GL00100)
INNER JOIN
	GL00102 AS ['Account Category Master'] --Account Category Master (GL00102)
		ON
			(['Account Category Master'].ACCATDSC = ['Account Master'].USERDEF2
WHERE
	LEN(RTRIM(['Account Master'].USERDEF2)) > 0
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client who had quite a few bank accounts and want to quickly check which G/L Accounts were linked to each bank account.

/*
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
	CM.CHEKBKID
	,CM.DSCRIPTN
	,GL105.ACTNUMST
	,GL100.ACTDESCR
FROM
	CM00100 AS CM WITH (NOLOCK) --CM Checkbook Master (CM00100)
INNER JOIN
	GL00100 AS GL100 WITH (NOLOCK) --Breakdown Account Master (GL00100)
		ON GL100.ACTINDX = CM.ACTINDX
INNER JOIN
	GL00105 AS GL105 WITH (NOLOCK) --Account Index Master (GL00105)
		ON GL105.ACTINDX = CM.ACTINDX

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will change en masse the email message assigned to workflow steps; it was created for a client after they created new messages for each workflow type and had a lot of workflow steps which needed to be assigned to the new email message.

The two parameters to set are highlighted; care needs to be taken, especially with the @EmailMessageID as there is no validation in the script.

/*
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).
*/
DECLARE @Workflow_Type_Name VARCHAR(40) = 'Purchase Requisition Approval'
DECLARE @EmailMessageID VARCHAR(30) = 'AZRCRV POREQ ASSIGNED'

UPDATE
	['Workflow Step Table']
SET
	EmailMessageID = @EmailMessageID
	,Workflow_Step_Send_Email = 1
FROM
	WF100003 AS ['Workflow Step Table'] --Workflow Step Table (WF100003)
INNER JOIN
	WF100002 AS ['Workflow Master'] --Workflow Master (WF100002)
		ON
			['Workflow Master'].Workflow_Name = ['Workflow Step Table'].Workflow_Name
WHERE
	['Workflow Master'].Workflow_Type_Name = @Workflow_Type_Name
AND
	['Workflow Step Table'].Workflow_Step_Send_Email = 0
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created a while ago when I was doing some testing on a client system and wanted to quickly insert some creditor item numbers; in fact, it inserts a creditor item number for every single creditor/item combination.

/*
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 INTO dbo.IV00103
	(ITEMNMBR
	,VENDORID
	,ITMVNDTY
	,VNDITNUM
	,QTYRQSTN
	,QTYONORD
	,QTY_Drop_Shipped
	,LSTORDDT
	,LSORDQTY
	,LRCPTQTY
	,LSRCPTDT
	,LRCPTCST
	,AVRGLDTM
	,NORCTITM
	,MINORQTY
	,MAXORDQTY
	,ECORDQTY
	,VNDITDSC
	,Last_Originating_Cost
	,Last_Currency_ID
	,FREEONBOARD
	,PRCHSUOM
	,CURRNIDX
	,PLANNINGLEADTIME
	,ORDERMULTIPLE
	,MNFCTRITMNMBR)
--VALUES
	(SELECT
		IV101.ITEMNMBR
		,PM2.VENDORID
		,2
		,IV101.ITEMNMBR
		,0,0,0
		,'1900-01-01 00:00:00.000'
		,0,0
		,'1900-01-01 00:00:00.000'
		,0,0,0,0,0,0
		,IV101.ITEMDESC
		,0
		,'' -- CURRENCY
		,1,'',9,0,1,''
	FROM
		PM00200 AS PM2
	CROSS JOIN
		IV00101 AS IV101
	LEFT JOIN
		IV00103 AS IV103
			ON
				IV103.VENDORID = PM2.VENDORID
			AND
				IV103.ITEMNMBR = IV101.ITEMNMBR
	WHERE
		IV103.VENDORID IS NULL)

If you use this script, make sure you test thoroughly before running on a production system. After running the script, you may need to run an Inventory Reconcile.

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script will return the approver of a workflow. It complements two other functions which I have previously posted:

• Return approval status
• Return last approval comment

IF OBJECT_ID (N'uf_AZRCRV_GetWorkflowApprover', N'FN') IS NOT NULL
    DROP FUNCTION uf_AZRCRV_GetWorkflowApprover
GO
CREATE FUNCTION [dbo].[uf_AZRCRV_GetWorkflowApprover](@WorkflowTypeName CHAR(50),@WfBusObjKey CHAR(20))
	RETURNS VARCHAR(50)
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).
*/
/*
Author: Ian Grieve
Version: 1.0
Date: 1/3/2017

Returns Workflow Approval status of a specified workflow item.

Requires parameters of WorkflowTypeName and WfBusObjKey

Valid Workflow Type Names are (as of Microsoft Dynamics GP 2016 R2):
	General Ledger Batch Approval
	Receivables Batch Approval
	Payables Batch Approval
	Payables Transaction Approval
	Purchase Order Approval
	Purchase Requisition Approval
	Vendor Approval
	Employee Profile Approval
	Employee Skills Approval
	Payroll Direct Deposit Approval
	Payroll Timecard Approval
	Payroll W4 Approval
	Expense Report Approval
	Timesheet Approval
	Smartlist Designer View Approval
*/
BEGIN
	RETURN ISNULL((
		SELECT TOP 1 
			WFU.ADDisplayName
		FROM 
			WF30100 AS WF --Workflow History (WF30100)
		INNER JOIN
			WFI10002 AS WFI --Workflow Instance Master (WFI10002)
				ON WFI.WorkflowInstanceID = WF.WorkflowInstanceID
		INNER JOIN
			WF40200 WFU --Workflow Users (WF40200)
				ON WFU.ADLogin = WF.Workflow_History_User
		WHERE
			WFI.Workflow_Type_Name = @WorkflowTypeName
		AND
			WFI.WfBusObjKey = @WfBusObjKey
		AND
			WF.Workflow_Action IN (3) --APPROVED
		ORDER BY
			WF.DEX_ROW_ID DESC)
	,'')
END
GO

GRANT SELECT ON uf_AZRCRV_GetWorkflowApprover TO DYNGRP
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created when I needed to verify that all work status Payables transactions were attached to a valid batch.

CREATE VIEW [dbo].[uv_AZRCRV_VerifyBatchForPayablesTransactions] 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
	PM.BACHNUMB AS 'PM Batch Number'
	,ISNULL(SY.BACHNUMB, 'Missing') AS 'Batch Record'
	,PM.VCHRNMBR AS 'Voucher Number'
	,PM.DOCDATE AS 'Document Date'
FROM
	PM10000 AS PM WITH (NOLOCK) --PM Transaction WORK File (PM10000)
LEFT JOIN
	SY00500 AS SY WITH (NOLOCK) --Posting Definitions Master Dup (SY00500)
		ON
			PM.BACHNUMB = SY.BACHNUMB
		AND
			SY.BCHSOURC = 'PM_TRXENT'
		AND
			SERIES = 4
WHERE
	SY.BACHNUMB IS NULL
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script was created for a client to use in an external system to verify vendor/document number combinations.

CREATE VIEW [dbo].[uv_AZRCRV_VendorDocumentNumberLookup] 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
	DISTINCT *
FROM
	(
		SELECT
			VENDORID
			,VNDDOCNM AS DOCNUMBR
		FROM
			POP10300 --Purchasing Receipt Work (POP10300)
		WHERE
			POPTYPE = 2
		UNION ALL
			SELECT
				VENDORID
				,VNDDOCNM AS DOCNUMBR
			FROM
				POP30300 --Purchasing Receipt History (POP30300)
			WHERE
				POPTYPE = 2
		UNION ALL
			SELECT
				VENDORID
				,DOCNUMBR
			FROM
				PM00400 --PM Key Master File (PM00400)
	) AS TRX
GO

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posting the scripts I wrote against Microsoft Dynamics GP over the 19 years before I stopped working with Dynamics GP.

This script returns segments which exist, but which are not linked to accounts; specifically it is checking segment 2 which the client used to store the cost centre.

CREATE VIEW [dbo].[uv_AZRCRV_UnlinkedCostCentres] 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
	['Segment Description Master'].SGMNTID
	,['Segment Description Master'].DSCRIPTN
FROM
	GL40200 AS ['Segment Description Master'] WITH (NOLOCK) --Segment Description Master (GL40200)
LEFT JOIN
	GL00105 AS ['Account Index Master'] WITH (NOLOCK) --Account Index Master (GL00105)
		ON
			['Account Index Master'].ACTNUMBR_2 = ['Segment Description Master'].SGMNTID
WHERE
		['Segment Description Master'].SGMTNUMB = 2
AND
	['Account Index Master'].ACTINDX IS NULL
GO

GRANT SELECT ON uv_AZRCRV_UnlinkedCostCentres TO DYNGRP
GO