[Home] [Help]
PACKAGE BODY: APPS.BOM_DELETE_ENTITY
Source
1 PACKAGE BODY bom_delete_entity AS
2 /* $Header: BOMDELMB.pls 120.2.12000000.2 2007/02/23 12:10:19 earumuga ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 -- BOMDELMB.pls
10 --
11 -- DESCRIPTION
12 -- This package contains Procedures used to insert delete groups
13 -- and Sub Entities Data into Bom_delete_groups and Bom_delete_sub
14 -- entities Tables Respectively.
15 --
16 -- PARAMETERS
17 -- Group Id Delete Group Sequence Id
18 -- Delete Type Type of the Delete Group
19 -- (Item,bill,routing,component,operation)
20 --
21 -- NOTES
22 --
23 -- HISTORY
24 -- 15-SEP-2000 Janaki B Initial Creation
25 --
26 ****************************************************************************/
27 FUNCTION Get_delorg_type(group_id in NUMBER)
28 RETURN NUMBER
29 IS
30 CURSOR delorg_type is
31 select delete_org_type
32 FROM bom_delete_groups
33 where delete_group_sequence_id = group_id;
34 BEGIN
35 FOR c_delorg_type IN delorg_type LOOP
36 RETURN c_delorg_type.delete_org_type;
37 END LOOP;
38 RETURN NULL;
39 END Get_delorg_type;
40
41 FUNCTION Get_delete_type(group_id in NUMBER)
42 RETURN NUMBER
43 IS
44 CURSOR del_type is
45 select delete_type
46 FROM bom_delete_groups
47 where delete_group_sequence_id = group_id;
48 BEGIN
49 FOR c_del_type IN del_type LOOP
50 RETURN c_del_type.delete_type;
51 END LOOP;
52 RETURN NULL;
53 END Get_delete_type;
54
55
56 FUNCTION Get_delorg_hrchy(group_id in NUMBER)
57 RETURN VARCHAR2
58 IS
59 CURSOR delorg_hrchy is
60 select organization_hierarchy
61 FROM bom_delete_groups
62 where delete_group_sequence_id = group_id;
63 BEGIN
64 FOR c_delorg_hrchy IN delorg_hrchy LOOP
65 RETURN c_delorg_hrchy.organization_hierarchy;
66 END LOOP;
67 RETURN NULL;
68 END Get_delorg_hrchy;
69
70 FUNCTION Get_common_flag(group_id in NUMBER)
71 RETURN NUMBER
72 IS
73 CURSOR common_flag is
74 select delete_common_bill_flag
75 FROM bom_delete_groups
76 where delete_group_sequence_id = group_id;
77 BEGIN
78 FOR c_common_flag IN common_flag LOOP
79 RETURN c_common_flag.delete_common_bill_flag;
80 END LOOP;
81 RETURN NULL;
82 END Get_common_flag;
83
84 FUNCTION Get_delorg_id(group_id in NUMBER)
85 RETURN NUMBER
86 IS
87 CURSOR orgid is
88 select organization_id
89 FROM bom_delete_groups
90 where delete_group_sequence_id = group_id;
91 BEGIN
92 FOR c_orgid IN orgid LOOP
93 RETURN c_orgid.organization_id;
94 END LOOP;
95 RETURN NULL;
96 END Get_delorg_id;
97
98 FUNCTION Get_delorg_name(org_id in number)
99 RETURN varchar2
100 IS
101 -- bug:5003575 Replaced ORG_ORGANIZATION_DEFINITIONS view by ORG_ACCESS_VIEW
102 -- to improve performance
103 CURSOR orgname is
104 SELECT oav.ORGANIZATION_NAME
105 FROM ORG_ACCESS_VIEW oav
106 WHERE
107 oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
108 AND oav.RESP_APPLICATION_ID = FND_PROFILE.Value('RESP_APPL_ID')
109 AND oav.ORGANIZATION_ID = org_id;
110 BEGIN
111 FOR c_orgname IN orgname LOOP
112 RETURN c_orgname.organization_name;
113 END LOOP;
114 RETURN NULL;
115 END Get_delorg_name;
116
117 PROCEDURE get_delorg_list(org_type in number,
118 org_hrchy in varchar2,
119 current_org_id in number,
120 current_org_name in varchar2,
121 org_list in out nocopy /* file.sql.39 change */ inv_orghierarchy_pvt.orgid_tbl_type)
122 IS
123 CURSOR orgs is
124 SELECT MP.organization_id
125 FROM MTL_PARAMETERS MP
126 WHERE MP.master_organization_id = (select m.master_organization_id
127 from mtl_parameters m
128 where m.organization_id = current_org_id)
129 and
130 exists ( select 'x'
131 from org_access_view
132 where responsibility_id =
133 to_number(FND_PROFILE.value('RESP_ID')) and
134 resp_application_id =
135 to_number(FND_PROFILE.value('RESP_APPL_ID'))
136 );
137 l_org_id mtl_parameters.organization_id%type;
138 l_index NUMBER := 0;
139 BEGIN
140 l_index := nvl(org_list.LAST,0);
141 If (org_type = 1) then
142 l_index := l_index+1;
143 org_list(l_index) := current_org_id;
144 elsif (org_type = 2) then
145 inv_orghierarchy_pvt.org_hierarchy_list(org_hrchy,
146 current_org_id,
147 org_list);
148 elsif (org_type = 3) then
149 FOR c_orgs in orgs LOOP
150 l_index := l_index+1;
151 org_list(l_index) := c_orgs.organization_id;
152 END LOOP;
153 end if;
154 END get_delorg_list;
155
156 FUNCTION Get_bill_seq(assembly_id in NUMBER,
157 org_id in NUMBER,
158 alternate_bom in VARCHAR2)
159 RETURN NUMBER
160 IS
161 CURSOR billseq is
162 select bill_sequence_id
163 FROM bom_bill_of_materials
164 where assembly_item_id = assembly_id and
165 organization_id = org_id and
166 nvl(alternate_bom_designator,'none') = nvl(alternate_bom,'none');
167 BEGIN
168 FOR c_billseq IN billseq LOOP
169 RETURN c_billseq.bill_sequence_id;
170 END LOOP;
171 RETURN NULL;
172 END Get_bill_seq;
173
174 FUNCTION Get_rtg_seq(assembly_id in NUMBER,
175 org_id in NUMBER,
176 alternate_desg in VARCHAR2)
177 RETURN NUMBER
178 IS
179 CURSOR rtgseq is
180 select routing_sequence_id
181 FROM bom_operational_routings
182 where assembly_item_id = assembly_id and
183 organization_id = org_id and
184 nvl(alternate_routing_designator,'none') = nvl(alternate_desg,'none');
185 BEGIN
186 FOR c_rtgseq IN rtgseq LOOP
187 RETURN c_rtgseq.routing_sequence_id;
188 END LOOP;
189 RETURN NULL;
190 END Get_rtg_seq;
191
192
193 FUNCTION Get_comp_seq(bill_seq in NUMBER,
194 component_id in NUMBER,
195 oper_seq_num in NUMBER,
196 effective_date in DATE)
197 RETURN NUMBER
198 IS
199 CURSOR compseq is
200 select component_sequence_id
201 FROM bom_inventory_components
202 where bill_sequence_id = bill_seq and
203 component_item_id = component_id and
204 operation_seq_num = oper_seq_num and
205 effectivity_date = effective_date ;
206 BEGIN
207 FOR c_compseq IN compseq LOOP
208 RETURN c_compseq.component_sequence_id;
209 END LOOP;
210 RETURN NULL;
211 END Get_comp_seq;
212
213
214 FUNCTION Get_oper_seq(rtg_seq in NUMBER,
215 oper_seq_num in NUMBER,
216 effective_date in DATE,
217 dept_code in VARCHAR2,
218 org_id in NUMBER)
219 RETURN NUMBER
220 IS
221 CURSOR operseq is
222 select operation_sequence_id
223 FROM bom_operation_sequences
224 where routing_sequence_id = rtg_seq and
225 operation_seq_num = oper_seq_num and
226 -- effectivity_date = effective_date and -- Changed for bug 2647027
227 trunc(effectivity_date) = trunc(effective_date) and -- Changed back for bug 3738241
228 department_id = (select department_id
229 from bom_departments
230 where department_code = dept_code and
231 organization_id = org_id);
232 BEGIN
233 FOR c_operseq IN operseq LOOP
234 RETURN c_operseq.operation_sequence_id;
235 END LOOP;
236 RETURN NULL;
237 END Get_oper_seq;
238
239 FUNCTION Get_dept_code(
240 dept_code in VARCHAR2,
241 org_id in NUMBER)
242 RETURN VARCHAR2
243 IS
244 CURSOR dept is
245 select department_code
246 FROM bom_departments
247 where department_code = dept_code and
248 organization_id = org_id;
249 BEGIN
250 FOR c_dept IN dept LOOP
251 RETURN c_dept.department_code;
252 END LOOP;
253 RETURN NULL;
254 END Get_dept_code;
255
256
257 FUNCTION Get_item_descr(assembly_id in NUMBER,
258 org_id in NUMBER)
259 RETURN VARCHAR2
260 IS
261 CURSOR descr is
262 select description
263 FROM mtl_system_items
264 where inventory_item_id = assembly_id and
265 organization_id = org_id;
266 BEGIN
267 FOR c_descr IN descr LOOP
268 RETURN c_descr.description;
269 END LOOP;
270 RETURN NULL;
271 END Get_item_descr;
272
273 FUNCTION Get_concat_segs(assembly_id in NUMBER,
274 org_id in NUMBER)
275 RETURN VARCHAR2
276 IS
277 CURSOR concat is
278 select concatenated_segments
279 FROM mtl_system_items_vl
280 where inventory_item_id = assembly_id and
281 organization_id = org_id;
282 BEGIN
283 FOR c_concat IN concat LOOP
284 RETURN c_concat.concatenated_segments;
285 END LOOP;
286 RETURN NULL;
287 END Get_concat_segs;
288
289
290 FUNCTION get_item_id(assembly_id in NUMBER,
291 current_org in NUMBER)
292 RETURN NUMBER
293 IS
294 CURSOR item IS
295 SELECT inventory_item_id
296 FROM mtl_system_items
297 where inventory_item_id = assembly_id and
298 organization_id = current_org;
299
300 BEGIN
301 FOR c_item in item LOOP
302 RETURN c_item.inventory_item_id;
303 END LOOP;
304 RETURN NULL;
305 END get_item_id;
306
307 PROCEDURE process_delete_entities(delete_type in NUMBER,
308 group_id in NUMBER,
309 original_org in NUMBER,
310 org_list in inv_orghierarchy_pvt.orgid_tbl_type)
311 IS
312 l_index number := 0;
313 current_org number;
314 temp varchar2(1);
315 common_bill bom_bill_of_materials.common_bill_sequence_id%type;
316 bill_seq_id bom_bill_of_materials.bill_sequence_id%type;
317 current_bill_seq bom_bill_of_materials.bill_sequence_id%type;
318 del_ent_type bom_delete_entities.delete_entity_type%type;
319 inv_item_id bom_delete_entities.inventory_item_id%type;
320 alt_desg bom_delete_entities.alternate_designator%type;
321 item_concat_seg bom_delete_entities.item_concat_segments%type;
322 last_upd_by bom_delete_entities.last_updated_by%type;
323 crtd_by bom_delete_entities.created_by%type;
324 current_del_seq bom_delete_entities.delete_entity_sequence_id%type;
325
326 CURSOR bom_entities IS
327 SELECT bill_sequence_id,delete_entity_type,
328 inventory_item_id,alternate_designator,
329 item_concat_segments,last_updated_by,
330 created_by,delete_entity_sequence_id
331 FROM bom_delete_entities
332 WHERE delete_group_sequence_id = group_id
333 AND organization_id = original_org;
334
335 CURSOR common_bills IS
336 SELECT common_bill_sequence_id,assembly_item_id,
337 alternate_bom_designator,bill_sequence_id
338 FROM bom_bill_of_materials
339 WHERE common_bill_sequence_id in (
340 select BOM2.bill_sequence_id
341 from bom_bill_of_materials BOM2
342 where BOM2.assembly_item_id = inv_item_id) and
343 bill_sequence_id <> common_bill_sequence_id and
344 organization_id = current_org;
345
346 BEGIN
347 if (delete_type in (2,6,7)) then -- Bill,Bill/Rtg,Item/Bill/Rtg
348 FOR c_bom_entities in bom_entities
349 LOOP --- ENTITIES LOOP
350 current_bill_seq := c_bom_entities.bill_sequence_id;
351 del_ent_type := c_bom_entities.delete_entity_type;
352 inv_item_id := c_bom_entities.inventory_item_id;
353 alt_desg := c_bom_entities.alternate_designator;
354 item_concat_seg := c_bom_entities.item_concat_segments;
355 last_upd_by := c_bom_entities.last_updated_by;
356 crtd_by := c_bom_entities.created_by;
357 current_del_seq := c_bom_entities.delete_entity_sequence_id;
358 l_index := org_list.FIRST;
359 while (l_index <= org_list.LAST)
360 LOOP --- ORG LIST LOOP
361 current_org := org_list(l_index);
362 /* current_org above means org being processed in the loop */
363 IF (current_bill_seq is not NULL) then -- BILLS
364 begin
365 select common_bill_sequence_id
366 into common_bill
367 from bom_bill_of_materials
368 where bill_sequence_id = current_bill_seq;
369 IF (current_bill_seq <> common_bill) then
370 exit;
371 END IF;
372 exception
373 when others then
374 exit;
375 end;
376 FOR c_common_bills in common_bills
377 LOOP --- COMMON LOOP
378 begin
379 select 'x'
380 into temp
381 from bom_delete_entities
382 where bill_sequence_id = c_common_bills.bill_sequence_id and
383 delete_group_sequence_id = group_id and
384 organization_id = current_org;
385 exception
386 when no_data_found then
387 insert into bom_delete_entities
388 (DELETE_ENTITY_SEQUENCE_ID,
389 DELETE_GROUP_SEQUENCE_ID,
390 DELETE_ENTITY_TYPE,
391 BILL_SEQUENCE_ID,
392 ROUTING_SEQUENCE_ID,
393 INVENTORY_ITEM_ID,
394 ORGANIZATION_ID,
395 ALTERNATE_DESIGNATOR,
396 ITEM_DESCRIPTION,
397 ITEM_CONCAT_SEGMENTS,
398 DELETE_STATUS_TYPE,
399 DELETE_DATE,
400 PRIOR_PROCESS_FLAG,
401 PRIOR_COMMIT_FLAG,
402 LAST_UPDATE_DATE,
403 LAST_UPDATED_BY,
404 CREATION_DATE,
405 CREATED_BY,
406 LAST_UPDATE_LOGIN,
407 REQUEST_ID,
408 PROGRAM_APPLICATION_ID,
409 PROGRAM_ID,
410 PROGRAM_UPDATE_DATE)
411 select bom_delete_entities_s.nextval,
412 group_id,
413 del_ent_type,
414 get_bill_seq(c_common_bills.assembly_item_id,
415 current_org,
416 c_common_bills.alternate_bom_designator),
417 null,
418 inv_item_id,
419 current_org,
420 alt_desg,
421 get_item_descr(c_common_bills.assembly_item_id,
422 current_org),
423 get_concat_segs(c_common_bills.assembly_item_id,
424 current_org),
425 1,
426 null,
427 1,
428 1,
429 sysdate,
430 last_upd_by,
431 sysdate,
432 crtd_by,
433 null,
434 null,
435 null,
436 null,
437 null
438 from bom_bill_of_materials
439 where organization_id = current_org and
440 common_bill_sequence_id = current_bill_seq and
441 bill_sequence_id <> current_bill_seq and
442 common_organization_id = original_org and
443 common_assembly_item_id = inv_item_id;
444 update bom_delete_entities
445 set delete_status_type = 5
446 where delete_group_sequence_id = group_id and
447 delete_entity_sequence_id = current_del_seq and
448 delete_status_type <> 4;
449 when others then
450 null;
451 end;
452 END LOOP; --- COMMON LOOP
453 END IF; --- BILLS
454 l_index := org_list.NEXT(l_index);
455 END LOOP; --- ORG LIST LOOP
456 END LOOP; --- ENTITIES LOOP
457 end if; -- Bill,Bill/Rtg,Item/Bill/Rtg
458 END process_delete_entities;
459
460 PROCEDURE modify_original_bills( group_id in NUMBER,
461 common_flag in NUMBER)
462 IS
463 BEGIN
464 if (common_flag = 1) then
465 begin
466 delete from bom_delete_entities
467 where delete_group_sequence_id = group_id and
468 delete_status_type = 5;
469 exception
470 when others then
471 null;
472 end;
473 elsif (common_flag = 2) then
474 begin
475 update bom_delete_entities
476 set delete_status_type = 1
477 where delete_group_sequence_id = group_id and
478 delete_status_type = 5;
479 exception
480 when others then
481 null;
482 end;
483 end if;
484 END modify_original_bills;
485
486 PROCEDURE process_original_sub_entities(
487 delete_type in NUMBER,
488 group_id in NUMBER,
489 original_org in NUMBER,
490 common_flag in NUMBER,
491 org_list in inv_orghierarchy_pvt.orgid_tbl_type)
492 IS
493 l_index number := 0;
494 current_org number;
495 temp varchar2(1);
496 common_bill bom_bill_of_materials.common_bill_sequence_id%type;
497 bill_seq_id bom_bill_of_materials.bill_sequence_id%type;
498 current_bill_seq bom_bill_of_materials.bill_sequence_id%type;
499 current_rtg_seq bom_delete_entities.routing_sequence_id%type;
500 del_status bom_delete_entities.delete_status_type%type;
501 del_ent_type bom_delete_entities.delete_entity_type%type;
502 inv_item_id bom_delete_entities.inventory_item_id%type;
503 alt_desg bom_delete_entities.alternate_designator%type;
504 item_concat_seg bom_delete_entities.item_concat_segments%type;
505 last_upd_by bom_delete_entities.last_updated_by%type;
506 crtd_by bom_delete_entities.created_by%type;
507 current_del_seq bom_delete_entities.delete_entity_sequence_id%type;
508 new_del_seq bom_delete_entities.delete_entity_sequence_id%type;
509 new_comp_seq bom_delete_sub_entities.component_sequence_id%type := NULL;
510 new_operation_seq bom_delete_sub_entities.operation_sequence_id%type;
511 new_bill_seq bom_bill_of_materials.bill_sequence_id%type;
512 new_rtg_seq bom_operational_routings.routing_sequence_id%type;
513 new_item_id bom_delete_entities.inventory_item_id%type;
514 component_id bom_inventory_components.component_item_id%type;
515 oper_seq_num bom_inventory_components.operation_seq_num%type;
516 effective_date bom_inventory_components.effectivity_date%type;
517
518 CURSOR bom_entities IS
519 SELECT bill_sequence_id,delete_entity_type,delete_status_type,
520 inventory_item_id,alternate_designator,routing_sequence_id,
521 item_concat_segments,last_updated_by,
522 created_by,delete_entity_sequence_id
523 FROM bom_delete_entities
524 WHERE
525 delete_group_sequence_id = group_id
526 AND organization_id = original_org
527 AND request_id = FND_GLOBAL.CONC_REQUEST_ID;
528
529 CURSOR sub_entity IS
530 SELECT OPERATION_SEQ_NUM,EFFECTIVITY_DATE,
531 COMPONENT_ITEM_ID,COMPONENT_CONCAT_SEGMENTS,
532 ITEM_NUM,DISABLE_DATE,OPERATION_DEPARTMENT_CODE
533 FROM BOM_DELETE_SUB_ENTITIES
534 where DELETE_ENTITY_SEQUENCE_ID = current_del_seq;
535
536 CURSOR compseq is
537 select component_sequence_id
538 FROM bom_inventory_components
539 where bill_sequence_id = new_bill_seq and
540 component_item_id = component_id and
541 operation_seq_num = oper_seq_num and
542 trunc(effectivity_date) = trunc(effective_date) ;
543 BEGIN
544 if (common_flag = 2) then -- Delete original and common bills
545 /*
546 Process all Orgs except for original Bill Org as it is already in
547 Delete entities Table
548 */
549
550 FOR c_bom_entities in bom_entities
551 LOOP --- ENTITIES LOOP
552 current_bill_seq := c_bom_entities.bill_sequence_id;
553 current_rtg_seq := c_bom_entities.routing_sequence_id;
554 del_ent_type := c_bom_entities.delete_entity_type;
555 del_status := c_bom_entities.delete_status_type;
556 inv_item_id := c_bom_entities.inventory_item_id;
557 alt_desg := c_bom_entities.alternate_designator;
558 item_concat_seg := c_bom_entities.item_concat_segments;
559 last_upd_by := c_bom_entities.last_updated_by;
560 crtd_by := c_bom_entities.created_by;
561 current_del_seq := c_bom_entities.delete_entity_sequence_id;
562 if (current_bill_seq is not null) then
563 begin
564 select common_bill_sequence_id
565 into common_bill
566 from bom_bill_of_materials
567 where bill_sequence_id = current_bill_seq;
568 exception
569 when no_data_found then
570 null;
571 end;
572 end if;
573 if ((del_status = 4) and
574 (current_bill_seq is not null) and
575 (current_bill_seq <> common_bill)) then
576 null;
577 else
578 l_index := org_list.FIRST;
579 while (l_index <= org_list.LAST)
580 LOOP --- ORG LIST LOOP
581 current_org := org_list(l_index);
582 if (current_org <> original_org) then
583 /* If Org in Loop is Other than Org passed in Concurrent Request */
584
585 new_bill_seq := get_bill_seq(inv_item_id,
586 current_org,
587 alt_desg);
588
589 new_rtg_seq := get_rtg_seq(inv_item_id,
590 current_org,
591 alt_desg);
592
593 new_item_id := get_item_id(inv_item_id,
594 current_org);
595 --modifications by vhymavat for bug 2441107
596 --spiltting the logic to deal seperately for each delete type
597
598 if (((delete_type = 2)or (delete_type = 6) or (delete_type = 7) or
599 (delete_type = 4))
600 and (new_bill_seq is not NULL)
601 and (current_bill_seq is not null)) --added by arudresh for bug 3735729
602 /* The clause (current_bill_seq is not null) is added as the same alt_desg is
603 * used to identify both bills and routings. This will avoid the case when
604 * bill as well as routing of the same alternate got deleted even if only one
605 * was specified.*/
606 then
607 begin
608 select 'x'
609 into temp
610 from bom_delete_entities
611 where bill_sequence_id= new_bill_seq and
612 inventory_item_id = new_item_id and
613 delete_group_sequence_id = group_id and
614 organization_id = current_org;
615 exception
616 when no_data_found then
617 select bom_delete_entities_s.nextval
618 into new_del_seq
619 from dual;
620 insert into bom_delete_entities
621 (DELETE_ENTITY_SEQUENCE_ID,
622 DELETE_GROUP_SEQUENCE_ID,
623 DELETE_ENTITY_TYPE,
624 BILL_SEQUENCE_ID,
625 ROUTING_SEQUENCE_ID,
626 INVENTORY_ITEM_ID,
627 ORGANIZATION_ID,
628 ALTERNATE_DESIGNATOR,
629 ITEM_DESCRIPTION,
630 ITEM_CONCAT_SEGMENTS,
631 DELETE_STATUS_TYPE,
632 DELETE_DATE,
633 PRIOR_PROCESS_FLAG,
634 PRIOR_COMMIT_FLAG,
635 LAST_UPDATE_DATE,
636 LAST_UPDATED_BY,
637 CREATION_DATE,
638 CREATED_BY,
639 LAST_UPDATE_LOGIN,
640 REQUEST_ID,
641 PROGRAM_APPLICATION_ID,
642 PROGRAM_ID,
643 PROGRAM_UPDATE_DATE)
644 values( new_del_seq,
645 group_id,
646 decode(delete_type,4,4,2),
647 new_bill_seq,
648 null,
649 inv_item_id,
650 current_org,
651 alt_desg,
652 get_item_descr(new_item_id,
653 current_org),
654 get_concat_segs(new_item_id,
655 current_org),
656 decode(delete_type,4,null,
657 5,null,1),
658 null,
659 1,
660 1,
661 sysdate,
662 last_upd_by,
663 sysdate,
664 crtd_by,
665 null,
666 null,
667 null,
668 null,
669 null);
670 when others then
671 null;
672 end;
673 end if; -- Delete Type 2,4,6,7
674
675 if (((delete_type = 3)or (delete_type = 6) or (delete_type = 7) or
676 (delete_type = 5))
677 and (new_rtg_seq is not NULL)
678 and (current_rtg_seq is not null)) --added by arudresh,bug 3735729
679 then
680 begin
681 select 'x'
682 into temp
683 from bom_delete_entities
684 where routing_sequence_id= new_rtg_seq and
685 inventory_item_id = new_item_id and
686 delete_group_sequence_id = group_id and
687 organization_id = current_org;
688 exception
689 when no_data_found then
690 select bom_delete_entities_s.nextval
691 into new_del_seq
692 from dual;
693 insert into bom_delete_entities
694 (DELETE_ENTITY_SEQUENCE_ID,
695 DELETE_GROUP_SEQUENCE_ID,
696 DELETE_ENTITY_TYPE,
697 BILL_SEQUENCE_ID,
698 ROUTING_SEQUENCE_ID,
699 INVENTORY_ITEM_ID,
700 ORGANIZATION_ID,
701 ALTERNATE_DESIGNATOR,
702 ITEM_DESCRIPTION,
703 ITEM_CONCAT_SEGMENTS,
704 DELETE_STATUS_TYPE,
705 DELETE_DATE,
706 PRIOR_PROCESS_FLAG,
707 PRIOR_COMMIT_FLAG,
708 LAST_UPDATE_DATE,
709 LAST_UPDATED_BY,
710 CREATION_DATE,
711 CREATED_BY,
712 LAST_UPDATE_LOGIN,
713 REQUEST_ID,
714 PROGRAM_APPLICATION_ID,
715 PROGRAM_ID,
716 PROGRAM_UPDATE_DATE)
717 values( new_del_seq,
718 group_id,
719 decode(delete_type,5,5,3),
720 null,
721 new_rtg_seq,
722 inv_item_id,
723 current_org,
724 alt_desg,
725 get_item_descr(new_item_id,
726 current_org),
727 get_concat_segs(new_item_id,
728 current_org),
729 decode(delete_type,4,null,
730 5,null,1),
731 null,
732 1,
733 1,
734 sysdate,
735 last_upd_by,
736 sysdate,
737 crtd_by,
738 null,
739 null,
740 null,
741 null,
742 null);
743 when others then
744 null;
745 end;
746 end if; --- Delete Type 3,5,6,7
747
748 if (((delete_type = 1)or (delete_type=7))
749 and (new_item_id is not NULL))
750 then
751 begin
752 select 'x'
753 into temp
754 from bom_delete_entities
755 where inventory_item_id = new_item_id and
756 delete_group_sequence_id = group_id and
757 delete_entity_type = 1 and
758 organization_id = current_org;
759 exception
760 when no_data_found then
761 select bom_delete_entities_s.nextval
762 into new_del_seq
763 from dual;
764 insert into bom_delete_entities
765 (DELETE_ENTITY_SEQUENCE_ID,
766 DELETE_GROUP_SEQUENCE_ID,
767 DELETE_ENTITY_TYPE,
768 BILL_SEQUENCE_ID,
769 ROUTING_SEQUENCE_ID,
770 INVENTORY_ITEM_ID,
771 ORGANIZATION_ID,
772 ALTERNATE_DESIGNATOR,
773 ITEM_DESCRIPTION,
774 ITEM_CONCAT_SEGMENTS,
775 DELETE_STATUS_TYPE,
776 DELETE_DATE,
777 PRIOR_PROCESS_FLAG,
778 PRIOR_COMMIT_FLAG,
779 LAST_UPDATE_DATE,
780 LAST_UPDATED_BY,
781 CREATION_DATE,
782 CREATED_BY,
783 LAST_UPDATE_LOGIN,
784 REQUEST_ID,
785 PROGRAM_APPLICATION_ID,
786 PROGRAM_ID,
787 PROGRAM_UPDATE_DATE)
788 values( new_del_seq,
789 group_id,
790 1,
791 null,
792 null,
793 inv_item_id,
794 current_org,
795 alt_desg,
796 get_item_descr(new_item_id,
797 current_org),
798 get_concat_segs(new_item_id,
799 current_org),
800 decode(delete_type,4,null,
801 5,null,1),
802 null,
803 1,
804 1,
805 sysdate,
806 last_upd_by,
807 sysdate,
808 crtd_by,
809 null,
810 null,
811 null,
812 null,
813 null);
814 when others then
815 null;
816 end;
817 end if; -- Delete Type 1,7
818
819
820 IF delete_type in (4,5) then
821 FOR c_sub_entity in sub_entity
822 LOOP --- SUB ENTITY LOOP
823 component_id := c_sub_entity.component_item_id;
824 oper_seq_num := c_sub_entity.operation_seq_num;
825 effective_date := c_sub_entity.effectivity_date;
826 /*
827 new_comp_seq := get_comp_seq(new_bill_seq,
828 c_sub_entity.component_item_id,
829 c_sub_entity.operation_seq_num,
830 c_sub_entity.effectivity_date);
831 */
832 new_operation_seq := get_oper_seq( new_rtg_seq,
833 c_sub_entity.operation_seq_num,
834 c_sub_entity.effectivity_date,
835 c_sub_entity.operation_department_code,
836 current_org
837 );
838 if ((delete_type=5) and new_operation_seq is not NULL) then
839 begin
840 select 'x'
841 into temp
842 from bom_delete_sub_entities
843 where delete_entity_sequence_id = new_del_seq and
844 ((component_sequence_id = new_comp_seq) or
845 (operation_sequence_id = new_operation_seq));
846 exception
847 when no_data_found then
848 insert into bom_delete_sub_entities
849 (DELETE_ENTITY_SEQUENCE_ID,
850 COMPONENT_SEQUENCE_ID,
851 OPERATION_SEQUENCE_ID,
852 OPERATION_SEQ_NUM,
853 EFFECTIVITY_DATE,
854 COMPONENT_ITEM_ID,
855 COMPONENT_CONCAT_SEGMENTS,
856 ITEM_NUM,
857 DISABLE_DATE,
858 DESCRIPTION,
859 OPERATION_DEPARTMENT_CODE,
860 DELETE_STATUS_TYPE,
861 DELETE_DATE,
862 LAST_UPDATE_DATE,
863 LAST_UPDATED_BY,
864 CREATION_DATE,
865 CREATED_BY,
866 LAST_UPDATE_LOGIN,
867 REQUEST_ID,
868 PROGRAM_APPLICATION_ID,
869 PROGRAM_ID,
870 PROGRAM_UPDATE_DATE,
871 FROM_END_ITEM_UNIT_NUMBER,
872 TO_END_ITEM_UNIT_NUMBER)
873 values(
874 new_del_seq,
875 decode(delete_type,4,new_comp_seq,5,null),
876 decode(delete_type,5,new_operation_seq,4,null),
877 c_sub_entity.operation_seq_num,
878 c_sub_entity.effectivity_date,
879 c_sub_entity.component_item_id,
880 get_concat_segs(c_sub_entity.component_item_id,
881 current_org),
882 c_sub_entity.item_num,
883 c_sub_entity.disable_date,
884 get_item_descr(c_sub_entity.component_item_id,
885 current_org),
886 get_dept_code(c_sub_entity.operation_department_code,
887 current_org),
888 1,
889 null,
890 sysdate,
891 last_upd_by,
892 sysdate,
893 crtd_by,
894 null,
895 null,
896 null,
897 null,
898 null,
899 null,
900 null);
901 when others then
902 null;
903 end;
904 END IF; -- Delete Type 5
905 IF delete_type = 4 then
906 FOR c_compseq in compseq
907 LOOP
908 new_comp_seq := c_compseq.component_sequence_id;
909 if new_comp_seq is not NULL then
910 begin
911 select 'x'
912 into temp
913 from bom_delete_sub_entities
914 where delete_entity_sequence_id = new_del_seq and
915 ((component_sequence_id = new_comp_seq) or
916 (operation_sequence_id = new_operation_seq));
917 exception
918 when no_data_found then
919 insert into bom_delete_sub_entities
920 (DELETE_ENTITY_SEQUENCE_ID,
921 COMPONENT_SEQUENCE_ID,
922 OPERATION_SEQUENCE_ID,
923 OPERATION_SEQ_NUM,
924 EFFECTIVITY_DATE,
925 COMPONENT_ITEM_ID,
926 COMPONENT_CONCAT_SEGMENTS,
927 ITEM_NUM,
928 DISABLE_DATE,
929 DESCRIPTION,
930 OPERATION_DEPARTMENT_CODE,
931 DELETE_STATUS_TYPE,
932 DELETE_DATE,
933 LAST_UPDATE_DATE,
934 LAST_UPDATED_BY,
935 CREATION_DATE,
936 CREATED_BY,
937 LAST_UPDATE_LOGIN,
938 REQUEST_ID,
939 PROGRAM_APPLICATION_ID,
940 PROGRAM_ID,
941 PROGRAM_UPDATE_DATE,
942 FROM_END_ITEM_UNIT_NUMBER,
943 TO_END_ITEM_UNIT_NUMBER)
944 values(
945 new_del_seq,
946 decode(delete_type,4,new_comp_seq,5,null),
947 decode(delete_type,5,new_operation_seq,4,null),
948 c_sub_entity.operation_seq_num,
949 c_sub_entity.effectivity_date,
950 c_sub_entity.component_item_id,
951 get_concat_segs(c_sub_entity.component_item_id,
952 current_org),
953 c_sub_entity.item_num,
954 c_sub_entity.disable_date,
955 get_item_descr(c_sub_entity.component_item_id,
956 current_org),
957 get_dept_code(c_sub_entity.operation_department_code,
958 current_org),
959 1,
960 null,
961 sysdate,
962 last_upd_by,
963 sysdate,
964 crtd_by,
965 null,
966 null,
967 null,
968 null,
969 null,
970 null,
971 null);
972 when others then
973 null;
974 end;
975 END IF; -- Delete Type 4 or 5
976 END LOOP;
977 END IF;
978 END LOOP; --- SUB ENTITY LOOP
979 END IF; -- Delete Type 4 or 5
980 end if; -- Current org <> original org
981 l_index := org_list.NEXT(l_index);
982 END LOOP; -- ORG LIST LOOP
983 end if;
984 END LOOP; -- ENTITIES LOOP
985 end if;
986 END process_original_sub_entities;
987
988 /* ------------------------ Insert_common_bill_details ------------------------
989 NAME
990 insert_common_bills
991 Entities Table
992 DESCRIPTION
993 Insert the common bill details in Bom_delete_entities
994
995 MODIFIES
996 BOM_DELETE_ENTITIES Table
997 ---------------------------------------------------------------------------*/
998
999
1000 PROCEDURE insert_common_bills(group_id IN NUMBER,
1001 delete_type IN NUMBER)
1002 IS
1003 delete_org_type bom_delete_groups.delete_org_type%type;
1004 delete_org_hrchy bom_delete_groups.organization_hierarchy%type;
1005 delete_common_flag bom_delete_groups.delete_common_bill_flag%type;
1006 current_org_id bom_delete_groups.organization_id%type;
1007 current_org_name org_access_view.organization_name%type;
1008 del_org_list inv_orghierarchy_pvt.orgid_tbl_type;
1009 BEGIN
1010 delete_org_type := get_delorg_type(group_id);
1011 delete_org_hrchy := get_delorg_hrchy(group_id);
1012 delete_common_flag := get_common_flag(group_id);
1013 current_org_id := get_delorg_id(group_id);
1014 current_org_name := get_delorg_name(current_org_id);
1015
1016 get_delorg_list(delete_org_type,
1017 delete_org_hrchy,
1018 current_org_id,
1019 current_org_name,
1020 del_org_list);
1021
1022 process_delete_entities(delete_type,
1023 group_id,
1024 current_org_id,
1025 del_org_list);
1026
1027 END insert_common_bills;
1028
1029 /* ------------------------ Insert_original_bills ------------------------
1030 NAME
1031 insert_original_bills in Delete Entities Table
1032 and Component,Operation Info in Sub Entities Table
1033 DESCRIPTION
1034 Insert the original bill details in Bom_delete_entities
1035 Insert Component and Operation Info in Delete Sub Entities Table
1036
1037 MODIFIES
1038 BOM_DELETE_ENTITIES Table
1039 BOM_DELETE_SUB_ENTITIES Table
1040 ---------------------------------------------------------------------------*/
1041 PROCEDURE insert_original_bills(group_id IN NUMBER,
1042 delete_type IN NUMBER)
1043 IS
1044 delete_org_type bom_delete_groups.delete_org_type%type;
1045 delete_org_hrchy bom_delete_groups.organization_hierarchy%type;
1046 delete_common_flag bom_delete_groups.delete_common_bill_flag%type;
1047 current_org_id bom_delete_groups.organization_id%type;
1048 current_org_name org_access_view.organization_name%type;
1049 del_org_list inv_orghierarchy_pvt.orgid_tbl_type;
1050
1051 BEGIN
1052 delete_org_type := get_delorg_type(group_id);
1053 delete_org_hrchy := get_delorg_hrchy(group_id);
1054 delete_common_flag := get_common_flag(group_id);
1055 current_org_id := get_delorg_id(group_id);
1056 current_org_name := get_delorg_name(current_org_id);
1057
1058 modify_original_bills(group_id,
1059 delete_common_flag);
1060
1061 get_delorg_list(delete_org_type,
1062 delete_org_hrchy,
1063 current_org_id,
1064 current_org_name,
1065 del_org_list);
1066
1067 process_original_sub_entities(delete_type,
1068 group_id,
1069 current_org_id,
1070 delete_common_flag,
1071 del_org_list);
1072
1073 END insert_original_bills;
1074
1075 END bom_delete_entity;