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.

 

/*-------------------------------------------------------------------------

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