Search Results bism_subjects




Overview
Explore in AI Chat

Overview

BISM_SUBJECTS is a reference (lookup) table owned by the APPLSYS schema in Oracle E-Business Suite, registered under the FND – Application Object Library product. It stores the canonical list of "subjects" used by the Business Intelligence/Student Information Management (BISM) subsystem that underpins Oracle's education and institutional reporting components. In EBS 12.1.1 and 12.2.2 the table is documented as VALID with a physical schema of only three columns, making it a compact, low-cardinality master of subject codes and their classifications.

The heuristic Data Vault classification mined from the foreign-key structure is standalone. From a modeling perspective this marks BISM_SUBJECTS as neither a pure hub nor a link nor a satellite, but rather a self-contained reference dimension. Its primary key, SUBJECT_ID, is referenced by external tables rather than referencing them, so it functions as an authoritative source of subject identity without being absorbed into a broader hub-and-satellite pattern.

Key Information Stored

Only three columns are documented in the 12.2.2 physical schema, all of which are material to its function:

  • SUBJECT_ID – the surrogate primary key. It is enforced by the unique index BISM_SUBJECTS_PK and uniquely identifies each subject row. Because it is a surrogate, it carries no intrinsic business meaning and should not be exposed as a natural code in down-stream reports.
  • SUBJECT_NAME – the human-readable, business-meaningful name of the subject. This column is the target of the unique index BISM_SUBJECTS_UK1, making it a business-key candidate and the natural identifier used when a subject must be resolved by name rather than by ID.
  • SUBJECT_TYPE – a classification attribute that categorises each subject into a type (e.g., distinguishing subject groupings for reporting or validity filtering). It allows consumers to segment the subject list without joining to additional tables.

Together, the two unique indexes (BISM_SUBJECTS_PK and BISM_SUBJECTS_UK1) confirm that both the surrogate identifier and the descriptive name are guaranteed unique, a requirement typical of seeded reference data.

Common Use Cases and Queries

BISM_SUBJECTS is most often joined to transactional or mapping tables that store a SUBJECT_ID. Typical queries resolve the surrogate key back to a readable name for reporting, or validate that a submitted subject exists before an insert. For example, resolving a name from an ID:

  • SELECT subject_id, subject_name, subject_type FROM applsys.bism_subjects WHERE subject_id = :p_id;
  • SELECT subject_id FROM applsys.bism_subjects WHERE subject_name = :p_name; — used to enforce the business key prior to linking data.
  • SELECT subject_type, COUNT(*) FROM applsys.bism_subjects GROUP BY subject_type; — a validation report confirming the composition of the reference list.

Common scenarios include populating LOVs that present subjects to end users, generating subject-to-mapping reconciliation reports, and auditing referential integrity between BISM_SUBJECTS and its dependent tables. Because the table is small and seeded, it is generally cached by the application and queried infrequently.

Related Objects

The only documented foreign-key relationship is from BISM_SUBJECTS.SUBJECT_ID into the IGS_UC_COM_EBL_SUBJ table, indicating that subjects are shared with UCAS/common education components. Operationally, BISM_SUBJECTS is referenced by the tables and views in the BISM module that store subject identifiers, and is consumed through FND lookup mechanisms and concurrent reporting programs that list available subjects. Any table containing a SUBJECT_ID column participates in the referential model and should be joined to BISM_SUBJECTS via SUBJECT_ID for name resolution. Dependency analysis should also consider FND-side metadata and security that governs access to APPLSYS reference tables.