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

In Acumatica, the names of the fields may appear differently as exposed via OData versus how they appear inside the Generic Inquiry functionality. This article uses the Odata-exposed names when possible.

Order history

Configured Commerce retrieves Order history data from Acumatica via a direct call. The data is stored within the Configured Commerce database and refreshed on a regular interval, typically once a day.

To limit the number of order records analyzed, the integration process uses a lookback period in the query. This query is performed against the Acumatica order modified date.

Orders placed via Configured Commerce are immediately added to the Configured Commerce order history tables. This ensures that a user will immediately see their orders on the Order History pages under My Account. Because the Acumatica connector order submission uses the real-time API, the ERP order # should also be present.

This data is a combination of the SalesOrder, SOBillingAddress, and SOShipmentAddress Objects.

Implementation Note Lookback Days: Optimizely s strategy is to limit the amount of data being reviewed and transmitted to Configured Commerce for large history files. Configured Commerce only looks for records that have changed within a relatively short period of time, with a default of 5 days to cover any missed refreshes, weekends and holidays. The number can be modified based on implementation preferences. It is coded directly into the Generic Inquiry.

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 (SalesOrder) Commerce Table.Field (OrderHistory) Notes

ERP Order #

OrderNbr

ERPOrderNumber

 

Order Status

Status

Status

 

Order Date

OrderDate

OrderDate

 

Customer #

CustomerID

CustomerNumber

 

Ship-To #

CustomerLocationID

CustomerSequence

 

Customer PO #

CustomerOrder

CustomerPO

 

Ship Via

ShipVia

ShipCode

 

Salesperson

DefaultSalesperson

Salesperson

 

Billing Information: Address

SOBillingAddress.Addressline1/2

BTAddress1..2

 

Billing Information: City

SOBillingAddress.City

BTCity

 

Billing Information: State

SOBillingAddress.State

BTState

 

Billing Information : Zip

SOBillingAddress.PostalCode

BTPostalCode

 

Billing Information Country

SOBillingAddress.Country

BTCountry

 

Shipping Information: Address

SOBillingAddress.Addressline1/2

STAddress1..2

 

Shipping Information: City

SOBillingAddress.City

STCity

 

Shipping Information: State

SOBillingAddress.State

STState

 

Shipping Information: Postal Code

SOBillingAddress.PostalCode

STPostalCode

 

Shipping Information: Country

SOBillingAddress.Country

STCountry

 

Subtotal

LineTotal

ProductTotal

 

Order Discount

DiscTot

OrderDiscountAmount

 

Shipping

FreightTot

ShippingCharges

 

Misc. Charges

MiscTot

OtherCharges

 

Sales Tax

TaxTot

TaxAmount

 

Order Total

CuryOpenOrderTotal

OrderTotal

 

Field mapping: Order history detail

Field Name ERP Table.Field (SalesOrderLines) Commerce Table.Field (OrderHistoryLine) Notes

ERP Order #

SalesOrder.OrderNbr

OrderHistoryId

 

Line Number

LineNbr

LineNumber

 

Customer #

SalesOrder.CustomerID

CustomerNumber

 

Line Type

 

LineType

Static Value = 'Product'

Item #

InventoryID

ProductERPNumber

 

Item Description

InventoryID_Description

Description

 

Unit of Measure

UOM

UnitOfMeasure

 

QTY Ordered

OrderQty

QtyOrdered

 

QTY Shipped

ShippedQty

QtyShipped

 

Unit Price

UnitPrice

UnitNetPrice

 

Extended Price

ExtPrice

LineTotal

 

Ship Date

ShipDate

LastShipDate

 

Shipments

Configured Commerce's shipment refresh gathers shipment information from the Shipments object and individual package information from the Packages object.. This refresh typically runs once per day and uses a lookback period from which to run the query.

To limit the number of order records analyzed, the integration process uses a lookback period in the query. This query is used against only the transaction date, since we do not expect records to change once invoiced.

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 (Shipments) Commerce Table.Field (Shipment) Notes

ERP Order Number

OrderNbr

ERPOrderNumber

 

Shipment #

ShipmentNbr

ShipmentNumber

 

Shipment Date

Shipment Date

ShipmentDate

 

Field mapping: Order history shipment packages

Field Name ERP Table.Field (Packages) Commerce Table.Field (ShipmentPackage) Notes

Shipment #

ShipmentNbr

ShipmentID

 

Ship Via

ShipVia

Carrier

 

Tracking #

TrackingNumber

TrackingNumber

 

 

Invoices

Invoice data is retrieved from Acumatica via an Odata call to the ARInvoice object. The data is stored within the Configured Commerce database and refreshed on a regular interval, typically once a day.

To limit the number of invoice records analyzed, the integration process uses a lookback period in the query.

Refer to the Implementation Notes in Order History Refresh for additional information.

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 (ARInvoice) Commerce Table.Field (InvoiceHistory) Notes

Invoice #

ReferenceNbr

InvoiceNumber

 

Invoice Date

Date

InvoiceDate

 

Invoice Due Date

Due Date

DueDate

 

Invoice Type

 

InvoiceType

Static value = Invoice

Invoice Status

Status

Status

 

Open Invoice Flag

 

IsOpen

If Status = "Open", then "Open".

Customer #

CustomerID

CustomerNumber

 

Ship-To #

CustomerLocationID

CustomerSequence

 

Currency

CuryID

CurrencyCode

 

Customer PO #

RefNbr

CustomerPO

 

Terms

TermsID

Terms

 

Salesperson

SalesPersonID

Salesperson

 

Subtotal

LineTotal

ProductTotal

 

Sales Tax

TaxTotal

TaxAmount

 

Discount Amount

DiscTot

DiscountAmount

 

Misc Charges

MiscTot

OtherCharges

 

Invoice Total

OrigDocAmount

InvoiceTotal

 

Current Balance

CuryDocBal

CurrentBalance

 

 

 

Field mapping: Invoice history detail

Field Name ERP Web Service Field (ARTran) Commerce Table.Field (InvoiceHistoryLine) Notes

Invoice #

ARInvoice.RefNbr

InvoiceHistoryId

 

Line Number

LineNbr

LineNumber

 

Line Type

 

LineType

Static Value = 'Product'

ERP Order #

SOOrderNbr

ERPOrderNumber

 

Item #

InventoryID

ProductERPNumber

 

Item Description

InventoryID_Description

Description

 

Unit of Measure

UOM

UnitOfMeasure

 

Warehouse

SiteID

Warehouse

 

Qty Invoiced

Qty

QtyInvoiced

 

Unit Price

UnitPrice

UnitPrice

 

Extended Price

ExtPrice

LineTotal

 

Existing orders

Configured Commerce calls the payment gateway (typically Authorize.net for Acumatica) directly to authorize a user s credit card. The authorization information is passed into Acumatica via the order submission process using an authorization token returned by the payment gateway. Card tokens will also be passed as a CustomerPaymentProfile.

Credit card processing

Order submit API

Configured Commerce will submit orders to Acumatica via the API. Typical order submission data will be included: bill-to information, ship-to information, and line item information. If a credit card was used for the order, Configured Commerce will also submit the authorization token information. The SalesOrder endpoint is used for orders.

The standard Configured Commerce connector mapping for API calls is hard-coded but implemented with pipelines to allow the implementer to extend the integration to incorporate additional mapping information for other fields or adjust the standard mappings. The following shows the default approach to mapping the API call.

Field mapping: Order submit

Field Name ERP Source (customer) Configured Commerce Destination (Customer) Notes
Acumatica Order Submit Field Configured Commerce Field Notes  
BaseCurrencyID CustomerOrder.CurrencyID    
BillToAddress.ID Customer.ERPNumber Lookup to BillTo  
BillToAddress.AddressLine1 CustomerOrder.BTAddress1    
BillToAddress.AddressLine2 CustomerOrder.BTAddress2    
BillToAddress.City CustomerOrder.BTCity    
BillToAddress.Country CustomerOrder.BTCountry    
BillToAddress.PostalCode CustomerOrder.BTPostalCode    
BillToAddress.State CustomerOrder.BTState    
CurrencyID CustomerOrder.CurrencyID    
CustomerID Customer.ERPNumber Lookup to ShipTo  
Date Today's DateTime Stamp    
Details.RowNumber Autonumber for Line Item    
Details.InventoryID Product.ERPNumber    
Details.Qty OrderLine.QtyOrdered    
Details.UOM Orderline.UnitOfMeasure    
Details.TaxCategory OrderLine.TaxCode    
Details.UnitPrice OrderLine.UnitNetPrice    
DisableAutomaticDiscountUpdate   This will be driven by a flag in the Acumatica connector settings - determines whether ERP will recalculate prices with web promos.  
DiscountDetails.rowNumber Incremental by discount    
Discount.Description Promotion.description    
Discount.DiscountableAmount Total value of line item affected by discounts (before discounts)    
Discount.DiscountableQty Total quantity of the line item affected by discounts    
Discount.DiscountAmount Total value of the discount to the line item.    
Discount.DiscountCode Promotion.name    
Discount.DiscountPercent Percentage discount (0 if none for this discount)    
Discount.FreeItem null, otherwise include Item ERPNumber for free item.    
Discount.FreeItemQuantity 0, otherwise include Item quantity for free item.    
Discount.ManualDiscount TRUE    
Discount.SequenceID Count    
EffectiveDate Current Datetime Stamp    
ExternalRef Web Order Number    
LocationID CustomerOrder.ERPNumber For BillTo  
OrderType SA Set value for Order Type.  
PaymentCardIdentifier Card ID retrieved from credit card submit call    
PaymentMethod CustomerOrder.TermsCode    
PaymentProfileID Authorize.Net Token (Token2)    
PreAuthorizationNbr CreditCardTransaction.PNRef    
PreAuthorizedAmount CreditCardTransaction.Amount    
RequestedOn Current Datetime Stamp    
ShipToAddress.AddressLine1 CustomerOrder.STAddress1    
ShipToAddress.AddressLine2 CustomerOrder.STAddress2    
ShipToAddress.City CustomerOrder.STCity    
ShipToAddress.Country CustomerOrder.STCountry    
ShipToAddress.PostalCode CustomerOrder.STPostalCode    
ShipToAddress.State CustomerOrder.STState    
ShipToAddressOverride True if One-time ship-to, False otherwise.    

Order submission

Tax calculation

Acumatica supports Avatax out of the box and calls will be made directly to Avalara for this functionality.

Tax (order simulation)

Pricing API

Configured Commerce retrieves pricing via a refresh crafted specifically for Acumatica. Note that unlike many integrations, Acumatica does not have an ecommerce-focused inventory/pricing endpoint. Real-Time Inventory is retrieved separately.

Configured Commerce has a custom Integration Processor (PricingRefreshAcumatica) to read Acumatica price data and incorporate them into the correct price types in the price matrix.

Field mapping: Pricing

Field Name ERP Table (ARSalesPrice) Commerce Table.Field (PriceMatrix) Notes

Price Break Quantitys

BreakQty

BreakQty01-11

Ordered from smallest to largest.

Currency Code

CuryID

Currency Code

 

CustomerKeyPart

CustomerID OR CustPriceClassID

Customer Key Part

The same field is used in Configured Commerce for Customers and Customer Price Classes.

U/M

Unit of Measure

UnitOfMeasure

 

Warehouse

SiteID

Warehouse

 

Record Type

PriceType AND IsPromotionalPrice

RecordType

Combine these two values to determine the Record Type

Product

InventoryID

Product

 

Active On

EffectiveDate

ActiveOn

Logic exists to flatten all records with Effective Dates in the past and the same expiration date.

Deactivate On

ExpirationDate

DeactivateOn

Only unexpired records are included.

Pricing tables

Customer

Configured Commerce retrieves Bill-to customer data from the Customer object and Ship-to data from the Location object. We do this in three separate sweeps. First the Bill-Tos are retrieved, then the Ship-Tos are retrieved, then any Customer-Specific data is retrieved, due to issues joining the customer and locations tables in the same inquiry.

This information comes from a series of joined tables (BAccount, Location, Address, Customer, LocationExtAddress, Contact) and will be marked individually in the ERP Source fields.

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 three separate sweeps.

Field mapping: Customer (bill-to)

Field Name ERP Source Configured Commerce Destination (Customer) Notes

Customer #

BAccount.AcctCD

CustomerNumber

ERPNumber

We only pull over 'Customer' and 'Customer & Vendor' account types

Ship-To #

 

CustomerSequence

Static Value = Blank

Customer Name

BAccount.AcctName

Company

 

Address Line 1-2

Address.AddressLine1/2

Address1/2

 

City

Address.city

City

 

State

Address.state

StateId

Lookup being used must exist in ISC

Country

Address.CountryID

CountryId

Must match country abbreviation to be valid, uses lookup

Postal Code

Address.PostalCode

PostalCode

 

Tax Code 1

LocationExtAddress.CTaxZoneID

TaxCode1

While populated, we rely on calls to API to determine the tax amount in cart.

Price Code

LocationExtAddress.CPriceClassID

PriceCode

 

Warehouse

LocationExtAddress.CSiteID

DefaultWarehouseId

Must match a valid warehouse

Field mapping: Customer (ship-to)

Field Name ERP Source Configured Commerce Destination (Customer) Notes

Customer #

BAccount.AcctCD

CustomerNumber

ERPNumber

We only pull over 'Customer' and 'Customer & Vendor' account types

Ship-To #

Location.LocationCD

CustomerSequence

 

Customer Name

BAccount.AcctName

Company

 

Address Line 1-2

Address.AddressLine1/2

Address1/2

 

City

Address.city

City

 

State

Address.state

StateId

Lookup being used must exist in ISC

Country

Address.CountryID

CountryId

Must match country abbreviation to be valid, uses lookup

Postal Code

Address.PostalCode

PostalCode

 

Tax Code 1

LocationExtAddress.CTaxZoneID

TaxCode1

While populated, we rely on calls to API to determine the tax amount in cart.

Price Code

LocationExtAddress.CPriceClassID

PriceCode

 

Warehouse

LocationExtAddress.CSiteID

DefaultWarehouseId

Must match a valid warehouse

Field mapping: Customer (customer data)

Field Name ERP Table.Field Commerce Table.Field Notes

Customer #

Customer.AcctCD

CustomerNumber

ERPNumber

Lookup

Pricing Customer

Customer.BillCustomerID

Pricing Customer

 

Currency

Customer.CuryID

Currency

Lookup - currency code must match in ISC.

Customer Type

Customer.CustomerClassID

CustomerType

 

Default Payment Method

Customer.DefPaymentMethodID

DefaultPaymentMethod

Must match a terms code.

Email

Contact.Email

Email

 

Phone

Contact.Phone1

Phone

 

Customer products

Customer-specific product data will be retrieved by Configured Commerce via a direct call to Acumatica.

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 (INItemXRef) Configured Commerce Destination (CustomerProduct) Notes

Record Filter

Alternate Type

 

Only pull records WHERE

AlternateType = "Customer Part Number"

ERP Part #

InventoryID

ProductId

 

Customer #

BAccountID

CustomerId

Field name is because this table also pulls Vendor Part #s.

Customer Part #

AlternateID

Name

Customer s product number

Unit of Measure

UOM

UnitOfMeasure

This will become the default unit of measure for the customer if specified

Customer tables

Configured Commerce retrieves Inventory via a Generic Inquiry to Acumatica. We retrieve the amount available for shipping and expect that real-time inventory will be used as the primary function for inventory.

This inquiry joins the InventoryItem, ItemStats, and Site objects.

Deletion Strategy: This refresh uses the ignore option.

Field mapping: Alternate units of measure

Field Name ERP Source Configured Commerce Destination (ProductWarehouse) Notes

Product Number

InventoryItem.Inventory ID

Product

Product lookup

Warehouse

Site.SiteCD

Warehouse

Warehouse lookup

Quantity Available

ItemStats.qtyOnHand

ERP Qty Available

 

Configured Commerce uses real-time calls to obtain pricing and availability via real-time calls. Real-time calls return the AvailableforShipping value. The below refresh will also be used to supplement the data, if needed.

Inventory tables

Products

The Product Refresh process retrieves item/product information directly from Acumatica. ISC's Connector looks at the InventoryItem object, which contains both StockItems and NonStockItems. Only records marked as active within Acumatica will be retrieved in the refresh job.

Depending on your business setup for NonStockItems, you may need to include additional logic in the base Generic Inquiry to retrieve only a subset of records, based on the type of NonStockItem.

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: We use Set Field to make items deactivated in Configured Commerce.

Field mapping: Product refresh product

Field Name ERP Source (Inventory Item) Configured Commerce Destination (Product) Notes

Active/Deactivate

Item Status

ActivateOn

DeactivateOn

Only pulling records with statustype 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 Inactive will be archived by setting their deactivateon to the current date as part of the DeleteAction.

 

ERP Item #

Inventory ID

Name

ERPNumber

This is the primary natural key to the table

Item Title &

Item Description

Description

ShortDescription

ERPDescription

Short description will not be overwritten it is expected to be maintained in the application or from a PIM

URL Segment

Inventory ID

URLSegment

Appending the item number to ensure uniqueness

Manufacturer s Part #

Manufacturer Model

ManufacturerItem

 

Tax Code/Class

Tax Category

TaxCode1

 

Unit of Measure

Sales Unit

UnitOfMeasure

 

Unit Weight

weight

ShippingWeight

 

Price Code

Price Class

PriceCode

 

Product alternate units of measure

Configured Commerce retrieves Product Alternate Units of Measure via a Generic Inquiry to Acumatica. This refresh can be modified or copied to use specific record types or relationships needed for a given implementation.


This refresh joins the InventoryItem and INUnit tables to return only the applicable values for a given product.

Deletion Strategy: This refresh uses the delete option to remove unsupported units of measure.

Field mapping: Alternate units of measure

Field Name ERP Source (INUnit) Configured Commerce Destination (Alternate Unit of Measure) Notes

Product Number

Inventory ID

ERPNumber

Product lookup

Quantity per base U/M

ConversionFactor

Quantity per U/M

 

Unit of Measure

ToUnit

U/M

 

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.

Note that Acumatica's SalesPerson object does not contain contact information for your sales force. We have joined the Contact object to get this information.

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

Field mapping: Salesperson refresh

Field Name ERP Source (Salesperson / PX.Object.AR.SalesPerson) Configured Commerce Destination (Salesperson) Notes

Salesperson Number

SalesPersonID

SalespersonNumber

 

Name

Descr

Name

 

Email Address

Contact.EMail

Email

 

Phone Number

Contact.Phone1

Phone  

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 . The description, by default, uses Acumatica's friendly "Descr"/Description field.

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 (PaymentMethod) Configured Commerce Destination (PaymentMethod) Notes

Active Records

IsActive = True, UseForAR = True

 

This is used as the filter in Acumatica's Generic Inquiry to retrieve only active payment methods

Terms Code

PaymentMethodID

Name

 

Description

Descr

Description

 

Payment Type

PaymentType = "Credit Card"

IsCreditCard

Payment Methods marked as credit cards in Acumatica will be marked the same way in ISC.

Active Indicator

 

ActivateOn

Not overwritten set on initial setup to current date