InsiteConnect for SX.e (v10) reference

26 min

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 <<product-name>>. It is also required if using the Request for Quote functionality in <<product-name>>.

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)	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 <<product-name>>, 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 Name	ERP 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 <<product-name>>. 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 <<product-name>> 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 <<product-name>>, 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, <<product-name>> 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. <<product-name>> 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)	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)	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

<<product-name>> 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)	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

<<product-name>> 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 – <<product-name>> uses the Delete option for this data.

Field Mapping: Product Alternate Unit of Measure
Field Name	ERP Source (icseu)	Destination (ProductUnitOfMeasure)	Notes
Product #	Prod	Product.ERPNumber	Lookup to Product
Unit of Measure	Units	UnitOfMeasure
Conversion Factor	Unitconv	QtyPerBaseUnitOfMeasure
Descrption	Descrip	Description

Inventory tables

<<product-name>> 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

<<product-name>> retrieves both Customer (ARSC) and Ship-To (ARSS) information into a common customer table via the Customer refresh.

Deletion Strategy – <<product-name>> retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. <<product-name>> 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)	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 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 <<product-name>> via a direct call to the SX.e database. While SX.e supports ship-to level customer-specific products, <<product-name>> integration will only pull the bill-to level records.

Deletion Strategy – <<product-name>> uses the Delete option to physically remove records that are no longer valid. <<product-name>> also uses Delta Datasets.

Field Mapping: Customer Product
Field Name	ERP 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

<<product-name>> retrieves pricing from SX.e via real-time calls, so no refresh is needed. The API used for this function is sxapiOEPricingMultpleV3.

<<product-name>> 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, <<product-name>> sends in the correct quantity values and will retrieve the correct pricing for the price tier in the cart.

Field Mapping: Pricing & 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>

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 <<product-name>> 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>
Ship-To Contact	STFirstName + " " + STLastName	<shipToContact>
Ship-To Name	STCompanyName	<shipToName>
Ship-To Address	STAddress1 + " " + STAddress2	<shipToAddress>
Order Lines Table
ERP Part #	ErpNumber	<itemNumber>
Quantity Ordered	QtyOrdered	<orderQty>
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>

Order submission

Order Submit API

<<product-name>> 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, <<product-name>> 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 <<product-name>> 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:

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.

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 `<>` 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 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 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

<<product-name>> 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

<<product-name>> 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

Existing orders

Order history

<<product-name>> retrieves Order history data from SX.e via a direct call to the ERP database. The data is stored within the <<product-name>> 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 <<product-name>> 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 <<product-name>> for large history files. <<product-name>> 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 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, 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 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

<<product-name>>’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 <<product-name>> database and refreshed on a regular interval, typically once a day.

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