Search Results end_onh_val_g




Overview
Explore in AI Chat

Overview

OPI.OPI_EDW_IPS_NET_CHG is an interface-layer staging table owned by the OPI (Oracle Process/Inventory Intelligence) schema and stored in the APPS_TS_INTERFACE tablespace. It holds net change inventory position data extracted from Oracle Inventory for consumption by the Oracle E-Business Suite data warehouse and embedded analytics (Daily Business Intelligence for Supply Chain and related IPS — Inventory Planning and Shipping — subject areas). Each row represents a net change snapshot for a combination of inventory organization, item, locator, lot, cost group, and accounting period, expressed in both quantity, base currency value (the "_B" suffix columns), and global/functional currency value (the "_G" suffix columns), which is directly relevant to the user's search term TOT_CUST_SHIP_VAL_G — the global-currency total customer shipment value.

Per the ETRM dependency metadata, the columns ROW_ID, CREATION_DATE, LAST_UPDATE_DATE, NETTABLE_FLAG, and the USER_ATTRIBUTE1–15 / USER_MEASURE1–5 / USER_FK1–5 extensibility columns confirm this is an ETL-controlled staging object built by Oracle's warehouse collection programs, not a transactional base table. The heuristic Data Vault classification mined from the FK structure is standalone, reflecting that the object behaves primarily as a denormalized satellite supplying measures rather than participating in a traditional hub/link/satellite network. Oracle flags this object as Oracle Internal Use Only: it is not supported for direct customer access except from standard Oracle Applications programs.

Key Information Stored

The table contains 99 documented columns. The most operationally significant, in the context of the searched value:

Common Use Cases and Queries

Typical usage is warehouse-load validation, reconciliation of shipped-to-customer value against GL or AR, and ad-hoc inventory turn / shipment reporting. A representative query pattern:

  • Aggregate customer shipment value by period and organization: SELECT INV_ORG_FK_KEY, PRD_DATE_FK_KEY, SUM(TOT_CUST_SHIP_VAL_G) FROM OPI.OPI_EDW_IPS_NET_CHG WHERE NETTABLE_FLAG = 'Y' GROUP BY INV_ORG_FK_KEY, PRD_DATE_FK_KEY;
  • Compare base versus global currency measures to detect exchange-rate setup gaps: SELECT INV_ORG_FK_KEY, SUM(TOT_CUST_SHIP_VAL_B - TOT_CUST_SHIP_VAL_G) FROM OPI.OPI_EDW_IPS_NET_CHG GROUP BY INV_ORG_FK_KEY;
  • Reconcile beginning-plus-activity-minus-ending balances using BEG_ONH_QTY, TOTAL_REC_QTY, TOT_ISSUES_QTY, TOT_CUST_SHIP_QTY, and END_ONH_QTY.
  • Filter by INSTANCE_FK_KEY or INV_PERIOD_STATUS_PK_KEY to isolate a specific warehouse instance or accounting period during ETL troubleshooting.

Related Objects

  • CS_SYSTEMS_ALL_B_TEMP — referenced by the documented FK OPI_EDW_IPS_NET_CHG.ROW_ID → CS_SYSTEMS_ALL_B_TEMP; the joining column is ROW_ID.
  • OPI.OPI_EDW_INV_ORG_D and other OPI EDW dimension tables — resolved via INV_ORG_FK_KEY and ITEM_ORG_FK_KEY.
  • OPI.OPI_EDW_ITEM_D / OPI_EDW_LOCATOR_D / OPI_EDW_LOT_D — dimension lookups for ITEM_ORG_FK_KEY, LOCATOR_FK_KEY, and LOT_FK_KEY.
  • OPI.OPI_EDW_PERIOD_D — resolves PRD_DATE_FK_KEY.
  • OPI.OPI_EDW_CURRENCY_D — resolves BASE_CURRENCY_FK_KEY for _B and _G measure interpretation.
  • OPI.OPI_EDW_IPS_FACTS (or equivalent IPS summary/fact tables) — the downstream aggregation targets loaded from this staging interface by the standard OPI collection programs.

Because the object is documented as Oracle Internal Use Only, integration should be attempted only through Oracle's standard collection and reporting programs rather than by direct SQL against this table.