[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT TEST_CODE
FROM gmd_qc_tests_b
WHERE test_id = p_test_id;
SELECT distinct r1.seq,
r1.test_id,
r1.test_method_id
FROM gmd_results r1
WHERE r1.sample_id = p_child_sample_id
AND r1.result_value_char IS NULL
AND r1.result_value_num IS NULL
AND r1.reserve_sample_id IS NULL
and r1.delete_mark = 0
AND r1.test_id IN (SELECT distinct r2.test_id
FROM gmd_results r2,
gmd_spec_results sr
WHERE r2.sample_id = p_parent_sample_id
AND r2.result_id = sr.result_id
AND r2.test_method_id = r1.test_method_id
AND r2.delete_mark = 0
AND sr.evaluation_ind IN ('0A','1V','2R','3E'))
ORDER BY r1.seq; --RLNAGARA bug5197746 added the ORDER BY clause
SELECT r.result_id,
r.TEST_REPLICATE_CNT
FROM gmd_results r
WHERE r.sample_id = p_child_sample_id
AND r.test_id = p_test_id
AND r.test_method_id = p_test_method_id
AND r.result_value_char IS NULL
AND r.result_value_num IS NULL
AND r.reserve_sample_id IS NULL
AND r.delete_mark = 0;
SELECT r.result_id,
r.test_replicate_cnt,
r.result_value_char,
r.result_value_num,
r.result_date,
r.test_method_id
FROM gmd_results r,
gmd_spec_results sr,
gmd_sample_spec_disp ssd,
gmd_event_spec_disp esd
WHERE r.sample_id = p_parent_sample_id
AND r.test_id = p_test_id
AND r.test_method_id = p_test_method_id
AND r.result_id = sr.result_id
AND sr.evaluation_ind IN ('0A','1V','2R','3E')
AND sr.event_spec_disp_id = ssd.event_spec_disp_id
AND r.sample_id = ssd.sample_id
AND ssd.event_spec_disp_id = esd.event_spec_disp_id
AND esd.SPEC_USED_FOR_LOT_ATTRIB_IND = 'Y'
AND r.delete_mark = 0
ORDER by r.result_date desc;
select r.composite_result_id,
r.test_id test_id,
nvl(to_char(r.mean),r.mode_char) result,
r.mean,
r.mode_num,
r.mode_char,
r.low_num,
r.high_num,
r.range,
r.non_validated_result,
r.standard_deviation , ges.spec_id,
r.creation_date
from gmd_composite_results r,
gmd_composite_spec_disp sd,
gmd_event_spec_disp ges
where r.composite_spec_disp_id =sd.composite_spec_disp_id
--and sd.event_spec_disp_id = 1
and sd.event_spec_disp_id = ges.event_spec_disp_id
and nvl(ges.spec_used_for_lot_attrib_ind,'N') = 'Y'
and nvl(sd.latest_ind,'N') = 'Y'
and ges.sampling_event_id = p_parent_id
and r.test_id in (select r1.test_id
from gmd_composite_results r1,
gmd_composite_spec_disp gcs,
gmd_event_spec_disp ges
where ges.event_spec_disp_id = gcs.event_spec_disp_id
and gcs.latest_ind = 'Y'
and gcs.composite_spec_disp_id = r1.composite_spec_disp_id
and ges.sampling_event_id = p_child_id
and r1.mean IS NULL AND r1.mode_char IS NULL);
select r1.composite_result_id
from gmd_composite_results r1,
gmd_composite_spec_disp gcs,
gmd_event_spec_disp ges
where ges.event_spec_disp_id = gcs.event_spec_disp_id
and gcs.latest_ind = 'Y'
and gcs.composite_spec_disp_id = r1.composite_spec_disp_id
and ges.sampling_event_id = p_sampling_event_id
and r1.test_id = p_test_id
and r1.mean IS NULL AND r1.mode_char IS NULL;
DELETE gmd_result_transfer_gt;
SELECT 'Y'
INTO l_prev_used
FROM gmd_results
WHERE sample_id = p_child_id
and parent_result_id = c_get_parent_sample_row.result_id
AND rownum =1;
--insert statement for temp table
IF (l_prev_used = 'N') THEN
l_common_test_count := l_common_test_count +1;
INSERT INTO gmd_result_transfer_gt
(parent_result_id,
child_result_id,
test_code,
result,
result_date,
parent_replicate,
child_replicate)
VALUES(c_get_parent_sample_row.result_id,
c_get_child_sample_row.result_id,
l_test_code,
nvl(c_get_parent_sample_row.result_value_char,c_get_parent_sample_row.result_value_num),
c_get_parent_sample_row.result_date,
c_get_parent_sample_row.test_replicate_cnt,
c_get_child_sample_row.test_replicate_cnt);
--INSERT Statement
l_test_code := get_test_code(c_get_composite_test_row.test_id);
INSERT INTO gmd_result_transfer_gt
(parent_result_id,
child_result_id,
test_code,
result,
result_date,
parent_replicate,
child_replicate)
VALUES(c_get_composite_test_row.composite_result_id,
l_child_composite_result_id,
l_test_code,
c_get_composite_test_row.result,
c_get_composite_test_row.creation_date,
NULL,
NULL);
SELECT a.parent_result_id,
a.child_result_id,
b.result_value_char,
b.result_value_num,
nvl(b.result_value_char,b.result_value_num) result,
b.result_date,
a.child_replicate,
b.lab_organization_id,
b.tester,
b.tester_id,
b.test_id,
b.text_code,
b.ATTRIBUTE_CATEGORY,
b.ATTRIBUTE1,
b.ATTRIBUTE2,
b.ATTRIBUTE3,
b.ATTRIBUTE4,
b.ATTRIBUTE5,
b.ATTRIBUTE6,
b.ATTRIBUTE7,
b.ATTRIBUTE8,
b.ATTRIBUTE9,
b.ATTRIBUTE10,
b.ATTRIBUTE11,
b.ATTRIBUTE12,
b.ATTRIBUTE13,
b.ATTRIBUTE14,
b.ATTRIBUTE15,
b.ATTRIBUTE16,
b.ATTRIBUTE17,
b.ATTRIBUTE18,
b.ATTRIBUTE19,
b.ATTRIBUTE20,
b.ATTRIBUTE21,
b.ATTRIBUTE22,
b.ATTRIBUTE23,
b.ATTRIBUTE24,
b.ATTRIBUTE25,
b.ATTRIBUTE26,
b.ATTRIBUTE27,
b.ATTRIBUTE28,
b.ATTRIBUTE29,
b.ATTRIBUTE30
FROM GMD_RESULT_TRANSFER_GT A,
gmd_results b
WHERE b.result_id = a.parent_result_id
;
SELECT test_code, test_unit,b.qcunit_desc,
test_class, test_type,
min_value_num, max_value_num, test_desc,
exp_error_type, below_spec_min, above_spec_min,
below_spec_max, above_spec_max,
below_min_action_code, above_min_action_code,
below_max_action_code, above_max_action_code,
priority,
t.display_precision, t.report_precision,
t.expression, tm.resources
FROM gmd_qc_tests t,gmd_units b, gmd_test_methods_b tm
WHERE t.test_id = p_test_id
AND t.test_method_id = tm.test_method_id
AND t.test_unit = b.qcunit_code (+) ;
select a.parent_result_id,
a.child_result_id,
r.test_id,
nvl(to_char(r.mean),r.mode_char) result,
r.mean,
r.mode_num,
r.mode_char,
r.median_num,
r.median_char,
r.low_num,
r.high_num,
r.range,
r.non_validated_result,
r.standard_deviation,
r.text_code,
r.ATTRIBUTE_CATEGORY,
r.ATTRIBUTE1,
r.ATTRIBUTE2,
r.ATTRIBUTE3,
r.ATTRIBUTE4,
r.ATTRIBUTE5,
r.ATTRIBUTE6,
r.ATTRIBUTE7,
r.ATTRIBUTE8,
r.ATTRIBUTE9,
r.ATTRIBUTE10,
r.ATTRIBUTE11,
r.ATTRIBUTE12,
r.ATTRIBUTE13,
r.ATTRIBUTE14,
r.ATTRIBUTE15,
r.ATTRIBUTE16,
r.ATTRIBUTE17,
r.ATTRIBUTE18,
r.ATTRIBUTE19,
r.ATTRIBUTE20,
r.ATTRIBUTE21,
r.ATTRIBUTE22,
r.ATTRIBUTE23,
r.ATTRIBUTE24,
r.ATTRIBUTE25,
r.ATTRIBUTE26,
r.ATTRIBUTE27,
r.ATTRIBUTE28,
r.ATTRIBUTE29,
r.ATTRIBUTE30
FROM gmd_composite_results r,
GMD_RESULT_TRANSFER_GT A
WHERE r.composite_result_id = a.parent_result_id;
select retest_action_code
, resample_action_code
from gmd_quality_config
where organization_id = (select organization_id
from gmd_samples
where sample_id =p_sample_id)
order by orgn_code;
l_update_instance_id NUMBER;
SELECT spec_id,
event_spec_disp_id
INTO l_spec_id,
l_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';
select GMD_QC_UPDATE_INST_ID_S.NEXTVAL
into l_update_instance_id
from dual;
UPDATE gmd_results
SET result_value_char = temp_table_rslt_row.result_value_char,
result_value_num = temp_table_rslt_row.result_value_num,
update_instance_id = l_update_instance_id,
result_date = temp_table_rslt_row.result_date,
lab_organization_id = temp_table_rslt_row.lab_organization_id,
tester_id = temp_table_rslt_row.tester_id,
tester = temp_table_rslt_row.tester,
parent_result_id = temp_table_rslt_row.parent_result_id,
last_update_date = sysdate,
last_updated_by = fnd_global.USER_ID,
text_code = decode(p_copy_edit_text,'Y',
temp_table_rslt_row.text_code,text_code),
attribute_category = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute_category, attribute_category),
attribute1 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute1, attribute1),
attribute2 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute2, attribute2),
attribute3 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute3, attribute3),
attribute4 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute4, attribute4),
attribute5 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute5, attribute5),
attribute6 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute6, attribute6),
attribute7 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute7, attribute7),
attribute8 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute8, attribute8),
attribute9 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute9, attribute9),
attribute10 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute10, attribute10),
attribute11 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute11, attribute11),
attribute12 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute12, attribute12),
attribute13 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute13, attribute13),
attribute14 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute14, attribute14),
attribute15 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute15, attribute15),
attribute16 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute16, attribute16),
attribute17 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute17, attribute17),
attribute18 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute18, attribute18),
attribute19 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute19, attribute19),
attribute20 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute20, attribute20),
attribute21 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute21, attribute21),
attribute22 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute22, attribute22),
attribute23 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute23, attribute23),
attribute24 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute24, attribute24),
attribute25 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute25, attribute25),
attribute26 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute26, attribute26),
attribute27 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute27, attribute27),
attribute28 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute28, attribute28),
attribute29 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute29, attribute29),
attribute30 = decode(p_copy_flex,'Y',temp_table_rslt_row.attribute30, attribute30)
WHERE result_id = temp_table_rslt_row.child_result_id;
UPDATE GMD_SPEC_RESULTS
SET IN_SPEC_IND = l_validate_res.in_spec,
evaluation_ind = l_validate_res.evaluation_ind,
action_code = l_validate_res.result_action_code,
last_update_date = SYSDATE,
last_updated_by = fnd_global.user_id
WHERE event_spec_disp_id = l_event_spec_disp_id
AND result_id = temp_table_rslt_row.child_result_id;
-- Write code for inserting the test row.
NULL;
select test_qty,test_qty_uom
into l_test_qty, l_test_qty_uom
from gmd_results
where result_id = temp_table_rslt_row.child_result_id;
SELECT inventory_item_id, lot_number
INTO l_inventory_item_id, l_lot_number
FROM gmd_samples
WHERE sample_id = p_sample_id;
gmd_api_pub.raise (P_EVENT_NAME => 'oracle.apps.gmi.lotretestdate.update',
P_EVENT_KEY => to_char(l_inventory_item_id)
||'-'|| l_lot_number);
SELECT sample_active_cnt
INTO l_sample_active_cnt
FROM gmd_sampling_events gse
WHERE gse.sampling_event_id=p_sampling_event_id;
UPDATE gmd_composite_results
SET in_spec_ind = l_in_spec,
mean= c_temp_table_cmpt_row.mean ,
mode_num= c_temp_table_cmpt_row.mode_num,
mode_char = c_temp_table_cmpt_row.mode_char,
median_char = c_temp_table_cmpt_row.median_char,
median_num = c_temp_table_cmpt_row.median_num,
low_num= c_temp_table_cmpt_row.low_num,
high_num= c_temp_table_cmpt_row.high_num,
range= c_temp_table_cmpt_row.range,
non_validated_result = c_temp_table_cmpt_row.non_validated_result,
standard_deviation = c_temp_table_cmpt_row.standard_deviation,
last_update_date = SYSDATE,
last_updated_by = fnd_global.USER_ID,
text_code = decode(p_copy_edit_text,'Y',
c_temp_table_cmpt_row.text_code,text_code),
parent_composite_result_id = c_temp_table_cmpt_row.parent_result_id,
attribute_category = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute_category, attribute_category),
attribute1 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute1, attribute1),
attribute2 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute2, attribute2),
attribute3 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute3, attribute3),
attribute4 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute4, attribute4),
attribute5 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute5, attribute5),
attribute6 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute6, attribute6),
attribute7 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute7, attribute7),
attribute8 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute8, attribute8),
attribute9 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute9, attribute9),
attribute10 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute10, attribute10),
attribute11 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute11, attribute11),
attribute12 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute12, attribute12),
attribute13 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute13, attribute13),
attribute14 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute14, attribute14),
attribute15 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute15, attribute15),
attribute16 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute16, attribute16),
attribute17 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute17, attribute17),
attribute18 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute18, attribute18),
attribute19 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute19, attribute19),
attribute20 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute20, attribute20),
attribute21 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute21, attribute21),
attribute22 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute22, attribute22),
attribute23 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute23, attribute23),
attribute24 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute24, attribute24),
attribute25 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute25, attribute25),
attribute26 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute26, attribute26),
attribute27 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute27, attribute27),
attribute28 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute28, attribute28),
attribute29 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute29, attribute29),
attribute30 = decode(p_copy_flex,'Y',c_temp_table_cmpt_row.attribute30, attribute30)
WHERE composite_result_id = c_temp_table_cmpt_row.child_result_id;
SELECT a.event_spec_disp_id,r.in_spec_ind,r.parent_composite_result_id,
r.composite_result_id,
r.composite_spec_disp_id,
r.test_id,
nvl(to_char(r.mean),r.mode_char) result,
r.mean,
r.mode_num,
r.mode_char,
r.low_num,
r.high_num,
r.range,
r.non_validated_result,
r.standard_deviation,
r.text_code,
r.ATTRIBUTE_CATEGORY,
r.ATTRIBUTE1,
r.ATTRIBUTE2,
r.ATTRIBUTE3,
r.ATTRIBUTE4,
r.ATTRIBUTE5,
r.ATTRIBUTE6,
r.ATTRIBUTE7,
r.ATTRIBUTE8,
r.ATTRIBUTE9,
r.ATTRIBUTE10,
r.ATTRIBUTE11,
r.ATTRIBUTE12,
r.ATTRIBUTE13,
r.ATTRIBUTE14,
r.ATTRIBUTE15,
r.ATTRIBUTE16,
r.ATTRIBUTE17,
r.ATTRIBUTE18,
r.ATTRIBUTE19,
r.ATTRIBUTE20,
r.ATTRIBUTE21,
r.ATTRIBUTE22,
r.ATTRIBUTE23,
r.ATTRIBUTE24,
r.ATTRIBUTE25,
r.ATTRIBUTE26,
r.ATTRIBUTE27,
r.ATTRIBUTE28,
r.ATTRIBUTE29,
r.ATTRIBUTE30
FROM gmd_composite_results r,
gmd_composite_spec_disp a --Bug 3017743, added to get event spec disp id.
WHERE r.composite_spec_disp_id = p_composite_spec_disp_id
AND r.composite_spec_disp_id = a.composite_spec_disp_id
AND r.parent_composite_result_id IS NOT NULL
;
SELECT composite_result_id
FROM gmd_composite_results a, gmd_composite_spec_disp b
WHERE a.composite_spec_disp_id = b.composite_spec_disp_id
AND b.latest_ind = 'Y'
AND b.event_spec_disp_id = p_event_spec_disp_id
AND a.test_id = p_test_id
AND (( a.mode_char IS NULL) OR (a.mean IS NULL));
UPDATE gmd_composite_results
SET in_spec_ind = l_previous_row.in_spec_ind,
mean= l_previous_row.mean ,
mode_num= l_previous_row.mode_num,
mode_char = l_previous_row.mode_char,
low_num= l_previous_row.low_num,
high_num= l_previous_row.high_num,
range= l_previous_row.range,
non_validated_result = l_previous_row.non_validated_result,
standard_deviation = l_previous_row.standard_deviation,
last_update_date = SYSDATE,
last_updated_by = fnd_global.USER_ID,
text_code =l_previous_row.text_code,
parent_composite_result_id = l_previous_row.parent_composite_result_id,
attribute_category = l_previous_row.attribute_category,
attribute1 = l_previous_row.attribute1,
attribute2 = l_previous_row.attribute2,
attribute3 = l_previous_row.attribute3,
attribute4 = l_previous_row.attribute4,
attribute5 = l_previous_row.attribute5,
attribute6 = l_previous_row.attribute6,
attribute7 = l_previous_row.attribute7,
attribute8 = l_previous_row.attribute8,
attribute9 = l_previous_row.attribute9,
attribute10 = l_previous_row.attribute10,
attribute11 = l_previous_row.attribute11,
attribute12 = l_previous_row.attribute12,
attribute13 = l_previous_row.attribute13,
attribute14 = l_previous_row.attribute14,
attribute15 = l_previous_row.attribute15,
attribute16 = l_previous_row.attribute16,
attribute17 = l_previous_row.attribute17,
attribute18 = l_previous_row.attribute18,
attribute19 = l_previous_row.attribute19,
attribute20 = l_previous_row.attribute20,
attribute21 = l_previous_row.attribute21,
attribute22 = l_previous_row.attribute22,
attribute23 = l_previous_row.attribute23,
attribute24 = l_previous_row.attribute24,
attribute25 = l_previous_row.attribute25,
attribute26 = l_previous_row.attribute26,
attribute27 = l_previous_row.attribute27,
attribute28 = l_previous_row.attribute28,
attribute29 = l_previous_row.attribute29,
attribute30 = l_previous_row.attribute30
WHERE composite_result_id = l_curr_compo_rec.composite_result_id;
PROCEDURE delete_single_composite(p_composite_spec_disp_id NUMBER,
x_message_count OUT NOCOPY NUMBER,
x_message_data OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_place NUMBER;
delete gmd_composite_result_assoc
where composite_result_id IN
(select composite_result_id
from gmd_composite_results
where composite_spec_disp_id = p_composite_spec_disp_id);
delete gmd_composite_results
where composite_spec_disp_id = p_composite_spec_disp_id;
delete gmd_composite_spec_disp
where composite_spec_disp_id = p_composite_spec_disp_id;
log_msg('GMD_RESULT_TRANSFER_GRP.delete_single_composite AT '||l_place||' '|| SUBSTR(SQLERRM,1,100));
END delete_single_composite;