View: AHL_ROUTE_EFFECTIVITIES_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	View based on the views AHL_ROUTE_EFFECTIVITIES , AHL.AHL_MC_HEADERS_V, AHL.AHL_ROUTES_V and AHL_MTL_ITEMS_NON_OU_V to get effectivities of a Route.
Implementation/DBA Data:	APPS.AHL_ROUTE_EFFECTIVITIES_V

View Text

SELECT DISTINCT RE.ROUTE_EFFECTIVITY_ID
, ICD.ITEM_COMPOSITION_ID
, RE.ROUTE_ID
, RM.ROUTE_NO
, RM.TITLE
, RM.REVISION_NUMBER
, RM.START_DATE_ACTIVE
, RM.END_DATE_ACTIVE
, RE.INVENTORY_ITEM_ID
, RE.INVENTORY_MASTER_ORG_ID
, MTL.CONCATENATED_SEGMENTS
, MTL.DESCRIPTION
, ( SELECT DISTINCT ORGANIZATION_CODE
FROM MTL_PARAMETERS MP
WHERE MP.MASTER_ORGANIZATION_ID = RE.INVENTORY_MASTER_ORG_ID
AND MP.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID)
, RE.MC_ID
, MC.NAME
, DECODE(RE.MC_HEADER_ID
, NULL
, NULL
, MC.VERSION_NUMBER)
, DECODE(RE.MC_HEADER_ID
, NULL
, NULL
, MC.REVISION)
, MC.DESCRIPTION
, RE.MC_HEADER_ID
, RE.LAST_UPDATE_DATE
, RE.LAST_UPDATED_BY
, RE.CREATION_DATE
, RE.CREATED_BY
, RE.LAST_UPDATE_LOGIN
, RE.OBJECT_VERSION_NUMBER
, RE.SECURITY_GROUP_ID
, RE.ATTRIBUTE_CATEGORY
, RE.ATTRIBUTE1
, RE.ATTRIBUTE2
, RE.ATTRIBUTE3
, RE.ATTRIBUTE4
, RE.ATTRIBUTE5
, RE.ATTRIBUTE6
, RE.ATTRIBUTE7
, RE.ATTRIBUTE8
, RE.ATTRIBUTE9
, RE.ATTRIBUTE10
, RE.ATTRIBUTE11
, RE.ATTRIBUTE12
, RE.ATTRIBUTE13
, RE.ATTRIBUTE14
, RE.ATTRIBUTE15
, NVL ( MTL.DESCRIPTION
, MC.DESCRIPTION )
FROM AHL_ROUTE_EFFECTIVITIES RE
, AHL_MC_HEADERS_V MC
, AHL_ROUTES_APP_V RM
, MTL_SYSTEM_ITEMS_KFV MTL
, MTL_PARAMETERS MP
, AHL_ITEM_COMP_V ICD
WHERE RE.ROUTE_ID = RM.ROUTE_ID
AND RE.INVENTORY_ITEM_ID=MTL.INVENTORY_ITEM_ID(+)
AND RE.INVENTORY_MASTER_ORG_ID=MTL.ORGANIZATION_ID(+)
AND RE.INVENTORY_MASTER_ORG_ID=MP.MASTER_ORGANIZATION_ID(+)
AND NVL(RE.MC_HEADER_ID
, RE.MC_ID)=MC.MC_HEADER_ID(+)
AND MC.CONFIG_STATUS_CODE(+)='COMPLETE'
AND RE.INVENTORY_ITEM_ID=ICD.INVENTORY_ITEM_ID(+)
AND RE.INVENTORY_MASTER_ORG_ID=ICD.INVENTORY_MASTER_ORG_ID(+)
AND ICD.APPROVAL_STATUS_CODE(+)='COMPLETE'

Columns