Prophet 21 connectors reference

32 min

Important

Optimizely's ERP connectors do not support .NET 8. Work with your partner or development team to take the open-source code and modify it to work with .NET 8 for your projects.

Note

Optimizely lets partners and the implementation community build ERP connectors to increase the number of ERP connectors and enhancements available to customers. Optimizely offers existing ERP connectors in their current state, and partners can access the connector code for future enhancements.

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 - DescriptionY - CompletedAny 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)
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)
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" = 1All 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 Commerce 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)

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	CustomerNumberERPNumber
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 = 0All others = 1

Field mapping: Customer (ship-to)

Field Name	ERP Source (customer)	Configured Commerce Destination (Customer)	Notes
Customer #	customer_id	CustomerNumberERPNumber
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 = 0All 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	NameERPNumber	This is the primary natural key to the table
Item Title &Item Description	extended_desc	ShortDescriptionERPDescription	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