Search Results bsc_sys_periodicity_id_s

Overview

The APPS.BSC_PERIODS_UTILITY_PKG package body is a core utility component within the Oracle EBS Balanced Scorecard (BSC) and Daily Business Intelligence (DBI) infrastructure. Its primary business function is to manage the definition, validation, and retrieval of calendar and periodicity metadata used throughout the performance management and reporting modules. The package abstracts the complexities of the underlying calendar and periodicity schema, providing a consistent API layer for other packages, concurrent programs, and forms to resolve fiscal calendars, period names, periodicities, and their associated date ranges. In Oracle EBS 12.1.1 and 12.2.2, this package is essential for ensuring that period-based aggregations, comparisons, and reporting cycles align correctly with the configured enterprise calendars.

Key Procedures and Functions

The package exposes 27 documented procedures and functions, categorized by their utility:

• Calendar and Periodicity Resolution: GET_CALENDAR_NAME, GET_CALENDAR_SHORT_NAME, GET_PERIODICITY_NAME, GET_PERIODICITY_SHORT_NAME, GET_PERIODICITY_SOURCE, and GET_UNIQUE_SHORT_NAME retrieve descriptive identifiers and source information for calendars and periodicities.
• Sequence and ID Management: GET_NEXT_PERIODICITY_ID and GET_NEXT_CALENDAR_ID obtain new primary keys from the respective sequence-backed tables, while GET_NEXT_CUST_PERIOD_DB_COLUMN determines the next custom period database column mapping.
• Validation and Uniqueness Checks: IS_PERIOD_NAME_UNIQUE, IS_PERIODICITY_IN_CALENDAR, and IS_BASE_PERIODICITY_DAILY validate period naming and periodicity associations, ensuring data integrity.
• Metadata and Counting Utilities: GET_CUST_PER_CNT_BY_CALENDAR, GET_FISCALYEAR_BY_CALENDAR, GET_PERIODS_IN_BASE_PERIOD, and PRINT_PERIOD_METADATA provide counting, fiscal year derivation, base period expansion, and diagnostic output.
• Date Range Functions: GET_START_PERIOD_DATE and GET_END_PERIOD_DATE return the boundary dates for a given period within a calendar.
• Error Handling: CHECK_ERROR_MESSAGE and ROLLBACK_API manage exception inspection and transactional rollback, adhering to the FND_API error-handling model.

Tables Accessed

The package reads and writes several core BSC tables through APPS synonyms. BSC_SYS_CALENDARS_B, BSC_SYS_CALENDARS_TL, and BSC_SYS_CALENDARS_VL store calendar definitions and translations, while BSC_SYS_CALENDAR_ID_S supplies calendar identifiers. Periodicity definitions reside in BSC_SYS_PERIODICITIES, BSC_SYS_PERIODICITIES_VL, and the sequence table BSC_SYS_PERIODICITY_ID_S — the latter being the exact object referenced by the user search term bsc_sys_periodicity_id_s. The calendar-to-level mappings are maintained in BIS_LEVELS, BIS_LEVELS_TL, and BIS_LEVELS_VL. Diagnostic and exception logging occurs in BSC_MESSAGE_LOGS, and internal calendar computations leverage BSC_DB_CALENDAR and BSC_DBI_CALENDAR. Utility calls to DUAL and DBMS_UTILITY support lightweight queries and stack diagnostics.

Usage Notes

BSC_PERIODS_UTILITY_PKG is not referenced by any database object directly, but the ETRM metadata confirms it is referenced by nine other packages, indicating its role as a foundational service layer within the BSC/DBI suite. It is typically invoked by higher-level APIs such as BSC_PERIODICITIES_PUB, and by concurrent programs that build or refresh performance management calendars. Administrators and developers call its functions when configuring new calendars or periodicities through the Balanced Scorecard setup forms, and when custom reporting code must resolve period boundaries or validate period names. Because it relies on FND_API, FND_MSG_PUB, and FND_GLOBAL, invocation should occur within an initialized EBS session context to ensure correct message stack and rollback behavior. Direct modification is not supported; the package should be treated as a read-only utility and maintained only through Oracle-supplied patches.