VIEW: APPS.AHL_POSITION_ALTERNATES_V

Source

View Text - Preformatted

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, (Select ITEM_GROUP_ID from AHL_ITEM_GROUPS_B where STATUS_CODE = 'COMPLETE') IG, (SELECT ITEM_GROUP_ID, RELATIONSHIP_ID FROM AHL_MC_RELATIONSHIPS UNION ALL SELECT DISTINCT R.ITEM_GROUP_ID, MCRR.RELATIONSHIP_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 MCH.CONFIG_STATUS_CODE = 'COMPLETE' AND MCRR.MC_HEADER_ID = MCH.MC_HEADER_ID AND R.PARENT_RELATIONSHIP_ID IS NULL) A WHERE MCH.MC_HEADER_ID = MCR.MC_HEADER_ID AND MCH.MC_ID = MCP.MC_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 = MCP.PATH_POSITION_ID) AND IG.ITEM_GROUP_ID = A.ITEM_GROUP_ID AND A.RELATIONSHIP_ID = MCR.RELATIONSHIP_ID AND IGASS.ITEM_GROUP_ID = IG.ITEM_GROUP_ID

View Text - HTML Formatted

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
, (SELECT ITEM_GROUP_ID
FROM AHL_ITEM_GROUPS_B
WHERE STATUS_CODE = 'COMPLETE') IG
, (SELECT ITEM_GROUP_ID
, RELATIONSHIP_ID
FROM AHL_MC_RELATIONSHIPS UNION ALL SELECT DISTINCT R.ITEM_GROUP_ID
, MCRR.RELATIONSHIP_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 MCH.CONFIG_STATUS_CODE = 'COMPLETE'
AND MCRR.MC_HEADER_ID = MCH.MC_HEADER_ID
AND R.PARENT_RELATIONSHIP_ID IS NULL) A
WHERE MCH.MC_HEADER_ID = MCR.MC_HEADER_ID
AND MCH.MC_ID = MCP.MC_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 = MCP.PATH_POSITION_ID)
AND IG.ITEM_GROUP_ID = A.ITEM_GROUP_ID
AND A.RELATIONSHIP_ID = MCR.RELATIONSHIP_ID
AND IGASS.ITEM_GROUP_ID = IG.ITEM_GROUP_ID