[Home] [Help]
PACKAGE BODY: APPS.IGW_VIEW_PARAMETERS
Source
1 PACKAGE BODY IGW_VIEW_PARAMETERS as
2 --$Header: igwprprb.pls 115.8 2002/10/18 01:36:51 vmedikon ship $
3
4 Function get_project_location_id (i_proposal_id NUMBER) RETURN NUMBER is
5 o_project_location_id number(15);
6 Begin
7 select performing_organization_id
8 into o_project_location_id
9 from igw_prop_locations
10 where proposal_id = i_proposal_id AND
11 rownum = 1;
12 RETURN o_project_location_id;
13 exception
14 when no_data_found then
15 o_project_location_id := NULL;
16 RETURN o_project_location_id;
17 End;
18 ------------------------------------------------------------------------------------------
19
20 Function get_project_location_name(i_organization_id NUMBER) RETURN VARCHAR2 is
21 o_project_location_name hr_all_organization_units.NAME%TYPE;
22 Begin
23 if (i_organization_id is null) then
24 o_project_location_name := NULL;
25 RETURN o_project_location_name;
26 else
27 select name
28 into o_project_location_name
29 from hr_organization_units
30 where organization_id = i_organization_id;
31 RETURN o_project_location_name;
32 end if;
33 End;
34 ----------------------------------------------------------------------------------------------
35
36 Function get_major_goals (i_proposal_id NUMBER) RETURN VARCHAR2 is
37 o_major_goals varchar2(250);
38 Begin
39 select substr(abstract, 1, 250)
40 into o_major_goals
41 from igw_prop_abstracts
42 where proposal_id = i_proposal_id AND
43 abstract_type = 'IGW_ABSTRACT_TYPES' AND
44 abstract_type_code = 'IGW1';
45 RETURN o_major_goals;
46 exception
47 when no_data_found then
48 o_major_goals := NULL;
49 RETURN o_major_goals;
50 End;
51 ---------------------------------------------------------------------------------------------------
52 Function get_pi_id (i_award_id NUMBER,
53 i_proposal_id NUMBER) RETURN NUMBER is
54 o_pi_id number;
55 prop_start_date date;
56 Begin
57 select proposal_start_date
58 into prop_start_date
59 from igw_proposals
60 where proposal_id = i_proposal_id;
61
62 o_pi_id := 0;
63
64 select person_id
65 into o_pi_id
66 from gms_personnel gp
67 where award_id = i_award_id and
68 award_role = 'AM' and
69 prop_start_date >= start_date_active and
70 prop_start_date <= nvl(end_date_active, sysdate);
71
72 return o_pi_id;
73 Exception
74 when no_data_found then
75 begin
76 select person_id
77 into o_pi_id
78 from gms_personnel gp
79 where award_id = i_award_id and
80 award_role = 'AM' and
81 rownum = 1;
82 return o_pi_id;
83 Exception
84 when no_data_found then
85 o_pi_id := null;
86 return o_pi_id;
87 End;
88 End get_pi_id;
89 ---------------------------------------------------------------------------------------------------
90 Function get_pi_name (i_award_id NUMBER,
91 i_proposal_id NUMBER) RETURN VARCHAR2 is
92 prop_start_date date;
93 o_pi_id number;
94 o_pi_name per_all_people_f.FULL_NAME%TYPE;
95 Begin
96 select proposal_start_date
97 into prop_start_date
98 from igw_proposals
99 where proposal_id = i_proposal_id;
100
101 o_pi_id := 0;
102 o_pi_name := 'pi';
103
104 select gp.person_id, ppx.full_name
105 into o_pi_id, o_pi_name
106 from gms_personnel gp, per_people_x ppx
107 where gp.award_id = i_award_id and
108 gp.award_role = 'AM' and
109 prop_start_date >= gp.start_date_active and
110 prop_start_date <= nvl(gp.end_date_active, sysdate) and
111 gp.person_id = ppx.person_id;
112 return o_pi_name;
113 Exception
114 when no_data_found then
115 begin
116 select gp.person_id, ppx.full_name
117 into o_pi_id, o_pi_name
118 from gms_personnel gp, per_people_x ppx
119 where gp.award_id = i_award_id and
120 gp.award_role = 'AM' and
121 gp.person_id = ppx.person_id and
122 rownum = 1;
123 return o_pi_name;
124 Exception
125 when no_data_found then
126 o_pi_id := null;
127 o_pi_name := null;
128 return o_pi_name;
129 End;
130 End get_pi_name;
131
132 ----------------------------------------------------------------------------------------------------
133 Function get_percent_effort (i_proposal_id NUMBER,
134 i_person_id NUMBER) RETURN NUMBER is
135 o_percent_effort number;
136
137 begin
138 select percent_effort
139 into o_percent_effort
140 from igw_prop_persons
141 where proposal_id = i_proposal_id
142 AND person_id = i_person_id;
143 return o_percent_effort;
144 exception
145 when others then
146 o_percent_effort := NULL;
147 return o_percent_effort;
148
149 End get_percent_effort;
150
151 ----------------------------------------------------------------------------------------------------
152 Function get_direct_cost (i_award_id NUMBER,
153 i_proposal_id NUMBER) RETURN NUMBER is
154
155 prop_start_date date;
156 o_direct_cost number;
157 Begin
158 select proposal_start_date
159 into prop_start_date
160 from igw_proposals
161 where proposal_id = i_proposal_id;
162
163 select sum(direct_cost)
164 into o_direct_cost
165 from gms_installments
166 where award_id = i_award_id and
167 active_flag = 'Y' and
168 prop_start_date >= start_date_active and
169 prop_start_date <= nvl(end_date_active, sysdate);
170 return o_direct_cost;
171 Exception
172 when no_data_found then
173 o_direct_cost := null;
174 return o_direct_cost;
175 End get_direct_cost;
176
177 --------------------------------------------------------------------------------------------------------
178 Function get_total_cost (i_award_id NUMBER,
179 i_proposal_id NUMBER) RETURN NUMBER is
180
181 prop_start_date date;
182 o_total_cost number;
183 Begin
184 select proposal_start_date
185 into prop_start_date
186 from igw_proposals
187 where proposal_id = i_proposal_id;
188
189 select sum(direct_cost + indirect_cost)
190 into o_total_cost
191 from gms_installments
192 where award_id = i_award_id and
193 active_flag = 'Y' and
194 prop_start_date >= start_date_active and
195 prop_start_date <= nvl(end_date_active, sysdate);
196 return o_total_cost;
197 Exception
198 when no_data_found then
199 o_total_cost := null;
200 return o_total_cost;
201 End get_total_cost;
202
203 ---------------------------------------------------------------------------------------------------------
204 Function get_old_direct_cost (i_award_id NUMBER) RETURN NUMBER is
205
206 o_old_direct_cost number;
207 Begin
208 select sum(direct_cost)
209 into o_old_direct_cost
210 from gms_installments
211 where award_id = i_award_id;
212 return o_old_direct_cost;
213 Exception
214 when no_data_found then
215 o_old_direct_cost := null;
216 return o_old_direct_cost;
217 End get_old_direct_cost;
218
219 -------------------------------------------------------------------------------------------------------------
220 Function get_old_total_cost (i_award_id NUMBER) RETURN NUMBER is
221
222 o_old_total_cost number;
223 Begin
224 select sum(direct_cost + indirect_cost)
225 into o_old_total_cost
226 from gms_installments
227 where award_id = i_award_id;
228 return o_old_total_cost;
229 Exception
230 when no_data_found then
231 o_old_total_cost := null;
232 return o_old_total_cost;
233 End get_old_total_cost;
234
235 END IGW_VIEW_PARAMETERS;