As of January 2023, Optimizely allows partners and the implementation community to build ERP connectors to increase the number of ERP connectors and enhancements available to customers. Optimizely continues to offer existing ERP connectors in their current state, and partners have access to the connector code for future enhancements.
Order history
Order history data is pulled from A+ 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 A+ order records analyzed, the integration process will use a lookback period when constructing the query but only against history. Note that there are 2 source tables: orhed is for open orders and hshed is for historical orders. The intent is to pull this data through a union query to present a single dataset. We will pull the entirety of the current order table and only use the lookback for historical orders.
During end of day in A+, invoiced orders are moved from orhed to hshed so the order never lives in both places simultaneously. In orhed, the order is defined by ohcono/ohorno/ohorgn (company, order #, generation) and in history this is company/order/origin and sequence (oahssq) to make it unique. Technically, the sequence itself is unique but it s possible to have more than one although we will not be supporting duplicates.
Note that we understand that, over time, the order number itself can wrap around are be restarted. We are not accounting for this in the standard connector under the premise that we would not store more than, say 3 years of historical data and that this wraparound of the orders will not occur in this timeframe
Orders placed on the site are immediately added to the Configured Commerce order history tables. This ensures that a user will immediately see their orders if they go to Order History pages in the My Account area of the site. If the order submission works in real-time, then the ERP order # should be present as well.
Implementation Note - Performance: Because many A+ customers have very large tables and the system must perform a table scan which can make this refresh take a very long time. For performance purposes, the customer may wish to establish a custom index on hshed.oadndt.
Implementation Note Lookback Days: There is a general strategy to limit the amount of data being reviewed and transmitted to the website for large, history files. The idea is to only look for records that have changed within a relatively short period of time. We use a default of 5 days to cover any missed refreshes, weekends and holidays. The number could be expanded or shortened based on implementation preferences. It is coded directly into the WHERE clauses of the history refreshes. For history, use hshed.oadncc = 20 and hshed.oadndt - #/days (integer field).
Deletion Strategy: There is a special strategy for Order History because we don t want to do a full snapshot of the data but we need to catch any changes to, say, order lines that were deleted. A special option to Delete Children will be used with the delete action set to Delete. This way, any order that is processed will clear and reload the order line data to history.
Field mapping: Order history header
Field Name | ERP Table.Field (orhed/hshed) | Commerce Table.Field (OrderHistory) | Notes |
---|---|---|---|
Company |
Ohcono/oacono |
|
Lookup to Selecting records based on defined company number in settings
Only pull in order types O, R, I, B |
ERP Order # |
Ohorno + ohorgn/oaorno + oaorgn |
ERPOrderNumber |
concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
Order Status |
Ohorst/ I |
Status |
Value - Description 1 Ready to Print 2 Pick List Printed 3 Ship Confirmed 4 - Invoiced 9 On Hold I - Invoiced
|
History Sequence # |
Hshed.oahssq |
N/A |
Used to link to history detail |
Order Date |
Ohetdt (yymmdd)/oaentd(ccyymmdd) |
OrderDate |
|
Order Type/ Transaction Type |
Ohortp/oaortp |
|
No real landing place for this.
Values: O Orders R Returns I = Invoice Only B Entire order on B/O hold |
Customer # |
Ohcsno/oacsno |
CustomerNumber |
|
Ship-To # |
Ohshp#/oashp# |
CustomerSequence |
|
Customer PO # |
Ohcspo/oacspo |
CustomerPO |
|
Terms Code |
Ohtmcd/oatmcd |
Terms |
|
Ship Via |
|
ShipCode |
|
Requested Ship Date |
|
RequestedDeliveryDate |
Note that these are not exactly the same |
Salesperson |
|
Salesperson |
|
Billing Information: Company/Name |
Ohcsnm Hsnam.oyname |
BTCompanyName |
History to hsnam on oyhssq = oahssq and oynmtp = B |
Billing Information: Address |
Ohcad1..4 Hsman/oyadd1..4 |
BTAddress1..2 |
|
Billing Information: City |
Ohblcy Hsman.oycity |
BTCity |
|
Billing Information: State |
Ohblst Hsnam.oystat |
BTState |
|
Billing Information : Zip |
Ohblzp Hsnam.oyzip4 |
BTPostalCode |
|
Billing Information Country |
Ohctid Hsnam/oyctid |
BTCountry |
|
Shipping Information: Company/Name |
Ohshnm Hsnam.oynam |
STCompanyName |
History to hsnam on oyhssq = oahssq and oynmtp = S |
Shipping Information: Address |
Ohsad1..4 Hsnam.oyadd1..4 |
STAddress1..2 |
|
Shipping Information: City |
Ohscty Hsnam/oyciity |
STCity |
|
Shipping Information: State |
Ohshst Hsnam.oystat |
STState |
|
Shipping Information: Postal Code |
Ohszip Hsnam.oyzip4 |
STPostalCode |
|
Shipping Information: Country |
Ohshci Hsnam.oyctid |
STCountry |
|
Subtotal |
See notes |
ProductTotal |
If orhed.ohorst = 1,2,9 then sum(ordet.odinvl) only I line types
If ohorst = 3,4 then ohisam
If history hshed.oaisam |
Order Discount |
|
OrderDiscountAmount |
|
Shipping |
|
ShippingCharges |
No separate field |
Misc. Charges |
Ohscam/oascam |
OtherCharges |
Includes shipping only open orders with status 3,4 |
Sales Tax |
Ohtxam/oatxam |
TaxAmount |
Open status 3,4 only |
Order Total |
Calculated |
OrderTotal |
If from history, oainam If from open: If ohorst = 3 or 4, then ohinam If ohorst = 1,2,9 then ohorvl |
Order Notes |
|
Notes |
|
Field mapping: Order history detail
Field Name | ERP Table.Field (ordet/hsdet) | Commerce Table.Field (OrderHistoryLine) | Notes |
---|---|---|---|
Company |
Odcono/obcono |
|
Lookup to Selecting records based on defined company number in settings
Only select line items where ordet.odlitp = I or hsdet.oblitp = I (line item type) |
ERP Order # |
Odorno + odorgn/oborno + oborgn |
OrderHistoryId |
concatenation of (oeeh.orderno) + "-" + (oeeh.ordersuf,"99") |
Line Number |
Odorsq/oborsq |
LineNumber |
|
Customer # |
Odorno/oborno |
CustomerNumber |
|
Ship-To # |
Odshp#/obshp# |
CustomerSequence |
|
Line Type |
|
LineType |
Static Value = 'Product' |
Item # |
Oditno/obitno |
ProductERPNumber |
|
Item Description |
Oditd1 + oditd2/obitd1 + oditd2 |
Description |
|
Warehouse |
Odwhid/obwhid |
Warehouse |
|
Unit of Measure |
See notes |
UnitOfMeasure |
If (ordet.odcoum <> blank and ordet.odcqto <> 0) then ordet.odcoum else ordet.odunms
If (hsdset.obcoum <> blank and hsdet.obcqto <> 0) then hsdet.obcoum else hsdet.obunms |
QTY Ordered |
See notes |
QtyOrdered |
Different fields: Obqtor = stk qty ordered Obcqto = customer qty ordered Obcoum = customer u/m ordered Obunms = stock u/m Obprum = pricing u/m If (ordet.odcoum <> blank and ordet.odcqto <> 0) then ordet.odcqto else ordet.odqtor If (hsdet.obcoum <> blank and hsdet.obcqto <> 0) then hsdet.obcqto else hsdet.obqtor |
QTY Shipped |
See notes |
QtyShipped |
If (ordet.odcoum <> blank and ordet.odcqto <> 0) then (ordet.odqtsh / ordet.odqtor) * ordet.odcqto else ordet.odqtsh If (hsdet.obcoum <> blank and hsdet.obcqto <> 0) then (hsdet.obqtsh / hsdet.obqtor) * hsdet.obcqto else hsdet.obqtsh |
Inventory Qty Ordered |
|
InventoryQtyOrdered |
Skip |
Inventory Qty Shipped |
|
InventoryQtyShipped |
Skip |
Unit Price |
|
UnitNetPrice |
Open status 1,2,9: Extended price / qty ordered) Open status 3,4 If QtyShipped <> 0 then extended price / qty shipped Else ordet.odlnvl / qty ordered History If qty shipped <> 0 then oblnam / qty shipped Else oblnam / qty ordered Preference to use shipped extension vs ordered |
Extended Price |
|
LineTotal |
If orhed.ohorst = 1,2,9 then ordet.odlnvl else odinam History = hsdet.oblnam |
Ship Date |
|
LastShipDate |
|
Line Notes |
Ordet.oditd1 + oditd2 Hsdet.obitd1 + obitd2 |
Notes |
Not including in standard if desired, include this line type. Only line type odlitp = M For history, hsdet.oblitp = M by sequence (obhssq = oahssq) |
|
|
|
|
Invoices
Invoice data is pulled from A+ 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 A+ order records analyzed, the integration process will use a lookback period when constructing the query. This query is used against the A+ order header transaction date only since we do not expect invoice records to change once invoiced.
There are 2 files for invoices open (ardhd) and closed (arhhd). Except for general backloading of the data, we will only be pulling from the open file. Additionally, since we will only be displaying invoice summary and A/R information, we will not be obtaining full customer/ship-to addresses and other order-related items. If additional order level information is required, tie to hshed using hshed.oahssq = ardhd.rhhsid. We will use a delta dataset and grab the entire open invoice table on each refresh.
Deletion Strategy: We will employ the Ignore strategy since 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 (ardhd) | Commerce Table.Field (InvoiceHistory) | Notes |
---|---|---|---|
Company |
Rhcono |
|
Lookup to Selecting records based on defined company number in settings |
Invoice # |
Rhinno |
InvoiceNumber |
|
Invoice Date |
Rhivdt |
InvoiceDate |
Yymmdd only |
Invoice Due Date |
Rhcddt |
DueDate |
Yymmdd only |
Invoice Type |
Rhintp |
InvoiceType |
Blank = invoice, F = finance charge |
Invoice Status |
|
Status |
|
Open Invoice Flag |
|
IsOpen |
If rhinbl <> 0, open else closed |
Customer # |
rhcsno |
CustomerNumber |
|
Ship-To # |
|
CustomerSequence |
|
Currency |
|
CurrencyCode |
|
Customer PO # |
|
CustomerPO |
|
Terms |
Rhtmcd |
Terms |
Terms description in ORCTL (see Payment Methods refresh) |
Ship Code |
|
ShipCode |
|
Salesperson |
|
Salesperson |
|
Subtotal |
|
ProductTotal |
|
Sales Tax |
|
TaxAmount |
|
Shipping |
|
ShippingAndHandling |
|
Discount Amount |
|
DiscountAmount |
|
Misc Charges |
|
OtherCharges |
|
Invoice Total |
|
InvoiceTotal |
|
Current Balance |
rhinbl |
CurrentBalance |
Links from invoice history to A/R using hshed.oahssq = ardhd.rhhsid |
Billing Information |
Using rhslto |
BT fields |
See order history |
Shipping Information |
|
ST fields |
See order history |
Existing orders
A/R aging balances API
A standard direct call to the API endpoint will be used on the standard Invoice History page to display the A/R aging information. The standard aging buckets must be set up in Settings as global options for the aging ranges. We will not be using the returned labels from the API, only the balances themselves.
The API used for this information will be ARSummary.
Field mapping: A/R aging
Field Name | Commerce Table.Field (Current Session Context) | ERP Web Service Field | Notes |
---|---|---|---|
Company Number |
|
Parameter 1: Company # |
|
Customer # |
BillTo.ERPNumber |
Parameter 2: Customer # |
|
Aging Period Balances |
|
<AgePeriodXAmt> |
X = 1..4 |
Future Amount |
|
<FutureAmount> |
|
A/R aging balances (invoices)
Configured Commerce will call the payment gateway (CenPOS) directly to authorize a user s credit card. The authorization information is passed into A+ via the order submission process using a token returned by the payment gateway.
Credit card processing
Order submit API
Configured Commerce submits order to A+ via real-time calls. The API used for this function is OrderImport.
Field mapping: Order submit header
Field Name | Commerce Table.Field (CustomerOrder) | ERP Web Service Field | Notes |
---|---|---|---|
Customer # |
CustomerNumber |
<CustomerID> |
|
|
|
|
|
Customer PO |
CustomerPo |
<PONumber> |
|
Order Number |
OrderNumber |
<WebOrderID> |
|
Order Type |
Type |
<OrderType> |
|
Bill-To Contact |
BTFirstName + + BTLastName |
<BillToContact> |
|
Bill-To Name |
BTCompanyName |
<CustomerName> |
|
Bill-To Address Line 1 |
BTAddress1 |
<CustomerAddr1> |
|
Bill-To Address Line 2 |
BTAddress2 |
<CustomerAddr2> |
|
Bill-To Address Line 3 |
BTAddress3 |
<CustomerAddr3> |
|
Bill-To Address Line 4 |
BTAddress4 |
<CustomerAddr4> |
|
Bill-To City |
BTCity |
<BillToCity> |
|
Bill-To State |
BTState |
<BillToState> |
|
Bill-To Postal Code |
BTPostalCode |
<BillToZip> |
|
Bill-To Country |
BTCountry |
<BillToCntryCode> |
|
Bill-To Phone |
BTPhone |
<BillToPhone> |
|
Ship-To # |
CustomerSequence |
<ShipToNumber> |
|
Ship-To Contact |
STFirstName + + STLastName |
<ShipToContact> |
For actual order submission, if there is no change to the customer record, we should not populate any of the ship-to address fields to mimic standard behavior. Only populate if it s a 1-time or new address. |
Ship-To Name |
STCompanyName |
<ShipToName> |
|
Ship-To Address Line 1 |
STAddress1 |
<ShipToAddr1> |
|
Ship-To Address Line 2 |
STAddress2 |
<ShipToAddr2> |
|
Ship-To Address Line 3 |
STAddress3 |
<ShipToAddr3> |
|
Ship-To Address Line 4 |
STAddress4 |
<ShipToAddr4> |
|
Ship-To City |
STCity |
<ShipToCity> |
|
Ship-To State |
STState |
<ShipToState> |
|
Ship-To Postal Code |
STPostalCode |
<ShipToZip> |
|
Ship-To Country |
STCountry |
<ShipToCntryCode> |
Country code must be derived by finding the Configured Commerce country and using the Abbreviation field as the code the API will allow any value but the application itself will not work with bad country codes. |
Ship-To Phone |
STPhone |
<ShipToPhone> |
|
Ship Via |
ShipVia.ERPShipCode |
<CarrierCode> |
|
Requested Ship Date |
RequestedShipDate |
<ReqShipDate> |
|
Transaction Type |
|
<WebTransactionType> |
Value = LSF (Order Load) to submit order Value = TSF (Order Total) to use for order simulate/tax calculation |
Web User |
UserName |
<WebUserID> |
|
Field mapping: Credit card data
Field Name | Commerce Table.Field | ERP Web Service Field | Notes |
---|---|---|---|
CC Customer ID |
2 character Company Code + 10 character Customer # (include leading 0's if less than 10) |
CCCustomerID |
|
Credit Card # |
Static Value = <Blank> |
CCCreditCardNbr |
|
Credit Card Payment Type |
Static Value = <Blank> |
CCPaymentType |
|
Credit Card Exp Date |
Static Value = <Blank> |
CCCreditCardExp |
|
Card Holder |
Static Value = <Blank> |
CCCardHolder |
|
CVV |
Static Value = <Blank> |
CCCVV2 |
|
Card Billing Address 1 |
Static Value = <Blank> |
CCAddr1 |
|
Card Billing Address 2 |
Static Value = <Blank> |
CCAddr2 |
|
Card Billing Address 3 |
Static Value = <Blank> |
CCAddr3 |
|
Card Billing Address 4 |
Static Value = <Blank> |
CCAddr4 |
|
Card Billing City |
Static Value = <Blank> |
CCCity |
|
Card Billing State |
Static Value = <Blank> |
CCState |
|
Card Billing ZIP |
Static Value = <Blank> |
CCZip |
|
Card Billing Country |
Static Value = <Blank> |
CCCountry |
|
PO Number |
CustomerOrder.CustomerPO |
CCPONumber |
|
Ship To Zip |
CustomerOrder.STZip |
CCShipToZip |
|
Tax Amount |
Use value from TFS call (that is order simulation) if CC order |
CCTaxAmount |
|
Authorization Amount |
CreditCardTransaction.Amount |
CCAuthorizationAmount |
Note: This value will be the authorization amount and NOT the order total. The value will include the percentage increase. |
Merchant ID |
Optimizely Application Setting: PaymentGateway_Cenpos_MerchantId |
CCMerchantId |
|
Card Number - Masked |
CreditCardTransaction. CreditCardNumber |
CCMaskedCard |
Value will be the masked card # sent in CenPOS response |
Card Token |
CreditCardTransaction.Token1 |
CCToken |
|
Authorization Number |
CreditCardTransaction.AuthCode |
CCAuthorizationNumber |
|
Reference Number |
CreditCardTransaction.PNRef |
CCReferenceNumber |
|
Credit Card Type |
CreditCardTransaction.CardType |
CCCardType |
Values submitted will be the values obtained from CenPOS: Visa , MasterCard , Amex or Discover |
CC End |
Static Value = Y |
CCEND |
|
Field mapping: Order submit detail/line
Field Name | Commerce Table.Field (OrderLine) | ERP Web Service Field | Notes |
---|---|---|---|
Line number |
Line |
lineIden |
|
ERP Item # |
ErpNumber |
<ItemId> |
|
Quantity Ordered |
QtyOrdered |
<OrderQty> |
|
Unit of Measure |
UnitOfMeasure |
<UnitOfMeasure> |
|
Unit Price |
UnitRegularPrice |
<ListPrice> |
|
Warehouse |
WarehouseId |
<WarehouseID> |
|
Description |
Description |
<ItemDesc1> |
|
Due Date |
DueDate |
<DueDate> |
|
ActualPrice |
ActualPrice |
<ActualSellPrice> |
|
Regular Price |
RegularPrice |
<ListPrice> |
|
As part of the standard connector for A+, there will be a Tax Calculator specific to the connector which will make an API call to A+ to calculate the tax amount and return the tax amount to the website on the Cart (if configured) and Checkout screens. If another tax calculator, such as Avalara, is used, then this Order Simulate function will not be used.
The API CreateOrder will be used for this function.
It is expected that 1-time addresses, if allowed, will be able to have tax calculated internally within A+ without additional information provided. If taxes are critical for the order to be submitted but the tax jurisdiction cannot reliably be determined, we suggest self-registration, new ship-to addresses and 1-time addresses should all be disabled.
Note2: The Order Simulate/Tax Calculation is the same API call as the Order Submit, using the WebTransactionType of TSF
Tax (Order Simulation)
Pricing API
Pricing and inventory will be done using real-time calls to A+ so no refresh will be needed. The API used for this function is GetAvail.
Pricing: The requested information will be calculated and returned on the basis of customer number, company number, item number, unit of measure, and quantity.
Availability: If it is Default and alternate warehouses then the quantity will be the sum of the default warehouse and the quantity available in the alternate warehouses. If the DftWhsOnly element is N then the quantity will be the sum of the default warehouse and the quantity available in the alternate warehouses. If the DftWhsOnly element is Y then it will only display the quantity available in the default warehouse.
Field mapping: Pricing and availability
Field Name | Commerce Table.Field (ProdDataPrcAvail) | ERP Web Service Field | Notes |
---|---|---|---|
Transaction # | "1" (unique string) | <TransactionID> | |
Request Identification | Company Number to 4 digits plus Customer.ERPNumber formatted to 12 digits | <RequestID> | 2 position company number and 10 position customer number |
Order Number | Blank | <OrderNumber> | There will be no order number, so a null is sent. |
<Items> | |||
Warehouse | Warehouse.Name | <WarehouseID> | The warehouse for which pricing is calculated. If blank, will return all warehouses. |
Qty Ordered | "1" | <OrderQuantity> | We use this to get the default price for a qty of 1. |
Unit of Measure | Product.UnitofMeasure | <UnitofMeasure> | |
<NonStockFlag> | |||
Product # | Product.ERPNumber | <ItemNumber> | |
Ship To | Customer.ERPSequence | <ShiptoNumber> | |
<DftWhyOnly> | |||
<CalculatePrices> | *see notes above about availability |
Pricing tables
Customer
The Customer refresh will be pulling both Customer (cusms) and Ship-To (addr) information over to Configured Commerce into a common customer table.
Deletion Strategy: Configured Commerce retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. Configured Commerce must use Ignore, since the data will be retrieved in two separate sweeps.
Field mapping: Customer (bill-to)
Field Name | ERP Source (cusms) | Configured Commerce Destination (Customer) | Notes |
---|---|---|---|
Company |
cmcono |
|
Lookup to Selecting records based on defined company number in settings |
Customer # |
cmcsno |
CustomerNumber ERPNumber |
|
Ship-To # |
|
CustomerSequence |
Static Value = Blank |
Customer Name |
cmcsnm |
Company |
|
Contact Name |
cmpcnt |
Attention |
Map only if exposed |
Address Line 1-3 |
cmcad1..cmcad3 |
Address1..3 |
|
City |
cmcity |
City |
|
State |
cmstat |
StateId |
Lookup being used - must exist in ISC |
Country |
cmctid |
CountryId |
Must match country abbreviation to be valid, uses lookup. |
Postal Code |
cmzip4 |
PostalCode |
|
Customer Type |
cmcscl |
CustomerType |
Optional field |
Territory |
cmtrno |
Territory |
Not a standard field |
Phone # |
cmpupn |
Phone |
|
Fax |
Cmfxpn |
Fax |
Skipping - not a standard field |
Terms Code |
Cmpacd |
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 |
|
Warehouse |
cmdfwh |
DefaultWarehouseId |
Must match a valid warehouse. |
Outside Sales Rep |
Cmrp#1 |
PrimarySalespersonId |
Must match a valid sales rep |
Inside Sales Rep |
|
|
If desired, can be mapped to Salesperson instead of the outside sales rep. We only support a single, assigned sales rep to the account. |
Default Ship Via |
Cmcad |
ShipCode |
Should match a valid Carrier/Service code by the ERP ShipCode field not enforced but required to map properly when setting the default carrier/service in the checkout screen |
Alternate Pricing Customer |
Cmcrid |
|
Optional - represents the corporate group ID |
Credit Limit |
cmcrlm |
CreditLimit |
Not enforced as standard feature. Note that open order amount + A/R Balance > credit limit puts a customer on credit hold |
PO Required |
Cmporq |
|
Future Implementation |
Field mapping: Customer (ship-to)
Field Name | ERP Source (addr) | Configured Commerce Destination (Customer) | Notes |
---|---|---|---|
Company |
Sacono |
|
Lookup to Select records based on defined company number in settings |
Customer # |
Sacsno |
CustomerNumber ERPNumber |
|
Ship-To # |
Sashp# |
CustomerSequence |
Cannot be blank - skipping blanks. |
Customer Name |
Sashm |
Company |
|
Attention |
Sascnt |
Attention |
|
Address Line 1-3 |
Sasad1..sasad3 |
Address1..3 |
|
City |
sascty |
City |
|
State |
sashst |
StateId |
Lookup must match a valid state for the country supplied |
Country |
sactid |
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 |
saszip |
PostalCode |
|
Territory |
Sashtr |
Territory |
If exposed from dictionary |
Phone |
sasphn |
Phone |
|
Fax |
safaxn |
Fax |
|
Tax Code 1 |
|
TaxCode1 |
Static Value = Blank; Relying on calls to API to determine the tax amount in cart. |
Tax Code 2 |
|
TaxCode2 |
|
Warehouse |
sadfwh |
WarehouseID |
Must Match Valid Warhouse |
Outside Sales Rep |
sarp#1 |
PrimarySalespersonId |
Must match valid sales rep |
Default Ship Via |
Sacacd |
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 |
Active |
sasusp |
IsActive |
if any value, inactive customer |
Customer products
Customer-specific product data will be pulled into Configured Commerce via a direct call to the A+ database. While A+ 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 | Configured Commerce Destination (CustomerProduct) | Notes |
---|---|---|---|
Company |
cicono |
|
Lookup to Select records based on defined company number in settings |
ERP Part # |
ciitno |
ProductId |
Lookup to Product table |
Customer # |
Cicsno |
CustomerId |
Lookup to customer table. Only valid for bill-to customers. |
Customer Part # |
cicsit |
Name |
|
Unit of Measure |
Cdfum.duunms |
UnitOfMeasure |
This will become the default unit of measure for the customer if specified
Retieve this record using: ducono = cicono ducsno = cicsno duitno = ciitno |
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 integration process retrieves item/product information directly from the A+ ERP database. The field itmst.impwbu will be used to determine which products are active.
Deletion Strategy: Since these tables can be large, we do want to only retrieve records that are important to pull into ISC. This job may be run using Delta Datasets to minimize transaction volume coming over. We will use 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 (itmst) | Configured Commerce Destination (Product) | Notes |
---|---|---|---|
Company |
Imcono |
|
Selecting records based on defined company number in settings company 0 = global entries |
Active/Deactivate |
imsusp |
ActivateOn DeactivateOn |
Only pull records where this field is not S (only pull active products) and Also do not pull products if impwbu = Y |
ERP Item # |
imitno |
Name ERPNumber |
This is the primary natural key to the table |
Item Title & Item Description |
Imitd1 + + imitd2 |
ShortDescription ERPDescription |
Short description will not be overwritten it is expected to be maintained in the application or from a PIM |
URL Segment |
Imitd1 + + imitd2 + + imitno |
URLSegment |
Appending the item number to ensure uniqueness |
Active Flag |
impwbu |
DeactivateOn |
This will be used for filtering the products selected. |
Discontinued Flag |
imdsco |
IsDiscontinued |
Y/N |
Default Hide/Show |
imrscd |
DefaultVisibility |
Join to ORCTL: Cccono = 0 Ccapid = OE Ccctlk = * + imrscd Ccdata(31,1) = P (default Show) or A (default Hide) |
Manufacturer's Part # |
immfno |
ManufacturerItem |
vendprod from default warehouse |
Unit of Measure |
imunm1 |
UnitOfMeasure |
This is the stocking unit of measure to properly handle conversions |
Rounding Rule |
imaum.auaufc imstus |
roundingRule, multipleSaleQty |
For the primary U/M only if it exists A = Round Up R = Round Y =-No Rounding
If imstus <> 0 then set rule to Multiple and set MultipleSaleQty |
Multiple Sale Qty |
Imstus |
multipleSaleQty |
Items must be sold in multiples of this value if > 1 then set Rounding Rule = Multiple Only |
Unit of Measure Description |
see notes |
|
This is rather difficult to retrieve and can be added if needed.
Retrieve the record as a subselect to orctl where cccono = company, ccapid = IA , ccctlk = @UOM followed by imunm1. The description is returned in ccdata(1,30) |
Allow Backorder |
imbocd |
|
Not Used |
Catch Weight |
imctwt |
|
Y/N, Not Used |
Unit Weight |
imuwt1 |
ShippingWeight |
|
UNSPSC |
imcmod |
UNSPSC |
Not mapped: Used for punchout and needs to be enabled in the Application Dictionary |
UPC Code |
itupc.upbrit |
UPCCode |
Not mapped: Used for punchout and needs to be enabled in Application dictionary Match to itupc on upitno and upunms (u/m) |
Replacement Product |
imxrf.mrrpit |
ReplacementProductId |
Lookup to Product by checking for an item in imxrf where mrpocd = R and mrorit = current product and mrrpit = replacement product |
Vendor |
imvnno |
VendorId |
Lookup to Vendor table assuming vendors are set up in ISC |
Unit cost |
see notes |
|
Not part of the standard connector. Cost options would come from ITBAL and include:
IBLCST last cost |
Base Unit Price |
|
|
not mapped - used for pricing, but default connecotr uses real-time pricing |
Tax Class |
imitxc |
TaxCode1 |
|
MSDS Available |
Immsdt |
HasMsds |
Not being mapped not a standard field but available if needed |
Special Order Flag |
itbal.idbrsh |
IsSpecialOrder |
Not being mapped not a standard field but available if needed; Mark as special order if D (drop ship) or S cross-dock) would need to use default warehouse to get itbal record if needed |
Extended Description |
iaeic.incmtx |
n/a |
NOT STANDARD if long descriptions are kept in A+. Must retrieve and concatenate records using: Inwhid=blank Initno = imitno Inigid = blank Incono = 0 Incsno = 0 Incgid = blank Inedcc/ineddt (end century/date) >= today Instcc/instdt <= today Incmsq = comment sequence |
Product cross-sell
Configured Commerce retrieves Product Cross-Sells via a direct call to the A+ database. This refresh can be modified or copied to use specific record types or relationships needed for a given implementation.
Deletion Strategy:
We will use the Ignore option users may create manual entries for different types of relationships that are not managed in A+.
The data will be coming from table imxrf with A records mapped to Accessories relationship and C records mapped to Cross-Sells relationship.
Field mapping: Product cross-sells
Field Name | ERP Source (imxrf) | Configured Commerce Destination (ProductRelatedProduct) | Notes |
---|---|---|---|
Record Filter |
Only pull records where imxrf.mrrpcd = A or C A=Accessories C=CrossSells |
||
Primary Item | inv_mast.inv_mast_UID | ProductID | This is joined to ony match records of the inv_mast_uid field in inv_sub. |
Related Item | inv_sub.item_id | RelatedProductID | This is joined to ony match records of the sub_inv_mast_uid field in inv_sub in addition to the above join. |
Product restriction group
There is the concept of restriction groups within A+ and the following tables can be used to refresh them. Note that we are not supporting the opt-in/opt-out flag in cusms.cmckrs which has a Y/N/S flag indicating if product restrictions are applied or not.
Restriction groups in A+ work as follows:
- The list of groups is in ORCTL and defines the default behavior for the group (that is hide/show)
- The table OECAU defines the relationships
- We will need to create a restriction group with the customer # and optional Ship to # for distinct product restrictions which will be by customer
- If there is an entry in OECAU for a customer and a carscd (restriction code) field, then they override the behavior of the entire group. These customers will be added to the Restriction Group which will have an opposite behavior of the product
- If there is an entry in OECAU for a customer and a caitno field (product), then they override the behavior for just that product. These customers will be added to a customer-specific restriction group with just the products specified and the opposite behavior of the default.
Note that if a customer is assigned to both a restriction group and has overrides to that restriction group, this will only effectively work to HIDE a product, not show one. For example, if the default behavior is HIDE and they are in a restriction group to SHOW with an exception to HIDE, the individual product will be hidden. The guiding rule in restriction groups is that any override with a hide supersedes overrides to show.
On the other hand, if a product is defaulted to SHOW and the customer is in a restriction group to HIDE the product but there is an individual override of the product to SHOW it again, the HIDE on the restriction group will supersede the SHOW of the individual product so that override will have no effect.
The standard integration will set up the restriction groups and overrides correctly, they just, under certain circumstances, may not have the impact desired.
Deletion Strategy: We will use the Delete option for this data (entire restriction group only). Note that there is no current way to synch/delete records in a child collection which all assigned products to the restriction group are. A process will need to be implemented to remove individual products from customer-specific restriction groups or customers from standard restriction groups.
Field mapping: Restriction groups
Field Name | ERP Source (orctl) | Configured Commerce Destination (RestrictionGroup) | Notes |
---|---|---|---|
Name |
Ccctlk(2,6) |
Name |
Retrieve records from ORCTL Cccono=0 Ccapid = OE Ccctlk = * + restriction group code |
Description |
Ccdata(1,30) |
Description |
|
Behavior |
Ccdata(31,1) |
DisplayType |
P = Show by default, Hide by exception A = Hide by default, Show by exception |
Field mapping: Restriction group products
Field Name | ERP Source () | Configured Commerce Destination (RestrictionGroup ProductAddition) | Notes |
---|---|---|---|
Company |
|
Cacono |
Filter for records to retrieve |
Restriction Group |
Carscd |
RestrictionGroupId |
Used for constructing the group name |
Customer # |
Cacsno |
CustomerId |
Used for constructing the group name |
Ship To # |
Cashp# |
|
Used for constructing the group name and assigning the customer |
Product # |
Caitno |
ProductId |
Lookup to Product table |
Product alternate unit of measure
Configured Commerce retrieves Alternate Units of Measure via a direct call to the Prophet 21 database. This refresh is done as a separate step in the Product Refresh
Deletion Strategy: Configured Commerce uses the Delete option for this data.
Field mapping: Product alternate unit of measure
Field Name | ERP Source (imaum) | Configured Commerce Destination (ProductUnitOfMeasure) | Notes |
---|---|---|---|
Product # |
auitno |
Product.ERPNumber |
Lookup to Product |
Unit of Measure |
Auunms |
UnitOfMeasure |
|
Conversion Factor |
Auaumf |
QtyPerBaseUnitOfMeasure |
|
Description |
Auaumd |
Description |
|
Conversion Method |
Aucum1, Aucum2 |
|
If aucum1 = 1 then auaumf is the right conversion factor for #/stocking units (that is base = EA, alt = CS 10 EA/CS) If aucum2 = 1 then must invert represents #/units per stocking unit (that is base = DZ, alt = EA 12 EA/DZ) |
Default Ordering UOM |
itbal.ibdoum |
IsDefault |
Set to 1 if this value matches auunms otherwise 0 Get to itbal using auitno + the default warehouse to 2 digits (that is 01 ) |
Rounding Method |
Auaufc |
RoundingRule |
A = adjust up to next whole (Round Up) R = round normal (Round) Y = no rounding (No Rounding) |
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 (repms) | Configured Commerce Destination (Salesperson) | Notes |
---|---|---|---|
Company # |
Smcono |
|
Used for filtering records to the current company |
Salesperson Number |
Smrp#1 |
SalespersonNumber |
Select only records from contact_salesrep where delete_flag = N |
Name |
smrpnm |
Name |
|
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 (orctl) | Configured Commerce Destination (PaymentMethod) | Notes |
---|---|---|---|
Record Type |
|
|
Pull records from orctl using: Cccono = default company number Ccapid = AR Ccrccd = AT Ccctlk = @TRMS + terms code |
Terms Code |
Cctlk(6) |
Name |
|
Description |
Ccdata(1,30) |
Description |
|
COD/Cash |
|
IsCreditCard |
This terms code requires payment by credit card on the site |
Active Indicator |
|
ActivateOn |
Not overwritten set on initial setup to current date |
Ship codes
The ship codes are refreshed to help support the default ship via codes for customers which is then surfaced in the review and pay page on the site. Configured Commerce has a Carrier/Service structure while most ERPs only have the service. To that end, we will pull in the data and duplicate it between the carrier and the actual service. The end users can then either re-arrange the data as needed or remove the integration steps (Carriers/ShipVias).
Deletion Strategy: We will use Ignore and not delete any ship via records automatically during the refresh due to referential integrity concerns.
Field mapping: Ship codes
Field Name | ERP Source (orctl) | Configured Commerce Destination (PaymentMethod) | Notes |
---|---|---|---|
Record Type |
|
|
Pull records from orctl using: Cccono = 0 Ccapid = OE Ccrccd = CR Ccctlk = CC + ship via code
|
Ship Via Code |
Cctlk(3) |
Name |
Maps to carrier.name, shipvia.shipcode and shipvia.ERPShipCode |
Description |
Ccdata(1,30) |
Description |
|
COD/Cash |
|
IsCreditCard |
This terms code requires payment by credit card on the site must be set manually |
Vendors
The vendors table is imported just to support the vendor to product relationship if needed.
Deletion Strategy: We will use delete option on the table to remove unneeded vendors. If a vendor is already assigned to a product, then the deletion will fail.
Field mapping: Vendor
Field Name | ERP Source (vendr) | Configured Commerce Destination (Vendor) | Notes |
---|---|---|---|
Vendor Number |
Pvvnno |
vendorNumber |
Only include vendors that are associated with products |
Name |
Pvvnm |
Name |
|
Address 1..2 |
Pvvad1,pvvad2 |
Address1, Address2 |
|
City |
Pvvcty |
City |
|
State |
Pvvnst |
State |
|
Postal Code |
Pvvzip |
PostalCode |
|
Country |
Pvctcd |
Country |
Lookup to country table |
Phone |
Pvphno |
|
Phone |
Contact Name |
Pvvcnt |
contactName |
|
Please sign in to leave a comment.