Search Results cm_whse_eff_v

Overview

CM_WHSE_EFF_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the GMF — Process Manufacturing Financials product family, the module that governs costing, rollups, and period-close processing for process-manufacturing organizations. According to the Oracle ETRM metadata, the view is VALID in the documented environment and exists specifically to enable a master-detail relationship on the Rollup Target Warehouses form.

In the Oracle Forms architecture, a master-detail block requires a queryable record source that exposes a join key shared with its detail block. The Rollup Target Warehouses form uses warehouse and calendar context to drive the detail records it displays. Rather than duplicating that logic inside the form's base table block, Oracle defined CM_WHSE_EFF_V as a thin projection over the warehouse effectiveness data so the form can enforce the master-detail coordination cleanly. The view is therefore a form-support object first and a reporting object second, though because it is exposed in APPS it is equally usable as a query target in custom reports, Discoverer workbooks, and BI Publisher data models.

Users searching on "period_code" typically arrive at this view because it is one of the few GMF objects that surfaces PERIOD_CODE alongside an organization code and a calendar code without requiring a join to a period or calendar table. That makes it a convenient lookup for period-scoped warehouse validity questions.

Underlying Base Objects

The view is defined over a single referenced base object, CM_WHSE_EFF, which is documented in ETRM as a SYNONYM. The synonym resolves at runtime to the underlying GMF warehouse effectiveness table in the Process Manufacturing schema. The view text performs a distinct projection of three columns from that base object:

SELECT DISTINCT ORGN_CODE, CALENDAR_CODE, PERIOD_CODE FROM CM_WHSE_EFF

Two properties of this definition are significant. First, the DISTINCT keyword means the view returns the set of unique organization/calendar/period combinations present in CM_WHSE_EFF, not one row per warehouse record. Duplicate warehouse rows that share the same three key values collapse into a single row. Second, the column list is deliberately narrow — no warehouse identifiers beyond ORGN_CODE, no validity dates, no status flags. This confirms the view's purpose as a key-source for form coordination rather than a substantive data extract.

Because the view is defined only over CM_WHSE_EFF, any predicate applied to it is effectively a predicate on the base table. There is no aggregation, no outer join, and no subquery, so the view should be treated as a filtered key list with no independent business logic of its own.

Key Columns

• ORGN_CODE — The organization (warehouse) code. In GMF, this identifies the process-manufacturing organization whose effectiveness is being tracked. It is the primary column through which the Rollup Target Warehouses form links its master record to the detail.
• CALENDAR_CODE — The accounting or manufacturing calendar associated with the record. This identifies which period calendar governs the periods in which the organization is considered effective.
• PERIOD_CODE — The specific period within the referenced calendar. This is the column most frequently searched by users and is the finer-grained key that, together with CALENDAR_CODE and ORGN_CODE, uniquely identifies a row returned by the view.

The combination of ORGN_CODE, CALENDAR_CODE, and PERIOD_CODE constitutes the effective unique key of the view after the DISTINCT operation. No other columns are exposed, so any query needing warehouse descriptions, dates, or flags must join back to CM_WHSE_EFF or to the standard inventory organization and period tables.

Common Use Cases and Queries

The most common use is resolving which warehouses are effective in a given period, and conversely which periods exist for a given warehouse. A typical lookup filtered on the searched column is:

SELECT ORGN_CODE, CALENDAR_CODE, PERIOD_CODE FROM APPS.CM_WHSE_EFF_V WHERE PERIOD_CODE = :p_period ORDER BY ORGN_CODE;

To populate a period list of values for a specific organization and calendar:

SELECT DISTINCT PERIOD_CODE FROM APPS.CM_WHSE_EFF_V WHERE ORGN_CODE = :p_org AND CALENDAR_CODE = :p_calendar ORDER BY PERIOD_CODE;

To enumerate valid organization/calendar pairings for cross-validation in a rollup report:

SELECT ORGN_CODE, CALENDAR_CODE, COUNT(DISTINCT PERIOD_CODE) period_count FROM APPS.CM_WHSE_EFF_V GROUP BY ORGN_CODE, CALENDAR_CODE ORDER BY 1, 2;

Because the view is a key-only projection, any report requiring descriptive attributes should join it to the base table or to standard GMF and inventory lookups on ORGN_CODE. Users should also note that since CM_WHSE_EFF_V derives from CM_WHSE_EFF, its contents reflect the current state of that base object at query time and carry the same security and MOAC implications as direct access to CM_WHSE_EFF.