Sage Intacct Connection Details¶

Introduction¶

Connector Version

This documentation is based on version 23.0.8707 of the connector.

Get Started¶

Sage Intacct Version Support

The Sage Intacct connector connects with the Sage Intacct Web Services API.

Establish a Connection¶

Connect to Sage Intacct¶

To connect to Web Services, you will first need to enable the Web Services subscription. Navigate to Company > Admin Tab > Subscriptions and enable Web Services.

Intacct also recommends creating a Web Services-only user, which can be done by navigating to Company > Admin Tab and clicking on the + sign beside Web Services users.

You can establish a connection to Sage Intacct using either the embedded credentials or with your own credentials.

Authenticate to Sage Intacct¶

To authenticate, set CompanyID and set User and Password to the credentials you use to log on to Sage Intacct. In addition, you will need to either set your own SenderID and SenderPassword, or use the embedded credentials in the Sage Intacct connector.

BasicReadOnly¶

The connector has embedded Web Services credentials that are used when reading data from Intacct.

Follow these steps to authenticate:

• AuthScheme: Set this to BasicReadOnly.
• Ensure that you have not specified a SenderID or SenderPassword.
• In your Web Services dashboard, navigate to the Company > Company Info > Security tab.
• Add "" to your Web Services authorizations. This is case-sensitive. To do this, navigate to Company > Company Info (Configuration > Company in new UI) > Security > Web Services Authorizations / Edit.

Basic¶

You can use your own Web Services credentials to write data to Intacct. Set the following to authenticate:

• AuthScheme: Set this to Basic.
• SenderID: Set this to the Web Services Sender ID assigned to you by Sage Intacct.
• SenderPassword: Set this to your registered Web Services password.

Okta¶

The following connection properties are used to connect to Okta:

• AuthScheme: Set this to Okta.
• User: Set this to the Okta user.
• Password: Set this to Okta password for the user.
• SSOLoginURL: Set this to the login URL used by the SSO provider.

In addition, set the following SSOProperties:

• IntacctUserID: Set this value to the Intacct User ID that is mapped to the Okta user you set in the User connection property.
• APIToken (optional): Set this to the API Token that the customer created from the Okta organization. It should be used when authenticating a user via a trusted application or proxy that overrides Okta client request context.

The following is an example connection string:

AuthScheme=Okta; SSOLoginURL='https://example.okta.com/home/appType/0bg4ivz6cJRZgCz5d6/46'; User=oktaUserName; Password=oktaPassword; SSOProperties='IntacctUserID=intacct_user';

Fine-Tuning Data Access¶

Fine Tuning Data Access¶

You can use the following properties to gain more control over the data returned from Sage Intacct:

• LocationsID: To connect to a shared child company, specify its locations Id.
• ClientID: To connect to a distributed child company, specify its client id.

LocationsID and ClientID cannot be specified at the same time.

New Sage Intacct API¶

Support for the new Sage Intacct API has been added.

The new API adds support for server-side filtering, ORDERBY, offset and aggregate functions including count, avg, min, max and sum.

Enable this support by setting UseLegacy to False.

The new API also supports the displaying of attributes related to objects using IncludeRelatedAttributes.

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 Sage Intacct 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 Sage Intacct and then processes the rest of the query in memory (client-side).

See Query Processing for more information.

User Defined Views¶

The Sage Intacct connector 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 Customer 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.

Data Model¶

Sage Intacct connector models Sage Intacct entities in relational Tables.

Table definitions are dynamically retrieved. When you connect using the connector, any changes you make, such as adding a new table, adding new columns, or changing a column's data type, are reflected immediately.

The Tables and Views listed in the documentation are samples of what you may have access to in your Intacct account.

Sage Intacct offers support for various document tables that coincide with a specific document type. By default the document tables do not include custom fields associated with the table, but the user can access the following tables to retrieve any custom fields associated with the document type.

Document Tables

The following is a sample of the document tables and their associated document type tables.

Tables with separate required primary keys for INSERT and DELETE

The following table lists tables that have separate required primary keys for INSERT and DELETE, and their composite primary keys:

Tables¶

The connector models the data in Sage Intacct into a list of tables that can be queried using standard SQL statements.

Generally, querying Sage Intacct tables is the same as querying a table in a relational database. Sometimes there are special cases, for example, including a certain column in the WHERE clause might be required to get data for certain columns in the table. This is typically needed for situations where a separate request must be made for each row to get certain columns. These types of situations are clearly documented at the top of the table page linked below.

Sage Intacct Connector Tables¶

AcctLabelTaxGroup¶

Retrieves AcctLabelTaxGroup report data.

Columns¶

AcctRange¶

Retrieves AcctRange report data.

Columns¶

ActivityLog¶

Retrieves ActivityLog report data.

Columns¶

Aisle¶

Retrieves Aisle report data.

Columns¶

Allocation¶

Retrieves Allocation report data.

Columns¶

APAccountLabel¶

Retrieves APAccountLabel report data.

Columns¶

APAdjustment¶

Retrieves APAdjustment report data.

Columns¶

APBill¶

Retrieves APBill report data.

Columns¶

APBillBatch¶

Retrieves APBillBatch report data.

Columns¶

APPymt¶

Retrieves APPymt report data.

Columns¶

APRecurBill¶

Retrieves APRecurBill report data.

Columns¶

APTerm¶

Retrieves APTerm report data.

Columns¶

ARAccountLabel¶

Retrieves ARAccountLabel report data.

Columns¶

ARAdjustment¶

Retrieves ARAdjustment report data.

Columns¶

ARInvoice¶

Retrieves ARInvoice report data.

Columns¶

ARInvoiceBatch¶

Retrieves ARInvoiceBatch report data.

Columns¶

ARPaymentBatch¶

Retrieves ARPaymentBatch report data.

Columns¶

ARTerm¶

Retrieves ARTerm report data.

Columns¶

Attendee¶

Retrieves Attendee report data.

Columns¶

Billing_Contact¶

Retrieves Billing_Contact report data.

Columns¶

Bin¶

Retrieves Bin report data.

Columns¶

CheckingAccount¶

Retrieves CheckingAccount report data.

Columns¶

Class¶

Retrieves Class report data.

Columns¶

Comments¶

Retrieves Comments report data.

Columns¶

Contact¶

Retrieves Contact report data.

Columns¶

Contract¶

A collection of Contract items.

Columns¶

CreditCard¶

Retrieves CreditCard report data.

Columns¶

Customer¶

Retrieves Customer report data.

Columns¶