[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
* DELETE_RESULTS *
* *
* Use This is the public layer for the QC RESULTS *
* *
* History *
* Written by H Verdding, OPM Development (EMEA) *
* *
* magupta B2752102: Added the parameters for validation of *
* resource for passing it to results group API*
* HVerddin B2711643: Added call to set user_context *
* odaboval 2709353: Added call to create composite results *
* P.Raghu 3467531: Commented the check for Disposition in *
* Record_Results procedure. *
* magupta 3492836: Reserve Sample Id Validation *
* *
* Sulipta Tripathy Bug # 3848483 Added new fields in update *
* statement. TESTER_ID in GMD_RESULTS *
* VALUE_IN_REPORT_PRECISION in GMD_SPEC_RESULTS*
* Modified cond. to update GMD_SPEC_RESULTS *
* even if eval_ind is NULL. *
* B.Stone 9-Sep-2004 Bug 3763419; Added Guaranteed by *
* P Lowe 03-OCT-2008 Bug 7420373 allow update of numeric *
* test if one has already been recorded just as in *
* form. *
* Because API needs to reflect what is in the form, *
* results cannot be updated if the disposition is *
* changed to one of these below *
* ('0RT', '4A', '5AV', '6RJ', '7CN') *
* P Lowe 12-NOV-2008 Bug 7524393 *
* P Lowe 12-NOV-2008 Bug 13566337 *
* comment out call to GMD_RESULTS_GRP.change_disp_for_auto_lot *
* as form does not behave like this i.e sample automatically *
* going from complete to accept (in record_results proc ) *
* P Lowe 07-JUL-2012 Bug 14239488 *
* Added call to the procedure *
* calc_expression after updating the gmd_results table. *
* P Lowe 14-DEC-2012 Bug 14635099 *
* Changed procedure RECORD_RESULTS *
* 1. The Action Code is now populating on the result record *
* when result value is an out of specification and not passed *
* explicitly *
* 2. A new line is now being created for the test which is *
* out of spec. *
* 3. If user passes the evaluation status Accept with variance *
* (1V), Reject (2R) , Cancel (4C) , Void (50) then this takes *
* precedence over everything even if results is in spec l *
* *
*****************************************************************
*/
-- Global variables
G_PKG_NAME CONSTANT VARCHAR2(30):='GMD_RESULTS_PUB';
SELECT e.event_spec_disp_id, s.disposition , e.spec_id,
se.sample_req_cnt, se.sample_active_cnt
FROM gmd_event_spec_disp e , gmd_sample_spec_disp s , gmd_sampling_events se
WHERE s.event_spec_disp_id = e.event_spec_disp_id
AND se.sampling_event_id = e.sampling_event_id
AND se.sampling_event_id = p_sampling_event_id
AND s.sample_id = p_sample_id
AND e.spec_used_for_lot_attrib_ind = 'Y'
AND e.delete_mark = 0
AND s.delete_mark = 0;
SELECT text_range_seq
FROM GMD_QC_TEST_VALUES_B
WHERE test_id = p_test_id
AND value_char = p_result_char;
SELECT out_of_spec_action
FROM gmd_spec_tests_b
WHERE spec_id = p_spec_id
AND test_id = p_test_id;
select retest_action_code
from gmd_quality_config
where organization_id = l_samples_rec.organization_id;
SELECT disposition
FROM GMD_EVENT_SPEC_DISP
WHERE event_spec_disp_id = p_event_spec_disp
AND delete_mark = 0;
SELECT application_id
FROM fnd_application
WHERE application_short_name = 'GMD';
SELECT 1
FROM gmd_parameters_hdr gmd, org_access_view org
WHERE org.organization_id = gmd.organization_id
AND org.organization_id = cp_lab_organization_id
AND org.responsibility_id = p_user_responsibility_id
AND org.resp_application_id = cp_application_id
AND gmd.lab_ind = 1
AND org.inventory_enabled_flag = 'Y' ;
IF l_results_row.delete_mark = 1 THEN
GMD_API_PUB.Log_Message('GMD_RESULT_DELETED');
SELECT sampling_event_id
INTO l_reserve_sampling_event_id
FROM gmd_samples
WHERE sample_id = p_results_rec.reserve_sample_id;
select result_value_num into l_validate_res.result_num from gmd_results WHERE result_id = l_results_row.result_id;
select in_spec_ind into l_result_value_char from gmd_spec_results WHERE result_id = l_results_row.result_id;
UPDATE GMD_RESULTS
SET result_date = NVL(p_results_rec.result_date,l_date), -- 3559127 (use of p_results_rec rather than l_results_rec)
last_update_date = l_date,
last_updated_by = l_user_id,
result_value_num = l_validate_res.result_num,
result_value_char = l_result_value_char,
planned_resource = l_results_rec.planned_resource,
planned_resource_instance = l_results_rec.planned_resource_instance,
actual_resource = l_results_rec.actual_resource,
actual_resource_instance = l_results_rec.actual_resource_instance,
planned_result_date = l_results_rec.planned_result_date,
-- test_qty = l_results_row.test_qty, -- bug 3468300: test qty does not change in this API
test_uom = l_results_row.test_uom,
consumed_qty = l_results_row.consumed_qty,
test_by_date = l_results_rec.test_by_date,
tester_id = NVL(p_results_rec.tester_id,l_user_id), /* Bug # 3848483 Added this line */
reserve_sample_id = p_results_rec.reserve_sample_id,
attribute_category = p_results_rec.attribute_category, -- added dffs for Bug 13976016 - HOW TO UPDATE DFF IN QUALITY RESULT FORM, USING API
attribute1 = p_results_rec.attribute1,
attribute2 = p_results_rec.attribute2,
attribute3 = p_results_rec.attribute3,
attribute4 = p_results_rec.attribute4,
attribute5 = p_results_rec.attribute5,
attribute6 = p_results_rec.attribute6,
attribute7 = p_results_rec.attribute7,
attribute8 = p_results_rec.attribute8,
attribute9 = p_results_rec.attribute9,
attribute10 = p_results_rec.attribute10,
attribute11 = p_results_rec.attribute11,
attribute12 = p_results_rec.attribute12,
attribute13 = p_results_rec.attribute13,
attribute14 = p_results_rec.attribute14,
attribute15 = p_results_rec.attribute15,
attribute16 = p_results_rec.attribute16,
attribute17 = p_results_rec.attribute17,
attribute18 = p_results_rec.attribute18,
attribute19 = p_results_rec.attribute19,
attribute20 = p_results_rec.attribute20,
attribute21 = p_results_rec.attribute21,
attribute22 = p_results_rec.attribute22,
attribute23 = p_results_rec.attribute23,
attribute24 = p_results_rec.attribute24,
attribute25 = p_results_rec.attribute25,
attribute26 = p_results_rec.attribute26,
attribute27 = p_results_rec.attribute27,
attribute28 = p_results_rec.attribute28,
attribute29 = p_results_rec.attribute29,
attribute30 = p_results_rec.attribute30
WHERE result_id = l_results_row.result_id;
UPDATE gmd_results
SET result_date = NULL ,
tester_id = NULL
WHERE result_id = l_results_row.result_id
AND result_value_num IS NULL;
gmd_samples_grp.update_remaining_qty
(l_results_row.result_id, -- B3600012 changed from l_results_rec
p_results_rec.reserve_sample_id ,
l_results_row.consumed_qty ,
l_return_status
);
gmd_samples_grp.update_remaining_qty
(l_results_row.result_id, -- B3600012 changed from l_results_rec
l_results_row.sample_id ,
l_results_row.consumed_qty ,
l_return_status
);
/* Bug # 3848483 Update the spec results even if eval_ind is NULL - Commenting the condition */
-- IF NVL(l_validate_res.evaluation_ind, p_results_rec.eval_ind) is NOT NULL THEN
IF NOT GMD_SPEC_RESULTS_PVT.lock_row
( p_event_spec_disp_id => l_event_spec_disp_id,
p_result_id => l_results_row.result_id
) THEN
RAISE FND_API.G_EXC_ERROR;
UPDATE GMD_SPEC_RESULTS
SET IN_SPEC_IND = l_validate_res.in_spec,
evaluation_ind = l_validate_res.evaluation_ind,
-- NVL(l_validate_res.evaluation_ind,p_results_rec.eval_ind), -- pal CT wants to be able to pass null as in the UI BUT wants system to update /* Bug # 3848483 Swapped args. l_validate_res takes precedence BUT wait - see above */
action_code = NVL(p_results_rec.action_code,
l_validate_res.result_action_code), -- 14635099 l_validate_res.result_action_code was not being automatically filled in.
last_update_date = l_date,
last_updated_by = l_user_id,
--value_in_report_precision = ROUND(p_results_rec.result_value,l_validate_res.report_precision)/* Bug # 3848483 Added this line */
value_in_report_precision = -- replaced above line with this -- bug 7524393
DECODE(l_tests_rec.test_type, 'T', NULL, 'V', NULL, 'U', NULL,
ROUND(p_results_rec.result_value,l_validate_res.report_precision))
WHERE event_spec_disp_id = l_event_spec_disp_id
AND result_id = l_results_row.result_id;
SELECT 1
FROM GMD_EVENT_SPEC_DISP
WHERE EVENT_SPEC_DISP_ID = p_event_spec_disp
AND DELETE_MARK = 0;
IF x_samples_rec.delete_mark = 1 THEN
GMD_API_PUB.Log_Message('GMD_RECORD_DELETE_MARKED',
'l_table_name', 'GMD_SAMPLES',
'l_column_name', 'SAMPLE_ID',
'l_key_value', x_samples_rec.sample_id);