[Home] [Help]
PACKAGE BODY: APPS.CTO_CONFIG_COST_PK
Source
1 package body CTO_CONFIG_COST_PK as
2 /* $Header: CTOCSTRB.pls 120.5.12010000.2 2008/09/26 13:29:14 ntungare ship $ */
3 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
4
5 --Bugfix 6717614: Added this new function
6 function cost_update_required (p_config_item_id number,
7 p_organization_id number,
8 p_cto_cost_type_id number
9 )
10 return varchar2
11 is
12
13 CURSOR cost_details(c_config_item_id number, c_organization_id number, c_cost_type number) IS
14 select cst.inventory_item_id, cst.organization_id, cst.cost_type_id,
15 cst.item_cost , cst.material_cost, cst.material_overhead_cost,
16 cst.resource_cost, cst.outside_processing_cost, cst.overhead_cost
17 --cicd.cost_element_id ,cicd.item_cost cicd_item_cost
18 from cst_item_costs cst
19 where cst.inventory_item_id = c_config_item_id
20 AND cst.organization_id = c_organization_id
21 AND cst.cost_type_id = c_cost_type;
22
23 v_cost_details_frozen cost_details%ROWTYPE;
24 v_cost_details_cto cost_details%ROWTYPE;
25
26
27 begin
28 IF PG_DEBUG <> 0 THEN
29 oe_debug_pub.add( 'Inside cost_update_required ' ,1 ) ;
30 END IF;
31
32 OPEN cost_details(p_config_item_id, p_organization_id, 1);
33 FETCH cost_details INTO v_cost_details_frozen;
34
35 IF PG_DEBUG <> 0 THEN
36 oe_debug_pub.add( 'v_cost_details_frozen ' ,1 ) ;
37 oe_debug_pub.add( ' iid ' || v_cost_details_frozen.inventory_item_id ||
38 ' org ' || to_char( v_cost_details_frozen.organization_id) ||
39 ' cstyp ' || to_char( v_cost_details_frozen.cost_type_id) ||
40 ' icst ' || to_char( v_cost_details_frozen.item_cost ) ||
41 ' mcst ' || to_char( v_cost_details_frozen.material_cost ) ||
42 ' mocst ' || to_char( v_cost_details_frozen.material_overhead_cost) ||
43 ' opcst ' || to_char( v_cost_details_frozen.outside_processing_cost ) ||
44 ' ocst ' || to_char( v_cost_details_frozen.overhead_cost ) ||
45 ' rcst ' || to_char( v_cost_details_frozen.resource_cost ) , 1 ) ;
46 END IF;
47
48 CLOSE cost_details;
49
50 OPEN cost_details(p_config_item_id, p_organization_id, p_cto_cost_type_id);
51 FETCH cost_details INTO v_cost_details_cto;
52
53 IF PG_DEBUG <> 0 THEN
54 oe_debug_pub.add( 'v_cost_details_cto ' ,1 ) ;
55 oe_debug_pub.add( ' iid ' || v_cost_details_cto.inventory_item_id ||
56 ' org ' || to_char( v_cost_details_cto.organization_id) ||
57 ' cstyp ' || to_char( v_cost_details_cto.cost_type_id) ||
58 ' icst ' || to_char( v_cost_details_cto.item_cost ) ||
59 ' mcst ' || to_char( v_cost_details_cto.material_cost ) ||
60 ' mocst ' || to_char( v_cost_details_cto.material_overhead_cost) ||
61 ' opcst ' || to_char( v_cost_details_cto.outside_processing_cost ) ||
62 ' ocst ' || to_char( v_cost_details_cto.overhead_cost ) ||
63 ' rcst ' || to_char( v_cost_details_cto.resource_cost ) , 1 ) ;
64 END IF;
65
66 CLOSE cost_details;
67
68 IF ( nvl(v_cost_details_frozen.item_cost,0) = nvl(v_cost_details_cto.item_cost,0) ) THEN
69
70 IF PG_DEBUG <> 0 THEN
71 oe_debug_pub.add('***Inside 1st if.. total cost is same..' , 1);
72 END IF; --item cost same.. check for cost components
73
74 IF Nvl(v_cost_details_frozen.material_cost, -1) = Nvl(v_cost_details_cto.material_cost, -1) and
75 Nvl(v_cost_details_frozen.material_overhead_cost, -1) = Nvl(v_cost_details_cto.material_overhead_cost, -1) and
76 Nvl(v_cost_details_frozen.resource_cost, -1) = Nvl(v_cost_details_cto.resource_cost, -1) and
77 Nvl(v_cost_details_frozen.outside_processing_cost, -1) = Nvl(v_cost_details_cto.outside_processing_cost, -1) AND
78 Nvl(v_cost_details_frozen.overhead_cost, -1) = Nvl(v_cost_details_cto.overhead_cost, -1) THEN
79
80 IF PG_DEBUG <> 0 THEN
81 oe_debug_pub.add('***Inside 2nd if.. component costs are same..no processing needed' , 1);
82 END IF;
83
84 return 'N';
85 else
86 return 'Y';
87 END if;
88 else
89 return 'Y';
90 END if;
91 END cost_update_required;
92
93 procedure populate_buy_cost( p_line_id number
94 , p_config_item_id number
95 , p_organization_id number
96 , p_buy_cost_type_id number
97 , p_buy_item_cost number
98 )
99 is
100 lStmtNumber number;
101 begin
102
103 /*-------------------------------------------------------+
104 Insert a row into the cst_item_costs_table
105 +------------------------------------------------------- */
106
107 lStmtNumber := 220;
108
109 insert into CST_ITEM_COSTS
110 (inventory_item_id,
111 organization_id,
112 cost_type_id,
113 last_update_date,
114 last_updated_by,
115 creation_date,
116 created_by,
117 last_update_login,
118 inventory_asset_flag,
119 lot_size,
120 based_on_rollup_flag,
121 shrinkage_rate,
122 defaulted_flag,
123 cost_update_id,
124 pl_material,
125 pl_material_overhead,
126 pl_resource,
127 pl_outside_processing,
128 pl_overhead,
129 tl_material,
130 tl_material_overhead,
131 tl_resource,
132 tl_outside_processing,
133 tl_overhead,
134 material_cost,
135 material_overhead_cost,
136 resource_cost,
137 outside_processing_cost ,
138 overhead_cost,
139 pl_item_cost,
140 tl_item_cost,
141 item_cost,
142 unburdened_cost ,
143 burden_cost,
144 attribute_category,
145 attribute1,
146 attribute2,
147 attribute3,
148 attribute4,
149 attribute5,
150 attribute6,
151 attribute7,
152 attribute8,
153 attribute9,
154 attribute10,
155 attribute11,
156 attribute12,
157 attribute13,
158 attribute14,
159 attribute15
160 )
161 values
162 (
163 p_config_item_id, -- INVENTORY_ITEM_ID
164 p_organization_id,
165 p_buy_cost_type_id,
166 sysdate, -- last_update_date
167 -1, -- last_updated_by
168 sysdate, -- creation_date
169 -1, -- created_by
170 -1, -- last_update_login
171 1 , -- C.inventory_asset_flag,
172 1 , -- C.lot_size,
173 1 , -- C.based_on_rollup_flag,
174 0 , -- C.shrinkage_rate,
175 2 , -- C.defaulted_flag,
176 NULL, -- cost_update_id
177 0 , -- C.pl_material,
178 0 , -- C.pl_material_overhead,
179 0 , -- C.pl_resource,
180 0 , -- C.pl_outside_processing,
181 0 , -- C.pl_overhead,
182 p_buy_item_cost , -- C.tl_material,
183 0 , -- C.tl_material_overhead,
184 0 , -- C.tl_resource,
185 0 , -- C.tl_outside_processing,
186 0 , --C.tl_overhead,
187 p_buy_item_cost , -- C.material_cost,
188 0 , -- C.material_overhead_cost,
189 0, -- C.resource_cost,
190 0 , -- C.outside_processing_cost ,
191 0 , -- C.overhead_cost,
192 0 , -- C.pl_item_cost,
193 p_buy_item_cost , -- C.tl_item_cost,
194 p_buy_item_cost , -- C.item_cost,
195 0 , -- C.unburdened_cost ,
196 0 , -- C.burden_cost,
197 0 , -- C.attribute_category,
198 0 , -- C.attribute1,
199 0 , -- C.attribute2,
200 0 , -- C.attribute3,
201 0 , -- C.attribute4,
202 0 , -- C.attribute5,
203 0 , -- C.attribute6,
204 0 , -- C.attribute7,
205 0 , -- C.attribute8,
206 0 , -- C.attribute9,
207 0 , -- C.attribute10,
208 0 , -- C.attribute11,
209 0 , -- C.ATTRIBUTE12,
210 0 , -- C.attribute13,
211 0 , -- C.attribute14,
212 0 -- C.attribute15
213 );
214
215 IF PG_DEBUG <> 0 THEN
216 oe_debug_pub.add('populate_buy_cost: ' || 'after insert:CST_ITEM_COSTS',2);
217
218 oe_debug_pub.add('populate_buy_cost: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
219 END IF;
220
221 /*------ ----------------------------------------------+
222 Insert rows into the cst_item_cost_details table
223 +-----------------------------------------------------*/
224
225 lStmtNumber := 230;
226
227 insert into cst_item_cost_details
228 (inventory_item_id,
229 cost_type_id,
230 last_update_date,
231 last_updated_by,
232 creation_date,
233 created_by,
234 last_update_login,
235 organization_id,
236 operation_sequence_id,
237 operation_seq_num,
238 department_id,
239 level_type,
240 activity_id,
241 resource_seq_num,
242 resource_id,
243 resource_rate,
244 item_units,
245 activity_units,
246 usage_rate_or_amount,
247 basis_type,
248 basis_resource_id,
249 basis_factor,
250 net_yield_or_shrinkage_factor,
251 item_cost,
252 cost_element_id,
253 rollup_source_type,
254 activity_context,
255 attribute_category,
256 attribute1,
257 attribute2,
258 attribute3,
259 attribute4,
260 attribute5,
261 attribute6,
262 attribute7,
263 attribute8,
264 attribute9,
265 attribute10,
266 attribute11,
267 attribute12,
268 attribute13,
269 attribute14,
270 attribute15
271 )
272 select
273 p_config_item_id, -- inventory_item_id
274 p_buy_cost_type_id,
275 sysdate, -- last_update_date
276 -1, -- last_updated_by
277 sysdate, -- creation_date
278 -1, -- created_by
279 -1, -- last_update_login
280 p_organization_id,
281 null , -- c.operation_sequence_id,
282 null , -- c.operation_seq_num,
283 null , -- c.department_id,
284 1 , -- c.level_type,
285 null , -- c.activity_id,
286 null , -- c.resource_seq_num,
287 mp.default_material_cost_id, -- c.resource_id[material sub element],
288 1 , -- c.resource_rate,
289 null , -- c.item_units,
290 null , -- c.activity_units,
291 p_buy_item_cost , -- c.usage_rate_or_amount,
292 1 , -- c.basis_type,
293 null , -- c.basis_resource_id,
294 1 , -- c.basis_factor,
295 1 , -- c.net_yield_or_shrinkage_factor,
296 p_buy_item_cost, --item_cost
297 1 , -- c.cost_element_id,
298 1 , -- C.rollup_source_type,
299 null , -- C.activity_context,
300 null , -- C.attribute_category,
301 null , -- C.attribute1,
302 null , -- C.attribute2,
303 null , -- C.attribute3,
304 null , -- C.attribute4,
305 null , -- C.attribute5,
306 null , -- C.attribute6,
307 null , -- C.attribute7,
308 null , -- C.attribute8,
309 null , -- C.attribute9,
310 null , -- C.attribute10,
311 null , --C.attribute11,
312 null , -- C.attribute12,
313 null , -- C.attribute13,
314 null , -- C.attribute14,
315 null -- C.attribute15
316 from mtl_parameters mp
317 where mp.organization_id = p_organization_id ;
318
319 IF PG_DEBUG <> 0 THEN
320 oe_debug_pub.add('populate_buy_cost: ' || 'after insert:cst_item_cost_details',2);
321
322 oe_debug_pub.add('populate_buy_cost: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
323 END IF;
324
325
326 exception
327 when NO_DATA_FOUND THEN
328
329 IF PG_DEBUG <> 0 THEN
330 oe_debug_pub.add('populate_buy_cost: ' || 'populate_buy_cost no data found ',2);
331 END IF;
332
333 when OTHERS THEN
334
335 IF PG_DEBUG <> 0 THEN
336 oe_debug_pub.add('populate_buy_cost: ' || 'populate_buy_cost others '||sqlerrm,2);
337 END IF;
338
339
340
341
342
343 end populate_buy_cost;
344
345 -- FP J: populate_buy_cost_rollup is added since above API
346 -- populate_buy_cost can not be used as it requires line_id as IN param
347
348 procedure populate_buy_cost_rollup( p_config_item_id number
349 , p_organization_id number
350 , p_buy_cost_type_id number
351 , p_buy_item_cost number
352 , x_return_status OUT NOCOPY varchar2
353 )
354 is
355 lStmtNumber number;
356 begin
357
358 /*-------------------------------------------------------+
359 Insert a row into the cst_item_costs_table
360 +------------------------------------------------------- */
361
362 lStmtNumber := 220;
363 x_return_status := FND_API.G_RET_STS_SUCCESS;
364
365 insert into CST_ITEM_COSTS
366 (inventory_item_id,
367 organization_id,
368 cost_type_id,
369 last_update_date,
370 last_updated_by,
371 creation_date,
372 created_by,
373 last_update_login,
374 inventory_asset_flag,
375 lot_size,
376 based_on_rollup_flag,
377 shrinkage_rate,
378 defaulted_flag,
379 cost_update_id,
380 pl_material,
381 pl_material_overhead,
382 pl_resource,
383 pl_outside_processing,
384 pl_overhead,
385 tl_material,
386 tl_material_overhead,
387 tl_resource,
388 tl_outside_processing,
389 tl_overhead,
390 material_cost,
391 material_overhead_cost,
392 resource_cost,
393 outside_processing_cost ,
394 overhead_cost,
395 pl_item_cost,
396 tl_item_cost,
397 item_cost,
398 unburdened_cost ,
399 burden_cost,
400 attribute_category,
401 attribute1,
402 attribute2,
403 attribute3,
404 attribute4,
405 attribute5,
406 attribute6,
407 attribute7,
408 attribute8,
409 attribute9,
410 attribute10,
411 attribute11,
412 attribute12,
413 attribute13,
414 attribute14,
415 attribute15
416 )
417 values
418 (
419 p_config_item_id, -- INVENTORY_ITEM_ID
420 p_organization_id,
421 p_buy_cost_type_id,
422 sysdate, -- last_update_date
423 -1, -- last_updated_by
424 sysdate, -- creation_date
425 -1, -- created_by
426 -1, -- last_update_login
427 1 , -- C.inventory_asset_flag,
428 1 , -- C.lot_size,
429 1 , -- C.based_on_rollup_flag,
430 0 , -- C.shrinkage_rate,
431 2 , -- C.defaulted_flag,
432 NULL, -- cost_update_id
433 0 , -- C.pl_material,
434 0 , -- C.pl_material_overhead,
435 0 , -- C.pl_resource,
436 0 , -- C.pl_outside_processing,
437 0 , -- C.pl_overhead,
438 p_buy_item_cost , -- C.tl_material,
439 0 , -- C.tl_material_overhead,
440 0 , -- C.tl_resource,
441 0 , -- C.tl_outside_processing,
442 0 , --C.tl_overhead,
443 p_buy_item_cost , -- C.material_cost,
444 0 , -- C.material_overhead_cost,
445 0, -- C.resource_cost,
446 0 , -- C.outside_processing_cost ,
447 0 , -- C.overhead_cost,
448 0 , -- C.pl_item_cost,
449 p_buy_item_cost , -- C.tl_item_cost,
450 p_buy_item_cost , -- C.item_cost,
451 0 , -- C.unburdened_cost ,
452 0 , -- C.burden_cost,
453 0 , -- C.attribute_category,
454 0 , -- C.attribute1,
455 0 , -- C.attribute2,
456 0 , -- C.attribute3,
457 0 , -- C.attribute4,
458 0 , -- C.attribute5,
459 0 , -- C.attribute6,
460 0 , -- C.attribute7,
461 0 , -- C.attribute8,
462 0 , -- C.attribute9,
463 0 , -- C.attribute10,
464 0 , -- C.attribute11,
465 0 , -- C.ATTRIBUTE12,
466 0 , -- C.attribute13,
467 0 , -- C.attribute14,
468 0 -- C.attribute15
469 );
470
471 IF PG_DEBUG <> 0 THEN
472 oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:CST_ITEM_COSTS',2);
473
474 oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
475 END IF;
476
477 /*------ ----------------------------------------------+
478 Insert rows into the cst_item_cost_details table
479 +-----------------------------------------------------*/
480
481 lStmtNumber := 230;
482
483 insert into cst_item_cost_details
484 (inventory_item_id,
485 cost_type_id,
486 last_update_date,
487 last_updated_by,
488 creation_date,
489 created_by,
490 last_update_login,
491 organization_id,
492 operation_sequence_id,
493 operation_seq_num,
494 department_id,
495 level_type,
496 activity_id,
497 resource_seq_num,
498 resource_id,
499 resource_rate,
500 item_units,
501 activity_units,
502 usage_rate_or_amount,
503 basis_type,
504 basis_resource_id,
505 basis_factor,
506 net_yield_or_shrinkage_factor,
507 item_cost,
508 cost_element_id,
509 rollup_source_type,
510 activity_context,
511 attribute_category,
512 attribute1,
513 attribute2,
514 attribute3,
515 attribute4,
516 attribute5,
517 attribute6,
518 attribute7,
519 attribute8,
520 attribute9,
521 attribute10,
522 attribute11,
523 attribute12,
524 attribute13,
525 attribute14,
526 attribute15
527 )
528 select
529 p_config_item_id, -- inventory_item_id
530 p_buy_cost_type_id,
531 sysdate, -- last_update_date
532 -1, -- last_updated_by
533 sysdate, -- creation_date
534 -1, -- created_by
535 -1, -- last_update_login
536 p_organization_id,
537 null , -- c.operation_sequence_id,
538 null , -- c.operation_seq_num,
539 null , -- c.department_id,
540 1 , -- c.level_type,
541 null , -- c.activity_id,
542 null , -- c.resource_seq_num,
543 mp.default_material_cost_id, -- c.resource_id[material sub element],
544 1 , -- c.resource_rate,
545 null , -- c.item_units,
546 null , -- c.activity_units,
547 p_buy_item_cost , -- c.usage_rate_or_amount,
548 1 , -- c.basis_type,
549 null , -- c.basis_resource_id,
550 1 , -- c.basis_factor,
551 1 , -- c.net_yield_or_shrinkage_factor,
552 p_buy_item_cost, --item_cost
553 1 , -- c.cost_element_id,
554 1 , -- C.rollup_source_type,
555 null , -- C.activity_context,
556 null , -- C.attribute_category,
557 null , -- C.attribute1,
558 null , -- C.attribute2,
559 null , -- C.attribute3,
560 null , -- C.attribute4,
561 null , -- C.attribute5,
562 null , -- C.attribute6,
563 null , -- C.attribute7,
564 null , -- C.attribute8,
565 null , -- C.attribute9,
566 null , -- C.attribute10,
567 null , --C.attribute11,
568 null , -- C.attribute12,
569 null , -- C.attribute13,
570 null , -- C.attribute14,
571 null -- C.attribute15
572 from mtl_parameters mp
573 where mp.organization_id = p_organization_id ;
574
575 IF PG_DEBUG <> 0 THEN
576 oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:cst_item_cost_details',2);
577
578 oe_debug_pub.add('populate_buy_cost_rollup: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
579 END IF;
580
581
582 exception
583
584 when OTHERS THEN
585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
586 oe_debug_pub.add('populate_buy_cost_rollup: ' || 'populate_buy_cost others '||sqlerrm,2);
587
588
589 end populate_buy_cost_rollup;
590
591
592 procedure copy_ctocost_to_frozen(
593 p_config_item_id number
594 , p_organization_id number
595 , p_cto_cost_type_id number
596 )
597 is
598 lStmtNumber number;
599 l_cost_update number; --Bugfix 6363308
600 begin
601
602 /*-------------------------------------------------------+
603 Insert a row into the cst_item_costs_table
604 +------------------------------------------------------- */
605
606 lStmtNumber := 220;
607
608 --Bugfix 6363308
609 Select cst_lists_s.nextval
610 INTO l_cost_update
611 From DUAL;
612 --Bugfix 6363308
613
614 lStmtNumber := 230;
615
616 insert into CST_ITEM_COSTS
617 (inventory_item_id,
618 organization_id,
619 cost_type_id,
620 last_update_date,
621 last_updated_by,
622 creation_date,
623 created_by,
624 last_update_login,
625 inventory_asset_flag,
626 lot_size,
627 based_on_rollup_flag,
628 shrinkage_rate,
629 defaulted_flag,
630 cost_update_id,
631 pl_material,
632 pl_material_overhead,
633 pl_resource,
634 pl_outside_processing,
635 pl_overhead,
636 tl_material,
637 tl_material_overhead,
638 tl_resource,
639 tl_outside_processing,
640 tl_overhead,
641 material_cost,
642 material_overhead_cost,
643 resource_cost,
644 outside_processing_cost ,
645 overhead_cost,
646 pl_item_cost,
647 tl_item_cost,
648 item_cost,
649 unburdened_cost ,
650 burden_cost,
651 attribute_category,
652 attribute1,
653 attribute2,
654 attribute3,
655 attribute4,
656 attribute5,
657 attribute6,
658 attribute7,
659 attribute8,
660 attribute9,
661 attribute10,
662 attribute11,
663 attribute12,
664 attribute13,
665 attribute14,
666 attribute15
667 )
668 select distinct
669 p_config_item_id, -- INVENTORY_ITEM_ID
670 p_organization_id,
671 1,
672 sysdate, -- last_update_date
673 -1, -- last_updated_by
674 sysdate, -- creation_date
675 -1, -- created_by
676 -1, -- last_update_login
677 C.inventory_asset_flag,
678 C.lot_size,
679 C.based_on_rollup_flag,
680 C.shrinkage_rate,
681 C.defaulted_flag,
682 --p_cto_cost_type_id, -- cost_update_id
683 l_cost_update, --Bugfix 6363308 -- cost_update_id now taken from sequence
684 C.pl_material,
685 C.pl_material_overhead,
686 C.pl_resource,
687 C.pl_outside_processing,
688 C.pl_overhead,
689 C.tl_material,
690 C.tl_material_overhead,
691 C.tl_resource,
692 C.tl_outside_processing,
693 C.tl_overhead,
694 C.material_cost,
695 C.material_overhead_cost,
696 C.resource_cost,
697 C.outside_processing_cost ,
698 C.overhead_cost,
699 C.pl_item_cost,
700 C.tl_item_cost,
701 C.item_cost,
702 C.unburdened_cost ,
703 C.burden_cost,
704 C.attribute_category,
705 C.attribute1,
706 C.attribute2,
707 C.attribute3,
708 C.attribute4,
709 C.attribute5,
710 C.attribute6,
711 C.attribute7,
712 C.attribute8,
713 C.attribute9,
714 C.attribute10,
715 C.attribute11,
716 C.ATTRIBUTE12,
717 C.attribute13,
718 C.attribute14,
719 C.attribute15
720 from
721 cst_item_costs C
722 where C.inventory_item_id = p_config_item_id
723 and C.organization_id = p_organization_id
724 and C.cost_type_id = p_cto_cost_type_id;
725
726 IF PG_DEBUG <> 0 THEN
727 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:CST_ITEM_COSTS' || sql%rowcount ,2);
728 END IF;
729
730 /*------ ----------------------------------------------+
731 Insert rows into the cst_item_cost_details table
732 +-----------------------------------------------------*/
733
734 lStmtNumber := 230;
735
736 insert into cst_item_cost_details
737 (inventory_item_id,
738 cost_type_id,
739 last_update_date,
740 last_updated_by,
741 creation_date,
742 created_by,
743 last_update_login,
744 organization_id,
745 operation_sequence_id,
746 operation_seq_num,
747 department_id,
748 level_type,
749 activity_id,
750 resource_seq_num,
751 resource_id,
752 resource_rate,
753 item_units,
754 activity_units,
755 usage_rate_or_amount,
756 basis_type,
757 basis_resource_id,
758 basis_factor,
759 net_yield_or_shrinkage_factor,
760 item_cost,
761 cost_element_id,
762 rollup_source_type,
763 activity_context,
764 attribute_category,
765 attribute1,
766 attribute2,
767 attribute3,
768 attribute4,
769 attribute5,
770 attribute6,
771 attribute7,
772 attribute8,
773 attribute9,
774 attribute10,
775 attribute11,
776 attribute12,
777 attribute13,
778 attribute14,
779 attribute15
780 )
781 select distinct
782 p_config_item_id, -- inventory_item_id
783 1,
784 sysdate, -- last_update_date
785 -1, -- last_updated_by
786 sysdate, -- creation_date
787 -1, -- created_by
788 -1, -- last_update_login
789 p_organization_id,
790 c.operation_sequence_id,
791 c.operation_seq_num,
792 c.department_id,
793 c.level_type,
794 c.activity_id,
795 c.resource_seq_num,
796 c.resource_id,
797 c.resource_rate,
798 c.item_units,
799 c.activity_units,
800 c.usage_rate_or_amount,
801 c.basis_type,
802 c.basis_resource_id,
803 c.basis_factor,
804 c.net_yield_or_shrinkage_factor,
805 c.item_cost,
806 c.cost_element_id,
807 C.rollup_source_type,
808 C.activity_context,
809 C.attribute_category,
810 C.attribute1,
811 C.attribute2,
812 C.attribute3,
813 C.attribute4,
814 C.attribute5,
815 C.attribute6,
816 C.attribute7,
817 C.attribute8,
818 C.attribute9,
819 C.attribute10,
820 C.attribute11,
821 C.attribute12,
822 C.attribute13,
823 C.attribute14,
824 C.attribute15
825 from
826 cst_item_cost_details C
827 where C.inventory_item_id = p_config_item_id
828 and C.organization_id = p_organization_id
829 and C.cost_type_id = p_cto_cost_type_id ;
830
831 IF PG_DEBUG <> 0 THEN
832 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_item_cost_details' || sql%rowcount ,2);
833 END IF;
834
835 --Begin Bugfix 6363308
836 lStmtNumber := 250;
837
838 IF PG_DEBUG <> 0 THEN
839 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'Inserting records in csc and cec',2);
840 END IF;
841
842 INSERT INTO cst_standard_costs
843 (cost_update_id,
844 organization_id,
845 inventory_item_id,
846 last_update_date,
847 last_updated_by,
848 creation_date,
849 created_by,
850 last_update_login,
851 standard_cost_revision_date,
852 standard_cost
853 )
854 SELECT
855 l_cost_update,
856 p_organization_id,
857 p_config_item_id,
858 SYSDATE,
859 -1,
860 SYSDATE,
861 -1,
862 -1,
863 SYSDATE,
864 NVL(SUM(item_cost),0)
865 FROM
866 cst_item_cost_details
867 WHERE organization_id = p_organization_id
868 AND inventory_item_id = p_config_item_id
869 AND cost_type_id = 1;
870
871 IF PG_DEBUG <> 0 THEN
872 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_standard_costs ' || sql%rowcount ,2);
873 END IF;
874
875 lStmtNumber := 260;
876
877 INSERT INTO cst_elemental_costs
878 (cost_update_id,
879 organization_id,
880 inventory_item_id,
881 cost_element_id,
882 last_update_date,
883 last_updated_by,
884 creation_date,
885 created_by,
886 last_update_login,
887 standard_cost
888 )
889 SELECT
890 l_cost_update,
891 p_organization_id,
892 p_config_item_id,
893 cost_element_id,
894 SYSDATE,
895 -1,
896 SYSDATE,
897 -1,
898 -1,
899 NVL(SUM(item_cost),0)
900 FROM
901 cst_item_cost_details
902 WHERE organization_id = p_organization_id
903 AND inventory_item_id = p_config_item_id
904 AND cost_type_id = 1
905 GROUP BY cost_element_id;
906
907 IF PG_DEBUG <> 0 THEN
908 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'after insert:cst_elemental_costs ' || sql%rowcount ,2);
909 END IF;
910 --End Bugfix 6363308
911
912
913 exception
914 when NO_DATA_FOUND THEN
915 /*
916 xErrorMessage := 'CTOCSTRB:' || to_char(lStmtNum) || ':' ||
917 substrb(sqlerrm,1,150);
918 xMessageName := 'CTO_CALC_COST_ROLLUP_ERROR';
919 */
920
921 IF PG_DEBUG <> 0 THEN
922 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'copy_ctocost_to_frozen no data found ',2);
923 END IF;
924
925 when OTHERS THEN
926 /*
927 xErrorMessage := 'CTOCSTRB:' || to_char(lStmtNum) || ':' ||
928 substrb(sqlerrm,1,150);
929 --xMessageName := 'BOM_ATO_PROCESS_ERROR';
930 xMessageName := 'CTO_CALC_COST_ROLLUP_ERROR';
931 */
932
933 IF PG_DEBUG <> 0 THEN
934 oe_debug_pub.add('copy_ctocost_to_frozen: ' || 'copy_ctocost_to_frozen others ',2);
935 END IF;
936
937
938
939
940
941 end copy_ctocost_to_frozen;
942
943
944
945 FUNCTION check_ct_updateable( p_inventory_item_id NUMBER
946 , p_organization_id NUMBER
947 , p_cost_type_id NUMBER )
948 Return BOOLEAN IS
949 Updateable VARCHAR2(10) := null ;
950 RetVal BOOLEAN;
951 intransit_count NUMBER;
952
953 Cursor Check_Updateable is
954 Select 'NO'
955 From MTL_MATERIAL_TRANSACTIONS t
956 Where Inventory_Item_Id = p_inventory_item_id
957 And Exists
958 (Select 'all these org have the org as costing org'
959 From MTL_PARAMETERS
960 Where Cost_Organization_Id = p_organization_id
961 AND Organization_Id = t.Organization_Id);
962
963 Cursor Check_Updateable_2 is
964 Select 'NO'
965 From MTL_MATERIAL_TRANSACTIONS_TEMP t
966 Where Inventory_Item_Id = p_inventory_item_id
967 And Exists
968 (Select 'all these org have the org as costing org'
969 From MTL_PARAMETERS
970 Where Cost_Organization_Id = p_organization_id
971 AND Organization_Id = t.Organization_Id);
972
973 BEGIN
974 -- If we are dealing with a frozon cost type, it is only updateable when
975 -- there does not exist any transactions.
976
977 IF ( p_cost_type_id = 1) THEN
978 IF (Updateable is NULL) THEN
979 Open Check_Updateable;
980 Fetch Check_Updateable into Updateable;
981 Close Check_Updateable;
982
983 IF (Updateable is Null) THEN
984
985 Open Check_Updateable_2;
986 Fetch Check_Updateable_2 into Updateable;
987 Close Check_Updateable_2;
988 END IF;
989
990 IF (Updateable is NULL) THEN
991
992 select count(*)
993 into intransit_count
994 from mtl_supply m
995 where m.item_id = p_inventory_item_id
996 and m.intransit_owning_org_id = p_organization_id
997 and m.to_organization_id = p_organization_id ;
998 IF (intransit_count > 0) THEN
999 Updateable := 'NO';
1000 END IF;
1001 END IF;
1002
1003 END IF;
1004 IF (Updateable = 'NO') THEN
1005 -- fnd_message.Set_Name('BOM', 'CST_ITEM_USED_IN_TXN');
1006 RetVal := FALSE;
1007 ELSE
1008 IF PG_DEBUG <> 0 THEN
1009 oe_debug_pub.add( ' check_ct_updateable is null -> true ' ) ;
1010 END IF;
1011
1012 RetVal := TRUE;
1013 END IF;
1014
1015 ELSE
1016 IF PG_DEBUG <> 0 THEN
1017 oe_debug_pub.add( ' cost type id not 1 ' ) ;
1018 END IF;
1019
1020 RetVal := TRUE;
1021 END IF;
1022
1023 IF PG_DEBUG <> 0 THEN
1024
1025 if( RetVal = TRUE ) then
1026 oe_debug_pub.add( ' check_ct_updateable is true ' ) ;
1027 elsif( RetVal = False ) then
1028 oe_debug_pub.add( ' check_ct_updateable is false' ) ;
1029 elsif( RetVal is null ) then
1030 oe_debug_pub.add( ' check_ct_updateable is null ' ) ;
1031 end if ;
1032 END IF;
1033
1034
1035
1036 Return RetVal;
1037 END Check_CT_Updateable;
1038
1039
1040 /* fp-J : This procedure has been added for optional processing for cost rollup (ksarkar)
1041 */
1042
1043 FUNCTION Cost_Roll_Up_ML( p_cfg_itm_tbl in CTO_COST_ROLLUP_CONC_PK.t_cfg_item
1044 , x_msg_count out NOCOPY number
1045 , x_msg_data out NOCOPY varchar2
1046 )
1047 RETURN integer
1048 IS
1049
1050 lMrpAssignmentSet number;
1051 lRollupId number;
1052 gUserId number;
1053 gLoginId number;
1054 lMsgCount number;
1055 lMsgData varchar2(2000);
1056 lStmtNumber number;
1057 lErrorCode number;
1058 lErrorMsg varchar2(2000);
1059 lCnt number;
1060 lConversionType varchar2(30);
1061 lBuyCostType varchar2(30);
1062
1063 CTO_MRP_ASSIGNMENT_SET exception;
1064
1065 v_cto_cost_type_id cst_cost_types.cost_type_id%type ;
1066 v_buy_cost_type_id cst_cost_types.cost_type_id%type ;
1067 v_buy_cost number ;
1068 c_line_id bom_cto_src_orgs.line_id%type ;
1069 c_model_item_id bom_cto_src_orgs.model_item_id%type ;
1070 c_config_item_id bom_cto_src_orgs.model_item_id%type ;
1071 c_match_config_item_id bom_cto_src_orgs.model_item_id%type ;
1072 c_org_id bom_cto_src_orgs.rcv_org_id%type ;
1073 c_organization_id bom_cto_src_orgs.organization_id%type ;
1074 c_po_valid_org financials_system_params_all.inventory_organization_id%type ;
1075 c_oper_unit inv_organization_info_v.operating_unit%type ;
1076
1077 v_buy_item_cost mtl_system_items.list_price_per_unit%type ;
1078
1079 -- rkaza. 3742393. 08/12/2004.
1080 -- Repalcing org_organization_definitions with inv_organization_info_v
1081
1082
1083 -- For Performance reason, We are removing the join with financial_system_params_all table
1084 -- For some reason, this join is causing non mergable view or inv_organization_info_v.
1085 -- We don't know the exact reason yet. But for now, we are planning to break the sql into two parts
1086 -- to avoid this performance issue.
1087 cursor c_buy_items( xcfg_itm_id number,
1088 xcfg_org_id number
1089 )
1090 is
1091 select
1092 msi.base_item_id
1093 , msi.inventory_item_id
1094 , mp1.cost_organization_id -- 3116778
1095 , nvl(org.operating_unit,0) oper_unit
1096 from inv_organization_info_v org
1097 , mtl_system_items msi
1098 , cst_item_costs cic
1099 , mtl_parameters mp1
1100 where org.organization_id = msi.organization_id
1101 and cic.inventory_item_id = msi.inventory_item_id
1102 and cic.organization_id = mp1.cost_organization_id --3116778
1103 and cic.based_on_rollup_flag = 1
1104 and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id)
1105 and msi.organization_id = MP1.organization_id
1106 and msi.inventory_item_id = xcfg_itm_id
1107 and msi.organization_id = xcfg_org_id;
1108
1109
1110 /*
1111 This cursor does not use bcol table to check for matched items
1112 as cost rollup may be called for items that do not have an entry in bcol.
1113 Instead we will check whether the item has a valuation cost with a rolled up
1114 component.
1115 */
1116
1117 cursor c_matched_items_cost_synchup ( xcfg_itm_id number,
1118 xcfg_org_id number
1119 )
1120 is
1121 select msi.inventory_item_id
1122 , msi.organization_id
1123 , cic.cost_type_id
1124 , cic.item_cost
1125 from mtl_system_items msi
1126 , cst_item_costs cic
1127 , mtl_parameters mp
1128 where msi.organization_id = cic.organization_id
1129 and msi.inventory_item_id = cic.inventory_item_id
1130 and msi.organization_id = mp.organization_id
1131 and mp.primary_cost_method = cic.cost_type_id
1132 and EXISTS /* check whether item has been rolled up */
1133 ( select NULL
1134 from cst_item_cost_details
1135 where rollup_source_type = 3
1136 and inventory_item_id = msi.inventory_item_id
1137 and cost_type_id = cic.cost_type_id
1138 and organization_id = msi.organization_id
1139 )
1140 and msi.inventory_item_id = xcfg_itm_id
1141 and msi.organization_id = xcfg_org_id;
1142
1143 cursor c_frozen_cost_update( c_cto_cost_type_id number,
1144 xcfg_itm_id number,
1145 xcfg_org_id number
1146 )
1147 is
1148 select msi.inventory_item_id
1149 , mp.cost_organization_id rollup_org_id
1150 from mtl_system_items msi
1151 , mtl_parameters mp
1152 , cst_item_costs cic
1153 where msi.organization_id = mp.organization_id
1154 and mp.primary_cost_method = 1
1155 and cic.inventory_item_id = msi.inventory_item_id
1156 and cic.organization_id = msi.organization_id
1157 and cic.cost_type_id = c_cto_cost_type_id
1158 and msi.inventory_item_id = xcfg_itm_id
1159 and msi.organization_id = xcfg_org_id;
1160
1161
1162 v_item_cost cst_item_costs.item_cost%type ;
1163 v_cto_cost cst_item_costs.item_cost%type ;
1164 v_material_cost cst_item_costs.material_cost%type ;
1165 v_material_overhead_cost cst_item_costs.material_overhead_cost%type ;
1166 v_resource_cost cst_item_costs.resource_cost%type ;
1167 v_outside_processing_cost cst_item_costs.outside_processing_cost%type ;
1168 v_overhead_cost cst_item_costs.overhead_cost%type ;
1169
1170 v_cost_updateable BOOLEAN := false ;
1171
1172
1173 v_group_id bom_explosion_temp.group_id%type ;
1174 x_return_status varchar2(200) ;
1175
1176
1177 c_primary_cost_method mtl_parameters.primary_cost_method%type ;
1178 c_valuation_cost number;
1179
1180
1181 l_token CTO_MSG_PUB.token_tbl;
1182
1183
1184
1185 v_po_validation_org varchar2(2000) ;
1186 v_org varchar2(2000) ;
1187 v_model_name varchar2(2000) ;
1188
1189
1190 v_cto_cost_type_name cst_cost_types.cost_type%type;
1191
1192 l_cost_data_exists Varchar2(1) := 'N'; -- Bug Fix 4867460
1193 l_cost_update_required varchar2(1) := 'N'; --Bugfix 6717614
1194
1195 BEGIN
1196 lStmtNumber := 10;
1197
1198
1199 if p_cfg_itm_tbl.COUNT = 0 then
1200 oe_debug_pub.add ('Cost_Roll_Up_ML: '|| 'Array p_cfg_itm_tbl does not contain any rows to process.');
1201 return(1);
1202 else
1203 oe_debug_pub.add ('Cost_Roll_Up_ML: '|| 'Going to process '||p_cfg_itm_tbl.COUNT|| ' rows... ');
1204
1205 end if;
1206
1207 gUserId := nvl(fnd_global.user_id, -1);
1208 gLoginId := nvl(fnd_global.login_id, -1);
1209
1210 IF PG_DEBUG <> 0 THEN
1211 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'gUserId::'||to_char(gUserId)
1212 ||'::gLoginId::'||to_char(gLoginId), 2);
1213
1214 END IF;
1215
1216 -- changed sequence as per bugfix 3239456
1217 /*select bom_lists_s.nextval
1218 into lRollupId from dual;*/
1219
1220 select cst_lists_s.nextval
1221 into lRollupId from dual;
1222
1223
1224
1225 IF PG_DEBUG <> 0 THEN
1226 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'lRollupId::'||to_char(lRollupId), 2);
1227 END IF;
1228
1229 lStmtNumber := 20;
1230 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
1231
1232 IF lMrpAssignmentSet is null THEN
1233 IF PG_DEBUG <> 0 THEN
1234 oe_debug_pub.add('Cost_Roll_Up_ML '
1235 || 'Default assignment set is null', 1);
1236 END IF;
1237
1238
1239 --FND_MESSAGE.set_name('BOM','CTO_MRP_ASSIGNMENT_SET');
1240 --raise CTO_MRP_ASSIGNMENT_SET;
1241 ELSE
1242 IF PG_DEBUG <> 0 THEN
1243 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Default assignment set is '
1244 ||to_char(lMrpAssignmentSet), 2);
1245 END IF;
1246 END IF;
1247
1248 --
1249 -- Getting the Currency Conversion Type from profile
1250 -- "INV:Inter-organization currency conversion"
1251 --
1252 lStmtNumber := 25;
1253 lConversionType := FND_PROFILE.VALUE('CURRENCY_CONVERSION_TYPE');
1254
1255
1256 IF PG_DEBUG <> 0 THEN
1257 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Currency Conversion Type ::'
1258 ||lConversionType, 2);
1259 END IF;
1260
1261
1262
1263 /* bug 4177423
1264 statement to retrieve CTO cost type id has been removed out from the loop.
1265 This will execute the statement only once.
1266 */
1267
1268
1269 IF PG_DEBUG <> 0 THEN
1270 oe_debug_pub.add ('Fetching CTO cost_type_id..');
1271 END IF;
1272
1273
1274
1275 /* commented for bug 4057651
1276 begin
1277
1278 select cost_type_id into v_cto_cost_type_id
1279 from cst_cost_types
1280 where cost_type = 'CTO' ;
1281
1282
1283
1284 exception
1285 when no_data_found then
1286 -- rollback to CTOCST; bug 4177423
1287 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
1288 -- goto endloop; bug 4177423
1289 raise FND_API.G_EXC_ERROR;
1290
1291 when others then
1292 oe_debug_pub.add('Unexpected error while getting the cost_type_id: ' ||sqlerrm);
1293 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1294
1295 end;
1296
1297
1298 commented for bug 4057651 */
1299
1300
1301
1302 /* begin bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1303 begin
1304
1305 select cost_type_id into v_cto_cost_type_id
1306 from cst_cost_types
1307 where cost_type = 'CTO' ;
1308
1309 exception
1310 when no_data_found then
1311
1312 IF PG_DEBUG <> 0 THEN
1313 oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
1314 oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
1315 END IF;
1316
1317 v_cto_cost_type_id := 7 ;
1318
1319 begin
1320 select cost_type into v_cto_cost_type_name
1321 from cst_cost_types
1322 where cost_type_id = v_cto_cost_type_id ;
1323
1324 IF PG_DEBUG <> 0 THEN
1325 oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || v_cto_cost_type_id ||
1326 ' has cost_type = ' || v_cto_cost_type_name ,2);
1327 END IF;
1328 exception
1329 when no_data_found then
1330 IF PG_DEBUG <> 0 THEN
1331 oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
1332 END IF;
1333 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
1334 raise FND_API.G_EXC_ERROR;
1335 when others then
1336
1337 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1338 end ;
1339
1340 when others then
1341 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1342 end ;
1343 /* end bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1344
1345
1346
1347 /* bug 4177423
1348 CTO BUY cost type id will be defaulted to CTO cost type if it is null.
1349 */
1350
1351 lStmtNumber := 27;
1352 lBuyCostType := FND_PROFILE.VALUE('CTO_BUY_COST_TYPE');
1353 IF PG_DEBUG <> 0 THEN
1354 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Buy Cost Type ::'|| lBuyCostType , 2);
1355 END IF;
1356
1357
1358 lStmtNumber := 28;
1359
1360 if( lBuyCostType is not null ) then
1361 begin
1362 select cost_type_id into v_buy_cost_type_id
1363 from cst_cost_types
1364 where cost_type = lBuyCostType ;
1365
1366 IF PG_DEBUG <> 0 THEN
1367 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Buy Cost Type id ::'
1368 || v_buy_cost_type_id , 2);
1369 END IF;
1370
1371 exception
1372 when no_data_found then
1373
1374 cto_msg_pub.cto_message('BOM','CTO_BUY_COST_NOT_FOUND');
1375 raise FND_API.G_EXC_ERROR;
1376
1377 when others then
1378
1379 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1380
1381 end;
1382
1383 else /* fix for bug 4177423 */
1384
1385 IF PG_DEBUG <> 0 THEN
1386 oe_debug_pub.add('Cost_Roll_Up_ML ' || ' Defaulting Buy Cost Type to CTO cost id ' || v_cto_cost_type_id , 2);
1387 END IF;
1388
1389 v_buy_cost_type_id := v_cto_cost_type_id ;
1390
1391 end if ;
1392
1393
1394 lStmtNumber := 29;
1395
1396 IF PG_DEBUG <> 0 THEN
1397 oe_debug_pub.add('Cost_Roll_Up_ML ' || ' opening buy cost cursor for bcso ' , 2);
1398 END IF;
1399
1400
1401
1402 for i in p_cfg_itm_tbl.FIRST..p_cfg_itm_tbl.LAST
1403 LOOP -- to loop thru all rows in p_cfg_itm_tbl
1404
1405 SAVEPOINT CTOCST;
1406
1407 -- Instead of doing a simple select, we are doing a cursor fetch
1408 -- to ensure that it doesnt fail with too_many_rows.
1409
1410
1411 /* bug 4341197 do not populate list price in buy cost type if the buy cost type profile is not set by the user */
1412 if( lBuyCostType is not null ) then
1413
1414
1415
1416
1417
1418 oe_debug_pub.add ('---------------------------------------------------------------------------');
1419 oe_debug_pub.add ('Processing config_item_id '|| p_cfg_itm_tbl(i).cfg_item_id ||
1420 ' in orgn '||p_cfg_itm_tbl(i).cfg_org_id);
1421 oe_debug_pub.add ('---------------------------------------------------------------------------');
1422
1423 open c_buy_items( p_cfg_itm_tbl(i).cfg_item_id,
1424 p_cfg_itm_tbl(i).cfg_org_id) ;
1425
1426 fetch c_buy_items into c_model_item_id
1427 , c_config_item_id
1428 , c_org_id
1429 , c_oper_unit;
1430
1431
1432 close c_buy_items ;
1433
1434 -- Added by Renga Kannan on 03/15/20067 to get the po validation org
1435
1436 Select nvl(inventory_organization_id,0)
1437 into c_po_valid_org
1438 from financials_system_params_All
1439 where org_id = c_oper_unit;
1440
1441
1442 IF PG_DEBUG <> 0 THEN
1443 oe_debug_pub.add('Cost_Roll_Up_ML ' ||
1444 ' model ' || c_model_item_id ||
1445 ' config ' || c_config_item_id ||
1446 ' org ' || c_org_id ||
1447 ' po val ' || c_po_valid_org ||
1448 ' oper ' || c_oper_unit, 2);
1449 END IF;
1450
1451 lStmtNumber := 30;
1452
1453
1454 /* Check whether buy cost exists for the item */
1455 begin
1456 select item_cost into v_buy_cost
1457 from cst_item_costs
1458 where cost_type_id = v_buy_cost_type_id
1459 and organization_id = c_org_id
1460 and inventory_item_id = c_config_item_id ;
1461
1462 exception
1463 when no_data_found then
1464 v_buy_cost := null ;
1465
1466 IF PG_DEBUG <> 0 THEN
1467 oe_debug_pub.add('no buy cost exists for item ' || c_config_item_id
1468 || ' in org ' || c_org_id , 2);
1469 END IF;
1470
1471
1472 when others then
1473 oe_debug_pub.add('Unexpected error while checking buy cost for item_id ' ||c_config_item_id|| ' in orgn '||c_org_id);
1474 oe_debug_pub.add(sqlerrm);
1475 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1476
1477 end ;
1478
1479
1480
1481 if( v_buy_cost is null ) then
1482
1483 lStmtNumber := 32;
1484 BEGIN
1485 select nvl(list_price_per_unit,0) into v_buy_item_cost
1486 from mtl_system_items
1487 where organization_id = c_po_valid_org
1488 and inventory_item_id = c_config_item_id ;
1489
1490 EXCEPTION
1491 when no_data_found then
1492
1493
1494 IF PG_DEBUG <> 0 THEN
1495 oe_debug_pub.add('create_config_item_wf: ' || 'Item Not Enabled in PO Validation Org ' || c_po_valid_org , 1);
1496 oe_debug_pub.add('create_config_item_wf: ' || 'PO Validation Org for Org ' || c_org_id , 1);
1497 END IF;
1498
1499
1500 -- cto_msg_pub.cto_message('BOM', l_xmsgdata);
1501
1502 l_token(1).token_name := 'POV_ORG';
1503
1504 -- rkaza. 3742393. 08/12/2004.
1505 -- Repalcing org_organization_definitions with
1506 -- inv_organization_name_v
1507
1508 select organization_name into v_po_validation_org
1509 from inv_organization_name_v
1510 where organization_id = c_po_valid_org ;
1511
1512
1513 l_token(1).token_value := v_po_validation_org ;
1514
1515
1516 l_token(2).token_name := 'ORG';
1517
1518 select organization_name into v_org
1519 from inv_organization_name_v
1520 where organization_id = c_org_id ;
1521
1522
1523 l_token(2).token_value := v_org ;
1524
1525
1526
1527 l_token(3).token_name := 'MODEL_NAME';
1528
1529 select concatenated_segments into v_model_name
1530 from mtl_system_items_kfv
1531 where inventory_item_id = ( select base_item_id
1532 from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
1533 and rownum = 1 ;
1534
1535 l_token(3).token_value := v_model_name ;
1536
1537
1538 cto_msg_pub.cto_message('BOM', 'CTO_ITEM_NOT_ENABLED_IN_POV', l_token );
1539
1540 raise FND_API.G_EXC_ERROR;
1541
1542
1543
1544
1545 when others then
1546
1547
1548 raise ;
1549
1550 END ;
1551
1552
1553
1554 lStmtNumber := 35;
1555
1556 IF PG_DEBUG <> 0 THEN
1557 oe_debug_pub.add('going to populate buy cost for item ' || c_config_item_id
1558 || ' for $ ' || v_buy_item_cost , 2);
1559 END IF;
1560
1561 populate_buy_cost_rollup( c_config_item_id
1562 , c_org_id
1563 , v_buy_cost_type_id
1564 , v_buy_item_cost
1565 , x_return_status) ;
1566
1567 if x_return_status = FND_API.G_RET_STS_ERROR then
1568 ROLLBACK to CTOCST;
1569 oe_debug_pub.add ('Expected error in populate_buy_cost_rollup.');
1570 goto endloop;
1571
1572 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1573 oe_debug_pub.add ('UnExpected error in populate_buy_cost_rollup.');
1574 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1575 end if;
1576
1577
1578 else
1579
1580 IF PG_DEBUG <> 0 THEN
1581 oe_debug_pub.add('buy cost exists for item ' || c_config_item_id
1582 || ' for $ ' || v_buy_cost , 2);
1583 END IF;
1584
1585 end if ;
1586
1587
1588
1589
1590
1591 end if; /* check if buy cost is not null */
1592 /* bug 4341197 do not populate list price in buy cost type if the buy cost type profile is not set by the user */
1593
1594
1595
1596
1597 lStmtNumber := 37;
1598 IF PG_DEBUG <> 0 THEN
1599 oe_debug_pub.add ('Fetching c_matched_items_cost_synchup cursor ..');
1600 END IF;
1601
1602 /* copy valuation cost to cto cost for matched items */
1603
1604 open c_matched_items_cost_synchup ( p_cfg_itm_tbl(i).cfg_item_id,
1605 p_cfg_itm_tbl(i).cfg_org_id) ;
1606
1607 fetch c_matched_items_cost_synchup into c_match_config_item_id
1608 , c_organization_id
1609 , c_primary_cost_method
1610 , c_valuation_cost ;
1611
1612 close c_matched_items_cost_synchup;
1613
1614 IF PG_DEBUG <> 0 THEN
1615 oe_debug_pub.add ('c_match_config_item_id = '|| c_match_config_item_id || ';' ||
1616 'c_organization_id = '|| c_organization_id || ';' ||
1617 'c_primary_cost_method = '|| c_primary_cost_method || ';' ||
1618 'c_valuation_cost = '|| c_valuation_cost );
1619 END IF;
1620
1621 lStmtNumber := 38;
1622 begin
1623
1624 select item_cost into v_cto_cost from cst_item_costs
1625 where inventory_item_id = c_match_config_item_id
1626 and organization_id = c_organization_id
1627 and cost_type_id = v_cto_cost_type_id ;
1628
1629
1630 exception
1631 when others then
1632 v_cto_cost := null ;
1633
1634 end;
1635
1636 IF PG_DEBUG <> 0 THEN
1637 oe_debug_pub.add ('v_cto_cost = '|| v_cto_cost );
1638 END IF;
1639
1640 lStmtNumber := 39;
1641 if( c_valuation_cost <> v_cto_cost or v_cto_cost is null ) then
1642
1643 CTO_UTILITY_PK.copy_cost( c_primary_cost_method
1644 , v_cto_cost_type_id
1645 , c_match_config_item_id
1646 , c_organization_id
1647 ) ;
1648
1649 end if;
1650
1651
1652 IF PG_DEBUG <> 0 THEN
1653 oe_debug_pub.add('calling costing API', 2);
1654 oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet || '; cto cost id ' || v_cto_cost_type_id ||
1655 ' buy cost id ' || v_buy_cost_type_id , 2);
1656
1657 END IF;
1658
1659
1660
1661 /* can't do bulk insert into cst_sc_lists for PLS-00436 */
1662 /* insert item by item */
1663
1664 lStmtNumber := 39.1;
1665
1666
1667 insert into cst_sc_lists(
1668 rollup_id,
1669 inventory_item_id,
1670 organization_id,
1671 last_update_date,
1672 last_updated_by,
1673 creation_date,
1674 created_by
1675 )
1676 select distinct
1677 lRollupId,
1678 p_cfg_itm_tbl(i).cfg_item_id,
1679 mp.cost_organization_id, --3116778
1680 sysdate,
1681 gUserId,
1682 sysdate,
1683 gUserId
1684 from mtl_system_items msi,
1685 mtl_parameters mp ,
1686 cst_item_costs cic
1687 where msi.costing_enabled_flag = 'Y'
1688 and mp.organization_id = msi.organization_id
1689 and cic.inventory_item_id = msi.base_item_id
1690 and cic.organization_id = mp.cost_organization_id
1691 and cic.based_on_rollup_flag = 1
1692 and mp.primary_cost_method = cic.cost_type_id
1693 and cic.cost_type_id in ( 1, 2, 5, 6 )
1694 and msi.inventory_item_id = p_cfg_itm_tbl(i).cfg_item_id
1695 and msi.organization_id = p_cfg_itm_tbl(i).cfg_org_id
1696 and NOT EXISTS
1697 (select NULL
1698 from cst_sc_lists
1699 where rollup_id = lRollupId
1700 and inventory_item_id = msi.inventory_item_id
1701 and organization_id = mp.cost_organization_id) --3116778
1702 and NOT EXISTS /* check whether item has been rolled up */
1703 ( select NULL
1704 from cst_item_cost_details
1705 where rollup_source_type = 3
1706 and inventory_item_id = msi.inventory_item_id
1707 and cost_type_id = cic.cost_type_id
1708 and organization_id = mp.cost_organization_id) ; --3116778
1709
1710 lCnt := sql%rowcount;
1711 -- Bug Fix for 4867460
1712 If lCnt > 0 Then
1713 l_cost_data_exists := 'Y';
1714 End if;
1715 IF PG_DEBUG <> 0 THEN
1716 oe_debug_pub.add('populate_buy_cost: ' || 'rows inserted into bom_lists::'||to_char(lCnt), 2);
1717 END IF;
1718
1719 << endloop>>
1720 null;
1721
1722 END LOOP; /* end of array loop */
1723
1724
1725 -- commented if condition
1726
1727
1728
1729
1730
1731 --
1732
1733
1734 -- bug 4177423 no need of end if as if stmt has been commented end if; /* end of p_cfg_itm_tbl.COUNT */
1735
1736 --
1737 -- call SC Cost Rollup API
1738 --
1739 lStmtNumber := 60;
1740 -- Bug Fix 4867460
1741 If l_cost_data_exists = 'Y' Then
1742
1743 If PG_DEBUG <> 0 Then
1744 oe_debug_pub.add('Populate_buy_cost: Calling Supply Chain Rollup program',5);
1745 End if;
1746
1747 /* Commented as part of Bugfix 7246036
1748 CSTPSCEX.Supply_Chain_Rollup(
1749 lRollupId, -- i_rollup_id
1750 1, -- i_explosion_level
1751 NULL, -- i_report_levels
1752 lMrpAssignmentSet, -- i_assignment_set_id
1753 lConversionType, -- i_conversion_type
1754 -- 1, -- i_cost_type_id
1755 v_cto_cost_type_id , -- i_cost_type_id
1756 v_buy_cost_type_id, -- i_buy_cost_type_id
1757 SYSDATE, -- i_effective_date
1758 1, -- exclude unimplemented ECOs, implemented only
1759 1, -- BOM items only, exclude ENG items
1760 '', -- i_alt_bom_desg
1761 '', -- i_alt_rtg_desg
1762 2, -- i_lock_flag
1763 gUserId, -- i_user_id
1764 gLoginId, -- i_login_id
1765 NULL, -- i_request_id
1766 NULL, -- i_prog_id??
1767 702, -- i_prog_appl_id
1768 lErrorCode, -- o_error_code
1769 lErrorMsg); -- o_error_msg
1770 */
1771
1772 CSTPSCEX.Supply_Chain_Rollup(
1773 i_rollup_id => lRollupId,
1774 i_explosion_levels => 1,
1775 i_report_levels => NULL,
1776 i_assignment_set_id => lMrpAssignmentSet,
1777 i_conversion_type => lConversionType,
1778 i_cost_type_id => v_cto_cost_type_id,
1779 i_buy_cost_type_id => v_buy_cost_type_id,
1780 i_effective_date => SYSDATE,
1781 i_exclude_unimpl_eco => 1,
1782 i_exclude_eng => 1,
1783 i_alt_bom_desg => '',
1784 i_alt_rtg_desg => '',
1785 i_lock_flag => 2,
1786 i_user_id => gUserId,
1787 i_login_id => gLoginId,
1788 i_request_id => NULL,
1789 i_prog_id => NULL,
1790 i_prog_appl_id => 702,
1791 o_error_code => lErrorCode,
1792 o_error_msg => lErrorMsg,
1793 i_buy_cost_detail => 1 ); --Bugfix 7246036: Passing preserve buy cost details parameter as Yes.
1794
1795 IF lErrorCode <> 0 THEN
1796 IF PG_DEBUG <> 0 THEN
1797 oe_debug_pub.add('populate_buy_cost: ' || 'SC Rollup API returned with error::'||lErrorMsg, 1);
1798 END IF;
1799 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1800 ELSE
1801 IF PG_DEBUG <> 0 THEN
1802 oe_debug_pub.add('populate_buy_cost: ' || 'SC Rollup API returned with success', 1);
1803 END IF;
1804
1805
1806 END IF;
1807
1808 Else
1809
1810 If PG_DEBUG <> 0 Then
1811 oe_debug_pub.add('Populate_buy_cost: No cost data exists. No need to call Supply chain Cost rollup API',5);
1812 End if;
1813
1814 End if;
1815 /*
1816 **
1817 ** Costs need to be copied from simulation cost to frozen cost in case of
1818 ** standard costing organization
1819 **
1820 */
1821
1822
1823
1824
1825 lStmtNumber := 80;
1826 /* update Frozen cost with CTO Cost in case of standard costing organizations */
1827
1828
1829 for i in p_cfg_itm_tbl.FIRST..p_cfg_itm_tbl.LAST
1830
1831 LOOP
1832
1833 open c_frozen_cost_update ( v_cto_cost_type_id,
1834 p_cfg_itm_tbl(i).cfg_item_id,
1835 p_cfg_itm_tbl(i).cfg_org_id) ;
1836
1837 fetch c_frozen_cost_update into c_config_item_id
1838 , c_organization_id ;
1839
1840
1841 --exit when c_frozen_cost_update%notfound ; Bugfix 6038548
1842 IF c_frozen_cost_update%found THEN --Bugfix 6038548
1843
1844 lStmtNumber := 95;
1845 --Bugfix 6717614
1846 l_cost_update_required := cost_update_required(c_config_item_id,
1847 c_organization_id,
1848 v_cto_cost_type_id);
1849
1850 IF (l_cost_update_required = 'Y') THEN
1851
1852 IF PG_DEBUG <> 0 THEN
1853 oe_debug_pub.add('***************** going to do frozen cost update for config ' || c_config_item_id
1854 || ' in org ' || c_organization_id , 1);
1855 END IF;
1856
1857
1858 delete from cst_item_cost_details
1859 where inventory_item_id = c_config_item_id
1860 and organization_id = c_organization_id
1861 and cost_type_id = 1 ;
1862
1863 IF PG_DEBUG <> 0 THEN
1864 oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
1865 END IF;
1866
1867
1868 delete from cst_item_costs
1869 where inventory_item_id = c_config_item_id
1870 and organization_id = c_organization_id
1871 and cost_type_id = 1 ;
1872
1873
1874 IF PG_DEBUG <> 0 THEN
1875 oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
1876 END IF;
1877
1878
1879 copy_ctocost_to_frozen ( c_config_item_id
1880 , c_organization_id
1881 , v_cto_cost_type_id ) ;
1882
1883 END IF;
1884 END IF; --c_frozen_cost_update%found. Bugfix 6038548
1885
1886 close c_frozen_cost_update ;
1887
1888
1889 END LOOP;
1890
1891
1892
1893
1894
1895
1896 IF PG_DEBUG <> 0 THEN
1897 oe_debug_pub.add('populate_buy_cost: ' || 'At end of cost rollup', 2);
1898 END IF;
1899 return(1);
1900
1901 EXCEPTION
1902 WHEN NO_DATA_FOUND THEN
1903 oe_debug_pub.add ('populate_buy_cost: ' || 'cost_rollup::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
1904 return(0);
1905
1906 WHEN CTO_MRP_ASSIGNMENT_SET THEN
1907 oe_debug_pub.add ('populate_buy_cost: ' || 'cost_rollup::mrp assgn set is null'||to_char(lStmtNumber)||sqlerrm, 1);
1908
1909 cto_msg_pub.count_and_get
1910 ( p_msg_count => x_msg_count
1911 , p_msg_data => x_msg_data
1912 );
1913
1914 return(0);
1915
1916
1917 WHEN FND_API.G_EXC_ERROR THEN
1918 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
1919
1920 cto_msg_pub.count_and_get
1921 ( p_msg_count => x_msg_count
1922 , p_msg_data => x_msg_data
1923 );
1924
1925
1926 return(0);
1927
1928
1929 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1930 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
1931
1932 cto_msg_pub.count_and_get
1933 ( p_msg_count => x_msg_count
1934 , p_msg_data => x_msg_data
1935 );
1936
1937
1938 return(-1);
1939
1940 WHEN OTHERS THEN
1941 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::other excpn ::'||to_char(lStmtNumber)||sqlerrm, 1);
1942
1943 cto_msg_pub.count_and_get
1944 ( p_msg_count => x_msg_count
1945 , p_msg_data => x_msg_data
1946 );
1947
1948
1949 return(0);
1950
1951 END Cost_Roll_Up_ML;
1952 /* This procedure has been maintained in patchset J to account for configuration item
1953 ** workflow references to patchset I
1954 */
1955 FUNCTION Cost_Rollup_ML(pTopAtoLineId in number,
1956 x_msg_count out NOCOPY number,
1957 x_msg_data out NOCOPY varchar2)
1958 RETURN integer
1959 IS
1960
1961 lMrpAssignmentSet number;
1962 lRollupId number;
1963 gUserId number;
1964 gLoginId number;
1965 lMsgCount number;
1966 lMsgData varchar2(2000);
1967 lStmtNumber number;
1968 lErrorCode number;
1969 lErrorMsg varchar2(2000);
1970 lCnt number;
1971 lConversionType varchar2(30);
1972 lBuyCostType varchar2(30);
1973
1974 CTO_MRP_ASSIGNMENT_SET exception;
1975
1976 v_cto_cost_type_id cst_cost_types.cost_type_id%type ;
1977 v_buy_cost_type_id cst_cost_types.cost_type_id%type ;
1978 v_buy_cost number ;
1979 c_line_id bom_cto_src_orgs.line_id%type ;
1980 c_model_item_id bom_cto_src_orgs.model_item_id%type ;
1981 c_config_item_id bom_cto_src_orgs.model_item_id%type ;
1982 c_cost_org_id mtl_parameters.cost_organization_id%type ; -- 3116778
1983 c_organization_id bom_cto_src_orgs.organization_id%type ;
1984 c_po_valid_org financials_system_params_all.inventory_organization_id%type ;
1985 c_oper_unit inv_organization_info_v.operating_unit%type ;
1986
1987 v_buy_item_cost mtl_system_items.list_price_per_unit%type ;
1988
1989
1990
1991 cursor c_buy_items
1992 is
1993 select bcso.line_id
1994 , bcso.model_item_id
1995 , bcso.config_item_id
1996 -- 3116778 , bcso.rcv_org_id
1997 , mp2.cost_organization_id -- 3116778
1998 , nvl(fsp.inventory_organization_id,0) po_valid_org
1999 , nvl(org.operating_unit,0) oper_unit
2000 from bom_cto_src_orgs bcso
2001 , bom_cto_order_lines bcol
2002 , financials_system_params_all fsp
2003 , inv_organization_info_v org
2004 , cst_item_costs cic
2005 , mtl_parameters mp1 /* master organization */
2006 , mtl_parameters mp2
2007 where bcso.top_model_line_id = pTopAtoLineId
2008 and bcol.line_id = bcso.line_id
2009 and bcso.cost_rollup = 'Y'
2010 and ( ( bcso.organization_type in (3,5) and bcol.config_creation in ( 1,2) )
2011 OR bcol.config_creation = 3 )
2012 and org.organization_id = bcso.organization_id
2013 and nvl(org.operating_unit,-1) = nvl(fsp.org_id,-1)
2014 and cic.inventory_item_id = bcso.config_item_id
2015 and cic.organization_id = mp2.cost_organization_id -- 3116778
2016 and mp2.organization_id = bcso.organization_id -- 3116778
2017 and mp2.cost_organization_id = mp1.organization_id -- 3116778
2018 and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id) ;
2019
2020 /* 3116778 */
2021 /*
2022 BUG 3931290
2023 The original cursor c_frozen_cost_update was using a union (organization_id , rcv_org_id )
2024 to determine organizations where standard cost update needs to be performed.
2025 The organizations where standard cost update will be performed will now be determined using organization_id.
2026 This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2027 A distinct clause has been added to the statement.
2028 */
2029 cursor c_frozen_cost_update(c_cto_cost_type_id number )
2030 is
2031 select distinct -- bug 3931290
2032 bcso.config_item_id
2033 , mp1.cost_organization_id rollup_org_id
2034 from bom_cto_src_orgs bcso
2035 , mtl_parameters mp1
2036 , mtl_parameters mp2
2037 , cst_item_costs cic
2038 where bcso.top_model_line_id = pTopAtoLineId
2039 and bcso.cost_rollup = 'Y'
2040 and bcso.organization_id = mp2.organization_id
2041 and mp2.cost_organization_id = mp1.organization_id
2042 and mp1.primary_cost_method = 1
2043 and cic.inventory_item_id = bcso.config_item_id
2044 and cic.organization_id = mp1.organization_id
2045 and cic.cost_type_id = c_cto_cost_type_id
2046 and ( cic.based_on_rollup_flag = 1 or bcso.organization_type = 3 ); /* bug 3931290 */
2047
2048
2049
2050 -- debug 3116778
2051 cursor cst_cur (xRollupId number) is
2052 select inventory_item_id,organization_id
2053 from cst_sc_lists
2054 where rollup_id =xRollupId;
2055 d_item_id number;
2056 d_org_id number;
2057
2058 cursor c1_cst (c_inventory_item_id number) is
2059 select cst.inventory_item_id, cst.organization_id, cst.cost_type_id,
2060 cst.item_cost , cst.material_cost, cst.material_overhead_cost,
2061 cicd.cost_element_id ,cicd.item_cost cicd_item_cost
2062 from cst_item_costs cst, cst_item_cost_details cicd
2063 where cst.inventory_item_id = cicd.inventory_item_id
2064 and cst.organization_id = cicd.organization_id
2065 and cst.cost_type_id = cicd.cost_type_id
2066 and cst.inventory_item_id = c_inventory_item_id ;
2067
2068
2069 c1_config_item_id number ;
2070
2071 v_cur_c1_cst c1_cst%rowtype ;
2072
2073 -- debug 3116778
2074
2075
2076 v_item_cost cst_item_costs.item_cost%type ;
2077 v_material_cost cst_item_costs.material_cost%type ;
2078 v_material_overhead_cost cst_item_costs.material_overhead_cost%type ;
2079 v_resource_cost cst_item_costs.resource_cost%type ;
2080 v_outside_processing_cost cst_item_costs.outside_processing_cost%type ;
2081 v_overhead_cost cst_item_costs.overhead_cost%type ;
2082
2083 v_cost_updateable BOOLEAN := false ;
2084
2085
2086 v_group_id bom_explosion_temp.group_id%type ;
2087 /*
2088 x_msg_count number ;
2089 x_msg_data varchar2(200) ;
2090 */
2091 x_return_status varchar2(200) ;
2092
2093
2094 l_token CTO_MSG_PUB.token_tbl;
2095
2096 v_po_validation_org varchar2(2000) ;
2097 v_org varchar2(2000) ;
2098 v_model_name varchar2(2000) ;
2099
2100 v_cto_cost_type_name cst_cost_types.cost_type%type;
2101 cst_sc_list_count number; --bug 4867460
2102
2103 l_cost_update_required varchar2(1) := 'N'; --bug 6717614
2104
2105 BEGIN
2106 lStmtNumber := 10;
2107
2108 gUserId := nvl(fnd_global.user_id, -1);
2109 gLoginId := nvl(fnd_global.login_id, -1);
2110 IF PG_DEBUG <> 0 THEN
2111 oe_debug_pub.add('Cost_Rollup_ML: ' || 'gUserId::'||to_char(gUserId)
2112 ||'::gLoginId::'||to_char(gLoginId), 2);
2113
2114 oe_debug_pub.add('Cost_Rollup_ML: ' || 'top line ::'|| pTopAtoLineId , 2 );
2115 END IF;
2116
2117
2118 -- changed sequence as per bugfix 3239456
2119 /*select bom_lists_s.nextval
2120 into lRollupId from dual;*/
2121
2122 select cst_lists_s.nextval
2123 into lRollupId from dual;
2124
2125
2126
2127 IF PG_DEBUG <> 0 THEN
2128 oe_debug_pub.add('Cost_Rollup_ML: ' || 'lRollupId::'||to_char(lRollupId), 2);
2129 END IF;
2130
2131 lStmtNumber := 20;
2132 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
2133
2134 IF lMrpAssignmentSet is null THEN
2135 IF PG_DEBUG <> 0 THEN
2136 oe_debug_pub.add('Cost_Rollup_ML: '
2137 || 'Default assignment set is null', 1);
2138 END IF;
2139
2140
2141 --FND_MESSAGE.set_name('BOM','CTO_MRP_ASSIGNMENT_SET');
2142 --raise CTO_MRP_ASSIGNMENT_SET;
2143 ELSE
2144 IF PG_DEBUG <> 0 THEN
2145 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Default assignment set is '
2146 ||to_char(lMrpAssignmentSet), 2);
2147 END IF;
2148 END IF;
2149
2150 --
2151 -- Getting the Currency Conversion Type from profile
2152 -- "INV:Inter-organization currency conversion"
2153 --
2154 lStmtNumber := 25;
2155 lConversionType := FND_PROFILE.VALUE('CURRENCY_CONVERSION_TYPE');
2156
2157
2158 IF PG_DEBUG <> 0 THEN
2159 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Currency Conversion Type ::'
2160 ||lConversionType, 2);
2161 END IF;
2162
2163 lStmtNumber := 27;
2164 lBuyCostType := FND_PROFILE.VALUE('CTO_BUY_COST_TYPE');
2165 IF PG_DEBUG <> 0 THEN
2166 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Buy Cost Type ::'|| lBuyCostType , 2);
2167 END IF;
2168
2169
2170 lStmtNumber := 28;
2171
2172 if( lBuyCostType is not null ) then
2173 begin
2174 select cost_type_id into v_buy_cost_type_id
2175 from cst_cost_types
2176 where cost_type = lBuyCostType ;
2177
2178 IF PG_DEBUG <> 0 THEN
2179 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Buy Cost Type id ::'
2180 || v_buy_cost_type_id , 2);
2181 END IF;
2182
2183 exception
2184 when no_data_found then
2185
2186 cto_msg_pub.cto_message('BOM','CTO_BUY_COST_NOT_FOUND');
2187 raise FND_API.G_EXC_ERROR;
2188
2189 when others then
2190
2191 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2192
2193
2194 end;
2195
2196
2197 end if ;
2198
2199
2200
2201 lStmtNumber := 29;
2202
2203 IF PG_DEBUG <> 0 THEN
2204 oe_debug_pub.add('Cost_Rollup_ML: '
2205 || ' opening buy cost cursor for bcso ' , 2);
2206 END IF;
2207
2208 if( lBuyCostType is not null ) then
2209
2210
2211 open c_buy_items ;
2212 loop
2213 fetch c_buy_items into c_line_id
2214 , c_model_item_id
2215 , c_config_item_id
2216 , c_cost_org_id -- 3116778
2217 , c_po_valid_org
2218 , c_oper_unit ;
2219
2220
2221 exit when c_buy_items%notfound ;
2222
2223 IF PG_DEBUG <> 0 THEN
2224 oe_debug_pub.add('Cost_Rollup_ML: ' ||
2225 ' fetched line ' || c_line_id ||
2226 ' model ' || c_model_item_id ||
2227 ' config ' || c_config_item_id ||
2228 ' cost org ' || c_cost_org_id || --3116778
2229 ' po val ' || c_po_valid_org ||
2230 ' oper ' || c_oper_unit , 2);
2231 END IF;
2232
2233 lStmtNumber := 30;
2234
2235 /* Check whether buy cost exists for the item */
2236 begin
2237 select item_cost into v_buy_cost
2238 from cst_item_costs
2239 where cost_type_id = v_buy_cost_type_id
2240 and organization_id = c_cost_org_id -- 3116778
2241 and inventory_item_id = c_config_item_id ;
2242
2243 exception
2244 when no_data_found then
2245 v_buy_cost := null ;
2246
2247 IF PG_DEBUG <> 0 THEN
2248 oe_debug_pub.add('no buy cost exists for item ' || c_config_item_id
2249 || ' in org ' || c_cost_org_id , 2); -- 3116778
2250 END IF;
2251
2252
2253 when others then
2254
2255
2256 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2257
2258 end ;
2259
2260
2261
2262 if( v_buy_cost is null ) then
2263
2264 lStmtNumber := 32;
2265
2266
2267
2268 BEGIN
2269
2270
2271 select nvl(list_price_per_unit,0) into v_buy_item_cost
2272 from mtl_system_items
2273 where organization_id = c_po_valid_org and inventory_item_id = c_config_item_id ;
2274
2275
2276 EXCEPTION
2277 when no_data_found then
2278
2279
2280 IF PG_DEBUG <> 0 THEN
2281 oe_debug_pub.add('create_config_item_wf: ' || 'Item Not Enabled in PO Validation Org ' || c_po_valid_org , 1);
2282 oe_debug_pub.add('create_config_item_wf: ' || 'PO Validation Org for Org ' || c_cost_org_id , 1);
2283 END IF;
2284
2285
2286 -- cto_msg_pub.cto_message('BOM', l_xmsgdata);
2287
2288 l_token(1).token_name := 'POV_ORG';
2289
2290
2291 select organization_name into v_po_validation_org
2292 from inv_organization_name_v
2293 where organization_id = c_po_valid_org ;
2294
2295
2296 l_token(1).token_value := v_po_validation_org ;
2297
2298
2299 l_token(2).token_name := 'ORG';
2300
2301 select organization_name into v_org
2302 from inv_organization_name_v
2303 where organization_id = c_cost_org_id ;
2304
2305
2306 l_token(2).token_value := v_org ;
2307
2308 l_token(3).token_name := 'MODEL_NAME';
2309
2310 select concatenated_segments into v_model_name
2311 from mtl_system_items_kfv
2312 where inventory_item_id = ( select base_item_id
2313 from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
2314 and rownum = 1 ;
2315
2316 l_token(3).token_value := v_model_name ;
2317
2318
2319
2320 cto_msg_pub.cto_message('BOM', 'CTO_ITEM_NOT_ENABLED_IN_POV', l_token );
2321
2322 raise FND_API.G_EXC_ERROR;
2323
2324
2325
2326
2327 when others then
2328
2329
2330 raise ;
2331
2332 END ;
2333
2334
2335
2336
2337 lStmtNumber := 35;
2338
2339 IF PG_DEBUG <> 0 THEN
2340 oe_debug_pub.add('going to populate buy cost for item ' || c_config_item_id
2341 || ' for $ ' || v_buy_item_cost , 2);
2342 END IF;
2343
2344 populate_buy_cost( c_line_id
2345 , c_config_item_id
2346 , c_cost_org_id -- 3116778
2347 , v_buy_cost_type_id
2348 , v_buy_item_cost ) ;
2349
2350
2351 else
2352
2353 IF PG_DEBUG <> 0 THEN
2354 oe_debug_pub.add('buy cost exists for item ' || c_config_item_id
2355 || ' for $ ' || v_buy_cost || 'in org ' || c_cost_org_id, 2); -- 3116778
2356 END IF;
2357
2358 end if ;
2359
2360
2361 end loop;
2362
2363
2364 close c_buy_items ;
2365
2366
2367 end if; /* check if buy cost is not null */
2368
2369
2370
2371
2372
2373
2374
2375 --
2376 -- insert into CST_SC_LISTS rows for all config items in all possible
2377 -- src orgs, where ->
2378 -- 1. cost has not been calculated yet (cost_rollup = Y)
2379 -- 2. costing_enabled_flag = Y
2380 -- 3. primary_cost_method = 1 (standard)
2381 --
2382 IF PG_DEBUG <> 0 THEN
2383 oe_debug_pub.add('Cost_Rollup_ML: ' || 'inserting into cst_sc_lists', 2);
2384 END IF;
2385
2386
2387 /*
2388 BUG 3931290
2389 The statement insert into cst_sc_lists was using a union (organization_id, rcv_org_id)
2390 to determine organizations where cost rollup needs to be performed.
2391 The organization where cost rollup will be performed will now be determined using organization_id.
2392 This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2393 */
2394
2395 lStmtNumber := 38;
2396 insert into cst_sc_lists(
2397 rollup_id,
2398 inventory_item_id,
2399 organization_id,
2400 last_update_date,
2401 last_updated_by,
2402 creation_date,
2403 created_by
2404 )
2405 select distinct
2406 lRollupId,
2407 bcso.config_item_id,
2408 mp.cost_organization_id,
2409 sysdate,
2410 gUserId,
2411 sysdate,
2412 gUserId
2413 from bom_cto_src_orgs bcso,
2414 mtl_system_items msi,
2415 mtl_parameters mp ,
2416 cst_item_costs cic
2417 where bcso.top_model_line_id = pTopAtoLineId
2418 and bcso.cost_rollup = 'Y'
2419 and bcso.config_item_id = msi.inventory_item_id
2420 and bcso.organization_id = msi.organization_id
2421 and msi.costing_enabled_flag = 'Y'
2422 and mp.organization_id = bcso.organization_id
2423 and cic.inventory_item_id = msi.inventory_item_id
2424 -- 3116778 and cic.organization_id = msi.organization_id
2425 and cic.organization_id = mp.cost_organization_id -- 3116778
2426 -- 3116778 and cic.based_on_rollup_flag = 1
2427 and (cic.based_on_rollup_flag = 1 or bcso.organization_type = 3) -- 3116778
2428 and (
2429 ( ( mp.primary_cost_method = 1 )
2430 and cic.cost_type_id = 1
2431 )
2432 OR
2433 ( ( mp.primary_cost_method = 2 )
2434 and cic.cost_type_id = 2
2435 )
2436 OR
2437 ( ( mp.primary_cost_method = 6 )
2438 and cic.cost_type_id = 6
2439 )
2440 OR
2441 ( ( mp.primary_cost_method = 5 )
2442 and cic.cost_type_id = 5
2443 )
2444 )
2445 and NOT EXISTS
2446 (select NULL
2447 from cst_sc_lists
2448 where rollup_id = lRollupId
2449 and inventory_item_id = bcso.config_item_id
2450 and organization_id = mp.cost_organization_id ) ;
2451
2452
2453
2454 -- debug 3116778
2455 lCnt := sql%rowcount;
2456 IF PG_DEBUG <> 0 THEN
2457 oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2458 END IF;
2459 cst_sc_list_count := lCnt; -- Bug# 4867460
2460 open cst_cur ( lRollupId ) ;
2461
2462 loop
2463
2464 fetch cst_cur into d_item_id,d_org_id;
2465
2466 exit when cst_cur%notfound ;
2467
2468 IF PG_DEBUG <> 0 THEN
2469 oe_debug_pub.add('Cst list has item : ' ||d_item_id|| ' in org ' || d_org_id,1);
2470 END IF;
2471
2472 end loop;
2473 close cst_cur;
2474 -- debug 3116778
2475
2476
2477
2478 lCnt := sql%rowcount;
2479 IF PG_DEBUG <> 0 THEN
2480 oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2481 END IF;
2482
2483 --
2484 -- call SC Cost Rollup API
2485 --
2486
2487 lStmtNumber := 40;
2488
2489
2490 /* commented for bug 4057651
2491 begin
2492
2493 select cost_type_id into v_cto_cost_type_id
2494 from cst_cost_types
2495 where cost_type = 'CTO' ;
2496
2497
2498 if( lBuyCostType is null ) then
2499 v_buy_cost_type_id := v_cto_cost_type_id ;
2500 end if ;
2501
2502
2503 exception
2504 when no_data_found then
2505
2506 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
2507 raise FND_API.G_EXC_ERROR;
2508
2509 when others then
2510
2511 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2512
2513
2514 end;
2515
2516
2517 commented for bug 4057651 */
2518
2519
2520
2521
2522
2523
2524
2525 /* begin bugfix 4057651, default CTO cost type id = 7 if it does not exist */
2526 begin
2527
2528 select cost_type_id into v_cto_cost_type_id
2529 from cst_cost_types
2530 where cost_type = 'CTO' ;
2531
2532
2533 exception
2534 when no_data_found then
2535
2536 IF PG_DEBUG <> 0 THEN
2537 oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
2538 oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
2539 END IF;
2540
2541 v_cto_cost_type_id := 7 ;
2542
2543 begin
2544 select cost_type into v_cto_cost_type_name
2545 from cst_cost_types
2546 where cost_type_id = v_cto_cost_type_id ;
2547
2548 IF PG_DEBUG <> 0 THEN
2549 oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || v_cto_cost_type_id ||
2550 ' has cost_type = ' || v_cto_cost_type_name ,2);
2551 END IF;
2552 exception
2553 when no_data_found then
2554 IF PG_DEBUG <> 0 THEN
2555 oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
2556 END IF;
2557 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
2558 raise FND_API.G_EXC_ERROR;
2559 when others then
2560
2561 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2562 end ;
2563
2564 when others then
2565 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2566 end ;
2567
2568 if( lBuyCostType is null ) then
2569 v_buy_cost_type_id := v_cto_cost_type_id ;
2570 end if ;
2571
2572 /* end bugfix 4057651, default CTO cost type id = 7 if it does not exist */
2573
2574
2575
2576 -- debug 3116778
2577
2578 select config_item_id
2579 into c1_config_item_id
2580 from bom_cto_order_lines
2581 where line_id = pTopatolineid ;
2582
2583 open c1_cst ( c1_config_item_id ) ;
2584
2585 IF PG_DEBUG <> 0 THEN
2586 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2587 END IF;
2588
2589 loop
2590
2591 fetch c1_cst into v_cur_c1_cst ;
2592
2593 exit when c1_cst%notfound ;
2594
2595 IF PG_DEBUG <> 0 THEN
2596 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2597 oe_debug_pub.add( 'iid ' || v_cur_c1_cst.inventory_item_id ||
2598 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
2599 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
2600 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
2601 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
2602 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost)||
2603 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
2604 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
2605 END IF;
2606
2607
2608 end loop ;
2609
2610 IF PG_DEBUG <> 0 THEN
2611 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
2612 END IF;
2613
2614 close c1_cst ;
2615
2616 -- debug 3116778
2617
2618
2619
2620
2621
2622
2623 lStmtNumber := 60;
2624 if cst_sc_list_count > 0 then --bug4867460: call the costing api only if records are inserted
2625 -- in cst_sc_lists. This is to improve performance.
2626 IF PG_DEBUG <> 0 THEN
2627 oe_debug_pub.add('******************** calling costing API ****************** ', 2);
2628 oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet ||
2629 ' cto cost id ' || v_cto_cost_type_id ||
2630 ' buy cost id ' || v_buy_cost_type_id , 2);
2631
2632 END IF;
2633
2634 /* Commented as part of Bugfix 7246036
2635 CSTPSCEX.Supply_Chain_Rollup(
2636 lRollupId, -- i_rollup_id
2637 1, -- i_explosion_level
2638 NULL, -- i_report_levels
2639 lMrpAssignmentSet, -- i_assignment_set_id
2640 lConversionType, -- i_conversion_type
2641 -- 1, -- i_cost_type_id
2642 v_cto_cost_type_id , -- i_cost_type_id
2643 v_buy_cost_type_id, -- i_buy_cost_type_id
2644 SYSDATE, -- i_effective_date
2645 1, -- exclude unimplemented ECOs, implemented only
2646 1, -- BOM items only, exclude ENG items
2647 '', -- i_alt_bom_desg
2648 '', -- i_alt_rtg_desg
2649 2, -- i_lock_flag
2650 gUserId, -- i_user_id
2651 gLoginId, -- i_login_id
2652 NULL, -- i_request_id
2653 NULL, -- i_prog_id??
2654 702, -- i_prog_appl_id
2655 lErrorCode, -- o_error_code
2656 lErrorMsg); -- o_error_msg
2657 */
2658
2659 CSTPSCEX.Supply_Chain_Rollup(
2660 i_rollup_id => lRollupId,
2661 i_explosion_levels => 1,
2662 i_report_levels => NULL,
2663 i_assignment_set_id => lMrpAssignmentSet,
2664 i_conversion_type => lConversionType,
2665 i_cost_type_id => v_cto_cost_type_id,
2666 i_buy_cost_type_id => v_buy_cost_type_id,
2667 i_effective_date => SYSDATE,
2668 i_exclude_unimpl_eco => 1,
2669 i_exclude_eng => 1,
2670 i_alt_bom_desg => '',
2671 i_alt_rtg_desg => '',
2672 i_lock_flag => 2,
2673 i_user_id => gUserId,
2674 i_login_id => gLoginId,
2675 i_request_id => NULL,
2676 i_prog_id => NULL,
2677 i_prog_appl_id => 702,
2678 o_error_code => lErrorCode,
2679 o_error_msg => lErrorMsg,
2680 i_buy_cost_detail => 1 ); --Bugfix 7246036: Passing preserve buy cost details parameter as Yes.
2681
2682 IF lErrorCode <> 0 THEN
2683 IF PG_DEBUG <> 0 THEN
2684 oe_debug_pub.add('************** Cost_Rollup_ML: ' || 'SC Rollup API returned with error::'||lErrorMsg, 1);
2685 END IF;
2686 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2687 ELSE
2688 IF PG_DEBUG <> 0 THEN
2689 oe_debug_pub.add('************** Cost_Rollup_ML: ' || 'SC Rollup API returned with success', 1);
2690 END IF;
2691
2692
2693 END IF;
2694 Else
2695 IF PG_DEBUG <> 0 Then
2696 oe_debug_pub.add('No cost data Exists. No need to call supply Chain cost rollup API',5);
2697 End if;
2698 End If; -- Bug fix 4867460
2699
2700
2701 -- debug 3116778
2702
2703 open c1_cst ( c1_config_item_id ) ;
2704
2705 IF PG_DEBUG <> 0 THEN
2706 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2707 END IF;
2708
2709 loop
2710
2711 fetch c1_cst into v_cur_c1_cst ;
2712
2713 exit when c1_cst%notfound ;
2714
2715 IF PG_DEBUG <> 0 THEN
2716 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2717 oe_debug_pub.add( 'iid ' || v_cur_c1_cst.inventory_item_id ||
2718 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
2719 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
2720 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
2721 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
2722 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost) ||
2723 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
2724 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
2725 END IF;
2726
2727 end loop ;
2728
2729 IF PG_DEBUG <> 0 THEN
2730 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
2731 END IF;
2732
2733 close c1_cst ;
2734
2735 -- debug 3116778
2736
2737
2738 /*
2739 **
2740 ** Costs need to be copied from simulation cost to frozen cost in case of
2741 ** standard costing organization
2742 **
2743 */
2744
2745
2746
2747
2748 lStmtNumber := 80;
2749 --Bugfix 6717614
2750 if cst_sc_list_count = 0 then
2751
2752 IF PG_DEBUG <> 0 THEN
2753 oe_debug_pub.add('***No need to do frozen cost update as no rollup has been done' , 1);
2754 END IF;
2755
2756 else
2757 /*update Frozen cost with CTO Cost in case of standard costing organizations */
2758 lStmtNumber := 90;
2759
2760 open c_frozen_cost_update ( v_cto_cost_type_id ) ;
2761
2762 loop
2763
2764 fetch c_frozen_cost_update into c_config_item_id
2765 , c_organization_id ;
2766
2767
2768 exit when c_frozen_cost_update%notfound ;
2769
2770 --Bugfix 6717614
2771 lStmtNumber := 95;
2772 l_cost_update_required := cost_update_required(c_config_item_id,
2773 c_organization_id,
2774 v_cto_cost_type_id);
2775
2776 IF (l_cost_update_required = 'Y') THEN
2777
2778
2779 IF PG_DEBUG <> 0 THEN
2780 oe_debug_pub.add('********************** going to do frozen cost update for config ' || c_config_item_id
2781 || ' in org ' || c_organization_id , 1);
2782 END IF;
2783
2784
2785 delete from cst_item_cost_details
2786 where inventory_item_id = c_config_item_id
2787 and organization_id = c_organization_id
2788 and cost_type_id = 1 ;
2789
2790 IF PG_DEBUG <> 0 THEN
2791 oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
2792 END IF;
2793
2794
2795 delete from cst_item_costs
2796 where inventory_item_id = c_config_item_id
2797 and organization_id = c_organization_id
2798 and cost_type_id = 1 ;
2799
2800
2801 IF PG_DEBUG <> 0 THEN
2802 oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
2803 END IF;
2804
2805
2806 copy_ctocost_to_frozen ( c_config_item_id
2807 , c_organization_id
2808 , v_cto_cost_type_id ) ;
2809
2810 END IF; --l_cost_update_required Bugfix 6717614
2811 end loop ;
2812
2813
2814 close c_frozen_cost_update ;
2815 end if; --cst_sc_list_count = 0 Bugfix 6717614
2816
2817
2818 -- debug 3116778
2819
2820 open c1_cst ( c1_config_item_id ) ;
2821
2822 IF PG_DEBUG <> 0 THEN
2823 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2824 END IF;
2825
2826 loop
2827
2828 fetch c1_cst into v_cur_c1_cst ;
2829
2830 exit when c1_cst%notfound ;
2831
2832 IF PG_DEBUG <> 0 THEN
2833 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2834 oe_debug_pub.add( ' iid ' || v_cur_c1_cst.inventory_item_id ||
2835 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
2836 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
2837 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
2838 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
2839 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost) ||
2840 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
2841 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
2842 END IF;
2843
2844 end loop ;
2845
2846 IF PG_DEBUG <> 0 THEN
2847 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
2848 END IF;
2849
2850 close c1_cst ;
2851
2852 -- debug 3116778
2853
2854
2855
2856 IF PG_DEBUG <> 0 THEN
2857 oe_debug_pub.add('Cost_Rollup_ML: ' || 'At end of cost rollup', 2);
2858 END IF;
2859 return(1);
2860
2861 EXCEPTION
2862 WHEN NO_DATA_FOUND THEN
2863 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
2864 return(0);
2865
2866 WHEN CTO_MRP_ASSIGNMENT_SET THEN
2867 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::mrp assgn set is null'||to_char(lStmtNumber)||sqlerrm, 1);
2868
2869 cto_msg_pub.count_and_get
2870 ( p_msg_count => x_msg_count
2871 , p_msg_data => x_msg_data
2872 );
2873
2874
2875 return(0);
2876
2877
2878 WHEN FND_API.G_EXC_ERROR THEN
2879 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
2880
2881 cto_msg_pub.count_and_get
2882 ( p_msg_count => x_msg_count
2883 , p_msg_data => x_msg_data
2884 );
2885
2886
2887 return(0);
2888
2889
2890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2891 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
2892
2893 cto_msg_pub.count_and_get
2894 ( p_msg_count => x_msg_count
2895 , p_msg_data => x_msg_data
2896 );
2897
2898 return(-1);
2899
2900
2901
2902 END Cost_Rollup_ML;
2903
2904 end CTO_CONFIG_COST_PK;