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 21 st Century.

DPU Inspector discovers Workflow discrepancy Inspection Details Scanned Duplicate Enter inspection details and inspection upload scan into custom off-line form on tablet entry Connect to City network; store in can OnBase database. Inspector requests Inspector Work Order in WAM, requests duplicating details from Yes WAM WO? OnBase lead No Safety Mgr. to requests WAM Yes WO? Inspector edits mistakes! No Inspection to log WAM Work Order Cancel Inspection Item and Task Number

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

Inspection Worksheet Item Inspection Worksheet Item Inspection Worksheet Item Inspection Worksheet Item Triggers creation of work order

Dashboard Dashboard Dashboard Dashboard

What is it used for? Inspecting the safety of their facilities — literally anything concerned with safety. ● Signs ● Fire hydrants ● Chairs ● Tables There is no limit to how many items they inspect. Where is it used? 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

Deployment ● 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.

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 Solution: 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

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.

DPU Inspector discovers Workflow New discrepancy Inspection Details Scanned Enter inspection details and inspection upload scan into custom off-line form on tablet OnBase writes out flat file with work Connect to City order data network; store in OnBase database. Batch job on WAM server Inspector imports work requests order/task, Yes WAM WO? writes .out or .err file No Watch for .out or .err .err Safety Mgr. .out requests WAM Yes WO? Work Order and No Task Number get written back to Cancel Inspection Item OnBase

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 on WAM app server on WAM app server on WAM app server on 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

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

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