Search Results qty_produced

Overview

APPS.PMI_REP_TOP_10_PROD_INGRD_V is a reporting view in Oracle EBS Process Manufacturing (OPM) that exposes summarized production yield and ingredient consumption data at the item, organization, and GL period level. It is designed primarily to support "top 10" style analytical reports comparing quantities produced against quantities consumed for manufactured items. The view surfaces the user-searched column QTY_PRODUCED, which is derived from the underlying WHSE_YIELD_QTY column of the production summary table.

Functionally, the view acts as a denormalized reporting layer combining production summary facts with fiscal calendar context, item master reference data, and organization-level security filtering. It is not a transactional object; rather it is intended for read-only reporting and integration use, providing a consistent, period-aligned view of manufacturing performance.

Underlying Base Objects

The view is defined over five documented referenced objects:

• PMI_PROD_SUM (synonym) — the production summary fact source supplying yield and usage quantities and values, the GL period start date, company code, organization code, item, and convertible UOM.
• GL_PLCY_MST (synonym) — the fiscal policy master, joined on company code (CO_CODE) and set of books name to align production data to the correct fiscal policy.
• PMI_GL_TIME_V (view) — the GL time/calendar view supplying YEAR_ID, QUARTER_ID, and PERIOD_ID by matching the production summary's GL period start date between the calendar period start and end dates.
• IC_ITEM_MST (synonym) — the item master, joined on ITEM_ID to validate and enrich the item dimension.
• PMI_SECURITY_PKG (package) — invoked in the WHERE clause via PMI_SECURITY_PKG.SHOW_RECORD(PSUM.ORGN_CODE) to enforce organization-level security, ensuring users only see records for organizations they are authorized to access.

Key Columns

• COMPANY_ID (CO_CODE) — the company/legal entity owning the production record.
• ORGANIZATION_ID (ORGN_CODE) — the manufacturing organization, also the security filter key.
• YEAR_ID, QUARTER_ID, PERIOD_ID — fiscal calendar identifiers from the GL time view, used for period-based aggregation and trending.
• ITEM_ID — the produced/consumed item identifier from the item master.
• UOM (CONVERTIBLE_UOM) — the unit of measure for the summarized quantities.
• QTY_PRODUCED (WHSE_YIELD_QTY) — the warehouse yield quantity, i.e., the quantity produced. This is the column most relevant to the user's search.
• PROD_VALUE (WHSE_YIELD_VALUE) — the monetary value associated with the produced quantity.
• QTY_CONSUMED (WHSE_USAGE_QTY * -1) — the ingredient usage quantity, sign-inverted to a positive value for reporting.
• INGD_VALUE (WHSE_USAGE_VALUE * -1) — the monetary value of ingredients consumed, also sign-inverted.

Common Use Cases and Queries

Typical usage includes production yield vs. consumption analysis, top-10 produced item ranking, and period-over-period manufacturing reporting. The following query ranks produced quantities by item for a given period:

SELECT item_id, uom, SUM(qty_produced) qty_produced,
       SUM(prod_value) prod_value
FROM   apps.pmi_rep_top_10_prod_ingrd_v
WHERE  period_id = :period_id
GROUP  BY item_id, uom
ORDER  BY qty_produced DESC;

To compare produced versus consumed quantities at the organization level:

SELECT organization_id, item_id,
       SUM(qty_produced) qty_produced,
       SUM(qty_consumed) qty_consumed
FROM   apps.pmi_rep_top_10_prod_ingrd_v
GROUP  BY organization_id, item_id;

Because organization security is enforced internally by PMI_SECURITY_PKG.SHOW_RECORD, no additional organization filtering is required; results are automatically restricted to the organizations the querying user is permitted to see. Reporting tools and concurrent programs referencing this view should therefore be aware that returned row counts depend on the runtime security profile of the executing user.