The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT 'X' INTO l_exists
FROM GMD_QC_TESTS_B
WHERE test_id = p_test_id ;
SELECT 'X' INTO l_exists
FROM GMD_QC_TESTS_B
WHERE test_code = p_test_code ;
SELECT 'Y' FROM GMD_SPEC_TESTS_B gst ,GMD_SPECIFICATIONS_B gs
WHERE gst.test_id = p_test_id
AND gst.spec_id = gs.spec_id
AND gs.delete_mark = 0 ;
ELSIF l_test_rec.delete_mark = 1 OR l_test_rec.test_type IN ('U','V','T','E') THEN
FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_TEST_EXP');
PROCEDURE NAME: insert_exp_test_values
===========================================================================*/
PROCEDURE insert_exp_test_values(
p_init_msg_list IN VARCHAR2 ,
p_test_id IN NUMBER,
p_test_id_tab IN exp_test_id_tab_type,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
DELETE from GMD_QC_TEST_VALUES_TL
WHERE test_value_id IN
(SELECT test_value_id FROM gmd_qc_test_values_b
WHERE test_id = p_test_id );
DELETE from GMD_QC_TEST_VALUES_B
WHERE test_id = p_test_id;
GMD_QC_TEST_VALUES_PVT.INSERT_ROW(
X_ROWID => l_rowid,
X_TEST_ID => p_test_id,
X_TEST_VALUE_ID => l_test_value_id,
X_EXPRESSION_REF_TEST_ID => p_test_id_tab(i),
X_DISPLAY_LABEL_NUMERIC_RANGE => 'EXPRESSION');
FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.INSERT_EXP_TEST_VALUES' );
END insert_exp_test_values;
SELECT display_precision INTO l_test_method_precision
FROM GMD_TEST_METHODS_B
WHERE test_method_id = p_test_method_id ;
SELECT 'X' INTO l_temp
FROM fnd_lookup_values
WHERE lookup_type = 'GMD_QC_TEST_PRIORITY'
AND lookup_code = p_test_priority
AND language = userenv('LANG') ;
SELECT 'x' FROM GMD_TEST_METHODS_B
WHERE test_method_id = p_test_method_id
AND delete_mark = 0;
SELECT 'x' FROM GMD_TEST_CLASSES_B
WHERE test_class = p_test_class
AND delete_mark = 0;
SELECT 'x' FROM GMD_UNITS_B
WHERE qcunit_code = p_qcunit_code
AND delete_mark = 0;
SELECT 'x' FROM GMD_ACTIONS_B
WHERE action_code = p_action_code
AND delete_mark = 0;
x_gmd_qc_tests_rec.delete_mark := 0;
PROCEDURE VALIDATE_BEFORE_INSERT(
p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
x_gmd_qc_tests_rec OUT NOCOPY GMD_QC_TESTS%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.validate_before_insert');
END validate_before_insert ;
PROCEDURE NAME: process_after_insert
DESCRIPTION: This procedure inserts records into test values for expression
test data type.
===========================================================================*/
PROCEDURE PROCESS_AFTER_INSERT (
p_init_msg_list IN VARCHAR2 ,
p_gmd_qc_tests_rec IN GMD_QC_TESTS%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2,
x_message_data OUT NOCOPY VARCHAR2) IS
l_progress VARCHAR2(3);
GMD_QC_TESTS_GRP.INSERT_EXP_TEST_VALUES(
p_test_id => p_gmd_qc_tests_rec.test_id,
p_test_id_tab => l_exp_test_id_tab,
x_return_status => x_return_status,
x_message_data => x_message_data);
FND_MESSAGE.Set_Token('PACKAGE','GMD_QC_TESTS_GRP.process_after_insert');
END process_after_insert ;
SELECT 'X' INTO l_exists
FROM GMD_QC_TESTS_B
WHERE test_class = p_test_class
AND test_group_order = p_test_group_order
AND rownum =1 ;
RLNAGARA B5099998 23-Mar-2006 Added the order by clause in the insert statement so that the
tests are inserted in the table according to the given order.
===========================================================================*/
PROCEDURE POPULATE_TEST_GRP_GT(p_test_class IN varchar2,
p_spec_id IN NUMBER default NULL,
p_sample_id IN NUMBER default NULL,
x_return_status OUT NOCOPY VARCHAR2) IS
CURSOR c_get_spec_id(p_sample_id NUMBER) IS
select c.spec_id
from gmd_samples a,
gmd_sampling_events b,
gmd_event_spec_disp c
where a.sample_id = p_sample_id
and a.sampling_event_id = b.sampling_event_id
and a.sampling_event_id = c.sampling_event_id
and nvl(c.spec_used_for_lot_attrib_ind,'N') ='Y';
select test_id
from gmd_test_group_gt;
delete gmd_test_group_gt;
INSERT INTO GMD_TEST_GROUP_GT
(TEST_GROUP_ORDER,
TEST_CODE,
TEST_ID,
TEST_DESC,
TEST_TYPE,
TEST_QTY,
TEST_QTY_UOM, --rconv
USED_IN_SPEC,
INCLUDE)
SELECT a.TEST_GROUP_ORDER test_group_order,
a.TEST_CODE test_code,
a.TEST_ID,
a.TEST_DESC test_desc,
b.meaning test_type,
c.test_qty,
c.test_qty_uom, --rconv
'N',
'Y'
FROM GMD_QC_TESTS a, gem_lookups b , gmd_test_methods c
where a.test_class = p_test_class
AND b.lookup_type = 'GMD_QC_TEST_TYPE'
AND a.test_type = b.lookup_code
AND a.test_method_id = c.test_method_id
ORDER BY test_group_order; --RLNAGARA B5099998 Added this ORDER BY Clause.
UPDATE gmd_test_group_gt
SET used_in_spec = 'Y', include = 'N'
WHERE test_id IN (SELECT test_id
FROM gmd_spec_tests
WHERE spec_id = p_spec_id);
SELECT nvl(a.test_qty_uom, c.test_qty_uom) test_qty_uom,
nvl(a.test_qty, c.test_qty) test_qty
INTO l_test_qty_uom, l_test_qty
FROM gmd_spec_tests a,
gmd_qc_tests b,
gmd_test_methods_b c
WHERE a.spec_id = l_spec_id
AND a.test_id = b.test_id
AND b.test_id = l_test_id.test_id
AND b.test_method_id = c.test_method_id;
SELECT a.test_qty_uom, a.test_qty
INTO l_test_qty_uom, l_test_qty
FROM gmd_test_methods_b a, gmd_qc_tests b
WHERE b.test_id = l_test_id.test_id
AND a.test_method_id = b.test_method_id;
UPDATE gmd_test_group_gt
SET test_qty = l_test_qty,
test_qty_uom = l_test_qty_uom
WHERE test_id = l_test_id.test_id;
FUNCTION NAME: update_test_grp
DESCRIPTION: This procedure updates the Include flag in the Global
temporary table for a given test_id
PARAMETERS: In : p_test_id, p_include
CHANGE HISTORY: Created 24-MAY-04 RBODDU
===========================================================================*/
PROCEDURE update_test_grp(
p_test_id IN NUMBER ,
p_include IN VARCHAR2,
p_test_qty IN NUMBER,
p_test_uom IN VARCHAR2) IS
BEGIN
UPDATE gmd_test_group_gt SET
include = p_include,
test_qty = p_test_qty,
test_qty_uom = p_test_uom
WHERE test_id = p_test_id;
END update_test_grp;