Search Results engineering_date




Overview
Explore in AI Chat

Overview

APPS.MTL_SYSTEM_ITEMS_FKEYS_V is a denormalized reporting view in Oracle E-Business Suite that exposes the foreign key descriptive values associated with item master records. The view is anchored on MTL_SYSTEM_ITEMS_B and resolves the surrogate identifiers stored on the item definition — buyer, planner, source organization, ATP rule, picking rule, hazard class, UN number, routing, receiving routing, coverage template, and dozens of lookup-code columns — into their human-readable equivalents. Its purpose is to spare report developers and integration engineers from writing the large set of outer joins required to translate these coded attributes.

The view is equally relevant to EBS 12.1.1 and 12.2.2. Because the underlying item tables in 12.2.x were reorganized for organization-specific item attributes, the 12.2.2 metadata identifies MTL_SYSTEM_ITEMS_B as a synonym, which shields dependent code from the physical table/partitioning differences between the two releases. The primary key of the view remains the (INVENTORY_ITEM_ID, ORGANIZATION_ID) pair, since MTL_SYSTEM_ITEMS_B is partitioned by organization.

The user search term "lookup_type" is highly relevant here: a dominant portion of the view's columns are resolved through lookup views, specifically FND_COMMON_LOOKUPS, FND_LOOKUPS, MFG_LOOKUPS, CS_LOOKUPS, and PO_LOOKUP_CODES. Each of those is keyed in part by a LOOKUP_TYPE value (for example, ITEM_TYPE, WIP_SUPPLY_TYPE, or PTO_ITEM_TYPE), which is why queries against this view frequently constrain on lookup semantics rather than on stored code values.

Underlying Base Objects

The documented base objects fall into four functional groups:

Almost every non-item table is joined with an Oracle outer-join marker (+), so the view returns a row for every item/organization combination even when optional attributes such as hazard class, UN number, or coverage template are null.

Key Columns

  • INVENTORY_ITEM_ID, ORGANIZATION_ID — the composite identifier of the item within an inventory organization.
  • ORGANIZATION_CODE, MASTER_ORGANIZATION_ID — organization context derived from MTL_PARAMETERS; the base table also exposes a DECODE that yields 0 for the master organization and 1 otherwise, an internal control marker.
  • FULL_NAME — the buyer's name from PER_PEOPLE_F, resolved date-effectively.
  • RA1.NAME, RA2.NAME, TERM.NAME — receiving routing, return routing, and payment term descriptions.
  • PUN.UN_NUMBER, HAZ.HAZARD_CLASS — transport and safety classification data.
  • PO1–PO4.DISPLAYED_FIELD — descriptive text pulled from PO_LOOKUP_CODES for purchasing-related code columns.
  • FND.MEANING, FND2.MEANING, FND1.MEANING, FND3.MEANING, FND4.MEANING, CSL.MEANING — decoded lookup meanings from the FND and CS lookup views, each selected using its respective LOOKUP_TYPE context.
  • MFG1–MFG24.MEANING — the largest block of columns, decoding Manufacturing (MFG_LOOKUPS) reference codes such as item type, supply type, WIP supply type, and cost-related classifications.
  • ATP.RULE_NAME, PICK.PICKING_RULE_NAME, ROUT.ROUTING_NAME — rule and routing descriptions.
  • MSI.ENGINEERING_DATE, COV.NAME — engineering effectivity and the service coverage template name.

Common Use Cases and Queries

The view is typically used for item master extracts, interface staging tables, and operational reports that must display decoded attribute values rather than internal codes. A representative query filtering on organization and returning decoded lookup meanings is:

  • SELECT inventory_item_id, organization_id, organization_code, full_name, mfg1_meaning, mfg2_meaning, atp_rule_name FROM apps.mtl_system_items_fkeys_v WHERE organization_id = :p_org_id AND mfg1_meaning = 'Finished Good';
  • SELECT organization_code, COUNT(*) FROM apps.mtl_system_items_fkeys_v GROUP BY organization_code; to profile item counts by organization.
  • SELECT s.inventory_item_id, s.full_name FROM apps.mtl_system_items_fkeys_v s, fnd_common_lookups l WHERE l.lookup_type = 'ITEM_TYPE' AND s.mfg1_meaning = l.meaning; to reconcile MFG lookup values against the FND lookup registry, which directly addresses the "lookup_type" search intent.

Because many of the joins are outer joins to views and synonyms, and because HR_SECURITY predicates are applied through accessed packages, performance is best when queries filter by ORGANIZATION_ID or INVENTORY_ITEM_ID rather than scanning the full view.