Maximo Open Forum

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!

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.

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!

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. 

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!

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.

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!

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.

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. 

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!

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.

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!

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!