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