[Home] [Help]
PACKAGE BODY: APPS.BOMPCEXP
Source
1 PACKAGE BODY BOMPCEXP AS
2 /* $Header: BOMCEXPB.pls 120.1.12010000.2 2008/10/09 21:31:21 liawei ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMCEXPB.pls |
9 | Description : This is the costing exploder. This exploder needs |
10 | to join to cst_default_cost_view to get the costing |
11 | attributes. |
12 | Parameters: org_id organization_id |
13 | grp_id unique value to identify current explosion |
14 | use value from sequence bom_explosion_temp_s|
15 | cst_type_id cost type id |
16 | err_msg error message out buffer |
17 | error_code error code out. returns sql error code |
18 | History: |
19 | 01-FEB-93 Shreyas Shah Initial coding |
20 | 06-JUN-93 Shreyas Shah Scrapped the costing exploder that joined |
21 | to CST_DEFAULT_COST_VIEW since it was |
22 | very slow. Now just calling bom exploder |
23 | and doing a post explosion update |
24 | 23-JUN-93 Evelyn Tran Add checking of COMPONENT_YIELD_FLAG when |
25 | computing extended quantity |
26 | 09/05/96 Robert Yee Increase Sort Order Width to 4 from 3 |
27 | (Bills can have >= 1000 components |
28 +==========================================================================*/
29
30 PROCEDURE cst_exploder(
31 grp_id IN NUMBER,
32 org_id IN NUMBER,
33 cst_type_id IN NUMBER,
34 inq_flag IN NUMBER := 2,
35 err_msg IN OUT NOCOPY VARCHAR2,
36 error_code IN OUT NOCOPY NUMBER) AS
37
38 counter NUMBER;
39 cost_org_id NUMBER;
40 l_comp_yield_flag NUMBER;
41 l_default_cost_type_id NUMBER;
42 l_error_code NUMBER;
43 exploded_levels NUMBER;
44 l_err_msg VARCHAR2(80);
45 X_SortWidth number := BOMPBEXP.G_SortWidth; -- 4
46
47 /* Added following cursor for Bug 2566842 */
48
49 CURSOR Explosion (c_grp_id NUMBER,
50 c_counter NUMBER,
51 c_cost_org_id NUMBER,
52 c_org_id NUMBER,
53 c_cst_type_id NUMBER,
54 c_l_comp_yield_flag NUMBER,
55 c_l_default_cost_type_id NUMBER,
56 c_X_SortWidth NUMBER
57 ) IS
58 SELECT
59 nvl(CIC.ITEM_COST, nvl(CIC_DEF.ITEM_COST, 0)),
60 nvl(CIC.BASED_ON_ROLLUP_FLAG,nvl(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)),
61 nvl(CIC.SHRINKAGE_RATE, nvl(CIC_DEF.SHRINKAGE_RATE, 0)),
62 (BET1.EXTENDED_QUANTITY * nvl(BET.COMPONENT_QUANTITY, 0)
63 * (nvl(BET.PLANNING_FACTOR, 100) / 100)
64 )
65 /
66 (decode(c_l_comp_yield_flag, 2, 1,
67 decode(nvl(BET.COMPONENT_YIELD_FACTOR, 0), 0, 1,
68 nvl(BET.COMPONENT_YIELD_FACTOR, 0)
69 )
70 ) * (1 - nvl(BET1.SHRINKAGE_RATE, 0))
71 ),
72 decode(BET1.EXTEND_COST_FLAG, 2, 2,
73 decode(BET1.INVENTORY_ASSET_FLAG, 2, 2,
74 decode(BET1.BASED_ON_ROLLUP_FLAG, 2, 2,
75 decode(BET.INCLUDE_IN_ROLLUP_FLAG, 1, 1, 2)))),
76 nvl(CIC.COST_TYPE_ID,
77 nvl(CIC_DEF.COST_TYPE_ID, BET1.ACTUAL_COST_TYPE_ID)),
78 nvl(CIC.INVENTORY_ASSET_FLAG,
79 nvl(CIC_DEF.INVENTORY_ASSET_FLAG, 2)),
80 BET.TOP_BILL_SEQUENCE_ID,
81 BET.ROWID
82 FROM CST_ITEM_COSTS CIC,
83 CST_ITEM_COSTS CIC_DEF,
84 BOM_EXPLOSION_TEMP BET_MUST_HAVE,
85 BOM_EXPLOSION_TEMP BET1,
86 BOM_EXPLOSION_TEMP BET
87 WHERE BET1.GROUP_ID = c_grp_id
88 AND BET1.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID
89 AND BET1.SORT_ORDER =
90 SUBSTR(BET.SORT_ORDER, 1, c_counter*c_X_SortWidth)
91 AND BET_MUST_HAVE.ROWID = BET.ROWID
92 AND CIC.COST_TYPE_ID(+) = c_cst_type_id
93 AND CIC.ORGANIZATION_ID(+) = c_cost_org_id
94 AND CIC.INVENTORY_ITEM_ID(+) =
95 BET_MUST_HAVE.COMPONENT_ITEM_ID
96 AND CIC_DEF.COST_TYPE_ID(+) = c_l_default_cost_type_id
97 AND CIC_DEF.ORGANIZATION_ID(+) = c_cost_org_id
98 AND CIC_DEF.INVENTORY_ITEM_ID(+)
99 = BET_MUST_HAVE.COMPONENT_ITEM_ID
100 AND BET.GROUP_ID = c_grp_id
101 AND BET.PLAN_LEVEL = c_counter
102 AND EXISTS (SELECT 'Costing must be enabled'
103 FROM MTL_SYSTEM_ITEMS
104 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
105 AND ORGANIZATION_ID = c_org_id
106 AND COSTING_ENABLED_FLAG = 'Y');
107
108 /* Added For Bug 6973616.*/
109 CURSOR Explosion_small (c_grp_id NUMBER,
110 c_counter NUMBER,
111 c_cost_org_id NUMBER,
112 c_org_id NUMBER,
113 c_cst_type_id NUMBER,
114 c_l_comp_yield_flag NUMBER,
115 c_l_default_cost_type_id NUMBER,
116 c_X_SortWidth NUMBER)
117 IS
118 SELECT
119 nvl(CIC.ITEM_COST, nvl(CIC_DEF.ITEM_COST, 0)),
120 nvl(CIC.BASED_ON_ROLLUP_FLAG,nvl(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)),
121 nvl(CIC.SHRINKAGE_RATE, nvl(CIC_DEF.SHRINKAGE_RATE, 0)),
122 (BET1.EXTENDED_QUANTITY*nvl(BET.COMPONENT_QUANTITY, 0)*(nvl(BET.PLANNING_FACTOR,100)/100))/
123 (decode(c_l_comp_yield_flag,2,1,decode(nvl(BET.COMPONENT_YIELD_FACTOR,0),0,1,nvl(BET.COMPONENT_YIELD_FACTOR,0)))*(1-nvl(BET1.SHRINKAGE_RATE,0))),
124 decode(BET1.EXTEND_COST_FLAG, 2, 2,decode(BET1.INVENTORY_ASSET_FLAG, 2, 2,decode(BET1.BASED_ON_ROLLUP_FLAG, 2, 2,decode(BET.INCLUDE_IN_ROLLUP_FLAG, 1, 1, 2)))),
125 nvl(CIC.COST_TYPE_ID,
126 nvl(CIC_DEF.COST_TYPE_ID, BET1.ACTUAL_COST_TYPE_ID)),
127 nvl(CIC.INVENTORY_ASSET_FLAG,nvl(CIC_DEF.INVENTORY_ASSET_FLAG, 2)),
128 BET.TOP_BILL_SEQUENCE_ID,
129 BET.ROWID
130 FROM
131 CST_ITEM_COSTS CIC,
132 CST_ITEM_COSTS CIC_DEF,
133 BOM_SMALL_EXPL_TEMP BET_MUST_HAVE,
134 BOM_SMALL_EXPL_TEMP BET1,
135 BOM_SMALL_EXPL_TEMP BET
136 WHERE BET1.GROUP_ID = c_grp_id
137 AND BET1.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID
138 AND BET1.SORT_ORDER = SUBSTR(BET.SORT_ORDER, 1, c_counter*c_X_SortWidth)
139 AND BET_MUST_HAVE.ROWID = BET.ROWID
140 AND CIC.COST_TYPE_ID(+) = c_cst_type_id
141 AND CIC.ORGANIZATION_ID(+) = c_cost_org_id
142 AND CIC.INVENTORY_ITEM_ID(+) = BET_MUST_HAVE.COMPONENT_ITEM_ID
143 AND CIC_DEF.COST_TYPE_ID(+) = c_l_default_cost_type_id
144 AND CIC_DEF.ORGANIZATION_ID(+) = c_cost_org_id
145 AND CIC_DEF.INVENTORY_ITEM_ID(+) = BET_MUST_HAVE.COMPONENT_ITEM_ID
146 AND BET.GROUP_ID = c_grp_id
147 AND BET.PLAN_LEVEL = c_counter
148 AND EXISTS (SELECT 'Costing must be enabled'
149 FROM MTL_SYSTEM_ITEMS
150 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
151 AND ORGANIZATION_ID = c_org_id
152 AND COSTING_ENABLED_FLAG = 'Y');
153
154
155 TYPE number_tab_tp IS TABLE OF NUMBER
156 INDEX BY BINARY_INTEGER;
157
158 TYPE varchar_tab_tp IS TABLE OF VARCHAR2(30)
159 INDEX BY BINARY_INTEGER;
160
161 l_item_cost number_tab_tp;
162 l_rollup number_tab_tp;
163 l_shrinkage number_tab_tp;
164 l_extnd_qty number_tab_tp;
165 l_extnd_cost number_tab_tp;
166 l_actual_cost number_tab_tp;
167 l_asset_flag number_tab_tp;
168 l_top_bill_id number_tab_tp;
169 l_row_id varchar_tab_tp;
170
171 Loop_Count_Val Number := 0;
172
173 /* Bug 2566842 */
174
175 BEGIN
176 /*
177 ** select the cost org id
178 */
179 SELECT COST_ORGANIZATION_ID
180 INTO cost_org_id
181 FROM MTL_PARAMETERS
182 WHERE ORGANIZATION_ID = org_id;
183
184 /*
185 ** select COMPONENT_YIELD_FLAG
186 */
187 SELECT COMPONENT_YIELD_FLAG
188 INTO l_comp_yield_flag
189 FROM CST_COST_TYPES
190 WHERE COST_TYPE_ID = cst_type_id;
191
192 /*
193 ** for non-inquiry calls use the bigger temp table
194 */
195 if (inq_flag = 2) then
196
197 /*
198 ** need to update certain columns dependent on the cost rollup
199 ** update level 0 seperately
200 */
201
202 UPDATE BOM_EXPLOSION_TEMP BET
203 SET (ITEM_COST, BASED_ON_ROLLUP_FLAG, EXTEND_COST_FLAG,
204 ACTUAL_COST_TYPE_ID, SHRINKAGE_RATE, INVENTORY_ASSET_FLAG) =
205 (SELECT /*+ ORDERED
206 INDEX (CIC,CST_ITEM_COSTS_U1)
207 INDEX (CCT,CST_COST_TYPES_U1)
208 USE_NL (CIC CCT) */
209 nvl(CIC.ITEM_COST,0),
210 nvl(CIC.BASED_ON_ROLLUP_FLAG, 2),
211 DECODE(CIC.INVENTORY_ASSET_FLAG, 2, 2,
212 DECODE(CIC.BASED_ON_ROLLUP_FLAG, 1, 1, 2)),
213 nvl(CIC.COST_TYPE_ID, cst_type_id),
214 nvl(CIC.SHRINKAGE_RATE,0),
215 nvl(CIC.INVENTORY_ASSET_FLAG, 2)
216 FROM CST_ITEM_COSTS CIC,
217 CST_COST_TYPES CCT
218 Where CIC.ORGANIZATION_ID=NVL(CCT.ORGANIZATION_ID,CIC.ORGANIZATION_ID)
219 AND (((CIC.COST_TYPE_ID = CCT.DEFAULT_COST_TYPE_ID)
220 AND (NOT EXISTS
221 (SELECT 'X' FROM CST_ITEM_COSTS CIC2
222 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
223 AND CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
224 AND CIC2.COST_TYPE_ID = CCT.COST_TYPE_ID)))
225 OR (CIC.COST_TYPE_ID = CCT.COST_TYPE_ID))
226 AND CCT.COST_TYPE_ID(+) = cst_type_id
227 AND CIC.INVENTORY_ITEM_ID(+) = BET.TOP_ITEM_ID
228 AND CIC.ORGANIZATION_ID(+) = cost_org_id)
229 WHERE GROUP_ID = grp_id
230 AND PLAN_LEVEL = 0;
231
232 SELECT nvl(MAX(PLAN_LEVEL), 0)
233 INTO exploded_levels
234 FROM BOM_EXPLOSION_TEMP
235 WHERE GROUP_ID = grp_id;
236
237 SELECT DEFAULT_COST_TYPE_ID
238 INTO l_default_cost_type_id
239 FROM cst_cost_types
240 WHERE COST_TYPE_ID = cst_type_id;
241
242 /* Bug 2566842 */
243
244 FOR counter IN 1..exploded_levels LOOP
245
246 -- Delete pl/sql tables.
247
248 l_item_cost.delete;
249 l_rollup.delete;
250 l_shrinkage.delete;
251 l_extnd_qty.delete;
252 l_extnd_cost.delete;
253 l_actual_cost.delete;
254 l_asset_flag.delete;
255 l_top_bill_id.delete;
256 l_row_id.delete;
257
258
259 IF not Explosion%isopen then
260 open explosion( grp_id,
261 counter,
262 cost_org_id,
263 org_id,
264 cst_type_id,
265 l_comp_yield_flag,
266 l_default_cost_type_id,
267 X_SortWidth);
268 End if;
269
270 FETCH Explosion bulk collect into
271 l_item_cost,
272 l_rollup,
273 l_shrinkage,
274 l_extnd_qty,
275 l_extnd_cost,
276 l_actual_cost,
277 l_asset_flag,
278 l_top_bill_id,
279 l_row_id;
280
281 loop_count_val := explosion%rowcount;
282
283 CLOSE Explosion;
284
285
286 FORALL i IN 1..loop_count_val
287
288 UPDATE BOM_EXPLOSION_TEMP BET
289 SET ITEM_COST = l_item_cost(i),
290 BASED_ON_ROLLUP_FLAG = l_rollup(i),
291 SHRINKAGE_RATE = l_shrinkage(i),
292 EXTENDED_QUANTITY = l_extnd_qty(i),
293 EXTEND_COST_FLAG = l_extnd_cost(i),
294 ACTUAL_COST_TYPE_ID = l_actual_cost(i),
295 INVENTORY_ASSET_FLAG = l_asset_flag(i)
296 WHERE BET.GROUP_ID = grp_id
297 AND BET.PLAN_LEVEL = counter
298 AND BET.TOP_BILL_SEQUENCE_ID = l_top_bill_id(i)
299 AND BET.ROWID = l_row_id(i)
300 AND EXISTS (SELECT 'Costing must be enabled'
301 FROM MTL_SYSTEM_ITEMS
302 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
303 AND ORGANIZATION_ID = org_id);
304 END LOOP;
305
306 /* Bug 2566842 */
307
308 /*
309 FOR counter IN 1..exploded_levels LOOP
310 UPDATE BOM_EXPLOSION_TEMP BET
311 SET (ITEM_COST,
312 BASED_ON_ROLLUP_FLAG,
313 SHRINKAGE_RATE,
314 EXTENDED_QUANTITY,
315 EXTEND_COST_FLAG,
316 ACTUAL_COST_TYPE_ID,
317 INVENTORY_ASSET_FLAG) =
318 (SELECT
319 nvl(CIC.ITEM_COST, nvl(CIC_DEF.ITEM_COST, 0)),
320 nvl(CIC.BASED_ON_ROLLUP_FLAG,
321 nvl(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)),
322 nvl(CIC.SHRINKAGE_RATE, nvl(CIC_DEF.SHRINKAGE_RATE, 0)),
323 (BET1.EXTENDED_QUANTITY * nvl(BET.COMPONENT_QUANTITY, 0)
324 * (nvl(BET.PLANNING_FACTOR, 100) / 100)
325 )
326 /
327 (decode(l_comp_yield_flag, 2, 1,
328 decode(nvl(BET.COMPONENT_YIELD_FACTOR, 0), 0, 1,
329 nvl(BET.COMPONENT_YIELD_FACTOR, 0)
330 )
331 )
332 *
333 (1 - nvl(BET1.SHRINKAGE_RATE, 0))
334 ),
335 decode(BET1.EXTEND_COST_FLAG, 2, 2,
336 decode(BET1.INVENTORY_ASSET_FLAG, 2, 2,
337 decode(BET1.BASED_ON_ROLLUP_FLAG, 2, 2,
338 decode(BET.INCLUDE_IN_ROLLUP_FLAG, 1, 1, 2)))),
339 nvl(CIC.COST_TYPE_ID,
340 nvl(CIC_DEF.COST_TYPE_ID, BET1.ACTUAL_COST_TYPE_ID)),
341 nvl(CIC.INVENTORY_ASSET_FLAG,
342 nvl(CIC_DEF.INVENTORY_ASSET_FLAG, 2))
343 FROM CST_ITEM_COSTS CIC,
344 CST_ITEM_COSTS CIC_DEF,
345 BOM_EXPLOSION_TEMP BET_MUST_HAVE,
346 BOM_EXPLOSION_TEMP BET1
347 WHERE BET1.GROUP_ID = grp_id
348 AND BET1.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID
349 AND BET1.SORT_ORDER =
350 SUBSTR(BET.SORT_ORDER, 1, counter*X_SortWidth)
351 AND BET_MUST_HAVE.ROWID = BET.ROWID
352 AND CIC.COST_TYPE_ID(+) = cst_type_id
353 AND CIC.ORGANIZATION_ID(+) = cost_org_id
354 AND CIC.INVENTORY_ITEM_ID(+) =
355 BET_MUST_HAVE.COMPONENT_ITEM_ID
356 AND CIC_DEF.COST_TYPE_ID(+) = l_default_cost_type_id
357 AND CIC_DEF.ORGANIZATION_ID(+) = cost_org_id
358 AND CIC_DEF.INVENTORY_ITEM_ID(+)
359 = BET_MUST_HAVE.COMPONENT_ITEM_ID)
360 WHERE BET.GROUP_ID = grp_id
361 AND BET.PLAN_LEVEL = counter
362 AND EXISTS (SELECT 'Costing must be enabled'
363 FROM MTL_SYSTEM_ITEMS
364 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
365 AND ORGANIZATION_ID = org_id
366 AND COSTING_ENABLED_FLAG = 'Y');
367
368 END LOOP;
369
370 */
371 else
372
373 UPDATE BOM_SMALL_EXPL_TEMP BET
374 SET (ITEM_COST, BASED_ON_ROLLUP_FLAG, EXTEND_COST_FLAG,
375 ACTUAL_COST_TYPE_ID, SHRINKAGE_RATE, INVENTORY_ASSET_FLAG) =
376 (SELECT /*+ ORDERED
377 INDEX (CIC,CST_ITEM_COSTS_U1)
378 INDEX (CCT,CST_COST_TYPES_U1)
379 USE_NL (CIC CCT) */
380 nvl(CIC.ITEM_COST,0),
381 nvl(CIC.BASED_ON_ROLLUP_FLAG, 2),
382 DECODE(CIC.INVENTORY_ASSET_FLAG, 2, 2,
383 DECODE(CIC.BASED_ON_ROLLUP_FLAG, 1, 1, 2)),
384 nvl(CIC.COST_TYPE_ID, cst_type_id),
385 nvl(CIC.SHRINKAGE_RATE,0),
386 nvl(CIC.INVENTORY_ASSET_FLAG, 2)
387 FROM CST_ITEM_COSTS CIC,
388 CST_COST_TYPES CCT
389 Where CIC.ORGANIZATION_ID=NVL(CCT.ORGANIZATION_ID,CIC.ORGANIZATION_ID)
390 AND (((CIC.COST_TYPE_ID = CCT.DEFAULT_COST_TYPE_ID)
391 AND (NOT EXISTS
392 (SELECT 'X' FROM CST_ITEM_COSTS CIC2
393 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
394 AND CIC2.ORGANIZATION_ID = CIC.ORGANIZATION_ID
395 AND CIC2.COST_TYPE_ID = CCT.COST_TYPE_ID)))
396 OR (CIC.COST_TYPE_ID = CCT.COST_TYPE_ID))
397 AND CCT.COST_TYPE_ID(+) = cst_type_id
398 AND CIC.INVENTORY_ITEM_ID(+) = BET.TOP_ITEM_ID
399 AND CIC.ORGANIZATION_ID(+) = cost_org_id)
400 WHERE GROUP_ID = grp_id
401 AND PLAN_LEVEL = 0;
402
403 SELECT nvl(MAX(PLAN_LEVEL), 0)
404 INTO exploded_levels
405 FROM BOM_SMALL_EXPL_TEMP
406 WHERE GROUP_ID = grp_id;
407 /*
408 FOR counter IN 1..exploded_levels LOOP
409 UPDATE BOM_SMALL_EXPL_TEMP BET
410 SET (ITEM_COST,
411 BASED_ON_ROLLUP_FLAG,
412 SHRINKAGE_RATE,
413 EXTENDED_QUANTITY,
414 EXTEND_COST_FLAG,
415 ACTUAL_COST_TYPE_ID,
416 INVENTORY_ASSET_FLAG) =
417 (SELECT
418 nvl(CIC.ITEM_COST, nvl(CIC_DEF.ITEM_COST, 0)),
419 nvl(CIC.BASED_ON_ROLLUP_FLAG,
420 nvl(CIC_DEF.BASED_ON_ROLLUP_FLAG, 2)),
421 nvl(CIC.SHRINKAGE_RATE, nvl(CIC_DEF.SHRINKAGE_RATE, 0)),
422 (BET1.EXTENDED_QUANTITY * nvl(BET.COMPONENT_QUANTITY, 0)
423 * (nvl(BET.PLANNING_FACTOR, 100) / 100)
424 )
425 /
426 (decode(l_comp_yield_flag, 2, 1,
427 decode(nvl(BET.COMPONENT_YIELD_FACTOR, 0), 0, 1,
428 nvl(BET.COMPONENT_YIELD_FACTOR, 0)
429 )
430 )
431 *
432 (1 - nvl(BET1.SHRINKAGE_RATE, 0))
433 ),
434 decode(BET1.EXTEND_COST_FLAG, 2, 2,
435 decode(BET1.INVENTORY_ASSET_FLAG, 2, 2,
436 decode(BET1.BASED_ON_ROLLUP_FLAG, 2, 2,
437 decode(BET.INCLUDE_IN_ROLLUP_FLAG, 1, 1, 2)))),
438 nvl(CIC.COST_TYPE_ID,
439 nvl(CIC_DEF.COST_TYPE_ID, BET1.ACTUAL_COST_TYPE_ID)),
440 nvl(CIC.INVENTORY_ASSET_FLAG,
441 nvl(CIC_DEF.INVENTORY_ASSET_FLAG, 2))
442 FROM CST_ITEM_COSTS CIC,
443 CST_ITEM_COSTS CIC_DEF,
444 BOM_SMALL_EXPL_TEMP BET_MUST_HAVE,
445 BOM_SMALL_EXPL_TEMP BET1
446 WHERE BET1.GROUP_ID = grp_id
447 AND BET1.TOP_BILL_SEQUENCE_ID = BET.TOP_BILL_SEQUENCE_ID
448 AND BET1.SORT_ORDER =
449 SUBSTR(BET.SORT_ORDER, 1, counter*X_SortWidth)
450 AND BET_MUST_HAVE.ROWID = BET.ROWID
451 AND CIC.COST_TYPE_ID(+) = cst_type_id
452 AND CIC.ORGANIZATION_ID(+) = cost_org_id
453 AND CIC.INVENTORY_ITEM_ID(+) =
454 BET_MUST_HAVE.COMPONENT_ITEM_ID
455 AND CIC_DEF.COST_TYPE_ID(+) = l_default_cost_type_id
456 AND CIC_DEF.ORGANIZATION_ID(+) = cost_org_id
457 AND CIC_DEF.INVENTORY_ITEM_ID(+)
458 = BET_MUST_HAVE.COMPONENT_ITEM_ID)
459 WHERE BET.GROUP_ID = grp_id
460 AND BET.PLAN_LEVEL = counter
461 AND EXISTS (SELECT 'Costing must be enabled'
462 FROM MTL_SYSTEM_ITEMS
463 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
464 AND ORGANIZATION_ID = org_id
465 AND COSTING_ENABLED_FLAG = 'Y');
466
467 END LOOP;
468 */
469 /* Modified For Bug 6973616.*/
470
471 FOR counter IN 1..exploded_levels LOOP
472
473 l_item_cost.delete;
474 l_rollup.delete;
475 l_shrinkage.delete;
476 l_extnd_qty.delete;
477 l_extnd_cost.delete;
478 l_actual_cost.delete;
479 l_asset_flag.delete;
480 l_top_bill_id.delete;
481 l_row_id.delete;
482
483 IF not Explosion_small%isopen then
484 open Explosion_small( grp_id,
485 counter,
486 cost_org_id,
487 org_id,
488 cst_type_id,
489 l_comp_yield_flag,
490 l_default_cost_type_id,
491 X_SortWidth);
492 End if;
493
494 FETCH Explosion_small bulk collect into
495 l_item_cost,
496 l_rollup,
497 l_shrinkage,
498 l_extnd_qty,
499 l_extnd_cost,
500 l_actual_cost,
501 l_asset_flag,
502 l_top_bill_id,
503 l_row_id;
504
505 loop_count_val := Explosion_small%rowcount;
506
507 CLOSE Explosion_small;
508
509 FORALL i IN 1..loop_count_val
510
511 UPDATE BOM_SMALL_EXPL_TEMP BET SET
512 ITEM_COST = l_item_cost(i),
513 BASED_ON_ROLLUP_FLAG = l_rollup(i),
514 SHRINKAGE_RATE = l_shrinkage(i),
515 EXTENDED_QUANTITY = l_extnd_qty(i),
516 EXTEND_COST_FLAG = l_extnd_cost(i),
517 ACTUAL_COST_TYPE_ID = l_actual_cost(i),
518 INVENTORY_ASSET_FLAG = l_asset_flag(i)
519 WHERE BET.GROUP_ID = grp_id
520 AND BET.PLAN_LEVEL = counter
521 AND BET.TOP_BILL_SEQUENCE_ID = l_top_bill_id(i)
522 AND BET.ROWID = l_row_id(i)
523 AND EXISTS (SELECT 'Costing must be enabled'
524 FROM MTL_SYSTEM_ITEMS
525 WHERE INVENTORY_ITEM_ID = BET.COMPONENT_ITEM_ID
526 AND ORGANIZATION_ID = org_id);
527 END LOOP;
528
529 end if;
530 /*
531 ** exception handlers
532 */
533 EXCEPTION
534 WHEN OTHERS THEN
535 error_code := SQLCODE;
536 err_msg := 'BOMPCEXP:' || substrb(SQLERRM,1,60);
537
538 END cst_exploder;
539
540 END BOMPCEXP;