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.
/*-------------------------------------------------------------------------
Creation Date: 10th of Match, 2015
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to provide a report of cumulative quantity and cost per item, per site.
Revision History:
Revision No. RevisionDate Description
1 10/03/2015 Original Version
------------------------------------------------------------------------- */
CREATE PROCEDURE [dbo].[Get_HITB_perSite]
@ItemNumber NVARCHAR(MAX) ,
@Site NVARCHAR(MAX) ,
@Year INT ,
@Month INT
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.CostBalance
FROM ( 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 CostBalance
FROM ( SELECT
E.[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 DT
FROM
( SELECT
C.[ITEMNMBR] ,
C.LOCNCODE ,
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 DT
FROM SY40100 AS A
WHERE PeriodID <> 0
) AS D
INNER JOIN
( SELECT
B.[ITEMNMBR] ,
[LOCNCODE] ,
MIN(B.[DOCDATE]) AS DT
FROM
[SEE30303] AS B
GROUP BY B.[ITEMNMBR] ,
[LOCNCODE]
) AS C ON D.DT > C.DT
) AS E
LEFT OUTER JOIN
( SELECT YEAR([DOCDATE]) AS YER,
MONTH([DOCDATE]) AS MOT,
[ITEMNMBR] AS ITM,
SUM([TRXQTYInBase]) AS QTY,
SUM([EXTDCOST]) AS COST,
[LOCNCODE]
FROM SEE30303
GROUP BY YEAR([DOCDATE]),
MONTH([DOCDATE]),
[ITEMNMBR] ,
[LOCNCODE]
) AS F ON
E.ITEMNMBR = F.ITM
AND E.PeriodID = F.MOT
AND E.Year1 = F.YER
AND E.locncode = f.locncode
) AS A
LEFT OUTER JOIN
( SELECT
YEAR([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 ,
LOCNCODE
FROM SEE30303
GROUP BY
YEAR([DOCDATE]),
MONTH([DOCDATE]),
[ITEMNMBR],
LOCNCODE
) AS B ON
A.[ITM] = B.[ITM]
AND A.[DT] >= B.[DT]
AND A.LOCNCODE = B.LOCNCODE
GROUP BY A.[YER] ,
A.[MOT] ,
A.[ITM] ,
ISNULL(A.[LOCNCODE],'') ,
ISNULL(A.[QTY], 0),
ISNULL(A.[Cost], 0),
A.LOCNCODE
) AS SEE
LEFT OUTER JOIN IV00101 AS IV
ON SEE.ItemNumber = IV.ITEMNMBR
LEFT OUTER JOIN IV40700 AS ST
ON SEE.LocationCode = ST.LOCNCODE
WHERE SEE.ItemNumber = @ItemNumber
AND SEE.LocationCode = @Site
AND 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
