InsiteConnect for SX.e (v10) reference

📘

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.

Supporting tables

Salespeople

The salesperson information is important so that we can set the primary sales rep on the customer records.  This allows your customers to know who their sales reps are, and allows your sales reps to view customer data in Optimizely Configured Commerce. It is also required if using the Request for Quote functionality in Configured Commerce.

Deletion Strategy – We will use Ignore and not delete any salesperson records automatically during the refresh.

Payment Methods

The payment terms refresh populates the payment methods table in Configured Commerce, which is referenced in history tables and sets the default value in the customer table used in order submission.    Typically, once this is run, customers can update the descriptions in the Admin Console to reflect the values displayed to the end user, such as ‘Terms’.

Deletion Strategy – We will use Ignore and not delete any payment method records automatically during the refresh.

Product tables

Products

The Product Refresh process retrieves item/product information directly from the SX.e ERP database.  It first looks for records in the ICSP (Products) table, and then it looks to the ICSC (Catalog) table for records that do not exist in the ICSP table.  If the same item appears in both the ICSP & ICSC tables, only the ICSP data will be integrated into Configured Commerce. Only records marked as active within SX.e will be retrieved in the refresh job.

If SX.e catalog information should be excluded, remove the Union join information for ICSC in the query within the job definition.

Some Product-related data required by Configured Commerce may not exist in the ICSP table.  Instead, this data will be retrieved from the ICSW table by joining on the cono, prod, and whse fields between the ICSP & ICSW tables. The whse value is determined using the default warehouse value as defined in a parameter in the integration job.

Unlike the ICSP, the ICSC table contains all the Product data required by Configured Commerce, so there is no need to join to the ICSW table if used.

Note that we only retrieve products that are in stock with an active status.  Inactive products are not retrieved by default nor is the inactive flag set on the products directly.  If an active product becomes inactive, it will be effectively discontinued on the site. This logic can be changed by the implementer.

Deletion Strategy – Since these tables can be large, Configured Commerce should only retrieve records important to the platform.  The job also runs using Delta Datasets to minimize transaction volume and needs to do a union query between the two tables, excluding the data in ICSC that already exists in ICSP. Configured Commerce uses the Set Field option to set the DeactivateOn field automatically based on using a snapshot strategy for data retrieval.

Product Cross-Sell

Configured Commerce retrieves Product Cross-Sells via a direct call to the SX.e database. This refresh can be modified or copied to use specific record types or relationships needed for a given implementation.

Deletion Strategy – This refresh uses the Ignore option. Users may create manual entries for different types of relationships that are not managed in SX.e.

Product Alternate Unit of Measure

Configured Commerce retrieves Alternate Units of Measure via a direct call to the SX.e database.  This refresh is done as a separate step in the Product Refresh

Deletion Strategy – Configured Commerce uses the Delete option for this data.

Inventory tables

Configured Commerce uses real-time calls to obtain pricing and availability via real-time calls. No refresh of inventory will be used.  See the section on Pricing for the API call mapping.

Customer tables

Customer

Configured Commerce retrieves both Customer (ARSC) and Ship-To (ARSS) information into a common customer table via the Customer refresh.

Deletion Strategy – Configured Commerce retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. Configured Commerce must use Ignore, since the data will be retrieved in two separate sweeps.  Delta Dataset option will be disabled.

Customer Products

Customer-specific product data will be retrieved by Configured Commerce via a direct call to the SX.e database.  While SX.e supports ship-to level customer-specific products, Configured Commerce integration will only pull the bill-to level records.

Deletion Strategy – Configured Commerce uses the Delete option to physically remove records that are no longer valid. Configured Commerce also uses Delta Datasets.

Pricing tables

Pricing API

Configured Commerce retrieves pricing from SX.e via real-time calls, so no refresh is needed.  The API used for this function is sxapiOEPricingMultpleV3.

Configured Commerce does not retrieve price breaks from this call - OEPricingMultipleV3 does not return enough information to calculate pricing based on the price break information returned.  However, if price-break-based pricing is set up, Configured Commerce sends in the correct quantity values and will retrieve the correct pricing for the price tier in the cart.

Tax (order simulation)

Tax Calculation

As part of the standard connector for SX.e, there is a Tax Calculator which makes an API call to SX.e to calculate and return the tax amount to Configured Commerce on the Cart (if configured) and Checkout screens.  If another tax calculator, such as Avalara, is used, then this Order Simulate function will be disabled. When using another tax calculator, be sure to review the Product & Customer refresh to ensure the correct data is being pulled into those records to pass into the tax service.

The API sxapiSFOEOrderTotLoadV4 will be used for this function.

📘

Note

It is expected that 1-time addresses, if allowed, will be able to have tax calculated internally within SX.e without additional information provided.  If taxes are critical for the order to be > submitted but the tax jurisdiction cannot reliably be determined, Optimizely suggests self-registration, new ship-to addresses, and 1-time addresses should all be disabled.

Order submission

Order Submit API

Configured Commerce will submit orders to SX.e via the API.  Typical order submission data will be included: bill-to information, ship-to information, and line item information.  If a credit card was used for the order, Configured Commerce will also submit the authorization token information.

A new customer is created if the user self-registers or adds a new ship-to address.

If a 1-time order address is submitted, it will be placed into the Order Header (OEEH).

The API sxapiSFOEOrderTotLoadV4 is used for this function.  The data below will show the net differences for the actual order submission.

The standard Configured Commerce connector mapping for API calls is hard-coded but implemented with pipelines to allow the implementer to extend the integration to incorporate additional mapping information for other fields or adjust the standard mappings.  The following shows the default approach to mapping the API call and only calls out the changes from the tax calculation.

The standard SX.e connector code will attempt to override the price if there is a line-level promotion.  This only works with the following options configured within SX.e:

1. If the setting ediprcfl is turned on in SX.e and ARSC/ARSS tables allow for it.
2. Create/update a setting in SASBR for category SXAPI,
   1. Rule: Override Price and value: Yes.
   2. Note that this can be used to set several parameters such as various defaults, debug directory, and so on.

Credit card processing

Configured Commerce calls the payment gateway (typically CenPOS for SX.e) directly to authorize a user’s credit card.  The authorization information is passed into SX.e via the order submission process using an authorization token returned by the payment gateway.  This information does not apply to SX.e v6.

A/R aging balances (invoices)

A/R Aging Balances API

Configured Commerce uses a standard direct call to an API endpoint to display the A/R aging information on the Invoice History page.  The standard aging buckets must be set up as global options in Settings.  We do not use the labels returned from the API, only the balances themselves.

The API used is sxapiARGetCustomerBalanceV2.

Existing orders

Order history

Configured Commerce retrieves Order history data from SX.e via a direct call to the ERP database.  The data is stored within the Configured Commerce database and refreshed on a regular interval, typically once a day.

To limit the number of SX.e order records analyzed, the integration process uses a lookback period in the query.  This query is performed against the SX.e order header transaction date AND the order line transaction date.

Orders placed in Configured Commerce are immediately added to the order history tables.  This ensures that a user will immediately see their orders on the Order History pages under My Account. Because the SX.e connector order submission uses the real-time API, the ERP order # should also be present.

Implementation Note - Performance – Because many SX.e customers have very large tables and there is not an index on the transdt field, the system must perform a table scan, which can greatly increase refresh run time.  A setting exists to capture the starting order number so that the refreshes will look back by date but limit the search to orders beginning at a specific number.  This improves performance and this value can be updated periodically (that is quarterly) if performance is an issue.  This applies to order history refresh, invoice history, and shipment history refreshes.

Implementation Note – Lookback Days – Optimizely’s strategy is to limit the amount of data being reviewed and transmitted to Configured Commerce for large history files. Configured Commerce only looks for records that have changed within a relatively short period of time, with a default of 5 days to cover any missed refreshes, weekends and holidays. The number can be modified based on implementation preferences. It is coded directly into the WHERE clauses of the history refreshes.

Record Selection – Only records for the current company, stagecd between 1 and 5, selected transdt withing the lookback days, and for transtypes not BL, QU, ST, FO, or BR.

Deletion Strategy – There is a special strategy for Order History, as it’s not ideal to do a full snapshot of the data. However, we need to catch any changes to, say, order lines that were deleted.  A special option to “Delete Children” is used, with the delete action set to Delete.  Any order that is processed clears and reloads the order line data to history.

Shipments

Configured Commerce’s shipment refresh  assumes some sort of shipping interface is used within SX.e so that OEEHP records are being generated with tracking information.  This refresh typically runs once per day and uses a lookback period from which to run the query.

To limit the number of SX.e order records analyzed, the integration process uses a lookback period in the query.  This query is used against only the SX.e order header transaction date, since we do not expect OEEH/OEEL records to change once invoiced.

Refer to the Implementation Notes in Order History Refresh for additional information. (Anchor link)

Deletion Strategy – Since this is not a full snapshot and this data is highly unlikely to change once processed, the Ignore delete action is used.

Invoices

Invoice data is retrieved from SX.e via a direct call to ERP database. The data is stored within the Configured Commerce database and refreshed on a regular interval, typically once a day.

To limit the number of SX.e order records analyzed, the integration process uses a lookback period in the query.  This query is used against only the SX.e order header transaction date, since we do not expect OEEH/OEEL records to change once invoiced.

Refer to the Implementation Notes in Order History Refresh for additional information.

The filter for these records only includes the specified company; stagecd 4 or 5; excludes transtypes BL, QU, ST, FO, and BR; enterdt not null; and transdt within the specified timeframe window.

Deletion Strategy – We will employ the Ignore strategy, as we will only be taking a snapshot and do not expect line information to change once the status of the order is changed to Invoiced.