Search Results bsc_sys_formats_pk

Overview

BSC_SYS_FORMATS is a configuration table within the Oracle E-Business Suite Balanced Scorecard (BSC) module, whose description in the ETRM metadata is simply "Number format." The table stores the named numeric display formats that govern how measures, key performance indicators, and dataset values are rendered throughout the Balanced Scorecard application and its downstream presentation layers. Each row defines a formatting rule — such as decimal precision, thousands separators, or scaling — that other BSC and non-BSC components reference by a numeric identifier.

The metadata explicitly flags this product as Obsolete and records the implementation status as "Not implemented in this database." This indicates that although the table definition, primary key, and constraints may still be present in a 12.1.1 or 12.2.2 installation, no active data is expected. The heuristic Data Vault classification is standalone, suggesting the table be modelled as an independent reference or lookup entity rather than as a hub, link, or satellite. It carries no foreign keys of its own, and its role in the model is that of a small, self-contained code table consumed by multiple parent tables.

Key Information Stored

The documented physical schema contains only three columns, owned by the BSC schema:

• FORMAT_ID — the surrogate primary key, enforced by constraint BSC_SYS_FORMATS_PK. This value is propagated as a foreign key into dependent tables, so it is the true integration point of the table.
• NAME — the user-facing or descriptive label for the format, referenced in setup screens and list-of-values prompts.
• FORMAT — the format expression or pattern string itself, defining how a numeric value is displayed.

The unique index BSC_SYS_FORMATS_U1 covers FORMAT_ID, confirming it as the business-key candidate and reinforcing that the NAME and FORMAT columns are descriptive attributes rather than alternate keys. Because only three columns exist, there is no audit or date-tracking information, which is consistent with a static, low-volume reference table.

Common Use Cases and Queries

Typical use cases centre on resolving format definitions during reporting, KPI rendering, and label-set generation. A standard lookup joins the child foreign key to the primary key:

• Resolving the display format for a KPI: SELECT f.name, f.format FROM bsc_sys_formats f, bsc_kpi_tree_nodes_b k WHERE f.format_id = k.format_id;
• Listing all configured formats for a setup or administration report: SELECT format_id, name, format FROM bsc_sys_formats ORDER BY name;
• Identifying which datasets or feed fields use a given format: join BSC_SYS_DATASETS_B or POR_FEED_FIELD_FORMATS on FORMAT_ID.
• Detecting orphaned format references when data has been partially migrated, using an outer join from the child tables back to BSC_SYS_FORMATS.

Because the module is obsolete, queries are most often diagnostic — confirming whether the table is empty, verifying referential integrity, or auditing legacy configuration before decommissioning.

Related Objects

Four documented foreign keys point to this table, each joining on FORMAT_ID:

• BSC_SYS_DATASETS_B — BSC dataset definitions that inherit a numeric format.
• BSC_KPI_TREE_NODES_B — KPI tree nodes whose displayed values are formatted accordingly.
• WMS_LABEL_SET_FORMATS — Warehouse Management label components that reference BSC format definitions.
• POR_FEED_FIELD_FORMATS — Purchasing feed field formats that reuse the same numeric format identifiers.

These relationships confirm that the table, although standalone in Data Vault terms, functions as a shared formatting dictionary across BSC and adjacent modules. Its primary key constraint BSC_SYS_FORMATS_PK and unique index BSC_SYS_FORMATS_U1 are the only structural dependencies, and no packaged API is documented against it.