[Home] [Help]
PACKAGE BODY: APPS.PA_FORECAST_COST
Source
1 PACKAGE BODY PA_FORECAST_COST as
2 /* $Header: PARFRTCB.pls 120.4 2005/08/19 16:52:15 mwasowic noship $ */
3
4
5 l_exp_func_curr_code_null EXCEPTION;
6 l_proj_func_curr_code_null EXCEPTION;
7 l_raw_cost_null EXCEPTION;
8 l_burden_cost_null EXCEPTION;
9 l_x_return_status VARCHAR2(50);
10
11
12 PROCEDURE Get_Raw_Cost(P_person_id IN NUMBER ,
13 P_expenditure_org_id IN NUMBER ,
14 P_labor_Cost_Mult_Name IN VARCHAR2 ,
15 P_Item_date IN DATE ,
16 P_exp_func_curr_code IN OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
17 P_Quantity IN NUMBER ,
18 X_Raw_cost_rate OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
19 X_Raw_cost OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
20 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
21 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
22 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
23 )
24 IS
25
26 l_cost_multiplier pa_labor_cost_multipliers.multiplier%TYPE;
27 l_labor_cost_rate pa_compensation_details_all.HOURLY_COST_RATE%TYPE;
28 l_x_raw_cost NUMBER;
29 l_x_raw_cost_rate NUMBER;
30
31 l_no_labor_cost_rate EXCEPTION;
32
33
34 l_exp_func_curr_code varchar2(15);
35
36 BEGIN
37
38
39 /* ATG Changes */
40
41 l_exp_func_curr_code := p_exp_func_curr_code;
42
43 --------------------------------------------
44 -- Initialize the successfull return status
45 --------------------------------------------
46
47 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
48
49
50
51 -----------------------------------------------------------
52 -- Get the labor cost rate from pa_compensation_details_all
53 -----------------------------------------------------------
54
55 SELECT HOURLY_COST_RATE
56 INTO l_labor_cost_rate
57 FROM PA_COMPENSATION_DETAILS_ALL CD
58 WHERE CD.person_id = P_person_id
59 AND CD.org_id = P_expenditure_org_id
60 AND P_item_date BETWEEN CD.start_date_active AND NVL(CD.end_date_active,P_item_date);
61
62
63 -----------------------------------------------------------------------
64 -- Get the cost multiplier from pa_labor_cost_multipliers for the given
65 -- labor cost multiplier name
66 -----------------------------------------------------------------------
67
68
69 IF P_labor_Cost_Mult_Name IS NOT NULL THEN
70
71
72 BEGIN
73
74
75 SELECT multiplier
76 INTO l_cost_multiplier
77 FROM PA_LABOR_COST_MULTIPLIERS LCM
78 WHERE LCM.LABOR_COST_MULTIPLIER_NAME = P_labor_Cost_Mult_Name
79 AND P_item_date BETWEEN LCM.start_date_active AND NVL(LCM.end_date_active,P_item_date);
80
81
82 EXCEPTION
83 WHEN NO_DATA_FOUND THEN
84 l_cost_multiplier := NULL;
85
86 END;
87
88
89 END IF;
90
91
92
93 ----------------------------------------------------------------------
94 -- If Input expenditure functional currency code is null then call the
95 -- procedure get_curr_code to get the currency code
96 ----------------------------------------------------------------------
97
98
99 IF (p_exp_func_curr_code IS NULL) THEN
100
101 p_exp_func_curr_code := get_curr_code(p_expenditure_org_id);
102
103 END IF;
104
105
106 IF (p_exp_func_curr_code IS NULL) THEN
107
108 RAISE l_exp_func_curr_code_null;
109
110 END IF;
111
112
113 ----------------------------------------
114 -- Calulating Raw cost and Raw cost rate
115 ----------------------------------------
116
117 l_x_raw_cost_rate := l_labor_cost_rate * NVL(l_cost_multiplier,1.0);
118
119 l_x_Raw_cost := pa_currency.round_trans_currency_amt(
120 l_x_raw_cost_rate * nvl(P_Quantity,0), p_exp_func_curr_code);
121
122
123 -------------------------------------------------
124 -- Checking If Calculated raw cost is null or not
125 -------------------------------------------------
126
127 IF (l_x_raw_cost_rate is NULL) OR (l_x_raw_cost is NULL) THEN
128
129 Raise l_raw_cost_null;
130
131 END IF;
132
133
134
135 ----------------------------------------------------------
136 -- Storing Calculated raw cost values into output variable
137 ----------------------------------------------------------
138
139 x_raw_cost_rate := l_x_raw_cost_rate;
140 x_raw_cost := l_x_raw_cost;
141
142
143 -------------------------------------------------------
144 -- Assign the successful status back to output variable
145 -------------------------------------------------------
146
147 x_return_status := l_x_return_status;
148
149
150 EXCEPTION
151 WHEN l_exp_func_curr_code_null THEN
152 PA_UTILS.add_message('PA', 'PA_EXP_FUNC_CURR_CODE_NULL');
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 x_msg_count := 1;
155 x_msg_data := 'PA_EXP_FUNC_CURR_CODE_NULL';
156
157 WHEN NO_DATA_FOUND THEN
158 PA_UTILS.add_message('PA', 'PA_NO_LABOR_COST_RATE');
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 x_msg_count := 1;
161 x_msg_data := 'PA_NO_LABOR_COST_RATE';
162
163 WHEN l_raw_cost_null THEN
164 PA_UTILS.add_message('PA', 'PA_RAW_COST_NULL');
165 x_return_status := FND_API.G_RET_STS_ERROR;
166 x_msg_count := 1;
167 x_msg_data := 'PA_RAW_COST_NULL';
168
169 WHEN OTHERS THEN
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 x_msg_count := 1;
172 x_msg_data := SQLERRM;
173
174 /* ATG Changes */
175
176 p_exp_func_curr_code := l_exp_func_curr_code;
177
178
179 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
180 p_procedure_name => 'Get_Raw_Cost');
181
182 END Get_Raw_Cost;
183
184
185
186 PROCEDURE Override_exp_organization(P_item_date IN DATE ,
187 P_person_id IN NUMBER ,
188 P_project_id IN NUMBER ,
189 P_incurred_by_organz_id IN NUMBER ,
190 P_Expenditure_type IN VARCHAR2 ,
191 X_overr_to_organization_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
192 X_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
193 X_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
194 X_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
195 )
196 IS
197
198
199 l_x_override_to_org_id NUMBER;
200 l_override_organz_id_null EXCEPTION;
201
202
203 BEGIN
204
205
206 --------------------------------------------
207 -- Initialize the successfull return status
208 --------------------------------------------
209
210 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
211
212
213
214 l_x_override_to_org_id := NULL;
215
216
217 BEGIN
218
219 -------------------------------------------------------------
220 -- Organization overrides for person and expenditure_category
221 -------------------------------------------------------------
222
223
224 SELECT OVERRIDE_TO_ORGANIZATION_ID
225 INTO l_x_override_to_org_id
226 FROM pa_cost_dist_overrides CDO,
227 pa_expenditure_types ET
228 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
229 AND CDO.person_id = P_person_id
230 AND CDO.project_id = P_project_id
231 AND CDO.expenditure_category = ET.expenditure_category
232 AND ET.expenditure_type = P_expenditure_type;
233
234 EXCEPTION
235 WHEN NO_DATA_FOUND THEN
236 l_x_override_to_org_id := NULL;
237
238
239 END;
240
241
242
243 IF l_x_override_to_org_id IS NULL THEN
244
245 BEGIN
246
247 -----------------------------------------
248 -- Organization overrides for person only
249 -----------------------------------------
250
251
252 SELECT OVERRIDE_TO_ORGANIZATION_ID
253 INTO l_x_override_to_org_id
254 FROM pa_cost_dist_overrides CDO,
255 pa_expenditure_types ET
256 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
257 AND CDO.person_id = P_person_id
258 AND CDO.project_id = P_project_id
259 AND CDO.expenditure_category IS NULL;
260
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 l_x_override_to_org_id := NULL;
264
265 END;
266
267 END IF;
268
269
270
271 IF l_x_override_to_org_id IS NULL THEN
272
273 BEGIN
274
275
276 ----------------------------------------------------------------------
277 -- Organization overrides for organization id and expenditure_category
278 ----------------------------------------------------------------------
279
280
281 SELECT OVERRIDE_TO_ORGANIZATION_ID
282 INTO l_x_override_to_org_id
283 FROM pa_cost_dist_overrides CDO,
284 pa_expenditure_types ET
285 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
286 AND CDO.project_id = P_project_id
287 AND CDO.override_from_organization_id = P_incurred_by_organz_id
288 AND CDO.expenditure_category = ET.expenditure_category
289 AND ET.expenditure_type = P_expenditure_type;
290
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 l_x_override_to_org_id := NULL;
294
295 END;
296
297 END IF;
298
299
300
301
302 IF l_x_override_to_org_id IS NULL THEN
303
304 BEGIN
305
306
307
308 -----------------------------------------------
309 -- Organization overrides for organization only
310 -----------------------------------------------
311
312
313 SELECT OVERRIDE_TO_ORGANIZATION_ID
314 INTO l_x_override_to_org_id
315 FROM pa_cost_dist_overrides CDO,
316 pa_expenditure_types ET
317 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
318 AND CDO.project_id = P_project_id
319 AND CDO.override_from_organization_id = P_incurred_by_organz_id
320 AND CDO.expenditure_category is NULL;
321
322 EXCEPTION
323 WHEN NO_DATA_FOUND THEN
324 l_x_override_to_org_id := NULL;
325
326 END;
327
328 END IF;
329
330
331
332 --------------------------------------------------------------
333 -- Raise the exception, If override to organization Id is null
334 --------------------------------------------------------------
335
336 IF (l_x_override_to_org_id IS NULL) THEN
337
338 RAISE l_override_organz_id_null;
339
340 END IF;
341
342
343 ----------------------------------------------------
344 --Assign override to org Id into the output variable
345 ----------------------------------------------------
346
347 x_overr_to_organization_id := l_x_override_to_org_id;
348
349
350 -------------------------------------------------------
351 -- Assign the successful status back to output variable
352 -------------------------------------------------------
353
354 x_return_status := l_x_return_status;
355
356
357 EXCEPTION
358 WHEN l_override_organz_id_null THEN
359 PA_UTILS.add_message('PA', 'PA_OVERRIDE_TO_ORGANZ_ID_NULL');
360 x_return_status := FND_API.G_RET_STS_ERROR;
361 x_msg_count := 1;
362 x_msg_data := 'PA_OVERRIDE_TO_ORGANZ_ID_NULL';
363
364 WHEN OTHERS THEN
365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
366 x_msg_count := 1;
367 x_msg_data := SQLERRM;
368
369 X_overr_to_organization_id := null;
370
371 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
372 p_procedure_name => 'Override_exp_org');
373
374 END Override_exp_organization;
375
376
377
378 PROCEDURE Get_Burden_cost(p_project_type IN VARCHAR2 ,
379 p_project_id IN NUMBER ,
380 p_task_id IN NUMBER ,
381 p_item_date IN DATE ,
382 p_expenditure_type IN VARCHAR2 ,
383 p_schedule_type IN VARCHAR2 ,
384 p_exp_func_curr_code IN OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
385 p_Incurred_by_organz_id IN NUMBER ,
386 p_raw_cost IN NUMBER ,
387 p_raw_cost_rate IN NUMBER ,
391 x_burden_cost_rate OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
388 p_quantity IN NUMBER ,
389 p_override_to_organz_id IN NUMBER ,
390 x_burden_cost OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
392 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
393 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
394 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
395 )
396 IS
397
398 l_burden_cost_flag pa_project_types.burden_cost_flag%TYPE;
399 l_burden_amt_disp_method pa_project_types.burden_amt_display_method%TYPE;
400 l_x_burden_sch_fixed_date DATE;
401 l_x_burden_sch_revision_id NUMBER;
402 l_burden_sch_revision_id NUMBER;
403 l_cost_base VARCHAR2(30);
404 l_x_cost_base VARCHAR2(30);
405 l_expenditure_org_id NUMBER;
406 l_cp_structure VARCHAR2(30);
407 l_x_cp_structure VARCHAR2(30);
408 l_x_compiled_multiplier NUMBER;
409 l_raw_cost_rate NUMBER;
410 l_raw_cost NUMBER;
411 l_x_status NUMBER;
412 l_x_stage NUMBER;
413 l_burden_cost NUMBER;
414 l_burden_cost_rate NUMBER;
415
416
417 l_done_burden_cost_calc EXCEPTION;
418 l_cost_plus_struture_not_found EXCEPTION;
419 l_cost_base_not_found EXCEPTION;
420 l_comp_multiplier_not_found EXCEPTION;
421 l_invalid_schedule_id EXCEPTION;
422
423
424 l_exp_func_curr_code varchar2(15);
425
426 BEGIN
427
428
429 l_exp_func_curr_code := p_exp_func_curr_code;
430
431 --------------------------------------------
432 -- Initialize the successfull return status
433 --------------------------------------------
434
435 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
436
437
438 ---------------------------------------------------------------------------------------
439 -- Assign Input Raw cost into local variables anc check if input raw cost i null or not
440 ---------------------------------------------------------------------------------------
441
442 l_raw_cost_rate := p_raw_cost_rate ;
443 l_raw_cost := p_raw_cost;
444
445
446 IF (l_raw_cost IS NULL) OR (l_raw_cost_rate IS NULL) THEN
447
448 RAISE l_raw_cost_null;
449
450 END IF;
451
452
453 ------------------------------------------------------------------------------
454 -- If schedule type is not equal to REVENUE then only get the burden cost flag
455 -- for calculate the burden cost.
456 ------------------------------------------------------------------------------
457
458 IF p_schedule_type <> 'REVENUE' THEN
459
460
461 ------------------------------------------------------
462 -- Get the burden cost flag for the given project type.
463 ------------------------------------------------------
464
465 SELECT burden_cost_flag
466 INTO l_burden_cost_flag
467 FROM pa_project_types ptypes
468 WHERE project_type = P_project_type;
469
470
471 --------------------------------------------------------------
472 -- Assign Raw Cost into Burden cost, If burden_cost flag = 'N'
473 --------------------------------------------------------------
474
475
476 IF (NVL(l_burden_cost_flag,'N') = 'N') THEN
477
478 X_burden_cost_rate := l_raw_cost_rate;
479 X_burden_cost := l_raw_cost;
480
481 RAISE l_done_burden_cost_calc;
482
483 END IF;
484
485
486 END IF;
487
488 ---------------------------------------------------------------------
489 -- Get burden schdeule Revision Id from the procedure get_schedule_id
490 ---------------------------------------------------------------------
491
492 get_schedule_id(p_schedule_type ,
493 p_project_id ,
494 p_task_id ,
495 p_item_date ,
496 p_expenditure_type ,
497 l_x_burden_sch_revision_id ,
498 l_x_burden_sch_fixed_date ,
499 x_return_status ,
500 x_msg_count ,
501 x_msg_data
502 );
503
504
505 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
506
507 RAISE l_invalid_schedule_id;
508
509 END IF;
510
511
512 --------------------------------------------------------------------
513 -- Get cost plus structure for the given burden schdeule revision id
514 --------------------------------------------------------------------
515
516 l_burden_sch_revision_id := l_x_burden_sch_revision_id;
517
518 pa_cost_plus.get_cost_plus_structure(l_burden_sch_revision_id,
519 l_x_cp_structure ,
520 l_x_status ,
521 l_x_stage
522 );
523
524 IF (l_x_status <> 0) THEN
525
529
526 RAISE l_cost_plus_struture_not_found;
527
528 END IF;
530
531 ------------------------------------------------------------------------
532 -- Get Cost Base for the given Expenditure Type and Cost plus structure
533 ------------------------------------------------------------------------
534
535 l_cp_structure := l_x_cp_structure;
536
537 pa_cost_plus.get_cost_base(P_expenditure_type ,
538 l_cp_structure ,
539 l_x_cost_base ,
540 l_x_status ,
541 l_x_stage
542 );
543
544 IF (l_x_status <> 0) THEN
545
546 RAISE l_cost_base_not_found;
547
548 END IF;
549
550
551 -------------------------------------------------------------------------------
552 -- Get compiled Multiplier for the given Expenditure Org, Cost Base,
553 -- Burden schedule revision id. If override to organization id is not null then
554 -- consider it as expenditure Org. If Override to organization is is null then
555 -- consider Incurred by organization is an expenditure Org.
556 ------------------------------------------------------------------------------
557
558
559 l_expenditure_org_id := NVL(p_override_to_organz_id, P_Incurred_by_organz_id);
560
561
562 ------------------------------
563 -- Get the compiled multiplier
564 ------------------------------
565
566 l_cost_base := l_x_cost_base;
567
568 pa_cost_plus.get_compiled_multiplier(l_expenditure_org_id ,
569 l_cost_base ,
570 l_burden_sch_revision_id ,
571 l_x_compiled_multiplier ,
572 l_x_status ,
573 l_x_stage
574 );
575
576
577 IF (l_x_status <> 0) THEN
578
579 RAISE l_comp_multiplier_not_found;
580
581 END IF;
582
583
584 -------------------------------------------------------
585 -- Get Burden Cost and rate from Raw Cost and Quantity.
586 -------------------------------------------------------
587
588 l_burden_cost := pa_currency.round_trans_currency_amt(
589 l_raw_cost * l_x_compiled_multiplier,p_exp_func_curr_code) +
590 l_raw_cost ;
591
592
593 l_burden_cost_rate := X_burden_cost / NVL(P_quantity, 1) ;
594
595
596 -----------------------------------------------
597 -- Check If output burden cost and rate is null
598 -----------------------------------------------
599
600 IF (l_burden_cost IS NULL) OR (l_burden_cost_rate IS NULL) THEN
601
602 RAISE l_burden_cost_null;
603
604 END IF;
605
606
607 ------------------------------------------
608 -- Assing Burden cost into Output variable
609 ------------------------------------------
610
611 x_burden_cost := l_burden_cost;
612 x_burden_cost_rate := l_burden_cost_rate;
613
614
615
616 -------------------------------------------------------
617 -- Assign the successful status back to output variable
618 -------------------------------------------------------
619
620 x_return_status := l_x_return_status;
621
622
623 EXCEPTION
624 WHEN NO_DATA_FOUND THEN
625 PA_UTILS.add_message('PA', 'PA_NO_BURDEN_COST_FLAG');
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 x_msg_count := 1;
628 x_msg_data := 'PA_NO_BURDEN_COST_FLAG';
629
630 WHEN l_raw_cost_null THEN
631 PA_UTILS.add_message('PA', 'PA_RAW_COST_NULL');
632 x_return_status := FND_API.G_RET_STS_ERROR;
633 x_msg_count := 1;
634 x_msg_data := 'PA_RAW_COST_NULL';
635
636 WHEN l_done_burden_cost_calc THEN
637 x_return_status := FND_API.G_RET_STS_SUCCESS;
638 x_msg_count := NULL;
639 x_msg_data := NULL;
640
641 WHEN l_cost_plus_struture_not_found THEN
642 PA_UTILS.add_message('PA', 'PA_NO_COST_PLUS_STRUCTURE');
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 x_msg_count := 1;
645 x_msg_data := 'PA_NO_COST_PLUS_STRUCTURE';
646
647 WHEN l_cost_base_not_found THEN
648 PA_UTILS.add_message('PA', 'PA_COST_BASE_NOT_FOUND');
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 x_msg_count := 1;
651 x_msg_data := 'PA_COST_BASE_NOT_FOUND';
652
653 WHEN l_comp_multiplier_not_found THEN
654 PA_UTILS.add_message('PA', 'PA_NO_COMPILED_MULTIPLIER');
655 x_return_status := FND_API.G_RET_STS_ERROR;
656 x_msg_count := 1;
657 x_msg_data := 'PA_NO_COMPILED_MULTIPLIER';
658
659 WHEN l_invalid_schedule_id THEN
660 x_return_status := FND_API.G_RET_STS_ERROR;
661 x_msg_count := 1;
662 x_msg_data := 'PA_INVALID_SCH_REV_ID';
663
664 WHEN l_burden_cost_null THEN
668 x_msg_data := 'PA_BURDEN_COST_NULL';
665 PA_UTILS.add_message('PA', 'PA_BURDEN_COST_NULL');
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 x_msg_count := 1;
669
670 WHEN OTHERS THEN
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 x_msg_count := 1;
673 x_msg_data := SQLERRM;
674
675 p_exp_func_curr_code := l_exp_func_curr_code;
676 x_burden_cost := null;
677 x_burden_cost_rate := null;
678
679
680 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
681 p_procedure_name => 'Get_Burden_cost');
682
683
684 END Get_burden_cost;
685
686
687
688
689 PROCEDURE Get_proj_raw_Burden_cost(P_exp_org_id IN NUMBER ,
690 P_proj_org_id IN NUMBER ,
691 P_project_id IN NUMBER ,
692 P_task_id IN NUMBER ,
693 P_item_date IN DATE ,
694 P_exp_func_curr_code IN OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
695 p_proj_func_curr_code IN OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
696 p_raw_cost IN NUMBER ,
697 p_burden_cost IN NUMBER ,
698 x_proj_raw_cost OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
699 x_proj_raw_cost_rate OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
700 x_proj_burden_cost OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
701 x_proj_burden_cost_rate OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
702 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
703 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
704 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
705 )
706 IS
707
708
709 l_proj_org_id NUMBER;
710 l_proj_func_curr_code fnd_currencies.currency_code%TYPE;
711 l_exp_func_curr_code fnd_currencies.currency_code%TYPE;
712 l_proj_rate_date DATE;
713 l_proj_rate_type VARCHAR2(30);
714 l_x_proj_raw_cost NUMBER;
715 l_x_burden_raw_cost NUMBER;
716 l_x_proj_burden_cost NUMBER;
717 l_denominator NUMBER;
718 l_numerator NUMBER;
719 l_exchange_rate NUMBER;
720
721
722 x_status NUMBER;
723
724
725 l_done_proj_cost_calc EXCEPTION;
726 l_invalid_rate_date_type EXCEPTION;
727
728 lx_proj_func_curr_code varchar2(15);
729 lx_exp_func_curr_code varchar2(15);
730
731 BEGIN
732
733 lx_proj_func_curr_code := p_proj_func_curr_code;
734 lx_exp_func_curr_code := p_exp_func_curr_code;
735
736 --------------------------------------------
737 -- Initialize the successfull return status
738 --------------------------------------------
739
740 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742
743
744 ---------------------------------------------
745 -- Check If Input Raw Cost is null
746 ---------------------------------------------
747
748 IF (p_raw_cost IS NULL) THEN
749
750 RAISE l_raw_cost_null;
751
752 END IF;
753
754
755 ---------------------------------------------
756 -- Check If Input Burden cost is null
757 ---------------------------------------------
758
759 IF (p_burden_cost IS NULL) THEN
760
761 RAISE l_burden_cost_null;
762
763 END IF;
764
765
766
767 -------------------------------------------------------------------------------
768 -- If expenditure org and project org are same then project raw and burden cost
769 -- are equal to transaction raw and burden cost
770 -------------------------------------------------------------------------------
771
772 IF (P_exp_org_id = P_proj_org_id) THEN
773
774 x_proj_raw_cost := p_raw_cost;
775 x_proj_burden_cost := p_burden_cost;
776
777 RAISE l_done_proj_cost_calc;
778
779 END IF;
780
781
782
783 -------------------------------------------
784 -- Get Project functional currency code
785 -------------------------------------------
786
787
788 IF (p_proj_func_curr_code IS NULL) THEN
789
790 p_proj_func_curr_code := get_curr_code(p_proj_org_id);
791
792 END IF;
793
794
795 IF (p_proj_func_curr_code IS NULL) THEN
796
797 RAISE l_proj_func_curr_code_null;
798
799 END IF;
800
801
802 -------------------------------------------
803 -- Get Expenditure functional currency code
804 -------------------------------------------
805
806
807 IF (p_exp_func_curr_code IS NULL) THEN
808
809 p_exp_func_curr_code := get_curr_code(p_exp_org_id);
810
811 END IF;
812
813
817
814 IF (p_exp_func_curr_code IS NULL) THEN
815
816 RAISE l_exp_func_curr_code_null;
818 END IF;
819
820
821
822 l_proj_rate_date := NULL;
823 l_proj_rate_type := NULL;
824
825
826
827 IF (p_task_id IS NOT NULL) THEN
828
829
830 BEGIN
831
832
833 -- Get the project_rate_date and project_rate_type
834
835
836 SELECT NVL(tsk.project_rate_date,
837 DECODE(imp.default_rate_date_code,'E',p_item_date,
838 'P',get_pa_date(p_item_date,p_exp_org_id))),
839 NVL(tsk.project_rate_type, imp.default_rate_type)
840 INTO l_proj_rate_date,
841 l_proj_rate_type
842 FROM pa_projects_all prj,
843 pa_tasks tsk,
844 pa_implementations_all imp
845 WHERE prj.project_id = p_project_id
846 AND prj.project_id = tsk.project_id
847 AND tsk.task_id = p_task_id
848 AND prj.org_id = imp.org_id
849 AND imp.org_id = p_proj_org_id;
850
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 l_proj_rate_date := NULL;
854 l_proj_rate_type := NULL;
855
856 END;
857
858 END IF;
859
860
861
862 IF (l_proj_rate_type IS NULL) THEN
863
864 -- Get the Project Rate Date and Rate Type
865
866 BEGIN
867
868
869 SELECT NVL(prj.project_rate_date,
870 DECODE(imp.default_rate_date_code,'E',p_item_date,
871 'P',get_pa_date(p_item_date,p_exp_org_id))),
872 NVL(prj.project_rate_type, imp.default_rate_type)
873 INTO l_proj_rate_date,
874 l_proj_rate_type
875 FROM pa_projects_all prj,
876 pa_implementations_all imp
877 WHERE prj.project_id = p_project_id
878 AND prj.org_id = imp.org_id
879 AND imp.org_id = p_proj_org_id;
880
881
882 EXCEPTION
883 WHEN NO_DATA_FOUND THEN
884 l_proj_rate_date := NULL;
885 l_proj_rate_type := NULL;
886
887 END;
888
889 END IF;
890
891
892 IF (l_proj_rate_type IS NULL) OR (l_proj_rate_date IS NULL) THEN
893
894 RAISE l_invalid_rate_date_type ;
895
896 END IF;
897
898
899
900 -------------------------------
901 -- Get the Project Raw cost
902 -------------------------------
903
904 pa_multi_currency.convert_amount(p_exp_func_curr_code ,
905 p_proj_func_curr_code ,
906 l_proj_rate_date ,
907 l_proj_rate_type ,
908 p_Raw_cost ,
909 'N' ,
910 'N' ,
911 l_x_proj_raw_cost ,
912 l_denominator ,
913 l_numerator ,
914 l_exchange_rate ,
915 x_status
916 );
917
918
919
920
921 IF (l_x_proj_raw_cost IS NULL) THEN
922
923 RAISE l_raw_cost_null;
924
925 END IF;
926
927
928 x_proj_raw_cost := l_x_proj_raw_cost;
929
930
931 ------------------------------
932 -- Get the Project Burden cost
933 ------------------------------
934
935 pa_multi_currency.convert_amount(p_exp_func_curr_code ,
936 p_proj_func_curr_code ,
937 l_proj_rate_date ,
938 l_proj_rate_type ,
939 p_burden_cost ,
940 'N' ,
941 'N' ,
942 l_x_proj_burden_cost ,
943 l_denominator ,
944 l_numerator ,
945 l_exchange_rate ,
946 x_status
947 );
948
949
950 IF (l_x_proj_burden_cost IS NULL) THEN
951
952 RAISE l_burden_cost_null;
953
954 END IF;
955
956
957 x_proj_burden_cost := l_x_proj_burden_cost;
958
959
960 x_return_status := l_x_return_status;
961
962
963 EXCEPTION
964 WHEN l_done_proj_cost_calc THEN
965 x_return_status := FND_API.G_RET_STS_SUCCESS;
966 x_msg_count := NULL;
967 x_msg_data := NULL;
968
969 WHEN l_proj_func_curr_code_null THEN
970 PA_UTILS.add_message('PA', 'PA_PROJ_FUNC_CURR_CODE_NULL');
971 x_return_status := FND_API.G_RET_STS_ERROR;
972 x_msg_count := 1;
973 x_msg_data := 'PA_PROJ_FUNC_CURR_CODE_NULL';
974
978 x_msg_count := 1;
975 WHEN l_exp_func_curr_code_null THEN
976 PA_UTILS.add_message('PA', 'PA_EXP_FUNC_CURR_CODE_NULL');
977 x_return_status := FND_API.G_RET_STS_ERROR;
979 x_msg_data := 'PA_EXP_FUNC_CURR_CODE_NULL';
980
981 WHEN l_invalid_rate_date_type THEN
982 PA_UTILS.add_message('PA', 'PA_INVALID_RATE_DATE_TYPE');
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 x_msg_count := 1;
985 x_msg_data := 'PA_INVALID_RATE_DATE_TYPE';
986
987 WHEN l_raw_cost_null THEN
988 PA_UTILS.add_message('PA', 'PA_RAW_COST_NULL');
989 x_return_status := FND_API.G_RET_STS_ERROR;
990 x_msg_count := 1;
991 x_msg_data := 'PA_PA_PROJ_RAW_COST_NULL';
992
993 WHEN l_burden_cost_null THEN
994 PA_UTILS.add_message('PA', 'PA_BURDEN_COST_NULL');
995 x_return_status := FND_API.G_RET_STS_ERROR;
996 x_msg_count := 1;
997 x_msg_data := 'PA_BURDEN_COST_NULL';
998
999 WHEN OTHERS THEN
1000 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1001 x_msg_count := 1;
1002 x_msg_data := SQLERRM;
1003
1004 /* ATG Changes */
1005 p_proj_func_curr_code := lx_proj_func_curr_code;
1006 p_exp_func_curr_code := lx_exp_func_curr_code;
1007 x_proj_raw_cost := null;
1008 x_proj_raw_cost_rate := null;
1009 x_proj_burden_cost := null;
1010 x_proj_burden_cost_rate := null;
1011
1012 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
1013 p_procedure_name => 'Get_Proj_Raw_Burden_Cost');
1014
1015 END Get_proj_raw_Burden_cost;
1016
1017
1018
1019 FUNCTION Get_pa_date(P_item_date IN DATE,
1020 P_expenditure_org_id IN NUMBER
1021 )
1022 return date
1023 IS
1024
1025 l_pa_date date ;
1026
1027 BEGIN
1028
1029 -- Get the PA Date
1030
1031 SELECT MIN(pap.end_date)
1032 INTO l_pa_date
1033 FROM pa_periods pap
1034 WHERE status in ('O','F')
1035 AND pap.end_date >= P_item_date
1036 AND NVL(pap.org_id, -99) = NVL(p_expenditure_org_id, -99);
1037
1038 return l_pa_date ;
1039
1040 EXCEPTION
1041 WHEN NO_DATA_FOUND THEN
1042 RETURN NULL;
1043 WHEN OTHERS THEN
1044 RAISE;
1045
1046 END Get_pa_date;
1047
1048
1049
1050 FUNCTION Get_curr_code(p_org_id IN NUMBER
1051 )
1052
1053 RETURN VARCHAR2
1054 IS
1055
1056 l_currency_code fnd_currencies.currency_code%TYPE;
1057
1058 BEGIN
1059
1060 SELECT FC.currency_code
1061 INTO l_currency_code
1062 FROM FND_CURRENCIES FC,
1063 GL_SETS_OF_BOOKS GB,
1064 PA_IMPLEMENTATIONS_ALL IMP
1065 WHERE FC.currency_code = DECODE(imp.set_of_books_id, NULL, NULL, GB.currency_code)
1066 AND GB.set_of_books_id = IMP.set_of_books_id
1067 AND IMP.org_id = p_org_id;
1068
1069 return l_currency_code;
1070
1071
1072 EXCEPTION
1073 WHEN NO_DATA_FOUND THEN
1074 NULL;
1075
1076 WHEN OTHERS THEN
1077 Raise;
1078
1079
1080 END Get_curr_code;
1081
1082
1083
1084 PROCEDURE get_schedule_id( p_schedule_type IN VARCHAR2 ,
1085 p_project_id IN NUMBER ,
1086 p_task_id IN NUMBER ,
1087 p_item_date IN DATE ,
1088 p_exp_type IN VARCHAR2 ,
1089 x_burden_sch_rev_id OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1090 x_burden_sch_fixed_date OUT NOCOPY DATE , --File.Sql.39 bug 4440895
1091 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1092 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1093 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1094 )
1095 IS
1096
1097
1098 l_sch_fixed_date DATE;
1099 l_burden_sch_fixed_date DATE;
1100 l_burden_schedule_id NUMBER;
1101 l_x_burden_sch_revision_id NUMBER;
1102 l_burden_sch_id NUMBER;
1103 l_x_status NUMBER;
1104 l_x_stage NUMBER;
1105
1106 l_sch_rev_id_found EXCEPTION;
1107 l_sch_rev_id_not_found EXCEPTION;
1108 l_invalid_revision_by_date EXCEPTION;
1109
1110 BEGIN
1111
1112
1113 --------------------------------------------
1114 -- Initialize the successfull return status
1115 --------------------------------------------
1116
1117 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1118
1119
1120
1121 l_burden_sch_id := NULL;
1122 l_burden_sch_fixed_date := NULL;
1123
1124 --------------------------------------------
1125 -- Task level schedule override
1126 --------------------------------------------
1127
1128
1129 IF p_task_id IS NOT NULL THEN
1130
1131 BEGIN
1132
1133 SELECT irs.ind_rate_sch_id,
1134 DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
1135 'REVENUE', t.rev_ind_sch_fixed_date,
1139 FROM pa_tasks t,
1136 'INVOICE', t.inv_ind_sch_fixed_date)
1137 INTO l_burden_sch_id,
1138 l_burden_sch_fixed_date
1140 pa_ind_rate_schedules irs
1141 WHERE t.task_id = p_task_id
1142 AND t.task_id = irs.task_id
1143 AND ( (p_schedule_type = 'COST'
1144 AND NVL(cost_ovr_sch_flag,'N') = 'Y')
1145 OR (p_schedule_type = 'REVENUE'
1146 AND NVL(rev_ovr_sch_flag,'N') = 'Y')
1147 OR (p_schedule_type = 'INVOICE'
1148 AND NVL(inv_ovr_sch_flag,'N') = 'Y')
1149 );
1150
1151 EXCEPTION
1152 WHEN NO_DATA_FOUND THEN
1153 l_burden_sch_id := NULL;
1154 l_burden_sch_fixed_date := NULL;
1155
1156 END;
1157
1158 END IF;
1159
1160
1161
1162 IF (l_burden_sch_id IS NOT NULL) THEN
1163
1164 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1165 l_burden_sch_fixed_date,
1166 p_item_date,
1167 l_x_burden_sch_revision_id,
1168 l_x_status,
1169 l_x_stage
1170 );
1171 END IF;
1172
1173
1174 IF (l_x_status) <> 0 THEN
1175
1176 RAISE l_invalid_revision_by_date;
1177
1178 END IF;
1179
1180
1181 -------------------------------------------------------
1182 -- Calling client extension to override rate_sch_rev_id
1183 -------------------------------------------------------
1184
1185 -------------------------------------------------------------------
1186 ---------------------- This is a open Issue -----------------------
1187
1188 /* PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
1189 p_expenditure_id,
1190 p_exp_type,
1191 p_task_id,
1192 p_schedule_type,
1193 p_item_date,
1194 l_sch_fixed_date,
1195 l_burden_schedule_id,
1196 status
1197 );
1198
1199 IF (l_burden_schedule_id IS NOT NULL) THEN
1200
1201 l_x_burden_sch_revision_id := l_burden_schedule_id;
1202
1203 IF (l_sch_fixed_date IS NOT NULL) THEN
1204
1205 l_burden_sch_fixed_date := l_sch_fixed_date;
1206
1207 END IF;
1208
1209 END IF;*/
1210 ------------------------------------------------------------------
1211
1212
1213 IF (l_x_burden_sch_revision_id IS NOT NULL) THEN
1214
1215 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1216 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1217
1218 RAISE l_sch_rev_id_found;
1219
1220 END IF;
1221
1222
1223 ----------------------------------------------------------------
1224 -- There is no override rate schedule id found at the task level
1225 -- Find the override rate schedule at project level
1226 ----------------------------------------------------------------
1227
1228
1229 l_burden_sch_id := NULL;
1230 l_burden_sch_fixed_date := NULL;
1231
1232
1233 BEGIN
1234
1235 SELECT irs.ind_rate_sch_id,
1236 DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
1237 'REVENUE', prj.rev_ind_sch_fixed_date,
1238 'INVOICE', prj.inv_ind_sch_fixed_date )
1239 INTO l_burden_sch_id,
1240 l_burden_sch_fixed_date
1241 FROM pa_ind_rate_schedules irs,
1242 pa_projects_all prj
1243 WHERE irs.project_id = prj.project_id
1244 AND irs.project_id = p_project_id
1245 AND irs.task_id is NULL
1246 AND ( (p_schedule_type = 'COST'
1247 AND NVL(cost_ovr_sch_flag,'N') = 'Y')
1248 OR (p_schedule_type = 'REVENUE'
1249 AND NVL(rev_ovr_sch_flag,'N') = 'Y')
1250 OR (p_schedule_type = 'INVOICE'
1251 AND NVL(inv_ovr_sch_flag,'N') = 'Y')
1252 );
1253
1254 EXCEPTION
1255 WHEN NO_DATA_FOUND THEN
1256 l_burden_sch_id := NULL;
1257 l_burden_sch_fixed_date := NULL;
1258
1259 END;
1260
1261
1262 IF (l_burden_sch_id IS NOT NULL) THEN
1263
1264 -- Get the project override schedule id and fixed date
1265
1266 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1267 l_burden_sch_fixed_date,
1268 p_item_date,
1269 l_x_burden_sch_revision_id,
1270 l_x_status,
1271 l_x_stage
1272 );
1273 END IF;
1274
1275
1276 IF (l_x_status) <> 0 THEN
1277
1278 RAISE l_invalid_revision_by_date;
1279
1280 END IF;
1281
1282
1283
1284
1285 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1286
1287 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1288 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1289
1293
1290 RAISE l_sch_rev_id_found;
1291
1292 END IF;
1294
1295 -------------------------------------------------------------------
1296 -- There is no override rate schedule id found at the project level
1297 -- Find the override rate schedule at lowest task level
1298 -------------------------------------------------------------------
1299
1300
1301 l_burden_sch_id := NULL;
1302 l_burden_sch_fixed_date := NULL;
1303
1304
1305 IF p_task_id IS NOT NULL THEN
1306
1307 BEGIN
1308
1309 SELECT t.cost_ind_rate_sch_id,
1310 DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
1311 'REVENUE', t.rev_ind_sch_fixed_date,
1312 'INVOICE', t.inv_ind_sch_fixed_date)
1313 INTO l_burden_sch_id,
1314 l_burden_sch_fixed_date
1315 FROM pa_tasks t
1316 WHERE t.task_id = p_task_id
1317 AND ( p_schedule_type = 'COST'
1318 OR p_schedule_type = 'REVENUE'
1319 OR p_schedule_type = 'INVOICE'
1320 );
1321
1322 EXCEPTION
1323 WHEN NO_DATA_FOUND THEN
1324 l_burden_sch_id := NULL;
1325 l_burden_sch_fixed_date := NULL;
1326
1327 END;
1328
1329 END IF;
1330
1331
1332 IF (l_burden_sch_id IS NOT NULL) THEN
1333
1334 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1335 l_burden_sch_fixed_date,
1336 p_item_date,
1337 l_x_burden_sch_revision_id,
1338 l_x_status,
1339 l_x_stage
1340 );
1341
1342 END IF;
1343
1344 ------------------------------------------------------
1345
1346 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1347
1348 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1349 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1350
1351 RAISE l_sch_rev_id_found;
1352
1353 END IF;
1354
1355
1356 l_burden_sch_id := NULL;
1357 l_burden_sch_fixed_date := NULL;
1358
1359
1360 BEGIN
1361
1362 SELECT prj.cost_ind_rate_sch_id,
1363 DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
1364 'REVENUE', prj.rev_ind_sch_fixed_date,
1365 'INVOICE', prj.inv_ind_sch_fixed_date)
1366 INTO l_burden_sch_id,
1367 l_burden_sch_fixed_date
1368 FROM pa_projects prj
1369 WHERE prj.project_id = p_project_id
1370 AND ( p_schedule_type = 'COST'
1371 OR p_schedule_type = 'REVENUE'
1372 OR p_schedule_type = 'INVOICE'
1373 );
1374
1375 EXCEPTION
1376 WHEN NO_DATA_FOUND THEN
1377 l_burden_sch_id := NULL;
1378 l_burden_sch_fixed_date := NULL;
1379
1380 END;
1381
1382
1383 IF (l_burden_sch_id IS NOT NULL) THEN
1384
1385 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1386 l_burden_sch_fixed_date,
1387 p_item_date,
1388 l_x_burden_sch_revision_id,
1389 l_x_status,
1390 l_x_stage
1391 );
1392 END IF;
1393
1394 ------------------------------------------------------
1395
1396 IF (l_x_status) <> 0 THEN
1397
1398 RAISE l_invalid_revision_by_date;
1399
1400 END IF;
1401
1402
1403
1404 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1405
1406 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1407 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1408
1409 RAISE l_sch_rev_id_found;
1410
1411 ELSE
1412
1413 RAISE l_sch_rev_id_not_found;
1414
1415 END IF;
1416
1417
1418
1419 x_return_status := l_x_return_status;
1420
1421
1422
1423 EXCEPTION
1424 WHEN l_invalid_revision_by_date THEN
1425 PA_UTILS.add_message('PA', 'PA_SCH_REV_NOT_FOUND');
1426 x_return_status := FND_API.G_RET_STS_ERROR;
1427 x_msg_count := 1;
1428 x_msg_data := 'PA_SCH_REV_NOT_FOUND';
1429
1430 WHEN l_sch_rev_id_found THEN
1431 x_return_status := FND_API.G_RET_STS_SUCCESS;
1432 x_msg_count := NULL;
1433 x_msg_data := NULL;
1434
1435 WHEN l_sch_rev_id_not_found THEN
1436 PA_UTILS.add_message('PA', 'PA_SCH_REV_ID_NOT_FOUND');
1437 x_return_status := FND_API.G_RET_STS_ERROR;
1438 x_msg_count := 1;
1439 x_msg_data := 'PA_SCH_REV_ID_NOT_FOUND';
1440
1441 WHEN OTHERS THEN
1442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1443 x_msg_count := 1;
1444 x_msg_data := SQLERRM;
1445
1446
1447 /* ATG Changes */
1448
1449 x_burden_sch_rev_id := null;
1450 x_burden_sch_fixed_date := null;
1451
1455 END get_schedule_id;
1452 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
1453 p_procedure_name => 'Get_Schedule_Id');
1454
1456
1457
1458
1459 PROCEDURE Requirement_raw_cost(
1460 p_forecast_cost_job_group_id IN NUMBER ,
1461 p_forecast_cost_job_id IN NUMBER ,
1462 p_proj_cost_job_group_id IN NUMBER ,
1463 p_proj_cost_job_id IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1464 p_item_date IN DATE ,
1465 p_job_cost_rate_sch_id IN NUMBER ,
1466 p_schedule_date IN DATE ,
1467 p_quantity IN NUMBER ,
1468 p_cost_rate_multiplier IN NUMBER ,
1469 x_raw_cost_rate OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1470 x_raw_cost OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1471 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
1472 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
1473 x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
1474 )
1475 IS
1476
1477 l_x_raw_cost_rate NUMBER;
1478 l_x_raw_cost NUMBER;
1479 l_to_job_id NUMBER;
1480 l_currency_code fnd_currencies.currency_code%TYPE;
1481
1482 l_raw_cost_null EXCEPTION;
1483
1484 l_proj_cost_job_id number;
1485
1486 BEGIN
1487
1488 /* ATG Changes */
1489 l_proj_cost_job_id := p_proj_cost_job_id ;
1490
1491 --------------------------------------------
1492 -- Initialize the successfull return status
1493 --------------------------------------------
1494
1495 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1496
1497
1498 ---------------------------------------
1499 -- Get the Project Cost Job Id from API.
1500 ---------------------------------------
1501
1502
1503 /* IF (p_proj_cost_job_id IS NULL) THEN
1504
1505 Pa_Resource_Utils.GetToJobId( p_forecast_cost_job_group_id ,
1506 p_forecast_cost_job_id ,
1507 p_proj_cost_job_group_id ,
1508 p_proj_cost_job_id
1509 );
1510 END IF;
1511 */
1512
1513
1514 SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_cost_rate_multiplier,1)),
1515 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_cost_rate_multiplier,1)
1516 * p_quantity)
1517 INTO l_x_raw_cost_rate, l_x_raw_cost
1518 FROM pa_bill_rates b
1519 WHERE b.bill_rate_sch_id = p_job_cost_rate_sch_id
1520 AND b.job_id = p_proj_cost_job_id
1521 AND b.rate is NOT NULL
1522 AND to_date(nvl(to_date(p_schedule_date, 'YYYY/MM/DD'), to_date(p_item_date, 'YYYY/MM/DD'))+ 0.99999, 'YYYY/MM/DD')
1523 BETWEEN b.start_date_active
1524 AND NVL(to_date(b.end_date_active, 'YYYY/MM/DD'),
1525 to_date(nvl(to_date(p_schedule_date,'YYYY/MM/DD'), to_date(p_item_date,'YYYY/MM/DD')), 'YYYY/MM/DD')) + 0.99999;
1526
1527
1528
1529
1530 IF (l_x_raw_cost_rate IS NULL) OR (l_x_raw_cost IS NULL) THEN
1531
1532 RAISE l_raw_cost_null;
1533
1534 END IF;
1535
1536
1537 x_raw_cost_rate := l_x_raw_cost_rate;
1538 x_raw_cost := l_x_raw_cost;
1539
1540
1541 x_return_status := l_x_return_status;
1542
1543
1544 EXCEPTION
1545 WHEN NO_DATA_FOUND THEN
1546 PA_UTILS.add_message('PA', 'PA_NO_COST_RATE');
1547 x_return_status := FND_API.G_RET_STS_ERROR;
1548 x_msg_count := 1;
1549 x_msg_data := 'PA_NO_COST_RATE';
1550
1551 WHEN l_raw_cost_null THEN
1552 PA_UTILS.add_message('PA', 'PA_RAW_COST_NULL');
1553 x_return_status := FND_API.G_RET_STS_ERROR;
1554 x_msg_count := 1;
1555 x_msg_data := 'PA_RAW_COST_NULL';
1556
1557 WHEN OTHERS THEN
1558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1559 x_msg_count := 1;
1560 x_msg_data := SQLERRM;
1561
1562 /* ATG Changes */
1563
1564 /* ATG Changes */
1565 p_proj_cost_job_id := l_proj_cost_job_id ;
1566 x_raw_cost_rate := null;
1567 x_raw_cost := null;
1568
1569 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_GET_RAW_BURDEN_COST',
1570 p_procedure_name => 'Requirement_raw_cost');
1571
1572 END Requirement_raw_cost;
1573
1574
1575
1576 END PA_FORECAST_COST;