Alberta Pipes | Integration portal for Oil & Gas company

Location: Alberta, Canada

Industry: Oil & Gas (OCTG)

Problem

ATP is an Oil and Gas Pipeline company specializing in piping and pipe related products in Calgary Canada. ATP’s warehouses are outsourced to 3rd Party vendors who manage the inventory and handle shipping. Stock movement from these warehouses is consolidated on a daily basis and sent to ATP where it is interpreted and imported into the company ERP – Sage Adonix X3. Several warehouse management firms are involved in the process with various systems and file formats used to track the stock movement.

This import took several days to enter and was prone to errors which left the ERP lagging several days on the real stock position. This meant that ATP incurred a loss of several million dollars a year simply from interest payments against stock not reflected in the system.

Solution

Allied consultants engaged ATP to conduct a detailed analysis of the various systems and parties involved. The evaluation included an evaluation of:

The systems and protocols involved
The semantics and schematics of the files produced by these systems
The business process and business rules involved in the import process in ATP
Allied developed an Integration Portal that allows business users to view and correct data received from the Yards and then submit it to Sage. The system checks for new data every 5 minutes from the Yards and attempts to validate the data for submission to Sage.



One of the major challenges with Yard data is the bad quality of data. For example the Yards may send “ALBERTA TUBULASR” when referring to a sage entity named “Alberta Tubular Products Limited”. A custom-developed learning module was developed to enable the system to do a fuzzy match between the two and determine that they mean the same thing. If suitable corrections could not be made, the system raised the data for correction to the users. The system was designed to learn from any corrections so that a correction once made by users was remembered by the system for future references for that customer.  Middleware services also ensured that stock records were processed in order and that a stock out movement related to a stock in the movement were treated in the correct order.

An SOA oriented middleware is envisioned with BizTalk acting as the common services hub. BizTalk services exposed standardized middleware web services that current yards and future systems could use to load data into Sage. Business and transformation rules along with business processes implemented in BizTalk ensured that a central normalized view was available throughout the enterprise and all its systems.

An add-on Sage Adonix X3 enabled services to be exposed for all objects and subprograms available within Adonix. This enabled almost all the functionality available within Sage to be available to external integration via the service bus.

Tech Stack


Microsoft BizTalk Server 2010
Microsoft SQL Server 2008 R2
ASP.NET 4.0, JQuery
Sage Adonix X3