Search Results currency_fk

Overview

PMI_EDW_MARGIN_F_FCV is a flat, denormalized view in the Oracle E-Business Suite Process Manufacturing Intelligence (PMI) module. PMI is documented in ETRM as Obsolete, meaning it is a legacy Business Intelligence (BI) component that was traditionally deployed alongside Oracle Process Manufacturing (OPM) to feed the Oracle Enterprise Data Warehouse (EDW). The view's name follows EDW naming conventions: the PMI_EDW_ prefix denotes the Process Manufacturing Intelligence EDW layer, MARGIN indicates a margin/fact-oriented subject area, F denotes a fact-style structure, and FCV typically signals a "fact currency view" or a conformed currency-bearing fact view.

The view exposes sales order line data — order headers, shipping lines, customers, items, warehouses and grades — combined with pricing, cost and currency information, so that a downstream warehouse can compute margin (price minus cost) at order-line grain. Its role is as a staging/interface source: EDW extraction programs query the view, transform the results, and load the fact tables of the analytical model. It is not a transactional view; it is intended for read-only, set-based extraction.

The ETRM record states that this object is "Not implemented in this database" for the sampled instance, and lists no documented base objects. The column list below is derived from the view text captured in the ETRM content excerpt.

Underlying Base Objects

ETRM 12.2.2 documentation records no explicitly referenced base objects for this view. The view SQL, however, clearly joins and references several OPM and common EBS entities by alias, plus PL/SQL package functions:

• SH — sales order header (order number, customer, order date, requested/promised ship dates, status, set of books).
• SD — sales order detail/line (line number, item, from warehouse, ship method, quantities, pricing, currency, exchange rate, dates).
• IM — item master (item number, stocking UOM).
• INST — OPM instance/organization code.
• BC, SC — bill-to and ship-to customer records (customer name).
• PM — pricing/margin reference supplying GL cost method.
• SOB — set of books, providing the functional currency and EDW calendar join key.
• Package functions: PMI_EDW_COMM_PKG (geography keys), EDW_TIME_PKG.CAL_DAY_FK (time dimension keys), PMI_SALES_PKG.PMISA_GET_CHARGE (freight/charges), GMICUOM.I2UOM_CV (UOM conversion), PMI_COMMON_PKG.PMICO_GET_COST (item cost).

Because the underlying tables are undocumented in ETRM, database lineage should be confirmed against the live view definition with USER_DEPENDENCIES or DBMS_METADATA.

Key Columns

The view produces surrogate/conformed keys and descriptive attributes. The most relevant columns include:

• Currency keys (currency_fk) — currency is supplied as SOB.CURRENCY_CODE and via base/billing currency columns used in the DECODE expression; EDW-style foreign keys for currency are typically resolved here so downstream facts carry a currency dimension reference. The user search term "currency_fk" aligns with this conformed-key pattern.
• Time foreign keys — SUBSTR(EDW_TIME_PKG.CAL_DAY_FK(...),1,120) for order date, requested/promised ship date, scheduled/actual ship date, required/actual delivery date.
• Customer/geography keys — PMI_EDW_COMM_PKG.GEOG_BILL_LOC_PK and GEOG_SHIP_LOC_PK, plus bill-to/ship-to customer names.
• Item and warehouse keys — composite strings of ORGN_CODE, ORDER_NO, LINE_NO, INSTANCE_CODE, warehouse and item number.
• Measures — ORDER_QTY1, BASE_PRICE, NET_PRICE, EXTENDED_PRICE, LIST_PRICE, and computed extended price adjusted by exchange rate and MUL_DIV_SIGN, plus a cost expression using GMICUOM.I2UOM_CV and PMICO_GET_COST.
• Descriptive — ORDER_STATUS, LINE_STATUS, SHIP_STATUS, SHIPPER_CODE, SHIP_MTHD, QC_GRADE_WANTED, GL_COST_MTHD.

Common Use Cases and Queries

Typical use is margin analysis and EDW extraction. A representative query selecting currency-bearing margin data is:

SELECT currency_code,
       order_no, line_no, order_qty1,
       net_price, extended_price,
       actual_shipdate
FROM   apps.pmi_edw_margin_f_fcv
WHERE  currency_code = 'USD'
AND    order_date >= TO_DATE('01-JAN-2023','DD-MON-YYYY');

Other scenarios include loading a currency-conformed fact table, reconciling OPM sales margin against GL revenue, and validating cost calculation logic by comparing PMICO_GET_COST results to inventory valuations. Note that PMI is obsolete; in 12.1.1/12.2.2 environments where the object is absent, equivalent data should be sourced from current OPM sales, pricing and costing views, or replaced by OBIEE/OTBI subject areas. Always verify object existence and column names in the target instance before deploying extract scripts.