VIEW: APPS.AHL_AVAILABLE_INSTANCES_V

Source

View Text - Preformatted

SELECT csi.instance_id, msn.inventory_item_id, msn.current_organization_id organization_id, msn.serial_number, csi.unit_of_measure uom, csi.inv_subinventory_name, msi.status_id condition_id, mms.status_code condition_code, mms.description condition_description FROM csi_item_instances csi, mtl_secondary_inventories msi, mtl_serial_numbers msn, mtl_material_statuses mms WHERE trunc(sysdate) >= trunc(nvl(csi.active_start_date,sysdate)) AND trunc(sysdate) < trunc(nvl(csi.active_end_date,sysdate+1)) AND msn.current_status = 3 AND msn.reservation_id is null AND (msn.group_mark_id is null or msn.group_mark_id = -1) AND msi.status_id = mms.status_id AND csi.inv_subinventory_name = msi.secondary_inventory_name (+) AND csi.last_vld_organization_id = msi.organization_id (+) AND csi.inventory_item_id = msn.inventory_item_id AND csi.serial_number = msn.serial_number AND csi.last_vld_organization_id = msn.current_organization_id

View Text - HTML Formatted

SELECT CSI.INSTANCE_ID
, MSN.INVENTORY_ITEM_ID
, MSN.CURRENT_ORGANIZATION_ID ORGANIZATION_ID
, MSN.SERIAL_NUMBER
, CSI.UNIT_OF_MEASURE UOM
, CSI.INV_SUBINVENTORY_NAME
, MSI.STATUS_ID CONDITION_ID
, MMS.STATUS_CODE CONDITION_CODE
, MMS.DESCRIPTION CONDITION_DESCRIPTION
FROM CSI_ITEM_INSTANCES CSI
, MTL_SECONDARY_INVENTORIES MSI
, MTL_SERIAL_NUMBERS MSN
, MTL_MATERIAL_STATUSES MMS
WHERE TRUNC(SYSDATE) >= TRUNC(NVL(CSI.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(SYSDATE) < TRUNC(NVL(CSI.ACTIVE_END_DATE
, SYSDATE+1))
AND MSN.CURRENT_STATUS = 3
AND MSN.RESERVATION_ID IS NULL
AND (MSN.GROUP_MARK_ID IS NULL OR MSN.GROUP_MARK_ID = -1)
AND MSI.STATUS_ID = MMS.STATUS_ID
AND CSI.INV_SUBINVENTORY_NAME = MSI.SECONDARY_INVENTORY_NAME (+)
AND CSI.LAST_VLD_ORGANIZATION_ID = MSI.ORGANIZATION_ID (+)
AND CSI.INVENTORY_ITEM_ID = MSN.INVENTORY_ITEM_ID
AND CSI.SERIAL_NUMBER = MSN.SERIAL_NUMBER
AND CSI.LAST_VLD_ORGANIZATION_ID = MSN.CURRENT_ORGANIZATION_ID