Search Results bom_department_classes

Overview

The view APPS.EDW_BRES_DEPT_CLASS_LCV is an Engineering (ENG) module database object in Oracle EBS 12.1.1 and 12.2.2. The "EDW" prefix indicates that it belongs to the Oracle E-Business Suite Data Warehouse / Business Intelligence (EDW/BRES) integration layer, and the "_LCV" suffix denotes a "List of Values" Consolidated View used for conformed-dimension extraction. Its stated purpose is to hold resource department class information and present it in a shape suitable for downstream warehouse loading.

The critical role of this view is denormalization. The base table BOM_DEPARTMENT_CLASSES stores the department class code keyed only by ORGANIZATION_ID, which is a surrogate with no human-readable context. Reporting tools such as the Oracle Business Intelligence Applications (OBIA) require globally unique, human-meaningful keys. The view resolves this by concatenating DEPARTMENT_CLASS_CODE, ORGANIZATION_CODE, and INSTANCE_CODE into the primary key column DEPARTMENT_CLASS_PK, formatted as CLASS-ORG-INSTANCE. It also emits NVL-protected organization codes to avoid null joins. The final WITH READ ONLY clause guarantees the view cannot be treated as an update target, which is correct for an extract object.

Underlying Base Objects

Although the ETRM entry records no documented base objects, the view text exposes them unambiguously:

• BOM_DEPARTMENT_CLASSES DC — the ENG base table holding DEPARTMENT_CLASS_CODE, DESCRIPTION, ORGANIZATION_ID, and audit columns. Joined on DC.ORGANIZATION_ID = MP.ORGANIZATION_ID + 0.
• MTL_PARAMETERS MP — the per-organization parameters table, supplying ORGANIZATION_CODE. This is the standard EBS construct that maps an ORGANIZATION_ID to a readable short code.
• EDW_LOCAL_INSTANCE INST — the instance-tagging seed object supplying INSTANCE_CODE, ensuring extracted rows are attributable to a specific EBS instance in a multi-instance warehouse.
• MFG_LOOKUPS ML — used in the second UNION ALL branch to inject the CRP report type, a manufactured "LINE" element for report definitions.

The +0 on the join predicate suppresses index usage of the numeric column in favor of a full scan on MTL_PARAMETERS, a deliberate choice for a bug-tolerant full-extract target. The UNION ALL deliberately fabricates synthetic rows not present in the base table, so row counts from the view are not equal to BOM_DEPARTMENT_CLASSES.

Key Columns

• DEPARTMENT_CLASS_PK — concatenated surrogate key DEPARTMENT_CLASS_CODE-ORGANIZATION_CODE-INSTANCE_CODE; the warehouse primary key.
• INSTANCE — INSTANCE_CODE from EDW_LOCAL_INSTANCE, identifying the source EBS instance.
• PLANT_FK — ORGANIZATION_CODE normalized to NA_EDW when null; the plant/warehouse foreign key.
• DEPARTMENT_CLASS_DP, NAME, DEPARTMENT_CLASS — three outbound representations, each combining code and organization in parentheses, providing presentation and drilldown variants.
• DESCRIPTION — the department class description.
• CREATION_DATE / LAST_UPDATE_DATE — audit stamps used for incremental (delta) loading.
• OPERATION_CODE and USER_ATTRIBUTE1–5 — exposed but hard-coded NULL in the view text, reserved placeholders for future extensibility.

Common Use Cases and Queries

Typical scenarios include department class dimension load, validation of organization-to-class mapping, and reconciliation of class counts per instance.

• Full extract for a warehouse staging table:
  SELECT department_class_pk, instance, plant_fk, department_class, description FROM apps.edw_bres_dept_class_lcv;
• Find classes assigned to a specific plant:
  SELECT department_class, description FROM apps.edw_bres_dept_class_lcv WHERE plant_fk = 'M1';
• Detect orphaned classes (no matching organization code, showing NA_EDW):
  SELECT department_class_pk FROM apps.edw_bres_dept_class_lcv WHERE plant_fk = 'NA_EDW';
• Incremental pull using audit stamps:
  SELECT * FROM apps.edw_bres_dept_class_lcv WHERE last_update_date >= :last_run;
• Cross-instance inventory:
  SELECT instance, COUNT(*) FROM apps.edw_bres_dept_class_lcv GROUP BY instance;

Because the view is read only and contains synthetic UNION rows, it should be used for extraction and reporting only, never as a source of truth for transactional maintenance.