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