Reporting Actual Service Hours via SPD and SPI Prefixes

When trying to reconcile Actual Payroll Hours posted to Service Work Orders, Maxwell Management Suite software offers two Formatted Report Prefixes, SPD (History/Billed Tickets), and SPI (Current/Unbilled Tickets), that give you access to the Actual Payroll Hours spent on Work Orders.

The problem is field (F035) in the SPD/SPI Prefixes is called Actual Quantity/Hours, meaning Actual Hours shares the field with Actual Quantity. Actual Quantity is the quantity of material items used on that Work Order, for example, from an Inventory Material Requisition. So the question is, “How do you separate Actual Hours from Actual Quantity when trying to analyze Payroll hours?” Here are several solutions.

Using the Unit of Measure Field to determine if it is a Payroll transaction

The first method I used to isolate the Hours from the Quantity involved using the Unit of Measure (F023), which assmes all Payroll transactions have a Unit of Measure of “HR” (hours). I created a Formatter Field (F500) and tried to use this definition:

F035;IF F023="HR" THEN X7$="0"

Unfortunately, the program kept reporting a syntax error with that equation. A little detective work found the ‘code parser’ was converting the F023 into an invalid variable “J(-6)” and testing that numeric variable against a character string. The parser didn’t seem to like the “F023”. Here’s what Formatter Field Maintenance reported as the incorrect parsed code:

LET A=J(6);IF J(-6)="HR" THEN X7$="0"

So, remembering back to old days, found that Maxwell still kept a string array U$, where each element contained that particular field value (e.g. U$[23] is the Unit of Measure). So using that array allowed for a successful field definition. Essentially, the definition says, “assign the Actual Quantity/Hours (F035) to our variable, but if Unit of Measure (U$[23]) is NOT “HR” then zero out our variable (Actual Hours).” Note that X7$ is the current field. Here’s the completed definition that worked:

F035;IF U$[23]<>"HR" THEN X7$="0"

Using the Source Code Field to determine if it is a Payroll transaction

The problem with using the Unit of Measure field is that it is not completely accurate. Meaning, a Work Order could also contain subcontract hours that use the Unit of Measure of “HR”. The only other accessible field that signifies a Payroll cost, is the Ticket Actual Cost Source Field (F049). When the Payroll Journal posts the Actual Cost transactions to Service, it uses a “1” representing the Payroll Journal, plus the Journal Date, plus the Report ID, to form the Ticket Actual Cost Source Field. So the solution was to use the first digit of the field to find out if the transaction originated in Payroll. Here’s the definition that worked, and proved more accurate than using the Unit of Measure field:

F035; IF MID(U$[49],1,1)<>"1" THEN X7$="0"

One other note: With Version 6.5.3 of Maxwell Management Suite, the Formatter Field specification for Actual Quantity/Hours (F035) is only defined for a field length of 11, but is stated to be a length of 15 in the Data Dictionary. To make the above examples work, the “SPD 035” and “SPI 035” records in CSCOD and CSCFF need to be corrected to reflect the correct field length.