View: AHL_UNIT_ALTERNATES_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve alternate item instances available in Install Base for a position (node) as defined by Master Configuration
Implementation/DBA Data:	APPS.AHL_UNIT_ALTERNATES_V

View Text

SELECT CSI.INSTANCE_ID CSI_ITEM_INSTANCE_ID
, CSI.INSTANCE_NUMBER CSI_INSTANCE_NUMBER
, CSI.OBJECT_VERSION_NUMBER CSI_OBJECT_VERSION_NUMBER
, CSI.INVENTORY_ITEM_ID
, CSI.INV_MASTER_ORGANIZATION_ID INVENTORY_ORG_ID
, CSI.UNIT_OF_MEASURE UOM_CODE
, CSI.QUANTITY
, P.PARTY_ID OWNER_ID
, ((SELECT HZ.PARTY_NUMBER
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'HZ_PARTIES') UNION ALL (SELECT PO.SEGMENT1
FROM PO_VENDORS PO
WHERE PO.VENDOR_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'PO_VENDORS')) OWNER_NUMBER
, ((SELECT HZ.PARTY_NAME
FROM HZ_PARTIES HZ
WHERE HZ.PARTY_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'HZ_PARTIES') UNION ALL (SELECT PO.VENDOR_NAME
FROM PO_VENDORS PO
WHERE PO.VENDOR_ID = P.PARTY_ID
AND P.PARTY_SOURCE_TABLE = 'PO_VENDORS')) OWNER_NAME
, (SELECT DECODE(P.PARTY_SOURCE_TABLE
, 'HZ_PARTIES'
, 'PARTY'
, 'PO_VENDORS'
, 'VENDOR')
FROM DUAL) PARTY_TYPE
, P.OBJECT_VERSION_NUMBER CSI_PARTY_OBJECT_VERSION_NUM
, A.RELATIONSHIP_ID
, ASSOC.PRIORITY
, CSI.SERIAL_NUMBER
, CSI.INVENTORY_REVISION
, CSI.INSTALL_DATE
, CSI.LOCATION_ID CSI_LOCATION_ID
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONCODE(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE) OWNER_SITE_NUMBER
, CSI.LOCATION_TYPE_CODE CSI_LOCATION_TYPE_CODE
, AHL_UTIL_UC_PKG.GETCSI_LOCATIONDESC(CSI.LOCATION_ID
, CSI.LOCATION_TYPE_CODE
, CSI.INV_MASTER_ORGANIZATION_ID
, CSI.INV_SUBINVENTORY_NAME
, CSI.INV_LOCATOR_ID
, CSI.WIP_JOB_ID) LOCATION_DESCRIPTION
, (SELECT MEANING
FROM CSI_LOOKUPS F
WHERE CSI.INSTANCE_USAGE_CODE = F.LOOKUP_CODE
AND F.LOOKUP_TYPE = 'CSI_INSTANCE_USAGE_CODE') STATUS
, KFV.CONCATENATED_SEGMENTS ITEM_NUMBER
, KFV.DESCRIPTION ITEM_DESCRIPTION
, (SELECT TO_DATE(CIEA.ATTRIBUTE_VALUE
, 'DD/MM/YYYY')
FROM CSI_INST_EXTEND_ATTRIB_V CIEA
WHERE CIEA.INSTANCE_ID = CSI.INSTANCE_ID
AND CIEA.ATTRIBUTE_CODE = 'AHL_MFG_DATE'
AND CIEA.ATTRIBUTE_LEVEL = 'GLOBAL') MFG_DATE
, (SELECT MSTAT.DESCRIPTION
FROM MTL_MATERIAL_STATUSES MSTAT
WHERE MSTAT.STATUS_ID = CSI.INSTANCE_CONDITION_ID) CONDITION
, (SELECT ORG.ORGANIZATION_CODE
FROM ORG_ORGANIZATION_DEFINITIONS ORG
WHERE ORGANIZATION_ID = CSI.INV_MASTER_ORGANIZATION_ID) ORGANIZATION_CODE
FROM CSI_ITEM_INSTANCES CSI
, AHL_ITEM_ASSOCIATIONS_B ASSOC
, AHL_MC_RELATIONSHIPS A
, CSI_I_PARTIES P
, MTL_SYSTEM_ITEMS_KFV KFV
WHERE CSI.INVENTORY_ITEM_ID = KFV.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = KFV.ORGANIZATION_ID
AND CSI.INVENTORY_ITEM_ID = ASSOC.INVENTORY_ITEM_ID
AND CSI.INV_MASTER_ORGANIZATION_ID = ASSOC.INVENTORY_ORG_ID
AND A.ITEM_GROUP_ID = ASSOC.ITEM_GROUP_ID
AND ASSOC.ITEM_ASSOCIATION_ID = AHL_UTIL_UC_PKG.VALIDATE_ALTERNATE_ITEM(A.RELATIONSHIP_ID
, CSI.INVENTORY_ITEM_ID
, CSI.INV_MASTER_ORGANIZATION_ID
, CSI.QUANTITY
, CSI.INVENTORY_REVISION
, CSI.UNIT_OF_MEASURE)
AND ((CSI.INSTANCE_USAGE_CODE = 'OUT_OF_SERVICE' OR CSI.INSTANCE_USAGE_CODE IS NULL OR CSI.INSTANCE_USAGE_CODE='OUT_OF_ENTERPRISE')
AND CSI.LOCATION_TYPE_CODE NOT IN ('WIP'
, 'PO'
, 'IN-TRANSIT'
, 'PROJECT'
, 'INVENTORY'))
AND TRUNC(SYSDATE) < TRUNC(NVL(CSI.ACTIVE_END_DATE
, SYSDATE+1))
AND P.INSTANCE_ID = CSI.INSTANCE_ID
AND P.RELATIONSHIP_TYPE_CODE = 'OWNER'
AND (P.PARTY_SOURCE_TABLE ='HZ_PARTIES' OR P.PARTY_SOURCE_TABLE ='PO_VENDORS' )
AND NOT EXISTS (SELECT SUBJECT_ID
FROM CSI_II_RELATIONSHIPS
WHERE (SUBJECT_ID = CSI.INSTANCE_ID OR OBJECT_ID = CSI.INSTANCE_ID)
AND RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND TRUNC(SYSDATE) >= TRUNC(NVL(ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(ACTIVE_END_DATE
, SYSDATE+1)) )
AND CSI.INV_MASTER_ORGANIZATION_ID IN (SELECT ORGANIZATION_ID
FROM ORG_ORGANIZATION_DEFINITIONS
WHERE NVL(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