InsiteConnect for Prophet 21 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 Prophet 21 ERP database (inv_mast).

Deletion Strategy – We will set the Deactivate On flag for the item record.

Product Cross-Sell

Configured Commerce retrieves Product Cross-Sells via a direct call to the Prophet 21 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 Prophet 21.

Product Alternate Unit of Measure

Configured Commerce retrieves Alternate Units of Measure via a direct call to the Prophet 21 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

The customer refresh pulls both Bill-To and Ship-to information into a common customer table.

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.

Customer Products

Customer-specific product data will be retrieved by Configured Commerce via a direct call to the Prophet 21 database.

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 Prophet 21 via real-time calls, so no refresh is needed.  The API used for this function is GetItemPrice.

Tax (order simulation)

Tax Calculation

Use an API to calculate if the tax calculator is set to Prophet 21 and uses the API GetOrderSummary.

Order submission

Order Submit API

Configured Commerce submits order to Prophet 21 via real-time calls.  The API used for this function is OrderImport.

Credit card processing

Configured Commerce calls the payment gateway directly to authorize a user’s credit card.  The authorization information is passed into Prophet 21 via the order submission process using an authorization token returned by the payment gateway.

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

Existing orders

Order History

Configured Commerce retrieves Order history data from Prophet 21 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 order records analyzed, the integration process uses a lookback period in the query.  This query is performed against the Prophet 21 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 Prophet 21’s Order Submit API does not dynamically create the order, we had to create a batch process to find orders that were created from the Web and update them in Configured Commerce prior to running Order History. It is imperative that the Order Assignment job is run first or else the order history records for orders that started on the web will be duplicated in history since the ERP Number is the data we anchor to for creating or updating the information. This job specifically looks back 5 days (defined in the job’s where clause and can be adjusted) to find web-based orders (based on the ‘taker’ field = ‘ESTORE’) and update them in Configured Commerce.

Shipments

The shipment refresh is dependent on having shipment data in the ERP from which we can obtain tracking information.  This refresh is typically run once per day and also uses a lookback period from which to run the query to limit the number of order records analyzed. This query is used against the order header transaction date only since we do not expect shipment records to change once generated.

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 Prophet 21 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 records analyzed, the integration process will use a lookback period when constructing the query. This query is used against the invoice date since we do not expect records to change once invoiced.

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

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.