InsiteConnect for SX.e (v10) guide

📘

Note

As of January 2023, Optimizely no longer adds new functionality to ERP connectors. Instead, partners can build customized solutions for clients. Optimizely continues to fix ERP connector bugs only if there are no workarounds or extensibility solutions available.

Audience and purpose

This document series outlines the integration elements included in the Optimizely Configured Commerce connector for Infor SX.e 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 SX.e, there may be customer-specific requirements that you need to incorporate into a customer's Configured Commerce implementation.

Configured Commerce SX.e connector uses standard integration mechanisms between SX.e and Configured Commerce. This document focuses on customers implementing a version of Configured Commerce and integrating it with Infor SX.e version 10, with implementation assistance from Optimizely partners.

Connector requirements

Configured Commerce based this integration on the following:

• Versions Supported: 10.3   On Prem
• On-Premise Implementation   You need to install the Configured Commerce Windows Integration Service application on a local Windows machine to orchestrate the integrations
• Payment Gateways – The Order Submit job assumes you use CenPOS with SX.e
• Tax Calculators – Configured Commerce can call Avalara or an SX.e API to execute an order simulation, which returns tax amounts

Complete the following pre-requisites before attempting this integration:

• Infor SX.e APIs must be in place and accessible to both a pilot/test instance and the production instance
• The SX.e APIs must be accessible from the internet over port 443
• The Configured Commerce integration agent (Windows Integration Service) must be up and running in the customer's environment and have direct access to the databases
• OpenEdge ODBC driver (32 or 64 bit) must be installed and able to connect to the databases
• If web promotions are used which may impact line level pricing, the global setting for OverridePrice in the Business Rules area of SX.e must be set to true. In the alternative, you can set the EDI Price override flag on individual customers to true (arsc.ediprcfl) since the APIs used for order submission are treated similarly to EDI orders.

Business issues/considerations

Many portions of this document refer to tables within SX.e. See SX.e documentation for definitions of those tables and potential values.

Technical issues

Configured Commerce relies heavily on the Pricing and Availability APIs, so Optimizely assumes the SX.e APIs are responsive. Any issues with the speed of the APIs may require an ERP expert to assist, as Configured Commerce does not have the resources or expertise to troubleshoot standard ERP APIs.

Overview of integration jobs

The integration tasks between SX.e ERP 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 SX.e to provide product, customer, order history, and invoice data.  Refresh tasks are typically run at scheduled intervals to synchronize new or updated data with Configured Commerce.  Direct calls to the SX.e database are used for refreshes.

Direct API Calls (Real-Time): Direct calls to the exposed SX.e API are made for things like the A/R Summary, inventory, and pricing.

Submit (Push): Configured Commerce relies on the Infor SX.e APIs for functions like order submissions that require data to be submitted to the ERP.

Refresh mapping

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 for refresh jobs.  The mapping documents below call out the standard connector logic and it is up to the implementation partner or customer to adjust as needed for their particular project needs.

For example, by default, the product length/width/height are not visible in the Admin Console, and therefore are not available in the Field Mapper.  The SX.e connector query will retrieve this data from the ERP, but it will not be in the standard field map tool to populate the Configured Commerce database. If the field is configured to be available via the application dictionary, they must be added to the field mapper in order for the data to flow into Configured Commerce.

Deletion strategy

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.)

Each refresh will define the standard deletion strategy being used.

API list

The following is the list of standard APIs used for integration   we will use the applicable versions based on the SX.e version being integrated:

• Pricing & Availability: sxapiOEPricingMultipleV3
• A/R Summary: sxapiARGetCustomerBalanceV2
• Tax Calculation/Order Simulation (if 3^rd-Party Tax system is not used): sxapiSFOEOrderTotLoadV4
• Order Submit: sxapiSFOEOrderTotLoadV4

Implementation Notes

The following is a list of suggested tasks to follow and review during the implementation of this connector.

Turn on the Connector

Connector enablement settings are global.

• Set up the Integration Connection
  • Select the type of SXe API.
  • The API Endpoint should be the URL or IP to the App server that is accessible from the website   for example: https://mycompany.com/sxapi
  • The SX.e API endpoint is often set up in a DMZ to segregate it from the network, and the app server host is configured to be accessible through the firewall.
• Enter the Company Number   we intentionally have the company associated with the API connection separate from the one used for refreshes.
  • This allows for the circumstance where data is being acquired from multiple SX.e companies but the API must only have a single, numeric company number for calls.
• The App Server Host is typically something like Appserver://mysxserver.mycompany.com:7282/testsxapiappsrv
  • The appserver address is based on where the API server is and how it accesses the SX.e app server. An IP address can also be used rather than a DNS reference.
• The User Name and Password must be entered conforming to the security and user setup in SX.e.
• Assign the connection to the generated integration jobs in the Admin Console.
• Set up the WIS SiteConnections.config table with an entry to the new Integration Connection.
• This presumes you already have the WIS installed, the machine registered as a valid integration server and a valid user with the role of ISC_Integration
• Optional: Chain the jobs together.  It is a common practice to set up the first job as recurring so that it runs every night and then chains from job to job through the chain of refreshes ending with rebuilding the search index.
  • This keeps your jobs from attempting to run concurrently.
• Enter the parameters for Company and Refresh Warehouse.
• The SX.e Version is used to trigger version-specific functionality of the integration.
• The SX.e Company number determines which company the integration will leverage in the ERP.
• The SX.e Web Order Number represents the field in SX.e that the Configured Commerce Web Order # value will be submitted to.
• The Refresh Warehouse is used for specific product information gathered from a default ICSW record instead of ISCP.
• The Starting Order Number is used to limit how far back the refreshes need to go to pull order or invoice updates.
• The SX.e Integration Connection that will be used for SXe. API calls. Setup of this connection is outlined below.

Business Considerations 

The following section outlines business considerations for particular Configured Commerce SX.e integration jobs. Based on how a customer has implemented & configured SX.e, various jobs may need to be adjusted accordingly.

SXe 03: Product

• Determine if you want to have the Catalog (ICSC) table retrieved by Configured Commerce in addition to the ICSP table
  • If you do not want to bring catalogs in, go into the first step to the WHERE clause and delete everything from UNION and below.
  • If you want other products (such as kits) to flow into Configured Commerce, modify the WHERE clause to include those item types.
  • If you are integrating product data with another source, make sure to determine which fields are derived from SX.e and which from another system or method. Those being managed from another system or method should be removed from the query and/or mapping.

SXe 04:Product Cross Sells

• This pulls records with record type of U (upgrades) and S (substitutes) from the ICSEC table into the Cross Sells related product type.
  • You can adjust this by changing either the SELECT clause for the desired relationship and/or the WHERE clause for the records to select.
  • You can also clone the job definition or add additional steps to include different sets of related products as needed. If doing this, confirm that the Product Relationship exists in the System Lists table.

SXe 05 :Customers

• Decide if you want to assign inside or outside sales reps to the customer. The default is the outside sales rep.  This drives the ability for a sales rep to sign into Configured Commerce and access the customers they are assigned to.
• Select the country code to use as a default if none is defined in the SX.e database. The default for the job is  US .

SXe 07: Order History

• The following points relate to the lookback period, which differ in integration versus the settings that control the default behavior on the site.

• Fill in the Order Status Mapping table in the Admin Console for order status codes to get the descriptions desired. Below is an exampledefault setup:

  • 0 = Entered
  • 1 = Ordered
  • 2 = Picked
  • 3 = Shipped
  • 4 = Invoiced
  • 5 = Paid
  • 9 = Canceled

• We suggest you backfill order history by adjusting this job so the WHERE clause using the transdt uses a range of orders or has an enterdt to only retrieve orders from a certain date.

  • This should be done in batches if there are a large volume of records being processed.

  • During this backfill process, turn OFF the delta dataset option so that multi-threading can be used to improve performance.

  • Make sure to confirm each clause is referring to the same time period being backfilled..

  • Optional: Turn Use Delta Dataset back on if desired. This will send only the net changes to Configured Commerce instead of all records.

  • Adjust the lookback days as needed. The intent is that we are using the

  • Once the backfill is done, set the Starting Order Number setting in Settings.

  • The starting order number should be old enough to cover any open orders that may incur changes, but new enough to limit the number of records pulled by the query for performance reasons.

SXe 08:Invoice History

• The strategy for invoices is identical to orders, especially since the data comes from the same source tables (OEEH, OEEL). This set of queries will only return records in Invoiced and Paid status to limit its scope.  Follow the  Order History  instructions above to load the initial tables and then set the job for ongoing refreshes.

SXe 09: Shipments

• This job is optional if you don t have any OEEHP records.
• It takes up almost no resources so it is not required to be deleted for performance concerns if these records don t exist.