View: AHL_UNIT_CONFIG_HEADERS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view includes all the information related to unit configuration headers, master configuration headers, and the hierarchy of uc and its parent UC, root UC and parent instance.
Implementation/DBA Data:	APPS.AHL_UNIT_CONFIG_HEADERS_V

View Text

SELECT U.UNIT_CONFIG_HEADER_ID
, U.OBJECT_VERSION_NUMBER
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.LAST_UPDATE_LOGIN
, U.NAME
, U.MASTER_CONFIG_ID
, M.NAME
, M.REVISION
, M.CONFIG_STATUS_CODE
, MCSC.MEANING
, M.DESCRIPTION
, R.POSITION_REF_CODE
, MRSC.MEANING
, R.ACTIVE_START_DATE
, R.ACTIVE_END_DATE
, U.CSI_ITEM_INSTANCE_ID
, C.OBJECT_VERSION_NUMBER
, I.CONCATENATED_SEGMENTS
, C.INSTANCE_NUMBER
, C.SERIAL_NUMBER
, C.LOT_NUMBER
, C.ACTIVE_START_DATE
, C.ACTIVE_END_DATE
, U.ACTIVE_START_DATE
, U.ACTIVE_END_DATE
, U.UNIT_CONFIG_STATUS_CODE
, UCSC.MEANING
, U.ACTIVE_UC_STATUS_CODE
, UASC.MEANING
, U.PARENT_UC_HEADER_ID
, PU.NAME
, CR.OBJECT_ID
, (SELECT UNIT_CONFIG_HEADER_ID
FROM AHL_UNIT_CONFIG_HEADERS
WHERE PARENT_UC_HEADER_ID IS NULL START WITH UNIT_CONFIG_HEADER_ID = U.UNIT_CONFIG_HEADER_ID CONNECT BY UNIT_CONFIG_HEADER_ID = PRIOR PARENT_UC_HEADER_ID)
, U.ATTRIBUTE_CATEGORY
, U.ATTRIBUTE1
, U.ATTRIBUTE2
, U.ATTRIBUTE3
, U.ATTRIBUTE4
, U.ATTRIBUTE5
, U.ATTRIBUTE6
, U.ATTRIBUTE7
, U.ATTRIBUTE8
, U.ATTRIBUTE9
, U.ATTRIBUTE10
, U.ATTRIBUTE11
, U.ATTRIBUTE12
, U.ATTRIBUTE13
, U.ATTRIBUTE14
, U.ATTRIBUTE15
FROM AHL_UNIT_CONFIG_HEADERS U
, CSI_ITEM_INSTANCES C
, MTL_SYSTEM_ITEMS_KFV I
, AHL_MC_HEADERS_VL M
, AHL_MC_RELATIONSHIPS R
, AHL_UNIT_CONFIG_HEADERS PU
, CSI_II_RELATIONSHIPS CR
, FND_LOOKUP_VALUES_VL UCSC
, FND_LOOKUP_VALUES_VL UASC
, FND_LOOKUP_VALUES_VL MCSC
, FND_LOOKUP_VALUES_VL MRSC
WHERE U.CSI_ITEM_INSTANCE_ID = C.INSTANCE_ID
AND U.MASTER_CONFIG_ID = M.MC_HEADER_ID
AND C.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID
AND C.INV_MASTER_ORGANIZATION_ID = I.ORGANIZATION_ID
AND M.MC_HEADER_ID = R.MC_HEADER_ID
AND R.PARENT_RELATIONSHIP_ID IS NULL
AND U.PARENT_UC_HEADER_ID = PU.UNIT_CONFIG_HEADER_ID (+)
AND U.UNIT_CONFIG_STATUS_CODE = UCSC.LOOKUP_CODE (+)
AND 'AHL_CONFIG_STATUS' = UCSC.LOOKUP_TYPE (+)
AND U.ACTIVE_UC_STATUS_CODE = UASC.LOOKUP_CODE (+)
AND 'AHL_CONFIG_STATUS' = UASC.LOOKUP_TYPE (+)
AND M.CONFIG_STATUS_CODE = MCSC.LOOKUP_CODE
AND 'AHL_CONFIG_STATUS' = MCSC.LOOKUP_TYPE
AND R.POSITION_REF_CODE = MRSC.LOOKUP_CODE
AND 'AHL_POSITION_REFERENCE' = MRSC.LOOKUP_TYPE
AND U.CSI_ITEM_INSTANCE_ID = CR.SUBJECT_ID (+)
AND CR.RELATIONSHIP_TYPE_CODE (+) = 'COMPONENT-OF'
AND TRUNC(NVL(CR.ACTIVE_END_DATE (+)
, SYSDATE+1)) > TRUNC(SYSDATE)
AND EXISTS (SELECT 1
FROM ORG_ORGANIZATION_DEFINITIONS O
WHERE I.ORGANIZATION_ID = O.ORGANIZATION_ID
AND NVL(O.OPERATING_UNIT
, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99))

Columns