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