Search Results mrp_ap_process_effectivity_v

Overview

The MRP_AP_PROCESS_EFFECTIVITY_V view is a Master Scheduling/MRP (MRP) diagnostic and skeleton view used to expose the effectivity relationships between bills of material and routings for a given inventory item and organization. It is one of a family of "MRP_AP_" application-program view constructs whose naming convention (items, BOMs, routings, planning parameters) indicates they support the internal processing logic of the Oracle MRP and Advanced Supply Chain Planning engines rather than being primary user-facing inquiry views.

The view consolidates BOM and routing references for an item so that the planning process can reconcile which assemblies have a bill of material, which have a routing, and where an alternate BOM designator must be matched against an alternate routing designator. The ETRM record for this object is marked "Not implemented in this database," meaning it is a documented internal structure that may not be present in every implementation; where present, it is generated by the MRP schema and consumed by the planning/process-effectivity logic. It presents a unified, union-based result set with effectivity date and "RN" rank columns that order the competing BOM/routing combinations.

Underlying Base Objects

Although the ETRM metadata documents no referenced base objects, the view text itself reveals its sources. It is defined over four MRP application snapshot/selection tables:

• MRP_AP_SYS_ITEMS_SN — the driving item/organization source.
• MRP_AP_MRP_PARAS_SN — planning parameter records, joined by organization.
• MRP_AP_BOMS_SN — BOM snapshot, supplying BILL_SEQUENCE_ID and ALTERNATE_BOM_DESIGNATOR.
• MRP_AP_OPR_RTNS_SN — routing snapshot, supplying ROUTING_SEQUENCE_ID and ALTERNATE_ROUTING_DESIGNATOR.

The view is built as a UNION ALL of two branches. The first branch uses outer joins (+) so that items with either a BOM but no routing, or a routing but no BOM, are surfaced. The second branch, aliased X, performs an inner join between BOMs and routings on assembly and organization, and additionally correlates ALTERNATE_BOM_DESIGNATOR to ALTERNATE_ROUTING_DESIGNATOR. Each branch emits rank columns RN1 through RN5 that permit deterministic selection when multiple matches exist.

Key Columns

• INVENTORY_ITEM_ID / ORGANIZATION_ID — the item and organization keys tying the row to the planning context.
• BILL_SEQUENCE_ID — identifies the BOM record; null when only a routing exists.
• ALTERNATE_BOM_DESIGNATOR — the alternate BOM label (e.g., a specific alternate bill).
• ROUTING_SEQUENCE_ID / ALTERNATE_ROUTING_DESIGNATOR — the routing identifier and its alternate designator, which is the column a user searching "alternate_routing_designator" would be examining.
• EFFECTIVITY_DATE — hard-coded as SYSDATE-3650, an artificial "always valid" effectivity date rather than a true calendar date.
• LINE_ID, PRIMARY_LINE_FLAG, PRODUCTION_LINE_RATE — emitted as NULL placeholders via TO_NUMBER(NULL), maintaining column position for unioned queries.
• RN1–RN5 — rank/sequence counters used to order and de-duplicate candidate BOM/routing pairs.

Common Use Cases and Queries

Typical usage is diagnostic: identifying items that have a BOM and routing whose alternate designators agree or disagree, and surfacing assemblies with only one of the two structures.

SELECT inventory_item_id,
       organization_id,
       alternate_bom_designator,
       alternate_routing_designator,
       routing_sequence_id
FROM   mrp_ap_process_effectivity_v
WHERE  organization_id = :org_id
AND    inventory_item_id = :item_id;

To find mismatched alternates:

SELECT *
FROM   mrp_ap_process_effectivity_v
WHERE  NVL(alternate_bom_designator,'X') <> NVL(alternate_routing_designator,'X');

Because the view relies on MRP_AP_*_SN snapshot tables and hard-codes the effectivity date, results reflect the state of the planning snapshot at execution time and should not be used as a substitute for the base BOM and routing inquiry forms.