[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_MIGRATION
Source
1 PACKAGE BODY gmd_recipe_migration AS
2 /* $Header: GMDRMIGB.pls 120.4 2006/09/19 14:37:13 txdaniel noship $ */
3 PROCEDURE migrate_recipe (
4 p_api_version IN NUMBER,
5 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
6 p_commit IN VARCHAR2 := fnd_api.g_false,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_msg_count OUT NOCOPY NUMBER,
9 x_msg_data OUT NOCOPY VARCHAR2,
10 p_recipe_no_choice IN VARCHAR2 )
11 IS
12 /* This cursor extracts distinct formula - routing combination to create Recipes as
13 formulas */
14 CURSOR formula_cur
15 IS
16 SELECT h.formula_no,
17 h.formula_vers,
18 e.routing_id,
19 e.formula_id,
20 h.formula_desc1
21 FROM fm_form_eff_bak e,
22 fm_form_mst_b h
23 WHERE NOT EXISTS ( SELECT 1
24 FROM gmd_recipe_eff_assoc
25 WHERE fmeff_id = e.fmeff_id )
26 AND e.formula_id = h.formula_id
27 GROUP BY h.formula_no,
28 h.formula_vers,
29 e.routing_id,
30 e.formula_id,
31 h.formula_desc1
32 ORDER BY h.formula_no,
33 h.formula_vers;
34
35 /* This cursor extracts distinct formula - routing combination to create Recipes as
36 items */
37 CURSOR product_cur
38 IS
39 SELECT i.item_no,
40 e.formula_id,
41 e.routing_id,
42 i.item_desc1
43 FROM fm_form_mst_b h,
44 fm_form_eff_bak e,
45 fm_matl_dtl m,
46 ic_item_mst i
47 WHERE NOT EXISTS ( SELECT 1
48 FROM gmd_recipe_eff_assoc
49 WHERE fmeff_id = e.fmeff_id )
50 AND e.formula_id = m.formula_id
51 AND e.formula_id = h.formula_id
52 AND m.item_id = i.item_id
53 AND m.line_no = 1
54 AND m.line_type = 1
55 GROUP BY i.item_no,
56 e.formula_id,
57 e.routing_id,
58 i.item_desc1
59 ORDER BY i.item_no;
60
61 /* Extracts routing step and line information from fm_rout_mtl_bak table */
62 CURSOR recipe_material_cur
63 IS
64 SELECT rm.routingstep_no,
65 rm.formulaline_id,
66 rm.routing_id,
67 rm.formula_id,
68 rm.text_code,
69 rm.last_updated_by,
70 rm.created_by,
71 rm.last_update_date,
72 rm.creation_date,
73 rm.last_update_login
74 FROM fm_rout_mtl_bak rm
75 WHERE rm.routingstep_no IS NOT NULL
76 AND NOT EXISTS ( SELECT 1
77 FROM gmd_recipe_step_materials
78 WHERE formulaline_id = rm.formulaline_id
79 AND EXISTS ( SELECT recipe_id
80 FROM gmd_recipes
81 WHERE formula_id = rm.formula_id
82 AND routing_id = rm.routing_id ))
83 GROUP BY rm.routingstep_no,
84 rm.formulaline_id,
85 rm.routing_id,
86 rm.formula_id,
87 rm.text_code,
88 rm.last_updated_by,
89 rm.created_by,
90 rm.last_update_date,
91 rm.creation_date,
92 rm.last_update_login;
93
94 /* Get the duplicate effectivity ids */
95 /* Get all effectivities where the only difference is the customer id */
96 /* To create a customer specific effectivity, in past a new effectivity for that
97 customer would be created. However, now with the new GMD model customer specific effectivities
98 are stored in a recipe-customer table */
99 CURSOR get_dup_cust_eff
100 IS
101 SELECT orgn_code,
102 item_id,
103 formula_use,
104 end_date,
105 start_date,
106 min_qty,
107 max_qty,
108 std_qty,
109 item_um,
110 preference,
111 routing_id,
112 formula_id,
113 /* Thomas - Bug 2562007, duplicate rows with delete mark are needed */
114 -- delete_mark,
115 COUNT (* ) eff_dup_count
116 FROM fm_form_eff
117 /* Thomas - Bug 2562007,Their may be duplicates without customer id */
118 -- WHERE cust_id IS NOT NULL
119 GROUP BY orgn_code,
120 item_id,
121 formula_use,
122 end_date,
123 start_date,
124 min_qty,
125 max_qty,
126 std_qty,
127 item_um,
128 preference,
129 routing_id,
130 formula_id
131 -- delete_mark
132 HAVING COUNT (* ) > 1;
133
134 /* Check if recipe customer exists */
135 Cursor check_recipe_cust_exists(v_recipe_id NUMBER, v_customer_id NUMBER) IS
136 SELECT 1
137 FROM sys.DUAL
138 WHERE EXISTS ( SELECT 1
139 FROM gmd_recipe_customers
140 WHERE recipe_id = v_recipe_id
141 AND customer_id = v_customer_id);
142
143 /* Check if recipe step material exists */
144 Cursor check_step_mat_exists(v_recipe_id NUMBER, v_formulaline_id NUMBER) IS
145 SELECT 1
146 FROM sys.DUAL
147 WHERE EXISTS ( SELECT 1
148 FROM gmd_recipe_step_materials
149 WHERE recipe_id = v_recipe_id
150 AND formulaline_id = v_formulaline_id);
151
152 /* Get all invalid validity rules */
153 CURSOR get_invalid_validity_rules
154 IS
155 SELECT recipe_validity_rule_id,
156 item_id
157 FROM gmd_recipe_validity_rules;
158
159 /* Check if the item id exists in ic_item_mst table */
160 CURSOR get_valid_item ( v_item_id NUMBER )
161 IS
162 SELECT 1
163 FROM sys.DUAL
164 WHERE EXISTS ( SELECT 1
165 FROM ic_item_mst
166 WHERE item_id = v_item_id
167 AND delete_mark = 0 );
168
169 /* Get the costing details based on effectivities that have cost rollup done */
170 CURSOR cm_cmpt_dtl_cur (pfmeff_id NUMBER )
171 IS
172 SELECT *
173 FROM cm_cmpt_dtl
174 WHERE fmeff_id = pfmeff_id;
175
176 /* Defining all local variables */
177 l_recipe_id gmd_recipes.recipe_id%TYPE := 0;
178 l_routingstep_id NUMBER := 0;
179 l_recipe_no gmd_recipes.recipe_no%TYPE := NULL;
180 l_recipe_version gmd_recipes.recipe_version%TYPE := 0;
181 l_recipe_status gmd_recipes.recipe_status%TYPE := '700';
182 l_counter NUMBER := 0;
183 l_cm_counter NUMBER := 0;
184 l_recipe_vr_id NUMBER := 0;
185 l_return_val NUMBER := 0;
186 l_item_count NUMBER := 0;
187 l_dup_counter NUMBER := 0;
188 l_fmeff_id NUMBER;
189 l_dummy_id NUMBER;
190 l_owner_orgn VARCHAR2(4);
191 l_creation_orgn VARCHAR2(4);
192 l_delete_mark NUMBER(5);
193 l_creation_date DATE;
194 l_created_by NUMBER(15);
195 l_last_updated_by NUMBER(15);
196 l_last_update_date DATE;
197 l_last_update_login NUMBER(15);
198 l_text_code NUMBER(15);
199 l_owner_id NUMBER(15);
200 l_creation_orgn_code VARCHAR2 ( 4 );
201 l_owner_orgn_code VARCHAR2 ( 4 );
202 l_msg_data VARCHAR2 ( 240 );
203 l_msg_count NUMBER;
204 l_return_status VARCHAR2 ( 1 );
205 l_return_code NUMBER;
206 error_msg VARCHAR2 ( 240 );
207 BEGIN
208 /* Define Savepoint */
209 SAVEPOINT recipe_migration;
210
211 /* Irrespective of the whether recipes are named based on formulas
212 or items, the number of recipes created should remain the same.
213 However, the number of recipe_no's might be different
214 in the two cases
215 */
216
217 /* Step 1 : To migrate data into the Recipe Header table */
218 IF ( UPPER ( p_recipe_no_choice ) = 'FORMULA' )
219 THEN
220 FOR formula_rec IN formula_cur
221 LOOP
222 BEGIN
223 /* intialize the return status */
224 x_return_status := 'S';
225
226 /* Compare the new recipe_no with the previous record */
227 /* If it is a new one, the recipe version is 1 */
228 /* else the recipe version is incremented by 1 */
229
230 /* Since the cursor is grouped and ordered by formula no and version */
231 /* the recipe no and version assignments would work fine. */
232 /* Thomas - Bug 2562007, Removed the TRIM condition to migrate the formulae */
233 /* as they were defined */
234 IF ( ( l_recipe_no ) <> ( formula_rec.formula_no ))
235 THEN
236 l_recipe_no := formula_rec.formula_no;
237 l_recipe_version := 1;
238 ELSE
239 l_recipe_no := formula_rec.formula_no;
240 l_recipe_version := l_recipe_version + 1;
241 END IF;
242
243
244 /* ================================ */
245 /* Based on Recipe_no and Recipe_version check if a recipe */
246 /* already exists in the database */
247 /* ================================= */
248 gmd_recipe_val.recipe_name (
249 p_api_version => 1.0,
250 p_init_msg_list => fnd_api.g_false,
251 p_commit => fnd_api.g_false,
252 p_recipe_no => l_recipe_no,
253 p_recipe_version => l_recipe_version,
254 x_return_status => l_return_status,
255 x_msg_count => l_msg_count,
256 x_msg_data => l_msg_data,
257 x_return_code => l_return_code,
258 x_recipe_id => l_recipe_id );
259
260 IF ( l_recipe_id IS NOT NULL )
261 THEN
262 x_return_status := fnd_api.g_ret_sts_error;
263 /* Thomas - Bug 2562007, Added the following loggin of message */
264 insert_message (p_source_table => 'FM_FORM_MST'
265 ,p_target_table => 'GMD_RECIPES'
266 ,p_source_id => l_recipe_no
267 ,p_target_id => l_recipe_version
268 ,p_message => 'Recipe:'||l_recipe_no||' Version:'||l_recipe_version||' already exists.'
269 ,p_error_type => 'U');
270 END IF;
271
272 IF ( x_return_status = 'S' )
273 THEN
274
275 /* Get the recipe id from the sequence */
276 SELECT gmd_recipe_id_s.NEXTVAL
277 INTO l_recipe_id
278 FROM sys.DUAL;
279 /* function gmdfmval_pub.locked_effectivity_val gets the recipe status */
280 /* This function return non zero value when the effectivity is locked */
281
282 l_return_val := gmdfmval_pub.locked_effectivity_val (
283 formula_rec.formula_id );
284
285 IF ( l_return_val <> 0 )
286 THEN
287 l_recipe_status := '900';
288 ELSE
289 l_recipe_status := '700';
290 END IF;
291
292 /* Derive the delete mark and WHO column values */
293 /* Bug 3503706 - Created separate SQL statements
294 to derive delete mark and Who columns
295 */
296 SELECT Min(delete_mark)
297 INTO l_delete_mark
298 FROM fm_form_eff_bak
299 WHERE formula_id = formula_rec.formula_id
300 AND NVL ( routing_id, -999 ) =
301 NVL ( formula_rec.routing_id, -999 );
302
303 SELECT creation_date,
304 created_by,
305 last_updated_by,
306 last_update_date,
307 last_update_login,
308 created_by
309 INTO l_creation_date,
310 l_created_by,
311 l_last_updated_by,
312 l_last_update_date,
313 l_last_update_login,
314 l_owner_id
315 FROM fm_form_eff_bak
316 WHERE formula_id = formula_rec.formula_id
317 AND NVL ( routing_id, -999 ) =
318 NVL ( formula_rec.routing_id, -999 )
319 AND ROWNUM = 1;
320
321 /* Define the creation and owner orgn values from profile values */
322
323 l_creation_orgn_code :=
324 TRIM (fnd_profile.value_specific ('GEMMS_DEFAULT_ORGN' ,
325 l_created_by ));
326 l_owner_orgn_code :=
327 TRIM (fnd_profile.value_specific ('GEMMS_DEFAULT_ORGN' ,
328 l_owner_id ));
329
330 INSERT INTO gmd_recipes_b (recipe_id, recipe_no, recipe_version, owner_orgn_code, creation_orgn_code,
331 formula_id, routing_id, recipe_status, calculate_step_quantity, owner_id,
332 delete_mark, creation_date, created_by, last_update_date, last_updated_by, last_update_login)
333 VALUES (l_recipe_id, l_recipe_no, l_recipe_version, l_owner_orgn_code, l_creation_orgn_code,
334 formula_rec.formula_id, formula_rec.routing_id, l_recipe_status, 0, l_owner_id,
335 l_delete_mark, l_creation_date, l_created_by, l_last_update_date, l_last_updated_by, l_last_update_login);
336
337 INSERT INTO gmd_recipes_tl (recipe_id, recipe_description, source_lang, created_by, creation_date,
338 last_updated_by, last_update_date, last_update_login, language
339 )
340 SELECT l_recipe_id, formula_rec.formula_desc1, userenv('lang'), l_created_by, l_creation_date,
341 l_last_updated_by, l_last_update_date, l_last_update_login, l.language_code
342 FROM fnd_languages l
343 WHERE l.installed_flag in ('I', 'B')
344 AND NOT EXISTS (SELECT null
345 FROM gmd_recipes_tl t
346 WHERE t.recipe_id = l_recipe_id
347 AND t.language = l.language_code);
348 END IF;
349
350 EXCEPTION
351 WHEN OTHERS
352 THEN
353 error_msg := SQLERRM;
354 insert_message (p_source_table => 'FM_FORM_EFF'
355 ,p_target_table => 'GMD_RECIPES'
356 ,p_source_id => formula_rec.formula_id
357 ,p_target_id => l_recipe_id
358 ,p_message => error_msg
359 ,p_error_type => 'U');
360 END; /* End prior to end loop */
361 END LOOP; /* loop completed for all distinct formula- routings */
362 /* ******************* End of Formula selection ********************** */
363 ELSIF ( UPPER ( p_recipe_no_choice ) = 'PRODUCT' )
364 THEN
365 FOR product_rec IN product_cur
366 LOOP
367 BEGIN
368 /* initialize the return status */
369 x_return_status := 'S';
370
371 /* Compare the new recipe_no with the previous record */
372 /* If it is a new one, the recipe version is 1 */
373 /* else the recipe version is inremented by 1 */
374
375 /* Since the cursor is grouped and ordered by item no */
376 IF ( TRIM ( l_recipe_no ) <> TRIM ( product_rec.item_no ))
377 THEN
378 l_recipe_no := TRIM ( product_rec.item_no );
379 l_recipe_version := 1;
380 ELSE
381 l_recipe_no := TRIM ( product_rec.item_no );
382 l_recipe_version := l_recipe_version + 1;
383 END IF;
384
385
386 /* ================================ */
387 /* Based on Recipe_no and Recipe_version check if a recipe */
388 /* already exists in the database */
389 /* ================================= */
390 gmd_recipe_val.recipe_name (
391 p_api_version => 1.0,
392 p_init_msg_list => fnd_api.g_false,
393 p_commit => fnd_api.g_false,
394 p_recipe_no => l_recipe_no,
395 p_recipe_version => l_recipe_version,
396 x_return_status => l_return_status,
397 x_msg_count => l_msg_count,
398 x_msg_data => l_msg_data,
399 x_return_code => l_return_code,
400 x_recipe_id => l_recipe_id );
401
402 IF ( l_recipe_id IS NOT NULL )
403 THEN
404 x_return_status := fnd_api.g_ret_sts_error;
405 END IF;
406
407 /* Get the recipe id from sequence */
408
409 SELECT gmd_recipe_id_s.NEXTVAL
410 INTO l_recipe_id
411 FROM sys.DUAL;
412 /* function gmdfmval_pub.locked_effectivity_val gets the recipe status */
413 /* This function return non zero value when the effectivity is locked */
414
415 l_return_val := gmdfmval_pub.locked_effectivity_val (
416 product_rec.formula_id );
417
418 IF ( l_return_val <> 0 )
419 THEN
420 l_recipe_status := '900';
421 ELSE
422 l_recipe_status := '700';
423 END IF;
424
425 /* get the delete mark and WHO columns */
426 /* Bug 3503706 - Created separate SQL statements
427 to derive delete mark and Who columns
428 */
429 SELECT Min(delete_mark)
430 INTO l_delete_mark
431 FROM fm_form_eff_bak
432 WHERE formula_id = product_rec.formula_id
433 AND NVL ( routing_id, -999 ) =
434 NVL ( product_rec.routing_id, -999 );
435
436 SELECT creation_date,
437 created_by,
438 last_updated_by,
439 last_update_date,
440 last_update_login,
441 created_by
442 INTO l_creation_date,
443 l_created_by,
444 l_last_updated_by,
445 l_last_update_date,
446 l_last_update_login,
447 l_owner_id
448 FROM fm_form_eff_bak
449 WHERE formula_id = product_rec.formula_id
450 AND NVL ( routing_id, -999 ) =
451 NVL ( product_rec.routing_id, -999 )
452 AND ROWNUM = 1;
453
454 /* Define the creation and owner orgn values from profile values */
455
456 l_creation_orgn_code :=
457 TRIM (fnd_profile.value_specific ('GEMMS_DEFAULT_ORGN' ,
458 l_created_by ));
459 l_owner_orgn_code :=
460 TRIM (fnd_profile.value_specific ( 'GEMMS_DEFAULT_ORGN' ,
461 l_owner_id ));
462
463 /* Insert into the recipe header table */
464 IF ( x_return_status = 'S' )
465 THEN
466 INSERT INTO gmd_recipes_b (recipe_id, recipe_no, recipe_version, owner_orgn_code, creation_orgn_code,
467 formula_id, routing_id, recipe_status, calculate_step_quantity, owner_id,
468 delete_mark, creation_date, created_by, last_update_date, last_updated_by, last_update_login)
469 VALUES (l_recipe_id, l_recipe_no, l_recipe_version, l_owner_orgn_code, l_creation_orgn_code,
470 product_rec.formula_id, product_rec.routing_id, l_recipe_status, 0, l_owner_id,
471 l_delete_mark, l_creation_date, l_created_by, l_last_update_date, l_last_updated_by, l_last_update_login);
472
473 INSERT INTO gmd_recipes_tl (recipe_id, recipe_description, source_lang, created_by, creation_date,
474 last_updated_by, last_update_date, last_update_login, language
475 )
476 SELECT l_recipe_id, product_rec.item_desc1, userenv('lang'), l_created_by, l_creation_date,
477 l_last_updated_by, l_last_update_date, l_last_update_login, l.language_code
478 FROM fnd_languages l
479 WHERE l.installed_flag in ('I', 'B')
480 AND NOT EXISTS (SELECT null
481 FROM gmd_recipes_tl t
482 WHERE t.recipe_id = l_recipe_id
483 AND t.language = l.language_code);
484
485 END IF;
486 EXCEPTION
487 WHEN OTHERS
488 THEN
489 error_msg := SQLERRM;
490 insert_message (p_source_table => 'FM_FORM_EFF'
491 ,p_target_table => 'GMD_RECIPES'
492 ,p_source_id => product_rec.item_no
493 ,p_target_id => l_recipe_id
494 ,p_message => error_msg
495 ,p_error_type => 'U');
496
497 END; /* End prior to end loop */
498 END LOOP;
499 /* ******************* End of item - recipe selection ********************** */
500 END IF;
501
502 /* *******************Step Common to either selection **************** */
503
504 /* Step 2: Create an association table */
505 /* Loop thro every fmeff_id in the fm_form_eff table */
506 /* Based on the formula and routing id we could derive the recipe_id from recipe_header. */
507
508 /* We need to use an association table GMD_RECIPE_EFF_ASSOC that relates fmeff_id */
509 /* to the recipe_id and validity rule id */
510
511 INSERT INTO gmd_recipe_eff_assoc
512 ( fmeff_id,
513 recipe_id,
514 orgn_code,
515 item_id,
516 formula_use,
517 end_date,
518 start_date,
519 inv_min_qty,
520 inv_max_qty,
521 min_qty,
522 max_qty,
523 std_qty,
524 item_um,
525 preference,
526 routing_id,
527 formula_id,
528 cust_id,
529 creation_date,
530 last_update_date,
531 created_by,
532 last_updated_by,
533 delete_mark,
534 text_code,
535 trans_cnt,
536 last_update_login,
537 recipe_validity_rule_id )
538 SELECT eff.fmeff_id fmeff_id,
539 rec.recipe_id,
540 eff.orgn_code,
541 eff.item_id,
542 eff.formula_use,
543 eff.end_date,
544 eff.start_date,
545 eff.inv_min_qty,
546 eff.inv_max_qty,
547 eff.min_qty,
548 eff.max_qty,
549 eff.std_qty,
550 eff.item_um,
551 eff.preference,
552 eff.routing_id,
553 eff.formula_id,
554 eff.cust_id,
555 eff.creation_date,
556 eff.last_update_date,
557 eff.created_by,
558 eff.last_updated_by,
559 eff.delete_mark,
560 eff.text_code,
561 eff.trans_cnt,
562 eff.last_update_login,
563 eff.fmeff_id vr_id
564 FROM fm_form_eff_bak eff,
565 gmd_recipes_b rec
566 WHERE NOT EXISTS ( SELECT 1
567 FROM gmd_recipe_eff_assoc
568 WHERE fmeff_id = eff.fmeff_id )
569 AND rec.formula_id = eff.formula_id
570 AND NVL ( rec.routing_id, -111 ) = NVL ( eff.routing_id, -111 );
571
572 /* Step 3 */
573 /* Migrate data into the GMD_RECIPE_CUSTOMERS table */
574 FOR recipe_cust_rec IN (SELECT a.recipe_id,
575 a.cust_id,
576 a.created_by,
577 a.creation_date,
578 a.last_updated_by,
579 a.last_update_login,
580 a.text_code,
581 a.last_update_date
582 FROM gmd_recipe_eff_assoc a
583 WHERE NOT EXISTS ( SELECT 1
584 FROM gmd_recipe_customers
585 WHERE recipe_id = a.recipe_id
586 AND customer_id = a.cust_id )
587 AND cust_id IS NOT NULL
588 GROUP BY a.cust_id,
589 a.recipe_id,
590 a.created_by,
591 a.creation_date,
592 a.last_updated_by,
593 a.last_update_login,
594 a.text_code,
595 a.last_update_date ) LOOP
596
597 OPEN check_recipe_cust_exists(recipe_cust_rec.recipe_id,
598 recipe_cust_rec.cust_id);
599 FETCH check_recipe_cust_exists INTO l_dummy_id;
600 IF (check_recipe_cust_exists%NOTFOUND) THEN
601 INSERT INTO gmd_recipe_customers
602 ( recipe_id,
603 customer_id,
604 created_by,
605 creation_date,
606 last_updated_by,
607 last_update_login,
608 text_code,
609 last_update_date )
610 VALUES ( recipe_cust_rec.recipe_id,
611 recipe_cust_rec.cust_id,
612 recipe_cust_rec.created_by,
613 recipe_cust_rec.creation_date,
614 recipe_cust_rec.last_updated_by,
615 recipe_cust_rec.last_update_login,
616 recipe_cust_rec.text_code,
617 recipe_cust_rec.last_update_date );
618 END IF;
619 CLOSE check_recipe_cust_exists;
620 END LOOP; /* for recipe customers */
621
622 /* Step 4 : */
623 /* Migrate data into gmd_recipe_validity_rules table */
624 /* All effectvities are inserted into the validity rules table */
625
626 INSERT INTO gmd_recipe_validity_rules
627 ( recipe_validity_rule_id,
628 recipe_id,
629 orgn_code,
630 item_id,
631 recipe_use,
632 preference,
633 start_date,
634 end_date,
635 min_qty,
636 max_qty,
637 std_qty,
638 item_um,
639 inv_min_qty,
640 inv_max_qty,
641 text_code,
642 created_by,
643 creation_date,
644 last_updated_by,
645 last_update_date,
646 last_update_login,
647 delete_mark,
648 validity_rule_status )
649 SELECT fmeff_id,
650 recipe_id,
651 orgn_code,
652 item_id,
653 formula_use,
654 preference,
655 start_date,
656 end_date,
657 min_qty,
658 max_qty,
659 std_qty,
660 item_um,
661 inv_min_qty,
662 inv_max_qty,
663 text_code,
664 created_by,
665 creation_date,
666 last_updated_by,
667 last_update_date,
668 last_update_login,
669 delete_mark,
670 gmdfmval_pub.gmd_effectivity_locked_status ( fmeff_id )
671 FROM gmd_recipe_eff_assoc
672 WHERE NOT EXISTS ( SELECT 1
673 FROM gmd_recipe_validity_rules
674 WHERE recipe_validity_rule_id = fmeff_id );
675
676 /* Step 5 */
677 /* Migrate data into the Recipe Materials Step table from
678 fm_rout_mtl_bak table */
679 FOR recipe_material_rec IN recipe_material_cur
680 LOOP
681 BEGIN
682 IF ( recipe_material_rec.routing_id IS NOT NULL )
683 THEN
684 SELECT recipe_id
685 INTO l_recipe_id
686 FROM gmd_recipes_b
687 WHERE formula_id = recipe_material_rec.formula_id
688 AND routing_id = recipe_material_rec.routing_id
689 AND ROWNUM = 1;
690 ELSE
691 SELECT DISTINCT recipe_id
692 INTO l_recipe_id
693 FROM gmd_recipes_b
694 WHERE formula_id = recipe_material_rec.formula_id
695 AND routing_id IS NULL
696 AND ROWNUM = 1;
697 END IF;
698
699 /* Important based on the routingstep_no and routing_id get the */
700 /* routingstep_id */
701
702 SELECT routingstep_id
703 INTO l_routingstep_id
704 FROM fm_rout_dtl
705 WHERE routing_id = recipe_material_rec.routing_id
706 AND routingstep_no = recipe_material_rec.routingstep_no
707 AND ROWNUM = 1;
708
709 /* Check for unique constraints on this table */
710 OPEN check_step_mat_exists(l_recipe_id,
711 recipe_material_rec.formulaline_id);
712 FETCH check_step_mat_exists INTO l_dummy_id;
713 IF (check_step_mat_exists%NOTFOUND) THEN
714 INSERT INTO gmd_recipe_step_materials
715 ( recipe_id,
716 routingstep_id,
717 formulaline_id,
718 creation_date,
719 created_by,
720 last_updated_by,
721 last_update_date,
722 last_update_login )
723 VALUES ( l_recipe_id,
724 l_routingstep_id,
725 recipe_material_rec.formulaline_id,
726 recipe_material_rec.creation_date,
727 recipe_material_rec.created_by,
728 recipe_material_rec.last_updated_by,
729 recipe_material_rec.last_update_date,
730 recipe_material_rec.last_update_login );
731
732 END IF;
733 CLOSE check_step_mat_exists;
734
735 EXCEPTION
736 WHEN OTHERS
737 THEN
738 error_msg := SQLERRM;
739 insert_message (p_source_table => 'FM_ROUT_MTL'
740 ,p_target_table => 'GMD_RECIPE_STEP_MATERIALS'
741 ,p_source_id => recipe_material_rec.formulaline_id
742 ,p_target_id => recipe_material_rec.formulaline_id
743 ,p_message => error_msg
744 ,p_error_type => 'U');
745 END; /* End prior to end loop */
746 END LOOP;
747
748 /* Step 6: Obsolete VRs and recipes that are based on Obsolted formulas
749 During formula migartion we had obsoleted formulas whose items
750 are inactive. Recipes and VRs that use this formula are also obsoleted */
751 UPDATE gmd_recipes_b
752 SET recipe_status = '1000'
753 WHERE formula_id IN (SELECT formula_id
754 FROM fm_form_mst_b
755 WHERE formula_Status = '1000');
756
757 /* Obsolete Vrs that are based on obsoleted Recipes */
758 UPDATE gmd_recipe_validity_rules
759 SET validity_rule_status = '1000'
760 WHERE recipe_id IN (SELECT recipe_id
761 FROM gmd_recipes_b
762 WHERE recipe_Status = '1000');
763
764 /* Step 7: Update all tables that use fmeff_id - with VR id */
765 /* Validate duplicate effectivity customers */
766 FOR get_dup_eff_rec IN get_dup_cust_eff
767 LOOP /* All dup effectivity groups - loop eff 1*/
768 /* Get the duplicate effectivity ids for diffent customers */
769
770 /* initialize the dup effectivity counter */
771 l_dup_counter := 0;
772 /* changed fm_form_eff to _bak as it was deleting new recipes */
773 FOR update_eff_rec IN
774 (SELECT fmeff_id
775 FROM fm_form_eff_bak
776 WHERE NVL(orgn_code,fnd_api.g_miss_char) =
777 NVL(get_dup_eff_rec.orgn_code,
778 fnd_api.g_miss_char )
779 AND item_id = get_dup_eff_rec.item_id
780 AND formula_use = get_dup_eff_rec.formula_use
781 /* Thomas - Bug 2562007, Added NVL condition as end date could be null */
782 AND NVL(end_date, fnd_api.g_miss_date) = NVL(get_dup_eff_rec.end_date, fnd_api.g_miss_date)
783 AND start_date = get_dup_eff_rec.start_date
784 AND min_qty = get_dup_eff_rec.min_qty
785 AND max_qty = get_dup_eff_rec.max_qty
786 AND std_qty = get_dup_eff_rec.std_qty
787 AND item_um = get_dup_eff_rec.item_um
788 AND preference = get_dup_eff_rec.preference
789 AND NVL ( routing_id, fnd_api.g_miss_num ) =
790 NVL (get_dup_eff_rec.routing_id,
791 fnd_api.g_miss_num )
792 AND formula_id = get_dup_eff_rec.formula_id
793 /* Thomas - Bug 2562007, Commented out the following checking */
794 /* and added the order by clause */
795 -- AND delete_mark = get_dup_eff_rec.delete_mark
796 -- AND cust_id IS NOT NULL
797 ORDER BY delete_mark, cust_id desc, fmeff_id)
798
799 LOOP /* dupplicate effectivities for a given group - loop eff 2 */
800 IF (l_dup_counter = 0) THEN
801 l_fmeff_id := update_eff_rec.fmeff_id;
802 END IF;
803 /* Skip the first row */
804 /* for e.g if the select i.e loop eff 2 above returns fmeff_id
805 101, 102 and 103 as duplicate ids we want delete 102 and 103
806 from the VR table so we skip the 1st row that returns 101*/
807 /* Thomas - Bug 2562007, Changed the l_dup_counter from > 1 to > 0 */
808 IF (l_dup_counter > 0) THEN
809 BEGIN
810 UPDATE pm_btch_hdr
811 SET fmeff_id = l_fmeff_id
812 WHERE fmeff_id = update_eff_rec.fmeff_id;
813
814 UPDATE gl_item_cst
815 SET fmeff_id = l_fmeff_id
816 WHERE fmeff_id = update_eff_rec.fmeff_id;
817
818 UPDATE gmp_form_eff
819 SET fmeff_id = l_fmeff_id
820 WHERE fmeff_id = update_eff_rec.fmeff_id;
821
822 /* Since table cm_cmpt_dtl could have many fmeff_ids
823 row to be updated the system might run out of rollback segment
824 space - to prevent this we provide regular interval commits */
825 SELECT NVL ( COUNT (* ), 0 )
826 INTO l_cm_counter
827 FROM cm_cmpt_dtl
828 WHERE fmeff_id = update_eff_rec.fmeff_id;
829
830 FOR k IN cm_cmpt_dtl_cur ( update_eff_rec.fmeff_id )
831 LOOP
832 UPDATE cm_cmpt_dtl
833 SET fmeff_id = l_fmeff_id
834 WHERE fmeff_id = update_eff_rec.fmeff_id
835 AND ROWNUM < 51;
836 COMMIT;
837 SAVEPOINT recipe_migration;
838 IF ( cm_cmpt_dtl_cur%ROWCOUNT > l_cm_counter/50 )
839 THEN
840 l_cm_counter := 0;
841 EXIT;
842 END IF;
843 END LOOP;
844 /* Increment the counter */
845 l_counter := l_counter + 1;
846 IF ( l_counter > 5 )
847 THEN
848 COMMIT;
849 SAVEPOINT recipe_migration;
850 l_counter := 0;
851 END IF;
852
853 /* Delete duplicate VR id from gmd_recipe_validity_rule table */
854 DELETE
855 FROM gmd_recipe_validity_rules
856 WHERE recipe_validity_rule_id = update_eff_rec.fmeff_id;
857
858 insert_message (p_source_table => 'FM_FORM_EFF'
859 ,p_target_table => 'GMD_RECIPE_VALIDITY_RULES'
860 ,p_source_id => update_eff_rec.fmeff_id
861 ,p_target_id => l_fmeff_id
862 ,p_message => 'Deleted effectivity:'||update_eff_rec.fmeff_id||' Updated with effectivity:'||l_fmeff_id
863 ,p_error_type => 'E');
864 EXCEPTION
865 WHEN OTHERS
866 THEN
867 error_msg := SQLERRM;
868 insert_message (p_source_table => 'GMD_RECIPE_EFF_ASSOC'
869 ,p_target_table => 'PM_BTCH_HDR - CM_CMPT_DTL - GL_ITEM_CST - GMP_FORM_EFF'
870 ,p_source_id => l_fmeff_id
871 ,p_target_id => update_eff_rec.fmeff_id
872 ,p_message => error_msg
873 ,p_error_type => 'U');
874 END; /* End prior to end loop */
875 END IF; /* l_dup_counter > 1 */
876
877 /* increment this counter */
878 l_dup_counter := l_dup_counter + 1;
879
880 END LOOP; /* End loop for duplicate eff 2 */
881 END LOOP; /* End loop for duplicate eff 1 */
882 EXCEPTION
883 WHEN OTHERS
884 THEN
885 ROLLBACK TO recipe_migration;
886 error_msg := SQLERRM;
887 insert_message (p_source_table => 'Old GMD tables'
888 ,p_target_table => 'New GMD tables'
889 ,p_source_id => 'Unknown'
890 ,p_target_id => 'Unknown'
891 ,p_message => error_msg
892 ,p_error_type => 'U');
893 x_return_status := fnd_api.g_ret_sts_error;
894 fnd_msg_pub.count_and_get (
895 p_count => x_msg_count,
896 p_data => x_msg_data );
897 END migrate_recipe;
898
899 PROCEDURE insert_message (
900 p_source_table IN VARCHAR2,
901 p_target_table IN VARCHAR2,
902 p_source_id IN VARCHAR2,
903 p_target_id IN VARCHAR2,
904 p_message IN VARCHAR2,
905 p_error_type IN VARCHAR2
906 ) IS
907 PRAGMA autonomous_transaction;
908 BEGIN
909 INSERT INTO gmd_migration
910 ( migration_id,
911 source_table,
912 target_table,
913 source_id,
914 target_id,
915 message_text )
916 SELECT gmd_request_id_s.NEXTVAL,
917 p_source_table,
918 p_target_table,
919 p_source_id,
920 p_target_id,
921 p_message
922 FROM DUAL;
923 COMMIT;
924 END insert_message;
925
926 PROCEDURE qty_update_fxd_scaling IS
927 CURSOR fixed_scaling IS
928 SELECT grv.item_id, fmd.qty,fmd.item_um base_um, grv.std_qty, grv.item_um conv_um,
929 recipe_validity_rule_id rule_id, grv.min_qty, grv.max_qty
930 FROM fm_form_mst_b ffm,
931 fm_matl_dtl fmd,
932 gmd_recipes_b gr,
933 gmd_recipe_validity_rules grv
934 WHERE ffm.formula_id = fmd.formula_id
935 AND ffm.formula_id = gr.formula_id
936 AND grv.orgn_code = ffm.orgn_code
937 AND grv.recipe_id = gr.recipe_id
938 AND fmd.scale_type = 0
939 AND fmd.line_type = 1
940 AND ( grv.max_qty <> fmd.qty
941 OR grv.min_qty <> fmd.qty
942 OR grv.std_qty <> fmd.qty
943 )
944 AND grv.item_id = fmd.item_id;
945 x_out_qty NUMBER;
946 BEGIN
947 FOR x_rec IN fixed_scaling LOOP
948 /* Update only those records whose quantities are incorrect, depending upon the UOM's */
949 IF x_rec.base_um = x_rec.conv_um THEN
950 UPDATE gmd_recipe_validity_rules
951 SET max_qty = x_rec.qty,
952 min_qty = x_rec.qty,
953 std_qty = x_rec.qty
954 WHERE recipe_validity_rule_id = x_rec.rule_id;
955 ELSE
956 /*Calling the UOM conversation package, if the prduct UOM then the effectivitity UOM */
957 /*gmicuom.icuomcv(p_item_id,p_lot_id,P_min_qty,P_item_um,P_inv_item_um,X_inv_min_qty)*/
958 gmicuom.icuomcv (
959 x_rec.item_id,
960 0,
961 x_rec.qty,
962 x_rec.base_um,
963 x_rec.conv_um,
964 x_out_qty
965 );
966
967 IF ( x_rec.min_qty <> x_out_qty
968 OR x_rec.max_qty <> x_out_qty
969 OR x_rec.std_qty <> x_out_qty
970 ) THEN
971 UPDATE gmd_recipe_validity_rules
972 SET max_qty = x_out_qty,
973 min_qty = x_out_qty,
974 std_qty = x_out_qty
975 WHERE recipe_validity_rule_id = x_rec.rule_id;
976 END IF;
977 END IF;
978 END LOOP;
979 END qty_update_fxd_scaling;
980
981
982 END gmd_recipe_migration;