Search Results gather_table_stats

Overview

FII_NON_DBI_TIME_C is an Oracle EBS Applications (APPS) package body classified as a non-DBI utility within the FII (Financial Intelligence/Time Intelligence) product family. Its primary responsibility is the population and maintenance of the FII Time Calendar Name layer, which provides the foundational calendar definitions used downstream by Oracle's Daily Business Intelligence (DBI) and Time Intelligence reporting structures. The package shields the calendar-naming foundation from direct dependency on DBI schema objects, hence its "NON_DBI" designation. It operates within the APPS schema in both Oracle EBS 12.1.1 and 12.2.2 environments.

The package body includes a private helper procedure, GATHER_TABLE_STATS, which wraps the seeded FND_STATS.gather_table_stats API. This is a significant detail, particularly relevant to searches for "gather_table_stats," since it demonstrates that this package participates in optimizer statistics collection for the tables it manages.

Key Procedures and Functions

The documented public routine exposed by this package body is LOAD_CAL_NAME. This procedure synchronizes the FII_TIME_CAL_NAME table with the calendar definitions found in Oracle General Ledger. It determines the maximum existing calendar_id, resolves the FII schema name through FND_INSTALLATION.GET_APP_INFO, and iterates over a cursor that identifies period set and period type combinations present in GL_PERIODS but absent from FII_TIME_CAL_NAME. For each uncovered combination, it inserts a new row into the calendar name table with appropriate audit columns (creation_date, created_by, last_update_date, last_updated_by, last_update_login).

The private helper GATHER_TABLE_STATS accepts a table name and an optional schema name (defaulting to the resolved FII schema) and invokes FND_STATS.gather_table_stats against that object. This supports post-load statistics gathering for performance stability of the calendar tables.

Tables Accessed

• GL_PERIODS — Read via APPS synonym. Used as the authoritative source of defined period sets and period types; drives the MINUS query that identifies missing calendar definitions.
• FII_TIME_CAL_NAME — Both read and written. Read to compute the current maximum calendar_id and to establish the set of already-known period set/type combinations; written to insert newly discovered calendar name rows with audit columns.

These two tables constitute the complete data surface of the documented functionality, and statistics gathered through the helper procedure target such FII calendar objects.

Usage Notes

This package is referenced by one other package within the FII/DBI layer, indicating it is typically called programmatically rather than directly by end users. In practice, LOAD_CAL_NAME is invoked during Time Intelligence calendar setup or maintenance, most often as a dependency of a broader calendar-generation process or a concurrent program responsible for building the FII time dimension. Administrators running DBI or Time Intelligence implementation steps may trigger it indirectly through those higher-level programs.

Because the helper procedure gathers table statistics, DBAs troubleshooting stale optimizer statistics on FII calendar tables should be aware that this package can contribute to statistics collection. Any custom code extending the calendar layer should call LOAD_CAL_NAME before dependent dimension or hierarchy builds to ensure all GL period set/type combinations are represented.