Product: | AHL - Complex Maintenance Repair and Overhaul |
---|---|
Description: | This view stores the unit/subunit position/header/instance information. |
Implementation/DBA Data: | APPS.AHL_UC_HEADER_PATHS_V |
SELECT U.UNIT_CONFIG_HEADER_ID
, U.NAME
, U.CSI_ITEM_INSTANCE_ID
, U.MASTER_CONFIG_ID
, CHDR.REVISION
, CHDR.MC_ID
, CHDR.VERSION_NUMBER
, CREL.RELATIONSHIP_ID
, CREL.POSITION_KEY
, PU.UNIT_CONFIG_HEADER_ID
, PU.CSI_ITEM_INSTANCE_ID
, PHDR.MC_HEADER_ID
, PHDR.MC_ID
, PHDR.VERSION_NUMBER
, PREL.RELATIONSHIP_ID
, PREL.POSITION_KEY
FROM AHL_UNIT_CONFIG_HEADERS U
, AHL_MC_HEADERS_B CHDR
, AHL_MC_RELATIONSHIPS CREL
, CSI_II_RELATIONSHIPS CSI_II
, AHL_UNIT_CONFIG_HEADERS PU
, AHL_MC_HEADERS_B PHDR
, AHL_MC_RELATIONSHIPS PREL
WHERE U.MASTER_CONFIG_ID = CHDR.MC_HEADER_ID
AND CHDR.MC_HEADER_ID = CREL.MC_HEADER_ID
AND CREL.PARENT_RELATIONSHIP_ID IS NULL
AND U.PARENT_UC_HEADER_ID = PU.UNIT_CONFIG_HEADER_ID (+)
AND U.CSI_ITEM_INSTANCE_ID = CSI_II.SUBJECT_ID (+)
AND CSI_II.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF'
AND TRUNC(NVL(CSI_II.ACTIVE_START_DATE(+)
, SYSDATE-1)) < TRUNC(SYSDATE)
AND TRUNC(NVL(CSI_II.ACTIVE_END_DATE(+)
, SYSDATE+1))> TRUNC(SYSDATE)
AND TO_NUMBER(CSI_II.POSITION_REFERENCE) = PREL.RELATIONSHIP_ID(+)
AND PREL.MC_HEADER_ID = PHDR.MC_HEADER_ID(+)