[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_PERSON_SUPPORT_PVT
Source
1 PACKAGE BODY IGW_PROP_PERSON_SUPPORT_PVT as
2 /* $Header: igwvppsb.pls 120.5 2005/09/12 21:05:18 vmedikon ship $ */
3 PROCEDURE create_prop_person_support (
4 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5 p_commit IN VARCHAR2 := FND_API.G_FALSE,
6 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
7 p_get_data IN VARCHAR2,
8 x_rowid out NOCOPY VARCHAR2,
9 X_PROP_PERSON_SUPPORT_ID OUT NOCOPY NUMBER,
10 P_PROPOSAL_ID IN NUMBER,
11 P_PERSON_ID IN NUMBER,
12 P_PARTY_ID IN NUMBER,
13 P_SUPPORT_TYPE IN VARCHAR2,
14 P_PROPOSAL_AWARD_ID IN NUMBER,
15 P_PROPOSAL_AWARD_NUMBER IN VARCHAR2,
16 P_PROPOSAL_AWARD_TITLE IN VARCHAR2,
17 P_PI_PERSON_ID IN NUMBER,
18 P_PI_PARTY_ID IN NUMBER,
19 P_PI_PERSON_NAME IN VARCHAR2,
20 P_SPONSOR_ID IN NUMBER,
21 P_SPONSOR_NAME IN VARCHAR2,
22 P_PROJECT_LOCATION IN VARCHAR2,
23 P_LOCATION_PARTY_ID IN NUMBER,
24 P_PROJECT_LOCATION_NAME IN VARCHAR2,
25 P_START_DATE IN DATE,
26 P_END_DATE IN DATE,
27 P_PERCENT_EFFORT IN NUMBER,
28 P_MAJOR_GOALS IN VARCHAR2,
29 P_OVERLAP IN VARCHAR2,
30 P_ANNUAL_DIRECT_COST IN NUMBER,
31 P_TOTAL_COST IN NUMBER,
32 P_CALENDAR_START_DATE IN DATE,
33 P_CALENDAR_END_DATE IN DATE,
34 P_ACADEMIC_START_DATE IN DATE,
35 P_ACADEMIC_END_DATE IN DATE,
36 P_SUMMER_START_DATE IN DATE,
37 P_SUMMER_END_DATE IN DATE,
38 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
39 P_ATTRIBUTE1 IN VARCHAR2,
40 P_ATTRIBUTE2 IN VARCHAR2,
41 P_ATTRIBUTE3 IN VARCHAR2,
42 P_ATTRIBUTE4 IN VARCHAR2,
43 P_ATTRIBUTE5 IN VARCHAR2,
44 P_ATTRIBUTE6 IN VARCHAR2,
45 P_ATTRIBUTE7 IN VARCHAR2,
46 P_ATTRIBUTE8 IN VARCHAR2,
47 P_ATTRIBUTE9 IN VARCHAR2,
48 P_ATTRIBUTE10 IN VARCHAR2,
49 P_ATTRIBUTE11 IN VARCHAR2,
50 P_ATTRIBUTE12 IN VARCHAR2,
51 P_ATTRIBUTE13 IN VARCHAR2,
52 P_ATTRIBUTE14 IN VARCHAR2,
53 P_ATTRIBUTE15 IN VARCHAR2,
54 P_SEQUENCE_NUMBER IN NUMBER,
55 x_return_status OUT NOCOPY VARCHAR2,
56 x_msg_count OUT NOCOPY NUMBER,
57 x_msg_data OUT NOCOPY VARCHAR2)
58
59 is
60
61 l_return_status VARCHAR2(1);
62 l_error_msg_code VARCHAR2(250);
63 l_msg_count NUMBER;
64 l_msg_data VARCHAR2(250);
65 l_data VARCHAR2(250);
66 l_msg_index_out NUMBER;
67
68
69 l_proposal_award_id number := p_proposal_award_id;
70 l_proposal_award_title varchar2(250) := p_proposal_award_title;
71 l_sponsor_id number := p_sponsor_id;
72 l_start_date date := p_start_date;
73 l_end_date date := p_end_date;
74 l_pi_person_id number := p_pi_person_id;
75 l_pi_party_id number := p_pi_party_id;
76 l_project_location varchar2(100) := p_project_location;
77 l_annual_direct_cost number := p_annual_direct_cost;
78 l_total_cost number := p_total_cost;
79 l_major_goals varchar2(4000) := p_major_goals;
80 l_percent_effort number := p_percent_effort;
81
82 l_location_party_id number := p_location_party_id;
83
84
85
86
87 BEGIN
88 null;
89
90 END create_prop_person_support;
91 --------------------------------------------------------------------------------------------------------------
92
93 Procedure update_prop_person_support (
94 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
95 p_commit IN VARCHAR2 := FND_API.G_FALSE,
96 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
97 p_get_data IN VARCHAR2,
98 x_rowid IN VARCHAR2,
99 P_PROP_PERSON_SUPPORT_ID IN NUMBER,
100 P_PROPOSAL_ID IN NUMBER,
101 P_PERSON_ID IN NUMBER,
102 P_PARTY_ID IN NUMBER,
103 P_SUPPORT_TYPE IN VARCHAR2,
104 P_PROPOSAL_AWARD_ID IN NUMBER,
105 P_PROPOSAL_AWARD_NUMBER IN VARCHAR2,
106 P_PROPOSAL_AWARD_TITLE IN VARCHAR2,
107 P_PI_PERSON_ID IN NUMBER,
108 P_PI_PARTY_ID IN NUMBER,
109 P_PI_PERSON_NAME IN VARCHAR2,
110 P_SPONSOR_ID IN NUMBER,
111 P_SPONSOR_NAME IN VARCHAR2,
112 P_PROJECT_LOCATION IN VARCHAR2,
113 P_LOCATION_PARTY_ID IN NUMBER,
114 P_PROJECT_LOCATION_NAME IN VARCHAR2,
115 P_START_DATE IN DATE,
116 P_END_DATE IN DATE,
117 P_PERCENT_EFFORT IN NUMBER,
118 P_MAJOR_GOALS IN VARCHAR2,
119 P_OVERLAP IN VARCHAR2,
120 P_ANNUAL_DIRECT_COST IN NUMBER,
121 P_TOTAL_COST IN NUMBER,
122 P_CALENDAR_START_DATE IN DATE,
123 P_CALENDAR_END_DATE IN DATE,
124 P_ACADEMIC_START_DATE IN DATE,
125 P_ACADEMIC_END_DATE IN DATE,
126 P_SUMMER_START_DATE IN DATE,
127 P_SUMMER_END_DATE IN DATE,
128 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
129 P_ATTRIBUTE1 IN VARCHAR2,
130 P_ATTRIBUTE2 IN VARCHAR2,
131 P_ATTRIBUTE3 IN VARCHAR2,
132 P_ATTRIBUTE4 IN VARCHAR2,
133 P_ATTRIBUTE5 IN VARCHAR2,
134 P_ATTRIBUTE6 IN VARCHAR2,
135 P_ATTRIBUTE7 IN VARCHAR2,
136 P_ATTRIBUTE8 IN VARCHAR2,
137 P_ATTRIBUTE9 IN VARCHAR2,
138 P_ATTRIBUTE10 IN VARCHAR2,
139 P_ATTRIBUTE11 IN VARCHAR2,
140 P_ATTRIBUTE12 IN VARCHAR2,
141 P_ATTRIBUTE13 IN VARCHAR2,
142 P_ATTRIBUTE14 IN VARCHAR2,
143 P_ATTRIBUTE15 IN VARCHAR2,
144 p_record_version_number IN NUMBER,
145 P_SEQUENCE_NUMBER IN NUMBER,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2) is
149
150
151 l_return_status VARCHAR2(1);
152 l_error_msg_code VARCHAR2(250);
153 l_msg_count NUMBER;
154 l_data VARCHAR2(250);
155 l_msg_data VARCHAR2(250);
156 l_msg_index_out NUMBER;
157
158 l_proposal_award_id number := p_proposal_award_id;
159 l_proposal_award_title varchar2(250) := p_proposal_award_title;
160 l_sponsor_id number := p_sponsor_id;
161 l_start_date date := p_start_date;
162 l_end_date date := p_end_date;
163 l_pi_person_id number := p_pi_person_id;
164 l_pi_party_id number := p_pi_party_id ;
165 l_project_location varchar2(100) := p_project_location;
166 l_annual_direct_cost number := p_annual_direct_cost;
167 l_total_cost number := p_total_cost;
168 l_major_goals varchar2(4000) := p_major_goals;
169 l_percent_effort number := p_percent_effort;
170
171 l_location_party_id number := p_location_party_id;
172
173 BEGIN
174 null;
175
176 END update_prop_person_support;
177 --------------------------------------------------------------------------------------------------------
178
179 Procedure delete_prop_person_support (
180 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
181 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
182 ,p_validate_only IN VARCHAR2 := FND_API.G_FALSE
183 ,x_rowid IN VARCHAR2
184 ,p_record_version_number IN NUMBER
185 ,x_return_status OUT NOCOPY VARCHAR2
186 ,x_msg_count OUT NOCOPY NUMBER
187 ,x_msg_data OUT NOCOPY VARCHAR2) is
188
189 l_proposal_id NUMBER;
190
191
192 l_return_status VARCHAR2(1);
193 l_error_msg_code VARCHAR2(250);
194 l_msg_count NUMBER;
195 l_data VARCHAR2(250);
196 l_performing_org_id NUMBER;
197 l_msg_data VARCHAR2(250);
198 l_msg_index_out NUMBER;
199
200 BEGIN
201 null;
202 END delete_prop_person_support;
203
204 ------------------------------------------------------------------------------------------
205 PROCEDURE CHECK_LOCK
206 (x_rowid IN VARCHAR2
207 ,p_record_version_number IN NUMBER
208 ,x_return_status OUT NOCOPY VARCHAR2) is
209
210 l_proposal_id number;
211 BEGIN
212 null;
213 END CHECK_LOCK;
214
215 -------------------------------------------------------------------------------------------------------
216 PROCEDURE CHECK_ERRORS is
217 l_msg_count NUMBER;
218 BEGIN
219 null;
220
221 END CHECK_ERRORS;
222 -------------------------------------------------------------------------------------------------------
223
224 PROCEDURE POPULATE_OTHER_SUPPORT_TABLE (p_init_msg_list in varchar2 := FND_API.G_FALSE,
225 p_commit in varchar2 := FND_API.G_FALSE,
226 p_validate_only in varchar2 := FND_API.G_FALSE,
227 p_proposal_id in number,
228 p_person_id in number,
229 p_party_id in number,
230 x_return_status out NOCOPY varchar2,
231 x_msg_count out NOCOPY number,
232 x_msg_data out NOCOPY varchar2) IS
233
234 n number;
235 prop_person_support igw_prop_person_support%rowtype;
236 v_pi_name per_all_people_f.FULL_NAME%TYPE;
237 deadline_date date;
238 x_date date;
239 x_date_minus_three_yrs date;
240 prop_id number; -- This is proposal_id in igw_awards_v
241 percent_effort number;
242 x_rowid varchar2(30);
243 x_prop_person_support_id number;
244
245 cursor c is
246 select ipp.person_id,
247 ipp.person_party_id,
248 ipp.percent_effort,
249 ipv.proposal_id,
250 ipv.proposal_number,
251 ipv.proposal_title,
252 ipv.proposal_start_date,
253 ipv.proposal_end_date,
254 ipv.sponsor_id,
255 ipv.pi_id,
256 ipv.major_goals,
257 ipv.total_cost,
258 ipv.annual_direct_cost,
259 ipv.project_location_id
260 from igw_prop_persons ipp,
261 igw_prop_v ipv
262 where ipp.person_party_id = p_party_id AND
263 ipv.proposal_id = ipp.proposal_id AND
264 ipv.proposal_id <> p_proposal_id AND
265 ipv.sponsor_action_code in ('8', '4', '5') AND
266 ipv.proposal_type_code <> 3;
267
268
269 cursor d is
270 select gp.person_id,
271 pa.award_id,
272 pa.funding_source_award_number,
273 pa.award_full_name,
274 pa.major_goals,
275 pa.funding_source_id,
276 pa.start_date_active,
277 pa.end_date_active,
278 pa.proposal_id
279 from gms_personnel gp,
280 igw_awards_v pa
281 where gp.person_id = p_person_id AND
282 gp.award_id = pa.award_id AND
283 pa.status = 'ACTIVE' AND
284 ((pa.proposal_id <> p_proposal_id) OR (pa.proposal_id is NULL));
285
286
287 cursor e is
288 select gp.person_id,
289 pa.award_id,
290 pa.funding_source_award_number,
291 pa.award_full_name,
292 pa.major_goals,
293 pa.funding_source_id,
294 pa.start_date_active,
295 pa.end_date_active,
296 pa.proposal_id
297 from gms_personnel gp,
298 igw_awards_v pa
299 where gp.person_id = p_person_id AND
300 gp.award_id = pa.award_id AND
301 pa.close_date > x_date_minus_three_yrs AND
302 pa.close_date < x_date AND
303 pa.close_date is not null AND
304 pa.status = 'CLOSED' AND
305 ((pa.proposal_id <> p_proposal_id) OR (pa.proposal_id is NULL));
306
307
308
309 BEGIN
310 null;
311
312 END POPULATE_OTHER_SUPPORT_TABLE;
313
314 -------------------------------------------------------------------------------------------------------
315 PROCEDURE POPULATE_PROP_AWARD_INFO (p_proposal_id in number,
316 p_person_id in number,
317 p_party_id in number,
318 p_support_type in varchar2,
319 p_proposal_award_number in varchar2,
320 x_proposal_award_id out NOCOPY number,
321 x_proposal_award_title out NOCOPY varchar2,
322 x_sponsor_id out NOCOPY number,
323 x_start_date out NOCOPY date,
324 x_end_date out NOCOPY date,
325 x_pi_person_id out NOCOPY number,
326 x_pi_party_id out NOCOPY number,
327 x_project_location out NOCOPY varchar2,
328 x_location_party_id out NOCOPY number,
329 x_annual_direct_cost out NOCOPY number,
330 x_total_cost out NOCOPY number,
331 x_major_goals out NOCOPY varchar2,
332 x_percent_effort out NOCOPY number,
333 x_return_status out NOCOPY varchar2) IS
334
335 l_proposal_id number;
336 BEGIN
337 null;
338
339 END POPULATE_PROP_AWARD_INFO;
340 ------------------------------------------------------------------------------------------------------
341 PROCEDURE Get_Award_Id
342 (
343 p_context_field IN VARCHAR2,
344 p_check_id_flag IN VARCHAR2,
345 p_award_number IN VARCHAR2,
346 p_award_id IN NUMBER,
347 x_award_id OUT NOCOPY NUMBER,
348 x_return_status OUT NOCOPY VARCHAR2
349 ) IS
350
351 l_api_name CONSTANT VARCHAR2(30) := 'Get_Award_Id';
352
353 --Possible values for p_context_field
354 /* AWARD_ID */
355
356 BEGIN
357
358 null;
359 END Get_Award_id;
360
361 FUNCTION get_person_name
362 ( p_party_id number, p_person_id number )
363 RETURN varchar2 IS
364 o_person_name varchar2(250);
365 BEGIN
366 null;
367 end;
368
369
370
371 END IGW_PROP_PERSON_SUPPORT_PVT;