OPM Quality – Query to List Samples Information

/*********************************************************
*PURPOSE: Query to List Samples Information *
*AUTHOR: Shailender Thallam *
**********************************************************/
SELECT gs.sample_no,
  sample_desc,
  gs.organization_id,
  msi.segment1 item_number,
  msi.description ,
  gs.sampling_event_id ,
  gs.step_no ,
  gs.step_id ,
  gs.sample_id ,
  gs.sample_no ,
  gs.sample_desc ,
  gs.type ,
  gs.qc_lab_orgn_code ,
  gs.item_id ,
  gs.location ,
  gs.expiration_date ,
  gs.lot_id ,
  gs.lot_no ,
  gs.batch_id ,
  gs.recipe_id ,
  gs.formula_id ,
  gs.formulaline_id ,
  gs.routing_id ,
  gs.oprn_id ,
  gs.charge ,
  gs.cust_id ,
  gs.order_id ,
  gs.order_line_id ,
  gs.org_id ,
  gs.supplier_id ,
  gs.sample_qty ,
  gs.sample_uom ,
  gl1.meaning "SOURCE" ,
  gs.sampler_id ,
  gs.date_drawn ,
  gs.source_comment ,
  gs.storage_whse ,
  gs.storage_location ,
  gs.external_id ,
  gs.sample_approver_id ,
  gs.inv_approver_id ,
  gl2.meaning "PRIORITY" ,
  gs.sample_inv_trans_ind ,
  gs.delete_mark ,
  gs.text_code ,
  gs.attribute_category ,
  gs.creation_date ,
  gs.created_by ,
  gs.last_updated_by ,
  gs.last_update_date ,
  gs.last_update_login ,
  gs.supplier_site_id ,
  gs.whse_code ,
  gs.orgn_code ,
  gs.po_header_id ,
  gs.po_line_id ,
  gs.receipt_id ,
  gs.receipt_line_id ,
  gl3.meaning "SAMPLE_DISPOSITION" ,
  gs.ship_to_site_id ,
  gs.supplier_lot_no ,
  gs.lot_retest_ind ,
  gs.sample_instance ,
  gs.sublot_no ,
  gs.source_whse ,
  gs.source_location ,
  gs.date_received ,
  gs.date_required ,
  gs.instance_id ,
  gs.resources ,
  gs.retrieval_date ,
  gl4.meaning "SAMPLE_TYPE" ,
  gs.time_point_id ,
  gs.variant_id ,
  gs.remaining_qty ,
  gs.retain_as ,
  gs.inventory_item_id ,
  gs.lab_organization_id ,
  gs.locator_id ,
  gs.lot_number ,
  gs.organization_id ,
  gs.parent_lot_number ,
  gs.revision ,
  gs.sample_qty_uom ,
  gs.source_locator_id ,
  gs.source_subinventory ,
  gs.storage_locator_id ,
  gs.storage_organization_id ,
  gs.storage_subinventory ,
  gs.subinventory ,
  gs.migrated_ind ,
  gs.material_detail_id ,
  gspec.spec_name ,
  gspec.spec_vers ,
  gspec.spec_desc
FROM gmd_samples gs,
  mtl_system_items_b msi,
  gem_lookups gl1,
  gem_lookups gl2,
  gem_lookups gl3,
  gem_lookups gl4,
  gmd_event_spec_disp gesd,
  gmd_specifications gspec
WHERE gs.sample_id = 4969 --Sample ID
  --
AND gs.inventory_item_id = msi.inventory_item_id
AND gs.organization_id   = msi.organization_id
  --
AND gl1.lookup_type = 'GMD_QC_SOURCE'
AND gl1.lookup_code = gs.source
  --
AND gl2.lookup_type = 'GMD_QC_TEST_PRIORITY'
AND gl2.lookup_code = gs.priority
  --
AND gl3.lookup_type = 'GMD_QC_SAMPLE_DISP'
AND gl3.lookup_code = gs.sample_disposition
  --
AND gl4.lookup_type = 'GMD_QC_SPEC_TYPE'
AND gl4.lookup_code = gs.sample_type
  --
AND gs.sampling_event_id = gesd.sampling_event_id
  --
AND gesd.spec_id = gspec.spec_id ;