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

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