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