[Home] [Help]
PACKAGE BODY: APPS.BOM_OE_EXPLODER_PKG
Source
4 /*==========================================================================+
1 package body bom_oe_exploder_pkg as
2 /* $Header: BOMORXPB.pls 120.12.12020000.2 2012/12/13 12:19:23 ntungare ship $ */
3
5 | Copyright (c) 1996 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMOEXPB.pls |
10 | DESCRIPTION : This file is the body for a packaged procedure for the
11 | custom bom exploder for use by Order Entry. It creates
12 | a time independent 'OPTIONAL' or 'INCLUDED' or 'ALL' bom
13 | for the given item in the BOM_EXPLOSIONS table.
14 |
15 | Parameters: arg_org_id organization_id
16 | arg_starting_rev_date
17 | arg_expl_type 'OPTIONAL' or 'INCLUDED' or 'ALL'
18 | arg_order_by 1 - Op seq, item seq
19 | 2 - Item seq, op seq
20 | arg_levels_to_explode
21 | arg_item_id item id of asembly to explode
22 | arg_user_id user id
23 | arg_comp_code concatenated component code (not used)
24 | arg_err_msg error message out buffer
25 | arg_error_code error code out. returns sql error code
26 | if sql error, 9999 if loop detected.
27 | Revision
28 | 13-SEP-95 Raj Jain Creation
29 | 26-SEP-95 Raj Jain Split .sql into spec and body files
30 | 04-JAN-96 Rob Yee Include 'ALL' explosion type for zoom to
31 | Configurator from Bill form
32 | 22-MAR-96 Rob Yee Filter strictly negative quantities
33 | 02-JAN-97 Rob Yee Correct Loop Check
34 | 03-MAR-03 Sangeetha Mani Added bulk insert and bulk fetch improvement
35 | for better performance. Rows are fetched in
36 | batches of 1000.
37 | 15-Sep-03 Rahul Chitko Modified the for inserting and selecting
38 | data for all parents at a given level.
39 | This will reduce the overall number of selects
40 | Added procedure Generate_Sort_Order which will
41 | help in generating the sort_order for every batch
42 | of 1000 rows.
43 | 15-Sep-03 Rahul Chitko Deletes are moved into an Autonomous block.
44 | |
45 +==========================================================================*/
46
47 /* Package Globals */
48 /* Type and Table definition that can be reused in the package without having to pass them around */
49
50 -- Bug 2822347
51
52 TYPE number_tab_tp IS TABLE OF NUMBER
53 INDEX BY BINARY_INTEGER;
54
55 TYPE date_tab_tp IS TABLE OF DATE
56 INDEX BY BINARY_INTEGER;
57
58 TYPE varchar_tab_1 IS TABLE OF VARCHAR2(1)
59 INDEX BY BINARY_INTEGER;
60
61 TYPE varchar_tab_3 IS TABLE OF VARCHAR2(3)
62 INDEX BY BINARY_INTEGER;
63
64 TYPE varchar_tab_10 IS TABLE OF VARCHAR2(10)
65 INDEX BY BINARY_INTEGER;
66
67 TYPE varchar_tab_20 IS TABLE OF VARCHAR2(20)
68 INDEX BY BINARY_INTEGER;
69
70 TYPE varchar_tab_25 IS TABLE OF VARCHAR2(25)
71 INDEX BY BINARY_INTEGER;
72
73 TYPE varchar_tab_30 IS TABLE OF VARCHAR2(30)
74 INDEX BY BINARY_INTEGER;
75
76 TYPE varchar_tab_150 IS TABLE OF VARCHAR2(150)
77 INDEX BY BINARY_INTEGER;
78
79 TYPE varchar_tab_2000 IS TABLE OF VARCHAR2(2000)
80 INDEX BY BINARY_INTEGER;
81
82 TYPE varchar_tab_1000 IS TABLE OF VARCHAR2(1000)
83 INDEX BY BINARY_INTEGER;
84
85 OB_TOP_BILL_SEQUENCE_ID number_tab_tp;
86 OB_BILL_SEQUENCE_ID number_tab_tp;
87 OB_ORGANIZATION_ID number_tab_tp;
88 OB_EXPLOSION_TYPE varchar_tab_20;
89 OB_COMPONENT_SEQUENCE_ID number_tab_tp;
90 OB_COMPONENT_ITEM_ID number_tab_tp;
91 OB_PLAN_LEVEL number_tab_tp;
92 OB_EXTENDED_QUANTITY number_tab_tp;
93 OB_SORT_ORDER varchar_tab_2000;
94 OB_CREATION_DATE date_tab_tp;
95 OB_CREATED_BY number_tab_tp;
96 OB_LAST_UPDATE_DATE date_tab_tp;
97 OB_LAST_UPDATED_BY number_tab_tp;
98 OB_TOP_ITEM_ID number_tab_tp;
99 OB_ATTRIBUTE1 varchar_tab_150;
100 OB_ATTRIBUTE2 varchar_tab_150;
101 OB_ATTRIBUTE3 varchar_tab_150;
102 OB_ATTRIBUTE4 varchar_tab_150;
103 OB_ATTRIBUTE5 varchar_tab_150;
104 OB_ATTRIBUTE6 varchar_tab_150;
105 OB_ATTRIBUTE7 varchar_tab_150;
106 OB_ATTRIBUTE8 varchar_tab_150;
107 OB_ATTRIBUTE9 varchar_tab_150;
108 OB_ATTRIBUTE10 varchar_tab_150;
109 OB_ATTRIBUTE11 varchar_tab_150;
110 OB_ATTRIBUTE12 varchar_tab_150;
111 OB_ATTRIBUTE13 varchar_tab_150;
115 OB_COMPONENT_QUANTITY number_tab_tp;
112 OB_ATTRIBUTE14 varchar_tab_150;
113 OB_ATTRIBUTE15 varchar_tab_150;
114 OB_BASIS_TYPE number_tab_tp;
116 OB_SO_BASIS number_tab_tp;
117 OB_OPTIONAL number_tab_tp;
118 OB_MUTUALLY_EXCLUSIVE_OPTIONS number_tab_tp;
119 OB_CHECK_ATP number_tab_tp;
120 OB_SHIPPING_ALLOWED number_tab_tp;
121 OB_REQUIRED_TO_SHIP number_tab_tp;
122 OB_REQUIRED_FOR_REVENUE number_tab_tp;
123 OB_INCLUDE_ON_SHIP_DOCS number_tab_tp;
124 OB_INCLUDE_ON_BILL_DOCS number_tab_tp;
125 OB_LOW_QUANTITY number_tab_tp;
126 OB_HIGH_QUANTITY number_tab_tp;
127 OB_PICK_COMPONENTS number_tab_tp;
128 OB_PRIMARY_UOM_CODE varchar_tab_3;
129 OB_PRIMARY_UNIT_OF_MEASURE varchar_tab_25;
130 OB_BASE_ITEM_ID number_tab_tp;
131 OB_ATP_COMPONENTS_FLAG varchar_tab_1;
132 OB_ATP_FLAG varchar_tab_1;
133 OB_BOM_ITEM_TYPE number_tab_tp;
134 OB_PICK_COMPONENTS_FLAG varchar_tab_1;
135 OB_REPLENISH_TO_ORDER_FLAG varchar_tab_1;
136 OB_SHIPPABLE_ITEM_FLAG varchar_tab_1;
137 OB_CUSTOMER_ORDER_FLAG varchar_tab_1;
138 OB_INTERNAL_ORDER_FLAG varchar_tab_1;
139 OB_CUSTOMER_ORDER_ENABLED_FLAG varchar_tab_1;
140 OB_INTERNAL_ORDER_ENABLED_FLAG varchar_tab_1;
141 OB_SO_TRANSACTIONS_FLAG varchar_tab_1;
142 OB_DESCRIPTION varchar_tab_2000;
143 OB_ASSEMBLY_ITEM_ID number_tab_tp;
144 OB_COMPONENT_CODE varchar_tab_1000;
145 OB_LOOP_FLAG number_tab_tp;
146 OB_PARENT_BOM_ITEM_TYPE number_tab_tp;
147 OB_OPERATION_SEQ_NUM number_tab_tp;
148 OB_ITEM_NUM number_tab_tp;
149 OB_EFFECTIVITY_DATE date_tab_tp;
150 OB_DISABLE_DATE date_tab_tp;
151 OB_IMPLEMENTATION_DATE date_tab_tp;
152 OB_REXPLODE_FLAG number_tab_tp;
153 OB_COMMON_BILL_SEQUENCE_ID number_tab_tp;
154 OB_COMP_BILL_SEQ_ID number_tab_tp;
155 OB_COMP_COMMON_BILL_SEQ_ID number_tab_tp;
156 OB_AUTO_REQUEST_MATERIAL varchar_tab_1;
157 OB_SOURCE_BILL_SEQUENCE_ID number_tab_tp;
158 OB_COMMON_COMPONENT_SEQ_ID number_tab_tp;
159 OB_COMP_SOURCE_BILL_SEQ_ID number_tab_tp;
160
161 X_SortWidth CONSTANT NUMBER := Bom_Common_Definitions.G_Bom_SortCode_Width ; -- at most 9999999 components
162 G_MAX_BATCH_FETCH_SIZE CONSTANT NUMBER := 10000;
163
164 /*
165 ** Procedure: Empty_Sql_Tables
166 ** Purpose: Local procedure, Used only for cleaning up the pl/sql tables
167 ** Every iteration of the loop that selects the data will need to cleanup before
168 ** appending.
169 */
170 PROCEDURE Empty_Sql_Tables
171 AS
172 BEGIN
173 -- Delete pl/sql table Bug 2822347
174 OB_TOP_BILL_SEQUENCE_ID.delete;
175 OB_BILL_SEQUENCE_ID.delete;
176 OB_ORGANIZATION_ID.delete ;
177 OB_EXPLOSION_TYPE.delete;
178 OB_COMPONENT_SEQUENCE_ID.delete;
179 OB_COMPONENT_ITEM_ID.delete;
180 OB_PLAN_LEVEL.delete;
181 OB_EXTENDED_QUANTITY.delete;
182 OB_SORT_ORDER.delete;
183 OB_CREATION_DATE.delete;
184 OB_CREATED_BY.delete;
185 OB_LAST_UPDATE_DATE.delete;
186 OB_LAST_UPDATED_BY.delete;
187 OB_TOP_ITEM_ID.delete;
188 OB_ATTRIBUTE1.delete;
189 OB_ATTRIBUTE2.delete;
190 OB_ATTRIBUTE3.delete;
191 OB_ATTRIBUTE4.delete;
192 OB_ATTRIBUTE5.delete;
193 OB_ATTRIBUTE6.delete;
194 OB_ATTRIBUTE7.delete;
195 OB_ATTRIBUTE8.delete;
196 OB_ATTRIBUTE9.delete;
197 OB_ATTRIBUTE10.delete;
198 OB_ATTRIBUTE11.delete;
199 OB_ATTRIBUTE12.delete;
200 OB_ATTRIBUTE13.delete;
201 OB_ATTRIBUTE14.delete;
202 OB_ATTRIBUTE15.delete;
203 OB_BASIS_TYPE.delete;
204 OB_COMPONENT_QUANTITY.delete;
205 OB_SO_BASIS.delete;
206 OB_OPTIONAL.delete;
207 OB_MUTUALLY_EXCLUSIVE_OPTIONS.delete;
208 OB_CHECK_ATP.delete;
209 OB_SHIPPING_ALLOWED.delete;
210 OB_REQUIRED_TO_SHIP.delete;
211 OB_REQUIRED_FOR_REVENUE.delete;
212 OB_INCLUDE_ON_SHIP_DOCS.delete;
213 OB_INCLUDE_ON_BILL_DOCS.delete;
214 OB_LOW_QUANTITY.delete;
215 OB_HIGH_QUANTITY.delete;
216 OB_PICK_COMPONENTS.delete;
217 OB_PRIMARY_UOM_CODE.delete;
218 OB_PRIMARY_UNIT_OF_MEASURE.delete;
219 OB_BASE_ITEM_ID.delete;
220 OB_ATP_COMPONENTS_FLAG.delete;
221 OB_ATP_FLAG.delete;
222 OB_BOM_ITEM_TYPE.delete;
223 OB_PICK_COMPONENTS_FLAG.delete;
224 OB_REPLENISH_TO_ORDER_FLAG.delete;
225 OB_SHIPPABLE_ITEM_FLAG.delete;
226 OB_CUSTOMER_ORDER_FLAG.delete;
227 OB_INTERNAL_ORDER_FLAG.delete;
228 OB_CUSTOMER_ORDER_ENABLED_FLAG.delete;
229 OB_INTERNAL_ORDER_ENABLED_FLAG.delete;
230 OB_SO_TRANSACTIONS_FLAG.delete;
231 OB_DESCRIPTION.delete;
232 OB_ASSEMBLY_ITEM_ID.delete;
233 OB_COMPONENT_CODE.delete;
234 OB_LOOP_FLAG.delete;
235 OB_PARENT_BOM_ITEM_TYPE.delete;
236 OB_OPERATION_SEQ_NUM.delete;
237 OB_ITEM_NUM.delete;
238 OB_EFFECTIVITY_DATE.delete;
239 OB_DISABLE_DATE.delete;
240 OB_IMPLEMENTATION_DATE.delete;
241 OB_REXPLODE_FLAG.delete;
242 OB_COMMON_BILL_SEQUENCE_ID.delete;
243 OB_COMP_BILL_SEQ_ID.delete;
244 OB_COMP_COMMON_BILL_SEQ_ID.delete;
245 OB_AUTO_REQUEST_MATERIAL.delete;
246 OB_SOURCE_BILL_SEQUENCE_ID.delete;
247 OB_COMMON_COMPONENT_SEQ_ID.delete;
248 OB_COMP_SOURCE_BILL_SEQ_ID.delete;
249
250 END Empty_Sql_Tables;
251
252 /*
253 ** Procedure: DELETE_EXPL_BILL
254 ** Purpose: Local procedure used for deleting records from the explosion table
255 belonging to the same parent
256 */
257 Procedure DELETE_EXPL_BILL(top_bill_id Number,
258 arg_expl_type Varchar2)
259 IS
260 pragma AUTONOMOUS_TRANSACTION;
261
262 CURSOR c_rows_to_delete IS
263 select sort_order
264 from bom_explosions
265 where top_bill_sequence_id = top_bill_id
269
266 and explosion_type = arg_expl_type
267 and rexplode_flag = 1;
268 BEGIN
270 for parent in c_rows_to_delete
271 loop
272 DELETE from bom_explosions
273 WHERE top_bill_sequence_id = top_bill_id
274 AND explosion_type = arg_expl_type
275 AND sort_order like parent.sort_order || '%'
276 AND sort_order <> parent.sort_order;
277 commit;
278 end loop;
279 END Delete_Expl_Bill;
280
281 /*
282 ** Procedure: Generate_Sort_Order
283 ** Purpose: Local procedure used for generating the sort_order for a node within the pl/sql table
284 */
285 PROCEDURE Generate_Sort_Order
286 AS
287 l_parent_id Number;
288 l_parent_sort_order VARCHAR2(2000);
289 x_sort_counter Number;
290 BEGIN
291 IF ob_sort_order.count > 0
292 THEN
293 l_parent_id := OB_BILL_SEQUENCE_ID(1);
294 l_parent_sort_order := ob_sort_order(1);
295 ELSE
296 return;
297 END IF;
298
299 /* When starting the number generation, fetch the count and then proceed
300 This is because the fetch size is 1000 and the sort_order should be unique.
301 */
302 SELECT count(bill_sequence_id)
303 INTO x_sort_counter
304 FROM bom_explosions
305 WHERE top_bill_sequence_id = OB_TOP_BILL_SEQUENCE_ID(1)
306 AND bill_sequence_id = OB_BILL_SEQUENCE_ID(1)
307 AND ( sort_order like OB_SORT_ORDER(1)||'%' AND
308 sort_order <> OB_SORT_ORDER(1)
309 )
310 AND explosion_type = OB_EXPLOSION_TYPE(1);
311
312 FOR l_node_count in 1..ob_sort_order.count
313 LOOP
314 IF ( l_parent_sort_order <> ob_sort_order(l_node_count)
315 )
316 THEN
317 x_sort_counter := 1;
318 l_parent_id := OB_BILL_SEQUENCE_ID(l_node_count);
319 l_parent_sort_order := ob_sort_order(l_node_count);
320 ELSE
321 x_sort_counter := x_sort_counter + 1;
322 END IF;
323 ob_sort_order(l_node_count) := ob_sort_order(l_node_count) ||
324 lpad(to_char(x_sort_counter), X_SortWidth, '0');
325 END LOOP;
326 END Generate_Sort_Order;
327
328
329 /*
330 ** Procedure: Be_Exploder
331 ** Purpose : This is the driving procedure for the Explosion
332 ** External applications requiring data from explosion table will first invoke
333 ** this procedure before selecting directly from the table.
334 */
335
336 procedure be_exploder (
337 arg_org_id IN NUMBER,
338 arg_starting_rev_date IN DATE,
339 arg_expl_type IN VARCHAR2 DEFAULT 'OPTIONAL',
340 arg_order_by IN NUMBER DEFAULT 1,
341 arg_levels_to_explode IN NUMBER DEFAULT 20,
342 arg_item_id IN NUMBER,
343 arg_comp_code IN VARCHAR2 DEFAULT '',
344 arg_user_id IN NUMBER DEFAULT 0,
345 arg_err_msg OUT NOCOPY VARCHAR2,
346 arg_error_code OUT NOCOPY NUMBER,
347 arg_alt_bom_desig IN VARCHAR2
348 ) IS
349
350 x_expl_qty NUMBER := 1;
351 stmt_num NUMBER := 0;
352
353 x_no_top_assy EXCEPTION;
354 x_loop_detected EXCEPTION;
355 x_bom_expl_del EXCEPTION;
356 x_bom_expl_run EXCEPTION; /* Fix for bug 9198518-added this exception */
357
358 x_top_bill_id NUMBER;
359 x_top_common_bill_id NUMBER;
360 x_top_source_bill_id NUMBER;
361
362 x_sort_counter NUMBER := 0;
363 x_req_id NUMBER := 0;
364 x_delete_bom_expl NUMBER := 2;
365
366 x_yes constant number := 1;
367 x_no constant number := 2;
368 bill_exists Number := 0;
369 l_count number := 0; -- Added to fix bug #8496032
370 update_exp EXCEPTION; -- Added to fix bug #8496032
371
372 CURSOR get_bill_id IS
373 SELECT bill_sequence_id,
374 common_bill_sequence_id,
375 source_bill_sequence_id
376 FROM bom_bill_of_materials
377 WHERE assembly_item_id = arg_item_id
378 AND organization_id = arg_org_id
379 AND NVL(alternate_bom_designator,'NONE') = NVL(arg_alt_bom_desig,'NONE');
380
381 CURSOR bom_expl(top_bill_id Number) IS
382 Select REQUEST_ID
383 FROM BOM_EXPLOSIONS
384 WHERE top_bill_sequence_id = top_bill_id
385 AND explosion_type = arg_expl_type;
386 -- bug 15961704
387 --AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode;
388
389 Cursor GetExplodeFlags is
390 Select rowid,
391 plan_level,
392 sort_order
393 From bom_explosions
394 Where rexplode_flag = 1
395 And top_bill_sequence_id = x_top_bill_id
396 And explosion_type = arg_expl_type
397 Order by plan_level;
398
399 X_MoreLevels boolean := true; -- more levels to explode
400 X_FirstLevel number; -- first level needing re-explosion
401
402 Cursor ordered_bill(p_plan_level number) IS
403 SELECT /*+ LEADING (BE) */
404 x_top_bill_id TOP_BILL_SEQUENCE_ID,
405 BOM.BILL_SEQUENCE_ID,
406 BOM.ORGANIZATION_ID,
407 arg_expl_type EXPLOSION_TYPE,
408 BIC.COMPONENT_SEQUENCE_ID,
409 BIC.COMPONENT_ITEM_ID,
410 BE.PLAN_LEVEL + 1 PLAN_LEVEL,
411 decode(BIC.BASIS_TYPE, null, BE.EXTENDED_QUANTITY,1) * BIC.COMPONENT_QUANTITY EXTENDED_QUANTITY,
412 BE.SORT_ORDER,
413 sysdate CREATION_DATE,
414 arg_user_id CREATED_BY,
415 sysdate LAST_UPDATE_DATE,
416 arg_user_id LAST_UPDATED_BY,
417 BE.TOP_ITEM_ID,
418 BIC.ATTRIBUTE1,
419 BIC.ATTRIBUTE2,
420 BIC.ATTRIBUTE3,
421 BIC.ATTRIBUTE4,
422 BIC.ATTRIBUTE5,
423 BIC.ATTRIBUTE6,
427 BIC.ATTRIBUTE10,
424 BIC.ATTRIBUTE7,
425 BIC.ATTRIBUTE8,
426 BIC.ATTRIBUTE9,
428 BIC.ATTRIBUTE11,
429 BIC.ATTRIBUTE12,
430 BIC.ATTRIBUTE13,
431 BIC.ATTRIBUTE14,
432 BIC.ATTRIBUTE15,
433 BIC.BASIS_TYPE,
434 BIC.COMPONENT_QUANTITY,
435 BIC.SO_BASIS,
436 BIC.OPTIONAL,
437 BIC.MUTUALLY_EXCLUSIVE_OPTIONS,
438 BIC.CHECK_ATP,
439 BIC.SHIPPING_ALLOWED,
440 BIC.REQUIRED_TO_SHIP,
441 BIC.REQUIRED_FOR_REVENUE,
442 BIC.INCLUDE_ON_SHIP_DOCS,
443 BIC.INCLUDE_ON_BILL_DOCS,
444 BIC.LOW_QUANTITY,
445 BIC.HIGH_QUANTITY,
446 BIC.PICK_COMPONENTS,
447 MSI.PRIMARY_UOM_CODE,
448 MSI.PRIMARY_UNIT_OF_MEASURE,
449 MSI.BASE_ITEM_ID,
450 MSI.ATP_COMPONENTS_FLAG,
451 MSI.ATP_FLAG,
452 MSI.BOM_ITEM_TYPE,
453 MSI.PICK_COMPONENTS_FLAG,
454 MSI.REPLENISH_TO_ORDER_FLAG,
455 MSI.SHIPPABLE_ITEM_FLAG,
456 MSI.CUSTOMER_ORDER_FLAG,
457 MSI.INTERNAL_ORDER_FLAG,
458 MSI.CUSTOMER_ORDER_ENABLED_FLAG,
459 MSI.INTERNAL_ORDER_ENABLED_FLAG,
460 MSI.SO_TRANSACTIONS_FLAG,
461 MSITL.DESCRIPTION,
462 BOM.ASSEMBLY_ITEM_ID,
463 BE.COMPONENT_CODE,
464 BE.LOOP_FLAG,
465 BE.BOM_ITEM_TYPE PARENT_BOM_ITEM_TYPE,
466 BIC.OPERATION_SEQ_NUM,
467 BIC.ITEM_NUM,
468 GREATEST(BE.EFFECTIVITY_DATE, BIC.EFFECTIVITY_DATE) EFFECTIVITY_DATE,
469 LEAST(BE.DISABLE_DATE, NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) DISABLE_DATE,
470 BIC.IMPLEMENTATION_DATE,
471 1 REXPLODE_FLAG,
472 BOM.COMMON_BILL_SEQUENCE_ID,
473 BBOM_C.BILL_SEQUENCE_ID COMP_BILL_SEQ_ID,
474 BBOM_C.COMMON_BILL_SEQUENCE_ID COMP_COMMON_BILL_SEQ_ID,
475 -- chrng: added auto_request_material
476 BIC.AUTO_REQUEST_MATERIAL,
477 BOM.SOURCE_BILL_SEQUENCE_ID,
478 BIC.COMMON_COMPONENT_SEQUENCE_ID,
479 BBOM_C.SOURCE_BILL_SEQUENCE_ID COMP_SOURCE_BILL_SEQ_ID
480 FROM
481 BOM_STRUCTURES_B BBOM_C,
482 MTL_SYSTEM_ITEMS MSI,
483 MTL_SYSTEM_ITEMS_TL MSITL,
484 BOM_COMPONENTS_B BIC,
485 BOM_STRUCTURES_B BOM,
486 BOM_EXPLOSIONS BE
487 -- FP bug fix for 12.1.1. The bug # is 7307613.
488 -- Fixed by Minling on 10/15/08.
489 -- Changed the WHERE condition to improve performance of the query.
490 WHERE ( ( BBOM_C.obj_name IS NULL AND fnd_global.RESP_APPL_ID = 431 )
491 OR ( BBOM_C.obj_name IS NULL AND fnd_global.RESP_APPL_ID = -1 )
492 OR ( BBOM_C.obj_name is null and fnd_global.RESP_APPL_ID <> 431 and nvl(BBOM_C.effectivity_control,1) <= 3 ) )
493 AND ( ( BOM.obj_name IS NULL AND fnd_global.RESP_APPL_ID = 431 )
494 OR ( BOM.obj_name IS NULL AND fnd_global.RESP_APPL_ID = -1 )
495 OR ( BOM.obj_name is null and fnd_global.RESP_APPL_ID <> 431 and nvl(BOM.effectivity_control,1) <= 3 ) )
496 AND BE.TOP_BILL_SEQUENCE_ID = x_top_bill_id
497 -- END of bug fix 7307613.
498
499
500 AND BE.EXPLOSION_TYPE = arg_expl_type
501 --AND BE.SORT_ORDER = P_Parent
502 AND nvl(BBOM_C.effectivity_control,1) <= 3
503 AND
504 (
505 BBOM_C.obj_name is null
506 OR BBOM_C.obj_name = 'EGO_ITEM'
507 )
508 AND
509 (
510 BOM.obj_name is null
511 OR BOM.obj_name = 'EGO_ITEM'
512 )
513 AND NVL(BOM.effectivity_control,1) <= 3 --Bug 7444587(7450613,7450614)
514 AND BIC.overlapping_changes is null
515 AND
516 (
517 BIC.obj_name is null
518 OR BIC.obj_name = 'EGO_ITEM'
519 )
520 AND BE.rexplode_flag = 1
521 AND BE.PLAN_LEVEL = p_plan_level
522 AND BOM.ORGANIZATION_ID = BE.ORGANIZATION_ID
523 AND BOM.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID
524 AND (
525 ( arg_alt_bom_desig IS NULL
526 AND
527 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
528 )
529 OR
530 (arg_alt_bom_desig IS NOT NULL
531 AND
532 BOM.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
533 AND
534 BOM.ALTERNATE_BOM_DESIGNATOR=arg_alt_bom_desig
535 )
536 OR
537 ( arg_alt_bom_desig IS NOT NULL
538 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
539 AND NOT EXISTS
540 (SELECT 'X'
541 FROM BOM_BILL_OF_MATERIALS BOM2
542 WHERE BOM2.ORGANIZATION_ID = arg_org_id
543 AND BOM2.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID
544 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
545 arg_alt_bom_desig
546 AND BOM2.ASSEMBLY_TYPE = 1
547 ) -- subquery
548 )
549 ) -- end of alt logic
550 AND BIC.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
551 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
552 AND BIC.IMPLEMENTATION_DATE IS NOT NULL
553 AND BIC.COMPONENT_QUANTITY >= 0
554 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
555 AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
556 AND MSITL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
557 AND MSITL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
558 AND MSITL.LANGUAGE = USERENV('LANG')
559 AND ( (arg_expl_type = 'OPTIONAL'
560 AND BE.BOM_ITEM_TYPE in (1,2) -- parent is a model or opt class
561 AND (BIC.BOM_ITEM_TYPE IN (1,2) OR -- comp is a model or opt class
562 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)))
563 -- comp is an optional standard item
564 OR (arg_expl_type = 'INCLUDED'
565 AND BE.PICK_COMPONENTS_FLAG = 'Y' -- parent is PTO
566 AND BIC.BOM_ITEM_TYPE = 4 -- comp is a mandatory standard item
567 AND BIC.OPTIONAL = 2)
568 OR (arg_expl_type not in ('OPTIONAL', 'INCLUDED')) -- both
569 )
570 AND ( (BE.BASE_ITEM_ID IS NOT NULL AND
571 BIC.BOM_ITEM_TYPE NOT IN (1,2)
572 )
573 OR
574 BE.BASE_ITEM_ID IS NULL
578 GREATEST(BE.EFFECTIVITY_DATE, BIC.EFFECTIVITY_DATE)
575 ) /* Added for bug 3531716*/
576 AND BOM.ASSEMBLY_TYPE = 1
577 AND LEAST(BE.DISABLE_DATE, NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) >=
579 AND BE.LOOP_FLAG = x_no
580 AND BBOM_C.ORGANIZATION_ID(+) = arg_org_id
581 AND BBOM_C.ASSEMBLY_ITEM_ID (+) = BIC.COMPONENT_ITEM_ID
582 AND (
583 ( arg_alt_bom_desig IS NULL
584 AND
585 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL
586 )
587 OR
588 (arg_alt_bom_desig IS NOT NULL
589 AND
590 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
591 AND
592 BBOM_C.ALTERNATE_BOM_DESIGNATOR=arg_alt_bom_desig
593 )
594 OR
595 ( arg_alt_bom_desig IS NOT NULL
596 AND BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL
597 AND NOT EXISTS
598 (SELECT 'X'
599 FROM BOM_BILL_OF_MATERIALS BOM2
600 WHERE BOM2.ORGANIZATION_ID = arg_org_id
601 AND BOM2.ASSEMBLY_ITEM_ID = BIC.COMPONENT_ITEM_ID
602 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
603 arg_alt_bom_desig
604 AND BOM2.ASSEMBLY_TYPE = 1
605 ) -- subquery
606 )
607 ) -- end of alt logic
608 ORDER BY be.sort_order,
609 decode(arg_order_by,1,bic.operation_seq_num, bic.item_num),
610 decode(arg_order_by,1,bic.item_num, bic.operation_seq_num);
611
612
613 X_ParentCode bom_explosions.component_code%type;
614 X_Ancestor number; -- component item id within component code
615
616 Loop_Count_Val Number := 0;
617 L_Bulk_Count Number := 0;
618 l_plan_level Number := 0;
619
620 -- New plsql tables, etc added for resolving Bug 2822347
621
622 pragma AUTONOMOUS_TRANSACTION; --added for bug 2709042
623
624 BEGIN
625
626 SAVEPOINT BE;
627
628 x_top_bill_id := 0;
629 x_req_id := 0;
630 x_delete_bom_expl := 2;
631 x_top_common_bill_id := 0;
632 x_top_source_bill_id := 0;
633
634 stmt_num := 10;
635
636 -- Get the bill sequence id for the given item/org. If no primary bill exists
637 -- raise an exception
638
639 FOR cr IN get_bill_id LOOP
640 x_top_bill_id := cr.bill_sequence_id;
641 x_top_common_bill_id := cr.common_bill_sequence_id;
642 x_top_source_bill_id := cr.source_bill_sequence_id;
643 END LOOP;
644
645 IF (x_top_bill_id = 0) THEN
646 raise x_no_top_assy;
647 END IF;
648
649 --Added for bug 2700606
650
651 x_delete_bom_expl := fnd_profile.value('BOM:DELETE_BOM_EXPLOSIONS');
652
653 stmt_num := 15;
654
655 IF (x_delete_bom_expl = 1) THEN
656
657 For cr in bom_expl(x_top_bill_id) Loop
658 /* Fix for bug 9198518 - modify the If condition to ignore rows with request_id -999 */
659 IF ( (cr.request_id IS NOT NULL) AND (cr.request_id <> -999))THEN
660 x_req_id := cr.request_id;
661 raise x_bom_expl_del;
662 END IF;
663 End Loop;
664
665 END IF;
666
667 /* Fix for bug 9198518 - check whether explosion of the top model is currently underway,
668 and if so throw an error msg asking users to wait till the explosion completes */
669 stmt_num := 16;
670
671 For cr in bom_expl(x_top_bill_id) Loop
672 IF (cr.request_id is not null) AND (cr.request_id = -999) THEN
673 raise x_bom_expl_run;
674 END IF;
675 End Loop;
676
677 stmt_num := 20;
678
679 -- Insert a record for the assembly in BOM_EXPLOSIONS. This will serve as the
680 -- parent (plan_level = 0) for the rest of the explosion.
681 /*Bug 6407303 Added the attribute parent_sort_order and set its value to null*/
682 insert into bom_explosions
683 (TOP_BILL_SEQUENCE_ID ,
684 BILL_SEQUENCE_ID ,
685 ORGANIZATION_ID ,
686 EXPLOSION_TYPE ,
687 COMPONENT_SEQUENCE_ID ,
688 COMPONENT_ITEM_ID ,
689 PLAN_LEVEL ,
690 EXTENDED_QUANTITY ,
691 SORT_ORDER ,
692 CREATION_DATE ,
693 CREATED_BY ,
694 LAST_UPDATE_DATE ,
695 LAST_UPDATED_BY ,
696 TOP_ITEM_ID ,
697 BASIS_TYPE ,
698 COMPONENT_QUANTITY ,
699 BOM_ITEM_TYPE ,
700 PARENT_BOM_ITEM_TYPE ,
701 COMMON_BILL_SEQUENCE_ID ,
702 EFFECTIVITY_DATE ,
703 DISABLE_DATE ,
704 COMPONENT_CODE ,
705 DESCRIPTION ,
706 PRIMARY_UOM_CODE ,
707 PRIMARY_UNIT_OF_MEASURE ,
708 BASE_ITEM_ID ,
709 ATP_COMPONENTS_FLAG ,
710 ATP_FLAG ,
711 PICK_COMPONENTS_FLAG ,
712 REPLENISH_TO_ORDER_FLAG ,
713 SHIPPABLE_ITEM_FLAG ,
714 CUSTOMER_ORDER_FLAG ,
715 INTERNAL_ORDER_FLAG ,
716 CUSTOMER_ORDER_ENABLED_FLAG ,
717 INTERNAL_ORDER_ENABLED_FLAG ,
718 SO_TRANSACTIONS_FLAG ,
719 REXPLODE_FLAG ,
720 COMP_BILL_SEQ_ID ,
721 COMP_COMMON_BILL_SEQ_ID ,
722 LOOP_FLAG ,
723 -- chrng: added auto_request_material
724 AUTO_REQUEST_MATERIAL,
725 SOURCE_BILL_SEQUENCE_ID,
726 COMP_SOURCE_BILL_SEQ_ID,
727 PARENT_SORT_ORDER)
728 SELECT
729 x_top_bill_id ,
730 x_top_bill_id ,
731 arg_org_id ,
732 arg_expl_type ,
733 x_top_bill_id ,
734 arg_item_id ,
735 0 ,
736 x_expl_qty ,
737 lpad('1', X_SortWidth, '0') ,
738 sysdate ,
742 arg_item_id ,
739 arg_user_id ,
740 sysdate ,
741 arg_user_id ,
743 1 ,
744 1 ,
745 msi.bom_item_type ,
746 msi.bom_item_type ,
747 x_top_common_bill_id ,
748 arg_starting_rev_date ,
749 sysdate + 30000 ,
750 to_char(msi.inventory_item_id) ,
751 msitl.description ,
752 msi.PRIMARY_UOM_CODE ,
753 msi.PRIMARY_UNIT_OF_MEASURE ,
754 msi.BASE_ITEM_ID ,
755 msi.ATP_COMPONENTS_FLAG ,
756 msi.ATP_FLAG ,
757 msi.PICK_COMPONENTS_FLAG ,
758 msi.REPLENISH_TO_ORDER_FLAG ,
759 msi.SHIPPABLE_ITEM_FLAG ,
760 msi.CUSTOMER_ORDER_FLAG ,
761 msi.INTERNAL_ORDER_FLAG ,
762 msi.CUSTOMER_ORDER_ENABLED_FLAG ,
763 msi.INTERNAL_ORDER_ENABLED_FLAG ,
764 msi.SO_TRANSACTIONS_FLAG ,
765 1 ,
766 x_top_bill_id ,
767 x_top_common_bill_id ,
768 x_no ,
769 -- chrng: added 'Y' as default for auto_request_material
770 'Y',
771 x_top_source_bill_id,
772 x_top_source_bill_id,
773 NULL
774 FROM mtl_system_items msi,
775 mtl_system_items_tl msitl
776 WHERE msi.organization_id = arg_org_id
777 AND msi.inventory_item_id = arg_item_id
778 AND msitl.organization_id = msi.organization_id
779 AND msitl.inventory_item_id = msi.inventory_item_id
780 AND msitl.language = userenv('LANG')
781 and not exists (
782 select null
783 from bom_explosions be
784 where be.top_bill_sequence_id = x_top_bill_id
785 and be.explosion_type = arg_expl_type
786 );
787
788 -- Moved code for Performance from the while loop to the outer loop
789 -- Do not execute the update statement if the insert statement just inserted
790 --the record, bug: 3809420
791 If (sql%rowcount = 0) then
792
793 /* Fix for bug 9198518 - check whether explosion of the top model is currently underway,
794 and if so throw an error msg asking users to wait till the explosion completes */
795 stmt_num := 21;
796
797 For cr in bom_expl(x_top_bill_id) Loop
798 IF (cr.request_id is not null) AND (cr.request_id = -999) THEN
799 raise x_bom_expl_run;
800 END IF;
801 End Loop;
802
803 /* Start : Additions to fix bug #8496032 */
804
805 select count(*)
806 into l_count
807 from BOM_EXPLOSIONS be
808 where be.rexplode_flag = 1
809 And be.top_bill_sequence_id = x_top_bill_id
810 And be.explosion_type = arg_expl_type
811 and exists (select *
812 FROM MTL_SYSTEM_ITEMS msi
813 WHERE msi.organization_id = arg_org_id
814 and be.component_item_id = msi.inventory_item_id);
815
816 if(l_count > 0) then
817
818 BEGIN
819 /* End : Additions to fix bug #8479442
820 Also see exception below */
821
822 update BOM_EXPLOSIONS be
823 SET (BOM_ITEM_TYPE ,
824 DESCRIPTION ,
825 PRIMARY_UOM_CODE ,
826 PRIMARY_UNIT_OF_MEASURE ,
827 BASE_ITEM_ID ,
828 ATP_COMPONENTS_FLAG ,
829 ATP_FLAG ,
830 PICK_COMPONENTS_FLAG ,
831 REPLENISH_TO_ORDER_FLAG ,
832 SHIPPABLE_ITEM_FLAG ,
833 CUSTOMER_ORDER_FLAG ,
834 INTERNAL_ORDER_FLAG ,
835 CUSTOMER_ORDER_ENABLED_FLAG ,
836 INTERNAL_ORDER_ENABLED_FLAG ,
837 SO_TRANSACTIONS_FLAG)
838 = (select msi.bom_item_type ,
839 msitl.description ,
840 msi.PRIMARY_UOM_CODE ,
841 msi.PRIMARY_UNIT_OF_MEASURE ,
842 msi.BASE_ITEM_ID ,
843 msi.ATP_COMPONENTS_FLAG ,
844 msi.ATP_FLAG ,
845 msi.PICK_COMPONENTS_FLAG ,
846 msi.REPLENISH_TO_ORDER_FLAG ,
847 msi.SHIPPABLE_ITEM_FLAG ,
848 msi.CUSTOMER_ORDER_FLAG ,
849 msi.INTERNAL_ORDER_FLAG ,
850 msi.CUSTOMER_ORDER_ENABLED_FLAG ,
851 msi.INTERNAL_ORDER_ENABLED_FLAG ,
852 msi.SO_TRANSACTIONS_FLAG
853 from MTL_SYSTEM_ITEMS msi,
854 MTL_SYSTEM_ITEMS_TL msitl
855 WHERE msi.organization_id = arg_org_id
856 and msi.inventory_item_id = be.component_item_id
857 AND msitl.organization_id = msi.organization_id
858 AND msitl.inventory_item_id = msi.inventory_item_id
859 AND msitl.language = userenv('LANG'))
860 WHERE be.rexplode_flag = 1
861 And be.top_bill_sequence_id = x_top_bill_id
862 And be.explosion_type = arg_expl_type;
863
864 /* Exception also added for bug 8479442 */
865 EXCEPTION
866 when Others THEN
867 raise update_exp;
868 END;
869 /* Exception added for bug 8479442 */
870
871 Commit; -- Added commit after Update as it was causing deadlock
872
873 /* Fix for bug 9198518 - Populate the request_id for top model as - 999 signifying
874 that the explosion is in process. Commit it so as to be visible for other explosion runs. */
875
876 UPDATE BOM_EXPLOSIONS
877 SET request_id= -999
878 WHERE top_bill_sequence_id = x_top_bill_id
879 AND explosion_type = arg_expl_type
880 -- Bug 15961704
881 -- AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode
882 AND request_id is null;
883
884 If (sql%rowcount = 0) Then
885 raise x_bom_expl_run;
886 End If;
887
891 -- delete the subtree needing re-explosion
888 commit;
889 End If; --closing if statement introduced by bug 8496032
890 End If;
892
893 /*stmt_num := 40;
894 Delete from bom_explosions be
895 Where be.top_bill_sequence_id = x_top_bill_id
896 And be.explosion_type = arg_expl_type
897 and be.rexplode_flag = 1;
898 and be.sort_order like be.sort_order||'%'
899 and be.sort_order <> be.sort_order
900 */
901
902 Delete_Expl_Bill(x_top_bill_id,arg_expl_type);
903 /*Delete from bom_explosions be
904 Where be.top_bill_sequence_id = x_top_bill_id
905 and be.explosion_type = arg_expl_type
906 and exists (select 'X'
907 from bom_explosions be1
908 where be1.top_bill_sequence_id = x_top_bill_id
909 and be1.explosion_type = arg_expl_type
910 and be1.sort_order <> be.sort_order
911 and be.sort_order like be1.sort_order || '%'
912 and be1.rexplode_flag = 1);
913 */
914
915 -- End of code moved
916
917 l_plan_level := 0;
918 While X_MoreLevels
919 LOOP
920 X_MoreLevels := true;
921 stmt_num := 30;
922
923 /*
924 For X_Flags in GetExplodeFlags loop
925 If GetExplodeFlags%rowcount = 1 then
926 X_FirstLevel := X_Flags.plan_level;
927 End if;
928 If X_Flags.plan_level > X_FirstLevel then
929 X_MoreLevels := true;
930 Exit;
931 End if;
932 */
933
934 -- Explode the next level unless we've reached the maximum level
935
936 stmt_num := 50;
937 If l_plan_level > arg_levels_to_explode
938 then
939 Exit;
940 else
941 x_sort_counter := 0;
942
943 /*
944 Update the sort_order for the plan_level =0 to sort_order in constant as the
945 first never gets deleted even if the re_explode flag is 1.This has to be done else
946 the existing BOM's sort_order will go wrong during re_explosion
947 */
948 IF l_plan_level = 0
949 THEN
950 UPDATE bom_explosions be
951 SET sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode
952 WHERE be.plan_level = 0
953 AND be.top_bill_sequence_id = x_top_bill_id
954 AND be.explosion_type = arg_expl_type
955 AND be.rexplode_flag = 1;
956 END IF;
957
958 stmt_num := 60;
959 Loop_Count_Val := 0;
960 l_bulk_count := 0;
961
962 LOOP
963 /* Empty the pl/sql tables before the iteration */
964 Empty_Sql_Tables;
965
966 If not ordered_bill%isopen then
967 open ordered_bill(p_plan_level => l_plan_level);
968 end if;
969 Fetch ordered_bill bulk collect into
970 OB_TOP_BILL_SEQUENCE_ID,
971 OB_BILL_SEQUENCE_ID,
972 OB_ORGANIZATION_ID,
973 OB_EXPLOSION_TYPE,
974 OB_COMPONENT_SEQUENCE_ID,
975 OB_COMPONENT_ITEM_ID,
976 OB_PLAN_LEVEL,
977 OB_EXTENDED_QUANTITY,
978 OB_SORT_ORDER,
979 OB_CREATION_DATE,
980 OB_CREATED_BY,
981 OB_LAST_UPDATE_DATE,
982 OB_LAST_UPDATED_BY,
983 OB_TOP_ITEM_ID,
984 OB_ATTRIBUTE1,
985 OB_ATTRIBUTE2,
986 OB_ATTRIBUTE3,
987 OB_ATTRIBUTE4,
988 OB_ATTRIBUTE5,
989 OB_ATTRIBUTE6,
990 OB_ATTRIBUTE7,
991 OB_ATTRIBUTE8,
992 OB_ATTRIBUTE9,
993 OB_ATTRIBUTE10,
994 OB_ATTRIBUTE11,
995 OB_ATTRIBUTE12,
996 OB_ATTRIBUTE13,
997 OB_ATTRIBUTE14,
998 OB_ATTRIBUTE15,
999 OB_BASIS_TYPE,
1000 OB_COMPONENT_QUANTITY,
1001 OB_SO_BASIS,
1002 OB_OPTIONAL,
1003 OB_MUTUALLY_EXCLUSIVE_OPTIONS,
1004 OB_CHECK_ATP,
1005 OB_SHIPPING_ALLOWED,
1006 OB_REQUIRED_TO_SHIP,
1007 OB_REQUIRED_FOR_REVENUE,
1008 OB_INCLUDE_ON_SHIP_DOCS,
1009 OB_INCLUDE_ON_BILL_DOCS,
1010 OB_LOW_QUANTITY,
1011 OB_HIGH_QUANTITY,
1012 OB_PICK_COMPONENTS,
1013 OB_PRIMARY_UOM_CODE,
1014 OB_PRIMARY_UNIT_OF_MEASURE,
1015 OB_BASE_ITEM_ID,
1016 OB_ATP_COMPONENTS_FLAG,
1017 OB_ATP_FLAG,
1018 OB_BOM_ITEM_TYPE,
1019 OB_PICK_COMPONENTS_FLAG,
1020 OB_REPLENISH_TO_ORDER_FLAG,
1021 OB_SHIPPABLE_ITEM_FLAG,
1022 OB_CUSTOMER_ORDER_FLAG,
1023 OB_INTERNAL_ORDER_FLAG,
1024 OB_CUSTOMER_ORDER_ENABLED_FLAG,
1025 OB_INTERNAL_ORDER_ENABLED_FLAG,
1026 OB_SO_TRANSACTIONS_FLAG,
1027 OB_DESCRIPTION,
1028 OB_ASSEMBLY_ITEM_ID,
1029 OB_COMPONENT_CODE,
1030 OB_LOOP_FLAG,
1031 OB_PARENT_BOM_ITEM_TYPE,
1032 OB_OPERATION_SEQ_NUM,
1033 OB_ITEM_NUM,
1034 OB_EFFECTIVITY_DATE,
1035 OB_DISABLE_DATE,
1036 OB_IMPLEMENTATION_DATE,
1037 OB_REXPLODE_FLAG,
1038 OB_COMMON_BILL_SEQUENCE_ID,
1039 OB_COMP_BILL_SEQ_ID,
1040 OB_COMP_COMMON_BILL_SEQ_ID,
1041 OB_AUTO_REQUEST_MATERIAL,
1042 OB_SOURCE_BILL_SEQUENCE_ID,
1043 OB_COMMON_COMPONENT_SEQ_ID,
1044 OB_COMP_SOURCE_BILL_SEQ_ID
1045 limit G_MAX_BATCH_FETCH_SIZE;
1046
1047 loop_Count_Val := ordered_bill%rowcount - l_bulk_count;
1048 If (ordered_bill%rowcount = 0) then
1049 X_MoreLevels := false; --bug 3809420
1050 End If;
1051 /* Generate the sort order for the node based on it count in
1052 in the parent.
1053 */
1054 generate_sort_order;
1055
1059 /*
1056 -- Loop the values from the above fetch and assign values for sort_order and
1057 -- component code
1058 FOR i IN 1..loop_Count_Val loop
1060 x_sort_counter := x_sort_counter + 1;
1061 ob_sort_order(i) := ob_sort_order(i) ||
1062 lpad(to_char(x_sort_counter), X_SortWidth, '0');
1063 */
1064 -- Loop Check
1065 X_ParentCode := ob_component_code(i);
1066 While X_ParentCode is not null
1067 LOOP
1068 If instr(X_ParentCode, '-') = 0 then
1069 X_Ancestor := to_number(X_ParentCode);
1070 X_ParentCode := null;
1071 Else
1072 X_Ancestor := to_number(substr(X_ParentCode, 1,
1073 instr(X_ParentCode, '-') - 1));
1074 X_ParentCode := substr(X_ParentCode, instr(X_ParentCode, '-')+1);
1075 End if;
1076 If X_Ancestor = ob_component_item_id(i)
1077 then -- loop detected
1078 ob_loop_flag(i) := x_yes;
1079 If ob_disable_date(i) > sysdate then
1080 close ordered_bill;
1081 raise x_loop_detected;
1082 End if;
1083 Exit;
1084 End if; -- loop found
1085 END LOOP; -- Loop Check while loop
1086
1087 /* assign the comoponent code */
1088 ob_component_code(i) := ob_component_code(i)||'-'||
1089 to_char(ob_component_item_id(i));
1090
1091 /* check if the component is a having a BOM. If the component has a BOM
1092 only then the component should be fetched for rexplosion
1093 */
1094 Begin
1095 select count(*)
1096 into bill_exists
1097 from bom_bill_of_materials
1098 where assembly_item_id = ob_component_item_id(i)
1099 and organization_id = OB_ORGANIZATION_ID(i);
1100
1101 If bill_exists = 0 then
1102 OB_REXPLODE_FLAG(i) := 0;
1103 end if;
1104 bill_exists := 0;
1105 End;
1106
1107 End Loop; -- For loop_Count_Val FOR LOOP
1108
1109 l_bulk_count := ordered_bill%rowcount;
1110
1111 -- Insert the pl/sql table using FORALL.
1112 stmt_num := 70;
1113 FORALL i IN 1..loop_Count_Val
1114 -- Removed append hint for bug 6065696 INSERT /*+ append */ INTO bom_explosions(
1115 INSERT INTO bom_explosions(
1116 TOP_BILL_SEQUENCE_ID,
1117 BILL_SEQUENCE_ID,
1118 ORGANIZATION_ID,
1119 EXPLOSION_TYPE,
1120 COMPONENT_SEQUENCE_ID,
1121 COMPONENT_ITEM_ID,
1122 PLAN_LEVEL,
1123 EXTENDED_QUANTITY,
1124 SORT_ORDER,
1125 CREATION_DATE,
1126 CREATED_BY,
1127 LAST_UPDATE_DATE,
1128 LAST_UPDATED_BY,
1129 TOP_ITEM_ID,
1130 ATTRIBUTE1,
1131 ATTRIBUTE2,
1132 ATTRIBUTE3,
1133 ATTRIBUTE4,
1134 ATTRIBUTE5,
1135 ATTRIBUTE6,
1136 ATTRIBUTE7,
1137 ATTRIBUTE8,
1138 ATTRIBUTE9,
1139 ATTRIBUTE10,
1140 ATTRIBUTE11,
1141 ATTRIBUTE12,
1142 ATTRIBUTE13,
1143 ATTRIBUTE14,
1144 ATTRIBUTE15,
1145 BASIS_TYPE,
1146 COMPONENT_QUANTITY,
1147 SO_BASIS,
1148 OPTIONAL,
1149 MUTUALLY_EXCLUSIVE_OPTIONS,
1150 CHECK_ATP,
1151 SHIPPING_ALLOWED,
1152 REQUIRED_TO_SHIP,
1153 REQUIRED_FOR_REVENUE,
1154 INCLUDE_ON_SHIP_DOCS,
1155 INCLUDE_ON_BILL_DOCS,
1156 LOW_QUANTITY,
1157 HIGH_QUANTITY,
1158 PICK_COMPONENTS,
1159 PRIMARY_UOM_CODE,
1160 PRIMARY_UNIT_OF_MEASURE,
1161 BASE_ITEM_ID,
1162 ATP_COMPONENTS_FLAG,
1163 ATP_FLAG,
1164 BOM_ITEM_TYPE,
1165 PICK_COMPONENTS_FLAG,
1166 REPLENISH_TO_ORDER_FLAG,
1167 SHIPPABLE_ITEM_FLAG,
1168 CUSTOMER_ORDER_FLAG,
1169 INTERNAL_ORDER_FLAG,
1170 CUSTOMER_ORDER_ENABLED_FLAG,
1171 INTERNAL_ORDER_ENABLED_FLAG,
1172 SO_TRANSACTIONS_FLAG,
1173 DESCRIPTION,
1174 ASSEMBLY_ITEM_ID,
1175 COMPONENT_CODE,
1176 LOOP_FLAG,
1177 PARENT_BOM_ITEM_TYPE,
1178 OPERATION_SEQ_NUM,
1179 ITEM_NUM,
1180 EFFECTIVITY_DATE,
1181 DISABLE_DATE,
1182 IMPLEMENTATION_DATE,
1183 REXPLODE_FLAG,
1184 COMMON_BILL_SEQUENCE_ID,
1185 COMP_BILL_SEQ_ID,
1186 COMP_COMMON_BILL_SEQ_ID,
1187 -- chrng: added auto_request_material,
1188 AUTO_REQUEST_MATERIAL,
1189 SOURCE_BILL_SEQUENCE_ID,
1190 COMMON_COMPONENT_SEQUENCE_ID,
1191 COMP_SOURCE_BILL_SEQ_ID,
1192 PARENT_SORT_ORDER)
1193 Values(
1194 OB_TOP_BILL_SEQUENCE_ID(i),
1195 OB_BILL_SEQUENCE_ID(i),
1196 OB_ORGANIZATION_ID(i),
1197 OB_EXPLOSION_TYPE(i),
1198 OB_COMPONENT_SEQUENCE_ID(i),
1199 OB_COMPONENT_ITEM_ID(i),
1200 OB_PLAN_LEVEL(i),
1201 OB_EXTENDED_QUANTITY(i),
1202 OB_SORT_ORDER(i),
1203 OB_CREATION_DATE(i),
1204 OB_CREATED_BY(i),
1205 OB_LAST_UPDATE_DATE(i),
1206 OB_LAST_UPDATED_BY(i),
1207 OB_TOP_ITEM_ID(i),
1208 OB_ATTRIBUTE1(i),
1209 OB_ATTRIBUTE2(i),
1210 OB_ATTRIBUTE3(i),
1211 OB_ATTRIBUTE4(i),
1212 OB_ATTRIBUTE5(i),
1213 OB_ATTRIBUTE6(i),
1214 OB_ATTRIBUTE7(i),
1215 OB_ATTRIBUTE8(i),
1216 OB_ATTRIBUTE9(i),
1217 OB_ATTRIBUTE10(i),
1218 OB_ATTRIBUTE11(i),
1219 OB_ATTRIBUTE12(i),
1220 OB_ATTRIBUTE13(i),
1221 OB_ATTRIBUTE14(i),
1222 OB_ATTRIBUTE15(i),
1223 OB_BASIS_TYPE(i),
1224 OB_COMPONENT_QUANTITY(i),
1225 OB_SO_BASIS(i),
1226 OB_OPTIONAL(i),
1227 OB_MUTUALLY_EXCLUSIVE_OPTIONS(i),
1228 OB_CHECK_ATP(i),
1229 OB_SHIPPING_ALLOWED(i),
1230 OB_REQUIRED_TO_SHIP(i),
1231 OB_REQUIRED_FOR_REVENUE(i),
1232 OB_INCLUDE_ON_SHIP_DOCS(i),
1236 OB_PICK_COMPONENTS(i),
1233 OB_INCLUDE_ON_BILL_DOCS(i),
1234 OB_LOW_QUANTITY(i),
1235 OB_HIGH_QUANTITY(i),
1237 OB_PRIMARY_UOM_CODE(i),
1238 OB_PRIMARY_UNIT_OF_MEASURE(i),
1239 OB_BASE_ITEM_ID(i),
1240 OB_ATP_COMPONENTS_FLAG(i),
1241 OB_ATP_FLAG(i),
1242 OB_BOM_ITEM_TYPE(i),
1243 OB_PICK_COMPONENTS_FLAG(i),
1244 OB_REPLENISH_TO_ORDER_FLAG(i),
1245 OB_SHIPPABLE_ITEM_FLAG(i),
1246 OB_CUSTOMER_ORDER_FLAG(i),
1247 OB_INTERNAL_ORDER_FLAG(i),
1248 OB_CUSTOMER_ORDER_ENABLED_FLAG(i),
1249 OB_INTERNAL_ORDER_ENABLED_FLAG(i),
1250 OB_SO_TRANSACTIONS_FLAG(i),
1251 OB_DESCRIPTION(i),
1252 OB_ASSEMBLY_ITEM_ID(i),
1253 OB_COMPONENT_CODE(i),
1254 OB_LOOP_FLAG(i),
1255 OB_PARENT_BOM_ITEM_TYPE(i),
1256 OB_OPERATION_SEQ_NUM(i),
1257 OB_ITEM_NUM(i),
1258 OB_EFFECTIVITY_DATE(i),
1259 OB_DISABLE_DATE(i),
1260 OB_IMPLEMENTATION_DATE(i),
1261 OB_REXPLODE_FLAG(i),
1262 OB_COMMON_BILL_SEQUENCE_ID(i),
1263 OB_COMP_BILL_SEQ_ID(i),
1264 OB_COMP_COMMON_BILL_SEQ_ID(i),
1265 OB_AUTO_REQUEST_MATERIAL(i),
1266 OB_SOURCE_BILL_SEQUENCE_ID(i),
1267 OB_COMMON_COMPONENT_SEQ_ID(i),
1268 OB_COMP_SOURCE_BILL_SEQ_ID(i),
1269 substr(OB_SORT_ORDER(i), 0 ,length(OB_SORT_ORDER(i)) - X_SortWidth ) );
1270 /* Bug 6407303 Added the new attribute parent_sort_order*/
1271 exit when Loop_Count_Val < G_MAX_BATCH_FETCH_SIZE;
1272 End loop;
1273 /* End of Bulk Fetch . Exit when all components are inserted for that level */
1274
1275 close ordered_bill; -- Close the cursor
1276
1277 X_MoreLevels := true;
1278
1279 End If; -- explode next level
1280
1281
1282
1283 stmt_num := 80;
1284 /* Update the current level level so that the next iteration does not pick the
1285 the rows
1286 */
1287 UPDATE bom_explosions be
1288 SET be.rexplode_flag = 0
1289 WHERE be.plan_level = l_plan_level
1290 AND be.top_bill_sequence_id = x_top_bill_id
1291 AND be.explosion_type = arg_expl_type
1292 AND be.rexplode_flag = 1;
1293
1294
1295 l_plan_level := l_plan_level + 1;
1296
1297 IF (l_plan_level > arg_levels_to_explode)
1298 THEN
1299 X_MoreLevels := false;
1300 END IF;
1301 --End loop; -- get flags
1302 End loop; -- more levels
1303
1304 arg_error_code := 0;
1305 arg_err_msg := '';
1306
1307 /* Fix for bug 9198518 - After successful explosion of the entire top model,
1308 reset the request_id for top model back to NULL. */
1309
1310 UPDATE BOM_EXPLOSIONS
1311 SET request_id= NULL
1312 WHERE top_bill_sequence_id = x_top_bill_id
1313 AND explosion_type = arg_expl_type
1314 -- Bug 15961704
1315 --AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode
1316 AND request_id = -999;
1317
1318 commit;
1319
1320 EXCEPTION
1321 WHEN DUP_VAL_ON_INDEX THEN
1322 arg_error_code := SQLCODE;
1323 arg_err_msg := 'BOMORXPB Duplicate(' || stmt_num ||'): ' ||
1324 substrb(SQLERRM,1,60);
1325 ROLLBACK ;
1326 WHEN x_loop_detected THEN
1327 arg_error_code := 9999;
1328 FND_MESSAGE.Set_Name('BOM', 'BOM_LOOP_EXISTS');
1329 arg_err_msg := FND_MESSAGE.GET;
1330 ROLLBACK; --bug 2709042
1331 WHEN x_no_top_assy THEN
1332 arg_error_code := 9998;
1333 FND_MESSAGE.Set_Name('BOM', 'BOM_BILL_DOES_NOT_EXIST');
1334 arg_err_msg := FND_MESSAGE.GET;
1335 ROLLBACK; --bug 2709042
1336 WHEN x_bom_expl_del THEN
1337 arg_error_code := 9997;
1338 FND_MESSAGE.Set_Name('BOM', 'BOM_EXPL_DEL_IN_PROGRESS');
1339 FND_MESSAGE.Set_Token('REQUEST_ID', x_req_id);
1340 arg_err_msg := FND_MESSAGE.GET;
1341 ROLLBACK; --bug 2709042
1342 /* Fix for bug 9198518- handle below exception which gets thrown
1343 when bom explosion is already underway.
1344 Rollback all the changes, then explicitly reset request_id as NULL.*/
1345 WHEN x_bom_expl_run THEN
1346 arg_error_code := 9996;
1347 FND_MESSAGE.Set_Name('BOM', 'BOM_EXPLOSION_IN_PROGRESS');
1348 arg_err_msg := FND_MESSAGE.GET;
1349 ROLLBACK;
1350
1351 WHEN update_exp THEN
1352 ROLLBACK; --update_exp definition added for bug 8496032
1353 WHEN OTHERS THEN
1354 arg_error_code := SQLCODE;
1355 arg_err_msg := 'BOMORXPB(' || stmt_num ||'): ' ||
1356 substrb(SQLERRM,1,60);
1357 --ROLLBACK TO SAVEPOINT BE;
1358 ROLLBACK; --bug 2709042
1359
1360 END be_exploder;
1361
1362
1363 PROCEDURE delete_config_exp (
1364 arg_session_id IN NUMBER
1365 ) is
1366
1367 BEGIN
1368 DELETE FROM BOM_CONFIG_EXPLOSIONS
1369 WHERE SESSION_ID = arg_session_id;
1370 END delete_config_exp;
1371
1372 END bom_oe_exploder_pkg;