Integrate ERP Data with Local SQL Data

Many organizations have strategic investments in enterprise resource planning (ERP) systems, such as SAP, Oracle, Salesforce, Sage, and Microsoft Dynamics. In many cases, the organization needs to integrate their ERP data with local SQL data the business has defined.

You can integrate and automate ERP data with local SQL data using the following alternatives:
  • ERP JDBC drivers. Access ERP data by way JDBC drivers that encapsulate the business logic.
  • ERP REST calls. Invoke the ERP system's RESTful APIs from API Creator.
  • ERP stored procedures. Read/update data using stored procedures that encapsulate the business logic.

Access ERP Data by way of JDBC Drivers

ERP systems store their data in relational database and direct access to the underlying data bypasses the business logic. Most organizations prohibit direct access to underlying data by way of standard JDBC drivers. You can access underlying data by way of a JDBC driver for reporting and business intelligence.

Some integration vendors, such as Progress JDBC (formerly Data Direct) and RSS Bus, have built ERP JDBC drivers that are distinct from (generic) JDBC access and link in the ERP business logic. The ERP data looks like SQL tables. You can apply API Creator SQL automation to this data by accessing the data by way of these JDBC drivers.

Accessing underlying data by way of a JDBC driver provides the following benefits:

  • Instant RESTFul API creation.


Most ERP systems provide RESTful APIs, so it's reasonable to ask why custom resources provide value. 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 (for example, aliases), and integration with other SQL data.

  • Custom resources. This includes integrating such ERP data with other data.
  • Reactive logic enforcement. You can define declarative integrity and security, including JavaScript extensibility based on the automatic object model.
  • Data Explorer. Data Explorer-support for features like undateable grids and master/detail presentation to be valuable complements to native ERP user interfaces.

For more information about engaging API Creator SQL automation, consult with your CA Technologies representatives.

Access ERP Data by way of REST Calls

You can integrate RESTful ERP data 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 sign on) and wants to expose the ERP REST calls as part of an integrated service. You can add a custom security authentication service for active directory and the custom resource can expose the specific REST service calls. End users or business logic rules or events, such as SysUtility.getResource("ERPRestServiceNm", parms)can make these calls directly.

For more information:

Access ERP Data by way of Stored Procedures 

You can access (read/update) ERP data using stored procedures that some systems, such as Microsoft Dynamics, expose. These stored procedures encapsulate the business logic, which maintains database integrity. You access these stored procedures by defining an additional data source so that you can access ERP data access.

Best Practice: Disable all direct SQL access by clearing the Default Access Levels.

After you have defined an additional data source, you can access your ERP data in the following ways:

  • Stored procedure resources can be exposed as end points.
  • Invoke stored procedures from rules and events (for example, to calculate invoice total).
For more information: