[Home] [Help]
PACKAGE BODY: APPS.GMD_OPRN_MIGRATION
Source
1 PACKAGE BODY GMD_OPRN_MIGRATION AS
2 /* $Header: GMDOMIGB.pls 120.1 2006/07/18 14:17:05 kmotupal noship $ */
3
4 PROCEDURE INSERT_GMD_OPERATIONS IS
5
6 CURSOR Cur_Formula_Id(prouting_id NUMBER) IS
7 SELECT formula_Id
8 FROM fm_form_eff_bak
9 WHERE routing_id = prouting_id;
10
11 CURSOR Cur_Routing_id(poprn_Id NUMBER) IS
12 SELECT routing_Id
13 FROM fm_rout_dtl
14 WHERE oprn_id = poprn_Id;
15
16 /*Bug#3601848 - Thomas Daniel */
17 /*Added exists clause as the migration was getting rerun for the customer */
18 /*upgrading from 11.5.9 to 11.5.10 and the deleted activity and resources */
19 /*were getting added */
20 CURSOR Cur_get_oprn_id IS
21 SELECT *
22 FROM gmd_operations_b o
23 WHERE operation_status IS NULL;
24
25 l_orgn_code VARCHAR2(6);
26 l_oprn_Id NUMBER;
27 l_operation_status GMD_STATUS.status_code%TYPE := '700';
28 l_formula_id NUMBER;
29 l_return_val NUMBER;
30 l_routing_id NUMBER;
31 error_msg VARCHAR2(2000);
32
33 BEGIN
34 FOR oprn_rout_rec IN Cur_get_oprn_ID LOOP
35 BEGIN
36 l_operation_status := '700'; -- Bug 5383916
37 /* To determine the operation status - which by default is 700 */
38 FOR get_routing_rec IN Cur_Routing_id(oprn_rout_rec.oprn_id) LOOP
39 FOR get_formula_rec IN Cur_Formula_id(get_routing_rec.routing_id) LOOP
40 l_return_val := GMDFMVAL_PUB.locked_effectivity_val(get_formula_rec.formula_Id);
41 IF l_return_val <> 0 THEN
42 l_operation_status := '900';
43 EXIT;
44 ELSE
45 l_operation_status := '700';
46 END IF;
47 END LOOP;
48 END LOOP;
49
50 /* If the operation is inactive or it is marked for purge
51 then we make it obsoleted */
52 IF ((oprn_rout_rec.inactive_ind = 1) OR (oprn_rout_rec.delete_mark = 1)) THEN
53 l_operation_status := '1000';
54 END IF;
55
56 /* Update the gmd_operations_b and tl table */
57 UPDATE gmd_operations_b
58 SET operation_status = l_operation_status,
59 effective_start_date = oprn_rout_rec.creation_date,
60 owner_orgn_code =
61 fnd_profile.value_specific('GEMMS_DEFAULT_ORGN',oprn_rout_rec.created_by)
62 WHERE oprn_id = oprn_rout_rec.oprn_id;
63
64 /*Bug#3601848 - Thomas Daniel */
65 /*Added call to insert the operation components passing the operation id */
66 /*to avoid a blind population of all the activities */
67 Insert_GMD_Operation_Comps (p_oprn_id => oprn_rout_rec.oprn_id);
68
69 EXCEPTION
70 WHEN OTHERS THEN
71 error_msg := SQLERRM;
72 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_OPRN_MST'
73 ,p_target_table => 'GMD_OPERATIONS'
74 ,p_source_id => oprn_rout_rec.oprn_id
75 ,p_target_id => oprn_rout_rec.oprn_id
76 ,p_message => error_msg
77 ,p_error_type => 'U');
78 END;
79 END LOOP; /* for insert in gmd operations */
80 END INSERT_GMD_OPERATIONS;
81
82 PROCEDURE INSERT_GMD_OPERATION_COMPS (P_Oprn_id IN NUMBER) IS
83 /*Bug#3601848 - Thomas Daniel */
84 /*Added oprn_id as parameter to this procedure and restricting the activities */
85 /*to only those for which the operation was not migrated earlier */
86 CURSOR get_activity IS
87 SELECT *
88 FROM fm_oprn_dtl_bak
89 WHERE oprn_id = P_oprn_id
90 ORDER BY activity;
91
92 v_activity varchar2(16);
93 v_oprn_line_id number := 0;
94 BEGIN
95 OPEN get_activity;
96 FETCH get_activity into v_activity_rec;
97 WHILE ( get_activity % FOUND ) LOOP
98 /* activity has been replaced with oprn_line_id to insert even same activity */
99 IF (v_activity_rec.oprn_line_id = v_oprn_line_id) THEN
100 insert_operation_resource(P_oprn_id,v_oprn_line_id);
101 ELSE
102 insert_operation_activity;
103 v_activity := v_activity_rec.activity;
104 v_oprn_line_id := v_activity_rec.oprn_line_id;
105 insert_operation_resource(P_oprn_id,v_oprn_line_id);
106 END IF;
107 FETCH get_activity INTO v_activity_rec;
108
109 END LOOP;
110 CLOSE get_activity;
111 /*Bug#3601848 - Thomas Daniel */
112 /*Commented the commit as this procedure is now being called from insert operation*/
113 -- COMMIT;
114 END INSERT_GMD_OPERATION_COMPS;
115
116 PROCEDURE INSERT_OPERATION_ACTIVITY IS
117 error_msg VARCHAR2(240);
118 BEGIN
119 INSERT INTO gmd_operation_activities
120 (oprn_line_id
121 ,oprn_id
122 ,activity
123 ,offset_interval
124 ,activity_factor
125 ,delete_mark
126 ,text_code
127 ,creation_date
128 ,created_by
129 ,last_updated_by
130 ,last_update_date
131 ,last_update_login
132 ,attribute1
133 ,attribute2
134 ,attribute3
135 ,attribute4
136 ,attribute5
137 ,attribute6
138 ,attribute7
139 ,attribute8
140 ,attribute9
141 ,attribute10
142 ,attribute11
143 ,attribute12
144 ,attribute13
145 ,attribute14
146 ,attribute15
147 ,attribute16
148 ,attribute17
149 ,attribute18
150 ,attribute19
151 ,attribute20
152 ,attribute21
153 ,attribute22
154 ,attribute23
155 ,attribute24
156 ,attribute25
157 ,attribute26
158 ,attribute27
159 ,attribute28
160 ,attribute29
161 ,attribute30
162 ,attribute_category)
163 SELECT v_activity_rec.oprn_line_id
164 ,v_activity_rec.oprn_id
165 ,v_activity_rec.activity
166 ,v_activity_rec.offset_interval
167 ,1 /* Activity Factor */
168 ,0 /* Delete mark */
169 ,v_activity_rec.text_code
170 ,v_activity_rec.creation_date
171 ,v_activity_rec.created_by
172 ,v_activity_rec.last_updated_by
173 ,v_activity_rec.last_update_date
174 ,v_activity_rec.last_update_login
175 ,v_activity_rec.attribute1
176 ,v_activity_rec.attribute2
177 ,v_activity_rec.attribute3
178 ,v_activity_rec.attribute4
179 ,v_activity_rec.attribute5
180 ,v_activity_rec.attribute6
181 ,v_activity_rec.attribute7
182 ,v_activity_rec.attribute8
183 ,v_activity_rec.attribute9
184 ,v_activity_rec.attribute10
185 ,v_activity_rec.attribute11
186 ,v_activity_rec.attribute12
187 ,v_activity_rec.attribute13
188 ,v_activity_rec.attribute14
189 ,v_activity_rec.attribute15
190 ,v_activity_rec.attribute16
191 ,v_activity_rec.attribute17
192 ,v_activity_rec.attribute18
193 ,v_activity_rec.attribute19
194 ,v_activity_rec.attribute20
195 ,v_activity_rec.attribute21
196 ,v_activity_rec.attribute22
197 ,v_activity_rec.attribute23
198 ,v_activity_rec.attribute24
199 ,v_activity_rec.attribute25
200 ,v_activity_rec.attribute26
201 ,v_activity_rec.attribute27
202 ,v_activity_rec.attribute28
203 ,v_activity_rec.attribute29
204 ,v_activity_rec.attribute30
205 ,v_activity_rec.attribute_category
206 FROM dual
207 WHERE NOT EXISTS (SELECT 1
208 FROM gmd_operation_activities
209 WHERE oprn_line_id = v_activity_rec.oprn_line_id);
210 EXCEPTION
211 WHEN OTHERS THEN
212 error_msg := SQLERRM;
213 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_OPRN_DTL'
214 ,p_target_table => 'GMD_OPERATION_ACTIVITIES'
215 ,p_source_id => v_activity_rec.activity
216 ,p_target_id => v_activity_rec.activity
217 ,p_message => error_msg
218 ,p_error_type => 'U');
219 END INSERT_OPERATION_ACTIVITY;
220
221
222 PROCEDURE INSERT_OPERATION_RESOURCE(p_oprn_id NUMBER,p_oprn_line_id NUMBER) IS
223 CURSOR get_resource_capacity IS
224 SELECT min_capacity
225 ,max_capacity
226 ,capacity_uom
227 FROM cr_rsrc_dtl
228 WHERE resources = v_activity_rec.resources;
229
230 v_min_capacity NUMBER;
231 v_max_capacity NUMBER;
232 v_capacity_uom varchar2(4);
233 v_process_uom varchar2(4);
234 error_msg varchar2(240);
235 invalid_err exception;--BUG#3316385
236 BEGIN
237 OPEN get_resource_capacity;
238 FETCH get_resource_capacity INTO
239 v_min_capacity
240 ,v_max_capacity
241 ,v_capacity_uom;
242 CLOSE get_resource_capacity;
243
244 SELECT PROCESS_QTY_UM
245 INTO v_process_uom
246 FROM gmd_operations
247 WHERE oprn_id = p_oprn_id;
248
249 --BEGIN BUG#3316385
250 IF v_activity_rec.process_qty = 0 AND v_activity_rec.resource_usage <> 0
251 AND v_activity_rec.scale_type in (1,2) THEN
252 RAISE invalid_err;
253 END IF;
254 --END BUG#3316385
255 INSERT INTO gmd_operation_resources (
256 oprn_line_id
257 ,resources
258 ,resource_usage
259 ,resource_count
260 ,usage_um
261 ,process_qty
262 ,process_uom /* Process UOM */
263 ,prim_rsrc_ind
264 ,scale_type
265 ,cost_analysis_code
266 ,cost_cmpntcls_id
267 ,offset_interval
268 ,delete_mark
269 ,text_code
270 ,Min_Capacity
271 ,Max_capacity
272 ,capacity_uom /* Capacity UOM */
273 ,attribute_category
274 ,attribute1
275 ,attribute2
276 ,attribute3
277 ,attribute4
278 ,attribute5
279 ,attribute6
280 ,attribute7
281 ,attribute8
282 ,attribute9
283 ,attribute10
284 ,attribute11
285 ,attribute12
286 ,attribute13
287 ,attribute14
288 ,attribute15
289 ,attribute16
290 ,attribute17
291 ,attribute18
292 ,attribute19
293 ,attribute20
294 ,attribute21
295 ,attribute22
296 ,attribute23
297 ,attribute24
298 ,attribute25
299 ,attribute26
300 ,attribute27
301 ,attribute28
302 ,attribute29
303 ,attribute30
304 ,creation_date
305 ,created_by
306 ,last_update_date
307 ,last_updated_by
308 ,last_update_login
309 ,Process_parameter_1
310 ,Process_parameter_2
311 ,Process_parameter_3
312 ,Process_parameter_4
313 ,Process_parameter_5)
314 SELECT p_oprn_line_id
315 ,v_activity_rec.resources
316 ,v_activity_rec.resource_usage
317 ,v_activity_rec.resource_count
318 ,v_activity_rec.usage_um
319 ,v_activity_rec.process_qty
320 ,v_process_uom /* Process UOM */
321 ,v_activity_rec.prim_rsrc_ind
322 ,v_activity_rec.scale_type
323 ,v_activity_rec.cost_analysis_code
324 ,v_activity_rec.cost_cmpntcls_id
325 ,v_activity_rec.offset_interval
326 ,0 /* delete mark */
327 ,v_activity_rec.text_code
328 ,v_min_capacity /* Min Capacity */
329 ,v_max_capacity /* Max capacity */
330 ,v_capacity_uom /* Capacity UOM */
331 ,v_activity_rec.attribute_category
332 ,v_activity_rec.attribute1
333 ,v_activity_rec.attribute2
334 ,v_activity_rec.attribute3
335 ,v_activity_rec.attribute4
336 ,v_activity_rec.attribute5
337 ,v_activity_rec.attribute6
338 ,v_activity_rec.attribute7
339 ,v_activity_rec.attribute8
340 ,v_activity_rec.attribute9
341 ,v_activity_rec.attribute10
342 ,v_activity_rec.attribute11
343 ,v_activity_rec.attribute12
344 ,v_activity_rec.attribute13
345 ,v_activity_rec.attribute14
346 ,v_activity_rec.attribute15
347 ,v_activity_rec.attribute16
348 ,v_activity_rec.attribute17
349 ,v_activity_rec.attribute18
350 ,v_activity_rec.attribute19
351 ,v_activity_rec.attribute20
352 ,v_activity_rec.attribute21
353 ,v_activity_rec.attribute22
354 ,v_activity_rec.attribute23
355 ,v_activity_rec.attribute24
356 ,v_activity_rec.attribute25
357 ,v_activity_rec.attribute26
358 ,v_activity_rec.attribute27
359 ,v_activity_rec.attribute28
360 ,v_activity_rec.attribute29
361 ,v_activity_rec.attribute30
362 ,v_activity_rec.creation_date
363 ,v_activity_rec.created_by
364 ,v_activity_rec.last_update_date
365 ,v_activity_rec.last_updated_by
366 ,v_activity_rec.last_update_login
367 ,NULL /* Process parameter 1 */
368 ,NULL /* Process parameter 2 */
369 ,NULL /* Process parameter 3 */
370 ,NULL /* Process parameter 4 */
371 ,NULL /* Process parameter 5 */
372 FROM dual
373 WHERE NOT EXISTS (SELECT 1
374 FROM gmd_operation_resources
375 WHERE oprn_line_id = p_oprn_line_id AND
376 resources = v_activity_rec.resources);
377
378 EXCEPTION
379 --BEGIN BUG#3316385
380 WHEN invalid_err THEN
381 error_msg := 'Invalid Combination of Process Quantity,Usage and Scale Type';
382 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_OPRN_DTL'
383 ,p_target_table => 'GMD_OPERATION_RESOURCES'
384 ,p_source_id => v_activity_rec.resources
385 ,p_target_id => v_activity_rec.resources
386 ,p_message => error_msg
387 ,p_error_type => 'U');
388 --END BUG#3316385
389 WHEN OTHERS THEN
390 error_msg := SQLERRM;
391 GMD_RECIPE_MIGRATION.insert_message (p_source_table => 'FM_OPRN_DTL'
392 ,p_target_table => 'GMD_OPERATION_RESOURCES'
393 ,p_source_id => v_activity_rec.resources
394 ,p_target_id => v_activity_rec.resources
395 ,p_message => error_msg
396 ,p_error_type => 'U');
397 END INSERT_OPERATION_RESOURCE;
398
399 END GMD_OPRN_MIGRATION;