[Home] [Help]
PACKAGE BODY: APPS.CSTPPCIC
Source
1 PACKAGE BODY CSTPPCIC AS
2 /* $Header: CSTPCICB.pls 120.0 2005/05/25 03:49:36 appldev noship $ */
3
4 FUNCTION get_uom_conv_rate(
5 i_item_id IN NUMBER,
6 i_from_org_id IN NUMBER,
7 i_to_org_id IN NUMBER)
8 RETURN NUMBER IS
9
10 -- Local variables
11
12 l_conv_rate NUMBER := 1;
13 o_from_uom VARCHAR2(25);
14 o_to_uom VARCHAR2(25);
15 o_rate NUMBER := 1;
16 o_err_num NUMBER ;
17 o_err_code VARCHAR2(240) ;
18 o_err_msg VARCHAR2(240) ;
19
20 BEGIN
21
22 CSTPAVCP.get_snd_rcv_uom(i_item_id,
23 i_from_org_id,
24 i_to_org_id,
25 o_from_uom,
26 o_to_uom,
27 o_err_num,
28 o_err_code,
29 o_err_msg);
30
31 inv_convert.inv_um_conversion(o_from_uom,
32 o_to_uom,
33 i_item_id,
34 o_rate);
35
36 RETURN (o_rate);
37
38 END; /* End of get_uom_conv_rate */
39
40 /*---------------------------------------------------------------------------*
41 | PUBLIC PROCEDURE |
42 | copy_item_period_cost |
43 | |
44 | p_copy_option: |
45 | 1: Merge and update |
46 | 2: New cost only |
47 | 3: remove and replace |
48 | p_range: |
49 | 1: All items |
50 | 2: Specific Item |
51 | 5: Category Items |
52 | |
53 | This procedure defaults the following values: |
54 | |
55 | CST_ITEM_COSTS.defaulted_flag = 2 (Do not use default cost controls) |
56 | CST_ITEM_COSTS.cost_update_id = NULL |
57 | CST_ITEM_COSTS.based_on_rollup_flag = 1 |
58 | CST_ITEM_COST_DETAILS.basis_factor = 1 |
59 | CST_ITEM_COST_DETAILS.rollup_source_type = 1(User defined) |
60 | |
61 *----------------------------------------------------------------------------*/
62 PROCEDURE copy_item_period_cost(
63 errbuf OUT NOCOPY VARCHAR2,
64 retcode OUT NOCOPY NUMBER,
65 p_legal_entity IN NUMBER,
66 p_from_cost_type_id IN NUMBER,
67 p_from_cost_group_id IN NUMBER,
68 p_period_id IN NUMBER,
69 p_to_org_id IN NUMBER,
70 p_to_cost_type_id IN NUMBER,
71 p_material IN NUMBER,
72 p_material_overhead IN NUMBER,
73 p_resource IN NUMBER,
74 p_outside_processing IN NUMBER,
75 p_overhead IN NUMBER,
76 p_copy_option IN NUMBER,
77 p_range IN NUMBER,
78 p_item_dummy IN NUMBER,
79 p_category_dummy IN NUMBER,
80 p_specific_item_id IN NUMBER,
81 p_category_set_id IN NUMBER,
82 p_category_validate_flag IN VARCHAR2,
83 p_category_structure IN NUMBER,
84 p_category_id IN NUMBER,
85 p_last_updated_by IN NUMBER,
86 p_full_validate IN NUMBER)
87
88 IS
89
90 -- Local PL/SQL variable
91 --
92
93 cst_fail_uomconvert EXCEPTION;
94 cst_fail_parameters EXCEPTION;
95 conc_status BOOLEAN;
96 l_row_count NUMBER;
97 l_stmt_num NUMBER;
98 l_valid NUMBER;
99 l_master_org_id NUMBER;
100 l_program_id NUMBER;
101 l_prog_appl_id NUMBER;
102 l_login_id NUMBER;
103 l_request_id NUMBER;
104 l_user_id NUMBER;
105 l_grp_id NUMBER;
106
107
108 l_err_num NUMBER;
109 l_err_code VARCHAR2(240);
110 l_err_msg VARCHAR2(240);
111
112 --fptr utl_file.file_type; --debug
113
114 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
115 l_process_enabled_flag mtl_parameters.process_enabled_flag%TYPE;
116 l_organization_code mtl_parameters.organization_code%TYPE;
117 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
118
119 BEGIN
120
121 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 Start */
122 BEGIN
123 SELECT nvl(process_enabled_flag,'N')
124 , organization_code
125 INTO l_process_enabled_flag
126 , l_organization_code
127 FROM mtl_parameters
128 WHERE organization_id = p_to_org_id;
129
130 IF nvl(l_process_enabled_flag,'N') = 'Y' THEN
131 l_err_num := 30001;
132 fnd_message.set_name('GMF', 'GMF_PROCESS_ORG_ERROR');
133 fnd_message.set_token('ORGCODE', l_organization_code);
134 l_err_msg := FND_MESSAGE.Get;
135 l_err_msg := substrb('CSTPPCIC : ' || l_err_msg,1,240);
136 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
137 fnd_file.put_line(fnd_file.log,l_err_msg);
138 RETURN;
139 END IF;
140
141 EXCEPTION
142 WHEN no_data_found THEN
143 l_process_enabled_flag := 'N';
144 l_organization_code := NULL;
145 END;
146 /* Skip Process Organizations Cost Manager Changes - Anand Thiyagarajan - 26-Oct-2004 End */
147
148 --fptr:=utl_file.fopen('/sqlcom/log/dmt11irw','pcc.log','W'); --debug
149 --utl_file.put_line(fptr,'testing');
150
151 ----------------------------------------------------------------------------
152 -- Initializing Variables
153 ----------------------------------------------------------------------------
154 l_user_id := 0;
155 l_program_id := 0;
156 l_err_num := 0;
157 l_request_id := 0;
158 l_err_code := '';
159 l_err_msg := '';
160
161 ----------------------------------------------------------------------------
162 -- retrieving concurrent program information
163 ----------------------------------------------------------------------------
164 l_stmt_num := 10;
165
166
167 l_request_id := FND_GLOBAL.conc_request_id;
168 l_prog_appl_id := FND_GLOBAL.prog_appl_id;
169 l_user_id := FND_GLOBAL.user_id;
170 l_program_id := FND_GLOBAL.conc_program_id;
171 l_login_id := FND_GLOBAL.conc_login_id;
172
173 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Global Variables... ');
174 FND_FILE.PUT_LINE(FND_FILE.LOG, 'request_id... '||to_char(l_request_id));
175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'prog_appl_id... '||to_char(l_prog_appl_id));
176 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_user_id... '||to_char(l_user_id));
177 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_program_id... '||to_char(l_program_id));
178 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_login_id... '||to_char(l_login_id));
179
180 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Arguments... ');
181 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Legal Entity: '|| TO_CHAR(p_legal_entity));
182 FND_FILE.PUT_LINE(FND_FILE.LOG, 'From Cost Type: '
183 || TO_CHAR(p_from_cost_type_id));
184 FND_FILE.PUT_LINE(FND_FILE.LOG, 'From Cost Group: '
185 || TO_CHAR(p_from_cost_group_id));
186 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Period: ' || TO_CHAR(p_period_id));
187 FND_FILE.PUT_LINE(FND_FILE.LOG, 'To Organization: ' || TO_CHAR(p_to_org_id));
188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'To Cost Type: '
189 || TO_CHAR(p_to_cost_type_id));
190 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Material Subelement: '
191 || TO_CHAR(p_material));
192 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Material Overhead Subelement: '
193 || TO_CHAR(p_material_overhead));
194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Resource Subelement: '
195 || TO_CHAR(p_resource));
196 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Outside Processing Subelement: '
197 || TO_CHAR(p_outside_processing));
198 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Overhead Subelement: '
199 || TO_CHAR(p_overhead));
200 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Copy Option: ' || TO_CHAR(p_copy_option));
201 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Range: ' || TO_CHAR(p_range));
202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Item Dummy: ' || TO_CHAR(p_item_dummy));
203 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Dummy: '
204 || TO_CHAR(p_category_dummy));
205 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Specific Item: '
206 || TO_CHAR(p_specific_item_id));
207 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Set: '
208 || TO_CHAR(p_category_set_id));
209
210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Validate Flag: '
211 || p_category_validate_flag);
212 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category Structure: '
213 || TO_CHAR(p_category_structure));
214 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Category: '
215 || TO_CHAR(p_category_id));
216 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Last Updated by: '
217 || TO_CHAR(p_last_updated_by));
218 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Full Validate: '
219 || TO_CHAR(p_full_validate));
220
221
222 IF p_full_validate = 1 THEN
223
224 ----------------------------------------------------------------------------
225 -- Validating the parameters passed. This is not required if the call
226 -- is from the concurrent program, but will be required if it is called
227 -- as standalone.
228 ----------------------------------------------------------------------------
229
230
231 l_valid := 0;
232
233 -- Validate the Legal Entity.
234
235 l_stmt_num := 21;
236
237 SELECT 1
238 INTO l_valid
239 FROM cst_le_cost_types
240 WHERE legal_entity = p_legal_entity;
241
242 l_valid := 0;
243
244 -- Validate From Cost Type
245
246 l_stmt_num := 22;
247
248
249 select 1
250 into l_valid
251 from cst_cost_types cct
252 where cost_type_id = p_from_cost_type_id
253 and cost_type_id in
254 (select distinct clct.cost_type_id
255 from cst_le_cost_types clct
256 where clct.legal_entity = p_legal_entity
257 )
258 and nvl(cct.disable_date, sysdate+1) > sysdate;
259
260 l_valid := 0;
261
262
263 -- Validate From Cost Group
264
265 l_stmt_num := 23;
266
267
268 select 1
269 into l_valid
270 from dual
271 where p_from_cost_group_id in
272 (select distinct cost_group
273 from cst_cost_groups
274 where legal_entity = p_legal_entity
275 )
276 and exists
277 (select 1
278 from cst_pac_periods cpp
279 where legal_entity = p_legal_entity
280 and cost_type_id = p_from_cost_type_id
281 and cpp.pac_period_id in
282 (select cppp.pac_period_id
283 from cst_pac_process_phases cppp
284 where cppp.cost_group_id = p_from_cost_group_id
285 and cppp.pac_period_id = cpp.pac_period_id
286 and process_phase = 5
287 and process_status = 4
288 )
289 );
290
291
292 l_valid := 0;
293
294
295 -- Validate From Period
296
297 l_stmt_num := 24;
298
299
300 select 1
301 into l_valid
302 from dual
303 where p_period_id in
304 (select distinct pac_period_id
305 from cst_pac_process_phases
306 where cost_group_id = p_from_cost_group_id
307 and process_phase = 5
308 and process_status = 4
309 );
310
311
312 l_valid := 0;
313
314
315 -- Validate To Organization
316
317 l_stmt_num := 25;
318
319
320 select 1
321 into l_valid
322 from dual
323 where p_to_org_id in
324 (
325 (select distinct CCGA.organization_id
326 from cst_cost_group_assignments CCGA
327 where CCGA.cost_group_id = p_from_cost_group_id
328 )
329 UNION
330 (select distinct CCG.organization_id
331 from cst_cost_groups CCG
332 where CCG.cost_group_id = p_from_cost_group_id
333 )
334 );
335
336
337 l_valid := 0;
338
339 -- Validate To Cost Type
340
341 l_stmt_num := 26;
342
343
344 select 1
345 into l_valid
346 from dual
347 where p_to_cost_type_id in
348 (select distinct cost_type_id
349 from cst_cost_types
350 where organization_id = p_to_org_id
351 and cost_type_id not in (1,2,3)
352 and nvl(allow_updates_flag,1) = 1
353 and nvl(disable_date, sysdate+1) > sysdate
354 )
355 and p_to_cost_type_id not in
356 (select distinct cost_type_id
357 from cst_le_cost_types
358 );
359
360 l_valid := 0;
361
362
363 -- Validate To Material Subelement
364
365 l_stmt_num := 27;
366
367
368 select 1
369 into l_valid
370 from dual
371 where p_material in
372 (select resource_id
373 from bom_resources
374 where organization_id = p_to_org_id
375 and cost_element_id = 1
376 and default_basis_type = 1
377 and nvl(disable_date, sysdate+1) > sysdate
378 and nvl(allow_costs_flag,1) = 1
379 );
380
381
382 l_valid := 0;
383
384
385 -- Validate To Material Overhead Subelement
386
387
388 l_stmt_num := 28;
389
390
391 select 1
392 into l_valid
393 from dual
394 where p_material_overhead in
395 (select resource_id
396 from bom_resources
397 where organization_id = p_to_org_id
398 and cost_element_id = 2
399 and default_basis_type = 1
400 and nvl(disable_date, sysdate+1) > sysdate
401 and nvl(allow_costs_flag,1) = 1
402 );
403
404 l_valid := 0;
405
406
407 -- Validate To Resource Subelement
408
409 l_stmt_num := 29;
410
411
412 select 1
413 into l_valid
414 from dual
415 where p_resource in
416 (select resource_id
417 from bom_resources
418 where organization_id = p_to_org_id
419 and cost_element_id = 3
420 and default_basis_type = 1
421 and nvl(disable_date, sysdate+1) > sysdate
422 and nvl(allow_costs_flag,1) = 1
423 );
424
425 l_valid := 0;
426
427
428 -- Validate To Outside Processing Subelement
429
430
431 l_stmt_num := 30;
432
433
434 select 1
435 into l_valid
436 from dual
437 where p_outside_processing in
438 (select resource_id
439 from bom_resources
440 where organization_id = p_to_org_id
441 and cost_element_id = 4
442 and default_basis_type = 1
443 and nvl(disable_date, sysdate+1) > sysdate
444 and nvl(allow_costs_flag,1) = 1
445 );
446
447 l_valid := 0;
448
449
450 -- Validate To Overhead Subelement
451
452 l_stmt_num := 31;
453
454
455 select 1
456 into l_valid
457 from dual
458 where p_overhead in
459 (select resource_id
460 from bom_resources
461 where organization_id = p_to_org_id
462 and cost_element_id = 5
463 and default_basis_type = 1
464 and nvl(disable_date, sysdate+1) > sysdate
465 and nvl(allow_costs_flag,1) = 1
466 );
467
468 l_valid := 0;
469
470 -- what about validating categories/category_set ????
471
472 -- Validate Copy Option
473
474 l_stmt_num := 32;
475
476
477 select 1
478 into l_valid
479 from dual
480 where p_copy_option in (1,2,3);
481
482 l_valid := 0;
483
484
485 -- Validate Range
486
487 l_stmt_num := 33;
488
489
490 select 1
491 into l_valid
492 from dual
493 where p_range in (1,2,5);
494
495
496 IF (l_valid <> 1) THEN
497 RAISE CST_FAIL_PARAMETERS;
498 END IF;
499
500
501 END IF; -- check for p_validate
502
503
504
505 ----------------------------------------------------------------------------
506 -- Get the item master organization
507 ----------------------------------------------------------------------------
508
509 l_stmt_num := 0;
510
511 SELECT organization_id
512 INTO l_master_org_id
513 FROM cst_cost_groups ccg
514 WHERE ccg.cost_group_id = p_from_cost_group_id;
515
516 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
517 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Master Org: ' || TO_CHAR(l_master_org_id));
518 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
519
520 IF p_copy_option = 1 THEN
521
522
523 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Merge and Update Costs');
524 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
525
526 --------------------------------------------------------------------------
527 -- Merge and update existing costs
528 -- Logic:
529 -- * Copy the based_on_rollup_flag for the p_to_cost_type_id items in CIC
530 -- to cst_item_costs_interface
531 -- * Delete the p_to_cost_type_id items in CIC, CICD, only if they also
532 -- have p_from_cost_type_id.
533 -- * Items get their p_to_cost_type_id costs from p_from_cost_type_id.
534 -- The based_on_rollup_flag is copied from CICI
535 -- * Items that did not have p_from_cost_type_id are left untouched.
536 --------------------------------------------------------------------------
537
538
539
540 --------------------------------------------------------------------------
541 -- step 1> Deleting existing cost information from CICD
542 -- for the p_to_cost_type_id, p_to_org_id, item
543 --------------------------------------------------------------------------
544
545 l_stmt_num := 35;
546
547
548 DELETE cst_item_cost_details CICD
549 WHERE CICD.cost_type_id = p_to_cost_type_id
550 AND CICD.organization_id = p_to_org_id
551 AND (p_range = 1
552 OR
553 (p_range = 2 AND CICD.inventory_item_id = p_specific_item_id)
554 OR
555 EXISTS
556 (SELECT NULL
557 FROM mtl_item_categories MIC
558 WHERE MIC.organization_id = l_master_org_id
559 AND MIC.category_id = p_category_id
560 AND MIC.category_set_id = p_category_set_id
561 AND MIC.inventory_item_id = CICD.inventory_item_id
562 AND p_range = 5)
563 )
564 AND EXISTS
565 (SELECT NULL
566 FROM cst_pac_item_cost_details cpicd,
567 cst_pac_item_costs cpic
568 WHERE cpicd.cost_layer_id = cpic.cost_layer_id
569 AND cpic.cost_group_id = p_from_cost_group_id
570 AND cpic.pac_period_id = p_period_id
571 AND cpic.inventory_item_id = cicd.inventory_item_id);
572
573
574 l_row_count := 0;
575 l_row_count := SQL%ROWCOUNT;
576
577 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
578 ||' Rows deleted from CICD');
579
580 --------------------------------------------------------------------------
581 -- step 2> Copying rollup flags, then Deleting existing cost information from CIC
582 -- for the p_to_cost_type_id, p_to_org_id, item
583 --------------------------------------------------------------------------
584
585 l_stmt_num := 37;
586
587 l_grp_id := 0;
588
589 SELECT CST_LISTS_S.NEXTVAL INTO l_grp_id
590 FROM dual;
591
592 INSERT INTO cst_item_costs_interface
593 ( inventory_item_id
594 , cost_type_id
595 , based_on_rollup_flag
596 , group_id
597 )
598 SELECT
599 inventory_item_id
600 , p_to_cost_type_id
601 , based_on_rollup_flag
602 , l_grp_id
603 FROM cst_item_costs CIC
604 WHERE CIC.cost_type_id = p_to_cost_type_id
605 AND CIC.organization_id = p_to_org_id
606 AND (p_range = 1
607 OR
608 (p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
609 OR
610 EXISTS
611 (SELECT NULL
612 FROM mtl_item_categories MIC
613 WHERE MIC.organization_id = l_master_org_id
614 AND MIC.category_id = p_category_id
615 AND MIC.category_set_id = p_category_set_id
616 AND MIC.inventory_item_id = CIC.inventory_item_id
617 AND p_range = 5)
618 )
619 AND EXISTS
620 (SELECT NULL
621 FROM cst_pac_item_costs cpic
622 WHERE cpic.cost_group_id = p_from_cost_group_id
623 AND cpic.pac_period_id = p_period_id
624 AND cpic.inventory_item_id = cic.inventory_item_id);
625
626 l_row_count := 0;
627 l_row_count := SQL%ROWCOUNT;
628
629 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
630 ||' Rollup Flags backed up from CIC to CICI');
631
632 l_stmt_num := 40;
633
634
635 DELETE cst_item_costs CIC
636 WHERE CIC.cost_type_id = p_to_cost_type_id
637 AND CIC.organization_id = p_to_org_id
638 AND (p_range = 1
639 OR
640 (p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
641 OR
642 EXISTS
643 (SELECT NULL
644 FROM mtl_item_categories MIC
645 WHERE MIC.organization_id = l_master_org_id
646 AND MIC.category_id = p_category_id
647 AND MIC.category_set_id = p_category_set_id
648 AND MIC.inventory_item_id = CIC.inventory_item_id
649 AND p_range = 5)
650 )
651 AND EXISTS
652 (SELECT NULL
653 FROM cst_pac_item_costs cpic
654 WHERE cpic.cost_group_id = p_from_cost_group_id
655 AND cpic.pac_period_id = p_period_id
656 AND cpic.inventory_item_id = cic.inventory_item_id);
657
658 l_row_count := 0;
659 l_row_count := SQL%ROWCOUNT;
660
661 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
662 ||' Rows deleted from CIC');
663
664
665 --------------------------------------------------------------------------
666 -- step 3> Inserting costs from CPIC
667 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CIC
668 -- and removing backed up rollup flags from cst_item_costs_interface
669 --------------------------------------------------------------------------
670
671
672 l_stmt_num := 50;
673
674
675 INSERT INTO cst_item_costs
676 ( inventory_item_id
677 , organization_id
678 , cost_type_id
679 , last_update_date
680 , last_updated_by
681 , creation_date
682 , created_by
683 , last_update_login
684 , inventory_asset_flag
685 , lot_size
686 , based_on_rollup_flag
687 , shrinkage_rate
688 , defaulted_flag
689 , cost_update_id
690 , pl_material
691 , pl_material_overhead
692 , pl_resource
693 , pl_outside_processing
694 , pl_overhead
695 , tl_material
696 , tl_material_overhead
697 , tl_resource
698 , tl_outside_processing
699 , tl_overhead
700 , material_cost
701 , material_overhead_cost
702 , resource_cost
703 , outside_processing_cost
704 , overhead_cost
705 , pl_item_cost
706 , tl_item_cost
707 , item_cost
708 , unburdened_cost
709 , burden_cost
710 , request_id
711 , program_application_id
712 , program_id
713 , program_update_date
714 )
715 SELECT
716 CPIC.inventory_item_id
717 , p_to_org_id
718 , p_to_cost_type_id
719 , SYSDATE
720 , l_user_id
721 , SYSDATE
722 , l_user_id
723 , l_login_id -- last update login
724 , decode(MSI.inventory_asset_flag , 'Y', 1, 2)
725 , nvl(MSI.std_lot_size,1)
726 , nvl(BORF.based_on_rollup_flag, nvl(dBORF.based_on_rollup_flag,1)) -- set the borf to pre-existing value/default
727 , nvl(MSI.shrinkage_rate , 0)
728 , 2 -- defaulted_flag
729 , NULL -- cost_update_id
730 , CPIC.pl_material
731 , CPIC.pl_material_overhead
732 , CPIC.pl_resource
733 , CPIC.pl_outside_processing
734 , CPIC.pl_overhead
735 , CPIC.tl_material
736 , CPIC.tl_material_overhead
737 , CPIC.tl_resource
738 , CPIC.tl_outside_processing
739 , CPIC.tl_overhead
740 , CPIC.material_cost *
741 get_uom_conv_rate(CPIC.inventory_item_id,
742 l_master_org_id,
743 p_to_org_id)
744 , CPIC.material_overhead_cost *
745 get_uom_conv_rate(CPIC.inventory_item_id,
746 l_master_org_id,
747 p_to_org_id)
748 , CPIC.resource_cost *
749 get_uom_conv_rate(CPIC.inventory_item_id,
750 l_master_org_id,
751 p_to_org_id)
752 , CPIC.outside_processing_cost *
753 get_uom_conv_rate(CPIC.inventory_item_id,
754 l_master_org_id,
755 p_to_org_id)
756 , CPIC.overhead_cost *
757 get_uom_conv_rate(CPIC.inventory_item_id,
758 l_master_org_id,
759 p_to_org_id)
760 , CPIC.pl_item_cost *
761 get_uom_conv_rate(CPIC.inventory_item_id,
762 l_master_org_id,
763 p_to_org_id)
764 , CPIC.tl_item_cost *
765 get_uom_conv_rate(CPIC.inventory_item_id,
766 l_master_org_id,
767 p_to_org_id)
768 , CPIC.item_cost *
769 get_uom_conv_rate(CPIC.inventory_item_id,
770 l_master_org_id,
771 p_to_org_id)
772 , CPIC.unburdened_cost *
773 get_uom_conv_rate(CPIC.inventory_item_id,
774 l_master_org_id,
775 p_to_org_id)
776 , CPIC.burden_cost *
777 get_uom_conv_rate(CPIC.inventory_item_id,
778 l_master_org_id,
779 p_to_org_id)
780 , l_request_id
781 , 702
782 , l_program_id
783 , SYSDATE
784 FROM cst_pac_item_costs CPIC,
785 mtl_system_items MSI,
786 (SELECT inventory_item_id, based_on_rollup_flag
787 FROM cst_item_costs_interface
788 WHERE group_id = l_grp_id) BORF, -- based_on_rollup_flag backed up from destination cost type
789 (SELECT cic.inventory_item_id, cic.based_on_rollup_flag
790 FROM cst_cost_types cct, cst_item_costs cic
791 WHERE cic.organization_id = p_to_org_id
792 AND cct.cost_type_id = p_to_cost_type_id
793 AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
794 WHERE CPIC.pac_period_id = p_period_id
795 AND CPIC.cost_group_id = p_from_cost_group_id
796 AND CPIC.inventory_item_id = MSI.inventory_item_id
797 AND MSI.organization_id = p_to_org_id
798 AND BORF.inventory_item_id(+) = CPIC.inventory_item_id
799 AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
800 AND (p_range = 1
801 OR
802 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
803 OR
804 EXISTS
805 (SELECT NULL
806 FROM mtl_item_categories MIC
807 WHERE MIC.organization_id = l_master_org_id
808 AND MIC.category_id = p_category_id
809 AND MIC.category_set_id = p_category_set_id
810 AND MIC.inventory_item_id = CPIC.inventory_item_id
811 AND p_range = 5)
812 );
813
814 l_row_count := 0;
815 l_row_count := SQL%ROWCOUNT;
816
817 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
818 ||' Rows Inserted into CIC');
819
820 l_stmt_num := 55;
821
822 DELETE cst_item_costs_interface
823 WHERE group_id = l_grp_id;
824
825 l_row_count := 0;
826 l_row_count := SQL%ROWCOUNT;
827
828 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
829 ||' Backup Rows deleted from CICI');
830
831 --------------------------------------------------------------------------
832 -- step 4> Getting costs from CPICD having the cost_layer_id
833 -- as used above in CPIC and inserting
834 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CICD
835 --------------------------------------------------------------------------
836
837 l_stmt_num := 60;
838
839
840 INSERT INTO cst_item_cost_details
841 ( inventory_item_id
842 , organization_id
843 , cost_type_id
844 , last_update_date
845 , last_updated_by
846 , creation_date
847 , created_by
848 , last_update_login
849 , level_type
850 , resource_id
851 , usage_rate_or_amount
852 , basis_type
853 , basis_resource_id
854 , basis_factor
855 , net_yield_or_shrinkage_factor
856 , item_cost
857 , cost_element_id
858 , rollup_source_type
859 , request_id
860 , program_application_id
861 , program_id
862 , program_update_date
863 )
864 SELECT
865 CPIC.inventory_item_id
866 , p_to_org_id
867 , p_to_cost_type_id
868 , SYSDATE
869 , l_user_id
870 , SYSDATE
871 , l_user_id
872 , l_login_id
873 , CPICD.level_type
874 , decode(CPICD.cost_element_id, -- resource id
875 1, p_material,
876 2, p_material_overhead,
877 3, p_resource,
878 4, p_outside_processing,
879 5, p_overhead)
880 , CPICD.item_cost *
881 get_uom_conv_rate(CPIC.inventory_item_id,
882 l_master_org_id,
883 p_to_org_id) --usage_rate
884 , 1
885 , NULL -- Basis resource_id
886 , 1 -- basis_factor
887 , 1 -- net_yield_or_shrinkage_factor
888 , CPICD.item_cost *
889 get_uom_conv_rate(CPIC.inventory_item_id,
890 l_master_org_id,
891 p_to_org_id)
892 , CPICD.cost_element_id
893 , 1 -- rollup_source_type
894 , l_request_id
895 , 702
896 , l_program_id
897 , SYSDATE
898 FROM cst_pac_item_costs CPIC,
899 cst_pac_item_cost_details CPICD,
900 mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
901 WHERE CPIC.pac_period_id = p_period_id
902 AND CPIC.cost_group_id = p_from_cost_group_id
903 AND CPIC.cost_layer_id = CPICD.cost_layer_id
904 AND MSI.inventory_item_id = CPIC.inventory_item_id
905 AND MSI.organization_id = p_to_org_id
906 AND (p_range = 1
907 OR
908 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
909 OR
910 EXISTS
911 (SELECT NULL
912 FROM mtl_item_categories MIC
913 WHERE MIC.organization_id = l_master_org_id
914 AND MIC.category_id = p_category_id
915 AND MIC.category_set_id = p_category_set_id
916 AND MIC.inventory_item_id = CPIC.inventory_item_id
917 AND p_range = 5)
918 );
919
920 l_row_count := 0;
921 l_row_count := SQL%ROWCOUNT;
922
923 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
924 ||' Rows Inserted into CICD: ');
925
926
927 END IF; -- If p_copy_option
928
929
930 IF p_copy_option = 2 THEN
931
932 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert New Cost Information Only');
933
934 --------------------------------------------------------------------------
935 -- New Cost Information Only
936 -- Logic:
937 --* If Items already have a p_to_cost_type_id, then do not
938 -- touch those items.
939 --* All other Items get their p_to_cost_type_id costs
940 -- from p_from_cost_type_id.
941 --------------------------------------------------------------------------
942
943 --------------------------------------------------------------------------
944 -- step 1> Inserting costs from CPIC
945 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CIC
946 --------------------------------------------------------------------------
947
948
949 l_stmt_num := 70;
950
951
952 INSERT INTO cst_item_costs
953 ( inventory_item_id
954 , organization_id
955 , cost_type_id
956 , last_update_date
957 , last_updated_by
958 , creation_date
959 , created_by
960 , last_update_login
961 , inventory_asset_flag
962 , lot_size
963 , based_on_rollup_flag
964 , shrinkage_rate
965 , defaulted_flag
966 , cost_update_id
967 , pl_material
968 , pl_material_overhead
969 , pl_resource
970 , pl_outside_processing
971 , pl_overhead
972 , tl_material
973 , tl_material_overhead
974 , tl_resource
975 , tl_outside_processing
976 , tl_overhead
977 , material_cost
978 , material_overhead_cost
979 , resource_cost
980 , outside_processing_cost
981 , overhead_cost
982 , pl_item_cost
983 , tl_item_cost
984 , item_cost
985 , unburdened_cost
986 , burden_cost
987 , request_id
988 , program_application_id
989 , program_id
990 , program_update_date
991 )
992 SELECT
993 CPIC.inventory_item_id
994 , p_to_org_id
995 , p_to_cost_type_id
996 , SYSDATE
997 , l_user_id
998 , SYSDATE
999 , l_user_id
1000 , l_login_id
1001 , decode(MSI.inventory_asset_flag,'Y',1,2)
1002 , nvl(MSI.std_lot_size,1)
1003 , nvl(dBORF.based_on_rollup_flag,1) -- default the borf to 1 if not in default cost type
1004 , nvl(MSI.shrinkage_rate,0)
1005 , 2 -- defaulted flag
1006 , NULL -- cost update id
1007 , CPIC.pl_material
1008 , CPIC.pl_material_overhead
1009 , CPIC.pl_resource
1010 , CPIC.pl_outside_processing
1011 , CPIC.pl_overhead
1012 , CPIC.tl_material
1013 , CPIC.tl_material_overhead
1014 , CPIC.tl_resource
1015 , CPIC.tl_outside_processing
1016 , CPIC.tl_overhead
1017 , CPIC.material_cost *
1018 get_uom_conv_rate(CPIC.inventory_item_id,
1019 l_master_org_id,
1020 p_to_org_id)
1021 , CPIC.material_overhead_cost *
1022 get_uom_conv_rate(CPIC.inventory_item_id,
1023 l_master_org_id,
1024 p_to_org_id)
1025 , CPIC.resource_cost *
1026 get_uom_conv_rate(CPIC.inventory_item_id,
1027 l_master_org_id,
1028 p_to_org_id)
1029 , CPIC.outside_processing_cost *
1030 get_uom_conv_rate(CPIC.inventory_item_id,
1031 l_master_org_id,
1032 p_to_org_id)
1033 , CPIC.overhead_cost *
1034 get_uom_conv_rate(CPIC.inventory_item_id,
1035 l_master_org_id,
1036 p_to_org_id)
1037 , CPIC.pl_item_cost *
1038 get_uom_conv_rate(CPIC.inventory_item_id,
1039 l_master_org_id,
1040 p_to_org_id)
1041 , CPIC.tl_item_cost *
1042 get_uom_conv_rate(CPIC.inventory_item_id,
1043 l_master_org_id,
1044 p_to_org_id)
1045 , CPIC.item_cost *
1046 get_uom_conv_rate(CPIC.inventory_item_id,
1047 l_master_org_id,
1048 p_to_org_id)
1049 , CPIC.unburdened_cost *
1050 get_uom_conv_rate(CPIC.inventory_item_id,
1051 l_master_org_id,
1052 p_to_org_id)
1053 , CPIC.burden_cost *
1054 get_uom_conv_rate(CPIC.inventory_item_id,
1055 l_master_org_id,
1056 p_to_org_id)
1057
1058 , l_request_id
1059 , 702
1060 , l_program_id
1061 , SYSDATE
1062 FROM cst_pac_item_costs CPIC,
1063 mtl_system_items MSI,
1064 (SELECT cic.inventory_item_id, based_on_rollup_flag
1065 FROM cst_cost_types cct, cst_item_costs cic
1066 WHERE cic.organization_id = p_to_org_id
1067 AND cct.cost_type_id = p_to_cost_type_id
1068 AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
1069
1070 WHERE CPIC.pac_period_id = p_period_id
1071 AND CPIC.cost_group_id = p_from_cost_group_id
1072 AND CPIC.inventory_item_id = MSI.inventory_item_id
1073 AND MSI.organization_id = p_to_org_id
1074 AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
1075 AND (p_range = 1
1076 OR
1077 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
1078 OR
1079 EXISTS
1080 (SELECT NULL
1081 FROM mtl_item_categories MIC
1082 WHERE MIC.organization_id = l_master_org_id
1083 AND MIC.category_id = p_category_id
1084 AND MIC.category_set_id = p_category_set_id
1085 AND MIC.inventory_item_id = CPIC.inventory_item_id
1086 AND p_range = 5)
1087 )
1088 AND NOT EXISTS
1089 (SELECT 'x'
1090 FROM cst_item_costs CIC
1091 WHERE CIC.cost_type_id = p_to_cost_type_id
1092 AND CIC.organization_id = p_to_org_id
1093 AND CIC.inventory_item_id = CPIC.inventory_item_id);
1094
1095 l_row_count := 0;
1096 l_row_count := SQL%ROWCOUNT;
1097
1098 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1099 ||' Rows Inserted into CIC');
1100
1101
1102
1103 --------------------------------------------------------------------------
1104 -- step 2> Getting costs from CPICD having the cost_layer_id
1105 -- as used above in CPIC and inserting
1106 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CICD
1107 --------------------------------------------------------------------------
1108
1109
1110 l_stmt_num := 80;
1111
1112
1113 INSERT INTO cst_item_cost_details
1114 ( inventory_item_id
1115 , organization_id
1116 , cost_type_id
1117 , last_update_date
1118 , last_updated_by
1119 , creation_date
1120 , created_by
1121 , last_update_login
1122 , level_type
1123 , resource_id
1124 , usage_rate_or_amount
1125 , basis_type
1126 , basis_resource_id
1127 , basis_factor
1128 , net_yield_or_shrinkage_factor
1129 , item_cost
1130 , cost_element_id
1131 , rollup_source_type
1132 , request_id
1133 , program_application_id
1134 , program_id
1135 , program_update_date
1136 )
1137 SELECT
1138 CPIC.inventory_item_id
1139 , p_to_org_id
1140 , p_to_cost_type_id
1141 , SYSDATE
1142 , l_user_id
1143 , SYSDATE
1144 , l_user_id
1145 , l_login_id
1146 , CPICD.level_type
1147 , decode(CPICD.cost_element_id, -- resource id
1148 1, p_material,
1149 2, p_material_overhead,
1150 3, p_resource,
1151 4, p_outside_processing,
1152 5, p_overhead)
1153 , CPICD.item_cost *
1154 get_uom_conv_rate(CPIC.inventory_item_id,
1155 l_master_org_id,
1156 p_to_org_id) --usage_rate
1157 , 1 -- basis_type
1158 , NULL -- basis_resource_id
1159 , 1 -- basis_factor
1160 , 1 -- net_yield_or_shrinkage_factor
1161 , CPICD.item_cost*
1162 get_uom_conv_rate(CPIC.inventory_item_id,
1163 l_master_org_id,
1164 p_to_org_id)
1165 , CPICD.cost_element_id
1166 , 1 -- rollup_source_type
1167 , l_request_id
1168 , 702
1169 , l_program_id
1170 , SYSDATE
1171 FROM cst_pac_item_costs CPIC,
1172 cst_pac_item_cost_details CPICD,
1173 mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
1174 WHERE CPIC.pac_period_id = p_period_id
1175 AND CPIC.cost_group_id = p_from_cost_group_id
1176 AND CPIC.cost_layer_id = CPICD.cost_layer_id
1177 AND MSI.inventory_item_id = CPIC.inventory_item_id
1178 AND MSI.organization_id = p_to_org_id
1179 AND (p_range = 1
1180 OR
1181 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
1182 OR
1183 EXISTS
1184 (SELECT NULL
1185 FROM mtl_item_categories MIC
1186 WHERE MIC.organization_id = l_master_org_id
1187 AND MIC.category_id = p_category_id
1188 AND MIC.category_set_id = p_category_set_id
1189 AND MIC.inventory_item_id = CPIC.inventory_item_id
1190 AND p_range = 5)
1191 )
1192 -- Bug 2619991 - Commented out this NOT EXISTS check.
1193 -- Statment 70 already makes this check and then inserts to CIC, so this always fails.
1194 /*
1195 AND NOT EXISTS
1196 (SELECT 'x'
1197 FROM cst_item_costs CIC
1198 WHERE CIC.cost_type_id = p_to_cost_type_id
1199 AND CIC.organization_id = p_to_org_id
1200 AND CIC.inventory_item_id = CPIC.inventory_item_id)
1201 */
1202 AND NOT EXISTS
1203 (SELECT 'x'
1204 FROM cst_item_cost_details CICD
1205 WHERE CICD.cost_type_id = p_to_cost_type_id
1206 AND CICD.organization_id = p_to_org_id
1207 AND CICD.inventory_item_id = CPIC.inventory_item_id);
1208
1209 l_row_count := 0;
1210 l_row_count := SQL%ROWCOUNT;
1211
1212 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1213 ||' Rows Inserted into CICD');
1214
1215 END IF; -- If p_copy_option = 2
1216
1217
1218 IF p_copy_option = 3 THEN
1219
1220 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Remove and Replace Cost Information');
1221 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
1222
1223 --------------------------------------------------------------------------
1224 --Remove and replace all cost information
1225 -- Logic:
1226 -- * Copy the based_on_rollup_flag for the p_to_cost_type_id items in CIC
1227 -- to cst_item_costs_interface
1228 -- * Delete the p_to_cost_type_id items in CIC, CICD.
1229 -- * Items get their p_to_cost_type_id costs from p_from_cost_type_id.
1230 -- The based_on_rollup_flag is copied from CICI for items that had rows
1231 --------------------------------------------------------------------------
1232
1233
1234 --------------------------------------------------------------------------
1235 -- step 1> Deleting existing cost information from CICD
1236 -- for the p_to_cost_type_id, p_to_org_id, item
1237 --------------------------------------------------------------------------
1238
1239 l_stmt_num := 90;
1240
1241
1242 DELETE cst_item_cost_details CICD
1243 WHERE CICD.cost_type_id = p_to_cost_type_id
1244 AND CICD.organization_id = p_to_org_id
1245 AND (p_range = 1
1246 OR
1247 (p_range = 2 AND CICD.inventory_item_id = p_specific_item_id)
1248 OR
1249 EXISTS
1250 (SELECT NULL
1251 FROM mtl_item_categories MIC
1252 WHERE MIC.organization_id = l_master_org_id
1253 AND MIC.category_id = p_category_id
1254 AND MIC.category_set_id = p_category_set_id
1255 AND MIC.inventory_item_id = CICD.inventory_item_id
1256 AND p_range = 5)
1257 );
1258
1259 l_row_count := 0;
1260 l_row_count := SQL%ROWCOUNT;
1261
1262 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1263 ||' Rows deleted from CICD');
1264
1265
1266 --------------------------------------------------------------------------
1267 -- step 2> Copying rollup flags, then Deleting existing cost information from CIC
1268 -- for the p_to_cost_type_id, p_to_org_id, item
1269 --------------------------------------------------------------------------
1270
1271 l_stmt_num := 95;
1272
1273 l_grp_id := 0;
1274
1275 SELECT CST_LISTS_S.NEXTVAL INTO l_grp_id
1276 FROM dual;
1277
1278 INSERT INTO cst_item_costs_interface
1279 ( inventory_item_id
1280 , cost_type_id
1281 , based_on_rollup_flag
1282 , group_id
1283 )
1284 SELECT
1285 inventory_item_id
1286 , p_to_cost_type_id
1287 , based_on_rollup_flag
1288 , l_grp_id
1289 FROM cst_item_costs CIC
1290 WHERE CIC.cost_type_id = p_to_cost_type_id
1291 AND CIC.organization_id = p_to_org_id
1292 AND (p_range = 1
1293 OR
1294 (p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
1295 OR
1296 EXISTS
1297 (SELECT NULL
1298 FROM mtl_item_categories MIC
1299 WHERE MIC.organization_id = l_master_org_id
1300 AND MIC.category_id = p_category_id
1301 AND MIC.category_set_id = p_category_set_id
1302 AND MIC.inventory_item_id = CIC.inventory_item_id
1303 AND p_range = 5)
1304 )
1305 AND EXISTS
1306 (SELECT NULL
1307 FROM cst_pac_item_costs cpic
1308 WHERE cpic.cost_group_id = p_from_cost_group_id
1309 AND cpic.pac_period_id = p_period_id
1310 AND cpic.inventory_item_id = cic.inventory_item_id);
1311
1312 l_row_count := 0;
1313 l_row_count := SQL%ROWCOUNT;
1314
1315 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1316 ||' Rollup Flags backed up from CIC to CICI');
1317
1318
1319 l_stmt_num := 100;
1320
1321
1322 DELETE cst_item_costs CIC
1323 WHERE CIC.cost_type_id = p_to_cost_type_id
1324 AND CIC.organization_id = p_to_org_id
1325 AND (p_range = 1
1326 OR
1327 (p_range = 2 AND CIC.inventory_item_id = p_specific_item_id)
1328 OR
1329 EXISTS
1330 (SELECT NULL
1331 FROM mtl_item_categories MIC
1332 WHERE MIC.organization_id = l_master_org_id
1333 AND MIC.category_id = p_category_id
1334 AND MIC.category_set_id = p_category_set_id
1335 AND MIC.inventory_item_id = CIC.inventory_item_id
1336 AND p_range = 5)
1337 );
1338
1339 l_row_count := 0;
1340 l_row_count := SQL%ROWCOUNT;
1341
1342 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1343 ||' Rows deleted from CIC');
1344
1345
1346 --------------------------------------------------------------------------
1347 -- step 3> Inserting costs from CPIC
1348 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CIC
1349 -- and removing backed up rollup flags from cst_item_costs_interface
1350 --------------------------------------------------------------------------
1351
1352
1353 l_stmt_num := 110;
1354
1355
1356 INSERT INTO cst_item_costs
1357 ( inventory_item_id
1358 , organization_id
1359 , cost_type_id
1360 , last_update_date
1361 , last_updated_by
1362 , creation_date
1363 , created_by
1364 , last_update_login
1365 , inventory_asset_flag
1366 , lot_size
1367 , based_on_rollup_flag
1368 , shrinkage_rate
1369 , defaulted_flag
1370 , cost_update_id
1371 , pl_material
1372 , pl_material_overhead
1373 , pl_resource
1374 , pl_outside_processing
1375 , pl_overhead
1376 , tl_material
1377 , tl_material_overhead
1378 , tl_resource
1379 , tl_outside_processing
1380 , tl_overhead
1381 , material_cost
1382 , material_overhead_cost
1383 , resource_cost
1384 , outside_processing_cost
1385 , overhead_cost
1386 , pl_item_cost
1387 , tl_item_cost
1388 , item_cost
1389 , unburdened_cost
1390 , burden_cost
1391 , request_id
1392 , program_application_id
1393 , program_id
1394 , program_update_date
1395 )
1396 SELECT
1397 CPIC.inventory_item_id
1398 , p_to_org_id
1399 , p_to_cost_type_id
1400 , SYSDATE
1401 , l_user_id
1402 , SYSDATE
1403 , l_user_id
1404 , l_login_id
1405 , decode(MSI.inventory_asset_flag, 'Y', 1, 2) --inventory_asset_flag
1406 , nvl(MSI.std_lot_size,1)
1407 , nvl(BORF.based_on_rollup_flag, nvl(dBORF.based_on_rollup_flag,1)) -- set the borf to pre-existing value/default
1408 , nvl(MSI.shrinkage_rate,0)
1409 , 2 -- defaulted_flag
1410 , NULL -- cost_update_id
1411 , CPIC.pl_material
1412 , CPIC.pl_material_overhead
1413 , CPIC.pl_resource
1414 , CPIC.pl_outside_processing
1415 , CPIC.pl_overhead
1416 , CPIC.tl_material
1417 , CPIC.tl_material_overhead
1418 , CPIC.tl_resource
1419 , CPIC.tl_outside_processing
1420 , CPIC.tl_overhead
1421 , CPIC.material_cost *
1422 get_uom_conv_rate(CPIC.inventory_item_id,
1423 l_master_org_id,
1424 p_to_org_id)
1425 , CPIC.material_overhead_cost *
1426 get_uom_conv_rate(CPIC.inventory_item_id,
1427 l_master_org_id,
1428 p_to_org_id)
1429 , CPIC.resource_cost *
1430 get_uom_conv_rate(CPIC.inventory_item_id,
1431 l_master_org_id,
1432 p_to_org_id)
1433 , CPIC.outside_processing_cost *
1434 get_uom_conv_rate(CPIC.inventory_item_id,
1435 l_master_org_id,
1436 p_to_org_id)
1437 , CPIC.overhead_cost *
1438 get_uom_conv_rate(CPIC.inventory_item_id,
1439 l_master_org_id,
1440 p_to_org_id)
1441 , CPIC.pl_item_cost *
1442 get_uom_conv_rate(CPIC.inventory_item_id,
1443 l_master_org_id,
1444 p_to_org_id)
1445 , CPIC.tl_item_cost *
1446 get_uom_conv_rate(CPIC.inventory_item_id,
1447 l_master_org_id,
1448 p_to_org_id)
1449 , CPIC.item_cost *
1450 get_uom_conv_rate(CPIC.inventory_item_id,
1451 l_master_org_id,
1452 p_to_org_id)
1453 , CPIC.unburdened_cost *
1454 get_uom_conv_rate(CPIC.inventory_item_id,
1455 l_master_org_id,
1456 p_to_org_id)
1457 , CPIC.burden_cost *
1458 get_uom_conv_rate(CPIC.inventory_item_id,
1459 l_master_org_id,
1460 p_to_org_id)
1461 , l_request_id
1462 , 702
1463 , l_program_id
1464 , SYSDATE
1465 FROM cst_pac_item_costs CPIC,
1466 mtl_system_items MSI,
1467 (SELECT inventory_item_id, based_on_rollup_flag
1468 FROM cst_item_costs_interface
1469 WHERE group_id = l_grp_id) BORF, -- based_on_rollup_flag backed up from destination cost type
1470 (SELECT cic.inventory_item_id, cic.based_on_rollup_flag
1471 FROM cst_cost_types cct, cst_item_costs cic
1472 WHERE cic.organization_id = p_to_org_id
1473 AND cct.cost_type_id = p_to_cost_type_id
1474 AND cic.cost_type_id = cct.default_cost_type_id) dBORF -- based_on_rollup_flag from default cost type
1475 WHERE CPIC.pac_period_id = p_period_id
1476 AND CPIC.cost_group_id = p_from_cost_group_id
1477 AND CPIC.inventory_item_id = MSI.inventory_item_id
1478 AND MSI.organization_id = p_to_org_id
1479 AND BORF.inventory_item_id(+) = CPIC.inventory_item_id
1480 AND dBORF.inventory_item_id(+) = CPIC.inventory_item_id
1481 AND (p_range = 1
1482 OR
1483 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
1484 OR
1485 EXISTS
1486 (SELECT NULL
1487 FROM mtl_item_categories MIC
1488 WHERE MIC.organization_id = l_master_org_id
1489 AND MIC.category_id = p_category_id
1490 AND MIC.category_set_id = p_category_set_id
1491 AND MIC.inventory_item_id = CPIC.inventory_item_id
1492 AND p_range = 5)
1493 );
1494
1495 l_row_count := 0;
1496 l_row_count := SQL%ROWCOUNT;
1497
1498 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1499 ||' Rows Inserted into CIC');
1500
1501 l_stmt_num := 115;
1502
1503 DELETE cst_item_costs_interface
1504 WHERE group_id = l_grp_id;
1505
1506 l_row_count := 0;
1507 l_row_count := SQL%ROWCOUNT;
1508
1509 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1510 ||' Backup Rows deleted from CICI');
1511
1512 --------------------------------------------------------------------------
1513 -- step 4> Getting costs from CPICD having the cost_layer_id
1514 -- as used above in CPIC and inserting
1515 -- to the costs for p_to_org_id, p_to_cost_type_id, item in CICD
1516 --------------------------------------------------------------------------
1517
1518
1519 l_stmt_num := 120;
1520
1521
1522 INSERT INTO cst_item_cost_details
1523 ( inventory_item_id
1524 , organization_id
1525 , cost_type_id
1526 , last_update_date
1527 , last_updated_by
1528 , creation_date
1529 , created_by
1530 , last_update_login
1531 , level_type
1532 , resource_id
1533 , usage_rate_or_amount
1534 , basis_type
1535 , basis_resource_id
1536 , basis_factor
1537 , net_yield_or_shrinkage_factor
1538 , item_cost
1539 , cost_element_id
1540 , rollup_source_type
1541 , request_id
1542 , program_application_id
1543 , program_id
1544 , program_update_date
1545 )
1546 SELECT
1547 CPIC.inventory_item_id
1548 , p_to_org_id
1549 , p_to_cost_type_id
1550 , SYSDATE
1551 , l_user_id
1552 , SYSDATE
1553 , l_user_id
1554 , l_login_id
1555 , CPICD.level_type
1556 , decode(CPICD.cost_element_id, -- For resource id
1557 1, p_material,
1558 2, p_material_overhead,
1559 3, p_resource,
1560 4, p_outside_processing,
1561 5, p_overhead)
1562 , CPICD.item_cost *
1563 get_uom_conv_rate(CPIC.inventory_item_id,
1564 l_master_org_id,
1565 p_to_org_id) --usage_rate
1566 , 1 -- basis_type
1567 , NULL -- basis_resource_id
1568 , 1 -- basis_factor
1569 , 1 -- net_yield_or_shrinkage_factor
1570 , CPICD.item_cost *
1571 get_uom_conv_rate(CPIC.inventory_item_id,
1572 l_master_org_id,
1573 p_to_org_id)
1574 , CPICD.cost_element_id
1575 , 1 -- rollup_source_type
1576 , l_request_id
1577 , 702
1578 , l_program_id
1579 , SYSDATE
1580 FROM cst_pac_item_costs CPIC,
1581 cst_pac_item_cost_details CPICD,
1582 mtl_system_items MSI -- Bug 2570867 - joined with MSI to select only to_org items
1583 WHERE CPIC.pac_period_id = p_period_id
1584 AND CPIC.cost_group_id = p_from_cost_group_id
1585 AND CPIC.cost_layer_id = CPICD.cost_layer_id
1586 AND MSI.inventory_item_id = CPIC.inventory_item_id
1587 AND MSI.organization_id = p_to_org_id
1588 AND (p_range = 1
1589 OR
1590 (p_range = 2 AND CPIC.inventory_item_id = p_specific_item_id)
1591 OR
1592 EXISTS
1593 (SELECT NULL
1594 FROM mtl_item_categories MIC
1595 WHERE MIC.organization_id = l_master_org_id
1596 AND MIC.category_id = p_category_id
1597 AND MIC.category_set_id = p_category_set_id
1598 AND MIC.inventory_item_id = CPIC.inventory_item_id
1599 AND p_range = 5)
1600 );
1601
1602 l_row_count := 0;
1603 l_row_count := SQL%ROWCOUNT;
1604
1605 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(l_row_count)
1606 ||' Rows Inserted into CICD');
1607
1608
1609 END IF; -- If p_copy_option = 3
1610
1611 COMMIT;
1612
1613
1614 EXCEPTION
1615
1616 WHEN cst_fail_uomconvert THEN
1617
1618 l_err_num := 30001;
1619 l_err_code := SQLCODE;
1620 FND_MESSAGE.set_name('BOM', 'CST_FAIL_UOMCONVERT');
1621
1622 l_err_msg := FND_MESSAGE.Get;
1623 l_err_msg := substrb('CSTPPCIC.copy_item_period_cost('|| to_char(l_stmt_num)|| ')' || ' : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg,1,240);
1624
1625 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
1626 fnd_file.put_line(fnd_file.log,l_err_msg);
1627
1628 WHEN cst_fail_parameters THEN
1629
1630 l_err_num := 30001;
1631 l_err_code := SQLCODE;
1632 FND_MESSAGE.set_name('BOM', 'CST_FAIL_PARAMETERS');
1633
1634 l_err_msg := FND_MESSAGE.Get;
1635 l_err_msg := substrb('CSTPPCIC.copy_item_period_cost('|| to_char(l_stmt_num)|| ')' || ' : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg,1,240);
1636
1637 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
1638 fnd_file.put_line(fnd_file.log,l_err_msg);
1639
1640 WHEN OTHERS THEN
1641
1642 l_err_num := 30001;
1643 l_err_code := SQLCODE;
1644 l_err_msg := TO_CHAR(l_stmt_num)||SUBSTR(SQLERRM,1,220);
1645 FND_MESSAGE.set_name('BOM', 'CST_PAC_INVALID_LE');
1646 l_err_msg := FND_MESSAGE.Get;
1647 l_err_msg := substrb('CSTPPCIC.copy_item_period_cost('|| to_char(l_stmt_num)|| ')' || ' : (' || to_char(l_err_num) || '):'|| l_err_code ||' : '||l_err_msg,1,240);
1648
1649 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_err_msg);
1650 fnd_file.put_line(fnd_file.log,l_err_msg);
1651
1652 END copy_item_period_cost;
1653
1654 END CSTPPCIC;