VIEW: APPS.OE_RELATED_ITEMS_V

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, T.description related_item_description, B.concatenated_segments 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, mtl_system_items_tl T, mtl_system_items_b_kfv B, mtl_parameters mp WHERE rel.related_item_id = B.inventory_item_id and rel.organization_id = mp.master_organization_id and mp.organization_id = B.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 B.bom_item_type in (1,4) and B.customer_order_enabled_flag = 'Y' and look.lookup_code <> '14' AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND T.LANGUAGE = userenv('LANG') 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
, T.DESCRIPTION RELATED_ITEM_DESCRIPTION
, B.CONCATENATED_SEGMENTS 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
, MTL_SYSTEM_ITEMS_TL T
, MTL_SYSTEM_ITEMS_B_KFV B
, MTL_PARAMETERS MP
WHERE REL.RELATED_ITEM_ID = B.INVENTORY_ITEM_ID
AND REL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND MP.ORGANIZATION_ID = B.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 B.BOM_ITEM_TYPE IN (1
, 4)
AND B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND LOOK.LOOKUP_CODE <> '14'
AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = USERENV('LANG')
AND (TRUNC(SYSDATE) BETWEEN NVL(REL.START_DATE
, TRUNC(SYSDATE))
AND NVL(REL.END_DATE
, TRUNC(SYSDATE)))