Day: July 7, 2022

SQL View to Return Accounts Assigned to Inventory Item Classes in Microsoft Dynamics GP

Ian Grieve10 minute read0 comment

Microsoft Dynamics GPI was writing a report pulling data from the Inventory Control module of Microsoft Dynamics GP the other day and I needed to include the accounts assigned to the Item Classes. I’ve written this query before and, having a need for it again, I decided to create it as a separate SQL view in order to make it more easily reusable.

/*
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).
*/
-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_ItemClassAccounts', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_ItemClassAccounts
GO
-- create view
CREATE VIEW uv_AZRCRV_ItemClassAccounts AS
SELECT
	['Item Class Setup'].ITMCLSCD AS 'Item Class Code'
	,['Item Class Setup'].ITMCLSDC AS 'Item Class Description'
	,['Account Index Master - Inventory'].ACTNUMST AS 'Inventory Account'
	, ['Account Index Master - Inventory Offset'].ACTNUMST AS 'Inventory Offset Account'
	, ['Account Index Master - Cost Of Goods Sold'].ACTNUMST AS 'Cost of Goods Sold'
	, ['Account Index Master - Discounts'].ACTNUMST AS 'Discounts'
	, ['Account Index Master - Sales'].ACTNUMST AS 'Sales'
	, ['Account Index Master - Sales Returns'].ACTNUMST AS 'Sales Returns'
	, ['Account Index Master - In Use'].ACTNUMST AS 'in Use'
	, ['Account Index Master - In Service'].ACTNUMST AS 'In Service'
	, ['Account Index Master - Damaged'].ACTNUMST AS 'Damaged'
	, ['Account Index Master - Variance'].ACTNUMST AS 'Varianves'
	, ['Account Index Master - Drop Ship'].ACTNUMST AS 'Drop Ship'
	, ['Account Index Master - Purchase Price Variance'].ACTNUMST AS 'Purchase Price Variance'
	, ['Account Index Master - Unrealised Purchase Price Variance'].ACTNUMST AS 'Unrealised Purchase Price Variance'
	, ['Account Index Master - Inventory Returns'].ACTNUMST AS 'Inventory Returns'
	, ['Account Index Master - Assembly Variance'].ACTNUMST AS 'Assembly Variances'
FROM
	IV40400 AS ['Item Class Setup'] -- Item Class Setup (IV40400)
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory'] -- Account Index Master (GL00105)
		ON
			['Account Index Master - Inventory'].ACTINDX = ['Item Class Setup'].IVIVINDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory Offset']
		ON
			['Account Index Master - Inventory Offset'].ACTINDX = ['Item Class Setup'].IVIVOFIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Cost Of Goods Sold']
		ON
			['Account Index Master - Cost Of Goods Sold'].ACTINDX = ['Item Class Setup'].IVCOGSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Sales']
		ON
			['Account Index Master - Sales'].ACTINDX = ['Item Class Setup'].IVSLSIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Discounts']
		ON
			['Account Index Master - Discounts'].ACTINDX = ['Item Class Setup'].IVSLDSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Sales Returns']
		ON
			['Account Index Master - Sales Returns'].ACTINDX = ['Item Class Setup'].IVSLRNIX
LEFT JOIN
	GL00105 AS ['Account Index Master - In Use']
		ON
			['Account Index Master - In Use'].ACTINDX = ['Item Class Setup'].IVINUSIX
LEFT JOIN
	GL00105 AS ['Account Index Master - In Service']
		ON
			['Account Index Master - In Service'].ACTINDX = ['Item Class Setup'].IVINSVIX
LEFT JOIN
	GL00105 AS ['Account Index Master - Damaged']
		ON
			['Account Index Master - Damaged'].ACTINDX = ['Item Class Setup'].IVDMGIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Variance']
		ON
			['Account Index Master - Variance'].ACTINDX = ['Item Class Setup'].IVVARIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Drop Ship']
		ON
			['Account Index Master - Drop Ship'].ACTINDX = ['Item Class Setup'].DPSHPIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Purchase Price Variance']
		ON
			['Account Index Master - Purchase Price Variance'].ACTINDX = ['Item Class Setup'].PURPVIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Unrealised Purchase Price Variance']
		ON
			['Account Index Master - Unrealised Purchase Price Variance'].ACTINDX = ['Item Class Setup'].UPPVIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Inventory Returns']
		ON
			['Account Index Master - Inventory Returns'].ACTINDX = ['Item Class Setup'].IVRETIDX
LEFT JOIN
	GL00105 AS ['Account Index Master - Assembly Variance']
		ON
			['Account Index Master - Assembly Variance'].ACTINDX = ['Item Class Setup'].ASMVRIDX
GO

GRANT SELECT ON uv_AZRCRV_ItemClassAccounts TO DYNGRP
GO