[Home] [Help]
PACKAGE BODY: APPS.GMD_QC_MIGRATE_TO_1151J
Source
1 PACKAGE BODY GMD_QC_MIGRATE_TO_1151J AS
2 /* $Header: GMDQCMJB.pls 120.1 2006/08/29 21:14:28 rakulkar noship $ */
3
4 --Bug 5025951
5 G_display_precision PLS_INTEGER := to_number(NVL(fnd_profile.value('GMD_MIG_TEST_STORAGE_PRECISION'),'0'));
6 G_report_precision PLS_INTEGER := to_number(NVL(fnd_profile.value('GMD_MIG_TEST_REPORT_PRECISION'), '0'));
7
8 /*===========================================================================
9 -- FUNCTION:
10 -- Get_Base_Language
11 --
12 -- DESCRIPTION:
13 -- This function is use to retrieve the base language of the installation.
14 --
15 -- PARAMETERS:
16 -- NONE
17 --
18 -- RETURN VALUES:
19 -- base_language
20 --
21 -- SYNOPSIS:
22 -- l_base_lang := Get_Base_Language;
23 --
24 -- HISTORY
25 --========================================================================== */
26 /* PURPOSE: Apply fixes to qc_spec_mst for migration to Patch 11.5.1J */
27 /* to fix overlapping spec tests from and to dates */
28 /* when the same test or assay_code */
29 /* occurs in a spec more than once */
30 /* AUTHOR: Brenda Stone OPM Development */
31 /* DATE: 6-Mar-2003 */
32 /* 19-May-2003 B. Stone Added logic for 2 spec tests in same */
33 /* spec header with exact same from and to */
34 /* dates. Only 1 spec test can be migrated. */
35 /* --------------------------------------------------------------------------*/
36
37 PROCEDURE Chk_overlapping_Spec_Tests ( p_migration_id IN NUMBER,
38 x_return_status OUT NOCOPY VARCHAR2)
39 IS
40 /* ------------- LOCAL VARIABLES ------------------- */
41 c_qc_spec_id number null;
42 l_spec_hdr_id number null;
43 l_qc_spec_id number null;
44 l_qcassy_typ_id number null;
45 l_assay_code varchar2(16) null;
46 l_from_date date null;
47 l_to_date date null;
48 l_overlap_qc_spec_id number null;
49 o_spec_hdr_id number null;
50 o_qc_spec_id number null;
51 o_assay_code varchar2(16) null;
52 o_from_date date null;
53 o_from_date_2hr date null;
54 o_to_date date null;
55 new_to_date date null;
56 new_mig_status varchar2(2) null;
57 l_max_cr_date date null;
58 l_min_cr_date date null;
59 l_min_rslt_date date null;
60 r_min_rslt_date date null;
61 last_spec_hdr_id number null;
62 last_qc_spec_id number null;
63 last_assay_code varchar2(16) null;
64 last_max_cr_date date null;
65 last_from_date date null;
66 last_to_date date null;
67 r_max_cr_date date null;
68
69
70 CURSOR c_specs IS
71 SELECT a.spec_hdr_id,
72 a.qcassy_typ_id,
73 a.assay_code
74 FROM qc_spec_mst a,
75 qc_spec_mst b
76 WHERE a.migration_status is NULL
77 AND a.spec_hdr_id = b.spec_hdr_id
78 AND a.QC_SPEC_ID <> b.QC_SPEC_ID
79 AND a.QCASSY_TYP_ID = b.QCASSY_TYP_ID
80 AND b.from_date <= a.to_date
81 GROUP BY a.spec_hdr_id, a.qcassy_typ_id,
82 a.assay_code
83 order by a.spec_hdr_id,a.qcassy_typ_id,
84 a.assay_code;
85
86
87 CURSOR c_overlaps IS
88 SELECT spec_hdr_id,
89 s.qc_spec_id,
90 s.assay_code,
91 from_date,
92 to_date
93 FROM qc_spec_mst s
94 WHERE s.spec_hdr_id = l_spec_hdr_id
95 AND (( s.assay_code = l_assay_code)
96 or ( s.qcassy_typ_id = l_qcassy_typ_id ))
97 AND migration_status is NULL
98 ORDER BY from_date ;
99
100 CURSOR c_results IS
101 SELECT min(s.sample_date),
102 max(s.sample_date)
103 FROM qc_rslt_mst r,
104 qc_smpl_mst s
105 WHERE r.qc_spec_id = c_qc_spec_id
106 and s.sample_id = r.sample_id;
107
108 CURSOR c_rslt_date IS
109 SELECT min(s.sample_date)
110 FROM qc_rslt_mst r,
111 qc_smpl_mst s
112 WHERE r.qc_spec_id = o_qc_spec_id
113 and s.sample_id = r.sample_id;
114
115 CURSOR c_chk_overlaps IS
116 SELECT s.qc_spec_id
117 FROM qc_spec_mst s
118 WHERE (( s.from_date > r_min_rslt_date
119 and s.to_date > o_to_date )
120 OR ( s.from_date < r_min_rslt_date
121 and s.to_date < o_to_date )
122 OR ( s.from_date > r_min_rslt_date
123 and s.to_date < o_to_date ))
124 AND s.spec_hdr_id = l_spec_hdr_id
125 AND s.qcassy_typ_id = l_qcassy_typ_id
126 and s.qc_spec_id <> o_qc_spec_id
127 AND migration_status is NULL
128 ;
129
130
131
132 BEGIN
133
134 -- gmd_p_fs_context sets the formula security context
135 --
136 gmd_p_fs_context.set_additional_attr;
137
138 UPDATE qc_spec_mst
139 SET old_from_date = from_date,
140 old_to_date = to_date
141 WHERE old_from_date is NULL ;
142 COMMIT;
143
144 UPDATE qc_spec_mst
145 SET to_date = ( to_date - 1/86400 ),
146 from_date = ( from_date - 1/86400 )
147 WHERE old_from_date is NULL ;
148 COMMIT;
149
150
151 /* Update Migration_status to 'NM' for delete specs with no results */
152 UPDATE qc_spec_mst s
153 set migration_status = 'NM'
154 where s.delete_mark = 1
155 and s.migration_status is NULL
156 and not exists (
157 select *
158 from qc_rslt_mst r
159 where s.qc_spec_id = r.qc_spec_id );
160 COMMIT;
161
162 /* Result rows with an invalid test ( qcassy_typ_id) and
163 -- spec combination are updated to eliminate the problem.
164 -- The qc_spec_id is updated to NULL since the correct
165 -- qc_spec_id cannot be derived. */
166 UPDATE qc_rslt_mst r
167 set old_qc_spec_id = qc_spec_id ,
168 qc_spec_id = NULL
169 where qc_spec_id = (
170 SELECT r.qc_spec_id
171 from qc_spec_mst s
172 where s.qc_spec_id = r.qc_spec_id
173 and s.qcassy_typ_id <> r.qcassy_typ_id);
174 COMMIT;
175
176
177 OPEN c_specs;
178 FETCH c_specs into
179 l_spec_hdr_id,
180 l_qcassy_typ_id,
181 l_assay_code;
182
183 last_spec_hdr_id := l_spec_hdr_id;
184 last_assay_code := l_assay_code;
185
186 WHILE c_specs%FOUND LOOP
187 OPEN c_overlaps;
188 FETCH c_overlaps into
189 o_spec_hdr_id,
190 o_qc_spec_id ,
191 o_assay_code ,
192 o_from_date,
193 o_to_date;
194
195 last_qc_spec_id := o_qc_spec_id;
196 last_to_date := o_to_date;
197
198 WHILE c_overlaps%FOUND LOOP
199 new_mig_status := NULL;
200 IF o_from_date < last_to_date
201 AND last_qc_spec_id <> o_qc_spec_id
202 THEN
203 /* two spec tests with the exact same from dates, qc_typ_id */
204 IF o_from_date = last_from_date
205 AND o_to_date = last_to_date
206 THEN
207 /* INSERT into bfs_msg
208 values ( 'Dup Dates, qc_spec_id = '||o_qc_spec_id ||
209 ' o_from_date = '|| o_from_date ||
210 ' o_to_date = '||o_to_date );
211 commit; */
212 c_qc_spec_id := last_qc_spec_id;
213 l_max_cr_date := NULL;
214 OPEN c_results;
215 FETCH c_results into
216 l_min_cr_date,
217 l_max_cr_date;
218 r_max_cr_date := l_max_cr_date;
219 /*
220 insert into bfs_msg
221 values ( 'last_qc_spec_id = '||last_qc_spec_id ||
222 'r_max_cr_date = '|| r_max_cr_date );
223 commit; */
224
225 CLOSE c_results;
226 IF r_max_cr_date is not NULL
227 THEN
228 /* last spec test has corresponding results */
229 c_qc_spec_id := o_qc_spec_id;
230 l_max_cr_date := NULL;
231 OPEN c_results;
232 FETCH c_results into
233 l_min_cr_date,
234 l_max_cr_date;
235 r_max_cr_date := l_max_cr_date;
236 /*
237 insert into bfs_msg
238 values ( 'o_qc_spec_id = '||o_qc_spec_id ||
239 ' r_max_cr_date = '|| r_max_cr_date );
240 commit; */
241
242 CLOSE c_results;
243 IF r_max_cr_date is not NULL
244 THEN
245 /* Both dup spec tests have results, set migration_status = 'DR' */
246 /* for both spec tests and corresponding results and the result's */
247 /* sample */
248 UPDATE qc_spec_mst
249 SET migration_status = 'DR'
250 WHERE qc_spec_id = o_qc_spec_id
251 or qc_spec_id = last_qc_spec_id;
252 UPDATE qc_rslt_mst
253 SET migration_status = 'DR'
254 WHERE qc_spec_id = o_qc_spec_id
255 or qc_spec_id = last_qc_spec_id;
256 UPDATE qc_smpl_mst
257 SET migration_status = 'DR'
258 WHERE sample_id IN (
259 select sample_id
260 from qc_rslt_mst
261 where qc_spec_id = o_qc_spec_id
262 or qc_spec_id = last_qc_spec_id);
263
264 ELSE
265 /* The last spec test has results and the current spec test */
266 /* does not have results; Therefore, the last spec test is */
267 /* migrated and the current spec test is not migrated; it's */
268 /* migration_status = 'DN' */
269 UPDATE qc_spec_mst
270 SET migration_status = 'DN'
271 WHERE qc_spec_id = o_qc_spec_id;
272
273 END IF;
274 /* CLOSE c_results; */
275 ELSE
276 /* The last spec test did not have results; therefore it is safe */
277 /* to migrate the current spec test */
278 UPDATE qc_spec_mst
279 SET migration_status = 'DN'
280 WHERE qc_spec_id = last_qc_spec_id;
281 END IF;
282 ELSE
283 c_qc_spec_id := last_qc_spec_id;
284 OPEN c_results;
285 FETCH c_results into
286 l_min_cr_date,
287 l_max_cr_date;
288
289 /* insert into bfs_msg
290 values ( 'last_qc_spec_id = '||last_qc_spec_id ||
291 ' l_max_cr_date = '|| l_max_cr_date );
292 commit;
293 */
294 /* When spec has results; derive the max creation_date of the
295 -- results and use as the TO_DATE */
296 o_from_date_2hr := o_from_date - 7200/86400;
297 /* insert into bfs_msg
298 values ( 'before: CR_DATE is <> NULL: o_from_date_2hr = '||
299 to_char(o_from_date_2hr, 'DD-MON-YYYY HH24:MI:SS')||
300 ' l_max_cr_date = '||
301 to_char( l_max_cr_date, 'DD-MON-YYYY HH24:MI:SS') );
302 commit; */
303
304 IF l_max_cr_date is not NULL
305 THEN
306 /* Max results creation date must be < 2 hrs the following
307 -- spec's from date */
308 /* insert into bfs_msg
309 values ( 'CR_DATE is <> NULL: o_from_date_2hr = '||
310 o_from_date_2hr ||
311 ' l_max_cr_date = '|| l_max_cr_date );
312 commit; */
313 IF l_max_cr_date < o_from_date_2hr and
314 l_max_cr_date > last_from_date
315 THEN
316 /* Results exist and max results creation date is more than
317 -- 2 hrs less than the following spec's from date */
318 new_to_date := l_max_cr_date;
319 last_to_date := l_max_cr_date;
320 ELSE
321 /* Results exist and max results creation date is less than
322 -- 2 hrs from the following spec's from date; therefore,
323 -- is flagged to not migrate and msg is written to
324 -- migration log table */
325 new_mig_status := 'UM';
326 /* insert into bfs_msg
327 values ( 'o_from_date_2hr = '||o_from_date_2hr ||
328 ' l_max_cr_date = '|| l_max_cr_date );
329 commit; */
330 END IF;
331 ELSE
332 /* spec does not have any results; 2 hrs are subtracted from the
333 -- from_date of the following spec to use as the new TO_DATE
334 -- The spec's from_date must be less then the new to_date
335 -- else spec test is not migrated */
336 IF last_from_date < o_from_date_2hr
337 THEN
338 new_to_date := o_from_date_2hr;
339 ELSE
340 new_mig_status := 'UM';
341 END IF;
342 END IF;
343 IF new_mig_status = 'UM'
344 THEN
345 UPDATE qc_spec_mst
346 SET migration_status = 'UM'
347 WHERE spec_hdr_id = l_spec_hdr_id;
348 UPDATE qc_rslt_mst
349 SET migration_status = 'UM'
350 WHERE qc_spec_id = o_qc_spec_id
351 or qc_spec_id = last_qc_spec_id;
352 UPDATE qc_smpl_mst s
353 SET migration_status = 'UM'
354 WHERE s.sample_id IN (
355 select sample_id
356 from qc_spec_mst sp,
357 qc_rslt_mst r
358 where sp.spec_hdr_id = l_spec_hdr_id
359 and r.qc_spec_id = sp.qc_spec_id);
360 commit;
361
362 commit;
363 new_mig_status := NULL;
364 GMA_MIGRATION.gma_insert_message (
365 p_run_id => p_migration_id,
366 p_table_name => 'QC_SPEC_MST',
367 p_DB_ERROR => '',
368 p_param1 => 'Overlapping spec test dates ',
369 p_param2 => 'And spec tests has results ',
370 p_param3 => 'Migration_status set to UM',
371 p_param4 => 'Following qc_spec_id = '||o_qc_spec_id,
372 p_param5 => '',
373 p_message_token => 'Unable to migrate spec test'||last_qc_spec_id,
374 p_message_type => 'P',
375 p_line_no => '1',
376 p_position => '',
377 p_base_message => '');
378 COMMIT;
379 ELSE
380 UPDATE qc_spec_mst
381 SET to_date = new_to_date
382 WHERE qc_spec_id = last_qc_spec_id;
383 COMMIT;
384 END IF;
385 CLOSE c_results;
386 END IF;
387 ELSE IF o_from_date > o_to_date THEN
388 /* insert into bfs values
389 ( ' qc_spec_id = '||o_qc_spec_id ||'; o_from_date = '||
390 o_from_date||'; o_to_date = '||o_to_date);
391 commit; */
392 OPEN c_rslt_date;
393 FETCH c_rslt_date into
394 l_min_rslt_date;
395 r_min_rslt_date := l_min_rslt_date;
396 /* insert into bfs values
397 ( 'r_min_rslt_date = '|| r_min_rslt_date );
398 commit; */
399 /* If results are not found then the FROM_DATE is updated to */
400 /* the TO_DATE to eliminate the overlap of dates */
401 CLOSE c_rslt_date;
402 IF r_min_rslt_date is NULL THEN
403 /* insert into bfs values
404 ('From_date = To_date; o_qc_spec_id = '||
405 o_qc_spec_id );
406 commit; */
407 UPDATE qc_spec_mst
408 SET from_date = to_date
409 WHERE qc_spec_id = o_qc_spec_id;
410 COMMIT;
411 ELSE
412 /* Results are found and the FROM_DATE is updated to the */
413 /* earliest RESULT_DATE */
414
415 OPEN c_chk_overlaps;
416 FETCH c_chk_overlaps INTO l_overlap_qc_spec_id;
417 IF c_chk_overlaps%NOTFOUND THEN
418 UPDATE qc_spec_mst
419 SET from_date = r_min_rslt_date
420 WHERE qc_spec_id = o_qc_spec_id;
421 COMMIT;
422 ELSE
423 /* Unable to resolve overlapping dates, write msg to log */
424 UPDATE qc_spec_mst
425 SET migration_status = 'UM'
426 WHERE qc_spec_id = o_qc_spec_id;
427 commit;
428 new_mig_status := NULL;
429 GMA_MIGRATION.gma_insert_message (
430 p_run_id => p_migration_id,
431 p_table_name => 'QC_SPEC_MST',
432 p_DB_ERROR => '',
433 p_param1 => 'qc_spec_id = '||o_qc_spec_id,
434 p_param2 => '',
435 p_param3 => 'Migration_status set to UM',
436 p_param4 => 'Overlapping dates with qc_spec_id',
437 p_param5 => l_overlap_qc_spec_id,
438 p_message_token => 'Unable to migrate spec test',
439 p_message_type => 'P',
440 p_line_no => '1',
441 p_position => '',
442 p_base_message => '');
443 COMMIT;
444 END IF;
445 CLOSE c_chk_overlaps;
446 END IF;
447 END IF;
448 last_to_date := o_to_date;
449 END IF;
450 last_spec_hdr_id := l_spec_hdr_id;
451 last_qc_spec_id := o_qc_spec_id;
452 last_assay_code := l_assay_code;
453 last_from_date := o_from_date;
454 last_to_date := o_to_date;
455 FETCH c_overlaps into
456 o_spec_hdr_id,
457 o_qc_spec_id ,
458 o_assay_code ,
459 o_from_date,
460 o_to_date;
461 END LOOP;
462 CLOSE c_overlaps;
463 last_spec_hdr_id := l_spec_hdr_id;
464 last_assay_code := l_assay_code;
465 last_max_cr_date := l_max_cr_date;
466
467 FETCH c_specs into
468 l_spec_hdr_id ,
469 l_qcassy_typ_id,
470 l_assay_code;
471 END LOOP;
472 CLOSE c_specs;
473 COMMIT;
474
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 x_return_status := 'U';
479 GMA_MIGRATION.gma_insert_message (
480 p_run_id => p_migration_id,
481 p_table_name => 'QC_SPEC_MST',
482 p_DB_ERROR => sqlerrm,
483 p_param1 => '',
484 p_param2 => '',
485 p_param3 => '',
486 p_param4 => '',
487 p_param5 => '',
488 p_message_token => 'GMA_MIGRATION_DB_ERROR',
489 p_message_type => 'E',
490 p_line_no => '1',
491 p_position => '',
492 p_base_message => 'Failed to complete spec test dates chk '||sqlerrm);
493 END Chk_overlapping_Spec_Tests;
494
495
496 FUNCTION Get_Base_Language RETURN VARCHAR2
497 IS
498
499 /* ------------- LOCAL VARIABLES ------------------- */
500 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
501
502 /* ------------------ CURSORS ---------------------- */
503 /* Get the installation's base language */
504 CURSOR c_get_base_lang IS
505 SELECT language_code
506 FROM fnd_languages
507 WHERE installed_flag = 'B';
508
509 BEGIN
510 OPEN c_get_base_lang;
511 FETCH c_get_base_lang into l_base_lang;
512 CLOSE c_get_base_lang;
513
514 RETURN l_base_lang;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 RAISE;
519
520 END Get_Base_Language;
521
522
523 /*===========================================================================
524 -- PROCEDURE:
525 -- Migrate_Assay_Classes
526 --
527 -- DESCRIPTION:
528 -- This PL/SQL procedure is used to migrate assay (test) classes
529 -- to a base and translated table for OPM patch 11.5.1J.
530 --
531 -- PARAMETERS:
532 -- p_migration_id - id to use to right to migration log
533 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
534 --
535 -- SYNOPSIS:
536 -- Migrate_Assay_Classes;
537 --
538 -- HISTORY
539 --=========================================================================== */
540 PROCEDURE Migrate_Assay_Classes (p_migration_id IN NUMBER,
541 x_return_status OUT NOCOPY VARCHAR2)
542 IS
543
544 /* ------------- LOCAL VARIABLES ------------------- */
545 l_table_name VARCHAR2(40);
546 l_rec_count NUMBER;
547 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
548
549
550
551 BEGIN
552
553 /* Get the installation's base language */
554 l_base_lang := Get_Base_Language;
555
556 INSERT INTO gmd_test_classes_b
557 (
558 test_class,
559 delete_mark,
560 text_code,
561 creation_date,
562 created_by,
563 last_update_date,
564 last_updated_by,
565 last_update_login,
566 attribute1,
567 attribute2,
568 attribute3,
569 attribute4,
570 attribute5,
571 attribute6,
572 attribute7,
573 attribute8,
574 attribute9,
575 attribute10,
576 attribute11,
577 attribute12,
578 attribute13,
579 attribute14,
580 attribute15,
581 attribute16,
582 attribute17,
583 attribute18,
584 attribute19,
585 attribute20,
586 attribute21,
587 attribute22,
588 attribute23,
589 attribute24,
590 attribute25,
591 attribute26,
592 attribute27,
593 attribute28,
594 attribute29,
595 attribute30,
596 attribute_category
597 )
598 SELECT
599 assay_class,
600 delete_mark,
601 text_code,
602 creation_date,
603 created_by,
604 last_update_date,
605 last_updated_by,
606 last_update_login,
607 attribute1,
608 attribute2,
609 attribute3,
610 attribute4,
611 attribute5,
612 attribute6,
613 attribute7,
614 attribute8,
615 attribute9,
616 attribute10,
617 attribute11,
618 '',
619 attribute13,
620 attribute14,
621 attribute15,
622 attribute16,
623 attribute17,
624 attribute18,
625 attribute19,
626 attribute20,
627 attribute21,
628 attribute22,
629 attribute23,
630 attribute24,
631 attribute25,
632 attribute26,
633 attribute27,
634 attribute28,
635 attribute29,
636 attribute30,
637 attribute_category
638 FROM gmd_qc_assay_class
639 WHERE decode(migration_status,NULL,'NM') <> 'MO';
640
641 INSERT INTO gmd_test_classes_tl
642 (
643 test_class,
644 language,
645 test_class_desc,
646 source_lang,
647 creation_date,
648 created_by,
649 last_update_date,
650 last_updated_by,
651 last_update_login
652 )
653 SELECT
654 assay_class,
655 l_base_lang,
656 assay_class_desc,
657 l_base_lang,
658 creation_date,
659 created_by,
660 last_update_date,
661 last_updated_by,
662 last_update_login
663 FROM gmd_qc_assay_class
664 WHERE decode(migration_status,NULL,'NM') <> 'MO';
665
666
667 /* Updated record status to migrated */
668 UPDATE gmd_qc_assay_class
669 SET migration_status = 'MO';
670
671 l_rec_count := SQL%ROWCOUNT;
672
673 GMA_MIGRATION.gma_insert_message (
674 p_run_id => p_migration_id,
675 p_table_name => 'GMD_QC_ASSAY_CLASS',
676 p_DB_ERROR => '',
677 p_param1 => '',
678 p_param2 => l_rec_count,
679 p_param3 => '',
680 p_param4 => '',
681 p_param5 => '',
682 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
683 p_message_type => 'P',
684 p_line_no => '1',
685 p_position => '',
686 p_base_message => '');
687
688 COMMIT;
689
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 x_return_status := 'U';
694 GMA_MIGRATION.gma_insert_message (
695 p_run_id => p_migration_id,
696 p_table_name => 'GMD_QC_ASSAY_CLASS',
697 p_DB_ERROR => sqlerrm,
698 p_param1 => '',
699 p_param2 => '',
700 p_param3 => '',
701 p_param4 => '',
702 p_param5 => '',
703 p_message_token => 'GMA_MIGRATION_DB_ERROR',
704 p_message_type => 'E',
705 p_line_no => '1',
706 p_position => '',
707 p_base_message => 'Failed to migrate assay classes due to '||sqlerrm);
708
709 GMA_MIGRATION.gma_insert_message (
710 p_run_id => p_migration_id,
711 p_table_name => 'GMD_QC_ASSAY_CLASS',
712 p_DB_ERROR => '',
713 p_param1 => '',
714 p_param2 => '',
715 p_param3 => '',
716 p_param4 => '',
717 p_param5 => '',
718 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
719 p_message_type => 'P',
720 p_line_no => '1',
721 p_position => '',
722 p_base_message => '');
723
724 END Migrate_Assay_Classes;
725
726
727 /*===========================================================================
728 -- PROCEDURE:
729 -- Migrate_Action_Codes
730 --
731 -- DESCRIPTION:
732 -- This PL/SQL procedure is used to migrate action codes
733 -- to a base and translated table for OPM patch 11.5.1J.
734 --
735 -- PARAMETERS:
736 -- p_migration_id - id to use to right to migration log
737 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
738 --
739 -- SYNOPSIS:
740 -- Migrate_Action_Codes;
741 --
742 -- HISTORY
743 --=========================================================================== */
744 PROCEDURE Migrate_Action_Codes (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
745 IS
746
747 /* ------------- LOCAL VARIABLES ------------------- */
748 l_table_name VARCHAR2(40);
749 l_rec_count NUMBER;
750 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
751
752 BEGIN
753
754 /* Get the installation's base language */
755 l_base_lang := Get_Base_Language;
756
757 INSERT INTO gmd_actions_b
758 (
759 action_code,
760 action_interval,
761 delete_mark,
762 text_code,
763 creation_date,
764 created_by,
765 last_update_date,
766 last_updated_by,
767 last_update_login,
768 attribute1,
769 attribute2,
770 attribute3,
771 attribute4,
772 attribute5,
773 attribute6,
774 attribute7,
775 attribute8,
776 attribute9,
777 attribute10,
778 attribute11,
779 attribute12,
780 attribute13,
781 attribute14,
782 attribute15,
783 attribute16,
784 attribute17,
785 attribute18,
786 attribute19,
787 attribute20,
788 attribute21,
789 attribute22,
790 attribute23,
791 attribute24,
792 attribute25,
793 attribute26,
794 attribute27,
795 attribute28,
796 attribute29,
797 attribute30,
798 attribute_category
799 )
800 SELECT
801 action_code,
802 action_interval,
803 delete_mark,
804 text_code,
805 creation_date,
806 created_by,
807 last_update_date,
808 last_updated_by,
809 last_update_login,
810 attribute1,
811 attribute2,
812 attribute3,
813 attribute4,
814 attribute5,
815 attribute6,
816 attribute7,
817 attribute8,
818 attribute9,
819 attribute10,
820 attribute11,
821 attribute12,
822 attribute13,
823 attribute14,
824 attribute15,
825 attribute16,
826 attribute17,
827 attribute18,
828 attribute19,
829 attribute20,
830 attribute21,
831 attribute22,
832 attribute23,
833 attribute24,
834 attribute25,
835 attribute26,
836 attribute27,
837 attribute28,
838 attribute29,
839 attribute30,
840 attribute_category
841 FROM qc_actn_mst_bak
842 WHERE decode(migration_status,NULL,'NM') <> 'MO';
843
844 /* Insert action code description in translation table */
845 INSERT INTO gmd_actions_tl
846 (
847 action_code,
848 language,
849 action_desc,
850 source_lang,
851 creation_date,
852 created_by,
853 last_update_date,
854 last_updated_by,
855 last_update_login
856 )
857 SELECT
858 action_code,
859 l_base_lang,
860 action_desc,
861 l_base_lang,
862 creation_date,
863 created_by,
864 last_update_date,
865 last_updated_by,
866 last_update_login
867 FROM qc_actn_mst_bak
868 WHERE decode(migration_status,NULL,'NM') <> 'MO';
869
870
871 /* Updated record status to migrated */
872 UPDATE qc_actn_mst_bak
873 SET migration_status = 'MO';
874
875 l_rec_count := SQL%ROWCOUNT;
876
877 GMA_MIGRATION.gma_insert_message (
878 p_run_id => p_migration_id,
879 p_table_name => 'QC_ACTN_MST_BAK',
880 p_DB_ERROR => '',
881 p_param1 => '',
882 p_param2 => l_rec_count,
883 p_param3 => '',
884 p_param4 => '',
885 p_param5 => '',
886 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
887 p_message_type => 'P',
888 p_line_no => '1',
889 p_position => '',
890 p_base_message => '') ;
891
892
893 COMMIT;
894
895 EXCEPTION
896 WHEN OTHERS THEN
897 x_return_status := 'U';
898 GMA_MIGRATION.gma_insert_message (
899 p_run_id => p_migration_id,
900 p_table_name => 'QC_ACTN_MST_BAK',
901 p_DB_ERROR => sqlerrm,
902 p_param1 => '',
903 p_param2 => '',
904 p_param3 => '',
905 p_param4 => '',
906 p_param5 => '',
907 p_message_token => 'GMA_MIGRATION_DB_ERROR',
908 p_message_type => 'E',
909 p_line_no => '1',
910 p_position => '',
911 p_base_message => 'Failed to migrate action codes due to '||sqlerrm);
912
913 GMA_MIGRATION.gma_insert_message (
914 p_run_id => p_migration_id,
915 p_table_name => 'QC_ACTN_MST',
916 p_DB_ERROR => '',
917 p_param1 => '',
918 p_param2 => '',
919 p_param3 => '',
920 p_param4 => '',
921 p_param5 => '',
922 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
923 p_message_type => 'P',
924 p_line_no => '1',
925 p_position => '',
926 p_base_message => '');
927
928 END Migrate_Action_Codes;
929
930
931
932 /*===========================================================================
933 -- PROCEDURE:
934 -- Migrate_Hold_Reasons
935 --
936 -- DESCRIPTION:
937 -- This PL/SQL procedure is used to migrate hold reason codes
938 -- to a base and translated table for OPM patch 11.5.1J.
939 --
940 -- PARAMETERS:
941 -- p_migration_id - id to use to right to migration log
942 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
943 --
944 -- SYNOPSIS:
945 -- Migrate_Hold_Reasons;
946 --
947 -- HISTORY
948 --=========================================================================== */
949 PROCEDURE Migrate_Hold_Reasons (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
950 IS
951
952 /* ------------- LOCAL VARIABLES ------------------- */
953 l_table_name VARCHAR2(40);
954 l_rec_count NUMBER;
955 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
956
957 BEGIN
958
959 /* Get the installation's base language */
960 l_base_lang := Get_Base_Language;
961
962 INSERT INTO gmd_hold_reasons_b
963 (
964 qchold_res_code,
965 delete_mark,
966 text_code,
967 creation_date,
968 created_by,
969 last_update_date,
970 last_updated_by,
971 last_update_login,
972 attribute1,
973 attribute2,
974 attribute3,
975 attribute4,
976 attribute5,
977 attribute6,
978 attribute7,
979 attribute8,
980 attribute9,
981 attribute10,
982 attribute11,
983 attribute12,
984 attribute13,
985 attribute14,
986 attribute15,
987 attribute16,
988 attribute17,
989 attribute18,
990 attribute19,
991 attribute20,
992 attribute21,
993 attribute22,
994 attribute23,
995 attribute24,
996 attribute25,
997 attribute26,
998 attribute27,
999 attribute28,
1000 attribute29,
1001 attribute30,
1002 attribute_category
1003 )
1004 SELECT
1005 qchold_res_code,
1006 delete_mark,
1007 text_code,
1008 creation_date,
1009 created_by,
1010 SYSDATE,
1011 last_updated_by,
1012 last_update_login,
1013 attribute1,
1014 attribute2,
1015 attribute3,
1016 attribute4,
1017 attribute5,
1018 attribute6,
1019 attribute7,
1020 attribute8,
1021 attribute9,
1022 attribute10,
1023 attribute11,
1024 attribute12,
1025 attribute13,
1026 attribute14,
1027 attribute15,
1028 attribute16,
1029 attribute17,
1030 attribute18,
1031 attribute19,
1032 attribute20,
1033 attribute21,
1034 attribute22,
1035 attribute23,
1036 attribute24,
1037 attribute25,
1038 attribute26,
1039 attribute27,
1040 attribute28,
1041 attribute29,
1042 attribute30,
1043 attribute_category
1044 FROM qc_hres_mst_bak
1045 WHERE decode(migration_status,NULL,'NM') <> 'MO';
1046
1047 INSERT INTO gmd_hold_reasons_tl
1048 (
1049 qchold_res_code,
1050 language,
1051 qchold_res_desc,
1052 source_lang,
1053 creation_date,
1054 created_by,
1055 last_update_date,
1056 last_updated_by,
1057 last_update_login
1058 )
1059 SELECT
1060 qchold_res_code,
1061 l_base_lang,
1062 qchold_res_desc,
1063 l_base_lang,
1064 creation_date,
1065 created_by,
1066 last_update_date,
1067 last_updated_by,
1068 last_update_login
1069 FROM qc_hres_mst_bak
1070 WHERE decode(migration_status,NULL,'NM') <> 'MO';
1071
1072 /* Updated record status to migrated */
1073 UPDATE qc_hres_mst_bak
1074 SET migration_status = 'MO';
1075
1076 l_rec_count := SQL%ROWCOUNT;
1077
1078 GMA_MIGRATION.gma_insert_message (
1079 p_run_id => p_migration_id,
1080 p_table_name => 'QC_HRES_MST_BAK',
1081 p_DB_ERROR => '',
1082 p_param1 => '',
1083 p_param2 => l_rec_count,
1084 p_param3 => '',
1085 p_param4 => '',
1086 p_param5 => '',
1087 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
1088 p_message_type => 'P',
1089 p_line_no => '1',
1090 p_position => '',
1091 p_base_message => '');
1092
1093 COMMIT;
1094
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 x_return_status := 'U';
1099 GMA_MIGRATION.gma_insert_message (
1100 p_run_id => p_migration_id,
1101 p_table_name => 'QC_HRES_MST_BAK',
1102 p_DB_ERROR => sqlerrm,
1103 p_param1 => '',
1104 p_param2 => '',
1105 p_param3 => '',
1106 p_param4 => '',
1107 p_param5 => '',
1108 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1109 p_message_type => 'E',
1110 p_line_no => '1',
1111 p_position => '',
1112 p_base_message => 'Failed to migrate hold reasons due to '||sqlerrm);
1113
1114 GMA_MIGRATION.gma_insert_message (
1115 p_run_id => p_migration_id,
1116 p_table_name => 'QC_HRES_MST_BAK',
1117 p_DB_ERROR => '',
1118 p_param1 => '',
1119 p_param2 => '',
1120 p_param3 => '',
1121 p_param4 => '',
1122 p_param5 => '',
1123 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1124 p_message_type => 'P',
1125 p_line_no => '1',
1126 p_position => '',
1127 p_base_message => '');
1128
1129 END Migrate_Hold_Reasons;
1130
1131
1132 /*===========================================================================
1133 -- PROCEDURE:
1134 -- Migrate_Tests_Base
1135 --
1136 -- DESCRIPTION:
1137 -- This PL/SQL procedure is used to migrate records in the base
1138 -- test table for OPM patch 11.5.1J.
1139 --
1140 -- PARAMETERS:
1141 -- p_migration_id - id to use to right to migration log
1142 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
1143 --
1144 -- SYNOPSIS:
1145 -- Migrate_Tests_Base;
1146 --
1147 -- HISTORY
1148 -- M. Grosser 24-Sep-2002 Modified code to set display_precision to 9
1149 -- only if we are dealing with a numeric range
1150 -- (with or without a label)
1151 -- B. Stone 3-Sep-2003 BUG - 3051829; Update test's uom to NULL
1152 -- if the uom is invalid.
1153 -- B. Stone 25-Aug-2003 Bug - 3097029; expression error type requires
1154 -- at least one action code specified;
1155 -- if none are specified, then it is set to NULL
1156 -- Bug - 3051829; Test qty set to NULL instead of 0
1157 -- Uday Phadtare Bug 5025951. Default display_precision and report_precision from
1158 -- user configured profiles. If profile is NULL precision is considered as zero.
1159 --=========================================================================== */
1160 PROCEDURE Migrate_Tests_Base (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
1161 IS
1162
1163 /* ------------- LOCAL VARIABLES ------------------- */
1164 l_table_name VARCHAR2(40);
1165 l_rec_count NUMBER;
1166
1167 BEGIN
1168
1169 INSERT INTO gmd_qc_tests_b
1170 (
1171 test_id,
1172 test_code,
1173 test_method_id,
1174 test_type,
1175 test_unit,
1176 test_oprn_id,
1177 test_oprn_line_id,
1178 test_provider_code,
1179 test_class,
1180 min_value_num,
1181 max_value_num,
1182 below_spec_min,
1183 above_spec_max,
1184 above_spec_min,
1185 below_spec_max,
1186 exp_error_type,
1187 below_min_action_code,
1188 above_max_action_code,
1189 above_min_action_code,
1190 below_max_action_code,
1191 expression,
1192 display_precision,
1193 report_precision,
1194 priority,
1195 delete_mark,
1196 text_code,
1197 creation_date,
1198 created_by,
1199 last_update_date,
1200 last_updated_by,
1201 last_update_login,
1202 attribute1,
1203 attribute2,
1204 attribute3,
1205 attribute4,
1206 attribute5,
1207 attribute6,
1208 attribute7,
1209 attribute8,
1210 attribute9,
1211 attribute10,
1212 attribute11,
1213 attribute12,
1214 attribute13,
1215 attribute14,
1216 attribute15,
1217 attribute16,
1218 attribute17,
1219 attribute18,
1220 attribute19,
1221 attribute20,
1222 attribute21,
1223 attribute22,
1224 attribute23,
1225 attribute24,
1226 attribute25,
1227 attribute26,
1228 attribute27,
1229 attribute28,
1230 attribute29,
1231 attribute30,
1232 attribute_category
1233 )
1234 SELECT
1235 qcassy_typ_id,
1236 orgn_code||assay_code,
1237 0,
1238 decode(assay_type,0,'U',1,'N',2,'V',3,'T',4,'L'),
1239 qcunit_code,
1240 test_oprn_id,
1241 test_oprn_line_id,
1242 test_provider_code,
1243 assay_class,
1244 min_valid,
1245 max_valid,
1246 outside_spec_min,
1247 outside_spec_max,
1248 inside_spec_min,
1249 inside_spec_max,
1250 decode(error_val_type,'NUM','N','PCT','P',NULL),
1251 outside_min_action_code,
1252 outside_max_action_code,
1253 inside_min_action_code,
1254 inside_max_action_code,
1255 NULL,
1256 decode(assay_type,1,G_display_precision,4,G_display_precision,NULL), --Bug 5025951
1257 decode(assay_type,1,G_report_precision,4, G_report_precision, NULL), --Bug 5025951
1258 '5N',
1259 delete_mark,
1260 text_code,
1261 creation_date,
1262 created_by,
1263 last_update_date,
1264 last_updated_by,
1265 last_update_login,
1266 attribute1,
1267 attribute2,
1268 attribute3,
1269 attribute4,
1270 attribute5,
1271 attribute6,
1272 attribute7,
1273 attribute8,
1274 attribute9,
1275 attribute10,
1276 attribute11,
1277 attribute12,
1278 attribute13,
1279 attribute14,
1280 attribute15,
1281 attribute16,
1282 attribute17,
1283 attribute18,
1284 attribute19,
1285 attribute20,
1286 attribute21,
1287 attribute22,
1288 attribute23,
1289 attribute24,
1290 attribute25,
1291 attribute26,
1292 attribute27,
1293 attribute28,
1294 attribute29,
1295 attribute30,
1296 attribute_category
1297 FROM gmd_tests_b
1298 WHERE decode(migration_status,NULL,'NM') <> 'MO';
1299
1300
1301 -- Bug 3097029; expression error type requires at least
1302 -- one action code specified; if none specified,
1303 -- set to NULL
1304 --
1305
1306 UPDATE gmd_qc_tests_b
1307 set exp_error_type = NULL
1308 where below_min_action_code is null
1309 and above_max_action_code is null
1310 and above_min_action_code is null
1311 and below_max_action_code is null
1312 and exp_error_type is not null;
1313 COMMIT;
1314
1315
1316
1317 /* Updated record status to migrated */
1318 UPDATE gmd_tests_b
1319 SET migration_status = 'MO';
1320
1321 l_rec_count := SQL%ROWCOUNT;
1322
1323 GMA_MIGRATION.gma_insert_message (
1324 p_run_id => p_migration_id,
1325 p_table_name => 'GMD_TESTS_B',
1326 p_DB_ERROR => '',
1327 p_param1 => '',
1328 p_param2 => l_rec_count,
1329 p_param3 => '',
1330 p_param4 => '',
1331 p_param5 => '',
1332 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
1333 p_message_type => 'P',
1334 p_line_no => '1',
1335 p_position => '',
1336 p_base_message => '');
1337
1338 COMMIT;
1339
1340
1341 EXCEPTION
1342 WHEN OTHERS THEN
1343 x_return_status := 'U';
1344 GMA_MIGRATION.gma_insert_message (
1345 p_run_id => p_migration_id,
1346 p_table_name => 'GMD_TESTS_B',
1347 p_DB_ERROR => sqlerrm,
1348 p_param1 => '',
1349 p_param2 => '',
1350 p_param3 => '',
1351 p_param4 => '',
1352 p_param5 => '',
1353 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1354 p_message_type => 'E',
1355 p_line_no => '1',
1356 p_position => '',
1357 p_base_message => 'Failed to migrate tests base due to '||sqlerrm);
1358
1359 GMA_MIGRATION.gma_insert_message (
1360 p_run_id => p_migration_id,
1361 p_table_name => 'GMD_TESTS_B',
1362 p_DB_ERROR => '',
1363 p_param1 => '',
1364 p_param2 => '',
1365 p_param3 => '',
1366 p_param4 => '',
1367 p_param5 => '',
1368 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1369 p_message_type => 'P',
1370 p_line_no => '1',
1371 p_position => '',
1372 p_base_message => '');
1373
1374 END Migrate_Tests_Base;
1375
1376
1377 /*===========================================================================
1378 -- PROCEDURE:
1379 -- Migrate_Tests_Translated
1380 --
1381 -- DESCRIPTION:
1382 -- This PL/SQL procedure is used to migrate records in the translated
1383 -- test table for OPM patch 11.5.1J.
1384 --
1385 -- PARAMETERS:
1386 -- p_migration_id - id to use to right to migration log
1387 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
1388 --
1389 -- SYNOPSIS:
1390 -- Migrate_Tests_Translated;
1391 --
1392 -- HISTORY
1393 --=========================================================================== */
1394 PROCEDURE Migrate_Tests_Translated (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
1395 IS
1396
1397 /* ------------- LOCAL VARIABLES ------------------- */
1398 l_table_name VARCHAR2(40);
1399 l_rec_count NUMBER;
1400
1401 BEGIN
1402
1403 INSERT INTO gmd_qc_tests_tl
1404 (
1405 test_id,
1406 language,
1407 test_desc,
1408 source_lang,
1409 creation_date,
1410 created_by,
1411 last_update_date,
1412 last_updated_by,
1413 last_update_login
1414 )
1415 SELECT
1416 qcassy_typ_id,
1417 language,
1418 assay_desc,
1419 source_lang,
1420 creation_date,
1421 created_by,
1422 last_update_date,
1423 last_updated_by,
1424 last_update_login
1425 FROM gmd_tests_tl
1426 WHERE decode(migration_status,NULL,'NM') <> 'MO';
1427
1428
1429 /* Updated record status to migrated */
1430 UPDATE gmd_tests_tl
1431 SET migration_status = 'MO';
1432
1433 l_rec_count := SQL%ROWCOUNT;
1434
1435 GMA_MIGRATION.gma_insert_message (
1436 p_run_id => p_migration_id,
1437 p_table_name => 'GMD_TESTS_TL',
1438 p_DB_ERROR => '',
1439 p_param1 => '',
1440 p_param2 => l_rec_count,
1441 p_param3 => '',
1442 p_param4 => '',
1443 p_param5 => '',
1444 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
1445 p_message_type => 'P',
1446 p_line_no => '1',
1447 p_position => '',
1448 p_base_message => '');
1449
1450 COMMIT;
1451
1452
1453 EXCEPTION
1454 WHEN OTHERS THEN
1455 x_return_status := 'U';
1456 GMA_MIGRATION.gma_insert_message (
1457 p_run_id => p_migration_id,
1458 p_table_name => 'GMD_TESTS_TL',
1459 p_DB_ERROR => sqlerrm,
1460 p_param1 => '',
1461 p_param2 => '',
1462 p_param3 => '',
1463 p_param4 => '',
1464 p_param5 => '',
1465 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1466 p_message_type => 'E',
1467 p_line_no => '1',
1468 p_position => '',
1469 p_base_message => 'Failed to migrate tests translated due to '||sqlerrm);
1470
1471 GMA_MIGRATION.gma_insert_message (
1472 p_run_id => p_migration_id,
1473 p_table_name => 'GMD_TESTS_TL',
1474 p_DB_ERROR => '',
1475 p_param1 => '',
1476 p_param2 => '',
1477 p_param3 => '',
1478 p_param4 => '',
1479 p_param5 => '',
1480 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1481 p_message_type => 'P',
1482 p_line_no => '1',
1483 p_position => '',
1484 p_base_message => '');
1485
1486 END Migrate_Tests_Translated;
1487
1488
1489 /*===========================================================================
1490 -- PROCEDURE:
1491 -- Migrate_Values_Base
1492 --
1493 -- DESCRIPTION:
1494 -- This PL/SQL procedure is used to migrate records in the base
1495 -- test values table for OPM patch 11.5.1J.
1496 --
1497 -- PARAMETERS:
1498 -- p_migration_id - id to use to right to migration log
1499 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
1500 --
1501 -- SYNOPSIS:
1502 -- Migrate_Values_Base;
1503 --
1504 -- HISTORY
1505 --=========================================================================== */
1506 PROCEDURE Migrate_Values_Base (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
1507 IS
1508
1509 /* ------------- LOCAL VARIABLES ------------------- */
1510 l_table_name VARCHAR2(40);
1511 l_rec_count NUMBER;
1512
1513 BEGIN
1514
1515 INSERT INTO gmd_qc_test_values_b
1516 (
1517 test_value_id,
1518 test_id,
1519 value_char,
1520 min_num,
1521 max_num,
1522 text_range_seq,
1523 expression_ref_test_id,
1524 text_code,
1525 creation_date,
1526 created_by,
1527 last_update_date,
1528 last_updated_by,
1529 last_update_login,
1530 attribute1,
1531 attribute2,
1532 attribute3,
1533 attribute4,
1534 attribute5,
1535 attribute6,
1536 attribute7,
1537 attribute8,
1538 attribute9,
1539 attribute10,
1540 attribute11,
1541 attribute12,
1542 attribute13,
1543 attribute14,
1544 attribute15,
1545 attribute16,
1546 attribute17,
1547 attribute18,
1548 attribute19,
1549 attribute20,
1550 attribute21,
1551 attribute22,
1552 attribute23,
1553 attribute24,
1554 attribute25,
1555 attribute26,
1556 attribute27,
1557 attribute28,
1558 attribute29,
1559 attribute30,
1560 attribute_category
1561 )
1562 SELECT
1563 qcassy_val_id,
1564 qcassy_typ_id,
1565 assay_value,
1566 value_num_min,
1567 value_num_max,
1568 assay_value_range_order,
1569 '',
1570 text_code,
1571 creation_date,
1572 created_by,
1573 last_update_date,
1574 last_updated_by,
1575 last_update_login,
1576 attribute1,
1577 attribute2,
1578 attribute3,
1579 attribute4,
1580 attribute5,
1581 attribute6,
1582 attribute7,
1583 attribute8,
1584 attribute9,
1585 attribute10,
1586 attribute11,
1587 attribute12,
1588 attribute13,
1589 attribute14,
1590 attribute15,
1591 attribute16,
1592 attribute17,
1593 attribute18,
1594 attribute19,
1595 attribute20,
1596 attribute21,
1597 attribute22,
1598 attribute23,
1599 attribute24,
1600 attribute25,
1601 attribute26,
1602 attribute27,
1603 attribute28,
1604 attribute29,
1605 attribute30,
1606 attribute_category
1607 FROM gmd_test_values_b
1608 WHERE decode(migration_status,NULL,'NM') <> 'MO';
1609
1610 /* Updated record status to migrated */
1611 UPDATE gmd_test_values_b
1612 SET migration_status = 'MO';
1613
1614 l_rec_count := SQL%ROWCOUNT;
1615
1616 GMA_MIGRATION.gma_insert_message (
1617 p_run_id => p_migration_id,
1618 p_table_name => 'GMD_TEST_VALUES_B',
1619 p_DB_ERROR => '',
1620 p_param1 => '',
1621 p_param2 => l_rec_count,
1622 p_param3 => '',
1623 p_param4 => '',
1624 p_param5 => '',
1625 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
1626 p_message_type => 'P',
1627 p_line_no => '1',
1628 p_position => '',
1629 p_base_message => '');
1630
1631 COMMIT;
1632
1633
1634 EXCEPTION
1635 WHEN OTHERS THEN
1636 x_return_status := 'U';
1637 GMA_MIGRATION.gma_insert_message (
1638 p_run_id => p_migration_id,
1639 p_table_name => 'GMD_TEST_VALUES_B',
1640 p_DB_ERROR => sqlerrm,
1641 p_param1 => '',
1642 p_param2 => '',
1643 p_param3 => '',
1644 p_param4 => '',
1645 p_param5 => '',
1646 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1647 p_message_type => 'E',
1648 p_line_no => '1',
1649 p_position => '',
1650 p_base_message => 'Failed to migrate test values base due to '||sqlerrm);
1651
1652 GMA_MIGRATION.gma_insert_message (
1653 p_run_id => p_migration_id,
1654 p_table_name => 'GMD_TEST_VALUES_B',
1655 p_DB_ERROR => '',
1656 p_param1 => '',
1657 p_param2 => '',
1658 p_param3 => '',
1659 p_param4 => '',
1660 p_param5 => '',
1661 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1662 p_message_type => 'P',
1663 p_line_no => '1',
1664 p_position => '',
1665 p_base_message => '');
1666
1667 END Migrate_Values_Base;
1668
1669
1670 /*===========================================================================
1671 -- PROCEDURE:
1672 -- Migrate_Values_Translated
1673 --
1674 -- DESCRIPTION:
1675 -- This PL/SQL procedure is used to migrate records in the translated
1676 -- test table for OPM patch 11.5.1J.
1677 --
1678 -- PARAMETERS:
1679 -- p_migration_id - id to use to right to migration log
1680 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
1681 --
1682 -- SYNOPSIS:
1683 -- Migrate_Values_Translated;
1684 --
1685 -- HISTORY
1686 --=========================================================================== */
1687 PROCEDURE Migrate_Values_Translated (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
1688 IS
1689
1690 /* ------------- LOCAL VARIABLES ------------------- */
1691 l_table_name VARCHAR2(40);
1692 l_rec_count NUMBER;
1693
1694 BEGIN
1695
1696 INSERT INTO gmd_qc_test_values_tl
1697 (
1698 test_value_id,
1699 language,
1700 test_value_desc,
1701 display_label_numeric_range,
1702 source_lang,
1703 creation_date,
1704 created_by,
1705 last_update_date,
1706 last_updated_by,
1707 last_update_login
1708 )
1709 SELECT
1710 t.qcassy_val_id,
1711 t.language,
1712 t.value_desc,
1713 DECODE(b.value_desc,NULL,b.assay_value,NULL),
1714 t.source_lang,
1715 t.creation_date,
1716 t.created_by,
1717 t.last_update_date,
1718 t.last_updated_by,
1719 t.last_update_login
1720 FROM gmd_test_values_tl t,
1721 gmd_test_values_b b
1722 WHERE t.qcassy_val_id = b.qcassy_val_id AND
1723 decode(t.migration_status,NULL,'NM') <> 'MO';
1724
1725 /* Updated record status to migrated */
1726 UPDATE gmd_test_values_tl
1727 SET migration_status = 'MO';
1728
1729 l_rec_count := SQL%ROWCOUNT;
1730
1731 GMA_MIGRATION.gma_insert_message (
1732 p_run_id => p_migration_id,
1733 p_table_name => 'GMD_TEST_VALUES_TL',
1734 p_DB_ERROR => '',
1735 p_param1 => '',
1736 p_param2 => l_rec_count,
1737 p_param3 => '',
1738 p_param4 => '',
1739 p_param5 => '',
1740 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
1741 p_message_type => 'P',
1742 p_line_no => '1',
1743 p_position => '',
1744 p_base_message => '');
1745
1746 COMMIT;
1747
1748
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 x_return_status := 'U';
1752 GMA_MIGRATION.gma_insert_message (
1753 p_run_id => p_migration_id,
1754 p_table_name => 'GMD_TEST_VALUES_TL',
1755 p_DB_ERROR => sqlerrm,
1756 p_param1 => '',
1757 p_param2 => '',
1758 p_param3 => '',
1759 p_param4 => '',
1760 p_param5 => '',
1761 p_message_token => 'GMA_MIGRATION_DB_ERROR',
1762 p_message_type => 'E',
1763 p_line_no => '1',
1764 p_position => '',
1765 p_base_message => 'Failed to migrate test values translated due to '||sqlerrm);
1766
1767 GMA_MIGRATION.gma_insert_message (
1768 p_run_id => p_migration_id,
1769 p_table_name => 'GMD_TEST_VALUES_TL',
1770 p_DB_ERROR => '',
1771 p_param1 => '',
1772 p_param2 => '',
1773 p_param3 => '',
1774 p_param4 => '',
1775 p_param5 => '',
1776 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
1777 p_message_type => 'P',
1778 p_line_no => '1',
1779 p_position => '',
1780 p_base_message => '');
1781
1782 END Migrate_Values_Translated;
1783
1784 /*===========================================================================
1785 -- PROCEDURE:
1786 -- insert_temp_table_rows
1787 --
1788 -- DESCRIPTION:
1789 -- This Global temprary table gmd_qc_spec_mst_gtmp along with
1790 -- gmd_spec_mapping_gtmp is used to improve Specification migration performance.
1791 --
1792 -- PARAMETERS:
1793 -- p_spec_hdr_id - All qc_spec_mst records for spec_hdr_id are inserted in
1794 -- Global temprary table gmd_qc_spec_mst_gtmp for performance.
1795 --
1796 -- SYNOPSIS:
1797 -- insert_temp_table_rows;
1798 --
1799 -- HISTORY
1800 -- PK Bug 4226263 07-Jun-2005 Created this procedure.
1801 --=========================================================================== */
1802 PROCEDURE insert_temp_table_rows(p_spec_hdr_id IN NUMBER)
1803 IS
1804
1805 Begin
1806
1807 Insert Into gmd_qc_spec_mst_gtmp
1808 (
1809 QC_SPEC_ID,
1810 QCASSY_TYP_ID,
1811 ORGN_CODE,
1812 ITEM_ID,
1813 LOT_ID,
1814 WHSE_CODE,
1815 LOCATION,
1816 FORMULA_ID,
1817 FORMULALINE_ID,
1818 ROUTING_ID,
1819 ROUTINGSTEP_ID,
1820 OPRN_ID,
1821 DOC_TYPE,
1822 DOC_ID,
1823 DOCLINE_ID,
1824 CUST_ID,
1825 CUST_SPECIFICATION,
1826 CUST_CERTIFICATION,
1827 VENDOR_ID,
1828 VENDOR_SPECIFICATION,
1829 VENDOR_CERTIFICATION,
1830 BATCH_ID,
1831 ASSAY_CODE,
1832 TEXT_SPEC,
1833 TARGET_SPEC,
1834 MIN_SPEC,
1835 MAX_SPEC,
1836 QCUNIT_CODE,
1837 FROM_DATE,
1838 TO_DATE,
1839 OUTACTION_CODE,
1840 OUTACTION_INTERVAL,
1841 PREFERENCE,
1842 PRINT_COA_SHIPPED,
1843 PRINT_COA_INVOICED,
1844 VENDOR_COA_REQUIRED,
1845 TEST_OPRN_ID,
1846 TEST_OPRN_LINE_ID,
1847 TEST_PROVIDER_CODE,
1848 DELETE_MARK,
1849 TEXT_CODE,
1850 TRANS_CNT,
1851 CREATION_DATE,
1852 LAST_UPDATE_LOGIN,
1853 CREATED_BY,
1854 LAST_UPDATE_DATE,
1855 LAST_UPDATED_BY,
1856 ROUTINGSTEP_NO,
1857 ATTRIBUTE1,
1858 ATTRIBUTE2,
1859 ATTRIBUTE3,
1860 ATTRIBUTE4,
1861 ATTRIBUTE5,
1862 ATTRIBUTE6,
1863 ATTRIBUTE7,
1864 ATTRIBUTE8,
1865 ATTRIBUTE9,
1866 ATTRIBUTE10,
1867 ATTRIBUTE11,
1868 ATTRIBUTE12,
1869 ATTRIBUTE13,
1870 ATTRIBUTE14,
1871 ATTRIBUTE15,
1872 ATTRIBUTE16,
1873 ATTRIBUTE17,
1874 ATTRIBUTE18,
1875 ATTRIBUTE19,
1876 ATTRIBUTE20,
1877 ATTRIBUTE21,
1878 ATTRIBUTE22,
1879 ATTRIBUTE23,
1880 ATTRIBUTE24,
1881 ATTRIBUTE25,
1882 ATTRIBUTE26,
1883 ATTRIBUTE27,
1884 ATTRIBUTE28,
1885 ATTRIBUTE29,
1886 ATTRIBUTE30,
1887 ATTRIBUTE_CATEGORY,
1888 OPM_CUST_ID,
1889 CHARGE,
1890 OUTSIDE_SPEC_MIN,
1891 INSIDE_SPEC_MIN,
1892 INSIDE_SPEC_MAX,
1893 OUTSIDE_SPEC_MAX,
1894 ERROR_VAL_TYPE,
1895 OUTSIDE_MIN_ACTION_CODE,
1896 INSIDE_MIN_ACTION_CODE,
1897 INSIDE_MAX_ACTION_CODE,
1898 OUTSIDE_MAX_ACTION_CODE,
1899 MIN_CHAR,
1900 MAX_CHAR,
1901 ORDER_HEADER_ID,
1902 ORDER_LINE_NO,
1903 ORDER_ORG_ID,
1904 QC_REC_TYPE,
1905 SHIP_TO_SITE_ID,
1906 SPEC_HDR_ID,
1907 OLD_FROM_DATE,
1908 OLD_TO_DATE,
1909 MIGRATION_STATUS
1910 )
1911 SELECT
1912 QC_SPEC_ID,
1913 QCASSY_TYP_ID,
1914 ORGN_CODE,
1915 ITEM_ID,
1916 LOT_ID,
1917 WHSE_CODE,
1918 LOCATION,
1919 FORMULA_ID,
1920 FORMULALINE_ID,
1921 ROUTING_ID,
1922 ROUTINGSTEP_ID,
1923 OPRN_ID,
1924 DOC_TYPE,
1925 DOC_ID,
1926 DOCLINE_ID,
1927 CUST_ID,
1928 CUST_SPECIFICATION,
1929 CUST_CERTIFICATION,
1930 VENDOR_ID,
1931 VENDOR_SPECIFICATION,
1932 VENDOR_CERTIFICATION,
1933 BATCH_ID,
1934 ASSAY_CODE,
1935 TEXT_SPEC,
1936 TARGET_SPEC,
1937 MIN_SPEC,
1938 MAX_SPEC,
1939 QCUNIT_CODE,
1940 FROM_DATE,
1941 TO_DATE,
1942 OUTACTION_CODE,
1943 OUTACTION_INTERVAL,
1944 PREFERENCE,
1945 PRINT_COA_SHIPPED,
1946 PRINT_COA_INVOICED,
1947 VENDOR_COA_REQUIRED,
1948 TEST_OPRN_ID,
1949 TEST_OPRN_LINE_ID,
1950 TEST_PROVIDER_CODE,
1951 DELETE_MARK,
1952 TEXT_CODE,
1953 TRANS_CNT,
1954 CREATION_DATE,
1955 LAST_UPDATE_LOGIN,
1956 CREATED_BY,
1957 LAST_UPDATE_DATE,
1958 LAST_UPDATED_BY,
1959 ROUTINGSTEP_NO,
1960 ATTRIBUTE1,
1961 ATTRIBUTE2,
1962 ATTRIBUTE3,
1963 ATTRIBUTE4,
1964 ATTRIBUTE5,
1965 ATTRIBUTE6,
1966 ATTRIBUTE7,
1967 ATTRIBUTE8,
1968 ATTRIBUTE9,
1969 ATTRIBUTE10,
1970 ATTRIBUTE11,
1971 ATTRIBUTE12,
1972 ATTRIBUTE13,
1973 ATTRIBUTE14,
1974 ATTRIBUTE15,
1975 ATTRIBUTE16,
1976 ATTRIBUTE17,
1977 ATTRIBUTE18,
1978 ATTRIBUTE19,
1979 ATTRIBUTE20,
1980 ATTRIBUTE21,
1981 ATTRIBUTE22,
1982 ATTRIBUTE23,
1983 ATTRIBUTE24,
1984 ATTRIBUTE25,
1985 ATTRIBUTE26,
1986 ATTRIBUTE27,
1987 ATTRIBUTE28,
1988 ATTRIBUTE29,
1989 ATTRIBUTE30,
1990 ATTRIBUTE_CATEGORY,
1991 OPM_CUST_ID,
1992 CHARGE,
1993 OUTSIDE_SPEC_MIN,
1994 INSIDE_SPEC_MIN,
1995 INSIDE_SPEC_MAX,
1996 OUTSIDE_SPEC_MAX,
1997 ERROR_VAL_TYPE,
1998 OUTSIDE_MIN_ACTION_CODE,
1999 INSIDE_MIN_ACTION_CODE,
2000 INSIDE_MAX_ACTION_CODE,
2001 OUTSIDE_MAX_ACTION_CODE,
2002 MIN_CHAR,
2003 MAX_CHAR,
2004 ORDER_HEADER_ID,
2005 ORDER_LINE_NO,
2006 ORDER_ORG_ID,
2007 QC_REC_TYPE,
2008 SHIP_TO_SITE_ID,
2009 SPEC_HDR_ID,
2010 OLD_FROM_DATE,
2011 OLD_TO_DATE,
2012 MIGRATION_STATUS
2013 FROM qc_spec_mst
2014 WHERE spec_hdr_id = p_spec_hdr_id;
2015
2016 END insert_temp_table_rows;
2017
2018 /*===========================================================================
2019 -- PROCEDURE:
2020 -- Migrate_Specifications
2021 --
2022 -- DESCRIPTION:
2023 -- This PL/SQL procedure is used to migrate specifications into a
2024 -- header/detail/validity rule model for OPM patch 11.5.1J.
2025 --
2026 -- PARAMETERS:
2027 -- p_migration_id - id to use to right to migration log
2028 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
2029 --
2030 -- SYNOPSIS:
2031 -- Migrate_Specifications;
2032 --
2033 -- HISTORY
2034 -- M. Grosser 24-Sep-2002 Modified code to set display_precision to 9
2035 -- only if we are dealing with a numeric range
2036 -- (with or without a label)
2037 -- M. Grosser 27-Sep-2002 BUG 2593962 - Changed value from 900 to 1000
2038 -- for Obsolete/Expired specification versions
2039 -- and their corresponding validity rules
2040 -- M. Grosser 29-Sep-2002 BUG 2596689 - Modified code to check to see
2041 -- if a sample no is used more than once within an
2042 -- organization. If so, add the record type to the
2043 -- sample name
2044 -- M. Grosser 30-Sep-2002 BUG 2598751 - Modified code to check to see
2045 -- if any of the coa indicators are set for the
2046 -- specification vers.
2047 -- M. Grosser 08-Oct-2002 Set sampling_plan_id to NULL in validity rules
2048 -- C. Nagar 18-Dec-2002 Bug 2714197 - Fetch recipe id, no, and version based on
2049 -- batch id
2050 -- B. Stone 7-Aug-2003 Bug 3088400 - Order spec tests by preference
2051 -- field in qc_spec_mst
2052 -- Bug 3084500 - Print_spec_ind, print_result_ind
2053 -- fields in gmd_spec_tests are set to 'Y' when
2054 -- qc_spec_mst.print_coa_shipped = 1 (yes); value
2055 -- remains Null otherwise.
2056 -- B. Stone 25-Aug-2003 Bug - 3097029; expression error type requires
2057 -- at least one action code specified;
2058 -- if none specified, set to NULL
2059 -- Bug - 3051829; Test qty set to NULL instead of 0
2060 -- Set the test's uom to NULL
2061 -- B.Stone 4-Jan-2004 Added code so the same version of the code will
2062 -- work for J or K.
2063 -- B.Stone 23-Apr-2004 Bug 3588513; changed decode for PRINT_SPEC_IND and
2064 -- PRINT_RESULT_IND to include PRINT_COA_INVOICED.
2065 -- B. Stone 9-July-2004 Bug 3691496; Changed decode for EXP_ERROR_TYP
2066 -- to eliminate the update statement.
2067 -- B. Stone 13-Oct-2004 Bug 3934121;
2068 -- 1) Removed preference from order by in
2069 -- c_get_spec_details, so spec tests and result
2070 -- tests are displayed in the same order, by
2071 -- assay_code.
2072 -- 2) Changed the Where clause in c_get_coa_inds
2073 -- to access rows for a version from
2074 -- GMD_SPEC_MAPPING table instead of using dates
2075 -- to retrieve the tests for a version.
2076 -- 3) Changed logic for deriving l_version_end_date
2077 -- 4) Removed check-dup logic
2078 --
2079 -- PK Bug 4226263 07-June-2005 Created temporary tables gmd_qc_spec_mst_gtmp
2080 -- and gmd_spec_mapping_gtmp. These tables contain subset of
2081 -- data being migrated . These tables are used for performance
2082 -- Improvement. Varioys cusors are changed to use gmd_qc_spec_mst_gtmp
2083 -- instead of qc_spec_mst and gmd_spec_mapping_gtmp instead of gmd_spec_mapping
2084 -- Suitable code changes are made to insert data into these tables.
2085 -- Uday Phadtare Bug 5025951. Default display_precision and report_precision from
2086 -- user configured profiles. If profile is NULL precision is considered as zero.
2087 --=========================================================================== */
2088 PROCEDURE Migrate_Specifications (p_migration_id IN NUMBER,
2089 x_return_status OUT NOCOPY VARCHAR2)
2090 IS
2091
2092 /* ------------- LOCAL VARIABLES ------------------- */
2093 l_first NUMBER;
2094 l_owner_orgn_code SY_ORGN_MST.orgn_code%TYPE;
2095 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
2096 l_spec_name VARCHAR2(80);
2097 l_spec_version NUMBER;
2098 l_test_seq NUMBER;
2099 l_spec_id NUMBER;
2100 l_spec_vr_id NUMBER;
2101 l_start_date DATE;
2102 l_end_date DATE;
2103 l_version_end_date DATE;
2104 l_version_end_date_a DATE;
2105 l_version_end_date_b DATE;
2106 l_new_start_date DATE;
2107 l_todays_date DATE := SYSDATE;
2108 l_spec_status GMD_SPECIFICATIONS_B.spec_status%TYPE;
2109 l_rec_count NUMBER := 0;
2110 l_retest_lot_exp_ind VARCHAR2(1);
2111 l_supplier_id NUMBER;
2112 l_supplier_site_id NUMBER;
2113 l_qcassy_typ_id NUMBER;
2114 l_spec_hdr_id NUMBER;
2115 l_cnt NUMBER;
2116 l_from_dt DATE;
2117 l_to_dt DATE;
2118 l_rslt_cnt NUMBER;
2119 l_qc_spec_id NUMBER;
2120 l_no_overlap_ind VARCHAR2(1);
2121 l_sysdate DATE;
2122 l_min_rslt_dt DATE;
2123 prev_qc_spec_id NUMBER;
2124 prev_from_dt DATE;
2125 prev_to_dt DATE;
2126 l_patch_level VARCHAR2(2); -- Valid values are: NULL and "K+"
2127 l_sql_stmt VARCHAR2(2000);
2128 l_y VARCHAR2(1);
2129 l_1 NUMBER;
2130 l_gmd VARCHAR2(3) := 'GMD';
2131 -- Bug 4252591
2132 l_max_date DATE;
2133 -- Bug 4898620
2134 l_copied_text_code NUMBER;
2135
2136 -- Cursors c_get_start_and_end, c_get_version_end_date_a, c_get_version_end_date_b, c_get_creation
2137 -- c_get_last_update, c_get_spec_details, (c_get_coa_inds as well ?????) would be based on temporary table
2138 -- gmd_qc_spec_mst_gtmp
2139
2140 /* Get SYSDATE */
2141 CURSOR c_get_sysdate IS
2142 SELECT sysdate FROM DUAL;
2143
2144 /* Get the ids of the spec header groupings that have not been migrated */
2145 CURSOR c_get_spec_header IS
2146 SELECT DISTINCT spec_hdr_id, item_id, orgn_code
2147 FROM qc_spec_mst
2148 WHERE migration_status is NULL;
2149
2150 hdr_rec c_get_spec_header%ROWTYPE;
2151
2152 /* Select the item no associated to spec */
2153 CURSOR c_get_item_no IS
2154 SELECT item_no, lot_ctl
2155 FROM ic_item_mst
2156 WHERE item_id = hdr_rec.item_id;
2157 item_rec c_get_item_no%ROWTYPE;
2158
2159 /* Get the earliest start and latest end date */
2160 CURSOR c_get_start_and_end IS
2161 SELECT min(from_date), max(to_date)
2162 FROM gmd_qc_spec_mst_gtmp
2163 WHERE spec_hdr_id = hdr_rec.spec_hdr_id
2164 AND migration_status is NULL;
2165
2166 /* Get the creation info for the spec version */
2167 CURSOR c_get_creation IS
2168 SELECT creation_date, created_by
2169 FROM gmd_qc_spec_mst_gtmp
2170 WHERE from_date <= l_start_date and
2171 to_date >= l_version_end_date and
2172 spec_hdr_id = hdr_rec.spec_hdr_id and
2173 migration_status is NULL
2174 ORDER BY creation_date;
2175 create_rec c_get_creation%ROWTYPE;
2176
2177 /* Get the last_updated info for the spec version */
2178 CURSOR c_get_last_update IS
2179 SELECT last_update_date, last_updated_by, last_update_login
2180 FROM gmd_qc_spec_mst_gtmp
2181 WHERE from_date <= l_start_date and
2182 to_date >= l_version_end_date and
2183 spec_hdr_id = hdr_rec.spec_hdr_id and
2184 migration_status is NULL
2185 ORDER BY last_update_date desc;
2186 update_rec c_get_last_update%ROWTYPE;
2187
2188 /* Find the end date of the version (earliest end date in group) */
2189 CURSOR c_get_version_end_date_a IS
2190 SELECT min(to_date)
2191 FROM gmd_qc_spec_mst_gtmp
2192 ----from_date <= l_start_date and
2193 WHERE to_date >= l_start_date
2194 and spec_hdr_id = hdr_rec.spec_hdr_id
2195 and migration_status is NULL;
2196
2197 CURSOR c_get_version_end_date_b IS
2198 SELECT min(from_date) - 1/86400
2199 FROM gmd_qc_spec_mst_gtmp
2200 WHERE from_date > l_start_date
2201 and spec_hdr_id = hdr_rec.spec_hdr_id
2202 and migration_status is NULL;
2203
2204
2205 /* Bug 3241005; A separate cursor is required if a new l_version_end_date
2206 is not found with the original cursor for l_version_end_date.
2207 This is needed when there are not spec tests with a from_date
2208 earlier than the l_start_date and a to_date greater than the
2209 l_start_date */
2210 /* Bug 3934121; Removed CURSOR c_get_ver_end_date_no_overlap */
2211
2212 /* Get the next spec id */
2213 CURSOR c_get_spec_id IS
2214 SELECT gmd_qc_spec_id_s.nextval
2215 FROM SYS.DUAL;
2216
2217 /* Get the next spec validity rule id */
2218 CURSOR c_get_spec_vr_id IS
2219 SELECT gmd_qc_spec_vr_id_s.nextval
2220 FROM SYS.DUAL;
2221
2222 /* Bug 3934121; Removed CURSOR c_chk_for_dup_tests */
2223 /* Bug 3934121; Removed CURSOR c_get_dup_tests */
2224
2225
2226 /* Cursor to select detail records associated with a spec version */
2227 /* Bug 3241005; Cursor c_get_spec_details is modified to retrieve spec tests
2228 when no overlap spec tests condition occurs. */
2229 CURSOR c_get_spec_details IS
2230 SELECT qc_spec_id,
2231 qcassy_typ_id,
2232 orgn_code,
2233 item_id,
2234 lot_id,
2235 whse_code,
2236 location,
2237 formula_id,
2238 formulaline_id,
2239 routing_id,
2240 routingstep_id,
2241 routingstep_no,
2242 oprn_id,
2243 doc_type,
2244 doc_id,
2245 docline_id,
2246 cust_id,
2247 cust_specification,
2248 cust_certification,
2249 vendor_id,
2250 vendor_specification,
2251 vendor_certification,
2252 batch_id,
2253 text_spec,
2254 target_spec,
2255 min_spec,
2256 max_spec,
2257 qcunit_code,
2258 from_date,
2259 to_date,
2260 outaction_code,
2261 outaction_interval,
2262 print_coa_shipped,
2263 print_coa_invoiced,
2264 vendor_coa_required,
2265 test_oprn_id,
2266 test_oprn_line_id,
2267 test_provider_code,
2268 charge,
2269 min_char,
2270 max_char,
2271 outside_spec_min,
2272 outside_spec_max,
2273 inside_spec_min,
2274 inside_spec_max,
2275 error_val_type,
2276 outside_min_action_code,
2277 outside_max_action_code,
2278 inside_min_action_code,
2279 inside_max_action_code,
2280 order_header_id,
2281 order_line_no,
2282 order_org_id,
2283 qc_rec_type,
2284 ship_to_site_id,
2285 delete_mark,
2286 text_code,
2287 creation_date,
2288 created_by,
2289 last_update_date,
2290 last_updated_by,
2291 last_update_login,
2292 attribute1,
2293 attribute2,
2294 attribute3,
2295 attribute4,
2296 attribute5,
2297 attribute6,
2298 attribute7,
2299 attribute8,
2300 attribute9,
2301 attribute10,
2302 attribute11,
2303 attribute12,
2304 attribute13,
2305 attribute14,
2306 attribute15,
2307 attribute16,
2308 attribute17,
2309 attribute18,
2310 attribute19,
2311 attribute20,
2312 attribute21,
2313 attribute22,
2314 attribute23,
2315 attribute24,
2316 attribute25,
2317 attribute26,
2318 attribute27,
2319 attribute28,
2320 attribute29,
2321 attribute30,
2322 attribute_category
2323 FROM gmd_qc_spec_mst_gtmp
2324 WHERE from_date <= l_start_date and
2325 to_date >= l_version_end_date and
2326 spec_hdr_id = hdr_rec.spec_hdr_id and
2327 migration_status is NULL
2328 ORDER BY assay_code;
2329 sd c_get_spec_details%ROWTYPE;
2330
2331 -- ORDER BY preference,assay_code;
2332 -- Bug 3934121; Removed preference from order by.
2333 -- Modified WHERE clause. The old Where clause is:
2334 -- WHERE ( ( from_date <= l_start_date and
2335 -- to_date >= l_version_end_date and
2336 -- l_no_overlap_ind = 'N' ) OR
2337 -- ( from_date >= l_start_date and
2338 -- to_date <= l_version_end_date and
2339 -- l_no_overlap_ind = 'Y' ) ) AND
2340 -- spec_hdr_id = hdr_rec.spec_hdr_id and
2341 -- migration_status is NULL
2342
2343 -- Get the lot numbers
2344 CURSOR c_lot_nums (v_lot_id number) IS
2345 SELECT lot_no, sublot_no
2346 FROM ic_lots_mst
2347 WHERE lot_id = v_lot_id;
2348
2349 l_lot_no ic_lots_mst.lot_no%TYPE;
2350 l_sublot_no ic_lots_mst.sublot_no%TYPE;
2351
2352 -- Get the recipe id + no + version
2353 CURSOR c_recipe_id_no_vers (v_batch_id number) IS
2354 SELECT r.recipe_id, r.recipe_no, r.recipe_version
2355 FROM gmd_recipes r,
2356 gmd_recipe_validity_rules feff,
2357 gme_batch_header bh
2358 WHERE bh.batch_id = v_batch_id
2359 AND bh.recipe_validity_rule_id = feff.recipe_validity_rule_id
2360 AND feff.recipe_id = r.recipe_id;
2361
2362 CURSOR c_routing_no_vers (v_routing_id number) IS
2363 SELECT routing_no, routing_vers
2364 FROM gmd_routings_b
2365 WHERE routing_id = v_routing_id;
2366
2367 -- Get the formula name and version
2368 CURSOR c_formula_num_vers (v_formula_id number) IS
2369 SELECT formula_no, formula_vers
2370 FROM fm_form_mst_b
2371 WHERE formula_id = v_formula_id;
2372
2373 l_formula_no fm_form_mst_b.formula_no%TYPE;
2374 l_formula_vers fm_form_mst_b.formula_vers%TYPE;
2375
2376 l_recipe_id gmd_recipes_b.recipe_id%TYPE;
2377 l_recipe_no gmd_recipes_b.recipe_no%TYPE;
2378 l_recipe_version gmd_recipes_b.recipe_version%TYPE;
2379
2380 l_routing_no gmd_routings_b.routing_no%TYPE;
2381 l_routing_vers gmd_routings_b.routing_vers%TYPE;
2382
2383 -- Get the operation name and version
2384 CURSOR c_oprn_num_vers (v_oprn_id number) IS
2385 SELECT oprn_no, oprn_vers
2386 FROM gmd_operations_b
2387 WHERE oprn_id = v_oprn_id;
2388
2389 l_oprn_no gmd_operations_b.oprn_no%TYPE;
2390 l_oprn_vers gmd_operations_b.oprn_vers%TYPE;
2391
2392 /* M. Grosser 30-Sep-2002 BUG 2598751 - Modified code to check to see
2393 if any of the coa indicators are set for the
2394 specification vers.
2395 */
2396 /* Cursor to select coa indicators within a spec version */
2397 -- Bug 3934121; changed the Where clause to access rows for a version
2398 -- from GMD_SPEC_MAPPING table instead of using dates
2399 -- to retrieve the tests for a version.
2400 CURSOR c_get_coa_inds IS
2401 SELECT
2402 max(print_coa_shipped) as print_coa_shipped,
2403 max(print_coa_invoiced) as print_coa_invoiced,
2404 max(vendor_coa_required) as vendor_coa_required
2405 FROM gmd_qc_spec_mst_gtmp s ,
2406 gmd_spec_mapping_gtmp m
2407 WHERE m.spec_id = l_spec_id
2408 AND s.qc_spec_id = m.qc_spec_id ;
2409 coa_rec c_get_coa_inds%ROWTYPE;
2410
2411
2412 -- Bug 3859406; replaced table all_tab_columns with fnd_columns;
2413 -- per apps standards
2414 -- Revert back to all_tab_columns since the file is delivered to 11.5.10
2415 CURSOR c_patch_level IS
2416 SELECT 'K+'
2417 from all_tab_columns
2418 where table_name='GMD_SPECIFICATIONS_B'
2419 and column_name='SPEC_TYPE'
2420 and owner = l_gmd;
2421
2422
2423 BEGIN
2424
2425 l_y := 'Y';
2426 l_1 := 1;
2427
2428 -- Begin Bug 4252591 Clear VR end dates if those match SY$MAX_DATE
2429 BEGIN
2430 l_max_date := trunc(to_date(nvl(fnd_profile.value('SY$MAX_DATE'),'2010/12/31'), 'YYYY/MM/DD' ));
2431 EXCEPTION
2432 WHEN OTHERS THEN
2433 l_max_date := trunc(to_date('2010/12/31' , 'YYYY/MM/DD'));
2434 END;
2435
2436 GMA_MIGRATION.gma_insert_message (
2437 p_run_id => p_migration_id,
2438 p_table_name => 'QC_SPEC_MST',
2439 p_DB_ERROR => '',
2440 p_param1 => '',
2441 p_param2 => '',
2442 p_param3 => '',
2443 p_param4 => '',
2444 p_param5 => '',
2445 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
2446 p_message_type => 'P',
2447 p_line_no => '1',
2448 p_position => '',
2449 p_base_message => '');
2450 COMMIT;
2451
2452 /* Get the installation's base language */
2453 l_base_lang := Get_Base_Language;
2454
2455 /* Find the patch level */
2456 OPEN c_patch_level;
2457 FETCH c_patch_level INTO l_patch_level;
2458 CLOSE c_patch_level;
2459
2460
2461 -- B3883674 START
2462 -- Performance Improvement
2463 IF l_patch_level IS NULL THEN
2464 -- Customer is on J
2465 l_sql_stmt := 'INSERT INTO gmd_specifications_b'
2466 || '('
2467 || 'spec_id,'
2468 || 'spec_name,'
2469 || 'spec_vers,'
2470 || 'item_id,'
2471 || 'spec_status,'
2472 || 'owner_orgn_code,'
2473 || 'owner_id,'
2474 || 'delete_mark,'
2475 || 'creation_date,'
2476 || 'created_by,'
2477 || 'last_update_date,'
2478 || 'last_updated_by,'
2479 || 'last_update_login'
2480 || ')'
2481 || 'VALUES'
2482 || '('
2483 || ':l_spec_id,'
2484 || ':l_spec_name,'
2485 || ':l_spec_version,'
2486 || ':item_id,'
2487 || ':l_spec_status,'
2488 || ':l_owner_orgn_code,'
2489 || ':last_updated_by,'
2490 || ':delete_mark,'
2491 || ':creation_date,'
2492 || ':created_by,'
2493 || ':last_update_date,'
2494 || ':last_updated_by2,'
2495 || ':last_update_login'
2496 || ')'
2497 ;
2498
2499 ELSE
2500 -- Customer is on K or onward
2501
2502 l_sql_stmt := 'INSERT INTO gmd_specifications_b'
2503 || '('
2504 || 'spec_id,'
2505 || 'spec_name,'
2506 || 'spec_vers,'
2507 || 'item_id,'
2508 || 'spec_status,'
2509 || 'owner_orgn_code,'
2510 || 'owner_id,'
2511 || 'delete_mark,'
2512 || 'creation_date,'
2513 || 'created_by,'
2514 || 'last_update_date,'
2515 || 'last_updated_by,'
2516 || 'last_update_login,'
2517 || 'spec_type'
2518 || ')'
2519
2520 || 'VALUES'
2521
2522 || '('
2523 || ':l_spec_id,'
2524 || ':l_spec_name,'
2525 || ':l_spec_version,'
2526 || ':item_id,'
2527 || ':l_spec_status,'
2528 || ':l_owner_orgn_code,'
2529 || ':last_updated_by,'
2530 || ':delete_mark,'
2531 || ':creation_date,'
2532 || ':created_by,'
2533 || ':last_update_date,'
2534 || ':last_updated_by,'
2535 || ':last_update_login,'
2536 || ':spec_type'
2537 || ')'
2538 ;
2539
2540 END IF; /* Insert into SPEC_B */
2541 -- B3883674 END
2542
2543
2544 OPEN c_get_sysdate;
2545 FETCH c_get_sysdate into l_sysdate;
2546 CLOSE c_get_sysdate;
2547
2548
2549 /* While there are spec header groupings that have not been migrated */
2550
2551 OPEN c_get_spec_header;
2552 FETCH c_get_spec_header into hdr_rec;
2553
2554 /* While there are spec header groupings that have not been migrated */
2555 -- Header Loop
2556 WHILE c_get_spec_header%FOUND LOOP
2557
2558 -- Insert into temp table here all qc_spec_mst rows for hdr_id
2559 -- Cursors c_get_start_and_end, c_get_version_end_date_a, c_get_version_end_date_b, c_get_creation
2560 -- c_get_last_update, c_get_spec_details, (c_get_coa_inds as well ?????) would be based on temporary table
2561
2562 insert_temp_table_rows(hdr_rec.spec_hdr_id);
2563
2564
2565 /* Bug 3934121; Removed Savepoint */
2566 /* SAVEPOINT Specification_Group; */
2567
2568 l_spec_version := 1;
2569 l_start_date := NULL;
2570 l_end_date := NULL;
2571
2572 /* Select the item no associated to spec */
2573 OPEN c_get_item_no;
2574 FETCH c_get_item_no into item_rec;
2575 CLOSE c_get_item_no;
2576
2577 /* Build the spec name from the item no and spec_hdr_id */
2578 l_spec_name := item_rec.item_no || TO_CHAR(hdr_rec.spec_hdr_id);
2579
2580 /* Retest lot expiry ind is only applicable to lot controlled items */
2581 IF item_rec.lot_ctl = 0 THEN
2582 l_retest_lot_exp_ind := NULL;
2583 ELSE
2584 l_retest_lot_exp_ind := 'Y';
2585 END IF;
2586
2587 /* Check for duplicate tests selected for the spec */
2588 l_spec_hdr_id := hdr_rec.spec_hdr_id;
2589
2590 /* Bug 3934121; Removed Dup Tests code. */
2591 -- spec versioning logic
2592 /* Get the earliest start and latest end date as well as creation and last update date */
2593 OPEN c_get_start_and_end;
2594 FETCH c_get_start_and_end into l_start_date,l_end_date;
2595 CLOSE c_get_start_and_end;
2596
2597 /* Loop from the start date through the end date to figure out versions */
2598 /* Bug 3934121; Changed logic for deriving l_version_end_date */
2599
2600 WHILE l_start_date <= l_end_date LOOP
2601 /* Find the end date of the version (earliest end date in group) */
2602 OPEN c_get_version_end_date_a;
2603 FETCH c_get_version_end_date_a into l_version_end_date_a;
2604 CLOSE c_get_version_end_date_a;
2605
2606 OPEN c_get_version_end_date_b;
2607 FETCH c_get_version_end_date_b into l_version_end_date_b;
2608 CLOSE c_get_version_end_date_b;
2609
2610 IF l_version_end_date_a <
2611 nvl ( l_version_end_date_b ,
2612 l_end_date + 1000/186400) THEN
2613 l_version_end_date := l_version_end_date_a;
2614 ELSE
2615 l_version_end_date := l_version_end_date_b;
2616 END IF;
2617
2618 /* Bug 3241005; l_no_overlap_ind is set to 'N' when the l_version_end_date
2619 value is derived with the original cursor */
2620
2621
2622 /* Select the creation info for the spec version */
2623 OPEN c_get_creation;
2624 FETCH c_get_creation into create_rec;
2625 -- CLOSE c_get_creation;
2626
2627 -- Bug 3784121; If creation_date is not found then no tests exists for the
2628 -- spec l_start_date and l_version_end_date combination; and
2629 -- spec is created for these dates.
2630 IF c_get_creation%FOUND THEN
2631
2632 /* Select the update info for the spec version */
2633 OPEN c_get_last_update;
2634 FETCH c_get_last_update into update_rec;
2635 CLOSE c_get_last_update;
2636
2637 l_owner_orgn_code := TRIM(FND_PROFILE.value_specific('GEMMS_DEFAULT_ORGN',update_rec.last_updated_by));
2638
2639 IF l_version_end_date < l_todays_date THEN
2640
2641 /* M. Grosser 27-Sep-2002 BUG 2593962 - Changed value from 900 to 1000
2642 for Obsolete/Expired specification versions
2643 and their corresponding validity rules
2644 */
2645 l_spec_status := '1000'; /* Obsolete */
2646 ELSE
2647 l_spec_status := '700'; /* Approved for general use */
2648 END IF;
2649
2650 /* Get the new spec id */
2651 OPEN c_get_spec_id;
2652 FETCH c_get_spec_id into l_spec_id;
2653 CLOSE c_get_spec_id;
2654
2655 l_rec_count := l_rec_count +1;
2656
2657 /* Create the spec header record for version */
2658 IF l_patch_level IS NULL THEN
2659 -- Customer is on J
2660 EXECUTE IMMEDIATE l_sql_stmt USING
2661 l_spec_id,
2662 l_spec_name,
2663 l_spec_version,
2664 hdr_rec.item_id,
2665 l_spec_status,
2666 l_owner_orgn_code,
2667 update_rec.last_updated_by,
2668 0,
2669 create_rec.creation_date,
2670 create_rec.created_by,
2671 update_rec.last_update_date,
2672 update_rec.last_updated_by,
2673 update_rec.last_update_login;
2674
2675 ELSE
2676 -- Customer is on K or onward
2677 EXECUTE IMMEDIATE l_sql_stmt USING
2678 l_spec_id,
2679 l_spec_name,
2680 l_spec_version,
2681 hdr_rec.item_id,
2682 l_spec_status,
2683 l_owner_orgn_code,
2684 update_rec.last_updated_by,
2685 0,
2686 create_rec.creation_date,
2687 create_rec.created_by,
2688 update_rec.last_update_date,
2689 update_rec.last_updated_by,
2690 update_rec.last_update_login,
2691 'I';
2692 END IF; /* Insert into SPEC_B */
2693
2694 /* Create the translated description for version */
2695
2696
2697 INSERT INTO gmd_specifications_tl
2698 (
2699 spec_id,
2700 language,
2701 spec_desc,
2702 source_lang,
2703 creation_date,
2704 created_by,
2705 last_update_date,
2706 last_updated_by,
2707 last_update_login
2708 )
2709 VALUES
2710 (
2711 l_spec_id,
2712 l_base_lang,
2713 l_spec_name,
2714 l_base_lang,
2715 create_rec.creation_date,
2716 create_rec.created_by,
2717 update_rec.last_update_date,
2718 update_rec.last_updated_by,
2719 update_rec.last_update_login
2720 );
2721
2722 /* Test sequence counter - tests in alphabetical order as currently done */
2723 l_test_seq := 10;
2724
2725 /* Retrieve next validity rule id - use to create validity rule and use in mapping table */
2726 OPEN c_get_spec_vr_id;
2727 FETCH c_get_spec_vr_id into l_spec_vr_id;
2728 CLOSE c_get_spec_vr_id;
2729
2730 OPEN c_get_spec_details;
2731 FETCH c_get_spec_details into sd;
2732
2733 /* For each detail record retrieved */
2734 WHILE c_get_spec_details%FOUND LOOP
2735 /* Insert record into new spec detail table */
2736 -- Bug 3588513; Changed decode for PRINT_SPEC_IND and PRINT_RESULT_IND to include
2737 -- PRINT_COA_SHIPPED also.
2738 -- Bug 3691496; Changed decode for EXP_ERROR_TYP to eliminate the update
2739 -- statement.
2740 -- PK Bug 4898620 always insert new text and use inserted value of text_code
2741 -- instead of sd.text_code in this insert below
2742
2743 IF sd.text_code IS NOT NULL THEN
2744
2745 BEGIN
2746
2747 l_copied_text_code := GMA_EDITTEXT_PKG.Copy_Text(sd.text_code,'QC_TEXT_TBL_TL', 'QC_TEXT_TBL_TL');
2748 sd.text_code := l_copied_text_code;
2749
2750 EXCEPTION
2751 WHEN others THEN
2752 GMA_MIGRATION.gma_insert_message (
2753 p_run_id => p_migration_id,
2754 p_table_name => 'GMD_SPEC_TESTS_B',
2755 p_DB_ERROR => '',
2756 p_param1 => l_spec_id,
2757 p_param2 => sd.qcassy_typ_id,
2758 p_param3 => sd.text_code,
2759 p_param4 => '',
2760 p_param5 => '',
2761 p_message_token => 'TEXT_CODE_NOT_COPIED',
2762 p_message_type => 'P',
2763 p_line_no => '1',
2764 p_position => '',
2765 p_base_message => '');
2766
2767 sd.text_code := NULL;
2768
2769 END;
2770
2771 END IF;
2772
2773 -- END Bug 4898620
2774
2775 INSERT INTO gmd_spec_tests_b
2776 (
2777 spec_id,
2778 test_id,
2779 test_method_id,
2780 seq,
2781 test_qty,
2782 test_uom,
2783 target_value_char,
2784 target_value_num,
2785 min_value_num,
2786 max_value_num,
2787 min_value_char,
2788 max_value_char,
2789 test_replicate,
2790 below_spec_min,
2791 above_spec_max,
2792 above_spec_min,
2793 below_spec_max,
2794 exp_error_type,
2795 below_min_action_code,
2796 above_max_action_code,
2797 above_min_action_code,
2798 below_max_action_code,
2799 out_of_spec_action,
2800 use_to_control_step,
2801 check_result_interval,
2802 optional_ind,
2803 display_precision,
2804 report_precision,
2805 test_priority,
2806 retest_lot_expiry_ind,
2807 text_code,
2808 creation_date,
2809 created_by,
2810 last_update_date,
2811 last_updated_by,
2812 last_update_login,
2813 attribute1,
2814 attribute2,
2815 attribute3,
2816 attribute4,
2817 attribute5,
2818 attribute6,
2819 attribute7,
2820 attribute8,
2821 attribute9,
2822 attribute10,
2823 attribute11,
2824 attribute12,
2825 attribute13,
2826 attribute14,
2827 attribute15,
2828 attribute16,
2829 attribute17,
2830 attribute18,
2831 attribute19,
2832 attribute20,
2833 attribute21,
2834 attribute22,
2835 attribute23,
2836 attribute24,
2837 attribute25,
2838 attribute26,
2839 attribute27,
2840 attribute28,
2841 attribute29,
2842 attribute30,
2843 attribute_category,
2844 print_spec_ind,
2845 print_result_ind
2846 )
2847 VALUES
2848 (
2849 l_spec_id,
2850 sd.qcassy_typ_id,
2851 '0',
2852 l_test_seq,
2853 NULL,
2854 NULL,
2855 sd.text_spec,
2856 sd.target_spec,
2857 sd.min_spec,
2858 sd.max_spec,
2859 sd.min_char,
2860 sd.max_char,
2861 '1',
2862 sd.outside_spec_min,
2863 sd.outside_spec_max,
2864 sd.inside_spec_min,
2865 sd.inside_spec_max,
2866 decode(sd.error_val_type,NULL,
2867 decode(sd.outside_min_action_code,NULL,
2868 decode( sd.outside_max_action_code,NULL,
2869 decode(sd.inside_min_action_code,NULL,
2870 decode(sd.inside_max_action_code,NULL,NULL,sd.error_val_type),
2871 sd.error_val_type),sd.error_val_type),sd.error_val_type ),
2872 'PCT','P','NUM','N',NULL),
2873 -- DECODE(sd.error_val_type,'PCT','P','NUM','N',NULL),
2874 sd.outside_min_action_code,
2875 sd.outside_max_action_code,
2876 sd.inside_min_action_code,
2877 sd.inside_max_action_code,
2878 sd.outaction_code,
2879 'Y',
2880 sd.outaction_interval,
2881 '',
2882 decode(sd.text_spec,NULL,G_display_precision,NULL), --Bug 5025951
2883 decode(sd.text_spec,NULL,G_report_precision, NULL), --Bug 5025951
2884 '5N',
2885 l_retest_lot_exp_ind,
2886 sd.text_code,
2887 sd.creation_date,
2888 sd.created_by,
2889 sd.last_update_date,
2890 sd.last_updated_by,
2891 sd.last_update_login,
2892 sd.attribute1,
2893 sd.attribute2,
2894 sd.attribute3,
2895 sd.attribute4,
2896 sd.attribute5,
2897 sd.attribute6,
2898 sd.attribute7,
2899 sd.attribute8,
2900 sd.attribute9,
2901 sd.attribute10,
2902 sd.attribute11,
2903 sd.attribute12,
2904 sd.attribute13,
2905 sd.attribute14,
2906 sd.attribute15,
2907 sd.attribute16,
2908 sd.attribute17,
2909 sd.attribute18,
2910 sd.attribute19,
2911 sd.attribute20,
2912 sd.attribute21,
2913 sd.attribute22,
2914 sd.attribute23,
2915 sd.attribute24,
2916 sd.attribute25,
2917 sd.attribute26,
2918 sd.attribute27,
2919 sd.attribute28,
2920 sd.attribute29,
2921 sd.attribute30,
2922 sd.attribute_category,
2923 DECODE(sd.print_coa_shipped,0,
2924 DECODE(sd.print_coa_invoiced,0,NULL,1,'Y',NULL),1,'Y',NULL),
2925 DECODE(sd.print_coa_shipped,0,
2926 DECODE(sd.print_coa_invoiced,0,NULL,1,'Y',NULL),1,'Y',NULL)
2927 );
2928
2929 /* Insert dummy record into gmd_spec_tests_tl table
2930 to ensure that the view will work
2931 */
2932 INSERT INTO gmd_spec_tests_tl
2933 (
2934 spec_id,
2935 test_id,
2936 language,
2937 test_display,
2938 source_lang,
2939 creation_date,
2940 created_by,
2941 last_updated_by,
2942 last_update_date,
2943 last_update_login
2944 )
2945 VALUES
2946 (
2947 l_spec_id,
2948 sd.qcassy_typ_id,
2949 l_base_lang,
2950 '',
2951 l_base_lang,
2952 sd.creation_date,
2953 sd.created_by,
2954 sd.last_updated_by,
2955 sd.last_update_date,
2956 sd.last_update_login
2957 );
2958
2959 /* Insert record into mapping table */
2960 INSERT INTO gmd_spec_mapping
2961 (
2962 qc_spec_id,
2963 spec_id,
2964 test_id,
2965 qc_rec_type,
2966 spec_vr_id,
2967 start_date,
2968 end_date
2969 )
2970 VALUES
2971 (
2972 sd.qc_spec_id,
2973 l_spec_id,
2974 sd.qcassy_typ_id,
2975 sd.qc_rec_type,
2976 l_spec_vr_id,
2977 l_start_date,
2978 l_version_end_date
2979 );
2980
2981 INSERT INTO gmd_spec_mapping_gtmp
2982 (
2983 qc_spec_id,
2984 spec_id,
2985 test_id,
2986 qc_rec_type,
2987 spec_vr_id,
2988 start_date,
2989 end_date
2990 )
2991 VALUES
2992 (
2993 sd.qc_spec_id,
2994 l_spec_id,
2995 sd.qcassy_typ_id,
2996 sd.qc_rec_type,
2997 l_spec_vr_id,
2998 l_start_date,
2999 l_version_end_date
3000 );
3001
3002
3003
3004 l_test_seq := l_test_seq + 10;
3005
3006 FETCH c_get_spec_details into sd;
3007
3008 END LOOP; /* Inserting detail records for spec version */
3009
3010 CLOSE c_get_spec_details;
3011
3012 -- Bug 3097029; expression error type requires at least
3013 -- one action code specified; if none specified,
3014 -- set to NULL
3015 --
3016 /* UPDATE gmd_spec_tests_b
3017 set exp_error_type = NULL
3018 where below_min_action_code is null
3019 and above_max_action_code is null
3020 and above_min_action_code is null
3021 and below_max_action_code is null
3022 and exp_error_type is not null; */
3023
3024 /* M. Grosser 30-Sep-2002 BUG 2598751 - Modified code to check to see
3025 if any of the coa indicators are set for the
3026 specification vers.
3027 */
3028 /* Select coa indicators within a spec version */
3029 OPEN c_get_coa_inds;
3030 FETCH c_get_coa_inds into coa_rec;
3031 CLOSE c_get_coa_inds;
3032
3033 -- Begin Bug 4252591
3034
3035 IF trunc(l_version_end_date) + 1 >= l_max_date THEN
3036
3037 l_version_end_date := NULL;
3038
3039 END IF;
3040
3041 -- End Bug 4252591
3042
3043
3044 /* if this is a production spec */
3045 IF sd.qc_rec_type = 'P' THEN
3046 /* value in new table is W */
3047 sd.qc_rec_type := 'W';
3048
3049 /* B2714197 Fetch recipe id, no, and version based on batch id */
3050 IF sd.batch_id is not null THEN
3051 open c_recipe_id_no_vers (sd.batch_id);
3052 fetch c_recipe_id_no_vers into l_recipe_id, l_recipe_no, l_recipe_version;
3053 close c_recipe_id_no_vers ;
3054 ELSE
3055 l_recipe_id :=null;
3056 l_recipe_no :=null;
3057 l_recipe_version :=null;
3058 END IF;
3059
3060 /* Fetch routing_no and version based in routing_id */
3061 IF sd.routing_id is not null THEN
3062 open c_routing_no_vers (sd.routing_id);
3063 fetch c_routing_no_vers into l_routing_no, l_routing_vers;
3064 close c_routing_no_vers ;
3065 ELSE
3066 l_routing_no :=null;
3067 l_routing_vers:=null;
3068 END IF;
3069
3070 IF sd.formula_id is not null THEN
3071 open c_formula_num_vers (sd.formula_id);
3072 fetch c_formula_num_vers into l_formula_no, l_formula_vers;
3073 close c_formula_num_vers ;
3074 ELSE
3075 l_formula_no :=null;
3076 l_formula_vers :=null;
3077 ENd IF;
3078
3079 IF sd.oprn_id is not null THEN
3080 open c_oprn_num_vers (sd.oprn_id);
3081 fetch c_oprn_num_vers into l_oprn_no, l_oprn_vers;
3082 close c_oprn_num_vers ;
3083 ELSE
3084 l_oprn_no :=null;
3085 l_oprn_vers :=null;
3086 ENd IF;
3087
3088 /* Create a production spec validity rule */
3089 INSERT INTO gmd_wip_spec_vrs
3090 (
3091 spec_vr_id,
3092 spec_id,
3093 orgn_code,
3094 sampling_plan_id,
3095 batch_id,
3096 recipe_id,
3097 recipe_no,
3098 recipe_version,
3099 formula_id,
3100 formulaline_id,
3101 formula_no,
3102 formula_vers,
3103 routing_id,
3104 routing_no,
3105 routing_vers,
3106 oprn_id,
3107 oprn_no,
3108 oprn_vers,
3109 step_id,
3110 step_no,
3111 charge,
3112 spec_vr_status,
3113 lot_optional_on_sample,
3114 start_date,
3115 end_date,
3116 sample_inv_trans_ind,
3117 control_lot_attrib_ind,
3118 out_of_spec_lot_status,
3119 in_spec_lot_status,
3120 control_batch_step_ind,
3121 coa_type,
3122 coa_at_ship_ind,
3123 coa_at_invoice_ind,
3124 coa_req_from_supl_ind,
3125 text_code,
3126 delete_mark,
3127 creation_date,
3128 created_by,
3129 last_update_date,
3130 last_updated_by,
3131 last_update_login,
3132 attribute1,
3133 attribute2,
3134 attribute3,
3135 attribute4,
3136 attribute5,
3137 attribute6,
3138 attribute7,
3139 attribute8,
3140 attribute9,
3141 attribute10,
3142 attribute11,
3143 attribute12,
3144 attribute13,
3145 attribute14,
3146 attribute15,
3147 attribute16,
3148 attribute17,
3149 attribute18,
3150 attribute19,
3151 attribute20,
3152 attribute21,
3153 attribute22,
3154 attribute23,
3155 attribute24,
3156 attribute25,
3157 attribute26,
3158 attribute27,
3159 attribute28,
3160 attribute29,
3161 attribute30,
3162 attribute_category
3163 )
3164 VALUES
3165 (
3166 l_spec_vr_id,
3167 l_spec_id,
3168 sd.orgn_code,
3169 '',
3170 sd.batch_id,
3171 l_recipe_id,
3172 l_recipe_no,
3173 l_recipe_version,
3174 sd.formula_id,
3175 sd.formulaline_id,
3176 l_formula_no,
3177 l_formula_vers,
3178 sd.routing_id,
3179 l_routing_no,
3180 l_routing_vers,
3181 sd.oprn_id,
3182 l_oprn_no,
3183 l_oprn_vers,
3184 sd.routingstep_id,
3185 sd.routingstep_no,
3186 sd.charge,
3187 l_spec_status,
3188 '',
3189 l_start_date,
3190 l_version_end_date,
3191 '',
3192 '',
3193 '',
3194 '',
3195 '',
3196 '',
3197 DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
3198 DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
3199 DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
3200 '',
3201 '0',
3202 create_rec.creation_date,
3203 create_rec.created_by,
3204 update_rec.last_update_date,
3205 update_rec.last_updated_by,
3206 update_rec.last_update_login,
3207 '',
3208 '',
3209 '',
3210 '',
3211 '',
3212 '',
3213 '',
3214 '',
3215 '',
3216 '',
3217 '',
3218 '',
3219 '',
3220 '',
3221 '',
3222 '',
3223 '',
3224 '',
3225 '',
3226 '',
3227 '',
3228 '',
3229 '',
3230 '',
3231 '',
3232 '',
3233 '',
3234 '',
3235 '',
3236 '',
3237 ''
3238 );
3239
3240 /* If this is an item spec */
3241 ELSIF sd.qc_rec_type = 'I' THEN
3242
3243
3244 IF sd.lot_id is not null THEN
3245 open c_lot_nums (sd.lot_id);
3246 fetch c_lot_nums into l_lot_no, l_sublot_no;
3247 close c_lot_nums;
3248 ELSE
3249 l_lot_no := null;
3250 l_sublot_no := null;
3251 ENd IF;
3252
3253 /* Create an item spec validity rule */
3254 INSERT INTO gmd_inventory_spec_vrs
3255 (
3256 spec_vr_id,
3257 spec_id,
3258 orgn_code,
3259 sampling_plan_id,
3260 lot_id,
3261 lot_no,
3262 sublot_no,
3263 whse_code,
3264 location,
3265 spec_vr_status,
3266 lot_optional_on_sample,
3267 start_date,
3268 end_date,
3269 sample_inv_trans_ind,
3270 control_lot_attrib_ind,
3271 out_of_spec_lot_status,
3272 in_spec_lot_status,
3273 coa_type,
3274 coa_at_ship_ind,
3275 coa_at_invoice_ind,
3276 coa_req_from_supl_ind,
3277 text_code,
3278 delete_mark,
3279 creation_date,
3280 created_by,
3281 last_update_date,
3282 last_updated_by,
3283 last_update_login,
3284 attribute1,
3285 attribute2,
3286 attribute3,
3287 attribute4,
3288 attribute5,
3289 attribute6,
3290 attribute7,
3291 attribute8,
3292 attribute9,
3293 attribute10,
3294 attribute11,
3295 attribute12,
3296 attribute13,
3297 attribute14,
3298 attribute15,
3299 attribute16,
3300 attribute17,
3301 attribute18,
3302 attribute19,
3303 attribute20,
3304 attribute21,
3305 attribute22,
3306 attribute23,
3307 attribute24,
3308 attribute25,
3309 attribute26,
3310 attribute27,
3311 attribute28,
3312 attribute29,
3313 attribute30,
3314 attribute_category
3315 )
3316 VALUES
3317 (
3318 l_spec_vr_id,
3319 l_spec_id,
3320 sd.orgn_code,
3321 '',
3322 sd.lot_id,
3323 l_lot_no,
3324 l_sublot_no,
3325 sd.whse_code,
3326 sd.location,
3327 l_spec_status,
3328 '',
3329 l_start_date,
3330 l_version_end_date,
3331 '',
3332 '',
3333 '',
3334 '',
3335 '',
3336 DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
3337 DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
3338 DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
3339 '',
3340 '0',
3341 create_rec.creation_date,
3342 create_rec.created_by,
3343 update_rec.last_update_date,
3344 update_rec.last_updated_by,
3345 update_rec.last_update_login,
3346 '',
3347 '',
3348 '',
3349 '',
3350 '',
3351 '',
3352 '',
3353 '',
3354 '',
3355 '',
3356 '',
3357 '',
3358 '',
3359 '',
3360 '',
3361 '',
3362 '',
3363 '',
3364 '',
3365 '',
3366 '',
3367 '',
3368 '',
3369 '',
3370 '',
3371 '',
3372 '',
3373 '',
3374 '',
3375 '',
3376 ''
3377 );
3378
3379 ELSIF sd.qc_rec_type = 'C' THEN
3380 /* Create an customer spec validity rule */
3381 INSERT INTO gmd_customer_spec_vrs
3382 (
3383 spec_vr_id,
3384 spec_id,
3385 orgn_code,
3386 sampling_plan_id,
3387 cust_id,
3388 order_id,
3389 order_line_id,
3390 order_line,
3391 ship_to_site_id,
3392 org_id,
3393 lot_optional_on_sample,
3394 spec_vr_status,
3395 start_date,
3396 end_date,
3397 sample_inv_trans_ind,
3398 coa_type,
3399 coa_at_ship_ind,
3400 coa_at_invoice_ind,
3401 coa_req_from_supl_ind,
3402 text_code,
3403 delete_mark,
3404 creation_date,
3405 created_by,
3406 last_update_date,
3407 last_updated_by,
3408 last_update_login,
3409 attribute1,
3410 attribute2,
3411 attribute3,
3412 attribute4,
3413 attribute5,
3414 attribute6,
3415 attribute7,
3416 attribute8,
3417 attribute9,
3418 attribute10,
3419 attribute11,
3420 attribute12,
3421 attribute13,
3422 attribute14,
3423 attribute15,
3424 attribute16,
3425 attribute17,
3426 attribute18,
3427 attribute19,
3428 attribute20,
3429 attribute21,
3430 attribute22,
3431 attribute23,
3432 attribute24,
3433 attribute25,
3434 attribute26,
3435 attribute27,
3436 attribute28,
3437 attribute29,
3438 attribute30,
3439 attribute_category
3440 )
3441 VALUES
3442 (
3443 l_spec_vr_id,
3444 l_spec_id,
3445 sd.orgn_code,
3446 '',
3447 sd.cust_id,
3448 sd.order_header_id,
3449 '',
3450 sd.order_line_no,
3451 sd.ship_to_site_id,
3452 sd.order_org_id,
3453 '',
3454 l_spec_status,
3455 l_start_date,
3456 l_version_end_date,
3457 '',
3458 '',
3459 DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
3460 DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
3461 DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
3462 '',
3463 '0',
3464 create_rec.creation_date,
3465 create_rec.created_by,
3466 update_rec.last_update_date,
3467 update_rec.last_updated_by,
3468 update_rec.last_update_login,
3469 '',
3470 '',
3471 '',
3472 '',
3473 '',
3474 '',
3475 '',
3476 '',
3477 '',
3478 '',
3479 '',
3480 '',
3481 '',
3482 '',
3483 '',
3484 '',
3485 '',
3486 '',
3487 '',
3488 '',
3489 '',
3490 '',
3491 '',
3492 '',
3493 '',
3494 '',
3495 '',
3496 '',
3497 '',
3498 '',
3499 ''
3500 );
3501
3502 /* If this is a supplier (vendor) spec */
3503 ELSIF sd.qc_rec_type = 'S' THEN
3504 /* Get the purchasing supplier ids */
3505 IF sd.vendor_id IS NOT NULL THEN
3506 OPEN g_get_supplier_ids(sd.vendor_id);
3507 FETCH g_get_supplier_ids into l_supplier_id, l_supplier_site_id;
3508 CLOSE g_get_supplier_ids;
3509 ELSE
3510 l_supplier_id := NULL;
3511 l_supplier_site_id := NULL;
3512 END IF;
3513
3514 /* Create an supplier spec validity rule */
3515 INSERT INTO gmd_supplier_spec_vrs
3516 (
3517 spec_vr_id,
3518 spec_id,
3519 orgn_code,
3520 sampling_plan_id,
3521 supplier_id,
3522 supplier_site_id,
3523 po_header_id,
3524 po_line_id,
3525 lot_optional_on_sample,
3526 spec_vr_status,
3527 start_date,
3528 end_date,
3529 sample_inv_trans_ind,
3530 coa_type,
3531 coa_at_ship_ind,
3532 coa_at_invoice_ind,
3533 coa_req_from_supl_ind,
3534 text_code,
3535 delete_mark,
3536 creation_date,
3537 created_by,
3538 last_update_date,
3539 last_updated_by,
3540 last_update_login,
3541 attribute1,
3542 attribute2,
3543 attribute3,
3544 attribute4,
3545 attribute5,
3546 attribute6,
3547 attribute7,
3548 attribute8,
3549 attribute9,
3550 attribute10,
3551 attribute11,
3552 attribute12,
3553 attribute13,
3554 attribute14,
3555 attribute15,
3556 attribute16,
3557 attribute17,
3558 attribute18,
3559 attribute19,
3560 attribute20,
3561 attribute21,
3562 attribute22,
3563 attribute23,
3564 attribute24,
3565 attribute25,
3566 attribute26,
3567 attribute27,
3568 attribute28,
3569 attribute29,
3570 attribute30,
3571 attribute_category
3572 )
3573 VALUES
3574 (
3575 l_spec_vr_id,
3576 l_spec_id,
3577 sd.orgn_code,
3578 '',
3579 l_supplier_id,
3580 l_supplier_site_id,
3581 '',
3582 '',
3583 '',
3584 l_spec_status,
3585 l_start_date,
3586 l_version_end_date,
3587 '',
3588 '',
3589 DECODE(coa_rec.print_coa_shipped,0,NULL,1,'Y',NULL),
3590 DECODE(coa_rec.print_coa_invoiced,0,NULL,1,'Y',NULL),
3591 DECODE(coa_rec.vendor_coa_required,0,NULL,1,'Y',NULL),
3592 '',
3593 '0',
3594 create_rec.creation_date,
3595 create_rec.created_by,
3596 update_rec.last_update_date,
3597 update_rec.last_updated_by,
3598 update_rec.last_update_login,
3599 '',
3600 '',
3601 '',
3602 '',
3603 '',
3604 '',
3605 '',
3606 '',
3607 '',
3608 '',
3609 '',
3610 '',
3611 '',
3612 '',
3613 '',
3614 '',
3615 '',
3616 '',
3617 '',
3618 '',
3619 '',
3620 '',
3621 '',
3622 '',
3623 '',
3624 '',
3625 '',
3626 '',
3627 '',
3628 '',
3629 ''
3630 );
3631
3632 END IF; /* Type of specification */
3633
3634 l_spec_version := l_spec_version + 1;
3635
3636 END IF; -- c_get_creation%FOUND
3637 CLOSE c_get_creation;
3638 l_start_date := l_version_end_date + (1/86400);
3639
3640 END LOOP; /* Where start_date < end_date */
3641
3642
3643 /* Set status of records to migrated */
3644 UPDATE qc_spec_mst
3645 SET migration_status = 'MO'
3646 WHERE spec_hdr_id = hdr_rec.spec_hdr_id
3647 and migration_status is NULL;
3648
3649 COMMIT;
3650 -- This commit should delete temporary table rows.
3651
3652
3653 FETCH c_get_spec_header into hdr_rec;
3654
3655 END LOOP; /* Spec_hdr_id loop */
3656
3657 CLOSE c_get_spec_header;
3658
3659
3660
3661 GMA_MIGRATION.gma_insert_message (
3662 p_run_id => p_migration_id,
3663 p_table_name => 'QC_SPEC_MST',
3664 p_DB_ERROR => '',
3665 p_param1 => '',
3666 p_param2 => '',
3667 p_param3 => '',
3668 p_param4 => '',
3669 p_param5 => '',
3670 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS',
3671 p_message_type => 'P',
3672 p_line_no => '1',
3673 p_position => '',
3674 p_base_message => '');
3675 COMMIT;
3676
3677
3678
3679 EXCEPTION
3680 WHEN OTHERS THEN
3681 x_return_status := 'U';
3682 /* ROLLBACK TO SAVEPOINT Specification_Group; */
3683 GMA_MIGRATION.gma_insert_message (
3684 p_run_id => p_migration_id,
3685 p_table_name => 'QC_SPEC_MST',
3686 p_DB_ERROR => sqlerrm,
3687 p_param1 => sd.qc_spec_id,
3688 p_param2 => l_spec_id,
3689 p_param3 => sd.qcassy_typ_id,
3690 p_param4 => '',
3691 p_param5 => '',
3692 p_message_token => 'GMA_MIGRATION_DB_ERROR',
3693 p_message_type => 'E',
3694 p_line_no => '1',
3695 p_position => '',
3696 p_base_message => 'Failed to migrate specifications due to '||sqlerrm);
3697
3698 GMA_MIGRATION.gma_insert_message (
3699 p_run_id => p_migration_id,
3700 p_table_name => 'QC_SPEC_MST',
3701 p_DB_ERROR => '',
3702 p_param1 => '',
3703 p_param2 => '',
3704 p_param3 => '',
3705 p_param4 => '',
3706 p_param5 => '',
3707 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
3708 p_message_type => 'P',
3709 p_line_no => '1',
3710 p_position => '',
3711 p_base_message => '');
3712
3713
3714 END Migrate_Specifications;
3715
3716
3717
3718 /*===========================================================================
3719 -- PROCEDURE:
3720 -- Migrate_Samples
3721 --
3722 -- DESCRIPTION:
3723 -- This PL/SQL procedure is used to migrate samples to the new data
3724 -- model for OPM patch 11.5.1J.
3725 --
3726 -- PARAMETERS:
3727 -- p_migration_id - id to use to right to migration log
3728 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
3729 --
3730 -- SYNOPSIS:
3731 -- Migrate_Samples;
3732 --
3733 -- HISTORY
3734 -- M. Grosser 27-Sep-2002 BUG 2596865 - Modified code to look for a
3735 -- value of 'ACCEPT' instead of 'ACCEPTED'
3736 -- M. Grosser 29-Sep-2002 BUG 2596689 - Modified code to check to see
3737 -- if a sample no is used more than once within an
3738 -- organization. If so, add the record type to the
3739 -- sample name
3740 -- M. Grosser 08-Oct-2002 Set sampling_plan_id to NULL in gmd_sampling_events
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
3750 -- c_get_mapping. Then code added to derive the
3751 -- spec for samples with dates outside the
3752 -- effective dates of the spec.
3753 -- B.Stone 4-Jan-2004 Added code so the same version of the code will
3754 -- work for J or K.
3755 --=========================================================================== */
3756 PROCEDURE Migrate_Samples (p_migration_id IN NUMBER,
3757 x_return_status OUT NOCOPY VARCHAR2)
3758 IS
3759
3760 /* ------------- LOCAL VARIABLES ------------------- */
3761 l_spec_vr_id NUMBER;
3762 l_sm_spec_vr_id NUMBER;
3763 l_spec_id NUMBER;
3764 l_sampling_event_id NUMBER;
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);
3774 l_return_status VARCHAR2(4);
3775 l_wip_spec_rec GMD_SPEC_MATCH_MIG_GRP.wip_spec_rec_type;
3776 l_customer_spec_rec GMD_SPEC_MATCH_MIG_GRP.customer_spec_rec_type;
3777 l_inventory_spec_rec GMD_SPEC_MATCH_MIG_GRP.inventory_spec_rec_type;
3778 l_supplier_spec_rec GMD_SPEC_MATCH_MIG_GRP.supplier_spec_rec_type;
3779
3780 l_lot_no ic_lots_mst.lot_no%TYPE;
3781 l_sublot_no ic_lots_mst.sublot_no%TYPE;
3782 l_recipe_id NUMBER;
3783 l_r_qc_spec_id NUMBER;
3784 l_r_tests_cnt NUMBER;
3785 l_sm_spec_id NUMBER;
3786 l_sm_tests_cnt NUMBER;
3787 l_same_tests_cnt NUMBER;
3788 l_patch_level VARCHAR2(2); -- Valid values are: NULL and "K+"
3789 l_sql_stmt1 VARCHAR2(4000); --Changed it for bug no. 3486120
3790 l_sql_stmt2 VARCHAR2(4000); --Changed it for bug no. 3486120
3791 l_y VARCHAR2(1);
3792 l_1 NUMBER;
3793 l_prty VARCHAR2(2);
3794 l_i VARCHAR2(1);
3795 l_smp_w_spec_ok_cnt NUMBER;
3796 l_smp_w_spec_nok_cnt NUMBER;
3797 l_smp_no_spec_cnt NUMBER;
3798 l_smp_no_spec_fnd_cnt NUMBER;
3799
3800 -- B3883674
3801 l_samples_level VARCHAR2(2); -- Valid values are: NULL and "K+"
3802 l_sampling_events_level VARCHAR2(2); -- Valid values are: NULL and "K+"
3803 l_gmd VARCHAR2(3);
3804
3805
3806
3807 /* ------------------ CURSORS ---------------------- */
3808 /* Get lot_no and sublot_no */
3809 CURSOR c_lot_sublot (v_lot_id NUMBER) IS
3810 SELECT lot_no, sublot_no
3811 FROM ic_lots_mst
3812 WHERE lot_id = v_lot_id;
3813
3814 /* Get recipe_id based on batch_id */
3815 CURSOR c_recipe_id (v_batch_id number) IS
3816 SELECT feff.recipe_id
3817 FROM gmd_recipe_validity_rules feff,
3818 gme_batch_header bh
3819 WHERE bh.batch_id = v_batch_id
3820 AND bh.recipe_validity_rule_id = feff.recipe_validity_rule_id;
3821
3822 /* Get the next spec validity rule id */
3823 CURSOR c_get_sampling_event_id IS
3824 SELECT gmd_qc_sampling_event_id_s.nextval
3825 FROM SYS.DUAL;
3826
3827 /* Get the next event spec diposition id */
3828 CURSOR c_get_event_spec_disp_id IS
3829 SELECT gmd_qc_event_spec_disp_id_s.nextval
3830 FROM SYS.DUAL;
3831
3832 /* Select sample data that has not been migrated */
3833 CURSOR c_get_samples IS
3834 SELECT sample_id,
3835 orgn_code,
3836 sample_no,
3837 sample_desc,
3838 batch_id,
3839 formula_id,
3840 formulaline_id,
3841 routing_id,
3842 routingstep_id,
3843 oprn_id,
3844 item_id,
3845 lot_id,
3846 whse_code,
3847 location,
3848 cust_id,
3849 vendor_id,
3850 sample_date,
3851 sampled_by,
3852 sample_qty,
3853 sample_um,
3854 external_id,
3855 sample_status,
3856 sample_final_approver,
3857 sample_test_approver,
3858 storage_whse,
3859 storage_location,
3860 sample_source,
3861 charge,
3862 order_header_id,
3863 order_line_id,
3864 order_line,
3865 order_org_id,
3866 qc_rec_type,
3867 ship_to_site_id,
3868 delete_mark,
3869 text_code,
3870 creation_date,
3871 created_by,
3872 last_update_date,
3873 last_updated_by,
3874 last_update_login,
3875 attribute1,
3876 attribute2,
3877 attribute3,
3878 attribute4,
3879 attribute5,
3880 attribute6,
3881 attribute7,
3882 attribute8,
3883 attribute9,
3884 attribute10,
3885 attribute11,
3886 attribute12,
3887 attribute13,
3888 attribute14,
3889 attribute15,
3890 attribute16,
3891 attribute17,
3892 attribute18,
3893 attribute19,
3894 attribute20,
3895 attribute21,
3896 attribute22,
3897 attribute23,
3898 attribute24,
3899 attribute25,
3900 attribute26,
3901 attribute27,
3902 attribute28,
3903 attribute29,
3904 attribute30,
3905 attribute_category
3906 FROM qc_smpl_mst
3907 WHERE migration_status is NULL ;
3908 smpl_rec c_get_samples%ROWTYPE;
3909
3910 /* Get the spec validity rule from the mapping table */
3911 /* Bug 3376111; Removed qc_rec_type from where clause */
3912 CURSOR c_get_mapping IS
3913 SELECT spec_vr_id, spec_id
3914 FROM gmd_spec_mapping map, qc_rslt_mst rslt
3915 WHERE smpl_rec.sample_date >= map.start_date
3916 and smpl_rec.sample_date <= map.end_date
3917 -- and map.qc_spec_id = NVL(rslt.qc_spec_id,0) --Bug 3486120
3918 and map.qc_spec_id = rslt.qc_spec_id
3919 and rslt.sample_id = smpl_rec.sample_id;
3920
3921 /* Bug 3376111; Get the count of tests in qc_rslt_mst for the sample */
3922 CURSOR c_get_r_tests_cnt IS
3923 SELECT 1
3924 FROM qc_rslt_mst r
3925 WHERE r.sample_id = smpl_rec.sample_id
3926 AND r.qc_spec_id is not null ;
3927
3928 CURSOR c_chk_smpl_has_rslt IS
3929 SELECT 1
3930 FROM qc_rslt_mst r
3931 WHERE r.sample_id = smpl_rec.sample_id;
3932
3933 /* Bug 3376111; Derive the spec from gmd_spec_mapping with the most
3934 tests found in qc_rslt_mst for the sample */
3935 CURSOR c_get_sm_specs IS
3936 SELECT spec_id, spec_vr_id, count(*) cnt
3937 FROM gmd_spec_mapping sm,
3938 qc_rslt_mst r
3939 WHERE r.sample_id = smpl_rec.sample_id
3940 and sm.qc_spec_id = r.qc_spec_id
3941 -- B3486120 Removed condition below
3942 -- and l_r_tests_cnt = ( select count(*)
3943 -- from qc_rslt_mst r
3944 -- where r.sample_id = smpl_rec.sample_id
3945 -- and r.qc_spec_id is not null )
3946 group by spec_id, spec_vr_id
3947 order by cnt desc;
3948
3949 /* Check to see if the same sample number exists within the same
3950 organization but with a different record type since that used
3951 to be allowed */
3952 CURSOR c_check_dup IS
3953 SELECT count(*)
3954 FROM qc_smpl_mst
3955 WHERE orgn_code = smpl_rec.orgn_code
3956 and sample_no = smpl_rec.sample_no;
3957
3958 -- Bug 3859406; replaced table all_tab_columns with fnd_columns;
3959 -- per apps standards
3960
3961 -- B3883674 Added following cursor
3962 -- Refer to GMD_SAMPLING_EVENTS for sample_type column
3963 CURSOR c_patch_level1 IS
3964 SELECT 'K+'
3965 from all_tab_columns
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'
3975 and column_name='SAMPLE_TYPE'
3976 and owner = l_gmd;
3977
3978 -- PK Bug 4898620
3979 Cursor fnd_user(l_user Varchar2) IS
3980 Select User_id from fnd_user where user_name = l_user;
3981
3982 l_sample_approver varchar2(30);
3983 l_inv_approver varchar2(30);
3984
3985
3986
3987 BEGIN
3988
3989 GMA_MIGRATION.gma_insert_message (
3990 p_run_id => p_migration_id,
3991 p_table_name => 'QC_SMPL_MST',
3992 p_DB_ERROR => '',
3993 p_param1 => '',
3994 p_param2 => '',
3995 p_param3 => '',
3996 p_param4 => '',
3997 p_param5 => '',
3998 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
3999 p_message_type => 'P',
4000 p_line_no => '1',
4001 p_position => '',
4002 p_base_message => '');
4003 COMMIT;
4004
4005 -- gmd_p_fs_context sets the formula security context
4006 --
4007
4008 gmd_p_fs_context.set_additional_attr;
4009
4010 l_prty := '5N';
4011 l_i := 'I';
4012 l_gmd := 'GMD';
4013
4014 /* Find the patch level */
4015 -- B3883674 Fetch sample_type for GMD_SAMPLING_EVENTS
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
4025
4026 -- Bug 3486120, moved sql statement build out of the loop
4027
4028 -- B3883674 Use appropriate variable
4029 IF l_sampling_events_level IS NULL THEN
4030 -- IF l_patch_level IS NULL THEN
4031
4032 -- Customer is on J
4033 l_sql_stmt1 := 'INSERT INTO gmd_sampling_events '
4034 || '('
4035 || 'item_id,'
4036 || 'sampling_event_id,'
4037 || 'original_spec_vr_id,'
4038 || 'complete_ind,'
4039 || 'disposition,'
4040 || 'source,'
4041 || 'sample_req_cnt,'
4042 || 'sample_taken_cnt,'
4043 || 'batch_id,'
4044 || 'recipe_id,'
4045 || 'formula_id,'
4046 || 'formulaline_id,'
4047 || 'routing_id,'
4048 || 'step_id,'
4049 || 'oprn_id,'
4050 || 'lot_id,'
4051 || 'lot_no,'
4052 || 'sublot_no,'
4053 || 'whse_code,'
4054 || 'location,'
4055 || 'cust_id,'
4056 || 'supplier_id,'
4057 || 'charge,'
4058 || 'order_id,'
4059 || 'order_line_id,'
4060 || 'org_id,'
4061 || 'ship_to_site_id,'
4062 || 'creation_date,'
4063 || 'created_by,'
4064 || 'last_update_date,'
4065 || 'last_updated_by,'
4066 || 'last_update_login '
4067 || ')'
4068 || ' VALUES '
4069 || '( '
4070 || ':item_id,'
4071 || ':sampling_event_id,'
4072 || ':spec_vr_id,'
4073 || ':l_y,'
4074 || ':disposition,'
4075 || ':qc_rec_type,'
4076 || ':l_1,'
4077 || ':l_2,'
4078 || ':batch_id,'
4079 || ':recipe_id,'
4080 || ':formula_id,'
4081 || ':formulaline_id,'
4082 || ':routing_id,'
4083 || ':routingstep_id,'
4084 || ':oprn_id,'
4085 || ':lot_id,'
4086 || ':lot_no,'
4087 || ':sublot_no,'
4088 || ':whse_code,'
4089 || ':location,'
4090 || ':cust_id,'
4091 || ':supplier_id,'
4092 || ':charge,'
4093 || ':order_header_id,'
4094 || ':order_line_id,'
4095 || ':order_org_id,'
4096 || ':ship_to_site_id,'
4097 || ':creation_date,'
4098 || ':created_by,'
4099 || ':last_update_date,'
4100 || ':last_updated_by,'
4101 || ':last_update_login '
4102 || ' )';
4103
4104 ELSE
4105 -- Customer is on K
4106 l_sql_stmt1 := 'INSERT INTO gmd_sampling_events'
4107 || '('
4108 || 'item_id,'
4109 || 'sampling_event_id,'
4110 || 'original_spec_vr_id,'
4111 || 'complete_ind,'
4112 || 'disposition,'
4113 || 'source,'
4114 || 'sample_req_cnt,'
4115 || 'sample_taken_cnt,'
4116 || 'batch_id,'
4117 || 'recipe_id,'
4118 || 'formula_id,'
4119 || 'formulaline_id,'
4120 || 'routing_id,'
4121 || 'step_id,'
4122 || 'oprn_id,'
4123 || 'lot_id,'
4124 || 'lot_no,'
4125 || 'sublot_no,'
4126 || 'whse_code,'
4127 || 'location,'
4128 || 'cust_id,'
4129 || 'supplier_id,'
4130 || 'charge,'
4131 || 'order_id,'
4132 || 'order_line_id,'
4133 || 'org_id,'
4134 || 'ship_to_site_id,'
4135 || 'creation_date,'
4136 || 'created_by,'
4137 || 'last_update_date,'
4138 || 'last_updated_by,'
4139 || 'last_update_login,'
4140 || 'sample_type '
4141 || ')'
4142 || ' VALUES '
4143 || '( '
4144 || ':item_id,'
4145 || ':sampling_event_id,'
4146 || ':spec_vr_id,'
4147 || ':l_y,'
4148 || ':disposition,'
4149 || ':qc_rec_type,'
4150 || ':l_1,'
4151 || ':l_2,'
4152 || ':batch_id,'
4153 || ':recipe_id,'
4154 || ':formula_id,'
4155 || ':formulaline_id,'
4156 || ':routing_id,'
4157 || ':routingstep_id,'
4158 || ':oprn_id,'
4159 || ':lot_id,'
4160 || ':lot_no,'
4161 || ':sublot_no,'
4162 || ':whse_code,'
4163 || ':location,'
4164 || ':cust_id,'
4165 || ':supplier_id,'
4166 || ':charge,'
4167 || ':order_header_id,'
4168 || ':order_line_id,'
4169 || ':order_org_id,'
4170 || ':ship_to_site_id,'
4171 || ':creation_date,'
4172 || ':created_by,'
4173 || ':last_update_date,'
4174 || ':last_updated_by,'
4175 || ':last_update_login,'
4176 || ':l_i'
4177 || ' ) ';
4178
4179
4180 END IF;
4181
4182 -- B3883674 Use appropriate variable
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,'
4192 ||'sample_desc,'
4193 ||'sample_disposition,'
4194 ||'sampling_event_id,'
4195 ||'source,'
4196 ||'batch_id,'
4197 ||'recipe_id,'
4198 ||'formula_id,'
4199 ||'formulaline_id,'
4200 ||'routing_id,'
4201 ||'step_id,'
4202 ||'oprn_id,'
4203 ||'item_id,'
4204 ||'lot_id,'
4205 ||'lot_no,'
4206 ||'sublot_no,'
4207 ||'whse_code,'
4208 ||'location,'
4209 ||'cust_id,'
4210 ||'supplier_id,'
4211 ||'date_drawn,'
4212 ||'sampler_id,'
4213 ||'sample_qty,'
4214 ||'sample_uom,'
4215 ||'external_id,'
4216 ||'inv_approver_id,'
4217 ||'sample_approver_id,'
4218 ||'storage_whse,'
4219 ||'storage_location,'
4220 ||'source_comment,'
4221 ||'charge,'
4222 ||'order_id,'
4223 ||'order_line_id,'
4224 ||'org_id,'
4225 ||'ship_to_site_id,'
4226 ||'priority,'
4227 ||'delete_mark,'
4228 ||'creation_date,'
4229 ||'created_by,'
4230 ||'last_update_date,'
4231 ||'last_updated_by,'
4232 ||'last_update_login,'
4233 ||'attribute1,'
4234 ||'attribute2,'
4235 ||'attribute3,'
4236 ||'attribute4,'
4237 ||'attribute5,'
4238 ||'attribute6,'
4239 ||'attribute7,'
4240 ||'attribute8,'
4241 ||'attribute9,'
4242 ||'attribute10,'
4243 ||'attribute11,'
4244 ||'attribute12,'
4245 ||'attribute13,'
4246 ||'attribute14,'
4247 ||'attribute15,'
4248 ||'attribute16,'
4249 ||'attribute17,'
4250 ||'attribute18,'
4251 ||'attribute19,'
4252 ||'attribute20,'
4253 ||'attribute21,'
4254 ||'attribute22,'
4255 ||'attribute23,'
4256 ||'attribute24,'
4257 ||'attribute25,'
4258 ||'attribute26,'
4259 ||'attribute27,'
4260 ||'attribute28,'
4261 ||'attribute29,'
4262 ||'attribute30,'
4263 ||'attribute_category'
4264 ||')'
4265 ||' VALUES '
4266 ||'( '
4267 ||':sample_id,'
4268 ||':orgn_code,'
4269 ||':l_sample_no,'
4270 ||':sample_desc,'
4271 ||':l_disposition,'
4272 ||':l_sampling_event_id,'
4273 ||':qc_rec_type,'
4274 ||':batch_id,'
4275 ||':l_recipe_id,'
4276 ||':formula_id,'
4277 ||':formulaline_id,'
4278 ||':routing_id,'
4279 ||':routingstep_id,'
4280 ||':oprn_id,'
4281 ||':item_id,'
4282 ||':lot_id,'
4283 ||':l_lot_no,'
4284 ||':l_sublot_no,'
4285 ||':whse_code,'
4286 ||':location,'
4287 ||':cust_id,'
4288 ||':l_supplier_id,'
4289 ||':sample_date,'
4290 ||':sampled_by,'
4291 ||':sample_qty,'
4292 ||':sample_um,'
4293 ||':external_id,'
4294 ||':sample_final_approver,'
4295 ||':sample_test_approver,'
4296 ||':storage_whse,'
4297 ||':storage_location,'
4298 ||':sample_source,'
4299 ||':charge,'
4300 ||':order_header_id,'
4301 ||':order_line_id,'
4302 ||':order_org_id,'
4303 ||':ship_to_site_id,'
4304 ||':l_prty,'
4305 ||':delete_mark,'
4306 ||':creation_date,'
4307 ||':created_by,'
4308 ||':last_update_date,'
4309 ||':last_updated_by,'
4310 ||':last_update_login,'
4311 ||':attribute1,'
4312 ||':attribute2,'
4313 ||':attribute3,'
4314 ||':attribute4,'
4315 ||':attribute5,'
4316 ||':attribute6,'
4317 ||':attribute7,'
4318 ||':attribute8,'
4319 ||':attribute9,'
4320 ||':attribute10,'
4321 ||':attribute11,'
4322 ||':attribute12,'
4323 ||':attribute13,'
4324 ||':attribute14,'
4325 ||':attribute15,'
4326 ||':attribute16,'
4327 ||':attribute17,'
4328 ||':attribute18,'
4329 ||':attribute19,'
4330 ||':attribute20,'
4331 ||':attribute21,'
4332 ||':attribute22,'
4333 ||':attribute23,'
4334 ||':attribute24,'
4335 ||':attribute25,'
4336 ||':attribute26,'
4337 ||':attribute27,'
4338 ||':attribute28,'
4339 ||':attribute29,'
4340 ||':attribute30,'
4341 ||':attribute_category'
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,'
4351 ||'sample_desc,'
4352 ||'sample_disposition,'
4353 ||'sampling_event_id,'
4354 ||'source,'
4355 ||'batch_id,'
4356 ||'recipe_id,'
4357 ||'formula_id,'
4358 ||'formulaline_id,'
4359 ||'routing_id,'
4360 ||'step_id,'
4361 ||'oprn_id,'
4362 ||'item_id,'
4363 ||'lot_id,'
4364 ||'lot_no,'
4365 ||'sublot_no,'
4366 ||'whse_code,'
4367 ||'location,'
4368 ||'cust_id,'
4369 ||'supplier_id,'
4370 ||'date_drawn,'
4371 ||'sampler_id,'
4372 ||'sample_qty,'
4373 ||'sample_uom,'
4374 ||'external_id,'
4375 ||'inv_approver_id,'
4376 ||'sample_approver_id,'
4377 ||'storage_whse,'
4378 ||'storage_location,'
4379 ||'source_comment,'
4380 ||'charge,'
4381 ||'order_id,'
4382 ||'order_line_id,'
4383 ||'org_id,'
4384 ||'ship_to_site_id,'
4385 ||'priority,'
4386 ||'delete_mark,'
4387 ||'text_code,'
4388 ||'creation_date,'
4389 ||'created_by,'
4390 ||'last_update_date,'
4391 ||'last_updated_by,'
4392 ||'last_update_login,'
4393 ||'sample_type,'
4394 ||'attribute1,'
4395 ||'attribute2,'
4396 ||'attribute3,'
4397 ||'attribute4,'
4398 ||'attribute5,'
4399 ||'attribute6,'
4400 ||'attribute7,'
4401 ||'attribute8,'
4402 ||'attribute9,'
4403 ||'attribute10,'
4404 ||'attribute11,'
4405 ||'attribute12,'
4406 ||'attribute13,'
4407 ||'attribute14,'
4408 ||'attribute15,'
4409 ||'attribute16,'
4410 ||'attribute17,'
4411 ||'attribute18,'
4412 ||'attribute19,'
4413 ||'attribute20,'
4414 ||'attribute21,'
4415 ||'attribute22,'
4416 ||'attribute23,'
4417 ||'attribute24,'
4418 ||'attribute25,'
4419 ||'attribute26,'
4420 ||'attribute27,'
4421 ||'attribute28,'
4422 ||'attribute29,'
4423 ||'attribute30,'
4424 ||'attribute_category'
4425 ||')'
4426 ||' VALUES '
4427 ||'( '
4428 ||':sample_id,'
4429 ||':orgn_code,'
4430 ||':l_sample_no,'
4431 ||':sample_desc,'
4432 ||':l_disposition,'
4433 ||':l_sampling_event_id,'
4434 ||':qc_rec_type,'
4435 ||':batch_id,'
4436 ||':l_recipe_id,'
4437 ||':formula_id,'
4438 ||':formulaline_id,'
4439 ||':routing_id,'
4440 ||':routingstep_id,'
4441 ||':oprn_id,'
4442 ||':item_id,'
4443 ||':lot_id,'
4444 ||':l_lot_no,'
4445 ||':l_sublot_no,'
4446 ||':whse_code,'
4447 ||':location,'
4448 ||':cust_id,'
4449 ||':l_supplier_id,'
4450 ||':sample_date,'
4451 ||':sampled_by,'
4452 ||':sample_qty,'
4453 ||':sample_um,'
4454 ||':external_id,'
4455 ||':sample_final_approver,'
4456 ||':sample_test_approver,'
4457 ||':storage_whse,'
4458 ||':storage_location,'
4459 ||':sample_source,'
4460 ||':charge,'
4461 ||':order_header_id,'
4462 ||':order_line_id,'
4463 ||':order_org_id,'
4464 ||':ship_to_site_id,'
4465 ||':l_prty,'
4466 ||':delete_mark,'
4467 ||':text_code,'
4468 ||':creation_date,'
4469 ||':created_by,'
4470 ||':last_update_date,'
4471 ||':last_updated_by,'
4472 ||':last_update_login,'
4473 ||':l_i,'
4474 ||':attribute1,'
4475 ||':attribute2,'
4476 ||':attribute3,'
4477 ||':attribute4,'
4478 ||':attribute5,'
4479 ||':attribute6,'
4480 ||':attribute7,'
4481 ||':attribute8,'
4482 ||':attribute9,'
4483 ||':attribute10,'
4484 ||':attribute11,'
4485 ||':attribute12,'
4486 ||':attribute13,'
4487 ||':attribute14,'
4488 ||':attribute15,'
4489 ||':attribute16,'
4490 ||':attribute17,'
4491 ||':attribute18,'
4492 ||':attribute19,'
4493 ||':attribute20,'
4494 ||':attribute21,'
4495 ||':attribute22,'
4496 ||':attribute23,'
4497 ||':attribute24,'
4498 ||':attribute25,'
4499 ||':attribute26,'
4500 ||':attribute27,'
4501 ||':attribute28,'
4502 ||':attribute29,'
4503 ||':attribute30,'
4504 ||':attribute_category'
4505 ||' )';
4506
4507 END IF;
4508 -- End 3486120, moved sql statement out the loop
4509
4510 /* Select sample data that has not been migrated */
4511 OPEN c_get_samples;
4512 FETCH c_get_samples into smpl_rec;
4513
4514 /* While there are spec header groupings that have not been migrated */
4515 WHILE c_get_samples%FOUND LOOP
4516
4517 -- PK Bug 4898620 Approvers in QC are saved as Usernames. Need to find IDs.
4518
4519 IF ( smpl_rec.sample_final_approver IS NOT NULL) THEN
4520 OPEN fnd_user(smpl_rec.sample_final_approver);
4521 Fetch fnd_user INTO l_inv_approver ;
4522 Close fnd_user ;
4523 ELSE
4524 l_inv_approver := NULL;
4525 END IF;
4526
4527 IF ( smpl_rec.sample_test_approver IS NOT NULL) THEN
4528 OPEN fnd_user(smpl_rec.sample_test_approver);
4529 Fetch fnd_user INTO l_sample_approver ;
4530 Close fnd_user ;
4531 ELSE
4532 l_sample_approver := NULL;
4533 END IF;
4534
4535 SAVEPOINT Sample_Rec;
4536
4537
4538 /* Convert to new disposition */
4539
4540 /* M. Grosser 27-Sep-2002 BUG 2596865 - Modified code to look for a
4541 value of 'ACCEPT' instead of 'ACCEPTED'
4542 */
4543 IF smpl_rec.sample_status = 'ACCEPT' THEN
4544 l_disposition := '4A';
4545 ELSIF smpl_rec.sample_status = 'REJECT' THEN
4546 l_disposition := '6RJ';
4547 ELSIF smpl_rec.sample_status = 'PENDING' THEN
4548 l_disposition := '1P';
4549 ELSE
4550 /* All other statuses (retest, partial retest, etc) should be set to inprogress */
4551 l_disposition := '2I';
4552 END IF;
4553
4554 /* Get the purchasing supplier ids */
4555 IF smpl_rec.vendor_id IS NOT NULL THEN
4556 OPEN g_get_supplier_ids(smpl_rec.vendor_id);
4557 FETCH g_get_supplier_ids into l_supplier_id, l_supplier_site_id;
4558 CLOSE g_get_supplier_ids;
4559 ELSE
4560 l_supplier_id := NULL;
4561 l_supplier_site_id := NULL;
4562 END IF;
4563
4564 l_spec_vr_id := NULL;
4565 l_spec_id := NULL;
4566 l_r_tests_cnt := 0;
4567
4568 OPEN c_get_r_tests_cnt;
4569 FETCH c_get_r_tests_cnt INTO l_r_tests_cnt;
4570 CLOSE c_get_r_tests_cnt;
4571
4572 /* Spec Matching code is only performed for samples with results and
4573 qc_spec_id is not null */
4574 IF nvl(l_r_tests_cnt,0) > 0 THEN
4575
4576 /* Get the spec validity rule from the mapping table */
4577 OPEN c_get_mapping;
4578 FETCH c_get_mapping into l_spec_vr_id, l_spec_id;
4579
4580 /* If results were NOT entered against a specification */
4581 IF c_get_mapping%NOTFOUND THEN
4582 /* Bug 3376111; Sample( Sample_date ) could be outside the spec's
4583 start_date */
4584 l_spec_id := NULL;
4585
4586 OPEN c_get_sm_specs;
4587 FETCH c_get_sm_specs into l_spec_id, l_spec_vr_id, l_sm_tests_cnt;
4588 IF c_get_sm_specs%FOUND then
4589
4590 IF l_sm_tests_cnt = 0 then
4591 GMA_MIGRATION.gma_insert_message (
4592 p_run_id => p_migration_id,
4593 p_table_name => 'QC_SMPL_MST',
4594 p_DB_ERROR => '',
4595 p_param1 => 'Sample with results have with spec tests',
4596 p_param2 => 'Cannot match migrated spec ',
4597 p_param3 => 'Sample_id = '||smpl_rec.sample_id,
4598 p_param4 => '',
4599 p_param5 => '',
4600 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
4601 p_message_type => 'P',
4602 p_line_no => '1',
4603 p_position => '',
4604 p_base_message => '');
4605 End if;
4606 END IF; /* c_get_sm_specs */
4607 CLOSE c_get_sm_specs;
4608 END IF; /* c_get_mapping; If sample was entered against a spec */
4609 CLOSE c_get_mapping;
4610
4611 ELSE /* Samples w/out Results; find spec w/spec matching */
4612
4613 l_r_tests_cnt := 0;
4614 OPEN c_chk_smpl_has_rslt;
4615 FETCH c_chk_smpl_has_rslt into l_r_tests_cnt;
4616 CLOSE c_chk_smpl_has_rslt;
4617 IF nvl(l_r_tests_cnt,0) = 0 THEN /* Sample has No Results */
4618
4619 /* Try to find an applicable spec through spec matching */
4620 IF smpl_rec.qc_rec_type = 'P' THEN
4621 /* value in new table is W */
4622 smpl_rec.qc_rec_type := 'W';
4623 l_wip_spec_rec.item_id := smpl_rec.item_id;
4624 l_wip_spec_rec.orgn_code := smpl_rec.orgn_code;
4625 l_wip_spec_rec.batch_id := smpl_rec.batch_id;
4626 l_wip_spec_rec.formula_id := smpl_rec.formula_id;
4627 l_wip_spec_rec.formulaline_id := smpl_rec.formulaline_id;
4628 l_wip_spec_rec.routing_id := smpl_rec.routing_id;
4629 l_wip_spec_rec.step_id := smpl_rec.routingstep_id;
4630 l_wip_spec_rec.oprn_id := smpl_rec.oprn_id;
4631 l_wip_spec_rec.charge := smpl_rec.charge;
4632 l_wip_spec_rec.lot_id := smpl_rec.lot_id;
4633 l_wip_spec_rec.date_effective := smpl_rec.sample_date;
4634 l_wip_spec_rec.exact_match := 'N';
4635
4636 IF NOT (GMD_SPEC_MATCH_MIG_GRP.FIND_WIP_OR_INV_SPEC(
4637 p_wip_spec_rec => l_wip_spec_rec,
4638 x_spec_id => l_spec_id,
4639 x_spec_vr_id => l_spec_vr_id,
4640 x_spec_type => l_spec_type,
4641 x_return_status => l_return_status,
4642 x_message_data => l_message_data )) THEN
4643 l_spec_vr_id := NULL;
4644 l_spec_id := NULL;
4645 END IF; /* No matching spec could be found */
4646
4647 ELSIF smpl_rec.qc_rec_type = 'C' THEN
4648 l_customer_spec_rec.item_id := smpl_rec.item_id;
4649 l_customer_spec_rec.cust_id := smpl_rec.cust_id;
4650 l_customer_spec_rec.orgn_code := smpl_rec.orgn_code;
4651 l_customer_spec_rec.whse_code := smpl_rec.whse_code;
4652 l_customer_spec_rec.org_id := smpl_rec.order_org_id;
4653 l_customer_spec_rec.order_id := smpl_rec.order_header_id;
4654 l_customer_spec_rec.order_line_id := smpl_rec.order_line_id;
4655 l_customer_spec_rec.ship_to_site_id := smpl_rec.ship_to_site_id;
4656 l_customer_spec_rec.date_effective := smpl_rec.sample_date;
4657 l_customer_spec_rec.lot_id := smpl_rec.lot_id;
4658 l_customer_spec_rec.look_in_other_orgn := 'N';
4659 l_customer_spec_rec.exact_match := 'N';
4660
4661 IF NOT(GMD_SPEC_MATCH_MIG_GRP.FIND_CUST_OR_INV_SPEC(
4662 p_customer_spec_rec => l_customer_spec_rec,
4663 x_spec_id => l_spec_id,
4664 x_spec_vr_id => l_spec_vr_id,
4665 x_spec_type => l_spec_type,
4666 x_return_status => l_return_status,
4667 x_message_data => l_message_data )) THEN
4668 l_spec_vr_id := NULL;
4669 l_spec_id := NULL;
4670 END IF; /* No matching spec could be found */
4671
4672 ELSIF smpl_rec.qc_rec_type = 'I' THEN
4673 l_inventory_spec_rec.item_id := smpl_rec.item_id;
4674 l_inventory_spec_rec.orgn_code := smpl_rec.orgn_code;
4675 l_inventory_spec_rec.lot_id := smpl_rec.lot_id;
4676 l_inventory_spec_rec.whse_code := smpl_rec.whse_code;
4677 l_inventory_spec_rec.location := smpl_rec.location;
4678 l_inventory_spec_rec.date_effective := smpl_rec.sample_date;
4679 l_inventory_spec_rec.exact_match := 'N';
4680
4681 IF NOT (GMD_SPEC_MATCH_MIG_GRP.FIND_INVENTORY_SPEC(
4682 p_inventory_spec_rec => l_inventory_spec_rec,
4683 x_spec_id => l_spec_id,
4684 x_spec_vr_id => l_spec_vr_id,
4685 x_return_status => l_return_status,
4686 x_message_data => l_message_data )) THEN
4687 l_spec_vr_id := NULL;
4688 l_spec_id := NULL;
4689 END IF; /* No matching spec could be found */
4690
4691 ELSIF smpl_rec.qc_rec_type = 'S' THEN
4692 l_supplier_spec_rec.item_id := smpl_rec.item_id;
4693 l_supplier_spec_rec.orgn_code := smpl_rec.orgn_code;
4694 l_supplier_spec_rec.lot_id := l_supplier_id;
4695 l_supplier_spec_rec.whse_code := smpl_rec.whse_code;
4696 l_supplier_spec_rec.supplier_site_id := l_supplier_site_id;
4697 l_supplier_spec_rec.date_effective := smpl_rec.sample_date;
4698 l_supplier_spec_rec.lot_id := smpl_rec.lot_id;
4699 l_supplier_spec_rec.exact_match := 'N';
4700
4701 IF NOT (GMD_SPEC_MATCH_MIG_GRP.FIND_SUPPLIER_OR_INV_SPEC(
4702 p_supplier_spec_rec => l_supplier_spec_rec,
4703 x_spec_id => l_spec_id,
4704 x_spec_vr_id => l_spec_vr_id,
4705 x_spec_type => l_spec_type,
4706 x_return_status => l_return_status,
4707 x_message_data => l_message_data )) THEN
4708 l_spec_vr_id := NULL;
4709 l_spec_id := NULL;
4710 END IF; /* No matching spec could be found */
4711
4712 END IF; /* Type of sample */
4713 END IF; /* Sample does not have Results */
4714 END IF;
4715
4716 /* if this is a production spec */
4717 IF smpl_rec.qc_rec_type = 'P' THEN
4718 /* value in new table is W */
4719 smpl_rec.qc_rec_type := 'W';
4720 END IF;
4721
4722 /* B2714760 If lot_id is NOT NULL then fetch lot_no and sublot_no */
4723 IF smpl_rec.lot_id IS NOT NULL THEN
4724 OPEN c_lot_sublot(smpl_rec.lot_id);
4725 FETCH c_lot_sublot INTO l_lot_no, l_sublot_no;
4726 CLOSE c_lot_sublot;
4727 ELSE
4728 l_lot_no := NULL;
4729 l_sublot_no := NULL;
4730 END IF;
4731
4732 /* B2714760 If batch_id is NOT NULL then fetch recipe_id */
4733 IF smpl_rec.batch_id IS NOT NULL THEN
4734 OPEN c_recipe_id(smpl_rec.batch_id);
4735 FETCH c_recipe_id INTO l_recipe_id;
4736 CLOSE c_recipe_id;
4737 ELSE
4738 l_recipe_id := NULL;
4739 END IF;
4740
4741 /* Get the new sampling event id */
4742 OPEN c_get_sampling_event_id;
4743 FETCH c_get_sampling_event_id into l_sampling_event_id;
4744 CLOSE c_get_sampling_event_id;
4745
4746 /* Create sampling event to link to sample */
4747 -- B3883674 Use appropriate variable
4748 IF l_sampling_events_level IS NULL THEN
4749 -- IF l_patch_level IS NULL THEN
4750 -- Customer is on J
4751 --Moved the sql string build out the loop 3486120
4752 EXECUTE IMMEDIATE l_sql_stmt1 USING
4753 smpl_rec.item_id,
4754 l_sampling_event_id,
4755 l_spec_vr_id,
4756 l_y,
4757 l_disposition,
4758 smpl_rec.qc_rec_type,
4759 l_1,
4760 l_1,
4761 smpl_rec.batch_id,
4762 l_recipe_id,
4763 smpl_rec.formula_id,
4764 smpl_rec.formulaline_id,
4765 smpl_rec.routing_id,
4766 smpl_rec.routingstep_id,
4767 smpl_rec.oprn_id,
4768 smpl_rec.lot_id,
4769 l_lot_no,
4770 l_sublot_no,
4771 smpl_rec.whse_code,
4772 smpl_rec.location,
4773 smpl_rec.cust_id,
4774 l_supplier_id,
4775 smpl_rec.charge,
4776 smpl_rec.order_header_id,
4777 smpl_rec.order_line_id,
4778 smpl_rec.order_org_id,
4779 smpl_rec.ship_to_site_id,
4780 smpl_rec.creation_date,
4781 smpl_rec.created_by,
4782 smpl_rec.last_update_date,
4783 smpl_rec.last_updated_by,
4784 smpl_rec.last_update_login;
4785
4786 ELSE
4787 -- Customer is on K
4788
4789 --Bug 3486120, moved the sql string build out of the loop
4790 EXECUTE IMMEDIATE l_sql_stmt1 USING
4791 smpl_rec.item_id,
4792 l_sampling_event_id,
4793 l_spec_vr_id,
4794 'Y',
4795 l_disposition,
4796 smpl_rec.qc_rec_type,
4797 '1',
4798 '1',
4799 smpl_rec.batch_id,
4800 l_recipe_id,
4801 smpl_rec.formula_id,
4802 smpl_rec.formulaline_id,
4803 smpl_rec.routing_id,
4804 smpl_rec.routingstep_id,
4805 smpl_rec.oprn_id,
4806 smpl_rec.lot_id,
4807 l_lot_no,
4808 l_sublot_no,
4809 smpl_rec.whse_code,
4810 smpl_rec.location,
4811 smpl_rec.cust_id,
4812 l_supplier_id,
4813 smpl_rec.charge,
4814 smpl_rec.order_header_id,
4815 smpl_rec.order_line_id,
4816 smpl_rec.order_org_id,
4817 smpl_rec.ship_to_site_id,
4818 smpl_rec.creation_date,
4819 smpl_rec.created_by,
4820 smpl_rec.last_update_date,
4821 smpl_rec.last_updated_by,
4822 smpl_rec.last_update_login,
4823 'I';
4824
4825 END IF;
4826
4827 /* M. Grosser 29-Sep-2002 BUG 2596689 - Modified code to check to see
4828 if a sample no is used more than once within an
4829 organization. If so, add the record type to the
4830 sample name
4831 */
4832 /* Check to see if the same sample number exists within the same
4833 organization but with a different record type since that used
4834 to be allowed. If so, concatenate the record type to the end
4835 */
4836 OPEN c_check_dup;
4837 FETCH c_check_dup INTO l_dup_count;
4838 CLOSE c_check_dup;
4839
4840 IF l_dup_count > 1 THEN
4841 l_sample_no := smpl_rec.sample_no ||'-'||smpl_rec.sample_id;
4842 ELSE
4843 l_sample_no := smpl_rec.sample_no;
4844 END IF;
4845
4846 /* Insert record into new sample table */
4847 -- B3883674 Use appropriate variable
4848 IF l_samples_level IS NULL THEN
4849 -- IF l_patch_level IS NULL THEN
4850 -- Customer is on J
4851
4852 EXECUTE IMMEDIATE l_sql_stmt2 USING
4853 smpl_rec.sample_id,
4854 smpl_rec.orgn_code,
4855 l_sample_no,
4856 smpl_rec.sample_desc,
4857 l_disposition,
4858 l_sampling_event_id,
4859 smpl_rec.qc_rec_type,
4860 smpl_rec.batch_id,
4861 l_recipe_id,
4862 smpl_rec.formula_id,
4863 smpl_rec.formulaline_id,
4864 smpl_rec.routing_id,
4865 smpl_rec.routingstep_id,
4866 smpl_rec.oprn_id,
4867 smpl_rec.item_id,
4868 smpl_rec.lot_id,
4869 l_lot_no,
4870 l_sublot_no,
4871 smpl_rec.whse_code,
4872 smpl_rec.location,
4873 smpl_rec.cust_id,
4874 l_supplier_id,
4875 smpl_rec.sample_date,
4876 smpl_rec.sampled_by,
4877 smpl_rec.sample_qty,
4878 smpl_rec.sample_um,
4879 smpl_rec.external_id,
4880 l_inv_approver, -- 4898620
4881 l_sample_approver, -- 4898620
4882 smpl_rec.storage_whse,
4883 smpl_rec.storage_location,
4884 smpl_rec.sample_source,
4885 smpl_rec.charge,
4886 smpl_rec.order_header_id,
4887 smpl_rec.order_line_id,
4888 smpl_rec.order_org_id,
4889 smpl_rec.ship_to_site_id,
4890 l_prty,
4891 smpl_rec.delete_mark,
4892 smpl_rec.creation_date,
4893 smpl_rec.created_by,
4894 smpl_rec.last_update_date,
4895 smpl_rec.last_updated_by,
4896 smpl_rec.last_update_login ,
4897 smpl_rec.attribute1,
4898 smpl_rec.attribute2,
4899 smpl_rec.attribute3,
4900 smpl_rec.attribute4,
4901 smpl_rec.attribute5,
4902 smpl_rec.attribute6,
4903 smpl_rec.attribute7,
4904 smpl_rec.attribute8,
4905 smpl_rec.attribute9,
4906 smpl_rec.attribute10,
4907 smpl_rec.attribute11,
4908 smpl_rec.attribute12,
4909 smpl_rec.attribute13,
4910 smpl_rec.attribute14,
4911 smpl_rec.attribute15,
4912 smpl_rec.attribute16,
4913 smpl_rec.attribute17,
4914 smpl_rec.attribute18,
4915 smpl_rec.attribute19,
4916 smpl_rec.attribute20,
4917 smpl_rec.attribute21,
4918 smpl_rec.attribute22,
4919 smpl_rec.attribute23,
4920 smpl_rec.attribute24,
4921 smpl_rec.attribute25,
4922 smpl_rec.attribute26,
4923 smpl_rec.attribute27,
4924 smpl_rec.attribute28,
4925 smpl_rec.attribute29,
4926 smpl_rec.attribute30,
4927 smpl_rec.attribute_category;
4928 ELSE
4929 --sql statement construction taken out of loop,Changed it for bug no. 3486120
4930 -- Customer is on K
4931 EXECUTE IMMEDIATE l_sql_stmt2 USING
4932 smpl_rec.sample_id,
4933 smpl_rec.orgn_code,
4934 l_sample_no,
4935 smpl_rec.sample_desc,
4936 l_disposition,
4937 l_sampling_event_id,
4938 smpl_rec.qc_rec_type,
4939 smpl_rec.batch_id,
4940 l_recipe_id,
4941 smpl_rec.formula_id,
4942 smpl_rec.formulaline_id,
4943 smpl_rec.routing_id,
4944 smpl_rec.routingstep_id,
4945 smpl_rec.oprn_id,
4946 smpl_rec.item_id,
4947 smpl_rec.lot_id,
4948 l_lot_no,
4949 l_sublot_no,
4950 smpl_rec.whse_code,
4951 smpl_rec.location,
4952 smpl_rec.cust_id,
4953 l_supplier_id,
4954 smpl_rec.sample_date,
4955 smpl_rec.sampled_by,
4956 smpl_rec.sample_qty,
4957 smpl_rec.sample_um,
4958 smpl_rec.external_id,
4959 l_inv_approver, -- 4898620
4960 l_sample_approver, -- 4898620
4961 smpl_rec.storage_whse,
4962 smpl_rec.storage_location,
4963 smpl_rec.sample_source,
4964 smpl_rec.charge,
4965 smpl_rec.order_header_id,
4966 smpl_rec.order_line_id,
4967 smpl_rec.order_org_id,
4968 smpl_rec.ship_to_site_id,
4969 l_prty,
4970 smpl_rec.delete_mark,
4971 smpl_rec.text_code,
4972 smpl_rec.creation_date,
4973 smpl_rec.created_by,
4974 smpl_rec.last_update_date,
4975 smpl_rec.last_updated_by,
4976 smpl_rec.last_update_login,
4977 'I',
4978 smpl_rec.attribute1,
4979 smpl_rec.attribute2,
4980 smpl_rec.attribute3,
4981 smpl_rec.attribute4,
4982 smpl_rec.attribute5,
4983 smpl_rec.attribute6,
4984 smpl_rec.attribute7,
4985 smpl_rec.attribute8,
4986 smpl_rec.attribute9,
4987 smpl_rec.attribute10,
4988 smpl_rec.attribute11,
4989 smpl_rec.attribute12,
4990 smpl_rec.attribute13,
4991 smpl_rec.attribute14,
4992 smpl_rec.attribute15,
4993 smpl_rec.attribute16,
4994 smpl_rec.attribute17,
4995 smpl_rec.attribute18,
4996 smpl_rec.attribute19,
4997 smpl_rec.attribute20,
4998 smpl_rec.attribute21,
4999 smpl_rec.attribute22,
5000 smpl_rec.attribute23,
5001 smpl_rec.attribute24,
5002 smpl_rec.attribute25,
5003 smpl_rec.attribute26,
5004 smpl_rec.attribute27,
5005 smpl_rec.attribute28,
5006 smpl_rec.attribute29,
5007 smpl_rec.attribute30,
5008 smpl_rec.attribute_category;
5009
5010 END IF;
5011 --Bug 3486120
5012 /* Get the new event spec disp id */
5013 --OPEN c_get_event_spec_disp_id;
5014 --FETCH c_get_event_spec_disp_id into l_event_spec_disp_id;
5015 --CLOSE c_get_event_spec_disp_id;
5016 --End Bug 3486120
5017
5018
5019 /* Create new event spec disposition, use sample dispostion */
5020
5021 INSERT INTO gmd_event_spec_disp
5022 (
5023 event_spec_disp_id,
5024 sampling_event_id,
5025 spec_id,
5026 spec_vr_id,
5027 disposition,
5028 spec_used_for_lot_attrib_ind,
5029 delete_mark,
5030 creation_date,
5031 created_by,
5032 last_update_date,
5033 last_updated_by,
5034 last_update_login
5035 )
5036 VALUES
5037 (
5038 gmd_qc_event_spec_disp_id_s.nextval, --Bug 3486120, changed it for performance
5039 l_sampling_event_id,
5040 l_spec_id,
5041 l_spec_vr_id,
5042 l_disposition,
5043 'Y',
5044 '0',
5045 smpl_rec.creation_date,
5046 smpl_rec.created_by,
5047 smpl_rec.last_update_date,
5048 smpl_rec.last_updated_by,
5049 smpl_rec.last_update_login
5050 ) RETURNING event_spec_disp_id INTO l_event_spec_disp_id; --Bug 3486120, changed it for perm.
5051
5052 /* Create new sample spec disposition to hold sample dispostion */
5053
5054 INSERT INTO gmd_sample_spec_disp
5055 (
5056 event_spec_disp_id,
5057 sample_id,
5058 disposition,
5059 delete_mark,
5060 creation_date,
5061 created_by,
5062 last_update_date,
5063 last_updated_by,
5064 last_update_login
5065 )
5066 VALUES
5067 (
5068 l_event_spec_disp_id,
5069 smpl_rec.sample_id,
5070 l_disposition,
5071 '0',
5072 smpl_rec.creation_date,
5073 smpl_rec.created_by,
5074 smpl_rec.last_update_date,
5075 smpl_rec.last_updated_by,
5076 smpl_rec.last_update_login
5077 );
5078
5079 /* Set status to migrated for record */
5080 UPDATE qc_smpl_mst
5081 SET migration_status = 'MO'
5082 WHERE sample_id = smpl_rec.sample_id;
5083
5084 COMMIT;
5085
5086 l_rec_count := l_rec_count + 1;
5087
5088 FETCH c_get_samples into smpl_rec;
5089
5090 END LOOP; /* Number of records returned */
5091
5092 CLOSE c_get_samples;
5093
5094 GMA_MIGRATION.gma_insert_message (
5095 p_run_id => p_migration_id,
5096 p_table_name => 'QC_SMPL_MST',
5097 p_DB_ERROR => '',
5098 p_param1 => '',
5099 p_param2 => l_rec_count,
5100 p_param3 => '',
5101 p_param4 => '',
5102 p_param5 => '',
5103 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
5104 p_message_type => 'P',
5105 p_line_no => '1',
5106 p_position => '',
5107 p_base_message => '');
5108
5109 EXCEPTION
5110 WHEN OTHERS THEN
5111 x_return_status := 'U';
5112 ROLLBACK TO SAVEPOINT Sample_Rec;
5113 GMA_MIGRATION.gma_insert_message (
5114 p_run_id => p_migration_id,
5115 p_table_name => 'QC_SMPL_MST',
5116 p_DB_ERROR => sqlerrm,
5117 p_param1 => '',
5118 p_param2 => '',
5119 p_param3 => '',
5120 p_param4 => '',
5121 p_param5 => '',
5122 p_message_token => 'GMA_MIGRATION_DB_ERROR',
5123 p_message_type => 'E',
5124 p_line_no => '1',
5125 p_position => '',
5126 p_base_message => 'Failed to migrate samples due to '||sqlerrm);
5127
5128 GMA_MIGRATION.gma_insert_message (
5129 p_run_id => p_migration_id,
5130 p_table_name => 'QC_SMPL_MST',
5131 p_DB_ERROR => '',
5132 p_param1 => '',
5133 p_param2 => '',
5134 p_param3 => '',
5135 p_param4 => '',
5136 p_param5 => '',
5137 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
5138 p_message_type => 'P',
5139 p_line_no => '1',
5140 p_position => '',
5141 p_base_message => '');
5142
5143
5144 END Migrate_Samples;
5145
5146
5147
5148
5149 /*===========================================================================
5150 -- PROCEDURE:
5151 -- Migrate_Results
5152 --
5153 -- DESCRIPTION:
5154 -- This PL/SQL procedure is used to migrate results to the new data
5155 -- model for OPM patch 11.5.1J.
5156 --
5157 -- PARAMETERS:
5158 -- p_migration_id - id to use to right to migration log
5159 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
5160 --
5161 -- SYNOPSIS:
5162 -- Migrate_Samples;
5163 --
5164 -- HISTORY
5165 -- M. Grosser 24-Sep-2002 Added cursor to set value of column seq in
5166 -- gmd_results in procedure Migrate_Results
5167 -- M. Grosser 08-Oct-2002 Moved additional_test_ind from gmd_results to
5168 -- gmd_spec_results
5169 -- B. Stone 21-Jan-2004 Moved the Fetch to c_ids to outside the IF
5170 -- following IF statement so it will execute for
5171 -- all results; before it was only executing for
5172 -- results with specs ( qc_spec_id not = null )
5173 -- 27-Jan-2004 B.Stone Bug 3388873 -
5174 -- Added Index Hint to table GMD_RESULTS column
5175 -- SAMPLE_ID for cursor C_GET_SEQ
5176 -- B. Stone 13-Oct-2004 Bug 3934121;
5177 -- Added ORDER BY assay_code to c_get_results
5178 -- so spec tests and result tests are displayed
5179 -- in the same order.
5180
5181 --=========================================================================== */
5182 PROCEDURE Migrate_Results (p_migration_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2)
5183 IS
5184
5185 /* ------------- LOCAL VARIABLES ------------------- */
5186 l_sampling_event_id NUMBER;
5187 l_sample_spec_disp_id NUMBER;
5188 l_in_spec_ind GMD_SPEC_RESULTS.in_spec_ind%TYPE;
5189 l_rec_count NUMBER := 0;
5190 l_seq NUMBER;
5191 l_additional_test_ind VARCHAR2(4);
5192 l_qc_lab_orgn_code GMD_RESULTS.qc_lab_orgn_code%TYPE;
5193 l_evaluation_ind GMD_SPEC_RESULTS.evaluation_ind%TYPE;
5194 l_return_status VARCHAR2(4);
5195 l_base_lang FND_LANGUAGES.LANGUAGE_CODE%TYPE;
5196 l_test_seq NUMBER;
5197 l_retest_lot_exp_ind VARCHAR2(1);
5198 l_temp NUMBER;
5199 l_commit_count NUMBER:=0;
5200 l_result_dt_null DATE:=TO_DATE('01-01-1970 00:00:00','DD-MM-YYYY HH24:MI:SS');
5201
5202 /* ------------------ CURSORS ---------------------- */
5203 /* Select results data that has not been migrated */
5204 CURSOR c_get_results IS
5205 SELECT qcassy_typ_id,
5206 qc_result_id,
5207 qc_spec_id,
5208 sample_id,
5209 orgn_code,
5210 result_date,
5211 assay_code,
5212 text_result,
5213 num_result,
5214 qcunit_code,
5215 accept_anyway,
5216 final_mark,
5217 test_provider_code,
5218 assay_tester,
5219 assay_retest,
5220 wf_response,
5221 item_id,
5222 lot_id,
5223 whse_code,
5224 location,
5225 cust_id,
5226 vendor_id,
5227 charge,
5228 qc_rec_type,
5229 ship_to_site_id,
5230 delete_mark,
5231 text_code,
5232 creation_date,
5233 created_by,
5234 last_update_date,
5235 last_updated_by,
5236 last_update_login,
5237 attribute1,
5238 attribute2,
5239 attribute3,
5240 attribute4,
5241 attribute5,
5242 attribute6,
5243 attribute7,
5244 attribute8,
5245 attribute9,
5246 attribute10,
5247 attribute11,
5248 attribute12,
5249 attribute13,
5250 attribute14,
5251 attribute15,
5252 attribute16,
5253 attribute17,
5254 attribute18,
5255 attribute19,
5256 attribute20,
5257 attribute21,
5258 attribute22,
5259 attribute23,
5260 attribute24,
5261 attribute25,
5262 attribute26,
5263 attribute27,
5264 attribute28,
5265 attribute29,
5266 attribute30,
5267 attribute_category
5268 FROM qc_rslt_mst
5269 WHERE migration_status is NULL
5270 ORDER BY assay_code;
5271 -- Bug 3934121; Added ORDER BY assay_code so spec tests and result tests
5272 -- are displayed in the same order.
5273 rslt_rec c_get_results%ROWTYPE;
5274
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
5284 /* M. Grosser 24-Sep-2002 Added cursor to set value of column seq in
5285 gmd_results in procedure Migrate_Results
5286 */
5287 /* Retrieve the next sequence number for use in gmd_results */
5288 /* Bug 3388873 - Added Index Hint to table GMD_RESULTS column
5289 SAMPLE_ID for cursor C_GET_SEQ */
5290 CURSOR c_get_seq IS
5291 SELECT /*+ INDEX ( gmd_results gmd.gmd_results_n1 ) */
5292 NVL(max(seq),0) + 10
5293 FROM gmd_results
5294 WHERE sample_id = rslt_rec.sample_id;
5295
5296 /* Check that test is in the spec */
5297 CURSOR c_check_spec (pspec_id NUMBER, ptest_id NUMBER) IS
5298 SELECT 1
5299 FROM gmd_spec_tests_b
5300 WHERE spec_id = pspec_id
5301 AND test_id = ptest_id;
5302
5303 /* Retrieve the next sequence number for use in gmd_spec_tests_b */
5304 CURSOR c_get_spec_seq (pspec_id NUMBER) IS
5305 SELECT NVL(max(seq),0) + 10
5306 FROM gmd_spec_tests_b
5307 WHERE spec_id = pspec_id;
5308
5309 /* Retrieve the value of retest_lot_expiry_ind for use in gmd_spec_tests_b */
5310 CURSOR c_get_retest_lot (pspec_id NUMBER) IS
5311 SELECT retest_lot_expiry_ind
5312 FROM gmd_spec_tests_b
5313 WHERE spec_id = pspec_id;
5314
5315 BEGIN
5316
5317 GMA_MIGRATION.gma_insert_message (
5318 p_run_id => p_migration_id,
5319 p_table_name => 'QC_RSLT_MST',
5320 p_DB_ERROR => '',
5321 p_param1 => '',
5322 p_param2 => '',
5323 p_param3 => '',
5324 p_param4 => '',
5325 p_param5 => '',
5326 p_message_token => 'GMA_MIGRATION_TABLE_STARTED',
5327 p_message_type => 'P',
5328 p_line_no => '1',
5329 p_position => '',
5330 p_base_message => '');
5331 COMMIT;
5332
5333 /* Get the installation's base language */
5334 l_base_lang := Get_Base_Language;
5335
5336 /* Get the default lab type */
5337 l_qc_lab_orgn_code := FND_PROFILE.VALUE('GEMMS_DEFAULT_LAB_TYPE');
5338
5339 /* Select results data that has not been migrated */
5340 OPEN c_get_results;
5341 FETCH c_get_results into rslt_rec;
5342
5343 /* While there are results that have not been migrated */
5344 WHILE c_get_results%FOUND LOOP
5345
5346 /* SAVEPOINT Result_Rec; */
5347 -- Bug 3934121; Result date was assigned default value of 1-1-1970 and
5348 -- result_num assigned default value of 0 when no results
5349 -- were recoreded. Need to replace these values with NULL;
5350 -- UNLESS THE:
5351 -- TEXT_RESULT IS NOT NULL or NUM_RESULT <> 0
5352 -- THEN only THE RESULT_DATE IS updated wicreateion_date.
5353 -- NUM_RESULT = 0 or is NULL THEN
5354 -- RESULT_DATE IS updated with NULL.
5355 -- NUM_RESULT IS updated with NULL.
5356
5357 IF rslt_rec.result_date = l_result_dt_null THEN
5358 IF rslt_rec.text_result is NOT NULL or
5359 ( rslt_rec.num_result <> 0 ) THEN
5360 rslt_rec.result_date := rslt_rec.creation_date;
5361 ELSE IF ( rslt_rec.num_result = 0 ) or
5362 ( rslt_rec.num_result IS NULL ) THEN
5363 rslt_rec.result_date := NULL;
5364 rslt_rec.num_result := NULL;
5365 ELSE
5366 rslt_rec.result_date := NULL;
5367 rslt_rec.num_result := NULL;
5368 rslt_rec.text_result := NULL;
5369 END IF;
5370 END IF;
5371 END IF;
5372
5373 /* If this is a production sample */
5374 IF rslt_rec.qc_rec_type = 'P' THEN
5375 /* Value in new table is W */
5376 rslt_rec.qc_rec_type := 'W';
5377 END IF;
5378
5379 l_evaluation_ind := NULL;
5380 l_additional_test_ind := NULL;
5381 l_in_spec_ind := NULL;
5382
5383 -- Bug 3388873; Code moved outside If statement below so it is called
5384 -- for all Results
5385 /* Retrieve sample_event_spec_disp_id using sample id */
5386 OPEN c_get_ids;
5387 FETCH c_get_ids into id_rec;
5388 CLOSE c_get_ids;
5389
5390 IF rslt_rec.qc_spec_id IS NOT NULL AND id_rec.spec_id IS NOT NULL THEN
5391 /* If the sample was entered against a spec */
5392 /* Make sure test was included in spec - due to date issues */
5393 OPEN c_check_spec(id_rec.spec_id,rslt_rec.qcassy_typ_id);
5394 FETCH c_check_spec INTO l_temp;
5395 -- Bug 3536902 ; Test were added to a spec incorrectly when the spec and test
5396 -- do not match.
5397 -- Changed to add the test as an additional test and
5398 -- msg is written to migration log.
5399 IF c_check_spec%NOTFOUND THEN
5400 GMA_MIGRATION.gma_insert_message (
5401 p_run_id => p_migration_id,
5402 p_table_name => 'QC_RSLT_MST',
5403 p_DB_ERROR => '',
5404 p_param1 => ' Spec_Id = '||id_rec.spec_id,
5405 p_param2 => ' Test_ID = '||rslt_rec.qcassy_typ_id,
5406 p_param3 => ' Result_Id = '||rslt_rec.qc_result_id ,
5407 p_param4 => ' ',
5408 p_param5 => '',
5409 p_base_message => 'Result Test changed to additional test, '||
5410 'since the Spec and Test do not match',
5411 p_message_type => 'P',
5412 p_line_no => '1',
5413 p_position => '',
5414 p_message_token => 'CHANGED_TO_ADDITIONAL_TEST');
5415 -- COMMIT;
5416
5417 l_additional_test_ind := 'Y';
5418
5419 /* Evaluation only depends upon accept_anyway and if a result is entered */
5420 IF rslt_rec.accept_anyway = 1 THEN
5421 l_evaluation_ind := '0A';
5422 ELSIF ( rslt_rec.num_result IS NOT NULL OR
5423 rslt_rec.text_result IS NOT NULL ) THEN
5424 l_evaluation_ind := '2R';
5425 END IF;
5426 ELSE
5427 l_in_spec_ind := GMD_RESULTS_GRP.rslt_is_in_spec(
5428 id_rec.spec_id,
5429 rslt_rec.qcassy_typ_id,
5430 rslt_rec.num_result,
5431 rslt_rec.text_result);
5432
5433 /* Evaluation depends upon accept_anyway and
5434 if the result has been entered and is in spec */
5435 IF rslt_rec.accept_anyway = 1 THEN
5436 IF l_in_spec_ind = 'Y' THEN
5437 l_evaluation_ind := '0A';
5438 ELSE
5439 l_evaluation_ind := '1V';
5440 END IF;
5441 ELSIF (rslt_rec.num_result IS NOT NULL OR
5442 rslt_rec.text_result IS NOT NULL) THEN
5443 l_evaluation_ind := '2R';
5444 END IF;
5445 END IF; /* Test is not part of selected spec */
5446 CLOSE c_check_spec;
5447 ELSE /* Result was NOT entered against a spec */
5448 l_additional_test_ind := 'Y';
5449
5450 /* Evaluation only depends upon accept_anyway and if a result is entered */
5451 IF rslt_rec.accept_anyway = 1 THEN
5452 l_evaluation_ind := '0A';
5453 ELSIF (rslt_rec.num_result IS NOT NULL OR
5454 rslt_rec.text_result IS NOT NULL) THEN
5455 l_evaluation_ind := '2R';
5456 END IF;
5457
5458 END IF;
5459
5460 IF rslt_rec.result_date IS NULL THEN
5461 l_evaluation_ind := NULL;
5462 l_in_spec_ind := NULL;
5463 END IF;
5464
5465 /* M. Grosser 24-Sep-2002 Added cursor to set value of column seq in
5466 gmd_results in procedure Migrate_Results
5467 */
5468 /* Retrieve next sequence value */
5469 OPEN c_get_seq;
5470 FETCH c_get_seq into l_seq;
5471 CLOSE c_get_seq;
5472
5473 /* Insert record into new results table */
5474 INSERT INTO gmd_results
5475 (
5476 result_id,
5477 sample_id,
5478 test_id,
5479 seq,
5480 test_replicate_cnt,
5481 qc_lab_orgn_code,
5482 result_value_num,
5483 result_value_char,
5484 result_date,
5485 test_kit_item_id,
5486 test_kit_lot_no,
5487 test_kit_sublot_no,
5488 tester,
5489 tester_id,
5490 test_provider_code,
5491 assay_retest,
5492 text_code,
5493 delete_mark,
5494 creation_date,
5495 created_by,
5496 last_update_date,
5497 last_updated_by,
5498 last_update_login,
5499 attribute1,
5500 attribute2,
5501 attribute3,
5502 attribute4,
5503 attribute5,
5504 attribute6,
5505 attribute7,
5506 attribute8,
5507 attribute9,
5508 attribute10,
5509 attribute11,
5510 attribute12,
5511 attribute13,
5512 attribute14,
5513 attribute15,
5514 attribute16,
5515 attribute17,
5516 attribute18,
5517 attribute19,
5518 attribute20,
5519 attribute21,
5520 attribute22,
5521 attribute23,
5522 attribute24,
5523 attribute25,
5524 attribute26,
5525 attribute27,
5526 attribute28,
5527 attribute29,
5528 attribute30,
5529 attribute_category
5530 )
5531 VALUES
5532 (
5533 rslt_rec.qc_result_id,
5534 rslt_rec.sample_id,
5535 rslt_rec.qcassy_typ_id,
5536 l_seq,
5537 '1',
5538 l_qc_lab_orgn_code,
5539 rslt_rec.num_result,
5540 rslt_rec.text_result,
5541 rslt_rec.result_date,
5542 '',
5543 '',
5544 '',
5545 rslt_rec.assay_tester,
5546 '',
5547 rslt_rec.test_provider_code,
5548 rslt_rec.assay_retest,
5549 rslt_rec.text_code,
5550 rslt_rec.delete_mark,
5551 rslt_rec.creation_date,
5552 rslt_rec.created_by,
5553 rslt_rec.last_update_date,
5554 rslt_rec.last_updated_by,
5555 rslt_rec.last_update_login,
5556 rslt_rec.attribute1,
5557 rslt_rec.attribute2,
5558 rslt_rec.attribute3,
5559 rslt_rec.attribute4,
5560 rslt_rec.attribute5,
5561 rslt_rec.attribute6,
5562 rslt_rec.attribute7,
5563 rslt_rec.attribute8,
5564 rslt_rec.attribute9,
5565 rslt_rec.attribute10,
5566 rslt_rec.attribute11,
5567 rslt_rec.attribute12,
5568 rslt_rec.attribute13,
5569 rslt_rec.attribute14,
5570 rslt_rec.attribute15,
5571 rslt_rec.attribute16,
5572 rslt_rec.attribute17,
5573 rslt_rec.attribute18,
5574 rslt_rec.attribute19,
5575 rslt_rec.attribute20,
5576 rslt_rec.attribute21,
5577 rslt_rec.attribute22,
5578 rslt_rec.attribute23,
5579 rslt_rec.attribute24,
5580 rslt_rec.attribute25,
5581 rslt_rec.attribute26,
5582 rslt_rec.attribute27,
5583 rslt_rec.attribute28,
5584 rslt_rec.attribute29,
5585 rslt_rec.attribute30,
5586 rslt_rec.attribute_category
5587 );
5588
5589 /* Create record for acceptance against a particular spec */
5590 INSERT INTO gmd_spec_results
5591 (
5592 event_spec_disp_id,
5593 result_id,
5594 evaluation_ind,
5595 in_spec_ind,
5596 value_in_report_precision,
5597 additional_test_ind,
5598 delete_mark,
5599 creation_date,
5600 created_by,
5601 last_update_date,
5602 last_updated_by,
5603 last_update_login
5604 )
5605 VALUES
5606 (
5607 id_rec.event_spec_disp_id,
5608 rslt_rec.qc_result_id,
5609 l_evaluation_ind,
5610 l_in_spec_ind,
5611 rslt_rec.num_result,
5612 l_additional_test_ind,
5613 '0',
5614 rslt_rec.creation_date,
5615 rslt_rec.created_by,
5616 rslt_rec.last_update_date,
5617 rslt_rec.last_updated_by,
5618 rslt_rec.last_update_login
5619 );
5620 /* GMA_MIGRATION.gma_insert_message (
5621 p_run_id => p_migration_id,
5622 p_table_name => 'QC_RSLT_MST',
5623 p_DB_ERROR => '',
5624 p_param1 => '',
5625 p_param2 => '',
5626 p_param3 => '',
5627 p_param4 => '',
5628 p_param5 => '',
5629 p_message_token => 'aFTER insert into GMD_SPEC_RESULTS',
5630 p_message_type => 'P',
5631 p_line_no => '1',
5632 p_position => '',
5633 p_base_message => '');
5634 COMMIT; */
5635
5636 /* Set record status to migrated */
5637 UPDATE qc_rslt_mst
5638 SET migration_status = 'MO'
5639 WHERE qc_result_id = rslt_rec.qc_result_id;
5640
5641 -- COMMIT; -- Bug 4150468 - removed this commit after every record processed.
5642
5643 l_rec_count := l_rec_count + 1;
5644 l_commit_count := l_commit_count + 1;
5645
5646 IF (l_commit_count > 10000) THEN
5647 -- BEGIN - Bug 4150468 - After every 10000 records DON'T CLOSE AND REOPEN THE CURSOR.
5648 -- Commit instead!!
5649 -- CLOSE c_get_results;
5650 -- OPEN c_get_results;
5651 COMMIT;
5652 -- END
5653 l_commit_count := 0;
5654 END IF;
5655
5656 FETCH c_get_results into rslt_rec;
5657
5658 END LOOP; /* Number or records selected */
5659
5660
5661 CLOSE c_get_results;
5662
5663 COMMIT;
5664
5665
5666 GMA_MIGRATION.gma_insert_message (
5667 p_run_id => p_migration_id,
5668 p_table_name => 'QC_RSLT_MST',
5669 p_DB_ERROR => '',
5670 p_param1 => '',
5671 p_param2 => l_rec_count,
5672 p_param3 => '',
5673 p_param4 => '',
5674 p_param5 => '',
5675 p_message_token => 'GMA_MIGRATION_TABLE_SUCCESS_RW',
5676 p_message_type => 'P',
5677 p_line_no => '1',
5678 p_position => '',
5679 p_base_message => '');
5680
5681 EXCEPTION
5682 WHEN OTHERS THEN
5683 x_return_status := 'U';
5684 /* ROLLBACK TO SAVEPOINT Result_Rec; */
5685 GMA_MIGRATION.gma_insert_message (
5686 p_run_id => p_migration_id,
5687 p_table_name => 'QC_RSLT_MST',
5688 p_DB_ERROR => sqlerrm,
5689 p_param1 => '',
5690 p_param2 => '',
5691 p_param3 => '',
5692 p_param4 => '',
5693 p_param5 => '',
5694 p_message_token => 'GMA_MIGRATION_DB_ERROR',
5695 p_message_type => 'E',
5696 p_line_no => '1',
5697 p_position => '',
5698 p_base_message => 'Failed to migrate results due to '||sqlerrm);
5699
5700 GMA_MIGRATION.gma_insert_message (
5701 p_run_id => p_migration_id,
5702 p_table_name => 'QC_RSLT_MST',
5703 p_DB_ERROR => '',
5704 p_param1 => '',
5705 p_param2 => '',
5706 p_param3 => '',
5707 p_param4 => '',
5708 p_param5 => '',
5709 p_message_token => 'GMA_TABLE_MIGRATION_TABLE_FAIL',
5710 p_message_type => 'P',
5711 p_line_no => '1',
5712 p_position => '',
5713 p_base_message => '');
5714
5715
5716 END Migrate_Results;
5717
5718 /*===========================================================================
5719 -- PROCEDURE:
5720 -- Create_Sample_Results
5721 --
5722 -- DESCRIPTION:
5723 -- This PL/SQL procedure is used to create result records for a sample
5724 -- that did not have any results against it but there is a valid spec.
5725 --
5726 -- PARAMETERS:
5727 -- p_migration_id - id to use to right to migration log
5728 -- x_return_status - 'S'uccess, 'E'rror or 'U'known Error
5729 --
5730 -- SYNOPSIS:
5731 -- Create_Sample_Results;
5732 --
5733 -- HISTORY
5734 -- 29-Apr-2004 B.Stone Bug 3601780; Changed sample disposition to
5735 -- Complete for samples with results for all
5736 -- tests and all tests are evaluated.
5737 --=========================================================================== */
5738 PROCEDURE Create_Sample_Results (p_migration_id IN NUMBER,
5739 x_return_status OUT NOCOPY VARCHAR2)
5740 IS
5741
5742 /* ------------- LOCAL VARIABLES ------------------- */
5743 l_return_status VARCHAR2(4);
5744 l_event_spec_tab GMD_EVENT_SPEC_DISP%ROWTYPE;
5745 l_sample_spec_tab GMD_SAMPLE_SPEC_DISP%ROWTYPE;
5746 l_spec_results_tab GMD_API_PUB.gmd_spec_results_tab;
5747 l_results_tab GMD_API_PUB.gmd_results_tab;
5748 l_spec_vr_id NUMBER;
5749 l_temp NUMBER;
5750 l_date DATE;
5751 -- 3934121
5752 l_sample_id number;
5753 l_test_id number;
5754 l_cnt number;
5755 l_result_id number;
5756 l_result_date date;
5757 l_rep_cnt number;
5758
5759 CURSOR c_rep_cnt IS
5760 select r.sample_id, r.test_id, count(*) cnt
5761 from GMD_RESULTS r
5762 group by r.sample_id, r.test_id
5763 having count(*) > 1;
5764
5765 CURSOR c_rep_tests IS
5766 select r.result_id, r.result_date
5767 from GMD_RESULTS r
5768 where r.sample_id = l_sample_id
5769 and r.test_id = l_test_id
5770 order by decode ( r.result_date, NULL,
5771 to_date( '01-01-2040', 'DD-MM-YYYY' ),
5772 r.creation_date) asc;
5773 -- r.result_date) asc;
5774
5775
5776 /* ------------------ CURSORS ---------------------- */
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;
5786
5787 CURSOR c_check_validity_rule (psampling_event_id NUMBER) IS
5788 SELECT original_spec_vr_id
5789 FROM gmd_sampling_events
5790 WHERE sampling_event_id = psampling_event_id;
5791
5792 -- Bug 3601780
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
5802 ( SELECT /*+ INDEX(SR GMD_SPEC_RESULTS_PK) */
5803 1
5804 FROM gmd_spec_results sr
5805 WHERE SR.EVENT_SPEC_DISP_ID = esd.EVENT_SPEC_DISP_ID
5806 AND SR.EVALUATION_IND IS NULL );
5807
5808 CURSOR c_cnt_results (psample_id NUMBER) IS
5809 SELECT 1
5810 FROM gmd_results r,
5811 gmd_spec_results sr
5812 WHERE r.sample_id = psample_id
5813 AND sr.result_id = r.result_id
5814 AND sr.evaluation_ind is null ;
5815 -- End of Bug 3601780
5816
5817 BEGIN
5818 -- gmd_p_fs_context sets the formula security context
5819 --
5820 gmd_p_fs_context.set_additional_attr;
5821
5822 GMA_MIGRATION.gma_insert_message (
5823 p_run_id => p_migration_id,
5824 p_table_name => 'Create_Sample_Results',
5825 p_DB_ERROR => '',
5826 p_param1 => '',
5827 p_param2 => '',
5828 p_param3 => '',
5829 p_param4 => '',
5830 p_param5 => '',
5831 p_message_token => 'STARTED',
5832 p_message_type => 'I',
5833 p_line_no => '',
5834 p_position => NULL,
5835 p_base_message => '');
5836
5837 /* Check to see if any samples had been created that have no result
5838 records
5839 */
5840 OPEN c_get_no_results;
5841 FETCH c_get_no_results into nores_rec;
5842
5843 /* While there are samples with no results */
5844 WHILE c_get_no_results%FOUND LOOP
5845
5846 l_spec_vr_id := NULL;
5847
5848 OPEN c_check_validity_rule(nores_rec.sampling_event_id);
5849 FETCH c_check_validity_rule INTO l_spec_vr_id;
5850 CLOSE c_check_validity_rule;
5851
5852 /* If there is an applicable spec */
5853 IF l_spec_vr_id IS NOT NULL THEN
5854
5855 /* Create the results and all applicable rows */
5856 GMD_RESULTS_GRP.create_rslt_and_spec_rslt_rows (
5857 p_sample => nores_rec,
5858 p_migration => 'Y',
5859 x_event_spec_disp => l_event_spec_tab,
5860 x_sample_spec_disp => l_sample_spec_tab,
5861 x_results_tab => l_results_tab,
5862 x_spec_results_tab => l_spec_results_tab,
5863 x_return_status => l_return_status);
5864
5865 IF l_return_status = 'S' THEN
5866 COMMIT;
5867 ELSE
5868 ROLLBACK;
5869 GMA_MIGRATION.gma_insert_message (
5870 p_run_id => p_migration_id,
5871 p_table_name => 'GMD_SPEC_RESULTS',
5872 p_DB_ERROR => sqlerrm,
5873 p_param1 => 'l_spec_vr_id= '||l_spec_vr_id,
5874 p_param2 => 'nores_rec.sample_id= '||nores_rec.sample_id,
5875 p_param3 => 'nores_rec.lot_retest_ind= '||nores_rec.lot_retest_ind,
5876 p_param4 => 'nores_rec.sampling_event_id= '||nores_rec.sampling_event_id,
5877 p_param5 => '',
5878 p_message_token => 'GMA_MIGRATION_DB_ERROR',
5879 p_message_type => 'E',
5880 p_line_no => '1',
5881 p_position => '',
5882 p_base_message => 'Failed to migrate results due to '||sqlerrm);
5883
5884 END IF;
5885 END IF; /* If there is an applicable spec */
5886
5887 FETCH c_get_no_results into nores_rec;
5888
5889 END LOOP; /* Number or records selected */
5890
5891 CLOSE c_get_no_results;
5892
5893 -- 29-Apr-2004 B.Stone Bug 3601780; Changed sample disposition to
5894 -- Complete for samples with results for all
5895 -- tests and all tests are evaluated.
5896
5897 select sysdate into l_date from dual ;
5898
5899 GMA_MIGRATION.gma_insert_message (
5900 p_run_id => p_migration_id,
5901 p_table_name => 'Create_Sample_Results',
5902 p_DB_ERROR => '',
5903 p_param1 => '',
5904 p_param2 => '',
5905 p_param3 => '',
5906 p_param4 => '',
5907 p_param5 => '',
5908 p_message_token => 'STARTED - Checking for Completed Samples',
5909 p_message_type => 'I',
5910 p_line_no => '',
5911 p_position => NULL,
5912 p_base_message => '');
5913
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'
5923 where event_spec_disp_id = l_ip_samples.ip_event_spec ;
5924 update gmd_event_spec_disp
5925 set disposition = '3C'
5926 where event_spec_disp_id = l_ip_samples.ip_event_spec ;
5927 update gmd_sampling_events
5928 set disposition = '3C'
5929 where sampling_event_id = l_ip_samples.ip_sampling_event;
5930 COMMIT;
5931
5932 /* END IF; */
5933 /* CLOSE c_cnt_results; */
5934 END LOOP;
5935
5936 -- 3934121; Update test_replicate_cnt
5937 OPEN c_rep_cnt;
5938 FETCH c_rep_cnt into l_sample_id, l_test_id, l_cnt;
5939
5940 WHILE c_rep_cnt%FOUND LOOP
5941 OPEN c_rep_tests;
5942 FETCH c_rep_tests INTO l_result_id, l_result_date;
5943 l_rep_cnt := 0;
5944 WHILE c_rep_tests%FOUND LOOP
5945 l_rep_cnt := l_rep_cnt + 1;
5946 IF l_rep_cnt > 1 THEN
5947 UPDATE gmd_results r
5948 set test_replicate_cnt = l_rep_cnt
5949 where result_id = l_result_id;
5950 END IF;
5951 FETCH c_rep_tests INTO l_result_id, l_result_date;
5952 END LOOP; -- c_rep_tests
5953 CLOSE c_rep_tests;
5954 FETCH c_rep_cnt into l_sample_id, l_test_id, l_cnt;
5955 END LOOP; -- c_rep_cnt
5956 CLOSE c_rep_cnt;
5957
5958 commit;
5959
5960 GMA_MIGRATION.gma_insert_message (
5961 p_run_id => p_migration_id,
5962 p_table_name => 'Create_Sample_Results',
5963 p_DB_ERROR => '',
5964 p_param1 => '',
5965 p_param2 => '',
5966 p_param3 => '',
5967 p_param4 => '',
5968 p_param5 => '',
5969 p_message_token => 'ENDED - Checking for Completed Samples',
5970 p_message_type => 'I',
5971 p_line_no => '',
5972 p_position => NULL,
5973 p_base_message => '');
5974
5975 EXCEPTION
5976 WHEN OTHERS THEN
5977 x_return_status := 'U';
5978 GMA_MIGRATION.gma_insert_message (
5979 p_run_id => p_migration_id,
5980 p_table_name => 'SAMPLE_RESULTS',
5981 p_DB_ERROR => sqlerrm,
5982 p_param1 => '',
5983 p_param2 => '',
5984 p_param3 => '',
5985 p_param4 => '',
5986 p_param5 => '',
5987 p_message_token => 'GMA_MIGRATION_DB_ERROR',
5988 p_message_type => 'E',
5989 p_line_no => '1',
5990 p_position => '',
5991 p_base_message => '');
5992
5993 END Create_Sample_Results;
5994
5995 PROCEDURE Clear_end_date (p_migration_id IN NUMBER
5996 , x_return_status OUT NOCOPY VARCHAR2)
5997 IS
5998
5999 l_max_date date;
6000 l_return_status VARCHAR2(4);
6001
6002 BEGIN
6003
6004 l_max_date := trunc(fnd_date.canonical_to_date(nvl(fnd_profile.value('SY$MAX_DATE'),'2010/12/31') ));
6005
6006 GMA_MIGRATION.gma_insert_message (
6007 p_run_id => p_migration_id,
6008 p_table_name => 'GMD_WIP_SPEC_VRS',
6009 p_DB_ERROR => '',
6010 p_param1 => 'Nulling out end date on wip vrs ',
6011 p_param2 => 'Where end date is SY$MAX_DATE ',
6012 p_param3 => '',
6013 p_param4 => '',
6014 p_param5 => '',
6015 p_message_token => '',
6016 p_message_type => 'P',
6017 p_line_no => '',
6018 p_position => '',
6019 p_base_message => '');
6020
6021 update gmd_wip_spec_vrs
6022 set end_date = NULL
6023 where trunc(end_date + 1) >= l_max_date
6024 and SPEC_VR_STATUS = 700;
6025
6026 GMA_MIGRATION.gma_insert_message (
6027 p_run_id => p_migration_id,
6028 p_table_name => 'GMD_INVENTORY_SPEC_VRS',
6029 p_DB_ERROR => '',
6030 p_param1 => 'Nulling out end date on Inventory vrs ',
6031 p_param2 => 'Where end date is SY$MAX_DATE ',
6032 p_param3 => '',
6033 p_param4 => '',
6034 p_param5 => '',
6035 p_message_token => '',
6036 p_message_type => 'P',
6037 p_line_no => '',
6038 p_position => '',
6039 p_base_message => '');
6040
6041
6042 update gmd_inventory_spec_vrs
6043 set end_date = NULL
6044 where trunc(end_date + 1) >= l_max_date
6045 and SPEC_VR_STATUS = 700;
6046
6047 GMA_MIGRATION.gma_insert_message (
6048 p_run_id => p_migration_id,
6049 p_table_name => 'GMD_CUSTOMER_SPEC_VRS',
6050 p_DB_ERROR => '',
6051 p_param1 => 'Nulling out end date on customer vrs ',
6052 p_param2 => 'Where end date is SY$MAX_DATE ',
6053 p_param3 => '',
6054 p_param4 => '',
6055 p_param5 => '',
6056 p_message_token => '',
6057 p_message_type => 'P',
6058 p_line_no => '',
6059 p_position => '',
6060 p_base_message => '');
6061
6062 update gmd_customer_spec_vrs
6063 set end_date = NULL
6064 where trunc(end_date + 1) >= l_max_date
6065 and SPEC_VR_STATUS = 700;
6066
6067 GMA_MIGRATION.gma_insert_message (
6068 p_run_id => p_migration_id,
6069 p_table_name => 'GMD_SUPPLIER_SPEC_VRS',
6070 p_DB_ERROR => '',
6071 p_param1 => 'Nulling out end date on supplier vrs ',
6072 p_param2 => 'Where end date is SY$MAX_DATE ',
6073 p_param3 => '',
6074 p_param4 => '',
6075 p_param5 => '',
6076 p_message_token => '',
6077 p_message_type => 'P',
6078 p_line_no => '',
6079 p_position => '',
6080 p_base_message => '');
6081
6082 update gmd_supplier_spec_vrs
6083 set end_date = NULL
6084 where trunc(end_date + 1) >= l_max_date
6085 and SPEC_VR_STATUS = 700;
6086
6087 x_return_status := 'S';
6088
6089 EXCEPTION
6090 WHEN OTHERS THEN
6091 x_return_status := 'U';
6092 GMA_MIGRATION.gma_insert_message (
6093 p_run_id => p_migration_id,
6094 p_table_name => 'GMD_***_SPEC_VRS',
6095 p_DB_ERROR => sqlerrm,
6096 p_param1 => '',
6097 p_param2 => '',
6098 p_param3 => '',
6099 p_param4 => '',
6100 p_param5 => '',
6101 p_message_token => 'GMA_MIGRATION_DB_ERROR',
6102 p_message_type => 'E',
6103 p_line_no => '1',
6104 p_position => '',
6105 p_base_message => '');
6106
6107
6108 END;
6109
6110
6111
6112 END GMD_QC_MIGRATE_TO_1151J;
6113