[Home] [Help]
PACKAGE BODY: APPS.PA_BILLING_CORE
Source
1 package body pa_billing_core as
2 -- $Header: PAXINBCB.pls 120.5.12010000.4 2009/07/15 09:32:49 dbudhwar ship $
3
4 -- This is the main billing procedure to verify baseline funding.
5 --
6 -- History
7 -- 10-SEP-97 jwhite Added code for mulitple messaging
8 --
9 --
10 -- 02.26.1999 Partha Comment corrected for dbms_output as per adchkdrv log.
11
12 -- 01-MAR-99 Tianyi Change != to <>
13 -- 07-SEP-01 modified to use _all tables
14 -- Changed to use projfunc amounts (MCB2)
15 r_amount number; /* Added for bug 2913524 */
16 procedure verify_baseline_funding(
17 x_project_id in number,
18 x_draft_version_id in number,
19 x_entry_level_code in varchar2,
20 x_proj_bu_revenue in number,
21 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
22 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
23 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
24 is
25
26 x_funding_level varchar2(2);
27 x_task_id number(15) default 0;
28 dummy number;
29 cost_rule_flag varchar2(2) default 'N';
30 x_revenue number default 0;
31 x_raw_cost number default 0;
32 x_burdened_cost number default 0;
33 x_labor_quantity number default 0;
34 funding_total number default 0;
35 old_stack varchar2(630);
36 cost_rec number;
37 amt_over_rec number;
38 proj_event_rec number;
39 agr_rec number; /* Bug#2303396 */
40 l_fin_plan_type_id number(15) default null; --Bug#2668857
41 l_version_type varchar2(30) default null; --Bug#2668857
42
43
44 cursor cost is
45 select 1
46 from pa_projects_all
47 where project_id = x_project_id
48 and substr(distribution_rule, 1, 5) = 'COST/';
49
50 /* Commented for bug 2744993
51 cursor amt_over is
52 select 1
53 from sys.dual
54 where not exists
55 (select 1
56 from pa_agreements_all a,
57 pa_summary_project_fundings f
58 where f.project_id = x_project_id
59 and nvl(f.task_id, 0) = x_task_id
60 and ( ( nvl(f.total_baselined_amount, 0)
61 + f.total_unbaselined_amount
62 )
63 < greatest(nvl(f.total_accrued_amount, 0),
64 nvl(f.total_billed_amount, 0))
65 )
66 and f.agreement_id = a.agreement_id
67 and a.revenue_limit_flag = 'Y');
68 */
69 cursor amt_over is
70 select 1
71 from sys.dual
72 where not exists
73 (select 1
74 from pa_agreements_all a,
75 pa_summary_project_fundings f
76 where f.project_id = x_project_id
77 and nvl(f.task_id, 0) = x_task_id
78 and ( ( nvl(f.projfunc_baselined_amount, 0)
79 + f.projfunc_unbaselined_amount
80 )
81 < nvl(f.projfunc_accrued_amount, 0)
82 )
83 and f.agreement_id = a.agreement_id
84 and a.revenue_limit_flag = 'Y')
85 and not exists
86 (select 1
87 from pa_agreements_all a,
88 pa_summary_project_fundings f
89 where f.project_id = x_project_id
90 and nvl(f.task_id, 0) = x_task_id
91 and ( ( nvl(f.invproc_baselined_amount, 0)
92 + f.invproc_unbaselined_amount
93 )
94 < nvl(f.invproc_billed_amount, 0)
95 )
96 and f.agreement_id = a.agreement_id
97 and a.invoice_limit_flag = 'Y');
98
99
100 cursor proj_event is
101 select 1
102 from pa_events
103 where project_id = x_project_id
104 and task_id is null;
105 -- Bug 748105 (In task level baselining every task level funding
106 -- should have corresponding task level budget entry
107 -- and vice versa)
108 --Bug 2668245 Added a Or condition ( Approved_rev_plan_type_flag=Y) in budget_type_code
109 cursor budget_task is
110 SELECT t.top_task_id , sum(l.revenue) revenue
111 FROM pa_budget_lines l,
112 pa_resource_assignments a,
113 pa_tasks t,
114 pa_budget_versions v
115 WHERE v.project_id = x_project_id
116 AND (v.budget_type_code = 'AR'
117 OR v.approved_rev_plan_type_flag ='Y')
118 AND v.budget_status_code IN ('S','W') /* Fix for Bug # 1206240*/
119 AND decode(v.budget_type_code,null,v.current_working_flag,'Y')='Y' /* Added for bug 2834104 */
120 and a.budget_version_id = v.budget_version_id
121 and a.project_id = v.project_id
122 and t.project_id = v.project_id
123 and t.task_id = a.task_id
124 and a.task_id is not null
125 AND l.resource_assignment_id = a.resource_assignment_id
126 group by t.top_task_id
127 having nvl(sum(l.revenue),0) <> 0 /* Fix for Bug 4735399 */
128 order by t.top_task_id;
129 /*
130 Commented and changed for the below cursor (MCB2)
131 select task_id, sum(nvl(allocated_amount,0)) funding_total
132 */
133 -- Following cursor is modified for bux fix 3763133
134 cursor funding_task is
135 select task_id, sum(nvl(projfunc_allocated_amount,0)) funding_total
136 from pa_project_fundings
137 where project_id = x_project_id
138 AND ( (budget_type_code IN ('DRAFT', 'BASELINE') AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N'
139 AND PA_Funding_Core.G_Fund_Baseline_Flag = 'N')
140 OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y' AND (
141 ( (budget_type_code ='BASELINE') OR
142 (budget_type_code ='DRAFT' AND funding_category=
143 'REVALUATION') )))
144 OR (PA_Funding_Core.G_Fund_Baseline_Flag = 'Y' AND
145 ( Budget_Type_Code = 'BASELINE' ) OR -- Modified for bug 4057927
146 ( Budget_Type_Code = 'DRAFT' AND NVL(Submit_Baseline_Flag,'N') = 'Y') )
147 )
148 and task_id is not null
149 group by task_id
150 having nvl(sum(nvl(projfunc_allocated_amount,0)),0) <> 0 /* Fix for Bug 4710749 */
151 order by task_id;
152
153 /* Code for Bug#2303396 */
154
155 cursor agreements is
156 select 1 from dual where not exists
157 (
161 and not exists
158 select PPC.customer_id from pa_project_customers PPC
159 where PPC.project_id= x_project_id
160 and PPC.customer_bill_split > 0 /* Added for Bug2453912 */
162 (
163 select 1 from pa_summary_project_fundings PSPF,
164 pa_agreements_all PAA
165 where PPC.customer_id = PAA.customer_id
166 and PAA.agreement_id = PSPF.agreement_id
167 and PPC.project_id= PSPF.project_id
168 )
169 );
170
171 /* End of Code Fix Bug#2303396 */
172
173
174 b_task_id number;
175 f_task_id number;
176 b_revenue number;
177 b_end_flag varchar2(1) := 'F';
178 f_end_flag varchar2(1) := 'F';
179
180 -- END 748105 (code continues below)
181
182 begin
183
184 x_err_code := 0;
185 old_stack := x_err_stack;
186 x_err_stack := 'pa_billing_core->check_funding_for_baseline';
187
188 -- check project funding level flag properly
189 check_funding_level(x_project_id,
190 x_funding_level,
191 x_err_code,
192 x_err_stage,
193 x_err_stack);
194
195 if x_err_code <> 0 then
196
197 return;
198
199 end if;
200
201 -- Check if it is a COST/ based project.
202 open cost;
203 fetch cost into cost_rec;
204 if cost%notfound then
205 cost_rule_flag := 'N';
206 else
207 cost_rule_flag := 'Y';
208 end if;
209
210 close cost;
211
212 if (x_funding_level = 'P') then
213
214 -- check the sum of revenue budget equals the sum of
215 -- draft and baseline funding
216 x_err_stage := 'check if budget equals funding <'
217 || to_char(x_draft_version_id) || '>';
218
219 /*
220 Commented and changed for the below sql (MCB2)
221 select sum(nvl(allocated_amount,0))
222 */
223 -- Following select is modified for bux fix 3763133
224 select sum(nvl(projfunc_allocated_amount,0))
225 into funding_total
226 from pa_project_fundings
227 where project_id = x_project_id
228 AND ( (budget_type_code IN ('DRAFT', 'BASELINE') AND PA_FUND_REVAL_PVT.G_REVAL_FLAG ='N'
229 AND PA_Funding_Core.G_Fund_Baseline_Flag = 'N')
230 OR (PA_FUND_REVAL_PVT.G_REVAL_FLAG ='Y' AND (
231 ( (budget_type_code ='BASELINE') OR
232 (budget_type_code ='DRAFT' AND funding_category=
233 'REVALUATION') )))
234 OR (PA_Funding_Core.G_Fund_Baseline_Flag = 'Y' AND
235 ( Budget_Type_Code = 'BASELINE') OR -- Modified for bug 4057927
236 ( Budget_Type_Code = 'DRAFT' AND NVL(Submit_Baseline_Flag,'N') = 'Y') )
237 );
238
239 if (x_proj_bu_revenue <> funding_total) then
240 x_err_code := 50;
241 x_err_stage := 'PA_BU_UNBALANCED_PROJ_BUDGET';
242
243 -- 10-SEP-97, jwhite: multiple messaging
244 PA_UTILS.Add_Message
245 ( p_app_short_name => 'PA'
246 , p_msg_name => x_err_stage
247 );
248
249 end if;
250
251 -- total amount allocated cannot be less than amount accrued
252 -- or billed
253 open amt_over;
254 fetch amt_over into amt_over_rec;
255 if amt_over%notfound then
256 x_err_code := 40;
257 x_err_stage := 'PA_BU_AMT_ALLOC_LT_AMT_ACCRUED';
258
259 -- 10-SEP-97, jwhite: multiple messaging
260 PA_UTILS.Add_Message
261 ( p_app_short_name => 'PA'
262 , p_msg_name => x_err_stage
263 );
264
265
266
267 elsif sqlcode < 0 then
268
269 x_err_code := SQLCODE;
270 -- 10-SEP-97, jwhite: multiple messaging
271 FND_MSG_PUB.Add_Exc_Msg
272 ( p_pkg_name => 'PA_BILLING_CORE'
273 , p_procedure_name => 'VERIFY_BASELINE_FUNDING'
274 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
275 );
276
277 return;
278
279 end if;
280 close amt_over;
281
282 if cost_rule_flag = 'Y' then
283 /* Code Changes starts for Bug#2668857*/
284 BEGIN
285 select fin_plan_type_id,
286 version_type
287 into l_fin_plan_type_id,l_version_type
288 from pa_budget_versions
289 where budget_version_id = x_draft_version_id;
290
291 EXCEPTION
292 WHEN NO_DATA_FOUND THEN
293 l_fin_plan_type_id := NULL;
294 l_version_type := NULL;
295 END;
296
297 IF l_fin_plan_type_id is NOT NULL THEN
298 dummy := pa_fin_plan_utils.check_proj_fin_plan_exists (x_project_id => x_project_id ,
299 x_budget_version_id => x_draft_version_id ,
300 x_budget_status_code => 'B' ,
301 x_plan_type_code => 'AC', -- Approved Cost Budget Plan
302 x_fin_plan_type_id => l_fin_plan_type_id ,
303 x_version_type => l_version_type );
304 ELSE
305
306
307 /* Code Changes ends here for Bug#2668857*/
308
309 dummy := pa_budget_utils.check_proj_budget_exists(
313 END IF; /* End if for l_fin_plan_type_id is NOT NULL THEN for Bug#2668857*/
310 x_project_id,
311 'B', -- budget status code
312 'AC'); -- budget type code
314 if dummy = 0 then
315
316 x_err_code := 80;
317 x_err_stage := 'PA_BU_NO_BASE_COST_BUDGET';
318 -- 10-SEP-97, jwhite: multiple messaging
319 PA_UTILS.Add_Message
320 ( p_app_short_name => 'PA'
321 , p_msg_name => x_err_stage
322 );
323
324
325
326 elsif dummy <> 1 then
327
328 x_err_code := dummy;
329 return;
330
331 end if;
332
333 end if; -- cost_rule_flag = 'Y'
334
335 else -- x_funding_level = 'T'
336
337 -- Cannot have project level budget.
338 if x_entry_level_code = 'P' then
339 x_err_code := 60;
343 ( p_app_short_name => 'PA'
340 x_err_stage := 'PA_BU_TASK_FUND_PROJ_BUDGET';
341 -- 10-SEP-97, jwhite: multiple messaging
342 PA_UTILS.Add_Message
344 , p_msg_name => x_err_stage
345 );
346 end if;
347
348 -- Cannot have project level events.
349 open proj_event;
350 fetch proj_event into proj_event_rec;
351 if proj_event%found then
352 x_err_code := 65;
353 x_err_stage := 'PA_TASK_FUND_NO_PROJ_EVENTS';
354 -- 10-SEP-97, jwhite: multiple messaging
355 PA_UTILS.Add_Message
356 ( p_app_short_name => 'PA'
357 , p_msg_name => x_err_stage
358 );
359 end if;
360 CLOSE proj_event; --For Bug3936620
361
362 /*
363 -- for each top task
364 for fund_rec in (select task_id,
365 sum(nvl(allocated_amount,0)) funding_total
366 from pa_project_fundings
367 where project_id = x_project_id
368 and budget_type_code in ('BASELINE', 'DRAFT')
369 group by task_id) loop
370
371 x_revenue :=0;
372 if fund_rec.task_id is not null then
373
374 x_task_id := fund_rec.task_id;
375 pa_budget_utils.get_task_budget_amount(
376 x_project_id,
377 x_task_id,
378 'AR', -- x_budget_type
379 'DRAFT', -- x_which_version
380 x_revenue,
381 x_raw_cost,
382 x_burdened_cost,
383 x_labor_quantity);
384
385 end if;
386 */
387 -- BEGIN 748105
388 -- for each top task there should be an equal funding and
389 -- vice versa
390
391 open budget_task;
392 open funding_task;
393
394 LOOP
395
396 b_task_id := 0;
397 b_revenue := 0;
398 f_task_id := 0;
399 funding_total := 0;
400
401 fetch budget_task into b_task_id, b_revenue;
402 if budget_task%NOTFOUND then
403 b_end_flag := 'T';
404 end if;
405
406 fetch funding_task into f_task_id, funding_total;
407 if funding_task%NOTFOUND then
408 f_end_flag := 'T';
409 end if;
410
411 if ((b_end_flag <> f_end_flag) OR
412 (nvl(b_task_id,0) <> nvl(f_task_id,0) ) OR
413 (nvl(b_revenue,0) <> nvl(funding_total,0))) THEN
414 x_err_code := 70;
415 x_err_stage := 'PA_BU_UNBALANCED_TASK_BUDGET';
416 -- 10-SEP-97, jwhite: multiple messaging
417 PA_UTILS.Add_Message
418 ( p_app_short_name => 'PA'
419 , p_msg_name => x_err_stage
420 );
421 return;
422 end if;
423 exit when b_end_flag = 'T';
424 -- END 748105
425
426 -- total amount allocated cannot be less than amount accrued
427 -- or billed
428 x_task_id := b_task_id;
429 open amt_over;
430 fetch amt_over into amt_over_rec;
431 if amt_over%notfound then
432 x_err_code := 40;
433 x_err_stage := 'PA_BU_AMT_ALLOC_LT_AMT_ACCRUED';
434 -- 10-SEP-97, jwhite: multiple messaging
435 PA_UTILS.Add_Message
436 ( p_app_short_name => 'PA'
437 , p_msg_name => x_err_stage
438 );
439
440 elsif sqlcode < 0 then
441 x_err_code := SQLCODE;
442 -- 10-SEP-97, jwhite: multiple messaging
443 FND_MSG_PUB.Add_Exc_Msg
444 ( p_pkg_name => 'PA_BILLING_CORE'
445 , p_procedure_name => 'VERIFY_BASELINE_FUNDING'
446 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
447 );
448
449 return;
450
451 end if;
452 close amt_over;
453
454 if cost_rule_flag = 'Y' then
455 /* Code Changes starts for Bug#2668857*/
456 BEGIN
457 select fin_plan_type_id,
458 version_type
459 into l_fin_plan_type_id,
460 l_version_type
461 from pa_budget_versions
462 where budget_version_id = x_draft_version_id;
463
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 l_fin_plan_type_id := NULL;
467 l_version_type := NULL;
468 END;
469
470 IF l_fin_plan_type_id is NOT NULL THEN
471 dummy := pa_fin_plan_utils.check_task_fin_plan_exists (x_task_id => x_task_id ,
472 x_budget_version_id => x_draft_version_id ,
473 x_budget_status_code => 'B' ,
474 x_plan_type_code => 'AC', -- Approved Cost Budget Plan
475 x_fin_plan_type_id => l_fin_plan_type_id ,
479 dummy := pa_budget_utils.check_task_budget_exists(
476 x_version_type => l_version_type );
477 ELSE
478 /* Code Changes ends here for Bug#2668857*/
480 x_task_id,
481 'B', -- budget status code
482 'AC'); -- budget type code
483 END IF;-- End if of l_fin_plan_type_id is NOT NULL Bug#2668857
484 if dummy = 0 then
485
486 x_err_code := 80;
487 x_err_stage := 'PA_BU_NO_TASK_COST_BUDGET';
488 -- 10-SEP-97, jwhite: multiple messaging
489 PA_UTILS.Add_Message
490 ( p_app_short_name => 'PA'
491 , p_msg_name => x_err_stage
492 );
493
494
495
496 elsif dummy <> 1 then
497
498 x_err_code := dummy;
499 return;
500
501 end if;
502
503 end if; -- cost_rule_flag = 'Y'
504
505 end loop;
506 --Introducing the Close statement after the loops For Bug 3936620
507 CLOSE budget_task;
508 CLOSE funding_task;
509 end if;
510
511 /* code fix for Bug#2303396 */
512
513 open agreements;
514 fetch agreements into agr_rec;
515 If ( agreements%notfound ) then
516 -- data not exists
517 x_err_code := 1000;
518 x_err_stage := 'PA_MULTI_CUST_AGR_CHECK';
519 -- multiple messaging
520 PA_UTILS.Add_Message
521 ( p_app_short_name => 'PA',
522 p_msg_name => x_err_stage
523 );
524 end if;
525 CLOSE agreements; --For Bug 3936620
526 /* End of Code fix for Bug#2303396 */
527
528
529 -- Comment out call to update_funding to use as verify only.
530 -- (ckh 09/04/97)
531 --
532 -- change DRAFT funding to BASELINE
533 -- update_funding( x_project_id,
534 -- x_funding_level,
535 -- x_err_code,
536 -- x_err_stage,
537 -- x_err_stack);
538 -- if (x_err_code <> 0) then
539 -- return;
540 -- end if;
541 --
542
543 x_err_stack := old_stack;
544
545 exception
546 when others then
547 x_err_code := SQLCODE;
548 -- 10-SEP-97, jwhite: multiple messaging
549 FND_MSG_PUB.Add_Exc_Msg
550 ( p_pkg_name => 'PA_BILLING_CORE'
551 , p_procedure_name => 'VERIFY_BASELINE_FUNDING'
552 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
553 );
554 return;
555 end verify_baseline_funding;
556
557 -- This procedure is to do funding related update statments.
558 -- 07-SEP-01 updated all use project/projfunc amounts (MCB2)
559
560 procedure update_funding(
561 x_project_id in number,
562 x_funding_level in out NOCOPY varchar2, --File.Sql.39 bug 4440895
563 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
564 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
565 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
566 is
567 -- Standard who
568 x_created_by number(15);
569 x_last_update_login number(15);
570
571 old_stack varchar2(630);
572 non_contract number := 0;
573 l_funding_level varchar2(1) := x_funding_level;
574
575 begin
576
577 x_err_code := 0;
578 old_stack := x_err_stack;
579 x_err_stack := 'pa_billing_core->update_funding';
580
581 x_created_by := FND_GLOBAL.USER_ID;
582 x_last_update_login := FND_GLOBAL.LOGIN_ID;
583
584 -- set project level funding flag
585 x_err_stage := 'set project level funding flag <'
586 || to_char(x_project_id) || '>';
587
588 if x_funding_level is null then
589
590 -- Only project level funding is allowed for the non-contract project.
591 x_funding_level := 'P';
592 non_contract := 1;
593
594 end if;
595
596
597 /*----------------------------------------------------
598 -- Funding revaluation changes:
599 --
600 -- If action is not from revaluation process,
601 -- baseline all the funding records
602 -- update all the amount in summary project funding
603 -- update the all currency columns
604 -- elseif action is from revaluation process
605 -- baseline only the revaluation funding line amount
606 -- update only the revaluation funding line amount
607 -- update only the projfunc, revproc and invproc columns
608 -- End if
609 --------------------------------------------------------*/
610
611 IF PA_FUND_REVAL_PVT.G_REVAL_FLAG='N' THEN
612
613 x_err_stage := 'Not Revaluation: change draft to baseline <' || to_char(x_project_id)
614 || '>';
615
616 update pa_project_fundings
617
618 set /* PJI_SUMMARIZED_FLAG = 'N' -- For Bug 2244796 and bug 2440676 */
619 PJI_SUMMARIZED_FLAG = decode(budget_type_code, 'BASELINE', pji_summarized_flag, 'N'), -- For Bug 3821126
620 budget_type_code = 'BASELINE',
621 last_update_date = SYSDATE,
622 last_updated_by = x_created_by,
626
623 last_update_login = x_last_update_login
624 where project_id = x_project_id
625 and budget_type_code IN('DRAFT','BASELINE');
627 -- update summary funding
628 x_err_stage := 'update summary funding <' || to_char(x_project_id)
629 || '>';
630
631 update pa_summary_project_fundings
632 set total_baselined_amount = total_unbaselined_amount +
633 nvl(total_baselined_amount, 0),
634 total_unbaselined_amount = 0,
635 /* MCB2 code begins */
636 project_baselined_amount = project_unbaselined_amount +
637 nvl(project_baselined_amount, 0),
638 project_unbaselined_amount = 0,
639 projfunc_baselined_amount = projfunc_unbaselined_amount +
640 nvl(projfunc_baselined_amount, 0),
641 projfunc_unbaselined_amount = 0,
642 invproc_baselined_amount = invproc_unbaselined_amount +
643 nvl(invproc_baselined_amount, 0),
644 invproc_unbaselined_amount = 0,
645 revproc_baselined_amount = revproc_unbaselined_amount +
646 nvl(revproc_baselined_amount, 0),
647 revproc_unbaselined_amount = 0,
648 /* MCB2 code ends */
649 last_update_date = SYSDATE,
650 last_updated_by = x_created_by,
651 last_update_login = x_last_update_login
652 where project_id = x_project_id;
653
654 ELSIF PA_FUND_REVAL_PVT.G_REVAL_FLAG='Y' THEN
655
656
657 -- update summary funding
658 x_err_stage := 'update summary funding <' || to_char(x_project_id)
659 || '>';
660
661 /* Bug 2602874 Added agreement_id as join fields for pa_project_funding and
662 pa_summary_project_funding are agreement_id, project_id, task_id */
663
664 FOR reval_rec IN (SELECT project_funding_id, agreement_id, project_id,task_id, projfunc_allocated_amount,
665 invproc_allocated_amount,revproc_allocated_amount
666 FROM pa_project_fundings
667 WHERE project_id = x_project_id
668 AND budget_type_code ='DRAFT'
669 AND funding_category ='REVALUATION') LOOP
670
671 /* Bug 2670854 Since pa_mc_sum_proj_fundings updates its baselined, unbaselined amount
672 based on the budget_type_code, before updating summary project fundings in primary
673 this is being done as trigger on summary project fundings will update mc summary project
674 fundings based on this col value */
675
676 UPDATE pa_project_fundings
677 set /* PJI_SUMMARIZED_FLAG = 'N' -- For Bug 2244796 and bug 2440676 */
678 PJI_SUMMARIZED_FLAG = decode(budget_type_code, 'BASELINE', pji_summarized_flag, 'N'), -- For Bug 3821126
679 budget_type_code = 'BASELINE',
680 last_update_date = SYSDATE,
681 last_updated_by = x_created_by,
682 last_update_login = x_last_update_login
683 WHERE project_funding_id = reval_rec.project_funding_id;
684
685 UPDATE pa_summary_project_fundings
686 SET projfunc_baselined_amount = projfunc_baselined_amount +
687 NVL(reval_rec.projfunc_allocated_amount, 0),
688 projfunc_unbaselined_amount = projfunc_unbaselined_amount -
689 NVL(reval_rec.projfunc_allocated_amount,0),
690 invproc_baselined_amount = invproc_baselined_amount +
691 NVL(reval_rec.invproc_allocated_amount, 0),
692 invproc_unbaselined_amount = invproc_unbaselined_amount -
693 NVL(reval_rec.invproc_allocated_amount,0),
694 revproc_baselined_amount = revproc_baselined_amount +
695 NVL(reval_rec.revproc_allocated_amount, 0),
696 revproc_unbaselined_amount = revproc_unbaselined_amount -
697 NVL(reval_rec.revproc_allocated_amount,0),
698 last_update_date = SYSDATE,
699 last_updated_by = x_created_by,
700 last_update_login = x_last_update_login
701 WHERE project_id = x_project_id
702 AND agreement_id = reval_rec.agreement_id
703 AND nvl(task_id,0) = nvl(reval_rec.task_id,0);
704
705 /* Bug 2602874 Added agreement_id and task_id in where clause as join fields
706 for pa_project_funding and
707 pa_summary_project_funding are agreement_id, project_id, task_id */
708
709 END LOOP;
710
711 /* Moved this code after the updation of spf as otherwise the spf rec will not fetch any record
712 Bug 2547696 */
713 -- baseline funding
714 x_err_stage := 'Revaluation: change draft to baseline <' || to_char(x_project_id)
715 || '>';
716
717 /* Bug 2670854 commented this and moved in the loop above after reading
718 spf but before updating spf
719
720 UPDATE pa_project_fundings
721 SET budget_type_code = 'BASELINE',
722 last_update_date = SYSDATE,
723 last_updated_by = x_created_by,
724 last_update_login = x_last_update_login,
725 pji_summarized_flag = 'N' -- For Bug 2244796 and bug 2440676
726 WHERE project_id = x_project_id
730
727 AND budget_type_code = 'DRAFT'
728 AND funding_category='REVALUATION';
729 */
731 -----------------------------------------------------------------------
732 -- FP_M Changes
733 -- Following IF clause is used only for baselining only required
734 -- Project's agreements funding lines
735 -----------------------------------------------------------------------
736 ELSIF PA_Funding_Core.G_FUND_BASELINE_FLAG = 'Y' THEN
737 -- update summary funding
738 x_err_stage := 'update summary funding <' || to_char(x_project_id) || '>';
739
740 FOR Agreement_rec IN ( SELECT project_funding_id, agreement_id,
741 project_id,task_id, projfunc_allocated_amount,
742 invproc_allocated_amount, revproc_allocated_amount
743 FROM pa_project_fundings
744 WHERE project_id = x_project_id
745 AND budget_type_code ='DRAFT'
746 AND NVL(Submit_Baseline_Flag,'N') = 'Y' )
747 LOOP
748 -- Update the Project fundings for the baselined lines
749 UPDATE pa_project_fundings
750 SET budget_type_code = 'BASELINE',
751 last_update_date = SYSDATE,
752 last_updated_by = x_created_by,
753 last_update_login = x_last_update_login,
754 pji_summarized_flag = 'N', -- For Bug 2244796 and bug 2440676
755 Submit_Baseline_Flag = 'N'
756 WHERE project_funding_id = Agreement_rec.project_funding_id;
757
758 -- Update the Project summary fundings of PFC, Invoice and revenue
759 -- baselined and unbaselined amounts
760 UPDATE pa_summary_project_fundings
761 SET projfunc_baselined_amount = projfunc_baselined_amount +
762 NVL(Agreement_rec.projfunc_allocated_amount, 0),
763 projfunc_unbaselined_amount = projfunc_unbaselined_amount -
764 NVL(Agreement_rec.projfunc_allocated_amount,0),
765 invproc_baselined_amount = invproc_baselined_amount +
766 NVL(Agreement_rec.invproc_allocated_amount, 0),
767 invproc_unbaselined_amount = invproc_unbaselined_amount -
768 NVL(Agreement_rec.invproc_allocated_amount,0),
769 revproc_baselined_amount = revproc_baselined_amount +
770 NVL(Agreement_rec.revproc_allocated_amount, 0),
771 revproc_unbaselined_amount = revproc_unbaselined_amount -
772 NVL(Agreement_rec.revproc_allocated_amount,0),
773 last_update_date = SYSDATE,
774 last_updated_by = x_created_by,
775 last_update_login = x_last_update_login
776 WHERE project_id = x_project_id
777 AND agreement_id = Agreement_rec.agreement_id
778 AND nvl(task_id,0) = nvl(Agreement_rec.task_id,0);
779
780 -- Unset the global fund baseline flag back to 'N'
781 PA_Funding_Core.G_FUND_BASELINE_FLAG := 'N';
782
783 END LOOP;
784
785 END IF;
786 -- End of FP_M changes
787 -----------------------------------------------------------------------
788
789 /* Release the revenue hold from the realized gains and losses events */
790
791 UPDATE pa_events evt
792 SET evt.revenue_hold_flag ='N'
793 WHERE evt.project_id = x_project_id
794 AND evt.project_funding_id IS NOT NULL;
795
796 -- update project level funding flag
797 update pa_projects_all
798 set project_level_funding_flag = decode(x_funding_level,'P','Y','N'),
799 last_update_date = SYSDATE,
800 last_updated_by = x_created_by,
801 last_update_login = x_last_update_login
802 where project_id = x_project_id;
803
804 x_err_stack := old_stack;
805 return;
806
807 exception
808 when NO_DATA_FOUND then
809 if non_contract = 0 then
810 x_funding_level := l_funding_level; -- NOCOPY
811 x_err_code := sqlcode;
812 return;
813 end if;
814
815 when others then
816 x_funding_level := l_funding_level; -- NOCOPY
817 x_err_code := SQLCODE;
818 return;
819
820 end update_funding;
821
822 -----------------------------------------------------------------------------
823 --
824 -- History
825 -- 10-SEP-97 jwhite Added code to
826 -- address multiple messaging
827 --
828
829 procedure check_funding_level(
830 x_project_id in number,
831 x_funding_level in out NOCOPY varchar2, --File.Sql.39 bug 4440895
832 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
833 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
834 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
835 is
836
837 cursor proj is
838 select 1
839 from pa_summary_project_fundings
840 where project_id = x_project_id
841 and task_id is null
842 and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
843
844 cursor task is
845 select 1
846 from pa_summary_project_fundings
847 where project_id = x_project_id
848 and task_id is not null
849 and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
850
851 cursor proj_zero is
852
853 select 1 from pa_project_fundings
854 where project_id = x_project_id
855 and task_id is null
856 having sum(allocated_amount) = 0;
857
861 and task_id is not null
858 cursor task_zero is
859 select 1 from pa_project_fundings
860 where project_id = x_project_id
862 having sum(allocated_amount) = 0;
863
864 -- Added cursors project_negation and task_negation for bug 1286536
865 cursor project_negation is
866 select 1 from pa_summary_project_fundings
867 where project_id = x_project_id
868 and task_id is null
869 and total_unbaselined_amount = nvl(total_baselined_amount,0)*(-1)
870 and total_unbaselined_amount <> 0;
871
872 cursor task_negation is
873 select 1 from pa_summary_project_fundings
874 where project_id = x_project_id
875 and task_id is not null
876 and total_unbaselined_amount = nvl(total_baselined_amount,0)*(-1)
877 and total_unbaselined_amount <> 0;
878
879 old_stack varchar2(630);
880 proj_rec number;
881 task_rec number;
882 proj_zero_rec number;
883 task_zero_rec number;
884
885 /* Added recs project_negation_rec and task_negation_rec for bug 1286536 */
886 project_negation_rec number;
887 task_negation_rec number;
888 l_funding_level varchar2(1) := x_funding_level;
889
890 begin
891
892 x_err_code := 0;
893 old_stack := x_err_stack;
894 x_err_stack := 'pa_billing_core->check_funding_level';
895
896 -- set project level funding flag
897 x_err_stage := 'Check project level funding flag <'
898 || to_char(x_project_id) || '>';
899
900 open proj;
901 fetch proj into proj_rec;
902 if proj%notfound then
903
904 close proj;
905 open task;
906 fetch task into task_rec;
907 if task%notfound then
908
909 close task;
910 open proj_zero;
911 open task_zero;
912 fetch proj_zero into proj_zero_rec;
913 fetch task_zero into task_zero_rec;
914
915 if proj_zero%found and task_zero%found then
916 -- Begin - Addition for bug 1286536
917 open project_negation;
918 open task_negation;
919 fetch project_negation into project_negation_rec;
920 fetch task_negation into task_negation_rec;
921
922 if project_negation%found then
923 x_funding_level := 'P';
924 elsif task_negation%found then
925 x_funding_level := 'T';
926 else
927 -- End- Addition for bug 1286536
928 x_err_code := 30;
929 x_err_stage := 'PA_ZERO_PROJ_TASK_DRAFT';
930
931 -- 10-SEP-97, jwhite: multiple messaging
932 PA_UTILS.Add_Message
933 ( p_app_short_name => 'PA'
934 , p_msg_name => x_err_stage
935 );
936 end if;
937 close project_negation; /* Added for bug 1286536 */
938 close task_negation; /* Added for bug 1286536 */
939
940 elsif proj_zero%found then
941 x_funding_level := 'P';
942
943 elsif task_zero%found then
944 x_funding_level := 'T';
945
946 else
947 -- No positive or zero funding for this project
948 x_err_code := 20;
949 x_err_stage := 'PA_BU_NO_PROJECT_FUNDING';
950
951 -- 10-SEP-97, jwhite: multiple messaging
952 PA_UTILS.Add_Message
953 ( p_app_short_name => 'PA'
954 , p_msg_name => x_err_stage
955 );
956
957
958 end if;
959
960 close proj_zero;
961 close task_zero;
962
963 else
964 x_funding_level := 'T';
965 close task;
966
967 end if;
968
969 else
970 -- x_funding_level := 'P'; -- FP_M changes
971 -- If the Project is implemented with Top Task Customer enabled or
972 -- implemented with Top Task invoice method flag then
973 -- the default fundling level should be Task level funding
974 BEGIN
975 Select 'T' INTO x_funding_level
976 FROM PA_Projects_All
977 Where Project_ID = X_Project_ID
978 AND (ENABLE_TOP_TASK_CUSTOMER_FLAG = 'Y' OR
979 ENABLE_TOP_TASK_INV_MTH_FLAG = 'Y' );
980 Exception When NO_Data_Found then
981 x_funding_level := 'P';
982 END;
983 close proj;
984
985 end if;
986
987 exception
988 when others then
989 x_err_code := sqlcode;
990 x_funding_level := l_funding_level;
991 -- 10-SEP-97, jwhite: multiple messaging
992 FND_MSG_PUB.Add_Exc_Msg
993 ( p_pkg_name => 'PA_BILLING_CORE'
994 , p_procedure_name => 'CHECK_FUNDING_LEVEL'
995 , p_error_text => 'ORA-'||LPAD(substr(SQLCODE,2),5,'0')
996 );
997 return;
998
999 end check_funding_level;
1000
1001 -----------------------------------------------------------------------------
1002
1003 /* Modified to include MCB2 columns
1004 owning_organization_id, agreement_currency_code */
1005 procedure copy_agreement(
1006 x_orig_project_id in number,
1007 x_new_project_id in number,
1008 x_customer_id in number,
1009 x_owning_organization_id in number default null,
1010 x_agreement_currency_code in varchar2 default null,
1011 x_amount in number default null,
1012 x_template_flag in varchar2,
1016 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
1013 x_delta in number,
1014 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1015 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1017 is
1018 old_stack varchar2(630);
1019 x_agreement_id number;
1020 l_funding_level varchar2(1);
1021 l_amount number;
1022
1023 l_fund_exists varchar2(1) := 'N';
1024 l_err_code NUMBER := null;
1025 l_err_stage varchar2(250) := null;
1026 l_err_stack varchar2(250) := null;
1027 l_funding_count NUMBER := 0;
1028
1029 begin
1030
1031 x_err_code := 0;
1032 old_stack := x_err_stack;
1033 x_err_stack := 'pa_billing_core->copy_agreement';
1034
1035 x_err_stage := 'check proj/task level funding ';
1036
1037
1038 /* Check if any funding exists at all */
1039
1040 begin
1041 select 'Y' into l_fund_exists
1042 from pa_summary_project_fundings
1043 where project_id = x_orig_project_id
1044 and nvl(total_baselined_amount, 0) + total_unbaselined_amount > 0;
1045
1046 exception
1047
1048 when no_data_found then
1049
1050 l_fund_exists := 'N';
1051 when others then null;
1052 end;
1053
1054
1055 if l_fund_exists = 'N' then
1056
1057 return;
1058
1059 end if;
1060
1061 /* MCB2 code begins check proj/task level funding */
1062
1063 check_funding_level(
1064 x_project_id => x_orig_project_id ,
1065 x_funding_level => l_funding_level ,
1066 x_err_code => l_err_code,
1067 x_err_stage => l_err_stage,
1068 x_err_stack => l_err_stack );
1069
1070
1071 r_amount := NULL; /* Added for bug 2913524 */
1072
1073 if l_err_code = 0 then
1074
1075 if l_funding_level = 'P' THEN
1076 l_amount := x_amount; /* if proj level funding use input amount */
1077
1078 /* Code fix for bug 2913524 starts here*/
1079
1080 r_amount := x_amount;
1081
1082 SELECT count(*)
1083 INTO l_funding_count
1084 FROM pa_project_fundings
1085 WHERE project_id = x_orig_project_id;
1086
1087 /* Code fix for bug 2913524 ends here*/
1088 elsif l_funding_level = 'T' THEN
1089 l_amount := null; /* if task level funding use amount from template project */
1090 end if;
1091 else
1092
1093 if (l_err_code <> 20 AND l_err_code <> 30) then
1094
1095 x_err_code := l_err_code;
1096 x_err_stage := l_err_stage;
1097 x_err_stack := l_err_stack ;
1098
1099 end if;
1100
1101 return;
1102
1103 end if;
1104
1105
1106 /* MCB2 code ends */
1107
1108 x_err_stage := 'Get next agreement id.';
1109
1110 select pa_agreements_s.nextval
1111 into x_agreement_id
1112 from dual;
1113
1114 -- dbms_output.put_line('x_agreement_id = '||to_char(x_agreement_id));
1115 x_err_stage := 'Insert into pa_agreements.';
1116
1117 INSERT INTO PA_AGREEMENTS_ALL(
1118 agreement_id,
1119 customer_id,
1120 agreement_num,
1121 agreement_type,
1122 last_update_date,
1123 last_updated_by,
1124 creation_date,
1125 created_by,
1126 last_update_login,
1127 owned_by_person_id,
1128 term_id,
1129 revenue_limit_flag,
1130 amount,
1131 description,
1132 expiration_date,
1133 attribute_category,
1134 attribute1,
1135 attribute2,
1136 attribute3,
1137 attribute4,
1138 attribute5,
1139 attribute6,
1140 attribute7,
1141 attribute8,
1142 attribute9,
1143 attribute10,
1144 template_flag,
1145 agreement_currency_code, /* MCB2 column begins */
1146 owning_organization_id,
1147 invoice_limit_flag, /* MCB2 column ends */
1148 org_id)
1149 SELECT x_agreement_id,
1150 nvl(x_customer_id, a.customer_id),
1151 p.segment1,
1152 a.agreement_type,
1153 sysdate,
1154 fnd_global.user_id,
1155 sysdate,
1156 fnd_global.user_id,
1157 fnd_global.login_id,
1158 a.owned_by_person_id,
1159 a.term_id,
1160 a.revenue_limit_flag,
1161 nvl(l_amount, a.amount), /* MCB2 change */
1162 a.description,
1163 decode(a.expiration_date, null, null,
1164 a.expiration_date + nvl(x_delta, 0)),
1165 a.attribute_category,
1166 a.attribute1,
1167 a.attribute2,
1168 a.attribute3,
1169 a.attribute4,
1170 a.attribute5,
1171 a.attribute6,
1172 a.attribute7,
1173 a.attribute8,
1174 a.attribute9,
1175 a.attribute10,
1176 x_template_flag,
1177 /* MCB2 columns begin */
1178 nvl(x_agreement_currency_code,a.agreement_currency_code),
1179 nvl(x_owning_organization_id,a.owning_organization_id),
1180 a.invoice_limit_flag,
1181 mo_global.get_current_org_id
1182 /* MCB2 columns end */
1183 FROM pa_agreements_all a, pa_projects_all p
1184 WHERE p.project_id = x_new_project_id
1185 /* Bug 727421 Performance Issue
1186 AND exists
1187 (select null
1188 from pa_project_customers c2, pa_project_fundings f
1189 where f.project_id = x_orig_project_id
1190 and f.agreement_id = a.agreement_id
1191 and c2.project_id = x_orig_project_id
1192 and c2.customer_bill_split = 100
1193 and c2.customer_id = a.customer_id);
1194 */
1195 AND A.agreement_id IN
1196 (SELECT F.agreement_id from pa_summary_project_fundings F
1197 WHERE F.project_id = x_orig_project_id)
1198 AND exists
1199 (select null
1200 from pa_project_customers c2
1201 where c2.project_id = x_orig_project_id
1202 -- and nvl(c2.customer_bill_split,0) = 100
1203 -- FP_M changes
1204 -- If the project is implemented with Top Task Customer then
1205 -- assume the bill split is 100%
1206 and nvl(c2.customer_bill_split,0) =
1207 Decode(c2.Default_Top_Task_Cust_Flag, 'Y', 0, 100)
1208 and c2.customer_id = A.customer_id);
1209
1210 x_err_stage := 'Call pa_billing_core.copy_funding.';
1211
1212 /* Code fix for bug 2913524 Starts Here */
1213 IF (l_funding_level = 'P' AND x_amount IS NOT NULL AND l_funding_count <> 1 ) THEN
1214 NULL;
1215 ELSE
1216 /* Code fix for bug 2913524 Ends Here */
1217 pa_billing_core.copy_funding(
1218 x_orig_project_id,
1219 x_new_project_id,
1220 x_agreement_id,
1221 x_delta,
1222 x_err_code,
1223 x_err_stage,
1224 x_err_stack);
1225 END IF; -- Added for bug 2913524
1226
1227 if x_err_code <> 0 then
1228 return;
1229 end if;
1230
1231 x_err_stack := old_stack;
1232
1233 exception
1234 when NO_DATA_FOUND then
1235 x_err_code := 100;
1236 return;
1237
1238 when others then
1239 x_err_code := sqlcode;
1240 return;
1241
1242 end copy_agreement;
1243
1244 -----------------------------------------------------------------------------
1245
1246 procedure copy_funding(
1247 x_orig_project_id in number,
1248 x_new_project_id in number,
1249 x_agreement_id in number,
1250 x_delta in number,
1251 x_err_code in out NOCOPY number, --File.Sql.39 bug 4440895
1252 x_err_stage in out NOCOPY varchar2, --File.Sql.39 bug 4440895
1253 x_err_stack in out NOCOPY varchar2) --File.Sql.39 bug 4440895
1254 is
1255 old_stack varchar2(630);
1256 proj number := 0;
1257
1258 /* MCB2 code begins */
1259 l_funding_currency_code VARCHAR2(15);
1260 l_allocated_amount number;
1261
1262 l_project_currency_code VARCHAR2(15);
1263 l_project_rate_type VARCHAR2(30);
1264 l_project_rate_date DATE;
1265 l_project_exchange_rate NUMBER;
1266 l_project_allocated_amount NUMBER;
1267
1268 l_projfunc_currency_code VARCHAR2(15);
1269 l_projfunc_rate_type VARCHAR2(30);
1270 l_projfunc_rate_date DATE;
1271 l_projfunc_exchange_rate NUMBER;
1272 l_projfunc_allocated_amount NUMBER;
1273
1274 l_invproc_currency_code VARCHAR2(15);
1275 l_invproc_rate_type VARCHAR2(30);
1276 l_invproc_rate_date DATE;
1277 l_invproc_exchange_rate NUMBER;
1278 l_invproc_allocated_amount NUMBER;
1279
1280 l_revproc_currency_code VARCHAR2(15);
1281 l_revproc_rate_type VARCHAR2(30);
1282 l_revproc_rate_date DATE;
1283 l_revproc_exchange_rate NUMBER;
1284 l_revproc_allocated_amount NUMBER;
1285
1286 l_err_stage VARCHAR2(50);
1287 l_err_code NUMBER;
1288
1289 /* MCB2 code ends */
1290
1291 /* Commented for bug 5140179
1292 cursor c is
1293 select 1
1294 from pa_project_fundings
1295 where project_id = x_orig_project_id
1296 and task_id is null;*/
1297
1298 /* MCB2 code begins */
1299 cursor tmp_proj_funding is
1300 SELECT pa_project_fundings_s.nextval project_funding_id ,
1301 sysdate last_update_date, fnd_global.user_id last_updated_by,
1302 sysdate creation_date, fnd_global.user_id created_by,
1303 fnd_global.login_id last_update_login,
1304 x_agreement_id agreement_id, x_new_project_id project_id,
1305 NULL task_id, 'DRAFT' budget_type_code,
1306 /* l_allocated_amount allocated_amount, Commented code for bug 2793120 */
1307 NVL(r_amount,f.allocated_amount) allocated_amount, /* Added for bug 2793120 */ /* Modified for bug 2913524 */
1308 f.date_allocated + nvl(x_delta, 0) date_allocated,
1309 f.attribute_category attribute_category,
1310 f.attribute1 attribute1,
1311 f.attribute2 attribute2,
1312 f.attribute3 attribute3,
1313 f.attribute4 attribute4,
1314 f.attribute5 attribute5,
1315 f.attribute6 attribute6,
1316 f.attribute7 attribute7,
1317 f.attribute8 attribute8,
1318 f.attribute9 attribute9,
1319 f.attribute10 attribute10,
1320 l_funding_currency_code funding_currency_code,
1321 f.funding_category /* For Bug 2244796 */
1322 FROM pa_project_fundings f
1323 WHERE f.project_id = x_orig_project_id
1324 AND f.task_id is null ;/*Added for bug 5140179*/
1325 /*
1326 AND exists(
1327 select null
1328 from pa_agreements
1329 where agreement_id = x_agreement_id);
1330 */
1331
1332 proj_rec tmp_proj_funding%ROWTYPE;
1333
1334 cursor tmp_task_funding is
1335 SELECT pa_project_fundings_s.nextval project_funding_id ,
1336 sysdate last_update_date, fnd_global.user_id last_updated_by,
1337 sysdate creation_date, fnd_global.user_id created_by,
1338 fnd_global.login_id last_update_login,
1339 x_agreement_id agreement_id, x_new_project_id project_id,
1340 t2.task_id task_id, 'DRAFT' budget_type_code,
1341 f.allocated_amount allocated_amount,
1342 f.date_allocated + nvl(x_delta, 0) date_allocated,
1343 f.attribute_category attribute_category,
1344 f.attribute1 attribute1,
1345 f.attribute2 attribute2,
1346 f.attribute3 attribute3,
1347 f.attribute4 attribute4,
1348 f.attribute5 attribute5,
1349 f.attribute6 attribute6,
1350 f.attribute7 attribute7,
1351 f.attribute8 attribute8,
1352 f.attribute9 attribute9,
1353 f.attribute10 attribute10,
1354 l_funding_currency_code funding_currency_code,
1355 f.funding_category /* For Bug 2244796 */
1356 FROM pa_tasks t2, pa_tasks t, pa_project_fundings f
1357 WHERE f.project_id = x_orig_project_id
1358 AND t.project_id = f.project_id
1359 AND t.task_id = f.task_id
1360 AND t2.task_number = t.task_number
1361 AND t2.project_id = x_new_project_id;
1362 /*
1363 AND exists(
1364 select null
1365 from pa_agreements
1366 where agreement_id = x_agreement_id);
1367 */
1368 task_rec tmp_task_funding%ROWTYPE;
1369
1370 /* MCB2 code ends */
1371 begin
1372
1373 old_stack := x_err_stack;
1374 x_err_stack := 'pa_billing_core->copy_funding';
1375
1376 x_err_code := 0;
1377 x_err_stage := 'Get agreement currency ';
1378
1379 /* dbms_output.put_line(' in copy fund x_agreement_id = '||
1380 to_char(x_agreement_id));
1381 */
1382
1383 select agreement_currency_code, amount
1384 into l_funding_currency_code, l_allocated_amount
1385 from pa_agreements_all
1386 where agreement_id = x_agreement_id;
1387
1388 /*
1389 dbms_output.put_line(' in copy fund agreement_curr = '||
1390 l_funding_currency_code);
1391 */
1392
1393 /* Commented for bug 5140179
1394 open c;
1395 fetch c into proj;
1396 close c;
1397
1398 if proj = 1 then */
1399
1400 x_err_stage := 'Get funding template values PROJ ';
1401
1402 OPEN tmp_proj_funding;
1403
1404 LOOP
1405 FETCH tmp_proj_funding into proj_rec;
1406 EXIT when tmp_proj_funding%NOTFOUND or x_err_code <> 0;
1407
1408 x_err_stage := 'Get values for MCB2 columns ';
1409
1410 pa_funding_core.get_MCB2_attributes(
1411 p_project_id => proj_rec.project_id,
1412 p_agreement_id => proj_rec.agreement_id,
1413 p_date_allocated => proj_rec.date_allocated,
1414 p_allocated_amount => proj_rec.allocated_amount,
1415 p_funding_currency_code => proj_rec.funding_currency_code,
1416 p_project_currency_code => l_project_currency_code,
1417 p_project_rate_type => l_project_rate_type,
1418 p_project_rate_date => l_project_rate_date,
1419 p_project_exchange_rate => l_project_exchange_rate,
1420 p_project_allocated_amount => l_project_allocated_amount,
1421 p_projfunc_currency_code => l_projfunc_currency_code,
1422 p_projfunc_rate_type => l_projfunc_rate_type,
1423 p_projfunc_rate_date => l_projfunc_rate_date,
1424 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
1425 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
1426 p_invproc_currency_code => l_invproc_currency_code,
1427 p_invproc_rate_type => l_invproc_rate_type,
1428 p_invproc_rate_date => l_invproc_rate_date,
1429 p_invproc_exchange_rate => l_invproc_exchange_rate,
1430 p_invproc_allocated_amount => l_invproc_allocated_amount,
1431 p_revproc_currency_code => l_revproc_currency_code,
1432 p_revproc_rate_type => l_revproc_rate_type,
1433 p_revproc_rate_date => l_revproc_rate_date,
1434 p_revproc_exchange_rate => l_revproc_exchange_rate,
1435 p_revproc_allocated_amount => l_revproc_allocated_amount,
1436 p_validate_parameters => 'N',
1437 x_err_code => l_err_code,
1438 x_err_msg => l_err_stage
1439 );
1440
1441 x_err_code := l_err_code;
1442 x_err_stage := l_err_stage;
1443
1444 if x_err_code = 0 then
1445
1446 x_err_stage := 'Insert into pa_project_fundings';
1447
1448 INSERT INTO pa_project_fundings(
1449 project_funding_id,
1450 last_update_date,
1451 last_updated_by,
1452 creation_date,
1453 created_by,
1454 last_update_login,
1455 agreement_id,
1456 project_id,
1457 task_id,
1458 budget_type_code,
1459 allocated_amount,
1460 date_allocated,
1461 attribute_category,
1462 attribute1,
1463 attribute2,
1464 attribute3,
1465 attribute4,
1466 attribute5,
1467 attribute6,
1468 attribute7,
1469 attribute8,
1470 attribute9,
1471 attribute10,
1472 funding_currency_code,
1473 project_currency_code,
1474 project_rate_type,
1475 project_rate_date,
1476 project_exchange_rate,
1477 project_allocated_amount,
1478 projfunc_currency_code,
1479 projfunc_rate_type,
1480 projfunc_rate_date,
1481 projfunc_exchange_rate,
1482 projfunc_allocated_amount,
1483 invproc_currency_code,
1484 invproc_rate_type,
1485 invproc_rate_date,
1486 invproc_exchange_rate,
1487 invproc_allocated_amount,
1488 revproc_currency_code,
1489 revproc_rate_type,
1490 revproc_rate_date,
1491 revproc_exchange_rate,
1492 revproc_allocated_amount,
1493 funding_category /* For Bug2244796 */
1494 )
1495 VALUES
1496 ( proj_rec.project_funding_id,
1497 proj_rec.last_update_date,
1498 proj_rec.last_updated_by,
1499 proj_rec.creation_date,
1500 proj_rec.created_by,
1504 proj_rec.task_id,
1501 proj_rec.last_update_login,
1502 proj_rec.agreement_id,
1503 proj_rec.project_id,
1505 proj_rec.budget_type_code,
1506 proj_rec.allocated_amount,
1507 proj_rec.date_allocated,
1508 proj_rec.attribute_category,
1509 proj_rec.attribute1,
1510 proj_rec.attribute2,
1511 proj_rec.attribute3,
1512 proj_rec.attribute4,
1513 proj_rec.attribute5,
1514 proj_rec.attribute6,
1515 proj_rec.attribute7,
1516 proj_rec.attribute8,
1517 proj_rec.attribute9,
1518 proj_rec.attribute10,
1519 proj_rec.funding_currency_code,
1520 l_project_currency_code,
1521 l_project_rate_type,
1522 l_project_rate_date,
1523 l_project_exchange_rate,
1524 l_project_allocated_amount,
1525 l_projfunc_currency_code,
1526 l_projfunc_rate_type,
1527 l_projfunc_rate_date,
1528 l_projfunc_exchange_rate,
1529 l_projfunc_allocated_amount,
1530 l_invproc_currency_code,
1531 l_invproc_rate_type,
1532 l_invproc_rate_date,
1533 l_invproc_exchange_rate,
1534 l_invproc_allocated_amount,
1535 l_revproc_currency_code,
1536 l_revproc_rate_type,
1537 l_revproc_rate_date,
1538 l_revproc_exchange_rate,
1539 l_revproc_allocated_amount,
1540 proj_rec.funding_category /* For Bug 2244796 */
1541 );
1542
1543 end if;
1544
1545 END LOOP;
1546 CLOSE tmp_proj_funding;
1547
1548 /* else-- Task level funding Commented for bug 5140179*/
1549
1550 x_err_stage := 'Get funding template values TASK ';
1551
1552 OPEN tmp_task_funding;
1553
1554 LOOP
1555
1556 FETCH tmp_task_funding into task_rec;
1557 EXIT when tmp_task_funding%NOTFOUND or x_err_code <> 0;
1558
1559 x_err_stage := 'Get values for MCB2 columns ';
1560
1561 pa_funding_core.get_MCB2_attributes(
1562 p_project_id => task_rec.project_id,
1563 p_agreement_id => task_rec.agreement_id,
1564 p_date_allocated => task_rec.date_allocated,
1565 p_allocated_amount => task_rec.allocated_amount,
1566 p_funding_currency_code => task_rec.funding_currency_code,
1567 p_project_currency_code => l_project_currency_code,
1568 p_project_rate_type => l_project_rate_type,
1569 p_project_rate_date => l_project_rate_date,
1570 p_project_exchange_rate => l_project_exchange_rate,
1571 p_project_allocated_amount => l_project_allocated_amount,
1572 p_projfunc_currency_code => l_projfunc_currency_code,
1573 p_projfunc_rate_type => l_projfunc_rate_type,
1574 p_projfunc_rate_date => l_projfunc_rate_date,
1575 p_projfunc_exchange_rate => l_projfunc_exchange_rate,
1576 p_projfunc_allocated_amount => l_projfunc_allocated_amount,
1577 p_invproc_currency_code => l_invproc_currency_code,
1578 p_invproc_rate_type => l_invproc_rate_type,
1579 p_invproc_rate_date => l_invproc_rate_date,
1580 p_invproc_exchange_rate => l_invproc_exchange_rate,
1581 p_invproc_allocated_amount => l_invproc_allocated_amount,
1582 p_revproc_currency_code => l_revproc_currency_code,
1583 p_revproc_rate_type => l_revproc_rate_type,
1584 p_revproc_rate_date => l_revproc_rate_date,
1585 p_revproc_exchange_rate => l_revproc_exchange_rate,
1586 p_revproc_allocated_amount => l_revproc_allocated_amount,
1587 p_validate_parameters => 'N',
1588 x_err_code => l_err_code,
1589 x_err_msg => l_err_stage
1590 );
1591
1592 x_err_code := l_err_code;
1593 x_err_stage := l_err_stage;
1594
1595 if x_err_code = 0 then
1596
1597 x_err_stage := 'Insert into pa_project_fundings';
1598
1599 INSERT INTO pa_project_fundings(
1600 project_funding_id,
1601 last_update_date,
1602 last_updated_by,
1603 creation_date,
1604 created_by,
1605 last_update_login,
1606 agreement_id,
1607 project_id,
1608 task_id,
1609 budget_type_code,
1610 allocated_amount,
1611 date_allocated,
1612 attribute_category,
1613 attribute1,
1614 attribute2,
1615 attribute3,
1616 attribute4,
1617 attribute5,
1618 attribute6,
1619 attribute7,
1620 attribute8,
1621 attribute9,
1622 attribute10,
1623 funding_currency_code,
1624 project_currency_code,
1625 project_rate_type,
1626 project_rate_date,
1630 projfunc_rate_type,
1627 project_exchange_rate,
1628 project_allocated_amount,
1629 projfunc_currency_code,
1631 projfunc_rate_date,
1632 projfunc_exchange_rate,
1633 projfunc_allocated_amount,
1634 invproc_currency_code,
1635 invproc_rate_type,
1636 invproc_rate_date,
1637 invproc_exchange_rate,
1638 invproc_allocated_amount,
1639 revproc_currency_code,
1640 revproc_rate_type,
1641 revproc_rate_date,
1642 revproc_exchange_rate,
1643 revproc_allocated_amount,
1644 funding_category /* For Bug2244796 */
1645 )
1646 VALUES
1647 ( task_rec.project_funding_id,
1648 task_rec.last_update_date,
1649 task_rec.last_updated_by,
1650 task_rec.creation_date,
1651 task_rec.created_by,
1652 task_rec.last_update_login,
1653 task_rec.agreement_id,
1654 task_rec.project_id,
1655 task_rec.task_id,
1656 task_rec.budget_type_code,
1657 task_rec.allocated_amount,
1658 task_rec.date_allocated,
1659 task_rec.attribute_category,
1660 task_rec.attribute1,
1661 task_rec.attribute2,
1662 task_rec.attribute3,
1663 task_rec.attribute4,
1664 task_rec.attribute5,
1665 task_rec.attribute6,
1666 task_rec.attribute7,
1667 task_rec.attribute8,
1668 task_rec.attribute9,
1669 task_rec.attribute10,
1670 task_rec.funding_currency_code,
1671 l_project_currency_code,
1672 l_project_rate_type,
1673 l_project_rate_date,
1674 l_project_exchange_rate,
1675 l_project_allocated_amount,
1676 l_projfunc_currency_code,
1677 l_projfunc_rate_type,
1678 l_projfunc_rate_date,
1679 l_projfunc_exchange_rate,
1680 l_projfunc_allocated_amount,
1681 l_invproc_currency_code,
1682 l_invproc_rate_type,
1683 l_invproc_rate_date,
1684 l_invproc_exchange_rate,
1685 l_invproc_allocated_amount,
1686 l_revproc_currency_code,
1687 l_revproc_rate_type,
1688 l_revproc_rate_date,
1689 l_revproc_exchange_rate,
1690 l_revproc_allocated_amount,
1691 task_rec.funding_category /* For Bug 2244796 */
1692 );
1693
1694 end if;
1695
1696 END LOOP;
1697 CLOSE tmp_task_funding;
1698
1699 /*end if; Commented for bug 5140179*/
1700
1701 if x_err_code = 0 then
1702
1703 x_err_stage := 'Insert or update pa_summary_project_fundings';
1704
1705 INSERT INTO pa_summary_project_fundings(
1706 agreement_id,
1707 project_id,
1708 task_id,
1709 total_baselined_amount,
1710 total_unbaselined_amount,
1711 total_accrued_amount,
1712 total_billed_amount,
1713 last_update_login,
1714 last_update_date,
1715 last_updated_by,
1716 creation_date,
1717 created_by,
1718 funding_currency_code,
1719 project_currency_code, project_baselined_amount,
1720 project_unbaselined_amount, project_accrued_amount,
1721 project_billed_amount,
1722 projfunc_currency_code, projfunc_baselined_amount,
1723 projfunc_unbaselined_amount, projfunc_accrued_amount,
1724 projfunc_billed_amount,
1725 invproc_currency_code, invproc_baselined_amount,
1726 invproc_unbaselined_amount,
1727 invproc_billed_amount,
1728 revproc_currency_code, revproc_baselined_amount,
1729 revproc_unbaselined_amount, revproc_accrued_amount)
1730 SELECT agreement_id,
1731 project_id,
1732 task_id,
1733 0,
1734 nvl(sum(nvl(allocated_amount, 0)), 0),
1735 0, 0,
1736 fnd_global.login_id,
1737 sysdate,
1738 fnd_global.user_id,
1739 sysdate,
1740 fnd_global.user_id,
1741 funding_currency_code,
1742 project_currency_code,
1743 0,
1744 nvl(sum(nvl(project_allocated_amount, 0)), 0),
1745 0, 0,
1746 projfunc_currency_code,
1747 0,
1748 nvl(sum(nvl(projfunc_allocated_amount, 0)), 0),
1749 0, 0,
1750 invproc_currency_code,
1751 0,
1752 nvl(sum(nvl(invproc_allocated_amount, 0)), 0),
1753 0,
1754 revproc_currency_code,
1755 0,
1756 nvl(sum(nvl(revproc_allocated_amount, 0)), 0),
1757 0
1758 FROM pa_project_fundings
1759 WHERE project_id = x_new_project_id
1760 GROUP BY agreement_id, project_id, task_id, funding_currency_code,
1761 project_currency_code, projfunc_currency_code,
1762 invproc_currency_code, revproc_currency_code ;
1763
1764 end if;
1765
1769 when others then
1766 x_err_stack := old_stack;
1767
1768 exception
1770 -- dbms_output.put_line ( 'in copy funding' || x_err_stage);
1771 x_err_code := sqlcode;
1772 x_err_stage := sqlerrm;
1773 return;
1774
1775 end copy_funding;
1776 -----------------------------------------------------------------------------
1777 --
1778 -- History
1779 -- 10-Nov-02 johnson Added code
1780 -- This function to check the funding exists or not
1781 -- while create projects
1782 -- This will be called only if project creation creates agreements
1783
1784 Function check_funding_exists( x_project_id in number) return varchar2 IS
1785
1786 l_fund_exists varchar2(2):='N';
1787
1788 BEGIN
1789
1790 BEGIN
1791 select 'Y' into l_fund_exists
1792 from dual
1793 where exists(select null
1794 from pa_summary_project_fundings spf
1795 where spf.project_id = x_project_id);
1796
1797 EXCEPTION
1798 WHEN no_data_found THEN
1799 l_fund_exists := 'N';
1800
1801 WHEN others THEN
1802 l_fund_exists := 'N';
1803
1804 END;
1805
1806 RETURN l_fund_exists;
1807
1808 END check_funding_exists;
1809
1810 -- These changes are made for FP_M
1811 -- Function to check whether Top Task Customer Flag at project level
1812 -- can be updateable
1813 Function Update_Top_Task_Cust_Flag (
1814 P_Project_ID IN Number
1815 ) Return Varchar2 IS
1816 l_Exist_Flag VARCHAR2(1);
1817 l_Funding_Level_Flag VARCHAR2(1);
1818 l_Check_Next_Condition VARCHAR2(1) :='N'; /*bug 3638361 */
1819
1820 BEGIN
1821 -- Get the funding level value
1822 BEGIN
1823 Select Project_Level_Funding_Flag
1824 INTO l_Funding_Level_Flag
1825 FROM PA_Projects_All
1826 Where Project_ID = P_Project_ID;
1827 Exception When NO_Data_Found then
1828 l_Funding_Level_Flag := 'P';
1829 END;
1830
1831 begin
1832 Select 'N'
1833 Into l_Exist_Flag
1834 from dual
1835 where exists ( select null
1836 From PA_Project_Fundings
1837 Where Project_ID = P_Project_ID
1838 and budget_type_code = 'DRAFT');
1839
1840 Return l_Exist_Flag;
1841
1842 Exception
1843 When Others then
1844 l_Check_Next_Condition := 'Y';
1845 end;
1846
1847 begin
1848 Select 'N'
1849 Into l_Exist_Flag
1850 From PA_summary_Project_Fundings
1851 Where Project_ID = P_Project_ID
1852 HAVING ( sum(nvl(Total_Baselined_Amount,0)) > 0 OR sum(nvl(Total_Unbaselined_Amount,0)) > 0 );
1853
1854 Return l_Exist_Flag;
1855
1856 Exception
1857 When Others then
1858 l_Check_Next_Condition := 'Y';
1859 end;
1860
1861 -- If the project funding level is "Project".
1862 If l_Check_Next_Condition = 'Y' AND l_Funding_Level_Flag = 'P' Then
1863 Return 'N';
1864 Else
1865 Return 'Y';
1866 END IF;
1867
1868 END Update_Top_Task_Cust_Flag;
1869
1870 -- Function to check whether Top Task Invoice Method Flag at project level
1871 -- can be updateable
1872 Function Update_Top_Task_Inv_Mthd_Flag (
1873 P_Project_ID IN Number
1874 ) Return Varchar2
1875 IS
1876 l_Exist_Flag VARCHAR2(1);
1877 l_Funding_Level_Flag VARCHAR2(1);
1878 l_Check_Next_Condition VARCHAR2(1) :='N'; /*bug 3638361 */
1879 BEGIN
1880 -- Get the funding level value
1881 BEGIN
1882
1883 Select Project_Level_Funding_Flag
1884 INTO l_Funding_Level_Flag
1885 FROM PA_Projects_All
1886 Where Project_ID = P_Project_ID;
1887
1888 Exception When NO_Data_Found then
1889 l_Funding_Level_Flag := 'P';
1890 END;
1891
1892
1893 begin
1894 -- Case 1 : If it has any project level unbaselined funding
1895 Select 'N'
1896 Into l_Exist_Flag
1897 from dual
1898 where exists ( select null
1899 From PA_Project_Fundings
1900 Where Project_ID = P_Project_ID
1901 and Task_ID IS NULL
1902 and budget_type_code = 'DRAFT');
1903
1904 Return l_Exist_Flag;
1905
1906 Exception
1907 When Others then
1908 l_Check_Next_Condition := 'Y';
1909 end;
1910
1911
1912 -- Fix for bug 3601308
1913 -- Case 1 : If it has any project level funding (baselined or unbaselined)
1914 begin
1915 Select 'N'
1916 Into l_Exist_Flag
1917 From PA_Summary_Project_Fundings
1918 Where Project_ID = P_Project_ID
1919 and Task_ID IS NULL
1920 HAVING ( sum(Total_Baselined_Amount) > 0 OR sum(Total_Unbaselined_Amount) > 0 );
1921
1922 Return l_Exist_Flag;
1923
1924 Exception When Others then
1925 l_Check_Next_Condition := 'Y';
1926 end;
1927
1928
1929 -- Case 2 : If it has task level funding and it's billed
1930 IF l_Check_Next_Condition = 'Y' THEN
1931 begin
1932
1933 l_Check_Next_Condition := 'N'; --Added for bug3703094
1934 Select 'N'
1935 Into l_Exist_Flag
1936 From PA_Summary_Project_Fundings
1937 Where Project_ID = P_Project_ID
1941 Return l_Exist_Flag;
1938 and Task_ID IS NOT NULL
1939 HAVING sum(Total_Billed_Amount) > 0;
1940
1942
1943 Exception When Others then
1944 l_Check_Next_Condition := 'Y';
1945 end;
1946 END IF;
1947
1948 -- Case 3 : If the project funding level is "Project".
1949 If l_Check_Next_Condition = 'Y' AND l_Funding_Level_Flag = 'P' Then
1950 Return 'N';
1951 Else
1952 Return 'Y';
1953 END IF;
1954
1955 END Update_Top_Task_Inv_Mthd_Flag;
1956
1957 -- Function to check whether the combination of Invoice and Revenue
1958 -- methods are existing in Project Type distribution rules or not
1959 Function Check_Revenue_Invoice_Methods (
1960 P_Project_ID IN Number
1961 ) Return Varchar2
1962 IS
1963 l_Exist_Flag varchar2(1);
1964 BEGIN
1965
1966 begin
1967 /* Select 'N'
1968 Into l_Exist_Flag
1969 From PA_Summary_Project_Fundings
1970 Where Project_ID = P_Project_ID
1971 HAVING ( sum(Total_Billed_Amount) > 0 OR
1972 sum(Total_Accrued_Amount) > 0 ); --Added for Bug3729634 */
1973 /* commented above and added below for bug 8683074 */
1974 Select 'N'
1975 Into l_Exist_Flag
1976 From dual
1977 Where exists
1978 ( select 1 from pa_draft_revenue_items
1979 where project_id = P_Project_ID
1980 group by nvl(task_id,-99)
1981 having sum(Amount) <> 0
1982 Union all
1983 select 1 from pa_draft_invoice_items
1984 where project_id = P_Project_ID
1985 group by nvl(task_id,-99)
1986 having sum(Amount) <> 0 );
1987
1988 Exception When Others then
1989 l_Exist_Flag := 'Y';
1990 end;
1991 Return l_Exist_Flag;
1992
1993 END Check_Revenue_Invoice_Methods;
1994
1995 -- Check required at Top Task level
1996 -- Function to check whether Top Task Customer can be updateable
1997 -- at Task level window
1998 Function Update_Top_Task_Customer (
1999 P_Project_ID IN Number,
2000 P_Task_ID IN Number
2001 ) Return Varchar2
2002 IS
2003 l_Exist_Flag varchar2(1);
2004 BEGIN
2005
2006 begin
2007 Select 'N'
2008 Into l_Exist_Flag
2009 from dual
2010 where exists ( select null
2011 From PA_Project_Fundings
2012 Where Project_ID = P_Project_ID
2013 AND Task_ID = P_Task_ID
2014 and budget_type_code = 'DRAFT');
2015
2016 Return l_Exist_Flag;
2017
2018 Exception
2019 When Others then
2020 null;
2021 end;
2022
2023
2024 begin
2025 Select 'N'
2026 Into l_Exist_Flag
2027 From PA_Summary_Project_Fundings
2028 Where Project_ID = P_Project_ID
2029 AND Task_ID = P_Task_ID
2030 HAVING (sum(Total_Baselined_Amount) <> 0
2031 OR sum(Total_UnBaselined_Amount) <> 0
2032 OR sum(Total_Accrued_Amount)<>0 /* added for bug 7291160 */
2033 OR sum(Total_Billed_Amount)<>0 /* added for bug 7291160 */
2034 )
2035 ;
2036 Return l_Exist_Flag;
2037
2038 Exception When Others then
2039 NULL; /* Modified fix for bug 7437739 */
2040 end;
2041
2042 /* Fix for bug 7437739 starts here*/
2043 begin
2044 select 'N'
2045 into l_Exist_Flag
2046 from dual
2047 where exists (select 1
2048 from pa_expenditure_items_all ei, pa_tasks pt
2049 where ei.task_id = pt.task_id
2050 and pt.top_task_id = P_Task_ID
2051 and pt.project_id = P_Project_ID
2052 and (NVL(accrued_revenue,0) <> 0 or NVL(bill_amount,0) <> 0)
2053 and ei.net_zero_adjustment_flag = 'N');
2054
2055 Return l_Exist_Flag;
2056 Exception
2057 When Others then
2058 NULL;
2059 end;
2060
2061 begin
2062
2063 select 'N'
2064 into l_Exist_Flag
2065 from dual where exists (select 1
2066 from pa_expenditure_items_all ei, pa_tasks pt
2067 where ei.task_id = pt.task_id
2068 and pt.top_task_id = P_Task_ID
2069 and pt.project_id = P_Project_ID
2070 and (( NVL(ei.accrued_revenue,0) + NVL((select ei1.accrued_revenue
2071 from pa_expenditure_items_all ei1 where ei1.expenditure_item_id = ei.adjusted_expenditure_item_id),0) <> 0)
2072 or
2073 ( NVL(ei.bill_amount,0) + NVL((select ei2.bill_amount
2074 from pa_expenditure_items_all ei2 where ei2.expenditure_item_id = ei.adjusted_expenditure_item_id),0) <> 0))
2075 and ei.adjusted_expenditure_item_id IS NOT NULL
2076 and ei.net_zero_adjustment_flag = 'Y');
2077
2078 Return l_Exist_Flag;
2079
2080 Exception
2081 When Others then
2082 NULL;
2083 end;
2084
2085 /* Fix for bug 7437739 ends here*/
2086
2087 Return 'Y'; --Modified for bug 7437739
2088
2089
2090 END Update_Top_Task_Customer;
2091
2092 -- Function to check whether Top Task Invoice Method can be updateable
2093 -- at Task level window
2094 Function Update_Top_Task_Invoice_Method (
2095 P_Project_ID IN Number,
2096 P_Task_ID IN Number
2097 ) Return Varchar2
2098 IS
2099 l_Exist_Flag varchar2(1);
2100 BEGIN
2101 begin
2102 Select 'N'
2103 Into l_Exist_Flag
2104 From PA_Summary_Project_Fundings
2105 Where Project_ID = P_Project_ID
2109 l_Exist_Flag := 'Y';
2106 AND Task_ID = P_Task_ID
2107 HAVING sum(Total_Billed_Amount) > 0;
2108 Exception When Others then
2110 end;
2111 Return l_Exist_Flag;
2112
2113 END Update_Top_Task_Invoice_Method;
2114
2115 end pa_billing_core ;