Inventory Material Transactions Reports

1)Inter organization Reports
--------------------------------
SELECT  segment1 code
       ,msib.description Material_Description
       ,msib.primary_unit_of_measure uom
       ,abs(mmt.transaction_quantity) Quantity
       ,mmt.subinventory_code
       ,mmt.transfer_subinventory
       ,mmt.transaction_date  dd      
       ,fu.user_name
      , mmt.transaction_id trx 
      ,mmt.MATERIAL_EXPENSE_ACCOUNT
FROM MTL_SYSTEM_ITEMS_B msib,
     MTL_MATERIAL_TRANSACTIONS mmt ,
     FND_USER FU  
WHERE mmt.transaction_action_id in(21,3)
AND msib.organization_id = MMT.organization_id
AND msib.inventory_item_id = MMT.inventory_item_id
AND mmt.organization_id=:P_FROM_ORGANIZATION_ID
AND mmt.transfer_organization_id= :P_TO_ORGANIZATION_ID
AND mmt.subinventory_code=nvl(:P_FROM_SUBINV,mmt.subinventory_code)
AND mmt.created_by = :P_USER_ID
AND to_date(to_char(mmt.transaction_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS')= :P_DATE
AND msib.inventory_item_id=nvl(:P_INV_ID,msib.INVENTORY_ITEM_ID)
and mmt.created_by =fu.user_id

2)MISCELLANOUS REPORT
--------------------------------------------
SELECT msib.segment1 CODE,
       msib.description Material_Description,
       msib.PRIMARY_UNIT_OF_MEASURE UOM,
       abs(mmt.TRANSACTION_QUANTITY) Quantity ,
       mmt.SUBINVENTORY_CODE,     
       mmt.transaction_date,
       MMT.MATERIAL_EXPENSE_ACCOUNT,
       MMT.TRANSACTION_ID,
       fu.user_name      
FROM mtl_system_items_b msib,
     mtl_material_transactions mmt,
     fnd_user fu   
WHERE mmt.TRANSACTION_ACTION_ID =1
AND MSIB.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSIB.INVENTORY_ITEM_ID = MMT.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID=:P_FROM_ORGANIZATION_ID  --169
AND mmt.SUBINVENTORY_CODE=:P_FROM_SUBINV--VPT-M-STR
AND mmt.created_by = :P_USER_ID --1199
AND to_date(to_char(transaction_date,'YYYY/MM/DD HH24:MI:SS'),'YYYY/MM/DD HH24:MI:SS') = :P_DATE 
AND msib.INVENTORY_ITEM_ID=nvl(:P_INV_ID,msib.INVENTORY_ITEM_ID)
and fu.user_id=mmt.created_by

3)Subinventory Transfer Report
--------------------------------------------
select msi.segment1 code
      ,msi.description
      ,abs(mmt.TRANSACTION_QUANTITY)
      ,SUBINVENTORY_CODE
      ,TRANSFER_SUBINVENTORY
      ,transaction_date
      ,fu.USER_NAME
      ,mmt.TRANSACTION_ID TRX      
 from  mtl_material_transactions mmt,
       mtl_system_items_b msi,
       fnd_user fu
where TRANSACTION_ACTION_id=2
and msi.organization_id=mmt.organization_id
and mmt.inventory_item_id=msi.inventory_item_id
and mmt.organization_id=:P_FROM_ORGANIZATION_ID --VP1
and SUBINVENTORY_CODE=:P_FROM_SUBINV --VPT-M-STR
and TRANSFER_SUBINVENTORY=:P_TO_SUBINV --D14-PS-STR
AND to_date(to_char(mmt.transaction_date,'DD/MON/YYYY HH24:MI:SS'),'DD/MON/YYYY HH24:MI:SS')= :P_DATE  --1/13/2014 12:48:29 PM
AND mmt.created_by = :P_USER_ID --1199
AND msi.inventory_item_id=nvl(:P_INV_ID,msi.inventory_item_id)
and mmt.created_by=fu.user_id