[Home] [Help]
PACKAGE BODY: APPS.BOM_COPY_ROUTING
Source
1 PACKAGE BODY bom_copy_routing AS
2 /* $Header: BOMCPYRB.pls 120.14 2008/02/20 06:51:08 jiabraha ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1995 Oracle Corporation, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMCPYRB.pls |
9 | Description : Routing copy package |
10 | Created By : Manu Chadha |
11 | |
12 | from_org_id Copy from org id |
13 | to_org_id Copy to org id |
14 | from_sequence_id Copy from routing sequence id |
15 | to_sequence_id Copy to routing sequence id |
16 | display_option copy option |
17 | 1 - all (not supported from form) |
18 | 2 - current |
19 | 3 - current + future |
20 | user_id user id |
21 | to_item_id Copy to item id |
22 | direction direction of copy |
23 | 1 - BOM to BOM |
24 | 2 - BOM to ENG |
25 | 3 - ENG to ENG |
26 | 2 - ENG to BOM |
27 | to_alternate Copy to alternate designator |
28 | rev_date Revision date to copy |
29 | err_msg Error message |
30 | error_code Error code |
31 | |
32 | 30-Jun-2005 Ezhilarasan Added new overloaded procedure to support |
33 | copy routing in context of eco |
34 +==========================================================================*/
35 PROCEDURE rtg_get_msg_info (
36 total_opseqs IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
37 total_resources IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
38 total_sub_resources IN OUT NOCOPY /* file.sql.39 change */ NUMBER, --2991810
39 total_instructions IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
40 total_hdr_instructions IN OUT NOCOPY /* file.sql.39 change */ NUMBER, --bug 3473851
41 from_rtg_seq_id IN NUMBER,
42 rev_date IN DATE,
43 display_option IN NUMBER
44 )
45 IS
46 err_msg VARCHAR2 (2000);
47 sql_stmt_num NUMBER;
48 BEGIN
49 sql_stmt_num := 1;
50
51 SELECT COUNT (*)
52 INTO total_opseqs
53 FROM bom_operation_sequences
54 WHERE routing_sequence_id = from_rtg_seq_id
55 AND NVL (eco_for_production, 2) = 2
56 AND (display_option = 1
57 OR (display_option = 2
58 AND effectivity_date <= rev_date
59 AND NVL (disable_date, rev_date) >= rev_date
60 )
61 OR (display_option = 3
62 AND ((effectivity_date <= rev_date
63 AND NVL (disable_date, rev_date) >= rev_date
64 )
65 OR effectivity_date >= rev_date
66 )
67 )
68 );
69
70 sql_stmt_num := 2;
71
72 SELECT COUNT (*)
73 INTO total_resources
74 FROM bom_operation_sequences a,
75 bom_operation_resources b
76 WHERE a.routing_sequence_id = from_rtg_seq_id
77 AND NVL (a.eco_for_production, 2) = 2
78 AND (display_option = 1
79 OR (display_option = 2
80 AND a.effectivity_date <= rev_date
81 AND NVL (a.disable_date, rev_date + 1) > rev_date
82 )
83 OR (display_option = 3
84 AND ((a.effectivity_date <= rev_date
85 AND NVL (a.disable_date, rev_date + 1) > rev_date
86 )
87 OR a.effectivity_date > rev_date
88 )
89 )
90 )
91 AND a.operation_sequence_id = b.operation_sequence_id;
92
93 -- Bug Fix 2991810
94 --bug 3853743 added distinct clause
95 sql_stmt_num := 4;
96
97 SELECT DISTINCT COUNT (*)
98 INTO total_sub_resources
99 FROM bom_operation_sequences a,
100 bom_operation_resources b,
101 bom_sub_operation_resources c
102 WHERE a.routing_sequence_id = from_rtg_seq_id
103 AND NVL (a.eco_for_production, 2) = 2
104 AND (display_option = 1
105 OR (display_option = 2
106 AND a.effectivity_date <= rev_date
107 AND NVL (a.disable_date, rev_date + 1) > rev_date
108 )
109 OR (display_option = 3
110 AND ((a.effectivity_date <= rev_date
111 AND NVL (a.disable_date, rev_date + 1) >
112 rev_date
113 )
114 OR a.effectivity_date > rev_date
115 )
116 )
117 )
118 AND a.operation_sequence_id = b.operation_sequence_id
119 AND b.operation_sequence_id = c.operation_sequence_id
120 AND b.schedule_seq_num = c.schedule_seq_num;
121
122 -- Bug Fix 2991810
123 sql_stmt_num := 3;
124
125 SELECT COUNT (*)
126 INTO total_instructions
127 FROM bom_operation_sequences a,
128 fnd_attached_documents b
129 WHERE a.routing_sequence_id = from_rtg_seq_id
130 AND NVL (a.eco_for_production, 2) = 2
131 AND (display_option = 1
132 OR (display_option = 2
133 AND a.effectivity_date <= rev_date
134 AND NVL (a.disable_date, rev_date + 1) > rev_date
135 )
136 OR (display_option = 3
137 AND ((a.effectivity_date <= rev_date
138 AND NVL (a.disable_date, rev_date + 1) > rev_date
139 )
140 OR a.effectivity_date > rev_date
141 )
142 )
143 )
144 AND a.operation_sequence_id = b.pk1_value
145 AND b.entity_name = 'BOM_OPERATION_SEQUENCES';
146
147 --begin bug fix 3473851
148 sql_stmt_num := 5;
149
150 SELECT COUNT (*)
151 INTO total_hdr_instructions
152 FROM bom_operational_routings a,
153 fnd_attached_documents b
154 WHERE a.routing_sequence_id = from_rtg_seq_id
155 AND a.routing_sequence_id = b.pk1_value
156 AND b.entity_name = 'BOM_OPERATIONAL_ROUTINGS';
157 --end bug fix 3473851
158 EXCEPTION
159 WHEN OTHERS
160 THEN
161 err_msg := 'RTG_GET_MSG_INFO (' || sql_stmt_num || ') ' || SQLERRM;
162 fnd_message.set_name ('BOM', 'BOM_SQL_ERR');
163 fnd_message.set_token ('ENTITY', err_msg);
164 ROLLBACK TO begin_routing_copy;
165 app_exception.raise_exception;
166 END rtg_get_msg_info;
167
168 PROCEDURE copy_routing (
169 to_sequence_id IN NUMBER,
170 from_sequence_id IN NUMBER,
171 from_org_id IN NUMBER,
172 to_org_id IN NUMBER,
173 display_option IN NUMBER DEFAULT 2,
174 user_id IN NUMBER DEFAULT -1,
175 to_item_id IN NUMBER,
176 direction IN NUMBER,
177 to_alternate IN VARCHAR2,
178 rev_date DATE
179 )
180 IS
181 BEGIN
182 copy_routing
183 (to_sequence_id,
184 from_sequence_id,
185 from_org_id,
186 to_org_id,
187 display_option,
188 user_id,
189 to_item_id,
190 direction,
191 to_alternate,
192 rev_date,
193 NULL, -- Change Notice
194 NULL, -- Revised Item Sequence Id
195 1, -- routing_or_eco ( Routing always for the existing flows )
196 -- Existing routing copy will copied to sysdate
197 SYSDATE, -- Targtet Effectivity Date Bug 4863227
198 NULL, -- Eco Effectivity Date
199 NULL, -- Context ECO in which the copy occurs
200 NULL, -- no need to log errors
201 NULL, -- Request id will be null, this is not from TTMO
202 'N'
203 );
204 END;
205
206 PROCEDURE copy_routing (
207 to_sequence_id IN NUMBER,
208 from_sequence_id IN NUMBER,
209 from_org_id IN NUMBER,
210 to_org_id IN NUMBER,
211 display_option IN NUMBER DEFAULT 2,
212 user_id IN NUMBER DEFAULT -1,
213 to_item_id IN NUMBER,
214 direction IN NUMBER,
215 to_alternate IN VARCHAR2,
216 rev_date DATE,
217 p_e_change_notice IN VARCHAR2,
218 p_rev_item_seq_id IN NUMBER,
219 p_routing_or_eco IN NUMBER DEFAULT 1,
220 p_trgt_eff_date IN DATE,
221 p_eco_eff_date IN DATE,
222 p_context_eco IN VARCHAR2,
223 p_log_errors IN VARCHAR2 DEFAULT 'N',
224 p_copy_request_id IN NUMBER DEFAULT NULL,
225 p_cpy_disable_fields IN VARCHAR2 DEFAULT 'N'
226 )
227 IS
228 x_from_sequence_id NUMBER := from_sequence_id;
229 -- X_rev_date date := trunc(rev_date); -- Removed for bug 2647027
230 total_opseqs NUMBER := 0;
231 total_resources NUMBER := 0;
232 total_sub_resources NUMBER := 0;
233 total_instructions NUMBER := 0;
234 total_hdr_instructions NUMBER := 0;
235 hour_uom_code_v VARCHAR2 (3);
236 hour_uom_class_v VARCHAR2 (10);
237 sql_stmt_num NUMBER;
238 err_msg VARCHAR2 (2000);
239 copy_resources NUMBER := 0;
240 copy_sub_resources NUMBER := 0;
241 copy_instrs NUMBER;
242 copy_hdr_instrs NUMBER;
243 copy_operations NUMBER;
244 p_op_seq_id NUMBER;
245 p_op_seq_num NUMBER;
246 new_p_op_seq_id NUMBER;
247 l_op_seq_id NUMBER;
248 l_op_seq_num NUMBER;
249 new_l_op_seq_id NUMBER;
250 l_curr_date DATE; -- Added for bug 2718955
251 -- Bug fix 3473802
252 p_st_op_id NUMBER;
253 new_st_op_id NUMBER;
254 min_qty NUMBER;
255 back_flag NUMBER;
256 opt_flag NUMBER;
257 count_type NUMBER;
258 opr_desc VARCHAR2 (240);
259 copy_ops_update NUMBER;
260
261 CURSOR source_rtg
262 IS
263 SELECT operation_sequence_id,
264 last_updated_by
265 FROM bom_operation_sequences
266 WHERE routing_sequence_id = to_sequence_id
267 AND NVL (eco_for_production, 2) = 2;
268
269 CURSOR process_op
270 IS
271 SELECT operation_sequence_id,
272 operation_seq_num
273 FROM bom_operation_sequences
274 WHERE routing_sequence_id = x_from_sequence_id -- Bug 2642427
275 AND NVL (eco_for_production, 2) = 2
279 IS
276 AND operation_type = 2;
277
278 CURSOR line_op
280 SELECT operation_sequence_id,
281 operation_seq_num
282 FROM bom_operation_sequences
283 WHERE routing_sequence_id = x_from_sequence_id -- Bug 2642427
284 AND NVL (eco_for_production, 2) = 2
285 AND operation_type = 3;
286
287 -- Cursor Bug fix 3473802
288 CURSOR update_st_op
289 IS
290 SELECT standard_operation_id,
291 operation_sequence_id
292 FROM bom_operation_sequences
293 WHERE routing_sequence_id = to_sequence_id;
294 l_from_item_id NUMBER;
295 BEGIN
296 SAVEPOINT begin_routing_copy;
297 sql_stmt_num := 1;
298 fnd_profile.get ('BOM:HOUR_UOM_CODE', hour_uom_code_v);
299
300 BEGIN
301 SELECT uom_class
302 INTO hour_uom_class_v
303 FROM mtl_units_of_measure
304 WHERE uom_code = hour_uom_code_v;
305 EXCEPTION
306 WHEN OTHERS
307 THEN
308 NULL;
309 END;
310
311 sql_stmt_num := 10;
312
313 SELECT common_routing_sequence_id
314 INTO x_from_sequence_id
315 FROM bom_operational_routings
316 WHERE routing_sequence_id = from_sequence_id;
317
318 IF (from_org_id <> to_org_id)
319 THEN
320 rtg_get_msg_info (total_opseqs,
321 total_resources,
322 total_sub_resources, -- 2991810
323 total_instructions,
324 total_hdr_instructions, --bug 3473851
325 x_from_sequence_id,
326 rev_date,
327 display_option
328 );
329 END IF;
330
331 --copy operations
332 --null out std op id, operation_offset_%
333 --do not copy operations where department does not exist in to org
334 --if dept_id is diff in to org, reset dept id
335 -- For flow routings, we need to update the process_op_seq_id
336 -- and line_op_seq_id with the new values generated.
337 sql_stmt_num := 15;
338 IF p_trgt_eff_date IS NULL THEN
339 l_curr_date := SYSDATE;
340 ELSE
341 l_curr_date := p_trgt_eff_date; -- Routing can be copied to particular from TTMO flow - R12
342 END IF;
343
344 INSERT INTO bom_operation_sequences
345 (operation_sequence_id,
346 routing_sequence_id,
347 operation_seq_num,
348 last_update_date,
349 last_updated_by,
350 creation_date,
351 created_by,
352 last_update_login,
353 standard_operation_id,
354 department_id,
355 operation_lead_time_percent,
356 minimum_transfer_quantity,
357 count_point_type,
358 operation_description,
359 effectivity_date,
360 disable_date,
361 backflush_flag,
362 option_dependent_flag,
363 attribute_category,
364 attribute1,
365 attribute2,
366 attribute3,
367 attribute4,
368 attribute5,
369 attribute6,
370 attribute7,
371 attribute8,
372 attribute9,
373 attribute10,
374 attribute11,
375 attribute12,
376 attribute13,
377 attribute14,
378 attribute15,
379 request_id,
380 program_application_id,
381 program_id,
382 program_update_date,
383 operation_type,
384 reference_flag,
385 process_op_seq_id,
386 line_op_seq_id,
387 yield,
388 cumulative_yield,
389 reverse_cumulative_yield,
390 labor_time_calc,
391 machine_time_calc,
392 total_time_calc,
393 labor_time_user,
394 machine_time_user,
395 total_time_user,
396 net_planning_percent,
397 x_coordinate,
398 y_coordinate,
399 include_in_rollup,
400 operation_yield_enabled,
401 old_operation_sequence_id,
402 acd_type,
403 revised_item_sequence_id,
404 original_system_reference,
405 change_notice,
406 implementation_date,
407 eco_for_production,
408 shutdown_type,
409 -- Added by MK 04/10/2001
410 long_description, -- Added for bug 2767630
414 run_mandatory_flag,
411 lowest_acceptable_yield, -- Added for MES Enhancement
412 use_org_settings,
413 queue_mandatory_flag,
415 to_move_mandatory_flag,
416 show_next_op_by_default,
417 show_scrap_code,
418 show_lot_attrib,
419 track_multiple_res_usage_dates
420 )
421 SELECT bom_operation_sequences_s.NEXTVAL,
422 to_sequence_id,
423 a.operation_seq_num,
424 l_curr_date,
425 a.operation_sequence_id,
426 l_curr_date,
427 user_id,
428 user_id,
429 a.standard_operation_id,
430 c.department_id,
431 NULL,
432 a.minimum_transfer_quantity,
433 a.count_point_type,
434 a.operation_description,
435 -- Bug 2161841
436 -- GREATEST(A.EFFECTIVITY_DATE, l_curr_date), -- Changed for bug 2647027
437 CASE
438 WHEN display_option = 2 AND p_routing_or_eco = 2
439 THEN NVL(p_eco_eff_date,l_curr_date)
440 WHEN display_option = 2
441 THEN NVL(p_trgt_eff_date,l_curr_date)
442 -- For all don't check any effectivity, blindly copy
443 WHEN display_option = 1
444 THEN a.effectivity_date
445 WHEN p_routing_or_eco = 2 -- Added through ECO and explosion date is past and effectivity date
446 -- in the past
447 AND ( a.effectivity_date < p_eco_eff_date AND rev_date < p_eco_eff_date )
448 -- Explosion in the Past and Effectivity Date is also in the past, then the operations
449 -- which are past effective will be effective from p_eco_eff_date
450 THEN NVL(p_eco_eff_date,l_curr_date)
451 WHEN p_routing_or_eco = 2 -- Added through ECO and explosion date is future
452 AND ( a.effectivity_date = rev_date AND rev_date > p_eco_eff_date )
453 -- Explosion in the future and Effectivity Date is also in the future, then the operations
454 -- which are effective with that effective data will be effective from p_eco_eff_date
455 THEN NVL(p_eco_eff_date,l_curr_date)
456 -- Past effective operations should be target date effective
457 WHEN p_routing_or_eco = 2
458 AND a.effectivity_date < p_eco_eff_date
459 THEN NVL(p_eco_eff_date,l_curr_date)
460 WHEN p_routing_or_eco = 1 -- Inline and explosion date is past
461 AND ( a.effectivity_date < p_trgt_eff_date AND rev_date < p_trgt_eff_date )
462 -- Explosion in the Past and Effectivity Date is also in the past, then the operations
463 -- which are past effective will be effective from p_trgt_eff_date
464 THEN NVL(p_trgt_eff_date,l_curr_date)
465 WHEN p_routing_or_eco = 1 -- Inline and explosion date is future
466 AND ( a.effectivity_date = rev_date AND rev_date > p_trgt_eff_date )
467 -- Explosion in the future and Effectivity Date is also in the future, then the operations
468 -- which are effective at the explosion time alone will be effective from p_trgt_eff_date
469 THEN NVL(p_trgt_eff_date,l_curr_date)
470 -- Past effective components should be target data effective
471 WHEN p_routing_or_eco = 1
472 AND a.effectivity_date < p_trgt_eff_date
473 THEN NVL(p_trgt_eff_date,l_curr_date)
474 ELSE
475 a.effectivity_date
476 END AS effectivity_date,
477 CASE
478 -- This flag will be set when current and future option is selected with
479 -- copy through ECO
480 WHEN p_cpy_disable_fields = 'Y'
481 AND display_option = 2
482 AND p_routing_or_eco = 2
483 AND a.disable_date IS NOT NULL
484 AND a.disable_date > p_eco_eff_date
485 THEN a.disable_date
486 -- For current never disable the operations
487 WHEN display_option = 2
488 THEN TO_DATE (NULL)
489 -- Past disabled operations will be copied with disable date as null
490 WHEN p_routing_or_eco = 2 AND ( a.disable_date < p_eco_eff_date )
491 THEN TO_DATE (NULL)
492 -- Past disabled operations will be copied with disable date as null
493 WHEN p_routing_or_eco = 1 AND ( a.disable_date < p_trgt_eff_date )
494 THEN TO_DATE (NULL)
495 ELSE
496 -- Future disabled components should be disabled as per the disable date of component
497 a.disable_date
498 END AS disable_date,
499
500 /* Commented as part of R12 TTMO enhancement to support specific target eff date
501 DECODE (p_routing_or_eco,
502 1, DECODE (display_option,
503 1, a.effectivity_date,
504 GREATEST (a.effectivity_date, l_curr_date)
505 ),
506 p_eco_eff_date
507 ), -- Changed for bug 2788795
508 a.disable_date,*/
509 -- TRUNC(GREATEST(A.EFFECTIVITY_DATE, SYSDATE)),/* Bug: 1636829 */
510 -- TRUNC(A.DISABLE_DATE),
511 a.backflush_flag,
512 a.option_dependent_flag,
513 a.attribute_category,
514 a.attribute1,
515 a.attribute2,
516 a.attribute3,
517 a.attribute4,
518 a.attribute5,
522 a.attribute9,
519 a.attribute6,
520 a.attribute7,
521 a.attribute8,
523 a.attribute10,
524 a.attribute11,
525 a.attribute12,
526 a.attribute13,
527 a.attribute14,
528 a.attribute15,
529 fnd_global.conc_request_id,
530 NULL,
531 fnd_global.conc_program_id,
532 sysdate,
533 a.operation_type,
534 DECODE (from_org_id, to_org_id, a.reference_flag, 2),
535 -- Bug 3473802
536 a.process_op_seq_id,
537 a.line_op_seq_id,
538 a.yield,
539 a.cumulative_yield,
540 a.reverse_cumulative_yield,
541 a.labor_time_calc,
542 a.machine_time_calc,
543 a.total_time_calc,
544 a.labor_time_user,
545 a.machine_time_user,
546 a.total_time_user,
547 a.net_planning_percent,
548 a.x_coordinate,
549 a.y_coordinate,
550 a.include_in_rollup,
551 a.operation_yield_enabled,
552 a.old_operation_sequence_id,
553 DECODE (p_routing_or_eco, 1, a.acd_type, 1),
554 -- When it is ECO it is Add always
555 DECODE (p_routing_or_eco,
556 1, a.revised_item_sequence_id,
557 p_rev_item_seq_id
558 ),
559 a.original_system_reference,
560 DECODE (p_routing_or_eco, 1, a.change_notice, p_e_change_notice),
561 DECODE (p_routing_or_eco, 1, a.implementation_date, NULL),
562 -- When it is ECO populate NULL
563 a.eco_for_production,
564 a.shutdown_type,
565 -- Added by MK 04/10/2001
566 a.long_description,
567 a.lowest_acceptable_yield, -- Added for MES Enhancement
568 a.use_org_settings,
569 a.queue_mandatory_flag,
570 a.run_mandatory_flag,
571 a.to_move_mandatory_flag,
572 a.show_next_op_by_default,
573 a.show_scrap_code,
574 a.show_lot_attrib,
575 a.track_multiple_res_usage_dates
576 FROM bom_operation_sequences a, -- from op
577 bom_departments b, -- from op's dept
578 bom_departments c -- to op's dept
579 WHERE a.routing_sequence_id = x_from_sequence_id
580 AND NVL (a.eco_for_production, 2) = 2
581 AND (display_option = 1 /* ALL */
582 OR (display_option = 2 /* CURRENT */
583 AND a.effectivity_date <= rev_date
584 -- Bug 2161841
585 AND ((a.disable_date >= rev_date
586 AND a.disable_date >= l_curr_date
587 )
588 OR a.disable_date IS NULL
589 )
590 )
591 OR (display_option = 3 /* CURRENT_FUTURE */
592 AND ((a.effectivity_date <= rev_date
593 -- Bug 2161841
594 AND ((a.disable_date >= rev_date
595 AND a.disable_date >= l_curr_date
596 )
597 OR a.disable_date IS NULL
598 )
599 )
600 OR a.effectivity_date >= rev_date
601 )
602 )
603 )
604 AND a.department_id = b.department_id
605 AND b.department_code = c.department_code
606 -- comparing departments with same name
607 AND c.organization_id = to_org_id
608 AND NVL (c.disable_date, l_curr_date + 1) > l_curr_date
609 -- AND A.IMPLEMENTATION_DATE IS NOT NULL ; /* Bug 2717982 */
610 AND (a.implementation_date IS NOT NULL
611 OR (a.implementation_date IS NULL
612 AND a.change_notice = p_context_eco
613 AND ( a.acd_type = 1 OR a.acd_type = 2 )
614 )
615 )
616 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
617 (
618 SELECT 1
619 FROM bom_operation_sequences bos
620 WHERE bos.routing_sequence_id = a.routing_sequence_id
621 AND bos.old_operation_sequence_id = a.operation_sequence_id
622 AND bos.change_notice = p_context_eco
623 AND bos.acd_type = 3
624 AND bos.effectivity_date <= p_trgt_eff_date
625 AND bos.implementation_date IS NULL
626 );
627
628 IF p_log_errors = 'Y' THEN
629 -- There should not be any no data found for this case
630 SELECT
631 assembly_item_id INTO l_from_item_id
632 FROM
633 bom_operational_routings bor
634 WHERE
635 bor.routing_sequence_id = from_sequence_id;
639 transaction_id,
636 INSERT INTO mtl_interface_errors
637 (unique_id,
638 organization_id,
640 table_name,
641 column_name,
642 error_message,
643 bo_identifier,
644 last_update_date,
645 last_updated_by,
646 creation_date,
647 created_by,
648 message_type,
649 request_id,
650 program_application_id,
651 program_id,
652 program_update_date
653 )
654 SELECT
655 l_from_item_id,
656 to_org_id,
657 p_copy_request_id,
658 NULL,
659 NULL,
660 bom_copy_routing.get_message('BOM_CE_RTG_DEPT_NOT_EXISTS',
661 a.operation_seq_num, b.department_code,'DEP'),
662 'BOM_COPY',
663 sysdate,
664 user_id,
665 sysdate,
666 user_id,
667 'E',
668 fnd_global.conc_request_id,
669 NULL,
670 fnd_global.conc_program_id,
671 sysdate
672 FROM bom_operation_sequences a, -- from op
673 bom_departments b, -- from op's dept
674 bom_departments c -- to op's dept
675 WHERE a.routing_sequence_id = x_from_sequence_id
676 AND (display_option = 1 /* ALL */
677 OR (display_option = 2 /* CURRENT */
678 AND a.effectivity_date <= rev_date
679 -- Bug 2161841
680 AND ((a.disable_date >= rev_date
681 AND a.disable_date >= l_curr_date
682 )
683 OR a.disable_date IS NULL
684 )
685 )
686 OR (display_option = 3 /* CURRENT_FUTURE */
687 AND ((a.effectivity_date <= rev_date
688 -- Bug 2161841
689 AND ((a.disable_date >= rev_date
690 AND a.disable_date >= l_curr_date
691 )
692 OR a.disable_date IS NULL
693 )
694 )
695 OR a.effectivity_date >= rev_date
696 )
697 )
698 )
699 AND a.department_id = b.department_id
700 AND b.department_code = c.department_code (+)
701 -- comparing departments with same name
702 AND c.organization_id = to_org_id
703 AND NVL (c.disable_date, l_curr_date + 1) > l_curr_date
704 AND (a.implementation_date IS NOT NULL
705 OR (a.implementation_date IS NULL
706 AND a.change_notice = p_context_eco
707 AND ( a.acd_type = 1 OR a.acd_type = 2 )
708 )
709 )
710 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
711 (
712 SELECT 1
713 FROM bom_operation_sequences bos
714 WHERE bos.routing_sequence_id = a.routing_sequence_id
715 AND bos.old_operation_sequence_id = a.operation_sequence_id
716 AND bos.change_notice = p_context_eco
717 AND bos.acd_type = 3
718 AND bos.effectivity_date <= p_trgt_eff_date
719 AND bos.implementation_date IS NULL
720 )
721 MINUS -- Filter the departments for which the match is found
722 SELECT
723 l_from_item_id,
724 to_org_id,
725 p_copy_request_id,
726 NULL,
727 NULL,
728 bom_copy_routing.get_message('BOM_CE_RTG_DEPT_NOT_EXISTS',
729 a.operation_seq_num, b.department_code,'DEP'),
730 'BOM_COPY',
731 sysdate,
732 user_id,
733 sysdate,
734 user_id,
735 'E',
736 fnd_global.conc_request_id,
737 NULL,
738 fnd_global.conc_program_id,
739 sysdate
740 FROM bom_operation_sequences a, -- from op
741 bom_departments b, -- from op's dept
742 bom_departments c -- to op's dept
743 WHERE a.routing_sequence_id = x_from_sequence_id
744 AND (display_option = 1 /* ALL */
745 OR (display_option = 2 /* CURRENT */
746 AND a.effectivity_date <= rev_date
747 -- Bug 2161841
748 AND ((a.disable_date >= rev_date
749 AND a.disable_date >= l_curr_date
750 )
751 OR a.disable_date IS NULL
752 )
753 )
754 OR (display_option = 3 /* CURRENT_FUTURE */
755 AND ((a.effectivity_date <= rev_date
756 -- Bug 2161841
757 AND ((a.disable_date >= rev_date
761 )
758 AND a.disable_date >= l_curr_date
759 )
760 OR a.disable_date IS NULL
762 )
763 OR a.effectivity_date >= rev_date
764 )
765 )
766 )
767 AND a.department_id = b.department_id
768 AND b.department_code = c.department_code
769 -- comparing departments with same name
770 AND c.organization_id = to_org_id
771 AND NVL (c.disable_date, l_curr_date + 1) > l_curr_date
772 AND (a.implementation_date IS NOT NULL
773 OR (a.implementation_date IS NULL
774 AND a.change_notice = p_context_eco
775 AND ( a.acd_type = 1 OR a.acd_type = 2 )
776 )
777 )
778 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
779 (
780 SELECT 1
781 FROM bom_operation_sequences bos
782 WHERE bos.routing_sequence_id = a.routing_sequence_id
783 AND bos.old_operation_sequence_id = a.operation_sequence_id
784 AND bos.change_notice = p_context_eco
785 AND bos.acd_type = 3
786 AND bos.effectivity_date <= p_trgt_eff_date
787 AND bos.implementation_date IS NULL
788 );
789 INSERT INTO mtl_interface_errors
790 (unique_id,
791 organization_id,
792 transaction_id,
793 table_name,
794 column_name,
795 error_message,
796 bo_identifier,
797 last_update_date,
798 last_updated_by,
799 creation_date,
800 created_by,
801 message_type,
802 request_id,
803 program_application_id,
804 program_id,
805 program_update_date
806 )
807 SELECT
808 l_from_item_id,
809 to_org_id,
810 p_copy_request_id,
811 NULL,
812 NULL,
813 bom_copy_routing.get_message('BOM_CE_RTG_OPER_FOR_WIP_JOB',a.operation_seq_num),
814 'BOM_COPY',
815 sysdate,
816 user_id,
817 sysdate,
818 user_id,
819 'E',
820 fnd_global.conc_request_id,
821 NULL,
822 fnd_global.conc_program_id,
823 sysdate
824 FROM bom_operation_sequences a
825 WHERE a.routing_sequence_id = x_from_sequence_id
826 AND (display_option = 1 /* ALL */
827 OR (display_option = 2 /* CURRENT */
828 AND a.effectivity_date <= rev_date
829 -- Bug 2161841
830 AND ((a.disable_date >= rev_date
831 AND a.disable_date >= l_curr_date
832 )
833 OR a.disable_date IS NULL
834 )
835 )
836 OR (display_option = 3 /* CURRENT_FUTURE */
837 AND ((a.effectivity_date <= rev_date
838 -- Bug 2161841
839 AND ((a.disable_date >= rev_date
840 AND a.disable_date >= l_curr_date
841 )
842 OR a.disable_date IS NULL
843 )
844 )
845 OR a.effectivity_date >= rev_date
846 )
847 )
848 )
849 AND a.eco_for_production <> 2
850 AND (a.implementation_date IS NOT NULL
851 OR (a.implementation_date IS NULL
852 AND a.change_notice = p_context_eco
853 AND ( a.acd_type = 1 OR a.acd_type = 2 )
854 )
855 )
856 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
857 (
858 SELECT 1
859 FROM bom_operation_sequences bos
860 WHERE bos.routing_sequence_id = a.routing_sequence_id
861 AND bos.old_operation_sequence_id = a.operation_sequence_id
862 AND bos.change_notice = p_context_eco
863 AND bos.acd_type = 3
864 AND bos.effectivity_date <= p_trgt_eff_date
865 AND bos.implementation_date IS NULL
866 );
867 INSERT INTO mtl_interface_errors
868 (unique_id,
869 organization_id,
870 transaction_id,
871 table_name,
872 column_name,
873 error_message,
874 bo_identifier,
875 last_update_date,
876 last_updated_by,
877 creation_date,
878 created_by,
879 message_type,
880 request_id,
881 program_application_id,
882 program_id,
883 program_update_date
884 )
885 SELECT
886 l_from_item_id,
890 NULL,
887 to_org_id,
888 p_copy_request_id,
889 NULL,
891 bom_copy_routing.get_message('BOM_CE_RTG_OPER_NOT_IMPL',a.operation_seq_num),
892 'BOM_COPY',
893 sysdate,
894 user_id,
895 sysdate,
896 user_id,
897 'E',
898 fnd_global.conc_request_id,
899 NULL,
900 fnd_global.conc_program_id,
901 sysdate
902 FROM bom_operation_sequences a
903 WHERE a.routing_sequence_id = x_from_sequence_id
904 AND (display_option = 1 /* ALL */
905 OR (display_option = 2 /* CURRENT */
906 AND a.effectivity_date <= rev_date
907 -- Bug 2161841
908 AND ((a.disable_date >= rev_date
909 AND a.disable_date >= l_curr_date
910 )
911 OR a.disable_date IS NULL
912 )
913 )
914 OR (display_option = 3 /* CURRENT_FUTURE */
915 AND ((a.effectivity_date <= rev_date
916 -- Bug 2161841
917 AND ((a.disable_date >= rev_date
918 AND a.disable_date >= l_curr_date
919 )
920 OR a.disable_date IS NULL
921 )
922 )
923 OR a.effectivity_date >= rev_date
924 )
925 )
926 )
927 AND (a.implementation_date IS NULL
928 AND p_context_eco IS NULL);
929
930 END IF; /* IF p_log_errors = 'Y' */
931
932
933 copy_operations := SQL%ROWCOUNT;
934
935 IF (from_org_id = to_org_id)
936 THEN
937 total_opseqs := SQL%ROWCOUNT;
938 END IF;
939
940 -- Begin Bug fix 3473802
941 IF (from_org_id <> to_org_id)
942 THEN
943 OPEN update_st_op;
944
945 copy_ops_update := 0;
946
947 LOOP
948 FETCH update_st_op
949 INTO p_st_op_id,
950 p_op_seq_id;
951
952 EXIT WHEN update_st_op%NOTFOUND;
953
954 BEGIN
955 SELECT b.standard_operation_id,
956 b.minimum_transfer_quantity,
957 b.backflush_flag,
958 b.option_dependent_flag,
959 b.count_point_type,
960 b.operation_description
961 INTO new_st_op_id,
962 min_qty,
963 back_flag,
964 opt_flag,
965 count_type,
966 opr_desc
967 FROM bom_standard_operations_v a, -- BUG 3936049
968 bom_standard_operations_v b -- BUG 3936049
969 WHERE a.standard_operation_id = p_st_op_id
970 AND a.operation_code = b.operation_code
971 AND a.organization_id = from_org_id
972 AND b.organization_id = to_org_id
973 AND NVL (a.line_code, '@@@') = NVL (b.line_code, '@@@')
974 -- BUG 3936049
975 AND NVL (a.operation_type, -99) = NVL (b.operation_type,
976 -99); -- BUG 3936049
977
978 UPDATE bom_operation_sequences
979 SET standard_operation_id = new_st_op_id,
980 minimum_transfer_quantity = min_qty,
981 backflush_flag = back_flag,
982 option_dependent_flag = opt_flag,
983 count_point_type = count_type,
984 operation_description = opr_desc
985 WHERE routing_sequence_id = to_sequence_id
986 AND operation_sequence_id = p_op_seq_id;
987
988 copy_ops_update := copy_ops_update + 1;
989 EXCEPTION
990 WHEN NO_DATA_FOUND
991 THEN
992 UPDATE bom_operation_sequences
993 SET standard_operation_id = NULL
994 WHERE routing_sequence_id = to_sequence_id
995 AND operation_sequence_id = p_op_seq_id;
996 END;
997 END LOOP;
998 END IF;
999
1000 -- End Bug fix 3473802
1001 OPEN process_op;
1002
1003 LOOP
1004 FETCH process_op
1005 INTO p_op_seq_id,
1006 p_op_seq_num;
1007
1008 EXIT WHEN process_op%NOTFOUND;
1009
1010 BEGIN
1011 SELECT operation_sequence_id
1012 INTO new_p_op_seq_id
1013 FROM bom_operation_sequences
1014 WHERE routing_sequence_id = to_sequence_id
1015 AND operation_type = 2
1016 AND NVL (eco_for_production, 2) = 2
1017 AND operation_seq_num = p_op_seq_num;
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND
1023
1020 THEN
1021 NULL;
1022 END;
1024 UPDATE bom_operation_sequences
1025 SET process_op_seq_id = new_p_op_seq_id
1026 WHERE operation_type = 1
1027 AND routing_sequence_id = to_sequence_id
1028 AND process_op_seq_id = p_op_seq_id;
1029 END LOOP;
1030
1031 OPEN line_op;
1032
1033 LOOP
1034 FETCH line_op
1035 INTO l_op_seq_id,
1036 l_op_seq_num;
1037
1038 EXIT WHEN line_op%NOTFOUND;
1039
1040 BEGIN
1041 SELECT operation_sequence_id
1042 INTO new_l_op_seq_id
1043 FROM bom_operation_sequences
1044 WHERE routing_sequence_id = to_sequence_id
1045 AND operation_type = 3
1046 AND NVL (eco_for_production, 2) = 2
1047 AND operation_seq_num = l_op_seq_num;
1048 EXCEPTION
1049 WHEN NO_DATA_FOUND
1050 THEN
1051 NULL;
1052 END;
1053
1054 UPDATE bom_operation_sequences
1055 SET line_op_seq_id = new_l_op_seq_id
1056 WHERE operation_type = 1
1057 AND routing_sequence_id = to_sequence_id
1058 AND line_op_seq_id = l_op_seq_id;
1059 END LOOP;
1060
1061 INSERT INTO bom_operation_networks
1062 (from_op_seq_id,
1063 to_op_seq_id,
1064 transition_type,
1065 planning_pct,
1066 effectivity_date,
1067 disable_date,
1068 created_by,
1069 creation_date,
1070 last_updated_by,
1071 last_update_date,
1072 last_update_login,
1073 attribute_category,
1074 attribute1,
1075 attribute2,
1076 attribute3,
1077 attribute4,
1078 attribute5,
1079 attribute6,
1080 attribute7,
1081 attribute8,
1082 attribute9,
1083 attribute10,
1084 attribute11,
1085 attribute12,
1086 attribute13,
1087 attribute14,
1088 attribute15,
1089 request_id,
1090 program_application_id,
1091 program_id,
1092 program_update_date
1093 )
1094 SELECT bos3.operation_sequence_id,
1095 bos4.operation_sequence_id,
1096 bon.transition_type,
1097 bon.planning_pct,
1098 -- Operation Network effectivity will be effective from target's to operation,
1099 -- which will be greater than target's from operation
1100 bos4.effectivity_date,
1101 /*
1102 DECODE (p_routing_or_eco,
1103 1, bon.effectivity_date,
1104 p_eco_eff_date
1105 ),*/
1106 bon.disable_date,
1107 bon.created_by,
1108 bon.creation_date,
1109 bon.last_updated_by,
1110 bon.last_update_date,
1111 bon.last_update_login,
1112 bon.attribute_category,
1113 bon.attribute1,
1114 bon.attribute2,
1115 bon.attribute3,
1116 bon.attribute4,
1117 bon.attribute5,
1118 bon.attribute6,
1119 bon.attribute7,
1120 bon.attribute8,
1121 bon.attribute9,
1122 bon.attribute10,
1123 bon.attribute11,
1124 bon.attribute12,
1125 bon.attribute13,
1126 bon.attribute14,
1127 bon.attribute15,
1128 fnd_global.conc_request_id,
1129 NULL,
1130 fnd_global.conc_program_id,
1131 sysdate
1132 FROM bom_operation_networks bon,
1133 bom_operation_sequences bos4, -- dest to op
1134 bom_operation_sequences bos3, -- dest from op
1135 bom_operation_sequences bos2, -- src to op
1136 bom_operation_sequences bos1 -- src from op
1137 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
1138 AND bon.to_op_seq_id = bos2.operation_sequence_id
1139 AND bos1.routing_sequence_id = bos2.routing_sequence_id
1140 AND bos1.routing_sequence_id = x_from_sequence_id
1141 AND bos3.routing_sequence_id = to_sequence_id
1142 AND bos3.operation_seq_num = bos1.operation_seq_num
1143 -- AND greatest(bos1.effectivity_date, l_curr_date) = greatest(bos3.effectivity_date, l_curr_date) -- added for bug 2718955
1144 -- Just compare the last updated by which will have the from operation seq num
1145 -- If the operation is copied then we need to copy the network, the effectivity filter
1146 -- is already applied at the operation sequence level
1147 AND bos3.last_updated_by = bos1.operation_sequence_id
1148 AND bos4.last_updated_by = bos2.operation_sequence_id
1152 GREATEST (bos1.effectivity_date, l_curr_date)
1149 /* Commented as part of TTMO Enh R12
1150 AND DECODE (display_option,
1151 1, bos1.effectivity_date,
1153 ) =
1154 DECODE
1155 (display_option,
1156 1, bos3.effectivity_date,
1157 GREATEST (bos3.effectivity_date, l_curr_date)
1158 ) -- added for bug 2788795
1159 */
1160 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
1161 AND NVL (bos1.eco_for_production, 2) = 2
1162 AND NVL (bos2.eco_for_production, 2) = 2
1163 AND NVL (bos3.eco_for_production, 2) = 2
1164 AND NVL (bos4.eco_for_production, 2) = 2
1165 AND bos4.routing_sequence_id = to_sequence_id
1166 AND bos4.operation_seq_num = bos2.operation_seq_num
1167 -- Just compare the last updated by which will have the from operation seq num
1168 -- If the operation is copied then we need to copy the network, the effectivity filter
1169 -- is already applied at the operation sequence level
1170 /*
1171 AND DECODE (display_option,
1172 1, bos2.effectivity_date,
1173 GREATEST (bos2.effectivity_date, l_curr_date)
1174 ) =
1175 DECODE
1176 (display_option,
1177 1, bos4.effectivity_date,
1178 GREATEST (bos4.effectivity_date, l_curr_date)
1179 ) -- added for bug 2788795
1180 */
1181 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1);
1182
1183 IF p_log_errors = 'Y' THEN
1184 INSERT INTO mtl_interface_errors
1185 (unique_id,
1186 organization_id,
1187 transaction_id,
1188 table_name,
1189 column_name,
1190 error_message,
1191 bo_identifier,
1192 last_update_date,
1193 last_updated_by,
1194 creation_date,
1195 created_by,
1196 message_type,
1197 request_id,
1198 program_application_id,
1199 program_id,
1200 program_update_date
1201 )
1202 SELECT
1203 l_from_item_id,
1204 to_org_id,
1205 p_copy_request_id,
1206 NULL,
1207 NULL,
1208 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos1.operation_seq_num,mfgl.meaning),
1209 'BOM_COPY',
1210 sysdate,
1211 user_id,
1212 sysdate,
1213 user_id,
1214 'E',
1215 fnd_global.conc_request_id,
1216 NULL,
1217 fnd_global.conc_program_id,
1218 sysdate
1219 FROM bom_operation_networks bon,
1220 bom_operation_sequences bos4, -- dest to op
1221 bom_operation_sequences bos3, -- dest from op
1222 bom_operation_sequences bos2, -- src to op
1223 bom_operation_sequences bos1, -- src from op
1224 mfg_lookups mfgl
1225 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
1226 AND bon.to_op_seq_id = bos2.operation_sequence_id
1227 AND bos1.routing_sequence_id = bos2.routing_sequence_id
1228 AND bos1.routing_sequence_id = x_from_sequence_id
1229 AND bos3.routing_sequence_id = to_sequence_id
1230 AND bos3.operation_seq_num = bos1.operation_seq_num
1231 AND bos3.last_updated_by = bos1.operation_sequence_id
1232 AND bos4.last_updated_by = bos2.operation_sequence_id
1236 AND NVL (bos3.eco_for_production, 2) = 2
1233 AND bos3.operation_type(+) = bos1.operation_type
1234 AND NVL (bos1.eco_for_production, 2) = 2
1235 AND NVL (bos2.eco_for_production, 2) = 2
1237 AND NVL (bos4.eco_for_production, 2) = 2
1238 AND bos4.routing_sequence_id = to_sequence_id
1239 AND bos4.operation_seq_num = bos2.operation_seq_num
1240 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1)
1241 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
1242 AND mfgl.lookup_code = bos1.operation_type
1243 MINUS
1244 SELECT
1245 l_from_item_id,
1246 to_org_id,
1247 p_copy_request_id,
1248 NULL,
1249 NULL,
1250 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos1.operation_seq_num, mfgl.meaning),
1251 'BOM_COPY',
1252 sysdate,
1253 user_id,
1254 sysdate,
1255 user_id,
1256 'E',
1257 fnd_global.conc_request_id,
1258 NULL,
1259 fnd_global.conc_program_id,
1260 sysdate
1261 FROM bom_operation_networks bon,
1262 bom_operation_sequences bos4, -- dest to op
1263 bom_operation_sequences bos3, -- dest from op
1264 bom_operation_sequences bos2, -- src to op
1265 bom_operation_sequences bos1, -- src from op
1266 mfg_lookups mfgl
1267 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
1268 AND bon.to_op_seq_id = bos2.operation_sequence_id
1269 AND bos1.routing_sequence_id = bos2.routing_sequence_id
1270 AND bos1.routing_sequence_id = x_from_sequence_id
1271 AND bos3.routing_sequence_id = to_sequence_id
1272 AND bos3.operation_seq_num = bos1.operation_seq_num
1273 AND bos3.last_updated_by = bos1.operation_sequence_id
1274 AND bos4.last_updated_by = bos2.operation_sequence_id
1275 AND bos3.operation_type = bos1.operation_type
1276 AND NVL (bos1.eco_for_production, 2) = 2
1277 AND NVL (bos2.eco_for_production, 2) = 2
1278 AND NVL (bos3.eco_for_production, 2) = 2
1279 AND NVL (bos4.eco_for_production, 2) = 2
1280 AND bos4.routing_sequence_id = to_sequence_id
1281 AND bos4.operation_seq_num = bos2.operation_seq_num
1282 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1)
1283 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
1284 AND mfgl.lookup_code = bos1.operation_type;
1285 INSERT INTO mtl_interface_errors
1286 (unique_id,
1287 organization_id,
1288 transaction_id,
1289 table_name,
1290 column_name,
1291 error_message,
1292 bo_identifier,
1293 last_update_date,
1294 last_updated_by,
1295 creation_date,
1296 created_by,
1297 message_type,
1298 request_id,
1299 program_application_id,
1300 program_id,
1301 program_update_date
1302 )
1303 SELECT
1304 l_from_item_id,
1305 to_org_id,
1306 p_copy_request_id,
1307 NULL,
1308 NULL,
1309 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos2.operation_seq_num,mfgl.meaning),
1310 'BOM_COPY',
1311 sysdate,
1312 user_id,
1313 sysdate,
1314 user_id,
1315 'E',
1316 fnd_global.conc_request_id,
1317 NULL,
1318 fnd_global.conc_program_id,
1319 sysdate
1320 FROM bom_operation_networks bon,
1321 bom_operation_sequences bos4, -- dest to op
1322 bom_operation_sequences bos3, -- dest from op
1323 bom_operation_sequences bos2, -- src to op
1324 bom_operation_sequences bos1, -- src from op
1325 mfg_lookups mfgl
1326 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
1327 AND bon.to_op_seq_id = bos2.operation_sequence_id
1328 AND bos1.routing_sequence_id = bos2.routing_sequence_id
1329 AND bos1.routing_sequence_id = x_from_sequence_id
1333 AND bos4.last_updated_by = bos2.operation_sequence_id
1330 AND bos3.routing_sequence_id = to_sequence_id
1331 AND bos3.operation_seq_num = bos1.operation_seq_num
1332 AND bos3.last_updated_by = bos1.operation_sequence_id
1334 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
1335 AND NVL (bos1.eco_for_production, 2) = 2
1336 AND NVL (bos2.eco_for_production, 2) = 2
1337 AND NVL (bos3.eco_for_production, 2) = 2
1338 AND NVL (bos4.eco_for_production, 2) = 2
1339 AND bos4.routing_sequence_id = to_sequence_id
1340 AND bos4.operation_seq_num = bos2.operation_seq_num
1341 AND bos4.operation_type(+) = bos2.operation_type
1342 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
1343 AND mfgl.lookup_code = bos2.operation_type
1344 MINUS
1345 SELECT
1346 l_from_item_id,
1347 to_org_id,
1348 p_copy_request_id,
1349 NULL,
1350 NULL,
1351 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos2.operation_seq_num,mfgl.meaning),
1352 'BOM_COPY',
1353 sysdate,
1354 user_id,
1355 sysdate,
1356 user_id,
1357 'E',
1358 fnd_global.conc_request_id,
1359 NULL,
1360 fnd_global.conc_program_id,
1361 sysdate
1362 FROM bom_operation_networks bon,
1363 bom_operation_sequences bos4, -- dest to op
1364 bom_operation_sequences bos3, -- dest from op
1365 bom_operation_sequences bos2, -- src to op
1366 bom_operation_sequences bos1, -- src from op
1367 mfg_lookups mfgl
1368 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
1369 AND bon.to_op_seq_id = bos2.operation_sequence_id
1370 AND bos1.routing_sequence_id = bos2.routing_sequence_id
1371 AND bos1.routing_sequence_id = x_from_sequence_id
1372 AND bos3.routing_sequence_id = to_sequence_id
1373 AND bos3.operation_seq_num = bos1.operation_seq_num
1374 AND bos3.last_updated_by = bos1.operation_sequence_id
1375 AND bos4.last_updated_by = bos2.operation_sequence_id
1376 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
1377 AND NVL (bos1.eco_for_production, 2) = 2
1378 AND NVL (bos2.eco_for_production, 2) = 2
1379 AND NVL (bos3.eco_for_production, 2) = 2
1380 AND NVL (bos4.eco_for_production, 2) = 2
1381 AND bos4.routing_sequence_id = to_sequence_id
1382 AND bos4.operation_seq_num = bos2.operation_seq_num
1383 AND bos4.operation_type = bos2.operation_type
1384 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
1385 AND mfgl.lookup_code = bos2.operation_type;
1386
1387
1388 END IF; /* IF p_log_errors = 'Y' */
1389
1390 sql_stmt_num := 101;
1391
1392 BEGIN
1393 FOR x_op IN source_rtg
1394 LOOP
1395 sql_stmt_num := 201;
1396 fnd_attached_documents2_pkg.copy_attachments
1397 (x_from_entity_name => 'BOM_OPERATION_SEQUENCES',
1398 x_from_pk1_value => x_op.last_updated_by,
1399 x_from_pk2_value => '',
1400 x_from_pk3_value => '',
1401 x_from_pk4_value => '',
1402 x_from_pk5_value => '',
1403 x_to_entity_name => 'BOM_OPERATION_SEQUENCES',
1404 x_to_pk1_value => x_op.operation_sequence_id,
1405 x_to_pk2_value => '',
1406 x_to_pk3_value => '',
1407 x_to_pk4_value => '',
1408 x_to_pk5_value => '',
1409 x_created_by => user_id,
1410 x_last_update_login => '',
1411 x_program_application_id => '',
1412 x_program_id => fnd_global.conc_program_id,
1413 x_request_id => fnd_global.conc_request_id
1414 );
1415 sql_stmt_num := 301;
1416 END LOOP;
1417
1418 sql_stmt_num := 401;
1419 END;
1420
1421 --begin bug fix 3473851
1422 sql_stmt_num := 501;
1423 fnd_attached_documents2_pkg.copy_attachments
1424 (x_from_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
1425 x_from_pk1_value => x_from_sequence_id,
1426 x_from_pk2_value => '',
1427 x_from_pk3_value => '',
1428 x_from_pk4_value => '',
1429 x_from_pk5_value => '',
1430 x_to_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
1431 x_to_pk1_value => to_sequence_id,
1432 x_to_pk2_value => '',
1433 x_to_pk3_value => '',
1437 x_last_update_login => '',
1434 x_to_pk4_value => '',
1435 x_to_pk5_value => '',
1436 x_created_by => user_id,
1438 x_program_application_id => '',
1439 x_program_id => fnd_global.conc_program_id,
1440 x_request_id => fnd_global.conc_request_id
1441 );
1442 --end bug fix 3473851
1443
1444 --INSERT OPERATION RESOURCES
1445 --NULL OUT RESOURCE_OFFSET_PERCENT
1446 --SET ASSIGNED UNITS TO 1 IF RESOURCES IN DEPT IS 24 HRS -- removed restriction as per bug 2661684
1447 --UPDATE RESOURCE_ID TO THAT OF COPY_TO_ORG
1448 --LEAVE OUT RESOURCES THAT DO NOT EXIST IN COPY_TO_ORG DEPT.
1449 sql_stmt_num := 20;
1450
1451 INSERT INTO bom_operation_resources
1452 (operation_sequence_id,
1453 resource_seq_num,
1454 resource_id,
1455 activity_id,
1456 standard_rate_flag,
1457 assigned_units,
1458 usage_rate_or_amount,
1459 usage_rate_or_amount_inverse,
1460 basis_type,
1461 schedule_flag,
1462 last_update_date,
1463 last_updated_by,
1464 creation_date,
1465 created_by,
1466 last_update_login,
1467 resource_offset_percent,
1468 autocharge_type,
1469 attribute_category,
1470 attribute1,
1471 attribute2,
1472 attribute3,
1473 attribute4,
1474 attribute5,
1475 attribute6,
1476 attribute7,
1477 attribute8,
1478 attribute9,
1479 attribute10,
1480 attribute11,
1481 attribute12,
1482 attribute13,
1483 attribute14,
1484 attribute15,
1485 request_id,
1486 program_application_id,
1487 program_id,
1488 program_update_date,
1489 schedule_seq_num,
1490 substitute_group_num,
1491 principle_flag,
1492 setup_id,
1493 change_notice,
1494 acd_type,
1495 original_system_reference
1496 )
1497 SELECT a.operation_sequence_id,
1498 b.resource_seq_num,
1499 d.resource_id,
1500 b.activity_id,
1501 b.standard_rate_flag,
1502 -- DECODE(E.AVAILABLE_24_HOURS_FLAG, 1, 1, B.ASSIGNED_UNITS), -- changed for bug 2661684
1503 b.assigned_units,
1504 b.usage_rate_or_amount,
1505 b.usage_rate_or_amount_inverse,
1506 b.basis_type,
1507 b.schedule_flag,
1508 SYSDATE,
1509 b.operation_sequence_id, -- Instead of last_updated_by
1510 SYSDATE,
1511 NVL (b.schedule_seq_num, user_id), -- Instead of created by
1512 user_id,
1513 NULL,
1514 b.autocharge_type,
1515 b.attribute_category,
1516 b.attribute1,
1517 b.attribute2,
1518 b.attribute3,
1519 b.attribute4,
1520 b.attribute5,
1521 b.attribute6,
1522 b.attribute7,
1523 b.attribute8,
1524 b.attribute9,
1525 b.attribute10,
1526 b.attribute11,
1527 b.attribute12,
1528 b.attribute13,
1529 b.attribute14,
1530 b.attribute15,
1531 fnd_global.conc_request_id,
1532 NULL,
1533 fnd_global.conc_program_id,
1534 sysdate,
1535 b.schedule_seq_num,
1536 b.substitute_group_num,
1537 b.principle_flag,
1538 b.setup_id,
1539 DECODE (p_routing_or_eco, 1, b.change_notice, p_e_change_notice),
1540 DECODE (p_routing_or_eco, 1, b.acd_type, 1),
1541 -- Add is the action for ECO
1542 b.original_system_reference
1543 FROM bom_operation_sequences a,
1544 bom_operation_resources b,
1545 bom_resources c,
1546 bom_resources d
1547 -- ,BOM_DEPARTMENT_RESOURCES E
1548 WHERE a.routing_sequence_id = to_sequence_id
1549 AND a.last_updated_by = b.operation_sequence_id
1550 AND b.resource_id = c.resource_id
1551 AND c.resource_code = d.resource_code
1552 AND d.organization_id = to_org_id
1553 -- AND D.RESOURCE_ID = E.RESOURCE_ID
1554 -- AND E.DEPARTMENT_ID = A.DEPARTMENT_ID
1555 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE;
1556
1557 IF p_log_errors = 'Y' THEN
1561 transaction_id,
1558 INSERT INTO mtl_interface_errors
1559 (unique_id,
1560 organization_id,
1562 table_name,
1563 column_name,
1564 error_message,
1565 bo_identifier,
1566 last_update_date,
1567 last_updated_by,
1568 creation_date,
1569 created_by,
1570 message_type,
1571 request_id,
1572 program_application_id,
1573 program_id,
1574 program_update_date
1575 )
1576 SELECT
1577 l_from_item_id,
1578 to_org_id,
1579 p_copy_request_id,
1580 NULL,
1581 NULL,
1582 bom_copy_routing.get_message('BOM_CE_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code,'RES'),
1583 'BOM_COPY',
1584 sysdate,
1585 user_id,
1586 sysdate,
1587 user_id,
1588 'E',
1589 fnd_global.conc_request_id,
1590 NULL,
1591 fnd_global.conc_program_id,
1592 sysdate
1593 FROM bom_operation_sequences a,
1594 bom_operation_resources b,
1595 bom_operation_sequences fbor,
1596 bom_resources c,
1597 bom_resources d
1598 WHERE a.routing_sequence_id = to_sequence_id
1599 AND a.last_updated_by = b.operation_sequence_id
1600 AND b.resource_id = c.resource_id
1601 AND c.resource_code = d.resource_code(+)
1602 AND d.organization_id = to_org_id
1603 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
1604 AND fbor.operation_sequence_id = b.operation_sequence_id
1605 MINUS
1606 SELECT
1607 l_from_item_id,
1608 to_org_id,
1609 p_copy_request_id,
1610 NULL,
1611 NULL,
1612 bom_copy_routing.get_message('BOM_CE_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code,'RES'),
1613 'BOM_COPY',
1614 sysdate,
1615 user_id,
1616 sysdate,
1617 user_id,
1618 'E',
1619 fnd_global.conc_request_id,
1620 NULL,
1621 fnd_global.conc_program_id,
1622 sysdate
1623 FROM bom_operation_sequences a,
1624 bom_operation_resources b,
1625 bom_operation_sequences fbor,
1626 bom_resources c,
1627 bom_resources d
1628 WHERE a.routing_sequence_id = to_sequence_id
1629 AND a.last_updated_by = b.operation_sequence_id
1630 AND b.resource_id = c.resource_id
1631 AND c.resource_code = d.resource_code
1632 AND d.organization_id = to_org_id
1633 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
1634 AND fbor.operation_sequence_id = b.operation_sequence_id;
1635 END IF; /* IF p_log_errors = 'Y' */
1636
1637
1638 copy_resources := SQL%ROWCOUNT;
1639
1640 IF (from_org_id = to_org_id)
1641 THEN
1642 total_resources := SQL%ROWCOUNT;
1643 END IF;
1644
1645 -- UPDATE OPERATION RESOURCES TO
1646 -- IF ACTIVITY IS ORG DEPENDENT, NULL IT OUT
1647 -- IF NO CONVERSION TO HOUR UOM CLASS, SET SCHEDUEL FLAG TO NO
1648 -- If there exist a similar setup in the other org, copy the correct setup_id
1649 -- else null out the setup_id field -- bug 2751946
1650 -- ONLY FOR INTER ORG COPY
1651 IF (from_org_id <> to_org_id)
1652 THEN
1653 sql_stmt_num := 30;
1654
1655 UPDATE bom_operation_resources a
1656 SET activity_id =
1657 (SELECT DECODE (organization_id, NULL, activity_id, NULL)
1658 FROM cst_activities
1659 WHERE activity_id = a.activity_id),
1660 schedule_flag =
1661 (SELECT DECODE (c.unit_of_measure,
1662 NULL, 2,
1663 hour_uom_code_v, a.schedule_flag,
1664 DECODE (b.uom_class,
1665 hour_uom_class_v, a.schedule_flag,
1666 2
1667 )
1668 )
1669 FROM mtl_units_of_measure b,
1670 bom_resources c
1671 WHERE a.resource_id = c.resource_id
1672 AND c.unit_of_measure = b.unit_of_measure(+)),
1673 setup_id =
1674 (SELECT brs.setup_id
1675 FROM bom_resource_setups brs,
1676 bom_setup_types bst -- added for bug 2751946
1677 WHERE brs.resource_id = a.resource_id
1678 AND brs.setup_id = bst.setup_id
1679 AND bst.setup_code = (SELECT setup_code
1680 FROM bom_setup_types
1681 WHERE setup_id = a.setup_id))
1682 WHERE a.operation_sequence_id IN (
1683 SELECT operation_sequence_id
1684 FROM bom_operation_sequences
1688
1685 WHERE routing_sequence_id =
1686 to_sequence_id);
1687 END IF;
1689 -- Bug Fix 2991810
1690 sql_stmt_num := 25;
1691
1692 INSERT INTO bom_sub_operation_resources
1693 (operation_sequence_id,
1694 substitute_group_num,
1695 resource_id,
1696 schedule_seq_num,
1697 replacement_group_num,
1698 activity_id,
1699 standard_rate_flag,
1700 assigned_units,
1701 usage_rate_or_amount,
1702 usage_rate_or_amount_inverse,
1703 basis_type,
1704 schedule_flag,
1705 last_update_date,
1706 last_updated_by,
1707 creation_date,
1708 created_by,
1709 last_update_login,
1710 resource_offset_percent,
1711 autocharge_type,
1712 attribute_category,
1713 request_id,
1714 program_application_id,
1715 program_id,
1716 program_update_date,
1717 attribute1,
1718 attribute2,
1719 attribute3,
1720 attribute4,
1721 attribute5,
1722 attribute6,
1723 attribute7,
1724 attribute8,
1725 attribute9,
1726 attribute10,
1727 attribute11,
1728 attribute12,
1729 attribute13,
1730 attribute14,
1731 attribute15,
1732 principle_flag,
1733 setup_id,
1734 change_notice,
1735 acd_type,
1736 original_system_reference
1737 )
1738 SELECT /*DISTINCT Commented the above distinct for bug 6828461*/
1739 a.operation_sequence_id,
1740 b.substitute_group_num,
1741 d.resource_id,
1742 b.schedule_seq_num,
1743 b.replacement_group_num,
1744 b.activity_id,
1745 b.standard_rate_flag,
1746 b.assigned_units,
1747 b.usage_rate_or_amount,
1748 b.usage_rate_or_amount_inverse,
1749 b.basis_type,
1750 b.schedule_flag,
1751 SYSDATE,
1752 user_id,
1753 SYSDATE,
1754 user_id,
1755 NULL,
1756 b.resource_offset_percent,
1757 b.autocharge_type,
1758 b.attribute_category,
1759 fnd_global.conc_request_id,
1760 NULL,
1761 fnd_global.conc_program_id,
1762 sysdate,
1763 b.attribute1,
1764 b.attribute2,
1765 b.attribute3,
1766 b.attribute4,
1767 b.attribute5,
1768 b.attribute6,
1769 b.attribute7,
1770 b.attribute8,
1771 b.attribute9,
1772 b.attribute10,
1773 b.attribute11,
1774 b.attribute12,
1775 b.attribute13,
1776 b.attribute14,
1777 b.attribute15,
1778 b.principle_flag,
1779 b.setup_id,
1780 DECODE (p_routing_or_eco,
1781 1, b.change_notice,
1782 p_e_change_notice
1783 ),
1784 DECODE (p_routing_or_eco, 1, b.acd_type, 1),
1785 -- Add is the action for ECO
1786 b.original_system_reference
1787 FROM /*BOM_OPERATION_RESOURCES A, Commented for Bug 6828461*/
1788 bom_operation_sequences a, /*Added for Bug 6828461*/
1789 bom_sub_operation_resources b,
1790 bom_resources c,
1791 bom_resources d
1792 WHERE A.ROUTING_SEQUENCE_ID = to_sequence_id /*Added for performance improvement for bug 6828461*/
1793 AND a.last_updated_by = b.operation_sequence_id
1794 -- AND a.created_by = b.schedule_seq_num Bug No 6407518
1795 AND b.resource_id = c.resource_id
1796 AND c.resource_code = d.resource_code
1797 AND d.organization_id = to_org_id
1798 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE;
1799
1800 IF p_log_errors = 'Y' THEN
1801 INSERT INTO mtl_interface_errors
1802 (unique_id,
1803 organization_id,
1804 transaction_id,
1805 table_name,
1806 column_name,
1807 error_message,
1811 creation_date,
1808 bo_identifier,
1809 last_update_date,
1810 last_updated_by,
1812 created_by,
1813 message_type,
1814 request_id,
1815 program_application_id,
1816 program_id,
1817 program_update_date
1818 )
1819 SELECT DISTINCT
1820 l_from_item_id,
1821 to_org_id,
1822 p_copy_request_id,
1823 NULL,
1824 NULL,
1825 bom_copy_routing.get_message('BOM_CE_SUB_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code, 'SUB'),
1826 'BOM_COPY',
1827 sysdate,
1828 user_id,
1829 sysdate,
1830 user_id,
1831 'E',
1832 fnd_global.conc_request_id,
1833 NULL,
1834 fnd_global.conc_program_id,
1835 sysdate
1836 FROM bom_operation_resources a,
1837 bom_operation_sequences fbor,
1838 bom_sub_operation_resources b,
1839 bom_resources c,
1840 bom_resources d
1841 WHERE a.last_updated_by = b.operation_sequence_id
1842 AND b.operation_sequence_id = fbor.operation_sequence_id
1843 AND a.created_by = b.schedule_seq_num
1844 AND b.resource_id = c.resource_id
1845 AND c.resource_code = d.resource_code(+)
1846 AND d.organization_id = to_org_id
1847 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
1848 MINUS
1849 SELECT DISTINCT
1850 l_from_item_id,
1851 to_org_id,
1852 p_copy_request_id,
1853 NULL,
1854 NULL,
1855 bom_copy_routing.get_message('BOM_CE_SUB_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code, 'SUB'),
1856 'BOM_COPY',
1857 sysdate,
1858 user_id,
1859 sysdate,
1860 user_id,
1861 'E',
1862 fnd_global.conc_request_id,
1863 NULL,
1864 fnd_global.conc_program_id,
1865 sysdate
1866 FROM bom_operation_resources a,
1867 bom_operation_sequences fbor,
1868 bom_sub_operation_resources b,
1869 bom_resources c,
1870 bom_resources d
1871 WHERE a.last_updated_by = b.operation_sequence_id
1872 AND a.created_by = b.schedule_seq_num
1873 AND b.operation_sequence_id = fbor.operation_sequence_id
1874 AND b.resource_id = c.resource_id
1875 AND c.resource_code = d.resource_code
1876 AND d.organization_id = to_org_id
1877 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE;
1878 END IF; /* IF p_log_errors = 'Y' */
1879 copy_sub_resources := SQL%ROWCOUNT;
1880
1881 IF (from_org_id = to_org_id)
1882 THEN
1883 total_sub_resources := SQL%ROWCOUNT;
1884 END IF;
1885
1886 IF (from_org_id <> to_org_id)
1887 THEN
1888 sql_stmt_num := 35;
1889
1890 UPDATE bom_sub_operation_resources a
1891 SET activity_id =
1892 (SELECT DECODE (organization_id, NULL, activity_id, NULL)
1893 FROM cst_activities
1894 WHERE activity_id = a.activity_id),
1895 schedule_flag =
1896 (SELECT DECODE (c.unit_of_measure,
1897 NULL, 2,
1898 hour_uom_code_v, a.schedule_flag,
1899 DECODE (b.uom_class,
1900 hour_uom_class_v, a.schedule_flag,
1901 2
1902 )
1903 )
1904 FROM mtl_units_of_measure b,
1905 bom_resources c
1906 WHERE a.resource_id = c.resource_id
1907 AND c.unit_of_measure = b.unit_of_measure(+)),
1908 setup_id =
1909 (SELECT brs.setup_id
1910 FROM bom_resource_setups brs,
1911 bom_setup_types bst
1912 WHERE brs.resource_id = a.resource_id
1913 AND brs.setup_id = bst.setup_id
1914 AND bst.setup_code = (SELECT setup_code
1915 FROM bom_setup_types
1916 WHERE setup_id = a.setup_id))
1917 WHERE a.operation_sequence_id IN (
1918 SELECT operation_sequence_id
1919 FROM bom_operation_sequences
1920 WHERE routing_sequence_id =
1921 to_sequence_id);
1922 END IF;
1923
1924 -- Bug Fix 2991810
1925
1926 -- UPDATE LAST_UPDATED_BY COLUMN USED TO STORE COPY_FROM OP_SEQ_IDS
1927 sql_stmt_num := 55;
1928
1929 UPDATE bom_operation_sequences
1930 SET last_updated_by = user_id
1931 WHERE routing_sequence_id = to_sequence_id;
1932
1933 -- Bug Fix 2991810
1934 sql_stmt_num := 65;
1938 created_by = user_id
1935
1936 UPDATE bom_operation_resources
1937 SET last_updated_by = user_id,
1939 WHERE operation_sequence_id IN (
1940 SELECT operation_sequence_id
1941 FROM bom_operation_sequences
1942 WHERE routing_sequence_id =
1943 to_sequence_id);
1944
1945 sql_stmt_num := 39;
1946
1947 SELECT COUNT (*)
1948 INTO copy_instrs
1949 FROM fnd_attached_documents b,
1950 bom_operation_sequences a
1951 WHERE a.routing_sequence_id = to_sequence_id
1952 AND a.operation_sequence_id = b.pk1_value
1953 AND b.entity_name = 'BOM_OPERATION_SEQUENCES';
1954
1955 --begin bug fix 3473851
1956 sql_stmt_num := 40;
1957
1958 SELECT COUNT (*)
1959 INTO copy_hdr_instrs
1960 FROM fnd_attached_documents b,
1961 bom_operational_routings a
1962 WHERE a.routing_sequence_id = to_sequence_id
1963 AND a.routing_sequence_id = b.pk1_value
1964 AND b.entity_name = 'BOM_OPERATIONAL_ROUTINGS';
1965
1966 --end bug fix 3473851
1967 IF (from_org_id = to_org_id)
1968 THEN
1969 total_instructions := copy_instrs;
1970 total_hdr_instructions := copy_hdr_instrs; --bug 3473851
1971 END IF;
1972
1973 fnd_message.set_name ('BOM', 'BOM_ROUTING_COPY_DONE');
1974 fnd_message.set_token ('ENTITY1', copy_operations);
1975 fnd_message.set_token ('ENTITY2', total_opseqs);
1976 fnd_message.set_token ('ENTITY3', copy_resources);
1977 fnd_message.set_token ('ENTITY4', total_resources);
1978 fnd_message.set_token ('ENTITY7', copy_sub_resources);
1979 -- Bug2991810 the message also needs to be modified
1980 fnd_message.set_token ('ENTITY8', total_sub_resources); -- Bug2991810
1981 fnd_message.set_token ('ENTITY5', copy_instrs);
1982 fnd_message.set_token ('ENTITY6', total_instructions);
1983 fnd_message.set_token ('ENTITY9', copy_hdr_instrs); --bug 3473851
1984 fnd_message.set_token ('ENTITY10', total_hdr_instructions);
1985 --bug 3473851
1986 EXCEPTION
1987 WHEN OTHERS
1988 THEN
1989 err_msg :=
1990 'COPY_ROUTING (' || TO_CHAR (sql_stmt_num) || ') '
1991 || SQLERRM;
1992 fnd_message.set_name ('BOM', 'BOM_SQL_ERR');
1993 fnd_message.set_token ('ENTITY', err_msg);
1994 ROLLBACK TO begin_routing_copy;
1995 app_exception.raise_exception;
1996 END copy_routing;
1997
1998 PROCEDURE switch_to_primary_rtg (
1999 p_org_id IN NUMBER,
2000 p_ass_itm_id IN NUMBER,
2001 p_alt_rtg_desg IN VARCHAR2,
2002 p_alt_desg_for_prim_rtg IN VARCHAR2
2003 )
2004 IS
2005 BEGIN
2006 UPDATE bom_operational_routings
2007 SET alternate_routing_designator = 'NONE'
2008 WHERE organization_id = p_org_id
2009 AND assembly_item_id = p_ass_itm_id
2010 AND alternate_routing_designator = p_alt_rtg_desg;
2011
2012 UPDATE bom_operational_routings
2013 SET alternate_routing_designator = p_alt_desg_for_prim_rtg
2014 WHERE organization_id = p_org_id
2015 AND assembly_item_id = p_ass_itm_id
2016 AND NVL (alternate_routing_designator, 'NULL') = 'NULL';
2017
2018 UPDATE bom_operational_routings
2019 SET alternate_routing_designator = NULL
2020 WHERE organization_id = p_org_id
2021 AND assembly_item_id = p_ass_itm_id
2022 AND alternate_routing_designator = 'NONE';
2023 END switch_to_primary_rtg;
2024
2025 /*** Added as part of Patchset I enhancement - 2544075 ***/
2026 PROCEDURE switch_rtg_validate (
2027 p_org_id IN NUMBER,
2028 p_ass_itm_id IN NUMBER,
2029 p_alt_rtg_desg IN VARCHAR2,
2030 p_alt_desg_for_prim_rtg IN VARCHAR2,
2031 x_return_status IN OUT NOCOPY VARCHAR2,
2032 x_message_name IN OUT NOCOPY VARCHAR2
2033 )
2034 IS
2035 CURSOR validateswitch
2036 IS
2037 SELECT NULL
2038 FROM DUAL
2039 WHERE EXISTS (
2040 SELECT 1
2041 /* Checking for the BOM components operation seq. num. for primary */
2042 FROM bom_bill_of_materials bom,
2043 bom_component_operations bco
2044 WHERE bom.organization_id = p_org_id
2045 AND bom.assembly_item_id = p_ass_itm_id
2046 AND bom.alternate_bom_designator IS NULL
2047 AND bom.bill_sequence_id = bco.bill_sequence_id)
2048 OR EXISTS (
2049 SELECT 1
2050 /* Checking for the BOM components operation seq. num. for primary*/
2051 FROM bom_bill_of_materials bom,
2052 bom_inventory_components bic
2053 WHERE bom.organization_id = p_org_id
2054 AND bom.assembly_item_id = p_ass_itm_id
2055 AND bom.alternate_bom_designator IS NULL
2059 SELECT 1
2056 AND bom.bill_sequence_id = bic.bill_sequence_id
2057 AND bic.operation_seq_num > 1)
2058 OR EXISTS (
2060 /* Checking for the BOM components operation seq. num. for alternate */
2061 FROM bom_bill_of_materials bom,
2062 bom_component_operations bco
2063 WHERE bom.organization_id = p_org_id
2064 AND bom.assembly_item_id = p_ass_itm_id
2065 AND bom.alternate_bom_designator = p_alt_rtg_desg
2066 AND bom.bill_sequence_id = bco.bill_sequence_id)
2067 OR EXISTS (
2068 SELECT 1
2069 /* Checking for the BOM components operation seq. num. for alternate */
2070 FROM bom_bill_of_materials bom,
2071 bom_inventory_components bic
2072 WHERE bom.organization_id = p_org_id
2073 AND bom.assembly_item_id = p_ass_itm_id
2074 AND bom.alternate_bom_designator = p_alt_rtg_desg
2075 AND bom.bill_sequence_id = bic.bill_sequence_id
2076 AND bic.operation_seq_num > 1)
2077 OR EXISTS (
2078 SELECT 1 /* Check if flow schedule exists for primary */
2079 FROM wip_flow_schedules
2080 WHERE organization_id = p_org_id
2081 AND primary_item_id = p_ass_itm_id
2082 AND alternate_routing_designator IS NULL)
2083 OR EXISTS (
2084 SELECT 1 /* Check if flow schedule exists for alternate */
2085 FROM wip_flow_schedules
2086 WHERE organization_id = p_org_id
2087 AND primary_item_id = p_ass_itm_id
2088 AND alternate_routing_designator = p_alt_rtg_desg)
2089 OR EXISTS (
2090 SELECT 1 /* Check for WIP Jobs on primary */
2091 FROM wip_discrete_jobs job,
2092 bom_operational_routings bor
2093 WHERE job.organization_id = p_org_id
2094 AND job.primary_item_id = p_ass_itm_id
2095 AND job.alternate_routing_designator IS NULL)
2096 OR EXISTS (
2097 SELECT 1 /* Check for WIP Jobs on alternate */
2098 FROM wip_discrete_jobs job,
2099 bom_operational_routings bor
2100 WHERE job.organization_id = p_org_id
2101 AND job.primary_item_id = p_ass_itm_id
2102 AND job.alternate_routing_designator = p_alt_rtg_desg);
2103 BEGIN
2104 x_return_status := fnd_api.g_ret_sts_success;
2105
2106 FOR valswitch IN validateswitch
2107 LOOP
2108 x_return_status := bom_rtg_error_handler.g_status_warning;
2109 x_message_name := 'BOM_SWITCH_ROUTING_WARNINGS';
2110 END LOOP;
2111 END switch_rtg_validate;
2112
2113 PROCEDURE switch_to_primary_rtg (
2114 p_org_id IN NUMBER,
2115 p_ass_itm_id IN NUMBER,
2116 p_alt_rtg_desg IN VARCHAR2,
2117 p_alt_desg_for_prim_rtg IN VARCHAR2,
2118 x_return_status IN OUT NOCOPY VARCHAR2,
2119 x_message_name IN OUT NOCOPY VARCHAR2
2120 )
2121 IS
2122 BEGIN
2123 switch_rtg_validate
2124 (p_org_id => p_org_id,
2125 p_ass_itm_id => p_ass_itm_id,
2126 p_alt_rtg_desg => p_alt_rtg_desg,
2127 p_alt_desg_for_prim_rtg => p_alt_desg_for_prim_rtg,
2128 x_return_status => x_return_status,
2129 x_message_name => x_message_name
2130 );
2131 switch_to_primary_rtg
2132 (p_org_id => p_org_id,
2133 p_ass_itm_id => p_ass_itm_id,
2134 p_alt_rtg_desg => p_alt_rtg_desg,
2135 p_alt_desg_for_prim_rtg => p_alt_desg_for_prim_rtg
2136 );
2137 END switch_to_primary_rtg;
2138
2139 FUNCTION GET_MESSAGE (p_msg_name IN VARCHAR2, p_op_seq_num IN NUMBER)
2140 RETURN VARCHAR2
2141 IS
2142 BEGIN
2143 fnd_message.set_name ('BOM', p_msg_name);
2144 fnd_message.set_token ('OP_SEQ', p_op_seq_num);
2145 RETURN fnd_message.get;
2146 END;
2147 FUNCTION GET_MESSAGE (p_msg_name IN VARCHAR2, p_op_seq_num IN NUMBER, p_oper_type IN VARCHAR2)
2148 RETURN VARCHAR2
2149 IS
2150 BEGIN
2151 fnd_message.set_name ('BOM', p_msg_name);
2152 fnd_message.set_token ('OP_SEQ', p_op_seq_num);
2153 fnd_message.set_token ('OP_TYPE', p_oper_type);
2154 RETURN fnd_message.get;
2155 END;
2156 FUNCTION GET_MESSAGE (p_msg_name IN VARCHAR2, p_op_seq_num IN NUMBER, p_entity_name IN VARCHAR2, p_type IN VARCHAR2)
2157 RETURN VARCHAR2
2158 IS
2159 BEGIN
2160 fnd_message.set_name ('BOM', p_msg_name);
2161 fnd_message.set_token ('OP_SEQ', p_op_seq_num);
2162 IF p_type = 'DEP' THEN
2163 fnd_message.set_token ('FROM_DEPT', p_entity_name);
2164 ELSE
2168 END;
2165 fnd_message.set_token ('RES_CODE', p_entity_name);
2166 END IF;
2167 RETURN fnd_message.get;
2169
2170 PROCEDURE switch_common_to_primary_rtg ( -- BUG 4712488
2171 p_org_id IN NUMBER,
2172 p_ass_itm_id IN NUMBER,
2173 p_alt_rtg_desg IN VARCHAR2,
2174 p_alt_desg_for_prim_rtg IN VARCHAR2
2175 )
2176 IS
2177 BEGIN
2178
2179 UPDATE bom_operational_routings
2180 SET common_assembly_item_id =
2181 ( SELECT assembly_item_id
2182 FROM bom_operational_routings
2183 WHERE assembly_item_id = p_ass_itm_id
2184 AND organization_id = p_org_id
2185 AND alternate_routing_designator = p_alt_rtg_desg )
2186 , common_routing_sequence_id =
2187 ( SELECT routing_sequence_id
2188 FROM bom_operational_routings
2189 WHERE assembly_item_id = p_ass_itm_id
2190 AND organization_id = p_org_id
2191 AND alternate_routing_designator = p_alt_rtg_desg )
2192 , completion_subinventory =
2193 ( SELECT completion_subinventory
2194 FROM bom_operational_routings
2195 WHERE assembly_item_id = p_ass_itm_id
2196 AND organization_id = p_org_id
2197 AND alternate_routing_designator = p_alt_rtg_desg )
2198 , completion_locator_id =
2199 ( SELECT completion_locator_id
2200 FROM bom_operational_routings
2201 WHERE assembly_item_id = p_ass_itm_id
2202 AND organization_id = p_org_id
2203 AND alternate_routing_designator = p_alt_rtg_desg )
2204 WHERE common_routing_sequence_id IN
2205 ( SELECT routing_Sequence_id FROM bom_operational_routings
2206 WHERE assembly_item_id = p_ass_itm_id
2207 AND organization_id = p_org_id
2208 AND alternate_routing_designator IS NULL )
2209 AND common_assembly_item_id IN
2210 ( SELECT assembly_item_id FROM bom_operational_routings
2211 WHERE assembly_item_id = p_ass_itm_id
2212 AND organization_id = p_org_id
2213 AND alternate_routing_designator IS NULL );
2214
2215 END switch_common_to_primary_rtg;
2216
2217 PROCEDURE switch_common_to_alternate_rtg ( -- BUG 4712488
2218 p_org_id IN NUMBER,
2219 p_ass_itm_id IN NUMBER,
2220 p_alt_desg_for_prim_rtg IN VARCHAR2,
2221 p_rtg_seq_id IN NUMBER
2222 )
2223 IS
2224 BEGIN
2225
2226 UPDATE bom_operational_routings
2227 SET common_assembly_item_id =
2228 ( SELECT assembly_item_id
2229 FROM bom_operational_routings
2230 WHERE assembly_item_id = p_ass_itm_id
2231 AND organization_id = p_org_id
2232 AND alternate_routing_designator IS NULL )
2233 , common_routing_sequence_id =
2234 ( SELECT routing_sequence_id
2235 FROM bom_operational_routings
2236 WHERE assembly_item_id = p_ass_itm_id
2237 AND organization_id = p_org_id
2238 AND alternate_routing_designator IS NULL )
2239 , completion_subinventory =
2240 ( SELECT completion_subinventory
2241 FROM bom_operational_routings
2242 WHERE assembly_item_id = p_ass_itm_id
2243 AND organization_id = p_org_id
2244 AND alternate_routing_designator IS NULL )
2245 , completion_locator_id =
2246 ( SELECT completion_locator_id
2247 FROM bom_operational_routings
2248 WHERE assembly_item_id = p_ass_itm_id
2249 AND organization_id = p_org_id
2250 AND alternate_routing_designator IS NULL )
2251 , alternate_routing_designator =
2252 ( p_alt_desg_for_prim_rtg )
2253 WHERE routing_sequence_id = p_rtg_seq_id;
2254
2255 END switch_common_to_alternate_rtg;
2256
2257 PROCEDURE copy_routing_for_revised_item (
2258 to_sequence_id IN NUMBER,
2259 from_sequence_id IN NUMBER,
2260 from_org_id IN NUMBER,
2261 to_org_id IN NUMBER,
2262 user_id IN NUMBER DEFAULT -1,
2263 to_item_id IN NUMBER,
2264 direction IN NUMBER,
2265 to_alternate IN VARCHAR2,
2266 rev_date DATE,
2267 p_e_change_notice IN VARCHAR2,
2268 p_rev_item_seq_id IN NUMBER,
2269 p_routing_or_eco IN NUMBER DEFAULT 1,
2270 p_trgt_eff_date IN DATE,
2271 p_eco_eff_date IN DATE,
2272 p_context_eco IN VARCHAR2,
2273 p_log_errors IN VARCHAR2 DEFAULT 'N',
2274 p_copy_request_id IN NUMBER DEFAULT NULL,
2275 p_cpy_disable_fields IN VARCHAR2 DEFAULT 'N'
2276 )
2277 IS
2278 x_from_sequence_id NUMBER := from_sequence_id;
2279 -- X_rev_date date := trunc(rev_date); -- Removed for bug 2647027
2280 total_opseqs NUMBER := 0;
2281 total_resources NUMBER := 0;
2282 total_sub_resources NUMBER := 0;
2283 total_instructions NUMBER := 0;
2284 total_hdr_instructions NUMBER := 0;
2285 hour_uom_code_v VARCHAR2 (3);
2286 hour_uom_class_v VARCHAR2 (10);
2287 sql_stmt_num NUMBER;
2288 err_msg VARCHAR2 (2000);
2292 copy_hdr_instrs NUMBER;
2289 copy_resources NUMBER := 0;
2290 copy_sub_resources NUMBER := 0;
2291 copy_instrs NUMBER;
2293 copy_operations NUMBER;
2294 p_op_seq_id NUMBER;
2295 p_op_seq_num NUMBER;
2296 new_p_op_seq_id NUMBER;
2297 l_op_seq_id NUMBER;
2298 l_op_seq_num NUMBER;
2299 new_l_op_seq_id NUMBER;
2300 l_curr_date DATE; -- Added for bug 2718955
2301 -- Bug fix 3473802
2302 p_st_op_id NUMBER;
2303 new_st_op_id NUMBER;
2304 min_qty NUMBER;
2305 back_flag NUMBER;
2306 opt_flag NUMBER;
2307 count_type NUMBER;
2308 opr_desc VARCHAR2 (240);
2309 copy_ops_update NUMBER;
2310
2311 CURSOR source_rtg
2312 IS
2313 SELECT operation_sequence_id,
2314 last_updated_by
2315 FROM bom_operation_sequences
2316 WHERE routing_sequence_id = to_sequence_id
2317 AND NVL (eco_for_production, 2) = 2;
2318
2319 CURSOR process_op
2320 IS
2321 SELECT operation_sequence_id,
2322 operation_seq_num
2323 FROM bom_operation_sequences
2324 WHERE routing_sequence_id = x_from_sequence_id -- Bug 2642427
2325 AND NVL (eco_for_production, 2) = 2
2326 AND operation_type = 2;
2327
2328 CURSOR line_op
2329 IS
2330 SELECT operation_sequence_id,
2331 operation_seq_num
2332 FROM bom_operation_sequences
2333 WHERE routing_sequence_id = x_from_sequence_id -- Bug 2642427
2334 AND NVL (eco_for_production, 2) = 2
2335 AND operation_type = 3;
2336
2337 -- Cursor Bug fix 3473802
2338 CURSOR update_st_op
2339 IS
2340 SELECT standard_operation_id,
2341 operation_sequence_id
2342 FROM bom_operation_sequences
2343 WHERE routing_sequence_id = to_sequence_id;
2344 l_from_item_id NUMBER;
2345 BEGIN
2346 SAVEPOINT begin_routing_copy;
2347 sql_stmt_num := 1;
2348 fnd_profile.get ('BOM:HOUR_UOM_CODE', hour_uom_code_v);
2349
2350 BEGIN
2351 SELECT uom_class
2352 INTO hour_uom_class_v
2353 FROM mtl_units_of_measure
2354 WHERE uom_code = hour_uom_code_v;
2355 EXCEPTION
2356 WHEN OTHERS
2357 THEN
2358 NULL;
2359 END;
2360
2361 sql_stmt_num := 10;
2362
2363 SELECT common_routing_sequence_id
2364 INTO x_from_sequence_id
2365 FROM bom_operational_routings
2366 WHERE routing_sequence_id = from_sequence_id;
2367
2368 IF (from_org_id <> to_org_id)
2369 THEN
2370 rtg_get_msg_info (total_opseqs,
2371 total_resources,
2372 total_sub_resources, -- 2991810
2373 total_instructions,
2374 total_hdr_instructions, --bug 3473851
2375 x_from_sequence_id,
2376 rev_date,
2377 2
2378 );
2379 END IF;
2380
2381 --copy operations
2382 --null out std op id, operation_offset_%
2383 --do not copy operations where department does not exist in to org
2384 --if dept_id is diff in to org, reset dept id
2385 -- For flow routings, we need to update the process_op_seq_id
2386 -- and line_op_seq_id with the new values generated.
2387 sql_stmt_num := 15;
2388 IF p_trgt_eff_date IS NULL THEN
2389 l_curr_date := SYSDATE;
2390 ELSE
2391 l_curr_date := p_trgt_eff_date; -- Routing can be copied to particular from TTMO flow - R12
2392 END IF;
2393
2394 INSERT INTO bom_operation_sequences
2395 (operation_sequence_id,
2396 routing_sequence_id,
2397 operation_seq_num,
2398 last_update_date,
2399 last_updated_by,
2400 creation_date,
2401 created_by,
2402 last_update_login,
2403 standard_operation_id,
2404 department_id,
2405 operation_lead_time_percent,
2406 minimum_transfer_quantity,
2407 count_point_type,
2408 operation_description,
2409 effectivity_date,
2410 disable_date,
2411 backflush_flag,
2412 option_dependent_flag,
2413 attribute_category,
2414 attribute1,
2415 attribute2,
2416 attribute3,
2417 attribute4,
2418 attribute5,
2419 attribute6,
2420 attribute7,
2421 attribute8,
2422 attribute9,
2423 attribute10,
2424 attribute11,
2425 attribute12,
2429 request_id,
2426 attribute13,
2427 attribute14,
2428 attribute15,
2430 program_application_id,
2431 program_id,
2432 program_update_date,
2433 operation_type,
2434 reference_flag,
2435 process_op_seq_id,
2436 line_op_seq_id,
2437 yield,
2438 cumulative_yield,
2439 reverse_cumulative_yield,
2440 labor_time_calc,
2441 machine_time_calc,
2442 total_time_calc,
2443 labor_time_user,
2444 machine_time_user,
2445 total_time_user,
2446 net_planning_percent,
2447 x_coordinate,
2448 y_coordinate,
2449 include_in_rollup,
2450 operation_yield_enabled,
2451 old_operation_sequence_id,
2452 acd_type,
2453 revised_item_sequence_id,
2454 original_system_reference,
2455 change_notice,
2456 implementation_date,
2457 eco_for_production,
2458 shutdown_type,
2459 -- Added by MK 04/10/2001
2460 long_description, -- Added for bug 2767630
2461 lowest_acceptable_yield, -- Added for MES Enhancement
2462 use_org_settings,
2463 queue_mandatory_flag,
2464 run_mandatory_flag,
2465 to_move_mandatory_flag,
2466 show_next_op_by_default,
2467 show_scrap_code,
2468 show_lot_attrib,
2469 track_multiple_res_usage_dates
2470 )
2471 SELECT bom_operation_sequences_s.NEXTVAL,
2472 to_sequence_id,
2473 a.operation_seq_num,
2474 l_curr_date,
2475 a.operation_sequence_id,
2476 l_curr_date,
2477 user_id,
2478 user_id,
2479 a.standard_operation_id,
2480 c.department_id,
2481 NULL,
2482 a.minimum_transfer_quantity,
2483 a.count_point_type,
2484 a.operation_description,
2485 -- Bug 2161841
2486 -- GREATEST(A.EFFECTIVITY_DATE, l_curr_date), -- Changed for bug 2647027
2487 p_eco_eff_date,
2488 CASE
2489 -- This flag will be set when current and future option is selected with
2490 -- copy through ECO
2491 WHEN a.disable_date IS NOT NULL
2492 AND a.disable_date > p_eco_eff_date
2493 THEN a.disable_date
2494 ELSE
2495 TO_DATE (NULL)
2496 END AS disable_date,
2497 a.backflush_flag,
2498 a.option_dependent_flag,
2499 a.attribute_category,
2500 a.attribute1,
2501 a.attribute2,
2502 a.attribute3,
2503 a.attribute4,
2504 a.attribute5,
2505 a.attribute6,
2506 a.attribute7,
2507 a.attribute8,
2508 a.attribute9,
2509 a.attribute10,
2510 a.attribute11,
2511 a.attribute12,
2512 a.attribute13,
2513 a.attribute14,
2514 a.attribute15,
2515 fnd_global.conc_request_id,
2516 NULL,
2517 fnd_global.conc_program_id,
2518 sysdate,
2519 a.operation_type,
2520 DECODE (from_org_id, to_org_id, a.reference_flag, 2),
2521 -- Bug 3473802
2522 a.process_op_seq_id,
2523 a.line_op_seq_id,
2524 a.yield,
2525 a.cumulative_yield,
2526 a.reverse_cumulative_yield,
2527 a.labor_time_calc,
2528 a.machine_time_calc,
2529 a.total_time_calc,
2530 a.labor_time_user,
2531 a.machine_time_user,
2532 a.total_time_user,
2533 a.net_planning_percent,
2534 a.x_coordinate,
2535 a.y_coordinate,
2536 a.include_in_rollup,
2537 a.operation_yield_enabled,
2538 a.old_operation_sequence_id,
2539 1,
2540 p_rev_item_seq_id,
2541 a.original_system_reference,
2542 p_e_change_notice,
2543 NULL,
2544 a.eco_for_production,
2545 a.shutdown_type,
2546 -- Added by MK 04/10/2001
2547 a.long_description,
2548 a.lowest_acceptable_yield, -- Added for MES Enhancement
2549 a.use_org_settings,
2550 a.queue_mandatory_flag,
2551 a.run_mandatory_flag,
2552 a.to_move_mandatory_flag,
2553 a.show_next_op_by_default,
2554 a.show_scrap_code,
2555 a.show_lot_attrib,
2556 a.track_multiple_res_usage_dates
2560 WHERE a.routing_sequence_id = x_from_sequence_id
2557 FROM bom_operation_sequences a, -- from op
2558 bom_departments b, -- from op's dept
2559 bom_departments c -- to op's dept
2561 AND NVL (a.eco_for_production, 2) = 2
2562 AND a.department_id = b.department_id
2563 AND b.department_code = c.department_code
2564 -- comparing departments with same name
2565 AND c.organization_id = to_org_id
2566 AND NVL (c.disable_date, l_curr_date + 1) > l_curr_date
2567 AND ( ( p_cpy_disable_fields = 'Y' AND a.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
2568 OR ( p_cpy_disable_fields = 'N' AND a.effectivity_date = rev_date )
2569 )
2570 -- AND A.IMPLEMENTATION_DATE IS NOT NULL ; /* Bug 2717982 */
2571 AND (a.implementation_date IS NOT NULL
2572 OR (a.implementation_date IS NULL
2573 AND a.change_notice = p_context_eco
2574 AND ( a.acd_type = 1 OR a.acd_type = 2 )
2575 )
2576 )
2577 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
2578 (
2579 SELECT 1
2580 FROM bom_operation_sequences bos
2581 WHERE bos.routing_sequence_id = a.routing_sequence_id
2582 AND bos.old_operation_sequence_id = a.operation_sequence_id
2583 AND bos.change_notice = p_context_eco
2584 AND bos.acd_type = 3
2585 AND bos.effectivity_date <= p_eco_eff_date
2586 AND bos.implementation_date IS NULL
2587 );
2588
2589 IF p_log_errors = 'Y' THEN
2590 -- There should not be any no data found for this case
2591 SELECT
2592 assembly_item_id INTO l_from_item_id
2593 FROM
2594 bom_operational_routings bor
2595 WHERE
2596 bor.routing_sequence_id = from_sequence_id;
2597 INSERT INTO mtl_interface_errors
2598 (unique_id,
2599 organization_id,
2600 transaction_id,
2601 table_name,
2602 column_name,
2603 error_message,
2604 bo_identifier,
2605 last_update_date,
2606 last_updated_by,
2607 creation_date,
2608 created_by,
2609 message_type,
2610 request_id,
2611 program_application_id,
2612 program_id,
2613 program_update_date
2614 )
2615 SELECT
2616 l_from_item_id,
2617 to_org_id,
2618 p_copy_request_id,
2619 NULL,
2620 NULL,
2621 bom_copy_routing.get_message('BOM_CE_RTG_DEPT_NOT_EXISTS',
2622 a.operation_seq_num, b.department_code,'DEP'),
2623 'BOM_COPY',
2624 sysdate,
2625 user_id,
2626 sysdate,
2627 user_id,
2628 'E',
2629 fnd_global.conc_request_id,
2630 NULL,
2631 fnd_global.conc_program_id,
2632 sysdate
2633 FROM bom_operation_sequences a, -- from op
2634 bom_departments b, -- from op's dept
2635 bom_departments c -- to op's dept
2636 WHERE a.routing_sequence_id = x_from_sequence_id
2637 AND ( ( p_cpy_disable_fields = 'Y' AND a.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
2638 OR ( p_cpy_disable_fields = 'N' AND a.effectivity_date = rev_date )
2639 )
2640 AND a.department_id = b.department_id
2641 AND b.department_code = c.department_code (+)
2642 -- comparing departments with same name
2643 AND c.organization_id = to_org_id
2644 AND NVL (c.disable_date, l_curr_date + 1) > l_curr_date
2645 AND (a.implementation_date IS NOT NULL
2646 OR (a.implementation_date IS NULL
2647 AND a.change_notice = p_context_eco
2648 AND ( a.acd_type = 1 OR a.acd_type = 2 )
2649 )
2650 )
2651 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
2652 (
2653 SELECT 1
2654 FROM bom_operation_sequences bos
2655 WHERE bos.routing_sequence_id = a.routing_sequence_id
2656 AND bos.old_operation_sequence_id = a.operation_sequence_id
2657 AND bos.change_notice = p_context_eco
2658 AND bos.acd_type = 3
2659 AND bos.effectivity_date <= p_eco_eff_date
2660 AND bos.implementation_date IS NULL
2661 )
2662 MINUS -- Filter the departments for which the match is found
2663 SELECT
2664 l_from_item_id,
2665 to_org_id,
2666 p_copy_request_id,
2667 NULL,
2668 NULL,
2669 bom_copy_routing.get_message('BOM_CE_RTG_DEPT_NOT_EXISTS',
2670 a.operation_seq_num, b.department_code,'DEP'),
2671 'BOM_COPY',
2672 sysdate,
2673 user_id,
2674 sysdate,
2675 user_id,
2676 'E',
2677 fnd_global.conc_request_id,
2678 NULL,
2679 fnd_global.conc_program_id,
2680 sysdate
2684 AND ( ( p_cpy_disable_fields = 'Y' AND a.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
2681 FROM bom_operation_sequences a, -- from op
2682 bom_departments b -- from op's dept
2683 WHERE a.routing_sequence_id = x_from_sequence_id
2685 OR ( p_cpy_disable_fields = 'N' AND a.effectivity_date = rev_date )
2686 )
2687 AND a.department_id = b.department_id
2688 AND (a.implementation_date IS NOT NULL
2689 OR (a.implementation_date IS NULL
2690 AND a.change_notice = p_context_eco
2691 AND ( a.acd_type = 1 OR a.acd_type = 2 )
2692 )
2693 )
2694 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
2695 (
2696 SELECT 1
2697 FROM bom_operation_sequences bos
2698 WHERE bos.routing_sequence_id = a.routing_sequence_id
2699 AND bos.old_operation_sequence_id = a.operation_sequence_id
2700 AND bos.change_notice = p_context_eco
2701 AND bos.acd_type = 3
2702 AND bos.effectivity_date <= p_eco_eff_date
2703 AND bos.implementation_date IS NULL
2704 );
2705 INSERT INTO mtl_interface_errors
2706 (unique_id,
2707 organization_id,
2708 transaction_id,
2709 table_name,
2710 column_name,
2711 error_message,
2712 bo_identifier,
2713 last_update_date,
2714 last_updated_by,
2715 creation_date,
2716 created_by,
2717 message_type,
2718 request_id,
2719 program_application_id,
2720 program_id,
2721 program_update_date
2722 )
2723 SELECT
2724 l_from_item_id,
2725 to_org_id,
2726 p_copy_request_id,
2727 NULL,
2728 NULL,
2729 bom_copy_routing.get_message('BOM_CE_RTG_OPER_FOR_WIP_JOB',a.operation_seq_num),
2730 'BOM_COPY',
2731 sysdate,
2732 user_id,
2733 sysdate,
2734 user_id,
2735 'E',
2736 fnd_global.conc_request_id,
2737 NULL,
2738 fnd_global.conc_program_id,
2739 sysdate
2740 FROM bom_operation_sequences a
2741 WHERE a.routing_sequence_id = x_from_sequence_id
2742 AND ( ( p_cpy_disable_fields = 'Y' AND a.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
2743 OR ( p_cpy_disable_fields = 'N' AND a.effectivity_date = rev_date )
2744 )
2745 AND a.eco_for_production <> 2
2746 AND (a.implementation_date IS NOT NULL
2747 OR (a.implementation_date IS NULL
2748 AND a.change_notice = p_context_eco
2749 AND ( a.acd_type = 1 OR a.acd_type = 2 )
2750 )
2751 )
2752 AND NOT EXISTS -- Bug 5151332 Disabled operations should not get copied in ECO context
2753 (
2754 SELECT 1
2755 FROM bom_operation_sequences bos
2756 WHERE bos.routing_sequence_id = a.routing_sequence_id
2757 AND bos.old_operation_sequence_id = a.operation_sequence_id
2758 AND bos.change_notice = p_context_eco
2759 AND bos.acd_type = 3
2760 AND bos.effectivity_date <= p_eco_eff_date
2761 AND bos.implementation_date IS NULL
2762 );
2763
2764 INSERT INTO mtl_interface_errors
2765 (unique_id,
2766 organization_id,
2767 transaction_id,
2768 table_name,
2769 column_name,
2770 error_message,
2771 bo_identifier,
2772 last_update_date,
2773 last_updated_by,
2774 creation_date,
2775 created_by,
2776 message_type,
2777 request_id,
2778 program_application_id,
2779 program_id,
2780 program_update_date
2781 )
2782 SELECT
2783 l_from_item_id,
2784 to_org_id,
2785 p_copy_request_id,
2786 NULL,
2787 NULL,
2788 bom_copy_routing.get_message('BOM_CE_RTG_OPER_NOT_IMPL',a.operation_seq_num),
2789 'BOM_COPY',
2790 sysdate,
2791 user_id,
2792 sysdate,
2793 user_id,
2794 'E',
2795 fnd_global.conc_request_id,
2796 NULL,
2797 fnd_global.conc_program_id,
2798 sysdate
2799 FROM bom_operation_sequences a
2800 WHERE a.routing_sequence_id = x_from_sequence_id
2801 AND ( ( p_cpy_disable_fields = 'Y' AND a.effectivity_date <= rev_date) -- For first revised item we can have past eff comps as eff on the target date
2802 OR ( p_cpy_disable_fields = 'N' AND a.effectivity_date = rev_date )
2803 )
2804 AND (a.implementation_date IS NULL
2805 AND p_context_eco IS NULL);
2806
2807 END IF; /* IF p_log_errors = 'Y' */
2808
2809
2810 copy_operations := SQL%ROWCOUNT;
2811
2812 IF (from_org_id = to_org_id)
2813 THEN
2817 -- Begin Bug fix 3473802
2814 total_opseqs := SQL%ROWCOUNT;
2815 END IF;
2816
2818 IF (from_org_id <> to_org_id)
2819 THEN
2820 OPEN update_st_op;
2821
2822 copy_ops_update := 0;
2823
2824 LOOP
2825 FETCH update_st_op
2826 INTO p_st_op_id,
2827 p_op_seq_id;
2828
2829 EXIT WHEN update_st_op%NOTFOUND;
2830
2831 BEGIN
2832 SELECT b.standard_operation_id,
2833 b.minimum_transfer_quantity,
2834 b.backflush_flag,
2835 b.option_dependent_flag,
2836 b.count_point_type,
2837 b.operation_description
2838 INTO new_st_op_id,
2839 min_qty,
2840 back_flag,
2841 opt_flag,
2842 count_type,
2843 opr_desc
2844 FROM bom_standard_operations_v a, -- BUG 3936049
2845 bom_standard_operations_v b -- BUG 3936049
2846 WHERE a.standard_operation_id = p_st_op_id
2847 AND a.operation_code = b.operation_code
2848 AND a.organization_id = from_org_id
2849 AND b.organization_id = to_org_id
2850 AND NVL (a.line_code, '@@@') = NVL (b.line_code, '@@@')
2851 -- BUG 3936049
2852 AND NVL (a.operation_type, -99) = NVL (b.operation_type,
2853 -99); -- BUG 3936049
2854
2855 UPDATE bom_operation_sequences
2856 SET standard_operation_id = new_st_op_id,
2857 minimum_transfer_quantity = min_qty,
2858 backflush_flag = back_flag,
2859 option_dependent_flag = opt_flag,
2860 count_point_type = count_type,
2861 operation_description = opr_desc
2862 WHERE routing_sequence_id = to_sequence_id
2863 AND operation_sequence_id = p_op_seq_id;
2864
2865 copy_ops_update := copy_ops_update + 1;
2866 EXCEPTION
2867 WHEN NO_DATA_FOUND
2868 THEN
2869 UPDATE bom_operation_sequences
2870 SET standard_operation_id = NULL
2871 WHERE routing_sequence_id = to_sequence_id
2872 AND operation_sequence_id = p_op_seq_id;
2873 END;
2874 END LOOP;
2875 END IF;
2876
2877 -- End Bug fix 3473802
2878 OPEN process_op;
2879
2880 LOOP
2881 FETCH process_op
2882 INTO p_op_seq_id,
2883 p_op_seq_num;
2884
2885 EXIT WHEN process_op%NOTFOUND;
2886
2887 BEGIN
2888 SELECT operation_sequence_id
2889 INTO new_p_op_seq_id
2890 FROM bom_operation_sequences
2891 WHERE routing_sequence_id = to_sequence_id
2892 AND operation_type = 2
2893 AND NVL (eco_for_production, 2) = 2
2894 AND operation_seq_num = p_op_seq_num;
2895 EXCEPTION
2896 WHEN NO_DATA_FOUND
2897 THEN
2898 NULL;
2899 END;
2900
2901 UPDATE bom_operation_sequences
2902 SET process_op_seq_id = new_p_op_seq_id
2903 WHERE operation_type = 1
2904 AND routing_sequence_id = to_sequence_id
2905 AND process_op_seq_id = p_op_seq_id;
2906 END LOOP;
2907
2908 OPEN line_op;
2909
2910 LOOP
2911 FETCH line_op
2912 INTO l_op_seq_id,
2913 l_op_seq_num;
2914
2915 EXIT WHEN line_op%NOTFOUND;
2916
2917 BEGIN
2918 SELECT operation_sequence_id
2919 INTO new_l_op_seq_id
2920 FROM bom_operation_sequences
2921 WHERE routing_sequence_id = to_sequence_id
2922 AND operation_type = 3
2923 AND NVL (eco_for_production, 2) = 2
2924 AND operation_seq_num = l_op_seq_num;
2925 EXCEPTION
2926 WHEN NO_DATA_FOUND
2927 THEN
2928 NULL;
2929 END;
2930
2931 UPDATE bom_operation_sequences
2932 SET line_op_seq_id = new_l_op_seq_id
2933 WHERE operation_type = 1
2934 AND routing_sequence_id = to_sequence_id
2935 AND line_op_seq_id = l_op_seq_id;
2936 END LOOP;
2937
2938 INSERT INTO bom_operation_networks
2939 (from_op_seq_id,
2940 to_op_seq_id,
2941 transition_type,
2942 planning_pct,
2943 effectivity_date,
2944 disable_date,
2945 created_by,
2946 creation_date,
2947 last_updated_by,
2948 last_update_date,
2949 last_update_login,
2950 attribute_category,
2951 attribute1,
2952 attribute2,
2956 attribute6,
2953 attribute3,
2954 attribute4,
2955 attribute5,
2957 attribute7,
2958 attribute8,
2959 attribute9,
2960 attribute10,
2961 attribute11,
2962 attribute12,
2963 attribute13,
2964 attribute14,
2965 attribute15,
2966 request_id,
2967 program_application_id,
2968 program_id,
2969 program_update_date
2970 )
2971 SELECT bos3.operation_sequence_id,
2972 bos4.operation_sequence_id,
2973 bon.transition_type,
2974 bon.planning_pct,
2975 -- Operation Network effectivity will be effective from target's to operation,
2976 -- which will be greater than target's from operation
2977 bos4.effectivity_date,
2978 /*
2979 DECODE (p_routing_or_eco,
2980 1, bon.effectivity_date,
2981 p_eco_eff_date
2982 ),*/
2983 bon.disable_date,
2984 bon.created_by,
2985 bon.creation_date,
2986 bon.last_updated_by,
2987 bon.last_update_date,
2988 bon.last_update_login,
2989 bon.attribute_category,
2990 bon.attribute1,
2991 bon.attribute2,
2992 bon.attribute3,
2993 bon.attribute4,
2994 bon.attribute5,
2995 bon.attribute6,
2996 bon.attribute7,
2997 bon.attribute8,
2998 bon.attribute9,
2999 bon.attribute10,
3000 bon.attribute11,
3001 bon.attribute12,
3002 bon.attribute13,
3003 bon.attribute14,
3004 bon.attribute15,
3005 fnd_global.conc_request_id,
3006 NULL,
3007 fnd_global.conc_program_id,
3008 sysdate
3009 FROM bom_operation_networks bon,
3010 bom_operation_sequences bos4, -- dest to op
3011 bom_operation_sequences bos3, -- dest from op
3012 bom_operation_sequences bos2, -- src to op
3013 bom_operation_sequences bos1 -- src from op
3014 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
3015 AND bon.to_op_seq_id = bos2.operation_sequence_id
3016 AND bos1.routing_sequence_id = bos2.routing_sequence_id
3017 AND bos1.routing_sequence_id = x_from_sequence_id
3018 AND bos3.routing_sequence_id = to_sequence_id
3019 AND bos3.operation_seq_num = bos1.operation_seq_num
3020 -- AND greatest(bos1.effectivity_date, l_curr_date) = greatest(bos3.effectivity_date, l_curr_date) -- added for bug 2718955
3021 -- Just compare the last updated by which will have the from operation seq num
3022 -- If the operation is copied then we need to copy the network, the effectivity filter
3023 -- is already applied at the operation sequence level
3024 AND bos3.last_updated_by = bos1.operation_sequence_id
3025 AND bos4.last_updated_by = bos2.operation_sequence_id
3026 /* Commented as part of TTMO Enh R12
3027 AND DECODE (display_option,
3028 1, bos1.effectivity_date,
3029 GREATEST (bos1.effectivity_date, l_curr_date)
3030 ) =
3031 DECODE
3032 (display_option,
3033 1, bos3.effectivity_date,
3034 GREATEST (bos3.effectivity_date, l_curr_date)
3035 ) -- added for bug 2788795
3036 */
3037 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
3038 AND NVL (bos1.eco_for_production, 2) = 2
3039 AND NVL (bos2.eco_for_production, 2) = 2
3040 AND NVL (bos3.eco_for_production, 2) = 2
3041 AND NVL (bos4.eco_for_production, 2) = 2
3042 AND bos4.routing_sequence_id = to_sequence_id
3043 AND bos4.operation_seq_num = bos2.operation_seq_num
3044 -- Just compare the last updated by which will have the from operation seq num
3045 -- If the operation is copied then we need to copy the network, the effectivity filter
3046 -- is already applied at the operation sequence level
3047 /*
3048 AND DECODE (display_option,
3049 1, bos2.effectivity_date,
3050 GREATEST (bos2.effectivity_date, l_curr_date)
3051 ) =
3052 DECODE
3053 (display_option,
3054 1, bos4.effectivity_date,
3055 GREATEST (bos4.effectivity_date, l_curr_date)
3056 ) -- added for bug 2788795
3057 */
3058 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1)
3059 AND bos2.revised_item_sequence_id = p_rev_item_seq_id;
3060
3061 IF p_log_errors = 'Y' THEN
3062 INSERT INTO mtl_interface_errors
3063 (unique_id,
3064 organization_id,
3068 error_message,
3065 transaction_id,
3066 table_name,
3067 column_name,
3069 bo_identifier,
3070 last_update_date,
3071 last_updated_by,
3072 creation_date,
3073 created_by,
3074 message_type,
3075 request_id,
3076 program_application_id,
3077 program_id,
3078 program_update_date
3079 )
3080 SELECT
3081 l_from_item_id,
3082 to_org_id,
3083 p_copy_request_id,
3084 NULL,
3085 NULL,
3086 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos1.operation_seq_num,mfgl.meaning),
3087 'BOM_COPY',
3088 sysdate,
3089 user_id,
3090 sysdate,
3091 user_id,
3092 'E',
3093 fnd_global.conc_request_id,
3094 NULL,
3095 fnd_global.conc_program_id,
3096 sysdate
3097 FROM bom_operation_networks bon,
3098 bom_operation_sequences bos4, -- dest to op
3099 bom_operation_sequences bos3, -- dest from op
3100 bom_operation_sequences bos2, -- src to op
3101 bom_operation_sequences bos1, -- src from op
3102 mfg_lookups mfgl
3103 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
3104 AND bon.to_op_seq_id = bos2.operation_sequence_id
3105 AND bos1.routing_sequence_id = bos2.routing_sequence_id
3106 AND bos1.routing_sequence_id = x_from_sequence_id
3107 AND bos3.routing_sequence_id = to_sequence_id
3108 AND bos3.operation_seq_num = bos1.operation_seq_num
3109 AND bos3.last_updated_by = bos1.operation_sequence_id
3110 AND bos4.last_updated_by = bos2.operation_sequence_id
3111 AND bos3.operation_type(+) = bos1.operation_type
3112 AND NVL (bos1.eco_for_production, 2) = 2
3113 AND NVL (bos2.eco_for_production, 2) = 2
3114 AND NVL (bos3.eco_for_production, 2) = 2
3115 AND NVL (bos4.eco_for_production, 2) = 2
3116 AND bos4.routing_sequence_id = to_sequence_id
3117 AND bos4.operation_seq_num = bos2.operation_seq_num
3118 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1)
3119 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
3120 AND mfgl.lookup_code = bos1.operation_type
3121 AND bos2.revised_item_sequence_id = p_rev_item_seq_id
3122 MINUS
3123 SELECT
3124 l_from_item_id,
3125 to_org_id,
3126 p_copy_request_id,
3127 NULL,
3128 NULL,
3129 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos1.operation_seq_num, mfgl.meaning),
3130 'BOM_COPY',
3131 sysdate,
3132 user_id,
3133 sysdate,
3134 user_id,
3135 'E',
3136 fnd_global.conc_request_id,
3137 NULL,
3138 fnd_global.conc_program_id,
3139 sysdate
3140 FROM bom_operation_networks bon,
3141 bom_operation_sequences bos4, -- dest to op
3142 bom_operation_sequences bos3, -- dest from op
3143 bom_operation_sequences bos2, -- src to op
3144 bom_operation_sequences bos1, -- src from op
3145 mfg_lookups mfgl
3146 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
3147 AND bon.to_op_seq_id = bos2.operation_sequence_id
3148 AND bos1.routing_sequence_id = bos2.routing_sequence_id
3149 AND bos1.routing_sequence_id = x_from_sequence_id
3150 AND bos3.routing_sequence_id = to_sequence_id
3151 AND bos3.operation_seq_num = bos1.operation_seq_num
3152 AND bos3.last_updated_by = bos1.operation_sequence_id
3153 AND bos4.last_updated_by = bos2.operation_sequence_id
3154 AND bos3.operation_type = bos1.operation_type
3155 AND NVL (bos1.eco_for_production, 2) = 2
3156 AND NVL (bos2.eco_for_production, 2) = 2
3157 AND NVL (bos3.eco_for_production, 2) = 2
3158 AND NVL (bos4.eco_for_production, 2) = 2
3159 AND bos4.routing_sequence_id = to_sequence_id
3160 AND bos4.operation_seq_num = bos2.operation_seq_num
3161 AND NVL (bos4.operation_type, 1) = NVL (bos2.operation_type, 1)
3162 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
3163 AND mfgl.lookup_code = bos1.operation_type
3164 AND bos2.revised_item_sequence_id = p_rev_item_seq_id;
3165 INSERT INTO mtl_interface_errors
3166 (unique_id,
3167 organization_id,
3168 transaction_id,
3169 table_name,
3170 column_name,
3171 error_message,
3172 bo_identifier,
3173 last_update_date,
3174 last_updated_by,
3175 creation_date,
3176 created_by,
3177 message_type,
3178 request_id,
3179 program_application_id,
3180 program_id,
3181 program_update_date
3182 )
3183 SELECT
3184 l_from_item_id,
3185 to_org_id,
3189 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos2.operation_seq_num,mfgl.meaning),
3186 p_copy_request_id,
3187 NULL,
3188 NULL,
3190 'BOM_COPY',
3191 sysdate,
3192 user_id,
3193 sysdate,
3194 user_id,
3195 'E',
3196 fnd_global.conc_request_id,
3197 NULL,
3198 fnd_global.conc_program_id,
3199 sysdate
3200 FROM bom_operation_networks bon,
3201 bom_operation_sequences bos4, -- dest to op
3202 bom_operation_sequences bos3, -- dest from op
3203 bom_operation_sequences bos2, -- src to op
3204 bom_operation_sequences bos1, -- src from op
3205 mfg_lookups mfgl
3206 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
3207 AND bon.to_op_seq_id = bos2.operation_sequence_id
3208 AND bos1.routing_sequence_id = bos2.routing_sequence_id
3209 AND bos1.routing_sequence_id = x_from_sequence_id
3210 AND bos3.routing_sequence_id = to_sequence_id
3211 AND bos3.operation_seq_num = bos1.operation_seq_num
3212 AND bos3.last_updated_by = bos1.operation_sequence_id
3213 AND bos4.last_updated_by = bos2.operation_sequence_id
3214 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
3215 AND NVL (bos1.eco_for_production, 2) = 2
3216 AND NVL (bos2.eco_for_production, 2) = 2
3217 AND NVL (bos3.eco_for_production, 2) = 2
3218 AND NVL (bos4.eco_for_production, 2) = 2
3219 AND bos4.routing_sequence_id = to_sequence_id
3220 AND bos4.operation_seq_num = bos2.operation_seq_num
3221 AND bos4.operation_type(+) = bos2.operation_type
3222 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
3223 AND mfgl.lookup_code = bos2.operation_type
3224 AND bos2.revised_item_sequence_id = p_rev_item_seq_id
3225 MINUS
3226 SELECT
3227 l_from_item_id,
3228 to_org_id,
3229 p_copy_request_id,
3230 NULL,
3231 NULL,
3232 bom_copy_routing.get_message('BOM_CE_NRTG_OPTYPE_NOT_EXISTS',bos2.operation_seq_num,mfgl.meaning),
3233 'BOM_COPY',
3234 sysdate,
3235 user_id,
3236 sysdate,
3237 user_id,
3238 'E',
3239 fnd_global.conc_request_id,
3240 NULL,
3241 fnd_global.conc_program_id,
3242 sysdate
3243 FROM bom_operation_networks bon,
3244 bom_operation_sequences bos4, -- dest to op
3245 bom_operation_sequences bos3, -- dest from op
3246 bom_operation_sequences bos2, -- src to op
3247 bom_operation_sequences bos1, -- src from op
3248 mfg_lookups mfgl
3249 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
3250 AND bon.to_op_seq_id = bos2.operation_sequence_id
3251 AND bos1.routing_sequence_id = bos2.routing_sequence_id
3252 AND bos1.routing_sequence_id = x_from_sequence_id
3253 AND bos3.routing_sequence_id = to_sequence_id
3254 AND bos3.operation_seq_num = bos1.operation_seq_num
3255 AND bos3.last_updated_by = bos1.operation_sequence_id
3256 AND bos4.last_updated_by = bos2.operation_sequence_id
3257 AND NVL (bos3.operation_type, 1) = NVL (bos1.operation_type, 1)
3258 AND NVL (bos1.eco_for_production, 2) = 2
3259 AND NVL (bos2.eco_for_production, 2) = 2
3260 AND NVL (bos3.eco_for_production, 2) = 2
3261 AND NVL (bos4.eco_for_production, 2) = 2
3262 AND bos4.routing_sequence_id = to_sequence_id
3263 AND bos4.operation_seq_num = bos2.operation_seq_num
3264 AND bos4.operation_type = bos2.operation_type
3265 AND mfgl.lookup_type = 'BOM_OPERATION_TYPE'
3266 AND mfgl.lookup_code = bos2.operation_type
3267 AND bos2.revised_item_sequence_id = p_rev_item_seq_id;
3268
3269
3270 END IF; /* IF p_log_errors = 'Y' */
3271
3272
3273 --INSERT OPERATION RESOURCES
3274 --NULL OUT RESOURCE_OFFSET_PERCENT
3275 --SET ASSIGNED UNITS TO 1 IF RESOURCES IN DEPT IS 24 HRS -- removed restriction as per bug 2661684
3276 --UPDATE RESOURCE_ID TO THAT OF COPY_TO_ORG
3277 --LEAVE OUT RESOURCES THAT DO NOT EXIST IN COPY_TO_ORG DEPT.
3278 sql_stmt_num := 20;
3279
3280 INSERT INTO bom_operation_resources
3281 (operation_sequence_id,
3282 resource_seq_num,
3283 resource_id,
3284 activity_id,
3285 standard_rate_flag,
3286 assigned_units,
3287 usage_rate_or_amount,
3288 usage_rate_or_amount_inverse,
3289 basis_type,
3290 schedule_flag,
3291 last_update_date,
3292 last_updated_by,
3293 creation_date,
3294 created_by,
3295 last_update_login,
3296 resource_offset_percent,
3297 autocharge_type,
3298 attribute_category,
3299 attribute1,
3300 attribute2,
3301 attribute3,
3302 attribute4,
3306 attribute8,
3303 attribute5,
3304 attribute6,
3305 attribute7,
3307 attribute9,
3308 attribute10,
3309 attribute11,
3310 attribute12,
3311 attribute13,
3312 attribute14,
3313 attribute15,
3314 request_id,
3315 program_application_id,
3316 program_id,
3317 program_update_date,
3318 schedule_seq_num,
3319 substitute_group_num,
3320 principle_flag,
3321 setup_id,
3322 change_notice,
3323 acd_type,
3324 original_system_reference
3325 )
3326 SELECT a.operation_sequence_id,
3327 b.resource_seq_num,
3328 d.resource_id,
3329 b.activity_id,
3330 b.standard_rate_flag,
3331 -- DECODE(E.AVAILABLE_24_HOURS_FLAG, 1, 1, B.ASSIGNED_UNITS), -- changed for bug 2661684
3332 b.assigned_units,
3333 b.usage_rate_or_amount,
3334 b.usage_rate_or_amount_inverse,
3335 b.basis_type,
3336 b.schedule_flag,
3337 SYSDATE,
3338 b.operation_sequence_id, -- Instead of last_updated_by
3339 SYSDATE,
3340 NVL (b.schedule_seq_num, user_id), -- Instead of created by
3341 user_id,
3342 NULL,
3343 b.autocharge_type,
3344 b.attribute_category,
3345 b.attribute1,
3346 b.attribute2,
3347 b.attribute3,
3348 b.attribute4,
3349 b.attribute5,
3350 b.attribute6,
3351 b.attribute7,
3352 b.attribute8,
3353 b.attribute9,
3354 b.attribute10,
3355 b.attribute11,
3356 b.attribute12,
3357 b.attribute13,
3358 b.attribute14,
3359 b.attribute15,
3360 fnd_global.conc_request_id,
3361 NULL,
3362 fnd_global.conc_program_id,
3363 sysdate,
3364 b.schedule_seq_num,
3365 b.substitute_group_num,
3366 b.principle_flag,
3367 b.setup_id,
3368 DECODE (p_routing_or_eco, 1, b.change_notice, p_e_change_notice),
3369 DECODE (p_routing_or_eco, 1, b.acd_type, 1),
3370 -- Add is the action for ECO
3371 b.original_system_reference
3372 FROM bom_operation_sequences a,
3373 bom_operation_resources b,
3374 bom_resources c,
3375 bom_resources d
3376 -- ,BOM_DEPARTMENT_RESOURCES E
3377 WHERE a.routing_sequence_id = to_sequence_id
3378 AND a.last_updated_by = b.operation_sequence_id
3379 AND b.resource_id = c.resource_id
3380 AND c.resource_code = d.resource_code
3381 AND d.organization_id = to_org_id
3382 -- AND D.RESOURCE_ID = E.RESOURCE_ID
3383 -- AND E.DEPARTMENT_ID = A.DEPARTMENT_ID
3384 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
3385 AND a.revised_item_sequence_id = p_rev_item_seq_id;
3386
3387 IF p_log_errors = 'Y' THEN
3388 INSERT INTO mtl_interface_errors
3389 (unique_id,
3390 organization_id,
3391 transaction_id,
3392 table_name,
3393 column_name,
3394 error_message,
3395 bo_identifier,
3396 last_update_date,
3397 last_updated_by,
3398 creation_date,
3399 created_by,
3400 message_type,
3401 request_id,
3402 program_application_id,
3403 program_id,
3404 program_update_date
3405 )
3406 SELECT
3407 l_from_item_id,
3408 to_org_id,
3409 p_copy_request_id,
3410 NULL,
3411 NULL,
3412 bom_copy_routing.get_message('BOM_CE_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code,'RES'),
3413 'BOM_COPY',
3414 sysdate,
3415 user_id,
3416 sysdate,
3417 user_id,
3418 'E',
3419 fnd_global.conc_request_id,
3420 NULL,
3421 fnd_global.conc_program_id,
3422 sysdate
3423 FROM bom_operation_sequences a,
3424 bom_operation_resources b,
3425 bom_operation_sequences fbor,
3426 bom_resources c,
3427 bom_resources d
3428 WHERE a.routing_sequence_id = to_sequence_id
3429 AND a.last_updated_by = b.operation_sequence_id
3430 AND b.resource_id = c.resource_id
3431 AND c.resource_code = d.resource_code(+)
3432 AND d.organization_id = to_org_id
3433 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
3434 AND fbor.operation_sequence_id = b.operation_sequence_id
3435 AND a.revised_item_sequence_id = p_rev_item_seq_id
3436 MINUS
3440 p_copy_request_id,
3437 SELECT
3438 l_from_item_id,
3439 to_org_id,
3441 NULL,
3442 NULL,
3443 bom_copy_routing.get_message('BOM_CE_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code,'RES'),
3444 'BOM_COPY',
3445 sysdate,
3446 user_id,
3447 sysdate,
3448 user_id,
3449 'E',
3450 fnd_global.conc_request_id,
3451 NULL,
3452 fnd_global.conc_program_id,
3453 sysdate
3454 FROM bom_operation_sequences a,
3455 bom_operation_resources b,
3456 bom_operation_sequences fbor,
3457 bom_resources c,
3458 bom_resources d
3459 WHERE a.routing_sequence_id = to_sequence_id
3460 AND a.last_updated_by = b.operation_sequence_id
3461 AND b.resource_id = c.resource_id
3462 AND c.resource_code = d.resource_code
3463 AND d.organization_id = to_org_id
3464 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
3465 AND fbor.operation_sequence_id = b.operation_sequence_id
3466 AND a.revised_item_sequence_id = p_rev_item_seq_id;
3467 END IF; /* IF p_log_errors = 'Y' */
3468
3469
3470 copy_resources := SQL%ROWCOUNT;
3471
3472 IF (from_org_id = to_org_id)
3473 THEN
3474 total_resources := SQL%ROWCOUNT;
3475 END IF;
3476
3477 -- UPDATE OPERATION RESOURCES TO
3478 -- IF ACTIVITY IS ORG DEPENDENT, NULL IT OUT
3479 -- IF NO CONVERSION TO HOUR UOM CLASS, SET SCHEDUEL FLAG TO NO
3480 -- If there exist a similar setup in the other org, copy the correct setup_id
3481 -- else null out the setup_id field -- bug 2751946
3482 -- ONLY FOR INTER ORG COPY
3483 IF (from_org_id <> to_org_id)
3484 THEN
3485 sql_stmt_num := 30;
3486
3487 UPDATE bom_operation_resources a
3488 SET activity_id =
3489 (SELECT DECODE (organization_id, NULL, activity_id, NULL)
3490 FROM cst_activities
3491 WHERE activity_id = a.activity_id),
3492 schedule_flag =
3493 (SELECT DECODE (c.unit_of_measure,
3494 NULL, 2,
3495 hour_uom_code_v, a.schedule_flag,
3496 DECODE (b.uom_class,
3497 hour_uom_class_v, a.schedule_flag,
3498 2
3499 )
3500 )
3501 FROM mtl_units_of_measure b,
3502 bom_resources c
3503 WHERE a.resource_id = c.resource_id
3504 AND c.unit_of_measure = b.unit_of_measure(+)),
3505 setup_id =
3506 (SELECT brs.setup_id
3507 FROM bom_resource_setups brs,
3508 bom_setup_types bst -- added for bug 2751946
3509 WHERE brs.resource_id = a.resource_id
3510 AND brs.setup_id = bst.setup_id
3511 AND bst.setup_code = (SELECT setup_code
3512 FROM bom_setup_types
3513 WHERE setup_id = a.setup_id))
3514 WHERE a.operation_sequence_id IN (
3515 SELECT operation_sequence_id
3516 FROM bom_operation_sequences
3517 WHERE routing_sequence_id =
3518 to_sequence_id);
3519 END IF;
3520
3521 -- Bug Fix 2991810
3522 sql_stmt_num := 25;
3523
3524 INSERT INTO bom_sub_operation_resources
3525 (operation_sequence_id,
3526 substitute_group_num,
3527 resource_id,
3528 schedule_seq_num,
3529 replacement_group_num,
3530 activity_id,
3531 standard_rate_flag,
3532 assigned_units,
3533 usage_rate_or_amount,
3534 usage_rate_or_amount_inverse,
3535 basis_type,
3536 schedule_flag,
3537 last_update_date,
3538 last_updated_by,
3539 creation_date,
3540 created_by,
3541 last_update_login,
3542 resource_offset_percent,
3543 autocharge_type,
3544 attribute_category,
3545 request_id,
3546 program_application_id,
3547 program_id,
3548 program_update_date,
3549 attribute1,
3550 attribute2,
3551 attribute3,
3552 attribute4,
3553 attribute5,
3554 attribute6,
3555 attribute7,
3556 attribute8,
3557 attribute9,
3558 attribute10,
3559 attribute11,
3560 attribute12,
3561 attribute13,
3562 attribute14,
3566 change_notice,
3563 attribute15,
3564 principle_flag,
3565 setup_id,
3567 acd_type,
3568 original_system_reference
3569 )
3570 SELECT DISTINCT a.operation_sequence_id,
3571 b.substitute_group_num,
3572 d.resource_id,
3573 b.schedule_seq_num,
3574 b.replacement_group_num,
3575 b.activity_id,
3576 b.standard_rate_flag,
3577 b.assigned_units,
3578 b.usage_rate_or_amount,
3579 b.usage_rate_or_amount_inverse,
3580 b.basis_type,
3581 b.schedule_flag,
3582 SYSDATE,
3583 user_id,
3584 SYSDATE,
3585 user_id,
3586 NULL,
3587 b.resource_offset_percent,
3588 b.autocharge_type,
3589 b.attribute_category,
3590 fnd_global.conc_request_id,
3591 NULL,
3592 fnd_global.conc_program_id,
3593 sysdate,
3594 b.attribute1,
3595 b.attribute2,
3596 b.attribute3,
3597 b.attribute4,
3598 b.attribute5,
3599 b.attribute6,
3600 b.attribute7,
3601 b.attribute8,
3602 b.attribute9,
3603 b.attribute10,
3604 b.attribute11,
3605 b.attribute12,
3606 b.attribute13,
3607 b.attribute14,
3608 b.attribute15,
3609 b.principle_flag,
3610 b.setup_id,
3611 DECODE (p_routing_or_eco,
3612 1, b.change_notice,
3613 p_e_change_notice
3614 ),
3615 DECODE (p_routing_or_eco, 1, b.acd_type, 1),
3616 -- Add is the action for ECO
3617 b.original_system_reference
3618 FROM bom_operation_resources a,
3619 bom_sub_operation_resources b,
3620 bom_resources c,
3621 bom_resources d,
3622 bom_operation_sequences bos
3623 WHERE a.last_updated_by = b.operation_sequence_id
3624 AND bos.operation_sequence_id = b.operation_sequence_id
3625 AND bos.revised_item_sequence_id = p_rev_item_seq_id
3626 AND a.created_by = b.schedule_seq_num
3627 AND b.resource_id = c.resource_id
3628 AND c.resource_code = d.resource_code
3629 AND d.organization_id = to_org_id
3630 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE;
3631
3632 IF p_log_errors = 'Y' THEN
3633 INSERT INTO mtl_interface_errors
3634 (unique_id,
3635 organization_id,
3636 transaction_id,
3637 table_name,
3638 column_name,
3639 error_message,
3640 bo_identifier,
3641 last_update_date,
3642 last_updated_by,
3643 creation_date,
3644 created_by,
3645 message_type,
3646 request_id,
3647 program_application_id,
3648 program_id,
3649 program_update_date
3650 )
3651 SELECT DISTINCT
3652 l_from_item_id,
3653 to_org_id,
3654 p_copy_request_id,
3655 NULL,
3656 NULL,
3657 bom_copy_routing.get_message('BOM_CE_SUB_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code, 'SUB'),
3658 'BOM_COPY',
3659 sysdate,
3660 user_id,
3661 sysdate,
3662 user_id,
3663 'E',
3664 fnd_global.conc_request_id,
3665 NULL,
3666 fnd_global.conc_program_id,
3667 sysdate
3668 FROM bom_operation_resources a,
3669 bom_operation_sequences fbor,
3670 bom_sub_operation_resources b,
3671 bom_resources c,
3672 bom_resources d
3673 WHERE a.last_updated_by = b.operation_sequence_id
3674 AND fbor.revised_item_sequence_id = p_rev_item_seq_id
3675 AND b.operation_sequence_id = fbor.operation_sequence_id
3676 AND a.created_by = b.schedule_seq_num
3677 AND b.resource_id = c.resource_id
3678 AND c.resource_code = d.resource_code(+)
3679 AND d.organization_id = to_org_id
3680 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE
3681 MINUS
3682 SELECT DISTINCT
3683 l_from_item_id,
3684 to_org_id,
3685 p_copy_request_id,
3686 NULL,
3687 NULL,
3691 user_id,
3688 bom_copy_routing.get_message('BOM_CE_SUB_OPER_RES_NOT_EXISTS',fbor.operation_seq_num, c.resource_code, 'SUB'),
3689 'BOM_COPY',
3690 sysdate,
3692 sysdate,
3693 user_id,
3694 'E',
3695 fnd_global.conc_request_id,
3696 NULL,
3697 fnd_global.conc_program_id,
3698 sysdate
3699 FROM bom_operation_resources a,
3700 bom_operation_sequences fbor,
3701 bom_sub_operation_resources b,
3702 bom_resources c,
3703 bom_resources d
3704 WHERE a.last_updated_by = b.operation_sequence_id
3705 AND a.created_by = b.schedule_seq_num
3706 AND fbor.revised_item_sequence_id = p_rev_item_seq_id
3707 AND b.operation_sequence_id = fbor.operation_sequence_id
3708 AND b.resource_id = c.resource_id
3709 AND c.resource_code = d.resource_code
3710 AND d.organization_id = to_org_id
3711 AND NVL (d.disable_date, SYSDATE + 1) > SYSDATE;
3712 END IF; /* IF p_log_errors = 'Y' */
3713 copy_sub_resources := SQL%ROWCOUNT;
3714
3715 IF (from_org_id = to_org_id)
3716 THEN
3717 total_sub_resources := SQL%ROWCOUNT;
3718 END IF;
3719
3720 IF (from_org_id <> to_org_id)
3721 THEN
3722 sql_stmt_num := 35;
3723
3724 UPDATE bom_sub_operation_resources a
3725 SET activity_id =
3726 (SELECT DECODE (organization_id, NULL, activity_id, NULL)
3727 FROM cst_activities
3728 WHERE activity_id = a.activity_id),
3729 schedule_flag =
3730 (SELECT DECODE (c.unit_of_measure,
3731 NULL, 2,
3732 hour_uom_code_v, a.schedule_flag,
3733 DECODE (b.uom_class,
3734 hour_uom_class_v, a.schedule_flag,
3735 2
3736 )
3737 )
3738 FROM mtl_units_of_measure b,
3739 bom_resources c
3740 WHERE a.resource_id = c.resource_id
3741 AND c.unit_of_measure = b.unit_of_measure(+)),
3742 setup_id =
3743 (SELECT brs.setup_id
3744 FROM bom_resource_setups brs,
3745 bom_setup_types bst
3746 WHERE brs.resource_id = a.resource_id
3747 AND brs.setup_id = bst.setup_id
3748 AND bst.setup_code = (SELECT setup_code
3749 FROM bom_setup_types
3750 WHERE setup_id = a.setup_id))
3751 WHERE a.operation_sequence_id IN (
3752 SELECT operation_sequence_id
3753 FROM bom_operation_sequences
3754 WHERE routing_sequence_id =
3755 to_sequence_id);
3756 END IF;
3757
3758 -- Bug Fix 2991810
3759
3760 -- UPDATE LAST_UPDATED_BY COLUMN USED TO STORE COPY_FROM OP_SEQ_IDS
3761 sql_stmt_num := 55;
3762
3763 UPDATE bom_operation_sequences
3764 SET last_updated_by = user_id
3765 WHERE routing_sequence_id = to_sequence_id;
3766
3767 -- Bug Fix 2991810
3768 sql_stmt_num := 65;
3769
3770 UPDATE bom_operation_resources
3771 SET last_updated_by = user_id,
3772 created_by = user_id
3773 WHERE operation_sequence_id IN (
3774 SELECT operation_sequence_id
3775 FROM bom_operation_sequences
3776 WHERE routing_sequence_id =
3777 to_sequence_id);
3778
3779 sql_stmt_num := 39;
3780
3781 SELECT COUNT (*)
3782 INTO copy_instrs
3783 FROM fnd_attached_documents b,
3784 bom_operation_sequences a
3785 WHERE a.routing_sequence_id = to_sequence_id
3786 AND a.operation_sequence_id = b.pk1_value
3787 AND b.entity_name = 'BOM_OPERATION_SEQUENCES';
3788
3789 --begin bug fix 3473851
3790 sql_stmt_num := 40;
3791
3792 SELECT COUNT (*)
3793 INTO copy_hdr_instrs
3794 FROM fnd_attached_documents b,
3795 bom_operational_routings a
3796 WHERE a.routing_sequence_id = to_sequence_id
3797 AND a.routing_sequence_id = b.pk1_value
3798 AND b.entity_name = 'BOM_OPERATIONAL_ROUTINGS';
3799
3800 --end bug fix 3473851
3801 IF (from_org_id = to_org_id)
3802 THEN
3803 total_instructions := copy_instrs;
3804 total_hdr_instructions := copy_hdr_instrs; --bug 3473851
3805 END IF;
3806
3807 fnd_message.set_name ('BOM', 'BOM_ROUTING_COPY_DONE');
3808 fnd_message.set_token ('ENTITY1', copy_operations);
3809 fnd_message.set_token ('ENTITY2', total_opseqs);
3810 fnd_message.set_token ('ENTITY3', copy_resources);
3814 fnd_message.set_token ('ENTITY8', total_sub_resources); -- Bug2991810
3811 fnd_message.set_token ('ENTITY4', total_resources);
3812 fnd_message.set_token ('ENTITY7', copy_sub_resources);
3813 -- Bug2991810 the message also needs to be modified
3815 fnd_message.set_token ('ENTITY5', copy_instrs);
3816 fnd_message.set_token ('ENTITY6', total_instructions);
3817 fnd_message.set_token ('ENTITY9', copy_hdr_instrs); --bug 3473851
3818 fnd_message.set_token ('ENTITY10', total_hdr_instructions);
3819 --bug 3473851
3820 EXCEPTION
3821 WHEN OTHERS
3822 THEN
3823 err_msg :=
3824 'COPY_ROUTING (' || TO_CHAR (sql_stmt_num) || ') '
3825 || SQLERRM;
3826 fnd_message.set_name ('BOM', 'BOM_SQL_ERR');
3827 fnd_message.set_token ('ENTITY', err_msg);
3828 ROLLBACK TO begin_routing_copy;
3829 app_exception.raise_exception;
3830 END copy_routing_for_revised_item;
3831
3832 PROCEDURE copy_attachments(p_from_sequence_id IN NUMBER,
3833 p_to_sequence_id IN NUMBER,
3834 p_user_id IN NUMBER)
3835 IS
3836 CURSOR source_rtg
3837 IS
3838 SELECT operation_sequence_id,
3839 last_updated_by
3840 FROM bom_operation_sequences
3841 WHERE routing_sequence_id = p_to_sequence_id
3842 AND NVL (eco_for_production, 2) = 2;
3843 BEGIN
3844
3845 BEGIN
3846 FOR x_op IN source_rtg
3847 LOOP
3848 fnd_attached_documents2_pkg.copy_attachments
3849 (x_from_entity_name => 'BOM_OPERATION_SEQUENCES',
3850 x_from_pk1_value => x_op.last_updated_by,
3851 x_from_pk2_value => '',
3852 x_from_pk3_value => '',
3853 x_from_pk4_value => '',
3854 x_from_pk5_value => '',
3855 x_to_entity_name => 'BOM_OPERATION_SEQUENCES',
3856 x_to_pk1_value => x_op.operation_sequence_id,
3857 x_to_pk2_value => '',
3858 x_to_pk3_value => '',
3859 x_to_pk4_value => '',
3860 x_to_pk5_value => '',
3861 x_created_by => p_user_id,
3862 x_last_update_login => '',
3863 x_program_application_id => '',
3864 x_program_id => fnd_global.conc_program_id,
3865 x_request_id => fnd_global.conc_request_id
3866 );
3867 END LOOP;
3868
3869 END;
3870
3871 --begin bug fix 3473851
3872 fnd_attached_documents2_pkg.copy_attachments
3873 (x_from_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
3874 x_from_pk1_value => p_from_sequence_id,
3875 x_from_pk2_value => '',
3876 x_from_pk3_value => '',
3877 x_from_pk4_value => '',
3878 x_from_pk5_value => '',
3879 x_to_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
3880 x_to_pk1_value => p_to_sequence_id,
3881 x_to_pk2_value => '',
3882 x_to_pk3_value => '',
3883 x_to_pk4_value => '',
3884 x_to_pk5_value => '',
3885 x_created_by => p_user_id,
3886 x_last_update_login => '',
3887 x_program_application_id => '',
3888 x_program_id => fnd_global.conc_program_id,
3889 x_request_id => fnd_global.conc_request_id
3890 );
3891 --end bug fix 3473851
3892 END copy_attachments;
3893
3894 PROCEDURE update_last_updated_by (
3895 p_user_id IN NUMBER
3896 ,p_to_sequence_id IN NUMBER )
3897 IS
3898 BEGIN
3899
3900 UPDATE bom_operation_sequences bos
3901 SET last_updated_by = p_user_id
3902 WHERE bos.routing_sequence_id = p_to_sequence_id;
3903
3904 END;
3905
3906 END bom_copy_routing;