Oracle Applications : Inventory Queries

------Item category Query---------------

SELECT   DISTINCT msib.SEGMENT1 "item_name",
                  ood.ORGANIZATION_NAME,
                  msi.SECONDARY_INVENTORY_NAME,
                  mil.SEGMENT1 || '-' || mil.SEGMENT2 "locator_name",
                  mcb.SEGMENT1 "category",
                  mcst.CATEGORY_SET_NAME
  FROM   mtl_system_items_b msib,
         mtl_secondary_inventories msi,
         org_organization_definitions ood,
         mtl_item_locations mil,
         MTL_ITEM_CATEGORIES mic,
         mtl_categories_b mcb,
         mtl_category_sets_tl mcst
 WHERE       msib.ORGANIZATION_ID = ood.ORGANIZATION_ID
         AND msib.INVENTORY_ITEM_ID = mic.INVENTORY_ITEM_ID
         AND mic.CATEGORY_ID = mcb.CATEGORY_ID
         AND mic.CATEGORY_SET_ID = mcst.CATEGORY_SET_ID
         AND msib.ORGANIZATION_ID = msi.ORGANIZATION_ID
         AND msib.ORGANIZATION_ID = mil.ORGANIZATION_ID
         AND msib.SEGMENT1 LIKE 'CP10ITE%'
         AND msi.SECONDARY_INVENTORY_NAME LIKE 'CP10SUB1'
        


-----------------Item Description--------------        
       
SELECT   ood.organization_name "INVENTORY_ORG_NAME",
         ood.organization_code "INVENTORY_CODE",
         msib.segment1 "ITEM_NAME",
         msib.description "ITEM_DESCRIPTION",
         msib.primary_unit_of_measure,
         msib.inventory_item_status_code "ITEM_STATUS",
         msib.inventory_item_flag "INVENTORY_ITEM_CHECK_BOX",
         msib.segment1 "INVENTORY_NAME",
         msib.mtl_transactions_enabled_flag "TRANSACTION_ITEM",
         msib.purchasing_item_flag "PURCHASING_ITEM",
         msib.expense_account "PURCHASING_ITEM_EXPENSE_ACC",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '_'
         || gcc.segment4
            "ACCOUNT_N0"
  FROM   mtl_system_items_b MSIB,
         org_organization_definitions ood,
         gl_code_combinations gcc
 WHERE       msib.organization_id = ood.organization_id
         AND gcc.code_combination_id = msib.expense_account
         AND msib.segment1 like 'KITTU_ITEM'
         AND ood.organization_name like 'Vision Operations'
        
----------------------Item Transactions--------

/* Formatted on 2/13/2015 3:47:50 PM (QP5 v5.115.810.9015) */
SELECT   mmt.transaction_date,
         mtt.transaction_type_name,
         mmt.subinventory_code,
         mmt.transaction_quantity,
         mmt.transaction_uom,
         gcck.concatenated_segments
  FROM   mtl_material_transactions mmt,
         mtl_system_items_b msib,
         mtl_transaction_types mtT,
         gl_code_combinations_kfv gcck
 WHERE       msib.inventory_item_id = mmt.inventory_item_id
         AND mmt.distribution_account_id = gcck.code_combination_id
         AND mmt.transaction_type_id = mtt.transaction_type_id
         AND msib.segment1 LIKE 'XYX'
         AND mmt.transaction_date BETWEEN TO_DATE ('10-12-2010',
                                                   'dd-mm-yyyy')
                                      AND  TO_DATE (,(SYSDATE),
                                                    'dd-mm-yyyy')
         AND mtt.transaction_type_name LIKE 'Miscellaneous issue'
        
        
----------------------------------------------------------
-----------------onhand quantities-------
/* Formatted on 2/13/2015 3:51:38 PM (QP5 v5.115.810.9015) */
SELECT   ood.organization_name "INVENTORY_ORG_NAME",
         ood.organization_code "INVENTORY_CODE",
         msib.segment1 "ITEM_NAME",
         msib.description "ITEM_DESCRIPTION",
         msib.primary_unit_of_measure,
         msib.inventory_item_status_code "ITEM_STATUS",
         msib.inventory_item_flag "INVENTORY_ITEM_CHECK_BOX",
         msib.segment1 "INVENTORY_NAME",
         msib.mtl_transactions_enabled_flag "TRANSACTION_ITEM",
         msib.purchasing_item_flag "PURCHASING_ITEM",
         msib.expense_account "PURCHASING_ITEM_EXPENSE_ACC",
            gcc.segment1
         || '-'
         || gcc.segment2
         || '-'
         || gcc.segment3
         || '_'
         || gcc.segment4
            "ACCOUNT_N0"
  FROM   mtl_system_items_b MSIB,
         org_organization_definitions ood,
         gl_code_combinations gcc
 WHERE       msib.organization_id = ood.organization_id
         AND gcc.code_combination_id = msib.expense_account
         AND msib.segment1 like 'xyz'
         AND ood.organization_name LIKE 'Vision Operations'