1 PACKAGE BODY GMD_QM_VALIDATE_FIX AS
2 /* $Header: GMDQVADB.pls 120.0 2005/05/26 01:06:25 appldev noship $ */
3
4 PROCEDURE POPULATE_SPEC_HEADER IS
5 cursor get_nextval is
6 select GMD_QC_SPEC_HDR_ID_S.nextval from dual;
7
8 cursor get_spec IS
9 select *
10 from qc_spec_mst
11 order by orgn_code, whse_code, location, lot_id,
12 cust_id, order_org_id, ship_to_site_id, vendor_id,
13 batch_id, formula_id, formulaline_id, routing_id, routingstep_id,
14 charge, oprn_id, item_id, spec_hdr_id
15 for update of spec_hdr_id NOWAIT;
16
17 seq_id NUMBER;
18 l_o qc_spec_mst.orgn_code%TYPE;
19 l_w qc_spec_mst.whse_code%TYPE;
20 l_l qc_spec_mst.location%TYPE;
21 l_i qc_spec_mst.item_id%TYPE := -1;
22 l_lot qc_spec_mst.lot_id%TYPE ;
23 l_c qc_spec_mst.cust_id%TYPE;
24 l_ou qc_spec_mst.order_org_id%TYPE;
25 l_sh qc_spec_mst.ship_to_site_id%TYPE;
26 l_v qc_spec_mst.vendor_id%TYPE;
27 l_b qc_spec_mst.batch_id%TYPE;
28 l_f qc_spec_mst.formula_id%TYPE;
29 l_fl qc_spec_mst.formulaline_id%TYPE;
30 l_r qc_spec_mst.routing_id%TYPE;
31 l_rs qc_spec_mst.routingstep_id%TYPE;
32 l_ch qc_spec_mst.charge%TYPE;
33 l_op qc_spec_mst.oprn_id%TYPE;
34
35 BEGIN
36
37 /******* Update all 3 main qc tables with QC_REC_TYPE ********/
38
39
40
41 -- CR we need all the following stmt to update the tables to 'Z' without any
42 -- condition
43 -- Bug 3697857; Added Where clause where qc_rec_type is NULL
44
45 update qc_spec_mst
46 set qc_rec_type = 'Z'
47 where qc_rec_type is NULL
48 ;
49
50 update qc_smpl_mst
51 set qc_rec_type = 'Z'
52 where qc_rec_type is NULL
53 ;
54
55 update qc_rslt_mst
56 set qc_rec_type = 'Z'
57 where qc_rec_type is NULL
58 ;
59
60
61 /******* Update spec table with spec_hdr_id ********/
62 /******* This id associates all the assays ********/
63 /******* in a spec together. ********/
64
65
66 /*** Should not matter if this is run before or after cust_id migration ***/
67 /*** removed WHERE clause in cursor GET_SPEC. If this procedure is run ***/
68 /*** more than once, all rows have to be considered in cases where ***/
69 /*** an assay was added to a spec - existing assays had hdr id, new ***/
70 /*** assay did not. (This should not happen, unless a user runs this ***/
71 /*** procedure the 1st time, then allows users to enter data before ***/
72 /*** the new versions of the forms are implemented.) ***/
73 /*** ***/
74 /*** This procedure should NOT be run after the new forms are implemented, ***/
75 /*** if users have entered order-specific customer specs. ***/
76
77
78
79 for each_spec in get_spec LOOP
80 IF each_spec.spec_hdr_id IS NULL THEN
81 -- this procedure must be re-runable. Only set spec_hdr_id
82 -- for rows which do not already have a valid hdr id.
83
84 -- dbms_output.put_line ('l ' || l_lot || ' ' || each_spec.lot_id
85 -- ||' c ' || l_c || ' ' || each_spec.cust_id
86 -- ||' s ' || seq_id);
87
88 IF (l_o <> each_spec.orgn_code OR (l_o is not null and
89 each_spec.orgn_code is null))
90 OR (l_w <> each_spec.whse_code OR (l_w is not null and
91 each_spec.whse_code is null))
92 OR (l_l <> each_spec.location OR (l_l is not null and
93 each_spec.location is null))
94 OR (l_i <> each_spec.item_id OR (l_i is not null and
95 each_spec.item_id is null))
96 OR (l_lot <> each_spec.lot_id OR (l_lot is not null and
97 each_spec.lot_id is null))
98 OR (l_c <> each_spec.cust_id OR (l_c is not null and
99 each_spec.cust_id is null))
100 OR (l_ou <> each_spec.order_org_id OR (l_ou is not null and
101 each_spec.order_org_id is null))
102 OR (l_sh <> each_spec.ship_to_site_id OR (l_sh is not null and
103 each_spec.ship_to_site_id is null))
104 OR (l_v <> each_spec.vendor_id OR (l_v is not null and
105 each_spec.vendor_id is null))
106 OR (l_b <> each_spec.batch_id OR (l_b is not null and
107 each_spec.batch_id is null))
108 OR (l_f <> each_spec.formula_id OR (l_f is not null and
109 each_spec.formula_id is null))
110 OR (l_fl <> each_spec.formulaline_id OR (l_fl is not null and
111 each_spec.formulaline_id is null))
112 OR (l_r <> each_spec.routing_id OR (l_r is not null and
113 each_spec.routing_id is null))
114 OR (l_rs <> each_spec.routingstep_id OR (l_rs is not null and
115 each_spec.routingstep_id is null))
116 OR (l_ch <> each_spec.charge OR (l_ch is not null and
117 each_spec.charge is null))
118 OR (l_op <> each_spec.oprn_id OR (l_op is not null and
119 each_spec.oprn_id is null))
120 THEN
121 OPEN get_nextval;
122 FETCH get_nextval INTO seq_id;
123 CLOSE get_nextval;
124 END IF; -- end if current header is diff from prev header
125 ELSE
126 -- if somehow, an assay was added to an existing spec,
127 -- no need to get a new spec hdr id, just update the new assay row
128 -- with the same spec hdr id as the other assays within the same spec.
129 -- The IF statement around the UPDATE will take care of only updating
130 -- rows which do not have a hdr id yet.
131
132 seq_id := each_spec.spec_hdr_id;
133
134 END IF; -- end if current spec hdr id is IS NULL
135 l_o := each_spec.orgn_code;
136 l_w := each_spec.whse_code;
137 l_l := each_spec.location;
138 l_i := each_spec.item_id;
139 l_lot := each_spec.lot_id;
140 l_c := each_spec.cust_id;
141 l_ou := each_spec.order_org_id;
142 l_sh := each_spec.ship_to_site_id;
143 l_v := each_spec.vendor_id;
144 l_b := each_spec.batch_id;
145 l_f := each_spec.formula_id;
146 l_fl := each_spec.formulaline_id;
147 l_r := each_spec.routing_id;
148 l_rs := each_spec.routingstep_id;
149 l_ch := each_spec.charge;
150 l_op := each_spec.oprn_id;
151
152 IF each_spec.spec_hdr_id IS NULL THEN
153 update qc_spec_mst
154 set spec_hdr_id = seq_id
155 where CURRENT OF get_spec;
156 END IF; -- if spec hdr id is NULL , give it a valid value.
157 END LOOP;
158
159
160 end POPULATE_SPEC_HEADER;
161
162 PROCEDURE VALIDATION_FIX (p_migration_id in NUMBER DEFAULT NULL,
163 p_data_fix IN BOOLEAN DEFAULT FALSE,
164 x_return_status OUT NOCOPY VARCHAR2) IS
165 /*+=======================================================================+
166 | DESGCRIPTION
167 | Validation script for QM
168 | HISTORY
169 | 19-Mar-2004 Manish Gupta CREATED
170 | The purpose of this script is to run it before migrating the data
171 | so that data which is there can be rectified before running the
172 | migration.
173 | This procedure can run in 2 modes:-
174 | 1. Datafix.
175 | 2. Validation.
176 -- B.Stone 23-Apr-2004
177 -- Bug 3587546; Changed to only mark the row with the from_date > to_date
178 -- to not migrate, instead of all the spec_hdr_id's qc_spec_id's
179 -- along with the corrsponding samples and results.
180 |
181 *=======================================================================*/
182
183 CURSOR c_sysdate IS
184 SELECT sysdate from dual;
185
186 CURSOR c_22hrs IS
187 SELECT sysdate - 80000 / 86400 from dual;
188
189 -- Bug 3542894
190 CURSOR c_spec_less_from_date IS
191 SELECT a.spec_hdr_id,
192 a.qc_spec_id,
193 a.from_date,
194 a.to_date
195 FROM qc_spec_mst a
196 WHERE migration_status is NULL
197 AND a.from_date > a.to_date;
198
199 CURSOR c_samples_date(p_qc_spec_id NUMBER) IS
200 SELECT min(a.sample_date) min_date, max(a.sample_date) max_date
201 FROM qc_smpl_mst a, qc_rslt_mst b
202 WHERE a.sample_id = b.sample_id
203 AND b.qc_spec_id = p_qc_spec_id;
204 -- end Bug 3542894
205
206 CURSOR c_specs IS
207 SELECT a.spec_hdr_id,
208 a.qc_spec_id l_a_qc_spec_id,
209 a.qcassy_typ_id,
210 a.assay_code,
211 a.to_date,
212 b.from_date,
213 b.to_date l_b_to_date,
214 b.qc_spec_id l_b_qc_spec_id
215 FROM qc_spec_mst a,
216 qc_spec_mst b
217 WHERE a.migration_status is NULL
218 AND b.migration_status is NULL
219 AND a.spec_hdr_id = b.spec_hdr_id
220 AND a.QC_SPEC_ID <> b.QC_SPEC_ID
221 AND a.QCASSY_TYP_ID = b.QCASSY_TYP_ID
222 AND b.from_date <= a.to_date
223 -- CN
224 AND b.from_date >= a.from_date
225 /*GROUP BY a.spec_hdr_id, a.qcassy_typ_id,
226 a.assay_code, a.to_date,
227 b.from_date,
228 b.qc_spec_id*/
229 order by a.spec_hdr_id,a.qcassy_typ_id;
230
231
232
233 CURSOR c_samples_a(p_from_date DATE,
234 p_to_date DATE,
235 p_a_qc_spec_id NUMBER) IS
236 SELECT max(a.sample_date) max_sample_date, count(*) cnt
237 FROM qc_smpl_mst a, qc_rslt_mst b
238 WHERE a.sample_date between p_from_date and p_to_date
239 AND a.sample_id = b.sample_id
240 AND b.qc_spec_id = p_a_qc_spec_id;
241
242
243 CURSOR c_samples_b(p_from_date DATE,
244 p_to_date DATE,
245 p_b_qc_spec_id NUMBER) IS
246 SELECT min(a.sample_date) min_sample_date, count(*) cnt
247 FROM qc_smpl_mst a, qc_rslt_mst b
248 WHERE a.sample_date between p_from_date and p_to_date
249 AND a.sample_id = b.sample_id
250 AND b.qc_spec_id = p_b_qc_spec_id;
251
252 CURSOR c_null_vendor_id_spec IS
253 SELECT sp.qc_spec_id, sp.vendor_id
254 FROM qc_spec_mst sp, po_vend_mst v
255 WHERE sp.vendor_id IS NOT NULL
256 AND sp.vendor_id = v.vendor_id
257 AND v.of_vendor_id IS NULL
258 AND sp.migration_status IS NULL;
259
260 CURSOR c_deleted_w_results (p_sysdate date) IS
261 SELECT sp.qc_spec_id dlt_qc_spec_id
262 FROM qc_spec_mst sp
263 WHERE sp.delete_mark = 1
264 AND sp.migration_status is null
265 and sp.to_date > p_sysdate; -- CR Use the variable for
266 -- SYSDATE and not nested SQL
267
268
269 qc_in_a boolean:= false ;
270 qc_in_b boolean:= false;
271 qc_repeat_flag boolean := true;
272 qc_max_dup_counter pls_integer :=0;
273 l_a_min_date DATE;
274 l_a_max_date DATE;
275 l_b_min_date DATE;
276 l_b_max_date DATE;
277 is_total_overlap BOOLEAN;
278 unable_to_resolve BOOLEAN;
279 l_data_fix BOOLEAN:=FALSE;
280 mig_name VARCHAR2(100);
281 migration_id NUMBER;
282 l_position PLS_INTEGER;
283 l_sysdate DATE;
284 l_22hrs DATE;
285
286
287 BEGIN
288 l_position:= 5;
289 l_data_fix := p_data_fix;
290 --We are making migration_id and p_data_fix flag independent....
291 IF (p_migration_id IS NULL) THEN
292 mig_name := 'QM Migration Data Validation Script';
293 migration_id := GMA_MIGRATION.gma_migration_start( p_app_short_name =>
294 'GMD',
295 p_mig_name =>
296 mig_name);
297 ELSE
298 migration_id := p_migration_id;
299 END IF;
300
301 OPEN c_sysdate;
302 FETCH c_sysdate INTO l_sysdate;
303 CLOSE c_sysdate;
304
305
306 OPEN c_22hrs;
307 FETCH c_22hrs INTO l_22hrs;
308 CLOSE c_22hrs;
309
310
311 /*=============================================== for spec
312 ** overlay=============================================---
313 -- Case 1, Mismatch for test's in results and test.
314 -- eg:-
315 -- gmd_rslt_mst.qcassy_typ_id gmd_rslt_mst.qc_spec_id
316 -- gmd_spec_mst.qc_spec_id gmd_spec_mst.qcassy_typ_id
317 -- 100 1 1
318 -- 100
319 -- 101 2 2
320 -- 101
321 -- 102 3 5(this is
322 -- diff.) 102
323 -- 103 4 4
324 -- 103
325 -- Resolution : Treat these results as additional tests. Updating it in both the
326 -- modes.
327 --==================================================================================================================*/
328
329 l_position :=10;
330 UPDATE qc_rslt_mst r
331 SET old_qc_spec_id = qc_spec_id ,
332 qc_spec_id = NULL
333 WHERE qc_spec_id = (
334 SELECT r.qc_spec_id
335 from qc_spec_mst s
336 WHERE s.qc_spec_id = r.qc_spec_id
337 and s.qcassy_typ_id <> r.qcassy_typ_id);
338
339
340 l_position :=20;
341 /*=================For all the tests deduct one second from from_date and add to
342 ** date to to date====/
343 -- Case 2. The two tests overlap each other with 2 sec.
344 -- T1 -----------------------
345 | |================> 1 sec. overlap.
346 T1 ------------
347 -- Resolution : Update the tests so that this overlapping is removed.
348 -- Note: NEVER EVER UPDATE old_from_date, old_to_date, updating it in both the
349 -- modes.
350 */
351
352 UPDATE qc_spec_mst
353 SET old_from_date = from_date,
354 old_to_date = to_date,
355 to_date = (to_date - 1/86400),
356 from_date = (from_date+1/86400)
357 WHERE old_from_date IS NULL;
358
359 l_position :=30;
360
361 /* Case 3. Update Migration_status to 'DL' for delete specs with no results
362 ** */
363 UPDATE qc_spec_mst s
364 set migration_status = 'DL'
365 where s.delete_mark = 1
366 and s.migration_status is NULL
367 and not exists (
368 select 1
369 from qc_rslt_mst r
370 where s.qc_spec_id = r.qc_spec_id );
371
372 l_position := 35;
373
374 /* Case 3.5 Update TO_DATE to today's date - 1 day for deleted spec
375 -- with results and TO_DATE > sysdate or today's date */
376
377 FOR l_dlt_specs IN c_deleted_w_results (l_sysdate) LOOP
378 l_position :=35;
379 UPDATE qc_spec_mst
380 SET to_date = l_22hrs
381 -- CR get this into a variable
382 WHERE qc_spec_id = l_dlt_specs.dlt_qc_spec_id;
383 END LOOP;
384
385 /*=============== When there is large overlapping for the same test
386 ** ===========================
387 -- Case 4. The two tests overlap each other with large overlap
388 -- T1 -----------------------
389 | |================> Large overlap.
390 T1 ----------------
391 -- Resolution : depending on sample creation date, increase or decrease or
392 -- decrease from and two dates.
393 If it not possible then flag that <SAMPLE RESULTS> with status
394 OL-Overlap cannot be resolved.
395 */
396 --Bug start 3542894
397 l_position := 51;
398 GMA_MIGRATION.gma_insert_message (
399 p_run_id => migration_id,
400 p_table_name => 'QC_SPEC_MST',
401 p_DB_ERROR => '',
402 p_param1 => '',
403 p_param2 => '',
404 p_param3 => '',
405 p_param4 => '',
406 p_param5 => '',
407 p_message_token => 'LESS_FROM_START',
408 p_message_type => 'LT',
409 p_line_no => '1',
413 FOR l_samples_date IN c_samples_date(l_spec_less_from_date.qc_spec_id) LOOP
410 p_position => l_position,
411 p_base_message => '');
412 FOR l_spec_less_from_date in c_spec_less_from_date LOOP
414 l_position :=52;
415 IF (l_samples_date.min_date IS NOT NULL) THEN
416 IF (l_data_fix) THEN
417 UPDATE qc_spec_mst
418 SET from_date = l_samples_date.min_date,
419 to_date = l_samples_date.max_date
420 WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
421 END IF;
422 GMA_MIGRATION.gma_insert_message (
423 p_run_id => migration_id,
424 p_table_name => 'QC_SPEC_MST',
425 p_DB_ERROR => '',
426 p_param1 => l_spec_less_from_date.qc_spec_id,
427 p_param2 => l_spec_less_from_date.from_date,
428 p_param3 => l_spec_less_from_date.to_date,
429 p_param4 => '',
430 p_param5 => '',
431 p_message_token => 'LESS_FROM_RESOLVED',
432 p_message_type => 'WD',
433 p_line_no => '1',
434 p_position => l_position,
435 p_base_message => '');
436
437 ELSE
438 l_position := 53;
439 -- Bug 3587546; Changed to only mark the row with the from_date > to_date
440 -- to not migrate, instead of all the spec_hdr_id's qc_spec_id's
441 -- along with the corrsponding samples and results.
442 IF (l_data_fix) THEN
443 UPDATE qc_spec_mst
444 SET migration_status = 'WD'
445 WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
446 END IF;
447 GMA_MIGRATION.gma_insert_message (
448 p_run_id => migration_id,
449 p_table_name => 'QC_SPEC_MST',
450 p_DB_ERROR => '',
451 p_param1 => l_spec_less_from_date.qc_spec_id,
452 p_param2 => l_spec_less_from_date.from_date,
453 p_param3 => l_spec_less_from_date.to_date,
454 p_param4 => '',
455 p_param5 => '',
456 p_message_token => 'LESS_FROM_UNRESOLVED',
457 p_message_type => 'WD',
458 p_line_no => '1',
459 p_position => l_position,
460 p_base_message => '');
461
462 END IF;
463 END LOOP;
464 END LOOP;
465 l_position:=59;
466 GMA_MIGRATION.gma_insert_message (
467 p_run_id => migration_id,
468 p_table_name => 'QC_SPEC_MST',
469 p_DB_ERROR => '',
470 p_param1 => '',
471 p_param2 => '',
472 p_param3 => '',
473 p_param4 => '',
474 p_param5 => '',
475 p_message_token => 'LESS_FROM_END',
476 p_message_type => 'WD',
477 p_line_no => '1',
478 p_position => l_position,
479 p_base_message => '');
480 --End bug 3542894
481
482
483 -- B3568239 START
484 -- Check that for the Vendor Spec the spec vendor_id has corresponding
485 -- of_vendor_id
486 FOR l_null_vendor_id_spec IN c_null_vendor_id_spec
487 LOOP
488 -- The spec vendor_id does not have corresponding of_vendor_id
489 GMA_MIGRATION.gma_insert_message (
490 p_run_id => migration_id,
491 p_table_name => 'QC_SPEC_MST',
492 p_DB_ERROR => '',
493 p_param1 => l_null_vendor_id_spec.qc_spec_id,
494 p_param2 => l_null_vendor_id_spec.vendor_id,
495 p_param3 => '',
496 p_param4 => '',
497 p_param5 => '',
498 p_message_token => 'SPEC_VENDOR_INVALID_NOTRESOLVED',
499 p_message_type => 'VI',
500 p_line_no => '1',
501 p_position => l_position,
502 p_base_message => '');
503
504 -- Mark the Spec, Samples, and Results as VI (Vendor Invalid)
505
506 UPDATE qc_spec_mst
507 SET migration_status = 'VI'
508 WHERE migration_status IS NULL
509 AND spec_hdr_id in (SELECT spec_hdr_id
510 FROM qc_spec_mst
511 WHERE qc_spec_id = l_null_vendor_id_spec.qc_spec_id
512 )
513 ;
514
515 UPDATE qc_smpl_mst
516 SET migration_status = 'VI'
517 WHERE migration_status IS NULL
518 AND sample_id IN (SELECT sample_id
519 FROM qc_rslt_mst
520 WHERE qc_spec_id in (SELECT qc_spec_id
521 FROM qc_spec_mst
522 WHERE spec_hdr_id in (select
523 spec_hdr_id
524 from
525 qc_spec_mst
526 where
527 qc_spec_id = l_null_vendor_id_spec.qc_spec_id
528 )
529 )
530 )
531 ;
532
533 UPDATE qc_rslt_mst
534 SET migration_status = 'VI'
535 WHERE migration_status IS NULL
536 AND sample_id in (SELECT sample_id
537 FROM qc_rslt_mst
538 WHERE qc_spec_id in (SELECT qc_spec_id
542 from
539 FROM qc_spec_mst
540 WHERE spec_hdr_id in (select
541 spec_hdr_id
543 qc_spec_mst
544 where
545 qc_spec_id = l_null_vendor_id_spec.qc_spec_id
546 )
547 )
548 )
549 ;
550
551 END LOOP;
552 -- B3568239 END
553
554
555 l_position :=60;
556 GMA_MIGRATION.gma_insert_message (
557 p_run_id => migration_id,
558 p_table_name => 'QC_SPEC_MST',
559 p_DB_ERROR => '',
560 p_param1 => '',
561 p_param2 => '',
562 p_param3 => '',
563 p_param4 => '',
564 p_param5 => '',
565 p_message_token => 'OVERLAP_START',
566 p_message_type => 'OL',
567 p_line_no => '1',
568 p_position => l_position,
569 p_base_message => '');
570
571 -- dbms_output.put_line('Before in while...');
572 WHILE (qc_repeat_flag AND qc_max_dup_counter < 10) LOOP
573
574 l_position :=70;
575 qc_repeat_flag := FALSE;
576 qc_max_dup_counter := qc_max_dup_counter +1;
577 --------------------------------
578 --Start Loop for all duplicates
579 --------------------------------
580 FOR l_specs in c_specs LOOP
581 qc_repeat_flag := TRUE;
582
583 l_position :=80;
584
585 qc_in_a := FALSE;
586 qc_in_b := FALSE;
587 is_total_overlap := FALSE;
588 unable_to_resolve := FALSE;
589
590 -- dbms_output.put_line('Going in while...');
591 ---------------------------------------
592 --Is it total overlap???
593 ---------------------------------------
594 IF (l_specs.to_date > l_specs.l_b_to_date) THEN
595 ----------------------------
596 -- Total overlap, mark in OL
597 ----------------------------
598 is_total_overlap := TRUE;
599 l_position :=90;
600 --dbms_output.put_line('Is overlap true is true, no processing exit...');
601 -- CN Just return or continue after marking Spec, Smpl and Rslt
602 GMA_MIGRATION.gma_insert_message (
603 p_run_id => migration_id,
604 p_table_name => 'QC_SPEC_TEST',
605 p_DB_ERROR => '',
606 p_param1 => l_specs.l_a_qc_spec_id,
607 p_param2 => l_specs.l_b_qc_spec_id,
608 p_param3 => l_specs.from_date,
609 p_param4 => l_specs.to_date,
610 p_param5 => '',
611 p_message_token => 'TOTAL_OVERLAP',
612 p_message_type => 'OL',
613 p_line_no => '1',
614 p_position => l_position,
615 p_base_message => 'Qc_spec_ids '||l_specs.l_a_qc_spec_id||' and
616 '||l_specs.l_b_qc_spec_id||' has totally overlapping dates');
617 ELSE
618 --------------------------------------
619 --Start Sample is there for Spec A?
620 --------------------------------------
621 FOR l_a_qc_spec IN c_samples_a(l_specs.from_date,
622 l_specs.to_date,
623 l_specs.l_a_qc_spec_id) LOOP
624 l_position :=100;
625 l_a_max_date := l_a_qc_spec.max_sample_date;
626 IF (l_a_qc_spec.cnt >0) THEN
627 qc_in_a := TRUE;
628 END IF;
629 --dbms_output.put_line('Sample for qc_spec_id A...');
630 EXIT; --Not required as aggregate func. in select
631 END LOOP;
632 --------------------------------------
633 --End Sample is there for spec A?
634 --------------------------------------
635
636
637 --------------------------------------
638 --Start Sample is there for Spec B?
639 --------------------------------------
640 FOR l_b_qc_spec IN c_samples_b(l_specs.from_date,
641 l_specs.to_date,
642 l_specs.l_b_qc_spec_id) LOOP
643 l_b_min_date := l_b_qc_spec.min_sample_date;
644 l_position :=110;
645 IF (l_b_qc_spec.cnt >0) THEN
646 qc_in_b := TRUE;
647 END IF;
648 --dbms_output.put_line('Sample for qc_spec_id B...');
649 EXIT; --Not required as aggreagte func. in select
650 END LOOP;
651 --------------------------------------
652 --End Sample is there for Spec B?
653 --------------------------------------
654
655
656
657 -------------------------------------
658 -- Start Main IF
659 -------------------------------------
660 IF (qc_in_a AND NOT qc_in_b) THEN
661 -------------------------------------
662 -- Only spec A used for samples
663 -- Decrease Spec B's from_date
664 -------------------------------------
665 IF (l_data_fix) THEN
666 UPDATE qc_spec_mst
667 SET from_date = l_specs.to_date + 1/86400
668 WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
669 END IF;
673
670 l_position :=120;
671
672
674 GMA_MIGRATION.gma_insert_message (
675 p_run_id => migration_id,
676 p_table_name => 'QC_SPEC_TEST',
677 p_DB_ERROR => '',
678 p_param1 => l_specs.l_a_qc_spec_id,
679 p_param2 => l_specs.l_b_qc_spec_id,
680 p_param3 => l_specs.from_date,
681 p_param4 => l_specs.to_date,
682 p_param5 => '',
683 p_message_token => 'OVERLAP_RESOLVED',
684 p_message_type => 'OL',
685 p_line_no => '1',
686 p_position => l_position,
687 p_base_message => 'In A not in B');
688
689 ELSIF (NOT qc_in_a AND qc_in_b) THEN
690 -------------------------------------
691 -- Only spec B used for samples
692 -- Decrease Spec A's to_date
693 -------------------------------------
694 IF (l_data_fix) THEN
695 UPDATE qc_spec_mst
696 SET to_date = l_specs.from_date - 1/86400
697 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
698 END IF;
699 l_position :=130;
700
701 GMA_MIGRATION.gma_insert_message (
702 p_run_id => migration_id,
703 p_table_name => 'QC_SPEC_TEST',
704 p_DB_ERROR => '',
705 p_param1 => l_specs.l_a_qc_spec_id,
706 p_param2 => l_specs.l_b_qc_spec_id,
707 p_param3 => l_specs.from_date,
708 p_param4 => l_specs.to_date,
709 p_param5 => '',
710 p_message_token => 'OVERLAP_RESOLVED',
711 p_message_type => 'OL',
712 p_line_no => '1',
713 p_position => l_position,
714 p_base_message => 'Not in A in B');
715
716 ELSIF (NOT qc_in_a and NOT qc_in_b) THEN
717 ---------------------------------------
718 -- No samples for overlapped dates
719 -- Does not matter which you inc. or dec.
720 -- We are:-->
721 -- Descreasing Spec A's to_date
722 -------------------------------------
723 l_position :=140;
724 IF (l_data_fix) THEN
725 UPDATE qc_spec_mst
726 SET to_date = l_specs.from_date - 1/86400
727 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
728 END IF;
729
730 l_position :=150;
731
732 GMA_MIGRATION.gma_insert_message (
733 p_run_id => migration_id,
734 p_table_name => 'QC_SPEC_TEST',
735 p_DB_ERROR => '',
736 p_param1 => l_specs.l_a_qc_spec_id,
737 p_param2 => l_specs.l_b_qc_spec_id,
738 p_param3 => l_specs.from_date,
739 p_param4 => l_specs.to_date,
740 p_param5 => '',
741 p_message_token => 'OVERLAP_RESOLVED',
742 p_message_type => 'OL',
743 p_line_no => '1',
744 p_position => l_position,
745 p_base_message => 'not in A not in B');
746 ELSIF (qc_in_a and qc_in_b) THEN
747 ---------------------------------------------------------
748 -- Both Specs have Samples for overlapped dates
749 ---------------------------------------------------------
750 ---------------------------------------------------------------
751 -- IF max(sample_date) for spec A < min(sample_date) for Spec B
752 -- Overlap is resolved by
753 -- Increasing the from_date of Spec to max(sample_date)
754 -- Decreasing the to_date of the spec to min(sample_date)
755 -- Total overlap automatically taken care of
756 ---------------------------------------------------------------
757 l_position :=160;
758 IF (l_a_max_date < l_b_min_date) THEN
759
760 l_position :=170;
761 IF (l_data_fix) THEN
762 UPDATE qc_spec_mst
763 SET to_date = l_a_max_date
764 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
765
766
767 UPDATE qc_spec_mst
768 SET from_date = l_b_min_date
769 WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
770 END IF;
771
772 l_position :=180;
773
774 GMA_MIGRATION.gma_insert_message (
775 p_run_id => migration_id,
776 p_table_name => 'QC_SPEC_TEST',
777 p_DB_ERROR => '',
778 p_param1 => l_specs.l_a_qc_spec_id,
779 p_param2 => l_specs.l_b_qc_spec_id,
780 p_param3 => l_specs.from_date,
781 p_param4 => l_specs.to_date,
782 p_param5 => '',
783 p_message_token => 'OVERLAP_RESOLVED',
784 p_message_type => 'OL',
785 p_line_no => '1',
786 p_position => l_position,
787 p_base_message => 'In A in B');
788 ELSE
789
790 unable_to_resolve := TRUE;
791 l_position :=190;
792 GMA_MIGRATION.gma_insert_message (
793 p_run_id => migration_id,
794 p_table_name => 'QC_SPEC_TEST',
795 p_DB_ERROR => '',
796 p_param1 => l_specs.l_a_qc_spec_id,
797 p_param2 => l_specs.l_b_qc_spec_id,
798 p_param3 => l_specs.from_date,
799 p_param4 => l_specs.to_date,
800 p_param5 => '',
801 p_message_token => 'NOT_RESOLVED',
802 p_message_type => 'OL',
803 p_line_no => '1',
804 p_position => l_position,
805 p_base_message => 'Samples dates are there in qc_spec_ids
806 '||l_specs.l_a_qc_spec_id||','||l_specs.l_b_qc_spec_id||' for overlapped dates
807 between '||l_specs.from_date||' and '||l_specs.to_date);
808 END IF;
809 END IF;
810 ----------------------
811 --End of main IF
812 ----------------------
813 END IF; -- Total Overlap or NOT
814 -- cannot migrate, update the status to OL and report it when the problem
815 -- cases run...
816 IF (unable_to_resolve OR is_total_overlap) THEN
817 -- IF (l_data_fix) THEN
818 l_position :=200;
819 UPDATE qc_spec_mst
820 SET migration_status = 'OL'
821 WHERE spec_hdr_id in (SELECT spec_hdr_id
822 FROM qc_spec_mst
823 WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
824 l_specs.l_b_qc_spec_id))
825 AND migration_status IS NULL;
826
827 l_position :=220;
828 UPDATE qc_smpl_mst
829 SET migration_status = 'OL'
830 WHERE sample_id IN (SELECT sample_id
831 FROM qc_rslt_mst
832 WHERE qc_spec_id in (SELECT qc_spec_id
833 FROM qc_spec_mst
834 WHERE spec_hdr_id in (select
835 spec_hdr_id
836 from
837 qc_spec_mst
838 where
839 qc_spec_id in
840 (l_specs.l_a_qc_spec_id,
841 l_specs.l_b_qc_spec_id))));
842
843
844 l_position :=240;
845 UPDATE qc_rslt_mst
846 SET migration_status = 'OL'
847 WHERE sample_id in (SELECT sample_id
848 FROM qc_rslt_mst
849 WHERE qc_spec_id in (SELECT qc_spec_id
850 FROM qc_spec_mst
851 WHERE spec_hdr_id in (select
852 spec_hdr_id
853 from
854 qc_spec_mst
855 where
856 qc_spec_id in
857 (l_specs.l_a_qc_spec_id,
858 l_specs.l_b_qc_spec_id))));
859
860
861 --END IF;
862
863 END IF;
864 -------------------
865 --Unable to resolve
866 -------------------
867
868 END LOOP;
869 -------------------------------------
870 --End Loop for all duplicates
871 --------------------------------
872 END LOOP;
873 --------------------------------
874 -- End 10 iteration while loop
875 --------------------------------
876 EXCEPTION
877 WHEN OTHERS THEN
878 x_return_status := 'U';
879 GMA_MIGRATION.gma_insert_message (
880 p_run_id => migration_id,
881 p_table_name => 'QC_SPEC_MST',
882 p_DB_ERROR => sqlerrm,
883 p_param1 => '',
884 p_param2 => '',
885 p_param3 => '',
886 p_param4 => '',
887 p_param5 => '',
888 p_message_token => 'GMA_MIGRATION_DB_ERROR',
889 p_message_type => 'E',
890 p_line_no => '1',
891 p_position => l_position,
892 p_base_message => 'Validation DB ERROR '||sqlerrm);
893 END VALIDATION_FIX;
894 end GMD_QM_VALIDATE_FIX;
[Home] [Help]