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