[Home] [Help]
152: --| |
153: --| LeAta Jackson 07-Nov-2002 Per Karen, no tester id if no result |
154: --| |
155: --| Susan Feinstein 10-Mar-2003 allow retained samples to save record|
156: --| in gmd_sample_spec_disp table |
157: --| |
158: --| Chetan Nagar 01-Apr-2002 Added exclude_ind column condition |
159: --| in select for table - gmd_spec_tests_b |
160: --| Brenda Stone 21-Aug-2003 Changed cursor c_vrs_vw; removed the |
176: (
177: p_sample IN GMD_SAMPLES%ROWTYPE
178: , p_migration IN VARCHAR2
179: , x_event_spec_disp OUT NOCOPY GMD_EVENT_SPEC_DISP%ROWTYPE
180: , x_sample_spec_disp OUT NOCOPY GMD_SAMPLE_SPEC_DISP%ROWTYPE
181: , x_results_tab OUT NOCOPY GMD_API_PUB.gmd_results_tab
182: , x_spec_results_tab OUT NOCOPY GMD_API_PUB.gmd_spec_results_tab
183: , x_return_status OUT NOCOPY VARCHAR2
184: ) IS
248: l_meaning VARCHAR2(80);
249:
250: l_sampling_event gmd_sampling_events%ROWTYPE;
251: l_event_spec_disp gmd_event_spec_disp%ROWTYPE;
252: l_sample_spec_disp gmd_sample_spec_disp%ROWTYPE;
253: l_results gmd_results%ROWTYPE;
254: l_spec_results gmd_spec_results%ROWTYPE;
255:
256: l_in_sampling_event gmd_sampling_events%ROWTYPE;
329: -- DESC FLEX Enhancement
330:
331: -- Check that sample is not "Retain"
332: -- Bug 2790099: Retained samples still need an entry in
333: -- GMD_SAMPLE_SPEC_DISP table.
334: -- IF (p_sample.sample_disposition = '0RT') THEN
335: -- Well, if you just want to retain the sample then
336: -- why should I create the results rows. Abort.
337:
461: -- Migration is the ONLY valid instance where we can skip inserting into
462: -- sample_spec_disp.
463:
464: IF (p_migration IS NULL OR p_migration <> 'Y')THEN
465: -- Create a record in GMD_SAMPLE_SPEC_DISP
466:
467: -- Construct the record
468: l_sample_spec_disp.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
469: l_sample_spec_disp.sample_id := p_sample.sample_id;
481: l_sample_spec_disp.disposition := '1P';
482: END IF; -- check for retained sample
483:
484: -- We are ready for insert, so then lets do it.
485: IF NOT(gmd_sample_spec_disp_pvt.Insert_Row(
486: p_sample_spec_disp => l_sample_spec_disp)
487: )
488: THEN
489: -- Insert Error
498: -- rows in GMD_RESULTS and GMD_SPEC_RESULTS for all the tests
499:
500:
501: -- Bug 2790099: Retained samples still need an entry in
502: -- GMD_SAMPLE_SPEC_DISP table.
503: -- Bug 3079877: Planned samples should not get an event disp record
504: IF (l_event_spec_disp.spec_id IS NOT NULL)
505: AND (p_sample.sample_disposition <> '0RT')
506: AND (p_sample.sample_disposition <> '0PL') THEN
712:
713: END IF; -- We have the Spec from Sampling Event
714:
715: -- Bug 2790099: Retained samples still need an entry in
716: -- GMD_SAMPLE_SPEC_DISP table.
717: -- Bug 3079877: iPlanning samples do not need to be recomposited
718: IF (p_sample.sample_disposition <> '0RT')
719: AND (p_sample.sample_disposition <> '0PL') THEN
720: -- Since we altered the Sampling Event set the recomposite_flag to 'Y'
1417:
1418: -- Begin 3903309
1419: CURSOR c_get_sample_spec_disp (p_sample_id number) is
1420: SELECT disposition
1421: FROM gmd_sample_spec_disp
1422: WHERE sample_id = p_sample_id
1423: AND event_spec_disp_id = p_event_spec_disp_id ;
1424:
1425: l_sample_spec_disp varchar2(3) ;
1777: IF (l_test_added_flag AND l_sample_spec_disp <> '1P') THEN
1778: -- Now that we added a new test to the sample, the
1779: -- disposition of the sample must be set back to "In Progress"
1780:
1781: -- B3005589 The sample disposition is stored in gmd_sample_spec_disp.
1782: -- So the following update is not required.
1783: -- UPDATE gmd_samples
1784: -- SET sample_disposition = '2I',
1785: -- last_updated_by = l_user_id,
1788: -- ;
1789:
1790: -- Set the disposition of the sample spec disp
1791: -- back to "In Progress"
1792: UPDATE gmd_sample_spec_disp
1793: SET disposition = '2I',
1794: last_updated_by = l_user_id,
1795: last_update_date = l_date
1796: WHERE event_spec_disp_id = p_event_spec_disp_id
2082: --| |
2083: --| This procedure will first create any missing replicate |
2084: --| of the tests for the target spec in gmd_results. Then |
2085: --| create a row in GMD_EVENT_SPEC_DISP and a row in |
2086: --| GMD_SAMPLE_SPEC_DISP and a set of rows in |
2087: --| GMD_SPEC_RESULTS |
2088: --| |
2089: --| |
2090: --| PARAMETERS : |
2160: l_test_id GMD_QC_TESTS.test_id%TYPE;
2161: l_spec_id gmd_spec_tests_b.spec_id%TYPE;
2162:
2163: CURSOR c_evaluation_ind (p_result_id NUMBER, p_event_spec_disp_id NUMBER) IS
2164: SELECT evaluation_ind from gmd_spec_results gsr, gmd_event_spec_disp esd --, gmd_sample_spec_disp ssd
2165: WHERE gsr.result_id=p_result_id
2166: AND esd.event_spec_disp_id=p_event_spec_disp_id
2167: AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
2168:
2167: AND gsr.event_spec_disp_id=esd.event_spec_disp_id;
2168:
2169:
2170: CURSOR c_event_spec_disp_id (p_sample_id NUMBER) IS
2171: SELECT esd.event_spec_disp_id from gmd_event_spec_disp esd, gmd_sample_spec_disp ssd
2172: WHERE ssd.sample_id=p_sample_id
2173: AND esd.event_spec_disp_id=ssd.event_spec_disp_id
2174: AND esd.spec_used_for_lot_attrib_ind = 'Y';
2175:
2242: l_results_tab GMD_API_PUB.gmd_results_tab;
2243: l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
2244:
2245: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
2246: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
2247: l_spec_result GMD_SPEC_RESULTS%ROWTYPE;
2248: l_sample GMD_SAMPLES%ROWTYPE;
2249:
2250: l_in_sample GMD_SAMPLES%ROWTYPE;
2393:
2394: END IF;
2395: CLOSE c_event_spec;
2396:
2397: -- Create a record in GMD_SAMPLE_SPEC_DISP for the Target Spec
2398:
2399: -- Construct the record
2400: l_sample_spec_disp.EVENT_SPEC_DISP_ID := l_event_spec_disp.event_spec_disp_id;
2401: l_sample_spec_disp.SAMPLE_ID := l_sample.sample_id;
2406: l_sample_spec_disp.LAST_UPDATE_DATE := l_date;
2407: l_sample_spec_disp.LAST_UPDATED_BY := l_user_id;
2408:
2409: -- We are ready for insert, so then lets do it.
2410: IF NOT(GMD_SAMPLE_SPEC_DISP_PVT.Insert_Row(
2411: p_sample_spec_disp => l_sample_spec_disp)
2412: )
2413: THEN
2414: -- Insert Error
2419: RAISE e_sample_spec_disp_insert_err;
2420: END IF;
2421:
2422: IF (l_debug = 'Y') THEN
2423: gmd_debug.put_line(' A new record is created in GMD_SAMPLE_SPEC_DISP.');
2424: gmd_debug.put_line(' Now duplicate all current GMD_SPEC_RESULTS under new Spec.'); -- up to here
2425: END IF;
2426:
2427: -- Create a set of records in GMD_SPEC_RESULTS for the target Spec
3662: --| |
3663: --| HISTORY |
3664: --| Chetan Nagar 04-Sep-2002 Created. |
3665: --| Chetan Nagar 09-Dec-2002 Changed disposition from gmd_samples|
3666: --| To gmd_sample_spec_disp. |
3667: --| |
3668: --+========================================================================+
3669: -- End of comments
3670:
3679: -- Bug 3079877: do not composite planning samples
3680: CURSOR c_sample(p_sampling_event_id NUMBER) IS
3681: SELECT s.sample_id
3682: FROM gmd_event_spec_disp esd,
3683: gmd_sample_spec_disp ssd,
3684: gmd_samples s
3685: WHERE esd.sampling_event_id = p_sampling_event_id
3686: AND esd.spec_used_for_lot_attrib_ind = 'Y'
3687: AND esd.event_spec_disp_id = ssd.event_spec_disp_id
6381: l_sampling_event_in GMD_SAMPLING_EVENTS%ROWTYPE;
6382: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
6383: l_event_spec_disp_in GMD_EVENT_SPEC_DISP%ROWTYPE;
6384: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6385: l_sample_spec_disp_in GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6386: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6387: l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
6388:
6389: -- Exceptions
6382: l_sampling_event GMD_SAMPLING_EVENTS%ROWTYPE;
6383: l_event_spec_disp_in GMD_EVENT_SPEC_DISP%ROWTYPE;
6384: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
6385: l_sample_spec_disp_in GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6386: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
6387: l_update_disp_rec GMD_SAMPLES_GRP.update_disp_rec;
6388:
6389: -- Exceptions
6390: e_sample_fetch_error EXCEPTION;
6438:
6439: -- Get the sample spec record
6440: l_sample_spec_disp_in.event_spec_disp_id := l_event_spec_disp.event_spec_disp_id;
6441: l_sample_spec_disp_in.sample_id := l_sample.sample_id;
6442: IF NOT (GMD_SAMPLE_SPEC_DISP_PVT.fetch_row(
6443: p_sample_spec_disp => l_sample_spec_disp_in,
6444: x_sample_spec_disp => l_sample_spec_disp)
6445: )
6446: THEN
6767: svr.out_of_spec_lot_status_id,
6768: s.organization_id
6769: FROM gmd_sampling_events se,
6770: gmd_event_spec_disp esd,
6771: gmd_sample_spec_disp ssd,
6772: gmd_samples s,
6773: gmd_com_spec_vrs_vl svr
6774: WHERE s.sample_id = p_sample_id
6775: AND s.sampling_event_id = se.sampling_event_id
7278:
7279:
7280: /*
7281: l_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7282: l_sample_spec_disp GMD_SAMPLE_SPEC_DISP%ROWTYPE;
7283: l_results GMD_RESULTS%ROWTYPE;
7284: l_spec_results GMD_SPEC_RESULTS%ROWTYPE;
7285:
7286: l_in_event_spec_disp GMD_EVENT_SPEC_DISP%ROWTYPE;
7572:
7573: PROCEDURE update_exptest_value_null
7574: (p_exp_ref_test_id IN gmd_qc_tests_b.test_id%TYPE
7575: , p_sample_id IN gmd_samples.sample_id%TYPE
7576: , p_event_spec_disp_id IN gmd_sample_spec_disp.event_spec_disp_id%TYPE
7577: , x_return_status OUT NOCOPY VARCHAR2
7578: )
7579: --Start of comments
7580: --+========================================================================+
7602: WHERE expression_ref_test_id = l_exp_ref_test_id ;
7603:
7604: CURSOR exp_tests_need_calc ( l_sample_id gmd_samples.sample_id%TYPE,
7605: l_test_id gmd_qc_tests_b.test_id%TYPE,
7606: l_event_spec_disp_id gmd_sample_spec_disp.event_spec_disp_id%TYPE)
7607: IS
7608: SELECT r.result_id
7609: FROM gmd_results r, gmd_spec_results sr
7610: WHERE r.result_id = sr.result_id