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)