Database Tables

The samples shipped with Business Rules include the database tables that are used to populate the tables required by the collector validation node. Table 1 describes the sample tables that contain data for content validation.
Table 1. Sample database tables for collector validation
Database Table Name Description
ERROR_CATEGORIES List of error categories. Table 1 contains the definition for this table.
REJECT_LEVELS List of validation rejection levels. Table 2 contains the definition for this table.
ERROR_CODES List of valid error codes with their associated rejection level, override flag, notification flag, and description. Table 3 contains the definition for this table.
X9_VAL_COLLECTORS List of collectors. Table 2 contains the definition for this table.
X9_VAL_COLLECTOR_COUNT List of counters. Table 3 contains the definition for this table.
X9_VAL_COLLECTOR_SUM List of accumulators. Table 4 contains the definition for this table.
X9_VAL_COLLECTOR_UNIQUE List of unique fields. Table 5 contains the definition for this table.

NACHA_VAL_COLLECTORS_GENERAL_SPEC

NACHA_VAL_COLLECTORS_SPECIFIC_SPEC

 List of collectors. Table 6 contains the definition for these tables.
NACHA_VAL_COLLECTOR_COUNT_SPEC List of counters. Table 7 contains the definition for this table.
NACHA_VAL_COLLECTOR_SUM_SPEC List of accumulators. Table 8 contains the definition for this table.
NACHA_VAL_COLLECTOR_UNIQUE_SPEC List of unique fields. Table 9 contains the definition for this table.

NACHA_VAL_FIELDS_SPEC

 List of fields for each record type. Table 5 contains the definition for this table.

The samples shipped with Business Rules include the database tables that are used to populate the tables required by the collector content validation node.

The X9 collector table provides the basic collector definitions.
Table 2. X9_VAL_COLLECTORS Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10 Unique name for the collector
DESCRIPTION varchar 64  
TYPE char 1 Options are:
  • C - Count Collector
  • S - Sum Collector
  • U - Unique Collector
SOURCE_RECORD_TYPE smallint   Record type where the collector is defined. It is one of the record types previously defined in X9_VAL_RECORDS.
TARGET_RECORD_TYPE smallint   Record type where the collector is compared. It is one of the record types previously defined in X9_VAL_RECORDS.
TARGET_FIELD_NUMBER smallint   Field number in the target record for the collector comparison. It is one of the fields in X9_VAL_FIELDS.
TARGET_VALUE bigint   Fixed value for comparison
COMPARISON char 2 Comparison type. Use the symbols for:
  • = Equal
  • != Not equal (also <>)
  • < Less than
  • <= Less than or equal
  • > Greater than
  • >= Greater than or equal

It can be blank if the type is unique or Target_Record_Type, Target_Field_Number, and Target_Value are all blank.
COMPARE_FACTOR smallint   Factor by which the collector value is multiplied before comparison. It facilitates percentage rules, such as no more than 10% of records can have a particular failure. Since each failure is counted, the count is multiplied by 10 and compared to the record count.
CONDITIONAL varchar 100 Optional conditional expression for the 'activation' of the collector. If the condition is true, the collector is 'activated', otherwise it is not. For more information, see Conditionals.
Note: This field can also be blank.
COMPARE_CONDITIONAL varchar 100 Optional conditional expression for the 'evaluation' of the collector. If the condition is true, the collector is 'evaluated', otherwise it is not. For more information, see Conditionals.
Note: This field can also be blank.
ERROR_CODE char 24 Error code when the collector comparison fails. It can be blank if Target_Record_Type, Target_Field_Number, and Target_Value are all blank. It is one of the values defined in the error codes table.
The collector definition is completed by the values in the type specific tables.
Table 3. X9_VAL_COLLECTOR_COUNT Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in X9_VAL_RECORDS.
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector increments. If it is false, the collector does not increment. For more information, see Conditionals.
Note: This field can also be blank.
Table 4. X9_VAL_COLLECTOR_SUM Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in X9_VAL_RECORDS.
FIELD_NUMBER smallint   Field in the record to be checked for uniqueness. It is one of the fields defined in X9_VAL_FIELDS.
ADDITIONAL_CONSTANT bigint   Additional value to add when this field is included in the sum
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector is accumulated. If it is false, it is not. For more information, see Conditionals.
Note: This field can also be blank.
Table 5. X9_VAL_COLLECTOR_UNIQUE Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in X9_VAL_RECORDS.
FIELD_NUMBER smallint   Field in the record to be checked for uniqueness. It is one of the fields defined in X9_VAL_FIELDS.
ORDERING varchar 12 Options are:
  • ASCENDING - Each new value must be greater than the last.
  • DESCENDING - Each new value must be less than the last.
  • INCREMENTING - Each new value must be greater by one.
  • DECREMENTING - Each new value must be less by one.
  • RETAIN_LAST - Collector holds on to last setting. Used for conditionals. For more information, see Conditionals.
Note: This field can also be blank which allows for random values.
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector is checked for uniqueness. If it is false, it is not. For more information, see Conditionals.
Note: This field can also be blank.
The NACHA collector tables provides the basic collector definitions.
Table 6. NACHA_VAL_COLLECTORS_GENERAL_SPEC and NACHA_VAL_COLLECTORS_SPECIFIC_SPEC Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10 Unique name for the collector
DESCRIPTION varchar 64  
TYPE char 1 Options are:
  • C - Count Collector
  • S - Sum Collector
  • U - Unique Collector
SOURCE_RECORD_TYPE smallint   Record type where the collector is activated.
SOURCE_SEC_TYPE char 3 SEC type where the collector is activated. (NACHA_VAL_COLLECTORS_SPECIFIC_SPEC only)
SOURCE_ADDENDA smallint   Addenda type where the collector is activated. ‘0’ for all record types except record type ‘7’. (NACHA_VAL_COLLECTORS_SPECIFIC_SPEC only)
TARGET_RECORD_TYPE smallint   Record type where the collector is evaluated. It is one of the record types previously defined in NACHA_VAL_RECORDS.
TARGET_FIELD_DESCRIPTION varchar 100 Field Description. Relates to DESCRIPTION in Validation Fields table: Table 5.
TARGET_VALUE bigint   Fixed value for comparison
COMPARISON char 2 Comparison type. Use the symbols for:
  • = Equal
  • != Not equal (also <>)
  • < Less than
  • <= Less than or equal
  • > Greater than
  • >= Greater than or equal

It can be blank if the type is unique or Target_Record_Type, Target_Field_Number, and Target_Value are all blank.
COMPARE_FACTOR smallint   Factor by which the collector value is multiplied before comparison. It facilitates percentage rules, such as no more than 10% of records can have a particular failure. Since each failure is counted, the count is multiplied by 10 and compared to the record count.
CONDITIONAL varchar 100 Optional conditional expression for the 'activation' of the collector. If the condition is true, the collector is 'activated', otherwise it is not. For more information, see Conditionals.
Note: This field can also be blank.
COMPARE_CONDITIONAL varchar 100 Optional conditional expression for the 'evaluation' of the collector. If the condition is true, the collector is 'evaluated', otherwise it is not. For more information, see Conditionals.
Note: This field can also be blank.
ERROR_CODE char 24 Error code when the collector comparison fails. It can be blank if Target_Record_Type, Target_Field_Number, and Target_Value are all blank. It is one of the values defined in the error codes table.
The collector definition is completed by the values in the type specific tables.
Table 7. NACHA_VAL_COLLECTOR_COUNT_SPEC Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in NACHA_VAL_RECORDS_SPEC.
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector increments. If it is false, the collector does not increment. For more information, see Conditionals.
Note: This field can also be blank.
Table 8. NACHA_VAL_COLLECTOR_SUM_SPEC Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in NACHA_VAL_RECORDS_SPEC.
FIELD_DESCRIPTION varchar 100 Field Description. Relates to DESCRIPTION in Validation Fields table: Table 5.
ADDITIONAL_CONSTANT bigint   Additional value to add when this field is included in the sum
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector is accumulated. If it is false, it is not. For more information, see Conditionals.
Note: This field can also be blank.
Table 9. NACHA_VAL_COLLECTOR_UNIQUE_SPEC Table Definition
Field Name Data Type Length Description
DATA_TYPE smallint   Data Type. Makes content validation specific to a transmission type. Refer to Table 1
NAME char 10  
RECORD_TYPE smallint   Record type counted by the collector. It is one of the record types defined in NACHA_VAL_RECORDS_SPEC.
FIELD_DESCRIPTION varchar 100 Field Description. Relates to DESCRIPTION in Validation Fields table: Table 5.
ORDERING varchar 12 Options are:
  • ASCENDING - Each new value must be greater than the last.
  • DESCENDING - Each new value must be less than the last.
  • INCREMENTING - Each new value must be greater by one.
  • DECREMENTING - Each new value must be less by one.
  • RETAIN_LAST - Collector holds on to last setting. Used for conditionals. For more information, see Conditionals.
Note: This field can also be blank which allows for random values.
CONDITIONAL varchar 100 Optional conditional expression for the 'execution' of the collector. If the condition is true, the collector is checked for uniqueness. If it is false, it is not. For more information, see Conditionals.
Note: This field can also be blank.