[Home] [Help]
PACKAGE BODY: APPS.BOMPIINQ
Source
4 /*==========================================================================+
1 package body bompiinq as
2 /* $Header: BOMIINQB.pls 120.6.12020000.2 2012/07/05 09:28:54 ntungare ship $ */
3
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
79 under the current Organization Hierarchy
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
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
244
241 AND ORGANIZATION_ID = t_org_code_list(I)
242 )
243 );
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 --
336 -- bug 6957708
337 -- Added a hint based on an Index XX_BOM_COMPONENTS_B_I1 on BOM_COMPONENTS_B
338 -- which is not a seeded index.
339 -- This index XX_BOM_COMPONENTS_B_I1 is to be based on the
340 -- columns COMPONENT_ITEM_ID and PK1_VALUE (in the same order)
341 -- This index is needed only in case the customer has a very high data volume
342 -- for other customers this hint would play no role.
343 -- Also added 2 where clauses based on PK1_value and PK2_Value to cut
344 -- down the data from BIC
345 -- ntungare
346 --
347 --added hint for bug 13393349
348 CURSOR imploder (c_sequence_id NUMBER,
349 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
350 c_display_option NUMBER,
351 c_implosion_date VARCHAR2,
352 c_unit_number_from VARCHAR2,
353 c_unit_number_to VARCHAR2,
354 c_serial_number_from VARCHAR2,
355 c_serial_number_to VARCHAR2,
356 c_implemented_only_option NUMBER
357 ) IS
358 SELECT /*+ leading(bitt, bic, bbm, msi) cardinality(bitt,1) */
359 BITT.LOWEST_ITEM_ID LID,
360 BITT.PARENT_ITEM_ID PID,
361 BBM.ASSEMBLY_ITEM_ID AID,
362 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
363 BITT.SORT_CODE SC,
364 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
365 BBM.ASSEMBLY_TYPE CAT,
366 BIC.COMPONENT_SEQUENCE_ID CSI,
367 BIC.OPERATION_SEQ_NUM OSN,
368 BIC.EFFECTIVITY_DATE ED,
369 BIC.DISABLE_DATE DD,
370 BIC.BASIS_TYPE BT,
371 BIC.COMPONENT_QUANTITY CQ,
372 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
373 BIC.CHANGE_NOTICE CN,
374 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
375 BBM.ORGANIZATION_ID OI,
376 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
377 BIC.TO_END_ITEM_UNIT_NUMBER TUN
378 FROM
379 BOM_SMALL_IMPL_TEMP BITT,
380 BOM_INVENTORY_COMPONENTS BIC,
381 BOM_BILL_OF_MATERIALS BBM,
382 MTL_SYSTEM_ITEMS MSI
383 where bic.pk1_value = BITT.PARENT_ITEM_ID and
384 bic.pk2_value = NVL(bbm.common_organization_id,bbm.organization_id) and
385 bitt.current_level = 0
386 and bitt.organization_id = c_org_id
387 and MSI.ORGANIZATION_ID = BBM.ORGANIZATION_ID
388 and MSI.INVENTORY_ITEM_ID = BBM.ASSEMBLY_ITEM_ID
389 and bitt.sequence_id = c_sequence_id
390 and bitt.parent_item_id = bic.component_item_id
394 and (
391 and bic.bill_sequence_id = bbm.source_bill_sequence_id --bug 10361269
392 and bbm.organization_id = c_org_id
393 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
395 ( c_eng_mfg_flag = 1
396 and bbm.assembly_type = 1
397 ) /* get only Mfg boms */
398 or
399 (c_eng_mfg_flag = 2
400 ) /*both Mfg-Eng BOMs in ENG mode*/
401 ) /* end of entire and predicate */
402 and ( /* match par alt */
403 ((bbm.alternate_bom_designator is null and
404 bitt.lowest_alternate_designator is null)
405 or
406 (bbm.alternate_bom_designator =
407 bitt.lowest_alternate_designator))
408 or /* Pickup par with spec alt only, if start alt is null,*/
409 ( bitt.lowest_alternate_designator is null /*and bill with spec*/
410 and bbm.alternate_bom_designator is not null
411 /* alt doesnt exist */
412 and not exists (select NULL /*for current item */
413 from bom_bill_of_materials bbm2
414 where bbm2.organization_id = c_org_id
415 and bbm2.assembly_item_id = bitt.parent_item_id
416 and bbm2.alternate_bom_designator =
417 bbm.alternate_bom_designator
418 and (
419 (bitt.current_assembly_type = 1
420 and bbm2.assembly_type = 1)
421 or
422 (bitt.current_assembly_type = 2)
423 )
424 ) /* end of subquery */
425 ) /* end of parent with specific alt */
426 or /* Pickup prim par only if start alt is not null and bill 4*/
427 ( bitt.lowest_alternate_designator is not null
428 /* same par doesnt */
429 and bbm.alternate_bom_designator is null
430 /* exist with this alt */
431 and not exists (select NULL
432 from bom_bill_of_materials bbm2
433 where bbm2.organization_id = c_org_id
434 and bbm2.assembly_item_id = bbm.assembly_item_id
435 and bbm2.alternate_bom_designator =
436 bitt.lowest_alternate_designator
437 and (
438 (bitt.current_assembly_type = 1
439 and bbm2.assembly_type = 1)
440 or
441 (bitt.current_assembly_type = 2)
442 )
443 ) /* end of subquery */
444 ) /* end of parent with null alt */
445 )/* end of all alternate logic */
446 and ( /* Effectivity_control */
447 ( msi.effectivity_control =1 -- Date Effectivity
448 AND
449 ( /* start of all display options */
450 ( c_display_option = 2
451 and bic.effectivity_date
452 <= to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
453 and ( bic.disable_date is null
454 or bic.disable_date >
455 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
456 )
457 ) /* end of CURRENT */
458 or
459 c_display_option = 1
460 or
461 ( c_display_option = 3
462 and ( bic.disable_date is null
463 or bic.disable_date >
464 to_date (c_implosion_date, 'YYYY/MM/DD HH24:MI')
465 )
466 ) /* end of CURRENT_AND_FUTURE */
467 ) /* end of all display options */
468 ) /* msi.effectivity_control =1 */
469 OR (
470 msi.effectivity_control =2 -- Unit Number Effectivity
471 AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
472 AND
473 c_unit_number_from is NOT NULL -- Profile Model/Unit Eff=YES
474 AND
475 (c_display_option = 1
476 OR (c_display_option in (2,3) AND bic.disable_date is null))
477 AND
478 BIC.FROM_END_ITEM_UNIT_NUMBER <= c_unit_number_to
479 AND
480 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_unit_number_from) >= c_unit_number_from
481 )
482 OR (
483 msi.effectivity_control =2 -- Unit Number Effectivity
484 AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
485 AND
486 c_serial_number_from is NOT NULL -- Serial Effectivity for EAM items
487 AND
488 (c_display_option = 1
489 OR (c_display_option in (2,3) AND bic.disable_date is null))
490 AND
491 BIC.FROM_END_ITEM_UNIT_NUMBER <= c_serial_number_to
492 AND
493 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,c_serial_number_from) >= c_serial_number_from
494 )
495 ) /* end of effectivity control */
496 and ( /* effectivity_control */
497 ( msi.effectivity_control =1 -- Date Effectivity
498 AND
499 ( /* start of implemented yes/no logic */
500 ( c_implemented_only_option = 1
501 and bic.implementation_date is not null
502 )
503 or
504 ( c_implemented_only_option = 2
505 and ( /* start of all display */
506 ( c_display_option = 2
507 and
508 bic.effectivity_date =
509 (select max(effectivity_date)
510 from bom_inventory_components bic2
514 and decode(bic.implementation_date, NULL,
511 where bic2.bill_sequence_id = bic.bill_sequence_id
512 and bic2.component_item_id = bic.component_item_id
513 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
515 bic.old_component_sequence_id,
516 bic.component_sequence_id) =
517 decode(bic2.implementation_date, NULL,
518 bic2.old_component_sequence_id,
519 bic2.component_sequence_id)
520 and trunc(bic2.effectivity_date, 'MI') <=
521 to_date(c_implosion_date,
522 'YYYY/MM/DD HH24:MI')
523 and NOT EXISTS (SELECT null
524 FROM bom_inventory_components bic3
525 WHERE bic3.bill_sequence_id =
526 bic.bill_sequence_id
527 AND bic3.old_component_sequence_id =
528 bic.component_sequence_id
529 AND NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
530 AND bic3.acd_type in (2,3)
531 AND bic3.disable_date <=
532 to_date(c_implosion_date,
533 'YYYY/MM/DD HH24:MI'))
534 and ( bic2.disable_date >
535 to_date(c_implosion_date,
536 'YYYY/MM/DD HH24:MI')
537 or bic2.disable_date is null )
538 ) /* end of subquery */
539 ) /* end of CURRENT */
540 or
541 ( c_display_option = 3
542 and bic.effectivity_date =
543 (select max(effectivity_date)
544 from bom_inventory_components bic2
545 where bic2.bill_sequence_id = bic.bill_sequence_id
546 and bic2.component_item_id = bic.component_item_id
547 and NVL(bic2.ECO_FOR_PRODUCTION,2) = 2
548 and nvl(bic2.old_component_sequence_id,
549 bic2.component_sequence_id) =
550 nvl(bic.old_component_sequence_id,
551 bic.component_sequence_id)
552 and bic2.effectivity_date <=
553 to_date(c_implosion_date,
554 'YYYY/MM/DD HH24:MI')
555 and NOT EXISTS (SELECT null
556 FROM bom_inventory_components bic4
557 WHERE bic4.bill_sequence_id =
558 bic.bill_sequence_id
559 AND bic4.old_component_sequence_id =
560 bic.component_sequence_id
561 AND NVL(bic4.ECO_FOR_PRODUCTION,2)= 2
562 AND bic4.acd_type in (2,3)
563 AND bic4.disable_date <=
564 to_date(c_implosion_date,
565 'YYYY/MM/DD HH24:MI'))
566 and ( bic2.disable_date >
567 to_date(c_implosion_date,
568 'YYYY/MM/DD HH24:MI')
569 or bic2.disable_date is null )
570 ) /* end of subquery */
571 or
572 bic.effectivity_date > to_date(c_implosion_date,
573 'YYYY/MM/DD HH24:MI')
574 ) /* end of current and future */
575 or
576 ( c_display_option = 1)
577 ) /* end of all display */
578 ) /* end of impl = no */
579 ) /* end of impl = yes-no */
580 ) /* effectivity_control = 1 */
581 OR /* serial effectivity control */
582 ( MSI.effectivity_control=2 -- Unit Effectivity
583 AND nvl(msi.eam_item_type,0) <> 1 -- do not include serial eff EAM items
584 AND
585 c_unit_number_from is NOT NULL
586 AND
587 ( /* start of implemented yes/no logic */
588 ( c_implemented_only_option = 1
589 and bic.implementation_date is not null
590 )
591 or
592 ( c_implemented_only_option = 2 )
593 )
594 ) /* effectivity_control = 2 */
595 OR /* serial effectivity control */
596 ( MSI.effectivity_control=2 -- Serial Effectivity for EAM items
597 AND nvl(msi.eam_item_type,0) = 1 -- include only serial eff EAM items
598 AND
599 c_serial_number_from is NOT NULL
600 AND
601 ( /* start of implemented yes/no logic */
602 ( c_implemented_only_option = 1
603 and bic.implementation_date is not null
604 )
605 or
606 ( c_implemented_only_option = 2 )
607 )
608 ) /* effectivity_control = 2 */
609 ) /* effectivity_control*/
610 order by bitt.parent_item_id,
611 bbm.assembly_item_id, bic.operation_seq_num;
612 Cursor Check_Configured_Parent(
613 P_Parent_Item in number,
614 P_Comp_Item in number) is
615 Select 1 dummy
616 From mtl_system_items msi1,
617 mtl_system_items msi2
618 Where msi1.inventory_item_id = P_Parent_Item
619 And msi1.organization_id = org_id
620 And msi2.inventory_item_id = P_Comp_Item
621 And msi2.organization_id = org_id
622 And msi1.bom_item_type = 4 -- Standard
623 And msi1.replenish_to_order_flag = 'Y'
624 And msi1.base_item_id is not null -- configured item
625 And msi2.bom_item_type in (1, 2); -- model or option class
626 Cursor Check_Disabled_Parent(
627 P_Parent_Item in number) is
628 Select 1 dummy
632 And msi.bom_enabled_flag = 'N';
629 From mtl_system_items msi
630 Where msi.inventory_item_id = P_Parent_Item
631 And msi.organization_id = org_id
633
634 TYPE number_tab_tp IS TABLE OF NUMBER
635 INDEX BY BINARY_INTEGER;
636
637 TYPE date_tab_tp IS TABLE OF DATE
638 INDEX BY BINARY_INTEGER;
639
640 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
641 INDEX BY BINARY_INTEGER;
642
643 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
644 INDEX BY BINARY_INTEGER;
645
646 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
647 INDEX BY BINARY_INTEGER;
648
649 l_lid number_tab_tp;
650 l_pid number_tab_tp;
651 l_aid number_tab_tp;
652 l_abd varchar_tab_10;
653 l_sc varchar_tab_240;
654 l_lad varchar_tab_10;
655 l_cat number_tab_tp;
656 l_csi number_tab_tp;
657 l_oi number_tab_tp;
658 l_osn number_tab_tp;
659 l_ed date_tab_tp;
660 l_dd date_tab_tp;
661 l_fun varchar_tab_30;
662 l_tun varchar_tab_30;
663 l_bt number_tab_tp;
664 l_cq number_tab_tp;
665 l_risd number_tab_tp;
666 l_cn varchar_tab_10;
667 l_impf number_tab_tp;
668
669 l_lid1 number_tab_tp;
670 l_pid1 number_tab_tp;
671 l_aid1 number_tab_tp;
672 l_abd1 varchar_tab_10;
673 l_sc1 varchar_tab_240;
674 l_lad1 varchar_tab_10;
675 l_cat1 number_tab_tp;
676 l_csi1 number_tab_tp;
677 l_oi1 number_tab_tp;
678 l_osn1 number_tab_tp;
679 l_ed1 date_tab_tp;
680 l_dd1 date_tab_tp;
681 l_fun1 varchar_tab_30;
682 l_tun1 varchar_tab_30;
683 l_bt1 number_tab_tp;
684 l_cq1 number_tab_tp;
685 l_risd1 number_tab_tp;
686 l_cn1 varchar_tab_10;
687 l_impf1 number_tab_tp;
688
689 Loop_Count_Val Number := 0;
690 l_bulk_count Number := 0;
691
692 Prune_Tree exception;
693
694 BEGIN
695
696 total_rows := 0;
697 l_bulk_count := 0;
698 -- Delete pl/sql tables.
699 l_lid1.delete;
700 l_pid1.delete;
701 l_aid1.delete;
702 l_abd1.delete;
703 l_sc1.delete;
704 l_lad1.delete;
705 l_cat1.delete;
706 l_csi1.delete;
707 l_oi1.delete;
708 l_osn1.delete;
709 l_ed1.delete;
710 l_dd1.delete;
711 l_fun1.delete;
712 l_tun1.delete;
713 l_bt1.delete;
714 l_cq1.delete;
715 l_risd1.delete;
716 l_cn1.delete;
717 l_impf1.delete;
718
719 l_lid.delete;
720 l_pid.delete;
721 l_aid.delete;
722 l_abd.delete;
723 l_sc.delete;
724 l_lad.delete;
725 l_cat.delete;
726 l_csi.delete;
727 l_oi.delete;
728 l_osn.delete;
729 l_ed.delete;
730 l_dd.delete;
731 l_fun.delete;
732 l_tun.delete;
733 l_bt.delete;
734 l_cq.delete;
735 l_risd.delete;
736 l_cn.delete;
737 l_impf.delete;
738
739 IF not imploder%isopen then
740 open imploder(sequence_id,
741 eng_mfg_flag, org_id,display_option,
742 IMpl_date, unit_number_from, unit_number_to,
743 serial_number_from, serial_number_to,
744 impl_flag);
745 end if;
746 FETCH imploder bulk collect into
747 l_lid,
748 L_pid,
749 l_aid,
750 l_abd,
751 l_sc,
752 l_lad,
753 l_cat,
754 l_csi,
755 l_osn,
756 l_ed,
757 l_dd,
758 l_bt,
759 l_cq,
760 l_risd,
761 l_cn,
762 l_impf,
763 l_oi,
764 l_fun,
765 l_tun;
766 loop_Count_Val := imploder%rowcount;
767 CLOSE imploder;
768
769 For i in 1..loop_Count_Val
770 Loop
771 Begin
772 For X_Item_Attributes in Check_Disabled_Parent(
773 P_Parent_Item => l_aid(i)) loop
774 l_lid.delete(i);
775 l_pid.delete(i);
776 l_aid.delete(i);
777 l_abd.delete(i);
778 l_sc.delete(i);
779 l_lad.delete(i);
780 l_cat.delete(i);
781 l_csi.delete(i);
782 l_oi.delete(i);
783 l_osn.delete(i);
784 l_ed.delete(i);
785 l_dd.delete(i);
786 l_fun.delete(i);
787 l_tun.delete(i);
788 l_bt.delete(i);
789 l_cq.delete(i);
790 l_risd.delete(i);
791 l_cn.delete(i);
792 l_impf.delete(i);
793 Raise Prune_Tree;
797
794 End loop; /* Cursor loop for Check_Disabled_Parent*/
795
796 l_lad(i) := l_abd(i);
798 total_rows := total_rows + 1;
799
800 -- cat_sort := lpad(total_rows, 7, '0');
801 cat_sort := lpad(total_rows, Bom_Common_Definitions.G_Bom_SortCode_Width, '0');
802
803 l_sc(i) := l_sc(i) || cat_sort;
804 Exception
805 When Prune_Tree then
806 null;
807 End;
808 End loop; /* For loop */
809
810 --Loop to check if the record exist. If It exist then copy the record into
811 --an other table and insert the other table.
812 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
813
814 For i in 1..loop_Count_Val Loop
815 if (l_impf.EXISTS(i)) Then
816 l_bulk_count := l_bulk_count + 1;
817 l_lid1(l_bulk_count) := l_lid(i);
818 l_pid1(l_bulk_count) := l_pid(i);
819 l_aid1(l_bulk_count) := l_aid(i);
820 l_abd1(l_bulk_count) := l_abd(i);
821 l_sc1(l_bulk_count) := l_sc(i);
822 l_lad1(l_bulk_count) := l_lad(i);
823 l_cat1(l_bulk_count) := l_cat(i);
824 l_csi1(l_bulk_count) := l_csi(i);
825 l_oi1(l_bulk_count) := l_oi(i);
826 l_osn1(l_bulk_count) := l_osn(i);
827 l_ed1(l_bulk_count) := l_ed(i);
828 l_dd1(l_bulk_count) := l_dd(i);
829 l_fun1(l_bulk_count) := l_fun(i);
830 l_tun1(l_bulk_count) := l_tun(i);
831 l_bt1(l_bulk_count) := l_bt(i);
832 l_cq1(l_bulk_count) := l_cq(i);
833 l_risd1(l_bulk_count):= l_risd(i);
834 l_impf1(l_bulk_count):= l_impf(i);
835 l_cn1(l_bulk_count) := l_cn(i);
836 End if;
837 END LOOP;
838
839
840 FORALL i IN 1..l_bulk_count
841 -- commented for Bug #4070863 and added below
842 /*INSERT INTO BOM_SMALL_IMPL_TEMP
843 (LOWEST_ITEM_ID,
844 CURRENT_ITEM_ID,
845 PARENT_ITEM_ID,
846 ALTERNATE_DESIGNATOR,
847 CURRENT_LEVEL,
848 SORT_CODE,
849 LOWEST_ALTERNATE_DESIGNATOR,
850 CURRENT_ASSEMBLY_TYPE,
851 SEQUENCE_ID,
852 COMPONENT_SEQUENCE_ID,
853 ORGANIZATION_ID,
854 OPERATION_SEQ_NUM,
855 EFFECTIVITY_DATE,
856 DISABLE_DATE,
857 FROM_END_ITEM_UNIT_NUMBER,
858 TO_END_ITEM_UNIT_NUMBER,
859 COMPONENT_QUANTITY,
860 REVISED_ITEM_SEQUENCE_ID,
861 CHANGE_NOTICE,
862 IMPLEMENTED_FLAG,
863 LAST_UPDATE_DATE,
864 LAST_UPDATED_BY,
865 CREATION_DATE,
866 CREATED_BY,
867 PARENT_SORT_CODE,
868 implosion_date) VALUES (
869 l_lid1(i),
870 l_pid1(i),
871 l_aid1(i),
872 l_abd1(i),
873 1,
874 l_sc1(i),
875 l_lad1(i),
876 l_cat1(i),
877 sequence_id,
878 l_csi1(i),
879 l_oi1(i),
880 l_osn1(i),
881 l_ed1(i),
882 l_dd1(i),
883 l_fun1(i),
884 l_tun1(i),
885 l_cq1(i),
886 l_risd1(i),
887 l_cn1(i),
888 l_impf1(i),
889 sysdate,
890 -1,
891 sysdate,
892 -1,
893 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
894 to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
895
896 INSERT INTO BOM_SMALL_IMPL_TEMP
897 (LOWEST_ITEM_ID,
898 CURRENT_ITEM_ID,
899 PARENT_ITEM_ID,
900 ALTERNATE_DESIGNATOR,
901 CURRENT_LEVEL,
902 SORT_CODE,
903 LOWEST_ALTERNATE_DESIGNATOR,
904 CURRENT_ASSEMBLY_TYPE,
905 SEQUENCE_ID,
906 COMPONENT_SEQUENCE_ID,
907 ORGANIZATION_ID,
908 OPERATION_SEQ_NUM,
909 EFFECTIVITY_DATE,
910 DISABLE_DATE,
911 FROM_END_ITEM_UNIT_NUMBER,
912 TO_END_ITEM_UNIT_NUMBER,
913 BASIS_TYPE,
914 COMPONENT_QUANTITY,
915 REVISED_ITEM_SEQUENCE_ID,
916 CHANGE_NOTICE,
917 IMPLEMENTED_FLAG,
918 LAST_UPDATE_DATE,
919 LAST_UPDATED_BY,
920 CREATION_DATE,
921 CREATED_BY,
922 PARENT_SORT_CODE,
923 IMPLOSION_DATE)
924 ( SELECT
925 l_lid1(i),
926 l_pid1(i),
927 l_aid1(i),
928 l_abd1(i),
929 1,
930 l_sc1(i),
931 l_lad1(i),
932 l_cat1(i),
933 sequence_id,
934 l_csi1(i),
935 l_oi1(i),
936 l_osn1(i),
937 l_ed1(i),
941 l_bt1(i),
938 l_dd1(i),
939 l_fun1(i),
940 l_tun1(i),
942 l_cq1(i),
943 l_risd1(i),
944 l_cn1(i),
945 l_impf1(i),
946 sysdate,
947 -1,
948 sysdate,
949 -1,
950 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
951 to_date(impl_date, 'YYYY/MM/DD HH24:MI')
952 FROM DUAL
953 WHERE NOT EXISTS
954 ( SELECT 'X'
955 FROM BOM_SMALL_IMPL_TEMP
956 WHERE LOWEST_ITEM_ID = l_lid1(i)
957 AND CURRENT_ITEM_ID = l_pid1(i)
958 AND PARENT_ITEM_ID = l_aid1(i)
959 AND ALTERNATE_DESIGNATOR = l_abd1(i)
960 AND CURRENT_LEVEL = 1
961 AND SORT_CODE = l_sc1(i)
962 AND SEQUENCE_ID = sequence_id
963 AND COMPONENT_SEQUENCE_ID = l_csi1(i)
964 AND ORGANIZATION_ID = l_oi1(i)
965 AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
966 )
967 );
968 /*
969 ** exception handlers
970 */
971 EXCEPTION
972 WHEN OTHERS THEN
973 error_code := SQLCODE;
974 err_msg := substrb(SQLERRM, 1, 80);
975 END sl_imploder;
976
977 PROCEDURE ml_imploder(
978 sequence_id IN NUMBER,
979 eng_mfg_flag IN NUMBER,
980 org_id IN NUMBER,
981 impl_flag IN NUMBER,
982 a_levels_to_implode IN NUMBER,
983 impl_date IN VARCHAR2,
984 unit_number_from IN VARCHAR2,
985 unit_number_to IN VARCHAR2,
986 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
987 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
988 serial_number_from IN VARCHAR2,
989 serial_number_to IN VARCHAR2) AS
990
991 prev_parent_item_id NUMBER;
992 cum_count NUMBER;
993 cur_level NUMBER;
994 total_rows NUMBER;
995 levels_to_implode NUMBER;
996 max_level NUMBER;
997 cat_sort VARCHAR2(7);
998 max_extents EXCEPTION;
999
1000 /*
1001 ** max extents exceeded exception
1002 */
1003 PRAGMA EXCEPTION_INIT(max_extents, -1631);
1004
1005 --
1006 -- bug 6957708
1007 -- Added a hint based on an Index XX_BOM_COMPONENTS_B_I1 on BOM_COMPONENTS_B
1008 -- which is not a seeded index.
1009 -- This index XX_BOM_COMPONENTS_B_I1 is to be based on the
1010 -- columns COMPONENT_ITEM_ID and PK1_VALUE (in the same order)
1011 -- This index is needed only in case the customer has a very high data volume
1012 -- for other customers this hint would play no role.
1013 -- Also added 2 where clauses based on PK1_value and PK2_Value to cut
1014 -- down the data from BIC
1018 CURSOR imploder (c_current_level NUMBER, c_sequence_id NUMBER,
1015 -- ntungare
1016 --
1017 --added hint for bug 13393349
1019 c_eng_mfg_flag NUMBER, c_org_id NUMBER,
1020 c_implosion_date VARCHAR2, c_unit_number_from VARCHAR2,
1021 c_unit_number_to VARCHAR2,c_serial_number_from VARCHAR2,
1022 c_serial_number_to VARCHAR2, c_implemented_only_option NUMBER
1023 ) IS
1024 SELECT /*+ leading(bitt, bic, bbm, msi) cardinality(bitt,1) */
1025 BITT.LOWEST_ITEM_ID LID,
1026 BITT.PARENT_ITEM_ID PID,
1027 BBM.ASSEMBLY_ITEM_ID AID,
1028 BBM.ALTERNATE_BOM_DESIGNATOR ABD,
1029 BITT.SORT_CODE SC,
1030 BITT.LOWEST_ALTERNATE_DESIGNATOR LAD,
1031 BBM.ASSEMBLY_TYPE CAT,
1032 BIC.COMPONENT_SEQUENCE_ID CSI,
1033 BIC.OPERATION_SEQ_NUM OSN,
1034 BIC.EFFECTIVITY_DATE ED,
1035 BIC.DISABLE_DATE DD,
1036 BIC.BASIS_TYPE BT,
1037 BIC.COMPONENT_QUANTITY CQ,
1038 BIC.REVISED_ITEM_SEQUENCE_ID RISD,
1039 BIC.CHANGE_NOTICE CN,
1040 DECODE(BIC.IMPLEMENTATION_DATE, NULL, 2, 1) IMPF,
1041 BBM.ORGANIZATION_ID OI,
1042 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
1043 BIC.TO_END_ITEM_UNIT_NUMBER TUN
1044 FROM
1045 BOM_SMALL_IMPL_TEMP BITT,
1046 BOM_INVENTORY_COMPONENTS BIC,
1047 BOM_BILL_OF_MATERIALS BBM,
1048 MTL_SYSTEM_ITEMS MSI
1052 and bitt.organization_id = c_org_id
1049 where bic.pk1_value = BITT.PARENT_ITEM_ID and
1050 bic.pk2_value = NVL(bbm.common_organization_id, bbm.organization_id) and
1051 bitt.current_level = c_current_level
1053 and msi.organization_id = BBM.organization_id
1054 and msi.inventory_item_id = BBM.assembly_item_id
1055 and bitt.sequence_id = c_sequence_id
1056 and bitt.parent_item_id = bic.component_item_id
1057 and bic.bill_sequence_id = bbm.source_bill_sequence_id --bug 10361269
1058 and bbm.organization_id = c_org_id
1059 and NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
1060 and ( c_eng_mfg_flag = 2 or c_eng_mfg_flag = 1 and
1061 ( c_current_level = 0
1062 and bbm.assembly_type = 1
1063 or c_current_level <> 0 and bitt.current_assembly_type = 1
1064 and bbm.assembly_type = 1))
1065 and ( c_current_level = 0
1066 or /* start of all alternate logic */
1067 bbm.alternate_bom_designator is null and
1068 bitt.lowest_alternate_designator is null
1069 or bbm.alternate_bom_designator = bitt.lowest_alternate_designator
1070 or ( bitt.lowest_alternate_designator is null
1071 and bbm.alternate_bom_designator is not null
1072 and not exists (select NULL /*for current item */
1073 from bom_bill_of_materials bbm2
1074 where bbm2.organization_id = c_org_id
1075 and bbm2.assembly_item_id =
1076 bitt.parent_item_id
1077 and bbm2.alternate_bom_designator =
1078 bbm.alternate_bom_designator
1079 and ( bitt.current_assembly_type = 2
1080 or bbm2.assembly_type = 1
1081 and bitt.current_assembly_type = 1)
1082 )
1083 )
1084 or /* Pickup prim par only if starting alt is not
1085 null and bill for .. */
1086 (bitt.lowest_alternate_designator is not null
1087 and bbm.alternate_bom_designator is null
1088 and not exists (select NULL
1089 from bom_bill_of_materials bbm2
1090 where bbm2.organization_id = c_org_id
1091 and bbm2.assembly_item_id =
1092 bbm.assembly_item_id
1093 and bbm2.alternate_bom_designator =
1094 bitt.lowest_alternate_designator
1095 and ( bitt.current_assembly_type = 1
1096 and bbm2.assembly_type = 1
1097 or bitt.current_assembly_type = 2)
1098 )
1099 )
1100 )
1101 and (( msi.effectivity_control=1 -- Date Effectivity Control
1102 and bic.effectivity_date <= to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI')
1103 and ( bic.disable_date is null or
1104 bic.disable_date > to_date(c_implosion_date, 'YYYY/MM/DD HH24:MI'))
1105 and ( c_implemented_only_option = 1
1106 and bic.implementation_date is not null
1107 or
1108 ( c_implemented_only_option = 2
1109 and bic.effectivity_date =
1110 (select max(effectivity_date)
1111 from bom_inventory_components bic2
1112 where bic.bill_sequence_id = bic2.bill_sequence_id
1113 and bic.component_item_id = bic2.component_item_id
1114 and NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
1115 and decode(bic.implementation_date, NULL,
1116 decode(bic.old_component_sequence_id,null,
1117 bic.component_sequence_id,
1118 bic.old_component_sequence_id)
1119 ,bic.component_sequence_id) =
1120 decode(bic2.implementation_date,NULL,
1121 decode(bic2.old_component_sequence_id,null,
1122 -- bic2.component_sequence_id,bic.old_component_sequence_id)
1123 bic2.component_sequence_id,bic2.old_component_sequence_id) -- For FP Bug 6134733 (Base Bug : 5405194 )
1124 , bic2.component_sequence_id)
1125 and bic2.effectivity_date <=
1126 to_date(c_implosion_date,'YYYY/MM/DD HH24:MI')
1127 and NOT EXISTS (SELECT null
1128 FROM bom_inventory_components bic3
1129 WHERE bic3.bill_sequence_id =
1130 bic.bill_sequence_id
1131 AND bic3.old_component_sequence_id =
1132 bic.component_sequence_id
1133 and NVL(BIC3.ECO_FOR_PRODUCTION,2)= 2
1134 AND bic3.acd_type in (2,3)
1135 AND bic3.disable_date <= to_date(c_implosion_date,'YYYY/MM/DD HH24:MI'))
1136 and (bic2.disable_date is null
1137 or bic2.disable_date > to_date(c_implosion_date,
1138 'YYYY/MM/DD HH24:MI')))
1139 )))
1140 OR
1141 ( msi.effectivity_control = 2
1142 AND
1143 BIC.FROM_END_ITEM_UNIT_NUMBER <= NVL(BITT.TO_END_ITEM_UNIT_NUMBER,
1144 BIC.FROM_END_ITEM_UNIT_NUMBER)
1145 AND
1146 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
1147 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
1148 BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
1149 NVL(BITT.FROM_END_ITEM_UNIT_NUMBER,
1150 BIC.FROM_END_ITEM_UNIT_NUMBER)
1151 AND(c_implemented_only_option=1 and bic.implementation_date is not null
1152 or c_implemented_only_option = 2)
1153 AND bic.from_end_item_unit_number <= decode(msi.eam_item_type,1,c_serial_number_to,c_unit_number_to)
1157 order by bitt.parent_item_id, bbm.assembly_item_id,
1154 AND decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from) is not null -- exclude serial eff EAM items
1155 AND (bic.to_end_item_unit_number is null --added parenthesis for bug 9771643
1156 OR bic.to_end_item_unit_number >= decode(msi.eam_item_type,1,c_serial_number_from,c_unit_number_from))))
1158 bic.operation_seq_num;
1159
1160 Cursor Check_Configured_Parent(
1161 P_Parent_Item in number,
1162 P_Comp_Item in number) is
1163 Select 1 dummy
1164 From mtl_system_items msi1,
1165 mtl_system_items msi2
1166 Where msi1.inventory_item_id = P_Parent_Item
1167 And msi1.organization_id = org_id
1168 And msi2.inventory_item_id = P_Comp_Item
1169 And msi2.organization_id = org_id
1170 And msi1.bom_item_type = 4 -- Standard
1171 And msi1.replenish_to_order_flag = 'Y'
1172 And msi1.base_item_id is not null -- configured item
1173 And msi2.bom_item_type in (1, 2); -- model or option class
1174 Cursor Check_Disabled_Parent(
1175 P_Parent_Item in number) is
1176 Select 1 dummy
1177 From mtl_system_items msi
1178 Where msi.inventory_item_id = P_Parent_Item
1179 And msi.organization_id = org_id
1180 And msi.bom_enabled_flag = 'N';
1181
1182 TYPE number_tab_tp IS TABLE OF NUMBER
1183 INDEX BY BINARY_INTEGER;
1184
1185 TYPE date_tab_tp IS TABLE OF DATE
1186 INDEX BY BINARY_INTEGER;
1187
1188 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
1189 INDEX BY BINARY_INTEGER;
1190
1191 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
1192 INDEX BY BINARY_INTEGER;
1193
1194 TYPE varchar_tab_240 IS TABLE OF VARCHAR2(240)
1195 INDEX BY BINARY_INTEGER;
1196
1197
1198 l_lid number_tab_tp;
1199 l_pid number_tab_tp;
1200 l_aid number_tab_tp;
1201 l_abd varchar_tab_10;
1202 l_sc varchar_tab_240;
1203 l_lad varchar_tab_10;
1204 l_cat number_tab_tp;
1205 l_csi number_tab_tp;
1206 l_oi number_tab_tp;
1207 l_osn number_tab_tp;
1208 l_ed date_tab_tp;
1209 l_dd date_tab_tp;
1210 l_fun varchar_tab_30;
1211 l_tun varchar_tab_30;
1212 l_bt number_tab_tp;
1213 l_cq number_tab_tp;
1214 l_risd number_tab_tp;
1215 l_cn varchar_tab_10;
1216 l_impf number_tab_tp;
1217
1218 l_lid1 number_tab_tp;
1219 l_pid1 number_tab_tp;
1220 l_aid1 number_tab_tp;
1221 l_abd1 varchar_tab_10;
1222 l_sc1 varchar_tab_240;
1223 l_lad1 varchar_tab_10;
1224 l_cat1 number_tab_tp;
1225 l_csi1 number_tab_tp;
1226 l_oi1 number_tab_tp;
1227 l_osn1 number_tab_tp;
1228 l_ed1 date_tab_tp;
1229 l_dd1 date_tab_tp;
1230 l_fun1 varchar_tab_30;
1231 l_tun1 varchar_tab_30;
1232 l_bt1 number_tab_tp;
1233 l_cq1 number_tab_tp;
1234 l_risd1 number_tab_tp;
1235 l_cn1 varchar_tab_10;
1236 l_impf1 number_tab_tp;
1237
1238 Loop_Count_Val Number := 0;
1239 l_bulk_count Number := 0;
1240
1241 Prune_Tree exception;
1242 BEGIN
1243
1244 SELECT max(MAXIMUM_BOM_LEVEL)
1245 INTO max_level
1246 FROM BOM_PARAMETERS
1247 WHERE ORGANIZATION_ID = org_id;
1248
1249 IF SQL%NOTFOUND or max_level is null THEN
1250 max_level := 60;
1251 END IF;
1252
1253 levels_to_implode := a_levels_to_implode;
1254
1255 IF (levels_to_implode < 0 OR levels_to_implode > max_level) THEN
1256 levels_to_implode := max_level;
1257 END IF;
1258
1259 cur_level := 0; /* initialize level */
1260
1261 WHILE (cur_level < levels_to_implode) LOOP
1262 Loop_Count_Val := 0;
1263 total_rows := 0;
1264 cum_count := 0;
1265 l_bulk_count := 0;
1266
1267 -- Delete pl/sql tables.
1268 l_lid1.delete;
1269 l_pid1.delete;
1270 l_aid1.delete;
1271 l_abd1.delete;
1272 l_sc1.delete;
1273 l_lad1.delete;
1274 l_cat1.delete;
1275 l_csi1.delete;
1276 l_oi1.delete;
1277 l_osn1.delete;
1278 l_ed1.delete;
1279 l_dd1.delete;
1280 l_fun1.delete;
1281 l_tun1.delete;
1282 l_bt1.delete;
1283 l_cq1.delete;
1284 l_risd1.delete;
1285 l_cn1.delete;
1286 l_impf1.delete;
1287
1288 l_lid.delete;
1289 l_pid.delete;
1290 l_aid.delete;
1291 l_abd.delete;
1292 l_sc.delete;
1293 l_lad.delete;
1294 l_cat.delete;
1295 l_csi.delete;
1296 l_oi.delete;
1297 l_osn.delete;
1298 l_ed.delete;
1299 l_dd.delete;
1300 l_fun.delete;
1301 l_tun.delete;
1302 l_bt.delete;
1303 l_cq.delete;
1304 l_risd.delete;
1305 l_cn.delete;
1306 l_impf.delete;
1307
1308 -- Open the Cursor, Fetch and Close for each level
1309
1310 IF not imploder%isopen then
1314 serial_number_from, serial_number_to,impl_flag);
1311 open imploder(cur_level,sequence_id,
1312 eng_mfg_flag, org_id, IMpl_date,
1313 unit_number_from, unit_number_to,
1315 end if;
1316 FETCH imploder bulk collect into
1317 l_lid,
1318 l_pid,
1319 l_aid,
1320 l_abd,
1321 l_sc,
1322 l_lad,
1323 l_cat,
1324 l_csi,
1325 l_osn,
1326 l_ed,
1327 l_dd,
1328 l_bt,
1329 l_cq,
1330 l_risd,
1331 l_cn,
1332 l_impf,
1333 l_oi,
1334 l_fun,
1335 l_tun;
1339 -- Loop through the values and check for cursors Check_Configured_Parent
1336 loop_Count_Val := imploder%rowcount ;
1337 CLOSE imploder;
1338
1340 -- and Check_Disabled_Parent. If Record is found then delete that
1341 -- row from the pl/sql table
1342
1343 For i in 1..loop_Count_Val Loop -- Check Loop
1344 Begin
1345 if (cur_level >= 1) then
1346 For X_Item_Attributes in Check_Configured_Parent(
1347 P_Parent_Item => l_aid(i),
1348 P_Comp_Item => l_pid(i)) loop
1349 l_lid.delete(i);
1350 l_pid.delete(i);
1351 l_aid.delete(i);
1352 l_abd.delete(i);
1353 l_sc.delete(i);
1354 l_lad.delete(i);
1355 l_cat.delete(i);
1356 l_csi.delete(i);
1360 l_dd.delete(i);
1357 l_oi.delete(i);
1358 l_osn.delete(i);
1359 l_ed.delete(i);
1361 l_fun.delete(i);
1362 l_tun.delete(i);
1363 l_bt.delete(i);
1364 l_cq.delete(i);
1365 l_risd.delete(i);
1366 l_cn.delete(i);
1367 l_impf.delete(i);
1368 Raise Prune_Tree;
1369 End loop;
1370 End if;
1371 For X_Item_Attributes in Check_Disabled_Parent(
1372 P_Parent_Item => l_aid(i)) loop
1373 l_lid.delete(i);
1374 l_pid.delete(i);
1375 l_aid.delete(i);
1376 l_abd.delete(i);
1377 l_sc.delete(i);
1378 l_lad.delete(i);
1379 l_cat.delete(i);
1380 l_csi.delete(i);
1381 l_oi.delete(i);
1382 l_osn.delete(i);
1383 l_ed.delete(i);
1384 l_dd.delete(i);
1385 l_fun.delete(i);
1386 l_tun.delete(i);
1387 l_bt.delete(i);
1388 l_cq.delete(i);
1389 l_risd.delete(i);
1390 l_cn.delete(i);
1391 l_impf.delete(i);
1392 Raise Prune_Tree;
1393 End loop;
1394 total_rows := total_rows + 1;
1395 IF (cur_level = 0) THEN
1396 l_LAD(i) := l_ABD(i);
1397 END IF;
1398 IF (cum_count = 0) THEN
1399 prev_parent_item_id := l_PID(i);
1400 END IF;
1401
1402 IF (prev_parent_item_id <> l_PID(i)) THEN
1403 cum_count := 0;
1404 prev_parent_item_id := l_PID(i);
1405 END IF;
1406
1407 cum_count := cum_count + 1;
1408
1409 -- cat_sort := lpad(cum_count, 7, '0');
1410 cat_sort := lpad(cum_count, Bom_Common_Definitions.G_Bom_SortCode_Width , '0');
1411
1412 l_SC(i) := l_SC(i) || cat_sort;
1413 Exception
1414 When Prune_tree then
1415 null;
1416 End;
1417 End Loop; -- End of Check Loop
1418
1419
1420 --Loop to check if the record exist. If It exist then copy the record into
1421 --an other table and insert the other table.
1422 --This has to be done to avoid "ELEMENT DOES NOT EXIST exception"
1423
1424 For i in 1..loop_Count_Val Loop
1425 if (l_impf.EXISTS(i)) Then
1426 l_bulk_count := l_bulk_count + 1;
1427 l_lid1(l_bulk_count) := l_lid(i);
1428 l_pid1(l_bulk_count) := l_pid(i);
1429 l_aid1(l_bulk_count) := l_aid(i);
1430 l_abd1(l_bulk_count) := l_abd(i);
1431 l_sc1(l_bulk_count) := l_sc(i);
1432 l_lad1(l_bulk_count) := l_lad(i);
1433 l_cat1(l_bulk_count) := l_cat(i);
1434 l_csi1(l_bulk_count) := l_csi(i);
1435 l_oi1(l_bulk_count) := l_oi(i);
1436 l_osn1(l_bulk_count) := l_osn(i);
1437 l_ed1(l_bulk_count) := l_ed(i);
1438 l_dd1(l_bulk_count) := l_dd(i);
1439 l_fun1(l_bulk_count) := l_fun(i);
1440 l_tun1(l_bulk_count) := l_tun(i);
1441 l_bt1(l_bulk_count) := l_bt(i);
1442 l_cq1(l_bulk_count) := l_cq(i);
1443 l_risd1(l_bulk_count):= l_risd(i);
1444 l_impf1(l_bulk_count):= l_impf(i);
1445 l_cn1(l_bulk_count) := l_cn(i);
1446 End if;
1447 END LOOP;
1448
1449 -- Insert the Second table values using FORALL.
1450
1451 FORALL i IN 1..l_bulk_count
1452 -- commented for Bug #4070863 and added below
1453 /*INSERT INTO BOM_SMALL_IMPL_TEMP
1454 (LOWEST_ITEM_ID,
1455 CURRENT_ITEM_ID,
1456 PARENT_ITEM_ID,
1457 ALTERNATE_DESIGNATOR,
1458 CURRENT_LEVEL,
1459 SORT_CODE,
1460 LOWEST_ALTERNATE_DESIGNATOR,
1461 CURRENT_ASSEMBLY_TYPE,
1462 SEQUENCE_ID,
1463 COMPONENT_SEQUENCE_ID,
1464 ORGANIZATION_ID,
1465 REVISED_ITEM_SEQUENCE_ID,
1466 CHANGE_NOTICE,
1467 OPERATION_SEQ_NUM,
1468 EFFECTIVITY_DATE,
1469 DISABLE_DATE,
1470 FROM_END_ITEM_UNIT_NUMBER,
1471 TO_END_ITEM_UNIT_NUMBER,
1472 COMPONENT_QUANTITY,
1473 IMPLEMENTED_FLAG,
1474 LAST_UPDATE_DATE,
1475 LAST_UPDATED_BY,
1479 implosion_date) VALUES (
1476 CREATION_DATE,
1477 CREATED_BY,
1478 PARENT_SORT_CODE,
1480 l_lid1(i),
1481 l_pid1(i),
1482 l_aid1(i),
1483 l_abd1(i),
1484 cur_level + 1,
1485 l_sc1(i),
1486 l_lad1(i),
1487 l_cat1(i),
1488 sequence_id,
1489 l_csi1(i),
1490 l_oi1(i),
1491 l_risd1(i),
1492 l_cn1(i),
1493 l_osn1(i),
1494 l_ed1(i),
1495 l_dd1(i),
1496 l_fun1(i),
1497 l_tun1(i),
1498 l_cq1(i),
1499 l_impf1(i),
1500 sysdate,
1501 -1,
1502 sysdate,
1503 -1,
1504 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
1505 to_date(impl_date, 'YYYY/MM/DD HH24:MI')); */
1506
1507 INSERT INTO BOM_SMALL_IMPL_TEMP
1508 (LOWEST_ITEM_ID,
1509 CURRENT_ITEM_ID,
1510 PARENT_ITEM_ID,
1511 ALTERNATE_DESIGNATOR,
1512 CURRENT_LEVEL,
1513 SORT_CODE,
1514 LOWEST_ALTERNATE_DESIGNATOR,
1515 CURRENT_ASSEMBLY_TYPE,
1516 SEQUENCE_ID,
1517 COMPONENT_SEQUENCE_ID,
1518 ORGANIZATION_ID,
1519 REVISED_ITEM_SEQUENCE_ID,
1520 CHANGE_NOTICE,
1521 OPERATION_SEQ_NUM,
1522 EFFECTIVITY_DATE,
1523 DISABLE_DATE,
1524 FROM_END_ITEM_UNIT_NUMBER,
1525 TO_END_ITEM_UNIT_NUMBER,
1526 BASIS_TYPE,
1527 COMPONENT_QUANTITY,
1528 IMPLEMENTED_FLAG,
1529 LAST_UPDATE_DATE,
1530 LAST_UPDATED_BY,
1531 CREATION_DATE,
1532 CREATED_BY,
1533 PARENT_SORT_CODE,
1534 IMPLOSION_DATE )
1535 ( SELECT
1536 l_lid1(i),
1537 l_pid1(i),
1538 l_aid1(i),
1539 l_abd1(i),
1540 (cur_level + 1),
1541 l_sc1(i),
1542 l_lad1(i),
1543 l_cat1(i),
1544 sequence_id,
1545 l_csi1(i),
1546 l_oi1(i),
1547 l_risd1(i),
1548 l_cn1(i),
1549 l_osn1(i),
1550 l_ed1(i),
1551 l_dd1(i),
1552 l_fun1(i),
1553 l_tun1(i),
1554 l_bt1(i),
1555 l_cq1(i),
1556 l_impf1(i),
1557 sysdate,
1558 -1,
1559 sysdate,
1560 -1,
1561 decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7)),
1562 to_date(impl_date, 'YYYY/MM/DD HH24:MI')
1563 FROM DUAL
1564 WHERE NOT EXISTS
1565 ( SELECT 'X'
1566 FROM BOM_SMALL_IMPL_TEMP
1567 WHERE LOWEST_ITEM_ID = l_lid1(i)
1568 AND CURRENT_ITEM_ID = l_pid1(i)
1569 AND PARENT_ITEM_ID = l_aid1(i)
1570 AND ALTERNATE_DESIGNATOR = l_abd1(i)
1571 AND CURRENT_LEVEL = (cur_level + 1)
1572 AND SORT_CODE = l_sc1(i)
1573 AND SEQUENCE_ID = sequence_id
1574 AND COMPONENT_SEQUENCE_ID = l_csi1(i)
1575 AND ORGANIZATION_ID = l_oi1(i)
1576 AND PARENT_SORT_CODE = decode(length(l_sc1(i)), 7,null,substrb(l_sc1(i),1,length(l_sc1(i))-7))
1577 )
1578 );
1579
1580 IF (total_rows <> 0) THEN
1581 cur_level := cur_level + 1;
1582 ELSE
1583 goto done_imploding;
1584 END IF;
1585
1586 END LOOP; /* while levels */
1587
1588
1589 <<done_imploding>>
1590 error_code := 0;
1591 /*
1592 ** exception handlers
1593 */
1594 EXCEPTION
1595 WHEN max_extents THEN
1596 error_code := SQLCODE;
1597 err_msg := substrb(SQLERRM, 1, 80);
1598 WHEN OTHERS THEN
1599 error_code := SQLCODE;
1600 err_msg := substrb(SQLERRM, 1, 80);
1601 END ml_imploder;
1602
1603 END bompiinq;