SQL Scripts for Microsoft Dynamics GP: Item Report

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

This script returns a report of items including assigned bins and average and highest selling prices in the last 12 months,

/*
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
	['Item Master'].ITEMNMBR
	,['Item Master'].ITEMDESC
	,['Item Site Quantity'].QTYONHND
	,['Item Site Quantity'].QTYBKORD
	,['Item Site Quantity'].ATYALLOC
	,['Item Site Quantity'].QTYONHND - ['Item Site Quantity'].ATYALLOC AS 'Available Quantity'
	,['Item Site Quantity'].ORDRPNTQTY
	,['Item Site Quantity'].ORDRUPTOLVL
	,['Item Site Quantity'].QTYSOLD
	,['Item Master'].CURRCOST
	,['Item Site Quantity'].QTYONHND * ['Item Master'].CURRCOST AS EXTNDCST
	,['Item Site Quantity'].LOCNCODE
	,'Site' AS 'Record Type'
	,['Item Master'].ITMCLSCD
	,['Item Master'].ITMSHNAM
	,['Item Master'].UOMSCHDL AS 'Unit of measurement'
	,['BOM Master'].MODIFDT AS 'Last changed BOM date'
	,(
		STUFF((
			SELECT
				', ' + RTRIM(BIN)
			FROM
				IV00112 AS ['Item Site Bin Master'] --Item Site Bin Master (IV00112)
			WHERE
				['Item Site Bin Master'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
			AND
				['Item Site Bin Master'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
			AND
				['Item Site Bin Master'].QUANTITY > 0
			ORDER BY
				BIN
			FOR XML PATH('')
			), 1, 2, '')
		) AS 'Bin Location Code(s)'
	,(
		SELECT
			SUM(['Sales Transaction Amounts History'].UNITPRCE) / SUM(['Sales Transaction Amounts History'].QUANTITY)
		FROM
			SOP30300 AS ['Sales Transaction Amounts History'] --Sales Transaction Amounts History (SOP30300)
		INNER JOIN
			SOP30200 AS ['Sales Transaction History'] --Sales Transaction History (SOP30200)
				ON
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
				AND
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
		WHERE
			['Sales Transaction History'].SOPTYPE = 3
		AND
			['Sales Transaction History'].DOCDATE >= DATEADD(month, -13, GETDATE())
		AND
			['Sales Transaction Amounts History'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Sales Transaction Amounts History'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
		AND
			['Sales Transaction History'].VOIDSTTS = 0
		) AS 'Average selling price - last 13 months'
	,(
		SELECT TOP 1
			['Sales Transaction Amounts History'].UNITPRCE
		FROM
			SOP30300 AS ['Sales Transaction Amounts History'] --Sales Transaction Amounts History (SOP30300)
		INNER JOIN
			SOP30200 AS ['Sales Transaction History'] --Sales Transaction History (SOP30200)
				ON
					['Sales Transaction History'].SOPTYPE = ['Sales Transaction Amounts History'].SOPTYPE
				AND
					['Sales Transaction History'].SOPNUMBE = ['Sales Transaction Amounts History'].SOPNUMBE
		WHERE
			['Sales Transaction History'].SOPTYPE = 3
		AND
			['Sales Transaction History'].DOCDATE >= DATEADD(month, -13, GETDATE())
		AND
			['Sales Transaction Amounts History'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Sales Transaction Amounts History'].LOCNCODE = ['Item Site Quantity'].LOCNCODE
		AND
			['Sales Transaction History'].VOIDSTTS = 0
		ORDER BY
			['Sales Transaction Amounts History'].UNITCOST DESC
		) AS 'Highest selling price - last 13 months'
		,['GL Account Index Master'].ACTNUMST
		,['GL Account Master'].ACTDESCR
FROM
	IV00101 AS ['Item Master'] --Item Master (IV00101)
LEFT JOIN
	IV00102 AS ['Item Site Quantity'] --Item Quantity Master (IV00102)
		ON
			['Item Master'].ITEMNMBR = ['Item Site Quantity'].ITEMNMBR
		AND
			['Item Site Quantity'].LOCNCODE <> ''
LEFT JOIN
	GL00105 AS ['GL Account Index Master'] --Account Index Master (GL00105)
		ON
			['Item Master'].IVIVINDX = ['GL Account Index Master'].ACTINDX
LEFT JOIN
	GL00100 AS ['GL Account Master'] --Breakdown Account Master (GL00100)
		ON
			['Item Master'].IVIVINDX = ['GL Account Master'].ACTINDX
LEFT OUTER JOIN
	BM00101 AS ['BOM Master'] --Bill of Materials Header (BM00101)
		ON
			['Item Master'].ITEMNMBR = ['BOM Master'].ITEMNMBR
		AND
			['BOM Master'].Bill_Status = 1
WHERE
	['Item Master'].ITEMTYPE = 1
AND
	['Item Site Quantity'].QTYONHND <> 0