1 PACKAGE BODY GMD_QM_VALIDATE_FIX AS
2 /* $Header: GMDQVADB.pls 120.0.12020000.2 2012/07/17 10:13:54 mtou ship $ */
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
302 -- 5727706 add message below
303
304 GMA_COMMON_LOGGING.gma_migration_central_log (
305 p_run_id => migration_id,
306 p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
307 p_message_token => 'GMA_PRE_MIGRATION_STARTED',
308 p_table_name => 'VALIDATION_FIX',
309 p_context => '',
310 p_param1 => NULL,
311 p_param2 => NULL,
312 p_param3 => NULL,
313 p_param4 => NULL,
314 p_param5 => NULL,
315 p_db_error => NULL,
316 p_app_short_name => 'GMD');
317
318
319 OPEN c_sysdate;
320 FETCH c_sysdate INTO l_sysdate;
321 CLOSE c_sysdate;
322
323
324 OPEN c_22hrs;
325 FETCH c_22hrs INTO l_22hrs;
326 CLOSE c_22hrs;
327
328
329 /*=============================================== for spec
330 ** overlay=============================================---
331 -- Case 1, Mismatch for test's in results and test.
332 -- eg:-
333 -- gmd_rslt_mst.qcassy_typ_id gmd_rslt_mst.qc_spec_id
334 -- gmd_spec_mst.qc_spec_id gmd_spec_mst.qcassy_typ_id
335 -- 100 1 1
336 -- 100
337 -- 101 2 2
338 -- 101
339 -- 102 3 5(this is
340 -- diff.) 102
341 -- 103 4 4
342 -- 103
343 -- Resolution : Treat these results as additional tests. Updating it in both the
344 -- modes.
345 --==================================================================================================================*/
346
347 l_position :=10;
348 UPDATE qc_rslt_mst r
349 SET old_qc_spec_id = qc_spec_id ,
350 qc_spec_id = NULL
351 WHERE qc_spec_id = (
352 SELECT r.qc_spec_id
353 from qc_spec_mst s
354 WHERE s.qc_spec_id = r.qc_spec_id
355 and s.qcassy_typ_id <> r.qcassy_typ_id);
356
357
358 l_position :=20;
359 /*=================For all the tests deduct one second from from_date and add to
360 ** date to to date====/
361 -- Case 2. The two tests overlap each other with 2 sec.
362 -- T1 -----------------------
363 | |================> 1 sec. overlap.
364 T1 ------------
365 -- Resolution : Update the tests so that this overlapping is removed.
366 -- Note: NEVER EVER UPDATE old_from_date, old_to_date, updating it in both the
367 -- modes.
368 */
369
370 UPDATE qc_spec_mst
371 SET old_from_date = from_date,
372 old_to_date = to_date,
373 to_date = (to_date - 1/86400),
374 from_date = (from_date+1/86400)
375 WHERE old_from_date IS NULL;
376
377 l_position :=30;
378
379 /* Case 3. Update Migration_status to 'DL' for delete specs with no results
380 ** */
381 UPDATE qc_spec_mst s
382 set migration_status = 'DL'
383 where s.delete_mark = 1
384 and s.migration_status is NULL
385 and not exists (
386 select 1
387 from qc_rslt_mst r
388 where s.qc_spec_id = r.qc_spec_id );
389
390 l_position := 35;
391
392 /* Case 3.5 Update TO_DATE to today's date - 1 day for deleted spec
393 -- with results and TO_DATE > sysdate or today's date */
394
395 FOR l_dlt_specs IN c_deleted_w_results (l_sysdate) LOOP
396 l_position :=35;
397 UPDATE qc_spec_mst
398 SET to_date = l_22hrs
399 -- CR get this into a variable
400 WHERE qc_spec_id = l_dlt_specs.dlt_qc_spec_id;
401 END LOOP;
402
403 /*=============== When there is large overlapping for the same test
404 ** ===========================
405 -- Case 4. The two tests overlap each other with large overlap
406 -- T1 -----------------------
407 | |================> Large overlap.
408 T1 ----------------
409 -- Resolution : depending on sample creation date, increase or decrease or
410 -- decrease from and two dates.
411 If it not possible then flag that <SAMPLE RESULTS> with status
412 OL-Overlap cannot be resolved.
413 */
414 --Bug start 3542894
415 l_position := 51;
416
417
418 /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
419 GMA_MIGRATION.gma_insert_message (
420 p_run_id => migration_id,
421 p_table_name => 'QC_SPEC_MST',
422 p_DB_ERROR => '',
423 p_param1 => '',
424 p_param2 => '',
425 p_param3 => '',
426 p_param4 => '',
427 p_param5 => '',
428 p_message_token => 'LESS_FROM_START', -- pal token not mapped
429 p_message_type => 'LT', -- this is not even used in proc below
430 p_line_no => '1',
431 p_position => l_position,
432 p_base_message => '');
433 */
434
435 GMA_COMMON_LOGGING.gma_migration_central_log (
436 p_run_id => migration_id,
437 p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
438 p_message_token => 'LESS_FROM_START',
439 p_table_name => 'QC_SPEC_MST',
440 p_context => '',
441 p_param1 => NULL,
442 p_param2 => NULL,
443 p_param3 => NULL,
444 p_param4 => NULL,
445 p_param5 => NULL,
446 p_db_error => NULL,
447 p_app_short_name => 'GMD');
448
449
450
451
452
453
454
455 FOR l_spec_less_from_date in c_spec_less_from_date LOOP
456 FOR l_samples_date IN c_samples_date(l_spec_less_from_date.qc_spec_id) LOOP
457 l_position :=52;
458 IF (l_samples_date.min_date IS NOT NULL) THEN
459 IF (l_data_fix) THEN
460 UPDATE qc_spec_mst
461 SET from_date = l_samples_date.min_date,
462 to_date = l_samples_date.max_date
463 WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
464 END IF;
465 GMA_MIGRATION.gma_insert_message (
466 p_run_id => migration_id,
467 p_table_name => 'QC_SPEC_MST',
468 p_DB_ERROR => '',
469 p_param1 => l_spec_less_from_date.qc_spec_id,
470 p_param2 => l_spec_less_from_date.from_date,
471 p_param3 => l_spec_less_from_date.to_date,
472 p_param4 => '',
473 p_param5 => '',
474 p_message_token => 'LESS_FROM_RESOLVED',
475 p_message_type => 'WD',
476 p_line_no => '1',
477 p_position => l_position,
478 p_base_message => '');
479
480 ELSE
481 l_position := 53;
482 -- Bug 3587546; Changed to only mark the row with the from_date > to_date
483 -- to not migrate, instead of all the spec_hdr_id's qc_spec_id's
484 -- along with the corrsponding samples and results.
485 IF (l_data_fix) THEN
486 UPDATE qc_spec_mst
487 SET migration_status = 'WD'
488 WHERE qc_spec_id = l_spec_less_from_date.qc_spec_id;
489 END IF;
490 GMA_MIGRATION.gma_insert_message (
491 p_run_id => migration_id,
492 p_table_name => 'QC_SPEC_MST',
493 p_DB_ERROR => '',
494 p_param1 => l_spec_less_from_date.qc_spec_id,
495 p_param2 => l_spec_less_from_date.from_date,
496 p_param3 => l_spec_less_from_date.to_date,
497 p_param4 => '',
498 p_param5 => '',
499 p_message_token => 'LESS_FROM_UNRESOLVED',
500 p_message_type => 'WD',
501 p_line_no => '1',
502 p_position => l_position,
503 p_base_message => '');
504
505 END IF;
506 END LOOP;
507 END LOOP;
508 l_position:=59;
509
510 /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
511
512 GMA_MIGRATION.gma_insert_message (
513 p_run_id => migration_id,
514 p_table_name => 'QC_SPEC_MST',
515 p_DB_ERROR => '',
516 p_param1 => '',
517 p_param2 => '',
518 p_param3 => '',
519 p_param4 => '',
520 p_param5 => '',
521 p_message_token => 'LESS_FROM_END', -- pal token not mapped
522 p_message_type => 'WD', -- not even used in base routine
523 p_line_no => '1',
524 p_position => l_position,
525 p_base_message => ''); */
526
527
528 GMA_COMMON_LOGGING.gma_migration_central_log (
529 p_run_id => migration_id,
530 p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
531 p_message_token => 'LESS_FROM_END',
532 p_table_name => 'QC_SPEC_MST',
533 p_context => '',
534 p_param1 => NULL,
535 p_param2 => NULL,
536 p_param3 => NULL,
537 p_param4 => NULL,
538 p_param5 => NULL,
539 p_db_error => NULL,
540 p_app_short_name => 'GMD');
541
542
543
544
545
546 --End bug 3542894
547
548
549 -- B3568239 START
550 -- Check that for the Vendor Spec the spec vendor_id has corresponding
551 -- of_vendor_id
552 FOR l_null_vendor_id_spec IN c_null_vendor_id_spec
553 LOOP
554 -- The spec vendor_id does not have corresponding of_vendor_id
555 GMA_MIGRATION.gma_insert_message (
556 p_run_id => migration_id,
557 p_table_name => 'QC_SPEC_MST',
558 p_DB_ERROR => '',
559 p_param1 => l_null_vendor_id_spec.qc_spec_id,
560 p_param2 => l_null_vendor_id_spec.vendor_id,
561 p_param3 => '',
562 p_param4 => '',
563 p_param5 => '',
564 p_message_token => 'SPEC_VENDOR_INVALID_NOTRESOLVED',
565 p_message_type => 'VI',
566 p_line_no => '1',
567 p_position => l_position,
568 p_base_message => '');
569
570 -- Mark the Spec, Samples, and Results as VI (Vendor Invalid)
571
572 UPDATE qc_spec_mst
573 SET migration_status = 'VI'
574 WHERE migration_status IS NULL
575 AND spec_hdr_id in (SELECT spec_hdr_id
576 FROM qc_spec_mst
577 WHERE qc_spec_id = l_null_vendor_id_spec.qc_spec_id
578 )
579 ;
580
581 UPDATE qc_smpl_mst
582 SET migration_status = 'VI'
583 WHERE migration_status IS NULL
584 AND sample_id IN (SELECT sample_id
585 FROM qc_rslt_mst
586 WHERE qc_spec_id in (SELECT qc_spec_id
587 FROM qc_spec_mst
588 WHERE spec_hdr_id in (select
589 spec_hdr_id
590 from
591 qc_spec_mst
592 where
593 qc_spec_id = l_null_vendor_id_spec.qc_spec_id
594 )
595 )
596 )
597 ;
598
599 UPDATE qc_rslt_mst
600 SET migration_status = 'VI'
601 WHERE migration_status IS NULL
602 AND sample_id in (SELECT sample_id
603 FROM qc_rslt_mst
604 WHERE qc_spec_id in (SELECT qc_spec_id
605 FROM qc_spec_mst
606 WHERE spec_hdr_id in (select
607 spec_hdr_id
608 from
609 qc_spec_mst
610 where
611 qc_spec_id = l_null_vendor_id_spec.qc_spec_id
612 )
613 )
614 )
615 ;
616
617 END LOOP;
618 -- B3568239 END
619
620
621 l_position :=60;
622
623 /*-- replaced below with GMA_COMMON_LOGGING.gma_migration_central_log to make it not an error but informational
624
625 GMA_MIGRATION.gma_insert_message (
626 p_run_id => migration_id,
627 p_table_name => 'QC_SPEC_MST',
628 p_DB_ERROR => '',
629 p_param1 => '',
630 p_param2 => '',
631 p_param3 => '',
632 p_param4 => '',
633 p_param5 => '',
634 p_message_token => 'OVERLAP_START', -- pal token not mapped
635 p_message_type => 'OL', -- not even used in base routine
636 p_line_no => '1',
637 p_position => l_position,
638 p_base_message => ''); */
639
640 GMA_COMMON_LOGGING.gma_migration_central_log (
641 p_run_id => migration_id,
642 p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
643 p_message_token => 'OVERLAP_START',
644 p_table_name => 'QC_SPEC_MST',
645 p_context => '',
646 p_param1 => NULL,
647 p_param2 => NULL,
648 p_param3 => NULL,
649 p_param4 => NULL,
650 p_param5 => NULL,
651 p_db_error => NULL,
652 p_app_short_name => 'GMD');
653
654
655
656 -- dbms_output.put_line('Before in while...');
657 WHILE (qc_repeat_flag AND qc_max_dup_counter < 10) LOOP
658
659 l_position :=70;
660 qc_repeat_flag := FALSE;
661 qc_max_dup_counter := qc_max_dup_counter +1;
662 --------------------------------
663 --Start Loop for all duplicates
664 --------------------------------
665 FOR l_specs in c_specs LOOP
666 qc_repeat_flag := TRUE;
667
668 l_position :=80;
669
670 qc_in_a := FALSE;
671 qc_in_b := FALSE;
672 is_total_overlap := FALSE;
673 unable_to_resolve := FALSE;
674
675 -- dbms_output.put_line('Going in while...');
676 ---------------------------------------
677 --Is it total overlap???
678 ---------------------------------------
679 IF (l_specs.to_date > l_specs.l_b_to_date) THEN
680 ----------------------------
681 -- Total overlap, mark in OL
682 ----------------------------
683 is_total_overlap := TRUE;
684 l_position :=90;
685 --dbms_output.put_line('Is overlap true is true, no processing exit...');
686 -- CN Just return or continue after marking Spec, Smpl and Rslt
687 GMA_MIGRATION.gma_insert_message (
688 p_run_id => migration_id,
689 p_table_name => 'QC_SPEC_TEST',
690 p_DB_ERROR => '',
691 p_param1 => l_specs.l_a_qc_spec_id,
692 p_param2 => l_specs.l_b_qc_spec_id,
693 p_param3 => l_specs.from_date,
694 p_param4 => l_specs.to_date,
695 p_param5 => '',
696 p_message_token => 'TOTAL_OVERLAP',
697 p_message_type => 'OL',
698 p_line_no => '1',
699 p_position => l_position,
700 p_base_message => 'Qc_spec_ids '||l_specs.l_a_qc_spec_id||' and
701 '||l_specs.l_b_qc_spec_id||' has totally overlapping dates');
702 ELSE
703 --------------------------------------
704 --Start Sample is there for Spec A?
705 --------------------------------------
706 FOR l_a_qc_spec IN c_samples_a(l_specs.from_date,
707 l_specs.to_date,
708 l_specs.l_a_qc_spec_id) LOOP
709 l_position :=100;
710 l_a_max_date := l_a_qc_spec.max_sample_date;
711 IF (l_a_qc_spec.cnt >0) THEN
712 qc_in_a := TRUE;
713 END IF;
714 --dbms_output.put_line('Sample for qc_spec_id A...');
715 EXIT; --Not required as aggregate func. in select
716 END LOOP;
717 --------------------------------------
718 --End Sample is there for spec A?
719 --------------------------------------
720
721
722 --------------------------------------
723 --Start Sample is there for Spec B?
724 --------------------------------------
725 FOR l_b_qc_spec IN c_samples_b(l_specs.from_date,
726 l_specs.to_date,
727 l_specs.l_b_qc_spec_id) LOOP
728 l_b_min_date := l_b_qc_spec.min_sample_date;
729 l_position :=110;
730 IF (l_b_qc_spec.cnt >0) THEN
731 qc_in_b := TRUE;
732 END IF;
733 --dbms_output.put_line('Sample for qc_spec_id B...');
734 EXIT; --Not required as aggreagte func. in select
735 END LOOP;
736 --------------------------------------
737 --End Sample is there for Spec B?
738 --------------------------------------
739
740
741
742 -------------------------------------
743 -- Start Main IF
744 -------------------------------------
745 IF (qc_in_a AND NOT qc_in_b) THEN
746 -------------------------------------
747 -- Only spec A used for samples
748 -- Decrease Spec B's from_date
749 -------------------------------------
750 IF (l_data_fix) THEN
751 UPDATE qc_spec_mst
752 SET from_date = l_specs.to_date + 1/86400
753 WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
754 END IF;
755 l_position :=120;
756
757
758
759 GMA_MIGRATION.gma_insert_message (
760 p_run_id => migration_id,
761 p_table_name => 'QC_SPEC_TEST',
762 p_DB_ERROR => '',
763 p_param1 => l_specs.l_a_qc_spec_id,
764 p_param2 => l_specs.l_b_qc_spec_id,
765 p_param3 => l_specs.from_date,
766 p_param4 => l_specs.to_date,
767 p_param5 => '',
768 p_message_token => 'OVERLAP_RESOLVED',
769 p_message_type => 'OL',
770 p_line_no => '1',
771 p_position => l_position,
772 p_base_message => 'In A not in B');
773
774 ELSIF (NOT qc_in_a AND qc_in_b) THEN
775 -------------------------------------
776 -- Only spec B used for samples
777 -- Decrease Spec A's to_date
778 -------------------------------------
779 IF (l_data_fix) THEN
780 UPDATE qc_spec_mst
781 SET to_date = l_specs.from_date - 1/86400
782 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
783 END IF;
784 l_position :=130;
785
786 GMA_MIGRATION.gma_insert_message (
787 p_run_id => migration_id,
788 p_table_name => 'QC_SPEC_TEST',
789 p_DB_ERROR => '',
790 p_param1 => l_specs.l_a_qc_spec_id,
791 p_param2 => l_specs.l_b_qc_spec_id,
792 p_param3 => l_specs.from_date,
793 p_param4 => l_specs.to_date,
794 p_param5 => '',
795 p_message_token => 'OVERLAP_RESOLVED',
796 p_message_type => 'OL',
797 p_line_no => '1',
798 p_position => l_position,
799 p_base_message => 'Not in A in B');
800
801 ELSIF (NOT qc_in_a and NOT qc_in_b) THEN
802 ---------------------------------------
803 -- No samples for overlapped dates
804 -- Does not matter which you inc. or dec.
805 -- We are:-->
806 -- Descreasing Spec A's to_date
807 -------------------------------------
808 l_position :=140;
809 IF (l_data_fix) THEN
810 UPDATE qc_spec_mst
811 SET to_date = l_specs.from_date - 1/86400
812 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
813 END IF;
814
815 l_position :=150;
816
817 GMA_MIGRATION.gma_insert_message (
818 p_run_id => migration_id,
819 p_table_name => 'QC_SPEC_TEST',
820 p_DB_ERROR => '',
821 p_param1 => l_specs.l_a_qc_spec_id,
822 p_param2 => l_specs.l_b_qc_spec_id,
823 p_param3 => l_specs.from_date,
824 p_param4 => l_specs.to_date,
825 p_param5 => '',
826 p_message_token => 'OVERLAP_RESOLVED',
827 p_message_type => 'OL',
828 p_line_no => '1',
829 p_position => l_position,
830 p_base_message => 'not in A not in B');
831 ELSIF (qc_in_a and qc_in_b) THEN
832 ---------------------------------------------------------
833 -- Both Specs have Samples for overlapped dates
834 ---------------------------------------------------------
835 ---------------------------------------------------------------
836 -- IF max(sample_date) for spec A < min(sample_date) for Spec B
837 -- Overlap is resolved by
838 -- Increasing the from_date of Spec to max(sample_date)
839 -- Decreasing the to_date of the spec to min(sample_date)
840 -- Total overlap automatically taken care of
841 ---------------------------------------------------------------
842 l_position :=160;
843 IF (l_a_max_date < l_b_min_date) THEN
844
845 l_position :=170;
846 IF (l_data_fix) THEN
847 UPDATE qc_spec_mst
848 SET to_date = l_a_max_date
849 WHERE qc_spec_id = l_specs.l_a_qc_spec_id;
850
851
852 UPDATE qc_spec_mst
853 SET from_date = l_b_min_date
854 WHERE qc_spec_id = l_specs.l_b_qc_spec_id;
855 END IF;
856
857 l_position :=180;
858
859 GMA_MIGRATION.gma_insert_message (
860 p_run_id => migration_id,
861 p_table_name => 'QC_SPEC_TEST',
862 p_DB_ERROR => '',
863 p_param1 => l_specs.l_a_qc_spec_id,
864 p_param2 => l_specs.l_b_qc_spec_id,
865 p_param3 => l_specs.from_date,
866 p_param4 => l_specs.to_date,
867 p_param5 => '',
868 p_message_token => 'OVERLAP_RESOLVED',
869 p_message_type => 'OL',
870 p_line_no => '1',
871 p_position => l_position,
872 p_base_message => 'In A in B');
873 ELSE
874
875 unable_to_resolve := TRUE;
876 l_position :=190;
877 GMA_MIGRATION.gma_insert_message (
878 p_run_id => migration_id,
879 p_table_name => 'QC_SPEC_TEST',
880 p_DB_ERROR => '',
881 p_param1 => l_specs.l_a_qc_spec_id,
882 p_param2 => l_specs.l_b_qc_spec_id,
883 p_param3 => l_specs.from_date,
884 p_param4 => l_specs.to_date,
885 p_param5 => '',
886 p_message_token => 'NOT_RESOLVED',
887 p_message_type => 'OL',
888 p_line_no => '1',
889 p_position => l_position,
890 p_base_message => 'Samples dates are there in qc_spec_ids
891 '||l_specs.l_a_qc_spec_id||','||l_specs.l_b_qc_spec_id||' for overlapped dates
892 between '||l_specs.from_date||' and '||l_specs.to_date);
893 END IF;
894 END IF;
895 ----------------------
896 --End of main IF
897 ----------------------
898 END IF; -- Total Overlap or NOT
899 -- cannot migrate, update the status to OL and report it when the problem
900 -- cases run...
901 IF (unable_to_resolve OR is_total_overlap) THEN
902 -- IF (l_data_fix) THEN
903 l_position :=200;
904 UPDATE qc_spec_mst
905 SET migration_status = 'OL'
906 WHERE spec_hdr_id in (SELECT spec_hdr_id
907 FROM qc_spec_mst
908 WHERE qc_spec_id in (l_specs.l_a_qc_spec_id,
909 l_specs.l_b_qc_spec_id))
910 AND migration_status IS NULL;
911
912 l_position :=220;
913 UPDATE qc_smpl_mst
914 SET migration_status = 'OL'
915 WHERE sample_id IN (SELECT sample_id
916 FROM qc_rslt_mst
917 WHERE qc_spec_id in (SELECT qc_spec_id
918 FROM qc_spec_mst
919 WHERE spec_hdr_id in (select
920 spec_hdr_id
921 from
922 qc_spec_mst
923 where
924 qc_spec_id in
925 (l_specs.l_a_qc_spec_id,
926 l_specs.l_b_qc_spec_id))));
927
928
929 l_position :=240;
930 UPDATE qc_rslt_mst
931 SET migration_status = 'OL'
932 WHERE sample_id in (SELECT sample_id
933 FROM qc_rslt_mst
934 WHERE qc_spec_id in (SELECT qc_spec_id
935 FROM qc_spec_mst
936 WHERE spec_hdr_id in (select
937 spec_hdr_id
938 from
939 qc_spec_mst
940 where
941 qc_spec_id in
942 (l_specs.l_a_qc_spec_id,
943 l_specs.l_b_qc_spec_id))));
944
945
946 --END IF;
947
948 END IF;
949 -------------------
950 --Unable to resolve
951 -------------------
952
953 END LOOP;
954 -------------------------------------
955 --End Loop for all duplicates
956 --------------------------------
957 END LOOP;
958 --------------------------------
959 -- End 10 iteration while loop
960
961 --------------------------------
962
963 -- 5727706 add message below
964
965 GMA_COMMON_LOGGING.gma_migration_central_log (
966 p_run_id => migration_id,
967 p_log_level => FND_LOG.LEVEL_EVENT, -- = informational ,
968 p_message_token => 'GMA_PRE_MIGRATION_SUCCESS',
969 p_table_name => 'VALIDATION_FIX',
970 p_context => '',
971 p_param1 => NULL,
972 p_param2 => NULL,
973 p_param3 => NULL,
974 p_param4 => NULL,
975 p_param5 => NULL,
976 p_db_error => NULL,
977 p_app_short_name => 'GMD');
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 x_return_status := 'U';
982 GMA_MIGRATION.gma_insert_message (
983 p_run_id => migration_id,
984 p_table_name => 'QC_SPEC_MST',
985 p_DB_ERROR => sqlerrm,
986 p_param1 => '',
987 p_param2 => '',
988 p_param3 => '',
989 p_param4 => '',
990 p_param5 => '',
991 p_message_token => 'GMA_MIGRATION_DB_ERROR',
992 p_message_type => 'E',
993 p_line_no => '1',
994 p_position => l_position,
995 p_base_message => 'Validation DB ERROR '||sqlerrm);
996 END VALIDATION_FIX;
997 end GMD_QM_VALIDATE_FIX;
[Home] [Help]