query for on hand qty,reserve qty

onhand qty 包括了预留的数量，如果要计算可用量，需要使用onhand qty-reserve qty.

select A.organization_code,A.segment1,A.description,A.transaction_quantity,nvl(b.RESERVATION_QUANTITY,0) resqty,A.subinventory_code,A.locator
  from (select ood.legal_entity,
               ood.operating_unit,
               ood.organization_id,
               ood.organization_code,
               ood.organization_name,
               msib.inventory_item_id,
               msib.segment1,
               msib.description,
               moq.transaction_quantity,
               moq.LOCATOR_ID,
               moq.subinventory_code,
               mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
               mil.segment4 locator
          FROM APPS.MTL_ONHAND_QUANTITIES        MOQ,
               APPS.MTL_SYSTEM_ITEMS_B           MSIB,
               apps.mtl_item_locations           mil,
               APPS.ORG_ORGANIZATION_DEFINITIONS OOD
         WHERE 1 = 1
           and MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
           and moq.organization_id=msib.organization_id
           AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
            and msib.inventory_item_id=1887257
           and moq.LOCATOR_ID = mil.inventory_location_id
           and moq.organization_id = mil.organization_id
           and moq.SUBINVENTORY_CODE = mil.SUBINVENTORY_CODE) A
  left join
 (select mr.organization_id ,
         mr.INVENTORY_ITEM_ID ,
         mr.RESERVATION_QUANTITY,
         mr.LOCATOR_ID,
         mr.SUBINVENTORY_CODE
    from apps.MTL_RESERVATIONS mr
   where mr.SUPPLY_SOURCE_TYPE_ID = 13) B --inventory)
    on (A.organization_id = B.organization_id and
       A.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID and
       A.LOCATOR_ID = B.LOCATOR_ID and
       A.SUBINVENTORY_CODE = B.SUBINVENTORY_CODE)

or---------------------

select ood.legal_entity,
               ood.operating_unit,
               ood.organization_id,
               ood.organization_code,
               ood.organization_name,
               msib.inventory_item_id,
               msib.segment1,
               msib.description,
               moq.transaction_quantity,
               mr.RESERVATION_QUANTITY,
               moq.LOCATOR_ID,
               moq.subinventory_code,
               mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
               mil.segment4 locator
          FROM APPS.MTL_ONHAND_QUANTITIES        MOQ,
               APPS.MTL_SYSTEM_ITEMS_B           MSIB,
               apps.mtl_item_locations           mil,
               APPS.ORG_ORGANIZATION_DEFINITIONS OOD,
               apps.MTL_RESERVATIONS mr
         WHERE 1 = 1
           and MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
           and moq.organization_id=msib.organization_id
          -- and msib.segment1 = 'KHA005201701'
           AND MSIB.ORGANIZATION_ID = OOD.ORGANIZATION_ID
            and msib.inventory_item_id=1887257
           and moq.LOCATOR_ID = mil.inventory_location_id
           and moq.organization_id = mil.organization_id
           and moq.SUBINVENTORY_CODE = mil.SUBINVENTORY_CODE
           and moq.organization_id = mr.organization_id(+)
           and moq.INVENTORY_ITEM_ID = mr.INVENTORY_ITEM_ID(+)
           and moq.LOCATOR_ID = mr.LOCATOR_ID(+)
           and moq.SUBINVENTORY_CODE = mr.SUBINVENTORY_CODE(+)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-660928/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15225049/viewspace-660928/