Inventory Useful Query

1. Pull the details of item lot numbers.

/* Formatted on 9/4/2014 2:05:45 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1 "Item Name",
       msib.description,
       ml.meaning,
       msib.auto_lot_alpha_prefix "Lot Prefix",
       msib.start_auto_lot_number "Lot Starting Number",
       mln.lot_number
FROM mtl_system_items_b msib, mtl_lot_numbers mln, mfg_lookups ml
WHERE     msib.organization_id = mln.organization_id
      AND msib.inventory_item_id = mln.inventory_item_id
      AND msib.lot_control_code = ml.lookup_code
      AND ml.lookup_type LIKE 'MTL_LOT_CONTROL'
      AND msib.segment1 LIKE 'sample_464';
--and msib.segment1 like 'Lot control item';

2. Display transaction quantity at Sub-Inventory level.

/* Formatted on 9/4/2014 2:05:58 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1, moq.subinventory_code, SUM (moq.transaction_quantity)
FROM mtl_system_items_b msib, mtl_onhand_quantities moq --mtl_material_transactions
WHERE     msib.inventory_item_id = moq.inventory_item_id
      AND msib.organization_id = moq.organization_id
      AND msib.inventory_item_id = 13128
GROUP BY msib.segment1, moq.subinventory_code, moq.organization_id;


3. Display details of Items, Organization and controls on that item.

/* Formatted on 9/4/2014 2:06:13 PM (QP5 v5.115.810.9015) */
SELECT item.segment1,
       org.organization_code,
       l1.meaning,
       l2.meaning,
       l3.meaning
FROM mtl_system_items_b item,
     mtl_parameters org,
     mfg_lookups l1,
     mfg_lookups l2,
     mfg_lookups l3
WHERE     1 = 1
      AND org.organization_id = item.organization_id
      AND l1.lookup_code = item.lot_control_code
      AND l1.lookup_type = 'MTL_LOT_CONTROL'
      AND l2.lookup_code = item.serial_number_control_code
      AND l2.lookup_type = 'MTL_SERIAL_NUMBER'
      AND l3.lookup_code = item.shelf_life_code
      AND l3.lookup_type = 'MTL_SHELF_LIFE'
      AND item.segment1 LIKE 'sample_464';
     
4. Query to list out main assembly item and corresponding Components

/* Formatted on 9/4/2014 2:06:34 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1 "Component Name", msib1.segment1 "Sub-Components Name"
FROM mtl_system_items_b msib,
     mtl_system_items_b msib1,
     bom_bill_of_materials bbom,
     bom_inventory_components bic
WHERE     bbom.organization_id = msib.organization_id
      AND bbom.assembly_item_id = msib.inventory_item_id
      AND bbom.bill_sequence_id = bic.bill_sequence_id
      AND bic.component_item_id = msib1.inventory_item_id
      AND msib.segment1 LIKE 'NokiaMobile'
      AND bbom.alternate_bom_designator IS NULL
      AND msib1.organization_id = msib.organization_id;


5. Item NO, Organization, Cost Type Name and material Cost of Item.

/* Formatted on 9/4/2014 2:06:50 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1 "Item Name",
       mp.organization_code,
       cict.cost_type,
       cic.material_cost
FROM mtl_system_items_b msib,
     mtl_parameters mp,
     cst_item_costs cic,
     cst_cost_types cict
WHERE     1 - 1 = 0
      AND msib.organization_id = mp.organization_id
      AND msib.organization_id = cic.organization_id
      AND msib.inventory_item_id = cic.inventory_item_id
      AND cic.cost_type_id = cict.cost_type_id
      AND msib.segment1 LIKE 'NokiaMobile'
      AND cic.organization_id = 207;

6. Display details of all Routing Information, if Standard Operation display Standard Operation Name.

/* Formatted on 9/4/2014 2:06:59 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1,
       mp.organization_code,
       msib.description,
       bd.department_class_code,
       bd.department_code,
       br.resource_code,
       bos.operation_description,
       bso.operation_code
FROM mtl_system_items_b msib,
     mtl_parameters mp,
     bom_operational_routings bor,
     bom_operation_sequences bos,
     bom_standard_operations bso,
     bom_departments bd,
     bom_department_classes bdc,
     bom_operation_resources bore,
     bom_resources br
WHERE     1 = 1
      AND msib.organization_id = mp.organization_id
      AND msib.inventory_item_id = bor.assembly_item_id
      AND msib.organization_id = bor.organization_id
      AND bor.routing_sequence_id = bos.routing_sequence_id
      AND bos.standard_operation_id = bso.standard_operation_id
      AND bos.department_id = bd.department_id
      AND bd.organization_id = bdc.organization_id
      AND bd.department_class_code = bdc.department_class_code
      AND bos.operation_sequence_id = bore.operation_sequence_id
      AND bore.resource_id = br.resource_id
      AND bd.organization_id = br.organization_id
      AND msib.segment1 LIKE 'NokiaMobile'
      AND msib.organization_id = 207
      AND bor.alternate_routing_designator IS NULL;

7.Write a query to find the Item name, Organization, Revision, Inventory Item Id of a item based on the Item
name(Inventory).

/* Formatted on 9/4/2014 2:07:12 PM (QP5 v5.115.810.9015) */
SELECT msib.segment1 "Item Name", mp.organization_code, mir.revision_label
FROM mtl_system_items_b msib, mtl_item_revisions mir, mtl_parameters mp
WHERE     1 - 1 = 0
      AND msib.organization_id = mp.organization_id
      AND msib.inventory_item_id = mir.inventory_item_id
      AND msib.organization_id = mir.organization_id
      AND msib.segment1 LIKE 'NokiaMobile';