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
