HostBridge Technology

A HostBridge Case Study


Content > Case Studies

Excel with CICS

City Controller’s Office Streamlines PO Cancellation Using HostBridge Web Services


One of the nation’s largest municipalities relies on a CICS accounting application to manage its financial operations. At the same time, employees in the city’s 60-plus departments use Excel for day-to-day financial tasks and analysis. At the end of every fiscal year, when unliquidated purchase orders must be cancelled, data from Excel spreadsheets has to be transferred to CICS in order to reconcile the two systems. Until recently, the transfer was handled manually. Departments submitted spreadsheets with PO cancellation data to the controller’s office, and controller staff keyed the data into CICS – a process that consumed at least 100 hours. Today, however, thanks to the HostBridge Professional Services Team and their implementation of HostBridge Web services, the process takes just half a day.

Problem

Like many organizations, departments within the city create purchase orders that are not completely used, i.e., liquidated. Accounting requirements dictate that they be cancelled at year’s end, and cancellation must be done within CICS.

City employees, however, use Excel for many financial purposes, one of which is compiling and maintaining lists of financial documents, including purchase orders. Typically the lists are generated from mainframe data, but in the course of normal business they are frequently corrected or updated. And ultimately the newer data must be transferred from Excel into the CICS application.

To cancel purchase orders, departments submitted spreadsheets listing the POs to be cancelled. Accounting staff in the controller’s office then copied and pasted or keyed the Excel data into CICS. Each cancellation invoked CICS uniquely, and each required navigation through CICS screens. As exception conditions arose, staff also had to compile an exceptions list by copying and pasting data from CICS to Excel.

This labor-intensive process was tedious, time-consuming, and prone to error. More importantly, it was a drain on human and financial resources. Depending on the volume of POs to be cancelled – from 3,000 to 4,000 per year – as many as 10 staff spent days entering data into CICS. The process annually consumed at least 100 hours.

The city knew that solving the problem would bring significant benefits. “In these times of staff shortages,” said a controller’s office staff member, “freeing valuable staff from a tedious manual process would be worth much more than just salary savings. It would enable us to shift that skilled staff time to other, essential year-end close procedures.”

The question was, how? IT staff considered writing a cancellation process within the CICS application – but only briefly. Given the complexity of this core accounting system, they quickly understood that programming, testing, and implementing such a solution would be prohibitive. Modifying CICS would require making risky changes to a major system that otherwise works well, and it would be more expensive than simply continuing with the manual entry.

The city then found a far better solution – faster, easier, less risky, and far less costly – in HostBridge Web services.

Requirements

To streamline its PO cancellation process, the controller’s office turned to HostBridge Professional Services and the HostBridge Process Automation Engine, the JavaScript-based mainframe Web services/scripting engine. Requirements were as follows:

The city gave the go-ahead to the HostBridge team, but on a very tight timeline – about three weeks before the Excel spreadsheets began arriving in the controller’s office.

Solution

The project lead proposed a solution that included (1) a HostBridge Web service that could be invoked from Excel and execute mainframe processes, (2) a single master Excel workbook to aggregate PO records, and (3) configuration of the Excel workbook for XML-based communication with HostBridge and the mainframe.

HostBridge JavaScript Web Service

Installed on the mainframe, HostBridge software is able to process CICS transactions with the highest performance and fidelity. Using JavaScript as its mainframe Web services engine, it is fast, easy, and flexible.

The Web service developed for the controller’s office was a straightforward use of HostBridge JavaScript. A single script consisting of about 80 lines of functional code plus 250 lines of comments now enables Excel to invoke CICS, navigates multiple CICS screens, automates the entry of data into appropriate fields, handles exceptions, and generates the XML that returns results to Excel.

Excel Workbook and Configuration

Trickier than the CICS Web service was accommodating the business process and Excel. Rather than require city employees to change the way they work, the HostBridge team determined that the most efficient way to aggregate PO data from inbound spreadsheets would simply be to copy it into a single master workbook. The workbook would then have to be configured to communicate with HostBridge and display results in real time. There were other challenges with Excel as well.

While Excel can consume and display XML and make HTTP calls, it does not support either (a) manipulation of or programmatic access to XML or (b) the “posting” of requests to an HTTP service. So the team lead designed the solution to invoke the HostBridge Web service via an HTTP GET method, pass all parameters to HostBridge within the URL query string, and return an XML document containing only basic status information.

To address these varied issues, the project lead created and configured two spreadsheets within the workbook – one for data and results, the other to specify interoperability parameters (target host, port, service name, etc.). This required about 150 lines of Visual Basic code divided into three functions and three subroutines.

Two of the functions process the rows of data in the spreadsheet; the third encodes the URL prior to sending the data to the HostBridge service. The main subroutine iterates over the rows of data and, using the XmlImport method, invokes the HostBridge service for each row. It also handles returned data, noting successful cancellations in green, as illustrated below, and errors/exceptions in red.

Figure 1

Figure 1. Results reported to Excel from the CICS accounting app via a HostBridge Web service.

Security and Auditing

Another VB-based subroutine establishes a connection to the mainframe and carries out authentication for the Excel session utilizing the same Windows infrastructure that maintains user credentials for Internet Explorer sessions. The credentials are cached for the life of the Excel session and flushed when Excel is closed. The HostBridge team lead used the VB QueryTables object to configure Excel to prompt for a user’s CICS credentials and send them to the mainframe via a standard Basic Authentication header. The VB code never has access to the credentials, and all host security/auditing remains in force.

Results

HostBridge Professional Services handed its work off to the controller’s office in less than two weeks. After QA testing, the controller’s office migrated the code to their production system on the morning PO cancellation began. By 10:00 am, over 2,000 purchase orders had been canceled. By the end of the day, all of the work that had been submitted was complete – without any risky change to the mainframe.

A process that a year ago took nearly 12 days now takes less than one – a reduction of more than 90%. With the HostBridge Web service putting CICS and Excel on the same page, the Controller’s office saves at least $4,000 per year and has essentially regained at least 100 staff hours to use for other essential tasks.

Pleased with the outcome, the Controller’s office plans to use this first HostBridge Web service as a model and is now determining which processes to streamline next for greater savings.

Copyright Notice

Copyright © 2011-2015 by HostBridge Technology. All rights reserved. No part of this publication may be reproduced, transmitted, transcribed, stored in a retrieval system, or translated into any computer language, in any form or by any means, electronic, mechanical, optical, chemical, manual, or otherwise, without prior written permission. You have limited permission to make hardcopy or other reproductions of any machine-readable documentation for your own use, provided that each such reproduction shall carry this copyright notice. No other rights under copyright are granted without prior written permission. The document is not intended for production and is furnished “as is” without warranty of any kind. All warranties on this document are hereby disclaimed including the warranties of merchantability and fitness for a particular purpose.

Trademarks

HostBridge and the HostBridge logo are trademarked by HostBridge Technology. All other trademarks mentioned are property of their respective owners.