SQL*Magic : Super*SQL
|SQL*MagicSuper*SQLCase StudiesResourcesTechnology ChoicesSQL StandardLinks

Case Studies


  1. Identification and Resolution of Discrepancies in Unique Well Identifiers
  2. Improve Invoice Handling Workflow and Capital Reporting

Case Study #1

Identification and Resolution of Discrepancies in Unique Well Identifiers

Business Problem

The Financial Accounting, Production Accounting, Drilling, Field Data Capture, and Land Systems had data for slightly different lists of UWI's.  As a result, it was not possible to produce completely accurate reports on drilling success, return on investment by well or area, or to package up information easily for aquisition and divestment deals.

Technical Problem

Each of the systems used its own primary key structure, and the systems were spread across two different hardware (AS/400 and Solaris) and database (Oracle and DB2) architectures. Wells showed up in different systems at different times depending upon the source of the information (acquisition or drilled in-house), and the stage in the lifecycle of the well (planned, licensed, spudded, on production, reclamation).


First, the Oracle Transparent Gateway was implemented so that all DB2 tables could be accessed seemlessly from Oracle applications. Two new database tables were then created that were designed to track all changes to UWI's in all systems.  One table maintained a list of UWI's, the second mapped the primary key for each of the 5 systems to particular UWI values.

Several PL/SQL scripts were written to scan all 5 systems looking for new UWI's or changes in the mapping of UWI to primary key.  These scripts were run nightly, and when discrepancies were found, notification was sent by email to business contacts designated for each of the 5 systems.

In order to review and resolve UWI discrepancies, a web-enabled Oracle Forms application was developed which displayed UWIs in a tabular format.  Checkboxes were used to indicate which systems contained references to each UWI.  By sorting the data by LSD, the listing provided a quick way to identify data busts.  For example, the well 100102303615W300 might appear in 4 systems, where the well 100102303615W301 appeared in only one system.  The conclusion would be that the "301" well should be corrected to a "300" well.  The application allowed a clerical user to make this recommendation and provide a note as to why the change should be made.  An email then went to the appropriate business contact with this change request.

Over the course of 2 months a summer student was able to review and make change requests for all UWI discrepancies for a company managing 3,000 wells.

Case Study 2:

Improve Invoice Handling Workflow and Capital Reporting

Business Problem

The handling of invoices for field activities was time-consuming and labour intensive, resulting in lowered productivity for field engineers and drilling staff, numerous cases of misplaced invoices, and significant penalties for late payment of invoices.  The problem was particularly bad for large dollar value invoices that included work on a number of projects requiring review by field engineers in different field offices as well as final approval by someone at head office.

Technical Problem

The entire invoice handling process was dependent upon the movement of a physical invoice from reviewer to approver to the accounting department.


An electronic invoice handling process was developed which leveraged web technologies and email.  Physical invoices were scanned at the location where they were received, and basic data such as invoice amount, date, and vendor was captured in a web interface screen and stored in a central database.  Engineers assigned to review invoices were notified that an invoice was waiting for review, and web-based screens were provided to code invoices in whole or part to particular G/L accounts and/or capital projects. 

When all line items for an invoice had been reviewed and coded, a designated invoice approver could electronically approve the invoice for payment.  The accounts payable department then received email notification that an invoice was ready to be paid, and an A/P clerk could upload all required invoice and coding information through a batch process into the accounting system.

The result was a system where complex invoices could be reviewed and coded by several field engineers in a matter of hours or days, and the final approved invoice could be uploaded directly into the accounting system for payment without re-keying.

A side benefit of the system was the ability to do web-based capital reporting of all expentidures for a project, including invoices that had not yet been uploaded to the accounting system.  This just-in-time reporting was significantly more accurate than the numerous spreadsheets that had been used previously to estimate YTD expenses by project.