InsiteConnect for Prophet 21 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 Epicor Prophet 21 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 Prophet 21, there may be customer-specific requirements that you need to incorporate into a customer's Configured Commerce implementation.

Optimizely's Prophet 21 connector uses standard integration mechanisms between Prophet 21 and Configured Commerce. This document focuses on Configured Commerce customers implementing a version of Configured Commerce and integrating it with Epicor Prophet 21 with implementation assistance from Configured Commerce partners.

Connector requirements

Configured Commerce based this integration on the following:

• Versions Supported – 2017.2, 2018.1
• On-Premises Implementation – You need to install the Configured Commerce Windows Integration Service application on a local Windows machine to orchestrate the integrations
• Payment Gateways – VantivExpress (Element)
• Tax Calculators – Prophet 21 uses GetOrderSummar

Complete the following pre-requisites before attempting this integration:

• Epicor Prophet 21 APIs must be in place and accessible to both a pilot/test instance and the production instance
• The Prophet 21 APIs must be accessible from the internet over port 443
• The integration agent (Windows Integration Service) must be up and running in the customer's environment and have direct access to the databases
• A direct connection to the SQL Server databases for refreshes

Business issues/considerations

Many portions of this document refer to tables within Prophet 21. See Prophet 21 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 Prophet 21 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 Prophet 21 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 Prophet 21 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 Prophet 21 database are used for refreshes.

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

Submit (Push): Configured Commerce relies on the Epicor Prophet 21 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 Prophet 21 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 Prophet 21 version being integrated:

• Pricing – GetItemPrice
• Inventory – GetItemPrice
• A/R Aging – GetMyAccountOpenAR
• Order Submit – OrderImport
• Tax Calculation – GetOrderSummary

Implementation notes

This article gives additional information for implementing Prophet 21 using the Configured Commerce connector.

Turn on the connector

• Enter the Prophet 21 version you are running   There is currently little to no code that is specific to a version but this will allow us to make adjustments to the connector as needed as the versions change.
• Select the API version   Currently we support 2017.2 and 2018.1. Your version may be slightly different and, depending on the changes to the APIs we implement, one of these two versions may or may not work as expected. Contact Optimizely or your partner if you have a newer or older version to work on updating the connector.
• Company   This is an important component of the API integrations and should be formatted correctly (that is 01 for company 1)
• Freight Code   If you are charging freight on the site, enter the appropriate charge item code that the freight charge will be posted to. Remember that if you are charging the credit card, you may be fixing the freight charge in the eyes of the customer rather than estimating it and charging as incurred   this is a business decision and should be clear to customers what the behavior will be.
• Order Submit Contact Treatment   Prophet 21 may be set up to require a contact to be associated with each order. There are several options on how the software will behave depending on your particular needs: - Do not submit contact   This will skip any logic relating to the contact
  • Use API to lookup/submit   This setting will attempt to find an existing contact by their email address and company. If they exist, the located contact will be used. If they do not exist, they will be created on the fly as part of the order submission process. If this option is selected, the APIs must support the capability to perform the lookup which, to our understanding, requires version 2018.1 or later.
  • Contact from Customer   This setting requires a custom property for Customer in Configured Commerce called ContactId and populated with the actual contact id you wish to use. This should be updated from refreshes.
  • Contact from User   This setting implements the same strategy as for Contact from Customer except that the custom property is associated with the individual web user.
• Starting Order Number   This optional setting is used to help with improving the performance of the order history refresh job.
• Integration Connection   A connection to the API endpoint is required to be established and this setting will identify the connection to be used for contacting the APIs for various functions described in the reference sections.
• Send AuthCode In Order Submit - If this setting is set to yes, we will send in the payment gateway's authorization code into the AuthCode field in Order Submit. If disabled, we will send in a null in this field. Default value: Yes.

 Establish an API connection

• You must establish an endpoint to connect to the Prophet 21 Middleware. We use the XBG (eBusiness APIs) in our standard connections.  The following describes the configuration fields for the connection:
  • Type – ApiEndpoint
  • Source Server Time – select the timezone that the Prophet 21 server is in, although this setting is primarily used for refreshes and not API calls
  • Debugging Enabled – Set to FALSE unless an implementer is debugging something. This setting will turn on debugging messages for all integration jobs which puts additional burden on the servers and database and should be used only when actually debugging.
  • API Address – This would be the address of the middleware. It may be in the format of https://999.999.999.999:3333 where the last digits are the port number configured for the API. Consult your Epicor implementer for additional information on setting up the proper address.
  • UserName – This would represent the actual username for the APIs and is likely to be a valid username within Prophet 21 directly
  • Password – This is the password associated with the user established above.

Determine pricing/inventory services

• If you want to use real-time services for pricing and/or inventory, you will have to set that up in Settings. You will have to save the settings and re-load them between turning on the connector and setting real-time services so that the Prophet 21 services will be displayed.
• For real-time pricing, set the Pricing Service to RealTime, select the API connection configured above and select Prophet 21 as the Real Time Pricing Service.
• For real-time inventory, set the Inventory Service to RealTime, select the API connection configured above and select Prophet 21 as the Real Time Inventory Service. The Included With Pricing setting should be turned to ON to minimize calls to the ERP.
• If you are using any real-time services, make sure to turn on Shared Caching to SQL Server. This will cache data from Prophet 21 across web servers to minimize calls to the ERP, which both lightens the burden on those servers and improves overall speed.

 Determine taxcalculation

• The Prophet 21 connector comes with a Prophet 21 version of the Tax Calculator, which will use an API call to calculate tax within the ERP. You may also use an external service like Avalara if you wish but you need to make sure it will be supported by the ERP and that the calculated tax will be the same within any calculations done within the ERP itself for the same order.

Set up the Prophet 21 import process

We are using the eCommerce APIs to submit the order with the connector.  When this happens, a file is actually transcribed to a location defined in the screen below which is provided by way of example and is not intended to be a specific recommendation. Work with your Epicor advisor for proper setup in your environment.

Additionally, the Scheduled Import Service Manager must be running in order to pick up the submitted orders and process them. You will know that they are being processed when the status on the Cart History in the Admin Console shows a status of Processing vs. Submitted and they will also have an ERP Order Number assigned   this happens as a result of the Order Assignment integration job.

Business considerations 

The following section outlines business considerations for particular jobs in this connector.  Based on how a customer has implemented and configured both the ERP and Configured Commerce, the jobs may need to be adjusted.  We generally recommend to chain these jobs together to run at a time during the night when customers are not likely to be processing and the ERP is available and not running backups or doing significant processing.

P21 - Warehouse Initial Load – This is an optional job that is normally only run at the start of the project to initially create all of the warehouse records in Configured Commerce from Prophet 21.

P21:01   Code Tables – This job is intended to be run as the first job in a chain and updates Sales Reps, A/R Payment Terms, Carriers and Carrier Services.  While we segregate carriers and services in Configured Commerce, that distinction doesn t exist in the ERP so this data will show up as duplicated. If you don t wish to refresh any of this information regularly, simply delete the step for the items you wish to bypass.

P21:02   Products – This job refreshes products and alternate units of measure.  It selects products where Inactive = N, delete_flag = N, product_type = R and suppress_from_web_flag <> Y.  This job uses delta datasets by default.

P21:03   Related Products – This job will pull in substitutions (inv_sub) as Cross Sells and Accessories (inv_accessory) as Accessories for products that meet the same criteria as products listed above.  If you wish to change where these land, the job can be altered as needed.  Note that this job will only add to the mapped products   it will not purge records that are removed in the ERP.

P21:04   Customers – This job pulls in BillTo customers and ShipTo customers as separate queries and, therefore, we cannot DELETE records as the DeleteAction.  Customers for the defined company and where web_enabled_flag = Y will be pulled in.  The ShipTos are created by joining customer to address. This job uses delta datasets by default.

P21:05   Customer/Product – This refresh pulls in the inv_xref records for active products. Configured Commerce only allows for a single cross-reference record for a given customer and product combination. This job uses delta datasets by default.

P21:06   Order Assignment – Because P21'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 this 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.

P21:07   Order History – This job is run and also looks back 5 days based on the date_last_modified field and will delete all lines first and then repopulate them in order to have a correct version of the order represented in Configured Commerce.  We do not delete old orders with this refresh job so orders that are entirely deleted from Prophet 21, once they are refreshed, will not be automatically removed from Configured Commerce.

P21:08   Invoice History – This job follows the same pattern as for Order History but does not delete child lines with the expectation that invoices, once generated, do not change.

P21:09   Shipment History – This job is intended to obtain tracking information so that it can be displayed on the Order History screen in the site.  It is based on the oe_pick_ticket table.  If you do not need to show this information on the site, you can safely remove this job from the list.

P21:10   Inventory Refresh – This job is created as a convenience measure if you wish to NOT use real-time inventory.  If you do use real-time inventory, do not bother linking this job to run.  This job is specifically set to runs using delta datasets meaning that it will determine the net changes from the last time inventory was refreshed and only send up the changes.  This would be useful if you are updating inventory more frequently than once per day and not using real-time inventory.