[Home] [Help]
PACKAGE BODY: APPS.IGW_REPORT_PROCESSING
Source
1 PACKAGE BODY IGW_REPORT_PROCESSING as
2 -- $Header: igwburpb.pls 115.41 2002/11/15 00:47:57 ashkumar ship $
3 --------------------------------------------------------------------------------------------------
4 Function get_proposal_id RETURN NUMBER is
5 Begin
6 RETURN IGW_REPORT_PROCESSING.G_PROPOSAL_ID;
7 End;
8 --------------------------------------------------------------------------------------------------
9 Function get_period_id RETURN NUMBER is
10 Begin
11 RETURN IGW_REPORT_PROCESSING.G_START_PERIOD;
12 End;
13 --------------------------------------------------------------------------------------------------
14 Function get_version_id RETURN NUMBER is
15 Begin
16 RETURN IGW_REPORT_PROCESSING.G_VERSION_ID;
17 End;
18 --------------------------------------------------------------------------------------------------
19 --used in setup budget category hierarchy form
20 Function get_category(p_category_code VARCHAR2, p_proposal_form_number VARCHAR2)
21 RETURN VARCHAR2 is
22 x_category VARCHAR2(80);
23 Begin
24 select distinct proposal_budget_category
25 into x_category
26 from igw_report_budget_seed
27 where proposal_form_number = p_proposal_form_number
28 and proposal_budget_category_code = p_category_code;
29
30 RETURN x_category;
31 End;
32
33 --------------------------------------------------------------------------------------------------
34 Function get_period_total(p_budget_category_code VARCHAR2
35 , p_period_id NUMBER) RETURN NUMBER is
36 l_version_id number(4);
37 l_period_total number(15,2);
38 Begin
39 --G_PROPOSAL_ID
40 --G_PROPOSAL_FORM_NUMBER :=
41 l_version_id := get_final_version(G_proposal_id);
42
43
44 select period_total_direct_cost
45 into l_period_total
46 from igw_report_budget
47 where proposal_budget_category_code = p_budget_category_code
48 and proposal_form_number = G_PROPOSAL_FORM_NUMBER
49 and proposal_id = G_PROPOSAL_ID
50 and version_id = l_version_id
51 and budget_period_id = p_period_id;
52
53 RETURN l_period_total;
54 Exception
55 when no_data_found then
56 RETURN null;
57 End get_period_total;
58
59 --------------------------------------------------------------------------------------------------
60 FUNCTION get_final_version(p_proposal_id NUMBER) RETURN NUMBER is
61 l_version_id NUMBER(15);
62 Begin
63 select version_id
64 into l_version_id
65 from igw_budgets
66 where proposal_id = p_proposal_id
67 and final_version_flag = 'Y';
68
69 RETURN l_version_id;
70 Exception
71 when no_data_found then
72 fnd_message.set_name('IGW', 'IGW_NO_FINAL_BUDGET_VERSION');
73 fnd_msg_pub.add;
74 RETURN NULL;
75 raise FND_API.G_EXC_ERROR;
76 when others then
77 RETURN NULL;
78 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'GET_FINAL_VERSION');
79 End get_final_version;
80
81 --------------------------------------------------------------------------------------------------
82 Function get_award_role(p_role_code VARCHAR2) RETURN VARCHAR2 is
83 l_award_role VARCHAR2(80);
84 Begin
85 select meaning
86 into l_award_role
87 from fnd_lookups
88 where lookup_type = 'AWARD_ROLE'
89 and lookup_code = p_role_code;
90
91 RETURN l_award_role;
92 End;
93 --------------------------------------------------------------------------------------------------
94 Function get_proposal_role(p_role_code VARCHAR2) RETURN VARCHAR2 is
95 l_role VARCHAR2(80);
96 Begin
97 select meaning
98 into l_role
99 from fnd_lookups
100 where lookup_type = 'IGW_PROPOSAL_ROLE_TYPES'
101 and lookup_code = p_role_code;
102 RETURN l_role;
103 End;
104 --------------------------------------------------------------------------------------------------
105
106 PROCEDURE create_budget_justification (p_proposal_id NUMBER
107 ,p_proposal_form_number VARCHAR2
108 ,x_return_status OUT NOCOPY VARCHAR2
109 ,x_msg_data OUT NOCOPY VARCHAR2) is
110
111
112 l_proposal_budg_category_code VARCHAR2(30);
113 l_budget_category_code VARCHAR2(30);
114 l_version_id NUMBER(15);
115
116 cursor c_budget_category is
117 select distinct proposal_budget_category
118 , budget_category_code
119 , details_required_flag
120 from igw_report_budget_seed
121 where proposal_form_number = p_proposal_form_number
122 and budget_category_code NOT IN ('84','SB');
123
124 cursor c_itemized_budget is
125 select expenditure_type
126 , budget_justification
127 from igw_budget_line_category_v pbd
128 where pbd.proposal_id = p_proposal_id
129 and pbd.version_id = l_version_id
130 and pbd.budget_category_code =l_budget_category_code;
131
132 -- a new procedure is created for this
133 /*
134 cursor c_report_justification is
135 select proposal_budget_category
136 , justification
137 from igw_report_budg_justification
138 where proposal_id = p_proposal_id
139 and version_id = l_version_id
140 and proposal_form_number = p_proposal_form_number;
141 */
142
143 l_item_budget_justification LONG;
144 l_category_budget_just LONG;
145 l_proposal_budget_just LONG;
146
147 BEGIN
148 fnd_msg_pub.initialize;
149 l_version_id := get_final_version(p_proposal_id);
150 if l_version_id is null then
151 raise FND_API.G_EXC_ERROR;
152 end if;
153
154 --deleting previously precessed data
155 delete from igw_report_budg_justification
156 where p_proposal_form_number = p_proposal_form_number
157 and proposal_id = p_proposal_id
158 and version_id = l_version_id;
159
160 /*
161 --deleting inserted abstract into table igw_prop_abstracats
162 delete from igw_prop_abstracts
163 where proposal_id = p_proposal_id
164 and abstract_type_code = 'C.1'
165 and abstract_type = 'IGW_ABSTRACT_TYPES';
166 */
167
168
169 l_proposal_budget_just := null;
170 for rec_budget_category in c_budget_category
171 LOOP
172 l_budget_category_code := rec_budget_category.budget_category_code;
173 l_category_budget_just := null;
174
175 for rec_itemized_budget in c_itemized_budget
176 LOOP
177 if rec_itemized_budget.budget_justification is not null then
178 if c_itemized_budget%ROWCOUNT = 1 then
179 l_item_budget_justification := rec_itemized_budget.budget_justification||fnd_global.local_chr(10);
180 else
181 if l_item_budget_justification is not null then
182 l_item_budget_justification := l_item_budget_justification||fnd_global.local_chr(10)||rec_itemized_budget.budget_justification||fnd_global.local_chr(10);
183 else
184 l_item_budget_justification := rec_itemized_budget.budget_justification;
185 end if;
186 end if;
187 end if;
188 END LOOP; --rec_itemized_budget
189 if l_item_budget_justification is not null then
190 if c_budget_category%ROWCOUNT = 1 then
191 l_category_budget_just := l_item_budget_justification;
192 else
193 if l_category_budget_just is not null then
194 l_category_budget_just := l_category_budget_just||fnd_global.local_chr(10)||l_item_budget_justification;
195 else
196 l_category_budget_just := l_item_budget_justification;
197 end if;
198 end if;
199 end if;
200 if l_category_budget_just /* l_proposal_budget_just */ is not null then
201 --dbms_output.put_line('insrting'||l_category_budget_just);
202 insert into igw_report_budg_justification(
203 proposal_id
204 ,version_id
205 ,proposal_budget_category
206 ,justification
207 ,proposal_form_number)
208 values(
209 p_proposal_id
210 ,l_version_id
211 ,rec_budget_category.proposal_budget_category
212 ,l_category_budget_just
213 ,p_proposal_form_number);
214 end if;
215
216 l_item_budget_justification := null;
217 l_category_budget_just := null;
218 END LOOP; --rec_budget_category
219
220 -- a new procedure called dump_justification is created for the
221 -- following code
222 /*
223 for rec_report_justification in c_report_justification
224 LOOP
225 l_proposal_budget_just := l_proposal_budget_just||rec_report_justification.proposal_budget_category||
226 fnd_global.local_chr(10)||rec_report_justification.justification;
227 END LOOP; --rec_report_justification
228
229 l_proposal_budget_just := nvl(substr(l_proposal_budget_just,1,4000),'NULL');
230 insert into igw_prop_abstracts(
231 proposal_id
232 ,abstract_type_code
233 ,abstract
234 ,abstract_type
235 ,last_update_date
236 ,last_updated_by
237 ,creation_date
238 ,created_by
239 ,last_update_login )
240 values(
241 p_proposal_id
242 ,'C.1'
243 ,l_proposal_budget_just
244 ,'IGW_ABSTRACT_TYPES'
245 ,sysdate
246 ,fnd_global.user_id
247 ,sysdate
248 ,fnd_global.user_id
249 ,fnd_global.login_id);
250 */
251 x_return_status := 'S';
252 EXCEPTION
253 when FND_API.G_EXC_ERROR then
254 x_return_status := 'E';
255 --x_msg_data := l_msg_data;
256 --x_msg_data := fnd_msg_pub.get(p_msg_index=>1, p_encoded=>'TRUE');
257 --dbms_output.put_line('x_msg_data right after EXCEPTION is '||x_msg_data);
258 --fnd_msg_pub.count_and_get(p_count => x_msg_count,
259 --p_data => x_msg_data);
260 when others then
261 x_return_status := 'U';
262 x_msg_data := SQLCODE||' '||SQLERRM;
263 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CREATE_BUDGET_JUSTIFICATION');
264 END create_budget_justification;
265 --------------------------------------------------------------------------------------------------
266 PROCEDURE dump_justification (p_proposal_id NUMBER
267 ,p_proposal_form_number VARCHAR2
268 ,x_return_status OUT NOCOPY VARCHAR2
269 ,x_msg_data OUT NOCOPY VARCHAR2) is
270
271 l_version_id NUMBER(15);
272 l_proposal_budget_just LONG;
273
274 cursor c_report_justification is
275 select proposal_budget_category
276 , justification
277 from igw_report_budg_justification
278 where proposal_id = p_proposal_id
279 --and version_id = l_version_id
280 and proposal_form_number = p_proposal_form_number;
281
282
283 BEGIN
284 --deleting inserted abstract into table igw_prop_abstracats
285 delete from igw_prop_abstracts
286 where proposal_id = p_proposal_id
287 and abstract_type_code = 'C.1'
288 and abstract_type = 'IGW_ABSTRACT_TYPES';
289
290 for rec_report_justification in c_report_justification
291 LOOP
292 l_proposal_budget_just := l_proposal_budget_just||rec_report_justification.proposal_budget_category||
293 fnd_global.local_chr(10)||rec_report_justification.justification;
294 END LOOP; --rec_report_justification
295
296 l_proposal_budget_just := nvl(substr(l_proposal_budget_just,1,4000),'NULL');
297 insert into igw_prop_abstracts(
298 proposal_id
299 ,abstract_type_code
300 ,abstract
301 ,abstract_type
302 ,last_update_date
303 ,last_updated_by
304 ,creation_date
305 ,created_by
306 ,last_update_login )
307 values(
308 p_proposal_id
309 ,'C.1'
310 ,l_proposal_budget_just
311 ,'IGW_ABSTRACT_TYPES'
312 ,sysdate
313 ,fnd_global.user_id
314 ,sysdate
315 ,fnd_global.user_id
316 ,fnd_global.login_id);
317
318 x_return_status := 'S';
319
320 EXCEPTION
321 when FND_API.G_EXC_ERROR then
322 x_return_status := 'E';
323 --dbms_output.put_line('x_msg_data right after EXCEPTION is '||x_msg_data);
324 --fnd_msg_pub.count_and_get(p_count => x_msg_count,
325 --p_data => x_msg_data);
326 when others then
327 x_return_status := 'U';
328 x_msg_data := SQLCODE||' '||SQLERRM;
329 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'DUMP_JUSTIFICATION');
330 END;
331
332
333 --------------------------------------------------------------------------------------------------
334
335 PROCEDURE create_q_explanation (p_proposal_form_number VARCHAR2
336 ,x_return_status OUT NOCOPY VARCHAR2
337 ,x_msg_data OUT NOCOPY VARCHAR2) is
338 cursor c_org_questions is
339 select organization_id
340 , question_number
341 , explanation
342 from igw_org_questions
343 where explanation is not null;
344 BEGIN
345 fnd_msg_pub.initialize;
346
347 --deleting previously precessed data
348 delete from igw_report_q_explanation
349 where p_proposal_form_number = p_proposal_form_number;
350
351 --inserting processed data for organization questions explanations;
352 for rec_org_questions in c_org_questions
353 LOOP
354 insert into igw_report_q_explanation (
355 organization_id
356 ,question_number
357 ,explanation
358 ,proposal_form_number )
359 values
360 ( rec_org_questions.organization_id
361 ,rec_org_questions.question_number
362 ,rec_org_questions.explanation
363 ,p_proposal_form_number );
364 END LOOP; --rec_org_questions
365
366 EXCEPTION
367 when others then
368 x_return_status := 'U';
369 x_msg_data := SQLCODE||' '||SQLERRM;
370 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'IGW_REPORT_PROCESSING:CREATE_Q_EXPLANATION');
371 END create_q_explanation;
372
373 --------------------------------------------------------------------------------------------------
374 PROCEDURE create_base_rate (p_proposal_id NUMBER
375 ,p_proposal_form_number VARCHAR2
376 ,x_return_status OUT NOCOPY VARCHAR2
377 ,x_msg_data OUT NOCOPY VARCHAR2
378 ,x_msg_count OUT NOCOPY NUMBER) is
379
380 l_version_id NUMBER;
381
382 cursor c_no_of_periods is
383 select budget_period_id
384 , start_date
385 , end_date
386 from igw_budget_periods
387 where proposal_id = p_proposal_id
388 and version_id = l_version_id;
389
390 l_base_amount NUMBER;
391 l_total_indirect_cost NUMBER;
392 l_rate_applied NUMBER;
393 l_fiscal_start_date DATE;
394 l_fiscal_end_date DATE;
395 l_fiscal_year NUMBER(4);
396 l_return_status VARCHAR2(1);
397 l_msg_data VARCHAR2(200);
398
399 BEGIN
400 fnd_msg_pub.initialize;
401 l_version_id := get_final_version(p_proposal_id);
402
403 --deleting previously precessed data
404 delete from igw_report_budget_base_rate
405 where p_proposal_form_number = p_proposal_form_number
406 and proposal_id = p_proposal_id
407 and version_id = l_version_id;
408
409 --creating data for base amount, rate and indirect cost
410 for rec_no_of_periods in c_no_of_periods
411 LOOP
412 select sum(base_amt)
413 into l_base_amount
414 from igw_budget_category_v pbcv
415 , igw_budget_details_cal_amts pbdc
416 , igw_rate_classes prc
417 where pbcv.line_item_id = pbdc.line_item_id
418 and pbdc.rate_class_id = prc.rate_class_id
419 and prc.rate_class_type = 'O'
420 and pbdc.apply_rate_flag = 'Y'
421 and pbcv.proposal_id = p_proposal_id
422 and pbcv.version_id = l_version_id
423 and pbcv.budget_period_id = rec_no_of_periods.budget_period_id
424 and pbcv.oh_applied_flag = 'Y';
425
426 select total_indirect_cost
427 into l_total_indirect_cost
428 from igw_budget_periods
429 where proposal_id = p_proposal_id
430 and version_id = l_version_id
431 and budget_period_id = rec_no_of_periods.budget_period_id;
432
433 l_rate_applied := l_total_indirect_cost/l_base_amount * 100;
434
435 IGW_OVERHEAD_CAL.get_date_details(rec_no_of_periods.start_date
436 ,l_fiscal_year
437 ,l_fiscal_start_date
438 ,l_fiscal_end_date
439 ,l_return_status
440 ,l_msg_data);
441 if l_return_status <> 'S' then
442 raise FND_API.G_EXC_ERROR;
443 end if;
444 if l_fiscal_end_date < rec_no_of_periods.end_date then
445 l_fiscal_year := null;
446 end if;
447
448 insert into igw_report_budget_base_rate(proposal_id
449 ,version_id
450 ,budget_period_id
451 ,base_amount
452 ,rate_applied
453 ,total_indirect_cost
454 ,start_date
455 ,end_date
456 ,fiscal_year
457 ,proposal_form_number )
458 values
459 ( p_proposal_id
460 ,l_version_id
461 ,rec_no_of_periods.budget_period_id
462 ,l_base_amount
463 ,l_rate_applied
464 ,l_total_indirect_cost
465 ,rec_no_of_periods.start_date
466 ,rec_no_of_periods.end_date
467 ,l_fiscal_year
468 ,p_proposal_form_number );
469 END LOOP; --rec_no_of_periods
470
471 begin
472 l_version_id := null;
473 l_base_amount := null;
474 l_total_indirect_cost := null;
475 l_rate_applied := null;
476
477 select version_id
478 , sum(nvl(total_indirect_cost,0))/sum(nvl(base_amount,0)) * 100
479 , avg(base_amount)
480 into l_version_id
481 , l_rate_applied
482 , l_base_amount
483 from igw_report_budget_base_rate
484 where proposal_id = p_proposal_id
485 and proposal_form_number = p_proposal_form_number
486 group by proposal_id, version_id;
487
488 insert into igw_report_budget_base_rate(proposal_id
489 ,version_id
490 ,budget_period_id
491 ,base_amount
492 ,rate_applied
493 ,total_indirect_cost
494 ,start_date
495 ,end_date
496 ,fiscal_year
497 ,proposal_form_number )
498 values
499 ( p_proposal_id
500 ,l_version_id
501 ,0
502 ,l_base_amount
503 ,l_rate_applied
504 ,null
505 ,null
506 ,null
507 ,null
508 ,p_proposal_form_number );
509
510
511 exception
512 when no_data_found then null;
513 end;
514
515 x_return_status := 'S';
516 EXCEPTION
517 when FND_API.G_EXC_ERROR then
518 x_return_status := l_return_status;
519 x_msg_data := l_msg_data;
520 fnd_msg_pub.count_and_get(p_count => x_msg_count,
521 p_data => x_msg_data);
522
523 when others then
524 x_return_status := 'U';
525 x_msg_data := SQLCODE||' '||SQLERRM;
526 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CREATE_BASE_RATE');
527 fnd_msg_pub.count_and_get(p_count => x_msg_count,
528 p_data => x_msg_data);
529 END create_base_rate;
530
531
532 --------------------------------------------------------------------------------------------------
533
534 /* Logic: First of all put all the lowest categories in igw_report_budget fetching
535 their corresponding data from the database by the setting the recently updated flag to 'Y'.
536 Then find their parent categories in the seed table and insert them into the above table
537 ( if parent category alrady exists then update it instead of inserting). Repeat the
538 process till the loop */
539
540 PROCEDURE create_reporting_data( p_proposal_id NUMBER
541 ,p_proposal_form_number VARCHAR2
542 ,x_return_status OUT NOCOPY VARCHAR2
543 ,x_msg_data OUT NOCOPY VARCHAR2
544 ,x_msg_count OUT NOCOPY NUMBER) is
545
546 l_budget_period_id NUMBER(15);
547 l_version_id NUMBER(15);
548 l_proposal_budget_category VARCHAR2(80);
549 l_budget_category_code VARCHAR2(30);
550
551
552 cursor c_lowest_category is
553 select irs.budget_category_code
554 from igw_report_budget_seed irs
555 where irs.proposal_form_number = p_proposal_form_number
556 and irs.budget_category_code not in
557 (select distinct IR.proposal_budget_category_code
558 from igw_report_budget_seed IR
559 where IR.proposal_form_number = p_proposal_form_number);
560
561 cursor c_parent_category is
562 select distinct proposal_budget_category_code
563 from igw_report_budget_seed
564 where proposal_form_number = p_proposal_form_number
565 and budget_category_code IN (
566 select proposal_budget_category_code
567 from igw_report_budget
568 where proposal_form_number = p_proposal_form_number
569 and proposal_id = p_proposal_id
570 and recently_updated_flag = 'Y');
571
572
573 cursor c_budget_period is
574 select budget_period_id
575 from igw_budget_periods
576 where proposal_id = p_proposal_id
577 and version_id = l_version_id;
578
579 l_budget_category VARCHAR2(80);
580 l_period_direct_cost_amt NUMBER(17,2);
581 l_period_eb_amt NUMBER(17,2);
582 l_period_direct_cost_amt1 NUMBER(17,2);
583 l_period_eb_amt1 NUMBER(17,2);
584 l_post_award_exists VARCHAR2(3);
585 l_proposal_role VARCHAR2(80);
586 l_parent_exists VARCHAR2(1);
587 l_found NUMBER(1);
588 l_expenditure_description LONG;
589 l_return_status VARCHAR2(1);
590 l_msg_data VARCHAR2(200);
591
592 BEGIN
593 fnd_msg_pub.initialize;
594 l_version_id := get_final_version(p_proposal_id);
595 if l_version_id is null then
596 raise FND_API.G_EXC_ERROR;
597 end if;
598
599 G_START_PERIOD := 1;
600 G_PROPOSAL_ID := p_proposal_id;
601 G_VERSION_ID := l_version_id;
602 G_PROPOSAL_FORM_NUMBER := p_proposal_form_number;
603
604
605 l_post_award_exists := igw_security.gms_enabled;
606
607 --dbms_output.put_line('before role selection'||l_post_award_exists);
608
609 --deleting previously precessed data
610 delete from igw_report_budget
611 where p_proposal_form_number = p_proposal_form_number
612 and proposal_id = p_proposal_id
613 and version_id = l_version_id;
614
615 for rec_budget_period in c_budget_period
616 LOOP
617 l_budget_period_id := rec_budget_period.budget_period_id;
618
619 for rec_lowest_category in c_lowest_category
620 LOOP
621 begin
622 select sum(base_amt) period_amt
623 , sum(eb_cost) eb_amt
624 , budget_period_id
625 into l_period_direct_cost_amt
626 , l_period_eb_amt
627 , l_budget_period_id
628 from igw_budget_category_v
629 where budget_category_code = rec_lowest_category.budget_category_code
630 and proposal_id = p_proposal_id
631 and version_id = l_version_id
632 and budget_period_id = rec_budget_period.budget_period_id
633 group by proposal_id, version_id, budget_period_id;
634 exception
635 when no_data_found then null;
636 --GOTO LABLE1;
637 end;
638 --dbms_output.put_line('stage1'||'code'||rec_lowest_category.budget_category_code);
639 begin
640 l_budget_category := null;
641 select proposal_budget_category
642 into l_budget_category
643 from igw_report_budget_seed
644 where proposal_form_number = p_proposal_form_number
645 and budget_category_code = rec_lowest_category.budget_category_code;
646 exception
647 when no_data_found then
648 begin
649 select meaning
650 into l_budget_category
651 from igw_lookups_v
652 where lookup_type = 'IGW_BUDGET_CATEGORY'
653 and lookup_code = rec_lowest_category.budget_category_code;
654 exception
655 when no_data_found then null;
656 end;
657 end;
658
659 insert into igw_report_budget(
660 proposal_id
661 ,version_id
662 ,budget_period_id
663 ,proposal_budget_category
664 ,proposal_budget_category_code
665 ,period_total_direct_cost
666 ,eb_total
667 ,proposal_form_number
668 --,order_sequence
669 ,recently_updated_flag
670 )
671 values
672 ( p_proposal_id
673 ,l_version_id
674 ,l_budget_period_id
675 ,l_budget_category
676 ,rec_lowest_category.budget_category_code
677 ,l_period_direct_cost_amt
678 ,l_period_eb_amt
679 ,p_proposal_form_number
680 --,igw_report_budget_seed_s.nextval
681 ,'Y');
682
683 <<LABLE1>>
684 l_period_direct_cost_amt := null;
685 l_period_eb_amt := null;
686
687 END LOOP; --rec_lowest_category
688
689
690 <<LABLE2>>
691 null;
692
693 l_parent_exists := null;
694 for rec_parent_category in c_parent_category
695 LOOP
696 l_parent_exists := null;
697 begin
698 select '1'
699 into l_parent_exists
700 from igw_report_budget_seed
701 where budget_category_code = rec_parent_category.proposal_budget_category_code
702 and rownum < 2;
703 exception
704 when no_data_found then null;
705 l_parent_exists := null;
706 end;
707
708 begin
709 select sum(nvl(base_amt,0)) period_amt
710 , sum(nvl(eb_cost,0)) eb_amt
711 , budget_period_id
712 into l_period_direct_cost_amt
713 , l_period_eb_amt
714 , l_budget_period_id
715 from igw_budget_category_v
716 where budget_category_code = rec_parent_category.proposal_budget_category_code
717 and proposal_id = p_proposal_id
718 and version_id = l_version_id
719 and budget_period_id = rec_budget_period.budget_period_id
720 group by proposal_id, version_id, budget_period_id;
721 exception
722 when no_data_found then null;
723 end;
724
725
726 select sum(nvl(period_total_direct_cost,0))
727 , sum(nvl(eb_total,0))
728 into l_period_direct_cost_amt1
729 , l_period_eb_amt1
730 from igw_report_budget
731 where proposal_id = p_proposal_id
732 and version_id = l_version_id
733 and budget_period_id = rec_budget_period.budget_period_id
734 and proposal_form_number = p_proposal_form_number
735 and proposal_budget_category_code IN (
736 select budget_category_code
737 from igw_report_budget_seed
738 where proposal_form_number = p_proposal_form_number
739 and proposal_budget_category_code = rec_parent_category.proposal_budget_category_code)
740 group by proposal_id, version_id, budget_period_id;
741
742 l_period_direct_cost_amt := nvl(l_period_direct_cost_amt,0) + nvl(l_period_direct_cost_amt1,0);
743 l_period_eb_amt := nvl(l_period_eb_amt,0) + nvl(l_period_eb_amt1,0);
744
745 if l_period_direct_cost_amt= 0 then
746 l_period_direct_cost_amt := null;
747 end if;
748
749 if l_period_eb_amt= 0 then
750 l_period_eb_amt := null;
751 end if;
752
753
754 begin
755 l_budget_category := null;
756 select proposal_budget_category
757 into l_budget_category
758 from igw_report_budget_seed
759 where proposal_form_number = p_proposal_form_number
760 and budget_category_code = rec_parent_category.proposal_budget_category_code;
761 exception
762 when no_data_found then
763 begin
764 select meaning
765 into l_budget_category
766 from igw_lookups_v
767 where lookup_type = 'IGW_BUDGET_CATEGORY'
768 and lookup_code = rec_parent_category.proposal_budget_category_code;
769 exception
770 when no_data_found then null;
771 end;
772 end;
773
774 begin
775 l_found := null;
776 update igw_report_budget
777 set period_total_direct_cost = l_period_direct_cost_amt
778 , eb_total = l_period_eb_amt
779 where proposal_form_number = p_proposal_form_number
780 and proposal_id = p_proposal_id
781 and version_id = l_version_id
782 and budget_period_id = l_budget_period_id
783 and proposal_budget_category = l_budget_category
784 and proposal_budget_category_code = rec_parent_category.proposal_budget_category_code;
785
786 if SQL%FOUND then
787 l_found := 1;
788 end if;
789 end;
790
791 if l_found is null then
792 insert into igw_report_budget(
793 proposal_id
794 ,version_id
795 ,budget_period_id
796 ,proposal_budget_category
797 ,proposal_budget_category_code
798 ,period_total_direct_cost
799 ,eb_total
800 ,proposal_form_number
801 ,recently_updated_flag
802 )
803 values
804 ( p_proposal_id
805 ,l_version_id
806 ,l_budget_period_id
807 ,l_budget_category
808 ,rec_parent_category.proposal_budget_category_code
809 ,l_period_direct_cost_amt
810 ,l_period_eb_amt
811 ,p_proposal_form_number
812 ,'Y');
813 end if;
814
815 update igw_report_budget
816 set recently_updated_flag = 'N'
817 where proposal_form_number = p_proposal_form_number
818 and proposal_id = p_proposal_id
819 and proposal_budget_category_code IN (
820 select budget_category_code
821 from igw_report_budget_seed
822 where proposal_form_number = p_proposal_form_number
823 and proposal_budget_category_code = rec_parent_category.proposal_budget_category_code);
824
825 if l_found = 1 then
826 update igw_report_budget
827 set recently_updated_flag = 'Y'
828 where proposal_form_number = p_proposal_form_number
829 and proposal_id = p_proposal_id
830 and proposal_budget_category_code = rec_parent_category.proposal_budget_category_code;
831 end if;
832
833
834
835 l_period_direct_cost_amt := null;
836 l_period_eb_amt := null;
837 END LOOP; --rec_parent_category
838
839 if l_parent_exists = 1 THEN
840 GOTO LABLE2;
841 end if;
842 END LOOP; --rec_budget_period
843
844 /* marked as it iterferes with 194TS or any other tree hierarchy. coded for PHS 398 */
845 /*
846 -- summing up expenditures into 'other' category for categories not included in seed data
847 begin
848 for rec_budget_period in c_budget_period
849 LOOP
850 l_period_direct_cost_amt := null;
851 l_period_eb_amt := null;
852 l_budget_period_id := null;
853 begin
854 select sum(base_amt) period_amt
855 , sum(eb_cost) eb_amt
856 , budget_period_id
857 into l_period_direct_cost_amt
858 , l_period_eb_amt
859 , l_budget_period_id
860 from igw_budget_category_v
861 where budget_category_code NOT IN
862 (
863 select budget_category_code
864 from igw_report_budget_seed
865 where proposal_form_number = p_proposal_form_number)
866 and proposal_id = p_proposal_id
867 and version_id = l_version_id
868 and budget_period_id = rec_budget_period.budget_period_id
869 group by proposal_id, version_id, budget_period_id;
870 exception
871 when no_data_found then null;
872 -- GOTO LABLE2;
873
874 end;
875
876
877 update igw_report_budget
878 set period_total_direct_cost = l_period_direct_cost_amt
879 , eb_total = l_period_eb_amt
880 where proposal_form_number = p_proposal_form_number
881 and proposal_id = p_proposal_id
882 and version_id = l_version_id
883 and budget_period_id = rec_budget_period.budget_period_id
884 and proposal_budget_category_code = '39';
885 END LOOP;
886 end;
887 */
888
889 x_return_status := 'S';
890 EXCEPTION
891 when FND_API.G_EXC_ERROR then
892 x_return_status := l_return_status;
893 x_msg_data := l_msg_data;
894 fnd_msg_pub.count_and_get(p_count => x_msg_count,
895 p_data => x_msg_data);
896
897 when others then
898 x_return_status := 'U';
899 x_msg_data := SQLCODE||' '||SQLERRM;
900 --dbms_output.put_line(x_msg_data);
901 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CREATE_REPORTING_DATA');
902 fnd_msg_pub.count_and_get(p_count => x_msg_count,
903 p_data => x_msg_data);
904
905 END create_reporting_data;
906 --------------------------------------------------------------------------------------------------
907 PROCEDURE create_itemized_budget( p_proposal_id NUMBER
908 ,p_proposal_form_number VARCHAR2
909 ,x_return_status OUT NOCOPY VARCHAR2
910 ,x_msg_data OUT NOCOPY VARCHAR2
911 ,x_msg_count OUT NOCOPY NUMBER) is
912
913 l_budget_period_id NUMBER(15);
914 l_version_id NUMBER(15);
915 l_proposal_budget_category VARCHAR2(80);
916 l_proposal_budg_category_code VARCHAR2(30);
917 l_budget_category_code VARCHAR2(30);
918
919 cursor c_budget_category is
920 select proposal_budget_category
921 , budget_category_code
922 , details_required_flag
923 from igw_report_budget_seed
924 where proposal_form_number = p_proposal_form_number
925 and details_required_flag = 'Y';
926
927 cursor c_itemized_budget is
928 select line_item_description
929 , line_item_cost
930 from igw_budget_line_category_v pbd
931 where pbd.proposal_id = p_proposal_id
932 and pbd.version_id = l_version_id
933 and pbd.budget_period_id = 1
934 and pbd.budget_category_code =l_budget_category_code;
935
936 l_budget_category VARCHAR2(80);
937 l_post_award_exists VARCHAR2(3);
938 l_proposal_role VARCHAR2(80);
939 l_expenditure_description LONG;
940 l_return_status VARCHAR2(1);
941 l_msg_data VARCHAR2(200);
942
943 BEGIN
944 fnd_msg_pub.initialize;
945 l_version_id := get_final_version(p_proposal_id);
946 if l_version_id is null then
947 raise FND_API.G_EXC_ERROR;
948 end if;
949
950 G_START_PERIOD := 1;
951 G_PROPOSAL_ID := p_proposal_id;
952 G_VERSION_ID := l_version_id;
953
954
955 delete from igw_report_itemized_budget
956 where p_proposal_form_number = p_proposal_form_number
957 and proposal_id = p_proposal_id
958 and version_id = l_version_id
959 and proposal_form_number = p_proposal_form_number;
960
961 for rec_budget_category in c_budget_category
962 LOOP
963 --if rec_budget_category.details_required_flag = 'Y' then
964 l_budget_category_code := rec_budget_category.budget_category_code;
965 for rec_itemized_budget in c_itemized_budget
966 LOOP
967 if rec_itemized_budget.line_item_description is not null then
968 if c_itemized_budget%ROWCOUNT = 1 then
969 l_expenditure_description :=rec_itemized_budget.line_item_description;
970 else
971 l_expenditure_description := l_expenditure_description||';'||rec_itemized_budget.line_item_description;
972 end if;
973 end if;
974 END LOOP; --rec_itemized_budget
975
976 if l_expenditure_description is not null then
977 insert into igw_report_itemized_budget ( proposal_id
978 ,version_id
979 ,budget_period_id
980 ,proposal_budget_category
981 ,expenditure_description
982 ,proposal_form_number)
983 values
984 ( p_proposal_id
985 ,l_version_id
986 ,1
987 ,rec_budget_category.proposal_budget_category
988 ,l_expenditure_description
989 ,p_proposal_form_number);
990 l_expenditure_description := null;
991 end if;
992
993 --end if;
994 END LOOP; --rec_budget_category_code
995
996 EXCEPTION
997 when FND_API.G_EXC_ERROR then
998 x_return_status := l_return_status;
999 x_msg_data := l_msg_data;
1000 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1001 p_data => x_msg_data);
1002
1003 when others then
1004 x_return_status := 'U';
1005
1006 x_msg_data := SQLCODE||' '||SQLERRM;
1007 --dbms_output.put_line('the x_msg_dat is'||x_msg_data);
1008 fnd_msg_pub.add_exc_msg(G_PKG_NAME, 'CREATE_ITEMIZED_BUDGET');
1009 --fnd_msg_pub.add;
1010 fnd_msg_pub.count_and_get(p_count => x_msg_count,
1011 p_data => x_msg_data);
1012
1013 END create_itemized_budget;
1014
1015 --------------------------------------------------------------------------------------------------
1016
1017 FUNCTION get_abstract(p_proposal_id in INTEGER,
1018 p_abstract_type_code in INTEGER) RETURN VARCHAR2 is
1019
1020 v_abstract varchar2(4000):=null;
1021
1022 cursor c is
1023 select abstract
1024 from igw_prop_abstracts
1025 where proposal_id = p_proposal_id
1026 and abstract_type_code = p_abstract_type_code;
1027
1028 begin
1029 open c;
1030 fetch c into v_abstract;
1031 close c;
1032 return(v_abstract);
1033
1034 exception
1035 when others then
1036 if c%isopen then
1037 close c;
1038 end if;
1039 return(v_abstract);
1040 END get_abstract;
1041
1042 PROCEDURE get_answer(p_proposal_id in INTEGER,
1043 P_question_no in VARCHAR2,
1044 p_person_id in INTEGER,
1045 p_party_id in INTEGER,
1046 p_organization_id in INTEGER,
1047 p_response1 out NOCOPY VARCHAR2,
1048 p_response2 out NOCOPY VARCHAR2) is
1049 begin
1050 if p_person_id is null and p_organization_id is null then
1051 select answer,
1052 explanation
1053 into p_response1,
1054 p_response2
1055 from igw_prop_questions
1056 where proposal_id = p_proposal_id and
1057 question_number = p_question_no;
1058 elsif p_organization_id is not null then
1059 select answer,
1060 explanation
1061 into p_response1,
1062 p_response2
1063 from igw_org_questions
1064 where organization_id = p_organization_id
1065 and question_number = p_question_no;
1066 else
1067 select answer,
1068 explanation
1069 into p_response1,
1070 p_response2
1071 from igw_prop_person_questions
1072 where proposal_id = p_proposal_id and
1073 question_number = p_question_no and
1074 party_id = p_party_id;
1075 end if;
1076 exception
1077 when others then
1078 p_response1 := null;
1079 p_response2:= null;
1080 end get_answer;
1081
1082
1083 function get_response(p_proposal_id in INTEGER,
1084 P_question_no in VARCHAR2,
1085 p_person_id in INTEGER default null,
1086 p_party_id in INTEGER,
1087 p_organization_id in INTEGER default null) return varchar2 is
1088 v_response varchar2(2);
1089 begin
1090 if p_person_id is null and p_organization_id is null then
1091 select answer
1092 into v_response
1093 from igw_prop_questions
1094 where proposal_id = p_proposal_id and
1095 question_number = p_question_no;
1096 elsif p_organization_id is not null then
1097 select answer
1098 into v_response
1099 from igw_org_questions
1100 where organization_id = p_organization_id
1101 and question_number = p_question_no;
1102 else
1103 select answer
1104 into v_response
1105 from igw_prop_person_questions
1106 where proposal_id = p_proposal_id and
1107 question_number = p_question_no and
1108 party_id = p_party_id;
1109 end if;
1110 return(v_response);
1111 exception
1112 when others then
1113 return(null);
1114 end get_response;
1115
1116 function get_explanation(p_proposal_id in INTEGER,
1117 P_question_no in VARCHAR2,
1118 p_person_id in INTEGER default null,
1119 p_party_id IN INTEGER,
1120 p_organization_id in INTEGER default null) return varchar2 is
1121 v_explanation varchar2(2005);
1122 begin
1123 if p_person_id is null and p_organization_id is null then
1124 select explanation
1125 into v_explanation
1126 from igw_prop_questions
1127 where proposal_id = p_proposal_id and
1128 question_number = p_question_no;
1129 elsif p_organization_id is not null then
1130 select explanation
1131 into v_explanation
1132 from igw_org_questions
1133 where organization_id = p_organization_id
1134 and question_number = p_question_no;
1135 else
1136 select explanation
1137 into v_explanation
1138 from igw_prop_person_questions
1139 where proposal_id = p_proposal_id and
1140 question_number = p_question_no and
1141 party_id = p_person_id;
1142 end if;
1143 return(v_explanation);
1144 exception
1145 when others then
1146 return(null);
1147 end get_explanation;
1148
1149 FUNCTION get_subjects(p_proposal_id in integer,
1150 p_study_title_id integer,
1151 p_subject_race in varchar2,
1152 p_subject_gender in varchar2) RETURN INTEGER is
1153 v_subjects integer;
1154 begin
1155 select no_of_subjects
1156 into v_subjects
1157 from igw_study_titles ST,
1158 igw_subject_information SI
1159 where ST.proposal_id = p_proposal_id and
1160 ST.study_title_id = SI.study_title_id and
1161 SI.subject_race_code = p_subject_race and
1162 SI.subject_type_code = p_subject_gender and
1163 SI.study_title_id = p_study_title_id;
1164 return(v_subjects);
1165 exception
1166 when others then
1167 return(null);
1168 end get_subjects;
1169
1170 -- not changing person_id to party_id since the data job name is got from hr tables and
1171 -- is presently not captured by external persons screen.
1172 FUNCTION get_job_name(person_id_v in number) RETURN VARCHAR2 is
1173 segment varchar2(100);
1174 cursor job_cursor(segment_p varchar2) is
1175 select decode(segment_p,'SEGMENT1',pjd.segment1,
1176 'SEGMENT2',pjd.segment2,
1177 'SEGMENT3',pjd.segment3,
1178 'SEGMENT4',pjd.segment4,
1179 'SEGMENT5',pjd.segment5,
1180 'SEGMENT6',pjd.segment6,
1181 'SEGMENT7',pjd.segment7,
1182 'SEGMENT8',pjd.segment8,
1183 'SEGMENT9',pjd.segment9,
1184 'SEGMENT10',pjd.segment10,
1185 'SEGMENT11',pjd.segment11,
1186 'SEGMENT12',pjd.segment12,
1187 'SEGMENT13',pjd.segment13,
1188 'SEGMENT14',pjd.segment14,
1189 'SEGMENT15',pjd.segment15,
1190 'SEGMENT16',pjd.segment16,
1191 'SEGMENT17',pjd.segment17,
1192 'SEGMENT18',pjd.segment18,
1193 'SEGMENT19',pjd.segment19,
1194 'SEGMENT20',pjd.segment20,
1195 'SEGMENT21',pjd.segment21,
1196 'SEGMENT22',pjd.segment22,
1197 'SEGMENT23',pjd.segment23,
1198 'SEGMENT24',pjd.segment24,
1199 'SEGMENT25',pjd.segment25,
1200 'SEGMENT26',pjd.segment26,
1201 'SEGMENT27',pjd.segment27,
1202 'SEGMENT28',pjd.segment28,
1203 'SEGMENT29',pjd.segment29,
1204 'SEGMENT30',pjd.segment30)
1205 FROM per_position_definitions pjd,
1206 per_all_positions pap,
1207 per_assignments_x paf,
1208 per_people_x ppx
1209 WHERE ppx.person_id = paf.person_id
1210 and ppx.business_group_id = paf.business_group_id
1211 and paf.primary_flag = 'Y'
1212 and paf.position_id = pap.position_id
1213 and pap.position_definition_id = pjd.position_definition_id
1214 and ppx.person_id = person_id_v;
1215
1216 begin
1217
1218 segment := fnd_profile.value('IGW_JOB_NAME_SEGMENT');
1219
1220 if segment is null then
1221 return null;
1222 end if;
1223 open job_cursor(segment);
1224 fetch job_cursor into segment;
1225 if not job_cursor%found then
1226 return null;
1227 else
1228 close job_cursor;
1229 return segment;
1230 end if;
1231 exception
1232 when others then
1233 return null;
1234 end get_job_name;
1235
1236
1237 -- not changing person_id to party_id since the data phone number is got from hr tables and
1238 -- is presently not captured by external persons screen. Also this info is required only for PI.
1239 FUNCTION get_phone_number(v_person_id in NUMBER,
1240 v_phone_type in VARCHAR2) RETURN VARCHAR2 is
1241 v_phone_number varchar2(60);
1242 cursor c1 is select phone_number
1243 from per_phones
1244 where parent_id = v_person_id and
1245 parent_table = 'PER_ALL_PEOPLE_F' and
1246 phone_type = v_phone_type and
1247 date_to is null;
1248 begin
1249 open c1;
1250 fetch c1 into v_phone_number;
1251 close c1;
1252 return v_phone_number;
1253 exception
1254 when others then
1255 if c1%isopen then
1256 close c1;
1257 end if;
1258 return null;
1259 END get_phone_number;
1260
1261
1262
1263 FUNCTION get_person_Degrees(person_id_p in NUMBER,
1264 party_id_p in NUMBER,
1265 proposal_id_p in number)
1266 return varchar2 is
1267 degrees varchar2(100);
1268 degree varchar2(80);
1269 counter integer := 1;
1270 cursor c1 is
1271 SELECT PER_D.DEGREE
1272 FROM IGW_PROP_PERSON_DEGREES PROP_D,
1273 IGW_PERSON_DEGREES PER_D
1274 WHERE PER_D.PERSON_DEGREE_ID = PROP_D.PERSON_DEGREE_ID AND
1275 PROP_D.SHOW_FLAG = 'Y' AND
1276 PER_D.PARTY_ID = party_id_p and
1277 PROP_D.proposal_id = proposal_id_p
1278 ORDER BY PROP_D.DEGREE_SEQUENCE;
1279 BEGIN
1280 degrees := null;
1281 open c1;
1282 LOOP
1283 fetch c1 into degree;
1284 if c1%found then
1285 if degrees is null then
1286 degrees := degree;
1287 else
1288 degrees := degrees||','||degree;
1289 end if;
1290 else
1291 exit;
1292 end if;
1293 if counter = 3 then
1294 exit;
1295 end if;
1296 counter := counter + 1;
1297 end LOOP;
1298 close c1;
1299 return degrees;
1300 exception
1301 when others then
1302 if c1%isopen then
1303 close c1;
1304 end if;
1305 return null;
1306 end get_person_degrees;
1307
1308
1309
1310 -- not changing org_id to party_id since the data org type is required only for applicant org which
1311 -- is present in HR
1312 FUNCTION get_org_type (p_org_id in integer,
1313 p_org_type1 in Varchar2,
1314 p_org_type2 in Varchar2 default null,
1315 p_org_type3 in Varchar2 default null) return Varchar2 is
1316 row_count integer;
1317 begin
1318 select count(*)
1319 into row_count
1320 from igw_org_types
1321 where organization_id = p_org_id
1322 and organization_type_code in (p_org_type1, p_org_type2, p_org_type3);
1323 if row_count > 0 then
1324 return('X');
1325 end if;
1326 return(NULL);
1327 EXCEPTION
1328 when others then
1329 return(NULL);
1330 END;
1331 -------------------------------------------------------------------------------
1332
1333 FUNCTION get_org_party_name(p_party_id in number, p_org_id in number) RETURN VARCHAR2 is
1334 l_org_party_name hz_parties.party_name%TYPE;
1335 begin
1336 if p_party_id is not null then
1337 select party_name
1338 into l_org_party_name
1339 from hz_parties
1340 where party_id = p_party_id
1341 and party_type = 'ORGANIZATION';
1342 elsif p_org_id is not null then
1343 select name
1344 into l_org_party_name
1345 from hr_organization_units
1346 where organization_id = p_org_id;
1347 end if;
1348 return (l_org_party_name);
1349 exception
1350 when no_data_found then null;
1351 return (null);
1352 end;
1353
1354
1355 END IGW_REPORT_PROCESSING;