Prophet 21 connectors reference

  • Updated

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 via Configured Commerce are immediately added to the Configured Commerce order history tables. This ensures that a user will immediately see their orders on the Order History pages under My Account. Because Prophet 21s 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 jobs where clause and can be adjusted) to find web-based orders (based on the taker field = ESTORE) and update them in Configured Commerce.

Field mapping: Order history header

Field Name ERP Table.Field (oe_hdr) Commerce Table.Field (OrderHistory) Notes

ERP Order #

Order_no

ERPOrderNumber

 

Order Status

Completed

Status

Value - Description

Y - Completed

Any other value - Open

Order Date

order_date

OrderDate

 

Customer #

customer_id

CustomerNumber

 

Customer PO #

po_no

CustomerPO

 

Terms Code

terms

Terms

 

Ship Via

carrier_id

ShipCode

 

Requested Ship Date

requested_date

RequestedDeliveryDate

 

Billing Information: Company/Name

customer_name

BTCompanyName

 

Billing Information: Address

phys_address[1..2]

BTAddress1..2

 

Billing Information: City

phys_city

BTCity

 

Billing Information: State

phys_state

BTState

 

Billing Information : Zip

phys_postal_code

BTPostalCode

 

Billing Information Country

phys_country

BTCountry

 

Shipping Information: Company/Name

ship2_name

STCompanyName

 

Shipping Information: Address

ship2_add[1..2]

STAddress1..2

 

Shipping Information: City

ship2_city

STCity

 

Shipping Information: State

ship2_state

STState

 

Shipping Information: Postal Code

ship2_zip

STPostalCode

 

Shipping Information: Country

ship2_country

STCountry

 

Subtotal

oe_line.extended_price

ProductTotal

This is calculated by summing all of the extended_price lines from the oe_line table for this order.

Sales Tax

oe_line.sales_tax

TaxAmount

This is calculated by summing all of the sales_tax lines from the oe_line table for this order.

Order Total

Calculated

OrderTotal

SUM(Subtotal + Sales Tax)

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.

Field mapping: Order history shipment

Field Name ERP Table.Field (oe_pick_ticket) Commerce Table.Field (Shipment) Notes

ERP Order Number

Oe_hdr.order_no

ERPOrderNumber

 

Shipment #

pick_ticket_no

ShipmentNumber

 

Shipment Date

ship_date

ShipmentDate

 

Field mapping: order History shipment packages

Field Name ERP Table.Field (oe_pick_ticket) Commerce Table.Field (ShipmentPackage) Notes

Shipment #

pick_ticket_no

ShipmentID

 

Ship Via

carrier_id

Carrier

 

Tracking #

tracking_no

TrackingNumber

 

Freight Amount

freight_out

Freight

 

Package #

line_no

PackageNumber

 

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.

Field mapping: Invoice history header

Field Name ERP Table.Field (invoice_hdr) Commerce Table.Field (InvoiceHistory) Notes

Invoice #

invoice_no

InvoiceNumber

 

Invoice Date

invoice_date

InvoiceDate

 

Invoice Due Date

net_due_date

DueDate

 

Invoice Type

 

InvoiceType

Static value = Invoice

Open Invoice Flag

pain_in_full_flag

IsOpen

"N" = 1

All other values = 0

Customer #

customer_id

CustomerNumber

 

Ship-To #

ship_to_id

CustomerSequence

 

Customer PO #

po_no

CustomerPO

 

Terms

terms_id

Terms

 

Ship Code

carrier_name

ShipCode

 

Salesperson

salesrep_id

Salesperson

 

Subtotal

Calculated

ProductTotal

total_amount - other_charge_amount - tax_amount

Sales Tax

tax_amount

TaxAmount

 

Shipping

other_charge_amount

ShippingAndHandling

 

Invoice Total

total_amount

InvoiceTotal

 

Current Balance

calculated

CurrentBalance

total_amount - amount_paid

Billing Information: Company/Name

bill2_name

BTCompanyName

 

Billing Information: Address

bill2_address[1..2]

BTAddress1..2

 

Billing Information: City

bill2_city

BTCity

 

Billing Information: Country

bill2_country

BTCountry

 

Billing Information: State

bill2_state

BTState

 

Billing Information: Zip

bill2_postal-code

BTPostalCode

 

Ship-To Information: Company/Name

ship2_name

STCompanyName

 

Ship-To Information: Address

ship2_address[1..2]

STAddress1..2

 

Ship-To Information: City

ship2_city

STCity

 

Ship-To Information: State

ship2_state

STState

 

Ship-To Information : Zip

ship2_postal_code

STPostalCode

 

Ship-To Information : Country

ship2_country

STCountry

 

Field mapping: Invoice history detail

Field Name ERP Web Service Field (invoice_line) Commerce Table.Field (InvoiceHistoryLine) Notes

Invoice #

invoice_hdr.invoice_no

InvoiceHistoryId

concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")

Line Number

line_no

LineNumber

 

Release Number

 

ReleaseNumber

Static value = 0

Item #

item_id

ProductERPNumber

 

Item Description

item_desc

Description

 

Unit of Measure

unit_of_measure

UnitOfMeasure

 

Qty Invoiced

qty_shipped

QtyInvoiced

 

Unit Price

unit_price

UnitPrice

 

Extended Price

extended_price

LineTotal

 

Existing Orders

A/R aging balances API

Configured Commerceuses 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.

A/R aging balances (invoices)

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

Credit card processing

Order submit API

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

Field mapping: Order submit header

Field Name ERP Source (customer) Configured Commerce Destination (Customer) Notes
    <B2BSellerVersion> Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
Customer # CustomerNumber <CustomerCode>  
    <StoreName>  
Web Order # OrderNumber <WebReferenceNumber>  
    <Anonymous> Hard-coded to N.
Customer PO CustomerPo <PONumber>  
    <NotePadText>  
    <UseContractAddress> Hard-coded to False
    <FreightCode>  
    <ContactID>  
<CustomerShipTo> Contains ShipToIDs and ShipToAddresses>
Ship-To # CustomerSequence <ShipToID>  
<ShipToAddress> Contains Below Tags
Ship-To Name STCompanyName <ShipToCompanyName>  
Ship-To Address 1 STAddress1 <ShipToAddress1>  
Ship-To Address 2 STAddress2 <ShipToAddress2>  
Ship-to Address 3 STAddress3 <ShipToAddress3>  
Ship-to City STCity <ShipToCity>  
Ship-to State STState <ShipToState>  
Ship-to Postal Code STPostalCode <ShipToZip>  
Ship-to Country STCountry <ShipToCountry>  
</ShipToAddress>  
Ship-To Contact First Name STFirstName <ShiptoContactFirstName>  
Ship-To Contact Last Name STLastName <ShipToContactLastName>  
Ship-To Phone STPhone <ShipToPhone>  
Ship-To Email STEmail <ShipToEMail>  
Ship Via ShipVia.ERPShipCode <ShipToCarrierID>  
</CustomerShipTo>  
<CreditCard> This entire envelope is only required for credit card orders.
</CreditCard>  
<ListOfMerchandiseCredits> Contains Merchandise Credit Lines
<MerchandiseCredit> Contains MerchandiseCredit # and Amount
    <MerchandiseCreditNumber>  
    <Amount>  
</MerchandiseCredit>  
</ListofMerchandiseCredits>  
<ListOfLineItems> Contains Line Items
<LineItem> Contains Individual line item information.
ERP Part # EPRNumber <ItemID>  
Quantity Ordered QtyOrdered <OrderQuantity>  
Unit of Measure UnitOfMeasure <UnitName>  
Unit Sell Price NetUnitPrice <UnitPrice>  
Warehouse Warehouse <SourceLocation>  
    <NotepadText>  
</LineItem>  
</ListOfLineItems>  
<ListOfCoupons>  
</ListOfCoupons>  

Order submission

Tax calculation

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

Field mapping: Order submit header

Field Name ERP Source (customer) Configured Commerce Destination (Customer) Notes
    <B2BSellerVersion> Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
Customer # CustomerNumber <CustomerCode>  
    <StoreName>  
Web Order # OrderNumber <WebReferenceNumber>  
    <Anonymous> Hard-coded to N.
Customer PO CustomerPo <PONumber>  
    <NotePadText>  
  "False" <UseContractAddress> Hard-coded to False
    <FreightCode>  
    <ContactID>  
<CustomerShipTo> Contains ShipToIDs and ShipToAddresses>
Ship-To # CustomerSequence <ShipToID>  
<ShipToAddress> Contains Below Tags
Ship-To Name STCompanyName <ShipToCompanyName>  
Ship-To Address 1 STAddress1 <ShipToAddress1>  
Ship-To Address 2 STAddress2 <ShipToAddress2>  
Ship-to Address 3 STAddress3 <ShipToAddress3>  
Ship-to City STCity <ShipToCity>  
Ship-to State STState <ShipToState>  
Ship-to Postal Code STPostalCode <ShipToZip>  
Ship-to Country STCountry <ShipToCountry>  
</ShipToAddress>  
Ship-To Contact First Name STFirstName <ShiptoContactFirstName>  
Ship-To Contact Last Name STLastName <ShipToContactLastName>  
Ship-To Phone STPhone <ShipToPhone>  
Ship-To Email STEmail <ShipToEMail>  
Ship Via ShipVia.ERPShipCode <ShipToCarrierID>  
</CustomerShipTo>  
<CreditCard> This entire envelope is only required for credit card orders.
</CreditCard>  
<ListOfMerchandiseCredits> Contains Merchandise Credit Lines
<MerchandiseCredit> Contains MerchandiseCredit # and Amount
    <MerchandiseCreditNumber>  
    <Amount>  
</MerchandiseCredit>  
</ListofMerchandiseCredits>  
<ListOfLineItems> Contains Line Items
<LineItem> Contains Individual line item information.
ERP Part # EPRNumber <ItemID>  
Quantity Ordered QtyOrdered <OrderQuantity>  
Unit of Measure UnitOfMeasure <UnitName>  
Unit Sell Price NetUnitPrice <UnitPrice>  
Warehouse Warehouse <SourceLocation>  
    <NotepadText>  
</LineItem>  
</ListOfLineItems>  
<ListOfCoupons>  
</ListOfCoupons>  

Tax (order simulation)

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.

Field mapping: Pricing and availability

Field Name Commerce Table.Field ERP Web Service Field Notes
  N/A <B2BSellerVersion> Defaults to MajorVersion of 5, MinorVersion of 11, BuildNumber of 100.
Customer # CustomerNumber <CustomerCode>  
    <StoreName>  
    <LocationID>  
<ListOfItems> This contains some number of line items as children, each in item tags.
<Item> Information in this tag is for a single item.
Product # Product.ERPNumber <ItemID>  
Qty Ordered "1" <Quantity> We use this normally to get the default price ofr a quantity of 1.
Unit of Measure Product.UnitOfMeasure <UnitName>  
</Item> End of Item
</ListofItems> End of List

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

Field mapping: Customer (bill-to)

Field Name ERP Source (customer) Configured Commerce Destination (Customer) Notes

Customer #

customer_id

CustomerNumber

ERPNumber

 

Ship-To #

 

CustomerSequence

Static Value = Blank

Customer Name

customer_name

Company

 

Address Line 1-3

phys_address[1..3]

Address1..3

 

City

phys_city

City

 

State

phys_state

StateId

Lookup being used must exist in ISC

Country

phys_country

CountryId

Must match country abbreviation to be valid, uses lookup

Postal Code

phys_postal_code

PostalCode

 

Customer Type

 

CustomerType

Optional field

Email

email_address

Email

Must match proper email format or an error will be generated - default job ignores nulls and "DNE"

Phone 1

central_phone_number

Phone

 

Fax

 

Fax

 

Terms Code

terms_id

TermsCode

Should match a valid payment method;not enforced on refresh

Tax Code 1

 

TaxCode1

Static Value = Blank; Relying on calls to API to determine the tax amount in cart.

Tax Code 2

 

TaxCode2

Currency Code

currency_id

CurrencyId

Must match a valid currency code

Outside Sales Rep

salesrep_id

PrimarySalespersonId

Must match a valid sales rep

Inside Sales Rep

 

 

If desired, can be mapped to Salesperson instead of the outside sales rep. We only support a single, assigned sales rep to the account.

Credit Limit

credit_limit

CreditLimit

Not enforced as standard feature

Default Warehouse

preferred_location_id

DefaultWarehouse

Sets the customer default warehouse.

Active

delete_flag

IsActive

Y = 0

All others = 1

Field mapping: Customer (ship-to)

Field Name ERP Source (customer) Configured Commerce Destination (Customer) Notes

Customer #

customer_id

CustomerNumber

ERPNumber

 

Ship-To #

 

CustomerSequence

 

Customer Name

customer_name

Company

 

Address Line 1-3

phys_address[1..3]

Address1..3

 

City

phys_city

City

 

State

phys_state

StateId

Lookup must match a valid state for the country supplied

Country

phys_country

CountryId

Lookup must match a valid country.

Postal Code

phys_postal_code

PostalCode

 

Customer Type

arsc.custtype

CustomerType

 

Email

email_address

Email

Validated against standard email formats and will error if incorrect.

Phone 1

central_phone_number

Phone

 

Fax

 

Fax

 

Tax Code 1

 

TaxCode1

Static Value = Blank; Relying on calls to API to determine the tax amount in cart.

Tax Code 2

 

TaxCode2

Currency Code

currency_id

CurrencyId

Must match valid currency code

Outside Sales Rep

salesrep_id

PrimarySalespersonId

Must match valid sales rep

Inside Sales Rep

 

PrimarySalespersonId

If desired, can be mapped to Salesperson instead of the outside sales rep. We only support a single, assigned sales rep to the account.

Credit Limit

credit_limit

CreditLimit

Typically enforced only at customer level but not a standard function

Active

delete_flag

IsActive

Y = 0

All others = 1

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.

Field mapping: Customer product

Field Name ERP Source Configured Commerce Destination (CustomerProduct) Notes

ERP Part #

inv_xref.item_id

ProductId

Lookup to Product table

Customer #

customer.customer_id

CustomerId

Only pull records WHERE icsec.shipto is blank (bill-to level). Lookup to customer table.

Customer Part #

inv_xref.their_item_id

Name

Customers product number

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

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

Field mapping: Product refresh product

Field Name ERP Source (inv_mast) Configured Commerce Destination (Product) Notes

ERP Item #

item_id

Name

ERPNumber

This is the primary natural key to the table

Item Title &

Item Description

extended_desc

ShortDescription

ERPDescription

Short description will not be overwritten it is expected to be maintained in the application or from a PIM

URL Segment

item_id

URLSegment

Appending the item number to ensure uniqueness

Tax Code/Class

item_sales_tax_class

TaxCode1

 

Unit of Measure

base_unit

UnitOfMeasure

This is the stocking unit of measure to properly handle conversions

Unit Weight

net_weight

ShippingWeight

 

UNSPSC

unspsc_code

UNSPSC

Not mapped: Used for punchout and needs to be enabled in the Application Dictionary

UPC Code

upc_or_ean_id

UPCCode

Not mapped: Used for punchout and needs to be enabled in the Application Dictionary

Base Unit Price

price1

BasicListPrice

 

Tax Class

item_sales_tax_class

TaxCode1

 

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.

Field mapping: Product cross-sells

Field Name ERP Source (imxrf) Configured Commerce Destination (ProductRelatedProduct) Notes
Primary Item mrorit ProductID Lookup to Product table
Related Item mrrpit RelatedProductID Child collection to be populated

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.

Field mapping: Product alternate unit of measure

Field Name ERP Source Configured Commerce Destination (ProductUnitOfMeasure) Notes

Product #

inv_mast.item_id

Product.ERPNumber

Lookup to Product

Unit of Measure

item_uom.unit_of_measure

UnitOfMeasure

 

Conversion Factor

item_uom.unit_size

QtyPerBaseUnitOfMeasure

 

Product 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 ISC. It is also required if using the Request for Quote functionality in ISC.

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

Field mapping: Salesperson refresh

Field Name ERP Source (contacts) Configured Commerce Destination (Salesperson) Notes

Salesperson Number

Contact_salesrep.salesrep_id

SalespersonNumber

Select only records from contact_salesrep where delete_flag = N

Name

First_name + + last_name

Name

 

Email Address

email_address

Email

 

Title

title

Title

 

Phone number

direct_phone

Phone1

 

Manager Number

sales_manager_id

SalesManager

If provided, we can track the manager of the sales rep which allows access to the sales reps accounts and quotes

Payment methods

The payment terms refresh populates the payment methods table in ISC, 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 Configured Commerce 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.

Field mapping: Payment terms refresh

Field Name ERP Source (sasta) Configured Commerce Destination (PaymentMethod) Notes

Company

Cono

N/A

Used for filtering the correct records only.

Obtain value from the system setting

Record Type

Codeiden = T

 

Using code T to pull in the terms codes from the system admin table

Terms Code

Codeval

Name

 

Description

descrip

Description

 

COD/Cash

n/a

IsCreditCard

This terms code requires payment by credit card on the site

Active Indicator

 

ActivateOn

Not overwritten set on initial setup to current date