[Home] [Help]
PACKAGE BODY: APPS.BOMPEXPL
Source
1 package body bompexpl as
2 /* $Header: BOMEXPLB.pls 120.12.12010000.3 2009/01/12 12:26:06 rrajkule ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMPEXPL.sql |
10 | DESCRIPTION : This file is a packaged procedure for the exploders.
11 | This package contains 3 different exploders for the
12 | modules it can be called from. The procedure exploders
13 | calls the correct exploder based on the module option.
14 | Each of the 3 exploders can be called on directly too.
15 | Parameters: org_id organization_id
16 | order_by 1 - Op seq, item seq
17 | 2 - Item seq, op seq
18 | grp_id unique value to identify current explosion
19 | use value from sequence bom_explosion_temp_s
20 | session_id unique value to identify current session
21 | use value from bom_explosion_temp_session_s
22 | levels_to_explode
23 | bom_or_eng 1 - BOM
24 | 2 - ENG
25 | impl_flag 1 - implemented only
26 | 2 - both impl and unimpl
27 | explode_option 1 - All
28 | 2 - Current
29 | 3 - Current and future
30 | module 1 - Costing
31 | 2 - Bom
32 | 3 - Order entry
33 | 4 - ATO
34 | 5 - WSM
35 | cst_type_id cost type id for costed explosion
36 | std_comp_flag 1 - explode only standard components
37 | 2 - all components
38 | expl_qty explosion quantity
39 | item_id item id of asembly to explode
40 | list_id unique id for lists in bom_lists for range
41 | report_option 1 - cost rollup with report
42 | 2 - cost rollup no report
43 | 3 - temp cost rollup with report
44 | cst_rlp_id rollup_id
45 | req_id request id
46 | prgm_appl_id program application id
47 | prg_id program id
48 | user_id user id
49 | lock_flag 1 - do not lock the table
50 | 2 - lock the table
51 | alt_rtg_desg alternate routing designator
52 | rollup_option 1 - single level rollup
53 | 2 - full rollup
54 | plan_factor_flag1 - Yes
55 | 2 - No
56 | incl_lt_flag 1 - Yes
57 | 2 - No
58 | alt_desg alternate bom designator
59 | rev_date explosion date YYYY/MM/DD HH24:MI:SS
60 | comp_code concatenated component code lpad 16
61 | err_msg error message out buffer
62 | error_code error code out. returns sql error code
63 | if sql error
64 | Revision
65 Shreyas Shah creation
66 02/10/94 Shreyas Shah added multi-org capability from bom_lists
67 max_bom_levels of all orgs for multi-org
68 03/24/94 Shreyas Shah added 4 to module parameter so that
69 if ATO calls it dont commit but if CST
70 calls it then commit data
71 10/19/95 Robert Yee Added lead time flags
72 | 09/05/96 Robert Yee Increase Sort Order Width to 4 from 3 |
73 | (Bills can have >= 1000 components |
74 | 09/20/97 Robert Yee Use depth first search for loop check |
75 | 04/15/02 Rahul Chitko Added a new value for module. Module = 5 |
76 | added for WSM. When the calling application |
77 | is WSM, the process will only explode sub- |
78 | assemblies that are Phantom.
79 | 07/14/04 Refai Farook Modified the depth first logic into breadth first.
80 | Implemented bulk.
81 | 15-Jun-05 Hari Gelli Reverted the populating the component code to 11.5.10 style.
82 +==========================================================================*/
83
84 -- globals for loop checking
85 Type StackTabType is table of number index by binary_integer;
86 G_Yes constant number := 1;
87 G_No constant number := 2;
88 G_LoopErrorCode constant number := 9999;
89 G_MaxLevelCode constant number := 9998;
90 -- G_SortWidth constant number := 7;
91 G_SortWidth constant number := Bom_Common_Definitions.G_Bom_SortCode_Width;
92
93 -- Added new parameter which will decide if trimmed dates need to be populated
94 -- to the explosions table. If the flag is not set, the effectivity_date and
95 -- disable date in Bom-Inventory_components table will only be populated even
96 -- when explosion_type is 'ALL'
97
98 G_Allow_Date_Trimming_Flag Varchar2(1) := 'N';
99 G_Module Number := 2;
100
101
102 /**************************************************************************************/
103
104 g_parent_sort_order VARCHAR2(2000) := lpad('1', G_SortWidth, '0');
105 g_sort_count NUMBER := 0;
106
107 TYPE G_VARCHAR2_TBL_TYPE_2000 IS TABLE OF VARCHAR2(2000)
108 INDEX BY BINARY_INTEGER;
109
110 TYPE G_NUMBER_TBL_TYPE IS TABLE OF NUMBER
111 INDEX BY BINARY_INTEGER;
112
113 g_parent_sort_order_tbl G_VARCHAR2_TBL_TYPE_2000;
114 g_quantity_of_children_tbl G_NUMBER_TBL_TYPE;
115 g_total_qty_at_next_level_tbl G_NUMBER_TBL_TYPE;
116
117 g_global_count NUMBER := 1;
118 g_total_quantity NUMBER := 0;
119
120
121 PROCEDURE Reset_Globals IS
122 BEGIN
123
124 /* Reset all the globally used values */
125
126 g_quantity_of_children_tbl.DELETE;
127 g_total_qty_at_next_level_tbl.DELETE;
128 g_parent_sort_order_tbl.DELETE;
129 g_global_count := 1;
130 g_total_quantity := 0;
131 g_sort_count := 0;
132 g_parent_sort_order := '0000001';
133
134 END;
135
136 FUNCTION Get_Sort_Order (p_parent_sort_order IN VARCHAR2,
137 p_component_quantity IN NUMBER := NULL)
138 RETURN VARCHAR2 IS
139
140 BEGIN
141
142 IF p_parent_sort_order <> g_parent_sort_order THEN
143
144 g_parent_sort_order_tbl(g_global_count) := g_parent_sort_order;
145 g_quantity_of_children_tbl(g_global_count) := g_sort_count;
146 g_total_qty_at_next_level_tbl(g_global_count) := g_total_quantity;
147
148 g_sort_count := 0;
149 g_total_quantity := 0;
150 g_parent_sort_order := p_parent_sort_order;
151 g_global_count := g_global_count + 1;
152
153 END IF;
154
155 g_sort_count := g_sort_count + 1;
156 g_total_quantity := g_total_quantity + p_component_quantity;
157
158 Return (g_parent_sort_order||lpad(to_char(g_sort_count), G_SortWidth, '0'));
159
160 END;
161
162 PROCEDURE bom_exploder(
163 verify_flag IN NUMBER DEFAULT 0,
164 online_flag IN NUMBER DEFAULT 1,
165 top_bill_id IN NUMBER,
166 org_id IN NUMBER,
167 order_by IN NUMBER DEFAULT 1,
168 grp_id IN NUMBER,
169 levels_to_explode IN NUMBER DEFAULT 1,
170 bom_or_eng IN NUMBER DEFAULT 1,
171 impl_flag IN NUMBER DEFAULT 1,
172 plan_factor_flag IN NUMBER DEFAULT 2,
173 explode_option IN NUMBER DEFAULT 2,
174 std_comp_flag IN NUMBER DEFAULT 2,
175 incl_oc_flag IN NUMBER DEFAULT 1,
176 max_level IN NUMBER,
177 rev_date IN DATE DEFAULT sysdate,
178 show_rev IN NUMBER DEFAULT 2,
179 material_ctrl IN NUMBER DEFAULT 2,
180 lead_time IN NUMBER DEFAULT 2,
181 unit_number IN VARCHAR2,
182 release_option IN NUMBER DEFAULT 0,
183 err_msg OUT NOCOPY VARCHAR2,
184 error_code OUT NOCOPY NUMBER) IS
185
186 prev_sort_order VARCHAR2(4000);
187 prev_top_bill_id NUMBER;
188 cum_count NUMBER;
189 total_rows NUMBER;
190 cat_sort VARCHAR2(7);
191 impl_eco varchar2(20);
192
193 -- verify local vars
194 cur_component VARCHAR2(20);
195 cur_substr VARCHAR2(20);
196 cur_loopstr VARCHAR2(4000);
197 cur_loopflag VARCHAR2(1);
198 loop_found BOOLEAN := false;
199 max_level_exceeded BOOLEAN := false;
200 start_pos NUMBER;
201 end_pos NUMBER;
202
203
204 CURSOR exploder (
205 c_level NUMBER,
206 c_grp_id NUMBER,
207 c_org_id NUMBER,
208 c_bom_or_eng NUMBER,
209 c_rev_date date,
210 c_impl_flag NUMBER,
211 c_explode_option NUMBER,
212 c_order_by NUMBER,
213 c_verify_flag NUMBER,
214 c_plan_factor_flag NUMBER,
215 c_std_comp_flag NUMBER,
216 c_incl_oc NUMBER ) IS
217 SELECT
218 BET.TOP_BILL_SEQUENCE_ID TBSI,
219 BOM.BILL_SEQUENCE_ID BSI,
220 BOM.COMMON_BILL_SEQUENCE_ID CBSI,
221 BOM.COMMON_ORGANIZATION_ID COI,
222 BOM.ORGANIZATION_ID OI,
223 BIC.COMPONENT_SEQUENCE_ID CSI,
224 BIC.COMPONENT_ITEM_ID CID,
225 BIC.BASIS_TYPE BT,
226 BIC.COMPONENT_QUANTITY CQ,
227 c_level,
228 (BIC.COMPONENT_QUANTITY *
229 DECODE(BIC.BASIS_TYPE, null,BET.EXTENDED_QUANTITY,1) *
230 decode(c_plan_factor_flag, 1, BIC.PLANNING_FACTOR/100, 1) /
231 decode(BIC.COMPONENT_YIELD_FACTOR, 0, 1,
232 BIC.COMPONENT_YIELD_FACTOR)) EQ,
233 BET.SORT_ORDER SO,
234 c_grp_id,
235 BET.TOP_ALTERNATE_DESIGNATOR TAD,
236 BIC.COMPONENT_YIELD_FACTOR CYF,
237 BET.TOP_ITEM_ID TID,
238 BET.COMPONENT_CODE CC,
239 BIC.INCLUDE_IN_COST_ROLLUP IICR,
240 BET.LOOP_FLAG LF,
241 BIC.PLANNING_FACTOR PF,
242 BIC.OPERATION_SEQ_NUM OSN,
243 BIC.BOM_ITEM_TYPE BIT,
244 BET.BOM_ITEM_TYPE PBIT,
245 BET.COMPONENT_ITEM_ID PAID,
246 BOM.ALTERNATE_BOM_DESIGNATOR,
247 BIC.WIP_SUPPLY_TYPE WST,
248 BIC.ITEM_NUM ITN,
249 DECODE(G_Allow_Date_Trimming_Flag,'N',BIC.EFFECTIVITY_DATE,Greatest(BIC.EFFECTIVITY_DATE,Nvl(BET.EFFECTIVITY_DATE,BIC.EFFECTIVITY_DATE))) ED,
250 DECODE(G_Allow_Date_Trimming_Flag,'N',BIC.DISABLE_DATE,Least(Nvl(BIC.DISABLE_DATE,BET.DISABLE_DATE),Nvl(BET.DISABLE_DATE,BIC.DISABLE_DATE))) DD,
251 --BIC.EFFECTIVITY_DATE ED,
252 --BIC.DISABLE_DATE DD,
253 BIC.FROM_END_ITEM_UNIT_NUMBER FUN,
254 BIC.TO_END_ITEM_UNIT_NUMBER EUN,
255 BIC.IMPLEMENTATION_DATE ID,
256 BIC.OPTIONAL OPT,
257 BIC.SUPPLY_SUBINVENTORY SS,
258 BIC.SUPPLY_LOCATOR_ID SLI,
259 BIC.COMPONENT_REMARKS CR,
260 BIC.CHANGE_NOTICE CN,
261 BIC.OPERATION_LEAD_TIME_PERCENT OLTP,
262 BIC.MUTUALLY_EXCLUSIVE_OPTIONS MEO,
263 BIC.CHECK_ATP CATP,
264 BIC.REQUIRED_TO_SHIP RTS,
265 BIC.REQUIRED_FOR_REVENUE RFR,
266 BIC.INCLUDE_ON_SHIP_DOCS IOSD,
267 BIC.LOW_QUANTITY LQ,
268 BIC.HIGH_QUANTITY HQ,
269 BIC.SO_BASIS SB,
270 --BET.OPERATION_OFFSET,
271 --BET.CURRENT_REVISION,
272 --BET.LOCATOR,
273 BIC.ATTRIBUTE_CATEGORY,
274 BIC.ATTRIBUTE1,
275 BIC.ATTRIBUTE2,
276 BIC.ATTRIBUTE3,
277 BIC.ATTRIBUTE4,
278 BIC.ATTRIBUTE5,
279 BIC.ATTRIBUTE6,
280 BIC.ATTRIBUTE7,
281 BIC.ATTRIBUTE8,
282 BIC.ATTRIBUTE9,
283 BIC.ATTRIBUTE10,
284 BIC.ATTRIBUTE11,
285 BIC.ATTRIBUTE12,
286 BIC.ATTRIBUTE13,
287 BIC.ATTRIBUTE14,
288 BIC.ATTRIBUTE15,
289 BET.SORT_ORDER PARENT_SORT_ORDER,
290 BIC.AUTO_REQUEST_MATERIAL
291 FROM BOM_EXPLOSION_TEMP BET, BOM_BILL_OF_MATERIALS BOM,
292 MTL_SYSTEM_ITEMS_B SI,
293 BOM_INVENTORY_COMPONENTS BIC,
294 ENG_REVISED_ITEMS ERI
295 WHERE BET.PLAN_LEVEL = c_level - 1
296 AND BET.GROUP_ID = c_grp_id
297 AND BET.TOP_BILL_SEQUENCE_ID = top_bill_id
298 AND BOM.ASSEMBLY_ITEM_ID = SI.INVENTORY_ITEM_ID
299 AND BOM.ORGANIZATION_ID = SI.ORGANIZATION_ID
300 AND BOM.COMMON_BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
301 AND BET.COMPONENT_ITEM_ID = BOM.ASSEMBLY_ITEM_ID
302 AND BET.ORGANIZATION_ID = BOM.ORGANIZATION_ID -- Bug 7159394 .. Reverting fix 6707314
303 --AND BOM.ORGANIZATION_ID = decode(BET.COMMON_BILL_SEQUENCE_ID,BET.BILL_SEQUENCE_ID,BET.ORGANIZATION_ID,BET.COMMON_ORGANIZATION_ID) /* Bug: 6707314 */
304 AND NVL(BIC.ECO_FOR_PRODUCTION,2) = 2
305 AND (G_Module <> 5 OR (G_Module = 5 AND (nvl(BET.wip_supply_type, si.wip_supply_type) = 6 /*Added nvl for bug 7700219 (FP of 7638607)*/
306 OR BET.PLAN_LEVEL = 0
307 )
308 )
309 )
310 --Explode only Phantom components when module=5
311 AND ( (c_std_comp_flag = 1 -- only std components
312 AND BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 2
313 )
314 OR
315 (c_std_comp_flag = 2)
316 OR
317 (c_std_comp_flag = 3 AND nvl(BET.BOM_ITEM_TYPE, 1) IN (1,2)
318 AND (BIC.BOM_ITEM_TYPE IN (1,2)
319 OR
320 (BIC.BOM_ITEM_TYPE = 4 AND BIC.OPTIONAL = 1)
321 )
322 )
323 )
324 AND ( (c_bom_or_eng = 1 and BOM.ASSEMBLY_TYPE = 1)
325 OR
326 (c_bom_or_eng = 2)
327 )
328 AND (
329 (BET.TOP_ALTERNATE_DESIGNATOR IS NULL
330 AND
331 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
332 )
333 OR
334 (BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
335 AND
336 BOM.ALTERNATE_BOM_DESIGNATOR=BET.TOP_ALTERNATE_DESIGNATOR
337 )
338 OR
339 ( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
340 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL
341 AND NOT EXISTS
342 (SELECT 'X'
343 FROM BOM_BILL_OF_MATERIALS BOM2
344 WHERE BOM2.ORGANIZATION_ID = c_org_id
345 AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
346 AND BOM2.ALTERNATE_BOM_DESIGNATOR =
347 BET.TOP_ALTERNATE_DESIGNATOR
348 AND ((c_bom_or_eng = 1 and BOM2.ASSEMBLY_TYPE = 1)
349 OR c_bom_or_eng = 2
350 )
351 ) -- subquery
352 )
353 ) -- end of alt logic
354 -- whether to include option classes and models under a standard item
355 -- special logic added at CST request
356 AND ( (c_incl_oc = 1)
357 or
358 (c_incl_oc = 2 AND
359 ( BET.BOM_ITEM_TYPE = 4 AND BIC.BOM_ITEM_TYPE = 4)
360 OR
361 ( BET.BOM_ITEM_TYPE <> 4)
362 )
363 )
364 -- do not explode if immediate parent is standard and current
365 -- component is option class or model - special logic for config items
366 AND NOT ( BET.PARENT_BOM_ITEM_TYPE = 4
367 AND
368 BET.BOM_ITEM_TYPE IN (1,2)
369 )
370 AND (
371 ( NVL(SI.EFFECTIVITY_CONTROL,1) = 2
372 AND ((c_explode_option = 1) -- ALL
376 AND unit_number_from <=
373 OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL)
374 )
375 /*
377 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,unit_number_from)
378 AND unit_number_to >= BIC.FROM_END_ITEM_UNIT_NUMBER
379 AND BIC.FROM_END_ITEM_UNIT_NUMBER <=
380 NVL(BET.TO_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
381 AND
382 NVL(BIC.TO_END_ITEM_UNIT_NUMBER,
383 NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)) >=
384 NVL(BET.FROM_END_ITEM_UNIT_NUMBER,BIC.FROM_END_ITEM_UNIT_NUMBER)
385 AND
386 ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
387 OR
388 c_impl_flag = 2 )*/
389
390 AND BIC.from_end_item_unit_number IS NOT NULL
391 AND ( (c_explode_option = 2
392 AND unit_number >= BIC.from_end_item_unit_number
393 AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
394 OR
395 (c_explode_option = 3
396 AND unit_number <= Nvl( BIC.to_end_item_unit_number, unit_number))
397 )
398 AND ( (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
399 OR c_impl_flag = 2 )
400 )
401 OR
402 (
403 NVL(SI.EFFECTIVITY_CONTROL,1) =1
404 AND
405 ( --(c_explode_option = 1 ) -- ALL
406
407 (c_explode_option = 1 /* ALL */ /* When option is all, level 0 should pick all comps */
408 AND ( (c_level-1 = 0) OR /* but the subsequent levels should continue to narrow */
409 ( bic.effectivity_date <= nvl(bet.disable_date, bic.effectivity_date)
410 AND NVL(bic.disable_date, bet.effectivity_date) >= bet.effectivity_date) ) )
411 OR
412 (c_explode_option = 2 AND -- CURRENT
413 c_rev_date >=
414 BIC.EFFECTIVITY_DATE AND
415 c_rev_date < -- Bug #3138456
416 nvl(BIC.DISABLE_DATE,
417 c_rev_date+1)
418 ) -- CURRENT
419 OR
420 (c_explode_option = 3 -- CURRENT AND FUTURE
421 AND nvl(BIC.DISABLE_DATE, c_rev_date + 1) > c_rev_date
422 /* Modified above line for Bug #3138456 */
423 ) -- CURRENT AND FUTURE
424 )
425 AND ( (c_impl_flag = 2 AND
426 ( c_explode_option = 1
427 OR
428 (c_explode_option = 2 AND not exists
429 (SELECT null
430 FROM BOM_INVENTORY_COMPONENTS CIB,
431 ENG_REVISED_ITEMS ERI2
432 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
433 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
434 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
435 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
436 CIB.OLD_COMPONENT_SEQUENCE_ID,
437 CIB.COMPONENT_SEQUENCE_ID) =
438 decode(BIC.IMPLEMENTATION_DATE, NULL,
439 BIC.OLD_COMPONENT_SEQUENCE_ID,
440 BIC.COMPONENT_SEQUENCE_ID)
441 OR
442 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
443 ) -- decode
444 AND CIB.EFFECTIVITY_DATE <=
445 c_rev_date
446 AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
447 AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+)
448 AND ( ( release_option = 1 AND nvl(ERI2.STATUS_TYPE,6) IN (4,6,7) )
449 OR
450 ( release_option = 2 AND nvl(ERI2.STATUS_TYPE,6) IN (1,4,6,7))
451 OR
452 ( release_option = 0 AND nvl(ERI2.STATUS_TYPE,6) = 6 )
453 OR
454 (release_option = 3)
455 )
456 ) -- end of subquery
457 ) -- CURRENT
458 OR
459 (c_explode_option = 3 AND not exists
460 (SELECT null
461 FROM BOM_INVENTORY_COMPONENTS CIB,
462 ENG_REVISED_ITEMS ERI2
463 WHERE CIB.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
464 AND CIB.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
465 AND NVL(CIB.ECO_FOR_PRODUCTION,2) = 2
466 AND ( decode(CIB.IMPLEMENTATION_DATE, NULL,
467 CIB.OLD_COMPONENT_SEQUENCE_ID,
468 CIB.COMPONENT_SEQUENCE_ID) =
469 decode(BIC.IMPLEMENTATION_DATE, NULL,
470 BIC.OLD_COMPONENT_SEQUENCE_ID,
471 BIC.COMPONENT_SEQUENCE_ID)
472 OR
473 CIB.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
474 ) -- decode
475 AND CIB.EFFECTIVITY_DATE <=
476 c_rev_date
477 AND BIC.EFFECTIVITY_DATE < cib.EFFECTIVITY_DATE
478 AND CIB.REVISED_ITEM_SEQUENCE_ID = ERI2.REVISED_ITEM_SEQUENCE_ID (+)
479 AND ( ( release_option = 1 AND nvl(ERI2.STATUS_TYPE,6) IN (4,6,7) )
480 OR
481 ( release_option = 2 AND nvl(ERI2.STATUS_TYPE,6) IN (1,4,6,7))
482 OR
483 ( release_option = 0 AND nvl(ERI2.STATUS_TYPE,6) = 6 )
484 OR
485 (release_option = 3)
486 )
487 ) -- end of subquery
491 ) -- explode_option
488 OR BIC.EFFECTIVITY_DATE >
489 c_rev_date
490 ) -- CURRENT AND FUTURE
492 ) -- impl_flag = 2
493 OR
494 (c_impl_flag = 1 AND BIC.IMPLEMENTATION_DATE IS NOT NULL)
495 ) -- explode option
496 )
497 )
498 AND BET.LOOP_FLAG = 2
499 AND BIC.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID (+)
500 AND (
501 ( release_option = 1
502 AND nvl(ERI.STATUS_TYPE,6) IN (4,6,7)
503 )
504 OR
505 ( release_option = 2
506 AND nvl(ERI.STATUS_TYPE,6) IN (1,4,6,7)
507 )
508 OR
509 (
510 release_option = 0
511 AND nvl(ERI.STATUS_TYPE,6) = 6
512 )
513 OR
514 (release_option = 3)
515 )
516 ORDER BY BET.TOP_BILL_SEQUENCE_ID, BET.SORT_ORDER,
517 decode(c_order_by, 1, BIC.OPERATION_SEQ_NUM, BIC.ITEM_NUM),
518 decode(c_order_by, 1, BIC.ITEM_NUM, BIC.OPERATION_SEQ_NUM);
519
520 Cursor Get_Locator (P_Locator in number) is
521 Select mil.concatenated_segments
522 From mtl_item_locations_kfv mil
523 Where mil.inventory_location_id = P_Locator;
524
525 Cursor Get_OLTP (P_Assembly in number,
526 P_Alternate in varchar2,
527 P_Operation in number) is
528 Select round(bos.operation_lead_time_percent, 2) oltp
529 From Bom_Operation_Sequences bos,
530 Bom_Operational_Routings bor
531 Where bor.assembly_item_id = P_Assembly
532 And bor.organization_Id = org_id
533 And (bor.alternate_routing_designator = P_Alternate
534 or
535 (bor.alternate_routing_designator is null AND not exists (
536 SELECT null
537 FROM bom_operational_routings bor2
538 WHERE bor2.assembly_item_id = P_Assembly
539 AND bor2.organization_id = org_id
540 AND bor2.alternate_routing_designator = P_Alternate)
541 ))
542 And bor.common_routing_sequence_id = bos.routing_sequence_id
543 And bos.operation_seq_num = P_Operation
544 And bos.effectivity_date <=
545 trunc(rev_date)
546 And nvl(bos.disable_date,
547 rev_date + 1) >=
548 trunc(rev_date);
549
550 Cursor Calculate_Offset(P_ParentItem in number, P_Percent in number) is
551 Select P_Percent/100 * msi.full_lead_time offset
552 From mtl_system_items_b msi
553 Where msi.inventory_item_id = P_ParentItem
554 And msi.organization_id = Org_Id;
555
556 No_Revision_Found exception;
557 Pragma exception_init(no_revision_found, -20001);
558
559 Cursor l_TopBill_csr is
560 Select msi.concatenated_segments,
561 bom.alternate_bom_designator
562 From mtl_system_items_b_kfv msi,
563 bom_bill_of_materials bom,
564 BOM_EXPLOSION_TEMP bet
565 Where msi.inventory_item_id = bom.assembly_item_id
566 And msi.organization_id = bom.organization_id
567 And bom.bill_sequence_id = bet.top_bill_sequence_id
568 And bet.group_id = grp_id
569 And rownum = 1;
570
571 total number;
572
573 CURSOR getItemRevDetails (p_revision_id IN NUMBER) IS
574 SELECT revision_id, revision, revision_label FROM mtl_item_revisions_vl WHERE revision_id = p_revision_id;
575
576
577 CURSOR getItemRevision (p_inventory_item_id IN NUMBER,
578 p_organization_id IN NUMBER,
579 p_revision_date IN DATE,
580 p_impl_flag IN NUMBER) IS
581 SELECT revision,revision_label,revision_id
582 FROM mtl_item_revisions_b MIR
583 WHERE mir.inventory_item_id = p_inventory_item_id
584 AND mir.organization_id = p_organization_id
585 AND mir.effectivity_date <= p_revision_date
586 AND (p_impl_flag = 2 OR (p_impl_flag = 1 AND mir.implementation_date IS NOT NULL) )
587 ORDER BY mir.effectivity_date DESC;
588
589 l_revision_id NUMBER;
590 l_revision_label VARCHAR2(100);
591 l_revision VARCHAR2(10);
592
593 l_comp_common_bill_seq_id NUMBER;
594
595 /*
596 TYPE be_temp_TYPE IS TABLE OF bom_plm_explosion_temp%ROWTYPE;
597 be_temp_TBL be_temp_TYPE;
598 */
599
600 l_batch_size NUMBER := 20000;
601
602 /* Declare pl/sql tables for all coulmns in the select list. BULK BIND and INSERT with
603 pl/sql table of records work fine in 9i releases but not in 8i. So, the only option is
604 to use individual pl/sql table for each column in the cursor select list */
605
606
607 TYPE NUMBER_TBL_TYPE IS TABLE OF NUMBER
608 INDEX BY BINARY_INTEGER;
609
610 TYPE DATE_TBL_TYPE IS TABLE OF DATE
611 INDEX BY BINARY_INTEGER;
612
613 /* Declared seperate tables based on the column size since pl/sql preallocates the memory for the varchar variable
614 when it is lesser than 2000 chars */
615
616 /*
617 TYPE VARCHAR2_TBL_TYPE IS TABLE OF VARCHAR2(2000)
618 INDEX BY BINARY_INTEGER;
619 */
620
621 TYPE VARCHAR2_TBL_TYPE_1 IS TABLE OF VARCHAR2(1)
622 INDEX BY BINARY_INTEGER;
626
623
624 TYPE VARCHAR2_TBL_TYPE_3 IS TABLE OF VARCHAR2(3)
625 INDEX BY BINARY_INTEGER;
627 TYPE VARCHAR2_TBL_TYPE_10 IS TABLE OF VARCHAR2(10)
628 INDEX BY BINARY_INTEGER;
629
630 TYPE VARCHAR2_TBL_TYPE_20 IS TABLE OF VARCHAR2(20)
631 INDEX BY BINARY_INTEGER;
632
633 TYPE VARCHAR2_TBL_TYPE_25 IS TABLE OF VARCHAR2(25)
634 INDEX BY BINARY_INTEGER;
635
636 TYPE VARCHAR2_TBL_TYPE_30 IS TABLE OF VARCHAR2(30)
637 INDEX BY BINARY_INTEGER;
638
639 TYPE VARCHAR2_TBL_TYPE_40 IS TABLE OF VARCHAR2(40)
640 INDEX BY BINARY_INTEGER;
641
642 TYPE VARCHAR2_TBL_TYPE_80 IS TABLE OF VARCHAR2(80)
643 INDEX BY BINARY_INTEGER;
644
645 TYPE VARCHAR2_TBL_TYPE_150 IS TABLE OF VARCHAR2(150)
646 INDEX BY BINARY_INTEGER;
647
648 TYPE VARCHAR2_TBL_TYPE_240 IS TABLE OF VARCHAR2(240)
649 INDEX BY BINARY_INTEGER;
650
651 TYPE VARCHAR2_TBL_TYPE_260 IS TABLE OF VARCHAR2(260)
652 INDEX BY BINARY_INTEGER;
653
654 TYPE VARCHAR2_TBL_TYPE_1000 IS TABLE OF VARCHAR2(1000)
655 INDEX BY BINARY_INTEGER;
656
657 TYPE VARCHAR2_TBL_TYPE_2000 IS TABLE OF VARCHAR2(2000)
658 INDEX BY BINARY_INTEGER;
659
660 TYPE VARCHAR2_TBL_TYPE_4000 IS TABLE OF VARCHAR2(4000)
661 INDEX BY BINARY_INTEGER;
662
663 top_bill_sequence_id_tbl NUMBER_TBL_TYPE;
664 bill_sequence_id_tbl NUMBER_TBL_TYPE;
665 common_bill_sequence_id_tbl NUMBER_TBL_TYPE;
666 common_organization_id_tbl NUMBER_TBL_TYPE;
667 organization_id_tbl NUMBER_TBL_TYPE;
668 component_sequence_id_tbl NUMBER_TBL_TYPE;
669 component_item_id_tbl NUMBER_TBL_TYPE;
670 basis_type_tbl NUMBER_TBL_TYPE;
671 component_quantity_tbl NUMBER_TBL_TYPE;
672 plan_level_tbl NUMBER_TBL_TYPE;
673 extended_quantity_tbl NUMBER_TBL_TYPE;
674 sort_order_tbl VARCHAR2_TBL_TYPE_2000;
675 group_id_tbl NUMBER_TBL_TYPE;
676 top_alternate_designator_tbl VARCHAR2_TBL_TYPE_10;
677 component_yield_factor_tbl NUMBER_TBL_TYPE;
678 top_item_id_tbl NUMBER_TBL_TYPE;
679 component_code_tbl VARCHAR2_TBL_TYPE_1000;
680 include_in_cost_rollup_tbl NUMBER_TBL_TYPE;
681 loop_flag_tbl NUMBER_TBL_TYPE;
682 planning_factor_tbl NUMBER_TBL_TYPE;
683 operation_seq_num_tbl NUMBER_TBL_TYPE;
684 bom_item_type_tbl NUMBER_TBL_TYPE;
685 parent_bom_item_type_tbl NUMBER_TBL_TYPE;
686 parent_item_id_tbl NUMBER_TBL_TYPE;
687 alternate_bom_designator_tbl VARCHAR2_TBL_TYPE_10;
688 wip_supply_type_tbl NUMBER_TBL_TYPE;
689 item_num_tbl NUMBER_TBL_TYPE;
690 effectivity_date_tbl DATE_TBL_TYPE;
691 disable_date_tbl DATE_TBL_TYPE;
692 from_end_item_unit_number_tbl VARCHAR2_TBL_TYPE_30;
693 to_end_item_unit_number_tbl VARCHAR2_TBL_TYPE_30;
694 implementation_date_tbl DATE_TBL_TYPE;
695 optional_tbl NUMBER_TBL_TYPE;
696 supply_subinventory_tbl VARCHAR2_TBL_TYPE_10;
697 supply_locator_id_tbl NUMBER_TBL_TYPE;
698 component_remarks_tbl VARCHAR2_TBL_TYPE_240;
699 change_notice_tbl VARCHAR2_TBL_TYPE_10;
700 operation_leadtime_percent_tbl NUMBER_TBL_TYPE;
701 mutually_exclusive_options_tbl NUMBER_TBL_TYPE;
702 check_atp_tbl NUMBER_TBL_TYPE;
703 required_to_ship_tbl NUMBER_TBL_TYPE;
704 required_for_revenue_tbl NUMBER_TBL_TYPE;
705 include_on_ship_docs_tbl NUMBER_TBL_TYPE;
706 low_quantity_tbl NUMBER_TBL_TYPE;
707 high_quantity_tbl NUMBER_TBL_TYPE;
708 so_basis_tbl NUMBER_TBL_TYPE;
709 operation_offset_tbl NUMBER_TBL_TYPE;
710 current_revision_tbl VARCHAR2_TBL_TYPE_3;
711 primary_uom_code_tbl VARCHAR2_TBL_TYPE_3;
712 locator_tbl VARCHAR2_TBL_TYPE_40;
713 attribute_category_tbl VARCHAR2_TBL_TYPE_30;
714 attribute1_tbl VARCHAR2_TBL_TYPE_150;
715 attribute2_tbl VARCHAR2_TBL_TYPE_150;
716 attribute3_tbl VARCHAR2_TBL_TYPE_150;
717 attribute4_tbl VARCHAR2_TBL_TYPE_150;
718 attribute5_tbl VARCHAR2_TBL_TYPE_150;
719 attribute6_tbl VARCHAR2_TBL_TYPE_150;
720 attribute7_tbl VARCHAR2_TBL_TYPE_150;
721 attribute8_tbl VARCHAR2_TBL_TYPE_150;
722 attribute9_tbl VARCHAR2_TBL_TYPE_150;
723 attribute10_tbl VARCHAR2_TBL_TYPE_150;
724 attribute11_tbl VARCHAR2_TBL_TYPE_150;
725 attribute12_tbl VARCHAR2_TBL_TYPE_150;
726 attribute13_tbl VARCHAR2_TBL_TYPE_150;
727 attribute14_tbl VARCHAR2_TBL_TYPE_150;
728 attribute15_tbl VARCHAR2_TBL_TYPE_150;
729 component_item_revision_id_tbl NUMBER_TBL_TYPE;
730 parent_sort_order_tbl VARCHAR2_TBL_TYPE_2000;
734 bom_implementation_date_tbl DATE_TBL_TYPE;
731 assembly_type_tbl NUMBER_TBL_TYPE;
732 revision_label_tbl VARCHAR2_TBL_TYPE_260;
733 revision_id_tbl NUMBER_TBL_TYPE;
735 creation_date_tbl DATE_TBL_TYPE;
736 created_by_tbl NUMBER_TBL_TYPE;
737 last_update_date_tbl DATE_TBL_TYPE;
738 last_updated_by_tbl NUMBER_TBL_TYPE;
739 auto_request_material_tbl VARCHAR2_TBL_TYPE_3;
740
741
742 l_rows_fetched NUMBER := 0;
743
744 BEGIN
745 Reset_Globals;
746
747 FOR cur_level in 1..levels_to_explode
748 LOOP
749
750 --dbms_output.put_line('cur level is '||cur_level);
751
752 total_rows := 0;
753 cum_count := 0;
754
755 OPEN exploder (
756 cur_level,
757 grp_id,
758 org_id,
759 bom_or_eng,
760 rev_date,
761 impl_flag,
762 explode_option,
763 order_by,
764 verify_flag,
765 plan_factor_flag,
766 std_comp_flag,
767 incl_oc_flag
768 );
769
770 l_rows_fetched := 0;
771
772 LOOP
773
774 FETCH exploder BULK COLLECT INTO
775 top_bill_sequence_id_tbl ,
776 bill_sequence_id_tbl,
777 common_bill_sequence_id_tbl,
778 common_organization_id_tbl,
779 organization_id_tbl,
780 component_sequence_id_tbl,
781 component_item_id_tbl,
782 basis_type_tbl,
783 component_quantity_tbl,
784 plan_level_tbl,
785 extended_quantity_tbl,
786 sort_order_tbl,
787 group_id_tbl,
788 top_alternate_designator_tbl,
789 component_yield_factor_tbl ,
790 top_item_id_tbl,
791 component_code_tbl,
792 include_in_cost_rollup_tbl ,
793 loop_flag_tbl,
794 planning_factor_tbl,
795 operation_seq_num_tbl,
796 bom_item_type_tbl ,
797 parent_bom_item_type_tbl,
798 parent_item_id_tbl,
799 alternate_bom_designator_tbl,
800 wip_supply_type_tbl,
801 item_num_tbl,
802 effectivity_date_tbl,
803 disable_date_tbl,
804 from_end_item_unit_number_tbl,
805 to_end_item_unit_number_tbl ,
806 implementation_date_tbl,
807 optional_tbl,
808 supply_subinventory_tbl,
809 supply_locator_id_tbl,
810 component_remarks_tbl,
811 change_notice_tbl,
812 operation_leadtime_percent_tbl ,
813 mutually_exclusive_options_tbl ,
814 check_atp_tbl ,
815 required_to_ship_tbl,
816 required_for_revenue_tbl ,
817 include_on_ship_docs_tbl ,
818 low_quantity_tbl ,
819 high_quantity_tbl ,
820 so_basis_tbl ,
821 --operation_offset_tbl ,
822 --Current_revision_tbl ,
823 --locator_tbl ,
824 attribute_category_tbl ,
825 attribute1_tbl ,
826 attribute2_tbl ,
827 attribute3_tbl ,
828 attribute4_tbl ,
829 attribute5_tbl ,
830 attribute6_tbl ,
831 attribute7_tbl ,
832 attribute8_tbl ,
833 attribute9_tbl ,
834 attribute10_tbl ,
835 attribute11_tbl ,
836 attribute12_tbl ,
837 attribute13_tbl ,
838 attribute14_tbl ,
839 attribute15_tbl,
840 parent_sort_order_tbl,
841 auto_request_material_tbl LIMIT l_batch_size;
842
843 EXIT WHEN exploder%ROWCOUNT = l_rows_fetched;
844 l_rows_fetched := exploder%ROWCOUNT;
845
846 --dbms_output.put_line('Exploder Row count is '||exploder%ROWCOUNT);
847
848 FOR i IN 1..top_bill_sequence_id_tbl.COUNT
849 LOOP
850
851 --dbms_output.put_line('inside expl_rows');
852
853 IF cur_level > levels_to_explode THEN
854 IF cur_level > max_level THEN
855 max_level_exceeded := true;
856 END IF; -- exceed max level
857 exit; -- do not insert extra level
858 END IF; -- exceed lowest level
859
860 total_rows := total_rows + 1;
861
862 -- Get the sort order
863
864 --dbms_output.put_line('calling sort order : '||parent_sort_order_tbl(i));
865 sort_order_tbl(i) := Get_Sort_Order(parent_sort_order_tbl(i), component_quantity_tbl(i));
866
867 -- Get the component code
868
869 --dbms_output.put_line('Get the component code');
870
871 loop_found := FALSE;
872 cur_loopstr := component_code_tbl(i);
873
874 cur_component := component_item_id_tbl(i);
875
876 -- search the current loop_string for current component
877
878 start_pos := 1;
879 FOR i IN 1..cur_level LOOP
880
881 end_pos := INSTR(cur_loopstr, '-', start_pos,1);
882 IF end_pos = 0 THEN
883 end_pos := LENGTH(cur_loopstr);
884 ELSE
885 End_pos := end_pos-1;
886 END IF;
887
888 cur_substr := SUBSTR( cur_loopstr, start_pos, (end_pos-start_pos+1));
889
890 IF (cur_component = cur_substr) THEN
891 loop_found := TRUE;
892 EXIT;
893 END IF;
894 start_pos := end_pos + 2;
895 END LOOP;
896
897 component_code_tbl(i) := component_code_tbl(i) || '-' || cur_component;
898 IF loop_found THEN
902 END IF;
899 loop_flag_tbl(i) := 1;
900 ELSE
901 loop_flag_tbl(i) := 2;
903
904 --dbms_output.put_line('Get the revision');
905
906 --current_revision_tbl(i) := Null;
907
908 -- The following pieces are valid only IF the component row is an inventory item
909
910 /*
911 IF show_rev = 1 THEN
912
913 IF component_item_revision_id_tbl(i) IS NOT NULL THEN
914
915 FOR r1 IN getItemRevDetails(component_item_revision_id_tbl(i))
916 LOOP
917 revision_id_tbl(i) := component_item_revision_id_tbl(i);
918 current_revision_tbl(i) := r1.revision;
919 revision_label_tbl(i) := r1.revision_label;
920 END LOOP;
921
922 ELSE
923
924 FOR r1 IN getItemRevision(component_item_id_tbl(i),
925 nvl(common_organization_id_tbl(i),organization_id_tbl(i)),
926 rev_date,
927 impl_flag)
928 LOOP
929 revision_id_tbl(i) := r1.revision_id;
930 current_revision_tbl(i) := r1.revision;
931 revision_label_tbl(i) := r1.revision_label;
932 Exit;
933 END LOOP;
934
935 END IF; -- current component revision
936
937 END IF; -- show rev
938 */
939
940 --dbms_output.put_line('Get the locator');
941
942 locator_tbl(i) := Null;
943
944 IF material_ctrl = 1 THEN
945
946 IF FND_FLEX_KEYVAL.validate_ccid
947 (appl_short_name => 'INV',
948 key_flex_code => 'MTLL',
949 structure_number => 101,
950 combination_id => supply_locator_id_tbl(i),
951 displayable => 'ALL',
952 data_set => organization_id_tbl(i)
953 )
954 THEN
955 locator_tbl(i) := FND_FLEX_KEYVAL.concatenated_values ;
956 END IF;
957
958 END IF; -- supply locator
959
960 --dbms_output.put_line('Get the lead time');
961
962 operation_leadtime_percent_tbl(i) := Null;
963
964 FOR X_Operation in Get_OLTP(
965 P_Assembly => parent_item_id_tbl(i),
966 P_Alternate => alternate_bom_designator_tbl(i),
967 P_Operation => operation_seq_num_tbl(i))
968 LOOP
969 operation_leadtime_percent_tbl(i) := X_Operation.OLTP;
970 END LOOP;
971
972 /*
973 operation_offset_tbl(i) := Null;
974
975 IF lead_time = 1 THEN
976 For X_Item in Calculate_Offset(P_ParentItem => parent_item_id_tbl(i),
977 P_Percent => operation_leadtime_percent_tbl(i))
978 LOOP
979 operation_offset_tbl(i) := X_Item.offset;
980 END LOOP;
981 END IF; -- operation offset
982 */
983
984 END LOOP;
985
986 -- We are doing this to capture the values for the last parent
987 g_parent_sort_order_tbl(g_global_count) := g_parent_sort_order;
988 g_quantity_of_children_tbl(g_global_count) := g_sort_count;
989 g_total_qty_at_next_level_tbl(g_global_count) := g_total_quantity;
990
991 --dbms_output.put_line('O.K. enough. insert now');
992
993 FORALL i IN 1..top_bill_sequence_id_tbl.COUNT
994 INSERT INTO bom_explosion_temp
995 (
996 TOP_BILL_SEQUENCE_ID ,
997 BILL_SEQUENCE_ID ,
998 COMMON_BILL_SEQUENCE_ID ,
999 COMMON_ORGANIZATION_ID ,
1000 ORGANIZATION_ID ,
1001 COMPONENT_SEQUENCE_ID ,
1002 COMPONENT_ITEM_ID ,
1003 BASIS_TYPE ,
1004 COMPONENT_QUANTITY ,
1005 PLAN_LEVEL ,
1006 EXTENDED_QUANTITY ,
1007 SORT_ORDER ,
1008 GROUP_ID ,
1009 TOP_ALTERNATE_DESIGNATOR ,
1010 COMPONENT_YIELD_FACTOR ,
1011 TOP_ITEM_ID ,
1012 COMPONENT_CODE ,
1013 INCLUDE_IN_ROLLUP_FLAG ,
1014 LOOP_FLAG ,
1015 PLANNING_FACTOR ,
1016 OPERATION_SEQ_NUM ,
1017 BOM_ITEM_TYPE ,
1018 PARENT_BOM_ITEM_TYPE ,
1019 ASSEMBLY_ITEM_ID ,
1020 --ALTERNATE_BOM_DESIGNATOR ,
1021 WIP_SUPPLY_TYPE ,
1022 ITEM_NUM ,
1023 EFFECTIVITY_DATE ,
1024 DISABLE_DATE ,
1025 FROM_END_ITEM_UNIT_NUMBER ,
1026 TO_END_ITEM_UNIT_NUMBER ,
1027 IMPLEMENTATION_DATE ,
1028 OPTIONAL ,
1029 SUPPLY_SUBINVENTORY ,
1030 SUPPLY_LOCATOR_ID ,
1031 COMPONENT_REMARKS ,
1032 CHANGE_NOTICE ,
1033 OPERATION_LEAD_TIME_PERCENT ,
1034 MUTUALLY_EXCLUSIVE_OPTIONS ,
1035 CHECK_ATP ,
1036 REQUIRED_TO_SHIP ,
1037 REQUIRED_FOR_REVENUE ,
1038 INCLUDE_ON_SHIP_DOCS ,
1039 LOW_QUANTITY ,
1040 HIGH_QUANTITY ,
1041 SO_BASIS ,
1042 --OPERATION_OFFSET ,
1046 ATTRIBUTE1 ,
1043 --CURRENT_REVISION ,
1044 --LOCATOR ,
1045 CONTEXT ,
1047 ATTRIBUTE2 ,
1048 ATTRIBUTE3 ,
1049 ATTRIBUTE4 ,
1050 ATTRIBUTE5 ,
1051 ATTRIBUTE6 ,
1052 ATTRIBUTE7 ,
1053 ATTRIBUTE8 ,
1054 ATTRIBUTE9 ,
1055 ATTRIBUTE10 ,
1056 ATTRIBUTE11 ,
1057 ATTRIBUTE12 ,
1058 ATTRIBUTE13 ,
1059 ATTRIBUTE14 ,
1060 ATTRIBUTE15 ,
1061 --PARENT_SORT_ORDER ,
1062 AUTO_REQUEST_MATERIAL )
1063 VALUES
1064 (
1065 top_bill_sequence_id_tbl(i) ,
1066 bill_sequence_id_tbl(i) ,
1067 common_bill_sequence_id_tbl(i) ,
1068 common_organization_id_tbl(i) ,
1069 organization_id_tbl(i) ,
1070 component_sequence_id_tbl(i) ,
1071 component_item_id_tbl(i) ,
1072 basis_type_tbl(i) ,
1073 component_quantity_tbl(i) ,
1074 plan_level_tbl(i) ,
1075 extended_quantity_tbl(i) ,
1076 sort_order_tbl(i) ,
1077 group_id_tbl(i) ,
1078 top_alternate_designator_tbl(i) ,
1079 component_yield_factor_tbl(i) ,
1080 top_item_id_tbl(i) ,
1081 component_code_tbl(i) ,
1082 include_in_cost_rollup_tbl(i) ,
1083 loop_flag_tbl(i) ,
1084 planning_factor_tbl(i) ,
1085 operation_seq_num_tbl(i) ,
1086 bom_item_type_tbl(i) ,
1087 parent_bom_item_type_tbl(i) ,
1088 parent_item_id_tbl(i) ,
1089 --alternate_bom_designator_tbl(i) ,
1090 wip_supply_type_tbl(i) ,
1091 item_num_tbl(i) ,
1092 effectivity_date_tbl(i) ,
1093 disable_date_tbl(i) ,
1094 from_end_item_unit_number_tbl(i) ,
1095 to_end_item_unit_number_tbl(i) ,
1096 implementation_date_tbl(i) ,
1097 optional_tbl(i) ,
1098 supply_subinventory_tbl(i) ,
1099 supply_locator_id_tbl(i) ,
1100 component_remarks_tbl(i) ,
1101 change_notice_tbl(i) ,
1102 operation_leadtime_percent_tbl(i) ,
1103 mutually_exclusive_options_tbl(i) ,
1104 check_atp_tbl(i) ,
1105 required_to_ship_tbl(i) ,
1106 required_for_revenue_tbl(i) ,
1107 include_on_ship_docs_tbl(i) ,
1108 low_quantity_tbl(i) ,
1109 high_quantity_tbl(i) ,
1110 so_basis_tbl(i) ,
1111 --operation_offset_tbl(i) ,
1112 --Current_revision_tbl(i) ,
1113 --locator_tbl(i) ,
1114 attribute_category_tbl(i) ,
1115 attribute1_tbl(i) ,
1116 attribute2_tbl(i) ,
1117 attribute3_tbl(i) ,
1118 attribute4_tbl(i) ,
1119 attribute5_tbl(i) ,
1120 attribute6_tbl(i) ,
1121 attribute7_tbl(i) ,
1122 attribute8_tbl(i) ,
1123 attribute9_tbl(i) ,
1124 attribute10_tbl(i) ,
1125 attribute11_tbl(i) ,
1126 attribute12_tbl(i) ,
1127 attribute13_tbl(i) ,
1128 attribute14_tbl(i) ,
1129 attribute15_tbl(i) ,
1130 --parent_sort_order_tbl(i),
1131 auto_request_material_tbl(i) );
1132
1133 --dbms_output.put_line('O.K. insert done. now what?');
1134
1135 END LOOP;
1136
1137 CLOSE exploder;
1138
1139 /* Update the quantity of children for every parent, total quantity for every parent */
1140
1141 /*
1142 FORALL i IN 1..g_parent_sort_order_tbl.COUNT
1143 UPDATE bom_explosion_temp
1144 SET quantity_of_children = g_quantity_of_children_tbl(i),
1145 total_qty_at_next_level = g_total_qty_at_next_level_tbl(i)
1146 WHERE group_id = grp_id AND sort_order = g_parent_sort_order_tbl(i);
1147 */
1148
1149 --
1150 -- IF total rows fetched is 0, THEN break the loop here since nothing
1151 -- more to explode
1152 --
1153
1154 IF total_rows = 0 THEN
1155 exit;
1156 END IF;
1157
1158 END LOOP; -- while level
1159
1160
1161 IF max_level_exceeded THEN
1162
1163 error_code := 9998;
1164 Fnd_Message.Set_Name('BOM', 'BOM_LEVELS_EXCEEDED');
1165
1166 FOR l_bill_rec in l_TopBill_csr
1167 LOOP
1168 Fnd_Message.Set_Token('ENTITY', l_bill_rec.concatenated_segments);
1172
1169 Fnd_Message.Set_Token('ENTITY1', l_bill_rec.concatenated_segments);
1170 Fnd_Message.Set_Token('ENTITY2', l_bill_rec.alternate_bom_designator);
1171 END LOOP;
1173 err_msg := Fnd_Message.Get_Encoded;
1174 ELSE
1175 error_code := 0;
1176 err_msg := null;
1177
1178 END IF;
1179
1180 EXCEPTION WHEN OTHERS THEN
1181 error_code := SQLCODE;
1182 Fnd_Msg_Pub.Build_Exc_Msg(
1183 p_pkg_name => 'BOMPEXPL',
1184 p_procedure_name => 'BOM_EXPLODER',
1185 p_error_text => SQLERRM);
1186 err_msg := Fnd_Message.Get_Encoded;
1187 ROLLBACK;
1188
1189 END bom_exploder;
1190
1191 procedure exploders(
1192 verify_flag IN NUMBER DEFAULT 0,
1193 online_flag IN NUMBER DEFAULT 0,
1194 item_id IN NUMBER DEFAULT null, -- for forms
1195 org_id IN NUMBER,
1196 alternate IN VARCHAR2 DEFAULT null, -- for forms
1197 list_id IN NUMBER DEFAULT null, -- for reports
1198 order_by IN NUMBER DEFAULT 1,
1199 grp_id IN NUMBER,
1200 session_id IN NUMBER DEFAULT 0,
1201 req_id IN NUMBER DEFAULT 0,
1202 prgm_appl_id IN NUMBER DEFAULT -1,
1203 prgm_id IN NUMBER DEFAULT -1,
1204 l_levels_to_explode IN NUMBER DEFAULT 1,
1205 bom_or_eng IN NUMBER DEFAULT 1,
1206 impl_flag IN NUMBER DEFAULT 1,
1207 plan_factor_flag IN NUMBER DEFAULT 2,
1208 incl_lt_flag IN NUMBER DEFAULT 2,
1209 l_explode_option IN NUMBER DEFAULT 2,
1210 module IN NUMBER DEFAULT 2,
1211 cst_type_id IN NUMBER DEFAULT 0,
1212 std_comp_flag IN NUMBER DEFAULT 0,
1213 rev_date IN VARCHAR2,
1214 expl_qty IN NUMBER DEFAULT 1,
1215 unit_number IN VARCHAR2,
1216 release_option IN NUMBER DEFAULT 0,
1217 err_msg OUT NOCOPY VARCHAR2,
1218 error_code OUT NOCOPY NUMBER) AS
1219
1220 max_level NUMBER;
1221 levels_to_explode NUMBER;
1222 explode_option NUMBER;
1223 cost_org_id NUMBER;
1224 incl_oc_flag NUMBER;
1225 counter NUMBER;
1226 l_std_comp_flag NUMBER;
1227 l_error_code NUMBER := 0;
1228 l_err_msg VARCHAR2(2000) := null;
1229 loop_detected boolean := false;
1230 l_path StackTabType;
1231 l_level binary_integer := 0;
1232
1233 -- Bug Fix: 3633030
1234 -- Description: Performance issue, FTS on table BOM_STRUCTURES_B
1235 -- Commented as part of bugfix.
1236 /*cursor l_list_csr is
1237 Select bl.assembly_item_id,
1238 bl.alternate_designator,
1239 bl.conc_flex_string,
1240 bom.bill_sequence_id,
1241 bom.common_bill_sequence_id,
1242 msi.bom_item_type
1243 From mtl_system_items msi,
1244 Bom_Bill_Of_Materials bom,
1245 Bom_Lists bl
1246 Where bl.sequence_id = list_id
1247 And bom.assembly_item_id = bl.assembly_item_id
1248 And bom.organization_id = org_id
1249 And nvl(bom.alternate_bom_designator, 'PRIMARY ALTERNATE') =
1250 nvl(bl.alternate_designator, 'PRIMARY ALTERNATE')
1251 And msi.inventory_item_id = bom.assembly_item_id
1252 And msi.organization_id = bom.organization_id;*/
1253
1254 -- Bug Fix: 3633030
1255 -- Description: Performance issue, FTS on table BOM_STRUCTURES_B
1256 -- Query modified with UNION ALL avoiding the NVL statement.
1257
1258 cursor l_list_csr is
1259 SELECT
1260 bl.assembly_item_id,
1261 bl.alternate_designator,
1262 bl.conc_flex_string,
1263 bom.bill_sequence_id,
1264 bom.common_bill_sequence_id,
1265 bom.common_organization_id,
1266 msi.bom_item_type
1267 FROM
1268 mtl_system_items msi,
1269 Bom_Bill_Of_Materials bom,
1270 Bom_Lists bl
1271 WHERE
1272 bl.sequence_id = list_id And
1273 bom.assembly_item_id = bl.assembly_item_id And
1274 bom.organization_id = org_id And
1275 bom.alternate_bom_designator = bl.alternate_designator And
1276 BOM.alternate_bom_designator IS NOT NULL And
1277 bl.alternate_designator is NOT null And
1278 msi.inventory_item_id = bom.assembly_item_id And
1279 msi.organization_id = bom.organization_id
1280 UNION ALL
1281 SELECT
1282 bl.assembly_item_id,
1283 bl.alternate_designator,
1284 bl.conc_flex_string,
1285 bom.bill_sequence_id,
1286 bom.common_bill_sequence_id,
1287 bom.common_organization_id,
1288 msi.bom_item_type
1289 FROM
1290 mtl_system_items msi,
1291 Bom_Bill_Of_Materials bom,
1292 Bom_Lists bl
1293 WHERE
1294 bl.sequence_id = list_id And
1295 bom.assembly_item_id = bl.assembly_item_id And
1296 bom.organization_id = org_id And
1297 bom.alternate_bom_designator is null And
1298 bl.alternate_designator is null And
1299 msi.inventory_item_id = bom.assembly_item_id And
1300 msi.organization_id = bom.organization_id;
1301
1302 cursor l_bill_csr(p_ItemId number, p_OrgId number, p_alternate varchar2) is
1303 Select bom.bill_sequence_id,
1304 bom.common_bill_sequence_id,
1305 bom.common_organization_id,
1306 msi.bom_item_type,
1307 msi.item_number
1308 From mtl_item_flexfields msi,
1309 bom_bill_of_materials bom
1310 Where bom.assembly_item_id = p_ItemId
1311 And bom.organization_id = P_OrgId
1312 And nvl(alternate_bom_designator, 'PRIMARY ALTERNATE') =
1313 nvl(p_alternate, 'PRIMARY ALTERNATE')
1314 And msi.inventory_item_id = bom.assembly_item_id
1315 And msi.organization_id = bom.organization_id;
1319 -- l_SortCode varchar2(4000) := '0000001';
1316
1317
1318 l_LoopFlag number := g_no;
1320 l_SortCode Bom_Common_Definitions.G_Bom_SortCode_Type := Bom_Common_Definitions.G_Bom_Init_SortCode;
1321 l_FatalError exception;
1322
1323 BEGIN
1324
1325 levels_to_explode := l_levels_to_explode;
1326 explode_option := l_explode_option;
1327
1328 /*
1329 ** fetch the max permissible levels for explosion
1330 ** doing a max since if no row exist to prevent no_Data_found exception
1331 ** from being raised
1332 */
1333
1334 SELECT max(MAXIMUM_BOM_LEVEL)
1335 INTO max_level
1336 FROM BOM_PARAMETERS
1337 WHERE (org_id = -1
1338 or
1339 (org_id <> -1 and ORGANIZATION_ID = org_id)
1340 );
1341
1342 /* Since sort width is increased to 7 and the sort_order column is only 2000
1343 wide, we must reduce maximum levels to 59 */
1344
1345 IF nvl(max_level, 60) > 59 THEN
1346 max_level := 59; -- 60 levels including level 0
1347 END IF;
1348
1349 /*
1350 ** if levels to explode > max levels or < 0, set it to max_level
1351 */
1352 IF (levels_to_explode < 0) OR (levels_to_explode > max_level) THEN
1353 levels_to_explode := max_level;
1354 END IF;
1355
1356 /*
1357 ** if levels_to_explode > 1, then explode_option = CURRENT is the
1358 ** only valid option
1359 ** 05/20/93 removed this condition to make it generic. Also the verify
1360 ** needs current+future indented explosion.
1361
1362 IF levels_to_explode > 1 THEN
1363 explode_option := 2;
1364 END IF;
1365 */
1366
1367 IF (module = 1 or module = 2 or module = 4 or module = 5) THEN /* cst, bom, ato*/
1368 l_std_comp_flag := 2; /* ALL */
1369 ELSE
1370 l_std_comp_flag := std_comp_flag;
1371 END IF;
1372
1373 IF (module = 1 or module = 4 ) THEN /* CST or ATO */
1374 incl_oc_flag := 2;
1375 ELSE
1376 incl_oc_flag := 1;
1377 END IF;
1378
1379 --dbms_output.put_line ('incl_oc_flag' ||incl_oc_flag);
1380
1381 If online_flag = g_yes THEN
1382
1383 For l_bill_rec in l_bill_csr( p_ItemId => item_id, p_OrgId => org_id, p_alternate => alternate)
1384 loop
1385
1386 l_err_msg := null;
1387 l_error_code := 0;
1388
1389
1390 insert into bom_explosion_temp(
1391 group_id,
1392 bill_sequence_id,
1393 common_bill_sequence_id,
1394 component_sequence_id,
1395 organization_id,
1396 common_organization_id,
1397 top_item_id,
1398 component_item_id,
1399 plan_level,
1400 extended_quantity,
1401 basis_type,
1402 component_quantity,
1403 sort_order,
1404 request_id,
1405 program_application_id,
1406 program_id,
1407 program_update_date,
1408 top_bill_sequence_id,
1409 component_code,
1410 loop_flag,
1411 top_alternate_designator,
1412 bom_item_type,
1413 parent_bom_item_type,
1414 auto_request_material
1415 )
1416 values(
1417 grp_id,
1418 l_bill_rec.bill_sequence_id,
1419 l_bill_rec.common_bill_sequence_id,
1420 NULL,
1421 org_id,
1422 l_bill_rec.common_organization_id,
1423 item_id,
1424 item_id,
1425 0,
1426 expl_qty,
1427 NULL,
1428 1,
1429 lpad('1', G_SortWidth, '0'),
1430 req_id,
1431 prgm_appl_id,
1432 prgm_id,
1433 sysdate,
1434 l_bill_rec.bill_sequence_id,
1435 item_id,
1436 --to_char(item_id),
1437 --l_LoopFlag,
1438 g_no,
1439 alternate,
1440 l_bill_rec.bom_item_type,
1441 l_bill_rec.bom_item_type,
1442 'Y'
1443 );
1444
1445 bom_exploder(
1446 verify_flag => verify_flag,
1447 online_flag => online_flag,
1448 top_bill_id => l_bill_rec.bill_sequence_id,
1449 org_id => org_id,
1450 order_by => order_by,
1451 grp_id => grp_id,
1452 levels_to_explode => levels_to_explode,
1453 bom_or_eng => bom_or_eng,
1454 impl_flag => impl_flag,
1455 std_comp_flag => l_std_comp_flag,
1456 plan_factor_flag => plan_factor_flag,
1457 explode_option => explode_option,
1458 incl_oc_flag => incl_oc_flag,
1459 max_level => max_level,
1460 rev_date => to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'),
1461 show_rev => g_no,
1462 material_ctrl => g_no,
1463 lead_time => incl_lt_flag,
1464 unit_number => unit_number,
1465 release_option => release_option,
1466 err_msg => l_err_msg,
1467 error_code => l_error_code
1468 );
1469
1470 If l_error_code < 0 or l_error_code = G_MaxLevelCode THEN
1471 Raise l_FatalError;
1472 End if;
1473
1474 /*
1475 If verify_flag = g_yes THEN
1476 If loop_detected THEN
1477 l_err_msg := l_bill_rec.item_number || l_err_msg;
1478 End if;
1479 Else
1480 If loop_detected THEN
1481 l_LoopFlag := g_yes;
1482 Else
1483 l_LoopFlag := g_no;
1484 End if;
1485 End if; -- verify flag
1486 */
1487
1488 End loop; -- from form
1489
1490 Else -- report
1491
1495 insert into bom_explosion_temp(
1492 For l_list_rec in l_list_csr
1493 loop
1494
1496 group_id,
1497 bill_sequence_id,
1498 common_bill_sequence_id,
1499 component_sequence_id,
1500 organization_id,
1501 common_organization_id,
1502 top_item_id,
1503 component_item_id,
1504 plan_level,
1505 extended_quantity,
1506 basis_type,
1507 component_quantity,
1508 sort_order,
1509 request_id,
1510 program_application_id,
1511 program_id,
1512 program_update_date,
1513 top_bill_sequence_id,
1514 component_code,
1515 loop_flag,
1516 top_alternate_designator,
1517 bom_item_type,
1518 parent_bom_item_type,
1519 auto_request_material
1520 )
1521 values(
1522 grp_id,
1523 l_list_rec.bill_sequence_id,
1524 l_list_rec.common_bill_sequence_id,
1525 NULL,
1526 org_id,
1527 l_list_rec.common_organization_id,
1528 l_list_rec.assembly_item_id,
1529 l_list_rec.assembly_item_id,
1530 0,
1531 expl_qty,
1532 1,
1533 1,
1534 lpad('1', G_SortWidth, '0'),
1535 req_id,
1536 prgm_appl_id,
1537 prgm_id,
1538 sysdate,
1539 l_list_rec.bill_sequence_id,
1540 l_list_rec.assembly_item_id,
1541 --l_LoopFlag,
1542 g_no,
1543 l_list_rec.alternate_designator,
1544 l_list_rec.bom_item_type,
1545 l_list_rec.bom_item_type,
1546 'Y'
1547 );
1548
1549
1550 bom_exploder(
1551 verify_flag => verify_flag,
1552 online_flag => online_flag,
1553 top_bill_id => l_list_rec.bill_sequence_id,
1554 org_id => org_id,
1555 order_by => order_by,
1556 grp_id => grp_id,
1557 levels_to_explode => levels_to_explode,
1558 bom_or_eng => bom_or_eng,
1559 impl_flag => impl_flag,
1560 std_comp_flag => l_std_comp_flag,
1561 plan_factor_flag => plan_factor_flag,
1562 explode_option => explode_option,
1563 incl_oc_flag => incl_oc_flag,
1564 max_level => max_level,
1565 rev_date => to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'),
1566 show_rev => g_no,
1567 material_ctrl => g_no,
1568 lead_time => incl_lt_flag,
1569 unit_number => unit_number,
1570 err_msg => l_err_msg,
1571 error_code => l_error_code
1572 );
1573
1574 If l_error_code < 0 or l_error_code = G_MaxLevelCode THEN
1575 Raise l_FatalError;
1576 End if;
1577
1578 IF module = 1 THEN -- intermittent commits for CST
1579 commit;
1580 END IF;
1581
1582 End loop; -- from list
1583
1584 End If; -- online_flag
1585
1586 error_code := l_error_code;
1587 err_msg := l_err_msg;
1588
1589 EXCEPTION
1590 When l_FatalError THEN
1591 error_code := l_error_code;
1592 err_msg := l_err_msg;
1593 WHEN OTHERS THEN
1594 error_code := sqlcode;
1595 err_msg := 'BOMPEXPL[exploders] '||sqlerrm;
1596 END exploders;
1597
1598 procedure exploder_userexit (
1599 verify_flag IN NUMBER DEFAULT 0,
1600 org_id IN NUMBER,
1601 order_by IN NUMBER DEFAULT 1,
1602 grp_id IN NUMBER,
1603 session_id IN NUMBER DEFAULT 0,
1604 levels_to_explode IN NUMBER DEFAULT 1,
1605 bom_or_eng IN NUMBER DEFAULT 1,
1606 impl_flag IN NUMBER DEFAULT 1,
1607 plan_factor_flag IN NUMBER DEFAULT 2,
1608 explode_option IN NUMBER DEFAULT 2,
1609 module IN NUMBER DEFAULT 2,
1610 cst_type_id IN NUMBER DEFAULT 0,
1611 std_comp_flag IN NUMBER DEFAULT 0,
1612 expl_qty IN NUMBER DEFAULT 1,
1613 item_id IN NUMBER,
1614 alt_desg IN VARCHAR2 DEFAULT '',
1615 comp_code IN VARCHAR2 DEFAULT '',
1616 rev_date IN VARCHAR2,
1617 unit_number IN VARCHAR2 DEFAULT '',
1618 release_option IN NUMBER DEFAULT 0,
1619 err_msg OUT NOCOPY VARCHAR2,
1620 error_code OUT NOCOPY NUMBER) AS
1621
1622 out_code NUMBER;
1623 cost_org_id NUMBER;
1624 stmt_num NUMBER := 1;
1625 out_message VARCHAR2(240);
1626 expl_date VARCHAR2(25);
1627 parameter_error EXCEPTION;
1628 bom_exploder_error EXCEPTION;
1629
1630 BEGIN
1631
1632 --DBMS_PROFILER.Start_Profiler(session_id);
1633
1634 IF (verify_flag = 1) AND (module <> 2) THEN
1635 raise parameter_error;
1636 END IF;
1637
1638 IF (grp_id is null or item_id is null) THEN
1639 raise parameter_error;
1640 END IF;
1641
1642 expl_date := substr(rev_date, 1, 16);
1643 G_Module := module;
1644
1645 IF (expl_date is null) THEN
1646 select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
1647 into expl_date
1648 from dual;
1649 ELSE
1650 -- we will make sure that the canonical and the nls_date formats are supported.
1651 BEGIN
1652 select fnd_date.date_to_canonical(fnd_date.displayDT_to_date(rev_date))
1653 into expl_date
1654 from dual;
1655 EXCEPTION WHEN OTHERS THEN
1656 BEGIN
1657 select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
1658 into expl_date
1659 from dual;
1663
1660 END;
1661 END;
1662 END IF;
1664 G_Allow_Date_Trimming_Flag := 'Y';
1665
1666 exploders(
1667 verify_flag => verify_flag,
1668 online_flag => g_yes,
1669 item_id => item_id, -- for forms
1670 org_id => org_id,
1671 alternate => alt_desg, -- for forms
1672 list_id => null, -- for reports
1673 order_by => order_by,
1674 grp_id => grp_id,
1675 session_id => session_id,
1676 req_id => 0,
1677 prgm_appl_id => -1,
1678 prgm_id => -1,
1679 l_levels_to_explode => levels_to_explode,
1680 bom_or_eng => bom_or_eng,
1681 impl_flag => impl_flag,
1682 plan_factor_flag => plan_factor_flag,
1683 incl_lt_flag => g_no,
1684 l_explode_option => explode_option,
1685 module => module,
1686 cst_type_id => cst_type_id,
1687 std_comp_flag => std_comp_flag,
1688 rev_date => expl_date,
1689 expl_qty => expl_qty,
1690 unit_number => unit_number,
1691 release_option => release_option,
1692 err_msg => out_message,
1693 error_code => out_code);
1694
1695 IF verify_flag <> 1 THEN
1696 IF out_code <> 0 THEN
1697 raise bom_exploder_error;
1698 END IF;
1699 ELSIF verify_flag = 1 THEN
1700 IF out_code not in (9999, 0) THEN
1701 raise bom_exploder_error;
1702 END IF;
1703 END IF;
1704
1705 IF (module = 1 or module = 4) THEN /* CST or ATO */
1706 BOMPCEXP.cst_exploder(
1707 grp_id => grp_id,
1708 org_id => org_id,
1709 cst_type_id => cst_type_id,
1710 err_msg => out_message,
1711 error_code => out_code);
1712 END IF;
1713
1714 error_code := out_code;
1715 err_msg := out_message;
1716
1717 --DBMS_PROFILER.Stop_Profiler;
1718
1719 EXCEPTION
1720 WHEN bom_exploder_error THEN
1721 error_code := out_code;
1722 err_msg := out_message;
1723 WHEN parameter_error THEN
1724 error_code := 9997;
1725 err_msg := 'BOMPEXPL: verify parameters';
1726 WHEN no_data_found THEN
1727 error_code := SQLCODE;
1728 err_msg := 'BOMPEXPL: ' || substrb(SQLERRM,1,60);
1729 WHEN OTHERS THEN
1730 error_code := SQLCODE;
1731 err_msg := 'BOMPEXPL (' || stmt_num ||'): ' ||substrb(SQLERRM,1,60);
1732
1733 END exploder_userexit;
1734
1735 PROCEDURE explosion_report(
1736 verify_flag IN NUMBER DEFAULT 0,
1737 org_id IN NUMBER,
1738 order_by IN NUMBER DEFAULT 1,
1739 list_id IN NUMBER,
1740 grp_id IN NUMBER,
1741 session_id IN NUMBER DEFAULT 0,
1742 levels_to_explode IN NUMBER DEFAULT 1,
1743 bom_or_eng IN NUMBER DEFAULT 1,
1744 impl_flag IN NUMBER DEFAULT 1,
1745 plan_factor_flag IN NUMBER DEFAULT 2,
1746 incl_lt_flag IN NUMBER DEFAULT 2,
1747 explode_option IN NUMBER DEFAULT 2,
1748 module IN NUMBER DEFAULT 2,
1749 cst_type_id IN NUMBER DEFAULT 0,
1750 std_comp_flag IN NUMBER DEFAULT 0,
1751 expl_qty IN NUMBER DEFAULT 1,
1752 report_option IN NUMBER DEFAULT 0,
1753 req_id IN NUMBER DEFAULT 0,
1754 cst_rlp_id IN NUMBER DEFAULT 0,
1755 lock_flag IN NUMBER DEFAULT 2,
1756 rollup_option IN NUMBER DEFAULT 2,
1757 alt_rtg_desg IN VARCHAR2 DEFAULT '',
1758 alt_desg IN VARCHAR2 DEFAULT '',
1759 rev_date IN VARCHAR2,
1760 err_msg OUT NOCOPY VARCHAR2,
1761 error_code OUT NOCOPY NUMBER) AS
1762
1763 rollup_error EXCEPTION;
1764 explode_error EXCEPTION;
1765 parameter_error EXCEPTION;
1766 prgm_appl_id NUMBER;
1767 prg_id NUMBER;
1768 user_id NUMBER;
1769 cost_org_id NUMBER;
1770 out_code NUMBER;
1771 rollup_status NUMBER;
1772 num_of_assys NUMBER;
1773 unimpl_flag NUMBER;
1774 stmt_num NUMBER;
1775 out_message VARCHAR2(80);
1776 expl_date VARCHAR2(25);
1777 rollup_date VARCHAR2(25);
1778 leaves_found boolean := true;
1779
1780 CURSOR loop_flag_rows ( c_group_id NUMBER )
1781 IS
1782 SELECT SORT_ORDER
1783 FROM BOM_EXPLOSION_TEMP
1784 WHERE
1785 LOOP_FLAG = 1
1786 AND GROUP_ID = c_group_id;
1787
1788
1789 BEGIN
1790
1791 IF (verify_flag = 1) AND (module <> 2) THEN
1792 raise parameter_error;
1793 END IF;
1794
1795 IF (grp_id is null) THEN
1796 raise parameter_error;
1797 END IF;
1798
1799 expl_date := substr(rev_date, 1, 16);
1800 G_Module := module;
1801
1802 stmt_num := 0;
1803
1804 IF (expl_date is null) THEN
1805 select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
1806 into expl_date
1807 from dual;
1808 ELSE
1809 -- we will make sure that the canonical and the nls_date formats are supported.
1810 -- Bug 4740913. Removed fnd_date.charDT_to_date as both fnd_date.charDT_to_date
1811 -- and fnd_date.displayDT_to_date do the same operation.
1812 BEGIN
1813 select fnd_date.date_to_canonical(fnd_date.displayDT_to_date(rev_date))
1814 into expl_date
1815 from dual;
1816 EXCEPTION WHEN OTHERS THEN
1817 BEGIN
1818 select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
1819 into expl_date
1820 from dual;
1824
1821 END;
1822 END;
1823 END IF;
1825 IF (module = 1 or module = 4) THEN /* CST */
1826
1827 stmt_num := 3;
1828
1829 INSERT INTO BOM_EXPLOSION_TEMP
1830 (
1831 GROUP_ID,
1832 BILL_SEQUENCE_ID,
1833 COMPONENT_SEQUENCE_ID,
1834 ORGANIZATION_ID,
1835 COMPONENT_ITEM_ID,
1836 PLAN_LEVEL,
1837 EXTENDED_QUANTITY,
1838 BASIS_TYPE,
1839 COMPONENT_QUANTITY,
1840 SORT_ORDER,
1841 PROGRAM_UPDATE_DATE,
1842 TOP_BILL_SEQUENCE_ID,
1843 TOP_ITEM_ID,
1844 TOP_ALTERNATE_DESIGNATOR,
1845 COMPONENT_CODE,
1846 LOOP_FLAG
1847 )
1848 SELECT grp_id,
1849 0,
1850 NULL,
1851 nvl(BL.ORGANIZATION_ID, org_id),
1852 BL.ASSEMBLY_ITEM_ID,
1853 0,
1854 expl_qty,
1855 1,
1856 1,
1857 lpad('1', G_SortWidth, '0'),
1858 sysdate,
1859 0,
1860 BL.ASSEMBLY_ITEM_ID,
1861 NULL,
1862 to_char(BL.ASSEMBLY_ITEM_ID),
1863 2
1864 FROM BOM_LISTS BL
1865 WHERE BL.SEQUENCE_ID = list_id
1866 AND BL.ALTERNATE_DESIGNATOR IS NULL
1867 AND NOT EXISTS (SELECT 'NO BILL' FROM BOM_BILL_OF_MATERIALS BOM
1868 WHERE BOM.ORGANIZATION_ID =
1869 nvl(BL.ORGANIZATION_ID, org_id)
1870 AND BOM.ASSEMBLY_ITEM_ID = BL.ASSEMBLY_ITEM_ID
1871 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL);
1872
1873 IF SQL%NOTFOUND and num_of_assys = 0 THEN
1874 raise no_data_found;
1875 END IF;
1876
1877 END IF;
1878
1879 /*
1880 ** get the conc who values
1881 */
1882 IF (req_id <> 0) THEN
1883 stmt_num := 4;
1884 SELECT PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
1885 REQUESTED_BY
1886 INTO prgm_appl_id, prg_id, user_id
1887 FROM FND_CONCURRENT_REQUESTS
1888 WHERE REQUEST_ID = req_id;
1889 ELSE
1890 prgm_appl_id := 1;
1891 prg_id := 1;
1892 user_id := 1;
1893 END IF;
1894
1895 /*
1896 ** call the exploder
1897 */
1898
1899 G_Allow_Date_Trimming_Flag := 'N';
1900
1901 exploders(
1902 verify_flag => verify_flag,
1903 online_flag => g_no,
1904 item_id => null, -- for forms
1905 org_id => org_id,
1906 alternate => null, -- for forms
1907 list_id => list_id, -- for reports
1908 order_by => order_by,
1909 grp_id => grp_id,
1910 session_id => session_id,
1911 req_id => req_id,
1912 prgm_appl_id => prgm_appl_id,
1913 prgm_id => prg_id,
1914 l_levels_to_explode => levels_to_explode,
1915 bom_or_eng => bom_or_eng,
1916 impl_flag => impl_flag,
1917 plan_factor_flag => plan_factor_flag,
1918 incl_lt_flag => incl_lt_flag,
1919 l_explode_option => explode_option,
1920 module => module,
1921 cst_type_id => cst_type_id,
1922 std_comp_flag => std_comp_flag,
1923 rev_date => expl_date,
1924 expl_qty => expl_qty,
1925 unit_number => '',
1926 err_msg => out_message,
1927 error_code => out_code);
1928
1929 --bug:5362238 For Loop Report, propage loop flag up the hierarchy.
1930 IF ( verify_flag = 1 ) THEN
1931 FOR l_loop_flag_row_rec IN loop_flag_rows( grp_id )
1932 LOOP
1933 UPDATE BOM_EXPLOSION_TEMP bet_update
1934 SET bet_update.LOOP_FLAG = 1
1935 WHERE bet_update.SORT_ORDER IN
1936 ( SELECT bet.SORT_ORDER
1937 FROM BOM_EXPLOSION_TEMP bet
1938 WHERE
1939 bet.GROUP_ID = grp_id
1940 CONNECT BY PRIOR
1941 SubStr( bet.SORT_ORDER, 1, (bet.PLAN_LEVEL * 7) ) = bet.SORT_ORDER
1942 START WITH bet.SORT_ORDER = l_loop_flag_row_rec.sort_order )
1943 AND bet_update.GROUP_ID = grp_id;
1944 END LOOP;
1945 END IF; -- end if ( :P_VERIFY_FLAG = 1 )
1946
1947
1948 IF verify_flag <> 1 THEN
1949 IF out_code <> 0 THEN
1950 raise explode_error;
1951 END IF;
1952 ELSE
1953 IF out_code not in (0, 9999) THEN
1954 raise explode_error;
1955 END IF;
1956 END IF;
1957
1958 /*
1959 ** for a costed explosion, if temp or permanent rollup
1960 ** need to do rollup related stuff. But only if not frozen std cst type
1961 */
1962
1963 IF ((module = 1 or module = 4)and (report_option = 1 or report_option = 3 or
1964 report_option = 2)) THEN
1965
1966 /*
1967 ** commit here else may run out of rollback segments
1968 */
1969 IF (module = 1) THEN
1970 commit;
1971 END IF;
1972
1973
1974 /*
1975 ** insert low level codes from the explosion that was
1976 ** just performed
1977 */
1978 stmt_num := 5;
1979
1980 INSERT INTO CST_LOW_LEVEL_CODES
1981 (ROLLUP_ID, INVENTORY_ITEM_ID, LOW_LEVEL_CODE,
1982 LAST_UPDATE_DATE, LAST_UPDATED_BY,
1983 CREATION_DATE, CREATED_BY)
1984
1985 SELECT cst_rlp_id, COMPONENT_ITEM_ID, max(PLAN_LEVEL),
1986 sysdate, user_id, sysdate, user_id
1987 FROM BOM_EXPLOSION_TEMP
1988 WHERE GROUP_ID = grp_id
1989 GROUP BY COMPONENT_ITEM_ID;
1990
1991 /*
1992 ** if single level rollup, delete items that do not exist in bom_lists
1993 */
1997
1994
1995 IF (rollup_option = 1) THEN
1996 stmt_num := 6;
1998 DELETE CST_LOW_LEVEL_CODES CLLC
1999 WHERE NOT EXISTS (SELECT 'Item in list'
2000 FROM BOM_LISTS BL
2001 WHERE SEQUENCE_ID = list_id
2002 AND BL.ASSEMBLY_ITEM_ID = CLLC.INVENTORY_ITEM_ID)
2003 AND ROLLUP_ID = cst_rlp_id;
2004
2005 END IF;
2006
2007 rollup_date := to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS');
2008
2009 /*
2010 ** call the cost rollup here
2011 */
2012
2013 IF (impl_flag = 1) THEN
2014 unimpl_flag := 2;
2015 ELSE
2016 unimpl_flag := 1;
2017 END IF;
2018 /*
2019 rollup_status := CSTPUCRU.cstflcru(
2020 l_group_id => grp_id,
2021 l_organization_id => org_id,
2022 l_rollup_id => cst_rlp_id,
2023 l_cost_type_id => cst_type_id,
2024 req_id => req_id,
2025 prgm_appl_id => prgm_appl_id,
2026 prgm_id => prg_id,
2027 l_last_updated_by => user_id,
2028 conc_flag => 1,
2029 unimp_flag => unimpl_flag,
2030 locking_flag => lock_flag,
2031 rollup_date => rollup_date,
2032 revision_date => expl_date,
2033 alt_bom_designator => alt_desg,
2034 alt_rtg_designator => alt_rtg_desg,
2035 rollup_option => rollup_option,
2036 report_option => report_option,
2037 l_mfg_flag => bom_or_eng,
2038 err_buf => out_message);
2039
2040 IF (rollup_status <> 0) THEN
2041 raise rollup_error;
2042 END IF;
2043 */
2044 --commented for bug 5322048.
2045 /*
2046 ** delete low level codes
2047 */
2048 /* delete from cst_low_level_codes
2049 where rollup_id = cst_rlp_id;
2050 */
2051 END IF;
2052
2053 /*
2054 ** do the post explosion updates for costing attributes only if no
2055 ** report is selected
2056
2057 */
2058 IF ((module = 1 or module = 4) and report_option <> 2) THEN
2059 BOMPCEXP.cst_exploder(
2060 grp_id => grp_id,
2061 org_id => org_id,
2062 cst_type_id => cst_type_id,
2063 err_msg => out_message,
2064 error_code => out_code);
2065 END IF;
2066
2067 error_code := out_code;
2068 err_msg := out_message;
2069
2070 EXCEPTION
2071 WHEN NO_DATA_FOUND THEN
2072 error_code := SQLCODE;
2073 err_msg := 'BOMPEXPL(' || stmt_num || '): ' ||
2074 substrb(SQLERRM, 1, 60);
2075 /* WHEN rollup_error THEN
2076 error_code := rollup_status;
2077 err_msg := out_message;*/
2078 WHEN explode_error THEN
2079 error_code := out_code;
2080 err_msg := out_message;
2081 WHEN parameter_error THEN
2082 error_code := 9997;
2083 err_msg := 'BOMPEXPL: verify parameters';
2084 WHEN OTHERS THEN
2085 error_code := SQLCODE;
2086 err_msg := 'BOMPEXPL(' || stmt_num || '): ' ||
2087 substrb(SQLERRM, 1, 60);
2088 END explosion_report;
2089
2090 /* new procedure for PDI usage.
2091 This exploder will populate trimmed dates in the explosion table
2092 */
2093 procedure explode(
2094 verify_flag IN NUMBER DEFAULT 0,
2095 org_id IN NUMBER,
2096 order_by IN NUMBER DEFAULT 1,
2097 grp_id IN NUMBER,
2098 session_id IN NUMBER DEFAULT 0,
2099 levels_to_explode IN NUMBER DEFAULT 1,
2100 bom_or_eng IN NUMBER DEFAULT 1,
2101 impl_flag IN NUMBER DEFAULT 1,
2102 plan_factor_flag IN NUMBER DEFAULT 2,
2103 explode_option IN NUMBER DEFAULT 1,
2104 module IN NUMBER DEFAULT 2,
2105 cst_type_id IN NUMBER DEFAULT 0,
2106 std_comp_flag IN NUMBER DEFAULT 0,
2107 expl_qty IN NUMBER DEFAULT 1,
2108 item_id IN NUMBER,
2109 alt_desg IN VARCHAR2 DEFAULT '',
2110 comp_code IN VARCHAR2 DEFAULT '',
2111 rev_date IN VARCHAR2,
2112 unit_number IN VARCHAR2 DEFAULT '',
2113 err_msg OUT NOCOPY VARCHAR2,
2114 error_code OUT NOCOPY NUMBER) AS
2115
2116 Begin
2117
2118 G_Allow_Date_Trimming_Flag := 'Y';
2119
2120 exploder_userexit (
2121 verify_flag ,
2122 org_id ,
2123 order_by ,
2124 grp_id ,
2125 session_id ,
2126 levels_to_explode ,
2127 bom_or_eng ,
2128 impl_flag ,
2129 plan_factor_flag ,
2130 explode_option ,
2131 module ,
2132 cst_type_id ,
2133 std_comp_flag ,
2134 expl_qty ,
2135 item_id ,
2136 alt_desg ,
2137 comp_code ,
2138 rev_date ,
2139 unit_number ,
2140 0, --release_option
2141 err_msg ,
2142 error_code ) ;
2143 end;
2144
2145 END bompexpl;