InsiteConnect for Prophet 21 guide

This document series outlines the integration elements included in the Optimizely {user["product-name"]} 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 {user["product-name"]} implementation.

Optimizely's Prophet 21 connector uses standard integration mechanisms between Prophet 21 and {user["product-name"]}. This document focuses on {user["product-name"]} customers implementing a version of {user["product-name"]} and integrating it with Epicor Prophet 21 with implementation assistance from {user["product-name"]} partners.

{user["product-name"]} based this integration on the following:

Complete the following pre-requisites before attempting this integration:

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

{user["product-name"]} 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 {user["product-name"]} does not have the resources or expertise to troubleshoot standard ERP APIs.

The integration tasks between Prophet 21 ERP and {user["product-name"]} 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): {user["product-name"]} 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 {user["product-name"]}.  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): {user["product-name"]} relies on the Epicor Prophet 21 APIs for functions like order submissions that require data to be submitted to the ERP.

Refreshes use the Field Map capability of the {user["product-name"]} 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 {user["product-name"]} 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 {user["product-name"]}.

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 {user["product-name"]} options are to Ignore, Delete or Set a field such as IsActive or DeactivateOn.  These strategies will depend on several factors, including:

Each refresh will define the standard deletion strategy being used.

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:

This article gives additional information for implementing Prophet 21 using the {user["product-name"]} connector.

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 {user["product-name"]}, 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 {user["product-name"]} 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 {user["product-name"]}, 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. {user["product-name"]} 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 {user["product-name"]} 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 {user["product-name"]}.

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 {user["product-name"]}.  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 {user["product-name"]}.

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.