1 PACKAGE BODY PA_REVENUE as
2 /* $Header: PAXBLRTB.pls 120.6 2007/07/24 07:16:34 jjgeorge ship $ */
3
4 l_no_revenue EXCEPTION;
5 l_no_bill_rate EXCEPTION;
6 /* Added for MCB2 */
7 l_conversion_fail EXCEPTION;
8 l_invalid_projfunc_curr_code EXCEPTION;
9 l_invalid_txn_curr_code EXCEPTION;
10 l_invalid_proj_curr_code EXCEPTION;
11
12
13
14 -- This procedure will calculate the bill rate and raw revenue from one of the given criteria's on the basis
15 -- of passed parameters
16 -- Input parameters
17 -- Parameters Type Required Description
18 -- P_project_id NUMBER YES Project Id
19 -- P_task_id NUMBER NO Task Id for the given project
20 -- P_bill_rate_multiplier NUMBER YES Bill rate multiplier for calculating the revenue
21 -- and rate
22 -- P_quantity NUMBER YES Quantity in Hours
23 -- P_raw_cost NUMBER YES Raw cost in project finctional currency
24 -- P_item_date DATE YES Forecast Item date
25 -- P_project_bill_job_grp_id NUMBER NO Billing job group id for project
26 -- P_labor_schdl_discnt NUMBER NO Labour schedule discount
27 -- P_labor_bill_rate_org_id NUMBER NO Bill rate organization id
28 -- P_labor_std_bill_rate_schdl VARCHAR2 NO Standard bill rate schedule
29 -- P_labor_schdl_fixed_date DATE NO Labor schedule fixed date
30 -- P_forecast_job_id NUMBER YES Forecast job Id at assignment level
31 -- P_forecast_job_grp_id NUMBER YES Forecast job group id at assignment level
32 -- P_labor_schdl_type VARCHAR2 NO Labor schedule type i.e. 'I' (Indirect) 'B'( Bill)
33 -- P_item_id NUMBER NO Unique id
34 -- P_forecast_item_id NUMBER NO Unique identifier for forecast item used in
35 -- client extension
36 -- P_forecasting_type VARCHAR2 YES It tells that from where we are calling extn.
37 -- P_project_org_id NUMBER NO Project org Id
38 -- P_job_bill_rate_schedule_id NUMBER YES Job bill rate schedule id
39 -- P_project_type VARCHAR2 YES Project Type
40 -- P_expenditure_type VARCHAR2 YES Expenditure Type
41 -- px_exp_func_curr_code VARCHAR2 YES Expenditure functional currency code
42 -- P_incurred_by_organz_id NUMBER YES Incurred by organz id
43 -- P_raw_cost_rate NUMBER YES Raw cost rate in expenditure currency
44 -- P_override_to_organz_id NUMBER YES Override to organz id
45 -- p_exp_raw_cost NUMBER YES Raw cost in Expenditure currency
46 -- p_expenditure_org_id NUMBER YES Expenditure Org id
47 -- p_projfunc_currency_code VARCHA2 No Project functional currency(PFC)
48 -- p_projfunc_bil_rate_date_code VARCHAR2 No Bill rate date code of PFC
49 --
50 --
51 -- Out parameters
52 --
53 -- X_bill_rate NUMBER YES Bill rate
54 -- X_raw_revenue NUMBER YES Raw revenue
55 -- x_markup_percentage NUMBER YES Markup percentage for that revenue
56 -- PX_project_bill_job_id NUMBER NO Billing Job id for project
57 -- px_projfunc_bil_rate_type VARCHAR2 No Bill rate type of PFC
58 -- px_projfunc_bil_rate_date DATE No Bill rate date code of PFC
59 -- px_projfunc_bil_exchange_rate NUMBER No Bill exchange rate of PFC
60
61
62 g1_debug_mode varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
63
64 PROCEDURE Requirement_Rev_Amt (
65 p_project_id IN NUMBER,
66 p_task_id IN NUMBER,
67 p_bill_rate_multiplier IN NUMBER,
68 p_quantity IN NUMBER,
69 p_raw_cost IN NUMBER,
70 p_item_date IN DATE,
71 p_project_bill_job_grp_id IN NUMBER,
72 p_labor_schdl_discnt IN NUMBER,
73 p_labor_bill_rate_org_id IN NUMBER,
74 p_labor_std_bill_rate_schdl IN VARCHAR2,
75 p_labor_schdl_fixed_date IN DATE,
76 p_forecast_job_id IN NUMBER,
77 p_forecast_job_grp_id IN NUMBER,
78 p_labor_sch_type IN VARCHAR2,
79 p_item_id IN NUMBER DEFAULT NULL, /* change from forecast */
80 /* item id to item id for bug 2212852 */
81 p_project_org_id IN NUMBER,
82 p_job_bill_rate_schedule_id IN NUMBER,
83 p_project_type IN VARCHAR2,
84 p_expenditure_type IN VARCHAR2,
85 px_exp_func_curr_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
86 p_incurred_by_organz_id IN NUMBER,
87 p_raw_cost_rate IN NUMBER,
88 p_override_to_organz_id IN NUMBER,
89 p_exp_raw_cost IN NUMBER,
90 p_expenditure_org_id IN NUMBER,
91 p_projfunc_currency_code IN VARCHAR2,/* Added for MCB2 */
92 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
93 p_forecast_item_id IN NUMBER DEFAULT NULL, /* added para for bug 2212852 */
94 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING', /* added para for bug 2212852 */
95 p_sys_linkage_function IN VARCHAR2 , /* Added for Org Forecasting */
96 px_project_bill_job_id IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
97 x_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
98 x_raw_revenue OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
99 x_markup_percentage OUT NOCOPY NUMBER, /* Added for Asgmt overide */ --File.Sql.39 bug 4440895
100 x_txn_currency_code OUT NOCOPY VARCHAR2, /* Added for Org Forecasting */ --File.Sql.39 bug 4440895
101 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
102 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
103 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
104 IS
105
106
107
108 l_raw_revenue NUMBER :=null; -- It will be used to store the raw revenue
109 -- from one of the raw revenue calculating
110 -- criteria
111 l_bill_rate NUMBER :=null; -- It will be used to store bill amount
112 -- from one of the bill amount calculating
113 -- criteria
114
115 l_schedule_type VARCHAR2(50) := 'REVENUE';
116
117 l_x_return_status VARCHAR2(50); -- It will be used to store the return status
118 -- and used it to validate whether the
119 -- calling procedure has run successfully
120 -- or encounter any error
121 l_adjusted_revenue NUMBER; -- Local variable
122 l_adjusted_rate NUMBER:=NULL; -- Local variable
123 l_labor_schdl_discnt NUMBER; -- Variable to store labor schedule discount
124 l_discount_pct NUMBER; -- Variable to store the discount pct override
125 l_labor_bill_rate_org_id NUMBER; -- Variable to store labor bill rate organization id
126 l_labor_std_bill_rate_schdl pa_projects_all.labor_std_bill_rate_schdl%TYPE; -- store labor standard
127 -- bill rate schedule
128 l_labor_schdl_fixed_date DATE; -- variable to store labor schedule fixed date
129 l_labor_sch_type pa_projects_all.labor_sch_type%TYPE; -- store labor schedule type
130
131 l_no_revenue EXCEPTION;--no revenue
132 l_no_val_in_funct EXCEPTION; -- Exception if no record found from get to job proc.
133 l_job_bill_rate_schedule_id pa_projects_all.job_bill_rate_schedule_id%TYPE; -- store job bill rate
134 -- schedule id
135 l_project_org_id pa_projects_all.org_id%TYPE;
136
137 /* Added for MCB2 */
138 l_txn_bill_rate NUMBER :=null; -- store bill amount transaction curr.
139 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
140 l_rate_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
141
142 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
143 l_markup_percentage pa_bill_rates_all.markup_percentage%TYPE; /* Added for Asgmt overide */
144 l_assignment_precedes_task pa_projects_all.assign_precedes_task%TYPE; /* Added for Asgmt overide */
145
146 l_revenue_calculated_flag VARCHAR2(1); /* Added for bug 2212852, if it is Y means it has calculated
147 the revenue from client extension */
148 l_item_quantity pa_forecast_items.item_quantity%TYPE; /* Added for bug 2212852 */
149 l_item_amount NUMBER; /* Added for bug 2212852 */
150 l_bill_rate_flag VARCHAR(1); /* Added for bug 2212852 */
151 l_status_client NUMBER; /* Added for bug 2212852 */
152 l_dummy_rate NUMBER; /* Added for bug 2212852 */
153 l_dummy_markup_percentage NUMBER; /* Added for bug 2212852 */
154 l_dummy_rate_source_id NUMBER; /* Added for bug 2212852 */
155
156
157 lx_exp_func_curr_code varchar2(15);
158 lx_project_bill_job_id number;
159
160 BEGIN
161
162
163 /* ATG Changes */
164
165 lx_exp_func_curr_code := px_exp_func_curr_code ;
166 lx_project_bill_job_id := px_project_bill_job_id;
167
168
169
170
171
172 -- Initializing return status with success sothat if some unexpected error comes
173 -- , we change its status from succes to error sothat we can take necessary step to rectify the problem
174 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
175 l_adjusted_revenue := NULL;
176 l_markup_percentage := NULL; /* Added for Asgmt overide */
177
178 l_revenue_calculated_flag := 'N'; /* added for bug 2212852 */
179 l_item_quantity := 0; /* added for bug 2212852 */
180
181 /* Calling client extension if getting the value from ext. then ignore all
182 Added for bug 2212852 */
183 IF (p_forecast_item_id IS NOT NULL ) THEN
184 pa_billing.Call_Calc_Bill_Amount(
185 x_transaction_type => 'FORECAST',
186 x_expenditure_item_id => p_forecast_item_id,
187 /* x_sys_linkage_function => 'ST', */
188 x_sys_linkage_function => p_sys_linkage_function, /* Added for Org Fcst */
189 x_amount => l_item_amount,
190 x_bill_rate_flag => l_bill_rate_flag,
191 x_status => l_status_client,
192 x_bill_trans_currency_code => l_rate_currency_code,
193 x_bill_txn_bill_rate => l_dummy_rate,
194 x_markup_percentage => l_dummy_markup_percentage,
195 x_rate_source_id => l_dummy_rate_source_id
196 );
197 l_rate_currency_code := NVL(l_rate_currency_code,p_projfunc_currency_code);
198 l_projfunc_currency_code := p_projfunc_currency_code;
199 IF (NVL(l_item_amount,0) <> 0) THEN
200 l_revenue_calculated_flag := 'Y';
201 IF (p_forecasting_type = 'PROJECT_FORECASTING') THEN
202 SELECT item_quantity
203 INTO l_item_quantity
204 FROM pa_forecast_items
205 WHERE forecast_item_id = p_forecast_item_id;
206
207 l_bill_rate := l_item_amount/l_item_quantity;
208
209 l_raw_revenue := (l_bill_rate * p_quantity);
210 ELSE
211 l_bill_rate := l_item_amount/p_quantity;
212 l_raw_revenue := l_item_amount;
213 END IF;
214 END IF;
215
216 /* Moved this bunch of statement from the if of project to here, sothat it should execute every time */
217 IF p_labor_schdl_discnt IS NOT NULL THEN
218 l_labor_schdl_discnt := p_labor_schdl_discnt;
219 END IF;
220
221 IF p_labor_bill_rate_org_id IS NOT NULL THEN
222 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
223 END IF;
224
225 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
226 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
227 END IF;
228 IF p_labor_schdl_fixed_date IS NOT NULL THEN
229 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
230 END IF;
231 IF p_labor_sch_type IS NOT NULL THEN
232 l_labor_sch_type := p_labor_sch_type;
233 END IF;
234 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
235 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
236 END IF;
237 IF p_project_org_id IS NOT NULL THEN
238 l_project_org_id := p_project_org_id;
239 END IF;
240
241 /* The following code have been added for MCB 2 */
242 IF p_projfunc_currency_code IS NOT NULL THEN
243 l_projfunc_currency_code := p_projfunc_currency_code;
244 END IF;
245
246 /* Added for Asgmt overide */
247 IF p_assignment_precedes_task IS NOT NULL THEN
248 l_assignment_precedes_task := p_assignment_precedes_task;
249 END IF;
250 END IF; /* end if of forecast_item_id */
251
252 IF ( NVL(l_revenue_calculated_flag,'N') = 'N') THEN /* added for bug 2212852 { */
253
254
255 -- Selecting labor schedule discount,labor bill rate orgnization id,labor standard bill rate
256 -- schedule and labor schedule fixed date if any one of them is null then taking value from task
257 -- table only if passed task id is not null if it is null then taking value from project table
258
259 IF ( (p_labor_schdl_discnt IS NULL )OR (p_labor_bill_rate_org_id IS NULL)
260 OR (p_labor_std_bill_rate_schdl IS NULL) OR (p_labor_schdl_fixed_date IS NULL)OR
261 (p_labor_sch_type IS NULL)) THEN
262 IF (p_task_id IS NULL ) THEN
263 BEGIN
264 SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
265 labor_schedule_fixed_date,labor_sch_type,job_bill_rate_schedule_id,org_id,
266 projfunc_currency_code, /* Added the following column for MCB2 */
267 NVL(assign_precedes_task,'1') /* Added for Asgmt overide */
268 INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
269 l_labor_schdl_fixed_date,l_labor_sch_type,l_job_bill_rate_schedule_id,l_project_org_id,
270 l_projfunc_currency_code, /* Added the following columns for MCB2 */
271 l_assignment_precedes_task
272 FROM pa_projects_all
273 WHERE project_id = p_project_id;
274
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 NULL;
279 END;
280 ELSE
281 BEGIN
282 SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
283 labor_schedule_fixed_date,labor_sch_type
284 INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
285 l_labor_schdl_fixed_date,l_labor_sch_type
286 FROM pa_tasks
287 WHERE task_id = p_task_id;
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 NULL;
291 END;
292 END IF;
293 ELSE
294 l_labor_schdl_discnt := p_labor_schdl_discnt;
295 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
296 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
297 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
298 l_labor_sch_type := p_labor_sch_type;
299 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
300 END IF;
301
302 /* Moved this bunch of statement from the if of project to here, sothat it should execute every time */
303 IF p_labor_schdl_discnt IS NOT NULL THEN
304 l_labor_schdl_discnt := p_labor_schdl_discnt;
305 END IF;
306
307 IF p_labor_bill_rate_org_id IS NOT NULL THEN
308 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
309 END IF;
310
311 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
312 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
313 END IF;
314 IF p_labor_schdl_fixed_date IS NOT NULL THEN
315 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
316 END IF;
317 IF p_labor_sch_type IS NOT NULL THEN
318 l_labor_sch_type := p_labor_sch_type;
319 END IF;
320 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
321 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
322 END IF;
323 IF p_project_org_id IS NOT NULL THEN
324 l_project_org_id := p_project_org_id;
325 END IF;
326
327 /* The following code have been added for MCB 2 */
328 IF p_projfunc_currency_code IS NOT NULL THEN
329 l_projfunc_currency_code := p_projfunc_currency_code;
330 END IF;
331
332 /* Added for Asgmt overide */
333 IF p_assignment_precedes_task IS NOT NULL THEN
334 l_assignment_precedes_task := p_assignment_precedes_task;
335 END IF;
336
337
338 /* Checking if the labor schedule type is indirect then calling other api
339 otherwise following the steps given below { */
340
341 IF ( l_labor_sch_type = 'I' ) THEN
342 -- Calling burden cost API
343 PA_COST.get_burdened_cost(p_project_type => p_project_type ,
344 p_project_id => p_project_id ,
345 p_task_id => p_task_id ,
346 p_item_date => p_item_date ,
347 p_expenditure_type => p_expenditure_type ,
348 p_schedule_type => l_schedule_type ,
349 px_exp_func_curr_code => px_exp_func_curr_code ,
350 p_Incurred_by_organz_id => p_Incurred_by_organz_id ,
351 p_raw_cost => p_exp_raw_cost ,
352 p_raw_cost_rate => p_raw_cost_rate ,
353 p_quantity => p_quantity ,
354 p_override_to_organz_id => p_override_to_organz_id ,
355 x_burden_cost => l_raw_revenue ,
356 x_burden_cost_rate => l_bill_rate ,
357 x_return_status => l_x_return_status ,
358 x_msg_count => x_msg_count ,
359 x_msg_data => x_msg_data);
360
361 l_rate_currency_code := px_exp_func_curr_code;
362 /* There was a call for PA_COST.get_projfunc_raw_burdened , it has been deleted
363 for Org Forecasting */
364
365 ELSIF (l_labor_sch_type = 'B' ) THEN
366 -- Calling job id conversion procedure from resource
367 PA_RESOURCE_UTILS.GetToJobId( p_forecast_job_grp_id,
368 p_forecast_job_id,
369 p_project_bill_job_grp_id,
370 px_project_bill_job_id);
371
372
373 /* This override is added for Assignment level override functionality ,
374 it executed if the override precedence takes at assignment level i.e
375 assignment_precedes_task is 'Y' */
376
377 /*------------------------------------------------------------------+
378 | 1. Assignment level overrides |
379 +------------------------------------------------------------------+
380 | Set bill rate and raw revenue using Assignment level |
381 | overrides . |
382 +------------------------------------------------------------------*/
383 /* If the call is from Assignment api then the item_id will be null so this override will
384 not execute */
385 IF (p_item_id IS NOT NULL) THEN
386 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL ) THEN
387 -- IF ( l_assignment_precedes_task = 'Y') THEN
388 BEGIN
389 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
390 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
391 ),
392 DECODE(asgn.bill_rate_override, NULL,
393 ((100 + asgn.markup_percent_override)
394 * p_raw_cost / 100),
395 (asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
396 * p_quantity)),
397 DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
398 asgn.markup_percent_override,
399 asgn.discount_percentage
400
401 INTO l_bill_rate,l_raw_revenue,
402 l_rate_currency_code,
403 l_markup_percentage,
404 l_discount_pct
405 FROM pa_project_assignments asgn
406 WHERE asgn.assignment_id = p_item_id;
407
408 EXCEPTION
409 WHEN TOO_MANY_ROWS THEN
410 RAISE;
411 WHEN NO_DATA_FOUND THEN
412 l_raw_revenue := NULL;
413 l_bill_rate := NULL;
414 END;
415 -- END IF; /* end of l_assignment_precedes_task flag check */
416 END IF; /* end of revenue and rate check */
417 END IF;
418
419
420 /*------------------------------------------------------------------+
421 | 2. Task job bill rate overrides |
422 +------------------------------------------------------------------+
423 | Set bill rate and raw revenue using Task job bill rate |
424 | overrides . |
425 +------------------------------------------------------------------*/
426 -- IT IS NOT FOR THIS PHASE
427 /*
428 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND p_task_id IS NOT NULL
429 AND l_discount_pct IS NULL) THEN
430 BEGIN
431 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
432 (j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
433 j.rate_currency_code ,
434 j.discount_percentage
435 INTO l_bill_rate,l_raw_revenue,
436 l_rate_currency_code ,-- Added for MCB2
437 l_discount_pct
438 FROM pa_job_bill_rate_overrides j
439 WHERE j.task_id = p_task_id
440 AND TO_DATE(p_item_date)
441 BETWEEN TO_DATE(j.start_date_active)
442 AND NVL(TO_DATE(j.end_date_active),
443 TO_DATE(p_item_date))
444 AND j.job_id = px_project_bill_job_id;
445
446
447 EXCEPTION
448 WHEN TOO_MANY_ROWS THEN
449 RAISE;
450 WHEN NO_DATA_FOUND THEN
451 l_raw_revenue := NULL;
452 l_bill_rate := NULL;
453 END;
454 END IF;
455 */
456
457 /* This override is added for Assignment level override functionality ,
458 it executed if the override precedence takes at Task level i.e
459 assign_precedes_task = 'N' */
460
461 /*------------------------------------------------------------------+
462 | 3. Assignment level overrides ,but Task take precedence |
463 +------------------------------------------------------------------+
464 | Set bill rate and raw revenue using Assignment level |
465 | overrides . |
466 +------------------------------------------------------------------*/
467 /* If the call is from Assignment api then the item_id will be null so this override will
468 not execute */
469 /*
470
471 IF (p_item_id IS NOT NULL) THEN
472 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL ) THEN
473 IF ( ( l_assignment_precedes_task = 'N' and l_discount_pct is null) ) THEN -- Removed task id check to fix bug 2354746
474 BEGIN
475 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
476 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
477 ),
478 DECODE(asgn.bill_rate_override, NULL,
479 ((100 + asgn.markup_percent_override)
480 * p_raw_cost / 100),
481 (asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
482 * p_quantity)),
483 DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
484 asgn.markup_percent_override,
485 asgn.discount_percentage
486 INTO l_bill_rate,l_raw_revenue,
487 l_rate_currency_code,
488 l_markup_percentage,
489 l_discount_pct
490 FROM pa_project_assignments asgn
491 WHERE asgn.assignment_id = p_item_id;
492
493 EXCEPTION
494 WHEN TOO_MANY_ROWS THEN
495 RAISE;
496 WHEN NO_DATA_FOUND THEN
497 l_raw_revenue := NULL;
498 l_bill_rate := NULL;
499 END;
500 END IF; -- end of l_assignment_precedes_task flag check
501 END IF; -- end of revenue and rate check
502 END IF;
503 */
504
505 /*------------------------------------------------------------------+
506 | 4. Project job bill rate overrides |
507 +------------------------------------------------------------------+
508 | Set bill rate and raw revenue using Project job bill rate |
509 | overrides . |
510 +------------------------------------------------------------------*/
511
512 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND l_discount_pct IS NULL) THEN
513
514 BEGIN
515 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
516 (j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
517 j.rate_currency_code,
518 j.discount_percentage /* Added for MCB2 */
519 INTO l_bill_rate,
520 l_raw_revenue,
521 l_rate_currency_code, /* Added for MCB2 */
522 l_discount_pct /* Added for discount percentage*/
523 FROM pa_job_bill_rate_overrides j
524 WHERE j.project_id = p_project_id
525 /* 0.99999 added to the dates so that the starting clause of
526 the between condition does not have aby function on it so
527 as to better use the index */
528 AND trunc(p_item_date) + 0.99999 /* BUG#3118592 */
529 BETWEEN j.start_date_active
530 AND NVL(trunc(j.end_date_active) + 0.99999, /* BUG#3118592 */
531 trunc(p_item_date) + 0.99999) /* BUG#3118592 */
532 AND j.job_id = px_project_bill_job_id;
533
534
535 EXCEPTION
536 WHEN TOO_MANY_ROWS THEN
537 RAISE;
538 WHEN NO_DATA_FOUND THEN
539 l_raw_revenue := NULL;
540 l_bill_rate := NULL;
541 END;
542 END IF;
543
544 /*------------------------------------------------------------+
545 |5. Job based bill rate schedule for forecasting |
546 +------------------------------------------------------------+
547 | Set bill rate, raw revenue, adjusted rate, adjusted |
548 | revenue using standard job bill rate schedule. |
549 +------------------------------------------------------------*/
550
551 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL ) THEN
552
553 BEGIN
554 SELECT DECODE(b.rate, NULL, NULL,
555 b.rate * NVL(p_bill_rate_multiplier,1)
556 ),
557 DECODE(b.rate, NULL,
558 ((100 + b.markup_percentage)
559 * p_raw_cost / 100),
560 (b.rate * NVL(p_bill_rate_multiplier,1)
561 * p_quantity)),
562 DECODE(NVL( l_discount_pct,l_labor_schdl_discnt), NULL, NULL,
563 (b.rate * NVL(p_bill_rate_multiplier,1)
564 * (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) /100)),
565 DECODE(NVL( l_discount_pct,l_labor_schdl_discnt), NULL, NULL,
566 DECODE(b.rate, NULL,
567 ((100 + b.markup_percentage)
568 * (p_raw_cost / 100)
569 * (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) / 100),
570 ((b.rate * p_quantity)
571 * NVL(p_bill_rate_multiplier,1)
572 * (100 - NVL( l_discount_pct,l_labor_schdl_discnt)) / 100)
573 )
574 ),
575 DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code),
576 b.markup_percentage
577 INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
578 l_rate_currency_code /* Added for MCB2 */,
579 l_markup_percentage /* Added for Asgmt overide */
580 FROM pa_bill_rates_all b
581 WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
582 AND b.job_id = px_project_bill_job_id
583 AND trunc(NVL(l_labor_schdl_fixed_date, p_item_date)) + 0.99999 /* BUG#3118592 */
584 BETWEEN b.start_date_active
585 AND NVL(trunc(b.end_date_active),trunc(NVL(l_labor_schdl_fixed_date, p_item_date))) + 0.99999; /* BUG#3118592 */
586 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
587
588
589 EXCEPTION
590 WHEN TOO_MANY_ROWS THEN
591 RAISE;
592 WHEN NO_DATA_FOUND THEN
593 l_raw_revenue := NULL;
594 l_bill_rate := NULL;
595 END;
596 END IF;
597 END IF; /* Labor schedule type end if } */
598 END IF; /* end if revenue calculated flag } */
599
600 l_txn_bill_rate := l_bill_rate; -- Removed NVL condition for bug 5079230
601
602 IF (l_adjusted_revenue IS NOT NULL ) THEN
603 l_txn_raw_revenue := NVL(l_adjusted_revenue,0);
604 ELSE
605 l_txn_raw_revenue := NVL(l_raw_revenue,0);
606 END IF;
607
608 IF ( (l_txn_raw_revenue IS NULL) OR (l_txn_raw_revenue = 0) ) THEN
609 RAISE l_no_revenue;
610 END IF;
611
612 x_raw_revenue := NVL(l_txn_raw_revenue,0) ;
613 x_bill_rate := l_txn_bill_rate ; -- Removed NVL condition for bug 5079230
614 x_markup_percentage := l_markup_percentage; /* Added for Asgmt overide */
615 x_txn_currency_code := l_rate_currency_code; /* Added for Org Forecasting */
616
617 x_return_status := l_x_return_status;
618
619 EXCEPTION
620 WHEN l_no_revenue THEN
621 x_bill_rate := NULL;
622 x_raw_revenue:= 0;
623 x_markup_percentage := NULL; /* Added for Asgmt overide */
624 /* Checking error condition. Added for bug 2218386 */
625 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
626 PA_UTILS.add_message('PA', 'PA_FCST_NO_BILL_RATE');
627 END IF;
628 x_return_status := FND_API.G_RET_STS_ERROR;
629 x_msg_count := 1;
630 x_msg_data := 'PA_FCST_NO_BILL_RATE';
631 WHEN OTHERS THEN
632 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
633 x_msg_count := 1;
634 x_msg_data := SUBSTR(SQLERRM,1,30);
635
636 /* ATG Changes */
637 px_project_bill_job_id := lx_project_bill_job_id;
638 px_exp_func_curr_code := lx_exp_func_curr_code ;
639 x_bill_rate := null;
640 x_raw_revenue := null;
641 x_markup_percentage := null;
642 x_txn_currency_code := null;
643
644 /* Checking error condition. Added for bug 2218386 */
645
646 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
647 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REVENUE', /* Moved this here to fix bug 2434663 */
648 p_procedure_name => 'Requirement_Rev_Amt');
649 RAISE;
650 END IF;
651
652
653 END Requirement_Rev_Amt;
654
655
656
657 -- This procedure will calculate the raw revenue and bill amount from one of the 12 criterias on the basis
658 -- of passed parameters
659 -- Input parameters
660 -- Parameters Type Required Description
661 -- P_project_id NUMBER YES Project Id
662 -- P_task_id NUMBER NO Task Id for the given project
663 -- P_bill_rate_multiplier NUMBER YES Bill rate multiplier for calculating the revenue
664 -- and rate
665 -- P_quantity NUMBER YES Quantity in Hours
666 -- P_person_id NUMBER YES Person Id
667 -- P_raw_cost NUMBER YES Row cost
668 -- P_item_date DATE YES Forecast Item date
669 -- P_labor_schdl_discnt NUMBER NO Labour schedule discount
670 -- P_labor_bill_rate_org_id NUMBER NO Bill rate organization id
671 -- P_labor_std_bill_rate_schdl VARCHAR2 NO Standard bill rate schedule
672 -- P_labor_schdl_fixed_date DATE NO Schedule date
673 -- P_bill_job_grp_id NUMBER NO Project Group Id
674 -- P_item_id NUMBER NO Unique id
675 -- P_forecast_item_id NUMBER NO Unique identifier for forecast item used in
676 -- client extension
677 -- P_forecasting_type VARCHAR2 YES It tells that from where we are calling extn.
678 -- P_labor_sch_type VARCHAR2 NO Labor schedule type
679 -- P_project_org_id NUMBER NO Project Org ID
680 -- P_project_type VARCHAR2 YES Project Type
681 -- P_expenditure_type VARCHAR2 YES Expenditure Type
682 -- p_exp_func_curr_code VARCHAR2 YES Expenditure functional currency code
683 -- P_incurred_by_organz_id NUMBER YES Incurred by organz id
684 -- P_raw_cost_rate NUMBER YES Raw cost rate
685 -- P_override_to_organz_id NUMBER YES Override to organz id
686 --
687 -- Out parameters
688 --
689 -- X_bill_rate NUMBER YES
690 -- X_raw_revenue NUMBER YES
691 -- X_rev_currency_code VARCHAR2 YES
692
693 PROCEDURE Assignment_Rev_Amt(
694 p_project_id IN NUMBER,
695 p_task_id IN NUMBER DEFAULT NULL,
696 p_bill_rate_multiplier IN NUMBER DEFAULT NULL,
697 p_quantity IN NUMBER,
698 p_person_id IN NUMBER,
699 p_raw_cost IN NUMBER DEFAULT NULL,
700 p_item_date IN DATE,
701 p_labor_schdl_discnt IN NUMBER DEFAULT NULL,
702 p_labor_bill_rate_org_id IN NUMBER DEFAULT NULL,
703 p_labor_std_bill_rate_schdl IN VARCHAR2 DEFAULT NULL,
704 p_labor_schdl_fixed_date IN DATE DEFAULT NULL,
705 p_bill_job_grp_id IN NUMBER DEFAULT NULL,
706 p_item_id IN NUMBER DEFAULT NULL, /* change from forecast item id */
707 /* to item id for bug 2212852 */
708 p_labor_sch_type IN VARCHAR2 DEFAULT NULL,
709 p_project_org_id IN NUMBER DEFAULT NULL,
710 p_project_type IN VARCHAR2 DEFAULT NULL,
711 p_expenditure_type IN VARCHAR2 DEFAULT NULL,
712 p_exp_func_curr_code IN VARCHAR2 DEFAULT NULL,
713 p_incurred_by_organz_id IN NUMBER DEFAULT NULL,
714 p_raw_cost_rate IN NUMBER DEFAULT NULL,
715 p_override_to_organz_id IN NUMBER DEFAULT NULL,
716 p_emp_bill_rate_schedule_id IN VARCHAR2 DEFAULT NULL,
717 p_resource_job_id IN NUMBER,
718 p_exp_raw_cost IN NUMBER,
719 p_expenditure_org_id IN NUMBER,
720 p_projfunc_currency_code IN VARCHAR2, /* Added for MCB2 */
721 p_assignment_precedes_task IN VARCHAR2, /* Added for Asgmt overide */
722 p_forecast_item_id IN NUMBER DEFAULT NULL, /* added para for bug 2212852 */
723 p_forecasting_type IN VARCHAR2 DEFAULT 'PROJECT_FORECASTING', /* added para for bug 2212852 */
724 p_sys_linkage_function IN VARCHAR2 , /* Added for Org Forecasting */
725 x_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
726 x_raw_revenue OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
727 x_rev_currency_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
728 x_markup_percentage OUT NOCOPY NUMBER, /* Added for Asgmt overide */ --File.Sql.39 bug 4440895
729 x_txn_currency_code OUT NOCOPY VARCHAR2,/*Added for Org Forecasting */ --File.Sql.39 bug 4440895
730 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
731 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
732 x_msg_data OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
733 /*Bill rate Discount */
734 p_mcb_flag IN VARCHAR2 DEFAULT NULL,
735 p_denom_raw_cost IN NUMBER DEFAULT NULL,
736 p_denom_curr_code IN VARCHAR2 DEFAULT NULL,
737 p_called_process IN VARCHAR2 DEFAULT NULL,
738 p_job_bill_rate_schedule_id IN NUMBER DEFAULT NULL,
739 /* Added for bug 2668753 */
740 p_project_raw_cost IN NUMBER DEFAULT NULL,
741 p_project_currency_code IN VARCHAR2 DEFAULT NULL,
742 x_adjusted_bill_rate OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
743 IS
744
745 l_raw_revenue NUMBER :=null; -- store the raw revenue
746 -- from one of the raw revenue calculating
747 -- criteria
748 l_bill_rate NUMBER :=null; -- store bill amount
749 -- from one of the bill amount calculating
750 -- criteria
751 l_schedule_type VARCHAR2(50) := 'REVENUE';
752
753 l_x_return_status VARCHAR2(50); -- store the return status
754 -- and used it to validate whether the
755 -- calling procedure has run successfully
756 -- or encounter any error
757 l_adjusted_revenue NUMBER; -- Local variable
758 l_adjusted_rate NUMBER:=NULL; -- Local variable
759 l_labor_schdl_discnt NUMBER; -- store labor schedule discount
760 l_labor_bill_rate_org_id NUMBER; -- store labor bill rate organization id
761 l_labor_std_bill_rate_schdl VARCHAR2(20); -- store labor standard bill rate schedule
762 l_labor_schdl_fixed_date DATE; -- store labor schedule fixed date
763 l_labor_sch_type VARCHAR2(1); -- store labor schedule type
764 l_expenditure_currency_code gl_sets_of_books.currency_code%TYPE := null;
765 l_bill_job_grp_id pa_projects_all.bill_job_group_id%TYPE; -- store bill job group id
766 l_project_org_id pa_projects_all.org_id%TYPE; -- store project org id
767 l_rev_currency_code pa_projects_all.project_currency_code%TYPE; -- store revenue currency code
768 l_emp_bill_rate_schedule_id NUMBER;
769 l_job_bill_rate_schedule_id NUMBER;
770
771 /* Added for MCB2 */
772 l_txn_bill_rate NUMBER :=null; -- store bill amount transaction curr.
773 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
774 l_rate_currency_code pa_bill_rates_all.rate_currency_code%TYPE;
775
776 l_projfunc_currency_code pa_projects_all.projfunc_currency_code%TYPE;
777
778 l_markup_percentage pa_bill_rates_all.markup_percentage%TYPE; /* Added for Asgmt overide */
779 l_assignment_precedes_task pa_projects_all.assign_precedes_task%TYPE; /* Added for Asgmt overide */
780
781 l_revenue_calculated_flag VARCHAR2(1); /* Added for bug 2212852, if it is Y means it has calculated
782 the revenue from client extension */
783 l_item_quantity pa_forecast_items.item_quantity%TYPE; /* Added for bug 2212852 */
784 l_item_amount NUMBER; /* Added for bug 2212852 */
785 l_bill_rate_flag VARCHAR2(1); /* Added for bug 2212852 */
786 l_status_client NUMBER; /* Added for bug 2212852 */
787 l_dummy_rate NUMBER; /* Added for bug 2212852 */
788 l_dummy_markup_percentage NUMBER; /* Added for bug 2212852 */
789 l_dummy_rate_source_id NUMBER; /* Added for bug 2212852 */
790 l_discount_percentage NUMBER; /* Added for Transfer Price changes */
791 l_amount_calculation_code varchar2(1); /* Added for Transfer Price changes */
792 /* Added for bug 2668753 */
793 l_mcb_cost_flag varchar2(50) := null;
794 l_mcb_raw_cost number := null;
795 l_mcb_currency_code varchar2(50) := null;
796 l_called_process NUMBER; /*Added for Doosan rate api enhancement */
797 l_txn_adjusted_bill_rate NUMBER :=null; --4038485
798 BEGIN
799
800 -- Initializing return status with success so that if some unexpected error comes
801 -- , we change its status from succes to error so that we can take necessary step to rectify the problem
802 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
803 l_markup_percentage := NULL; /* Added for Asgmt overide */
804
805 l_revenue_calculated_flag := 'N'; /* added for bug 2212852 */
806 l_item_quantity := 0; /* added for bug 2212852 */
807
808 l_adjusted_revenue := NULL;
809
810 /* Changes for bug 2668753 */
811
812 /* Adding the following piece of code for Doosan rate api changes . */
813
814 l_called_process := 0;
815
816 IF P_called_process ='PROJECT_LEVEL_PLANNING' THEN
817 l_called_process :=1;
818 END IF;
819
820 IF P_called_process ='TASK_LEVEL_PLANNING' THEN
821 l_called_process :=2;
822 END IF;
823
824 /* Bug 2668753 : Get the BTC_COST_BASE_REV_CODE from pa_projects_all table */
825 IF ( nvl(p_mcb_flag,'N') = 'Y' ) THEN
826 BEGIN
827 /* Added the following nvl so that code doesn't break even if upgrade script fails - For bug 2668753 */
828
829 select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
830 into l_mcb_cost_flag
831 from pa_projects_all
832 where project_id = p_project_id;
833
834 EXCEPTION
835 WHEN NO_DATA_FOUND THEN
836 RAISE ;
837 END;
838
839 IF (l_mcb_cost_flag = 'EXP_TRANS_CURR') THEN
840 l_mcb_raw_cost := p_denom_raw_cost;
841 l_mcb_currency_code := p_denom_curr_code;
842
843 ELSIF (l_mcb_cost_flag = 'EXP_FUNC_CURR') THEN
844 l_mcb_raw_cost := p_exp_raw_cost;
845 l_mcb_currency_code := p_exp_func_curr_code;
846
847 ELSIF (l_mcb_cost_flag = 'PROJ_FUNC_CURR') THEN
848 l_mcb_raw_cost := p_raw_cost;
849 l_mcb_currency_code := p_projfunc_currency_code;
850
851 ELSIF (l_mcb_cost_flag = 'PROJECT_CURR') THEN
852 l_mcb_raw_cost := p_project_raw_cost;
853 l_mcb_currency_code := p_project_currency_code;
854
855 END IF;
856 /* Added for bug 2742778 */
857 ELSE
858 l_mcb_raw_cost := p_raw_cost;
859 l_mcb_currency_code := p_projfunc_currency_code;
860 /* End of changes for bug 2742778 */
861 END IF;
862 /* End of changes for bug 2668753 */
863
864
865 IF (p_exp_func_curr_code IS NOT NULL) THEN
866 l_expenditure_currency_code := p_exp_func_curr_code;
867 END IF;
868
869
870
871 IF (p_project_id IS NOT NULL and p_called_process is NULL) THEN
872 BEGIN
873 SELECT projfunc_currency_code
874 INTO l_rev_currency_code
875 FROM pa_projects_all
876 WHERE project_id = p_project_id;
877 x_rev_currency_code := l_rev_currency_code;
878 EXCEPTION
879 WHEN NO_DATA_FOUND THEN
880 NULL;
881 END;
882 END IF;
883
884 /* Calling client extension if getting the value from ext. then ignore all
885 Added for bug 2212852 */
886 IF (p_forecast_item_id IS NOT NULL ) THEN
887 pa_billing.Call_Calc_Bill_Amount(
888 x_transaction_type => 'FORECAST',
889 x_expenditure_item_id => p_forecast_item_id,
890 /* x_sys_linkage_function => 'ST', */
891 x_sys_linkage_function => p_sys_linkage_function, /* Added for Org Fcst */
892 x_amount => l_item_amount,
893 x_bill_rate_flag => l_bill_rate_flag,
894 x_status => l_status_client,
895 x_bill_trans_currency_code => l_rate_currency_code,
896 x_bill_txn_bill_rate => l_dummy_rate,
897 x_markup_percentage => l_dummy_markup_percentage,
898 x_rate_source_id => l_dummy_rate_source_id
899 );
900 l_rate_currency_code := NVL(l_rate_currency_code,p_projfunc_currency_code);
901 l_projfunc_currency_code := p_projfunc_currency_code;
902 IF (NVL(l_item_amount,0) <> 0) THEN
903 l_revenue_calculated_flag := 'Y';
904 IF (p_forecasting_type = 'PROJECT_FORECASTING') THEN
905 SELECT item_quantity
906 INTO l_item_quantity
907 FROM pa_forecast_items
908 WHERE forecast_item_id = p_forecast_item_id;
909
910 l_bill_rate := l_item_amount/l_item_quantity;
911
912 l_raw_revenue := (l_bill_rate * p_quantity);
913 ELSE
914 l_bill_rate := l_item_amount/p_quantity;
915 l_raw_revenue := l_item_amount;
916 END IF;
917 END IF;
918 /* Moved these bunch of statement from if of project to here so that it execute every time */
919 IF p_labor_schdl_discnt IS NOT NULL THEN
920 l_labor_schdl_discnt := p_labor_schdl_discnt;
921 END IF;
922
923 IF p_labor_bill_rate_org_id IS NOT NULL THEN
924 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
925 END IF;
926
927 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
928 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
929 END IF;
930 IF p_labor_schdl_fixed_date IS NOT NULL THEN
931 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
932 END IF;
933 IF p_labor_sch_type IS NOT NULL THEN
934 l_labor_sch_type := p_labor_sch_type;
935 END IF;
936 IF p_bill_job_grp_id IS NOT NULL THEN
937 l_bill_job_grp_id := p_bill_job_grp_id;
938 END IF;
939 IF p_project_org_id IS NOT NULL THEN
940 l_project_org_id := p_project_org_id;
941 END IF;
942 IF p_emp_bill_rate_schedule_id IS NOT NULL THEN
943 l_emp_bill_rate_schedule_id := p_emp_bill_rate_schedule_id;
944 END IF;
945
946 /* The following code have been added for MCB 2 */
947 IF p_projfunc_currency_code IS NOT NULL THEN
948 l_projfunc_currency_code := p_projfunc_currency_code;
949 END IF;
950
951 /* Added for Asgmt overide */
952 IF p_assignment_precedes_task IS NOT NULL THEN
953 l_assignment_precedes_task := p_assignment_precedes_task;
954 END IF;
955 END IF; /* end if of forecast_item_id */
956
957 IF ( NVL(l_revenue_calculated_flag,'N') = 'N' ) THEN /* added for bug 2212852 { */
958
959
960 -- Selecting labor schedule discount,labor bill rate orgnization id,labor standard bill rate
961 -- schedule and labor schedule fixed date if any one of them is null then taking value from task
962 -- table only if passed task id is not null if it is null then taking value from project table
963 /* bug#4245956, added the p_called_Process='TASK or PROJECT' for RATE API */
964 IF ( ((p_labor_schdl_discnt IS NULL )OR (p_labor_bill_rate_org_id IS NULL)
965 OR (p_labor_std_bill_rate_schdl IS NULL) OR (p_labor_schdl_fixed_date IS NULL)OR
966 (p_labor_sch_type IS NULL) OR (p_bill_job_grp_id IS NULL)OR
967 (p_project_org_id IS NULL)) AND (p_called_process is NULL OR
968 ((p_called_process = 'TASK_LEVEL_PLANNING' OR
969 p_called_process = 'PROJECT_LEVEL_PLANNING'
970 ))) ) THEN
971 IF (p_task_id IS NULL ) THEN
972 BEGIN
973 SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
974 labor_schedule_fixed_date,labor_sch_type,bill_job_group_id,org_id,
975 emp_bill_rate_schedule_id,job_bill_rate_schedule_id,
976 projfunc_currency_code, /* Added the following column for MCB2 */
977 NVL(assign_precedes_task,'1') /* Added for Asgmt overide */
978 INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
979 l_labor_schdl_fixed_date,l_labor_sch_type,l_bill_job_grp_id,l_project_org_id,
980 l_emp_bill_rate_schedule_id,l_job_bill_rate_schedule_id,
981 l_projfunc_currency_code, /* Added the following columns for MCB2 */
982 l_assignment_precedes_task
983 FROM pa_projects_all
984 WHERE project_id = p_project_id;
985
986 EXCEPTION
987 WHEN NO_DATA_FOUND THEN
988 NULL;
989 END;
990 ELSE
991 BEGIN
992 SELECT labor_schedule_discount,labor_bill_rate_org_id,labor_std_bill_rate_schdl,
993 labor_schedule_fixed_date,labor_sch_type
994 INTO l_labor_schdl_discnt,l_labor_bill_rate_org_id,l_labor_std_bill_rate_schdl,
995 l_labor_schdl_fixed_date,l_labor_sch_type
996 FROM pa_tasks
997 WHERE task_id = p_task_id;
998 EXCEPTION
999 WHEN NO_DATA_FOUND THEN
1000 NULL;
1001 END;
1002 END IF;
1003 ELSE
1004 l_labor_schdl_discnt := p_labor_schdl_discnt;
1005 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
1006 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
1007 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
1008 l_labor_sch_type := p_labor_sch_type;
1009 l_bill_job_grp_id := p_bill_job_grp_id;
1010 l_project_org_id := p_project_org_id;
1011 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
1012 END IF;
1013
1014
1015 /* Moved these bunch of statement from if of project to here so that it execute every time */
1016 IF p_labor_schdl_discnt IS NOT NULL THEN
1017 l_labor_schdl_discnt := p_labor_schdl_discnt;
1018 END IF;
1019
1020 IF p_labor_bill_rate_org_id IS NOT NULL THEN
1021 l_labor_bill_rate_org_id := p_labor_bill_rate_org_id;
1022 END IF;
1023
1024 IF p_labor_std_bill_rate_schdl IS NOT NULL THEN
1025 l_labor_std_bill_rate_schdl := p_labor_std_bill_rate_schdl;
1026 END IF;
1027 IF p_labor_schdl_fixed_date IS NOT NULL THEN
1028 l_labor_schdl_fixed_date := p_labor_schdl_fixed_date;
1029 END IF;
1030 IF p_labor_sch_type IS NOT NULL THEN
1031 l_labor_sch_type := p_labor_sch_type;
1032 END IF;
1033 IF p_bill_job_grp_id IS NOT NULL THEN
1034 l_bill_job_grp_id := p_bill_job_grp_id;
1035 END IF;
1036 IF p_project_org_id IS NOT NULL THEN
1037 l_project_org_id := p_project_org_id;
1038 END IF;
1039 IF p_emp_bill_rate_schedule_id IS NOT NULL THEN
1040 l_emp_bill_rate_schedule_id := p_emp_bill_rate_schedule_id;
1041 END IF;
1042
1043 IF p_job_bill_rate_schedule_id IS NOT NULL THEN
1044 l_job_bill_rate_schedule_id := p_job_bill_rate_schedule_id;
1045 END IF;
1046
1047 /* The following code have been added for MCB 2 */
1048 IF p_projfunc_currency_code IS NOT NULL THEN
1049 l_projfunc_currency_code := p_projfunc_currency_code;
1050 END IF;
1051
1052 /* Added for Asgmt overide */
1053 IF p_assignment_precedes_task IS NOT NULL THEN
1054 l_assignment_precedes_task := p_assignment_precedes_task;
1055 END IF;
1056
1057 /* Checking if the labor schedule type is indirect then calling
1058 other api otherwise following the steps given be low { */
1059
1060 /* As the revenue is generated by applying burden on mcb_raw_cost when labor_schd_type is 'Indirect'
1061 changing the p_exp_raw_cost and l_expenditure_currency_code to mcb values -bug 2742778*/
1062
1063 IF ( l_labor_sch_type = 'I' ) THEN
1064 -- Calling burden cost API. This api will return the revnue so will skip the rest steps
1065 PA_COST.get_burdened_cost(p_project_type => p_project_type ,
1066 p_project_id => p_project_id ,
1067 p_task_id => p_task_id ,
1068 p_item_date => p_item_date ,
1069 p_expenditure_type => p_expenditure_type ,
1070 p_schedule_type => l_schedule_type ,
1071 px_exp_func_curr_code => l_mcb_currency_code ,
1072 p_Incurred_by_organz_id => p_Incurred_by_organz_id ,
1073 p_raw_cost => l_mcb_raw_cost ,
1074 p_raw_cost_rate => p_raw_cost_rate ,
1075 p_quantity => p_quantity ,
1076 p_override_to_organz_id => p_override_to_organz_id ,
1077 x_burden_cost => l_raw_revenue ,
1078 x_burden_cost_rate => l_bill_rate ,
1079 x_return_status => l_x_return_status ,
1080 x_msg_count => x_msg_count ,
1081 x_msg_data => x_msg_data);
1082
1083 /* l_rate_currency_code := l_expenditure_currency_code; -Commented for bug 2742778 and added the following line */
1084
1085 l_rate_currency_code := l_mcb_currency_code;
1086
1087 /* There was a call for PA_COST.get_projfunc_raw_burdened , it has been deleted
1088 for Org Forecasting */
1089
1090 ELSIF (l_labor_sch_type = 'B' ) THEN
1091
1092 /* This override is added for Assignment level override functionality ,
1093 it executed if the override precedence takes at assignment level i.e
1094 assignment_precedes_task is 'Y' */
1095
1096 /*------------------------------------------------------------------+
1097 | 1. Assignment level overrides |
1098 +------------------------------------------------------------------+
1099 | Set bill rate and raw revenue using Assignment level |
1100 | overrides . |
1101 +------------------------------------------------------------------*/
1102 /* If the call is from Assignment api then the item_id will be null so this override will
1103 not execute */
1104 /* Changes done for bug 2668753. Whenever MCB is 'Y' the denom_raw_cost and denom_curr_code
1105 are changed to l_mcb_raw_cost and l_mcb_currency_code */
1106 IF (p_item_id IS NOT NULL) THEN
1107 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL ) THEN
1108 IF ( l_assignment_precedes_task = 'Y') THEN
1109 IF (p_mcb_flag ='Y') THEN
1110 BEGIN
1111 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
1112 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)),
1113 DECODE(asgn.bill_rate_override, NULL,
1114 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
1115 * l_mcb_raw_cost / 100),l_mcb_currency_code),
1116 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT ((asgn.bill_rate_override *
1117 NVL(p_bill_rate_multiplier,1) * p_quantity),asgn.bill_rate_curr_override)),
1118 DECODE(asgn.bill_rate_override,NULL,l_mcb_currency_code,asgn.bill_rate_curr_override),
1119 asgn.markup_percent_override,
1120 'O',
1121 asgn.discount_percentage
1122 INTO l_bill_rate,l_raw_revenue,
1123 l_rate_currency_code,
1124 l_markup_percentage,
1125 l_amount_calculation_code,
1126 l_discount_percentage
1127 FROM pa_project_assignments asgn
1128 WHERE asgn.assignment_id = p_item_id;
1129
1130 EXCEPTION
1131 WHEN TOO_MANY_ROWS THEN
1132 RAISE;
1133 WHEN NO_DATA_FOUND THEN
1134 l_raw_revenue := NULL;
1135 l_bill_rate := NULL;
1136 END;
1137 ELSE
1138 BEGIN
1139 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
1140 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
1141 ),
1142 DECODE(asgn.bill_rate_override, NULL,
1143 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
1144 * p_raw_cost / 100),l_projfunc_currency_code),
1145 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
1146 * NVL(p_bill_rate_multiplier,1)
1147 * p_quantity),asgn.bill_rate_curr_override)),
1148 DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
1149 asgn.markup_percent_override,
1150 'O',
1151 asgn.discount_percentage
1152 INTO l_bill_rate,l_raw_revenue,
1153 l_rate_currency_code,
1154 l_markup_percentage,
1155 l_amount_calculation_code,
1156 l_discount_percentage
1157 FROM pa_project_assignments asgn
1158 WHERE asgn.assignment_id = p_item_id;
1159
1160 EXCEPTION
1161 WHEN TOO_MANY_ROWS THEN
1162 RAISE;
1163 WHEN NO_DATA_FOUND THEN
1164 l_raw_revenue := NULL;
1165 l_bill_rate := NULL;
1166 END;
1167 END IF; /* mcb flag */
1168 END IF; /* end of l_assignment_precedes_task flag check */
1169 IF g1_debug_mode = 'Y' THEN
1170 pa_debug.write_file('LOG','1000 Disc. Percent: ' || l_discount_percentage ||
1171 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1172 END IF;
1173 END IF; /* end of revenue and rate check */
1174 END IF;
1175
1176
1177
1178 /* When the procedure is called from finnancil planning api then
1179 the overrides should be used depending upon the value of p_called_process.
1180 If p_called_process ='PROJECT_LEVEL_PLANNING' THEN only project level overrides should be used and
1181 if p_called_process ='TASK_LEVEL_PLANNING' THEN only task level overrides should be used .
1182 This check is implemented by the parameter l_called_process. */
1183
1184 /*-------------------------------------------------------------+
1185 | 2. Emp Bill Rate Overrides for Task |
1186 +-------------------------------------------------------------+
1187 | Set bill rate and raw revenue using employee bill rate |
1188 | overrides for Task |
1189 +-------------------------------------------------------------*/
1190 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL and (p_called_process = 'PA' or p_called_process ='TASK_LEVEL_PLANNING')
1191 and l_discount_percentage is null) THEN
1192
1193 DECLARE
1194
1195 -- This cursor will select the bill rate and raw revenue on the basis of passed parameters i.e.
1196 -- if task id is not null then select will bring the task id row
1197
1198 CURSOR C_Task IS SELECT o.rate * NVL(p_bill_rate_multiplier,1) b_rate,
1199 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((o.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),o.rate_currency_code) r_revenue,
1200 o.rate_currency_code,
1201 'O',
1202 o.discount_percentage
1203 FROM pa_emp_bill_rate_overrides o
1204 WHERE o.person_id+0 = p_person_id
1205 AND o.task_id = p_task_id
1206 AND p_item_date
1207 BETWEEN o.start_date_active
1208 AND NVL(o.end_date_active,p_item_date);
1209
1210 l_true BOOLEAN := FALSE; --Flag is used to determine that wheather the cursor
1211 -- is returning more than one row or not.
1212 l_more_than_one_row EXCEPTION; -- Local exception using to check that cursor should not return
1213 -- more than one row
1214
1215 BEGIN
1216 -- Opening cursor and fetching row
1217
1218 FOR l_v_c_task IN C_Task LOOP
1219 -- Checking if the cursor is returning more than one row then error out
1220 IF (l_true) THEN
1221 RAISE l_more_than_one_row;
1222 ELSE
1223 l_true := TRUE;
1224 END IF;
1225
1226 -- Assigning the raw revenue to the local variable
1227 l_raw_revenue := l_v_c_task.r_revenue;
1228
1229 -- Assigning the bill rate to the local variable
1230 l_bill_rate := l_v_c_task.b_rate;
1231
1232
1233 -- Assigning the bill rate currency to the local variable for MCB2
1234 l_rate_currency_code := l_v_c_task.rate_currency_code;
1235
1236
1237 --Assigning Amount_calculation_code to the local variable.
1238 l_amount_calculation_code := 'O' ;
1239
1240 --Assigning discount_percentage to the local variable
1241 l_discount_percentage := l_v_c_task.discount_percentage ;
1242
1243
1244 END LOOP;
1245 IF g1_debug_mode = 'Y' THEN
1246 pa_debug.write_file('LOG','1001 Disc. Percent: ' || l_discount_percentage ||
1247 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1248 END IF;
1249
1250 EXCEPTION
1251 WHEN l_more_than_one_row THEN
1252 RAISE;
1253 WHEN NO_DATA_FOUND THEN
1254 l_raw_revenue := NULL;
1255 l_bill_rate := NULL;
1256 END;
1257
1258 END IF;
1259
1260
1261 /* This override is added for Assignment level override functionality ,
1262 it executed if the override precedence takes at Task level i.e
1263 assign_precedes_task = 'N' */
1264
1265 /*------------------------------------------------------------------+
1266 | 7. Assignment level overrides ,but Task take precedence |
1267 +------------------------------------------------------------------+
1268 | Set bill rate and raw revenue using Assignment level |
1269 | overrides . |
1270 +------------------------------------------------------------------*/
1271 /* If the call is from Assignment api then the item_id will be null so this override will
1272 not execute */
1273 /* Changes done for bug 2668753. Whenever MCB is 'Y' the denom_raw_cost and denom_curr_code
1274 are changed to l_mcb_raw_cost and l_mcb_currency_code */
1275
1276 IF (p_item_id IS NOT NULL ) THEN
1277 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL and l_discount_percentage is null ) THEN
1278 IF ( ( l_assignment_precedes_task = 'N') ) THEN /* Removed task id check to fix bug 2354746 */
1279 IF (p_mcb_flag ='Y') then
1280 BEGIN
1281 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
1282 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)),
1283 DECODE(asgn.bill_rate_override, NULL,
1284 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
1285 * l_mcb_raw_cost / 100),l_mcb_currency_code),
1286 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
1287 * NVL(p_bill_rate_multiplier,1)
1288 * p_quantity),asgn.bill_rate_curr_override)),
1289 DECODE(asgn.bill_rate_override,NULL,l_mcb_currency_code,asgn.bill_rate_curr_override),
1290 asgn.markup_percent_override,
1291 'O',
1292 asgn.discount_percentage
1293 INTO l_bill_rate,l_raw_revenue,
1294 l_rate_currency_code,
1295 l_markup_percentage,
1296 l_amount_calculation_code,
1297 l_discount_percentage
1298 FROM pa_project_assignments asgn
1299 WHERE asgn.assignment_id = p_item_id;
1300
1301 EXCEPTION
1302 WHEN TOO_MANY_ROWS THEN
1303 RAISE;
1304 WHEN NO_DATA_FOUND THEN
1305 l_raw_revenue := NULL;
1306 l_bill_rate := NULL;
1307 END;
1308 ELSE
1309 BEGIN
1310 SELECT DECODE(asgn.bill_rate_override, NULL, NULL,
1311 asgn.bill_rate_override * NVL(p_bill_rate_multiplier,1)
1312 ),
1313 DECODE(asgn.bill_rate_override, NULL,
1314 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + asgn.markup_percent_override)
1315 * p_raw_cost / 100),l_projfunc_currency_code),
1316 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((asgn.bill_rate_override
1317 * NVL(p_bill_rate_multiplier,1)
1318 * p_quantity),asgn.bill_rate_curr_override)),
1319 DECODE(asgn.bill_rate_override,NULL,l_projfunc_currency_code,asgn.bill_rate_curr_override),
1320 asgn.markup_percent_override,
1321 'O',
1322 asgn.discount_percentage
1323 INTO l_bill_rate,l_raw_revenue,
1324 l_rate_currency_code,
1325 l_markup_percentage,
1326 l_amount_calculation_code,
1327 l_discount_percentage
1328 FROM pa_project_assignments asgn
1329 WHERE asgn.assignment_id = p_item_id;
1330
1331 EXCEPTION
1332 WHEN TOO_MANY_ROWS THEN
1333 RAISE;
1334 WHEN NO_DATA_FOUND THEN
1335 l_raw_revenue := NULL;
1336 l_bill_rate := NULL;
1337 END;
1338 END IF; /* mcb flag */
1339 IF g1_debug_mode = 'Y' THEN
1340 pa_debug.write_file('LOG','1111 Disc. Percent: ' || l_discount_percentage || 'Revenue : ' || l_raw_revenue );
1341 END IF;
1342 END IF; /* end of l_assignment_precedes_task flag check */
1343 END IF; /* end of revenue and rate check */
1344 END IF;
1345
1346 /*-------------------------------------------------------------+
1347 | 8. Emp Bill Rate Overrides for Project |
1348 +-------------------------------------------------------------+
1349 | Set bill rate and raw revenue using employee bill rate |
1350 | overrides for Project |
1351 +-------------------------------------------------------------*/
1352 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL and l_discount_percentage is null) THEN
1353
1354 DECLARE
1355
1356 -- This cursor will select the bill rate and raw revenue on the basis of passed parameters i.e.
1357 -- if task id is null or not null then it will select according to the project id.
1358 -- select will bring all the raws except the row/rows which is already selected in
1359 -- task level select
1360
1361 CURSOR C_Project IS
1362 SELECT o2.rate * NVL(p_bill_rate_multiplier,1) b_rate,
1363 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((o2.rate *
1364 NVL(p_bill_rate_multiplier,1) * p_quantity),o2.rate_currency_code) r_revenue,
1365 o2.rate_currency_code,
1366 'O',
1367 o2.discount_percentage
1368 FROM pa_emp_bill_rate_overrides o2
1369 WHERE o2.person_id = p_person_id
1370 AND o2.project_id = p_project_id
1371 AND l_called_process <>2 /*Added for Doosan rate api change */
1372 AND p_item_date
1373 BETWEEN o2.start_date_active
1374 AND NVL(o2.end_date_active,p_item_date);
1375
1376 l_true BOOLEAN := FALSE; --Flag is used to determine that wheather the cursor
1377 -- is returning more than one row or not.
1378 l_more_than_one_row EXCEPTION; -- Local exception using to check that cursor should not return
1379 -- more than one row
1380
1381 BEGIN
1382 -- Opening cursor and fetching row
1383
1384 FOR l_v_c_project IN C_Project LOOP
1385 -- Checking if the cursor is returning more than one row then error out
1386 IF (l_true) THEN
1387 RAISE l_more_than_one_row;
1388 ELSE
1389 l_true := TRUE;
1390 END IF;
1391
1392 -- Assigning the raw revenue to the local variable
1393 l_raw_revenue := l_v_c_project.r_revenue;
1394
1395 -- Assigning the bill rate to the local variable
1396 l_bill_rate := l_v_c_project.b_rate;
1397
1398
1399 -- Assigning the bill rate currency to the local variable for MCB2
1400 l_rate_currency_code := l_v_c_project.rate_currency_code;
1401
1402
1403 --Assigning Amount_calculation_code to the local variable.
1404 l_amount_calculation_code := 'O' ;
1405
1406 --Assigning discount_percentage to the local variable
1407 l_discount_percentage := l_v_c_project.discount_percentage ;
1408
1409
1410 END LOOP;
1411
1412 IF g1_debug_mode = 'Y' THEN
1413 pa_debug.write_file('LOG','1002 Disc. Percent: ' || l_discount_percentage ||
1414 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1415 END IF;
1416 EXCEPTION
1417 WHEN l_more_than_one_row THEN
1418 RAISE;
1419 WHEN NO_DATA_FOUND THEN
1420 l_raw_revenue := NULL;
1421 l_bill_rate := NULL;
1422 END;
1423 END IF;
1424
1425 /*---------------------------------------------------------------+
1426 | 3. Task Job Bill Rate Overrides with Task Job Assn. Overrides |
1427 +---------------------------------------------------------------+
1428 | Set bill rate and raw revenue using Task job bill rate |
1429 | overrides with Task Job Assignment Overrides. |
1430 +---------------------------------------------------------------*/
1431 -- IT IS NOT IN THIS Forecasting so added p_called_process
1432 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1433 and l_discount_percentage is null and (p_called_process = 'PA' OR p_called_process='TASK_LEVEL_PLANNING')) THEN
1434
1435 BEGIN
1436
1437 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1438 PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1439 j.rate_currency_code,
1440 decode(j.discount_percentage,NULL,'O','T'),
1441 j.discount_percentage
1442 INTO l_bill_rate,l_raw_revenue,
1443 l_rate_currency_code,
1444 l_amount_calculation_code,
1445 l_discount_percentage
1446 FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
1447 WHERE j.task_id = p_task_id
1448 AND p_item_date
1449 BETWEEN j.start_date_active
1450 AND NVL(j.end_date_active,p_item_date)
1451 AND j.job_id+0 = a.job_id
1452 AND a.person_id = p_person_id
1453 AND a.task_id = p_task_id
1454 AND p_item_date
1455 BETWEEN a.start_date_active
1456 AND NVL(a.end_date_active,p_item_date);
1457
1458 IF g1_debug_mode = 'Y' THEN
1459 pa_debug.write_file('LOG','1003 Disc. Percent: ' || l_discount_percentage ||
1460 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1461 END IF;
1462
1463 EXCEPTION
1464 WHEN TOO_MANY_ROWS THEN
1465 RAISE;
1466 WHEN NO_DATA_FOUND THEN
1467 l_raw_revenue := NULL;
1468 l_bill_rate := NULL;
1469 END;
1470 END IF;
1471
1472 /*------------------------------------------------------------------+
1473 | 4. Project job bill rate overrides with Task Job Assn. Overrides |
1474 +------------------------------------------------------------------+
1475 | Set bill rate and raw revenue using Project job bill rate |
1476 | overrides with Task Job Assignment Overrides. |
1477 +------------------------------------------------------------------*/
1478
1479 -- IT IS NOT IN THIS Forecasting so added p_called_process
1480 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1481 and l_discount_percentage is null and p_called_process = 'PA') THEN
1482
1483 BEGIN
1484 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1485 PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1486 j.rate_currency_code,
1487 decode(j.discount_percentage,NULL,'O','T'),
1488 j.discount_percentage
1489 INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
1490 l_amount_calculation_code,l_discount_percentage
1491 FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
1492 WHERE j.project_id = p_project_id
1493 AND p_item_date
1494 BETWEEN j.start_date_active
1495 AND NVL(j.end_date_active,p_item_date)
1496 AND j.job_id+0 = a.job_id
1497 AND a.person_id = p_person_id
1498 AND a.task_id = p_task_id
1499 AND l_called_process =0 /*Added for Doosan rate api change */
1500 AND p_item_date
1501 BETWEEN a.start_date_active
1502 AND NVL(a.end_date_active,p_item_date);
1503
1504 IF g1_debug_mode = 'Y' THEN
1505 pa_debug.write_file('LOG','1004 Disc. Percent: ' || l_discount_percentage ||
1506 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1507 END IF;
1508 EXCEPTION
1509 WHEN TOO_MANY_ROWS THEN
1510 RAISE;
1511 WHEN NO_DATA_FOUND THEN
1512 l_raw_revenue := NULL;
1513 l_bill_rate := NULL;
1514 END;
1515 END IF;
1516
1517 /*---------------------------------------------------------------------------+
1518 | 6. Task job bill rate overrides with project Job Assignments overrides |
1519 +---------------------------------------------------------------------------+
1520 | Set bill rate and raw revenue using Task job bill rate |
1521 | overrides with Project Job Assignment Overrides. |
1522 +--------------------------------------------------------------------------*/
1523
1524 -- IT IS NOT IN THIS Forecasting so added p_called_process
1525 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1526 and l_discount_percentage is null and p_called_process = 'PA') THEN
1527 BEGIN
1528 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1529 PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1530 j.rate_currency_code,
1531 decode(j.discount_percentage,NULL,'O','P'),
1532 j.discount_percentage
1533 INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
1534 l_amount_calculation_code,l_discount_percentage
1535 FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
1536 WHERE j.task_id = p_task_id
1537 AND p_item_date
1538 BETWEEN j.start_date_active
1539 AND NVL(j.end_date_active,
1540 p_item_date)
1541 AND j.job_id+0 = a.job_id
1542 AND a.person_id = p_person_id
1543 AND a.project_id = p_project_id
1544 AND l_called_process =0 /*Added for Doosan rate api change */
1545 AND p_item_date
1546 BETWEEN a.start_date_active
1547 AND NVL(a.end_date_active,p_item_date);
1548
1549 IF g1_debug_mode = 'Y' THEN
1550 pa_debug.write_file('LOG','1005 Disc. Percent: ' || l_discount_percentage ||
1551 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1552 END IF;
1553 EXCEPTION
1554 WHEN TOO_MANY_ROWS THEN
1555 RAISE;
1556 WHEN NO_DATA_FOUND THEN
1557 l_raw_revenue := NULL;
1558 l_bill_rate := NULL;
1559 END;
1560 END IF;
1561
1562 /*--------------------------------------------------------------------+
1563 | 9. Project job bill rate overrides with Project Job Assn. Overrides|
1564 +--------------------------------------------------------------------+
1565 | Set bill rate and raw revenue using Project job bill rate |
1566 | overrides with Project Job Assignment Overrides. |
1567 +--------------------------------------------------------------------*/
1568
1569 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1570 and l_discount_percentage is null ) THEN
1571
1572 BEGIN
1573 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1574 PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
1575 j.rate_currency_code,
1576 decode(j.discount_percentage,NULL,'O','P'),
1577 j.discount_percentage
1578 INTO l_bill_rate,l_raw_revenue,
1579 l_rate_currency_code,
1580 l_amount_calculation_code,
1581 l_discount_percentage
1582 FROM pa_job_assignment_overrides a, pa_job_bill_rate_overrides j
1583 WHERE j.project_id = p_project_id
1584 AND p_item_date
1585 BETWEEN j.start_date_active
1586 AND NVL(j.end_date_active,p_item_date)
1587 AND j.job_id+0 = a.job_id
1588 AND a.person_id = p_person_id
1589 AND a.project_id = p_project_id
1590 AND l_called_process <>2 /*Added for Doosan rate api change */
1591 AND p_item_date
1592 BETWEEN a.start_date_active
1593 AND NVL(a.end_date_active,p_item_date) ;
1594
1595 IF g1_debug_mode = 'Y' THEN
1596 pa_debug.write_file('LOG','1006 Disc. Percent: ' || l_discount_percentage ||
1597 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1598 END IF;
1599
1600 EXCEPTION
1601 WHEN TOO_MANY_ROWS THEN
1602 RAISE;
1603 WHEN NO_DATA_FOUND THEN
1604 l_raw_revenue := NULL;
1605 l_bill_rate := NULL;
1606 END;
1607 END IF;
1608
1609 /*------------------------------------------------------------------+
1610 | 12. Task job bill rate overrides with primary Job Assignments |
1611 +------------------------------------------------------------------+
1612 | Set bill rate and raw revenue using Task job bill rate |
1613 | overrides with primary Job Assignment. |
1614 +------------------------------------------------------------------*/
1615
1616 -- IT IS NOT IN THIS Forecasting so added p_called_process
1617 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1618 and l_discount_percentage is null and (p_called_process = 'PA'/* or p_called_process='TASK_LEVEL_PLANNING'*/)) THEN
1619
1620 BEGIN
1621 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1622 PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
1623 j.rate_currency_code,
1624 decode(j.discount_percentage,NULL,'O','J'),
1625 j.discount_percentage
1626 INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
1627 l_amount_calculation_code,
1628 l_discount_percentage
1629 FROM -- per_assignments_f a, /* Bug 6058676 : Removed per_assignments_f and related joins */
1630 pa_job_bill_rate_overrides j
1631 -- Bug 4398492 query made to refer base table per_all_assignments_f
1632 -- (0 * a.person_id) is used to make assignments as the driving table
1633 WHERE j.task_id = p_task_id -- + (0 * a.person_id)
1634 AND p_item_date
1635 BETWEEN j.start_date_active
1636 AND NVL(j.end_date_active,p_item_date)
1637 /* AND j.job_id = a.job_id commented for bug 3193077 */
1638 AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Added for bug 3193077 */
1639 --AND a.person_id = p_person_id /* Commented for Bug 6058676*/
1640 --AND a.primary_flag || '' = 'Y'
1641 -- AND a.assignment_type = 'E' /* bug 2911451 */
1642 --AND a.assignment_type IN ('E','C') -- Modified for CWK changes /* Commented for Bug 6058676*/
1643 AND l_called_process <>1 ; /*Added for Doosan rate api change */
1644 /* AND p_item_date
1645 BETWEEN a.effective_start_date Commented for Bug 6058676
1646 AND a.effective_end_date ; */
1647
1648 IF g1_debug_mode = 'Y' THEN
1649 pa_debug.write_file('LOG','1007 Disc. Percent: ' || l_discount_percentage ||
1650 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1651 END IF;
1652
1653 EXCEPTION
1654 WHEN TOO_MANY_ROWS THEN
1655 RAISE;
1656 WHEN NO_DATA_FOUND THEN
1657 l_raw_revenue := NULL;
1658 l_bill_rate := NULL;
1659 END;
1660
1661 ELSIF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1662 and l_discount_percentage is null and (/*p_called_process = 'PA' */ p_called_process='TASK_LEVEL_PLANNING')) THEN
1663
1664 BEGIN
1665 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1666 PA_CURRENCY.ROUND_CURRENCY_AMT((j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
1667 j.rate_currency_code,
1668 decode(j.discount_percentage,NULL,'O','J'),
1669 j.discount_percentage
1670 INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
1671 l_amount_calculation_code,
1672 l_discount_percentage
1673 FROM pa_job_bill_rate_overrides j
1674 WHERE j.task_id = p_task_id
1675 AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */ /*bug3737994*/
1676 AND p_item_date
1677 BETWEEN j.start_date_active
1678 AND NVL(j.end_date_active,p_item_date)
1679 AND l_called_process <>1; /*Added for Doosan rate api change */
1680
1681 IF g1_debug_mode = 'Y' THEN
1682 pa_debug.write_file('LOG','1007 Disc. Percent: ' || l_discount_percentage ||
1683 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1684 END IF;
1685
1686 EXCEPTION
1687 WHEN TOO_MANY_ROWS THEN
1688 RAISE;
1689 WHEN NO_DATA_FOUND THEN
1690 l_raw_revenue := NULL;
1691 l_bill_rate := NULL;
1692 END;
1693 END IF;
1694
1695 /*------------------------------------------------------------------+
1696 | 13. Project job bill rate overrides with primary Job Assignment |
1697 +------------------------------------------------------------------+
1698 | Set bill rate and raw revenue using Project job bill rate |
1699 | overrides with primary Job Assignment. |
1700 +------------------------------------------------------------------*/
1701
1702 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1703 and l_discount_percentage is null and l_called_process = 0) THEN
1704
1705 BEGIN
1706 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1707 PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1708 j.rate_currency_code,
1709 decode(j.discount_percentage,NULL,'O','J'),
1710 j.discount_percentage
1711 INTO l_bill_rate,l_raw_revenue,
1712 l_rate_currency_code,
1713 l_amount_calculation_code,
1714 l_discount_percentage
1715 FROM pa_job_bill_rate_overrides j --, per_all_assignments_f a /* Bug 6058676: Removed per_assignments_f and related predicates*/
1716 -- Bug 4398492 query made to refer base table per_all_assignments_f
1717 WHERE j.project_id = p_project_id -- + (0 * a.person_id)
1718 AND p_item_date + 0.99999
1719 BETWEEN j.start_date_active
1720 AND NVL(j.end_date_active + 0.99999,p_item_date + 0.99999)
1721 AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id);
1722 --AND a.person_id = p_person_id
1723 --AND a.primary_flag = 'Y'
1724 -- AND a.assignment_type = 'E' /* bug 2911451 */
1725 --AND a.assignment_type IN ('E','C') -- Modified for CWK changes
1726 --AND p_item_date BETWEEN a.effective_start_date AND a.effective_end_date ;
1727
1728 IF g1_debug_mode = 'Y' THEN
1729 pa_debug.write_file('LOG','1008 Disc. Percent: ' || l_discount_percentage ||
1730 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1731 END IF;
1732 EXCEPTION
1733 WHEN TOO_MANY_ROWS THEN
1734 RAISE;
1735 WHEN NO_DATA_FOUND THEN
1736 l_raw_revenue := NULL;
1737 l_bill_rate := NULL;
1738 END;
1739
1740 ELSIF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL
1741 and l_discount_percentage is null and l_called_process = 1) THEN /*Bug3737994 2 to 1*/
1742 /*Bug3737994 commented the code reference to per_assignments_f and added p_resource_job_id*/
1743 BEGIN
1744 SELECT j.rate * NVL(p_bill_rate_multiplier,1),
1745 PA_CURRENCY.ROUND_CURRENCY_AMT(j.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1746 j.rate_currency_code,
1747 decode(j.discount_percentage,NULL,'O','J'),
1748 j.discount_percentage
1749 INTO l_bill_rate,l_raw_revenue,
1750 l_rate_currency_code,
1751 l_amount_calculation_code,
1752 l_discount_percentage
1753 FROM pa_job_bill_rate_overrides j--, per_assignments_f a
1754 WHERE j.project_id = p_project_id --+ (0 * a.person_id)
1755 AND p_item_date + 0.99999
1756 BETWEEN j.start_date_active
1757 AND NVL(j.end_date_active + 0.99999,p_item_date + 0.99999)
1758 AND j.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) ;--pa_cross_business_grp.IsmappedTojob(a.job_id,l_bill_job_grp_id) /* Bug 6058676 */
1759 -- AND a.person_id = p_person_id
1760 -- AND a.primary_flag = 'Y'
1761 -- AND a.assignment_type = 'E' /* bug 2911451 */
1762 -- AND a.assignment_type IN ('E','C') -- Modified for CWK changes
1763 -- AND p_item_date BETWEEN a.effective_start_date AND a.effective_end_date ;
1764
1765 IF g1_debug_mode = 'Y' THEN
1766 pa_debug.write_file('LOG','1008 Disc. Percent: ' || l_discount_percentage ||
1767 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1768 END IF;
1769 EXCEPTION
1770 WHEN TOO_MANY_ROWS THEN
1771 RAISE;
1772 WHEN NO_DATA_FOUND THEN
1773 l_raw_revenue := NULL;
1774 l_bill_rate := NULL;
1775 END;
1776 END IF;
1777
1778 /*------------------------------------------------------------+
1779 |14. Labor Multipliers |
1780 +------------------------------------------------------------+
1781 | Set bill rate, raw revenue using labor multipliers. |
1782 | (Task first, then Project) V2.0 |
1783 +------------------------------------------------------------*/
1784 /* Changes done for bug 2668753. Whenever MCB is 'Y' the denom_raw_cost and denom_curr_code
1785 are changed to l_mcb_raw_cost and l_mcb_currency_code */
1786
1787 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND p_raw_cost IS NOT NULL
1788 and l_discount_percentage is null ) THEN
1789
1790 DECLARE
1791 -- This cursor will select the bill rate and raw revenue on the basis of passed parameters i.e.
1792 -- if task id is null then it will select according to the project id but if task id is not
1793 -- null then first select will bring the task id row and second select
1794 -- will bring all the raws ( If exists ) except the row which is already selected in first select
1795
1796 CURSOR C1 IS( SELECT NULL b_rate,
1797 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1798 (m.labor_multiplier * decode(p_mcb_flag,'Y',l_mcb_raw_cost,p_raw_cost)),
1799 decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code)) r_revenue,
1800 decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code) curr_code
1801 FROM pa_labor_multipliers m
1802 WHERE m.task_id = p_task_id
1803 AND l_called_process <>1 /*Added for Doosan rate api change */
1804 AND p_item_date
1805 BETWEEN m.start_date_active
1806 AND NVL(m.end_date_active,p_item_date)
1807 UNION ALL
1808 SELECT NULL b_rate,
1809 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
1810 (m2.labor_multiplier * decode(p_mcb_flag,'Y',l_mcb_raw_cost,p_raw_cost)),
1811 decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code)) r_revenue,
1812 decode(p_mcb_flag,'Y',l_mcb_currency_code,l_projfunc_currency_code) curr_code
1813 FROM pa_labor_multipliers m2
1814 WHERE m2.project_id = p_project_id
1815 AND l_called_process <>2 /*Added for Doosan rate api change */
1816 AND p_item_date
1817 BETWEEN m2.start_date_active
1818 AND NVL(m2.end_date_active,p_item_date)
1819 AND NOT EXISTS
1820 ( SELECT NULL
1821 FROM pa_labor_multipliers m3
1822 WHERE m3.task_id = p_task_id
1823 AND l_called_process <>1 /*Added for Doosan rate api change */
1824 AND p_item_date
1825 BETWEEN m3.start_date_active
1826 AND NVL(m3.end_date_active,p_item_date)
1827 ));
1828 l_true BOOLEAN := FALSE; --Flag is used to determine that wheather the cursor
1829 -- is returning more than one row or not.
1830 l_more_than_one_row EXCEPTION; -- Local exception using to check that cursor should not return
1831 -- more than one row
1832
1833 BEGIN
1834 -- Opening cursor and fetching row
1835
1836 FOR l_v_c1 IN C1 LOOP
1837 -- Checking if the cursor is returning more than one row then error out
1838 IF (l_true) THEN
1839 RAISE l_more_than_one_row;
1840 ELSE
1841 l_true := TRUE;
1842 END IF;
1843
1844 -- Assigning the raw revenue to the local variable
1845 l_raw_revenue := l_v_c1.r_revenue;
1846
1847 -- Assigning the bill rate to the local variable
1848 l_bill_rate := l_v_c1.b_rate;
1849
1850 -- Assigning the bill rate currency to the local variable for MCB2
1851 l_rate_currency_code := l_v_c1.curr_code ;
1852
1853 --Assigning Amount_calculation_code to the local variable.
1854 l_amount_calculation_code := 'O' ;
1855
1856 --Assigning discount_percentage to the local variable
1857 l_discount_percentage := Null;
1858
1859 END LOOP;
1860
1861 IF (l_raw_revenue IS NOT NULL) THEN
1862 x_raw_revenue := l_raw_revenue;
1863 x_bill_rate := l_bill_rate;
1864 END IF;
1865
1866 IF g1_debug_mode = 'Y' THEN
1867 pa_debug.write_file('LOG','1009 Disc. Percent: ' || l_discount_percentage ||
1868 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1869 END IF;
1870 EXCEPTION
1871 WHEN l_more_than_one_row THEN
1872 RAISE;
1873 WHEN NO_DATA_FOUND THEN
1874 l_raw_revenue := NULL;
1875 l_bill_rate := NULL;
1876 END;
1877 END IF;
1878
1879 /*------------------------------------------------------------+
1880 |15. Standard Employee bill rate schedule . |
1881 | Set bill rate, raw revenue, adjusted rate, adjusted |
1882 | revenue using standard employee bill rate schedule. |
1883 +------------------------------------------------------------*/
1884 /* Changes done for bug 2668753. Whenever MCB is 'Y' the denom_raw_cost and denom_curr_code
1885 are changed to l_mcb_raw_cost and l_mcb_currency_code */
1886
1887 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND nvl(l_amount_calculation_code,'O')='O') THEN
1888 IF(p_mcb_flag='Y') then
1889 BEGIN
1890 SELECT DECODE(b.rate, NULL, NULL,
1891 b.rate * NVL(p_bill_rate_multiplier,1)
1892 ),
1893 DECODE(b.rate, NULL,
1894 ((100 + b.markup_percentage) * l_mcb_raw_cost / 100),
1895 (b.rate * NVL(p_bill_rate_multiplier,1) *
1896 p_quantity)),
1897 DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1898 (b.rate * NVL(p_bill_rate_multiplier,1) *
1899 (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
1900 DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1901 DECODE(b.rate, NULL,
1902 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + b.markup_percentage)
1903 * (l_mcb_raw_cost / 100)
1904 * (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100),
1905 l_mcb_currency_code),
1906 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((b.rate * p_quantity) *
1907 NVL(p_bill_rate_multiplier,1) *
1908 (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100),
1909 b.rate_currency_code))),
1910 DECODE(b.rate, NULL,l_mcb_currency_code,b.rate_currency_code) /* Added for MCB2-Added for bug 2697945 */,
1911 b.markup_percentage, /* Added for Asgmt overide */
1912 DECODE(l_discount_percentage,NULL,'B','O'),
1913 nvl(l_discount_percentage,l_labor_schdl_discnt)
1914 INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
1915 l_rate_currency_code /* Added for MCB2 */,
1916 l_markup_percentage, /* Added for Asgmt overide */
1917 l_amount_calculation_code,
1918 l_discount_percentage
1919 FROM pa_bill_rates_all b
1920 WHERE b.bill_rate_sch_id = l_emp_bill_rate_schedule_id
1921 AND b.person_id = p_person_id
1922 AND NVL(l_labor_schdl_fixed_date,p_item_date)
1923 BETWEEN b.start_date_active
1924 AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
1925 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
1926
1927 EXCEPTION
1928 WHEN TOO_MANY_ROWS THEN
1929 RAISE;
1930 WHEN NO_DATA_FOUND THEN
1931 l_raw_revenue := NULL;
1932 l_bill_rate := NULL;
1933 END;
1934 ElSE
1935 BEGIN
1936 SELECT DECODE(b.rate, NULL, NULL,
1937 b.rate * NVL(p_bill_rate_multiplier,1)
1938 ),
1939 DECODE(b.rate, NULL,((100 + b.markup_percentage) * p_raw_cost / 100),
1940 (b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity)),
1941 DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1942 (b.rate * NVL(p_bill_rate_multiplier,1) *
1943 (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
1944 DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1945 DECODE(b.rate, NULL,
1946 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((100 + b.markup_percentage)
1947 * (p_raw_cost / 100) * (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100), l_projfunc_currency_Code),
1948 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(((b.rate * p_quantity)* NVL(p_bill_rate_multiplier,1)* (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100), b.rate_currency_code))),
1949 DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code) /* Added for MCB2 */,
1950 b.markup_percentage ,/* Added for Asgmt overide */
1951 DECODE(l_discount_percentage,NULL,'B','O'),
1952 nvl(l_discount_percentage,l_labor_schdl_discnt)
1953 INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
1954 l_rate_currency_code /* Added for MCB2 */,
1955 l_markup_percentage ,/* Added for Asgmt overide */
1956 l_amount_calculation_code,
1957 l_discount_percentage
1958 FROM pa_bill_rates_all b
1959 WHERE b.bill_rate_sch_id = l_emp_bill_rate_schedule_id
1960 AND b.person_id = p_person_id
1961 AND NVL(l_labor_schdl_fixed_date,p_item_date)
1962 BETWEEN b.start_date_active
1963 AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
1964 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
1965
1966 EXCEPTION
1967 WHEN TOO_MANY_ROWS THEN
1968 RAISE;
1969 WHEN NO_DATA_FOUND THEN
1970 l_raw_revenue := NULL;
1971 l_bill_rate := NULL;
1972 END;
1973 END IF; /* MCB FLAG */
1974 IF g1_debug_mode = 'Y' THEN
1975 pa_debug.write_file('LOG','1010 Disc. Percent: ' || l_discount_percentage ||
1976 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
1977 END IF;
1978 END IF;
1979
1980 /*------------------------------------------------------------------+
1981 | 5. Task job bill rate schedule with task job assn. overrides |
1982 +------------------------------------------------------------------+
1983 | Set bill rate and raw revenue using task job bill rate |
1984 | schedule with task job assignment overrides -Kal |
1985 +------------------------------------------------------------------*/
1986
1987
1988 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND p_task_id IS NOT NULL
1989 and (p_called_process = 'PA'or p_called_process = 'TASK_LEVEL_PLANNING') AND (nvl(l_amount_calculation_code,'O') IN ('T','O'))) THEN
1990
1991
1992 BEGIN
1993 SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_bill_rate_multiplier,1)),
1994 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) * p_quantity),
1995 DECODE(nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1996 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1) *
1997 (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) /100)),
1998 DECODE( nvl(l_discount_percentage,l_labor_schdl_discnt), NULL, NULL,
1999 PA_CURRENCY.ROUND_CURRENCY_AMT((b.rate * p_quantity)
2000 * NVL(p_bill_rate_multiplier,1)
2001 * (100 - nvl(l_discount_percentage,l_labor_schdl_discnt)) / 100)),
2002 DECODE(l_discount_percentage,NULL,'B','O'),
2003 nvl(l_discount_percentage,l_labor_schdl_discnt),
2004 b.rate_currency_code
2005 INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue, l_amount_calculation_code,
2006 l_discount_percentage,
2007 l_rate_currency_code /*Rate added for bug 2636678 */
2008 FROM pa_bill_rates_all b, pa_job_assignment_overrides ao
2009 WHERE ao.person_id = p_person_id
2010 AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2011 AND b.job_id = ao.job_id(+)
2012 AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
2013 AND p_task_id = ao.task_id
2014 AND l_called_process <>1 /*Added for Doosan rate api change */
2015 AND p_item_date
2016 BETWEEN ao.start_date_active
2017 AND NVL(ao.end_date_active,p_item_date)
2018 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2019 BETWEEN b.start_date_active
2020 AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
2021 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
2022
2023 IF g1_debug_mode = 'Y' THEN
2024 pa_debug.write_file('LOG','1011 Disc. Percent: ' || l_discount_percentage ||
2025 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
2026 END IF;
2027
2028 EXCEPTION
2029 WHEN TOO_MANY_ROWS THEN
2030 RAISE;
2031 WHEN NO_DATA_FOUND THEN
2032 l_raw_revenue := NULL;
2033 l_bill_rate := NULL;
2034 END ;
2035 END IF;
2036
2037 /*------------------------------------------------------------------+
2038 | 10. Task job bill rate schedule with Project Job Assn. Overrides |
2039 +------------------------------------------------------------------+
2040 | Set bill rate and raw revenue using task job bill rate |
2041 | schedule with project job assignment overrides -Kal |
2042 +------------------------------------------------------------------*/
2043
2044 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND (nvl(l_amount_calculation_code,'O') IN ('P','O')))
2045 THEN
2046 BEGIN
2047 SELECT DECODE(b.rate, NULL, NULL,b.rate * NVL(p_bill_rate_multiplier,1)),
2048 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)*
2049 p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
2050 b.rate_currency_code,
2051 DECODE(l_discount_percentage,NULL,'B','O'),
2052 nvl(l_discount_percentage,l_labor_schdl_discnt)
2053 INTO l_bill_rate,l_raw_revenue,l_rate_currency_code,
2054 l_amount_calculation_code,l_discount_percentage
2055 FROM pa_bill_rates_all b, pa_job_assignment_overrides ao, pa_tasks t
2056 WHERE ao.person_id = p_person_id
2057 AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2058 AND b.job_id = ao.job_id(+)
2059 AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
2060 AND t.project_id = ao.project_id
2061 AND t.task_id = p_task_id
2062 AND l_called_process <>2 /*Added for Doosan rate api change */
2063 AND p_item_date
2064 BETWEEN ao.start_date_active
2065 AND NVL(ao.end_date_active,p_item_date)
2066 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2067 BETWEEN b.start_date_active
2068 AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date));
2069 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
2070
2071 IF g1_debug_mode = 'Y' THEN
2072 pa_debug.write_file('LOG','1012 Disc. Percent: ' || l_discount_percentage || 'Revenue : '
2073 || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
2074 END IF;
2075 EXCEPTION
2076 WHEN TOO_MANY_ROWS THEN
2077 RAISE;
2078 WHEN NO_DATA_FOUND THEN
2079 l_raw_revenue := NULL;
2080 l_bill_rate := NULL;
2081 END;
2082 END IF;
2083
2084 /*---------------------------------------------------------------------+
2085 | 11. Project job bill rate schedule with Project Job Assn. Overrides |
2086 +---------------------------------------------------------------------+
2087 | Set bill rate and raw revenue using task job bill rate |
2088 | schedule with project job assignment overrides -Kal |
2089 +--------------------------------------------------------------------
2090
2091 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND p_called_process is NULL ) THEN
2092 BEGIN
2093 SELECT DECODE(b.rate, NULL, NULL,
2094 b.rate * NVL(p_bill_rate_multiplier,1)),
2095 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2096 * p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
2097 b.rate_currency_code,
2098 DECODE(l_discount_percentage,NULL,'B','O'),
2099 nvl(l_discount_percentage,l_labor_schdl_discnt)
2100 INTO l_bill_rate,l_raw_revenue,
2101 l_rate_currency_code,
2102 l_amount_calculation_code,
2103 l_discount_percentage
2104 FROM pa_bill_rates_all b, pa_job_assignment_overrides ao
2105 WHERE ao.person_id = p_person_id
2106 AND b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2107 AND b.job_id = ao.job_id
2108 AND ao.project_id = p_project_id
2109 AND p_item_date
2110 BETWEEN ao.start_date_active
2111 AND NVL(ao.end_date_active,p_item_date)
2112 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2113 BETWEEN b.start_date_active
2114 AND NVL(b.end_date_active,NVL(l_labor_schdl_fixed_date,p_item_date))
2115 AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99);
2116 EXCEPTION
2117 WHEN TOO_MANY_ROWS THEN
2118 RAISE;
2119 WHEN NO_DATA_FOUND THEN
2120 l_raw_revenue := NULL;
2121 l_bill_rate := NULL;
2122 END;
2123 END IF; */
2124
2125
2126 /*------------------------------------------------------------+
2127 |16. Standard Job bill rate schedule |
2128 +------------------------------------------------------------+
2129 | Set bill rate, raw revenue, adjusted rate, adjusted |
2130 | revenue using standard job bill rate schedule. |
2131 +------------------------------------------------------------*/
2132
2133 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND l_called_process =0 AND
2134 (nvl(l_amount_calculation_code,'O') IN ('J','O')) ) THEN
2135 BEGIN
2136 SELECT DECODE(b.rate, NULL, NULL,
2137 b.rate * NVL(p_bill_rate_multiplier,1)),
2138 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2139 * p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
2140 b.rate_currency_code,
2141 DECODE(l_discount_percentage,NULL,'B','O'),
2142 nvl(l_discount_percentage,l_labor_schdl_discnt)
2143 INTO l_bill_rate,l_raw_revenue,
2144 l_rate_currency_code,
2145 l_amount_calculation_code,
2146 l_discount_percentage
2147 FROM pa_bill_rates_all b -- per_assignments_f pa Commented for Bug 4398492 query made to refer base table
2148 , per_all_assignments_f pa
2149 WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2150 AND pa.person_id = p_person_id
2151 AND pa.primary_flag = 'Y'
2152 -- AND pa.assignment_type = 'E'
2153 AND pa.assignment_type IN ('E','C') -- Modified for CWK changes
2154 AND p_item_date /* BUG#3118592 */
2155 BETWEEN pa.effective_start_date
2156 AND pa.effective_end_date
2157 AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Changed the join instead of joining with p_resource_job_id, now joining using function IsmappedTojob to fix bug 2155331 */ /* Bug 6058676 */
2158 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2159 BETWEEN b.start_date_active
2160 AND NVL(b.end_date_active,
2161 NVL(l_labor_schdl_fixed_date,p_item_date));
2162 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
2163 EXCEPTION
2164 WHEN TOO_MANY_ROWS THEN
2165 RAISE;
2166 WHEN NO_DATA_FOUND THEN
2167 l_raw_revenue := NULL;
2168 l_bill_rate := NULL;
2169 END;
2170 IF g1_debug_mode = 'Y' THEN
2171 pa_debug.write_file('LOG','1013 Disc. Percent: ' || l_discount_percentage ||
2172 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
2173 END IF;
2174 ELSIF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND l_called_process <>0 AND
2175 (nvl(l_amount_calculation_code,'O') IN ('J','O')) ) THEN
2176 BEGIN
2177 SELECT DECODE(b.rate, NULL, NULL,
2178 b.rate * NVL(p_bill_rate_multiplier,1)),
2179 PA_CURRENCY.ROUND_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
2180 * p_quantity * (100 - (nvl(l_discount_percentage,nvl(l_labor_schdl_discnt,0))))/100),
2181 b.rate_currency_code,
2182 DECODE(l_discount_percentage,NULL,'B','O'),
2183 nvl(l_discount_percentage,l_labor_schdl_discnt)
2184 INTO l_bill_rate,l_raw_revenue,
2185 l_rate_currency_code,
2186 l_amount_calculation_code,
2187 l_discount_percentage
2188 FROM pa_bill_rates_all b
2189 WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2190 AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id) /* Bug 6058676 */
2191 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2192 BETWEEN b.start_date_active
2193 AND NVL(b.end_date_active,
2194 NVL(l_labor_schdl_fixed_date,p_item_date));
2195 -- AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99); /* Commented for Bug 6041769 */
2196 EXCEPTION
2197 WHEN TOO_MANY_ROWS THEN
2198 RAISE;
2199 WHEN NO_DATA_FOUND THEN
2200 l_raw_revenue := NULL;
2201 l_bill_rate := NULL;
2202 END;
2203 IF g1_debug_mode = 'Y' THEN
2204 pa_debug.write_file('LOG','1013 Disc. Percent: ' || l_discount_percentage ||
2205 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
2206 END IF;
2207 END IF ;
2208
2209
2210
2211 /*------------------------------------------------------------+
2212 |16a. Standard Job bill rate schedule at Project |
2213 +------------------------------------------------------------+
2214 | Set bill rate, raw revenue, adjusted rate, adjusted |
2215 | revenue using standard job bill rate schedule. |
2216 +------------------------------------------------------------
2217
2218 IF ( l_raw_revenue IS NULL AND l_bill_rate IS NULL AND p_called_process !='PA') THEN
2219
2220 BEGIN
2221 SELECT DECODE(b.rate, NULL, NULL,
2222 b.rate * NVL(p_bill_rate_multiplier,1)
2223 ),
2224 DECODE(b.rate, NULL,
2225 ((100 + b.markup_percentage) *
2226 p_raw_cost / 100),
2227 (b.rate * NVL(p_bill_rate_multiplier,1) *
2228 p_quantity)),
2229 DECODE(l_labor_schdl_discnt, NULL, NULL,
2230 (b.rate * NVL(p_bill_rate_multiplier,1) *
2231 (100 - l_labor_schdl_discnt) /100)),
2232 DECODE(l_labor_schdl_discnt, NULL, NULL,
2233 DECODE(b.rate, NULL,
2234 ((100 + b.markup_percentage)
2235 * (p_raw_cost / 100)
2236 * (100 - l_labor_schdl_discnt) / 100),
2237 ((b.rate * p_quantity)
2238 * NVL(p_bill_rate_multiplier,1)
2239 * (100 - l_labor_schdl_discnt) / 100)
2240 )
2241 ),
2242 DECODE(b.rate, NULL,l_projfunc_currency_code,b.rate_currency_code)
2243 b.markup_percentage
2244 INTO l_bill_rate,l_raw_revenue,l_adjusted_rate,l_adjusted_revenue,
2245 l_rate_currency_code
2246 l_markup_percentage
2247 FROM pa_bill_rates_all b
2248 WHERE b.bill_rate_sch_id = l_job_bill_rate_schedule_id
2249 AND b.job_id = pa_cross_business_grp.IsmappedTojob(p_resource_job_id,l_bill_job_grp_id)
2250 AND NVL(l_labor_schdl_fixed_date,p_item_date)
2251 BETWEEN b.start_date_active
2252 AND NVL(b.end_date_active,
2253 NVL(l_labor_schdl_fixed_date,p_item_date))
2254 AND NVL(b.org_id,-99) = NVL(l_project_org_id,-99);
2255
2256 EXCEPTION
2257 WHEN TOO_MANY_ROWS THEN
2258 RAISE;
2259 WHEN NO_DATA_FOUND THEN
2260 l_raw_revenue := NULL;
2261 l_bill_rate := NULL;
2262 END;
2263 END IF; */
2264
2265 END IF; /* end of sch check }*/
2266 END IF; /* end if revenue calculated flag } */
2267
2268
2269 IF g1_debug_mode = 'Y' THEN
2270 pa_debug.write_file('LOG','9999 Disc. Percent: ' || l_discount_percentage ||
2271 'Revenue : ' || l_raw_revenue || 'currency_code : ' || l_rate_currency_code);
2272 END IF;
2273 l_txn_bill_rate := l_bill_rate; -- Removed NVL condition for bug 5079230
2274
2275 IF (l_adjusted_revenue IS NOT NULL ) THEN
2276 l_txn_raw_revenue := NVL(l_adjusted_revenue,0);
2277 ELSE
2278 l_txn_raw_revenue := NVL(l_raw_revenue,0);
2279 END IF;
2280
2281 IF ( ( l_txn_raw_revenue IS NULL) OR (l_txn_raw_revenue = 0) ) THEN
2282 RAISE l_no_revenue;
2283 END IF;
2284
2285 /*bug 4169912 passed the adjusted rate after applying the discount percentage if its not calculated above*/
2286 l_adjusted_rate :=NVL(l_adjusted_rate ,(l_txn_bill_rate *(100 - l_discount_percentage)/100));
2287 IF l_adjusted_rate =0 then
2288 l_adjusted_rate :=NULl;
2289 END IF;
2290 /*end of bug 4169912*/
2291 x_raw_revenue := NVL(l_txn_raw_revenue,0) ;
2292 x_bill_rate := l_txn_bill_rate ; -- Removed NVL condition for bug 5079230
2293 x_adjusted_bill_rate:= l_adjusted_rate; --4038485
2294 x_txn_currency_code := l_rate_currency_code ; /* Added for Org Forecasting */
2295 x_markup_percentage := l_markup_percentage; /* Added for Asgmt overide */
2296
2297 x_return_status := l_x_return_status;
2298 IF g1_debug_mode = 'Y' THEN
2299 pa_debug.write_file('LOG','Last statement in Assignment rev');
2300 END IF;
2301 EXCEPTION
2302 WHEN l_no_revenue THEN
2303 x_bill_rate := NULL;
2304 x_raw_revenue:= 0;
2305 x_markup_percentage := NULL; /* Added for Asgmt overide */
2306 x_txn_currency_code := l_rate_currency_code; /* Added for bug 3385744 */
2307 x_adjusted_bill_rate := NULL ; --4038485
2308 /* Checking error condition. Added for bug 2218386 */
2309 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2310 PA_UTILS.add_message('PA', 'PA_FCST_NO_BILL_RATE');
2311 END IF;
2312 x_return_status := FND_API.G_RET_STS_ERROR;
2313 x_msg_count := 1;
2314 IF p_called_process IS NULL THEN
2315 x_msg_data := 'PA_FCST_NO_BILL_RATE';
2316 END IF;
2317 WHEN OTHERS THEN
2318 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2319 x_msg_count := 1;
2320 x_msg_data := SUBSTR(SQLERRM,1,30);
2321
2322 /* ATG Changes */
2323
2324 x_bill_rate := null;
2325 x_raw_revenue := null;
2326 x_rev_currency_code := null;
2327 x_markup_percentage := null;
2328 x_txn_currency_code := null;
2329 x_adjusted_bill_rate := null;
2330
2331 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2332 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REVENUE', /* Moved this here to fix bug 2434663 */
2333 p_procedure_name => 'Assignment_Rev_Amt');
2334 RAISE;
2335 END IF;
2336 IF p_called_process ='PA' then
2337 raise;
2338 END IF;
2339 END Assignment_Rev_Amt;
2340
2341 /* This is new procedure created for Org Forecasting */
2342
2343 -- This procedure will convert the transaction amounts in Project,and Project Functional.
2344 -- Input/Output parameters
2345 -- Parameters Type Required Description
2346 -- p_item_date DATE YES Forecast Item date
2347 -- px_txn_curr_code VARCHA2 YES Transaction currency
2348 -- px_txn_raw_revenue NUMBER YES Raw revenue in Transaction currency
2349 -- px_txn_bill_rate NUMBER YES Bill rate in Transaction currency
2350 -- px_projfunc_curr_code VARCHA2 YES Project functional currency(PFC)
2351 -- p_projfunc_bil_rate_date_code VARCHAR2 No Bill rate date code of PFC
2352 -- px_projfunc_bil_rate_type VARCHAR2 No Bill rate type of PFC
2353 -- px_projfunc_bil_rate_date DATE No Bill rate date code of PFC
2354 -- px_projfunc_bil_exchange_rate NUMBER No Bill exchange rate of PFC
2355 -- px_projfunc_raw_revenue NUMBER YES Raw revenue in PFC
2356 -- px_projfunc_bill_rate NUMBER YES Bill rate in PFC
2357 -- px_project_curr_code VARCHA2 YES Project currency(PC)
2358 -- p_project_bil_rate_date_code VARCHAR2 No Bill rate date code of PC
2359 -- px_project_bil_rate_type VARCHAR2 No Bill rate type of PC
2360 -- px_project_bil_rate_date DATE No Bill rate date code of PC
2361 -- px_project_bil_exchange_rate NUMBER No Bill exchange rate of PC
2362 -- px_project_raw_revenue NUMBER YES Raw revenue in PC
2363 -- px_project_bill_rate NUMBER YES Bill rate in PC
2364
2365
2366 PROCEDURE Get_Converted_Revenue_Amounts(
2367 p_item_date IN DATE,
2368 px_txn_curr_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2369 px_txn_raw_revenue IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2370 px_txn_bill_rate IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2371 px_projfunc_curr_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2372 p_projfunc_bil_rate_date_code IN VARCHAR2,
2373 px_projfunc_bil_rate_type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2374 px_projfunc_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
2375 px_projfunc_bil_exchange_rate IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2376 px_projfunc_raw_revenue IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2377 px_projfunc_bill_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2378 px_project_curr_code IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2379 p_project_bil_rate_date_code IN VARCHAR2,
2380 px_project_bil_rate_type IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2381 px_project_bil_rate_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
2382 px_project_bil_exchange_rate IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2383 px_project_raw_revenue IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2384 px_project_bill_rate IN OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2385 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
2386 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2387 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
2388 IS
2389
2390 l_x_return_status VARCHAR2(50); -- It will be used to store the return status
2391 -- and used it to validate whether the
2392 -- calling procedure has run successfully
2393 -- or encounter any error
2394 l_txn_bill_rate NUMBER :=null; -- store bill amount transaction curr.
2395 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
2396 l_rate_currency_code PA_BILL_RATES_all.rate_currency_code%TYPE;
2397 l_denominator NUMBER;
2398 l_numerator NUMBER;
2399 l_status VARCHAR2(30);
2400
2401 l_converted_projfunc_rev_amt NUMBER;
2402 l_converted_projfunc_bill_rate NUMBER :=null;
2403 l_conversion_projfunc_date DATE; -- store item date
2404 l_converted_project_rev_amount NUMBER;
2405 l_converted_project_bill_rate NUMBER :=null;
2406 l_conversion_project_date DATE; -- store item date
2407
2408 l_projfunc_currency_code PA_PROJECTS_ALL.projfunc_currency_code%TYPE;
2409 l_projfunc_bil_rate_date_code PA_PROJECTS_ALL.projfunc_bil_rate_date_code%TYPE;
2410 l_projfunc_bil_rate_type PA_PROJECTS_ALL.projfunc_bil_rate_type%TYPE;
2411 l_projfunc_bil_rate_date PA_PROJECTS_ALL.projfunc_bil_rate_date%TYPE;
2412 l_projfunc_bil_exchange_rate PA_PROJECTS_ALL.projfunc_bil_exchange_rate%TYPE;
2413
2414 l_project_currency_code PA_PROJECTS_ALL.project_currency_code%TYPE;
2415 l_project_bil_rate_date_code PA_PROJECTS_ALL.project_bil_rate_date_code%TYPE;
2416 l_project_bil_rate_type PA_PROJECTS_ALL.project_bil_rate_type%TYPE;
2417 l_project_bil_rate_date PA_PROJECTS_ALL.project_bil_rate_date%TYPE;
2418 l_project_bil_exchange_rate PA_PROJECTS_ALL.project_bil_exchange_rate%TYPE;
2419
2420
2421 /* ATG Changes */
2422
2423 lx_txn_curr_code VARCHAR2(15);
2424 lx_txn_raw_revenue NUMBER;
2425 lx_txn_bill_rate NUMBER;
2426 lx_projfunc_curr_code VARCHAR2(15);
2427 lx_projfunc_bil_rate_type VARCHAR2(30);
2428 lx_projfunc_bil_rate_date DATE;
2429 lx_projfunc_bil_exchange_rate NUMBER;
2430 lx_projfunc_raw_revenue NUMBER;
2431 lx_projfunc_bill_rate NUMBER;
2432 lx_project_curr_code VARCHAR2(15);
2433 lx_project_bil_rate_type VARCHAR2(30);
2434 lx_project_bil_rate_date DATE;
2435 lx_project_bil_exchange_rate NUMBER;
2436 lx_project_raw_revenue NUMBER;
2437 lx_project_bill_rate NUMBER;
2438
2439
2440
2441 BEGIN
2442
2443 /* ATG Changes */
2444
2445 lx_txn_curr_code := px_txn_curr_code;
2446 lx_txn_raw_revenue := px_txn_raw_revenue;
2447 lx_txn_bill_rate := px_txn_bill_rate ;
2448 lx_projfunc_curr_code := px_projfunc_curr_code ;
2449 lx_projfunc_bil_rate_type := px_projfunc_bil_rate_type ;
2450 lx_projfunc_bil_rate_date := px_projfunc_bil_rate_date;
2451 lx_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate;
2452 lx_projfunc_raw_revenue := px_projfunc_raw_revenue;
2453 lx_projfunc_bill_rate := px_projfunc_bill_rate;
2454 lx_project_curr_code := px_project_curr_code ;
2455 lx_project_bil_rate_type := px_project_bil_rate_type;
2456 lx_project_bil_rate_date := px_project_bil_rate_date;
2457 lx_project_bil_exchange_rate := px_project_bil_exchange_rate;
2458 lx_project_raw_revenue := px_project_raw_revenue ;
2459 lx_project_bill_rate := px_project_bill_rate;
2460
2461
2462
2463
2464 -- Initializing return status with success sothat if some unexpected error comes
2465 -- , we change its status from succes to error sothat we can take necessary step to rectify the problem
2466 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2467
2468 -------------------------------------------------------------------------------
2469 -- Assigning the denorm raw revenue, rate and Project, Project Functional
2470 -- conversion attributes to local variables
2471 ------------------------------------------------------------------------------
2472
2473 l_rate_currency_code := px_txn_curr_code;
2474 l_txn_raw_revenue := NVL(px_txn_raw_revenue,0);
2475 l_txn_bill_rate := px_txn_bill_rate; -- Removed NVL condition for bug 5079230
2476
2477 l_projfunc_currency_code := px_projfunc_curr_code;
2478 l_projfunc_bil_rate_date_code := p_projfunc_bil_rate_date_code;
2479 l_projfunc_bil_rate_type := px_projfunc_bil_rate_type;
2480 l_projfunc_bil_rate_date := px_projfunc_bil_rate_date;
2481 l_projfunc_bil_exchange_rate := px_projfunc_bil_exchange_rate;
2482
2483 l_project_currency_code := px_project_curr_code;
2484 l_project_bil_rate_date_code := p_project_bil_rate_date_code;
2485 l_project_bil_rate_type := px_project_bil_rate_type;
2486 l_project_bil_rate_date := px_project_bil_rate_date;
2487 l_project_bil_exchange_rate := px_project_bil_exchange_rate;
2488
2489
2490 --------------------------------------------------------------------------------------
2491 -- Checking for Currencies if null
2492 --------------------------------------------------------------------------------------
2493 IF (l_projfunc_currency_code IS NULL ) THEN
2494 RAISE l_invalid_projfunc_curr_code;
2495 END IF;
2496 IF (l_project_currency_code IS NULL ) THEN
2497 RAISE l_invalid_proj_curr_code;
2498 END IF;
2499 IF (l_rate_currency_code IS NULL ) THEN
2500 RAISE l_invalid_txn_curr_code;
2501 END IF;
2502
2503 ---------------------------------------------------------------------------------------
2504 -- Start Conversion code to convert the Transaction Revenue/Rate in Project Functional
2505 -- Taking project rate date , because all the transaction has to go under same date for EIs and rate
2506 ---------------------------------------------------------------------------------------
2507 IF ( l_projfunc_bil_rate_date_code = 'FIXED_DATE') THEN
2508 l_conversion_projfunc_date := l_projfunc_bil_rate_date;
2509 ELSE
2510 l_conversion_projfunc_date := p_item_date;
2511 END IF;
2512
2513
2514 ----------------------------------------------------------------------------
2515 -- Get the Raw Revenue in Project Functional
2516 ---------------------------------------------------------------------------
2517 PA_MULTI_CURRENCY.convert_amount(
2518 P_FROM_CURRENCY => l_rate_currency_code,
2519 P_TO_CURRENCY => l_projfunc_currency_code,
2520 P_CONVERSION_DATE => l_conversion_projfunc_date,
2521 P_CONVERSION_TYPE => l_projfunc_bil_rate_type,
2522 P_AMOUNT => l_txn_raw_revenue,
2523 P_USER_VALIDATE_FLAG => 'Y',
2524 P_HANDLE_EXCEPTION_FLAG => 'Y',
2525 P_CONVERTED_AMOUNT => l_converted_projfunc_rev_amt,
2526 P_DENOMINATOR => l_denominator,
2527 P_NUMERATOR => l_numerator,
2528 P_RATE => l_projfunc_bil_exchange_rate,
2529 X_STATUS => l_status);
2530
2531 IF (l_status IS NOT NULL) THEN
2532 RAISE l_conversion_fail;
2533 END IF;
2534
2535 ----------------------------------------------------------------------------
2536 -- Get the Rate in Project Functional
2537 ---------------------------------------------------------------------------
2538 PA_MULTI_CURRENCY.convert_amount(
2539 P_FROM_CURRENCY => l_rate_currency_code,
2540 P_TO_CURRENCY => l_projfunc_currency_code,
2541 P_CONVERSION_DATE => l_conversion_projfunc_date,
2542 P_CONVERSION_TYPE => l_projfunc_bil_rate_type,
2543 P_AMOUNT => l_txn_bill_rate,
2544 P_USER_VALIDATE_FLAG => 'Y',
2545 P_HANDLE_EXCEPTION_FLAG => 'Y',
2546 P_CONVERTED_AMOUNT => l_converted_projfunc_bill_rate,
2547 P_DENOMINATOR => l_denominator,
2548 P_NUMERATOR => l_numerator,
2549 P_RATE => l_projfunc_bil_exchange_rate,
2550 X_STATUS => l_status);
2551
2552 IF (l_status IS NOT NULL) THEN
2553 RAISE l_conversion_fail;
2554 END IF;
2555
2556 ----------------------------------------------------------------------------
2557 -- Start Conversion code to convert the Transaction Revenue/Rate in Project
2558 -- Taking project rate date , because all the transaction has to go under same date for EIs and rate
2559 ---------------------------------------------------------------------------
2560 IF ( l_project_bil_rate_date_code = 'FIXED_DATE') THEN
2561 l_conversion_project_date := l_project_bil_rate_date;
2562 ELSE
2563 l_conversion_project_date := p_item_date;
2564 END IF;
2565
2566
2567 ----------------------------------------------------------------------------
2568 -- Get the Raw Revenue in Project
2569 ---------------------------------------------------------------------------
2570 PA_MULTI_CURRENCY.convert_amount(
2571 P_FROM_CURRENCY => l_rate_currency_code,
2572 P_TO_CURRENCY => l_project_currency_code,
2573 P_CONVERSION_DATE => l_conversion_project_date,
2574 P_CONVERSION_TYPE => l_project_bil_rate_type,
2575 P_AMOUNT => l_txn_raw_revenue,
2576 P_USER_VALIDATE_FLAG => 'Y',
2577 P_HANDLE_EXCEPTION_FLAG => 'Y',
2578 P_CONVERTED_AMOUNT => l_converted_project_rev_amount,
2579 P_DENOMINATOR => l_denominator,
2580 P_NUMERATOR => l_numerator,
2581 P_RATE => l_project_bil_exchange_rate,
2582 X_STATUS => l_status);
2583
2584 IF (l_status IS NOT NULL) THEN
2585 RAISE l_conversion_fail;
2586 END IF;
2587
2588 ----------------------------------------------------------------------------
2589 -- Get the Rate in Project
2590 ---------------------------------------------------------------------------
2591 PA_MULTI_CURRENCY.convert_amount(
2592 P_FROM_CURRENCY => l_rate_currency_code,
2593 P_TO_CURRENCY => l_project_currency_code,
2594 P_CONVERSION_DATE => l_conversion_project_date,
2595 P_CONVERSION_TYPE => l_project_bil_rate_type,
2596 P_AMOUNT => l_txn_bill_rate,
2597 P_USER_VALIDATE_FLAG => 'Y',
2598 P_HANDLE_EXCEPTION_FLAG => 'Y',
2599 P_CONVERTED_AMOUNT => l_converted_project_bill_rate,
2600 P_DENOMINATOR => l_denominator,
2601 P_NUMERATOR => l_numerator,
2602 P_RATE => l_project_bil_exchange_rate,
2603 X_STATUS => l_status);
2604
2605 IF (l_status IS NOT NULL) THEN
2606 RAISE l_conversion_fail;
2607 END IF;
2608
2609 -------------------------------------------------------------------------------
2610 -- Assigning the back the local variable to denorm raw revenue, rate and Project,
2611 -- Project Functional
2612 ------------------------------------------------------------------------------
2613 px_txn_curr_code := l_rate_currency_code;
2614 px_txn_raw_revenue := NVL(l_txn_raw_revenue,0);
2615 px_txn_bill_rate := l_txn_bill_rate; -- Removed NVL condition for bug 5079230
2616
2617 px_projfunc_curr_code := l_projfunc_currency_code;
2618 px_projfunc_bil_rate_date := l_conversion_projfunc_date;
2619 px_projfunc_bil_rate_type := l_projfunc_bil_rate_type;
2620 px_projfunc_bil_exchange_rate := l_projfunc_bil_exchange_rate;
2621 px_projfunc_raw_revenue := NVL(l_converted_projfunc_rev_amt,0);
2622 px_projfunc_bill_rate := l_converted_projfunc_bill_rate; -- Removed NVL condition for bug 5079230
2623
2624 px_project_curr_code := l_project_currency_code;
2625 px_project_bil_rate_date := l_conversion_project_date;
2626 px_project_bil_rate_type := l_project_bil_rate_type;
2627 px_project_bil_exchange_rate := l_project_bil_exchange_rate;
2628 px_project_raw_revenue := NVL(l_converted_project_rev_amount,0);
2629 px_project_bill_rate := l_converted_project_bill_rate; -- Removed NVL condition for bug 5079230
2630
2631 x_return_status := l_x_return_status;
2632 EXCEPTION
2633 WHEN l_invalid_projfunc_curr_code THEN
2634 px_txn_raw_revenue := 0;
2635 px_txn_bill_rate := NULL;
2636 px_projfunc_raw_revenue := 0;
2637 px_projfunc_bill_rate := 0;
2638 px_project_raw_revenue := 0;
2639 px_project_bill_rate := NULL;
2640
2641 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2642 PA_UTILS.add_message('PA', 'PA_MISSING_PRJFUNC_CURR');
2643 END IF;
2644
2645 x_return_status := FND_API.G_RET_STS_ERROR;
2646 x_msg_count := 1;
2647 x_msg_data := 'PA_MISSING_PRJFUNC_CURR';
2648 WHEN l_invalid_txn_curr_code THEN
2649 px_txn_raw_revenue := 0;
2650 px_txn_bill_rate := NULL;
2651 px_projfunc_raw_revenue := 0;
2652 px_projfunc_bill_rate := 0;
2653 px_project_raw_revenue := 0;
2654 px_project_bill_rate := NULL;
2655
2656 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2657 PA_UTILS.add_message('PA', 'PA_REQUIRE_DENOM_CURR');
2658 END IF;
2659
2660 x_return_status := FND_API.G_RET_STS_ERROR;
2661 x_msg_count := 1;
2662 x_msg_data := 'PA_REQUIRE_DENOM_CURR';
2663 WHEN l_invalid_proj_curr_code THEN
2664 px_txn_raw_revenue := 0;
2665 px_txn_bill_rate := NULL;
2666 px_projfunc_raw_revenue := 0;
2667 px_projfunc_bill_rate := 0;
2668 px_project_raw_revenue := 0;
2669 px_project_bill_rate := NULL;
2670
2671 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2672 PA_UTILS.add_message('PA', 'PA_MISSING_PROJ_CURR');
2673 END IF;
2674
2675 x_return_status := FND_API.G_RET_STS_ERROR;
2676 x_msg_count := 1;
2677 x_msg_data := 'PA_MISSING_PROJ_CURR';
2678 WHEN l_conversion_fail THEN
2679 px_txn_raw_revenue := 0;
2680 px_txn_bill_rate := NULL;
2681 px_projfunc_raw_revenue := 0;
2682 px_projfunc_bill_rate := 0;
2683 px_project_raw_revenue := 0;
2684 px_project_bill_rate := NULL;
2685
2686 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2687 PA_UTILS.add_message('PA', l_status||'_BC_PF');
2688 END IF;
2689
2690 x_return_status := FND_API.G_RET_STS_ERROR;
2691 x_msg_count := 1;
2692 x_msg_data := l_status||'_BC_PF';
2693
2694 WHEN OTHERS THEN
2695 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2696 x_msg_count := 1;
2697 x_msg_data := SUBSTR(SQLERRM,1,30);
2698
2699 /* ATG Changes */
2700
2701 px_txn_curr_code := lx_txn_curr_code;
2702 px_txn_raw_revenue := lx_txn_raw_revenue;
2703 px_txn_bill_rate := lx_txn_bill_rate ;
2704 px_projfunc_curr_code := lx_projfunc_curr_code ;
2705 px_projfunc_bil_rate_type := lx_projfunc_bil_rate_type ;
2706 px_projfunc_bil_rate_date := lx_projfunc_bil_rate_date;
2707 px_projfunc_bil_exchange_rate := lx_projfunc_bil_exchange_rate;
2708 px_projfunc_raw_revenue := lx_projfunc_raw_revenue;
2709 px_projfunc_bill_rate := lx_projfunc_bill_rate;
2710 px_project_curr_code := lx_project_curr_code ;
2711 px_project_bil_rate_type := lx_project_bil_rate_type;
2712 px_project_bil_rate_date := lx_project_bil_rate_date;
2713 px_project_bil_exchange_rate := lx_project_bil_exchange_rate;
2714 px_project_raw_revenue := lx_project_raw_revenue ;
2715 px_project_bill_rate := lx_project_bill_rate;
2716
2717
2718 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
2719 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REVENUE', /* Moved this here to fix bug 2434663 */
2720 p_procedure_name => 'Get_Converted_Revenue_Amounts');
2721 RAISE;
2722 END IF;
2723
2724
2725 END Get_Converted_Revenue_Amounts;
2726
2727
2728 PROCEDURE Non_Labor_Rev_amount(
2729 p_project_id IN NUMBER ,
2730 p_task_id IN NUMBER ,
2731 p_bill_rate_multiplier IN NUMBER ,
2732 p_quantity IN NUMBER ,
2733 p_raw_cost IN NUMBER ,
2734 p_burden_cost IN NUMBER ,
2735 p_denom_raw_cost IN NUMBER ,
2736 p_denom_burdened_cost IN NUMBER ,
2737 p_expenditure_item_date IN DATE ,
2738 p_task_bill_rate_org_id IN NUMBER ,
2739 p_project_bill_rate_org_id IN NUMBER ,
2740 p_task_std_bill_rate_sch IN VARCHAR2 DEFAULT NULL ,
2741 p_project_std_bill_rate_sch IN VARCHAR2 DEFAULT NULL ,
2742 p_project_org_id IN NUMBER ,
2743 p_sl_function IN NUMBER,
2744 p_denom_currency_code IN VARCHAR2 ,
2745 p_proj_func_currency IN VARCHAR2 ,
2746 p_expenditure_type IN VARCHAR2 ,
2747 p_non_labor_resource IN VARCHAR2 ,
2748 p_task_sch_date IN DATE ,
2749 p_project_sch_date IN DATE ,
2750 p_project_sch_discount IN NUMBER ,
2751 p_task_sch_discount IN NUMBER ,
2752 p_mcb_flag IN VARCHAR2 ,
2753 p_non_labor_sch_type IN VARCHAR2 ,
2754 p_project_type IN VARCHAR2 ,
2755 p_exp_raw_cost IN NUMBER,
2756 p_raw_cost_rate IN NUMBER ,
2757 p_Incurred_by_organz_id IN NUMBER ,
2758 p_override_to_organz_id IN VARCHAR2 ,
2759 px_exp_func_curr_code IN OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2760 x_raw_revenue OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2761 x_rev_Curr_code OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2762 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2763 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
2764 x_msg_data OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
2765 /* Added for bug 2668753 */
2766 p_project_raw_cost IN NUMBER DEFAULT NULL,
2767 p_project_currency_code IN VARCHAR2 DEFAULT NULL,
2768 p_project_burdened_cost IN NUMBER DEFAULT NULL,
2769 p_proj_func_burdened_cost IN NUMBER DEFAULT NULL,
2770 p_exp_func_burdened_cost IN NUMBER DEFAULT NULL,
2771 /*Added for Doosan rate api changes */
2772 p_task_nl_std_bill_rate_sch_id IN NUMBER DEFAULT NULL,
2773 p_proj_nl_std_bill_rate_sch_id IN NUMBER DEFAULT NULL,
2774 p_called_process IN VARCHAR2 DEFAULT NULL,
2775 x_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2776 x_markup_percentage OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2777 x_adjusted_bill_rate OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
2778 p_uom_flag IN NUMBER DEFAULT 1
2779
2780 )
2781 AS
2782
2783
2784 l_raw_revenue NUMBER :=null; -- store the raw revenue
2785 l_bill_rate NUMBER;
2786 l_trans_adjust_amount NUMBER;
2787 l_more_than_one_row_excep EXCEPTION;
2788 l_true BOOLEAN := FALSE;
2789 l_no_revenue EXCEPTION;
2790 l_txn_raw_revenue NUMBER :=null; -- store the raw revenue trans. curr.
2791 l_rate_discount_pct NUMBER;
2792 l_x_return_status VARCHAR2(50); -- store the return status
2793 -- and used it to validate whether the
2794 -- calling procedure has run successfully
2795 -- or encounter any error
2796 /* Added for bug 2668753 */
2797 l_mcb_cost_flag varchar2(50) := null;
2798 l_mcb_raw_cost number := null;
2799 l_mcb_burdened_cost number := null;
2800 l_mcb_currency_code varchar2(50) := null;
2801
2802 --l_msg_count NUMBER;
2803 -- l_msg_data VARCHAR2(100);
2804 l_proj_std_bill_rate_sch_id NUMBER;/*Added for bug 2690011*/
2805 l_task_std_bill_rate_sch_id NUMBER;
2806 l_called_process NUMBER; /*Added for Doosan rate api enhancement */
2807 l_adjusted_bill_rate NUMBER:=NULL; --4038485
2808
2809 lx_exp_func_curr_code varchar2(15);
2810
2811
2812 BEGIN
2813
2814 /* ATG Changes */
2815
2816 lx_exp_func_curr_code := px_exp_func_curr_code ;
2817
2818
2819 /* Adding the following piece of code for Doosan rate api changes . */
2820
2821 l_called_process := 0;
2822
2823 IF P_called_process ='PROJECT_LEVEL_PLANNING' THEN
2824 l_called_process :=1;
2825 END IF;
2826
2827 IF P_called_process ='TASK_LEVEL_PLANNING' THEN
2828 l_called_process :=2;
2829 END IF;
2830 -- Initializing return status with success so that if some unexpected error comes
2831 -- , we change its status from succes to error sothat we can take necessary step to rectify the problem
2832 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
2833
2834 /* Checking if the labor schedule type is indirect then calling other api
2835 otherwise following the steps given below { */
2836
2837 /* Changes for bug 2668753 */
2838
2839 /* Bug 2668753 : Get the BTC_COST_BASE_REV_CODE from pa_projects_all table */
2840 IF ( nvl(p_mcb_flag,'N') = 'Y' ) THEN
2841 BEGIN
2842
2843 /* Added the following nvl so that code does not break even when upgrade script fails-bug 2742778 */
2844
2845 select nvl(BTC_COST_BASE_REV_CODE,'EXP_TRANS_CURR')
2846 into l_mcb_cost_flag
2847 from pa_projects_all
2848 where project_id = p_project_id;
2849
2850 EXCEPTION
2851 WHEN NO_DATA_FOUND THEN
2852 RAISE ;
2853 END;
2854
2855 IF (l_mcb_cost_flag = 'EXP_TRANS_CURR') THEN
2856 l_mcb_raw_cost := p_denom_raw_cost;
2857 l_mcb_currency_code := p_denom_currency_code;
2858 l_mcb_burdened_cost := p_denom_burdened_cost;
2859
2860 ELSIF (l_mcb_cost_flag = 'EXP_FUNC_CURR') THEN
2861 l_mcb_raw_cost := p_exp_raw_cost;
2862 l_mcb_currency_code := px_exp_func_curr_code;
2863 l_mcb_burdened_cost := p_exp_func_burdened_cost;
2864
2865 ELSIF (l_mcb_cost_flag = 'PROJ_FUNC_CURR') THEN
2866 l_mcb_raw_cost := p_raw_cost;
2867 l_mcb_currency_code := p_proj_func_currency;
2868 l_mcb_burdened_cost := p_proj_func_burdened_cost;
2869
2870 ELSIF (l_mcb_cost_flag = 'PROJECT_CURR') THEN
2871 l_mcb_raw_cost := p_project_raw_cost;
2872 l_mcb_currency_code := p_project_currency_code;
2873 l_mcb_burdened_cost := p_project_burdened_cost;
2874
2875 END IF;
2876 /* Added for bug 2726298 */
2877
2878 ELSIF(nvl(p_mcb_flag,'N')='N') THEN
2879 l_mcb_raw_cost := p_raw_cost;
2880 l_mcb_currency_code := p_proj_func_currency;
2881 l_mcb_burdened_cost := p_proj_func_burdened_cost;
2882
2883 END IF;
2884 /* End of changes for bug 2668753 */
2885
2886 /* As the revenue is generated by applying burden on mcb_raw_cost when non_labor_schd_type is 'Indirect'
2887 changing the exp_raw_cost and exp_func_curr_code to mcb values -bug 2668753*/
2888 IF ( p_non_labor_sch_type = 'I' ) THEN
2889 -- Calling burden cost API
2890 PA_COST.get_burdened_cost(p_project_type => p_project_type ,
2891 p_project_id => p_project_id ,
2892 p_task_id => p_task_id ,
2893 p_item_date => p_expenditure_item_date ,
2894 p_expenditure_type => p_expenditure_type ,
2895 p_schedule_type => 'REVENUE' ,
2896 px_exp_func_curr_code => l_mcb_currency_code ,
2897 p_Incurred_by_organz_id => p_Incurred_by_organz_id ,
2898 p_raw_cost => l_mcb_raw_cost ,
2899 p_raw_cost_rate => p_raw_cost_rate ,
2900 p_quantity => p_quantity ,
2901 p_override_to_organz_id => p_override_to_organz_id ,
2902 x_burden_cost => l_raw_revenue ,
2903 x_burden_cost_rate => l_bill_rate ,
2904 x_return_status => l_x_return_status ,
2905 x_msg_count => x_msg_count ,
2906 x_msg_data => x_msg_data);
2907
2908 -- x_rev_curr_code := px_exp_func_curr_code; /* Commented this line and added the following line for bug 2726298 */
2909 x_rev_curr_code := l_mcb_currency_code;
2910
2911 x_raw_revenue := l_raw_revenue;
2912 /* Added the following out parameters for Doosan rate api changes */
2913
2914 x_bill_rate :=l_bill_rate;
2915 x_markup_percentage :=null;
2916
2917 ELSIF (p_non_labor_sch_type = 'B' ) THEN
2918
2919 /*------------------------------------------------------------+
2920 |22. Non non_labor resource bill rate overrides |
2921 +------------------------------------------------------------+
2922 | Set bill rate and raw revenue using non non_labor resource |
2923 | bill rate overrides. |
2924 +------------------------------------------------------------*/
2925 /*bill_rate, bill_trans_raw_revenue,bill_trans_currency_code,
2926 amount_calculation_code,bill_markup_percentage,discount_percentage,
2927 non_labor_multiplier,rate_source_id */
2928
2929 /*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
2930 other audit columns.
2931 - Amount calculation code = 'O' for overrides
2932 - Bill Transaction Currency code is from overrides table.
2933 - Change column from raw_revenue to bill_trans_raw_revnue
2934 (Bill rate and Raw revenue should update only in Bill transaction currency)
2935 - Change the WHERE clause from raw_revenue IS NULL to
2936 bill_trans_raw_revenue IS NULL
2937 - Update denom raw cost if markup applied
2938 - Update denom burden cost if markup applied ***/
2939
2940 /* Changes done for bug 2668753. In the cursor C_Nl_Bill_Rate_Overrides_Mcb, denom_raw_cost,denom_burdened_cost and denom_currency_code
2941 are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
2942
2943 IF ( l_raw_revenue IS NULL) THEN
2944
2945 DECLARE
2946
2947 CURSOR C_Nl_Bill_Rate_Overrides_Mcb IS
2948 SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
2949 DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
2950 DECODE(o.bill_rate, NULL,
2951 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
2952 * (DECODE(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
2953 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
2954 * p_quantity , o.rate_currency_code)) r_revenue,
2955 DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
2956 o.discount_percentage discount_pct
2957 FROM pa_nl_bill_rate_overrides o
2958 WHERE o.task_id = p_task_id
2959 AND o.expenditure_type = p_expenditure_type
2960 AND o.non_labor_resource = p_non_labor_resource
2961 AND l_called_process <> 1 /*Added for Doosan rate api change */
2962 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
2963 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
2964 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
2965 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
2966
2967 UNION
2968
2969 SELECT DECODE(o.bill_rate, NULL,NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)),
2970 DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
2971 DECODE(o.bill_rate, NULL,
2972 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
2973 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
2974 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
2975 * p_quantity, o.rate_currency_code)),
2976 DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
2977 o.discount_percentage discount_pct
2978 FROM pa_nl_bill_rate_overrides o
2979 WHERE o.task_id = p_task_id
2980 AND o.expenditure_type = p_expenditure_type
2981 AND o.non_labor_resource is NULL
2982 AND l_called_process <> 1 /*Added for Doosan rate api change */
2983 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
2984 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
2985 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
2986 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
2987 AND NOT EXISTS
2988 (SELECT o3.bill_rate
2989 FROM pa_nl_bill_rate_overrides o3
2990 WHERE o3.task_id = p_task_id
2991 AND o3.expenditure_type = p_expenditure_type
2992 AND o3.non_labor_resource = p_non_labor_resource
2993 AND l_called_process <> 1 /*Added for Doosan rate api change */
2994 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
2995 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
2996 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
2997 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
2998 )
2999
3000 UNION
3001
3002 SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
3003 DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
3004 DECODE(o2.bill_rate, NULL,
3005 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3006 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
3007 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3008 * p_quantity, o2.rate_currency_code)),
3009 DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
3010 o2.discount_percentage discount_pct
3011 FROM pa_nl_bill_rate_overrides o2
3012 WHERE o2.project_id = p_project_id
3013 AND o2.expenditure_type = p_expenditure_type
3014 AND o2.non_labor_resource = p_non_labor_resource
3015 AND l_called_process <> 2 /*Added for Doosan rate api change */
3016 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3017 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3018 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3019 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3020 AND NOT EXISTS
3021 (SELECT o3.bill_rate
3022 FROM pa_nl_bill_rate_overrides o3
3023 WHERE o3.task_id = p_task_id
3024 AND o3.expenditure_type = p_expenditure_type
3025 AND l_called_process <> 1 /*Added for Doosan rate api change */
3026 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3027 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3028 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3029 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3030 )
3031
3032 UNION
3033
3034 SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3035 DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
3036 DECODE(o2.bill_rate, NULL,
3037 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3038 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
3039 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3040 * p_quantity, o2.rate_currency_code)) r_revenue,
3041 DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
3042 o2.discount_percentage discount_pct
3043 FROM pa_nl_bill_rate_overrides o2
3044 WHERE o2.project_id = p_project_id
3045 AND o2.expenditure_type = p_expenditure_type
3046 AND o2.non_labor_resource is NULL
3047 AND l_called_process <> 2 /*Added for Doosan rate api change */
3048 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3049 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3050 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3051 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3052 AND NOT EXISTS
3053 (SELECT o3.bill_rate
3054 FROM pa_nl_bill_rate_overrides o3
3055 WHERE o3.task_id = p_task_id
3056 AND o3.expenditure_type = p_expenditure_type
3057 AND l_called_process <> 1 /*Added for Doosan rate api change */
3058 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3059 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3060 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3061 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date))) /* BUG#3118592 */
3062 AND NOT EXISTS
3063 (SELECT o3.bill_rate
3064 FROM pa_nl_bill_rate_overrides o3
3065 WHERE o3.project_id = p_project_id
3066 AND o3.expenditure_type = p_expenditure_type
3067 AND o3.non_labor_resource = p_non_labor_resource
3068 AND l_called_process <> 2 /*Added for Doosan rate api change */
3069 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3070 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3071 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3072 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3073 );
3074
3075
3076
3077 CURSOR C_Nl_Bill_Rate_Overrides IS
3078 SELECT DECODE(o.bill_rate, NULL, NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3079 DECODE(o.markup_percentage, NULL, NULL, o.markup_percentage) b_markup,
3080 DECODE(o.bill_rate,NULL,
3081 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
3082 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3083 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
3084 * p_quantity, o.rate_currency_code)) r_revenue,
3085 DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code,
3086 o.discount_percentage discount_pct
3087 FROM pa_nl_bill_rate_overrides o
3088 WHERE o.task_id = p_task_id
3089 AND o.expenditure_type = p_expenditure_type
3090 AND o.non_labor_resource = p_non_labor_resource
3091 AND l_called_process <> 1 /*Added for Doosan rate api change */
3092 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3093 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3094 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
3095 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3096 UNION
3097 SELECT DECODE(o.bill_rate, NULL,NULL, o.bill_rate * NVL(p_bill_rate_multiplier,1)),
3098 DECODE(o.markup_percentage, NULL, NULL, o.markup_percentage) b_markup,
3099 DECODE(o.bill_rate, NULL,
3100 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
3101 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3102 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
3103 * p_quantity, o.rate_currency_code)),
3104 DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code ,
3105 o.discount_percentage discount_pct
3106 FROM pa_nl_bill_rate_overrides o
3107 WHERE o.task_id = p_task_id
3108 AND o.expenditure_type = p_expenditure_type
3109 AND o.non_labor_resource is NULL
3110 AND l_called_process <> 1 /*Added for Doosan rate api change */
3111 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3112 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3113 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
3114 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3115 AND NOT EXISTS
3116 (SELECT o3.bill_rate
3117 FROM pa_nl_bill_rate_overrides o3
3118 WHERE o3.task_id = p_task_id
3119 AND o3.expenditure_type = p_expenditure_type
3120 AND o3.non_labor_resource = p_non_labor_resource
3121 AND l_called_process <> 1 /*Added for Doosan rate api change */
3122 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3123 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3124 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3125 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3126 )
3127 UNION
3128 SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
3129 DECODE(o2.markup_percentage, NULL, NULL, o2.markup_percentage) b_markup,
3130 DECODE(o2.bill_rate, NULL,
3131 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3132 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3133 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3134 * p_quantity, o2.rate_currency_code)),
3135 DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code ,
3136 o2.discount_percentage discount_pct
3137 FROM pa_nl_bill_rate_overrides o2
3138 WHERE o2.project_id = p_project_id
3139 AND o2.expenditure_type = p_expenditure_type
3140 AND o2.non_labor_resource = p_non_labor_resource
3141 AND l_called_process <> 2 /*Added for Doosan rate api change */
3142 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3143 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3144 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3145 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3146 AND NOT EXISTS
3147 (SELECT o3.bill_rate
3148 FROM pa_nl_bill_rate_overrides o3
3149 WHERE o3.task_id = p_task_id
3150 AND o3.expenditure_type = p_expenditure_type
3151 AND l_called_process <> 1 /*Added for Doosan rate api change */
3152 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3153 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3154 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3155 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3156 )
3157 UNION
3158 SELECT DECODE(o2.bill_rate, NULL,NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
3159 DECODE(o2.markup_percentage, NULL, NULL, o2.markup_percentage) b_markup,
3160 DECODE(o2.bill_rate, NULL,
3161 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3162 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100), p_proj_func_currency),
3163 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3164 * p_quantity, o2.rate_currency_code)),
3165 DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code,
3166 o2.discount_percentage discount_pct
3167 FROM pa_nl_bill_rate_overrides o2
3168 WHERE o2.project_id = p_project_id
3169 AND o2.expenditure_type = p_expenditure_type
3170 AND o2.non_labor_resource is NULL
3171 AND l_called_process <> 2 /*Added for Doosan rate api change */
3172 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3173 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3174 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3175 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3176 AND NOT EXISTS
3177 (SELECT o3.bill_rate
3178 FROM pa_nl_bill_rate_overrides o3
3179 WHERE o3.task_id = p_task_id
3180 AND o3.expenditure_type = p_expenditure_type
3181 AND l_called_process <> 1 /*Added for Doosan rate api change */
3182 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3183 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3184 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3185 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3186 )
3187 AND NOT EXISTS
3188 (SELECT o3.bill_rate
3189 FROM pa_nl_bill_rate_overrides o3
3190 WHERE o3.project_id = p_project_id
3191 AND o3.expenditure_type = p_expenditure_type
3192 AND o3.non_labor_resource = p_non_labor_resource
3193 AND l_called_process <> 2 /*Added for Doosan rate api change */
3194 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3195 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3196 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3197 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3198
3199 );
3200
3201
3202 BEGIN
3203
3204
3205 IF ( p_mcb_flag='Y') THEN /* MCB enabled */
3206
3207 -- Opening cursor and fetching row
3208 FOR Rec_Nl_Bill_Rate_Overrides IN C_Nl_Bill_Rate_Overrides_Mcb LOOP
3209 -- Checking if the cursor is returning more than one row then error out
3210 IF (l_true) THEN
3211 RAISE l_more_than_one_row_excep;
3212 ELSE
3213 l_true := TRUE;
3214 END IF;
3215
3216 -- Assigning the raw revenue to the local variable
3217 l_raw_revenue := Rec_Nl_Bill_Rate_Overrides.r_revenue;
3218
3219 --Assigning the override discount rate to the local variable
3220 l_rate_discount_pct := Rec_Nl_Bill_Rate_Overrides.discount_pct;
3221
3222 x_Rev_curr_code := rec_nl_bill_rate_overrides.rate_currency_code;
3223 /* Added the following out parameters for Doosan rate api changes */
3224
3225 x_bill_rate :=Rec_Nl_Bill_Rate_Overrides.b_rate;
3226 x_markup_percentage :=Rec_Nl_Bill_Rate_Overrides.b_markup;
3227
3228 END LOOP;
3229 ELSE /* IF p_mcb=N*/
3230
3231 -- Opening cursor and fetching row
3232 FOR Rec_Nl_Bill_Rate_Overrides IN C_Nl_Bill_Rate_Overrides LOOP
3233 -- Checking if the cursor is returning more than one row then error out
3234 IF (l_true) THEN
3235 RAISE l_more_than_one_row_excep;
3236 ELSE
3237 l_true := TRUE;
3238 END IF;
3239 -- Assigning the raw revenue to the local variable
3240 l_raw_revenue := Rec_Nl_Bill_Rate_Overrides.r_revenue;
3241
3242 --Assigning the override discount rate to the local variable
3243 l_rate_discount_pct := Rec_Nl_Bill_Rate_Overrides.discount_pct;
3244 x_Rev_curr_code := rec_nl_bill_rate_overrides.rate_currency_code;
3245 /* Added the following out parameters for Doosan rate api changes */
3246
3247 x_bill_rate :=Rec_Nl_Bill_Rate_Overrides.b_rate;
3248 x_markup_percentage :=Rec_Nl_Bill_Rate_Overrides.b_markup;
3249 END LOOP;
3250
3251 END IF;/* end of p_mcb*/
3252
3253 EXCEPTION
3254 WHEN l_more_than_one_row_excep THEN
3255 RAISE;
3256 END;/*End of Item 22 ,pcb_='Y'*/
3257 IF g1_debug_mode = 'Y' THEN
3258 pa_debug.write_file('LOG','1001 Disc. Percent: ' || l_rate_discount_pct || 'Revenue : '
3259 || l_raw_revenue || 'currency_code : ' || x_Rev_curr_code);
3260 END IF;
3261 END IF;
3262
3263
3264
3265 l_true :=false ;
3266 /*--------------------------------------------------------------+
3267 |23. Std non labor resource bill rates schedule |
3268 +--------------------------------------------------------------+
3269 | Set non labor markup bill rate, raw revenue, adjusted |
3270 | rate and adjusted revenue using std non labor resource |
3271 | bill rate schedules. |
3272 | If discounted revenue after markup is less than raw cost, |
3273 | set adjusted revenue equal to raw cost. |
3274 +--------------------------------------------------------------*/
3275 /** Change for Project Manufacturing, For bill markup raw_cost is used.
3276 For System Linkage 'Burdened Transaction' the raw_cost = 0
3277 So in this case we have to substitute raw_cost by burden_cost
3278 **/
3279 /* Changes done for bug 2668753. In the cursor C_Std_Non_Labor_Mcb, denom_raw_cost,denom_burdened_cost and denom_currency_code
3280 are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
3281
3282 IF ( l_raw_revenue IS NULL) THEN
3283 /*added for bug 2690011 .If there is
3284 any performance issue because of
3285 the select statements below
3286 then l_proj_std_bill_rate_sch_id and
3287 l_task_std_bill_rate_sch_id can be passed as
3288 input parameters to these functions*/
3289
3290
3291 /* Commenting out the below select statements as the schedule ids are now passed as input
3292 parameters to the function */
3293 /* SELECT non_lab_std_bill_rt_sch_id
3294 into l_proj_std_bill_rate_sch_id
3295 FROM pa_projects_all
3296 WHERE project_id=p_project_id;
3297
3298 SELECT non_lab_std_bill_rt_sch_id
3299 into l_task_std_bill_rate_sch_id
3300 FROM pa_tasks
3301 WHERE task_id=p_task_id; */
3302
3303 l_proj_std_bill_rate_sch_id := p_proj_nl_std_bill_rate_sch_id;
3304 l_task_std_bill_rate_sch_id := p_task_nl_std_bill_rate_sch_id;
3305
3306 DECLARE
3307 CURSOR C_Std_Non_Labor_Mcb IS
3308 SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3309 DECODE(b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
3310 DECODE(b.rate, NULL,
3311 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
3312 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
3313 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3314 * p_quantity, b.rate_currency_code)) r_revenue,
3315 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
3316 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3317 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100 , b.rate_currency_code)) adjusted_rate,
3318 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,DECODE(b.rate, NULL,
3319 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
3320 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
3321 * ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), l_mcb_currency_code),
3322 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
3323 * NVL(p_bill_rate_multiplier,1)
3324 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))) trans_adjusted_revenue,
3325 DECODE(b.rate, NULL, l_mcb_currency_code, b.rate_currency_code) rate_currency_code,
3326 NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
3327 FROM pa_bill_rates_all b
3328 WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
3329 AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3330 b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3331 AND b.expenditure_type = p_expenditure_type
3332 AND b.non_labor_resource = p_non_labor_resource
3333 AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3334 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3335 BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
3336 AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3337
3338 UNION
3339
3340 SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
3341 DECODE(b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
3342 DECODE(b2.rate, NULL, PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
3343 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
3344 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3345 * p_quantity, b2.rate_currency_code)),
3346 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
3347 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3348 * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
3349 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,DECODE(b2.rate, NULL,
3350 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage) *
3351 (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
3352 * ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100), l_mcb_currency_code),
3353 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
3354 * NVL(p_bill_rate_multiplier,1) * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
3355 DECODE(b2.rate, NULL, l_mcb_currency_code, b2.rate_currency_code) rate_currency_code,
3356 NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
3357 FROM pa_bill_rates_all b2
3358 WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
3359 AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
3360 b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
3361 AND b2.expenditure_type = p_expenditure_type
3362 AND b2.non_labor_resource = p_non_labor_resource
3363 AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3364 AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3365 BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
3366 AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3367 AND NOT EXISTS
3368 (SELECT b3.rate
3369 FROM pa_bill_rates_all b3
3370 WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
3371 AND b3.bill_rate_organization_id =p_task_bill_rate_org_id commented for bug2690011*/
3372 b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3373 AND b3.expenditure_type = p_expenditure_type
3374 AND b3.non_labor_resource = p_non_labor_resource
3375 AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3376 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3377 BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
3378 AND NVL(trunc(b3.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3379 );
3380
3381 CURSOR C_Std_Non_Labor IS
3382 SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3383 DECODE(b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
3384 DECODE(b.rate, NULL,
3385 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
3386 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3387 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3388 * p_quantity, b.rate_currency_code)) r_revenue,
3389 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
3390 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3391 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code)) adjusted_rate,
3392 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,DECODE(b.rate, NULL,
3393 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
3394 (100 + b.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
3395 * ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), p_proj_func_currency ),
3396 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
3397 * NVL(p_bill_rate_multiplier,1)
3398 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))) trans_adjusted_revenue,
3399 DECODE(b.rate, NULL, p_proj_func_currency, b.rate_currency_code) rate_currency_code,
3400 NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
3401 FROM pa_bill_rates_all b
3402 WHERE/* b.std_bill_rate_schedule = p_task_std_bill_rate_sch
3403 AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3404 b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3405 AND b.expenditure_type = p_expenditure_type
3406 AND b.non_labor_resource = p_non_labor_resource
3407 AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3408 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3409 BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
3410 AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3411
3412 UNION
3413
3414 SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
3415 DECODE(b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
3416 DECODE(b2.rate, NULL,
3417 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
3418 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),
3419 p_proj_func_currency),
3420 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3421 * p_quantity, b2.rate_currency_code)),
3422 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
3423 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3424 * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
3425 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,DECODE(b2.rate, NULL,
3426 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
3427 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
3428 * ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100)
3429 ,p_proj_func_currency),
3430 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
3431 * NVL(p_bill_rate_multiplier,1)
3432 * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
3433 DECODE(b2.rate, NULL, p_proj_func_currency, b2.rate_currency_code) rate_currency_code,
3434 NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
3435 FROM pa_bill_rates_all b2
3436 WHERE/* b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
3437 AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
3438 b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
3439 AND b2.expenditure_type = p_expenditure_type
3440 AND b2.non_labor_resource = p_non_labor_resource
3441 AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3442 AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3443 BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
3444 AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3445 AND NOT EXISTS
3446 (SELECT b3.rate
3447 FROM pa_bill_rates_all b3
3448 WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
3449 AND b3.bill_rate_organization_id =p_task_bill_rate_org_id commented for bug2690011*/
3450 b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3451 AND b3.expenditure_type = p_expenditure_type
3452 AND b3.non_labor_resource = p_non_labor_resource
3453 AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3454 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3455 BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
3456 AND NVL(trunc(b3.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3457 );
3458
3459 BEGIN
3460 IF ( p_mcb_flag='Y') THEN /* MCB enabled */
3461 -- Opening cursor and fetching row
3462 FOR Rec_Std_Non_Labor IN C_Std_Non_Labor_Mcb LOOP
3463 -- Checking if the cursor is returning more than one row then error out
3464 IF (l_true) THEN
3465 RAISE l_more_than_one_row_excep;
3466 ELSE
3467 l_true := TRUE;
3468 END IF;
3469
3470 -- Assigning the raw revenue to the local variable
3471 l_raw_revenue := Rec_Std_Non_Labor.r_revenue;
3472
3473 -- Assigning the trans adjusted amount to local varaible
3474 l_trans_adjust_amount := Rec_Std_Non_Labor.trans_adjusted_revenue;
3475 x_rev_curr_code := Rec_std_non_Labor.rate_currency_code;
3476
3477 /* Added the following out parameters for Doosan rate api changes */
3478
3479 x_bill_rate :=Rec_Std_Non_Labor.b_rate;
3480 x_adjusted_bill_rate :=Rec_Std_Non_Labor.adjusted_rate; --4038485
3481 x_markup_percentage :=Rec_Std_Non_Labor.b_markup;
3482
3483 END LOOP;
3484 ELSE /* IF p_mcb=N*/
3485 -- Opening cursor and fetching row
3486 FOR Rec_Std_Non_Labor IN C_Std_Non_Labor LOOP
3487 -- Checking if the cursor is returning more than one row then error out
3488 IF (l_true) THEN
3489 RAISE l_more_than_one_row_excep;
3490 ELSE
3491 l_true := TRUE;
3492 END IF;
3493
3494 -- Assigning the raw revenue to the local variable
3495 l_raw_revenue := Rec_Std_Non_Labor.r_revenue;
3496
3497 -- Assigning the trans adjusted amount to local varaible
3498 l_trans_adjust_amount := Rec_Std_Non_Labor.trans_adjusted_revenue;
3499 x_rev_curr_code := Rec_std_non_Labor.rate_currency_code;
3500
3501 /* Added the following out parameters for Doosan rate api changes */
3502
3503 x_bill_rate :=Rec_Std_Non_Labor.b_rate;
3504 x_adjusted_bill_rate :=Rec_Std_Non_Labor.adjusted_rate; --4038485
3505 x_markup_percentage :=Rec_Std_Non_Labor.b_markup;
3506
3507 END LOOP;
3508 END IF;/* end of p_mcb*/
3509 EXCEPTION
3510 WHEN l_more_than_one_row_excep THEN
3511 RAISE;
3512 END;/*End of Item 23 ,pcb_='Y'*/
3513
3514 IF g1_debug_mode = 'Y' THEN
3515 pa_debug.write_file('LOG','1002 Disc. Percent: ' || l_rate_discount_pct || 'Revenue : '
3516 || l_raw_revenue || 'currency_code : ' || x_Rev_curr_code);
3517 END IF;
3518 END IF;
3519
3520
3521 l_true :=false ;
3522
3523 /*--------------------------------------------------------------+
3524 |24. Non non_labor expenditure type bill rate overrides |
3525 +--------------------------------------------------------------+
3526 | Set bill rate and raw revenue using non non_labor expenditure |
3527 | type bill rate or markup overrides. |
3528 +--------------------------------------------------------------*/
3529
3530 /** Change for Project Manufacturing, For bill markup raw_cost is used.
3531 For System Linkage 'Burdened Transaction' the raw_cost = 0
3532 So in this case we have to substitute raw_cost by burden_cost
3533 **/
3534
3535
3536 /*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
3537 other audit columns.
3538 - Amount calculation code = 'O' for Overrides
3539 - Bill Transaction Currency code is from overrides table.
3540 - Change column from raw_revenue to bill_trans_raw_revnue
3541 (Bill rate and Raw revenue should update only in Bill transaction currency)
3542 - Change the WHERE clause from raw_revenue IS NULL to
3543 bill_trans_raw_revenue IS NULL
3544 - Update denom raw cost if markup applied
3545 - Update denom burden cost if markup applied ***/
3546 /* Changes done for bug 2668753. In the cursor C_Exp_Type_Overrides_Ncb , denom_raw_cost,denom_burdened_cost and denom_currency_code are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
3547
3548 IF ( l_raw_revenue IS NULL and l_rate_discount_pct is null) THEN
3549
3550 DECLARE
3551 CURSOR C_Exp_Type_Overrides_Ncb IS
3552 SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3553 DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
3554 DECODE(o.bill_rate, NULL,
3555 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
3556 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
3557 l_mcb_currency_code),
3558 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
3559 * p_quantity, o.rate_currency_code)) r_revenue,
3560 DECODE(o.bill_rate, NULL, l_mcb_currency_code, o.rate_currency_code) rate_currency_code,
3561 o.discount_percentage discount_pct
3562 FROM pa_nl_bill_rate_overrides o
3563 WHERE o.task_id = p_task_id
3564 AND o.expenditure_type = p_expenditure_type
3565 AND o.non_labor_resource IS NULL
3566 AND l_called_process <> 1 /*Added for Doosan rate api change */
3567 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3568 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3569 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
3570 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3571
3572 UNION
3573
3574 SELECT DECODE(o2.bill_rate, NULL, NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
3575 DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
3576 DECODE(o2.bill_rate, NULL,
3577 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3578 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
3579 l_mcb_currency_code),
3580 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3581 * p_quantity, o2.rate_currency_code)),
3582 DECODE(o2.bill_rate, NULL, l_mcb_currency_code, o2.rate_currency_code) rate_currency_code,
3583 o2.discount_percentage
3584 FROM pa_nl_bill_rate_overrides o2
3585 WHERE o2.project_id = p_project_id
3586 AND o2.expenditure_type = p_expenditure_type
3587 AND o2.non_labor_resource IS NULL
3588 AND l_called_process <> 2 /*Added for Doosan rate api change */
3589 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3590 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3591 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3592 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3593 AND NOT EXISTS
3594 (SELECT o3.bill_rate
3595 FROM pa_nl_bill_rate_overrides o3
3596 WHERE o3.task_id = p_task_id
3597 AND o3.expenditure_type = p_expenditure_type
3598 AND o3.non_labor_resource IS NULL
3599 AND l_called_process <> 1 /*Added for Doosan rate api change */
3600 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3601 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3602 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3603 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3604 );
3605
3606
3607 CURSOR C_Exp_Type_Overrides IS
3608 SELECT DECODE(o.bill_rate, NULL, NULL,o.bill_rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3609 DECODE(o.markup_percentage, NULL, NULL,o.markup_percentage ) b_markup,
3610 DECODE(o.bill_rate, NULL,
3611 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o.markup_percentage)
3612 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3613 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o.bill_rate * NVL(p_bill_rate_multiplier,1)
3614 * p_quantity, o.rate_currency_code)) r_revenue,
3615 DECODE(o.bill_rate, NULL, p_proj_func_currency, o.rate_currency_code) rate_currency_code,
3616 o.discount_percentage discount_pct
3617 FROM pa_nl_bill_rate_overrides o
3618 WHERE o.task_id = p_task_id
3619 AND o.expenditure_type = p_expenditure_type
3620 AND o.non_labor_resource IS NULL
3621 AND l_called_process <> 1 /*Added for Doosan rate api change */
3622 AND (o.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3623 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3624 BETWEEN trunc(o.start_date_active) /* BUG#3118592 */
3625 AND trunc(NVL(o.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3626
3627 UNION
3628
3629 SELECT DECODE(o2.bill_rate, NULL, NULL,o2.bill_rate * NVL(p_bill_rate_multiplier,1)),
3630 DECODE(o2.markup_percentage, NULL, NULL,o2.markup_percentage ) b_markup,
3631 DECODE(o2.bill_rate, NULL,
3632 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + o2.markup_percentage)
3633 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3634 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(o2.bill_rate * NVL(p_bill_rate_multiplier,1)
3635 * p_quantity, o2.rate_currency_code)),
3636 DECODE(o2.bill_rate, NULL, p_proj_func_currency, o2.rate_currency_code) rate_currency_code,
3637 o2.discount_percentage
3638 FROM pa_nl_bill_rate_overrides o2
3639 WHERE o2.project_id = p_project_id
3640 AND o2.expenditure_type = p_expenditure_type
3641 AND o2.non_labor_resource IS NULL
3642 AND l_called_process <> 2 /*Added for Doosan rate api change */
3643 AND (o2.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3644 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3645 BETWEEN trunc(o2.start_date_active) /* BUG#3118592 */
3646 AND trunc(NVL(o2.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3647 AND NOT EXISTS
3648 (SELECT o3.bill_rate
3649 FROM pa_nl_bill_rate_overrides o3
3650 WHERE o3.task_id = p_task_id
3651 AND o3.expenditure_type = p_expenditure_type
3652 AND o3.non_labor_resource IS NULL
3653 AND l_called_process <> 1 /*Added for Doosan rate api change */
3654 AND (o3.bill_rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3655 AND trunc(p_expenditure_item_date) /* BUG#3118592 */
3656 BETWEEN trunc(o3.start_date_active) /* BUG#3118592 */
3657 AND trunc(NVL(o3.end_date_active,p_expenditure_item_date)) /* BUG#3118592 */
3658 );
3659
3660 BEGIN
3661
3662 IF ( p_mcb_flag='Y') THEN /* MCB enabled */
3663
3664 -- Opening cursor and fetching row
3665 FOR Rec_Exp_Type_Overrides IN C_Exp_Type_Overrides_Ncb LOOP
3666 -- Checking if the cursor is returning more than one row then error out
3667 IF (l_true) THEN
3668 RAISE l_more_than_one_row_excep;
3669 ELSE
3670 l_true := TRUE;
3671 END IF;
3672
3673 -- Assigning the raw revenue to the local variable
3674 l_raw_revenue := Rec_Exp_Type_Overrides.r_revenue;
3675
3676 -- Assigning the Override discount percentage to the local variable
3677 l_rate_discount_pct := Rec_Exp_Type_Overrides.discount_pct;
3678 x_rev_curr_code := Rec_exp_type_overrides.rate_currency_code;
3679
3680 /* Added the following out parameters for Doosan rate api changes */
3681
3682 x_bill_rate := Rec_Exp_Type_Overrides.b_rate;
3683 x_markup_percentage := Rec_Exp_Type_Overrides.b_markup;
3684
3685
3686 END LOOP;
3687 ELSE /* IF p_mcb=N*/
3688
3689 -- Opening cursor and fetching row
3690 FOR Rec_Exp_Type_Overrides IN C_Exp_Type_Overrides LOOP
3691 -- Checking if the cursor is returning more than one row then error out
3692 IF (l_true) THEN
3693 RAISE l_more_than_one_row_excep;
3694 ELSE
3695 l_true := TRUE;
3696 END IF;
3697
3698 -- Assigning the raw revenue to the local variable
3699 l_raw_revenue := Rec_Exp_Type_Overrides.r_revenue;
3700 x_rev_curr_code := Rec_exp_type_overrides.rate_currency_code;
3701
3702 /* Added the following out parameters for Doosan rate api changes */
3703
3704 x_bill_rate := Rec_Exp_Type_Overrides.b_rate;
3705 x_markup_percentage := Rec_Exp_Type_Overrides.b_markup;
3706
3707
3708 END LOOP;
3709
3710 END IF;/* end of p_mcb*/
3711 IF g1_debug_mode = 'Y' THEN
3712 pa_debug.write_file('LOG','1002 Disc. Percent: ' || l_rate_discount_pct || 'Revenue : '
3713 || l_raw_revenue || 'currency_code : ' || x_Rev_curr_code);
3714 END IF;
3715
3716
3717 EXCEPTION
3718 WHEN l_more_than_one_row_excep THEN
3719 RAISE;
3720 END;/*End of Item 24 ,pcb_='Y'*/
3721 END IF;
3722
3723
3724 /*--------------------------------------------------------------+
3725 |25. Std non non_labor expenditure type bill rates schedule |
3726 +--------------------------------------------------------------+
3727 | Set non non_labor markup bill rate, raw revenue, adjusted |
3728 | rate and adjusted revenue using std non non_labor expenditure |
3729 | type bill rates schedules. |
3730 | If discounted revenue after markup is less than raw cost, |
3731 | set adjusted revenue equal to raw cost. |
3732 +--------------------------------------------------------------*/
3733
3734 /*** MCB Changes : Update the bill transaction bill rate, bill transaction raw revenue and
3735 other audit columns.
3736 - Amount calculation code = 'B' for Bill Rates.
3737 - Bill Transaction Currency code is from overrides table.
3738 - Change column from raw_revenue to bill_trans_raw_revnue
3739 (Bill rate and Raw revenue should update only in Bill transaction currency)
3740 - Change the WHERE clause from raw_revenue IS NULL to
3741 bill_trans_raw_revenue IS NULL
3742 - Update denom raw cost if markup applied
3743 - Update denom burden cost if markup applied ***/
3744
3745
3746
3747 l_true :=false ;
3748 /* Changes done for bug 2668753.In the cursor C_Std_Exp_Type_Sch_Ncb ,denom_raw_cost,denom_burdened_cost and denom_currency_code
3749 are changed to l_mcb_raw_cost ,l_mcb_burdened_cost and l_mcb_currency_code */
3750
3751 /* changes done for bug 4169912, in the cursor C_Std_Exp_Type_Sch_Ncb to change the p_task_sch_discount to
3752 NVL(l_rate_discount_pct,p_task_sch_discount) so that it'll be taken into consideration
3753 while calculating adjusted rate if any override discount Percentage is there */
3754 IF ( l_raw_revenue IS NULL ) THEN
3755
3756
3757 DECLARE
3758 CURSOR C_Std_Exp_Type_Sch_Ncb IS
3759 SELECT DECODE (b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3760 DECODE (b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
3761 DECODE (b.rate, NULL,
3762 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
3763 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),
3764 l_mcb_currency_code) ,
3765 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3766 * p_quantity, b.rate_currency_code)) r_revenue,
3767 DECODE (NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
3768 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3769 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code) ) adjusted_rate ,
3770 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
3771 DECODE(b.rate, NULL,
3772 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
3773 (100 + b.markup_percentage) * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
3774 * ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), l_mcb_currency_code),
3775 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
3776 * NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100, b.rate_currency_code))
3777 ) trans_adjusted_amount,
3778 DECODE(b.rate, NULL, l_mcb_currency_code, b.rate_currency_code) rate_currency_code ,
3779 NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
3780 FROM pa_bill_rates_all b
3781 WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
3782 AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3783 b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3784 AND b.expenditure_type = p_expenditure_type
3785 AND b.non_labor_resource IS NULL
3786 AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3787 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3788 BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
3789 AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3790 )
3791 UNION
3792
3793 SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3794 DECODE (b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
3795 DECODE(b2.rate, NULL,
3796 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
3797 * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100),l_mcb_currency_code),
3798 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3799 * p_quantity, b2.rate_currency_code)) r_revenue,
3800 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
3801 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3802 * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)) adjusted_rate,
3803 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
3804 DECODE(b2.rate, NULL,
3805 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
3806 (100 + b2.markup_percentage) * (decode(p_sl_function,6,l_mcb_burdened_cost,l_mcb_raw_cost) / 100)
3807 * ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100),l_mcb_currency_code),
3808 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
3809 * NVL(p_bill_rate_multiplier,1) * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100,
3810 b2.rate_currency_code))) trans_adjusted_amount,
3811 DECODE(b2.rate, NULL, l_mcb_currency_code, b2.rate_currency_code) rate_currency_code,
3812 NVL(l_rate_discount_pct,p_project_sch_discount) discount_pct
3813 FROM pa_bill_rates_all b2
3814 WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
3815 AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
3816 b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id/*added for bug2690011*/
3817 AND b2.expenditure_type = p_expenditure_type
3818 AND b2.non_labor_resource IS NULL
3819 AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3820 AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3821 BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
3822 AND NVL(trunc(b2.end_date_active), trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3823 AND NOT EXISTS
3824 (SELECT b3.rate
3825 FROM pa_bill_rates_all b3
3826 WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
3827 AND b3.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3828 b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3829 AND b3.expenditure_type = p_expenditure_type
3830 AND b3.non_labor_resource IS NULL
3831 AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3832 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3833 BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
3834 AND NVL(trunc(b3.end_date_active), /* BUG#3118592 */
3835 trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3836 );
3837
3838
3839 CURSOR C_Std_Exp_Type_Sch IS
3840 SELECT DECODE(b.rate, NULL,NULL, b.rate * NVL(p_bill_rate_multiplier,1)) b_rate,
3841 DECODE (b.markup_percentage, NULL,NULL, b.markup_percentage ) b_markup,
3842 DECODE(b.rate, NULL,
3843 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b.markup_percentage)
3844 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3845 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3846 * p_quantity, b.rate_currency_code)) r_revenue,
3847 DECODE(NVL(l_rate_discount_pct,p_task_sch_discount), NULL, NULL,
3848 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b.rate * NVL(p_bill_rate_multiplier,1)
3849 * (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) /100, b.rate_currency_code)) adjusted_rate,
3850 DECODE (NVL(l_rate_discount_pct,p_task_sch_discount), NULL,NULL,
3851 DECODE(b.rate, NULL,
3852 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
3853 (100 + b.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
3854 * ((100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100), p_proj_func_currency),
3855 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b.rate * p_quantity)
3856 * NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_task_sch_discount)) / 100,
3857 b.rate_currency_code))) trans_adjusted_amount,
3858 DECODE(b.rate, NULL, p_proj_func_currency, b.rate_currency_code) rate_currency_code ,
3859 NVL(l_rate_discount_pct,p_task_sch_discount) discount_pct
3860 FROM pa_bill_rates_all b
3861 WHERE /*b.std_bill_rate_schedule = p_task_std_bill_rate_sch
3862 AND b.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3863 b.bill_rate_sch_id=l_task_std_bill_rate_sch_id/*added for bug2690011*/
3864 AND b.expenditure_type = p_expenditure_type
3865 AND b.non_labor_resource IS NULL
3866 AND (b.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3867 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3868 BETWEEN trunc(b.start_date_active) /* BUG#3118592 */
3869 AND NVL(trunc(b.end_date_active),trunc(NVL(p_task_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3870
3871 UNION
3872
3873 SELECT DECODE(b2.rate, NULL, NULL,b2.rate * NVL(p_bill_rate_multiplier,1)),
3874 DECODE (b2.markup_percentage, NULL,NULL, b2.markup_percentage ) b_markup,
3875 DECODE(b2.rate, NULL,
3876 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((100 + b2.markup_percentage)
3877 * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100),p_proj_func_currency),
3878 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3879 * p_quantity, b2.rate_currency_code)),
3880 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL, NULL,
3881 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(b2.rate * NVL(p_bill_rate_multiplier,1)
3882 * (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) /100, b2.rate_currency_code)),
3883 DECODE(NVL(l_rate_discount_pct,p_project_sch_discount), NULL,NULL,
3884 DECODE(b2.rate, NULL,
3885 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT(
3886 (100 + b2.markup_percentage) * (decode(p_sl_function,6,p_burden_cost,p_raw_cost) / 100)
3887 * ((100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100), p_proj_func_currency),
3888 PA_MULTI_CURRENCY_BILLING.ROUND_TRANS_CURRENCY_AMT((b2.rate * p_quantity)
3889 * NVL(p_bill_rate_multiplier,1)* (100 - NVL(l_rate_discount_pct,p_project_sch_discount)) / 100, b2.rate_currency_code))),
3890 DECODE(b2.rate, NULL, p_proj_func_currency, b2.rate_currency_code) rate_currency_code,
3891 NVL(l_rate_discount_pct,p_project_sch_discount)
3892 FROM pa_bill_rates_all b2
3893 WHERE /*b2.std_bill_rate_schedule = p_project_std_bill_rate_sch
3894 AND b2.bill_rate_organization_id = p_project_bill_rate_org_id commented for bug2690011*/
3895 b2.bill_rate_sch_id=l_proj_std_bill_rate_sch_id /*added for bug2690011*/
3896 AND b2.expenditure_type = p_expenditure_type
3897 AND b2.non_labor_resource IS NULL
3898 AND (b2.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3899 AND trunc(NVL(p_project_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3900 BETWEEN trunc(b2.start_date_active) /* BUG#3118592 */
3901 AND NVL(trunc(b2.end_date_active),trunc(NVL(p_project_sch_date,p_expenditure_item_date))) /* BUG#3118592 */
3902 AND NOT EXISTS
3903 (SELECT b3.rate
3904 FROM pa_bill_rates_all b3
3905 WHERE /*b3.std_bill_rate_schedule = p_task_std_bill_rate_sch
3906 AND b3.bill_rate_organization_id = p_task_bill_rate_org_id commented for bug2690011*/
3907 b3.bill_rate_sch_id=l_task_std_bill_rate_sch_id /*added for bug2690011*/
3908 AND b3.expenditure_type = p_expenditure_type
3909 AND b3.non_labor_resource IS NULL
3910 AND (b3.rate IS NULL OR p_uom_flag =1 ) /*Added for UOM enhancement */
3911 AND trunc(NVL(p_task_sch_date,p_expenditure_item_date)) /* BUG#3118592 */
3912 BETWEEN trunc(b3.start_date_active) /* BUG#3118592 */
3913 AND NVL(trunc(b3.end_date_active), trunc(NVL(p_task_sch_date, /* BUG#3118592 */
3914 p_expenditure_item_date)))
3915 );
3916
3917 BEGIN
3918
3919 IF ( p_mcb_flag='Y') THEN /* MCB enabled */
3920
3921 -- Opening cursor and fetching row
3922 FOR Rec_Std_Exp_Type_Sch IN C_Std_Exp_Type_Sch_Ncb LOOP
3923 -- Checking if the cursor is returning more than one row then error out
3924 IF (l_true) THEN
3925 RAISE l_more_than_one_row_excep;
3926 ELSE
3927 l_true := TRUE;
3928 END IF;
3929
3930 -- Assigning the raw revenue to the local variable
3931 l_raw_revenue := Rec_Std_Exp_Type_Sch.r_revenue;
3932
3933 -- Assigning the bill rate to the local variable
3934 l_trans_adjust_amount := Rec_Std_Exp_Type_Sch.trans_adjusted_amount;
3935 x_rev_curr_code := Rec_Std_Exp_type_sch.rate_currency_code;
3936
3937 /* Added the following out parameters for Doosan rate api changes */
3938
3939 x_bill_rate := Rec_Std_Exp_Type_Sch.b_rate;
3940 x_adjusted_bill_rate :=Rec_Std_Exp_Type_Sch.adjusted_rate; --4038485
3941 x_markup_percentage := Rec_Std_Exp_Type_Sch.b_markup;
3942
3943
3944
3945 END LOOP;
3946 ELSE /* IF p_mcb=N*/
3947
3948 -- Opening cursor and fetching row
3949 FOR Rec_Exp_Type_Sch IN C_Std_Exp_Type_Sch LOOP
3950 -- Checking if the cursor is returning more than one row then error out
3951 IF (l_true) THEN
3952 RAISE l_more_than_one_row_excep;
3953 ELSE
3954 l_true := TRUE;
3955 END IF;
3956
3957 -- Assigning the raw revenue to the local variable
3958 l_raw_revenue := Rec_Exp_Type_Sch.r_revenue;
3959
3960 -- Assigning the bill rate to the local variable
3961 l_trans_adjust_amount := Rec_Exp_Type_Sch.trans_adjusted_amount;
3962 x_rev_curr_code := Rec_Exp_Type_Sch.rate_currency_code;
3963
3964 /* Added the following out parameters for Doosan rate api changes */
3965
3966 x_bill_rate := Rec_Exp_Type_Sch.b_rate;
3967 x_adjusted_bill_rate :=Rec_Exp_Type_Sch.adjusted_rate; --4038485
3968 x_markup_percentage := Rec_Exp_Type_Sch.b_markup;
3969
3970 END LOOP;
3971 END IF;/* end of p_mcb*/
3972
3973 EXCEPTION
3974 WHEN l_more_than_one_row_excep THEN
3975 RAISE;
3976 END;/*End of Item 25 ,pcb_='Y'*/
3977
3978 IF g1_debug_mode = 'Y' THEN
3979 pa_debug.write_file('LOG','1004 Disc. Percent: ' || l_rate_discount_pct ||
3980 'Revenue : ' || l_raw_revenue || 'currency_code : ' || x_Rev_curr_code);
3981 END IF;
3982
3983 END IF;
3984 END IF ;/*End of scheduled type check*/
3985
3986
3987 IF (l_trans_adjust_amount IS NOT NULL ) THEN
3988 l_txn_raw_revenue := l_trans_adjust_amount;
3989 ELSE
3990 l_txn_raw_revenue := l_raw_revenue;
3991 END IF;
3992
3993 IF ( l_txn_raw_revenue IS NULL) THEN
3994 RAISE l_no_revenue;
3995 END IF;
3996
3997 x_raw_revenue := l_txn_raw_revenue ;
3998 x_return_status := l_x_return_status;
3999
4000 IF g1_debug_mode = 'Y' THEN
4001 pa_debug.write_file('LOG','9999 Disc. Percent: ' || l_rate_discount_pct ||
4002 'Revenue : ' || l_raw_revenue || 'currency_code : ' || x_Rev_curr_code);
4003 END IF;
4004
4005 EXCEPTION
4006
4007 WHEN l_no_revenue THEN
4008 x_raw_revenue:= NULL;
4009 /* Added the following out parameters for Doosan rate api changes */
4010
4011 x_bill_rate :=null;
4012 x_markup_percentage :=null;
4013 x_adjusted_bill_rate :=null; --4038485
4014
4015 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
4016 PA_UTILS.add_message('PA', 'PA_FCST_NO_BILL_RATE');
4017 END IF;
4018 x_return_status := FND_API.G_RET_STS_ERROR;
4019 x_msg_count := 1;
4020 IF g1_debug_mode = 'Y' THEN
4021 pa_debug.write_file('LOG','1.SQLERROR ' || SQLCODE);
4022 END IF;
4023
4024 WHEN OTHERS THEN
4025 x_raw_revenue:= NULL;
4026 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4027 /* Added the following out parameters for Doosan rate api changes */
4028
4029 x_bill_rate :=null;
4030 x_markup_percentage :=null;
4031 x_adjusted_bill_rate :=null; --4038485
4032
4033 px_exp_func_curr_code := lx_exp_func_curr_code;
4034 x_raw_revenue := null;
4035 x_rev_Curr_code := null;
4036
4037 x_msg_count := 1;
4038 x_msg_data := SUBSTR(SQLERRM,1,30);
4039 IF (NVL(PA_RATE_PVT_PKG.G_add_error_to_stack_flag,'Y') = 'Y') THEN
4040 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_REVENUE', /* name of the package*/
4041 p_procedure_name => 'Non_labor_Assignment');
4042 IF g1_debug_mode = 'Y' THEN
4043 pa_debug.write_file('LOG','2.SQLERROR ' || SQLCODE);
4044 END IF;
4045 RAISE;
4046 END IF;
4047
4048 END Non_Labor_Rev_amount;
4049
4050 END PA_REVENUE;
4051
[Home] [Help]