[Home] [Help]
PACKAGE BODY: APPS.ENG_CANCEL_ECO
Source
1 PACKAGE BODY ENG_CANCEL_ECO AS
2 /* $Header: ENGCNCLB.pls 120.4.12010000.2 2008/11/14 09:50:05 gliang ship $ */
3
4
5 Procedure Cancel_Eco (
6 org_id number ,
7 change_order varchar2,
8 user_id number,
9 login number
10 -- comment varchar2
11 ) IS
12 err_text varchar2(2000);
13 stmt_num number;
14 l_revised_item_sequence_id number;
15 l_new_item_revision_id number;
16 l_revised_item_id number;
17 l_change_id number;
18 l_return_status varchar2(4000);
19 l_msg_data varchar2(4000);
20 l_msg_count number;
21 l_new_change_id number;
22 l_cm_type_code varchar2(2000);
23
24 cursor delete_attachments is
25 select change_id,revised_item_id,new_item_revision_id,revised_item_sequence_id
26 from eng_revised_items
27 where organization_id = org_id
28 and change_notice = change_order
29 and status_type not in (5,6);
30
31 CURSOR update_att_status is
32 SELECT change_id, change_mgmt_type_code
33 FROM eng_engineering_changes
34 WHERE organization_id = org_id
35 and change_notice = change_order;
36
37 -- Changes for Bug 3668603
38 -- Cursor to fetch the routing_sequence_id for cancelled revised items
39 CURSOR c_cancelled_RI IS
40 SELECT DISTINCT ri.routing_sequence_id
41 FROM ENG_REVISED_ITEMS ri
42 WHERE TRUNC(ri.last_update_date) = TRUNC(SYSDATE)
43 AND ri.status_type = 5 -- Cancelled
44 AND ri.organization_id = org_id
45 AND ri.change_notice = change_order
46 ORDER BY routing_sequence_id desc;
47
48 -- Cursor to check if the routing header used in the revised item has any references
49 Cursor c_check_rtg_header_del(p_routing_sequence_id NUMBER) is
50 select routing_sequence_id
51 from bom_operational_routings bor
52 WHERE bor.pending_from_ecn IS NOT NULL
53 And bor.routing_sequence_id = p_routing_sequence_id
54 and not exists (select null
55 from BOM_OPERATION_SEQUENCES bos
56 where bos.routing_sequence_id = bor.routing_sequence_id
57 and (bos.change_notice is null
58 or
59 bos.change_notice <> change_order))
60 and ((bor.alternate_routing_designator is null
61 and not exists (select null
62 from BOM_OPERATIONAL_ROUTINGS bor2
63 where bor2.organization_id = bor.organization_id
64 and bor2.assembly_item_id = bor.assembly_item_id
65 and bor2.alternate_routing_designator is not null)
66 and not exists (select null
67 from MTL_RTG_ITEM_REVISIONS mriv
68 where mriv.organization_id = bor.organization_id
69 and mriv.inventory_item_id = bor.assembly_item_id
70 and mriv.implementation_date is not null
71 and mriv.change_notice is null))
72 or
73 (bor.alternate_routing_designator is not null))
74 and not exists (select null
75 from ENG_REVISED_ITEMS eri
76 where eri.organization_id = bor.organization_id
77 and eri.routing_sequence_id = bor.routing_sequence_id
78 and eri.change_notice <> change_order
79 and eri.status_type <> 5);
80
81 Cursor is_editable_common_bom( org_id number , change_order varchar2) is
82 select bill_sequence_id
83 from BOM_BILL_OF_MATERIALS
84 where common_bill_sequence_id <> source_bill_sequence_id
85 and bill_sequence_id = common_bill_sequence_id
86 and pending_from_ECN = change_order
87 and organization_id = org_id;
88
89 l_del_rtg_header NUMBER;
90 l_routing_sequence_id NUMBER;
91 Common_bom NUMBER;
92 -- End changes for bug 3668603
93
94 Begin
95
96 --Bug : 3507992 Calling the following API on all cases, i.e. attachments made to new item revision,
97 --existing item revision or at the Item Level.
98
99 begin
100 open delete_attachments;
101 loop
102 fetch delete_attachments into l_change_id,l_revised_item_id,l_new_item_revision_id,l_revised_item_sequence_id;
103 exit when delete_attachments%NOTFOUND ;
104
105 ENG_ATTACHMENT_IMPLEMENTATION.delete_attachments_for_curr_co
106 (p_api_version => 1.0
107 ,p_change_id => l_change_id
108 ,p_rev_item_seq_id => l_revised_item_sequence_id
109 ,x_return_status => l_return_status
110 ,x_msg_data => l_msg_data
111 ,x_msg_count => l_msg_count
112 );
113 end loop;
114 close delete_attachments;
115 exception
116 when others then
117 close delete_attachments;
118 end;
119
120 begin
121 OPEN update_att_status;
122 loop
123 fetch update_att_status into l_new_change_id,l_cm_type_code;
124 exit when update_att_status%NOTFOUND ;
125
126 IF l_cm_type_code = 'ATTACHMENT_APPROVAL' OR l_cm_type_code = 'ATTACHMENT_REVIEW' then
127 Change_Att_Status (l_new_change_id, user_id, login);
128 END if;
129 END loop;
130 close update_att_status;
131 exception
132 when others then
133 close update_att_status;
134 end;
135
136 /*
137 ** set cancel date and comments on ECO
138 stmt_num := 5;
139 UPDATE ENG_ENGINEERING_CHANGES
140 SET CANCELLATION_DATE = SYSDATE,
141 STATUS_TYPE = 5,
142 CANCELLATION_COMMENTS = comment,
143 LAST_UPDATED_BY = user_id,
144 LAST_UPDATE_LOGIN = login
145 WHERE ORGANIZATION_ID = org_id
146 AND CHANGE_NOTICE = change_order;
147 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eec');
148 */
149
150 /*
151 ** set cancellation date of all pending revised items on ECO
152 */
153 stmt_num := 10;
154 UPDATE ENG_REVISED_ITEMS
155 SET CANCELLATION_DATE = SYSDATE,
156 STATUS_TYPE = 5,
157 LAST_UPDATED_BY = user_id,
158 LAST_UPDATE_LOGIN = login
159 WHERE ORGANIZATION_ID = org_id
160 AND CHANGE_NOTICE = change_order
161 AND STATUS_TYPE NOT IN (5,6);
162 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eri');
163
164 /*
165 ** delete substitute components of all pending revised items on ECO
166 */
167 stmt_num := 20;
168 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
169 WHERE SC.COMPONENT_SEQUENCE_ID IN
170 (SELECT IC.COMPONENT_SEQUENCE_ID
171 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
172 WHERE RI.ORGANIZATION_ID = org_id
173 AND RI.CHANGE_NOTICE = change_order
174 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
175 AND IC.IMPLEMENTATION_DATE IS NULL);
176 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted in bsc');
177
178 /*
179 ** delete reference designators of all pending revised items on ECO
180 */
181 stmt_num := 30;
182 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
183 WHERE RD.COMPONENT_SEQUENCE_ID IN
184 (SELECT IC.COMPONENT_SEQUENCE_ID
185 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
186 WHERE RI.ORGANIZATION_ID = org_id
187 AND RI.CHANGE_NOTICE = change_order
188 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
189 AND IC.IMPLEMENTATION_DATE IS NULL);
190 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted in brd');
191
192 /*
193 ** insert the cancelled rev components into eng_revised_components
194 */
195 stmt_num := 40;
196 INSERT INTO ENG_REVISED_COMPONENTS (
197 COMPONENT_SEQUENCE_ID,
198 COMPONENT_ITEM_ID,
199 OPERATION_SEQUENCE_NUM,
200 BILL_SEQUENCE_ID,
201 CHANGE_NOTICE,
202 EFFECTIVITY_DATE,
203 COMPONENT_QUANTITY,
204 COMPONENT_YIELD_FACTOR,
205 LAST_UPDATE_DATE,
206 LAST_UPDATED_BY,
207 CREATION_DATE,
208 CREATED_BY,
209 LAST_UPDATE_LOGIN,
210 CANCELLATION_DATE,
211 OLD_COMPONENT_SEQUENCE_ID,
212 ITEM_NUM,
213 WIP_SUPPLY_TYPE,
214 COMPONENT_REMARKS,
215 SUPPLY_SUBINVENTORY,
216 SUPPLY_LOCATOR_ID,
217 DISABLE_DATE,
218 ACD_TYPE,
219 PLANNING_FACTOR,
220 QUANTITY_RELATED,
221 SO_BASIS,
222 OPTIONAL,
223 MUTUALLY_EXCLUSIVE_OPTIONS,
224 INCLUDE_IN_COST_ROLLUP,
225 CHECK_ATP,
226 SHIPPING_ALLOWED,
227 REQUIRED_TO_SHIP,
228 REQUIRED_FOR_REVENUE,
229 INCLUDE_ON_SHIP_DOCS,
230 LOW_QUANTITY,
231 HIGH_QUANTITY,
232 REVISED_ITEM_SEQUENCE_ID,
233 ATTRIBUTE_CATEGORY,
234 ATTRIBUTE1,
235 ATTRIBUTE2,
236 ATTRIBUTE3,
237 ATTRIBUTE4,
238 ATTRIBUTE5,
239 ATTRIBUTE6,
240 ATTRIBUTE7,
241 ATTRIBUTE8,
242 ATTRIBUTE9,
243 ATTRIBUTE10,
244 ATTRIBUTE11,
245 ATTRIBUTE12,
246 ATTRIBUTE13,
247 ATTRIBUTE14,
248 ATTRIBUTE15,
249 BASIS_TYPE)
250 SELECT
251 IC.COMPONENT_SEQUENCE_ID,
252 IC.COMPONENT_ITEM_ID,
253 IC.OPERATION_SEQ_NUM,
254 IC.BILL_SEQUENCE_ID,
255 IC.CHANGE_NOTICE,
256 IC.EFFECTIVITY_DATE,
257 IC.COMPONENT_QUANTITY,
258 IC. COMPONENT_YIELD_FACTOR,
259 SYSDATE,
260 user_id,
261 SYSDATE,
262 user_id,
263 login,
264 sysdate,
265 IC.OLD_COMPONENT_SEQUENCE_ID,
266 IC.ITEM_NUM,
267 IC.WIP_SUPPLY_TYPE,
268 IC.COMPONENT_REMARKS,
269 IC.SUPPLY_SUBINVENTORY,
270 IC.SUPPLY_LOCATOR_ID,
271 IC.DISABLE_DATE,
272 IC.ACD_TYPE,
273 IC.PLANNING_FACTOR,
274 IC.QUANTITY_RELATED,
275 IC.SO_BASIS,
276 IC.OPTIONAL,
277 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
278 IC.INCLUDE_IN_COST_ROLLUP,
279 IC.CHECK_ATP,
280 IC.SHIPPING_ALLOWED,
281 IC.REQUIRED_TO_SHIP,
282 IC.REQUIRED_FOR_REVENUE,
283 IC.INCLUDE_ON_SHIP_DOCS,
284 IC.LOW_QUANTITY,
285 IC.HIGH_QUANTITY,
286 IC.REVISED_ITEM_SEQUENCE_ID,
287 IC.ATTRIBUTE_CATEGORY,
288 IC.ATTRIBUTE1,
289 IC.ATTRIBUTE2,
290 IC.ATTRIBUTE3,
291 IC.ATTRIBUTE4,
292 IC.ATTRIBUTE5,
293 IC.ATTRIBUTE6,
294 IC.ATTRIBUTE7,
295 IC.ATTRIBUTE8,
296 IC.ATTRIBUTE9,
297 IC.ATTRIBUTE10,
298 IC.ATTRIBUTE11,
299 IC.ATTRIBUTE12,
300 IC.ATTRIBUTE13,
301 IC.ATTRIBUTE14,
302 IC.ATTRIBUTE15,
303 IC.BASIS_TYPE
304 FROM BOM_INVENTORY_COMPONENTS IC, ENG_REVISED_ITEMS RI
305 WHERE RI.ORGANIZATION_ID = org_id
306 AND RI.CHANGE_NOTICE = change_order
307 AND IC.CHANGE_NOTICE = RI.CHANGE_NOTICE
308 AND IC.REVISED_ITEM_SEQUENCE_ID = RI.REVISED_ITEM_SEQUENCE_ID
309 AND RI.BILL_SEQUENCE_ID = IC.BILL_SEQUENCE_ID
310 AND IC.IMPLEMENTATION_DATE IS NULL;
311 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows inserted in erc');
312
313 /*
314 ** delete the rows from bom_inventory_components
315 */
316 stmt_num := 50;
317 DELETE FROM BOM_INVENTORY_COMPONENTS IC
318 WHERE CHANGE_NOTICE = change_order
319 AND IMPLEMENTATION_DATE IS NULL
320 AND REVISED_ITEM_SEQUENCE_ID IN (SELECT REVISED_ITEM_SEQUENCE_ID
321 FROM ENG_REVISED_ITEMS ERI
322 WHERE ERI.ORGANIZATION_ID = org_id
323 AND ERI.CHANGE_NOTICE = change_order
324 AND ERI.STATUS_TYPE = 5);
325 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bic');
326 --------------------------------------------------------------------
327 /*Special handling for Editable common bom*/
328 for Common_bom in is_editable_common_bom(org_id,change_order) loop
329
330 /*
331 ** delete the rows from bom_inventory_components
332 */
333 DELETE FROM BOM_INVENTORY_COMPONENTS IC
334 WHERE IC.BILL_SEQUENCE_ID =Common_bom.bill_sequence_id ;
335 END loop;
336
337
338 /* ADDED BY VHYMAVAT FOR THE BUG 2647795 TO HANDLE ROUTINGS*/
339 --------------------------------------------------------------------
340
341 -- Delete substitute operation resources of all pending revised items on ECO
342 DELETE FROM BOM_SUB_OPERATION_RESOURCES sor
343 WHERE EXISTS (SELECT NULL
344 FROM BOM_OPERATION_SEQUENCES bos
345 , ENG_REVISED_ITEMS ri
346 WHERE sor.operation_sequence_id = bos.operation_sequence_id
347 AND bos.implementation_date IS NULL
348 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
349 AND ri.status_type = 5 -- Cancelled
350 AND ri.organization_id = org_id
351 AND ri.change_notice =change_order
352 ) ;
353
354 -- Delete operation resources of all pending revised items on ECO
355 DELETE FROM BOM_OPERATION_RESOURCES bor
356 WHERE EXISTS (SELECT NULL
357 FROM BOM_OPERATION_SEQUENCES bos
358 , ENG_REVISED_ITEMS ri
359 WHERE bor.operation_sequence_id = bos.operation_sequence_id
360 AND bos.implementation_date IS NULL
361 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
362 AND ri.status_type = 5 -- Cancelled
363 AND ri.organization_id = org_id
364 AND ri.change_notice = change_order
365 ) ;
366 -- Insert the cancelled rev operations into eng_revised_operations
367 INSERT INTO ENG_REVISED_OPERATIONS (
368 operation_sequence_id
369 , routing_sequence_id
370 , operation_seq_num
371 , last_update_date
372 , last_updated_by
373 , creation_date
374 , created_by
375 , last_update_login
376 , standard_operation_id
377 , department_id
378 , operation_lead_time_percent
379 , minimum_transfer_quantity
380 , count_point_type
381 , operation_description
382 , effectivity_date
383 , disable_date
384 , backflush_flag
385 , option_dependent_flag
386 , attribute_category
387 , attribute1
388 , attribute2
389 , attribute3
390 , attribute4
391 , attribute5
392 , attribute6
393 , attribute7
394 , attribute8
395 , attribute9
396 , attribute10
397 , attribute11
398 , attribute12
399 , attribute13
400 , attribute14
401 , attribute15
402 , request_id
403 , program_update_date
404 , operation_type
405 , reference_flag
406 , process_op_seq_id
407 , line_op_seq_id
408 , yield
409 , cumulative_yield
410 , reverse_cumulative_yield
411 , labor_time_calc
412 , machine_time_calc
413 , total_time_calc
414 , labor_time_user
415 , machine_time_user
416 , total_time_user
417 , net_planning_percent
418 , x_coordinate
419 , y_coordinate
420 , include_in_rollup
421 , operation_yield_enabled
422 , change_notice
423 , implementation_date
424 , old_operation_sequence_id
425 , acd_type
426 , revised_item_sequence_id
427 , cancellation_date)
428 SELECT
429 bos.OPERATION_SEQUENCE_ID
430 , bos.ROUTING_SEQUENCE_ID
431 , bos.OPERATION_SEQ_NUM
432 , SYSDATE /* Last Update Date */
433 , user_id /* Last Updated By */
434 , SYSDATE /* Creation Date */
435 , user_id /* Created By */
436 , login /* Last Update Login */
437 , bos.STANDARD_OPERATION_ID
438 , bos.DEPARTMENT_ID
439 , bos.OPERATION_LEAD_TIME_PERCENT
440 , bos.MINIMUM_TRANSFER_QUANTITY
441 , bos.COUNT_POINT_TYPE
442 , bos.OPERATION_DESCRIPTION
443 , bos.EFFECTIVITY_DATE
444 , bos.DISABLE_DATE
445 , bos.BACKFLUSH_FLAG
446 , bos.OPTION_DEPENDENT_FLAG
447 , bos.ATTRIBUTE_CATEGORY
448 , bos.ATTRIBUTE1
449 , bos.ATTRIBUTE2
450 , bos.ATTRIBUTE3
451 , bos.ATTRIBUTE4
452 , bos.ATTRIBUTE5
453 , bos.ATTRIBUTE6
454 , bos.ATTRIBUTE7
455 , bos.ATTRIBUTE8
456 , bos.ATTRIBUTE9
457 , bos.ATTRIBUTE10
458 , bos.ATTRIBUTE11
459 , bos.ATTRIBUTE12
460 , bos.ATTRIBUTE13
461 , bos.ATTRIBUTE14
462 , bos.ATTRIBUTE15
463 , NULL /* Request Id */
464 , SYSDATE /* program_update_date */
465 , bos.OPERATION_TYPE
466 , bos.REFERENCE_FLAG
467 , bos.PROCESS_OP_SEQ_ID
468 , bos.LINE_OP_SEQ_ID
469 , bos.YIELD
470 , bos.CUMULATIVE_YIELD
471 , bos.REVERSE_CUMULATIVE_YIELD
472 , bos.LABOR_TIME_CALC
473 , bos.MACHINE_TIME_CALC
474 , bos.TOTAL_TIME_CALC
475 , bos.LABOR_TIME_USER
476 , bos.MACHINE_TIME_USER
477 , bos.TOTAL_TIME_USER
478 , bos.NET_PLANNING_PERCENT
479 , bos.X_COORDINATE
480 , bos.Y_COORDINATE
481 , bos.INCLUDE_IN_ROLLUP
482 , bos.OPERATION_YIELD_ENABLED
483 , bos.CHANGE_NOTICE
484 , bos.IMPLEMENTATION_DATE
485 , bos.OLD_OPERATION_SEQUENCE_ID
486 , bos.ACD_TYPE
487 , bos.REVISED_ITEM_SEQUENCE_ID
488 , SYSDATE /* Cancellation Date */
489 FROM BOM_OPERATION_SEQUENCES bos
490 , ENG_REVISED_ITEMS ri
491 WHERE bos.implementation_date IS NULL
492 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
493 AND ri.status_type = 5 -- Cancelled
494 AND ri.organization_id = org_id
495 AND ri.change_notice = change_order;
496 -- Delete the rows from bom_operation_sequences
497 DELETE FROM BOM_OPERATION_SEQUENCES bos
498 WHERE EXISTS (SELECT NULL
499 FROM ENG_REVISED_ITEMS ri
500 WHERE bos.implementation_date IS NULL
501 AND bos.revised_item_sequence_id = ri.revised_item_sequence_id
502 AND ri.status_type = 5 -- Cancelled
503 AND ri.organization_id = org_id
504 AND ri.change_notice = change_order
505 ) ;
506
507 -- Delete routing revisions created by revised items on ECO
508 DELETE FROM MTL_RTG_ITEM_REVISIONS rev
509 WHERE EXISTS (SELECT NULL
510 FROM ENG_REVISED_ITEMS ri
511 WHERE rev.implementation_date IS NULL
512 AND rev.revised_item_sequence_id = ri.revised_item_sequence_id
513 AND ri.status_type = 5 -- Cancelled
514 AND ri.organization_id = org_id
515 AND ri.change_notice = change_order
516 ) ;
517
518
519 -- Delete the bom header if routing was created by this revised item and
520 -- nothing else references this
521
522 --
523 -- Bug 3668603
524 -- Before deleting the routing header, check if it is referenced,
525 -- For each routing_sequence_id referenced in the cancelled revised items ,
526 -- Check using cursor c_check_rtg_header_del, if it is referenced.
527 -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
528 -- set to null for the routing header header if value is current eco change_notice.
529 -- If not referenced, then delete the routing revisions if the header is a primary routing
530 -- Delete the header and unset the routing_sequence_id on the revised items
531 -- using the routing_sequence_id.
532 --
533 FOR cri IN c_cancelled_RI
534 LOOP
535
536 l_del_rtg_header := 0;
537 l_routing_sequence_id := cri.routing_sequence_id;
538
539 FOR crh IN c_check_rtg_header_del(cri.routing_sequence_id)
540 LOOP
541 l_del_rtg_header := 1;
542
543 END LOOP;
544
545 IF (l_del_rtg_header = 1)
546 THEN
547 DELETE FROM MTL_RTG_ITEM_REVISIONS rev
548 WHERE EXISTS (SELECT 1
549 FROM BOM_OPERATIONAL_ROUTINGS bor
550 WHERE bor.routing_sequence_id = l_routing_sequence_id
551 AND bor.alternate_routing_designator IS NULL
552 AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
553 AND bor.organization_id = rev.organization_id);
554
555 DELETE FROM BOM_OPERATIONAL_ROUTINGS
556 WHERE routing_sequence_id = l_routing_sequence_id;
557
558 -- If routing was deleted, then unset the routing_sequence_id on the revised items
559 UPDATE ENG_REVISED_ITEMS ri
560 SET routing_sequence_id = ''
561 , last_updated_by = user_id
562 , last_update_login = login
563 WHERE ri.organization_id = org_id
564 AND ri.change_notice = change_order
565 AND ri.status_type = 5 -- Cancelled
566 AND ri.routing_sequence_id = l_routing_sequence_id
567 AND NOT EXISTS (SELECT 'No Rtg Header'
568 FROM BOM_OPERATIONAL_ROUTINGS bor
569 WHERE bor.routing_sequence_id = ri.routing_sequence_id
570 ) ;
571 ELSE
572
573 UPDATE BOM_OPERATIONAL_ROUTINGS
574 SET last_update_date = SYSDATE
575 , last_updated_by = user_id
576 , last_update_login = login
577 , pending_from_ecn = null
578 WHERE routing_sequence_id = l_routing_sequence_id
579 AND pending_from_ecn = change_order;
580 END IF;
581
582 END LOOP;
583
584
585 /*DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
586 WHERE EXISTS ( SELECT NULL
587 FROM ENG_REVISED_ITEMS ri
588 WHERE
589 bor.routing_sequence_id = ri.routing_sequence_id
590 AND TRUNC(ri.last_update_date) = TRUNC(SYSDATE)
591 AND ri.status_type = 5 -- Cancelled
592 AND ri.organization_id = org_id
593 AND ri.change_notice = change_order
594 )
595 AND NOT EXISTS (SELECT NULL
596 FROM BOM_OPERATION_SEQUENCES bos
597 WHERE bos.routing_sequence_id = bor.routing_sequence_id
598 AND (bos.change_notice IS NULL
599 OR bos.change_notice <> change_notice)
600 )
601 AND (( bor.alternate_routing_designator IS NULL
602 AND NOT EXISTS( SELECT NULL
603 FROM BOM_OPERATIONAL_ROUTINGS bor2
604 WHERE bor2.organization_id = bor.organization_id
605 AND bor2.assembly_item_id = bor.assembly_item_id
606 AND bor2.alternate_routing_designator IS NOT NULL )
607 )
608 OR
609 ( bor.alternate_routing_designator IS NOT NULL
610 AND NOT EXISTS( SELECT NULL
611 FROM ENG_REVISED_ITEMS ri2
612 WHERE ri2.organization_id = bor.organization_id
613 AND ri2.routing_sequence_id = bor.routing_sequence_id
614 AND ri2.change_notice <> change_order)
615 )) ;
616 -- If routing was deleted, then unset the routing_sequence_id on the revised items
617 IF SQL%FOUND THEN
618
619 UPDATE ENG_REVISED_ITEMS ri
620 SET routing_sequence_id = ''
621 , last_updated_by = user_id
622 , last_update_login =login
623 WHERE ri.organization_id = org_id
624 AND ri.change_notice = change_notice
625 AND ri.status_type = 5 -- Cancelled
626 AND NOT EXISTS (SELECT 'No Rtg Header'
627 FROM BOM_OPERATIONAL_ROUTINGS bor
628 WHERE bor.routing_sequence_id = ri.routing_sequence_id
629 ) ;
630 END IF;*/
631
632 -- End changes for bug 3668603
633
634
635 /*
636 ** delete item revisions created by revised items on ECO
637 */
638 stmt_num := 60;
639
640 delete from MTL_ITEM_REVISIONS_TL
641 where revision_id IN (select revision_id
642 from MTL_ITEM_REVISIONS_B I
643 WHERE CHANGE_NOTICE = change_order
644 AND ORGANIZATION_ID = org_id
645 AND IMPLEMENTATION_DATE IS NULL
646 AND INVENTORY_ITEM_ID IN
647 (SELECT REVISED_ITEM_ID
648 FROM ENG_REVISED_ITEMS R
649 WHERE R.CHANGE_NOTICE = change_order
650 AND R.ORGANIZATION_ID = org_id
651 AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
652 AND R.CANCELLATION_DATE IS NOT NULL));
653
654 DELETE FROM MTL_ITEM_REVISIONS_B I
655 WHERE CHANGE_NOTICE = change_order
656 AND ORGANIZATION_ID = org_id
657 AND IMPLEMENTATION_DATE IS NULL
658 AND INVENTORY_ITEM_ID IN (SELECT REVISED_ITEM_ID
659 FROM ENG_REVISED_ITEMS R
660 WHERE R.CHANGE_NOTICE = change_order
661 AND R.ORGANIZATION_ID = org_id
662 AND R.REVISED_ITEM_SEQUENCE_ID = I.REVISED_ITEM_SEQUENCE_ID
663 AND R.CANCELLATION_DATE IS NOT NULL);
664
665 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from mir');
666
667 /*
668 ** Syalaman - Fix for bug 6469639.
669 ** delete UDA data created by revised items of ECO
670 */
671 stmt_num := 70;
672
673 DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL A
674 WHERE A.REVISION_ID IN (SELECT NEW_ITEM_REVISION_ID
675 FROM ENG_REVISED_ITEMS
676 WHERE NEW_ITEM_REVISION_ID IS NOT NULL
677 AND CHANGE_NOTICE = change_order
678 AND ORGANIZATION_ID = org_id);
679
680 -- Begin Bug 7381299 fixing
681 -- Fixed by Gabriel on 10/29/2008.
682
683 /* DELETE FROM EGO_MTL_SY_ITEMS_EXT_B A
684 WHERE A.REVISION_ID IN (SELECT NEW_ITEM_REVISION_ID
685 FROM ENG_REVISED_ITEMS
686 WHERE NEW_ITEM_REVISION_ID IS NOT NULL
687 AND CHANGE_NOTICE = change_order
688 AND ORGANIZATION_ID = org_id); */
689
690 ---- Changed as follows, added more columns: A.ORGANIZATION_ID,
691 ---- A.INVENTORY_ITEM_ID that have indexes built on it to avoid
692 ---- full table scan.
693
694 DELETE
695 FROM EGO_MTL_SY_ITEMS_EXT_B A
696 WHERE (A.ORGANIZATION_ID, A.INVENTORY_ITEM_ID, A.REVISION_ID) IN
697 (SELECT ORGANIZATION_ID,
698 REVISED_ITEM_ID ,
699 NEW_ITEM_REVISION_ID
700 FROM ENG_REVISED_ITEMS
701 WHERE NEW_ITEM_REVISION_ID IS NOT NULL
702 AND CHANGE_NOTICE = change_order
703 AND ORGANIZATION_ID = org_id
704 );
705
706 -- End Bug 7381299 fixing
707
708 -- Syalaman - End of fix for bug 6469639.
709
710 /*
711 ** delete the bom header if bill was created by this revised item and
712 ** nothing else references this
713 */
714 /* This is to fix bug 1522704. Now cancellation of ECO won't delete
715 corresponding row from bom_bill_of_materials. Deletion will be done by
716 delete groups only. Here two line of code is added update BOM_BILL_OF_MATERIALS B
717 set pending_from_ecn = null*/
718 stmt_num := 80;
719 DELETE FROM BOM_BILL_OF_MATERIALS B
720 WHERE B.BILL_SEQUENCE_ID in (SELECT BILL_SEQUENCE_ID
721 FROM ENG_REVISED_ITEMS ERI
722 WHERE ORGANIZATION_ID = org_id
723 AND CHANGE_NOTICE = change_order
724 AND STATUS_TYPE = 5
725 AND TRUNC(LAST_UPDATE_DATE) = trunc(sysdate))
726 AND B.PENDING_FROM_ECN = change_order
727 AND NOT EXISTS (SELECT NULL
728 FROM BOM_INVENTORY_COMPONENTS C
729 WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
730 AND (C.CHANGE_NOTICE IS NULL
731 OR C.CHANGE_NOTICE <> change_order))
732 AND ((B.ALTERNATE_BOM_DESIGNATOR IS NULL
733 AND NOT EXISTS (SELECT NULL
734 FROM BOM_BILL_OF_MATERIALS B2
735 WHERE B2.ORGANIZATION_ID = B.ORGANIZATION_ID
736 AND B2.ASSEMBLY_ITEM_ID = B.ASSEMBLY_ITEM_ID
737 AND B2.ALTERNATE_BOM_DESIGNATOR IS NOT NULL))
738 OR
739 (NOT EXISTS (SELECT NULL
740 FROM ENG_REVISED_ITEMS R
741 WHERE R.ORGANIZATION_ID = B.ORGANIZATION_ID
742 AND R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
743 AND R.CHANGE_NOTICE <> change_order
744 AND R.STATUS_TYPE <> 5)));
745
746 /*
747 ** if bills was deleted, then unset the bill_sequence_id on the revise items
748 */
749 if (SQL%ROWCOUNT > 0) then
750 -- dbms_output.put_line('Deleted BOM headers');
751 stmt_num := 90;
752 UPDATE ENG_REVISED_ITEMS R
753 SET BILL_SEQUENCE_ID = ''
754 WHERE R.ORGANIZATION_ID = org_id
755 AND R.CHANGE_NOTICE = change_order
756 AND R.STATUS_TYPE = 5
757 AND NOT EXISTS (SELECT 'NO SUCH BILL'
758 FROM BOM_BILL_OF_MATERIALS BOM
759 WHERE BOM.BILL_SEQUENCE_ID = R.BILL_SEQUENCE_ID);
760 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eri for bsi');
761 end if;
762
763 EXCEPTION
764 WHEN OTHERS THEN
765 rollback;
766 err_text := 'Cancel_Eco' || '(' || stmt_num || ')' || SQLERRM;
767 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
768 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
769 APP_EXCEPTION.RAISE_EXCEPTION;
770 END Cancel_ECO;
771
772 Procedure Cancel_Revised_Item (
773 rev_item_seq number,
774 bill_seq_id number,
775 user_id number,
776 login number,
777 change_order varchar2
778 -- comment varchar2
779 ) IS
780 err_text varchar2(2000);
781 stmt_num number;
782 l_revision_id NUMBER;
783 l_revised_item_sequence_id number;
784 l_new_item_revision_id number;
785 l_revised_item_id varchar2(4000);
786 l_change_id number;
787 l_return_status varchar2(4000);
788 l_msg_data varchar2(4000);
789 l_msg_count number;
790 l_org_id number;
791 common number;
792
793 cursor delete_attachments is
794 select change_id,revised_item_id,new_item_revision_id,revised_item_sequence_id,organization_id
795 from eng_revised_items
796 where revised_item_sequence_id = rev_item_seq;
797
798 Cursor is_editable_common_bom1( rev_item_seq number ) is
799 select bill_sequence_id
800 from BOM_BILL_OF_MATERIALS
801 where common_bill_sequence_id <> source_bill_sequence_id
802 and bill_sequence_id = common_bill_sequence_id
803 and pending_from_ECN =(select change_notice
804 from eng_revised_items
805 where revised_item_sequence_id = rev_item_seq);
806
807 Begin
808 --Bug : 3507992 Calling the following API on all cases, i.e. attachments made to new item revision,
809 --existing item revision or at the Item Level.
810
811 begin
812 open delete_attachments;
813 loop
814 fetch delete_attachments into l_change_id,l_revised_item_id,l_new_item_revision_id,l_revised_item_sequence_id,l_org_id;
815 exit when delete_attachments%NOTFOUND ;
816
817 ENG_ATTACHMENT_IMPLEMENTATION.delete_attachments_for_curr_co
818 (p_api_version => 1.0
819 ,p_change_id => l_change_id
820 ,p_rev_item_seq_id => l_revised_item_sequence_id
821 ,x_return_status => l_return_status
822 ,x_msg_data => l_msg_data
823 ,x_msg_count => l_msg_count
824 );
825 end loop;
826 close delete_attachments;
827 exception
828 when others then
829 close delete_attachments;
830 end;
831 /*
832 ** set cancellation date of all pending revised items on ECO
833 stmt_num := 10;
834 UPDATE ENG_REVISED_ITEMS
835 SET CANCELLATION_DATE = SYSDATE,
836 STATUS_TYPE = 5,
837 CANCEL_COMMENTS = comment,
838 LAST_UPDATED_BY = user_id,
839 LAST_UPDATE_LOGIN = login
840 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
841 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eri');
842 */
843
844 /*
845 ** delete substitute components of all pending revised items on ECO
846 */
847 stmt_num := 20;
848 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
849 WHERE SC.COMPONENT_SEQUENCE_ID IN
850 (SELECT IC.COMPONENT_SEQUENCE_ID
851 FROM BOM_INVENTORY_COMPONENTS IC
852 WHERE IC.REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
853 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bsc');
854
855 /*
856 ** delete reference designators of all pending revised items on ECO
857 */
858 stmt_num := 30;
859 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
860 WHERE RD.COMPONENT_SEQUENCE_ID IN
861 (SELECT IC.COMPONENT_SEQUENCE_ID
862 FROM BOM_INVENTORY_COMPONENTS IC
863 WHERE IC.REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
864 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from brd');
865
866 /*
867 ** insert the cancelled rev components into eng_revised_components
868 */
869 stmt_num := 40;
870 INSERT INTO ENG_REVISED_COMPONENTS (
871 COMPONENT_SEQUENCE_ID,
872 COMPONENT_ITEM_ID,
873 OPERATION_SEQUENCE_NUM,
874 BILL_SEQUENCE_ID,
875 CHANGE_NOTICE,
876 EFFECTIVITY_DATE,
877 COMPONENT_QUANTITY,
878 COMPONENT_YIELD_FACTOR,
879 LAST_UPDATE_DATE,
880 LAST_UPDATED_BY,
881 CREATION_DATE,
882 CREATED_BY,
883 LAST_UPDATE_LOGIN,
884 CANCELLATION_DATE,
885 OLD_COMPONENT_SEQUENCE_ID,
886 ITEM_NUM,
887 WIP_SUPPLY_TYPE,
888 COMPONENT_REMARKS,
889 SUPPLY_SUBINVENTORY,
890 SUPPLY_LOCATOR_ID,
891 DISABLE_DATE,
892 ACD_TYPE,
893 PLANNING_FACTOR,
894 QUANTITY_RELATED,
895 SO_BASIS,
896 OPTIONAL,
897 MUTUALLY_EXCLUSIVE_OPTIONS,
898 INCLUDE_IN_COST_ROLLUP,
899 CHECK_ATP,
900 SHIPPING_ALLOWED,
901 REQUIRED_TO_SHIP,
902 REQUIRED_FOR_REVENUE,
903 INCLUDE_ON_SHIP_DOCS,
904 LOW_QUANTITY,
905 HIGH_QUANTITY,
906 REVISED_ITEM_SEQUENCE_ID,
907 ATTRIBUTE_CATEGORY,
908 ATTRIBUTE1,
909 ATTRIBUTE2,
910 ATTRIBUTE3,
911 ATTRIBUTE4,
912 ATTRIBUTE5,
913 ATTRIBUTE6,
914 ATTRIBUTE7,
915 ATTRIBUTE8,
916 ATTRIBUTE9,
917 ATTRIBUTE10,
918 ATTRIBUTE11,
919 ATTRIBUTE12,
920 ATTRIBUTE13,
921 ATTRIBUTE14,
922 ATTRIBUTE15)
923 SELECT
924 IC.COMPONENT_SEQUENCE_ID,
925 IC.COMPONENT_ITEM_ID,
926 IC.OPERATION_SEQ_NUM,
927 IC.BILL_SEQUENCE_ID,
928 IC.CHANGE_NOTICE,
929 IC.EFFECTIVITY_DATE,
930 IC.COMPONENT_QUANTITY,
931 IC. COMPONENT_YIELD_FACTOR,
932 SYSDATE,
933 user_id,
934 SYSDATE,
935 user_id,
936 login,
937 sysdate,
938 IC.OLD_COMPONENT_SEQUENCE_ID,
939 IC.ITEM_NUM,
940 IC.WIP_SUPPLY_TYPE,
941 IC.COMPONENT_REMARKS,
942 IC.SUPPLY_SUBINVENTORY,
943 IC.SUPPLY_LOCATOR_ID,
944 IC.DISABLE_DATE,
945 IC.ACD_TYPE,
946 IC.PLANNING_FACTOR,
947 IC.QUANTITY_RELATED,
948 IC.SO_BASIS,
949 IC.OPTIONAL,
950 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
951 IC.INCLUDE_IN_COST_ROLLUP,
952 IC.CHECK_ATP,
953 IC.SHIPPING_ALLOWED,
954 IC.REQUIRED_TO_SHIP,
955 IC.REQUIRED_FOR_REVENUE,
956 IC.INCLUDE_ON_SHIP_DOCS,
957 IC.LOW_QUANTITY,
958 IC.HIGH_QUANTITY,
959 IC.REVISED_ITEM_SEQUENCE_ID,
960 IC.ATTRIBUTE_CATEGORY,
961 IC.ATTRIBUTE1,
962 IC.ATTRIBUTE2,
963 IC.ATTRIBUTE3,
964 IC.ATTRIBUTE4,
965 IC.ATTRIBUTE5,
966 IC.ATTRIBUTE6,
967 IC.ATTRIBUTE7,
968 IC.ATTRIBUTE8,
969 IC.ATTRIBUTE9,
970 IC.ATTRIBUTE10,
971 IC.ATTRIBUTE11,
972 IC.ATTRIBUTE12,
973 IC.ATTRIBUTE13,
974 IC.ATTRIBUTE14,
975 IC.ATTRIBUTE15
976 FROM BOM_INVENTORY_COMPONENTS IC
977 WHERE IC.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
978 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows inserted into erc');
979
980 /*
981 ** delete the rows from bom_inventory_components
982 */
983 stmt_num := 50;
984 DELETE FROM BOM_INVENTORY_COMPONENTS IC
985 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
986 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bic');
987
988 /*Special handling for Editable common bom*/
989 for common in is_editable_common_bom1(rev_item_seq) loop
990 /*
991 ** delete the rows from bom_inventory_components
992 */
993
994 DELETE FROM BOM_INVENTORY_COMPONENTS IC
995 WHERE IC.BILL_SEQUENCE_ID = common.bill_sequence_id ;
996 end loop;
997
998 /*
999 ** delete item revisions created by revised items on ECO
1000 */
1001 stmt_num := 50;
1002 -- Modified where clause for performance bug 4251776
1003 delete from MTL_ITEM_REVISIONS_TL
1004 WHERE revision_id IN (select new_item_revision_id
1005 from eng_revised_items I
1006 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
1007
1008 /*where revision_id IN (select revision_id
1009 from MTL_ITEM_REVISIONS_B I
1010 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);*/
1011 -- Modified where clause for performance bug 4251776
1012 DELETE FROM MTL_ITEM_REVISIONS_B I --Fix for bug 3215586
1013 WHERE revision_id IN (select new_item_revision_id
1014 from eng_revised_items I
1015 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
1016
1017 /*WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;*/
1018
1019 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from eri');
1020
1021 /*
1022 ** Syalaman - Fix for bug 6469639.
1023 ** delete UDA data created by revised items on ECO
1024 */
1025 stmt_num := 60;
1026
1027 DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL A
1028 WHERE A.REVISION_ID IN (SELECT NEW_ITEM_REVISION_ID
1029 FROM ENG_REVISED_ITEMS
1030 WHERE NEW_ITEM_REVISION_ID IS NOT NULL
1031 AND REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
1032
1033 DELETE FROM EGO_MTL_SY_ITEMS_EXT_B A
1034 WHERE A.REVISION_ID IN (SELECT NEW_ITEM_REVISION_ID
1035 FROM ENG_REVISED_ITEMS
1036 WHERE NEW_ITEM_REVISION_ID IS NOT NULL
1037 AND REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
1038 -- Syalaman - End of fix for bug 6469639.
1039
1040 /*
1041 ** delete the bom header if bill was created by this revised item and
1042 ** nothing else references this
1043 */
1044 /* This is to fix bug 1522704. Now cancellation of ECO won't delete
1045 corresponding row from bom_bill_of_materials. Deletion will be done by
1046 delete groups only. Here two line of code is added update BOM_BILL_OF_MATERIALS B
1047 set pending_from_ecn = null*/
1048 stmt_num := 70;
1049 DELETE FROM BOM_BILL_OF_MATERIALS B
1050 WHERE B.BILL_SEQUENCE_ID = bill_seq_id
1051 AND B.PENDING_FROM_ECN = change_order
1052 AND NOT EXISTS (SELECT NULL
1053 FROM BOM_INVENTORY_COMPONENTS C
1054 WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
1055 AND (C.REVISED_ITEM_SEQUENCE_ID IS NULL
1056 OR C.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq))
1057 AND ((B.ALTERNATE_BOM_DESIGNATOR IS NULL
1058 AND NOT EXISTS (SELECT NULL
1059 FROM BOM_BILL_OF_MATERIALS B2
1060 WHERE B2.ORGANIZATION_ID = B.ORGANIZATION_ID
1061 AND B2.ASSEMBLY_ITEM_ID = B.ASSEMBLY_ITEM_ID
1062 AND B2.ALTERNATE_BOM_DESIGNATOR IS NOT NULL))
1063 OR
1064 (NOT EXISTS (SELECT NULL
1065 FROM ENG_REVISED_ITEMS R
1066 WHERE R.ORGANIZATION_ID = B.ORGANIZATION_ID
1067 AND R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
1068 AND R.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq
1069 AND R.STATUS_TYPE <> 5)));
1070
1071 /*
1072 ** if bill was deleted, then unset the bill_sequence_id on the revise item
1073 */
1074 -- if (SQL%ROWCOUNT > 0) then
1075 --dbms_output.put_line('Deleted BOM header');
1076 stmt_num := 80;
1077 UPDATE ENG_REVISED_ITEMS R
1078 SET BILL_SEQUENCE_ID = ''
1079 WHERE R.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
1080 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eri for bsi');
1081 -- end if;
1082
1083 /*
1084 ** if bill was deleted, then unset the bill_sequence_id on the revise item
1085 */
1086 -- if (SQL%ROWCOUNT > 0) then
1087 --dbms_output.put_line('Deleted BOM header');
1088 stmt_num := 90;
1089 UPDATE ENG_REVISED_ITEMS R
1090 SET ALTERNATE_BOM_DESIGNATOR = ''
1091 WHERE R.REVISED_ITEM_SEQUENCE_ID = rev_item_seq
1092 AND ROUTING_SEQUENCE_ID IS NULL;
1093 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows updated in eri for bsi');
1094 -- end if;
1095
1096 EXCEPTION
1097 WHEN OTHERS THEN
1098 rollback;
1099 err_text := 'Cancel_Revised_Item' || '(' || stmt_num || ')' || SQLERRM;
1100 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1101 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1102 APP_EXCEPTION.RAISE_EXCEPTION;
1103 END Cancel_Revised_Item;
1104
1105 Procedure Cancel_Revised_Component (
1106 comp_seq_id number,
1107 user_id number,
1108 login number,
1109 comment varchar2
1110 ) IS
1111 err_text varchar2(2000);
1112 stmt_num number;
1113 -- R12 changes for common bom
1114 -- Cursor to fetch all the related components for the specified component being cancelled
1115 CURSOR c_related_components IS
1116 SELECT component_sequence_id
1117 FROM bom_components_b
1118 WHERE common_component_sequence_id = comp_seq_id;
1119 Begin
1120 /*
1121 ** insert the cancelled rev components into eng_revised_components
1122 */
1123 stmt_num := 10;
1124 INSERT INTO ENG_REVISED_COMPONENTS (
1125 COMPONENT_SEQUENCE_ID,
1126 COMPONENT_ITEM_ID,
1127 OPERATION_SEQUENCE_NUM,
1128 BILL_SEQUENCE_ID,
1129 CHANGE_NOTICE,
1130 EFFECTIVITY_DATE,
1131 COMPONENT_QUANTITY,
1132 COMPONENT_YIELD_FACTOR,
1133 LAST_UPDATE_DATE,
1134 LAST_UPDATED_BY,
1135 CREATION_DATE,
1136 CREATED_BY,
1137 LAST_UPDATE_LOGIN,
1138 CANCELLATION_DATE,
1139 CANCEL_COMMENTS,
1140 OLD_COMPONENT_SEQUENCE_ID,
1141 ITEM_NUM,
1142 WIP_SUPPLY_TYPE,
1143 COMPONENT_REMARKS,
1144 SUPPLY_SUBINVENTORY,
1145 SUPPLY_LOCATOR_ID,
1146 DISABLE_DATE,
1147 ACD_TYPE,
1148 PLANNING_FACTOR,
1149 QUANTITY_RELATED,
1150 SO_BASIS,
1151 OPTIONAL,
1152 MUTUALLY_EXCLUSIVE_OPTIONS,
1153 INCLUDE_IN_COST_ROLLUP,
1154 CHECK_ATP,
1155 SHIPPING_ALLOWED,
1156 REQUIRED_TO_SHIP,
1157 REQUIRED_FOR_REVENUE,
1158 INCLUDE_ON_SHIP_DOCS,
1159 LOW_QUANTITY,
1160 HIGH_QUANTITY,
1161 REVISED_ITEM_SEQUENCE_ID,
1162 ATTRIBUTE_CATEGORY,
1163 ATTRIBUTE1,
1164 ATTRIBUTE2,
1165 ATTRIBUTE3,
1166 ATTRIBUTE4,
1167 ATTRIBUTE5,
1168 ATTRIBUTE6,
1169 ATTRIBUTE7,
1170 ATTRIBUTE8,
1171 ATTRIBUTE9,
1172 ATTRIBUTE10,
1173 ATTRIBUTE11,
1174 ATTRIBUTE12,
1175 ATTRIBUTE13,
1176 ATTRIBUTE14,
1177 ATTRIBUTE15)
1178 SELECT
1179 IC.COMPONENT_SEQUENCE_ID,
1180 IC.COMPONENT_ITEM_ID,
1181 IC.OPERATION_SEQ_NUM,
1182 IC.BILL_SEQUENCE_ID,
1183 IC.CHANGE_NOTICE,
1184 IC.EFFECTIVITY_DATE,
1185 IC.COMPONENT_QUANTITY,
1186 IC. COMPONENT_YIELD_FACTOR,
1187 SYSDATE,
1188 user_id,
1189 SYSDATE,
1190 user_id,
1191 login,
1192 sysdate,
1193 comment,
1194 IC.OLD_COMPONENT_SEQUENCE_ID,
1195 IC.ITEM_NUM,
1196 IC.WIP_SUPPLY_TYPE,
1197 IC.COMPONENT_REMARKS,
1198 IC.SUPPLY_SUBINVENTORY,
1199 IC.SUPPLY_LOCATOR_ID,
1200 IC.DISABLE_DATE,
1201 IC.ACD_TYPE,
1202 IC.PLANNING_FACTOR,
1203 IC.QUANTITY_RELATED,
1204 IC.SO_BASIS,
1205 IC.OPTIONAL,
1206 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
1207 IC.INCLUDE_IN_COST_ROLLUP,
1208 IC.CHECK_ATP,
1209 IC.SHIPPING_ALLOWED,
1210 IC.REQUIRED_TO_SHIP,
1211 IC.REQUIRED_FOR_REVENUE,
1212 IC.INCLUDE_ON_SHIP_DOCS,
1213 IC.LOW_QUANTITY,
1214 IC.HIGH_QUANTITY,
1215 IC.REVISED_ITEM_SEQUENCE_ID,
1216 IC.ATTRIBUTE_CATEGORY,
1217 IC.ATTRIBUTE1,
1218 IC.ATTRIBUTE2,
1219 IC.ATTRIBUTE3,
1220 IC.ATTRIBUTE4,
1221 IC.ATTRIBUTE5,
1222 IC.ATTRIBUTE6,
1223 IC.ATTRIBUTE7,
1224 IC.ATTRIBUTE8,
1225 IC.ATTRIBUTE9,
1226 IC.ATTRIBUTE10,
1227 IC.ATTRIBUTE11,
1228 IC.ATTRIBUTE12,
1229 IC.ATTRIBUTE13,
1230 IC.ATTRIBUTE14,
1231 IC.ATTRIBUTE15
1232 FROM BOM_INVENTORY_COMPONENTS IC
1233 WHERE IC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1234 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows inserted into erc');
1235
1236 /*
1237 ** delete from bom_inventory_comps
1238 */
1239 DELETE FROM BOM_INVENTORY_COMPONENTS
1240 WHERE COMPONENT_SEQUENCE_ID = comp_seq_id;
1241 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows delete from bic');
1242
1243 -- Fixed bug 618781.
1244 -- Cancelling of Revised component must also cancel the
1245 -- Subs. components and the reference designators.
1246
1247 /*
1248 ** Delete the Substitute Components and also the Reference Designators
1249 */
1250 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
1251 WHERE SC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1252
1253 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bsc');
1254
1255 /*
1256 ** delete reference designators of all pending revised items on ECO
1257 */
1258 stmt_num := 30;
1259 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
1260 WHERE RD.COMPONENT_SEQUENCE_ID = comp_seq_id;
1261 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from rfd');
1262
1263 stmt_num := 40;
1264
1265 FOR crc IN c_related_components
1266 LOOP
1267 stmt_num := stmt_num+1;
1268 /* delete from bom_inventory_comps */
1269 DELETE FROM bom_components_b
1270 WHERE COMPONENT_SEQUENCE_ID = crc.COMPONENT_SEQUENCE_ID;
1271 stmt_num := stmt_num+1;
1272 /* delete the Substitute Components and also the Reference Designators */
1273 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
1274 WHERE SC.COMPONENT_SEQUENCE_ID = crc.COMPONENT_SEQUENCE_ID;
1275 /* Delete reference designators of all pending revised items on ECO */
1276 stmt_num := stmt_num+1;
1277 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
1278 WHERE RD.COMPONENT_SEQUENCE_ID = crc.COMPONENT_SEQUENCE_ID;
1279 END LOOP;
1280 EXCEPTION
1281 WHEN OTHERS THEN
1282 rollback;
1283 err_text := 'Cancel_Revised_Component' || '(' || stmt_num || ')' ||
1284 SQLERRM;
1285 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1286 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1287 APP_EXCEPTION.RAISE_EXCEPTION;
1288 END Cancel_Revised_Component;
1289
1290 /*
1291 Changing the attachment status to '' when the approval / review is being cancelled
1292 */
1293 Procedure Change_Att_Status (
1294 p_change_id number,
1295 user_id number,
1296 login_id number
1297 )
1298 IS
1299 err_text varchar2(2000);
1300 Begin
1301 SAVEPOINT before_status_update;
1302 update fnd_attached_documents
1303 set status = '',
1304 last_update_date = sysdate,
1305 last_updated_by = user_id,
1306 last_update_login = login_id
1307 where attached_document_id in
1308 (SELECT attachment_id FROM eng_attachment_changes WHERE change_id = p_change_id);
1309 EXCEPTION
1310 WHEN OTHERS THEN
1311 rollback;
1312 err_text := 'Change_Attachment_Status' || '(' || ')' || SQLERRM;
1313 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1314 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1315 APP_EXCEPTION.RAISE_EXCEPTION;
1316 END Change_Att_Status;
1317
1318 END ENG_CANCEL_ECO;