[Home] [Help]
View: ICX_MTL_ONHAND_LOT_KFV
Product: | ICX - Oracle iProcurement |
Description: | On-Hand Item Quantity View in a Lot |
Implementation/DBA Data: |
APPS.ICX_MTL_ONHAND_LOT_KFV
|
View Text
SELECT A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS ITEM
, A.REVISION
, SUM(A.TRANSACTION_QUANTITY) TOTAL_QOH
, A.SUBINVENTORY_CODE
, NVL(A.LOCATOR_ID
, -1) LOCATOR_ID
, F.CONCATENATED_SEGMENTS LOCATION
, A.LOT_NUMBER LOT
, B.DESCRIPTION ITEM_DESCRIPTION
, B.PRIMARY_UOM_CODE
, C.ORGANIZATION_CODE
, E.NAME ORGANIZATION_NAME
, D.EXPIRATION_DATE
, B.ATTRIBUTE14 ITEM_URL
FROM MTL_ONHAND_QUANTITIES A
, MTL_SYSTEM_ITEMS_KFV B
, MTL_PARAMETERS C
, MTL_LOT_NUMBERS D
, MTL_ITEM_LOCATIONS_KFV F
, HR_ORGANIZATION_UNITS E
WHERE A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND A.LOT_NUMBER = D.LOT_NUMBER
AND A.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
AND A.ORGANIZATION_ID = D.ORGANIZATION_ID
AND A.ORGANIZATION_ID = E.ORGANIZATION_ID
AND A.LOCATOR_ID IS NOT NULL
AND A.LOCATOR_ID = F.INVENTORY_LOCATION_ID
AND A.ORGANIZATION_ID = F.ORGANIZATION_ID GROUP BY A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS
, A.REVISION
, A.SUBINVENTORY_CODE
, A.LOCATOR_ID
, F.CONCATENATED_SEGMENTS
, A.LOT_NUMBER
, B.DESCRIPTION
, B.PRIMARY_UOM_CODE
, C.ORGANIZATION_CODE
, E.NAME
, D.EXPIRATION_DATE
, B.ATTRIBUTE14 UNION SELECT A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS ITEM
, A.REVISION
, SUM(A.TRANSACTION_QUANTITY) TOTAL_QOH
, A.SUBINVENTORY_CODE
, NVL(A.LOCATOR_ID
, -1) LOCATOR_ID
, NULL LOCATION
, A.LOT_NUMBER LOT
, B.DESCRIPTION ITEM_DESCRIPTION
, B.PRIMARY_UOM_CODE
, C.ORGANIZATION_CODE
, E.NAME ORGANIZATION_NAME
, D.EXPIRATION_DATE
, B.ATTRIBUTE14 ITEM_URL
FROM MTL_ONHAND_QUANTITIES A
, MTL_SYSTEM_ITEMS_KFV B
, MTL_PARAMETERS C
, MTL_LOT_NUMBERS D
, HR_ORGANIZATION_UNITS E
WHERE A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND A.LOT_NUMBER = D.LOT_NUMBER
AND A.INVENTORY_ITEM_ID = D.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID = B.ORGANIZATION_ID
AND A.ORGANIZATION_ID = C.ORGANIZATION_ID
AND A.ORGANIZATION_ID = D.ORGANIZATION_ID
AND A.ORGANIZATION_ID = E.ORGANIZATION_ID
AND A.LOCATOR_ID IS NULL GROUP BY A.ORGANIZATION_ID
, A.INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS
, A.REVISION
, A.SUBINVENTORY_CODE
, A.LOCATOR_ID
, A.LOT_NUMBER
, B.DESCRIPTION
, B.PRIMARY_UOM_CODE
, C.ORGANIZATION_CODE
, E.NAME
, D.EXPIRATION_DATE
, B.ATTRIBUTE14
Columns
Name |
ORGANIZATION_ID |
INVENTORY_ITEM_ID |
ITEM |
REVISION |
TOTAL_QOH |
SUBINVENTORY_CODE |
LOCATOR_ID |
LOCATION |
LOT |
ITEM_DESCRIPTION |
PRIMARY_UOM_CODE |
ORGANIZATION_CODE |
ORGANIZATION_NAME |
EXPIRATION_DATE |
ITEM_URL |