Search Results picked_partial

Overview

PMIFV_SALES_DETAILS_V is an APPS-owned database view within the Process Manufacturing Intelligence (PMI) product family of Oracle E-Business Suite. It is a reporting and analytics construct rather than a transactional entity, and its purpose is to expose the details of all shipped sales orders, joined to their corresponding General Ledger periods, in a single denormalized result set. This makes it suitable for consumption by Process Manufacturing Intelligence dashboards, operational reporting, and downstream data extraction routines that require sales shipment facts aligned to financial calendar periods.

The view straddles two functional worlds: Oracle Order Management (and its Process Manufacturing order-entry equivalents) on the transactional side, and Oracle General Ledger on the accounting-period side. It normalizes order header, order line, item master, customer, address, warehouse, and currency attributes into a flat structure, while also invoking PMI package functions to derive surcharge and unit-of-measure conversion values at query time. Its status is VALID, and it is documented as a view in both EBS 12.1.1 and 12.2.2. The ETRM metadata notes that the underlying objects and structure are largely consistent across these releases, with minor variation in the referenced source objects. The view is security-aware, as evidenced by its dependency on PMI_SECURITY_PKG, meaning row-level access can be constrained by the security profile of the invoking user.

Underlying Base Objects

The view is defined over a substantial set of base tables, synonyms, and views. Its primary fact sources are OP_ORDR_HDR and OP_ORDR_DTL, the Process Manufacturing order header and detail stores, supplemented by OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, and OE_SYSTEM_PARAMETERS_ALL from standard Order Management. Item and warehouse master data are drawn from IC_ITEM_MST, IC_WHSE_MST, MTL_SYSTEM_ITEMS, and IC_WHSE_MST. Customer, address, and salesperson attributes come from OP_CUST_MST, SY_ADDR_MST, PMI_HZ_PARTY_V, and RA_SALESREPS_ALL, while organization and operating unit context is supplied by HR_OPERATING_UNITS and SY_ORGN_MST.

Financial calendar and period context is provided by GL_PERIODS, GL_SETS_OF_BOOKS, GL_PLCY_MST, and PMI_GL_CALENDAR_V, which is what enables the "associated to corresponding GL periods" linkage described in the object documentation. Runtime derivation logic is centralized in the PMI_SALES_PKG, PMI_COMMON_PKG, PMI_SECURITY_PKG, GMICUOM, and FND_PROFILE packages, which supply surcharge calculations, UOM conversions, security filtering, and profile option lookups respectively.

Key Columns

The view exposes a broad column list. Identity and status columns include ORDER_SOURCE_SYSTEM (a literal 'OPSO'), ORDER_ID, ORDER_NO, ORDER_DATE, ORDER_STATUS, LINE_ID, LINE_NO, and LINE_STATUS. Item attributes include ITEM_ID, ITEM_NO, ITEM_DESC1, ITEM_UM, and SALES_CLASS. Quantity and pricing columns include ORDER_QTY1, ORDER_UM1, a derived UOM-converted quantity via GMICUOM.I2UOM_CV, BASE_PRICE, NET_PRICE, EXTENDED_PRICE, PRICE_UM, and PRICE_SELECTED_IND. Currency columns include BILLING_CURRENCY, BASE_CURRENCY, and EXCHANGE_RATE, with a DECODE expression computing converted extended price depending on whether billing and base currencies match and on the MUL_DIV_SIGN indicator.

Two columns are computed through PL/SQL functions: PMI_SALES_PKG.PMISA_GET_CHARGE returns the charge amount for the order line, and GMICUOM.I2UOM_CV performs item-specific UOM conversion. Customer data is represented for both billing and shipping parties (CUST_NO, CUST_NAME, CUST_CLASS, ADDR_ID, ADDR4, STATE_CODE, COUNTRY_CODE). Shipping and warehouse attributes include SHIPPER_CODE, SHIP_MTHD, TO_WHSE, FROM_WHSE, SHIP_STATUS, and ORGN_CODE/ORGN_NAME from the shipping organization. This dual billing/shipping customer structure supports margin and territory analysis in addition to shipment reporting.

Common Use Cases and Queries

The most common use case is GL-period-aligned sales reporting, where analysts need shipped order lines bucketed by accounting period for revenue recognition, forecasting, or PMI analytics. Given the search term "qc_grade_wanted," users may be looking to correlate shipped sales detail with quality grade attributes captured elsewhere in Process Manufacturing. The view itself does not expose a QC grade column directly, so such analysis would require joining to quality tables (for example, quality results or grade master data) by ITEM_ID or ORDER/LINE identifiers.

A representative query selecting by period and customer:

• SELECT order_no, order_date, item_no, order_qty1, extended_price, billing_currency FROM apps.pmifv_sales_details_v WHERE order_date BETWEEN :p_from AND :p_to AND cust_no = :p_cust;

A query aggregating shipped revenue by item and warehouse:

• SELECT item_no, orgn_code, SUM(extended_price) revenue FROM apps.pmifv_sales_details_v WHERE order_status = :p_status GROUP BY item_no, orgn_code;

Because the view calls PL/SQL functions and applies security package logic, queries should filter aggressively on indexed columns such as ORDER_DATE, ORDER_NO, or CUST_NO to limit the volume of function invocations and reduce execution time.