1. NetSuite Enterprise Performance Management
  2. NetSuite Account Reconciliation
  3. Account Reconciliation Sync SuiteApp
  4. Account Reconciliation Sync SuiteApp Installation and Setup
  5. Manage SuiteQL Queries in Account Reconciliation Sync

Manage SuiteQL Queries in Account Reconciliation Sync

You can write SuiteQL queries to load transaction data and metadata from NetSuite into your Account Reconciliation application.

Note:

The SuiteQL query results don't include metadata hierarchy.

To define an SQL query:

  1. In NetSuite, go to NetSuite EPM > Account Reconciliation > Manage SuiteQL Queries.

  2. On the Manage SuiteQL Queries page, do one of the following:

    • To create a new SQL query, click Add SuiteQL Query.

    • To update an existing SQL query, click Edit next to the query.

  3. In the ID field, enter a unique ID for the SQL query you are creating.

    Important:

    The integration recognizes IDs the prefixes of which begin with custsql_. If you omit this prefix, custsql_ is automatically prepended.

  4. In the Name field, enter a unique name for the SQL query you are creating.

  5. In the Result Column Labels field, enter the names of your SuiteQL query result columns, separated by commas.

  6. In the Query field, provide the query details.

    Read the following guidelines on how to add filters in SuiteQL queries:

    • Add only the filters you want to make available in Data Exchange.

    • To filter values using the '@' sign, keep the following in mind:

      • You can use the '@' sign to specify parameter placeholders for filters. For example, see the following query where a date is used as a filter:

        select top 10 memo from transaction where createddate > to_date(@datecreatedfilter@, 'DD-MM-YYYY');

        When using this query, ensure that the value you provide for datecreatedfilter matches the date format specified in the to_date function. Here are a couple of examples:

        • If your date is '31-07-2020', the query should be:

          select top 10 memo from transaction where createddate > to_date('31-07-2020', 'DD-MM-YYYY');

        • If your date and time is '2015/05/15 8:30:25', the query should be:

          select top 10 memo from transaction where createddate > to_date('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS');

      • Using the '@' sign to separate values in IN clauses is not supported.

      Note:

      The filter placeholders will become available in Data Exchange, and their values should be set during Data Integration execution. There are no default values set for these placeholders.

  7. Click Save.

  8. To ensure the integration recognizes the created SQL query, place the custsql_ prefix on the Account Reconciliation allowlist. For instructions, see Data Source Allowlist.

For general information about SuiteQL, see SuiteQL.

Related Topics

General Notices