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);