Introduction:
This blog has the PLSQL query that can be used to create Quality Test Values alone using oracle seeded API.
Cause of the issue:
Business will provide the raw data, based on that we need to create Quality Test values.
How do we solve:
Created a PL/SQL procedure to create Quality Test Values using API.
| CREATE OR REPLACE PROCEDURE xx_load_qc_test_values (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS CURSOR c_test_values IS SELECT * FROM xx_gmd_qc_test_values_stg WHERE process_flag = ‘N’;
l_err_flag BOOLEAN; l_qc_test_value_id NUMBER; l_test_id NUMBER; l_rowid ROWID; l_test_val_cnt NUMBER; l_msg_data VARCHAR2(2000); l_msg_dummy VARCHAR2(2000); BEGIN FOR rt IN c_test_values LOOP
l_msg_data := null; l_msg_dummy := null; l_err_flag := FALSE; l_qc_test_value_id := null; l_test_id := null; l_test_val_cnt := 0;
BEGIN
DBMS_OUTPUT.put_line (‘1–‘||rt.test_code); IF rt.test_code IS NOT NULL THEN BEGIN
DBMS_OUTPUT.put_line (‘2–‘||rt.test_code);
SELECT test_id INTO l_test_id FROM gmd_qc_tests WHERE test_code = rt.test_code; EXCEPTION WHEN NO_DATA_FOUND THEN l_err_flag := TRUE;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘TEST Code’ || ‘ ‘ || rt.test_code || ‘ ‘ || ‘is not exists in the base Table GMD_QC_TESTS’ WHERE staging_id = rt.staging_id; WHEN OTHERS THEN l_err_flag := TRUE;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘TEST Code’ || ‘ ‘ || rt.test_code || ‘ ‘ || ‘is not exists in the base Table GMD_QC_TESTS’ WHERE staging_id = rt.staging_id; END; END IF;
BEGIN SELECT COUNT (*) INTO l_test_val_cnt FROM gmd_qc_tests gt, gmd_qc_test_values gts WHERE gt.test_id = gts.test_id AND gts.test_value_desc = rt.test_value_desc AND gts.value_char = rt.value_char AND gt.test_code = rt.test_code;
IF l_test_val_cnt = 1 THEN l_err_flag := TRUE;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘TEST Code values of ‘ || ‘ ‘ || rt.test_code || ‘ ‘ || ‘is exists in the base Table GMD_QC_TEST_VALUES Table’ WHERE staging_id = rt.staging_id; ELSE l_err_flag := FALSE; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘TEST Code values of ‘ || ‘ ‘ || rt.test_code || ‘ ‘ || ‘is exists in the base Table GMD_QC_TEST_VALUES Table’ WHERE staging_id = rt.staging_id; WHEN OTHERS THEN l_err_flag := TRUE;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘TEST Code values of ‘ || ‘ ‘ || rt.test_code || ‘ ‘ || ‘is exists in the base Table GMD_QC_TEST_VALUES Table’ WHERE staging_id = rt.staging_id;
END;
IF l_err_flag <> TRUE THEN
gmd_qc_test_values_pvt.insert_row (x_rowid => l_rowid, x_test_value_id => l_qc_test_value_id, x_test_id => l_test_id, x_min_num => NULL, x_max_num => NULL, x_value_char => rt.value_char, x_text_range_seq => NULL, x_expression_ref_test_id => NULL, x_text_code => NULL, x_attribute_category => NULL, x_attribute1 => NULL, x_attribute2 => NULL, x_attribute3 => NULL, x_attribute4 => NULL, x_attribute5 => NULL, x_attribute6 => NULL, x_attribute7 => NULL, x_attribute8 => NULL, x_attribute9 => NULL, x_attribute10 => NULL, x_attribute11 => NULL, x_attribute12 => NULL, x_attribute13 => NULL, x_attribute14 => NULL, x_attribute15 => NULL, x_attribute16 => NULL, x_attribute17 => NULL, x_attribute18 => NULL, x_attribute19 => NULL, x_attribute20 => NULL, x_attribute21 => NULL, x_attribute22 => NULL, x_attribute23 => NULL, x_attribute24 => NULL, x_attribute25 => NULL, x_attribute26 => NULL, x_attribute27 => NULL, x_attribute28 => NULL, x_attribute29 => NULL, x_attribute30 => NULL, x_display_label_numeric_range => NULL, x_test_value_desc => rt.test_value_desc, x_creation_date => SYSDATE, x_created_by => -1, x_last_update_date => SYSDATE, x_last_updated_by => -1, x_last_update_login => -1 );
IF l_qc_test_value_id IS NOT NULL THEN
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘S’, error_message = NULL WHERE staging_id = rt.staging_id; ELSE UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = SUBSTR (errbuf, 1, 2000) WHERE staging_id = rt.staging_id; END IF; ELSE UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘E’, error_message = ‘Validation Errors: — ‘|| error_message WHERE staging_id = rt.staging_id; END IF; EXCEPTION WHEN OTHERS THEN
FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get (i, fnd_api.g_false, l_msg_data, l_msg_dummy ); DBMS_OUTPUT.put_line ( ‘Quality Test Values ‘ || i || ‘: ‘ || l_msg_data || ‘: ‘||l_msg_dummy ); END LOOP;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘X’, error_message = SUBSTR (l_qc_test_value_id || ‘ ‘ || l_msg_data||’ ‘||l_msg_dummy, 1, 1000 ) WHERE staging_id = rt.staging_id; END; END LOOP;
COMMIT; END xx_load_qc_test_values; |
