[Home] [Help]
PACKAGE BODY: APPS.GMD_PROC_PARAMS_MIGR
Source
1 package body GMD_PROC_PARAMS_MIGR as
2 /* $Header: GMDPROCB.pls 120.1 2005/10/05 06:51:15 txdaniel noship $ */
3
4 P_run_id NUMBER;
5 P_line_no NUMBER DEFAULT 0;
6
7 PROCEDURE check_process_parameter IS
8 CURSOR Cur_check_param1 IS
9 SELECT 1
10 FROM sys.dual
11 WHERE EXISTS (SELECT 1
12 FROM GMD_OPERATION_RESOURCES
13 WHERE process_parameter_1 IS NOT NULL
14 UNION
15 SELECT 1
16 FROM GMD_RECIPE_ORGN_RESOURCES
17 WHERE process_parameter_1 IS NOT NULL
18 UNION
19 SELECT 1
20 FROM GME_BATCH_STEP_RESOURCES
21 WHERE process_parameter_1 IS NOT NULL)
22 AND NOT EXISTS (SELECT 1
23 FROM GMP_PROCESS_PARAMETERS_B
24 WHERE parameter_id = 1);
25
26 CURSOR Cur_check_param2 IS
27 SELECT 1
28 FROM sys.dual
29 WHERE EXISTS (SELECT 1
30 FROM GMD_OPERATION_RESOURCES
31 WHERE process_parameter_2 IS NOT NULL
32 UNION
33 SELECT 1
34 FROM GMD_RECIPE_ORGN_RESOURCES
35 WHERE process_parameter_2 IS NOT NULL
36 UNION
37 SELECT 1
38 FROM GME_BATCH_STEP_RESOURCES
39 WHERE process_parameter_2 IS NOT NULL)
40 AND NOT EXISTS (SELECT 1
41 FROM GMP_PROCESS_PARAMETERS_B
42 WHERE parameter_id = 2);
43
44 CURSOR Cur_check_param3 IS
45 SELECT 1
46 FROM sys.dual
47 WHERE EXISTS (SELECT 1
48 FROM GMD_OPERATION_RESOURCES
49 WHERE process_parameter_3 IS NOT NULL
50 UNION
51 SELECT 1
52 FROM GMD_RECIPE_ORGN_RESOURCES
53 WHERE process_parameter_3 IS NOT NULL
54 UNION
55 SELECT 1
56 FROM GME_BATCH_STEP_RESOURCES
57 WHERE process_parameter_3 IS NOT NULL)
58 AND NOT EXISTS (SELECT 1
59 FROM GMP_PROCESS_PARAMETERS_B
60 WHERE parameter_id = 3);
61
62 CURSOR Cur_check_param4 IS
63 SELECT 1
64 FROM sys.dual
65 WHERE EXISTS (SELECT 1
66 FROM GMD_OPERATION_RESOURCES
67 WHERE process_parameter_4 IS NOT NULL
68 UNION
69 SELECT 1
70 FROM GMD_RECIPE_ORGN_RESOURCES
71 WHERE process_parameter_4 IS NOT NULL
72 UNION
73 SELECT 1
74 FROM GME_BATCH_STEP_RESOURCES
75 WHERE process_parameter_4 IS NOT NULL)
76 AND NOT EXISTS (SELECT 1
77 FROM GMP_PROCESS_PARAMETERS_B
78 WHERE parameter_id = 4);
79
80 CURSOR Cur_check_param5 IS
81 SELECT 1
82 FROM sys.dual
83 WHERE EXISTS (SELECT 1
84 FROM GMD_OPERATION_RESOURCES
85 WHERE process_parameter_5 IS NOT NULL
86 UNION
87 SELECT 1
88 FROM GMD_RECIPE_ORGN_RESOURCES
89 WHERE process_parameter_5 IS NOT NULL
90 UNION
91 SELECT 1
92 FROM GME_BATCH_STEP_RESOURCES
93 WHERE process_parameter_5 IS NOT NULL)
94 AND NOT EXISTS (SELECT 1
95 FROM GMP_PROCESS_PARAMETERS_B
96 WHERE parameter_id = 5);
97
98 X_temp NUMBER;
99 X_row_id VARCHAR2(100) DEFAULT NULL;
100 BEGIN
101 /* Open the cursor to check if process parameter1 has to be migrated */
102 OPEN Cur_check_param1;
103 FETCH Cur_check_param1 INTO X_temp;
104 IF Cur_check_param1%FOUND THEN
105 GMP_PROCESS_PARAMETERS_PKG.INSERT_ROW
106 (X_ROWID => X_row_id,
107 X_PARAMETER_ID => 1,
108 X_ATTRIBUTE21 => NULL,
109 X_ATTRIBUTE22 => NULL,
110 X_ATTRIBUTE23 => NULL,
111 X_ATTRIBUTE24 => NULL,
112 X_ATTRIBUTE25 => NULL,
113 X_ATTRIBUTE26 => NULL,
114 X_ATTRIBUTE27 => NULL,
115 X_ATTRIBUTE28 => NULL,
116 X_ATTRIBUTE29 => NULL,
117 X_ATTRIBUTE30 => NULL,
118 X_ATTRIBUTE_CATEGORY => NULL,
119 X_ATTRIBUTE1 => NULL,
120 X_ATTRIBUTE2 => NULL,
121 X_ATTRIBUTE3 => NULL,
122 X_ATTRIBUTE4 => NULL,
123 X_ATTRIBUTE5 => NULL,
124 X_ATTRIBUTE6 => NULL,
125 X_ATTRIBUTE7 => NULL,
126 X_ATTRIBUTE8 => NULL,
127 X_ATTRIBUTE9 => NULL,
128 X_ATTRIBUTE10 => NULL,
129 X_ATTRIBUTE11 => NULL,
130 X_ATTRIBUTE12 => NULL,
131 X_ATTRIBUTE13 => NULL,
132 X_ATTRIBUTE14 => NULL,
133 X_MAXIMUM_VALUE => NULL,
134 X_DELETE_MARK => 0,
135 X_TEXT_CODE => NULL,
136 X_ATTRIBUTE15 => NULL,
137 X_ATTRIBUTE16 => NULL,
138 X_ATTRIBUTE17 => NULL,
139 X_ATTRIBUTE18 => NULL,
140 X_ATTRIBUTE19 => NULL,
141 X_ATTRIBUTE20 => NULL,
142 X_PARAMETER_TYPE => 1,
143 X_MINIMUM_VALUE => NULL,
144 X_PARAMETER_NAME => 'PROCESS_PARAMETER_1',
145 X_UNITS => NULL,
146 X_PARAMETER_DESCRIPTION => 'Process Parameter 1',
147 X_CREATION_DATE => SYSDATE,
148 X_CREATED_BY => 0,
149 X_LAST_UPDATE_DATE => SYSDATE,
150 X_LAST_UPDATED_BY => 0,
151 X_LAST_UPDATE_LOGIN => NULL);
152 END IF;
153 CLOSE Cur_check_param1;
154
155 OPEN Cur_check_param2;
156 FETCH Cur_check_param2 INTO X_temp;
157 IF Cur_check_param2%FOUND THEN
158 GMP_PROCESS_PARAMETERS_PKG.INSERT_ROW
159 (X_ROWID => X_row_id,
160 X_PARAMETER_ID => 2,
161 X_ATTRIBUTE21 => NULL,
162 X_ATTRIBUTE22 => NULL,
163 X_ATTRIBUTE23 => NULL,
164 X_ATTRIBUTE24 => NULL,
165 X_ATTRIBUTE25 => NULL,
166 X_ATTRIBUTE26 => NULL,
167 X_ATTRIBUTE27 => NULL,
168 X_ATTRIBUTE28 => NULL,
169 X_ATTRIBUTE29 => NULL,
170 X_ATTRIBUTE30 => NULL,
171 X_ATTRIBUTE_CATEGORY => NULL,
172 X_ATTRIBUTE1 => NULL,
173 X_ATTRIBUTE2 => NULL,
174 X_ATTRIBUTE3 => NULL,
175 X_ATTRIBUTE4 => NULL,
176 X_ATTRIBUTE5 => NULL,
177 X_ATTRIBUTE6 => NULL,
178 X_ATTRIBUTE7 => NULL,
179 X_ATTRIBUTE8 => NULL,
180 X_ATTRIBUTE9 => NULL,
181 X_ATTRIBUTE10 => NULL,
182 X_ATTRIBUTE11 => NULL,
183 X_ATTRIBUTE12 => NULL,
184 X_ATTRIBUTE13 => NULL,
185 X_ATTRIBUTE14 => NULL,
186 X_MAXIMUM_VALUE => NULL,
187 X_DELETE_MARK => 0,
188 X_TEXT_CODE => NULL,
189 X_ATTRIBUTE15 => NULL,
190 X_ATTRIBUTE16 => NULL,
191 X_ATTRIBUTE17 => NULL,
192 X_ATTRIBUTE18 => NULL,
193 X_ATTRIBUTE19 => NULL,
194 X_ATTRIBUTE20 => NULL,
195 X_PARAMETER_TYPE => 1,
196 X_MINIMUM_VALUE => NULL,
197 X_PARAMETER_NAME => 'PROCESS_PARAMETER_2',
198 X_UNITS => NULL,
199 X_PARAMETER_DESCRIPTION => 'Process Parameter 2',
200 X_CREATION_DATE => SYSDATE,
201 X_CREATED_BY => 0,
202 X_LAST_UPDATE_DATE => SYSDATE,
203 X_LAST_UPDATED_BY => 0,
204 X_LAST_UPDATE_LOGIN => NULL);
205 END IF;
206 CLOSE Cur_check_param2;
207
208 OPEN Cur_check_param3;
209 FETCH Cur_check_param3 INTO X_temp;
210 IF Cur_check_param3%FOUND THEN
211 GMP_PROCESS_PARAMETERS_PKG.INSERT_ROW
212 (X_ROWID => X_row_id,
213 X_PARAMETER_ID => 3,
214 X_ATTRIBUTE21 => NULL,
215 X_ATTRIBUTE22 => NULL,
216 X_ATTRIBUTE23 => NULL,
217 X_ATTRIBUTE24 => NULL,
218 X_ATTRIBUTE25 => NULL,
219 X_ATTRIBUTE26 => NULL,
220 X_ATTRIBUTE27 => NULL,
221 X_ATTRIBUTE28 => NULL,
222 X_ATTRIBUTE29 => NULL,
223 X_ATTRIBUTE30 => NULL,
224 X_ATTRIBUTE_CATEGORY => NULL,
225 X_ATTRIBUTE1 => NULL,
226 X_ATTRIBUTE2 => NULL,
227 X_ATTRIBUTE3 => NULL,
228 X_ATTRIBUTE4 => NULL,
229 X_ATTRIBUTE5 => NULL,
230 X_ATTRIBUTE6 => NULL,
231 X_ATTRIBUTE7 => NULL,
232 X_ATTRIBUTE8 => NULL,
233 X_ATTRIBUTE9 => NULL,
234 X_ATTRIBUTE10 => NULL,
235 X_ATTRIBUTE11 => NULL,
236 X_ATTRIBUTE12 => NULL,
237 X_ATTRIBUTE13 => NULL,
238 X_ATTRIBUTE14 => NULL,
242 X_ATTRIBUTE15 => NULL,
239 X_MAXIMUM_VALUE => NULL,
240 X_DELETE_MARK => 0,
241 X_TEXT_CODE => NULL,
243 X_ATTRIBUTE16 => NULL,
244 X_ATTRIBUTE17 => NULL,
245 X_ATTRIBUTE18 => NULL,
246 X_ATTRIBUTE19 => NULL,
247 X_ATTRIBUTE20 => NULL,
248 X_PARAMETER_TYPE => 1,
249 X_MINIMUM_VALUE => NULL,
250 X_PARAMETER_NAME => 'PROCESS_PARAMETER_3',
251 X_UNITS => NULL,
252 X_PARAMETER_DESCRIPTION => 'Process Parameter 3',
253 X_CREATION_DATE => SYSDATE,
254 X_CREATED_BY => 0,
255 X_LAST_UPDATE_DATE => SYSDATE,
256 X_LAST_UPDATED_BY => 0,
257 X_LAST_UPDATE_LOGIN => NULL);
258 END IF;
259 CLOSE Cur_check_param3;
260
261 OPEN Cur_check_param4;
262 FETCH Cur_check_param4 INTO X_temp;
263 IF Cur_check_param4%FOUND THEN
264 GMP_PROCESS_PARAMETERS_PKG.INSERT_ROW
265 (X_ROWID => X_row_id,
266 X_PARAMETER_ID => 4,
267 X_ATTRIBUTE21 => NULL,
268 X_ATTRIBUTE22 => NULL,
269 X_ATTRIBUTE23 => NULL,
270 X_ATTRIBUTE24 => NULL,
271 X_ATTRIBUTE25 => NULL,
272 X_ATTRIBUTE26 => NULL,
273 X_ATTRIBUTE27 => NULL,
274 X_ATTRIBUTE28 => NULL,
275 X_ATTRIBUTE29 => NULL,
276 X_ATTRIBUTE30 => NULL,
277 X_ATTRIBUTE_CATEGORY => NULL,
278 X_ATTRIBUTE1 => NULL,
279 X_ATTRIBUTE2 => NULL,
280 X_ATTRIBUTE3 => NULL,
281 X_ATTRIBUTE4 => NULL,
282 X_ATTRIBUTE5 => NULL,
283 X_ATTRIBUTE6 => NULL,
284 X_ATTRIBUTE7 => NULL,
285 X_ATTRIBUTE8 => NULL,
286 X_ATTRIBUTE9 => NULL,
287 X_ATTRIBUTE10 => NULL,
288 X_ATTRIBUTE11 => NULL,
289 X_ATTRIBUTE12 => NULL,
290 X_ATTRIBUTE13 => NULL,
291 X_ATTRIBUTE14 => NULL,
292 X_MAXIMUM_VALUE => NULL,
293 X_DELETE_MARK => 0,
294 X_TEXT_CODE => NULL,
295 X_ATTRIBUTE15 => NULL,
296 X_ATTRIBUTE16 => NULL,
297 X_ATTRIBUTE17 => NULL,
298 X_ATTRIBUTE18 => NULL,
299 X_ATTRIBUTE19 => NULL,
300 X_ATTRIBUTE20 => NULL,
301 X_PARAMETER_TYPE => 1,
302 X_MINIMUM_VALUE => NULL,
303 X_PARAMETER_NAME => 'PROCESS_PARAMETER_4',
304 X_UNITS => NULL,
305 X_PARAMETER_DESCRIPTION => 'Process Parameter 4',
306 X_CREATION_DATE => SYSDATE,
307 X_CREATED_BY => 0,
308 X_LAST_UPDATE_DATE => SYSDATE,
309 X_LAST_UPDATED_BY => 0,
310 X_LAST_UPDATE_LOGIN => NULL);
311 END IF;
312 CLOSE Cur_check_param4;
313
314 OPEN Cur_check_param5;
315 FETCH Cur_check_param5 INTO X_temp;
316 IF Cur_check_param5%FOUND THEN
317 GMP_PROCESS_PARAMETERS_PKG.INSERT_ROW
318 (X_ROWID => X_row_id,
319 X_PARAMETER_ID => 5,
320 X_ATTRIBUTE21 => NULL,
321 X_ATTRIBUTE22 => NULL,
322 X_ATTRIBUTE23 => NULL,
323 X_ATTRIBUTE24 => NULL,
324 X_ATTRIBUTE25 => NULL,
325 X_ATTRIBUTE26 => NULL,
326 X_ATTRIBUTE27 => NULL,
327 X_ATTRIBUTE28 => NULL,
328 X_ATTRIBUTE29 => NULL,
329 X_ATTRIBUTE30 => NULL,
330 X_ATTRIBUTE_CATEGORY => NULL,
331 X_ATTRIBUTE1 => NULL,
332 X_ATTRIBUTE2 => NULL,
333 X_ATTRIBUTE3 => NULL,
334 X_ATTRIBUTE4 => NULL,
335 X_ATTRIBUTE5 => NULL,
336 X_ATTRIBUTE6 => NULL,
337 X_ATTRIBUTE7 => NULL,
338 X_ATTRIBUTE8 => NULL,
339 X_ATTRIBUTE9 => NULL,
340 X_ATTRIBUTE10 => NULL,
341 X_ATTRIBUTE11 => NULL,
342 X_ATTRIBUTE12 => NULL,
346 X_DELETE_MARK => 0,
343 X_ATTRIBUTE13 => NULL,
344 X_ATTRIBUTE14 => NULL,
345 X_MAXIMUM_VALUE => NULL,
347 X_TEXT_CODE => NULL,
348 X_ATTRIBUTE15 => NULL,
349 X_ATTRIBUTE16 => NULL,
350 X_ATTRIBUTE17 => NULL,
351 X_ATTRIBUTE18 => NULL,
352 X_ATTRIBUTE19 => NULL,
353 X_ATTRIBUTE20 => NULL,
354 X_PARAMETER_TYPE => 1,
355 X_MINIMUM_VALUE => NULL,
356 X_PARAMETER_NAME => 'PROCESS_PARAMETER_5',
357 X_UNITS => NULL,
361 X_LAST_UPDATE_DATE => SYSDATE,
358 X_PARAMETER_DESCRIPTION => 'Process Parameter 5',
359 X_CREATION_DATE => SYSDATE,
360 X_CREATED_BY => 0,
362 X_LAST_UPDATED_BY => 0,
363 X_LAST_UPDATE_LOGIN => NULL);
364 END IF;
365 CLOSE Cur_check_param5;
366 EXCEPTION
367 WHEN OTHERS THEN
368 P_line_no := P_line_no + 1;
369 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
370 ,p_table_name => 'GMP_PROCESS_PARAMETERS'
371 ,p_db_error => sqlerrm
372 ,p_param1 => NULL
373 ,p_param2 => NULL
374 ,p_param3 => NULL
375 ,p_param4 => NULL
376 ,p_param5 => NULL
377 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
378 ,p_message_type => 'D'
379 ,p_line_no => P_line_no
380 ,p_position=> 1
381 ,p_base_message=> NULL);
382 END check_process_parameter;
383
384 PROCEDURE oprn_process_parameter IS
385 CURSOR Cur_oprn_parameters IS
386 SELECT Process_parameter_1,Process_parameter_2,Process_parameter_3,
387 Process_parameter_4,Process_parameter_5,oprn_line_id,resources
388 FROM gmd_operation_resources;
389 BEGIN
390 FOR l_rec IN Cur_oprn_parameters LOOP
391 /* Insert process parameter 1 value into GMD_OPRN_PROCESS_PARAMETERS table */
392 IF l_rec.process_parameter_1 IS NOT NULL THEN
393 INSERT INTO gmd_oprn_process_parameters
394 (oprn_line_id,
395 resources,
396 parameter_id,
397 target_value,
398 creation_date,
399 created_by,
400 last_update_date,
401 last_updated_by,
402 last_update_login)
403 SELECT l_rec.oprn_line_id,
404 l_rec.resources,
405 1,
406 l_rec.process_parameter_1,
407 SYSDATE,
408 0,
409 SYSDATE,
410 0,
411 0
412 FROM DUAL
413 WHERE NOT EXISTS (SELECT 1
414 FROM gmd_oprn_process_parameters
415 WHERE oprn_line_id = l_rec.oprn_line_id
416 AND resources = l_rec.resources
417 AND parameter_id = 1);
418 END IF; /*IF l_rec.process_parameter_1 IS NOT NULL THEN*/
419
420 IF l_rec.process_parameter_2 IS NOT NULL THEN
421 INSERT INTO gmd_oprn_process_parameters
422 (oprn_line_id,
423 resources,
424 parameter_id,
425 target_value,
426 creation_date,
427 created_by,
428 last_update_date,
429 last_updated_by,
430 last_update_login)
431 SELECT l_rec.oprn_line_id,
432 l_rec.resources,
433 2,
434 l_rec.process_parameter_2,
435 SYSDATE,
436 0,
437 SYSDATE,
438 0,
439 0
440 FROM DUAL
441 WHERE NOT EXISTS (SELECT 1
442 FROM gmd_oprn_process_parameters
443 WHERE oprn_line_id = l_rec.oprn_line_id
444 AND resources = l_rec.resources
445 AND parameter_id = 2);
446 END IF; /*IF l_rec.process_parameter_2 IS NOT NULL THEN*/
447
448 IF l_rec.process_parameter_3 IS NOT NULL THEN
449 INSERT INTO gmd_oprn_process_parameters
450 (oprn_line_id,
451 resources,
452 parameter_id,
453 target_value,
454 creation_date,
455 created_by,
456 last_update_date,
457 last_updated_by,
458 last_update_login)
459 SELECT l_rec.oprn_line_id,
460 l_rec.resources,
461 3,
462 l_rec.process_parameter_3,
463 SYSDATE,
464 0,
465 SYSDATE,
466 0,
467 0
468 FROM DUAL
469 WHERE NOT EXISTS (SELECT 1
470 FROM gmd_oprn_process_parameters
471 WHERE oprn_line_id = l_rec.oprn_line_id
472 AND resources = l_rec.resources
473 AND parameter_id = 3);
474 END IF; /*IF l_rec.process_parameter_3 IS NOT NULL THEN*/
475
476 IF l_rec.process_parameter_4 IS NOT NULL THEN
477 INSERT INTO gmd_oprn_process_parameters
478 (oprn_line_id,
479 resources,
480 parameter_id,
481 target_value,
482 creation_date,
483 created_by,
484 last_update_date,
485 last_updated_by,
486 last_update_login)
487 SELECT l_rec.oprn_line_id,
488 l_rec.resources,
489 4,
490 l_rec.process_parameter_4,
491 SYSDATE,
492 0,
493 SYSDATE,
494 0,
495 0
496 FROM DUAL
497 WHERE NOT EXISTS (SELECT 1
498 FROM gmd_oprn_process_parameters
499 WHERE oprn_line_id = l_rec.oprn_line_id
500 AND resources = l_rec.resources
501 AND parameter_id = 4);
502 END IF; /*IF l_rec.process_parameter_4 IS NOT NULL THEN*/
503
504 IF l_rec.process_parameter_5 IS NOT NULL THEN
508 resources,
505 /*Check if the row for the resource already exists if it does not then*/
506 INSERT INTO gmd_oprn_process_parameters
507 (oprn_line_id,
509 parameter_id,
510 target_value,
511 creation_date,
512 created_by,
513 last_update_date,
514 last_updated_by,
515 last_update_login)
519 l_rec.process_parameter_5,
516 SELECT l_rec.oprn_line_id,
517 l_rec.resources,
518 5,
520 SYSDATE,
521 0,
522 SYSDATE,
523 0,
524 0
525 FROM DUAL
526 WHERE NOT EXISTS (SELECT 1
527 FROM gmd_oprn_process_parameters
528 WHERE oprn_line_id = l_rec.oprn_line_id
529 AND resources = l_rec.resources
530 AND parameter_id = 5);
531 END IF; /*IF l_rec.process_parameter_5 IS NOT NULL THEN*/
532 END LOOP;
533 EXCEPTION
534 WHEN OTHERS THEN
535 P_line_no := P_line_no + 1;
536 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
537 ,p_table_name => 'GMD_OPRN_PROCESS_PARAMETERS'
538 ,p_db_error => sqlerrm
539 ,p_param1 => NULL
540 ,p_param2 => NULL
541 ,p_param3 => NULL
542 ,p_param4 => NULL
543 ,p_param5 => NULL
544 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
545 ,p_message_type => 'D'
546 ,p_line_no => P_line_no
547 ,p_position=> 1
548 ,p_base_message=> NULL);
549 END oprn_process_parameter;
550
551 PROCEDURE recipe_process_parameter IS
552 CURSOR Cur_recipe_parameters IS
553 SELECT Process_parameter_1,Process_parameter_2,Process_parameter_3,
554 Process_parameter_4,Process_parameter_5,oprn_line_id,resources,
555 recipe_id,routingstep_id,orgn_code
556 FROM gmd_recipe_orgn_resources;
557 BEGIN
558 FOR l_rec IN Cur_recipe_parameters LOOP
559 /* Insert process parameter 1 value into GMD_RECIPE_PROCESS_PARAMETERS table */
560 IF l_rec.process_parameter_1 IS NOT NULL THEN
561 /*Check if the row for the resource already exists if it does not then*/
562 INSERT INTO gmd_recipe_process_parameters
563 (oprn_line_id,
564 resources,
565 recipe_id,
566 routingstep_id,
567 orgn_code,
568 parameter_id,
569 target_value,
570 creation_date,
571 created_by,
572 last_update_date,
573 last_updated_by,
574 last_update_login)
575 SELECT
576 l_rec.oprn_line_id,
577 l_rec.resources,
578 l_rec.recipe_id,
579 l_rec.routingstep_id,
580 l_rec.orgn_code,
581 1,
582 l_rec.process_parameter_1,
583 SYSDATE,
584 0,
585 SYSDATE,
586 0,
587 0
588 FROM DUAL
589 WHERE NOT EXISTS (SELECT 1
590 FROM gmd_recipe_process_parameters
591 WHERE oprn_line_id = l_rec.oprn_line_id
592 AND resources = l_rec.resources
593 AND orgn_code = l_rec.orgn_code
594 AND recipe_id = l_rec.recipe_id
595 AND routingstep_id = l_rec.routingstep_id
596 AND parameter_id = 1);
597 END IF; /*IF l_rec.process_parameter_1 IS NOT NULL THEN*/
598
602 (oprn_line_id,
599 IF l_rec.process_parameter_2 IS NOT NULL THEN
600 /*Check if the row for the resource already exists if it does not then*/
601 INSERT INTO gmd_recipe_process_parameters
603 resources,
604 recipe_id,
605 routingstep_id,
606 orgn_code,
607 parameter_id,
608 target_value,
609 creation_date,
610 created_by,
611 last_update_date,
612 last_updated_by,
613 last_update_login)
614 SELECT
615 l_rec.oprn_line_id,
616 l_rec.resources,
617 l_rec.recipe_id,
618 l_rec.routingstep_id,
619 l_rec.orgn_code,
620 2,
621 l_rec.process_parameter_2,
622 SYSDATE,
623 0,
624 SYSDATE,
625 0,
626 0
627 FROM DUAL
628 WHERE NOT EXISTS (SELECT 1
629 FROM gmd_recipe_process_parameters
630 WHERE oprn_line_id = l_rec.oprn_line_id
631 AND resources = l_rec.resources
632 AND orgn_code = l_rec.orgn_code
633 AND recipe_id = l_rec.recipe_id
634 AND routingstep_id = l_rec.routingstep_id
635 AND parameter_id = 2);
636 END IF; /*IF l_rec.process_parameter_2 IS NOT NULL THEN*/
637
638 IF l_rec.process_parameter_3 IS NOT NULL THEN
639 /*Check if the row for the resource already exists if it does not then*/
640 INSERT INTO gmd_recipe_process_parameters
641 (oprn_line_id,
642 resources,
643 recipe_id,
644 routingstep_id,
645 orgn_code,
646 parameter_id,
647 target_value,
648 creation_date,
649 created_by,
650 last_update_date,
651 last_updated_by,
652 last_update_login)
653 SELECT
654 l_rec.oprn_line_id,
655 l_rec.resources,
656 l_rec.recipe_id,
657 l_rec.routingstep_id,
658 l_rec.orgn_code,
659 3,
660 l_rec.process_parameter_3,
661 SYSDATE,
662 0,
663 SYSDATE,
664 0,
665 0
666 FROM DUAL
667 WHERE NOT EXISTS (SELECT 1
668 FROM gmd_recipe_process_parameters
669 WHERE oprn_line_id = l_rec.oprn_line_id
670 AND resources = l_rec.resources
671 AND orgn_code = l_rec.orgn_code
672 AND recipe_id = l_rec.recipe_id
673 AND routingstep_id = l_rec.routingstep_id
674 AND parameter_id = 3);
675 END IF; /*IF l_rec.process_parameter_3 IS NOT NULL THEN*/
676
677 IF l_rec.process_parameter_4 IS NOT NULL THEN
678 /*Check if the row for the resource already exists if it does not then*/
679 INSERT INTO gmd_recipe_process_parameters
680 (oprn_line_id,
681 resources,
682 recipe_id,
683 routingstep_id,
684 orgn_code,
685 parameter_id,
686 target_value,
687 creation_date,
688 created_by,
689 last_update_date,
690 last_updated_by,
691 last_update_login)
692 SELECT
693 l_rec.oprn_line_id,
694 l_rec.resources,
695 l_rec.recipe_id,
696 l_rec.routingstep_id,
697 l_rec.orgn_code,
698 4,
699 l_rec.process_parameter_4,
700 SYSDATE,
701 0,
702 SYSDATE,
703 0,
704 0
705 FROM DUAL
706 WHERE NOT EXISTS (SELECT 1
707 FROM gmd_recipe_process_parameters
708 WHERE oprn_line_id = l_rec.oprn_line_id
709 AND resources = l_rec.resources
710 AND orgn_code = l_rec.orgn_code
711 AND recipe_id = l_rec.recipe_id
712 AND routingstep_id = l_rec.routingstep_id
713 AND parameter_id = 4);
714 END IF; /*IF l_rec.process_parameter_4 IS NOT NULL THEN*/
715
716 IF l_rec.process_parameter_5 IS NOT NULL THEN
717 /*Check if the row for the resource already exists if it does not then*/
718 INSERT INTO gmd_recipe_process_parameters
719 (oprn_line_id,
720 resources,
721 recipe_id,
722 routingstep_id,
723 orgn_code,
724 parameter_id,
725 target_value,
726 creation_date,
727 created_by,
728 last_update_date,
729 last_updated_by,
730 last_update_login)
731 SELECT
732 l_rec.oprn_line_id,
733 l_rec.resources,
734 l_rec.recipe_id,
735 l_rec.routingstep_id,
736 l_rec.orgn_code,
737 5,
738 l_rec.process_parameter_5,
739 SYSDATE,
740 0,
741 SYSDATE,
742 0,
743 0
744 FROM DUAL
745 WHERE NOT EXISTS (SELECT 1
746 FROM gmd_recipe_process_parameters
747 WHERE oprn_line_id = l_rec.oprn_line_id
748 AND resources = l_rec.resources
749 AND orgn_code = l_rec.orgn_code
753 END IF; /*IF l_rec.process_parameter_5 IS NOT NULL THEN*/
750 AND recipe_id = l_rec.recipe_id
751 AND routingstep_id = l_rec.routingstep_id
752 AND parameter_id = 5);
754 END LOOP;
755 EXCEPTION
756 WHEN OTHERS THEN
757 P_line_no := P_line_no + 1;
758 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
759 ,p_table_name => 'GMD_RECIPE_PROCESS_PARAMETERS'
760 ,p_db_error => sqlerrm
761 ,p_param1 => NULL
762 ,p_param2 => NULL
763 ,p_param3 => NULL
764 ,p_param4 => NULL
765 ,p_param5 => NULL
766 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
767 ,p_message_type => 'D'
768 ,p_line_no => P_line_no
769 ,p_position=> 1
770 ,p_base_message=> NULL);
771 END recipe_process_parameter;
772
773 PROCEDURE batch_process_parameter IS
774 CURSOR Cur_batch_parameters IS
775 SELECT Process_parameter_1,Process_parameter_2,Process_parameter_3,
776 Process_parameter_4,Process_parameter_5,batch_id,batchstep_id,
777 batchstep_activity_id,resources,batchstep_resource_id
778 FROM gme_batch_step_resources;
779 BEGIN
780 FOR l_rec IN Cur_batch_parameters LOOP
781 /* Insert process parameter 1 value into GME_PROCESS_PARAMETERS table */
782 IF l_rec.process_parameter_1 IS NOT NULL THEN
783 /*Check if the row for the resource already exists if it does not then*/
784 INSERT INTO gme_process_parameters
785 (process_param_id,
786 batch_id,
787 batchstep_id,
788 batchstep_activity_id,
789 resources,
790 batchstep_resource_id,
791 parameter_id,
792 actual_value,
793 creation_date,
794 created_by,
795 last_update_date,
796 last_updated_by,
797 last_update_login)
798 SELECT
799 gme_process_parameters_id_s.nextval,
800 l_rec.batch_id,
801 l_rec.batchstep_id,
802 l_rec.batchstep_activity_id,
803 l_rec.resources,
804 l_rec.batchstep_resource_id,
805 1,
806 l_rec.process_parameter_1,
807 SYSDATE,
808 0,
809 SYSDATE,
810 0,
811 0
812 FROM DUAL
813 WHERE NOT EXISTS (SELECT 1
814 FROM gme_process_parameters
815 WHERE batchstep_resource_id = l_rec.batchstep_resource_id
816 AND parameter_id = 1);
817 END IF; /*IF l_rec.process_parameter_1 IS NOT NULL THEN*/
818
819 IF l_rec.process_parameter_2 IS NOT NULL THEN
820 /*Check if the row for the resource already exists if it does not then*/
821 INSERT INTO gme_process_parameters
822 (process_param_id,
823 batch_id,
824 batchstep_id,
825 batchstep_activity_id,
826 resources,
827 batchstep_resource_id,
828 parameter_id,
829 actual_value,
830 creation_date,
831 created_by,
832 last_update_date,
833 last_updated_by,
834 last_update_login)
835 SELECT
836 gme_process_parameters_id_s.nextval,
837 l_rec.batch_id,
838 l_rec.batchstep_id,
839 l_rec.batchstep_activity_id,
840 l_rec.resources,
841 l_rec.batchstep_resource_id,
842 2,
843 l_rec.process_parameter_2,
844 SYSDATE,
845 0,
849 FROM DUAL
846 SYSDATE,
847 0,
848 0
850 WHERE NOT EXISTS (SELECT 1
851 FROM gme_process_parameters
852 WHERE batchstep_resource_id = l_rec.batchstep_resource_id
853 AND parameter_id = 2);
854 END IF; /*IF l_rec.process_parameter_2 IS NOT NULL THEN*/
855
856 IF l_rec.process_parameter_3 IS NOT NULL THEN
857 /*Check if the row for the resource already exists if it does not then*/
858 INSERT INTO gme_process_parameters
859 (process_param_id,
860 batch_id,
861 batchstep_id,
862 batchstep_activity_id,
866 actual_value,
863 resources,
864 batchstep_resource_id,
865 parameter_id,
867 creation_date,
868 created_by,
869 last_update_date,
870 last_updated_by,
871 last_update_login)
872 SELECT
873 gme_process_parameters_id_s.nextval,
874 l_rec.batch_id,
875 l_rec.batchstep_id,
876 l_rec.batchstep_activity_id,
877 l_rec.resources,
878 l_rec.batchstep_resource_id,
879 3,
880 l_rec.process_parameter_3,
881 SYSDATE,
882 0,
883 SYSDATE,
884 0,
885 0
886 FROM DUAL
887 WHERE NOT EXISTS (SELECT 1
888 FROM gme_process_parameters
889 WHERE batchstep_resource_id = l_rec.batchstep_resource_id
890 AND parameter_id = 3);
891 END IF; /*IF l_rec.process_parameter_3 IS NOT NULL THEN*/
892
893 IF l_rec.process_parameter_4 IS NOT NULL THEN
894 /*Check if the row for the resource already exists if it does not then*/
895 INSERT INTO gme_process_parameters
896 (process_param_id,
897 batch_id,
898 batchstep_id,
899 batchstep_activity_id,
900 resources,
901 batchstep_resource_id,
902 parameter_id,
903 actual_value,
904 creation_date,
905 created_by,
906 last_update_date,
907 last_updated_by,
908 last_update_login)
909 SELECT
910 gme_process_parameters_id_s.nextval,
911 l_rec.batch_id,
912 l_rec.batchstep_id,
913 l_rec.batchstep_activity_id,
914 l_rec.resources,
915 l_rec.batchstep_resource_id,
916 4,
917 l_rec.process_parameter_4,
918 SYSDATE,
919 0,
920 SYSDATE,
921 0,
922 0
923 FROM DUAL
924 WHERE NOT EXISTS (SELECT 1
925 FROM gme_process_parameters
926 WHERE batchstep_resource_id = l_rec.batchstep_resource_id
927 AND parameter_id = 4);
928 END IF; /*IF l_rec.process_parameter_4 IS NOT NULL THEN*/
929
930 IF l_rec.process_parameter_5 IS NOT NULL THEN
931 /*Check if the row for the resource already exists if it does not then*/
932 INSERT INTO gme_process_parameters
933 (process_param_id,
934 batch_id,
935 batchstep_id,
936 batchstep_activity_id,
937 resources,
938 batchstep_resource_id,
939 parameter_id,
940 actual_value,
941 creation_date,
942 created_by,
943 last_update_date,
944 last_updated_by,
945 last_update_login)
946 SELECT
947 gme_process_parameters_id_s.nextval,
948 l_rec.batch_id,
949 l_rec.batchstep_id,
950 l_rec.batchstep_activity_id,
951 l_rec.resources,
952 l_rec.batchstep_resource_id,
953 5,
954 l_rec.process_parameter_5,
955 SYSDATE,
956 0,
957 SYSDATE,
958 0,
959 0
960 FROM DUAL
961 WHERE NOT EXISTS (SELECT 1
962 FROM gme_process_parameters
963 WHERE batchstep_resource_id = l_rec.batchstep_resource_id
964 AND parameter_id = 5);
965 END IF; /*IF l_rec.process_parameter_5 IS NOT NULL THEN*/
966 END LOOP;
967 EXCEPTION
968 WHEN OTHERS THEN
969 P_line_no := P_line_no + 1;
970 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
971 ,p_table_name => 'GME_PROCESS_PARAMETERS'
972 ,p_db_error => sqlerrm
973 ,p_param1 => NULL
974 ,p_param2 => NULL
975 ,p_param3 => NULL
976 ,p_param4 => NULL
977 ,p_param5 => NULL
978 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
979 ,p_message_type => 'D'
980 ,p_line_no => P_line_no
981 ,p_position=> 1
982 ,p_base_message=> NULL);
983 END batch_process_parameter;
984
985 PROCEDURE get_override IS
986 CURSOR Cur_get_override IS
987 SELECT DISTINCT resources,Parameter_id
988 FROM gmd_oprn_process_parameters
989 UNION
990 SELECT DISTINCT resources,Parameter_id
991 FROM gmd_recipe_process_parameters
992 UNION
993 SELECT DISTINCT resources,Parameter_id
994 FROM gme_process_parameters
995 order by 1;
996 l_rec Cur_get_override%ROWTYPE;
997 CURSOR Cur_get_target(V_resources VARCHAR2, V_parameter_id NUMBER) IS
998 SELECT Target_Value, COUNT(1)
999 FROM gmd_oprn_process_parameters
1000 WHERE resources = V_resources
1001 AND parameter_id = V_parameter_id
1002 GROUP BY Target_Value
1003 UNION
1004 SELECT Target_Value, COUNT(1)
1005 FROM gmd_recipe_process_parameters
1006 WHERE resources = V_resources
1010 SELECT Target_Value, COUNT(1)
1007 AND parameter_id = V_parameter_id
1008 GROUP BY Target_Value
1009 UNION
1011 FROM gme_process_parameters
1012 WHERE resources = V_resources
1013 AND parameter_id = V_parameter_id
1014 GROUP BY Target_Value
1018 X_seq NUMBER;
1015 ORDER BY 2 DESC;
1016 X_target_value VARCHAR2(16);
1017 X_count NUMBER;
1019 X_resources cr_rsrc_mst_b.resources%type;
1020 BEGIN
1021 X_seq := 0;
1022 FOR l_rec IN Cur_get_override LOOP
1023 IF NVL(X_resources, 'z') = l_rec.resources THEN
1024 X_seq := X_seq + 1;
1025 ELSE
1026 X_seq := 1;
1027 X_resources := l_rec.resources;
1028 END IF;
1029 OPEN Cur_get_target(l_rec.resources,l_rec.parameter_id);
1030 FETCH Cur_get_target INTO X_target_value, X_count;
1031 CLOSE Cur_get_target;
1032 INSERT INTO gmp_resource_parameters
1033 (resources,
1034 sequence_no,
1035 parameter_id,
1036 target_value,
1037 creation_date,
1038 created_by,
1039 last_update_date,
1040 last_updated_by)
1041 SELECT
1042 l_rec.resources,
1043 X_seq,
1044 l_rec.parameter_id,
1045 X_target_value,
1046 SYSDATE,
1047 0,
1048 SYSDATE,
1049 0
1050 FROM DUAL
1051 WHERE NOT EXISTS (SELECT 1
1052 FROM GMP_RESOURCE_PARAMETERS
1053 WHERE resources = l_rec.resources
1054 AND parameter_id = l_rec.parameter_id);
1055 END LOOP;
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 P_line_no := P_line_no + 1;
1059 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
1060 ,p_table_name => 'GMP_RESOURCE_PARAMETERS'
1061 ,p_db_error => sqlerrm
1062 ,p_param1 => NULL
1063 ,p_param2 => NULL
1064 ,p_param3 => NULL
1065 ,p_param4 => NULL
1066 ,p_param5 => NULL
1067 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
1068 ,p_message_type => 'D'
1069 ,p_line_no => P_line_no
1070 ,p_position=> 1
1071 ,p_base_message=> NULL);
1072 END get_override;
1073
1074 PROCEDURE run IS
1075 BEGIN
1076 P_run_id := GMA_MIGRATION.gma_migration_start
1077 (p_app_short_name => 'GMD'
1078 ,p_mig_name => 'PROCESS_PARAMETERS_MIGRATION');
1079 check_process_parameter;
1080 oprn_process_parameter;
1081 recipe_process_parameter;
1082 batch_process_parameter;
1083 get_override;
1084 GMA_MIGRATION.gma_migration_end (l_run_id => p_run_id);
1085 END run;
1086
1087
1088 END GMD_PROC_PARAMS_MIGR;