Search Results first_open_requirements_date




Overview
Explore in AI Chat

Overview

The WIP_JOB_OPEN_ALLOCATIONS_V view is a reporting object in the Oracle E-Business Suite Work in Process (WIP) module, owned by the APPS schema and carrying a VALID status. Its stated purpose is to expose open allocations for jobs — that is, discrete work order requirements that remain unsatisfied because the required material quantity exceeds the quantity already issued or otherwise supplied. A key detail is the view's status filter: it restricts results to jobs whose STATUS_TYPE IN (3, 4), which correspond to Released and Complete job statuses in the WIP_JOB_STATUS lookup. Only discrete jobs (ENTITY_TYPE = 1) with genuine outstanding material demand, and with no supply subinventory assigned, appear in the result set. The view further intersects each requirement with its parent operation and applies a count point filter (COUNT_POINT_TYPE IN (1, 2) or NULL), so only requirements tied to counting operations are surfaced. The view is a read-only construct intended for reports, dashboards, integration extracts, and ad hoc analysis rather than transactional processing.

Underlying Base Objects

The view is defined over ten documented base objects, joined in a single flat query. The driving table is WIP_DISCRETE_JOBS, linked to WIP_ENTITIES on WIP_ENTITY_ID to obtain the job name and organization context. The requirement side is driven by WIP_REQUIREMENT_OPERATIONS, which supplies required quantity, issued quantity, supply subinventory, and the requirement's transaction identifiers. WIP_OPERATIONS is outer-joined to capture the count point type for each operation sequence. MTL_SYSTEM_ITEMS_KFV is outer-joined on both INVENTORY_ITEM_ID and ORGANIZATION_ID to resolve the primary item's concatenated segments and description. MFG_LOOKUPS is joined to translate STATUS_TYPE into a readable status meaning via the WIP_JOB_STATUS lookup type. WIP_LINES, WIP_SCHEDULE_GROUPS, and WIP_PARAMETERS provide supplementary job attributes — line code, schedule group name, and the organization-level WIP parameter row that must exist for the job. A correlated subquery against MTL_TXN_REQUEST_LINES nets off pending move-order demand when evaluating whether a requirement is genuinely open. Except for MFG_LOOKUPS, the referenced objects are resolved through synonyms in the APPS schema.

Key Columns

Common Use Cases and Queries

Typical uses include material shortage reporting, open-requirement aging, and feeding downstream planning extracts. Because the view aggregates with DISTINCT and MIN, it returns one row per job/item combination rather than one row per requirement, so it is suited to headline shortage reporting rather than line-level detail. A representative query follows:

SELECT wip_entity_name,
       job_description,
       primary_item,
       status,
       first_open_requirements_date
FROM   apps.wip_job_open_allocations_v
WHERE  organization_id = :org_id
AND    first_open_requirements_date < SYSDATE
ORDER  BY first_open_requirements_date;

The view can also be joined back to jobs or items for drill-down reports, or filtered by line_code and schedule_group_name for shop-floor execution reviews. Because it targets only Released and Complete discrete jobs with unmet, non-subinventoried demand, it should not be used as a substitute for the full WIP_REQUIREMENT_OPERATIONS table when complete requirement history is required.