[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT st.test_id
, st.seq
, st.test_replicate
, tm.test_kit_inv_item_id
, tm.resources, st.viability_duration
, st.test_qty
, st.test_qty_uom
, tm.test_method_id
, tm.test_duration
FROM gmd_spec_tests_b st, gmd_test_methods_b tm
WHERE st.spec_id = p_spec_id
AND st.exclude_ind IS NULL
AND ((p_lot_retest_ind IS NULL) OR
(st.retest_lot_expiry_ind = p_lot_retest_ind)
)
AND st.test_method_id = tm.test_method_id
;
SELECT spec_id
FROM gmd_inventory_spec_vrs
where spec_vr_id = p_spec_vr_id
UNION ALL
SELECT spec_id
FROM gmd_wip_spec_vrs
where spec_vr_id = p_spec_vr_id
UNION ALL
SELECT spec_id
FROM gmd_customer_spec_vrs
where spec_vr_id = p_spec_vr_id
UNION ALL
SELECT spec_id
FROM gmd_supplier_spec_vrs
where spec_vr_id = p_spec_vr_id
UNION ALL
SELECT spec_id
FROM gmd_monitoring_spec_vrs
where spec_vr_id = p_spec_vr_id
-- B3584185 Added following to the union query
UNION ALL
SELECT spec_id
FROM gmd_stability_spec_vrs
where spec_vr_id = p_spec_vr_id
;
e_results_insert_error EXCEPTION;
e_spec_results_insert_error EXCEPTION;
e_event_spec_disp_insert_error EXCEPTION;
e_sample_spec_disp_insert_err EXCEPTION;
SELECT meaning
INTO l_meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_SAMPLE_DISP'
AND lookup_code = l_sampling_event.disposition;
l_event_spec_disp.delete_mark := 0;
l_event_spec_disp.last_update_date := l_date;
l_event_spec_disp.last_updated_by := l_user_id;
IF NOT(gmd_event_spec_disp_pvt.insert_row(
p_event_spec_disp => l_event_spec_disp,
x_event_spec_disp => l_out_event_spec_disp)
)
THEN
-- Insert Error
RAISE e_event_spec_disp_insert_error;
l_sample_spec_disp.delete_mark := 0;
l_sample_spec_disp.last_update_date := l_date;
l_sample_spec_disp.last_updated_by := l_user_id;
IF NOT(gmd_sample_spec_disp_pvt.Insert_Row(
p_sample_spec_disp => l_sample_spec_disp)
)
THEN
-- Insert Error
RAISE e_sample_spec_disp_insert_err;
l_results.delete_mark := 0;
l_results.last_updated_by := l_user_id;
l_results.last_update_date := l_date;
IF NOT(GMD_RESULTS_PVT.Insert_Row(
p_results => l_results,
x_results => l_out_results)
)
THEN
-- Insert Error
RAISE e_results_insert_error;
l_spec_results.delete_mark := 0;
l_spec_results.last_updated_by := l_user_id;
l_spec_results.last_update_date := l_date;
IF NOT(gmd_spec_results_pvt.Insert_Row(p_spec_results => l_spec_results))
THEN
-- Insert Error
RAISE e_spec_results_insert_error;
UPDATE gmd_event_spec_disp
SET disposition = '2I',
last_updated_by = l_user_id,
last_update_date = l_date
WHERE event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
;
UPDATE gmd_sampling_events
SET disposition = '2I',
last_updated_by = l_user_id,
last_update_date = l_date
WHERE sampling_event_id = l_sampling_event.sampling_event_id
;
OR e_results_insert_error
OR e_spec_results_insert_error
OR e_event_spec_disp_insert_error
OR e_sample_spec_disp_insert_err
OR e_event_spec_disp_fetch_error
THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
PROCEDURE delete_rslt_and_spec_rslt_rows
(
p_sample_id IN NUMBER
, x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
IF (l_debug = 'Y') THEN
gmd_debug.put_line('Entering procedure DELETE_RSLT_AND_SPEC_RSLT_ROWS');
DELETE gmd_spec_results
WHERE result_id IN (SELECT result_id
FROM gmd_results
WHERE sample_id = p_sample_id)
;
gmd_debug.put_line('Rows deleted from gmd_spec_results: '|| SQL%ROWCOUNT);
DELETE gmd_results
WHERE sample_id = p_sample_id
;
gmd_debug.put_line('Rows deleted from gmd_results: '|| SQL%ROWCOUNT);
gmd_debug.put_line('Leaving procedure DELETE_RSLT_AND_SPEC_RSLT_ROWS');
'PACKAGE','DELETE_RSLT_AND_SPEC_RSLT_ROWS',
'ERROR', SUBSTR(SQLERRM,1,100));
END delete_rslt_and_spec_rslt_rows;
SELECT event_spec_disp_id
FROM gmd_event_spec_disp
WHERE sampling_event_id = p_sampling_event_id
AND spec_used_for_lot_attrib_ind = 'Y'
AND delete_mark = 0
;
SELECT st.test_id
FROM gmd_spec_tests_b st
WHERE st.spec_id = p_spec_id
AND st.exclude_ind IS NULL
AND st.test_id NOT IN
(SELECT r.test_id
FROM gmd_results r
WHERE r.sample_id = p_sample_id)
ORDER BY st.seq
;
SELECT st.test_id
FROM gmd_spec_tests_b st
WHERE st.spec_id = p_spec_id
AND st.exclude_ind IS NULL
AND st.test_id NOT IN
(SELECT cr.test_id
FROM gmd_composite_results cr
WHERE cr.composite_spec_disp_id = p_composite_spec_disp_id)
ORDER BY st.seq
;
SELECT t.test_type, st.min_value_num, st.max_value_num, st.target_value_char
FROM gmd_qc_tests_b t, gmd_spec_tests_b st
WHERE t.test_id= st.test_id
AND st.exclude_ind IS NULL
AND st.spec_id = p_spec_id
AND st.test_id = p_test_id
;
SELECT text_range_seq
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND value_char = p_value_char
;
SELECT 1
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND nvl(min_num, p_num) <= p_num
AND nvl(max_num, p_num) >= p_num
;
SELECT tm.test_kit_inv_item_id,
tm.test_replicate,
tm.test_method_id, -- 5353794
tm.test_qty,
tm.test_qty_uom,
tm.test_duration -- Bug 13476354
FROM gmd_qc_tests_b t, gmd_test_methods_b tm
WHERE t.test_id = p_test_id
AND t.test_method_id = tm.test_method_id
;
SELECT disposition
FROM gmd_sample_spec_disp
WHERE sample_id = p_sample_id
AND event_spec_disp_id = p_event_spec_disp_id ;
e_results_insert_error EXCEPTION;
e_spec_results_insert_error EXCEPTION;
SELECT meaning
INTO l_meaning
FROM gem_lookups
WHERE lookup_type = 'GMD_QC_SAMPLE_DISP'
AND lookup_code = l_sample_spec_disp; -- 3903309
SELECT test_type
INTO l_test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_ids(i)
;
SELECT NVL(MAX(test_replicate_cnt), 0) + 1
INTO l_next_test_replicate_cnt
FROM gmd_results
WHERE sample_id = l_sample.sample_id
AND test_id = p_test_ids(i)
;
SELECT st.test_replicate, st.viability_duration, tm.resources
INTO l_replicate,l_viability_duration, l_resources
FROM gmd_spec_tests_b st, gmd_test_methods_b tm
WHERE st.spec_id = l_event_spec_disp.spec_id
AND st.exclude_ind IS NULL
AND st.test_id = p_test_ids(i)
AND st.test_method_id = tm.test_method_id
;
l_results.delete_mark := 0;
l_results.last_updated_by := l_user_id;
l_results.last_update_date := l_date;
l_spec_results.delete_mark := 0;
l_spec_results.last_updated_by := l_user_id;
l_spec_results.last_update_date := l_date;
gmd_debug.put_line('Inserting test for replicate: ' || l_results.TEST_REPLICATE_CNT);
IF NOT(GMD_RESULTS_PVT.Insert_Row(
p_results => l_results,
x_results => l_out_results)
)
THEN
-- Insert Error
RAISE e_results_insert_error;
gmd_debug.put_line('Result record inserted, Result ID: ' || l_results.RESULT_ID);
IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_results))
THEN
-- Insert Error
RAISE e_spec_results_insert_error;
gmd_debug.put_line('Spec Result record inserted');
UPDATE gmd_sample_spec_disp
SET disposition = '2I',
last_updated_by = l_user_id,
last_update_date = l_date
WHERE event_spec_disp_id = p_event_spec_disp_id
AND sample_id = l_sample.sample_id
;
UPDATE gmd_event_spec_disp
SET disposition = '2I',
last_updated_by = l_user_id,
last_update_date = l_date
WHERE event_spec_disp_id = p_event_spec_disp_id
;
UPDATE gmd_sampling_events
SET disposition = '2I',
recomposite_ind = 'Y',
last_updated_by = l_user_id,
last_update_date = l_date
WHERE sampling_event_id = l_sample.sampling_event_id
;
e_results_insert_error OR
e_spec_results_insert_error OR
e_samples_fetch_error OR
e_event_spec_fetch_error OR
e_sampling_event_fetch_error
THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
SELECT seq
FROM gmd_results
WHERE sample_id = p_sample_id
AND test_id = p_test_id
;
SELECT (floor(nvl(max(seq),0) / 10) * 10) + 10
INTO l_seq
FROM gmd_results
WHERE sample_id = p_sample_id
;
SELECT 1
FROM gmd_qc_test_values_b tv
WHERE tv.test_id = p_test_id
AND tv.expression_ref_test_id NOT IN
(SELECT test_id
FROM gmd_results
WHERE sample_id = p_sample_id)
;
SELECT result_id,
test_id,
result_value_num,
result_value_char,
result_date --Bug 5220513
FROM gmd_results
WHERE sample_id = p_sample_id
;
SELECT st.test_id
, st.test_replicate
, tm.test_kit_inv_item_id
FROM gmd_spec_tests_b st, gmd_test_methods_b tm
WHERE st.spec_id = p_spec_id
AND st.exclude_ind IS NULL
AND st.test_method_id = tm.test_method_id
;
SELECT spec_id
FROM gmd_com_spec_vrs_vl
WHERE spec_vr_id = p_spec_vr_id
;
SELECT *
FROM gmd_event_spec_disp
WHERE sampling_event_id = p_sampling_event_id
AND spec_vr_id = p_spec_vr_id
AND spec_used_for_lot_attrib_ind = 'Y'
;
SELECT evaluation_ind from gmd_spec_results gsr, gmd_event_spec_disp esd --, gmd_sample_spec_disp ssd
WHERE gsr.result_id=p_result_id
AND esd.event_spec_disp_id=p_event_spec_disp_id
AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
SELECT esd.event_spec_disp_id from gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
WHERE ssd.sample_id=p_sample_id
AND esd.event_spec_disp_id=ssd.event_spec_disp_id
AND esd.spec_used_for_lot_attrib_ind = 'Y';
SELECT t.test_type, t.test_code, t.test_method_id, t.expression, t.test_unit,
m.test_method_code
FROM gmd_qc_tests_b t , gmd_test_methods_b m
WHERE t.test_id = l_test_id
AND t.test_method_id = m.test_method_id;
SELECT v.display_label_numeric_range
FROM gmd_qc_test_values v
WHERE v.test_id = l_test_id;
SELECT s.min_value_num, s.max_value_num, s.target_value_num,s.display_precision
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
SELECT s.min_value_char, s.max_value_char, s.target_value_char
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
SELECT *
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id
;
SELECT organization_code
FROM mtl_parameters
WHERE organization_id = l_organization_id;
e_event_spec_disp_insert_error EXCEPTION;
e_sample_spec_disp_insert_err EXCEPTION;
e_spec_results_insert_error EXCEPTION;
UPDATE gmd_event_spec_disp
set spec_used_for_lot_attrib_ind = NULL
where sampling_event_id = l_sample.sampling_event_id
and spec_used_for_lot_attrib_ind = 'Y'
;
gmd_debug.put_line(' Updated all previous gmd_event_spec_disp to NOT-Current.');
l_event_spec_disp.DELETE_MARK := 0;
l_event_spec_disp.LAST_UPDATE_DATE := l_date;
l_event_spec_disp.LAST_UPDATED_BY := l_user_id;
IF NOT(GMD_EVENT_SPEC_DISP_PVT.Insert_Row(
p_event_spec_disp => l_event_spec_disp,
x_event_spec_disp => l_out_event_spec_disp)
)
THEN
-- Insert Error
RAISE e_event_spec_disp_insert_error;
l_sample_spec_disp.DELETE_MARK := 0;
l_sample_spec_disp.LAST_UPDATE_DATE := l_date;
l_sample_spec_disp.LAST_UPDATED_BY := l_user_id;
IF NOT(GMD_SAMPLE_SPEC_DISP_PVT.Insert_Row(
p_sample_spec_disp => l_sample_spec_disp)
)
THEN
-- Insert Error
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' MAKE_TARGET_SPEC_THE_BASE_SPEC RAISE e_sample_spec_disp_insert_err ');
RAISE e_sample_spec_disp_insert_err;
l_spec_result.DELETE_MARK := 0;
l_spec_result.LAST_UPDATED_BY := l_user_id;
l_spec_result.LAST_UPDATE_DATE := l_date;
IF NOT(GMD_SPEC_RESULTS_PVT.Insert_Row(p_spec_results => l_spec_result))
THEN
-- Insert Error
IF (l_debug = 'Y') THEN
gmd_debug.put_line(' gmd_results_grp.make_target_spec_the_base_spec Insert Error from GMD_SPEC_RESULTS_PVT.Insert_Row RAISE e_spec_results_insert_error ');
RAISE e_spec_results_insert_error;
gmd_debug.put_line('before SELECT nvl(max(test_replicate_cnt), 0) ');
SELECT nvl(max(test_replicate_cnt), 0)
INTO l_curr_replicate_cnt
FROM GMD_RESULTS
WHERE sample_id = l_sample.sample_id
AND test_id = l_spec_test.test_id
;
gmd_debug.put_line('after SELECT nvl(max(test_replicate_cnt), 0) ');
l_test_ids.DELETE;
OR e_spec_results_insert_error
OR e_event_spec_disp_insert_error
OR e_sample_spec_disp_insert_err
THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line('Leaving Procedure - MAKE_TARGET_SPEC_THE_BASE_SPEC with return status of ERROR ');
SELECT sampling_event_id
FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd
WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND csd.latest_ind = 'Y'
AND csd.delete_mark = 0
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND esd.delete_mark = 0
;
SELECT *
FROM gmd_result_data_points_gt;
SELECT t.test_type, t.test_code, t.test_method_id, t.expression, t.test_unit,
m.test_method_code
FROM gmd_qc_tests_b t , gmd_test_methods_b m
WHERE t.test_id = l_test_id
AND t.test_method_id = m.test_method_id;
SELECT v.display_label_numeric_range
FROM gmd_qc_test_values v
WHERE v.test_id = l_test_id;
SELECT s.min_value_num, s.max_value_num, s.target_value_num,s.display_precision
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
SELECT s.min_value_char, s.max_value_char, s.target_value_char
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
SELECT *
FROM gmd_results
WHERE sample_id = p_sample_id;
l_results_rec_tbl.DELETE;
SELECT sr.additional_test_ind
INTO X_additional_test
FROM gmd_spec_results sr
WHERE sr.event_spec_disp_id = p_event_spec_disp_id
AND sr.result_id = LocalResRec.result_id
;
SELECT display_precision
INTO X_display_precision
FROM gmd_qc_tests_b
WHERE test_id = l_test_id;
SELECT 1
FROM gmd_composite_spec_disp
WHERE event_spec_disp_id = p_event_spec_disp_id
AND nvl(latest_ind, 'N') = 'Y'
;
SELECT nvl(recomposite_ind, 'N')
FROM gmd_sampling_events
WHERE sampling_event_id = p_sampling_event_id
;
SELECT 1
FROM gmd_composite_spec_disp
WHERE event_spec_disp_id = p_event_spec_disp_id
AND nvl(latest_ind, 'N') = 'Y'
;
UPDATE gmd_sampling_events
SET recomposite_ind = 'Y'
WHERE sampling_event_id = p_sampling_event_id;
SELECT 1
FROM gmd_composite_spec_disp csd, gmd_composite_results cr, gmd_composite_result_assoc cra
WHERE csd.event_spec_disp_id = p_event_spec_disp_id
AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
AND cr.composite_result_id = cra.composite_result_id
AND cra.result_id = p_result_id
AND nvl(csd.latest_ind, 'N') = 'Y'
;
UPDATE gmd_sampling_events
SET recomposite_ind = 'Y'
WHERE sampling_event_id =
(SELECT sampling_event_id
FROM gmd_event_spec_disp
WHERE event_spec_disp_id = p_event_spec_disp_id);
SELECT s.sample_id
FROM gmd_event_spec_disp esd,
gmd_sample_spec_disp ssd,
gmd_samples s
WHERE esd.sampling_event_id = p_sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND esd.event_spec_disp_id = ssd.event_spec_disp_id
AND ssd.sample_id = s.sample_id
AND nvl(ssd.disposition, 'XX') NOT IN ('0RT', '7CN', '0PL')
AND esd.delete_mark = 0
AND ssd.delete_mark = 0
AND s.delete_mark = 0
ORDER BY s.sample_id
;
EXECUTE IMMEDIATE 'delete from gmd_result_data_points_gt';
'INSERT INTO gmd_result_data_points_gt (result_id, test_id, exclude_ind, data_num, data_char)'
||' ('
||' SELECT result_id, test_id, 0, result_value_num, result_value_char FROM'
||' ('
||' SELECT r.result_id, r.test_id, r.result_value_num, r.result_value_char,'
||' r.result_date, r.test_replicate_cnt,'
||' last_value(r.result_id)'
||' over (partition by r.test_id order by NVL(r.result_date, :l_start_date),'
||' r.test_replicate_cnt'
||' range between unbounded preceding and unbounded following) rmax_id'
||' FROM gmd_results r, gmd_spec_results sr, gmd_samples s'
||' WHERE r.result_id = sr.result_id'
||' AND r.sample_id = :l_sample_id'
||' AND sr.event_spec_disp_id = :l_event_spec_disp_id'
||' AND nvl(sr.evaluation_ind, ' || '''' || 'XX' || '''' || ') not in ('
|| '''' || '5O' || '''' ||','|| '''' || '4C' || '''' ||')'
||' AND sr.delete_mark = 0'
||' AND r.delete_mark = 0'
||' AND r.sample_id = s.sample_id'
||' AND s.retain_as IS NULL'
||' )'
||' WHERE result_id = rmax_id'
||')'
;
SELECT *
FROM gmd_result_data_points_gt
ORDER BY TEST_ID;
SELECT gt.test_id
FROM gmd_result_data_points_gt gt, gmd_results r
WHERE gt.result_id = r.result_id
GROUP BY gt.test_id
ORDER BY min(r.seq)
;
SELECT result_id, data_num, data_char
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
;
SELECT spec_id
FROM gmd_event_spec_disp
WHERE event_spec_disp_id = p_event_spec_disp_id
;
SELECT test_type, display_precision
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT display_precision
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id
AND exclude_ind IS NULL
;
e_comp_spec_disp_insert_error EXCEPTION;
e_comp_result_insert_error EXCEPTION;
e_spec_comp_rslt_insert_error EXCEPTION;
e_comp_rslt_assoc_insert_error EXCEPTION;
UPDATE gmd_composite_spec_disp
SET latest_ind = NULL
WHERE event_spec_disp_id = p_event_spec_disp_id
AND latest_ind = 'Y'
;
l_composite_spec_disp.DELETE_MARK := 0;
l_composite_spec_disp.LAST_UPDATE_DATE := l_date;
l_composite_spec_disp.LAST_UPDATED_BY := l_user_id;
IF NOT(GMD_COMPOSITE_SPEC_DISP_PVT.Insert_Row(
p_composite_spec_disp => l_composite_spec_disp,
x_composite_spec_disp => l_out_composite_spec_disp)
)
THEN
-- Insert Error
RAISE e_comp_spec_disp_insert_error;
gmd_debug.put_line(' Record inserted in GMD_COMPOSITE_SPEC_DISP, CSD ID-' || l_composite_spec_disp.composite_spec_disp_id);
l_composite_result.DELETE_MARK := 0;
l_composite_result.LAST_UPDATE_DATE := l_date;
l_composite_result.LAST_UPDATED_BY := l_user_id;
IF NOT(GMD_COMPOSITE_RESULTS_PVT.Insert_Row(
p_composite_results => l_composite_result,
x_composite_results => l_out_composite_result)
)
THEN
-- Insert Error
RAISE e_comp_result_insert_error;
l_composite_result_assoc.LAST_UPDATE_DATE := l_date;
l_composite_result_assoc.LAST_UPDATED_BY := l_user_id;
IF NOT(GMD_COMPOSITE_RESULT_ASSOC_PVT.Insert_Row(
p_composite_result_assoc => l_composite_result_assoc)
)
THEN
-- Insert Error
RAISE e_comp_rslt_assoc_insert_error;
UPDATE gmd_composite_results
SET sample_total = sample_cnt,
sample_cnt_used = sample_cnt
WHERE composite_result_id = l_composite_result.composite_result_id
AND test_id = test_rec.test_id
;
e_comp_spec_disp_insert_error OR
e_comp_result_insert_error OR
e_comp_rslt_assoc_insert_error
THEN
x_return_status := FND_API.G_RET_STS_ERROR ;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT avg(data_num)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT avg(data_num)
FROM
(
SELECT max(data_num) data_num
FROM
(
SELECT data_num
FROM
(
SELECT data_num
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
ORDER BY data_num
)
WHERE rownum <= (SELECT ceil(count(*)/2)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL)
)
UNION
SELECT min(data_num) data_num
FROM
(
SELECT data_num
FROM
(
SELECT data_num
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
ORDER BY data_num desc
)
WHERE rownum <= (SELECT ceil(count(*)/2)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL)
)
)
;
SELECT value_char
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND text_range_seq = p_num
;
SELECT nvl(count(*), 0)
INTO l_count
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
;
SELECT data_num
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
GROUP BY data_num
HAVING count(*) = (SELECT max(count(*))
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
GROUP BY data_num)
;
SELECT data_char
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_char IS NOT NULL
GROUP BY data_char
HAVING count(*) = (SELECT max(count(*))
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_char IS NOT NULL
GROUP BY data_char)
;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT value_char
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND text_range_seq = p_num
;
SELECT max(data_num)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT value_char
INTO x_high_char
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND text_range_seq = x_high_num
;
SELECT min(data_num)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT value_char
INTO x_low_char
FROM gmd_qc_test_values_b
WHERE test_id = p_test_id
AND text_range_seq = x_low_num
;
SELECT test_type
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT stddev(data_num)
FROM gmd_result_data_points_gt
WHERE test_id = p_test_id
AND exclude_ind = 0
AND data_num IS NOT NULL
;
SELECT cr.composite_spec_disp_id,
cr.composite_result_id,
cr.in_spec_ind,
cr.test_id,
cr.median_num,
cr.median_char,
cr.mode_num,
cr.mode_char
FROM gmd_composite_results cr
WHERE cr.composite_spec_disp_id = p_composite_spec_disp_id;
SELECT t.test_type, t.test_code, t.test_method_id, m.test_method_code
FROM gmd_qc_tests_b t , gmd_test_methods_b m
WHERE t.test_id = l_test_id
AND t.test_method_id = m.test_method_id;
SELECT s.min_value_num, s.max_value_num, s.target_value_num
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
SELECT s.min_value_char, s.max_value_char, s.target_value_char
FROM gmd_spec_tests_b s
WHERE s.spec_id = l_spec_id
AND s.test_id = l_test_id
AND s.exclude_ind IS NULL;
l_comres_tab.DELETE;
SELECT transaction_reason_id
FROM gmd_quality_config
WHERE organization_id = p_organization_id
AND transaction_reason_id IS NOT NULL
;
gmd_samples_grp.update_lot_grade_batch( -- nsrivast
p_sample_id => p_sample_id
, p_composite_spec_disp_id => NULL
, p_to_lot_status_id => p_lot_status
, p_from_lot_status_id => NULL --p_from_lot_status
, p_to_grade_code => NULL
, p_from_grade_code => NULL
, p_to_qc_status => NULL
, p_hold_date => NULL
, p_reason_id => l_reason_id
-- , p_update_child => NULL -- Added for Results Convergence. rboddu.
, x_return_status => x_return_status
, x_message_data => l_message_data );
SELECT 1
FROM cr_rsrc_mst
WHERE resources = p_resource
AND delete_mark = 0;
SELECT 1
FROM gmp_resource_instances ri,
cr_rsrc_dtl rd
WHERE rd.resources = p_resource
AND rd.organization_id = p_lab_organization_id
AND rd.resource_id = ri.resource_id
AND ri.instance_id = p_resource_instance
AND ri.inactive_ind = 0;
SELECT expression_ref_test_id
FROM gmd_qc_test_values_b
where test_id = p_exp_test_id;
SELECT t.test_id, t.test_code, t.expression, t.display_precision,
t.report_precision, r.result_id, r.result_value_num , r.test_replicate_cnt -- 14021970 added r.test_replicate_cnt
FROM gmd_samples s,
gmd_results r,
gmd_spec_results sr,
gmd_qc_tests_b t
WHERE s.sample_id = r.sample_id
AND s.sample_id = p_sample_id
AND s.retain_as IS NULL
AND r.result_id = sr.result_id
AND sr.event_spec_disp_id = p_event_spec_disp_id
AND NVL(sr.evaluation_ind, 'XX') NOT IN ('5O','4C')
AND r.test_id = t.test_id
AND t.test_type= 'E'
AND sr.delete_mark = 0
AND r.delete_mark = 0
--AND r.result_value_num IS NULL; -- Bug# 5195678
SELECT *
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id
;
SELECT ADDITIONAL_TEST_IND
FROM gmd_spec_results
WHERE event_spec_disp_id = p_event_spec_id
AND result_id = p_result_id
;
SELECT min_value_num,max_value_num
FROM gmd_qc_tests_b
WHERE test_id = p_test_id
;
SELECT gtmp.data_num,
t.test_code
FROM gmd_result_data_points_gt gtmp,
gmd_results r,
gmd_qc_tests_b t,
gmd_qc_test_values_b tv
WHERE gtmp.result_id = r.result_id
AND r.test_id = t.test_id
AND t.test_id = tv.expression_ref_test_id
AND tv.test_id = p_exp_test_id
;
SELECT display_precision, report_precision
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id
AND exclude_ind IS NULL
;
l_exptab.DELETE;
select count(*) INTO l_ref_count from gmd_qc_test_values_b where test_id=l_exp_test.test_id; --Bug#5097709
UPDATE gmd_results
SET result_date = NULL
WHERE result_id = l_exp_test.result_id
AND result_value_num IS NULL;
UPDATE gmd_spec_results
SET value_in_report_precision = l_report_value,
in_spec_ind = x_rec.in_spec,
action_code = x_rec.result_action_code,
evaluation_ind = x_rec.evaluation_ind
WHERE event_spec_disp_id = p_event_spec_disp_id
AND result_id = l_exp_test.result_id
;
UPDATE gmd_results
SET result_value_num = l_test_results(m).result_value_num,
result_date = SYSDATE
WHERE result_id = l_test_results(m).result_id;
l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
SELECT count(1)
INTO l_count
FROM gmd_spec_results sr, gmd_results r
WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = l_sample.sample_id
AND (((r.result_value_num IS NOT NULL OR r.result_value_char IS NOT NULL) AND
nvl(sr.evaluation_ind, 'XX') not in ( '4C')) OR
(r.result_value_num IS NULL AND r.result_value_char IS NULL AND
sr.evaluation_ind = '1Z'))
AND r.delete_mark = 0
;
SELECT count(1) INTO l_qlty_config_present
FROM gmd_quality_config
WHERE organization_id = l_sample.organization_id;
-- Select COUNT of Incomplete Tests in Sample WITH Spec
SELECT count(1)
INTO l_incomplete_count_from_spec
FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr
WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = l_sample.sample_id
AND st.spec_id = l_event_spec_disp.spec_id
AND st.test_id = r.test_id
AND st.exclude_ind IS NULL
AND st.optional_ind IS NULL -- Check only REQUIRED tests
AND (sr.evaluation_ind is NULL -- No Evaluation --> Incomplete
OR sr.evaluation_ind = '3E' -- Evaluation is 'Exp Error' --> Incomplete
OR ( r.result_value_num IS NULL
AND r.result_value_char IS NULL
AND sr.evaluation_ind not in ('4C', '5O', '1Z') -- Result not entered
)
)
AND r.delete_mark = 0 -- Check only active ones
;
/* SELECT count(1)
INTO l_incomplete_count_from_spec
FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = l_sample.sample_id
AND st.spec_id = l_event_spec_disp.spec_id
AND st.test_id = r.test_id
AND st.exclude_ind IS NULL
AND gc.organization_id = l_sample.organization_id
AND (
( (gc.include_optional_test_rslt_ind IS NULL OR gc.include_optional_test_rslt_ind='N') and st.optional_ind IS NULL) OR
(gc.include_optional_test_rslt_ind ='Y'
and st.optional_ind = 'Y' and (r.result_value_num IS NOT NULL OR r.result_value_char IS NOT NULL) -- 14349136 added this line
)
)
AND (sr.evaluation_ind is NULL -- No Evaluation --> Incomplete
OR sr.evaluation_ind = '3E' -- Evaluation is 'Exp Error' --> Incomplete
OR ( r.result_value_num IS NULL
AND r.result_value_char IS NULL
AND sr.evaluation_ind not in ('4C', '5O', '1Z') -- Result not entered
)
)
AND r.delete_mark = 0 ; -- Check only active ones
SELECT count(1)
INTO l_incomplete_count_from_spec
FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = l_sample.sample_id
AND st.spec_id = l_event_spec_disp.spec_id
AND st.test_id = r.test_id
AND st.exclude_ind IS NULL
AND gc.organization_id = l_sample.organization_id
AND (
( (gc.include_optional_test_rslt_ind IS NULL OR gc.include_optional_test_rslt_ind='N') and st.optional_ind IS NULL) OR
(gc.include_optional_test_rslt_ind ='Y'
and ((st.optional_ind = 'Y' and (r.result_value_num IS NOT NULL OR r.result_value_char IS NOT NULL))
OR (NVL(st.optional_ind, 'N') = 'N' and r.result_value_num IS NULL AND r.result_value_char IS NULL) -- 14349136 added this line reworked for 15885923
))
)
AND (sr.evaluation_ind is NULL
OR sr.evaluation_ind = '3E'
OR ( r.result_value_num IS NULL
AND r.result_value_char IS NULL
AND sr.evaluation_ind not in ('4C', '5O', '1Z') -- Result not entered
)
)
AND r.delete_mark = 0 ; -- Check only active ones
SELECT count(1)
INTO l_incomplete_count_from_spec
FROM gmd_results r, gmd_spec_tests st, gmd_spec_results sr,gmd_quality_config gc
WHERE sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = l_sample.sample_id
AND st.spec_id = l_event_spec_disp.spec_id
AND st.test_id = r.test_id
AND st.exclude_ind IS NULL
AND gc.organization_id = l_sample.organization_id
AND NVL(st.optional_ind, 'N') = 'N' -- Check only REQUIRED tests
-- 15885923 new bug 15942047 rework
AND
(sr.evaluation_ind is NULL -- No Evaluation --> Incomplete
OR sr.evaluation_ind = '3E' -- Evaluation is 'Exp Error' --> Incomplete
OR ( r.result_value_num IS NULL
AND r.result_value_char IS NULL
AND sr.evaluation_ind not in ('4C', '5O', '1Z') -- Result not entered
)
)
AND r.delete_mark = 0 ;
SELECT count(1)
INTO l_incomplete_count_additional
FROM gmd_results r, gmd_spec_results sr
WHERE sr.result_id = r.result_id
AND sr.event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
AND r.sample_id = l_sample.sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind is NULL
OR sr.evaluation_ind = '3E'
OR ( r.result_value_num IS NULL
AND r.result_value_char IS NULL
AND sr.evaluation_ind not in ('4C', '5O')
)
)
AND r.delete_mark = 0
;
l_update_disp_rec.sample_id := l_sample.sample_id;
l_update_disp_rec.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
l_update_disp_rec.no_of_samples_for_event := l_sampling_event.sample_req_cnt;
l_update_disp_rec.curr_disposition := l_sample_spec_disp.disposition;
gmd_debug.put_line (' Call GMD_SAMPLES_GRP.update_sample_comp_disp '|| l_sample_spec_disp.disposition);
GMD_SAMPLES_GRP.update_sample_comp_disp(
p_update_disp_rec => l_update_disp_rec
, p_to_disposition => l_change_disp_to
, x_return_status => x_return_status
, x_message_data => x_message_data);
SELECT se.sampling_event_id,
se.sample_active_cnt,
se.sample_req_cnt,
esd.event_spec_disp_id,
esd.spec_id,
esd.spec_vr_id,
ssd.disposition,
svr.control_lot_attrib_ind,
svr.in_spec_lot_status_id,
svr.out_of_spec_lot_status_id,
s.organization_id
FROM gmd_sampling_events se,
gmd_event_spec_disp esd,
gmd_sample_spec_disp ssd,
gmd_samples s,
gmd_com_spec_vrs_vl svr
WHERE s.sample_id = p_sample_id
AND s.sampling_event_id = se.sampling_event_id
AND se.sampling_event_id = esd.sampling_event_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND esd.event_spec_disp_id = ssd.event_spec_disp_id
AND ssd.sample_id = s.sample_id
AND svr.spec_vr_id = esd.spec_vr_id
AND s.delete_mark = 0
AND esd.delete_mark = 0
AND ssd.delete_mark = 0
;
l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
SELECT include_optional_test_rslt_ind
FROM gmd_quality_config
WHERE organization_id = p_org_id;
SELECT st.optional_ind
FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = p_event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_ssample_id
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id;
SELECT count(1)
INTO l_count_with_spec
FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_sample_id
AND (sr.evaluation_ind IS NULL OR
sr.evaluation_ind NOT IN ('0A', '4C', '5O', '1Z')
)
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id
AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR
(l_include_optional = 'Y' and (r.result_value_num IS NOT NULL OR
r.result_value_char IS NOT NULL)
)
)
;
SELECT count(1)
INTO l_count_wo_spec
FROM gmd_results r, gmd_spec_results sr
WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind IS NULL OR
sr.evaluation_ind NOT IN ('0A', '4C', '5O')
)
AND r.delete_mark = 0
AND sr.delete_mark = 0
;
SELECT count(1)
INTO l_count_with_spec
FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
gmd_spec_tests_b st
WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
AND esd.event_spec_disp_id = sr.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_sample_id
AND (sr.evaluation_ind IS NULL OR
sr.evaluation_ind NOT IN ('0A', '1V', '4C', '5O', '1Z')
)
AND r.delete_mark = 0
AND sr.delete_mark = 0
AND esd.spec_id = st.spec_id
AND st.test_id = r.test_id
AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR
(l_include_optional = 'Y' and (r.result_value_num IS NOT NULL OR
r.result_value_char IS NOT NULL)
)
)
;
SELECT count(1)
INTO l_count_wo_spec
FROM gmd_results r, gmd_spec_results sr
WHERE sr.event_spec_disp_id = l_sample_rec.event_spec_disp_id
AND sr.result_id = r.result_id
AND r.sample_id = p_sample_id
AND sr.additional_test_ind = 'Y'
AND (sr.evaluation_ind IS NULL OR
sr.evaluation_ind NOT IN ('0A', '1V', '4C', '5O', '1Z')
)
AND r.delete_mark = 0
AND sr.delete_mark = 0
;
l_update_disp_rec.sample_id := p_sample_id;
l_update_disp_rec.event_spec_disp_id := l_sample_rec.event_spec_disp_id;
l_update_disp_rec.no_of_samples_for_event := l_sample_rec.sample_req_cnt;
l_update_disp_rec.curr_disposition := l_sample_rec.disposition;
gmd_debug.put_line (' Call GMD_SAMPLES_GRP.update_sample_comp_disp '|| l_change_disp_to);
GMD_SAMPLES_GRP.update_sample_comp_disp(
p_update_disp_rec => l_update_disp_rec
, p_to_disposition => l_change_disp_to
, x_return_status => x_return_status
, x_message_data => l_message_data);
SELECT disposition
INTO l_disposition
FROM gmd_sampling_events
WHERE sampling_event_id = l_sample_rec.sampling_event_id
;
SELECT esd.disposition,
svr.control_lot_attrib_ind,
svr.in_spec_lot_status_id,
svr.out_of_spec_lot_status_id,
svr.organization_id
FROM gmd_event_spec_disp esd,
gmd_com_spec_vrs_vl svr,
gmd_composite_spec_disp csd
WHERE esd.event_spec_disp_id = p_event_spec_disp_id
AND esd.spec_used_for_lot_attrib_ind = 'Y'
AND svr.spec_vr_id = esd.spec_vr_id
AND esd.delete_mark = 0
;
SELECT transaction_reason_id
FROM gmd_quality_config
WHERE organization_id = p_organization_id
AND transaction_reason_id IS NOT NULL;
l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
SELECT include_optional_test_rslt_ind
FROM gmd_quality_config
WHERE organization_id = p_org_id;
SELECT count(1)
INTO l_count_with_spec
FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
gmd_composite_results cr, gmd_spec_tests_b st
WHERE csd.event_spec_disp_id = l_event_spec_disp_id
AND csd.latest_ind = 'Y'
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
AND cr.in_spec_ind IS NULL -- Result is out-of-spec
AND st.spec_id = esd.spec_id
AND st.test_id = cr.test_id
AND ((l_include_optional = 'N' and st.optional_ind IS NULL) OR
(l_include_optional = 'Y' and (cr.mean IS NOT NULL or cr.mode_char IS NOT NULL))
)
;
SELECT count(1)
INTO l_count_wo_spec
FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
gmd_composite_results cr
WHERE csd.event_spec_disp_id = l_event_spec_disp_id
AND csd.latest_ind = 'Y'
AND csd.event_spec_disp_id = esd.event_spec_disp_id
AND csd.composite_spec_disp_id = cr.composite_spec_disp_id
AND cr.in_spec_ind IS NULL -- Result is out-of-spec
AND cr.test_id NOT IN
(SELECT st.test_id
FROM gmd_spec_tests_b st
WHERE st.spec_id = esd.spec_id)
;
SELECT composite_spec_disp_id
INTO l_composite_spec_disp_id
FROM gmd_composite_spec_disp csd
WHERE csd.event_spec_disp_id = l_event_spec_disp_id
AND csd.latest_ind = 'Y'
;
l_update_disp_rec.composite_spec_disp_id := l_composite_spec_disp_id;
l_update_disp_rec.event_spec_disp_id := l_event_spec_disp_id;
l_update_disp_rec.no_of_samples_for_event := l_sampling_event.sample_req_cnt;
l_update_disp_rec.sampling_event_id := l_sampling_event.sampling_event_id;
l_update_disp_rec.curr_disposition := l_spec_dtl.disposition;
gmd_debug.put_line (' Call GMD_SAMPLES_GRP.update_sample_comp_disp '|| l_change_disp_to);
GMD_SAMPLES_GRP.update_sample_comp_disp(
p_update_disp_rec => l_update_disp_rec
, p_to_disposition => l_change_disp_to
, x_return_status => l_return_status
, x_message_data => l_message_data);
SELECT organization_id
INTO l_organization_id
FROM gmd_samples
WHERE sampling_event_id = l_sampling_event.sampling_event_id
AND rownum = 1 ;
gmd_samples_grp.update_lot_grade_batch( --nsrivast
p_sample_id => NULL
, p_composite_spec_disp_id => l_composite_spec_disp_id
, p_to_lot_status_id => l_lot_status_id
, p_from_lot_status_id => NULL --l_lot_status_id
, p_to_grade_code => NULL
, p_from_grade_code => NULL
, p_to_qc_status => NULL
, p_reason_id => l_reason_id
, p_hold_date => NULL
, x_return_status => x_return_status
, x_message_data => l_message_data );
PROCEDURE update_exptest_value_null
(p_exp_ref_test_id IN gmd_qc_tests_b.test_id%TYPE
, p_sample_id IN gmd_samples.sample_id%TYPE
, p_event_spec_disp_id IN gmd_sample_spec_disp.event_spec_disp_id%TYPE
, x_return_status OUT NOCOPY VARCHAR2
)
--Start of comments
--+========================================================================+
--| API Name : update_exptest_value_null |
--| TYPE : Group |
--| Notes : This procedure takes the sample information and |
--| test_id, and updates the result of latest replicates |
--| of all the Expression tests of the current sample, |
--| which use the given test as dependent test, to NULL. | |
--| |
--| HISTORY |
--| Ravi Boddu 31-Dec-2004 Created. |
--| |
--+========================================================================+
-- End of comments
IS
l_rslt_tbl gmd_results_grp.rslt_tbl;
SELECT DISTINCT test_id
FROM gmd_qc_test_values_b val
WHERE expression_ref_test_id = l_exp_ref_test_id ;
SELECT r.result_id
FROM gmd_results r, gmd_spec_results sr
WHERE r.result_id = sr.result_id
AND r.sample_id = l_sample_id
AND sr.event_spec_disp_id = l_event_spec_disp_id
AND NVL(sr.evaluation_ind, 'XX') NOT IN ('50' ,'4C')
AND sr.delete_mark = 0
AND r.delete_mark = 0
AND r.test_id = l_test_id
ORDER BY NVL(r.result_date,l_start_date) DESC , r.test_replicate_cnt DESC;
gmd_debug.put_line('Entering procedure update_exptest_value_null');
UPDATE gmd_results SET result_value_num = NULL WHERE result_id = l_result_id;
GMD_API_PUB.Log_Message('GMD_API_ERROR','PACKAGE','update_exptest_value_null','ERROR', SUBSTR(SQLERRM,1,100));
END update_exptest_value_null;
EXECUTE IMMEDIATE 'delete from gmd_result_data_points_gt';
'INSERT INTO gmd_result_data_points_gt (result_id, test_id, exclude_ind, data_num, data_char)'
||' ('
||' SELECT result_id, test_id, 0, result_value_num, result_value_char FROM'
||' ('
||' SELECT r.result_id, r.test_id, r.result_value_num, r.result_value_char,'
||' r.result_date, r.test_replicate_cnt'
||' FROM gmd_results r, gmd_spec_results sr, gmd_samples s'
||' WHERE r.result_id = sr.result_id'
||' AND r.sample_id = :l_sample_id'
||' AND r.test_replicate_cnt = :l_test_replicate_cnt'
||' AND sr.event_spec_disp_id = :l_event_spec_disp_id'
||' AND nvl(sr.evaluation_ind, ' || '''' || 'XX' || '''' || ') not in ('
|| '''' || '5O' || '''' ||','|| '''' || '4C' || '''' ||')'
||' AND sr.delete_mark = 0'
||' AND r.delete_mark = 0'
||' AND r.sample_id = s.sample_id'
||' AND s.retain_as IS NULL'
||' )'
||')'
;