1 PACKAGE BODY GMD_FORM_MIGRATION AS
2 /* $Header: GMDFMIGB.pls 120.1 2005/08/12 06:18:41 txdaniel noship $ pxkumar*/
3
4 PROCEDURE Insert_form_status
5 IS
6 /* Need to migrate data from fm_form_mst to fm_form_mst_vl */
7 CURSOR formula_cur_vl IS
8 SELECT * from fm_form_mst m1
9 WHERE EXISTS ( Select 1
10 from fm_form_mst
11 Where formula_no = m1.formula_no AND
12 formula_vers = m1.formula_vers AND
13 formula_status IS NULL);
14
15 /* BUG # 2562689 - Cursor that gets all formulas that have
16 inactive items */
17 CURSOR get_inactive_item_formula IS
18 SELECT distinct d.formula_id
19 FROM ic_item_mst i, fm_matl_dtl d
20 WHERE i.item_id = d.item_id
21 AND i.delete_mark = 1
22 GROUP BY d.formula_id;
23
24 l_formula_id NUMBER;
25 l_orgn_code VARCHAR2(6);
26 l_formula_status GMD_STATUS.status_code%type;
27 formula_rowid VARCHAR2(32);
28 l_total_output_qty NUMBER;
29 l_total_input_qty NUMBER;
30 l_uom VARCHAR2(4);
31 l_return_val NUMBER;
32 l_return_status varchar2(1);
33 l_msg_data varchar2(240);
34 l_msg_count NUMBER;
35 error_msg varchar2(240);
36 /* TKW 6/25/2002 B2428431 */
37 l_fm_desc fm_form_mst.formula_desc1%TYPE;
38
39 l_inactive_ind NUMBER;
40
41 TOTAL_OUTPUT_NOT_CALC EXCEPTION ;
42
43 BEGIN
44
45 /* Update the apps.fm_form_mst with appropriate status and qty values */
46 FOR formula_rec IN formula_cur_vl LOOP
47 BEGIN
48 /* get the qty and uoms */
49 GMD_COMMON_VAL.CALCULATE_TOTAL_QTY (
50 formula_id => formula_rec.formula_id ,
51 x_product_qty => l_total_output_qty ,
52 x_ingredient_qty => l_total_input_qty ,
53 x_uom => l_uom ,
54 x_msg_count => l_msg_count ,
55 x_msg_data => l_msg_data ,
56 x_return_status => l_return_status);
57
58 /* function to get formula status */
59 l_return_val := GMDFMVAL_PUB.locked_effectivity_val(formula_rec.formula_id);
60 IF (l_return_val <> 0) THEN
61 l_formula_status := '900';
62 ELSE
63 l_formula_status := '700';
64 END IF;
65
66 /* If this formula is marked for purge or incative set the status to obsolete */
67 l_inactive_ind := formula_rec.inactive_ind;
68
69 /* bug 2912872 Seting the inactive formulas to active*/
70 IF (formula_rec.inactive_ind = 1) THEN
71 l_formula_status := '1000';
72 l_inactive_ind := 0;
73 END IF;
74
75 IF (formula_rec.delete_mark = 1) THEN
76 l_formula_status := '1000';
77 END IF;
78
79 /* TKW 6/25/2002 B2428431 Concatenate formula number and version if formula desc is null */
80 IF (formula_rec.FORMULA_DESC1 is null) THEN
81 l_fm_desc := formula_rec.formula_no || ' ' || formula_rec.formula_vers;
82 ELSE
83 l_fm_desc := formula_rec.formula_desc1;
84 END IF;
85
86 /* Define the creation and owner orgn values */
87 l_orgn_code := fnd_profile.value_specific('GEMMS_DEFAULT_ORGN',formula_rec.created_by);
88
89 /* Call the formula update MLS API*/
90 UPDATE fm_form_mst_b
91 SET orgn_code = l_orgn_code,
92 total_output_qty = l_total_output_qty,
93 total_input_qty = l_total_input_qty,
94 formula_uom = l_uom,
95 formula_status = l_formula_status,
96 owner_id = formula_rec.created_by,
97 inactive_ind = l_inactive_ind
98 WHERE formula_id = formula_rec.formula_id;
99
100 IF formula_rec.FORMULA_DESC1 IS NULL THEN
101 UPDATE fm_form_mst_tl
102 SET formula_desc1 = l_fm_desc
103 WHERE formula_id = formula_rec.formula_id
104 AND userenv('LANG') IN (language, source_lang);
105 END IF;
106 EXCEPTION
107 WHEN TOTAL_OUTPUT_NOT_CALC THEN
108 error_msg := 'The Total Output Quantity for this formula cannot be calculated';
109 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_FORM_MST'
110 ,p_target_table => 'FM_FORM_MST'
111 ,p_source_id => formula_rec.formula_id
112 ,p_target_id => formula_rec.formula_id
113 ,p_message => error_msg
114 ,p_error_type => 'E');
115 WHEN OTHERS THEN
116 error_msg := SQLERRM;
117 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_FORM_MST'
118 ,p_target_table => 'FM_FORM_MST'
119 ,p_source_id => formula_rec.formula_id
120 ,p_target_id => formula_rec.formula_id
121 ,p_message => error_msg
122 ,p_error_type => 'U');
123 END; /* End prior to end loop */
124 END LOOP;
125
126 /* Bug # 2562689 - Obsolete all formulas whose item are inactive */
127 FOR get_inactive_form_rec IN get_inactive_item_formula LOOP
128 UPDATE fm_form_mst_b
129 SET formula_status = '1000'
130 WHERE formula_id = get_inactive_form_rec.formula_id;
131 END LOOP;
132
133 END Insert_form_status ;
134
135 PROCEDURE MIGRATE_FORMULA_DETAIL IS
136 error_msg VARCHAR2(240);
137 BEGIN
138 /*Bug 2980227 - Thomas Daniel */
139 /*Fixed the following code for not resetting the values for customers who are upgrading */
140 /*Introduced the NVL statements to update the defaults only if they were not filled in earlier */
141 -- FOR my_rec IN (select * from fm_matl_dtl) LOOP
142
143 BEGIN
144 UPDATE fm_matl_dtl
145 SET contribute_step_qty_ind = NVL(contribute_step_qty_ind, 'Y'),
146 scale_multiple = NVL(scale_multiple, 0),
147 scale_rounding_variance = NVL(scale_rounding_variance, 0),
148 contribute_yield_ind = NVL(contribute_yield_ind, 'Y');
149
150 /*The customers who are migrating from FP prior to G would have only scale types as */
151 /*0 or 1 so the decode is not required for setting the contribute to yield ind */
152 -- = decode(my_rec.scale_type,0,'Y',1,'Y',2,'N',3,'N')
153 -- where formulaline_id = my_rec.formulaline_id;
154
155 EXCEPTION
156 WHEN OTHERS THEN
157 error_msg := SQLERRM;
158 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_MATL_DTL'
159 ,p_target_table => 'FM_MATL_DTL'
160 ,p_source_id => NULL
161 ,p_target_id => NULL
162 ,p_message => error_msg
163 ,p_error_type => 'U');
164 END;
165 -- END LOOP;
166 END MIGRATE_FORMULA_DETAIL;
167
168 END GMD_FORM_MIGRATION;
[Home] [Help]