VIEW: APPS.OE_RELATED_ITEMS_MV

Source

View Text - Preformatted

SELECT mp.organization_id, rel.inventory_item_id, rel.related_item_id, rel.relationship_type_id, look.meaning relationship_type, rel.reciprocal_flag, mv.item_description related_item_description, mv.item related_item, B.primary_uom_code uom, B.list_price_per_unit unit_price, decode(rel.relationship_type_id,2,'R',4,'R',8,'R','A') substitution_type, B.customer_order_enabled_flag, B.bom_item_type from mtl_related_items_view rel, mfg_lookups look, oe_items_ord_mv mv, mtl_system_items_b B, mtl_parameters mp WHERE rel.related_item_id = mv.inventory_item_id and rel.organization_id = mp.master_organization_id and mp.organization_id = mv.organization_id and look.lookup_type='MTL_RELATIONSHIP_TYPES' and look.enabled_flag ='Y' and sysdate between nvl(look.start_date_active,sysdate) and nvl(look.end_date_active,sysdate) and rel.relationship_type_id = look.lookup_code AND mv.item_identifier_type = 'INT' AND mv.inventory_item_id = b.inventory_item_id AND mv.organization_id = b.organization_id and look.lookup_code <> '14' AND (Trunc(SYSDATE) BETWEEN Nvl(rel.start_date,trunc(SYSDATE)) AND Nvl(rel.end_date,trunc(SYSDATE)))

View Text - HTML Formatted

SELECT MP.ORGANIZATION_ID
, REL.INVENTORY_ITEM_ID
, REL.RELATED_ITEM_ID
, REL.RELATIONSHIP_TYPE_ID
, LOOK.MEANING RELATIONSHIP_TYPE
, REL.RECIPROCAL_FLAG
, MV.ITEM_DESCRIPTION RELATED_ITEM_DESCRIPTION
, MV.ITEM RELATED_ITEM
, B.PRIMARY_UOM_CODE UOM
, B.LIST_PRICE_PER_UNIT UNIT_PRICE
, DECODE(REL.RELATIONSHIP_TYPE_ID
, 2
, 'R'
, 4
, 'R'
, 8
, 'R'
, 'A') SUBSTITUTION_TYPE
, B.CUSTOMER_ORDER_ENABLED_FLAG
, B.BOM_ITEM_TYPE
FROM MTL_RELATED_ITEMS_VIEW REL
, MFG_LOOKUPS LOOK
, OE_ITEMS_ORD_MV MV
, MTL_SYSTEM_ITEMS_B B
, MTL_PARAMETERS MP
WHERE REL.RELATED_ITEM_ID = MV.INVENTORY_ITEM_ID
AND REL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = MV.ORGANIZATION_ID
AND LOOK.LOOKUP_TYPE='MTL_RELATIONSHIP_TYPES'
AND LOOK.ENABLED_FLAG ='Y'
AND SYSDATE BETWEEN NVL(LOOK.START_DATE_ACTIVE
, SYSDATE)
AND NVL(LOOK.END_DATE_ACTIVE
, SYSDATE)
AND REL.RELATIONSHIP_TYPE_ID = LOOK.LOOKUP_CODE
AND MV.ITEM_IDENTIFIER_TYPE = 'INT'
AND MV.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND MV.ORGANIZATION_ID = B.ORGANIZATION_ID
AND LOOK.LOOKUP_CODE <> '14'
AND (TRUNC(SYSDATE) BETWEEN NVL(REL.START_DATE
, TRUNC(SYSDATE))
AND NVL(REL.END_DATE
, TRUNC(SYSDATE)))