Search Results wip_bis_prod_indicators




Overview
Explore in AI Chat

Overview

The WIP.WIP_BIS_PROD_INDICATORS table is a Work in Process (WIP) business intelligence staging and reporting table within Oracle E-Business Suite. As its name implies (BIS — Business Intelligence System), it stores aggregated productivity, utilization, and yield metrics collected by job, assembly, department, and resource. Rather than holding transactional shop-floor data, this table serves as a denormalized fact store populated by concurrent programs (indicated by REQUEST_ID, PROGRAM_ID, and PROGRAM_APPLICATION_ID columns) that periodically extract and summarize WIP execution data for analytical reporting.

The table is physically owned by the WIP schema and is valid in both EBS 12.1.1 and 12.2.2, containing 48 documented columns in the 12.2.2 ETRM physical schema. It is a standalone table from a referential perspective, though the mined foreign-key metadata identifies relationships to FV_LEGAL_ENTITIES (via LEGAL_ENTITY_ID) and BOM_DEPARTMENTS (via DEPARTMENT_ID). The heuristic Data Vault classification is standalone, suggesting this object is best modeled as an independent satellite or fact table rather than a hub or link, since no other tables depend on it through enforced foreign keys.

Key Information Stored

The most operationally significant columns fall into organizational, temporal, resource, and quantitative categories:

The documented schema does not expose a single explicit surrogate primary key column. The combination of ORGANIZATION_ID, WIP_ENTITY_ID, DEPARTMENT_ID, RESOURCE_ID, OPERATION_SEQ_NUM, and TRANSACTION_DATE constitutes the natural business-key candidate that uniquely identifies a metric row. SHARE_FROM_DEPT_ID supports cross-department resource sharing analysis.

Common Use Cases and Queries

This table is typically queried for manufacturing performance dashboards. A representative pattern retrieves yield by department for a given period:

SELECT department_code,
       SUM(total_quantity)          AS total_qty,
       SUM(scrap_quantity)          AS scrap_qty,
       SUM(applied_hours_prd)       AS productive_hrs,
       SUM(available_hours)         AS avail_hrs,
       ROUND(SUM(applied_hours_prd) /
             NULLIF(SUM(available_hours),0)*100, 2) AS utilization_pct
FROM   wip_bis_prod_indicators
WHERE  organization_id = :org_id
AND    year = :yr AND month = :mo
GROUP  BY department_code;

Typical scenarios include yield trending by assembly, utilization reporting by resource, scrap-rate analysis by job, and period-over-period comparison using the YEAR/MONTH/QUARTER columns. Because data is staged by concurrent request, joins to FND_CONCURRENT_REQUESTS on REQUEST_ID verify the freshness of a given snapshot.

Related Objects

  • FV_LEGAL_ENTITIES — joined on LEGAL_ENTITY_ID = FV_LEGAL_ENTITIES.LEGAL_ENTITY_ID.
  • BOM_DEPARTMENTS — joined on DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID.
  • WIP_ENTITIES — the job/repetitive schedule master referenced by WIP_ENTITY_ID.
  • MTL_SYSTEM_ITEMS_B — source of INVENTORY_ITEM_ID assembly descriptions.
  • BOM_RESOURCES / BOM_DEPARTMENT_RESOURCES — resolve RESOURCE_ID and RESOURCE_CODE.
  • HR_ALL_ORGANIZATION_UNITS — resolves ORGANIZATION_ID and OPERATING_UNIT_ID.
  • FND_CONCURRENT_REQUESTS — validates the populating request via REQUEST_ID.