Reference for connectors for Acumatica
Of note in general for Acumatica, the names of the fields may appear differently as exposed via OData versus how they appear inside the Generic Inquiry functionality. We tried to use the Odata-exposed names when possible in the below documentation.
Order history
ISC retrieves Order history data from Acumatica via a direct call. The data is stored within the B2B 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 B2B are immediately added to the B2B 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 B2B for large history files. B2B 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
ISC 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 B2B 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
ISC 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
ISC 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, B2B will also submit the authorization token information. The SalesOrder endpoint is used for orders.
The standard B2B 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) | ISC Destination (Customer) | Notes |
|---|---|---|---|
| Acumatica Order Submit Field | ISC 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
ISC 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.
ISC 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 B2B 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
ISC 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: B2B retrieves all customer records and physically sets the IsActive flag directly from the data, so the Ignore delete action will be used. B2B must use Ignore, since the data will be retrieved in three separate sweeps.
Field mapping: Customer (bill-to)
| Field Name | ERP Source | ISC 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 | ISC 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. |
|
|
Contact.Email |
|
|
|
Phone |
Contact.Phone1 |
Phone |
|
Customer products
Customer-specific product data will be retrieved by B2B via a direct call to Acumatica.
Deletion Strategy: ISC uses the Delete option to physically remove records that are no longer valid. B2B also uses Delta Datasets.
Field mapping: Customer product
| Field Name | ERP Source (INItemXRef) | ISC 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
ISC 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 | ISC Destination (ProductWarehouse) | Notes |
|---|---|---|---|
|
Product Number |
InventoryItem.Inventory ID |
Product |
Product lookup |
|
Warehouse |
Site.SiteCD |
Warehouse |
Warehouse lookup |
|
Quantity Available |
ItemStats.qtyOnHand |
ERP Qty Available |
|
Inventory Refresh
ISC 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 B2B Commerce.
Field mapping: Product refresh product
| Field Name | ERP Source (Inventory Item) | ISC 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
ISC 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) | ISC 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) | ISC Destination (Salesperson) | Notes |
|---|---|---|---|
|
Salesperson Number |
SalesPersonID |
SalespersonNumber |
|
|
Name |
Descr |
Name |
|
|
Email Address |
Contact.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 B2B 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) | ISC 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 |
Need help?