[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP
Source
1 PACKAGE BODY igw_prop AS
2 --$Header: igwprcpb.pls 115.57 2002/11/14 18:49:26 vmedikon ship $
3
4 -----------------------------------------------------------------------------
5
6 PROCEDURE get_business_group(
7 o_business_group_id out NOCOPY number,
8 o_business_group_name out NOCOPY varchar2 ) IS
9 BEGIN
10
11 SELECT imp.business_group_id,
12 org.name
13 INTO o_business_group_id,
14 o_business_group_name
15 FROM igw_implementations imp,
16 hr_organization_units org
17 WHERE org.organization_id = imp.business_group_id;
18
19 EXCEPTION
20
21 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
22 null;
23
24 END get_business_group;
25
26 -----------------------------------------------------------------------------
27
28 PROCEDURE get_signing_official(
29 i_organization_id in number,
30 o_signing_official_id out NOCOPY number,
31 o_signing_official_name out NOCOPY varchar2 ) IS
32 BEGIN
33
34 SELECT o.signing_official_id,
35 p.full_name
36 INTO o_signing_official_id,
37 o_signing_official_name
38 FROM igw_org_details o,
39 per_people_x p
40 WHERE o.organization_id = i_organization_id
41 AND p.person_id = o.signing_official_id;
42
43 EXCEPTION
44
45 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
46 null;
47
48 END get_signing_official;
49
50 -----------------------------------------------------------------------------
51
52 FUNCTION get_admin_official_id( i_organization_id number )
53 RETURN number IS
54
55 v_admin_official_id number(15);
56
57 CURSOR cur_org_details IS
58 SELECT admin_official_id
59 FROM igw_org_details
60 WHERE organization_id = i_organization_id;
61
62 BEGIN
63
64 OPEN cur_org_details;
65 FETCH cur_org_details INTO v_admin_official_id;
66 CLOSE cur_org_details;
67
68 RETURN v_admin_official_id;
69
70 END get_admin_official_id;
71
72 -----------------------------------------------------------------------------
73
74 FUNCTION get_user_name( p_person_id number )
75 RETURN varchar2 IS
76
77 v_user_name varchar2(100);
78
79 CURSOR cur_user IS
80 SELECT user_name
81 FROM fnd_user
82 WHERE employee_id = p_person_id
83 ORDER BY user_id;
84
85 BEGIN
86
87 OPEN cur_user;
88 FETCH cur_user INTO v_user_name;
89 CLOSE cur_user;
90
91 RETURN v_user_name;
92
93 END get_user_name;
94
95 -----------------------------------------------------------------------------
96
97 FUNCTION get_pi_full_name( p_proposal_id number )
98 RETURN varchar2 IS
99
100 v_pi_full_name per_all_people_f.FULL_NAME%TYPE;
101
102 BEGIN
103
104 SELECT full_name
105 INTO v_pi_full_name
106 FROM igw_prop_persons pp,
107 per_people_x per
108 WHERE pp.proposal_id = p_proposal_id
109 AND pp.pi_flag = 'Y'
110 AND per.person_id = pp.person_id;
111
112 RETURN v_pi_full_name;
113
114 EXCEPTION
115
116 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
117 RETURN null;
118
119 END get_pi_full_name;
120
121 -----------------------------------------------------------------------------
122 FUNCTION get_pi_formatted_name( p_proposal_id number )
123 RETURN varchar2 IS
124
125 v_pi_full_name varchar2(301);
126
127 BEGIN
128
129 SELECT last_name||','||first_name
130 INTO v_pi_full_name
131 FROM igw_prop_persons pp,
132 per_people_x per
133 WHERE pp.proposal_id = p_proposal_id
134 AND pp.pi_flag = 'Y'
135 AND per.person_id = pp.person_id;
136
137 RETURN v_pi_full_name;
138
139 EXCEPTION
140
141 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
142 RETURN null;
143
144 END get_pi_formatted_name;
145
146 -----------------------------------------------------------------------------
147
148 FUNCTION get_lookup_meaning( p_lookup_type varchar2, p_lookup_code varchar2 )
149 RETURN varchar2 IS
150
151 v_lookup_meaning fnd_lookups.meaning%TYPE;
152
153 BEGIN
154
155 SELECT meaning
156 INTO v_lookup_meaning
157 FROM fnd_lookups
158 WHERE lookup_type = p_lookup_type
159 AND lookup_code = p_lookup_code;
160
161 RETURN v_lookup_meaning;
162
163 EXCEPTION
164
165 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
166 RETURN null;
167
168 END get_lookup_meaning;
169
170 -----------------------------------------------------------------------------
171
172 FUNCTION get_narrative_status( p_proposal_id number )
173 RETURN varchar2 IS
174
175 v_count_incomplete number(3);
176 v_count_complete number(3);
177
178 v_narrative_status varchar2(1) := 'N';
179
180 BEGIN
181
182 SELECT count(*)
183 INTO v_count_incomplete
184 FROM igw_prop_narratives
185 WHERE proposal_id = p_proposal_id
186 AND module_status = 'I';
187
188 SELECT count(*)
189 INTO v_count_complete
190 FROM igw_prop_narratives
191 WHERE proposal_id = p_proposal_id
192 AND module_status = 'C';
193
194 IF v_count_incomplete = 0 and v_count_complete = 0 THEN
195 v_narrative_status := 'N';
196 ELSIF v_count_incomplete > 0 THEN
197 v_narrative_status := 'I';
198 ELSE
199 v_narrative_status := 'C';
200 END IF;
201
202 RETURN v_narrative_status;
203
204 END get_narrative_status;
205
206 -----------------------------------------------------------------------------
207
208 FUNCTION get_major_subdivision( p_organization_id number )
209 RETURN varchar2 IS
210
211 v_major_subdivision varchar2(80);
212
213 BEGIN
214
215 SELECT l.meaning
216 INTO v_major_subdivision
217 FROM fnd_lookups l,
218 igw_org_details o
219 WHERE o.organization_id = p_organization_id
220 AND l.lookup_type = 'IGW_NIH_MAJOR_SUBDIVISION'
221 AND l.lookup_code = o.nih_subdivision_code;
222
223 return v_major_subdivision;
224
225 EXCEPTION
226
227 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
228 RETURN null;
229
230 END;
231
232 -----------------------------------------------------------------------------
233
234 FUNCTION is_proposal_signing_official( p_proposal_id number, p_user_id number )
235 RETURN varchar2 IS
236
237 CURSOR cur_user_roles IS
238 select 'Y'
239 from igw_prop_user_roles
240 where proposal_id = p_proposal_id
241 and user_id = p_user_id
242 and role_id = 3;
243
244 v_temp varchar2(1);
245
246 BEGIN
247
248 OPEN cur_user_roles;
249 FETCH cur_user_roles INTO v_temp;
250 CLOSE cur_user_roles;
251
252 if v_temp = 'Y' then
253 return 'Y';
254 end if;
255
256 return 'N';
257
258 END is_proposal_signing_official;
259
260 -----------------------------------------------------------------------------
261
262 FUNCTION get_top_parent_org_name( p_organization_id number )
263 RETURN varchar2 IS
264
265 v_curr_organization_id number(15) := p_organization_id;
266 v_parent_organization_id number(15);
267
268 v_top_parent_org_name hr_all_organization_units.NAME%TYPE;
269
270 BEGIN
271
272 LOOP
273
274 v_parent_organization_id := igw_proposal_approval.get_parent_org_id( v_curr_organization_id );
275
276 IF v_parent_organization_id IS null THEN
277 exit;
278 END IF;
279
280 v_curr_organization_id := v_parent_organization_id;
281
282 END LOOP;
283
284 BEGIN
285
286 SELECT name
287 INTO v_top_parent_org_name
288 FROM hr_organization_units
289 WHERE organization_id = v_curr_organization_id;
290
291 EXCEPTION
292 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
293 null;
294
295 END;
296
297 RETURN v_top_parent_org_name;
298
299 END get_top_parent_org_name;
300
301 -----------------------------------------------------------------------------
302
303 PROCEDURE ins_prop_user_role( p_proposal_id number,
304 p_user_id number,
305 p_role_id number ) IS
306 BEGIN
307
308 INSERT INTO igw_prop_users
309 (
310 proposal_id,
311 user_id,
312 start_date_active,
313 end_date_active,
314 last_update_date,
315 last_updated_by,
316 creation_date,
317 created_by,
318 last_update_login
319 )
320 SELECT
321 p_proposal_id,
322 p_user_id,
323 SYSDATE,
324 null,
325 SYSDATE,
326 FND_GLOBAL.USER_ID,
327 SYSDATE,
328 FND_GLOBAL.USER_ID,
329 FND_GLOBAL.LOGIN_ID
330 FROM
331 dual
332 WHERE not exists
333 ( SELECT 'X'
334 FROM igw_prop_users
335 WHERE proposal_id = p_proposal_id
336 AND user_id = p_user_id );
337
338
339 INSERT INTO igw_prop_user_roles
340 (
341 proposal_id,
342 user_id,
343 role_id,
344 last_update_date,
345 last_updated_by,
346 creation_date,
347 created_by,
348 last_update_login
349 )
350 SELECT
351 p_proposal_id,
352 p_user_id,
353 p_role_id,
354 SYSDATE,
355 FND_GLOBAL.USER_ID,
356 SYSDATE,
357 FND_GLOBAL.USER_ID,
358 FND_GLOBAL.LOGIN_ID
359 FROM
360 dual
361 WHERE not exists
362 ( SELECT 'X'
363 FROM igw_prop_user_roles
364 WHERE proposal_id = p_proposal_id
365 AND user_id = p_user_id
366 AND role_id = p_role_id );
367
368
369 END ins_prop_user_role;
370
371 -----------------------------------------------------------------------------
372
373 PROCEDURE del_prop_user_role( p_proposal_id number,
374 p_user_id number,
375 p_role_id number ) IS
376 BEGIN
377
378 DELETE igw_prop_user_roles
379 WHERE proposal_id = p_proposal_id
380 AND user_id = p_user_id
381 AND role_id = p_role_id;
382
383 DELETE igw_prop_users
384 WHERE proposal_id = p_proposal_id
385 AND user_id = p_user_id
386 AND not exists (
387 SELECT 'X'
388 FROM igw_prop_user_roles
389 WHERE proposal_id = p_proposal_id
390 AND user_id = p_user_id );
391
392 END del_prop_user_role;
393
394 -----------------------------------------------------------------------------
395
396 PROCEDURE copy_proposal_all(
397 i_old_proposal_id IN number,
398 i_new_proposal_id IN number,
399 i_new_proposal_number IN varchar2,
400 i_budget_copy_flag IN varchar2,
401 i_budget_version_id IN number,
402 i_narrative_copy_flag IN varchar2,
403 o_error_message OUT NOCOPY varchar2,
404 o_return_status OUT NOCOPY varchar2 ) IS
405
406
407 PROCEDURE copy_proposal
408 ( p_old_proposal_id number, p_new_proposal_id number, p_new_proposal_number varchar2 ) IS
409 BEGIN
410 INSERT INTO igw_proposals_all
411 (
412 sponsor_action_code,
413 sponsor_action_date,
414 award_amount,
415 proposal_id,
416 proposal_number,
417 lead_organization_id,
418 org_id,
419 proposal_status,
420 proposal_start_date,
421 proposal_end_date,
422 proposal_title,
423 proposal_type_code,
424 award_number,
425 original_proposal_number,
426 original_award_number,
427 original_proposal_start_date,
428 original_proposal_end_date,
429 activity_type_code,
430 sponsor_id,
431 funding_sponsor_unit,
432 original_sponsor_id,
433 sponsor_proposal_number,
434 notice_of_opportunity_code,
435 program_number,
436 program_title,
437 program_url,
438 submitting_organization_id,
439 signing_official_id,
440 admin_official_id,
441 deadline_date,
442 deadline_type,
443 letter_of_intent_due_date,
444 record_version_number,
445 last_update_date,
446 last_updated_by,
447 creation_date,
448 created_by,
449 last_update_login,
450 attribute_category,
451 attribute1,
452 attribute2,
453 attribute3,
454 attribute4,
455 attribute5,
456 attribute6,
457 attribute7,
458 attribute8,
459 attribute9,
460 attribute10,
461 attribute11,
462 attribute12,
463 attribute13,
464 attribute14,
465 attribute15
466 )
467 SELECT
468 null, /* sponsor_action_code */
469 null, /* sponsor_action_date */
470 null, /* award_amount */
471 p_new_proposal_id,
472 p_new_proposal_number,
473 lead_organization_id,
474 org_id,
475 'P',
476 proposal_start_date,
477 proposal_end_date,
478 proposal_title,
479 proposal_type_code,
480 null, /* award_number */
481 original_proposal_number,
482 original_award_number,
483 original_proposal_start_date,
484 original_proposal_end_date,
488 original_sponsor_id,
485 activity_type_code,
486 sponsor_id,
487 funding_sponsor_unit,
489 null, /* sponsor_proposal_number */
490 notice_of_opportunity_code,
491 program_number,
492 program_title,
493 program_url,
494 submitting_organization_id,
495 signing_official_id,
496 admin_official_id,
497 deadline_date,
498 deadline_type,
499 letter_of_intent_due_date,
500 1,
501 SYSDATE,
502 FND_GLOBAL.USER_ID,
503 SYSDATE,
504 FND_GLOBAL.USER_ID,
505 FND_GLOBAL.LOGIN_ID,
506 attribute_category,
507 attribute1,
508 attribute2,
509 attribute3,
510 attribute4,
511 attribute5,
512 attribute6,
513 attribute7,
514 attribute8,
515 attribute9,
516 attribute10,
517 attribute11,
518 attribute12,
519 attribute13,
520 attribute14,
521 attribute15
522 FROM
523 igw_proposals_all
524 WHERE
525 proposal_id = p_old_proposal_id;
526 EXCEPTION
527 WHEN OTHERS THEN
528 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PROPOSAL');
529 raise;
530 END;
531
532
533 PROCEDURE copy_proposal_manager_role
534 ( p_old_proposal_id number, p_new_proposal_id number ) IS
535 BEGIN
536 INSERT INTO igw_prop_users
537 (
538 proposal_id,
539 user_id,
540 start_date_active,
541 end_date_active,
542 record_version_number,
543 last_update_date,
544 last_updated_by,
545 creation_date,
546 created_by,
547 last_update_login
548 )
549 SELECT
550 p_new_proposal_id,
551 user_id,
552 SYSDATE,
553 null,
554 1,
555 SYSDATE,
556 FND_GLOBAL.USER_ID,
557 SYSDATE,
558 FND_GLOBAL.USER_ID,
559 FND_GLOBAL.LOGIN_ID
560 FROM
561 igw_prop_user_roles
562 WHERE
563 proposal_id = p_old_proposal_id and
564 role_id = 0
565 ;
566
567
568 INSERT INTO igw_prop_user_roles
569 (
570 proposal_id,
571 user_id,
572 role_id,
573 record_version_number,
574 last_update_date,
575 last_updated_by,
576 creation_date,
577 created_by,
578 last_update_login
579 )
580 SELECT
581 p_new_proposal_id,
582 user_id,
583 role_id,
584 1,
585 SYSDATE,
586 FND_GLOBAL.USER_ID,
587 SYSDATE,
588 FND_GLOBAL.USER_ID,
589 FND_GLOBAL.LOGIN_ID
590 FROM
591 igw_prop_user_roles
592 WHERE
593 proposal_id = p_old_proposal_id and
594 role_id = 0
595 ;
596
597 EXCEPTION
598 WHEN OTHERS THEN
599 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PROPOSAL_MANAGER_ROLE');
600 raise;
601 END;
602
603
604
605
606 PROCEDURE copy_program_addresses
607 ( p_old_proposal_id number, p_new_proposal_id number ) IS
608 BEGIN
609 INSERT INTO igw_prop_program_addresses
610 (
611 proposal_id,
612 address_id,
613 number_of_copies,
614 mail_description,
615 record_version_number,
616 last_update_date,
617 last_updated_by,
618 creation_date,
619 created_by,
620 last_update_login
621 )
622 SELECT
623 p_new_proposal_id,
624 address_id,
625 number_of_copies,
626 mail_description,
627 1,
628 SYSDATE,
629 FND_GLOBAL.USER_ID,
630 SYSDATE,
631 FND_GLOBAL.USER_ID,
632 FND_GLOBAL.LOGIN_ID
633 FROM
634 igw_prop_program_addresses
635 WHERE
636 proposal_id = p_old_proposal_id;
637 EXCEPTION
638 WHEN OTHERS THEN
639 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PROGRAM_ADDRESSES');
640 raise;
641 END;
642
643
644
645 PROCEDURE copy_locations
646 ( p_old_proposal_id number, p_new_proposal_id number ) IS
647 BEGIN
651 proposal_id,
648 INSERT INTO igw_prop_locations
649 (
650 prop_location_id,
652 performing_organization_id,
653 party_id,
654 record_version_number,
655 last_update_date,
656 last_updated_by,
657 creation_date,
658 created_by,
659 last_update_login
660 )
661 SELECT
662 igw_prop_locations_s.nextval,
663 p_new_proposal_id,
664 performing_organization_id,
665 party_id,
666 1,
667 SYSDATE,
668 FND_GLOBAL.USER_ID,
669 SYSDATE,
670 FND_GLOBAL.USER_ID,
671 FND_GLOBAL.LOGIN_ID
672 FROM
673 igw_prop_locations
674 WHERE
675 proposal_id = p_old_proposal_id;
676 EXCEPTION
677 WHEN OTHERS THEN
678 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_LOCATIONS');
679 raise;
680 END;
681
682
683
684 PROCEDURE copy_persons
685 ( p_old_proposal_id number, p_new_proposal_id number ) IS
686 BEGIN
687 INSERT INTO igw_prop_persons
688 (
689 proposal_id,
690 person_id,
691 person_party_id,
692 person_sequence,
693 proposal_role_code,
694 pi_flag,
695 key_person_flag,
696 percent_effort,
697 person_organization_id,
698 org_party_id,
699 record_version_number,
700 last_update_date,
701 last_updated_by,
702 creation_date,
703 created_by,
704 last_update_login
705 )
706 SELECT
707 p_new_proposal_id,
708 person_id,
709 person_party_id,
710 person_sequence,
711 proposal_role_code,
712 pi_flag,
713 key_person_flag,
714 percent_effort,
715 person_organization_id,
716 org_party_id,
717 1,
718 SYSDATE,
719 FND_GLOBAL.USER_ID,
720 SYSDATE,
721 FND_GLOBAL.USER_ID,
722 FND_GLOBAL.LOGIN_ID
723 FROM
724 igw_prop_persons
725 WHERE
726 proposal_id = p_old_proposal_id;
727 EXCEPTION
728 WHEN OTHERS THEN
729 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PERSONS');
730 raise;
731 END;
732
733
734 PROCEDURE copy_special_reviews
735 ( p_old_proposal_id number, p_new_proposal_id number ) IS
736 BEGIN
737 INSERT INTO igw_prop_special_reviews
738 (
739 proposal_id,
740 special_review_code,
741 special_review_type,
742 approval_type_code,
743 protocol_number,
744 application_date,
745 approval_date,
746 comments,
747 record_version_number,
748 last_update_date,
749 last_updated_by,
750 creation_date,
751 created_by,
752 last_update_login
753 )
754 SELECT
755 p_new_proposal_id,
756 special_review_code,
757 special_review_type,
758 approval_type_code,
759 protocol_number,
760 application_date,
761 approval_date,
762 comments,
763 1,
764 SYSDATE,
765 FND_GLOBAL.USER_ID,
766 SYSDATE,
767 FND_GLOBAL.USER_ID,
768 FND_GLOBAL.LOGIN_ID
769 FROM
770 igw_prop_special_reviews
771 WHERE
772 proposal_id = p_old_proposal_id;
773 EXCEPTION
774 WHEN OTHERS THEN
775 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_SPECIAL_REVIEWS');
776 raise;
777 END;
778
779
780
781
782 PROCEDURE copy_science_codes
783 ( p_old_proposal_id number, p_new_proposal_id number ) IS
784 BEGIN
785 INSERT INTO igw_prop_science_codes
786 (
787 proposal_id,
788 science_code,
789 record_version_number,
790 last_update_date,
791 last_updated_by,
792 creation_date,
793 created_by,
794 last_update_login
795 )
796 SELECT
797 p_new_proposal_id,
798 science_code,
799 1,
800 SYSDATE,
801 FND_GLOBAL.USER_ID,
802 SYSDATE,
803 FND_GLOBAL.USER_ID,
804 FND_GLOBAL.LOGIN_ID
805 FROM
806 igw_prop_science_codes
807 WHERE
808 proposal_id = p_old_proposal_id;
809 EXCEPTION
813 END;
810 WHEN OTHERS THEN
811 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_SCIENCE_CODES');
812 raise;
814
815
816 PROCEDURE copy_abstracts
817 ( p_old_proposal_id number, p_new_proposal_id number ) IS
818 BEGIN
819 INSERT INTO igw_prop_abstracts
820 (
821 proposal_id,
822 abstract_type,
823 abstract_type_code,
824 abstract,
825 record_version_number,
826 last_update_date,
827 last_updated_by,
828 creation_date,
829 created_by,
830 last_update_login,
831 attribute_category,
832 attribute1,
833 attribute2,
834 attribute3,
835 attribute4,
836 attribute5,
837 attribute6,
838 attribute7,
839 attribute8,
840 attribute9,
841 attribute10,
842 attribute11,
843 attribute12,
844 attribute13,
845 attribute14,
846 attribute15
847 )
848 SELECT
849 p_new_proposal_id,
850 abstract_type,
851 abstract_type_code,
852 abstract,
853 1,
854 decode(last_update_date,null,null,SYSDATE),
855 decode(last_updated_by,null,null,FND_GLOBAL.USER_ID),
856 SYSDATE,
857 FND_GLOBAL.USER_ID,
858 FND_GLOBAL.LOGIN_ID,
859 attribute_category,
860 attribute1,
861 attribute2,
862 attribute3,
863 attribute4,
864 attribute5,
865 attribute6,
866 attribute7,
867 attribute8,
868 attribute9,
869 attribute10,
870 attribute11,
871 attribute12,
872 attribute13,
873 attribute14,
874 attribute15
875 FROM
876 igw_prop_abstracts
877 WHERE
878 proposal_id = p_old_proposal_id;
879 EXCEPTION
880 WHEN OTHERS THEN
881 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_ABSTRACTS');
882 raise;
883 END;
884
885
886 PROCEDURE copy_budgets
887 ( p_old_proposal_id number, p_new_proposal_id number, p_budget_version_id number ) IS
888
889 v_return_status varchar2(80);
890 v_error_message varchar2(2000);
891 v_msg_count number(10);
892
893 copy_budget_error exception;
894
895 BEGIN
896
897 igw_budget_operations.copy_budget
898 (
899 p_old_proposal_id,
900 p_new_proposal_id,
901 p_budget_version_id,
902 null,
903 'P',
904 'PROPOSAL_BUDGET',
905 v_return_status,
906 v_error_message,
907 v_msg_count
908 );
909
910 IF v_return_status = 'E' or v_return_status = 'U' THEN
911 raise copy_budget_error;
912 END IF;
913
914 EXCEPTION
915 WHEN copy_budget_error THEN
916 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_BUDGETS');
917 raise;
918
919 WHEN others THEN
920 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_BUDGETS');
921 raise;
922 END;
923
924
925
926 PROCEDURE copy_narratives
927 ( p_old_proposal_id number, p_new_proposal_id number ) IS
928 BEGIN
929 INSERT INTO igw_prop_narratives
930 (
931 proposal_id,
932 module_id,
933 comments,
934 record_version_number,
935 last_update_date,
936 last_updated_by,
937 creation_date,
938 created_by,
939 last_update_login,
940 email_address,
941 module_status,
942 contact_name,
943 phone_number,
944 module_title
945 )
946 SELECT
947 p_new_proposal_id,
948 module_id,
949 comments,
950 1,
951 SYSDATE,
952 FND_GLOBAL.USER_ID,
953 SYSDATE,
954 FND_GLOBAL.USER_ID,
955 FND_GLOBAL.LOGIN_ID,
956 email_address,
957 'I',
958 contact_name,
959 phone_number,
960 module_title
961 FROM
962 igw_prop_narratives
963 WHERE
964 proposal_id = p_old_proposal_id;
965
966 UPDATE igw_proposals_all
967 SET (narrative_type_code, narrative_submission_code) =
968 ( SELECT narrative_type_code, narrative_submission_code
972
969 FROM igw_proposals_all
970 WHERE proposal_id = p_old_proposal_id )
971 WHERE proposal_id = p_new_proposal_id;
973 EXCEPTION
974 WHEN OTHERS THEN
975 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_NARRATIVES');
976 raise;
977 END;
978
979
980 PROCEDURE copy_component_statuses
981 ( p_old_proposal_id number, p_new_proposal_id number,
982 p_budget_copy_flag varchar2, p_narrative_copy_flag varchar2 ) IS
983
984 l_return_status VARCHAR2(1);
985
986 BEGIN
987
988 /*
989
990 INSERT INTO igw_prop_checklist
991 (
992 proposal_id,
993 document_type_code,
994 checklist_order,
995 complete,
996 not_applicable,
997 record_version_number,
998 last_update_date,
999 last_updated_by,
1000 creation_date,
1001 created_by,
1002 last_update_login
1003 )
1004 SELECT
1005 p_new_proposal_id,
1006 document_type_code,
1007 checklist_order,
1008 'N',
1009 not_applicable,
1010 1,
1011 SYSDATE,
1012 FND_GLOBAL.USER_ID,
1013 SYSDATE,
1014 FND_GLOBAL.USER_ID,
1015 FND_GLOBAL.LOGIN_ID
1016 FROM
1017 igw_prop_checklist
1018 WHERE
1019 proposal_id = p_old_proposal_id;
1020
1021 */
1022
1023 Igw_Prop_Checklist_Pvt.Populate_Checklist
1024 (
1025 p_proposal_id => p_new_proposal_id,
1026 x_return_status => l_return_status
1027 );
1028
1029 EXCEPTION
1030 WHEN OTHERS THEN
1031 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_COMPONENT_STATUSES');
1032 raise;
1033 END;
1034
1035
1036 PROCEDURE copy_proposal_attachments
1037 ( p_old_proposal_id number, p_new_proposal_id number ) IS
1038 BEGIN
1039 fnd_attached_documents2_pkg.copy_attachments
1040 (
1041 x_from_entity_name => 'IGW_PROPOSALS_ALL',
1042 x_from_pk1_value => p_old_proposal_id,
1043 x_from_pk2_value => null,
1044 x_from_pk3_value => null,
1045 x_from_pk4_value => null,
1046 x_from_pk5_value => null,
1047 x_to_entity_name => 'IGW_PROPOSALS_ALL',
1048 x_to_pk1_value => p_new_proposal_id,
1049 x_to_pk2_value => null,
1050 x_to_pk3_value => null,
1051 x_to_pk4_value => null,
1052 x_to_pk5_value => null,
1053 x_created_by => null,
1054 x_last_update_login => null,
1055 x_program_application_id => null,
1056 x_program_id => null,
1057 x_request_id => null
1058 );
1059 EXCEPTION
1060 WHEN OTHERS THEN
1061 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PROPOSAL_ATTACHMENTS');
1062 raise;
1063 END;
1064
1065
1066 PROCEDURE copy_narrative_attachments
1067 ( p_old_proposal_id number, p_new_proposal_id number ) IS
1068
1069 cursor cur_narr_attch is
1070 select distinct pk2_value
1071 from fnd_attached_documents
1072 where entity_name = 'IGW_PROP_NARRATIVES'
1073 and pk1_value = p_old_proposal_id;
1074
1075 BEGIN
1076
1077 for rec_narr_attch in cur_narr_attch loop
1078
1079 fnd_attached_documents2_pkg.copy_attachments
1080 (
1081 x_from_entity_name => 'IGW_PROP_NARRATIVES',
1082 x_from_pk1_value => p_old_proposal_id,
1083 x_from_pk2_value => rec_narr_attch.pk2_value,
1084 x_from_pk3_value => null,
1085 x_from_pk4_value => null,
1086 x_from_pk5_value => null,
1087 x_to_entity_name => 'IGW_PROP_NARRATIVES',
1088 x_to_pk1_value => p_new_proposal_id,
1089 x_to_pk2_value => rec_narr_attch.pk2_value,
1090 x_to_pk3_value => null,
1091 x_to_pk4_value => null,
1092 x_to_pk5_value => null,
1093 x_created_by => null,
1094 x_last_update_login => null,
1095 x_program_application_id => null,
1096 x_program_id => null,
1097 x_request_id => null
1098 );
1099
1100 end loop;
1101
1102 EXCEPTION
1103 WHEN OTHERS THEN
1104 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_NARRATIVE_ATTACHMENTS');
1105 raise;
1106 END;
1107
1108
1109 begin
1110
1111 savepoint a;
1112
1113 copy_proposal( i_old_proposal_id, i_new_proposal_id, i_new_proposal_number );
1114
1115 copy_proposal_manager_role( i_old_proposal_id, i_new_proposal_id );
1116
1117 copy_program_addresses( i_old_proposal_id, i_new_proposal_id );
1118
1119 copy_locations( i_old_proposal_id, i_new_proposal_id );
1120
1121 copy_persons( i_old_proposal_id, i_new_proposal_id );
1122
1123 copy_special_reviews( i_old_proposal_id, i_new_proposal_id );
1124
1125 copy_science_codes( i_old_proposal_id, i_new_proposal_id );
1126
1127 copy_abstracts( i_old_proposal_id, i_new_proposal_id );
1128
1129 copy_proposal_attachments( i_old_proposal_id, i_new_proposal_id );
1130
1131 copy_component_statuses( i_old_proposal_id, i_new_proposal_id, i_budget_copy_flag, i_narrative_copy_flag);
1132
1133 if i_budget_copy_flag <> 'N' then
1134
1135 copy_budgets( i_old_proposal_id, i_new_proposal_id, i_budget_version_id );
1136
1137 end if;
1138
1139 if i_narrative_copy_flag <> 'N' then
1140
1141 copy_narratives( i_old_proposal_id, i_new_proposal_id );
1142
1143 copy_narrative_attachments( i_old_proposal_id, i_new_proposal_id );
1144
1145 end if;
1146
1147 o_return_status := 'S';
1148
1149 exception
1150 when others then
1151 rollback to a;
1152 fnd_msg_pub.add_exc_msg('IGW_PROP', 'COPY_PROPOSAL_ALL' );
1153 o_error_message := fnd_msg_pub.get( p_msg_index => FND_MSG_PUB.G_FIRST,
1154 p_encoded => FND_API.G_TRUE );
1155 o_return_status := 'U';
1156
1157 end;
1158
1159 -----------------------------------------------------------------------------
1160
1161 PROCEDURE set_component_status
1162 ( i_component_name IN varchar2,
1163 i_proposal_id IN number,
1164 i_value IN varchar2 ) IS
1165 BEGIN
1166
1167 IF i_component_name = 'BUDGET' THEN
1168 update igw_prop_checklist
1169 set complete = decode(i_value,'C','Y','N'),
1170 not_applicable = decode(i_value,'N','Y','N'),
1171 last_update_date = sysdate,
1172 last_updated_by = fnd_global.user_id,
1173 last_update_login = fnd_global.login_id
1174 where proposal_id = i_proposal_id
1175 and document_type_code = 'BUDGETS';
1176
1177 ELSIF i_component_name = 'NARRATIVE' THEN
1178
1179 update igw_prop_checklist
1180 set complete = decode(i_value,'C','Y','N'),
1181 not_applicable = decode(i_value,'N','Y','N'),
1182 last_update_date = sysdate,
1183 last_updated_by = fnd_global.user_id,
1184 last_update_login = fnd_global.login_id
1185 where proposal_id = i_proposal_id
1186 and document_type_code = 'NARRATIVES';
1187
1188 END IF;
1189
1190 END set_component_status;
1191
1192 -----------------------------------------------------------------------------
1193
1194 END igw_prop;