A+ connectors reference

  • Updated

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:

  • IBAVCS average cost
  • IBUSCS user cost
  • UBSTCS standard cost

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