1 package body PA_COST1 as
2 -- $Header: PAXCSR1B.pls 120.9 2011/07/10 05:41:48 racheruv 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 ,p_resource_assignment_id IN NUMBER DEFAULT NULL /* bug 12374022 */
563 ,x_trxn_curr_code OUT NOCOPY VARCHAR2
564 ,x_trxn_raw_cost OUT NOCOPY NUMBER
565 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
566 ,x_trxn_burden_cost OUT NOCOPY NUMBER
567 ,x_trxn_burden_cost_rate OUT NOCOPY NUMBER
568 ,x_burden_multiplier OUT NOCOPY NUMBER
569 ,x_cost_ind_compiled_set_id OUT NOCOPY NUMBER
570 ,x_raw_cost_rejection_code OUT NOCOPY VARCHAR2
571 ,x_burden_cost_rejection_code OUT NOCOPY VARCHAR2
572 ,x_return_status OUT NOCOPY VARCHAR2
573 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
574
575 l_insufficient_parms EXCEPTION;
576 l_no_rate_found EXCEPTION;
577 l_no_burdrate_found EXCEPTION;
578 l_invalid_override_attributes EXCEPTION;
579 l_invalid_currency EXCEPTION;
580 l_cost_source Number := p_mfc_cost_source ;
581
582 l_stage varchar2(1000);
583 l_err_code varchar2(1000);
584 l_debug_flag varchar2(10);
585 l_msg_data varchar2(1000);
586 l_msg_count Number;
587 l_return_status varchar2(10);
588 l_job_id Number;
589 l_txn_curr_code varchar2(100);
590 l_txn_raw_cost Number;
591 l_txn_raw_cost_rate Number;
592 l_burden_cost Number;
593 l_burden_cost_rate Number;
594 l_burden_multiplier Number;
595 l_override_organization_id Number;
596 l_cost_rate_multiplier Number;
597 l_start_date_active Date;
598 l_end_date_active Date;
599 l_org_labor_sch_rule_id Number;
600 l_costing_rule Varchar2(100);
601 l_rate_sch_id Number;
602 l_acct_rate_type varchar2(100);
603 l_acct_rate_date_code varchar2(100);
604 l_acct_exch_rate Number;
605 l_ot_project_id Number;
606 l_ot_task_id Number;
607 l_api_version Number;
608 l_burd_sch_id Number;
609 l_burd_sch_rev_id Number;
610 l_burd_sch_fixed_date Date;
611 l_burd_sch_cost_base varchar2(150);
612 l_burd_sch_cp_structure varchar2(150);
613 l_burd_ind_compiled_set_id Number;
614 l_proj_flag varchar2(1000);
615 l_rate_organization_id Number;
616 l_markup_percent Number;
617 l_bill_rate_schedule_type varchar2(150);
618 l_bill_rate_sch_id Number;
619
620 /* This is to identify the planning transactions as LABOR, NON-LABOR or BOM transactions depending on the
621 * validation of the input params*/
622 l_trxn_type varchar2(100) := NULL;
623 l_INVENTORY_ASSET_FLAG VARCHAR2(1) := null; --Added for Bug 9830012
624
625 /* bug 12374022 .. start */
626 cursor c_get_format(p_rlmi_id number) is
627 select name
628 from pa_res_formats_vl
629 where res_format_id = (select res_format_id
630 from pa_resource_list_members rlm
631 where resource_list_member_id = p_rlmi_id);
632
633 l_format_name pa_res_formats_vl.name%TYPE;
634
635 cursor c_get_res_asgn is
636 select *
637 from pa_resource_assignments
638 where resource_assignment_id = p_resource_assignment_id;
639
640 l_asgn_rec c_get_res_asgn%ROWTYPE;
641
642 l_curr_indx number;
643 l_prev_indx number;
644
645 l_named_person_flag varchar2(1);
646 l_job_flag varchar2(1);
647 l_organization_flag varchar2(1);
648 l_team_role_flag varchar2(1);
649 /* bug 12374022 .. end */
650
651 BEGIN
652
653 --Initialize the out variables
654 l_job_id := p_job_id;
655 l_txn_curr_code := p_override_trxn_curr_code;
656 l_txn_raw_cost := p_override_trxn_raw_cost ;
657 l_txn_raw_cost_rate := p_override_trxn_cost_rate;
658 l_burden_cost := p_override_trxn_burden_cost ;
659 l_burden_cost_rate := p_override_burden_cost_rate;
660 l_burden_multiplier := NULL;
661 l_override_organization_id := p_override_organization_id;
662 l_cost_rate_multiplier := NUll;
663 l_msg_data := NULL;
664 l_msg_count := 0;
665 l_return_status := 'S';
666 x_raw_cost_rejection_code := Null;
667 x_burden_cost_rejection_code := Null;
668 x_error_msg_code := Null;
669 x_return_status := 'S';
670
671 --- Initialize the error statck
672 If g_debug_flag is NULL Then
673 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
674 g_debug_flag := NVL(g_debug_flag, 'N');
675 End If;
676 l_debug_flag := NVL(g_debug_flag,'N');
677 IF l_debug_flag = 'Y' Then
678 PA_DEBUG.init_err_stack ('PA_COST1.Get_Plan_Actual_Cost_Rates');
679 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
680 ,x_write_file => 'LOG'
681 ,x_debug_mode => l_debug_flag
682 );
683 End If;
684
685 /* Derive the Rate Organization Id from the params*/
686 l_rate_organization_id := NVL(p_override_organization_id,NVL(p_incurred_by_organization_id,p_nlr_organization_id));
687
688 l_stage := 'Inside PA_COST1.Get_Plan_Actual_Cost_Rates API';
689 l_stage := l_stage||' IN PARAMS mode['||p_calling_mode||']Proj Type['||p_project_type||
690 ']Proj Id['||p_project_id||']TaskId['||p_task_id||']TopTask['||p_top_task_id||
691 ']Ei Date['||p_Exp_item_date||']ExpType['||p_expenditure_type||
692 ']ResClass['||p_resource_class||']Personid['||p_person_id||']NLR['||p_non_labor_resource||
693 ']NLR Org['||p_NLR_organization_id||']ExpOU['||p_expenditure_OU||']ProjOU['||p_project_OU||
694 ']IncurrOrg['||p_incurred_by_organization_id||']OverrideOrg['||l_override_organization_id||
695 ']Qty['||p_Quantity||']InvItemId['||p_inventory_item_id||']BomRes['||p_BOM_resource_id||
696 ']ProjCostJobId['||p_job_id||']p_mfc_cost_type_id['||p_mfc_cost_type_id||
697 ']p_mfc_cost_source['||p_mfc_cost_source||']RateOrganzId['||l_rate_organization_id||
698 ']JobRateSch['||p_plan_cost_job_rate_sch_id||']EmpRateSch['||p_plan_cost_emp_rate_sch_id||
699 ']NlrRateSch['||p_plan_cost_nlr_rate_sch_id||']BurdRateSch['||p_plan_cost_burden_sch_id||']' ;
700 print_msg(l_debug_flag,l_stage);
701
702 l_stage := 'Override values override_trxn_curr_code['||p_override_trxn_curr_code||
703 ']overide Multi['||p_override_burden_cost_rate||']OverrideCostRate['||p_override_trxn_cost_rate||
704 ']overrideRawCost['||p_override_trxn_raw_cost||']OverrideBurdCost['||p_override_trxn_burden_cost||']' ;
705 print_msg(l_debug_flag,l_stage);
706
707
708 /* Validate the override parameters */
709 l_trxn_type := NULL;
710 If ( p_override_trxn_raw_cost is NOT NULL
711 and p_override_trxn_burden_cost is NOT NULL ) Then
712 -- Just return the control back to calling api
713 l_stage := 'Assigning override values to Out params';
714 print_msg(l_debug_flag,l_stage);
715 x_trxn_raw_cost := p_override_trxn_raw_cost;
716 x_trxn_burden_cost := p_override_trxn_burden_cost;
717 x_trxn_curr_code := p_override_trxn_curr_code;
718 x_trxn_raw_cost_rate := p_override_trxn_cost_rate;
719 x_trxn_burden_cost_rate := p_override_burden_cost_rate;
720 x_raw_cost_rejection_code := Null;
721 x_burden_cost_rejection_code := Null;
722 x_error_msg_code := Null;
723 x_return_status := 'S';
724 If l_debug_flag = 'Y' Then
725 PA_DEBUG.reset_err_stack;
726 End If;
727 RETURN;
728 ElsIf (p_override_trxn_cost_rate is NOT NULL OR p_override_trxn_raw_cost is NOT NULL ) Then
729 l_stage := 'Validating override params';
730 IF p_override_trxn_curr_code is NULL Then
731 l_stage := 'Validating override params No Override Currency';
732 print_msg(l_debug_flag,l_stage);
733 Raise l_invalid_override_attributes;
734 Else
735 l_txn_curr_code := p_override_trxn_curr_code;
736 -- if cost rate is null derive the cost rate based on quantity and amount
737 If p_override_trxn_cost_rate is NULL Then
738 If NVL(p_quantity,0) <> 0 Then
739 l_txn_raw_cost_rate := p_override_trxn_raw_cost / NVL(p_quantity,1);
740 Else
741 l_txn_raw_cost_rate := 1;
742 End IF;
743 Else
744 l_txn_raw_cost_rate := p_override_trxn_cost_rate;
745 End If;
746
747 -- if cost is null then derive the cost based on rate and quantity
748 If p_override_trxn_raw_cost is NULL Then
749 If NVL(p_quantity,0) <> 0 then
750 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
751 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code );
752 Else
753 l_txn_raw_cost := null;
754 End If;
755 Else
756 l_txn_raw_cost := p_override_trxn_raw_cost;
757 End If;
758 l_trxn_type := 'BURDEN';
759 End If;
760 End If;
761
762 /* Based on the resource class and input params set the transaction type as one of the following values
763 * LABOR RATE -- for resource class is People and person or job is not null
764 * BOM RESOURCE RATE -- for resource class is people and bom resource id isnot null
765 * EXP TYPE RATE -- for resource class is people and financial category
766 * NON LABOR RESOURCE RATE -- for resource class equipment
767 * BOM EQUIPMENT RATE -- for resource class equiment and inventory item is not null
768 * MATERIAL ITEM RATE -- for resource class material items and inventory items not null
769 */
770 -- Identify the transaction Type
771 If l_trxn_type is NULL Then
772 l_stage := 'Deriving transaction type based on resource class';
773 print_msg(l_debug_flag,l_stage);
774
775 If p_calling_mode = 'ACTUAL_RATES' Then
776 If (p_resource_class = 'PEOPLE') Then
777
778 If ( p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
779 l_trxn_type := 'BOM RESOURCE RATE';
780
781 ElsIf (p_person_id is NOT NULL OR p_job_id is NOT NULL ) Then
782 l_trxn_type := 'LABOR RATE' ;
783
784 /* Elsif (p_expenditure_type is NOT NULL and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
785 l_trxn_type := 'EXP TYPE RATE';
786 */
787
788 Else
789 l_stage := 'Invalid People Class params';
790 -- This is an invalid combination
791 Raise l_insufficient_parms;
792 End If;
793
794 Elsif (p_resource_class = 'EQUIPMENT') Then
795 If (p_non_labor_resource is NOT NULL
796 and NVL(p_NLR_organization_id,l_rate_organization_id) is NOT NULL
797 and p_expenditure_type is NOT NULL ) Then
798 l_trxn_type := 'NON LABOR RESOURCE RATE' ;
799
800 Elsif (p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
801 l_trxn_type := 'BOM EQUIPMENT RATE';
802
803 /* Bug fix: as per discussion with Anders and Jhonson for people and equipemnt class
804 * if rate is not found from schedule then it should pick from resource class level
805 * finally decided as we should retain this logic */
806 Elsif (p_expenditure_type is NOT NULL
807 and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
808 l_trxn_type := 'EXP TYPE RATE';
809
810 Else
811 l_stage := 'Invalid Equipment Class params';
812 -- This is an invalid combination
813 Raise l_insufficient_parms;
814 End If;
815 Elsif (p_resource_class = 'MATERIAL_ITEMS') Then
816
817 If (p_inventory_item_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
818 l_trxn_type := 'MATERIAL ITEM RATE';
819 Elsif (p_expenditure_type is NOT NULL
820 and check_expCostRateFlag(p_expenditure_type) = 'Y') Then
821 l_trxn_type := 'EXP TYPE RATE';
822 Else
823 l_stage := 'Invalid Material Itms Class params';
824 -- This is an invalid combination
825 Raise l_insufficient_parms;
826 End If;
827
828 Elsif (p_resource_class = 'FINANCIAL_ELEMENTS' ) Then
829 If p_expenditure_type is NOT NULL Then
830 IF check_expCostRateFlag(p_expenditure_type) = 'Y' Then
831 l_trxn_type := 'EXP TYPE RATE';
832 Else
833 l_stage := 'Invalid Financial Elements Class params';
834 -- This is an invalid combination
835 Raise l_insufficient_parms;
836 End If;
837 /**Else
838 l_stage := 'Financial Elements Class params NO COST RATE';
839 l_trxn_type := 'EXP TYPE RATE N_FLAG';
840 End IF;
841 **/
842 Else
843 l_stage := 'Invalid Financial Elements Class params';
844 -- This is an invalid combination
845 Raise l_insufficient_parms;
846 End If;
847 End If; -- end of resource class
848
849 ELSIF p_calling_mode = 'PLAN_RATES' Then
850
851 If (p_resource_class = 'PEOPLE') Then
852 If ( p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
853 l_trxn_type := 'BOM RESOURCE RATE';
854
855 ElsIf (p_person_id is NOT NULL and p_plan_cost_emp_rate_sch_id is NOT NULL ) Then
856 l_trxn_type := 'LABOR SCH RATE' ;
857
858 Elsif (p_job_id is NOT NULL and p_plan_cost_job_rate_sch_id is NOT NULL ) Then
859 l_trxn_type := 'JOB SCH RATE';
860
861 /* Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
862 l_trxn_type := 'NON LABOR SCH RATE';
863 */
864
865 Else
866 l_stage := 'Invalid People Class params';
867 -- This is an invalid combination
868 Raise l_insufficient_parms;
869 End If;
870 Elsif (p_resource_class = 'EQUIPMENT') Then
871 If (p_non_labor_resource is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL
872 and p_expenditure_type is NOT NULL ) Then
873 l_trxn_type := 'NON LABOR SCH RATE' ;
874
875 Elsif (p_BOM_resource_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
876 l_trxn_type := 'BOM EQUIPMENT RATE';
877
878 Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
879 l_trxn_type := 'NON LABOR SCH RATE' ;
880
881 Else
882 l_stage := 'Invalid Equipment Class params';
883 -- This is an invalid combination
884 Raise l_insufficient_parms;
885 End If;
886 Elsif (p_resource_class = 'MATERIAL_ITEMS') Then
887
888 If (p_inventory_item_id is NOT NULL and l_rate_organization_id is NOT NULL ) Then
889 l_trxn_type := 'MATERIAL ITEM RATE';
890
891 Elsif (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
892 l_trxn_type := 'NON LABOR SCH RATE' ;
893
894 Else
895 l_stage := 'Invalid Material Itms Class params';
896 -- This is an invalid combination
897 Raise l_insufficient_parms;
898 End If;
899 Elsif (p_resource_class = 'FINANCIAL_ELEMENTS' ) Then
900 If (p_expenditure_type is NOT NULL and p_plan_cost_nlr_rate_sch_id is NOT NULL ) Then
901 l_trxn_type := 'NON LABOR SCH RATE' ;
902
903 /**Else
904 If p_expenditure_type is NOT NULL Then
905 IF check_expCostRateFlag(p_expenditure_type) = 'Y' Then
906 l_trxn_type := 'EXP TYPE RATE';
907 Else
908 l_stage := 'Financial Elements Class params NO COST RATE';
909 l_trxn_type := 'EXP TYPE RATE N_FLAG';
910 End IF;
911 **/
912 Else
913 l_stage := 'Invalid Financial Elements Class params';
914 -- This is an invalid combination
915 Raise l_insufficient_parms;
916 --End If;
917 End If;
918
919 End If; -- end of resource class
920 End If; -- end of calling mode
921 End If ; -- end of transaction type null
922
923 l_stage := 'After validating input params: Transaction Type['||l_trxn_type||']' ;
924 print_msg(l_debug_flag,l_stage);
925
926 /* Bug fix: 4232181 Derive the organization overrides from the project level */
927 IF l_trxn_type IN ('LABOR SCH RATE','JOB SCH RATE','LABOR RATE') Then
928 IF l_override_organization_id is NULL Then
929 l_stage := 'Calling pa_cost.Override_exp_organization api';
930 print_msg(l_debug_flag,l_stage);
931 pa_cost.Override_exp_organization
932 (P_item_date => p_exp_item_date
933 ,P_person_id => p_person_id
934 ,P_project_id => p_project_id
935 ,P_incurred_by_organz_id => p_incurred_by_organization_id
936 ,P_Expenditure_type => p_expenditure_type
937 ,X_overr_to_organization_id => l_override_organization_id
938 ,X_return_status => l_return_status
939 ,X_msg_count => l_msg_count
940 ,X_msg_data => l_msg_data
941 );
942 l_stage := 'Return status of pa_cost.Override_exp_organization ['||l_return_status||']';
943 l_stage := l_stage||']msgData['||l_msg_data||']OverideOrg['||l_override_organization_id||']' ;
944 print_msg(l_debug_flag,l_stage);
945 End If;
946 End If;
947
948 /* Actual Rate Calculation logic starts here */
949 IF l_trxn_type in ('BOM EQUIPMENT RATE', 'BOM RESOURCE RATE') Then
950
951 -- call the api provided by PO/BOM team to dervie the cost rate
952 l_cost_source := p_mfc_cost_source ;
953 l_api_version := 1.0;
954 IF p_BOM_resource_id is NOT NULL Then
955
956 BEGIN
957 l_stage := 'Calling CST_ItemResourceCosts_GRP.Get_ResourceRate API';
958 print_msg(l_debug_flag,l_stage);
959
960 CST_ItemResourceCosts_GRP.Get_ResourceRate(
961 p_api_version => l_api_version
962 ,p_init_msg_list => FND_API.G_FALSE
963 ,p_commit => FND_API.G_FALSE
964 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
965 ,x_return_status => l_return_status
966 ,x_msg_count => l_msg_count
967 ,x_msg_data => l_msg_data
968 ,p_resource_id => p_BOM_resource_id
969 ,p_organization_id => l_rate_organization_id
970 ,p_cost_type_id => p_mfc_cost_type_id
971 ,x_resource_rate => l_txn_raw_cost_rate
972 ,x_currency_code => l_txn_curr_code
973 );
974 l_stage := 'After CST_ItemResourceCosts_GRP.Get_ResourceRate API returnStatus['||l_return_status||
975 ']CostRate['||l_txn_raw_cost_rate||']CurrCode['||l_txn_curr_code||']msgDate['||l_msg_data||']';
976 print_msg(l_debug_flag,l_stage);
977 If ( l_return_status <> 'S' OR l_txn_curr_code is NULL ) Then
978 If l_return_status = 'U' Then
979 l_stage := l_stage||'SQLERRM['||SQLCODE||SQLERRM;
980 End If;
981 print_msg(l_debug_flag,l_stage);
982 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
983 Raise l_no_rate_found;
984 End If;
985
986 If l_txn_curr_code is NOT NULL Then
987 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
988 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code);
989 End If;
990 EXCEPTION
991 WHEN NO_DATA_FOUND THEN
992 l_msg_data := 'PA_FP_MISSING_RATE';
993 l_return_status := 'E';
994 x_return_status := 'E';
995 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
996 RAISE l_no_rate_found;
997 WHEN OTHERS THEN
998 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
999 l_msg_data := 'PA_FP_MISSING_RATE';
1000 l_return_status := 'E';
1001 x_return_status := 'E';
1002 x_error_msg_code := 'PA_FP_MISSING_RATE';
1003 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1004 RAISE l_no_rate_found;
1005 End If;
1006 RAISE;
1007 END;
1008
1009 End If;
1010
1011 ElsIF l_trxn_type in ('MATERIAL ITEM RATE') Then
1012 IF p_inventory_item_id is NOT NULL Then
1013 -- call the api provided by PO/BOM team to dervie the Raw cost
1014 BEGIN
1015 l_stage := 'Calling CST_ItemResourceCosts_GRP.Get_ItemCost API';
1016 print_msg(l_debug_flag,l_stage);
1017 l_cost_source := p_mfc_cost_source ;
1018 l_api_version := 1.0;
1019
1020 select INVENTORY_ASSET_FLAG into l_INVENTORY_ASSET_FLAG --Select added for bug 9830012
1021 from MTL_SYSTEM_ITEMS_B
1022 where INVENTORY_ITEM_ID = p_inventory_item_id
1023 and ORGANIZATION_ID = l_rate_organization_id;
1024 if nvl(l_INVENTORY_ASSET_FLAG, 'N') = 'Y' then --If condition added for bug 9896718
1025 CST_ItemResourceCosts_GRP.Get_ItemCost
1026 (
1027 p_api_version => l_api_version
1028 ,p_init_msg_list => FND_API.G_FALSE
1029 ,p_commit => FND_API.G_FALSE
1030 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1031 ,x_return_status => l_return_status
1032 ,x_msg_count => l_msg_count
1033 ,x_msg_data => l_msg_data
1034 ,p_item_id => p_inventory_item_id
1035 ,p_organization_id => l_rate_organization_id
1036 ,p_cost_source => l_cost_source
1037 ,p_cost_type_id => p_mfc_cost_type_id
1038 /*Bug fix:4154009 ,x_item_cost => l_txn_raw_cost */
1039 ,x_item_cost => l_txn_raw_cost_rate
1040 ,x_currency_code => l_txn_curr_code
1041 );
1042 l_stage := 'After CST_ItemResourceCosts_GRP.Get_ItemCost API returnStatus['||l_return_status||
1043 ']MaterialCostRate['||l_txn_raw_cost_rate||']CurrCode['||l_txn_curr_code||']msgDate['||l_msg_data||']' ;
1044 print_msg(l_debug_flag,l_stage);
1045 If ( l_return_status <> 'S' OR l_txn_curr_code is NULL ) Then
1046 If l_return_status = 'U' Then
1047 l_stage := l_stage||'SQLERRM['||SQLCODE||SQLERRM;
1048 End If;
1049 print_msg(l_debug_flag,l_stage);
1050 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1051 Raise l_no_rate_found;
1052 End If;
1053
1054 If l_txn_curr_code is NOT NULL Then
1055 -- this is the list price per unit
1056 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1057 (l_txn_raw_cost_rate * NVL(p_quantity,1), l_txn_curr_code );
1058 End If;
1059 end if; --End of if condition added for bug 9896718
1060 /*i Bug fix:4154009 The Api returns the rate not the cost So need not re derive the rate
1061 If l_txn_raw_cost_rate is NULL Then
1062 -- derive the cost rate based on the item cost and quantity
1063 If NVL(p_quantity,1) <> 0 Then
1064 l_txn_raw_cost_rate := l_txn_raw_cost / NVL(p_quantity,1);
1065 Else
1066 l_txn_raw_cost_rate := l_txn_raw_cost ;
1067 End If;
1068 End If;
1069 **/
1070 EXCEPTION
1071 WHEN NO_DATA_FOUND THEN
1072 l_msg_data := 'PA_FP_MISSING_RATE';
1073 l_return_status := 'E';
1074 l_msg_data := 'PA_FP_MISSING_RATE';
1075 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1076 RAISE l_no_rate_found;
1077 WHEN OTHERS THEN
1078 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
1079 l_return_status := 'E';
1080 l_msg_data := 'PA_FP_MISSING_RATE';
1081 x_error_msg_code := 'PA_FP_MISSING_RATE';
1082 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1083 RAISE l_no_rate_found;
1084 End If;
1085 RAISE;
1086 END;
1087 End If;
1088
1089
1090 ELSIF l_trxn_type = 'LABOR RATE' Then
1091 -- call the get raw cost api for the person id is not null else call the requirement raw cost for the
1092 -- job and organization type transactions
1093 If (p_person_id is NOT NULL OR p_job_id is NOT NULL ) Then
1094 /* Derive default labor cost multiplier for the given Tasks */
1095 IF ( p_task_id IS NOT NULL AND l_cost_rate_multiplier is NULL ) THEN
1096 l_stage := 'Getting labor cost multiplier name';
1097 l_cost_rate_multiplier := get_CostRateMultiplier
1098 (p_task_id => p_task_id
1099 ,p_exp_item_date => p_exp_item_date
1100 );
1101 End If;
1102
1103 /* 12.2 payroll integration .. set the globals for RBC .. start */
1104
1105 pa_pay_util.g_project_number := NULL;
1106 pa_pay_util.g_task_number := NULL;
1107 pa_pay_util.g_location_code := NULL;
1108 pa_pay_util.g_job_name := NULL;
1109 pa_pay_util.g_work_type_name := NULL;
1110
1111 l_stage := 'Start of get and set values for rate by criteria. Resource Assignment is: ' || p_resource_assignment_id;
1112 print_msg(l_debug_flag,l_stage);
1113
1114 /* Bug 12374022 .. start */
1115
1116 if p_resource_assignment_id is not null then
1117
1118 open c_get_res_asgn;
1119 fetch c_get_res_asgn into l_asgn_rec;
1120 close c_get_res_asgn;
1121
1122 open c_get_format(l_asgn_rec.resource_list_member_id);
1123 fetch c_get_format into l_format_name;
1124 close c_get_format;
1125
1126 l_curr_indx := 0;
1127 l_prev_indx := 0;
1128
1129 l_named_person_flag := 'N';
1130 l_job_flag := 'N';
1131 l_organization_flag := 'N';
1132 l_team_role_flag := 'N';
1133
1134 l_stage := 'Resource format name: ' || l_format_name;
1135 print_msg(l_debug_flag,l_stage);
1136
1137 loop
1138 l_curr_indx := instr(substr(l_format_name, l_prev_indx), ' - ');
1139 if l_curr_indx > 0 then
1140 if ltrim(rtrim(substr(l_format_name, l_prev_indx, l_curr_indx - 1))) = 'Named Person' then
1141 l_named_person_flag := 'Y';
1142 elsif ltrim(rtrim(substr(l_format_name, l_prev_indx, l_curr_indx - 1))) = 'Job' then
1143 l_job_flag := 'Y';
1144 elsif (ltrim(rtrim(substr(l_format_name, l_prev_indx, l_curr_indx - 1))) = 'Organization' OR
1145 ltrim(rtrim(substr(l_format_name, l_prev_indx, l_curr_indx - 1))) = 'Org') then
1146 l_organization_flag := 'Y';
1147 elsif ltrim(rtrim(substr(l_format_name, l_prev_indx, l_curr_indx - 1))) = 'Team Role' then
1148 l_team_role_flag := 'Y';
1149 end if;
1150 else
1151 if ltrim(rtrim(substr(l_format_name, l_prev_indx))) = 'Named Person' then
1152 l_named_person_flag := 'Y';
1153 elsif ltrim(rtrim(substr(l_format_name, l_prev_indx))) = 'Job' then
1154 l_job_flag := 'Y';
1155 elsif (ltrim(rtrim(substr(l_format_name, l_prev_indx))) = 'Organization' OR
1156 ltrim(rtrim(substr(l_format_name, l_prev_indx))) = 'Org') then
1157 l_organization_flag := 'Y';
1158 elsif ltrim(rtrim(substr(l_format_name, l_prev_indx))) = 'Team Role' then
1159 l_team_role_flag := 'Y';
1160 end if;
1161
1162 exit;
1163 end if;
1164 l_prev_indx := l_prev_indx + l_curr_indx + 2;
1165 end loop;
1166
1167 end if; -- p_resource_assignment_id is not null
1168
1169 l_stage := 'Resource Format Flags: job: ' || l_job_flag ||', person: ' || l_named_person_flag ||', org flag: ' || l_organization_flag ||', team role: ' || l_team_role_flag;
1170 print_msg(l_debug_flag,l_stage);
1171
1172 -- For BnF, deriving the job has the following rules:
1173 -- Team Role takes precedence over named person. Job would be on it's own and hence will be taken as is.
1174 -- For regular transactions however, the person takes precedence, and team role is not relevant.
1175
1176 if p_resource_assignment_id is null then
1177 if p_person_id is not null then
1178 if p_job_id is null then
1179 select name
1180 into pa_pay_util.g_job_name
1181 from per_jobs_v
1182 where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_exp_item_date);
1183
1184 l_stage := 'RBC: Employee Job name: ' || pa_pay_util.g_job_name;
1185 print_msg(l_debug_flag,l_stage);
1186 elsif p_job_id is not null then
1187 select name
1188 into pa_pay_util.g_job_name
1189 from per_jobs_v
1190 where job_id = p_job_id;
1191
1192 l_stage := 'RBC: Job name: ' || pa_pay_util.g_job_name;
1193 print_msg(l_debug_flag,l_stage);
1194 end if;
1195 end if;
1196
1197 select location_code
1198 into pa_pay_util.g_location_code
1199 from hr_locations a, per_all_assignments_f b
1200 where b.person_id = p_person_id
1201 and a.location_use = 'HR'
1202 and a.location_id = b.location_id
1203 and p_exp_item_date between b.effective_start_date and nvl(b.effective_end_date, p_exp_item_date);
1204
1205 l_stage := 'RBC: Employee Location Code: ' || pa_pay_util.g_location_code;
1206 print_msg(l_debug_flag,l_stage);
1207
1208 if p_job_id is not null then
1209 select name
1210 into pa_pay_util.g_job_name
1211 from per_jobs_v
1212 where job_id = p_job_id;
1213
1214 l_stage := 'RBC: Job name: ' || pa_pay_util.g_job_name;
1215 print_msg(l_debug_flag,l_stage);
1216 end if;
1217 else --- p_resource_assignment_id is null then
1218
1219 if l_job_flag = 'Y' then
1220 select name
1221 into pa_pay_util.g_job_name
1222 from per_jobs_v
1223 where job_id = l_asgn_rec.job_id;
1224
1225 l_stage := 'RBC for BnF: Job Flag: ' || l_job_flag ||', Job name: ' || pa_pay_util.g_job_name;
1226 print_msg(l_debug_flag,l_stage);
1227 end if;
1228
1229 if l_team_role_flag = 'Y' then
1230 -- find the job for the team role
1231 begin
1232 select name
1233 into pa_pay_util.g_job_name
1234 from per_jobs_v
1235 where job_id = (select a.default_job_id
1236 from pa_project_role_types_vl a
1237 where a.project_role_id = l_asgn_rec.project_role_id);
1238
1239 l_stage := 'RBC for BnF: Team Role Flag: ' || l_team_role_flag ||', Team Role Job name: ' || pa_pay_util.g_job_name;
1240 print_msg(l_debug_flag,l_stage);
1241
1242 exception
1243 when no_data_found then
1244 null;
1245 end;
1246
1247 end if; -- l_team_role_flag
1248
1249 if l_named_person_flag = 'Y' then
1250 if pa_pay_util.g_job_name is null then
1251 begin
1252 select name
1253 into pa_pay_util.g_job_name
1254 from per_jobs_v
1255 where job_id = pa_utils.getempjobid(X_person_id => p_person_id, X_date => p_exp_item_date);
1256
1257 l_stage := 'RBC for BnF: Named Person Flag: ' || l_named_person_flag ||', Employee Job name: ' || pa_pay_util.g_job_name;
1258 print_msg(l_debug_flag,l_stage);
1259 exception
1260 when no_data_found then
1261 null;
1262 end;
1263 end if;
1264
1265 if pa_pay_util.g_location_code is null and l_organization_flag = 'Y' then
1266 begin
1267 select hl.location_code
1268 into pa_pay_util.g_location_code
1269 from hr_all_organization_units hou, hr_locations hl
1270 where hou.location_id = hl.location_id
1271 and hou.organization_id = l_asgn_rec.organization_id;
1272
1273 l_stage := 'RBC for BnF: Named Person Flag: ' || l_named_person_flag ||', Organization Flag: ' || l_organization_flag ||', Organization Location code: ' || pa_pay_util.g_location_code;
1274 print_msg(l_debug_flag,l_stage);
1275 exception
1276 when no_data_found then
1277 null;
1278 end;
1279 elsif pa_pay_util.g_location_code is null and l_organization_flag = 'N' then
1280 begin
1281 select a.location_code
1282 into pa_pay_util.g_location_code
1283 from hr_locations a, per_all_assignments_f b
1284 where b.person_id = p_person_id
1285 and a.location_use = 'HR'
1286 and a.location_id = b.location_id
1287 and p_exp_item_date between b.effective_start_date and nvl(b.effective_end_date, p_exp_item_date);
1288
1289 l_stage := 'RBC for BnF: Named Person Flag: ' || l_named_person_flag ||', Organization Flag: ' || l_organization_flag ||', Assignment Location Code: ' || pa_pay_util.g_location_code;
1290 print_msg(l_debug_flag,l_stage);
1291 exception
1292 when no_data_found then
1293 null;
1294 end;
1295 end if; -- l_organization_flag
1296 end if; -- l_named_person_flag
1297
1298 if pa_pay_util.g_location_code is null and l_organization_flag = 'Y' then
1299 begin
1300 select hl.location_code
1301 into pa_pay_util.g_location_code
1302 from hr_all_organization_units hou, hr_locations hl
1303 where hou.location_id = hl.location_id
1304 and hou.organization_id = l_asgn_rec.organization_id;
1305
1306 l_stage := 'RBC for BnF: Organization Flag: ' || l_organization_flag ||', Organization Location Code: ' || pa_pay_util.g_location_code;
1307 print_msg(l_debug_flag,l_stage);
1308 exception
1309 when no_data_found then
1310 null;
1311 end;
1312 end if;
1313
1314 end if;
1315
1316 -- get the project number and task number details
1317 select segment1
1318 into pa_pay_util.g_project_number
1319 from pa_projects
1320 where project_id = p_project_id;
1321
1322 l_stage := 'RBC: Project Number: ' || pa_pay_util.g_project_number;
1323 print_msg(l_debug_flag,l_stage);
1324
1325 if p_task_id is not null then
1326 -- get the task number details
1327 -- bug 12557318: changed the cursor to get the task_number from pa_proj_elements
1328 -- and not pa_tasks. Non-shared workplan tasks do not exist in pa_tasks.
1329 begin
1330 select element_number
1331 into pa_pay_util.g_task_number
1332 from pa_proj_elements
1333 where proj_element_id = p_task_id
1334 and project_id = p_project_id;
1335
1336 l_stage := 'RBC: Task Number from elements: ' || pa_pay_util.g_task_number;
1337 print_msg(l_debug_flag,l_stage);
1338
1339 exception
1340 when no_data_found then
1341 begin
1342 select task_number
1343 into pa_pay_util.g_task_number
1344 from pa_tasks
1345 where task_id = p_task_id;
1346
1347 l_stage := 'RBC: Task Number from fin tasks: ' || pa_pay_util.g_task_number;
1348 print_msg(l_debug_flag,l_stage);
1349
1350 exception
1351 when no_data_found then
1352 null;
1353 end;
1354 end;
1355
1356 -- get the default work type information from the task
1357 -- work plan tasks do not have work types associated to tasks.
1358 begin
1359 select name
1360 into pa_pay_util.g_work_type_name
1361 from pa_work_types_vl a, pa_tasks b
1362 where a.work_type_id = b.work_type_id
1363 and b.task_id = p_task_id;
1364
1365 l_stage := 'RBC: Work Type name: ' || pa_pay_util.g_work_type_name;
1366 print_msg(l_debug_flag,l_stage);
1367
1368 exception
1369 when no_data_found then
1370 null;
1371 end;
1372 end if; -- p_task_id not null
1373
1374 /* Bug 12374022 .. end */
1375 l_stage := 'End of get and set values for rate by criteria';
1376 print_msg(l_debug_flag,l_stage);
1377
1378 /* 12.2 payroll integration .. set the globals for RBC .. end */
1379
1380 l_stage := 'Calling PA_COST_RATE_PUB.get_labor_rate API in STAFFED calling mode';
1381 print_msg(l_debug_flag,l_stage);
1382 l_rate_organization_id := NVL(l_override_organization_id,
1383 NVl(p_incurred_by_organization_id,p_nlr_organization_id));
1384 PA_COST_RATE_PUB.get_labor_rate
1385 (p_person_id => p_person_id
1386 ,p_txn_date => p_Exp_item_date
1387 ,p_calling_module =>'STAFFED'
1388 ,p_org_id => NVL(p_expenditure_ou,p_project_OU)
1389 ,x_job_id => l_job_id
1390 ,x_organization_id =>l_rate_organization_id
1391 ,x_cost_rate =>l_txn_raw_cost_rate
1392 ,x_start_date_active =>l_start_date_active
1393 ,x_end_date_active =>l_end_date_active
1394 ,x_org_labor_sch_rule_id =>l_org_labor_sch_rule_id
1395 ,x_costing_rule =>l_costing_rule
1396 ,x_rate_sch_id =>l_rate_sch_id
1397 ,x_cost_rate_curr_code =>l_txn_curr_code
1398 ,x_acct_rate_type =>l_acct_rate_type
1399 ,x_acct_rate_date_code =>l_acct_rate_date_code
1400 ,x_acct_exch_rate =>l_acct_exch_rate
1401 ,x_ot_project_id =>l_ot_project_id
1402 ,x_ot_task_id =>l_ot_task_id
1403 ,x_err_stage => l_msg_data
1404 ,x_err_code => l_err_code
1405 );
1406
1407 l_stage := 'After get_labor_rate :return code['||l_err_code||']msgData['||l_msg_data||
1408 ']LaborCostRate['||l_txn_raw_cost_rate||']CostCurrCode['||l_txn_curr_code||']' ;
1409 print_msg(l_debug_flag,l_stage);
1410
1411 If l_err_code is NOT NULL OR l_txn_raw_cost_rate is NULL THEN
1412 l_stage := 'No Rate from Get Labor Rate(STAFFED) API:'||l_msg_data ;
1413 print_msg(l_debug_flag,l_stage);
1414 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1415 RAISE l_no_rate_found;
1416 End If;
1417
1418 If l_txn_curr_code is NOT NULL Then
1419 l_txn_raw_cost_rate := l_txn_raw_cost_rate * NVL(l_cost_rate_multiplier,1);
1420 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1421 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1422 End If;
1423
1424 End If;
1425
1426 ELSIF l_trxn_type = 'NON LABOR RESOURCE RATE' Then
1427 -- Call non-labor raw cost api
1428 l_stage := 'Calling Non Labor raw cost API';
1429 print_msg(l_debug_flag,l_stage);
1430 If (p_non_labor_resource is NOT NULL and p_expenditure_type is NOT NULL ) Then
1431 pa_cost1.Get_Non_Labor_raw_cost
1432 (p_project_id => p_project_id
1433 ,p_task_id => p_task_id
1434 ,p_non_labor_resource => p_non_labor_resource
1435 ,p_nlr_organization_id => p_nlr_organization_id
1436 ,p_expenditure_type => p_expenditure_type
1437 ,p_exp_item_date => p_exp_item_date
1438 ,p_override_organization_id => l_rate_organization_id
1439 ,p_quantity => p_quantity
1440 ,p_org_id => NVL(p_expenditure_ou,p_project_ou)
1441 ,p_nlr_schedule_id => Null
1442 ,x_trxn_raw_cost_rate => l_txn_raw_cost_rate
1443 ,x_trxn_raw_cost => l_txn_raw_cost
1444 ,x_txn_currency_code => l_txn_curr_code
1445 ,x_return_status => l_return_status
1446 ,x_error_msg_code => l_msg_data
1447 );
1448 l_stage := 'After Get_Non_Labor_raw_cost api return status['||l_return_status||']msgData['||l_msg_data||
1449 ']NlrCostrate['||l_txn_raw_cost_rate||']NlrRawcost['||l_txn_raw_cost||
1450 ']NlrCostRateCurr['||l_txn_curr_code||']' ;
1451 print_msg(l_debug_flag,l_stage);
1452
1453 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1454 x_raw_cost_rejection_code := 'PA_NLR_NO_RATE_FOUND' ;
1455 RAISE l_no_rate_found;
1456 End If;
1457 End If;
1458
1459 ELSIF l_trxn_type = 'EXP TYPE RATE' Then
1460 If ( p_exp_item_date is NOT NULL and p_expenditure_type is NOT NULL
1461 and NVL(p_expenditure_ou,p_project_ou) is NOT NULL ) Then
1462 /* get the currency code */
1463 l_txn_curr_code := Get_curr_code(p_org_id => NVL(p_expenditure_ou,p_project_ou));
1464 l_txn_raw_cost_rate := GetExpTypeCostRate
1465 (p_exp_type => p_expenditure_type
1466 ,p_exp_item_date => p_exp_item_date
1467 ,p_org_id => NVL(p_expenditure_ou,p_project_ou)
1468 );
1469
1470 If l_txn_raw_cost_rate is NULL OR l_txn_curr_code is NULL Then
1471 l_stage := 'No Rate from GetExpTypeCostRate api';
1472 print_msg(l_debug_flag,l_stage);
1473 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1474 RAISE l_no_rate_found;
1475 End If;
1476
1477 If l_txn_raw_cost_rate is NOT NULL and l_txn_curr_code is NOT NULL Then
1478 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1479 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1480 End If;
1481 l_stage := 'ExpcostRateCur['||l_txn_curr_code||']ExpCostRate['||l_txn_raw_cost_rate||']' ;
1482 print_msg(l_debug_flag,l_stage);
1483 End If;
1484
1485 ELSIF l_trxn_type = 'EXP TYPE RATE N_FLAG' Then
1486 If p_override_trxn_curr_code is NULL Then
1487 l_txn_curr_code := Get_curr_code(p_org_id =>
1488 NVL(p_expenditure_ou,p_project_ou));
1489 Else
1490 l_txn_curr_code := p_override_trxn_curr_code;
1491 End If;
1492
1493 l_txn_raw_cost_rate := 1;
1494 If l_txn_raw_cost_rate is NULL OR l_txn_curr_code is NULL Then
1495 l_stage := 'No Rate currency code from Get_curr_code api';
1496 print_msg(l_debug_flag,l_stage);
1497 x_raw_cost_rejection_code := 'PA_FP_MISSING_RATE';
1498 RAISE l_no_rate_found;
1499 End If;
1500
1501 If l_txn_raw_cost_rate is NOT NULL and l_txn_curr_code is NOT NULL Then
1502 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1503 (l_txn_raw_cost_rate * NVL(p_quantity,0),
1504 l_txn_curr_code );
1505 End If;
1506 l_stage := 'Get_curr_code['||l_txn_curr_code||']ExpCostRate['||
1507 l_txn_raw_cost_rate||']' ;
1508 print_msg(l_debug_flag,l_stage);
1509
1510 ELSIF l_trxn_type in ('LABOR SCH RATE','JOB SCH RATE','NON LABOR SCH RATE') Then
1511 /* Derive default labor cost multiplier for the given Tasks */
1512 IF ( p_task_id IS NOT NULL AND l_cost_rate_multiplier is NULL
1513 and l_trxn_type IN ('LABOR SCH RATE','JOB SCH RATE' )) THEN
1514 l_stage := 'Getting labor cost multiplier name';
1515 l_cost_rate_multiplier := get_CostRateMultiplier
1516 (p_task_id => p_task_id
1517 ,p_exp_item_date => p_exp_item_date
1518 );
1519 End If;
1520
1521 l_rate_organization_id := NVL(l_override_organization_id,
1522 NVl(p_incurred_by_organization_id,p_nlr_organization_id));
1523 If l_trxn_type = 'LABOR SCH RATE' Then
1524 l_bill_rate_schedule_type := 'EMPLOYEE';
1525 l_bill_rate_sch_id := p_plan_cost_emp_rate_sch_id ;
1526 Elsif l_trxn_type = 'JOB SCH RATE' Then
1527 l_bill_rate_schedule_type := 'JOB';
1528 l_bill_rate_sch_id := p_plan_cost_job_rate_sch_id ;
1529 Elsif l_trxn_type = 'NON LABOR SCH RATE' Then
1530 l_bill_rate_schedule_type := 'NON-LABOR';
1531 l_bill_rate_sch_id := p_plan_cost_Nlr_rate_sch_id ;
1532 l_cost_rate_multiplier := NULL;
1533 End If;
1534 l_stage := 'Calling get_RateSchDetails SchType['||l_bill_rate_schedule_type||']SchId['||l_bill_rate_sch_id||']';
1535 print_msg(l_debug_flag,l_stage);
1536 pa_cost1.get_RateSchDetails
1537 (p_schedule_type => l_bill_rate_schedule_type
1538 ,p_rate_sch_id => l_bill_rate_sch_id
1539 ,p_person_id => p_person_id
1540 ,p_job_id => p_job_id
1541 ,p_non_labor_resource => p_non_labor_resource
1542 ,p_expenditure_type => p_expenditure_type
1543 ,p_rate_organization_id => l_rate_organization_id
1544 ,p_exp_item_date => p_exp_item_date
1545 ,p_org_id => NVL(p_expenditure_OU,p_project_ou)
1546 ,x_currency_code => l_txn_curr_code
1547 ,x_cost_rate => l_txn_raw_cost_rate
1548 ,x_markup_percent => l_markup_percent
1549 ,x_return_status => l_return_status
1550 ,x_error_msg_code => l_msg_data );
1551
1552 l_stage := 'After Calling Get_RateSchDetails api return status['||l_return_status||
1553 ']msgData['||l_msg_data||']RateSchCostrate['||l_txn_raw_cost_rate||
1554 ']RateSchRawcost['||l_txn_raw_cost||']RateSchCurr['||l_txn_curr_code||
1555 ']MarkupPercent['||l_markup_percent||']' ;
1556 print_msg(l_debug_flag,l_stage);
1557
1558 /* check if no rate found for the Employee sch then derive the
1559 * rate from job schedule if the resource class is people
1560 */
1561 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1562 If (p_resource_class = 'PEOPLE'
1563 AND l_trxn_type = 'LABOR SCH RATE'
1564 AND p_job_id is NOT NULL
1565 AND p_plan_cost_job_rate_sch_id is NOT NULL ) Then
1566
1567 l_trxn_type := 'JOB SCH RATE' ;
1568 l_bill_rate_schedule_type := 'JOB';
1569 l_bill_rate_sch_id := p_plan_cost_job_rate_sch_id ;
1570
1571 l_stage := 'Calling get_RateSchDetails SchType['
1572 ||l_bill_rate_schedule_type||']SchId['
1573 ||l_bill_rate_sch_id||']';
1574 print_msg(l_debug_flag,l_stage);
1575 pa_cost1.get_RateSchDetails
1576 (p_schedule_type => l_bill_rate_schedule_type
1577 ,p_rate_sch_id => l_bill_rate_sch_id
1578 ,p_person_id => p_person_id
1579 ,p_job_id => p_job_id
1580 ,p_non_labor_resource => p_non_labor_resource
1581 ,p_expenditure_type => p_expenditure_type
1582 ,p_rate_organization_id => l_rate_organization_id
1583 ,p_exp_item_date => p_exp_item_date
1584 ,p_org_id => NVL(p_expenditure_OU,p_project_ou)
1585 ,x_currency_code => l_txn_curr_code
1586 ,x_cost_rate => l_txn_raw_cost_rate
1587 ,x_markup_percent => l_markup_percent
1588 ,x_return_status => l_return_status
1589 ,x_error_msg_code => l_msg_data );
1590
1591 l_stage := 'After Calling Get_JOBRateSchDetails api return status['||l_return_status||
1592 ']msgData['||l_msg_data||']RateSchCostrate['||l_txn_raw_cost_rate||
1593 ']RateSchRawcost['||l_txn_raw_cost||']RateSchCurr['||l_txn_curr_code||
1594 ']MarkupPercent['||l_markup_percent||']' ;
1595 print_msg(l_debug_flag,l_stage);
1596
1597 End If;
1598 End If;
1599
1600 IF l_return_status <> 'S' OR l_txn_curr_code is NULL Then
1601 x_raw_cost_rejection_code := 'PA_NO_PLAN_SCH_RATE_FOUND' ;
1602 RAISE l_no_rate_found;
1603 End If;
1604 If l_txn_curr_code is NOT NULL Then
1605 l_txn_raw_cost_rate := l_txn_raw_cost_rate * NVL(l_cost_rate_multiplier,1);
1606 l_txn_raw_cost := pa_currency.round_trans_currency_amt1
1607 (l_txn_raw_cost_rate * NVL(p_quantity,0), l_txn_curr_code );
1608 End If;
1609
1610 END If; -- end of the transaction type
1611
1612 --Assign the out variables
1613 x_trxn_curr_code := l_txn_curr_code;
1614 x_trxn_raw_cost := l_txn_raw_cost;
1615 x_trxn_raw_cost_rate := l_txn_raw_cost_rate;
1616 l_stage := 'End of Raw Cost Calculation:RawCost['||x_trxn_raw_cost||']Rate['||x_trxn_raw_cost_rate||
1617 ']CurrCode['||x_trxn_curr_code||']';
1618 print_msg(l_debug_flag,l_stage);
1619 /* End of Raw Cost calculation*/
1620
1621 l_proj_flag := pa_cost1.check_proj_burdened(p_project_type,p_project_id);
1622 print_msg(l_debug_flag,'ProjBurdFlag['||l_proj_flag||']');
1623 -- Check if project type is burdened if so calculate the burdened costs
1624
1625 /**** Burden cost Calculation Starts here */
1626
1627 If x_trxn_raw_cost is NOT NULL Then
1628
1629 If ( p_override_trxn_burden_cost is NOT NULL OR
1630 p_override_burden_cost_rate is NOT NULL) Then
1631 --assigning override burden cost to out params
1632 l_stage := 'Deriving burden cost from Override params';
1633 /* Assign burden cost*/
1634 If p_override_trxn_burden_cost is NOT NULL Then
1635 l_burden_cost := p_override_trxn_burden_cost;
1636
1637 Elsif p_override_burden_cost_rate is NOT NULL Then
1638 -- if quantity is zero this is amount based so multiply cost * rate
1639 If NVL(p_quantity,0) = 0 Then
1640 l_burden_cost := pa_currency.round_trans_currency_amt1
1641 (p_override_burden_cost_rate * x_trxn_raw_cost ,l_txn_curr_code ) ;
1642 Else
1643 l_burden_cost := pa_currency.round_trans_currency_amt1
1644 (p_override_burden_cost_rate * NVL(p_quantity,1),l_txn_curr_code ) ;
1645 End If;
1646 End If;
1647
1648 /* assign burden cost rate */
1649 If p_override_burden_cost_rate is NOT NULL Then
1650 l_burden_cost_rate := p_override_burden_cost_rate;
1651 Else
1652 If NVL(P_quantity, 0) <> 0 Then
1653 If l_burden_cost = l_txn_raw_cost Then
1654 l_burden_cost_rate := x_trxn_raw_cost_rate;
1655 Else
1656 l_burden_cost_rate := l_burden_cost / NVL(p_quantity,1) ;
1657 End If;
1658 Else
1659 l_burden_cost_rate := x_trxn_raw_cost_rate;
1660 End If;
1661 End If;
1662
1663 /* derive burden multiplier */
1664 If NVL(l_txn_raw_cost,0) <> 0 then
1665 l_burden_multiplier := (l_burden_cost-l_txn_raw_cost) / l_txn_raw_cost ;
1666 Else
1667 l_burden_multiplier := 0;
1668 End If;
1669
1670 ElsIf ( pa_cost1.check_proj_burdened(p_project_type,p_project_id) = 'Y' ) Then
1671
1672 l_stage := 'Calling PA_COST1.Get_burden_sch_details API';
1673 print_msg(l_debug_flag,l_stage);
1674 pa_cost1.Get_burden_sch_details
1675 (p_calling_mode => p_calling_mode
1676 ,p_exp_item_id => NULL
1677 ,p_trxn_type => NULL
1678 ,p_project_type => p_project_type
1679 ,p_project_id => p_project_id
1680 ,p_task_id => p_task_id
1681 ,p_exp_organization_id => l_rate_organization_id
1682 ,p_overide_organization_id => l_override_organization_id
1683 ,p_person_id => p_person_id
1684 ,p_expenditure_type => p_expenditure_type
1685 ,p_schedule_type => 'C'
1686 ,p_exp_item_date => p_exp_item_date
1687 ,p_trxn_curr_code => l_txn_curr_code
1688 ,p_burden_schedule_id => p_plan_cost_burden_sch_id
1689 ,x_schedule_id => l_burd_sch_id
1690 ,x_sch_revision_id => l_burd_sch_rev_id
1691 ,x_sch_fixed_date => l_burd_sch_fixed_date
1692 ,x_cost_base => l_burd_sch_cost_base
1693 ,x_cost_plus_structure => l_burd_sch_cp_structure
1694 ,x_compiled_set_id => l_burd_ind_compiled_set_id
1695 ,x_burden_multiplier => l_burden_multiplier
1696 ,x_return_status => l_return_status
1697 ,x_error_msg_code => l_msg_data
1698 );
1699 l_stage := 'After Get_Burdened_cost api return status['||l_return_status||']msgData['||l_msg_data||']' ;
1700 print_msg(l_debug_flag,l_stage);
1701
1702 If ( l_return_status <> 'S' OR l_burden_multiplier is NULL ) Then
1703 l_stage := 'Error while Calculating burden costs';
1704 x_burden_cost_rejection_code := substr(l_msg_data,1,30);
1705 Raise l_no_rate_found;
1706 End If;
1707
1708 /* Bug fix: 4240140 l_burden_cost := (l_txn_raw_cost * l_burden_multiplier) + l_txn_raw_cost ;
1709 If NVL(P_quantity, 0) <> 0 Then
1710 --assign raw cost rate to burden cost rate if burden cost is same as raw cost
1711 If l_burden_cost = l_txn_raw_cost Then
1712 l_burden_cost_rate := x_trxn_raw_cost_rate;
1713 Else
1714 l_burden_cost_rate := l_burden_cost / NVL(P_quantity, 1) ;
1715 End If;
1716 Else
1717 l_burden_cost_rate := x_trxn_raw_cost_rate;
1718 End If;
1719 */
1720 If (NVL(P_quantity, 0) <> 0 AND NVL(l_txn_raw_cost_rate,0) <> 0 ) Then
1721 l_burden_cost_rate := (l_txn_raw_cost_rate * l_burden_multiplier ) + l_txn_raw_cost_rate;
1722 l_burden_cost := pa_currency.round_trans_currency_amt1((P_quantity*l_burden_cost_rate),l_txn_curr_code);
1723 Else
1724 l_burden_cost := (l_txn_raw_cost * l_burden_multiplier) + l_txn_raw_cost ;
1725 If l_burden_cost = l_txn_raw_cost Then
1726 l_burden_cost_rate := l_txn_raw_cost_rate;
1727 Else
1728 l_burden_cost_rate := l_burden_cost / NVL(P_quantity, 1) ;
1729 End If;
1730 End If;
1731 x_burden_multiplier := l_burden_multiplier;
1732 x_cost_ind_compiled_set_id := l_burd_ind_compiled_set_id;
1733
1734 Else -- project type is not burdened
1735 --copy the raw cost to the burden costs
1736 l_stage := 'Copying raw costs to burden costs';
1737 print_msg(l_debug_flag,l_stage);
1738
1739 l_burden_cost := l_txn_raw_cost;
1740 l_burden_cost_rate := l_txn_raw_cost_rate;
1741 l_burden_multiplier := 0;
1742
1743 End IF;
1744
1745 End If; -- end of raw cost is not null
1746 --Assign values to the out variables
1747 x_trxn_burden_cost := l_burden_cost;
1748 x_trxn_burden_cost_rate := l_burden_cost_rate;
1749 x_burden_multiplier := NVL(l_burden_multiplier,0);
1750
1751 /* Raise invalid currency code when the derived rate sch currency code is different from
1752 * passed value override currency code
1753 */
1754 If p_override_trxn_curr_code is NOT NULL Then
1755 If p_override_trxn_curr_code <> NVL(l_txn_curr_code ,p_override_trxn_curr_code) Then
1756 l_stage := 'Invalid override currency is passed';
1757 print_msg(l_debug_flag,l_stage||'[costtxncur['||l_txn_curr_code||']OvrCur['||p_override_trxn_curr_code||']');
1758 Raise l_invalid_currency;
1759 End If;
1760 End if;
1761
1762 l_stage := 'End of Burden Calculation burdenCost['||x_trxn_burden_cost||']burdenCostrate['||x_trxn_burden_cost_rate||
1763 ']burdenMultiplier['||x_burden_multiplier||']' ;
1764 print_msg(l_debug_flag,l_stage);
1765 /* End of burden cost calculation */
1766
1767 x_error_msg_code := l_msg_data;
1768 x_return_status := l_return_status;
1769
1770 -- reset the error stack
1771 If l_debug_flag = 'Y' Then
1772 PA_DEBUG.reset_err_stack;
1773 End If;
1774
1775 EXCEPTION
1776 WHEN l_insufficient_parms THEN
1777 If NVL(p_calling_mode,'ACTUAL_RATES') = 'ACTUAL_RATES' Then
1778 If p_resource_class = 'PEOPLE' Then
1779 x_error_msg_code := 'PA_INVALID_ACT_PEOPLE_PARAMS';
1780 Elsif p_resource_class = 'EQUIPMENT' Then
1781 x_error_msg_code := 'PA_INVALID_ACT_EQUIP_PARAMS';
1782 Elsif p_resource_class = 'MATERIAL_ITEMS' Then
1783 x_error_msg_code := 'PA_INVALID_ACT_MAT_PARAMS';
1784 Elsif p_resource_class = 'FINANCIAL_ELEMENTS' Then
1785 x_error_msg_code := 'PA_INVALID_ACT_FIN_PARAMS';
1786 End If;
1787 Else
1788 If p_resource_class = 'PEOPLE' Then
1789 x_error_msg_code := 'PA_INVALID_PLAN_PEOPLE_PARAMS';
1790 Elsif p_resource_class = 'EQUIPMENT' Then
1791 x_error_msg_code := 'PA_INVALID_PLAN_EQUIP_PARAMS';
1792 Elsif p_resource_class = 'MATERIAL_ITEMS' Then
1793 x_error_msg_code := 'PA_INVALID_PLAN_MAT_PARAMS';
1794 Elsif p_resource_class = 'FINANCIAL_ELEMENTS' Then
1795 x_error_msg_code := 'PA_INVALID_PLAN_FIN_PARAMS';
1796 End If;
1797 End If;
1798 If x_error_msg_code is NULL Then
1799 x_error_msg_code := 'PA_COST1_INVALID_PARAMS';
1800 End If;
1801 x_return_status := 'E';
1802 print_msg(l_debug_flag,l_stage);
1803 If l_debug_flag = 'Y' Then
1804 PA_DEBUG.write_file('LOG',l_stage);
1805 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1806 PA_DEBUG.reset_err_stack;
1807 End If;
1808
1809 WHEN l_no_rate_found THEN
1810 x_error_msg_code := 'PA_FP_MISSING_RATE';
1811 x_return_status := 'E';
1812 print_msg(l_debug_flag,l_stage);
1813 If l_debug_flag = 'Y' Then
1814 PA_DEBUG.write_file('LOG',l_stage);
1815 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1816 PA_DEBUG.reset_err_stack;
1817 End If;
1818
1819 WHEN l_no_burdrate_found THEN
1820 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
1821 x_return_status := 'E';
1822 print_msg(l_debug_flag,l_stage);
1823 If l_debug_flag = 'Y' Then
1824 PA_DEBUG.write_file('LOG',l_stage);
1825 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1826 PA_DEBUG.reset_err_stack;
1827 End If;
1828
1829 WHEN l_invalid_override_attributes THEN
1830 x_error_msg_code := 'PA_INVALID_OVERRIDE_PARAM';
1831 x_return_status := 'E';
1832 print_msg(l_debug_flag,l_stage);
1833 If l_debug_flag = 'Y' Then
1834 PA_DEBUG.write_file('LOG',l_stage);
1835 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1836 PA_DEBUG.reset_err_stack;
1837 End If;
1838
1839 WHEN l_invalid_currency THEN
1840 x_error_msg_code := 'PA_INVALID_DENOM_CURRENCY';
1841 x_return_status := 'E';
1842 print_msg(l_debug_flag,l_stage);
1843 If l_debug_flag = 'Y' Then
1844 PA_DEBUG.write_file('LOG',l_stage);
1845 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']errCode['||l_err_code||']' );
1846 PA_DEBUG.reset_err_stack;
1847 End If;
1848
1849
1850 WHEN OTHERS THEN
1851 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
1852 x_return_status := 'E';
1853 x_error_msg_code := 'PA_FP_MISSING_RATE';
1854 Else
1855 x_return_status := 'U';
1856 x_error_msg_code := substr(SQLCODE||SQLERRM,1,30);
1857 End If;
1858 print_msg(l_debug_flag,l_stage);
1859 If l_debug_flag = 'Y' Then
1860 PA_DEBUG.write_file('LOG',l_stage);
1861 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']sqlcode['||sqlcode||']ErrMsg['||x_error_msg_code||']');
1862 PA_DEBUG.reset_err_stack;
1863 End If;
1864
1865 END Get_Plan_Actual_Cost_Rates;
1866
1867
1868 PROCEDURE Get_Non_Labor_raw_cost
1869 (p_project_id IN NUMBER
1870 ,p_task_id IN NUMBER
1871 ,p_non_labor_resource IN VARCHAR2
1872 ,p_nlr_organization_id IN NUMBER
1873 ,p_expenditure_type IN VARCHAR2
1874 ,p_exp_item_date IN DATE
1875 ,p_override_organization_id IN NUMBER
1876 ,p_quantity IN NUMBER
1877 ,p_org_id IN NUMBER
1878 ,p_nlr_schedule_id IN NUMBER
1879 ,p_nlr_trxn_cost_rate IN NUMBER DEFAULT NULL
1880 ,p_nlr_trxn_raw_cost IN NUMBER DEFAULT NULL
1881 ,p_nlr_trxn_currency_code IN VARCHAR2 DEFAULT NULL
1882 ,x_trxn_raw_cost_rate OUT NOCOPY NUMBER
1883 ,x_trxn_raw_cost OUT NOCOPY NUMBER
1884 ,x_txn_currency_code OUT NOCOPY VARCHAR2
1885 ,x_return_status OUT NOCOPY VARCHAR2
1886 ,x_error_msg_code OUT NOCOPY VARCHAR2
1887 ) IS
1888
1889 l_return_status varchar2(1000) := 'S';
1890 l_msg_data varchar2(1000);
1891 l_msg_count Number;
1892 l_stage varchar2(1000);
1893 l_debug_flag varchar2(10);
1894 l_exp_cost_rate_flag varchar2(100);
1895 l_nlr_raw_cost Number;
1896 l_nlr_raw_cost_rate Number;
1897 l_nlr_txn_curr_code varchar2(100);
1898 l_cost_rate_curr_code varchar2(100);
1899
1900
1901 Cursor cur_nlr_sch_details IS
1902 SELECT sch.rate_sch_currency_code
1903 ,rates.rate
1904 FROM pa_std_bill_rate_schedules_all sch
1905 ,pa_bill_rates_all rates
1906 WHERE sch.bill_rate_sch_id = p_nlr_schedule_id
1907 AND sch.schedule_type = 'NON-LABOR'
1908 AND rates.bill_rate_sch_id = sch.bill_rate_sch_id
1909 AND rates.expenditure_type = p_expenditure_type
1910 AND ( rates.non_labor_resource is NULL
1911 OR rates.non_labor_resource = p_non_labor_resource
1912 )
1913 AND trunc(p_exp_item_date) between trunc(rates.start_date_active)
1914 and trunc(nvl(rates.end_date_active,p_exp_item_date))
1915 /*Bug fix:3793618 This is to ensure that records with NLR and Exp combo orders first */
1916 ORDER BY decode(rates.non_labor_resource,p_non_labor_resource,0,1),rates.expenditure_type ;
1917
1918 BEGIN
1919 --- Initialize the error statck
1920 If g_debug_flag is NULL Then
1921 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
1922 g_debug_flag := NVL(g_debug_flag, 'N');
1923 End If;
1924 l_debug_flag := NVL(g_debug_flag,'N');
1925 IF l_debug_flag = 'Y' Then
1926 PA_DEBUG.init_err_stack ('PA_COST1.Get_Non_Labor_raw_cost');
1927 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
1928 ,x_write_file => 'LOG'
1929 ,x_debug_mode => l_debug_flag
1930 );
1931 End If;
1932 --Initialize the out varibales
1933 l_return_status := 'S';
1934 l_msg_data := Null;
1935 l_nlr_txn_curr_code := p_nlr_trxn_currency_code;
1936 l_cost_rate_curr_code := Null;
1937
1938 l_exp_cost_rate_flag := check_expCostRateFlag(p_expenditure_type);
1939
1940 l_stage := 'Inside PA_COST1.Get_Non_Labor_raw_cost API CostRateFlag['||l_exp_cost_rate_flag||']' ;
1941 print_msg(l_debug_flag,l_stage);
1942
1943 /* get the currency code */
1944 l_stage := 'Getting currency code the for the given OU';
1945 print_msg(l_debug_flag,l_stage);
1946 l_cost_rate_curr_code := Get_curr_code(p_org_id => p_org_id );
1947
1948 If l_cost_rate_curr_code is NULL Then
1949 l_stage :='Currency not found for the OU';
1950 print_msg(l_debug_flag,l_stage);
1951 l_return_status := 'E';
1952 End If;
1953
1954 IF l_exp_cost_rate_flag = 'N' Then
1955 l_nlr_raw_cost_rate := 1;
1956
1957 ELSE
1958 -- check the cost rates available at non labor resource level if no rate found then
1959 -- get the rates at expendityre type level, if no rate found then return error
1960 BEGIN
1961
1962 /** commented as NLR schedule is not used to derive cost rates
1963 If p_nlr_schedule_id is NOT NULL Then
1964 Open cur_nlr_sch_details;
1965 Fetch cur_nlr_sch_details
1966 INTO l_cost_rate_curr_code
1967 ,l_nlr_raw_cost_rate;
1968 Close cur_nlr_sch_details;
1969 End If;
1970 **/
1971
1972 IF l_cost_rate_curr_code IS NOT NULL Then
1973
1974 /* bug fix: 3819799 changed the order of the table. Now first hit the PA_Expenditure_Cost_Rates_all instead of
1975 * PA_Expenditure_Types. This will avoid the following issue
1976 * PA_USAGE_COST_RATE_OVR_ALL is being referenced more than 3 times.
1977 * Single-row table count exceeds 3 for PA_USAGE_COST_RATE_OVR_ALL.
1978 */
1979 l_stage := 'Getting Cost rates from Usage Overrides';
1980 print_msg(l_debug_flag,l_stage);
1981 SELECT R.Rate
1982 INTO l_nlr_raw_cost_rate
1983 FROM PA_Expenditure_Types T,
1984 PA_Usage_Cost_Rate_Ovr_all R
1985 WHERE T.Expenditure_type = R.Expenditure_type
1986 AND T.Cost_Rate_Flag = 'Y'
1987 AND R.Expenditure_type = p_expenditure_type
1988 AND R.Non_Labor_Resource = p_Non_Labor_Resource
1989 AND R.Organization_Id = NVL(p_nlr_organization_id,p_override_organization_id)
1990 AND NVL(R.org_id,-99) = NVL(p_org_id,-99)
1991 AND trunc(p_exp_item_date)
1992 BETWEEN R.Start_Date_Active
1993 AND NVL(R.End_Date_Active, p_exp_item_date);
1994
1995 END If;
1996
1997 l_stage := 'costRateCur['||l_cost_rate_curr_code||']RawCostRate['||l_nlr_raw_cost_rate||']' ;
1998 print_msg(l_debug_flag,l_stage);
1999
2000 EXCEPTION
2001
2002 WHEN NO_DATA_FOUND THEN
2003
2004 BEGIN
2005 l_stage := 'Getting Cost rates from expenditure type cost rates';
2006 print_msg(l_debug_flag,l_stage);
2007 l_nlr_raw_cost_rate := GetExpTypeCostRate
2008 (p_exp_type => p_expenditure_type
2009 ,p_exp_item_date => p_exp_item_date
2010 ,p_org_id => p_org_id
2011 );
2012 l_stage := 'costRateCur['||l_cost_rate_curr_code||']RawCostRate['||l_nlr_raw_cost_rate||']' ;
2013 print_msg(l_debug_flag,l_stage);
2014
2015 If l_nlr_raw_cost_rate is NULL Then
2016 l_msg_data := 'PA_NLR_NO_RATE_FOUND';
2017 l_return_status := 'E';
2018 l_stage := 'No Rates found for Non-labor resources';
2019 End If;
2020 END;
2021
2022 WHEN OTHERS THEN
2023 l_return_status := 'U';
2024 l_msg_data := SQLCODE||SQLERRM;
2025 END;
2026
2027 END IF;
2028
2029 If p_nlr_trxn_raw_cost is NOT NULL Then
2030 l_nlr_raw_cost := p_nlr_trxn_raw_cost;
2031 ElsIF l_cost_rate_curr_code is NOT NULL Then
2032 If l_nlr_raw_cost_rate is NOT NULL Then
2033 l_nlr_raw_cost := pa_currency.round_trans_currency_amt1
2034 (l_nlr_raw_cost_rate * NVL(p_quantity,0), l_cost_rate_curr_code );
2035 End If;
2036
2037 End If;
2038 -- Assign the output variables with the derived values
2039 x_return_status := l_return_status;
2040 x_error_msg_code := l_msg_data;
2041 x_trxn_raw_cost := l_nlr_raw_cost;
2042 x_trxn_raw_cost_rate:= l_nlr_raw_cost_rate;
2043 x_txn_currency_code := NVL(l_cost_rate_curr_code,l_nlr_txn_curr_code);
2044
2045 -- reset the error stack
2046 If l_debug_flag = 'Y' Then
2047 PA_DEBUG.reset_err_stack;
2048 End If;
2049
2050 EXCEPTION
2051
2052 WHEN OTHERS THEN
2053 print_msg(l_debug_flag,l_stage);
2054 If l_debug_flag = 'Y' Then
2055 PA_DEBUG.write_file('LOG',l_stage);
2056 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']ErrMsg['||l_msg_data||']' );
2057 PA_DEBUG.reset_err_stack;
2058 End If;
2059 END Get_Non_Labor_raw_cost;
2060
2061 /* This is a wrapper api to derive compiled set id and burden multiplier
2062 * Which in turn makes calls to pa_cost_plus package
2063 * p_calling_mode IN required for PLAN_RATES
2064 * p_burden_schedule_id IN required for PLAN_RATES
2065 */
2066 PROCEDURE Get_burden_sch_details
2067 (p_calling_mode IN VARCHAR2 DEFAULT 'ACTUAL_RATES'
2068 ,p_exp_item_id IN NUMBER
2069 ,p_trxn_type IN VARCHAR2
2070 ,p_project_type IN VARCHAR2
2071 ,p_project_id IN NUMBER
2072 ,p_task_id IN NUMBER
2073 ,p_exp_organization_id IN NUMBER
2074 /* bug fix:4232181 Derive organization override for burden calculate */
2075 ,p_overide_organization_id IN NUMBER DEFAULT NULL
2076 ,p_person_id IN NUMBER DEFAULT NULL
2077 /* end of bug fix:4232181 */
2078 ,p_expenditure_type IN VARCHAR2
2079 ,p_schedule_type IN VARCHAR2 DEFAULT 'C'
2080 ,p_exp_item_date IN DATE
2081 ,p_trxn_curr_code IN VARCHAR2
2082 ,p_burden_schedule_id IN NUMBER DEFAULT NULL
2083 ,x_schedule_id OUT NOCOPY NUMBER
2084 ,x_sch_revision_id OUT NOCOPY NUMBER
2085 ,x_sch_fixed_date OUT NOCOPY DATE
2086 ,x_cost_base OUT NOCOPY VARCHAR2
2087 ,x_cost_plus_structure OUT NOCOPY VARCHAR2
2088 ,x_compiled_set_id OUT NOCOPY NUMBER
2089 ,x_burden_multiplier OUT NOCOPY NUMBER
2090 ,x_return_status OUT NOCOPY VARCHAR2
2091 ,x_error_msg_code OUT NOCOPY VARCHAR2 ) IS
2092
2093 l_exp_item_id NUMBER := p_exp_item_id;
2094 l_trxn_type VARCHAR2(150) := p_trxn_type;
2095 l_project_type VARCHAR2(150) := p_project_type;
2096 l_project_id NUMBER := p_project_id;
2097 l_task_id NUMBER := p_task_id;
2098 l_exp_organization_id NUMBER := p_exp_organization_id;
2099 l_overide_organization_id NUMBER := p_overide_organization_id;
2100 l_person_id NUMBER := p_person_id;
2101 l_schedule_type VARCHAR2(150) := p_schedule_type;
2102 l_exp_item_date DATE := p_exp_item_date;
2103 l_trxn_curr_code VARCHAR2(150) := p_trxn_curr_code;
2104 l_expenditure_type VARCHAR2(150) := p_expenditure_type;
2105 l_schedule_id NUMBER := NULL;
2106 l_sch_revision_id NUMBER := NULL;
2107 l_sch_fixed_date DATE := NULL;
2108 l_cost_base VARCHAR2(150) := NULL;
2109 l_cost_plus_structure VARCHAR2(150) := NULL;
2110 l_compiled_set_id NUMBER := NULL;
2111 l_burden_multiplier NUMBER := NULL;
2112 l_return_status VARCHAR2(100) := 'S';
2113 l_error_msg_code VARCHAR2(1000) := NULL;
2114 l_stage VARCHAR2(1000) := NULL;
2115 l_err_code VARCHAR2(1000) := NULL;
2116 l_debug_flag VARCHAR2(100) := 'N';
2117 l_status VARCHAR2(100) := NULL;
2118 l_msg_count NUMBER := 0;
2119
2120 L_INVALID_SCHEDULE EXCEPTION;
2121 L_NO_MULTIPLIER EXCEPTION;
2122 L_NO_COMPILED_SET EXCEPTION;
2123 L_INVALID_ERROR EXCEPTION;
2124 L_NO_COST_BASE EXCEPTION;
2125
2126 BEGIN
2127
2128 --- Initialize the error statck
2129 If g_debug_flag is NULL Then
2130 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
2131 g_debug_flag := NVL(g_debug_flag, 'N');
2132 End If;
2133 l_debug_flag := NVL(g_debug_flag,'N');
2134 IF l_debug_flag = 'Y' Then
2135 PA_DEBUG.init_err_stack ('PA_COST1.Get_burden_sch_details');
2136 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2137 ,x_write_file => 'LOG'
2138 ,x_debug_mode => l_debug_flag
2139 );
2140 End If;
2141 l_return_status := 'S';
2142 l_error_msg_code := NULL;
2143 x_return_status := 'S';
2144 x_error_msg_code := NULL;
2145
2146
2147 l_stage := 'Inside Get_burden_sch_details params ProjId['||l_project_id||']ProjType['||l_project_type||
2148 ']TaskId['||l_task_id||']expOrgId['||l_exp_organization_id||']SchType['||l_schedule_type||
2149 ']ExpType['||l_expenditure_type||']CurrCode['||l_trxn_curr_code||']EiDate['||l_exp_item_date||
2150 ']BurdenSchId['||p_burden_schedule_id||']CallingMode['||p_calling_mode||']' ;
2151 print_msg(l_debug_flag,l_stage);
2152
2153 If ( l_schedule_type is NOT NULL and check_proj_burdened (p_project_type ,p_project_id ) = 'Y') Then
2154 --call the api to get the schedule info
2155 l_stage := 'Calling pa_cost_plus.find_rate_sch_rev_id to get sch Id and RevId';
2156 print_msg(l_debug_flag,l_stage);
2157 If l_schedule_type = 'COST' Then
2158 l_schedule_type := 'C';
2159 Elsif l_schedule_type = 'REVENUE' Then
2160 l_schedule_type := 'R';
2161 Elsif l_schedule_type is NULL Then
2162 l_schedule_type := 'C';
2163 End If;
2164
2165 /* Derive the burden schedule revision based on the calling mode
2166 * if calling mode is PLAN_RATES then schedule is already passed
2167 * else schedule id should be derived based the given task or project */
2168 If p_calling_mode = 'ACTUAL_RATES' Then
2169
2170 IF (l_task_id is NULL OR is_workPlan_Task(l_project_id,l_task_id) = 'Y') Then
2171 -- For Task Effort calculation the task id will be passed NULL so
2172 -- derive the schedule for the given project
2173 get_projLevel_BurdSchds
2174 (p_project_id => l_project_id
2175 ,p_task_id => NULL
2176 ,p_exp_item_date => l_exp_item_date
2177 ,p_burden_sch_id => p_burden_schedule_id
2178 ,x_burden_sch_id => l_schedule_id
2179 ,x_burden_sch_revision_id => l_sch_revision_id
2180 ,x_status => l_status
2181 );
2182
2183 ELSE -- get details for the given task
2184
2185 pa_cost_plus.find_rate_sch_rev_id
2186 (l_exp_item_id --transaction_id
2187 ,l_trxn_type --transaction_type
2188 ,l_task_id --t_id
2189 ,l_schedule_type --schedule_type
2190 ,l_exp_item_date --exp_item_date
2191 ,l_schedule_id --x_sch_id
2192 ,l_sch_revision_id --x_rate_sch_rev_id
2193 ,l_sch_fixed_date --x_sch_fixed_date
2194 ,l_status --x_status
2195 ,l_error_msg_code --x_stage
2196 );
2197
2198 END IF;
2199
2200 If l_status <> 0 Then
2201
2202 l_stage := 'No Schedule or Revision found';
2203 print_msg(l_debug_flag,l_stage);
2204 Raise l_invalid_schedule;
2205 End If;
2206
2207 ElsIf p_calling_mode = 'PLAN_RATES' Then
2208 /* get the schedule revision id for the given schedule
2209 * For Task Effort calculation the task id will be passed NULL so
2210 * derive the schedule for the given project
2211 */
2212 l_schedule_id := p_burden_schedule_id;
2213 get_projLevel_BurdSchds
2214 (p_project_id => l_project_id
2215 ,p_task_id => l_task_id
2216 ,p_exp_item_date => l_exp_item_date
2217 ,p_burden_sch_id => p_burden_schedule_id
2218 ,x_burden_sch_id => l_schedule_id
2219 ,x_burden_sch_revision_id => l_sch_revision_id
2220 ,x_status => l_status
2221 );
2222 If l_status <> 0 Then
2223 l_stage := 'No Schedule Revision found nor Compiled for Planning burden Rate Schedule';
2224 print_msg(l_debug_flag,l_stage);
2225 Raise l_invalid_schedule;
2226 End If;
2227
2228 End If;
2229
2230 If l_sch_revision_id is NOT NULL Then
2231 l_stage := 'Calling pa_cost_plus.get_cost_plus_structure api';
2232 print_msg(l_debug_flag,l_stage);
2233 pa_cost_plus.get_cost_plus_structure
2234 (l_sch_revision_id
2235 ,l_cost_plus_structure
2236 ,l_status
2237 ,l_error_msg_code );
2238
2239 If l_status <> 0 Then
2240 l_stage := 'No Cost Plus Structure';
2241 print_msg(l_debug_flag,l_stage);
2242 Raise L_INVALID_ERROR;
2243 End If;
2244 End If;
2245
2246 If l_cost_plus_structure is NOT NULL Then
2247 l_stage := 'Calling pa_cost_plus.get_cost_base api';
2248 print_msg(l_debug_flag,l_stage);
2249 pa_cost_plus.get_cost_base
2250 (l_expenditure_type
2251 ,l_cost_plus_structure
2252 ,l_cost_base
2253 ,l_status
2254 ,l_error_msg_code );
2255
2256 If l_status <> 0 Then
2257 l_stage := 'No Cost base found Status['||l_status||']';
2258 print_msg(l_debug_flag,l_stage);
2259 Raise L_NO_COST_BASE;
2260 End If;
2261
2262
2263 End If;
2264
2265 If l_cost_base is NOT NULL Then
2266 /* Bug fix:4232181 Get the override organization Id from the project level org overrides */
2267 /* sent mail to anders, if its ok to call this for all resource classes once receiving the responce
2268 * the comment has to be opened
2269 IF l_overide_organization_id is NULL Then
2270 l_stage := 'Calling pa_cost.Override_exp_organization api From Burden sch api';
2271 print_msg(l_debug_flag,l_stage);
2272 pa_cost.Override_exp_organization
2273 (P_item_date => l_exp_item_date
2274 ,P_person_id => l_person_id
2275 ,P_project_id => l_project_id
2276 ,P_incurred_by_organz_id => l_exp_organization_id
2277 ,P_Expenditure_type => l_expenditure_type
2278 ,X_overr_to_organization_id => l_overide_organization_id
2279 ,X_return_status => l_return_status
2280 ,X_msg_count => l_msg_count
2281 ,X_msg_data => l_error_msg_code
2282 );
2283 l_stage := 'Return status of pa_cost.Override_exp_organization ['||l_return_status||']';
2284 l_stage := l_stage||']msgData['||l_error_msg_code||']OverideOrg['||l_overide_organization_id||']' ;
2285 print_msg(l_debug_flag,l_stage);
2286 End If;
2287 **/
2288 l_stage := 'Calling pa_cost_plus.get_compiled_set_id api';
2289 print_msg(l_debug_flag,l_stage);
2290 pa_cost_plus.get_compiled_set_id
2291 (l_sch_revision_id
2292 ,NVL(l_overide_organization_id,l_exp_organization_id)
2293 ,l_cost_base
2294 ,l_compiled_set_id
2295 ,l_status
2296 ,l_error_msg_code );
2297
2298 If l_status <> 0 Then
2299 l_stage := 'No Cost Ind Compiled SetId exists';
2300 print_msg(l_debug_flag,l_stage);
2301 Raise L_NO_COMPILED_SET;
2302 End If;
2303 End If;
2304
2305 IF l_compiled_set_id is NOT NULL Then
2306 l_stage := 'Calling pa_cost_plus.get_compiled_multiplier api';
2307 print_msg(l_debug_flag,l_stage);
2308 pa_cost_plus.get_compiled_multiplier
2309 (NVL(l_overide_organization_id,l_exp_organization_id)
2310 ,l_cost_base
2311 ,l_sch_revision_id
2312 ,l_burden_multiplier
2313 ,l_status
2314 ,l_error_msg_code );
2315
2316 If l_status <> 0 Then
2317 l_stage := 'No Compiled Multiplier exists';
2318 print_msg(l_debug_flag,l_stage);
2319 Raise L_NO_MULTIPLIER;
2320 End If;
2321 End If;
2322 End If; --end of task id not null
2323
2324 --Assign the values to out params
2325 x_schedule_id := l_schedule_id;
2326 x_sch_revision_id := l_sch_revision_id;
2327 x_sch_fixed_date := l_sch_fixed_date;
2328 x_cost_base := l_cost_base;
2329 x_cost_plus_structure := l_cost_plus_structure;
2330 x_compiled_set_id := l_compiled_set_id;
2331 x_burden_multiplier := l_burden_multiplier;
2332 x_return_status := NVL(l_return_status,'S');
2333 x_error_msg_code := substr(l_error_msg_code,1,30);
2334
2335 l_stage := 'Out Param Values SchId['||x_schedule_id||']SchRev['||x_sch_revision_id||']Schdate['||x_sch_fixed_date||
2336 ']Costbase['||x_cost_base||']CPStruc['||x_cost_plus_structure||']Compilset['||x_compiled_set_id||
2337 ']BurdMulti['||x_burden_multiplier||']retSts['||x_return_status||']ErrMsg['||x_error_msg_code||']' ;
2338 print_msg(l_debug_flag,l_stage);
2339
2340 --reset error stack
2341 If l_debug_flag = 'Y' Then
2342 PA_DEBUG.reset_err_stack;
2343 End If;
2344
2345 EXCEPTION
2346 WHEN l_invalid_schedule Then
2347 x_return_status := 'E';
2348 x_error_msg_code := 'PA_FCST_INVL_BURDEN_SCH_REV_ID';
2349 If l_debug_flag = 'Y' Then
2350 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2351 PA_DEBUG.write_file('LOG',l_stage);
2352 PA_DEBUG.reset_err_stack;
2353 End If;
2354
2355 WHEN l_no_cost_base Then
2356 If l_status = 100 Then
2357 -- ie. expenditure type is not part of the burdening
2358 -- so set the return status to success
2359 x_return_status := 'S';
2360 x_error_msg_code := NULL;
2361 x_burden_multiplier := 0;
2362 Else
2363 --ie. some unexpected error happened
2364 x_return_status := 'E';
2365 x_error_msg_code := 'PA_FCST_NO_COST_BASE';
2366
2367 End If;
2368 If l_debug_flag = 'Y' Then
2369 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2370 PA_DEBUG.write_file('LOG',l_stage);
2371 PA_DEBUG.reset_err_stack;
2372 End If;
2373
2374 WHEN l_no_compiled_set Then
2375 If p_exp_organization_id is NULL Then
2376 -- ie. expenditure organization id is not passed then
2377 -- set the multiplier as zero so that burdened cost = raw cost
2378 x_return_status := 'S';
2379 x_error_msg_code := NULL;
2380 x_burden_multiplier := 0;
2381 Else
2382 x_return_status := 'E';
2383 x_error_msg_code := 'PA_NO_COMPILED_SET_ID';
2384 End If;
2385 If l_debug_flag = 'Y' Then
2386 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2387 PA_DEBUG.write_file('LOG',l_stage);
2388 PA_DEBUG.reset_err_stack;
2389 End If;
2390
2391
2392 WHEN l_no_multiplier Then
2393 x_return_status := 'E';
2394 x_error_msg_code := 'PA_FCST_NO_COMPILED_MULTI';
2395 If l_debug_flag = 'Y' Then
2396 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2397 PA_DEBUG.write_file('LOG',l_stage);
2398 PA_DEBUG.reset_err_stack;
2399 End If;
2400
2401 WHEN l_invalid_error Then
2402 x_return_status := 'E';
2403 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
2404 If l_debug_flag = 'Y' Then
2405 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2406 PA_DEBUG.write_file('LOG',l_stage);
2407 PA_DEBUG.reset_err_stack;
2408 End If;
2409
2410 WHEN OTHERS THEN
2411 IF to_char(sqlcode) in ('00100','01403','100','1403') Then
2412 x_return_status := 'E';
2413 x_error_msg_code := 'PA_CALC_BURDENED_COST_FAILED';
2414 Else
2415 x_return_status := 'U';
2416 x_error_msg_code := substr(SQLCODE||SQLERRM,1,30);
2417 End If;
2418 If l_debug_flag = 'Y' Then
2419 PA_DEBUG.write_file('ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2420 PA_DEBUG.write_file('LOG',l_stage);
2421 PA_DEBUG.reset_err_stack;
2422 End If;
2423
2424
2425 END Get_burden_sch_details;
2426
2427 /* This is an internal API which will be called from Convert_COSTto PC and PFC api
2428 * this api does the calculation for amount conversion based on the planning conversion
2429 * attributes
2430 */
2431 PROCEDURE Convert_amounts
2432 (p_calling_mode IN VARCHAR2 DEFAULT 'PC'
2433 ,p_txn_raw_cost IN NUMBER
2434 ,p_txn_burden_cost IN NUMBER
2435 ,p_txn_quantity IN NUMBER
2436 ,p_Conversion_Date IN DATE
2437 ,p_From_curr_code IN VARCHAR2
2438 ,p_To_curr_code IN VARCHAR2
2439 ,p_To_Curr_Rate_Type IN VARCHAR2
2440 ,p_To_Curr_Exchange_Rate IN NUMBER
2441 ,x_To_Curr_raw_cost OUT NOCOPY NUMBER
2442 ,x_To_Curr_raw_cost_rate OUT NOCOPY NUMBER
2443 ,x_To_Curr_burden_cost OUT NOCOPY NUMBER
2444 ,x_To_Curr_burden_cost_rate OUT NOCOPY NUMBER
2445 ,x_To_Curr_Exchange_Rate OUT NOCOPY NUMBER
2446 ,x_return_status OUT NOCOPY VARCHAR2
2447 ,x_error_msg_code OUT NOCOPY VARCHAR2
2448 ) IS
2449
2450 l_denominator Number;
2451 l_numerator Number ;
2452 l_rate Number ;
2453 l_calling_mode Varchar2(100) := NVL(p_calling_mode,'PC');
2454 l_usrRateAllowed Varchar2(100);
2455 INVALID_CURRENCY Exception;
2456 NO_RATE Exception;
2457
2458 BEGIN
2459 -- Initialize the out variables
2460 x_return_status := 'S';
2461 x_error_msg_code := NULL;
2462
2463 If p_From_curr_code = p_To_curr_code Then
2464 x_To_Curr_raw_cost := p_txn_raw_cost;
2465 x_To_Curr_burden_cost := p_txn_burden_cost;
2466 x_To_Curr_Exchange_Rate := p_To_Curr_Exchange_Rate ;
2467 If NVL(p_txn_quantity,0) <> 0 Then
2468 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2469 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2470 Else
2471 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2472 If NVL(p_txn_burden_cost,0) <> 0 Then
2473 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2474 End If;
2475 End If;
2476 Else
2477 If p_To_Curr_Rate_Type = 'User' Then
2478 -- check if the user rate type is allowed for this currency
2479 l_usrRateAllowed := pa_multi_currency.is_user_rate_type_allowed
2480 (P_from_currency => p_From_curr_code
2481 ,P_to_currency => p_To_curr_code
2482 ,P_conversion_date => p_Conversion_Date );
2483 If NVL(l_usrRateAllowed,'N') = 'Y' Then
2484 If p_To_Curr_Exchange_Rate is NOT NULL Then
2485
2486 x_To_Curr_raw_cost := pa_currency.round_trans_currency_amt1
2487 (p_txn_raw_cost * NVL(p_To_Curr_Exchange_Rate,1),P_to_curr_code);
2488 x_To_Curr_burden_cost := pa_currency.round_trans_currency_amt1
2489 (p_txn_burden_cost * NVL(p_To_Curr_Exchange_Rate,1),P_to_curr_code);
2490 x_To_Curr_Exchange_Rate := p_To_Curr_Exchange_Rate ;
2491
2492 If NVL(p_txn_quantity,0) <> 0 Then
2493 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2494 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2495 Else
2496 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2497 If NVL(p_txn_burden_cost,0) <> 0 Then
2498 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2499 End If;
2500 End If;
2501
2502
2503 Else
2504 x_return_status := 'E';
2505 If l_calling_mode = 'PC' Then
2506 x_error_msg_code := 'PA_FP_PJ_COST_RATE_NOT_DEFINED';
2507 Else
2508 x_error_msg_code := 'PA_FP_PF_COST_RATE_NOT_DEFINED';
2509 End If;
2510 End If;
2511
2512 Else -- user rate type is not allowed so error out
2513 x_return_status := 'E';
2514 If l_calling_mode = 'PC' Then
2515 x_error_msg_code := 'PA_FP_PJC_USR_RATE_NOT_ALLOWED';
2516 Else
2517 x_error_msg_code := 'PA_FP_PFC_USR_RATE_NOT_ALLOWED';
2518 End If;
2519
2520 End If; -- End of userRateAllowed
2521
2522 ELse
2523 -- Call GL conversion api to derive the exchagne rate
2524 BEGIN
2525 print_msg('Calling Gl_currency_api.get_triangulation_rate');
2526 Gl_currency_api.get_triangulation_rate (
2527 x_from_currency => p_From_curr_code
2528 ,x_to_currency => p_To_Curr_code
2529 ,x_conversion_date => p_Conversion_date
2530 ,x_conversion_type => p_To_Curr_rate_Type
2531 ,x_denominator => l_denominator
2532 ,x_numerator => l_numerator
2533 ,x_rate => l_rate
2534 );
2535 x_To_Curr_raw_cost := pa_currency.round_trans_currency_amt1
2536 (p_txn_raw_cost * NVL(l_rate,1),p_To_Curr_code);
2537 x_To_Curr_burden_cost := pa_currency.round_trans_currency_amt1
2538 (p_txn_burden_cost * NVL(l_rate,1),p_To_Curr_code);
2539 x_To_Curr_Exchange_Rate := l_rate ;
2540
2541 If NVL(p_txn_quantity,0) <> 0 Then
2542 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost / p_txn_quantity ;
2543 x_To_Curr_burden_cost_rate := x_To_Curr_burden_cost / p_txn_quantity ;
2544 Else
2545 x_To_Curr_raw_cost_rate := x_To_Curr_raw_cost;
2546 If NVL(p_txn_burden_cost,0) <> 0 Then
2547 x_To_Curr_burden_cost_rate := x_To_Curr_raw_cost_rate;
2548 End If;
2549 End If;
2550
2551 EXCEPTION
2552 WHEN OTHERS then
2553 x_return_status := 'E';
2554 If ( l_denominator = -2 OR l_denominator = -2 ) Then
2555 x_error_msg_code := 'PA_FP_CURR_NOT_VALID';
2556 Else
2557 If l_calling_mode = 'PC' Then
2558 x_error_msg_code := 'PA_FP_NO_PJ_EXCH_RATE_EXISTS';
2559 Else
2560 x_error_msg_code := 'PA_FP_NO_PF_EXCH_RATE_EXISTS';
2561 End If;
2562
2563 End If;
2564 END;
2565
2566 End If; -- end of User Type
2567
2568 End If; -- End of From Curr <> To Curr
2569
2570 Return;
2571
2572 END Convert_amounts;
2573
2574
2575
2576 /* This API converts the cost amount from transaction currency to
2577 * project and project functional currency based on the
2578 * planning transaction currency conversion attributes
2579 * NOTE: Please donot use this API for actual cost conversion
2580 */
2581 PROCEDURE Convert_COST_TO_PC_PFC
2582 (p_txn_raw_cost IN NUMBER
2583 ,p_txn_burden_cost IN NUMBER
2584 ,p_txn_quantity IN NUMBER
2585 ,p_txn_curr_code IN VARCHAR2
2586 ,p_txn_date IN DATE
2587 ,p_project_id IN NUMBER
2588 ,p_budget_Version_id IN NUMBER
2589 ,p_budget_Line_id IN NUMBER
2590 ,x_project_curr_code OUT NOCOPY VARCHAR2
2591 ,x_projfunc_curr_code OUT NOCOPY VARCHAR2
2592 ,x_proj_raw_cost OUT NOCOPY NUMBER
2593 ,x_proj_raw_cost_rate OUT NOCOPY NUMBER
2594 ,x_proj_burdened_cost OUT NOCOPY NUMBER
2595 ,x_proj_burdened_cost_rate OUT NOCOPY NUMBER
2596 ,x_projfunc_raw_cost OUT NOCOPY NUMBER
2597 ,x_projfunc_raw_cost_rate OUT NOCOPY NUMBER
2598 ,x_projfunc_burdened_cost OUT NOCOPY NUMBER
2599 ,x_projfunc_burdened_cost_rate OUT NOCOPY NUMBER
2600 ,x_return_status OUT NOCOPY VARCHAR2
2601 ,x_error_msg_code OUT NOCOPY VARCHAR2
2602 ) IS
2603 l_insufficient_parms EXCEPTION;
2604 l_No_budget_version EXCEPTION;
2605 l_return_status VARCHAR2(100);
2606 l_error_msg_code VARCHAR2(100);
2607 l_stage VARCHAR2(1000);
2608 l_debug_flag VARCHAR2(10);
2609 l_txn_curr_code VARCHAR2(100);
2610 l_project_id NUMBER;
2611 l_budget_version_id NUMBER;
2612 l_resource_assignment_id NUMBER;
2613 /* project attributes */
2614 l_project_curr_code VARCHAR2(100);
2615 l_project_rate_type VARCHAR2(100);
2616 l_project_rate_date_type VARCHAR2(100);
2617 l_project_rate_date DATE;
2618 l_project_exchange_rate NUMBER;
2619 /* project functional attributes */
2620 l_projfunc_curr_code VARCHAR2(100);
2621 l_projfunc_rate_type VARCHAR2(100);
2622 l_projfunc_rate_date_type VARCHAR2(100);
2623 l_projfunc_rate_date DATE;
2624 l_projfunc_exchange_rate NUMBER;
2625
2626 l_proj_raw_cost NUMBER;
2627 l_proj_raw_cost_rate NUMBER;
2628 l_proj_burden_cost NUMBER;
2629 l_proj_burden_cost_rate NUMBER;
2630 l_projfunc_raw_cost NUMBER;
2631 l_projfunc_raw_cost_rate NUMBER;
2632 l_projfunc_burden_cost NUMBER;
2633 l_projfunc_burden_cost_rate NUMBER;
2634
2635 CURSOR cur_currencyAttribs(lv_budget_version_id Number,lv_resource_assignment_id Number) IS
2636 SELECT pp.project_id
2637 ,bv.budget_version_id
2638 ,cur.txn_currency_code txn_currency_code
2639 /* budget line currency attributes selected for testing
2640 --,bl.project_currency_code bgl_project_curr_code
2641 --,bl.projfunc_currency_code bgl_projfunc_curr_code
2642 --,bl.project_cost_rate_type bgl_project_rate_type
2643 --,bl.project_cost_rate_date_type bgl_project_rate_date_type
2644 --,bl.start_date bgl_project_rate_date
2645 --,bl.project_cost_exchange_rate bgl_project_exchange_rate */
2646 /* -----------Project Currency conversion Atrributes -----------------------------------------*/
2647 ,NVL(bl.project_currency_code,pp.project_currency_code) project_currency_code
2648 ,NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type) project_rate_type
2649 ,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
2650 ,'User',NULL
2651 ,NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)) project_rate_date_type
2652 ,decode(NVL(bl.project_cost_rate_date_type,fpo.project_cost_rate_date_type)
2653 ,'START_DATE', NVL(bl.start_date,p_txn_date)
2654 ,'END_DATE' , NVL(bl.end_date,p_txn_date)
2655 , NVL(bl.project_cost_rate_date,Nvl(fpo.project_cost_rate_date,p_txn_date))) project_rate_date
2656 ,decode(bl.project_cost_exchange_rate,NULL
2657 , decode(NVL(bl.project_cost_rate_type,fpo.project_cost_rate_type)
2658 ,'User',cur.PROJECT_COST_EXCHANGE_RATE
2659 , null ),bl.project_cost_exchange_rate) project_exchange_rate
2660 /* -------------project functinal currency conversion attributes -------------------------------*/
2661 ,NVL(bl.projfunc_currency_code,pp.projfunc_currency_code) ProjFunc_currency_code
2662 ,NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type) projfunc_rate_type
2663 ,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
2664 ,'User',NULL
2665 ,NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)) projfunc_rate_date_type
2666 ,decode(NVL(bl.projfunc_cost_rate_date_type,fpo.projfunc_cost_rate_date_type)
2667 ,'START_DATE', NVL(bl.start_date, p_txn_date)
2668 ,'END_DATE' , NVL(bl.end_date ,p_txn_date)
2669 , NVL(bl.projfunc_cost_rate_date,Nvl(fpo.projfunc_cost_rate_date,p_txn_date))) projfunc_rate_date
2670 ,decode(bl.projfunc_cost_exchange_rate,NULL
2671 , decode(NVL(bl.projfunc_cost_rate_type,fpo.projfunc_cost_rate_type)
2672 ,'User',cur.PROJFUNC_COST_EXCHANGE_RATE
2673 , null),bl.projfunc_cost_exchange_rate) projfunc_exchange_rate
2674 from pa_budget_versions bv
2675 ,pa_proj_fp_options fpo
2676 ,pa_projects_all pp
2677 ,pa_fp_txn_currencies cur
2678 ,pa_budget_lines bl
2679 where bv.project_id = pp.project_id
2680 and fpo.project_id = pp.project_id
2681 and nvl(fpo.fin_plan_type_id,0) = nvl(bv.fin_plan_type_id,0)
2682 and fpo.fin_plan_version_id = bv.budget_version_id
2683 and bv.budget_version_id = cur.fin_plan_version_id
2684 and cur.txn_currency_code = p_txn_curr_code
2685 and pp.project_id = p_project_id
2686 and bv.budget_version_id = lv_budget_version_id
2687 and bv.budget_version_id = bl.budget_version_id (+)
2688 and ( (nvl(bl.resource_assignment_id,lv_resource_assignment_id) = lv_resource_assignment_id
2689 and trunc(p_txn_date) between trunc(bl.start_date) and nvl(bl.end_date,p_txn_date)
2690 and bl.txn_currency_code = p_txn_curr_code
2691 ) OR
2692 (NOT EXISTS
2693 (select null from pa_budget_lines bl1
2694 where bl1.budget_version_id = bv.budget_version_id
2695 and bl1.resource_assignment_id = lv_resource_assignment_id
2696 and trunc(p_txn_date) between trunc(bl1.start_date) and nvl(bl1.end_date,p_txn_date)
2697 and bl.txn_currency_code = p_txn_curr_code
2698 ))
2699 )
2700 order by bv.budget_version_id ;
2701
2702
2703 BEGIN
2704 --- Initialize the error statck
2705 If g_debug_flag is NULL Then
2706 fnd_profile.get('PA_DEBUG_MODE',g_debug_flag);
2707 g_debug_flag := NVL(g_debug_flag, 'N');
2708 End If;
2709 l_debug_flag := NVL(g_debug_flag,'N');
2710 If l_debug_flag = 'Y' Then
2711 PA_DEBUG.init_err_stack ('PA_COST1.Convert_COST_TO_PC_PFC');
2712 PA_DEBUG.SET_PROCESS( x_process => 'PLSQL'
2713 ,x_write_file => 'LOG'
2714 ,x_debug_mode => l_debug_flag
2715 );
2716 End If;
2717
2718 l_return_status := 'S';
2719 l_error_msg_code := NULL;
2720 l_budget_version_id := p_budget_version_id;
2721 x_return_status := 'S';
2722 x_error_msg_code := NULL;
2723
2724 l_stage := 'Inside Convert_TxnTo_PV_PFC api:TxnCost['||p_txn_raw_cost||']TxnBdCost['||p_txn_burden_cost||
2725 ']TxnCurr['||p_txn_curr_code||']TxnDate['||p_txn_date||']ProjId['||p_project_id||']BdgtLine['||p_budget_Line_id||
2726 ']BudgetVersion['||l_budget_version_id||']Quantity['||p_txn_quantity||']';
2727 print_msg(l_debug_flag,l_stage);
2728
2729 /* Validate In Params */
2730 IF (( p_project_id is NULL or p_txn_curr_code is NULL or p_txn_date is NULL )
2731 or (p_budget_Line_id is NULL AND p_budget_version_id is NULL )
2732 ) Then
2733 Raise l_insufficient_parms;
2734 End If;
2735
2736 /* Get the Budget Version Id for the given Budget Line */
2737 Begin
2738 If l_budget_version_id is NULL Then
2739 l_stage := 'Executing sql to get Budget Version';
2740 SELECT bl.budget_version_id
2741 ,bl.resource_assignment_id
2742 INTO l_budget_version_id
2743 ,l_resource_assignment_id
2744 FROM pa_budget_lines bl
2745 WHERE bl.budget_line_id = p_budget_line_id;
2746 End If;
2747 Exception
2748 When No_data_found Then
2749 l_stage := 'No Budget Version Found for the given Budget Line['||p_budget_line_id||']' ;
2750 Raise l_No_budget_version;
2751 End;
2752
2753 IF ( NVL(p_txn_raw_cost,0) <> 0 OR NVL(p_txn_burden_cost,0) <> 0 ) Then
2754 l_stage := 'Opening Currency Attributes Cursor with BudgtVer['||l_budget_version_id||
2755 ']ResAssn['||l_resource_assignment_id||']';
2756 OPEN cur_currencyAttribs(l_budget_version_id,l_resource_assignment_id);
2757 FETCH cur_currencyAttribs INTO
2758 l_project_id
2759 ,l_budget_version_id
2760 ,l_txn_curr_code
2761 /* project attributes */
2762 ,l_project_curr_code
2763 ,l_project_rate_type
2764 ,l_project_rate_date_type
2765 ,l_project_rate_date
2766 ,l_project_exchange_rate
2767 /* project functional attributes */
2768 ,l_projfunc_curr_code
2769 ,l_projfunc_rate_type
2770 ,l_projfunc_rate_date_type
2771 ,l_projfunc_rate_date
2772 ,l_projfunc_exchange_rate ;
2773 IF cur_currencyAttribs%FOUND Then
2774 l_stage := 'CurrAttributes:ProjCur['||l_project_curr_code||']ProjRateType['||l_project_rate_type||
2775 ']RateDate['||l_project_rate_date||']ProjXchange['||l_project_exchange_rate||
2776 ']ProjFuncCur['||l_projfunc_curr_code||']PFCRateType['||l_projfunc_rate_type||
2777 ']PFCRateDate['||l_projfunc_rate_date||']PFCXchnge['||l_projfunc_exchange_rate||']' ;
2778 print_msg(l_debug_flag,l_stage);
2779
2780 -- Call the conversion api for PC Amounts
2781 l_stage := 'Calling Convert_amounts for PC';
2782 Convert_amounts
2783 (p_calling_mode => 'PC'
2784 ,p_txn_raw_cost => p_txn_raw_cost
2785 ,p_txn_burden_cost => p_txn_burden_cost
2786 ,p_txn_quantity => p_txn_quantity
2787 ,p_From_curr_code => p_txn_curr_code
2788 ,p_To_curr_code => l_project_curr_code
2789 ,p_To_Curr_Rate_Type => l_project_rate_type
2790 ,p_Conversion_Date => l_project_rate_date
2791 ,p_To_Curr_Exchange_Rate => l_project_exchange_rate
2792 ,x_To_Curr_raw_cost => l_proj_raw_cost
2793 ,x_To_Curr_raw_cost_rate => l_proj_raw_cost_rate
2794 ,x_To_Curr_burden_cost => l_proj_burden_cost
2795 ,x_To_Curr_burden_cost_rate => l_proj_burden_cost_rate
2796 ,x_To_Curr_Exchange_Rate => l_project_exchange_rate
2797 ,x_return_status => l_return_status
2798 ,x_error_msg_code => l_error_msg_code
2799 );
2800
2801 If l_return_status = 'S' Then
2802 l_stage := 'Calling Convert_amounts for PFC';
2803 -- Call the conversion api for PFC amounts
2804 Convert_amounts
2805 (p_calling_mode => 'PFC'
2806 ,p_txn_raw_cost => p_txn_raw_cost
2807 ,p_txn_burden_cost => p_txn_burden_cost
2808 ,p_txn_quantity => p_txn_quantity
2809 ,p_From_curr_code => p_txn_curr_code
2810 ,p_To_curr_code => l_projfunc_curr_code
2811 ,p_To_Curr_Rate_Type => l_projfunc_rate_type
2812 ,p_Conversion_Date => l_projfunc_rate_date
2813 ,p_To_Curr_Exchange_Rate => l_projfunc_exchange_rate
2814 ,x_To_Curr_raw_cost => l_projfunc_raw_cost
2815 ,x_To_Curr_raw_cost_rate => l_projfunc_raw_cost_rate
2816 ,x_To_Curr_burden_cost => l_projfunc_burden_cost
2817 ,x_To_Curr_burden_cost_rate => l_projfunc_burden_cost_rate
2818 ,x_To_Curr_Exchange_Rate => l_projfunc_exchange_rate
2819 ,x_return_status => l_return_status
2820 ,x_error_msg_code => l_error_msg_code
2821 );
2822
2823 End If;
2824
2825 Else
2826 print_msg(l_debug_flag,'Cursor / No Data Found for the Given params');
2827 END IF;
2828 CLOSE cur_currencyAttribs;
2829
2830 End If;
2831
2832
2833 -- Assign out variables
2834 x_return_status := l_return_status;
2835 x_error_msg_code := l_error_msg_code ;
2836 x_project_curr_code := l_project_curr_code;
2837 x_projfunc_curr_code := l_projfunc_curr_code;
2838 x_proj_raw_cost := l_proj_raw_cost;
2839 x_proj_raw_cost_rate := l_proj_raw_cost_rate;
2840 x_proj_burdened_cost := l_proj_burden_cost;
2841 x_proj_burdened_cost_rate := l_proj_burden_cost_rate;
2842 x_projfunc_raw_cost := l_projfunc_raw_cost;
2843 x_projfunc_raw_cost_rate := l_projfunc_raw_cost_rate;
2844 x_projfunc_burdened_cost := l_projfunc_burden_cost;
2845 x_projfunc_burdened_cost_rate := l_projfunc_burden_cost_rate;
2846
2847 l_stage := 'End of ConvertAmts: RetSts['||x_return_status||']Errmsg['||x_error_msg_code||
2848 ']ProjCur['||x_project_curr_code||']ProjFunc['||x_projfunc_curr_code||']ProjRaw['||x_proj_raw_cost||
2849 ']ProjBd['||x_proj_burdened_cost||']PFCRaw['||x_projfunc_raw_cost||']PFCBd['||x_projfunc_burdened_cost||
2850 ']ProjRawRate['||x_proj_raw_cost_rate||']' ;
2851
2852 print_msg(l_debug_flag,l_stage);
2853
2854
2855 -- Reset Err Stack
2856 If l_debug_flag = 'Y' Then
2857 PA_DEBUG.reset_err_stack;
2858 End If;
2859 EXCEPTION
2860 WHEN l_insufficient_parms THEN
2861 x_error_msg_code := 'PA_NO_BUDGET_VERSION';
2862 x_return_status := 'E';
2863 print_msg(l_debug_flag,l_stage);
2864 If l_debug_flag = 'Y' Then
2865 PA_DEBUG.write_file('LOG',l_stage);
2866 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']errCode['||l_error_msg_code||']' );
2867 PA_DEBUG.reset_err_stack;
2868 End If;
2869
2870 WHEN l_no_budget_version THEN
2871 x_error_msg_code := 'PA_INV_PARAM_PASSED';
2872 x_return_status := 'E';
2873 print_msg(l_debug_flag,l_stage);
2874 If l_debug_flag = 'Y' Then
2875 PA_DEBUG.write_file('LOG',l_stage);
2876 PA_DEBUG.write_file('LOG','ReturnStatus['||l_return_status||']errCode['||l_error_msg_code||']' );
2877 PA_DEBUG.reset_err_stack;
2878 End If;
2879
2880 WHEN OTHERS THEN
2881 x_return_status := 'U';
2882 x_error_msg_code := SQLERRM||SQLCODE;
2883 print_msg(l_debug_flag,l_stage||x_error_msg_code);
2884 If l_debug_flag = 'Y' Then
2885 PA_DEBUG.write_file('LOG','ReturnSts['||l_return_status ||']ErrCode['||l_error_msg_code||']' );
2886 PA_DEBUG.write_file('LOG',l_stage);
2887 PA_DEBUG.reset_err_stack;
2888 End If;
2889
2890 END Convert_COST_TO_PC_PFC;
2891
2892 END PA_COST1;
[Home] [Help]