このページでは、CData Cloud でのAcumatica への接続の確立 のガイド、利用可能なリソースに関する情報、および使用可能な接続プロパティのリファレンスについて説明します。

Acumatica への接続

接続の確立 は、CData Cloud にデータベースを作成するためのAcumatica への認証方法と必要な接続プロパティの設定方法について示します。

CData Cloud サービスからデータにアクセス

利用可能な標準サービスを経由してAcumatica からデータにアクセスする方法と、CData Cloud の管理については、CData Cloud ドキュメント で詳しく説明します。

Database タブで対応するアイコンを選択して、Acumatica に接続します。必須プロパティはSettings にリストされています。Advanced タブには、通常は必要ない接続プロパティが表示されます。

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

Authenticating 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

Web Applications

When connecting via a Web application, you need to create and register a custom OAuth application with Acumatica. See カスタムOAuth アプリの作成 for more information about custom applications. You can then use the Cloud 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 Cloud saves the OAuth token values, which persist across connections.

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.

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 qyery 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: 制限.

SSL 設定のカスタマイズ

デフォルトでは、Cloud はサーバーの証明書をシステムの信頼できる証明書ストアと照合してSSL / TLS のネゴシエーションを試みます。

別の証明書を指定するには、利用可能なフォーマットについてSSLServerCert プロパティを参照してください。

Firewall またはProxy 経由の接続

HTTP プロキシ

Windows のシステムプロキシ経由の接続では、接続プロパティを追加で設定する必要はありません。他のプロキシに接続するには、ProxyAutoDetect をfalse に設定します。

さらにHTTP プロキシへの認証には、ProxyServer とProxyPort に加えてProxyAuthScheme、ProxyUser、およびProxyPassword を設定します。

その他のプロキシ

次のプロパティを設定します。

• プロキシベースのファイヤーウォールを使用するには、FirewallType、FirewallServer、およびFirewallPort を設定します。
• 接続をトンネルするには、FirewallType をTUNNEL に設定します。
• 認証するには、FirewallUser とFirewallPassword を設定します。
• SOCKS プロキシへの認証には、さらにFirewallType をSOCKS5 に設定します。

Cloud はAcumatica API をリレーショナルテーブルとしてモデル化します。リモートデータへのあらゆる変更はクエリに即時に反映されます。テーブル定義は動的に取得されます。接続すると、Cloud はAcumatica に接続し、適切なWeb サービスを呼び出してテーブルのリストとテーブルのメタデータを取得します。

このセクションでは、API の制限および要件について説明します。既定のSupportEnhancedSQL 機能を使って、これらの制限の大半を回避できます。

テーブル

Acumatica のデフォルトテーブル定義を表示します。実際の表示は、カスタマイズにより異なる場合があります。これらのテーブルは version 17.200.001 の時点で記録されたものですが、それ以降のバージョンにはここに表示されているものより多くのテーブルとフィールドが含まれることがあります。CData Cloud はテーブル およびカラムのメタデータを動的に読み込みます。そのため、カスタマイズ内容と今後の標準 Acumatica エンティティに追加される新機能の両方が含まれます。変更を取得するには、Url 接続プロパティを最新バージョンに設定するだけです。

ストアドプロシージャ

ストアドプロシージャ は、Acumatica のファンクションライクなインターフェースです。これらは、テーブルと直接対話する標準的な操作には適さない追加のタスクを 実行するために使用できます。

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>
    

Creating 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'

Cloud はAcumatica のデータを、標準のSQL ステートメントを使用してクエリできるリレーショナルデータベースのテーブルのリストとしてモデル化します。

CData Cloud - Acumatica テーブル

Account is an auto-generated table.

Columns

AccountByPeriodInquiry is an auto-generated table.

Columns

AccountBySubaccountInquiry is an auto-generated table.

Columns

AccountDetailsInquiry is an auto-generated table.

Columns

AccountLocation is an auto-generated table.

Columns

AccountSummaryInquiry is an auto-generated table.

Columns

Adjustment is an auto-generated table.

Columns

AttributeDefinition is an auto-generated table.

Columns

Bill is an auto-generated table.

Columns

BusinessAccount is an auto-generated table.

Columns

Carrier is an auto-generated table.

Columns

Case is an auto-generated table.

Columns

CashSale is an auto-generated table.

Columns

Check is an auto-generated table.

Columns

Contact is an auto-generated table.

Columns

Currency is an auto-generated table.

Columns

Customer is an auto-generated table.

Columns

CustomerClass is an auto-generated table.

Columns

CustomerLocation is an auto-generated table.

Columns

CustomerPaymentMethod is an auto-generated table.

Columns

CustomerPriceClass is an auto-generated table.

Columns

Discount is an auto-generated table.

Columns

DiscountCode is an auto-generated table.

Columns

Email is an auto-generated table.

Columns

Employee is an auto-generated table.

Columns

Event is an auto-generated table.

Columns

FinancialPeriod is an auto-generated table.

Columns

FOBPoint is an auto-generated table.

Columns

InterBranchAccountMapping is an auto-generated table.

Columns

InventoryAllocationInquiry is an auto-generated table.

Columns