Day: March 27, 2020

SQL Script to get all accounts set as available in Purchasing lookups

Ian Grieve3 minute read0 comment

Microsoft Dynamics GPFollowing on from the last post, in which I posted a script to get accounts linked to a vendor, this post is a script which returns all accounts which are set as available in lookups in the Purchasing series.

The SQL is straightforward, but I had to work out the binary entries to check for by saving and checking each 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).
*/
SELECT
	['Account Master'].ACTINDX
	,['Account Index Master'].ACTNUMST
	,['Account Master'].ACTDESCR
FROM
	GL00100 AS ['Account Master']
INNER JOIN
	GL00105 AS ['Account Index Master']
		ON
			['Account Index Master'].ACTINDX = ['Account Master'].ACTINDX
WHERE
	DSPLKUPS IN
				(
				0x04000000 -- purchasing
				,0x05000000 -- sales/purchasing
				,0x06000000 -- inventory control/purchasing
				,0x07000000 -- sales/inventory control/purchasing
				,0x0C000000 -- purchasing/payroll
				,0x0D000000 -- sales/purchasing/payroll
				,0x0E000000 -- inventory control/purchasing/payroll
				,0x0F000000 -- sales/inventory control/purchasing/payroll
				,0xFFFFFF7F -- never set
				)
GO