onhand with serial number

as the user request, need the onhand with serial number, also need the last po informatin, so i used the following query:

select distinct msib.segment1,
                msib.description,
                msib.inventory_item_id,
                moq.transaction_quantity,
                moq.subinventory_code,
                mil.segment1 || '.' || mil.segment2 || '.' || mil.segment3 || '.' ||
                mil.segment4 || '.' || pa.segment1 || '.' || t.task_number locator,
                msn.SERIAL_NUMBER,
                v.VENDOR_NAME "Supplier",
                pha.segment1,
                pha.TYPE_LOOKUP_CODE

  FROM APPS.MTL_ONHAND_QUANTITIES_detail MOQ,
       APPS.MTL_SYSTEM_ITEMS_B           MSIB,
       apps.mtl_item_locations           mil,
       apps.mtl_serial_numbers           msn,
       apps.Pa_projects_all              pa,
       pa.pa_tasks                       t,
       apps.mtl_material_transactions    mmt,
       apps.po_headers_all               pha,
       po.po_vendors                     v
 where msib.organization_id = 00
   and moq.organization_id = 00
   and msib.inventory_item_id = moq.inventory_item_id
   and MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   and  msib.inventory_item_id = mmt.inventory_item_id
   and moq.LOCATOR_ID = mil.inventory_location_id
   and mil.project_id = pa.project_id(+)

   and moq.task_id = t.task_id(+)

   and moq.inventory_item_id = msn.inventory_item_id
   and moq.subinventory_code = msn.CURRENT_SUBINVENTORY_CODE
   and moq.LOCATOR_ID = msn.CURRENT_LOCATOR_ID
   and msn.CURRENT_STATUS = 3
  and mmt.transaction_id =
       (select max(transaction_id)
          from apps.mtl_material_transactions mmta
         where mmta.organization_id = 00
           and mmta.inventory_item_id = msib.inventory_item_id
           and mmta.transaction_type_id = 18)
   and msn.current_organization_id = 00
   and mmt.organization_id = 00
   AND MMT.TRANSACTION_TYPE_ID = 18
   and mmt.TRANSACTION_SOURCE_ID = pha.po_header_id
   and pha.vendor_id = v.vendor_id;

 

At first i though use the apps.mtl_unit_transactions mut for check the serial number information. but i found the result is not correctly

 

the onhand with the following transaction id:
4066
6055 (po receive)  09-AUG-2012 12:19:16
4134
3966
4080
4159 08-JUN-2012 09:18:45
9794 (subtransfer)
3993
but for 6055  po receive with serial A
in mmt 4709  po receive with serial B
in mmt 9794 subinventory transfer with A
so the onhand should be trans 4709, but we can't find this recrod in onhand. only 6055

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

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