[Home] [Help]
PACKAGE BODY: APPS.CSTPUMEC
Source
1 PACKAGE BODY CSTPUMEC AS
2 /* $Header: CSTPUMEB.pls 120.7.12010000.2 2008/10/22 14:20:09 smsasidh ship $ */
3
4
5 --
6 -- stored procedure to copy a cost type before executing the mass
7 -- edit procedure
8 --
9 PROCEDURE CSTPECPC (
10 I_ORGANIZATION_ID IN NUMBER,
11 I_COST_TYPE_ID IN NUMBER,
12 I_FROM_COST_TYPE IN NUMBER,
13 I_LIST_ID IN NUMBER,
14 I_USER_ID IN NUMBER,
15 I_REQ_ID IN NUMBER,
16 I_PRGM_ID IN NUMBER,
17 I_PRGM_APPL_ID IN NUMBER,
18 O_RETURN_CODE OUT NOCOPY NUMBER) IS
19
20 l_location NUMBER;
21
22 BEGIN
23
24 O_RETURN_CODE := 9999;
25
26 l_location := 1;
27
28 DELETE FROM cst_item_costs CIC
29 WHERE CIC.organization_id = I_ORGANIZATION_ID
30 AND CIC.cost_type_id = I_COST_TYPE_ID
31 AND CIC.inventory_item_id in
32 (SELECT C2.inventory_item_id
33 FROM cst_lists CL
34 , cst_item_costs C2
35 WHERE list_id = I_LIST_ID
36 AND C2.cost_type_id = I_FROM_COST_TYPE
37 AND C2.organization_id = I_ORGANIZATION_ID
38 AND C2.inventory_item_id = CL.entity_id
39 );
40
41 l_location := 2;
42
43 INSERT INTO cst_item_costs
44 (
45 inventory_item_id, organization_id, cost_type_id,
46 request_id, program_application_id,
47 program_id, program_update_date,
48 last_update_date, last_updated_by,
49 creation_date, created_by,
50 last_update_login, inventory_asset_flag,
51 lot_size, based_on_rollup_flag,
52 shrinkage_rate, defaulted_flag,
53 pl_material, pl_material_overhead,
54 pl_resource, pl_outside_processing,
55 pl_overhead,
56 tl_material, tl_material_overhead,
57 tl_resource, tl_outside_processing,
58 tl_overhead,
59 material_cost, material_overhead_cost,
60 resource_cost, outside_processing_cost,
61 overhead_cost,
62 pl_item_cost, tl_item_cost,
63 unburdened_cost, burden_cost,
64 item_cost, attribute_category,
65 attribute1, attribute2, attribute3,
66 attribute4, attribute5, attribute6,
67 attribute7, attribute8, attribute9,
68 attribute10, attribute11, attribute12,
69 attribute13, attribute14, attribute15
70 )
71 SELECT CIC.inventory_item_id
72 , CIC.organization_id
73 , I_COST_TYPE_ID
74 , I_REQ_ID
75 , I_PRGM_APPL_ID
76 , I_PRGM_ID
77 , SYSDATE
78 , SYSDATE
79 , I_USER_ID
80 , SYSDATE
81 , I_USER_ID
82 , -1
83 , CIC.inventory_asset_flag
84 , CIC.lot_size
85 , CIC.based_on_rollup_flag
86 , CIC.shrinkage_rate
87 , CIC.defaulted_flag
88 , CIC.pl_material
89 , CIC.pl_material_overhead
90 , CIC.pl_resource
91 , CIC.pl_outside_processing
92 , CIC.pl_overhead
93 , CIC.tl_material
94 , CIC.tl_material_overhead
95 , CIC.tl_resource
96 , CIC.tl_outside_processing
97 , CIC.tl_overhead
98 , CIC.material_cost
99 , CIC.material_overhead_cost
100 , CIC.resource_cost
101 , CIC.outside_processing_cost
102 , CIC.overhead_cost
103 , CIC.pl_item_cost
104 , CIC.tl_item_cost
105 , CIC.unburdened_cost
106 , CIC.burden_cost
107 , CIC.item_cost
108 , CIC.attribute_category
109 , CIC.attribute1
110 , CIC.attribute2
111 , CIC.attribute3
112 , CIC.attribute4
113 , CIC.attribute5
114 , CIC.attribute6
115 , CIC.attribute7
116 , CIC.attribute8
117 , CIC.attribute9
118 , CIC.attribute10
119 , CIC.attribute11
120 , CIC.attribute12
121 , CIC.attribute13
122 , CIC.attribute14
123 , CIC.attribute15
124 FROM cst_lists CL
125 , cst_item_costs CIC
126 WHERE CL.list_id = I_LIST_ID
127 AND CIC.inventory_item_id = CL.entity_id
128 AND CIC.cost_type_id = I_FROM_COST_TYPE
129 AND CIC.organization_id = I_ORGANIZATION_ID;
130
131 --
132 -- delete any cost information from CST_ITEM_COST_DETAILS
133 -- for items specified in the current edit list
134 --
135 l_location := 3;
136
137 DELETE FROM cst_item_cost_details CICD
138 WHERE CICD.organization_id = I_ORGANIZATION_ID
139 AND CICD.cost_type_id = I_COST_TYPE_ID
140 AND CICD.inventory_item_id in
141 (SELECT C2.inventory_item_id
142 FROM cst_lists CL
143 , cst_item_costs C2
144 WHERE list_id = I_LIST_ID
145 AND C2.cost_type_id = I_FROM_COST_TYPE
146 AND C2.organization_id = I_ORGANIZATION_ID
147 AND C2.inventory_item_id = CL.entity_id
148 );
149
150 --
151 -- copy cost information from the source cost type to
152 -- the target cost type
153 --
154 l_location := 4;
155
156
157 INSERT INTO cst_item_cost_details
158 ( inventory_item_id
159 , organization_id
160 , cost_type_id
161 , last_update_date
162 , last_updated_by
163 , creation_date
164 , created_by
165 , last_update_login
166 , operation_sequence_id
167 , operation_seq_num
168 , department_id
169 , level_type
170 , activity_id
171 , resource_seq_num
172 , resource_id
173 , resource_rate
174 , item_units
175 , activity_units
176 , usage_rate_or_amount
177 , basis_type
178 , basis_resource_id
179 , basis_factor
180 , net_yield_or_shrinkage_factor
181 , item_cost
182 , cost_element_id
183 , rollup_source_type
184 , activity_context
185 , request_id
186 , program_application_id
187 , program_id
188 , program_update_date
189 , yielded_cost
190 , attribute_category
191 , attribute1
192 , attribute2
193 , attribute3
194 , attribute4
195 , attribute5
196 , attribute6
197 , attribute7
198 , attribute8
199 , attribute9
200 , attribute10
201 , attribute11
202 , attribute12
203 , attribute13
204 , attribute14
205 , attribute15)
206 SELECT
207 CICD.inventory_item_id
208 , CICD.organization_id
209 , I_COST_TYPE_ID
210 , SYSDATE
211 , I_USER_ID
212 , SYSDATE
213 , I_USER_ID
214 , I_USER_ID
215 , CICD.operation_sequence_id
216 , CICD.operation_seq_num
217 , CICD.department_id
218 , CICD.level_type
219 , CICD.activity_id
220 , CICD.resource_seq_num
221 , CICD.resource_id
222 , CICD.resource_rate
223 , CICD.item_units
224 , CICD.activity_units
225 , CICD.usage_rate_or_amount
226 , CICD.basis_type
227 , CICD.basis_resource_id
228 , CICD.basis_factor
229 , CICD.net_yield_or_shrinkage_factor
230 , CICD.item_cost
231 , CICD.cost_element_id
232 , CICD.rollup_source_type
233 , CICD.activity_context
234 , I_REQ_ID
235 , I_PRGM_APPL_ID
236 , I_PRGM_ID
237 , SYSDATE
238 , CICD.yielded_cost
239 , CICD.attribute_category
240 , CICD.attribute1
241 , CICD.attribute2
242 , CICD.attribute3
243 , CICD.attribute4
244 , CICD.attribute5
245 , CICD.attribute6
246 , CICD.attribute7
247 , CICD.attribute8
248 , CICD.attribute9
249 , CICD.attribute10
250 , CICD.attribute11
251 , CICD.attribute12
252 , CICD.attribute13
253 , CICD.attribute14
254 , CICD.attribute15
255 FROM cst_lists CL
256 , cst_item_cost_details CICD
257 WHERE CL.list_id = I_LIST_ID
258 AND CICD.cost_type_id = I_FROM_COST_TYPE
259 AND CICD.organization_id = I_ORGANIZATION_ID
260 AND CICD.inventory_item_id = CL.entity_id;
261
262 O_RETURN_CODE := 0;
263
264 EXCEPTION
265 WHEN NO_DATA_FOUND THEN
266 O_RETURN_CODE := SQLCODE;
267 WHEN OTHERS THEN
268 O_RETURN_CODE := SQLCODE;
269 raise_application_error(-20001,
270 'CSTPECPC-' || l_location || ': ' || SQLERRM);
271
272 END CSTPECPC;
273
274 --
275 -- stored procedure to insert new item costs for actual cost edits
276 --
277 PROCEDURE CSTPEIIC (
278 I_ORGANIZATION_ID IN NUMBER,
279 I_COST_TYPE_ID IN NUMBER,
280 I_LIST_ID IN NUMBER,
281 I_RESOURCE_ID IN NUMBER,
282 I_USER_ID IN NUMBER,
283 I_REQ_ID IN NUMBER,
284 I_PRGM_ID IN NUMBER,
285 I_PRGM_APPL_ID IN NUMBER,
286 O_RETURN_CODE OUT NOCOPY NUMBER) IS
287
288
289 l_location NUMBER;
290 TYPE l_table IS TABLE OF CST_ITEM_COSTS.INVENTORY_ITEM_ID%TYPE;
291 l_temp_table l_table;
292 CURSOR cur_list IS
293 (select entity_id
294 from cst_lists l
295 where L.list_id = I_LIST_ID
296 and NOT EXISTS (select inventory_item_id
297 from cst_item_costs cic2
298 where cic2.inventory_item_id = l.entity_id
299 and cic2.organization_id = I_ORGANIZATION_ID
300 AND cic2.cost_type_id = I_COST_TYPE_ID)
301 );
302
303 BEGIN
304
305 O_RETURN_CODE := 9999;
306
307 l_location := 1;
308 /* Used the Bulk Collect to improve the performance Bug 4968362 */
309 OPEN cur_list;
310 LOOP
311 FETCH cur_list BULK COLLECT INTO l_temp_table LIMIT 1000;
312
313 FORALL i IN l_temp_table.FIRST..l_temp_table.LAST
314 INSERT INTO cst_item_costs
315 ( inventory_item_id
316 , organization_id
317 , cost_type_id
318 , request_id
319 , program_application_id
320 , program_id
321 , program_update_date
322 , last_update_date
323 , last_updated_by
324 , creation_date
325 , created_by
326 , last_update_login
327 , inventory_asset_flag
328 , lot_size
329 , based_on_rollup_flag
330 , shrinkage_rate
331 , defaulted_flag
332 , attribute_category
333 , attribute1
334 , attribute2
335 , attribute3
336 , attribute4
337 , attribute5
338 , attribute6
339 , attribute7
340 , attribute8
341 , attribute9
342 , attribute10
343 , attribute11
344 , attribute12
345 , attribute13
346 , attribute14
347 , attribute15
348 )
349 SELECT
350 CIC.inventory_item_id
351 , CIC.organization_id
352 , I_COST_TYPE_ID
353 , I_REQ_ID
354 , I_PRGM_APPL_ID
355 , I_PRGM_ID
356 , SYSDATE
357 , SYSDATE
358 , I_USER_ID
359 , SYSDATE
360 , I_USER_ID
361 , -1
362 , 1
363 , CIC.lot_size
364 , CIC.based_on_rollup_flag
365 , CIC.shrinkage_rate
366 , CIC.defaulted_flag
367 , CIC.attribute_category
368 , CIC.attribute1
369 , CIC.attribute2
370 , CIC.attribute3
371 , CIC.attribute4
372 , CIC.attribute5
373 , CIC.attribute6
374 , CIC.attribute7
375 , CIC.attribute8
376 , CIC.attribute9
377 , CIC.attribute10
378 , CIC.attribute11
379 , CIC.attribute12
380 , CIC.attribute13
381 , CIC.attribute14
382 , CIC.attribute15
383 FROM cst_item_costs CIC
384 WHERE CIC.organization_id = I_ORGANIZATION_ID
385 AND CIC.cost_type_id in (1,2)
386 AND CIC.inventory_item_id = l_temp_table(i)
387 AND CIC.inventory_asset_flag = 1 ;
388
389 EXIT WHEN cur_list%NOTFOUND;
390 END LOOP;
391 close cur_list;
392
393 /*
394 Fix for Bug#2122019 - Added activity_id in the insert to populate default
395 activity assigned to the sub element being edited. Selected default_activity_id
396 from bom_resources for the edited sub element
397 */
398 l_location := 2;
399
400 INSERT INTO cst_item_cost_details
401 ( inventory_item_id
402 , organization_id
403 , cost_type_id
404 , last_update_date
405 , last_updated_by
406 , creation_date
407 , created_by
408 , level_type
409 , activity_id
410 , resource_id
411 , resource_rate
412 , usage_rate_or_amount
413 , basis_type
417 , cost_element_id
414 , basis_factor
415 , net_yield_or_shrinkage_factor
416 , item_cost
418 , rollup_source_type
419 , request_id
420 , program_application_id
421 , program_id
422 , program_update_date
423 , attribute_category
424 , attribute1
425 , attribute2
426 , attribute3
427 , attribute4
428 , attribute5
429 , attribute6
430 , attribute7
431 , attribute8
432 , attribute9
433 , attribute10
434 , attribute11
435 , attribute12
436 , attribute13
437 , attribute14
438 , attribute15
439 )
440 SELECT
441 CIC.inventory_item_id
442 , CIC.organization_id
443 , CIC.cost_type_id
444 , SYSDATE
445 , I_USER_ID
446 , SYSDATE
447 , I_USER_ID
448 , 1
449 , BR.default_activity_id
450 , I_RESOURCE_ID
451 , 1
452 , 0
453 , 1 /* Item */
454 , 1
455 , 1
456 , 0
457 , 1 /* Material */
458 , 1 /* User defined */
459 , I_REQ_ID
460 , I_PRGM_APPL_ID
461 , I_PRGM_ID
462 , SYSDATE
463 , CIC.attribute_category
464 , CIC.attribute1
465 , CIC.attribute2
466 , CIC.attribute3
467 , CIC.attribute4
468 , CIC.attribute5
469 , CIC.attribute6
470 , CIC.attribute7
471 , CIC.attribute8
472 , CIC.attribute9
473 , CIC.attribute10
474 , CIC.attribute11
475 , CIC.attribute12
476 , CIC.attribute13
477 , CIC.attribute14
478 , CIC.attribute15
479 FROM cst_lists L
480 , cst_item_costs CIC
481 , bom_resources BR
482 WHERE CIC.organization_id = I_ORGANIZATION_ID
483 AND L.list_id = I_LIST_ID
484 AND BR.resource_id = I_RESOURCE_ID
485 AND CIC.cost_type_id = I_COST_TYPE_ID
486 AND CIC.inventory_item_id = L.entity_id
487 AND CIC.inventory_asset_flag = 1
488 AND L.entity_id not in (
489 SELECT inventory_item_id
490 FROM cst_item_cost_details
491 WHERE organization_id = I_ORGANIZATION_ID
492 AND cost_type_id = I_COST_TYPE_ID
493 -- Commented out lines to fix bug # 1962252 , mass edit adds new
494 -- sub-elements on unit cost of items.The changes were introduced due to fix made
495 -- for bug # 1175172
496 -- AND resource_id = I_RESOURCE_ID
497 -- AND level_type = 1
498 -- AND cost_element_id = 1
499
500
501 );
502
503 O_RETURN_CODE := 0;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 O_RETURN_CODE := SQLCODE;
508 WHEN OTHERS THEN
509 O_RETURN_CODE := SQLCODE;
510 raise_application_error(-20001,
511 'CSTPEIIC-'||l_location||': '||SQLERRM);
512
513 END CSTPEIIC;
514
515 --
516 -- stored procedure to recompute item costs after mass edit execution
517 --
518 PROCEDURE CSTPERIC (
519 I_ORGANIZATION_ID IN NUMBER,
520 I_COST_TYPE_ID IN NUMBER,
521 I_LIST_ID IN NUMBER,
522 I_USER_ID IN NUMBER,
523 I_REQ_ID IN NUMBER,
524 I_PRGM_ID IN NUMBER,
525 I_PRGM_APPL_ID IN NUMBER,
526 O_RETURN_CODE OUT NOCOPY NUMBER) IS
527
528 l_round_unit NUMBER;
529 l_precision NUMBER;
530 l_ext_prec NUMBER;
531 l_basis_factor NUMBER;
532
533 /* Cursor UPD_ITEM_ID modified for bug 1970016 , 2592136 */
534
535 Cursor UPD_ITEM_ID is
536 SELECT entity_id
537 FROM cst_lists cl
538 WHERE CL.list_id= I_LIST_ID
539 AND EXISTS (SELECT /*+ no_unnest index( cicd CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
540 NULL FROM
541 CST_ITEM_COST_DETAILS CICD
542 WHERE CICD.organization_id = I_ORGANIZATION_ID
543 AND CICD.cost_type_id = I_COST_TYPE_ID
544 AND CICD.level_type = 1
545 AND CICD.cost_element_id = 2
546 AND CICD.basis_type = 5
547 AND CL.entity_id = CICD.inventory_item_id);
548
549
550 /* Modified the cursor for Bug 5150357 */
551 Cursor C_ITEM_ID is
552 select entity_id from cst_lists cl
553 where list_id = I_LIST_ID
554 AND EXISTS (SELECT /*+ no_unnest index( cicd CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
555 NULL FROM
556 CST_ITEM_COST_DETAILS CICD
557 WHERE CICD.organization_id = I_ORGANIZATION_ID
558 AND CICD.cost_type_id = I_COST_TYPE_ID
559 AND CL.entity_id = CICD.inventory_item_id);
563
560
561 TYPE c_item_id_tbl_type IS TABLE OF cst_lists.entity_id%TYPE INDEX BY BINARY_INTEGER;
562 c_item_id_tbl c_item_id_tbl_type;
564 l_location NUMBER;
565
566 BEGIN
567
568 O_RETURN_CODE := 9999;
569
570 CSTPUTIL.CSTPUGCI(I_ORGANIZATION_ID, l_round_unit, l_precision, l_ext_prec);
571
572 --
573 -- recompute any material overhead which is based on total value
574 --
575 l_location := 0;
576
577
578 FOR ITEMS in UPD_ITEM_ID LOOP
579 /* added for bug 2592136 */
580 BEGIN
581 SELECT SUM(NVL(CICD.item_cost,0))
582 into l_basis_factor
583 FROM cst_item_cost_details CICD
584 WHERE CICD.inventory_item_id = ITEMS.entity_id
585 AND CICD.organization_id = I_ORGANIZATION_ID
586 AND CICD.cost_type_id = I_COST_TYPE_ID
587 AND NOT (CICD.cost_element_id = 2 AND CICD.level_type = 1);
588 EXCEPTION
589 WHEN Others THEN
590 l_basis_factor := -1;
591 END;
592
593
594 if (l_basis_factor >= 0) THEN
595 /* added for bug 1970016 */
596 UPDATE cst_item_cost_details CICD
597 SET last_update_date = SYSDATE,
598 last_updated_by = I_USER_ID,
599 basis_factor = l_basis_factor,
600 item_cost = ROUND((CICD.usage_rate_or_amount*l_basis_factor), l_ext_prec),
601 request_id = I_REQ_ID,
602 program_application_id = I_PRGM_APPL_ID,
603 program_id = I_PRGM_ID,
604 program_update_date = SYSDATE
605 WHERE organization_id = I_ORGANIZATION_ID
606 AND cost_type_id = I_COST_TYPE_ID
607 AND level_type = 1 /* This Level */
608 AND cost_element_id = 2 /* Material Overhead */
609 AND basis_type = 5 /* Total Value */
610 AND CICD.inventory_item_id = ITEMS.entity_id;
611 END IF;
612
613 END LOOP;
614 --
615 -- Update denormalized data in CST_ITEM_COSTS table
616 --
617 l_location := 1;
618 OPEN c_item_id;
619 LOOP
620 FETCH c_item_id BULK COLLECT INTO c_item_id_tbl
621 LIMIT 1000;
622
623 FORALL i IN c_item_id_tbl.first..c_item_id_tbl.last
624 UPDATE cst_item_costs CIC
625 SET (
626 last_update_date,
627 last_updated_by,
628 pl_material,
629 pl_material_overhead,
630 pl_resource,
631 pl_outside_processing,
632 pl_overhead,
633 tl_material,
634 tl_material_overhead,
635 tl_resource,
636 tl_outside_processing,
637 tl_overhead,
638 material_cost,
639 material_overhead_cost,
640 resource_cost,
641 outside_processing_cost,
642 overhead_cost,
643 pl_item_cost,
644 tl_item_cost,
645 item_cost,
646 unburdened_cost,
647 burden_cost,
648 request_id,
649 program_application_id,
650 program_id,
651 program_update_date) =
652 (
653 SELECT /*+ index(CICD CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
654 SYSDATE
655 , I_USER_ID
656 , SUM(DECODE(level_type,2,DECODE(cost_element_id,1,item_cost,0),0))
657 , SUM(DECODE(level_type,2,DECODE(cost_element_id,2,item_cost,0),0))
658 , SUM(DECODE(level_type,2,DECODE(cost_element_id,3,item_cost,0),0))
659 , SUM(DECODE(level_type,2,DECODE(cost_element_id,4,item_cost,0),0))
660 , SUM(DECODE(level_type,2,DECODE(cost_element_id,5,item_cost,0),0))
661 , SUM(DECODE(level_type,1,DECODE(cost_element_id,1,item_cost,0),0))
662 , SUM(DECODE(level_type,1,DECODE(cost_element_id,2,item_cost,0),0))
663 , SUM(DECODE(level_type,1,DECODE(cost_element_id,3,item_cost,0),0))
664 , SUM(DECODE(level_type,1,DECODE(cost_element_id,4,item_cost,0),0))
665 , SUM(DECODE(level_type,1,DECODE(cost_element_id,5,item_cost,0),0))
666 , SUM(DECODE(cost_element_id,1,item_cost))
667 , SUM(DECODE(cost_element_id,2,item_cost))
668 , SUM(DECODE(cost_element_id,3,item_cost))
669 , SUM(DECODE(cost_element_id,4,item_cost))
670 , SUM(DECODE(cost_element_id,5,item_cost))
671 , SUM(DECODE(level_type,2,item_cost,0))
672 , SUM(DECODE(level_type,1,item_cost,0))
673 , SUM(item_cost)
674 , SUM(DECODE(cost_element_id,
675 2, DECODE(level_type,2,item_cost,0),
676 item_cost))
677 , SUM(DECODE(cost_element_id,
678 2, DECODE(level_type,1,item_cost,0),
679 0))
680 , I_REQ_ID, I_PRGM_APPL_ID, I_PRGM_ID, SYSDATE
681 FROM cst_item_cost_details CICD
682 WHERE organization_id = I_ORGANIZATION_ID
683 AND cost_type_id = I_COST_TYPE_ID
684 AND inventory_item_id = c_item_id_tbl(i)
685 )
686 WHERE CIC.organization_id = I_ORGANIZATION_ID
687 AND CIC.cost_type_id = I_COST_TYPE_ID
691 END LOOP;
688 AND CIC.inventory_item_id = c_item_id_tbl(i);
689
690 EXIT WHEN c_item_id%NOTFOUND;
692
693 CLOSE c_item_id;
694
695 O_RETURN_CODE := 0;
696
697 EXCEPTION
698 WHEN NO_DATA_FOUND THEN
699 O_RETURN_CODE := SQLCODE;
700 WHEN OTHERS THEN
701 O_RETURN_CODE := SQLCODE;
702 raise_application_error(-20001,
703 'CSTPERIC-' || l_location || ': ' || SQLERRM);
704
705 END CSTPERIC;
706
707
708 -- Start of comments
709 --
710 -- PROCEDURE
711 -- set_cost_controls Invoked from the Mass Edit Menu as a concurrent
712 -- request. This function allows the user to set
713 -- the values of the following three fields in
714 -- cst_item_costs:
715 -- BASED_ON_ROLLUP_FLAG
716 -- DEFAULTED_FLAG
717 -- LOT_SIZE
718 --
719 --
720 -- PARAMETERS
721 -- O_Err_Num output parameter for errors
722 -- O_Err_Msg output parameter for errors
723 -- i_org_id organization
724 -- i_cost_type target cost type
725 -- i_range All items, specific item, item range, category range
726 -- i_specific_item Will contain an inventory_item_id
727 -- i_category_set Contains the category set ID # for the category set the user selected
728 -- i_cat_strct Contains the default category_structure assigned to the above category set
729 -- i_category_from Contains the category ID for the FROM category that the user selected
730 -- i_category_to Contains the category ID for the TO category that the user selected
731 -- i_item_from A character string containing the flexfield concatenated segs (segment1||...)
732 -- i_item_to A character string containing the flexfield concatenated segs (segment1||...)
733 -- i_copy_option Choices are: 1. From system item definition - meaning copy the fields from the
734 -- MSI table for the chosen item(s) and organization.
735 -- 2. From cost type - meaning copy the fields from the CIC table for the chosen
736 -- item(s), organization, and cost type.
737 -- i_co_dummy NULL unless copy option = From cost type (used to enable the src_cost_type param)
738 -- i_src_cost_type Source cost type when copy option = From cost type
739 -- i_bor_flag Based on rollup flag setting (flag indicating whether cost is rolled up):
740 -- 1 = Set to 1(YES), 2 = Set to 2(NO), 3 = Copy(from MSI or CIC), 4 = keep current
741 -- i_def_flag Defaulted flag setting (flag indicating whether the cost of the item is
742 -- defaulted from the default cost type during cost rollup):
743 -- 1 = Set to 1(YES), 2 = Set to 2(NO), 3 = Copy(from CIC), 4 = keep current
744 -- i_lotsz_lov Selection made from lot size LOV: 1 = Set to #(which is provided in i_lot_size)
745 -- 2 = Copy (from MSI or CIC), 3 = keep current
746 -- i_lot_size lot size (ignored unless the lot size selection = 1)
747 --
748 -- End of comments
749
750 procedure set_cost_controls (
751 O_Err_Num OUT NOCOPY NUMBER,
752 O_Err_Msg OUT NOCOPY VARCHAR2,
753 i_org_id IN NUMBER,
754 i_cost_type IN NUMBER,
755 i_range IN NUMBER,
756 i_item_dummy IN NUMBER,
757 i_specific_item IN NUMBER,
758 i_category_set IN NUMBER,
759 i_cat_strct IN NUMBER,
760 i_category_from IN VARCHAR2,
761 i_category_to IN VARCHAR2,
762 i_item_from IN VARCHAR2,
763 i_item_to IN VARCHAR2,
764 i_copy_option IN NUMBER,
765 i_co_dummy IN NUMBER,
766 i_src_cost_type IN NUMBER,
767 i_bor_flag IN NUMBER,
768 i_def_flag IN NUMBER,
769 i_lotsz_lov IN NUMBER,
770 i_lot_size IN NUMBER
771 )
772 IS
773
774 -- the following 5 variables get WHO info from global FND variables
775 l_request_id NUMBER;
776 l_user_id NUMBER;
777 l_login_id NUMBER;
778 l_pgm_app_id NUMBER;
779 l_pgm_id NUMBER;
780
781 l_stmt_num NUMBER := 0; -- keeps track of position in program
782 l_err_msg VARCHAR2(240); -- stores any error message
783 l_num_CIC_rows NUMBER := 0; -- number of rows updated in CIC
784 l_num_CICD_rows NUMBER := 0; -- number of rows updated in CICD
785
786 CONC_STATUS BOOLEAN; -- variable for SET_COMPLETION_STATUS
787
788 -- the following datatype holds the BULK COLLECTed item list
792 -- shrinkage_rate/BOR conflicts and defaulted_flag conflicts
789 TYPE ItemList IS TABLE OF cst_item_costs.inventory_item_id%TYPE;
790
791 l_items ItemList; -- Collection of items that will generate warnings due to
793 l_nonzero_shrinkage NUMBER; -- the number of such items
794
795 -- The next three variables are used to print a warning message with the
796 -- fnd_message utilities.
797 l_orgcode mtl_parameters.organization_code%TYPE := NULL;
798 l_costtype cst_cost_types.cost_type%TYPE := NULL;
799 l_itemname mtl_system_items_kfv.concatenated_segments%TYPE;
800
801 BEGIN
802
803 -- Start of program SAVEPOINT
804 SAVEPOINT set_cost_controls_PUB;
805
806 -- Get identifying information from global variables
807 l_request_id := FND_GLOBAL.conc_request_id;
808 l_user_id := FND_GLOBAL.user_id;
809 l_login_id := FND_GLOBAL.login_id;
810 l_pgm_app_id := FND_GLOBAL.PROG_APPL_ID;
811 l_pgm_id := FND_GLOBAL.CONC_PROGRAM_ID;
812
813 -- Write descriptive info to log file
814 fnd_file.put_line(fnd_file.log,'Request ID: '||to_char(l_request_id));
815 fnd_file.put_line(fnd_file.log,'PARAMETERS');
816 fnd_file.put_line(fnd_file.log,'Organization ID: '||to_char(i_org_id));
817 fnd_file.put_line(fnd_file.log,'Cost Type: '||to_char(i_cost_type));
818 fnd_file.put_line(fnd_file.log,'Range: '||to_char(i_range));
819 fnd_file.put_line(fnd_file.log,'Specific item: '||to_char(i_specific_item));
820 fnd_file.put_line(fnd_file.log,'Item From: '||i_item_from);
821 fnd_file.put_line(fnd_file.log,'Item To: '||i_item_to);
822 fnd_file.put_line(fnd_file.log,'Category Set: '||to_char(i_category_set));
823 fnd_file.put_line(fnd_file.log,'Category struct: '||to_char(i_cat_strct));
824 fnd_file.put_line(fnd_file.log,'Category From: '||i_category_from);
825 fnd_file.put_line(fnd_file.log,'Category To: '||i_category_to);
826 fnd_file.put_line(fnd_file.log,'Copy Option: '||to_char(i_copy_option));
827 fnd_file.put_line(fnd_file.log,'CO Dummy: '||to_char(i_co_dummy));
828 fnd_file.put_line(fnd_file.log,'Src Cost Type: '||to_char(i_src_cost_type));
829 fnd_file.put_line(fnd_file.log,'Based On Rollup: '||to_char(i_bor_flag));
830 fnd_file.put_line(fnd_file.log,'Defaulted Flag: '||to_char(i_def_flag));
831 fnd_file.put_line(fnd_file.log,'Lot Size Selection: '||to_char(i_lotsz_lov));
832 fnd_file.put_line(fnd_file.log,'Lot Size: '||to_char(i_lot_size));
833
834
835 if (i_bor_flag <> 3 AND i_def_flag <> 3 AND i_lotsz_lov <> 2) then
836 -- Update CIC in the case where none of the fields are copied - avoids superfluous subquery in SET stmt
837
838 l_stmt_num := 10;
839 UPDATE cst_item_costs cic
840 SET based_on_rollup_flag = decode(i_bor_flag, 1,1, 2,2, 4,cic.BASED_ON_ROLLUP_FLAG, NULL),
841 defaulted_flag = decode(i_def_flag, 1,1, 2,2, 4,cic.DEFAULTED_FLAG, NULL),
842 lot_size = decode(i_lotsz_lov, 1,nvl(i_lot_size,cic.LOT_SIZE), 3,cic.LOT_SIZE, NULL),
843 last_update_date = sysdate,
844 last_updated_by = l_user_id,
845 last_update_login = l_login_id,
846 request_id = l_request_id,
847 program_application_id = l_pgm_app_id,
848 program_id = l_pgm_id,
849 program_update_date = sysdate
850 WHERE cic.cost_type_id = i_cost_type
851 AND cic.organization_id = i_org_id
852 AND (i_range = 1
853 OR (i_range = 2
854 AND cic.inventory_item_id = i_specific_item)
855 OR (i_range = 3
856 AND cic.inventory_item_id IN
857 (SELECT msi1.inventory_item_id
858 FROM mtl_system_items_kfv msi1
859 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
860 OR (i_range = 5
861 AND cic.inventory_item_id IN
862 (SELECT msi2.inventory_item_id
863 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
864 WHERE mic.organization_id = i_org_id
865 AND mic.category_set_id = i_category_set
866 AND mic.inventory_item_id = msi2.inventory_item_id
867 AND mic.organization_id = msi2.organization_id
868 AND mic.category_id = mc.category_id
869 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
870
871 elsif (i_copy_option = 1) then
872 -- Update CIC where some fields are copied from MSI and others keep current settings
873
874 l_stmt_num := 20;
875 UPDATE cst_item_costs cic
876 SET (based_on_rollup_flag,
877 defaulted_flag,
878 lot_size,
879 last_update_date,
880 last_updated_by,
881 last_update_login,
882 request_id,
883 program_application_id,
884 program_id,
885 program_update_date) =
886 (SELECT decode(i_bor_flag, 1,1, 2,2, 3,nvl(msi.PLANNING_MAKE_BUY_CODE,cic.BASED_ON_ROLLUP_FLAG),
887 4,cic.BASED_ON_ROLLUP_FLAG, NULL),
888 decode(i_def_flag, 1,1, 2,2, 4,cic.DEFAULTED_FLAG, NULL),
889 decode(i_lotsz_lov, 1,nvl(i_lot_size,cic.LOT_SIZE), 2,nvl(msi.STD_LOT_SIZE,cic.LOT_SIZE),
890 3,cic.LOT_SIZE, NULL),
891 sysdate,
892 l_user_id,
893 l_login_id,
894 l_request_id,
895 l_pgm_app_id,
896 l_pgm_id,
897 sysdate
898 FROM mtl_system_items msi
899 WHERE msi.organization_id = cic.organization_id
903 AND (i_range = 1
900 AND msi.inventory_item_id = cic.inventory_item_id)
901 WHERE cic.cost_type_id = i_cost_type
902 AND cic.organization_id = i_org_id
904 OR (i_range = 2
905 AND cic.inventory_item_id = i_specific_item)
906 OR (i_range = 3
907 AND cic.inventory_item_id IN
908 (SELECT msi1.inventory_item_id
909 FROM mtl_system_items_kfv msi1
910 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
911 OR (i_range = 5
912 AND cic.inventory_item_id IN
913 (SELECT msi2.inventory_item_id
914 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
915 WHERE mic.organization_id = i_org_id
916 AND mic.category_set_id = i_category_set
917 AND mic.inventory_item_id = msi2.inventory_item_id
918 AND mic.organization_id = msi2.organization_id
919 AND mic.category_id = mc.category_id
920 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
921
922 elsif (i_copy_option = 2) then
923 -- Update CIC where some fields are copied from src cost type and others keep current settings
924
925 l_stmt_num := 30;
926 UPDATE cst_item_costs cic
927 SET (based_on_rollup_flag,
928 defaulted_flag,
929 lot_size,
930 last_update_date,
931 last_updated_by,
932 last_update_login,
933 request_id,
934 program_application_id,
935 program_id,
936 program_update_date) =
937 (SELECT decode(i_bor_flag, 1,1, 2,2, 3,nvl(cic1.BASED_ON_ROLLUP_FLAG,cic2.BASED_ON_ROLLUP_FLAG),
938 4,cic2.BASED_ON_ROLLUP_FLAG, NULL),
939 decode(i_def_flag, 1,1, 2,2, 3,nvl(cic1.DEFAULTED_FLAG,cic2.DEFAULTED_FLAG),
940 4,cic2.DEFAULTED_FLAG, NULL),
941 decode(i_lotsz_lov, 1,nvl(i_lot_size,cic2.LOT_SIZE), 2,nvl(cic1.LOT_SIZE,cic2.LOT_SIZE),
942 3,cic2.LOT_SIZE, NULL),
943 sysdate,
944 l_user_id,
945 l_login_id,
946 l_request_id,
947 l_pgm_app_id,
948 l_pgm_id,
949 sysdate
950 FROM cst_item_costs cic1, cst_item_costs cic2
951 WHERE cic2.organization_id = cic.organization_id
952 AND cic2.inventory_item_id = cic.inventory_item_id
953 AND cic2.cost_type_id = cic.cost_type_id
954 AND cic1.organization_id (+) = cic2.organization_id
955 AND cic1.inventory_item_id (+) = cic2.inventory_item_id
956 AND cic1.cost_type_id (+) = i_src_cost_type)
957 WHERE cic.cost_type_id = i_cost_type
958 AND cic.organization_id = i_org_id
959 AND (i_range = 1
960 OR (i_range = 2
961 AND cic.inventory_item_id = i_specific_item)
962 OR (i_range = 3
963 AND cic.inventory_item_id IN
964 (SELECT msi1.inventory_item_id
965 FROM mtl_system_items_kfv msi1
966 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
967 OR (i_range = 5
968 AND cic.inventory_item_id IN
969 (SELECT msi2.inventory_item_id
970 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
971 WHERE mic.organization_id = i_org_id
972 AND mic.category_set_id = i_category_set
973 AND mic.inventory_item_id = msi2.inventory_item_id
974 AND mic.organization_id = msi2.organization_id
975 AND mic.category_id = mc.category_id
976 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)));
977
978 end if;
979
980 l_num_CIC_rows := SQL%ROWCOUNT;
981 fnd_file.put_line(fnd_file.log,'');
982 fnd_file.put_line(fnd_file.log,'Updated '||to_char(l_num_CIC_rows)||' rows in cst_item_costs.');
983
984 if (i_bor_flag = 1 OR i_bor_flag = 3) then
985 -- Based On Rollup may have been updated to YES, print a NOTE message
986 fnd_file.put_line(fnd_file.log,'');
987 fnd_message.set_name('BOM', 'CST_SCC_SHRINKAGE_NOTE');
988 fnd_file.put_line(fnd_file.log, fnd_message.get);
989 end if;
990
991 if (i_lotsz_lov = 2 OR (i_lotsz_lov = 1 AND i_lot_size IS NOT NULL)) then
992 -- Update CICD usage rate and basis factor with new lot size information
993 -- Only rows where basis_type = 2 (lot) and level_type = 1 (this level) should be touched
994 -- Adjust usage rate: new usage rate = old usage rate * (old basis factor / new basis factor)
995 -- Adjust basis factor: new basis factor = 1 / new lot size
996 l_stmt_num := 40;
997 UPDATE cst_item_cost_details cicd
998 SET (cicd.basis_factor,
999 cicd.usage_rate_or_amount,
1000 last_update_date,
1001 last_updated_by,
1002 last_update_login,
1003 request_id,
1004 program_application_id,
1005 program_id,
1006 program_update_date) =
1007 (SELECT nvl( (1/cic.lot_size), cicd.basis_factor),
1008 nvl( (cicd.usage_rate_or_amount * cicd.basis_factor * cic.lot_size), cicd.usage_rate_or_amount),
1009 sysdate,
1010 l_user_id,
1011 l_login_id,
1012 l_request_id,
1013 l_pgm_app_id,
1014 l_pgm_id,
1015 sysdate
1019 AND cic.inventory_item_id = cicd.inventory_item_id)
1016 FROM cst_item_costs cic
1017 WHERE cic.organization_id = cicd.organization_id
1018 AND cic.cost_type_id = cicd.cost_type_id
1020 WHERE cicd.cost_type_id = i_cost_type
1021 AND cicd.organization_id = i_org_id
1022 AND (i_range = 1
1023 OR (i_range = 2
1024 AND cicd.inventory_item_id = i_specific_item)
1025 OR (i_range = 3
1026 AND cicd.inventory_item_id IN
1027 (SELECT msi1.inventory_item_id
1028 FROM mtl_system_items_kfv msi1
1029 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
1030 OR (i_range = 5
1031 AND cicd.inventory_item_id IN
1032 (SELECT msi2.inventory_item_id
1033 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
1034 WHERE mic.organization_id = i_org_id
1035 AND mic.category_set_id = i_category_set
1036 AND mic.inventory_item_id = msi2.inventory_item_id
1037 AND mic.organization_id = msi2.organization_id
1038 AND mic.category_id = mc.category_id
1039 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
1040 AND cicd.basis_type = 2
1041 AND cicd.level_type = 1;
1042
1043 l_num_CICD_rows := SQL%ROWCOUNT;
1044 fnd_file.put_line(fnd_file.log,'Updated '||to_char(l_num_CICD_rows)||' rows in cst_item_cost_details for lotsize.');
1045
1046 end if; -- end of lot size change affecting CICD
1047
1048 if (i_bor_flag = 2 OR i_bor_flag = 3) then
1049 -- Now the based_on_rollup_flag may have been set to NO for some records where
1050 -- the shrinkage_rate <> 0. This is illegal. Reset the shrinkage_rate for such
1051 -- rows back to 0 (effectively removing shrinkage rate). The RETURNING clause
1052 -- captures the list of inventory_item_ids for which the shrinkage rate was
1053 -- reset. Note that costs will not be changed for affected items. Instead, the
1054 -- usage_rate_or_amount in CICD will be adjusted to keep it consistent with the cost
1055 -- and the new net_yield_or_shrinkage_factor.
1056 l_stmt_num := 50;
1057 UPDATE cst_item_costs cic
1058 SET cic.shrinkage_rate = 0
1059 WHERE cic.cost_type_id = i_cost_type
1060 AND cic.organization_id = i_org_id
1061 AND (i_range = 1
1062 OR (i_range = 2
1063 AND cic.inventory_item_id = i_specific_item)
1064 OR (i_range = 3
1065 AND cic.inventory_item_id IN
1066 (SELECT msi1.inventory_item_id
1067 FROM mtl_system_items_kfv msi1
1068 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
1069 OR (i_range = 5
1070 AND cic.inventory_item_id IN
1071 (SELECT msi2.inventory_item_id
1072 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
1073 WHERE mic.organization_id = i_org_id
1074 AND mic.category_set_id = i_category_set
1075 AND mic.inventory_item_id = msi2.inventory_item_id
1076 AND mic.organization_id = msi2.organization_id
1077 AND mic.category_id = mc.category_id
1078 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
1079 AND cic.shrinkage_rate <> 0
1080 AND cic.based_on_rollup_flag = 2
1081 RETURNING cic.inventory_item_id BULK COLLECT INTO l_items;
1082
1083 l_nonzero_shrinkage := l_items.COUNT;
1084
1085 if (l_nonzero_shrinkage > 0) then
1086 -- print a warning message to the log file and set the request status to WARNING - yellow highlight
1087 fnd_file.put_line(fnd_file.log,'');
1088 fnd_message.set_name('BOM', 'CST_SCC_SHRINKAGE_TO_ZERO');
1089 fnd_message.set_token('NUMBER', to_char(l_nonzero_shrinkage));
1090
1091 select organization_code
1092 into l_orgcode
1093 from mtl_parameters
1094 where organization_id = i_org_id;
1095 fnd_message.set_token('ORG', l_orgcode);
1096
1097 select cost_type
1098 into l_costtype
1099 from cst_cost_types
1100 where cost_type_id = i_cost_type;
1101 fnd_message.set_token('CT', l_costtype);
1102 fnd_file.put_line(fnd_file.log, fnd_message.get);
1103
1104 FOR i in 1..l_nonzero_shrinkage LOOP
1105 select concatenated_segments
1106 into l_itemname
1107 from mtl_system_items_kfv
1108 where organization_id = i_org_id
1109 and inventory_item_id = l_items(i);
1110 fnd_file.put_line(fnd_file.log, l_itemname);
1111 END LOOP;
1112 fnd_file.put_line(fnd_file.log,'');
1113
1114 -- Now update the usage_rate_or_amount and the net_yield_or_shrinkage_factor in CICD
1115 l_stmt_num := 60;
1116 FORALL i in l_items.FIRST..l_items.LAST
1117 UPDATE cst_item_cost_details
1118 SET usage_rate_or_amount = (usage_rate_or_amount * net_yield_or_shrinkage_factor),
1119 net_yield_or_shrinkage_factor = 1,
1120 last_update_date = sysdate,
1121 last_updated_by = l_user_id,
1122 last_update_login = l_login_id,
1123 request_id = l_request_id,
1124 program_application_id = l_pgm_app_id,
1125 program_id = l_pgm_id,
1126 program_update_date = sysdate
1127 WHERE cost_type_id = i_cost_type
1128 AND organization_id = i_org_id
1132
1129 AND inventory_item_id = l_items(i);
1130
1131 fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details related to the shrinkage rate.');
1133 l_err_msg := 'Forcing shrinkage rate to 0';
1134 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_err_msg);
1135 end if;
1136 end if; -- end of based on rollup affecting shrinkage rate
1137
1138 if (i_def_flag = 1 or i_def_flag = 3) then
1139 -- The defaulted_flag cannot be updated to YES for items with user defined costs, or for items that
1140 -- do not exist in the default cost type. The defaulted_flag should be set back to NO for these
1141 -- rows in CIC.
1142
1143 l_stmt_num := 70;
1144 if l_items.EXISTS(1) then
1145 l_items.DELETE; -- reset the collection
1146 end if;
1147
1148 UPDATE cst_item_costs cic
1149 SET cic.defaulted_flag = 2
1150 WHERE cic.cost_type_id = i_cost_type
1151 AND cic.organization_id = i_org_id
1152 AND (i_range = 1
1153 OR (i_range = 2
1154 AND cic.inventory_item_id = i_specific_item)
1155 OR (i_range = 3
1156 AND cic.inventory_item_id IN
1157 (SELECT msi1.inventory_item_id
1158 FROM mtl_system_items_kfv msi1
1159 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to))
1160 OR (i_range = 5
1161 AND cic.inventory_item_id IN
1162 (SELECT msi2.inventory_item_id
1163 FROM mtl_system_items msi2, mtl_item_categories mic, mtl_categories_kfv mc
1164 WHERE mic.organization_id = i_org_id
1165 AND mic.category_set_id = i_category_set
1166 AND mic.inventory_item_id = msi2.inventory_item_id
1167 AND mic.organization_id = msi2.organization_id
1168 AND mic.category_id = mc.category_id
1169 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)))
1170 AND cic.defaulted_flag = 1
1171 AND (EXISTS (SELECT 'X'
1172 FROM cst_item_cost_details cicd
1173 WHERE cicd.organization_id = cic.organization_id
1174 AND cicd.cost_type_id = cic.cost_type_id
1175 AND cicd.inventory_item_id = cic.inventory_item_id
1176 AND cicd.rollup_source_type = 1) -- user defined
1177 OR NOT EXISTS (SELECT 'X'
1178 FROM cst_item_costs cic1, cst_cost_types cct
1179 WHERE cic1.organization_id = cic.organization_id
1180 AND cic1.cost_type_id = cct.default_cost_type_id
1181 AND cct.cost_type_id = cic.cost_type_id
1182 AND cic1.inventory_item_id = cic.inventory_item_id))
1183 RETURNING cic.inventory_item_id BULK COLLECT INTO l_items;
1184
1185 -- print warning CANNOT UPDATE DEFAULT_FLAG!!!!
1186 if (l_items.COUNT > 0) then
1187 fnd_file.put_line(fnd_file.log,'');
1188 fnd_message.set_name('BOM', 'CST_SCC_CANT_UPDT_DEFAULT');
1189 fnd_message.set_token('NUMBER', to_char(l_items.COUNT));
1190
1191 if (l_orgcode IS NULL) then
1192 select organization_code
1193 into l_orgcode
1194 from mtl_parameters
1195 where organization_id = i_org_id;
1196 end if;
1197 fnd_message.set_token('ORG', l_orgcode);
1198
1199 if (l_costtype IS NULL) then
1200 select cost_type
1201 into l_costtype
1202 from cst_cost_types
1203 where cost_type_id = i_cost_type;
1204 end if;
1205 fnd_message.set_token('CT', l_costtype);
1206 fnd_file.put_line(fnd_file.log, fnd_message.get);
1207
1208 FOR i in 1..l_items.COUNT LOOP
1209 select concatenated_segments
1210 into l_itemname
1211 from mtl_system_items_kfv
1212 where organization_id = i_org_id
1213 and inventory_item_id = l_items(i);
1214 fnd_file.put_line(fnd_file.log, l_itemname);
1215 END LOOP;
1216 fnd_file.put_line(fnd_file.log,'');
1217
1218 l_err_msg := 'Cannot update defaulted_flag to YES';
1219 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',l_err_msg);
1220 end if;
1221
1222 end if;
1223
1224 if (i_def_flag = 2 or i_def_flag = 3) then
1225 -- When the defaulted flag is updated to NO, any subcosts with a source type of defaulted
1226 -- should be changed to user defined.
1227 l_stmt_num := 80;
1228
1229 IF (i_range =1) THEN
1230
1231 UPDATE cst_item_cost_details cicd
1232 SET cicd.rollup_source_type = 1
1233 WHERE cicd.rollup_source_type = 2
1234 AND cicd.cost_type_id = i_cost_type
1235 AND cicd.organization_id = i_org_id
1236 AND EXISTS (SELECT 'X'
1237 FROM cst_item_costs cic
1238 WHERE cic.organization_id = cicd.organization_id
1239 AND cic.cost_type_id = cicd.cost_type_id
1240 AND cic.inventory_item_id = cicd.inventory_item_id
1241 AND cic.defaulted_flag = 2
1242 );
1243
1244 ELSIF (i_range =2) THEN
1245
1246 UPDATE cst_item_cost_details cicd
1247 SET cicd.rollup_source_type = 1
1248 WHERE cicd.rollup_source_type = 2
1249 AND cicd.cost_type_id = i_cost_type
1250 AND cicd.organization_id = i_org_id
1251 AND cicd.inventory_item_id = i_specific_item
1252 AND EXISTS (SELECT 'X'
1253 FROM cst_item_costs cic
1254 WHERE cic.organization_id = cicd.organization_id
1255 AND cic.cost_type_id = cicd.cost_type_id
1256 AND cic.inventory_item_id = cicd.inventory_item_id
1257 AND cic.defaulted_flag = 2
1258 );
1259
1260 ELSIF (i_range =3) THEN
1261
1262 UPDATE cst_item_cost_details cicd
1263 SET cicd.rollup_source_type = 1
1264 WHERE cicd.rollup_source_type = 2
1265 AND cicd.cost_type_id = i_cost_type
1266 AND cicd.organization_id = i_org_id
1267 AND cicd.inventory_item_id IN (SELECT msi1.inventory_item_id FROM mtl_system_items_kfv msi1 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to)
1268 AND EXISTS (SELECT 'X'
1269 FROM cst_item_costs cic
1270 WHERE cic.organization_id = cicd.organization_id
1271 AND cic.cost_type_id = cicd.cost_type_id
1272 AND cic.inventory_item_id = cicd.inventory_item_id
1273 AND cic.defaulted_flag = 2
1274 );
1275
1276
1277 ELSIF (i_range =5) THEN
1278
1279 UPDATE cst_item_cost_details cicd
1280 SET cicd.rollup_source_type = 1
1281 WHERE cicd.rollup_source_type = 2
1282 AND cicd.cost_type_id = i_cost_type
1283 AND cicd.organization_id = i_org_id
1284 AND cicd.inventory_item_id IN (SELECT mic.inventory_item_id FROM mtl_item_categories mic, mtl_categories_kfv mc
1285 WHERE mic.organization_id = i_org_id
1286 AND mic.category_set_id = i_category_set
1287 AND mic.category_id = mc.category_id
1288 AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)
1289 AND EXISTS (SELECT 'X'
1290 FROM cst_item_costs cic
1291 WHERE cic.organization_id = cicd.organization_id
1292 AND cic.cost_type_id = cicd.cost_type_id
1293 AND cic.inventory_item_id = cicd.inventory_item_id
1294 AND cic.defaulted_flag = 2
1295 );
1296 /* Change this select for bug 4881571 */
1297
1298 END IF;
1299
1300 fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details making them user-defined.');
1301 end if;
1302
1303
1304 commit;
1305
1306 O_Err_Num := 0;
1307 O_Err_Msg := '';
1308
1309 EXCEPTION
1310
1311 when others then
1312 l_err_msg := 'CSTPUMEC.set_cost_controls - error in statement '||to_char(l_stmt_num)||': '|| substrb(SQLERRM,1,150);
1313 fnd_file.put_line(fnd_file.log,l_err_msg);
1314 fnd_file.put_line(fnd_file.log,'All changes rolled back.');
1315 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
1316 O_Err_Num := SQLCODE;
1317 O_Err_Msg := l_err_msg;
1318 ROLLBACK TO SAVEPOINT set_cost_controls_PUB;
1319
1320 END set_cost_controls;
1321
1322 END CSTPUMEC;