Introduction:
This blog has the PLSQL query that can be used to create Quality Test & Quality Test Values using oracle seeded API.
Cause of the issue:
Business will provide the raw data, based on that we need to create Quality Test & Quality Test values.
How do we solve:
Created a PL/SQL procedure to create Quality Test & Quality Test Values using API.
| CREATE OR REPLACE PROCEDURE xx_load_qc_tests (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS CURSOR c_tests IS SELECT * FROM xx_gmd_qc_tests_stg WHERE process_flag IN (‘N’);
CURSOR c_test_values (p_test_code VARCHAR2) IS SELECT * FROM xx_gmd_qc_test_values_stg WHERE test_code = p_test_code AND process_flag = ‘N’;
l_qc_tests_rec gmd_qc_tests%ROWTYPE; l_qc_test_values_tbl gmd_qc_tests_pub.qc_test_values_tbl; l_qc_cust_tests_tbl gmd_qc_tests_pub.qc_cust_tests_tbl; x_qc_tests_rec gmd_qc_tests%ROWTYPE; x_qc_test_values_tbl gmd_qc_tests_pub.qc_test_values_tbl; x_qc_cust_tests_tbl gmd_qc_tests_pub.qc_cust_tests_tbl; l_return_status VARCHAR2 (1); l_msg_count NUMBER; v_test_method_id NUMBER; l_count NUMBER := 0; l_qt_count NUMBER := 0; l_error_flag BOOLEAN := FALSE; l_error_count NUMBER; v_test_type VARCHAR (10); v_test_class VARCHAR (8); v_priority VARCHAR (8); v_unit VARCHAR (25); l_idx NUMBER; l_msg_data VARCHAR2(2000); l_msg_dummy VARCHAR2(2000); BEGIN FOR r IN c_tests LOOP — Variable Initilization l_error_count := 0; v_test_type := NULL; v_test_class := NULL; v_priority := NULL; v_unit := NULL; l_qc_test_values_tbl.DELETE; l_idx := 0; l_msg_data := null; l_msg_dummy := null; BEGIN BEGIN –Validation to check given Quatity Test is already exists or not SELECT COUNT (*) INTO l_qt_count FROM gmd_qc_tests WHERE test_code = r.TEST;
IF l_qt_count = 1 THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = ‘Quality test already exists in the system’ WHERE staging_id = r.staging_id; ELSE l_error_count := 0; END IF; EXCEPTION WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = ‘Quality test already exists in the system’ WHERE staging_id = r.staging_id; END; BEGIN — Validation for given test method exists and should be active SELECT test_method_id INTO v_test_method_id FROM gmd_test_methods WHERE 1 = 1 — AND delete_mark <> 1 AND test_method_code = r.method; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test method is not defined in the system’ WHERE staging_id = r.staging_id; WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test method is not defined in the system’ WHERE staging_id = r.staging_id; END;
BEGIN — Validation for given test type exists SELECT flv.lookup_code INTO v_test_type FROM fnd_lookup_types flt, fnd_lookup_values flv WHERE flt.lookup_type = flv.lookup_type AND flv.lookup_type = ‘GMD_QC_TEST_TYPE’ AND LANGUAGE = ‘US’ AND flv.lookup_code = r.test_type; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test type is not defined in the system’ WHERE staging_id = r.staging_id; WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test type is not defined in the system’ WHERE staging_id = r.staging_id; END;
BEGIN — Validation for given test class exists SELECT test_class INTO v_test_class FROM gmd_test_classes WHERE test_class = r.CLASS; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test class in not defined in the system’ WHERE staging_id = r.staging_id; WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Quality test class in not defined in the system’ WHERE staging_id = r.staging_id; END;
BEGIN IF r.unit IS NOT NULL THEN BEGIN — Validation for given priority exists SELECT qcunit_code INTO v_unit FROM gmd_units WHERE qcunit_code = r.unit; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Test Unit is not defined in the system’ WHERE staging_id = r.staging_id; WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Test Unit is not defined in the system’ WHERE staging_id = r.staging_id; END; END IF; END;
BEGIN — Validation for given priority exists SELECT flv.lookup_code INTO v_priority FROM fnd_lookup_types flt, fnd_lookup_values flv WHERE flt.lookup_type = flv.lookup_type AND flv.lookup_type = ‘GMD_QC_TEST_PRIORITY’ AND LANGUAGE = ‘US’ AND flv.lookup_code = r.priority; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Given priority is not defined in the system’ WHERE staging_id = r.staging_id; WHEN OTHERS THEN l_error_count := 1;
UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = error_message || ‘ ,’ || ‘Given priority is not defined in the system’ WHERE staging_id = r.staging_id; END;
FOR test_rec IN c_test_values (r.TEST) LOOP l_idx := l_idx + 1; l_qc_test_values_tbl (l_idx).value_char := test_rec.value_char; l_qc_test_values_tbl (l_idx).test_value_desc := test_rec.test_value_desc; END LOOP;
IF l_error_count = 0 THEN — Reset record l_qc_tests_rec := NULL; l_qc_tests_rec.test_code := r.TEST; l_qc_tests_rec.test_desc := r.description; l_qc_tests_rec.test_method_id := v_test_method_id; l_qc_tests_rec.test_type := v_test_type; l_qc_tests_rec.test_unit := v_unit; l_qc_tests_rec.test_class := v_test_class; l_qc_tests_rec.test_oprn_line_id := r.test_oprn_line_id; l_qc_tests_rec.min_value_num := r.range_min_value; l_qc_tests_rec.max_value_num := r.range_max_value; l_qc_tests_rec.display_precision := r.stored_precision; l_qc_tests_rec.report_precision := r.report_precision; l_qc_tests_rec.priority := v_priority; — API Call gmd_qc_tests_pub.create_tests (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_false, p_validation_level => fnd_api.g_valid_level_full, p_qc_tests_rec => l_qc_tests_rec, p_qc_test_values_tbl => l_qc_test_values_tbl, p_qc_cust_tests_tbl => l_qc_cust_tests_tbl, p_user_name => ‘DATA_LOAD_USER’, x_qc_tests_rec => x_qc_tests_rec, x_qc_test_values_tbl => x_qc_test_values_tbl, x_qc_cust_tests_tbl => x_qc_cust_tests_tbl, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data );
IF l_return_status = ‘S’ THEN UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘S’, error_message = NULL WHERE staging_id = r.staging_id;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = ‘S’, error_message = NULL WHERE test_code = r.TEST; ELSE
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 );
UPDATE xx_gmd_qc_tests_stg SET process_flag = l_return_status, error_message = SUBSTR (l_return_status || ‘ ‘ || l_msg_data,1,2000) WHERE staging_id = r.staging_id;
UPDATE xx_gmd_qc_test_values_stg SET process_flag = l_return_status, error_message = SUBSTR (l_return_status || ‘ ‘ || l_msg_data,1,2000) WHERE test_code = r.TEST; END LOOP;
END IF; ELSE UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘E’, error_message = ‘Validation Errors: ‘ || ‘ ‘ || error_message WHERE staging_id = r.staging_id; END IF; EXCEPTION WHEN OTHERS THEN UPDATE xx_gmd_qc_tests_stg SET process_flag = ‘O’, error_message = SUBSTR (errbuf, 1, 2000) WHERE staging_id = r.staging_id; END; END LOOP;
COMMIT; END xx_load_qc_tests; |
