--OPM Quality Sample data with Lot number
SELECT a.source_header_id,
a.source_line_id,
g.sample_no,
g.sample_desc,
g.sample_nosample_qty,
g.sample_qty_uom,
a.LOT_NUMBER,
e.test_unit "TEST",
NVL (b.result_value_char, b.result_value_num) "TEST RESULTS",
e.test_id,
c.segment1 item_no,
c.inventory_item_id item_id,
d.lot_number lot_no,
f.min_value_num,
b.result_date,
f.max_value_num,
g.lot_no,
e.test_unit,
f.target_value_num,
b.test_id,
f.target_value_char "TEXT_SPEC",
(f.min_value_num || ' - ' || f.max_value_num || ' - ' || test_unit)
"SPEC",
g.lot_no
FROM wsh_delivery_details a,
gmd_samples g,
gmd_results b,
gmd_qc_tests e,
gmd_spec_tests_b f,
gmd_event_spec_disp h,
mtl_system_items_b c,
mtl_lot_numbers d
WHERE 1 = 1
AND a.lot_number = g.lot_number
AND a.inventory_item_id = g.inventory_item_id
AND a.organization_id = g.organization_id
AND b.sample_id = g.sample_id
AND b.test_id = e.test_id
AND b.test_id = f.test_id
AND f.spec_id = h.spec_id
AND h.sampling_event_id = g.sampling_event_id
AND a.inventory_item_id = c.inventory_item_id
AND a.organization_id = c.organization_id
AND g.inventory_item_id = c.inventory_item_id
AND g.organization_id = c.organization_id
AND a.inventory_item_id = d.inventory_item_id
AND a.organization_id = d.organization_id
AND a.lot_number = d.lot_number
AND d.inventory_item_id = c.inventory_item_id
AND d.organization_id = c.organization_id
AND d.inventory_item_id = g.inventory_item_id
AND d.organization_id = g.organization_id
AND d.lot_number = g.lot_number
AND a.released_status = 'C'
--AND e.test_code not like '%DESPATCH%'
AND e.delete_mark = 0
--AND b.attribute18 = 'Y'
AND b.sample_id IN
(SELECT MAX (sample_id)
FROM gmd_samples
WHERE inventory_item_id = g.inventory_item_id
AND SOURCE = NVL (:p_source, SOURCE)
AND lot_number = g.lot_number)
AND G.SOURCE = NVL (:p_source, G.SOURCE)
Order By g.sample_no