Inventory Tables and LINKS

SELECT * FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE='AR6'

select * from mtl_secondary_inventories where organization_id=8313 and secondary_inventory_name='FGI'

SELECT * FROM MTL_ITEM_LOCATIONS WHERE ORGANIZATION_ID='8313'

========================================================================================
INVENTORY :

select * from mtl_system_items_b where segment1='lenovo 15"led'

select * from mtl_system_items_tl where inventory_item_id=237063

select * from mtl_item_categories  where  inventory_item_id =237063

select * from mtl_item_revisions where inventory_item_id=237063

select * from mtl_customer_items where customer_item_number='lenovo 15"led india'

select * from mtl_customer_item_xrefs where customer_item_id=13237


13-MARCH-2013
=========================

SELECT SUM(MR.RESERVATION_QUANTITY) ITEM
      FROM MTL_RESERVATIONS MR,
           MTL_SYSTEM_ITEMS_B MSIB
     WHERE MSIB.ORGANIZATION_ID=MR.ORGANIZATION_ID
      AND  MSIB.SEGMENT1='DELL 26" LCD'
     
SELECT MOQ.TRANSACTION_QUANTITY ONHAND_QUANTITY
       FROM MTL_ONHAND_QUANTITIES MOQ,
            MTL_SYSTEM_ITEMS_B MSIB,
            MTL_PARAMETERS MP
       WHERE MOQ.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
       AND  MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
       AND  MOQ.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
      AND  MSIB.SEGMENT1='DELL 26" LCD'
       AND  MOQ.ORGANIZATION_ID=MP.ORGANIZATION_ID
       AND  MP.ORGANIZATION_CODE='M1'
      
      
SELECT ORGANIZATION_CODE MASTER_ORGANIZATION_CODE
      FROM  MTL_PARAMETERS MP
      WHERE ORGANIZATION_ID=MASTER_ORGANIZATION_ID

SELECT ORGANIZATION_CODE CHILD_INV_ORG_CODE
      FROM  MTL_PARAMETERS MP
      WHERE ORGANIZATION_ID<>MASTER_ORGANIZATION_ID
     
SELECT MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE MASTER_ORGANIZATION_CODE
     FROM MTL_PARAMETERS MPC,
          MTL_PARAMETERS MPM
     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID  
    
SELECT MPC.ORGANIZATION_CODE,DECODE(MPC.ORGANIZATION_CODE,MPM.ORGANIZATION_CODE,'ITSELF',MPM.ORGANIZATION_ID) MASTER_ORGANIZATION_CODE
     FROM MTL_PARAMETERS MPC,
          MTL_PARAMETERS MPM
     WHERE MPC.MASTER_ORGANIZATION_ID=MPM.ORGANIZATION_ID
    
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M1'      INTERSECT
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M2'  
     
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M2'  
     MINUS
SELECT MSIB.SEGMENT1 ITEMS
      FROM MTL_SYSTEM_ITEMS_B MSIB,
           MTL_PARAMETERS MP
      WHERE MSIB.ORGANIZATION_ID=MP.ORGANIZATION_ID
      AND MP.ORGANIZATION_CODE='M1'
     
SELECT SUM(MOQ.TRANSACTION_QUANTITY) ONHAND_QUANTITY
      FROM MTL_ONHAND_QUANTITIES MOQ,
           MTL_SYSTEM_ITEMS_B MSIB
      WHERE MSIB.INVENTORY_ITEM_ID=MOQ.INVENTORY_ITEM_ID
      AND MSIB.ORGANIZATION_ID=MOQ.ORGANIZATION_ID
      AND MSIB.SEGMENT1='DELL 26" LCD'


==============================================

14-march-2013

SELECT msib.segment1 item,
       msib.description,
       msib.primary_uom_code uom,
       mp.organization_code,
       sum(moq.transaction_quantity) onahnd_qty,
       moq.subinventory_code
  FROM MTL_SYSTEM_ITEMS_B MSIB,
       MTL_PARAMETERS MP,
       MTL_ONHAND_QUANTITIES MOQ
 WHERE SEGMENT1 = 'DELL 28" LCDR'
   AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
   AND MP.ORGANIZATION_CODE = :P_ORGANIZATION_CODE
   AND NVL(MSIB.MTL_TRANSACTIONS_ENABLED_FLAG,'N') = 'N'
   AND MOQ.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
   AND MOQ.ORGANIZATION_ID = MSIB.ORGANIZATION_ID
GROUP BY  msib.segment1 ,
          msib.description,
          msib.primary_uom_code,
          moq.subinventory_code,
          mp.organization_code
ORDER BY 4,1,6