Reference for connectors for Prophet 21
Order history
ISC retrieves Order history data from Prophet 21 via a direct call to the ERP database. The data is stored within the B2B 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 B2B are immediately added to the B2B 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 B2B 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 B2B 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 B2B 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
ISC 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.
A/R aging balances (invoices)
ISC 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
ISC 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) | ISC 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) | ISC 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
ISC 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: B2B retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. B2B must use Ignore, since the data will be retrieved in two separate sweeps.
Field mapping: Customer (bill-to)
| Field Name | ERP Source (customer) | ISC 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_address |
|
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) | ISC 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_address |
|
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 B2B via a direct call to the Prophet 21 database.
Deletion Strategy: ISC uses the Delete option to physically remove records that are no longer valid. B2B also uses Delta Datasets.
Field mapping: Customer product
| Field Name | ERP Source | ISC 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
ISC 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) | ISC 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
ISC 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) | ISC 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
ISC 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: ISC uses the Delete option for this data.
Field mapping: Product alternate unit of measure
| Field Name | ERP Source | ISC 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) | ISC 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 |
|
|
|
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 B2B 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) | ISC 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 |
Need help?