Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Tuesday, March 10, 2015

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

4 comments:

  1. Hi Mahmood,

    I want to let you know how much I appreciate the scripts on HITB Cumulative Balance. They are very helpful. Wish I had found this earlier. Thank you very much

    ReplyDelete
    Replies
    1. It is my pleasure, you are most welcome,

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
    2. Hi Mahmood,

      I applied the above script to a company that has 16 periods in SY40100 and got the message The conversion of a varchar data type to a datetime type resulted in an out-of-range value. How can the script be modified to accommodate this scenario?

      Thanks in advance.

      Delete
  2. BE SMART AND BECOME RICH IN LESS THAN 3DAYS....It all depends on how fast 
    you can be to get the new PROGRAMMED blank ATM card that is capable of
    hacking into any ATM machine,anywhere in the world. I got to know about 
    this BLANK ATM CARD when I was searching for job online about a month 
    ago..It has really changed my life for good and now I can say I'm rich and 
    I can never be poor again. The least money I get in a day with it is about 
    $50,000.(fifty thousand USD) Every now and then I keeping pumping money 
    into my account. Though is illegal,there is no risk of being caught 
    ,because it has been programmed in such a way that it is not traceable,it 
    also has a technique that makes it impossible for the CCTVs to detect 
    you..For details on how to get yours today, email the hackers on : (
    atmmachinehackers1@gmail.com ). Tell your 
    loved once too, and start to live large. That's the simple testimony of how 
    my life changed for good...Love you all ...the email address again is ;
    atmmachinehackers1@gmail.com

    ReplyDelete