Hitting a home run via OnBase integration with WAM 1.9 Bernard - - PowerPoint PPT Presentation

Hitting a home run via OnBase integration with WAM 1.9

Bernard Schubach, Resource International, Inc. implemented for City of Columbus DPU

Slides are available here https://tinyurl.com/OnBaseWAM and will be on https://ouug.org after the conference.

In a prior millennium…. In a prior millennium…. In a prior millennium…. In a prior millennium…. In a prior millennium….

To solve this, they employed…. …to bring inspections into the 21st Century.

Inspector requests Work Order in WAM, duplicating details from OnBase

Workflow

Inspector requests WAM WO? Enter inspection details and upload scan into custom

• ff-line form on tablet

Scanned inspection Inspection Details DPU Inspector discovers discrepancy No Yes Inspector edits Inspection to log WAM Work Order and Task Number Safety Mgr. requests WAM WO? Yes Cancel Inspection Item No Connect to City network; store in OnBase database.

Duplicate entry can lead to mistakes!

Primary Inspection Form Primary Inspection Form Primary Inspection Form Primary Inspection Form

Triggers creation of work order

Inspection Worksheet Item Inspection Worksheet Item Inspection Worksheet Item Inspection Worksheet Item

Dashboard Dashboard Dashboard Dashboard

What is it used for?

Inspecting the safety of their facilities — literally anything concerned with safety. Divison of Water Divison of Sewerage Division of Electricity 3 water treatment plants 2 Wastewater Treatment Plants utility metering services water distribution maint. sewer maint. operations center building maintenance watershed management composting facility fleet maintenance water quality assurance lab fleet maintenance ++++++more

• Signs
• Fire hydrants
• Chairs
• Tables

There is no limit to how many items they inspect.

Where is it used?

• About 8 safety inspectors regularly use this.
• Go-live was Jan. 31, 2020.
• As of February 28, there are 32 WAM work orders generated from this

integration.

Deployment

Key Data captured on Primary Inspection Form

• Inspection number (auto assigned ID)
• Inspection Location
• Inspection Type:

○ Monthly, Annual, Semi-Annual ○ Pre-startup Safety Review (PSSR) ○ Safety Observation ○ Other

• Plant Manager
• Additional Point of Contact
• Scheduled Date/Time
• Specific Inspection Assignments and to whom they are assigned
• Inspection Date, time, actual inspector name
• Inspection Status

Worksheet Items

• Each Safety Inspection Form contains multiple worksheet items:

○ Related Inspection Number (foreign key to Primary Inspection form) ○ Worksheet Item ID (auto assigned ID) ○ Item status ○ WO Required (True/False) - true triggers interface with WAM ○ WO/Task Number, if work order required (populated from WAM interface) ○ Hazard Class: ■ 1: Immediate Action ■ 2: Handle within 24 hours ■ 3: Handle within 72 hours ■ 4: Handle within 30 days ■ 4+: Handle within 30 to 60 days ○ Location of Area of Inspection ○ Discrepancy

Worksheet Items (continued)

• Applicable Regulatory Standards

○ OSHA 29CFR 1910 ○ OSHA 29CFR 1926 ○ NFPA Life Safety Code ○ NEC ○ NFPA 70E ○ Other

Benefits of using this interface

• OnBase form is offline - synced with server, so it can work everywhere.
• WAM 1.9 needs a server connection, but OnBase creates the work order

whenever it syncs with WAM.

• Details for work order filled out offline in OnBase.
• No need for duplicate data entry
• Minimal cost: User interface on WAM side is ultra-simple
• City already using both OnBase and WAM

Obstacle: User buy-in / inertia

Eventually management made them start using it.

“The Ideal Moment of Inertia for a Baseball or Softball Bat, IEEE Transactions on Systems” by A. Terry Bahill

Solution:

Technical Details

• OnBase creates a flat file with the data to create work order, vertical bar character as separator. File is

given a unique file name which is the OnBase worksheet number ○ Work Type (R - corrective) ○ Work Order Status (PLANNING) ○ Work Class: UNCLASS ○ Work Category: SAFETY ○ Requestor Name: ○ Asset Record Type / Asset ID ○ Planner

○

Work Description (Inspection Location + Discrepancy + Hazard Class) ○ Department, Area, and Account number get auto-populated from asset record ○ Work Order number gets auto-populated from WAM sequence numbers table

• SQL*Loader inserts the work order and sends the output to a same named file in staging folder on server
• Use Linux grep utility to parse output of SQL Loader for errors or for work order number; puts them into

.out or .err file, with work order number or error

• Batch on OnBase server looks for .out or .err file and brings work order number into OnBase.

Watch for .out or .err

Workflow

Inspector requests WAM WO? Enter inspection details and upload scan into custom

• ff-line form on tablet

Scanned inspection Inspection Details DPU Inspector discovers discrepancy No Yes Batch job on WAM server imports work

• rder/task,

writes .out or .err file Work Order and Task Number get written back to OnBase .out .err Safety Mgr. requests WAM WO? Yes Cancel Inspection Item No Connect to City network; store in OnBase database. OnBase writes out flat file with work

• rder data

New

Why a Flat File?

• WAM 1.9 doesn’t have a native interface to import Work Orders
• Advantage is that this interface is fully transportable to any future CMMS,

including WAM 2.x, because OnBase interface doesn’t know anything about WAM.

• SQL Loader is simple, old technology, so current interface to WAM 1.9 is

easily maintainable.

• It uses custom function get_next_work_order_number that mimics Oracle

functionality from sdbp_generate_sequence_no, but as an Oracle function (pragma autonomous transaction), so that it can be called by SQL Loader.

Windows Task Scheduler Windows Task Scheduler Windows Task Scheduler Windows Task Scheduler

• n WAM app server
• n WAM app server
• n WAM app server
• n WAM app server

Windows Task Scheduler Windows Task Scheduler Windows Task Scheduler Windows Task Scheduler The Batch Job The Batch Job The Batch Job The Batch Job

Runs every 5 minutes, Monday – Friday, 6:00 a.m. – 8:00 p.m.

The Schedule The Schedule The Schedule The Schedule

Feedback from Client

This eliminates the manual processes of the safety inspection and creates an automated, electronic, mobile solution, using a combination of various OnBase modules and an application developed by 3SG Plus specifically for DPU.

`

Questions?

Bernie Schubach bernard@resourceinternational.com (614) 797-9482