Inventory Item/site Summary – HITB report
In previous posts, two reports were provided on Historical IV Trial Balance - Per Period and HITB Per Period (Cumulative) – Inventory Balance on a Prior Date. I have got several requests to consider the cost as a cumulative piece of information on a item-site level.
The report has been designed as a stored procedure to retrieve specific parameters passed by the user (Item number, location ID, month and year), it does retrieve the balances (cost and quantity) as of end of a specific period accordingly.
The report is published on report writer and below is a proposed “design template” for the item-site statement.
AS
SELECT SEE.YEAR ,SEE.MONTH ,CONVERT(DATETIME, CAST(SEE.YEAR AS VARCHAR(4)) + '-'+ CAST(SEE.MONTH AS VARCHAR(2)) + '-' +CAST('01' AS VARCHAR(2)), 120) AS LongDate ,SEE.ItemNumber AS 'Item Number' ,IV.ITEMDESC AS 'Item Description' ,SEE.LocationCode AS 'Location Code' ,ST.LOCNDSCR AS 'Location Description' ,SEE.ThisMonthQTY AS 'This Month Quantity' ,SEE.QTYBalance AS 'Quantity Balance' ,SEE.ThisMonthCost AS 'This Month Cost' ,SEE.CostBalanceFROM ( SELECT A.[YER] AS 'YEAR' ,A.[MOT] AS 'Month' ,A.[ITM] AS 'ItemNumber' ,A.[LOCNCODE] AS 'LocationCode' ,ISNULL(A.[QTY], 0) AS ThisMonthQTY ,SUM(ISNULL(B.[QTY], 0)) AS QTYBalance ,ISNULL(A.[Cost], 0) AS ThisMonthCost ,SUM(ISNULL(B.[COST], 0)) AS CostBalanceFROM ( SELECTE.[ITEMNMBR] AS ITM ,E.[Year1] AS YER ,E.[PeriodID] AS MOT ,ISNULL(F.[QTY], 0) AS QTY ,ISNULL(F.COST, 0) AS Cost ,E.LOCNCODE ,( E.[Year1] * 365 ) +( E.[PeriodID] * 30 ) AS DTFROM( SELECTC.[ITEMNMBR] ,C.LOCNCODE ,D.[Year1] ,D.[PeriodID]FROM( SELECTDISTINCTYear1 ,PeriodID ,DATEADD(mm, 1,CONVERT(DATETIME, '01/'+ CAST(PeriodID AS VARCHAR(2))+ '/'+ CAST(YEar1 AS VARCHAR(4)), 103)) AS DTFROM SY40100 AS AWHERE PeriodID <> 0) AS DINNER JOIN( SELECTB.[ITEMNMBR] ,[LOCNCODE] ,MIN(B.[DOCDATE]) AS DTFROM[SEE30303] AS BGROUP BY B.[ITEMNMBR] ,[LOCNCODE]) AS C ON D.DT > C.DT) AS ELEFT OUTER JOIN( SELECT YEAR([DOCDATE]) AS YER,MONTH([DOCDATE]) AS MOT,[ITEMNMBR] AS ITM,SUM([TRXQTYInBase]) AS QTY,SUM([EXTDCOST]) AS COST,[LOCNCODE]FROM SEE30303GROUP BY YEAR([DOCDATE]),MONTH([DOCDATE]),[ITEMNMBR] ,[LOCNCODE]) AS F ONE.ITEMNMBR = F.ITMAND E.PeriodID = F.MOTAND E.Year1 = F.YERAND E.locncode = f.locncode) AS ALEFT OUTER JOIN( SELECTYEAR([DOCDATE]) AS YER ,MONTH([DOCDATE]) AS MOT ,( YEAR([DOCDATE]) * 365 )+ ( MONTH([DOCDATE]) * 30 ) AS DT,[ITEMNMBR] AS ITM ,SUM([TRXQTYInBase]) AS QTY ,SUM([EXTDCOST]) AS COST ,LOCNCODEFROM SEE30303GROUP BYYEAR([DOCDATE]),MONTH([DOCDATE]),[ITEMNMBR],LOCNCODE) AS B ONA.[ITM] = B.[ITM]AND A.[DT] >= B.[DT]AND A.LOCNCODE = B.LOCNCODEGROUP BY A.[YER] ,A.[MOT] ,A.[ITM] ,ISNULL(A.[LOCNCODE],'') ,ISNULL(A.[QTY], 0),ISNULL(A.[Cost], 0),A.LOCNCODE) AS SEELEFT OUTER JOIN IV00101 AS IVON SEE.ItemNumber = IV.ITEMNMBRLEFT OUTER JOIN IV40700 AS STON SEE.LocationCode = ST.LOCNCODEWHERE SEE.ItemNumber = @ItemNumberAND SEE.LocationCode = @SiteAND CONVERT(DATETIME, CAST(SEE.YEAR AS VARCHAR(4)) + '-'+ CAST(SEE.MONTH AS VARCHAR(2)) + '-' + CAST('01' AS VARCHAR(2)), 120)<= CONVERT(DATETIME, CAST(@Year AS VARCHAR(4))+ '-' + CAST(@Month AS VARCHAR(2)) + '-'+ CAST('01' AS VARCHAR(2)), 120)GO
You may download the report writer template and the associated SQL script from here >>> Download link
Best Regards,
Mahmoud M. AlSaadi
*This post is locked for comments