Search Results delete_codes_cascademn

Overview

BSC_DIMENSION_EDIT is a PL/SQL package owned by the APPS schema within Oracle E-Business Suite, and is part of the Oracle Balanced Scorecard (BSC) subsystem. Its principal business function is to provide the server-side editing, validation, and maintenance logic for dimension rows used by Balanced Scorecard KPIs, dimension levels, and their associated hierarchies. In practice, the package acts as a low-level utility layer behind the BSC administrative forms that allow users to create, update, and remove dimension codes and dimension members.

The package is classified as OTHER in Oracle's API taxonomy. That means it is not a formally published, supported public API; it is an internal package that the application calls from its own forms, concurrent logic, and supporting code. The header identifier (BSCEDITS.pls) and its revision level suggest it is an older, stable component carried forward through 12.1.1 and 12.2.2 without significant redesign. Callers should treat the package as private and expect its behavior to be driven by the calling form's context rather than by an externally stable contract.

Beyond simple row maintenance, the package also provides security synchronization, KPI existence marking, and validation routines. These capabilities allow the Balanced Scorecard application to keep its dimension definitions, dimension-level relationships, and security assignments consistent as administrators change the scorecard model.

Key Procedures and Functions

The documented interface exposes twenty-six routines. They can be grouped functionally:

• Security and KPI maintenance: SECURITY_SYNC synchronizes dimension and scorecard security assignments; MARKKPI marks KPI-related dimension rows.
• Row deletion: DELETENORMALROW removes a standard dimension row identified by its deleted code; DELETEMNROW removes a row in a many-to-many dimension table using its key columns and rowid; DELETE_CODES_CASCADEMN performs a cascading deletion of codes across a many-to-many relationship, central to the cascade-delete behavior administrators invoke from the dimension maintenance screens.
• Insert and update (normal, without foreign key): INSERTNORMALROWNO, UPDATENORMALROWNO, and SAVENORMALROWNO operate on dimension tables that have no foreign key dependency, deriving the next code automatically.
• Insert and update (normal, with foreign key): INSERTNORMALROWNW, UPDATENORMALROWNW, and SAVENORMALROWNW handle dimension tables that reference a parent code through a foreign key relationship.
• Insert and update (many-to-many variants): SAVENORMALROWNWM, UPDATENORMALROWNWM, and INSERTNORMALROWNWM apply the foreign-key behavior to many-to-many dimension structures.
• Validation helpers: CHECKRECORD, CHECKMNRECORD, CHECKMVNOT, CHECKUSERCODECHANGE, and REMOVECOMMA verify record existence, many-to-many validity, code-change legality, and simple text normalization.

Most insert, update, and save routines return a status message through an output parameter so the calling form can display a meaningful diagnostic.

Tables Accessed

The package reads and writes the following tables through APPS synonyms:

• BSC_KPIS_B — the base KPI definitions that dimension entries feed into.
• BSC_KPI_ANALYSIS_MEASURES_B — analysis measures linked to KPIs, used when validating dimension usage.
• BSC_KPI_DIM_LEVELS_B — the KPI-to-dimension-level relationships.
• BSC_SYS_COM_DIM_LEVELS — common dimension-level definitions applicable across scorecards.
• BSC_SYS_DIM_LEVELS_B — base system dimension-level definitions.
• BSC_SYS_DIM_LEVEL_RELS — the parent/child relationships between dimension levels that make cascade deletion and foreign key validation meaningful.

Together these tables supply the referential constraints that the package's cascade and foreign-key routines must respect.

Usage Notes

BSC_DIMENSION_EDIT is invoked internally by the Oracle Balanced Scorecard administrative forms and by BSC concurrent or scheduled maintenance processes, not directly by end users. It is referenced by zero other PL/SQL packages, which confirms its position as a leaf-level internal utility. Custom code should avoid calling these procedures unless it is prepared to mimic the exact context in which the forms call them, especially for the cascading deletion and foreign-key variants. Because the package is not a published API, Oracle does not guarantee compatibility across patch or release levels.