1 package body PA_COST1 as
2 -- $Header: PAXCSR1B.pls 120.1.12000000.2 2007/07/16 14:23:38 byeturi ship $
3
4 g_debug_flag Varchar2(10) ;
5
6 /*
7 procedure calc_log(p_msg varchar2) IS
8
9 pragma autonomous_transaction ;
10 BEGIN
11 --dbms_output.put_line(p_msg);
12 --IF P_PA_DEBUG_MODE = 'Y' Then
13 NULL;
14 INSERT INTO PA_FP_CALCULATE_LOG
15 (SESSIONID
16 ,SEQ_NUMBER
17 ,LOG_MESSAGE)
18 VALUES
19 (userenv('sessionid')
20 ,HR.PAY_US_GARN_FEE_RULES_S.nextval
21 ,substr(P_MSG,1,240)
22 );
23 --END IF;
24 COMMIT;
25
26 end calc_log;
27 */
28 PROCEDURE print_msg(p_debug_flag varchar2
29 ,p_msg varchar2 Default NULL) IS
30
31 l_msg varchar2(1000);
32 l_module varchar2(100) := 'PA_COST1';
33 BEGIN
34 --calc_log(p_msg);
35 If p_debug_flag = 'Y' Then
36 l_msg := substr(p_msg,1,1000);
37 PA_DEBUG.write
38 (x_Module => l_module
39 ,x_Msg => substr('LOG:'||p_msg,1,240)
40 ,x_Log_Level => 3);
41 End If;
42
43 END print_msg ;
44
45 /* This API checks whether the given task is a financial task
46 * if the given task is not exists in pa_tasks the derive the
47 * burden sch details from project level
48 */
49 FUNCTION is_workPlan_Task(p_project_id IN Number
50 ,p_task_id IN Number )
51 RETURN VARCHAR2 IS
52 l_exists_flag varchar2(10);
53 BEGIN
54 l_exists_flag := 'N';
55 If p_task_id is NOT NULL Then
56 SELECT 'N'
57 INTO l_exists_flag
58 FROM dual
59 WHERE EXISTS (select null
60 from pa_tasks t
61 where t.task_id = p_task_id
62 and t.project_id = p_project_id);
63 End If;
64
65 return l_exists_flag;
66
67 EXCEPTION
68 WHEN NO_DATA_FOUND THEN
69 l_exists_flag := 'Y';
70 RETURN l_exists_flag;
71 WHEN OTHERS THEN
72 l_exists_flag := 'N';
73 RETURN l_exists_flag;
74
75 END is_workPlan_Task;
76
77 /* This API checks whether the expenditure type is cost rate enabled or not */
78 FUNCTION check_expCostRateFlag
79 (p_exp_type varchar2) Return Varchar2 IS
80
81 Cursor cur_costrateFlag IS
82 SELECT nvl(exp.cost_rate_flag,'N')
83 FROM pa_expenditure_types exp
84 WHERE exp.expenditure_type = p_exp_type;
85
86 l_expCostRateFlag Varchar2(10);
87
88 BEGIN
89
90 OPEN cur_costrateFlag;
91 FETCH cur_costrateFlag INTO l_expCostRateFlag;
92 CLOSE cur_costrateFlag;
93
94 RETURN l_expCostRateFlag;
95
96 END check_expCostRateFlag;
97
98 /* This API derives the cost rate multiplier for the given tasks */
99 FUNCTION get_CostRateMultiplier
100 (p_task_id Number
101 ,p_exp_item_date Date
102 ) Return Number IS
103
104 l_cost_rate_multiplier Number := NULL;
105 l_stage Varchar2(1000);
106
107
108 BEGIN
109 l_stage := 'Getting labor cost multiplier ';
110 SELECT lcm.multiplier
111 INTO l_cost_rate_multiplier
112 FROM pa_tasks t
113 ,pa_labor_cost_multipliers lcm
114 WHERE t.task_id = p_task_id
115 AND t.labor_cost_multiplier_name = lcm.labor_cost_multiplier_name
116 AND trunc(P_exp_item_date) BETWEEN LCM.start_date_active AND
117 NVL(LCM.end_date_active,P_exp_item_date);
118
119 Return l_cost_rate_multiplier;
120 EXCEPTION
121 WHEN others THEN
122 l_cost_rate_multiplier := NULL;
123 Return l_cost_rate_multiplier;
124
125 END get_CostRateMultiplier;
126
127 /* This API derives the project level burden schedule details
128 * when task id is NULL. If p_burden_sch_is is passed then
129 * it dervies the sch_revision_id
130 * If the sch and revision found then x_status will be set to 0 (zero)
131 */
132 PROCEDURE get_projLevel_BurdSchds
133 (p_project_id IN Number
134 ,p_task_id IN Number Default NULL
135 ,p_exp_item_date IN DATE
136 ,p_burden_sch_id IN Number Default NULL
137 ,x_burden_sch_id OUT NOCOPY Number
138 ,x_burden_sch_revision_id OUT NOCOPY Number
139 ,x_status OUT NOCOPY Number ) IS
140
141 l_stage varchar2(1000);
142 l_debug_flag VARCHAR2(10);
143
144 BEGIN
145 --- Initialize the error statck
146 IF ( g_debug_flag IS NULL )
147 Then
148 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
149 g_debug_flag := NVL(g_debug_flag, 'N');
150 End If;
151 l_debug_flag := NVL(g_debug_flag,'N');
152 IF ( l_debug_flag = 'Y' )
153 THEN
154 PA_DEBUG.init_err_stack ('PA_COST1.get_projLevel_BurdSchds');
155 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
156 ,x_write_file => 'LOG'
157 ,x_debug_mode => l_debug_flag
158 );
159 End If;
160
161 /* Initialize the out params */
162 x_status := 0;
163 x_burden_sch_id := p_burden_sch_id;
164 x_burden_sch_revision_id := Null;
165
166 l_stage := 'Begin get_projLevel_BurdSchds IN Params: ProjId['||p_project_id||']ExpItemDate['||p_exp_item_date||
167 ']TaskId['||p_task_id||']BurdenSchId['||p_burden_sch_id||']' ;
168 IF ( l_debug_flag = 'Y' )
169 THEN
170 Print_msg(l_debug_flag,l_stage);
171 END IF;
172
173 IF (p_task_id is NOT NULL and x_burden_sch_id is NULL ) Then
174 BEGIN
175 -- Select the Task level schedule override if not found
176 -- then select the Project level override
177 SELECT irs.ind_rate_sch_id
178 INTO x_burden_sch_id
179 FROM pa_tasks t,
180 pa_ind_rate_schedules irs
181 WHERE t.task_id = p_task_id
182 AND t.project_id = p_project_id
183 AND t.task_id = irs.task_id
184 AND irs.cost_ovr_sch_flag = 'Y';
185
186 EXCEPTION
187 WHEN NO_DATA_FOUND THEN
188 -- check the project level override
189 BEGIN
190 SELECT irs.ind_rate_sch_id
191 INTO x_burden_sch_id
192 FROM pa_ind_rate_schedules irs
193 ,pa_projects_all pp
194 WHERE pp.project_id = p_project_id
195 AND irs.project_id = pp.project_id
196 AND irs.cost_ovr_sch_flag = 'Y' ;
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 -- check the task level schedule (not the override)
200 BEGIN
201 SELECT t.cost_ind_rate_sch_id
202 INTO x_burden_sch_id
203 FROM pa_tasks t
204 WHERE t.task_id = p_task_id
205 AND t.project_id = p_project_id;
206
207 EXCEPTION
208 WHEN OTHERS THEN
209 x_burden_sch_id := NULL;
210 END;
211 END;
212 END;
213
214 End IF;
215
216 IF (p_project_id is NOT NULL and x_burden_sch_id is NULL) Then
217 BEGIN
218
219 SELECT irs.ind_rate_sch_id
220 INTO x_burden_sch_id
221 FROM pa_ind_rate_schedules irs
222 ,pa_projects_all pp
223 WHERE pp.project_id = p_project_id
224 AND irs.project_id = pp.project_id
225 AND irs.cost_ovr_sch_flag = 'Y' ;
226 EXCEPTION
227 WHEN NO_DATA_FOUND Then
228 SELECT pp.cost_ind_rate_sch_id
229 INTO x_burden_sch_id
230 FROM pa_projects_all pp
231 WHERE pp.project_id = p_project_id ;
232
233 END;
234
235 End If;
236
237 IF x_burden_sch_id is NOT NULL Then
238 Begin
239 SELECT irs.ind_rate_sch_revision_id
240 INTO x_burden_sch_revision_id
241 FROM pa_ind_rate_sch_revisions irs
242 WHERE irs.ind_rate_sch_id = x_burden_sch_id
243 AND irs.compiled_flag = 'Y'
244 AND trunc(p_exp_item_date) BETWEEN irs.start_date_active
245 and NVL(irs.end_date_active ,p_exp_item_date );
246
247 Exception
248 When NO_DATA_FOUND Then
249 l_stage := 'No Schedule Revision found nor Compiled for given burden Rate Schedule';
250 x_burden_sch_revision_id := NULL ;
251 x_status := -1;
252
253 When Others Then
254 l_stage := 'Unexpected error occured in get_projLevel_BurdSchds';
255 x_burden_sch_revision_id := NULL ;
256 x_status := sqlcode;
257 End ;
258 End If;
259
260 IF x_burden_sch_id is NULL Then
261 x_burden_sch_revision_id := NULL ;
262 x_status := -1;
263 End IF;
264
265 l_stage := 'The Out params x_burden_sch_id['||x_burden_sch_id||'RevsionId['||x_burden_sch_revision_id||
266 ']RetrunStatus['||x_status||']' ;
267 IF ( l_debug_flag = 'Y' )
268 THEN
269 print_msg(l_debug_flag,l_stage);
270 END IF;
271 Return;
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276 l_stage := 'Unexpected error occured in get_projLevel_BurdSchds ['||SQLERRM||SQLCODE;
277 x_burden_sch_revision_id := NULL ;
278 x_status := sqlcode;
279
280 END get_projLevel_BurdSchds ;
281
282 /* This API derives the cost rates based on the bill rate schedules */
283 PROCEDURE get_RateSchDetails
284 (p_schedule_type IN Varchar2
285 ,p_rate_sch_id IN Number
286 ,p_person_id IN Number
287 ,p_job_id IN Number
288 ,p_non_labor_resource IN Varchar2
289 ,p_expenditure_type IN Varchar2
290 ,p_rate_organization_id IN Number
291 ,p_exp_item_date IN Date
292 ,p_org_id IN Number
293 ,x_currency_code OUT NOCOPY Varchar2
294 ,x_cost_rate OUT NOCOPY Number
295 ,x_markup_percent OUT NOCOPY Number
296 ,x_return_status OUT NOCOPY Varchar2
297 ,x_error_msg_code OUT NOCOPY Varchar2 ) IS
298
299
300 l_cost_rate Number;
301 l_cost_rate_curr_code Varchar2(30);
302 l_markup_percent Number;
303 l_return_status varchar2(100) := 'S';
304 l_error_msg_code varchar2(1000):= NULL;
305
306 Cursor cur_nlr_sch_details IS
307 SELECT sch.rate_sch_currency_code
308 ,rates.rate
309 ,rates.markup_percentage
310 FROM pa_std_bill_rate_schedules_all sch
311 ,pa_bill_rates_all rates
312 WHERE sch.bill_rate_sch_id = p_rate_sch_id
313 AND sch.schedule_type = 'NON-LABOR'
314 AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
315 AND rates.expenditure_type = p_expenditure_type
316 AND ( rates.non_labor_resource is NULL
317 OR rates.non_labor_resource = p_non_labor_resource
318 )
319 AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
320 and trunc(nvl(rates.end_date_active,p_exp_item_date))
321 /*Bug fix:3793618 This is to ensure that records with NLR and Exp combo orders first */
322 ORDER BY decode(rates.non_labor_resource,p_non_labor_resource,0,1),rates.expenditure_type ;
323
324 Cursor cur_emp_sch_details IS
325 SELECT sch.rate_sch_currency_code
326 ,rates.rate
327 ,rates.markup_percentage
328 FROM pa_std_bill_rate_schedules_all sch
329 ,pa_bill_rates_all rates
330 WHERE sch.bill_rate_sch_id = p_rate_sch_id
331 AND sch.schedule_type = 'EMPLOYEE'
332 AND rates.person_id = p_person_id
333 AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
334 AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
335 and trunc(nvl(rates.end_date_active,p_exp_item_date));
336
337 Cursor cur_job_sch_details IS
338 SELECT sch.rate_sch_currency_code
339 ,rates.rate
340 ,rates.markup_percentage
341 FROM pa_std_bill_rate_schedules_all sch
342 ,pa_bill_rates_all rates
343 WHERE sch.bill_rate_sch_id = p_rate_sch_id
344 AND sch.schedule_type = 'JOB'
345 AND rates.job_id = pa_cross_business_grp.IsMappedToJob(p_job_id, rates.job_group_id)
346 AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
347 AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
348 and trunc(nvl(rates.end_date_active,p_exp_item_date));
349
350 BEGIN
351
352 /* Initialize the out variables */
353 x_return_status := 'S';
354 x_error_msg_code := Null;
355
356 IF p_schedule_type = 'EMPLOYEE' Then
357 OPEN cur_emp_sch_details;
358 FETCH cur_emp_sch_details INTO
359 l_cost_rate_curr_code
360 ,l_cost_rate
361 ,l_markup_percent ;
362 CLOSE cur_emp_sch_details;
363
364 Elsif p_schedule_type = 'JOB' Then
365 OPEN cur_job_sch_details;
366 FETCH cur_job_sch_details INTO
367 l_cost_rate_curr_code
368 ,l_cost_rate
369 ,l_markup_percent ;
370 CLOSE cur_job_sch_details;
371 Elsif p_schedule_type = 'NON-LABOR' Then
372 OPEN cur_nlr_sch_details;
373 FETCH cur_nlr_sch_details INTO
374 l_cost_rate_curr_code
375 ,l_cost_rate
376 ,l_markup_percent ;
377 CLOSE cur_nlr_sch_details;
378 End If;
379
380 If (l_cost_rate_curr_code is NULL OR l_cost_rate is NULL ) Then
381 x_return_status := 'E';
382 x_error_msg_code := 'No Planning Rates Setup';
383 End If;
384 /* Assigning the to OUT params */
385 x_currency_code := l_cost_rate_curr_code;
386 x_cost_rate := l_cost_rate;
387 x_markup_percent := l_markup_percent;
388
389 EXCEPTION
390 When Others Then
391 x_return_status := 'U';
392 x_error_msg_code := substr(sqlCode||sqlerrm,1,30);
393
394 END get_RateSchDetails;
395
396 /* This API derives cost rate based on expenditure Type */
397 FUNCTION GetExpTypeCostRate(p_exp_type Varchar2
398 ,p_exp_item_date Date
399 ,p_org_id Number
400 ) Return Number IS
401
402 l_expTypeCostRate Number;
403 l_stage varchar2(1000);
404 BEGIN
405 If p_exp_type is NOT NULL and p_exp_item_date is NOT NULL Then
406
407 l_stage := 'Executing sql to get Cost rates from expenditure type ';
408 SELECT R.Cost_Rate
409 INTO l_expTypeCostRate
410 FROM PA_Expenditure_Types T
411 ,PA_Expenditure_Cost_Rates_all R
412 WHERE T.Expenditure_type = R.Expenditure_type
413 AND T.Cost_Rate_Flag = 'Y'
414 AND R.Expenditure_type = p_exp_type
415 AND R.org_id = p_org_id --Bug#5903720
416 AND trunc(p_exp_item_date)
417 BETWEEN R.Start_Date_Active AND NVL(R.End_Date_Active, p_exp_item_date);
418
419 l_stage := 'ExpCostRate['||l_expTypeCostRate||']' ;
420
421 End If;
422 Return l_expTypeCostRate;
423
424 EXCEPTION
425 WHEN NO_DATA_FOUND THEN
426 RETURN NULL;
427
428 WHEN OTHERS THEN
429 print_msg('Y',l_stage);
430 RETURN NULL;
431
432 END;
433
434 /* This API derives the currency code for the given Operating Unit */
435 FUNCTION Get_curr_code(p_org_id IN NUMBER)
436
437 RETURN VARCHAR2 IS
438
439 l_currency_code varchar2(100) := NULL ;
440
441 BEGIN
442
443 SELECT FC.currency_code
444 INTO l_currency_code
445 FROM FND_CURRENCIES FC,
446 GL_SETS_OF_BOOKS GB,
447 PA_IMPLEMENTATIONS_ALL IMP
448 WHERE FC.currency_code = DECODE(imp.set_of_books_id, NULL, NULL, GB.currency_code)
449 AND GB.set_of_books_id = IMP.set_of_books_id
450 AND IMP.org_id = p_org_id; --Bug#5903720
451
452 return l_currency_code;
453
454
455 EXCEPTION
456 WHEN NO_DATA_FOUND THEN
457 l_currency_code:= NULL;
458 return l_currency_code;
459
460 WHEN OTHERS THEN
461 l_currency_code:= NULL;
462 return l_currency_code;
463
464 END Get_curr_code;
465
466 /* This API checks whether the project is burdened or not and returns Y if project
467 * type is burdended */
468 FUNCTION check_proj_burdened
469 (p_project_type IN VARCHAR2
470 ,p_project_id IN NUMBER ) RETURN VARCHAR2 IS
471
472 cursor cur_burden_flag IS
473 SELECT NVL(burden_cost_flag,'N')
474 FROM pa_project_types_all typ
475 , pa_projects_all proj
476 WHERE typ.project_type = P_project_type
477 AND proj.project_type = typ.project_type
478 AND proj.project_id = p_project_id
479 AND proj.org_id = typ.org_id; --Bug#5903720
480
481 l_burden_flag varchar2(10) := 'N';
482 BEGIN
483 /* Bug fix: 4230258 Use one-level cache logic so that executing cursor will be avoided */
484 IF ((pa_cost1.g_project_type is NULL OR pa_cost1.g_project_type <> p_project_type )
485 OR (pa_cost1.g_project_id is NULL or pa_cost1.g_project_id <> p_project_id)) Then
486
487 OPEN cur_burden_flag;
488 FETCH cur_burden_flag INTO l_burden_flag;
489 CLOSE cur_burden_flag;
490 --print_msg(g_debug_flag,'Executing cursor to get burden cost flag');
491 pa_cost1.g_project_type := p_project_type;
492 pa_cost1.g_project_id := p_project_id;
493 pa_cost1.g_burden_costFlag := l_burden_flag;
494 Else
495 --print_msg(g_debug_flag,'Getting from cache');
496 l_burden_flag := pa_cost1.g_burden_costFlag;
497 End If;
498 RETURN l_burden_flag;
499
500 END check_proj_burdened;
501
502 /* This API derives transaction raw cost, burden costs in transaction currency. ie. the currency associated
503 * with the rate schedule.
504 * The following are the rules to derive cost rates for the planning resource
505 * 1. By default the rate engine will derive the raw and burden costs based on the transaction currency.
506 * I.e. The currency associated with the rate schedule. If the transaction override currency is passed then costs will be
507 * converted from transaction currency to override currency.
508 * 2. If the override cost rate is passed then rate engine will derive the actual raw cost and raw cost rates
509 * based on the override cost rate
510 * 3. If the override burden multiplier is passed, the rate engine will derive the burden costs
511 * based on the override burden multiplier.
512 * 4. If the parameter rate based flag is 'N' then rate engine will not derive raw cost instead,
513 * the burden costs will be derived based on the passed value transaction raw cost and transaction currency.
514 * 5. Rates will be derived based on the in parameter p_exp_item_date
515 * This API returns x_return_status as 'S' for successful rate 'E' if no rate found 'U' in case of unexpected errors
516 *
517 * NOTE: For BOM related transactions the following params needs to be passed
518 * p_mfc_cost_source Required possible values be
519 * 1 - Return item cost from valuation cost type.
520 * 2 - Return item cost from user-provided cost type.
521 * 3 - Return item cost as the list price per unit from item definition.
522 * 4 - Return item cost as average of the last 5 PO receipts of this item.
523 * PO price includes non-recoverable tax.
524 * p_mfd_cost_type_id Optional param default is 0
525 * p_exp_organization_id Required
526 * p_BOM_resource_id Required
527 * p_inventory_item_id Required
528 *
529 */
530 PROCEDURE Get_Plan_Actual_Cost_Rates
531 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
532 ,p_project_type IN VARCHAR2
533 ,p_project_id IN NUMBER
534 ,p_task_id IN NUMBER
535 ,p_top_task_id IN NUMBER
536 ,p_Exp_item_date IN DATE
537 ,p_expenditure_type IN VARCHAR2
538 ,p_expenditure_OU IN NUMBER
539 ,p_project_OU IN NUMBER
540 ,p_Quantity IN NUMBER
541 ,p_resource_class IN VARCHAR2
542 ,p_person_id IN NUMBER DEFAULT NULL
543 ,p_non_labor_resource IN VARCHAR2 DEFAULT NULL
544 ,p_NLR_organization_id IN NUMBER DEFAULT NULL
545 ,p_override_organization_id IN NUMBER DEFAULT NULL
546 ,p_incurred_by_organization_id IN NUMBER DEFAULT NULL
547 ,p_inventory_item_id IN NUMBER DEFAULT NULL
548 ,p_BOM_resource_id IN NUMBER DEFAULT NULL
549 ,p_override_trxn_curr_code IN VARCHAR2 DEFAULT NULL
550 ,p_override_burden_cost_rate IN NUMBER DEFAULT NULL
551 ,p_override_trxn_cost_rate IN NUMBER DEFAULT NULL
552 ,p_override_trxn_raw_cost IN NUMBER DEFAULT NULL
553 ,p_override_trxn_burden_cost IN NUMBER DEFAULT NULL
554 ,p_mfc_cost_type_id IN NUMBER DEFAULT 0
555 ,p_mfc_cost_source IN NUMBER DEFAULT 2
556 ,p_item_category_id IN NUMBER DEFAULT NULL
557 ,p_job_id IN NUMBER DEFAULT NULL
558 ,p_plan_cost_job_rate_sch_id IN NUMBER DEFAULT NULL
559 ,p_plan_cost_emp_rate_sch_id IN NUMBER DEFAULT NULL
560 ,p_plan_cost_nlr_rate_sch_id IN NUMBER DEFAULT NULL
561 ,p_plan_cost_burden_sch_id IN NUMBER DEFAULT NULL
562 ,x_trxn_curr_code OUT NOCOPY VARCHAR2
563 ,x_trxn_raw_cost OUT NOCOPY NUMBER
564 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
565 ,x_trxn_burden_cost OUT NOCOPY NUMBER
566 ,x_trxn_burden_cost_rate OUT NOCOPY NUMBER
567 ,x_burden_multiplier OUT NOCOPY NUMBER
568 ,x_cost_ind_compiled_set_id OUT NOCOPY NUMBER
569 ,x_raw_cost_rejection_code OUT NOCOPY VARCHAR2
570 ,x_burden_cost_rejection_code OUT NOCOPY VARCHAR2
571 ,x_return_status OUT NOCOPY VARCHAR2
572 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
573
574 l_insufficient_parms EXCEPTION;
575 l_no_rate_found EXCEPTION;
576 l_no_burdrate_found EXCEPTION;
577 l_invalid_override_attributes EXCEPTION;
578 l_invalid_currency EXCEPTION;
579 l_cost_source Number := p_mfc_cost_source ;
580
581 l_stage varchar2(1000);
582 l_err_code varchar2(1000);
583 l_debug_flag varchar2(10);
584 l_msg_data varchar2(1000);
585 l_msg_count Number;
586 l_return_status varchar2(10);
587 l_job_id Number;
588 l_txn_curr_code varchar2(100);
589 l_txn_raw_cost Number;
590 l_txn_raw_cost_rate Number;
591 l_burden_cost Number;
592 l_burden_cost_rate Number;
593 l_burden_multiplier Number;
594 l_override_organization_id Number;
595 l_cost_rate_multiplier Number;
596 l_start_date_active Date;
597 l_end_date_active Date;
598 l_org_labor_sch_rule_id Number;
599 l_costing_rule Varchar2(100);
600 l_rate_sch_id Number;
601 l_acct_rate_type varchar2(100);
602 l_acct_rate_date_code varchar2(100);
603 l_acct_exch_rate Number;
604 l_ot_project_id Number;
605 l_ot_task_id Number;
606 l_api_version Number;
607 l_burd_sch_id Number;
608 l_burd_sch_rev_id Number;
609 l_burd_sch_fixed_date Date;
610 l_burd_sch_cost_base varchar2(150);
611 l_burd_sch_cp_structure varchar2(150);
612 l_burd_ind_compiled_set_id Number;
613 l_proj_flag varchar2(1000);
614 l_rate_organization_id Number;
615 l_markup_percent Number;
616 l_bill_rate_schedule_type varchar2(150);
617 l_bill_rate_sch_id Number;
618
619 /* This is to identify the planning transactions as LABOR, NON-LABOR or BOM transactions depending on the
620 * validation of the input params*/
621 l_trxn_type varchar2(100) := NULL;
622
623 BEGIN
624
625 --Initialize the out variables
626 l_job_id := p_job_id;
627 l_txn_curr_code := p_override_trxn_curr_code;
628 l_txn_raw_cost := p_override_trxn_raw_cost ;
629 l_txn_raw_cost_rate := p_override_trxn_cost_rate;
630 l_burden_cost := p_override_trxn_burden_cost ;
631 l_burden_cost_rate := p_override_burden_cost_rate;
632 l_burden_multiplier := NULL;
633 l_override_organization_id := p_override_organization_id;
634 l_cost_rate_multiplier := NUll;
635 l_msg_data := NULL;
636 l_msg_count := 0;
637 l_return_status := 'S';
638 x_raw_cost_rejection_code := Null;
639 x_burden_cost_rejection_code := Null;
640 x_error_msg_code := Null;
641 x_return_status := 'S';
642
643 --- Initialize the error statck
644 If g_debug_flag is NULL Then
645 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
646 g_debug_flag := NVL(g_debug_flag, 'N');
647 End If;
648 l_debug_flag := NVL(g_debug_flag,'N');
649 IF l_debug_flag = 'Y' Then
650 PA_DEBUG.init_err_stack ('PA_COST1.Get_Plan_Actual_Cost_Rates');
651 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
652 ,x_write_file => 'LOG'
653 ,x_debug_mode => l_debug_flag
654 );
655 End If;
656
657 /* Derive the Rate Organization Id from the params*/
658 l_rate_organization_id := NVL(p_override_organization_id,NVL(p_incurred_by_organization_id,p_nlr_organization_id));
659
660 l_stage := 'Inside PA_COST1.Get_Plan_Actual_Cost_Rates API';
661 l_stage := l_stage||' IN PARAMS mode['||p_calling_mode||']Proj Type['||p_project_type||
662 ']Proj Id['||p_project_id||']TaskId['||p_task_id||']TopTask['||p_top_task_id||
663 ']Ei Date['||p_Exp_item_date||']ExpType['||p_expenditure_type||
664 ']ResClass['||p_resource_class||']Personid['||p_person_id||']NLR['||p_non_labor_resource||
665 ']NLR Org['||p_NLR_organization_id||']ExpOU['||p_expenditure_OU||']ProjOU['||p_project_OU||
666 ']IncurrOrg['||p_incurred_by_organization_id||']OverrideOrg['||l_override_organization_id||
667 ']Qty['||p_Quantity||']InvItemId['||p_inventory_item_id||']BomRes['||p_BOM_resource_id||
668 ']ProjCostJobId['||p_job_id||']p_mfc_cost_type_id['||p_mfc_cost_type_id||
669 ']p_mfc_cost_source['||p_mfc_cost_source||']RateOrganzId['||l_rate_organization_id||
670 ']JobRateSch['||p_plan_cost_job_rate_sch_id||']EmpRateSch['||p_plan_cost_emp_rate_sch_id||
671 ']NlrRateSch['||p_plan_cost_nlr_rate_sch_id||']BurdRateSch['||p_plan_cost_burden_sch_id||']' ;
672 print_msg(l_debug_flag,l_stage);
673
674 l_stage := 'Override values override_trxn_curr_code['||p_override_trxn_curr_code||
675 ']overide Multi['||p_override_burden_cost_rate||']OverrideCostRate['||p_override_trxn_cost_rate||
676 ']overrideRawCost['||p_override_trxn_raw_cost||']OverrideBurdCost['||p_override_trxn_burden_cost||']' ;
677 print_msg(l_debug_flag,l_stage);
678
679
680 /* Validate the override parameters */
681 l_trxn_type := NULL;
682 If ( p_override_trxn_raw_cost is NOT NULL
683 and p_override_trxn_burden_cost is NOT NULL ) Then
684 -- Just return the control back to calling api
685 l_stage := 'Assigning override values to Out params';
686 print_msg(l_debug_flag,l_stage);
687 x_trxn_raw_cost := p_override_trxn_raw_cost;
688 x_trxn_burden_cost := p_override_trxn_burden_cost;
689 x_trxn_curr_code := p_override_trxn_curr_code;
690 x_trxn_raw_cost_rate := p_override_trxn_cost_rate;
691 x_trxn_burden_cost_rate := p_override_burden_cost_rate;
692 x_raw_cost_rejection_code := Null;
693 x_burden_cost_rejection_code := Null;
694 x_error_msg_code := Null;
695 x_return_status := 'S';
696 If l_debug_flag = 'Y' Then
697 PA_DEBUG.reset_err_stack;
698 End If;
699 RETURN;
700 ElsIf (p_override_trxn_cost_rate is NOT NULL OR p_override_trxn_raw_cost is NOT NULL ) Then
701 l_stage := 'Validating override params';
702 IF p_override_trxn_curr_code is NULL Then
703 l_stage := 'Validating override params No Override Currency';
704 print_msg(l_debug_flag,l_stage);
705 Raise l_invalid_override_attributes;
706 Else
707 l_txn_curr_code := p_override_trxn_curr_code;
708 -- if cost rate is null derive the cost rate based on quantity and amount
709 If p_override_trxn_cost_rate is NULL Then
710 If NVL(p_quantity,0) <> 0 Then
711 l_txn_raw_cost_rate := p_override_trxn_raw_cost / NVL(p_quantity,1);
712 Else
713 l_txn_raw_cost_rate := 1;
714 End IF;
715 Else
716 l_txn_raw_cost_rate := p_override_trxn_cost_rate;
717 End If;
718
719 -- if cost is null then derive the cost based on rate and quantity
720 If p_override_trxn_raw_cost is NULL Then
721 If NVL(p_quantity,0) <> 0 then
722 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
723 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code );
724 Else
725 l_txn_raw_cost := null;
726 End If;
727 Else
728 l_txn_raw_cost := p_override_trxn_raw_cost;
729 End If;
730 l_trxn_type := 'BURDEN';
731 End If;
732 End If;
733
734 /* Based on the resource class and input params set the transaction type as one of the following values
735 * LABOR RATE -- for resource class is People and person or job is not null
736 * BOM RESOURCE RATE -- for resource class is people and bom resource id isnot null
737 * EXP TYPE RATE -- for resource class is people and financial category
738 * NON LABOR RESOURCE RATE -- for resource class equipment
739 * BOM EQUIPMENT RATE -- for resource class equiment and inventory item is not null
740 * MATERIAL ITEM RATE -- for resource class material items and inventory items not null
741 */
742 -- Identify the transaction Type
743 If l_trxn_type is NULL Then
744 l_stage := 'Deriving transaction type based on resource class';
745 print_msg(l_debug_flag,l_stage);
746
747 If p_calling_mode = 'ACTUAL_RATES' Then
748 If (p_resource_class = 'PEOPLE') Then
749
750 If ( p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
751 l_trxn_type := 'BOM RESOURCE RATE';
752
753 ElsIf (p_person_id is NOT NULL OR p_job_id is NOT NULL ) Then
754 l_trxn_type := 'LABOR RATE' ;
755
756 /* Elsif (p_expenditure_type is NOT NULL and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
757 l_trxn_type := 'EXP TYPE RATE';
758 */
759
760 Else
761 l_stage := 'Invalid People Class params';
762 -- This is an invalid combination
763 Raise l_insufficient_parms;
764 End If;
765
766 Elsif (p_resource_class = 'EQUIPMENT') Then
767 If (p_non_labor_resource is NOT NULL
768 and NVL(p_NLR_organization_id,l_rate_organization_id) is NOT NULL
769 and p_expenditure_type is NOT NULL ) Then
770 l_trxn_type := 'NON LABOR RESOURCE RATE' ;
771
772 Elsif (p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
773 l_trxn_type := 'BOM EQUIPMENT RATE';
774
775 /* Bug fix: as per discussion with Anders and Jhonson for people and equipemnt class
776 * if rate is not found from schedule then it should pick from resource class level
777 * finally decided as we should retain this logic */
778 Elsif (p_expenditure_type is NOT NULL
779 and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
780 l_trxn_type := 'EXP TYPE RATE';
781
782 Else
783 l_stage := 'Invalid Equipment Class params';
784 -- This is an invalid combination
785 Raise l_insufficient_parms;
786 End If;
787 Elsif (p_resource_class = 'MATERIAL_ITEMS') Then
788
789 If (p_inventory_item_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
790 l_trxn_type := 'MATERIAL ITEM RATE';
791 Elsif (p_expenditure_type is NOT NULL
792 and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
793 l_trxn_type := 'EXP TYPE RATE';
794 Else
795 l_stage := 'Invalid Material Itms Class params';
796 -- This is an invalid combination
797 Raise l_insufficient_parms;
798 End If;
799
800 Elsif (p_resource_class = 'FINANCIAL_ELEMENTS' ) Then
801 If p_expenditure_type is NOT NULL Then
802 IF check_expCostRateFlag(p_expenditure_type) = 'Y' Then
803 l_trxn_type := 'EXP TYPE RATE';
804 Else
805 l_stage := 'Invalid Financial Elements Class params';
806 -- This is an invalid combination
807 Raise l_insufficient_parms;
808 End If;
809 /**Else
810 l_stage := 'Financial Elements Class params NO COST RATE';
811 l_trxn_type := 'EXP TYPE RATE N_FLAG';
812 End IF;
813 **/
814 Else
815 l_stage := 'Invalid Financial Elements Class params';
816 -- This is an invalid combination
817 Raise l_insufficient_parms;
818 End If;
819 End If; -- end of resource class
820
821 ELSIF p_calling_mode = 'PLAN_RATES' Then
822
823 If (p_resource_class = 'PEOPLE') Then
824 If ( p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
825 l_trxn_type := 'BOM RESOURCE RATE';
826
827 ElsIf (p_person_id is NOT NULL and p_plan_cost_emp_rate_sch_id is NOT NULL ) Then
828 l_trxn_type := 'LABOR SCH RATE' ;
829
830 Elsif (p_job_id is NOT NULL and p_plan_cost_job_rate_sch_id is NOT NULL ) Then
831 l_trxn_type := 'JOB SCH RATE';
832
833 /* Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
834 l_trxn_type := 'NON LABOR SCH RATE';
835 */
836
837 Else
838 l_stage := 'Invalid People Class params';
839 -- This is an invalid combination
840 Raise l_insufficient_parms;
841 End If;
842 Elsif (p_resource_class = 'EQUIPMENT') Then
843 If (p_non_labor_resource is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL
844 and p_expenditure_type is NOT NULL ) Then
845 l_trxn_type := 'NON LABOR SCH RATE' ;
846
847 Elsif (p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
848 l_trxn_type := 'BOM EQUIPMENT RATE';
849
850 Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
851 l_trxn_type := 'NON LABOR SCH RATE' ;
852
853 Else
854 l_stage := 'Invalid Equipment Class params';
855 -- This is an invalid combination
856 Raise l_insufficient_parms;
857 End If;
858 Elsif (p_resource_class = 'MATERIAL_ITEMS') Then
859
860 If (p_inventory_item_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
861 l_trxn_type := 'MATERIAL ITEM RATE';
862
863 Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
864 l_trxn_type := 'NON LABOR SCH RATE' ;
865
866 Else
867 l_stage := 'Invalid Material Itms Class params';
868 -- This is an invalid combination
869 Raise l_insufficient_parms;
870 End If;
871 Elsif (p_resource_class = 'FINANCIAL_ELEMENTS' ) Then
872 If (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
873 l_trxn_type := 'NON LABOR SCH RATE' ;
874
875 /**Else
876 If p_expenditure_type is NOT NULL Then
877 IF check_expCostRateFlag(p_expenditure_type) = 'Y' Then
878 l_trxn_type := 'EXP TYPE RATE';
879 Else
880 l_stage := 'Financial Elements Class params NO COST RATE';
881 l_trxn_type := 'EXP TYPE RATE N_FLAG';
882 End IF;
883 **/
884 Else
885 l_stage := 'Invalid Financial Elements Class params';
886 -- This is an invalid combination
887 Raise l_insufficient_parms;
888 --End If;
889 End If;
890
891 End If; -- end of resource class
892 End If; -- end of calling mode
893 End If ; -- end of transaction type null
894
895 l_stage := 'After validating input params: Transaction Type['||l_trxn_type||']' ;
896 print_msg(l_debug_flag,l_stage);
897
898 /* Bug fix: 4232181 Derive the organization overrides from the project level */
899 IF l_trxn_type IN ('LABOR SCH RATE','JOB SCH RATE','LABOR RATE') Then
900 IF l_override_organization_id is NULL Then
901 l_stage := 'Calling pa_cost.Override_exp_organization api';
902 print_msg(l_debug_flag,l_stage);
903 pa_cost.Override_exp_organization
904 (P_item_date => p_exp_item_date
905 ,P_person_id => p_person_id
906 ,P_project_id => p_project_id
907 ,P_incurred_by_organz_id => p_incurred_by_organization_id
908 ,P_Expenditure_type => p_expenditure_type
909 ,X_overr_to_organization_id => l_override_organization_id
910 ,X_return_status => l_return_status
911 ,X_msg_count => l_msg_count
912 ,X_msg_data => l_msg_data
913 );
914 l_stage := 'Return status of pa_cost.Override_exp_organization ['||l_return_status||']';
915 l_stage := l_stage||']msgData['||l_msg_data||']OverideOrg['||l_override_organization_id||']' ;
916 print_msg(l_debug_flag,l_stage);
917 End If;
918 End If;
919
920 /* Actual Rate Calculation logic starts here */
921 IF l_trxn_type in ('BOM EQUIPMENT RATE', 'BOM RESOURCE RATE') Then
922
923 -- call the api provided by PO/BOM team to dervie the cost rate
924 l_cost_source := p_mfc_cost_source ;
925 l_api_version := 1.0;
926 IF p_BOM_resource_id is NOT NULL Then
927
928 BEGIN
929 l_stage := 'Calling CST_ItemResourceCosts_GRP.Get_ResourceRate API';
930 print_msg(l_debug_flag,l_stage);
931
932 CST_ItemResourceCosts_GRP.Get_ResourceRate(
933 p_api_version => l_api_version
934 ,p_init_msg_list => FND_API.G_FALSE
935 ,p_commit => FND_API.G_FALSE
936 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
937 ,x_return_status => l_return_status
938 ,x_msg_count => l_msg_count
939 ,x_msg_data => l_msg_data
940 ,p_resource_id => p_BOM_resource_id
941 ,p_organization_id => l_rate_organization_id
942 ,p_cost_type_id => p_mfc_cost_type_id
943 ,x_resource_rate => l_txn_raw_cost_rate
944 ,x_currency_code => l_txn_curr_code
945 );
946 l_stage := 'After CST_ItemResourceCosts_GRP.Get_ResourceRate API returnStatus['||l_return_status||
947 ']CostRate['||l_txn_raw_cost_rate||']CurrCode['||l_txn_curr_code||']msgDate['||l_msg_data||']';
948 print_msg(l_debug_flag,l_stage);
949 If ( l_return_status <> 'S' OR l_txn_curr_code is NULL ) Then
950 If l_return_status = 'U' Then
951 l_stage := l_stage||'SQLERRM['||SQLCODE||SQLERRM;
952 End If;
953 print_msg(l_debug_flag,l_stage);
954 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
955 Raise l_no_rate_found;
956 End If;
957
958 If l_txn_curr_code is NOT NULL Then
959 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
960 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code);
961 End If;
962 EXCEPTION
963 WHEN NO_DATA_FOUND THEN
964 l_msg_data := 'PA_FP_MISSING_RATE';
965 l_return_status := 'E';
966 x_return_status := 'E';
967 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
968 RAISE l_no_rate_found;
969 WHEN OTHERS THEN
970 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
971 l_msg_data := 'PA_FP_MISSING_RATE';
972 l_return_status := 'E';
973 x_return_status := 'E';
974 x_error_msg_code := 'PA_FP_MISSING_RATE';
975 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
976 RAISE l_no_rate_found;
977 End If;
978 RAISE;
979 END;
980
981 End If;
982
983 ElsIF l_trxn_type in ('MATERIAL ITEM RATE') Then
984 IF p_inventory_item_id is NOT NULL Then
985 -- call the api provided by PO/BOM team to dervie the Raw cost
986 BEGIN
987 l_stage := 'Calling CST_ItemResourceCosts_GRP.Get_ItemCost API';
988 print_msg(l_debug_flag,l_stage);
989 l_cost_source := p_mfc_cost_source ;
990 l_api_version := 1.0;
991 CST_ItemResourceCosts_GRP.Get_ItemCost
992 (
993 p_api_version => l_api_version
994 ,p_init_msg_list => FND_API.G_FALSE
995 ,p_commit => FND_API.G_FALSE
996 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
997 ,x_return_status => l_return_status
998 ,x_msg_count => l_msg_count
999 ,x_msg_data => l_msg_data
1000 ,p_item_id => p_inventory_item_id
1001 ,p_organization_id => l_rate_organization_id
1002 ,p_cost_source => l_cost_source
1003 ,p_cost_type_id => p_mfc_cost_type_id
1004 /*Bug fix:4154009 ,x_item_cost => l_txn_raw_cost */
1005 ,x_item_cost => l_txn_raw_cost_rate
1006 ,x_currency_code => l_txn_curr_code
1007 );
1008 l_stage := 'After CST_ItemResourceCosts_GRP.Get_ItemCost API returnStatus['||l_return_status||
1009 ']MaterialCostRate['||l_txn_raw_cost_rate||']CurrCode['||l_txn_curr_code||']msgDate['||l_msg_data||']' ;
1010 print_msg(l_debug_flag,l_stage);
1011 If ( l_return_status <> 'S' OR l_txn_curr_code is NULL ) Then
1012 If l_return_status = 'U' Then
1013 l_stage := l_stage||'SQLERRM['||SQLCODE||SQLERRM;
1014 End If;
1015 print_msg(l_debug_flag,l_stage);
1016 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1017 Raise l_no_rate_found;
1018 End If;
1019
1020 If l_txn_curr_code is NOT NULL Then
1021 -- this is the list price per unit
1022 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1023 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code );
1024 End If;
1025 /*i Bug fix:4154009 The Api returns the rate not the cost So need not re derive the rate
1026 If l_txn_raw_cost_rate is NULL Then
1027 -- derive the cost rate based on the item cost and quantity
1028 If NVL(p_quantity,1) <> 0 Then
1029 l_txn_raw_cost_rate := l_txn_raw_cost / NVL(p_quantity,1);
1030 Else
1031 l_txn_raw_cost_rate := l_txn_raw_cost ;
1032 End If;
1033 End If;
1034 **/
1035 EXCEPTION
1036 WHEN NO_DATA_FOUND THEN
1037 l_msg_data := 'PA_FP_MISSING_RATE';
1038 l_return_status := 'E';
1039 l_msg_data := 'PA_FP_MISSING_RATE';
1040 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1041 RAISE l_no_rate_found;
1042 WHEN OTHERS THEN
1043 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
1044 l_return_status := 'E';
1045 l_msg_data := 'PA_FP_MISSING_RATE';
1046 x_error_msg_code := 'PA_FP_MISSING_RATE';
1047 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1048 RAISE l_no_rate_found;
1049 End If;
1050 RAISE;
1051 END;
1052 End If;
1053
1054
1055 ELSIF l_trxn_type = 'LABOR RATE' Then
1056 -- call the get raw cost api for the person id is not null else call the requirement raw cost for the
1057 -- job and organization type transactions
1058 If (p_person_id is NOT NULL OR p_job_id is NOT NULL ) Then
1059 /* Derive default labor cost multiplier for the given Tasks */
1060 IF ( p_task_id IS NOT NULL AND l_cost_rate_multiplier is NULL ) THEN
1061 l_stage := 'Getting labor cost multiplier name';
1062 l_cost_rate_multiplier := get_CostRateMultiplier
1063 (p_task_id => p_task_id
1064 ,p_exp_item_date => p_exp_item_date
1065 );
1066 End If;
1067
1068 l_stage := 'Calling PA_COST_RATE_PUB.get_labor_rate API in STAFFED calling mode';
1069 print_msg(l_debug_flag,l_stage);
1070 l_rate_organization_id := NVL(l_override_organization_id,
1071 NVl(p_incurred_by_organization_id,p_nlr_organization_id));
1072 PA_COST_RATE_PUB.get_labor_rate
1073 (p_person_id => p_person_id
1074 ,p_txn_date => p_Exp_item_date
1075 ,p_calling_module =>'STAFFED'
1076 ,p_org_id => NVL(p_expenditure_ou,p_project_OU)
1077 ,x_job_id => l_job_id
1078 ,x_organization_id =>l_rate_organization_id
1079 ,x_cost_rate =>l_txn_raw_cost_rate
1080 ,x_start_date_active =>l_start_date_active
1081 ,x_end_date_active =>l_end_date_active
1082 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
1083 ,x_costing_rule =>l_costing_rule
1084 ,x_rate_sch_id =>l_rate_sch_id
1085 ,x_cost_rate_curr_code =>l_txn_curr_code
1086 ,x_acct_rate_type =>l_acct_rate_type
1087 ,x_acct_rate_date_code =>l_acct_rate_date_code
1088 ,x_acct_exch_rate =>l_acct_exch_rate
1089 ,x_ot_project_id =>l_ot_project_id
1090 ,x_ot_task_id =>l_ot_task_id
1091 ,x_err_stage => l_msg_data
1092 ,x_err_code => l_err_code
1093 );
1094
1095 l_stage := 'After get_labor_rate :return code['||l_err_code||']msgData['||l_msg_data||
1096 ']LaborCostRate['||l_txn_raw_cost_rate||']CostCurrCode['||l_txn_curr_code||']' ;
1097 print_msg(l_debug_flag,l_stage);
1098
1099 If l_err_code is NOT NULL OR l_txn_raw_cost_rate is NULL THEN
1100 l_stage := 'No Rate from Get Labor Rate(STAFFED) API:'||l_msg_data ;
1101 print_msg(l_debug_flag,l_stage);
1102 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1103 RAISE l_no_rate_found;
1104 End If;
1105
1106 If l_txn_curr_code is NOT NULL Then
1107 l_txn_raw_cost_rate := l_txn_raw_cost_rate * NVL(l_cost_rate_multiplier,1);
1108 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1109 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1110 End If;
1111
1112 End If;
1113
1114 ELSIF l_trxn_type = 'NON LABOR RESOURCE RATE' Then
1115 -- Call non-labor raw cost api
1116 l_stage := 'Calling Non Labor raw cost API';
1117 print_msg(l_debug_flag,l_stage);
1118 If (p_non_labor_resource is NOT NULL and p_expenditure_type is NOT NULL ) Then
1119 pa_cost1.Get_Non_Labor_raw_cost
1120 (p_project_id => p_project_id
1121 ,p_task_id => p_task_id
1122 ,p_non_labor_resource => p_non_labor_resource
1123 ,p_nlr_organization_id => p_nlr_organization_id
1124 ,p_expenditure_type => p_expenditure_type
1125 ,p_exp_item_date => p_exp_item_date
1126 ,p_override_organization_id => l_rate_organization_id
1127 ,p_quantity => p_quantity
1128 ,p_org_id => NVL(p_expenditure_ou,p_project_ou)
1129 ,p_nlr_schedule_id => Null
1130 ,x_trxn_raw_cost_rate => l_txn_raw_cost_rate
1131 ,x_trxn_raw_cost => l_txn_raw_cost
1132 ,x_txn_currency_code => l_txn_curr_code
1133 ,x_return_status => l_return_status
1134 ,x_error_msg_code => l_msg_data
1135 );
1136 l_stage := 'After Get_Non_Labor_raw_cost api return status['||l_return_status||']msgData['||l_msg_data||
1137 ']NlrCostrate['||l_txn_raw_cost_rate||']NlrRawcost['||l_txn_raw_cost||
1138 ']NlrCostRateCurr['||l_txn_curr_code||']' ;
1139 print_msg(l_debug_flag,l_stage);
1140
1141 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1142 x_raw_cost_rejection_code := 'PA_NLR_NO_RATE_FOUND' ;
1143 RAISE l_no_rate_found;
1144 End If;
1145 End If;
1146
1147 ELSIF l_trxn_type = 'EXP TYPE RATE' Then
1148 If ( p_exp_item_date is NOT NULL and p_expenditure_type is NOT NULL
1149 and NVL(p_expenditure_ou,p_project_ou) is NOT NULL ) Then
1150 /* get the currency code */
1151 l_txn_curr_code := Get_curr_code(p_org_id => NVL(p_expenditure_ou,p_project_ou));
1152 l_txn_raw_cost_rate := GetExpTypeCostRate
1153 (p_exp_type => p_expenditure_type
1154 ,p_exp_item_date => p_exp_item_date
1155 ,p_org_id => NVL(p_expenditure_ou,p_project_ou)
1156 );
1157
1158 If l_txn_raw_cost_rate is NULL OR l_txn_curr_code is NULL Then
1159 l_stage := 'No Rate from GetExpTypeCostRate api';
1160 print_msg(l_debug_flag,l_stage);
1161 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1162 RAISE l_no_rate_found;
1163 End If;
1164
1165 If l_txn_raw_cost_rate is NOT NULL and l_txn_curr_code is NOT NULL Then
1166 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1167 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1168 End If;
1169 l_stage := 'ExpcostRateCur['||l_txn_curr_code||']ExpCostRate['||l_txn_raw_cost_rate||']' ;
1170 print_msg(l_debug_flag,l_stage);
1171 End If;
1172
1173 ELSIF l_trxn_type = 'EXP TYPE RATE N_FLAG' Then
1174 If p_override_trxn_curr_code is NULL Then
1175 l_txn_curr_code := Get_curr_code(p_org_id =>
1176 NVL(p_expenditure_ou,p_project_ou));
1177 Else
1178 l_txn_curr_code := p_override_trxn_curr_code;
1179 End If;
1180
1181 l_txn_raw_cost_rate := 1;
1182 If l_txn_raw_cost_rate is NULL OR l_txn_curr_code is NULL Then
1183 l_stage := 'No Rate currency code from Get_curr_code api';
1184 print_msg(l_debug_flag,l_stage);
1185 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1186 RAISE l_no_rate_found;
1187 End If;
1188
1189 If l_txn_raw_cost_rate is NOT NULL and l_txn_curr_code is NOT NULL Then
1190 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1191 (l_txn_raw_cost_rate * NVL(p_quantity,0),
1192 l_txn_curr_code );
1193 End If;
1194 l_stage := 'Get_curr_code['||l_txn_curr_code||']ExpCostRate['||
1195 l_txn_raw_cost_rate||']' ;
1196 print_msg(l_debug_flag,l_stage);
1197
1198 ELSIF l_trxn_type in ('LABOR SCH RATE','JOB SCH RATE','NON LABOR SCH RATE') Then
1199 /* Derive default labor cost multiplier for the given Tasks */
1200 IF ( p_task_id IS NOT NULL AND l_cost_rate_multiplier is NULL
1201 and l_trxn_type IN ('LABOR SCH RATE','JOB SCH RATE' )) THEN
1202 l_stage := 'Getting labor cost multiplier name';
1203 l_cost_rate_multiplier := get_CostRateMultiplier
1204 (p_task_id => p_task_id
1205 ,p_exp_item_date => p_exp_item_date
1206 );
1207 End If;
1208
1209 l_rate_organization_id := NVL(l_override_organization_id,
1210 NVl(p_incurred_by_organization_id,p_nlr_organization_id));
1211 If l_trxn_type = 'LABOR SCH RATE' Then
1212 l_bill_rate_schedule_type := 'EMPLOYEE';
1213 l_bill_rate_sch_id := p_plan_cost_emp_rate_sch_id ;
1214 Elsif l_trxn_type = 'JOB SCH RATE' Then
1215 l_bill_rate_schedule_type := 'JOB';
1216 l_bill_rate_sch_id := p_plan_cost_job_rate_sch_id ;
1217 Elsif l_trxn_type = 'NON LABOR SCH RATE' Then
1218 l_bill_rate_schedule_type := 'NON-LABOR';
1219 l_bill_rate_sch_id := p_plan_cost_Nlr_rate_sch_id ;
1220 l_cost_rate_multiplier := NULL;
1221 End If;
1222 l_stage := 'Calling get_RateSchDetails SchType['||l_bill_rate_schedule_type||']SchId['||l_bill_rate_sch_id||']';
1223 print_msg(l_debug_flag,l_stage);
1224 pa_cost1.get_RateSchDetails
1225 (p_schedule_type => l_bill_rate_schedule_type
1226 ,p_rate_sch_id => l_bill_rate_sch_id
1227 ,p_person_id => p_person_id
1228 ,p_job_id => p_job_id
1229 ,p_non_labor_resource => p_non_labor_resource
1230 ,p_expenditure_type => p_expenditure_type
1231 ,p_rate_organization_id => l_rate_organization_id
1232 ,p_exp_item_date => p_exp_item_date
1233 ,p_org_id => NVL(p_expenditure_OU,p_project_ou)
1234 ,x_currency_code => l_txn_curr_code
1235 ,x_cost_rate => l_txn_raw_cost_rate
1236 ,x_markup_percent => l_markup_percent
1237 ,x_return_status => l_return_status
1238 ,x_error_msg_code => l_msg_data );
1239
1240 l_stage := 'After Calling Get_RateSchDetails api return status['||l_return_status||
1241 ']msgData['||l_msg_data||']RateSchCostrate['||l_txn_raw_cost_rate||
1242 ']RateSchRawcost['||l_txn_raw_cost||']RateSchCurr['||l_txn_curr_code||
1243 ']MarkupPercent['||l_markup_percent||']' ;
1244 print_msg(l_debug_flag,l_stage);
1245
1246 /* check if no rate found for the Employee sch then derive the
1247 * rate from job schedule if the resource class is people
1248 */
1249 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1250 If (p_resource_class = 'PEOPLE'
1251 AND l_trxn_type = 'LABOR SCH RATE'
1252 AND p_job_id is NOT NULL
1253 AND p_plan_cost_job_rate_sch_id is NOT NULL ) Then
1254
1255 l_trxn_type := 'JOB SCH RATE' ;
1256 l_bill_rate_schedule_type := 'JOB';
1257 l_bill_rate_sch_id := p_plan_cost_job_rate_sch_id ;
1258
1259 l_stage := 'Calling get_RateSchDetails SchType['
1260 ||l_bill_rate_schedule_type||']SchId['
1261 ||l_bill_rate_sch_id||']';
1262 print_msg(l_debug_flag,l_stage);
1263 pa_cost1.get_RateSchDetails
1264 (p_schedule_type => l_bill_rate_schedule_type
1265 ,p_rate_sch_id => l_bill_rate_sch_id
1266 ,p_person_id => p_person_id
1267 ,p_job_id => p_job_id
1268 ,p_non_labor_resource => p_non_labor_resource
1269 ,p_expenditure_type => p_expenditure_type
1270 ,p_rate_organization_id => l_rate_organization_id
1271 ,p_exp_item_date => p_exp_item_date
1272 ,p_org_id => NVL(p_expenditure_OU,p_project_ou)
1273 ,x_currency_code => l_txn_curr_code
1274 ,x_cost_rate => l_txn_raw_cost_rate
1275 ,x_markup_percent => l_markup_percent
1276 ,x_return_status => l_return_status
1277 ,x_error_msg_code => l_msg_data );
1278
1279 l_stage := 'After Calling Get_JOBRateSchDetails api return status['||l_return_status||
1280 ']msgData['||l_msg_data||']RateSchCostrate['||l_txn_raw_cost_rate||
1281 ']RateSchRawcost['||l_txn_raw_cost||']RateSchCurr['||l_txn_curr_code||
1282 ']MarkupPercent['||l_markup_percent||']' ;
1283 print_msg(l_debug_flag,l_stage);
1284
1285 End If;
1286 End If;
1287
1288 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1289 x_raw_cost_rejection_code := 'PA_NO_PLAN_SCH_RATE_FOUND' ;
1290 RAISE l_no_rate_found;
1291 End If;
1292 If l_txn_curr_code is NOT NULL Then
1293 l_txn_raw_cost_rate := l_txn_raw_cost_rate * NVL(l_cost_rate_multiplier,1);
1294 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1295 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1296 End If;
1297
1298 END If; -- end of the transaction type
1299
1300 --Assign the out variables
1301 x_trxn_curr_code := l_txn_curr_code;
1302 x_trxn_raw_cost := l_txn_raw_cost;
1303 x_trxn_raw_cost_rate := l_txn_raw_cost_rate;
1304 l_stage := 'End of Raw Cost Calculation:RawCost['||x_trxn_raw_cost||']Rate['||x_trxn_raw_cost_rate||
1305 ']CurrCode['||x_trxn_curr_code||']';
1306 print_msg(l_debug_flag,l_stage);
1307 /* End of Raw Cost calculation*/
1308
1309 l_proj_flag := pa_cost1.check_proj_burdened(p_project_type,p_project_id);
1310 print_msg(l_debug_flag,'ProjBurdFlag['||l_proj_flag||']');
1311 -- Check if project type is burdened if so calculate the burdened costs
1312
1313 /**** Burden cost Calculation Starts here */
1314
1315 If x_trxn_raw_cost is NOT NULL Then
1316
1317 If ( p_override_trxn_burden_cost is NOT NULL OR
1318 p_override_burden_cost_rate is NOT NULL) Then
1319 --assigning override burden cost to out params
1320 l_stage := 'Deriving burden cost from Override params';
1321 /* Assign burden cost*/
1322 If p_override_trxn_burden_cost is NOT NULL Then
1323 l_burden_cost := p_override_trxn_burden_cost;
1324
1325 Elsif p_override_burden_cost_rate is NOT NULL Then
1326 -- if quantity is zero this is amount based so multiply cost * rate
1327 If NVL(p_quantity,0) = 0 Then
1328 l_burden_cost := pa_currency.round_trans_currency_amt1
1329 (p_override_burden_cost_rate * x_trxn_raw_cost ,l_txn_curr_code ) ;
1330 Else
1331 l_burden_cost := pa_currency.round_trans_currency_amt1
1332 (p_override_burden_cost_rate * NVL(p_quantity,1),l_txn_curr_code ) ;
1333 End If;
1334 End If;
1335
1336 /* assign burden cost rate */
1337 If p_override_burden_cost_rate is NOT NULL Then
1338 l_burden_cost_rate := p_override_burden_cost_rate;
1339 Else
1340 If NVL(P_quantity, 0) <> 0 Then
1341 If l_burden_cost = l_txn_raw_cost Then
1342 l_burden_cost_rate := x_trxn_raw_cost_rate;
1343 Else
1344 l_burden_cost_rate := l_burden_cost / NVL(p_quantity,1) ;
1345 End If;
1346 Else
1347 l_burden_cost_rate := x_trxn_raw_cost_rate;
1348 End If;
1349 End If;
1350
1351 /* derive burden multiplier */
1352 If NVL(l_txn_raw_cost,0) <> 0 then
1353 l_burden_multiplier := (l_burden_cost-l_txn_raw_cost) / l_txn_raw_cost ;
1354 Else
1355 l_burden_multiplier := 0;
1356 End If;
1357
1358 ElsIf ( pa_cost1.check_proj_burdened(p_project_type,p_project_id) = 'Y' ) Then
1359
1360 l_stage := 'Calling PA_COST1.Get_burden_sch_details API';
1361 print_msg(l_debug_flag,l_stage);
1362 pa_cost1.Get_burden_sch_details
1363 (p_calling_mode => p_calling_mode
1364 ,p_exp_item_id => NULL
1365 ,p_trxn_type => NULL
1366 ,p_project_type => p_project_type
1367 ,p_project_id => p_project_id
1368 ,p_task_id => p_task_id
1369 ,p_exp_organization_id => l_rate_organization_id
1370 ,p_overide_organization_id => l_override_organization_id
1371 ,p_person_id => p_person_id
1372 ,p_expenditure_type => p_expenditure_type
1373 ,p_schedule_type => 'C'
1374 ,p_exp_item_date => p_exp_item_date
1375 ,p_trxn_curr_code => l_txn_curr_code
1376 ,p_burden_schedule_id => p_plan_cost_burden_sch_id
1377 ,x_schedule_id => l_burd_sch_id
1378 ,x_sch_revision_id => l_burd_sch_rev_id
1379 ,x_sch_fixed_date => l_burd_sch_fixed_date
1380 ,x_cost_base => l_burd_sch_cost_base
1381 ,x_cost_plus_structure => l_burd_sch_cp_structure
1382 ,x_compiled_set_id => l_burd_ind_compiled_set_id
1383 ,x_burden_multiplier => l_burden_multiplier
1384 ,x_return_status => l_return_status
1385 ,x_error_msg_code => l_msg_data
1386 );
1387 l_stage := 'After Get_Burdened_cost api return status['||l_return_status||']msgData['||l_msg_data||']' ;
1388 print_msg(l_debug_flag,l_stage);
1389
1390 If ( l_return_status <> 'S' OR l_burden_multiplier is NULL ) Then
1391 l_stage := 'Error while Calculating burden costs';
1392 x_burden_cost_rejection_code := substr(l_msg_data,1,30);
1393 Raise l_no_rate_found;
1394 End If;
1395
1396 /* Bug fix: 4240140 l_burden_cost := (l_txn_raw_cost * l_burden_multiplier) + l_txn_raw_cost ;
1397 If NVL(P_quantity, 0) <> 0 Then
1398 --assign raw cost rate to burden cost rate if burden cost is same as raw cost
1399 If l_burden_cost = l_txn_raw_cost Then
1400 l_burden_cost_rate := x_trxn_raw_cost_rate;
1401 Else
1402 l_burden_cost_rate := l_burden_cost / NVL(P_quantity, 1) ;
1403 End If;
1404 Else
1405 l_burden_cost_rate := x_trxn_raw_cost_rate;
1406 End If;
1407 */
1408 If (NVL(P_quantity, 0) <> 0 AND NVL(l_txn_raw_cost_rate,0) <> 0 ) Then
1409 l_burden_cost_rate := (l_txn_raw_cost_rate * l_burden_multiplier ) + l_txn_raw_cost_rate;
1410 l_burden_cost := pa_currency.round_trans_currency_amt1((P_quantity*l_burden_cost_rate),l_txn_curr_code);
1411 Else
1412 l_burden_cost := (l_txn_raw_cost * l_burden_multiplier) + l_txn_raw_cost ;
1413 If l_burden_cost = l_txn_raw_cost Then
1414 l_burden_cost_rate := l_txn_raw_cost_rate;
1415 Else
1416 l_burden_cost_rate := l_burden_cost / NVL(P_quantity, 1) ;
1417 End If;
1418 End If;
1419 x_burden_multiplier := l_burden_multiplier;
1420 x_cost_ind_compiled_set_id := l_burd_ind_compiled_set_id;
1421
1422 Else -- project type is not burdened
1423 --copy the raw cost to the burden costs
1424 l_stage := 'Copying raw costs to burden costs';
1425 print_msg(l_debug_flag,l_stage);
1426
1427 l_burden_cost := l_txn_raw_cost;
1428 l_burden_cost_rate := l_txn_raw_cost_rate;
1429 l_burden_multiplier := 0;
1430
1431 End IF;
1432
1433 End If; -- end of raw cost is not null
1434 --Assign values to the out variables
1435 x_trxn_burden_cost := l_burden_cost;
1436 x_trxn_burden_cost_rate := l_burden_cost_rate;
1437 x_burden_multiplier := NVL(l_burden_multiplier,0);
1438
1439 /* Raise invalid currency code when the derived rate sch currency code is different from
1440 * passed value override currency code
1441 */
1442 If p_override_trxn_curr_code is NOT NULL Then
1443 If p_override_trxn_curr_code <> NVL(l_txn_curr_code ,p_override_trxn_curr_code) Then
1444 l_stage := 'Invalid override currency is passed';
1445 print_msg(l_debug_flag,l_stage||'[costtxncur['||l_txn_curr_code||']OvrCur['||p_override_trxn_curr_code||']');
1446 Raise l_invalid_currency;
1447 End If;
1448 End if;
1449
1450 l_stage := 'End of Burden Calculation burdenCost['||x_trxn_burden_cost||']burdenCostrate['||x_trxn_burden_cost_rate||
1451 ']burdenMultiplier['||x_burden_multiplier||']' ;
1452 print_msg(l_debug_flag,l_stage);
1453 /* End of burden cost calculation */
1454
1455 x_error_msg_code := l_msg_data;
1456 x_return_status := l_return_status;
1457
1458 -- reset the error stack
1459 If l_debug_flag = 'Y' Then
1460 PA_DEBUG.reset_err_stack;
1461 End If;
1462
1463 EXCEPTION
1464 WHEN l_insufficient_parms THEN
1465 If NVL(p_calling_mode,'ACTUAL_RATES') = 'ACTUAL_RATES' Then
1466 If p_resource_class = 'PEOPLE' Then
1467 x_error_msg_code := 'PA_INVALID_ACT_PEOPLE_PARAMS';
1468 Elsif p_resource_class = 'EQUIPMENT' Then
1469 x_error_msg_code := 'PA_INVALID_ACT_EQUIP_PARAMS';
1470 Elsif p_resource_class = 'MATERIAL_ITEMS' Then
1471 x_error_msg_code := 'PA_INVALID_ACT_MAT_PARAMS';
1472 Elsif p_resource_class = 'FINANCIAL_ELEMENTS' Then
1473 x_error_msg_code := 'PA_INVALID_ACT_FIN_PARAMS';
1474 End If;
1475 Else
1476 If p_resource_class = 'PEOPLE' Then
1477 x_error_msg_code := 'PA_INVALID_PLAN_PEOPLE_PARAMS';
1478 Elsif p_resource_class = 'EQUIPMENT' Then
1479 x_error_msg_code := 'PA_INVALID_PLAN_EQUIP_PARAMS';
1480 Elsif p_resource_class = 'MATERIAL_ITEMS' Then
1481 x_error_msg_code := 'PA_INVALID_PLAN_MAT_PARAMS';
1482 Elsif p_resource_class = 'FINANCIAL_ELEMENTS' Then
1483 x_error_msg_code := 'PA_INVALID_PLAN_FIN_PARAMS';
1484 End If;
1485 End If;
1486 If x_error_msg_code is NULL Then
1487 x_error_msg_code := 'PA_COST1_INVALID_PARAMS';
1488 End If;
1489 x_return_status := 'E';
1490 print_msg(l_debug_flag,l_stage);
1491 If l_debug_flag = 'Y' Then
1492 PA_DEBUG.write_file('LOG',l_stage);
1493 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1494 PA_DEBUG.reset_err_stack;
1495 End If;
1496
1497 WHEN l_no_rate_found THEN
1498 x_error_msg_code := 'PA_FP_MISSING_RATE';
1499 x_return_status := 'E';
1500 print_msg(l_debug_flag,l_stage);
1501 If l_debug_flag = 'Y' Then
1502 PA_DEBUG.write_file('LOG',l_stage);
1503 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1504 PA_DEBUG.reset_err_stack;
1505 End If;
1506
1507 WHEN l_no_burdrate_found THEN
1508 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
1509 x_return_status := 'E';
1510 print_msg(l_debug_flag,l_stage);
1511 If l_debug_flag = 'Y' Then
1512 PA_DEBUG.write_file('LOG',l_stage);
1513 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1514 PA_DEBUG.reset_err_stack;
1515 End If;
1516
1517 WHEN l_invalid_override_attributes THEN
1518 x_error_msg_code := 'PA_INVALID_OVERRIDE_PARAM';
1519 x_return_status := 'E';
1520 print_msg(l_debug_flag,l_stage);
1521 If l_debug_flag = 'Y' Then
1522 PA_DEBUG.write_file('LOG',l_stage);
1523 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1524 PA_DEBUG.reset_err_stack;
1525 End If;
1526
1527 WHEN l_invalid_currency THEN
1528 x_error_msg_code := 'PA_INVALID_DENOM_CURRENCY';
1529 x_return_status := 'E';
1530 print_msg(l_debug_flag,l_stage);
1531 If l_debug_flag = 'Y' Then
1532 PA_DEBUG.write_file('LOG',l_stage);
1533 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1534 PA_DEBUG.reset_err_stack;
1535 End If;
1536
1537
1538 WHEN OTHERS THEN
1539 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
1540 x_return_status := 'E';
1541 x_error_msg_code := 'PA_FP_MISSING_RATE';
1542 Else
1543 x_return_status := 'U';
1544 x_error_msg_code := substr(SQLCODE||SQLERRM,1,30);
1545 End If;
1546 print_msg(l_debug_flag,l_stage);
1547 If l_debug_flag = 'Y' Then
1548 PA_DEBUG.write_file('LOG',l_stage);
1549 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']sqlcode['||sqlcode||']ErrMsg['||x_error_msg_code||']');
1550 PA_DEBUG.reset_err_stack;
1551 End If;
1552
1553 END Get_Plan_Actual_Cost_Rates;
1554
1555
1556 PROCEDURE Get_Non_Labor_raw_cost
1557 (p_project_id IN NUMBER
1558 ,p_task_id IN NUMBER
1559 ,p_non_labor_resource IN VARCHAR2
1560 ,p_nlr_organization_id IN NUMBER
1561 ,p_expenditure_type IN VARCHAR2
1562 ,p_exp_item_date IN DATE
1563 ,p_override_organization_id IN NUMBER
1564 ,p_quantity IN NUMBER
1565 ,p_org_id IN NUMBER
1566 ,p_nlr_schedule_id IN NUMBER
1567 ,p_nlr_trxn_cost_rate IN NUMBER DEFAULT NULL
1568 ,p_nlr_trxn_raw_cost IN NUMBER DEFAULT NULL
1569 ,p_nlr_trxn_currency_code IN VARCHAR2 DEFAULT NULL
1570 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
1571 ,x_trxn_raw_cost OUT NOCOPY NUMBER
1572 ,x_txn_currency_code OUT NOCOPY VARCHAR2
1573 ,x_return_status OUT NOCOPY VARCHAR2
1574 ,x_error_msg_code OUT NOCOPY VARCHAR2
1575 ) IS
1576
1577 l_return_status varchar2(1000) := 'S';
1578 l_msg_data varchar2(1000);
1579 l_msg_count Number;
1580 l_stage varchar2(1000);
1581 l_debug_flag varchar2(10);
1582 l_exp_cost_rate_flag varchar2(100);
1583 l_nlr_raw_cost Number;
1584 l_nlr_raw_cost_rate Number;
1585 l_nlr_txn_curr_code varchar2(100);
1586 l_cost_rate_curr_code varchar2(100);
1587
1588
1589 Cursor cur_nlr_sch_details IS
1590 SELECT sch.rate_sch_currency_code
1591 ,rates.rate
1592 FROM pa_std_bill_rate_schedules_all sch
1593 ,pa_bill_rates_all rates
1594 WHERE sch.bill_rate_sch_id = p_nlr_schedule_id
1595 AND sch.schedule_type = 'NON-LABOR'
1596 AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
1597 AND rates.expenditure_type = p_expenditure_type
1598 AND ( rates.non_labor_resource is NULL
1599 OR rates.non_labor_resource = p_non_labor_resource
1600 )
1601 AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
1602 and trunc(nvl(rates.end_date_active,p_exp_item_date))
1603 /*Bug fix:3793618 This is to ensure that records with NLR and Exp combo orders first */
1604 ORDER BY decode(rates.non_labor_resource,p_non_labor_resource,0,1),rates.expenditure_type ;
1605
1606 BEGIN
1607 --- Initialize the error statck
1608 If g_debug_flag is NULL Then
1609 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
1610 g_debug_flag := NVL(g_debug_flag, 'N');
1611 End If;
1612 l_debug_flag := NVL(g_debug_flag,'N');
1613 IF l_debug_flag = 'Y' Then
1614 PA_DEBUG.init_err_stack ('PA_COST1.Get_Non_Labor_raw_cost');
1615 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1616 ,x_write_file => 'LOG'
1617 ,x_debug_mode => l_debug_flag
1618 );
1619 End If;
1620 --Initialize the out varibales
1621 l_return_status := 'S';
1622 l_msg_data := Null;
1623 l_nlr_txn_curr_code := p_nlr_trxn_currency_code;
1624 l_cost_rate_curr_code := Null;
1625
1626 l_exp_cost_rate_flag := check_expCostRateFlag(p_expenditure_type);
1627
1628 l_stage := 'Inside PA_COST1.Get_Non_Labor_raw_cost API CostRateFlag['||l_exp_cost_rate_flag||']' ;
1629 print_msg(l_debug_flag,l_stage);
1630
1631 /* get the currency code */
1632 l_stage := 'Getting currency code the for the given OU';
1633 print_msg(l_debug_flag,l_stage);
1634 l_cost_rate_curr_code := Get_curr_code(p_org_id => p_org_id );
1635
1636 If l_cost_rate_curr_code is NULL Then
1637 l_stage :='Currency not found for the OU';
1638 print_msg(l_debug_flag,l_stage);
1639 l_return_status := 'E';
1640 End If;
1641
1642 IF l_exp_cost_rate_flag = 'N' Then
1643 l_nlr_raw_cost_rate := 1;
1644
1645 ELSE
1646 -- check the cost rates available at non labor resource level if no rate found then
1647 -- get the rates at expendityre type level, if no rate found then return error
1648 BEGIN
1649
1650 /** commented as NLR schedule is not used to derive cost rates
1651 If p_nlr_schedule_id is NOT NULL Then
1652 Open cur_nlr_sch_details;
1653 Fetch cur_nlr_sch_details
1654 INTO l_cost_rate_curr_code
1655 ,l_nlr_raw_cost_rate;
1656 Close cur_nlr_sch_details;
1657 End If;
1658 **/
1659
1660 IF l_cost_rate_curr_code IS NOT NULL Then
1661
1662 /* bug fix: 3819799 changed the order of the table. Now first hit the PA_Expenditure_Cost_Rates_all instead of
1663 * PA_Expenditure_Types. This will avoid the following issue
1664 * PA_USAGE_COST_RATE_OVR_ALL is being referenced more than 3 times.
1665 * Single-row table count exceeds 3 for PA_USAGE_COST_RATE_OVR_ALL.
1666 */
1667 l_stage := 'Getting Cost rates from Usage Overrides';
1668 print_msg(l_debug_flag,l_stage);
1669 SELECT R.Rate
1670 INTO l_nlr_raw_cost_rate
1671 FROM PA_Expenditure_Types T,
1672 PA_Usage_Cost_Rate_Ovr_all R
1673 WHERE T.Expenditure_type = R.Expenditure_type
1674 AND T.Cost_Rate_Flag = 'Y'
1675 AND R.Expenditure_type = p_expenditure_type
1676 AND R.Non_Labor_Resource = p_Non_Labor_Resource
1677 AND R.Organization_Id = NVL(p_nlr_organization_id,p_override_organization_id)
1678 AND NVL(R.org_id,-99) = NVL(p_org_id,-99)
1679 AND trunc(p_exp_item_date)
1680 BETWEEN R.Start_Date_Active
1681 AND NVL(R.End_Date_Active, p_exp_item_date);
1682
1683 END If;
1684
1685 l_stage := 'costRateCur['||l_cost_rate_curr_code||']RawCostRate['||l_nlr_raw_cost_rate||']' ;
1686 print_msg(l_debug_flag,l_stage);
1687
1688 EXCEPTION
1689
1690 WHEN NO_DATA_FOUND THEN
1691
1692 BEGIN
1693 l_stage := 'Getting Cost rates from expenditure type cost rates';
1694 print_msg(l_debug_flag,l_stage);
1695 l_nlr_raw_cost_rate := GetExpTypeCostRate
1696 (p_exp_type => p_expenditure_type
1697 ,p_exp_item_date => p_exp_item_date
1698 ,p_org_id => p_org_id
1699 );
1700 l_stage := 'costRateCur['||l_cost_rate_curr_code||']RawCostRate['||l_nlr_raw_cost_rate||']' ;
1701 print_msg(l_debug_flag,l_stage);
1702
1703 If l_nlr_raw_cost_rate is NULL Then
1704 l_msg_data := 'PA_NLR_NO_RATE_FOUND';
1705 l_return_status := 'E';
1706 l_stage := 'No Rates found for Non-labor resources';
1707 End If;
1708 END;
1709
1710 WHEN OTHERS THEN
1711 l_return_status := 'U';
1712 l_msg_data := SQLCODE||SQLERRM;
1713 END;
1714
1715 END IF;
1716
1717 If p_nlr_trxn_raw_cost is NOT NULL Then
1718 l_nlr_raw_cost := p_nlr_trxn_raw_cost;
1719 ElsIF l_cost_rate_curr_code is NOT NULL Then
1720 If l_nlr_raw_cost_rate is NOT NULL Then
1721 l_nlr_raw_cost := pa_currency.round_trans_currency_amt1
1722 (l_nlr_raw_cost_rate * NVL(p_quantity,0), l_cost_rate_curr_code );
1723 End If;
1724
1725 End If;
1726 -- Assign the output variables with the derived values
1727 x_return_status := l_return_status;
1728 x_error_msg_code := l_msg_data;
1729 x_trxn_raw_cost := l_nlr_raw_cost;
1730 x_trxn_raw_cost_rate:= l_nlr_raw_cost_rate;
1731 x_txn_currency_code := NVL(l_cost_rate_curr_code,l_nlr_txn_curr_code);
1732
1733 -- reset the error stack
1734 If l_debug_flag = 'Y' Then
1735 PA_DEBUG.reset_err_stack;
1736 End If;
1737
1738 EXCEPTION
1739
1740 WHEN OTHERS THEN
1741 print_msg(l_debug_flag,l_stage);
1742 If l_debug_flag = 'Y' Then
1743 PA_DEBUG.write_file('LOG',l_stage);
1744 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']' );
1745 PA_DEBUG.reset_err_stack;
1746 End If;
1747 END Get_Non_Labor_raw_cost;
1748
1749 /* This is a wrapper api to derive compiled set id and burden multiplier
1750 * Which in turn makes calls to pa_cost_plus package
1751 * p_calling_mode IN required for PLAN_RATES
1752 * p_burden_schedule_id IN required for PLAN_RATES
1753 */
1754 PROCEDURE Get_burden_sch_details
1755 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
1756 ,p_exp_item_id IN NUMBER
1757 ,p_trxn_type IN VARCHAR2
1758 ,p_project_type IN VARCHAR2
1759 ,p_project_id IN NUMBER
1760 ,p_task_id IN NUMBER
1761 ,p_exp_organization_id IN NUMBER
1762 /* bug fix:4232181 Derive organization override for burden calculate */
1763 ,p_overide_organization_id IN NUMBER DEFAULT NULL
1764 ,p_person_id IN NUMBER DEFAULT NULL
1765 /* end of bug fix:4232181 */
1766 ,p_expenditure_type IN VARCHAR2
1767 ,p_schedule_type IN VARCHAR2 DEFAULT 'C'
1768 ,p_exp_item_date IN DATE
1769 ,p_trxn_curr_code IN VARCHAR2
1770 ,p_burden_schedule_id IN NUMBER DEFAULT NULL
1771 ,x_schedule_id OUT NOCOPY NUMBER
1772 ,x_sch_revision_id OUT NOCOPY NUMBER
1773 ,x_sch_fixed_date OUT NOCOPY DATE
1774 ,x_cost_base OUT NOCOPY VARCHAR2
1775 ,x_cost_plus_structure OUT NOCOPY VARCHAR2
1776 ,x_compiled_set_id OUT NOCOPY NUMBER
1777 ,x_burden_multiplier OUT NOCOPY NUMBER
1778 ,x_return_status OUT NOCOPY VARCHAR2
1779 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
1780
1781 l_exp_item_id NUMBER := p_exp_item_id;
1782 l_trxn_type VARCHAR2(150) := p_trxn_type;
1783 l_project_type VARCHAR2(150) := p_project_type;
1784 l_project_id NUMBER := p_project_id;
1785 l_task_id NUMBER := p_task_id;
1786 l_exp_organization_id NUMBER := p_exp_organization_id;
1787 l_overide_organization_id NUMBER := p_overide_organization_id;
1788 l_person_id NUMBER := p_person_id;
1789 l_schedule_type VARCHAR2(150) := p_schedule_type;
1790 l_exp_item_date DATE := p_exp_item_date;
1791 l_trxn_curr_code VARCHAR2(150) := p_trxn_curr_code;
1792 l_expenditure_type VARCHAR2(150) := p_expenditure_type;
1793 l_schedule_id NUMBER := NULL;
1794 l_sch_revision_id NUMBER := NULL;
1795 l_sch_fixed_date DATE := NULL;
1796 l_cost_base VARCHAR2(150) := NULL;
1797 l_cost_plus_structure VARCHAR2(150) := NULL;
1798 l_compiled_set_id NUMBER := NULL;
1799 l_burden_multiplier NUMBER := NULL;
1800 l_return_status VARCHAR2(100) := 'S';
1801 l_error_msg_code VARCHAR2(1000) := NULL;
1802 l_stage VARCHAR2(1000) := NULL;
1803 l_err_code VARCHAR2(1000) := NULL;
1804 l_debug_flag VARCHAR2(100) := 'N';
1805 l_status VARCHAR2(100) := NULL;
1806 l_msg_count NUMBER := 0;
1807
1808 L_INVALID_SCHEDULE EXCEPTION;
1809 L_NO_MULTIPLIER EXCEPTION;
1810 L_NO_COMPILED_SET EXCEPTION;
1811 L_INVALID_ERROR EXCEPTION;
1812 L_NO_COST_BASE EXCEPTION;
1813
1814 BEGIN
1815
1816 --- Initialize the error statck
1817 If g_debug_flag is NULL Then
1818 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
1819 g_debug_flag := NVL(g_debug_flag, 'N');
1820 End If;
1821 l_debug_flag := NVL(g_debug_flag,'N');
1822 IF l_debug_flag = 'Y' Then
1823 PA_DEBUG.init_err_stack ('PA_COST1.Get_burden_sch_details');
1824 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1825 ,x_write_file => 'LOG'
1826 ,x_debug_mode => l_debug_flag
1827 );
1828 End If;
1829 l_return_status := 'S';
1830 l_error_msg_code := NULL;
1831 x_return_status := 'S';
1832 x_error_msg_code := NULL;
1833
1834
1835 l_stage := 'Inside Get_burden_sch_details params ProjId['||l_project_id||']ProjType['||l_project_type||
1836 ']TaskId['||l_task_id||']expOrgId['||l_exp_organization_id||']SchType['||l_schedule_type||
1837 ']ExpType['||l_expenditure_type||']CurrCode['||l_trxn_curr_code||']EiDate['||l_exp_item_date||
1838 ']BurdenSchId['||p_burden_schedule_id||']CallingMode['||p_calling_mode||']' ;
1839 print_msg(l_debug_flag,l_stage);
1840
1841 If ( l_schedule_type is NOT NULL and check_proj_burdened (p_project_type ,p_project_id ) = 'Y') Then
1842 --call the api to get the schedule info
1843 l_stage := 'Calling pa_cost_plus.find_rate_sch_rev_id to get sch Id and RevId';
1844 print_msg(l_debug_flag,l_stage);
1845 If l_schedule_type = 'COST' Then
1846 l_schedule_type := 'C';
1847 Elsif l_schedule_type = 'REVENUE' Then
1848 l_schedule_type := 'R';
1849 Elsif l_schedule_type is NULL Then
1850 l_schedule_type := 'C';
1851 End If;
1852
1853 /* Derive the burden schedule revision based on the calling mode
1854 * if calling mode is PLAN_RATES then schedule is already passed
1855 * else schedule id should be derived based the given task or project */
1856 If p_calling_mode = 'ACTUAL_RATES' Then
1857
1858 IF (l_task_id is NULL OR is_workPlan_Task(l_project_id,l_task_id) = 'Y') Then
1859 -- For Task Effort calculation the task id will be passed NULL so
1860 -- derive the schedule for the given project
1861 get_projLevel_BurdSchds
1862 (p_project_id => l_project_id
1863 ,p_task_id => NULL
1864 ,p_exp_item_date => l_exp_item_date
1865 ,p_burden_sch_id => p_burden_schedule_id
1866 ,x_burden_sch_id => l_schedule_id
1867 ,x_burden_sch_revision_id => l_sch_revision_id
1868 ,x_status => l_status
1869 );
1870
1871 ELSE -- get details for the given task
1872
1873 pa_cost_plus.find_rate_sch_rev_id
1874 (l_exp_item_id --transaction_id
1875 ,l_trxn_type --transaction_type
1876 ,l_task_id --t_id
1877 ,l_schedule_type --schedule_type
1878 ,l_exp_item_date --exp_item_date
1879 ,l_schedule_id --x_sch_id
1880 ,l_sch_revision_id --x_rate_sch_rev_id
1881 ,l_sch_fixed_date --x_sch_fixed_date
1882 ,l_status --x_status
1883 ,l_error_msg_code --x_stage
1884 );
1885
1886 END IF;
1887
1888 If l_status <> 0 Then
1889
1890 l_stage := 'No Schedule or Revision found';
1891 print_msg(l_debug_flag,l_stage);
1892 Raise l_invalid_schedule;
1893 End If;
1894
1895 ElsIf p_calling_mode = 'PLAN_RATES' Then
1896 /* get the schedule revision id for the given schedule
1897 * For Task Effort calculation the task id will be passed NULL so
1898 * derive the schedule for the given project
1899 */
1900 l_schedule_id := p_burden_schedule_id;
1901 get_projLevel_BurdSchds
1902 (p_project_id => l_project_id
1903 ,p_task_id => l_task_id
1904 ,p_exp_item_date => l_exp_item_date
1905 ,p_burden_sch_id => p_burden_schedule_id
1906 ,x_burden_sch_id => l_schedule_id
1907 ,x_burden_sch_revision_id => l_sch_revision_id
1908 ,x_status => l_status
1909 );
1910 If l_status <> 0 Then
1911 l_stage := 'No Schedule Revision found nor Compiled for Planning burden Rate Schedule';
1912 print_msg(l_debug_flag,l_stage);
1913 Raise l_invalid_schedule;
1914 End If;
1915
1916 End If;
1917
1918 If l_sch_revision_id is NOT NULL Then
1919 l_stage := 'Calling pa_cost_plus.get_cost_plus_structure api';
1920 print_msg(l_debug_flag,l_stage);
1921 pa_cost_plus.get_cost_plus_structure
1922 (l_sch_revision_id
1923 ,l_cost_plus_structure
1924 ,l_status
1925 ,l_error_msg_code );
1926
1927 If l_status <> 0 Then
1928 l_stage := 'No Cost Plus Structure';
1929 print_msg(l_debug_flag,l_stage);
1930 Raise L_INVALID_ERROR;
1931 End If;
1932 End If;
1933
1934 If l_cost_plus_structure is NOT NULL Then
1935 l_stage := 'Calling pa_cost_plus.get_cost_base api';
1936 print_msg(l_debug_flag,l_stage);
1937 pa_cost_plus.get_cost_base
1938 (l_expenditure_type
1939 ,l_cost_plus_structure
1940 ,l_cost_base
1941 ,l_status
1942 ,l_error_msg_code );
1943
1944 If l_status <> 0 Then
1945 l_stage := 'No Cost base found Status['||l_status||']';
1946 print_msg(l_debug_flag,l_stage);
1947 Raise L_NO_COST_BASE;
1948 End If;
1949
1950
1951 End If;
1952
1953 If l_cost_base is NOT NULL Then
1954 /* Bug fix:4232181 Get the override organization Id from the project level org overrides */
1955 /* sent mail to anders, if its ok to call this for all resource classes once receiving the responce
1956 * the comment has to be opened
1957 IF l_overide_organization_id is NULL Then
1958 l_stage := 'Calling pa_cost.Override_exp_organization api From Burden sch api';
1959 print_msg(l_debug_flag,l_stage);
1960 pa_cost.Override_exp_organization
1961 (P_item_date => l_exp_item_date
1962 ,P_person_id => l_person_id
1963 ,P_project_id => l_project_id
1964 ,P_incurred_by_organz_id => l_exp_organization_id
1965 ,P_Expenditure_type => l_expenditure_type
1966 ,X_overr_to_organization_id => l_overide_organization_id
1967 ,X_return_status => l_return_status
1968 ,X_msg_count => l_msg_count
1969 ,X_msg_data => l_error_msg_code
1970 );
1971 l_stage := 'Return status of pa_cost.Override_exp_organization ['||l_return_status||']';
1972 l_stage := l_stage||']msgData['||l_error_msg_code||']OverideOrg['||l_overide_organization_id||']' ;
1973 print_msg(l_debug_flag,l_stage);
1974 End If;
1975 **/
1976 l_stage := 'Calling pa_cost_plus.get_compiled_set_id api';
1977 print_msg(l_debug_flag,l_stage);
1978 pa_cost_plus.get_compiled_set_id
1979 (l_sch_revision_id
1980 ,NVL(l_overide_organization_id,l_exp_organization_id)
1981 ,l_cost_base
1982 ,l_compiled_set_id
1983 ,l_status
1984 ,l_error_msg_code );
1985
1986 If l_status <> 0 Then
1987 l_stage := 'No Cost Ind Compiled SetId exists';
1988 print_msg(l_debug_flag,l_stage);
1989 Raise L_NO_COMPILED_SET;
1990 End If;
1991 End If;
1992
1993 IF l_compiled_set_id is NOT NULL Then
1994 l_stage := 'Calling pa_cost_plus.get_compiled_multiplier api';
1995 print_msg(l_debug_flag,l_stage);
1996 pa_cost_plus.get_compiled_multiplier
1997 (NVL(l_overide_organization_id,l_exp_organization_id)
1998 ,l_cost_base
1999 ,l_sch_revision_id
2000 ,l_burden_multiplier
2001 ,l_status
2002 ,l_error_msg_code );
2003
2004 If l_status <> 0 Then
2005 l_stage := 'No Compiled Multiplier exists';
2006 print_msg(l_debug_flag,l_stage);
2007 Raise L_NO_MULTIPLIER;
2008 End If;
2009 End If;
2010 End If; --end of task id not null
2011
2012 --Assign the values to out params
2013 x_schedule_id := l_schedule_id;
2014 x_sch_revision_id := l_sch_revision_id;
2015 x_sch_fixed_date := l_sch_fixed_date;
2016 x_cost_base := l_cost_base;
2017 x_cost_plus_structure := l_cost_plus_structure;
2018 x_compiled_set_id := l_compiled_set_id;
2019 x_burden_multiplier := l_burden_multiplier;
2020 x_return_status := NVL(l_return_status,'S');
2021 x_error_msg_code := substr(l_error_msg_code,1,30);
2022
2023 l_stage := 'Out Param Values SchId['||x_schedule_id||']SchRev['||x_sch_revision_id||']Schdate['||x_sch_fixed_date||
2024 ']Costbase['||x_cost_base||']CPStruc['||x_cost_plus_structure||']Compilset['||x_compiled_set_id||
2025 ']BurdMulti['||x_burden_multiplier||']retSts['||x_return_status||']ErrMsg['||x_error_msg_code||']' ;
2026 print_msg(l_debug_flag,l_stage);
2027
2028 --reset error stack
2029 If l_debug_flag = 'Y' Then
2030 PA_DEBUG.reset_err_stack;
2031 End If;
2032
2033 EXCEPTION
2034 WHEN l_invalid_schedule Then
2035 x_return_status := 'E';
2036 x_error_msg_code := 'PA_FCST_INVL_BURDEN_SCH_REV_ID';
2037 If l_debug_flag = 'Y' Then
2038 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2039 PA_DEBUG.write_file('LOG',l_stage);
2040 PA_DEBUG.reset_err_stack;
2041 End If;
2042
2043 WHEN l_no_cost_base Then
2044 If l_status = 100 Then
2045 -- ie. expenditure type is not part of the burdening
2046 -- so set the return status to success
2047 x_return_status := 'S';
2048 x_error_msg_code := NULL;
2049 x_burden_multiplier := 0;
2050 Else
2051 --ie. some unexpected error happened
2052 x_return_status := 'E';
2053 x_error_msg_code := 'PA_FCST_NO_COST_BASE';
2054
2055 End If;
2056 If l_debug_flag = 'Y' Then
2057 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2058 PA_DEBUG.write_file('LOG',l_stage);
2059 PA_DEBUG.reset_err_stack;
2060 End If;
2061
2062 WHEN l_no_compiled_set Then
2063 If p_exp_organization_id is NULL Then
2064 -- ie. expenditure organization id is not passed then
2065 -- set the multiplier as zero so that burdened cost = raw cost
2066 x_return_status := 'S';
2067 x_error_msg_code := NULL;
2068 x_burden_multiplier := 0;
2069 Else
2070 x_return_status := 'E';
2071 x_error_msg_code := 'PA_NO_COMPILED_SET_ID';
2072 End If;
2073 If l_debug_flag = 'Y' Then
2074 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2075 PA_DEBUG.write_file('LOG',l_stage);
2076 PA_DEBUG.reset_err_stack;
2077 End If;
2078
2079
2080 WHEN l_no_multiplier Then
2081 x_return_status := 'E';
2082 x_error_msg_code := 'PA_FCST_NO_COMPILED_MULTI';
2083 If l_debug_flag = 'Y' Then
2084 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2085 PA_DEBUG.write_file('LOG',l_stage);
2086 PA_DEBUG.reset_err_stack;
2087 End If;
2088
2089 WHEN l_invalid_error Then
2090 x_return_status := 'E';
2091 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
2092 If l_debug_flag = 'Y' Then
2093 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2094 PA_DEBUG.write_file('LOG',l_stage);
2095 PA_DEBUG.reset_err_stack;
2096 End If;
2097
2098 WHEN OTHERS THEN
2099 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
2100 x_return_status := 'E';
2101 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
2102 Else
2103 x_return_status := 'U';
2104 x_error_msg_code := substr(SQLCODE||SQLERRM,1,30);
2105 End If;
2106 If l_debug_flag = 'Y' Then
2107 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2108 PA_DEBUG.write_file('LOG',l_stage);
2109 PA_DEBUG.reset_err_stack;
2110 End If;
2111
2112
2113 END Get_burden_sch_details;
2114
2115 /* This is an internal API which will be called from Convert_COSTto PC and PFC api
2116 * this api does the calculation for amount conversion based on the planning conversion
2117 * attributes
2118 */
2119 PROCEDURE Convert_amounts
2120 (p_calling_mode IN VARCHAR2 DEFAULT 'PC'
2121 ,p_txn_raw_cost IN NUMBER
2122 ,p_txn_burden_cost IN NUMBER
2123 ,p_txn_quantity IN NUMBER
2124 ,p_Conversion_Date IN DATE
2125 ,p_From_curr_code IN VARCHAR2
2126 ,p_To_curr_code IN VARCHAR2
2127 ,p_To_Curr_Rate_Type IN VARCHAR2
2128 ,p_To_Curr_Exchange_Rate IN NUMBER
2129 ,x_To_Curr_raw_cost OUT NOCOPY NUMBER
2130 ,x_To_Curr_raw_cost_rate OUT NOCOPY NUMBER
2131 ,x_To_Curr_burden_cost OUT NOCOPY NUMBER
2132 ,x_To_Curr_burden_cost_rate OUT NOCOPY NUMBER
2133 ,x_To_Curr_Exchange_Rate OUT NOCOPY NUMBER
2134 ,x_return_status OUT NOCOPY VARCHAR2
2135 ,x_error_msg_code OUT NOCOPY VARCHAR2
2136 ) IS
2137
2138 l_denominator Number;
2139 l_numerator Number ;
2140 l_rate Number ;
2141 l_calling_mode Varchar2(100) := NVL(p_calling_mode,'PC');
2142 l_usrRateAllowed Varchar2(100);
2143 INVALID_CURRENCY Exception;
2144 NO_RATE Exception;
2145
2146 BEGIN
2147 -- Initialize the out variables
2148 x_return_status := 'S';
2149 x_error_msg_code := NULL;
2150
2151 If p_From_curr_code = p_To_curr_code Then
2152 x_To_Curr_raw_cost := p_txn_raw_cost;
2153 x_To_Curr_burden_cost := p_txn_burden_cost;
2154 x_To_Curr_Exchange_Rate := p_To_Curr_Exchange_Rate ;
2155 If NVL(p_txn_quantity,0) <> 0 Then
2156 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2157 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2158 Else
2159 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2160 If NVL(p_txn_burden_cost,0) <> 0 Then
2161 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2162 End If;
2163 End If;
2164 Else
2165 If p_To_Curr_Rate_Type = 'User' Then
2166 -- check if the user rate type is allowed for this currency
2167 l_usrRateAllowed := pa_multi_currency.is_user_rate_type_allowed
2168 (P_from_currency => p_From_curr_code
2169 ,P_to_currency => p_To_curr_code
2170 ,P_conversion_date => p_Conversion_Date );
2171 If NVL(l_usrRateAllowed,'N') = 'Y' Then
2172 If p_To_Curr_Exchange_Rate is NOT NULL Then
2173
2174 x_To_Curr_raw_cost := pa_currency.round_trans_currency_amt1
2175 (p_txn_raw_cost * NVL(p_To_Curr_Exchange_Rate,1),P_to_curr_code);
2176 x_To_Curr_burden_cost := pa_currency.round_trans_currency_amt1
2177 (p_txn_burden_cost * NVL(p_To_Curr_Exchange_Rate,1),P_to_curr_code);
2178 x_To_Curr_Exchange_Rate := p_To_Curr_Exchange_Rate ;
2179
2180 If NVL(p_txn_quantity,0) <> 0 Then
2181 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2182 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2183 Else
2184 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2185 If NVL(p_txn_burden_cost,0) <> 0 Then
2186 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2187 End If;
2188 End If;
2189
2190
2191 Else
2192 x_return_status := 'E';
2193 If l_calling_mode = 'PC' Then
2194 x_error_msg_code := 'PA_FP_PJ_COST_RATE_NOT_DEFINED';
2195 Else
2196 x_error_msg_code := 'PA_FP_PF_COST_RATE_NOT_DEFINED';
2197 End If;
2198 End If;
2199
2200 Else -- user rate type is not allowed so error out
2201 x_return_status := 'E';
2202 If l_calling_mode = 'PC' Then
2203 x_error_msg_code := 'PA_FP_PJC_USR_RATE_NOT_ALLOWED';
2204 Else
2205 x_error_msg_code := 'PA_FP_PFC_USR_RATE_NOT_ALLOWED';
2206 End If;
2207
2208 End If; -- End of userRateAllowed
2209
2210 ELse
2211 -- Call GL conversion api to derive the exchagne rate
2212 BEGIN
2213 print_msg('Calling Gl_currency_api.get_triangulation_rate');
2214 Gl_currency_api.get_triangulation_rate (
2215 x_from_currency => p_From_curr_code
2216 ,x_to_currency => p_To_Curr_code
2217 ,x_conversion_date => p_Conversion_date
2218 ,x_conversion_type => p_To_Curr_rate_Type
2219 ,x_denominator => l_denominator
2220 ,x_numerator => l_numerator
2221 ,x_rate => l_rate
2222 );
2223 x_To_Curr_raw_cost := pa_currency.round_trans_currency_amt1
2224 (p_txn_raw_cost * NVL(l_rate,1),p_To_Curr_code);
2225 x_To_Curr_burden_cost := pa_currency.round_trans_currency_amt1
2226 (p_txn_burden_cost * NVL(l_rate,1),p_To_Curr_code);
2227 x_To_Curr_Exchange_Rate := l_rate ;
2228
2229 If NVL(p_txn_quantity,0) <> 0 Then
2230 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2231 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2232 Else
2233 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2234 If NVL(p_txn_burden_cost,0) <> 0 Then
2235 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2236 End If;
2237 End If;
2238
2239 EXCEPTION
2240 WHEN OTHERS then
2241 x_return_status := 'E';
2242 If ( l_denominator = -2 OR l_denominator = -2 ) Then
2243 x_error_msg_code := 'PA_FP_CURR_NOT_VALID';
2244 Else
2245 If l_calling_mode = 'PC' Then
2246 x_error_msg_code := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
2247 Else
2248 x_error_msg_code := 'PA_FP_NO_PF_EXCH_RATE_EXISTS';
2249 End If;
2250
2251 End If;
2252 END;
2253
2254 End If; -- end of User Type
2255
2256 End If; -- End of From Curr <> To Curr
2257
2258 Return;
2259
2260 END Convert_amounts;
2261
2262
2263
2264 /* This API converts the cost amount from transaction currency to
2265 * project and project functional currency based on the
2266 * planning transaction currency conversion attributes
2267 * NOTE: Please donot use this API for actual cost conversion
2268 */
2269 PROCEDURE Convert_COST_TO_PC_PFC
2270 (p_txn_raw_cost IN NUMBER
2271 ,p_txn_burden_cost IN NUMBER
2272 ,p_txn_quantity IN NUMBER
2273 ,p_txn_curr_code IN VARCHAR2
2274 ,p_txn_date IN DATE
2275 ,p_project_id IN NUMBER
2276 ,p_budget_Version_id IN NUMBER
2277 ,p_budget_Line_id IN NUMBER
2278 ,x_project_curr_code OUT NOCOPY VARCHAR2
2279 ,x_projfunc_curr_code OUT NOCOPY VARCHAR2
2280 ,x_proj_raw_cost OUT NOCOPY NUMBER
2281 ,x_proj_raw_cost_rate OUT NOCOPY NUMBER
2282 ,x_proj_burdened_cost OUT NOCOPY NUMBER
2283 ,x_proj_burdened_cost_rate OUT NOCOPY NUMBER
2284 ,x_projfunc_raw_cost OUT NOCOPY NUMBER
2285 ,x_projfunc_raw_cost_rate OUT NOCOPY NUMBER
2286 ,x_projfunc_burdened_cost OUT NOCOPY NUMBER
2287 ,x_projfunc_burdened_cost_rate OUT NOCOPY NUMBER
2288 ,x_return_status OUT NOCOPY VARCHAR2
2289 ,x_error_msg_code OUT NOCOPY VARCHAR2
2290 ) IS
2291 l_insufficient_parms EXCEPTION;
2292 l_No_budget_version EXCEPTION;
2293 l_return_status VARCHAR2(100);
2294 l_error_msg_code VARCHAR2(100);
2295 l_stage VARCHAR2(1000);
2296 l_debug_flag VARCHAR2(10);
2297 l_txn_curr_code VARCHAR2(100);
2298 l_project_id NUMBER;
2299 l_budget_version_id NUMBER;
2300 l_resource_assignment_id NUMBER;
2301 /* project attributes */
2302 l_project_curr_code VARCHAR2(100);
2303 l_project_rate_type VARCHAR2(100);
2304 l_project_rate_date_type VARCHAR2(100);
2305 l_project_rate_date DATE;
2306 l_project_exchange_rate NUMBER;
2307 /* project functional attributes */
2308 l_projfunc_curr_code VARCHAR2(100);
2309 l_projfunc_rate_type VARCHAR2(100);
2310 l_projfunc_rate_date_type VARCHAR2(100);
2311 l_projfunc_rate_date DATE;
2312 l_projfunc_exchange_rate NUMBER;
2313
2314 l_proj_raw_cost NUMBER;
2315 l_proj_raw_cost_rate NUMBER;
2316 l_proj_burden_cost NUMBER;
2317 l_proj_burden_cost_rate NUMBER;
2318 l_projfunc_raw_cost NUMBER;
2319 l_projfunc_raw_cost_rate NUMBER;
2320 l_projfunc_burden_cost NUMBER;
2321 l_projfunc_burden_cost_rate NUMBER;
2322
2323 CURSOR cur_currencyAttribs(lv_budget_version_id Number,lv_resource_assignment_id Number) IS
2324 SELECT pp.project_id
2325 ,bv.budget_version_id
2326 ,cur.txn_currency_code txn_currency_code
2327 /* budget line currency attributes selected for testing
2328 --,bl.project_currency_code bgl_project_curr_code
2329 --,bl.projfunc_currency_code bgl_projfunc_curr_code
2330 --,bl.project_cost_rate_type bgl_project_rate_type
2331 --,bl.project_cost_rate_date_type bgl_project_rate_date_type
2332 --,bl.start_date bgl_project_rate_date
2333 --,bl.project_cost_exchange_rate bgl_project_exchange_rate */
2334 /* -----------Project Currency conversion Atrributes -----------------------------------------*/
2335 ,NVL(bl.project_currency_code,pp.project_currency_code) project_currency_code
2336 ,NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type) project_rate_type
2337 ,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
2338 ,'User',NULL
2339 ,NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)) project_rate_date_type
2340 ,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
2341 ,'START_DATE', NVL(bl.start_date,p_txn_date)
2342 ,'END_DATE' , NVL(bl.end_date,p_txn_date)
2343 , NVL(bl.project_cost_rate_date,Nvl(fpo.project_cost_rate_date,p_txn_date))) project_rate_date
2344 ,decode(bl.project_cost_exchange_rate,NULL
2345 , decode(NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type)
2346 ,'User',cur.PROJECT_COST_EXCHANGE_RATE
2347 , null ),bl.project_cost_exchange_rate) project_exchange_rate
2348 /* -------------project functinal currency conversion attributes -------------------------------*/
2349 ,NVL(bl.projfunc_currency_code,pp.projfunc_currency_code) ProjFunc_currency_code
2350 ,NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type) projfunc_rate_type
2351 ,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
2352 ,'User',NULL
2353 ,NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)) projfunc_rate_date_type
2354 ,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
2355 ,'START_DATE', NVL(bl.start_date, p_txn_date)
2356 ,'END_DATE' , NVL(bl.end_date ,p_txn_date)
2357 , NVL(bl.projfunc_cost_rate_date,Nvl(fpo.projfunc_cost_rate_date,p_txn_date))) projfunc_rate_date
2358 ,decode(bl.projfunc_cost_exchange_rate,NULL
2359 , decode(NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type)
2360 ,'User',cur.PROJFUNC_COST_EXCHANGE_RATE
2361 , null),bl.projfunc_cost_exchange_rate) projfunc_exchange_rate
2362 from pa_budget_versions bv
2363 ,pa_proj_fp_options fpo
2364 ,pa_projects_all pp
2365 ,pa_fp_txn_currencies cur
2366 ,pa_budget_lines bl
2367 where bv.project_id = pp.project_id
2368 and fpo.project_id = pp.project_id
2369 and nvl(fpo.fin_plan_type_id,0) = nvl(bv.fin_plan_type_id,0)
2370 and fpo.fin_plan_version_id = bv.budget_version_id
2371 and bv.budget_version_id = cur.fin_plan_version_id
2372 and cur.txn_currency_code = p_txn_curr_code
2373 and pp.project_id = p_project_id
2374 and bv.budget_version_id = lv_budget_version_id
2375 and bv.budget_version_id = bl.budget_version_id (+)
2376 and ( (nvl(bl.resource_assignment_id,lv_resource_assignment_id) = lv_resource_assignment_id
2377 and trunc(p_txn_date) between trunc(bl.start_date) and nvl(bl.end_date,p_txn_date)
2378 and bl.txn_currency_code = p_txn_curr_code
2379 ) OR
2380 (NOT EXISTS
2381 (select null from pa_budget_lines bl1
2382 where bl1.budget_version_id = bv.budget_version_id
2383 and bl1.resource_assignment_id = lv_resource_assignment_id
2384 and trunc(p_txn_date) between trunc(bl1.start_date) and nvl(bl1.end_date,p_txn_date)
2385 and bl.txn_currency_code = p_txn_curr_code
2386 ))
2387 )
2388 order by bv.budget_version_id ;
2389
2390
2391 BEGIN
2392 --- Initialize the error statck
2393 If g_debug_flag is NULL Then
2394 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
2395 g_debug_flag := NVL(g_debug_flag, 'N');
2396 End If;
2397 l_debug_flag := NVL(g_debug_flag,'N');
2398 If l_debug_flag = 'Y' Then
2399 PA_DEBUG.init_err_stack ('PA_COST1.Convert_COST_TO_PC_PFC');
2400 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2401 ,x_write_file => 'LOG'
2402 ,x_debug_mode => l_debug_flag
2403 );
2404 End If;
2405
2406 l_return_status := 'S';
2407 l_error_msg_code := NULL;
2408 l_budget_version_id := p_budget_version_id;
2409 x_return_status := 'S';
2410 x_error_msg_code := NULL;
2411
2412 l_stage := 'Inside Convert_TxnTo_PV_PFC api:TxnCost['||p_txn_raw_cost||']TxnBdCost['||p_txn_burden_cost||
2413 ']TxnCurr['||p_txn_curr_code||']TxnDate['||p_txn_date||']ProjId['||p_project_id||']BdgtLine['||p_budget_Line_id||
2414 ']BudgetVersion['||l_budget_version_id||']Quantity['||p_txn_quantity||']';
2415 print_msg(l_debug_flag,l_stage);
2416
2417 /* Validate In Params */
2418 IF (( p_project_id is NULL or p_txn_curr_code is NULL or p_txn_date is NULL )
2419 or (p_budget_Line_id is NULL AND p_budget_version_id is NULL )
2420 ) Then
2421 Raise l_insufficient_parms;
2422 End If;
2423
2424 /* Get the Budget Version Id for the given Budget Line */
2425 Begin
2426 If l_budget_version_id is NULL Then
2427 l_stage := 'Executing sql to get Budget Version';
2428 SELECT bl.budget_version_id
2429 ,bl.resource_assignment_id
2430 INTO l_budget_version_id
2431 ,l_resource_assignment_id
2432 FROM pa_budget_lines bl
2433 WHERE bl.budget_line_id = p_budget_line_id;
2434 End If;
2435 Exception
2436 When No_data_found Then
2437 l_stage := 'No Budget Version Found for the given Budget Line['||p_budget_line_id||']' ;
2438 Raise l_No_budget_version;
2439 End;
2440
2441 IF ( NVL(p_txn_raw_cost,0) <> 0 OR NVL(p_txn_burden_cost,0) <> 0 ) Then
2442 l_stage := 'Opening Currency Attributes Cursor with BudgtVer['||l_budget_version_id||
2443 ']ResAssn['||l_resource_assignment_id||']';
2444 OPEN cur_currencyAttribs(l_budget_version_id,l_resource_assignment_id);
2445 FETCH cur_currencyAttribs INTO
2446 l_project_id
2447 ,l_budget_version_id
2448 ,l_txn_curr_code
2449 /* project attributes */
2450 ,l_project_curr_code
2451 ,l_project_rate_type
2452 ,l_project_rate_date_type
2453 ,l_project_rate_date
2454 ,l_project_exchange_rate
2455 /* project functional attributes */
2456 ,l_projfunc_curr_code
2457 ,l_projfunc_rate_type
2458 ,l_projfunc_rate_date_type
2459 ,l_projfunc_rate_date
2460 ,l_projfunc_exchange_rate ;
2461 IF cur_currencyAttribs%FOUND Then
2462 l_stage := 'CurrAttributes:ProjCur['||l_project_curr_code||']ProjRateType['||l_project_rate_type||
2463 ']RateDate['||l_project_rate_date||']ProjXchange['||l_project_exchange_rate||
2464 ']ProjFuncCur['||l_projfunc_curr_code||']PFCRateType['||l_projfunc_rate_type||
2465 ']PFCRateDate['||l_projfunc_rate_date||']PFCXchnge['||l_projfunc_exchange_rate||']' ;
2466 print_msg(l_debug_flag,l_stage);
2467
2468 -- Call the conversion api for PC Amounts
2469 l_stage := 'Calling Convert_amounts for PC';
2470 Convert_amounts
2471 (p_calling_mode => 'PC'
2472 ,p_txn_raw_cost => p_txn_raw_cost
2473 ,p_txn_burden_cost => p_txn_burden_cost
2474 ,p_txn_quantity => p_txn_quantity
2475 ,p_From_curr_code => p_txn_curr_code
2476 ,p_To_curr_code => l_project_curr_code
2477 ,p_To_Curr_Rate_Type => l_project_rate_type
2478 ,p_Conversion_Date => l_project_rate_date
2479 ,p_To_Curr_Exchange_Rate => l_project_exchange_rate
2480 ,x_To_Curr_raw_cost => l_proj_raw_cost
2481 ,x_To_Curr_raw_cost_rate => l_proj_raw_cost_rate
2482 ,x_To_Curr_burden_cost => l_proj_burden_cost
2483 ,x_To_Curr_burden_cost_rate => l_proj_burden_cost_rate
2484 ,x_To_Curr_Exchange_Rate => l_project_exchange_rate
2485 ,x_return_status => l_return_status
2486 ,x_error_msg_code => l_error_msg_code
2487 );
2488
2489 If l_return_status = 'S' Then
2490 l_stage := 'Calling Convert_amounts for PFC';
2491 -- Call the conversion api for PFC amounts
2492 Convert_amounts
2493 (p_calling_mode => 'PFC'
2494 ,p_txn_raw_cost => p_txn_raw_cost
2495 ,p_txn_burden_cost => p_txn_burden_cost
2496 ,p_txn_quantity => p_txn_quantity
2497 ,p_From_curr_code => p_txn_curr_code
2498 ,p_To_curr_code => l_projfunc_curr_code
2499 ,p_To_Curr_Rate_Type => l_projfunc_rate_type
2500 ,p_Conversion_Date => l_projfunc_rate_date
2501 ,p_To_Curr_Exchange_Rate => l_projfunc_exchange_rate
2502 ,x_To_Curr_raw_cost => l_projfunc_raw_cost
2503 ,x_To_Curr_raw_cost_rate => l_projfunc_raw_cost_rate
2504 ,x_To_Curr_burden_cost => l_projfunc_burden_cost
2505 ,x_To_Curr_burden_cost_rate => l_projfunc_burden_cost_rate
2506 ,x_To_Curr_Exchange_Rate => l_projfunc_exchange_rate
2507 ,x_return_status => l_return_status
2508 ,x_error_msg_code => l_error_msg_code
2509 );
2510
2511 End If;
2512
2513 Else
2514 print_msg(l_debug_flag,'Cursor / No Data Found for the Given params');
2515 END IF;
2516 CLOSE cur_currencyAttribs;
2517
2518 End If;
2519
2520
2521 -- Assign out variables
2522 x_return_status := l_return_status;
2523 x_error_msg_code := l_error_msg_code ;
2524 x_project_curr_code := l_project_curr_code;
2525 x_projfunc_curr_code := l_projfunc_curr_code;
2526 x_proj_raw_cost := l_proj_raw_cost;
2527 x_proj_raw_cost_rate := l_proj_raw_cost_rate;
2528 x_proj_burdened_cost := l_proj_burden_cost;
2529 x_proj_burdened_cost_rate := l_proj_burden_cost_rate;
2530 x_projfunc_raw_cost := l_projfunc_raw_cost;
2531 x_projfunc_raw_cost_rate := l_projfunc_raw_cost_rate;
2532 x_projfunc_burdened_cost := l_projfunc_burden_cost;
2533 x_projfunc_burdened_cost_rate := l_projfunc_burden_cost_rate;
2534
2535 l_stage := 'End of ConvertAmts: RetSts['||x_return_status||']Errmsg['||x_error_msg_code||
2536 ']ProjCur['||x_project_curr_code||']ProjFunc['||x_projfunc_curr_code||']ProjRaw['||x_proj_raw_cost||
2537 ']ProjBd['||x_proj_burdened_cost||']PFCRaw['||x_projfunc_raw_cost||']PFCBd['||x_projfunc_burdened_cost||
2538 ']ProjRawRate['||x_proj_raw_cost_rate||']' ;
2539
2540 print_msg(l_debug_flag,l_stage);
2541
2542
2543 -- Reset Err Stack
2544 If l_debug_flag = 'Y' Then
2545 PA_DEBUG.reset_err_stack;
2546 End If;
2547 EXCEPTION
2548 WHEN l_insufficient_parms THEN
2549 x_error_msg_code := 'PA_NO_BUDGET_VERSION';
2550 x_return_status := 'E';
2551 print_msg(l_debug_flag,l_stage);
2552 If l_debug_flag = 'Y' Then
2553 PA_DEBUG.write_file('LOG',l_stage);
2554 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']errCode['||l_error_msg_code||']' );
2555 PA_DEBUG.reset_err_stack;
2556 End If;
2557
2558 WHEN l_no_budget_version THEN
2559 x_error_msg_code := 'PA_INV_PARAM_PASSED';
2560 x_return_status := 'E';
2561 print_msg(l_debug_flag,l_stage);
2562 If l_debug_flag = 'Y' Then
2563 PA_DEBUG.write_file('LOG',l_stage);
2564 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']errCode['||l_error_msg_code||']' );
2565 PA_DEBUG.reset_err_stack;
2566 End If;
2567
2568 WHEN OTHERS THEN
2569 x_return_status := 'U';
2570 x_error_msg_code := SQLERRM||SQLCODE;
2571 print_msg(l_debug_flag,l_stage||x_error_msg_code);
2572 If l_debug_flag = 'Y' Then
2573 PA_DEBUG.write_file('LOG','ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2574 PA_DEBUG.write_file('LOG',l_stage);
2575 PA_DEBUG.reset_err_stack;
2576 End If;
2577
2578 END Convert_COST_TO_PC_PFC;
2579
2580 END PA_COST1;
[Home] [Help]