[Home] [Help]
PACKAGE BODY: APPS.MODAL_DELETE
Source
1 PACKAGE BODY MODAL_DELETE AS
2 /*$Header: BOMMDELB.pls 120.1 2005/06/21 05:24:45 appldev ship $*/
3
4 /* ---------------------------- Delete_Manager ------------------------------*/
5 /*
6 NAME
7 Delete_Manager
8 DESCRIPTION
9 Create Delete Groups for Bills, Components, Routings and Operations for
10 Bill and Routing forms.
11 REQUIRES
12 new_group_seq_id Seq Id of new group
13 name Delete Group name
14 group_desc Delete Group description
15 org_id Org Id
16 bom_or_eng 1 - bom
17 2 - eng
18 del_type 2 - Bill
19 3 - Routing
20 4 - Component
21 5 - Operation
22 ent_bill_seq_id Bill Seq Id
23 ent_rtg_seq_id Routing Seq Id
24 ent_inv_item_id Bill or Routing Item Id
25 ent_alt_designator Bill or Routing Alternate
26 ent_comp_seq_id Component Sequence Id
27 ent_op_seq_id Operation Sequence Id
28 user_id User Id
29 MODIFIES
30 BOM_DELETE_GROUPS
31 BOM_DELETE_ENTITIES
32 BOM_DELETE_SUB_ENTITIES
33 RETURNS
34 New Delete Group Sequence Id
35 NOTES
36 Function sends error message to Forms message line and raises an
37 exception if there is an error.
38 -----------------------------------------------------------------------------*/
39 FUNCTION DELETE_MANAGER(
40 new_group_seq_id IN NUMBER,
41 name IN VARCHAR2,
42 group_desc IN VARCHAR2,
43 org_id IN NUMBER,
44 bom_or_eng IN NUMBER,
45 del_type IN NUMBER,
46 ent_bill_seq_id IN NUMBER,
47 ent_rtg_seq_id IN NUMBER,
48 ent_inv_item_id IN NUMBER,
49 ent_alt_designator IN VARCHAR2,
50 ent_comp_seq_id IN NUMBER,
51 ent_op_seq_id IN NUMBER,
52 user_id IN NUMBER
53 ) RETURN NUMBER IS
54 ITEM CONSTANT NUMBER := 1;
55 BOM CONSTANT NUMBER := 2;
56 ROUTING CONSTANT NUMBER := 3;
57 COMP CONSTANT NUMBER := 4;
58 OPER CONSTANT NUMBER := 5;
59
60 new_group_seq_id_v NUMBER := new_group_seq_id;
61 err_msg VARCHAR2(240);
62 sql_stmt_num NUMBER;
63 status_value NUMBER;
64 del_header NUMBER;
65 Cursor GetNewGroup is
66 SELECT BOM_DELETE_GROUPS_S.NEXTVAL group_id
67 FROM SYS.DUAL;
68 Cursor GetEntity is
69 Select bde.delete_entity_sequence_id
70 From bom_delete_entities bde
71 Where bde.delete_group_sequence_id = new_group_seq_id_v
72 And nvl(bde.bill_sequence_id, 0) = nvl(ent_bill_seq_id, 0)
73 And nvl(bde.routing_sequence_id, 0) = nvl(ent_rtg_seq_id, 0)
74 And nvl(bde.inventory_item_id, 0) = nvl(ent_inv_item_id, 0)
75 And nvl(bde.organization_id, 0) = nvl(org_id, 0);
76 X_EntSeqId bom_delete_entities.delete_entity_sequence_id%type := null;
77 Cursor GetNewEntity is
78 Select BOM_DELETE_ENTITIES_S.NEXTVAL Entity_Id
79 From sys.dual;
80 Cursor GetComponent is
81 SELECT BIC.COMPONENT_SEQUENCE_ID,
82 BIC.EFFECTIVITY_DATE,
83 BIC.DISABLE_DATE,
84 BIC.FROM_END_ITEM_UNIT_NUMBER,
85 BIC.TO_END_ITEM_UNIT_NUMBER,
86 BIC.ITEM_NUM,
87 BIC.OPERATION_SEQ_NUM,
88 BIC.COMPONENT_ITEM_ID,
89 MSIK.CONCATENATED_SEGMENTS,
90 MSIK.DESCRIPTION
91 FROM MTL_SYSTEM_ITEMS_KFV MSIK,
92 BOM_INVENTORY_COMPONENTS BIC
93 WHERE BIC.COMPONENT_SEQUENCE_ID = ent_comp_seq_id
94 AND MSIK.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
95 AND MSIK.ORGANIZATION_ID = org_id
96 AND NOT EXISTS (
97 SELECT NULL
98 FROM BOM_DELETE_SUB_ENTITIES BDSE
99 WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
100 AND BDSE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID);
101 Cursor GetOperation is
102 SELECT BOS.OPERATION_SEQUENCE_ID,
103 BOS.EFFECTIVITY_DATE,
104 BOS.DISABLE_DATE,
105 BOS.OPERATION_SEQ_NUM,
106 BOS.OPERATION_DESCRIPTION,
107 BD.DEPARTMENT_CODE
108 FROM BOM_DEPARTMENTS BD,
109 BOM_OPERATION_SEQUENCES BOS
110 WHERE BOS.OPERATION_SEQUENCE_ID = ent_op_seq_id
111 AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
112 AND NOT EXISTS (
113 SELECT NULL
114 FROM BOM_DELETE_SUB_ENTITIES BDSE
115 WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
116 AND BDSE.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID);
117 BEGIN
118 SAVEPOINT BEGIN_DELETE;
119
120 IF new_group_seq_id IS NULL THEN
121 sql_stmt_num :=10;
122 For X_NewGroup in GetNewGroup loop
123 new_group_seq_id_v := X_NewGroup.group_id;
124
125 sql_stmt_num :=20;
126
127 INSERT INTO BOM_DELETE_GROUPS
128 (DELETE_GROUP_SEQUENCE_ID,
129 DELETE_GROUP_NAME,
130 ORGANIZATION_ID,
131 DELETE_TYPE,
132 ACTION_TYPE,
133 DATE_LAST_SUBMITTED,
134 DESCRIPTION,
135 ENGINEERING_FLAG,
136 LAST_UPDATE_DATE,
137 LAST_UPDATED_BY,
138 CREATION_DATE,
139 CREATED_BY,
140 LAST_UPDATE_LOGIN,
141 REQUEST_ID,
142 PROGRAM_APPLICATION_ID,
143 PROGRAM_ID,
144 PROGRAM_UPDATE_DATE,
145 ORGANIZATION_HIERARCHY,
146 DELETE_ORG_TYPE,
147 DELETE_COMMON_BILL_FLAG)
148 VALUES
149 (new_group_seq_id_v,
150 name,
151 org_id,
152 del_type,
153 1,
154 NULL,
155 group_desc,
156 bom_or_eng,
157 SYSDATE,
158 user_id,
159 SYSDATE,
160 user_id,
161 user_id,
162 NULL,
163 NULL,
164 NULL,
165 NULL,
166 NULL,
167 1,
168 2);
169 End loop;
170 END IF;
171 IF del_type = COMP THEN
172 status_value := NULL;
173 del_header := BOM;
174 ELSIF del_type = OPER then
175 status_value := NULL;
176 del_header := ROUTING;
177 ELSE
178 status_value := 1;
179 del_header := del_type;
180 END IF;
181
182 -- Check for duplicate Entities
183 For X_Entity in GetEntity loop
184 X_EntSeqId := X_Entity.delete_entity_sequence_id;
185 End loop;
186 If X_EntSeqId is null then
187 sql_stmt_num := 30;
188 For X_NewEntity in GetNewEntity loop
189 X_EntSeqId := X_NewEntity.Entity_Id;
190
191 sql_stmt_num := 40;
192 INSERT INTO bom_delete_entities
193 (DELETE_ENTITY_SEQUENCE_ID,
194 DELETE_GROUP_SEQUENCE_ID,
195 DELETE_ENTITY_TYPE,
196 BILL_SEQUENCE_ID,
197 ROUTING_SEQUENCE_ID,
198 INVENTORY_ITEM_ID,
199 ORGANIZATION_ID,
200 ALTERNATE_DESIGNATOR,
201 ITEM_DESCRIPTION,
202 ITEM_CONCAT_SEGMENTS,
203 DELETE_STATUS_TYPE,
204 DELETE_DATE,
205 PRIOR_PROCESS_FLAG,
206 PRIOR_COMMIT_FLAG,
207 LAST_UPDATE_DATE,
208 LAST_UPDATED_BY,
209 CREATION_DATE,
210 CREATED_BY,
211 LAST_UPDATE_LOGIN,
212 REQUEST_ID ,
213 PROGRAM_APPLICATION_ID,
214 PROGRAM_ID,
215 PROGRAM_UPDATE_DATE)
216 SELECT
217 X_EntSeqId,
218 new_group_seq_id_v,
219 del_header,
220 ent_bill_seq_id,
221 ent_rtg_seq_id,
222 ent_inv_item_id,
223 org_id,
224 ent_alt_designator,
225 MSIK.DESCRIPTION,
226 MSIK.CONCATENATED_SEGMENTS,
227 status_value, -- PENDING
228 NULL, -- Delete date should be null
229 2, -- Prior process flag
230 1, -- Prior Commit flag
231 SYSDATE,
232 user_id,
233 SYSDATE,
234 user_id,
235 user_id,
236 NULL,
237 NULL,
238 NULL,
239 NULL
240 FROM MTL_SYSTEM_ITEMS_KFV MSIK
241 WHERE MSIK.INVENTORY_ITEM_ID = ent_inv_item_id
242 AND MSIK.ORGANIZATION_ID = org_id;
243 End loop; -- new entity
244 End if; -- entity did not exist
245
246 -- Avoid duplicate subentities. If Component or Operation already exists,
247 -- the cursor will be empty. Otherwise it will have only one row.
248
249 If del_type = COMP then
250 sql_stmt_num := 50;
251 For X_Component in GetComponent loop
252 sql_stmt_num := 60;
253 INSERT INTO bom_delete_sub_entities
254 (DELETE_ENTITY_SEQUENCE_ID,
255 COMPONENT_SEQUENCE_ID,
256 OPERATION_SEQUENCE_ID,
257 OPERATION_SEQ_NUM,
258 EFFECTIVITY_DATE,
259 FROM_END_ITEM_UNIT_NUMBER,
260 COMPONENT_ITEM_ID,
261 COMPONENT_CONCAT_SEGMENTS,
262 ITEM_NUM,
263 DISABLE_DATE,
264 TO_END_ITEM_UNIT_NUMBER,
265 DESCRIPTION,
266 OPERATION_DEPARTMENT_CODE,
267 DELETE_STATUS_TYPE,
268 DELETE_DATE,
269 LAST_UPDATE_DATE,
270 LAST_UPDATED_BY,
271 CREATION_DATE,
272 CREATED_BY,
273 LAST_UPDATE_LOGIN,
274 REQUEST_ID,
275 PROGRAM_APPLICATION_ID,
276 PROGRAM_ID,
277 PROGRAM_UPDATE_DATE)
278 VALUES
279 (X_EntSeqId,
280 X_Component.COMPONENT_SEQUENCE_ID,
281 null,
282 X_Component.OPERATION_SEQ_NUM,
283 X_Component.EFFECTIVITY_DATE,
284 X_Component.FROM_END_ITEM_UNIT_NUMBER,
285 X_Component.COMPONENT_ITEM_ID,
286 X_Component.CONCATENATED_SEGMENTS,
287 X_Component.ITEM_NUM,
288 X_Component.DISABLE_DATE,
289 X_Component.TO_END_ITEM_UNIT_NUMBER,
290 X_Component.DESCRIPTION,
291 null,
292 1,
293 NULL,
294 SYSDATE,
295 user_id,
296 SYSDATE,
297 user_id,
298 user_id,
299 NULL,
300 NULL,
301 NULL,
302 NULL);
303 End loop; -- insert component
304 Elsif del_type = OPER then
305 sql_stmt_num := 70;
306 For X_Operation in GetOperation loop
307 sql_stmt_num := 80;
308 INSERT INTO bom_delete_sub_entities
309 (DELETE_ENTITY_SEQUENCE_ID,
310 COMPONENT_SEQUENCE_ID,
311 OPERATION_SEQUENCE_ID,
312 OPERATION_SEQ_NUM,
313 EFFECTIVITY_DATE,
314 COMPONENT_ITEM_ID,
315 COMPONENT_CONCAT_SEGMENTS,
316 ITEM_NUM,
317 DISABLE_DATE,
318 DESCRIPTION,
319 OPERATION_DEPARTMENT_CODE,
320 DELETE_STATUS_TYPE,
321 DELETE_DATE,
322 LAST_UPDATE_DATE,
323 LAST_UPDATED_BY,
324 CREATION_DATE,
325 CREATED_BY,
326 LAST_UPDATE_LOGIN,
327 REQUEST_ID,
328 PROGRAM_APPLICATION_ID,
329 PROGRAM_ID,
330 PROGRAM_UPDATE_DATE)
331 VALUES
332 (X_EntSeqId,
333 null,
334 X_Operation.OPERATION_SEQUENCE_ID,
335 X_Operation.OPERATION_SEQ_NUM,
336 X_Operation.EFFECTIVITY_DATE,
337 null,
338 null,
339 null,
340 X_Operation.DISABLE_DATE,
341 X_Operation.OPERATION_DESCRIPTION,
342 X_Operation.DEPARTMENT_CODE,
343 1,
344 NULL,
345 SYSDATE,
346 user_id,
347 SYSDATE,
348 user_id,
349 user_id,
350 NULL,
351 NULL,
352 NULL,
353 NULL);
354 End loop; -- insert operation
355 End if;
356
357 RETURN new_group_seq_id_v;
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 err_msg := 'MODAL DELETE (' ||sql_stmt_num||' ) ' || SQLERRM;
362 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
363 FND_MESSAGE.SET_TOKEN('ENTITY', err_msg);
364 ROLLBACK TO BEGIN_DELETE;
365 APP_EXCEPTION.RAISE_EXCEPTION;
366 END DELETE_MANAGER;
367
368
369 /* --------------------------- Delete_Manager_Oi ----------------------------*/
370 /*
371 NAME
372 Delete_Manager_Oi
373 DESCRIPTION
374 Create Delete Groups for Bills, Components, Routings and Operations for
375 the Open Interface program.
376 REQUIRES
377 new_group_seq_id Seq Id of new group
378 name Delete Group name
379 group_desc Delete Group description
380 org_id Org Id
381 bom_or_eng 1 - bom
382 2 - eng
383 del_type 2 - Bill
384 3 - Routing
385 4 - Component
386 5 - Operation
387 ent_bill_seq_id Bill Seq Id
388 ent_rtg_seq_id Routing Seq Id
389 ent_inv_item_id Bill or Routing Item Id
390 ent_alt_designator Bill or Routing Alternate
391 ent_comp_seq_id Component Sequence Id
392 ent_op_seq_id Operation Sequence Id
393 user_id User Id
394 MODIFIES
395 BOM_DELETE_GROUPS
396 BOM_DELETE_ENTITIES
397 BOM_DELETE_SUB_ENTITIES
398 RETURNS
399 0 if successful
400 SQLCODE if error
401 NOTES
402 -----------------------------------------------------------------------------*/
403 FUNCTION DELETE_MANAGER_OI(
404 new_group_seq_id IN NUMBER,
405 name IN VARCHAR2,
406 group_desc IN VARCHAR2,
407 org_id IN NUMBER,
408 bom_or_eng IN NUMBER,
409 del_type IN NUMBER,
410 ent_bill_seq_id IN NUMBER,
411 ent_rtg_seq_id IN NUMBER,
412 ent_inv_item_id IN NUMBER,
413 ent_alt_designator IN VARCHAR2,
414 ent_comp_seq_id IN NUMBER,
415 ent_op_seq_id IN NUMBER,
416 user_id IN NUMBER,
417 err_text IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
418 ) RETURN NUMBER IS
419 ITEM CONSTANT NUMBER := 1;
420 BOM CONSTANT NUMBER := 2;
421 ROUTING CONSTANT NUMBER := 3;
422 COMP CONSTANT NUMBER := 4;
423 OPER CONSTANT NUMBER := 5;
424
425 new_group_seq_id_v NUMBER := new_group_seq_id;
426 sql_stmt_num NUMBER;
427 status_value NUMBER;
428 del_header NUMBER;
429 INVALID_GRP EXCEPTION;
430
431 Cursor DelGrpType is
432 Select delete_type
433 From BOM_DELETE_GROUPS
434 WHERE DELETE_GROUP_NAME = name;
435
436 Cursor GetNewGroup is
437 SELECT BOM_DELETE_GROUPS_S.NEXTVAL group_id
438 FROM SYS.DUAL;
439
440 Cursor GetEntity is
441 Select bde.delete_entity_sequence_id
442 From bom_delete_entities bde
443 Where bde.delete_group_sequence_id = new_group_seq_id_v
444 And nvl(bde.bill_sequence_id, 0) = nvl(ent_bill_seq_id, 0)
445 And nvl(bde.routing_sequence_id, 0) = nvl(ent_rtg_seq_id, 0)
446 And nvl(bde.inventory_item_id, 0) = nvl(ent_inv_item_id, 0)
447 And nvl(bde.organization_id, 0) = nvl(org_id, 0);
448
449 X_EntSeqId bom_delete_entities.delete_entity_sequence_id%type := null;
450
451 Cursor GetNewEntity is
452 Select BOM_DELETE_ENTITIES_S.NEXTVAL Entity_Id
453 From sys.dual;
454
455 Cursor GetComponent is
456 SELECT BIC.COMPONENT_SEQUENCE_ID,
457 BIC.EFFECTIVITY_DATE,
458 BIC.DISABLE_DATE,
459 BIC.ITEM_NUM,
460 BIC.OPERATION_SEQ_NUM,
461 BIC.COMPONENT_ITEM_ID,
462 MSIK.CONCATENATED_SEGMENTS,
463 MSIK.DESCRIPTION
464 FROM MTL_SYSTEM_ITEMS_KFV MSIK,
465 BOM_INVENTORY_COMPONENTS BIC
466 WHERE BIC.COMPONENT_SEQUENCE_ID = ent_comp_seq_id
467 AND MSIK.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
468 AND MSIK.ORGANIZATION_ID = org_id
469 AND NOT EXISTS (
470 SELECT NULL
471 FROM BOM_DELETE_SUB_ENTITIES BDSE
472 WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
473 AND BDSE.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID);
474
475 Cursor GetOperation is
476 SELECT BOS.OPERATION_SEQUENCE_ID,
477 BOS.EFFECTIVITY_DATE,
478 BOS.DISABLE_DATE,
479 BOS.OPERATION_SEQ_NUM,
480 BOS.OPERATION_DESCRIPTION,
481 BD.DEPARTMENT_CODE
482 FROM BOM_DEPARTMENTS BD,
483 BOM_OPERATION_SEQUENCES BOS
484 WHERE BOS.OPERATION_SEQUENCE_ID = ent_op_seq_id
485 AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
486 AND NOT EXISTS (
487 SELECT NULL
488 FROM BOM_DELETE_SUB_ENTITIES BDSE
489 WHERE BDSE.DELETE_ENTITY_SEQUENCE_ID = X_EntSeqId
490 AND BDSE.OPERATION_SEQUENCE_ID = BOS.OPERATION_SEQUENCE_ID);
491 l_UserId NUMBER;
492 l_LoginId NUMBER;
493 l_RequestId NUMBER;
494 l_ProgramId NUMBER;
495 l_ApplicationId NUMBER;
496 BEGIN
497 -- who columns
498 l_UserId := nvl(Fnd_Global.USER_ID, -1);
499 l_LoginId := Fnd_Global.LOGIN_ID;
500 l_RequestId := Fnd_Global.CONC_REQUEST_ID;
501 l_ProgramId := Fnd_Global.CONC_PROGRAM_ID;
502 l_ApplicationId := Fnd_Global.PROG_APPL_ID;
503
504 SAVEPOINT BEGIN_DELETE;
505
506 For X_DelType in DelGrpType loop
507
508 if (del_type = BOM) then
509 if (X_DelType.delete_type not in (2,6)) then
510 err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
511 'Delete Type:'||to_char(del_type)||
512 'Invalid delete group type';
513 raise INVALID_GRP;
514 end if;
515 elsif (del_type = ROUTING) then
516 if (X_DelType.delete_type not in (3,6)) then
517 err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
518 'Delete Type:'||to_char(del_type)||
519 'Invalid delete group type';
520 raise INVALID_GRP;
521 end if;
522 elsif X_DelType.delete_type <> del_type then
523 err_text:= 'Delete Grp type:'||to_char(X_DelType.delete_type) ||
524 'Delete Type:'||to_char(del_type)||
525 'Invalid delete group type';
526 raise INVALID_GRP;
527 end if;
528 end loop;
529
530 IF new_group_seq_id IS NULL THEN
531 sql_stmt_num :=10;
532 For X_NewGroup in GetNewGroup loop
533 new_group_seq_id_v := X_NewGroup.group_id;
534
535 sql_stmt_num :=20;
536
537 INSERT INTO BOM_DELETE_GROUPS
538 (DELETE_GROUP_SEQUENCE_ID,
539 DELETE_GROUP_NAME,
540 ORGANIZATION_ID,
541 DELETE_TYPE,
542 ACTION_TYPE,
543 DATE_LAST_SUBMITTED,
544 DESCRIPTION,
545 ENGINEERING_FLAG,
546 LAST_UPDATE_DATE,
547 LAST_UPDATED_BY,
548 CREATION_DATE,
549 CREATED_BY,
550 LAST_UPDATE_LOGIN,
551 REQUEST_ID,
552 PROGRAM_APPLICATION_ID,
553 PROGRAM_ID,
554 PROGRAM_UPDATE_DATE,
555 ORGANIZATION_HIERARCHY,
556 DELETE_ORG_TYPE,
557 DELETE_COMMON_BILL_FLAG)
558 VALUES
559 (new_group_seq_id_v,
560 name,
561 org_id,
562 del_type,
563 1,
564 NULL,
565 group_desc,
566 bom_or_eng,
567 SYSDATE,
568 l_UserId,
569 SYSDATE,
570 l_UserId,
571 l_LoginId,
572 l_RequestId,
573 l_ApplicationId,
574 l_ProgramId,
575 sysdate,
576 NULL,
577 1,
578 2);
579 End loop;
580 END IF;
581 IF del_type = COMP THEN
582 status_value := NULL;
583 del_header := BOM;
584 ELSIF del_type = OPER then
585 status_value := NULL;
586 del_header := ROUTING;
587 ELSE
588 status_value := 1;
589 del_header := del_type;
590 END IF;
591
592 -- Check for duplicate Entities
593 For X_Entity in GetEntity loop
594 X_EntSeqId := X_Entity.delete_entity_sequence_id;
595 End loop;
596 If X_EntSeqId is null then
597 sql_stmt_num := 30;
598 For X_NewEntity in GetNewEntity loop
599 X_EntSeqId := X_NewEntity.Entity_Id;
600
601 sql_stmt_num := 40;
602 INSERT INTO bom_delete_entities
603 (DELETE_ENTITY_SEQUENCE_ID,
604 DELETE_GROUP_SEQUENCE_ID,
605 DELETE_ENTITY_TYPE,
606 BILL_SEQUENCE_ID,
607 ROUTING_SEQUENCE_ID,
608 INVENTORY_ITEM_ID,
609 ORGANIZATION_ID,
610 ALTERNATE_DESIGNATOR,
611 ITEM_DESCRIPTION,
612 ITEM_CONCAT_SEGMENTS,
613 DELETE_STATUS_TYPE,
614 DELETE_DATE,
615 PRIOR_PROCESS_FLAG,
616 PRIOR_COMMIT_FLAG,
617 LAST_UPDATE_DATE,
618 LAST_UPDATED_BY,
619 CREATION_DATE,
620 CREATED_BY,
621 LAST_UPDATE_LOGIN,
622 REQUEST_ID ,
623 PROGRAM_APPLICATION_ID,
624 PROGRAM_ID,
625 PROGRAM_UPDATE_DATE)
626 SELECT
627 X_EntSeqId,
628 new_group_seq_id_v,
629 del_header,
630 ent_bill_seq_id,
631 ent_rtg_seq_id,
632 ent_inv_item_id,
633 org_id,
634 ent_alt_designator,
635 MSIK.DESCRIPTION,
636 MSIK.CONCATENATED_SEGMENTS,
637 status_value, -- PENDING
638 NULL, -- Delete date should be null
639 2, -- Prior process flag
640 1, -- Prior Commit flag
641 SYSDATE,
642 l_UserId,
643 SYSDATE,
644 l_UserId,
645 l_LoginId,
646 l_RequestId,
647 l_ApplicationId,
648 l_ProgramId,
649 sysdate
650 FROM MTL_SYSTEM_ITEMS_KFV MSIK
651 WHERE MSIK.INVENTORY_ITEM_ID = ent_inv_item_id
652 AND MSIK.ORGANIZATION_ID = org_id;
653 End loop; -- new entity
654 End if; -- entity did not exist
655
656 -- Avoid duplicate subentities. If Component or Operation already exists,
657 -- the cursor will be empty. Otherwise it will have only one row.
658
659 If del_type = COMP then
660 sql_stmt_num := 50;
661 For X_Component in GetComponent loop
662 sql_stmt_num := 60;
663 INSERT INTO bom_delete_sub_entities
664 (DELETE_ENTITY_SEQUENCE_ID,
665 COMPONENT_SEQUENCE_ID,
666 OPERATION_SEQUENCE_ID,
667 OPERATION_SEQ_NUM,
668 EFFECTIVITY_DATE,
669 COMPONENT_ITEM_ID,
670 COMPONENT_CONCAT_SEGMENTS,
671 ITEM_NUM,
672 DISABLE_DATE,
673 DESCRIPTION,
674 OPERATION_DEPARTMENT_CODE,
675 DELETE_STATUS_TYPE,
676 DELETE_DATE,
677 LAST_UPDATE_DATE,
678 LAST_UPDATED_BY,
679 CREATION_DATE,
680 CREATED_BY,
681 LAST_UPDATE_LOGIN,
682 REQUEST_ID,
683 PROGRAM_APPLICATION_ID,
684 PROGRAM_ID,
685 PROGRAM_UPDATE_DATE)
686 VALUES
687 (X_EntSeqId,
688 X_Component.COMPONENT_SEQUENCE_ID,
689 null,
690 X_Component.OPERATION_SEQ_NUM,
691 X_Component.EFFECTIVITY_DATE,
692 X_Component.COMPONENT_ITEM_ID,
693 X_Component.CONCATENATED_SEGMENTS,
694 X_Component.ITEM_NUM,
695 X_Component.DISABLE_DATE,
696 X_Component.DESCRIPTION,
697 null,
698 1,
699 NULL,
700 SYSDATE,
701 l_UserId,
702 SYSDATE,
703 l_UserId,
704 l_LoginId,
705 l_RequestId,
706 l_ApplicationId,
707 l_ProgramId,
708 sysdate);
709 End loop; -- insert component
710 Elsif del_type = OPER then
711 sql_stmt_num := 70;
712 For X_Operation in GetOperation loop
713 sql_stmt_num := 80;
714 INSERT INTO bom_delete_sub_entities
715 (DELETE_ENTITY_SEQUENCE_ID,
716 COMPONENT_SEQUENCE_ID,
717 OPERATION_SEQUENCE_ID,
718 OPERATION_SEQ_NUM,
719 EFFECTIVITY_DATE,
720 COMPONENT_ITEM_ID,
721 COMPONENT_CONCAT_SEGMENTS,
722 ITEM_NUM,
723 DISABLE_DATE,
724 DESCRIPTION,
725 OPERATION_DEPARTMENT_CODE,
726 DELETE_STATUS_TYPE,
727 DELETE_DATE,
728 LAST_UPDATE_DATE,
729 LAST_UPDATED_BY,
730 CREATION_DATE,
731 CREATED_BY,
732 LAST_UPDATE_LOGIN,
733 REQUEST_ID,
734 PROGRAM_APPLICATION_ID,
735 PROGRAM_ID,
736 PROGRAM_UPDATE_DATE)
737 VALUES
738 (X_EntSeqId,
739 null,
740 X_Operation.OPERATION_SEQUENCE_ID,
741 X_Operation.OPERATION_SEQ_NUM,
742 X_Operation.EFFECTIVITY_DATE,
743 null,
744 null,
745 null,
746 X_Operation.DISABLE_DATE,
747 X_Operation.OPERATION_DESCRIPTION,
748 X_Operation.DEPARTMENT_CODE,
749 1,
750 NULL,
751 SYSDATE,
752 l_UserId,
753 SYSDATE,
754 l_UserId,
755 l_LoginId,
756 l_RequestId,
757 l_ApplicationId,
758 l_ProgramId,
759 sysdate);
760 End loop; -- insert operation
761 End if;
762
763 RETURN(0);
764
765 EXCEPTION
766 WHEN INVALID_GRP THEN
767 ROLLBACK TO BEGIN_DELETE;
768 RETURN(-1);
769
770 WHEN OTHERS THEN
771 err_text := 'BOMMDELB(OI-' ||sql_stmt_num||' ) '||substrb(SQLERRM,1,500);
772 ROLLBACK TO BEGIN_DELETE;
773 RETURN(SQLCODE);
774 END DELETE_MANAGER_OI;
775
776
777 END MODAL_DELETE;