Month: December 2022

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