SXev 11 connectors reference

  • Updated

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

All transtypes are retrived into Commerce except: BL, QU, ST, FO, BR.

Sample transtype retrieved into 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:

  1. If the setting ediprcfl is turned on in SX.e and ARSC/ARSS tables allow for it.
  2. Create/update a setting in SASBR for category SXAPI,
    1. Rule: Override Price and value: Yes.
    2. 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

Email

email

Email

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

 

Email

arss.email

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

Email

Email

 

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