Connecting to Epicor Prophet 21 (P21) for Integration (API’s)
When companies start their P21 customization journey, they will need to figure out how to best connect with the system. Many APIs are available for customization through P21 and its documentation, but understanding some of the differences can be hard. We’ll examine some of the different methods for interacting with P21 data and consider the advantages and disadvantages of each.
SQL / T-SQL
The simplest and most familiar place for most companies to start exploring the P21 system is via its database with SQL. SQL was first invented in the 1970s to work with data in structured databases. With P21, the structure comes from tables and views in the Microsoft SQL server system that supports standard SQL and T-SQL, which is a propriety extension to SQL for SQL Server. Companies will often use Windows ODBC or SQL Server Management Studio when they start working with P21 and SQL.
Tables
The tables in the SQL server are based on the object layout for the different screens in P21. These can be Order, Invoice, Product, Customer, Address, etc. The tables are stored in a normalized fashion to reduce data redundancy and improve system performance, so you will often find information broken out by header and detail. An example of this would be orders, which are stored in oe_hdr (header) and oe_line (detail)
Views
Prophet 21 comes with many pre-configured SQL views that eliminate the need to understand how to link different tables together, making it easier to get what is needed. When more information is needed that is not available from the view, different views can be joined together the same as tables to get a complete picture of the data.
Advantages and Disadvantages
The main advantages of using SQL for data access are its familiarity and ease of use. SQL is an industry-standard technology that many programmers are familiar with and allows a quick start.
There are a few disadvantages to highlight with SQL. The first is that it is not the recommended method for working with P21 from Epicor. Epicor recommends that integration be done via the APIs, which we will explore later in this article. Another disadvantage is that when using the cloud version of P21, SQL access will be limited to “read-only,” meaning that developers will only be able to query data from the database and not write back to it. The last disadvantage is that if you are updating data directly in the database, there is a chance that any business logic associated with the changed data will not be triggered, possibly leading to data inconsistencies.
P21 API’s
The Epicor P21 system has done a good job of creating and documenting the APIs. However, the APIs are complex due to the depth of their integration and abilities to work with the system. The documentation may also be a bit short in discussing the differences between what must be included in the API call and what is optional. Epicor and other P21 developers often cite the need to build up templates to help with P21 development, and with the templates, knowledge can be accumulated to allow faster development over time.
Transaction API
The Transaction API is designed to mimic how P21 processes transactions, such as Orders, Jobs, Items, and many others. Through this API, the programmer will be able to create, retrieve and update a stream of data in either XML or JSON that represents the information from a P21 screen, like Order Entry. With this API, integrations of individual or bulk actions can be created to process information in the P21 system.
To explore the Transaction API, navigate to https://{your middleware server}/docs/V2API.aspx
Advantages and Disadvantages
The advantage of the Transaction API is that it is quite flexible in terms of what can be done with it. Integrators can process large amounts of information through the system using patterned templates.
The disadvantage of the Transaction API is the depth of fields that are exchangeable with the API. The XML/JSON nodes can easily contain hundreds of fields of information without an indication of whether the fields are required or optional to be passed back. This can be mitigated over time as templates are created, but it causes an initial steep learning curve.
Entity API
The Entity API is designed to work on specific object models from the P21 system. It is designed to work on a 1:1 ratio for changes, where each create or retrieve and update is its own API call. The data for each call is directly linked to the model for that object and not from the P21 window metadata.
A unique feature of the Entity API is that some of the APIs allow for asynchronous calls with callback URLs. When an API call is asynchronous, it means that the API will return immediately when called without waiting for the call data to be processed. This can allow for a send-and-forget algorithm, where the API’s calls are made in quick succession without regard to when the information is processed. Adding the callback parameter allows the API call to receive a URL, which is then called by the P21 system when the request is processed. Using the callback allows an external system outside of P21 to be notified when the work is completed.
Advantages and Disadvantages
The advantage to using the Entity API is for doing updates or creating to specific records within P21. The Entity API, being directly tied to the model, means that implementors don’t need to worry about how different records within P21 interact and can focus on creating/updating just what they need to. Another advantage is the asynchronous and callback systems; these features can be handy for complex or event-oriented integrations.
The disadvantage of the Entity API is the smaller scope of each API call. Each call only works with a specific model object instead of the complete scope of all models, so it might require a larger number of API calls to complete a specific task. Due to this, other APIs may be better suited for bulk data processing.
Interactive API
The Interactive API is the only stateful API with P21. Stateful means that from API call to API call, the state of the interaction is maintained by the server, much like the way a user interacts with the system. When a user moves through different screens, P21 keeps an active state for that user so that it can be used for context for the user. The same is performed via the Interactive API.
The interactive API can be used for complex automation where feedback is required from P21 on the right choices to be made by an automation system. It can also be for interactions such as creating and updating user interfaces. The Interactive API could also be used to create test cases for business rules, where the results of a business rule can be tested to ensure it is working after changes or a P21 upgrade.
Advantages and Disadvantages
The Interactive API's advantage is that it mimics a user’s interaction with the system, allowing an external program to follow the same actions users would take.
The disadvantage of the Interactive API is that it can be quite complex to implement as the number of potential errors and warnings that can happen can be numerous and will need to be accounted for in any code created.
Data Services API
The Data Services API is designed for the retrieval of information from the P21 system. Through this API, the views mentioned in the SQL section above can be queried and returned via XML or JSON. The Data Services API is built on the OData protocol, which is an extension of REST API architecture.
The Data Services API promises near SQL performance, meaning that data retrieval will be quick. This also means it is optimal for bulk exporting data to data lakes, reporting, and analytics systems.
Advantages and Disadvantages
The advantage of the Data Services API is that it is easy to work with and is fast.
The disadvantage of the Data Services API is that it can only be used to extract data from P21. It can not be used to create or update records.
DynaChange Business Rules API’s
The DynaChange Business Rule API is a .Net library used in P21 business rules. All rules that an organization needs to start their classes inherit from the P21.Extensions.BusinessRule.Rule class from the P21.Extensions.BusinessRule namespace.
Once developers have started working with DynaChange Business Rules, many automations can be added to the P21 system to help users. Rules can be added to populate fields with default values, check the consistency of two or more fields to ensure that the correct information is being added, or copy information from one place to another on a screen to reduce users' having to enter information multiple times.
Inside DynaChange Business Rules, developers also have access direct access to the underlying SQL server, allowing the rule developer to query or update other parts of P21 when the rule is triggered.
Advantages and Disadvantages
The advantage of the DynaChange Business Rules APIs is that they allow companies to build specific integrations and processes that suit their company and help their users be more efficient with P21. The Business Rules API allows almost limitless customization of P21.
The disadvantage of the DynaChange Business Rules API is that it is a Software Development Kit API, so it needs to follow standard programming practices and is a complied program that is added to P21. They are more complex to create and integrate but offer tremendous power to companies.
Web Visual Rule’s API’s
The Web Visual Rule system is based on .NET the Model-View-Control (MVC) V5 framework that allows companies to add new windows P21. Developers will start by creating controllers that inherit from the BaseRuleController in P21.Extensions.Web namespace.
Web Visual Rules are unique in that they interact with P21 through XML that is passed to and back from the Web Visual Rule. The Web Visual Rule is initially passed an XML structure that contains the information from the screen the web visual rule is loaded from, the web visual rule then modifies that XML and sends it back the P21 where the screen is updated with the new information.
Integrators who would like to do more within the P21 system with Web Visual Rules can also add more integrations using the other available P21 APIs, such as SQL, Transaction API, and Entity API.
Advantages and Disadvantages
The advantage of the Web Visual Rule API is that it allows companies to build their own screens within the P21 system, allowing endless possibilities to improve user experiences.
Again, the disadvantage of the Web Visual Rules API is its complexity. It requires knowledge of the .NET and MVC development frameworks. Once a developer understands these concepts, development will be similar to that of other MVC applications.
Summary
Getting started with P21 customization can yield tremendous rewards in time and user experience for a company, but it does take some time. Reviewing the documentation and considering working with a consultant like BizXcel are good steps to take.
If you would like more information about BizXcel P21 services, please check out https://www.bizxcel.com/epicor-prophet-21