Search Results load_dbi_greg_cal
Overview
APPS.BSC_DBI_CALENDAR is a calendar-engineering PL/SQL package belonging to the Oracle Balanced Scorecard (BSC) / Daily Business Intelligence (DBI) product family. Its core business function is to build, populate, and maintain the derived calendar layers that DBI fact collection depends upon. Enterprise installations of DBI require three parallel notions of time to be reconciled: the Gregorian calendar, a retail-style 4-4-5 calendar, and the enterprise (fiscal) calendar defined by the customer in BSC_SYS_CALENDARS_B and its related periodicity and period tables. This package materializes each of those representations into the BSC_DB_CALENDAR table and maintains in-memory caches of the resulting conversion structures.
The header reveals the package's design intent. It declares a set of strongly typed, index-by-binary_integer collections — dbi_cal_record_t, dbi_ent_period_t, dbi_ent_qtr_t, and dbi_ent_year_t — that hold report dates, day/month/year labels, month IDs, enterprise period IDs, week IDs, 4-4-5 day codes, Gregorian day numbers, start/end dates, and quarterly/annual fiscal rollups. Global counters track the size of each collection, while flags such as g_db_cal_modified, g_init_mem, and g_bsc_greg_fiscal_year control whether recalculation is required. Language and logging state is carried in g_lang, g_num_lang, g_src_lang, and g_status_message.
Key Procedures and Functions
The documented API surface spans 50 procedures and functions. The most operationally significant include:
- INIT_ALL / INIT_CAL_PER_IDS — the initialization entry points. INIT_ALL primes the package's global collections and language settings; INIT_CAL_PER_IDS (the object referenced in the user's search) loads the identifier set for the enterprise calendar periodicities so downstream loaders know which period IDs to process.
- LOAD_DBI_ENT_CAL / LOAD_DBI_445_CAL / LOAD_DBI_GREG_CAL — the incremental loaders that construct the enterprise, 4-4-5, and Gregorian calendar rows respectively. Their _FULL counterparts (LOAD_DBI_ENT_CAL_FULL, LOAD_DBI_445_CAL_FULL, LOAD_DBI_GREG_CAL_FULL) perform complete rebuilds.
- LOADMEM_ENT_FULL / LOADMEM_445_FULL / LOADMEM_GREG_FULL — populate the in-memory record collections rather than persisting rows, supporting conversion lookups without repeated SQL.
- LOAD_DBI_CAL_INTO_BSC / LOAD_DBI_CAL_INTO_BSC_FULL — transfer the assembled DBI calendar data into the BSC calendar table used by the reporting layer.
- LOAD_DBI_ENT_CAL_DATA / LOAD_DBI_445_CAL_DATA — data-portion loaders that operate on already-validated calendar structures.
- LOAD_FII_TIME_DAY_FULL — builds the day-level time dimension consumed by Financial Intelligence and other DBI products.
- GET_PERIODICITY_NEXTVAL / GET_CALENDAR_NEXTVAL / GET_TIME — sequence and timestamp accessors used when inserting new periodicity, calendar, and load records.
- DELETE_DBI_CALENDARS — clears previously derived DBI calendar rows ahead of a rebuild.
- ANALYZE_TABLES — refreshes optimizer statistics on the calendar tables after bulk loads.
- WRITE_TO_LOG_FILE / WRITE_TO_LOG_FILE_N — diagnostic logging helpers honoring the package's language and debug globals.
Tables Accessed
The package touches four functional groups of tables through APPS synonyms. The BSC_SYS% group — BSC_SYS_CALENDARS_B, BSC_SYS_CALENDARS_TL, BSC_SYS_PERIODICITIES, BSC_SYS_PERIODICITIES_TL, BSC_SYS_PERIODS, BSC_SYS_PERIODS_TL, and their ID sequences BSC_SYS_CALENDAR_ID_S and BSC_SYS_PERIODICITY_ID_S — supplies the customer-defined fiscal and periodicity definitions that drive enterprise calendar generation. BSC_DB_CALENDAR is the primary target table receiving the materialized calendar rows. The BIS_LEVELS / BIS_LEVELS_TL pair provides level metadata used in DBI dimension construction. The FND group (FND_LANGUAGES, FND_LOOKUP_VALUES) supports multilingual names and lookup-driven behavior, while DUAL and PLITBLM are utility references.
Usage Notes
BSC_DBI_CALENDAR is not an end-user-facing API. It is invoked during DBI setup and refresh cycles, either from concurrent programs that rebuild calendar dimensions or from the collection programs that populate BSC fact tables. The DBI administrator is expected to run the full loaders (the _FULL variants) after defining or modifying a fiscal calendar, and incremental loaders thereafter. INIT_CAL_PER_IDS is called first within such runs to seed period-identifier state. Because the package holds significant state in package globals and PL/SQL collections, operations should not be interleaved between sessions, and its exception g_exception should be handled by each caller. The package is referenced by three other packages, confirming its role as a shared calendar service for the DBI module.