[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_PERSON_SUPPORT_TBH
Source
1 package body IGW_PROP_PERSON_SUPPORT_TBH as
2 /* $Header: igwtppsb.pls 115.8 2002/11/15 00:43:08 ashkumar ship $*/
3
4 PROCEDURE INSERT_ROW (
5 X_ROWID OUT NOCOPY VARCHAR2,
6 X_PROP_PERSON_SUPPORT_ID OUT NOCOPY NUMBER,
7 P_PROPOSAL_ID IN NUMBER,
8 P_PERSON_ID IN NUMBER,
9 P_PARTY_ID IN NUMBER,
10 P_SUPPORT_TYPE IN VARCHAR2,
11 P_PROPOSAL_AWARD_ID IN NUMBER,
12 P_PROPOSAL_AWARD_NUMBER IN VARCHAR2,
13 P_PROPOSAL_AWARD_TITLE IN VARCHAR2,
14 P_PI_PERSON_ID IN NUMBER,
15 P_PI_PARTY_ID IN NUMBER,
16 P_SPONSOR_ID IN NUMBER,
17 P_PROJECT_LOCATION IN VARCHAR2,
18 P_LOCATION_PARTY_ID IN NUMBER,
19 P_START_DATE IN DATE,
20 P_END_DATE IN DATE,
21 P_PERCENT_EFFORT IN NUMBER,
22 P_MAJOR_GOALS IN VARCHAR2,
23 P_OVERLAP IN VARCHAR2,
24 P_ANNUAL_DIRECT_COST IN NUMBER,
25 P_TOTAL_COST IN NUMBER,
26 P_CALENDAR_START_DATE IN DATE,
27 P_CALENDAR_END_DATE IN DATE,
28 P_ACADEMIC_START_DATE IN DATE,
29 P_ACADEMIC_END_DATE IN DATE,
30 P_SUMMER_START_DATE IN DATE,
31 P_SUMMER_END_DATE IN DATE,
32 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
33 P_ATTRIBUTE1 IN VARCHAR2,
34 P_ATTRIBUTE2 IN VARCHAR2,
35 P_ATTRIBUTE3 IN VARCHAR2,
36 P_ATTRIBUTE4 IN VARCHAR2,
37 P_ATTRIBUTE5 IN VARCHAR2,
38 P_ATTRIBUTE6 IN VARCHAR2,
39 P_ATTRIBUTE7 IN VARCHAR2,
40 P_ATTRIBUTE8 IN VARCHAR2,
41 P_ATTRIBUTE9 IN VARCHAR2,
42 P_ATTRIBUTE10 IN VARCHAR2,
43 P_ATTRIBUTE11 IN VARCHAR2,
44 P_ATTRIBUTE12 IN VARCHAR2,
45 P_ATTRIBUTE13 IN VARCHAR2,
46 P_ATTRIBUTE14 IN VARCHAR2,
47 P_ATTRIBUTE15 IN VARCHAR2,
48 P_MODE IN VARCHAR2,
49 P_SEQUENCE_NUMBER IN NUMBER,
50 X_RETURN_STATUS OUT NOCOPY VARCHAR2) is
51
52 L_PROP_PERSON_SUPPORT_ID NUMBER;
53
54 cursor c is select ROWID from IGW_PROP_PERSON_SUPPORT
55 where prop_person_support_id = l_prop_person_support_id;
56
57 l_last_update_date DATE;
58 l_last_updated_by NUMBER;
59 l_last_update_login NUMBER;
60
61
62 BEGIN
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64
65 l_last_update_date := SYSDATE;
66
67 if(p_mode = 'I') then
68 l_last_updated_by := 1;
69 l_last_update_login := 0;
70 elsif (p_mode = 'R') then
71 l_last_updated_by := FND_GLOBAL.USER_ID;
72
73 if l_last_updated_by is NULL then
74 l_last_updated_by := -1;
75 end if;
76
77 l_last_update_login := FND_GLOBAL.LOGIN_ID;
78
79 if l_last_update_login is NULL then
80 l_last_update_login := -1;
81 end if;
82 else
83 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
84 app_exception.raise_exception;
85 end if;
86
87 insert into igw_prop_person_support (
88 PROP_PERSON_SUPPORT_ID
89 ,PROPOSAL_ID
90 ,PERSON_ID
91 ,PARTY_ID
92 ,SUPPORT_TYPE
93 ,PROPOSAL_AWARD_ID
94 ,PROPOSAL_AWARD_NUMBER
95 ,PROPOSAL_AWARD_TITLE
96 ,PI_PERSON_ID
97 ,PI_PARTY_ID
98 ,SPONSOR_ID
99 ,PROJECT_LOCATION
100 ,LOCATION_PARTY_ID
101 ,START_DATE
102 ,END_DATE
103 ,PERCENT_EFFORT
104 ,MAJOR_GOALS
105 ,OVERLAP
106 ,ANNUAL_DIRECT_COST
107 ,TOTAL_COST
108 ,CALENDAR_START_DATE
109 ,CALENDAR_END_DATE
110 ,ACADEMIC_START_DATE
111 ,ACADEMIC_END_DATE
112 ,SUMMER_START_DATE
113 ,SUMMER_END_DATE
114 ,ATTRIBUTE_CATEGORY
115 ,ATTRIBUTE1
116 ,ATTRIBUTE2
117 ,ATTRIBUTE3
118 ,ATTRIBUTE4
119 ,ATTRIBUTE5
120 ,ATTRIBUTE6
121 ,ATTRIBUTE7
122 ,ATTRIBUTE8
123 ,ATTRIBUTE9
124 ,ATTRIBUTE10
125 ,ATTRIBUTE11
126 ,ATTRIBUTE12
127 ,ATTRIBUTE13
128 ,ATTRIBUTE14
129 ,ATTRIBUTE15
130 ,last_update_date
131 ,last_updated_by
132 ,creation_date
133 ,created_by
134 ,last_update_login
135 ,record_version_number
136 ,sequence_number
137 ) values (
138 IGW_PROP_PERSON_SUPPORT_S.NEXTVAL
139 ,P_PROPOSAL_ID
140 ,P_PERSON_ID
141 ,P_PARTY_ID
142 ,P_SUPPORT_TYPE
143 ,P_PROPOSAL_AWARD_ID
144 ,P_PROPOSAL_AWARD_NUMBER
145 ,P_PROPOSAL_AWARD_TITLE
146 ,P_PI_PERSON_ID
147 ,P_PI_PARTY_ID
148 ,P_SPONSOR_ID
149 ,P_PROJECT_LOCATION
150 ,P_LOCATION_PARTY_ID
151 ,P_START_DATE
152 ,P_END_DATE
153 ,P_PERCENT_EFFORT
154 ,P_MAJOR_GOALS
155 ,P_OVERLAP
156 ,P_ANNUAL_DIRECT_COST
157 ,P_TOTAL_COST
158 ,P_CALENDAR_START_DATE
159 ,P_CALENDAR_END_DATE
160 ,P_ACADEMIC_START_DATE
161 ,P_ACADEMIC_END_DATE
162 ,P_SUMMER_START_DATE
163 ,P_SUMMER_END_DATE
164 ,P_ATTRIBUTE_CATEGORY
165 ,P_ATTRIBUTE1
166 ,P_ATTRIBUTE2
167 ,P_ATTRIBUTE3
168 ,P_ATTRIBUTE4
169 ,P_ATTRIBUTE5
170 ,P_ATTRIBUTE6
171 ,P_ATTRIBUTE7
172 ,P_ATTRIBUTE8
173 ,P_ATTRIBUTE9
174 ,P_ATTRIBUTE10
175 ,P_ATTRIBUTE11
176 ,P_ATTRIBUTE12
177 ,P_ATTRIBUTE13
178 ,P_ATTRIBUTE14
179 ,P_ATTRIBUTE15
180 ,l_last_update_date
181 ,l_last_updated_by
182 ,l_last_update_date
183 ,l_last_updated_by
184 ,l_last_update_login
185 ,1
186 ,P_SEQUENCE_NUMBER
187 )
188 RETURNING PROP_PERSON_SUPPORT_ID INTO L_PROP_PERSON_SUPPORT_ID;
189
190 open c;
191 fetch c into x_rowid;
192 if (c%notfound) then
193 close c;
194 raise no_data_found;
195 end if;
196 close c;
197 X_PROP_PERSON_SUPPORT_ID := L_PROP_PERSON_SUPPORT_ID;
198
199 EXCEPTION
200 when others then
201 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSON_SUPPORT_TBH',
202 p_procedure_name => 'INSERT_ROW',
203 p_error_text => SUBSTRB(SQLERRM, 1, 240));
204 x_return_status := fnd_api.g_ret_sts_unexp_error;
205 raise;
206
207 END INSERT_ROW;
208 ----------------------------------------------------------------------------------------------------
209
210 PROCEDURE UPDATE_ROW (
211 X_ROWID IN VARCHAR2,
212 P_PROP_PERSON_SUPPORT_ID IN NUMBER,
213 P_PROPOSAL_ID IN NUMBER,
214 P_PERSON_ID IN NUMBER,
215 P_PARTY_ID IN NUMBER,
216 P_SUPPORT_TYPE IN VARCHAR2,
217 P_PROPOSAL_AWARD_ID IN NUMBER,
218 P_PROPOSAL_AWARD_NUMBER IN VARCHAR2,
219 P_PROPOSAL_AWARD_TITLE IN VARCHAR2,
220 P_PI_PERSON_ID IN NUMBER,
221 P_PI_PARTY_ID IN NUMBER,
222 P_SPONSOR_ID IN NUMBER,
223 P_PROJECT_LOCATION IN VARCHAR2,
224 P_LOCATION_PARTY_ID IN NUMBER,
225 P_START_DATE IN DATE,
226 P_END_DATE IN DATE,
227 P_PERCENT_EFFORT IN NUMBER,
228 P_MAJOR_GOALS IN VARCHAR2,
229 P_OVERLAP IN VARCHAR2,
230 P_ANNUAL_DIRECT_COST IN NUMBER,
231 P_TOTAL_COST IN NUMBER,
232 P_CALENDAR_START_DATE IN DATE,
233 P_CALENDAR_END_DATE IN DATE,
234 P_ACADEMIC_START_DATE IN DATE,
235 P_ACADEMIC_END_DATE IN DATE,
236 P_SUMMER_START_DATE IN DATE,
237 P_SUMMER_END_DATE IN DATE,
238 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
239 P_ATTRIBUTE1 IN VARCHAR2,
240 P_ATTRIBUTE2 IN VARCHAR2,
241 P_ATTRIBUTE3 IN VARCHAR2,
242 P_ATTRIBUTE4 IN VARCHAR2,
243 P_ATTRIBUTE5 IN VARCHAR2,
244 P_ATTRIBUTE6 IN VARCHAR2,
245 P_ATTRIBUTE7 IN VARCHAR2,
246 P_ATTRIBUTE8 IN VARCHAR2,
247 P_ATTRIBUTE9 IN VARCHAR2,
248 P_ATTRIBUTE10 IN VARCHAR2,
249 P_ATTRIBUTE11 IN VARCHAR2,
250 P_ATTRIBUTE12 IN VARCHAR2,
251 P_ATTRIBUTE13 IN VARCHAR2,
252 P_ATTRIBUTE14 IN VARCHAR2,
253 P_ATTRIBUTE15 IN VARCHAR2,
254 P_MODE IN VARCHAR2,
255 P_RECORD_VERSION_NUMBER IN NUMBER,
256 P_SEQUENCE_NUMBER IN NUMBER,
257 X_RETURN_STATUS OUT NOCOPY VARCHAR2
258 ) is
259
260 l_last_update_date DATE;
261 l_last_updated_by NUMBER;
262 l_last_update_login NUMBER;
263
264 BEGIN
265 x_return_status := fnd_api.g_ret_sts_success;
266
267
268 l_last_update_date := SYSDATE;
269 if(p_mode = 'I') then
270 l_last_updated_by := 1;
271 l_last_update_login := 0;
272 elsif (p_mode = 'R') then
273 l_last_updated_by := FND_GLOBAL.USER_ID;
274
275 if l_last_updated_by is NULL then
276 l_last_updated_by := -1;
277 end if;
278
279 l_last_update_login := FND_GLOBAL.LOGIN_ID;
280
281 if l_last_update_login is NULL then
282 l_last_update_login := -1;
283 end if;
284 else
285 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
286 app_exception.raise_exception;
287 end if;
288
289 update IGW_PROP_PERSON_SUPPORT set
290 PROP_PERSON_SUPPORT_ID = P_PROP_PERSON_SUPPORT_ID
291 ,PROPOSAL_ID = P_PROPOSAL_ID
292 ,PERSON_ID = P_PERSON_ID
293 ,PARTY_ID = P_PARTY_ID
294 ,SUPPORT_TYPE = P_SUPPORT_TYPE
295 ,PROPOSAL_AWARD_ID = P_PROPOSAL_AWARD_ID
296 ,PROPOSAL_AWARD_NUMBER = P_PROPOSAL_AWARD_NUMBER
297 ,PROPOSAL_AWARD_TITLE = P_PROPOSAL_AWARD_TITLE
298 ,PI_PERSON_ID = P_PI_PERSON_ID
299 ,PI_PARTY_ID = P_PI_PARTY_ID
300 ,SPONSOR_ID = P_SPONSOR_ID
301 ,PROJECT_LOCATION = P_PROJECT_LOCATION
302 ,LOCATION_PARTY_ID = P_LOCATION_PARTY_ID
303 ,START_DATE = P_START_DATE
304 ,END_DATE = P_END_DATE
305 ,PERCENT_EFFORT = P_PERCENT_EFFORT
306 ,MAJOR_GOALS = P_MAJOR_GOALS
307 ,OVERLAP = P_OVERLAP
308 ,ANNUAL_DIRECT_COST = P_ANNUAL_DIRECT_COST
309 ,TOTAL_COST = P_TOTAL_COST
310 ,CALENDAR_START_DATE = P_CALENDAR_START_DATE
311 ,CALENDAR_END_DATE = P_CALENDAR_END_DATE
312 ,ACADEMIC_START_DATE = P_ACADEMIC_START_DATE
313 ,ACADEMIC_END_DATE = P_ACADEMIC_END_DATE
314 ,SUMMER_START_DATE = P_SUMMER_START_DATE
315 ,SUMMER_END_DATE = P_SUMMER_END_DATE
316 ,ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY
317 ,ATTRIBUTE1 = P_ATTRIBUTE1
318 ,ATTRIBUTE2 = P_ATTRIBUTE2
319 ,ATTRIBUTE3 = P_ATTRIBUTE3
320 ,ATTRIBUTE4 = P_ATTRIBUTE4
321 ,ATTRIBUTE5 = P_ATTRIBUTE5
322 ,ATTRIBUTE6 = P_ATTRIBUTE6
323 ,ATTRIBUTE7 = P_ATTRIBUTE7
324 ,ATTRIBUTE8 = P_ATTRIBUTE8
325 ,ATTRIBUTE9 = P_ATTRIBUTE9
326 ,ATTRIBUTE10 = P_ATTRIBUTE10
327 ,ATTRIBUTE11 = P_ATTRIBUTE11
328 ,ATTRIBUTE12 = P_ATTRIBUTE12
329 ,ATTRIBUTE13 = P_ATTRIBUTE13
330 ,ATTRIBUTE14 = P_ATTRIBUTE14
331 ,ATTRIBUTE15 = P_ATTRIBUTE15
332 ,last_update_date = l_last_update_date
333 ,last_updated_by = l_last_updated_by
334 ,last_update_login = l_last_update_login
335 ,record_version_number = record_version_number + 1
336 ,sequence_number = P_SEQUENCE_NUMBER
337 where rowid = x_rowid
338 and record_version_number = p_record_version_number;
339
340 if (sql%notfound) then
341 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
342 fnd_msg_pub.Add;
343 x_return_status := 'E';
344 end if;
345
346 EXCEPTION
347 when others then
348 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSON_SUPPORT_TBH',
349 p_procedure_name => 'UPDATE_ROW',
350 p_error_text => SUBSTRB(SQLERRM, 1, 240));
351 x_return_status := fnd_api.g_ret_sts_unexp_error;
352 raise;
353
354 END UPDATE_ROW;
355
356 ----------------------------------------------------------------------------------------------------
357
358 PROCEDURE DELETE_ROW (
359 x_rowid in VARCHAR2,
360 p_record_version_number in NUMBER,
361 x_return_status out NOCOPY VARCHAR2
362 ) is
363
364 BEGIN
365 x_return_status := fnd_api.g_ret_sts_success;
366
367 delete from IGW_PROP_PERSON_SUPPORT
368 where rowid = x_rowid
369 and record_version_number = p_record_version_number;
370
371 if (sql%notfound) then
372 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
373 fnd_msg_pub.Add;
374 x_return_status := 'E';
375 end if;
376
377
378 EXCEPTION
379 when others then
380 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSONS_SUPPORT_TBH',
381 p_procedure_name => 'DELETE_ROW',
382 p_error_text => SUBSTRB(SQLERRM, 1, 240));
383 x_return_status := fnd_api.g_ret_sts_unexp_error;
384 raise;
385
386 END DELETE_ROW;
387
388 ------------------------------------------------------------------------------------------------------
389
390
391 /* procedure ADD_ROW (
392 X_ROWID in out NOCOPY VARCHAR2,
393 X_RULE_ID in NUMBER,
394 X_RULE_SEQUENCE_NUMBER in NUMBER,
395 X_ORGANIZATION_ID in NUMBER,
396 X_RULE_NAME in VARCHAR2,
397 X_RULE_TYPE in VARCHAR2,
398 X_MAP_ID in NUMBER,
399 X_VALID_FLAG in VARCHAR2,
400 X_START_DATE_ACTIVE in DATE,
401 X_END_DATE_ACTIVE in DATE,
402 X_MODE in VARCHAR2
403 ) is
404 cursor c1 is select rowid from IGW_BUSINESS_RULES_ALL
405 where RULE_ID = X_RULE_ID
406 ;
407 dummy c1%rowtype;
408 begin
409 open c1;
410 fetch c1 into dummy;
411 if (c1%notfound) then
412 close c1;
413 INSERT_ROW (
414 X_ROWID,
415 X_RULE_ID,
416 X_RULE_SEQUENCE_NUMBER,
417 X_ORGANIZATION_ID,
418 X_RULE_NAME,
419 X_RULE_TYPE,
420 X_MAP_ID,
421 X_VALID_FLAG,
422 X_START_DATE_ACTIVE,
423 X_END_DATE_ACTIVE,
424 X_MODE);
425 return;
426 end if;
427 close c1;
428 UPDATE_ROW (
429 X_RULE_ID,
430 X_RULE_SEQUENCE_NUMBER,
431 X_ORGANIZATION_ID,
432 X_RULE_NAME,
433 X_RULE_TYPE,
434 X_MAP_ID,
435 X_VALID_FLAG,
436 X_START_DATE_ACTIVE,
437 X_END_DATE_ACTIVE,
438 X_MODE);
439 end ADD_ROW; */
440
441
442 /* ---------------------- WILL NOT BE USED IN SELF SERVICE -----------------------------------------
443 procedure LOCK_ROW (
444 X_ROWID in VARCHAR2,
445 X_RULE_ID in NUMBER,
446 X_RULE_SEQUENCE_NUMBER in NUMBER,
447 X_ORGANIZATION_ID in NUMBER,
448 X_RULE_NAME in VARCHAR2,
449 X_RULE_TYPE in VARCHAR2,
450 X_MAP_ID in NUMBER,
451 X_VALID_FLAG in VARCHAR2,
452 X_START_DATE_ACTIVE in DATE,
453 X_END_DATE_ACTIVE in DATE
454 ) is
455 cursor c1 is select *
456 from IGW_BUSINESS_RULES_ALL
457 where ROWID = X_ROWID
458 for update of RULE_ID nowait;
459 tlinfo c1%rowtype;
460
461 begin
462 open c1;
463 fetch c1 into tlinfo;
464 if (c1%notfound) then
465 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
466 app_exception.raise_exception;
467 close c1;
468 return;
469 end if;
470 close c1;
471
472 if (
473 (tlinfo.RULE_ID = X_RULE_ID)
474 AND ((tlinfo.RULE_SEQUENCE_NUMBER = X_RULE_SEQUENCE_NUMBER)
475 OR ((tlinfo.RULE_SEQUENCE_NUMBER is null)
476 AND (X_RULE_SEQUENCE_NUMBER is null)))
477 AND (tlinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
478 AND (tlinfo.RULE_NAME = X_RULE_NAME)
479 AND (tlinfo.RULE_TYPE = X_RULE_TYPE)
480 AND ((tlinfo.MAP_ID = X_MAP_ID)
481 OR ((tlinfo.MAP_ID is null)
482 AND (X_MAP_ID is null)))
483 AND ((tlinfo.VALID_FLAG = X_VALID_FLAG)
484 OR ((tlinfo.VALID_FLAG is null)
485 AND (X_VALID_FLAG is null)))
486 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
487 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
488 OR ((tlinfo.END_DATE_ACTIVE is null)
489 AND (X_END_DATE_ACTIVE is null)))
490 ) then
491 null;
492 else
493 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
494 app_exception.raise_exception;
495 end if;
496 return;
497 end LOCK_ROW;
498 */
499
500 END IGW_PROP_PERSON_SUPPORT_TBH;