[Home] [Help]
PACKAGE BODY: APPS.BOMPIMPL
Source
1 package body bompimpl as
2 /* $Header: BOMIMPLB.pls 120.1 2005/06/21 01:53:41 appldev ship $ */
3 PROCEDURE imploder_userexit(
4 sequence_id IN NUMBER,
5 eng_mfg_flag IN NUMBER,
6 org_id IN NUMBER,
7 impl_flag IN NUMBER,
8 display_option IN NUMBER,
9 levels_to_implode IN NUMBER,
10 item_id IN NUMBER,
11 impl_date IN VARCHAR2,
12 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
13 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
14
15 a_err_msg VARCHAR2(80);
16 a_err_code NUMBER;
17
18 BEGIN
19
20 INSERT INTO BOM_IMPLOSION_TEMP
21 ( SEQUENCE_ID,
22 LOWEST_ITEM_ID,
23 CURRENT_ITEM_ID,
24 PARENT_ITEM_ID,
25 ALTERNATE_DESIGNATOR,
26 CURRENT_LEVEL,
27 SORT_CODE,
28 CURRENT_ASSEMBLY_TYPE,
29 COMPONENT_SEQUENCE_ID,
30 ORGANIZATION_ID,
31 LAST_UPDATE_DATE,
32 LAST_UPDATED_BY,
33 CREATION_DATE,
34 CREATED_BY)
35 VALUES (sequence_id,
36 item_id,
37 item_id,
38 item_id,
39 NULL,
40 0,
41 -- '0000001',
42 Bom_Common_Definitions.G_Bom_Init_SortCode,
43 NULL,
44 NULL,
45 org_id,
46 sysdate,
47 -1,
48 sysdate,
49 -1);
50
51 bompimpl.implosion(sequence_id, eng_mfg_flag, org_id, impl_flag,
52 display_option, levels_to_implode, impl_date, a_err_msg, a_err_code);
53
54 err_msg := a_err_msg;
55 err_code := a_err_code;
56
57 if (a_err_code <> 0) then
58 ROLLBACK;
59 end if;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 err_msg := substrb(SQLERRM, 1, 80);
64 err_code := SQLCODE;
65 ROLLBACK;
66 END imploder_userexit;
67
68 PROCEDURE implosion(
69 sequence_id IN NUMBER,
70 eng_mfg_flag IN NUMBER,
71 org_id IN NUMBER,
72 impl_flag IN NUMBER,
73 display_option IN NUMBER,
74 levels_to_implode IN NUMBER,
75 impl_date IN VARCHAR2,
76 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
77 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
78
79 implosion_date VARCHAR2(25);
80 error_msg VARCHAR(80);
81 error_code NUMBER;
82
83 BEGIN
84 -- implosion_date := substr(impl_date, 1, 16);
85 implosion_date := impl_date;
86
87 if levels_to_implode = 1 then
88 sl_imploder(sequence_id, eng_mfg_flag, org_id, impl_flag,
89 display_option, implosion_date, error_msg, error_code);
90 else
91 ml_imploder(sequence_id, eng_mfg_flag, org_id, impl_flag,
92 levels_to_implode, implosion_date, error_msg, error_code);
93 end if;
94
95 err_msg := error_msg;
96 err_code := error_code;
97
98 if (error_code <> 0) then
99 ROLLBACK;
100 end if;
101
102 EXCEPTION
103 WHEN OTHERS THEN
104 err_msg := error_msg;
105 err_code := error_code;
106 ROLLBACK;
107 END implosion;
108
109 PROCEDURE sl_imploder (
110 sequence_id IN NUMBER,
111 eng_mfg_flag IN NUMBER,
112 org_id IN NUMBER,
113 impl_flag IN NUMBER,
114 display_option IN NUMBER,
115 impl_date IN VARCHAR2,
116 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
117 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
118
119 total_rows NUMBER;
120 cat_sort VARCHAR2(7);
121
122 CURSOR imploder (c_sequence_id NUMBER,
123 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
124 c_display_option NUMBER,
125 c_implosion_date VARCHAR2, c_implemented_only_option NUMBER
126 ) IS
127 SELECT /*+ ordered first_rows */
128 BITT.LOWEST_ITEM_ID LID,
129 BITT.PARENT_ITEM_ID PID,
130 BBM.ASSEMBLY_ITEM_ID AID,
131 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
132 BITT.SORT_CODE SC,
133 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
134 BBM.ASSEMBLY_TYPE CAT,
135 BIC.COMPONENT_SEQUENCE_ID CSI,
136 BIC.OPERATION_SEQ_NUM OSN,
137 BIC.EFFECTIVITY_DATE ED,
138 BIC.DISABLE_DATE DD,
139 BIC.BASIS_TYPE BT,
140 BIC.COMPONENT_QUANTITY CQ,
141 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
142 BIC.CHANGE_NOTICE CN,
143 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
144 BBM.ORGANIZATION_ID OI
145 FROM
146 BOM_IMPLOSION_TEMP BITT,
147 BOM_INVENTORY_COMPONENTS BIC,
148 BOM_BILL_OF_MATERIALS BBM
149 where bitt.current_level = 0
150 and bitt.organization_id = c_org_id
151 and bitt.sequence_id = c_sequence_id
152 and bitt.parent_item_id = bic.component_item_id
153 and bic.bill_sequence_id = bbm.common_bill_sequence_id
154 and bbm.organization_id = c_org_id
155 and NVL(bic.ECO_FOR_PRODUCTION,2) = 2
156 and (
157 ( c_eng_mfg_flag = 1
158 and bbm.assembly_type = 1
159 ) /* get only Mfg boms */
160 or
161 (c_eng_mfg_flag = 2
162 ) /*both Mfg-Eng BOMs in ENG mode*/
163 ) /* end of entire and predicate */
164 and (
165 (nvl(bbm.alternate_bom_designator,'none') = /*Pickup match par*/
166 nvl(bitt.lowest_alternate_designator,'none')
167 )
168 or /* Pickup par with spec alt only, if start alt is null,*/
169 ( bitt.lowest_alternate_designator is null /*and bill with spec*/
170 and bbm.alternate_bom_designator is not null
171 /* alt doesnt exist */
172 and not exists (select NULL /*for current item */
173 from bom_bill_of_materials bbm2
174 where bbm2.organization_id = c_org_id
175 and bbm2.assembly_item_id = bitt.parent_item_id
176 and bbm2.alternate_bom_designator =
177 bbm.alternate_bom_designator
178 and (
179 (bitt.current_assembly_type = 1
180 and bbm2.assembly_type = 1)
181 or
182 (bitt.current_assembly_type = 2)
183 )
184 ) /* end of subquery */
185 ) /* end of parent with specific alt */
186 or /* Pickup prim par only if start alt is not null and bill 4*/
187 ( bitt.lowest_alternate_designator is not null
188 /* same par doesnt */
189 and bbm.alternate_bom_designator is null
190 /* exist with this alt */
191 and not exists (select NULL
192 from bom_bill_of_materials bbm2
193 where bbm2.organization_id = c_org_id
194 and bbm2.assembly_item_id = bbm.assembly_item_id
195 and bbm2.alternate_bom_designator =
196 bitt.lowest_alternate_designator
197 and (
198 (bitt.current_assembly_type = 1
199 and bbm2.assembly_type = 1)
200 or
201 (bitt.current_assembly_type = 2)
202 )
203 ) /* end of subquery */
204 ) /* end of parent with null alt */
205 )/* end of all alternate logic */
206 and ( /* start of all display options */
207 ( c_display_option = 2
208 and bic.effectivity_date
209 <= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
210 and ( bic.disable_date is null
211 or bic.disable_date >
212 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
213 )
214 ) /* end of CURRENT */
215 or
216 c_display_option = 1
217 or
218 ( c_display_option = 3
219 and ( bic.disable_date is null
220 or bic.disable_date >
221 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
222 )
223 ) /* end of CURRENT_AND_FUTURE */
224 ) /* end of all display options */
225 and ( /* start of implemented yes/no logic */
226 ( c_implemented_only_option = 1
227 and bic.implementation_date is not null
228 )
229 or
230 ( c_implemented_only_option = 2
231 and ( /* start of all display */
232 ( c_display_option = 2
233 and
234 bic.effectivity_date =
235 (select max(effectivity_date)
236 from bom_inventory_components bic2
237 where bic2.bill_sequence_id = bic.bill_sequence_id
238 and bic2.component_item_id = bic.component_item_id
239 and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
240 and decode(bic.implementation_date, NULL,
241 bic.old_component_sequence_id,
242 bic.component_sequence_id) =
243 decode(bic2.implementation_date, NULL,
244 bic2.old_component_sequence_id,
245 bic2.component_sequence_id)
246 and bic2.effectivity_date <=
247 to_date(c_implosion_date,
248 'YYYY/MM/DD HH24:MI:SS')
249 --* AND Clause added for Bug 3085543
250 and NOT EXISTS (SELECT null
251 FROM bom_inventory_components bic3
252 WHERE bic3.bill_sequence_id =
253 bic.bill_sequence_id
254 AND bic3.old_component_sequence_id =
255 bic.component_sequence_id
256 AND NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
257 AND bic3.acd_type in (2,3)
258 AND bic3.disable_date <=
259 to_date(c_implosion_date,
260 'YYYY/MM/DD HH24:MI:SS'))
261 --* End of Bug 3085543
262 and ( bic2.disable_date >
263 to_date(c_implosion_date,
264 'YYYY/MM/DD HH24:MI:SS')
265 or bic2.disable_date is null )
266 ) /* end of subquery */
267 ) /* end of CURRENT */
268 or
269 ( c_display_option = 3
270 and bic.effectivity_date =
271 (select max(effectivity_date)
272 from bom_inventory_components bic2
273 where bic2.bill_sequence_id = bic.bill_sequence_id
274 and bic2.component_item_id = bic.component_item_id
275 and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
276 and nvl(bic2.old_component_sequence_id,
277 bic2.component_sequence_id) =
278 nvl(bic.old_component_sequence_id,
279 bic.component_sequence_id)
280 and bic2.effectivity_date <=
281 to_date(c_implosion_date,
282 'YYYY/MM/DD HH24:MI:SS')
283 --* AND Clause added for Bug - 3155946
284 AND NOT EXISTS ( SELECT Null
285 FROM Bom_Inventory_Components bic4
286 WHERE bic4.bill_sequence_id =
287 bic.bill_sequence_id
288 AND bic4.old_component_sequence_id =
289 bic.component_sequence_id
290 AND Nvl(bic4.eco_for_production,2) = 2
291 AND bic4.acd_type in (2,3)
292 AND bic4.disable_date <=
293 to_date(c_implosion_date,
294 'YYYY/MM/DD HH24:MI:SS') )
295 --* End of Bug - 3155946
296 and ( bic2.disable_date >
297 to_date(c_implosion_date,
298 'YYYY/MM/DD HH24:MI:SS')
299 or bic2.disable_date is null )
300 ) /* end of subquery */
301 or
302 bic.effectivity_date > to_date(c_implosion_date,
303 'YYYY/MM/DD HH24:MI:SS')
304 ) /* end of current and future */
305 or
306 ( c_display_option = 1)
307 ) /* end of all display */
308 ) /* end of impl = no */
309 ) /* end of impl = yes-no */
310 order by bitt.parent_item_id,
311 bbm.assembly_item_id, bic.operation_seq_num;
312 Cursor Check_Configured_Parent(
313 P_Parent_Item in number,
314 P_Comp_Item in number) is
315 Select 1 dummy
316 From mtl_system_items msi1,
317 mtl_system_items msi2
318 Where msi1.inventory_item_id = P_Parent_Item
319 And msi1.organization_id = org_id
320 And msi2.inventory_item_id = P_Comp_Item
321 And msi2.organization_id = org_id
322 And msi1.bom_item_type = 4 -- Standard
323 And msi1.replenish_to_order_flag = 'Y'
324 And msi1.base_item_id is not null -- configured item
325 And msi2.bom_item_type in (1, 2); -- model or option class
326 Cursor Check_Disabled_Parent(
327 P_Parent_Item in number) is
328 Select 1 dummy
329 From mtl_system_items msi
330 Where msi.inventory_item_id = P_Parent_Item
331 And msi.organization_id = org_id
332 And msi.bom_enabled_flag = 'N';
333 Prune_Tree exception;
334
335 BEGIN
336
337 total_rows := 0;
338
339 FOR impl_row in imploder(sequence_id,
340 eng_mfg_flag, org_id, display_option,
341 impl_date, impl_flag) LOOP
342 Begin
343 IF imploder%NOTFOUND THEN
344 goto done_imploding;
345 END IF;
346
347 /*
348 For X_Item_Attributes in Check_Configured_Parent(
349 P_Parent_Item => impl_row.aid,
350 P_Comp_Item => impl_row.pid) loop
351 Raise Prune_Tree;
352 End loop;
353 */
354
355 For X_Item_Attributes in Check_Disabled_Parent(
356 P_Parent_Item => impl_row.aid) loop
357 Raise Prune_Tree;
358 End loop;
359
360 impl_row.LAD := impl_row.ABD;
361
362 total_rows := total_rows + 1;
363
364 -- cat_sort := lpad(total_rows, 7, '0');
365 cat_sort := lpad(total_rows, Bom_Common_Definitions.G_Bom_SortCode_Width, '0');
366
367 impl_row.SC := impl_row.SC || cat_sort;
368
369 INSERT INTO BOM_IMPLOSION_TEMP
370 (LOWEST_ITEM_ID,
371 CURRENT_ITEM_ID,
372 PARENT_ITEM_ID,
373 ALTERNATE_DESIGNATOR,
374 CURRENT_LEVEL,
375 SORT_CODE,
376 LOWEST_ALTERNATE_DESIGNATOR,
377 CURRENT_ASSEMBLY_TYPE,
378 SEQUENCE_ID,
379 COMPONENT_SEQUENCE_ID,
380 ORGANIZATION_ID,
381 OPERATION_SEQ_NUM,
382 EFFECTIVITY_DATE,
383 DISABLE_DATE,
384 BASIS_TYPE,
385 COMPONENT_QUANTITY,
389 LAST_UPDATE_DATE,
386 REVISED_ITEM_SEQUENCE_ID,
387 CHANGE_NOTICE,
388 IMPLEMENTED_FLAG,
390 LAST_UPDATED_BY,
391 CREATION_DATE,
392 CREATED_BY) VALUES (
393 impl_row.LID,
394 impl_row.PID,
395 impl_row.AID,
396 impl_row.ABD,
397 1,
398 impl_row.SC,
399 impl_row.LAD,
400 impl_row.CAT,
401 sequence_id,
402 impl_row.CSI,
403 impl_row.OI,
404 impl_row.OSN,
405 impl_row.ED,
406 impl_row.DD,
407 impl_row.BT,
408 impl_row.CQ,
409 impl_row.RISD,
410 impl_row.CN,
411 impl_row.IMPF,
412 sysdate,
413 -1,
414 sysdate,
415 -1);
416 Exception
417 When Prune_Tree then
418 null;
419 End; -- row
420 end loop; /* cursor fetch loop */
421
422 <<done_imploding>>
423 error_code := 0;
424 /*
425 ** exception handlers
426 */
427 EXCEPTION
428 WHEN OTHERS THEN
429 error_code := SQLCODE;
430 err_msg := substrb(SQLERRM, 1, 80);
431 END sl_imploder;
432
433 PROCEDURE ml_imploder(
434 sequence_id IN NUMBER,
435 eng_mfg_flag IN NUMBER,
436 org_id IN NUMBER,
437 impl_flag IN NUMBER,
438 a_levels_to_implode IN NUMBER,
439 impl_date IN VARCHAR2,
440 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
441 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) AS
442
443 prev_parent_item_id NUMBER;
444 cum_count NUMBER;
445 cur_level NUMBER;
446 total_rows NUMBER;
447 levels_to_implode NUMBER;
448 max_level NUMBER;
449 cat_sort VARCHAR2(7);
450 max_extents EXCEPTION;
451
452 /*
453 ** max extents exceeded exception
454 */
455 PRAGMA EXCEPTION_INIT(max_extents, -1631);
456
457 CURSOR imploder (c_current_level NUMBER, c_sequence_id NUMBER,
458 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
459 c_implosion_date VARCHAR2, c_implemented_only_option NUMBER
460 ) IS
461 SELECT /*+ ordered first_rows */
462 BITT.LOWEST_ITEM_ID LID,
463 BITT.PARENT_ITEM_ID PID,
464 BBM.ASSEMBLY_ITEM_ID AID,
465 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
466 BITT.SORT_CODE SC,
467 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
468 BBM.ASSEMBLY_TYPE CAT,
469 BIC.COMPONENT_SEQUENCE_ID CSI,
470 BIC.OPERATION_SEQ_NUM OSN,
471 BIC.EFFECTIVITY_DATE ED,
472 BIC.DISABLE_DATE DD,
473 BIC.BASIS_TYPE BT,
474 BIC.COMPONENT_QUANTITY CQ,
475 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
476 BIC.CHANGE_NOTICE CN,
477 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
478 BBM.ORGANIZATION_ID OI
479 FROM
480 BOM_IMPLOSION_TEMP BITT,
481 BOM_INVENTORY_COMPONENTS BIC,
482 BOM_BILL_OF_MATERIALS BBM
483 where
484 bitt.current_level = c_current_level
485 and bitt.organization_id = c_org_id
486 and bitt.sequence_id = c_sequence_id
487 and bitt.parent_item_id = bic.component_item_id
488 and bic.bill_sequence_id = bbm.common_bill_sequence_id
489 and bbm.organization_id = c_org_id
490 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
491 and (
492 ( c_current_level = 0
493 and
494 ( (c_eng_mfg_flag = 1
495 and bbm.assembly_type = 1
496 ) /* get only Mfg boms */
497 or
498 (c_eng_mfg_flag = 2
499 ) /*both Mfg-Eng BOMs in ENG mode*/
500 ) /* eng or mfg */
501 ) /* end of current_level = 0 */
502 or
503 ( c_current_level <> 0
504 and
505 ( (bitt.current_assembly_type = 1
506 and bbm.assembly_type = 1
507 and c_eng_mfg_flag = 1
508 )
509 or
510 (c_eng_mfg_flag = 2
511 )
512 ) /* eng or mfg */
513 ) /* end of current level <> 0 */
514 ) /* end of entire and predicate */
515 and ( c_current_level = 0
516 or /* start of all alternate logic */
517 ( nvl(bbm.alternate_bom_designator,'none') =
518 nvl(bitt.lowest_alternate_designator,'none')
519 )
520 or /* Pickup par with spec alt only, if start alt is null,*/
521 ( bitt.lowest_alternate_designator is null
522 and bbm.alternate_bom_designator is not null
523 /* alt doesnt exist */
524 and not exists (select NULL /*for current item */
525 from bom_bill_of_materials bbm2
526 where bbm2.organization_id = c_org_id
527 and bbm2.assembly_item_id = bitt.parent_item_id
528 and bbm2.alternate_bom_designator =
529 bbm.alternate_bom_designator
530 and (
531 (bitt.current_assembly_type = 1
532 and bbm2.assembly_type = 1
536 )
533 and c_eng_mfg_flag = 1)
534 or
535 (c_eng_mfg_flag = 2)
537 ) /* end of subquery */
538 ) /* end of parent with specific alt */
539 or /* Pickup prim par only if starting alt is not
540 null and bill for .. */
541 (bitt.lowest_alternate_designator is not null
542 /* .. same par doesnt */
543 and bbm.alternate_bom_designator is null
544 /* .. exist with this alt */
545 and not exists (select NULL
546 from bom_bill_of_materials bbm2
547 where bbm2.organization_id = c_org_id
548 and bbm2.assembly_item_id = bbm.assembly_item_id
549 and bbm2.alternate_bom_designator =
550 bitt.lowest_alternate_designator
551 and (
552 (bitt.current_assembly_type = 1
553 and bbm2.assembly_type = 1
554 and c_eng_mfg_flag = 1)
555 or
556 (c_eng_mfg_flag = 2)
557 )
558 ) /* end of subquery */
559 ) /* end of parent with null alt */
560 )/* end of all alternate logic */
561 and bic.effectivity_date <= to_date(c_implosion_date,
562 'YYYY/MM/DD HH24:MI:SS')
563 and ( bic.disable_date is null
564 or
565 bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI:SS')
566 )
567 and ( /* start of implemented yes-no logic */
568 ( c_implemented_only_option = 1
569 and bic.implementation_date is not null
570 )
571 or
572 ( c_implemented_only_option = 2
573 and bic.effectivity_date =
574 (select max(effectivity_date)
575 from bom_inventory_components bic2
576 where bic.bill_sequence_id = bic2.bill_sequence_id
577 and bic.component_item_id = bic2.component_item_id
578 and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
579 and decode(bic.implementation_date, NULL,
580 bic.old_component_sequence_id,
581 bic.component_sequence_id) =
582 decode(bic2.implementation_date, NULL,
583 bic2.old_component_sequence_id,
584 bic2.component_sequence_id)
585 and bic2.effectivity_date <= to_date(c_implosion_date,
586 'YYYY/MM/DD HH24:MI:SS')
587 --* AND Clause added for Bug 3085543
588 and NOT EXISTS (SELECT null
589 FROM bom_inventory_components bic3
590 WHERE bic3.bill_sequence_id =
591 bic.bill_sequence_id
592 AND bic3.old_component_sequence_id =
593 bic.component_sequence_id
594 and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
595 AND bic3.acd_type in (2,3)
596 AND bic3.disable_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI:SS'))
597 --* End of Bug 3085543
598 and ( bic2.disable_date is null
599 or
600 ( bic2.disable_date is not null
601 and bic2.disable_date >
602 to_date(c_implosion_date,
603 'YYYY/MM/DD HH24:MI:SS')
604 )
605 )
606 ) /* end of select (max) */
607 ) /* end of impl_only = no */
608 ) /* end of implemented yes-no logic */
609 order by bitt.parent_item_id,
610 bbm.assembly_item_id, bic.operation_seq_num;
611 Cursor Check_Configured_Parent(
612 P_Parent_Item in number,
613 P_Comp_Item in number) is
614 Select 1 dummy
615 From mtl_system_items msi1,
616 mtl_system_items msi2
617 Where msi1.inventory_item_id = P_Parent_Item
618 And msi1.organization_id = org_id
619 And msi2.inventory_item_id = P_Comp_Item
620 And msi2.organization_id = org_id
621 And msi1.bom_item_type = 4 -- Standard
622 And msi1.replenish_to_order_flag = 'Y'
623 And msi1.base_item_id is not null -- configured item
624 And msi2.bom_item_type in (1, 2); -- model or option class
625 Cursor Check_Disabled_Parent(
626 P_Parent_Item in number) is
627 Select 1 dummy
628 From mtl_system_items msi
629 Where msi.inventory_item_id = P_Parent_Item
630 And msi.organization_id = org_id
631 And msi.bom_enabled_flag = 'N';
632 Prune_Tree exception;
633 BEGIN
634
635 SELECT max(MAXIMUM_BOM_LEVEL)
636 INTO max_level
637 FROM BOM_PARAMETERS
638 WHERE ORGANIZATION_ID = org_id;
639
640 IF SQL%NOTFOUND or max_level is null THEN
641 max_level := 60;
642 END IF;
643
644 levels_to_implode := a_levels_to_implode;
645
646 IF (levels_to_implode < 0 OR levels_to_implode > max_level) THEN
647 levels_to_implode := max_level;
648 END IF;
649
650 cur_level := 0; /* initialize level */
651
652 WHILE (cur_level < levels_to_implode) LOOP
653
654 total_rows := 0;
655 cum_count := 0;
656
660
657 FOR impl_row in imploder (cur_level, sequence_id,
658 eng_mfg_flag, org_id, impl_date, impl_flag) LOOP
659 Begin
661 IF imploder%NOTFOUND THEN
662 goto no_more_rows;
663 END IF;
664
665 if (cur_level >=1)
666 then
667 For X_Item_Attributes in Check_Configured_Parent(
668 P_Parent_Item => impl_row.aid,
669 P_Comp_Item => impl_row.pid) loop
670 Raise Prune_Tree;
671 End loop;
672 end if;
673
674 For X_Item_Attributes in Check_Disabled_Parent(
675 P_Parent_Item => impl_row.aid) loop
676 Raise Prune_Tree;
677 End loop;
678
679 IF cur_level = 0 THEN
680 impl_row.LAD := impl_row.ABD;
681 END IF;
682
683 total_rows := total_rows + 1;
684
685 IF (cum_count = 0) THEN
686 prev_parent_item_id := impl_row.PID;
687 END IF;
688
689 IF (prev_parent_item_id <> impl_row.PID) THEN
690 cum_count := 0;
691 prev_parent_item_id := impl_row.PID;
692 END IF;
693
694 cum_count := cum_count + 1;
695
696 -- cat_sort := lpad(cum_count, 7, '0');
697 cat_sort := lpad(cum_count, Bom_Common_Definitions.G_Bom_SortCode_Width, '0');
698
699 impl_row.SC := impl_row.SC || cat_sort;
700
701 INSERT INTO BOM_IMPLOSION_TEMP
702 (LOWEST_ITEM_ID,
703 CURRENT_ITEM_ID,
704 PARENT_ITEM_ID,
705 ALTERNATE_DESIGNATOR,
706 CURRENT_LEVEL,
707 SORT_CODE,
708 LOWEST_ALTERNATE_DESIGNATOR,
709 CURRENT_ASSEMBLY_TYPE,
710 SEQUENCE_ID,
711 COMPONENT_SEQUENCE_ID,
712 ORGANIZATION_ID,
713 REVISED_ITEM_SEQUENCE_ID,
714 CHANGE_NOTICE,
715 OPERATION_SEQ_NUM,
716 EFFECTIVITY_DATE,
717 DISABLE_DATE,
718 BASIS_TYPE,
719 COMPONENT_QUANTITY,
720 IMPLEMENTED_FLAG,
721 LAST_UPDATE_DATE,
722 LAST_UPDATED_BY,
723 CREATION_DATE,
724 CREATED_BY) VALUES (
725 impl_row.LID,
726 impl_row.PID,
727 impl_row.AID,
728 impl_row.ABD,
729 cur_level + 1,
730 impl_row.SC,
731 impl_row.LAD,
732 impl_row.CAT,
733 sequence_id,
734 impl_row.CSI,
735 impl_row.OI,
736 impl_row.RISD,
737 impl_row.CN,
738 impl_row.OSN,
739 impl_row.ED,
740 impl_row.DD,
741 impl_row.BT,
742 impl_row.CQ,
743 impl_row.IMPF,
744 sysdate,
745 -1,
746 sysdate,
747 -1);
748 Exception
749 When Prune_tree then
750 null;
751 End; -- row
752 end loop; /* cursor fetch loop */
753
754 <<no_more_rows>>
755 IF (total_rows <> 0) THEN
756 cur_level := cur_level + 1;
757 ELSE
758 goto done_imploding;
759 END IF;
760
761 END LOOP; /* while levels */
762
763 <<done_imploding>>
764 error_code := 0;
765 /*
766 ** exception handlers
767 */
768 EXCEPTION
769 WHEN max_extents THEN
770 error_code := SQLCODE;
771 err_msg := substrb(SQLERRM, 1, 80);
772 WHEN OTHERS THEN
773 error_code := SQLCODE;
774 err_msg := substrb(SQLERRM, 1, 80);
775 END ml_imploder;
776
777 END bompimpl;