[Home] [Help]
108: --| |
109: --| LeAta Jackson 07-Nov-2002 Per Karen, no tester id if no result |
110: --| |
111: --| Susan Feinstein 10-Mar-2003 allow retained samples to save record|
112: --| in gmd_sample_spec_disp table |
113: --| |
114: --| Chetan Nagar 01-Apr-2002 Added exclude_ind column condition |
115: --| in select for table - gmd_spec_tests_b |
116: --| Brenda Stone 21-Aug-2003 Changed cursor c_vrs_vw; removed the |
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
138: ) IS
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:
208: l_in_sampling_event gmd_sampling_events%ROWTYPE;
281: -- DESC FLEX Enhancement
282:
283: -- Check that sample is not "Retain"
284: -- Bug 2790099: Retained samples still need an entry in
285: -- GMD_SAMPLE_SPEC_DISP table.
286: -- IF (p_sample.sample_disposition = '0RT') THEN
287: -- Well, if you just want to retain the sample then
288: -- why should I create the results rows. Abort.
289:
413: -- Migration is the ONLY valid instance where we can skip inserting into
414: -- sample_spec_disp.
415:
416: IF (p_migration IS NULL OR p_migration <> 'Y')THEN
417: -- Create a record in GMD_SAMPLE_SPEC_DISP
418:
419: -- Construct the record
420: l_sample_spec_disp.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
421: l_sample_spec_disp.sample_id := p_sample.sample_id;
433: l_sample_spec_disp.disposition := '1P';
434: END IF; -- check for retained sample
435:
436: -- We are ready for insert, so then lets do it.
437: IF NOT(gmd_sample_spec_disp_pvt.Insert_Row(
438: p_sample_spec_disp => l_sample_spec_disp)
439: )
440: THEN
441: -- Insert Error
450: -- rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
451:
452:
453: -- Bug 2790099: Retained samples still need an entry in
454: -- GMD_SAMPLE_SPEC_DISP table.
455: -- Bug 3079877: Planned samples should not get an event disp record
456: IF (l_event_spec_disp.spec_id IS NOT NULL)
457: AND (p_sample.sample_disposition <> '0RT')
458: AND (p_sample.sample_disposition <> '0PL') THEN
659:
660: END IF; -- We have the Spec from Sampling Event
661:
662: -- Bug 2790099: Retained samples still need an entry in
663: -- GMD_SAMPLE_SPEC_DISP table.
664: -- Bug 3079877: iPlanning samples do not need to be recomposited
665: IF (p_sample.sample_disposition <> '0RT')
666: AND (p_sample.sample_disposition <> '0PL') THEN
667: -- Since we altered the Sampling Event set the recomposite_flag to 'Y'
1342:
1343: -- Begin 3903309
1344: CURSOR c_get_sample_spec_disp (p_sample_id number) is
1345: SELECT disposition
1346: FROM gmd_sample_spec_disp
1347: WHERE sample_id = p_sample_id
1348: AND event_spec_disp_id = p_event_spec_disp_id ;
1349:
1350: l_sample_spec_disp varchar2(3) ;
1695: IF (l_test_added_flag AND l_sample_spec_disp <> '1P') THEN
1696: -- Now that we added a new test to the sample, the
1697: -- disposition of the sample must be set back to "In Progress"
1698:
1699: -- B3005589 The sample disposition is stored in gmd_sample_spec_disp.
1700: -- So the following update is not required.
1701: -- UPDATE gmd_samples
1702: -- SET sample_disposition = '2I',
1703: -- last_updated_by = l_user_id,
1706: -- ;
1707:
1708: -- Set the disposition of the sample spec disp
1709: -- back to "In Progress"
1710: UPDATE gmd_sample_spec_disp
1711: SET disposition = '2I',
1712: last_updated_by = l_user_id,
1713: last_update_date = l_date
1714: WHERE event_spec_disp_id = p_event_spec_disp_id
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: --| |
2008: --| PARAMETERS : |
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:
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:
2163: l_in_sample GMD_SAMPLES%ROWTYPE;
2292:
2293: END IF;
2294: CLOSE c_event_spec;
2295:
2296: -- Create a record in GMD_SAMPLE_SPEC_DISP for the Target Spec
2297:
2298: -- Construct the record
2299: l_sample_spec_disp.EVENT_SPEC_DISP_ID := l_event_spec_disp.event_spec_disp_id;
2300: l_sample_spec_disp.SAMPLE_ID := l_sample.sample_id;
2305: l_sample_spec_disp.LAST_UPDATE_DATE := l_date;
2306: l_sample_spec_disp.LAST_UPDATED_BY := l_user_id;
2307:
2308: -- We are ready for insert, so then lets do it.
2309: IF NOT(GMD_SAMPLE_SPEC_DISP_PVT.Insert_Row(
2310: p_sample_spec_disp => l_sample_spec_disp)
2311: )
2312: THEN
2313: -- Insert Error
2314: RAISE e_sample_spec_disp_insert_err;
2315: END IF;
2316:
2317: IF (l_debug = 'Y') THEN
2318: gmd_debug.put_line(' A new record is created in GMD_SAMPLE_SPEC_DISP.');
2319: gmd_debug.put_line(' Now duplicate all current GMD_SPEC_RESULTS under new Spec.');
2320: END IF;
2321:
2322: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
3493: --| |
3494: --| HISTORY |
3495: --| Chetan Nagar 04-Sep-2002 Created. |
3496: --| Chetan Nagar 09-Dec-2002 Changed disposition from gmd_samples|
3497: --| To gmd_sample_spec_disp. |
3498: --| |
3499: --+========================================================================+
3500: -- End of comments
3501:
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'
3518: AND esd.event_spec_disp_id = ssd.event_spec_disp_id
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:
6127: -- Exceptions
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:
6127: -- Exceptions
6128: e_sample_fetch_error EXCEPTION;
6176:
6177: -- Get the sample spec record
6178: l_sample_spec_disp_in.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
6179: l_sample_spec_disp_in.sample_id := l_sample.sample_id;
6180: IF NOT (GMD_SAMPLE_SPEC_DISP_PVT.fetch_row(
6181: p_sample_spec_disp => l_sample_spec_disp_in,
6182: x_sample_spec_disp => l_sample_spec_disp)
6183: )
6184: THEN
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
6451: AND s.sampling_event_id = se.sampling_event_id
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:
6909: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7195:
7196: PROCEDURE update_exptest_value_null
7197: (p_exp_ref_test_id IN gmd_qc_tests_b.test_id%TYPE
7198: , p_sample_id IN gmd_samples.sample_id%TYPE
7199: , p_event_spec_disp_id IN gmd_sample_spec_disp.event_spec_disp_id%TYPE
7200: , x_return_status OUT NOCOPY VARCHAR2
7201: )
7202: --Start of comments
7203: --+========================================================================+
7225: WHERE expression_ref_test_id = l_exp_ref_test_id ;
7226:
7227: CURSOR exp_tests_need_calc ( l_sample_id gmd_samples.sample_id%TYPE,
7228: l_test_id gmd_qc_tests_b.test_id%TYPE,
7229: l_event_spec_disp_id gmd_sample_spec_disp.event_spec_disp_id%TYPE)
7230: IS
7231: SELECT r.result_id
7232: FROM gmd_results r, gmd_spec_results sr
7233: WHERE r.result_id = sr.result_id