Acumatica v2 Connection Details

Introduction

Connector Version

This documentation is based on version 23.0.8858 of the connector.

Get Started

Acumatica Version Support

The connector supports Acumatica 2017 R2 and later.

Establish a Connection

Connect to Acumatica

In order to connect to the Acumatica data source, you must specify the following connection properties.

• URL: (Required) The base URL for the Acumatica ERP instance. For example: https://domain.acumatica.com/entity/.
• Schema: (Optional) There are two schemas which contains different data. The default one is REST, which uses the Acumatica REST Contract-Based API, and the OData schema, which uses the Acumatica OData API. The OData schema is used to query Acumatica Generic Inquiries.
• Company: (Partially required) Set this to the name of your company or tenant. It is required if Schema is set to OData.
• EndpointVersion: (Optional) The version of the Web Services endpoint. For example: 17.200.001. This applies only to the REST schema.
• EndpointName: (Optional) The name of the Web Services endpoint. For example: Default. This is applies only to the REST schema.

To find out the EndpointVersion and EndpointName for your Acumatica instance, log into Acumatica in a web browser, and then navigate to the Web Service Endpoints page. If necessary, navigate to this page by editing the web browser URL and replacing ScreenId=00000000 (the homepage) with ScreenId=SM207060. If you are redirected back to the homepage, this means your user does not have the necessary permissions to access web services. Under Endpoints properties get the Endpoint Name and Endpoint Version.

Authenticate to Acumatica

There are two authentication methods available for connecting to Acumatica data source, Basic and OAuth.

User Credentials

Set the AuthScheme to Basic and set the User and Password to your login credentials.

OAuth

OAuth requires the authenticating user to interact with Acumatica using the browser, so all OAuth flows require a custom OAuth application. Also, for all flows, set AuthScheme to OAuth. The following sections assume that you have done so.

Desktop Applications

An embedded OAuth application is provided that simplifies OAuth desktop Authentication. Alternatively, you can create a custom OAuth application. See Creating a Custom OAuth App for information about creating custom applications and reasons for doing so.

For authentication, the only difference between the two methods is that you must set two additional connection properties when using custom OAuth applications.

After setting the following connection properties, you are ready to connect:

• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken.
• OAuthClientId: Set this to the client ID in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• CallbackURL: Set this to the Redirect URL in your application settings.

When you connect the connector opens the OAuth endpoint in your default browser. Log in and grant permissions to the application. The connector then completes the OAuth process:

1. Extracts the access token from the callback URL and authenticates requests.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values in OAuthSettingsLocation that persist across connections.

Web Applications

When connecting via a Web application, you need to create and register a custom OAuth application with Acumatica. See Creating a Custom OAuth App for more information about custom applications. You can then use the connector to acquire and manage the OAuth token values.

Get an OAuth Access Token

Set the following connection properties to obtain the OAuthAccessToken:

• OAuthClientId: Set this to the client ID in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings

Then call stored procedures to complete the OAuth exchange:

1. Call the GetOAuthAuthorizationURL stored procedure. Set the CallbackURL input to the callback URL you specified in your application settings. If necessary;, set the Scope parameter to request custom permissions. The stored procedure returns the URL to the OAuth endpoint.
2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL.
3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions.

Once you have obtained the access and refresh tokens, you can connect to data and refresh the OAuth access token either automatically or manually.

Automatic Refresh of the OAuth Access Token

To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

• InitiateOAuth: Set this to REFRESH.
• OAuthClientId: Set this to the client ID in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.
• OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
• OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
• OAuthSettingsLocation: Set this to the location where the connector saves the OAuth token values, which persist across connections.

On subsequent data connections, the values for OAuthAccessToken and OAuthRefreshToken are taken from OAuthSettingsLocation.

Manual Refresh of the OAuth Access Token

The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

Use the RefreshOAuthAccessToken stored procedure to manually refresh the OAuthAccessToken after the ExpiresIn parameter value returned by GetOAuthAccessToken has elapsed, then set the following connection properties:

• OAuthClientId: Set this to the client ID in your application settings.
• OAuthClientSecret: Set this to the client secret in your application settings.

Then call RefreshOAuthAccessToken with OAuthRefreshToken set to the OAuth refresh token returned by GetOAuthAccessToken. After the new tokens have been retrieved, open a new connection by setting the OAuthAccessToken property to the value returned by RefreshOAuthAccessToken.

Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

Create a Custom OAuth App

Acumatica uses the OAuth authentication standard, which requires the authenticating user to interact with Acumatica via the browser. The connector facilitates the OAuth exchange in various ways as described below.

Register an OAuth Application

You can follow the procedure below to obtain the OAuth client credentials, the OAuthClientId and OAuthClientSecret.

1. You use the Connected Applications (SM303010) form to register an OAuth 2.0 or OpenID Connect client application. To register a client application in Acumatica ERP, you need to know the OAuth 2.0 flow that this application implements.
2. When you are registering the client application, you have to be logged in to the tenant whose data the client application needs to access.
3. On the System tab, click Integration. In the navigation pane, navigate to Configure > Connected Applications.
4. In the Client Name box, type the name of the registered application.
5. In the OAuth 2.0 Flow box, select Authorization Code.
6. On the Secrets tab, do the following for each client secret you want to add:
   1. On the tab toolbar, click Add Shared Secret. The Add Shared Secret dialog box opens.
   2. In the Description box, type the description of the shared secret.
   3. Optional: In the Expires On (UTC) box, enter the date and time on which the secret expires.
   4. Copy and save the value that is displayed in the Value box. The client application should use this client secret for authentication in Acumatica ERP.
7. Click OK to save the secret and close the dialog box.
8. On the Redirect URIs tab, do the following for each redirect URI you want to add: On the tab toolbar, click Add Row. In the Redirect URI column of the new row, type the exact redirect URI to which Acumatica ERP should redirect the client application after the client application has been authorized. The redirect URI must be absolute and must not have the fragment part (the part preceded with #). On the form toolbar, click Save. Notice that the client ID has been generated in the Client ID box. The client application should use this client ID along with the client secret for authentication in Acumatica ERP.

Authenticate to Acumatica from a Desktop Application

In order to obtain a token, the client application needs to call the Oauth2 endpoint using various grants depending on the authentication scenarios required. The default OAuthGrantType is CODE, which requires you to follow the steps below. After setting the following connection properties, you are ready to connect:

• OAuthClientId: Set this to your clientId.
• OAuthClientSecret: Set this to your clientSecret.
• InitiateOAuth: Set this to GETANDREFRESH. You can use InitiateOAuth to avoid repeating the OAuth exchange and manually setting the OAuthAccessToken connection property.
• CallbackURL: Set this to the redirect URI configured in the OAuth application.

When you connect, the connector completes the OAuth process:

1. Extracts the access token from the CallbackURL.
2. Obtains a new access token when the old one expires.
3. Saves OAuth values along with geolocation in OAuthSettingsLocation to be persisted across connections.

Acumatica Optimization

Acumatica has introduced an optimization in version 3 of the Contract Based API. When multiple records are retrieved from the Acumatica ERP through an endpoint, the system tries to optimize the retrieval of the records and obtain all needed records in one request to the database. If the optimization fails, the system returns an error, specifying the entities or fields that caused the failure of the optimized request. Mostly the Navigation Views break the optimization because Acumatica is trying to get data from the linked tables, because of this the SELECT * queries from Navigation Views without specifying the key of the parent table are not possible. Our drivers tries to remove the columns that break the optimization from the SELECT * queries.

Some of the Views that break the optimization when selected without specifying the keyfields are JournalVoucher_Details. In order to query the JournalVoucher_Details the ParentBatchNbr should be specified.

SELECT * FROM JournalVoucher_Details where ParentBatchNbr = '0001'

The view can be queried by geting the parent key field in this case ParentBatchNbr from the parent table JournalVoucher.

SELECT * FROM JournalVoucher_Details where ParentBatchNbr IN (SELECT BatchNbr FROM JournalVoucher)

More information on how to bypass the Acumatica optimization can be found here: Limitations.

Important Notes

Configuration Files and Their Paths

• All references to adding configuration files and their paths refer to files and locations on the Jitterbit agent where the connector is installed. These paths are to be adjusted as appropriate depending on the agent and the operating system. If multiple agents are used in an agent group, identical files will be required on each agent.

Advanced Features

This section details a selection of advanced features of the Acumatica connector.

User Defined Views

The connector allows you to define virtual tables, called user defined views, whose contents are decided by a pre-configured query. These views are useful when you cannot directly control queries being issued to the drivers. See User Defined Views for an overview of creating and configuring custom views.

SSL Configuration

Use SSL Configuration to adjust how connector handles TLS/SSL certificate negotiations. You can choose from various certificate formats; see the SSLServerCert property under "Connection String Options" for more information.

Proxy

To configure the connector using private agent proxy settings, select the Use Proxy Settings checkbox on the connection configuration screen.

Query Processing

The connector offloads as much of the SELECT statement processing as possible to Acumatica and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views

The Jitterbit Connector for Acumatica allows you to define a virtual table whose contents are decided by a pre-configured query. These are called User Defined Views, which are useful in situations where you cannot directly control the query being issued to the driver, e.g. when using the driver from Jitterbit. The User Defined Views can be used to define predicates that are always applied. If you specify additional predicates in the query to the view, they are combined with the query already defined as part of the view.

There are two ways to create user defined views:

• Create a JSON-formatted configuration file defining the views you want.
• DDL statements.

Define Views Using a Configuration File

User Defined Views are defined in a JSON-formatted configuration file called UserDefinedViews.json. The connector automatically detects the views specified in this file.

You can also have multiple view definitions and control them using the UserDefinedViews connection property. When you use this property, only the specified views are seen by the connector.

This User Defined View configuration file is formatted as follows:

• Each root element defines the name of a view.
• Each root element contains a child element, called query, which contains the custom SQL query for the view.

For example:

{
    "MyView": {
        "query": "SELECT * FROM Events WHERE MyColumn = 'value'"
    },
    "MyView2": {
        "query": "SELECT * FROM MyTable WHERE Id IN (1,2,3)"
    }
}

Use the UserDefinedViews connection property to specify the location of your JSON configuration file. For example:

"UserDefinedViews", "C:\Users\yourusername\Desktop\tmp\UserDefinedViews.json"

Define Views Using DDL Statements

The connector is also capable of creating and altering the schema via DDL Statements such as CREATE LOCAL VIEW, ALTER LOCAL VIEW, and DROP LOCAL VIEW.

Create a View

To create a new view using DDL statements, provide the view name and query as follows:

CREATE LOCAL VIEW [MyViewName] AS SELECT * FROM Customers LIMIT 20;

If no JSON file exists, the above code creates one. The view is then created in the JSON configuration file and is now discoverable. The JSON file location is specified by the UserDefinedViews connection property.

Alter a View

To alter an existing view, provide the name of an existing view alongside the new query you would like to use instead:

ALTER LOCAL VIEW [MyViewName] AS SELECT * FROM Customers WHERE TimeModified > '3/1/2020';

The view is then updated in the JSON configuration file.

Drop a View

To drop an existing view, provide the name of an existing schema alongside the new query you would like to use instead.

DROP LOCAL VIEW [MyViewName]

This removes the view from the JSON configuration file. It can no longer be queried.

Schema for User Defined Views

User Defined Views are exposed in the UserViews schema by default. This is done to avoid the view's name clashing with an actual entity in the data model. You can change the name of the schema used for UserViews by setting the UserViewsSchemaName property.

Work with User Defined Views

For example, a SQL statement with a User Defined View called UserViews.RCustomers only lists customers in Raleigh:

SELECT * FROM Customers WHERE City = 'Raleigh';

An example of a query to the driver:

SELECT * FROM UserViews.RCustomers WHERE Status = 'Active';

Resulting in the effective query to the source:

SELECT * FROM Customers WHERE City = 'Raleigh' AND Status = 'Active';

That is a very simple example of a query to a User Defined View that is effectively a combination of the view query and the view definition. It is possible to compose these queries in much more complex patterns. All SQL operations are allowed in both queries and are combined when appropriate.

SSL Configuration

Customize the SSL Configuration

By default, the connector attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

REST Data Model

The connector models the Acumatica API as relational tables. Any changes to the remote data are immediately reflected in your queries; the table definitions are dynamically retrieved. When you connect, the connector connects to Acumatica and gets the list of tables and the metadata for the tables by calling the appropriate Web services.

API limitations and requirements are documented in this section; you can use the SupportEnhancedSQL feature, set by default, to circumvent most of these limitations.

Tables

Shows default table definitions from Acumatica. Your actual tables may differ depending on your customizations. These tables were recorded as of version 17.200.001, but later versions may include more tables and fields than displayed here. The Jitterbit Connector for Acumatica reads table and column metadata dynamically, so it will include both your customizations and any new features that are added to standard Acumatica entities in the future. You just need to set the URL connection property to the latest version to get the changes.

Stored Procedures

Stored Procedures are function-like interfaces to Acumatica. They can be used to peform extra tasks that do not fit into the

standard operation of directly interacting with a table.

Limitations

Custom Fields

By default, the table and view schemas describe the column behavior of system fields as well as custom fields. You can set the IncludeCustomFields property to false if you want to work only with system fields. On a SELECT * FROM EntityName query, the custom fields will not be returned even though IncludeCustomFields is set to true. The Custom fields will be returned only when they are specified on projections. For example:

SELECT Custom_FieldA, Custom_FieldB, Custom_FieldC FROM EntityName

The requirement to explicitly select the custom fields is due to a limitation with the Acumatica API. It will not return results when too much overall data is selected per record. It is not possible to dynamically calculate at what point Acumatica will cut off results. You should only select the columns you need and not everything to keep things working smoothly.

In case a group of custom fields are important to your use case and the Acumatica API cannot return them in a single response, it is recommended to setup an extended endpoint and include the custom fields in it. After this change, you should be able to retrieve them as non-custom fields from the extended endpoint.

An alternative to extending an existing endpoint is to split the main query into two or more subqueries and distribute the selected custom fields between them. Lastly, you can join the subqueries on the primary key column as in the example below.

SELECT Customer_1.CustomerID, Customer_1.Custom_Timezone, Customer_1.Custom_ProductGroup, Customer_2.Custom_ContactCategory, Customer_2.Custom_DiscountGroup
FROM Customer Customer_1 JOIN Customer Customer_2
ON Customer_1.CustomerID = Customer_2.CustomerID

Linked and Detail Entities

The Linked and Detail entities to main entity are exposed in the main entity table as aggregate columns starting with the 'Linked' prefix. They are also exposed as separate views using the naming format 'MainEntityName_LinkedEntityName'. For example, the Customer table exposes the linked entity 'Contacts' as the aggregate field 'LinkedContacts'. The linked entity Contacts is also exposed as the separate view Customer_Contacts.

Due to Acumatica REST API limitations, the Linked and Detail entities cannot be retrieved when requesting multiple main entity records. For this reason, when querying the main entity, without specifying the primary key in the WHERE clause conditions, the linked aggregate fields are returned as null. For example, in order to retrieve the aggregate values of the linked entity Contacts in the Customer table, you can execute one of the queries below.

SELECT CustomerID, LinkedContacts FROM Customer WHERE customerId = '100009'

You can also make use of the IN operator and subqueries as below, in order to retrieve the Linked and Detail entities for multiple parent records.

SELECT CustomerID, LinkedContacts FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM Customer)

Additionally, to query the exposed Linked and Detail entities, specifying the parent entity primary key in the WHERE clause conditions, is required. For example:

SELECT * from Customer_Contacts where ParentCustomerID = '100009'

Excluded Columns

Acumatica has introduced an optimization in version 3 of the Contract Based API. When multiple records are retrieved from the Acumatica ERP through an endpoint, the system tries to optimize the retrieval of the records and obtain all needed records in one request to the database. If the optimization fails, the system returns an error, specifying the entities or fields that caused the failure of the optimized request. To prevent the error from occurring, we need to do one of the following:

• Get all the records one by one by specifying the key fields.
• Get only the fields that don't break the optimization.

Create the schema by calling the CreateSchema if you have not already done so.

Add the additional parameters in the .rsd schema files below.

• key="true"
• other:exclude="true"

These two parameters are needed so the new entities don't return an error in SELECT * FROM Table queries. The example below is a rsd snippet that shows how these parameters are used.

In the example Contact.rsd, Duplicate and AddressValidated are fields that break the Acumatica optimization so they have the additional parameter other:exclude="true". When a SELECT * FROM Contact query is done, the driver will get all fields except those with the other:exclude="true" parameter. However, the omitted fields will still appear on your result set as null. The data provider will request the excluded fields from the endpoint only if they are explicitly stated in the SELECT clause or if the primary key column/s are specified.

In the example Contact.rsd, ContactID is the primary key so the additional parameter key="true" is added. When a SELECT * FROM Contact WHERE ContactID='contactid' is done, the driver will get all fields from the API including the Duplicate and AddressValidated.

<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2">
  <rsb:info title="Contact" description="Contact is an auto-generated table." other:tablehref="https://try.acumatica.com/ISV/entity/Default/17.200.001/Contact" other:tablekind="EntitySet"/>
    <attr name="LanguageOrLocale" xs:type="string" columnsize="2000" readonly="false" description="null" other:entityname="Contact" other:schemanamespace="Contact" other:internalcolumnname="LanguageOrLocale"/>
    <attr name="DuplicateFound" xs:type="bool" readonly="false" description="null" other:internalcolumnname="DuplicateFound"/>
    <attr name="JobTitle" xs:type="string" columnsize="2000" readonly="false" description="null" other:internalcolumnname="JobTitle"/>
    <attr name="ContactID" xs:type="int" key="true" readonly="false" description="null" other:queue="0" other:internalkey="ContactID" other:internalcolumnname="ContactID"/>
    .......
    <attr name="Duplicate" xs:type="string" columnsize="2000" readonly="false" description="null" other:exclude="true" other:internalcolumnname="Duplicate"/>
    .......
    <attr name="AddressValidated" xs:type="bool" readonly="false" description="null" other:exclude="true" other:internalcolumnname="AddressValidated"/>
    .......
      <rsb:set attr="other:tablekind" value="EntitySet" />
  <rsb:set attr="tablename" value="Contact" />
  <rsb:set attr="description" value="Contact is an auto-generated table." />
  <rsb:set attr="entity" value="Contact" />
  <rsb:set attr="other:tablehref" value="https://try.acumatica.com/ISV/entity/Default/17.200.001/Contact" />
  <rsb:set attr="supportgetdeleted" value="true" />

  <rsb:script method="GET">
    <rsb:call op="acumaticaadoExecuteSearch">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="POST">
    <rsb:call op="acumaticaadoExecuteInsert">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="MERGE">
    <rsb:call op="acumaticaadoExecuteUpdate">
      <rsb:push/>
    </rsb:call>
  </rsb:script>

  <rsb:script method="DELETE">
    <rsb:call op="acumaticaadoExecuteDelete">
      <rsb:push/>
    </rsb:call>
  </rsb:script>
</rsb:script>

Create User Defined Views

A simple and effective way to automate the process of getting all records one by one by specifying the key fields are the User Defined Views. User Defined Views are predefined queries stored in a local JSON file, which can be queried from the driver just as the existing views.

As stated in the above section, the excluded fields will be retrieved if the primary key is specified. You can use the driver's enhanced SQL support to automatically do this for each record by appending an IN subquery to your main query. From the subquery, you should be selecting only the primary key column as in the example below.

SELECT * FROM Contact WHERE ContactId IN (SELECT ContactId FROM Contact)

You can then add this query under the "query" field of a local created JSON file (ex. C:\Users\user\Desktop\CustomViews.json).

{
    "AllContacts": {
        "query": "SELECT * FROM Contact WHERE ContactId IN (SELECT ContactId FROM Contact)"
    },
    "AllCustomers": {
        "query": "SELECT * FROM Customer WHERE CustomerId IN (SELECT CustomerId FROM Customer)"
    }
}

Lastly, you can directly query the defined views (AllContacts and AllCustomers) after stating the Custom Views file location in the UserDefinedViews property under the Other connection parameter.

Other='UserDefinedViews=C:\Users\User\Desktop\CustomViews.json;'

You can also execute a query to the sys_tables view to verify if the custom views are added. You should be able to find them under the UserViews schema.

SELECT * FROM sys_tables where TableType='view'

Tables

The connector models the data in Acumatica as a list of tables in a relational database that can be queried using standard SQL statements.

Jitterbit Connector for Acumatica Tables

Account

Account is an auto-generated table.

Columns

AccountByPeriodInquiry

AccountByPeriodInquiry is an auto-generated table.

Columns

AccountBySubaccountInquiry

AccountBySubaccountInquiry is an auto-generated table.

Columns

AccountDetailsInquiry

AccountDetailsInquiry is an auto-generated table.

Columns

AccountLocation

AccountLocation is an auto-generated table.

Columns

AccountSummaryInquiry

AccountSummaryInquiry is an auto-generated table.

Columns

Adjustment

Adjustment is an auto-generated table.

Columns

AttributeDefinition

AttributeDefinition is an auto-generated table.

Columns

Bill

Bill is an auto-generated table.

Columns

BusinessAccount

BusinessAccount is an auto-generated table.

Columns

Carrier

Carrier is an auto-generated table.

Columns

Case

Case is an auto-generated table.

Columns

CashSale

CashSale is an auto-generated table.

Columns

Check

Check is an auto-generated table.

Columns

Contact

Contact is an auto-generated table.

Columns

Currency

Currency is an auto-generated table.

Columns

Customer

Customer is an auto-generated table.

Columns

CustomerClass

CustomerClass is an auto-generated table.

Columns

CustomerLocation

CustomerLocation is an auto-generated table.

Columns

CustomerPaymentMethod

CustomerPaymentMethod is an auto-generated table.

Columns

CustomerPriceClass

CustomerPriceClass is an auto-generated table.

Columns

Discount

Discount is an auto-generated table.

Columns

DiscountCode

DiscountCode is an auto-generated table.

Columns

Email

Email is an auto-generated table.

Columns

Employee

Employee is an auto-generated table.

Columns

Event

Event is an auto-generated table.

Columns

FinancialPeriod

FinancialPeriod is an auto-generated table.

Columns

FOBPoint

FOBPoint is an auto-generated table.

Columns

InterBranchAccountMapping

InterBranchAccountMapping is an auto-generated table.

Columns

InventoryAllocationInquiry

InventoryAllocationInquiry is an auto-generated table.

Columns

InventoryReceipt

InventoryReceipt is an auto-generated table.

Columns

InventorySummaryInquiry

InventorySummaryInquiry is an auto-generated table.

Columns

Invoice

Invoice is an auto-generated table.

Columns