We had a requirement at hand to
change the capitalized status to non-capitalized of a list of items in bulk. When
we checked how many items were available at that time based on the given
criteria, we found more than a thousand. It was not possible to go to Item
Master application, browse through each and every item and use the action
Change Capitalized Status. We were left with no option but to take the route of
bulk update of these items through SQL. We need to consider some key points in
doing so.
What is a capitalized item? An
item is capitalized when it is recorded as an asset, rather than an expense.
What does it mean in Maximo? When the capitalized check box is checked for an
item, then the cost of the part/asset object is not charged to the work order
(or other associated charge account) when it is issued from the Storeroom.
Rather, it is placed on the Organization books as a depreciable asset and would
be expensed on a monthly basis over time. If the capitalized check box is
unchecked for an item, then the cost of the rotating part/asset object will be
charged to the appropriate work order and associated GL accounts upon use.
In
Maximo, you go to Item Master application. Select the item which you want to
capitalize. You select the action, Change Capitalized status. In the Change Capitalized Status window, you need to enter
an account code that you plan to use as the capital GL account. A default
capital GL account is not provided. When the item is changed to capitalized
status, the storeroom status is changed to capitalized, and the average, last,
and standard costs are set to zero in all storerooms containing the item.
For each inventory record, i.e.
for all storerooms containing the item, the account code manually entered in
the Capital GL Account field, gets associated. In the CONTROLACC column of the
INVCOST table, the inventory control account code is replaced with the capital
GL account code for all storerooms containing the item. In effect, this
transfers the charge or value associated with the item from the inventory
control account to the capital GL account. In the INVCOST table, for each row
that corresponds to an item, the SHRINKAGEACC and INVCOSTADJACC columns are
cleared.
For each storeroom that contains the item, a CAPCSTADJ transaction is
written to the INVTRANS table, INVTRANS.GLDEBITACCT defaults to manually
entered capital GL account value and INVTRANS.GLCREDITACCT defaults to
INVCOST.CONTROLACC or LOCATIONS.CONTROLACC, or INVLIFOFIFOCOST.CONTROLACC.
The correct path to be followed
is to first add the item to the storeroom and only after that change the
capitalized status. This is because Maximo 'Change Capitalized Status'
functionality is based on existence of inventory records. Additionally, Inventory
transactions of type CAPCSTADJ are created to record the transactions that
involve costs with changing the capitalized status back and forth.
Coming back to our requirement at
hand. The following SQL queries we had to run as per the sequence below.
1.
Let us assume the criteria to identify the items
is all lot items associated with commodity group=’XYZ’.
UPDATE
MAXIMO.ITEM SET CAPITALIZED=1 WHERE COMMODITYGROUP='XYZ' AND LOTTYPE=LOT AND
CAPITALIZED=0;
2.
Let us assume capital account to be provided for
these items is ‘6170-450-200’.
UPDATE MAXIMO.INVCOST SET AVGCOST=0.0, LASTCOST=0.0,
INVCOSTADJACC = '', SHRINKAGEACC = '', CONTROLACC = '6170-450-200'
WHERE ITEMNUM IN (SELECT DISTINCT ITEMNUM FROM
MAXIMO.ITEM WHERE COMMODITYGROUP='XYZ' AND LOTTYPE=LOT AND CAPITALIZED=1);
3.
Also, every time an item is made capitalized, a
record needs to be added to INVTRANS table for every inventory record with
transtype = CAPCSTADJ. This will be the trickiest query.
INSERT INTO MAXIMO.INVTRANS (INVTRANSID, ITEMNUM,
ITEMSETID, STORELOC, TRANSTYPE, TRANSDATE, ORGID, SITEID, ENTERBY, CONSIGNMENT,
QUANTITY, CURBAL, PHYSCNT, OLDCOST, NEWCOST, LINECOST, MEMO, GLDEBITACCT, GLCREDITACCT) SELECT INVTRANSSEQ.NEXTVAL INVTRANSID, ITEMNUM
ITEMNUM, ITEMSETID ITEMSETID, LOCATION STORECLOC, 'CAPCSTADJ' TRANSTYPE,
SYSDATE TRANSDATE, ORG ORGID, SITE SITEID, 'MAXADMIN' ENTERBY, 0 CONSIGNMENT,
QTY QUANTITY, BAL CURBAL, CNT PHYSCNT, CST OLDCOST, 0.0 NEWCOST, (QTY*CST)
LINECOST,
'Changing item to Capitalized' MEMO, '6170-450-200'
GLDEBITACCT, CONTROLACC GLCREDITACCT FROM (SELECT ITM.ITEMNUM ITEMNUM,
ITM.ITEMSETID ITEMSETID,
LOC.LOCATION, LOC.ORGID ORG, LOC.SITEID SITE,
INV.CURBAL QTY, INV.CURBAL BAL, INV.PHYSCNT CNT, CST.AVGCOST CST,
LOC.CONTROLACC CONTROLACC
FROM MAXIMO.ITEM ITM, MAXIMO.LOCATIONS LOC,
MAXIMO.INVBALANCES INV, MAXIMO.INVCOST CST
WHERE ITM.COMMODITYGROUP='XYZ' AND ITM.LOTTYPE=LOT AND
ITM.CAPITALIZED=1 AND ITM.ITEMNUM=CST.ITEMNUM AND ITM.ITEMSETID=CST.ITEMSETID
AND CST.ITEMNUM=INV.ITEMNUM AND
CST.ITEMSETID=INV.ITEMSETID AND CST.LOCATION=INV.LOCATION AND
CST.SITEID=INV.SITEID
AND INV.LOCATION=LOC.LOCATION AND INV.SITEID=LOC.SITEID);
References:
