Thanks for the detailed clarification.
I tested using Object structure of ASSET, ASSETTRANS tables and default Asset Processing inbound class.
Kindly let me know if we need to include any other objects.
Original Message:
Sent: 12-26-2023 08:21
From: Steven Shull
Subject: Bulk Update Asset Location -What is the best approach?
The general answer is never do SQL when what you need to achieve is doable through the UI/MIF. Often it will lead to issues down the road in a way that would be impossible through the framework. That often makes it very difficult to identify the issue. For example, your query above ignores the fromparent & toparent fields which should be recorded on all assettrans records (even if they are not modified). This feature is used for certain capabilities such as rolling a meter reading down to child assets that were part of the hierarchy when the meter reading was recorded (since meter readings can be backdated).
There are other updates that may occur depending on the data on the records. For example, if the asset is a calibration asset, there is additional logic that needs to be invoked that you would be bypassing here. There is also validation that you are bypassing as well. If you provide an invalid location for example, the SQL update will allow it, but the MIF would prevent it.
While 500,000 records may sound like a lot, it is definitely manageable with the MIF. You want to make sure you are writing to a continuous JMS queue, not something like application import/export that processes each record one at a time as one massive transaction. But 500,000 asset updates can be processed in a reasonable timeframe without downtime.
------------------------------
Steven Shull
IBM
Original Message:
Sent: 12-25-2023 20:43
From: Sankar Ganesh V S
Subject: Bulk Update Asset Location -What is the best approach?
Hi Prashath,
Thanks for your response!
I tested the Asset Move through Manual action and Data import. (with default Asset Object structrure processing class).
It works in both cases and ASSETTRANS populated with all relevant fields.
I guess, it would be the same for MXLoader also?
Since the data volume is huge (500k), we are planning to do via SQL Script. We have idenntified the ASSETTRANS values as below.
Field in ASSETTRANS | Value |
ASSETNUM | Asset Number |
DATEMOVED | Sys Date |
FROMLOC | Current Location |
TOLOC | New Location |
GLCREDITACCT | GL Account of Current Location |
GLDEBITACCT | GL Account of New Location |
TRANSDATE | Sys Date |
ENTERBY | MAXADMIN |
ASSETTRANSID | (select max(assettransid) from assettrans) |
SITEID | Site ID of Asset |
ORGID | Org ID of Asset |
TOSITEID | Site ID of Asset |
TOORGID | Org ID of Asset |
ASSETID | Asset ID |
TRANSTYPE | MOVED' |
Inset SQL format:
insert into [dbo].[assettrans]
(assetnum,datemoved,fromloc,toloc,glcreditacct,gldebitacct,transdate,enterby,assettransid,siteid,orgid,tositeid,toorgid,assetid,transtype)
values(<Asset Number>,<sysdate>,<Current Location>,<New Location>,<GL Account of Current Location>,<GL Account of New Location>,<System Date>
,'MAXADMIN',(select max(assettransid) from [assettrans]),<SITEID>,<ORGID>,<SITEID>,<ORGID>,<ASSETID>,'MOVED');
I'd like to confirm the above logic, particularly about the GL accounts population in ASSETTRANS.
Kindly check and let me know your suggestions.
Thanks!
------------------------------
Sankar Ganesh V S
DXC Technology
Original Message:
Sent: 12-23-2023 03:26
From: Prashant Sharma
Subject: Bulk Update Asset Location -What is the best approach?
When you are moving Assets from One Parent to Another parent, do you want to keep the history of movement recorded. If yes then best solution is to use Bulk Move/Modify function from UI, but if data is very high then you can try doing it with MXLoader, but it will not capture the move/modify history.
You can also use SQL to move but it will better to do move few and then testing of some UI txns on them to ensure you are not running into some data integrity or business logic validation failure.
------------------------------
Prashant Sharma
Sedin Technologies
Connect with me @ https://www.linkedin.com/in/psharmamaximo/
#IBM Champion 2022
#IBM Champion 2023
Original Message:
Sent: 12-22-2023 01:06
From: Sankar Ganesh V S
Subject: Bulk Update Asset Location -What is the best approach?
Hello all,
We have a requirement to bulk move assets to next parent lelevl location.
There are 100 thousands of assets and it would be a heavy load for data import and can affect queue performance.
Can we do it through SQL script ?
Insert into ASSETTRANS (assetnum,datemoved,fromloc,toloc,glcreditacct,gldebitacct,transdate,enterby,assettransid,siteid,orgid,
tositeid,toorgid,assetid,transtype) values ('','','' etc.);
Update ASSET set location ='xx' where assetnum='yy';
As I can find, the GLCredit ,GL Debit Accounts in ASSETTRANS table are GL Accounts of 'FROMLOC', 'TOLOC' locations.
If there any other validation to be cosidered? OR any potential issue with this approach?
Kindly advise.
Thanks!
#EverythingMaximo
------------------------------
Sankar Ganesh V S
DXC Technology
------------------------------