Entity Diagram for Budget Monitoring

We’ll start the second page by understanding the data structures for Budget Monitoring.

The first four objects/tables are what you see in the Budget Monitoring application:

• BUDGET – Is the main table behind the application. It is the one where you find the supervisor, year and financial period fields. The primary key is BUDGETNUM, ORGID. It is an organization level object and while it has a SITEID attribute this is an optional field, which you might use as a filter for the budget lines.
• BUDGETSTATUS – Is the status history for the budget record.
• BUDGETANALYSISAXISNUM – This table is the one that supports the Focal Point table window. Normally each budget will have just one focal point, but you might combine two or more GL segments, hence why there is a one to many relationship to the budget record. The primary key is BUDGETNUM, SEQUENCE, ORGID.
• BUDGETLINE – This table supports the bottom table window on the Budget Monitoring application. It is the one where there are 66 cost, hour or percentage attributes. The primary key is BUDGETNUM, BUDGETCODE, ORGID.

The next four objects/tables are probably only going to be reviewed by the Maximo Administrator who is setting up your system for you. The first is viewed from the Manage Focal Points action, the other three from the Manage Rules action.

• BUDGETANALYSISAXIS – This table supports the dialog box for the action Manage Focal Points. This is the axis of your budget lines, what would be the rows of a spreadsheet. In out of the box there are 10 of these: LOCATIONS, ASSET, CHARTOFACCOUNT and SEGMENT 1 to SEGMENT 7. New focal points can be created. The primary key is BUDGETANALYSISAXISNUM
• BUDGETRULE – This table supports the dialog box for the action Manage Rules. It may appear empty until you have used the Auto-Configuration button which creates 53 records for your default organization. Each rule determines how to calculate a budget attribute on the budget lines. The primary key is BUDGETRULENUM, ORGID.
• BUDGETRULETRANSDATE – This is the second table window in the Manage Rules dialog box and is linked to a budget rule. It controls how Maximo determines which budget record to associate a transaction with. The attributes are all date fields. Sometimes there are more than one record, and this provides an order by which the fields are evaluated in case a date field was null. The primary key is BUDGETRULENUM, ATTRIBUTENAME, ORGID
• BUDGETRULEASSOC – This is the bottom table window in the Manage Rules dialog box and is linked to a budget rule. It controls which attribute is evaluated for each rule and focal point, it may be in a parent table to the record where the transaction exists, for example WORKORDER or PO. The primary key is BUDGETRULENUM, BUDGETANALYSISAXISNUM, ORGID. This is the table that controls how the columns of a spreadsheet would be calculated for each value of the chosen focal point, the columns would be the cost, hours or percentage fields.

The final table/object is called BUDGETFLAG. You do not see this anywhere in Maximo, but it is populated when you use the Update Budget Lines action or the Cron Task runs. It stores the individual transaction values that make up the summed values you see in the budget line details. Maximo reads this table as the Update Budget Lines action starts, presumably to see whether it needs to evaluate the transaction records again, which it wouldn’t need to do if the Rowstamp has not changed.

Overview of the Budget Rules

The next part of this page will examine each budget rule. We’ll do this in sections which correspond to the columns in the details section of a budget line or the row in the budget details report.

There are 66 cost, hour and percentage fields. The 12 fields in the budget column are either manually entered or are calculated totals. There are 18 fields in the two percentage columns which are calculated based on the entry in other fields. There are also 12 other total fields calculated from other fields. This leaves 24 fields which are actually calculated by looking at the Maximo transaction tables, like the four each involved in work planning (labor, materials, services and tools) and work actuals. Some of these 24 sets of calculations are a little complex as they need to take into account that you may cancel a work order, PR or PO. The 24 fields that are calculated from transaction records are held in an ALN domain called RULE_TYPE. 

The above diagram was provided when Budget Monitoring was first released. This is how I would explain the three sections of estimated, committed and actual that you can see, starting from the top.

• For work orders, estimates are planned in Work Order Tracking and the commitment occurs whenever the work order is at a status other than WAPPR or CAN. Work order actuals occur for labor, tools and storeroom items (issues and returns). To get the full picture of costs for a work order we need also to consider the next bullet.
• A work order can also have direct issue items/materials or services/standard services. These are items for which there is a purchasing process rather than a reservation on a storeroom. For work order direct issue items and services, the commitment occurs at PO approval and the actuals occur when the material or service receipts have reached completed status. Actuals for work order direct issue items and services can also be changed through to invoice approval. For example, you can raise an invoice against a work order for services without referencing a PO.
• Some purchases are not associated with a work order, for example stock replenishments, or direct purchases for a location or asset. The estimate for these start with a PR, the commitment occurs at PO approval and the actuals occur when the material or service receipts have reached completed status but may be amended through to invoice approval.

The other rules are interesting:

• Work Orders, PRs and POs can all be cancelled. The budget rules need to take this into account. You will see later that a work order direct requisition item or service will use eight budget rules to calculate a single attribute. I’m giving you advanced warning that you might just stop reading this page in a few sentences time.
• The program logic has to take into account that work order, PR or PO lines can be deleted, which will affect the estimates. I believe this is another reason for the BUDGETFLAG table.
• Invoice actual costs can differ from that on the PO line.

A few points to bear in mind

Having studied the budget rules on Maximo 7.6.1.1 I have a few points to mention before we get into the details:

• There may be changes in the budget rules in subsequent releases
• I might have made mistakes in my interpretation of the budget conditional expressions which are laid out below.
• A client’s definition of estimate, commitment and actual may be different to what is defined out of the box in Maximo. For example:
  • When does an estimate occur? Maximo considers it as soon as it has been entered, your client might consider an estimate only when the work order has reached a certain status.
  • When does actual labor occur? Maximo considers it as soon as it has been entered, your client might consider an actual only when it has been approved. 
• A significant amount of testing is needed in order to verify that the estimates, commitments and actuals are what you and your client have agreed and what they expect. The more focal points a client uses, the more testing will be needed. If budgets are made at financial period level, then I would expect to do more testing than if it was by year. In the testing I performed I noticed that actual labor hours was not considering whether it was approved, or not, and did not consider premium pay hours. Also, there seemed to be an issue with the estimate of direct issue materials and services reducing when a PO was approved. 
• As with all financial records expect to be more rigorous in the amount of testing that is needed especially if you are configuring away from out of the box Maximo. These tests should include anything which might be used just once in a while. For example:
  • Purchase orders in foreign currency
  • Purchase orders being revised
  • Purchase orders with distribute costs against different GLs
  • Receipts being voided
  • Return of receipted items
  • Invoices with distribute costs against different GLs
  • Invoices with allocation (prorate) of standard services, e.g. freight, over other invoice lines
  • Credit notes and debit note handling
  • Reversing an invoice
  • Consignment items and invoices
  • Invoices for services with no purchase orders
  • Invoices for rental/lease contracts
  • Budgeting at site level, but centralised purchasing and invoicing
  • If focal point is LocationA physical place where assets exist and where work can be performed. More, Asset or GL based then costs not involving a work order. 
    • Issues and returns direct to an asset
    • POs, Receiving and Invoicing
    • etc, etc.

An interpretation of the 24 Budget Rules

I am going to follow an order that looks at estimates first, then commitments and lastly actuals. Within each of these I’ll tackle labor, materials, service, tools and then labor hours in that order. I’ll start by trying to explain in words how each of the 24 fields are calculated, which is valid only for Maximo 7.6.1.1, and please bear in mind that I have put into words what I read as SQL, and so I could have made mistakes.

ESTINTLABCOST – Estimated internal labor cost

This is the sum of work plan labor costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders without a vendor code and then subtracts the line cost for cancelled work orders.

ESTOUTLABCOST – Estimated external labor cost

This is the sum of work plan labor costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders with a vendor code and then subtracts the line cost for cancelled work orders.

ESTDIRMATCOST – Estimated material cost

This is the sum of work plan material costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost where it is marked as direct requisition and there is no PR, this will be for all work orders. It then subtracts the line cost for direct requisition lines which are on cancelled work orders whether there is a PR (or not).

It adds to this sum the total PR line cost in the organization’s base currency using the asset, location or GL Account on the PR line. The financial period is determined from the PR’s Required Date or Requested Date. The PR lines totals are not those with an associated PO or destined for a storeroom and it excludes lines with a line type of Service or Standard Service. It then subtracts from this the amount associated with cancelled PRs.

It adds to this sum the total PO line cost in the organization’s base currency using the asset, location or GL Account on the PO line. The financial period is determined from the PO’s Required Date or Order Date. The PO lines totals are not those destined for a storeroom and it excludes lines with a line type of Service or Standard Service. It then subtracts from this the amount associated with cancelled POs.

Finally, it takes into account the POs with an associated PR. It subtracts from the sum the total PR line cost in the organization’s base currency for all PR lines excluding lines with a line type of Service or Standard Service that are on a PO which are at a synonym status of waiting to be approved or approved and adds back the amount associated with cancelled PO’s.

This rather complex formula involving 8 budget rules might be summarised as adding work plan material, PR and PO lines without double counting the same records or counting work orders, PRs or POs that have been cancelled. Phew!

ESTSTOMATCOST – Estimated stored material cost

This is the sum of work plan material costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost where it is not marked as direct requisition and there is no PR, this will be for all work orders. It then subtracts the line cost for records where it is not marked as direct requisition and there is no PR, and which are on cancelled work orders.

ESTSERVCOST – Estimated service cost

This is the sum of work plan service costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost where there is no PR, this will be for all work orders. It then subtracts the line cost which are on cancelled work orders whether there is a PR (or not).

It adds to this sum the total PR line cost in the organization’s base currency using the asset, location or GL Account on the PR line. The financial period is determined from the PR’s Required Date or Requested Date. The PR lines totals are not those with an associated PO or destined for a storeroom and only includes lines with a line type of Service or Standard Service. It then subtracts from this the amount associated with cancelled PRs.

It adds to this sum the total PO line cost in the organization’s base currency using the asset, location or GL Account on the PO line. The financial period is determined from the PO’s Required Date or Order Date. The PO lines totals are not those destined for a storeroom and only includes lines with a line type of Service or Standard Service. It then subtracts from this the amount associated with cancelled POs. 

Finally, it takes into account the POs with an associated PR. It subtracts from the sum the total PR line cost in the organization’s base currency for all PR lines with a line type of Service or Standard Service that are on a PO which are at a synonym status of waiting to be approved or approved and adds back the amount associated with cancelled PO’s. 

This rather complex formula involving 8 budget rules might be summarised as adding work plan service, PR and PO lines without double counting the same records or counting work orders, PRs or POs that have been cancelled. Phew!

ESTTOOLCOST – Estimated tool cost

This is the sum of work plan tool costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders and then subtracts the line cost for cancelled work orders.

ESTINTLABHRS – Estimated internal labor hours

This is the sum of work plan labor hours for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the total labor hours for all work orders without a vendor code and then subtracts the total labor hours for cancelled work orders. Note. Total Labor Hours is a hidden field on Work Order Tracking application in the Planned Labor tab.

ESTOUTLABHRS – Estimated external labor hours

This is the sum of work plan labor hours for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the total labor hours for all work orders with a vendor code and then subtracts the total labor hours for cancelled work orders. Note. Total Labor Hours is a hidden field on Work Order Tracking application in the Planned Labor tab.

APPINTLABCOST – Committed internal labor cost

This is the sum of work plan labor costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders except those at a synonym status of WAPPR without a vendor code and then subtracts the line cost for cancelled work orders.

APPOUTLABCOST – Committed external labor cost

This is the sum of work plan labor costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders except those at a synonym status of WAPPR with a vendor code and then subtracts the line cost for cancelled work orders.

APPDIRMATCOST – Committed direct material cost

This is the total PO line cost in the organization’ base currency using the asset, location or GL Account on the PO line. The financial period is determined from the PO’s Required Date or Order Date. The PO lines totals are not those destined for a storeroom and excludes lines with a line type of Service or Standard Service, the POs included are those at a synonym status of approved, on hold, pending revision, in progress or closed – it excludes waiting to be approved status. It then subtracts from this the amount associated with approved direct requisition lines that are on cancelled or revised POs. 

APPSTOMATCOST – Committed stored material cost

This is the sum of work plan material costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders except those at a synonym status of WAPPR and then subtracts the line cost for cancelled work orders.

APPSERVCOST – Committed service cost

This is the total PO line cost in the organization’ base currency using the asset, location or GL Account on the PO line. The financial period is determined from the PO’s Required Date or Order Date. The PO lines totals are not those destined for a storeroom and only includes lines with a line type of Service or Standard Service, the POs included are those at a synonym status of approved, on hold, pending revision, in progress or closed – it excludes waiting to be approved status. It then subtracts from this the amount associated with approved services that are on cancelled or revised POs. 

APPTOOLCOST – Committed tool cost

This is the sum of work plan tool costs for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the line cost for all work orders except those at a synonym status of WAPPR and then subtracts the line cost for cancelled work orders.

APPINTLABHRS – Approved internal labor hours

This is the sum of work plan labor hours for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the total labor hours for all work orders except those at a synonym status of WAPPR without a vendor code and then subtracts the total labor hours for cancelled work orders. Note. Total Labor Hours is a hidden field on Work Order Tracking application in the Planned Labor tab.

APPOUTLABHRS – Approved external labor hours

This is the sum of work plan labor hours for the asset, location or GL Account on the work order. The financial period is determined from the reported date, target start date or scheduled start date. It first totals the total labor hours for all work orders except those at a synonym status of WAPPR with a vendor code and then subtracts the total labor hours for cancelled work orders. Note. Total Labor Hours is a hidden field on Work Order Tracking application in the Planned Labor tab.

ACTINTLABCOST – Actual internal labor cost

This is the sum of actual labor costs for the asset, location or GL Debit Account on the actual labor transaction record. The financial period is determined from the entered date. It totals the line cost excluding those records marked as for external labor. It includes transactions linked to work orders that are not at a status of cancelled. It excludes ticket time, and other time records not linked to a work order and assumes the time will be approved. 

ACTOUTLABCOST – Actual external labor cost

This is the sum of actual labor costs for the asset, location or GL Debit Account on the actual labor transaction record. The financial period is determined from the entered date. It totals the line cost for those records marked as for external labor. It includes transactions linked to work orders that are not at a status of cancelled. It excludes ticket time, and other time records not linked to a work order and assumes the time will be approved.

ACTDIRMATCOST – Actual direct material cost

This is the sum of line costs for the PO line asset, or the location or GL Debit Account on a direct issue material receipt transaction record. The financial period is determined from the actual date. The receipt is at completed status; it is not a receipt into a storeroom.

The direct issue receipts are added to actual direct requisition materials. This is the sum of line costs for the asset, location or GL Debit Account on a material use transaction record where the PO number is null and there is no storeroom. The financial period is determined from the actual date.

ACTSTOMATCOST – Actual stored material cost

This is the sum of line costs for the asset, location or GL Debit Account on a material use transaction record that has been issued or returned from a storeroom. The financial period is determined from the actual date. Note. A return has a negative line cost.

ACTSERVCOST – Actual service cost

This is the sum of line costs for the asset, location or GL Debit Account on a service receipt record that has been marked as completed. The financial period is determined from the transaction date.

ACTTOOLCOST – Actual tool cost

This is the sum of actual tool costs for the asset, location or GL Debit Account on the actual tool transaction record. The financial period is determined from the entered date. It includes transactions linked to work orders that are not at status of cancelled.

ACTINTLABHRS – Actual internal labor hours

This is the sum of actual labor hours for the asset, location or GL Debit Account on the actual labor transaction record. The financial period is determined from the entered date. It totals the regular hours only excluding those records marked as for external labor. It includes transactions linked to work orders that are not at status of cancelled. It excludes ticket time, and other time records not linked to a work order and assumes the time will be approved. It does not include premium pay hours.

ACTOUTLABHRS – Actual external labor hours

This is the sum of actual labor costs for the asset, location or GL Debit Account on the actual labor transaction record. The financial period is determined from the entered date. It totals the regular hours only for those records marked as for external labor. It includes transactions linked to work orders that are not at status of cancelled. It excludes ticket time, and other time records not linked to a work order and assumes the time will be approved. It does not include premium pay hours. 

Details of the 24 Budget Rules

Estimated Costs

Committed Costs

Actual Costs

Estimated Labor Hours

Committed Labor Hours

Actual Labor Hours 

Budget Rule Conditions

The budget rules use a condition which will be found in the Conditional Expression Manager application