Prior to the Production Management 3.1.0 version, Purchasing and Sales Pricing accessed Timberline Estimating Items, Phases, Material Classes and Assemblies in real time using the Timberline Data ODBC Driver (or Kauri PEData ODBC Driver for version 4.0 PEData). While this gave users the advantage of instantly being able to see changes in the Sage Timberline database, this approach came with several significant drawbacks.
Speed of access – Accessing large databases and/or accessing the database on busy networks was very slow and error prone. This generated a large number of support calls specifically related to ODBC errors accessing the Timberline Estimating database.
Price Records - Price Records in Purchasing were linked or ‘keyed’ to the Timberline Items in a separate database. Any changes to the Timberline Item number, Phase Code, Material Class or Order Unit ‘broke’ the link and the price record became a disconnected "orphan."
Repair of Price Records - The Purchasing routine to “Prepare for Renumber” and “Update and Validate Price Records” was tricky to use and not always able to retrieve the orphan prices correctly. This was due to the limitations of working with just the current Timberline Database. (i.e. No snapshot of the database as it existed before the renumber)
No cross database reporting - It was not possible to report across the separate databases. Therefore, Purchasing was unable to deliver even the most basic reports on Timberline Estimating Items and their related prices in Purchasing.
Sales Pricing Module - The Sales Pricing module required heavy access to the Assembly Detail and Item tables in Timberline to perform model option pricing for worksheets. This process was extremely slow due to the typically large amount of Models and options to be processed.
Referential Integrity - Timberline’s Estimating Database does not have referential integrity. In other words, it is possible to delete phases that are currently used by items, items that are currently used in Assemblies, Material Classes that are currently used with items, etc. This lack of guaranteed data structure makes interfacing with the Timberline data difficult and error prone, particularly given the wide variety of database structures created by each WMS client.
Timberline Data Structure - The actual structure of the Timberline Item tables (Multiple categories on the same record), the Phase and Assembly tables (Group Phases and Group Assemblies in the same table as the standard Phases and Assemblies), and duplication of Alpha Numeric ‘Multiple Key’ Data fields across tables, also makes SQL queries for reporting on this data very difficult and slow.
Speed of Access –All queries that previously were accessing the Timberline Estimating database through the ODBC driver to retrieve data now use Firebird queries directly on the WMS database. This is a much faster method of retrieving data. Also, the software is now able to use stored procedures to access the replicated data in the WMS database for even faster processing and to ensure referential integrity.
Price Records - Price records in Purchasing are now linked or ‘keyed’ to the replicated Timberline Estimating Items. This allows Cost Maintenance procedures to complete much faster. It also allows Workflow Management to better maintain the pricing links. If the Timberline Estimating Item is renumbered, or if any of the previous key fields are changed in Estimating, the WMS database now has a ‘before’ and ‘after’ snapshot of the Timberline Estimating Database.
Repair of Price Records - The Replication routine uses a new, more accurate algorithm to process the renumbering of the Timberline Estimating Items. The Replication routine also performs a ‘Prepare for Renumber’ process after each replication. This will ensure the Old Phase Item WBS code is correctly set in the Timberline Estimating Database. The Price table will be keyed to the Internal TLPE_Items_ID field, which will solidify the referential integrity, and totally eliminate the need to run “Update and Validate Price Records”.
Cross Database Reporting - Reports on Timberline Estimating Items and their related prices in Purchasing are now possible in the WMS Report Builder. Assembly Headers are also directly linked to Phase records, enabling advanced reporting on Model and Option details.
Sales Pricing Module - The Sales Pricing Module now runs database stored procedures to perform Model/Options pricing for Worksheets. This has increased the speed of this operation at least 100 fold.
Referential Integrity - The Workflow Management Replication process strictly enforces referential integrity in the Replicated tables. Any data in Timberline Estimating that does not conform is not replicated. In other words, Items that do not have a Phase are not replicated. Phases that do not have a Group Phase are not replicated. Items that do not exist but are referenced in Timberline Estimating Assembly details are not replicated in the Assembly Details table.
Timberline Data Structure - The replicated tables use internal unique IDs as primary and foreign keys. Group Phases and Group Assemblies have a separate table from Phases and Assemblies. The Item Record reference to the Phase and Material Class is now an internal ID field, rather than the actual Phase and Material Class text. Timberline Estimating Items with multiple categories (L, M, S, E, O) are split into separate items that are unique by Phase, Item and Estimating Category. All of these data structure improvements allow the detection of renumbered items to be much easier and more accurate. The internal unique ID's also allow lookups and joins to run faster, easier and in a more structured manner.
The WMS Purchasing module now processes strictly with the replicated 'image’ of the Timberline Estimating Database.
If the user is making frequent changes to the Estimating Database, it will be necessary to run the Replication process after each ‘batch’ of changes. We recommend a managed ‘batch’ or ‘release’ approach to making Estimating database changes rather than making individual ‘ad hoc’ changes on the fly. This requires the person or persons responsible for building or maintaining the Estimating Database to keep a 'master' copy of the database ‘off-line’ and to make the changes only to this master database.
Once the changes are tested and approved 'off-line', the Master Estimating Database is then ‘published’ to a shared network folder that can be accessed by the Production Estimators and Purchasing Officers. The Replication routine should be run every time the Estimating Database is ‘published’.
If supplier pricing is required for new items, this can be handled prior to publishing the new Timberline Estimating database by building Estimates using the new Items. These Estimates may be used to solicit supplier bids.
Once the Estimating Database is published and replicated – the prices for the new items can then be directly imported into Purchasing using the normal price maintenance routines.
The Push Supplier Costs to Estimating routine to update estimating items with default supplier prices can still be run to push Purchasing prices back to the Timberline Estimating Database.
This routine now allows the user to choose which Estimating Database to update. When this routine is started, a default database will be shown on screen.
This is the database accessed in the Replication Routine. If the user is following the workflow suggested above, this would be the ‘Published’ Estimating Database.
It is important to note, if the user is following the workflow suggested above and has both a 'Published' and an 'off-line' Master Estimating Database, the 'off-line' Master database will not contain the current Purchasing pricing unless the Push Supplier Costs to Estimating process is run separately on both databases. To run this routine on a different database, simply Click on the Browse folder and choose the path to the alternate database to be updated.