Oracle EBS Quality Management: Automate QC Sample Creation Using GMD_SAMPLES_PUB API
This blog explains how to use an API in Oracle EBS to create Quality (QC) samples along with their corresponding test results.
Introduction / Issue
This blog contains an API 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;
/
Comments
Post a Comment