Dev GuideAPI Reference
Dev GuideAPI ReferenceUser GuideGitHubNuGetDev CommunitySubmit a ticketLog In
GitHubNuGetDev CommunitySubmit a ticket

InsiteConnect for SX.e (v10) reference

Describes the InsiteConnect for SX.e (v10) reference.

📘

Note

As of January 2023, Optimizely no longer adds new functionality to ERP connectors. Instead, partners can build customized solutions for clients. Optimizely continues to fix ERP connector bugs only if there are no workarounds or extensibility solutions available.

Supporting 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 Optimizely Configured Commerce. It is also required if using the Request for Quote functionality in Configured Commerce.

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

Field Mapping: Salesperson Refresh
Field NameERP Source (smsn)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 Configured Commerce, 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 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 NameERP Source (sasta)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

Product 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 Configured Commerce. 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 Configured Commerce, 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 NameERP Source (icsp)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 NameERP Source (icsc)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 product information management or <>

Item Description

longdescrip

ERPDescription

Description

Not overwritten -expected to be managed via product information management or <>

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 NameERP Source (icsec)Destination (CustomerProduct)Notes
Record Filterrectype Only pull records WHERE icsec.rectype = ‘S’ (substitute) and ‘U’ (upgrade) records
Relationship CrossSellThis is the related product type we will use
Part #AltprodProductIdLookup to Product table
Cross-Sell Part #prodRelatedProductIdChild 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 StrategyConfigured Commerce uses the Delete option for this data.

Field Mapping: Product Alternate Unit of Measure
Field NameERP Source (icseu)Destination (ProductUnitOfMeasure)Notes
Product #ProdProduct.ERPNumberLookup to Product
Unit of MeasureUnitsUnitOfMeasure 
Conversion FactorUnitconvQtyPerBaseUnitOfMeasure 
DescrptionDescripDescription 

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

Customer tables

Customer

Configured Commerce retrieves both Customer (ARSC) and Ship-To (ARSS) information into a common customer table via the Customer refresh.

Deletion StrategyConfigured 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 NameERP Source (arsc)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 <>

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 NameERP Table.FieldCommerce Table.FieldNotes

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 StrategyConfigured 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 NameERP Source (icsec)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

Pricing tables

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

Configured Commerce does not retrieve price breaks from this call - OEPricingMultipleV3 does not return enough information to calculate pricing based on the price break information returned.  However, if price-break-based pricing is set up, Configured Commerce sends in the correct quantity values and will retrieve the correct pricing for the price tier in the cart.

Field Mapping: Pricing & Availability
Field NameERP Table.FieldCommerce Table.Field (ProdDataPrcAvail)Notes
Transaction #“1”Parameter 1: Transactions ID Number 
Request IdentificationCompany Number to 4 digits plus Customer.ERPNumber formatted  to 12 digitsParameter 2: Request ID #Formatted with leading zeros
Order NumberBlankParameter 3: Order #Since there wil be no order #, leave empty
Ship ToCustomer.ERPSequenceParameter 4: Ship To 
WarehouseWarehouse.NameParameter 5: WarehouseThe warehouse for which pricing is calculated.  If blank, will return all warehouses.
WarehouseWarehouse.Name<origWhse> 
Qty Ordered“1”<qtyOrd>We use this normally to get the default price for a qty of 1
Unit of MeasureProduct.UnitOfMeasure<unit> 
  <calcPriceTy> 
Product #Product.ERPNumber<Prod> 
 Unused<itemDetail> 
 Unused<calcPriceFl> 
 Unused<availabilityWhse> 
 Unused<altWhse> 
 Unused<netAvail> 
 Unused<unitConv> 

Tax (order simulation)

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.

📘

Note

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=”$””

 

 

 

 

Order submission

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

The API sxapiSFOEOrderTotLoadV4 is used for this function.  The data below will show the net differences for the actual order submission.

The standard Configured Commerce 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.

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.
Field Mapping: Order Submit – Header
Field NameCommerce 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 <> 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 don’t 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 NameCommerce 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 don’t 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)

Credit card processing

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.

A/R aging balances (invoices)

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 NameCommerce Table.Field (Current Session Context)ERP Web Service FieldNotes
Customer #BillTo.ERPNumberParameter 1: Customer # 
Ship ToBlankParameter 2 – Ship ToWe will always retrieve the customer level information but, if theERPSequence is provided, the balance for that specific ship-to would be returned.
Balance TypeBalanceType=tParameter 3 – Balance TypeT provides total exposure across ARSC and ARSS

Existing orders

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 in Configured Commerce are immediately added to the 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 current 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’s 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 NameERP 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, don't 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 NameERP 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

Configured Commerce’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 NameERP 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 NameERP 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 NameERP 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 NameERP Web Service Field (oeel)Commerce Table.Field (InvoiceHistoryLine)Notes
Invoice #orderno + ordersufInvoiceHistoryIdconcatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")
Line NumberlinenoLineNumber 
Release Number ReleaseNumberStatic value = 0
Line Type LineTypeStatic Value = 'Product'
ERP Order #orderno + ordersufERPOrderNumberconcatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99")
Item #shipprodProductERPNumber 
Item Descriptionicsp.descrip[1-2] OR oeel.proddesc & oeel.proddesc2DescriptionIF oeel.specnstype = "n" THEN concatenate(oeel.proddesc, " ", oeel.prodesc2) ELSE concatenate(icsp.descrip[1], " ", icsp.descrip[2])
Unit of MeasureunitUnitOfMeasure 
WarehouseWhseWarerhouse 
Qty InvoicedqtyordQtyInvoiced 
Unit PricepriceUnitPrice 
Extended PricenetamtLineTotal