Search Results get_apps_schema

Overview

BSC_DBGEN_UTILS is a utility package owned by the APPS schema in Oracle E-Business Suite, classified under the ETRM taxonomy as an "OTHER" API. The prefix "BSC" associates it with the Balanced Scorecard product family, while "DBGEN" indicates its role in database generation and metadata resolution for that module. The package does not expose a business-facing API in the manner of an entity-handler or a public interface; rather, it provides a library of helper routines that other Balanced Scorecard packages call to resolve schema names, inspect database objects, build dynamic SQL predicates, manipulate property collections, and evaluate table and objective metadata.

The header comment ($Header: BSCDBUTS.pls 120.6 2006/01/11) confirms the file is designated "noship," meaning it is delivered as part of the product installation rather than as a patchable standalone script. The package is referenced by eleven other packages, which is consistent with its role as a low-level, broadly consumed utility layer. The user search term "execute_immediate" aligns with this package's dynamic SQL orientation: several routines generate and evaluate SQL fragments that higher-level Balanced Scorecard code later executes via EXECUTE IMMEDIATE.

Key Procedures and Functions

The package exposes 27 documented procedures and functions. They can be grouped by purpose:

• Schema resolution: GET_BSC_SCHEMA, GET_APPS_SCHEMA, and the package-level variables g_apps_schema, g_bsc_schema, and g_ak_schema allow callers to determine the correct owning schema for Balanced Scorecard and related objects at runtime, supporting installations where product schemas differ.
• KPI and property access: GET_KPI_PROPERTY_VALUE, ADD_PROPERTY (overloaded for varchar2 and number values), and GET_PROPERTY_VALUE operate on BSC_DBGEN_STD_METADATA.tab_ClsProperties, a nested-table type used to pass property collections in and out of metadata routines.
• Dynamic predicate construction: GET_NEW_BIG_IN_COND_NUMBER, ADD_VALUE_BIG_IN_COND_NUMBER, GET_NEW_BIG_IN_COND_VARCHAR2, and ADD_VALUE_BIG_IN_COND_VARCHAR2 build and accumulate large IN-list predicates, which are stored transiently and later embedded in dynamically constructed SQL.
• Metadata introspection: GET_DATATYPE returns the data type of a given table column; GET_SOURCE_TABLE_NAMES returns the set of source tables underlying a view or table; GET_TABLE_TYPE classifies an object as I, B, S, T, D, or DI; and GET_MVLOG_FOR_TABLE identifies the materialized view log associated with a table.
• Parsing and formatting: PARSE_VALUE extracts a named value from a delimited string, and GET_CHAR_CHUNKS splits a message into character-sized segments suitable for output or logging buffers.
• Objective handling and initialization: GET_OBJECTIVE_TYPE, GET_OBJECTIVE_TYPE_FOR_B_TABLE, INIT, GET_MEASURE_LIST, and IS_TMP_TABLE_EXISTED support Balanced Scorecard objective resolution and temporary-table lifecycle checks.

Tables Accessed

Table references are made through APPS synonyms. Metadata tables BSC_DB_TABLES, BSC_DB_TABLES_RELS, BSC_KPIS_B, BSC_KPI_DATA_TABLES, and BSC_KPI_PROPERTIES supply the definitions the metadata and objective routines read. BSC_SYS_DIM_LEVELS_B supports dimension-level resolution, while BSC_TMP_BIG_IN_COND holds the transient values accumulated by the big-IN-condition routines. Data dictionary views ALL_TABLES, ALL_TAB_COLUMNS, ALL_SNAPSHOT_LOGS, and USER_SYNONYMS provide object existence, column type, materialized view log, and synonym information. FND_ORACLE_USERID resolves Oracle schema identifiers; AD_DDL tracks applied DDL; PLITBLM supports DBMS_SQL operations; and DBMS_SQL itself is used for dynamic SQL parsing and execution.

Usage Notes

BSC_DBGEN_UTILS is an internal utility package and is not intended to be called from Oracle Forms or from customer-written code directly. It is invoked by other Balanced Scorecard packages during metadata setup, KPI definition processing, and dynamic query generation. Because several routines produce SQL fragments intended for EXECUTE IMMEDIATE, callers must treat returned strings as dynamic SQL and be aware of the associated parse and injection considerations. The transient big-IN-condition storage in BSC_TMP_BIG_IN_COND implies that routines must be called in sequence, with cleanup expected by the consuming program. Customizations that reference this package risk breakage on upgrade, since it is delivered as a noship internal component.