Category: GP (Great Plains)

This script is part of the SQL Scripts for Microsoft Dynamics GP where I will be posted 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 for a client who wanted a SQL query to use in SSRS; it was put together in conjunction with the finance team to return the data exactly as they wanted (including one column with the amount shown as a positive or negative) it and then passed across to someone in IT to wrap the SSRS report around it.

/*
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
	GLT.SOURCDOC AS 'Source Document'
	,GLT.JRNENTRY AS 'Journal Entry'
	,GLT.SERIES AS 'Series'
	,GLT.TRXDATE AS 'TRX Date'
	,GL105.ACTNUMST AS 'Account Number'
	,GL100.ACTDESCR AS 'Account Description'
	,GLT.Amount AS 'Amount'
	,GLT.PERIODID AS 'Period ID'
	,GLT.USWHPSTD AS 'User Who Posted'
	,GLT.REFRENCE AS 'Reference'
	,GLT.DSCRIPTN AS 'Description'
	,GL105.ACTNUMBR_3 AS 'Segment 3'
	,GLT.ORMSTRNM AS 'Original Master Name'
	,GL100.USERDEF2 AS 'User Defined 2'
	,GL100.USRDEFS1 AS 'User Defined 3'
	,GL100.USRDEFS2 AS 'User Defined 4'
	,GL100.ACTNUMBR_1 AS 'Segment 1'
	,GLT.ORCTRNUM AS 'Originating Control Number'
FROM
	(SELECT
		ACTINDX,OPENYEAR AS 'Year',GLH.JRNENTRY,TRXDATE,SOURCDOC,SERIES,CASE WHEN DEBITAMT > 0 THEN DEBITAMT ELSE CRDTAMNT *-1 END AS 'Amount'
		,PERIODID,USWHPSTD,REFRENCE,DSCRIPTN,ORMSTRNM,ORCTRNUM
	FROM
		GL10001 AS GLL WITH (NOLOCK)
	INNER JOIN
		GL10000 AS GLH WITH (NOLOCK)
			ON
				GLH.JRNENTRY = GLL.JRNENTRY
	UNION ALL
		SELECT
			ACTINDX,OPENYEAR AS 'Year', JRNENTRY,TRXDATE,SOURCDOC,SERIES,CASE WHEN DEBITAMT > 0 THEN DEBITAMT ELSE CRDTAMNT *-1 END AS 'Amount'
		,PERIODID,USWHPSTD,REFRENCE,DSCRIPTN,ORMSTRNM,ORCTRNUM
		FROM
			GL20000 WITH (NOLOCK)
		UNION ALL
			SELECT
				ACTINDX,HSTYEAR AS 'Year',JRNENTRY,TRXDATE,SOURCDOC,SERIES,CASE WHEN DEBITAMT > 0 THEN DEBITAMT ELSE CRDTAMNT *-1 END AS 'Amount'
		,PERIODID,USWHPSTD,REFRENCE,DSCRIPTN,ORMSTRNM,ORCTRNUM
			FROM
				GL30000 WITH (NOLOCK)
			) AS GLT
INNER JOIN
	GL00105 AS GL105 WITH (NOLOCK)
		ON
			GL105.ACTINDX = GLT.ACTINDX
INNER JOIN
	GL00100 AS GL100 WITH (NOLOCK)
		ON
			GL100.ACTINDX = GL105.ACTINDX

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

This script will return a list of items calculating the status of serial number tracked items based on the posting status and the sales transaction type.

/*
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
	['Purchasing Serial Lot History'].ITEMNMBR
	,['Item Master'].ITEMDESC
	,['Purchasing Serial Lot History'].SERLTNUM
	,['Purchasing Serial Lot History'].UNITCOST
	,['Purchasing Serial Lot History'].DATERECD
	,['Sales Serial/Lot Work and History'].SOPNUMBE
	,CASE WHEN ['Sales Serial/Lot Work and History'].POSTED = 1 THEN
		'Invoiced'
	WHEN ['Sales Serial/Lot Work and History'].SOPTYPE = 3 THEN 
		'Despatched'
	WHEN ['Sales Serial/Lot Work and History'].POSTED IS NULL THEN 
		'Available'
	ELSE
		'Allocated'
	END AS SHIPDSTS
FROM
	POP30330 AS ['Purchasing Serial Lot History'] --Purchasing Serial Lot History (POP30330)
INNER JOIN
	IV00101 AS ['Item Master'] --Item Master (IV00101)
		ON
			['Item Master'].ITEMNMBR = ['Purchasing Serial Lot History'].ITEMNMBR
LEFT JOIN
	SOP10201 AS ['Sales Serial/Lot Work and History'] --Sales Serial/Lot Work and History (SOP10201)
		ON
			['Sales Serial/Lot Work and History'].ITEMNMBR = ['Purchasing Serial Lot History'].ITEMNMBR
		AND
			['Sales Serial/Lot Work and History'].SERLTNUM = ['Purchasing Serial Lot History'].SERLTNUM
GO

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

This script was created for one client but then used for a few. It adds the required table join and the fields to allow line comment text from purchase requisitions to be included on the workflow approval notification emails. Once the script has been run you can add the fields to the email through E-mail Message Setup.

/*
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).
*/
-- add table join
INSERT INTO
	CO00122 --QueryDesigner_Relationships (CO00122)
		(FieldsListGuid,Group_ID,SEQNUMBR,FromDictID,FromTable,FromTablePhysicalName,FromField,FromFieldPhysicalName,ToDictID,ToTable,ToTablePhysicalName,ToField,ToFieldPhysicalName,JoinType)
	VALUES
		('34635D2B-6C34-4283-87F7-2925445E196D ',5,0,0,'Purchasing Requisition Line','POP10210','','',0,'Requisition Comment','POP10550','','',3)
		,('34635D2B-6C34-4283-87F7-2925445E196D ',5,1,0,'Purchasing Requisition Line','POP10210','POP Requisition Number','POPRequisitionNumber',0,'Requisition Comment','POP10550','PO Number','POPNUMBE',3)
		,('34635D2B-6C34-4283-87F7-2925445E196D ',5,2,0,'Purchasing Requisition Line','POP10210','ORD','ORD',0,'Requisition Comment','POP10550','ORD','ORD',3)
GO

-- add line comment to email
INSERT INTO WF40202 --Workflow Template Fields (WF40202)
	(Workflow_Type_Name,Email_Message_Type,SEQNUMBR,ORD_LINE,WF_Template_Field_Type,TableSeries,TableDictID,RSRCID,TablePhysicalName,FieldPhysicalName,FieldName,FieldDataType,TableRelationship,AdditionalFields)
VALUES
	('Purchase Requisition Approval',2,64,0,18,4,0,0,'POP10550','CMMTTEXT','Line Comment Text',5,3,0)
	,('Purchase Requisition Approval',3,64,0,18,4,0,0,'POP10550','CMMTTEXT','Line Comment Text',5,3,0)
GO

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

This script returns a list of GL accounts along with the Posting Type and Account Category; I think it was created as a way to quickly give a user a file to check the configuration.

/*
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
	GL5.ACTNUMST AS 'Account'
	,GL1.ACTDESCR AS 'Description'
	,CASE WHEN GL1.PSTNGTYP =  0 THEN 'Balance Sheet' ELSE 'Profit & Loss' END AS 'Posting Type'
	,GL2.ACCATDSC AS 'Account Category'
FROM
	GL00105 GL5 --Account Index Master (GL00105)
INNER JOIN
	GL00100 GL1 --Breakdown Account Master (GL00100)
		ON
			GL1.ACTINDX = GL5.ACTINDX
INNER JOIN
	GL00102 GL2 --Account Category Master (GL00102)
		ON
			GL2.ACCATNUM = GL1.ACCATNUM

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

This script which unmatches and unmarks transactions and statement entries in the Advanced Bank Reconciliation module looks to be a variation of this one I posted in February of this year, although the one below is an older version. I think the difference is that this script also unmarks the transaction and statement items as well as removing the match.

/*
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 @BANK VARCHAR(15) = 'CURRENT'

UPDATE
	NCABR012 --NC_Matched_GL_TRX (NCABR012)
SET
	NC_Match_Number = 0
	,NC_Matched = 0
WHERE
	BANKID = @BANK
AND
	NC_Matched > 0

UPDATE
	NCABR011 --NC Matched Statement Trx 11003k (NCABR011)
SET
	NC_Match_Number = 0
	,NC_Matched = 0
WHERE
	BANKID = @BANK
AND
	NC_Matched > 0
GO

DECLARE @BANK VARCHAR(15) = 'CURRENT'

UPDATE
	NCABR012 --NC_Matched_GL_TRX (NCABR012)
SET
	NC_Marked = 0
WHERE
	BANKID = @BANK
AND
	NC_Marked > 0

UPDATE
	NCABR011 --NC Matched Statement Trx 11003k (NCABR011)
SET
	NC_Marked = 0
WHERE
	BANKID = @BANK
AND
	NC_Marked > 0
GO

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

This script checks the status of Analytical Accounting in all companies connected to the system database.

/*
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
	['Company Master'].CMPANYID AS CompanyID,
	['Company Master'].INTERID AS SQLDatabase,
	['Company Master'].CMPNYNAM AS CompanyName,
	['Analytical Acctg. Company Setup'].aaCompanyStatus AS AAType,
	CASE ['Analytical Acctg. Company Setup'].aaCompanyStatus
		WHEN 4 THEN 'Activated'
		WHEN 5 THEN 'Activation previously failed'
		ELSE 'Not activated'
	END AS AAStatus
FROM
	DYNAMICS..AAG00104 AS ['Analytical Acctg. Company Setup']
JOIN
	DYNAMICS..SY01500 AS ['Company Master']
		ON
			['Company Master'].CMPANYID = ['Analytical Acctg. Company Setup'].CMPANYID
ORDER BY
	AAType DESC, CompanyID

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

This script returns a list of work status sales transactions including the different quantity types.

/*
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 @ITEMNMBR VARCHAR(30) = '100XLG'

SELECT
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE
	,SUM([Item Quantity Master'].QTYONHND - [Item Quantity Master'].ATYALLOC - ISNULL(['Sales Transaction Amounts Work'].QTYALLOC,0) - ISNULL(['Sales Transaction Amounts Work Backorder'].QTYALLOC,0)) AS 'QTYAVAIL'
	,SUM([Item Quantity Master'].QTYONHND) AS QTYONHND
	,SUM([Item Quantity Master'].ATYALLOC)  AS 'QTYALLOC'
	,SUM(['Sales Transaction Amounts Work'].QTYALLOC) AS 'OPENORDERS'
	,SUM([Item Quantity Master'].QTYBKORD) AS 'OPENBACKORDERS'
	,SUM(['Sales Transaction Amounts Work Backorder'].QTYONPO) AS 'QTYONORD'
	,2 AS RCRDTYPE
FROM
	IV00102 AS [Item Quantity Master'] --Item Quantity Master (IV00102)
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 2
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) AS ['Sales Transaction Amounts Work']
		ON
			['Sales Transaction Amounts Work'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work'].LOCNCODE = [Item Quantity Master'].LOCNCODE
LEFT JOIN
	(
		SELECT
			ITEMNMBR
			,LOCNCODE
			,SUM((QTYREMAI - ATYALLOC) * QTYBSUOM) AS QTYALLOC
			,SUM(QTYONPO) AS QTYONPO
		FROM
			SOP10200 AS ['Sales Transaction Amounts Work'] --Sales Transaction Amounts Work (SOP10200)
		WHERE
			['Sales Transaction Amounts Work'].SOPTYPE = 5
		GROUP BY
			ITEMNMBR
			,LOCNCODE
			,SOPTYPE
	) AS ['Sales Transaction Amounts Work Backorder']
		ON
			['Sales Transaction Amounts Work Backorder'].ITEMNMBR = [Item Quantity Master'].ITEMNMBR
		AND
			['Sales Transaction Amounts Work Backorder'].LOCNCODE = [Item Quantity Master'].LOCNCODE
WHERE
	[Item Quantity Master'].RCRDTYPE = 2
AND
	[Item Quantity Master'].ITEMNMBR = @ITEMNMBR
GROUP BY
	[Item Quantity Master'].ITEMNMBR
	,[Item Quantity Master'].LOCNCODE

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

This script updates inventory item accounts from a CSV file; I have posted a similar script before, but the one in this article is updating more of the account fields.

As with any script which does updates, test the script and make sure you have a good backup before running on a production system.

/*
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 #StockCodes
	(ITEMNMBR VARCHAR(100)
	,ITEMDESC VARCHAR(100)	--Item Description(Not Used)
	,SALSCTGY VARCHAR(100)	--Sales Category (Not Used)
	,COGSCTGY VARCHAR(100)	--Cost of Goods Sold (Not Used)
	,ASMVRIDX VARCHAR(100)	--Assembly Variance
	,IVCOGSIX VARCHAR(100)	--Cost of Goods Sold
	,IVDMGIDX VARCHAR(100)	--Damaged
	,IVINSVIX VARCHAR(100)	--In Service
	,IVINUSIX VARCHAR(100)	--In Use
	,IVRETIDX VARCHAR(100)	--Inventory Returns
	,IVIVINDX VARCHAR(100)	--Inventory
	,IVIVOFIX VARCHAR(100)	--Inventory Offset
	,PURPVIDX VARCHAR(100)	--Purchase Price Variance
	,IVSLSIDX VARCHAR(100)	--Sales
	,IVSLDSIX VARCHAR(100)	--Markdowns
	,IVSLRNIX VARCHAR(100)	--Sales Returns
	,UPPVIDX VARCHAR(100)	--Unrealised Purchase Price Variance
	,IVVARIDX VARCHAR(100)	--Variance
	)
GO

BULK
 INSERT #StockCodes
FROM 'D:\PI Files\Amino Go-Live Scripts\22. Update Accounts.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

UPDATE
	IV
SET
	IVIVINDX = AIMIVIVINIX.ACTINDX	--Inventory
	,IVIVOFIX = AIMIVIVOFIX.ACTINDX	--Inventory Offset
	,IVCOGSIX = AIMIVCOGSIX.ACTINDX	--Cost of Goods Sold
	,IVSLSIDX = AIMIVSLSIDX.ACTINDX	--Sales
	,IVSLDSIX = AIMIVSLDSIX.ACTINDX	--Markdowns
	,IVSLRNIX = AIMIVSLRNIX.ACTINDX	--Sales Returns
	,IVINUSIX = AIMIVINUSIX.ACTINDX	--In Use
	,IVINSVIX = AIMIVINSVIX.ACTINDX	--In Service
	,IVDMGIDX = AIMIVDMGIDX.ACTINDX	--Damaged
	,IVVARIDX = AIMIVVARIDX.ACTINDX	--Variance
	,PURPVIDX = AIMPURPVIDX.ACTINDX	--Purchase Price Variance
	,UPPVIDX = AIMUPPVIDX.ACTINDX	--Unrealised Purchase Price Variance
	,IVRETIDX = AIMIVRETIDX.ACTINDX	--Inventory Returns
	,ASMVRIDX = AIMASMVRIDX.ACTINDX 	--Assembly Variance
FROM
	IV00101 IV --Item Master (IV00101)
INNER JOIN
	#StockCodes As Stock ON Stock.ITEMNMBR = IV.ITEMNMBR
INNER JOIN --Assembly Variance
	GL00105 As AIMASMVRIDX --Item Currency Master (IV00105)
		ON AIMASMVRIDX.ACTNUMST = Stock.ASMVRIDX
INNER JOIN --Cost of Goods Sold
	GL00105 As AIMIVCOGSIX ON AIMIVCOGSIX.ACTNUMST = Stock.IVCOGSIX
INNER JOIN --Damaged
	GL00105 As AIMIVDMGIDX ON AIMIVDMGIDX.ACTNUMST = Stock.IVDMGIDX
INNER JOIN --In Service
	GL00105 As AIMIVINSVIX ON AIMIVINSVIX.ACTNUMST = Stock.IVINSVIX
INNER JOIN --In Use
	GL00105 As AIMIVINUSIX ON AIMIVINUSIX.ACTNUMST = Stock.IVINUSIX
INNER JOIN --Inventory Returns
	GL00105 As AIMIVRETIDX ON AIMIVRETIDX.ACTNUMST = Stock.IVRETIDX
INNER JOIN --Inventory
	GL00105 As AIMIVIVINIX ON AIMIVIVINIX.ACTNUMST = Stock.IVIVINDX
INNER JOIN --Inventory Offset
	GL00105 As AIMIVIVOFIX ON AIMIVIVOFIX.ACTNUMST = Stock.IVIVOFIX
INNER JOIN --Purchase Price Variance
	GL00105 As AIMPURPVIDX ON AIMPURPVIDX.ACTNUMST = Stock.PURPVIDX
INNER JOIN --Sales
	GL00105 As AIMIVSLSIDX ON AIMIVSLSIDX.ACTNUMST = Stock.IVSLSIDX
INNER JOIN --Markdowns
	GL00105 As AIMIVSLDSIX ON AIMIVSLDSIX.ACTNUMST = Stock.IVSLDSIX
INNER JOIN --Sales Returns
	GL00105 As AIMIVSLRNIX ON AIMIVSLRNIX.ACTNUMST = Stock.IVSLRNIX
INNER JOIN --Unrealised Purchase Price Variance
	GL00105 As AIMUPPVIDX ON AIMUPPVIDX.ACTNUMST = Stock.UPPVIDX
INNER JOIN --Variance
	GL00105 As AIMIVVARIDX ON AIMIVVARIDX.ACTNUMST = Stock.IVVARIDX
GO


DROP TABLE #StockCodes
GO

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

This script updates the location on work status purchase order lines from a CSV file. I vaguely recall this script and after running it we ran the reconcile process against both Purchase Ordering and Inventory Control.

As with any script, before running on production test the script to make sure it works and have a good backup of the database.

/*
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 #SiteXRef
	(PONUMBER VARCHAR(100)
	,ITEMNMBR VARCHAR(100)
	,LOCNCODE VARCHAR(100)
	)
GO

BULK
	INSERT #SiteXRef
FROM
	'C:\TEMP\StockCodes.csv'
WITH
	(
		FIELDTERMINATOR = ',',
		ROWTERMINATOR = '\n'
	)
GO

UPDATE
	POP
SET
	POP.LOCNCODE = SiteXRef.LOCNCODE
FROM
	POP10110 POP
INNER JOIN
	#SiteXRef As SiteXRef ON SiteXRef.PONUMBER = POP.PONUMBER AND SiteXRef.ITEMNMBR = POP.ITEMNMBR
GO

DROP TABLE #SiteXRef
GO