Account Reconciliation Sync Now Supports Custom SuiteQL Queries with Parameterized Filters

What changed

The Account Reconciliation Sync SuiteApp now includes a dedicated UI at NetSuite EPM > Account Reconciliation > Manage SuiteQL Queries for authoring and managing custom SuiteQL queries. These queries load transaction data and metadata from NetSuite into Oracle Account Reconciliation.

Key technical details

• Query ID prefix requirement: All custom query IDs must begin with custsql_. If you omit the prefix, it is auto-prepended. The integration will not recognize queries without this prefix.
• Allowlist: The custsql_ prefix must also be added to the Account Reconciliation data source allowlist, or the integration will ignore your queries entirely.
• Parameterized filters: Use @paramname@ syntax to define placeholder parameters inside queries. These placeholders surface as configurable filters in Data Exchange and their values are supplied at Data Integration execution time. No default values are set—callers must provide them.
• Limitation – IN clauses: Using @ placeholders to separate values in IN clauses is not supported. You cannot do WHERE id IN (@ids@) with a comma-delimited string.
• Metadata hierarchy: SuiteQL query results do not include metadata hierarchy. If your reconciliation workflow depends on hierarchical metadata, you will need to handle that outside of these custom queries.
• Result columns: Column labels for the query result set are defined manually in a comma-separated Result Column Labels field—they are not inferred from the query itself.

Example: date filter with to_date

A query using a date placeholder:

SELECT TOP 10 memo FROM transaction WHERE createddate > to_date(@datecreatedfilter@, 'DD-MM-YYYY')

The value supplied for datecreatedfilter at runtime must match the format string passed to to_date. Mismatched formats will cause silent filtering errors or runtime failures.

What to do

1. Navigate to NetSuite EPM > Account Reconciliation > Manage SuiteQL Queries to create or edit queries.
2. Ensure every query ID starts with custsql_. If you have existing integrations referencing query IDs without this prefix, verify the auto-prepend behavior hasn't broken your references.
3. Add custsql_ to the Account Reconciliation data source allowlist. Without this step, the integration will not pick up your queries. See Oracle's Data Source Allowlist documentation for instructions.
4. If you use parameterized filters (@param@), confirm that the Data Integration job supplies values for every placeholder at execution time. There are no defaults—missing values will cause query failures.
5. Do not attempt to use @ placeholders inside IN clauses. If you need dynamic multi-value filtering, build the query with explicit values or explore alternative approaches.
6. If your reconciliation requires metadata hierarchy, plan a separate data pipeline for that data—these SuiteQL results will not include it.

Caveats

Oracle's documentation does not specify which NetSuite release introduced this feature, nor does it clarify governance impact for these SuiteQL queries when executed via the Account Reconciliation Sync scheduled scripts. If you are running high-frequency reconciliation syncs, monitor governance consumption on the associated script deployments. The source also does not document any SuiteScript API hooks for programmatically managing these queries—the workflow appears to be UI-driven only.