VIEW: APPS.MTL_AVAILABLE_INVENTORY_V

Source

View Text - Preformatted

SELECT pklt.locator_id ,lot.lot_number ,pklt.subinventory_code ,inv_project.get_locator(pklt.locator_id,pklt.organization_id) ,lot.creation_date ,lot.expiration_date ,pklt.grade_code ,pklt.transaction_temp_id ,pklt.reason_id ,rsn.reason_name ,decode(pklt.transaction_temp_id, 0, 0, sum(transaction_qty)) trans_qty ,decode(pklt.transaction_temp_id, 0, 0, sum(secondary_transaction_qty)) trans_qty2 ,pklt.transaction_uom ,pklt.secondary_uom ,pklt.revision ,pklt.lpn_id ,pklt.order_by ,pklt.wms_rule_id FROM mtl_available_inventory_temp pklt ,mtl_lot_numbers lot ,mtl_item_locations_kfv loct ,mtl_transaction_reasons rsn WHERE pklt.lot_number = lot.lot_number (+) AND pklt.locator_id = loct.inventory_location_id(+) AND pklt.reason_id = rsn.reason_id(+) AND pklt.inventory_item_id = lot.inventory_item_id(+) AND pklt.organization_id = lot.organization_id(+) GROUP BY pklt.locator_id ,lot.lot_number ,pklt.subinventory_code ,inv_project.get_locator(pklt.locator_id,pklt.organization_id) ,lot.creation_date,lot.expiration_date ,pklt.grade_code ,pklt.transaction_temp_id ,pklt.reason_id ,rsn.reason_name ,pklt.transaction_uom ,pklt.secondary_uom ,pklt.revision ,pklt.lpn_id ,pklt.order_by ,pklt.wms_rule_id ORDER BY order_by

View Text - HTML Formatted

SELECT PKLT.LOCATOR_ID
, LOT.LOT_NUMBER
, PKLT.SUBINVENTORY_CODE
, INV_PROJECT.GET_LOCATOR(PKLT.LOCATOR_ID
, PKLT.ORGANIZATION_ID)
, LOT.CREATION_DATE
, LOT.EXPIRATION_DATE
, PKLT.GRADE_CODE
, PKLT.TRANSACTION_TEMP_ID
, PKLT.REASON_ID
, RSN.REASON_NAME
, DECODE(PKLT.TRANSACTION_TEMP_ID
, 0
, 0
, SUM(TRANSACTION_QTY)) TRANS_QTY
, DECODE(PKLT.TRANSACTION_TEMP_ID
, 0
, 0
, SUM(SECONDARY_TRANSACTION_QTY)) TRANS_QTY2
, PKLT.TRANSACTION_UOM
, PKLT.SECONDARY_UOM
, PKLT.REVISION
, PKLT.LPN_ID
, PKLT.ORDER_BY
, PKLT.WMS_RULE_ID
FROM MTL_AVAILABLE_INVENTORY_TEMP PKLT
, MTL_LOT_NUMBERS LOT
, MTL_ITEM_LOCATIONS_KFV LOCT
, MTL_TRANSACTION_REASONS RSN
WHERE PKLT.LOT_NUMBER = LOT.LOT_NUMBER (+)
AND PKLT.LOCATOR_ID = LOCT.INVENTORY_LOCATION_ID(+)
AND PKLT.REASON_ID = RSN.REASON_ID(+)
AND PKLT.INVENTORY_ITEM_ID = LOT.INVENTORY_ITEM_ID(+)
AND PKLT.ORGANIZATION_ID = LOT.ORGANIZATION_ID(+) GROUP BY PKLT.LOCATOR_ID
, LOT.LOT_NUMBER
, PKLT.SUBINVENTORY_CODE
, INV_PROJECT.GET_LOCATOR(PKLT.LOCATOR_ID
, PKLT.ORGANIZATION_ID)
, LOT.CREATION_DATE
, LOT.EXPIRATION_DATE
, PKLT.GRADE_CODE
, PKLT.TRANSACTION_TEMP_ID
, PKLT.REASON_ID
, RSN.REASON_NAME
, PKLT.TRANSACTION_UOM
, PKLT.SECONDARY_UOM
, PKLT.REVISION
, PKLT.LPN_ID
, PKLT.ORDER_BY
, PKLT.WMS_RULE_ID ORDER BY ORDER_BY