[Home] [Help]
PACKAGE BODY: APPS.GMD_LM_MIGRATION
Source
1 PACKAGE BODY GMD_LM_MIGRATION AS
2 /* $Header: GMDLMMGB.pls 120.3 2005/10/05 08:49:43 txdaniel noship $ */
3
4 P_run_id NUMBER;
5 P_line_no NUMBER DEFAULT 0;
6
7 /* ***************************************************************
8 * PROCEDURE generate_tech_parm_id
9 *
10 * Synopsis : generate_tech_parm_id
11 *
12 * Description : From OPM_PF K and above the technical parameters
13 * base and translation table includes a surrogate key
14 * tech_parm_id. This surrogate needs to be populated
15 * using a sequence generator.
16 *
17 * History :
18 * Shyam Sitaraman 03/10/03 Initial Implementation
19 * *************************************************************** */
20 PROCEDURE generate_tech_parm_id IS
21 CURSOR C_get_tech_parms IS
22 SELECT
23 B.tech_parm_name,
24 B.orgn_code
25 FROM GMD_TECH_PARAMETERS_B B
26 WHERE B.orgn_code IS NOT NULL
27 AND B.tech_parm_id = 0;
28
29 l_row_id VARCHAR2(40);
30 l_tech_parm_id NUMBER;
31
32 BEGIN
33
34 FOR C_get_tech_parms_rec IN C_get_tech_parms LOOP
35 SELECT gmd_tech_parm_id_s.nextval INTO l_tech_parm_id
36 FROM sys.dual;
37
38 /* Create tech_parm_id for the hdr table - lm_tech_hdr */
39 UPDATE gmd_tech_parameters_b
40 SET tech_parm_id = l_tech_parm_id
41 WHERE tech_parm_name = C_get_tech_parms_rec.tech_parm_name
42 AND orgn_code = C_get_tech_parms_rec.orgn_code;
43
44 UPDATE gmd_tech_parameters_tl
45 SET tech_parm_id = l_tech_parm_id
46 WHERE tech_parm_name = C_get_tech_parms_rec.tech_parm_name
47 AND orgn_code = C_get_tech_parms_rec.orgn_code;
48
49 /* Create tech_parm_id for the dtl table - lm_tech_dtl */
50 UPDATE lm_tech_dtl
51 SET tech_parm_id = l_tech_parm_id
52 WHERE tech_parm_name = C_get_tech_parms_rec.tech_parm_name
53 AND orgn_code = C_get_tech_parms_rec.orgn_code;
54
55 /* Create tech_parm_id for LM_SPRD_TEC */
56 UPDATE lm_sprd_tec
57 SET tech_parm_id = l_tech_parm_id
58 WHERE tech_parm_name = C_get_tech_parms_rec.tech_parm_name
59 AND orgn_code = C_get_tech_parms_rec.orgn_code;
60
61 /* Create tech_parm_id for lm_sprd_prm */
62 UPDATE lm_sprd_prm
63 SET tech_parm_id = l_tech_parm_id
64 WHERE tech_parm_name = C_get_tech_parms_rec.tech_parm_name
65 AND orgn_code = C_get_tech_parms_rec.orgn_code;
66
67 END LOOP;
68 EXCEPTION
69 WHEN OTHERS THEN
70 P_line_no := P_line_no + 1;
71 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
72 ,p_table_name => 'GMD_TECH_PARAMETERS'
73 ,p_db_error => sqlerrm
74 ,p_param1 => NULL
75 ,p_param2 => NULL
76 ,p_param3 => NULL
77 ,p_param4 => NULL
78 ,p_param5 => NULL
79 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
80 ,p_message_type => 'D'
81 ,p_line_no => P_line_no
82 ,p_position=> 1
83 ,p_base_message=> NULL);
84
85 END generate_tech_parm_id;
86
87 /* ***************************************************************
88 * PROCEDURE populate_lm_sprd_dtl_sec_qty
89 *
90 * Synopsis : populate_lm_sprd_dtl_sec_qty
91 *
92 * Description : Get all items from lm_sprd_dtl that has dual uom
93 * and derives the secondary qty a (i.e the qty value
94 * converted from primary to secondary uom).
95 *
96 *
97 * History :
98 * Shyam Sitaraman 03/10/03 Initial Implementation
99 * *************************************************************** */
100 PROCEDURE populate_lm_sprd_dtl_sec_qty IS
101 CURSOR C_check_Item_dual_ind(vItem_id NUMBER) IS
102 SELECT item_um2
103 FROM ic_item_mst_b
104 Where item_id = vItem_id
105 AND dualum_ind > 0;
106
107 CURSOR C_get_lm_sprd_dtl IS
108 SELECT *
109 FROM lm_sprd_dtl;
110
111 l_dual_um ic_item_mst_b.item_um2%TYPE;
112
113 BEGIN
114 FOR lm_sprd_dtl_rec IN C_get_lm_sprd_dtl LOOP
115 OPEN C_check_Item_dual_ind(lm_sprd_dtl_rec.Item_id);
116 FETCH C_check_Item_dual_ind INTO l_dual_um;
117 IF C_check_Item_dual_ind%FOUND THEN
118 UPDATE lm_sprd_dtl
119 SET secondary_qty = GMICUOM.UOM_CONVERSION (lm_sprd_dtl_rec.Item_id,0,
120 lm_sprd_dtl_rec.qty,
121 lm_sprd_dtl_rec.item_um,
122 l_dual_um,0),
123 secondary_um = l_dual_um
124 WHERE line_id = lm_sprd_dtl_rec.line_id
125 AND sprd_id = lm_sprd_dtl_rec.sprd_id;
126 END IF;
127 CLOSE C_check_Item_dual_ind;
128 END LOOP;
129 EXCEPTION
130 WHEN OTHERS THEN
131 P_line_no := P_line_no + 1;
132 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
133 ,p_table_name => 'LM_SPRD_DTL'
134 ,p_db_error => sqlerrm
135 ,p_param1 => NULL
136 ,p_param2 => NULL
137 ,p_param3 => NULL
138 ,p_param4 => NULL
139 ,p_param5 => NULL
140 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
141 ,p_message_type => 'D'
142 ,p_line_no => P_line_no
143 ,p_position=> 1
144 ,p_base_message=> NULL);
145 END populate_lm_sprd_dtl_sec_qty;
146
147
148 /* ***************************************************************
149 * PROCEDURE populate_parsed_expressions
150 *
151 * Synopsis : populate_parsed_expressions
152 *
153 * Description : Populates the gmd_parsed_expression table with
154 * existing technical parmeters that are of non
155 * expression and expression type.
156 *
157 *
158 * History :
159 * Shyam Sitaraman 03/10/03 Initial Implementation
160 * *************************************************************** */
161 PROCEDURE populate_parsed_expressions IS
162
163 CURSOR C_get_exp_tech_parm IS
164 SELECT *
165 FROM gmd_tech_parameters_b
166 WHERE data_type = 4 OR data_type = 11
167 ORDER by tech_parm_id;
168
169 l_return_status VARCHAR2(1) := 'S';
170 l_msg_data VARCHAR2(2000);
171 l_msg_index NUMBER(5);
172 l_user_id NUMBER(15);
173 BEGIN
174 l_user_id := FND_PROFILE.VALUE('USER_ID');
175 -- Insert all expressions
176 FOR get_exp_rec IN C_get_exp_tech_parm LOOP
177 FND_PROFILE.PUT('USER_ID', get_exp_rec.created_by);
178 gmd_expression_mig_util.parse_expression (
179 p_orgn_code => get_exp_rec.orgn_code ,
180 p_tech_parm_id => get_exp_rec.tech_parm_id ,
181 p_expression => get_exp_rec.expression_char ,
182 x_return_status => l_return_status );
183 IF l_return_status <> FND_API.g_ret_sts_success THEN
184 FND_MSG_PUB.GET(p_msg_index => 1,
185 p_data => l_msg_data,
186 p_encoded => 'F',
187 p_msg_index_out => l_msg_index);
188 P_line_no := P_line_no + 1;
189 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
190 ,p_table_name => 'GMD_PARSED_EXPRESSION'
191 ,p_db_error => l_msg_data
192 ,p_param1 => get_exp_rec.tech_parm_name
193 ,p_param2 => get_exp_rec.orgn_code
194 ,p_param3 => NULL
195 ,p_param4 => NULL
196 ,p_param5 => NULL
197 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
198 ,p_message_type => 'D'
199 ,p_line_no => P_line_no
200 ,p_position=> 1
201 ,p_base_message=> NULL);
202 END IF;
203 END LOOP;
204 IF l_user_id IS NOT NULL THEN
205 FND_PROFILE.PUT('USER_ID', l_user_id);
206 END IF;
207 EXCEPTION
208 WHEN OTHERS THEN
209 P_line_no := P_line_no + 1;
210 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
211 ,p_table_name => 'GMD_PARSED_EXPRESSION'
212 ,p_db_error => sqlerrm
213 ,p_param1 => NULL
214 ,p_param2 => NULL
215 ,p_param3 => NULL
216 ,p_param4 => NULL
217 ,p_param5 => NULL
218 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
219 ,p_message_type => 'D'
220 ,p_line_no => P_line_no
221 ,p_position=> 1
222 ,p_base_message=> NULL);
223 END populate_parsed_expressions;
224
225 /* ***************************************************************
226 * PROCEDURE insert_gmd_tech_seq_hdr
227 *
228 * Synopsis : insert_gmd_tech_seq_hdr(xTech_seq_id)
229 *
230 * Description : This function cannot be called independently. It gets
231 * called within Procedure insert_gmd_tech_seq_comps.
232 * It inserts a row in gmd_tech_sequence_hdr table and
233 * it returns the Tech_seq_id, which is used to insert the
234 * details in gmd_tech_sequence_dtl table.
235 * History :
236 * Shyam Sitaraman 02/28/03 Initial Implementation
237 * *************************************************************** */
238 PROCEDURE insert_gmd_tech_seq_hdr(x_tech_seq_id OUT NOCOPY NUMBER) IS
239 BEGIN
240 SELECT gmd_tech_seq_id_s.nextval INTO x_tech_seq_id
241 FROM sys.dual;
242
243 INSERT INTO gmd_technical_sequence_hdr
244 ( tech_seq_id
245 , orgn_code
246 , item_id
247 , category_id
248 , delete_mark
249 , text_code
250 , creation_date
251 , created_by
252 , last_update_date
253 , last_updated_by
254 , last_update_login
255 ) SELECT
256 x_tech_seq_id
257 , v_lm_prlt_asc_rec.orgn_code
258 , null
259 , null
260 , v_lm_prlt_asc_rec.delete_mark
261 , v_lm_prlt_asc_rec.text_code
262 , v_lm_prlt_asc_rec.creation_date
263 , v_lm_prlt_asc_rec.created_by
264 , v_lm_prlt_asc_rec.last_update_date
265 , v_lm_prlt_asc_rec.last_updated_by
266 , v_lm_prlt_asc_rec.last_update_login
267 FROM
268 sys.dual
269 WHERE NOT EXISTS (SELECT 1
270 FROM gmd_technical_sequence_hdr
271 WHERE orgn_code = v_lm_prlt_asc_rec.orgn_code);
272 EXCEPTION
273 WHEN OTHERS THEN
274 P_line_no := P_line_no + 1;
275 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
276 ,p_table_name => 'GMD_TECHNICAL_SEQUENCE_HDR'
277 ,p_db_error => sqlerrm
278 ,p_param1 => NULL
279 ,p_param2 => NULL
280 ,p_param3 => NULL
281 ,p_param4 => NULL
282 ,p_param5 => NULL
283 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
284 ,p_message_type => 'D'
285 ,p_line_no => P_line_no
286 ,p_position=> 1
287 ,p_base_message=> NULL);
288 END insert_gmd_tech_seq_hdr;
289
290 /* ***************************************************************
291 * PROCEDURE insert_gmd_tech_seq_dtl
292 *
293 * Synopsis : insert_gmd_tech_seq_dtl(10,'DENSITY',1);
294 *
295 * Description : This function cannot be called independently. It gets
296 * called within Procedure insert_gmd_tech_seq_comps.
297 * After it inserts rows in gmd_tech_sequence_dtl table.
298 * History :
299 * Shyam Sitaraman 02/28/03 Initial Implementation
300 * *************************************************************** */
301 PROCEDURE insert_gmd_tech_seq_dtl( vTech_seq_id NUMBER) IS
302
303 CURSOR C_get_tech_parm_id(vtech_parm_name VARCHAR2
304 ,vorgn_code VARCHAR2) IS
305 SELECT tech_parm_id
306 FROM gmd_tech_parameters_b
307 WHERE tech_parm_name = vtech_parm_name
308 AND orgn_code = vorgn_code;
309
310 l_tech_parm_id NUMBER;
311 E_Tech_parm_Not_Found EXCEPTION;
312 BEGIN
313 IF (vTech_seq_id IS NOT NULL) THEN
314 /* Get the tech parm id */
315 OPEN C_get_tech_parm_id(v_lm_prlt_asc_rec.tech_parm_name
316 ,v_lm_prlt_asc_rec.orgn_code);
317 FETCH C_get_tech_parm_id INTO l_tech_parm_id;
318 IF C_get_tech_parm_id%NOTFOUND THEN
319 RAISE E_Tech_parm_Not_Found;
320 END IF;
321 CLOSE C_get_tech_parm_id;
322
323 INSERT INTO gmd_technical_sequence_dtl
324 ( tech_seq_id
325 , tech_parm_id
326 , sort_seq
327 , text_code
328 , creation_date
329 , created_by
330 , last_update_date
331 , last_updated_by
332 , last_update_login )
333 SELECT
334 vTech_seq_id
335 , l_tech_parm_id
336 , v_lm_prlt_asc_rec.sort_seq
337 , v_lm_prlt_asc_rec.text_code
338 , v_lm_prlt_asc_rec.creation_date
339 , v_lm_prlt_asc_rec.created_by
340 , v_lm_prlt_asc_rec.last_update_date
341 , v_lm_prlt_asc_rec.last_updated_by
342 , v_lm_prlt_asc_rec.last_update_login
343 FROM sys.dual
344 WHERE NOT EXISTS
345 (SELECT 1 FROM gmd_technical_sequence_dtl
346 WHERE tech_parm_id = l_tech_parm_id
347 AND tech_seq_id = vTech_seq_id);
348 END IF;
349
350 EXCEPTION
351 WHEN E_Tech_parm_Not_Found THEN
352 P_line_no := P_line_no + 1;
353 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
354 ,p_table_name => 'GMD_TECHNICAL_SEQUENCE_DTL'
355 ,p_db_error => sqlerrm
356 ,p_param1 => v_lm_prlt_asc_rec.tech_parm_name
357 ,p_param2 => v_lm_prlt_asc_rec.orgn_code
358 ,p_param3 => NULL
359 ,p_param4 => NULL
360 ,p_param5 => NULL
361 ,p_message_token => 'GMD_TECH_PARM_NOTFOUND'
362 ,p_message_type => 'D'
363 ,p_line_no => P_line_no
364 ,p_position=> 1
365 ,p_base_message=> NULL);
366
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 => 'GMD_TECHNICAL_SEQUENCE_DTL'
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 insert_gmd_tech_seq_dtl;
383
384 /* ***************************************************************
385 * PROCEDURE insert_gmd_tech_seq_comps
386 *
387 * Synopsis : insert_gmd_tech_seq_comps;
388 *
389 * Description : This procedure can be called independently. Although
390 * it is recommended to call the main program (procedure)
391 * GMD_LM_MIGRATION.Run which in turn calls this procedure.
392 * Data from lm_prlt_asc is sorted based on orgn_code.
393 * For each header row it calls procedure insert_gmd_tech_seq_hdr
394 * and for all details for this header it call procedure
395 * insert_gmd_tech_seq_dtl.
396 *
397 * History :
398 * Shyam Sitaraman 02/28/03 Initial Implementation
399 * *************************************************************** */
400 PROCEDURE insert_gmd_tech_seq_comps IS
401 CURSOR C_get_lm_prlt_asc IS
402 SELECT *
403 FROM lm_prlt_asc_bak
404 ORDER BY orgn_code;
405 l_orgn_code VARCHAR2(4);
406 l_tech_seq_id NUMBER;
407 BEGIN
408 OPEN C_get_lm_prlt_asc;
409 FETCH C_get_lm_prlt_asc INTO v_lm_prlt_asc_rec;
410 WHILE (C_get_lm_prlt_asc%FOUND) LOOP
411 IF (v_lm_prlt_asc_rec.orgn_code = l_orgn_code) THEN
412 -- Hdr row already inserted, only insert in the detail table
413 insert_gmd_tech_seq_dtl(l_tech_seq_id);
414 ELSE
415 -- Insert this row in the header table
416 insert_gmd_tech_seq_hdr(x_tech_seq_id => l_tech_seq_id);
417 -- Assign the unique key (orgn code value)
418 l_orgn_code := v_lm_prlt_asc_rec.orgn_code;
419 -- Then insert this row in the detail table
420 insert_gmd_tech_seq_dtl(l_tech_seq_id);
421 END IF;
422 FETCH C_get_lm_prlt_asc INTO v_lm_prlt_asc_rec;
423 END LOOP;
424 CLOSE C_get_lm_prlt_asc;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 P_line_no := P_line_no + 1;
429 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
430 ,p_table_name => 'GMD_TECHNICAL_SEQUENCE_HDR'
431 ,p_db_error => sqlerrm
432 ,p_param1 => NULL
433 ,p_param2 => NULL
434 ,p_param3 => NULL
435 ,p_param4 => NULL
436 ,p_param5 => NULL
437 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
438 ,p_message_type => 'D'
439 ,p_line_no => P_line_no
440 ,p_position=> 1
441 ,p_base_message=> NULL);
442 END insert_gmd_tech_seq_comps;
443
444
445 /* ***************************************************************
446 * PROCEDURE insert_gmd_tech_data_hdr
447 *
448 * Synopsis : insert_gmd_tech_data_hdr(xTech_data_id)
449 *
450 * Description : This function cannot be called independently. It gets
451 * called within Procedure insert_gmd_tech_data_comps.
452 * It inserts a row in gmd_technical_data_hdr table and also
453 * returns the Tech_data_id, which is used to insert
454 * details in gmd_technical_data_dtl table.
455 * History :
456 * Shyam Sitaraman 02/28/03 Initial Implementation
457 * *************************************************************** */
458 PROCEDURE insert_gmd_tech_data_hdr(x_tech_data_id OUT NOCOPY NUMBER) IS
459
460 BEGIN
461 SELECT gmd_tech_data_id_s.nextval INTO x_tech_data_id
462 FROM sys.dual;
463
464 INSERT INTO gmd_technical_data_hdr
465 ( tech_data_id
466 , orgn_code
467 , item_id
468 , lot_id
469 , formula_id
470 , batch_id
471 , delete_mark
472 , text_code
473 , creation_date
474 , created_by
475 , last_update_date
476 , last_updated_by
477 , last_update_login
478 ) SELECT
479 x_tech_data_id
480 , v_lm_item_dat_rec.orgn_code
481 , v_lm_item_dat_rec.item_id
482 , Decode(v_lm_item_dat_rec.lot_id, 0, Null, v_lm_item_dat_rec.lot_id)
483 , Decode(v_lm_item_dat_rec.formula_id, 0, Null, v_lm_item_dat_rec.formula_id)
484 , Null
485 , v_lm_item_dat_rec.delete_mark
486 , v_lm_item_dat_rec.text_code
487 , v_lm_item_dat_rec.creation_date
488 , v_lm_item_dat_rec.created_by
489 , v_lm_item_dat_rec.last_update_date
490 , v_lm_item_dat_rec.last_updated_by
491 , v_lm_item_dat_rec.last_update_login
492 FROM
493 sys.dual
494 WHERE NOT EXISTS (SELECT 1
495 FROM gmd_technical_data_hdr
496 WHERE orgn_code = v_lm_item_dat_rec.orgn_code
497 AND item_id = v_lm_item_dat_rec.item_id);
498
499 EXCEPTION
500
501 WHEN OTHERS THEN
502 P_line_no := P_line_no + 1;
503 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
504 ,p_table_name => 'GMD_TECHNICAL_DATA_HDR'
505 ,p_db_error => sqlerrm
506 ,p_param1 => NULL
507 ,p_param2 => NULL
508 ,p_param3 => NULL
509 ,p_param4 => NULL
510 ,p_param5 => NULL
511 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
512 ,p_message_type => 'D'
513 ,p_line_no => P_line_no
514 ,p_position=> 1
515 ,p_base_message=> NULL);
516 END insert_gmd_tech_data_hdr;
517
518 /* ***************************************************************
519 * PROCEDURE insert_gmd_tech_data_dtl
520 *
521 * Synopsis : insert_gmd_tech_data_dtl(1,'DENSITY');
522 *
523 * Description : This function cannot be called independently. It gets
524 * called within Procedure insert_gmd_tech_data_comps.
525 * After it inserts rows in gmd_technical_data_dtl table.
526 * History :
527 * Shyam Sitaraman 02/28/03 Initial Implementation
528 * *************************************************************** */
529 PROCEDURE insert_gmd_tech_data_dtl(vTech_data_id NUMBER) IS
530
531 CURSOR C_get_tech_parm_id(vtech_parm_name VARCHAR2
532 ,vorgn_code VARCHAR2) IS
533 SELECT tech_parm_id
534 FROM gmd_tech_parameters_b
535 WHERE tech_parm_name = vtech_parm_name
536 AND orgn_code = vorgn_code;
537
538 l_tech_parm_id NUMBER;
539 E_Tech_parm_Not_Found EXCEPTION;
540
541 BEGIN
542 IF (vTech_data_id IS NOT NULL) THEN
543
544 /* Get the tech parm id */
545 OPEN C_get_tech_parm_id(v_lm_item_dat_rec.tech_parm_name
546 ,v_lm_item_dat_rec.orgn_code);
547 FETCH C_get_tech_parm_id INTO l_tech_parm_id;
548 IF C_get_tech_parm_id%NOTFOUND THEN
549 RAISE E_Tech_parm_Not_Found;
550 END IF;
551 CLOSE C_get_tech_parm_id;
552
553 INSERT INTO gmd_technical_data_dtl
554 ( tech_data_id
555 , tech_parm_id
556 , sort_seq
557 , text_data
558 , num_data
559 , boolean_data
560 , text_code
561 , creation_date
562 , created_by
563 , last_update_date
564 , last_updated_by
565 , last_update_login
566 )
567 SELECT
568 vTech_data_id
569 , l_tech_parm_id
570 , v_lm_item_dat_rec.sort_seq
571 , v_lm_item_dat_rec.text_data
572 , v_lm_item_dat_rec.num_data
573 , v_lm_item_dat_rec.boolean_data
574 , v_lm_item_dat_rec.text_code
575 , v_lm_item_dat_rec.creation_date
576 , v_lm_item_dat_rec.created_by
577 , v_lm_item_dat_rec.last_update_date
578 , v_lm_item_dat_rec.last_updated_by
579 , v_lm_item_dat_rec.last_update_login
580 FROM sys.dual
581 WHERE NOT EXISTS
582 (SELECT 1 FROM gmd_technical_data_dtl
583 WHERE tech_parm_id = l_tech_parm_id
584 AND tech_data_id = vTech_data_id);
585 END IF;
586
587 EXCEPTION
588 WHEN E_Tech_parm_Not_Found THEN
589 P_line_no := P_line_no + 1;
590 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
591 ,p_table_name => 'GMD_TECHNICAL_DATA_HDR'
592 ,p_db_error => sqlerrm
593 ,p_param1 => NULL
594 ,p_param2 => NULL
595 ,p_param3 => NULL
596 ,p_param4 => NULL
597 ,p_param5 => NULL
598 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
599 ,p_message_type => 'D'
600 ,p_line_no => P_line_no
601 ,p_position=> 1
602 ,p_base_message=> NULL);
603
604 WHEN OTHERS THEN
605 P_line_no := P_line_no + 1;
606 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
607 ,p_table_name => 'GMD_TECHNICAL_DATA_DTL'
608 ,p_db_error => sqlerrm
609 ,p_param1 => NULL
610 ,p_param2 => NULL
611 ,p_param3 => NULL
612 ,p_param4 => NULL
613 ,p_param5 => NULL
614 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
615 ,p_message_type => 'D'
616 ,p_line_no => P_line_no
617 ,p_position=> 1
618 ,p_base_message=> NULL);
619 END insert_gmd_tech_data_dtl;
620
621 /* ***************************************************************
622 * PROCEDURE insert_gmd_tech_data_comps
623 *
624 * Synopsis : insert_gmd_tech_data_comps;
625 *
626 * Description : This procedure can be called independently. Although
627 * it is recommended to call the main program (procedure)
628 * GMD_LM_MIGRATION.Run which in turn calls this procedure.
629 * Data from lm_item_data is sorted based on orgn_code and
630 * item_id. For each header row it calls procedure
631 * insert_gmd_tech_data_hdr and for all details for this
632 * header it call procedure insert_gmd_tech_data_dtl.
633 *
634 * History :
635 * Shyam Sitaraman 02/28/03 Initial Implementation
636 * *************************************************************** */
637 PROCEDURE insert_gmd_tech_data_comps IS
638 CURSOR C_get_lm_item_dat IS
639 SELECT *
640 FROM lm_item_dat_bak
641 ORDER BY orgn_code, item_id;
642 l_orgn_code VARCHAR2(4);
643 l_item_id NUMBER;
644 l_formula_id NUMBER;
645 l_lot_id NUMBER;
646 l_tech_data_id NUMBER;
647 l_tech_parm_id NUMBER;
648 BEGIN
649 OPEN C_get_lm_item_dat;
650 FETCH C_get_lm_item_dat INTO v_lm_item_dat_rec;
651 WHILE (C_get_lm_item_dat%FOUND) LOOP
652 IF ((v_lm_item_dat_rec.orgn_code = l_orgn_code) AND
653 (v_lm_item_dat_rec.item_id = l_item_id)) THEN
654 -- Hdr row already inserted, only insert in the detail table
655 insert_gmd_tech_data_dtl(l_tech_data_id);
656 ELSE
657 -- Insert this row in the header table
658 insert_gmd_tech_data_hdr(x_tech_data_id => l_tech_data_id);
659
660 -- Assign the unique key (orgn code value)
661 l_orgn_code := v_lm_item_dat_rec.orgn_code;
662 l_item_id := v_lm_item_dat_rec.item_id;
663
664 -- Then insert this row in the detail table
665 insert_gmd_tech_data_dtl(l_tech_data_id);
666 END IF;
667 FETCH C_get_lm_item_dat INTO v_lm_item_dat_rec;
668 END LOOP;
669 CLOSE C_get_lm_item_dat;
670 EXCEPTION
671 WHEN OTHERS THEN
672 P_line_no := P_line_no + 1;
673 GMA_MIGRATION.gma_insert_message (p_run_id => p_run_id
674 ,p_table_name => 'GMD_TECHNICAL_DATA_HDR'
675 ,p_db_error => sqlerrm
676 ,p_param1 => NULL
677 ,p_param2 => NULL
678 ,p_param3 => NULL
679 ,p_param4 => NULL
680 ,p_param5 => NULL
681 ,p_message_token => 'GMA_MIGRATION_DB_ERROR'
682 ,p_message_type => 'D'
683 ,p_line_no => P_line_no
684 ,p_position=> 1
685 ,p_base_message=> NULL);
686 END insert_gmd_tech_data_comps;
687
688
689 /* ***************************************************************
690 * PROCEDURE Run
691 *
692 * Synopsis : GMD_LM_MIGRATION.run;
693 *
694 * Description : Main Program - it calls procedure insert_gmd_tech_seq_comps
695 * and insert_gmd_tech_data_comps. It also creates the rows in
696 * GMA TABLE called gma_migration_log that would list out all
697 * existing entity instances that might have problems migrating
698 * over to the New process parameter tables.
699 *
700 * History :
701 * Shyam Sitaraman 02/28/03 Initial Implementation
702 * *************************************************************** */
703 PROCEDURE run IS
704 BEGIN
705 P_run_id := GMA_MIGRATION.gma_migration_start
706 (p_app_short_name => 'GMD'
707 ,p_mig_name => 'GMD_LM_MIGRATION');
708 GMD_LM_MIGRATION.generate_tech_parm_id;
709 GMD_LM_MIGRATION.populate_lm_sprd_dtl_sec_qty;
710 GMD_LM_MIGRATION.populate_parsed_expressions;
711 GMD_LM_MIGRATION.insert_gmd_tech_seq_comps;
712 GMD_LM_MIGRATION.insert_gmd_tech_data_comps;
713 GMA_MIGRATION.gma_migration_end (l_run_id => p_run_id);
714 END run;
715
716
717 END GMD_LM_MIGRATION;