[Home] [Help]
PACKAGE BODY: APPS.PA_COST
Source
1 PACKAGE BODY PA_COST as
2 /* $Header: PAXCSRTB.pls 120.3.12000000.3 2007/07/16 14:25:21 byeturi ship $ */
3
4 l_exp_func_curr_code_null EXCEPTION;
5 l_project_curr_code_null EXCEPTION; /* Added for Org Forecasting */
6 l_multi_conversion_fail EXCEPTION; /* Added for Org Forecasting */
7 l_proj_func_curr_code_null EXCEPTION;
8 l_raw_cost_null EXCEPTION;
9 l_burden_cost_null EXCEPTION;
10 l_x_return_status VARCHAR2(50);
11
12 g_debug_mode varchar2(1);
13
14
15 PROCEDURE PRINT_MSG(p_msg varchar2) IS
16
17 BEGIN
18 --r_debug.r_msg('LOG:'||p_msg);
19 --dbms_output.put_line('LOG:'||p_msg);
20 IF g_debug_mode = 'Y' Then
21 PA_DEBUG.g_err_stage := Substr('LOG:'||p_msg,1,500);
22 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
23
24 END IF;
25
26 END PRINT_MSG;
27
28
29 PROCEDURE Get_Raw_Cost(P_person_id IN NUMBER ,
30 P_expenditure_org_id IN NUMBER ,
31 P_expend_organization_id IN NUMBER , /*LCE changes*/
32 P_labor_Cost_Mult_Name IN VARCHAR2 ,
33 P_Item_date IN DATE ,
34 px_exp_func_curr_code IN OUT NOCOPY VARCHAR2 ,
35 P_Quantity IN NUMBER ,
36 X_Raw_cost_rate OUT NOCOPY NUMBER ,
37 X_Raw_cost OUT NOCOPY NUMBER ,
38 X_return_status OUT NOCOPY VARCHAR2 ,
39 X_msg_count OUT NOCOPY NUMBER ,
40 X_msg_data OUT NOCOPY VARCHAR2
41 )
42 IS
43
44 l_cost_multiplier pa_labor_cost_multipliers.multiplier%TYPE;
45 l_labor_cost_rate pa_compensation_details_all.HOURLY_COST_RATE%TYPE;
46 l_x_raw_cost NUMBER;
47 l_x_raw_cost_rate NUMBER;
48
49 l_no_labor_cost_rate EXCEPTION;
50
51 /*LCE changes*/
52 l_expend_organization_id pa_expenditures_all.incurred_by_organization_id%type;
53 l_exp_org_id pa_expenditures_all.org_id%TYPE; /*2879644*/
54 l_job_id pa_expenditure_items_all.job_id%type;
55 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
56 l_start_date_active date;
57 l_end_date_active date;
58 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
59 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
60 l_override_type pa_compensation_details.override_type%type;
61 l_cost_rate_curr_code pa_compensation_details.cost_rate_currency_code%type;
62 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
63 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
64 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
65 l_acct_cost_rate pa_compensation_details.acct_exchange_rate%type;
66 l_ot_project_id pa_projects_all.project_id%type;
67 l_ot_task_id pa_tasks.task_id%type;
68 l_err_code varchar2(200);
69 l_err_stage number;
70 l_return_value varchar2(100);
71 l_numerator number;
72 l_denominator number;
73 l_conversion_date DATE;
74 user_exception EXCEPTION;
75 /*LCE changes*/
76
77
78 BEGIN
79
80
81 --------------------------------------------
82 -- Initialize the successfull return status
83 --------------------------------------------
84
85 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
86
87 /*****Commented for LCE Changes
88
89 -----------------------------------------------------------
90 -- Get the labor cost rate from pa_compensation_details_all
91 -----------------------------------------------------------
92
93 SELECT hourly_cost_rate
94 INTO l_labor_cost_rate
95 FROM pa_compensation_details_all cd
96 WHERE cd.person_id = p_person_id
97 AND cd.org_id = p_expenditure_org_id --Bug#5903720
98 AND p_item_date BETWEEN cd.start_date_active AND NVL(cd.end_date_active,P_item_date);
99
100 **End of comment for LCE******/
101
102 /***Start of LCE changes***/
103 -------------------------
104 -- Get the labor cost rate
105 --------------------------
106
107 l_expend_organization_id := P_expend_organization_id;
108 l_exp_org_id := P_expenditure_org_id; /*2879644*/
109
110 PA_COST_RATE_PUB.get_labor_rate(p_person_id =>P_person_id
111 ,p_txn_date =>P_Item_date
112 ,p_calling_module =>'STAFFED'
113 ,p_org_id =>l_exp_org_id /*2879644*/
114 ,x_job_id =>l_job_id
115 ,x_organization_id =>l_expend_organization_id
116 ,x_cost_rate =>l_labor_cost_rate
117 ,x_start_date_active =>l_start_date_active
118 ,x_end_date_active =>l_end_date_active
119 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
120 ,x_costing_rule =>l_costing_rule
121 ,x_rate_sch_id =>l_rate_sch_id
122 ,x_cost_rate_curr_code =>l_cost_rate_curr_code
123 ,x_acct_rate_type =>l_acct_rate_type
124 ,x_acct_rate_date_code =>l_acct_rate_date_code
125 ,x_acct_exch_rate =>l_acct_exch_rate
126 ,x_ot_project_id =>l_ot_project_id
127 ,x_ot_task_id =>l_ot_task_id
128 ,x_err_stage =>l_err_stage
129 ,x_err_code =>l_err_code
130 );
131
132 If l_err_code is NOT NULL THEN
133 pa_cc_utils.log_message('Error Occured in stage'||l_err_stage);
134 RAISE user_exception;
135 END IF;
136
137 pa_cc_utils.log_message('Converting from transaction currency to functional currency');
138 -- Get the Functional Currency code
139 ----------------------------------------------------------------------
140 -- If Input expenditure functional currency code is null then call the
141 -- procedure get_curr_code to get the currency code
142 ----------------------------------------------------------------------
143
144
145 IF (px_exp_func_curr_code IS NULL) THEN
146
147 px_exp_func_curr_code := get_curr_code(p_expenditure_org_id);
148
149 END IF;
150
151
152 IF (px_exp_func_curr_code IS NULL) THEN
153
154 RAISE l_exp_func_curr_code_null;
155
156 END IF;
157
158
159 --Check if the denom and functional currencies are different
160
161 IF px_exp_func_curr_code <> l_cost_rate_curr_code THEN
162
163 l_conversion_date := P_Item_date;
164
165 pa_multi_currency.convert_amount( P_from_currency =>l_cost_rate_curr_code,
166 P_to_currency =>px_exp_func_curr_code,
167 P_conversion_date =>l_conversion_date,
168 P_conversion_type =>l_acct_rate_type,
169 P_amount =>l_labor_cost_rate,
170 P_user_validate_flag =>'N',
171 P_handle_exception_flag =>'N',
172 P_converted_amount =>l_acct_cost_rate,
173 P_denominator =>l_denominator,
174 P_numerator =>l_numerator,
175 P_rate =>l_acct_exch_rate,
176 X_status =>l_err_code ) ;
177
178 IF l_err_code is NOT NULL THEN
179 pa_cc_utils.log_message('Error occured in conversion stage');
180 RAISE user_exception;
181 END IF;
182 ELSE
183
184 l_acct_cost_rate := l_labor_cost_rate; /*When denom and functional are same*/
185 END IF;
186 /***End of LCE changes ***/
187
188 -----------------------------------------------------------------------
189 -- Get the cost multiplier from pa_labor_cost_multipliers for the given
190 -- labor cost multiplier name
191 -----------------------------------------------------------------------
192
193
194 IF P_labor_Cost_Mult_Name IS NOT NULL THEN
195
196
197 BEGIN
198
199 SELECT multiplier
200 INTO l_cost_multiplier
201 FROM PA_LABOR_COST_MULTIPLIERS LCM
202 WHERE LCM.LABOR_COST_MULTIPLIER_NAME = P_labor_Cost_Mult_Name
203 AND P_item_date BETWEEN LCM.start_date_active AND NVL(LCM.end_date_active,P_item_date);
204
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 l_cost_multiplier := NULL;
209
210 END;
211
212
213 END IF;
214
215
216 ----------------------------------------
217 -- Calulating Raw cost and Raw cost rate
218 ----------------------------------------
219 /*LCE :Changed l_labor_cost_rate to l_acct_cost_rate */
220
221 l_x_raw_cost_rate := l_acct_cost_rate * NVL(l_cost_multiplier,1);
222
223 l_x_Raw_cost := pa_currency.round_trans_currency_amt(
224 l_x_raw_cost_rate * NVL(p_quantity,0), px_exp_func_curr_code);
225
226
227 -------------------------------------------------
228 -- Checking If Calculated raw cost is null or not
229 -------------------------------------------------
230
231 IF (l_x_raw_cost_rate is NULL) OR (l_x_raw_cost is NULL) THEN
232
233 Raise l_raw_cost_null;
234
235 END IF;
236
237
238
239 ----------------------------------------------------------
240 -- Storing Calculated raw cost values into output variable
241 ----------------------------------------------------------
242
243 x_raw_cost_rate := l_x_raw_cost_rate;
244 x_raw_cost := l_x_raw_cost;
245
246
247 -------------------------------------------------------
248 -- Assign the successful status back to output variable
249 -------------------------------------------------------
250
251 x_return_status := l_x_return_status;
252
253
254 EXCEPTION
255 WHEN l_exp_func_curr_code_null THEN
256 /* Checking error condition. Added for bug 2218386 */
257 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
258 PA_UTILS.add_message('PA', 'PA_FCST_EXP_CURR_CODE_NULL');
259 END IF;
260 x_return_status := FND_API.G_RET_STS_ERROR;
261 x_msg_count := 1;
262 x_msg_data := 'PA_FCST_EXP_CURR_CODE_NULL';
263
264 WHEN NO_DATA_FOUND THEN
265 x_raw_cost_rate := 0;
266 x_raw_cost := 0;
267 /* Checking error condition. Added for bug 2218386 */
268 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
269 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
270 END IF;
271 x_return_status := FND_API.G_RET_STS_ERROR;
272 x_msg_count := 1;
273 x_msg_data := 'PA_FCST_NO_COST_RATE';
274
275 WHEN l_raw_cost_null THEN
276 x_raw_cost_rate := 0;
277 x_raw_cost := 0;
278 /* Checking error condition. Added for bug 2218386 */
279 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
280 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
281 END IF;
282 x_return_status := FND_API.G_RET_STS_ERROR;
283 x_msg_count := 1;
284 x_msg_data := 'PA_FCST_NO_COST_RATE';
285
286 /*LCE changes*/
287 WHEN user_exception THEN
288 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
289 PA_UTILS.add_message('PA',l_err_code);
290 END IF;
291 x_return_status := FND_API.G_RET_STS_ERROR;
292 x_msg_count := 1;
293 x_msg_data := l_err_code;
294 /*End of LCE changes*/
295
296 WHEN OTHERS THEN
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 x_msg_count := 1;
299 x_msg_data := SUBSTR(SQLERRM,1,30);
300 /* Checking error condition. Added for bug 2218386 */
301 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
302 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
303 p_procedure_name => 'Get_Raw_Cost');
304 RAISE;
305 END IF;
306 END Get_Raw_Cost;
307
308
309
310 PROCEDURE Override_exp_organization(P_item_date IN DATE ,
311 P_person_id IN NUMBER ,
312 P_project_id IN NUMBER ,
313 P_incurred_by_organz_id IN NUMBER ,
314 P_Expenditure_type IN VARCHAR2 ,
315 X_overr_to_organization_id OUT NOCOPY NUMBER ,
316 X_return_status OUT NOCOPY VARCHAR2 ,
317 X_msg_count OUT NOCOPY NUMBER ,
318 X_msg_data OUT NOCOPY VARCHAR2
319 )
320 IS
321
322
323 l_x_override_to_org_id NUMBER;
324 -- l_override_organz_id_null EXCEPTION;
325
326
327 BEGIN
328
329
330 --------------------------------------------
331 -- Initialize the successfull return status
332 --------------------------------------------
333
334 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
335
336
337
338 l_x_override_to_org_id := NULL;
339
340
341 BEGIN
342
343 -------------------------------------------------------------
344 -- Organization overrides for person and expenditure_category
345 -------------------------------------------------------------
346
347
348 SELECT OVERRIDE_TO_ORGANIZATION_ID
349 INTO l_x_override_to_org_id
350 FROM pa_cost_dist_overrides CDO,
351 pa_expenditure_types ET
352 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
353 AND CDO.person_id = P_person_id
354 AND CDO.project_id = P_project_id
355 AND CDO.expenditure_category = ET.expenditure_category
356 AND ET.expenditure_type = P_expenditure_type;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 l_x_override_to_org_id := NULL;
361
362
363 END;
364
365
366
367 IF l_x_override_to_org_id IS NULL THEN
368
369 BEGIN
370
371 -----------------------------------------
372 -- Organization overrides for person only
373 -----------------------------------------
374 /*Removed pa_expenditure_types from 'from' clause as it is not used Bug # 2634995 */
375
376 SELECT OVERRIDE_TO_ORGANIZATION_ID
377 INTO l_x_override_to_org_id
378 FROM pa_cost_dist_overrides CDO
379 --,pa_expenditure_types ET
380 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
381 AND CDO.person_id = P_person_id
382 AND CDO.project_id = P_project_id
383 AND CDO.expenditure_category IS NULL;
384
385 EXCEPTION
386 WHEN NO_DATA_FOUND THEN
387 l_x_override_to_org_id := NULL;
388
389 END;
390
391 END IF;
392
393
394
395 IF l_x_override_to_org_id IS NULL THEN
396
397 BEGIN
398
399
400 ----------------------------------------------------------------------
401 -- Organization overrides for organization id and expenditure_category
402 ----------------------------------------------------------------------
403
404
405 SELECT OVERRIDE_TO_ORGANIZATION_ID
406 INTO l_x_override_to_org_id
407 FROM pa_cost_dist_overrides CDO,
408 pa_expenditure_types ET
409 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
410 AND CDO.project_id = P_project_id
411 AND CDO.override_from_organization_id = P_incurred_by_organz_id
412 AND CDO.expenditure_category = ET.expenditure_category
413 AND ET.expenditure_type = P_expenditure_type;
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 l_x_override_to_org_id := NULL;
418
419 END;
420
421 END IF;
422
423
424
425
426 IF l_x_override_to_org_id IS NULL THEN
427
428 BEGIN
429
430
431
432 -----------------------------------------------
433 -- Organization overrides for organization only
434 -----------------------------------------------
435 /*Removed pa_expenditure_types from 'from' clause as it is not used Bug # 2634995 */
436
437 SELECT OVERRIDE_TO_ORGANIZATION_ID
438 INTO l_x_override_to_org_id
439 FROM pa_cost_dist_overrides CDO
440 --,pa_expenditure_types ET
441 WHERE P_item_date between CDO.start_date_active and nvl(CDO.end_date_active, p_item_date)
442 AND CDO.project_id = P_project_id
443 AND CDO.override_from_organization_id = P_incurred_by_organz_id
444 AND CDO.expenditure_category is NULL;
445
446 EXCEPTION
447 WHEN NO_DATA_FOUND THEN
448 l_x_override_to_org_id := NULL;
449
450 END;
451
452 END IF;
453
454
455
456 --------------------------------------------------------------
457 -- Raise the exception, If override to organization Id is null
458 --------------------------------------------------------------
459
460 /* IF (l_x_override_to_org_id IS NULL) THEN
461
462 RAISE l_override_organz_id_null;
463
464 END IF; */
465
466
467 ----------------------------------------------------
468 --Assign override to org Id into the output variable
469 ----------------------------------------------------
470
471 x_overr_to_organization_id := l_x_override_to_org_id;
472
473
474 -------------------------------------------------------
475 -- Assign the successful status back to output variable
476 -------------------------------------------------------
477
478 x_return_status := l_x_return_status;
479
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 x_msg_count := 1;
485 x_msg_data := SUBSTR(SQLERRM,1,30);
486 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
487 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
488 p_procedure_name => 'Override_exp_organization');
489 END IF;
490
491 END Override_exp_organization;
492
493
494
495 PROCEDURE Get_Burdened_cost(p_project_type IN VARCHAR2 ,
496 p_project_id IN NUMBER ,
497 p_task_id IN NUMBER ,
498 p_item_date IN DATE ,
499 p_expenditure_type IN VARCHAR2 ,
500 p_schedule_type IN VARCHAR2 ,
501 px_exp_func_curr_code IN OUT NOCOPY VARCHAR2 ,
502 p_Incurred_by_organz_id IN NUMBER ,
503 p_raw_cost IN NUMBER ,
504 p_raw_cost_rate IN NUMBER ,
505 p_quantity IN NUMBER ,
506 p_override_to_organz_id IN NUMBER ,
507 x_burden_cost OUT NOCOPY NUMBER ,
508 x_burden_cost_rate OUT NOCOPY NUMBER ,
509 x_return_status OUT NOCOPY VARCHAR2 ,
510 x_msg_count OUT NOCOPY NUMBER ,
511 x_msg_data OUT NOCOPY VARCHAR2
512 )
513 IS
514
515 l_burden_cost_flag pa_project_types_all.burden_cost_flag%TYPE;
516 l_burden_amt_disp_method pa_project_types_all.burden_amt_display_method%TYPE;
517 l_x_burden_sch_fixed_date DATE;
518 l_x_burden_sch_revision_id NUMBER;
519 l_burden_sch_revision_id NUMBER;
520 l_cost_base VARCHAR2(30);
521 l_x_cost_base VARCHAR2(30);
522 l_expenditure_org_id NUMBER;
523 l_cp_structure VARCHAR2(30);
524 l_x_cp_structure VARCHAR2(30);
525 l_x_compiled_multiplier NUMBER;
526 l_raw_cost_rate NUMBER;
527 l_raw_cost NUMBER;
528 l_x_status NUMBER;
529 l_x_stage NUMBER;
530 l_burden_cost NUMBER;
531 l_burden_cost_rate NUMBER;
532
533
534 l_done_burden_cost_calc EXCEPTION;
535 l_cost_plus_struture_not_found EXCEPTION;
536 l_cost_base_not_found EXCEPTION;
537 l_comp_multiplier_not_found EXCEPTION;
538 l_invalid_schedule_id EXCEPTION;
539
540
541 BEGIN
542
543
544 --------------------------------------------
545 -- Initialize the successfull return status
546 --------------------------------------------
547
548 print_msg('Inside Get_Burdened_cost API IN params: prjtype['||p_project_type||
549 ']prjId['||p_project_id||']taskid['||p_task_id||']ItemDate['||p_item_date||
550 ']expType['||p_expenditure_type||']schType['||p_schedule_type||
551 ']expFuncurr['||px_exp_func_curr_code||']IncOrgId['||p_Incurred_by_organz_id||
552 ']Rawcost['||p_raw_cost||']CostRate['||p_raw_cost_rate||']Qty['||p_quantity||
553 ']OverrideOrgId['||p_override_to_organz_id||']');
554
555 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
556
557
558 ---------------------------------------------------------------------------------------
559 -- Assign Input Raw cost into local variables anc check if input raw cost i null or not
560 ---------------------------------------------------------------------------------------
561
562 l_raw_cost_rate := p_raw_cost_rate ;
563 l_raw_cost := p_raw_cost;
564
565
566 IF (l_raw_cost IS NULL) OR (l_raw_cost_rate IS NULL) THEN
567
568 RAISE l_raw_cost_null;
569
570 END IF;
571
572
573 ------------------------------------------------------------------------------
574 -- If schedule type is not equal to REVENUE then only get the burden cost flag
575 -- for calculate the burden cost.
576 ------------------------------------------------------------------------------
577
578 IF p_schedule_type <> 'REVENUE' THEN
579
580
581 ------------------------------------------------------
582 -- Get the burden cost flag for the given project type.
583 ------------------------------------------------------
584 SELECT burden_cost_flag
585 INTO l_burden_cost_flag
586 FROM pa_project_types_all typ, pa_projects_all proj
587 WHERE typ.project_type = P_project_type
588 AND proj.project_type = typ.project_type
589 AND proj.project_id = p_project_id
590 AND proj.org_id = typ.org_id; -- bug 5365286
591 -- AND nvl(proj.org_id,-99) = nvl(typ.org_id,-99); -- bug 5365286
592
593
594 --------------------------------------------------------------
595 -- Assign Raw Cost into Burden cost, If burden_cost flag = 'N'
596 --------------------------------------------------------------
597
598
599 IF (NVL(l_burden_cost_flag,'N') = 'N') THEN
600
601 X_burden_cost_rate := l_raw_cost_rate;
602 X_burden_cost := l_raw_cost;
603
604 RAISE l_done_burden_cost_calc;
605
606 END IF;
607
608
609 END IF;
610
611 ---------------------------------------------------------------------
612 -- Get burden schdeule Revision Id from the procedure get_schedule_id
613 ---------------------------------------------------------------------
614 print_msg('calling get_schedule_id API');
615 get_schedule_id(p_schedule_type ,
616 p_project_id ,
617 p_task_id ,
618 p_item_date ,
619 p_expenditure_type ,
620 l_x_burden_sch_revision_id ,
621 l_x_burden_sch_fixed_date ,
622 x_return_status ,
623 x_msg_count ,
624 x_msg_data
625 );
626 print_msg('After get_schedule_id['||l_x_burden_sch_revision_id||']date['||l_x_burden_sch_fixed_date||']');
627
628
629 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
630
631 RAISE l_invalid_schedule_id;
632
633 END IF;
634
635
636 --------------------------------------------------------------------
637 -- Get cost plus structure for the given burden schdeule revision id
638 --------------------------------------------------------------------
639
640 l_burden_sch_revision_id := l_x_burden_sch_revision_id;
641
642 print_msg('Calling get_cost_plus_structure API');
643
644 pa_cost_plus.get_cost_plus_structure(l_burden_sch_revision_id,
645 l_x_cp_structure ,
646 l_x_status ,
647 l_x_stage
648 );
649 print_msg('After get_cost_plus_structure ['||l_x_cp_structure||']l_x_stage['||l_x_stage);
650
651 IF (l_x_status <> 0) THEN
652
653 RAISE l_cost_plus_struture_not_found;
654
655 END IF;
656
657
658 ------------------------------------------------------------------------
659 -- Get Cost Base for the given Expenditure Type and Cost plus structure
660 ------------------------------------------------------------------------
661
662 l_cp_structure := l_x_cp_structure;
663
664 print_msg('Calling get_cost_base API');
665
666 pa_cost_plus.get_cost_base(P_expenditure_type ,
667 l_cp_structure ,
668 l_x_cost_base ,
669 l_x_status ,
670 l_x_stage
671 );
672 print_msg('After get_cost_base ['||l_x_cost_base||']l_x_stage['||l_x_stage||']');
673
674 IF (l_x_status <> 0) THEN
675
676 RAISE l_cost_base_not_found;
677
678 END IF;
679
680
681 -------------------------------------------------------------------------------
682 -- Get compiled Multiplier for the given Expenditure Org, Cost Base,
683 -- Burden schedule revision id. If override to organization id is not null then
684 -- consider it as expenditure Org. If Override to organization is null then
685 -- consider Incurred by organization is an expenditure Org.
686 ------------------------------------------------------------------------------
687
688
689 l_expenditure_org_id := NVL(p_override_to_organz_id, P_Incurred_by_organz_id);
690
691
692 ------------------------------
693 -- Get the compiled multiplier
694 ------------------------------
695
696 l_cost_base := l_x_cost_base;
697
698 print_msg('Calling get_compiled_multiplier API');
699
700 pa_cost_plus.get_compiled_multiplier(l_expenditure_org_id ,
701 l_cost_base ,
702 l_burden_sch_revision_id ,
703 l_x_compiled_multiplier ,
704 l_x_status ,
705 l_x_stage
706 );
707
708 print_msg('After Calling get_compiled_multiplier ['||l_x_compiled_multiplier||']');
709
710
711 IF (l_x_status <> 0) THEN
712
713 RAISE l_comp_multiplier_not_found;
714
715 END IF;
716
717
718 -------------------------------------------------------
719 -- Get Burden Cost and rate from Raw Cost and Quantity.
720 -------------------------------------------------------
721
722 l_burden_cost := pa_currency.round_trans_currency_amt(
723 l_raw_cost * l_x_compiled_multiplier,px_exp_func_curr_code) +
724 l_raw_cost ;
725
726 -- Bug 4434977 -- corrected logic for burden cost rate to avoid rounding error.
727 -- by giving precedence to raw_cost_rate before amout and quantiy based logic.
728 If (nvl(p_raw_cost_rate,0) <> 0) Then
729 l_burden_cost_rate := p_raw_cost_rate * ( 1 + l_x_compiled_multiplier );
730 Else
731 If (nvl(P_quantity,0) <> 0 ) THEN /* Added for Org forecasting */
732 l_burden_cost_rate := l_burden_cost / P_quantity;
733 End If;
734 End If;
735
736 -----------------------------------------------
737 -- Check If output burden cost and rate is null
738 -----------------------------------------------
739
740 IF (l_burden_cost IS NULL) OR (l_burden_cost_rate IS NULL) THEN
741
742 RAISE l_burden_cost_null;
743
744 END IF;
745
746
747 ------------------------------------------
748 -- Assing Burden cost into Output variable
749 ------------------------------------------
750
751 x_burden_cost := l_burden_cost;
752 x_burden_cost_rate := l_burden_cost_rate;
753
754
755
756 -------------------------------------------------------
757 -- Assign the successful status back to output variable
758 -------------------------------------------------------
759
760 x_return_status := l_x_return_status;
761
762
763 EXCEPTION
764 WHEN l_raw_cost_null THEN
765 x_burden_cost := 0;
766 x_burden_cost_rate := 0;
767
768 /* Checking error condition. Added for bug 2218386 */
769 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
770 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
771 END IF;
772
773 x_return_status := FND_API.G_RET_STS_ERROR;
774 x_msg_count := 1;
775 x_msg_data := 'PA_FCST_NO_COST_RATE';
776
777 WHEN l_done_burden_cost_calc THEN
778 x_return_status := FND_API.G_RET_STS_SUCCESS;
779 x_msg_count := NULL;
780 x_msg_data := NULL;
781
782 WHEN l_cost_plus_struture_not_found THEN
783
784 /* Checking error condition. Added for bug 2218386 */
785 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
786 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_PLUS_ST');
787 END IF;
788
789 x_return_status := FND_API.G_RET_STS_ERROR;
790 x_msg_count := 1;
791 x_msg_data := 'PA_FCST_NO_COST_PLUS_ST';
792
793 WHEN l_cost_base_not_found THEN
794
795 /* Checking error condition. Added for bug 2218386 */
796 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
797 PA_UTILS.add_message('PA', 'PA_FCST_COST_BASE_NOT_FOUND');
798 END IF;
799
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 x_msg_count := 1;
802 x_msg_data := 'PA_FCST_COST_BASE_NOT_FOUND';
803
804 WHEN l_comp_multiplier_not_found THEN
805
806 /* Checking error condition. Added for bug 2218386 */
807 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
808 PA_UTILS.add_message('PA', 'PA_FCST_NO_COMPILED_MULTI');
809 END IF;
810
811 x_return_status := FND_API.G_RET_STS_ERROR;
812 x_msg_count := 1;
813 x_msg_data := 'PA_FCST_NO_COMPILED_MULTI';
814
815 WHEN l_invalid_schedule_id THEN
816
817 /* Checking error condition. Added for bug 2218386 */
818 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
819 PA_UTILS.add_message('PA', 'PA_FCST_INVL_BURDEN_SCH_REV_ID');
820 END IF;
821
822 x_return_status := FND_API.G_RET_STS_ERROR;
823 x_msg_count := 1;
824 x_msg_data := 'PA_FCST_INVL_BURDEN_SCH_REV_ID';
825
826 WHEN l_burden_cost_null THEN
827 x_burden_cost := 0;
828 x_burden_cost_rate := 0;
829
830 /* Checking error condition. Added for bug 2218386 */
831 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
832 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
833 END IF;
834
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 x_msg_count := 1;
837 x_msg_data := 'PA_FCST_NO_COST_RATE';
838
839 WHEN OTHERS THEN
840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841 x_msg_count := 1;
842 x_msg_data := SUBSTR(SQLERRM,1,30);
843 print_msg('Others Exception:l_x_stage['||l_x_stage||']'||SQLERRM||SQLCODE);
844 /* Checking error condition. Added for bug 2218386 */
845 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
846 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
847 p_procedure_name => 'Get_Burden_cost');
848 RAISE;
849 END IF;
850
851 END Get_burdened_cost;
852
853
854 /* Changed the name of the procedure from get_proj_raw_burdened_cost to
855 get_projfunc_raw_burdened_cost and changed the params also for MCB II */
856
857 /* Changed the name of this proc from Get_projfunc_raw_Burdened_cost to
858 Get_Converted_Cost_Amounts for Org Forecasting */
859
860 PROCEDURE Get_Converted_Cost_Amounts(
861
862 P_exp_org_id IN NUMBER,
863 P_proj_org_id IN NUMBER,
864 P_project_id IN NUMBER,
865 P_task_id IN NUMBER,
866 P_item_date IN DATE,
867 p_system_linkage IN pa_expenditure_items_all.system_linkage_function%TYPE,/* Added */
868 /* for Org Forecasting */
869 px_txn_curr_code IN OUT NOCOPY VARCHAR2,/* Added for Org Forecasting */
870 px_raw_cost IN OUT NOCOPY NUMBER, /* Txn raw cost,change from IN */
871 /* to IN OUT for Org forecasting */
872 px_raw_cost_rate IN OUT NOCOPY NUMBER, /* Txn raw cost rate,change from IN to */
873 /* IN OUT for Org forecasting */
874 px_burden_cost IN OUT NOCOPY NUMBER, /* Txn burden cost,change from IN to */
875 /* IN OUT for Org forecasting */
876 px_burden_cost_rate IN OUT NOCOPY NUMBER, /* Txn burden cost rate,change from IN to */
877 /* IN OUT for Org forecasting */
878 px_exp_func_curr_code IN OUT NOCOPY VARCHAR2,
879 px_exp_func_rate_date IN OUT NOCOPY DATE, /* Added for Org Forecasting */
880 px_exp_func_rate_type IN OUT NOCOPY VARCHAR2,/* Added for Org Forecasting */
881 px_exp_func_exch_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
882 px_exp_func_cost IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
883 px_exp_func_cost_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
884 px_exp_func_burden_cost IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
885 px_exp_func_burden_cost_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
886 px_proj_func_curr_code IN OUT NOCOPY VARCHAR2,
887 px_projfunc_cost_rate_date IN OUT NOCOPY DATE, /* Added for Org Forecasting */
888 px_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2,/* Added for Org Forecasting */
889 px_projfunc_cost_exch_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
890 px_projfunc_raw_cost IN OUT NOCOPY NUMBER , /* The following 4 para name changed for MCB II */
891 /* change from OUT to IN OUT for Org forecasting */
892 px_projfunc_raw_cost_rate IN OUT NOCOPY NUMBER , /* change from OUT to IN OUT for Org forecasting */
893 px_projfunc_burden_cost IN OUT NOCOPY NUMBER , /* change from OUT to IN OUT for Org forecasting */
894 px_projfunc_burden_cost_rate IN OUT NOCOPY NUMBER , /* change from OUT to IN OUT for Org forecasting */
895 px_project_curr_code IN OUT NOCOPY VARCHAR2,/* Added for Org Forecasting */
896 px_project_rate_date IN OUT NOCOPY DATE, /* Added for Org Forecasting */
897 px_project_rate_type IN OUT NOCOPY VARCHAR2,/* Added for Org Forecasting */
898 px_project_exch_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
899 px_project_cost IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
900 px_project_cost_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
901 px_project_burden_cost IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
902 px_project_burden_cost_rate IN OUT NOCOPY NUMBER, /* Added for Org Forecasting */
903 x_return_status OUT NOCOPY VARCHAR2 ,
904 x_msg_count OUT NOCOPY NUMBER ,
905 x_msg_data OUT NOCOPY VARCHAR2
906 )
907 IS
908
909 l_proj_org_id pa_projects_all.org_id%TYPE;
910 l_exp_org_id pa_project_assignments.expenditure_org_id%TYPE; /* Changed for Org Forecasting */
911 l_txn_raw_cost NUMBER; /* Added for Org Forecasting */
912 l_txn_raw_cost_rate NUMBER; /* Added for Org Forecasting */
913 l_txn_burden_cost NUMBER; /* Added for Org Forecasting */
914 l_txn_burden_cost_rate NUMBER; /* Added for Org Forecasting */
915 l_txn_currency_code fnd_currencies.currency_code%TYPE; /* Added for Org Forecasting */
916 l_project_currency_code fnd_currencies.currency_code%TYPE; /* Added for Org Forecasting */
917 l_proj_func_curr_code fnd_currencies.currency_code%TYPE;
918 l_exp_func_curr_code fnd_currencies.currency_code%TYPE;
919 l_projfunc_rate_date DATE; /* changed the name of these varible for MCB 2 */
920 l_projfunc_rate_type VARCHAR2(30);
921 l_x_projfunc_raw_cost NUMBER;
922 l_x_projfunc_raw_cost_rate NUMBER;
923 l_x_projfunc_burden_cost NUMBER;
924 l_x_projfunc_burden_cost_rate NUMBER; /* Till here for MCB 2 */
925 l_denominator NUMBER;
926 l_numerator NUMBER;
927 l_exchange_rate NUMBER;
928
929
930 x_status NUMBER;
931
932 l_multi_status VARCHAR2(30); /* Added for Org Forecasting */
933 l_stage NUMBER; /* Added for Org Forecasting */
934
935
936 l_done_proj_cost_calc EXCEPTION;
937 l_invalid_rate_date_type EXCEPTION;
938
939 -- l_status VARCHAR2(100); /* Added for bug 2238712 */
940 -- l_conversion_fail EXCEPTION; /* Added for bug 2238712 */
941
942 BEGIN
943
944
945 --------------------------------------------
946 -- Initialize the successfull return status
947 --------------------------------------------
948
949 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
950
951
952
953 ---------------------------------------------
954 -- Check If Input Raw Cost is null
955 ---------------------------------------------
956
957 IF (px_raw_cost IS NULL) THEN /* Changed for Org Forecasting */
958
959 RAISE l_raw_cost_null;
960
961 END IF;
962
963
964 ---------------------------------------------
965 -- Check If Input Burden cost is null
966 ---------------------------------------------
967
968 IF (px_burden_cost IS NULL) THEN /* Changed for Org Forecasting */
969
970 RAISE l_burden_cost_null;
971
972 END IF;
973
974
975 -------------------------------------------------------------------------------
976 -- Assigning the denorm raw cost, rate and burden cost, rate to local variable
977 ------------------------------------------------------------------------------
978 l_txn_raw_cost := px_raw_cost; /* Added for Org Forecasting */
979 l_txn_raw_cost_rate := NVL(px_raw_cost_rate,0); /* Added for Org Forecasting */
980 l_txn_burden_cost := px_burden_cost; /* Added for Org Forecasting */
981 l_txn_burden_cost_rate := NVL(px_burden_cost_rate,0); /* Added for Org Forecasting */
982
983 -------------------------------------------
984 -- Get Project functional currency code
985 -------------------------------------------
986
987
988 IF (px_proj_func_curr_code IS NULL) THEN
989
990 px_proj_func_curr_code := get_curr_code(p_proj_org_id);
991 l_proj_func_curr_code := px_proj_func_curr_code; /* Added for Org Forecasting */
992 ELSE
993 l_proj_func_curr_code := px_proj_func_curr_code; /* Added for Org Forecasting */
994 END IF;
995
996
997 IF (px_proj_func_curr_code IS NULL) THEN
998
999 RAISE l_proj_func_curr_code_null;
1000
1001 END IF;
1002
1003
1004 -------------------------------------------
1005 -- Get Expenditure functional currency code
1006 -------------------------------------------
1007
1008
1009 IF (px_exp_func_curr_code IS NULL) THEN
1010
1011 px_exp_func_curr_code := get_curr_code(p_exp_org_id);
1012 l_exp_func_curr_code := px_exp_func_curr_code; /* Added for Org Forecasting */
1013 l_txn_currency_code := px_exp_func_curr_code; /* Added for Org Forecasting */
1014 ELSE
1015 l_exp_func_curr_code := px_exp_func_curr_code; /* Added for Org Forecasting */
1016 l_txn_currency_code := px_exp_func_curr_code; /* Added for Org Forecasting */
1017 END IF;
1018
1019
1020 IF (px_exp_func_curr_code IS NULL) THEN
1021
1022 RAISE l_exp_func_curr_code_null;
1023
1024 END IF;
1025
1026 -------------------------------------------
1027 -- Get Project currency code
1028 -------------------------------------------
1029
1030 IF (px_project_curr_code IS NOT NULL) THEN
1031
1032 l_project_currency_code := px_project_curr_code; /* Added for Org Forecasting */
1033
1034 END IF;
1035
1036
1037 /* COMMENTED FOR ORG FORECASTING, BECAUSE GOING TO CALL COSTING PROCEDURE
1038 WHICH WILL GIVE THE AMOUNTS IN ALL THE CURRENCIES
1039
1040 -------------------------------------------------------------------------------
1041 -- If expenditure org and project org are same then project raw and burden cost
1042 -- are equal to transaction raw and burden cost
1043 -------------------------------------------------------------------------------
1044 IF (NVL(P_exp_org_id,-99) = NVL(P_proj_org_id,-99)) THEN
1045 x_projfunc_raw_cost := p_raw_cost;
1046 x_projfunc_raw_cost_rate := p_raw_cost_rate;
1047 x_projfunc_burden_cost := p_burden_cost;
1048 x_projfunc_burden_cost_rate := p_burden_cost_rate;
1049 RAISE l_done_proj_cost_calc;
1050 END IF;
1051 l_projfunc_rate_date := NULL;
1052 l_projfunc_rate_type := NULL;
1053 IF (p_task_id IS NOT NULL) THEN
1054 BEGIN
1055 -- Get the project_rate_date and project_rate_type
1056 SELECT NVL(tsk.project_rate_date,
1057 DECODE(imp.default_rate_date_code,'E',p_item_date,
1058 'P',get_pa_date(p_item_date,p_exp_org_id))),
1059 NVL(tsk.project_rate_type, imp.default_rate_type)
1060 INTO l_projfunc_rate_date,
1061 l_projfunc_rate_type
1062 FROM pa_projects_all prj,
1063 pa_tasks tsk,
1064 pa_implementations_all imp
1065 WHERE prj.project_id = p_project_id
1066 AND prj.project_id = tsk.project_id
1067 AND tsk.task_id = p_task_id
1068 AND nvl(prj.org_id ,-99) = nvl(imp.org_id,-99)
1069 AND nvl(imp.org_id,-99) = nvl(p_proj_org_id,-99);
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 l_projfunc_rate_date := NULL;
1073 l_projfunc_rate_type := NULL;
1074 END;
1075 END IF;
1076 IF (l_projfunc_rate_type IS NULL) THEN
1077 -- Get the Project Rate Date and Rate Type
1078 BEGIN
1079 -- Selecting projfunc_cost_rate_type,projfunc_cost_rate_date in place of project_rate_date
1080 -- project_rate_type for MCB 2
1081 SELECT NVL(prj.projfunc_cost_rate_date,
1082 DECODE(imp.default_rate_date_code,'E',p_item_date,
1083 'P',get_pa_date(p_item_date,p_exp_org_id))),
1084 NVL(prj.projfunc_cost_rate_type, imp.default_rate_type)
1085 INTO l_projfunc_rate_date,
1086 l_projfunc_rate_type
1087 FROM pa_projects_all prj,
1088 pa_implementations_all imp
1089 WHERE prj.project_id = p_project_id
1090 AND nvl(prj.org_id,-99) = nvl(imp.org_id,-99)
1091 AND nvl(imp.org_id,-99) = nvl(p_proj_org_id,-99);
1092 EXCEPTION
1093 WHEN NO_DATA_FOUND THEN
1094 l_projfunc_rate_date := NULL;
1095 l_projfunc_rate_type := NULL;
1096 END;
1097 END IF;
1098 IF (l_projfunc_rate_type IS NULL) OR (l_projfunc_rate_date IS NULL) THEN
1099 RAISE l_invalid_rate_date_type ;
1100 END IF;
1101
1102 -------------------------------
1103 -- Get the Project Raw cost
1104 -------------------------------
1105 pa_multi_currency.convert_amount(px_exp_func_curr_code ,
1106 px_proj_func_curr_code ,
1107 l_projfunc_rate_date ,
1108 l_projfunc_rate_type ,
1109 p_Raw_cost ,
1110 'N' ,
1111 -- 'N' , commented for bug 2238712
1112 'Y' ,
1113 l_x_projfunc_raw_cost ,
1114 l_denominator ,
1115 l_numerator ,
1116 l_exchange_rate ,
1117 -- x_status commented for bug 2238712
1118 l_status
1119 );
1120
1121 -- Added for bug 2238712
1122 IF (l_status IS NOT NULL) THEN
1123 RAISE l_conversion_fail;
1124 END IF;
1125 ------------------------------------
1126 -- Get the Project Raw cost rate
1127 ------------------------------------
1128 pa_multi_currency.convert_amount(px_exp_func_curr_code ,
1129 px_proj_func_curr_code ,
1130 l_projfunc_rate_date ,
1131 l_projfunc_rate_type ,
1132 p_Raw_cost_rate ,
1133 'N' ,
1134 -- 'N' , commented for bug 2238712
1135 'Y' ,
1136 l_x_projfunc_raw_cost_rate ,
1137 l_denominator ,
1138 l_numerator ,
1139 l_exchange_rate ,
1140 -- x_status commented for bug 2238712
1141 l_status
1142 );
1143 -- Added for bug 2238712
1144 IF (l_status IS NOT NULL) THEN
1145 RAISE l_conversion_fail;
1146 END IF;
1147 IF (l_x_projfunc_raw_cost IS NULL) THEN
1148 RAISE l_raw_cost_null;
1149 END IF;
1150 x_projfunc_raw_cost := l_x_projfunc_raw_cost;
1151 x_projfunc_raw_cost_rate := l_x_projfunc_raw_cost_rate;
1152 ------------------------------
1153 -- Get the Project Burden cost
1154 ------------------------------
1155 pa_multi_currency.convert_amount(px_exp_func_curr_code ,
1156 px_proj_func_curr_code ,
1157 l_projfunc_rate_date ,
1158 l_projfunc_rate_type ,
1159 p_burden_cost ,
1160 'N' ,
1161 -- 'N' , commented for bug 2238712
1162 'Y' ,
1163 l_x_projfunc_burden_cost ,
1164 l_denominator ,
1165 l_numerator ,
1166 l_exchange_rate ,
1167 -- x_status commented for bug 2238712
1168 l_status
1169 );
1170 -- Added for bug 2238712
1171 IF (l_status IS NOT NULL) THEN
1172 RAISE l_conversion_fail;
1173 END IF;
1174 ------------------------------------
1175 -- Get the Project Burden cost rate
1176 -------------------------------------
1177 pa_multi_currency.convert_amount(px_exp_func_curr_code ,
1178 px_proj_func_curr_code ,
1179 l_projfunc_rate_date ,
1180 l_projfunc_rate_type ,
1181 p_burden_cost_rate ,
1182 'N' ,
1183 -- 'N' , commented for bug 2238712
1184 'Y' ,
1185 l_x_projfunc_burden_cost_rate ,
1186 l_denominator ,
1187 l_numerator ,
1188 l_exchange_rate ,
1189 -- x_status commented for bug 2238712
1190 l_status
1191 );
1192 -- Added for bug 2238712
1193 IF (l_status IS NOT NULL) THEN
1194 RAISE l_conversion_fail;
1195 END IF;
1196 IF (l_x_projfunc_burden_cost IS NULL) THEN
1197 RAISE l_burden_cost_null;
1198 END IF;
1199 x_projfunc_burden_cost := l_x_projfunc_burden_cost;
1200 x_projfunc_burden_cost_rate := l_x_projfunc_burden_cost_rate;
1201
1202 TILL HERE FOR ORG FORECASTING */
1203
1204
1205
1206 IF (P_exp_org_id IS NULL ) THEN
1207 l_exp_org_id := -99;
1208 ELSE /* 2868851 */
1209 l_exp_org_id := P_exp_org_id;
1210 END IF;
1211
1212
1213 ----------------------------------------------------------------------------
1214 -- Get the Raw cost in Project, Project Functional, and Expenditure currency
1215 ---------------------------------------------------------------------------
1216 -- DBMS_OUTPUT.PUT_LINE(' IN COST date '||p_item_date);
1217 PA_MULTI_CURRENCY_TXN.get_currency_amounts(
1218 P_project_id => p_project_id ,
1219 P_exp_org_id => l_exp_org_id ,
1220 P_calling_module => 'FORECAST',
1221 P_task_id => P_task_id,
1222 P_EI_date => p_item_date,
1223 p_system_linkage => p_system_linkage,
1224 P_denom_raw_cost => l_txn_raw_cost,
1225 P_denom_curr_code => l_txn_currency_code,
1226 P_acct_curr_code => l_exp_func_curr_code,
1227 P_acct_rate_date => px_exp_func_rate_date,
1228 P_acct_rate_type => px_exp_func_rate_type,
1229 P_acct_exch_rate => px_exp_func_exch_rate,
1230 P_acct_raw_cost => px_exp_func_cost,
1231 P_project_curr_code => l_project_currency_code,
1232 P_project_rate_date => px_project_rate_date,
1233 P_project_rate_type => px_project_rate_type ,
1234 P_project_exch_rate => px_project_exch_rate,
1235 P_project_raw_cost => px_project_cost,
1236 P_projfunc_curr_code => l_proj_func_curr_code,
1237 P_projfunc_cost_rate_date => px_projfunc_cost_rate_date,
1238 P_projfunc_cost_rate_type => px_projfunc_cost_rate_type ,
1239 P_projfunc_cost_exch_rate => px_projfunc_cost_exch_rate,
1240 P_projfunc_raw_cost => px_projfunc_raw_cost,
1241 P_status => l_multi_status,
1242 P_stage => l_stage) ;
1243
1244 -- DBMS_OUTPUT.PUT_LINE(' IN COST L_STATUS '||l_multi_status||' amount '||px_projfunc_raw_cost);
1245
1246 IF ( l_multi_status IS NOT NULL ) THEN
1247 -- Error in get_currency_amounts
1248 RAISE l_multi_conversion_fail;
1249 END IF;
1250
1251
1252 ---------------------------------------------------------------------------------------
1253 -- Get the Raw cost rate in Project, Project Functional, and Expenditure currency
1254 ---------------------------------------------------------------------------------------
1255
1256 PA_MULTI_CURRENCY_TXN.get_currency_amounts(
1257 P_project_id => p_project_id ,
1258 P_exp_org_id => l_exp_org_id ,
1259 P_calling_module => 'FORECAST',
1260 P_task_id => P_task_id,
1261 P_EI_date => p_item_date,
1262 p_system_linkage => p_system_linkage,
1263 P_denom_raw_cost => l_txn_raw_cost_rate,
1264 P_denom_curr_code => l_txn_currency_code,
1265 P_acct_curr_code => l_exp_func_curr_code,
1266 P_acct_rate_date => px_exp_func_rate_date,
1267 P_acct_rate_type => px_exp_func_rate_type,
1268 P_acct_exch_rate => px_exp_func_exch_rate,
1269 P_acct_raw_cost => px_exp_func_cost_rate,
1270 P_project_curr_code => l_project_currency_code,
1271 P_project_rate_date => px_project_rate_date,
1272 P_project_rate_type => px_project_rate_type ,
1273 P_project_exch_rate => px_project_exch_rate,
1274 P_project_raw_cost => px_project_cost_rate,
1275 P_projfunc_curr_code => l_proj_func_curr_code,
1276 P_projfunc_cost_rate_date => px_projfunc_cost_rate_date,
1277 P_projfunc_cost_rate_type => px_projfunc_cost_rate_type ,
1278 P_projfunc_cost_exch_rate => px_projfunc_cost_exch_rate,
1279 P_projfunc_raw_cost => px_projfunc_raw_cost_rate,
1280 P_status => l_multi_status,
1281 P_stage => l_stage) ;
1282
1283 -- DBMS_OUTPUT.PUT_LINE(' IN COST 1 L_STATUS '||l_multi_status||' rate '||px_projfunc_raw_cost_rate);
1284 IF ( l_multi_status IS NOT NULL ) THEN
1285 -- Error in get_currency_amounts
1286 RAISE l_multi_conversion_fail;
1287 END IF;
1288
1289 IF (px_projfunc_raw_cost IS NULL) THEN
1290
1291 -- dbms_output.put_line(' IN COST API');
1292 RAISE l_raw_cost_null;
1293
1294 END IF;
1295
1296
1297 ----------------------------------------------------------------------------
1298 -- Get the Burden cost in Project, Project Functional, and Expenditure currency
1299 ---------------------------------------------------------------------------
1300 PA_MULTI_CURRENCY_TXN.get_currency_amounts(
1301 P_project_id => p_project_id ,
1302 P_exp_org_id => l_exp_org_id ,
1303 P_calling_module => 'FORECAST',
1304 P_task_id => P_task_id,
1305 P_EI_date => p_item_date,
1306 p_system_linkage => p_system_linkage,
1307 P_denom_raw_cost => l_txn_burden_cost,
1308 P_denom_curr_code => l_txn_currency_code,
1309 P_acct_curr_code => l_exp_func_curr_code,
1310 P_acct_rate_date => px_exp_func_rate_date,
1311 P_acct_rate_type => px_exp_func_rate_type,
1312 P_acct_exch_rate => px_exp_func_exch_rate,
1313 P_acct_raw_cost => px_exp_func_burden_cost,
1314 P_project_curr_code => l_project_currency_code,
1315 P_project_rate_date => px_project_rate_date,
1316 P_project_rate_type => px_project_rate_type ,
1317 P_project_exch_rate => px_project_exch_rate,
1318 P_project_raw_cost => px_project_burden_cost,
1319 P_projfunc_curr_code => l_proj_func_curr_code,
1320 P_projfunc_cost_rate_date => px_projfunc_cost_rate_date,
1321 P_projfunc_cost_rate_type => px_projfunc_cost_rate_type ,
1322 P_projfunc_cost_exch_rate => px_projfunc_cost_exch_rate,
1323 P_projfunc_raw_cost => px_projfunc_burden_cost,
1324 P_status => l_multi_status,
1325 P_stage => l_stage) ;
1326
1327 -- DBMS_OUTPUT.PUT_LINE(' IN COST 2 L_STATUS '||l_multi_status);
1328 -- DBMS_OUTPUT.PUT_LINE(' IN COST px_project_burden_cost '||l_multi_status||' amount '||px_project_burden_cost);
1329 IF ( l_multi_status IS NOT NULL ) THEN
1330 -- Error in get_currency_amounts
1331 RAISE l_multi_conversion_fail;
1332 END IF;
1333
1334
1335 ---------------------------------------------------------------------------------------
1336 -- Get the Burden cost rate in Project, Project Functional, and Expenditure currency
1337 ---------------------------------------------------------------------------------------
1338
1339 PA_MULTI_CURRENCY_TXN.get_currency_amounts(
1340 P_project_id => p_project_id ,
1341 P_exp_org_id => l_exp_org_id ,
1342 P_calling_module => 'FORECAST',
1343 P_task_id => P_task_id,
1344 P_EI_date => p_item_date,
1345 p_system_linkage => p_system_linkage,
1346 P_denom_raw_cost => l_txn_burden_cost_rate,
1347 P_denom_curr_code => l_txn_currency_code,
1348 P_acct_curr_code => l_exp_func_curr_code,
1349 P_acct_rate_date => px_exp_func_rate_date,
1350 P_acct_rate_type => px_exp_func_rate_type,
1351 P_acct_exch_rate => px_exp_func_exch_rate,
1352 P_acct_raw_cost => px_exp_func_burden_cost_rate,
1353 P_project_curr_code => l_project_currency_code,
1354 P_project_rate_date => px_project_rate_date,
1355 P_project_rate_type => px_project_rate_type ,
1356 P_project_exch_rate => px_project_exch_rate,
1357 P_project_raw_cost => px_project_burden_cost_rate,
1358 P_projfunc_curr_code => l_proj_func_curr_code,
1359 P_projfunc_cost_rate_date => px_projfunc_cost_rate_date,
1360 P_projfunc_cost_rate_type => px_projfunc_cost_rate_type ,
1361 P_projfunc_cost_exch_rate => px_projfunc_cost_exch_rate,
1362 P_projfunc_raw_cost => px_projfunc_burden_cost_rate,
1363 P_status => l_multi_status,
1364 P_stage => l_stage) ;
1365
1366 -- DBMS_OUTPUT.PUT_LINE(' IN COST 3 L_STATUS '||l_multi_status);
1367 IF ( l_multi_status IS NOT NULL ) THEN
1368 -- Error in get_currency_amounts
1369 RAISE l_multi_conversion_fail;
1370 END IF;
1371
1372 IF (px_projfunc_burden_cost IS NULL) THEN
1373
1374 -- dbms_output.put_line(' IN COST API');
1375 RAISE l_burden_cost_null;
1376
1377 END IF;
1378
1379 -------------------------------------------------------------------------------
1380 -- Assigning back the local varible to in out parameters
1381 ------------------------------------------------------------------------------
1382 px_raw_cost := l_txn_raw_cost; /* Added for Org Forecasting */
1383 px_raw_cost_rate := l_txn_raw_cost_rate; /* Added for Org Forecasting */
1384 px_burden_cost := l_txn_burden_cost; /* Added for Org Forecasting */
1385 px_burden_cost_rate := l_txn_burden_cost_rate; /* Added for Org Forecasting */
1386 px_proj_func_curr_code := l_proj_func_curr_code; /* Added for Org Forecasting */
1387 px_exp_func_curr_code := l_exp_func_curr_code; /* Added for Org Forecasting */
1388 px_txn_curr_code := l_txn_currency_code; /* Added for Org Forecasting */
1389 px_project_curr_code := l_project_currency_code; /* Added for Org Forecasting */
1390
1391
1392 x_return_status := l_x_return_status;
1393
1394
1395 EXCEPTION
1396 /* WHEN l_conversion_fail THEN Added for bug 2238712
1397 x_return_status := FND_API.G_RET_STS_ERROR;
1398 x_msg_count := 1;
1399 x_msg_data := SUBSTR(l_status,1,30);
1400 x_projfunc_burden_cost := 0;
1401 x_projfunc_burden_cost_rate := 0;
1402 x_projfunc_raw_cost := 0;
1403 x_projfunc_raw_cost_rate := 0;
1404
1405 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1406 PA_UTILS.add_message('PA', SUBSTR(l_status,1,30));
1407 END IF; */
1408 WHEN l_proj_func_curr_code_null THEN
1409 /* Checking error condition. Added for bug 2218386 */
1410 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1411 PA_UTILS.add_message('PA', 'PA_FCST_PROJ_CURR_CODE_NULL');
1412 END IF;
1413
1414 x_return_status := FND_API.G_RET_STS_ERROR;
1415 x_msg_count := 1;
1416 x_msg_data := 'PA_FCST_PROJ_CURR_CODE_NULL';
1417
1418 WHEN l_exp_func_curr_code_null THEN
1419 /* Checking error condition. Added for bug 2218386 */
1420 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1421 PA_UTILS.add_message('PA', 'PA_FCST_EXP_CURR_CODE_NULL');
1422 END IF;
1423
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 x_msg_count := 1;
1426 x_msg_data := 'PA_FCST_EXP_CURR_CODE_NULL';
1427
1428 WHEN l_invalid_rate_date_type THEN
1429 /* Checking error condition. Added for bug 2218386 */
1430 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1431 PA_UTILS.add_message('PA', 'PA_FCST_INVL_RATE_DT_TYP');
1432 END IF;
1433
1434 x_return_status := FND_API.G_RET_STS_ERROR;
1435 x_msg_count := 1;
1436 x_msg_data := 'PA_FCST_INVL_RATE_DT_TYP';
1437
1438 WHEN l_raw_cost_null THEN
1439 px_raw_cost := 0;
1440 px_raw_cost_rate := 0;
1441 px_burden_cost := 0;
1442 px_burden_cost_rate := 0;
1443 px_exp_func_cost := 0;
1444 px_exp_func_cost_rate := 0;
1445 px_exp_func_burden_cost := 0;
1446 px_exp_func_burden_cost_rate := 0;
1447 px_projfunc_raw_cost := 0;
1448 px_projfunc_raw_cost_rate := 0;
1449 px_projfunc_burden_cost := 0;
1450 px_projfunc_burden_cost_rate := 0;
1451 px_project_cost := 0;
1452 px_project_cost_rate := 0;
1453 px_project_burden_cost := 0;
1454 px_project_burden_cost_rate := 0;
1455
1456 /* Checking error condition. Added for bug 2218386 */
1457 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1458 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
1459 END IF;
1460
1461 x_return_status := FND_API.G_RET_STS_ERROR;
1462 x_msg_count := 1;
1463 x_msg_data := 'PA_FCST_NO_COST_RATE';
1464
1465 WHEN l_burden_cost_null THEN
1466 px_raw_cost := 0;
1467 px_raw_cost_rate := 0;
1468 px_burden_cost := 0;
1469 px_burden_cost_rate := 0;
1470 px_exp_func_cost := 0;
1471 px_exp_func_cost_rate := 0;
1472 px_exp_func_burden_cost := 0;
1473 px_exp_func_burden_cost_rate := 0;
1474 px_projfunc_raw_cost := 0;
1475 px_projfunc_raw_cost_rate := 0;
1476 px_projfunc_burden_cost := 0;
1477 px_projfunc_burden_cost_rate := 0;
1478 px_project_cost := 0;
1479 px_project_cost_rate := 0;
1480 px_project_burden_cost := 0;
1481 px_project_burden_cost_rate := 0;
1482
1483 /* Checking error condition. Added for bug 2218386 */
1484 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1485 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
1486 END IF;
1487
1488 x_return_status := FND_API.G_RET_STS_ERROR;
1489 x_msg_count := 1;
1490 x_msg_data := 'PA_FCST_NO_COST_RATE';
1491
1492 WHEN l_multi_conversion_fail THEN
1493 px_raw_cost := 0;
1494 px_raw_cost_rate := 0;
1495 px_burden_cost := 0;
1496 px_burden_cost_rate := 0;
1497 px_exp_func_cost := 0;
1498 px_exp_func_cost_rate := 0;
1499 px_exp_func_burden_cost := 0;
1500 px_exp_func_burden_cost_rate := 0;
1501 px_projfunc_raw_cost := 0;
1502 px_projfunc_raw_cost_rate := 0;
1503 px_projfunc_burden_cost := 0;
1504 px_projfunc_burden_cost_rate := 0;
1505 px_project_cost := 0;
1506 px_project_cost_rate := 0;
1507 px_project_burden_cost := 0;
1508 px_project_burden_cost_rate := 0;
1509
1510 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1511 PA_UTILS.add_message('PA', l_multi_status);
1512 END IF;
1513
1514 x_return_status := FND_API.G_RET_STS_ERROR;
1515 x_msg_count := 1;
1516 x_msg_data := l_multi_status;
1517
1518 WHEN OTHERS THEN
1519 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1520 x_msg_count := 1;
1521 x_msg_data := SUBSTR(SQLERRM,1,30);
1522 /* Checking error condition. Added for bug 2218386 */
1523 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1524 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
1525 p_procedure_name => 'Get_Converted_Cost_Amounts');
1526 RAISE;
1527 END IF;
1528 END Get_Converted_Cost_Amounts;
1529
1530
1531
1532 FUNCTION Get_pa_date(P_item_date IN DATE,
1533 P_expenditure_org_id IN NUMBER
1534 )
1535 return date
1536 IS
1537
1538 l_pa_date date ;
1539
1540 BEGIN
1541
1542 -- Get the PA Date
1543
1544 SELECT MIN(pap.end_date)
1545 INTO l_pa_date
1546 FROM pa_periods pap
1547 WHERE status in ('O','F')
1548 AND pap.end_date >= P_item_date
1549 AND NVL(pap.org_id, -99) = NVL(p_expenditure_org_id, -99);
1550
1551 return l_pa_date ;
1552
1553 EXCEPTION
1554 WHEN NO_DATA_FOUND THEN
1555 RETURN NULL;
1556 WHEN OTHERS THEN
1557 /* Checking error condition. Added for bug 2218386 */
1558 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1559 RAISE;
1560 ELSE
1561 NULL;
1562 END IF;
1563
1564 END Get_pa_date;
1565
1566
1567
1568 FUNCTION Get_curr_code(p_org_id IN NUMBER
1569 )
1570
1571 RETURN VARCHAR2
1572 IS
1573
1574 l_currency_code fnd_currencies.currency_code%TYPE;
1575
1576 BEGIN
1577
1578 SELECT FC.currency_code
1579 INTO l_currency_code
1580 FROM FND_CURRENCIES FC,
1581 GL_SETS_OF_BOOKS GB,
1582 PA_IMPLEMENTATIONS_ALL IMP
1583 WHERE FC.currency_code = DECODE(imp.set_of_books_id, NULL, NULL, GB.currency_code)
1584 AND GB.set_of_books_id = IMP.set_of_books_id
1585 AND IMP.org_id = p_org_id; --Bug#5903720
1586
1587 return l_currency_code;
1588
1589
1590 EXCEPTION
1591 WHEN NO_DATA_FOUND THEN
1592 NULL;
1593
1594 WHEN OTHERS THEN
1595 /* Checking error condition. Added for bug 2218386 */
1596 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1597 Raise;
1598 ELSE
1599 NULL;
1600 END IF;
1601
1602
1603 END Get_curr_code;
1604
1605
1606
1607 PROCEDURE get_schedule_id( p_schedule_type IN VARCHAR2 ,
1608 p_project_id IN NUMBER ,
1609 p_task_id IN NUMBER ,
1610 p_item_date IN DATE ,
1611 p_exp_type IN VARCHAR2 ,
1612 x_burden_sch_rev_id OUT NOCOPY NUMBER ,
1613 x_burden_sch_fixed_date OUT NOCOPY DATE ,
1614 x_return_status OUT NOCOPY VARCHAR2 ,
1615 x_msg_count OUT NOCOPY NUMBER ,
1616 x_msg_data OUT NOCOPY VARCHAR2
1617 )
1618 IS
1619
1620
1621 l_sch_fixed_date DATE;
1622 l_burden_sch_fixed_date DATE;
1623 l_burden_schedule_id NUMBER;
1624 l_x_burden_sch_revision_id NUMBER;
1625 l_burden_sch_id NUMBER;
1626 l_x_status NUMBER;
1627 l_x_stage NUMBER;
1628
1629 l_sch_rev_id_found EXCEPTION;
1630 l_sch_rev_id_not_found EXCEPTION;
1631 l_invalid_revision_by_date EXCEPTION;
1632
1633 BEGIN
1634
1635
1636 --------------------------------------------
1637 -- Initialize the successfull return status
1638 --------------------------------------------
1639
1640 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1641
1642
1643
1644 l_burden_sch_id := NULL;
1645 l_burden_sch_fixed_date := NULL;
1646
1647 --------------------------------------------
1648 -- Task level schedule override
1649 --------------------------------------------
1650
1651
1652 IF p_task_id IS NOT NULL THEN
1653
1654 BEGIN
1655
1656 SELECT irs.ind_rate_sch_id,
1657 DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
1658 'REVENUE', t.rev_ind_sch_fixed_date,
1659 'INVOICE', t.inv_ind_sch_fixed_date)
1660 INTO l_burden_sch_id,
1661 l_burden_sch_fixed_date
1662 FROM pa_tasks t,
1663 pa_ind_rate_schedules irs
1664 WHERE t.task_id = p_task_id
1665 AND t.task_id = irs.task_id
1666 AND ( (p_schedule_type = 'COST'
1667 AND NVL(cost_ovr_sch_flag,'N') = 'Y')
1668 OR (p_schedule_type = 'REVENUE'
1669 AND NVL(rev_ovr_sch_flag,'N') = 'Y')
1670 OR (p_schedule_type = 'INVOICE'
1671 AND NVL(inv_ovr_sch_flag,'N') = 'Y')
1672 );
1673
1674 EXCEPTION
1675 WHEN NO_DATA_FOUND THEN
1676 l_burden_sch_id := NULL;
1677 l_burden_sch_fixed_date := NULL;
1678
1679 END;
1680
1681 END IF;
1682
1683
1684
1685 IF (l_burden_sch_id IS NOT NULL) THEN
1686
1687 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1688 l_burden_sch_fixed_date,
1689 p_item_date,
1690 l_x_burden_sch_revision_id,
1691 l_x_status,
1692 l_x_stage
1693 );
1694 END IF;
1695
1696
1697 IF (l_x_status) <> 0 THEN
1698
1699 RAISE l_invalid_revision_by_date;
1700
1701 END IF;
1702
1703
1704 -------------------------------------------------------
1705 -- Calling client extension to override rate_sch_rev_id
1706 -------------------------------------------------------
1707
1708 -------------------------------------------------------------------
1709 ---------------------- This is a open Issue -----------------------
1710
1711 /* PA_CLIENT_EXTN_BURDEN.Override_Rate_Rev_Id(
1712 p_expenditure_id,
1713 p_exp_type,
1714 p_task_id,
1715 p_schedule_type,
1716 p_item_date,
1717 l_sch_fixed_date,
1718 l_burden_schedule_id,
1719 status
1720 );
1721
1722 IF (l_burden_schedule_id IS NOT NULL) THEN
1723
1724 l_x_burden_sch_revision_id := l_burden_schedule_id;
1725
1726 IF (l_sch_fixed_date IS NOT NULL) THEN
1727
1728 l_burden_sch_fixed_date := l_sch_fixed_date;
1729
1730 END IF;
1731
1732 END IF;*/
1733 ------------------------------------------------------------------
1734
1735
1736 IF (l_x_burden_sch_revision_id IS NOT NULL) THEN
1737
1738 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1739 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1740
1741 RAISE l_sch_rev_id_found;
1742
1743 END IF;
1744
1745
1746 ----------------------------------------------------------------
1747 -- There is no override rate schedule id found at the task level
1748 -- Find the override rate schedule at project level
1749 ----------------------------------------------------------------
1750
1751
1752 l_burden_sch_id := NULL;
1753 l_burden_sch_fixed_date := NULL;
1754
1755 BEGIN
1756
1757 SELECT irs.ind_rate_sch_id,
1758 DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
1759 'REVENUE', prj.rev_ind_sch_fixed_date,
1760 'INVOICE', prj.inv_ind_sch_fixed_date )
1761 INTO l_burden_sch_id,
1762 l_burden_sch_fixed_date
1763 FROM pa_ind_rate_schedules irs,
1764 pa_projects_all prj
1765 WHERE irs.project_id = prj.project_id
1766 AND irs.project_id = p_project_id
1767 AND irs.task_id is NULL
1768 AND ( (p_schedule_type = 'COST'
1769 AND NVL(cost_ovr_sch_flag,'N') = 'Y')
1770 OR (p_schedule_type = 'REVENUE'
1771 AND NVL(rev_ovr_sch_flag,'N') = 'Y')
1772 OR (p_schedule_type = 'INVOICE'
1773 AND NVL(inv_ovr_sch_flag,'N') = 'Y')
1774 );
1775
1776 EXCEPTION
1777 WHEN NO_DATA_FOUND THEN
1778 l_burden_sch_id := NULL;
1779 l_burden_sch_fixed_date := NULL;
1780
1781 END;
1782
1783
1784 IF (l_burden_sch_id IS NOT NULL) THEN
1785
1786 -- Get the project override schedule id and fixed date
1787
1788 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1789 l_burden_sch_fixed_date,
1790 p_item_date,
1791 l_x_burden_sch_revision_id,
1792 l_x_status,
1793 l_x_stage
1794 );
1795 END IF;
1796
1797
1798 IF (l_x_status) <> 0 THEN
1799
1800 RAISE l_invalid_revision_by_date;
1801
1802 END IF;
1803
1804
1805
1806
1807 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1808
1809 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1810 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1811
1812 RAISE l_sch_rev_id_found;
1813
1814 END IF;
1815
1816
1817 -------------------------------------------------------------------
1818 -- There is no override rate schedule id found at the project level
1819 -- Find the override rate schedule at lowest task level
1820 -------------------------------------------------------------------
1821
1822
1823 l_burden_sch_id := NULL;
1824 l_burden_sch_fixed_date := NULL;
1825
1826
1827 /* Use the decode statement to select the ind_rate_sch_id according
1828 to the passed schedule_type to fix the bug 2046094 */
1829 IF p_task_id IS NOT NULL THEN
1830
1831 BEGIN
1832
1833 SELECT DECODE(p_schedule_type,'COST', t.cost_ind_rate_sch_id,
1834 'REVENUE', t.rev_ind_rate_sch_id,
1835 'INVOICE', t.inv_ind_rate_sch_id),
1836 DECODE(p_schedule_type,'COST', t.cost_ind_sch_fixed_date,
1837 'REVENUE', t.rev_ind_sch_fixed_date,
1838 'INVOICE', t.inv_ind_sch_fixed_date)
1839 INTO l_burden_sch_id,
1840 l_burden_sch_fixed_date
1841 FROM pa_tasks t
1842 WHERE t.task_id = p_task_id
1843 AND ( p_schedule_type = 'COST'
1844 OR p_schedule_type = 'REVENUE'
1845 OR p_schedule_type = 'INVOICE'
1846 );
1847
1848 EXCEPTION
1849 WHEN NO_DATA_FOUND THEN
1850 l_burden_sch_id := NULL;
1851 l_burden_sch_fixed_date := NULL;
1852
1853 END;
1854
1855 END IF;
1856
1857
1858 IF (l_burden_sch_id IS NOT NULL) THEN
1859
1860 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1861 l_burden_sch_fixed_date,
1862 p_item_date,
1863 l_x_burden_sch_revision_id,
1864 l_x_status,
1865 l_x_stage
1866 );
1867
1868 END IF;
1869
1870 ------------------------------------------------------
1871
1872 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1873
1874 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1875 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1876
1877 RAISE l_sch_rev_id_found;
1878
1879 END IF;
1880
1881
1882 l_burden_sch_id := NULL;
1883 l_burden_sch_fixed_date := NULL;
1884
1885 /* Use the decode statement to select the ind_rate_sch_id according
1886 to the passed schedule_type to fix the bug 2046094 */
1887
1888 BEGIN
1889
1890 SELECT DECODE(p_schedule_type,'COST', prj.cost_ind_rate_sch_id,
1891 'REVENUE', prj.rev_ind_rate_sch_id,
1892 'INVOICE', prj.inv_ind_rate_sch_id),
1893 DECODE(p_schedule_type,'COST', prj.cost_ind_sch_fixed_date,
1894 'REVENUE', prj.rev_ind_sch_fixed_date,
1895 'INVOICE', prj.inv_ind_sch_fixed_date)
1896 INTO l_burden_sch_id,
1897 l_burden_sch_fixed_date
1898 FROM pa_projects_all prj
1899 WHERE prj.project_id = p_project_id
1900 AND ( p_schedule_type = 'COST'
1901 OR p_schedule_type = 'REVENUE'
1902 OR p_schedule_type = 'INVOICE'
1903 );
1904
1905 EXCEPTION
1906 WHEN NO_DATA_FOUND THEN
1907 l_burden_sch_id := NULL;
1908 l_burden_sch_fixed_date := NULL;
1909
1910 END;
1911
1912
1913 IF (l_burden_sch_id IS NOT NULL) THEN
1914
1915 pa_cost_plus.get_revision_by_date(l_burden_sch_id,
1916 l_burden_sch_fixed_date,
1917 p_item_date,
1918 l_x_burden_sch_revision_id,
1919 l_x_status,
1920 l_x_stage
1921 );
1922 END IF;
1923
1924 ------------------------------------------------------
1925
1926 IF (l_x_status) <> 0 THEN
1927
1928 RAISE l_invalid_revision_by_date;
1929
1930 END IF;
1931
1932
1933
1934 IF (l_x_burden_sch_revision_id) is NOT NULL THEN
1935
1936 x_burden_sch_rev_id := l_x_burden_sch_revision_id;
1937 x_burden_sch_fixed_date := l_burden_sch_fixed_date;
1938
1939 RAISE l_sch_rev_id_found;
1940
1941 ELSE
1942
1943 RAISE l_sch_rev_id_not_found;
1944
1945 END IF;
1946
1947
1948
1949 x_return_status := l_x_return_status;
1950
1951
1952
1953 EXCEPTION
1954 WHEN l_invalid_revision_by_date THEN
1955 /* Checking error condition. Added for bug 2218386 */
1956 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1957 PA_UTILS.add_message('PA', 'PA_FCST_INVL_BURDEN_SCH_REV_ID');
1958 END IF;
1959 x_return_status := FND_API.G_RET_STS_ERROR;
1960 x_msg_count := 1;
1961 x_msg_data := 'PA_FCST_INVL_BURDEN_SCH_REV_ID';
1962
1963 WHEN l_sch_rev_id_found THEN
1964 x_return_status := FND_API.G_RET_STS_SUCCESS;
1965 x_msg_count := NULL;
1966 x_msg_data := NULL;
1967
1968 WHEN l_sch_rev_id_not_found THEN
1969 /* Checking error condition. Added for bug 2218386 */
1970 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1971 PA_UTILS.add_message('PA', 'PA_FCST_INVL_BURDEN_SCH_REV_ID');
1972 END IF;
1973 x_return_status := FND_API.G_RET_STS_ERROR;
1974 x_msg_count := 1;
1975 x_msg_data := 'PA_FCST_INVL_BURDEN_SCH_REV_ID';
1976
1977 WHEN OTHERS THEN
1978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1979 x_msg_count := 1;
1980 x_msg_data := SUBSTR(SQLERRM,1,30);
1981 /* Checking error condition. Added for bug 2218386 */
1982 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
1983 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
1984 p_procedure_name => 'Get_Schedule_Id');
1985 END IF;
1986
1987 END get_schedule_id;
1988
1989 /* Added four new columns and changed the logic to return the cost and rate in
1990 PFC for MCB II */
1991
1992 PROCEDURE Requirement_raw_cost(
1993 p_forecast_cost_job_group_id IN NUMBER ,
1994 p_forecast_cost_job_id IN NUMBER ,
1995 p_proj_cost_job_group_id IN NUMBER ,
1996 px_proj_cost_job_id IN OUT NOCOPY NUMBER ,
1997 p_item_date IN DATE ,
1998 p_job_cost_rate_sch_id IN NUMBER ,
1999 p_schedule_date IN DATE ,
2000 p_quantity IN NUMBER ,
2001 p_cost_rate_multiplier IN NUMBER ,
2002 p_org_id IN NUMBER ,
2003 P_expend_organization_id IN NUMBER , /*LCE*/
2004 /* p_projfunc_currency_code IN VARCHAR2, -- The following 4
2005 px_projfunc_cost_rate_type IN OUT NOCOPY VARCHAR2, -- added for MCB2
2006 px_projfunc_cost_rate_date IN OUT NOCOPY DATE,
2007 px_projfunc_cost_exchange_rate IN OUT NOCOPY NUMBER ,
2008 Commented for Org Forecasting */
2009 x_raw_cost_rate OUT NOCOPY NUMBER ,
2010 x_raw_cost OUT NOCOPY NUMBER ,
2011 x_txn_currency_code OUT NOCOPY VARCHAR2 , /* Added for Org Forecasting */
2012 x_return_status OUT NOCOPY VARCHAR2 ,
2013 x_msg_count OUT NOCOPY NUMBER ,
2014 x_msg_data OUT NOCOPY VARCHAR2
2015 )
2016 IS
2017
2018 l_x_raw_cost_rate NUMBER;
2019 l_x_raw_cost NUMBER;
2020 l_to_job_id NUMBER;
2021 l_currency_code fnd_currencies.currency_code%TYPE;
2022
2023 l_raw_cost_null EXCEPTION;
2024 l_conversion_fail EXCEPTION;
2025
2026 /* Added for MCB2 */
2027 l_txn_cost_rate NUMBER :=null; -- It will be used to store cost amount transaction curr.
2028 l_txn_cost NUMBER :=null; -- It will be used to store the raw revenue trans. curr.
2029 l_rate_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
2030 l_denominator Number;
2031 l_numerator Number;
2032 l_status Varchar2(30);
2033 l_converted_cost_amount Number;
2034 l_converted_cost_rate NUMBER :=null;
2035 l_conversion_date DATE; -- variable to store item date
2036
2037 /*LCE changes*/
2038 l_expend_organization_id pa_expenditures_all.incurred_by_organization_id%type;
2039 l_exp_org_id pa_expenditures_all.org_id%type; /*2879644*/
2040 l_acct_cost_rate pa_compensation_details.acct_exchange_rate%type;
2041 l_acct_currency_code fnd_currencies.currency_code%TYPE;
2042 l_costing_rule pa_compensation_details_all.compensation_rule_set%type;
2043 l_start_date_active date;
2044 l_end_date_active date;
2045 l_org_labor_sch_rule_id pa_org_labor_sch_rule.org_labor_sch_rule_id%type;
2046 l_rate_sch_id pa_std_bill_rate_schedules.bill_rate_sch_id%type;
2047 l_override_type pa_compensation_details.override_type%type;
2048 l_acct_rate_type pa_compensation_details.acct_rate_type%type;
2049 l_acct_rate_date_code pa_compensation_details.acct_rate_date_code%type;
2050 l_acct_exch_rate pa_compensation_details.acct_exchange_rate%type;
2051 l_ot_project_id pa_projects_all.project_id%type;
2052 l_ot_task_id pa_tasks.task_id%type;
2053 l_err_code varchar2(200);
2054 l_err_stage number;
2055 l_return_value varchar2(100);
2056 user_exception EXCEPTION;
2057 /*LCE changes*/
2058
2059
2060 BEGIN
2061
2062 g_debug_mode := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
2063 --Initialize the error stack
2064 PA_DEBUG.init_err_stack('PA_COST.Requirement_raw_cost');
2065
2066 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2067 ,x_write_file => 'LOG'
2068 ,x_debug_mode => g_debug_mode
2069 );
2070
2071 print_msg('IN Params :CostJobgoup_id['||p_forecast_cost_job_group_id||
2072 ']jobId['|| p_forecast_cost_job_id||']projJobGroupId['||p_proj_cost_job_group_id||
2073 ']projCostJobId['||px_proj_cost_job_id||']ItemDate['||p_item_date||
2074 ']rateSchId['||p_job_cost_rate_sch_id||']SchDate['||p_schedule_date||
2075 ']Qty['||p_quantity||']rateMultplier['||p_cost_rate_multiplier||
2076 ']OrgId['||p_org_id||']ExpOrgId['||P_expend_organization_id||']');
2077
2078
2079
2080 --------------------------------------------
2081 -- Initialize the successfull return status
2082 --------------------------------------------
2083
2084 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2085
2086
2087 ---------------------------------------
2088 -- Get the Project Cost Job Id from API.
2089 ---------------------------------------
2090
2091
2092 IF (px_proj_cost_job_id IS NULL) THEN
2093
2094 print_msg('Calling Pa_Resource_Utils.GetToJobId');
2095
2096 Pa_Resource_Utils.GetToJobId( p_forecast_cost_job_group_id ,
2097 p_forecast_cost_job_id ,
2098 p_proj_cost_job_group_id ,
2099 px_proj_cost_job_id
2100 );
2101
2102 print_msg('After Pa_Resource_Utils.GetToJobId API costJobid['||px_proj_cost_job_id||']');
2103 END IF;
2104
2105 /****Commented for LCE Changes
2106
2107 SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_cost_rate_multiplier,1)),
2108 (b.rate * NVL(p_cost_rate_multiplier,1) * p_quantity),rate_currency_code
2109 INTO l_x_raw_cost_rate, l_x_raw_cost ,l_rate_currency_code
2110 FROM pa_bill_rates_all b
2111 WHERE b.bill_rate_sch_id = p_job_cost_rate_sch_id
2112 AND b.job_id = px_proj_cost_job_id
2113 AND b.rate is NOT NULL
2114 AND to_date(nvl(to_date(p_schedule_date), to_date(p_item_date))+ 0.99999)
2115 BETWEEN b.start_date_active AND NVL(to_date(b.end_date_active),
2116 to_date(nvl(to_date(p_schedule_date), to_date(p_item_date)))) + 0.99999
2117 AND NVL(b.org_id,-99) = NVL(p_org_id,-99);
2118 End of comment for LCE ******/
2119
2120 /***LCE changes***/
2121
2122 l_expend_organization_id := P_expend_organization_id;
2123
2124 print_msg('Calling PA_COST_RATE_PUB.get_labor_rate API');
2125
2126 PA_COST_RATE_PUB.get_labor_rate(p_person_id =>NULL
2127 ,p_txn_date =>P_Item_date
2128 ,p_calling_module =>'REQUIREMENT'
2129 ,p_org_id =>l_expend_organization_id /*5511578*/
2130 ,x_job_id =>px_proj_cost_job_id
2131 ,x_organization_id =>l_expend_organization_id
2132 ,x_cost_rate =>l_x_raw_cost_rate
2133 ,x_start_date_active =>l_start_date_active
2134 ,x_end_date_active =>l_end_date_active
2135 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
2136 ,x_costing_rule =>l_costing_rule
2137 ,x_rate_sch_id =>l_rate_sch_id
2138 ,x_cost_rate_curr_code =>l_rate_currency_code
2139 ,x_acct_rate_type =>l_acct_rate_type
2140 ,x_acct_rate_date_code =>l_acct_rate_date_code
2141 ,x_acct_exch_rate =>l_acct_exch_rate
2142 ,x_ot_project_id =>l_ot_project_id
2143 ,x_ot_task_id =>l_ot_task_id
2144 ,x_err_stage =>l_err_stage
2145 ,x_err_code =>l_err_code
2146 );
2147 print_msg('l_x_raw_cost_rate['||l_x_raw_cost_rate||']l_org_labor_sch_rule_id['||l_org_labor_sch_rule_id||
2148 ']l_costing_rule['||l_costing_rule||']l_rate_sch_id['||l_rate_sch_id||
2149 ']l_rate_currency_code['||l_rate_currency_code||']l_acct_rate_type['||l_acct_rate_type||
2150 ']l_acct_rate_date_code['||l_acct_rate_date_code||']l_acct_exch_rate['||l_acct_exch_rate||
2151 ']l_ot_project_id['||l_ot_project_id||']l_ot_task_id['||l_ot_task_id||
2152 ']l_err_stage['||l_err_stage||']l_err_code['||l_err_code||']');
2153
2154
2155 IF l_err_code is NOT NULL THEN
2156 pa_cc_utils.log_message('Error Occured in stage'||l_err_stage);
2157 RAISE user_exception;
2158 END IF;
2159
2160 pa_cc_utils.log_message('Converting from transaction currency to functional currency');
2161 -- Get the Functional Currency code
2162
2163 l_acct_currency_code := get_curr_code(p_org_id);
2164
2165
2166 IF (l_acct_currency_code IS NULL) THEN
2167
2168 RAISE l_exp_func_curr_code_null;
2169
2170 END IF;
2171
2172
2173 --Check if the denom and functional currencies are different
2174
2175 IF l_acct_currency_code <> l_rate_currency_code THEN
2176
2177 l_conversion_date := P_Item_date;
2178
2179 print_msg('Calling pa_multi_currency.convert_amount API');
2180
2181 pa_multi_currency.convert_amount( P_from_currency =>l_rate_currency_code,
2182 P_to_currency =>l_acct_currency_code,
2183 P_conversion_date =>l_conversion_date,
2184 P_conversion_type =>l_acct_rate_type,
2185 P_amount =>l_x_raw_cost_rate,
2186 P_user_validate_flag =>'N',
2187 P_handle_exception_flag =>'N',
2188 P_converted_amount =>l_acct_cost_rate,
2189 P_denominator =>l_denominator,
2190 P_numerator =>l_numerator,
2191 P_rate =>l_acct_exch_rate,
2192 X_status =>l_err_code ) ;
2193 print_msg('l_x_raw_cost_rate['||l_x_raw_cost_rate||']l_acct_cost_rate['||l_acct_cost_rate||
2194 ']l_denominator['||l_denominator||']l_numerator['||l_numerator||
2195 ']l_acct_exch_rate['||l_acct_exch_rate||']l_err_code['||l_err_code||
2196 ']l_x_raw_cost_rate['||l_x_raw_cost_rate||']');
2197
2198 IF l_err_code is NOT NULL THEN
2199 pa_cc_utils.log_message('Error occured in conversion stage');
2200 RAISE user_exception;
2201 END IF;
2202
2203 ELSE
2204
2205 l_acct_cost_rate := l_x_raw_cost_rate; /*When denom and functional are same*/
2206
2207 END IF ;
2208
2209 l_x_raw_cost := l_acct_cost_rate * NVL(p_cost_rate_multiplier,1) * p_quantity;
2210
2211 /***End of LCE changes ***/
2212
2213 l_txn_cost_rate := NVL(l_acct_cost_rate,0);
2214 l_txn_cost := NVL(l_x_raw_cost,0);
2215
2216
2217 IF (l_txn_cost_rate IS NULL) OR (l_txn_cost IS NULL) THEN
2218
2219 RAISE l_raw_cost_null;
2220
2221 END IF;
2222
2223 x_raw_cost_rate := NVL(l_txn_cost_rate,0); /* Added for Org Forecasting */
2224 x_raw_cost := NVL(l_txn_cost,0); /* Added for Org Forecasting */
2225 x_txn_currency_code := l_acct_currency_code; /* Added for Org Forecasting */
2226
2227 /* COMMENTED FOR ORG FORECASTING, BECAUSE GOING TO CALL COSTING PROCEDURE
2228 WHICH WILL GIVE THE AMOUNTS IN ALL THE CURRENCIES
2229
2230 -- The following code has been added for MCB2
2231 l_conversion_date := p_item_date;
2232
2233 -- Calling convert amount proc to convert revenue amount in PFC
2234 PA_MULTI_CURRENCY.convert_amount(
2235 P_FROM_CURRENCY => l_rate_currency_code,
2236 P_TO_CURRENCY => p_projfunc_currency_code,
2237 P_CONVERSION_DATE => l_conversion_date,
2238 P_CONVERSION_TYPE => px_projfunc_cost_rate_type,
2239 P_AMOUNT => l_txn_cost,
2240 P_USER_VALIDATE_FLAG => 'Y',
2241 P_HANDLE_EXCEPTION_FLAG => 'Y',
2242 P_CONVERTED_AMOUNT => l_converted_cost_amount,
2243 P_DENOMINATOR => l_denominator,
2244 P_NUMERATOR => l_numerator,
2245 P_RATE => px_projfunc_cost_exchange_rate,
2246 X_STATUS => l_status);
2247
2248 IF (l_status IS NOT NULL) THEN
2249 RAISE l_conversion_fail;
2250 END IF;
2251
2252 -- Calling convert amount proc to convert rate in PFC
2253 PA_MULTI_CURRENCY.convert_amount(
2254 P_FROM_CURRENCY => l_rate_currency_code,
2255 P_TO_CURRENCY => p_projfunc_currency_code,
2256 P_CONVERSION_DATE => l_conversion_date,
2257 P_CONVERSION_TYPE => px_projfunc_cost_rate_type,
2258 P_AMOUNT => l_txn_cost_rate,
2259 P_USER_VALIDATE_FLAG => 'Y',
2260 P_HANDLE_EXCEPTION_FLAG => 'Y',
2261 P_CONVERTED_AMOUNT => l_converted_cost_rate,
2262 P_DENOMINATOR => l_denominator,
2263 P_NUMERATOR => l_numerator,
2264 P_RATE => px_projfunc_cost_exchange_rate,
2265 X_STATUS => l_status);
2266
2267 IF (l_status IS NOT NULL) THEN
2268 RAISE l_conversion_fail;
2269 END IF;
2270
2271
2272
2273 TILL HERE FRO ORG FORECASTING
2274 */
2275
2276 x_return_status := l_x_return_status;
2277 PA_DEBUG.reset_err_stack;
2278
2279 EXCEPTION
2280 WHEN l_conversion_fail THEN
2281 x_raw_cost_rate := 0;
2282 x_raw_cost := 0;
2283
2284 /* Checking error condition. Added for bug 2218386 */
2285 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2286 PA_UTILS.add_message('PA', l_status||'_BC_PF'); /* fix for bug 2199203 */
2287 END IF;
2288
2289 x_return_status := FND_API.G_RET_STS_ERROR;
2290 x_msg_count := 1;
2291 x_msg_data := l_status||'_BC_PF'; /* fix for bug 2199203 */
2292 PA_DEBUG.reset_err_stack;
2293 WHEN NO_DATA_FOUND THEN
2294 x_raw_cost_rate := 0;
2295 x_raw_cost := 0;
2296
2297 /* Checking error condition. Added for bug 2218386 */
2298 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2299 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
2300 END IF;
2301
2302 x_return_status := FND_API.G_RET_STS_ERROR;
2303 x_msg_count := 1;
2304 x_msg_data := 'PA_FCST_NO_COST_RATE';
2305 PA_DEBUG.reset_err_stack;
2306 WHEN l_raw_cost_null THEN
2307 x_raw_cost_rate := 0;
2308 x_raw_cost := 0;
2309
2310 /* Checking error condition. Added for bug 2218386 */
2311 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2312 PA_UTILS.add_message('PA', 'PA_FCST_NO_COST_RATE');
2313 END IF;
2314
2315 x_return_status := FND_API.G_RET_STS_ERROR;
2316 x_msg_count := 1;
2317 x_msg_data := 'PA_FCST_NO_COST_RATE';
2318 PA_DEBUG.reset_err_stack;
2319
2320 /*LCE changes*/
2321 WHEN user_exception THEN
2322 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2323 PA_UTILS.add_message('PA',l_err_code);
2324 END IF;
2325 x_return_status := FND_API.G_RET_STS_ERROR;
2326 x_msg_count := 1;
2327 x_msg_data := l_err_code;
2328 PA_DEBUG.reset_err_stack;
2329
2330 WHEN l_exp_func_curr_code_null THEN
2331 /* Checking error condition. Added for bug 2218386 */
2332 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2333 PA_UTILS.add_message('PA', 'PA_FCST_EXP_CURR_CODE_NULL');
2334 END IF;
2335 x_return_status := FND_API.G_RET_STS_ERROR;
2336 x_msg_count := 1;
2337 x_msg_data := 'PA_FCST_EXP_CURR_CODE_NULL';
2338 PA_DEBUG.reset_err_stack;
2339 /*LCE changes*/
2340
2341 WHEN OTHERS THEN
2342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2343 x_msg_count := 1;
2344 x_msg_data := SUBSTR(SQLERRM,1,30);
2345 /* Checking error condition. Added for bug 2218386 */
2346 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2347 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_COST', /* Moved this here to fix bug 2434663 */
2348 p_procedure_name => 'Requirement_raw_cost');
2349 RAISE;
2350 END IF;
2351 END Requirement_raw_cost;
2352
2353
2354 END PA_COST;