Prophet 21 connectors implementation

  • Updated

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 article gives additional information for implementing Prophet 21 using the Optimizely 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 a CreditCardTypeMapping List - Used to map credit card types in Configured Commerce to those in P21. See the below example - the Name will match the value in P21, the Description will match the name of the credit card type in Configured Commerce.


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 tax calculation

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.



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 does not exist in the ERP so this data will show up as duplicated. If you do not 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.

This job also attempts to import the freight_code field, which may or may not exist in your records. If you do not use freight_codes at the customer or ship-to level, this column should be removed from the job.

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.