View: AHL_UNIT_HEADER_DETAILS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve valid Unit Configuration header details.
Implementation/DBA Data:	APPS.AHL_UNIT_HEADER_DETAILS_V

View Text

SELECT U.UNIT_CONFIG_HEADER_ID
, U.OBJECT_VERSION_NUMBER
, U.NAME
, U.MASTER_CONFIG_ID
, U.CSI_ITEM_INSTANCE_ID
, U.UNIT_CONFIG_STATUS_CODE
, P.PARTY_ID OWNER_ID
, P.PARTY_NUMBER OWNER_NUMBER
, P.PARTY_NAME OWNER_NAME
, P.PARTY_TYPE
, P.OBJECT_VERSION_NUMBER CSI_PARTY_OBJECT_VERSION_NUM
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION_NUMBER
, U.ACTIVE_START_DATE
, U.ACTIVE_END_DATE
, F.MEANING STATUS_MEANING
, CSI.LOCATION_ID OWNER_SITE_ID
, DECODE(CSI.LOCATION_TYPE_CODE
, 'WIP'
, WIP.WIP_ENTITY_NAME
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONCODE(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE) ) OWNER_SITE_NUMBER
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONDESC(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE
, CSI.INV_ORGANIZATION_ID
, CSI.INV_SUBINVENTORY_NAME
, CSI. INV_LOCATOR_ID
, CSI.WIP_JOB_ID) LOCATION_DESCRIPTION
, CSI.SERIAL_NUMBER
, AMR.POSITION_REF_CODE POSITION_REF_CODE
, F1.MEANING POSITION_REF_MEANING
, A.NAME MASTER_CONFIG_NAME
, A.REVISION MASTER_CONFIG_REVISION
, A.DESCRIPTION MASTER_CONFIG_DESCRIPTION
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, KFV.DESCRIPTION ITEM_DESCRIPTION
FROM AHL_UNIT_CONFIG_HEADERS U
, CSI_ITEM_INSTANCES CSI
, CSI_INST_PARTY_DETAILS_V P
, FND_LOOKUPS F
, AHL_MC_HEADERS_VL A
, AHL_MC_RELATIONSHIPS AMR
, FND_LOOKUPS F1
, MTL_SYSTEM_ITEMS_KFV KFV
, WIP_ENTITIES WIP
WHERE U.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID
AND CSI.INSTANCE_ID = P.INSTANCE_ID
AND P.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.LAST_VLD_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND U.UNIT_CONFIG_STATUS_CODE = F.LOOKUP_CODE(+)
AND F.LOOKUP_TYPE(+) = 'AHL_CONFIG_STATUS'
AND U.MASTER_CONFIG_ID = A.MC_HEADER_ID
AND A.MC_HEADER_ID = AMR.MC_HEADER_ID
AND AMR.PARENT_RELATIONSHIP_ID IS NULL
AND AMR.POSITION_REF_CODE = F1.LOOKUP_CODE(+)
AND F1.LOOKUP_TYPE(+) = 'AHL_POSITION_REFERENCE'
AND CSI.WIP_JOB_ID = WIP.WIP_ENTITY_ID(+)
AND TRUNC(SYSDATE) < TRUNC(NVL(U.ACTIVE_END_DATE
, SYSDATE+1))

Columns