OPM QUERY TO FIND BATCH STATUS AND DETAILS.

select hou.name organization_name,
       mp.organization_code,
       gbh.batch_no batch_number,
       flv.meaning  batch_status,
       ffmb.attribute4 batch_type,
       gbh.attribute9 lot_number,
       msib.segment1 item_number
from apps.gme_batch_header gbh,gme_material_details gmd,
     mtl_system_items_b msib,hr_all_organization_units hou,mtl_parameters mp,
     apps.fnd_lookup_values flv,fm_form_mst_b ffmb
where     1=1
      AND gbh.batch_id = gmd.batch_id
      AND gbh.organization_id = gmd.organization_id
      AND gmd.organization_id = msib.organization_id
      AND gmd.inventory_item_id = msib.inventory_item_id
      AND gmd.line_type = '1'
      AND gmd.line_no = 1
      and gmd.organization_id = hou.organization_id
      and gmd.organization_id = mp.organization_id
      AND flv.lookup_code = TO_CHAR (gbh.batch_status)
      AND flv.lookup_type = 'GME_BATCH_STATUS'
      AND flv.language = 'US'
      AND ffmb.formula_id=gbh.formula_id
      ;