[Home] [Help]
PACKAGE BODY: APPS.CSTPSCCM
Source
1 PACKAGE BODY CSTPSCCM AS
2 /* $Header: CSTSCCMB.pls 120.2 2007/12/19 08:07:23 smsasidh ship $ */
3
4 FUNCTION merge_costs (
5 p_rollup_id IN NUMBER,
6 p_dest_cost_type_id IN NUMBER,
7 p_buy_cost_type_id IN NUMBER,
8 p_inventory_item_id IN NUMBER,
9 p_dest_organization_id IN NUMBER,
10 p_assignment_set_id IN NUMBER,
11 x_err_buf OUT NOCOPY VARCHAR2,
12 p_buy_cost_detail IN NUMBER -- SCAPI: option to preserve buy cost details
13 )
14 RETURN INTEGER
15 IS
16 x_return_code NUMBER;
17 l_stmt_num NUMBER;
18 l_counter NUMBER;
19 curr_org_make_weight NUMBER;
20 curr_vendor_buy_weight NUMBER;
21 l_count_src_rows NUMBER;
22 l_curr_rowid NUMBER;
23 t_inventory_item_id NUMBER;
24 default_cost_type_id NUMBER;
25 default_buy_cost_type_id NUMBER;
26 wsm_flag VARCHAR2(1);
27
28
29 l_user_id number := -1;
30 l_login_id number := -1;
31 l_request_id number := -1;
32 l_prog_appl_id number := -1;
33 l_prog_id number := -1;
34
35
36 CURSOR based_on_rollup_costs_cur (
37 l_dest_cost_type_id NUMBER,
38 l_inventory_item_id NUMBER,
39 l_dest_organization_id NUMBER) IS
40 SELECT cic.inventory_item_id
41 FROM cst_item_costs cic
42 WHERE cic.inventory_item_id = l_inventory_item_id
43 AND cic.organization_id = l_dest_organization_id
44 AND cic.cost_type_id = l_dest_cost_type_id
45 /* Bug 2077929 - When no costs are defined for l_dest_cost_type_id,
46 the present logic treats this as based_on_rollup_flag=1 and merges costs*/
47 /* AND cic.based_on_rollup_flag = 2; */
48 AND cic.based_on_rollup_flag = 1;
49
50 /* Bug 4547027 -Changed the cursor to ignore the Inactive items in the source organization */
51 CURSOR src_orgs_cur(l_rollup_id IN NUMBER,
52 l_inventory_item_id IN NUMBER,
53 l_dest_organization_id IN NUMBER,
54 l_assignment_set_id IN NUMBER) IS
55 SELECT
56 cssr.source_organization_id,
57 (cssr.allocation_percent/100.00) allocation_factor,
58 cssr.markup_code,
59 cssr.markup,
60 cssr.ship_charge_code,
61 cssr.ship_charge,
62 cssr.conversion_rate,
63 cssr.ship_method
64 FROM
65 CST_SC_SOURCING_RULES CSSR,
66 MTL_SYSTEM_ITEMS MSI,
67 BOM_PARAMETERS BP
68 WHERE
69 CSSR.ROLLUP_ID = l_rollup_id
70 AND CSSR.inventory_item_id = l_inventory_item_id
71 AND CSSR.organization_id = l_dest_organization_id
72 AND CSSR.source_organization_id <> CSSR.organization_id
73 AND CSSR.assignment_set_id = l_assignment_set_id
74 AND CSSR.source_type = 1
75 AND MSI.inventory_item_id = l_inventory_item_id
76 AND MSI.organization_id = CSSR.source_organization_id
77 AND BP.organization_id (+) = CSSR.source_organization_id
78 AND nvl(MSI.inventory_item_status_code,'NOT'||BP.bom_delete_status_code) <> nvl(BP.bom_delete_status_code,' ');
79
80 CURSOR vendors( l_rollup_id IN NUMBER,
81 l_inventory_item_id IN NUMBER,
82 l_dest_organization_id IN NUMBER,
83 l_assignment_set_id IN NUMBER) IS
84 SELECT
85 ROWID,
86 vendor_id,
87 vendor_site_id,
88 item_cost,
89 buy_cost_flag
90 FROM
91 CST_SC_SOURCING_RULES CSSR
92 WHERE
93 CSSR.ROLLUP_ID = l_rollup_id
94 AND CSSR.inventory_item_id = l_inventory_item_id
95 AND CSSR.organization_id = l_dest_organization_id
96 AND CSSR.assignment_set_id = l_assignment_set_id
97 AND CSSR.source_type = 3;
98
99
100
101 BEGIN
102
103
104 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Merge Routine');
105 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item = '||p_inventory_item_id);
106 FND_FILE.PUT_LINE(FND_FILE.LOG,'Org = '||p_dest_organization_id);
107 FND_FILE.PUT_LINE(FND_FILE.LOG,' ');
108
109 x_return_code := 0;
110
111 l_stmt_num := 1;
112 /* Supply chain enhancement: support default cost type */
113 SELECT DEFAULT_COST_TYPE_ID
114 INTO default_cost_type_id
115 FROM CST_COST_TYPES
116 WHERE COST_TYPE_ID = p_dest_cost_type_id;
117
118 /* SCAPI: to preserve buy cost details */
119 SELECT DEFAULT_COST_TYPE_ID
120 INTO default_buy_cost_type_id
121 FROM CST_COST_TYPES
122 WHERE COST_TYPE_ID = p_buy_cost_type_id;
123
124 /* The Who columns are not being correctly populated using FND_GLOBAL
125 structure.
126 CST_SC_ROLLUP_HISTORY has the correct information for the rollup_id
127 */
128 l_stmt_num := 2;
129
130 select
131 nvl( min( LAST_UPDATED_BY ), -1 ),
132 nvl( min( LAST_UPDATE_LOGIN ), -1 ),
133 nvl( min( request_id ), -1 ),
134 nvl( min( program_application_id ), -1 ),
135 nvl( min( program_id ), -1 )
136 into
137 l_user_id,
138 l_login_id,
139 l_request_id,
140 l_prog_appl_id,
141 l_prog_id
142 from
143 cst_sc_rollup_history
144 where
145 rollup_id = p_rollup_id;
146
147
148
149
150
151 l_stmt_num := 5;
152 /* *********************************************************************
153 | Not to merge costs for items that have BASED_ON_ROLLUP_FLAG not set
154 ********************************************************************* */
155
156 OPEN based_on_rollup_costs_cur(
157 p_dest_cost_type_id,
158 p_inventory_item_id,
159 p_dest_organization_id);
160
161 FETCH based_on_rollup_costs_cur INTO t_inventory_item_id;
162 /* Bug 2077929 - Need to check for NOTFOUND as per modification
163 done in cursor defintion */
164 /* IF (based_on_rollup_costs_cur%FOUND) THEN */
165 IF (based_on_rollup_costs_cur%NOTFOUND) THEN
166 CLOSE based_on_rollup_costs_cur;
167 x_err_buf := 'CSTPSCCM.remove_rollup_history' ||': Not Merged asbased_on_rollup_flag set to No ';
168
169 RETURN x_return_code;
170 END IF;
171 CLOSE based_on_rollup_costs_cur;
172
173 l_stmt_num := 10;
174
175 /* **********************************************
176 | Obtain weightage for the current org |
177 | for the MAKE rules |
178 ********************************************** */
179
180 SELECT
181 SUM(allocation_percent)/100
182 INTO
183 curr_org_make_weight
184 FROM
185 CST_SC_SOURCING_RULES CSSC
186 WHERE
187 CSSC.rollup_id = p_rollup_id
188 AND CSSC.assignment_set_id = p_assignment_set_id
189 AND CSSC.inventory_item_id = p_inventory_item_id
190 AND CSSC.organization_id = p_dest_organization_id
191 AND CSSC.source_type = 2;
192
193
194 /* *******************************************
195 FOR SRC:
196 1-txf
197 2-make
198 3-buy
199 ****************************************** */
200
201 l_stmt_num := 20;
202
203 IF(curr_org_make_weight IS NULL OR curr_org_make_weight < 0 ) THEN
204 curr_org_make_weight := 0;
205 END IF;
206
207
208 SELECT count(1)
209 INTO l_count_src_rows
210 FROM CST_SC_SOURCING_RULES CSSR
211 WHERE CSSR.rollup_id = p_rollup_id
212 AND CSSR.assignment_set_id = p_assignment_set_id
213 AND CSSR.inventory_item_id = p_inventory_item_id
214 AND CSSR.organization_id = p_dest_organization_id
215 and ROWNUM < 2; /* Added for Bug 5678464 */
216
217 IF (l_count_src_rows = 0 ) THEN
218 curr_org_make_weight := 1;
219 END IF;
220
221
222
223
224 /* *****************************************************
225 | Reduce CICD rows for the current org by weightage |
226 ***************************************************** */
227 l_stmt_num := 30;
228
229 UPDATE CST_ITEM_COST_DETAILS CICD
230 SET ITEM_COST = (ITEM_COST * curr_org_make_weight),
231 /* Propagate changes for Bug 2347889
232 Scale the yielded cost also */
233 YIELDED_COST = DECODE(YIELDED_COST,NULL,NULL,(YIELDED_COST * curr_org_make_weight)),
234 ALLOCATION_PERCENT = curr_org_make_weight*100,
235 USAGE_RATE_OR_AMOUNT = (USAGE_RATE_OR_AMOUNT
236 * curr_org_make_weight)
237 WHERE CICD.inventory_item_id = p_inventory_item_id
238 AND CICD.organization_id = p_dest_organization_id
239 AND CICD.cost_type_id = p_dest_cost_type_id
240 AND CICD.rollup_source_type NOT IN (1,2)
241 -- Bug 2302328: Do not reaverage for user-defined and default rows
242 AND curr_org_make_weight <> 1;
243
244 FND_FILE.PUT_LINE(FND_FILE.LOG,'Make Updated = '||SQL%ROWCOUNT);
245 FND_FILE.PUT_LINE(FND_FILE.LOG,'Alloc = '||curr_org_make_weight);
246
247
248
249
250
251 FOR vendor_cur IN vendors( p_rollup_id,
252 p_inventory_item_id,
253 p_dest_organization_id,
254 p_assignment_set_id)
255 LOOP
256
257 l_stmt_num := 40;
258
259 /* **********************************************
260 | Obtain weightage for the current org |
261 | ONLY for Buy items |
262 ********************************************** */
263
264 SELECT
265 NVL(allocation_percent,0)/100
266 INTO
267 curr_vendor_buy_weight
268 FROM
269 CST_SC_SOURCING_RULES CSSC
270 WHERE
271 CSSC.ROWID = vendor_cur.ROWID;
272
273
274 /* *******************************************
275 FOR SRC:
276 1-txf
277 2-make
278 3-buy
279 ****************************************** */
280
281
282 l_stmt_num := 50;
283
284 IF(curr_vendor_buy_weight IS NULL OR curr_vendor_buy_weight <0 ) THEN
285 curr_vendor_buy_weight := 0;
286 END IF;
287
288 IF (curr_vendor_buy_weight <> 0) THEN
289
290 /* *****************************************************
291 | Create CICD rows with Buy Cost |
292 ***************************************************** */
293 l_stmt_num := 60;
294
295 -- SCAPI: option to preserve the buy cost details
296 IF (p_buy_cost_detail <> 1 or p_buy_cost_detail is null) THEN
297 INSERT INTO CST_ITEM_COST_DETAILS
298 (
299 inventory_item_id,
300 organization_id,
301 cost_type_id,
302 last_update_date,
303 last_updated_by,
304 creation_date,
305 created_by,
306 last_update_login,
307 operation_sequence_id,
308 operation_seq_num,
309 department_id,
310 level_type,
311 activity_id,
312 resource_seq_num,
313 resource_id,
314 resource_rate,
315 item_units,
316 activity_units,
317 usage_rate_or_amount,
318 basis_type,
319 basis_resource_id,
320 basis_factor,
321 net_yield_or_shrinkage_factor,
322 item_cost,
323 cost_element_id,
324 rollup_source_type,
325 activity_context,
326 request_id,
327 program_application_id,
328 program_id,
329 program_update_date,
330 yielded_cost,
331 source_organization_id,
332 vendor_id,
333 vendor_site_id,
334 allocation_percent
335 )
336 SELECT
337 p_inventory_item_id,
338 p_dest_organization_id,
339 p_dest_cost_type_id,
340 SYSDATE,
341 l_user_id,
342 SYSDATE, --creation_date,
343 l_user_id,
344 l_login_id,
345 NULL, --operation_sequence_id,
346 NULL, --operation_seq_num,
347 NULL, --department_id,
348 1, -- level_type = This Level
349 NULL, --activity_id,
350 NULL, --resource_seq_num,
351 NULL, --resource_id,
352 NULL, --resource_rate,
353 NULL, --item_units,
354 NULL, --activity_units,
355 (vendor_cur.item_cost * curr_vendor_buy_weight), --usage_rate_or_amount,
356 1, -- ALWAYS basis_type= item,
357 NULL, --basis_resource_id,
358 1, -- Always basis_factor=1,
359 1, --net_yield_or_shrinkage_factor,
360 (vendor_cur.item_cost * curr_vendor_buy_weight), --item_cost, Item Buy cost,
361 1, -- ALways MAT cost_element_id,
362 3, -- rollup_source_type = Always rolled up
363 NULL, --activity_context,
364 l_request_id,
365 l_prog_appl_id,
366 l_prog_id,
367 SYSDATE, --program_update_date, Need to put correct one
368 NULL, --yielded_cost,
369 NULL, --source_organization_id
370 NVL(vendor_cur.vendor_id, -1), -- SCAPI: use -1 if no vendor name
371 NVL(vendor_cur.vendor_site_id, -1),
372 curr_vendor_buy_weight*100
373 FROM
374 CST_SC_SOURCING_RULES CSSR
375 WHERE
376 CSSR.organization_id = p_dest_organization_id AND
377 CSSR.inventory_item_id = p_inventory_item_id AND
378 UPPER(vendor_cur.buy_cost_flag) = 'Y' AND
379 CSSR.rollup_id = p_rollup_id AND
380 CSSR.ROWID = vendor_cur.ROWID;
381
382 ELSE
383 INSERT INTO CST_ITEM_COST_DETAILS
384 (
385 inventory_item_id,
386 organization_id,
387 cost_type_id,
388 last_update_date,
389 last_updated_by,
390 creation_date,
391 created_by,
392 last_update_login,
393 operation_sequence_id,
394 operation_seq_num,
395 department_id,
396 level_type,
397 activity_id,
398 resource_seq_num,
399 resource_id,
400 resource_rate,
401 item_units,
402 activity_units,
403 usage_rate_or_amount,
404 basis_type,
405 basis_resource_id,
406 basis_factor,
407 net_yield_or_shrinkage_factor,
408 item_cost,
409 cost_element_id,
410 rollup_source_type,
411 activity_context,
412 request_id,
413 program_application_id,
414 program_id,
415 program_update_date,
416 yielded_cost,
417 source_organization_id,
418 vendor_id,
419 vendor_site_id,
420 allocation_percent
421 )
422 SELECT
426 SYSDATE,
423 p_inventory_item_id,
424 p_dest_organization_id,
425 p_dest_cost_type_id,
427 l_user_id,
428 SYSDATE, -- creation_date
429 l_user_id, -- create_by
430 l_login_id,
431 CICD2.operation_sequence_id, --operation_sequence_id,
432 CICD2.operation_seq_num, --operation_seq_num,
433 CICD2.department_id, --department_id,
434 CICD2.level_type, -- level_type = Always Prev Level
435 CICD2.activity_id, --activity_id,
436 CICD2.resource_seq_num, --resource_seq_num,
437 CICD2.resource_id, --resource_id,
438 CICD2.resource_rate, -- resource_rate
439 CICD2.item_units,
440 CICD2.activity_units,
441 (CICD2.item_cost * curr_vendor_buy_weight) / decode(NVL(CICD2.resource_rate,0),0,1,nvl(CICD2.resource_rate, 1)),
442 -- usage_rate_or_amount
443 1, -- basis_type, -- Always Item Based
444 CICD2.basis_resource_id, -- basis_resource_id,
445 1, -- basis_factor, -- Always Item Based
446 1, -- net_yield_or_shrinkage_factor,
447 CICD2.item_cost * curr_vendor_buy_weight, -- item cost
448 CICD2.cost_element_id,
449 3, -- rollup_source_type = Always rolled up
450 CICD2.activity_context, --CICD2.activity_context,
451 l_request_id,
452 l_prog_appl_id,
453 l_prog_id,
454 SYSDATE, --program_update_date,
455 CICD2.yielded_cost * curr_vendor_buy_weight,
456 NULL, -- source_organization_id
457 NVL(vendor_cur.vendor_id, -1), -- SCAPI: use -1 if no vendor name
458 NVL(vendor_cur.vendor_site_id, -1),
459 curr_vendor_buy_weight*100
460 FROM
461 CST_ITEM_COST_DETAILS CICD2,
462 MTL_PARAMETERS MP
463 WHERE
464 -- If buy cost type equals destination cost type, do not include rolled-up costs.
465 -- This is to get consistent results with the no-buy-cost-detail option.
466 CICD2.rollup_source_type <> decode(p_buy_cost_type_id, p_dest_cost_type_id, 3, -1) AND
467 CICD2.inventory_item_id = p_inventory_item_id AND
468 CICD2.organization_id = p_dest_organization_id AND
469 MP.organization_id = p_dest_organization_id AND
470 (
471 CICD2.cost_type_id = p_buy_cost_type_id
472 OR
473 (
474 CICD2.cost_type_id = default_buy_cost_type_id
475 AND NOT EXISTS (
476 SELECT 'X'
477 FROM CST_ITEM_COSTS CIA3
478 WHERE CIA3.inventory_item_id = p_inventory_item_id
479 AND CIA3.organization_id = p_dest_organization_id
480 AND CIA3.cost_type_id = p_buy_cost_type_id)
481 )
482 OR
483 (
484 CICD2.cost_type_id = MP.primary_cost_method
485 AND NOT EXISTS (
486 SELECT 'X'
487 FROM CST_ITEM_COSTS CIA4
488 WHERE CIA4.inventory_item_id = p_inventory_item_id
489 AND CIA4.organization_id = p_dest_organization_id
490 AND CIA4.cost_type_id in (p_buy_cost_type_id,default_buy_cost_type_id))
491 )
492 );
493
494 END IF;
495
496 FND_FILE.PUT_LINE(FND_FILE.LOG,'Buy Inserted = '||SQL%ROWCOUNT);
497 FND_FILE.PUT_LINE(FND_FILE.LOG,'Vendor = '||vendor_cur.vendor_id);
498 FND_FILE.PUT_LINE(FND_FILE.LOG,'Alloc = '||curr_vendor_buy_weight);
499
500 END IF;
501
502 END LOOP;
503 /* *****************************************************
504 | Create CICD rows with Weighted Other Org CICD rows |
505 ***************************************************** */
506 l_stmt_num := 65;
507 SELECT wsm_enabled_flag
508 INTO wsm_flag
509 FROM MTL_PARAMETERS
510 WHERE organization_id = p_dest_organization_id;
511
512
513 l_stmt_num := 70;
514 l_counter := 0;
515
516 FOR src_org IN src_orgs_cur(p_rollup_id,
517 p_inventory_item_id,
518 p_dest_organization_id,
519 p_assignment_set_id)
520 LOOP
521
522 l_stmt_num := 80;
523 l_counter := l_counter + 1;
524
525
526 INSERT INTO CST_ITEM_COST_DETAILS
527 (
528 inventory_item_id,
529 organization_id,
530 cost_type_id,
531 last_update_date,
532 last_updated_by,
533 creation_date,
534 created_by,
535 last_update_login,
536 operation_sequence_id,
537 operation_seq_num,
538 department_id,
539 level_type,
540 activity_id,
541 resource_seq_num,
542 resource_id,
543 resource_rate,
547 basis_type,
544 item_units,
545 activity_units,
546 usage_rate_or_amount,
548 basis_resource_id,
549 basis_factor,
550 net_yield_or_shrinkage_factor,
551 item_cost,
552 cost_element_id,
553 rollup_source_type,
554 activity_context,
555 request_id,
556 program_application_id,
557 program_id,
558 program_update_date,
559 yielded_cost,
560 source_organization_id,
561 vendor_id,
562 allocation_percent,
563 ship_method
564 )
565 SELECT
566 CICD2.inventory_item_id,
567 p_dest_organization_id,
568 p_dest_cost_type_id,
569 SYSDATE,
570 l_user_id,
571 SYSDATE, -- creation_date
572 l_user_id, -- created_by
573 l_login_id,
574 NULL, --operation_sequence_id,
575 NULL, --operation_seq_num,
576 NULL, --department_id,
577 2, -- level_type = Always Prev Level
578 NULL, --activity_id,
579 NULL, --resource_seq_num,
580 NULL, --resource_id,
581 CICD2.resource_rate, -- resource_rate
582 CICD2.item_units,
583 CICD2.activity_units,
584 (CICD2.item_cost * NVL(src_org.conversion_rate,1))
585 * src_org.allocation_factor / decode(NVL(CICD2.resource_rate,0),0,1,nvl(CICD2.resource_rate, 1)),
586 -- usage_rate_or_amount
587 1, -- basis_type, -- Always Item Based
588 NULL, -- basis_resource_id,
589 1, -- basis_factor, -- Always Item Based
590 1, -- net_yield_or_shrinkage_factor,
591 (CICD2.item_cost * NVL(src_org.conversion_rate,1))
592 * src_org.allocation_factor, -- item cost
593 CICD2.cost_element_id,
594 3, -- rollup_source_type = Always rolled up
595 NULL, --CICD2.activity_context,
596 l_request_id,
597 l_prog_appl_id,
598 l_prog_id,
599 SYSDATE, --program_update_date,
600 /* Propagate Changes for Bug 2347889 - Scale yielded costs
601 Also, propagate yielded costs only if organization is
602 WSM_ENABLED */
603 decode(wsm_flag, 'Y', (CICD2.yielded_cost * NVL(src_org.conversion_rate,1))* src_org.allocation_factor, NULL),
604 src_org.source_organization_id,
605 NULL,
606 src_org.allocation_factor*100,
607 src_org.ship_method
608 FROM
609 CST_ITEM_COST_DETAILS CICD2,
610 MTL_PARAMETERS MP
611 WHERE
612 CICD2.inventory_item_id = p_inventory_item_id
613 AND CICD2.organization_id = src_org.source_organization_id
614 AND MP.organization_id = src_org.source_organization_id
615 AND (
616 CICD2.cost_type_id = p_dest_cost_type_id
617 OR
618 (
619 CICD2.cost_type_id = default_cost_type_id
620 AND NOT EXISTS (
621 SELECT 'X'
622 FROM CST_ITEM_COSTS CIA3
623 WHERE CIA3.inventory_item_id = p_inventory_item_id
624 AND CIA3.organization_id = src_org.source_organization_id
625 AND CIA3.cost_type_id = p_dest_cost_type_id)
626 )
627 OR
628 (
629 CICD2.cost_type_id = MP.primary_cost_method
630 AND NOT EXISTS (
631 SELECT 'X'
632 FROM CST_ITEM_COSTS CIA4
633 WHERE CIA4.inventory_item_id = p_inventory_item_id
634 AND CIA4.organization_id = src_org.source_organization_id
635 AND CIA4.cost_type_id in (p_dest_cost_type_id,default_cost_type_id))
636 )
637 ); /* Supply chain enhancement: support default valuation cost type */
638
639 FND_FILE.PUT_LINE(FND_FILE.LOG,'Txf Inserted = '||SQL%ROWCOUNT);
640 FND_FILE.PUT_LINE(FND_FILE.LOG,'Org = '||src_org.source_organization_id);
641 FND_FILE.PUT_LINE(FND_FILE.LOG,'Alloc = '||src_org.allocation_factor);
642
643
644
645 l_stmt_num := 90;
646 /***************************************************************************
647 | Insert rows into CICD for Markup and Shipping costs as MOH |
648 ***************************************************************************/
649
650 INSERT INTO CST_ITEM_COST_DETAILS
651 (
652 inventory_item_id,
653 organization_id,
654 cost_type_id,
655 last_update_date,
656 last_updated_by,
657 creation_date,
658 created_by,
659 last_update_login,
660 operation_sequence_id,
661 operation_seq_num,
662 department_id,
663 level_type,
664 activity_id,
668 item_units,
665 resource_seq_num,
666 resource_id,
667 resource_rate,
669 activity_units,
670 usage_rate_or_amount,
671 basis_type,
672 basis_resource_id,
673 basis_factor,
674 net_yield_or_shrinkage_factor,
675 item_cost,
676 cost_element_id,
677 rollup_source_type,
678 activity_context,
679 request_id,
680 program_application_id,
681 program_id,
682 program_update_date,
683 yielded_cost,
684 source_organization_id,
685 vendor_id,
686 allocation_percent,
687 ship_method
688 )
689 SELECT
690 p_inventory_item_id,
691 p_dest_organization_id,
692 p_dest_cost_type_id,
693 SYSDATE,
694 l_user_id,
695 SYSDATE, --creation_date,
696 l_user_id,
697 l_login_id,
698 NULL, --operation_sequence_id,
699 NULL, --operation_seq_num,
700 NULL, --department_id,
701 1, --level_type = THIS Level
702 NULL, --activity_id,
703 NULL, --resource_seq_num,
704 min(MP.default_matl_ovhd_cost_id), --resource_id, /* Supply chain enhancement */
705 NULL, --resource_rate,
706 NULL, --item_units,
707 NULL, --activity_units,
708 DECODE(src_org.MARKUP_CODE,
709 2,
710 src_org.MARKUP ,
711 3,
712 SUM(CICD2.ITEM_COST)*(src_org.MARKUP/100),
713 4,
714 SUM(CICD2.ITEM_COST)*(src_org.MARKUP/100)), --usage_rate_or_amount,
715 1, -- ALWAYS basis_type= item,
716 NULL, --basis_resource_id,
717 1, -- Always basis_factor=1,
718 1, --net_yield_or_shrinkage_factor,
719 DECODE(src_org.MARKUP_CODE,
720 2,
721 src_org.MARKUP ,
722 3,
723 SUM(CICD2.ITEM_COST)*(src_org.MARKUP/100) ,
724 4,
725 SUM(CICD2.ITEM_COST)*(src_org.MARKUP/100)), --item_cost,
726 2, -- ALways MOH cost_element_id,
727 3, -- rollup_source_type = Always rolled up
728 NULL, --activity_context,
729 l_request_id,
730 l_prog_appl_id,
731 l_prog_id,
732 SYSDATE, --program_update_date, /* Need to put correct one */
733 NULL, --yielded_cost,
734 src_org.source_organization_id, -- source_organization_id
735 NULL,
736 src_org.allocation_factor * 100,
737 src_org.ship_method
738 FROM
739 CST_ITEM_COST_DETAILS CICD2,
740 MTL_PARAMETERS MP
741 WHERE
742 CICD2.inventory_item_id = p_inventory_item_id
743 AND CICD2.organization_id = p_dest_organization_id
744 AND CICD2.cost_type_id = p_dest_cost_type_id
745 AND CICD2.source_organization_id = src_org.source_organization_id
746 AND CICD2.rollup_source_type = 3
747 AND MP.organization_id = p_dest_organization_id
748 AND src_org.MARKUP IS NOT NULL
749 AND src_org.MARKUP_CODE IN (2, 3, 4)
750 AND src_org.MARKUP <> 0
751 GROUP BY CICD2.inventory_item_id, CICD2.organization_id, MP.organization_id
752 HAVING SUM(CICD2.ITEM_COST) > 0
753
754 UNION ALL
755
756 SELECT
757 p_inventory_item_id,
758 p_dest_organization_id,
759 p_dest_cost_type_id,
760 SYSDATE,
761 l_user_id,
762 SYSDATE, --creation_date,
763 l_user_id,
764 l_login_id,
765 NULL, --operation_sequence_id,
766 NULL, --operation_seq_num,
767 NULL, --department_id,
768 1, -- level_type = THIS Level
769 NULL, --activity_id,
770 NULL, --resource_seq_num,
771 min(MP.default_matl_ovhd_cost_id), --resource_id, /* Supply chain enhancement */
772 NULL, --resource_rate,
773 NULL, --item_units,
774 NULL, --activity_units,
775 DECODE(src_org.SHIP_CHARGE_CODE,
776 2,
777 src_org.SHIP_CHARGE ,
778 3,
779 SUM(CICD2.ITEM_COST)*(src_org.SHIP_CHARGE/100),0), --usage_rate_or_amount,
780 1, -- ALWAYS basis_type= item,
781 NULL, --basis_resource_id,
782 1, -- Always basis_factor=1,
783 1, --net_yield_or_shrinkage_factor,
784 DECODE(src_org.SHIP_CHARGE_CODE,
785 2,
786 src_org.SHIP_CHARGE ,
787 3,
788 SUM(CICD2.ITEM_COST)*(src_org.SHIP_CHARGE/100),0), --item_cost,
789 2, -- ALways MOH cost_element_id,
793 l_prog_appl_id,
790 3, -- rollup_source_type = Always rolled up
791 NULL, --activity_context,
792 l_request_id,
794 l_prog_id,
795 SYSDATE, --program_update_date, /* Need to put correct one */
796 NULL, --yielded_cost,
797 src_org.source_organization_id, -- source_organization_id
798 NULL,
799 src_org.allocation_factor * 100,
800 src_org.ship_method
801 FROM
802 CST_ITEM_COST_DETAILS CICD2,
803 MTL_PARAMETERS MP
804 WHERE
805 CICD2.inventory_item_id = p_inventory_item_id
806 AND CICD2.organization_id = p_dest_organization_id
807 AND CICD2.cost_type_id = p_dest_cost_type_id
808 AND CICD2.source_organization_id = src_org.source_organization_id
809 AND CICD2.rollup_source_type = 3
810 AND MP.organization_id = p_dest_organization_id
811 AND src_org.SHIP_CHARGE IS NOT NULL
812 AND src_org.SHIP_CHARGE_CODE in (2, 3)
813 AND src_org.SHIP_CHARGE <> 0
814 GROUP BY CICD2.inventory_item_id, CICD2.organization_id, MP.organization_id
815 HAVING SUM(CICD2.ITEM_COST) > 0;
816
817 FND_FILE.PUT_LINE(FND_FILE.LOG,'Markup and Ship Inserted = '||SQL%ROWCOUNT);
818 FND_FILE.PUT_LINE(FND_FILE.LOG,'Org = '||src_org.source_organization_id);
819
820
821 END LOOP;
822
823 x_return_code := 0;
824 x_err_buf := 'CSTPSCCM.merge_costs' ||': Returned Success';
825 return x_return_code;
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 x_return_code := SQLCODE;
830 x_err_buf := 'CSTPSCCM.merge_costs' ||'stmt_num='||l_stmt_num||' : '||substrb(sqlerrm,1,1000);
831 --dbms_output.put_line(x_err_buf);
832 --dbms_output.put_line(to_char(x_return_code));
833
834 return x_return_code;
835
836
837 END merge_costs;
838
839
840 FUNCTION remove_rollup_history (
841 p_rollup_id IN NUMBER,
842 p_sc_cost_type_id IN NUMBER,
843 p_rollup_option IN NUMBER,
844 x_err_buf OUT NOCOPY VARCHAR2
845 )
846 RETURN INTEGER
847 IS
848 l_stmt_num NUMBER;
849 x_return_code NUMBER;
850
851 BEGIN
852
853 x_return_code := 0;
854
855 l_stmt_num := 10;
856
857 -- SCAPI: delete data of previous reports including the consolidated report
858 DELETE CST_SC_BOM_STRUCTURES CSBS
859 WHERE CSBS.ROLLUP_ID IN (
860 SELECT CSRH.ROLLUP_ID
861 FROM CST_SC_ROLLUP_HISTORY CSRH
862 WHERE CSRH.ROLLUP_ID <> p_rollup_id
863 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id
864 UNION
865 SELECT -1*CSRH.ROLLUP_ID
866 FROM CST_SC_ROLLUP_HISTORY CSRH
867 WHERE CSRH.ROLLUP_ID <> p_rollup_id
868 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id
869 )
870 AND (CSBS.COMPONENT_ITEM_ID,
871 CSBS.COMPONENT_ORGANIZATION_ID)
872 IN
873 (SELECT CSBS1.COMPONENT_ITEM_ID,
874 CSBS1.COMPONENT_ORGANIZATION_ID
875 FROM CST_SC_BOM_STRUCTURES CSBS1,
876 CST_SC_LOW_LEVEL_CODES CSLLC
877 WHERE CSBS1.ROLLUP_ID = p_rollup_id
878 AND CSBS1.ROLLUP_ID = CSLLC.ROLLUP_ID
879 AND CSBS1.COMPONENT_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
880 AND CSBS1.COMPONENT_ORGANIZATION_ID = CSLLC.ORGANIZATION_ID);
881
882 l_stmt_num := 20;
883
884 DELETE CST_SC_BOM_EXPLOSION CSBE
885 WHERE CSBE.ROLLUP_ID IN (
886 SELECT CSRH.ROLLUP_ID
887 FROM CST_SC_ROLLUP_HISTORY CSRH
888 WHERE CSRH.ROLLUP_ID <> p_rollup_id
889 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id)
890 AND (CSBE.COMPONENT_ITEM_ID,
891 CSBE.COMPONENT_ORGANIZATION_ID)
892 IN
893 (SELECT CSBE1.COMPONENT_ITEM_ID,
894 CSBE1.COMPONENT_ORGANIZATION_ID
895 FROM CST_SC_BOM_EXPLOSION CSBE1,
896 CST_SC_LOW_LEVEL_CODES CSLLC
897 WHERE CSBE1.ROLLUP_ID = p_rollup_id
898 AND CSBE1.ROLLUP_ID = CSLLC.ROLLUP_ID
899 AND CSBE1.COMPONENT_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
900 AND CSBE1.COMPONENT_ORGANIZATION_ID = CSLLC.ORGANIZATION_ID);
901
902 l_stmt_num := 30;
903
904 DELETE CST_SC_SOURCING_RULES CSSR
905 WHERE CSSR.ROLLUP_ID IN (
906 SELECT CSRH.ROLLUP_ID
907 FROM CST_SC_ROLLUP_HISTORY CSRH
908 WHERE CSRH.ROLLUP_ID <> p_rollup_id
909 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id)
910 AND (CSSR.INVENTORY_ITEM_ID,
911 CSSR.ORGANIZATION_ID)
912 IN
913 (SELECT CSSR1.INVENTORY_ITEM_ID,
914 CSSR1.ORGANIZATION_ID
915 FROM CST_SC_SOURCING_RULES CSSR1,
916 CST_SC_LOW_LEVEL_CODES CSLLC
917 WHERE CSSR1.ROLLUP_ID = p_rollup_id
918 AND CSSR1.ROLLUP_ID = CSLLC.ROLLUP_ID
919 AND CSSR1.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
923
920 AND CSSR1.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID);
921
922 l_stmt_num := 40;
924 DELETE CST_SC_LOW_LEVEL_CODES CSLLC
925 WHERE CSLLC.ROLLUP_ID IN (
926 SELECT CSRH.ROLLUP_ID
927 FROM CST_SC_ROLLUP_HISTORY CSRH
928 WHERE CSRH.ROLLUP_ID <> p_rollup_id
929 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id)
930 AND (CSLLC.INVENTORY_ITEM_ID,
931 CSLLC.ORGANIZATION_ID)
932 IN
933 (SELECT CSLLC1.INVENTORY_ITEM_ID,
934 CSLLC1.ORGANIZATION_ID
935 FROM CST_SC_LOW_LEVEL_CODES CSLLC1
936 WHERE CSLLC1.ROLLUP_ID = p_rollup_id);
937
938 -- SCAPI: need to delete CST_SC_LISTS also
939 l_stmt_num := 50;
940
941 DELETE CST_SC_LISTS CSL
942 WHERE CSL.ROLLUP_ID IN (
943 SELECT CSRH.ROLLUP_ID
944 FROM CST_SC_ROLLUP_HISTORY CSRH
945 WHERE CSRH.ROLLUP_ID <> p_rollup_id
946 AND CSRH.COST_TYPE_ID = p_sc_cost_type_id)
947 AND (CSL.INVENTORY_ITEM_ID,
948 CSL.ORGANIZATION_ID)
949 IN
950 (SELECT CSL1.INVENTORY_ITEM_ID,
951 CSL1.ORGANIZATION_ID
952 FROM CST_SC_LISTS CSL1,
953 CST_SC_LOW_LEVEL_CODES CSLLC
954 WHERE CSL1.ROLLUP_ID = p_rollup_id
955 AND CSL1.ROLLUP_ID = CSLLC.ROLLUP_ID
956 AND CSL1.INVENTORY_ITEM_ID = CSLLC.INVENTORY_ITEM_ID
957 AND CSL1.ORGANIZATION_ID = CSLLC.ORGANIZATION_ID);
958
959 x_return_code := 0;
960 x_err_buf := 'CSTPSCCM.remove_rollup_history' ||': Returned Success';
961 return x_return_code;
962
963 EXCEPTION
964 WHEN OTHERS THEN
965 x_return_code := SQLCODE;
966 x_err_buf := 'CSTPSCCM.remove_rollup_history' ||'stmt_num='||l_stmt_num||' : '||substrb(sqlerrm,1,240);
967
968 return x_return_code;
969
970
971 END remove_rollup_history;
972
973 /* Added for Bug 5678464 */
974 PROCEDURE proc_remove_rollup_history(
975 x_err_buf OUT NOCOPY VARCHAR2,
976 retcode OUT NOCOPY NUMBER,
977 p_rollup_id IN VARCHAR2,
978 p_sc_cost_type_id IN VARCHAR2,
979 p_rollup_option IN VARCHAR2
980
981 ) IS
982
983
984 BEGIN
985 retcode := CSTPSCCM.remove_rollup_history
986 (
987 p_rollup_id => to_number(p_rollup_id),
988 p_sc_cost_type_id => to_number(p_sc_cost_type_id),
989 p_rollup_option => to_number(p_rollup_option),
990 x_err_buf => x_err_buf
991 );
992 END proc_remove_rollup_history;
993
994 END CSTPSCCM;