[Home] [Help]
PACKAGE BODY: APPS.CSTPCOIC
Source
1 PACKAGE BODY CSTPCOIC AS
2 /* $Header: CSTPCOIB.pls 120.3.12000000.2 2007/05/11 22:49:29 hyu ship $ */
3
4 FUNCTION copy_to_interface(
5 copy_option IN NUMBER,
6 from_org_id IN NUMBER,
7 to_org_id IN NUMBER,
8 from_cst_type_id IN NUMBER,
9 to_cst_type_id IN NUMBER,
10 range_option IN NUMBER,
11 spec_item_id IN NUMBER,
12 spec_cat_set_id IN NUMBER,
13 spec_cat_id IN NUMBER,
14 grp_id IN NUMBER,
15 conv_type IN VARCHAR2,
16 l_last_updated_by IN NUMBER,
17 error_msg OUT NOCOPY VARCHAR2
18 ) RETURN INTEGER IS
19
20 location NUMBER := 0;
21 l_from_currency_code VARCHAR2(15);
22 l_to_currency_code VARCHAR2(15);
23 l_conversion_rate NUMBER := 1;
24 l_no_of_rows NUMBER := 0;
25
26 /* 11.5.10+ New variables to support UOM conversion */
27 /* This cursor selects from a global temp table that *
28 * was added for 11.5.10+ CST_UOM_CONV_RATES_TEMP. */
29 CURSOR cur_uom_conv IS
30 SELECT item_name
31 FROM cst_uom_conv_rates_temp
32 WHERE uom_conversion_rate IS NULL
33 OR uom_conversion_rate IN (0, -99999);
34 c_missing_uom_conv cur_uom_conv%ROWTYPE; -- cursor variable
35 uom_exception EXCEPTION; -- raised when no UOM conversion defined
36 CONC_STATUS BOOLEAN; -- variable for SET_COMPLETION_STATUS
37
38 BEGIN
39 /* Obtain currency information */
40 /*
41 select currency_code
42 into l_from_currency_code
43 from cst_organization_definitions
44 where organization_id = from_org_id;
45
46 select currency_code
47 into l_to_currency_code
48 from cst_organization_definitions
49 where organization_id = to_org_id;
50 */
51 --bug5839929
52 select distinct
53 sob_from.currency_code,
54 sob_to.currency_code
55 into l_from_currency_code,
56 l_to_currency_code
57 from org_organization_definitions ood_from,
58 gl_sets_of_books sob_from,
59 org_organization_definitions ood_to,
60 gl_sets_of_books sob_to
61 where ood_from.organization_id = from_org_id
62 and sob_from.set_of_books_id = ood_from.set_of_books_id
63 and ood_to.organization_id = to_org_id
64 and sob_to.set_of_books_id = ood_to.set_of_books_id;
65
66 l_conversion_rate := gl_currency_api.get_rate
67 ( l_from_currency_code,
68 l_to_currency_code,
69 sysdate,
70 conv_type );
71
72 if (l_conversion_rate IS NULL) then
73 l_conversion_rate := 1;
74 end if;
75
76 /* 11.5.10+ Copy Cost UOM Conversion *
77 /* Populate the global temp table with the UOM conversion rates for any items *
78 * where the from UOM <> to UOM *
79 */
80
81 INSERT INTO CST_UOM_CONV_RATES_TEMP (INVENTORY_ITEM_ID, UOM_CONVERSION_RATE, ITEM_NAME)
82 SELECT src.inventory_item_id,
83 inv_convert.inv_um_convert(src.inventory_item_id, 30, NULL, src.primary_uom_code, dst.primary_uom_code, NULL, NULL),
84 substr(src.concatenated_segments,1,50)
85 FROM mtl_system_items_kfv src, mtl_system_items_b dst, cst_item_costs cic
86 WHERE cic.cost_type_id = from_cst_type_id
87 AND (
88 /* -- ALL ITEMS -- */
89 (range_option = 1)
90 OR
91 /* -- SPECIFIC ITEM -- */
92 ( (range_option = 2) AND (
93 CIC.INVENTORY_ITEM_ID = spec_item_id
94 )
95 )
96 OR
97 /* -- SPECIFIC CATEGORY -- */
98 ( (range_option = 5) AND EXISTS
99 (
100 SELECT INVENTORY_ITEM_ID
101 FROM MTL_ITEM_CATEGORIES MIC
102 WHERE MIC.ORGANIZATION_ID = CIC.ORGANIZATION_ID
103 AND MIC.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
104 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
105 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
106 )
107 )
108 )
109 AND cic.organization_id = from_org_id
110 AND src.organization_id = cic.organization_id
111 AND dst.organization_id = to_org_id
112 AND src.inventory_item_id = cic.inventory_item_id
113 AND src.inventory_item_id = dst.inventory_item_id
114 AND src.primary_uom_code <> dst.primary_uom_code
115 AND ( -- This is necessary because you could incorrectly abort the program
116 ( (copy_option = 2) AND -- for items you're not trying to copy (based on copy option of
117 ( NOT EXISTS ( -- "New Cost Information Only")
118 SELECT NULL
119 FROM CST_ITEM_COSTS CIC2
120 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
121 AND CIC2.ORGANIZATION_ID = to_org_id
122 AND CIC2.COST_TYPE_ID = to_cst_type_id
123 )
124 )
125 ) OR
126 (copy_option in (1,3))
127 );
128
129 /* Now that the global temp table is populated, check if any UOM rates are undefined.
130 * If so, abort the program and print the offending item(s)
131 */
132 OPEN cur_uom_conv;
133 FETCH cur_uom_conv INTO c_missing_uom_conv;
134 if (cur_uom_conv%FOUND) then
135 raise uom_exception;
136 else
137 CLOSE cur_uom_conv;
138 end if;
139
140 /* end of addition for 11.5.10+ */
141
142
143 /* Deleteting destination starts here */
144
145 if (copy_option = 3) then
146
147 location := 1;
148
149 DELETE FROM CST_ITEM_COSTS CIC
150 WHERE CIC.ORGANIZATION_ID = to_org_id
151 AND CIC.COST_TYPE_ID = to_cst_type_id
152 AND (
153 /* -- ALL ITEMS -- */
154 (range_option = 1)
155 OR
156 /* -- SPECIFIC ITEM -- */
157 ( (range_option = 2) AND (
158 CIC.INVENTORY_ITEM_ID = spec_item_id
159 )
160 )
161 OR
162 /* -- SPECIFIC CATEGORY -- */
163 ( (range_option = 5) AND EXISTS
164 (
165 SELECT INVENTORY_ITEM_ID
166 FROM MTL_ITEM_CATEGORIES MIC
167 WHERE MIC.ORGANIZATION_ID = to_org_id
168 AND MIC.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
169 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
170 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
171 )
172 )
173 );
174
175 location := 2;
176
177 DELETE FROM CST_ITEM_COST_DETAILS CICD
178 WHERE CICD.ORGANIZATION_ID = to_org_id
179 AND CICD.COST_TYPE_ID = to_cst_type_id
180 AND (
181 /* -- ALL ITEMS -- */
182 (range_option = 1)
183 OR
184 /* -- SPECIFIC ITEM -- */
185 ( (range_option = 2) AND (
186 CICD.INVENTORY_ITEM_ID = spec_item_id
187 )
188 )
189 OR
190 /* -- SPECIFIC CATEGORY -- */
191 ( (range_option = 5) AND EXISTS
192 (
193 SELECT INVENTORY_ITEM_ID
194 FROM MTL_ITEM_CATEGORIES MIC
195 WHERE MIC.ORGANIZATION_ID = to_org_id
196 AND MIC.INVENTORY_ITEM_ID = CICD.INVENTORY_ITEM_ID
197 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
198 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
199 )
200 )
201 );
202
203 end if; /* COPY OPTION = 3 DELETE */
204
205 if ( copy_option = 1 ) then
206
207 location := 3;
208
209 DELETE FROM CST_ITEM_COSTS CIC
210 WHERE EXISTS (
211 SELECT 'Item exists in from org'
212 FROM CST_ITEM_COST_DETAILS CIC2
213 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
214 AND CIC2.ORGANIZATION_ID = from_org_id
215 AND CIC2.COST_TYPE_ID = from_cst_type_id
216 )
217 AND CIC.ORGANIZATION_ID = to_org_id
218 AND CIC.COST_TYPE_ID = to_cst_type_id
219 AND (
220 /* -- ALL ITEMS -- */
221 (range_option = 1)
222 OR
223 /* -- SPECIFIC ITEM -- */
224 ( (range_option = 2) AND (
225 CIC.INVENTORY_ITEM_ID = spec_item_id
226 )
227 )
228 OR
229 /* -- SPECIFIC CATEGORY -- */
230 ( (range_option = 5) AND EXISTS
231 (
232 SELECT INVENTORY_ITEM_ID
233 FROM MTL_ITEM_CATEGORIES MIC
234 WHERE MIC.ORGANIZATION_ID = to_org_id
235 AND MIC.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
236 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
237 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
238 )
239 )
240 );
241
242
243 /* following delete needs to change */
244
245 location := 4;
246
247 DELETE FROM CST_ITEM_COST_DETAILS CICD
248 WHERE EXISTS (
249 SELECT 'Item exists in from org'
250 FROM CST_ITEM_COST_DETAILS CIC2
251 WHERE CIC2.INVENTORY_ITEM_ID = CICD.INVENTORY_ITEM_ID
252 AND CIC2.ORGANIZATION_ID = from_org_id
253 AND CIC2.COST_TYPE_ID = from_cst_type_id
254 )
255 AND CICD.ORGANIZATION_ID = to_org_id
256 AND CICD.COST_TYPE_ID = to_cst_type_id
257 AND (
258 /* -- ALL ITEMS -- */
259 (range_option = 1)
260 OR
261 /* -- SPECIFIC ITEM -- */
262 ( (range_option = 2) AND (
263 CICD.INVENTORY_ITEM_ID = spec_item_id
264 )
265 )
266 OR
267 /* -- SPECIFIC CATEGORY -- */
268 ( (range_option = 5) AND EXISTS
269 (
270 SELECT INVENTORY_ITEM_ID
271 FROM MTL_ITEM_CATEGORIES MIC
272 WHERE MIC.ORGANIZATION_ID = to_org_id
273 AND MIC.INVENTORY_ITEM_ID = CICD.INVENTORY_ITEM_ID
274 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
275 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
276 )
277 )
278 );
279
280
281 end if;
282
283
284 /*
285 * Copy over non-code columns into CST_ITEM_COSTS_INTERFACE
286 * from CST_ITEM_COSTS
287 *
288 * CST_ITEM_COSTS ==> CST_ITEM_COSTS_INTERFACE
289 */
290
291 location := 5;
292
293 if (range_option = 1) then
294
295 /* ---- All Items ---- */
296
297 INSERT INTO CST_ITEM_COSTS_INTERFACE (
298 INVENTORY_ITEM_ID,
299 ORGANIZATION_ID,
300 COST_TYPE_ID,
301 INVENTORY_ITEM,
302 ORGANIZATION_CODE,
303 COST_TYPE,
304 LAST_UPDATE_DATE,
305 LAST_UPDATED_BY,
306 CREATION_DATE,
307 CREATED_BY,
308 LAST_UPDATE_LOGIN,
309 INVENTORY_ASSET_FLAG,
310 LOT_SIZE,
311 BASED_ON_ROLLUP_FLAG,
312 SHRINKAGE_RATE,
313 DEFAULTED_FLAG,
314 COST_UPDATE_ID,
315 PL_MATERIAL,
316 PL_MATERIAL_OVERHEAD,
317 PL_RESOURCE,
318 PL_OUTSIDE_PROCESSING,
319 PL_OVERHEAD,
320 TL_MATERIAL,
321 TL_MATERIAL_OVERHEAD,
322 TL_RESOURCE,
323 TL_OUTSIDE_PROCESSING,
324 TL_OVERHEAD,
325 MATERIAL_COST,
326 MATERIAL_OVERHEAD_COST,
327 RESOURCE_COST,
328 OUTSIDE_PROCESSING_COST,
329 OVERHEAD_COST,
330 PL_ITEM_COST,
331 TL_ITEM_COST,
332 ITEM_COST,
333 UNBURDENED_COST,
334 BURDEN_COST,
335 ATTRIBUTE_CATEGORY,
336 ATTRIBUTE1,
337 ATTRIBUTE2,
338 ATTRIBUTE3,
339 ATTRIBUTE4,
340 ATTRIBUTE5,
341 ATTRIBUTE6,
342 ATTRIBUTE7,
343 ATTRIBUTE8,
344 ATTRIBUTE9,
345 ATTRIBUTE10,
346 ATTRIBUTE11,
347 ATTRIBUTE12,
348 ATTRIBUTE13,
349 ATTRIBUTE14,
350 ATTRIBUTE15,
351 REQUEST_ID,
352 PROGRAM_APPLICATION_ID,
353 PROGRAM_ID,
354 PROGRAM_UPDATE_DATE,
355 GROUP_ID
356 ) SELECT
357 CIC.INVENTORY_ITEM_ID,
358 to_org_id,
359 to_cst_type_id,
360 MIF.ITEM_NUMBER,
361 MP.ORGANIZATION_CODE,
362 CCT.COST_TYPE,
363 SYSDATE,
364 l_last_updated_by,
365 SYSDATE,
366 l_last_updated_by,
367 NULL,
368 CIC.INVENTORY_ASSET_FLAG,
369 CIC.LOT_SIZE,
370 CIC.BASED_ON_ROLLUP_FLAG,
371 CIC.SHRINKAGE_RATE,
372 CIC.DEFAULTED_FLAG,
373 CIC.COST_UPDATE_ID,
374 CIC.PL_MATERIAL * nvl(l_conversion_rate,1),
375 CIC.PL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
376 CIC.PL_RESOURCE * nvl(l_conversion_rate,1),
377 CIC.PL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
378 CIC.PL_OVERHEAD * nvl(l_conversion_rate,1),
379 CIC.TL_MATERIAL * nvl(l_conversion_rate,1),
380 CIC.TL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
381 CIC.TL_RESOURCE * nvl(l_conversion_rate,1),
382 CIC.TL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
383 CIC.TL_OVERHEAD * nvl(l_conversion_rate,1),
384 CIC.MATERIAL_COST * nvl(l_conversion_rate,1),
385 CIC.MATERIAL_OVERHEAD_COST * nvl(l_conversion_rate,1),
386 CIC.RESOURCE_COST * nvl(l_conversion_rate,1),
387 CIC.OUTSIDE_PROCESSING_COST * nvl(l_conversion_rate,1),
388 CIC.OVERHEAD_COST * nvl(l_conversion_rate,1),
389 CIC.PL_ITEM_COST * nvl(l_conversion_rate,1),
390 CIC.TL_ITEM_COST * nvl(l_conversion_rate,1),
391 CIC.ITEM_COST * nvl(l_conversion_rate,1),
392 CIC.UNBURDENED_COST * nvl(l_conversion_rate,1),
393 CIC.BURDEN_COST * nvl(l_conversion_rate,1),
394 CIC.ATTRIBUTE_CATEGORY,
395 CIC.ATTRIBUTE1,
396 CIC.ATTRIBUTE2,
397 CIC.ATTRIBUTE3,
398 CIC.ATTRIBUTE4,
399 CIC.ATTRIBUTE5,
400 CIC.ATTRIBUTE6,
401 CIC.ATTRIBUTE7,
402 CIC.ATTRIBUTE8,
403 CIC.ATTRIBUTE9,
404 CIC.ATTRIBUTE10,
405 CIC.ATTRIBUTE11,
406 CIC.ATTRIBUTE12,
407 CIC.ATTRIBUTE13,
408 CIC.ATTRIBUTE14,
409 CIC.ATTRIBUTE15,
410 CIC.REQUEST_ID,
414 grp_id
411 CIC.PROGRAM_APPLICATION_ID,
412 CIC.PROGRAM_ID,
413 CIC.PROGRAM_UPDATE_DATE,
415 FROM CST_ITEM_COSTS CIC,
416 MTL_ITEM_FLEXFIELDS MIF,
417 CST_COST_TYPES CCT,
418 MTL_PARAMETERS MP
419 WHERE CIC.ORGANIZATION_ID = from_org_id
420 AND CIC.COST_TYPE_ID = from_cst_type_id
421 AND MP.ORGANIZATION_ID = to_org_id
422 AND NVL( CCT.ORGANIZATION_ID, to_org_id) = to_org_id
423 AND CCT.COST_TYPE_ID = to_cst_type_id
424 AND MIF.ORGANIZATION_ID = to_org_id
425 AND MIF.ITEM_ID = CIC.INVENTORY_ITEM_ID
426 AND (
427 ( (copy_option = 2 or copy_option = 1) AND
428 ( NOT EXISTS (
429 SELECT NULL
430 FROM CST_ITEM_COSTS CIC2
431 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
432 AND CIC2.ORGANIZATION_ID = to_org_id
433 AND CIC2.COST_TYPE_ID = to_cst_type_id
434 )
435 )
436 ) OR
437 (copy_option = 3)
438 );
439
440 elsif (range_option = 2) then
441
442 /* ---- Specific Item ---- */
443
444 SELECT COUNT(*) INTO l_no_of_rows FROM CST_ITEM_COSTS CIC
445 WHERE CIC.ORGANIZATION_ID = from_org_id
446 AND CIC.COST_TYPE_ID = from_cst_type_id
447 AND CIC.INVENTORY_ITEM_ID = spec_item_id;
448
449 if(l_no_of_rows = 0) then
450 commit;
451 RETURN(2);
452 end if;
453
454 INSERT INTO CST_ITEM_COSTS_INTERFACE (
455 INVENTORY_ITEM_ID,
456 ORGANIZATION_ID,
457 COST_TYPE_ID,
458 INVENTORY_ITEM,
459 ORGANIZATION_CODE,
460 COST_TYPE,
461 LAST_UPDATE_DATE,
462 LAST_UPDATED_BY,
463 CREATION_DATE,
464 CREATED_BY,
465 LAST_UPDATE_LOGIN,
466 INVENTORY_ASSET_FLAG,
467 LOT_SIZE,
468 BASED_ON_ROLLUP_FLAG,
469 SHRINKAGE_RATE,
470 DEFAULTED_FLAG,
471 COST_UPDATE_ID,
472 PL_MATERIAL,
473 PL_MATERIAL_OVERHEAD,
474 PL_RESOURCE,
475 PL_OUTSIDE_PROCESSING,
476 PL_OVERHEAD,
477 TL_MATERIAL,
478 TL_MATERIAL_OVERHEAD,
479 TL_RESOURCE,
480 TL_OUTSIDE_PROCESSING,
481 TL_OVERHEAD,
482 MATERIAL_COST,
483 MATERIAL_OVERHEAD_COST,
484 RESOURCE_COST,
485 OUTSIDE_PROCESSING_COST,
486 OVERHEAD_COST,
487 PL_ITEM_COST,
488 TL_ITEM_COST,
489 ITEM_COST,
490 UNBURDENED_COST,
491 BURDEN_COST,
492 ATTRIBUTE_CATEGORY,
493 ATTRIBUTE1,
494 ATTRIBUTE2,
495 ATTRIBUTE3,
496 ATTRIBUTE4,
497 ATTRIBUTE5,
498 ATTRIBUTE6,
499 ATTRIBUTE7,
500 ATTRIBUTE8,
501 ATTRIBUTE9,
502 ATTRIBUTE10,
503 ATTRIBUTE11,
504 ATTRIBUTE12,
505 ATTRIBUTE13,
506 ATTRIBUTE14,
507 ATTRIBUTE15,
508 REQUEST_ID,
509 PROGRAM_APPLICATION_ID,
510 PROGRAM_ID,
511 PROGRAM_UPDATE_DATE,
512 GROUP_ID
513 ) SELECT
514 CIC.INVENTORY_ITEM_ID,
515 to_org_id,
516 to_cst_type_id,
517 MIF.ITEM_NUMBER,
518 MP.ORGANIZATION_CODE,
519 CCT.COST_TYPE,
520 SYSDATE,
521 l_last_updated_by,
522 SYSDATE,
523 l_last_updated_by,
524 NULL,
525 CIC.INVENTORY_ASSET_FLAG,
526 CIC.LOT_SIZE,
527 CIC.BASED_ON_ROLLUP_FLAG,
528 CIC.SHRINKAGE_RATE,
529 CIC.DEFAULTED_FLAG,
530 CIC.COST_UPDATE_ID,
531 CIC.PL_MATERIAL * nvl(l_conversion_rate,1),
532 CIC.PL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
533 CIC.PL_RESOURCE * nvl(l_conversion_rate,1),
534 CIC.PL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
535 CIC.PL_OVERHEAD * nvl(l_conversion_rate,1),
536 CIC.TL_MATERIAL * nvl(l_conversion_rate,1),
537 CIC.TL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
538 CIC.TL_RESOURCE * nvl(l_conversion_rate,1),
539 CIC.TL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
540 CIC.TL_OVERHEAD * nvl(l_conversion_rate,1),
541 CIC.MATERIAL_COST * nvl(l_conversion_rate,1),
542 CIC.MATERIAL_OVERHEAD_COST * nvl(l_conversion_rate,1),
543 CIC.RESOURCE_COST * nvl(l_conversion_rate,1),
544 CIC.OUTSIDE_PROCESSING_COST * nvl(l_conversion_rate,1),
545 CIC.OVERHEAD_COST * nvl(l_conversion_rate,1),
546 CIC.PL_ITEM_COST * nvl(l_conversion_rate,1),
547 CIC.TL_ITEM_COST * nvl(l_conversion_rate,1),
548 CIC.ITEM_COST * nvl(l_conversion_rate,1),
549 CIC.UNBURDENED_COST * nvl(l_conversion_rate,1),
550 CIC.BURDEN_COST * nvl(l_conversion_rate,1),
551 CIC.ATTRIBUTE_CATEGORY,
552 CIC.ATTRIBUTE1,
553 CIC.ATTRIBUTE2,
554 CIC.ATTRIBUTE3,
555 CIC.ATTRIBUTE4,
556 CIC.ATTRIBUTE5,
557 CIC.ATTRIBUTE6,
558 CIC.ATTRIBUTE7,
559 CIC.ATTRIBUTE8,
560 CIC.ATTRIBUTE9,
561 CIC.ATTRIBUTE10,
562 CIC.ATTRIBUTE11,
563 CIC.ATTRIBUTE12,
564 CIC.ATTRIBUTE13,
565 CIC.ATTRIBUTE14,
566 CIC.ATTRIBUTE15,
567 CIC.REQUEST_ID,
568 CIC.PROGRAM_APPLICATION_ID,
569 CIC.PROGRAM_ID,
570 CIC.PROGRAM_UPDATE_DATE,
571 grp_id
572 FROM CST_ITEM_COSTS CIC,
573 MTL_ITEM_FLEXFIELDS MIF,
574 CST_COST_TYPES CCT,
575 MTL_PARAMETERS MP
576 WHERE CIC.ORGANIZATION_ID = from_org_id
577 AND CIC.COST_TYPE_ID = from_cst_type_id
578 AND CIC.INVENTORY_ITEM_ID = spec_item_id
579 AND MIF.ITEM_ID = CIC.INVENTORY_ITEM_ID
580 AND MIF.ORGANIZATION_ID = to_org_id
584 AND (
581 AND MP.ORGANIZATION_ID = to_org_id
582 AND NVL( CCT.ORGANIZATION_ID, to_org_id) = to_org_id
583 AND CCT.COST_TYPE_ID = to_cst_type_id
585 ( (copy_option = 2 or copy_option = 1) AND
586 ( NOT EXISTS (
587 SELECT NULL
588 FROM CST_ITEM_COSTS CIC2
589 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
590 AND CIC2.ORGANIZATION_ID = to_org_id
591 AND CIC2.COST_TYPE_ID = to_cst_type_id
592 )
593 )
594 ) OR
595 (copy_option = 3)
596 );
597
598 elsif (range_option = 5) then
599
600 /* ---- Specific Category ---- */
601
602 INSERT INTO CST_ITEM_COSTS_INTERFACE (
603 INVENTORY_ITEM_ID,
604 ORGANIZATION_ID,
605 COST_TYPE_ID,
606 INVENTORY_ITEM,
607 ORGANIZATION_CODE,
608 COST_TYPE,
609 LAST_UPDATE_DATE,
610 LAST_UPDATED_BY,
611 CREATION_DATE,
612 CREATED_BY,
613 LAST_UPDATE_LOGIN,
614 INVENTORY_ASSET_FLAG,
615 LOT_SIZE,
616 BASED_ON_ROLLUP_FLAG,
617 SHRINKAGE_RATE,
618 DEFAULTED_FLAG,
619 COST_UPDATE_ID,
620 PL_MATERIAL,
621 PL_MATERIAL_OVERHEAD,
622 PL_RESOURCE,
623 PL_OUTSIDE_PROCESSING,
624 PL_OVERHEAD,
625 TL_MATERIAL,
626 TL_MATERIAL_OVERHEAD,
627 TL_RESOURCE,
628 TL_OUTSIDE_PROCESSING,
629 TL_OVERHEAD,
630 MATERIAL_COST,
631 MATERIAL_OVERHEAD_COST,
632 RESOURCE_COST,
633 OUTSIDE_PROCESSING_COST,
634 OVERHEAD_COST,
635 PL_ITEM_COST,
636 TL_ITEM_COST,
637 ITEM_COST,
638 UNBURDENED_COST,
639 BURDEN_COST,
640 ATTRIBUTE_CATEGORY,
641 ATTRIBUTE1,
642 ATTRIBUTE2,
643 ATTRIBUTE3,
644 ATTRIBUTE4,
645 ATTRIBUTE5,
646 ATTRIBUTE6,
647 ATTRIBUTE7,
648 ATTRIBUTE8,
649 ATTRIBUTE9,
650 ATTRIBUTE10,
651 ATTRIBUTE11,
652 ATTRIBUTE12,
653 ATTRIBUTE13,
654 ATTRIBUTE14,
655 ATTRIBUTE15,
656 REQUEST_ID,
657 PROGRAM_APPLICATION_ID,
658 PROGRAM_ID,
659 PROGRAM_UPDATE_DATE,
660 GROUP_ID
661 ) SELECT
662 CIC.INVENTORY_ITEM_ID,
663 to_org_id,
664 to_cst_type_id,
665 MIF.ITEM_NUMBER,
666 MP.ORGANIZATION_CODE,
667 CCT.COST_TYPE,
668 SYSDATE,
669 l_last_updated_by,
670 SYSDATE,
671 l_last_updated_by,
672 NULL,
673 CIC.INVENTORY_ASSET_FLAG,
674 CIC.LOT_SIZE,
675 CIC.BASED_ON_ROLLUP_FLAG,
676 CIC.SHRINKAGE_RATE,
677 CIC.DEFAULTED_FLAG,
678 CIC.COST_UPDATE_ID,
679 CIC.PL_MATERIAL * nvl(l_conversion_rate,1),
680 CIC.PL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
681 CIC.PL_RESOURCE * nvl(l_conversion_rate,1),
682 CIC.PL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
683 CIC.PL_OVERHEAD * nvl(l_conversion_rate,1),
684 CIC.TL_MATERIAL * nvl(l_conversion_rate,1),
685 CIC.TL_MATERIAL_OVERHEAD * nvl(l_conversion_rate,1),
686 CIC.TL_RESOURCE * nvl(l_conversion_rate,1),
687 CIC.TL_OUTSIDE_PROCESSING * nvl(l_conversion_rate,1),
688 CIC.TL_OVERHEAD * nvl(l_conversion_rate,1),
689 CIC.MATERIAL_COST * nvl(l_conversion_rate,1),
690 CIC.MATERIAL_OVERHEAD_COST * nvl(l_conversion_rate,1),
691 CIC.RESOURCE_COST * nvl(l_conversion_rate,1),
692 CIC.OUTSIDE_PROCESSING_COST * nvl(l_conversion_rate,1),
693 CIC.OVERHEAD_COST * nvl(l_conversion_rate,1),
694 CIC.PL_ITEM_COST * nvl(l_conversion_rate,1),
695 CIC.TL_ITEM_COST * nvl(l_conversion_rate,1),
696 CIC.ITEM_COST * nvl(l_conversion_rate,1),
697 CIC.UNBURDENED_COST * nvl(l_conversion_rate,1),
698 CIC.BURDEN_COST * nvl(l_conversion_rate,1),
699 CIC.ATTRIBUTE_CATEGORY,
700 CIC.ATTRIBUTE1,
701 CIC.ATTRIBUTE2,
702 CIC.ATTRIBUTE3,
703 CIC.ATTRIBUTE4,
704 CIC.ATTRIBUTE5,
705 CIC.ATTRIBUTE6,
706 CIC.ATTRIBUTE7,
707 CIC.ATTRIBUTE8,
708 CIC.ATTRIBUTE9,
709 CIC.ATTRIBUTE10,
710 CIC.ATTRIBUTE11,
711 CIC.ATTRIBUTE12,
712 CIC.ATTRIBUTE13,
713 CIC.ATTRIBUTE14,
714 CIC.ATTRIBUTE15,
715 CIC.REQUEST_ID,
716 CIC.PROGRAM_APPLICATION_ID,
717 CIC.PROGRAM_ID,
718 CIC.PROGRAM_UPDATE_DATE,
719 grp_id
720 FROM CST_ITEM_COSTS CIC,
721 MTL_ITEM_FLEXFIELDS MIF,
722 CST_COST_TYPES CCT,
723 MTL_PARAMETERS MP
724 WHERE CIC.ORGANIZATION_ID = from_org_id
725 AND CIC.COST_TYPE_ID = from_cst_type_id
726 AND MP.ORGANIZATION_ID = to_org_id
727 AND NVL( CCT.ORGANIZATION_ID, to_org_id) = to_org_id
728 AND CCT.COST_TYPE_ID = to_cst_type_id
729 AND MIF.ORGANIZATION_ID = to_org_id
730 AND MIF.ITEM_ID = CIC.INVENTORY_ITEM_ID
731 AND CIC.INVENTORY_ITEM_ID in (
732 SELECT INVENTORY_ITEM_ID
733 FROM MTL_ITEM_CATEGORIES MIC
734 WHERE MIC.ORGANIZATION_ID = to_org_id
735 AND MIC.CATEGORY_SET_ID = spec_cat_set_id
736 AND MIC.CATEGORY_ID = DECODE(spec_cat_id,0,MIC.CATEGORY_ID,spec_cat_id)
737 )
738 AND (
739 ( (copy_option = 2 or copy_option = 1) AND
740 ( NOT EXISTS (
741 SELECT NULL
742 FROM CST_ITEM_COSTS CIC2
743 WHERE CIC2.INVENTORY_ITEM_ID = CIC.INVENTORY_ITEM_ID
744 AND CIC2.ORGANIZATION_ID = to_org_id
745 AND CIC2.COST_TYPE_ID = to_cst_type_id
746 )
747 )
751
748 ) OR
749 (copy_option = 3)
750 );
752 end if;
753
754
755 IF SQL%ROWCOUNT = 0 THEN
756 RETURN( 1 );
757 END IF;
758
759 /*
760 * Copy over columns into CST_ITEM_CST_DTLS_INTERFACE
761 * from CST_ITEM_COST_DETAILS
762 *
763 * CST_ITEM_COST_DETAILS ==> CST_ITEM_CST_DTLS_INTERFACE
764 */
765
766 location := 6;
767
768 INSERT INTO CST_ITEM_CST_DTLS_INTERFACE (
769 GROUP_ID,
770 INVENTORY_ITEM_ID,
771 ORGANIZATION_ID,
772 COST_TYPE_ID,
773 INVENTORY_ITEM,
774 COST_TYPE,
775 ORGANIZATION_CODE,
776 LAST_UPDATE_DATE,
777 LAST_UPDATED_BY,
778 CREATION_DATE,
779 CREATED_BY,
780 LAST_UPDATE_LOGIN,
781 OPERATION_SEQUENCE_ID,
782 OPERATION_SEQ_NUM,
783 DEPARTMENT_ID,
784 DEPARTMENT,
785 LEVEL_TYPE,
786 ACTIVITY_ID,
787 ACTIVITY,
788 RESOURCE_SEQ_NUM,
789 RESOURCE_ID,
790 RESOURCE_CODE,
791 RESOURCE_RATE,
792 ITEM_UNITS,
793 ACTIVITY_UNITS,
794 USAGE_RATE_OR_AMOUNT,
795 BASIS_TYPE,
796 BASIS_RESOURCE_ID,
797 BASIS_RESOURCE_CODE,
798 BASIS_FACTOR,
799 NET_YIELD_OR_SHRINKAGE_FACTOR,
800 ITEM_COST,
801 COST_ELEMENT_ID,
802 COST_ELEMENT,
803 ROLLUP_SOURCE_TYPE,
804 ACTIVITY_CONTEXT,
805 REQUEST_ID,
806 PROGRAM_APPLICATION_ID,
807 PROGRAM_ID,
808 PROGRAM_UPDATE_DATE,
809 ATTRIBUTE_CATEGORY,
810 ATTRIBUTE1,
811 ATTRIBUTE2,
812 ATTRIBUTE3,
813 ATTRIBUTE4,
814 ATTRIBUTE5,
815 ATTRIBUTE6,
816 ATTRIBUTE7,
817 ATTRIBUTE8,
818 ATTRIBUTE9,
819 ATTRIBUTE10,
820 ATTRIBUTE11,
821 ATTRIBUTE12,
822 ATTRIBUTE13,
823 ATTRIBUTE14,
824 ATTRIBUTE15,
825 --bug5839929
826 YIELDED_COST
827 ) SELECT /*+ ORDERED */
828 grp_id,
829 CICD.INVENTORY_ITEM_ID,
830 to_org_id,
831 to_cst_type_id,
832 CICI.INVENTORY_ITEM,
833 CICI.COST_TYPE,
834 CICI.ORGANIZATION_CODE,
835 SYSDATE,
836 l_last_updated_by,
837 SYSDATE,
838 l_last_updated_by,
839 -1,
840 NULL,
841 CICD.OPERATION_SEQ_NUM,
842 NULL, /* department_id */
843 BD.DEPARTMENT_CODE,
844 CICD.LEVEL_TYPE,
845 NULL, /* activity_id */
846 CA.ACTIVITY,
847 CICD.RESOURCE_SEQ_NUM,
848 NULL, /* resource_id */
849 BR.RESOURCE_CODE,
850 CICD.RESOURCE_RATE,
851 CICD.ITEM_UNITS,
852 CICD.ACTIVITY_UNITS,
853 decode(cicd.cost_element_id, /* No currency conversion for percentage rates and for some cases of resources and OSP*/
854 2,decode(cicd.basis_type,
855 4,CICD.USAGE_RATE_OR_AMOUNT,
856 5,CICD.USAGE_RATE_OR_AMOUNT,
857 CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1)),
858 3,decode(br.functional_currency_flag,
859 1,CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1),
860 decode(cicd.resource_id,
861 NULL,CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1),
862 CICD.USAGE_RATE_OR_AMOUNT)),
863 4,decode(br.functional_currency_flag,
864 1,CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1),
865 decode(cicd.resource_id,
866 NULL,CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1),
867 CICD.USAGE_RATE_OR_AMOUNT)),
868 5,decode(cicd.basis_type,
869 4,CICD.USAGE_RATE_OR_AMOUNT,
870 CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1)),
871 CICD.USAGE_RATE_OR_AMOUNT * nvl(l_conversion_rate,1)),
872 CICD.BASIS_TYPE,
873 NULL, /* BASIS_RESOURCE_ID */
874 BR.RESOURCE_CODE,
875 CICD.BASIS_FACTOR,
876 CICD.NET_YIELD_OR_SHRINKAGE_FACTOR,
877 CICD.ITEM_COST * nvl(l_conversion_rate,1),
878 CICD.COST_ELEMENT_ID, /* COST_ELEMENT_ID */
879 CCE.COST_ELEMENT,
880 CICD.ROLLUP_SOURCE_TYPE,
881 CICD.ACTIVITY_CONTEXT,
882 CICD.REQUEST_ID,
883 CICD.PROGRAM_APPLICATION_ID,
884 CICD.PROGRAM_ID,
885 CICD.PROGRAM_UPDATE_DATE,
886 CICD.ATTRIBUTE_CATEGORY,
887 CICD.ATTRIBUTE1,
888 CICD.ATTRIBUTE2,
889 CICD.ATTRIBUTE3,
890 CICD.ATTRIBUTE4,
891 CICD.ATTRIBUTE5,
892 CICD.ATTRIBUTE6,
893 CICD.ATTRIBUTE7,
894 CICD.ATTRIBUTE8,
895 CICD.ATTRIBUTE9,
896 CICD.ATTRIBUTE10,
897 CICD.ATTRIBUTE11,
898 CICD.ATTRIBUTE12,
899 CICD.ATTRIBUTE13,
900 CICD.ATTRIBUTE14,
901 CICD.ATTRIBUTE15,
902 --bug5839929
903 CICD.yielded_cost*NVL(l_conversion_rate,1)
904 FROM CST_ITEM_COSTS_INTERFACE CICI,
905 BOM_DEPARTMENTS BD,
906 CST_ACTIVITIES CA,
907 BOM_RESOURCES BR,
908 CST_COST_ELEMENTS CCE,
909 CST_ITEM_COST_DETAILS CICD
910 WHERE CICI.GROUP_ID = grp_id
911 AND CICD.INVENTORY_ITEM_ID = CICI.INVENTORY_ITEM_ID
912 AND CICD.ORGANIZATION_ID = from_org_id
913 AND CICD.COST_TYPE_ID = from_cst_type_id
914 AND BD.ORGANIZATION_ID (+) = from_org_id
918 AND BR.ORGANIZATION_ID (+) = from_org_id
915 AND BD.DEPARTMENT_ID (+) = CICD.DEPARTMENT_ID
916 AND NVL(CA.ORGANIZATION_ID,from_org_id) = from_org_id
917 AND CA.ACTIVITY_ID (+) = CICD.ACTIVITY_ID
919 AND BR.RESOURCE_ID (+) = CICD.RESOURCE_ID
920 AND CCE.COST_ELEMENT_ID (+) = CICD.COST_ELEMENT_ID;
921
922
923 /* 11.5.10+ Copy Cost UOM Conversion *
924 * Multiply the cost columns in the interface tables *
925 * by the UOM conversion rate */
926
927 location := 7;
928
929 UPDATE (
930 SELECT PL_MATERIAL,
931 PL_MATERIAL_OVERHEAD,
932 PL_RESOURCE,
933 PL_OUTSIDE_PROCESSING,
934 PL_OVERHEAD,
935 TL_MATERIAL,
936 TL_MATERIAL_OVERHEAD,
937 TL_RESOURCE,
938 TL_OUTSIDE_PROCESSING,
939 TL_OVERHEAD,
940 MATERIAL_COST,
941 MATERIAL_OVERHEAD_COST,
942 RESOURCE_COST,
943 OUTSIDE_PROCESSING_COST,
944 OVERHEAD_COST,
945 PL_ITEM_COST,
946 TL_ITEM_COST,
947 ITEM_COST,
948 UNBURDENED_COST,
949 BURDEN_COST,
950 LOT_SIZE,
951 uom_conversion_rate
952 FROM cst_item_costs_interface cici, cst_uom_conv_rates_temp cucr
953 WHERE cici.inventory_item_id = cucr.inventory_item_id
954 AND cici.group_id = grp_id)
955 SET PL_MATERIAL = PL_MATERIAL / uom_conversion_rate,
956 PL_MATERIAL_OVERHEAD = PL_MATERIAL_OVERHEAD / uom_conversion_rate,
957 PL_RESOURCE = PL_RESOURCE / uom_conversion_rate,
958 PL_OUTSIDE_PROCESSING = PL_OUTSIDE_PROCESSING / uom_conversion_rate,
959 PL_OVERHEAD = PL_OVERHEAD / uom_conversion_rate,
960 TL_MATERIAL = TL_MATERIAL / uom_conversion_rate,
961 TL_MATERIAL_OVERHEAD = TL_MATERIAL_OVERHEAD / uom_conversion_rate,
962 TL_RESOURCE = TL_RESOURCE / uom_conversion_rate,
963 TL_OUTSIDE_PROCESSING = TL_OUTSIDE_PROCESSING / uom_conversion_rate,
964 TL_OVERHEAD = TL_OVERHEAD / uom_conversion_rate,
965 MATERIAL_COST = MATERIAL_COST / uom_conversion_rate,
966 MATERIAL_OVERHEAD_COST = MATERIAL_OVERHEAD_COST / uom_conversion_rate,
967 RESOURCE_COST = RESOURCE_COST / uom_conversion_rate,
968 OUTSIDE_PROCESSING_COST = OUTSIDE_PROCESSING_COST / uom_conversion_rate,
969 OVERHEAD_COST = OVERHEAD_COST / uom_conversion_rate,
970 PL_ITEM_COST = PL_ITEM_COST / uom_conversion_rate,
971 TL_ITEM_COST = TL_ITEM_COST / uom_conversion_rate,
972 ITEM_COST = ITEM_COST / uom_conversion_rate,
973 UNBURDENED_COST = UNBURDENED_COST / uom_conversion_rate,
974 BURDEN_COST = BURDEN_COST / uom_conversion_rate,
975 LOT_SIZE = LOT_SIZE * uom_conversion_rate;
976
977 location := 8;
978 /* Update the usage_rate_or_amount in CICD for all rows where the basis type IS 'ITEM' *
979 * When the basis type is item there is no basis factor (it's always 1), so you must *
980 * multiply the usage rate of the item by the UOM conversion factor so that usage rate *
981 * times net_yield_or_shrinkage equals item_cost. */
982
983 UPDATE (
984 SELECT USAGE_RATE_OR_AMOUNT,
985 ITEM_COST,
986 uom_conversion_rate
987 FROM cst_item_cst_dtls_interface cicdi, cst_uom_conv_rates_temp cucr
988 WHERE cicdi.inventory_item_id = cucr.inventory_item_id
989 AND cicdi.basis_type = 1
990 AND cicdi.group_id = grp_id)
991 SET USAGE_RATE_OR_AMOUNT = USAGE_RATE_OR_AMOUNT / uom_conversion_rate,
992 ITEM_COST = ITEM_COST / uom_conversion_rate;
993
994 location := 9;
995 /* Update the basis_factor in CICD for all rows where the basis type IS NOT 'ITEM' *
996 * When the basis type is anything other than 'ITEM' (LOT, RESOURCE UNITS, RESOURCE *
997 * VALUE, TOTAL VALUE, ACTIVITY) the basis factor converts between basis and item, so *
998 * we should apply the UOM conversion to the basis factor. */
999
1000 UPDATE (
1001 SELECT BASIS_FACTOR,
1002 ITEM_COST,
1003 uom_conversion_rate
1004 FROM cst_item_cst_dtls_interface cicdi, cst_uom_conv_rates_temp cucr
1005 WHERE cicdi.inventory_item_id = cucr.inventory_item_id
1006 AND cicdi.basis_type <> 1
1007 AND cicdi.group_id = grp_id)
1008 SET BASIS_FACTOR = BASIS_FACTOR / uom_conversion_rate,
1009 ITEM_COST = ITEM_COST / uom_conversion_rate;
1010
1011
1012
1013 /* End of 11.5.10+ additions */
1014
1015 RETURN(0); /* No Error */
1016
1017 EXCEPTION
1018 WHEN uom_exception THEN
1019 fnd_file.put_line(fnd_file.log,'');
1020 fnd_message.set_name('BOM', 'CST_NO_UOM_CONV_RATE');
1021 fnd_file.put_line(fnd_file.log, fnd_message.get);
1022 LOOP
1023 fnd_file.put_line(fnd_file.log,c_missing_uom_conv.item_name);
1024 FETCH cur_uom_conv INTO c_missing_uom_conv;
1025 EXIT WHEN cur_uom_conv%NOTFOUND;
1026 END LOOP;
1027 CLOSE cur_uom_conv;
1028 error_msg := 'copy_to_interface(): Required Unit of Measure conversion rates are missing.';
1029 CONC_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',error_msg);
1030 RETURN(1); -- the calling program will rollback
1031
1032 WHEN OTHERS THEN
1033 error_msg := 'copy_to_interface('||location||'): ' || SQLERRM(100);
1034 RETURN(SQLCODE);
1035
1036
1037 END copy_to_interface;
1038
1039
1040 END CSTPCOIC; /* end package body */