[Home] [Help]
PACKAGE BODY: APPS.BOMPEXPL
Source
1 package body bompexpl as
2 /* $Header: BOMEXPLB.pls 120.16.12020000.4 2013/01/21 12:59:49 rambkond 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 --bug 14185560
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 /*+ push_subq */'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
373 OR (c_explode_option IN (2,3) AND BIC.DISABLE_DATE IS NULL)
374 )
375 /*
376 AND unit_number_from <=
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 /*+ push_subq */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 --bug 14185560
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 /*+ push_subq */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 --bug 14185560
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
488 OR BIC.EFFECTIVITY_DATE >
489 c_rev_date
490 ) -- CURRENT AND FUTURE
491 ) -- explode_option
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;
623
624 TYPE VARCHAR2_TBL_TYPE_3 IS TABLE OF VARCHAR2(3)
625 INDEX BY BINARY_INTEGER;
626
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;
731 assembly_type_tbl NUMBER_TBL_TYPE;
732 revision_label_tbl VARCHAR2_TBL_TYPE_260;
733 revision_id_tbl NUMBER_TBL_TYPE;
734 bom_implementation_date_tbl DATE_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
899 loop_flag_tbl(i) := 1;
900 ELSE
901 loop_flag_tbl(i) := 2;
902 END IF;
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 ,
1043 --CURRENT_REVISION ,
1044 --LOCATOR ,
1045 CONTEXT ,
1046 ATTRIBUTE1 ,
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);
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;
1172
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;
1316
1317
1318 l_LoopFlag number := g_no;
1319 -- l_SortCode varchar2(4000) := '0000001';
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
1492 For l_list_rec in l_list_csr
1493 loop
1494
1495 insert into bom_explosion_temp(
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 release_option =>release_option, /*bug 8299615 Passed Released Option Variable earlier not passing anything*/
1571 err_msg => l_err_msg,
1572 error_code => l_error_code
1573 );
1574
1575 If l_error_code < 0 or l_error_code = G_MaxLevelCode THEN
1576 Raise l_FatalError;
1577 End if;
1578
1579 IF module = 1 THEN -- intermittent commits for CST
1580 commit;
1581 END IF;
1582
1583 End loop; -- from list
1584
1585 End If; -- online_flag
1586
1587 error_code := l_error_code;
1588 err_msg := l_err_msg;
1589
1590 EXCEPTION
1591 When l_FatalError THEN
1592 error_code := l_error_code;
1593 err_msg := l_err_msg;
1594 WHEN OTHERS THEN
1595 error_code := sqlcode;
1596 err_msg := 'BOMPEXPL[exploders] '||sqlerrm;
1597 END exploders;
1598
1599 procedure exploder_userexit (
1600 verify_flag IN NUMBER DEFAULT 0,
1601 org_id IN NUMBER,
1602 order_by IN NUMBER DEFAULT 1,
1603 grp_id IN NUMBER,
1604 session_id IN NUMBER DEFAULT 0,
1605 levels_to_explode IN NUMBER DEFAULT 1,
1606 bom_or_eng IN NUMBER DEFAULT 1,
1607 impl_flag IN NUMBER DEFAULT 1,
1608 plan_factor_flag IN NUMBER DEFAULT 2,
1609 explode_option IN NUMBER DEFAULT 2,
1610 module IN NUMBER DEFAULT 2,
1611 cst_type_id IN NUMBER DEFAULT 0,
1612 std_comp_flag IN NUMBER DEFAULT 0,
1613 expl_qty IN NUMBER DEFAULT 1,
1614 item_id IN NUMBER,
1615 alt_desg IN VARCHAR2 DEFAULT '',
1616 comp_code IN VARCHAR2 DEFAULT '',
1617 rev_date IN VARCHAR2,
1618 unit_number IN VARCHAR2 DEFAULT '',
1619 release_option IN NUMBER DEFAULT 0,
1620 err_msg OUT NOCOPY VARCHAR2,
1621 error_code OUT NOCOPY NUMBER) AS
1622
1623 out_code NUMBER;
1624 cost_org_id NUMBER;
1625 stmt_num NUMBER := 1;
1626 out_message VARCHAR2(240);
1627 expl_date VARCHAR2(25);
1628 parameter_error EXCEPTION;
1629 bom_exploder_error EXCEPTION;
1630
1631 BEGIN
1632
1633 --DBMS_PROFILER.Start_Profiler(session_id);
1634
1635 IF (verify_flag = 1) AND (module <> 2) THEN
1636 raise parameter_error;
1637 END IF;
1638
1639 IF (grp_id is null or item_id is null) THEN
1640 raise parameter_error;
1641 END IF;
1642
1643 expl_date := substr(rev_date, 1, 16);
1644 G_Module := module;
1645
1646 IF (expl_date is null) THEN
1647 select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
1648 into expl_date
1649 from dual;
1650 ELSE
1651 -- we will make sure that the canonical and the nls_date formats are supported.
1652 -- Bug 16173516 - Modified to positional notation call of fnd_date.displayDT_to_date
1653 -- Named and Mixed notation calls in SQL statements do not support in 10g database.
1654 BEGIN
1655 select fnd_date.date_to_canonical(fnd_date.displayDT_to_date(rev_date, FND_DATE.calendar_aware_alt)) --changed for calendar internationalization project
1656 into expl_date
1657 from dual;
1658 EXCEPTION WHEN OTHERS THEN
1659 BEGIN
1660 select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
1661 into expl_date
1662 from dual;
1663 END;
1664 END;
1665 END IF;
1666
1667 --commented for bug 15959523
1668 --G_Allow_Date_Trimming_Flag := 'Y';
1669
1670 exploders(
1671 verify_flag => verify_flag,
1672 online_flag => g_yes,
1673 item_id => item_id, -- for forms
1674 org_id => org_id,
1675 alternate => alt_desg, -- for forms
1676 list_id => null, -- for reports
1677 order_by => order_by,
1678 grp_id => grp_id,
1679 session_id => session_id,
1680 req_id => 0,
1681 prgm_appl_id => -1,
1682 prgm_id => -1,
1683 l_levels_to_explode => levels_to_explode,
1684 bom_or_eng => bom_or_eng,
1685 impl_flag => impl_flag,
1686 plan_factor_flag => plan_factor_flag,
1687 incl_lt_flag => g_no,
1688 l_explode_option => explode_option,
1689 module => module,
1690 cst_type_id => cst_type_id,
1691 std_comp_flag => std_comp_flag,
1692 rev_date => expl_date,
1693 expl_qty => expl_qty,
1694 unit_number => unit_number,
1695 release_option => release_option,
1696 err_msg => out_message,
1697 error_code => out_code);
1698
1699 IF verify_flag <> 1 THEN
1700 IF out_code <> 0 THEN
1701 raise bom_exploder_error;
1702 END IF;
1703 ELSIF verify_flag = 1 THEN
1704 IF out_code not in (9999, 0) THEN
1705 raise bom_exploder_error;
1706 END IF;
1707 END IF;
1708
1709 IF (module = 1 or module = 4) THEN /* CST or ATO */
1710 BOMPCEXP.cst_exploder(
1711 grp_id => grp_id,
1712 org_id => org_id,
1713 cst_type_id => cst_type_id,
1714 err_msg => out_message,
1715 error_code => out_code);
1716 END IF;
1717
1718 error_code := out_code;
1719 err_msg := out_message;
1720
1721 --DBMS_PROFILER.Stop_Profiler;
1722
1723 EXCEPTION
1724 WHEN bom_exploder_error THEN
1725 error_code := out_code;
1726 err_msg := out_message;
1727 WHEN parameter_error THEN
1728 error_code := 9997;
1729 err_msg := 'BOMPEXPL: verify parameters';
1730 WHEN no_data_found THEN
1731 error_code := SQLCODE;
1732 err_msg := 'BOMPEXPL: ' || substrb(SQLERRM,1,60);
1733 WHEN OTHERS THEN
1734 error_code := SQLCODE;
1735 err_msg := 'BOMPEXPL (' || stmt_num ||'): ' ||substrb(SQLERRM,1,60);
1736
1737 END exploder_userexit;
1738
1739 PROCEDURE explosion_report(
1740 verify_flag IN NUMBER DEFAULT 0,
1741 org_id IN NUMBER,
1742 order_by IN NUMBER DEFAULT 1,
1743 list_id IN NUMBER,
1744 grp_id IN NUMBER,
1745 session_id IN NUMBER DEFAULT 0,
1746 levels_to_explode IN NUMBER DEFAULT 1,
1747 bom_or_eng IN NUMBER DEFAULT 1,
1748 impl_flag IN NUMBER DEFAULT 1,
1749 plan_factor_flag IN NUMBER DEFAULT 2,
1750 incl_lt_flag IN NUMBER DEFAULT 2,
1751 explode_option IN NUMBER DEFAULT 2,
1752 module IN NUMBER DEFAULT 2,
1753 cst_type_id IN NUMBER DEFAULT 0,
1754 std_comp_flag IN NUMBER DEFAULT 0,
1755 expl_qty IN NUMBER DEFAULT 1,
1756 report_option IN NUMBER DEFAULT 0,
1757 req_id IN NUMBER DEFAULT 0,
1758 cst_rlp_id IN NUMBER DEFAULT 0,
1759 lock_flag IN NUMBER DEFAULT 2,
1760 rollup_option IN NUMBER DEFAULT 2,
1761 alt_rtg_desg IN VARCHAR2 DEFAULT '',
1762 alt_desg IN VARCHAR2 DEFAULT '',
1763 rev_date IN VARCHAR2,
1764 err_msg OUT NOCOPY VARCHAR2,
1765 error_code OUT NOCOPY NUMBER) AS
1766
1767 rollup_error EXCEPTION;
1768 explode_error EXCEPTION;
1769 parameter_error EXCEPTION;
1770 prgm_appl_id NUMBER;
1771 prg_id NUMBER;
1772 user_id NUMBER;
1773 cost_org_id NUMBER;
1774 out_code NUMBER;
1775 rollup_status NUMBER;
1776 num_of_assys NUMBER;
1777 unimpl_flag NUMBER;
1778 stmt_num NUMBER;
1779 out_message VARCHAR2(2000); -- Modified for bug 13901371
1780 expl_date VARCHAR2(25);
1781 rollup_date VARCHAR2(25);
1782 leaves_found boolean := true;
1783
1784 --
1785 -- bug 13473719
1786 -- Modified the cursor to fetch the top_bill_sequence_id
1787 --
1788 CURSOR loop_flag_rows ( c_group_id NUMBER )
1789 IS
1790 SELECT SORT_ORDER, top_bill_sequence_id
1791 FROM BOM_EXPLOSION_TEMP
1792 WHERE
1793 LOOP_FLAG = 1
1794 AND GROUP_ID = c_group_id;
1795
1796
1797 BEGIN
1798
1799 IF (verify_flag = 1) AND (module <> 2) THEN
1800 raise parameter_error;
1801 END IF;
1802
1803 IF (grp_id is null) THEN
1804 raise parameter_error;
1805 END IF;
1806
1807 expl_date := substr(rev_date, 1, 16);
1808 G_Module := module;
1809
1810 stmt_num := 0;
1811
1812 IF (expl_date is null) THEN
1813 select to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS')
1814 into expl_date
1815 from dual;
1816 ELSE
1817 -- we will make sure that the canonical and the nls_date formats are supported.
1818 -- Bug 4740913. Removed fnd_date.charDT_to_date as both fnd_date.charDT_to_date
1819 -- and fnd_date.displayDT_to_date do the same operation.
1820 -- Bug 16173516 - Modified to positional notation call of fnd_date.displayDT_to_date
1821 -- Named and Mixed notation calls in SQL statements do not support in 10g database.
1822 BEGIN
1823 select fnd_date.date_to_canonical(fnd_date.displayDT_to_date( rev_date, FND_DATE.calendar_aware_alt)) --calendar internationalization project
1824 into expl_date
1825 from dual;
1826 EXCEPTION WHEN OTHERS THEN
1827 BEGIN
1828 select to_char(to_date(rev_date, 'YYYY/MM/DD HH24:MI:SS'), 'YYYY/MM/DD HH24:MI:SS')
1829 into expl_date
1830 from dual;
1831 END;
1832 END;
1833 END IF;
1834
1835 IF (module = 1 or module = 4) THEN /* CST */
1836
1837 stmt_num := 3;
1838
1839 INSERT INTO BOM_EXPLOSION_TEMP
1840 (
1841 GROUP_ID,
1842 BILL_SEQUENCE_ID,
1843 COMPONENT_SEQUENCE_ID,
1844 ORGANIZATION_ID,
1845 COMPONENT_ITEM_ID,
1846 PLAN_LEVEL,
1847 EXTENDED_QUANTITY,
1848 BASIS_TYPE,
1849 COMPONENT_QUANTITY,
1850 SORT_ORDER,
1851 PROGRAM_UPDATE_DATE,
1852 TOP_BILL_SEQUENCE_ID,
1853 TOP_ITEM_ID,
1854 TOP_ALTERNATE_DESIGNATOR,
1855 COMPONENT_CODE,
1856 LOOP_FLAG
1857 )
1858 SELECT grp_id,
1859 0,
1860 NULL,
1861 nvl(BL.ORGANIZATION_ID, org_id),
1862 BL.ASSEMBLY_ITEM_ID,
1863 0,
1864 expl_qty,
1865 1,
1866 1,
1867 lpad('1', G_SortWidth, '0'),
1868 sysdate,
1869 0,
1870 BL.ASSEMBLY_ITEM_ID,
1871 NULL,
1872 to_char(BL.ASSEMBLY_ITEM_ID),
1873 2
1874 FROM BOM_LISTS BL
1875 WHERE BL.SEQUENCE_ID = list_id
1876 AND BL.ALTERNATE_DESIGNATOR IS NULL
1877 AND NOT EXISTS (SELECT 'NO BILL' FROM BOM_BILL_OF_MATERIALS BOM
1878 WHERE BOM.ORGANIZATION_ID =
1879 nvl(BL.ORGANIZATION_ID, org_id)
1880 AND BOM.ASSEMBLY_ITEM_ID = BL.ASSEMBLY_ITEM_ID
1881 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL);
1882
1883 IF SQL%NOTFOUND and num_of_assys = 0 THEN
1884 raise no_data_found;
1885 END IF;
1886
1887 END IF;
1888
1889 /*
1890 ** get the conc who values
1891 */
1892 IF (req_id <> 0) THEN
1893 stmt_num := 4;
1894 SELECT PROGRAM_APPLICATION_ID, CONCURRENT_PROGRAM_ID,
1895 REQUESTED_BY
1896 INTO prgm_appl_id, prg_id, user_id
1897 FROM FND_CONCURRENT_REQUESTS
1898 WHERE REQUEST_ID = req_id;
1899 ELSE
1900 prgm_appl_id := 1;
1901 prg_id := 1;
1902 user_id := 1;
1903 END IF;
1904
1905 /*
1906 ** call the exploder
1907 */
1908
1909 G_Allow_Date_Trimming_Flag := 'N';
1910
1911 exploders(
1912 verify_flag => verify_flag,
1913 online_flag => g_no,
1914 item_id => null, -- for forms
1915 org_id => org_id,
1916 alternate => null, -- for forms
1917 list_id => list_id, -- for reports
1918 order_by => order_by,
1919 grp_id => grp_id,
1920 session_id => session_id,
1921 req_id => req_id,
1922 prgm_appl_id => prgm_appl_id,
1923 prgm_id => prg_id,
1924 l_levels_to_explode => levels_to_explode,
1925 bom_or_eng => bom_or_eng,
1926 impl_flag => impl_flag,
1927 plan_factor_flag => plan_factor_flag,
1928 incl_lt_flag => incl_lt_flag,
1929 l_explode_option => explode_option,
1930 module => module,
1931 cst_type_id => cst_type_id,
1932 std_comp_flag => std_comp_flag,
1933 rev_date => expl_date,
1934 expl_qty => expl_qty,
1935 unit_number => '',
1936 release_option =>2, /*bug 8299615 Passed Released Option variable earlier not passing anything*/
1937 err_msg => out_message,
1938 error_code => out_code);
1939
1940 --bug:5362238 For Loop Report, propage loop flag up the hierarchy.
1941 IF ( verify_flag = 1 ) THEN
1942 FOR l_loop_flag_row_rec IN loop_flag_rows( grp_id )
1943 LOOP
1944 UPDATE BOM_EXPLOSION_TEMP bet_update
1945 SET bet_update.LOOP_FLAG = 1
1946 WHERE (bet_update.SORT_ORDER, bet_update.top_bill_sequence_id) IN
1947 ( SELECT bet.SORT_ORDER, bet.top_bill_sequence_id -- bug 13473719
1948 FROM BOM_EXPLOSION_TEMP bet
1949 WHERE
1950 bet.GROUP_ID = grp_id
1951 --
1952 -- bug 13473719
1953 -- Added the below condition to update the loop flag for hirearchial sorts orders
1954 -- based on the top_bill_sequence_id
1955 --
1956 and bet.top_bill_sequence_id = l_loop_flag_row_rec.top_bill_sequence_id
1957 CONNECT BY PRIOR
1958 SubStr( bet.SORT_ORDER, 1, (bet.PLAN_LEVEL * 7) ) = bet.SORT_ORDER
1959 AND PRIOR BET.GROUP_ID = BET.GROUP_ID -- Added for bug 13995506
1960 AND prior BET.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID -- Added for bug 13995506
1961 START WITH (bet.SORT_ORDER = l_loop_flag_row_rec.sort_order AND
1962 BET.GROUP_ID = BET.GROUP_ID AND -- Added for bug 13995506
1963 BET.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID)) -- Added for bug 13995506
1964 AND bet_update.GROUP_ID = grp_id;
1965 END LOOP;
1966 END IF; -- end if ( :P_VERIFY_FLAG = 1 )
1967
1968
1969 IF verify_flag <> 1 THEN
1970 IF out_code <> 0 THEN
1971 raise explode_error;
1972 END IF;
1973 ELSE
1974 IF out_code not in (0, 9999) THEN
1975 raise explode_error;
1976 END IF;
1977 END IF;
1978
1979 /*
1980 ** for a costed explosion, if temp or permanent rollup
1981 ** need to do rollup related stuff. But only if not frozen std cst type
1982 */
1983
1984 IF ((module = 1 or module = 4)and (report_option = 1 or report_option = 3 or
1985 report_option = 2)) THEN
1986
1987 /*
1988 ** commit here else may run out of rollback segments
1989 */
1990 IF (module = 1) THEN
1991 commit;
1992 END IF;
1993
1994
1995 /*
1996 ** insert low level codes from the explosion that was
1997 ** just performed
1998 */
1999 stmt_num := 5;
2000
2001 INSERT INTO CST_LOW_LEVEL_CODES
2002 (ROLLUP_ID, INVENTORY_ITEM_ID, LOW_LEVEL_CODE,
2003 LAST_UPDATE_DATE, LAST_UPDATED_BY,
2004 CREATION_DATE, CREATED_BY)
2005
2006 SELECT cst_rlp_id, COMPONENT_ITEM_ID, max(PLAN_LEVEL),
2007 sysdate, user_id, sysdate, user_id
2008 FROM BOM_EXPLOSION_TEMP
2009 WHERE GROUP_ID = grp_id
2010 GROUP BY COMPONENT_ITEM_ID;
2011
2012 /*
2013 ** if single level rollup, delete items that do not exist in bom_lists
2014 */
2015
2016 IF (rollup_option = 1) THEN
2017 stmt_num := 6;
2018
2019 DELETE CST_LOW_LEVEL_CODES CLLC
2020 WHERE NOT EXISTS (SELECT 'Item in list'
2021 FROM BOM_LISTS BL
2022 WHERE SEQUENCE_ID = list_id
2023 AND BL.ASSEMBLY_ITEM_ID = CLLC.INVENTORY_ITEM_ID)
2024 AND ROLLUP_ID = cst_rlp_id;
2025
2026 END IF;
2027
2028 rollup_date := to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS');
2029
2030 /*
2031 ** call the cost rollup here
2032 */
2033
2034 IF (impl_flag = 1) THEN
2035 unimpl_flag := 2;
2036 ELSE
2037 unimpl_flag := 1;
2038 END IF;
2039 /*
2040 rollup_status := CSTPUCRU.cstflcru(
2041 l_group_id => grp_id,
2042 l_organization_id => org_id,
2043 l_rollup_id => cst_rlp_id,
2044 l_cost_type_id => cst_type_id,
2045 req_id => req_id,
2046 prgm_appl_id => prgm_appl_id,
2047 prgm_id => prg_id,
2048 l_last_updated_by => user_id,
2049 conc_flag => 1,
2050 unimp_flag => unimpl_flag,
2051 locking_flag => lock_flag,
2052 rollup_date => rollup_date,
2053 revision_date => expl_date,
2054 alt_bom_designator => alt_desg,
2055 alt_rtg_designator => alt_rtg_desg,
2056 rollup_option => rollup_option,
2057 report_option => report_option,
2058 l_mfg_flag => bom_or_eng,
2059 err_buf => out_message);
2060
2061 IF (rollup_status <> 0) THEN
2062 raise rollup_error;
2063 END IF;
2064 */
2065 --commented for bug 5322048.
2066 /*
2067 ** delete low level codes
2068 */
2069 /* delete from cst_low_level_codes
2070 where rollup_id = cst_rlp_id;
2071 */
2072 END IF;
2073
2074 /*
2075 ** do the post explosion updates for costing attributes only if no
2076 ** report is selected
2077
2078 */
2079 IF ((module = 1 or module = 4) and report_option <> 2) THEN
2080 BOMPCEXP.cst_exploder(
2081 grp_id => grp_id,
2082 org_id => org_id,
2083 cst_type_id => cst_type_id,
2084 err_msg => out_message,
2085 error_code => out_code);
2086 END IF;
2087
2088 error_code := out_code;
2089 /* Modified for bug 13901371 - Added substring to truncate the message below 80 characters,
2090 so that in the rdf file error_message does not through ORA-06502 error while assigning
2091 the error message, if the error message size is more than 80 characters.
2092 */
2093 err_msg := substrb(out_message, 1, 75);
2094
2095 EXCEPTION
2096 WHEN NO_DATA_FOUND THEN
2097 error_code := SQLCODE;
2098 err_msg := 'BOMPEXPL(' || stmt_num || '): ' ||
2099 substrb(SQLERRM, 1, 60);
2100 /* WHEN rollup_error THEN
2101 error_code := rollup_status;
2102 err_msg := out_message;*/
2103 WHEN explode_error THEN
2104 error_code := out_code;
2105 /* Modified for bug 13901371 - Added substring to truncate the message below 80 characters,
2106 so that in the rdf file error_message does not through ORA-06502 error while assigning
2107 the error message, if the error message size is more than 80 characters.
2108 */
2109 err_msg := substrb(out_message, 1, 75);
2110 WHEN parameter_error THEN
2111 error_code := 9997;
2112 err_msg := 'BOMPEXPL: verify parameters';
2113 WHEN OTHERS THEN
2114 error_code := SQLCODE;
2115 err_msg := 'BOMPEXPL(' || stmt_num || '): ' ||
2116 substrb(SQLERRM, 1, 60);
2117 END explosion_report;
2118
2119 /* new procedure for PDI usage.
2120 This exploder will populate trimmed dates in the explosion table
2121 */
2122 procedure explode(
2123 verify_flag IN NUMBER DEFAULT 0,
2124 org_id IN NUMBER,
2125 order_by IN NUMBER DEFAULT 1,
2126 grp_id IN NUMBER,
2127 session_id IN NUMBER DEFAULT 0,
2128 levels_to_explode IN NUMBER DEFAULT 1,
2129 bom_or_eng IN NUMBER DEFAULT 1,
2130 impl_flag IN NUMBER DEFAULT 1,
2131 plan_factor_flag IN NUMBER DEFAULT 2,
2132 explode_option IN NUMBER DEFAULT 1,
2133 module IN NUMBER DEFAULT 2,
2134 cst_type_id IN NUMBER DEFAULT 0,
2135 std_comp_flag IN NUMBER DEFAULT 0,
2136 expl_qty IN NUMBER DEFAULT 1,
2137 item_id IN NUMBER,
2138 alt_desg IN VARCHAR2 DEFAULT '',
2139 comp_code IN VARCHAR2 DEFAULT '',
2140 rev_date IN VARCHAR2,
2141 unit_number IN VARCHAR2 DEFAULT '',
2142 err_msg OUT NOCOPY VARCHAR2,
2143 error_code OUT NOCOPY NUMBER) AS
2144
2145 Begin
2146
2147 G_Allow_Date_Trimming_Flag := 'Y';
2148
2149 exploder_userexit (
2150 verify_flag ,
2151 org_id ,
2152 order_by ,
2153 grp_id ,
2154 session_id ,
2155 levels_to_explode ,
2156 bom_or_eng ,
2157 impl_flag ,
2158 plan_factor_flag ,
2159 explode_option ,
2160 module ,
2161 cst_type_id ,
2162 std_comp_flag ,
2163 expl_qty ,
2164 item_id ,
2165 alt_desg ,
2166 comp_code ,
2167 rev_date ,
2168 unit_number ,
2169 0, --release_option
2170 err_msg ,
2171 error_code ) ;
2172 end;
2173
2174 END bompexpl;