VIEW: APPS.AHL_MR_ITEMS_V

Source

View Text - Preformatted

SELECT DISTINCT mr.mr_header_id, mr.mr_effectivity_id, mtl.inventory_item_id, mtl.concatenated_segments FROM mtl_system_items_kfv mtl, ( SELECT DISTINCT ef.mr_header_id, ef.mr_effectivity_id, pa.inventory_item_id FROM ahl_position_alternates_v pa, ahl_mr_effectivities ef, ahl_mr_headers_b mr WHERE pa.relationship_id = ef.relationship_id and ef.mr_header_id = mr.mr_header_id and mr.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE') and ef.relationship_id is not null and ef.inventory_item_id is null UNION ALL SELECT DISTINCT ef.mr_header_id, ef.mr_effectivity_id, ef.inventory_item_id FROM ahl_mr_effectivities ef, ahl_mr_headers_b mr WHERE ef.mr_header_id = mr.mr_header_id and mr.application_usg_code = fnd_profile.value('AHL_APPLN_USAGE') and ef.inventory_item_id is not null ) mr WHERE mtl.inventory_item_id = mr.inventory_item_id

View Text - HTML Formatted

SELECT DISTINCT MR.MR_HEADER_ID
, MR.MR_EFFECTIVITY_ID
, MTL.INVENTORY_ITEM_ID
, MTL.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MTL
, ( SELECT DISTINCT EF.MR_HEADER_ID
, EF.MR_EFFECTIVITY_ID
, PA.INVENTORY_ITEM_ID
FROM AHL_POSITION_ALTERNATES_V PA
, AHL_MR_EFFECTIVITIES EF
, AHL_MR_HEADERS_B MR
WHERE PA.RELATIONSHIP_ID = EF.RELATIONSHIP_ID
AND EF.MR_HEADER_ID = MR.MR_HEADER_ID
AND MR.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND EF.RELATIONSHIP_ID IS NOT NULL
AND EF.INVENTORY_ITEM_ID IS NULL UNION ALL SELECT DISTINCT EF.MR_HEADER_ID
, EF.MR_EFFECTIVITY_ID
, EF.INVENTORY_ITEM_ID
FROM AHL_MR_EFFECTIVITIES EF
, AHL_MR_HEADERS_B MR
WHERE EF.MR_HEADER_ID = MR.MR_HEADER_ID
AND MR.APPLICATION_USG_CODE = FND_PROFILE.VALUE('AHL_APPLN_USAGE')
AND EF.INVENTORY_ITEM_ID IS NOT NULL ) MR
WHERE MTL.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID