TMWSuite Specifics: Database Process Hooks

One of the greatest strengths of TMWSuite is flexibility.  The system is highly configurable, not only with numerous settings, but the functionality to implement your own code at certain key events for further customization.


How Does It Work?

Update_Move_PostProcessing is likely the most recognizable custom event by far, as it is the most commonly utilized.  It is called after every save operation within the system at a Movement level, meaning that every single transaction has this executed at every point it is updated throughout the Dispatch process.

Due to the frequency of execution, it’s vitally important that code here be kept extremely lean and fast, or else you risk causing huge performance issues downstream.  However, by placing code here, you can override assignment of fields, create complex business rules for LabelFile assignment, or even drive integrations by caching changed data.

What Can We Do?

Here is a sample of the solutions where we utilized custom code within Update_Most_PostProcessing.

  1. Compute complex Round Trip evaluations (based on successive history and home events) and assign the result to a LabelFile for billing/settlements. Similar approaches were used for Day/Night, Per Diem, and Diversions.
  1. Customize the volume calculations using a certified ASTM D1250 product (per the American Petroleum Institute) for Crude Oil to derive Net Standard Volume for Oil Field.
  1. Call a SQL CLR Integration for PC-Miler, allowing the calculation of precise Tolls per the Load Tendered Route versus the Planned Route, enabling comparison and reporting on Toll Billing Opportunity and Savings, versus actual billed Tolls.
  1. Capture and/or move information onto unused fields that are displayed within the user interface. This can drive additional visible information in Visual Dispatch, Fuel Dispatch, and Operations.  For example, highlighting loads by adding flags to ord_remark2 (which is designable on the Card Planner), or adding color coding to loads when a follow up call was due (by checking last manual check call for a 3PL).


How Does It Work?

User_Defined_Billing is a feature which allows a stored procedure to return the applicability, quantity, and rate for billing charges on a Charge Type basis.  Essentially, you can set a Charge Type to call the procedure whenever the Tariffs engine is deciding what to apply, and the procedure can customize what the charge should be, or if it should apply at all.

What Can We Do?

Although more limited in scope than Update_Move_PostProcessing, User_Defined_Billing has exceptional use to compute highly unusual billing charges.  We have used it for the following solutions, though some of these are no longer necessary as newer versions have expanded the features within the billing engine.

  1. Customize the Volume fields to pull alternate measurements for billing quantity (allowed the billing of Net Volume for Crude instead of Gross).
  1. Perform highly complex time calculations for an hourly billing carrier. This allowed us to fill in the gaps within a shift (start and end of shift) as well as between trips when warranted by business rules, to ensure complete billing of all shift work time.
  1. Nullify charges by returning non-value for quantity, overriding the tariff engine based on custom business rules. This was used to create a charge that applied based on miscellaneous fields in the company profile, and would only apply for certain tariffs matching the text of the miscellaneous field (essentially, and “Operator” tariff for Oil Field by Lease).


How Does It Work?

Unlike the earlier events which are named fairly intuitively for their purpose, Calculated_Revenue is actually an event that customizes the pay calculation in Settlements.  It is essentially the mirror of User_Defined_Billing, but for the Settlements engine.

What Can We Do?

Essentially anything which User_Defined_Billing can be used for on the billing side, Calculated_Revenue can do the same for Settlements.  This enables customizing the application of a Pay Type, the quantity and rate returned, all though custom business logic in a procedure.  Here are some examples of solutions we have applied it for in the past.

  1. Customize the Volume fields to pull alternate measurements for settlements quantity (allowed the pay on Net Volume for Crude instead of Gross).
  1. Computed a complex time per shift for an hourly pay carrier. We filled in gaps at start and end of shift and between trips.Note that for this carrier, paying every minute of the worked time was essential for Department of Labor compliance on hourly pay, thus we also supplemented this calculation with a comparison by day for every employee showing PeopleNet Login/Logout, Web Login/Logout (from Link), Trip Time, eLog Time, and Paid Time to ensure accuracy.
  1. Evaluate specialized rates, like Per Diem, that only applied when working out of their home area, on the first trip of their shift, once per day maximum. These types of rules are difficult to build inside TMWSuite, but can easily be evaluated from various sources in a procedure.
  1. Create pay rates with extremely complex business rules. A carrier had a “Holiday” Pay Rate which occurred on New Years Day, Memorial Day, Fourth of July, Labor Day, and Thanksgiving Day (and another Double Time for Christmas Day).  We were able to compute not only the dynamic day every year for these holidays, but also their special business rules (only hours touching the calendar day, so night shifts would pay part of shift, etc…) through Pay Types using Calculated_Revenue.


How Does It Work?

Although named for the original purpose, this event allows much, much more functionality that originally expected.  This event is called when “Collecting” pay in Settlements for a specific PayHeader.  Thus, the entirety of finalizing pay (after Standing Deductions are applied) can be reviewed and modified, enabling extreme customization of pay rules.

What Can We Do?

Code in this procedure effectively acts as a second expansion of pay customization.  Since it’s called at the final step before pay is closed and locked, you have the entire pay amount finalized before the event, enabling you to enforce business validation or evaluations at the very end of the pay process, well beyond a trip level.  Here are some examples of how we have utilized this event for customization.

  1. Ensure all deductions and pay items expected are released, or hold items that should not be released. Oftentimes Fuel Deductions are the most difficult to properly hold and release for Owner Operator fleets.  We flagged these so any deductions through the period automatically release, and deductions after the period automatically go on hold, ensuring fuel deductions are never missed.
  1. Release time by a precise calendar week basis. A carrier who paid hourly had to pay only the exact calendar window bi-weekly, despite trips on night shift which crossed midnight.  We would split the time paid for these trips, and put the portion after midnight back on hold for the next period.
  1. Compute OverTime pay. This is the original purpose of the hook, and we’ve used it for hourly carriers who had custom overtime rules, enabling us to calculate the overtime based on dynamic rules (the hardest was a weighted average rate, based on the amount paid up to the 40 hour mark, not counting holiday time, chronologically).

As you can see, there is extraordinary depth to the customization possible in TMWSuite.  While much of this is driven through SQL Procedures and T-SQL code, the benefits are truly achieved through a merging with business process understanding to accomplish the intended result.

As one final example, here is a process customization we did for a client recently using the above tools to achieve an integrated result.

Challenge: Highlight Orders within the Card Planner where there is Preventative Maintenance work due soon (color code results in urgency of work), to enable planning to a shop for PM Scheduling and minimizing downtime.

Evaluation: We knew that the AMS Integration was creating Tractor/Trailer Expirations as Key Dates, so we could look inside TMWSuite at a Tractor/Trailer level to find Expirations matching the PM Due coding.  Further, the client was shift based, and had a set of Orders touched every day for that day, so order flags would be updated frequently and within a single day basis.  The client used the Card Planner in Fuel Dispatch.

Solution: We applied Update_Move_PostProcessing to write flags to ord_remark2 and set codes such as “TRC_PM_10DAY” when a given expiration was present within a set window of time.  The Card Planner designer was used to color code fields on the order when these flags were present.

Result: The final result was that orders dropped onto the Card Planner and saved would turn “warning” colors when a PM was due within the window for that vehicle.  This allowed the dispatchers to visually track when equipment needed to be scheduled home, and ensure they had a plan to cover it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s