4.4.3 Code Synchronization

The SYNCHRONIZE_STAGE procedure identifies code values in stage tables for which a corresponding description does not exist in key dimension or CD/MLS tables 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. This procedure displays a warning message for any unidentified values in the CODE columns where OFSAA reserves the entire range.

If the resulting objects 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 Stage table in the message log file FSI_MESSAGE_LOG.

For example, if you are synchronizing the STG_INVESTMENTS 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 gives output as an error message indicating the number of invalid values present.

OFSAA Reserved Code Description objects are identified by the following SQL statement:

SELECT distinct member_base_table_name, description_table_name FROM rev_description_tables,REV_DIMENSIONS_B WHERE rev_description_tables.member_base_table_name=REV_DIMENSIONS_B.Member_b_Table_Name and rev_description_tables.description_join_column_name=REV_DIMENSIONS_B.MEMBER_DISPLAY_CODE_COL AND stg_table_name = 'STG_INVESTMENTS' AND rev_dimensions_b.member_code_column is not null AND description_table_name IN (SELECT table_name FROM rev_table_class_assignment WHERE table_classification_cd = 197) AND STG_CD_COLUMN_NAME IN (SELECT column_name FROM user_tab_columns WHERE table_name = 'STG_INVESTMENTS');

An example of a User-Editable CODE column is BRANCH_CD. If the procedure finds any code values in BRANCH_CD in the FSI_BRANCH_CD table that do not have a description in FSI_BRANCH_MLS, it creates a default description 'No Description_<CD Column Value>' for each value. It is now, up to the users to update these descriptions as appropriate.

User-Editable Code Description objects are identified by the following SQL statement:

SELECT distinct member_base_table_name, description_table_name FROM rev_description_tables,REV_DIMENSIONS_B,user_tables WHERE rev_description_tables.member_base_table_name=REV_DIMENSIONS_B.Member_b_Table_Name and rev_description_tables.description_join_column_name=REV_DIMENSIONS_B.MEMBER_DISPLAY_CODE_COL and rev_description_tables.member_base_table_name=user_tables.Table_Name AND stg_table_name = 'STG_INVESTMENTS' AND rev_dimensions_b.member_code_column is not null AND description_table_name NOT IN (SELECT table_name FROM rev_table_class_assignment WHERE table_classification_cd = 197);