Search Results cogs_date_fk

Overview

OPI_EDW_OPMCOGS_FCV is a source view owned by the APPS schema and delivered as part of the OPI – Operations Intelligence product family in Oracle E-Business Suite 12.1.1 and 12.2.2. Its purpose is to expose Cost of Goods Sold (COGS) and shipment data originating from the Process Manufacturing (OPM) application tables in a form suitable for extraction into the Enterprise Data Warehouse. The view is a foundation object for the Operations Intelligence ETL layer, which loads OPM transactional data into the EDW star schema for analytical reporting.

A defining characteristic of this view is its construction of surrogate foreign-key columns that follow the EDW naming conventions. Each key is built by concatenating business identifiers with a delimiter and an instance code, ensuring that records sourced from multiple OPM instances remain uniquely identifiable within a single warehouse. This design allows the EDW to unify data across disparate operating units and installations without collision.

Underlying Base Objects

The ETRM metadata lists no explicitly documented base objects for this view, although the view text reveals its dependency on several Process Manufacturing and Oracle Inventory tables. Prominent among them are the OPM transaction table (aliased ITP, providing TRANS_ID and LINE_ID), the OPM order header and detail tables (aliased OOH and OOD, supplying ORDER_DATE, ACTUAL_SHIPDATE, PROMISED_SHIPDATE, BILLING_CURRENCY, and related columns), inventory master data (aliased MSI, IWM, and MP, providing INVENTORY_ITEM_ID, MTL_ORGANIZATION_ID, and ORGANIZATION_CODE), and customer/site tables (aliased OCMBC and OCMSC, supplying bill-to and ship-to site use identifiers).

In addition to these tables, the view invokes packaged PL/SQL functions that resolve warehouse dimension keys at query time. EDW_TIME_PKG.CAL_DAY_FK maps a date and set of books to a calendar day key; EDW_TRD_PARTNER_PKG.CUSTOMER_SITE_FK and EDW_GEOGRAPHY_PKG.CUSTOMER_SITE_LOCATION_FK resolve trading-partner and geography keys; and EDW_UTIL.GET_EDW_UOM derives a unit-of-measure key. The view therefore depends on both the OPM base tables and the EDW utility packages that must be present in the APPS schema.

Key Columns

• COGS_PK — composite primary key concatenating the OPM transaction ID, a literal segment, line ID, and instance code.
• COGS_DATE and COGS_DATE_FK — the actual ship date and its resolved calendar day foreign key.
• ORDER_LINE_ID — line identifier composed of the LINE_ID, instance code, and source system.
• TOP_MODEL_ITEM_FK, ITEM_ORG_FK, INV_ORG_FK — item and inventory organization keys built from inventory item ID, organization ID, and instance code.
• OPERATING_UNIT_FK — operating unit identifier derived from ORG_ID and instance code.
• CUSTOMER_FK, BILL_TO_SITE_FK, SHIP_TO_SITE_FK, BILL_TO_LOC_FK, SHIP_TO_LOC_FK — trading-partner and geography keys resolved through the EDW packages.
• SALES_CHANNEL_FK, PRIM_SALES_REP_FK, PROJECT_FK, TASK_FK, ORDER_CATEGORY_FK, ORDER_TYPE_FK — placeholder columns populated with the constant 'NA_EDW', indicating process-manufacturing data does not supply these dimensions.
• BASE_UOM_FK, TRX_CURRENCY_FK, BASE_CURRENCY_FK — unit-of-measure and currency keys for valuation.
• ORDER_DATE, MONTH_BOOKED_FK, DATE_BOOKED_FK, DATE_PROMISED_FK — order booking and promise dates together with their calendar keys.

Common Use Cases and Queries

The view is typically consumed by the Operations Intelligence ETL process rather than by end users directly. Analysts and technical consultants use it to verify COGS extraction, reconcile OPM shipment values to the warehouse, and diagnose load failures. A representative query extracts cost of goods sold by shipping date:

SELECT COGS_PK, COGS_DATE, ORDER_LINE_ID, ITEM_ORG_FK, OPERATING_UNIT_FK, TRX_CURRENCY_FK FROM APPS.OPI_EDW_OPMCOGS_FCV WHERE COGS_DATE >= :start_date AND COGS_DATE < :end_date;

Because the 'NA_EDW' placeholders carry no analytical value, most warehouse mappings deliberately exclude those columns from the target fact table. Troubleshooting efforts should focus on the packaged dimension functions, since a failure within EDW_TIME_PKG, EDW_TRD_PARTNER_PKG, or EDW_GEOGRAPHY_PKG will surface as a query error rather than as a base-table issue.