Search Results earliest_effective_date




Overview
Explore in AI Chat

Overview

APPS.ENGBV_ECO_REVISED_ITEMS is a read-only Oracle EBS view that exposes Engineering Change Order (ECO) data associated with revised items. It is defined over ENG_REVISED_ITEMS, filtered against MTL_SYSTEM_ITEMS_B to validate that each revised item exists as a valid inventory item within the same organization. The view presents both user-facing display labels (via the "_KF", "_LA", and "_SEC" attribute conventions used by Oracle's Forms/BC4J translation layer) and underlying key and audit columns.

Its role is primarily reporting and integration: it allows external systems, custom reports, and OBIEE/BI Publisher extracts to retrieve ECO revision details without directly joining the base engineering tables. The "_KF:INV:MSTK:SY" key flexfield marker on REVISED_ITEM_NAME indicates the item name is resolved through the Inventory item key flexfield, and the "_LA" markers resolve lookup meanings for status, disposition, and yes/no flags.

The user's search term earliest_effective_date maps directly to the column alias EARLIEST_EFFECTIVE_DATE, which is sourced from ENG_REVISED_ITEMS.EARLY_SCHEDULE_DATE. This is the earliest date the revision may be applied, distinct from START_EFFECTIVE_DATE (SCHEDULED_DATE).

Underlying Base Objects

The join condition is: RE.ORGANIZATION_ID = SY.ORGANIZATION_ID AND RE.REVISED_ITEM_ID = SY.INVENTORY_ITEM_ID, with a security predicate enforcing RE.ORGANIZATION_ID IS NOT NULL. The view is declared WITH READ ONLY, so it cannot be used for DML.

Key Columns

  • REVISED_ITEM_NAME — Item identifier resolved via the inventory key flexfield.
  • NEW_ITEM_REVISION — The new revision introduced by the ECO.
  • START_EFFECTIVE_DATE — From SCHEDULED_DATE; the planned effective date.
  • EARLIEST_EFFECTIVE_DATE — From EARLY_SCHEDULE_DATE; the earliest permissible effective date. This is the column most relevant to the user's search.
  • ECO_NAME, CHANGE_DESCRIPTION, IMPLEMENTATION_DATE — ECO identity and descriptive context.
  • CANCELLATION_DATE, CANCELLATION_COMMENTS — Cancellation details where the ECO was terminated.
  • USE_UP_PLAN_NAME — Associated use-up plan.
  • Status/Disposition/MRP/UPDATE_WIP/USE_UP lookups — Resolved meanings via _LA markers.
  • ORGANIZATION_ID, REVISED_ITEM_ID, REVISED_ITEM_SEQUENCE_ID, BILL_SEQUENCE_ID, USE_UP_ITEM_ID — Keys for downstream joins.
  • CREATED_ON, CREATED_BY, UPDATED_ON, UPADTED_BY — Audit columns (note the documented typo "UPADTED_BY").

Common Use Cases and Queries

Typical scenarios include identifying revisions and their earliest effective dates, measuring lead time between earliest and scheduled dates, and reporting cancellations.

SELECT revised_item_name, new_item_revision, eco_name,
       start_effective_date, earliest_effective_date, implementation_date
FROM   apps.engbv_eco_revised_items
WHERE  organization_id = :org_id
  AND  earliest_effective_date IS NOT NULL
ORDER BY earliest_effective_date;

To compute the implementation window:

SELECT revised_item_name, eco_name,
       (start_effective_date - earliest_effective_date) AS lead_days,   -- approximate
       earliest_effective_date, start_effective_date
FROM   apps.engbv_eco_revised_items
WHERE  organization_id = :org_id;

For cancelled ECOs:

SELECT revised_item_name, eco_name, cancellation_date, cancellation_comments
FROM   apps.engbv_eco_revised_items
WHERE  cancellation_date IS NOT NULL
ORDER BY cancellation_date DESC;

Because the view is read-only and security-filtered by ORGANIZATION_ID, queries should always bind or filter by organization to remain consistent with Oracle's data security model and to return meaningful item-level results.