View: AHL_UNIT_DETAILS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve Unit Configuration node details ( item and position information ).
Implementation/DBA Data:	APPS.AHL_UNIT_DETAILS_V

View Text

SELECT A.RELATIONSHIP_ID
, CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION
, F.MEANING POSITION_REF_MEANING
, F1.MEANING POSITION_NECESSITY_MEANING
, CSI.QUANTITY
, CSI.UNIT_OF_MEASURE UOM_CODE
, CII.RELATIONSHIP_ID CSI_II_RELATIONSHIP_ID
, CII.OBJECT_VERSION_NUMBER CSI_II_OBJECT_VERSION
, CII.OBJECT_ID PARENT_CSI_INSTANCE_ID
, AMH.DESCRIPTION
, A.PARENT_RELATIONSHIP_ID
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION REVISION
, CSI.LOT_NUMBER
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, TO_DATE(CIEA.ATTRIBUTE_VALUE
, 'DD/MM/YYYY') MFG_DATE
FROM AHL_MC_RELATIONSHIPS A
, AHL_MC_HEADERS_VL AMH
, CSI_II_RELATIONSHIPS CII
, CSI_ITEM_INSTANCES CSI
, FND_LOOKUPS F
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
, CSI_INST_EXTEND_ATTRIB_V CIEA
WHERE A.RELATIONSHIP_ID = TO_NUMBER(CII.POSITION_REFERENCE)
AND A.MC_HEADER_ID = AMH.MC_HEADER_ID
AND CII.SUBJECT_ID = CSI.INSTANCE_ID
AND CII.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND F.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND F.LOOKUP_CODE = A.POSITION_REF_CODE
AND F1.LOOKUP_TYPE = 'AHL_POSITION_NECESSITY'
AND F1.LOOKUP_CODE = NVL(A.POSITION_NECESSITY_CODE
, 'MANDATORY')
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND CSI.INSTANCE_ID = CIEA.INSTANCE_ID(+)
AND CIEA.ATTRIBUTE_CODE(+) = 'AHL_MFG_DATE'
AND CIEA.ATTRIBUTE_LEVEL(+) = 'GLOBAL'
AND TRUNC(SYSDATE) >= TRUNC(NVL(CII.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(CII.ACTIVE_END_DATE
, SYSDATE+1)) UNION SELECT A.RELATIONSHIP_ID
, CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION
, F.MEANING POSITION_REF_MEANING
, F1.MEANING POSITION_NECESSITY_MEANING
, CSI.QUANTITY
, CSI.UNIT_OF_MEASURE UOM_CODE
, TO_NUMBER(NULL) CSI_II_RELATIONSHIP_ID
, TO_NUMBER(NULL) CSI_II_OBJECT_VERSION
, TO_NUMBER(NULL) PARENT_CSI_INSTANCE_ID
, AMH.DESCRIPTION
, A.PARENT_RELATIONSHIP_ID
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION REVISION
, CSI.LOT_NUMBER
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, TO_DATE(NULL
, 'DD/MM/YYYY') MFG_DATE
FROM AHL_MC_RELATIONSHIPS A
, AHL_MC_HEADERS_VL AMH
, AHL_UNIT_CONFIG_HEADERS U
, CSI_ITEM_INSTANCES CSI
, FND_LOOKUPS F
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
WHERE AMH.MC_HEADER_ID = U.MASTER_CONFIG_ID
AND A.MC_HEADER_ID = AMH.MC_HEADER_ID
AND A.PARENT_RELATIONSHIP_ID IS NULL
AND U.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND F.LOOKUP_TYPE = 'AHL_POSITION_REFERENCE'
AND F.LOOKUP_CODE = A.POSITION_REF_CODE
AND F1.LOOKUP_TYPE = 'AHL_POSITION_NECESSITY'
AND F1.LOOKUP_CODE = NVL(A.POSITION_NECESSITY_CODE
, 'MANDATORY')
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND TRUNC(SYSDATE) < TRUNC(NVL(U.ACTIVE_END_DATE
, SYSDATE+1))

Columns