[Home] [Help]
PACKAGE BODY: APPS.BOMPBXIN
Source
1 PACKAGE BODY BOMPBXIN AS
2 -- $Header: BOMBXINB.pls 120.5.12010000.2 2008/09/25 13:33:36 rmpartha ship $
3 -- =========================================================================+
4 -- Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 -- All rights reserved. |
6 -- =========================================================================+
7 -- |
8 -- File Name : BOMBXINB.pls |
9 -- Description : This is the bom exploder. |
10 -- Parameters: org_id organization_id |
11 -- order_by 1 - Op seq, item seq |
12 -- 2 - Item seq, op seq |
13 -- grp_id unique value to identify current explosion |
14 -- use value from seq bom_small_expl_temp_s |
15 -- levels_to_explode |
16 -- bom_or_eng 1 - BOM |
17 -- 2 - ENG |
18 -- impl_flag 1 - implemented only |
19 -- 2 - both impl and unimpl |
20 -- explode_option 1 - All |
21 -- 2 - Current |
22 -- 3 - Current and future |
23 -- incl_oc_flag 1 - include OC and M under standard item |
24 -- 2 - do not include |
25 -- show_rev 1 - obtain current revision of component |
26 -- 2 - don't obtain current revision |
27 -- material_ctrl 1 - obtain subinventory locator |
28 -- 2 - don't obtain subinventory locator |
29 -- lead_time 1 - calculate offset percent |
30 -- 2 - don't calculate offset percent |
31 -- max_level max bom levels permissible for org |
32 -- rev_date explosion date |
33 -- err_msg error message out buffer |
34 -- error_code error code out. returns sql error code |
35 -- if sql error, 9999 if loop detected. |
36 -- Revision |
37 -- Shreyas Shah Creation |
38 -- 02/10/94 Shreyas Shah added common_bill_Seq_id to cursor |
39 -- added multi-org explosion |
40 -- 08/03/95 Rob Yee added parameters for 10SC |
41 -- 11/20/97 Rob Yee check max level one level farther |
42 -- |
43 -- =========================================================================
44
45 PROCEDURE bom_exploder(
46 verify_flag IN NUMBER DEFAULT 0,
47 online_flag IN NUMBER DEFAULT 1,
48 org_id IN NUMBER,
49 order_by IN NUMBER DEFAULT 1,
50 grp_id IN NUMBER,
51 levels_to_explode IN NUMBER DEFAULT 1,
52 bom_or_eng IN NUMBER DEFAULT 1,
53 impl_flag IN NUMBER DEFAULT 1,
54 plan_factor_flag IN NUMBER DEFAULT 2,
55 explode_option IN NUMBER DEFAULT 2,
56 std_comp_flag IN NUMBER DEFAULT 2,
57 incl_oc_flag IN NUMBER DEFAULT 1,
58 max_level IN NUMBER,
59 unit_number_from IN VARCHAR2,
60 unit_number_to IN VARCHAR2,
61 rev_date IN DATE DEFAULT sysdate,
62 show_rev IN NUMBER DEFAULT 2,
63 material_ctrl IN NUMBER DEFAULT 2,
64 lead_time IN NUMBER DEFAULT 2,
65 err_msg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
66 error_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
67
68 -- prev_sort_order VARCHAR2(4000);
69 prev_sort_order Bom_Common_Definitions.G_Bom_SortCode_Type;
70 prev_top_bill_id NUMBER;
71 cum_count NUMBER;
72 total_rows NUMBER;
73 cat_sort VARCHAR2(7);
74 impl_eco varchar2(20);
75
76 -- verify local vars
77 cur_component VARCHAR2(16);
78 cur_substr VARCHAR2(16);
79 cur_loopstr VARCHAR2(1000);
80 cur_loopflag VARCHAR2(1);
81 loop_found BOOLEAN := false;
82 max_level_exceeded BOOLEAN := false;
83 start_pos NUMBER;
84 -- Added Flex field values ATTRIBUTE1-ATTRIBUTE15 in the SELECT clause
85 -- #1409015
86 --- Bulk Collect Functioanlity Addition Bug 6039025 Start
87
88 TYPE number_tab_tp IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
89
90 TYPE varchar_tab_tp IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
91
92 TYPE DateTabType IS TABLE OF DATE INDEX BY BINARY_INTEGER;
93
94 p_oltp number_tab_tp;
95 l_TBSI number_tab_tp;
96 l_BSI number_tab_tp;
97 l_CBSI number_tab_tp;
98 l_CID number_tab_tp;
99 l_CSI number_tab_tp;
100 l_BT number_tab_tp;
101 l_CQ number_tab_tp;
102 l_EQ number_tab_tp;
103 l_SO varchar_tab_tp;
104 l_TID number_tab_tp;
105 l_TAD varchar_tab_tp;
106 l_CYF number_tab_tp;
107 l_OI number_tab_tp;
108 l_CC varchar_tab_tp;
109 l_IICR number_tab_tp;
110 l_LF number_tab_tp;
111 l_PF number_tab_tp;
112 l_OSN number_tab_tp;
113 l_BIT number_tab_tp;
114 l_PBIT number_tab_tp;
115 l_PAID number_tab_tp;
116 l_WST number_tab_tp;
117 l_ITN number_tab_tp;
118 l_ED DateTabType;
119 l_DD DateTabType;
120 l_ID DateTabType;
121 l_FUN varchar_tab_tp;
122 l_EUN varchar_tab_tp;
123 l_OPT number_tab_tp;
124 l_SS varchar_tab_tp;
125 l_SLI number_tab_tp;
126 l_CR varchar_tab_tp;
127 l_CN varchar_tab_tp;
128 l_OLTP number_tab_tp;
129 l_MEO number_tab_tp;
130 l_CATP number_tab_tp;
131 l_RTS number_tab_tp;
132 l_RFR number_tab_tp;
133 l_IOSD number_tab_tp;
134 l_LQ number_tab_tp;
135 l_HQ number_tab_tp;
136 l_SB number_tab_tp;
137 l_OPERATION_OFFSET number_tab_tp;
138 l_CURRENT_REVISION varchar_tab_tp;
139 l_LOCATOR varchar_tab_tp;
140 /*Bug 6350231 Changed the datatype of l_locator from
141 number_tab_tp to varchar_tab_tp */
142 l_ALTERNATE_BOM_DESIGNATOR varchar_tab_tp;
143 l_ATTRIBUTE_CATEGORY varchar_tab_tp;
144 l_ATTRIBUTE1 varchar_tab_tp;
145 l_ATTRIBUTE2 varchar_tab_tp;
146 l_ATTRIBUTE3 varchar_tab_tp;
147 l_ATTRIBUTE4 varchar_tab_tp;
148 l_ATTRIBUTE5 varchar_tab_tp;
149 l_ATTRIBUTE6 varchar_tab_tp;
150 l_ATTRIBUTE7 varchar_tab_tp;
151 l_ATTRIBUTE8 varchar_tab_tp;
152 l_ATTRIBUTE9 varchar_tab_tp;
153 l_ATTRIBUTE10 varchar_tab_tp;
154 l_ATTRIBUTE11 varchar_tab_tp;
155 l_ATTRIBUTE12 varchar_tab_tp;
156 l_ATTRIBUTE13 varchar_tab_tp;
157 l_ATTRIBUTE14 varchar_tab_tp;
158 l_ATTRIBUTE15 varchar_tab_tp;
159
160 loop_count_val Number := 0;
161
162 --- BulK Collect Functionality addition Bug 6039025 Stop
163
164 CURSOR exploder (
165 c_level NUMBER,
166 c_grp_id NUMBER,
167 c_org_id NUMBER,
168 c_bom_or_eng NUMBER,
169 c_rev_date date,
170 c_impl_flag NUMBER,
171 c_explode_option NUMBER,
172 c_order_by NUMBER,
173 c_verify_flag NUMBER,
174 c_plan_factor_flag NUMBER,
175 c_std_comp_flag NUMBER,
176 c_incl_oc NUMBER
177 ) IS
178 SELECT
179 BET.TOP_BILL_SEQUENCE_ID TBSI,
180 BOM.BILL_SEQUENCE_ID BSI,
181 BOM.COMMON_BILL_SEQUENCE_ID CBSI,
182 BIC.COMPONENT_ITEM_ID CID,
183 BIC.COMPONENT_SEQUENCE_ID CSI,
184 BIC.BASIS_TYPE BT,
185 BIC.COMPONENT_QUANTITY CQ,
186 (BIC.COMPONENT_QUANTITY *
187 decode(BIC.BASIS_TYPE , null,BET.EXTENDED_QUANTITY,1) *
188 decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
189 decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,
190 BIC.COMPONENT_YIELD_FACTOR)) EQ,
191 BET.SORT_ORDER SO,
192 BET.TOP_ITEM_ID TID,
193 BET.TOP_ALTERNATE_DESIGNATOR TAD,
194 BIC.COMPONENT_YIELD_FACTOR CYF,
195 BOM.ORGANIZATION_ID OI,
196 BET.COMPONENT_CODE CC,
197 BIC.INCLUDE_IN_COST_ROLLUP IICR,
198 BET.LOOP_FLAG LF,
199 BIC.PLANNING_FACTOR PF, BIC.OPERATION_SEQ_NUM OSN,
200 BIC.BOM_ITEM_TYPE BIT, BET.BOM_ITEM_TYPE PBIT,
201 BET.COMPONENT_ITEM_ID PAID, BIC.WIP_SUPPLY_TYPE WST,
202 BIC.ITEM_NUM ITN,
203 BIC.EFFECTIVITY_DATE ED,
204 BIC.DISABLE_DATE DD,
205 BIC.IMPLEMENTATION_DATE ID,
206 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
207 BIC.TO_END_ITEM_UNIT_NUMBER EUN,
208 BIC.OPTIONAL OPT,
209 BIC.SUPPLY_SUBINVENTORY SS,
210 BIC.SUPPLY_LOCATOR_ID SLI,
211 BIC.COMPONENT_REMARKS CR,
212 BIC.CHANGE_NOTICE CN,
213 BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
214 BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
215 BIC.CHECK_ATP CATP,
216 BIC.REQUIRED_TO_SHIP RTS,
217 BIC.REQUIRED_FOR_REVENUE RFR,
218 BIC.INCLUDE_ON_SHIP_DOCS IOSD,
219 BIC.LOW_QUANTITY LQ,
220 BIC.HIGH_QUANTITY HQ,
221 BIC.SO_BASIS SB,
222 BET.OPERATION_OFFSET,
223 BET.CURRENT_REVISION,
224 BET.LOCATOR,
225 BOM.ALTERNATE_BOM_DESIGNATOR, -- for routing
226 BIC.ATTRIBUTE_CATEGORY,
227 BIC.ATTRIBUTE1,
228 BIC.ATTRIBUTE2,
229 BIC.ATTRIBUTE3,
230 BIC.ATTRIBUTE4,
231 BIC.ATTRIBUTE5,
232 BIC.ATTRIBUTE6,
233 BIC.ATTRIBUTE7,
234 BIC.ATTRIBUTE8,
235 BIC.ATTRIBUTE9,
236 BIC.ATTRIBUTE10,
237 BIC.ATTRIBUTE11,
238 BIC.ATTRIBUTE12,
239 BIC.ATTRIBUTE13,
240 BIC.ATTRIBUTE14,
241 BIC.ATTRIBUTE15
242 FROM BOM_SMALL_EXPL_TEMP BET, BOM_BILL_OF_MATERIALS BOM,
243 MTL_SYSTEM_ITEMS SI,
244 BOM_INVENTORY_COMPONENTS BIC
245 WHERE BET.PLAN_LEVEL = c_level - 1
246 AND BET.GROUP_ID = c_grp_id
247 AND BOM.ASSEMBLY_ITEM_ID = SI.INVENTORY_ITEM_ID
248 AND BOM.ORGANIZATION_ID = SI.ORGANIZATION_ID
249 AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
250 AND BET.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
251 AND BOM.ORGANIZATION_ID = c_org_id
252 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
253 AND ( (c_std_comp_flag = 1 -- only std components
254 AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2
255 )
256 OR
257 (c_std_comp_flag = 2)
258 OR
259 (c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
260 AND (BIC.BOM_ITEM_TYPE IN (1,2)
261 OR
262 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)
263 )
264 )
265 )
266 AND ( (c_bom_or_eng = 1 and BOM.ASSEMBLY_TYPE = 1)
267 OR
268 (c_bom_or_eng = 2)
269 )
270 AND (
271 (BET.TOP_ALTERNATE_DESIGNATOR IS NULL
272 AND
273 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
274 )
275 OR
276 (BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
277 AND
278 BOM.ALTERNATE_BOM_DESIGNATOR=BET.TOP_ALTERNATE_DESIGNATOR
279 )
280 OR
281 ( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
282 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
283 AND NOT EXISTS
284 (SELECT 'X'
285 FROM BOM_BILL_OF_MATERIALS BOM2
286 WHERE BOM2.ORGANIZATION_ID = c_org_id
287 AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
288 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
289 BET.TOP_ALTERNATE_DESIGNATOR
290 AND ((c_bom_or_eng = 1 and BOM2.ASSEMBLY_TYPE = 1)
291 OR c_bom_or_eng = 2
292 )
293 ) -- subquery
294 )
295 ) -- end of alt logic
296 -- whether to include option classes and models under a standard item
297 -- special logic added at CST request
298 AND ( (c_incl_oc = 1)
299 or
300 (c_incl_oc = 2 AND
301 ( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
302 OR
303 ( BET.BOM_ITEM_TYPE <> 4)
304 )
305 )
306 -- do not explode if immediate parent is standard and current
307 -- component is option class or model - special logic for config items
308 AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4
309 AND
310 BET.BOM_ITEM_TYPE IN (1,2)
311 )
312 AND (
313 ( NVL(SI.EFFECTIVITY_CONTROL,1) = 2
314 AND ((c_explode_option = 1) -- ALL
315 OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL)
316 )
317 AND unit_number_from <=
318 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,unit_number_from)
319 AND unit_number_to >= BIC.FROM_END_ITEM_UNIT_NUMBER
320 AND BIC.FROM_END_ITEM_UNIT_NUMBER <=
321 NVL(BET.TO_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
322 AND
323 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
324 NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
325 NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
326 AND
327 ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
328 OR
329 c_impl_flag = 2 )
330 )
331 OR
332 (
333 NVL(SI.EFFECTIVITY_CONTROL,1) =1
334 AND ( (c_explode_option = 1
335 AND (c_level = 1
336 or
337 ( bic.effectivity_date <= nvl(bet.disable_date, bic.effectivity_date )
338 and nvl(bic.disable_date,bet.effectivity_date ) >= bet.effectivity_date
339 )
340 ) -- c_level Bug 4721383
341 ) -- ALL
342 OR
343 (c_explode_option = 2 AND -- CURRENT
344 c_rev_date >=
345 BIC.EFFECTIVITY_DATE AND
346 c_rev_date < -- Bug #3138456
347 nvl(BIC.DISABLE_DATE,
348 c_rev_date+1)
349 ) -- CURRENT
350 OR
351 (c_explode_option = 3 -- CURRENT AND FUTURE
352 AND nvl(BIC.DISABLE_DATE, c_rev_date + 1) > c_rev_date
353 /* Modified above line for Bug #3138456 */
354 ) -- CURRENT AND FUTURE
355 )
356 AND ( (c_impl_flag = 2 AND
357 ( c_explode_option = 1
358 OR
359 (c_explode_option = 2 AND not exists
360 (SELECT null
361 FROM BOM_INVENTORY_COMPONENTS CIB
362 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
363 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
364 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
365 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
366 CIB.OLD_COMPONENT_SEQUENCE_ID,
367 CIB.COMPONENT_SEQUENCE_ID) =
368 decode(BIC.IMPLEMENTATION_DATE, NULL,
369 BIC.OLD_COMPONENT_SEQUENCE_ID,
370 BIC.COMPONENT_SEQUENCE_ID)
371 OR
372 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
373 ) -- decode
374 AND CIB.EFFECTIVITY_DATE <=
375 c_rev_date
376 AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
377 ) -- end of subquery
378 ) -- CURRENT
379 OR
380 (c_explode_option = 3 AND not exists
384 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
381 (SELECT null
382 FROM BOM_INVENTORY_COMPONENTS CIB
383 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
385 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
386 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
387 CIB.OLD_COMPONENT_SEQUENCE_ID,
388 CIB.COMPONENT_SEQUENCE_ID) =
389 decode(BIC.IMPLEMENTATION_DATE, NULL,
390 BIC.OLD_COMPONENT_SEQUENCE_ID,
391 BIC.COMPONENT_SEQUENCE_ID)
392 OR
393 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
394 ) -- decode
395 AND CIB.EFFECTIVITY_DATE <=
396 c_rev_date
397 AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
398 ) -- end of subquery
399 OR BIC.EFFECTIVITY_DATE >
400 c_rev_date
401 ) -- CURRENT AND FUTURE
402 ) -- explode_option
403 ) -- impl_flag = 2
404 OR
405 (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
406 ) -- explode option
407 )
408 )
409 AND BET.LOOP_FLAG = 2
410 ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
411 decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
412 decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM);
413
414 Cursor Get_Locator (P_Locator in number) is
415 Select mil.concatenated_segments
416 From mtl_item_locations_kfv mil
417 Where mil.inventory_location_id = P_Locator;
418
419 Cursor Get_OLTP (P_Assembly in number,
420 P_Alternate in varchar2,
421 P_Operation in number) is
422 Select round(bos.operation_lead_time_percent, 2) oltp
423 From Bom_Operation_Sequences bos,
424 Bom_Operational_Routings bor
425 Where bor.assembly_item_id = P_Assembly
426 And bor.organization_Id = org_id
427 And (bor.alternate_routing_designator = P_Alternate
428 or
429 (bor.alternate_routing_designator is null and not exists (
430 select null
431 from bom_operational_routings bor2
432 where bor2.assembly_item_id = P_Assembly
433 and bor2.organization_id = org_id
434 and bor2.alternate_routing_designator = P_Alternate)
435 ))
436 And bor.common_routing_sequence_id = bos.routing_sequence_id
437 And bos.operation_seq_num = P_Operation
438 And bos.effectivity_date <=
439 trunc(rev_date)
440 And nvl(bos.disable_date,
441 rev_date + 1) >=
442 trunc(rev_date);
443
444 Cursor Calculate_Offset(P_ParentItem in number, P_Percent in number) is
445 Select P_Percent/100 * msi.full_lead_time offset
446 From mtl_system_items msi
447 Where msi.inventory_item_id = P_ParentItem
448 And msi.organization_id = Org_Id;
449
450 No_Revision_Found exception;
451 Pragma exception_init(no_revision_found, -20001);
452
453 Cursor l_TopBill_csr is
454 Select msi.concatenated_segments,
455 bom.alternate_bom_designator
456 From mtl_system_items_kfv msi,
457 bom_bill_of_materials bom,
458 bom_small_expl_temp bet
459 Where msi.inventory_item_id = bom.assembly_item_id
460 And msi.organization_id = bom.organization_id
461 And bom.bill_sequence_id = bet.top_bill_sequence_id
462 And bet.group_id = grp_id
463 And rownum = 1;
464 BEGIN
465
466 -- Added savepoint for bug 3863319
467 SAVEPOINT bom_exploder_pvt;
468
469 for cur_level in 1..levels_to_explode loop ----Changed from levels_to_explode+1 to levels_to_explode for Bug#7433441
470
471 total_rows := 0;
472 cum_count := 0;
473
474 --- Bulk Collect Functionality Bug 6039025 Start
475
476 -- Delete Pl/Sql Table
477
478 l_TBSI.delete;
479 l_BSI.delete;
480 l_CBSI.delete;
481 l_CID.delete;
482 l_CSI.delete;
483 l_BT.delete;
484 l_CQ.delete;
485 l_EQ.delete;
486 l_SO.delete;
487 l_TID.delete;
488 l_TAD.delete;
489 l_CYF.delete;
490 l_OI.delete;
491 l_CC.delete;
492 l_IICR.delete;
493 l_LF.delete;
494 l_PF.delete;
495 l_OSN.delete;
496 l_BIT.delete;
497 l_PBIT.delete;
498 l_PAID.delete;
499 l_WST.delete;
500 l_ITN.delete;
501 l_ED.delete;
502 l_DD.delete;
503 l_ID.delete;
504 l_FUN.delete;
505 l_EUN.delete;
506 l_OPT.delete;
507 l_SS.delete;
508 l_SLI.delete;
509 l_CR.delete;
510 l_CN.delete;
511 l_OLTP.delete;
512 l_MEO.delete;
513 l_CATP.delete;
514 l_RTS.delete;
515 l_RFR.delete;
516 l_IOSD.delete;
517 l_LQ.delete;
518 l_HQ.delete;
519 l_SB.delete;
520 l_OPERATION_OFFSET.delete;
521 l_CURRENT_REVISION.delete;
522 l_LOCATOR.delete;
523 l_ALTERNATE_BOM_DESIGNATOR.delete;
524 l_ATTRIBUTE_CATEGORY.delete;
525 l_ATTRIBUTE1.delete;
526 l_ATTRIBUTE2.delete;
527 l_ATTRIBUTE3.delete;
528 l_ATTRIBUTE4.delete;
529 l_ATTRIBUTE5.delete;
530 l_ATTRIBUTE6.delete;
531 l_ATTRIBUTE7.delete;
532 l_ATTRIBUTE8.delete;
536 l_ATTRIBUTE12.delete;
533 l_ATTRIBUTE9.delete;
534 l_ATTRIBUTE10.delete;
535 l_ATTRIBUTE11.delete;
537 l_ATTRIBUTE13.delete;
538 l_ATTRIBUTE14.delete;
539 l_ATTRIBUTE15.delete;
540
541 If not exploder%isopen then
542 OPEN exploder(
543 cur_level,
544 grp_id,
545 org_id,
546 bom_or_eng,
547 rev_date,
548 impl_flag,
549 explode_option,
550 order_by,
551 verify_flag,
552 plan_factor_flag,
553 std_comp_flag,
554 incl_oc_flag
555 );
556 End If;
557
558 FETCH exploder BULK COLLECT into
559 l_TBSI,
560 l_BSI,
561 l_CBSI,
562 l_CID,
563 l_CSI,
564 l_BT,
565 l_CQ,
566 l_EQ,
567 l_SO,
568 l_TID,
569 l_TAD,
570 l_CYF,
571 l_OI,
572 l_CC,
573 l_IICR,
574 l_LF,
575 l_PF,
576 l_OSN,
577 l_BIT,
578 l_PBIT,
579 l_PAID,
580 l_WST,
581 l_ITN,
582 l_ED,
583 l_DD,
584 l_ID,
585 l_FUN,
586 l_EUN,
587 l_OPT,
588 l_SS,
589 l_SLI,
590 l_CR,
591 l_CN,
592 l_OLTP,
593 l_MEO,
594 l_CATP,
595 l_RTS,
596 l_RFR,
597 l_IOSD,
598 l_LQ,
599 l_HQ,
600 l_SB,
601 l_OPERATION_OFFSET,
602 l_CURRENT_REVISION,
603 l_LOCATOR,
604 l_ALTERNATE_BOM_DESIGNATOR,
605 l_ATTRIBUTE_CATEGORY,
606 l_ATTRIBUTE1,
607 l_ATTRIBUTE2,
608 l_ATTRIBUTE3,
609 l_ATTRIBUTE4,
610 l_ATTRIBUTE5,
611 l_ATTRIBUTE6,
612 l_ATTRIBUTE7,
613 l_ATTRIBUTE8,
614 l_ATTRIBUTE9,
615 l_ATTRIBUTE10,
616 l_ATTRIBUTE11,
617 l_ATTRIBUTE12,
618 l_ATTRIBUTE13,
619 l_ATTRIBUTE14,
620 l_ATTRIBUTE15;
621
622 loop_count_val := exploder%rowcount;
623
624 CLOSE exploder;
625
626
627 FOR i IN 1..loop_count_val loop
628 /*
629 for expl_row in exploder (
630 cur_level,
631 grp_id,
632 org_id,
633 bom_or_eng,
634 rev_date,
635 impl_flag,
636 explode_option,
637 order_by,
638 verify_flag,
639 plan_factor_flag,
640 std_comp_flag,
641 incl_oc_flag
642 ) loop
643 */
644
645
646 if cur_level > levels_to_explode then
647 if cur_level > max_level then
648 max_level_exceeded := true;
649 end if; -- exceed max level
650 exit; -- do not insert extra level
651 end if; -- exceed lowest level
652
653 total_rows := total_rows + 1;
654 --
655 -- for very first iteration of the loop, set prevbillid = bill_id
656 --
657 if (cum_count = 0) then
658 prev_top_bill_id := l_TBSI(i);
659 prev_sort_order := l_SO(i);
660 end if;
661 --
662 -- whenever a diff assy at a particular level is being exploded, reset
663 -- the cum_count so that the sort code always starts from 001 for each
664 -- assembly
665 --
666 if ( prev_top_bill_id <> l_TBSI(i) or
667 (prev_top_bill_id = l_TBSI(i) and
668 prev_sort_order <> l_SO(i))) then
669 cum_count := 0;
670 prev_top_bill_id := l_TBSI(i);
671 prev_sort_order := l_SO(i);
672 end if;
673
674 cum_count := cum_count + 1;
675 --
676 -- lpad cat_sort with 0s upto 7 characters
677 --
678 cat_sort := lpad(to_char(cum_count), G_SortWidth, '0');
679
680
681 l_SO(i) := l_SO(i) || cat_sort;
682
683 -- SQL has been modified to carry the loopstr and loopflag
684 loop_found := FALSE;
685 cur_loopstr := l_CC(i);
686 cur_component := LPAD( TO_CHAR( l_CID(i) ), 16, '0' );
687
688 -- search the current loop_string for current component
689 FOR i IN 1..max_level LOOP
690 start_pos := 1+( (i-1) * 16 );
691 cur_substr := SUBSTR( cur_loopstr, start_pos, 16 );
692 if (cur_component = cur_substr) then
693 loop_found := TRUE;
694 EXIT;
695 end if;
696 END LOOP;
697
698 -- deal with the search results
699 l_CC(i) :=
700 l_CC(i) || cur_component;
701 if loop_found then
702 l_LF(i) := 1;
703 else
704 l_LF(i) := 2;
705 end if;
706
707 l_Current_Revision(i) := Null;
708 If show_rev = 1 then
709 Begin
710 /***Added as fix for 1036465 *******/
711 if impl_flag = 1 then
712 impl_eco := 'IMPL_ONLY';
713 else
714 impl_eco := 'ALL';
715 end if;
716 /*************************************/
717 Bom_Revisions.Get_Revision(
718 type => 'PART',
719 eco_status => 'ALL',
720 -- examine_type => 'IMPL_ONLY',
721 examine_type => impl_eco,
725 itm_rev => l_Current_Revision(i));
722 org_id => l_OI(i),
723 item_id => l_CID(i),
724 rev_date => rev_date,
726 Exception
727 When no_revision_found then
728 null;
729 End; -- nested block
730 End if; -- current component revision
731
732 l_Locator(i) := Null;
733
734 If material_ctrl = 1 then
735 IF FND_FLEX_KEYVAL.validate_ccid
736 (appl_short_name => 'INV',
737 key_flex_code => 'MTLL',
738 structure_number => 101,
739 combination_id => l_SLI(i),
740 displayable => 'ALL',
741 data_set => l_OI(i)
742 )
743 THEN
744 l_Locator(i) := FND_FLEX_KEYVAL.concatenated_values ;
745 End if;
746 /* Commented after bug fix 1252837. New code added above
747 For X_Location in Get_Locator(expl_row.SLI) loop
748 Expl_Row.Locator := X_Location.Concatenated_Segments;
749 End loop;
750 */
751 End if; -- supply locator
752
753 l_OLTP(i) := Null;
754 For X_Operation in Get_OLTP(
755 P_Assembly => l_PAID(i),
756 P_Alternate => l_alternate_bom_designator(i),
757 P_Operation => l_OSN(i)) loop
758 l_OLTP(i) := X_Operation.OLTP;
759 End loop;
760
761 l_Operation_Offset(i) := Null;
762 If lead_time = 1 then
763 For X_Item in Calculate_Offset(P_ParentItem => l_PAID(i),
764 P_Percent => l_OLTP(i)) loop
765 l_Operation_Offset(i) := X_Item.offset;
766 End loop;
767 End if; -- operation offset
768 -- Inserting the Flex field values ATTRIBUTE1-ATTRIBUTE15 also in the
769 -- BOM_SMALL_EXPL_TEMP table #1409015.
770
771 end loop; -- cursor fetch loop
772
773 FORALL i IN 1..loop_count_val
774
775 INSERT INTO BOM_SMALL_EXPL_TEMP (
776 TOP_BILL_SEQUENCE_ID,
777 BILL_SEQUENCE_ID,
778 COMMON_BILL_SEQUENCE_ID,
779 ORGANIZATION_ID,
780 COMPONENT_SEQUENCE_ID,
781 COMPONENT_ITEM_ID,
782 BASIS_TYPE,
783 COMPONENT_QUANTITY,
784 PLAN_LEVEL,
785 EXTENDED_QUANTITY,
786 SORT_ORDER,
787 GROUP_ID,
788 TOP_ALTERNATE_DESIGNATOR,
789 COMPONENT_YIELD_FACTOR,
790 TOP_ITEM_ID,
791 COMPONENT_CODE,
792 INCLUDE_IN_ROLLUP_FLAG,
793 LOOP_FLAG,
794 PLANNING_FACTOR,
795 OPERATION_SEQ_NUM,
796 BOM_ITEM_TYPE,
797 PARENT_BOM_ITEM_TYPE,
798 ASSEMBLY_ITEM_ID,
799 WIP_SUPPLY_TYPE,
800 ITEM_NUM,
801 EFFECTIVITY_DATE,
802 DISABLE_DATE,
803 FROM_END_ITEM_UNIT_NUMBER,
804 TO_END_ITEM_UNIT_NUMBER,
805 IMPLEMENTATION_DATE,
806 OPTIONAL,
807 SUPPLY_SUBINVENTORY,
808 SUPPLY_LOCATOR_ID,
809 COMPONENT_REMARKS,
810 CHANGE_NOTICE,
811 OPERATION_LEAD_TIME_PERCENT,
812 MUTUALLY_EXCLUSIVE_OPTIONS,
813 CHECK_ATP,
814 REQUIRED_TO_SHIP,
815 REQUIRED_FOR_REVENUE,
816 INCLUDE_ON_SHIP_DOCS,
817 LOW_QUANTITY,
818 HIGH_QUANTITY,
819 SO_BASIS,
820 OPERATION_OFFSET,
821 CURRENT_REVISION,
822 LOCATOR,
823 CONTEXT,
824 ATTRIBUTE1,
825 ATTRIBUTE2,
826 ATTRIBUTE3,
827 ATTRIBUTE4,
828 ATTRIBUTE5,
829 ATTRIBUTE6,
830 ATTRIBUTE7,
831 ATTRIBUTE8,
832 ATTRIBUTE9,
833 ATTRIBUTE10,
834 ATTRIBUTE11,
835 ATTRIBUTE12,
836 ATTRIBUTE13,
837 ATTRIBUTE14,
838 ATTRIBUTE15
839 ) VALUES (
840 l_TBSI(i),
841 l_BSI(i),
842 l_CBSI(i),
843 l_OI(i),
844 l_CSI(i),
845 l_CID(i),
846 l_BT(i),
847 l_CQ(i),
848 cur_level,
849 l_EQ(i),
850 l_SO(i),
851 grp_id,
852 l_TAD(i),
853 l_CYF(i),
854 l_TID(i),
855 l_CC(i),
856 l_IICR(i),
857 l_LF(i),
858 l_PF(i),
859 l_OSN(i),
860 l_BIT(i),
861 l_PBIT(i),
862 l_PAID(i),
863 l_WST(i),
864 l_ITN(i),
865 l_ED(i),
866 l_DD(i),
867 l_FUN(i),
868 l_EUN(i),
869 l_ID(i),
870 l_OPT(i),
871 l_SS(i),
872 l_SLI(i),
873 l_CR(i),
874 l_CN(i),
875 l_OLTP(i),
876 l_MEO(i),
877 l_CATP(i),
878 l_RTS(i),
879 l_RFR(i),
880 l_IOSD(i),
881 l_LQ(i),
882 l_HQ(i),
883 l_SB(i),
884 l_OPERATION_OFFSET(i),
885 l_CURRENT_REVISION(i),
886 l_LOCATOR(i),
887 l_ATTRIBUTE_CATEGORY(i),
888 l_ATTRIBUTE1(i),
889 l_ATTRIBUTE2(i),
890 l_ATTRIBUTE3(i),
891 l_ATTRIBUTE4(i),
892 l_ATTRIBUTE5(i),
893 l_ATTRIBUTE6(i),
894 l_ATTRIBUTE7(i),
895 l_ATTRIBUTE8(i),
896 l_ATTRIBUTE9(i),
897 l_ATTRIBUTE10(i),
898 l_ATTRIBUTE11(i),
899 l_ATTRIBUTE12(i),
900 l_ATTRIBUTE13(i),
901 l_ATTRIBUTE14(i),
902 l_ATTRIBUTE15(i)
903 );
904
905 --
906 -- if total rows fetched is 0, then break the loop here since nothing
907 -- more to explode
908 --
909
910 if total_rows = 0 then
911 exit;
912 end if;
913
914
915 END LOOP; -- while level
916
917 if max_level_exceeded then
918 error_code := 9998;
919 Fnd_Message.Set_Name('BOM', 'BOM_LEVELS_EXCEEDED');
920 For l_bill_rec in l_TopBill_csr loop
921 Fnd_Message.Set_Token('ENTITY', l_bill_rec.concatenated_segments);
922 Fnd_Message.Set_Token('ENTITY1', l_bill_rec.concatenated_segments);
923 Fnd_Message.Set_Token('ENTITY2', l_bill_rec.alternate_bom_designator);
924 End loop;
925 err_msg := Fnd_Message.Get_Encoded;
926 else
927 error_code := 0;
928 err_msg := null;
929 end if;
930
931 EXCEPTION
932 WHEN OTHERS THEN
933 error_code := SQLCODE;
934 Fnd_Msg_Pub.Build_Exc_Msg(
935 p_pkg_name => 'BOMPBXIN',
936 p_procedure_name => 'BOM_EXPLODER',
937 p_error_text => SQLERRM);
938 err_msg := Fnd_Message.Get_Encoded;
939 ROLLBACK TO bom_exploder_pvt; -- bug 3863319
940 END bom_exploder;
941
942 END BOMPBXIN;