Search Results g_table_not_exist

Overview

ASO_BI_UTIL_PVT is a private utility package in the Oracle E-Business Suite Order Management (ASO) module, belonging to the Business Intelligence (BI) collection layer. It is classified as a PVT (private) API, meaning it is intended for internal use by other ASO packages rather than direct invocation by external or customer code. Its primary responsibility is to support the extraction and staging of Quote data for the ASO Business Intelligence collection process, providing reusable housekeeping routines for schema resolution, table truncation, table statistics gathering, and currency rate lookups. In the context of Oracle EBS 12.1.1 and 12.2.2, the package forms part of the infrastructure that materializes denormalized quote data into BI staging tables consumed by Oracle Business Intelligence and related reporting tools.

Key Procedures and Functions

• INIT — Initializes the package-level variable g_aso_schema, which holds the database schema owner for ASO objects. It resolves this schema by calling FND_INSTALLATION.GET_APP_INFO for the ASO application. The procedure uses the ASO_BI_QUOTE_HDRS_ALL table both as the reference object whose owner is determined and as the basis for subsequent operations. Debug messages are emitted through BIS_COLLECTION_UTILITIES.DEBUG when debug mode is enabled. This procedure must be invoked before other routines that depend on the resolved schema.
• TRUNCATE_TABLE — Accepts a table name and issues a dynamic TRUNCATE TABLE statement against g_aso_schema concatenated with the supplied name. Its notable behavior is graceful degradation: the package declares the exception G_TABLE_NOT_EXIST and binds it via PRAGMA EXCEPTION_INIT to Oracle error -942 (table or view does not exist). When the specified table does not exist, the procedure silently takes no action rather than failing. Any other exception is re-raised. This makes the routine safe to call unconditionally during collection setup.
• ANALYZE_TABLE — Gathers optimizer statistics on the specified table. It lazily resolves g_aso_schema if the variable is still NULL, repeating the same FND_INSTALLATION.GET_APP_INFO lookup performed by INIT. This ensures the procedure is self-sufficient and does not strictly require a prior INIT call.
• GET_CLOSEST_RATE_SQL — Returns SQL text used to determine the closest applicable currency conversion rate for a given date context. It is used by the BI extraction logic when converting quote amounts into the reporting currency.

Tables Accessed

The package documentation references ASO_BI_QUOTE_HDRS_ALL as the principal object whose owning schema is resolved at runtime via FND_INSTALLATION.GET_APP_INFO. Because the utility operates generically, TRUNCATE_TABLE and ANALYZE_TABLE may be applied to any ASO BI staging table passed by the caller. Currency conversion logic in GET_CLOSEST_RATE_SQL relies on the standard EBS currency rate tables (such as GL_DAILY_CONVERSION_TYPES and GL_DAILY_RATES) accessed through APPS synonyms.

Usage Notes

ASO_BI_UTIL_PVT is referenced by four other packages within the ASO BI collection suite, typically the controllers that manage the full quote-extraction cycle. The normal invocation sequence is INIT, followed by TRUNCATE_TABLE calls to clear staging objects, extraction into those tables, ANALYZE_TABLE to refresh statistics, and rate lookups via GET_CLOSEST_RATE_SQL during data transformation. Because it is a PVT package, it should not be called directly from forms or concurrent program definitions; instead it is invoked from the parent BI collection packages, which are themselves scheduled in the Oracle Business Intelligence collection concurrent program flow. Custom code should avoid direct dependencies on this package, as its interface is not guaranteed across patch levels.