ERP System Integration

Many organizations have strategic investments in ERP (Enterprise Resource Planning) Systems, such as SAP, Oracle, SalesForce, Sage, Microsoft Dynamics, etc. In many cases, there is substantial need to integrate such ERP data with "local" data defined by the business.

This page explains various alternatives for integrating such data with "local" SQL data:
  1. ERP JDBC - you can access such data via JDBC drivers that encapsulate the business logic.
  2. ERP REST Calls - most ERP systems provide RESTful APIs you can invoke from API Creator.
  3. ERP Stored Procedures - some systems expose Stored Procedures which you can use to read / update data.
Observe the focus of API Creator support is the integration and automation of ERP data with local SQL data. API Creator is a complement to other products in this space, such as Data Integration and Enterprise Application Integration products.

ERP JDBC Drivers

ERP systems store their data in relational database, but direct access to the underlying data is a very bad idea, since it bypasses the business logic.  Most organizations therefore prohibit such access via standard JDBC drivers.

Yet, SQL access provides many benefits, such as reporting, Business Intelligence, etc. Some integration vendors have therefore recognized the opportunity to build 'special' JDBC drivers that link in the ERP business logic. These are referred to as ERP JDBC drivers, as distinct from (generic) JDBC access. Such vendors include Progress JDBC (formerly Data Direct), RSS Bus, and others.

These products unlock substantial value in API Creator: since the ERP data looks like SQL Tables, API Creator SQL automation can be applied to ERP data, including:
  • Instant RESTFul API creation
  • Why?

    Most ERP systems provide RESTful APIs, so it's reasonable to ask why Custom Resources provide value.

    Certainly, if the ERP RESTful APIs fit your requirements, we encourage their use.

    In our experience, most customers want customized APIs to gather the exact data they need, in the exact desired format (aliases etc), and integration with other SQL data.
    Custom Resources - including integrating such ERP data with other data, as explained in the containing page (see note at right)
  • Enforcement Logic - declarative integrity and security can be defined, including JavaScript extensibility based on the automatic Object Model
  • Live Browser - we have found cases where Live Browser support for features like undateable grids and master / detail presentation to be valuable complements to native ERP user interfaces
For these reasons, we advocate this as the preferred approach. Work with your CA Technologies representatives to engage this functionality.

ERP REST calls

RESTful ERP data can be integrated with local SQL data using JavaScript resources. In the case of ERP REST calls - the JavaScript service is used to handle authentication and access. For example, the client may be using some form of active directory for authentication (single signon) and wants to expose the ERP REST calls as part of an integrated service. A custom security authentication service can be added for active directory and the custom resource can expose the specific REST service calls. These calls can be made directly by an end-user application or by a business logic rule or event - SysUtility.getResource("ERPRestServiceNm", parms).

ERP Stored Procedures 

Some systems, such as Microsoft Dynamics, expose Stored Procedures which you can use to read / update data. In these cases, you can also use Stored Procedure Resources to access ERP data. These Stored Procedures encapsulate the business logic, maintaining database integrity.

Access such Stored Procedures by defining an additional Data Source for ERP data access. We recommend disabling all direct SQL access by unchecking Default Access Levels.

Then, you can access your ERP data in variety of ways:
  • Stored Procedure resources can be exposed as end points
  • Invoke Stored Procedures from rules and events (e.g., to calculate invoice total, etc)