/*********************************************************
*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 ;