4.3.3 Codes Synchronization

The SYNCHRONIZE_INSTRUMENT procedure identifies code values in Instrument and LEDGER_STAT tables for which a corresponding description does not exist and inserts a default description into the appropriate Code Description object. This applies only to CODE columns categorized as User-Editable or User-Defined (see the table classification). CODE columns for which OFSAA reserves all of the values are not updated by this procedure. The procedure displays a warning message for any unidentified values in CODE columns where OFSAA reserves the entire range.

For each CODE column (REV_DATA_TYPE_CD equals 3) on the specified object, the SYNCHRONIZE_INSTRUMENT procedure queries from REV_DESCRIPTION_TABLES to identify the object storing the corresponding descriptions. If the resulting object is a User-Editable or User-Defined Code Description object (checks from REV_TABLE_CLASS_ASSIGNMENT table), then the procedure inserts a default description for any code values for which a description record does not already exist. If the resulting object is an OFSAA Reserved Code Description object, then the procedure outputs a warning message indicating how many invalid code values exist in the specified Instrument or LEDGER_STAT table in the message log (FSI_MESSAGE_LOG).

For example, if you are synchronizing the FSI_D_TERM_DEPOSITS table, the procedure queries all of the CODE columns on this table. An example of a Reserved CODE column is ACCRUAL_BASIS_CD. If the procedure finds any code values in this column that are not present in the corresponding Code Description object (FSI_ACCRUAL_BASIS_CD), it outputs an error message indicating the number of invalid values present. OFSAA Reserved Code Description objects are identified by the following SQL statement:

select table_name from rev_table_class_assignment

where table_classification_cd = 197;

An example of a User-Editable CODE column is SIC_CD. If the procedure finds any code values in SIC_CD in the FSI_D_TERM_DEPOSITS table that do not have a description in FSI_SIC_MLS, it creates a default description 'No Description' for each value. It is then up to the users to update these descriptions as appropriate. User-Editable Code Description objects are identified by the following SQL statement:

select * from rev_description_tables

where table_name = 'FSI_D_TERM_DEPOSITS'

and description_table_name not in

(select table_name from rev_table_class_assignment

where table_classification_cd = 197)