Skip to main content

Notifications

Dynamics 365 Community / Blogs / Dynamics GP Essentials / Inventory Item/site Summary...

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.

HITB Per Period - Per Site

 
/*-------------------------------------------------------------------------
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

Comments

*This post is locked for comments