Search Results wip_temp_reports




Overview
Explore in AI Chat

Overview

WIP_TEMP_REPORTS is a transient working table within the Work in Process (WIP) schema of Oracle E-Business Suite, documented in ETRM 12.2.2 as containing "temporary information used while generating reports." Unlike permanent WIP transactional tables such as WIP_ENTITIES or WIP_DISCRETE_JOBS, WIP_TEMP_REPORTS functions as a scratchpad area: concurrent programs and report generators populate rows into this table, consume them during processing or output, and the contents are subsequently cleared or overwritten by later runs. The table is classified under product WIP - Work in Process and is marked VALID in the ETRM repository.

Because the table depends on two foreign keys, the heuristic Data Vault classification mined from the FK structure is link. This suggests modeling WIP_TEMP_REPORTS as a relationship-style object that connects a work in process entity to an inventory item within an organization, rather than as a standalone hub or a descriptive satellite. The two inbound reference paths both converge on WIP_TEMP_REPORTS as the referencing (child) table, reinforcing its role as an associative staging structure.

Key Information Stored

The documented physical schema comprises 24 columns. The most significant are:

  • ORGANIZATION_ID — the inventory organization context for the report run; part of the composite reference to MTL_SYSTEM_ITEMS_B.
  • INVENTORY_ITEM_ID — the item being reported on; combined with ORGANIZATION_ID, this forms the foreign key to MTL_SYSTEM_ITEMS_B.
  • WIP_ENTITY_ID — the work in process entity associated with the report row; foreign key to WIP_ENTITIES.
  • PROGRAM_SOURCE — identifies the concurrent program or report that populated the row, allowing multiple report processes to share the table without corrupting each other's data.
  • DESCRIPTION — free-form descriptive text carried through for report output.
  • ATTRIBUTE1 through ATTRIBUTE6 — six generic descriptive flexfield-style columns reserved for report-specific values.
  • KEY1 through KEY6 — six generic key columns, typically used to store grouping, sort, or lookup values.
  • DATE1 through DATE6 — six generic date columns for storing report-relevant dates such as transaction dates, completion dates, or period boundaries.
  • LAST_UPDATED_BY — the standard EBS WHO column identifying the last user or process to modify the row.

No surrogate primary key is documented for this table, which is consistent with a staging table where uniqueness is neither required nor enforced. The business-key candidates are implicit rather than indexed: the combination of ORGANIZATION_ID, INVENTORY_ITEM_ID, WIP_ENTITY_ID, and PROGRAM_SOURCE provides the closest thing to a natural identifier for any given report row.

Common Use Cases and Queries

WIP_TEMP_REPORTS is primarily interrogated during troubleshooting of WIP concurrent programs and during the design of custom reports that follow Oracle's staging-table pattern. Typical scenarios include:

  • Determining which concurrent program left residual data behind, and whether cleanup is required.
  • Reviewing the data a WIP report selected before it was formatted into output.
  • Diagnosing why a report produced unexpected items or entities within a given organization.

A representative query to inspect residual rows by source program is:

SELECT organization_id, inventory_item_id, wip_entity_id, program_source, description, date1
FROM wip.wip_temp_reports
WHERE organization_id = :org_id
ORDER BY program_source, wip_entity_id;

A join to resolve item and entity detail follows the documented FK paths:

SELECT t.wip_entity_id, we.wip_entity_name, t.inventory_item_id, msib.segment1
FROM wip.wip_temp_reports t,
    wip.wip_entities we,
    inv.mtl_system_items_b msib
WHERE t.wip_entity_id = we.wip_entity_id
  AND t.inventory_item_id = msib.inventory_item_id
  AND t.organization_id = msib.organization_id;

Because the table is transient, any query should be treated as a point-in-time snapshot; results persist only until the owning concurrent program runs again.

Related Objects

The ETRM metadata documents two foreign key relationships, and these are the most significant objects to consider:

  • WIP_ENTITIES — joined via WIP_TEMP_REPORTS.WIP_ENTITY_ID = WIP_ENTITIES.WIP_ENTITY_ID. This is the primary WIP master entity reference.
  • MTL_SYSTEM_ITEMS_B — joined via the composite WIP_TEMP_REPORTS.INVENTORY_ITEM_ID and WIP_TEMP_REPORTS.ORGANIZATION_ID to the corresponding columns on the item master.

In practice, reporting and diagnostic work against WIP_TEMP_REPORTS frequently also involves WIP_DISCRETE_JOBS and WIP_REPETITIVE_SCHEDULES to expand entity context, MTL_PARAMETERS for organization validation, and the FND concurrent program and request tables (FND_CONCURRENT_PROGRAMS, FND_CONCURRENT_REQUESTS) to correlate PROGRAM_SOURCE with the originating submission. Custom reports modeled on Oracle's approach should follow the same insert-process-purge cycle to avoid accumulating stale rows.