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