Search Results get_schema_name

Overview

APPS.BIL_BI_UTIL_COLLECTION_PKG is a utility PL/SQL package body in Oracle EBS that supports the Bilateral (BIL) Business Intelligence collection layer. Its primary business function is to provide centralized, reusable helper routines that BI collection programs invoke before executing their main extraction logic. These helpers abstract routine infrastructure tasks — resolving schema names for a given application, retrieving currency conversion rates, reading profile option values, and performing DDL/DML operations such as analyzing, truncating, and dropping staging tables, plus writing diagnostic log messages to a log table. By centralizing these operations, the collection layer avoids duplicating boilerplate code across the various BIL BI collection packages.

The package is an OTHER-classified API rather than a public business API; it is internal plumbing. The source header (120.1, dated 2006/03/27) shows the package is stable and largely unchanged across the 12.1.1 and 12.2.2 releases.

Key Procedures and Functions

• GET_SCHEMA_NAME — Retrieves the database schema (Oracle username) corresponding to a given application short name. It first calls FND_INSTALLATION.GET_APP_INFO and, if that raises an exception, falls back to a direct query joining FND_APPLICATION, FND_PRODUCT_INSTALLATIONS, and FND_ORACLE_USERID, returning NULL if the fallback also fails.
• GET_SCHEMA_NAME / GET_APPS_SCHEMA_NAME — The companion GET_APPS_SCHEMA_NAME returns the APPS schema name, providing a convenient shortcut where callers always need the APPS owner.
• GET_GLOBAL_RATE_P and GET_GLOBAL_RATE_S — Return the primary and secondary global currency conversion rates for a currency code and effective date, delegating to FII_CURRENCY. Both are declared PARALLEL_ENABLE, allowing them to execute within parallel DML/hash operations.
• ANALYZE_TABLE, TRUNCATE_TABLE, DROP_TABLE — DDL utilities used to manage BI staging tables: gathering optimizer statistics, clearing rows prior to a load, and removing temporary collection tables after use.
• GET_PROFILE_VALUE, CHKLOGLEVEL, WRITELOG, GET_USER_PROFILE_NAME — Profile and logging helpers. GET_PROFILE_VALUE and GET_USER_PROFILE_NAME resolve profile option settings, while CHKLOGLEVEL and WRITELOG implement the package's diagnostic logging, gated by a profile-controlled log level and the global g_debug flag.

Tables Accessed

• FND_APPLICATION — Maps an application short name to its application_id.
• FND_PRODUCT_INSTALLATIONS — Links the application to its installed Oracle user identifier (oracle_id).
• FND_ORACLE_USERID — Supplies the actual schema/oracle_username from the oracle_id. These three tables form the fallback lookup in GET_SCHEMA_NAME.
• FND_PROFILE_OPTIONS_TL — Provides translated profile option names, supporting GET_PROFILE_VALUE and GET_USER_PROFILE_NAME.

All tables are referenced through APPS synonyms, consistent with the package's APPS ownership.

Usage Notes

This package is not exposed through a standard Oracle Forms or a standalone concurrent program; instead it is invoked by custom and Oracle-delivered BI collection code that requires its utility services. ETRM records indicate it is referenced by five other packages, confirming its role as a shared dependency in the BIL BI collection framework. Typical invocation patterns include collecting code calling GET_SCHEMA_NAME to build schema-qualified dynamic SQL, calling GET_GLOBAL_RATE_P or GET_GLOBAL_RATE_S to convert amounts during data collection, and calling WRITELOG / CHKLOGLEVEL to emit trace information controlled through a profile option. Because GET_SCHEMA_NAME and its variants are the most frequently searched entry points, they represent the package's most commonly reused function. The PARALLEL_ENABLE designation on the currency functions means callers should avoid side effects within the same SQL statement. As an internal utility (OTHER classification), direct customer customization is uncommon; the package is expected to remain backward compatible, and its behavior is identical under 12.1.1 and 12.2.2.