[Home] [Help]
PACKAGE BODY: APPS.CTO_CONFIG_COST_PK
Source
1 package body CTO_CONFIG_COST_PK as
2 /* $Header: CTOCSTRB.pls 120.9 2011/12/30 13:36:18 kupradha 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 --Removing distinct for base BUG 13456609; FP 13541690
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 --Removing distinct for base BUG 13456609; FP 13541690
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 IF PG_DEBUG <> 0 THEN
977 oe_debug_pub.add('Inside check_ct_updateable for p_inventory_item_id :'||p_inventory_item_id ||' p_organization_id :'||p_organization_id||' p_cost_type_id :'||p_cost_type_id );
978 END IF;
979
980 IF ( p_cost_type_id = 1) THEN
981 IF (Updateable is NULL) THEN
982 Open Check_Updateable;
983 Fetch Check_Updateable into Updateable;
984 Close Check_Updateable;
985
986 IF PG_DEBUG <> 0 THEN
987 oe_debug_pub.add('Inside check_ct_updateable : After closing Check_Updateable flag is :'|| Updateable);
988 END IF;
989
990 IF (Updateable is Null) THEN
991
992 Open Check_Updateable_2;
993 Fetch Check_Updateable_2 into Updateable;
994 Close Check_Updateable_2;
995
996 IF PG_DEBUG <> 0 THEN
997 oe_debug_pub.add('Inside check_ct_updateable : After closing Check_Updateable_2 flag is :'|| Updateable);
998 END IF;
999 END IF;
1000
1001 IF (Updateable is NULL) THEN
1002 IF PG_DEBUG <> 0 THEN
1003 oe_debug_pub.add('Inside check_ct_updateable : Checking for intransit_count');
1004 END IF;
1005
1006 select count(*)
1007 into intransit_count
1008 from mtl_supply m
1009 where m.item_id = p_inventory_item_id
1010 and m.intransit_owning_org_id = p_organization_id
1011 and m.to_organization_id = p_organization_id ;
1012
1013 IF PG_DEBUG <> 0 THEN
1014 oe_debug_pub.add('Inside check_ct_updateable : Intransit_count ='|| intransit_count);
1015 END IF;
1016
1017 IF (intransit_count > 0) THEN
1018 Updateable := 'NO';
1019 END IF;
1020 END IF;
1021
1022 END IF;
1023 IF (Updateable = 'NO') THEN
1024 -- fnd_message.Set_Name('BOM', 'CST_ITEM_USED_IN_TXN');
1025 RetVal := FALSE;
1026 ELSE
1027 IF PG_DEBUG <> 0 THEN
1028 oe_debug_pub.add( ' check_ct_updateable is null -> true ' ) ;
1029 END IF;
1030
1031 RetVal := TRUE;
1032 END IF;
1033
1034 ELSE
1035 IF PG_DEBUG <> 0 THEN
1036 oe_debug_pub.add( ' cost type id not 1 ' ) ;
1037 END IF;
1038
1039 RetVal := TRUE;
1040 END IF;
1041
1042 IF PG_DEBUG <> 0 THEN
1043
1044 if( RetVal = TRUE ) then
1045 oe_debug_pub.add( ' check_ct_updateable is true ' ) ;
1046 elsif( RetVal = False ) then
1047 oe_debug_pub.add( ' check_ct_updateable is false' ) ;
1048 elsif( RetVal is null ) then
1049 oe_debug_pub.add( ' check_ct_updateable is null ' ) ;
1050 end if ;
1051 END IF;
1052
1053
1054
1055 Return RetVal;
1056 END Check_CT_Updateable;
1057
1058
1059 /* fp-J : This procedure has been added for optional processing for cost rollup (ksarkar)
1060 */
1061
1062 FUNCTION Cost_Roll_Up_ML( p_cfg_itm_tbl in CTO_COST_ROLLUP_CONC_PK.t_cfg_item
1063 , x_msg_count out NOCOPY number
1064 , x_msg_data out NOCOPY varchar2
1065 )
1066 RETURN integer
1067 IS
1068
1069 lMrpAssignmentSet number;
1070 lRollupId number;
1071 gUserId number;
1072 gLoginId number;
1073 lMsgCount number;
1074 lMsgData varchar2(2000);
1075 lStmtNumber number;
1076 lErrorCode number;
1077 lErrorMsg varchar2(2000);
1078 lCnt number;
1079 lConversionType varchar2(30);
1080 lBuyCostType varchar2(30);
1081
1082 CTO_MRP_ASSIGNMENT_SET exception;
1083
1084 v_cto_cost_type_id cst_cost_types.cost_type_id%type ;
1085 v_buy_cost_type_id cst_cost_types.cost_type_id%type ;
1086 v_buy_cost number ;
1087 c_line_id bom_cto_src_orgs.line_id%type ;
1088 c_model_item_id bom_cto_src_orgs.model_item_id%type ;
1089 c_config_item_id bom_cto_src_orgs.model_item_id%type ;
1090 c_match_config_item_id bom_cto_src_orgs.model_item_id%type ;
1091 c_org_id bom_cto_src_orgs.rcv_org_id%type ;
1092 c_organization_id bom_cto_src_orgs.organization_id%type ;
1093 c_po_valid_org financials_system_params_all.inventory_organization_id%type ;
1094 c_oper_unit inv_organization_info_v.operating_unit%type ;
1095
1096 v_buy_item_cost mtl_system_items.list_price_per_unit%type ;
1097
1098 -- rkaza. 3742393. 08/12/2004.
1099 -- Repalcing org_organization_definitions with inv_organization_info_v
1100
1101
1102 -- For Performance reason, We are removing the join with financial_system_params_all table
1103 -- For some reason, this join is causing non mergable view or inv_organization_info_v.
1104 -- We don't know the exact reason yet. But for now, we are planning to break the sql into two parts
1105 -- to avoid this performance issue.
1106 cursor c_buy_items( xcfg_itm_id number,
1107 xcfg_org_id number
1108 )
1109 is
1110 select
1111 msi.base_item_id
1112 , msi.inventory_item_id
1113 , mp1.cost_organization_id -- 3116778
1114 , nvl(org.operating_unit,0) oper_unit
1115 from inv_organization_info_v org
1116 , mtl_system_items msi
1117 , cst_item_costs cic
1118 , mtl_parameters mp1
1119 where org.organization_id = msi.organization_id
1120 and cic.inventory_item_id = msi.inventory_item_id
1121 and cic.organization_id = mp1.cost_organization_id --3116778
1122 and cic.based_on_rollup_flag = 1
1123 and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id)
1124 and msi.organization_id = MP1.organization_id
1125 and msi.inventory_item_id = xcfg_itm_id
1126 and msi.organization_id = xcfg_org_id;
1127
1128
1129 /*
1130 This cursor does not use bcol table to check for matched items
1131 as cost rollup may be called for items that do not have an entry in bcol.
1132 Instead we will check whether the item has a valuation cost with a rolled up
1133 component.
1134 */
1135
1136 cursor c_matched_items_cost_synchup ( xcfg_itm_id number,
1137 xcfg_org_id number
1138 )
1139 is
1140 select msi.inventory_item_id
1141 , msi.organization_id
1142 , cic.cost_type_id
1143 , cic.item_cost
1144 from mtl_system_items msi
1145 , cst_item_costs cic
1146 , mtl_parameters mp
1147 where msi.organization_id = cic.organization_id
1148 and msi.inventory_item_id = cic.inventory_item_id
1149 and msi.organization_id = mp.organization_id
1150 and mp.primary_cost_method = cic.cost_type_id
1151 and EXISTS /* check whether item has been rolled up */
1152 ( select NULL
1153 from cst_item_cost_details
1154 where rollup_source_type = 3
1155 and inventory_item_id = msi.inventory_item_id
1156 and cost_type_id = cic.cost_type_id
1157 and organization_id = msi.organization_id
1158 )
1159 and msi.inventory_item_id = xcfg_itm_id
1160 and msi.organization_id = xcfg_org_id;
1161
1162 cursor c_frozen_cost_update( c_cto_cost_type_id number,
1163 xcfg_itm_id number,
1164 xcfg_org_id number
1165 )
1166 is
1167 select msi.inventory_item_id
1168 , mp.cost_organization_id rollup_org_id
1169 from mtl_system_items msi
1170 , mtl_parameters mp
1171 , cst_item_costs cic
1172 where msi.organization_id = mp.organization_id
1173 and mp.primary_cost_method = 1
1174 and cic.inventory_item_id = msi.inventory_item_id
1175 and cic.organization_id = msi.organization_id
1176 and cic.cost_type_id = c_cto_cost_type_id
1177 and msi.inventory_item_id = xcfg_itm_id
1178 and msi.organization_id = xcfg_org_id;
1179
1180
1181 v_item_cost cst_item_costs.item_cost%type ;
1182 v_cto_cost cst_item_costs.item_cost%type ;
1183 v_material_cost cst_item_costs.material_cost%type ;
1184 v_material_overhead_cost cst_item_costs.material_overhead_cost%type ;
1185 v_resource_cost cst_item_costs.resource_cost%type ;
1186 v_outside_processing_cost cst_item_costs.outside_processing_cost%type ;
1187 v_overhead_cost cst_item_costs.overhead_cost%type ;
1188
1189 v_cost_updateable BOOLEAN := false ;
1190
1191
1192 v_group_id bom_explosion_temp.group_id%type ;
1193 x_return_status varchar2(200) ;
1194
1195
1196 c_primary_cost_method mtl_parameters.primary_cost_method%type ;
1197 c_valuation_cost number;
1198
1199
1200 l_token CTO_MSG_PUB.token_tbl;
1201
1202
1203
1204 v_po_validation_org varchar2(2000) ;
1205 v_org varchar2(2000) ;
1206 v_model_name varchar2(2000) ;
1207
1208
1209 v_cto_cost_type_name cst_cost_types.cost_type%type;
1210
1211 l_cost_data_exists Varchar2(1) := 'N'; -- Bug Fix 4867460
1212 l_cost_update_required varchar2(1) := 'N'; --Bugfix 6717614
1213
1214 -- bug 13106676
1215 --
1216 v_primary_cost_method mtl_parameters.primary_cost_method%type := null ;
1217 v_is_cst_updatable BOOLEAN := TRUE;
1218
1219 --Bugfix 12957444: New variables.
1220 --TYPE error_cache_tbl_type IS TABLE OF VARCHAR2(32767) INDEX BY LONG;
1221 --g_error_cache error_cache_tbl_type;
1222 l_error_cache_val varchar2(32767);
1223 l_ret_val number;
1224
1225 BEGIN
1226 lStmtNumber := 10;
1227
1228 --Bugfix 12957444: Cleaning the global collection.
1229 CTO_COST_ROLLUP_CONC_PK.g_error_cache.delete;
1230
1231 if p_cfg_itm_tbl.COUNT = 0 then
1232 oe_debug_pub.add ('Cost_Roll_Up_ML: '|| 'Array p_cfg_itm_tbl does not contain any rows to process.');
1233 return(1);
1234 else
1235 oe_debug_pub.add ('Cost_Roll_Up_ML: '|| 'Going to process '||p_cfg_itm_tbl.COUNT|| ' rows... ');
1236
1237 end if;
1238
1239 gUserId := nvl(fnd_global.user_id, -1);
1240 gLoginId := nvl(fnd_global.login_id, -1);
1241
1242 IF PG_DEBUG <> 0 THEN
1243 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'gUserId::'||to_char(gUserId)
1244 ||'::gLoginId::'||to_char(gLoginId), 2);
1245
1246 END IF;
1247
1248 -- changed sequence as per bugfix 3239456
1249 /*select bom_lists_s.nextval
1250 into lRollupId from dual;*/
1251
1252 select cst_lists_s.nextval
1253 into lRollupId from dual;
1254
1255
1256
1257 IF PG_DEBUG <> 0 THEN
1258 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'lRollupId::'||to_char(lRollupId), 2);
1259 END IF;
1260
1261 lStmtNumber := 20;
1262 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
1263
1264 IF lMrpAssignmentSet is null THEN
1265 IF PG_DEBUG <> 0 THEN
1266 oe_debug_pub.add('Cost_Roll_Up_ML '
1267 || 'Default assignment set is null', 1);
1268 END IF;
1269
1270
1271 --FND_MESSAGE.set_name('BOM','CTO_MRP_ASSIGNMENT_SET');
1272 --raise CTO_MRP_ASSIGNMENT_SET;
1273 ELSE
1274 IF PG_DEBUG <> 0 THEN
1275 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Default assignment set is '
1276 ||to_char(lMrpAssignmentSet), 2);
1277 END IF;
1278 END IF;
1279
1280 --
1281 -- Getting the Currency Conversion Type from profile
1282 -- "INV:Inter-organization currency conversion"
1283 --
1284 lStmtNumber := 25;
1285 lConversionType := FND_PROFILE.VALUE('CURRENCY_CONVERSION_TYPE');
1286
1287
1288 IF PG_DEBUG <> 0 THEN
1289 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Currency Conversion Type ::'
1290 ||lConversionType, 2);
1291 END IF;
1292
1293
1294
1295 /* bug 4177423
1296 statement to retrieve CTO cost type id has been removed out from the loop.
1297 This will execute the statement only once.
1298 */
1299
1300
1301 IF PG_DEBUG <> 0 THEN
1302 oe_debug_pub.add ('Fetching CTO cost_type_id..');
1303 END IF;
1304
1305
1306
1307 /* commented for bug 4057651
1308 begin
1309
1310 select cost_type_id into v_cto_cost_type_id
1311 from cst_cost_types
1312 where cost_type = 'CTO' ;
1313
1314
1315
1316 exception
1317 when no_data_found then
1318 -- rollback to CTOCST; bug 4177423
1319 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
1320 -- goto endloop; bug 4177423
1321 raise FND_API.G_EXC_ERROR;
1322
1323 when others then
1324 oe_debug_pub.add('Unexpected error while getting the cost_type_id: ' ||sqlerrm);
1325 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1326
1327 end;
1328
1329
1330 commented for bug 4057651 */
1331
1332
1333
1334 /* begin bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1335 begin
1336
1337 select cost_type_id into v_cto_cost_type_id
1338 from cst_cost_types
1339 where cost_type = 'CTO' ;
1340
1341 exception
1342 when no_data_found then
1343
1344 IF PG_DEBUG <> 0 THEN
1345 oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
1346 oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
1347 END IF;
1348
1349 v_cto_cost_type_id := 7 ;
1350
1351 begin
1352 select cost_type into v_cto_cost_type_name
1353 from cst_cost_types
1354 where cost_type_id = v_cto_cost_type_id ;
1355
1356 IF PG_DEBUG <> 0 THEN
1357 oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || v_cto_cost_type_id ||
1358 ' has cost_type = ' || v_cto_cost_type_name ,2);
1359 END IF;
1360 exception
1361 when no_data_found then
1362 IF PG_DEBUG <> 0 THEN
1363 oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
1364 END IF;
1365 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
1366 raise FND_API.G_EXC_ERROR;
1367 when others then
1368
1369 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1370 end ;
1371
1372 when others then
1373 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1374 end ;
1375 /* end bugfix 4057651, default CTO cost type id = 7 if it does not exist */
1376
1377
1378
1379 /* bug 4177423
1380 CTO BUY cost type id will be defaulted to CTO cost type if it is null.
1381 */
1382
1383 lStmtNumber := 27;
1384 lBuyCostType := FND_PROFILE.VALUE('CTO_BUY_COST_TYPE');
1385 IF PG_DEBUG <> 0 THEN
1386 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Buy Cost Type ::'|| lBuyCostType , 2);
1387 END IF;
1388
1389
1390 lStmtNumber := 28;
1391
1392 if( lBuyCostType is not null ) then
1393 begin
1394 select cost_type_id into v_buy_cost_type_id
1395 from cst_cost_types
1396 where cost_type = lBuyCostType ;
1397
1398 IF PG_DEBUG <> 0 THEN
1399 oe_debug_pub.add('Cost_Roll_Up_ML ' || 'Buy Cost Type id ::'
1400 || v_buy_cost_type_id , 2);
1401 END IF;
1402
1403 exception
1404 when no_data_found then
1405
1406 cto_msg_pub.cto_message('BOM','CTO_BUY_COST_NOT_FOUND');
1407 raise FND_API.G_EXC_ERROR;
1408
1409 when others then
1410
1411 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1412
1413 end;
1414
1415 else /* fix for bug 4177423 */
1416
1417 IF PG_DEBUG <> 0 THEN
1418 oe_debug_pub.add('Cost_Roll_Up_ML ' || ' Defaulting Buy Cost Type to CTO cost id ' || v_cto_cost_type_id , 2);
1419 END IF;
1420
1421 v_buy_cost_type_id := v_cto_cost_type_id ;
1422
1423 end if ;
1424
1425
1426 lStmtNumber := 29;
1427
1428 IF PG_DEBUG <> 0 THEN
1429 oe_debug_pub.add('Cost_Roll_Up_ML ' || ' opening buy cost cursor for bcso ' , 2);
1430 END IF;
1431
1432
1433
1434 for i in p_cfg_itm_tbl.FIRST..p_cfg_itm_tbl.LAST
1435 LOOP -- to loop thru all rows in p_cfg_itm_tbl
1436
1437 --Bugfix 12957444: Re-initializing the variables to null.
1438 c_model_item_id := NULL;
1439 c_config_item_id := NULL;
1440 c_org_id := NULL;
1441 c_oper_unit := NULL;
1442
1443 SAVEPOINT CTOCST;
1444
1445 -- Instead of doing a simple select, we are doing a cursor fetch
1446 -- to ensure that it doesnt fail with too_many_rows.
1447
1448
1449 /* bug 4341197 do not populate list price in buy cost type if the buy cost type profile is not set by the user */
1450 if( lBuyCostType is not null ) then
1451
1452
1453
1454
1455
1456 oe_debug_pub.add ('---------------------------------------------------------------------------');
1457 oe_debug_pub.add ('Processing config_item_id '|| p_cfg_itm_tbl(i).cfg_item_id ||
1458 ' in orgn '||p_cfg_itm_tbl(i).cfg_org_id);
1459 oe_debug_pub.add ('---------------------------------------------------------------------------');
1460
1461 open c_buy_items( p_cfg_itm_tbl(i).cfg_item_id,
1462 p_cfg_itm_tbl(i).cfg_org_id) ;
1463
1464 fetch c_buy_items into c_model_item_id
1465 , c_config_item_id
1466 , c_org_id
1467 , c_oper_unit;
1468
1469
1470 close c_buy_items ;
1471
1472 --Bugfix 12957444: Checking if c_oper_unit is null. The next sql gives ndf
1473 --exception otherwise and the block of configs is not processed.
1474 if c_oper_unit is null then
1475 IF PG_DEBUG <> 0 THEN
1476 oe_debug_pub.add('Cost_Roll_Up_ML:' || 'c_oper_unit is null for this config. Skipping this config..');
1477 END IF;
1478
1479 lStmtNumber := 29.1;
1480 CTO_COST_ROLLUP_CONC_PK.g_error_cache(p_cfg_itm_tbl(i).cfg_item_id || '-' || p_cfg_itm_tbl(i).cfg_org_id) := i;
1481 goto endloop;
1482 end if;
1483
1484 -- Added by Renga Kannan on 03/15/20067 to get the po validation org
1485
1486 lStmtNumber := 29.2;
1487
1488 Select nvl(inventory_organization_id,0)
1489 into c_po_valid_org
1490 from financials_system_params_All
1491 where org_id = c_oper_unit;
1492
1493
1494 IF PG_DEBUG <> 0 THEN
1495 oe_debug_pub.add('Cost_Roll_Up_ML ' ||
1496 ' model ' || c_model_item_id ||
1497 ' config ' || c_config_item_id ||
1498 ' org ' || c_org_id ||
1499 ' po val ' || c_po_valid_org ||
1500 ' oper ' || c_oper_unit, 2);
1501 END IF;
1502
1503 lStmtNumber := 30;
1504
1505
1506 /* Check whether buy cost exists for the item */
1507 begin
1508 select item_cost into v_buy_cost
1509 from cst_item_costs
1510 where cost_type_id = v_buy_cost_type_id
1511 and organization_id = c_org_id
1512 and inventory_item_id = c_config_item_id ;
1513
1514 exception
1515 when no_data_found then
1516 v_buy_cost := null ;
1517
1518 IF PG_DEBUG <> 0 THEN
1519 oe_debug_pub.add('no buy cost exists for item ' || c_config_item_id
1520 || ' in org ' || c_org_id , 2);
1521 END IF;
1522
1523
1524 when others then
1525 oe_debug_pub.add('Unexpected error while checking buy cost for item_id ' ||c_config_item_id|| ' in orgn '||c_org_id);
1526 oe_debug_pub.add(sqlerrm);
1527 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1528
1529 end ;
1530
1531
1532
1533 if( v_buy_cost is null ) then
1534
1535 lStmtNumber := 32;
1536 BEGIN
1537 select nvl(list_price_per_unit,0) into v_buy_item_cost
1538 from mtl_system_items
1539 where organization_id = c_po_valid_org
1540 and inventory_item_id = c_config_item_id ;
1541
1542 EXCEPTION
1543 when no_data_found then
1544
1545
1546 IF PG_DEBUG <> 0 THEN
1547 oe_debug_pub.add('create_config_item_wf: ' || 'Item Not Enabled in PO Validation Org ' || c_po_valid_org , 1);
1548 oe_debug_pub.add('create_config_item_wf: ' || 'PO Validation Org for Org ' || c_org_id , 1);
1549 END IF;
1550
1551
1552 -- cto_msg_pub.cto_message('BOM', l_xmsgdata);
1553
1554 l_token(1).token_name := 'POV_ORG';
1555
1556 -- rkaza. 3742393. 08/12/2004.
1557 -- Repalcing org_organization_definitions with
1558 -- inv_organization_name_v
1559
1560 select organization_name into v_po_validation_org
1561 from inv_organization_name_v
1562 where organization_id = c_po_valid_org ;
1563
1564
1565 l_token(1).token_value := v_po_validation_org ;
1566
1567
1568 l_token(2).token_name := 'ORG';
1569
1570 select organization_name into v_org
1571 from inv_organization_name_v
1572 where organization_id = c_org_id ;
1573
1574
1575 l_token(2).token_value := v_org ;
1576
1577
1578
1579 l_token(3).token_name := 'MODEL_NAME';
1580
1581 select concatenated_segments into v_model_name
1582 from mtl_system_items_kfv
1583 where inventory_item_id = ( select base_item_id
1584 from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
1585 and rownum = 1 ;
1586
1587 l_token(3).token_value := v_model_name ;
1588
1589
1590 cto_msg_pub.cto_message('BOM', 'CTO_ITEM_NOT_ENABLED_IN_POV', l_token );
1591
1592 raise FND_API.G_EXC_ERROR;
1593
1594
1595
1596
1597 when others then
1598
1599
1600 raise ;
1601
1602 END ;
1603
1604
1605
1606 lStmtNumber := 35;
1607
1608 IF PG_DEBUG <> 0 THEN
1609 oe_debug_pub.add('going to populate buy cost for item ' || c_config_item_id
1610 || ' for $ ' || v_buy_item_cost , 2);
1611 END IF;
1612
1613 populate_buy_cost_rollup( c_config_item_id
1614 , c_org_id
1615 , v_buy_cost_type_id
1616 , v_buy_item_cost
1617 , x_return_status) ;
1618
1619 if x_return_status = FND_API.G_RET_STS_ERROR then
1620 ROLLBACK to CTOCST;
1621 oe_debug_pub.add ('Expected error in populate_buy_cost_rollup.');
1622 goto endloop;
1623
1624 elsif x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
1625 oe_debug_pub.add ('UnExpected error in populate_buy_cost_rollup.');
1626 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1627 end if;
1628
1629
1630 else
1631
1632 IF PG_DEBUG <> 0 THEN
1633 oe_debug_pub.add('buy cost exists for item ' || c_config_item_id
1634 || ' for $ ' || v_buy_cost , 2);
1635 END IF;
1636
1637 end if ;
1638
1639
1640
1641
1642
1643 end if; /* check if buy cost is not null */
1644 /* bug 4341197 do not populate list price in buy cost type if the buy cost type profile is not set by the user */
1645
1646
1647
1648
1649 lStmtNumber := 37;
1650 IF PG_DEBUG <> 0 THEN
1651 oe_debug_pub.add ('Fetching c_matched_items_cost_synchup cursor ..');
1652 END IF;
1653
1654 /* copy valuation cost to cto cost for matched items */
1655
1656 open c_matched_items_cost_synchup ( p_cfg_itm_tbl(i).cfg_item_id,
1657 p_cfg_itm_tbl(i).cfg_org_id) ;
1658
1659 fetch c_matched_items_cost_synchup into c_match_config_item_id
1660 , c_organization_id
1661 , c_primary_cost_method
1662 , c_valuation_cost ;
1663
1664 close c_matched_items_cost_synchup;
1665
1666 IF PG_DEBUG <> 0 THEN
1667 oe_debug_pub.add ('c_match_config_item_id = '|| c_match_config_item_id || ';' ||
1668 'c_organization_id = '|| c_organization_id || ';' ||
1669 'c_primary_cost_method = '|| c_primary_cost_method || ';' ||
1670 'c_valuation_cost = '|| c_valuation_cost );
1671 END IF;
1672
1673 lStmtNumber := 38;
1674 begin
1675
1676 select item_cost into v_cto_cost from cst_item_costs
1677 where inventory_item_id = c_match_config_item_id
1678 and organization_id = c_organization_id
1679 and cost_type_id = v_cto_cost_type_id ;
1680
1681
1682 exception
1683 when others then
1684 v_cto_cost := null ;
1685
1686 end;
1687
1688 IF PG_DEBUG <> 0 THEN
1689 oe_debug_pub.add ('v_cto_cost = '|| v_cto_cost );
1690 END IF;
1691
1692 lStmtNumber := 39;
1693 if( c_valuation_cost <> v_cto_cost or v_cto_cost is null ) then
1694
1695 CTO_UTILITY_PK.copy_cost( c_primary_cost_method
1696 , v_cto_cost_type_id
1697 , c_match_config_item_id
1698 , c_organization_id
1699 ) ;
1700
1701 end if;
1702
1703
1704 IF PG_DEBUG <> 0 THEN
1705 oe_debug_pub.add('calling costing API', 2);
1706 oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet || '; cto cost id ' || v_cto_cost_type_id ||
1707 ' buy cost id ' || v_buy_cost_type_id , 2);
1708
1709 END IF;
1710
1711
1712
1713 /* can't do bulk insert into cst_sc_lists for PLS-00436 */
1714 /* insert item by item */
1715
1716 lStmtNumber := 39.1;
1717
1718
1719 insert into cst_sc_lists(
1720 rollup_id,
1721 inventory_item_id,
1722 organization_id,
1723 last_update_date,
1724 last_updated_by,
1725 creation_date,
1726 created_by
1727 )
1728 select distinct
1729 lRollupId,
1730 p_cfg_itm_tbl(i).cfg_item_id,
1731 mp.cost_organization_id, --3116778
1732 sysdate,
1733 gUserId,
1734 sysdate,
1735 gUserId
1736 from mtl_system_items msi,
1737 mtl_parameters mp ,
1738 cst_item_costs cic
1739 where msi.costing_enabled_flag = 'Y'
1740 and mp.organization_id = msi.organization_id
1741 and cic.inventory_item_id = msi.base_item_id
1742 and cic.organization_id = mp.cost_organization_id
1743 and cic.based_on_rollup_flag = 1
1744 and mp.primary_cost_method = cic.cost_type_id
1745 and cic.cost_type_id in ( 1, 2, 5, 6 )
1746 and msi.inventory_item_id = p_cfg_itm_tbl(i).cfg_item_id
1747 and msi.organization_id = p_cfg_itm_tbl(i).cfg_org_id
1748 and NOT EXISTS
1749 (select NULL
1750 from cst_sc_lists
1751 where rollup_id = lRollupId
1752 and inventory_item_id = msi.inventory_item_id
1753 and organization_id = mp.cost_organization_id) --3116778
1754 and NOT EXISTS /* check whether item has been rolled up */
1755 ( select NULL
1756 from cst_item_cost_details
1757 where rollup_source_type = 3
1758 and inventory_item_id = msi.inventory_item_id
1759 and cost_type_id = cic.cost_type_id
1760 and organization_id = mp.cost_organization_id) ; --3116778
1761
1762 lCnt := sql%rowcount;
1763 -- Bug Fix for 4867460
1764 If lCnt > 0 Then
1765 l_cost_data_exists := 'Y';
1766 End if;
1767 IF PG_DEBUG <> 0 THEN
1768 oe_debug_pub.add('populate_buy_cost: ' || 'rows inserted into bom_lists::'||to_char(lCnt), 2);
1769 END IF;
1770
1771 << endloop>>
1772 null;
1773
1774 END LOOP; /* end of array loop */
1775
1776
1777 -- commented if condition
1778
1779
1780
1781
1782
1783 --
1784
1785
1786 -- bug 4177423 no need of end if as if stmt has been commented end if; /* end of p_cfg_itm_tbl.COUNT */
1787
1788 --
1789 -- call SC Cost Rollup API
1790 --
1791 lStmtNumber := 60;
1792 -- Bug Fix 4867460
1793 If l_cost_data_exists = 'Y' Then
1794
1795 If PG_DEBUG <> 0 Then
1796 oe_debug_pub.add('Populate_buy_cost: Calling Supply Chain Rollup program',5);
1797 End if;
1798
1799 /* Commented as part of Bugfix 7246036
1800 CSTPSCEX.Supply_Chain_Rollup(
1801 lRollupId, -- i_rollup_id
1802 1, -- i_explosion_level
1803 NULL, -- i_report_levels
1804 lMrpAssignmentSet, -- i_assignment_set_id
1805 lConversionType, -- i_conversion_type
1806 -- 1, -- i_cost_type_id
1807 v_cto_cost_type_id , -- i_cost_type_id
1808 v_buy_cost_type_id, -- i_buy_cost_type_id
1809 SYSDATE, -- i_effective_date
1810 1, -- exclude unimplemented ECOs, implemented only
1811 1, -- BOM items only, exclude ENG items
1812 '', -- i_alt_bom_desg
1813 '', -- i_alt_rtg_desg
1814 2, -- i_lock_flag
1815 gUserId, -- i_user_id
1816 gLoginId, -- i_login_id
1817 NULL, -- i_request_id
1818 NULL, -- i_prog_id??
1819 702, -- i_prog_appl_id
1820 lErrorCode, -- o_error_code
1821 lErrorMsg); -- o_error_msg
1822 */
1823
1824 CSTPSCEX.Supply_Chain_Rollup(
1825 i_rollup_id => lRollupId,
1826 i_explosion_levels => 1,
1827 i_report_levels => NULL,
1828 i_assignment_set_id => lMrpAssignmentSet,
1829 i_conversion_type => lConversionType,
1830 i_cost_type_id => v_cto_cost_type_id,
1831 i_buy_cost_type_id => v_buy_cost_type_id,
1832 i_effective_date => SYSDATE,
1833 i_exclude_unimpl_eco => 1,
1834 i_exclude_eng => 1,
1835 i_alt_bom_desg => '',
1836 i_alt_rtg_desg => '',
1837 i_lock_flag => 2,
1838 i_user_id => gUserId,
1839 i_login_id => gLoginId,
1840 i_request_id => NULL,
1841 i_prog_id => NULL,
1842 i_prog_appl_id => 702,
1843 o_error_code => lErrorCode,
1844 o_error_msg => lErrorMsg,
1845 i_buy_cost_detail => 1 ); --Bugfix 7246036: Passing preserve buy cost details parameter as Yes.
1846
1847 IF lErrorCode <> 0 THEN
1848 IF PG_DEBUG <> 0 THEN
1849 oe_debug_pub.add('populate_buy_cost: ' || 'SC Rollup API returned with error::'||lErrorMsg, 1);
1850 END IF;
1851 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1852 ELSE
1853 IF PG_DEBUG <> 0 THEN
1854 oe_debug_pub.add('populate_buy_cost: ' || 'SC Rollup API returned with success', 1);
1855 END IF;
1856
1857
1858 END IF;
1859
1860 Else
1861
1862 If PG_DEBUG <> 0 Then
1863 oe_debug_pub.add('Populate_buy_cost: No cost data exists. No need to call Supply chain Cost rollup API',5);
1864 End if;
1865
1866 End if;
1867 /*
1868 **
1869 ** Costs need to be copied from simulation cost to frozen cost in case of
1870 ** standard costing organization
1871 **
1872 */
1873
1874
1875
1876
1877 lStmtNumber := 80;
1878 /* update Frozen cost with CTO Cost in case of standard costing organizations */
1879
1880
1881 for i in p_cfg_itm_tbl.FIRST..p_cfg_itm_tbl.LAST
1882
1883 LOOP
1884
1885 --Bugfix 12957444: Skipping the configs that are in error cache.
1886 if CTO_COST_ROLLUP_CONC_PK.g_error_cache.exists(p_cfg_itm_tbl(i).cfg_item_id || '-' || p_cfg_itm_tbl(i).cfg_org_id)
1887 then
1888 IF PG_DEBUG <> 0 THEN
1889 oe_debug_pub.add('Config::' || p_cfg_itm_tbl(i).cfg_item_id || ' in org::' || p_cfg_itm_tbl(i).cfg_org_id ||
1890 'is in error. Skipping it.');
1891 END IF;
1892
1893 goto endloop2;
1894 end if;
1895
1896 open c_frozen_cost_update ( v_cto_cost_type_id,
1897 p_cfg_itm_tbl(i).cfg_item_id,
1898 p_cfg_itm_tbl(i).cfg_org_id) ;
1899
1900 fetch c_frozen_cost_update into c_config_item_id
1901 , c_organization_id ;
1902
1903 -- bug 13106676
1904 --
1905 select mp1.primary_cost_method into v_primary_cost_method
1906 from mtl_parameters mp1
1907 where mp1.organization_id = c_organization_id ;
1908
1909 IF PG_DEBUG <> 0 THEN
1910 oe_debug_pub.add('populate_buy_cost: ' || 'v_primary_cost_method = '||v_primary_cost_method);
1911 END IF;
1912 -- If the org is Standard cost then check if the Item is transacted
1913 --
1914 IF (v_primary_cost_method = 1) THEN
1915 v_is_cst_updatable := check_ct_updateable(c_config_item_id, c_organization_id, 1);
1916 ELSE
1917 v_is_cst_updatable := TRUE;
1918 END IF;
1919
1920 IF PG_DEBUG <> 0 THEN
1921 IF (v_is_cst_updatable = TRUE) THEN
1922 oe_debug_pub.add('populate_buy_cost: ' || 'v_is_cst_updatable = TRUE' );
1923 ELSE
1924 oe_debug_pub.add('populate_buy_cost: ' || 'v_is_cst_updatable = FALSE' );
1925 END IF;
1926 END IF;
1927
1928 IF (v_is_cst_updatable = TRUE) THEN
1929 IF PG_DEBUG <> 0 THEN
1930 oe_debug_pub.add('populate_buy_cost: ' || 'v_is_cst_updatable is true. So frozen cost is updatable');
1931 END IF;
1932
1933 --exit when c_frozen_cost_update%notfound ; Bugfix 6038548
1934 IF c_frozen_cost_update%found THEN --Bugfix 6038548
1935
1936 lStmtNumber := 95;
1937 --Bugfix 6717614
1938 l_cost_update_required := cost_update_required(c_config_item_id,
1939 c_organization_id,
1940 v_cto_cost_type_id);
1941
1942 IF (l_cost_update_required = 'Y') THEN
1943
1944 IF PG_DEBUG <> 0 THEN
1945 oe_debug_pub.add('***************** going to do frozen cost update for config ' || c_config_item_id
1946 || ' in org ' || c_organization_id , 1);
1947 END IF;
1948
1949
1950 delete from cst_item_cost_details
1951 where inventory_item_id = c_config_item_id
1952 and organization_id = c_organization_id
1953 and cost_type_id = 1 ;
1954
1955 IF PG_DEBUG <> 0 THEN
1956 oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
1957 END IF;
1958
1959
1960 delete from cst_item_costs
1961 where inventory_item_id = c_config_item_id
1962 and organization_id = c_organization_id
1963 and cost_type_id = 1 ;
1964
1965
1966 IF PG_DEBUG <> 0 THEN
1967 oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
1968 END IF;
1969
1970
1971 copy_ctocost_to_frozen ( c_config_item_id
1972 , c_organization_id
1973 , v_cto_cost_type_id ) ;
1974
1975 END IF;
1976 END IF; --c_frozen_cost_update%found. Bugfix 6038548
1977 ELSE
1978 IF PG_DEBUG <> 0 THEN
1979 oe_debug_pub.add('populate_buy_cost: ' || 'v_is_cst_updatable is false. So frozen cost is not updatable. Updating Cto cost with frozen cost');
1980 END IF;
1981 -- Update CTO cost with Frozen cost
1982 --
1983 CTO_UTILITY_PK.copy_cost(v_primary_cost_method
1984 , v_cto_cost_type_id
1985 , c_config_item_id
1986 , c_organization_id
1987 );
1988 END IF; -- IF check_ct_updateable = TRUE
1989
1990 close c_frozen_cost_update ;
1991
1992 v_primary_cost_method := NULL;
1993 v_is_cst_updatable := TRUE;
1994
1995 <<endloop2>>
1996 null;
1997
1998 END LOOP;
1999
2000 --Bugfix 12957444: Adding some debugging.
2001 IF PG_DEBUG <> 0 THEN
2002 oe_debug_pub.add('populate_buy_cost: ' || 'At end of cost rollup', 2);
2003 oe_debug_pub.add('Cost_Roll_Up_ML:' || 'No. of configs in error::' || CTO_COST_ROLLUP_CONC_PK.g_error_cache.count);
2004 oe_debug_pub.add('Cost_Roll_Up_ML:' || '===========Printing configs in error============');
2005 l_error_cache_val := CTO_COST_ROLLUP_CONC_PK.g_error_cache.first;
2006 while l_error_cache_val is not null loop
2007 oe_debug_pub.add('Config-Org::' || l_error_cache_val);
2008 l_error_cache_val := CTO_COST_ROLLUP_CONC_PK.g_error_cache.next(l_error_cache_val);
2009 end loop;
2010 oe_debug_pub.add('Cost_Roll_Up_ML:' || '===========End printing configs in error============');
2011 END IF;
2012
2013 if CTO_COST_ROLLUP_CONC_PK.g_error_cache.count <> 0 then
2014 l_ret_val := 0;
2015 else
2016 l_ret_val := 1;
2017 end if;
2018
2019 --return(1);
2020 return(l_ret_val);
2021
2022 EXCEPTION
2023 WHEN NO_DATA_FOUND THEN
2024 oe_debug_pub.add ('populate_buy_cost: ' || 'cost_rollup::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
2025 return(0);
2026
2027 WHEN CTO_MRP_ASSIGNMENT_SET THEN
2028 oe_debug_pub.add ('populate_buy_cost: ' || 'cost_rollup::mrp assgn set is null'||to_char(lStmtNumber)||sqlerrm, 1);
2029
2030 cto_msg_pub.count_and_get
2031 ( p_msg_count => x_msg_count
2032 , p_msg_data => x_msg_data
2033 );
2034
2035 return(0);
2036
2037
2038 WHEN FND_API.G_EXC_ERROR THEN
2039 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
2040
2041 cto_msg_pub.count_and_get
2042 ( p_msg_count => x_msg_count
2043 , p_msg_data => x_msg_data
2044 );
2045
2046
2047 return(0);
2048
2049
2050 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2051 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
2052
2053 cto_msg_pub.count_and_get
2054 ( p_msg_count => x_msg_count
2055 , p_msg_data => x_msg_data
2056 );
2057
2058
2059 return(-1);
2060
2061 WHEN OTHERS THEN
2062 oe_debug_pub.add ('Cost_Roll_Up_ML ' || 'cost_rollup::other excpn ::'||to_char(lStmtNumber)||sqlerrm, 1);
2063
2064 cto_msg_pub.count_and_get
2065 ( p_msg_count => x_msg_count
2066 , p_msg_data => x_msg_data
2067 );
2068
2069
2070 return(0);
2071
2072 END Cost_Roll_Up_ML;
2073 /* This procedure has been maintained in patchset J to account for configuration item
2074 ** workflow references to patchset I
2075 */
2076 FUNCTION Cost_Rollup_ML(pTopAtoLineId in number,
2077 x_msg_count out NOCOPY number,
2078 x_msg_data out NOCOPY varchar2)
2079 RETURN integer
2080 IS
2081
2082 lMrpAssignmentSet number;
2083 lRollupId number;
2084 gUserId number;
2085 gLoginId number;
2086 lMsgCount number;
2087 lMsgData varchar2(2000);
2088 lStmtNumber number;
2089 lErrorCode number;
2090 lErrorMsg varchar2(2000);
2091 lCnt number;
2092 lConversionType varchar2(30);
2093 lBuyCostType varchar2(30);
2094
2095 CTO_MRP_ASSIGNMENT_SET exception;
2096
2097 v_cto_cost_type_id cst_cost_types.cost_type_id%type ;
2098 v_buy_cost_type_id cst_cost_types.cost_type_id%type ;
2099 v_buy_cost number ;
2100 c_line_id bom_cto_src_orgs.line_id%type ;
2101 c_model_item_id bom_cto_src_orgs.model_item_id%type ;
2102 c_config_item_id bom_cto_src_orgs.model_item_id%type ;
2103 c_cost_org_id mtl_parameters.cost_organization_id%type ; -- 3116778
2104 c_organization_id bom_cto_src_orgs.organization_id%type ;
2105 c_po_valid_org financials_system_params_all.inventory_organization_id%type ;
2106 c_oper_unit inv_organization_info_v.operating_unit%type ;
2107
2108 v_buy_item_cost mtl_system_items.list_price_per_unit%type ;
2109
2110
2111
2112 cursor c_buy_items
2113 is
2114 select bcso.line_id
2115 , bcso.model_item_id
2116 , bcso.config_item_id
2117 -- 3116778 , bcso.rcv_org_id
2118 , mp2.cost_organization_id -- 3116778
2119 , nvl(fsp.inventory_organization_id,0) po_valid_org
2120 , nvl(org.operating_unit,0) oper_unit
2121 from bom_cto_src_orgs bcso
2122 , bom_cto_order_lines bcol
2123 , financials_system_params_all fsp
2124 , inv_organization_info_v org
2125 , cst_item_costs cic
2126 , mtl_parameters mp1 /* master organization */
2127 , mtl_parameters mp2
2128 where bcso.top_model_line_id = pTopAtoLineId
2129 and bcol.line_id = bcso.line_id
2130 and bcso.cost_rollup = 'Y'
2131 and ( ( bcso.organization_type in (3,5) and bcol.config_creation in ( 1,2) )
2132 OR bcol.config_creation = 3 )
2133 and org.organization_id = bcso.organization_id
2134 and nvl(org.operating_unit,-1) = nvl(fsp.org_id,-1)
2135 and cic.inventory_item_id = bcso.config_item_id
2136 and cic.organization_id = mp2.cost_organization_id -- 3116778
2137 and mp2.organization_id = bcso.organization_id -- 3116778
2138 and mp2.cost_organization_id = mp1.organization_id -- 3116778
2139 and cic.cost_type_id in ( mp1.primary_cost_method, mp1.avg_rates_cost_type_id) ;
2140
2141 /* 3116778 */
2142 /*
2143 BUG 3931290
2144 The original cursor c_frozen_cost_update was using a union (organization_id , rcv_org_id )
2145 to determine organizations where standard cost update needs to be performed.
2146 The organizations where standard cost update will be performed will now be determined using organization_id.
2147 This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2148 A distinct clause has been added to the statement.
2149 */
2150 cursor c_frozen_cost_update(c_cto_cost_type_id number )
2151 is
2152 select distinct -- bug 3931290
2153 bcso.config_item_id
2154 , mp1.cost_organization_id rollup_org_id
2155 from bom_cto_src_orgs bcso
2156 , mtl_parameters mp1
2157 , mtl_parameters mp2
2158 , cst_item_costs cic
2159 where bcso.top_model_line_id = pTopAtoLineId
2160 and bcso.cost_rollup = 'Y'
2161 and bcso.organization_id = mp2.organization_id
2162 and mp2.cost_organization_id = mp1.organization_id
2163 and mp1.primary_cost_method = 1
2164 and cic.inventory_item_id = bcso.config_item_id
2165 and cic.organization_id = mp1.organization_id
2166 and cic.cost_type_id = c_cto_cost_type_id
2167 and ( cic.based_on_rollup_flag = 1 or bcso.organization_type = 3 ); /* bug 3931290 */
2168
2169
2170
2171 -- debug 3116778
2172 cursor cst_cur (xRollupId number) is
2173 select inventory_item_id,organization_id
2174 from cst_sc_lists
2175 where rollup_id =xRollupId;
2176 d_item_id number;
2177 d_org_id number;
2178
2179 cursor c1_cst (c_inventory_item_id number) is
2180 select cst.inventory_item_id, cst.organization_id, cst.cost_type_id,
2181 cst.item_cost , cst.material_cost, cst.material_overhead_cost,
2182 cicd.cost_element_id ,cicd.item_cost cicd_item_cost
2183 from cst_item_costs cst, cst_item_cost_details cicd
2184 where cst.inventory_item_id = cicd.inventory_item_id
2185 and cst.organization_id = cicd.organization_id
2186 and cst.cost_type_id = cicd.cost_type_id
2187 and cst.inventory_item_id = c_inventory_item_id ;
2188
2189
2190 c1_config_item_id number ;
2191
2192 v_cur_c1_cst c1_cst%rowtype ;
2193
2194 -- debug 3116778
2195
2196
2197 v_item_cost cst_item_costs.item_cost%type ;
2198 v_material_cost cst_item_costs.material_cost%type ;
2199 v_material_overhead_cost cst_item_costs.material_overhead_cost%type ;
2200 v_resource_cost cst_item_costs.resource_cost%type ;
2201 v_outside_processing_cost cst_item_costs.outside_processing_cost%type ;
2202 v_overhead_cost cst_item_costs.overhead_cost%type ;
2203
2204 v_cost_updateable BOOLEAN := false ;
2205
2206
2207 v_group_id bom_explosion_temp.group_id%type ;
2208 /*
2209 x_msg_count number ;
2210 x_msg_data varchar2(200) ;
2211 */
2212 x_return_status varchar2(200) ;
2213
2214
2215 l_token CTO_MSG_PUB.token_tbl;
2216
2217 v_po_validation_org varchar2(2000) ;
2218 v_org varchar2(2000) ;
2219 v_model_name varchar2(2000) ;
2220
2221 v_cto_cost_type_name cst_cost_types.cost_type%type;
2222 cst_sc_list_count number; --bug 4867460
2223
2224 l_cost_update_required varchar2(1) := 'N'; --bug 6717614
2225
2226 -- bug 13106676
2227 --
2228 v_primary_cost_method mtl_parameters.primary_cost_method%type := null ;
2229 v_is_cst_updatable BOOLEAN := TRUE;
2230 BEGIN
2231 lStmtNumber := 10;
2232
2233 gUserId := nvl(fnd_global.user_id, -1);
2234 gLoginId := nvl(fnd_global.login_id, -1);
2235 IF PG_DEBUG <> 0 THEN
2236 oe_debug_pub.add('Cost_Rollup_ML: ' || 'gUserId::'||to_char(gUserId)
2237 ||'::gLoginId::'||to_char(gLoginId), 2);
2238
2239 oe_debug_pub.add('Cost_Rollup_ML: ' || 'top line ::'|| pTopAtoLineId , 2 );
2240 END IF;
2241
2242
2243 -- changed sequence as per bugfix 3239456
2244 /*select bom_lists_s.nextval
2245 into lRollupId from dual;*/
2246
2247 select cst_lists_s.nextval
2248 into lRollupId from dual;
2249
2250
2251
2252 IF PG_DEBUG <> 0 THEN
2253 oe_debug_pub.add('Cost_Rollup_ML: ' || 'lRollupId::'||to_char(lRollupId), 2);
2254 END IF;
2255
2256 lStmtNumber := 20;
2257 lMrpAssignmentSet := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
2258
2259 IF lMrpAssignmentSet is null THEN
2260 IF PG_DEBUG <> 0 THEN
2261 oe_debug_pub.add('Cost_Rollup_ML: '
2262 || 'Default assignment set is null', 1);
2263 END IF;
2264
2265
2266 --FND_MESSAGE.set_name('BOM','CTO_MRP_ASSIGNMENT_SET');
2267 --raise CTO_MRP_ASSIGNMENT_SET;
2268 ELSE
2269 IF PG_DEBUG <> 0 THEN
2270 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Default assignment set is '
2271 ||to_char(lMrpAssignmentSet), 2);
2272 END IF;
2273 END IF;
2274
2275 --
2276 -- Getting the Currency Conversion Type from profile
2277 -- "INV:Inter-organization currency conversion"
2278 --
2279 lStmtNumber := 25;
2280 lConversionType := FND_PROFILE.VALUE('CURRENCY_CONVERSION_TYPE');
2281
2282
2283 IF PG_DEBUG <> 0 THEN
2284 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Currency Conversion Type ::'
2285 ||lConversionType, 2);
2286 END IF;
2287
2288 lStmtNumber := 27;
2289 lBuyCostType := FND_PROFILE.VALUE('CTO_BUY_COST_TYPE');
2290 IF PG_DEBUG <> 0 THEN
2291 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Buy Cost Type ::'|| lBuyCostType , 2);
2292 END IF;
2293
2294
2295 lStmtNumber := 28;
2296
2297 if( lBuyCostType is not null ) then
2298 begin
2299 select cost_type_id into v_buy_cost_type_id
2300 from cst_cost_types
2301 where cost_type = lBuyCostType ;
2302
2303 IF PG_DEBUG <> 0 THEN
2304 oe_debug_pub.add('Cost_Rollup_ML: ' || 'Buy Cost Type id ::'
2305 || v_buy_cost_type_id , 2);
2306 END IF;
2307
2308 exception
2309 when no_data_found then
2310
2311 cto_msg_pub.cto_message('BOM','CTO_BUY_COST_NOT_FOUND');
2312 raise FND_API.G_EXC_ERROR;
2313
2314 when others then
2315
2316 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2317
2318
2319 end;
2320
2321
2322 end if ;
2323
2324
2325
2326 lStmtNumber := 29;
2327
2328 IF PG_DEBUG <> 0 THEN
2329 oe_debug_pub.add('Cost_Rollup_ML: '
2330 || ' opening buy cost cursor for bcso ' , 2);
2331 END IF;
2332
2333 if( lBuyCostType is not null ) then
2334
2335
2336 open c_buy_items ;
2337 loop
2338 fetch c_buy_items into c_line_id
2339 , c_model_item_id
2340 , c_config_item_id
2341 , c_cost_org_id -- 3116778
2342 , c_po_valid_org
2343 , c_oper_unit ;
2344
2345
2346 exit when c_buy_items%notfound ;
2347
2348 IF PG_DEBUG <> 0 THEN
2349 oe_debug_pub.add('Cost_Rollup_ML: ' ||
2350 ' fetched line ' || c_line_id ||
2351 ' model ' || c_model_item_id ||
2352 ' config ' || c_config_item_id ||
2353 ' cost org ' || c_cost_org_id || --3116778
2354 ' po val ' || c_po_valid_org ||
2355 ' oper ' || c_oper_unit , 2);
2356 END IF;
2357
2358 lStmtNumber := 30;
2359
2360 /* Check whether buy cost exists for the item */
2361 begin
2362 select item_cost into v_buy_cost
2363 from cst_item_costs
2364 where cost_type_id = v_buy_cost_type_id
2365 and organization_id = c_cost_org_id -- 3116778
2366 and inventory_item_id = c_config_item_id ;
2367
2368 exception
2369 when no_data_found then
2370 v_buy_cost := null ;
2371
2372 IF PG_DEBUG <> 0 THEN
2373 oe_debug_pub.add('no buy cost exists for item ' || c_config_item_id
2374 || ' in org ' || c_cost_org_id , 2); -- 3116778
2375 END IF;
2376
2377
2378 when others then
2379
2380
2381 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2382
2383 end ;
2384
2385
2386
2387 if( v_buy_cost is null ) then
2388
2389 lStmtNumber := 32;
2390
2391
2392
2393 BEGIN
2394
2395
2396 select nvl(list_price_per_unit,0) into v_buy_item_cost
2397 from mtl_system_items
2398 where organization_id = c_po_valid_org and inventory_item_id = c_config_item_id ;
2399
2400
2401 EXCEPTION
2402 when no_data_found then
2403
2404
2405 IF PG_DEBUG <> 0 THEN
2406 oe_debug_pub.add('create_config_item_wf: ' || 'Item Not Enabled in PO Validation Org ' || c_po_valid_org , 1);
2407 oe_debug_pub.add('create_config_item_wf: ' || 'PO Validation Org for Org ' || c_cost_org_id , 1);
2408 END IF;
2409
2410
2411 -- cto_msg_pub.cto_message('BOM', l_xmsgdata);
2412
2413 l_token(1).token_name := 'POV_ORG';
2414
2415
2416 select organization_name into v_po_validation_org
2417 from inv_organization_name_v
2418 where organization_id = c_po_valid_org ;
2419
2420
2421 l_token(1).token_value := v_po_validation_org ;
2422
2423
2424 l_token(2).token_name := 'ORG';
2425
2426 select organization_name into v_org
2427 from inv_organization_name_v
2428 where organization_id = c_cost_org_id ;
2429
2430
2431 l_token(2).token_value := v_org ;
2432
2433 l_token(3).token_name := 'MODEL_NAME';
2434
2435 select concatenated_segments into v_model_name
2436 from mtl_system_items_kfv
2437 where inventory_item_id = ( select base_item_id
2438 from mtl_system_items where inventory_item_id = c_config_item_id and rownum = 1 )
2439 and rownum = 1 ;
2440
2441 l_token(3).token_value := v_model_name ;
2442
2443
2444
2445 cto_msg_pub.cto_message('BOM', 'CTO_ITEM_NOT_ENABLED_IN_POV', l_token );
2446
2447 raise FND_API.G_EXC_ERROR;
2448
2449
2450
2451
2452 when others then
2453
2454
2455 raise ;
2456
2457 END ;
2458
2459
2460
2461
2462 lStmtNumber := 35;
2463
2464 IF PG_DEBUG <> 0 THEN
2465 oe_debug_pub.add('going to populate buy cost for item ' || c_config_item_id
2466 || ' for $ ' || v_buy_item_cost , 2);
2467 END IF;
2468
2469 populate_buy_cost( c_line_id
2470 , c_config_item_id
2471 , c_cost_org_id -- 3116778
2472 , v_buy_cost_type_id
2473 , v_buy_item_cost ) ;
2474
2475
2476 else
2477
2478 IF PG_DEBUG <> 0 THEN
2479 oe_debug_pub.add('buy cost exists for item ' || c_config_item_id
2480 || ' for $ ' || v_buy_cost || 'in org ' || c_cost_org_id, 2); -- 3116778
2481 END IF;
2482
2483 end if ;
2484
2485
2486 end loop;
2487
2488
2489 close c_buy_items ;
2490
2491
2492 end if; /* check if buy cost is not null */
2493
2494
2495
2496
2497
2498
2499
2500 --
2501 -- insert into CST_SC_LISTS rows for all config items in all possible
2502 -- src orgs, where ->
2503 -- 1. cost has not been calculated yet (cost_rollup = Y)
2504 -- 2. costing_enabled_flag = Y
2505 -- 3. primary_cost_method = 1 (standard)
2506 --
2507 IF PG_DEBUG <> 0 THEN
2508 oe_debug_pub.add('Cost_Rollup_ML: ' || 'inserting into cst_sc_lists', 2);
2509 END IF;
2510
2511
2512 /*
2513 BUG 3931290
2514 The statement insert into cst_sc_lists was using a union (organization_id, rcv_org_id)
2515 to determine organizations where cost rollup needs to be performed.
2516 The organization where cost rollup will be performed will now be determined using organization_id.
2517 This will eliminate the 2nd sql statement (rcv_org_id) in the union and there will be no need for a union.
2518 */
2519
2520 lStmtNumber := 38;
2521 insert into cst_sc_lists(
2522 rollup_id,
2523 inventory_item_id,
2524 organization_id,
2525 last_update_date,
2526 last_updated_by,
2527 creation_date,
2528 created_by
2529 )
2530 select distinct
2531 lRollupId,
2532 bcso.config_item_id,
2533 mp.cost_organization_id,
2534 sysdate,
2535 gUserId,
2536 sysdate,
2537 gUserId
2538 from bom_cto_src_orgs bcso,
2539 mtl_system_items msi,
2540 mtl_parameters mp ,
2541 cst_item_costs cic
2542 where bcso.top_model_line_id = pTopAtoLineId
2543 and bcso.cost_rollup = 'Y'
2544 and bcso.config_item_id = msi.inventory_item_id
2545 and bcso.organization_id = msi.organization_id
2546 and msi.costing_enabled_flag = 'Y'
2547 and mp.organization_id = bcso.organization_id
2548 and cic.inventory_item_id = msi.inventory_item_id
2549 -- 3116778 and cic.organization_id = msi.organization_id
2550 and cic.organization_id = mp.cost_organization_id -- 3116778
2551 -- 3116778 and cic.based_on_rollup_flag = 1
2552 and (cic.based_on_rollup_flag = 1 or bcso.organization_type = 3) -- 3116778
2553 and (
2554 ( ( mp.primary_cost_method = 1 )
2555 and cic.cost_type_id = 1
2556 )
2557 OR
2558 ( ( mp.primary_cost_method = 2 )
2559 and cic.cost_type_id = 2
2560 )
2561 OR
2562 ( ( mp.primary_cost_method = 6 )
2563 and cic.cost_type_id = 6
2564 )
2565 OR
2566 ( ( mp.primary_cost_method = 5 )
2567 and cic.cost_type_id = 5
2568 )
2569 )
2570 and NOT EXISTS
2571 (select NULL
2572 from cst_sc_lists
2573 where rollup_id = lRollupId
2574 and inventory_item_id = bcso.config_item_id
2575 and organization_id = mp.cost_organization_id ) ;
2576
2577
2578
2579 -- debug 3116778
2580 lCnt := sql%rowcount;
2581 IF PG_DEBUG <> 0 THEN
2582 oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2583 END IF;
2584 cst_sc_list_count := lCnt; -- Bug# 4867460
2585 open cst_cur ( lRollupId ) ;
2586
2587 loop
2588
2589 fetch cst_cur into d_item_id,d_org_id;
2590
2591 exit when cst_cur%notfound ;
2592
2593 IF PG_DEBUG <> 0 THEN
2594 oe_debug_pub.add('Cst list has item : ' ||d_item_id|| ' in org ' || d_org_id,1);
2595 END IF;
2596
2597 end loop;
2598 close cst_cur;
2599 -- debug 3116778
2600
2601
2602
2603 lCnt := sql%rowcount;
2604 IF PG_DEBUG <> 0 THEN
2605 oe_debug_pub.add('Cost_Rollup_ML: ' || 'rows inserted into cst_sc_lists::'||to_char(lCnt), 2);
2606 END IF;
2607
2608 --
2609 -- call SC Cost Rollup API
2610 --
2611
2612 lStmtNumber := 40;
2613
2614
2615 /* commented for bug 4057651
2616 begin
2617
2618 select cost_type_id into v_cto_cost_type_id
2619 from cst_cost_types
2620 where cost_type = 'CTO' ;
2621
2622
2623 if( lBuyCostType is null ) then
2624 v_buy_cost_type_id := v_cto_cost_type_id ;
2625 end if ;
2626
2627
2628 exception
2629 when no_data_found then
2630
2631 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
2632 raise FND_API.G_EXC_ERROR;
2633
2634 when others then
2635
2636 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2637
2638
2639 end;
2640
2641
2642 commented for bug 4057651 */
2643
2644
2645
2646
2647
2648
2649
2650 /* begin bugfix 4057651, default CTO cost type id = 7 if it does not exist */
2651 begin
2652
2653 select cost_type_id into v_cto_cost_type_id
2654 from cst_cost_types
2655 where cost_type = 'CTO' ;
2656
2657
2658 exception
2659 when no_data_found then
2660
2661 IF PG_DEBUG <> 0 THEN
2662 oe_debug_pub.add('Create_Item: ' || ' no_data_found error CTO cost type id does not exist',2);
2663 oe_debug_pub.add('Create_Item: ' || ' defaulting CTO cost type id = 7 ',2);
2664 END IF;
2665
2666 v_cto_cost_type_id := 7 ;
2667
2668 begin
2669 select cost_type into v_cto_cost_type_name
2670 from cst_cost_types
2671 where cost_type_id = v_cto_cost_type_id ;
2672
2673 IF PG_DEBUG <> 0 THEN
2674 oe_debug_pub.add('Create_Item: ' || ' cost type id = ' || v_cto_cost_type_id ||
2675 ' has cost_type = ' || v_cto_cost_type_name ,2);
2676 END IF;
2677 exception
2678 when no_data_found then
2679 IF PG_DEBUG <> 0 THEN
2680 oe_debug_pub.add('Create_Item: ' || ' no_data_found error for cost type id = 7 ',2);
2681 END IF;
2682 cto_msg_pub.cto_message('BOM','CTO_COST_NOT_FOUND');
2683 raise FND_API.G_EXC_ERROR;
2684 when others then
2685
2686 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2687 end ;
2688
2689 when others then
2690 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2691 end ;
2692
2693 if( lBuyCostType is null ) then
2694 v_buy_cost_type_id := v_cto_cost_type_id ;
2695 end if ;
2696
2697 /* end bugfix 4057651, default CTO cost type id = 7 if it does not exist */
2698
2699
2700
2701 -- debug 3116778
2702
2703 select config_item_id
2704 into c1_config_item_id
2705 from bom_cto_order_lines
2706 where line_id = pTopatolineid ;
2707
2708 open c1_cst ( c1_config_item_id ) ;
2709
2710 IF PG_DEBUG <> 0 THEN
2711 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2712 END IF;
2713
2714 loop
2715
2716 fetch c1_cst into v_cur_c1_cst ;
2717
2718 exit when c1_cst%notfound ;
2719
2720 IF PG_DEBUG <> 0 THEN
2721 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2722 oe_debug_pub.add( 'iid ' || v_cur_c1_cst.inventory_item_id ||
2723 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
2724 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
2725 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
2726 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
2727 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost)||
2728 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
2729 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
2730 END IF;
2731
2732
2733 end loop ;
2734
2735 IF PG_DEBUG <> 0 THEN
2736 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
2737 END IF;
2738
2739 close c1_cst ;
2740
2741 -- debug 3116778
2742
2743
2744
2745
2746
2747
2748 lStmtNumber := 60;
2749 if cst_sc_list_count > 0 then --bug4867460: call the costing api only if records are inserted
2750 -- in cst_sc_lists. This is to improve performance.
2751 IF PG_DEBUG <> 0 THEN
2752 oe_debug_pub.add('******************** calling costing API ****************** ', 2);
2753 oe_debug_pub.add(' Params assig set ' || lMrpAssignmentSet ||
2754 ' cto cost id ' || v_cto_cost_type_id ||
2755 ' buy cost id ' || v_buy_cost_type_id , 2);
2756
2757 END IF;
2758
2759 /* Commented as part of Bugfix 7246036
2760 CSTPSCEX.Supply_Chain_Rollup(
2761 lRollupId, -- i_rollup_id
2762 1, -- i_explosion_level
2763 NULL, -- i_report_levels
2764 lMrpAssignmentSet, -- i_assignment_set_id
2765 lConversionType, -- i_conversion_type
2766 -- 1, -- i_cost_type_id
2767 v_cto_cost_type_id , -- i_cost_type_id
2768 v_buy_cost_type_id, -- i_buy_cost_type_id
2769 SYSDATE, -- i_effective_date
2770 1, -- exclude unimplemented ECOs, implemented only
2771 1, -- BOM items only, exclude ENG items
2772 '', -- i_alt_bom_desg
2773 '', -- i_alt_rtg_desg
2774 2, -- i_lock_flag
2775 gUserId, -- i_user_id
2776 gLoginId, -- i_login_id
2777 NULL, -- i_request_id
2778 NULL, -- i_prog_id??
2779 702, -- i_prog_appl_id
2780 lErrorCode, -- o_error_code
2781 lErrorMsg); -- o_error_msg
2782 */
2783
2784 CSTPSCEX.Supply_Chain_Rollup(
2785 i_rollup_id => lRollupId,
2786 i_explosion_levels => 1,
2787 i_report_levels => NULL,
2788 i_assignment_set_id => lMrpAssignmentSet,
2789 i_conversion_type => lConversionType,
2790 i_cost_type_id => v_cto_cost_type_id,
2791 i_buy_cost_type_id => v_buy_cost_type_id,
2792 i_effective_date => SYSDATE,
2793 i_exclude_unimpl_eco => 1,
2794 i_exclude_eng => 1,
2795 i_alt_bom_desg => '',
2796 i_alt_rtg_desg => '',
2797 i_lock_flag => 2,
2798 i_user_id => gUserId,
2799 i_login_id => gLoginId,
2800 i_request_id => NULL,
2801 i_prog_id => NULL,
2802 i_prog_appl_id => 702,
2803 o_error_code => lErrorCode,
2804 o_error_msg => lErrorMsg,
2805 i_buy_cost_detail => 1 ); --Bugfix 7246036: Passing preserve buy cost details parameter as Yes.
2806
2807 IF lErrorCode <> 0 THEN
2808 IF PG_DEBUG <> 0 THEN
2809 oe_debug_pub.add('************** Cost_Rollup_ML: ' || 'SC Rollup API returned with error::'||lErrorMsg, 1);
2810 END IF;
2811 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2812 ELSE
2813 IF PG_DEBUG <> 0 THEN
2814 oe_debug_pub.add('************** Cost_Rollup_ML: ' || 'SC Rollup API returned with success', 1);
2815 END IF;
2816
2817
2818 END IF;
2819 Else
2820 IF PG_DEBUG <> 0 Then
2821 oe_debug_pub.add('No cost data Exists. No need to call supply Chain cost rollup API',5);
2822 End if;
2823 End If; -- Bug fix 4867460
2824
2825
2826 -- debug 3116778
2827
2828 open c1_cst ( c1_config_item_id ) ;
2829
2830 IF PG_DEBUG <> 0 THEN
2831 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2832 END IF;
2833
2834 loop
2835
2836 fetch c1_cst into v_cur_c1_cst ;
2837
2838 exit when c1_cst%notfound ;
2839
2840 IF PG_DEBUG <> 0 THEN
2841 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2842 oe_debug_pub.add( 'iid ' || v_cur_c1_cst.inventory_item_id ||
2843 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
2844 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
2845 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
2846 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
2847 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost) ||
2848 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
2849 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
2850 END IF;
2851
2852 end loop ;
2853
2854 IF PG_DEBUG <> 0 THEN
2855 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
2856 END IF;
2857
2858 close c1_cst ;
2859
2860 -- debug 3116778
2861
2862
2863 /*
2864 **
2865 ** Costs need to be copied from simulation cost to frozen cost in case of
2866 ** standard costing organization
2867 **
2868 */
2869
2870
2871
2872
2873 lStmtNumber := 80;
2874 --Bugfix 6717614
2875 if cst_sc_list_count = 0 then
2876
2877 IF PG_DEBUG <> 0 THEN
2878 oe_debug_pub.add('***No need to do frozen cost update as no rollup has been done' , 1);
2879 END IF;
2880
2881 else
2882 /*update Frozen cost with CTO Cost in case of standard costing organizations */
2883 lStmtNumber := 90;
2884
2885 open c_frozen_cost_update ( v_cto_cost_type_id ) ;
2886
2887 loop
2888
2889 fetch c_frozen_cost_update into c_config_item_id
2890 , c_organization_id ;
2891
2892
2893 exit when c_frozen_cost_update%notfound ;
2894
2895 -- bug 13106676
2896 --
2897 select mp1.primary_cost_method into v_primary_cost_method
2898 from mtl_parameters mp1
2899 where mp1.organization_id = c_organization_id ;
2900
2901 IF PG_DEBUG <> 0 THEN
2902 oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_primary_cost_method = '||v_primary_cost_method);
2903 END IF;
2904
2905 -- If the org is Standard cost then check if the Item is transacted
2906 --
2907 IF (v_primary_cost_method = 1) THEN
2908 v_is_cst_updatable := check_ct_updateable(c_config_item_id, c_organization_id, 1);
2909 ELSE
2910 v_is_cst_updatable := TRUE;
2911 END IF;
2912
2913 IF PG_DEBUG <> 0 THEN
2914 IF (v_is_cst_updatable = TRUE) THEN
2915 oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_is_cst_updatable = TRUE' );
2916 ELSE
2917 oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_is_cst_updatable = FALSE' );
2918 END IF;
2919 END IF;
2920
2921 -- bug 13106676
2922 -- Check if Item is transacted
2923 --
2924 IF (v_is_cst_updatable = TRUE) THEN
2925
2926 IF PG_DEBUG <> 0 THEN
2927 oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_is_cst_updatable = TRUE, so calling the frozen cost update API');
2928 END IF;
2929
2930 --Bugfix 6717614
2931 lStmtNumber := 95;
2932 l_cost_update_required := cost_update_required(c_config_item_id,
2933 c_organization_id,
2934 v_cto_cost_type_id);
2935
2936 IF (l_cost_update_required = 'Y') THEN
2937
2938
2939 IF PG_DEBUG <> 0 THEN
2940 oe_debug_pub.add('********************** going to do frozen cost update for config ' || c_config_item_id
2941 || ' in org ' || c_organization_id , 1);
2942 END IF;
2943
2944
2945 delete from cst_item_cost_details
2946 where inventory_item_id = c_config_item_id
2947 and organization_id = c_organization_id
2948 and cost_type_id = 1 ;
2949
2950 IF PG_DEBUG <> 0 THEN
2951 oe_debug_pub.add('deleted details ' || SQL%ROWCOUNT , 1);
2952 END IF;
2953
2954
2955 delete from cst_item_costs
2956 where inventory_item_id = c_config_item_id
2957 and organization_id = c_organization_id
2958 and cost_type_id = 1 ;
2959
2960
2961 IF PG_DEBUG <> 0 THEN
2962 oe_debug_pub.add('deleted headers ' || SQL%ROWCOUNT , 1);
2963 END IF;
2964
2965
2966 copy_ctocost_to_frozen ( c_config_item_id
2967 , c_organization_id
2968 , v_cto_cost_type_id ) ;
2969
2970 END IF; --l_cost_update_required Bugfix 6717614
2971 ELSE
2972 IF PG_DEBUG <> 0 THEN
2973 oe_debug_pub.add('Cost_Rollup_ML: ' || 'v_is_cst_updatable = FALSE, so updating CTO cost with Frozen cost');
2974 END IF;
2975 -- Update CTO cost with Frozen cost
2976 --
2977 CTO_UTILITY_PK.copy_cost(v_primary_cost_method
2978 , v_cto_cost_type_id
2979 , c_config_item_id
2980 , c_organization_id
2981 );
2982 END IF; -- IF check_ct_updateable = TRUE THEN
2983
2984 v_primary_cost_method := NULL;
2985 v_is_cst_updatable := TRUE;
2986 end loop ;
2987
2988
2989 close c_frozen_cost_update ;
2990 end if; --cst_sc_list_count = 0 Bugfix 6717614
2991
2992
2993 -- debug 3116778
2994
2995 open c1_cst ( c1_config_item_id ) ;
2996
2997 IF PG_DEBUG <> 0 THEN
2998 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
2999 END IF;
3000
3001 loop
3002
3003 fetch c1_cst into v_cur_c1_cst ;
3004
3005 exit when c1_cst%notfound ;
3006
3007 IF PG_DEBUG <> 0 THEN
3008 oe_debug_pub.add( 'printing sc rollup values ' ,1 ) ;
3009 oe_debug_pub.add( ' iid ' || v_cur_c1_cst.inventory_item_id ||
3010 ' org ' || to_char( v_cur_c1_cst.organization_id) ||
3011 ' cstyp ' || to_char( v_cur_c1_cst.cost_type_id) ||
3012 ' icst ' || to_char( v_cur_c1_cst.item_cost ) ||
3013 ' mcst ' || to_char( v_cur_c1_cst.material_cost ) ||
3014 ' mocst ' || to_char( v_cur_c1_cst.material_overhead_cost) ||
3015 ' ceid ' || to_char( v_cur_c1_cst.cost_element_id ) ||
3016 ' cicst ' || to_char( v_cur_c1_cst.cicd_item_cost ) , 1 ) ;
3017 END IF;
3018
3019 end loop ;
3020
3021 IF PG_DEBUG <> 0 THEN
3022 oe_debug_pub.add( 'done printing sc rollup values ' ,1 ) ;
3023 END IF;
3024
3025 close c1_cst ;
3026
3027 -- debug 3116778
3028
3029
3030
3031 IF PG_DEBUG <> 0 THEN
3032 oe_debug_pub.add('Cost_Rollup_ML: ' || 'At end of cost rollup', 2);
3033 END IF;
3034 return(1);
3035
3036 EXCEPTION
3037 WHEN NO_DATA_FOUND THEN
3038 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::ndf::'||to_char(lStmtNumber)||sqlerrm, 1);
3039 return(0);
3040
3041 WHEN CTO_MRP_ASSIGNMENT_SET THEN
3042 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::mrp assgn set is null'||to_char(lStmtNumber)||sqlerrm, 1);
3043
3044 cto_msg_pub.count_and_get
3045 ( p_msg_count => x_msg_count
3046 , p_msg_data => x_msg_data
3047 );
3048
3049
3050 return(0);
3051
3052
3053 WHEN FND_API.G_EXC_ERROR THEN
3054 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::exp error::'||to_char(lStmtNumber)||sqlerrm, 1);
3055
3056 cto_msg_pub.count_and_get
3057 ( p_msg_count => x_msg_count
3058 , p_msg_data => x_msg_data
3059 );
3060
3061
3062 return(0);
3063
3064
3065 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3066 oe_debug_pub.add ('Cost_Rollup_ML: ' || 'cost_rollup::unexp error::'||to_char(lStmtNumber)||sqlerrm, 1);
3067
3068 cto_msg_pub.count_and_get
3069 ( p_msg_count => x_msg_count
3070 , p_msg_data => x_msg_data
3071 );
3072
3073 return(-1);
3074
3075
3076
3077 END Cost_Rollup_ML;
3078
3079 end CTO_CONFIG_COST_PK;