[Home] [Help]
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 )
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
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,
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:
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;
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
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:
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;
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
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 */
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 )
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) =
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
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