As of January 2023, Optimizely allows partners and the implementation community to build ERP connectors to increase the number of ERP connectors and enhancements available to customers. Optimizely continues to offer existing ERP connectors in their current state, and partners have access to the connector code for future enhancements.
This document series outlines the integration elements included in the Optimizely Configured Commerce connector for Infor FACTS ERP. It identifies and defines the default integration points, along with the necessary details, including the field mapping and high-level technical processes. While these integration points and jobs cover a majority of the integration requirements with FACTS, there may be customer-specific requirements that you need to incorporate into a customer's Configured Commerce implementation.
Commerce implementations will use standard integration mechanisms between FACTS and Configured Commerce will be used wherever possible, using the native ERP's APIs. Modifications can be made to the integration processes to support a customer's unique business needs. This document focuses on Optimizely customers implementing a version of Configured Commerce and integrating it with Infor FACTS, with implementation assistance from Optimizely partners.
Optimizely based this integration on the following:
- FACTS Versions Supported: v9.1.1 + (Credit Cards Available) / 7.8 (No Credit Cards Available)
- Payment Gateways Supported: Cenpos
- Tax Calculators Supported: Avalara or FACTS directly
- Freight: Configured Commerce's shipping engine
Complete the following pre-requisites before attempting this integration:
- FACTS APIs must be in place and accessible to both a pilot/test instance and the production instance
- APIs must be accessible from the internet over port 443
- The ODBC Driver and Server must be licensed and installed
- The default version of the ODBC driver is version 5x which does not support some of the more advanced capabilities of ODBC that we would otherwise leverage such as CASE statements and UNION joins. We will call out specific issues related to this in the body of this document. This has a very minor impact to the connector.
- A WIS server must be installed and running while most items are available via API, since some constructs, such as customers, are not available in APIs, we have developed all of the standard refreshes to be done via the WIS. Note that the 32 or 64 bit version of the ODBC driver should match the version of the WIS installed we recommend 64-bit version for both.
- If Pickup Orders are going to be supported make sure of the following:
- In FACTS, make sure that the warehouses allowed for pickup have corresponding web-enabled ship-via and tax codes assigned.
- Note that we have created a recommended list of jobs with numbers on them that can be linked together once the Connector setup creates the jobs. There are several jobs marked FACTSxx which you may or may not want to run on a regular basis but typically run at least once to populate initial data. These can be run on demand as needed or added to a chain of jobs that run on a recurring basis.
Many portions of this document refer to tables within FACTS. See FACTS documentation for definitions of those tables and potential values.
Configured Commerce relies heavily on the Pricing and Availability APIs, so Optimizely assumes the FACTS APIs are responsive. Any issues with the speed of the APIs may require an ERP expert to assist, as Optimizely does not have the resources or expertise to troubleshoot standard ERP APIs.
Version 7.8 of the FACTS ERP uses the table ar_slsp_terr instead of ar_salesperson_territory. If integrating to version 7.8, the salesperson refresh job will need to be updated with the correct table name.
There are many features in FACTS and some of these are worth calling out as not being supported by the standard connector including:
- Credit Card Processing:
- You must be at FACTS Version 9.1.1 or higher in order to process credit cards.
- The implementation of the connector will only pass through an authorization token to FACTS and this may or may not limit your ability to partial orders or ship after the time in which the authorization is valid.
- We cannot currently support discontinued products based on the Active flag either the active flag or the allow web purchase flag being NO will cause the product to be deactivated in the commerce database.
Overview of integration jobs
The integration tasks between FACTS and Configured Commerce are separated by the source and destination of the datasets and the mechanism used to implement the integration. The three types of integration tasks are designated as Refresh (Pull data in batch directly from the database), Submit (Push a transaction) or RealTime (direct calls to the APIs).
Refresh (Pull): Configured Commerce relies on FACTS to provide product, customer, order history, invoice data, and aged A/R data. Refresh tasks are typically run at scheduled intervals to synchronize new or updated data with Configured Commerce. Direct calls to the FACTS database are used for refreshes via the WIS.
Direct API Calls (Real-Time): Direct calls to the exposed FACTS API are made for things like inventory and pricing.
Submit (Push): Configured Commerce relies on the FACTS APIs for functions like order submissions that require data to be submitted to the ERP.
Refreshes use the Field Map capability of the Configured Commerce integration architecture. When we construct a connector, we create a series of job definitions that encode these mapping rules within the job definitions for refresh jobs. The mapping documents below will call out the standard connector logic and it is up to the implementation partner or customer to make adjustments as needed for their particular needs.
For example, by default, the product length/width/height are not exposed in the Admin Console unless exposed via the application dictionary. The query may obtain these fields, but they will not be in the standard field map since the fields are not exposed in the field mapper unless they are exposed via the application dictionary.
Note that the setting for the company number in FACTS is IntegrationConnector_FactsCompanyNumber and is incorporated into the standard jobs. The value of the setting name is case sensitive.
For each entity being refreshed, there must be a strategy on how to handle records that are not in the dataset retrieved from the source tables. The Configured Commerce options are to Ignore, Delete or Set a field such as IsActive or DeactivateOn. These strategies will depend on several factors, including:
- Is the data being snapshotted? (The whole table is being retrieved)
- Are we processing the data as delta datasets? (Net changes only)
- Are there child collection considerations? (Records that relate to a parent, such as invoice history lines being children to the invoice history header table)
- Are we retrieving the data in separate queries? (Such as Customer Bill-Tos and Ship-Tos.)
- Unless otherwise noted, we will be reading the -DELETE version of each refresh API and embedding it into the dataset to be processed. The records marked as being deleted will have the delete action applied.
Each refresh will define the standard deletion strategy being used.
There will be several real-time interactions with FACTS through the APIs, this document will outline the specifics of each API call as needed. Note that FACTS implements a set of specific APIs that can be used for website integration whereby records are added to an interface table named SMCATL. When the API is invoked against one of these APIs, it will normally consume those records and they will not show up again until the records are modified. There is a process to initialize these tables using menu SMF and the StoreFront Initialization option but the standard connector will be using ODBC for refreshes instead of APIs. There may be circumstances, however, that a partner may wish to leverage the SMCATL table instead. We are choosing to use ODBC for simplicity, control by the business user and consistency.
While we are not currently using these APIs for refresh jobs, we are providing additional information about them should a partner choose to implement a refresh that does.
There are some base fields and format that we would expect the refresh APIs to use a sample is below:
<?xml version="1.0" encoding="UTF-8"?><RequestBatch ConsumerKey="XXXXX" Password="XXXXX"> <Request RequestID="PaymentCodes" Company="01"> <MaxRecords>1000</MaxRecords> <RestartPoint></RestartPoint> </Request></RequestBatch>
The RequestID is the name of the API we are calling. All will use the Company as established in the Connector Parameters. The ConsumerKey and Password will come from the Integration Connection settings.
Note that he RestartPoint is used to indicate a place to grab the next batch of records if the records are not coming from the interface table and may be set from the previous call. Only APIs with MaxRecords will support this function and it is only available when the API is NOT using the SMCATL interface table.
The MaxRecords is used to indicate the maximum number of records to pull back from the ERP which can be set by integration job (Processing Batch Size) or use the global batch size set in the Integration Settings. Not all APIs support this function. Depending on the API, if the RestartPoint is available, it will require that as a starting value for the next batch.
The Delete version of the same API can be invoked if it exists and those records will be appended to the processed dataset as Deletions.