[Home] [Help]
PACKAGE BODY: APPS.CSTPPPOI
Source
1 PACKAGE BODY CSTPPPOI AS
2 /* $Header: CSTPPOIB.pls 120.1 2005/06/21 14:47:42 appldev ship $ */
3
4 PROCEDURE validate_cost_elements (
5 x_interface_header_id IN NUMBER,
6 x_no_of_rows OUT NOCOPY NUMBER,
7 x_err_num OUT NOCOPY NUMBER,
8 x_err_code OUT NOCOPY VARCHAR2,
9 x_err_msg OUT NOCOPY VARCHAR2)
10 IS
11
12 l_stmt_num NUMBER;
13
14 BEGIN
15 ----------------------------------------------------------------------
16 -- Initialize Variables
17 ----------------------------------------------------------------------
18
19 l_stmt_num := 1;
20
21 SELECT count(*)
22 INTO x_no_of_rows
23 FROM cst_pc_cost_det_interface cpicdi
24 WHERE cpicdi.interface_header_id = x_interface_header_id
25 AND cpicdi.cost_element_id NOT IN (1,2,3,4,5);
26
27
28
29
30 EXCEPTION
31
32 WHEN OTHERS THEN
33 ROLLBACK;
34 x_err_num := SQLCODE;
35 x_err_code := NULL;
36 x_err_msg := SUBSTR('CSTPPPOI.validate_cost_elements('
37 || to_char(l_stmt_num)
38 || '): '
39 ||SQLERRM,1,240);
40 END validate_cost_elements;
41
42
43 PROCEDURE validate_level_types (
44 x_interface_header_id IN NUMBER,
45 x_no_of_rows OUT NOCOPY NUMBER,
46 x_err_num OUT NOCOPY NUMBER,
47 x_err_code OUT NOCOPY VARCHAR2,
48 x_err_msg OUT NOCOPY VARCHAR2)
49 IS
50
51 l_stmt_num NUMBER;
52
53 BEGIN
54 ----------------------------------------------------------------------
55 -- Initialize Variables
56 ----------------------------------------------------------------------
57
58 l_stmt_num := 1;
59
60 SELECT count(*)
61 INTO x_no_of_rows
62 FROM cst_pc_cost_det_interface cpicdi
63 WHERE cpicdi.interface_header_id = x_interface_header_id
64 AND cpicdi.level_type NOT IN (1,2);
65
66
67
68
69 EXCEPTION
70
71 WHEN OTHERS THEN
72 ROLLBACK;
73 x_err_num := SQLCODE;
74 x_err_code := NULL;
75 x_err_msg := SUBSTR('CSTPPPOI.validate_level_types('
76 || to_char(l_stmt_num)
77 || '): '
78 ||SQLERRM,1,240);
79
80 END validate_level_types;
81
82
83 PROCEDURE get_le_cg_id (
84 x_interface_header_id IN NUMBER,
85 x_cost_group_id OUT NOCOPY NUMBER,
86 x_legal_entity OUT NOCOPY NUMBER,
87 x_err_num OUT NOCOPY NUMBER,
88 x_err_code OUT NOCOPY VARCHAR2,
89 x_err_msg OUT NOCOPY VARCHAR2)
90 IS
91
92 l_stmt_num NUMBER;
93
94 BEGIN
95 ----------------------------------------------------------------------
96 -- Initialize Variables
97 ----------------------------------------------------------------------
98
99 l_stmt_num := 1;
100
101
102
103 SELECT DISTINCT ccg.cost_group_id,
104 ccg.legal_entity
105 INTO x_cost_group_id,
106 x_legal_entity
107 FROM cst_cost_groups ccg,
108 cst_cost_group_assignments ccga
109 WHERE ccg.cost_group_id = ccga.cost_group_id
110 AND ccg.cost_group_type = 2
111 AND ccg.cost_group = ( SELECT cpici.cost_group
112 FROM cst_pc_item_cost_interface cpici
113 WHERE cpici.interface_header_id = x_interface_header_id);
114
115
116 l_stmt_num := 2;
117
118 UPDATE cst_pc_item_cost_interface cpici
119 SET cpici.cost_group_id = x_cost_group_id
120 WHERE cpici.interface_header_id = x_interface_header_id;
121
122 COMMIT;
123
124 EXCEPTION
125
126 WHEN OTHERS THEN
127 ROLLBACK;
128 x_err_num := SQLCODE;
129 x_err_code := NULL;
130 x_err_msg := SUBSTR('CSTPPPOI.get_le_cg_id('
131 || to_char(l_stmt_num)
132 || '): '
133 ||SQLERRM,1,240);
134
135
136 END get_le_cg_id;
137
138
139
140
141 PROCEDURE get_ct_cm_id (
142 x_interface_header_id IN NUMBER,
143 x_legal_entity IN NUMBER,
144 x_cost_type_id OUT NOCOPY NUMBER,
145 x_primary_cost_method OUT NOCOPY NUMBER,
146 x_err_num OUT NOCOPY NUMBER,
147 x_err_code OUT NOCOPY VARCHAR2,
148 x_err_msg OUT NOCOPY VARCHAR2)
149 IS
150
151 l_stmt_num NUMBER;
152
153 BEGIN
154 ----------------------------------------------------------------------
155 -- Initialize Variables
156 ----------------------------------------------------------------------
157
158 l_stmt_num := 1;
159
160
161 SELECT cct.cost_type_id,
162 clct.primary_cost_method
163 INTO x_cost_type_id,
164 x_primary_cost_method
165 FROM cst_cost_types cct,
166 cst_le_cost_types clct
167 WHERE cct.cost_type_id = clct.cost_type_id
168 AND clct.legal_entity = x_legal_entity
169 AND cct.cost_type = ( SELECT cpici.cost_type
170 FROM cst_pc_item_cost_interface cpici
171 WHERE cpici.interface_header_id = x_interface_header_id );
172
173
174
175 EXCEPTION
176
177 WHEN OTHERS THEN
178 ROLLBACK;
179 x_err_num := SQLCODE;
180 x_err_code := NULL;
181 x_err_msg := SUBSTR('CSTPPPOI.get_ct_cm_id('
182 || to_char(l_stmt_num)
183 || '): '
184 ||SQLERRM,1,240);
185
186
187 END get_ct_cm_id;
188
189
190 PROCEDURE get_pac_id (
191 x_interface_header_id IN NUMBER,
192 x_legal_entity IN NUMBER,
193 x_cost_type_id IN NUMBER,
194 x_pac_period_id OUT NOCOPY NUMBER,
195 x_err_num OUT NOCOPY NUMBER,
196 x_err_code OUT NOCOPY VARCHAR2,
197 x_err_msg OUT NOCOPY VARCHAR2)
198 IS
199
200 l_stmt_num NUMBER;
201
202 BEGIN
203 ----------------------------------------------------------------------
204 -- Initialize Variables
205 ----------------------------------------------------------------------
206
207 l_stmt_num := 1;
208
209 SELECT cpp.pac_period_id
210 INTO x_pac_period_id
211 FROM cst_pac_periods cpp
212 WHERE cpp.period_name = ( SELECT cpici.period_name
213 FROM cst_pc_item_cost_interface cpici
214 WHERE cpici.interface_header_id = x_interface_header_id )
215 AND cpp.pac_period_id =( SELECT MAX(cpp1.pac_period_id )
216 FROM cst_pac_periods cpp1
217 WHERE cpp1.legal_entity = x_legal_entity
218 AND cpp1.cost_type_id = x_cost_type_id );
219
220
221 l_stmt_num := 2;
222
223 UPDATE cst_pc_item_cost_interface cpici
224 SET cpici.pac_period_id = x_pac_period_id
225 WHERE cpici.interface_header_id = x_interface_header_id;
226
227
228 COMMIT;
229
230 EXCEPTION
231
232 WHEN OTHERS THEN
233 ROLLBACK;
234 x_err_num := SQLCODE;
235 x_err_code := NULL;
236 x_err_msg := SUBSTR('CSTPPPOI.get_pac_id('
237 || to_char(l_stmt_num)
238 || '): '
239 ||SQLERRM,1,240);
240
241
242 END get_pac_id;
243
244
245 PROCEDURE validate_item (
246 x_interface_header_id IN NUMBER,
247 x_cost_group_id IN NUMBER,
248 x_item_id OUT NOCOPY NUMBER,
249 x_err_num OUT NOCOPY NUMBER,
250 x_err_code OUT NOCOPY VARCHAR2,
251 x_err_msg OUT NOCOPY VARCHAR2)
252 IS
253
254 l_stmt_num NUMBER;
255
256 BEGIN
257 ----------------------------------------------------------------------
258 -- Initialize Variables
259 ----------------------------------------------------------------------
260
261 l_stmt_num := 1;
262
263
264 SELECT DISTINCT msi.inventory_item_id
265 INTO x_item_id
266 FROM mtl_system_items msi,
267 cst_cost_group_assignments ccga
268 WHERE msi.organization_id = ccga.organization_id
269 AND ccga.cost_group_id = x_cost_group_id
270 AND msi.inventory_item_id = ( SELECT cpici.inventory_item_id
271 FROM cst_pc_item_cost_interface cpici
272 WHERE cpici.interface_header_id = x_interface_header_id );
273
274
275
276 EXCEPTION
277
278 WHEN OTHERS THEN
279 ROLLBACK;
280 x_err_num := SQLCODE;
281 x_err_code := NULL;
282 x_err_msg := SUBSTR('CSTPPPOI.validate_item('
283 || to_char(l_stmt_num)
284 || '): '
285 ||SQLERRM,1,240);
286
287
288 END validate_item;
289
290
291 PROCEDURE validate_cost (
292 x_interface_header_id IN NUMBER,
293 x_item_id IN NUMBER,
294 x_pac_period_id IN NUMBER,
295 x_cost_group_id IN NUMBER,
296 x_no_of_rows OUT NOCOPY NUMBER,
297 x_err_num OUT NOCOPY NUMBER,
298 x_err_code OUT NOCOPY VARCHAR2,
299 x_err_msg OUT NOCOPY VARCHAR2)
300 IS
301
302 l_stmt_num NUMBER;
303
304 BEGIN
305 ----------------------------------------------------------------------
306 -- Initialize Variables
307 ----------------------------------------------------------------------
308
309 l_stmt_num := 1;
310
311
312 SELECT count(*)
313 INTO x_no_of_rows
314 FROM cst_pac_item_costs cpic
315 WHERE cpic.inventory_item_id = x_item_id
316 AND cpic.pac_period_id = x_pac_period_id
317 AND cpic.cost_group_id = x_cost_group_id;
318
319
320
321
322
323 EXCEPTION
324
325 WHEN OTHERS THEN
326 ROLLBACK;
327 x_err_num := SQLCODE;
328 x_err_code := NULL;
329 x_err_msg := SUBSTR('CSTPPPOI.validate_cost('
330 || to_char(l_stmt_num)
331 || '): '
332 ||SQLERRM,1,240);
333
334 END validate_cost;
335
336
337 PROCEDURE validate_market_value (
338 x_interface_header_id IN NUMBER,
339 x_no_of_rows OUT NOCOPY NUMBER,
340 x_err_num OUT NOCOPY NUMBER,
341 x_err_code OUT NOCOPY VARCHAR2,
342 x_err_msg OUT NOCOPY VARCHAR2)
343 IS
344
345 l_stmt_num NUMBER;
346
347 BEGIN
348 ----------------------------------------------------------------------
349 -- Initialize Variables
350 ----------------------------------------------------------------------
351
352 l_stmt_num := 1;
353
354 SELECT count(*)
355 INTO x_no_of_rows
356 FROM cst_pc_item_cost_interface cpici
357 WHERE cpici.interface_header_id = x_interface_header_id
358 AND cpici.market_value > cpici.item_cost;
359
360
361
362
363
364 EXCEPTION
365
366 WHEN OTHERS THEN
367 ROLLBACK;
368 x_err_num := SQLCODE;
369 x_err_code := NULL;
370 x_err_msg := SUBSTR('CSTPPPOI.validate_market_value('
371 || to_char(l_stmt_num)
372 || '): '
373 ||SQLERRM,1,240);
374
375 END validate_market_value;
376
377 PROCEDURE validate_justification (
378 x_interface_header_id IN NUMBER,
379 x_no_of_rows OUT NOCOPY NUMBER,
380 x_err_num OUT NOCOPY NUMBER,
381 x_err_code OUT NOCOPY VARCHAR2,
382 x_err_msg OUT NOCOPY VARCHAR2)
383 IS
384
385 l_stmt_num NUMBER;
386
387 BEGIN
388 ----------------------------------------------------------------------
389 -- Initialize Variables
390 ----------------------------------------------------------------------
391
392 l_stmt_num := 1;
393
394 SELECT count(*)
395 INTO x_no_of_rows
396 FROM cst_pc_item_cost_interface cpici
397 WHERE cpici.interface_header_id = x_interface_header_id
398 AND cpici.market_value IS NOT NULL
399 AND cpici.justification IS NULL;
400
401
402
403
404
405
406 EXCEPTION
407
408 WHEN OTHERS THEN
409 ROLLBACK;
410 x_err_num := SQLCODE;
411 x_err_code := NULL;
412 x_err_msg := SUBSTR('CSTPPPOI.validate_justification('
413 || to_char(l_stmt_num)
414 || '): '
415 ||SQLERRM,1,240);
416
417
418 END validate_justification;
419
420
421
422 PROCEDURE import_costs (
423 x_interface_header_id IN NUMBER,
424 x_user_id IN NUMBER,
425 x_login_id IN NUMBER,
426 x_req_id IN NUMBER,
427 x_prg_appid IN NUMBER,
428 x_prg_id IN NUMBER,
429 x_no_of_rows OUT NOCOPY NUMBER,
430 x_err_num OUT NOCOPY NUMBER,
431 x_err_code OUT NOCOPY VARCHAR2,
432 x_err_msg OUT NOCOPY VARCHAR2)
433 IS
434
435 l_stmt_num NUMBER;
436 no_rows_exception EXCEPTION;
437 l_primary_cost_method NUMBER;
438
439
440 BEGIN
441 ----------------------------------------------------------------------
442 -- Initialize Variables
443 ----------------------------------------------------------------------
444
445 l_stmt_num := 0;
446
447 SELECT clct.primary_cost_method
451 WHERE cct.cost_type_id = clct.cost_type_id
448 INTO l_primary_cost_method
449 FROM cst_cost_types cct,
450 cst_le_cost_types clct
452 AND cct.cost_type =
453 ( SELECT cpici.cost_type
454 FROM cst_pc_item_cost_interface cpici
455 WHERE cpici.interface_header_id = x_interface_header_id )
456 AND clct.legal_entity =
457 (SELECT DISTINCT ccg.legal_entity
458 FROM cst_cost_groups ccg,
459 cst_cost_group_assignments ccga
460 WHERE ccg.cost_group_id = ccga.cost_group_id
461 AND ccg.cost_group_type = 2
462 AND ccg.cost_group =
463 (SELECT cpici.cost_group
464 FROM cst_pc_item_cost_interface cpici
465 WHERE cpici.interface_header_id =
466 x_interface_header_id));
467
468 l_stmt_num := 1;
469
470 INSERT INTO cst_pac_item_costs (
471 COST_LAYER_ID,
472 PAC_PERIOD_ID,
473 COST_GROUP_ID,
474 INVENTORY_ITEM_ID,
475 BUY_QUANTITY,
476 MAKE_QUANTITY,
477 ISSUE_QUANTITY,
478 TOTAL_LAYER_QUANTITY,
479 ITEM_COST,
480 MARKET_VALUE,
481 JUSTIFICATION,
482 ITEM_BUY_COST,
483 ITEM_MAKE_COST,
484 BEGIN_ITEM_COST,
485 MATERIAL_COST,
486 MATERIAL_OVERHEAD_COST,
487 RESOURCE_COST,
488 OVERHEAD_COST,
489 OUTSIDE_PROCESSING_COST,
490 PL_MATERIAL,
491 PL_MATERIAL_OVERHEAD,
492 PL_RESOURCE,
493 PL_OUTSIDE_PROCESSING,
494 PL_OVERHEAD,
495 TL_MATERIAL,
496 TL_MATERIAL_OVERHEAD,
497 TL_RESOURCE,
498 TL_OUTSIDE_PROCESSING,
499 TL_OVERHEAD,
500 PL_ITEM_COST,
501 TL_ITEM_COST,
502 UNBURDENED_COST,
503 BURDEN_COST,
504 LAST_UPDATE_DATE,
505 LAST_UPDATED_BY,
506 CREATION_DATE,
507 CREATED_BY,
508 REQUEST_ID,
509 PROGRAM_APPLICATION_ID,
510 PROGRAM_ID,
511 PROGRAM_UPDATE_DATE,
512 LAST_UPDATE_LOGIN )
513 SELECT COST_LAYER_ID,
514 PAC_PERIOD_ID,
515 COST_GROUP_ID,
516 INVENTORY_ITEM_ID,
517 DECODE(l_primary_cost_method,4,1,BUY_QUANTITY),
518 DECODE(l_primary_cost_method,4,0,MAKE_QUANTITY),
519 ISSUE_QUANTITY,
520 DECODE(l_primary_cost_method,4, layer_quantity,NVL(BEGIN_LAYER_QUANTITY,0)),
521 DECODE(l_primary_cost_method,4,0,ITEM_COST),
522 MARKET_VALUE,
523 JUSTIFICATION,
524 DECODE(l_primary_cost_method,4,item_cost,ITEM_BUY_COST),
525 DECODE(l_primary_cost_method,4,0,ITEM_MAKE_COST),
526 BEGIN_ITEM_COST,
527 MATERIAL_COST,
528 MATERIAL_OVERHEAD_COST,
529 RESOURCE_COST,
530 OVERHEAD_COST,
531 OUTSIDE_PROCESSING_COST,
532 PL_MATERIAL,
533 PL_MATERIAL_OVERHEAD,
534 PL_RESOURCE,
535 PL_OUTSIDE_PROCESSING,
536 PL_OVERHEAD,
537 TL_MATERIAL,
538 TL_MATERIAL_OVERHEAD,
539 TL_RESOURCE,
540 TL_OUTSIDE_PROCESSING,
541 TL_OVERHEAD,
542 PL_ITEM_COST,
543 TL_ITEM_COST,
544 UNBURDENED_COST,
545 BURDEN_COST,
546 SYSDATE,
547 x_user_id,
548 SYSDATE,
549 x_user_id,
550 x_req_id,
554 x_login_id
551 x_prg_appid,
552 x_prg_id,
553 SYSDATE,
555 FROM cst_pc_item_cost_interface cpici
556 WHERE cpici.interface_header_id = x_interface_header_id ;
557
558
559 x_no_of_rows := SQL%ROWCOUNT;
560
561 if(SQL%ROWCOUNT = 0) then
562 RAISE no_rows_exception;
563 end if;
564
565 l_stmt_num := 2;
566
567 INSERT INTO cst_pac_item_cost_details (
568 COST_LAYER_ID,
569 COST_ELEMENT_ID,
570 LEVEL_TYPE,
571 ITEM_COST,
572 ITEM_BUY_COST,
573 ITEM_MAKE_COST,
574 LAST_UPDATE_DATE,
575 LAST_UPDATED_BY,
576 CREATION_DATE,
577 CREATED_BY,
578 REQUEST_ID,
579 PROGRAM_APPLICATION_ID,
580 PROGRAM_ID,
581 PROGRAM_UPDATE_DATE,
582 LAST_UPDATE_LOGIN )
583 SELECT COST_LAYER_ID,
584 COST_ELEMENT_ID,
585 LEVEL_TYPE,
586 ITEM_COST,
587 ITEM_BUY_COST,
588 ITEM_MAKE_COST,
589 SYSDATE,
590 x_user_id,
591 SYSDATE,
592 x_user_id,
593 x_req_id,
594 x_prg_appid,
595 x_prg_id,
596 SYSDATE,
597 x_login_id
598 FROM cst_pc_cost_det_interface cpcdi
599 WHERE cpcdi.interface_header_id = x_interface_header_id ;
600
601
602 if(SQL%ROWCOUNT = 0 AND l_primary_cost_method <> 4) then
603 RAISE no_rows_exception;
604 end if;
605
606
607 l_stmt_num := 3;
608
609 INSERT INTO CST_PAC_QUANTITY_LAYERS (
610 QUANTITY_LAYER_ID,
611 COST_LAYER_ID,
612 PAC_PERIOD_ID,
613 COST_GROUP_ID,
614 INVENTORY_ITEM_ID,
615 BEGIN_LAYER_QUANTITY,
616 LAYER_QUANTITY,
617 LAST_UPDATE_DATE,
618 LAST_UPDATED_BY,
619 CREATION_DATE,
620 CREATED_BY,
621 REQUEST_ID,
622 PROGRAM_APPLICATION_ID,
623 PROGRAM_ID,
624 PROGRAM_UPDATE_DATE,
625 LAST_UPDATE_LOGIN )
626 SELECT QUANTITY_LAYER_ID,
627 COST_LAYER_ID,
628 PAC_PERIOD_ID,
629 COST_GROUP_ID,
630 INVENTORY_ITEM_ID,
631 DECODE(l_primary_cost_method,4,
632 BEGIN_LAYER_QUANTITY,NULL),
633 DECODE(l_primary_cost_method,4,
634 LAYER_QUANTITY-BEGIN_LAYER_QUANTITY,
635 NVL(BEGIN_LAYER_QUANTITY,0)),
636 SYSDATE,
637 x_user_id,
638 SYSDATE,
639 x_user_id,
640 x_req_id,
641 x_prg_appid,
642 x_prg_id,
643 SYSDATE,
644 x_login_id
645 FROM CST_PC_ITEM_COST_INTERFACE cpici
646 WHERE cpici.interface_header_id = x_interface_header_id ;
647
648
649 if(SQL%ROWCOUNT = 0) then
650 RAISE no_rows_exception;
651 end if;
652
653 EXCEPTION
654
655 WHEN no_rows_exception THEN
656 ROLLBACK;
657 x_err_num := -1;
658 x_err_code := NULL;
659 x_err_msg := SUBSTR('CSTPPPOI.import_costs('
660 || to_char(l_stmt_num)
661 || '): '
662 ||'No rows imported ERROR',1,240);
663
664
665 WHEN OTHERS THEN
666 ROLLBACK;
667 x_err_num := SQLCODE;
668 x_err_code := NULL;
669 x_err_msg := SUBSTR('CSTPPPOI.import_costs('
670 || to_char(l_stmt_num)
671 || '): '
672 ||SQLERRM,1,240);
673
674
675 END import_costs;
676
677
678
679 PROCEDURE derive_costs (
683 x_err_msg OUT NOCOPY VARCHAR2)
680 x_interface_header_id IN NUMBER,
681 x_err_num OUT NOCOPY NUMBER,
682 x_err_code OUT NOCOPY VARCHAR2,
684 IS
685
686 l_stmt_num NUMBER;
687 no_rows_exception EXCEPTION;
688
689
690
691 BEGIN
692 ----------------------------------------------------------------------
693 -- Initialize Variables
694 ----------------------------------------------------------------------
695
696 l_stmt_num := 1;
697
698
699 UPDATE cst_pc_cost_det_interface cpcdi
700 SET cpcdi.item_buy_cost = NVL(cpcdi.item_buy_cost,0),
701 cpcdi.item_make_cost = NVL(cpcdi.item_make_cost,0)
702 WHERE cpcdi.interface_header_id = x_interface_header_id;
703
704
705 if(SQL%ROWCOUNT = 0) then
706 RAISE no_rows_exception;
707 end if;
708
709
710 l_stmt_num := 2;
711
712 UPDATE cst_pc_item_cost_interface cpici
713 SET tl_material = ( SELECT NVL(SUM(item_cost),0)
714 FROM cst_pc_cost_det_interface cpcdi
715 WHERE cpcdi.interface_header_id = x_interface_header_id
716 AND level_type = 1
717 AND cost_element_id = 1 )
718 WHERE cpici.interface_header_id = x_interface_header_id;
719
720 if(SQL%ROWCOUNT = 0) then
721 RAISE no_rows_exception;
722 end if;
723
724
725 l_stmt_num := 3;
726
727 UPDATE cst_pc_item_cost_interface cpici
728 SET tl_material_overhead = ( SELECT NVL(SUM(item_cost),0)
729 FROM cst_pc_cost_det_interface cpcdi
730 WHERE cpcdi.interface_header_id = x_interface_header_id
731 AND level_type = 1
732 AND cost_element_id = 2 )
733 WHERE cpici.interface_header_id = x_interface_header_id;
734
735 if(SQL%ROWCOUNT = 0) then
736 RAISE no_rows_exception;
737 end if;
738
739
740 l_stmt_num := 4;
741
742
743 UPDATE cst_pc_item_cost_interface cpici
744 SET tl_resource = ( SELECT NVL(SUM(item_cost),0)
745 FROM cst_pc_cost_det_interface cpcdi
746 WHERE cpcdi.interface_header_id = x_interface_header_id
747 AND level_type = 1
748 AND cost_element_id = 3 )
749 WHERE cpici.interface_header_id = x_interface_header_id;
750
751
752 if(SQL%ROWCOUNT = 0) then
753 RAISE no_rows_exception;
754 end if;
755
756
757 l_stmt_num := 5;
758
759
760 UPDATE cst_pc_item_cost_interface cpici
761 SET tl_outside_processing = ( SELECT NVL(SUM(item_cost),0)
762 FROM cst_pc_cost_det_interface cpcdi
763 WHERE cpcdi.interface_header_id = x_interface_header_id
764 AND level_type = 1
765 AND cost_element_id = 4 )
766 WHERE cpici.interface_header_id = x_interface_header_id;
767
768 if(SQL%ROWCOUNT = 0) then
769 RAISE no_rows_exception;
770 end if;
771
772
773 l_stmt_num := 6;
774
775 UPDATE cst_pc_item_cost_interface cpici
776 SET tl_overhead = ( SELECT NVL(SUM(item_cost),0)
777 FROM cst_pc_cost_det_interface cpcdi
778 WHERE cpcdi.interface_header_id = x_interface_header_id
779 AND level_type = 1
780 AND cost_element_id = 5 )
781 WHERE cpici.interface_header_id = x_interface_header_id;
782
783 if(SQL%ROWCOUNT = 0) then
784 RAISE no_rows_exception;
785 end if;
786
787
788 l_stmt_num := 7;
789
790 UPDATE cst_pc_item_cost_interface cpici
791 SET pl_material = ( SELECT NVL(SUM(item_cost),0)
792 FROM cst_pc_cost_det_interface cpcdi
793 WHERE cpcdi.interface_header_id = x_interface_header_id
794 AND level_type = 2
795 AND cost_element_id = 1 )
796 WHERE cpici.interface_header_id = x_interface_header_id;
797
798 if(SQL%ROWCOUNT = 0) then
799 RAISE no_rows_exception;
800 end if;
801
802
803 l_stmt_num := 8;
804
805 UPDATE cst_pc_item_cost_interface cpici
806 SET pl_material_overhead
807 = ( SELECT NVL(SUM(item_cost),0)
808 FROM cst_pc_cost_det_interface cpcdi
812 WHERE cpici.interface_header_id = x_interface_header_id;
809 WHERE cpcdi.interface_header_id = x_interface_header_id
810 AND level_type = 2
811 AND cost_element_id = 2 )
813
814 if(SQL%ROWCOUNT = 0) then
815 RAISE no_rows_exception;
816 end if;
817
818
819 l_stmt_num := 9;
820
821 UPDATE cst_pc_item_cost_interface cpici
822 SET pl_resource = ( SELECT NVL(SUM(item_cost),0)
823 FROM cst_pc_cost_det_interface cpcdi
824 WHERE cpcdi.interface_header_id = x_interface_header_id
825 AND level_type = 2
826 AND cost_element_id = 3 )
827 WHERE cpici.interface_header_id = x_interface_header_id;
828
829 if(SQL%ROWCOUNT = 0) then
830 RAISE no_rows_exception;
831 end if;
832
833
834 l_stmt_num := 10;
835
836 UPDATE cst_pc_item_cost_interface cpici
837 SET pl_outside_processing = ( SELECT NVL(SUM(item_cost),0)
838 FROM cst_pc_cost_det_interface cpcdi
839 WHERE cpcdi.interface_header_id = x_interface_header_id
840 AND level_type = 2
841 AND cost_element_id = 4 )
842 WHERE cpici.interface_header_id = x_interface_header_id;
843
844 if(SQL%ROWCOUNT = 0) then
845 RAISE no_rows_exception;
846 end if;
847
848
849 l_stmt_num := 11;
850
851
852 UPDATE cst_pc_item_cost_interface cpici
853 SET pl_overhead = ( SELECT NVL(SUM(item_cost),0)
854 FROM cst_pc_cost_det_interface cpcdi
855 WHERE cpcdi.interface_header_id = x_interface_header_id
856 AND level_type = 2
857 AND cost_element_id = 5 )
858 WHERE cpici.interface_header_id = x_interface_header_id;
859
860 if(SQL%ROWCOUNT = 0) then
861 RAISE no_rows_exception;
862 end if;
863
864
865 l_stmt_num := 12;
866
867 UPDATE cst_pc_item_cost_interface cpici
868 SET cpici.tl_item_cost = NVL(NVL(cpici.tl_material,0)+NVL(cpici.tl_material_overhead,0)+NVL(cpici.tl_resource,0)+NVL(cpici.tl_outside_processing,0)+NVL(cpici.tl_overhead,0),0)
869 WHERE cpici.interface_header_id = x_interface_header_id;
870
871 if(SQL%ROWCOUNT = 0) then
872 RAISE no_rows_exception;
873 end if;
874
875
876 l_stmt_num := 13;
877
878 UPDATE cst_pc_item_cost_interface cpici
879 SET cpici.pl_item_cost = NVL(NVL(cpici.pl_material,0)+NVL(cpici.pl_material_overhead,0)+NVL(cpici.pl_resource,0)+NVL(cpici.pl_outside_processing,0)+NVL(cpici.pl_overhead,0),0)
880 WHERE cpici.interface_header_id = x_interface_header_id;
881
882 if(SQL%ROWCOUNT = 0) then
883 RAISE no_rows_exception;
884 end if;
885
886
887 l_stmt_num := 14;
888
889 UPDATE cst_pc_item_cost_interface cpici
890 SET cpici.item_cost = NVL(NVL(cpici.tl_item_cost,0) + NVL(cpici.pl_item_cost,0),0)
891 WHERE cpici.interface_header_id = x_interface_header_id;
892
893 if(SQL%ROWCOUNT = 0) then
894 RAISE no_rows_exception;
895 end if;
896
897
898 l_stmt_num := 15;
899
900 UPDATE cst_pc_item_cost_interface cpici
901 SET cpici.material_cost = NVL(NVL(cpici.tl_material,0) + NVL(cpici.pl_material,0),0)
902 WHERE cpici.interface_header_id = x_interface_header_id;
903
904 if(SQL%ROWCOUNT = 0) then
905 RAISE no_rows_exception;
906 end if;
907
908
909 l_stmt_num := 16;
910
911 UPDATE cst_pc_item_cost_interface cpici
912 SET cpici.material_overhead_cost = NVL(NVL(cpici.tl_material_overhead,0) + NVL(cpici.pl_material_overhead,0),0)
913 WHERE cpici.interface_header_id = x_interface_header_id;
914
915 if(SQL%ROWCOUNT = 0) then
916 RAISE no_rows_exception;
917 end if;
918
919
920 l_stmt_num := 17;
921
922 UPDATE cst_pc_item_cost_interface cpici
923 SET cpici.resource_cost = NVL(NVL(cpici.tl_resource,0) + NVL(cpici.pl_resource,0),0)
924 WHERE cpici.interface_header_id = x_interface_header_id;
925
926 if(SQL%ROWCOUNT = 0) then
927 RAISE no_rows_exception;
928 end if;
929
930
931 l_stmt_num := 18;
932
933
934 UPDATE cst_pc_item_cost_interface cpici
935 SET cpici.outside_processing_cost = NVL(NVL(cpici.tl_outside_processing,0) + NVL(cpici. pl_outside_processing,0),0)
936 WHERE cpici.interface_header_id = x_interface_header_id;
937
938 if(SQL%ROWCOUNT = 0) then
939 RAISE no_rows_exception;
940 end if;
941
942
943 l_stmt_num := 19;
944
945 UPDATE cst_pc_item_cost_interface cpici
946 SET cpici.overhead_cost = NVL(NVL(cpici.tl_overhead,0) + NVL(cpici.pl_overhead,0),0)
947 WHERE cpici.interface_header_id = x_interface_header_id;
948
949 if(SQL%ROWCOUNT = 0) then
950 RAISE no_rows_exception;
951 end if;
952
953
954 l_stmt_num := 20;
955
956 UPDATE cst_pc_item_cost_interface cpici
957 SET cpici.buy_quantity = 0
958 WHERE cpici.interface_header_id = x_interface_header_id;
959
960 if(SQL%ROWCOUNT = 0) then
961 RAISE no_rows_exception;
962 end if;
963
964
965 l_stmt_num := 21;
966
967 UPDATE cst_pc_item_cost_interface cpici
968 SET cpici.make_quantity = 0
969 WHERE cpici.interface_header_id = x_interface_header_id;
970
971 if(SQL%ROWCOUNT = 0) then
972 RAISE no_rows_exception;
973 end if;
974
975
976 l_stmt_num := 22;
977
978 UPDATE cst_pc_item_cost_interface cpici
979 SET cpici.issue_quantity = 0
980 WHERE cpici.interface_header_id = x_interface_header_id;
981
982 if(SQL%ROWCOUNT = 0) then
983 RAISE no_rows_exception;
984 end if;
985
986
987 l_stmt_num := 23;
988
989 UPDATE cst_pc_item_cost_interface cpici
990 SET cpici.unburdened_cost = NVL(NVL(cpici.material_cost,0) + NVL(cpici.resource_cost,0) + NVL(cpici.outside_processing_cost,0),0)
991 WHERE cpici.interface_header_id = x_interface_header_id;
992
993 if(SQL%ROWCOUNT = 0) then
994 RAISE no_rows_exception;
995 end if;
996
997
998 l_stmt_num := 24;
999
1000 UPDATE cst_pc_item_cost_interface cpici
1001 SET cpici.burden_cost = NVL(NVL(cpici.overhead_cost,0) + NVL(cpici.material_overhead_cost,0),0)
1002 WHERE cpici.interface_header_id = x_interface_header_id;
1003
1004 if(SQL%ROWCOUNT = 0) then
1005 RAISE no_rows_exception;
1006 end if;
1007
1008
1009 l_stmt_num := 25;
1010
1011 UPDATE cst_pc_item_cost_interface cpici
1012 SET cpici.item_buy_cost = ( SELECT NVL(SUM(item_buy_cost),0)
1013 FROM cst_pc_cost_det_interface cpcdi
1014 WHERE cpcdi.interface_header_id = x_interface_header_id)
1015 WHERE cpici.interface_header_id = x_interface_header_id;
1016
1017 if(SQL%ROWCOUNT = 0) then
1018 RAISE no_rows_exception;
1019 end if;
1020
1021
1022 l_stmt_num := 26;
1023
1024 UPDATE cst_pc_item_cost_interface cpici
1025 SET cpici.item_make_cost = ( SELECT NVL(SUM(item_make_cost),0)
1026 FROM cst_pc_cost_det_interface cpcdi
1027 WHERE cpcdi.interface_header_id = x_interface_header_id )
1028 WHERE cpici.interface_header_id = x_interface_header_id;
1029
1030 if(SQL%ROWCOUNT = 0) then
1031 RAISE no_rows_exception;
1032 end if;
1033
1034
1035 EXCEPTION
1036
1037 WHEN no_rows_exception THEN
1038 ROLLBACK;
1039 x_err_num := -1;
1040 x_err_code := NULL;
1041 x_err_msg := SUBSTR('CSTPPPOI.derive_costs('
1042 || to_char(l_stmt_num)
1043 || '): '
1044 ||'No rows computed ERROR',1,240);
1045
1046
1047 WHEN OTHERS THEN
1048 ROLLBACK;
1049 x_err_num := SQLCODE;
1050 x_err_code := NULL;
1051 x_err_msg := SUBSTR('CSTPPPOI.derive_costs('
1052 || to_char(l_stmt_num)
1053 || '): '
1054 ||SQLERRM,1,240);
1055
1056
1057 END derive_costs;
1058
1059
1060
1061
1062
1063 END CSTPPPOI;
1064