Search Results warehouse_dp

Overview

The view EDW_PMI_PMIL_PWHS_LCV is a reporting and integration object delivered within the Process Manufacturing Intelligence (PMI) module of Oracle E-Business Suite. Its name follows the Enterprise Data Warehouse (EDW) naming convention, where LCV denotes a "Logical Conformed View." Such views are designed to present source data in a normalized, conformed shape so that downstream extract, transform, and load (ETL) routines, operational data store (ODS) builds, and analytical reporting layers can consume a consistent representation of warehouse and organization information. In EBS 12.1.1 and 12.2.2, the object is documented as a read-only view whose metadata records it as Not implemented in this database, meaning it exists only in environments where the PMI/ETRM data warehouse schema has been deployed. The view supplies the set of Process Manufacturing warehouse and organization combinations used to key dimensional models in PMI analytics.

Underlying Base Objects

The documented view text is defined over two base tables joined in a UNION ALL construct. The first SELECT draws from IC_WHSE_MST, the Process Manufacturing warehouse master table holding warehouse codes, names, and audit columns. The second SELECT draws from SY_ORGN_MST, the Process Manufacturing organization master table, producing an aggregate "ALL WAREHOUSES" row per organization. Both branches are cross-joined with EDW_LOCAL_INSTANCE, which supplies the INSTANCE_CODE that identifies the originating application instance in multi-instance EDW deployments. No additional referenced base objects are documented, indicating that the view is intentionally shallow and relies on these masters alone to project its conformed surface.

Key Columns

• WAREHOUSE_PK — Surrogate primary key built by concatenating warehouse code, instance code, and the literal "OPM" (or "PORG" for the aggregate rows).
• INSTANCE_CODE — Identifier of the source EBS instance, sourced from EDW_LOCAL_INSTANCE.
• ORGANIZATION_FK — Foreign key that joins warehouse rows to their owning inventory organization, again concatenating organization code, instance code, and "OPM". This is the column most commonly used to relate PMI warehouse facts to organization dimensions.
• WAREHOUSE / NAME — Warehouse code or, for the aggregate branch, an "ALL WAREHOUSES" label scoped to the organization.
• DESCRIPTION / WAREHOUSE_DP — Descriptive attributes; the aggregate branch returns "ALL WAREHOUSES(<orgn>)".
• CREATION_DATE / LAST_UPDATE_DATE — Audit stamps propagated from the source masters.
• USER_ATTRIBUTE1–5 — Placeholder columns returned as NULL to satisfy a standard conformed schema contract.

Common Use Cases and Queries

The view is typically queried to resolve organization-to-warehouse relationships for PMI fact loading, to populate warehouse dimension members, and to drive "all warehouses" reporting roll-ups. A common pattern filters by instance or joins facts on the organization foreign key, for example:

SELECT ORGANIZATION_FK, WAREHOUSE_PK, NAME, DESCRIPTION FROM EDW_PMI_PMIL_PWHS_LCV WHERE INSTANCE_CODE = :instance AND ORGANIZATION_FK = :org_fk;

Analysts also use it to enumerate distinct warehouses within an organization prior to aggregation, or to isolate the synthetic "ALL WAREHOUSES" row by testing for a NULL WAREHOUSE value. Because the object is documented as not implemented in the base database, DBAs must confirm its presence in the PMI/EDW schema before referencing it in concurrent programs, OBIEE repository mappings, or custom ETL packages.