32,738
Like
Share
ReportSeveral Customers are encountered with the inquiry of having a detailed report for Inventory Balances per Item per Period as shown below;
 |
| Historical Inventory Trial Balance per Period |
The script below retrieves the data from Historical Inventory Trial Balance Tables (SEE30303), to provide both quantity and cost balances per item.
--- Tables Included:
- SY40100: Period Setup
- SEE30303: Historical Inventory Trial Balance
SELECT Z.Year ,
Z.Period ,
Z.Item ,
Z.[Quantity] AS 'Quantity Balance',
Z.[Cost] AS 'Cost Balance'
FROM ( SELECT X.[Year] ,
X.[Period] ,
X.[Date] ,
X.[Item] ,
X.[Quantity] ,
X.[Cost]
FROM ( SELECT E.[ITEMNMBR] AS Item ,
E.[Year1] AS Year ,
E.[PeriodID] AS Period ,
ISNULL(F.[Quantity], 0) AS Quantity ,
ISNULL(F.[Cost], 0) AS Cost ,
( E.[Year1] * 365 ) + ( E.[PeriodID] * 30 ) AS Date
FROM ( SELECT Z.[ITEMNMBR] ,
D.[Year1] ,
D.[PeriodID]
FROM ( SELECT
DISTINCT Year1 ,
PeriodID ,
DATEADD(mm, 1,
CONVERT(DATETIME, '01/'
+ CAST(PeriodID AS VARCHAR(2))
+ '/'
+ CAST(YEar1 AS VARCHAR(4)), 103)) AS Date
FROM SY40100 AS X
WHERE PeriodID <> 0
) AS D
INNER JOIN ( SELECT
Y.[ITEMNMBR] ,
MIN(Y.[DOCDATE]) AS Date
FROM [SEE30303] AS Y
GROUP BY Y.[ITEMNMBR]
) AS Z ON D.Date > Z.Date
) AS E
LEFT OUTER JOIN ( SELECT YEAR([DOCDATE]) AS Year ,
MONTH([DOCDATE]) AS Period ,
[ITEMNMBR] AS Item ,
SUM([TRXQtyInBase]) AS Quantity ,
SUM([EXTDCost]) AS Cost
FROM SEE30303
GROUP BY YEAR([DOCDATE]) ,
MONTH([DOCDATE]) ,
[ITEMNMBR]
) AS F ON E.ITEMNMBR = F.Item
AND E.PeriodID = F.Period
AND E.Year1 = F.Year
) AS X
GROUP BY X.[Year] ,
X.[Period] ,
X.[Date] ,
X.[Item] ,
X.[Quantity] ,
X.[Cost]
) AS Z
Helping Note !
The script could be further enhanced in order to include further "drill down" details such as Inventory Account, Location and other inventorial parameters.
Best Regards,
Mahmoud M. AlSaadi
*This post is locked for comments