[Home] [Help]
PACKAGE BODY: APPS.BOM_DELETE_ITEMS
Source
1 PACKAGE BODY BOM_DELETE_ITEMS AS
2 /* $Header: BOMDELTB.pls 120.1 2005/06/08 15:28:05 appldev $ */
3 -- +==========================================================================+
4 -- | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 -- | All rights reserved. |
6 -- +==========================================================================+
7 -- | |
8 -- | File Name : BOMDELTB.pls |
9 -- | Description : Populate BOM_DELETE_ENTITIES and BOM_DELETE_SUB_ENTITIES |
10 -- | when called by BOMFDDEL (Item, BOM and Routing Delete Form |
11 -- | under the following conditions: |
12 -- | - called on return from Item Catalog Search |
13 -- | - Component or Routing Where Used request |
14 -- | - Master Org to Child Org explosion |
15 -- | - BOM and Routing explosion |
16 -- | Parameters : org_id organization_id |
17 -- | err_msg error message out buffer |
18 -- | error_code error code out. returns sql error code |
19 -- | if sql error. |
20 -- | Revision |
21 -- | 08-MAR-95 Anand Rajaraman Creation |
22 -- | 07-JUL-95 Anand Rajaraman Changes after Code Review (Shreyas Shah) |
23 -- | Removed expiration_time (Calvin Siew) |
24 -- | |
25 -- +==========================================================================+
26
27 -- +--------------------------- RAISE_ERROR ----------------------------------+
28
29 -- NAME
30 -- RAISE_ERROR
31
32 -- DESCRIPTION
33 -- Raise generic error message. For sql error failures, places the SQLERRM
34 -- error on the message stack
35
36 -- REQUIRES
37 -- func_name: function name
38 -- stmt_num : statement number
39
40 -- OUTPUT
41
42 -- NOTES
43
44 -- +--------------------------------------------------------------------------+
45
46 PROCEDURE RAISE_ERROR (
47 func_name VARCHAR2,
48 stmt_num NUMBER
49 )
50 IS
51 err_text VARCHAR2(1000);
52 BEGIN
53 ROLLBACK;
54 err_text := func_name || '(' || stmt_num || ') ' || SQLERRM;
55 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
56 FND_MESSAGE.SET_TOKEN('entity', err_text);
57 APP_EXCEPTION.RAISE_EXCEPTION;
58 EXCEPTION
59 WHEN OTHERS THEN
60 NULL;
61 END RAISE_ERROR;
62
63
64 -- +-------------------------- UPDATE_UNPROCESSED_ROWS -----------------------+
65
66 -- NAME
67 -- UPDATE_UNPROCESSED_ROWS
68
69 -- DESCRIPTION
70 -- Update all unprocessed rows that exist, to be processed for this group_id
71
72 -- REQUIRES
73 -- delete_group_id
74
75 -- OUTPUT
76
77 -- RETURNS
78
79 -- NOTES
80
81 -- +--------------------------------------------------------------------------+
82
83 PROCEDURE UPDATE_UNPROCESSED_ROWS
84 (
85 delete_group_id IN NUMBER
86 )
87 IS
88 BEGIN
89 UPDATE BOM_DELETE_ENTITIES
90 SET PRIOR_process_flag = 1
91 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
92 AND PRIOR_process_flag = 2;
93 EXCEPTION
94 WHEN OTHERS THEN
95 RAISE_ERROR(
96 func_name => 'UPDATE_UNPROCESSED_ROWS',
97 stmt_num => 1);
98 END UPDATE_UNPROCESSED_ROWS;
99
100
101 -- +------------------------------- POPULATE_DELETE --------------------------+
102
103 -- NAME
104 -- POPULATE_DELETE
105
106 -- DESCRIPTION
107 -- Populate BOM_DELETE_ENTITIES and BOM_DELETE_SUB_ENTITIES
108
109 -- REQUIRES
110 -- org_id: organization id
111 -- last_login_id
112 -- catalog_search_id: Item catalog search id
113 -- component_id
114 -- delete_group_id
115 -- delete_type
116 -- "1" - ITEM
117 -- "2" - BOM
118 -- "3" - ROUTING
119 -- "4" - COMPONENT
120 -- "5" - OPERATION
121 -- "6" - BOM and ROUTING
122 -- "7" - ITEM/BOM and ROUTING
123 -- del_grp_type
124 -- "1" - Non-ENG Items only
125 -- "2" - ENG Items only
126 -- process_type
127 -- "1" - called from form
128 -- "2" - called from search region
129 -- expiration_date
130
131 -- OUTPUT
132
133 -- RETURNS
134
135 -- NOTES
136
137 -- +--------------------------------------------------------------------------+
138
139 PROCEDURE POPULATE_DELETE
140 (
141 org_id IN NUMBER,
142 last_login_id IN NUMBER DEFAULT -1,
143 catalog_search_id IN NUMBER,
144 component_id IN NUMBER,
145 delete_group_id IN NUMBER,
146 delete_type IN NUMBER,
147 del_grp_type IN NUMBER,
148 process_type IN NUMBER,
149 expiration_date IN DATE) IS
150
151 invalid_parameter EXCEPTION;
152 finale EXCEPTION;
153 stmt_num NUMBER;
154 var_text VARCHAR2(1000);
155 process_flag NUMBER; -- =1 FOR PROCESSED, =2 FOR NOT PROCESSED
156 eng_flag NUMBER; -- =1 FOR NON-ENG ITEMS, =2 FOR ENG ITEMS
157 item_eng_flag CHAR;
158 commit_flag NUMBER; -- =1 IF ON COMMIT FROM FORM
159 -- =2 IF FROM SEARCH REGION
160 del_stat_type NUMBER;
161 master_org_flag CHAR;
162 userid NUMBER;
163 BEGIN
164
165 userid := FND_GLOBAL.USER_ID ;
166 -- process_flag = 2 MEANS NOT PROCESSED, SO ROWS STORED IN MASTER ORG
167 -- EXPLOSION WILL BE PROCESSED BY BOM AND ROUTING EXPLOSION
168
169 IF delete_type = 1 THEN
170 process_flag := 1;
171 ELSIF delete_type = 7 THEN
172 process_flag := 2;
173 END IF;
174
175 stmt_num := 1;
176 eng_flag := del_grp_type;
177 IF eng_flag = 1 THEN
178 item_eng_flag := 'N';
179 ELSIF eng_flag = 2 THEN
180 item_eng_flag := 'Y';
181 ELSE
182 var_text := 'delete group type';
183 RAISE invalid_parameter;
184 END IF;
185
186 stmt_num := 2;
187 commit_flag := 0;
188 IF process_type = 1 THEN
189 commit_flag := 2;
190 ELSIF process_type = 2 THEN
191 commit_flag := 1;
192 ELSE
193 var_text := 'process type';
194 RAISE invalid_parameter;
195 END IF;
196
197 stmt_num := 3;
198 IF delete_type IN (1, 2, 3, 7) THEN
199 del_stat_type := 1;
200 ELSIF delete_type IN (4, 5, 6) THEN
201 del_stat_type := -1;
202 ELSE
203 var_text := 'delete type';
204 RAISE invalid_parameter;
205 END IF;
206
207 IF process_type = 1 THEN
208
209 -- CATALOG SEARCH: GET ITEMS FROM MTL_CATALOG_SEARCH_ITEMS
210
211 IF catalog_search_id > 0 THEN
212 stmt_num := 4;
213
214 -- CASE 1 ITEM
215 -- CASE 6 BOM AND ROUTING
216 -- CASE 7 ITEM/BOM AND ROUTING
217
218 IF delete_type IN (1, 6, 7) THEN
219 INSERT INTO BOM_DELETE_ENTITIES(
220 DELETE_ENTITY_SEQUENCE_ID,
221 DELETE_GROUP_SEQUENCE_ID,
222 DELETE_ENTITY_TYPE,
223 INVENTORY_ITEM_ID,
224 ORGANIZATION_ID,
225 ALTERNATE_DESIGNATOR,
226 ITEM_DESCRIPTION,
227 ITEM_CONCAT_SEGMENTS,
228 DELETE_STATUS_TYPE,
229 PRIOR_PROCESS_FLAG,
230 PRIOR_COMMIT_FLAG,
231 BILL_SEQUENCE_ID,
232 LAST_UPDATE_DATE,
233 LAST_UPDATED_BY,
234 CREATION_DATE,
235 CREATED_BY,
236 LAST_UPDATE_LOGIN)
237 SELECT
238 BOM_DELETE_ENTITIES_S.NEXTVAL,
239 delete_group_id,
240 1,
241 MCSI.INVENTORY_ITEM_ID,
242 MCSI.ORGANIZATION_ID,
243 '',
244 MCSI.DESCRIPTION,
245 MIF.ITEM_NUMBER,
246 del_stat_type,
247 2,
248 commit_flag,
249 '',
250 SYSDATE,
251 userid,
252 SYSDATE,
253 userid,
254 last_login_id
255 FROM
256 MTL_CATALOG_SEARCH_ITEMS MCSI,
257 MTL_SYSTEM_ITEMS MSI,
258 MTL_ITEM_FLEXFIELDS MIF
259 WHERE MSI.INVENTORY_ITEM_ID = MCSI.INVENTORY_ITEM_ID
260 AND MSI.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
261 AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
262 AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
263 AND MCSI.ORGANIZATION_ID = org_id
264 AND MSI.ENG_ITEM_FLAG = DECODE(delete_type, 1, item_eng_flag,
265 7, item_eng_flag, MSI.ENG_ITEM_FLAG)
266 AND MCSI.GROUP_HANDLE_ID = catalog_search_id
267 AND MCSI.INVENTORY_ITEM_ID NOT IN(
268 SELECT DISTINCT INVENTORY_ITEM_ID
269 FROM BOM_DELETE_ENTITIES
270 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
271 AND ORGANIZATION_ID = org_id);
272
273 DELETE MTL_CATALOG_SEARCH_ITEMS
274 WHERE GROUP_HANDLE_ID = catalog_search_id;
275
276 -- CASE 2 BOM
277 -- CASE 4 COMPONENT
278
279 ELSIF delete_type IN (2, 4) THEN
280 INSERT INTO BOM_DELETE_ENTITIES(
281 DELETE_ENTITY_SEQUENCE_ID,
282 DELETE_GROUP_SEQUENCE_ID,
283 DELETE_ENTITY_TYPE,
284 INVENTORY_ITEM_ID,
285 ORGANIZATION_ID,
286 ALTERNATE_DESIGNATOR,
287 ITEM_DESCRIPTION,
288 ITEM_CONCAT_SEGMENTS,
289 DELETE_STATUS_TYPE,
290 PRIOR_PROCESS_FLAG,
291 PRIOR_COMMIT_FLAG,
292 BILL_SEQUENCE_ID,
293 LAST_UPDATE_DATE,
294 LAST_UPDATED_BY,
295 CREATION_DATE,
296 CREATED_BY,
297 LAST_UPDATE_LOGIN)
298 SELECT
299 BOM_DELETE_ENTITIES_S.NEXTVAL,
300 delete_group_id,
301 2,
302 BOM.ASSEMBLY_ITEM_ID,
303 BOM.ORGANIZATION_ID,
304 BOM.ALTERNATE_BOM_DESIGNATOR,
305 MCSI.DESCRIPTION,
306 MIF.ITEM_NUMBER,
307 del_stat_type,
308 2,
309 commit_flag,
310 BOM.BILL_SEQUENCE_ID,
311 SYSDATE,
312 userid,
313 SYSDATE,
314 userid,
315 last_login_id
316 FROM
317 MTL_CATALOG_SEARCH_ITEMS MCSI,
318 BOM_BILL_OF_MATERIALS BOM,
319 MTL_ITEM_FLEXFIELDS MIF
320 WHERE MCSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
321 AND MCSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
322 AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
323 AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
324 AND MCSI.ORGANIZATION_ID = org_id
325 AND BOM.ASSEMBLY_TYPE = eng_flag
326 AND MCSI.GROUP_HANDLE_ID = catalog_search_id
327 AND BOM.BILL_SEQUENCE_ID NOT IN(
328 SELECT NVL(BILL_SEQUENCE_ID, 0)
329 FROM BOM_DELETE_ENTITIES
330 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);
331
332 DELETE MTL_CATALOG_SEARCH_ITEMS
333 WHERE GROUP_HANDLE_ID = catalog_search_id;
334
335 -- CASE 3 ROUTING
336 -- CASE 5 OPERATION
337
338 ELSIF delete_type IN (3, 5) THEN
339 INSERT INTO BOM_DELETE_ENTITIES(
340 DELETE_ENTITY_SEQUENCE_ID,
341 DELETE_GROUP_SEQUENCE_ID,
342 DELETE_ENTITY_TYPE,
343 INVENTORY_ITEM_ID,
344 ORGANIZATION_ID,
345 ALTERNATE_DESIGNATOR,
346 ITEM_DESCRIPTION,
347 ITEM_CONCAT_SEGMENTS,
348 DELETE_STATUS_TYPE,
349 PRIOR_PROCESS_FLAG,
350 PRIOR_COMMIT_FLAG,
351 ROUTING_SEQUENCE_ID,
352 LAST_UPDATE_DATE,
353 LAST_UPDATED_BY,
354 CREATION_DATE,
355 CREATED_BY,
356 LAST_UPDATE_LOGIN)
357 SELECT
358 BOM_DELETE_ENTITIES_S.NEXTVAL,
359 delete_group_id,
360 3,
361 BOR.ASSEMBLY_ITEM_ID,
362 BOR.ORGANIZATION_ID,
363 BOR.ALTERNATE_ROUTING_DESIGNATOR,
364 MCSI.DESCRIPTION,
365 MIF.ITEM_NUMBER,
366 del_stat_type,
367 2,
368 commit_flag,
369 BOR.ROUTING_SEQUENCE_ID,
370 SYSDATE,
371 userid,
372 SYSDATE,
373 userid,
374 last_login_id
375 FROM
376 MTL_CATALOG_SEARCH_ITEMS MCSI,
377 BOM_OPERATIONAL_ROUTINGS BOR,
378 MTL_ITEM_FLEXFIELDS MIF
379 WHERE MCSI.ORGANIZATION_ID = org_id
380 AND MCSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
381 AND MCSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
382 AND MIF.ITEM_ID = MCSI.INVENTORY_ITEM_ID
383 AND MIF.ORGANIZATION_ID = MCSI.ORGANIZATION_ID
384 AND BOR.ROUTING_TYPE = eng_flag
385 AND MCSI.GROUP_HANDLE_ID = catalog_search_id
386 AND BOR.ROUTING_SEQUENCE_ID NOT IN(
387 SELECT NVL(ROUTING_SEQUENCE_ID, 0)
388 FROM BOM_DELETE_ENTITIES
389 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
390 AND ORGANIZATION_ID = org_id);
391 DELETE MTL_CATALOG_SEARCH_ITEMS
392 WHERE GROUP_HANDLE_ID = catalog_search_id;
393
394 ELSE
395 var_text := 'catalog search id';
396 RAISE invalid_parameter;
397 END IF; -- END OF CATALOG SEARCH
398
399 -- COMPONENT OR OPERATION WHERE USED SEARCH
400
401 ELSE
402 stmt_num := 5;
403
404 -- COMPONENT WHERE USED
405
406 IF delete_type = 4 THEN
407
408 -- COMPONENT ID: NULL, EXPIRATION DATE: NOT NULL
409
410 IF component_id <= 0 THEN
411 INSERT INTO BOM_DELETE_ENTITIES(
412 DELETE_ENTITY_SEQUENCE_ID,
413 DELETE_GROUP_SEQUENCE_ID,
414 DELETE_ENTITY_TYPE,
415 INVENTORY_ITEM_ID,
416 ORGANIZATION_ID,
417 ALTERNATE_DESIGNATOR,
418 ITEM_DESCRIPTION,
419 ITEM_CONCAT_SEGMENTS,
420 PRIOR_PROCESS_FLAG,
421 PRIOR_COMMIT_FLAG,
422 BILL_SEQUENCE_ID,
423 LAST_UPDATE_DATE,
424 LAST_UPDATED_BY,
425 CREATION_DATE,
426 CREATED_BY,
427 LAST_UPDATE_LOGIN)
428 SELECT
429 BOM_DELETE_ENTITIES_S.NEXTVAL,
430 delete_group_id,
431 2,
432 BOM.ASSEMBLY_ITEM_ID,
433 BOM.ORGANIZATION_ID,
434 BOM.ALTERNATE_BOM_DESIGNATOR,
435 MSI.DESCRIPTION,
436 MIF.ITEM_NUMBER,
437 1,
438 commit_flag,
439 BOM.BILL_SEQUENCE_ID,
440 SYSDATE,
441 userid,
442 SYSDATE,
443 userid,
444 last_login_id
445 FROM
446 BOM_BILL_OF_MATERIALS BOM,
447 MTL_SYSTEM_ITEMS MSI,
448 MTL_ITEM_FLEXFIELDS MIF
449 WHERE MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
450 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
451 AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
452 AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
453 AND BOM.BILL_SEQUENCE_ID IN(
454 SELECT DISTINCT BOM.BILL_SEQUENCE_ID
455 FROM
456 BOM_BILL_OF_MATERIALS BOM,
457 BOM_INVENTORY_COMPONENTS BIC
458 WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
459 AND BOM.ORGANIZATION_ID = org_id
460 AND BOM.ASSEMBLY_TYPE = eng_flag
461 AND expiration_date >= BIC.DISABLE_DATE
462 AND BOM.BILL_SEQUENCE_ID NOT IN(
463 SELECT NVL(BILL_SEQUENCE_ID, 0)
464 FROM BOM_DELETE_ENTITIES
465 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));
466
470 OPERATION_SEQ_NUM,
467 INSERT INTO BOM_DELETE_SUB_ENTITIES(
468 DELETE_ENTITY_SEQUENCE_ID,
469 COMPONENT_SEQUENCE_ID,
471 EFFECTIVITY_DATE,
472 COMPONENT_ITEM_ID,
473 COMPONENT_CONCAT_SEGMENTS,
474 ITEM_NUM,
475 DISABLE_DATE,
476 DESCRIPTION,
477 DELETE_STATUS_TYPE,
478 LAST_UPDATE_DATE,
479 LAST_UPDATED_BY,
480 CREATION_DATE,
481 CREATED_BY,
482 LAST_UPDATE_LOGIN)
483 SELECT
484 BDE.DELETE_ENTITY_SEQUENCE_ID,
485 BIC.COMPONENT_SEQUENCE_ID,
486 BIC.OPERATION_SEQ_NUM,
487 BIC.EFFECTIVITY_DATE,
488 BIC.COMPONENT_ITEM_ID,
489 MIF.ITEM_NUMBER,
490 BIC.ITEM_NUM,
491 BIC.DISABLE_DATE,
492 MSI.DESCRIPTION,
493 1,
494 SYSDATE,
495 userid,
496 SYSDATE,
497 userid,
498 last_login_id
499 FROM
500 BOM_INVENTORY_COMPONENTS BIC,
501 BOM_DELETE_ENTITIES BDE,
502 BOM_BILL_OF_MATERIALS BOM,
503 MTL_SYSTEM_ITEMS MSI,
504 MTL_ITEM_FLEXFIELDS MIF
505 WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
506 AND BDE.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
507 AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
508 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
509 AND MIF.ITEM_ID = BIC.COMPONENT_ITEM_ID
510 AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
511 AND BOM.ASSEMBLY_TYPE = eng_flag
512 AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
513 AND expiration_date >= BIC.DISABLE_DATE
514 AND BIC.COMPONENT_SEQUENCE_ID NOT IN(
515 SELECT NVL(COMPONENT_SEQUENCE_ID, 0)
516 FROM
517 BOM_DELETE_SUB_ENTITIES BDSE,
518 BOM_DELETE_ENTITIES BDE
519 WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
520 AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);
521
522 -- COMPONENT ID: NOT NULL, EXPIRATION DATE: NOT NULL OR NULL
523
524 -- THE EXPIRATION_DATE/DISABLE_DATE WHERE CLAUSE BELOW IS OBSCURE
525 -- EXPECTED BEHAVIOR:
526 -- EXPIRATION_DATE DISABLE_DATE BEHAVIOR
527 -- --------------- ------------ --------
528 -- NOT NULL NOT NULL RETAIN ROW IF EXPIRATION_DATE
529 -- >= BIC.DISABLE_DATE
530 -- NOT NULL NULL DROP ROW
531 -- NULL NOT NULL RETAIN ROW
532 -- NULL NULL RETAIN ROW
533
534 ELSE
535 INSERT INTO BOM_DELETE_ENTITIES(
536 DELETE_ENTITY_SEQUENCE_ID,
537 DELETE_GROUP_SEQUENCE_ID,
538 DELETE_ENTITY_TYPE,
539 INVENTORY_ITEM_ID,
540 ORGANIZATION_ID,
541 ALTERNATE_DESIGNATOR,
542 ITEM_DESCRIPTION,
543 ITEM_CONCAT_SEGMENTS,
544 PRIOR_PROCESS_FLAG,
545 PRIOR_COMMIT_FLAG,
546 BILL_SEQUENCE_ID,
547 LAST_UPDATE_DATE,
548 LAST_UPDATED_BY,
549 CREATION_DATE,
550 CREATED_BY,
551 LAST_UPDATE_LOGIN)
552 SELECT
553 BOM_DELETE_ENTITIES_S.NEXTVAL,
554 delete_group_id,
555 2,
556 BOM.ASSEMBLY_ITEM_ID,
557 BOM.ORGANIZATION_ID,
558 BOM.ALTERNATE_BOM_DESIGNATOR,
559 MSI.DESCRIPTION,
560 MIF.ITEM_NUMBER,
561 1,
562 commit_flag,
563 BOM.BILL_SEQUENCE_ID,
564 SYSDATE,
565 userid,
566 SYSDATE,
567 userid,
568 last_login_id
569 FROM
570 BOM_BILL_OF_MATERIALS BOM,
571 MTL_SYSTEM_ITEMS MSI,
572 MTL_ITEM_FLEXFIELDS MIF
573 WHERE MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
574 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
575 AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
576 AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
577 AND BOM.BILL_SEQUENCE_ID IN(
578 SELECT DISTINCT BOM.BILL_SEQUENCE_ID
579 FROM
580 BOM_BILL_OF_MATERIALS BOM,
581 BOM_INVENTORY_COMPONENTS BIC
582 WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
583 AND BOM.ORGANIZATION_ID = org_id
584 AND BOM.ASSEMBLY_TYPE = eng_flag
585 AND BIC.COMPONENT_ITEM_ID = component_id
586 AND NVL(expiration_date, NVL(BIC.DISABLE_DATE, TRUNC(SYSDATE))) >=
587 NVL(BIC.DISABLE_DATE, NVL(expiration_date+1, TRUNC(SYSDATE)))
588 AND BOM.BILL_SEQUENCE_ID NOT IN(
589 SELECT NVL(BILL_SEQUENCE_ID, 0)
590 FROM BOM_DELETE_ENTITIES
591 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));
592
593 -- THE EXPIRATION_DATE/DISABLE_DATE WHERE CLAUSE BELOW IS OBSCURE
594 -- EXPECTED BEHAVIOR:
595 -- EXPIRATION_DATE DISABLE_DATE BEHAVIOR
596 -- --------------- ------------ --------
597 -- NOT NULL NOT NULL RETAIN ROW IF EXPIRATION_DATE
598 -- >= BIC.DISABLE_DATE
599 -- NOT NULL NULL DROP ROW
600 -- NULL NOT NULL RETAIN ROW
601 -- NULL NULL RETAIN ROW
602
606 OPERATION_SEQ_NUM,
603 INSERT INTO BOM_DELETE_SUB_ENTITIES(
604 DELETE_ENTITY_SEQUENCE_ID,
605 COMPONENT_SEQUENCE_ID,
607 EFFECTIVITY_DATE,
608 COMPONENT_ITEM_ID,
609 COMPONENT_CONCAT_SEGMENTS,
610 ITEM_NUM,
611 DISABLE_DATE,
612 DESCRIPTION,
613 DELETE_STATUS_TYPE,
614 LAST_UPDATE_DATE,
615 LAST_UPDATED_BY,
616 CREATION_DATE,
617 CREATED_BY,
618 LAST_UPDATE_LOGIN)
619 SELECT
620 BDE.DELETE_ENTITY_SEQUENCE_ID,
621 BIC.COMPONENT_SEQUENCE_ID,
622 BIC.OPERATION_SEQ_NUM,
623 BIC.EFFECTIVITY_DATE,
624 BIC.COMPONENT_ITEM_ID,
625 MIF.ITEM_NUMBER,
626 BIC.ITEM_NUM,
627 BIC.DISABLE_DATE,
628 MSI.DESCRIPTION,
629 1,
630 SYSDATE,
631 userid,
632 SYSDATE,
633 userid,
634 last_login_id
635 FROM
636 BOM_INVENTORY_COMPONENTS BIC,
637 BOM_DELETE_ENTITIES BDE,
638 BOM_BILL_OF_MATERIALS BOM,
639 MTL_SYSTEM_ITEMS MSI,
640 MTL_ITEM_FLEXFIELDS MIF
641 WHERE BOM.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
642 AND BDE.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
643 AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
644 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
645 AND MIF.ITEM_ID = BIC.COMPONENT_ITEM_ID
646 AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
647 AND BOM.ASSEMBLY_TYPE = eng_flag
648 AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
649 AND BIC.COMPONENT_ITEM_ID = component_id
650 AND NVL(expiration_date, NVL(BIC.DISABLE_DATE,TRUNC(SYSDATE))) >=
651 NVL(BIC.DISABLE_DATE, NVL(expiration_date + 1, TRUNC(SYSDATE)))
652 AND BIC.COMPONENT_SEQUENCE_ID NOT IN(
653 SELECT NVL(COMPONENT_SEQUENCE_ID, 0)
654 FROM
655 BOM_DELETE_SUB_ENTITIES BDSE,
656 BOM_DELETE_ENTITIES BDE
657 WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
658 AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);
659 END IF; -- END OF COMPONENT WHERE USED SEARCH
660 RAISE finale;
661
662 -- OPERATION WHERE USED
663
664 ELSIF delete_type = 5 THEN
665 INSERT INTO BOM_DELETE_ENTITIES(
666 DELETE_ENTITY_SEQUENCE_ID,
667 DELETE_GROUP_SEQUENCE_ID,
668 DELETE_ENTITY_TYPE,
669 INVENTORY_ITEM_ID,
670 ORGANIZATION_ID,
671 ALTERNATE_DESIGNATOR,
672 ITEM_DESCRIPTION,
673 ITEM_CONCAT_SEGMENTS,
674 PRIOR_PROCESS_FLAG,
675 PRIOR_COMMIT_FLAG,
676 ROUTING_SEQUENCE_ID,
677 LAST_UPDATE_DATE,
678 LAST_UPDATED_BY,
679 CREATION_DATE,
680 CREATED_BY,
681 LAST_UPDATE_LOGIN)
682 SELECT
683 BOM_DELETE_ENTITIES_S.NEXTVAL,
684 delete_group_id,
685 3,
686 BOR.ASSEMBLY_ITEM_ID,
687 BOR.ORGANIZATION_ID,
688 BOR.ALTERNATE_ROUTING_DESIGNATOR,
689 MSI.DESCRIPTION,
690 MIF.ITEM_NUMBER,
691 1,
692 commit_flag,
693 BOR.ROUTING_SEQUENCE_ID,
694 SYSDATE,
695 userid,
696 SYSDATE,
697 userid,
698 last_login_id
699 FROM
700 BOM_OPERATIONAL_ROUTINGS BOR,
701 MTL_SYSTEM_ITEMS MSI,
702 MTL_ITEM_FLEXFIELDS MIF
703 WHERE BOR.ORGANIZATION_ID = org_id
704 AND MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
705 AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
706 AND MIF.ITEM_ID = BOR.ASSEMBLY_ITEM_ID
707 AND MIF.ORGANIZATION_ID = BOR.ORGANIZATION_ID
708 AND BOR.ROUTING_TYPE = eng_flag
709 AND BOR.ROUTING_SEQUENCE_ID IN(
710 SELECT DISTINCT BOS.ROUTING_SEQUENCE_ID
711 FROM
712 BOM_OPERATIONAL_ROUTINGS BOR,
713 BOM_OPERATION_SEQUENCES BOS
714 WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
715 AND BOS.DISABLE_DATE <= expiration_date
716 AND BOS.ROUTING_SEQUENCE_ID NOT IN(
717 SELECT NVL(ROUTING_SEQUENCE_ID, 0)
718 FROM BOM_DELETE_ENTITIES
719 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id));
720
721 INSERT INTO BOM_DELETE_SUB_ENTITIES(
722 DELETE_ENTITY_SEQUENCE_ID,
723 OPERATION_SEQUENCE_ID,
724 OPERATION_SEQ_NUM,
725 EFFECTIVITY_DATE,
726 DISABLE_DATE,
727 DESCRIPTION,
728 OPERATION_DEPARTMENT_CODE,
729 DELETE_STATUS_TYPE,
730 LAST_UPDATE_DATE,
731 LAST_UPDATED_BY,
732 CREATION_DATE,
733 CREATED_BY,
734 LAST_UPDATE_LOGIN)
735 SELECT
736 BDE.DELETE_ENTITY_SEQUENCE_ID,
737 BOS.OPERATION_SEQUENCE_ID,
738 BOS.OPERATION_SEQ_NUM,
739 BOS.EFFECTIVITY_DATE,
743 1,
740 BOS.DISABLE_DATE,
741 BOS.OPERATION_DESCRIPTION,
742 BD.DEPARTMENT_CODE,
744 SYSDATE,
745 userid,
746 SYSDATE,
747 userid,
748 last_login_id
749 FROM
750 BOM_OPERATION_SEQUENCES BOS,
751 BOM_DELETE_ENTITIES BDE,
752 BOM_OPERATIONAL_ROUTINGS BOR,
753 BOM_DEPARTMENTS BD
754 WHERE BOR.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
755 AND BDE.ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
756 AND BOR.ROUTING_TYPE = eng_flag
757 AND BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
758 AND BOS.DEPARTMENT_ID = BD.DEPARTMENT_ID
759 AND BOS.DISABLE_DATE <= expiration_date
760 AND BOS.OPERATION_SEQUENCE_ID NOT IN(
761 SELECT NVL(OPERATION_SEQUENCE_ID, 0)
762 FROM
763 BOM_DELETE_SUB_ENTITIES BDSE,
764 BOM_DELETE_ENTITIES BDE
765 WHERE BDE.DELETE_GROUP_SEQUENCE_ID = delete_group_id
766 AND BDE.DELETE_ENTITY_SEQUENCE_ID = BDSE.DELETE_ENTITY_SEQUENCE_ID);
767 RAISE finale;
768 ELSE
769 var_text := 'delete type';
770 RAISE invalid_parameter;
771 END IF; -- END OF OPERATION WHERE USED SEARCH
772 END IF; -- END OF COMPONENT OR OPERATION WHERE USED SEARCH
773 END IF; -- END OF process_type = 1
774
775 -- process_type = 2 CALLED ON COMMIT STARTS HERE
776 -- SOME process_type = 1 FALL THROUGH TO THIS SECTION TOO
777 -- - ITEM CATALOG SEARCHES
778
779 -- DETERMINE IF THE ORG IS A MASTER ORG. IF IT IS, THEN EXPLODE
780 -- TO INSERT THIS ITEM FOR CHILD ORGS.
781 /*
782 Bug 1457363
783 Because of the New Functionality to delete Items from single org,
784 Organization Hierarchy and all Organizations, The following code is
785 not required as we need to give an error whenever item is being deleted
786 from master org when it still exists in child Organizations ,
787 instead of deleting items from all the child Organizations also
788 Commenting the Following Code
789 */
790
791 /*
792 stmt_num := 6;
793 IF delete_type IN (1, 7) THEN
794 DECLARE
795 CURSOR GetMasterOrg IS
796 SELECT DECODE(ORGANIZATION_ID, MASTER_ORGANIZATION_ID, 'Y', 'N')
797 master_org_flag
798 FROM MTL_PARAMETERS
799 WHERE ORGANIZATION_ID = org_id;
800 BEGIN
801 FOR GetMasterOrgREC IN GetMasterOrg LOOP
802 IF GetMasterOrgREC.master_org_flag = 'N' THEN
803 IF delete_type = 1 THEN
804 UPDATE_UNPROCESSED_ROWS(delete_group_id);
805 RAISE finale;
806 END IF;
807 ELSE -- MASTER ORG
808 DECLARE
809 CURSOR GetDelEntities IS
810 SELECT DELETE_ENTITY_SEQUENCE_ID, INVENTORY_ITEM_ID
811 FROM BOM_DELETE_ENTITIES
812 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
813 AND PRIOR_PROCESS_FLAG = 2;
814 BEGIN
815
816 -- INSERT ROW IN BOM_DELETE_ENTITIES FOR THE ITEM IN ALL CHILD ORGS
817 FOR GetDelEntitiesREC IN GetDelEntities LOOP
818 INSERT INTO BOM_DELETE_ENTITIES(
819 DELETE_ENTITY_SEQUENCE_ID,
820 DELETE_GROUP_SEQUENCE_ID,
821 DELETE_ENTITY_TYPE,
822 INVENTORY_ITEM_ID,
823 ORGANIZATION_ID,
824 ITEM_DESCRIPTION,
825 ITEM_CONCAT_SEGMENTS,
826 DELETE_STATUS_TYPE,
827 PRIOR_PROCESS_FLAG,
828 PRIOR_COMMIT_FLAG,
829 LAST_UPDATE_DATE,
830 LAST_UPDATED_BY,
831 CREATION_DATE,
832 CREATED_BY)
833 SELECT
834 BOM_DELETE_ENTITIES_S.NEXTVAL,
835 delete_group_id,
836 1,
837 MSI.INVENTORY_ITEM_ID,
838 MSI.ORGANIZATION_ID,
839 MSI.DESCRIPTION,
840 MIF.ITEM_NUMBER,
841 1,
842 process_flag,
843 commit_flag,
844 SYSDATE,
845 userid,
846 SYSDATE,
847 userid
848 FROM
849 MTL_SYSTEM_ITEMS MSI,
850 MTL_ITEM_FLEXFIELDS MIF
851 WHERE MSI.INVENTORY_ITEM_ID = GetDelEntitiesREC.INVENTORY_ITEM_ID
852 AND MSI.ENG_ITEM_FLAG = item_eng_flag
853 AND MIF.ITEM_ID = MSI.INVENTORY_ITEM_ID
854 AND MIF.ORGANIZATION_ID = MSI.ORGANIZATION_ID
855 AND MSI.ORGANIZATION_ID IN(
856 SELECT ORGANIZATION_ID
857 FROM MTL_PARAMETERS
858 WHERE MASTER_ORGANIZATION_ID = org_id
859 AND ORGANIZATION_ID <> MASTER_ORGANIZATION_ID)
860 AND MSI.ORGANIZATION_ID NOT IN(
861 SELECT DISTINCT ORGANIZATION_ID
862 FROM BOM_DELETE_ENTITIES
863 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
864 AND INVENTORY_ITEM_ID = GetDelEntitiesREC.INVENTORY_ITEM_ID);
865 END LOOP; -- FOR
866 IF delete_type = 1 THEN
867 UPDATE_UNPROCESSED_ROWS(delete_group_id);
868 RAISE finale;
869 END IF;
870 END;
871 END IF;
872 END LOOP; -- FOR
876 IF delete_type IN (6, 7) THEN
873 END;
874 END IF;
875 */
877 DECLARE
878 CURSOR BR_GetDelEntities IS
879 SELECT DELETE_ENTITY_SEQUENCE_ID, INVENTORY_ITEM_ID, ORGANIZATION_ID
880 FROM BOM_DELETE_ENTITIES
881 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id
882 AND PRIOR_PROCESS_FLAG = 2;
883 BEGIN
884
885 -- INSERT ROW IN BOM_DELETE_ENTITIES FOR ALL BOMS AND ALTERNATES
886
887 FOR BR_GetDelEntitiesREC IN BR_GetDelEntities LOOP
888 INSERT INTO BOM_DELETE_ENTITIES(
889 DELETE_ENTITY_SEQUENCE_ID,
890 DELETE_GROUP_SEQUENCE_ID,
891 DELETE_ENTITY_TYPE,
892 INVENTORY_ITEM_ID,
893 ORGANIZATION_ID,
894 ALTERNATE_DESIGNATOR,
895 ITEM_DESCRIPTION,
896 ITEM_CONCAT_SEGMENTS,
897 DELETE_STATUS_TYPE,
898 PRIOR_PROCESS_FLAG,
899 PRIOR_COMMIT_FLAG,
900 BILL_SEQUENCE_ID,
901 LAST_UPDATE_DATE,
902 LAST_UPDATED_BY,
903 CREATION_DATE,
904 CREATED_BY)
905 SELECT
906 BOM_DELETE_ENTITIES_S.NEXTVAL,
907 delete_group_id,
908 2,
909 BOM.ASSEMBLY_ITEM_ID,
910 BOM.ORGANIZATION_ID,
911 BOM.ALTERNATE_BOM_DESIGNATOR,
912 MSI.DESCRIPTION,
913 MIF.ITEM_NUMBER,
914 1,
915 1,
916 commit_flag,
917 BOM.BILL_SEQUENCE_ID,
918 SYSDATE,
919 userid,
920 SYSDATE,
921 userid
922 FROM
923 BOM_BILL_OF_MATERIALS BOM,
924 MTL_SYSTEM_ITEMS MSI,
925 MTL_ITEM_FLEXFIELDS MIF
926 WHERE ASSEMBLY_ITEM_ID = BR_GetDelEntitiesREC.INVENTORY_ITEM_ID
927 AND BOM.ORGANIZATION_ID = BR_GetDelEntitiesREC.ORGANIZATION_ID
928 AND MSI.INVENTORY_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
929 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
930 AND MIF.ITEM_ID = BOM.ASSEMBLY_ITEM_ID
931 AND MIF.ORGANIZATION_ID = BOM.ORGANIZATION_ID
932 AND BOM.ASSEMBLY_TYPE = eng_flag
933 AND BOM.BILL_SEQUENCE_ID NOT IN(
934 SELECT NVL(BILL_SEQUENCE_ID, 0)
935 FROM BOM_DELETE_ENTITIES
936 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);
937
938 -- INSERT ROW IN BOM_DELETE_ENTITIES FOR ALL ROUTINGS AND ALTERNATES
939
940 INSERT INTO BOM_DELETE_ENTITIES(
941 DELETE_ENTITY_SEQUENCE_ID,
942 DELETE_GROUP_SEQUENCE_ID,
943 DELETE_ENTITY_TYPE,
944 INVENTORY_ITEM_ID,
945 ORGANIZATION_ID,
946 ALTERNATE_DESIGNATOR,
947 ITEM_DESCRIPTION,
948 ITEM_CONCAT_SEGMENTS,
949 DELETE_STATUS_TYPE,
950 PRIOR_PROCESS_FLAG,
951 PRIOR_COMMIT_FLAG,
952 ROUTING_SEQUENCE_ID,
953 LAST_UPDATE_DATE,
954 LAST_UPDATED_BY,
955 CREATION_DATE,
956 CREATED_BY)
957 SELECT
958 BOM_DELETE_ENTITIES_S.NEXTVAL,
959 delete_group_id,
960 3,
961 BOR.ASSEMBLY_ITEM_ID,
962 BOR.ORGANIZATION_ID,
963 BOR.ALTERNATE_ROUTING_DESIGNATOR,
964 MSI.DESCRIPTION,
965 MIF.ITEM_NUMBER,
966 1,
967 1,
968 commit_flag,
969 BOR.ROUTING_SEQUENCE_ID,
970 SYSDATE,
971 userid,
972 SYSDATE,
973 userid
974 FROM
975 BOM_OPERATIONAL_ROUTINGS BOR,
976 MTL_SYSTEM_ITEMS MSI,
977 MTL_ITEM_FLEXFIELDS MIF
978 WHERE ASSEMBLY_ITEM_ID = BR_GetDelEntitiesREC.INVENTORY_ITEM_ID
979 AND BOR.ORGANIZATION_ID = BR_GetDelEntitiesREC.ORGANIZATION_ID
980 AND MSI.INVENTORY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
981 AND MSI.ORGANIZATION_ID = BOR.ORGANIZATION_ID
982 AND MIF.ITEM_ID = BOR.ASSEMBLY_ITEM_ID
983 AND MIF.ORGANIZATION_ID = BOR.ORGANIZATION_ID
984 AND BOR.ROUTING_TYPE = eng_flag
985 AND BOR.ROUTING_SEQUENCE_ID NOT IN(
986 SELECT NVL(ROUTING_SEQUENCE_ID, 0)
987 FROM BOM_DELETE_ENTITIES
988 WHERE DELETE_GROUP_SEQUENCE_ID = delete_group_id);
989
990 -- DELETE ROW IN BOM_DELETE_ENTITIES FOR BILL AND ROUTING
991 -- THAT WAS THE ITEM PLACE HOLDER (CURRENT OF CURSOR)
992
993 IF delete_type = 6 THEN
994 DELETE
995 FROM BOM_DELETE_ENTITIES
996 WHERE DELETE_ENTITY_SEQUENCE_ID =
997 BR_GetDelEntitiesREC.DELETE_ENTITY_SEQUENCE_ID;
998 END IF;
999
1000 END LOOP;
1001 -- this is to fix the bug 1205006.
1002 -- Updates the pre-process-flag for row
1003 -- containing item info(ITEM PLACE HOLDER)if delete-type is 7.
1004 IF delete_type = 7 THEN
1005 UPDATE_UNPROCESSED_ROWS(delete_group_id);
1006 RAISE finale;
1007 END IF;
1008 END;
1009 ELSIF delete_type IN (2, 3, 4, 5) THEN
1010 UPDATE_UNPROCESSED_ROWS(delete_group_id);
1011 RAISE finale;
1012 ELSIF delete_type = 1 THEN
1013 null;
1014 ELSE
1015 var_text := 'delete type';
1016 RAISE invalid_parameter;
1017 END IF;
1018
1019 EXCEPTION
1020 WHEN invalid_parameter THEN
1021 FND_MESSAGE.SET_NAME('BOM','BOM_DELETE_ITEMS_PARAM');
1022 FND_MESSAGE.SET_TOKEN('ENTITY', var_text, TRUE);
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024 WHEN finale THEN
1025 null;
1026 WHEN OTHERS THEN
1027 RAISE_ERROR(
1028 func_name => 'P0PULATE_DELETE',
1029 stmt_num => stmt_num);
1030
1031 END POPULATE_DELETE;
1032
1033 END BOM_DELETE_ITEMS;