Results for “wip_bis_prod_dept_yield”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

WIP_BIS_PROD_DEPT_YIELD is a Business Intelligence System (BIS) summary table owned by the WIP (Work in Process) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores aggregated production yield information organized by department, providing pre-computed manufacturing performance metrics that support the Oracle Manufacturing Analytics and BIS reporting layer. Rather than deriving yield figures from raw transaction tables at query time, this table persists denormalized yield data across the manufacturing organizational hierarchy, enabling fast aggregation, trend analysis, and dimensional reporting.

The table captures yield statistics such as total quantity and scrap quantity, together with the full contextual hierarchy — set of books, legal entity, operating unit, inventory organization, department, and geographic attributes. The presence of both identifiers and descriptive names (for example, ORGANIZATION_ID alongside ORGANIZATION_NAME, or DEPARTMENT_ID alongside DEPARTMENT_CODE) reflects its role as a reporting-optimized structure. The periodic columns (YEAR, QUARTER, MONTH, PERIOD_SET_NAME) indicate it is intended for time-series analysis of departmental yield performance.

Under the heuristic Data Vault classification mined from its foreign key structure, this object is modeled as a standalone structure. It does not participate in the classic hub-link-satellite pattern that many transactional EBS tables exhibit, which is consistent with its nature as a derived BI aggregate rather than a normalized operational entity.

Key Information Stored

The table contains 37 documented columns. The most significant fall into organizational, temporal, and quantitative categories:

• Department identification: DEPARTMENT_ID (foreign key to BOM_DEPARTMENTS) and DEPARTMENT_CODE identify the manufacturing department whose yield is being measured. This is the primary analytical grain of the table.
• Organization context: ORGANIZATION_ID and ORGANIZATION_NAME identify the inventory organization; OPERATING_UNIT_ID, LEGAL_ENTITY_ID (foreign key to FV_LEGAL_ENTITIES), and SET_OF_BOOKS_ID anchor the record to the financial and legal reporting hierarchy.
• Work order context: WIP_ENTITY_ID links the yield back to a discrete work order, while INVENTORY_ITEM_ID and OPERATION_SEQ_NUM identify the item and routing operation being measured.
• Geographic attributes: COUNTRY_CODE, REGION_CODE, and AREA_CODE with their corresponding name columns support regional roll-up reporting.
• Time dimensions: TRANSACTION_DATE, PERIOD_SET_NAME, YEAR, QUARTER, and MONTH provide the temporal axis for period-over-period comparisons.
• Yield metrics: TOTAL_QUANTITY and SCRAP_QUANTITY are the core quantitative measures from which yield percentages are derived.
• Audit and program columns: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, and PROGRAM_UPDATE_DATE record who created the row and which concurrent program populated it, plus EXISTING_FLAG to indicate status.

No surrogate primary key is explicitly documented in the available metadata; the grain is effectively defined by the combination of organizational, departmental, item, and temporal dimensions. Business-key candidates include DEPARTMENT_ID combined with ORGANIZATION_ID, WIP_ENTITY_ID, INVENTORY_ITEM_ID, OPERATION_SEQ_NUM, and the period columns.

Common Use Cases and Queries

This table is most commonly leveraged for manufacturing yield dashboards, scrap analysis, and department-level performance scorecards. Typical reporting scenarios include yield by department over time, scrap trending by organization, and comparative yield across legal entities or regions.

A representative query computing monthly yield percentage by department:

• SELECT department_code, year, month, SUM(total_quantity) total_qty, SUM(scrap_quantity) scrap_qty, (SUM(total_quantity) - SUM(scrap_quantity)) / SUM(total_quantity) * 100 yield_pct FROM wip.wip_bis_prod_dept_yield WHERE organization_id = :org_id GROUP BY department_code, year, month;

Other practical patterns include filtering by PERIOD_SET_NAME to align with a specific accounting calendar, joining to BOM_DEPARTMENTS to obtain department descriptions, and using the geographic columns to produce regional scrap heat maps. Because the table is pre-aggregated, these queries avoid the heavier joins required against WIP transactional tables such as WIP_DISCRETE_JOBS or WIP_MOVE_TXN_INTERFACE.

Related Objects

The documented foreign key relationships connect this table to the following significant objects:

• BOM_DEPARTMENTS — referenced via WIP_BIS_PROD_DEPT_YIELD.DEPARTMENT_ID. Provides department master data and is the primary dimensional join.
• FV_LEGAL_ENTITIES — referenced via WIP_BIS_PROD_DEPT_YIELD.LEGAL_ENTITY_ID. Supplies legal entity attributes for financial reporting.
• WIP_ENTITIES — related through WIP_ENTITY_ID, linking yield data to the work order header.
• MTL_SYSTEM_ITEMS_B — related through INVENTORY_ITEM_ID for item-level yield analysis.
• HR_ALL_ORGANIZATION_UNITS and ORG_ORGANIZATION_DEFINITIONS — related through ORGANIZATION_ID and OPERATING_UNIT_ID for organizational roll-up.
• GL_SETS_OF_BOOKS — related through SET_OF_BOOKS_ID for ledger context.
• FND_CONCURRENT_REQUESTS — related through REQUEST_ID and PROGRAM_ID, identifying the concurrent program that populated the yield records.

As a BIS aggregate, the table is typically refreshed by concurrent programs rather than maintained transactionally, and should be queried as a read-only analytical source within the WIP reporting layer.