Cross-Validation Rules Viewing Rule Filters and Requirements

Overview

Each Cross-Validation Rule is made up of filters (which transactions the rule applies to) and requirements (what those transactions must or must not contain). To see these for every rule in one place, for example to find out which GL accounts a rule covers, run a query in the SuiteQL Editor and export the results.

The Netgain Tools Rule Filter Search and Netgain Tools Rule Requirement Search saved searches also list this data, but they show internal IDs, such as the internal ID of a GL account. The IDs are intentional: they let you export rules from sandbox and import them to production (see Cross-Validation Rules Transfer from Sandbox to Production). The queries in this article return the same filters and requirements with names in place of IDs, so they are easier to read and share with your accounting team.

Each query returns one row per filter or requirement, with the rule it belongs to.

Prerequisites

Access to the SuiteQL Editor under the Netgain menu.

Step-by-step process

Navigate to Netgain > Development > SuiteQL Editor.
Copy one of the queries below and paste it into the editor.
Click Execute.
Review the results in the grid. To save them, click Export, or switch the view to CSV and click Copy.

Query for rule filters

Use this query to see the filters on each rule. Each row is one filter.

SELECT
    rule.id AS rule_id,
    rule.name AS rule_name,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_status) AS rule_status,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_action) AS control_type,
    filter.custrecord_nta_cvr_filter_field AS field,
    filter.custrecord_nta_cvr_filter_operator AS operator,
    NVL (LISTAGG (lookup.name, ', ') WITHIN GROUP (ORDER BY lookup.name), filter.custrecord_nta_cvr_filter_condition) AS condition
FROM
    customrecord_nta_cvr_rule_filter filter
    INNER JOIN customrecord_nta_cvr_rule rule ON rule.id = filter.custrecord_nta_cvr_filter_rule
    LEFT OUTER JOIN (
        SELECT 'account' AS type, id, accountsearchdisplayname AS name FROM account
        UNION ALL SELECT 'subsidiary', id, name FROM subsidiary
        UNION ALL SELECT 'department', id, name FROM department
        UNION ALL SELECT 'class', id, name FROM classification
        UNION ALL SELECT 'location', id, name FROM location
        UNION ALL SELECT 'entity', id, altname FROM entity
        UNION ALL SELECT 'item', id, itemid FROM item
        UNION ALL SELECT 'user', id, entityid FROM employee
        UNION ALL SELECT 'role', id, name FROM role
        UNION ALL SELECT 'accountingbook', id, name FROM accountingbook
    ) lookup ON lookup.type = LOWER (REGEXP_REPLACE (filter.custrecord_nta_cvr_filter_field, '^.*\.|(Debit|Credit)$', ''))
    AND ',' || REPLACE (filter.custrecord_nta_cvr_filter_condition, ' ', '') || ',' LIKE '%,' || lookup.id || ',%'
GROUP BY
    rule.id,
    rule.name,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_status),
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_action),
    filter.id,
    filter.custrecord_nta_cvr_filter_field,
    filter.custrecord_nta_cvr_filter_operator,
    filter.custrecord_nta_cvr_filter_condition
ORDER BY
    rule.name,
    filter.id

Query for rule requirements

Use this query to see the requirements on each rule. Each row is one requirement, and the direction column shows whether the value MUST BE or CANNOT BE met.

SELECT
    rule.id AS rule_id,
    rule.name AS rule_name,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_status) AS rule_status,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_action) AS control_type,
    requirement.custrecord_nta_cvr_requirement_field AS field,
    requirement.custrecord_nta_cvr_requirement_direction AS direction,
    requirement.custrecord_nta_cvr_requirement_operator AS operator,
    NVL (LISTAGG (lookup.name, ', ') WITHIN GROUP (ORDER BY lookup.name), requirement.custrecord_nta_cvr_requirement_condition) AS condition
FROM
    customrecord_nta_cvr_rule_requirement requirement
    INNER JOIN customrecord_nta_cvr_rule rule ON rule.id = requirement.custrecord_nta_cvr_requirement_rule
    LEFT OUTER JOIN (
        SELECT 'account' AS type, id, accountsearchdisplayname AS name FROM account
        UNION ALL SELECT 'subsidiary', id, name FROM subsidiary
        UNION ALL SELECT 'department', id, name FROM department
        UNION ALL SELECT 'class', id, name FROM classification
        UNION ALL SELECT 'location', id, name FROM location
        UNION ALL SELECT 'entity', id, altname FROM entity
        UNION ALL SELECT 'item', id, itemid FROM item
        UNION ALL SELECT 'user', id, entityid FROM employee
        UNION ALL SELECT 'role', id, name FROM role
        UNION ALL SELECT 'accountingbook', id, name FROM accountingbook
    ) lookup ON lookup.type = LOWER (REGEXP_REPLACE (requirement.custrecord_nta_cvr_requirement_field, '^.*\.|(Debit|Credit)$', ''))
    AND ',' || REPLACE (requirement.custrecord_nta_cvr_requirement_condition, ' ', '') || ',' LIKE '%,' || lookup.id || ',%'
GROUP BY
    rule.id,
    rule.name,
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_status),
    BUILTIN.DF (rule.custrecord_nta_cvr_rule_action),
    requirement.id,
    requirement.custrecord_nta_cvr_requirement_field,
    requirement.custrecord_nta_cvr_requirement_direction,
    requirement.custrecord_nta_cvr_requirement_operator,
    requirement.custrecord_nta_cvr_requirement_condition
ORDER BY
    rule.name,
    requirement.id

Reading the results

Column	Description
rule_id, rule_name	The rule the filter or requirement belongs to.
rule_status	Whether the rule is Active or Inactive.
control_type	The rule's control type, such as Prevent.
field	The transaction field the filter or requirement checks, such as `transactionLine.account`.
direction	Requirements query only. MUST BE or CANNOT BE.
operator	How the field is compared to the condition, such as `equal` or `any_of`.
condition	The value being compared. Shows names instead of IDs for the field types listed below.

To see which GL accounts a rule covers, filter the field column to the account fields, such as transactionLine.account.

Limitations / things to be aware of

Names are shown for these field types only

The queries replace IDs with names for account, subsidiary, department, class, location, entity, item, user, role, and accounting book fields. For every other field, including amounts, memos, and custom fields, the condition appears exactly as it is stored.

Custom segment values still appear as IDs

A condition on a custom segment field, such as transactionLine.cseg_ng_activity_co, shows the internal IDs of the segment values (for example 7,5,6), not their names.

The queries return every rule, active and inactive. Use the rule_status column to filter.
When a condition lists several values and only some are matched to a name, the unmatched values are left out of the result instead of shown as IDs.
Run the queries in the account you want to review. Rules in sandbox and production are separate, so results can differ.