VIEW: APPS.MTL_ATT_QTY_V

Source

View Text - Preformatted

SELECT 1 type, moq.INVENTORY_ITEM_ID, moq.ORGANIZATION_ID, moq.REVISION, moq.SUBINVENTORY_CODE, moq.LOCATOR_ID, moq.TRANSACTION_QUANTITY, moq.LOT_NUMBER from MTL_ONHAND_QUANTITIES moq, MTL_LOT_NUMBERS mln WHERE mln.LOT_NUMBER (+) = moq.LOT_NUMBER and mln.INVENTORY_ITEM_ID (+) = moq.INVENTORY_ITEM_ID and mln.ORGANIZATION_ID (+) = moq.ORGANIZATION_ID union all select 2, mmtt.INVENTORY_ITEM_ID, mmtt.ORGANIZATION_ID, mmtt.REVISION, mmtt.SUBINVENTORY_CODE, mmtt.LOCATOR_ID, nvl(mtlt.PRIMARY_QUANTITY,mmtt.PRIMARY_QUANTITY), nvl(mtlt.LOT_NUMBER,mmtt.LOT_NUMBER) from MTL_MATERIAL_TRANSACTIONS_TEMP mmtt, MTL_TRANSACTION_LOTS_TEMP mtlt where mtlt.TRANSACTION_TEMP_ID (+) = mmtt.TRANSACTION_TEMP_ID and nvl(mtlt.LOT_EXPIRATION_DATE, nvl(mmtt.LOT_EXPIRATION_DATE, sysdate+1)) > sysdate and mmtt.POSTING_FLAG = 'Y' union all select 7, md.INVENTORY_ITEM_ID, md.ORGANIZATION_ID, md.REVISION, md.SUBINVENTORY, md.LOCATOR_ID, -(nvl(md.PRIMARY_UOM_QUANTITY,0)-nvl(COMPLETED_QUANTITY,0)), md.LOT_NUMBER from MTL_DEMAND md, MTL_LOT_NUMBERS mln where mln.LOT_NUMBER (+) = md.LOT_NUMBER and mln.INVENTORY_ITEM_ID (+) = md.INVENTORY_ITEM_ID and mln.ORGANIZATION_ID (+) = md.ORGANIZATION_ID and nvl(mln.EXPIRATION_DATE,sysdate+1) > sysdate and md.RESERVATION_TYPE = 2 and md.PARENT_DEMAND_ID is not null and md.ROW_STATUS_FLAG = 1

View Text - HTML Formatted

SELECT 1 TYPE
, MOQ.INVENTORY_ITEM_ID
, MOQ.ORGANIZATION_ID
, MOQ.REVISION
, MOQ.SUBINVENTORY_CODE
, MOQ.LOCATOR_ID
, MOQ.TRANSACTION_QUANTITY
, MOQ.LOT_NUMBER
FROM MTL_ONHAND_QUANTITIES MOQ
, MTL_LOT_NUMBERS MLN
WHERE MLN.LOT_NUMBER (+) = MOQ.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID (+) = MOQ.INVENTORY_ITEM_ID
AND MLN.ORGANIZATION_ID (+) = MOQ.ORGANIZATION_ID UNION ALL SELECT 2
, MMTT.INVENTORY_ITEM_ID
, MMTT.ORGANIZATION_ID
, MMTT.REVISION
, MMTT.SUBINVENTORY_CODE
, MMTT.LOCATOR_ID
, NVL(MTLT.PRIMARY_QUANTITY
, MMTT.PRIMARY_QUANTITY)
, NVL(MTLT.LOT_NUMBER
, MMTT.LOT_NUMBER)
FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
, MTL_TRANSACTION_LOTS_TEMP MTLT
WHERE MTLT.TRANSACTION_TEMP_ID (+) = MMTT.TRANSACTION_TEMP_ID
AND NVL(MTLT.LOT_EXPIRATION_DATE
, NVL(MMTT.LOT_EXPIRATION_DATE
, SYSDATE+1)) > SYSDATE
AND MMTT.POSTING_FLAG = 'Y' UNION ALL SELECT 7
, MD.INVENTORY_ITEM_ID
, MD.ORGANIZATION_ID
, MD.REVISION
, MD.SUBINVENTORY
, MD.LOCATOR_ID
, -(NVL(MD.PRIMARY_UOM_QUANTITY
, 0)-NVL(COMPLETED_QUANTITY
, 0))
, MD.LOT_NUMBER
FROM MTL_DEMAND MD
, MTL_LOT_NUMBERS MLN
WHERE MLN.LOT_NUMBER (+) = MD.LOT_NUMBER
AND MLN.INVENTORY_ITEM_ID (+) = MD.INVENTORY_ITEM_ID
AND MLN.ORGANIZATION_ID (+) = MD.ORGANIZATION_ID
AND NVL(MLN.EXPIRATION_DATE
, SYSDATE+1) > SYSDATE
AND MD.RESERVATION_TYPE = 2
AND MD.PARENT_DEMAND_ID IS NOT NULL
AND MD.ROW_STATUS_FLAG = 1