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.
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 SX.e 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 SX.e order records analyzed, the integration process uses a lookback period in the query. This query is performed against the SX.e 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 the SX.e connector order submission uses the real-time API, the ERP order # should also be present.
Implementation Note - Performance: Because many SX.e customers have very large tables and there is not an index on the transdt field, the system must perform a table scan, which can greatly increase refresh run time. A setting exists to capture the starting order number so that the refreshes will look back by date but limit the search to orders beginning at a specific number. This improves performance and this value can be updated periodically (that is quarterly) if performance is an issue. This applies to order history refresh, invoice history, and shipment history refreshes.
Implementation Note Lookback Days: Optimizely's strategy is to limit the amount of data being reviewed and transmitted to Configured Commerce for large history files. Configured Commerce only looks for records that have changed within a relatively short period of time, with a default of 5 days to cover any missed refreshes, weekends and holidays. The number can be modified based on implementation preferences. It is coded directly into the WHERE clauses of the history refreshes.
Record Selection: Only records for the currnet company, stagecd between 1 and 5, selected transdt withing the lookback days, and for transtypes not BL, QU, ST, FO, or BR.
Deletion Strategy: There is a special strategy for Order History, as it is not ideal to do a full snapshot of the data. However, we need to catch any changes to, say, order lines that were deleted. A special option to Delete Children is used, with the delete action set to Delete. Any order that is processed clears and reloads the order line data to history.
Field mapping: Order history header
| Field Name | ERP Table.Field (oeeh) | Commerce Table.Field (OrderHistory) | Notes |
|---|---|---|---|
| ERP Order # |
Orderno ordersuf |
ERPOrderNumber | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Order Status | stagecd | Status |
Value - Description 1 - Ordered 2 - Picked 3 - Shipped 4 - Invoiced 5 - Paid
Records not retrieved into Configured Commerce include: 0 - Quoted & 9 - Do Not Select |
| Order Date | enterdt | OrderDate | |
| Order Type/ Transaction Type | transtype |
Used to filter out records from being retrieved into Configured Commerce. All transtypes are retrived into Configured Commerce except: BL, QU, ST, FO, BR. Sample transtype retrieved into Configured Commerce: SO, CS, DO, CR, RM. |
|
| Customer # | custno | CustomerNumber | |
| Ship-To # | shipto | CustomerSequence | |
| Customer PO # | custpo | CustomerPO | |
| Terms Code | termtype | Terms | |
| Ship Via | shipviaty | ShipCode | |
| Requested Ship Date | reqshipdt | RequestedDeliveryDate | Note that these are not exactly the same |
| Salesperson | slsrepout | Salesperson | |
| Billing Information: Company/Name | arsc.name | BTCompanyName |
JOIN arsc to oeeh: arsc.cono = oeeh.cono arsc.custno = oeeh.custno |
| Billing Information: Address | arsc.addr[1..2] | BTAddress1..2 | |
| Billing Information: City | arsc.city | BTCity | |
| Billing Information: State | arsc.state | BTState | |
| Billing Information : Zip | arsc.zipcode | BTPostalCode | |
| Billing Information Country | Arsc.countrycd | BTCountry | |
| Shipping Information: Company/Name | shiptonm | STCompanyName | |
| Shipping Information: Address | shiptoaddr[1..2] | STAddress1..2 | |
| Shipping Information: City | shiptocity | STCity | |
| Shipping Information: State | shiptost | STState | |
| Shipping Information: Postal Code | shiptozip | STPostalCode | |
| Shipping Information: Country | Countrycd | STCountry | |
| Subtotal | totlineamt OR totlineord | ProductTotal | IF stagecd <= 1 THEN oeeh.totlineord ELSE oeeh.totlineamt Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) |
| Order Discount | specdiscamt | OrderDiscountAmount | |
| Shipping | addon.addonnet | ShippingCharges | SUM of addon.addonnet WHERE addon.addonno = 02 Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) JOIN addon TO oeeh: addon.cono = oeeh.cono addon.ordertype = "oe" addon.orderno = oeeh.orderno addon.ordesuf = oeeh.ordersuf addon.addonno = 02 |
| Misc. Charges | addon.addonnet | OtherCharges | SUM of addon.addonnet WHERE addon.addonno <> 02 Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) JOIN addon TO oeeh: addon.cono = oeeh.cono addon.ordertype = "oe" addon.orderno = oeeh.orderno addon.ordesuf = oeeh.ordersuf addon.addonno <> 02 |
| Sales Tax | taxamt[1-4] | TaxAmount |
oeeh.taxamt[1] + oeeh.taxamt[2] + oeeh.taxamt[3] + oeeh.taxamt[4] Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) |
| Order Total | Calculated | OrderTotal | SUM(Subtotal + Order Discount + Shipping + Misc Charges + Sales Tax) |
| Order Notes | notes.noteln[1 16] | Notes |
There are up to 16 lines of notes per page and up to 99 pages. If notes.noteln[x] is blank, do not display.
JOIN notes TO oeeh: notes.cono = oeeh.cono notes.notestype = "o" notes.primarykey = string(oeeh.orderno) notes.secondarykey = blank OR string(oeeh.ordersuf) (notes.printfl2 = yes OR notes.printfl5 = yes) |
Field mapping: Order history detail
| Field Name | ERP Table.Field (oeel) | Commerce Table.Field (OrderHistoryLine) | Notes |
|---|---|---|---|
| ERP Order # | orderno ordersuf |
OrderHistoryId | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Line Number | lineno | LineNumber | |
| Customer # | custno | CustomerNumber | |
| Ship-To # | shipto | CustomerSequence | |
| Line Type | LineType | Static Value = 'Product' | |
| Item # | shipprod | ProductERPNumber | |
| Item Description | descrip[1-2] OR icsp.proddesc & icsp.proddesc2 | Description | IF oeel.specnstype <> THEN (l=lost, s=special order, n=nonstock) concatenate(oeel.proddesc, " ", oeel.prodesc2) ELSE concatenate(icsp.descrip[1], " ", icsp.descrip[2]) |
| Warehouse | Whse | Warehouse | |
| Unit of Measure | Unit | UnitOfMeasure | |
| QTY Ordered | Qtyord | QtyOrdered | |
| QTY Shipped | Qtyship | QtyShipped | IF oeeh.stagecd <= 1 THEN blank or zero ELSE oeel.qtyship |
| Inventory Qty Ordered | StkQtyOrd | InventoryQtyOrdered | |
| Inventory Qty Shipped | Stkqtyship | InventoryQtyShipped | |
| Unit Price | Price | UnitNetPrice | |
| Extended Price | netord OR netamt | LineTotal | IF oeeh.stagecd <= 1 THEN oeel.netord ELSE oeel.netamt |
| Ship Date | oeeh.shipdt | LastShipDate | |
| Line Notes | com.noteln | Notes |
JOIN com TO oeel oeel.cono = com.cono AND oeel.orderno = com.orderno AND oeel.ordersuf = com.ordersuf AND oeel.lineno = com.lineno AND com.comtype = oe AND (com.printfl = yes OR com.printfl2 = yes) |
Shipments
ISC's shipment refresh assumes some sort of shipping interface is used within SX.e so that OEEHP records are being generated with tracking information. This refresh typically runs once per day and uses a lookback period from which to run the query.
To limit the number of SX.e order records analyzed, the integration process uses a lookback period in the query. This query is used against only the SX.e order header transaction date, since we do not expect OEEH/OEEL records to change once invoiced.
Refer to the Implementation Notes in Order History Refresh for additional information. (Anchor link)
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 (oeehp) | Commerce Table.Field (Shipment) | Notes |
|---|---|---|---|
| ERP Order Number | orderno ordersuf |
ERPOrderNumber | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Shipment # | orderno ordersuf |
ShipmentNumber | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Shipment Date | transdt | ShipmentDate |
Field mapping: Order history shipment packages
| Field Name | ERP Table.Field (oeehp) | Commerce Table.Field (ShipmentPackage) | Notes |
|---|---|---|---|
| Shipment # | orderno ordersuf |
ShipmentID | concatenation of (orderno) + "-" + (ordersuf,"99") |
| Ship Via | shipviaty | Carrier | |
| Tracking # | trackerno | TrackingNumber | |
| Freight Amount | freightamt | Freight | |
| Package # | pkgno | PackageNumber |
Invoices
Invoice data is retrieved from SX.e 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 SX.e order records analyzed, the integration process uses a lookback period in the query. This query is used against only the SX.e order header transaction date, since we do not expect OEEH/OEEL records to change once invoiced.
Refer to the Implementation Notes in Order History Refresh for additional information.
The filter for these records only includes the specified company; stagecd 4 or 5; excludes transtypes BL, QU, ST, FO, and BR; enterdt not null; and transdt within the specified timeframe window.
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 (oeeh) | Commerce Table.Field (InvoiceHistory) | Notes |
|---|---|---|---|
| Invoice # | orderno + ordersuf | InvoiceNumber | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Invoice Date | invoicedt | InvoiceDate | |
| Invoice Due Date | Aret.duedt | DueDate | Join to ARET on cono, custno, invdt, invno, invsuf where transcd = 0 |
| Invoice Type | InvoiceType | Static value = Invoice | |
| Invoice Status | stagecd | Status | Only stagecd 4 and 5 will be pulled into invoice history |
| Open Invoice Flag | IsOpen | If stagecd = 5,set = 1, else 0 | |
| Customer # | custno | CustomerNumber | |
| Ship-To # | shipto | CustomerSequence | |
| Currency | Currencyty | CurrencyCode | |
| Customer PO # | custpo | CustomerPO | |
| Terms | termtype | Terms | |
| Ship Code | Shipviaty | ShipCode | |
| Salesperson | Slsrepout | Salesperson | |
| Subtotal | Totlineamt | ProductTotal | Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) |
| Sales Tax | taxamt[1-4] | TaxAmount | oeeh.taxamt[1] + oeeh.taxamt[2] + oeeh.taxamt[3] + oeeh.taxamt[4] Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) |
| Shipping | addon.addonnet | ShippingAndHandling | SUM of addon.addonnet WHERE addon.addonno = 02 Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) JOIN addon TO oeeh: addon.cono = oeeh.cono addon.ordertype = "oe" addon.orderno = oeeh.orderno addon.ordesuf = oeeh.ordersuf addon.addonno = 02 |
| Discount Amount | specdiscamt | DiscountAmount | |
| Misc Charges | addon.addonnet | OtherCharges | SUM of addon.addonnet WHERE addon.addonno <> 02 Special Instructions: [result field] * (IF oeeh.transtype = "rm" THEN -1 ELSE 1) JOIN addon TO oeeh: addon.cono = oeeh.cono addon.ordertype = "oe" addon.orderno = oeeh.orderno addon.ordesuf = oeeh.ordersuf addon.addonno <> 02 |
| Invoice Total | Calculated | InvoiceTotal | SUM(Subtotal + Order Discount + Shipping + Misc Charges + Sales Tax) |
| Current Balance | Derived from ARET | CurrentBalance | InvoiceTotal sum(aret.amount) joined on cono, custno, invdt, invno, invsuf where transcd <> 0 and <> 11 |
| Billing Information: Company/Name | arsc.name | BTCompanyName | JOIN arsc to oeeh: arsc.cono = oeeh.cono arsc.custno =oeeh.custno |
| Billing Information: Address | arsc.addr[1] arsc.addr[2] |
BTAddress1..2 | |
| Billing Information: City | arsc.city | BTCity | |
| Billing Information: Country | arsc.countrycd | BTCountry | |
| Billing Information: State | arsc.state | BTState | |
| Billing Information: Zip | arsc.zipcode | BTPostalCode | |
| Billing Information: Country | arsc.countrycd | BTCountry | |
| Ship-To Information: Company/Name | shiptonm | STCompanyName | JOIN arsc to oeeh: arsc.cono = oeeh.cono arsc.custno =oeeh.custno |
| Ship-To Information: Address | shiptoaddr[1..2] | STAddress1..2 | |
| Ship-To Information: City | shiptocity | STCity | |
| Ship-To Information: State | shiptost | STState | |
| Ship-To Information : Zip | shiptozip | STPostalCode | |
| Ship-To Information : Country | Shiptocountrycd | STCountry |
Field mapping: Invoice history detail
| Field Name | ERP Web Service Field (oeel) | Commerce Table.Field (InvoiceHistoryLine) | Notes |
|---|---|---|---|
| Invoice # | orderno + ordersuf | InvoiceHistoryId | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Line Number | lineno | LineNumber | |
| Release Number | ReleaseNumber | Static value = 0 | |
| Line Type | LineType | Static Value = 'Product' | |
| ERP Order # | orderno + ordersuf | ERPOrderNumber | concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
| Item # | shipprod | ProductERPNumber | |
| Item Description | icsp.descrip[1-2] OR oeel.proddesc & oeel.proddesc2 | Description | IF oeel.specnstype = "n" THEN concatenate(oeel.proddesc, " ", oeel.prodesc2) ELSE concatenate(icsp.descrip[1], " ", icsp.descrip[2]) |
| Unit of Measure | unit | UnitOfMeasure | |
| Warehouse | Whse | Warerhouse | |
| Qty Invoiced | qtyord | QtyInvoiced | |
| Unit Price | price | UnitPrice | |
| Extended Price | netamt | 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 sxapiARGetCustomerBalanceV2.
Field mapping: A/R aging
| Field Name | Commerce Table.Field (Current Session Context) | ERP Web Service Field | Notes |
|---|---|---|---|
| Customer # | BillTo.ERPNumber | Parameter 1: Customer # | |
| Ship To | Blank | Parameter 2 Ship To | We will always retrieve the customer level information but, if theERPSequence is provided, the balance for that specific ship-to would be returned. |
| Balance Type | BalanceType=t | Parameter 3 Balance Type | T provides total exposure across ARSC and ARSS |
A/R aging balances (invoices)
Configured Commerce calls the payment gateway (typically CenPOS for SX.e) directly to authorize a user's credit card. The authorization information is passed into SX.e via the order submission process using an authorization token returned by the payment gateway. This information does not apply to SX.e v6.
Credit card processing
Order submit API
Configured Commerce will submit orders to SX.e via the API. Typical order submission data will be included: bill-to information, ship-to information, and line item information. If a credit card was used for the order, Configured Commerce will also submit the authorization token information.
A new customer is created if the user self-registers or adds a new ship-to address.
If a 1-time order address is submitted, it will be placed into the Order Header (OEEH).
Note that SX.e's order submit assumes that only authorizations are being committed for credit card orders through CenPOS. Our recommendation is to not run credit card transactions as Sales in Configured Commerce to prevent downstream issues with the ERP.
The API sxapiSFOEOrderTotLoadV4 is used for this function. The data below will show the net differences for the actual order submission.
The standard Configured connector mapping for API calls is hard-coded but implemented with pipelines to allow the implementer to extend the integration to incorporate additional mapping information for other fields or adjust the standard mappings. The following shows the default approach to mapping the API call and only calls out the changes from the tax calculation.
Note that SX.e's order submit assumes that only authorizations are being committed for credit card orders through CenPOS. Our recommendation is to not run credit card transactions as Sales in Configured Commerce to prevent downstream issues with the ERP.
The standard SX.e connector code will attempt to override the price if there is a line-level promotion. This only works with the following options configured within SX.e:
- If the setting ediprcfl is turned on in SX.e and ARSC/ARSS tables allow for it.
- Create/update a setting in SASBR for category SXAPI,
- Rule: Override Price and value: Yes.
- Note that this can be used to set several parameters such as various defaults, debug directory, and so on.
The standard SX.e connector code does not set the flag for non-stock items in the order submit. If customizing the order submit to submit non-stock items, flags need to be set at the bill-to and ship-to levels:
These fields are "edinsprodfl" in both ARSC and ARSS
Field mapping: Order submit header
| Field Name | Commerce Table.Field (CustomerOrder) | ERP Web Service Field (inputHeaderData) | Notes |
|---|---|---|---|
| Web Transaction Type | Webtransactiontype | LSF used for order submission | |
| Order Type | Order Type | O for order | |
| Requested Ship Date | RequestedDeliveryDate | Reqshipdate | This could potentially use the RequestedShipDate field from Configured Commerce but that field is currently disabled and delivery date is exposed as standard |
| Web Order # | OrderNumber |
Defined by setting IntegrationConnector_SXeWebOrderNumberField Headerextradata.fieldname = iondata Headerextradata.fieldvalue=OrderNumber |
This is for reference purposes only and is optional if the setting is configured, the target field (which would normally be refer or one of the user fields) will be populated with the web order # for reference purposes. Currently, the only one that can be set is the Refer field. |
| Order-Level Discount Amount | DiscountAmount |
Headerextradata.fieldname = discountamt Headerextradata.fieldvalue = <discountamount> |
This field is used for order level promotion discounts. If wanting to use a percentage, use the fieldname = discountpct |
| Miscellaneous Charge Amount | OtherCharges | N/A |
We do not expect to have any miscellaneous charges See Freight for populating addons |
| Tax Amount | TaxAmount | Taxamount | |
| Miscellaneous Charge Addon Field | Addon number 10 |
Headerextradata.fieldname=addon Headerextradata.value = addonno=1<tab>addonamt=XXX<tab>addontype=$ (or %) generic way to create addons Note that we expect SX to be configured to match this assignment |
|
| Freight Charge Amount | ShippingCharges | Addon number 2 | Note that we expect SX to be configured to match this assignment |
| Handling Charge Amount | HandlingCharges | Addon number 3 | Note that we expect SX to be configured to match this assignment |
| Terms Code | PaymentMethod.Name |
Headerextradata.fieldname=termstype Headerextradata.fieldvalue=<paymentmethod.name> |
|
| Total Order Amount | OrderTotal | n/a | No equivalent field in SX |
| Order Notes | Notes | See notes | Send in the same as line notes only set the itemnumber to / |
| CENPOS C/CARD INFO (all data from CreditCardTransaction unless otherwise noted) | |||
| General information | ALL |
All credit card fields are sent un using the InFieldValue table: Infieldvalue.level = SFOEOrderTotLoadV4 Infieldvalue.lineNumber = 0 (header) Infieldvalue.sequenceNumber = 0 Infieldvalue.fieldname = <see below> Infieldvalue.fieldvalue = <see below> |
|
| MerchantId | Setting by website |
Fieldname = MerchantId Fieldvalue = <setting value> |
Must be returned in order to read the SAST record for additional information about the card data |
| CenPos indicator |
Fieldname = PaymentType Fieldvalue = cenpos |
||
| Masked Credit Card # | CreditCardNumber |
Fieldname =-CardNumber fieldValue = masked credit card # |
Optional but helps identify to the customer if questions are asked which card was used |
| Card Type | CardType |
Fieldname = ProcPaymentType fieldValue = <card type> |
Valid values: AMEX, VISA, MASTERCARD, DISCOVER |
| Authorization Token | Token1 |
Fieldname = Token fieldValue = <> |
|
| Authorization Amount | Amount |
Fieldname = AuthAmt fieldValue = <> |
|
| Authorization Number | AuthCode |
Fieldname = AuthNumber fieldValue = <> |
|
| Card Reference Number |
Fieldname = ReferenceNumber fieldValue = <> |
Currently will not populate | |
Field mapping: Order submit detail/line
| Field Name | Commerce Table.Field (OrderLine) | ERP Web Service Field (inputLineData) | Notes |
|---|---|---|---|
| Line number | Line | SequenceNumber | |
| Line Type | LineItemType |
I for Item C for comment followed by X (do not print on documents), P (print on pick ticket only), I (print on invoice only) or leave blank to print on all documents. We will use the cp by default to print on pick ticket only. |
|
| Unit Sell Price | UnitNetPrice | Actualsellprice |
Represents the actual amount of the product if non-stock. This means that,basically, we do not get to set the price UNLESS we set a field in inheaderextra where fieldname = donotrecalculateprice with a value of yes in which case the unit sell price we send in will be used. The desired behavior is to check the order |
| Order Line Notes | Notes | ItemDescription1 | When sending in comments, send in as a separate line with the item number set to & (per Ron Stephen's example, not the documentation) and set the lineitemtype to cp, and sequencenumber to an increment for each note (that is order note would be 1) |
Order submission
Tax calculation
As part of the standard connector for SX.e, there is a Tax Calculator which makes an API call to SX.e to calculate and return the tax amount to Configured Commerce on the Cart (if configured) and Checkout screens. If another tax calculator, such as Avalara, is used, then this Order Simulate function will be disabled. When using another tax calculator, be sure to review the Product & Customer refresh to ensure the correct data is being pulled into those records to pass into the tax service.
The API sxapiSFOEOrderTotLoadV4 will be used for this function.
It is expected that 1-time addresses, if allowed, will be able to have tax calculated internally within SX.e without additional information provided. If taxes are critical for the order to be submitted but the tax jurisdiction cannot reliably be determined, Optimizely suggests self-registration, new ship-to addresses, and 1-time addresses should all be disabled.
Field mapping: Tax calculation
| Field Name | Commerce Table.Field (CustomerOrder) | ERP Web Service Field (inputHeaderData) | Notes |
|---|---|---|---|
|
Order Header Table |
|||
|
Customer # |
CustomerNumber |
<customerID> |
|
|
Warehouse |
Warehouse |
<warehouseId> |
|
|
Customer PO |
CustomerPo |
<poNumber> |
|
|
Web Transaction Type |
|
Webtransactiontype |
TSF used for calculation only |
|
Order Number |
OrderNumber |
<ordNumber> |
|
|
Bill-To Contact |
BTFirstName + + BTLastName |
<billToContact> |
|
|
Bill-To Name |
BTCompanyName |
<customerName> |
|
|
Bill-To Address Line 1 |
BTAddress1 |
<customerAddress1> |
|
|
Bill-To Address Line 2 |
BTAddress2 |
<customerAddress2> |
|
|
Bill-To Address Line 3 |
BTAddress3 |
<customerAddress3> |
|
|
Bill-To Address Line 4 |
BTAddress4 |
<customerAddress4> |
|
|
Bill-To City |
BTCity |
<billToCity> |
|
|
Bill-To State |
BTState |
<billToState> |
|
|
Bill-To Postal Code |
BTPostalCode |
<billToZip> |
|
|
Bill-To Country |
BTCountry |
<customerCountry> |
|
|
Bill-To Phone |
BTPhone |
<billToPhone> |
|
|
Ship-To # |
CustomerSequence |
<shipToNumber> |
|
|
Ship-To Contact |
STFirstName + + STLastName |
<shipToContact> |
|
|
Ship-To Name |
STCompanyName |
<shipToName> |
|
|
Ship-To Address Line 1 |
STAddress1 |
<shipToAddress1> |
|
|
Ship-To Address Line 2 |
STAddress2 |
<shipToAddress2> |
|
|
Ship-To Address Line 3 |
STAddress3 |
<shipToAddress3> |
|
|
Ship-To Address Line 4 |
STAddress4 |
<shipToAddress4> |
|
|
Ship-To City |
STCity |
<shipToCity> |
|
|
Ship-To State |
STState |
<shipToState> |
|
|
Ship-To Postal Code |
STPostalCode |
<shipToZip> |
|
|
Ship-To Country |
STCountry |
<shipToCountry> |
|
|
Ship-To Phone |
STPhone |
<shipToPhone> |
|
|
Ship Via |
ShipVia.ERPShipCode |
<carrierCode> |
|
|
Requested Ship Date |
RequestedShipDate |
<reqShipDate> |
|
|
Transaction Type |
|
<webTransactionType> |
Static Value = TSF (that is Order Total) Results in order not being submitted to SX.e |
|
Order Lines Table |
|||
| Field Name | Commerce Table.Field (OrderLine) | ERP Web Service Field (inputLineData) | Notes |
|
ERP Part # |
ErpNumber |
<itemNumber> |
|
|
Quantity Ordered |
QtyOrdered |
<orderQty> |
|
| Field Name | Commerce Table.Field (OrderLine) | ERP Web Service Field (inputLineData) | Notes |
|
Unit Of Measure |
UnitOfMeasure |
<unitOfMeasure> |
|
|
Warehouse |
Warehouse |
<warehouseId> |
|
|
Line Type |
I |
<lineItemType> |
|
|
Item Description |
Description |
<itemDesc1> |
|
|
Unit Sell Price |
NetUnitPrice |
<actualSellPrice> |
|
|
Unit List Price |
UnitListPrice |
<listPrice> |
|
|
Regular Unit Price |
UnitRegularPrice |
<cost> |
This represents the customer's normal unit price before promotional discounts |
|
Order Additional Info Table |
|||
| Field Name | Commerce Table.Field | ERP Web Service Field (headerExtra) | Notes |
|
Shipping Amount |
|
<fieldname> = addon |
|
|
Shipping Amount |
CustomerOrder.ShippingCharges + CustomerOrder.HandlingCharges |
<fieldValue> = 2<tab>addonamt=<shipping charges><tab>addontype=$ |
Must be formatted in this way addon 2 =Freight Out so final might look like 2<t>addonamt=50<t>addontype=$ |
|
|
|
|
|
Tax (order simulation)
Pricing API
Configured Commerce retrieves pricing from SX.e via real-time calls, so no refresh is needed. The API used for this function is sxapiOEPricingMultpleV4.
Note that Configured Commerce does not retrieve price breaks from this call - OEPricingMultipleV4 does not return enough information to calculate pricing based on the price break information returned. However, if price-break-based pricing is set up inside of SX.e, Configured Commerce sends in the correct quantity values for the pricing call and will retrieve the correct pricing for the price tier in the cart.
Field mapping: Pricing and availability
| Field Name | ERP Table.Field | Commerce Table.Field (ProdDataPrcAvail) | Notes |
|---|---|---|---|
| Transaction # | 1 | Parameter 1: Transactions ID Number | |
| Request Identification | Company Number to 4 digits plus Customer.ERPNumber formatted to 12 digits | Parameter 2: Request ID # | Formatted with leading zeros |
| Order Number | Blank | Parameter 3: Order # | Since there wil be no order #, leave empty |
| Ship To | Customer.ERPSequence | Parameter 4: Ship To | |
| Warehouse | Warehouse.Name | Parameter 5: Warehouse | The warehouse for which pricing is calculated. If blank, will return all warehouses. |
| Warehouse | Warehouse.Name | <origWhse> | |
| Qty Ordered | 1 | <qtyOrd> | We use this normally to get the default price for a qty of 1 |
| Unit of Measure | Product.UnitOfMeasure | <unit> | |
| <calcPriceTy> | |||
| Product # | Product.ERPNumber | <Prod> | |
| Unused | <itemDetail> | ||
| Unused | <calcPriceFl> | ||
| Unused | <availabilityWhse> | ||
| Unused | <altWhse> | ||
| Unused | <netAvail> | ||
| Unused | <unitConv> |
Pricing tables
Customer
Configured Commerce retrieves both Customer (ARSC) and Ship-To (ARSS) information into a common customer table via the Customer refresh.
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. Delta Dataset option will be disabled.
Field mapping: Customer (bill-to)
| Field Name | ERP Source (arsc) | Configured Commerce Destination (Customer) | Notes |
|---|---|---|---|
| Company # | cono | N/A |
Used for filtering the correct records only. Company will be incorporated into the query directly. |
| Customer # | custno |
CustomerNumber ERPNumber |
|
| Ship-To # | CustomerSequence | Static Value = Blank | |
| Customer Name | name | Company | |
| Address Line 1-3 | addr[1..3] | Address1..3 | |
| City | city | City | |
| State | state | StateId | Lookup being used must exist in ISC |
| Country | countrycd | CountryId | Must match country abbreviation to be valid, uses lookup |
| Postal Code | zipcd | PostalCode | |
| Customer Type | custtype | CustomerType | Optional field |
| Must match proper email format or an error will be generated | |||
| Phone 1 | phoneno | Phone | |
| Fax | faxphoneno | Fax | |
| Terms Code | termstype | 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 | ||
| Price Code | pricetype | PriceCode | Not used when real-time pricing implemented |
| Currency Code | currencyty | CurrencyId | Must match a valid currency code |
| Warehouse | whse | DefaultWarehouseId | Must match a valid warehouse |
| Outside Sales Rep | slsrepout | PrimarySalespersonId | Must match a valid sales rep |
| Optimizely Sales Rep | Slsrepin | If desired, can be mapped to Salesperson instead of the outside sales rep. We only support a single, assigned sales rep to the account. | |
| Default Ship Via | shipviaty | ShipCode | Should match a valid Carrier/Service code by the ShipCode field not enforced but required to map properly when setting the default carrier/service in the checkout screen |
| Alternate Pricing Customer | pdcustno | PricingCustomerId | Optional represents the customer number to be used for pricing. Since we use real-time pricing, this will be automatic and is not necessary for integration. |
| Bank Code | bankno | BankCode | |
| Price Level | pricecd | Not required using real-time pricing | |
| Line Discount Level | disccd | Not required using real-time pricing | |
| Credit Limit | credlim | CreditLimit | Not enforced as standard feature |
| Active | statustype | IsActive |
arsc.statustype = A (Active) use 1 for query (Boolean) arsc.statustype = I (Inactive) |
Field mapping: Customer (ship-to)
| Field Name | ERP Table.Field | Commerce Table.Field | Notes |
|---|---|---|---|
| Company # | arss.cono | N/A |
Used for filtering the correct records only. Value called out directly in query. |
| Customer # | arss.custno |
CustomerNumber ERPNumber |
|
| Ship-To # | arss.shipto | CustomerSequence | |
| Customer Name | arss.name | Company | |
| Address Line 1-3 | arss.addr[1..3] | Address1..3 | |
| City | arss.city | City | |
| State | arss.state | StateId | Lookup must match a valid state for the country supplied |
| Country | arss.countrycd | CountryId | Lookup must match a valid country. The standard code will default US as the country for any entries without a country supplied this can be changed in the query to match the correct base country |
| Postal Code | arss.zipcd | PostalCode | |
| Customer Type | arsc.custtype | CustomerType | |
| arss.email | Validated against standard email formats and will error if incorrect. | ||
| Phone 1 | arss.phoneno | Phone | |
| Fax | arss.faxphoneno | Fax | |
| Terms Code | arss.termstype | TermsCode | Mapped like arsc but normally the BillTo's terms is all that's used |
| Tax Code 1 | TaxCode1 | Static Value = Blank; Relying on calls to API to determine the tax amount in cart. | |
| Tax Code 2 | TaxCode2 | ||
| Price Code | arss.pricetype | PriceCode | Not required when real-time pricing used |
| Currency Code | arsc.currencyty | CurrencyId | Must match valid currency code |
| Warehouse | arss.whse | WarehouseId | Must match valid warehouse |
| Salesman | arss.slsrepout | PrimarySalespersonId | Must match valid sales rep |
| Salesman | arss.slsrepin | PrimarySalespersonId | Must match valid sales rep. If determined that inside sales rep is the better one to use, adjust the standard field map |
| Default Ship Via | arss.shipviaty | ShipCode | Should match a valid Carrier/Service code by the ShipCode field not enforced but required to map properly when setting the default carrier/service in the checkout screen. If none provided, will use the BillTo value |
| Alternate Pricing Customer | arss.pdcustno | PricingCustomerId | Optional should not be valid on a ShipTo |
| Bank Code | arsc.bankno | BankCode | |
| Price Level | arss.pricecd | Not required using real-time pricing | |
| Line Discount Level | arss.disccd | Not required using real-time pricing | |
| Credit Limit | arss.credlim | CreditLimit | Typically enforced only at customer level but not a standard function |
| Active | arss.statustype | IsActive |
arsc.statustype = A (Active) arsc.statustype = I (Inactive) |
Customer products
Customer-specific product data will be retrieved by Configured Commerce via a direct call to the SX.e database. While SX.e supports ship-to level customer-specific products, Configured Commerce integration will only pull the bill-to level records.
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 (icsec) | Configured Commerce Destination (CustomerProduct) | Notes |
|---|---|---|---|
| Record Filter | rectype |
Only pull records WHERE icsec.rectype = C (customer product) icsec.shipto = (no ship-to's) icsp.statustype = A (active products) icsp.prodtype = S (standard products) arsc.statustype = 1 (active customers) |
|
| ERP Part # | altprod | ProductId | Lookup to Product table |
| Customer # | custno | CustomerId | Only pull records WHERE icsec.shipto is blank (bill-to level). Lookup to customer table. |
| Customer Part # | prod | Name | Customer's product number |
| Unit of Measure | unitsell | UnitOfMeasure | This will become the default unit of measure for the customer if specified |
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 SX.e ERP database. It first looks for records in the ICSP (Products) table, and then it looks to the ICSC (Catalog) table for records that do not exist in the ICSP table. If the same item appears in both the ICSP & ICSC tables, only the ICSP data will be integrated into ISC. . Only records marked as active within SX.e will be retrieved in the refresh job.
If SX.e catalog information should be excluded, remove the Union join information for ICSC in the query within the job definition.
Some Product-related data required by Configured Commerce may not exist in the ICSP table. Instead, this data will be retrieved from the ICSW table by joining on the cono, prod, and whse fields between the ICSP & ICSW tables. The whse value is determined using the default warehouse value as defined in a parameter in the integration job.
Unlike the ICSP, the ICSC table contains all the Product data required by ISC, so there is no need to join to the ICSW table if used.
Note that we only retrieve products that are in stock with an active status. Inactive products are not retrieved by default nor is the inactive flag set on the products directly. If an active product becomes inactive, it will be effectively discontinued on the site. This logic can be changed by the implementer.
Deletion Strategy: Since these tables can be large, Configured Commerce should only retrieve records important to the platform. The job also runs using Delta Datasets to minimize transaction volume and needs to do a union query between the two tables, excluding the data in ICSC that already exists in ICSP. Configured Commerce uses the Set Field option to set the DeactivateOn field automatically based on using a snapshot strategy for data retrieval.
Field mapping: Product refresh product
| Field Name | ERP Source (icsp) | Configured Commerce Destination (Product) | Notes |
|---|---|---|---|
| Company | cono | N/A |
Used for filtering the correct records only. Obtain value from the system setting |
| Product Type | prodtype | N/A | Only pull records WHERE icsp.prodtype = 'S' (standard products) |
| Active/Deactivate | statustype |
ActivateOn DeactivateOn |
Only pulling records with statustype A (active) and the ActivateOn will be set with the current date but not overwritten. Active records will reset the DeactivateOn date to null. Products with statustype I (inactive) will be archived by setting their deactivateon to the current date as part of the DeleteAction. |
| ERP Item # | prod |
Name ERPNumber |
This is the primary natural key to the table |
|
Item Title & Item Description |
descrip[1] + " " + descrip[2] |
ShortDescription ERPDescription |
Short description will not be overwritten it is expected to be maintained in the application or from a PIM |
| URL Segment | Descrip[1] + + descrip[2] + + prod | URLSegment | Appending the item number to ensure uniqueness |
| Manufacturer's Part # | icsw.vendprod | ManufacturerItem | vendprod from default warehouse |
| Tax Code/Class | n/a | TaxCode1 | This field is not mapped as standard, but might need to be included for successful calls to 3rd-party tax systems (such as Avalara, Vertex) |
| Unit of Measure | unitstock | UnitOfMeasure | This is the stocking unit of measure to properly handle conversions |
| Multiple Sale Qty | Sellmult | multipleSaleQty | Items must be sold in multiples of this value |
| Unit Weight | weight | ShippingWeight | |
| Unit Length | length | ShippingLength | |
| Unit Width | width | ShippingWidth | |
| Unit Height | height | ShippingHeight | |
| Price Code | icsw.pricetype | PriceCode | Pricetype from default warehouse |
| Base Unit Price | icsw.listprice | BasicListPrice | Listprice from default warehouse |
| Product Code | prodcat | ProductCode | |
| Product Line | icsw.prodline | Not mapped used for pricing but will use real-time pricing | |
| Base Price | icsw.baseprice | Not mapped used for pricing but will use real-time pricing |
Field mapping: Product refresh catalog
| Field Name | ERP Source (icsc) | Configured Commerce Destination (Product) | Notes | ||
|---|---|---|---|---|---|
| Active/Deactivate | statustype |
Active Deactivate |
icsc.statustype = 'A' (Active) icsc.statustype = 'I' (Inactive) |
||
| ERP Item # | catalog |
Name ERPNumber |
|||
| Item Title | descrip[1] + " " + descrip[2] | ShortDescription | Not overwritten -expected to be managed via PIM or ISC | ||
| Item Description | longdescrip |
ERPDescription Description |
Not overwritten -expected to be managed via PIM or ISC | ||
| Manufacturer's Part # | vendprod | ManufacturerItem | |||
| Unit of Measure | unitstock | UnitOfMeasure | Default sales unit of measure | ||
| Unit Weight | weight | ShippingWeight | |||
| Unit Length | length | ShippingLength | |||
| Unit Width | width | ShippingWidth | |||
| Unit Height | height | ShippingHeight | |||
| Price Code | pricetype | PriceCode | |||
| Base Unit Price | listprice | BasicListPrice | |||
| Product Code | prodcat | ProductCode | |||
| Product Line | prodline | Not mapped used for pricing but will use real-time pricing | |||
| Base Price | baseprice | Not mapped used for pricing but will use real-time pricing | |||
Product cross-sell
Configured Commerce retrieves Product Cross-Sells via a direct call to the SX.e 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 SX.e.
Field mapping: Product cross-sells
| Field Name | ERP Source (icsec) | Configured Commerce Destination (CustomerProduct) | Notes |
|---|---|---|---|
| Record Filter | rectype | Only pull records WHERE icsec.rectype = S (substitute) and U (upgrade) records | |
| Relationship | CrossSell | This is the related product type we will use | |
| Part # | Altprod | ProductId | Lookup to Product table |
| Cross-Sell Part # | prod | RelatedProductId | Child collection to be populated |
Product alternate unit of measure
Configured Commerce retrieves Alternate Units of Measure via a direct call to the SX.e 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 (icseu) | Configured Commerce Destination (ProductUnitOfMeasure) | Notes |
|---|---|---|---|
| Product # | Prod | Product.ERPNumber | Lookup to Product |
| Unit of Measure | Units | UnitOfMeasure | |
| Conversion Factor | Unitconv | QtyPerBaseUnitOfMeasure | |
| Descrption | Descrip | Description |
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 (smsn) | Configured Commerce Destination (Salesperson) | Notes |
|---|---|---|---|
| Company | Cono | N/A |
Used for filtering the correct records only. Obtain value from the system setting |
| Salesperson Number | Slsrep | SalespersonNumber | |
| Name | Name | Name | |
| Email Address | |||
| Title | Slstitle | Title | |
| Phone number | Phoneno | Phone1 | |
| Manager Number | mgr | SalesManager | If provided, we can track the manager of the sales rep which allows access to the sales rep's 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 |
Please sign in to leave a comment.