Search Results lock_object

Overview

BSC_LOCKS_PVT is a private PL/SQL package in the APPS schema that implements the object-locking infrastructure for Oracle EBS Balanced Scorecard (BSC), part of the Enterprise Performance Foundation / ETRM product family. In a multi-user scorecard environment, multiple analysts and administrators may open the same design artifact — an overview page, scorecard, custom view, launchpad, objective, measure, data column, dimension, report, calendar, periodicity, or underlying table — at the same time. Without coordination, concurrent edits would silently overwrite each other. BSC_LOCKS_PVT provides the pessimistic locking layer that prevents this: it validates that a requested object exists, acquires a logical lock row in the lock table, tracks the user holding the lock, and raises an exception when another session already owns it. The package is classified as PVT, meaning it is an internal implementation package not intended as a public API; it is invoked by the higher-level BSC public packages and the OAF-based Scorecard administration UI rather than directly by customer code. Its header dates to 2005 (BSCVLOKS.pls, version 120.3), and the design has remained stable across the 12.1.1 and 12.2.2 releases, so behavior is essentially identical on both versions.

Key Procedures and Functions

The package exposes 36 documented procedures and functions. The central validation routine, VALIDATE_OBJECT, inspects the supplied object key and object type and raises an exception if the object does not exist in the database. The object key is normally the TO_CHAR form of the object ID; for objects with composite keys, callers pass a comma-separated concatenation of all key values. Calling code receives results through standard x_return_status, x_msg_count, and x_msg_data out parameters.

• LOCK_OBJECT — places a database lock on the corresponding row in the lock table and returns the last_save_time value; inserts a new lock row if none exists and updates the user table. Raises an exception if another user already holds the lock.
• LOCK_OBJECT_READ / LOCK_OBJECT_WRITE / LOCK_OBJECT_ALL — variants that acquire locks at different scopes or access modes (read versus write intent, and all-object locking).
• LOCK_USER — records or associates the current user with an object lock in BSC_OBJECT_LOCK_USERS.
• GET_CHILD_OBJECTS — retrieves the child objects belonging to a parent object, used to propagate locks down a hierarchy.
• INSERT_LOCK_ALL / INSERT_LOCK_ALL_AUTONOMOUS — insert lock rows for the full object set; the AUTONOMOUS variant runs in an autonomous transaction so the lock persists independently of the caller's transaction outcome.
• INSERT_LOCK_SCORECARD, INSERT_LOCK_OBJECTIVE, INSERT_LOCK_DIMENSION, INSERT_LOCK_DIMENSION_OBJECT, INSERT_LOCK_MEASURE, INSERT_LOCK_DATA_COLUMN, INSERT_LOCK_CUSTOM_VIEW, INSERT_LOCK_LAUNCHPAD, INSERT_LOCK_PERIODICITY, INSERT_LOCK_CALENDAR, INSERT_LOCK_TABLE — type-specific insert routines that create lock rows for each supported BSC object category, matching the object types enumerated by VALIDATE_OBJECT.

Tables Accessed

BSC_OBJECT_LOCKS is the core lock registry, holding the object key, object type, and last_save_time for each locked artifact. BSC_OBJECT_LOCK_USERS records which users currently hold locks, supporting the "locked by" indication in the UI. BSC_DB_TABLES participates in locking for table-type objects. The remaining tables supply object definitions and metadata used during validation and lock insertion: BSC_KPIS_B (scorecards and measures), BSC_KPI_DIM_GROUPS, BSC_SYS_DIM_GROUPS_TL, BSC_SYS_DIM_LEVELS_B, BSC_SYS_DIM_LEVELS_BY_GROUP (dimension hierarchy), BSC_SYS_MEASURES, BSC_SYS_PERIODICITIES, BSC_SYS_CALENDARS_B, BSC_SYS_DATASETS_B, BSC_TABS_B, BSC_TAB_VIEWS_B, and BSC_TAB_INDICATORS, which back overview pages, launchpads, and custom views. All are accessed through APPS synonyms.

Usage Notes

BSC_LOCKS_PVT is invoked indirectly whenever a user opens a BSC design object in edit mode. The Scorecard administration pages (OAF) call the public BSC packages, which in turn call LOCK_OBJECT or the type-specific insert routines; the returned last_save_time is used for optimistic concurrency checks at save time, detecting whether the record changed since it was locked. When a user closes or saves an object, the corresponding lock row and user lock association are released. The package is also referenced by one other BSC package. Because it is a PVT package, it must not be called directly from customizations; doing so bypasses the calling API's error handling and transaction management. Custom integrations requiring lock semantics should use the documented public BSC APIs. Multi-user contention exceptions surface to the end user as an "object is locked by another user" message, and the same design and behavior apply to both EBS 12.1.1 and 12.2.2.