[Home] [Help]
129: PROCEDURE create_rslt_and_spec_rslt_rows
130: (
131: p_sample IN GMD_SAMPLES%ROWTYPE
132: , p_migration IN VARCHAR2
133: , x_event_spec_disp OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
134: , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
135: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
136: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
137: , x_return_status OUT NOCOPY VARCHAR2
199: l_return_status VARCHAR2(1);
200: l_meaning VARCHAR2(80);
201:
202: l_sampling_event gmd_sampling_events%ROWTYPE;
203: l_event_spec_disp gmd_event_spec_disp%ROWTYPE;
204: l_sample_spec_disp gmd_sample_spec_disp%ROWTYPE;
205: l_results gmd_results%ROWTYPE;
206: l_spec_results gmd_spec_results%ROWTYPE;
207:
205: l_results gmd_results%ROWTYPE;
206: l_spec_results gmd_spec_results%ROWTYPE;
207:
208: l_in_sampling_event gmd_sampling_events%ROWTYPE;
209: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
210: l_out_event_spec_disp gmd_event_spec_disp%ROWTYPE;
211: l_out_results gmd_results%ROWTYPE;
212:
213: -- Exceptions
206: l_spec_results gmd_spec_results%ROWTYPE;
207:
208: l_in_sampling_event gmd_sampling_events%ROWTYPE;
209: l_in_event_spec_disp gmd_event_spec_disp%ROWTYPE;
210: l_out_event_spec_disp gmd_event_spec_disp%ROWTYPE;
211: l_out_results gmd_results%ROWTYPE;
212:
213: -- Exceptions
214: e_sampling_event_fetch_error EXCEPTION;
357: l_date := SYSDATE;
358: l_seq := 0;
359: dummy := 0;
360:
361: -- Check if we already have a record in GMD_EVENT_SPEC_DISP
362: l_event_spec_disp_id := get_current_event_spec_disp_id(
363: l_sampling_event.sampling_event_id);
364:
365: IF (l_event_spec_disp_id IS NULL) THEN
362: l_event_spec_disp_id := get_current_event_spec_disp_id(
363: l_sampling_event.sampling_event_id);
364:
365: IF (l_event_spec_disp_id IS NULL) THEN
366: -- This is the first sample so create a record in GMD_EVENT_SPEC_DISP
367:
368: -- Construct the record
369: l_event_spec_disp.sampling_event_id := l_sampling_event.sampling_event_id;
370: l_event_spec_disp.spec_id := l_spec_id;
380: l_event_spec_disp.created_by := l_user_id;
381: l_event_spec_disp.last_update_date := l_date;
382: l_event_spec_disp.last_updated_by := l_user_id;
383:
384: -- We are ready for insert in GMD_EVENT_SPEC_DISP, so then lets do it.
385: IF NOT(gmd_event_spec_disp_pvt.insert_row(
386: p_event_spec_disp => l_event_spec_disp,
387: x_event_spec_disp => l_out_event_spec_disp)
388: )
381: l_event_spec_disp.last_update_date := l_date;
382: l_event_spec_disp.last_updated_by := l_user_id;
383:
384: -- We are ready for insert in GMD_EVENT_SPEC_DISP, so then lets do it.
385: IF NOT(gmd_event_spec_disp_pvt.insert_row(
386: p_event_spec_disp => l_event_spec_disp,
387: x_event_spec_disp => l_out_event_spec_disp)
388: )
389: THEN
391: RAISE e_event_spec_disp_insert_error;
392: END IF;
393: l_event_spec_disp.event_spec_disp_id := l_out_event_spec_disp.event_spec_disp_id;
394: ELSE
395: -- Fetch the GMD_EVENT_SPEC_DISP record
396: -- l_event_spec_disp.event_spec_disp_id := l_event_spec_disp_id;
397: l_in_event_spec_disp.event_spec_disp_id := l_event_spec_disp_id;
398: IF NOT(gmd_event_spec_disp_pvt.fetch_row(
399: p_event_spec_disp => l_in_event_spec_disp,
394: ELSE
395: -- Fetch the GMD_EVENT_SPEC_DISP record
396: -- l_event_spec_disp.event_spec_disp_id := l_event_spec_disp_id;
397: l_in_event_spec_disp.event_spec_disp_id := l_event_spec_disp_id;
398: IF NOT(gmd_event_spec_disp_pvt.fetch_row(
399: p_event_spec_disp => l_in_event_spec_disp,
400: x_event_spec_disp => l_event_spec_disp)
401: )
402: THEN
677:
678: IF NOT (l_event_spec_disp.disposition IN ('1P', '2I')) THEN
679: -- Set the disposition of the Event spec disp
680: -- back to "In Progress"
681: UPDATE gmd_event_spec_disp
682: SET disposition = '2I',
683: last_updated_by = l_user_id,
684: last_update_date = l_date
685: WHERE event_spec_disp_id = l_event_spec_disp.event_spec_disp_id
820:
821: -- Cursors
822: CURSOR c_event_disp(p_sampling_event_id NUMBER) IS
823: SELECT event_spec_disp_id
824: FROM gmd_event_spec_disp
825: WHERE sampling_event_id = p_sampling_event_id
826: AND spec_used_for_lot_attrib_ind = 'Y'
827: AND delete_mark = 0
828: ;
1371: l_sample GMD_SAMPLES%ROWTYPE;
1372: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1373: l_results GMD_RESULTS%ROWTYPE;
1374: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
1375: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1376:
1377: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1378: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1379: l_out_results GMD_RESULTS%ROWTYPE;
1374: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
1375: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1376:
1377: l_in_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
1378: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
1379: l_out_results GMD_RESULTS%ROWTYPE;
1380:
1381: -- Exceptions
1382: e_results_insert_error EXCEPTION;
1446: -- Fetch the Event Spec Record
1447: IF p_event_spec_disp_id IS NOT NULL THEN
1448: -- l_event_spec_disp.event_spec_disp_id := p_event_spec_disp_id;
1449: l_in_event_spec_disp.event_spec_disp_id := p_event_spec_disp_id;
1450: IF NOT (GMD_EVENT_SPEC_DISP_PVT.fetch_row(
1451: p_event_spec_disp => l_in_event_spec_disp,
1452: x_event_spec_disp => l_event_spec_disp)
1453: )
1454: THEN
1717:
1718: IF (l_event_spec_disp.disposition <> '2I') THEN
1719: -- Set the disposition of the Event spec disp
1720: -- back to "In Progress"
1721: UPDATE gmd_event_spec_disp
1722: SET disposition = '2I',
1723: last_updated_by = l_user_id,
1724: last_update_date = l_date
1725: WHERE event_spec_disp_id = p_event_spec_disp_id
1999: --| spec for a given sample. |
2000: --| |
2001: --| This procedure will first create any missing replicate |
2002: --| of the tests for the target spec in gmd_results. Then |
2003: --| create a row in GMD_EVENT_SPEC_DISP and a row in |
2004: --| GMD_SAMPLE_SPEC_DISP and a set of rows in |
2005: --| GMD_SPEC_RESULTS |
2006: --| |
2007: --| |
2062: ;
2063:
2064: CURSOR c_event_spec (p_sampling_event_id NUMBER, p_spec_vr_id NUMBER) IS
2065: SELECT *
2066: FROM gmd_event_spec_disp
2067: WHERE sampling_event_id = p_sampling_event_id
2068: AND spec_vr_id = p_spec_vr_id
2069: AND spec_used_for_lot_attrib_ind = 'Y'
2070: ;
2073: l_test_id GMD_QC_TESTS.test_id%TYPE;
2074: l_spec_id gmd_spec_tests_b.spec_id%TYPE;
2075:
2076: CURSOR c_evaluation_ind (p_result_id NUMBER, p_event_spec_disp_id NUMBER) IS
2077: SELECT evaluation_ind from gmd_spec_results gsr, gmd_event_spec_disp esd --, gmd_sample_spec_disp ssd
2078: WHERE gsr.result_id=p_result_id
2079: AND esd.event_spec_disp_id=p_event_spec_disp_id
2080: AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
2081:
2080: AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
2081:
2082:
2083: CURSOR c_event_spec_disp_id (p_sample_id NUMBER) IS
2084: SELECT esd.event_spec_disp_id from gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
2085: WHERE ssd.sample_id=p_sample_id
2086: AND esd.event_spec_disp_id=ssd.event_spec_disp_id
2087: AND esd.spec_used_for_lot_attrib_ind = 'Y';
2088:
2154: l_test_ids GMD_API_PUB.number_tab;
2155: l_results_tab GMD_API_PUB.gmd_results_tab;
2156: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
2157:
2158: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2159: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
2160: l_spec_result GMD_SPEC_RESULTS%ROWTYPE;
2161: l_sample GMD_SAMPLES%ROWTYPE;
2162:
2160: l_spec_result GMD_SPEC_RESULTS%ROWTYPE;
2161: l_sample GMD_SAMPLES%ROWTYPE;
2162:
2163: l_in_sample GMD_SAMPLES%ROWTYPE;
2164: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2165:
2166: -- Exceptions
2167: e_sample_fetch_error EXCEPTION;
2168: e_event_spec_disp_insert_error EXCEPTION;
2246: FETCH c_event_spec INTO l_event_spec_disp;
2247: IF c_event_spec%NOTFOUND THEN
2248: -- Since the new spec is going to be the current one, change
2249: -- all the previous ones to NOT Current
2250: UPDATE gmd_event_spec_disp
2251: set spec_used_for_lot_attrib_ind = NULL
2252: where sampling_event_id = l_sample.sampling_event_id
2253: and spec_used_for_lot_attrib_ind = 'Y'
2254: ;
2253: and spec_used_for_lot_attrib_ind = 'Y'
2254: ;
2255:
2256: IF (l_debug = 'Y') THEN
2257: gmd_debug.put_line(' Updated all previous gmd_event_spec_disp to NOT-Current.');
2258: END IF;
2259:
2260: -- Create a new record in GMD_EVENT_SPEC_DISP for the Target Spec
2261:
2256: IF (l_debug = 'Y') THEN
2257: gmd_debug.put_line(' Updated all previous gmd_event_spec_disp to NOT-Current.');
2258: END IF;
2259:
2260: -- Create a new record in GMD_EVENT_SPEC_DISP for the Target Spec
2261:
2262: -- Construct the record
2263: l_event_spec_disp.SAMPLING_EVENT_ID := l_sample.sampling_event_id;
2264: l_event_spec_disp.SPEC_ID := p_target_spec_id;
2274: l_event_spec_disp.CREATED_BY := l_user_id;
2275: l_event_spec_disp.LAST_UPDATE_DATE := l_date;
2276: l_event_spec_disp.LAST_UPDATED_BY := l_user_id;
2277:
2278: -- We are ready for insert in GMD_EVENT_SPEC_DISP, so then lets do it.
2279: IF NOT(GMD_EVENT_SPEC_DISP_PVT.Insert_Row(
2280: p_event_spec_disp => l_event_spec_disp,
2281: x_event_spec_disp => l_out_event_spec_disp)
2282: )
2275: l_event_spec_disp.LAST_UPDATE_DATE := l_date;
2276: l_event_spec_disp.LAST_UPDATED_BY := l_user_id;
2277:
2278: -- We are ready for insert in GMD_EVENT_SPEC_DISP, so then lets do it.
2279: IF NOT(GMD_EVENT_SPEC_DISP_PVT.Insert_Row(
2280: p_event_spec_disp => l_event_spec_disp,
2281: x_event_spec_disp => l_out_event_spec_disp)
2282: )
2283: THEN
2286: END IF;
2287: l_event_spec_disp.EVENT_SPEC_DISP_ID := l_out_event_spec_disp.EVENT_SPEC_DISP_ID;
2288:
2289: IF (l_debug = 'Y') THEN
2290: gmd_debug.put_line(' A new record is created in GMD_EVENT_SPEC_DISP. ID - ' || l_event_spec_disp.event_spec_disp_id);
2291: END IF;
2292:
2293: END IF;
2294: CLOSE c_event_spec;
2655:
2656: -- Cursors
2657: CURSOR c_get_se_id (p_composite_spec_disp_id NUMBER) IS
2658: SELECT sampling_event_id
2659: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd
2660: WHERE csd.composite_spec_disp_id = p_composite_spec_disp_id
2661: AND csd.event_spec_disp_id = esd.event_spec_disp_id
2662: AND csd.latest_ind = 'Y'
2663: AND csd.delete_mark = 0
3456: UPDATE gmd_sampling_events
3457: SET recomposite_ind = 'Y'
3458: WHERE sampling_event_id =
3459: (SELECT sampling_event_id
3460: FROM gmd_event_spec_disp
3461: WHERE event_spec_disp_id = p_event_spec_disp_id);
3462: END IF;
3463: CLOSE c_composite;
3464:
3509:
3510: -- Bug 3079877: do not composite planning samples
3511: CURSOR c_sample(p_sampling_event_id NUMBER) IS
3512: SELECT s.sample_id
3513: FROM gmd_event_spec_disp esd,
3514: gmd_sample_spec_disp ssd,
3515: gmd_samples s
3516: WHERE esd.sampling_event_id = p_sampling_event_id
3517: AND esd.spec_used_for_lot_attrib_ind = 'Y'
3755: ;
3756:
3757: CURSOR c_spec_id (p_event_spec_disp_id NUMBER) IS
3758: SELECT spec_id
3759: FROM gmd_event_spec_disp
3760: WHERE event_spec_disp_id = p_event_spec_disp_id
3761: ;
3762:
3763: CURSOR c_test_type (p_test_id NUMBER) IS
6117: l_sample_in GMD_SAMPLES%ROWTYPE;
6118: l_sample GMD_SAMPLES%ROWTYPE;
6119: l_sampling_event_in GMD_SAMPLING_EVENTS%ROWTYPE;
6120: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
6121: l_event_spec_disp_in GMD_EVENT_SPEC_DISP%ROWTYPE;
6122: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6123: l_sample_spec_disp_in GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6124: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6125: l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
6118: l_sample GMD_SAMPLES%ROWTYPE;
6119: l_sampling_event_in GMD_SAMPLING_EVENTS%ROWTYPE;
6120: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
6121: l_event_spec_disp_in GMD_EVENT_SPEC_DISP%ROWTYPE;
6122: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6123: l_sample_spec_disp_in GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6124: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6125: l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
6126:
6160: -- Fetch Error.
6161: RAISE e_sampling_event_fetch_error;
6162: END IF;
6163:
6164: -- Check if we already have a record in GMD_EVENT_SPEC_DISP
6165: l_event_spec_disp_in.event_spec_disp_id := get_current_event_spec_disp_id(
6166: l_sampling_event.sampling_event_id);
6167: -- Get the event spec disp record
6168: IF NOT (GMD_EVENT_SPEC_DISP_PVT.fetch_row(
6164: -- Check if we already have a record in GMD_EVENT_SPEC_DISP
6165: l_event_spec_disp_in.event_spec_disp_id := get_current_event_spec_disp_id(
6166: l_sampling_event.sampling_event_id);
6167: -- Get the event spec disp record
6168: IF NOT (GMD_EVENT_SPEC_DISP_PVT.fetch_row(
6169: p_event_spec_disp => l_event_spec_disp_in,
6170: x_event_spec_disp => l_event_spec_disp)
6171: )
6172: THEN
6442: svr.in_spec_lot_status_id,
6443: svr.out_of_spec_lot_status_id,
6444: s.organization_id
6445: FROM gmd_sampling_events se,
6446: gmd_event_spec_disp esd,
6447: gmd_sample_spec_disp ssd,
6448: gmd_samples s,
6449: gmd_com_spec_vrs_vl svr
6450: WHERE s.sample_id = p_sample_id
6564:
6565: -- Select COUNT of Tests with Evaluation other then ACCEPT
6566: SELECT count(1)
6567: INTO l_count_with_spec
6568: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6569: gmd_spec_tests_b st
6570: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6571: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6572: AND sr.result_id = r.result_id
6653:
6654: -- Select COUNT of Tests with Evaluation other then ACCEPT
6655: SELECT count(1)
6656: INTO l_count_with_spec
6657: FROM gmd_event_spec_disp esd, gmd_results r, gmd_spec_results sr,
6658: gmd_spec_tests_b st
6659: WHERE esd.event_spec_disp_id = l_sample_rec.event_spec_disp_id
6660: AND esd.event_spec_disp_id = sr.event_spec_disp_id
6661: AND sr.result_id = r.result_id
6859: svr.control_lot_attrib_ind,
6860: svr.in_spec_lot_status_id,
6861: svr.out_of_spec_lot_status_id,
6862: svr.organization_id
6863: FROM gmd_event_spec_disp esd,
6864: gmd_com_spec_vrs_vl svr,
6865: gmd_composite_spec_disp csd
6866: WHERE esd.event_spec_disp_id = p_event_spec_disp_id
6867: AND esd.spec_used_for_lot_attrib_ind = 'Y'
6900: l_reason_id gmd_quality_config.transaction_reason_id%TYPE;
6901:
6902:
6903: /*
6904: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6905: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6906: l_results GMD_RESULTS%ROWTYPE;
6907: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
6908:
6905: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6906: l_results GMD_RESULTS%ROWTYPE;
6907: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
6908:
6909: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6910: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6911: l_out_results GMD_RESULTS%ROWTYPE;
6912: */
6913:
6906: l_results GMD_RESULTS%ROWTYPE;
6907: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
6908:
6909: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6910: l_out_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6911: l_out_results GMD_RESULTS%ROWTYPE;
6912: */
6913:
6914: -- Exception
7041: -- THE FOLLOWING
7042:
7043: SELECT count(1)
7044: INTO l_count_with_spec
7045: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7046: gmd_composite_results cr, gmd_spec_tests_b st
7047: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7048: AND csd.latest_ind = 'Y'
7049: AND csd.event_spec_disp_id = esd.event_spec_disp_id
7057: ;
7058:
7059: SELECT count(1)
7060: INTO l_count_wo_spec
7061: FROM gmd_composite_spec_disp csd, gmd_event_spec_disp esd,
7062: gmd_composite_results cr
7063: WHERE csd.event_spec_disp_id = l_event_spec_disp_id
7064: AND csd.latest_ind = 'Y'
7065: AND csd.event_spec_disp_id = esd.event_spec_disp_id