Here is an API approach to create Samples from backend and add Test results to Samples. Firstly we need to create Sample using API gmd_samples_pub.create_samples and then add Test Results to the Sample by using gmd_results_pub.add_tests_to_sample API.
Points to note
- Specification will be automatically picked up based on the item and org combination.
- Sample Type code should be picked up from GEM_LOOKUPS with lookup type as “SAMPLE_TYPE”
- Sample Disposition code should be picked up from GEM_LOOKUPS with lookup type as “GMD_QC_SAMPLE_DISP”
/****************************************************************
*PURPOSE: API to create Samples and Add Test Results to Samples *
*AUTHOR: Shailender Thallam *
*****************************************************************/
DECLARE
--
l_sample gmd_samples%ROWTYPE;
l_username fnd_user.user_name%TYPE := 'SYSADMIN';
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_count NUMBER;
x_msg_data VARCHAR2 (2000);
--
--
l_status_flag VARCHAR2 (1);
l_err_msg VARCHAR2 (3999) := NULL;
l_return_status VARCHAR2 (10);
l_msg_data VARCHAR2 (4000);
l_msg_count NUMBER;
l_number_tab gmd_api_pub.number_tab;
l_samples gmd_samples%ROWTYPE;
l_sample_id NUMBER;
l_sample_no VARCHAR2 (20);
l_test_id NUMBER;
l_event_spec_disp_id NUMBER;
--
--Cursor to get specification information
--
BEGIN
--
fnd_global.apps_initialize (0, 54476, 552);
--
fnd_message.CLEAR;
--
l_sample_rec.sample_qty := .01;
l_sample_rec.sample_qty_uom := 'VAL';
l_sample_rec.source := 'W'; --WIP Type
l_sample_rec.sampler_id := 0;
l_sample_rec.lab_organization_id := 11362;
l_sample_rec.organization_id := 11362;
l_sample_rec.inventory_item_id := 296395;
l_sample_rec.batch_id := 2993332;
l_sample_rec.sample_disposition := '1P'; --Pending
l_sample_rec.delete_mark := 0;
l_sample_rec.sample_type := 'I'; --Item type
l_sample_rec.lot_number := '6200001';
--
--Calling API to create Item Sample
--
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
);
--
DBMS_OUTPUT.put_line ( 'Status: '
|| x_return_status
|| '-'
|| x_msg_count
|| '-'
|| x_msg_data
);
l_sample_id := x_qc_samples_rec.sample_id;
DBMS_OUTPUT.put_line ('Sample ID: ' || l_sample_id);
l_sample_no := x_qc_samples_rec.sample_no;
DBMS_OUTPUT.put_line ('Sample Number: ' || l_sample_no);
--
IF x_msg_count > 1
THEN
FOR i IN 1 .. x_msg_count
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
END IF;
-- create results
l_status_flag := 'P';
l_err_msg := NULL;
l_test_id := 58;
l_return_status := NULL;
l_msg_data := NULL;
l_msg_count := NULL;
l_samples.sample_id := l_sample_id;
l_number_tab (1) := l_test_id;
SELECT event_spec_disp_id
INTO l_event_spec_disp_id
FROM gmd_sample_spec_disp
WHERE sample_id = l_sample_id;
--
--Calling API to add Test Results to Sample
--
gmd_results_pub.add_tests_to_sample
(p_api_version => 2.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_user_name => 'SYSADMIN',
p_sample_rec => l_samples,
p_test_id_tab => l_number_tab,
p_event_spec_disp_id => l_event_spec_disp_id,
x_results_tab => x_results_tab,
x_spec_results_tab => x_spec_results_tab,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
COMMIT;
IF (l_return_status <> fnd_api.g_ret_sts_success)
THEN
FOR i IN 1 .. fnd_msg_pub.count_msg
LOOP
DBMS_OUTPUT.put_line
( i
|| '. '
|| SUBSTR
(fnd_msg_pub.get (p_encoded => fnd_api.g_false),
1,
255
)
);
END LOOP;
l_err_msg := l_err_msg || l_msg_data;
l_status_flag := 'E';
END IF;
DBMS_OUTPUT.put_line ('l_err_msg create result:' || l_err_msg);
EXCEPTION
WHEN OTHERS
THEN
l_status_flag := 'E';
l_err_msg :=
l_err_msg || '|' || 'Unknown error ' || '|'
|| SUBSTR (SQLERRM, 1, 250);
DBMS_OUTPUT.put_line ('l_err_msg:' || l_err_msg);
END;