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