[Home] [Help]
PACKAGE BODY: APPS.PA_MC_BILLING_PVT
Source
1 PACKAGE BODY pa_mc_billing_pvt AS
2 /* $Header: PAMCPVTB.pls 120.5 2005/10/04 17:39:19 skannoji noship $ */
3
4
5 PROCEDURE get_budget_amount(
6 p_project_id IN NUMBER,
7 p_task_id IN NUMBER,
8 p_psob_id IN NUMBER,
9 p_rsob_id IN NUMBER,
10 p_billing_extension_id IN NUMBER,
11 p_cost_budget_type_code IN VARCHAR2 DEFAULT NULL,
12 p_rev_budget_type_code IN VARCHAR2 DEFAULT NULL,
13 x_revenue_amount OUT NOCOPY NUMBER,
14 x_cost_amount OUT NOCOPY NUMBER,
15 x_cost_budget_type_code OUT NOCOPY VARCHAR2,
16 x_rev_budget_type_code OUT NOCOPY VARCHAR2,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2)
20 IS
21
22
23 l_cost_budget_type_code VARCHAR2(30) ;
24 l_rev_budget_type_code VARCHAR2(30) ;
25
26 l_check_code VARCHAR2(1) ;
27
28 l_cost_budget_version_id NUMBER ;
29 l_rev_budget_version_id NUMBER ;
30 l_task_id NUMBER ;
31 l_raw_cost_total NUMBER ;
32 l_burdened_cost_total NUMBER ;
33 l_revenue_total NUMBER ;
34
35 invalid_cost_budget_code EXCEPTION ;
36 invalid_rev_budget_code EXCEPTION ;
37 rev_budget_not_baselined EXCEPTION ;
38 cost_budget_not_baselined EXCEPTION ;
39
40
41 l_return_status VARCHAR2(30);
42 l_msg_count NUMBER ;
43 l_msg_data VARCHAR2(240);
44
45
46 BEGIN
47
48
49 /* -----------------------------------------------------------
50 Initialize the Output Variables
51 ----------------------------------------------------------- */
52
53 l_return_status := FND_API.G_RET_STS_SUCCESS;
54 l_msg_count := 0;
55
56 l_raw_cost_total := NULL;
57 l_burdened_cost_total := NULL;
58 l_revenue_total := NULL;
59
60
61
62
63 /* ---------------------------------------------------------
64 Assigning the Input to the local variables
65 ---------------------------------------------------------
66
67 l_task_id := p_task_id;
68
69
70
71 /* ----------------------------------------------------------------------
72 Get the Cost and Revenue Budget Type code from pa_billing_extensions
73 ---------------------------------------------------------------------- */
74
75 l_cost_budget_type_code := P_cost_budget_type_code ;
76 l_rev_budget_type_code := P_rev_budget_type_code ;
77
78
79 IF (P_cost_budget_type_code IS NULL OR P_rev_budget_type_code IS NULL) THEN
80
81 SELECT decode(P_cost_budget_type_code,NULL,default_cost_budget_type_code, P_cost_budget_type_code),
82 decode(P_rev_budget_type_code,NULL,default_rev_budget_type_code,
83 P_rev_budget_type_code)
84 INTO l_cost_budget_type_code,
85 l_rev_budget_type_code
86 FROM pa_billing_extensions
87 WHERE billing_extension_id= p_billing_Extension_Id;
88
89 END IF;
90
91
92
93 /* -----------------------------------------------------------------------
94 Checking for the Cost budget type code is a valid and the correct amount
95 code, If it is not a valid code then Raise the exception
96 ----------------------------------------------------------------------- */
97
98
99 BEGIN
100
101 SELECT 'x'
102 INTO l_check_code
103 FROM pa_budget_types
104 WHERE budget_type_code = l_cost_budget_type_code
105 AND budget_amount_code = 'C';
106
107 EXCEPTION
108 WHEN NO_DATA_FOUND THEN
109 RAISE invalid_cost_budget_code;
110 END;
111
112
113
114
115 /* --------------------------------------------------------------------------
116 Checking for the Revenue budget type code is a valid and the correct amount
117 code, If it is not a valid code then Raise the exception
118 -------------------------------------------------------------------------- */
119
120
121 BEGIN
122
123 SELECT 'x'
124 INTO l_check_code
125 FROM pa_budget_types
126 WHERE budget_type_code = l_rev_budget_type_code
127 AND budget_amount_code = 'R';
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND THEN
131 RAISE invalid_rev_budget_code;
132 END ;
133
134
135
136
137
138 /* ----------------------------------------------------------------
139 Get the budget version id for cost budget
140 ---------------------------------------------------------------- */
141
142 BEGIN
143
144 SELECT budget_version_id
145 INTO l_cost_budget_version_id
146 FROM pa_budget_versions pbv
147 WHERE project_id = p_project_id
148 AND budget_type_code = l_cost_budget_type_code
149 AND budget_status_code = 'B'
150 AND current_flag = 'Y';
151
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 RAISE cost_budget_not_baselined;
155
156 END;
157
158
159 /* ----------------------------------------------------------------
160 Get the budget version id for revenue budget
161 ---------------------------------------------------------------- */
162
163
164 BEGIN
165
166 SELECT budget_version_id
167 INTO l_rev_budget_version_id
168 FROM pa_budget_versions pbv
169 WHERE project_id = p_project_id
170 AND budget_type_code = l_rev_budget_type_code
171 AND budget_status_code = 'B'
172 AND current_flag = 'Y';
173
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 RAISE rev_budget_not_baselined;
177 END;
178
179
180
181 /* -------------------------------------------------------------------------
182 Calling the API to get the cost budget amount in reporting currency
183 ------------------------------------------------------------------------- */
184
185
186 pa_mc_billing_pvt.get_project_task_budget_amount
187 (p_budget_version_id => l_cost_budget_version_id ,
188 p_project_id => p_project_id ,
189 p_task_id => p_task_id ,
190 p_psob_id => p_psob_id ,
191 p_rsob_id => p_rsob_id ,
192 x_raw_cost_total => l_raw_cost_total ,
193 x_burdened_cost_total => l_burdened_cost_total ,
194 x_revenue_total => l_revenue_total ,
195 x_return_status => l_return_status ,
196 x_msg_count => l_msg_count ,
197 x_msg_data => l_msg_data );
198
199
200 /* ------------------------------------------------------------
201 Copy the value of cost budget amount to the OUTPUT valiable
202 ------------------------------------------------------------ */
203
204
205 x_cost_amount := pa_currency.round_currency_amt(l_burdened_cost_total);
206
207
208
209
210 /* -------------------------------------------------------------------------
211 Calling the API to get the Revenue budget amount in reporting currency
212 ------------------------------------------------------------------------- */
213
214
215 pa_mc_billing_pvt.get_project_task_budget_amount
216 (p_budget_version_id => l_rev_budget_version_id ,
217 p_project_id => p_project_id ,
218 p_task_id => p_task_id ,
219 p_psob_id => p_psob_id ,
220 p_rsob_id => p_rsob_id ,
221 x_raw_cost_total => l_raw_cost_total ,
222 x_burdened_cost_total => l_burdened_cost_total ,
223 x_revenue_total => l_revenue_total ,
224 x_return_status => l_return_status ,
225 x_msg_count => l_msg_count ,
226 x_msg_data => l_msg_data );
227
228
229
230
231 /* ----------------------------------------------------------------
232 Copy the value of revenue budget amount and budget type code into
233 the OUTPUT variables.
234 --------------------------------------------------------------- */
235
236 x_revenue_amount := pa_currency.round_currency_amt(l_revenue_total);
237 x_cost_budget_type_code := l_cost_budget_type_code;
238 x_rev_budget_type_code := l_rev_budget_type_code;
239 x_return_status := l_return_status;
240 x_msg_count := l_msg_count;
241
242
243
244 EXCEPTION
245
246 WHEN invalid_cost_budget_code THEN
247 x_return_status := FND_API.G_RET_STS_ERROR;
248 x_msg_count := 1;
249 x_msg_data := 'INVALID_COST_BUDGET_TYPE';
250 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MC_BILLING_PVT',
251 p_procedure_name => 'get_budget_amount');
252
253 WHEN invalid_rev_budget_code THEN
254
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 x_msg_count := 1;
257 x_msg_data := 'INVALID_REV_BUDGET_TYPE';
258 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MC_BILLING_PVT',
259 p_procedure_name => 'get_budget_amount');
260
261 WHEN rev_budget_not_baselined THEN
262 x_return_status := FND_API.G_RET_STS_ERROR;
263 x_msg_count := 1;
264 x_msg_data := 'REV_BUDGET_NOT_BASELINED';
265 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MC_BILLING_PVT',
266 p_procedure_name => 'get_budget_amount');
267
268 WHEN cost_budget_not_baselined THEN
269 x_return_status := FND_API.G_RET_STS_ERROR;
270 x_msg_count := 1;
271 x_msg_data := 'COST_BUDGET_NOT_BASELINED';
272 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_MC_BILLING_PVT',
273 p_procedure_name => 'get_budget_amount');
274
275 WHEN OTHERS THEN
276 x_msg_count := 1;
277 x_msg_data := SUBSTR(SQLERRM, 1, 240);
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279
280 FND_MSG_PUB.add_Exc_msg(
281 p_pkg_name => 'PA_MC_BILLING_PVT',
282 p_procedure_name => 'get_budget_amount');
283
284 RAISE ;
285
286 END get_budget_amount;
287
288
289
290
291 PROCEDURE get_project_task_budget_amount(
292 p_budget_version_id IN NUMBER,
293 p_project_id IN NUMBER,
294 p_task_id IN NUMBER,
295 p_psob_id IN NUMBER,
296 p_rsob_id IN NUMBER,
297 x_raw_cost_total IN OUT NOCOPY NUMBER,
298 x_burdened_cost_total IN OUT NOCOPY NUMBER,
299 x_revenue_total IN OUT NOCOPY NUMBER,
300 x_return_status OUT NOCOPY VARCHAR2,
301 x_msg_count OUT NOCOPY NUMBER,
302 x_msg_data OUT NOCOPY VARCHAR2)
303
304 IS
305
306
307 /* -----------------------------------------------------------------------------
308 How to use this API:
309 This API can be used to get the total cost and revenue budget amount in reporting
310 currency for Project Level or at the task level. If Input task_id is passed
311 as a null value then project level totals are fetched. Otherwise task level totals
312 are fetched. For task level totals, first the task level is determined.
313 If the task level is top or intermediate level , then the amounts
314 are rolled from the child tasks.
315 ------------------------------------------------------------------------------ */
316
317 CURSOR csr_rollup IS
318 SELECT 'P'
319 FROM dual
320 WHERE p_task_id is null
321 UNION
322 SELECT 'T'
323 FROM pa_tasks
324 WHERE p_task_id is not null
325 AND task_id = p_task_id
326 AND parent_task_id is null
327 UNION
328 SELECT 'M'
329 FROM pa_tasks
330 WHERE p_task_id is not null
331 AND task_id = p_task_id
332 AND parent_task_id is not null
333 AND exists (select 'X'
334 from pa_tasks
335 where parent_task_id = p_task_id)
336 UNION
337 SELECt 'L'
338 FROM dual
339 WHERE p_task_id is not null
340 AND not exists (select 'X'
341 from pa_tasks
342 where parent_task_id = p_task_id);
343
344
345
346 l_rollup_level VARCHAR2(1);
347
348 l_raw_cost_total NUMBER;
349 l_burdened_cost_total NUMBER;
350 l_revenue_total NUMBER;
351
352 l_msg_count NUMBER;
353 l_return_status VARCHAR2(30);
354 BEGIN
355
356
357 /* -----------------------------------------------------------
358 Initialize the Output Variables
359 ----------------------------------------------------------- */
360
361 l_return_status := FND_API.G_RET_STS_SUCCESS;
362 l_msg_count := 0;
363
364
365
366 /* -------------------------------------------------------------------------
367 Cursor : To get the Rollup level, The Values are
368 Project(P) or Top Task(T) or Middle Task(M) or Low level Task(L)
369 ------------------------------------------------------------------------- */
370
371
372 OPEN csr_rollup;
373
374 FETCH csr_rollup
375 INTO l_rollup_level;
376
377 CLOSE csr_rollup;
378
379
380
381 /* ------------------------------------------------------------------------
382 Get the Cost and Revenue budget amount - Depends upon the Rollup level,
383 different SELECT for get the amount.
384 ------------------------------------------------------------------------ */
385
386
387 /* Project Level Task */
388 IF (l_rollup_level = 'P') THEN
389 NULL;
390 /* Commented out for MRC migration to SLA
391 SELECT SUM(NVL(mcbl.raw_cost,0)),
392 SUM(NVL(mcbl.burdened_cost,0)),
393 SUM(NVL(mcbl.revenue,0))
394 INTO l_raw_cost_total,
395 l_burdened_cost_total,
396 l_revenue_total
397 FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
398 pa_resource_assignments a
399 WHERE bl.budget_line_id = mcbl.budget_line_id
400 AND a.budget_version_id = p_budget_version_id
401 AND a.project_id = p_project_id
402 AND a.resource_assignment_id = bl.resource_assignment_id
403 AND mcbl.set_of_books_id = p_rsob_id
404 ;
405
406 */
407 /* Top Task Level */
408 ELSIF (l_rollup_level = 'T') THEN
409 NULL;
410 /* Commented out for MRC migration to SLA
411 SELECT SUM(NVL(mcbl.raw_cost,0)),
412 SUM(NVL(mcbl.burdened_cosT,0)),
413 SUM(NVL(mcbl.revenue,0))
414 INTO l_raw_cost_total,
415 l_burdened_cost_total,
416 l_revenue_total
417 FROM pa_tasks t, pa_mc_budget_lines mcbl , pa_budget_lines bl,
418 pa_resource_assignments a
419 WHERE bl.budget_line_id = mcbl.budget_line_id
420 AND a.budget_version_id = p_budget_version_id
421 AND a.task_id = t.task_id
422 AND t.top_task_id = p_task_id
423 AND a.resource_assignment_id = bl.resource_assignment_id
424 AND mcbl.set_of_books_id = p_rsob_id
425 ;
426
427 */
428 ELSIF (l_rollup_level = 'M') THEN /* Middle Level Task */
429 NULL;
430 /* Commented out for MRC migration to SLA
431 SELECT SUM(NVL(mcbl.raw_cost,0)),
432 SUM(NVL(mcbl.burdened_cost,0)),
433 SUM(NVL(mcbl.revenue,0))
434 INTO l_raw_cost_total,
435 l_burdened_cost_total,
436 l_revenue_total
437 FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
438 pa_resource_assignments a
439 WHERE bl.budget_line_id = mcbl.budget_line_id
440 AND a.budget_version_id = p_budget_version_id
441 AND a.task_id in (SELECT task_id
442 FROM pa_tasks
443 START with task_id = p_task_id
444 CONNECT by prior task_id = parent_task_id)
445 AND a.resource_assignment_id = bl.resource_assignment_id
446 AND mcbl.set_of_books_id = p_rsob_id
447 ;
448 */
449 ELSIF (l_rollup_level = 'L') THEN /* Low Level Task */
450
451 NULL;
452 /* Commented out for MRC migration to SLA
453 SELECT SUM(NVL(mcbl.raw_cost,0)),
454 SUM(NVL(mcbl.burdened_cost,0)),
455 SUM(NVL(mcbl.revenue,0))
456 INTO l_raw_cost_total,
457 l_burdened_cost_total,
458 l_revenue_total
459 FROM pa_mc_budget_lines mcbl, pa_budget_lines bl,
460 pa_resource_assignments a
461 WHERE bl.budget_line_id = mcbl.budget_line_id
462 AND a.budget_version_id = p_budget_version_id
463 AND a.task_id = p_task_id
464 AND a.resource_assignment_id = bl.resource_assignment_id
465 AND mcbl.set_of_books_id = p_rsob_id
466 ;
467 */
468 END IF;
469
470
471 /* ---------------------------------------------------------
472 Assign the Revenue and cost budget amount to the Output
473 --------------------------------------------------------- */
474
475
476 x_raw_cost_total := l_raw_cost_total ;
477 x_burdened_cost_total := l_burdened_cost_total ;
478 x_revenue_total := l_revenue_total ;
479 x_return_status := l_return_status;
480 x_msg_count := l_msg_count;
481
482
483 EXCEPTION
484 WHEN OTHERS THEN
485 x_msg_count := 1;
486 x_msg_data := SUBSTR(SQLERRM, 1, 240);
487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
488
489 FND_MSG_PUB.add_Exc_msg(
490 p_pkg_name => 'PA_MC_BILLING_PVT',
491 p_procedure_name => 'get_budget_amount');
492
493 RAISE ;
494
495 END get_project_task_budget_amount ;
496
497
498
499 PROCEDURE get_cost_amount(
500 p_project_id IN NUMBER ,
501 p_task_id IN NUMBER ,
502 p_psob_id IN NUMBER ,
503 p_rsob_id IN NUMBER ,
504 p_accrue_through_date IN DATE ,
505 x_cost_amount OUT NOCOPY NUMBER ,
506 x_return_status OUT NOCOPY VARCHAR2 ,
507 x_msg_count OUT NOCOPY NUMBER ,
508 x_msg_data OUT NOCOPY VARCHAR2)
509 IS
510
511 l_cost_amount NUMBER;
512 l_return_status VARCHAR2(30);
513 l_msg_count NUMBER;
514
515
516 BEGIN
517
518
519 /* -----------------------------------------------------------
520 Initialize the Output Variables
521 ----------------------------------------------------------- */
522
523 l_return_status := FND_API.G_RET_STS_SUCCESS;
524 l_msg_count := 0;
525
526
527
528 /* -----------------------------------------------------------------
529 Get the Cost amount for the sepcific Reporting currency and based
530 on the PA date and accrue throug date.
531 ----------------------------------------------------------------- */
532
533
534 NULL;
535 /* Commented out for MRC migration to SLA
536 SELECT SUM(NVL(mccdl.burdened_cost, NVL(mccdl.amount,0)))
537 INTO l_cost_amount
538 FROM pa_cost_distribution_lines_all cdl,
539 pa_mc_cost_dist_lines_all mccdl,
540 pa_tasks t, pa_periods pp
541 WHERE cdl.project_id = t.project_id
542 AND t.project_id = p_project_id
543 AND nvl(cdl.task_id, -1) = nvl(t.task_id, -1)
544 AND nvl(t.task_id, -1) = nvl(p_task_id, nvl(t.task_id, -1))
545 AND mccdl.expenditure_item_id = cdl.expenditure_item_id
546 AND mccdl.line_num = cdl.line_num
547 AND ( cdl.pa_date BETWEEN pp.start_date AND pp.end_date)
548 AND (trunc(NVL(p_accrue_through_date, SYSDATE)) >= TRUNC(pp.start_date)) -- BUG#3118592
549 AND cdl.line_type = 'R'
550 AND mccdl.set_of_books_id = p_rsob_id ;
551 */
552
553 /* ---------------------------------------------------
554 Assign the value to the OUTPUT variables
555 --------------------------------------------------- */
556
557 x_cost_amount := l_cost_amount ;
558 x_return_status := l_return_status;
559 x_msg_count := l_msg_count;
560
561
562 EXCEPTION
563 WHEN OTHERS THEN
564 x_msg_count := 1;
565 x_msg_data := SUBSTR(SQLERRM, 1, 240);
566 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
567
568 FND_MSG_PUB.add_Exc_msg(
569 p_pkg_name => 'PA_MC_BILLING_PVT',
570 p_procedure_name => 'get_cost_amount');
571 RAISE ;
572
573
574 END get_cost_amount;
575
576
577
578
579 PROCEDURE get_pot_event_amount(
580 p_project_id IN NUMBER,
581 p_task_id IN NUMBER,
582 p_psob_id IN NUMBER,
583 p_rsob_id IN NUMBER,
584 p_event_id IN NUMBER,
585 p_accrue_through_date IN DATE,
586 x_event_amount OUT NOCOPY NUMBER,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2)
590 IS
591
592
593 l_mc_revenue_amount NUMBER;
594 l_return_status VARCHAR2(30);
595 l_msg_count NUMBER;
596
597 BEGIN
598
599
600 /* -----------------------------------------------------------
601 Initialize the Output Variables
602 ----------------------------------------------------------- */
603
604 l_return_status := FND_API.G_RET_STS_SUCCESS;
605 l_msg_count := 0;
606 l_mc_revenue_amount := 0;
607
608
609 /* ----------------------------------------------------------------
610 Get the Revenue event in reporting currency. Ignore the current
611 processing event.
612 ---------------------------------------------------------------- */
613
614
615 NULL;
616 /* Commented out for MRC migration to SLA
617 SELECT SUM((DECODE(et.event_type_classification, 'WRITE OFF',-1 * NVL(mcevt.revenue_amount,0),
618 NVL(mcevt.revenue_amount,0))))
619 INTO l_mc_revenue_amount
620 FROM pa_events e,
621 pa_mc_events mcevt,
622 pa_event_types et
623 WHERE e.event_type = et.event_type
624 AND e.project_id = p_project_id
625 AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id,-1))
626 AND e.event_id = mcevt.event_id
627 AND e.event_id <> NVL(p_event_id, -1)
628 AND mcevt.set_of_books_id = p_rsob_id
629 AND TRUNC(e.completion_date) <= TRUNC(nvl(p_accrue_through_date, sysdate))
630 */
631
632 /* -------------------------------------------------------
633 Copy the value into the OUTPUT parameter
634 ------------------------------------------------------- */
635
636 x_event_amount := l_mc_revenue_amount ;
637 x_return_status := l_return_status;
638 x_msg_count := l_msg_count;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 x_msg_count := 1;
643 x_msg_data := SUBSTR(SQLERRM, 1, 240);
644 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
645
646 FND_MSG_PUB.add_Exc_msg(
647 p_pkg_name => 'PA_MC_BILLING_PVT',
648 p_procedure_name => 'get_pot_event_amount');
649 RAISE ;
650
651
652 END get_pot_event_amount;
653
654
655
656
657 PROCEDURE get_Lowest_amount_left(
658 p_project_id IN NUMBER,
659 p_task_id IN NUMBER,
660 p_psob_id IN NUMBER,
661 p_rsob_id IN NUMBER,
662 p_event_id IN NUMBER,
663 x_funding_amount OUT NOCOPY NUMBER,
664 x_return_status OUT NOCOPY VARCHAR2,
665 x_msg_count OUT NOCOPY NUMBER,
666 x_msg_data OUT NOCOPY VARCHAR2)
667 IS
668
669
670 l_mc_current_revenue NUMBER;
671 l_lowest_revenue_amount NUMBER;
672
673 l_Enable_Top_Task_Cust_Flag VARCHAR2(1);
674
675 l_return_status VARCHAR2(30);
676 l_msg_count NUMBER;
677 l_msg_data VARCHAR2(2000);
678
679 BEGIN
680
681
682 -- Following changes are made for FP_M : Top Task customer changes
683 -- If the Project is implemented with Top Task Customer flag enabled, then
684 -- assign 'Y' value to the variable l_Enable_Top_Task_Cust_Flag
685 l_Enable_Top_Task_Cust_Flag := PA_Billing_Pub.Get_Top_Task_Customer_Flag(P_Project_ID );
686
687 /* -----------------------------------------------------------
688 Initialize the Output Variables
689 ----------------------------------------------------------- */
690
691 l_return_status := FND_API.G_RET_STS_SUCCESS;
692 l_msg_count := 0;
693
694
695 /* -----------------------------------------------------------------
696 Get the event revenue amount in reporting currency for calculate
697 the avaialbe funding amount
698 ----------------------------------------------------------------- */
699 NULL;
700 /* Commented out for MRC migration to SLA
701 SELECT SUM(DECODE(e.revenue_distributed_flag,'N', NVL(mcevt.revenue_amount,0),0)) revenue_amount
702 INTO l_mc_current_revenue
703 FROM pa_events e, pa_mc_events mcevt, pa_event_types et
704 WHERE e.project_id = p_project_id
705 AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id,-1))
706 AND e.event_id = mcevt.event_id
707 AND e.event_id <> NVL(p_event_id, -1)
708 AND mcevt.set_of_books_id = p_rsob_id
709 AND e.event_type = et.event_type
710 AND et.event_type_classification||'' = 'AUTOMATIC';
711 */
712
713 -- Following IF clause is added for FP_M changes
714 -- If the project is implemented with Top Task Customer enabled then the lowest
715 -- amount left is calculated as the total baselined fundings less the
716 -- total accrued amount
717 If l_Enable_Top_Task_Cust_Flag = 'Y' then
718 NULL;
719 /* Commented out for MRC migration to SLA
720 SELECT SUM(NVL(mcspf.total_baselined_amount,0) -
721 NVL(mcspf.total_accrued_amount,0))
722 -- INTO x_Funding_Amount
723 INTO l_lowest_revenue_amount
724 FROM pa_summary_project_fundings spf,
725 pa_mc_sum_proj_fundings mcspf,
726 pa_agreements_all a
727 WHERE a.agreement_id = spf.agreement_id
728 AND spf.task_id = p_task_id
729 AND spf.project_id = p_project_id
730 AND a.revenue_limit_flag = 'Y'
731 AND mcspf.set_of_books_id = p_rsob_id;
732 */
733 Else
734 NULL;
735 /* Commented out for MRC migration to SLA
736 SELECT MIN(SUM(NVL(mcspf.total_baselined_amount,0)
737 - NVL(mcspf.total_accrued_amount,0))
738 * (100/pc.customer_bill_split) )
739 INTO l_lowest_revenue_amount
740 FROM pa_summary_project_fundings spf,
741 pa_mc_sum_proj_fundings mcspf,
742 pa_agreements_all a,
743 pa_projects p,
744 pa_project_customers pc
745 WHERE a.agreement_id = spf.agreement_id
746 AND p.project_id = spf.project_id
747 AND a.customer_id = pc.customer_id
748 AND pc.project_id = p.project_id
749 AND nvl(spf.task_id,-1) = nvl(p_task_id,-1)
750 AND spf.project_id = p_project_id
751 AND a.revenue_limit_flag = 'Y'
752 AND mcspf.project_id = spf.project_id
753 AND nvl(mcspf.task_id,-1) = nvl(spf.task_id,-1)
754 AND mcspf.agreement_id = spf.agreement_id
755 AND mcspf.set_of_books_id = p_rsob_id
756 GROUP BY pc.customer_id, pc.customer_bill_split;
757 */
758 End IF;
759
760 x_funding_amount := GREATEST((nvl(l_lowest_revenue_amount,999999999999)
761 - nvl(l_mc_current_revenue,0)),0);
762
763 x_return_status := l_return_status;
764 x_msg_count := l_msg_count;
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 x_msg_count := 1;
769 x_msg_data := SUBSTR(SQLERRM, 1, 240);
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771
772 FND_MSG_PUB.add_Exc_msg(
773 p_pkg_name => 'PA_MC_BILLING_PVT',
774 p_procedure_name => 'get_Lowest_amount_left');
775 RAISE ;
776
777 END get_Lowest_amount_left;
778
779
780 PROCEDURE get_revenue_amount(
781 p_project_id IN NUMBER,
782 p_task_id IN NUMBER,
783 p_psob_id IN NUMBER,
784 p_rsob_id IN NUMBER,
785 p_event_id IN NUMBER,
786 x_revenue_amount OUT NOCOPY NUMBER,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_msg_count OUT NOCOPY NUMBER,
789 x_msg_data OUT NOCOPY VARCHAR2)
790 IS
791
792
793
794 l_mc_revenue_amount NUMBER;
795 l_erdl_accrued_amount NUMBER;
796 l_rdl_accrued_amount NUMBER;
797
798 l_return_status VARCHAR2(30);
799 l_msg_count NUMBER;
800
801 BEGIN
802
803
804 /* -----------------------------------------------------------
805 Initialize the Output Variables
806 ----------------------------------------------------------- */
807
808 l_return_status := FND_API.G_RET_STS_SUCCESS;
809 l_msg_count := 0;
810
811 /* ---------------------------------------------------------------------
812 Get the Event Revenue amount for the pending revenue for the
813 specific Reporting set of book id., Ignore to process the current
814 processing event.
815 --------------------------------------------------------------------- */
816
817
818 NULL;
819 /* Commented out for MRC migration to SLA
820 SELECT SUM(NVL(mcevt.revenue_amount,0)) revenue_amount
821 INTO l_mc_revenue_amount
822 FROM pa_events e, pa_mc_events mcevt,
823 pa_billing_assignments bea,
824 pa_billing_extensions be
825 WHERE be.billing_extension_id = bea.billing_extension_id
826 AND e.project_id = p_project_id
827 AND nvl(e.task_id,-1) = nvl(p_task_id, nvl(e.task_id, -1))
828 AND e.event_id = mcevt.event_id
829 AND e.event_id <> nvl(p_event_id, -1)
830 AND mcevt.set_of_books_id = p_rsob_id
831 AND bea.billing_assignment_id = e.billing_assignment_id
832 AND be.procedure_name = 'pa_billing.ccrev'
833 AND e.revenue_distributed_flag||'' = 'N';
834 */
835
836
837
838 /* ---------------------------------------------------------------------
839 Get the Revenue amount for the specific Reporting currency from the
840 pa_mc_cust_event_rdl_all table
841 --------------------------------------------------------------------- */
842
843
844 NULL;
845 /* Commented out for MRC migration to SLA
846 SELECT sum(nvl(mcerdl.amount,0))
847 INTO l_erdl_accrued_amount
848 FROM pa_draft_revenue_items dri, pa_mc_cust_event_rdl_all mcerdl,
849 pa_events e, pa_billing_assignments bea,
850 pa_billing_extensions be
851 WHERE dri.project_id = p_project_id
852 AND NVL(dri.task_id,-1) = NVL(p_task_id, nvl(dri.task_id, -1))
853 AND mcerdl.project_id = dri.project_id
854 AND NVL(mcerdl.task_id, -1) = NVL(dri.task_id, -1)
855 AND mcerdl.draft_revenue_num = dri.draft_revenue_num
856 AND mcerdl.line_num = dri.line_num
857 AND mcerdl.set_of_books_id = p_rsob_id
858 AND e.project_id = mcerdl.project_id
859 AND nvl(e.task_id,-1) = nvl(mcerdl.task_id, -1)
860 AND e.event_num = mcerdl.event_num
861 AND be.billing_extension_id = bea.billing_extension_id
862 AND bea.billing_assignment_id = e.billing_assignment_id
863 AND be.procedure_name = 'pa_billing.ccrev'; */ /* Check with SS for this ccrev join */
864
865
866 /* ---------------------------------------------------------------------
867 Get the Revenue amount for the specific Reporting currency from the
868 pa_mc_cust_rdl_all table
869 --------------------------------------------------------------------- */
870
871 NULL;
872 /* Commented out for MRC migration to SLA
873 SELECT sum(nvl(mcrdl.amount,0))
874 INTO l_rdl_accrued_amount
875 FROM pa_draft_revenue_items dri, pa_mc_cust_rdl_all mcrdl
876 WHERE dri.project_id = p_project_id
877 AND NVL(dri.task_id,-1) = nvl(p_task_id, nvl(dri.task_id, -1))
878 AND mcrdl.project_id = dri.project_id
879 AND mcrdl.draft_revenue_num = dri.draft_revenue_num
880 AND mcrdl.line_num = dri.line_num
881 AND mcrdl.set_of_books_id = p_rsob_id
882 AND dri.revenue_source like 'Expenditure%' ; */
883 /* Check with SS for revenue source condition */
884
885
886
887 /* -------------------------------------------------------------------------
888 Get the sum of the Pending revenue amount (MC events) and accured revenue
889 in reporting currency from RDL and ERDL table.
890 ------------------------------------------------------------------------- */
891
892
893 /* dbms_output.put_line('Event Revenue ............ : ' || l_mc_revenue_amount);
894 dbms_output.put_line('ERDL Revenue ............ : ' || l_erdl_accrued_amount);
895 dbms_output.put_line('RDL Revenue ............ : ' || l_rdl_accrued_amount );
896 */
897
898
899 x_revenue_amount := NVL(l_mc_revenue_amount,0) + NVL(l_erdl_accrued_amount,0) +
900 NVL(l_rdl_accrued_amount,0) ;
901
902 x_return_status := l_return_status;
903 x_msg_count := l_msg_count;
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 x_msg_count := 1;
908 x_msg_data := SUBSTR(SQLERRM, 1, 240);
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
910
911 FND_MSG_PUB.add_Exc_msg(
912 p_pkg_name => 'PA_MC_BILLING_PVT',
913 p_procedure_name => 'get_revenue_amount');
914 RAISE ;
915
916 END get_revenue_amount;
917
918
919 END pa_mc_billing_pvt;