[Home] [Help]
PACKAGE BODY: APPS.MIGRATE_BATCH
Source
1 PACKAGE BODY migrate_batch AS
2 /* $Header: GMEMIGBB.pls 120.1 2005/06/09 08:25:43 appldev $ */
3
4 /***********************************************************/
5 -- Oracle Process Manufacturing Process Execution APIs
6 --
7 -- File Name: GMEMIGBB.pls
8 -- Contents: Package body for GME data migration
9 -- Description:
10 -- This package migrates GME data from 11.5.1H and prior to
11 -- 11.5.1I.
12 --
13 -- The general architecture is a table by table
14 -- copy via INSERT INTO...SELECT FROM. If calculations are
15 -- required, functions are used for that purpose inline.
16
17 -- There are 3 batch by batch processing sections:
18 -- 1. check_wip_batches,
19 -- 2. split_trans_line, and
20 -- 3. insert_batch_step_dtls.
21 -- check_wip_batches finds completed default transactions and reports them. It also
22 -- reverses the transaction and creates a new pending transaction.
23 -- split_trans_line deals with completed default transactions that are
24 -- either plain or non-plain with 0 qty. Here, for 0 qty completed, the
25 -- transaction can be flipped to pending. For non-zero completed plain, a
26 -- 0 qty pending transaction is inserted. insert_batch_step_dtls performs
27 -- batch by batch processing in order to normalize pm_oprn_dtl
28 -- into gme_batch_step_activities and gme_batch_step_resources.
29 --
30 -- The main package to call is migrate_batch.run. If it is called with no parameters,
31 -- or as migrate_batch.run(p_commit => FALSE) a rollback is performed at the end of the
32 -- migration. This is for purposes of validation, so that the user can find all data
33 -- conditions that exist without changing data. Call migrate_batch.run(p_commit => TRUE)
34 -- and this will migrate data with a commit at each phase of migration.
35 --
36 -- Messages are logged to gme_temp_exceptions.
37 -- Message types are: 'P' for progress, 'I' for information, 'E' for error, and 'D' for
38 -- unexpected error. 'D' will come from WHEN OTHERS exception block and there can only be
39 -- one 'D' because WHEN OTHERS do a RAISE and will not continue processing further. This
40 -- will typically occur because of unexpected SQL errors.
41 --
42 --
43 -- Author: Antonia Newbury
44 -- Date: March 2002
45 --
46 -- History
47 -- =======
48 -- Shrikant Nene - 06/19/2002 B2417758 .
49 -- Moved 1 line of code, so that it will print the correct
50 -- message.
51 -- Also changed l_tran_reverse.trans_id to l_tran_row.trans_id
52 -- Shrikant Nene - 02/10/2003 B2792583
53 -- Corrected reference of pm_matl_dt to pm_matl_dtl_bak
54 -- in procedure renumber_duplicate_line_no.
55 -- Since it was looking at the original table, this procedure
56 -- did not find anything.
57 -- Antonia Newbury - 2/26/2003 Added fixes which came out of 4.1
58 -- validation testing:
59 -- 1. Set IN_USE = 0 for in_use of NULL and in_use NOT in (0,100)
60 -- 2. Set delete mark for step details and resource trxns on steps
61 -- that are deleted.
62 -- 3. Perform a tablespace check to inform the user if the tablespace
63 -- for GME is less than a defined %. (Currently 60).
64 -- 4. Report item/step associations and step dependencies that are
65 -- orphaned from the parent table because these will not be migrated.
66 -- Antonia Newbury - 2/28/2003 Added fixes which are required to make
67 -- migration patchset candidate checkin to run with the 11.5.9 install.
68 -- 1. Continue with migration even if validation didn't run.
69 -- 2. Return from migration if there are no batches in pm_btch_hdr. This
70 -- is a speed optimization for new GME/OPM customers. No need to run through
71 -- all the checks in this program if there are no batches.
72 -- 3. Remove all GANTT table logic. This will be done via bug # 2565952.
73 -- That is a patchset candidate checkin for MP J and the sql file to run
74 -- the code which populates the tables is being changed to run after this runs.
75 -- (dbdrv hints are being changed). This way it rus after migration runs (so
76 -- that there is data to be populated in the GANTT table.
77 -- 4. Added nocopy.
78 -- Antonia Newbury - 01/14/2004 Changed call to gmi_locks.lock_inventory to have
79 -- named parameters.
80 --
81 -- G. Muratore - 06/09/2005 Bug 4249832 Removed hard coded schemas per gscc.
82 /***********************************************************/
83
84 g_resource pm_oprn_dtl.resources%TYPE;
85 g_batch_id pm_btch_hdr_bak.batch_id%TYPE;
86 g_min_capacity NUMBER;
87 g_max_capacity NUMBER;
88 g_capacity_uom VARCHAR2 (4);
89 g_mig_date DATE := NULL;
90 g_date_format VARCHAR2(100) := 'YYYY-MM-DD HH24:MI:SS';
91 g_tablespace_target_free NUMBER := 60;
92 g_tablespace_User VARCHAR2(10) := 'GME';
93
94 PROCEDURE insert_message_into_table (
95 p_table_name IN VARCHAR2,
96 p_procedure_name IN VARCHAR2,
97 p_parameters IN VARCHAR2,
98 p_message IN VARCHAR2,
99 p_error_type IN VARCHAR2
100 ) IS
101 PRAGMA autonomous_transaction;
102 BEGIN
103
104 IF p_error_type = 'V' THEN
105 /* This is validation control; if error_typpe is V, then we are ensuring that validation */
106 /* was run at least once. */
107 /* This was combined with log table because it was found that having 2 separate autonomous */
108 /* transactions didn't behave properly. So, for now these 2 events will be combined. */
109
110 IF p_procedure_name = 'set_GME_validated' THEN
111 set_GME_validated;
112 ELSIF p_procedure_name = 'reset_GME_validated' THEN
113 reset_GME_validated;
114 END IF;
115 ELSE
116 INSERT INTO gme_temp_exceptions
117 (table_name,
118 procedure_name,
119 parameters,
120 message,
121 error_type,
122 script_date
123 )
124 VALUES (p_table_name,
125 p_procedure_name,
126 p_parameters,
127 TO_CHAR (SYSDATE, g_date_format) || ': ' || p_message,
128 p_error_type,
129 g_mig_date
130 );
131 END IF;
132
133 COMMIT;
134 EXCEPTION
135 WHEN OTHERS THEN
136 dbms_output.put_line('SQLERRM = '||SQLERRM);
137
138 RAISE;
139 END insert_message_into_table;
140
141 PROCEDURE insert_message (
142 p_table_name IN VARCHAR2,
143 p_procedure_name IN VARCHAR2,
144 p_parameters IN VARCHAR2,
145 p_message IN VARCHAR2,
146 p_error_type IN VARCHAR2
147 ) IS
148 BEGIN
149 insert_message_into_table (
150 p_table_name => p_table_name,
151 p_procedure_name => p_procedure_name,
152 p_parameters => p_parameters,
153 p_message => p_message,
154 p_error_type => p_error_type
155 );
156 EXCEPTION
157 WHEN OTHERS THEN
158 dbms_output.put_line('SQLERRM = '||SQLERRM);
159
160 RAISE;
161 END insert_message;
162
163 PROCEDURE initialize_migration IS
164 l_pos NUMBER := 0;
165 BEGIN
166 l_pos := 1;
167
168 IF g_mig_date IS NULL THEN
169 g_mig_date := SYSDATE;
170 END IF;
171 EXCEPTION
172 WHEN OTHERS THEN
173 insert_message (
174 p_table_name => 'DUAL',
175 p_procedure_name => 'initialize_migration',
176 p_parameters => 'none',
177 p_message => SQLERRM || ' with pos = ' || l_pos,
178 p_error_type => 'D'
179 );
180 RAISE;
181 END initialize_migration;
182
183 PROCEDURE unlock_all IS
184 l_pos NUMBER := 0;
185 BEGIN
186 l_pos := 1;
187
188 UPDATE pm_btch_hdr_bak
189 SET in_use = 0
190 WHERE in_use IS NULL OR
191 in_use NOT in (0,100);
192
193 l_pos := 2;
194
195 insert_message (
196 p_table_name => 'pm_btch_hdr',
197 p_procedure_name => 'unlock_all',
198 p_parameters => 'none',
199 p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
200 p_error_type => 'P'
201 );
202
203 l_pos := 3;
204
205 UPDATE pm_matl_dtl_bak
206 SET in_use = 0
207 WHERE in_use IS NULL OR
208 in_use NOT in (0,100);
209
210 l_pos := 4;
211
212 insert_message (
213 p_table_name => 'pm_matl_dtl',
214 p_procedure_name => 'unlock_all',
215 p_parameters => 'none',
216 p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
217 p_error_type => 'P'
218 );
219
220 l_pos := 5;
221
222 UPDATE pm_rout_dtl
223 SET in_use = 0
224 WHERE in_use IS NULL OR
225 in_use NOT in (0,100);
226
227 l_pos := 6;
228
229 insert_message (
230 p_table_name => 'pm_rout_dtl',
231 p_procedure_name => 'unlock_all',
232 p_parameters => 'none',
233 p_message => 'number of records unlocked = ' || SQL%ROWCOUNT,
234 p_error_type => 'P'
235 );
236
237 l_pos := 7;
238
239 EXCEPTION
240 WHEN OTHERS THEN
241 insert_message (
242 p_table_name => 'pm_btch_hdr/pm_matl_dtl/pm_rout_dtl',
243 p_procedure_name => 'unlock_all',
244 p_parameters => 'none',
245 p_message => SQLERRM || ' with pos = ' || l_pos,
246 p_error_type => 'D'
247 );
248 RAISE;
249 END unlock_all;
250
251 PROCEDURE del_step_dtl_for_del_steps IS
252 l_pos NUMBER := 0;
253
254 CURSOR cur_get_del_steps IS
255 SELECT batch_id, batchstep_no
256 FROM pm_rout_dtl
257 WHERE delete_mark = 1;
258
259 CURSOR cur_get_del_step_dtls IS
260 SELECT batchstepline_id
261 FROM pm_oprn_dtl
262 WHERE delete_mark = 1;
263
264 l_cur_get_del_steps cur_get_del_steps%ROWTYPE;
265 l_cur_get_del_step_dtls cur_get_del_step_dtls%ROWTYPE;
266
267 BEGIN
268 l_pos := 1;
269
270 OPEN cur_get_del_steps;
271 FETCH cur_get_del_steps INTO l_cur_get_del_steps;
272 WHILE cur_get_del_steps%FOUND LOOP
273 UPDATE pm_oprn_dtl
274 SET delete_mark = 1
275 WHERE batch_id = l_cur_get_del_steps.batch_id AND
276 batchstep_no = l_cur_get_del_steps.batchstep_no;
277
278 FETCH cur_get_del_steps INTO l_cur_get_del_steps;
279 END LOOP;
280 CLOSE cur_get_del_steps;
281
282 OPEN cur_get_del_step_dtls;
283 FETCH cur_get_del_step_dtls INTO l_cur_get_del_step_dtls;
284 WHILE cur_get_del_step_dtls%FOUND LOOP
285 UPDATE pc_tran_pnd
286 SET delete_mark = 1
287 WHERE line_id = l_cur_get_del_step_dtls.batchstepline_id;
288
289 FETCH cur_get_del_step_dtls INTO l_cur_get_del_step_dtls;
290 END LOOP;
291 CLOSE cur_get_del_step_dtls;
292
293 EXCEPTION
294 WHEN OTHERS THEN
295 insert_message (
296 p_table_name => 'pm_oprn_dtl/pc_tran_pnd',
297 p_procedure_name => 'del_step_dtl_for_del_steps',
298 p_parameters => 'none',
299 p_message => SQLERRM || ' with pos = ' || l_pos,
300 p_error_type => 'D'
301 );
302 RAISE;
303 END del_step_dtl_for_del_steps;
304
305 FUNCTION get_actual_date (p_date IN DATE)
306 RETURN DATE IS
307 l_pos NUMBER := 0;
308 BEGIN
309 l_pos := 1;
310
311 IF TO_CHAR (p_date, 'YYYYMMDDHH24MISS') = '19700101000000' THEN
312 l_pos := 2;
313 RETURN NULL;
314 ELSE
315 l_pos := 3;
316 RETURN p_date;
317 END IF;
318 EXCEPTION
319 WHEN OTHERS THEN
320 insert_message (
321 p_table_name => 'none',
322 p_procedure_name => 'get_actual_date',
323 p_parameters => TO_CHAR (p_date, g_date_format),
324 p_message => SQLERRM || ' with pos = ' || l_pos,
325 p_error_type => 'D'
326 );
327 RAISE;
328 END get_actual_date;
329
330 FUNCTION get_planned_usage (p_batchstepline_id IN NUMBER)
331 RETURN NUMBER IS
332 v_resource_usage NUMBER;
333 l_pos NUMBER := 0;
334 BEGIN
335 l_pos := 1;
336 SELECT plan_rsrc_count * plan_rsrc_usage
337 INTO v_resource_usage
338 FROM pm_oprn_dtl
339 WHERE batchstepline_id = p_batchstepline_id;
340 l_pos := 2;
341 RETURN v_resource_usage;
342 EXCEPTION
343 WHEN OTHERS THEN
344 insert_message (
345 p_table_name => 'pm_oprn_dtl',
346 p_procedure_name => 'get_planned_usage',
347 p_parameters => p_batchstepline_id,
348 p_message => SQLERRM || ' with pos = ' || l_pos,
349 p_error_type => 'D'
350 );
351 RAISE;
352 END get_planned_usage;
353
354 FUNCTION get_actual_activity_factor (
355 p_batchstep_id IN gme_batch_steps.batchstep_id%TYPE
356 )
357 RETURN gme_batch_step_activities.actual_activity_factor%TYPE IS
358 l_step_status gme_batch_steps.step_status%TYPE;
359 l_pos NUMBER := 0;
360 BEGIN
361 l_pos := 1;
362 SELECT step_status
363 INTO l_step_status
364 FROM gme_batch_steps
365 WHERE batchstep_id = p_batchstep_id;
366 l_pos := 2;
367
368 IF l_step_status IN (1, 5) THEN
369 l_pos := 3;
370 RETURN NULL;
371 ELSE
372 l_pos := 4;
373 RETURN 1;
374 END IF;
375 EXCEPTION
376 WHEN OTHERS THEN
377 insert_message (
378 p_table_name => 'gme_batch_steps',
379 p_procedure_name => 'get_actual_activity_factor',
380 p_parameters => p_batchstep_id,
381 p_message => SQLERRM || ' with pos = ' || l_pos,
382 p_error_type => 'D'
383 );
384 RAISE;
385 END get_actual_activity_factor;
386
387 FUNCTION get_actual_usage (p_line_id IN NUMBER)
388 RETURN NUMBER IS
389 v_resource_usage NUMBER;
390 l_pos NUMBER := 0;
391 BEGIN
392 l_pos := 1;
393 SELECT SUM (resource_usage)
394 INTO v_resource_usage
395 FROM pc_tran_pnd
396 WHERE line_id = p_line_id AND
397 delete_mark <> 1 AND
398 completed_ind = 1;
399 l_pos := 2;
400 RETURN NVL (v_resource_usage, 0);
401 EXCEPTION
402 WHEN OTHERS THEN
403 insert_message (
404 p_table_name => 'pc_tran_pnd',
405 p_procedure_name => 'get_actual_usage',
406 p_parameters => p_line_id,
407 p_message => SQLERRM || ' with pos = ' || l_pos,
408 p_error_type => 'D'
409 );
410 RAISE;
411 END get_actual_usage;
412
413 FUNCTION get_oprn_id (p_batch_id IN NUMBER, p_batchstep_no IN NUMBER)
414 RETURN NUMBER IS
415 v_oprn_id NUMBER;
416 l_pos NUMBER := 0;
417 BEGIN
418 l_pos := 1;
419 SELECT oprn_id
420 INTO v_oprn_id
421 FROM gme_batch_steps
422 WHERE batch_id = p_batch_id AND
423 batchstep_no = p_batchstep_no;
424 l_pos := 2;
425 RETURN v_oprn_id;
426 EXCEPTION
427 WHEN OTHERS THEN
428 insert_message (
429 p_table_name => 'gme_batch_steps',
430 p_procedure_name => 'get_oprn_id',
431 p_parameters => 'batch_id = '
432 || p_batch_id
433 || ' batchstep_no = '
434 || p_batchstep_no,
435 p_message => SQLERRM || ' with pos = ' || l_pos,
436 p_error_type => 'D'
437 );
438 RAISE;
439 END get_oprn_id;
440
441 FUNCTION get_process_qty_uom (p_oprn_id NUMBER)
442 RETURN VARCHAR2 IS
443 v_process_qty_uom VARCHAR2 (4);
444 l_pos NUMBER := 0;
445 BEGIN
446 l_pos := 1;
447 SELECT process_qty_um
448 INTO v_process_qty_uom
449 FROM fm_oprn_mst
450 WHERE oprn_id = p_oprn_id;
451 l_pos := 2;
452 RETURN v_process_qty_uom;
453 EXCEPTION
454 WHEN OTHERS THEN
455 insert_message (
456 p_table_name => 'fm_oprn_mst',
457 p_procedure_name => 'get_process_qty_uom',
458 p_parameters => p_oprn_id,
459 p_message => SQLERRM || ' with pos = ' || l_pos,
460 p_error_type => 'D'
461 );
462 RAISE;
463 END get_process_qty_uom;
464
465 FUNCTION get_actual_cost_ind (p_batch_id IN NUMBER)
466 RETURN VARCHAR2 IS
467 v_actual_cost_ind NUMBER;
468 l_pos NUMBER := 0;
469 BEGIN
470 l_pos := 1;
471 SELECT NVL (MAX (rollover_ind), -1)
472 INTO v_actual_cost_ind
473 FROM cm_cmpt_dtl ccd,
474 cm_acst_led acl,
475 pm_matl_dtl_bak bdtl,
476 pm_btch_hdr_bak bhdr
477 WHERE ccd.cmpntcost_id = acl.cmpntcost_id AND
478 ccd.delete_mark = 0 AND
479 acl.source_ind = 0 AND
480 acl.transline_id = bdtl.line_id AND
481 bdtl.batch_id = bhdr.batch_id AND
482 bhdr.batch_id = p_batch_id;
483 l_pos := 2;
484
485 IF v_actual_cost_ind IN (0, 1) THEN
486 l_pos := 3;
487 RETURN 'Y';
488 ELSE
489 l_pos := 4;
490 RETURN 'N';
491 END IF;
492 EXCEPTION
493 WHEN OTHERS THEN
494 insert_message (
495 p_table_name => 'cm_cmpt_dtl, cm_acst_led, pm_matl_dtl_bak, pm_btch_hdr_bak',
496 p_procedure_name => 'get_actual_cost_ind',
497 p_parameters => p_batch_id,
498 p_message => SQLERRM || ' with pos = ' || l_pos,
499 p_error_type => 'D'
500 );
501 RAISE;
502 END get_actual_cost_ind;
503
504 FUNCTION get_gl_posted_ind (p_batch_id IN NUMBER)
505 RETURN NUMBER IS
506 v_gl_posted_ind NUMBER;
507 l_pos NUMBER := 0;
508 BEGIN
509 l_pos := 1;
510 SELECT NVL (
511 MAX (gl_posted_ind),
512 0
513 ) -- for those customers (IMCO and maybe others) who have manually reopened
514 -- batches via sqlplus and then closed through the application.
515 INTO v_gl_posted_ind
516 FROM pm_hist_hdr
517 WHERE batch_id = p_batch_id AND
518 new_status = 4;
519 l_pos := 2;
520 RETURN v_gl_posted_ind;
521 EXCEPTION
522 WHEN OTHERS THEN
523 insert_message (
524 p_table_name => 'pm_hist_hdr',
525 p_procedure_name => 'get_gl_posted_ind',
526 p_parameters => p_batch_id,
527 p_message => SQLERRM || ' with pos = ' || l_pos,
528 p_error_type => 'D'
529 );
530 RAISE;
531 END get_gl_posted_ind;
532
533 FUNCTION get_poc_data_ind (p_batch_id IN NUMBER)
534 RETURN VARCHAR2 IS
535 v_exist NUMBER := 0;
536 l_pos NUMBER := 0;
537 BEGIN
538 l_pos := 1;
539 SELECT 1
540 INTO v_exist
541 FROM pm_rout_dtl
542 WHERE batch_id = p_batch_id AND
543 delete_mark <> 1 AND
544 ROWNUM = 1;
545 l_pos := 2;
546
547 IF v_exist = 1 THEN
548 l_pos := 3;
549 RETURN 'Y';
550 ELSE
551 l_pos := 4;
552 RETURN 'N';
553 END IF;
554 EXCEPTION
555 WHEN NO_DATA_FOUND THEN
556 RETURN 'N';
557 WHEN OTHERS THEN
558 insert_message (
559 p_table_name => 'pm_rout_dtl',
560 p_procedure_name => 'get_poc_data_ind',
561 p_parameters => p_batch_id,
562 p_message => SQLERRM || ' with pos = ' || l_pos,
563 p_error_type => 'D'
564 );
565 RAISE;
566 END get_poc_data_ind;
567
568 FUNCTION get_ref_uom (p_uom_class IN VARCHAR2)
569 RETURN VARCHAR2 IS
570 v_ref_uom VARCHAR2 (4);
571 l_pos NUMBER := 0;
572 BEGIN
573 l_pos := 1;
574 SELECT std_um
575 INTO v_ref_uom
576 FROM sy_uoms_typ
577 WHERE um_type = p_uom_class;
578 l_pos := 2;
579 RETURN v_ref_uom;
580 EXCEPTION
581 WHEN NO_DATA_FOUND THEN
582 RETURN NULL;
583 WHEN OTHERS THEN
584 insert_message (
585 p_table_name => 'sy_uoms_typ',
586 p_procedure_name => 'get_ref_uom',
587 p_parameters => p_uom_class,
588 p_message => SQLERRM || ' with pos = ' || l_pos,
589 p_error_type => 'D'
590 );
591 RAISE;
592 END get_ref_uom;
593
594 FUNCTION get_batchstep_id (p_batch_id IN NUMBER, p_batchstep_no IN NUMBER)
595 RETURN NUMBER IS
596 v_batchstep_id NUMBER := 0;
597 l_pos NUMBER := 0;
598 BEGIN
599 l_pos := 1;
600 SELECT batchstep_id
601 INTO v_batchstep_id
602 FROM gme_batch_steps
603 WHERE batch_id = p_batch_id AND
604 batchstep_no = p_batchstep_no;
605 l_pos := 2;
606 RETURN v_batchstep_id;
607 EXCEPTION
608 WHEN OTHERS THEN
609 insert_message (
610 p_table_name => 'gme_batch_steps',
611 p_procedure_name => 'get_batchstep_id',
612 p_parameters => 'batch_id = '
613 || p_batch_id
614 || ' batchstep_no = '
615 || p_batchstep_no,
616 p_message => SQLERRM || ' with pos = ' || l_pos,
617 p_error_type => 'D'
618 );
619 RAISE;
620 END get_batchstep_id;
621
622 FUNCTION get_activity_id (
623 p_batch_id IN NUMBER,
624 p_batchstep_no IN NUMBER,
625 p_activity IN VARCHAR2
626 )
627 RETURN NUMBER IS
628 v_activity_id NUMBER := 0;
629 v_step_id NUMBER := 0;
630 l_pos NUMBER := 0;
631 BEGIN
632 l_pos := 1;
633 SELECT batchstep_id
634 INTO v_step_id
635 FROM gme_batch_steps
636 WHERE batch_id = p_batch_id AND
637 batchstep_no = p_batchstep_no;
638 l_pos := 2;
639 SELECT batchstep_activity_id
640 INTO v_activity_id
641 FROM gme_batch_step_activities
642 WHERE batch_id = p_batch_id AND
643 batchstep_id = v_step_id AND
644 activity = p_activity;
645 l_pos := 3;
646 RETURN v_activity_id;
647 EXCEPTION
648 WHEN OTHERS THEN
649 insert_message (
650 p_table_name => 'gme_batch_steps, gme_batch_step_activities',
651 p_procedure_name => 'get_activity_id',
652 p_parameters => 'batch_id = '
653 || p_batch_id
654 || ' batchstep_no = '
655 || p_batchstep_no
656 || ' activity = '
657 || p_activity,
658 p_message => SQLERRM || ' with pos = ' || l_pos,
659 p_error_type => 'D'
660 );
661 RAISE;
662 END get_activity_id;
663
664 FUNCTION get_rsrc_offset (
665 p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
666 p_batchstep_no IN pm_rout_dtl.batchstep_no%TYPE,
667 p_activity IN pm_oprn_dtl.activity%TYPE,
668 p_offset IN pm_oprn_dtl.offset_interval%TYPE
669 )
670 RETURN NUMBER IS
671 l_act_offset NUMBER;
672 l_pos NUMBER := 0;
673 BEGIN
674 l_pos := 1;
675 SELECT offset_interval
676 INTO l_act_offset
677 FROM gme_batch_step_activities
678 WHERE activity = p_activity AND
679 batch_id = p_batch_id AND
680 batchstep_id = (SELECT batchstep_id
681 FROM gme_batch_steps
682 WHERE batch_id = p_batch_id AND
683 batchstep_no = p_batchstep_no);
684 l_pos := 2;
685 RETURN p_offset - l_act_offset;
686 EXCEPTION
687 WHEN OTHERS THEN
688 insert_message (
689 p_table_name => 'gme_batch_step_activities',
690 p_procedure_name => 'get_rsrc_offset',
691 p_parameters => 'batch_id = '
692 || p_batch_id
693 || ' batchstep_no = '
694 || p_batchstep_no
695 || ' activity = '
696 || p_activity
697 || ' activity offset = '
698 || p_offset,
699 p_message => SQLERRM || ' with pos = ' || l_pos,
700 p_error_type => 'D'
701 );
702 RAISE;
703 END get_rsrc_offset;
704
705 PROCEDURE get_capacity (
706 p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
707 p_resources IN pm_oprn_dtl.resources%TYPE,
708 x_min_capacity OUT NOCOPY NUMBER,
709 x_max_capacity OUT NOCOPY NUMBER,
710 x_capacity_uom OUT NOCOPY VARCHAR2,
711 x_return_status OUT NOCOPY VARCHAR2
712 ) IS
713 CURSOR get_resource_capacity (
714 v_rsrc pm_oprn_dtl.resources%TYPE,
715 v_batch_id pm_btch_hdr_bak.batch_id%TYPE
716 ) IS
717 SELECT min_capacity,
718 max_capacity,
719 capacity_uom
720 FROM cr_rsrc_dtl
721 WHERE resources = v_rsrc AND
722 orgn_code = (SELECT plant_code
723 FROM pm_btch_hdr_bak
724 WHERE batch_id = v_batch_id);
725
726 l_return_status VARCHAR2 (1);
727 l_pos NUMBER := 0;
728 BEGIN
729 x_return_status := FND_API.G_RET_STS_SUCCESS;
730 l_pos := 1;
731 OPEN get_resource_capacity (p_resources, p_batch_id);
732 l_pos := 2;
733 FETCH get_resource_capacity INTO x_min_capacity,
734 x_max_capacity,
735 x_capacity_uom;
736 l_pos := 3;
737
738 IF get_resource_capacity%NOTFOUND THEN
739 l_pos := 4;
740
741 x_min_capacity := NULL;
742 x_max_capacity := NULL;
743 x_capacity_uom := NULL;
744
745 l_pos := 5;
746 END IF;
747
748 l_pos := 6;
749
750 CLOSE get_resource_capacity;
751
752 l_pos := 7;
753
754 g_resource := p_resources;
755 l_pos := 8;
756 g_batch_id := p_batch_id;
757 l_pos := 9;
758 g_min_capacity := x_min_capacity;
759 l_pos := 10;
760 g_max_capacity := x_max_capacity;
761 l_pos := 11;
762 g_capacity_uom := x_capacity_uom;
763
764 l_pos := 12;
765 EXCEPTION
766 WHEN OTHERS THEN
767 insert_message (
768 p_table_name => 'cr_rsrc_dtl',
769 p_procedure_name => 'get_capacity',
770 p_parameters => 'batch_id = '
771 || p_batch_id
772 || ' resource = '
773 || p_resources,
774 p_message => SQLERRM || ' with pos = ' || l_pos,
775 p_error_type => 'D'
776 );
777 RAISE;
778 END get_capacity;
779
780 FUNCTION get_min_capacity (
781 p_batch_id pm_btch_hdr_bak.batch_id%TYPE,
782 p_rsrc pm_oprn_dtl.resources%TYPE
783 )
784 RETURN NUMBER IS
785 l_min_c NUMBER;
786 l_max_c NUMBER;
787 l_cap_uom VARCHAR2 (4);
788 l_return_status VARCHAR2 (1);
789 l_pos NUMBER := 0;
790 BEGIN
791 l_pos := 1;
792
793 IF p_batch_id = g_batch_id AND
794 p_rsrc = g_resource THEN
795 l_pos := 2;
796 RETURN g_min_capacity;
797 ELSE
798 l_pos := 3;
799 get_capacity (
800 p_batch_id,
801 p_rsrc,
802 l_min_c,
803 l_max_c,
804 l_cap_uom,
805 l_return_status
806 );
807 l_pos := 4;
808 g_resource := p_rsrc;
809 l_pos := 5;
810 g_batch_id := p_batch_id;
811 l_pos := 6;
812 g_min_capacity := l_min_c;
813 l_pos := 7;
814 g_max_capacity := l_max_c;
815 l_pos := 8;
816 g_capacity_uom := l_cap_uom;
817 l_pos := 9;
818 END IF;
819
820 l_pos := 10;
821 RETURN g_min_capacity;
822 EXCEPTION
823 WHEN OTHERS THEN
824 insert_message (
825 p_table_name => 'none',
826 p_procedure_name => 'get_min_capacity',
827 p_parameters => 'batch_id = '
828 || p_batch_id
829 || ' resource = '
830 || p_rsrc,
831 p_message => SQLERRM || ' with pos = ' || l_pos,
832 p_error_type => 'D'
833 );
834 RAISE;
835 END get_min_capacity;
836
837 FUNCTION get_max_capacity (
838 p_batch_id pm_btch_hdr_bak.batch_id%TYPE,
839 p_rsrc pm_oprn_dtl.resources%TYPE
840 )
841 RETURN NUMBER IS
842 l_min_c NUMBER;
843 l_max_c NUMBER;
844 l_cap_uom VARCHAR2 (4);
845 l_return_status VARCHAR2 (1);
846 l_pos NUMBER := 0;
847 BEGIN
848 l_pos := 1;
849
850 IF p_batch_id = g_batch_id AND
851 p_rsrc = g_resource THEN
852 l_pos := 2;
853 RETURN g_max_capacity;
854 ELSE
855 l_pos := 3;
856 get_capacity (
857 p_batch_id,
858 p_rsrc,
859 l_min_c,
860 l_max_c,
861 l_cap_uom,
862 l_return_status
863 );
864 l_pos := 4;
865 g_resource := p_rsrc;
866 l_pos := 5;
867 g_batch_id := p_batch_id;
868 l_pos := 6;
869 g_min_capacity := l_min_c;
870 l_pos := 7;
871 g_max_capacity := l_max_c;
872 l_pos := 8;
873 g_capacity_uom := l_cap_uom;
874 l_pos := 9;
875 END IF;
876
877 l_pos := 10;
878 RETURN g_max_capacity;
879 EXCEPTION
880 WHEN OTHERS THEN
881 insert_message (
882 p_table_name => 'none',
883 p_procedure_name => 'get_max_capacity',
884 p_parameters => 'batch_id = '
885 || p_batch_id
886 || ' resource = '
887 || p_rsrc,
888 p_message => SQLERRM || ' with pos = ' || l_pos,
889 p_error_type => 'D'
890 );
891 RAISE;
892 END get_max_capacity;
893
894 FUNCTION get_capacity_uom (
895 p_batch_id pm_btch_hdr_bak.batch_id%TYPE,
896 p_rsrc pm_oprn_dtl.resources%TYPE
897 )
898 RETURN VARCHAR2 IS
899 l_min_c NUMBER;
900 l_max_c NUMBER;
901 l_cap_uom VARCHAR2 (4);
902 l_return_status VARCHAR2 (1);
903 l_pos NUMBER := 0;
904 BEGIN
905 l_pos := 1;
906
907 IF p_batch_id = g_batch_id AND
908 p_rsrc = g_resource THEN
909 l_pos := 2;
910 RETURN g_capacity_uom;
911 ELSE
912 l_pos := 3;
913 get_capacity (
914 p_batch_id,
915 p_rsrc,
916 l_min_c,
917 l_max_c,
918 l_cap_uom,
919 l_return_status
920 );
921 l_pos := 4;
922 g_resource := p_rsrc;
923 l_pos := 5;
924 g_batch_id := p_batch_id;
925 l_pos := 6;
926 g_min_capacity := l_min_c;
927 l_pos := 7;
928 g_max_capacity := l_max_c;
929 l_pos := 8;
930 g_capacity_uom := l_cap_uom;
931 l_pos := 9;
932 END IF;
933
934 l_pos := 10;
935 RETURN g_capacity_uom;
936 EXCEPTION
937 WHEN OTHERS THEN
938 insert_message (
939 p_table_name => 'none',
940 p_procedure_name => 'get_capacity_uom',
941 p_parameters => 'batch_id = '
942 || p_batch_id
943 || ' resource = '
944 || p_rsrc,
945 p_message => SQLERRM || ' with pos = ' || l_pos,
946 p_error_type => 'D'
947 );
948 RAISE;
949 END get_capacity_uom;
950
951 FUNCTION get_actual_qty (
952 p_batch_id IN NUMBER,
953 p_line_id IN NUMBER,
954 p_actual_qty IN NUMBER
955 )
956 RETURN NUMBER IS
957 v_batch_status NUMBER;
958 v_qty NUMBER;
959 v_line_type pm_matl_dtl_bak.line_type%TYPE;
960 v_batch_type pm_btch_hdr_bak.batch_type%TYPE;
961 l_pos NUMBER := 0;
962 BEGIN
963 l_pos := 1;
964 SELECT batch_status, batch_type
965 INTO v_batch_status, v_batch_type
966 FROM gme_batch_header
967 WHERE batch_id = p_batch_id;
968 l_pos := 2;
969
970 IF v_batch_status <> 2 OR v_batch_type = 10 THEN
971 l_pos := 3;
972 RETURN p_actual_qty;
973 END IF;
974
975 l_pos := 4;
976 SELECT SUM (
977 gmicuom.uom_conversion (
978 i.item_id,
979 i.lot_id,
980 i.trans_qty,
981 i.trans_um,
982 l.item_um,
983 0
984 )
985 )
986 INTO v_qty
987 FROM ic_tran_pnd i, pm_matl_dtl_bak l
988 WHERE doc_id = p_batch_id AND
989 doc_type IN ('PROD', 'FPO') AND
990 i.line_id = p_line_id AND
991 i.line_id = l.line_id AND
992 completed_ind = 1 AND
993 delete_mark = 0;
994 l_pos := 5;
995
996 IF v_qty IS NULL THEN
997 l_pos := 6;
998 v_qty := 0;
999 END IF;
1000
1001 l_pos := 7;
1002 SELECT line_type
1003 INTO v_line_type
1004 FROM pm_matl_dtl_bak
1005 WHERE line_id = p_line_id;
1006 l_pos := 8;
1007
1008 IF v_line_type = -1 AND
1009 v_qty <> 0 THEN
1010 l_pos := 9;
1011 v_qty := -1 * v_qty;
1012 END IF;
1013
1014 l_pos := 10;
1015 RETURN v_qty;
1016 EXCEPTION
1017 WHEN OTHERS THEN
1018 insert_message (
1019 p_table_name => 'ic_tran_pnd',
1020 p_procedure_name => 'get_actual_qty',
1021 p_parameters => 'batch_id = '
1022 || p_batch_id
1023 || ' line_id = '
1024 || p_line_id
1025 || ' old actual_qty = '
1026 || p_actual_qty,
1027 p_message => SQLERRM || ' with pos = ' || l_pos,
1028 p_error_type => 'D'
1029 );
1030 RAISE;
1031 END get_actual_qty;
1032
1033 FUNCTION get_planned_qty (
1034 p_batch_id IN NUMBER,
1035 p_line_id IN NUMBER,
1036 p_plan_qty IN NUMBER
1037 )
1038 RETURN NUMBER IS
1039 v_batch_status NUMBER;
1040 v_plant_code pm_btch_hdr_bak.plant_code%TYPE;
1041 v_batch_no pm_btch_hdr_bak.batch_no%TYPE;
1042 v_batch_type pm_btch_hdr_bak.batch_type%TYPE;
1043 v_qty NUMBER;
1044 v_line_type pm_matl_dtl_bak.line_type%TYPE;
1045 v_line_no pm_matl_dtl_bak.line_no%TYPE;
1046 l_pos NUMBER := 0;
1047 l_line_type_desc VARCHAR2(80);
1048 l_batch_type_desc VARCHAR2(10);
1049
1050 plan_qty_null EXCEPTION;
1051 BEGIN
1052 l_pos := 1;
1053 SELECT batch_status, plant_code, batch_no, batch_type
1054 INTO v_batch_status, v_plant_code, v_batch_no, v_batch_type
1055 FROM gme_batch_header
1056 WHERE batch_id = p_batch_id;
1057 l_pos := 2;
1058
1059 IF v_batch_status <> 1 OR v_batch_type = 10 THEN
1060 RETURN p_plan_qty;
1061 END IF;
1062
1063 l_pos := 3;
1064 SELECT SUM (
1065 gmicuom.uom_conversion (
1066 i.item_id,
1067 i.lot_id,
1068 i.trans_qty / (1 + l.scrap_factor),
1069 i.trans_um,
1070 l.item_um,
1071 0
1072 )
1073 )
1074 INTO v_qty
1075 FROM ic_tran_pnd i, pm_matl_dtl_bak l
1076 WHERE doc_id = p_batch_id AND
1077 doc_type IN ('PROD', 'FPO') AND
1078 i.line_id = p_line_id AND
1079 i.line_id = l.line_id AND
1080 delete_mark = 0;
1081 l_pos := 4;
1082
1083 SELECT line_type, line_no
1084 INTO v_line_type, v_line_no
1085 FROM pm_matl_dtl_bak
1086 WHERE line_id = p_line_id;
1087 l_pos := 5;
1088
1089 IF v_qty IS NULL THEN
1090 IF v_line_type = -1 THEN
1091 l_line_type_desc := 'ingredient';
1092 ELSIF v_line_type = 1 THEN
1093 l_line_type_desc := 'product';
1094 ELSE
1095 l_line_type_desc := 'byproduct';
1096 END IF;
1097
1098 IF v_batch_type = 0 THEN
1099 l_batch_type_desc := 'Batch';
1100 ELSE
1101 l_batch_type_desc := 'FPO';
1102 END IF;
1103
1104 RAISE plan_qty_null;
1105 END IF;
1106
1107 IF v_line_type = -1 AND
1108 v_qty <> 0 THEN
1109 l_pos := 6;
1110 v_qty := -1 * v_qty;
1111 END IF;
1112
1113 l_pos := 7;
1114 RETURN v_qty;
1115 EXCEPTION
1116 WHEN plan_qty_null THEN
1117 insert_message (
1118 p_table_name => 'ic_tran_pnd',
1119 p_procedure_name => 'get_planned_qty',
1120 p_parameters => 'batch_id = '
1121 || p_batch_id
1122 || ' line_id = '
1123 || p_line_id
1124 || ' original plan_qty = '
1125 || p_plan_qty,
1126 p_message =>
1127 'Plan quantity could not be calculated from transactions for '||l_batch_type_desc||
1128 ' with plant code = '||v_plant_code||'- batch no = '||v_batch_no||
1129 ' and '||l_line_type_desc||' line no = '||v_line_no||'. Using original plan_qty.',
1130 p_error_type => 'I'
1131 );
1132 RETURN p_plan_qty;
1133 WHEN OTHERS THEN
1134 insert_message (
1135 p_table_name => 'ic_tran_pnd',
1136 p_procedure_name => 'get_planned_qty',
1137 p_parameters => 'batch_id = '
1138 || p_batch_id
1139 || ' line_id = '
1140 || p_line_id
1141 || ' original plan_qty = '
1142 || p_plan_qty,
1143 p_message => SQLERRM || ' with pos = ' || l_pos,
1144 p_error_type => 'D'
1145 );
1146 RAISE;
1147 END get_planned_qty;
1148
1149 FUNCTION get_wip_planned_qty (
1150 p_batch_id IN NUMBER,
1151 p_line_id IN NUMBER,
1152 p_actual_qty IN NUMBER
1153 )
1154 RETURN NUMBER IS
1155 v_batch_status NUMBER;
1156 v_qty NUMBER;
1157 v_line_type pm_matl_dtl_bak.line_type%TYPE;
1158 v_scrap_factor pm_matl_dtl_bak.scrap_factor%TYPE;
1159 v_item_um pm_matl_dtl_bak.item_um%TYPE;
1160 l_pos NUMBER := 0;
1161 BEGIN
1162 l_pos := 1;
1163 SELECT batch_status
1164 INTO v_batch_status
1165 FROM gme_batch_header
1166 WHERE batch_id = p_batch_id;
1167 l_pos := 2;
1168
1169 IF v_batch_status = -1 /* Cancelled */ OR
1170 v_batch_status = 1 /* Pending */ THEN
1171 l_pos := 3;
1172 RETURN NULL;
1173 END IF;
1174
1175 l_pos := 4;
1176 SELECT scrap_factor,
1177 item_um,
1178 line_type
1179 INTO v_scrap_factor,
1180 v_item_um,
1181 v_line_type
1182 FROM pm_matl_dtl_bak
1183 WHERE line_id = p_line_id;
1184 l_pos := 5;
1185
1186 IF v_batch_status = 3 /* Certified */ OR
1187 v_batch_status = 4 /* Closed */ THEN
1188 l_pos := 6;
1189 v_qty := p_actual_qty / (1 + v_scrap_factor);
1190 RETURN v_qty;
1191 END IF;
1192
1193 l_pos := 7;
1194 SELECT SUM (
1195 gmicuom.uom_conversion (
1196 item_id,
1197 lot_id,
1198 trans_qty / (1 + v_scrap_factor),
1199 trans_um,
1200 v_item_um,
1201 0
1202 )
1203 )
1204 INTO v_qty
1205 FROM ic_tran_pnd
1206 WHERE doc_id = p_batch_id AND
1207 doc_type IN ('PROD', 'FPO') AND
1208 line_id = p_line_id AND
1209 delete_mark = 0;
1210 l_pos := 8;
1211
1212 IF v_line_type = -1 AND
1213 v_qty <> 0 THEN
1214 l_pos := 9;
1215 v_qty := -1 * v_qty;
1216 END IF;
1217
1218 l_pos := 10;
1219 RETURN v_qty;
1220 EXCEPTION
1221 WHEN OTHERS THEN
1222 insert_message (
1223 p_table_name => 'ic_tran_pnd',
1224 p_procedure_name => 'get_wip_planned_qty',
1225 p_parameters => 'batch_id = '
1226 || p_batch_id
1227 || ' line_id = '
1228 || p_line_id
1229 || ' old actual_qty = '
1230 || p_actual_qty,
1231 p_message => SQLERRM || ' with pos = ' || l_pos,
1232 p_error_type => 'D'
1233 );
1234 RAISE;
1235 END get_wip_planned_qty;
1236
1237 PROCEDURE check_wip_batches (
1238 x_return_status OUT NOCOPY VARCHAR2,
1239 p_reverse_compl_def_txns IN BOOLEAN DEFAULT FALSE
1240 ) IS
1241 /* Local variables */
1242 l_batch_header pm_btch_hdr_bak%ROWTYPE;
1243 l_return_status VARCHAR2 (1);
1244 l_trans_is_reversed NUMBER;
1245 l_is_plain BOOLEAN;
1246 l_def_lot_id ic_tran_pnd.trans_id%TYPE;
1247 l_completed_ind ic_tran_pnd.completed_ind%TYPE;
1248 l_trans_qty ic_tran_pnd.trans_qty%TYPE;
1249 l_ic_tran_cmp_out ic_tran_cmp%ROWTYPE;
1250 l_ic_tran_pnd_out ic_tran_pnd%ROWTYPE;
1251 l_tran_row ic_tran_pnd%ROWTYPE;
1252 l_tran_reverse gmi_trans_engine_pub.ictran_rec;
1253 l_tran_pending gmi_trans_engine_pub.ictran_rec;
1254 l_lock_status BOOLEAN;
1255 l_reversible BOOLEAN;
1256 l_reversal_count NUMBER := 0;
1257 l_line_type_desc VARCHAR2 (20);
1258 l_pos NUMBER := 0;
1259 l_msg_data VARCHAR2 (2000);
1260 l_message VARCHAR2 (2000);
1261 l_msg_count NUMBER;
1262 l_load_trans_fail BOOLEAN;
1263 error_create_tran EXCEPTION;
1264 error_inserting_txn EXCEPTION;
1265 error_build_ic_tran_row EXCEPTION;
1266
1267 /* Cursor definitions */
1268 CURSOR cur_get_batches IS
1269 SELECT h.batch_id,
1270 h.plant_code,
1271 h.batch_type,
1272 h.batch_no,
1273 d.line_no,
1274 d.line_id
1275 FROM pm_btch_hdr_bak h, pm_matl_dtl_bak d
1276 WHERE h.batch_status = 2 AND
1277 d.batch_id = h.batch_id AND
1278 d.line_type IN (-1, 1) AND
1279 d.in_use <
1280 100 -- => ensure you only get those materials that were not migrated.
1281 ORDER BY h.plant_code, h.batch_type, h.batch_no, d.line_no;
1282 BEGIN
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284 l_pos := 1;
1285
1286 IF g_mig_date IS NULL THEN
1287 g_mig_date := SYSDATE;
1288 END IF;
1289
1290 l_pos := 2;
1291
1292 -- Retrieve all ingredients and products for wip batches
1293 -- NOTE: we must check the products because there could be a phantom product corresponding to an
1294 -- auto release phantom ingredient which will have a completed default txn.
1295 FOR get_rec IN cur_get_batches
1296 LOOP
1297 l_pos := 3;
1298 l_batch_header.batch_id := get_rec.batch_id;
1299 load_trans (l_batch_header, l_return_status);
1300
1301 IF l_return_status <> x_return_status THEN
1302 l_load_trans_fail := TRUE;
1303 l_def_lot_id := NULL;
1304
1305 insert_message (
1306 p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
1307 p_procedure_name => 'check_wip_batches',
1308 p_parameters => 'batch_id = '
1309 || l_batch_header.batch_id
1310 || ' line_id = '
1311 || get_rec.line_id,
1312 p_message => 'Unable to load the transactions',
1313 p_error_type => FND_API.G_RET_STS_ERROR
1314 );
1315 ELSE
1316 l_load_trans_fail := FALSE;
1317 l_pos := 4;
1318 get_default_lot (
1319 get_rec.line_id,
1320 l_def_lot_id,
1321 l_is_plain,
1322 l_return_status
1323 );
1324 END IF;
1325
1326 l_pos := 5;
1327
1328 IF l_def_lot_id IS NULL OR
1329 l_return_status <> x_return_status THEN
1330 l_pos := 6;
1331 -- No need to write message if the load trans failed, because message
1332 -- for that was already written, and if load_trans failed, it follows that def lot
1333 -- could not be found.
1334 IF l_load_trans_fail = FALSE THEN
1335 insert_message (
1336 p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
1337 p_procedure_name => 'check_wip_batches',
1338 p_parameters => 'batch_id = '
1339 || l_batch_header.batch_id
1340 || ' line_id = '
1341 || get_rec.line_id,
1342 p_message => 'Unable to determine the default lot.',
1343 p_error_type => FND_API.G_RET_STS_ERROR
1344 );
1345 END IF;
1346 ELSE
1347 l_pos := 7;
1348 SELECT completed_ind,
1349 trans_qty
1350 INTO l_completed_ind,
1351 l_trans_qty
1352 FROM ic_tran_pnd
1353 WHERE trans_id = l_def_lot_id;
1354 l_pos := 8;
1355
1356 IF (l_is_plain = TRUE OR
1357 l_completed_ind = 0 OR
1358 l_trans_qty = 0
1359 ) THEN
1360 l_pos := 9;
1361 /* skip it
1362 1. if it's plain, it'll be dealt with later; i.e the def trans will be split if necessary
1363 2. if it's not completed, we don't care about it here
1364 3. if the trans_qty is 0 and it's completed, that will be un-completed later
1365 Items 1 and 3 will both be dealt with in split_trans_line... */
1366 NULL;
1367 ELSE
1368 l_pos := 10;
1369
1370 IF p_reverse_compl_def_txns THEN
1371 /* Let's reverse the completed transaction and create a pending transaction for the same qty. */
1372 SELECT *
1373 INTO l_tran_row
1374 FROM ic_tran_pnd
1375 WHERE trans_id = l_def_lot_id;
1376
1377 /* The cursor is only retrieving ingredients and products (only phantom products will ever */
1378 /* fall into this because phantom products are the only products that can be completed */
1379 /* in a WIP state. */
1380 IF l_tran_row.line_type = -1 THEN
1381 l_line_type_desc := 'ingredient';
1382 ELSE
1383 l_line_type_desc := 'product';
1384 END IF;
1385
1386 l_pos := 11;
1387 -- Set the USER_ID for each transaction reversed. This is required for the inventory API
1388 fnd_profile.put ('USER_ID', TO_CHAR (l_tran_row.created_by));
1389 l_pos := 12;
1390
1391 -- Bug 3372169 Changed call lock_inventory procedure to pass parameters by name.
1392 gmi_locks.lock_inventory (
1393 i_item_id => l_tran_row.item_id,
1394 i_whse_code => l_tran_row.whse_code,
1395 i_lot_id => l_tran_row.lot_id,
1396 i_location => l_tran_row.location,
1397 o_lock_status => l_lock_status
1398 );
1399
1400 l_pos := 13;
1401
1402 IF l_lock_status OR
1403 l_lock_status IS NULL THEN
1404 build_gmi_trans (
1405 p_ic_tran_row => l_tran_row,
1406 x_tran_row => l_tran_reverse,
1407 x_return_status => l_return_status
1408 );
1409
1410 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1411 RAISE error_build_ic_tran_row;
1412 END IF;
1413
1414 -- Check that the trans_date is in an open calendar period and whse.
1415 IF (gmiccal.trans_date_validate (
1416 trans_date => l_tran_reverse.trans_date,
1417 porgn_code => l_tran_reverse.orgn_code,
1418 pwhse_code => l_tran_reverse.whse_code
1419 ) <> 0
1420 ) THEN
1421 IF (gmiccal.trans_date_validate (
1422 trans_date => g_mig_date,
1423 porgn_code => l_tran_reverse.orgn_code,
1424 pwhse_code => l_tran_reverse.whse_code
1425 ) <> 0
1426 ) THEN
1427 l_reversible := FALSE;
1428 l_message := 'Cannot reverse trans_id '
1429 || l_tran_row.trans_id
1430 || ' '
1431 || 'with trans date '
1432 || TO_CHAR (
1433 l_tran_reverse.trans_date,
1434 g_date_format
1435 )
1436 || ' '
1437 || 'and system date '
1438 || TO_CHAR (
1439 g_mig_date,
1440 g_date_format
1441 );
1442 insert_message (
1443 p_table_name => 'IC_TRAN_PND',
1444 p_procedure_name => 'CHECK_WIP_BATCHES',
1445 p_parameters => 'Batch_Id=>'
1446 || TO_CHAR (get_rec.batch_id),
1447 p_message => l_message,
1448 p_error_type => FND_API.G_RET_STS_ERROR
1449 );
1450 ELSE
1451 l_reversible := TRUE;
1452 l_message :=
1453 'Cannot reverse trans_id '
1454 || l_tran_row.trans_id
1455 || ' '
1456 || 'with trans date '
1457 || TO_CHAR (
1458 l_tran_reverse.trans_date,
1459 g_date_format
1460 )
1461 || '. '
1462 || 'Using system date '
1463 || TO_CHAR (
1464 g_mig_date,
1465 g_date_format
1466 )
1467 || ' for the reversal of this transaction';
1468 /* B2417758 Moved this line from the statemet above
1469 So that the message will have the correct date */
1470 l_tran_reverse.trans_date := g_mig_date;
1471 insert_message (
1472 p_table_name => 'IC_TRAN_PND',
1473 p_procedure_name => 'CHECK_WIP_BATCHES',
1474 p_parameters => 'Batch_Id=>'
1475 || TO_CHAR (get_rec.batch_id),
1476 p_message => l_message,
1477 p_error_type => 'I'
1478 );
1479 END IF;
1480 ELSE
1481 -- trans_date_validate returned 0 for trans_date, so we can use the trans_date to reverse
1482 l_reversible := TRUE;
1483 END IF;
1484
1485 IF l_reversible THEN
1486 -- Reverse Out The Amount and Re-Post
1487 l_tran_reverse.trans_qty :=
1488 -1 * l_tran_reverse.trans_qty;
1489 l_tran_reverse.trans_qty2 :=
1490 -1 * l_tran_reverse.trans_qty2;
1491 gmi_trans_engine_pub.create_completed_transaction (
1492 p_api_version => 1,
1493 p_init_msg_list => FND_API.g_false,
1494 p_commit => FND_API.g_false,
1495 p_validation_level => FND_API.g_valid_level_full,
1496 p_tran_rec => l_tran_reverse,
1497 x_tran_row => l_ic_tran_cmp_out,
1498 x_return_status => l_return_status,
1499 x_msg_count => l_msg_count,
1500 x_msg_data => l_msg_data,
1501 p_table_name => 'IC_TRAN_PND'
1502 );
1503
1504 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1505
1506 --dbms_output.put_line (fnd_msg_pub.get (p_encoded => FND_API.g_false));
1507
1508 l_message :=
1509 'Error creating reversing transaction for trans_id '
1510 || l_tran_row.trans_id
1511 || ' in batch no '
1512 || get_rec.batch_no
1513 || ' in plant '
1514 || get_rec.plant_code
1515 || ' for '
1516 || l_line_type_desc
1517 || ' line number '
1518 || get_rec.line_no;
1519
1520 IF l_msg_count > 0 THEN
1521 l_message :=
1522 l_message
1523 || ' with following error: '
1524 || fnd_msg_pub.get (
1525 p_encoded => FND_API.g_false
1526 );
1527 END IF;
1528
1529 l_return_status := FND_API.G_RET_STS_ERROR;
1530 insert_message (
1531 p_table_name => 'IC_TRAN_PND',
1532 p_procedure_name => 'CHECK_WIP_BATCHES',
1533 p_parameters => 'Batch_Id=>'
1534 || TO_CHAR (get_rec.batch_id)
1535 || ' line_id=>'
1536 || TO_CHAR (get_rec.line_id)
1537 || ' trans_id=>'
1538 || TO_CHAR(l_tran_row.trans_id),
1539 p_message => l_message,
1540 p_error_type => l_return_status
1541 );
1542 RAISE error_create_tran;
1543 END IF;
1544
1545 insert_inv_txns_gtmp (
1546 p_batch_id => l_ic_tran_cmp_out.doc_id,
1547 p_doc_type => l_ic_tran_cmp_out.doc_type,
1548 p_trans_id => l_ic_tran_cmp_out.trans_id,
1549 x_return_status => l_return_status
1550 );
1551
1552 IF l_return_status <> x_return_status THEN
1553 RAISE error_inserting_txn;
1554 END IF;
1555
1556 /* Create corresponding pending transaction */
1557 build_gmi_trans (
1558 p_ic_tran_row => l_tran_row,
1559 x_tran_row => l_tran_pending,
1560 x_return_status => l_return_status
1561 );
1562
1563 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1564 RAISE error_build_ic_tran_row;
1565 END IF;
1566
1567 -- Stamp the pending transaction with sysdate if it was determined that trans_date is not usable
1568 IF l_tran_reverse.trans_date = g_mig_date THEN
1569 l_tran_pending.trans_date := g_mig_date;
1570 END IF;
1571
1572 /* Insert a pending transaction */
1573
1574 gmi_trans_engine_pub.create_pending_transaction (
1575 1,
1576 FND_API.g_false,
1577 FND_API.g_false,
1578 FND_API.g_valid_level_full,
1579 l_tran_pending,
1580 l_ic_tran_pnd_out,
1581 l_return_status,
1582 l_msg_count,
1583 l_msg_data
1584 );
1585
1586 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1587 l_message :=
1588 'Error creating pending transaction for trans_id '
1589 || l_tran_row.trans_id
1590 || ' in batch no '
1591 || get_rec.batch_no
1592 || ' in plant '
1593 || get_rec.plant_code
1594 || ' for '
1595 || l_line_type_desc
1596 || ' line number '
1597 || get_rec.line_no;
1598
1599 IF l_msg_count > 0 THEN
1600 l_message :=
1601 l_message
1602 || ' with following error: '
1603 || fnd_msg_pub.get (
1604 p_encoded => FND_API.g_false
1605 );
1606 END IF;
1607
1608 l_return_status := FND_API.G_RET_STS_ERROR;
1609 insert_message (
1610 p_table_name => 'IC_TRAN_PND',
1611 p_procedure_name => 'CHECK_WIP_BATCHES',
1612 p_parameters => 'Batch_Id=>'
1613 || TO_CHAR (get_rec.batch_id)
1614 || ' line_id=>'
1615 || TO_CHAR (get_rec.line_id)
1616 || ' trans_id=>'
1617 || TO_CHAR(l_tran_row.trans_id),
1618 p_message => l_message,
1619 p_error_type => l_return_status
1620 );
1621 RAISE error_create_tran;
1622 END IF;
1623
1624 insert_inv_txns_gtmp (
1625 p_batch_id => l_ic_tran_pnd_out.doc_id,
1626 p_doc_type => l_ic_tran_pnd_out.doc_type,
1627 p_trans_id => l_ic_tran_pnd_out.trans_id,
1628 x_return_status => l_return_status
1629 );
1630
1631 IF l_return_status <> x_return_status THEN
1632 RAISE error_inserting_txn;
1633 END IF;
1634
1635 /* Now let's indicate in the temporary table about the reversals */
1636 UPDATE gme_inventory_txns_gtmp
1637 SET transaction_no = 2
1638 WHERE trans_id IN
1639 (l_def_lot_id, l_ic_tran_cmp_out.trans_id);
1640 END IF; /* IF l_reversible THEN */
1641 ELSE
1642 l_message :=
1643 'Unable to lock inventory tables for trans_id '
1644 || l_tran_row.trans_id
1645 || ' in batch no '
1646 || get_rec.batch_no
1647 || ' in plant '
1648 || get_rec.plant_code
1649 || ' for '
1650 || l_line_type_desc
1651 || ' line number '
1652 || get_rec.line_no;
1653 l_return_status := FND_API.G_RET_STS_ERROR;
1654 insert_message (
1655 p_table_name => 'IC_TRAN_PND',
1656 p_procedure_name => 'CHECK_WIP_BATCHES',
1657 p_parameters => 'Batch_Id=>'
1658 || TO_CHAR (get_rec.batch_id),
1659 p_message => l_message,
1660 p_error_type => l_return_status
1661 );
1662 RAISE error_create_tran;
1663 END IF; /* IF l_lock_status OR */
1664 END IF; /* IF p_reverse_compl_def_txns THEN */
1665
1666 insert_message (
1667 p_table_name => 'IC_TRAN_PND',
1668 p_procedure_name => 'CHECK_WIP_BATCHES',
1669 p_parameters => 'Batch_Id=>' || TO_CHAR (get_rec.batch_id),
1670 p_message => 'Completed default lot transaction found for batch '
1671 || get_rec.batch_no
1672 || ' in plant '
1673 || get_rec.plant_code
1674 || ' for '
1675 || l_line_type_desc
1676 || ' line number '
1677 || get_rec.line_no,
1678 p_error_type => 'I'
1679 );
1680 l_reversal_count := l_reversal_count + 1;
1681 END IF; /* IF (l_is_plain = TRUE OR */
1682 END IF; /* IF l_def_lot_id IS NULL OR l_return_status ... */
1683 END LOOP;
1684
1685 insert_message (
1686 p_table_name => 'ic_tran_pnd',
1687 p_procedure_name => 'check_wip_batches',
1688 p_parameters => 'none',
1689 p_message => 'number of transactions reversed = ' || l_reversal_count,
1690 p_error_type => 'P'
1691 );
1692 EXCEPTION
1693 WHEN error_create_tran OR error_build_ic_tran_row OR error_inserting_txn THEN
1694 x_return_status := l_return_status;
1695 RAISE;
1696 WHEN OTHERS THEN
1697 insert_message (
1698 p_table_name => 'IC_TRAN_PND',
1699 p_procedure_name => 'CHECK_WIP_BATCHES',
1700 p_parameters => '',
1701 p_message => SQLERRM,
1702 p_error_type => 'D'
1703 );
1704 RAISE;
1705 END check_wip_batches;
1706
1707 PROCEDURE build_gmi_trans (
1708 p_ic_tran_row IN ic_tran_pnd%ROWTYPE,
1709 x_tran_row OUT NOCOPY gmi_trans_engine_pub.ictran_rec,
1710 x_return_status OUT NOCOPY VARCHAR2
1711 ) IS
1712 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_GMI_TRANS';
1713 l_return_status VARCHAR2 (1) := FND_API.G_RET_STS_SUCCESS;
1714 l_tran_row gmi_trans_engine_pub.ictran_rec;
1715 BEGIN
1716 -- The trans_um,trans_um will Should always be in the Items Base
1717 -- Units of measure. No Conversions are done in this routine.
1718 -- Assumes all IN data is correct.
1719
1720
1721 -- Lets Populate Inventory TRAN Row
1722 --gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Build INV tran Row');
1723 l_tran_row.trans_um := p_ic_tran_row.trans_um;
1724 l_tran_row.trans_qty := p_ic_tran_row.trans_qty;
1725 l_tran_row.trans_um2 := p_ic_tran_row.trans_um2;
1726 l_tran_row.trans_qty2 := p_ic_tran_row.trans_qty2;
1727 l_tran_row.item_id := p_ic_tran_row.item_id;
1728 l_tran_row.line_id := p_ic_tran_row.line_id;
1729 l_tran_row.co_code := p_ic_tran_row.co_code;
1730 l_tran_row.orgn_code := p_ic_tran_row.orgn_code;
1731 l_tran_row.whse_code := p_ic_tran_row.whse_code;
1732 l_tran_row.lot_id := NVL (p_ic_tran_row.lot_id, 0);
1733 l_tran_row.location := NVL (p_ic_tran_row.location, p_default_loct);
1734 l_tran_row.doc_id := p_ic_tran_row.doc_id;
1735 l_tran_row.doc_type := p_ic_tran_row.doc_type;
1736 l_tran_row.doc_line := p_ic_tran_row.doc_line;
1737 l_tran_row.line_type := p_ic_tran_row.line_type;
1738 l_tran_row.reason_code := p_ic_tran_row.reason_code;
1739 l_tran_row.trans_date := p_ic_tran_row.trans_date;
1740 l_tran_row.qc_grade := p_ic_tran_row.qc_grade;
1741 l_tran_row.lot_status := p_ic_tran_row.lot_status;
1742 l_tran_row.trans_stat := p_ic_tran_row.trans_stat;
1743 l_tran_row.event_id := p_ic_tran_row.event_id;
1744 l_tran_row.staged_ind := NVL (p_ic_tran_row.staged_ind, 0);
1745 l_tran_row.text_code := p_ic_tran_row.text_code;
1746 l_tran_row.user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
1747 x_return_status := l_return_status;
1748 x_tran_row := l_tran_row;
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 x_return_status := 'D';
1752 insert_message (
1753 p_table_name => 'IC_TRAN_PND',
1754 p_procedure_name => 'BUILD_GMI_TRANS',
1755 p_parameters => 'Batch_Id=>'
1756 || TO_CHAR (p_ic_tran_row.doc_id)
1757 || 'Trans ID=>'
1758 || TO_CHAR (p_ic_tran_row.trans_id),
1759 p_message => SQLERRM,
1760 p_error_type => x_return_status
1761 );
1762 END build_gmi_trans;
1763
1764 FUNCTION is_GME_validated RETURN BOOLEAN IS
1765 l_table_name VARCHAR2(50) := 'GME_validation';
1766 BEGIN
1767
1768 RETURN TRUE;
1769
1770 EXCEPTION
1771 WHEN OTHERS THEN
1772 insert_message (
1773 p_table_name => 'gme_migration_control',
1774 p_procedure_name => 'is_GME_validated',
1775 p_parameters => l_table_name,
1776 p_message => SQLERRM,
1777 p_error_type => 'D'
1778 );
1779 RAISE;
1780 END is_GME_validated;
1781
1782 PROCEDURE set_GME_validated IS
1783 l_pos NUMBER := 0;
1784 l_table_name VARCHAR2(50) := 'GME_validation';
1785
1786 BEGIN
1787 l_pos := 1;
1788
1789 UPDATE gme_migration_control
1790 SET migrated_ind = 'Y',
1791 last_update_date = g_mig_date
1792 WHERE table_name = l_table_name;
1793
1794 l_pos := 2;
1795
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 insert_message (
1799 p_table_name => 'gme_migration_control',
1800 p_procedure_name => 'set_GME_validated',
1801 p_parameters => l_table_name,
1802 p_message => SQLERRM || ' with pos = ' || l_pos,
1803 p_error_type => 'D'
1804 );
1805 RAISE;
1806 END set_GME_validated;
1807
1808 PROCEDURE reset_GME_validated IS
1809 l_mig_ind VARCHAR2(1);
1810 l_pos NUMBER := 0;
1811
1812 l_table_name VARCHAR2(50) := 'GME_validation';
1813
1814 CURSOR cur_check_control (v_table_name VARCHAR2) IS
1815 SELECT migrated_ind
1816 FROM gme_migration_control
1817 WHERE table_name = v_table_name;
1818
1819 BEGIN
1820
1821 l_pos := 1;
1822 OPEN cur_check_control (l_table_name);
1823 l_pos := 2;
1824 FETCH cur_check_control INTO l_mig_ind;
1825 l_pos := 3;
1826
1827 IF cur_check_control%NOTFOUND THEN
1828 INSERT INTO gme_migration_control
1829 (table_name,
1830 migrated_ind,
1831 last_update_date
1832 )
1833 VALUES (l_table_name,
1834 'N',
1835 g_mig_date
1836 );
1837
1838 l_pos := 4;
1839 ELSE
1840 UPDATE gme_migration_control
1841 SET migrated_ind = 'N',
1842 last_update_date = g_mig_date
1843 WHERE table_name = l_table_name;
1844 END IF;
1845
1846 l_pos := 5;
1847 CLOSE cur_check_control;
1848 l_pos := 6;
1849
1850 EXCEPTION
1851 WHEN OTHERS THEN
1852 insert_message (
1853 p_table_name => 'gme_migration_control',
1854 p_procedure_name => 'reset_GME_validated',
1855 p_parameters => l_table_name,
1856 p_message => SQLERRM || ' with pos = ' || l_pos,
1857 p_error_type => 'D'
1858 );
1859 RAISE;
1860 END reset_GME_validated;
1861
1862 FUNCTION is_table_migrated (p_table_name IN VARCHAR2)
1863 RETURN BOOLEAN IS
1864 l_mig_ind VARCHAR2(1);
1865 l_pos NUMBER := 0;
1866
1867
1868
1869 CURSOR cur_check_control (v_table_name VARCHAR2) IS
1870 SELECT migrated_ind
1871 FROM gme_migration_control
1872 WHERE table_name = v_table_name;
1873
1874 BEGIN
1875
1876 l_pos := 1;
1877 OPEN cur_check_control (p_table_name);
1878 l_pos := 2;
1879 FETCH cur_check_control INTO l_mig_ind;
1880 l_pos := 3;
1881
1882 IF cur_check_control%NOTFOUND THEN
1883 INSERT INTO gme_migration_control
1884 (table_name,
1885 migrated_ind,
1886 last_update_date
1887 )
1888 VALUES (p_table_name,
1889 'N',
1890 g_mig_date
1891 );
1892
1893 l_pos := 4;
1894 RETURN FALSE;
1895 END IF;
1896
1897 l_pos := 5;
1898 CLOSE cur_check_control;
1899 l_pos := 6;
1900
1901 IF l_mig_ind = 'Y' THEN
1902 l_pos := 7;
1903 RETURN TRUE;
1904 ELSE
1905 l_pos := 8;
1906 RETURN FALSE;
1907 END IF;
1908
1909 EXCEPTION
1910 WHEN OTHERS THEN
1911 insert_message (
1912 p_table_name => 'gme_migration_control',
1913 p_procedure_name => 'is_table_migrated',
1914 p_parameters => p_table_name,
1915 p_message => SQLERRM || ' with pos = ' || l_pos,
1916 p_error_type => 'D'
1917 );
1918 RAISE;
1919 END is_table_migrated;
1920
1921 PROCEDURE set_table_migrated (
1922 p_table_name IN VARCHAR2,
1923 x_return_status OUT NOCOPY VARCHAR2
1924 ) IS
1925 l_pos NUMBER := 0;
1926 BEGIN
1927 /* NOTE: this assumes that a record already exists in gme_migration_control
1928 for p_table_name. This is because in is_table_migrated, if the record is
1929 not found, it is created. And since is_table_migrated is always called before
1930 this procedure, we can assume that the record is there. */
1931
1932 x_return_status := FND_API.G_RET_STS_SUCCESS;
1933
1934 l_pos := 1;
1935
1936 UPDATE gme_migration_control
1937 SET migrated_ind = 'Y',
1938 last_update_date = g_mig_date
1939 WHERE table_name = p_table_name;
1940
1941 l_pos := 2;
1942
1943 EXCEPTION
1944 WHEN OTHERS THEN
1945 insert_message (
1946 p_table_name => 'gme_migration_control',
1947 p_procedure_name => 'set_table_migrated',
1948 p_parameters => p_table_name,
1949 p_message => SQLERRM || ' with pos = ' || l_pos,
1950 p_error_type => 'D'
1951 );
1952 RAISE;
1953 END set_table_migrated;
1954
1955 PROCEDURE tablespace_check(
1956 p_User IN VARCHAR2,
1957 p_pct_free IN NUMBER) IS
1958
1959 l_pos NUMBER := 0;
1960
1961 l_tablespace_name VARCHAR2(50);
1962
1963 l_total_space NUMBER(12);
1964 l_free_space NUMBER(12);
1965 l_pct_free NUMBER(6,3);
1966
1967 CURSOR cur_get_tablespace_name(v_User VARCHAR2) IS
1968 SELECT default_tablespace tablespace_name
1969 FROM dba_users
1970 WHERE username like v_User
1971 UNION
1972 SELECT distinct tablespace_name
1973 FROM all_tables
1974 WHERE owner like v_User;
1975
1976 l_cur_ts_name cur_get_tablespace_name%ROWTYPE;
1977
1978 BEGIN
1979 l_pos := 1;
1980
1981 OPEN cur_get_tablespace_name(p_User);
1982
1983 l_pos := 2;
1984
1985 FETCH cur_get_tablespace_name INTO l_cur_ts_name;
1986
1987 l_pos := 3;
1988
1989 WHILE cur_get_tablespace_name%FOUND LOOP
1990
1991 l_pos := 3.1;
1992
1993 --
1994 -- Get the total space for the current tablespace
1995 --
1996 SELECT sum(bytes) into l_total_space
1997 FROM dba_data_files
1998 WHERE TABLESPACE_NAME = l_cur_ts_name.tablespace_name;
1999 --
2000 -- Get the free space for the current tablespace
2001 --
2002
2003 l_pos := 3.2;
2004
2005 SELECT sum(bytes) into l_free_space
2006 FROM dba_free_space
2007 WHERE TABLESPACE_NAME = l_cur_ts_name.tablespace_name;
2008
2009 l_pos := 3.3;
2010
2011 --
2012 -- calculate the percent free for the current tablespace
2013 --
2014 l_pct_free := (l_free_space / l_total_space) * 100;
2015
2016 l_pos := 3.4;
2017
2018 -- Validate that the percent free is sufficient
2019 IF l_pct_free < p_pct_free THEN
2020 insert_message (
2021 p_table_name => 'none',
2022 p_procedure_name => 'tablespace_check',
2023 p_parameters => 'USER= '||p_User||
2024 ' tablespace name= '||l_cur_ts_name.tablespace_name||
2025 ' total space= '||to_char(l_total_space)||' bytes'||
2026 ' free space= '||to_char(l_free_space)||' bytes'||
2027 ' target minimum % free= '||to_char(p_pct_free)||
2028 ' actual % free= '||to_char(l_pct_free),
2029 p_message => 'Tablespace information',
2030 p_error_type => 'I'
2031 );
2032 END IF;
2033
2034 l_pos := 3.5;
2035
2036 FETCH cur_get_tablespace_name INTO l_cur_ts_name;
2037
2038 l_pos := 3.6;
2039
2040 END LOOP;
2041
2042 l_pos := 4;
2043
2044 CLOSE cur_get_tablespace_name;
2045
2046 l_pos := 5;
2047
2048 EXCEPTION
2049 WHEN OTHERS THEN
2050 insert_message (
2051 p_table_name => 'none',
2052 p_procedure_name => 'tablespace_check',
2053 p_parameters => '',
2054 p_message => SQLERRM || ' with pos = ' || l_pos,
2055 p_error_type => 'D'
2056 );
2057 RAISE;
2058 END tablespace_check;
2059
2060 FUNCTION GME_data_exists RETURN BOOLEAN IS
2061
2062 CURSOR cur_num_batches IS
2063 SELECT count(1)
2064 FROM pm_btch_hdr_bak;
2065
2066 l_num_batches NUMBER;
2067 l_pos NUMBER;
2068 BEGIN
2069 l_pos := 1;
2070 OPEN cur_num_batches;
2071 l_pos := 2;
2072 FETCH cur_num_batches INTO l_num_batches;
2073 l_pos := 3;
2074 CLOSE cur_num_batches;
2075 l_pos := 4;
2076
2077 IF l_num_batches > 0 THEN
2078 l_pos := 4.1;
2079 RETURN TRUE;
2080 ELSE
2081 l_pos := 4.2;
2082 RETURN FALSE;
2083 END IF;
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087 insert_message (
2088 p_table_name => 'none',
2089 p_procedure_name => 'GME_data_exists',
2090 p_parameters => '',
2091 p_message => SQLERRM || ' with pos = ' || l_pos,
2092 p_error_type => 'D'
2093 );
2094 END GME_data_exists;
2095
2096 PROCEDURE run (p_commit IN BOOLEAN DEFAULT FALSE) IS
2097 l_pos NUMBER := 0;
2098 l_message VARCHAR2 (2000);
2099 l_return_status VARCHAR2 (1);
2100 l_commit_text VARCHAR2 (10);
2101 error_detail EXCEPTION;
2102 ERROR_NO_VALIDATION EXCEPTION;
2103 GME_NO_DATA EXCEPTION;
2104 BEGIN
2105
2106 l_pos := 0.5;
2107
2108 initialize_migration;
2109
2110 IF p_commit THEN
2111 l_commit_text := 'TRUE';
2112 ELSE
2113 l_commit_text := 'FALSE';
2114 END IF;
2115
2116 IF GME_data_exists THEN
2117 NULL;
2118 ELSE
2119 RAISE GME_NO_DATA;
2120 END IF;
2121
2122 tablespace_check(p_User => g_tablespace_User, p_pct_free => g_tablespace_target_free);
2123
2124 l_pos := 1;
2125
2126 IF p_commit THEN
2127 IF is_GME_validated THEN
2128 NULL;
2129 ELSE
2130 RAISE ERROR_NO_VALIDATION;
2131 END IF;
2132 ELSE
2133 insert_message (
2134 p_table_name => NULL,
2135 p_procedure_name => 'reset_GME_validated',
2136 p_parameters => NULL,
2137 p_message => NULL,
2138 p_error_type => 'V' -- validation control
2139 );
2140 END IF;
2141
2142 insert_message (
2143 p_table_name => 'none',
2144 p_procedure_name => 'run',
2145 p_parameters => 'p_commit = '||l_commit_text,
2146 p_message => 'Procedure has started',
2147 p_error_type => 'P'
2148 );
2149
2150 IF (is_table_migrated (p_table_name => 'UNLOCK_ALL') = FALSE) THEN
2151 l_pos := 1.010;
2152 unlock_all;
2153 l_pos := 1.011;
2154
2155 set_table_migrated (
2156 p_table_name => 'UNLOCK_ALL',
2157 x_return_status => l_return_status
2158 );
2159
2160 IF p_commit THEN
2161 COMMIT;
2162 END IF;
2163 END IF;
2164
2165 IF (is_table_migrated (p_table_name => 'DEL_STEP_DTL') = FALSE) THEN
2166 l_pos := 1.020;
2167 del_step_dtl_for_del_steps;
2168 l_pos := 1.021;
2169
2170 set_table_migrated (
2171 p_table_name => 'DEL_STEP_DTL',
2172 x_return_status => l_return_status
2173 );
2174
2175 IF p_commit THEN
2176 COMMIT;
2177 END IF;
2178 END IF;
2179
2180 IF (is_table_migrated (p_table_name => 'DUPLICATE_LINE_NO') = FALSE) THEN
2181 l_pos := 1.1;
2182 renumber_duplicate_line_no;
2183 l_pos := 1.2;
2184
2185 set_table_migrated (
2186 p_table_name => 'DUPLICATE_LINE_NO',
2187 x_return_status => l_return_status
2188 );
2189
2190 IF p_commit THEN
2191 COMMIT;
2192 END IF;
2193 END IF;
2194
2195 IF (is_table_migrated (p_table_name => 'PM_BTCH_HDR') = FALSE) THEN
2196 l_pos := 2;
2197 insert_batch_header (x_return_status => l_return_status);
2198 l_pos := 3;
2199
2200 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2201 RAISE error_detail;
2202 END IF;
2203
2204 set_table_migrated (
2205 p_table_name => 'PM_BTCH_HDR',
2206 x_return_status => l_return_status
2207 );
2208
2209 IF p_commit THEN
2210 COMMIT;
2211 END IF;
2212 END IF;
2213
2214 IF (is_table_migrated (p_table_name => 'IC_TRAN_PND_WIP') = FALSE) THEN
2215 l_pos := 4;
2216 -- check_wip_batches must be before insert_material_details because it only checks those
2217 -- materials that have not been migrated
2218 check_wip_batches (
2219 x_return_status => l_return_status,
2220 p_reverse_compl_def_txns => TRUE
2221 );
2222 l_pos := 5;
2223
2224 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2225 RAISE error_detail;
2226 END IF;
2227
2228 set_table_migrated (
2229 p_table_name => 'IC_TRAN_PND_WIP',
2230 x_return_status => l_return_status
2231 );
2232
2233 IF p_commit THEN
2234 COMMIT;
2235 END IF;
2236 END IF;
2237
2238 IF (is_table_migrated (p_table_name => 'PM_MATL_DTL') = FALSE) THEN
2239 l_pos := 6;
2240 insert_material_details (x_return_status => l_return_status);
2241 l_pos := 7;
2242
2243 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2244 RAISE error_detail;
2245 END IF;
2246
2247 set_table_migrated (
2248 p_table_name => 'PM_MATL_DTL',
2249 x_return_status => l_return_status
2250 );
2251
2252 IF p_commit THEN
2253 COMMIT;
2254 END IF;
2255 END IF;
2256
2257 IF (is_table_migrated (p_table_name => 'PM_ROUT_DTL') = FALSE) THEN
2258 l_pos := 8;
2259 insert_batch_steps (x_return_status => l_return_status);
2260 l_pos := 9;
2261
2262 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2263 RAISE error_detail;
2264 END IF;
2265
2266 set_table_migrated (
2267 p_table_name => 'PM_ROUT_DTL',
2268 x_return_status => l_return_status
2269 );
2270
2271 IF p_commit THEN
2272 COMMIT;
2273 END IF;
2274 END IF;
2275
2276 IF (is_table_migrated (p_table_name => 'PM_OPRN_DTL') = FALSE) THEN
2277 l_pos := 10;
2278 insert_batch_step_dtls (x_return_status => l_return_status);
2279 l_pos := 11;
2280
2281 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2282 RAISE error_detail;
2283 END IF;
2284
2285 set_table_migrated (
2286 p_table_name => 'PM_OPRN_DTL',
2287 x_return_status => l_return_status
2288 );
2289
2290 IF p_commit THEN
2291 COMMIT;
2292 END IF;
2293 END IF;
2294
2295 IF (is_table_migrated (p_table_name => 'PM_ROUT_MTL') = FALSE) THEN
2296 l_pos := 12;
2297 insert_batch_step_items (x_return_status => l_return_status);
2298 l_pos := 13;
2299
2300 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2301 RAISE error_detail;
2302 END IF;
2303
2304 set_table_migrated (
2305 p_table_name => 'PM_ROUT_MTL',
2306 x_return_status => l_return_status
2307 );
2308
2309 IF p_commit THEN
2310 COMMIT;
2311 END IF;
2312 END IF;
2313
2314 IF (is_table_migrated (p_table_name => 'PM_ROUT_DEP') = FALSE) THEN
2315 l_pos := 14;
2316 insert_batch_step_dependencies (x_return_status => l_return_status);
2317 l_pos := 15;
2318
2319 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2320 RAISE error_detail;
2321 END IF;
2322
2323 set_table_migrated (
2324 p_table_name => 'PM_ROUT_DEP',
2325 x_return_status => l_return_status
2326 );
2327
2328 IF p_commit THEN
2329 COMMIT;
2330 END IF;
2331 END IF;
2332
2333 IF (is_table_migrated (p_table_name => 'PM_HIST_HDR') = FALSE) THEN
2334 l_pos := 16;
2335 insert_batch_history (x_return_status => l_return_status);
2336 l_pos := 17;
2337
2338 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2339 RAISE error_detail;
2340 END IF;
2341
2342 set_table_migrated (
2343 p_table_name => 'PM_HIST_HDR',
2344 x_return_status => l_return_status
2345 );
2346
2347 IF p_commit THEN
2348 COMMIT;
2349 END IF;
2350 END IF;
2351
2352 IF (is_table_migrated (p_table_name => 'PM_OPRN_WIP') = FALSE) THEN
2353 l_pos := 18;
2354 insert_batch_step_transfers (x_return_status => l_return_status);
2355 l_pos := 19;
2356
2357 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2358 RAISE error_detail;
2359 END IF;
2360
2361 set_table_migrated (
2362 p_table_name => 'PM_OPRN_WIP',
2363 x_return_status => l_return_status
2364 );
2365
2366 IF p_commit THEN
2367 COMMIT;
2368 END IF;
2369 END IF;
2370
2371 IF (is_table_migrated (p_table_name => 'PM_TEXT_HDR_PC_TEXT_HDR') =
2372 FALSE
2373 ) THEN
2374 l_pos := 20;
2375 insert_text_header (x_return_status => l_return_status);
2376 l_pos := 21;
2377
2378 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2379 RAISE error_detail;
2380 END IF;
2381
2382 set_table_migrated (
2383 p_table_name => 'PM_TEXT_HDR_PC_TEXT_HDR',
2384 x_return_status => l_return_status
2385 );
2386
2387 IF p_commit THEN
2388 COMMIT;
2389 END IF;
2390 END IF;
2391
2392 IF (is_table_migrated (p_table_name => 'PM_TEXT_TBL_TL_PC_TEXT_TBL_TL') =
2393 FALSE
2394 ) THEN
2395 l_pos := 22;
2396 insert_text_dtl (x_return_status => l_return_status);
2397 l_pos := 23;
2398
2399 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2400 RAISE error_detail;
2401 END IF;
2402
2403 set_table_migrated (
2404 p_table_name => 'PM_TEXT_TBL_TL_PC_TEXT_TBL_TL',
2405 x_return_status => l_return_status
2406 );
2407
2408 IF p_commit THEN
2409 COMMIT;
2410 END IF;
2411 END IF;
2412
2413 IF (is_table_migrated (p_table_name => 'BLANK_LINE_NO') = FALSE) THEN
2414 l_pos := 25.1;
2415 renumber_blank_line_no;
2416 l_pos := 25.2;
2417
2418 set_table_migrated (
2419 p_table_name => 'BLANK_LINE_NO',
2420 x_return_status => l_return_status
2421 );
2422
2423 IF p_commit THEN
2424 COMMIT;
2425 END IF;
2426 END IF;
2427
2428 IF NOT p_commit THEN
2429 ROLLBACK;
2430
2431 insert_message (
2432 p_table_name => NULL,
2433 p_procedure_name => 'set_GME_validated',
2434 p_parameters => NULL,
2435 p_message => NULL,
2436 p_error_type => 'V' -- validation control
2437 );
2438 END IF;
2439
2440 insert_message (
2441 p_table_name => 'none',
2442 p_procedure_name => 'run',
2443 p_parameters => 'p_commit = '||l_commit_text,
2444 p_message => 'Procedure has ended',
2445 p_error_type => 'P'
2446 );
2447
2448 l_pos := 26;
2449 EXCEPTION
2450 WHEN GME_NO_DATA THEN
2451 IF p_commit THEN
2452 l_message := 'migration';
2453 ELSE
2454 l_message := 'validation';
2455 END IF;
2456
2457 insert_message (
2458 p_table_name => 'none',
2459 p_procedure_name => 'run',
2460 p_parameters => 'p_commit = ' || l_commit_text,
2461 p_message => 'GME '||l_message||' found no data.',
2462 p_error_type => 'I'
2463 );
2464 WHEN error_detail THEN
2465 ROLLBACK;
2466 RAISE;
2467 WHEN ERROR_NO_VALIDATION THEN
2468 ROLLBACK;
2469 insert_message (
2470 p_table_name => 'none',
2471 p_procedure_name => 'run',
2472 p_parameters => 'p_commit = ' || l_commit_text,
2473 p_message => 'Validation must be successfully run prior to running migration.',
2474 p_error_type => 'D'
2475 );
2476 RAISE;
2477 WHEN OTHERS THEN
2478 IF p_commit THEN
2479 l_message :=
2480 ' Note: migrate_batch.run was called with commit on so any commits prior to pos = '
2481 || l_pos
2482 || ' has occurred';
2483 END IF;
2484
2485 insert_message (
2486 p_table_name => 'none',
2487 p_procedure_name => 'run',
2488 p_parameters => 'p_commit = ' || l_commit_text,
2489 p_message => SQLERRM || ' with pos = ' || l_pos || l_message,
2490 p_error_type => 'D'
2491 );
2492 ROLLBACK;
2493 RAISE;
2494 END run;
2495
2496 PROCEDURE insert_batch_header (x_return_status OUT NOCOPY VARCHAR2) IS
2497 BEGIN
2498 x_return_status := FND_API.G_RET_STS_SUCCESS;
2499
2500 INSERT INTO gme_batch_header
2501 (batch_id,
2502 plant_code,
2503 batch_no,
2504 batch_type,
2505 prod_id,
2506 prod_sequence,
2507 recipe_validity_rule_id,
2508 formula_id,
2509 routing_id,
2510 plan_start_date,
2511 actual_start_date,
2512 due_date,
2513 plan_cmplt_date,
2514 actual_cmplt_date,
2515 batch_status,
2516 priority_value,
2517 priority_code,
2518 print_count,
2519 fmcontrol_class,
2520 wip_whse_code,
2521 batch_close_date,
2522 poc_ind,
2523 actual_cost_ind,
2524 gl_posted_ind,
2525 update_inventory_ind,
2526 automatic_step_calculation,
2527 created_by,
2528 creation_date,
2529 last_updated_by,
2530 last_update_date,
2531 last_update_login,
2532 delete_mark,
2533 text_code,
2534 parentline_id,
2535 fpo_id,
2536 migrated_batch_ind,
2537 attribute1,
2538 attribute2,
2539 attribute3,
2540 attribute4,
2541 attribute5,
2542 attribute6,
2543 attribute7,
2544 attribute8,
2545 attribute9,
2546 attribute10,
2547 attribute11,
2548 attribute12,
2549 attribute13,
2550 attribute14,
2551 attribute15,
2552 attribute16,
2553 attribute17,
2554 attribute18,
2555 attribute19,
2556 attribute20,
2557 attribute21,
2558 attribute22,
2559 attribute23,
2560 attribute24,
2561 attribute25,
2562 attribute26,
2563 attribute27,
2564 attribute28,
2565 attribute29,
2566 attribute30,
2567 attribute_category
2568 )
2569 SELECT batch_id,
2570 plant_code,
2571 batch_no,
2572 batch_type,
2573 prod_id,
2574 prod_sequence,
2575 fmeff_id,
2576 formula_id,
2577 routing_id,
2578 plan_start_date,
2579 get_actual_date (actual_start_date),
2580 due_date,
2581 expct_cmplt_date,
2582 get_actual_date (actual_cmplt_date),
2583 batch_status,
2584 priority_value,
2585 priority_code,
2586 print_count,
2587 fmcontrol_class,
2588 wip_whse_code,
2589 get_actual_date (batch_close_date),
2590 get_poc_data_ind (batch_id),
2591 get_actual_cost_ind (batch_id),
2592 get_gl_posted_ind (batch_id),
2593 'Y' --update_inventory_ind => lab batches introduced in 11I+
2594 ,
2595 0 --automatic_step_calculation
2596 ,
2597 created_by,
2598 creation_date,
2599 last_updated_by,
2600 last_update_date,
2601 last_update_login,
2602 delete_mark,
2603 text_code,
2604 parentline_id,
2605 NULL --fpo_id
2606 ,
2607 'Y' --migrated_batch_ind
2608 ,
2609 attribute1,
2610 attribute2,
2611 attribute3,
2612 attribute4,
2613 attribute5,
2614 attribute6,
2615 attribute7,
2616 attribute8,
2617 attribute9,
2618 attribute10,
2619 attribute11,
2620 attribute12,
2621 attribute13,
2622 attribute14,
2623 attribute15,
2624 attribute16,
2625 attribute17,
2626 attribute18,
2627 attribute19,
2628 attribute20,
2629 attribute21,
2630 attribute22,
2631 attribute23,
2632 attribute24,
2633 attribute25,
2634 attribute26,
2635 attribute27,
2636 attribute28,
2637 attribute29,
2638 attribute30,
2639 attribute_category
2640 FROM pm_btch_hdr_bak
2641 WHERE in_use < 100;
2642
2643 insert_message (
2644 p_table_name => 'gme_batch_header',
2645 p_procedure_name => 'insert_batch_header',
2646 p_parameters => 'none',
2647 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
2648 p_error_type => 'P'
2649 );
2650
2651 /* If we get here, that means that the above insert was successful;
2652 let's indicate that the base batches were migrated */
2653 UPDATE pm_btch_hdr_bak
2654 SET in_use = in_use + 100
2655 WHERE in_use < 100;
2656 EXCEPTION
2657 WHEN OTHERS THEN
2658 x_return_status := 'D';
2659 insert_message (
2660 p_table_name => 'pm_btch_hdr_bak/gme_batch_header',
2661 p_procedure_name => 'insert_batch_header',
2662 p_parameters => 'none',
2663 p_message => SQLERRM,
2664 p_error_type => x_return_status
2665 );
2666 END insert_batch_header;
2667
2668 PROCEDURE insert_material_details (x_return_status OUT NOCOPY VARCHAR2) IS
2669 l_count NUMBER (5) DEFAULT 0;
2670 l_return_status VARCHAR2 (1);
2671 error_detail EXCEPTION;
2672 BEGIN
2673 x_return_status := FND_API.G_RET_STS_SUCCESS;
2674
2675 INSERT INTO gme_material_details
2676 (material_detail_id,
2677 batch_id,
2678 formulaline_id,
2679 line_no,
2680 item_id,
2681 line_type,
2682 plan_qty,
2683 item_um,
2684 item_um2,
2685 actual_qty,
2686 original_qty,
2687 wip_plan_qty,
2688 release_type,
2689 scrap_factor,
2690 scale_type,
2691 contribute_yield_ind,
2692 scale_multiple,
2693 scale_rounding_variance,
2694 rounding_direction,
2695 contribute_step_qty_ind,
2696 phantom_type,
2697 cost_alloc,
2698 alloc_ind,
2699 cost,
2700 text_code,
2701 phantom_id,
2702 created_by,
2703 creation_date,
2704 last_updated_by,
2705 last_update_date,
2706 last_update_login,
2707 attribute1,
2708 attribute2,
2709 attribute3,
2710 attribute4,
2711 attribute5,
2712 attribute6,
2713 attribute7,
2714 attribute8,
2715 attribute9,
2716 attribute10,
2717 attribute11,
2718 attribute12,
2719 attribute13,
2720 attribute14,
2721 attribute15,
2722 attribute16,
2723 attribute17,
2724 attribute18,
2725 attribute19,
2726 attribute20,
2727 attribute21,
2728 attribute22,
2729 attribute23,
2730 attribute24,
2731 attribute25,
2732 attribute26,
2733 attribute27,
2734 attribute28,
2735 attribute29,
2736 attribute30,
2737 attribute_category
2738 )
2739 SELECT line_id,
2740 batch_id,
2741 formulaline_id,
2742 line_no,
2743 item_id,
2744 line_type,
2745 get_planned_qty (batch_id, line_id, plan_qty),
2746 item_um,
2747 item_um2,
2748 get_actual_qty (batch_id, line_id, actual_qty),
2749 plan_qty --original_qty
2750 ,
2751 get_wip_planned_qty (batch_id, line_id, actual_qty),
2752 release_type,
2753 scrap_factor,
2754 DECODE (scale_type, 0, 0, 1, 1, 2, 0, 3, 1, scale_type),
2755 DECODE (scale_type, 2, 'N', 'Y') --contribute_yield_ind
2756 ,
2757 NULL --scale_multiple
2758 ,
2759 NULL --scale_rounding_variance
2760 ,
2761 NULL --rounding_direction
2762 ,
2763 'Y' --contribute_step_qty_ind
2764 ,
2765 phantom_type,
2766 cost_alloc,
2767 alloc_ind,
2768 cost,
2769 text_code,
2770 phantom_id,
2771 created_by,
2772 creation_date,
2773 last_updated_by,
2774 last_update_date,
2775 last_update_login,
2776 attribute1,
2777 attribute2,
2778 attribute3,
2779 attribute4,
2780 attribute5,
2781 attribute6,
2782 attribute7,
2783 attribute8,
2784 attribute9,
2785 attribute10,
2786 attribute11,
2787 attribute12,
2788 attribute13,
2789 attribute14,
2790 attribute15,
2791 attribute16,
2792 attribute17,
2793 attribute18,
2794 attribute19,
2795 attribute20,
2796 attribute21,
2797 attribute22,
2798 attribute23,
2799 attribute24,
2800 attribute25,
2801 attribute26,
2802 attribute27,
2803 attribute28,
2804 attribute29,
2805 attribute30,
2806 attribute_category
2807 FROM pm_matl_dtl_bak
2808 WHERE in_use < 100;
2809
2810 insert_message (
2811 p_table_name => 'gme_material_details',
2812 p_procedure_name => 'insert_material_details',
2813 p_parameters => 'none',
2814 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
2815 p_error_type => 'P'
2816 );
2817 split_trans_line (x_return_status => l_return_status);
2818
2819 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2820 RAISE error_detail;
2821 END IF;
2822
2823 UPDATE pm_matl_dtl_bak
2824 SET in_use = in_use + 100
2825 WHERE in_use < 100;
2826 EXCEPTION
2827 WHEN error_detail THEN
2828 x_return_status := l_return_status;
2829 WHEN OTHERS THEN
2830 x_return_status := 'D';
2831 insert_message (
2832 p_table_name => 'GME_MATERIAL_DETAILS',
2833 p_procedure_name => 'INSERT_MATERIAL_DETAILS',
2834 p_parameters => '',
2835 p_message => SQLERRM,
2836 p_error_type => x_return_status
2837 );
2838 END insert_material_details;
2839
2840 PROCEDURE insert_batch_steps (x_return_status OUT NOCOPY VARCHAR2) IS
2841 v_step_rec pm_rout_dtl%ROWTYPE;
2842 v_mass_ref_uom VARCHAR2 (4);
2843 v_volume_ref_uom VARCHAR2 (4);
2844 v_mass_qty NUMBER;
2845 v_volume_qty NUMBER;
2846 v_max_step_capacity NUMBER;
2847 v_step_capacity_uom VARCHAR2 (4);
2848 v_planned_charges NUMBER;
2849 v_actual_charges NUMBER;
2850 v_process_qty_uom VARCHAR2 (4);
2851 l_return_status VARCHAR2 (1);
2852 BEGIN
2853 x_return_status := FND_API.G_RET_STS_SUCCESS;
2854
2855 INSERT INTO gme_batch_steps
2856 (batch_id,
2857 batchstep_id,
2858 routingstep_id,
2859 batchstep_no,
2860 oprn_id,
2861 plan_step_qty,
2862 actual_step_qty,
2863 step_qty_uom,
2864 backflush_qty,
2865 plan_start_date,
2866 actual_start_date,
2867 due_date,
2868 plan_cmplt_date,
2869 actual_cmplt_date,
2870 step_close_date,
2871 step_status,
2872 priority_code,
2873 priority_value,
2874 steprelease_type,
2875 max_step_capacity,
2876 max_step_capacity_uom,
2877 plan_charges,
2878 actual_charges,
2879 text_code,
2880 delete_mark,
2881 created_by,
2882 creation_date,
2883 last_updated_by,
2884 last_update_date,
2885 last_update_login,
2886 attribute1,
2887 attribute2,
2888 attribute3,
2889 attribute4,
2890 attribute5,
2891 attribute6,
2892 attribute7,
2893 attribute8,
2894 attribute9,
2895 attribute10,
2896 attribute11,
2897 attribute12,
2898 attribute13,
2899 attribute14,
2900 attribute15,
2901 attribute16,
2902 attribute17,
2903 attribute18,
2904 attribute19,
2905 attribute20,
2906 attribute21,
2907 attribute22,
2908 attribute23,
2909 attribute24,
2910 attribute25,
2911 attribute26,
2912 attribute27,
2913 attribute28,
2914 attribute29,
2915 attribute30,
2916 attribute_category,
2917 mass_ref_uom,
2918 volume_ref_uom,
2919 plan_volume_qty,
2920 plan_mass_qty,
2921 actual_volume_qty,
2922 actual_mass_qty
2923 )
2924 SELECT batch_id,
2925 gme_batch_step_s.NEXTVAL --batchstep_id
2926 ,
2927 routingstep_id,
2928 batchstep_no,
2929 oprn_id,
2930 plan_step_qty,
2931 actual_step_qty,
2932 get_process_qty_uom (oprn_id) --step_qty_uom
2933 ,
2934 backflush_qty,
2935 plan_start_date,
2936 get_actual_date (actual_start_date),
2937 due_date,
2938 expct_cmplt_date,
2939 get_actual_date (actual_cmplt_date),
2940 get_actual_date (step_close_date),
2941 step_status,
2942 priority_code,
2943 priority_value,
2944 1 --steprelease_type
2945 ,
2946 NULL --max_step_capacity
2947 ,
2948 NULL --max_step_capacity_uom
2949 ,
2950 NULL --plan_charges
2951 ,
2952 NULL --actual_charges
2953 ,
2954 text_code,
2955 delete_mark,
2956 created_by,
2957 creation_date,
2958 last_updated_by,
2959 last_update_date,
2960 last_update_login,
2961 attribute1,
2962 attribute2,
2963 attribute3,
2964 attribute4,
2965 attribute5,
2966 attribute6,
2967 attribute7,
2968 attribute8,
2969 attribute9,
2970 attribute10,
2971 attribute11,
2972 attribute12,
2973 attribute13,
2974 attribute14,
2975 attribute15,
2976 attribute16,
2977 attribute17,
2978 attribute18,
2979 attribute19,
2980 attribute20,
2981 attribute21,
2982 attribute22,
2983 attribute23,
2984 attribute24,
2985 attribute25,
2986 attribute26,
2987 attribute27,
2988 attribute28,
2989 attribute29,
2990 attribute30,
2991 attribute_category,
2992 get_ref_uom (
2993 fnd_profile.VALUE ('LM$UOM_MASS_TYPE')
2994 ) --mass_ref_uom
2995 ,
2996 get_ref_uom (
2997 fnd_profile.VALUE ('LM$UOM_VOLUME_TYPE')
2998 ) --volume_ref_uom
2999 ,
3000 NULL --plan_volume_qty
3001 ,
3002 NULL --plan_mass_qty
3003 ,
3004 NULL --actual_volume_qty
3005 ,
3006 NULL --actual_mass_qty
3007 FROM pm_rout_dtl
3008 WHERE in_use < 100 AND
3009 delete_mark <> 1;
3010
3011 insert_message (
3012 p_table_name => 'gme_batch_steps',
3013 p_procedure_name => 'insert_batch_steps',
3014 p_parameters => 'none',
3015 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3016 p_error_type => 'P'
3017 );
3018
3019 UPDATE pm_rout_dtl
3020 SET in_use = in_use + 100
3021 WHERE in_use < 100 AND
3022 delete_mark <> 1;
3023 EXCEPTION
3024 WHEN OTHERS THEN
3025 x_return_status := 'D';
3026 insert_message (
3027 p_table_name => 'GME_BATCH_STEPS',
3028 p_procedure_name => 'INSERT_BATCH_STEPS',
3029 p_parameters => '',
3030 p_message => SQLERRM,
3031 p_error_type => x_return_status
3032 );
3033 END insert_batch_steps;
3034
3035 PROCEDURE insert_batch_step_dtls (x_return_status OUT NOCOPY VARCHAR2) IS
3036 CURSOR get_activities IS
3037 SELECT batch_id,
3038 batchstep_no,
3039 activity,
3040 MIN (offset_interval) min_offset,
3041 MIN (plan_start_date) plan_start_date,
3042 MAX (plan_cmplt_date) plan_cmplt_date,
3043 MIN (actual_start_date) actual_start_date,
3044 MAX (actual_cmplt_date) actual_cmplt_date
3045 FROM pm_oprn_dtl
3046 WHERE delete_mark < 100 AND
3047 delete_mark <> 1
3048 GROUP BY batch_id, batchstep_no, activity;
3049
3050 CURSOR get_oprn_dtl (
3051 v_batch_id pm_oprn_dtl.batch_id%TYPE,
3052 v_step_no pm_oprn_dtl.batchstep_no%TYPE,
3053 v_activity pm_oprn_dtl.activity%TYPE,
3054 v_offset pm_oprn_dtl.offset_interval%TYPE
3055 ) IS
3056 SELECT *
3057 FROM pm_oprn_dtl
3058 WHERE batch_id = v_batch_id AND
3059 batchstep_no = v_step_no AND
3060 activity = v_activity AND
3061 offset_interval = v_offset AND
3062 delete_mark < 100 AND
3063 delete_mark <> 1;
3064
3065 v_activities get_activities%ROWTYPE;
3066 v_oprn_dtl get_oprn_dtl%ROWTYPE;
3067 l_return_status VARCHAR2 (1);
3068 l_act_cnt NUMBER := 0;
3069 error_insert_rsrc_txns EXCEPTION;
3070 BEGIN
3071 x_return_status := FND_API.G_RET_STS_SUCCESS;
3072 OPEN get_activities;
3073 FETCH get_activities INTO v_activities;
3074
3075 WHILE (get_activities%FOUND)
3076 LOOP
3077 OPEN get_oprn_dtl (
3078 v_activities.batch_id,
3079 v_activities.batchstep_no,
3080 v_activities.activity,
3081 v_activities.min_offset
3082 );
3083 -- Take the first one that comes back if there are more than 1 resulting from this CURSOR.
3084 FETCH get_oprn_dtl INTO v_oprn_dtl;
3085
3086 INSERT INTO gme_batch_step_activities
3087 (batch_id,
3088 activity,
3089 batchstep_id,
3090 batchstep_activity_id,
3091 oprn_line_id,
3092 offset_interval,
3093 plan_start_date,
3094 actual_start_date,
3095 plan_cmplt_date,
3096 actual_cmplt_date,
3097 plan_activity_factor,
3098 actual_activity_factor,
3099 delete_mark,
3100 created_by,
3101 creation_date,
3102 last_updated_by,
3103 last_update_date,
3104 last_update_login
3105 )
3106 SELECT v_oprn_dtl.batch_id,
3107 v_oprn_dtl.activity,
3108 get_batchstep_id (
3109 v_oprn_dtl.batch_id,
3110 v_oprn_dtl.batchstep_no
3111 ) --batchstep_id
3112 ,
3113 gme_batch_step_activity_s.NEXTVAL --batchstep_activity_id
3114 ,
3115 v_oprn_dtl.oprn_line_id,
3116 v_oprn_dtl.offset_interval,
3117 v_activities.plan_start_date,
3118 get_actual_date (v_activities.actual_start_date),
3119 v_activities.plan_cmplt_date,
3120 get_actual_date (v_activities.actual_cmplt_date),
3121 1 --plan_activity_factor
3122 ,
3123 get_actual_activity_factor (
3124 get_batchstep_id (
3125 v_oprn_dtl.batch_id,
3126 v_oprn_dtl.batchstep_no
3127 )
3128 ) --actual_activity_factor
3129 ,
3130 v_oprn_dtl.delete_mark,
3131 v_oprn_dtl.created_by,
3132 v_oprn_dtl.creation_date,
3133 v_oprn_dtl.last_updated_by,
3134 v_oprn_dtl.last_update_date,
3135 v_oprn_dtl.last_update_login
3136 FROM sys.DUAL;
3137
3138 CLOSE get_oprn_dtl;
3139 l_act_cnt := l_act_cnt + 1;
3140 FETCH get_activities INTO v_activities;
3141 END LOOP;
3142
3143 CLOSE get_activities;
3144 insert_message (
3145 p_table_name => 'gme_batch_step_activities',
3146 p_procedure_name => 'insert_batch_step_dtls',
3147 p_parameters => 'none',
3148 p_message => 'number of records inserted = ' || l_act_cnt,
3149 p_error_type => 'P'
3150 );
3151
3152 INSERT INTO gme_batch_step_resources
3153 (batchstep_resource_id,
3154 batchstep_activity_id,
3155 resources,
3156 cost_analysis_code,
3157 cost_cmpntcls_id,
3158 prim_rsrc_ind,
3159 scale_type,
3160 plan_rsrc_count,
3161 actual_rsrc_count,
3162 resource_qty_uom,
3163 plan_rsrc_usage,
3164 actual_rsrc_usage,
3165 usage_uom,
3166 plan_start_date,
3167 actual_start_date,
3168 plan_cmplt_date,
3169 actual_cmplt_date,
3170 offset_interval,
3171 min_capacity,
3172 max_capacity,
3173 process_parameter_1,
3174 process_parameter_2,
3175 process_parameter_3,
3176 process_parameter_4,
3177 process_parameter_5,
3178 attribute_category,
3179 attribute1,
3180 attribute2,
3181 attribute3,
3182 attribute4,
3183 attribute5,
3184 attribute6,
3185 attribute7,
3186 attribute8,
3187 attribute9,
3188 attribute10,
3189 attribute11,
3190 attribute12,
3191 attribute13,
3192 attribute14,
3193 attribute15,
3194 attribute16,
3195 attribute17,
3196 attribute18,
3197 attribute19,
3198 attribute20,
3199 attribute21,
3200 attribute22,
3201 attribute23,
3202 attribute24,
3203 attribute25,
3204 attribute26,
3205 attribute27,
3206 attribute28,
3207 attribute29,
3208 attribute30,
3209 created_by,
3210 creation_date,
3211 last_updated_by,
3212 last_update_date,
3213 last_update_login,
3214 text_code,
3215 batch_id,
3216 batchstep_id,
3217 capacity_uom,
3218 actual_rsrc_qty,
3219 plan_rsrc_qty,
3220 calculate_charges
3221 )
3222 SELECT batchstepline_id --batchstep_resource_id
3223 /* batchstepline_id is used as the line_id for the pc_tran_pnd table */
3224 ,
3225 get_activity_id (
3226 batch_id,
3227 batchstep_no,
3228 activity
3229 ) --batchstep_activity_id
3230 ,
3231 resources,
3232 cost_analysis_code,
3233 cost_cmpntcls_id,
3234 prim_rsrc_ind,
3235 scale_type,
3236 plan_rsrc_count,
3237 actual_rsrc_count,
3238 get_process_qty_uom (
3239 get_oprn_id (batch_id, batchstep_no)
3240 ) --resource_qty_uom
3241 ,
3242 get_planned_usage (batchstepline_id) --plan_rsrc_usage
3243 ,
3244 get_actual_usage (batchstepline_id) --actual_rsrc_usage
3245 ,
3246 usage_um,
3247 plan_start_date,
3248 get_actual_date (actual_start_date),
3249 plan_cmplt_date,
3250 get_actual_date (actual_cmplt_date),
3251 get_rsrc_offset (
3252 batch_id,
3253 batchstep_no,
3254 activity,
3255 offset_interval
3256 ) --offset_interval
3257 ,
3258 get_min_capacity (batch_id, resources) --min_capacity
3259 ,
3260 get_max_capacity (batch_id, resources) --max_capacity
3261 ,
3262 NULL --process_parameter_1
3263 ,
3264 NULL --process_parameter_2
3265 ,
3266 NULL --process_parameter_3
3267 ,
3268 NULL --process_parameter_4
3269 ,
3270 NULL --process_parameter_5
3271 ,
3272 attribute_category,
3273 attribute1,
3274 attribute2,
3275 attribute3,
3276 attribute4,
3277 attribute5,
3278 attribute6,
3279 attribute7,
3280 attribute8,
3281 attribute9,
3282 attribute10,
3283 attribute11,
3284 attribute12,
3285 attribute13,
3286 attribute14,
3287 attribute15,
3288 attribute16,
3289 attribute17,
3290 attribute18,
3291 attribute19,
3292 attribute20,
3293 attribute21,
3294 attribute22,
3295 attribute23,
3296 attribute24,
3297 attribute25,
3298 attribute26,
3299 attribute27,
3300 attribute28,
3301 attribute29,
3302 attribute30,
3303 created_by,
3304 creation_date,
3305 last_updated_by,
3306 last_update_date,
3307 last_update_login,
3308 text_code,
3309 batch_id,
3310 get_batchstep_id (batch_id, batchstep_no) --batchstep_id
3311 ,
3312 get_capacity_uom (batch_id, resources) --capacity_uom
3313 ,
3314 actual_rsrc_qty,
3315 plan_rsrc_qty,
3316 0 --calculate_charges
3317 FROM pm_oprn_dtl
3318 WHERE delete_mark < 100 AND
3319 delete_mark <> 1;
3320
3321 insert_message (
3322 p_table_name => 'gme_batch_step_resources',
3323 p_procedure_name => 'insert_batch_step_dtls',
3324 p_parameters => 'none',
3325 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3326 p_error_type => 'P'
3327 );
3328
3329 UPDATE pm_oprn_dtl
3330 SET delete_mark = delete_mark + 100
3331 WHERE delete_mark < 100 AND
3332 delete_mark <> 1;
3333
3334 insert_resource_txns (l_return_status);
3335
3336 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3337 RAISE error_insert_rsrc_txns;
3338 END IF;
3339
3340 EXCEPTION
3341 WHEN error_insert_rsrc_txns THEN
3342 x_return_status := l_return_status;
3343 WHEN OTHERS THEN
3344 x_return_status := 'D';
3345 insert_message (
3346 p_table_name => 'PM_OPRN_DTL',
3347 p_procedure_name => 'INSERT_BATCH_STEP_DTLS',
3348 p_parameters => '',
3349 p_message => SQLERRM,
3350 p_error_type => x_return_status
3351 );
3352 END insert_batch_step_dtls;
3353
3354 PROCEDURE insert_batch_step_items (x_return_status OUT NOCOPY VARCHAR2) IS
3355 l_return_status VARCHAR2 (1);
3356 BEGIN
3357 x_return_status := FND_API.G_RET_STS_SUCCESS;
3358
3359 report_step_item_orphans;
3360
3361 INSERT INTO gme_batch_step_items
3362 (material_detail_id,
3363 batch_id,
3364 batchstep_id,
3365 text_code,
3366 created_by,
3367 creation_date,
3368 last_updated_by,
3369 last_update_date,
3370 last_update_login
3371 )
3372 SELECT batchline_id,
3373 MIN (batch_id),
3374 get_batchstep_id (
3375 MIN (batch_id),
3376 MIN (batchstep_no)
3377 ) --batchstep_id
3378 ,
3379 MIN (text_code),
3380 MIN (created_by),
3381 MIN (creation_date),
3382 MIN (last_updated_by),
3383 MIN (last_update_date),
3384 MIN (last_update_login)
3385 FROM pm_rout_mtl pm
3386 WHERE NOT EXISTS ( SELECT 1
3387 FROM gme_batch_step_items
3388 WHERE material_detail_id = pm.batchline_id)
3389 AND
3390 EXISTS ( SELECT 1
3391 FROM gme_batch_steps step
3392 WHERE step.batch_id = pm.batch_id AND
3393 step.batchstep_no = pm.batchstep_no)
3394 AND
3395 EXISTS ( SELECT 1
3396 FROM gme_material_details matl
3397 WHERE matl.batch_id = pm.batch_id AND
3398 matl.material_detail_id = pm.batchline_id)
3399 GROUP BY batchline_id;
3400
3401 insert_message (
3402 p_table_name => 'gme_batch_step_items',
3403 p_procedure_name => 'insert_batch_step_items',
3404 p_parameters => 'none',
3405 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3406 p_error_type => 'P'
3407 );
3408 EXCEPTION
3409 WHEN OTHERS THEN
3410 x_return_status := 'D';
3411 insert_message (
3412 p_table_name => 'GME_BATCH_STEP_ITEMS',
3413 p_procedure_name => 'INSERT_BATCH_STEP_ITEMS',
3414 p_parameters => '',
3415 p_message => SQLERRM,
3416 p_error_type => x_return_status
3417 );
3418 END insert_batch_step_items;
3419
3420 PROCEDURE report_step_item_orphans IS
3421
3422 CURSOR cur_get_orphans IS
3423 SELECT batch_id, batchstep_no, batchline_id
3424 FROM pm_rout_mtl pm
3425 WHERE NOT EXISTS ( SELECT 1
3426 FROM gme_batch_step_items
3427 WHERE material_detail_id = pm.batchline_id)
3428 AND
3429 (
3430 NOT EXISTS ( SELECT 1
3431 FROM gme_batch_steps step
3432 WHERE step.batch_id = pm.batch_id AND
3433 step.batchstep_no = pm.batchstep_no) OR
3434 NOT EXISTS ( SELECT 1
3435 FROM gme_material_details matl
3436 WHERE matl.batch_id = pm.batch_id AND
3437 matl.material_detail_id = pm.batchline_id)
3438 );
3439
3440 l_cur_get_orphans cur_get_orphans%ROWTYPE;
3441
3442 BEGIN
3443
3444 OPEN cur_get_orphans;
3445 FETCH cur_get_orphans INTO l_cur_get_orphans;
3446 WHILE cur_get_orphans%FOUND LOOP
3447 insert_message (
3448 p_table_name => 'gme_batch_step_items',
3449 p_procedure_name => 'report_step_item_orphans',
3450 p_parameters => 'Batch_id=>'||to_char(l_cur_get_orphans.batch_id)
3451 ||' step_no=>'||l_cur_get_orphans.batchstep_no
3452 ||' material_detail_id=>'||to_char(l_cur_get_orphans.batchline_id),
3453 p_message => 'Step or material does not exist in parent table; item / step association will not be migrated',
3454 p_error_type => 'I'
3455 );
3456
3457 FETCH cur_get_orphans INTO l_cur_get_orphans;
3458 END LOOP;
3459 CLOSE cur_get_orphans;
3460
3461 EXCEPTION
3462 WHEN OTHERS THEN
3463 insert_message (
3464 p_table_name => 'GME_BATCH_STEP_ITEMS',
3465 p_procedure_name => 'REPORT_STEP_ITEM_ORPHANS',
3466 p_parameters => '',
3467 p_message => SQLERRM,
3468 p_error_type => 'D'
3469 );
3470 END report_step_item_orphans;
3471
3472 PROCEDURE insert_batch_step_dependencies (x_return_status OUT NOCOPY VARCHAR2) IS
3473 BEGIN
3474 x_return_status := FND_API.G_RET_STS_SUCCESS;
3475
3476 report_step_dep_orphans;
3477
3478 INSERT INTO gme_batch_step_dependencies
3479 (batch_id,
3480 batchstep_id,
3481 dep_type,
3482 dep_step_id,
3483 rework_code,
3484 standard_delay,
3485 min_delay,
3486 max_delay,
3487 transfer_qty,
3488 transfer_um,
3489 transfer_percent,
3490 text_code,
3491 created_by,
3492 creation_date,
3493 last_updated_by,
3494 last_update_date,
3495 last_update_login,
3496 attribute1,
3497 attribute2,
3498 attribute3,
3499 attribute4,
3500 attribute5,
3501 attribute6,
3502 attribute7,
3503 attribute8,
3504 attribute9,
3505 attribute10,
3506 attribute11,
3507 attribute12,
3508 attribute13,
3509 attribute14,
3510 attribute15,
3511 attribute16,
3512 attribute17,
3513 attribute18,
3514 attribute19,
3515 attribute20,
3516 attribute21,
3517 attribute22,
3518 attribute23,
3519 attribute24,
3520 attribute25,
3521 attribute26,
3522 attribute27,
3523 attribute28,
3524 attribute29,
3525 attribute30,
3526 attribute_category
3527 )
3528 SELECT batch_id,
3529 get_batchstep_id (batch_id, batchstep_no) --batchstep_id
3530 ,
3531 dep_type,
3532 get_batchstep_id (batch_id, dep_step_no) --dep_step_id
3533 ,
3534 rework_code,
3535 standard_delay,
3536 min_delay,
3537 max_delay,
3538 transfer_qty,
3539 transfer_um,
3540 100 --transfer_percent
3541 ,
3542 text_code,
3543 created_by,
3544 creation_date,
3545 last_updated_by,
3546 last_update_date,
3547 last_update_login,
3548 NULL --attribute1
3549 ,
3550 NULL --attribute2
3551 ,
3552 NULL --attribute3
3553 ,
3554 NULL --attribute4
3555 ,
3556 NULL --attribute5
3557 ,
3558 NULL --attribute6
3559 ,
3560 NULL --attribute7
3561 ,
3562 NULL --attribute8
3563 ,
3564 NULL --attribute9
3565 ,
3566 NULL --attribute10
3567 ,
3568 NULL --attribute11
3569 ,
3570 NULL --attribute12
3571 ,
3572 NULL --attribute13
3573 ,
3574 NULL --attribute14
3575 ,
3576 NULL --attribute15
3577 ,
3578 NULL --attribute16
3579 ,
3580 NULL --attribute17
3581 ,
3582 NULL --attribute18
3583 ,
3584 NULL --attribute19
3585 ,
3586 NULL --attribute20
3587 ,
3588 NULL --attribute21
3589 ,
3590 NULL --attribute22
3591 ,
3592 NULL --attribute23
3593 ,
3594 NULL --attribute24
3595 ,
3596 NULL --attribute25
3597 ,
3598 NULL --attribute26
3599 ,
3600 NULL --attribute27
3601 ,
3602 NULL --attribute28
3603 ,
3604 NULL --attribute29
3605 ,
3606 NULL --attribute30
3607 ,
3608 NULL --attribute_category
3609 FROM pm_rout_dep dep
3610 WHERE dep_type <
3611 100 -- Only bring over dependencies for which both steps are still defined...
3612 -- If there is a record in dep table and not the 2 corresponding rows in step table, that means
3613 -- the record was marked for delete in the steps table, and, the old code
3614 -- did not delete the dependency.
3615 AND
3616 EXISTS ( SELECT 1
3617 FROM gme_batch_steps step
3618 WHERE step.batch_id = dep.batch_id AND
3619 step.batchstep_no = dep.batchstep_no) AND
3620 EXISTS ( SELECT 1
3621 FROM gme_batch_steps step
3622 WHERE step.batch_id = dep.batch_id AND
3623 step.batchstep_no = dep.dep_step_no);
3624
3625 insert_message (
3626 p_table_name => 'gme_batch_step_dependencies',
3627 p_procedure_name => 'insert_batch_step_dependencies',
3628 p_parameters => 'none',
3629 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3630 p_error_type => 'P'
3631 );
3632
3633 UPDATE pm_rout_dep
3634 SET dep_type = dep_type + 100
3635 WHERE dep_type < 100;
3636 EXCEPTION
3637 WHEN OTHERS THEN
3638 x_return_status := 'D';
3639 insert_message (
3640 p_table_name => 'GME_BATCH_STEP_DEPENDENCIES',
3641 p_procedure_name => 'INSERT_BATCH_STEP_DEPENDENCIES',
3642 p_parameters => '',
3643 p_message => SQLERRM,
3644 p_error_type => x_return_status
3645 );
3646 END insert_batch_step_dependencies;
3647
3648 PROCEDURE report_step_dep_orphans IS
3649
3650 CURSOR cur_get_orphans IS
3651 SELECT batch_id, batchstep_no, dep_step_no
3652 FROM pm_rout_dep dep
3653 WHERE dep_type < 100 AND
3654 (NOT EXISTS ( SELECT 1
3655 FROM gme_batch_steps step
3656 WHERE step.batch_id = dep.batch_id AND
3657 step.batchstep_no = dep.batchstep_no) OR
3658 NOT EXISTS ( SELECT 1
3659 FROM gme_batch_steps step
3660 WHERE step.batch_id = dep.batch_id AND
3661 step.batchstep_no = dep.dep_step_no));
3662
3663
3664 l_cur_get_orphans cur_get_orphans%ROWTYPE;
3665
3666 BEGIN
3667
3668 OPEN cur_get_orphans;
3669 FETCH cur_get_orphans INTO l_cur_get_orphans;
3670 WHILE cur_get_orphans%FOUND LOOP
3671 insert_message (
3672 p_table_name => 'gme_batch_step_dependencies',
3673 p_procedure_name => 'report_step_dep_orphans',
3674 p_parameters => 'Batch_id=>'||to_char(l_cur_get_orphans.batch_id)
3675 ||' step_no=>'||l_cur_get_orphans.batchstep_no
3676 ||' dep_step_no=>'||l_cur_get_orphans.dep_step_no,
3677 p_message => 'Step or dependent step does not exist in steps table; dependency will not be migrated',
3678 p_error_type => 'I'
3679 );
3680
3681 FETCH cur_get_orphans INTO l_cur_get_orphans;
3682 END LOOP;
3683 CLOSE cur_get_orphans;
3684
3685 EXCEPTION
3686 WHEN OTHERS THEN
3687 insert_message (
3688 p_table_name => 'GME_BATCH_STEP_DEPENDENCIES',
3689 p_procedure_name => 'REPORT_STEP_DEP_ORPHANS',
3690 p_parameters => '',
3691 p_message => SQLERRM,
3692 p_error_type => 'D'
3693 );
3694 END report_step_dep_orphans;
3695
3696 PROCEDURE insert_resource_txns (x_return_status OUT NOCOPY VARCHAR2) IS
3697 BEGIN
3698 x_return_status := FND_API.G_RET_STS_SUCCESS;
3699
3700 INSERT INTO gme_resource_txns
3701 (poc_trans_id,
3702 orgn_code,
3703 doc_type,
3704 doc_id,
3705 line_type,
3706 line_id,
3707 resources,
3708 resource_usage,
3709 trans_um,
3710 trans_date,
3711 completed_ind,
3712 event_id,
3713 posted_ind,
3714 overrided_protected_ind,
3715 reason_code,
3716 start_date,
3717 end_date,
3718 created_by,
3719 creation_date,
3720 last_updated_by,
3721 last_update_date,
3722 last_update_login,
3723 delete_mark,
3724 text_code,
3725 attribute1,
3726 attribute2,
3727 attribute3,
3728 attribute4,
3729 attribute5,
3730 attribute6,
3731 attribute7,
3732 attribute8,
3733 attribute9,
3734 attribute10,
3735 attribute11,
3736 attribute12,
3737 attribute13,
3738 attribute14,
3739 attribute15,
3740 attribute16,
3741 attribute17,
3742 attribute18,
3743 attribute19,
3744 attribute20,
3745 attribute21,
3746 attribute22,
3747 attribute23,
3748 attribute24,
3749 attribute25,
3750 attribute26,
3751 attribute27,
3752 attribute28,
3753 attribute29,
3754 attribute30,
3755 attribute_category,
3756 program_id,
3757 program_application_id,
3758 request_id,
3759 program_update_date
3760 )
3761 SELECT poc_trans_id,
3762 orgn_code,
3763 doc_type,
3764 doc_id,
3765 line_type,
3766 line_id,
3767 resources,
3768 resource_usage,
3769 trans_um,
3770 trans_date,
3771 completed_ind,
3772 event_id,
3773 posted_ind,
3774 'N' --overrided_protected_ind
3775 ,
3776 reason_code,
3777 start_date,
3778 end_date,
3779 created_by,
3780 creation_date,
3781 last_updated_by,
3782 last_update_date,
3783 last_update_login,
3784 delete_mark,
3785 text_code,
3786 attribute1,
3787 attribute2,
3788 attribute3,
3789 attribute4,
3790 attribute5,
3791 attribute6,
3792 attribute7,
3793 attribute8,
3794 attribute9,
3795 attribute10,
3796 attribute11,
3797 attribute12,
3798 attribute13,
3799 attribute14,
3800 attribute15,
3801 attribute16,
3802 attribute17,
3803 attribute18,
3804 attribute19,
3805 attribute20,
3806 attribute21,
3807 attribute22,
3808 attribute23,
3809 attribute24,
3810 attribute25,
3811 attribute26,
3812 attribute27,
3813 attribute28,
3814 attribute29,
3815 attribute30,
3816 attribute_category,
3817 program_id,
3818 program_application_id,
3819 request_id,
3820 program_update_date
3821 FROM pc_tran_pnd
3822 WHERE delete_mark < 100 AND
3823 delete_mark <> 1 AND
3824 (completed_ind = 1 OR
3825 (completed_ind = 0 AND
3826 resource_usage <> 0
3827 )
3828 ); --don't migrate 0 pending
3829
3830 insert_message (
3831 p_table_name => 'gme_resource_txns',
3832 p_procedure_name => 'insert_resource_txns',
3833 p_parameters => 'none',
3834 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3835 p_error_type => 'P'
3836 );
3837
3838 UPDATE pc_tran_pnd
3839 SET delete_mark = delete_mark + 100
3840 WHERE delete_mark < 100 AND
3841 delete_mark <> 1;
3842 EXCEPTION
3843 WHEN OTHERS THEN
3844 x_return_status := 'D';
3845 insert_message (
3846 p_table_name => 'GME_RESOURCE_TXNS',
3847 p_procedure_name => 'INSERT_RESOURCE_TXNS',
3848 p_parameters => '',
3849 p_message => SQLERRM,
3850 p_error_type => x_return_status
3851 );
3852 END insert_resource_txns;
3853
3854 PROCEDURE insert_batch_history (x_return_status OUT NOCOPY VARCHAR2) IS
3855 BEGIN
3856 x_return_status := FND_API.G_RET_STS_SUCCESS;
3857
3858 INSERT INTO gme_batch_history
3859 (event_id,
3860 batch_id,
3861 orig_status,
3862 new_status,
3863 orig_wip_whse,
3864 new_wip_whse,
3865 gl_posted_ind,
3866 created_by,
3867 creation_date,
3868 last_updated_by,
3869 last_update_date,
3870 last_update_login,
3871 program_application_id,
3872 program_id,
3873 request_id,
3874 program_update_date
3875 )
3876 SELECT event_id,
3877 batch_id,
3878 orig_status,
3879 new_status,
3880 orig_wip_whse,
3881 new_wip_whse,
3882 gl_posted_ind,
3883 created_by,
3884 creation_date,
3885 last_updated_by,
3886 last_update_date,
3887 last_update_login,
3888 program_application_id,
3889 program_id,
3890 request_id,
3891 program_update_date
3892 FROM pm_hist_hdr
3893 WHERE orig_status < 100;
3894
3895 insert_message (
3896 p_table_name => 'gme_batch_history',
3897 p_procedure_name => 'insert_batch_history',
3898 p_parameters => 'none',
3899 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
3900 p_error_type => 'P'
3901 );
3902
3903 UPDATE pm_hist_hdr
3904 SET orig_status = orig_status + 100
3905 WHERE orig_status < 100;
3906 EXCEPTION
3907 WHEN OTHERS THEN
3908 x_return_status := 'D';
3909 insert_message (
3910 p_table_name => 'GME_BATCH_HISTORY',
3911 p_procedure_name => 'INSERT_BATCH_HISTORY',
3912 p_parameters => '',
3913 p_message => SQLERRM,
3914 p_error_type => x_return_status
3915 );
3916 END insert_batch_history;
3917
3918 PROCEDURE insert_batch_step_transfers (x_return_status OUT NOCOPY VARCHAR2) IS
3919 BEGIN
3920 x_return_status := FND_API.G_RET_STS_SUCCESS;
3921
3922 INSERT INTO gme_batch_step_transfers
3923 (wip_trans_id,
3924 batch_id,
3925 batchstep_no,
3926 transfer_step_no,
3927 line_type,
3928 trans_qty,
3929 trans_um,
3930 trans_date,
3931 last_updated_by,
3932 last_update_date,
3933 last_update_login,
3934 creation_date,
3935 created_by,
3936 delete_mark,
3937 text_code,
3938 attribute1,
3939 attribute2,
3940 attribute3,
3941 attribute4,
3942 attribute5,
3943 attribute6,
3944 attribute7,
3945 attribute8,
3946 attribute9,
3947 attribute10,
3948 attribute11,
3949 attribute12,
3950 attribute13,
3951 attribute14,
3952 attribute15,
3953 attribute16,
3954 attribute17,
3955 attribute18,
3956 attribute19,
3957 attribute20,
3958 attribute21,
3959 attribute22,
3960 attribute23,
3961 attribute24,
3962 attribute25,
3963 attribute26,
3964 attribute27,
3965 attribute28,
3966 attribute29,
3967 attribute30,
3968 attribute_category
3969 )
3970 SELECT wip_trans_id,
3971 batch_id,
3972 batchstep_no,
3973 transfer_step_no,
3974 line_type,
3975 trans_qty,
3976 trans_um,
3977 trans_date,
3978 last_updated_by,
3979 last_update_date,
3980 last_update_login,
3981 creation_date,
3982 created_by,
3983 delete_mark,
3984 text_code,
3985 attribute1,
3986 attribute2,
3987 attribute3,
3988 attribute4,
3989 attribute5,
3990 attribute6,
3991 attribute7,
3992 attribute8,
3993 attribute9,
3994 attribute10,
3995 attribute11,
3996 attribute12,
3997 attribute13,
3998 attribute14,
3999 attribute15,
4000 attribute16,
4001 attribute17,
4002 attribute18,
4003 attribute19,
4004 attribute20,
4005 attribute21,
4006 attribute22,
4007 attribute23,
4008 attribute24,
4009 attribute25,
4010 attribute26,
4011 attribute27,
4012 attribute28,
4013 attribute29,
4014 attribute30,
4015 attribute_category
4016 FROM pm_oprn_wip
4017 WHERE delete_mark < 100;
4018
4019 insert_message (
4020 p_table_name => 'gme_batch_step_transfers',
4021 p_procedure_name => 'insert_batch_step_transfers',
4022 p_parameters => 'none',
4023 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
4024 p_error_type => 'P'
4025 );
4026
4027 UPDATE pm_oprn_wip
4028 SET delete_mark = delete_mark + 100
4029 WHERE delete_mark < 100;
4030 EXCEPTION
4031 WHEN OTHERS THEN
4032 x_return_status := 'D';
4033 insert_message (
4034 p_table_name => 'GME_BATCH_STEP_TRANSFERS',
4035 p_procedure_name => 'INSERT_BATCH_STEP_TRANSFERS',
4036 p_parameters => '',
4037 p_message => SQLERRM,
4038 p_error_type => x_return_status
4039 );
4040 END insert_batch_step_transfers;
4041
4042 PROCEDURE insert_text_header (x_return_status OUT NOCOPY VARCHAR2) IS
4043 BEGIN
4044 x_return_status := FND_API.G_RET_STS_SUCCESS;
4045
4046 INSERT INTO gme_text_header
4047 (text_code,
4048 last_updated_by,
4049 created_by,
4050 last_update_date,
4051 creation_date,
4052 last_update_login
4053 )
4054 SELECT text_code,
4055 last_updated_by,
4056 created_by,
4057 last_update_date,
4058 creation_date,
4059 last_update_login
4060 FROM pm_text_hdr pm
4061 WHERE NOT EXISTS ( SELECT 1
4062 FROM gme_text_header
4063 WHERE text_code = pm.text_code);
4064
4065 insert_message (
4066 p_table_name => 'gme_text_header/pm_text_hdr',
4067 p_procedure_name => 'insert_text_header',
4068 p_parameters => 'none',
4069 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
4070 p_error_type => 'P'
4071 );
4072
4073 INSERT INTO gme_text_header
4074 (text_code,
4075 last_updated_by,
4076 created_by,
4077 last_update_date,
4078 creation_date,
4079 last_update_login
4080 )
4081 SELECT text_code,
4082 last_updated_by,
4083 created_by,
4084 last_update_date,
4085 creation_date,
4086 last_update_login
4087 FROM pc_text_hdr pc
4088 WHERE NOT EXISTS ( SELECT 1
4089 FROM gme_text_header
4090 WHERE text_code = pc.text_code);
4091
4092 insert_message (
4093 p_table_name => 'gme_text_header/pc_text_hdr',
4094 p_procedure_name => 'insert_text_header',
4095 p_parameters => 'none',
4096 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
4097 p_error_type => 'P'
4098 );
4099 EXCEPTION
4100 WHEN OTHERS THEN
4101 x_return_status := 'D';
4102 insert_message (
4103 p_table_name => 'GME_TEXT_HEADER',
4104 p_procedure_name => 'INSERT_TEXT_HEADER',
4105 p_parameters => '',
4106 p_message => SQLERRM,
4107 p_error_type => x_return_status
4108 );
4109 END insert_text_header;
4110
4111 PROCEDURE insert_text_dtl (x_return_status OUT NOCOPY VARCHAR2) IS
4112 BEGIN
4113 x_return_status := FND_API.G_RET_STS_SUCCESS;
4114
4115 INSERT INTO gme_text_table_tl
4116 (text_code,
4117 lang_code,
4118 paragraph_code,
4119 sub_paracode,
4120 line_no,
4121 text,
4122 language,
4123 source_lang,
4124 last_updated_by,
4125 created_by,
4126 last_update_date,
4127 creation_date,
4128 last_update_login
4129 )
4130 SELECT text_code,
4131 lang_code,
4132 paragraph_code,
4133 sub_paracode,
4134 line_no,
4135 text,
4136 language,
4137 source_lang,
4138 last_updated_by,
4139 created_by,
4140 last_update_date,
4141 creation_date,
4142 last_update_login
4143 FROM pm_text_tbl_tl pm
4144 WHERE NOT EXISTS ( SELECT 1
4145 FROM gme_text_table_tl
4146 WHERE text_code = pm.text_code);
4147
4148 insert_message (
4149 p_table_name => 'gme_text_table_tl/pm_text_tbl_tl',
4150 p_procedure_name => 'insert_text_dtl',
4151 p_parameters => 'none',
4152 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
4153 p_error_type => 'P'
4154 );
4155
4156 INSERT INTO gme_text_table_tl
4157 (text_code,
4158 lang_code,
4159 paragraph_code,
4160 sub_paracode,
4161 line_no,
4162 text,
4163 language,
4164 source_lang,
4165 last_updated_by,
4166 created_by,
4167 last_update_date,
4168 creation_date,
4169 last_update_login
4170 )
4171 SELECT text_code,
4172 lang_code,
4173 paragraph_code,
4174 sub_paracode,
4175 line_no,
4176 text,
4177 language,
4178 source_lang,
4179 last_updated_by,
4180 created_by,
4181 last_update_date,
4182 creation_date,
4183 last_update_login
4184 FROM pc_text_tbl_tl pc
4185 WHERE NOT EXISTS ( SELECT 1
4186 FROM gme_text_table_tl
4187 WHERE text_code = pc.text_code);
4188
4189 insert_message (
4190 p_table_name => 'gme_text_table_tl/pc_text_tbl_tl',
4191 p_procedure_name => 'insert_text_dtl',
4192 p_parameters => 'none',
4193 p_message => 'number of records inserted = ' || SQL%ROWCOUNT,
4194 p_error_type => 'P'
4195 );
4196 EXCEPTION
4197 WHEN OTHERS THEN
4198 x_return_status := 'D';
4199 insert_message (
4200 p_table_name => 'GME_TEXT_TABLE_TL',
4201 p_procedure_name => 'INSERT_TEXT_DTL',
4202 p_parameters => '',
4203 p_message => SQLERRM,
4204 p_error_type => x_return_status
4205 );
4206 END insert_text_dtl;
4207
4208 PROCEDURE split_trans_line (x_return_status OUT NOCOPY VARCHAR2) IS
4209 CURSOR cur_get_materials IS
4210 SELECT b.batch_id,
4211 m.line_id
4212 FROM pm_matl_dtl_bak m, pm_btch_hdr_bak b
4213 WHERE m.in_use < 100 AND -- => only check for lines that were not migrated
4214 b.batch_id = m.batch_id AND
4215 b.batch_status IN (2, 3) AND
4216 b.batch_type = 0
4217 ORDER BY m.batch_id;
4218
4219 l_matl cur_get_materials%ROWTYPE;
4220 l_tran_rec ic_tran_pnd%ROWTYPE;
4221 l_def_lot_id ic_tran_pnd.trans_id%TYPE;
4222 l_trans_id ic_tran_pnd.trans_id%TYPE;
4223 l_is_plain BOOLEAN;
4224 l_load_trans_fail BOOLEAN;
4225 l_batch_header pm_btch_hdr_bak%ROWTYPE;
4226 l_pos NUMBER := 0;
4227 l_flip_count NUMBER := 0;
4228 l_new_txn_count NUMBER := 0;
4229 l_return_status VARCHAR2 (1);
4230 BEGIN
4231 x_return_status := FND_API.G_RET_STS_SUCCESS;
4232 l_pos := 1;
4233 OPEN cur_get_materials;
4234 l_pos := 2;
4235 FETCH cur_get_materials INTO l_matl;
4236 l_pos := 3;
4237
4238 WHILE cur_get_materials%FOUND
4239 LOOP
4240 l_batch_header.batch_id := l_matl.batch_id;
4241 l_pos := 4;
4242 load_trans (l_batch_header, l_return_status);
4243
4244 IF l_return_status <> x_return_status THEN
4245 l_load_trans_fail := TRUE;
4246 l_def_lot_id := NULL;
4247
4248 insert_message (
4249 p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4250 p_procedure_name => 'check_wip_batches',
4251 p_parameters => 'batch_id = '
4252 || l_batch_header.batch_id
4253 || ' line_id = '
4254 || l_matl.line_id,
4255 p_message => 'Unable to load the transactions',
4256 p_error_type => FND_API.G_RET_STS_ERROR
4257 );
4258 ELSE
4259 l_load_trans_fail := FALSE;
4260 l_pos := 5;
4261 get_default_lot (
4262 l_matl.line_id,
4263 l_def_lot_id,
4264 l_is_plain,
4265 l_return_status
4266 );
4267 l_pos := 6;
4268 END IF;
4269
4270 IF l_def_lot_id IS NULL OR
4271 l_return_status <> x_return_status THEN
4272 -- No need to write message if the load trans failed, because message
4273 -- for that was already written, and if load_trans failed, it follows that def lot
4274 -- could not be found.
4275 IF l_load_trans_fail = FALSE THEN
4276 insert_message (
4277 p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4278 p_procedure_name => 'check_wip_batches',
4279 p_parameters => 'batch_id = '
4280 || l_batch_header.batch_id
4281 || ' line_id = '
4282 || l_matl.line_id,
4283 p_message => 'Unable to determine the default lot.',
4284 p_error_type => FND_API.G_RET_STS_ERROR
4285 );
4286 END IF;
4287 ELSE
4288 SELECT *
4289 INTO l_tran_rec
4290 FROM ic_tran_pnd
4291 WHERE trans_id = l_def_lot_id;
4292
4293 IF l_tran_rec.completed_ind = 1 THEN
4294 IF (l_is_plain = FALSE) THEN
4295 /* transaction is zero qty and it's completed... let's un-complete it...
4296 if the def transaction is non zero qty for non plain item and completed, it would have
4297 been caught in check_wip_batches routine.
4298 Note, that data condition is only valid in WIP batch. */
4299 -- Can get here for non-plain items which could not be reversed in check_wip_batches.
4300 -- So, before flipping the completed ind, make sure it's zero qty and that we can flip the ind.
4301 IF l_tran_rec.trans_qty = 0 THEN
4302 UPDATE ic_tran_pnd
4303 SET completed_ind = 0
4304 WHERE trans_id = l_tran_rec.trans_id;
4305 END IF;
4306
4307 l_flip_count := l_flip_count + 1;
4308 ELSE -- this is a plain item
4309 -- Just create a new 0 transaction record that's pending if the qty is non zero.
4310 IF l_tran_rec.trans_qty = 0 THEN
4311 UPDATE ic_tran_pnd
4312 SET completed_ind = 0
4313 WHERE trans_id = l_tran_rec.trans_id;
4314 ELSE
4315 SELECT gem5_trans_id_s.NEXTVAL
4316 INTO l_trans_id
4317 FROM sys.DUAL;
4318
4319 INSERT INTO ic_tran_pnd
4320 (trans_id,
4321 item_id,
4322 line_id,
4323 co_code,
4324 orgn_code,
4325 whse_code,
4326 lot_id,
4327 location,
4328 doc_id,
4329 doc_type,
4330 doc_line,
4331 line_type,
4332 reason_code,
4333 creation_date,
4334 trans_date,
4335 trans_qty,
4336 trans_qty2,
4337 qc_grade,
4338 lot_status,
4339 trans_stat,
4340 trans_um,
4341 trans_um2,
4342 op_code,
4343 completed_ind,
4344 staged_ind,
4345 gl_posted_ind,
4346 event_id,
4347 delete_mark,
4348 text_code,
4349 last_update_date,
4350 created_by,
4351 last_updated_by,
4352 last_update_login,
4353 program_application_id,
4354 program_id,
4355 program_update_date,
4356 request_id,
4357 reverse_id
4358 )
4359 VALUES (l_trans_id,
4360 l_tran_rec.item_id,
4361 l_tran_rec.line_id,
4362 l_tran_rec.co_code,
4363 l_tran_rec.orgn_code,
4364 l_tran_rec.whse_code,
4365 l_tran_rec.lot_id,
4366 l_tran_rec.location,
4367 l_tran_rec.doc_id,
4368 l_tran_rec.doc_type,
4369 l_tran_rec.doc_line,
4370 l_tran_rec.line_type,
4371 l_tran_rec.reason_code,
4372 l_tran_rec.creation_date,
4373 l_tran_rec.trans_date,
4374 0 /* l_tran_rec.trans_qty */,
4375 0 /* l_tran_rec.trans_qty2 */,
4376 l_tran_rec.qc_grade,
4377 l_tran_rec.lot_status,
4378 l_tran_rec.trans_stat,
4379 l_tran_rec.trans_um,
4380 l_tran_rec.trans_um2,
4381 l_tran_rec.op_code,
4382 0 /* l_tran_rec.completed_ind */,
4383 l_tran_rec.staged_ind,
4384 l_tran_rec.gl_posted_ind,
4385 l_tran_rec.event_id,
4386 l_tran_rec.delete_mark,
4387 l_tran_rec.text_code,
4388 l_tran_rec.last_update_date,
4389 l_tran_rec.created_by,
4390 l_tran_rec.last_updated_by,
4391 l_tran_rec.last_update_login,
4392 l_tran_rec.program_application_id,
4393 l_tran_rec.program_id,
4394 l_tran_rec.program_update_date,
4395 l_tran_rec.request_id,
4396 l_tran_rec.reverse_id
4397 );
4398
4399 l_new_txn_count := l_new_txn_count + 1;
4400 END IF; /* IF l_tran_rec.trans_qty = 0 */
4401 END IF; /* IF (l_is_plain = FALSE) THEN */
4402 END IF; /* IF l_tran_rec.completed_ind = 1 THEN */
4403 END IF;
4404
4405 /* IF l_def_lot_id IS NULL OR l_return_status <> x_return_status THEN */
4406
4407 FETCH cur_get_materials INTO l_matl;
4408 END LOOP; /* WHILE cur_get_materials%FOUND LOOP */
4409
4410 CLOSE cur_get_materials;
4411 insert_message (
4412 p_table_name => 'ic_tran_pnd',
4413 p_procedure_name => 'split_trans_line',
4414 p_parameters => 'none',
4415 p_message => 'number of zero quantity transactions uncompleted = '
4416 || l_flip_count,
4417 p_error_type => 'P'
4418 );
4419 insert_message (
4420 p_table_name => 'ic_tran_pnd',
4421 p_procedure_name => 'split_trans_line',
4422 p_parameters => 'none',
4423 p_message => 'number of zero quantity transactions inserted = '
4424 || l_new_txn_count,
4425 p_error_type => 'P'
4426 );
4427 EXCEPTION
4428 WHEN OTHERS THEN
4429 x_return_status := 'D';
4430 insert_message (
4431 p_table_name => 'gme_inventory_txns_gtmp/ic_tran_pnd',
4432 p_procedure_name => 'SPLIT_TRANS_LINE',
4433 p_parameters => 'Batch ID='
4434 || l_matl.batch_id
4435 || ' Line ID='
4436 || l_matl.line_id,
4437 p_message => SQLERRM,
4438 p_error_type => x_return_status
4439 );
4440 RAISE;
4441 END split_trans_line;
4442
4443 PROCEDURE load_trans (
4444 p_batch_row IN pm_btch_hdr_bak%ROWTYPE,
4445 x_return_status OUT NOCOPY VARCHAR2
4446 ) IS
4447 CURSOR c_get_init_reversal IS
4448 SELECT *
4449 FROM gme_inventory_txns_gtmp
4450 WHERE transaction_no = 2 AND
4451 trans_qty <>
4452 0 -- these are already matched up... don't match them again.
4453 ORDER BY line_type,
4454 item_id,
4455 material_detail_id,
4456 whse_code,
4457 lot_id,
4458 location,
4459 completed_ind,
4460 trans_id;
4461
4462 CURSOR c_get_match_reversal IS
4463 SELECT *
4464 FROM gme_inventory_txns_gtmp
4465 WHERE transaction_no <> 2 -- Should this be indexed.
4466 ORDER BY line_type,
4467 item_id,
4468 material_detail_id,
4469 whse_code,
4470 lot_id,
4471 location,
4472 completed_ind,
4473 trans_id;
4474
4475 CURSOR c_get_cmplt_zero_def_txns IS
4476 SELECT *
4477 FROM gme_inventory_txns_gtmp
4478 WHERE completed_ind = 1 AND
4479 trans_qty = 0
4480 ORDER BY line_type,
4481 item_id,
4482 material_detail_id,
4483 whse_code,
4484 lot_id,
4485 location,
4486 completed_ind,
4487 trans_id;
4488
4489 CURSOR c_check_mat_transactions (
4490 p_batch_id IN NUMBER,
4491 p_batch_type IN VARCHAR2
4492 ) IS
4493 SELECT 1
4494 FROM gme_inventory_txns_gtmp
4495 WHERE doc_id = p_batch_id AND
4496 doc_type = p_batch_type AND
4497 ROWNUM = 1;
4498
4499 l_api_name CONSTANT VARCHAR2 (30) := 'LOAD_TRANS';
4500 l_inv_exists NUMBER := 0;
4501 l_batch_id pm_btch_hdr_bak.batch_id%TYPE;
4502 l_doc_type ic_tran_pnd.doc_type%TYPE;
4503 init_revs c_get_init_reversal%ROWTYPE;
4504 match_revs c_get_match_reversal%ROWTYPE;
4505 l_last_txn c_get_cmplt_zero_def_txns%ROWTYPE;
4506 l_current_txn c_get_cmplt_zero_def_txns%ROWTYPE;
4507 l_return_status VARCHAR2 (1) := FND_API.G_RET_STS_SUCCESS;
4508 l_batch_row pm_btch_hdr_bak%ROWTYPE;
4509 no_batch_id EXCEPTION;
4510 error_inserting_txn EXCEPTION;
4511 BEGIN
4512 x_return_status := FND_API.G_RET_STS_SUCCESS;
4513
4514 /* Check that we have at least a BATCH ID */
4515 IF ((p_batch_row.batch_id IS NULL) OR
4516 (p_batch_row.batch_id = FND_API.g_miss_num)
4517 ) THEN
4518 RAISE no_batch_id;
4519 END IF;
4520
4521 l_batch_id := p_batch_row.batch_id;
4522 SELECT *
4523 INTO l_batch_row
4524 FROM pm_btch_hdr_bak
4525 WHERE batch_id = l_batch_id;
4526
4527 -- Detemine Transactional Doc Type
4528 -- 0 - PROD 10 - FPO
4529
4530 IF (l_batch_row.batch_type = 0) THEN
4531 l_doc_type := 'PROD';
4532 ELSIF (l_batch_row.batch_type = 10) THEN
4533 l_doc_type := 'FPO';
4534 END IF;
4535
4536 /* Now Validate Transactions */
4537 /* Have Been Loaded */
4538
4539 -- Check if values already exist in Table
4540
4541 OPEN c_check_mat_transactions (l_batch_row.batch_id, l_doc_type);
4542 FETCH c_check_mat_transactions INTO l_inv_exists;
4543 CLOSE c_check_mat_transactions;
4544
4545 IF (l_inv_exists > 0) THEN -- We have Alreay Loaded INV Batch Data
4546 NULL;
4547 ELSE -- Now Populate The GME_INVENTORY_TXNS_GTMP table
4548 -- Should this be in same file as other table routines
4549 /* Clear out the temp table when a new batch is encountered;
4550 -- The code that calls this orders the batches by batch_id so, once a batch
4551 -- is loaded to the temp table, it doesn't have to be loaded for subsequent matl lines;
4552 -- however, once a new batch_id is started, we know we are done with the old batch, so,
4553 -- we can remove the data from the previous batch. */
4554 DELETE FROM gme_inventory_txns_gtmp;
4555
4556 insert_inv_txns_gtmp (
4557 p_batch_id => l_batch_id,
4558 p_doc_type => l_doc_type,
4559 x_return_status => l_return_status
4560 );
4561
4562 IF l_return_status <> x_return_status THEN
4563 RAISE error_inserting_txn;
4564 END IF;
4565
4566 /* Lets Now Mark all Transactions That are Reversals */
4567
4568 /* Let's look at zero completed def transactions first */
4569 OPEN c_get_cmplt_zero_def_txns;
4570 FETCH c_get_cmplt_zero_def_txns INTO l_last_txn;
4571
4572 IF c_get_cmplt_zero_def_txns%FOUND THEN
4573 LOOP
4574 FETCH c_get_cmplt_zero_def_txns INTO l_current_txn;
4575 EXIT WHEN c_get_cmplt_zero_def_txns%NOTFOUND;
4576
4577 IF (l_last_txn.material_detail_id =
4578 l_current_txn.material_detail_id
4579 ) AND
4580 (l_last_txn.line_type = l_current_txn.line_type) AND
4581 (l_last_txn.item_id = l_current_txn.item_id) AND
4582 (l_last_txn.whse_code = l_current_txn.whse_code) AND
4583 (l_last_txn.lot_id = l_current_txn.lot_id) AND
4584 (l_last_txn.location = l_current_txn.location) THEN
4585 UPDATE gme_inventory_txns_gtmp
4586 SET transaction_no = 2
4587 WHERE trans_id IN
4588 (l_last_txn.trans_id, l_current_txn.trans_id);
4589
4590 FETCH c_get_cmplt_zero_def_txns INTO l_last_txn;
4591 ELSE
4592 l_last_txn := l_current_txn;
4593 END IF;
4594 END LOOP;
4595 END IF;
4596
4597 CLOSE c_get_cmplt_zero_def_txns;
4598
4599 UPDATE gme_inventory_txns_gtmp
4600 SET transaction_no = 2
4601 WHERE ((line_type = -1 AND -- Ingredient
4602 trans_qty > 0
4603 ) OR
4604 (line_type <> -1 AND
4605 trans_qty < 0
4606 )
4607 );
4608
4609 IF (SQL%ROWCOUNT > 0) THEN
4610 OPEN c_get_init_reversal;
4611
4612 LOOP
4613 FETCH c_get_init_reversal INTO init_revs;
4614 EXIT WHEN c_get_init_reversal%NOTFOUND;
4615 OPEN c_get_match_reversal;
4616
4617 LOOP
4618 FETCH c_get_match_reversal INTO match_revs;
4619 EXIT WHEN c_get_match_reversal%NOTFOUND;
4620
4621 IF ((init_revs.material_detail_id =
4622 match_revs.material_detail_id
4623 ) AND
4624 (init_revs.line_type = match_revs.line_type) AND
4625 (init_revs.item_id = match_revs.item_id) AND
4626 (init_revs.whse_code = match_revs.whse_code) AND
4627 (init_revs.lot_id = match_revs.lot_id) AND
4628 (init_revs.location = match_revs.location) AND
4629 (init_revs.completed_ind = match_revs.completed_ind) AND
4630 ((init_revs.trans_qty) + (match_revs.trans_qty) = 0)
4631 ) THEN
4632 UPDATE gme_inventory_txns_gtmp
4633 SET transaction_no = 2
4634 WHERE trans_id = match_revs.trans_id;
4635
4636 EXIT;
4637 END IF;
4638 END LOOP;
4639
4640 CLOSE c_get_match_reversal;
4641 END LOOP;
4642
4643 CLOSE c_get_init_reversal;
4644 END IF;
4645 END IF;
4646
4647 x_return_status := l_return_status;
4648 EXCEPTION
4649 WHEN no_batch_id THEN
4650 x_return_status := 'E';
4651 insert_message (
4652 p_table_name => 'gme_inventory_txns_gtmp',
4653 p_procedure_name => 'load_trans',
4654 p_parameters => '',
4655 p_message => 'Batch_id not specified for load',
4656 p_error_type => x_return_status
4657 );
4658 WHEN error_inserting_txn THEN
4659 x_return_status := l_return_status;
4660 RAISE;
4661 WHEN OTHERS THEN
4662 x_return_status := 'D';
4663 insert_message (
4664 p_table_name => 'gme_inventory_txns_gtmp',
4665 p_procedure_name => 'load_trans',
4666 p_parameters => 'batch_id = ' || l_batch_id,
4667 p_message => SQLERRM -- || ' with pos = ' || l_pos
4668 ,
4669 p_error_type => x_return_status
4670 );
4671 RAISE;
4672 END load_trans;
4673
4674 PROCEDURE insert_inv_txns_gtmp (
4675 p_batch_id IN pm_btch_hdr_bak.batch_id%TYPE,
4676 p_doc_type IN ic_tran_pnd.doc_type%TYPE,
4677 x_return_status OUT NOCOPY VARCHAR2,
4678 p_trans_id IN ic_tran_pnd.trans_id%TYPE DEFAULT NULL
4679 ) IS
4680 l_all_txns VARCHAR2 (100);
4681 BEGIN
4682 x_return_status := FND_API.G_RET_STS_SUCCESS;
4683
4684 INSERT INTO gme_inventory_txns_gtmp
4685 (trans_id,
4686 item_id,
4687 co_code,
4688 orgn_code,
4689 whse_code,
4690 lot_id,
4691 location,
4692 doc_id,
4693 doc_type,
4694 doc_line,
4695 line_type,
4696 reason_code,
4697 trans_date,
4698 trans_qty,
4699 trans_qty2,
4700 qc_grade,
4701 lot_status,
4702 trans_stat,
4703 trans_um,
4704 trans_um2,
4705 completed_ind,
4706 staged_ind,
4707 gl_posted_ind,
4708 event_id,
4709 delete_mark,
4710 text_code,
4711 action_code,
4712 material_detail_id,
4713 transaction_no,
4714 organization_id,
4715 locator_id,
4716 subinventory,
4717 alloc_um,
4718 alloc_qty
4719 )
4720 SELECT i.trans_id,
4721 i.item_id,
4722 i.co_code,
4723 i.orgn_code,
4724 i.whse_code,
4725 i.lot_id,
4726 i.location,
4727 i.doc_id,
4728 i.doc_type,
4729 i.doc_line,
4730 i.line_type,
4731 i.reason_code,
4732 i.trans_date,
4733 i.trans_qty,
4734 i.trans_qty2,
4735 i.qc_grade,
4736 i.lot_status,
4737 i.trans_stat,
4738 i.trans_um,
4739 i.trans_um2,
4740 i.completed_ind,
4741 i.staged_ind,
4742 i.gl_posted_ind,
4743 i.event_id,
4744 i.delete_mark,
4745 i.text_code,
4746 'NONE',
4747 i.line_id,
4748 1,
4749 0,
4750 0,
4751 NULL,
4752 NULL,
4753 NULL
4754 FROM ic_tran_pnd i
4755 WHERE doc_id = p_batch_id AND
4756 doc_type = p_doc_type AND
4757 -- retrieve only the trans_id passed or if that's NULL, all txns for the batch
4758 (i.trans_id = p_trans_id OR
4759 p_trans_id IS NULL
4760 ) AND
4761 -- return only those txns that look like they may be def txns
4762 -- in get_default_lot, if more than one of these came back, we will determine which
4763 -- txn is really the default txn
4764 lot_id = 0 AND
4765 location = p_default_loct AND
4766 delete_mark = 0;
4767 EXCEPTION
4768 WHEN OTHERS THEN
4769 x_return_status := 'D';
4770
4771 IF p_trans_id IS NULL THEN
4772 l_all_txns := ' Note: Attempted to insert all batch txns.';
4773 ELSE
4774 l_all_txns := ' Attempt to insert single transaction.';
4775 END IF;
4776
4777 insert_message (
4778 p_table_name => 'GME_INVENTORY_TXNS_GTMP',
4779 p_procedure_name => 'INSERT_INV_TXNS_GTMP',
4780 p_parameters => ' Batch_Id=>'
4781 || p_batch_id
4782 || ' Doc Type=>'
4783 || p_doc_type
4784 || ' Trans ID=>'
4785 || p_trans_id
4786 || l_all_txns,
4787 p_message => SQLERRM,
4788 p_error_type => x_return_status
4789 );
4790 RAISE;
4791 END insert_inv_txns_gtmp;
4792
4793 PROCEDURE get_default_lot (
4794 p_line_id IN pm_matl_dtl_bak.line_id%TYPE,
4795 x_def_lot_id OUT NOCOPY ic_tran_pnd.trans_id%TYPE,
4796 x_is_plain OUT NOCOPY BOOLEAN,
4797 x_return_status OUT NOCOPY VARCHAR2
4798 ) IS
4799 l_matl_dtl pm_matl_dtl_bak%ROWTYPE;
4800 l_item_mst ic_item_mst%ROWTYPE;
4801 l_ic_tran_pnd ic_tran_pnd%ROWTYPE;
4802 l_whse_loct_ctl ic_whse_mst.whse_code%TYPE;
4803 l_def_lot_found BOOLEAN;
4804
4805 CURSOR cur_get_def_trans (
4806 v_batch_id pm_btch_hdr_bak.batch_id%TYPE,
4807 v_line_id pm_matl_dtl_bak.line_id%TYPE,
4808 v_doc_type gme_inventory_txns_gtmp.doc_type%TYPE
4809 ) IS
4810 -- The following cursor does not look at lot_id and loct because these were already screened
4811 -- in the cursor which populated gme_inventory_txns_gtmp in load_trans.
4812 SELECT trans_id,
4813 whse_code
4814 FROM gme_inventory_txns_gtmp
4815 WHERE doc_id = v_batch_id AND
4816 doc_type = v_doc_type AND
4817 material_detail_id = v_line_id AND
4818 transaction_no <> 2 -- don't look at the reversals...
4819 ORDER BY line_type,
4820 item_id,
4821 material_detail_id,
4822 whse_code,
4823 lot_id,
4824 location,
4825 completed_ind,
4826 trans_id;
4827
4828 CURSOR cur_get_whse_ctl (v_whse_code IN VARCHAR2) IS
4829 SELECT loct_ctl
4830 FROM ic_whse_mst
4831 WHERE whse_code = v_whse_code;
4832
4833 CURSOR cur_get_batch_info (v_batch_id IN NUMBER) IS
4834 SELECT batch_no,
4835 plant_code
4836 FROM pm_btch_hdr_bak
4837 WHERE batch_id = v_batch_id;
4838
4839 get_batch_info cur_get_batch_info%ROWTYPE;
4840 get_trans_rec cur_get_def_trans%ROWTYPE;
4841 l_tran_whse ps_whse_eff.whse_code%TYPE;
4842 l_batch_type pm_btch_hdr_bak.batch_type%TYPE;
4843 l_doc_type gme_inventory_txns_gtmp.doc_type%TYPE;
4844 l_pos NUMBER := 0;
4845 l_cnt NUMBER;
4846 BEGIN
4847 x_return_status := FND_API.G_RET_STS_SUCCESS;
4848 l_pos := 1;
4849 SELECT *
4850 INTO l_matl_dtl
4851 FROM pm_matl_dtl_bak
4852 WHERE line_id = p_line_id;
4853 l_pos := 2;
4854 SELECT *
4855 INTO l_item_mst
4856 FROM ic_item_mst
4857 WHERE item_id = l_matl_dtl.item_id;
4858 l_pos := 3;
4859 l_def_lot_found := FALSE;
4860 x_def_lot_id := 0;
4861 SELECT batch_type
4862 INTO l_batch_type
4863 FROM pm_btch_hdr_bak
4864 WHERE batch_id = l_matl_dtl.batch_id;
4865 l_pos := 4;
4866
4867 IF (l_batch_type = 0) THEN
4868 l_doc_type := 'PROD';
4869 ELSIF (l_batch_type = 10) THEN
4870 l_doc_type := 'FPO';
4871 END IF;
4872
4873 SELECT COUNT (1)
4874 INTO l_cnt
4875 FROM gme_inventory_txns_gtmp
4876 WHERE doc_id = l_matl_dtl.batch_id AND
4877 doc_type = l_doc_type AND
4878 material_detail_id = p_line_id AND
4879 transaction_no <> 2 AND
4880 trans_qty = 0;
4881 l_pos := 5;
4882
4883 IF l_cnt = 1 THEN
4884 -- This is the default lot for sure, because there can only at most one zero qty txn, completed
4885 -- or pending. No need for further processing.
4886
4887 l_pos := 6;
4888 SELECT trans_id
4889 INTO x_def_lot_id
4890 FROM gme_inventory_txns_gtmp
4891 WHERE doc_id = l_matl_dtl.batch_id AND
4892 doc_type = l_doc_type AND
4893 material_detail_id = p_line_id AND
4894 transaction_no <> 2 AND
4895 trans_qty = 0;
4896 l_pos := 7;
4897 l_def_lot_found := TRUE;
4898 ELSE
4899 l_pos := 9;
4900
4901 FOR get_rec IN cur_get_def_trans (
4902 l_matl_dtl.batch_id,
4903 p_line_id,
4904 l_doc_type
4905 )
4906 LOOP
4907 l_pos := 10;
4908 OPEN cur_get_whse_ctl (get_rec.whse_code);
4909 FETCH cur_get_whse_ctl INTO l_whse_loct_ctl;
4910 CLOSE cur_get_whse_ctl;
4911 l_pos := 11;
4912
4913 IF l_item_mst.lot_ctl = 1 OR
4914 (l_item_mst.loct_ctl > 0 AND
4915 l_whse_loct_ctl > 0
4916 ) THEN
4917 -- This should be the only transaction that was returned for lot or loct ctrl
4918 x_def_lot_id := get_rec.trans_id;
4919 x_is_plain := FALSE;
4920 l_def_lot_found := TRUE;
4921 -- If you find a lot or loct ctrl whse, then don't look any further. Looking further may
4922 -- get you in trouble if there is a plain controlled transaction fetched after this record
4923 -- which belongs to the cons/respl whse for the item. In that case, the plain txn will override
4924 -- this transaction and we definitely don't want that.
4925 l_pos := 12;
4926 ELSE
4927 IF l_def_lot_found = FALSE THEN
4928 l_pos := 13;
4929 x_is_plain := TRUE;
4930 SELECT *
4931 INTO l_ic_tran_pnd
4932 FROM ic_tran_pnd
4933 WHERE trans_id = get_rec.trans_id;
4934 deduce_transaction_warehouse (
4935 p_transaction => l_ic_tran_pnd,
4936 p_item_master => l_item_mst,
4937 x_whse_code => l_tran_whse
4938 );
4939 l_pos := 14;
4940
4941 IF (l_tran_whse = l_ic_tran_pnd.whse_code) THEN
4942 x_def_lot_id := get_rec.trans_id;
4943
4944 IF l_ic_tran_pnd.completed_ind = 0 OR
4945 l_ic_tran_pnd.trans_qty = 0 THEN
4946 l_def_lot_found := TRUE;
4947 END IF;
4948 END IF;
4949 END IF;
4950 END IF;
4951 END LOOP;
4952
4953 l_pos := 15;
4954
4955 IF x_def_lot_id = 0 THEN
4956 l_pos := 16;
4957 OPEN cur_get_def_trans (
4958 l_matl_dtl.batch_id,
4959 p_line_id,
4960 l_doc_type
4961 );
4962 FETCH cur_get_def_trans INTO get_trans_rec;
4963 x_def_lot_id := get_trans_rec.trans_id;
4964 CLOSE cur_get_def_trans;
4965 x_is_plain := TRUE;
4966 l_pos := 17;
4967 END IF;
4968 END IF;
4969
4970 x_return_status := FND_API.G_RET_STS_SUCCESS;
4971 EXCEPTION
4972 WHEN OTHERS THEN
4973 x_return_status := 'D';
4974 insert_message (
4975 p_table_name => 'gme_inventory_txns_gtmp',
4976 p_procedure_name => 'get_default_lot',
4977 p_parameters => 'line_id= ' || p_line_id,
4978 p_message => SQLERRM || ' with pos = ' || l_pos,
4979 p_error_type => x_return_status
4980 );
4981 END get_default_lot;
4982
4983 PROCEDURE deduce_transaction_warehouse (
4984 p_transaction IN ic_tran_pnd%ROWTYPE,
4985 p_item_master IN ic_item_mst%ROWTYPE,
4986 x_whse_code OUT NOCOPY ps_whse_eff.whse_code%TYPE
4987 ) IS
4988 CURSOR cur_eff_whse (
4989 p_orgn_code VARCHAR2,
4990 p_item_id NUMBER,
4991 p_line_type NUMBER
4992 ) IS
4993 SELECT whse_code
4994 FROM ps_whse_eff
4995 WHERE plant_code = p_orgn_code AND
4996 (whse_item_id IS NULL OR whse_item_id = p_item_id) AND
4997 ((p_line_type > 0 AND replen_ind = 1) OR
4998 (p_line_type < 0 AND consum_ind = 1)
4999 )
5000 ORDER BY whse_item_id, whse_code;
5001 BEGIN
5002 OPEN cur_eff_whse (
5003 p_transaction.orgn_code,
5004 p_item_master.whse_item_id,
5005 p_transaction.line_type
5006 );
5007 FETCH cur_eff_whse INTO x_whse_code;
5008
5009 IF cur_eff_whse%NOTFOUND THEN
5010 x_whse_code := NULL;
5011 END IF;
5012
5013 CLOSE cur_eff_whse;
5014 EXCEPTION
5015 WHEN OTHERS THEN
5016 x_whse_code := NULL;
5017 END deduce_transaction_warehouse;
5018
5019 /*****************************************************************
5020 * Shrikant Nene - 02/10/2003 B2792583 *
5021 * Changed the reference of pm_matl_dt to pm_matl_dtl_bak *
5022 * in the following procedure *
5023 *****************************************************************/
5024
5025 PROCEDURE renumber_duplicate_line_no IS
5026
5027 CURSOR cur_get_dup_line_no IS
5028 SELECT batch_id, line_type, line_no
5029 FROM pm_matl_dtl_bak
5030 GROUP BY batch_id, line_type, line_no
5031 HAVING COUNT(1) > 1;
5032
5033 CURSOR get_matl (v_batch_id NUMBER, v_line_type NUMBER) IS
5034 SELECT line_id
5035 FROM pm_matl_dtl_bak
5036 WHERE batch_id = v_batch_id
5037 AND line_type = v_line_type
5038 ORDER BY line_no asc;
5039
5040 l_line_no NUMBER := 0;
5041 l_dup_rec cur_get_dup_line_no%ROWTYPE;
5042
5043 l_dup_no NUMBER := 0;
5044 l_pos NUMBER := 0;
5045
5046 BEGIN
5047 l_pos := 1;
5048
5049 l_dup_no := 0;
5050
5051 OPEN cur_get_dup_line_no;
5052 l_pos := 2;
5053 FETCH cur_get_dup_line_no INTO l_dup_rec;
5054 l_pos := 3;
5055
5056 WHILE cur_get_dup_line_no%FOUND LOOP
5057 l_pos := 4;
5058
5059 insert_message (
5060 p_table_name => 'pm_matl_dtl',
5061 p_procedure_name => 'renumber_duplicate_line_no',
5062 p_parameters => 'batch_id = '||l_dup_rec.batch_id||
5063 ' line_type = '||l_dup_rec.line_type||
5064 ' line_no = '||l_dup_rec.line_no,
5065 p_message => 'Found batch with duplicate batch_id, line_type, line_no',
5066 p_error_type => 'I'
5067 );
5068
5069 l_pos := 4.1;
5070
5071 l_dup_no := l_dup_no + 1;
5072
5073 l_pos := 5;
5074 l_line_no := 1;
5075 l_pos := 6;
5076 FOR rec IN get_matl(l_dup_rec.batch_id, l_dup_rec.line_type) LOOP
5077 l_pos := 7;
5078
5079 UPDATE pm_matl_dtl_bak
5080 SET line_no = l_line_no
5081 WHERE line_id = rec.line_id;
5082
5083 l_pos := 8;
5084 l_line_no := l_line_no + 1;
5085 l_pos := 9;
5086 END LOOP;
5087
5088 l_pos := 10;
5089 FETCH cur_get_dup_line_no INTO l_dup_rec;
5090 l_pos := 11;
5091
5092 END LOOP;
5093
5094 l_pos := 12;
5095 CLOSE cur_get_dup_line_no;
5096
5097 l_pos := 13;
5098
5099 insert_message (
5100 p_table_name => 'pm_matl_dtl',
5101 p_procedure_name => 'renumber_duplicate_line_no',
5102 p_parameters => 'none',
5103 p_message => 'Number of batches to renumber for duplicate batch_id/line_type/line_no = ' || l_dup_no,
5104 p_error_type => 'P'
5105 );
5106
5107 EXCEPTION
5108 WHEN OTHERS THEN
5109 insert_message (
5110 p_table_name => 'pm_matl_dtl',
5111 p_procedure_name => 'renumber_duplicate_line_no',
5112 p_parameters => 'none',
5113 p_message => SQLERRM || ' with pos = ' || l_pos,
5114 p_error_type => 'D'
5115 );
5116 END renumber_duplicate_line_no;
5117
5118 PROCEDURE renumber_blank_line_no IS
5119
5120 CURSOR get_blank_line_batches IS
5121 SELECT d.batch_id, line_type, max(line_no) max_line_no,
5122 count(1) line_count
5123 FROM gme_material_details d, gme_batch_header b
5124 WHERE d.batch_id=b.batch_id
5125 AND batch_status in (1,2,3)
5126 AND batch_type = 0
5127 GROUP BY d.batch_id, line_type
5128 HAVING max(line_no) <> count(1);
5129
5130 CURSOR get_matl (v_batch_id NUMBER, v_line_type NUMBER) IS
5131 SELECT material_detail_id
5132 FROM gme_material_details
5133 WHERE batch_id = v_batch_id
5134 AND line_type = v_line_type
5135 ORDER BY line_no asc;
5136
5137 l_line_no NUMBER := 0;
5138 l_get_bl_batches get_blank_line_batches%ROWTYPE;
5139
5140 l_pos NUMBER := 0;
5141 l_dup_no NUMBER := 0;
5142
5143 BEGIN
5144
5145 l_pos := 1;
5146 l_dup_no := 0;
5147
5148 OPEN get_blank_line_batches;
5149 l_pos := 2;
5150 FETCH get_blank_line_batches INTO l_get_bl_batches;
5151 l_pos := 3;
5152 WHILE get_blank_line_batches%FOUND LOOP
5153 l_pos := 4;
5154 insert_message (
5155 p_table_name => 'gme_material_details',
5156 p_procedure_name => 'renumber_blank_line_no',
5157 p_parameters => 'batch_id = '||l_get_bl_batches.batch_id||
5158 ' line_type = '||l_get_bl_batches.line_type||
5159 ' max_line_no = '||
5160 l_get_bl_batches.max_line_no ||
5161 ' line_count = '||l_get_bl_batches.line_count,
5162 p_message => 'Found batch that required renumbering',
5163 p_error_type => 'I'
5164 );
5165
5166 l_dup_no := l_dup_no + 1;
5167 l_pos := 5;
5168 l_line_no := 1;
5169 l_pos := 6;
5170 FOR rec IN get_matl(l_get_bl_batches.batch_id,
5171 l_get_bl_batches.line_type) LOOP
5172 l_pos := 7;
5173
5174 UPDATE gme_material_details
5175 SET line_no = l_line_no
5176 WHERE material_detail_id = rec.material_detail_id;
5177
5178 l_pos := 8;
5179 l_line_no := l_line_no + 1;
5180 l_pos := 9;
5181 END LOOP;
5182
5183 l_pos := 10;
5184 FETCH get_blank_line_batches INTO l_get_bl_batches;
5185 l_pos := 11;
5186 END LOOP;
5187
5188 l_pos := 12;
5189 insert_message (
5190 p_table_name => 'gme_material_details',
5191 p_procedure_name => 'renumber_blank_line_no',
5192 p_parameters => 'none',
5193 p_message => 'Number of batches to renumber for blank line_no = ' || l_dup_no,
5194 p_error_type => 'P'
5195 );
5196
5197 EXCEPTION
5198 WHEN OTHERS THEN
5199 insert_message (
5200 p_table_name => 'gme_material_details',
5201 p_procedure_name => 'renumber_blank_line_no',
5202 p_parameters => 'none',
5203 p_message => SQLERRM || ' with pos = ' || l_pos,
5204 p_error_type => 'D'
5205 );
5206 END renumber_blank_line_no;
5207
5208 END migrate_batch;