Search Results cst_edw_cogs_v

Overview

The CST_EDW_COGS_V view is an Oracle E-Business Suite data-extraction object belonging to the Bills of Material (BOM) product family. It is designed to feed the Enterprise Data Warehouse (EDW) with Cost of Goods Sold (COGS) information derived from inventory and shipping transactions. As reflected in the view definition, its purpose is to assemble a denormalized record set that combines material transaction detail, picking and shipping data, customer and sales information, and a series of surrogate foreign keys produced by the EDW_DIM_FK package. The view is not implemented in the base transactional database schema; it exists for reporting and integration, populated at extract time. The presence of columns such as sets_of_books_id — searched by the user — aligns with multi-organization, multi-ledger reporting requirements, since COGS values must be attributed to the correct set of books for financial reconciliation. Because the view sits between the operational EBS tables and the dimensional model of the warehouse, it is typically consumed by ETL processes rather than by end-user forms.

Underlying Base Objects

The documented metadata indicates no base objects are explicitly registered in the ETRM repository for this view, which is common for EDW staging views that are materialized or referenced only during extract cycles. However, the view text reveals its primary dependencies. It reads from MTL_MATERIAL_TRANSACTIONS (aliased MMT) and its cost-element companion MTL_TRANSACTION_ACCOUNTS (MTA), which supply the transaction identifiers, cost element identifiers, currency, and accounting dates. Picking and shipping detail comes from picking line tables (PL, L, PLD), while customer and sales-channel attributes are drawn from order headers (H) and territory reference data (RT). Dimension keys are resolved through the EDW_DIM_FK package, which maps operational keys to warehouse surrogate keys. Instance context is provided by BIS_EDW_INSTANCE.GET_CODE. The sets_of_books_id concept surfaces indirectly through the operating unit and base currency logic applied to each transaction, ensuring COGS records are tied to the correct accounting ledger.

Key Columns

• COGS_PK — a composite primary key concatenating transaction ID, cost element ID, picking line detail, and instance code.
• INSTANCE_FK — identifies the source EBS instance for multi-instance consolidation.
• TOP_MODEL_ITEM_FK and ITEM_ORG_FK — dimensional references to the configured item and its organization.
• OPERATING_UNIT_FK and INV_ORG_FK — surrogate keys tying the COGS record to the reporting operating unit and inventory organization.
• CUSTOMER_FK, SALES_CHANNEL_FK, PRIM_SALES_REP_FK, and TERRITORY_FK — customer and sales attribution.
• TRX_CURRENCY_FK — transaction currency, defaulted from EDW_UTIL.GET_BASE_CURRENCY when a currency code is absent. This is the column most closely related to sets_of_books_id in reporting terms.
• Date foreign keys such as TRX_DATE_FK, DATE_BOOKED_FK, DATE_SHIPPED_FK, and DATE_SCHEDULED_FK — populate the warehouse time dimension.
• BASE_UOM_FK, BILL_TO_SITE_FK, SHIP_TO_SITE_FK, and SUB_INVENTORY_FK — additional dimensional context.

Common Use Cases and Queries

Typical consumers extract COGS rows for a given accounting period, join them to the ledger dimension to confirm sets_of_books_id alignment, and aggregate by operating unit, customer, or territory. A representative query follows:

SELECT c.OPERATING_UNIT_FK, c.TRX_CURRENCY_FK, c.TRX_DATE_FK, c.CUSTOMER_FK, COUNT(*) cogs_lines FROM CST_EDW_COGS_V c WHERE c.TRX_DATE_FK BETWEEN :start_date AND :end_date GROUP BY c.OPERATING_UNIT_FK, c.TRX_CURRENCY_FK, c.TRX_DATE_FK, c.CUSTOMER_FK;

Reporting teams also use the view to reconcile shipping operating unit COGS against general ledger entries, to feed profitability marts, and to validate that the reporting OU — not the selling OE OU — determines currency and base UOM, as the view comments explicitly instruct. When a sets_of_books_id lookup is required, it is resolved by joining the accounting ledger dimension to the operating unit foreign key.