[Home] [Help]
PACKAGE BODY: APPS.GMD_QC_MIGB12
Source
1 PACKAGE BODY GMD_QC_MIGB12 AS
2 /* $Header: gmdmb12b.pls 120.1 2006/09/22 11:48:30 ragsriva noship $
3 +==========================================================================+
4 | Copyright (c) 1998 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +==========================================================================+
8 | FILE NAME |
9 | gmdmb12b.pls |
10 | |
11 | PACKAGE NAME |
12 | GMD_QC_MIGB12 |
13 | |
14 | DESCRIPTION |
15 | This package contains migration procedures/functions |
16 | for Quality for 12 migration for batch_id. |
17 | |
18 | |
19 | HISTORY |
20 +==========================================================================+
21 */
22
23
24 /*===========================================================================
25 -- PROCEDURE
26 -- get_material_detail_id
27 --
28 -- DESCRIPTION:
29 -- This procedure takes the new batch_id and existing step_no and determines
30 -- the new material_detail_id for the batch_id/step_no combination.
31 --
32 -- PARAMETERS:
33 --
34 -- p_migration_run_id IN NUMBER - Migration Id.
35 --
36 -- p_key_id IN NUMBER - Key of record that is current.
37 -- - Used for Error reporting.
38 --
39 -- p_rec_context IN NUMBER - Record Context of migration.
40 -- - 1 = sample events
41 -- - 2 = gmd_wip_spec.
42 --
43 -- p_new_batch_id IN NUMBER - New Batch Id.
44 --
45 -- x_new_matl_det_id OUT NUMBER - New Material Detail Id
46 --
47 -- x_exception_count OUT NUMBER - Exception Count
48 --
49 --=========================================================================== */
50
51 PROCEDURE GET_MATL_DETAIL_ID
52 ( p_migration_run_id IN NUMBER
53 , p_key_id IN NUMBER
54 , p_rec_context IN NUMBER
55 , p_new_batch_id IN NUMBER
56 , p_old_mat_det_id IN NUMBER
57 , x_new_matl_det_id OUT NOCOPY NUMBER
58 , x_exception_count OUT NOCOPY NUMBER)
59
60 IS
61
62
63 /*==========================================
64 Cursor to get existing line_no and type.
65 ==========================================*/
66
67 CURSOR get_line_info IS
68 SELECT line_no, line_type
69 FROM gme_material_details
70 WHERE material_detail_id = p_old_mat_det_id;
71
72 l_line_no gme_material_details.line_no%TYPE;
73 l_line_type gme_material_details.line_type%TYPE;
74
75 /*==========================================
76 Cursor to new material detail id.
77 ==========================================*/
78
79 CURSOR get_matl_det IS
80 SELECT material_detail_id
81 FROM gme_material_details
82 WHERE batch_id = p_new_batch_id
83 AND line_no = l_line_no
84 AND line_type = l_line_type;
85
86 GET_LINE_INFO_ERROR EXCEPTION;
87 GET_MATL_DETL_ERROR EXCEPTION;
88
89 l_record VARCHAR2(35);
90 l_recordkey VARCHAR2(35);
91
92 BEGIN
93
94 x_exception_count := 0;
95
96 /*==============================
97 Get line_no and line_type.
98 ==============================*/
99
100 OPEN get_line_info;
101 FETCH get_line_info INTO l_line_no, l_line_type;
102 IF (get_line_info%NOTFOUND) THEN
103 x_new_matl_det_id := NULL;
104 CLOSE get_line_info;
105 RAISE GET_LINE_INFO_ERROR;
106 END IF;
107 CLOSE get_line_info;
108
109 /*==============================
110 Get new material detail id.
111 ==============================*/
112
113 OPEN get_matl_det;
114 FETCH get_matl_det INTO x_new_matl_det_id;
115 IF (get_matl_det%NOTFOUND) THEN
116 CLOSE get_matl_det;
117 x_new_matl_det_id := NULL;
118 RAISE GET_MATL_DETL_ERROR;
119 END IF;
120 CLOSE get_matl_det;
121
122 EXCEPTION
123
124 WHEN GET_LINE_INFO_ERROR THEN
125
126 /*===============================================
127 Format the error message based on the context
128 that this routine was called from.
129 ===============================================*/
130
131 IF (p_rec_context = 1) THEN -- gmd_sampling_events
132 l_record := 'gmd_sampling_events';
133 l_recordkey := 'sampling_event_id';
134 ELSE -- gmd_wip_spec_vrs
135 l_record := 'gmd_wip_spec_vrs';
136 l_recordkey := 'spec_vr_id';
137 END IF;
138
139 -- Bug# 5559748 Changed FND_LOG.LEVEL_UNEXPECTED to LEVEL_ERROR
140 GMA_COMMON_LOGGING.gma_migration_central_log (
141 p_run_id => p_migration_run_id,
142 p_log_level => FND_LOG.LEVEL_ERROR,
143 p_message_token => 'GMD_MIG_BATCH_LINE',
144 p_context => 'Quality Batch Migration - GMDI',
145 p_token1 => 'REC',
146 p_token2 => 'KEY',
147 p_token3 => 'KEYVAL',
148 p_param1 => l_record,
149 p_param2 => l_recordkey,
150 p_param3 => to_char(p_key_id),
151 p_app_short_name => 'GMD');
152
153 x_exception_count := x_exception_count + 1;
154
155 WHEN GET_MATL_DETL_ERROR THEN
156
157 /*===============================================
158 Format the error message based on the context
159 that this routine was called from.
160 ===============================================*/
161
162 IF (p_rec_context = 1) THEN -- gmd_sampling_events
163 l_record := 'gmd_sampling_events';
164 l_recordkey := 'sampling_event_id';
165 ELSE -- gmd_wip_spec_vrs
166 l_record := 'gmd_wip_spec_vrs';
167 l_recordkey := 'spec_vr_id';
168 END IF;
169
170 -- Bug# 5559748 Changed FND_LOG.LEVEL_UNEXPECTED to LEVEL_ERROR
171 GMA_COMMON_LOGGING.gma_migration_central_log (
172 p_run_id => p_migration_run_id,
173 p_log_level => FND_LOG.LEVEL_ERROR,
174 p_message_token => 'GMD_MIG_BATCH_DETL',
175 p_context => 'Quality Batch Migration - GMDI',
176 p_token1 => 'REC',
177 p_token2 => 'KEY',
178 p_token3 => 'KEYVAL',
179 p_param1 => l_record,
180 p_param2 => l_recordkey,
181 p_param3 => to_char(p_key_id),
182 p_app_short_name => 'GMD');
183
184 x_exception_count := x_exception_count + 1;
185
186 WHEN OTHERS THEN
187 GMA_COMMON_LOGGING.gma_migration_central_log (
188 p_run_id => p_migration_run_id,
189 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
190 p_message_token => 'GMA_MIGRATION_DB_ERROR',
191 p_context => 'Quality Batch Migration - GMDI',
192 p_db_error => SQLERRM,
193 p_app_short_name => 'GMA');
194
195 x_exception_count := x_exception_count + 1;
196
197 END GET_MATL_DETAIL_ID;
198
199
200 /*===========================================================================
201 -- PROCEDURE
202 -- gmd_qc_migrate_batch_id.
203 --
204 -- DESCRIPTION:
205 -- This procedure migrates batch_ids in Quality for 12.0.
206 -- It will be run as part of GME migration. For wip batches that
207 -- have be closed and re-created this routine will update GMD quality
208 -- with references to the cloned batch batch_id.
209 --
210 -- PARAMETERS:
211 --
212 -- p_migration_run_id IN NUMBER - Migration Id.
213 --
214 -- p_commit IN VARCHAR2 - Commit Flag
215 --
216 -- x_exception_count OUT NUMBER - Exception Count
217 --
218 --=========================================================================== */
219
220 PROCEDURE GMD_QC_MIGRATE_BATCH_ID
221 ( p_migration_run_id IN NUMBER
222 , p_commit IN VARCHAR2
223 , x_exception_count OUT NOCOPY NUMBER)
224
225 IS
226
227 /*=====================================
228 Counters for updates.
229 =====================================*/
230
231 l_samp_event_upd NUMBER;
232 l_samples_upd NUMBER;
233 l_wip_spec_upd NUMBER;
234 l_ss_matl_upd NUMBER;
235
236
237 /*=====================================
238 Cursor to get migrated batches.
239 =====================================*/
240
241 CURSOR get_mig_batch IS
242 SELECT old_batch_id, new_batch_id
243 FROM gme_batch_mapping_mig;
244
245 l_mig_batch get_mig_batch%ROWTYPE;
246
247
248 /*=====================================
249 Cursor to get gmd_sampling_events.
250 =====================================*/
251
252 CURSOR get_sampling_event IS
253 SELECT step_no, step_id, material_detail_id, sampling_event_id
254 FROM gmd_sampling_events
255 WHERE batch_id = l_mig_batch.old_batch_id;
256
257 l_sam_event get_sampling_event%ROWTYPE;
258
259 /*=====================================
260 Cursor to get new batchstep id.
261 =====================================*/
262
263 CURSOR get_new_step_id (p_batch_id NUMBER, p_step_no VARCHAR2) IS
264 SELECT batchstep_id
265 FROM gme_batch_steps
266 WHERE batch_id = p_batch_id
267 AND batchstep_no = p_step_no;
268
269 l_new_step_id gme_batch_steps.batchstep_id%TYPE;
270
271 /*=====================================
272 Cursor to get gmd_wip_spec_vrs.
273 =====================================*/
274
275 CURSOR get_wip_spec IS
276 SELECT step_no, step_id, material_detail_id, spec_vr_id
277 FROM gmd_wip_spec_vrs
278 WHERE batch_id = l_mig_batch.old_batch_id;
279
280 l_wip_spec get_wip_spec%ROWTYPE;
281
282 /*=======================================
283 Cursor to get gmd_ss_material_detail.
284 =======================================*/
285
286 CURSOR get_ss_matl_src IS
287 SELECT source_id
288 FROM gmd_ss_material_sources
289 WHERE batch_id = l_mig_batch.old_batch_id;
290
291 l_ss_matl get_ss_matl_src%ROWTYPE;
292
293
294 l_mat_exception_count NUMBER;
295 l_new_det_id NUMBER;
296
297 NO_MATL_ID EXCEPTION;
298 NO_STEP_ID EXCEPTION;
299 NO_WIP_MATL_ID EXCEPTION;
300 NO_WIP_STEP_ID EXCEPTION;
301
302
303 BEGIN
304
305 x_exception_count := 0;
306 l_samp_event_upd := 0;
307 l_samples_upd := 0;
308 l_wip_spec_upd := 0;
309 l_ss_matl_upd := 0;
310
311 FOR l_mig_batch IN get_mig_batch LOOP
312
313 BEGIN -- begin for get_mig_batch
314
315 /*===========================================
316 Check for batch in gmd_sampling_events.
317 ===========================================*/
318 FOR l_sam_event IN get_sampling_event LOOP
319 BEGIN -- begin for get_sampling_event
320 l_new_step_id := NULL;
321 l_new_det_id := NULL;
322 IF (l_sam_event.step_id IS NOT NULL) THEN
323 OPEN get_new_step_id (l_mig_batch.new_batch_id, l_sam_event.step_no);
324 FETCH get_new_step_id INTO l_new_step_id;
325 IF (get_new_step_id%NOTFOUND) THEN
326 CLOSE get_new_step_id;
327 RAISE NO_STEP_ID;
328 END IF;
329 CLOSE get_new_step_id;
330 END IF;
331
332 IF (l_sam_event.material_detail_id IS NOT NULL) THEN
333 GET_MATL_DETAIL_ID (p_migration_run_id,
334 l_sam_event.sampling_event_id,
335 1,
336 l_mig_batch.new_batch_id,
337 l_sam_event.material_detail_id,
338 l_new_det_id,
339 l_mat_exception_count);
340
341 IF (l_mat_exception_count > 0) THEN
342 RAISE NO_MATL_ID;
343 /*======================================
344 Raise exception to go to next
345 sampling event record.
346 Error logged in called routine.
347 *======================================*/
348 END IF;
349 END IF;
350
351 /*==================================
352 Update gmd_sampling_events and
353 gmd_samples.
354 ==================================*/
355
356 UPDATE gmd_sampling_events
357 SET batch_id = l_mig_batch.new_batch_id,
358 step_id = l_new_step_id,
359 material_detail_id = l_new_det_id
360 WHERE sampling_event_id = l_sam_event.sampling_event_id;
361
362 l_samp_event_upd := l_samp_event_upd + 1;
363
364
365 UPDATE gmd_samples
366 SET batch_id = l_mig_batch.new_batch_id,
367 step_id = l_new_step_id,
368 material_detail_id = l_new_det_id
369 WHERE sampling_event_id = l_sam_event.sampling_event_id;
370
371 l_samples_upd := l_samples_upd + SQL%ROWCOUNT;
372
373 IF (p_commit = FND_API.G_TRUE) THEN
374 COMMIT;
375 END IF;
376
377 EXCEPTION -- for get_sampling_event
378 WHEN NO_MATL_ID THEN
379 -- get next sampling event record.
380 NULL;
381
382 WHEN NO_STEP_ID THEN
383 -- Bug# 5559748 Changed FND_LOG.LEVEL_UNEXPECTED to LEVEL_ERROR
384 GMA_COMMON_LOGGING.gma_migration_central_log (
385 p_run_id => p_migration_run_id,
386 p_log_level => FND_LOG.LEVEL_ERROR,
387 p_message_token => 'GMD_MIG_BATCH_STEP_ID',
388 p_context => 'Quality Batch Migration - GSE',
389 p_token1 => 'REC',
390 p_token2 => 'KEY',
391 p_token3 => 'KEYVAL',
392 p_param1 => 'gmd_sampling_events',
393 p_param2 => 'sampling_event_id',
394 p_param3 => to_char(l_sam_event.sampling_event_id),
395 p_app_short_name => 'GMD');
396
397 x_exception_count := x_exception_count + 1;
398
399 WHEN OTHERS THEN
400 GMA_COMMON_LOGGING.gma_migration_central_log (
401 p_run_id => p_migration_run_id,
402 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
403 p_message_token => 'GMA_MIGRATION_DB_ERROR',
404 p_context => 'Quality Batch Migration - GSE',
405 p_db_error => SQLERRM,
406 p_app_short_name => 'GMA');
407
408 x_exception_count := x_exception_count + 1;
409
410 END; -- end for get_sampling_event
411
412 END LOOP; -- end loop for get_sampling_event.
413
414 /*=========================================
415 Check for batch in gmd_wip_spec_vrs.
416 =========================================*/
417 FOR l_wip_spec IN get_wip_spec LOOP
418 BEGIN -- begin for get_wip_spec
419 l_new_step_id := NULL;
420 l_new_det_id := NULL;
421 IF (l_wip_spec.step_id IS NOT NULL) THEN
422 OPEN get_new_step_id (l_mig_batch.new_batch_id, l_wip_spec.step_no);
423 FETCH get_new_step_id INTO l_new_step_id;
424 IF (get_new_step_id%NOTFOUND) THEN
425 CLOSE get_new_step_id;
426 RAISE NO_WIP_STEP_ID;
427 END IF;
428 CLOSE get_new_step_id;
429
430 END IF;
431
432 IF (l_wip_spec.material_detail_id IS NOT NULL) THEN
433 GET_MATL_DETAIL_ID (p_migration_run_id,
434 l_wip_spec.spec_vr_id,
435 2,
436 l_mig_batch.new_batch_id,
437 l_wip_spec.material_detail_id,
438 l_new_det_id,
439 l_mat_exception_count);
440 IF (l_mat_exception_count > 0) THEN
441 RAISE NO_WIP_MATL_ID;
442 /*==================================
443 Raise exception to to to next
444 gmd_wip_spec_vrs record.
445 Error logged in called routine.
446 ==================================*/
447 END IF;
448 END IF;
449
450 UPDATE gmd_wip_spec_vrs
451 SET batch_id = l_mig_batch.new_batch_id,
452 step_id = l_new_step_id,
453 material_detail_id = l_new_det_id
454 WHERE spec_vr_id = l_wip_spec.spec_vr_id;
455
456 l_wip_spec_upd := l_wip_spec_upd + 1;
457
458 IF (p_commit = FND_API.G_TRUE) THEN
459 COMMIT;
460 END IF;
461
462 EXCEPTION -- for get-wip-spec
463
464 WHEN NO_WIP_MATL_ID THEN
465 NULL; -- get next record.
466
467 WHEN NO_WIP_STEP_ID THEN
468 -- Bug# 5559748 Changed FND_LOG.LEVEL_UNEXPECTED to LEVEL_ERROR
469 GMA_COMMON_LOGGING.gma_migration_central_log (
470 p_run_id => p_migration_run_id,
471 p_log_level => FND_LOG.LEVEL_ERROR,
472 p_message_token => 'GMD_MIG_BATCH_STEP_ID',
473 p_context => 'Quality Batch Migration - GWS',
474 p_token1 => 'REC',
475 p_token2 => 'KEY',
476 p_token3 => 'KEYVAL',
477 p_param1 => 'gmd_wip_spec_vrs',
478 p_param2 => 'spec_vr_id',
479 p_param3 => to_char(l_wip_spec.spec_vr_id),
480 p_app_short_name => 'GMD');
481
482 x_exception_count := x_exception_count + 1;
483
484
485 WHEN OTHERS THEN
486 GMA_COMMON_LOGGING.gma_migration_central_log (
487 p_run_id => p_migration_run_id,
488 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
489 p_message_token => 'GMA_MIGRATION_DB_ERROR',
490 p_context => 'Quality Batch Migration - GWS',
491 p_db_error => SQLERRM,
492 p_app_short_name => 'GMA');
493
494 x_exception_count := x_exception_count + 1;
495 END; -- end for get_sampling_event
496
497 END LOOP; -- end loop for get_wip_spec
498
499 /*=============================================
500 Check gmd_ss_material_source for batch_id.
501 =============================================*/
502
503 FOR l_ss_matl IN get_ss_matl_src LOOP
504 BEGIN -- begin for get_ss_matl_src
505 UPDATE gmd_ss_material_sources
506 SET batch_id = l_mig_batch.new_batch_id
507 WHERE source_id = l_ss_matl.source_id;
508
509 l_ss_matl_upd := l_ss_matl_upd + SQL%ROWCOUNT;
510
511 IF (p_commit = FND_API.G_TRUE) THEN
512 COMMIT;
513 END IF;
514
515 EXCEPTION -- for get_ss_matl_src
516
517 WHEN OTHERS THEN
518 GMA_COMMON_LOGGING.gma_migration_central_log (
519 p_run_id => p_migration_run_id,
520 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
521 p_message_token => 'GMA_MIGRATION_DB_ERROR',
522 p_context => 'Quality Batch Migration - GSMS',
523 p_db_error => SQLERRM,
524 p_app_short_name => 'GMA');
525
526 x_exception_count := x_exception_count + 1;
527
528 END; -- begin for get_ss_matl_src
529
530 END LOOP; -- end loop for get_ss_matl_src
531
532
533 EXCEPTION -- for get_mig_batch.
534 WHEN OTHERS THEN
535 GMA_COMMON_LOGGING.gma_migration_central_log (
536 p_run_id => p_migration_run_id,
537 p_log_level => FND_LOG.LEVEL_UNEXPECTED,
538 p_message_token => 'GMA_MIGRATION_DB_ERROR',
539 p_context => 'Quality Batch Migration - GMB',
540 p_db_error => SQLERRM,
541 p_app_short_name => 'GMA');
542
543 x_exception_count := x_exception_count + 1;
544
545 END; -- end for get_mig_batch.
546
547 END LOOP;
548
549
550 /*============================
551 Log update counts.
552 ============================*/
553 -- Bug# 5559748 Changed FND_LOG.LEVEL_UNEXPECTED to LEVEL_EVENT
554 GMA_COMMON_LOGGING.gma_migration_central_log (
555 p_run_id => p_migration_run_id,
556 p_log_level => FND_LOG.LEVEL_EVENT,
557 p_message_token => 'GMD_MIG_BATCH_SUMMARY',
558 p_context => 'Quality Batch Migration',
559 p_token1 => 'SAMEVTUPD',
560 p_token2 => 'SAMPUPD',
561 p_token3 => 'WIPUPD',
562 p_token4 => 'MATDET',
563 p_param1 => to_char(l_samp_event_upd),
564 p_param2 => to_char(l_samples_upd),
565 p_param3 => to_char(l_wip_spec_upd),
566 p_param4 => to_char(l_ss_matl_upd),
567 p_app_short_name => 'GMD');
568
569
570 END GMD_QC_MIGRATE_BATCH_ID;
571
572 END;