Archival of Expired Child Records from ER

This feature enables the automatic archiving of expired child records that previously remained in the H$ tables. A new step has been introduced in ER Job 1 to manage this process. This feature is controlled by the global parameter F_ARCHIVE_EXPIRED_RECORDS in FCC_ER_CONFIG. By default, this parameter is set to N. To enable archiving, update F_ARCHIVE_EXPIRED_RECORDS to Y.

The F_ARCHIVE_EXPIRED_RECORDS flag acts as a global control for all eligible tables. In addition, there is a table-level flag called F_ENABLED in the FCC_ER_QUERY_DEFINITION table. Archival will occur for a specific child table only if both the global flag and the respective table’s F_ENABLED flag are set to Y. Even if the global flag is enabled, the table-level flag must also be enabled for archiving to occur for that table.

When this feature is enabled, expired records are archived from the latest partition of the H$ table to the corresponding deleted H$ table. For example, expired address mappings from H$STG_PARTY_ADDRESS_MAP_PRE are archived to H$STG_PARTY_ADDRESS_MAP_PRE_DELETED.

The archival operation is metadata-driven. Out-of-the-box metadata for address mapping archival is pre-seeded in the FCC_ER_QUERY_DEFINITION table in the ER schema. Archival for additional or custom child records can be implemented by adding similar metadata and ensuring the F_ENABLED flag is set to Y for those records in the table.

Archival of expired records helps maintain historical data more efficiently and reduces clutter in the main H$ tables.