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