Search Results edw_bres_resource_lcv




Overview
Explore in AI Chat

Overview

EDW_BRES_RESOURCE_LCV is a view owned by the APPS schema in Oracle E-Business Suite, classified under the Engineering (ENG) product module with a VALID status in both 12.1.1 and 12.2.2. The "EDW" prefix and the "LCV" (local consumer view) suffix indicate that it is a reporting-facing object designed for the Oracle Business Intelligence / Enterprise Data Warehouse extract layer rather than an operational forms object. Its stated purpose is simply to "hold resource information," and the view text confirms this: it consolidates work-center level resources defined in Oracle Bills of Material and Engineering.

The view serves as a flattened, denormalized staging source for downstream reporting and integration. Rather than requiring the reporting layer to join department, resource, organization, and instance tables directly, EDW_BRES_RESOURCE_LCV pre-joins them and concatenates the descriptive keys into human-readable composite identifiers. This makes it suitable for dimensional extracts where resource, department, organization, and instance must be presented as a single labeled key.

Underlying Base Objects

Although the ETRM 12.2.2 metadata records "none documented" for referenced base objects, the view text establishes the actual dependency set. The primary branch is a four-table inner join:

A second, UNION ALL branch introduces line-based resources drawn from an aliased line object (L), exposing LINE_CODE, MINIMUM_RATE, and MAXIMUM_RATE while substituting 'NA_EDW' placeholders for department and resource-group columns. The filter clause restricts rows to those whose RESOURCE or DEPARTMENT_RESOURCE records have a LAST_UPDATE_DATE later than 1000/01/01, effectively excluding never-maintained rows from the extract.

Key Columns

The projection establishes a stable column contract for consumers:

  • Composite resource key — RESOURCE_CODE concatenated with DEPARTMENT_CODE, ORGANIZATION_CODE, and INSTANCE_CODE.
  • INSTANCE_CODE — identifies the originating EBS instance for warehouse consolidation.
  • Resource group name — DRE.RESOURCE_GROUP_NAME, defaulted to 'NA_EDW' when null.
  • Formatted department/resource descriptions — patterns such as DEPARTMENT(RESOURCE(ORG)) and DEPARTMENT(MEANING(ORG)), where MEANING is the decoded MFG_LOOKUPS value.
  • RES.DESCRIPTION — free-text resource description.
  • Availability flag — DECODE of AVAILABLE_24_HOURS_FLAG to 'Y'/'N'.
  • Audit dates — GREATEST of DRE and RES last-update dates, plus creation date.
  • MINIMUM_RATE / MAXIMUM_RATE — numeric rate bands returned only for the line-resource branch; NULL for the department branch.

Common Use Cases and Queries

The most frequent consumer requirement is identifying the cost or billing rate window associated with a resource — precisely the "minimum_rate" search that led here. The RATE columns reside on the second UNION branch, so a query must tolerate NULLs on department rows:

  • Rate band lookup: SELECT composite_key, minimum_rate, maximum_rate FROM apps.edw_bres_resource_lcv WHERE minimum_rate IS NOT NULL;
  • Departmental resource roster: SELECT * FROM apps.edw_bres_resource_lcv WHERE composite_key NOT LIKE '%NA_EDW%';
  • Instance-scoped extract: adding WHERE instance_code = :instance to scope a warehouse load.
  • Incremental refresh: filtering on the last-update column to pull only recently changed resources.

Because the view performs functions and concatenation, predicates on the composite key are non-sargable; report authors should filter on constituent columns via the base tables where performance is critical. Note also that NULL rates arise structurally, not from missing data, so downstream logic must distinguish the two branches.