Maximo Open Forum

Our organization uses a CC-AAAAAA-DDDDDD format (Company, Account, DeptID) for GL codes used in Maximo. I am introducing a new set of DeptIDs based on a branch from from one department to another, basically duplicating a set of DeptIDs and change the first two digits. I am looking for a quick way to add to the chart of accounts table for each each combination of the existing set of accounts and new dept ids. Is there a utility that does this, or can it be done in SQL?
#Administration
#Procurement
#Reporting

------------------------------
Steve Jurkowski
City of Winnipeg
------------------------------

Steve, You can download your existing CHARTOFACCOUNTS values for a "template" department, transform in a spreadsheet, and then use MXLOADER to insert the transformed values. You could pull the values directly from the database or go into the front end of Maximo, query the Chart of Accounts application for __-______-123456 (or whataver the code is for the department you want to copy), and create a QBR that pulls all the CHARTOFACCOUNTS fields for your "template" department. Either way, you'll have all the data you need for MXLOADER or a flat file import.  With Microsoft Excel, you can quickly transform the GLCOMP03 field with a text function.  For example, if the downloaded values for GLCOMP03 are in column X and your new department code starts with 98, then you would generate a new value for GLCOMP03 in row 1 as "98"& right(X1,4).  Then, rebuild the value for CHARTOFACCOUNTS.GLACCOUNT by concatenating your three components.  For example, if GLCOMP01, GLCOMP02, and "new" GLCOMP03 are in columns V, W and Y, then you can replace the downloaded value for GLACCOUNT in row 1 with the formula V1&"-"&W1&"-"&Y1.  Copy down your formulas to create all the new account records, and remember to paste special (values) when you copy into your MXLOADER sheet. Remember to add the new department code to the GL components before loading the new accounts.   If you are not an MXLOADER user,  you could use the same approach to create a csv file for a flat file import using the MXAPICOA object.

------------------------------
Nancy Lerner
Brio Consulting
------------------------------

Original Message:
Sent: 03-08-2022 17:27
From: Steve Jurkowski
Subject: GL Distributions - Chart of Accounts - Introducing new cost centres

Our organization uses a CC-AAAAAA-DDDDDD format (Company, Account, DeptID) for GL codes used in Maximo. I am introducing a new set of DeptIDs based on a branch from from one department to another, basically duplicating a set of DeptIDs and change the first two digits. I am looking for a quick way to add to the chart of accounts table for each each combination of the existing set of accounts and new dept ids. Is there a utility that does this, or can it be done in SQL?
#Administration
#Procurement
#Reporting

------------------------------
Steve Jurkowski
City of Winnipeg
------------------------------

Thanks! This was exactly what I was looking for

------------------------------
Steve Jurkowski
City of Winnipeg
------------------------------

Original Message:
Sent: 03-21-2022 18:43
From: Nancy Lerner
Subject: GL Distributions - Chart of Accounts - Introducing new cost centres

Steve, You can download your existing CHARTOFACCOUNTS values for a "template" department, transform in a spreadsheet, and then use MXLOADER to insert the transformed values. You could pull the values directly from the database or go into the front end of Maximo, query the Chart of Accounts application for __-______-123456 (or whataver the code is for the department you want to copy), and create a QBR that pulls all the CHARTOFACCOUNTS fields for your "template" department. Either way, you'll have all the data you need for MXLOADER or a flat file import.  With Microsoft Excel, you can quickly transform the GLCOMP03 field with a text function.  For example, if the downloaded values for GLCOMP03 are in column X and your new department code starts with 98, then you would generate a new value for GLCOMP03 in row 1 as "98"& right(X1,4).  Then, rebuild the value for CHARTOFACCOUNTS.GLACCOUNT by concatenating your three components.  For example, if GLCOMP01, GLCOMP02, and "new" GLCOMP03 are in columns V, W and Y, then you can replace the downloaded value for GLACCOUNT in row 1 with the formula V1&"-"&W1&"-"&Y1.  Copy down your formulas to create all the new account records, and remember to paste special (values) when you copy into your MXLOADER sheet. Remember to add the new department code to the GL components before loading the new accounts.   If you are not an MXLOADER user,  you could use the same approach to create a csv file for a flat file import using the MXAPICOA object.

------------------------------
Nancy Lerner
Brio Consulting

Original Message:
Sent: 03-08-2022 17:27
From: Steve Jurkowski
Subject: GL Distributions - Chart of Accounts - Introducing new cost centres

Our organization uses a CC-AAAAAA-DDDDDD format (Company, Account, DeptID) for GL codes used in Maximo. I am introducing a new set of DeptIDs based on a branch from from one department to another, basically duplicating a set of DeptIDs and change the first two digits. I am looking for a quick way to add to the chart of accounts table for each each combination of the existing set of accounts and new dept ids. Is there a utility that does this, or can it be done in SQL?
#Administration
#Procurement
#Reporting

------------------------------
Steve Jurkowski
City of Winnipeg
------------------------------