[Home] [Help]
PACKAGE BODY: APPS.BOMPIINQ
Source
1 package body bompiinq as
2 /* $Header: BOMIINQB.pls 120.2.12000000.2 2007/06/28 10:10:29 venugala ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMPIINQ.sql |
10 | DESCRIPTION : This file is a packaged procedure for the imploders.
11 | This package contains 2 different imploders for the
12 | single level and multi level implosion. The package
13 | imploders calls the correct imploder based on the
14 | # of levels to implode.
15 | Parameters: org_id organization_id
16 | sequence_id unique value to identify current implosion
17 | use value from sequence bom_small_impl_temp_s
18 | levels_to_implode
19 | eng_mfg_flag 1 - BOM
20 | 2 - ENG
21 | impl_flag 1 - implemented only
22 | 2 - both impl and unimpl
23 | display_option 1 - All
24 | 2 - Current
25 | 3 - Current and future
26 | item_id item id of asembly to explode
27 | impl_date explosion date dd-mon-rr hh24:mi
28 | err_msg error message out buffer
29 | error_code error code out. returns sql error code
30 | if sql error, 9999 if loop detected.
31 | organization_option
32 | 1 - Current Organization
33 | 2 - Organization Hierarchy
34 | 3 - All Organizations to which access is allowed
35 | organization_hierarchy
36 | Organization Hierarchy Name
37 +==========================================================================*/
38 PROCEDURE imploder_userexit(
39 sequence_id IN NUMBER,
40 eng_mfg_flag IN NUMBER,
41 org_id IN NUMBER,
42 impl_flag IN NUMBER,
43 display_option IN NUMBER,
44 levels_to_implode IN NUMBER,
45 item_id IN NUMBER,
46 impl_date IN VARCHAR2,
47 unit_number_from IN VARCHAR2,
48 unit_number_to IN VARCHAR2,
49 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
50 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
51 organization_option IN NUMBER default 1,
52 organization_hierarchy IN VARCHAR2 default NULL,
53 serial_number_from IN VARCHAR2 default NULL,
54 serial_number_to IN VARCHAR2 default NULL) AS
55
56 a_err_msg VARCHAR2(80);
57 a_err_code NUMBER;
58 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
59 N NUMBER:=0;
60 dummy NUMBER;
61 l_org_name VARCHAR2(60);
62 item_found BOOLEAN:=TRUE;
63 l_master_org_for_current_org NUMBER;
64 l_master_org NUMBER;
65
66 CURSOR c_master_org(c_organization_id NUMBER) IS
67 SELECT MASTER_ORGANIZATION_ID L_MASTER_ORG
68 FROM MTL_PARAMETERS
69 WHERE ORGANIZATION_ID = c_organization_id;
70
71
72 BEGIN
73
74 /* If the parameter :
75 Organization_Option = 1 then
76 Take the current Organization
77 else If Organization_Option = 2 is passed then
78 Call the Inventory API to get the list of Organizations
79 under the current Organization Hierarchy
80 else if Organization Option = 3 is passed then
81 Find the list of all the Organizations to which
82 access is allowed */
83
84 if ( organization_option =2 ) then
85
86 /* SELECT organization_name into l_org_name
87 FROM org_organization_definitions
88 WHERE organization_id = org_id;
89 */
90
91 /* In case of an org hierarchy, make sure that for those orgs that
92 have a master org different from the master org of the current org
93 are not considered.*/
94
95 --get the master org id for the current org
96 OPEN c_master_org(org_id);
97 FETCH c_master_org into l_master_org_for_current_org;
98 CLOSE c_master_org;
99
100 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(organization_hierarchy ,
101 org_id ,t_org_code_list );
102
103 elsif ( organization_option = 3 ) then
104 /* Bug:4929268 Performance Fix */
105 for C1 in ( SELECT orgs.ORGANIZATION_ID
106 FROM ORG_ACCESS_VIEW oav, MTL_SYSTEM_ITEMS_B msi,
107 MTL_PARAMETERS orgs, MTL_PARAMETERS child_org
108 WHERE orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
109 AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
110 AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
111 AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
112 AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
113 AND msi.INVENTORY_ITEM_ID = item_id
114 AND child_org.ORGANIZATION_ID = org_id
115 )
116 LOOP
117 N:=N+1;
118 t_org_code_list(N) := C1.organization_id;
119 END LOOP;
120 elsif
121 ( organization_option = 1 ) then
122 t_org_code_list(1) := org_id;
123 end if;
124
125 FOR I in t_org_code_list.FIRST..t_org_code_list.LAST LOOP
126
127 /*In case of org hierarchy check if the org at current index is a
128 child of the master org of the current org. If it is, continue as
129 normal otherwise skip to end of loop*/
130
131 if ( organization_option = 2 ) THEN
132 OPEN c_master_org(t_org_code_list(I));
133 FETCH c_master_org into l_master_org;
134 CLOSE c_master_org;
135 end if;
136
137 if ( (organization_option = 2 and l_master_org = l_master_org_for_current_org)
138 or organization_option = 3 ) THEN
139
140 /*Check the existence of the Item in the curent Organization,
141 if found then call the Imploder API for the Organization,otherwise
142 check the existence of the Item in the next Organization of the
143 Organization List*/
144
145 select count(*) into dummy from mtl_system_items where
146 organization_id = t_org_code_list(I) and
147 inventory_item_id = item_id;
148
149 if dummy <1 then
150 item_found := FALSE;
151 end if;
152 /*setting item_found to false when organization_option = 2 and
153 l_master_org <> l_master_org_for_current_org: */
154 elsif (organization_option <> 1) THEN
155 item_found := FALSE;
156 end if;
157
158
159 if item_found then
160 -- commented for Bug #4070863 and added below
161 /*INSERT INTO BOM_SMALL_IMPL_TEMP
162 ( SEQUENCE_ID,
163 LOWEST_ITEM_ID,
164 CURRENT_ITEM_ID,
165 PARENT_ITEM_ID,
166 ALTERNATE_DESIGNATOR,
167 CURRENT_LEVEL,
168 SORT_CODE,
169 CURRENT_ASSEMBLY_TYPE,
170 COMPONENT_SEQUENCE_ID,
171 ORGANIZATION_ID,
172 LAST_UPDATE_DATE,
173 LAST_UPDATED_BY,
174 CREATION_DATE,
175 CREATED_BY,
176 implosion_date)
177 VALUES (sequence_id,
178 item_id,
179 item_id,
180 item_id,
181 NULL,
182 0,
183 -- '0000001',
184 Bom_Common_Definitions.G_Bom_Init_SortCode,
185 NULL,
186 NULL,
187 t_org_code_list(I),
188 sysdate,
189 -1,
190 sysdate,
191 -1,
192 to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
193
194 INSERT INTO BOM_SMALL_IMPL_TEMP
195 ( SEQUENCE_ID,
196 LOWEST_ITEM_ID,
197 CURRENT_ITEM_ID,
198 PARENT_ITEM_ID,
199 ALTERNATE_DESIGNATOR,
200 CURRENT_LEVEL,
201 SORT_CODE,
202 CURRENT_ASSEMBLY_TYPE,
203 COMPONENT_SEQUENCE_ID,
204 ORGANIZATION_ID,
205 LAST_UPDATE_DATE,
206 LAST_UPDATED_BY,
207 CREATION_DATE,
208 CREATED_BY,
209 IMPLOSION_DATE)
210 (
211 SELECT
212 sequence_id,
213 item_id,
214 item_id,
215 item_id,
216 NULL,
217 0,
218 --'0001',
219 Bom_Common_Definitions.G_Bom_Init_SortCode,
220 NULL,
221 NULL,
222 t_org_code_list(I),
223 sysdate,
224 -1,
225 sysdate,
226 -1,
227 to_date(impl_date, 'YYYY/MM/DD HH24:MI')
228 FROM DUAL
229 WHERE NOT EXISTS
230 ( SELECT 'X'
231 FROM BOM_SMALL_IMPL_TEMP
232 WHERE SEQUENCE_ID = sequence_id
233 AND LOWEST_ITEM_ID = item_id
234 AND CURRENT_ITEM_ID = item_id
235 AND PARENT_ITEM_ID = item_id
236 AND ALTERNATE_DESIGNATOR IS NULL
237 AND CURRENT_LEVEL = 0
238 AND SORT_CODE = Bom_Common_Definitions.G_Bom_Init_SortCode
239 AND CURRENT_ASSEMBLY_TYPE IS NULL
240 AND COMPONENT_SEQUENCE_ID IS NULL
241 AND ORGANIZATION_ID = t_org_code_list(I)
242 )
243 );
244
245 bompiinq.implosion(sequence_id,eng_mfg_flag,t_org_code_list(I),
246 impl_flag, display_option, levels_to_implode, impl_date,
247 unit_number_from, unit_number_to,
248 a_err_msg, a_err_code, serial_number_from, serial_number_to);
249
250 err_msg := a_err_msg;
251 err_code := a_err_code;
252 item_found := TRUE;
253 end if;
254 end loop;
255
256 if (a_err_code <> 0) then
257 ROLLBACK;
258 end if;
259
260 EXCEPTION
261 WHEN OTHERS THEN
262 err_msg := substrb(SQLERRM, 1, 80);
263 err_code := SQLCODE;
264 IF c_master_org%isopen THEN
265 CLOSE c_master_org;
266 END IF;
267 ROLLBACK;
268 END imploder_userexit;
269
270 PROCEDURE implosion(
271 sequence_id IN NUMBER,
272 eng_mfg_flag IN NUMBER,
273 org_id IN NUMBER,
274 impl_flag IN NUMBER,
275 display_option IN NUMBER,
276 levels_to_implode IN NUMBER,
277 impl_date IN VARCHAR2,
278 unit_number_from IN VARCHAR2,
279 unit_number_to IN VARCHAR2,
280 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
281 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
282 serial_number_from IN VARCHAR2 default NULL,
283 serial_number_to IN VARCHAR2 default NULL) AS
284
285 implosion_date VARCHAR2(25);
286 error_msg VARCHAR(80);
287 error_code NUMBER;
288
289 BEGIN
290 implosion_date := substr(impl_date, 1, 16);
291
292 if levels_to_implode = 1 then
293 sl_imploder(sequence_id, eng_mfg_flag, org_id, impl_flag,
294 display_option, implosion_date,unit_number_from,
295 unit_number_to, error_msg, error_code,
296 serial_number_from, serial_number_to);
297 else
298 ml_imploder(sequence_id, eng_mfg_flag, org_id, impl_flag,
299 levels_to_implode, implosion_date, unit_number_from,
300 unit_number_to, error_msg, error_code,
301 serial_number_from, serial_number_to);
302 end if;
303
304 err_msg := error_msg;
305 err_code := error_code;
306
307 if (error_code <> 0) then
308 ROLLBACK;
309 end if;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 err_msg := error_msg;
314 err_code := error_code;
315 ROLLBACK;
316 END implosion;
317
318 PROCEDURE sl_imploder (
319 sequence_id IN NUMBER,
320 eng_mfg_flag IN NUMBER,
321 org_id IN NUMBER,
322 impl_flag IN NUMBER,
323 display_option IN NUMBER,
324 impl_date IN VARCHAR2,
325 unit_number_from IN VARCHAR2,
326 unit_number_to IN VARCHAR2,
327 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
328 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
329 serial_number_from IN VARCHAR2 default NULL,
330 serial_number_to IN VARCHAR2 default NULL) AS
331
332 total_rows NUMBER;
333 cat_sort VARCHAR2(7);
334
335 CURSOR imploder (c_sequence_id NUMBER,
336 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
337 c_display_option NUMBER,
338 c_implosion_date VARCHAR2,
339 c_unit_number_from VARCHAR2,
340 c_unit_number_to VARCHAR2,
341 c_serial_number_from VARCHAR2,
342 c_serial_number_to VARCHAR2,
343 c_implemented_only_option NUMBER
344 ) IS
345 SELECT /*+ ordered first_rows */
346 BITT.LOWEST_ITEM_ID LID,
347 BITT.PARENT_ITEM_ID PID,
348 BBM.ASSEMBLY_ITEM_ID AID,
349 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
350 BITT.SORT_CODE SC,
351 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
352 BBM.ASSEMBLY_TYPE CAT,
353 BIC.COMPONENT_SEQUENCE_ID CSI,
354 BIC.OPERATION_SEQ_NUM OSN,
355 BIC.EFFECTIVITY_DATE ED,
356 BIC.DISABLE_DATE DD,
357 BIC.BASIS_TYPE BT,
358 BIC.COMPONENT_QUANTITY CQ,
359 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
360 BIC.CHANGE_NOTICE CN,
361 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
362 BBM.ORGANIZATION_ID OI,
363 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
364 BIC.TO_END_ITEM_UNIT_NUMBER TUN
365 FROM
366 BOM_SMALL_IMPL_TEMP BITT,
367 BOM_INVENTORY_COMPONENTS BIC,
368 BOM_BILL_OF_MATERIALS BBM,
369 MTL_SYSTEM_ITEMS MSI
370 where bitt.current_level = 0
371 and bitt.organization_id = c_org_id
372 and MSI.ORGANIZATION_ID = BBM.ORGANIZATION_ID
373 and MSI.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
374 and bitt.sequence_id = c_sequence_id
375 and bitt.parent_item_id = bic.component_item_id
376 and bic.bill_sequence_id = bbm.common_bill_sequence_id
377 and bbm.organization_id = c_org_id
378 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
379 and (
380 ( c_eng_mfg_flag = 1
381 and bbm.assembly_type = 1
382 ) /* get only Mfg boms */
383 or
384 (c_eng_mfg_flag = 2
385 ) /*both Mfg-Eng BOMs in ENG mode*/
386 ) /* end of entire and predicate */
387 and ( /* match par alt */
388 ((bbm.alternate_bom_designator is null and
389 bitt.lowest_alternate_designator is null)
390 or
391 (bbm.alternate_bom_designator =
392 bitt.lowest_alternate_designator))
393 or /* Pickup par with spec alt only, if start alt is null,*/
394 ( bitt.lowest_alternate_designator is null /*and bill with spec*/
395 and bbm.alternate_bom_designator is not null
396 /* alt doesnt exist */
397 and not exists (select NULL /*for current item */
398 from bom_bill_of_materials bbm2
399 where bbm2.organization_id = c_org_id
400 and bbm2.assembly_item_id = bitt.parent_item_id
401 and bbm2.alternate_bom_designator =
402 bbm.alternate_bom_designator
403 and (
404 (bitt.current_assembly_type = 1
405 and bbm2.assembly_type = 1)
406 or
407 (bitt.current_assembly_type = 2)
408 )
409 ) /* end of subquery */
410 ) /* end of parent with specific alt */
411 or /* Pickup prim par only if start alt is not null and bill 4*/
412 ( bitt.lowest_alternate_designator is not null
413 /* same par doesnt */
414 and bbm.alternate_bom_designator is null
415 /* exist with this alt */
416 and not exists (select NULL
417 from bom_bill_of_materials bbm2
418 where bbm2.organization_id = c_org_id
422 and (
419 and bbm2.assembly_item_id = bbm.assembly_item_id
420 and bbm2.alternate_bom_designator =
421 bitt.lowest_alternate_designator
423 (bitt.current_assembly_type = 1
424 and bbm2.assembly_type = 1)
425 or
426 (bitt.current_assembly_type = 2)
427 )
428 ) /* end of subquery */
429 ) /* end of parent with null alt */
430 )/* end of all alternate logic */
431 and ( /* Effectivity_control */
432 ( msi.effectivity_control =1 -- Date Effectivity
433 AND
434 ( /* start of all display options */
435 ( c_display_option = 2
436 and bic.effectivity_date
437 <= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
438 and ( bic.disable_date is null
439 or bic.disable_date >
440 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
441 )
442 ) /* end of CURRENT */
443 or
444 c_display_option = 1
445 or
446 ( c_display_option = 3
447 and ( bic.disable_date is null
448 or bic.disable_date >
449 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
450 )
451 ) /* end of CURRENT_AND_FUTURE */
452 ) /* end of all display options */
453 ) /* msi.effectivity_control =1 */
454 OR (
455 msi.effectivity_control =2 -- Unit Number Effectivity
456 AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
457 AND
458 c_unit_number_from is NOT NULL -- Profile Model/Unit Eff=YES
459 AND
460 (c_display_option = 1
461 OR (c_display_option in (2,3) AND bic.disable_date is null))
462 AND
463 BIC.FROM_END_ITEM_UNIT_NUMBER <= c_unit_number_to
464 AND
465 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_unit_number_from) >= c_unit_number_from
466 )
467 OR (
468 msi.effectivity_control =2 -- Unit Number Effectivity
469 AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
470 AND
471 c_serial_number_from is NOT NULL -- Serial Effectivity for EAM items
472 AND
473 (c_display_option = 1
474 OR (c_display_option in (2,3) AND bic.disable_date is null))
475 AND
476 BIC.FROM_END_ITEM_UNIT_NUMBER <= c_serial_number_to
477 AND
478 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_serial_number_from) >= c_serial_number_from
479 )
480 ) /* end of effectivity control */
481 and ( /* effectivity_control */
482 ( msi.effectivity_control =1 -- Date Effectivity
483 AND
484 ( /* start of implemented yes/no logic */
485 ( c_implemented_only_option = 1
486 and bic.implementation_date is not null
487 )
488 or
489 ( c_implemented_only_option = 2
490 and ( /* start of all display */
491 ( c_display_option = 2
492 and
493 bic.effectivity_date =
494 (select max(effectivity_date)
495 from bom_inventory_components bic2
496 where bic2.bill_sequence_id = bic.bill_sequence_id
497 and bic2.component_item_id = bic.component_item_id
498 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
499 and decode(bic.implementation_date, NULL,
500 bic.old_component_sequence_id,
501 bic.component_sequence_id) =
502 decode(bic2.implementation_date, NULL,
503 bic2.old_component_sequence_id,
504 bic2.component_sequence_id)
505 and trunc(bic2.effectivity_date, 'MI') <=
506 to_date(c_implosion_date,
507 'YYYY/MM/DD HH24:MI')
508 and NOT EXISTS (SELECT null
509 FROM bom_inventory_components bic3
510 WHERE bic3.bill_sequence_id =
511 bic.bill_sequence_id
512 AND bic3.old_component_sequence_id =
513 bic.component_sequence_id
514 AND NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
515 AND bic3.acd_type in (2,3)
516 AND bic3.disable_date <=
517 to_date(c_implosion_date,
518 'YYYY/MM/DD HH24:MI'))
519 and ( bic2.disable_date >
520 to_date(c_implosion_date,
521 'YYYY/MM/DD HH24:MI')
522 or bic2.disable_date is null )
523 ) /* end of subquery */
524 ) /* end of CURRENT */
525 or
526 ( c_display_option = 3
527 and bic.effectivity_date =
528 (select max(effectivity_date)
529 from bom_inventory_components bic2
530 where bic2.bill_sequence_id = bic.bill_sequence_id
531 and bic2.component_item_id = bic.component_item_id
535 nvl(bic.old_component_sequence_id,
532 and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
533 and nvl(bic2.old_component_sequence_id,
534 bic2.component_sequence_id) =
536 bic.component_sequence_id)
537 and bic2.effectivity_date <=
538 to_date(c_implosion_date,
539 'YYYY/MM/DD HH24:MI')
540 and NOT EXISTS (SELECT null
541 FROM bom_inventory_components bic4
542 WHERE bic4.bill_sequence_id =
543 bic.bill_sequence_id
544 AND bic4.old_component_sequence_id =
545 bic.component_sequence_id
546 AND NVL(bic4.ECO_FOR_PRODUCTION,2)= 2
547 AND bic4.acd_type in (2,3)
548 AND bic4.disable_date <=
549 to_date(c_implosion_date,
550 'YYYY/MM/DD HH24:MI'))
551 and ( bic2.disable_date >
552 to_date(c_implosion_date,
553 'YYYY/MM/DD HH24:MI')
554 or bic2.disable_date is null )
555 ) /* end of subquery */
556 or
557 bic.effectivity_date > to_date(c_implosion_date,
558 'YYYY/MM/DD HH24:MI')
559 ) /* end of current and future */
560 or
561 ( c_display_option = 1)
562 ) /* end of all display */
563 ) /* end of impl = no */
564 ) /* end of impl = yes-no */
565 ) /* effectivity_control = 1 */
566 OR /* serial effectivity control */
567 ( MSI.effectivity_control=2 -- Unit Effectivity
568 AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
569 AND
570 c_unit_number_from is NOT NULL
571 AND
572 ( /* start of implemented yes/no logic */
573 ( c_implemented_only_option = 1
574 and bic.implementation_date is not null
575 )
576 or
577 ( c_implemented_only_option = 2 )
578 )
579 ) /* effectivity_control = 2 */
580 OR /* serial effectivity control */
581 ( MSI.effectivity_control=2 -- Serial Effectivity for EAM items
582 AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
583 AND
584 c_serial_number_from is NOT NULL
585 AND
586 ( /* start of implemented yes/no logic */
587 ( c_implemented_only_option = 1
588 and bic.implementation_date is not null
589 )
590 or
591 ( c_implemented_only_option = 2 )
592 )
593 ) /* effectivity_control = 2 */
594 ) /* effectivity_control*/
595 order by bitt.parent_item_id,
596 bbm.assembly_item_id, bic.operation_seq_num;
597 Cursor Check_Configured_Parent(
598 P_Parent_Item in number,
599 P_Comp_Item in number) is
600 Select 1 dummy
601 From mtl_system_items msi1,
602 mtl_system_items msi2
603 Where msi1.inventory_item_id = P_Parent_Item
604 And msi1.organization_id = org_id
605 And msi2.inventory_item_id = P_Comp_Item
606 And msi2.organization_id = org_id
607 And msi1.bom_item_type = 4 -- Standard
608 And msi1.replenish_to_order_flag = 'Y'
609 And msi1.base_item_id is not null -- configured item
610 And msi2.bom_item_type in (1, 2); -- model or option class
611 Cursor Check_Disabled_Parent(
612 P_Parent_Item in number) is
613 Select 1 dummy
614 From mtl_system_items msi
615 Where msi.inventory_item_id = P_Parent_Item
616 And msi.organization_id = org_id
617 And msi.bom_enabled_flag = 'N';
618
619 TYPE number_tab_tp IS TABLE OF NUMBER
620 INDEX BY BINARY_INTEGER;
621
622 TYPE date_tab_tp IS TABLE OF DATE
623 INDEX BY BINARY_INTEGER;
624
625 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
626 INDEX BY BINARY_INTEGER;
627
628 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
629 INDEX BY BINARY_INTEGER;
630
631 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
632 INDEX BY BINARY_INTEGER;
633
634 l_lid number_tab_tp;
635 l_pid number_tab_tp;
636 l_aid number_tab_tp;
637 l_abd varchar_tab_10;
638 l_sc varchar_tab_240;
639 l_lad varchar_tab_10;
640 l_cat number_tab_tp;
641 l_csi number_tab_tp;
642 l_oi number_tab_tp;
643 l_osn number_tab_tp;
644 l_ed date_tab_tp;
645 l_dd date_tab_tp;
646 l_fun varchar_tab_30;
647 l_tun varchar_tab_30;
648 l_bt number_tab_tp;
649 l_cq number_tab_tp;
650 l_risd number_tab_tp;
651 l_cn varchar_tab_10;
652 l_impf number_tab_tp;
653
654 l_lid1 number_tab_tp;
655 l_pid1 number_tab_tp;
656 l_aid1 number_tab_tp;
657 l_abd1 varchar_tab_10;
658 l_sc1 varchar_tab_240;
659 l_lad1 varchar_tab_10;
663 l_osn1 number_tab_tp;
660 l_cat1 number_tab_tp;
661 l_csi1 number_tab_tp;
662 l_oi1 number_tab_tp;
664 l_ed1 date_tab_tp;
665 l_dd1 date_tab_tp;
666 l_fun1 varchar_tab_30;
667 l_tun1 varchar_tab_30;
668 l_bt1 number_tab_tp;
669 l_cq1 number_tab_tp;
670 l_risd1 number_tab_tp;
671 l_cn1 varchar_tab_10;
672 l_impf1 number_tab_tp;
673
674 Loop_Count_Val Number := 0;
675 l_bulk_count Number := 0;
676
677 Prune_Tree exception;
678
679 BEGIN
680
681 total_rows := 0;
682 l_bulk_count := 0;
683 -- Delete pl/sql tables.
684 l_lid1.delete;
685 l_pid1.delete;
686 l_aid1.delete;
687 l_abd1.delete;
688 l_sc1.delete;
689 l_lad1.delete;
690 l_cat1.delete;
691 l_csi1.delete;
692 l_oi1.delete;
693 l_osn1.delete;
694 l_ed1.delete;
695 l_dd1.delete;
696 l_fun1.delete;
697 l_tun1.delete;
698 l_bt1.delete;
699 l_cq1.delete;
700 l_risd1.delete;
701 l_cn1.delete;
702 l_impf1.delete;
703
704 l_lid.delete;
705 l_pid.delete;
706 l_aid.delete;
707 l_abd.delete;
708 l_sc.delete;
709 l_lad.delete;
710 l_cat.delete;
711 l_csi.delete;
712 l_oi.delete;
713 l_osn.delete;
714 l_ed.delete;
715 l_dd.delete;
716 l_fun.delete;
717 l_tun.delete;
718 l_bt.delete;
719 l_cq.delete;
720 l_risd.delete;
721 l_cn.delete;
722 l_impf.delete;
723
724 IF not imploder%isopen then
725 open imploder(sequence_id,
726 eng_mfg_flag, org_id,display_option,
727 IMpl_date, unit_number_from, unit_number_to,
728 serial_number_from, serial_number_to,
729 impl_flag);
730 end if;
731 FETCH imploder bulk collect into
732 l_lid,
733 L_pid,
734 l_aid,
735 l_abd,
736 l_sc,
737 l_lad,
738 l_cat,
739 l_csi,
740 l_osn,
741 l_ed,
742 l_dd,
743 l_bt,
744 l_cq,
745 l_risd,
746 l_cn,
747 l_impf,
748 l_oi,
749 l_fun,
750 l_tun;
751 loop_Count_Val := imploder%rowcount;
752 CLOSE imploder;
753
754 For i in 1..loop_Count_Val
755 Loop
756 Begin
757 For X_Item_Attributes in Check_Disabled_Parent(
758 P_Parent_Item => l_aid(i)) loop
759 l_lid.delete(i);
760 l_pid.delete(i);
761 l_aid.delete(i);
762 l_abd.delete(i);
763 l_sc.delete(i);
764 l_lad.delete(i);
765 l_cat.delete(i);
766 l_csi.delete(i);
767 l_oi.delete(i);
768 l_osn.delete(i);
769 l_ed.delete(i);
770 l_dd.delete(i);
771 l_fun.delete(i);
772 l_tun.delete(i);
773 l_bt.delete(i);
774 l_cq.delete(i);
775 l_risd.delete(i);
776 l_cn.delete(i);
777 l_impf.delete(i);
778 Raise Prune_Tree;
779 End loop; /* Cursor loop for Check_Disabled_Parent*/
780
781 l_lad(i) := l_abd(i);
782
783 total_rows := total_rows + 1;
784
785 -- cat_sort := lpad(total_rows, 7, '0');
786 cat_sort := lpad(total_rows, Bom_Common_Definitions.G_Bom_SortCode_Width, '0');
787
788 l_sc(i) := l_sc(i) || cat_sort;
789 Exception
790 When Prune_Tree then
791 null;
792 End;
793 End loop; /* For loop */
794
795 --Loop to check if the record exist. If It exist then copy the record into
796 --an other table and insert the other table.
797 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
798
799 For i in 1..loop_Count_Val Loop
800 if (l_impf.EXISTS(i)) Then
801 l_bulk_count := l_bulk_count + 1;
802 l_lid1(l_bulk_count) := l_lid(i);
803 l_pid1(l_bulk_count) := l_pid(i);
804 l_aid1(l_bulk_count) := l_aid(i);
805 l_abd1(l_bulk_count) := l_abd(i);
809 l_csi1(l_bulk_count) := l_csi(i);
806 l_sc1(l_bulk_count) := l_sc(i);
807 l_lad1(l_bulk_count) := l_lad(i);
808 l_cat1(l_bulk_count) := l_cat(i);
810 l_oi1(l_bulk_count) := l_oi(i);
811 l_osn1(l_bulk_count) := l_osn(i);
812 l_ed1(l_bulk_count) := l_ed(i);
813 l_dd1(l_bulk_count) := l_dd(i);
814 l_fun1(l_bulk_count) := l_fun(i);
815 l_tun1(l_bulk_count) := l_tun(i);
816 l_bt1(l_bulk_count) := l_bt(i);
817 l_cq1(l_bulk_count) := l_cq(i);
818 l_risd1(l_bulk_count):= l_risd(i);
819 l_impf1(l_bulk_count):= l_impf(i);
820 l_cn1(l_bulk_count) := l_cn(i);
821 End if;
822 END LOOP;
823
824
825 FORALL i IN 1..l_bulk_count
826 -- commented for Bug #4070863 and added below
827 /*INSERT INTO BOM_SMALL_IMPL_TEMP
828 (LOWEST_ITEM_ID,
829 CURRENT_ITEM_ID,
830 PARENT_ITEM_ID,
831 ALTERNATE_DESIGNATOR,
832 CURRENT_LEVEL,
833 SORT_CODE,
834 LOWEST_ALTERNATE_DESIGNATOR,
835 CURRENT_ASSEMBLY_TYPE,
836 SEQUENCE_ID,
837 COMPONENT_SEQUENCE_ID,
838 ORGANIZATION_ID,
839 OPERATION_SEQ_NUM,
840 EFFECTIVITY_DATE,
841 DISABLE_DATE,
842 FROM_END_ITEM_UNIT_NUMBER,
843 TO_END_ITEM_UNIT_NUMBER,
844 COMPONENT_QUANTITY,
845 REVISED_ITEM_SEQUENCE_ID,
846 CHANGE_NOTICE,
847 IMPLEMENTED_FLAG,
848 LAST_UPDATE_DATE,
849 LAST_UPDATED_BY,
850 CREATION_DATE,
851 CREATED_BY,
852 PARENT_SORT_CODE,
853 implosion_date) VALUES (
854 l_lid1(i),
855 l_pid1(i),
856 l_aid1(i),
857 l_abd1(i),
858 1,
859 l_sc1(i),
860 l_lad1(i),
861 l_cat1(i),
862 sequence_id,
863 l_csi1(i),
864 l_oi1(i),
865 l_osn1(i),
866 l_ed1(i),
867 l_dd1(i),
868 l_fun1(i),
869 l_tun1(i),
870 l_cq1(i),
871 l_risd1(i),
872 l_cn1(i),
873 l_impf1(i),
874 sysdate,
875 -1,
876 sysdate,
877 -1,
878 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
879 to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
880
881 INSERT INTO BOM_SMALL_IMPL_TEMP
882 (LOWEST_ITEM_ID,
883 CURRENT_ITEM_ID,
884 PARENT_ITEM_ID,
885 ALTERNATE_DESIGNATOR,
886 CURRENT_LEVEL,
887 SORT_CODE,
888 LOWEST_ALTERNATE_DESIGNATOR,
889 CURRENT_ASSEMBLY_TYPE,
890 SEQUENCE_ID,
891 COMPONENT_SEQUENCE_ID,
892 ORGANIZATION_ID,
893 OPERATION_SEQ_NUM,
894 EFFECTIVITY_DATE,
895 DISABLE_DATE,
896 FROM_END_ITEM_UNIT_NUMBER,
897 TO_END_ITEM_UNIT_NUMBER,
898 BASIS_TYPE,
899 COMPONENT_QUANTITY,
900 REVISED_ITEM_SEQUENCE_ID,
901 CHANGE_NOTICE,
902 IMPLEMENTED_FLAG,
903 LAST_UPDATE_DATE,
904 LAST_UPDATED_BY,
905 CREATION_DATE,
906 CREATED_BY,
907 PARENT_SORT_CODE,
908 IMPLOSION_DATE)
909 ( SELECT
910 l_lid1(i),
911 l_pid1(i),
912 l_aid1(i),
913 l_abd1(i),
914 1,
915 l_sc1(i),
916 l_lad1(i),
917 l_cat1(i),
918 sequence_id,
919 l_csi1(i),
920 l_oi1(i),
921 l_osn1(i),
922 l_ed1(i),
923 l_dd1(i),
924 l_fun1(i),
925 l_tun1(i),
926 l_bt1(i),
927 l_cq1(i),
928 l_risd1(i),
929 l_cn1(i),
930 l_impf1(i),
931 sysdate,
932 -1,
933 sysdate,
934 -1,
935 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
936 to_date(impl_date, 'YYYY/MM/DD HH24:MI')
937 FROM DUAL
938 WHERE NOT EXISTS
939 ( SELECT 'X'
940 FROM BOM_SMALL_IMPL_TEMP
941 WHERE LOWEST_ITEM_ID = l_lid1(i)
942 AND CURRENT_ITEM_ID = l_pid1(i)
946 AND SORT_CODE = l_sc1(i)
943 AND PARENT_ITEM_ID = l_aid1(i)
944 AND ALTERNATE_DESIGNATOR = l_abd1(i)
945 AND CURRENT_LEVEL = 1
947 AND SEQUENCE_ID = sequence_id
948 AND COMPONENT_SEQUENCE_ID = l_csi1(i)
949 AND ORGANIZATION_ID = l_oi1(i)
950 AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
951 )
952 );
953 /*
954 ** exception handlers
955 */
956 EXCEPTION
957 WHEN OTHERS THEN
958 error_code := SQLCODE;
959 err_msg := substrb(SQLERRM, 1, 80);
960 END sl_imploder;
961
962 PROCEDURE ml_imploder(
963 sequence_id IN NUMBER,
964 eng_mfg_flag IN NUMBER,
965 org_id IN NUMBER,
966 impl_flag IN NUMBER,
967 a_levels_to_implode IN NUMBER,
968 impl_date IN VARCHAR2,
969 unit_number_from IN VARCHAR2,
970 unit_number_to IN VARCHAR2,
971 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
972 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
973 serial_number_from IN VARCHAR2,
974 serial_number_to IN VARCHAR2) AS
975
976 prev_parent_item_id NUMBER;
977 cum_count NUMBER;
978 cur_level NUMBER;
979 total_rows NUMBER;
980 levels_to_implode NUMBER;
981 max_level NUMBER;
982 cat_sort VARCHAR2(7);
983 max_extents EXCEPTION;
984
985 /*
986 ** max extents exceeded exception
987 */
988 PRAGMA EXCEPTION_INIT(max_extents, -1631);
989
990 CURSOR imploder (c_current_level NUMBER, c_sequence_id NUMBER,
991 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
992 c_implosion_date VARCHAR2, c_unit_number_from VARCHAR2,
993 c_unit_number_to VARCHAR2,c_serial_number_from VARCHAR2,
994 c_serial_number_to VARCHAR2, c_implemented_only_option NUMBER
995 ) IS
996 SELECT /*+ ordered first_rows */
997 BITT.LOWEST_ITEM_ID LID,
998 BITT.PARENT_ITEM_ID PID,
999 BBM.ASSEMBLY_ITEM_ID AID,
1000 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
1001 BITT.SORT_CODE SC,
1002 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
1003 BBM.ASSEMBLY_TYPE CAT,
1004 BIC.COMPONENT_SEQUENCE_ID CSI,
1005 BIC.OPERATION_SEQ_NUM OSN,
1006 BIC.EFFECTIVITY_DATE ED,
1007 BIC.DISABLE_DATE DD,
1008 BIC.BASIS_TYPE BT,
1009 BIC.COMPONENT_QUANTITY CQ,
1010 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
1011 BIC.CHANGE_NOTICE CN,
1012 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
1013 BBM.ORGANIZATION_ID OI,
1014 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
1015 BIC.TO_END_ITEM_UNIT_NUMBER TUN
1016 FROM
1017 BOM_SMALL_IMPL_TEMP BITT,
1018 BOM_INVENTORY_COMPONENTS BIC,
1019 BOM_BILL_OF_MATERIALS BBM,
1020 MTL_SYSTEM_ITEMS MSI
1021 where
1022 bitt.current_level = c_current_level
1023 and bitt.organization_id = c_org_id
1024 and msi.organization_id = BBM.organization_id
1025 and msi.inventory_item_id = BBM.assembly_item_id
1026 and bitt.sequence_id = c_sequence_id
1027 and bitt.parent_item_id = bic.component_item_id
1028 and bic.bill_sequence_id = bbm.common_bill_sequence_id
1029 and bbm.organization_id = c_org_id
1030 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
1031 and ( c_eng_mfg_flag = 2 or c_eng_mfg_flag = 1 and
1032 ( c_current_level = 0
1033 and bbm.assembly_type = 1
1034 or c_current_level <> 0 and bitt.current_assembly_type = 1
1035 and bbm.assembly_type = 1))
1036 and ( c_current_level = 0
1037 or /* start of all alternate logic */
1038 bbm.alternate_bom_designator is null and
1039 bitt.lowest_alternate_designator is null
1040 or bbm.alternate_bom_designator = bitt.lowest_alternate_designator
1041 or ( bitt.lowest_alternate_designator is null
1042 and bbm.alternate_bom_designator is not null
1043 and not exists (select NULL /*for current item */
1044 from bom_bill_of_materials bbm2
1045 where bbm2.organization_id = c_org_id
1046 and bbm2.assembly_item_id =
1047 bitt.parent_item_id
1048 and bbm2.alternate_bom_designator =
1049 bbm.alternate_bom_designator
1050 and ( bitt.current_assembly_type = 2
1051 or bbm2.assembly_type = 1
1052 and bitt.current_assembly_type = 1)
1053 )
1054 )
1055 or /* Pickup prim par only if starting alt is not
1056 null and bill for .. */
1057 (bitt.lowest_alternate_designator is not null
1058 and bbm.alternate_bom_designator is null
1059 and not exists (select NULL
1060 from bom_bill_of_materials bbm2
1061 where bbm2.organization_id = c_org_id
1062 and bbm2.assembly_item_id =
1063 bbm.assembly_item_id
1064 and bbm2.alternate_bom_designator =
1068 or bitt.current_assembly_type = 2)
1065 bitt.lowest_alternate_designator
1066 and ( bitt.current_assembly_type = 1
1067 and bbm2.assembly_type = 1
1069 )
1070 )
1071 )
1072 and (( msi.effectivity_control=1 -- Date Effectivity Control
1073 and bic.effectivity_date <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI')
1074 and ( bic.disable_date is null or
1075 bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI'))
1076 and ( c_implemented_only_option = 1
1077 and bic.implementation_date is not null
1078 or
1079 ( c_implemented_only_option = 2
1080 and bic.effectivity_date =
1081 (select max(effectivity_date)
1082 from bom_inventory_components bic2
1083 where bic.bill_sequence_id = bic2.bill_sequence_id
1084 and bic.component_item_id = bic2.component_item_id
1085 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
1086 and decode(bic.implementation_date, NULL,
1087 decode(bic.old_component_sequence_id,null,
1088 bic.component_sequence_id,
1089 bic.old_component_sequence_id)
1090 ,bic.component_sequence_id) =
1091 decode(bic2.implementation_date,NULL,
1092 decode(bic2.old_component_sequence_id,null,
1093 -- bic2.component_sequence_id,bic.old_component_sequence_id)
1094 bic2.component_sequence_id,bic2.old_component_sequence_id) -- For FP Bug 6134733 (Base Bug : 5405194 )
1095 , bic2.component_sequence_id)
1096 and bic2.effectivity_date <=
1097 to_date(c_implosion_date,'YYYY/MM/DD HH24:MI')
1098 and NOT EXISTS (SELECT null
1099 FROM bom_inventory_components bic3
1100 WHERE bic3.bill_sequence_id =
1101 bic.bill_sequence_id
1102 AND bic3.old_component_sequence_id =
1103 bic.component_sequence_id
1104 and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
1105 AND bic3.acd_type in (2,3)
1106 AND bic3.disable_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI'))
1107 and (bic2.disable_date is null
1108 or bic2.disable_date > to_date(c_implosion_date,
1109 'YYYY/MM/DD HH24:MI')))
1110 )))
1111 OR
1112 ( msi.effectivity_control = 2
1113 AND
1114 BIC.FROM_END_ITEM_UNIT_NUMBER <= NVL(BITT.TO_END_ITEM_UNIT_NUMBER,
1115 BIC.FROM_END_ITEM_UNIT_NUMBER)
1116 AND
1117 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
1118 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
1119 BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
1120 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
1121 BIC.FROM_END_ITEM_UNIT_NUMBER)
1122 AND(c_implemented_only_option=1 and bic.implementation_date is not null
1123 or c_implemented_only_option = 2)
1124 AND bic.from_end_item_unit_number <= decode(msi.eam_item_type,1,c_serial_number_to,c_unit_number_to)
1125 AND decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from) is not null -- exclude serial eff EAM items
1126 AND bic.to_end_item_unit_number is null
1127 OR bic.to_end_item_unit_number >= decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from)))
1128 order by bitt.parent_item_id, bbm.assembly_item_id,
1129 bic.operation_seq_num;
1130
1131 Cursor Check_Configured_Parent(
1132 P_Parent_Item in number,
1133 P_Comp_Item in number) is
1134 Select 1 dummy
1135 From mtl_system_items msi1,
1136 mtl_system_items msi2
1137 Where msi1.inventory_item_id = P_Parent_Item
1138 And msi1.organization_id = org_id
1139 And msi2.inventory_item_id = P_Comp_Item
1140 And msi2.organization_id = org_id
1141 And msi1.bom_item_type = 4 -- Standard
1142 And msi1.replenish_to_order_flag = 'Y'
1143 And msi1.base_item_id is not null -- configured item
1144 And msi2.bom_item_type in (1, 2); -- model or option class
1145 Cursor Check_Disabled_Parent(
1146 P_Parent_Item in number) is
1147 Select 1 dummy
1148 From mtl_system_items msi
1149 Where msi.inventory_item_id = P_Parent_Item
1150 And msi.organization_id = org_id
1151 And msi.bom_enabled_flag = 'N';
1152
1153 TYPE number_tab_tp IS TABLE OF NUMBER
1154 INDEX BY BINARY_INTEGER;
1155
1156 TYPE date_tab_tp IS TABLE OF DATE
1157 INDEX BY BINARY_INTEGER;
1158
1159 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
1160 INDEX BY BINARY_INTEGER;
1161
1162 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
1163 INDEX BY BINARY_INTEGER;
1164
1165 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
1166 INDEX BY BINARY_INTEGER;
1167
1168
1169 l_lid number_tab_tp;
1170 l_pid number_tab_tp;
1171 l_aid number_tab_tp;
1172 l_abd varchar_tab_10;
1173 l_sc varchar_tab_240;
1174 l_lad varchar_tab_10;
1175 l_cat number_tab_tp;
1176 l_csi number_tab_tp;
1177 l_oi number_tab_tp;
1178 l_osn number_tab_tp;
1179 l_ed date_tab_tp;
1183 l_bt number_tab_tp;
1180 l_dd date_tab_tp;
1181 l_fun varchar_tab_30;
1182 l_tun varchar_tab_30;
1184 l_cq number_tab_tp;
1185 l_risd number_tab_tp;
1186 l_cn varchar_tab_10;
1187 l_impf number_tab_tp;
1188
1189 l_lid1 number_tab_tp;
1190 l_pid1 number_tab_tp;
1191 l_aid1 number_tab_tp;
1192 l_abd1 varchar_tab_10;
1193 l_sc1 varchar_tab_240;
1194 l_lad1 varchar_tab_10;
1195 l_cat1 number_tab_tp;
1196 l_csi1 number_tab_tp;
1197 l_oi1 number_tab_tp;
1198 l_osn1 number_tab_tp;
1199 l_ed1 date_tab_tp;
1200 l_dd1 date_tab_tp;
1201 l_fun1 varchar_tab_30;
1202 l_tun1 varchar_tab_30;
1203 l_bt1 number_tab_tp;
1204 l_cq1 number_tab_tp;
1205 l_risd1 number_tab_tp;
1206 l_cn1 varchar_tab_10;
1207 l_impf1 number_tab_tp;
1208
1209 Loop_Count_Val Number := 0;
1210 l_bulk_count Number := 0;
1211
1212 Prune_Tree exception;
1213 BEGIN
1214
1215 SELECT max(MAXIMUM_BOM_LEVEL)
1216 INTO max_level
1217 FROM BOM_PARAMETERS
1218 WHERE ORGANIZATION_ID = org_id;
1219
1220 IF SQL%NOTFOUND or max_level is null THEN
1221 max_level := 60;
1222 END IF;
1223
1224 levels_to_implode := a_levels_to_implode;
1225
1226 IF (levels_to_implode < 0 OR levels_to_implode > max_level) THEN
1227 levels_to_implode := max_level;
1228 END IF;
1229
1230 cur_level := 0; /* initialize level */
1231
1232 WHILE (cur_level < levels_to_implode) LOOP
1233 Loop_Count_Val := 0;
1234 total_rows := 0;
1235 cum_count := 0;
1236 l_bulk_count := 0;
1237
1238 -- Delete pl/sql tables.
1239 l_lid1.delete;
1240 l_pid1.delete;
1241 l_aid1.delete;
1242 l_abd1.delete;
1243 l_sc1.delete;
1244 l_lad1.delete;
1245 l_cat1.delete;
1246 l_csi1.delete;
1247 l_oi1.delete;
1248 l_osn1.delete;
1249 l_ed1.delete;
1250 l_dd1.delete;
1251 l_fun1.delete;
1252 l_tun1.delete;
1253 l_bt1.delete;
1254 l_cq1.delete;
1255 l_risd1.delete;
1256 l_cn1.delete;
1257 l_impf1.delete;
1258
1259 l_lid.delete;
1260 l_pid.delete;
1261 l_aid.delete;
1262 l_abd.delete;
1263 l_sc.delete;
1264 l_lad.delete;
1265 l_cat.delete;
1266 l_csi.delete;
1267 l_oi.delete;
1268 l_osn.delete;
1269 l_ed.delete;
1270 l_dd.delete;
1271 l_fun.delete;
1272 l_tun.delete;
1273 l_bt.delete;
1274 l_cq.delete;
1275 l_risd.delete;
1276 l_cn.delete;
1277 l_impf.delete;
1278
1279 -- Open the Cursor, Fetch and Close for each level
1280
1281 IF not imploder%isopen then
1282 open imploder(cur_level,sequence_id,
1283 eng_mfg_flag, org_id, IMpl_date,
1284 unit_number_from, unit_number_to,
1285 serial_number_from, serial_number_to,impl_flag);
1286 end if;
1287 FETCH imploder bulk collect into
1288 l_lid,
1289 l_pid,
1290 l_aid,
1291 l_abd,
1292 l_sc,
1293 l_lad,
1294 l_cat,
1295 l_csi,
1296 l_osn,
1297 l_ed,
1298 l_dd,
1299 l_bt,
1300 l_cq,
1301 l_risd,
1302 l_cn,
1303 l_impf,
1304 l_oi,
1305 l_fun,
1306 l_tun;
1307 loop_Count_Val := imploder%rowcount ;
1308 CLOSE imploder;
1309
1310 -- Loop through the values and check for cursors Check_Configured_Parent
1311 -- and Check_Disabled_Parent. If Record is found then delete that
1312 -- row from the pl/sql table
1313
1314 For i in 1..loop_Count_Val Loop -- Check Loop
1315 Begin
1316 if (cur_level >= 1) then
1317 For X_Item_Attributes in Check_Configured_Parent(
1318 P_Parent_Item => l_aid(i),
1319 P_Comp_Item => l_pid(i)) loop
1320 l_lid.delete(i);
1321 l_pid.delete(i);
1322 l_aid.delete(i);
1323 l_abd.delete(i);
1324 l_sc.delete(i);
1325 l_lad.delete(i);
1326 l_cat.delete(i);
1330 l_ed.delete(i);
1327 l_csi.delete(i);
1328 l_oi.delete(i);
1329 l_osn.delete(i);
1331 l_dd.delete(i);
1332 l_fun.delete(i);
1333 l_tun.delete(i);
1334 l_bt.delete(i);
1335 l_cq.delete(i);
1336 l_risd.delete(i);
1337 l_cn.delete(i);
1338 l_impf.delete(i);
1339 Raise Prune_Tree;
1340 End loop;
1341 End if;
1342 For X_Item_Attributes in Check_Disabled_Parent(
1343 P_Parent_Item => l_aid(i)) loop
1344 l_lid.delete(i);
1345 l_pid.delete(i);
1346 l_aid.delete(i);
1347 l_abd.delete(i);
1348 l_sc.delete(i);
1349 l_lad.delete(i);
1350 l_cat.delete(i);
1351 l_csi.delete(i);
1352 l_oi.delete(i);
1353 l_osn.delete(i);
1354 l_ed.delete(i);
1355 l_dd.delete(i);
1356 l_fun.delete(i);
1357 l_tun.delete(i);
1358 l_bt.delete(i);
1359 l_cq.delete(i);
1360 l_risd.delete(i);
1361 l_cn.delete(i);
1362 l_impf.delete(i);
1363 Raise Prune_Tree;
1364 End loop;
1365 total_rows := total_rows + 1;
1366 IF (cur_level = 0) THEN
1367 l_LAD(i) := l_ABD(i);
1368 END IF;
1369 IF (cum_count = 0) THEN
1370 prev_parent_item_id := l_PID(i);
1371 END IF;
1372
1373 IF (prev_parent_item_id <> l_PID(i)) THEN
1374 cum_count := 0;
1375 prev_parent_item_id := l_PID(i);
1376 END IF;
1377
1378 cum_count := cum_count + 1;
1379
1380 -- cat_sort := lpad(cum_count, 7, '0');
1381 cat_sort := lpad(cum_count, Bom_Common_Definitions.G_Bom_SortCode_Width , '0');
1382
1383 l_SC(i) := l_SC(i) || cat_sort;
1384 Exception
1385 When Prune_tree then
1386 null;
1387 End;
1388 End Loop; -- End of Check Loop
1389
1390
1391 --Loop to check if the record exist. If It exist then copy the record into
1392 --an other table and insert the other table.
1393 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
1394
1395 For i in 1..loop_Count_Val Loop
1396 if (l_impf.EXISTS(i)) Then
1397 l_bulk_count := l_bulk_count + 1;
1398 l_lid1(l_bulk_count) := l_lid(i);
1399 l_pid1(l_bulk_count) := l_pid(i);
1400 l_aid1(l_bulk_count) := l_aid(i);
1401 l_abd1(l_bulk_count) := l_abd(i);
1402 l_sc1(l_bulk_count) := l_sc(i);
1403 l_lad1(l_bulk_count) := l_lad(i);
1404 l_cat1(l_bulk_count) := l_cat(i);
1405 l_csi1(l_bulk_count) := l_csi(i);
1406 l_oi1(l_bulk_count) := l_oi(i);
1407 l_osn1(l_bulk_count) := l_osn(i);
1408 l_ed1(l_bulk_count) := l_ed(i);
1409 l_dd1(l_bulk_count) := l_dd(i);
1410 l_fun1(l_bulk_count) := l_fun(i);
1411 l_tun1(l_bulk_count) := l_tun(i);
1412 l_bt1(l_bulk_count) := l_bt(i);
1413 l_cq1(l_bulk_count) := l_cq(i);
1414 l_risd1(l_bulk_count):= l_risd(i);
1415 l_impf1(l_bulk_count):= l_impf(i);
1416 l_cn1(l_bulk_count) := l_cn(i);
1417 End if;
1418 END LOOP;
1419
1420 -- Insert the Second table values using FORALL.
1421
1422 FORALL i IN 1..l_bulk_count
1423 -- commented for Bug #4070863 and added below
1424 /*INSERT INTO BOM_SMALL_IMPL_TEMP
1425 (LOWEST_ITEM_ID,
1426 CURRENT_ITEM_ID,
1427 PARENT_ITEM_ID,
1428 ALTERNATE_DESIGNATOR,
1429 CURRENT_LEVEL,
1430 SORT_CODE,
1431 LOWEST_ALTERNATE_DESIGNATOR,
1432 CURRENT_ASSEMBLY_TYPE,
1433 SEQUENCE_ID,
1434 COMPONENT_SEQUENCE_ID,
1435 ORGANIZATION_ID,
1436 REVISED_ITEM_SEQUENCE_ID,
1437 CHANGE_NOTICE,
1438 OPERATION_SEQ_NUM,
1439 EFFECTIVITY_DATE,
1440 DISABLE_DATE,
1441 FROM_END_ITEM_UNIT_NUMBER,
1442 TO_END_ITEM_UNIT_NUMBER,
1443 COMPONENT_QUANTITY,
1444 IMPLEMENTED_FLAG,
1445 LAST_UPDATE_DATE,
1446 LAST_UPDATED_BY,
1447 CREATION_DATE,
1448 CREATED_BY,
1449 PARENT_SORT_CODE,
1450 implosion_date) VALUES (
1451 l_lid1(i),
1452 l_pid1(i),
1453 l_aid1(i),
1454 l_abd1(i),
1455 cur_level + 1,
1456 l_sc1(i),
1457 l_lad1(i),
1458 l_cat1(i),
1459 sequence_id,
1460 l_csi1(i),
1461 l_oi1(i),
1462 l_risd1(i),
1463 l_cn1(i),
1464 l_osn1(i),
1465 l_ed1(i),
1466 l_dd1(i),
1467 l_fun1(i),
1468 l_tun1(i),
1469 l_cq1(i),
1470 l_impf1(i),
1471 sysdate,
1472 -1,
1473 sysdate,
1474 -1,
1475 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
1476 to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
1477
1478 INSERT INTO BOM_SMALL_IMPL_TEMP
1479 (LOWEST_ITEM_ID,
1480 CURRENT_ITEM_ID,
1481 PARENT_ITEM_ID,
1482 ALTERNATE_DESIGNATOR,
1483 CURRENT_LEVEL,
1484 SORT_CODE,
1485 LOWEST_ALTERNATE_DESIGNATOR,
1486 CURRENT_ASSEMBLY_TYPE,
1487 SEQUENCE_ID,
1488 COMPONENT_SEQUENCE_ID,
1489 ORGANIZATION_ID,
1490 REVISED_ITEM_SEQUENCE_ID,
1494 DISABLE_DATE,
1491 CHANGE_NOTICE,
1492 OPERATION_SEQ_NUM,
1493 EFFECTIVITY_DATE,
1495 FROM_END_ITEM_UNIT_NUMBER,
1496 TO_END_ITEM_UNIT_NUMBER,
1497 BASIS_TYPE,
1498 COMPONENT_QUANTITY,
1499 IMPLEMENTED_FLAG,
1500 LAST_UPDATE_DATE,
1501 LAST_UPDATED_BY,
1502 CREATION_DATE,
1503 CREATED_BY,
1504 PARENT_SORT_CODE,
1505 IMPLOSION_DATE )
1506 ( SELECT
1507 l_lid1(i),
1508 l_pid1(i),
1509 l_aid1(i),
1510 l_abd1(i),
1511 (cur_level + 1),
1512 l_sc1(i),
1513 l_lad1(i),
1514 l_cat1(i),
1515 sequence_id,
1516 l_csi1(i),
1517 l_oi1(i),
1518 l_risd1(i),
1519 l_cn1(i),
1520 l_osn1(i),
1521 l_ed1(i),
1522 l_dd1(i),
1523 l_fun1(i),
1524 l_tun1(i),
1525 l_bt1(i),
1526 l_cq1(i),
1527 l_impf1(i),
1528 sysdate,
1529 -1,
1530 sysdate,
1531 -1,
1532 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
1533 to_date(impl_date, 'YYYY/MM/DD HH24:MI')
1534 FROM DUAL
1535 WHERE NOT EXISTS
1536 ( SELECT 'X'
1537 FROM BOM_SMALL_IMPL_TEMP
1538 WHERE LOWEST_ITEM_ID = l_lid1(i)
1539 AND CURRENT_ITEM_ID = l_pid1(i)
1540 AND PARENT_ITEM_ID = l_aid1(i)
1541 AND ALTERNATE_DESIGNATOR = l_abd1(i)
1542 AND CURRENT_LEVEL = (cur_level + 1)
1543 AND SORT_CODE = l_sc1(i)
1544 AND SEQUENCE_ID = sequence_id
1545 AND COMPONENT_SEQUENCE_ID = l_csi1(i)
1546 AND ORGANIZATION_ID = l_oi1(i)
1547 AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
1548 )
1549 );
1550
1551 IF (total_rows <> 0) THEN
1552 cur_level := cur_level + 1;
1553 ELSE
1554 goto done_imploding;
1555 END IF;
1556
1557 END LOOP; /* while levels */
1558
1559
1560 <<done_imploding>>
1561 error_code := 0;
1562 /*
1563 ** exception handlers
1564 */
1565 EXCEPTION
1566 WHEN max_extents THEN
1567 error_code := SQLCODE;
1568 err_msg := substrb(SQLERRM, 1, 80);
1569 WHEN OTHERS THEN
1570 error_code := SQLCODE;
1571 err_msg := substrb(SQLERRM, 1, 80);
1572 END ml_imploder;
1573
1574 END bompiinq;