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