[Home] [Help]
PACKAGE BODY: APPS.BOM_OE_EXPLODER_PKG
Source
1 package body bom_oe_exploder_pkg as
2 /* $Header: BOMORXPB.pls 120.9.12010000.3 2008/10/22 03:54:13 minxie ship $ */
3
4 /*==========================================================================+
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;
112 OB_ATTRIBUTE14 varchar_tab_150;
113 OB_ATTRIBUTE15 varchar_tab_150;
114 OB_BASIS_TYPE number_tab_tp;
115 OB_COMPONENT_QUANTITY 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
266 and explosion_type = arg_expl_type
267 and rexplode_flag = 1;
268 BEGIN
269
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
357 x_top_bill_id NUMBER;
358 x_top_common_bill_id NUMBER;
359 x_top_source_bill_id NUMBER;
360
361 x_sort_counter NUMBER := 0;
362 x_req_id NUMBER := 0;
363 x_delete_bom_expl NUMBER := 2;
364
365 x_yes constant number := 1;
366 x_no constant number := 2;
367 bill_exists Number := 0;
368
369 CURSOR get_bill_id IS
370 SELECT bill_sequence_id,
371 common_bill_sequence_id,
372 source_bill_sequence_id
373 FROM bom_bill_of_materials
374 WHERE assembly_item_id = arg_item_id
375 AND organization_id = arg_org_id
376 AND NVL(alternate_bom_designator,'NONE') = NVL(arg_alt_bom_desig,'NONE');
377
378 CURSOR bom_expl(top_bill_id Number) IS
379 Select REQUEST_ID
380 FROM BOM_EXPLOSIONS
381 WHERE top_bill_sequence_id = top_bill_id
382 AND explosion_type = arg_expl_type
383 AND sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode;
384
385 Cursor GetExplodeFlags is
386 Select rowid,
387 plan_level,
388 sort_order
389 From bom_explosions
390 Where rexplode_flag = 1
391 And top_bill_sequence_id = x_top_bill_id
392 And explosion_type = arg_expl_type
393 Order by plan_level;
394
395 X_MoreLevels boolean := true; -- more levels to explode
396 X_FirstLevel number; -- first level needing re-explosion
397
398 Cursor ordered_bill(p_plan_level number) IS
399 SELECT /*+ LEADING (BE) */
400 x_top_bill_id TOP_BILL_SEQUENCE_ID,
401 BOM.BILL_SEQUENCE_ID,
402 BOM.ORGANIZATION_ID,
403 arg_expl_type EXPLOSION_TYPE,
404 BIC.COMPONENT_SEQUENCE_ID,
405 BIC.COMPONENT_ITEM_ID,
406 BE.PLAN_LEVEL + 1 PLAN_LEVEL,
407 decode(BIC.BASIS_TYPE, null, BE.EXTENDED_QUANTITY,1) * BIC.COMPONENT_QUANTITY EXTENDED_QUANTITY,
408 BE.SORT_ORDER,
409 sysdate CREATION_DATE,
410 arg_user_id CREATED_BY,
411 sysdate LAST_UPDATE_DATE,
412 arg_user_id LAST_UPDATED_BY,
413 BE.TOP_ITEM_ID,
414 BIC.ATTRIBUTE1,
415 BIC.ATTRIBUTE2,
416 BIC.ATTRIBUTE3,
417 BIC.ATTRIBUTE4,
418 BIC.ATTRIBUTE5,
419 BIC.ATTRIBUTE6,
420 BIC.ATTRIBUTE7,
421 BIC.ATTRIBUTE8,
422 BIC.ATTRIBUTE9,
423 BIC.ATTRIBUTE10,
424 BIC.ATTRIBUTE11,
425 BIC.ATTRIBUTE12,
426 BIC.ATTRIBUTE13,
427 BIC.ATTRIBUTE14,
428 BIC.ATTRIBUTE15,
429 BIC.BASIS_TYPE,
430 BIC.COMPONENT_QUANTITY,
431 BIC.SO_BASIS,
432 BIC.OPTIONAL,
433 BIC.MUTUALLY_EXCLUSIVE_OPTIONS,
434 BIC.CHECK_ATP,
435 BIC.SHIPPING_ALLOWED,
436 BIC.REQUIRED_TO_SHIP,
437 BIC.REQUIRED_FOR_REVENUE,
438 BIC.INCLUDE_ON_SHIP_DOCS,
439 BIC.INCLUDE_ON_BILL_DOCS,
440 BIC.LOW_QUANTITY,
441 BIC.HIGH_QUANTITY,
442 BIC.PICK_COMPONENTS,
443 MSI.PRIMARY_UOM_CODE,
444 MSI.PRIMARY_UNIT_OF_MEASURE,
445 MSI.BASE_ITEM_ID,
446 MSI.ATP_COMPONENTS_FLAG,
447 MSI.ATP_FLAG,
448 MSI.BOM_ITEM_TYPE,
449 MSI.PICK_COMPONENTS_FLAG,
450 MSI.REPLENISH_TO_ORDER_FLAG,
451 MSI.SHIPPABLE_ITEM_FLAG,
452 MSI.CUSTOMER_ORDER_FLAG,
453 MSI.INTERNAL_ORDER_FLAG,
454 MSI.CUSTOMER_ORDER_ENABLED_FLAG,
455 MSI.INTERNAL_ORDER_ENABLED_FLAG,
456 MSI.SO_TRANSACTIONS_FLAG,
457 MSITL.DESCRIPTION,
458 BOM.ASSEMBLY_ITEM_ID,
459 BE.COMPONENT_CODE,
460 BE.LOOP_FLAG,
461 BE.BOM_ITEM_TYPE PARENT_BOM_ITEM_TYPE,
462 BIC.OPERATION_SEQ_NUM,
463 BIC.ITEM_NUM,
464 GREATEST(BE.EFFECTIVITY_DATE, BIC.EFFECTIVITY_DATE) EFFECTIVITY_DATE,
465 LEAST(BE.DISABLE_DATE, NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) DISABLE_DATE,
466 BIC.IMPLEMENTATION_DATE,
467 1 REXPLODE_FLAG,
468 BOM.COMMON_BILL_SEQUENCE_ID,
469 BBOM_C.BILL_SEQUENCE_ID COMP_BILL_SEQ_ID,
470 BBOM_C.COMMON_BILL_SEQUENCE_ID COMP_COMMON_BILL_SEQ_ID,
471 -- chrng: added auto_request_material
472 BIC.AUTO_REQUEST_MATERIAL,
473 BOM.SOURCE_BILL_SEQUENCE_ID,
474 BIC.COMMON_COMPONENT_SEQUENCE_ID,
475 BBOM_C.SOURCE_BILL_SEQUENCE_ID COMP_SOURCE_BILL_SEQ_ID
476 FROM
477 BOM_STRUCTURES_B BBOM_C,
478 MTL_SYSTEM_ITEMS MSI,
479 MTL_SYSTEM_ITEMS_TL MSITL,
480 BOM_COMPONENTS_B BIC,
481 BOM_STRUCTURES_B BOM,
482 BOM_EXPLOSIONS BE
483 -- FP bug fix for 12.1.1. The bug # is 7307613.
484 -- Fixed by Minling on 10/15/08.
485 -- Changed the WHERE condition to improve performance of the query.
486 WHERE ( ( BBOM_C.obj_name IS NULL AND fnd_global.RESP_APPL_ID = 431 )
487 OR ( BBOM_C.obj_name IS NULL AND fnd_global.RESP_APPL_ID = -1 )
488 OR ( BBOM_C.obj_name is null and fnd_global.RESP_APPL_ID <> 431 and nvl(BBOM_C.effectivity_control,1) <= 3 ) )
489 AND ( ( BOM.obj_name IS NULL AND fnd_global.RESP_APPL_ID = 431 )
490 OR ( BOM.obj_name IS NULL AND fnd_global.RESP_APPL_ID = -1 )
491 OR ( BOM.obj_name is null and fnd_global.RESP_APPL_ID <> 431 and nvl(BOM.effectivity_control,1) <= 3 ) )
492 AND BE.TOP_BILL_SEQUENCE_ID = x_top_bill_id
493 -- END of bug fix 7307613.
494
495
496 AND BE.EXPLOSION_TYPE = arg_expl_type
497 --AND BE.SORT_ORDER = P_Parent
498 AND nvl(BBOM_C.effectivity_control,1) <= 3
499 AND
500 (
501 BBOM_C.obj_name is null
502 OR BBOM_C.obj_name = 'EGO_ITEM'
503 )
504 AND
505 (
506 BOM.obj_name is null
507 OR BOM.obj_name = 'EGO_ITEM'
508 )
509 AND NVL(BOM.effectivity_control,1) <= 3 --Bug 7444587(7450613,7450614)
510 AND BIC.overlapping_changes is null
511 AND
512 (
513 BIC.obj_name is null
514 OR BIC.obj_name = 'EGO_ITEM'
515 )
516 AND BE.rexplode_flag = 1
517 AND BE.PLAN_LEVEL = p_plan_level
518 AND BOM.ORGANIZATION_ID = BE.ORGANIZATION_ID
519 AND BOM.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID
520 AND (
521 ( arg_alt_bom_desig IS NULL
522 AND
523 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
524 )
525 OR
526 (arg_alt_bom_desig IS NOT NULL
527 AND
528 BOM.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
529 AND
530 BOM.ALTERNATE_BOM_DESIGNATOR=arg_alt_bom_desig
531 )
532 OR
533 ( arg_alt_bom_desig IS NOT NULL
534 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
535 AND NOT EXISTS
536 (SELECT 'X'
537 FROM BOM_BILL_OF_MATERIALS BOM2
538 WHERE BOM2.ORGANIZATION_ID = arg_org_id
539 AND BOM2.ASSEMBLY_ITEM_ID = BE.COMPONENT_ITEM_ID
540 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
541 arg_alt_bom_desig
542 AND BOM2.ASSEMBLY_TYPE = 1
543 ) -- subquery
544 )
545 ) -- end of alt logic
546 AND BIC.BILL_SEQUENCE_ID = BOM.COMMON_BILL_SEQUENCE_ID
547 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
548 AND BIC.IMPLEMENTATION_DATE IS NOT NULL
549 AND BIC.COMPONENT_QUANTITY >= 0
550 AND MSI.ORGANIZATION_ID = BOM.ORGANIZATION_ID
551 AND MSI.INVENTORY_ITEM_ID = BIC.COMPONENT_ITEM_ID
552 AND MSITL.ORGANIZATION_ID = MSI.ORGANIZATION_ID
553 AND MSITL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
554 AND MSITL.LANGUAGE = USERENV('LANG')
555 AND ( (arg_expl_type = 'OPTIONAL'
556 AND BE.BOM_ITEM_TYPE in (1,2) -- parent is a model or opt class
557 AND (BIC.BOM_ITEM_TYPE IN (1,2) OR -- comp is a model or opt class
558 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)))
559 -- comp is an optional standard item
560 OR (arg_expl_type = 'INCLUDED'
561 AND BE.PICK_COMPONENTS_FLAG = 'Y' -- parent is PTO
562 AND BIC.BOM_ITEM_TYPE = 4 -- comp is a mandatory standard item
563 AND BIC.OPTIONAL = 2)
564 OR (arg_expl_type not in ('OPTIONAL', 'INCLUDED')) -- both
565 )
566 AND ( (BE.BASE_ITEM_ID IS NOT NULL AND
567 BIC.BOM_ITEM_TYPE NOT IN (1,2)
568 )
569 OR
570 BE.BASE_ITEM_ID IS NULL
571 ) /* Added for bug 3531716*/
572 AND BOM.ASSEMBLY_TYPE = 1
573 AND LEAST(BE.DISABLE_DATE, NVL(BIC.DISABLE_DATE,BE.DISABLE_DATE)) >=
574 GREATEST(BE.EFFECTIVITY_DATE, BIC.EFFECTIVITY_DATE)
575 AND BE.LOOP_FLAG = x_no
576 AND BBOM_C.ORGANIZATION_ID(+) = arg_org_id
577 AND BBOM_C.ASSEMBLY_ITEM_ID (+) = BIC.COMPONENT_ITEM_ID
578 AND (
579 ( arg_alt_bom_desig IS NULL
580 AND
581 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL
582 )
583 OR
584 (arg_alt_bom_desig IS NOT NULL
585 AND
586 BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NOT NULL
587 AND
588 BBOM_C.ALTERNATE_BOM_DESIGNATOR=arg_alt_bom_desig
589 )
590 OR
591 ( arg_alt_bom_desig IS NOT NULL
592 AND BBOM_C.ALTERNATE_BOM_DESIGNATOR IS NULL
593 AND NOT EXISTS
594 (SELECT 'X'
595 FROM BOM_BILL_OF_MATERIALS BOM2
596 WHERE BOM2.ORGANIZATION_ID = arg_org_id
597 AND BOM2.ASSEMBLY_ITEM_ID = BIC.COMPONENT_ITEM_ID
598 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
599 arg_alt_bom_desig
600 AND BOM2.ASSEMBLY_TYPE = 1
601 ) -- subquery
602 )
603 ) -- end of alt logic
604 ORDER BY be.sort_order,
605 decode(arg_order_by,1,bic.operation_seq_num, bic.item_num),
606 decode(arg_order_by,1,bic.item_num, bic.operation_seq_num);
607
608
609 X_ParentCode bom_explosions.component_code%type;
610 X_Ancestor number; -- component item id within component code
611
612 Loop_Count_Val Number := 0;
613 L_Bulk_Count Number := 0;
614 l_plan_level Number := 0;
615
616 -- New plsql tables, etc added for resolving Bug 2822347
617
618 pragma AUTONOMOUS_TRANSACTION; --added for bug 2709042
619
620 BEGIN
621
622 SAVEPOINT BE;
623
624 x_top_bill_id := 0;
625 x_req_id := 0;
626 x_delete_bom_expl := 2;
627 x_top_common_bill_id := 0;
628 x_top_source_bill_id := 0;
629
630 stmt_num := 10;
631
632 -- Get the bill sequence id for the given item/org. If no primary bill exists
633 -- raise an exception
634
635 FOR cr IN get_bill_id LOOP
636 x_top_bill_id := cr.bill_sequence_id;
637 x_top_common_bill_id := cr.common_bill_sequence_id;
638 x_top_source_bill_id := cr.source_bill_sequence_id;
639 END LOOP;
640
641 IF (x_top_bill_id = 0) THEN
642 raise x_no_top_assy;
643 END IF;
644
645 --Added for bug 2700606
646
647 x_delete_bom_expl := fnd_profile.value('BOM:DELETE_BOM_EXPLOSIONS');
648
649 stmt_num := 15;
650
651 IF (x_delete_bom_expl = 1) THEN
652
653 For cr in bom_expl(x_top_bill_id) Loop
654 IF (cr.request_id IS NOT NULL) THEN
655 x_req_id := cr.request_id;
656 raise x_bom_expl_del;
657 END IF;
658 End Loop;
659
660 END IF;
661
662 stmt_num := 20;
663
664 -- Insert a record for the assembly in BOM_EXPLOSIONS. This will serve as the
665 -- parent (plan_level = 0) for the rest of the explosion.
666 /*Bug 6407303 Added the attribute parent_sort_order and set its value to null*/
667 insert into bom_explosions
668 (TOP_BILL_SEQUENCE_ID ,
669 BILL_SEQUENCE_ID ,
670 ORGANIZATION_ID ,
671 EXPLOSION_TYPE ,
672 COMPONENT_SEQUENCE_ID ,
673 COMPONENT_ITEM_ID ,
674 PLAN_LEVEL ,
675 EXTENDED_QUANTITY ,
676 SORT_ORDER ,
677 CREATION_DATE ,
678 CREATED_BY ,
679 LAST_UPDATE_DATE ,
680 LAST_UPDATED_BY ,
681 TOP_ITEM_ID ,
682 BASIS_TYPE ,
683 COMPONENT_QUANTITY ,
684 BOM_ITEM_TYPE ,
685 PARENT_BOM_ITEM_TYPE ,
686 COMMON_BILL_SEQUENCE_ID ,
687 EFFECTIVITY_DATE ,
688 DISABLE_DATE ,
689 COMPONENT_CODE ,
690 DESCRIPTION ,
691 PRIMARY_UOM_CODE ,
692 PRIMARY_UNIT_OF_MEASURE ,
693 BASE_ITEM_ID ,
694 ATP_COMPONENTS_FLAG ,
695 ATP_FLAG ,
696 PICK_COMPONENTS_FLAG ,
697 REPLENISH_TO_ORDER_FLAG ,
698 SHIPPABLE_ITEM_FLAG ,
699 CUSTOMER_ORDER_FLAG ,
700 INTERNAL_ORDER_FLAG ,
701 CUSTOMER_ORDER_ENABLED_FLAG ,
702 INTERNAL_ORDER_ENABLED_FLAG ,
703 SO_TRANSACTIONS_FLAG ,
704 REXPLODE_FLAG ,
705 COMP_BILL_SEQ_ID ,
706 COMP_COMMON_BILL_SEQ_ID ,
707 LOOP_FLAG ,
708 -- chrng: added auto_request_material
709 AUTO_REQUEST_MATERIAL,
710 SOURCE_BILL_SEQUENCE_ID,
711 COMP_SOURCE_BILL_SEQ_ID,
712 PARENT_SORT_ORDER)
713 SELECT
714 x_top_bill_id ,
715 x_top_bill_id ,
716 arg_org_id ,
717 arg_expl_type ,
718 x_top_bill_id ,
719 arg_item_id ,
720 0 ,
721 x_expl_qty ,
722 lpad('1', X_SortWidth, '0') ,
723 sysdate ,
724 arg_user_id ,
725 sysdate ,
726 arg_user_id ,
727 arg_item_id ,
728 1 ,
729 1 ,
730 msi.bom_item_type ,
731 msi.bom_item_type ,
732 x_top_common_bill_id ,
733 arg_starting_rev_date ,
734 sysdate + 30000 ,
735 to_char(msi.inventory_item_id) ,
736 msitl.description ,
737 msi.PRIMARY_UOM_CODE ,
738 msi.PRIMARY_UNIT_OF_MEASURE ,
739 msi.BASE_ITEM_ID ,
740 msi.ATP_COMPONENTS_FLAG ,
741 msi.ATP_FLAG ,
742 msi.PICK_COMPONENTS_FLAG ,
743 msi.REPLENISH_TO_ORDER_FLAG ,
744 msi.SHIPPABLE_ITEM_FLAG ,
745 msi.CUSTOMER_ORDER_FLAG ,
746 msi.INTERNAL_ORDER_FLAG ,
747 msi.CUSTOMER_ORDER_ENABLED_FLAG ,
748 msi.INTERNAL_ORDER_ENABLED_FLAG ,
749 msi.SO_TRANSACTIONS_FLAG ,
750 1 ,
751 x_top_bill_id ,
752 x_top_common_bill_id ,
753 x_no ,
754 -- chrng: added 'Y' as default for auto_request_material
755 'Y',
756 x_top_source_bill_id,
757 x_top_source_bill_id,
758 NULL
759 FROM mtl_system_items msi,
760 mtl_system_items_tl msitl
761 WHERE msi.organization_id = arg_org_id
762 AND msi.inventory_item_id = arg_item_id
763 AND msitl.organization_id = msi.organization_id
764 AND msitl.inventory_item_id = msi.inventory_item_id
765 AND msitl.language = userenv('LANG')
766 and not exists (
767 select null
768 from bom_explosions be
769 where be.top_bill_sequence_id = x_top_bill_id
770 and be.explosion_type = arg_expl_type
771 );
772
773 -- Moved code for Performance from the while loop to the outer loop
774 -- Do not execute the update statement if the insert statement just inserted
775 --the record, bug: 3809420
776 If (sql%rowcount = 0) then
777 update BOM_EXPLOSIONS be
778 SET (BOM_ITEM_TYPE ,
779 DESCRIPTION ,
780 PRIMARY_UOM_CODE ,
781 PRIMARY_UNIT_OF_MEASURE ,
782 BASE_ITEM_ID ,
783 ATP_COMPONENTS_FLAG ,
784 ATP_FLAG ,
785 PICK_COMPONENTS_FLAG ,
786 REPLENISH_TO_ORDER_FLAG ,
787 SHIPPABLE_ITEM_FLAG ,
788 CUSTOMER_ORDER_FLAG ,
789 INTERNAL_ORDER_FLAG ,
790 CUSTOMER_ORDER_ENABLED_FLAG ,
791 INTERNAL_ORDER_ENABLED_FLAG ,
792 SO_TRANSACTIONS_FLAG)
793 = (select msi.bom_item_type ,
794 msitl.description ,
795 msi.PRIMARY_UOM_CODE ,
796 msi.PRIMARY_UNIT_OF_MEASURE ,
797 msi.BASE_ITEM_ID ,
798 msi.ATP_COMPONENTS_FLAG ,
799 msi.ATP_FLAG ,
800 msi.PICK_COMPONENTS_FLAG ,
801 msi.REPLENISH_TO_ORDER_FLAG ,
802 msi.SHIPPABLE_ITEM_FLAG ,
803 msi.CUSTOMER_ORDER_FLAG ,
804 msi.INTERNAL_ORDER_FLAG ,
805 msi.CUSTOMER_ORDER_ENABLED_FLAG ,
806 msi.INTERNAL_ORDER_ENABLED_FLAG ,
807 msi.SO_TRANSACTIONS_FLAG
808 from MTL_SYSTEM_ITEMS msi,
809 MTL_SYSTEM_ITEMS_TL msitl
810 WHERE msi.organization_id = arg_org_id
811 and msi.inventory_item_id = be.component_item_id
812 AND msitl.organization_id = msi.organization_id
813 AND msitl.inventory_item_id = msi.inventory_item_id
814 AND msitl.language = userenv('LANG'))
815 WHERE be.rexplode_flag = 1
816 And be.top_bill_sequence_id = x_top_bill_id
817 And be.explosion_type = arg_expl_type;
818
819
820 Commit; -- Added commit after Update as it was causing deadlock
821 End If;
822 -- delete the subtree needing re-explosion
823
824 /*stmt_num := 40;
825 Delete from bom_explosions be
826 Where be.top_bill_sequence_id = x_top_bill_id
827 And be.explosion_type = arg_expl_type
828 and be.rexplode_flag = 1;
829 and be.sort_order like be.sort_order||'%'
830 and be.sort_order <> be.sort_order
831 */
832
833 Delete_Expl_Bill(x_top_bill_id,arg_expl_type);
834 /*Delete from bom_explosions be
835 Where be.top_bill_sequence_id = x_top_bill_id
836 and be.explosion_type = arg_expl_type
837 and exists (select 'X'
838 from bom_explosions be1
839 where be1.top_bill_sequence_id = x_top_bill_id
840 and be1.explosion_type = arg_expl_type
841 and be1.sort_order <> be.sort_order
842 and be.sort_order like be1.sort_order || '%'
843 and be1.rexplode_flag = 1);
844 */
845
846 -- End of code moved
847
848 l_plan_level := 0;
849 While X_MoreLevels
850 LOOP
851 X_MoreLevels := true;
852 stmt_num := 30;
853
854 /*
855 For X_Flags in GetExplodeFlags loop
856 If GetExplodeFlags%rowcount = 1 then
857 X_FirstLevel := X_Flags.plan_level;
858 End if;
859 If X_Flags.plan_level > X_FirstLevel then
860 X_MoreLevels := true;
861 Exit;
862 End if;
863 */
864
865 -- Explode the next level unless we've reached the maximum level
866
867 stmt_num := 50;
868 If l_plan_level > arg_levels_to_explode
869 then
870 Exit;
871 else
872 x_sort_counter := 0;
873
874 /*
875 Update the sort_order for the plan_level =0 to sort_order in constant as the
876 first never gets deleted even if the re_explode flag is 1.This has to be done else
877 the existing BOM's sort_order will go wrong during re_explosion
878 */
879 IF l_plan_level = 0
880 THEN
881 UPDATE bom_explosions be
882 SET sort_order = Bom_Common_Definitions.G_Bom_Init_SortCode
883 WHERE be.plan_level = 0
884 AND be.top_bill_sequence_id = x_top_bill_id
885 AND be.explosion_type = arg_expl_type
886 AND be.rexplode_flag = 1;
887 END IF;
888
889 stmt_num := 60;
890 Loop_Count_Val := 0;
891 l_bulk_count := 0;
892
893 LOOP
894 /* Empty the pl/sql tables before the iteration */
895 Empty_Sql_Tables;
896
897 If not ordered_bill%isopen then
898 open ordered_bill(p_plan_level => l_plan_level);
899 end if;
900 Fetch ordered_bill bulk collect into
901 OB_TOP_BILL_SEQUENCE_ID,
902 OB_BILL_SEQUENCE_ID,
903 OB_ORGANIZATION_ID,
904 OB_EXPLOSION_TYPE,
905 OB_COMPONENT_SEQUENCE_ID,
906 OB_COMPONENT_ITEM_ID,
907 OB_PLAN_LEVEL,
908 OB_EXTENDED_QUANTITY,
909 OB_SORT_ORDER,
910 OB_CREATION_DATE,
911 OB_CREATED_BY,
912 OB_LAST_UPDATE_DATE,
913 OB_LAST_UPDATED_BY,
914 OB_TOP_ITEM_ID,
915 OB_ATTRIBUTE1,
916 OB_ATTRIBUTE2,
917 OB_ATTRIBUTE3,
918 OB_ATTRIBUTE4,
919 OB_ATTRIBUTE5,
920 OB_ATTRIBUTE6,
921 OB_ATTRIBUTE7,
922 OB_ATTRIBUTE8,
923 OB_ATTRIBUTE9,
924 OB_ATTRIBUTE10,
925 OB_ATTRIBUTE11,
926 OB_ATTRIBUTE12,
927 OB_ATTRIBUTE13,
928 OB_ATTRIBUTE14,
929 OB_ATTRIBUTE15,
930 OB_BASIS_TYPE,
931 OB_COMPONENT_QUANTITY,
932 OB_SO_BASIS,
933 OB_OPTIONAL,
934 OB_MUTUALLY_EXCLUSIVE_OPTIONS,
935 OB_CHECK_ATP,
936 OB_SHIPPING_ALLOWED,
937 OB_REQUIRED_TO_SHIP,
938 OB_REQUIRED_FOR_REVENUE,
939 OB_INCLUDE_ON_SHIP_DOCS,
940 OB_INCLUDE_ON_BILL_DOCS,
941 OB_LOW_QUANTITY,
942 OB_HIGH_QUANTITY,
943 OB_PICK_COMPONENTS,
944 OB_PRIMARY_UOM_CODE,
945 OB_PRIMARY_UNIT_OF_MEASURE,
946 OB_BASE_ITEM_ID,
947 OB_ATP_COMPONENTS_FLAG,
948 OB_ATP_FLAG,
949 OB_BOM_ITEM_TYPE,
950 OB_PICK_COMPONENTS_FLAG,
951 OB_REPLENISH_TO_ORDER_FLAG,
952 OB_SHIPPABLE_ITEM_FLAG,
953 OB_CUSTOMER_ORDER_FLAG,
954 OB_INTERNAL_ORDER_FLAG,
955 OB_CUSTOMER_ORDER_ENABLED_FLAG,
956 OB_INTERNAL_ORDER_ENABLED_FLAG,
957 OB_SO_TRANSACTIONS_FLAG,
958 OB_DESCRIPTION,
959 OB_ASSEMBLY_ITEM_ID,
960 OB_COMPONENT_CODE,
961 OB_LOOP_FLAG,
962 OB_PARENT_BOM_ITEM_TYPE,
963 OB_OPERATION_SEQ_NUM,
964 OB_ITEM_NUM,
965 OB_EFFECTIVITY_DATE,
966 OB_DISABLE_DATE,
967 OB_IMPLEMENTATION_DATE,
968 OB_REXPLODE_FLAG,
969 OB_COMMON_BILL_SEQUENCE_ID,
970 OB_COMP_BILL_SEQ_ID,
971 OB_COMP_COMMON_BILL_SEQ_ID,
972 OB_AUTO_REQUEST_MATERIAL,
973 OB_SOURCE_BILL_SEQUENCE_ID,
974 OB_COMMON_COMPONENT_SEQ_ID,
975 OB_COMP_SOURCE_BILL_SEQ_ID
976 limit G_MAX_BATCH_FETCH_SIZE;
977
978 loop_Count_Val := ordered_bill%rowcount - l_bulk_count;
979 If (ordered_bill%rowcount = 0) then
980 X_MoreLevels := false; --bug 3809420
981 End If;
982 /* Generate the sort order for the node based on it count in
983 in the parent.
984 */
985 generate_sort_order;
986
987 -- Loop the values from the above fetch and assign values for sort_order and
988 -- component code
989 FOR i IN 1..loop_Count_Val loop
990 /*
991 x_sort_counter := x_sort_counter + 1;
992 ob_sort_order(i) := ob_sort_order(i) ||
993 lpad(to_char(x_sort_counter), X_SortWidth, '0');
994 */
995 -- Loop Check
996 X_ParentCode := ob_component_code(i);
997 While X_ParentCode is not null
998 LOOP
999 If instr(X_ParentCode, '-') = 0 then
1000 X_Ancestor := to_number(X_ParentCode);
1001 X_ParentCode := null;
1002 Else
1003 X_Ancestor := to_number(substr(X_ParentCode, 1,
1004 instr(X_ParentCode, '-') - 1));
1005 X_ParentCode := substr(X_ParentCode, instr(X_ParentCode, '-')+1);
1006 End if;
1007 If X_Ancestor = ob_component_item_id(i)
1008 then -- loop detected
1009 ob_loop_flag(i) := x_yes;
1010 If ob_disable_date(i) > sysdate then
1011 close ordered_bill;
1012 raise x_loop_detected;
1013 End if;
1014 Exit;
1015 End if; -- loop found
1016 END LOOP; -- Loop Check while loop
1017
1018 /* assign the comoponent code */
1019 ob_component_code(i) := ob_component_code(i)||'-'||
1020 to_char(ob_component_item_id(i));
1021
1022 /* check if the component is a having a BOM. If the component has a BOM
1023 only then the component should be fetched for rexplosion
1024 */
1025 Begin
1026 select count(*)
1027 into bill_exists
1028 from bom_bill_of_materials
1029 where assembly_item_id = ob_component_item_id(i)
1030 and organization_id = OB_ORGANIZATION_ID(i);
1031
1032 If bill_exists = 0 then
1033 OB_REXPLODE_FLAG(i) := 0;
1034 end if;
1035 bill_exists := 0;
1036 End;
1037
1038 End Loop; -- For loop_Count_Val FOR LOOP
1039
1040 l_bulk_count := ordered_bill%rowcount;
1041
1042 -- Insert the pl/sql table using FORALL.
1043 stmt_num := 70;
1044 FORALL i IN 1..loop_Count_Val
1045 -- Removed append hint for bug 6065696 INSERT /*+ append */ INTO bom_explosions(
1046 INSERT INTO bom_explosions(
1047 TOP_BILL_SEQUENCE_ID,
1048 BILL_SEQUENCE_ID,
1049 ORGANIZATION_ID,
1050 EXPLOSION_TYPE,
1051 COMPONENT_SEQUENCE_ID,
1052 COMPONENT_ITEM_ID,
1053 PLAN_LEVEL,
1054 EXTENDED_QUANTITY,
1055 SORT_ORDER,
1056 CREATION_DATE,
1057 CREATED_BY,
1058 LAST_UPDATE_DATE,
1059 LAST_UPDATED_BY,
1060 TOP_ITEM_ID,
1061 ATTRIBUTE1,
1062 ATTRIBUTE2,
1063 ATTRIBUTE3,
1064 ATTRIBUTE4,
1065 ATTRIBUTE5,
1066 ATTRIBUTE6,
1067 ATTRIBUTE7,
1068 ATTRIBUTE8,
1069 ATTRIBUTE9,
1070 ATTRIBUTE10,
1071 ATTRIBUTE11,
1072 ATTRIBUTE12,
1073 ATTRIBUTE13,
1074 ATTRIBUTE14,
1075 ATTRIBUTE15,
1076 BASIS_TYPE,
1077 COMPONENT_QUANTITY,
1078 SO_BASIS,
1079 OPTIONAL,
1080 MUTUALLY_EXCLUSIVE_OPTIONS,
1081 CHECK_ATP,
1082 SHIPPING_ALLOWED,
1083 REQUIRED_TO_SHIP,
1084 REQUIRED_FOR_REVENUE,
1085 INCLUDE_ON_SHIP_DOCS,
1086 INCLUDE_ON_BILL_DOCS,
1087 LOW_QUANTITY,
1088 HIGH_QUANTITY,
1089 PICK_COMPONENTS,
1090 PRIMARY_UOM_CODE,
1091 PRIMARY_UNIT_OF_MEASURE,
1092 BASE_ITEM_ID,
1093 ATP_COMPONENTS_FLAG,
1094 ATP_FLAG,
1095 BOM_ITEM_TYPE,
1096 PICK_COMPONENTS_FLAG,
1097 REPLENISH_TO_ORDER_FLAG,
1098 SHIPPABLE_ITEM_FLAG,
1099 CUSTOMER_ORDER_FLAG,
1100 INTERNAL_ORDER_FLAG,
1101 CUSTOMER_ORDER_ENABLED_FLAG,
1102 INTERNAL_ORDER_ENABLED_FLAG,
1103 SO_TRANSACTIONS_FLAG,
1104 DESCRIPTION,
1105 ASSEMBLY_ITEM_ID,
1106 COMPONENT_CODE,
1107 LOOP_FLAG,
1108 PARENT_BOM_ITEM_TYPE,
1109 OPERATION_SEQ_NUM,
1110 ITEM_NUM,
1111 EFFECTIVITY_DATE,
1112 DISABLE_DATE,
1113 IMPLEMENTATION_DATE,
1114 REXPLODE_FLAG,
1115 COMMON_BILL_SEQUENCE_ID,
1116 COMP_BILL_SEQ_ID,
1117 COMP_COMMON_BILL_SEQ_ID,
1118 -- chrng: added auto_request_material,
1119 AUTO_REQUEST_MATERIAL,
1120 SOURCE_BILL_SEQUENCE_ID,
1121 COMMON_COMPONENT_SEQUENCE_ID,
1122 COMP_SOURCE_BILL_SEQ_ID,
1123 PARENT_SORT_ORDER)
1124 Values(
1125 OB_TOP_BILL_SEQUENCE_ID(i),
1126 OB_BILL_SEQUENCE_ID(i),
1127 OB_ORGANIZATION_ID(i),
1128 OB_EXPLOSION_TYPE(i),
1129 OB_COMPONENT_SEQUENCE_ID(i),
1130 OB_COMPONENT_ITEM_ID(i),
1131 OB_PLAN_LEVEL(i),
1132 OB_EXTENDED_QUANTITY(i),
1133 OB_SORT_ORDER(i),
1134 OB_CREATION_DATE(i),
1135 OB_CREATED_BY(i),
1136 OB_LAST_UPDATE_DATE(i),
1137 OB_LAST_UPDATED_BY(i),
1138 OB_TOP_ITEM_ID(i),
1139 OB_ATTRIBUTE1(i),
1140 OB_ATTRIBUTE2(i),
1141 OB_ATTRIBUTE3(i),
1142 OB_ATTRIBUTE4(i),
1143 OB_ATTRIBUTE5(i),
1144 OB_ATTRIBUTE6(i),
1145 OB_ATTRIBUTE7(i),
1146 OB_ATTRIBUTE8(i),
1147 OB_ATTRIBUTE9(i),
1148 OB_ATTRIBUTE10(i),
1149 OB_ATTRIBUTE11(i),
1150 OB_ATTRIBUTE12(i),
1151 OB_ATTRIBUTE13(i),
1152 OB_ATTRIBUTE14(i),
1153 OB_ATTRIBUTE15(i),
1154 OB_BASIS_TYPE(i),
1155 OB_COMPONENT_QUANTITY(i),
1156 OB_SO_BASIS(i),
1157 OB_OPTIONAL(i),
1158 OB_MUTUALLY_EXCLUSIVE_OPTIONS(i),
1159 OB_CHECK_ATP(i),
1160 OB_SHIPPING_ALLOWED(i),
1161 OB_REQUIRED_TO_SHIP(i),
1162 OB_REQUIRED_FOR_REVENUE(i),
1163 OB_INCLUDE_ON_SHIP_DOCS(i),
1164 OB_INCLUDE_ON_BILL_DOCS(i),
1165 OB_LOW_QUANTITY(i),
1166 OB_HIGH_QUANTITY(i),
1167 OB_PICK_COMPONENTS(i),
1168 OB_PRIMARY_UOM_CODE(i),
1169 OB_PRIMARY_UNIT_OF_MEASURE(i),
1170 OB_BASE_ITEM_ID(i),
1171 OB_ATP_COMPONENTS_FLAG(i),
1172 OB_ATP_FLAG(i),
1173 OB_BOM_ITEM_TYPE(i),
1174 OB_PICK_COMPONENTS_FLAG(i),
1175 OB_REPLENISH_TO_ORDER_FLAG(i),
1176 OB_SHIPPABLE_ITEM_FLAG(i),
1177 OB_CUSTOMER_ORDER_FLAG(i),
1178 OB_INTERNAL_ORDER_FLAG(i),
1179 OB_CUSTOMER_ORDER_ENABLED_FLAG(i),
1180 OB_INTERNAL_ORDER_ENABLED_FLAG(i),
1181 OB_SO_TRANSACTIONS_FLAG(i),
1182 OB_DESCRIPTION(i),
1183 OB_ASSEMBLY_ITEM_ID(i),
1184 OB_COMPONENT_CODE(i),
1185 OB_LOOP_FLAG(i),
1186 OB_PARENT_BOM_ITEM_TYPE(i),
1187 OB_OPERATION_SEQ_NUM(i),
1188 OB_ITEM_NUM(i),
1189 OB_EFFECTIVITY_DATE(i),
1190 OB_DISABLE_DATE(i),
1191 OB_IMPLEMENTATION_DATE(i),
1192 OB_REXPLODE_FLAG(i),
1193 OB_COMMON_BILL_SEQUENCE_ID(i),
1194 OB_COMP_BILL_SEQ_ID(i),
1195 OB_COMP_COMMON_BILL_SEQ_ID(i),
1196 OB_AUTO_REQUEST_MATERIAL(i),
1197 OB_SOURCE_BILL_SEQUENCE_ID(i),
1198 OB_COMMON_COMPONENT_SEQ_ID(i),
1199 OB_COMP_SOURCE_BILL_SEQ_ID(i),
1200 substr(OB_SORT_ORDER(i), 0 ,length(OB_SORT_ORDER(i)) - X_SortWidth ) );
1201 /* Bug 6407303 Added the new attribute parent_sort_order*/
1202 exit when Loop_Count_Val < G_MAX_BATCH_FETCH_SIZE;
1203 End loop;
1204 /* End of Bulk Fetch . Exit when all components are inserted for that level */
1205
1206 close ordered_bill; -- Close the cursor
1207
1208 X_MoreLevels := true;
1209
1210 End If; -- explode next level
1211
1212
1213
1214 stmt_num := 80;
1215 /* Update the current level level so that the next iteration does not pick the
1216 the rows
1217 */
1218 UPDATE bom_explosions be
1219 SET be.rexplode_flag = 0
1220 WHERE be.plan_level = l_plan_level
1221 AND be.top_bill_sequence_id = x_top_bill_id
1222 AND be.explosion_type = arg_expl_type
1223 AND be.rexplode_flag = 1;
1224
1225
1226 l_plan_level := l_plan_level + 1;
1227
1228 IF (l_plan_level > arg_levels_to_explode)
1229 THEN
1230 X_MoreLevels := false;
1231 END IF;
1232 --End loop; -- get flags
1233 End loop; -- more levels
1234
1235 arg_error_code := 0;
1236 arg_err_msg := '';
1237
1238 commit;
1239
1240 EXCEPTION
1241 WHEN DUP_VAL_ON_INDEX THEN
1242 arg_error_code := SQLCODE;
1243 arg_err_msg := 'BOMORXPB Duplicate(' || stmt_num ||'): ' ||
1244 substrb(SQLERRM,1,60);
1245 ROLLBACK ;
1246 WHEN x_loop_detected THEN
1247 arg_error_code := 9999;
1248 FND_MESSAGE.Set_Name('BOM', 'BOM_LOOP_EXISTS');
1249 arg_err_msg := FND_MESSAGE.GET;
1250 ROLLBACK; --bug 2709042
1251 WHEN x_no_top_assy THEN
1252 arg_error_code := 9998;
1253 FND_MESSAGE.Set_Name('BOM', 'BOM_BILL_DOES_NOT_EXIST');
1254 arg_err_msg := FND_MESSAGE.GET;
1255 ROLLBACK; --bug 2709042
1256 WHEN x_bom_expl_del THEN
1257 arg_error_code := 9997;
1258 FND_MESSAGE.Set_Name('BOM', 'BOM_EXPL_DEL_IN_PROGRESS');
1259 FND_MESSAGE.Set_Token('REQUEST_ID', x_req_id);
1260 arg_err_msg := FND_MESSAGE.GET;
1261 ROLLBACK; --bug 2709042
1262 WHEN OTHERS THEN
1263 arg_error_code := SQLCODE;
1264 arg_err_msg := 'BOMORXPB(' || stmt_num ||'): ' ||
1265 substrb(SQLERRM,1,60);
1266 --ROLLBACK TO SAVEPOINT BE;
1267 ROLLBACK; --bug 2709042
1268
1269 END be_exploder;
1270
1271
1272 PROCEDURE delete_config_exp (
1273 arg_session_id IN NUMBER
1274 ) is
1275
1276 BEGIN
1277 DELETE FROM BOM_CONFIG_EXPLOSIONS
1278 WHERE SESSION_ID = arg_session_id;
1279 END delete_config_exp;
1280
1281 END bom_oe_exploder_pkg;