Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Back to Library

"Zero-ing" your year to date inventory quantities 

Fri March 27, 2020 03:45 PM

Maximo stores historical data for Issue and Return transactions each year (or for any user-defined period) in the inventory table. This is information data only but provides a useful snapshot of usage for a particular item.

The 'zero year to date quantities' action is found in the Inventory application. It is manually initiated by the user either at the beginning or the end of a material management time period. This time period can vary depending upon the business requirement. For example, some customers may use their fiscal year to set this period. Others may choose to zero out their issue quantities based on the calendar year or a subset of a calendar year.

Some customers reported incorrect year to date quantities data because of different reasons. They were not able to perform this action on time or performed on an incomplete set of data. In order to fix this data, this action can also be performed through the backend in the Maximo database by running the SQL statements below:

For example:

To generate or update data for material management year/financial year 1/1/12- 12/31/12 the following update statements can be executed.

update inventory set issueytd =

        coalesce((select  -(sum(matusetrans.quantity)) from matusetrans

                  where matusetrans.itemnum=inventory.itemnum

                    and matusetrans.storeloc=inventory.location

                    and matusetrans.siteid=inventory.siteid

                    and matusetrans.itemsetid=inventory.itemsetid

                    and matusetrans.issuetype in (select value from

                                                  synonymdomain where

                                                  domainid = 'ISSUETYP'

                                                  and maxvalue in

                                                  ('ISSUE', 'RETURN'))

                and transdate BETWEEN '01/01/2012' and '12/31/2012'),0)
update inventory set issue1yrago =

        coalesce((select  -(sum(matusetrans.quantity)) from matusetrans

                  where matusetrans.itemnum= inventory.itemnum

                    and matusetrans.storeloc=inventory.location

                    and matusetrans.siteid=inventory.siteid

                    and matusetrans.itemsetid=inventory.itemsetid

                    and matusetrans.issuetype in (select value from

                                                  synonymdomain where

                                                  domainid = 'ISSUETYP'

                                                  and maxvalue in

                                                  ('ISSUE', 'RETURN'))

                and transdate BETWEEN '01/01/2011' and '12/31/2011'),0)
update inventory set issue2yrago =

        coalesce((select  -(sum(matusetrans.quantity)) from matusetrans

                  where matusetrans.itemnum= inventory.itemnum

                    and matusetrans.storeloc=inventory.location

                    and matusetrans.siteid=inventory.siteid

                    and matusetrans.itemsetid=inventory.itemsetid

                    and matusetrans.issuetype in (select value from

                                                  synonymdomain where

                                                  domainid = 'ISSUETYP'

                                                  and maxvalue in

                                                  ('ISSUE', 'RETURN'))

                and transdate BETWEEN '01/01/2010' and '12/31/2010'),0)
update inventory set issue3yrago =

        coalesce((select  -(sum(matusetrans.quantity)) from matusetrans

                  where matusetrans.itemnum= inventory.itemnum

                    and matusetrans.storeloc=inventory.location

                    and matusetrans.siteid=inventory.siteid

                    and matusetrans.itemsetid=inventory.itemsetid

                    and matusetrans.issuetype in (select value from

                                                  synonymdomain where

                                                  domainid = 'ISSUETYP'

                                                  and maxvalue in

                                                  ('ISSUE', 'RETURN'))

                and transdate BETWEEN '01/01/2009' and '12/31/2010'),0)

 

Comments added

The following additional comments to this article were added by bgbaird:

  • Using between dates like this (at least in SQL Server, will omit the transactions occurring on 12/31/xxxx. I use "between '1/1/2010' and '1/1/2011'"

  • Here is the SQL I use:

    DECLARE @foy DATETIME

    SELECT @foy = '1/1/'+cast(datepart(yy,getdate()) as varchar(4))
    begin tran
    update inventory
    set issueytd = isnull((select -sum(matusetrans.quantity)
    from matusetrans
    where matusetrans.itemnum=inventory.itemnum
    and matusetrans.storeloc=inventory.location
    and matusetrans.siteid=inventory.siteid
    and matusetrans.itemsetid=inventory.itemsetid
    and matusetrans.issuetype in (select value
    from synonymdomain
    where domainid = 'ISSUETYP'
    and maxvalue in ('ISSUE', 'RETURN')
    and transdate > @foy)),0),

    issue1yrago = isnull((select -sum(matusetrans.quantity)
    from matusetrans
    where matusetrans.itemnum=inventory.itemnum
    and matusetrans.storeloc=inventory.location
    and matusetrans.siteid=inventory.siteid
    and matusetrans.itemsetid=inventory.itemsetid
    and matusetrans.issuetype in (select value
    from synonymdomain
    where domainid = 'ISSUETYP'
    and maxvalue in ('ISSUE', 'RETURN')
    and transdate BETWEEN dateadd(yy,-1,@foy) and @foy)),0),

    issue2yrago = isnull((select -sum(matusetrans.quantity)
    from matusetrans
    where matusetrans.itemnum=inventory.itemnum
    and matusetrans.storeloc=inventory.location
    and matusetrans.siteid=inventory.siteid
    and matusetrans.itemsetid=inventory.itemsetid
    and matusetrans.issuetype in (select value
    from synonymdomain
    where domainid = 'ISSUETYP'
    and maxvalue in ('ISSUE', 'RETURN')
    and transdate BETWEEN dateadd(yy,-2,@foy)and dateadd(yy,-1,@foy))),0),

    issue3yrago = isnull((select -sum(matusetrans.quantity)
    from matusetrans
    where matusetrans.itemnum=inventory.itemnum
    and matusetrans.storeloc=inventory.location
    and matusetrans.siteid=inventory.siteid
    and matusetrans.itemsetid=inventory.itemsetid
    and matusetrans.issuetype in (select value
    from synonymdomain
    where domainid = 'ISSUETYP'
    and maxvalue in ('ISSUE', 'RETURN')
    and transdate BETWEEN dateadd(yy,-3,@foy)and dateadd(yy,-2,@foy))),0)

 




#Maximo
#AssetandFacilitiesManagement
#MaximoEAM

Statistics
0 Favorited
17 Views
0 Files
0 Shares
0 Downloads