Closing the work order loop with GIS

Closing the work order loop with GIS

Walter J. Bird
Niagara Mohawk Power Corporation
300 Erie Boulevard West
Syracuse, NY 13202

Overview
At Niagara Mohawk, as at any public utility, the construction order process is very important to the economic viability of the organization. Since rates are based upon installed capital plant, the sooner the material can be capitalized, the sooner the company can recoup the expense. When we first began investigating Geographic Information Systems (GIS), we envisioned a system that would simplify and speed up the entire construction order process. While we are not completely where we would like to be, we have made significant improvements by utilizing our GIS, and we have concrete plans and goals which will further improve the process. We have encountered obstacles along the way, both technical and procedural. We have worked through these obstacles and are well on our way to having an integrated, fully functional work order process. There are five components to our work order process; order initiation, construction design and estimating, construction, order tracking, and order completion. Gis is involved with all but the construction phase. There are a number of computer systems, both mainframe and client-server based, supporting the various phases of the process. To meet our work order integration goal, we had to interact with these systems in both real-time and batch modes.

Order Initiation
The first step in the order process is identi~ing the need for construction. This need is identified either internally or externally. External orders are those requested by customers or government entities. Internal orders are requested by someone in the company either to support load growth or to replace equipment nearing the end of its useful life. Before GIS was implemented, all new construction orders, whether internal or external, were initiated in our Work Order Tracking and Scheduling System (WOTSS). External orders were entered in WOTSS by a customer service representative as the customer described the work they would like done. Internal orders were entered in WOTSS by the individual planner or by the planning supervisor in each planning department.

WOTSS is a mainframe based system, and when beginning an internal order, the planner was first required to log into the mainframe system to initiate the order in WOTSS, and then log into GIS in order to begin the design process. This caused a problem since both systems use the same data. For these internal orders, the goal was to perform the entire initiation process within GIS and eliminate the need for the planner to use both systems. This goal turned out to be more involved than first anticipated, since there are several steps in the work order process where information is entered in WOTSS, and also because WOTS S interacts with several other mainframe and client-server systems. Performing the initiation function within GIS required that GIS had to duplicate the interactions with these systems.

Our first solution to the connectivity problem was to use an Alien Co-Processor (ACP) written in Smallworld Magik code. This ACP enabled us to use a C++ executable to attach to the mainframe using a Sybase gateway. Once attached to the mainframe, we were able to execute SQL calls against a SYBASE database that in turn accessed the DB2 databases. We also use an ACP to invoke stored procedures to access the order number information created by the client-server based Corporate Project Accounting System (CPAS).

All projects at Niagara Mohawk are tracked using a six-digit work order number. This order number is assigned in CPAS. We needed to access this order number in a real-time mode, and the ACP made this possible. However, it can be slow, and because of the large number of transfers and bridges used, it is not recommended for large amounts of data or large numbers of transactions. We are in the process of investigating the use of ODBC connectivity for our real-time connections. ODBC reduces the number of data transfers between platforms and should simplifi the real-time transfer of data.

WOTSS is our order tracking and scheduling system, so whether an order is external or internal, the order information must end up residing in WOTSS. This requirement meant that for the internal orders being initiated in GIS, we had to pass some of the order information back to WOTSS. This is again done with an ACP. Once the orders are contained in WOTSS, there is no difference in how they are tracked.

Design and Estimating
Afler the order information has been entered, the actual design phase begins. At Niagara Mohawk, we recognized early on the power and flexibility we could gain by using a graphical interface for designing construction orders. Prior to GIS, the design and estimating function was performed using an old, but very efficient mainframe system, the Construction Management System (CMS). CMS enabled the planner to specify all of the material and labor required for each construction order, but did not allow the planer to actually “see” the order he had just designed. Using our Smallworld GIS, the planners see a visual representation of the work that is being done. By using a variety of colors and shapes, the planner and construction crew can easily determine the facilities and type of work required at each location.

After the design phase is completed, the next step is to develop a cost estimate for completing the construction project. Since the facilities already exist in the GIS database, the challenge is to derive the materials and labor from those objects. In order to do this, we developed an Automated Construction Estimating System (ACES). ACES is based on a concept in use at Niagara Mohawk for 15 years called “design points”. A design point is a physical location where

one or more “units of property” have been installed or removed. A unit of property is any facility that must be tracked individually within our property records database. Examples of units of property are poles, wires, and transformers. If there is more than one unit of property object at a specific location, for example a transformer mounted on a pole, there will still be just one design point at that location.

At each design point there maybe more than one task. A task is assigned to every unit of work to be performed. For example, in our above case of a transformer being mounted on a pole, there will be a task for installing the pole and another task for installing the transformer. If there is conductor being installed on that pole, there would also be a task for installing the pole hardware required for the installation of that conductor.

Now, to complicate things further, each task may contain either a compatible unit or an assembly unit. A compatible unit is a self-contained module describing all of the materials and labor required to perform a task. For example, a pole compatible unit contains just the pole for materials, and an estimated time for the operation being performed with that pole, whether it is installing or removing. A compatible unit for a crossarm would contain the crossarm itself, and all the miscellaneous hardware used to install that crossarm to the pole, as well as the labor required to install or remove it.

An assembly unit is made up of two or more compatible units. They are useful when the planner knows that a commonly used group of compatible units is required at a design point. Instead of entering the compatible units individually, the planer can specify one assembly unit that contains all of the desired compatible units. An example would be a riser assembly unit which contains compatible units for the riser assembly, and the connections required to attach the underground cable to the overhead conductor.

Compatible units have been in use at Niagara Mohawk for over 15 years. Because our current set of compatible units is still being used by the mainframe estimating system, CMS, we did not want to make any changes to the compatible units we already had. In order to select a compatible unit for each facility in GIS, we developed a matrix of fields on the GIS objects with related information for each compatible unit. A good example is how we select pole compatible units. We determined that the selection criteria for poles were height, material, and class. When we look for a pole compatible unit, we just look for one that has the same height, material, and class as the object on the GIS map. If we installed a forty-foot, class 5 wood pole, the selection criteria narrowed down the choices to our PW405 compatible unit. The operation for that unit is determined by the status of the pole. In this case, “Install” would relate to an “IN’ operation for the compatible unit. In the case where the selection criteria could not pick just one compatible unit, we made one of the units the default, and gave the planner the ability to choose a different one if needed.

Once the compatible units and assembly units are determined, compatible unit details are added to the mix. The details contain the specific information related to the compatible units. There is one compatible unit detail object for each compatible unit. The detail object contains the total cost of the materials, the total labor hours required to perform the requested operation on that compatible unit, all related indirect labor, and the accounting information used to allocate costs to corporate accounts.

Associated with each compatible unit detail is a record containing the material information for that compatible unit. These are compatible unit material records, and there is one material record for each unique material contained in a compatible unit. Each record contains the quantity of that item required, a unique symbol number, which is the key used in the material data base for that item, a description of the item, and the cost for each item.

After all of the facilities, design points, tasks, compatible or assembly units, compatible unit details, and material objects have been created, the estimate for the order is derived. As mentioned, each compatible unit contains a list of materials and labor for completing a task. At estimate time, all of these materials and labor are added together to end up with an estimate for the order. Standard labor and material overhead percentages are then added to the initial estimate to arrive at a total dollar and time estimate for the order. The planner can then modify the design if necessary until the desired results are obtained, and then the materials and labor figures are passed onto the systems which track those components

Most of the information used for developing the estimate, such as the compatible and assembly units, is self-contained within the GIS. However, there are two pieces of information which change on a regular basis. These are the material costs, and the labor rates. The labor rates change according to the bargaining unit contract, and the material costs are updated monthly. These costs are stored on the mainframe in hierarchical IMS databases. We are currently using a manual process to update this information in the GIS. We determined that given the amount of data we needed to retrieve, our ACP process would be too time-consuming. Our goal is to use ODBC to access this data in a real-time mode.

Order Tracking
Now that the estimate for the order is complete, the emphasis switches to tracking the order. There are several systems that are used to track the order. Our scheduling system, WOTSS, tracks the stage of completion of the order. The Material Information Management System (MIMS) tracks the materials actually used on the order. And the Work Management System (WMS) tracks the time spent working on the order.

Since WOTSS tracks the stage of completion of the order, there are several checkpoints in the process within GIS when information must be sent to WOTSS. For internal orders initiated in GIS, we notify WOTSS when the order has been started. For all orders we must notify WOTSS when the planner has completed the design of the order. We must also notify WOTSS when the order has been turned over to the construction department to begin work, and then again when the construction has been completed. It is not imperative that these notifications be done in a real-time mode, so we simply write transactions to our GIS server and then use File Transfer Protocol (FTP) to retrieve them into a mainframe file where they can be used to update the WOTSS DB2 databases. Since we first developed our procedures using FTP, a new IBM product called MQ-Series has become available. MQ-Series allows systems to transfer data between platforms and can also be used to initiate jobs at those platforms. We may switch to MQ-Series for these transfers after we evaluate the product.

An important part of our work order process is keeping track of the amount of time it takes to complete the order. This is accomplished using the Work Management System (wMS). WMS tracks the type of work being done for the entire order, and also for each type of activity involved in the order. It does this using something called a “work unit”. These work units

represent a specific activity that can be identified from data contained within the design, and they usually are related to each task found in the ACES design estimate. For example, if the order is for a 5-pole line extension, there would be five design points with the installation of one pole at each. Each installation of a pole is assigned an “1P” work unit. The “1P” designates “Install Pole”. When a crew fills out their time sheet for the day, they indicate the order number for the work they completed, and also the number of “work units” completed, if any. If they spent two hours installing one pole, they would indicate that on the time sheet. This data is used to compare performance among crews, and between geographical areas. If for example it takes much longer to install a pole in one region than in another, the operations department can look at factors influencing the discrepancy and take corrective action if necessary.

In addition to the type of work completed and the time to complete the work, WMS stores accounting information for the order. The accounts are built from the ACES estimate data. As time is charged to the order, the accounts are used to assign the labor dollars to the correct corporate account. This data is then used to compare costs of construction between regions and time periods.

ACES supplies all of the data for these work units and accounts, and also all of the labor estimates used to create the WMS database. We did not have a requirement to update the WMS database in a real-time mode, so we write transactions to a file on the server when the design phase of the order is completed. Every thirty minutes these transactions are retrieved via FTP and processed into WMS.

At the same time that WMS projects are created, material information is sent to the Material Information Management System (MIMS). MIMS keeps track of each symbol number that is used for the order. The same six-digit order number is used by MIMS to keep track of these materials as they are issued from the storeroom. As you will see, this material information is used later in the closing process.

Order Closing
When the construction department has completed the order, and all of the time and materials have processed through WMS and MIMS, it is time for the closing process. The closing process uses information from all of these systems in order to place the costs for that order into the company database so that it can be included in the cost of our facilities. The rates we can charge are based in part on the cost of these facilities so it is important to complete the closing process as soon as possible after the work is completed.

The system that assigns the order number in the beginning of this whole process, CPAS, is the same system that controls the completion process. The process begins when GIS, WOTSS, and WMS all mark the order as being completed.

In the GIS, the order is marked as being completed when the status of the order is changed to “Complete in GIS”. This status signifies that the construction is completed and the GIS map has been updated to ensure it exactly matches the facilities actually used in the completion of the order. Sometime it is necessary for the construction crew to build a job slightly different than the planer designed. These changes must be made to the order in GIS before it is marked as complete. After the map is updated to reflect any changes made, and the status is changed to “Complete in GIS’, transactions are built and sent to CPAS. These transactions inform CPAS of the facilities used on the order, and the location of those facilities. These transactions are created as the order is moved into our “Top” partition, and then they are processed during the night when GIS is unavailable to the users.

CPAS then compares these transactions, which represent the actual facilities installed, with the materials which were issued for the order out of the storeroom by MIMS. If there is a large difference between the two figures, a manual investigation is done to determine the cause. The determining factor in WOTSS for order completion is usually when the construction has been energized, or when the power is actually flowing through the lines. For WMS, the determining factor is when the field crew completes the work. When these two conditions have been met, CPAS can mark the order as complete and place the dollars associated with that order in the Company assets for inclusion in the rate base.

As you can see, there is a large amount of coordination required in the work order life-cycle. Data is transferred back and forth between the GIS data stores, which reside on a UNIX server, and systems residing on our mainframe as well as other client-server systems using their own servers. It took some experimentation, as well as some ingenuity, to determine the most efficient method of doing these transfers. We also worked out compromises with users of these systems when we determined that initial requests would be unfeasible given the current architecture of the systems and the software available. An example of this is the WMS project creation. The initial request was to create the WMS projects real-time. We determined that the current architecture would make the process cumbersome and time-consuming, and we were able to agree on a thirty-minute turnaround for the project creation.

In addition to the technological hurdles to make this process work, we also had to contend with procedural issues. We were replacing some manual processes that were ingrained in the daily routine of the people responsible for these orders. A good example of this is the material issue process. Before GIS, the planner would print out a paper copy of all the materials required for the order along with the account number for those materials. The construction crew would then take this piece of paper to the storeroom to request the materials. With GIS, there is no paper copy. The material request is sent electronically to the storeroom. The person responsible for that order must then notify the storeroom when they need those materials issued. The completion process used to involve a clerk manually looking through a stack of papers to determine if there was a variance between the design and the completion data.

Now, that process is all done electronically. Maintaining the facility maps used to be a two step process. The planner would design the job, and then manually draw a sketch of the changes for the construction crew to use. When the order was completed, the sketch, with any changes made in the field annotated, was then sent to another department where the maps were manually changed to reflect the new facilities. Now, the facilities have already been added to the map by the planner during the design phase, and all that is required is for the construction changes to be changed on the map. We have made significant progress toward our goal of a completely integrated work order process. The amount of paper produced during the work order life cycle has been reduced from a folder containing a large stack of paper to usually just the sketch printed for the construction crew’s use.

The length of time from construction completion to order closure has been reduced dramatically, from what was commonly many months after the construction was complete to a matter of days. There are still some areas where we are looking for additional improvements. We want to reduce the number of systems that status change information must be captured in. We want to improve the data transfer between systems. As new tools become available, and as our GIS matures, we are confident we can continue to improve what has already proven to be a valuable resource for improving the overall efficiency of Niagara Mohawk.