[Home] [Help]
3741: -- M. Grosser 08-Nov-2002 Added calls to spec matching functions to see if
3742: -- there is a valid spec for samples with no
3743: -- results
3744: -- B. Stone 23-Jun-2003 Added column SAMPLE_TYPE to insert for Patch K
3745: -- to table GMD_SAMPLES
3746: -- create with default value of 'I'
3747: -- B. Stone 3-Jan-2004 Bug 3376111; Samples with Results containing
3748: -- valid values for qc_spec_id were not migrating
3749: -- with a spec. Remove qc_rec_type from cursor
3765: l_disposition VARCHAR2(3);
3766: l_rec_count NUMBER := 0;
3767: l_event_spec_disp_id NUMBER;
3768: l_dup_count NUMBER;
3769: l_sample_no GMD_SAMPLES.SAMPLE_NO%TYPE;
3770: l_supplier_id NUMBER;
3771: l_supplier_site_id NUMBER;
3772: l_spec_type VARCHAR2(2);
3773: l_message_data VARCHAR2(2000);
3966: where table_name='GMD_SAMPLING_EVENTS'
3967: and column_name='SAMPLE_TYPE'
3968: and owner = l_gmd;
3969:
3970: -- B3883674 Refer to GMD_SAMPLES for sample_type column
3971: CURSOR c_patch_level2 IS
3972: SELECT 'K+'
3973: from all_tab_columns
3974: where table_name='GMD_SAMPLES'
3970: -- B3883674 Refer to GMD_SAMPLES for sample_type column
3971: CURSOR c_patch_level2 IS
3972: SELECT 'K+'
3973: from all_tab_columns
3974: where table_name='GMD_SAMPLES'
3975: and column_name='SAMPLE_TYPE'
3976: and owner = l_gmd;
3977:
3978: -- PK Bug 4898620
4016: OPEN c_patch_level1;
4017: FETCH c_patch_level1 INTO l_sampling_events_level;
4018: CLOSE c_patch_level1;
4019:
4020: -- B3883674 Fetch sample_type for GMD_SAMPLES
4021: OPEN c_patch_level2;
4022: FETCH c_patch_level2 INTO l_samples_level; --l_patch_level;
4023: CLOSE c_patch_level2;
4024:
4183: IF l_samples_level IS NULL THEN
4184: -- IF l_patch_level IS NULL THEN
4185:
4186: -- Customer is on J
4187: l_sql_stmt2 := 'INSERT INTO gmd_samples'
4188: || '('
4189: ||'sample_id,'
4190: ||'orgn_code,'
4191: ||'sample_no,'
4342: ||' ) ';
4343:
4344: ELSE
4345: -- Customer is on K
4346: l_sql_stmt2 := 'INSERT INTO gmd_samples'
4347: || '('
4348: ||'sample_id,'
4349: ||'orgn_code,'
4350: ||'sample_no,'
5275:
5276: /* Retrieve sample_event_spec_disp_id, spec_id using sample id */
5277: CURSOR c_get_ids IS
5278: SELECT d.event_spec_disp_id, d.spec_id
5279: FROM gmd_event_spec_disp d, gmd_samples s
5280: WHERE d.sampling_event_id = s.sampling_event_id and
5281: s.sample_id = rslt_rec.sample_id;
5282: id_rec c_get_ids%ROWTYPE;
5283:
5777: /* Retrieve sample information for samples that have a valid spec */
5778: /* but no results */
5779: CURSOR c_get_no_results IS
5780: SELECT *
5781: FROM gmd_samples s
5782: WHERE delete_mark= 0 AND
5783: NOT EXISTS (SELECT 's' from gmd_results r
5784: where s.sample_id = r.sample_id);
5785: nores_rec GMD_SAMPLES%ROWTYPE;
5781: FROM gmd_samples s
5782: WHERE delete_mark= 0 AND
5783: NOT EXISTS (SELECT 's' from gmd_results r
5784: where s.sample_id = r.sample_id);
5785: nores_rec GMD_SAMPLES%ROWTYPE;
5786:
5787: CURSOR c_check_validity_rule (psampling_event_id NUMBER) IS
5788: SELECT original_spec_vr_id
5789: FROM gmd_sampling_events
5793: CURSOR c_ip_samples is
5794: SELECT /*+ INDEX(ESD GMD_EVENT_SPEC_DISP_N1) */
5795: s.sample_id ip_sample, esd.sampling_event_id ip_sampling_event,
5796: esd.event_spec_disp_id ip_event_spec
5797: FROM gmd_samples s,
5798: gmd_event_spec_disp esd
5799: WHERE S.sampling_event_id = ESD.sampling_event_id
5800: AND S.SAMPLE_DISPOSITION = '2I'
5801: and NOT EXISTS
5914: FOR l_ip_samples IN c_ip_samples LOOP
5915: /* OPEN c_cnt_results(l_ip_samples.ip_sample);
5916: FETCH c_cnt_results INTO l_temp;
5917: IF c_cnt_results%NOTFOUND THEN */
5918: update gmd_samples
5919: set sample_disposition = '3C'
5920: where sample_id = l_ip_samples.ip_sample;
5921: update gmd_sample_spec_disp
5922: set disposition = '3C'