View: AHL_POSITION_ALTERNATES_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	View based on the tables AHL_RELATIONSHIPS_B and AHL_ITEM_ASSOCIATIONS_B
Implementation/DBA Data:	APPS.AHL_POSITION_ALTERNATES_V

View Text

SELECT MCP.PATH_POSITION_ID RELATIONSHIP_ID
, IGASS.INVENTORY_ITEM_ID
, IGASS.INVENTORY_ORG_ID
, MCR.ACTIVE_START_DATE
, MCR.ACTIVE_END_DATE
FROM AHL_MC_RELATIONSHIPS MCR
, AHL_MC_HEADERS_B MCH
, AHL_MC_PATH_POSITION_NODES MCP
, AHL_ITEM_ASSOCIATIONS_VL IGASS
, AHL_MC_PATH_POSITIONS MPP
, AHL_ITEM_GROUPS_B IG
WHERE MCH.MC_HEADER_ID = MCR.MC_HEADER_ID
AND MCH.MC_ID = MCP.MC_ID
AND MPP.PATH_POSITION_ID = MCP.PATH_POSITION_ID
AND MCH.CONFIG_STATUS_CODE = 'COMPLETE'
AND MCH.VERSION_NUMBER = NVL(MCP.VERSION_NUMBER
, MCH.VERSION_NUMBER)
AND MCR.POSITION_KEY = MCP.POSITION_KEY
AND MCP.SEQUENCE = (SELECT MAX(SEQUENCE)
FROM AHL_MC_PATH_POSITION_NODES
WHERE PATH_POSITION_ID = MPP.PATH_POSITION_ID)
AND IG.ITEM_GROUP_ID = IGASS.ITEM_GROUP_ID
AND IG.STATUS_CODE = 'COMPLETE'
AND IG.ITEM_GROUP_ID IN (SELECT MCR2.ITEM_GROUP_ID
FROM AHL_MC_RELATIONSHIPS MCR2
WHERE MCR2.RELATIONSHIP_ID = MCR.RELATIONSHIP_ID UNION ALL SELECT DISTINCT R.ITEM_GROUP_ID
FROM AHL_MC_RELATIONSHIPS R
, AHL_MC_CONFIG_RELATIONS MCRR
, AHL_MC_HEADERS_B MCH
WHERE MCRR.MC_HEADER_ID=R.MC_HEADER_ID
AND MCRR.MC_HEADER_ID = MCH.MC_HEADER_ID
AND MCRR.RELATIONSHIP_ID = MCR.RELATIONSHIP_ID
AND MCH.CONFIG_STATUS_CODE = 'COMPLETE'
AND R.PARENT_RELATIONSHIP_ID IS NULL)

Columns