Introduction/ Issue: This blog contains an API and that can be used in Oracle EBS to create Quality samples and the corresponding results.
Why we need to do / Cause of the issue: The business requires creating a large number of quality samples and their corresponding results using an API, without any manual effort.
How do we solve: Create an Oracle package to create Quality samples and the results.
| CREATE OR REPLACE PACKAGE xx_gmd_qc_sample_result_pkg AS
/*********************************************************************************************** Who When Version Description Mohan G 04-Nov-2025 1.0 To create Sample and assign test results
************************************************************************************************/ g_conc_request_id number; PROCEDURE xx_validate_samples ( errbuf OUT VARCHAR2, retcode OUT NUMBER ); — Create QC Samples PROCEDURE xx_create_samples ( errbuf OUT VARCHAR2, retcode OUT NUMBER );
— Load QC Sample Results PROCEDURE xx_load_qc_sample_results ( errbuf OUT VARCHAR2, retcode OUT NUMBER, p_sample_id IN NUMBER, p_sample_no IN VARCHAR2 );
END xx_gmd_qc_sample_result_pkg; / CREATE OR REPLACE PACKAGE BODY xx_gmd_qc_sample_result_pkg IS /*********************************************************************************************** Who When Version Description Mohan G 04-Nov-2025 1.0 To create Sample and assign test results ************************************************************************************************/ PROCEDURE xx_validate_samples ( errbuf OUT VARCHAR2, retcode OUT NUMBER ) IS CURSOR c_validate IS SELECT staging_id, sample_no, item_no, whse_code, lot_no, specification, specification_vers FROM xx.xx_gmd_qc_samples_stg WHERE NVL(process_flag, ‘N’) = ‘N’; l_inv_id mtl_system_items_b.inventory_item_id%TYPE; l_lot_count NUMBER; l_spec_count NUMBER; l_error_msg VARCHAR2(2000); l_user_id NUMBER; l_username VARCHAR2(30) := ‘DATA_LOAD_USER’; l_sample_exists NUMBER :=0; l_errbuf VARCHAR2(2000); l_retcode NUMBER; l_err_message VARCHAR2(2000); BEGIN SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_username; dbms_output.put_line(‘Before Update1′); UPDATE XX.XX_GMD_QC_SAMPLES_STG SET creation_Date = sysdate, Created_by = l_user_id, request_id = g_conc_request_id where request_id is null and nvl(process_flag,’N’) =’N’ ; UPDATE XX.XX_GMD_QC_SAMPLES_STG SET sample_qty = 0 where request_id is null and nvl(process_flag,’N’) =’N’ and sample_qty is null; UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG SET creation_Date = sysdate, Created_by = l_user_id, request_id = g_conc_request_id where request_id is null and nvl(process_flag,’N’) =’N’ ; commit; retcode := 0; errbuf := NULL; FOR rec IN c_validate LOOP l_error_msg := NULL; dbms_output.put_line(‘Inside Loop’); BEGIN — Validate Sample number BEGIN dbms_output.put_line(‘number 1’); SELECT count(*) INTO l_sample_exists FROM gmd_samples where sample_no = rec.sample_no; IF l_sample_exists >=1 THEN l_error_msg := ‘Given sample number is already available in the system’; END IF; EXCEPTION WHEN OTHERS THEN l_error_msg := ‘Error while validating the sample number’; END; dbms_output.put_line(‘l_sample_exists 1 ‘|| l_sample_exists); IF l_error_msg IS NULL THEN — Validate Item & Organization dbms_output.put_line(‘l_error_msg 1 ‘|| l_error_msg); BEGIN SELECT msib.inventory_item_id INTO l_inv_id FROM mtl_system_items_b msib, org_organization_definitions ood WHERE msib.organization_id = ood.organization_id AND msib.segment1 = rec.item_no AND ood.organization_code = rec.whse_code; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_msg := ‘Invalid Item number / Organization’; WHEN OTHERS THEN l_error_msg := ‘Invalid Item number / Organization’; END; END IF; dbms_output.put_line(‘l_inv_id 1 ‘|| l_inv_id); — Validate Lot IF l_error_msg IS NULL THEN SELECT COUNT(*) INTO l_lot_count FROM mtl_lot_numbers mln WHERE mln.lot_number = rec.lot_no AND mln.inventory_item_id = l_inv_id; IF l_lot_count = 0 THEN l_error_msg := ‘Given lot is not defined in the system’; END IF; END IF; — Validate Specification IF l_error_msg IS NULL THEN BEGIN SELECT spec_id INTO l_spec_count FROM gmd_specifications gs WHERE gs.SPEC_VERS = rec.SPECIFICATION_VERS AND gs.spec_name = rec.specification; EXCEPTION WHEN NO_DATA_FOUND THEN l_error_msg := ‘Given specification not defined in the system’; END; END IF; dbms_output.put_line(‘Before Update’); — Update flag based on validation outcome IF l_error_msg IS NULL THEN UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘V’, error_message = NULL WHERE staging_id = rec.staging_id; xx_create_samples(errbuf => l_errbuf, retcode => l_retcode); ELSE dbms_output.put_line(‘Else Part’); UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘E’, error_message = l_error_msg WHERE staging_id = rec.staging_id; END IF; EXCEPTION WHEN OTHERS THEN l_err_message := SQLERRM; UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘E’, error_message = l_err_message WHERE staging_id = rec.staging_id; dbms_output.put_line(‘Exception Part’); commit; END; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN retcode := 2; errbuf := ‘Error in xx_validate_samples: ‘ || SQLERRM; ROLLBACK; DBMS_OUTPUT.put_line(errbuf); END xx_validate_samples; PROCEDURE xx_create_samples ( errbuf OUT VARCHAR2, retcode OUT NUMBER ) IS CURSOR c_sample_data IS SELECT * FROM xx.xx_gmd_qc_samples_stg WHERE process_flag = ‘V’; l_inv_id mtl_system_items_b.inventory_item_id%TYPE; l_sample_rec gmd_samples%ROWTYPE; x_qc_samples_rec gmd_samples%ROWTYPE; x_sampling_events_rec gmd_sampling_events%ROWTYPE; x_sample_spec_disp gmd_sample_spec_disp%ROWTYPE; x_event_spec_disp_rec gmd_event_spec_disp%ROWTYPE; x_results_tab gmd_api_pub.gmd_results_tab; x_spec_results_tab gmd_api_pub.gmd_spec_results_tab; x_return_status VARCHAR2(10); x_msg_data VARCHAR2(2000); x_msg_count NUMBER := 0; l_user_id NUMBER; l_username VARCHAR2(30) := ‘DATA_LOAD_USER’; g_conc_request_id NUMBER := fnd_profile.VALUE(‘CONC_REQUEST_ID’); l_dummy VARCHAR2(1000); l_org_id NUMBER := 0; l_errbuf VARCHAR2(2000); l_retcode NUMBER; BEGIN retcode := 0; errbuf := NULL; SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_username; fnd_global.apps_initialize(user_id => l_user_id, resp_id => 23805, resp_appl_id => 552); l_errbuf := NULL; l_retcode := 0; FOR rec IN c_sample_data LOOP BEGIN DBMS_OUTPUT.put_line(‘Loop Started’); — Get inventory item id again (validated already, but safe) SELECT msib.inventory_item_id, ood.organization_id INTO l_inv_id, l_org_id FROM mtl_system_items_b msib, org_organization_definitions ood WHERE msib.organization_id = ood.organization_id AND msib.segment1 = rec.item_no AND ood.organization_code = rec.whse_code; DBMS_OUTPUT.put_line(‘Inv Id’ || l_inv_id); — Prepare record l_sample_rec.sample_no := rec.sample_no; l_sample_rec.sample_desc := rec.sample_desc; l_sample_rec.source := rec.source; l_sample_rec.sample_qty := rec.sample_qty; l_sample_rec.sample_qty_uom := rec.sample_uom; l_sample_rec.sampler_id := l_user_id; l_sample_rec.lab_organization_id := NVL(rec.lab_org,l_org_id); l_sample_rec.organization_id := l_org_id; l_sample_rec.inventory_item_id := l_inv_id; l_sample_rec.sample_disposition := rec.sample_disposition; l_sample_rec.delete_mark := 0; l_sample_rec.sample_type := rec.sample_type; l_sample_rec.priority := rec.priority; l_sample_rec.attribute2 := rec.attribute2; l_sample_rec.lot_number := rec.lot_no; l_sample_rec.date_drawn := rec.date_drawn; — Call API gmd_samples_pub.create_samples( p_api_version => 3.0, p_init_msg_list => fnd_api.g_false, p_commit => fnd_api.g_false, p_validation_level => fnd_api.g_valid_level_full, p_qc_samples_rec => l_sample_rec, p_user_name => l_username, p_find_matching_spec => ‘Y’, p_grade => NULL, p_lpn => NULL, p_create_new_sample_group => ‘N’, x_qc_samples_rec => x_qc_samples_rec, x_sampling_events_rec => x_sampling_events_rec, x_sample_spec_disp => x_sample_spec_disp, x_event_spec_disp_rec => x_event_spec_disp_rec, x_results_tab => x_results_tab, x_spec_results_tab => x_spec_results_tab, x_return_status => x_return_status, x_msg_count => x_msg_count, x_msg_data => x_msg_data ); IF x_return_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line(‘Sample Created: ‘ || x_qc_samples_rec.sample_no); UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘S’, error_message = NULL –sample_id = x_qc_samples_rec.sample_id WHERE staging_id = rec.staging_id; UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG xgsr set xgsr.sample_id = x_qc_samples_rec.sample_id where xgsr.sample_no = rec.sample_no; xx_load_qc_sample_results(l_errbuf,l_retcode,x_qc_samples_rec.sample_id, rec.sample_no); ELSE — Collect API messages FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get(i, fnd_api.g_false, x_msg_data, l_dummy); DBMS_OUTPUT.put_line(‘API Error: ‘ || x_msg_data); END LOOP; UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘E’, error_message = SUBSTR(x_msg_data, 1, 2000) WHERE staging_id = rec.staging_id; END IF; EXCEPTION WHEN OTHERS THEN UPDATE xx.xx_gmd_qc_samples_stg SET process_flag = ‘E’, error_message = SUBSTR(‘Unexpected error during creation: ‘ || 1, 2000) WHERE staging_id = rec.staging_id; DBMS_OUTPUT.put_line(‘Unexpected error: ‘ || SQLERRM); END; END LOOP; COMMIT; EXCEPTION WHEN OTHERS THEN retcode := 2; errbuf := ‘Error in xx_create_samples: ‘ || SQLERRM; ROLLBACK; DBMS_OUTPUT.put_line(errbuf); END xx_create_samples; ——————————————————————— — Procedure: xx_load_qc_sample_results — Purpose : Record QC test results for given sample ——————————————————————— PROCEDURE xx_load_qc_sample_results ( errbuf OUT VARCHAR2, retcode OUT NUMBER, p_sample_id IN NUMBER, p_sample_no IN VARCHAR2 ) IS CURSOR c_test_results IS SELECT xgqs.staging_id, xgqs.sample_no, xgqs.seq, xgqs.test_code, — xgqs.test_id, xgqs.result_value_num, xgqs.result_value_char, xgqs.result_date, xgqs.test_replicate_cnt, gr.sample_id, gr.test_id FROM xx.XX_GMD_QC_SAMPLE_results_STG xgqs,gmd_results gr WHERE xgqs.sample_id = gr.sample_id and xgqs.seq = gr.seq AND NVL(xgqs.process_flag, ‘N’) = ‘N’ and gr.sample_id = p_sample_id and xgqs.sample_no = p_sample_no; l_results_rec gmd_results_pub.results_rec; l_results_out gmd_results%ROWTYPE; l_spec_results_out gmd_spec_results%ROWTYPE; l_org_id NUMBER; l_status VARCHAR2(1); l_msg_data VARCHAR2(4000); l_msg_count NUMBER := 0; l_username VARCHAR2(30) := ‘DATA_LOAD_USER’; l_user_id NUMBER; l_errbuf VARCHAR2(2000); l_retcode NUMBER; l_dummy VARCHAR2(1000); BEGIN SELECT user_id INTO l_user_id FROM fnd_user WHERE user_name = l_username; fnd_global.apps_initialize(user_id => l_user_id, resp_id => 23805, resp_appl_id => 552); UPDATE XX.XX_GMD_QC_SAMPLE_results_STG SET creation_Date = sysdate, Created_by = l_user_id, request_id = g_conc_request_id where request_id is null and nvl(process_flag,’N’) =’N’; UPDATE XX.XX_GMD_QC_SAMPLE_RESULTS_STG SET creation_Date = sysdate, Created_by = l_user_id, request_id = g_conc_request_id where request_id is null and nvl(process_flag,’N’) =’N’ ; commit; SELECT organization_id INTO l_org_id FROM gmd_samples WHERE sample_id = p_sample_id; retcode := 0; errbuf := NULL; FOR rec IN c_test_results LOOP l_results_rec.sample_id := p_sample_id; l_results_rec.test_id := rec.test_id; l_results_rec.test_code := rec.test_code; l_results_rec.result_value := NVL(rec.result_value_num, rec.result_value_char); l_results_rec.result_date := rec.result_date; l_results_rec.organization_id := l_org_id; l_results_rec.seq := rec.seq; — l_results_rec.lab_organization_id := l_org_id; l_results_rec.test_replicate_cnt := rec.test_replicate_cnt; gmd_results_pub.record_results ( p_api_version => 3.0, p_init_msg_list => fnd_api.g_false, p_commit => fnd_api.g_false, p_validation_level => fnd_api.g_valid_level_full, p_results_rec => l_results_rec, p_user_name => l_username, x_results_rec => l_results_out, x_spec_results_rec => l_spec_results_out, x_return_status => l_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_user_responsibility_id => 0 ); IF l_status = fnd_api.g_ret_sts_success THEN DBMS_OUTPUT.put_line(‘Result Loaded ? Sample: ‘ || p_sample_id || ‘, Test ID: ‘ || rec.test_id || ‘, Test Code: ‘ || rec.test_code || ‘, Status: ‘ || l_status || ‘, Message: ‘ || l_msg_data); UPDATE xx.XX_GMD_QC_SAMPLE_RESULTS_STG SET process_flag = ‘S’, error_message = NULL –sample_id = x_qc_samples_rec.sample_id WHERE staging_id = rec.staging_id; ELSE — Collect API messages FOR i IN 1 .. fnd_msg_pub.count_msg LOOP fnd_msg_pub.get(i, fnd_api.g_false, l_msg_data, l_dummy); DBMS_OUTPUT.put_line(‘API Error: ‘ || l_msg_data); END LOOP; UPDATE xx.XX_GMD_QC_SAMPLE_RESULTS_STG SET process_flag = ‘E’, error_message = SUBSTR(l_msg_data, 1, 2000) WHERE staging_id = rec.staging_id; END IF;
COMMIT; END LOOP;
EXCEPTION WHEN OTHERS THEN retcode := 2; errbuf := ‘Error in xx_load_qc_sample_results: ‘ || SQLERRM; DBMS_OUTPUT.put_line(errbuf); END xx_load_qc_sample_results; END xx_gmd_qc_sample_result_pkg; / |
