Search Results data_completion_date




Overview
Explore in AI Chat

Overview

APPS.MRP_BOM_PLAN_NAME_LOV_V is an Oracle E-Business Suite database view owned by the APPS schema that exposes a list of values (LOV) of production plan names together with supporting descriptive and item-level context. It is designed primarily to populate a plan-name selection list in Oracle Bills of Material and Oracle Material Requirements Planning (MRP) forms, allowing users to pick a valid plan compile designator that satisfies current data integrity constraints. The view returns the plan name, its description, the plan completion date, and item-level attributes such as item number, inventory use-up date, inventory item identifier, and organization identifier.

A distinguishing characteristic of this view is its strict filtering logic. Only plans whose explosion completion date precedes the data completion date, and whose data completion date precedes the plan completion date, are returned. Plan types are restricted to codes 1, 2, 3, and 4. Furthermore, the view includes only item/organization combinations whose inventory use-up date is greater than or equal to the current system date (TRUNC(SYSDATE)), effectively excluding expired items. These predicates tie the LOV to plans whose underlying data has completed the full MRP compilation cycle, ensuring that only meaningful, current plan names are offered.

Underlying Base Objects

The view is defined over the following documented base objects:

  • MRP_DESIGNATORS_VIEW (VIEW) — provides plan designator descriptions joined on the compile designator and organization identifier.
  • MRP_PLANS (SYNONYM) — supplies the plan name (via COMPILE_DESIGNATOR), plan completion date, explosion completion date, data completion date, and plan type used for the completion-date and plan-type filters.
  • MRP_PLAN_ORGANIZATIONS (SYNONYM) — joined with an outer join (+) at plan level 2 to resolve the planned organization for a given plan.
  • MRP_SYSTEM_ITEMS (SYNONYM) — provides item-level attributes including compile designator, inventory use-up date, inventory item identifier, and organization identifier, and enforces the use-up date filter.
  • MTL_ITEM_FLEXFIELDS (VIEW) — supplies the item number and is joined to MRP_SYSTEM_ITEMS on matching organization and inventory item identifiers.

The outer joins on MRP_PLAN_ORGANIZATIONS (PLAN_LEVEL, ORGANIZATION_ID, COMPILE_DESIGNATOR) allow the view to return plans even when no explicit plan-organization row exists for the selected plan, falling back to the plan's own organization. The relationship to MRP_PLANS is central, as three of the most restrictive predicates — EXPLOSION_COMPLETION_DATE, DATA_COMPLETION_DATE, and PLAN_COMPLETION_DATE — are evaluated against that object.

Key Columns

  • PLAN_NAME (MSI.COMPILE_DESIGNATOR) — the plan identifier used as the LOV display value.
  • DESCRIPTION (MD.DESCRIPTION) — the descriptive text of the plan, sourced from MRP_DESIGNATORS_VIEW.
  • COMPLETION_DATE (MP2.PLAN_COMPLETION_DATE) — the date on which the plan is expected to complete; also participates in the filter requiring it to be on or after DATA_COMPLETION_DATE.
  • ITEM (MIF.ITEM_NUMBER) — the concatenated item number from MTL_ITEM_FLEXFIELDS.
  • INVENTORY_USE_UP_DATE (MSI.INVENTORY_USE_UP_DATE) — the last date the item may be used; filtered to be on or after the current date.
  • INVENTORY_ITEM_ID (MSI.INVENTORY_ITEM_ID) — surrogate key for the item used in joins.
  • ORGANIZATION_ID (MSI.ORGANIZATION_ID) — the inventory organization to which the item belongs.

Note that DATA_COMPLETION_DATE is used internally in the view's WHERE clause as a filter criterion but is not exposed as a selected column. Users searching for this term should understand that it governs inclusion of plans in the results rather than being directly returnable through this view.

Common Use Cases and Queries

The predominant use case is populating an LOV in an MRP or BOM form. A typical query to retrieve plan names for the current organization is:

  • SELECT PLAN_NAME, DESCRIPTION, COMPLETION_DATE FROM APPS.MRP_BOM_PLAN_NAME_LOV_V WHERE ORGANIZATION_ID = :org_id ORDER BY PLAN_NAME;

Because the view enforces the current-date and completion-date predicates internally, no additional date filtering is generally required. For item-oriented lookups:

  • SELECT PLAN_NAME, ITEM, INVENTORY_USE_UP_DATE FROM APPS.MRP_BOM_PLAN_NAME_LOV_V WHERE ITEM = :item_number AND ORGANIZATION_ID = :org_id;

In integration or reporting scenarios, the view is often joined to other MRP objects using PLAN_NAME as the correlate. However, because the view aggregates across items and organizations, consumers should anticipate multiple rows per plan and apply appropriate DISTINCT or GROUP BY logic. If DATA_COMPLETION_DATE is required in output, it must be queried from MRP_PLANS directly, since the view only uses it as a filter.