[Home] [Help]
PACKAGE BODY: APPS.IGW_PROPOSALS_ALL_PKG
Source
1 package body IGW_PROPOSALS_ALL_PKG as
2 /* $Header: igwpr10b.pls 115.19 2002/03/28 19:13:23 pkm ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out VARCHAR2,
5 X_PROPOSAL_ID in NUMBER,
6 X_SPONSOR_ACTION_CODE in VARCHAR2,
7 X_SPONSOR_ACTION_DATE in DATE,
8 X_SPONSOR_ACTION_COMMENTS in VARCHAR2,
9 X_AWARD_AMOUNT in NUMBER,
10 X_PROPOSAL_NUMBER in VARCHAR2,
11 X_LEAD_ORGANIZATION_ID in NUMBER,
12 X_PROPOSAL_STATUS in VARCHAR2,
13 X_PROPOSAL_START_DATE in DATE,
14 X_PROPOSAL_END_DATE in DATE,
15 X_PROPOSAL_TITLE in VARCHAR2,
16 X_PROPOSAL_TYPE_CODE in VARCHAR2,
17 X_AWARD_NUMBER in VARCHAR2,
18 X_ORIGINAL_PROPOSAL_NUMBER in VARCHAR2,
19 X_ORIGINAL_AWARD_NUMBER in VARCHAR2,
20 X_ORIGINAL_PROPOSAL_START_DATE in DATE,
21 X_ORIGINAL_PROPOSAL_END_DATE in DATE,
22 X_ACTIVITY_TYPE_CODE in VARCHAR2,
23 X_SPONSOR_ID in NUMBER,
24 X_FUNDING_SPONSOR_UNIT in VARCHAR2,
25 X_ORIGINAL_SPONSOR_ID in NUMBER,
26 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
27 X_NOTICE_OF_OPPORTUNITY_CODE in VARCHAR2,
28 X_PROGRAM_NUMBER in VARCHAR2,
29 X_PROGRAM_TITLE in VARCHAR2,
30 X_PROGRAM_URL in VARCHAR2,
31 X_SUBMITTING_ORGANIZATION_ID in NUMBER,
32 X_SIGNING_OFFICIAL_ID in NUMBER,
33 X_ADMIN_OFFICIAL_ID in NUMBER,
34 X_DEADLINE_DATE in DATE,
35 X_DEADLINE_TYPE in VARCHAR2,
36 X_LETTER_OF_INTENT_DUE_DATE in DATE,
37 X_ATTRIBUTE_CATEGORY in VARCHAR2,
38 X_ATTRIBUTE1 in VARCHAR2,
39 X_ATTRIBUTE2 in VARCHAR2,
40 X_ATTRIBUTE3 in VARCHAR2,
41 X_ATTRIBUTE4 in VARCHAR2,
42 X_ATTRIBUTE5 in VARCHAR2,
43 X_ATTRIBUTE6 in VARCHAR2,
44 X_ATTRIBUTE7 in VARCHAR2,
45 X_ATTRIBUTE8 in VARCHAR2,
46 X_ATTRIBUTE9 in VARCHAR2,
47 X_ATTRIBUTE10 in VARCHAR2,
48 X_ATTRIBUTE11 in VARCHAR2,
49 X_ATTRIBUTE12 in VARCHAR2,
50 X_ATTRIBUTE13 in VARCHAR2,
51 X_ATTRIBUTE14 in VARCHAR2,
52 X_ATTRIBUTE15 in VARCHAR2,
53 X_MODE in VARCHAR2 default 'R'
54 ) is
55 cursor C is select ROWID from IGW_PROPOSALS_ALL
56 where PROPOSAL_ID = X_PROPOSAL_ID;
57 X_LAST_UPDATE_DATE DATE;
58 X_LAST_UPDATED_BY NUMBER;
59 X_LAST_UPDATE_LOGIN NUMBER;
60 begin
61 X_LAST_UPDATE_DATE := SYSDATE;
62 if(X_MODE = 'I') then
63 X_LAST_UPDATED_BY := 1;
64 X_LAST_UPDATE_LOGIN := 0;
65 elsif (X_MODE = 'R') then
66 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
67 if X_LAST_UPDATED_BY is NULL then
68 X_LAST_UPDATED_BY := -1;
69 end if;
70 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
71 if X_LAST_UPDATE_LOGIN is NULL then
72 X_LAST_UPDATE_LOGIN := -1;
73 end if;
74 else
75 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
76 app_exception.raise_exception;
77 end if;
78 insert into IGW_PROPOSALS_ALL (
79 SPONSOR_ACTION_CODE,
80 SPONSOR_ACTION_DATE,
81 SPONSOR_ACTION_COMMENTS,
82 AWARD_AMOUNT,
83 PROPOSAL_ID,
84 PROPOSAL_NUMBER,
85 LEAD_ORGANIZATION_ID,
86 PROPOSAL_STATUS,
87 PROPOSAL_START_DATE,
88 PROPOSAL_END_DATE,
89 PROPOSAL_TITLE,
90 PROPOSAL_TYPE_CODE,
91 AWARD_NUMBER,
92 ORIGINAL_PROPOSAL_NUMBER,
93 ORIGINAL_AWARD_NUMBER,
94 ORIGINAL_PROPOSAL_START_DATE,
95 ORIGINAL_PROPOSAL_END_DATE,
96 ACTIVITY_TYPE_CODE,
97 SPONSOR_ID,
98 FUNDING_SPONSOR_UNIT,
99 ORIGINAL_SPONSOR_ID,
100 SPONSOR_PROPOSAL_NUMBER,
101 NOTICE_OF_OPPORTUNITY_CODE,
102 PROGRAM_NUMBER,
103 PROGRAM_TITLE,
104 PROGRAM_URL,
105 SUBMITTING_ORGANIZATION_ID,
106 SIGNING_OFFICIAL_ID,
107 ADMIN_OFFICIAL_ID,
108 DEADLINE_DATE,
109 DEADLINE_TYPE,
110 LETTER_OF_INTENT_DUE_DATE,
111 ATTRIBUTE_CATEGORY,
112 ATTRIBUTE1,
113 ATTRIBUTE2,
114 ATTRIBUTE3,
115 ATTRIBUTE4,
116 ATTRIBUTE5,
117 ATTRIBUTE6,
118 ATTRIBUTE7,
119 ATTRIBUTE8,
120 ATTRIBUTE9,
121 ATTRIBUTE10,
122 ATTRIBUTE11,
123 ATTRIBUTE12,
124 ATTRIBUTE13,
125 ATTRIBUTE14,
126 ATTRIBUTE15,
127 CREATION_DATE,
128 CREATED_BY,
129 LAST_UPDATE_DATE,
130 LAST_UPDATED_BY,
131 LAST_UPDATE_LOGIN
132 ) values (
133 X_SPONSOR_ACTION_CODE,
134 X_SPONSOR_ACTION_DATE,
135 X_SPONSOR_ACTION_COMMENTS,
136 X_AWARD_AMOUNT,
137 X_PROPOSAL_ID,
138 X_PROPOSAL_NUMBER,
139 X_LEAD_ORGANIZATION_ID,
140 X_PROPOSAL_STATUS,
141 X_PROPOSAL_START_DATE,
142 X_PROPOSAL_END_DATE,
143 X_PROPOSAL_TITLE,
144 X_PROPOSAL_TYPE_CODE,
145 X_AWARD_NUMBER,
146 X_ORIGINAL_PROPOSAL_NUMBER,
147 X_ORIGINAL_AWARD_NUMBER,
148 X_ORIGINAL_PROPOSAL_START_DATE,
149 X_ORIGINAL_PROPOSAL_END_DATE,
150 X_ACTIVITY_TYPE_CODE,
151 X_SPONSOR_ID,
152 X_FUNDING_SPONSOR_UNIT,
153 X_ORIGINAL_SPONSOR_ID,
154 X_SPONSOR_PROPOSAL_NUMBER,
155 X_NOTICE_OF_OPPORTUNITY_CODE,
156 X_PROGRAM_NUMBER,
157 X_PROGRAM_TITLE,
158 X_PROGRAM_URL,
159 X_SUBMITTING_ORGANIZATION_ID,
160 X_SIGNING_OFFICIAL_ID,
161 X_ADMIN_OFFICIAL_ID,
162 X_DEADLINE_DATE,
163 X_DEADLINE_TYPE,
164 X_LETTER_OF_INTENT_DUE_DATE,
165 X_ATTRIBUTE_CATEGORY,
166 X_ATTRIBUTE1,
167 X_ATTRIBUTE2,
168 X_ATTRIBUTE3,
169 X_ATTRIBUTE4,
170 X_ATTRIBUTE5,
171 X_ATTRIBUTE6,
172 X_ATTRIBUTE7,
173 X_ATTRIBUTE8,
174 X_ATTRIBUTE9,
175 X_ATTRIBUTE10,
176 X_ATTRIBUTE11,
177 X_ATTRIBUTE12,
178 X_ATTRIBUTE13,
179 X_ATTRIBUTE14,
180 X_ATTRIBUTE15,
181 X_LAST_UPDATE_DATE,
182 X_LAST_UPDATED_BY,
183 X_LAST_UPDATE_DATE,
184 X_LAST_UPDATED_BY,
185 X_LAST_UPDATE_LOGIN
186 );
187
188 open c;
189 fetch c into X_ROWID;
190 if (c%notfound) then
191 close c;
192 raise no_data_found;
193 end if;
194 close c;
195
196 end INSERT_ROW;
197
198 procedure LOCK_ROW (
199 X_ROWID in VARCHAR2,
200 X_PROPOSAL_ID in NUMBER,
201 X_SPONSOR_ACTION_CODE in VARCHAR2,
202 X_SPONSOR_ACTION_DATE in DATE,
203 X_SPONSOR_ACTION_COMMENTS in VARCHAR2,
204 X_AWARD_AMOUNT in NUMBER,
205 X_PROPOSAL_NUMBER in VARCHAR2,
206 X_LEAD_ORGANIZATION_ID in NUMBER,
207 X_PROPOSAL_STATUS in VARCHAR2,
208 X_PROPOSAL_START_DATE in DATE,
209 X_PROPOSAL_END_DATE in DATE,
210 X_PROPOSAL_TITLE in VARCHAR2,
211 X_PROPOSAL_TYPE_CODE in VARCHAR2,
212 X_AWARD_NUMBER in VARCHAR2,
213 X_ORIGINAL_PROPOSAL_NUMBER in VARCHAR2,
214 X_ORIGINAL_AWARD_NUMBER in VARCHAR2,
215 X_ORIGINAL_PROPOSAL_START_DATE in DATE,
216 X_ORIGINAL_PROPOSAL_END_DATE in DATE,
217 X_ACTIVITY_TYPE_CODE in VARCHAR2,
218 X_SPONSOR_ID in NUMBER,
219 X_FUNDING_SPONSOR_UNIT in VARCHAR2,
220 X_ORIGINAL_SPONSOR_ID in NUMBER,
221 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
222 X_NOTICE_OF_OPPORTUNITY_CODE in VARCHAR2,
223 X_PROGRAM_NUMBER in VARCHAR2,
224 X_PROGRAM_TITLE in VARCHAR2,
225 X_PROGRAM_URL in VARCHAR2,
226 X_SUBMITTING_ORGANIZATION_ID in NUMBER,
227 X_SIGNING_OFFICIAL_ID in NUMBER,
228 X_ADMIN_OFFICIAL_ID in NUMBER,
229 X_DEADLINE_DATE in DATE,
230 X_DEADLINE_TYPE in VARCHAR2,
231 X_LETTER_OF_INTENT_DUE_DATE in DATE,
232 X_ATTRIBUTE_CATEGORY in VARCHAR2,
233 X_ATTRIBUTE1 in VARCHAR2,
234 X_ATTRIBUTE2 in VARCHAR2,
235 X_ATTRIBUTE3 in VARCHAR2,
236 X_ATTRIBUTE4 in VARCHAR2,
237 X_ATTRIBUTE5 in VARCHAR2,
238 X_ATTRIBUTE6 in VARCHAR2,
239 X_ATTRIBUTE7 in VARCHAR2,
240 X_ATTRIBUTE8 in VARCHAR2,
241 X_ATTRIBUTE9 in VARCHAR2,
242 X_ATTRIBUTE10 in VARCHAR2,
243 X_ATTRIBUTE11 in VARCHAR2,
244 X_ATTRIBUTE12 in VARCHAR2,
245 X_ATTRIBUTE13 in VARCHAR2,
246 X_ATTRIBUTE14 in VARCHAR2,
247 X_ATTRIBUTE15 in VARCHAR2
248 ) is
249 cursor c1 is select
250 SPONSOR_ACTION_CODE,
251 SPONSOR_ACTION_DATE,
252 SPONSOR_ACTION_COMMENTS,
253 AWARD_AMOUNT,
254 PROPOSAL_NUMBER,
255 LEAD_ORGANIZATION_ID,
256 PROPOSAL_STATUS,
257 PROPOSAL_START_DATE,
258 PROPOSAL_END_DATE,
259 PROPOSAL_TITLE,
260 PROPOSAL_TYPE_CODE,
261 AWARD_NUMBER,
262 ORIGINAL_PROPOSAL_NUMBER,
263 ORIGINAL_AWARD_NUMBER,
264 ORIGINAL_PROPOSAL_START_DATE,
265 ORIGINAL_PROPOSAL_END_DATE,
266 ACTIVITY_TYPE_CODE,
267 SPONSOR_ID,
268 FUNDING_SPONSOR_UNIT,
269 ORIGINAL_SPONSOR_ID,
270 SPONSOR_PROPOSAL_NUMBER,
271 NOTICE_OF_OPPORTUNITY_CODE,
272 PROGRAM_NUMBER,
273 PROGRAM_TITLE,
274 PROGRAM_URL,
275 SUBMITTING_ORGANIZATION_ID,
276 SIGNING_OFFICIAL_ID,
277 ADMIN_OFFICIAL_ID,
278 DEADLINE_DATE,
279 DEADLINE_TYPE,
280 LETTER_OF_INTENT_DUE_DATE,
281 ATTRIBUTE_CATEGORY,
282 ATTRIBUTE1,
283 ATTRIBUTE2,
284 ATTRIBUTE3,
285 ATTRIBUTE4,
286 ATTRIBUTE5,
287 ATTRIBUTE6,
288 ATTRIBUTE7,
289 ATTRIBUTE8,
290 ATTRIBUTE9,
291 ATTRIBUTE10,
292 ATTRIBUTE11,
293 ATTRIBUTE12,
294 ATTRIBUTE13,
295 ATTRIBUTE14,
296 ATTRIBUTE15
297 from IGW_PROPOSALS_ALL
298 where ROWID = X_ROWID
299 for update of PROPOSAL_ID nowait;
300 tlinfo c1%rowtype;
301
302 begin
303 open c1;
304 fetch c1 into tlinfo;
305 if (c1%notfound) then
306 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
307 app_exception.raise_exception;
308 close c1;
309 return;
310 end if;
311 close c1;
312
313 if ( ((tlinfo.SPONSOR_ACTION_CODE = X_SPONSOR_ACTION_CODE)
314 OR ((tlinfo.SPONSOR_ACTION_CODE is null)
315 AND (X_SPONSOR_ACTION_CODE is null)))
316 AND ((tlinfo.SPONSOR_ACTION_DATE = X_SPONSOR_ACTION_DATE)
317 OR ((tlinfo.SPONSOR_ACTION_DATE is null)
318 AND (X_SPONSOR_ACTION_DATE is null)))
319 AND ((tlinfo.SPONSOR_ACTION_COMMENTS = X_SPONSOR_ACTION_COMMENTS)
320 OR ((tlinfo.SPONSOR_ACTION_COMMENTS is null)
321 AND (X_SPONSOR_ACTION_COMMENTS is null)))
322 AND ((tlinfo.AWARD_AMOUNT = X_AWARD_AMOUNT)
323 OR ((tlinfo.AWARD_AMOUNT is null)
324 AND (X_AWARD_AMOUNT is null)))
325 AND (tlinfo.PROPOSAL_NUMBER = X_PROPOSAL_NUMBER)
326 AND (tlinfo.LEAD_ORGANIZATION_ID = X_LEAD_ORGANIZATION_ID)
327 AND ((tlinfo.PROPOSAL_STATUS = X_PROPOSAL_STATUS)
328 OR ((tlinfo.PROPOSAL_STATUS is null)
329 AND (X_PROPOSAL_STATUS is null)))
330 AND (tlinfo.PROPOSAL_START_DATE = X_PROPOSAL_START_DATE)
331 AND (tlinfo.PROPOSAL_END_DATE = X_PROPOSAL_END_DATE)
332 AND ((tlinfo.PROPOSAL_TITLE = X_PROPOSAL_TITLE)
333 OR ((tlinfo.PROPOSAL_TITLE is null)
334 AND (X_PROPOSAL_TITLE is null)))
335 AND (tlinfo.PROPOSAL_TYPE_CODE = X_PROPOSAL_TYPE_CODE)
336 AND ((tlinfo.AWARD_NUMBER = X_AWARD_NUMBER)
337 OR ((tlinfo.AWARD_NUMBER is null)
338 AND (X_AWARD_NUMBER is null)))
339 AND ((tlinfo.ORIGINAL_PROPOSAL_NUMBER = X_ORIGINAL_PROPOSAL_NUMBER)
340 OR ((tlinfo.ORIGINAL_PROPOSAL_NUMBER is null)
341 AND (X_ORIGINAL_PROPOSAL_NUMBER is null)))
342 AND ((tlinfo.ORIGINAL_AWARD_NUMBER = X_ORIGINAL_AWARD_NUMBER)
343 OR ((tlinfo.ORIGINAL_AWARD_NUMBER is null)
344 AND (X_ORIGINAL_AWARD_NUMBER is null)))
345 AND ((tlinfo.ORIGINAL_PROPOSAL_START_DATE = X_ORIGINAL_PROPOSAL_START_DATE)
346 OR ((tlinfo.ORIGINAL_PROPOSAL_START_DATE is null)
347 AND (X_ORIGINAL_PROPOSAL_START_DATE is null)))
348 AND ((tlinfo.ORIGINAL_PROPOSAL_END_DATE = X_ORIGINAL_PROPOSAL_END_DATE)
349 OR ((tlinfo.ORIGINAL_PROPOSAL_END_DATE is null)
350 AND (X_ORIGINAL_PROPOSAL_END_DATE is null)))
351 AND (tlinfo.ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE)
352 AND ((tlinfo.SPONSOR_ID = X_SPONSOR_ID)
353 OR ((tlinfo.SPONSOR_ID is null)
354 AND (X_SPONSOR_ID is null)))
355 AND ((tlinfo.FUNDING_SPONSOR_UNIT = X_FUNDING_SPONSOR_UNIT)
356 OR ((tlinfo.FUNDING_SPONSOR_UNIT is null)
357 AND (X_FUNDING_SPONSOR_UNIT is null)))
358 AND ((tlinfo.ORIGINAL_SPONSOR_ID = X_ORIGINAL_SPONSOR_ID)
359 OR ((tlinfo.ORIGINAL_SPONSOR_ID is null)
360 AND (X_ORIGINAL_SPONSOR_ID is null)))
361 AND ((tlinfo.SPONSOR_PROPOSAL_NUMBER = X_SPONSOR_PROPOSAL_NUMBER)
362 OR ((tlinfo.SPONSOR_PROPOSAL_NUMBER is null)
363 AND (X_SPONSOR_PROPOSAL_NUMBER is null)))
364 AND ((tlinfo.NOTICE_OF_OPPORTUNITY_CODE = X_NOTICE_OF_OPPORTUNITY_CODE)
365 OR ((tlinfo.NOTICE_OF_OPPORTUNITY_CODE is null)
366 AND (X_NOTICE_OF_OPPORTUNITY_CODE is null)))
367 AND ((tlinfo.PROGRAM_NUMBER = X_PROGRAM_NUMBER)
368 OR ((tlinfo.PROGRAM_NUMBER is null)
369 AND (X_PROGRAM_NUMBER is null)))
370 AND ((tlinfo.PROGRAM_TITLE = X_PROGRAM_TITLE)
371 OR ((tlinfo.PROGRAM_TITLE is null)
372 AND (X_PROGRAM_TITLE is null)))
373 AND ((tlinfo.PROGRAM_URL = X_PROGRAM_URL)
374 OR ((tlinfo.PROGRAM_URL is null)
375 AND (X_PROGRAM_URL is null)))
376 AND ((tlinfo.SUBMITTING_ORGANIZATION_ID = X_SUBMITTING_ORGANIZATION_ID)
377 OR ((tlinfo.SUBMITTING_ORGANIZATION_ID is null)
378 AND (X_SUBMITTING_ORGANIZATION_ID is null)))
379 AND ((tlinfo.SIGNING_OFFICIAL_ID = X_SIGNING_OFFICIAL_ID)
380 OR ((tlinfo.SIGNING_OFFICIAL_ID is null)
381 AND (X_SIGNING_OFFICIAL_ID is null)))
382 AND ((tlinfo.ADMIN_OFFICIAL_ID = X_ADMIN_OFFICIAL_ID)
383 OR ((tlinfo.ADMIN_OFFICIAL_ID is null)
384 AND (X_ADMIN_OFFICIAL_ID is null)))
385 AND ((tlinfo.DEADLINE_DATE = X_DEADLINE_DATE)
386 OR ((tlinfo.DEADLINE_DATE is null)
387 AND (X_DEADLINE_DATE is null)))
388 AND ((tlinfo.DEADLINE_TYPE = X_DEADLINE_TYPE)
389 OR ((tlinfo.DEADLINE_TYPE is null)
390 AND (X_DEADLINE_TYPE is null)))
391 AND ((tlinfo.LETTER_OF_INTENT_DUE_DATE = X_LETTER_OF_INTENT_DUE_DATE)
392 OR ((tlinfo.LETTER_OF_INTENT_DUE_DATE is null)
393 AND (X_LETTER_OF_INTENT_DUE_DATE is null)))
394 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
395 OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
396 AND (X_ATTRIBUTE_CATEGORY is null)))
397 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
398 OR ((tlinfo.ATTRIBUTE1 is null)
399 AND (X_ATTRIBUTE1 is null)))
400 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
401 OR ((tlinfo.ATTRIBUTE2 is null)
402 AND (X_ATTRIBUTE2 is null)))
403 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
404 OR ((tlinfo.ATTRIBUTE3 is null)
405 AND (X_ATTRIBUTE3 is null)))
406 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
407 OR ((tlinfo.ATTRIBUTE4 is null)
408 AND (X_ATTRIBUTE4 is null)))
409 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
410 OR ((tlinfo.ATTRIBUTE5 is null)
411 AND (X_ATTRIBUTE5 is null)))
412 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
413 OR ((tlinfo.ATTRIBUTE6 is null)
414 AND (X_ATTRIBUTE6 is null)))
415 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
416 OR ((tlinfo.ATTRIBUTE7 is null)
417 AND (X_ATTRIBUTE7 is null)))
418 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
419 OR ((tlinfo.ATTRIBUTE8 is null)
420 AND (X_ATTRIBUTE8 is null)))
424 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
421 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
422 OR ((tlinfo.ATTRIBUTE9 is null)
423 AND (X_ATTRIBUTE9 is null)))
425 OR ((tlinfo.ATTRIBUTE10 is null)
426 AND (X_ATTRIBUTE10 is null)))
427 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
428 OR ((tlinfo.ATTRIBUTE11 is null)
429 AND (X_ATTRIBUTE11 is null)))
430 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
431 OR ((tlinfo.ATTRIBUTE12 is null)
432 AND (X_ATTRIBUTE12 is null)))
433 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
434 OR ((tlinfo.ATTRIBUTE13 is null)
435 AND (X_ATTRIBUTE13 is null)))
436 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
437 OR ((tlinfo.ATTRIBUTE14 is null)
438 AND (X_ATTRIBUTE14 is null)))
439 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
440 OR ((tlinfo.ATTRIBUTE15 is null)
441 AND (X_ATTRIBUTE15 is null)))
442 ) then
443 null;
444 else
445 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
446 app_exception.raise_exception;
447 end if;
448 return;
449 end LOCK_ROW;
450
451 procedure UPDATE_ROW (
452 X_ROWID in VARCHAR2,
453 X_PROPOSAL_ID in NUMBER,
454 X_SPONSOR_ACTION_CODE in VARCHAR2,
455 X_SPONSOR_ACTION_DATE in DATE,
456 X_SPONSOR_ACTION_COMMENTS in VARCHAR2,
457 X_AWARD_AMOUNT in NUMBER,
458 X_PROPOSAL_NUMBER in VARCHAR2,
459 X_LEAD_ORGANIZATION_ID in NUMBER,
460 X_PROPOSAL_STATUS in VARCHAR2,
461 X_PROPOSAL_START_DATE in DATE,
462 X_PROPOSAL_END_DATE in DATE,
463 X_PROPOSAL_TITLE in VARCHAR2,
464 X_PROPOSAL_TYPE_CODE in VARCHAR2,
465 X_AWARD_NUMBER in VARCHAR2,
466 X_ORIGINAL_PROPOSAL_NUMBER in VARCHAR2,
467 X_ORIGINAL_AWARD_NUMBER in VARCHAR2,
468 X_ORIGINAL_PROPOSAL_START_DATE in DATE,
469 X_ORIGINAL_PROPOSAL_END_DATE in DATE,
470 X_ACTIVITY_TYPE_CODE in VARCHAR2,
471 X_SPONSOR_ID in NUMBER,
472 X_FUNDING_SPONSOR_UNIT in VARCHAR2,
473 X_ORIGINAL_SPONSOR_ID in NUMBER,
474 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
475 X_NOTICE_OF_OPPORTUNITY_CODE in VARCHAR2,
476 X_PROGRAM_NUMBER in VARCHAR2,
477 X_PROGRAM_TITLE in VARCHAR2,
478 X_PROGRAM_URL in VARCHAR2,
479 X_SUBMITTING_ORGANIZATION_ID in NUMBER,
480 X_SIGNING_OFFICIAL_ID in NUMBER,
481 X_ADMIN_OFFICIAL_ID in NUMBER,
482 X_DEADLINE_DATE in DATE,
483 X_DEADLINE_TYPE in VARCHAR2,
484 X_LETTER_OF_INTENT_DUE_DATE in DATE,
485 X_ATTRIBUTE_CATEGORY in VARCHAR2,
486 X_ATTRIBUTE1 in VARCHAR2,
487 X_ATTRIBUTE2 in VARCHAR2,
488 X_ATTRIBUTE3 in VARCHAR2,
489 X_ATTRIBUTE4 in VARCHAR2,
490 X_ATTRIBUTE5 in VARCHAR2,
491 X_ATTRIBUTE6 in VARCHAR2,
492 X_ATTRIBUTE7 in VARCHAR2,
493 X_ATTRIBUTE8 in VARCHAR2,
494 X_ATTRIBUTE9 in VARCHAR2,
495 X_ATTRIBUTE10 in VARCHAR2,
496 X_ATTRIBUTE11 in VARCHAR2,
497 X_ATTRIBUTE12 in VARCHAR2,
498 X_ATTRIBUTE13 in VARCHAR2,
499 X_ATTRIBUTE14 in VARCHAR2,
500 X_ATTRIBUTE15 in VARCHAR2,
501 X_MODE in VARCHAR2 default 'R'
502 ) is
503
504 X_LAST_UPDATE_DATE DATE;
505 X_LAST_UPDATED_BY NUMBER;
506 X_LAST_UPDATE_LOGIN NUMBER;
507
508 l_old_sponsor_action_date date;
509 l_old_sponsor_action_code varchar2(30);
510 l_old_sponsor_action_comments varchar2(2000);
511
512 begin
513 X_LAST_UPDATE_DATE := SYSDATE;
514 if(X_MODE = 'I') then
515 X_LAST_UPDATED_BY := 1;
516 X_LAST_UPDATE_LOGIN := 0;
517 elsif (X_MODE = 'R') then
518 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
519 if X_LAST_UPDATED_BY is NULL then
520 X_LAST_UPDATED_BY := -1;
521 end if;
522 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
523 if X_LAST_UPDATE_LOGIN is NULL then
524 X_LAST_UPDATE_LOGIN := -1;
525 end if;
526 else
527 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
528 app_exception.raise_exception;
529 end if;
530
531 select sponsor_action_date,
532 sponsor_action_code,
533 sponsor_action_comments
534 into l_old_sponsor_action_date,
535 l_old_sponsor_action_code,
536 l_old_sponsor_action_comments
537 from igw_proposals_all
538 where proposal_id = x_proposal_id;
539
540 update IGW_PROPOSALS_ALL set
541 SPONSOR_ACTION_CODE = X_SPONSOR_ACTION_CODE,
542 SPONSOR_ACTION_DATE = X_SPONSOR_ACTION_DATE,
543 SPONSOR_ACTION_COMMENTS = X_SPONSOR_ACTION_COMMENTS,
544 AWARD_AMOUNT = X_AWARD_AMOUNT,
545 PROPOSAL_NUMBER = X_PROPOSAL_NUMBER,
546 LEAD_ORGANIZATION_ID = X_LEAD_ORGANIZATION_ID,
547 PROPOSAL_STATUS = X_PROPOSAL_STATUS,
548 PROPOSAL_START_DATE = X_PROPOSAL_START_DATE,
549 PROPOSAL_END_DATE = X_PROPOSAL_END_DATE,
550 PROPOSAL_TITLE = X_PROPOSAL_TITLE,
551 PROPOSAL_TYPE_CODE = X_PROPOSAL_TYPE_CODE,
552 AWARD_NUMBER = X_AWARD_NUMBER,
553 ORIGINAL_PROPOSAL_NUMBER = X_ORIGINAL_PROPOSAL_NUMBER,
554 ORIGINAL_AWARD_NUMBER = X_ORIGINAL_AWARD_NUMBER,
555 ORIGINAL_PROPOSAL_START_DATE = X_ORIGINAL_PROPOSAL_START_DATE,
559 FUNDING_SPONSOR_UNIT = X_FUNDING_SPONSOR_UNIT,
556 ORIGINAL_PROPOSAL_END_DATE = X_ORIGINAL_PROPOSAL_END_DATE,
557 ACTIVITY_TYPE_CODE = X_ACTIVITY_TYPE_CODE,
558 SPONSOR_ID = X_SPONSOR_ID,
560 ORIGINAL_SPONSOR_ID = X_ORIGINAL_SPONSOR_ID,
561 SPONSOR_PROPOSAL_NUMBER = X_SPONSOR_PROPOSAL_NUMBER,
562 NOTICE_OF_OPPORTUNITY_CODE = X_NOTICE_OF_OPPORTUNITY_CODE,
563 PROGRAM_NUMBER = X_PROGRAM_NUMBER,
564 PROGRAM_TITLE = X_PROGRAM_TITLE,
565 PROGRAM_URL = X_PROGRAM_URL,
566 SUBMITTING_ORGANIZATION_ID = X_SUBMITTING_ORGANIZATION_ID,
567 SIGNING_OFFICIAL_ID = X_SIGNING_OFFICIAL_ID,
568 ADMIN_OFFICIAL_ID = X_ADMIN_OFFICIAL_ID,
569 DEADLINE_DATE = X_DEADLINE_DATE,
570 DEADLINE_TYPE = X_DEADLINE_TYPE,
571 LETTER_OF_INTENT_DUE_DATE = X_LETTER_OF_INTENT_DUE_DATE,
572 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
573 ATTRIBUTE1 = X_ATTRIBUTE1,
574 ATTRIBUTE2 = X_ATTRIBUTE2,
575 ATTRIBUTE3 = X_ATTRIBUTE3,
576 ATTRIBUTE4 = X_ATTRIBUTE4,
577 ATTRIBUTE5 = X_ATTRIBUTE5,
578 ATTRIBUTE6 = X_ATTRIBUTE6,
579 ATTRIBUTE7 = X_ATTRIBUTE7,
580 ATTRIBUTE8 = X_ATTRIBUTE8,
581 ATTRIBUTE9 = X_ATTRIBUTE9,
582 ATTRIBUTE10 = X_ATTRIBUTE10,
583 ATTRIBUTE11 = X_ATTRIBUTE11,
584 ATTRIBUTE12 = X_ATTRIBUTE12,
585 ATTRIBUTE13 = X_ATTRIBUTE13,
586 ATTRIBUTE14 = X_ATTRIBUTE14,
587 ATTRIBUTE15 = X_ATTRIBUTE15,
588 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
589 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
590 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
591 where ROWID = X_ROWID
592 ;
593 if (sql%notfound) then
594 raise no_data_found;
595 end if;
596
597 if x_sponsor_action_code is not null then
598
599 if x_sponsor_action_date <> l_old_sponsor_action_date or
600 nvl(x_sponsor_action_code,'_') <> nvl(l_old_sponsor_action_code,'_') or
601 nvl(x_sponsor_action_comments,'_') <> nvl(l_old_sponsor_action_comments,'_') then
602
603 insert into igw_prop_comments
604 (
605 PROPOSAL_ID,
606 COMMENT_ID,
607 COMMENTS,
608 LAST_UPDATE_DATE,
609 LAST_UPDATED_BY,
610 CREATION_DATE,
611 CREATED_BY,
612 LAST_UPDATE_LOGIN,
613 RECORD_VERSION_NUMBER,
614 SPONSOR_ACTION_CODE,
615 SPONSOR_ACTION_DATE
616 )
617 values
618 (
619 X_PROPOSAL_ID,
620 IGW_PROP_COMMENTS_S.NEXTVAL,
621 X_SPONSOR_ACTION_COMMENTS,
622 X_LAST_UPDATE_DATE,
623 X_LAST_UPDATED_BY,
624 X_LAST_UPDATE_DATE,
625 X_LAST_UPDATED_BY,
626 X_LAST_UPDATE_LOGIN,
627 1,
628 X_SPONSOR_ACTION_CODE,
629 X_SPONSOR_ACTION_DATE
630 );
631
632 end if;
633
634 end if;
635
636
637 end UPDATE_ROW;
638
639 procedure ADD_ROW (
640 X_ROWID in out VARCHAR2,
641 X_PROPOSAL_ID in NUMBER,
642 X_SPONSOR_ACTION_CODE in VARCHAR2,
643 X_SPONSOR_ACTION_DATE in DATE,
644 X_SPONSOR_ACTION_COMMENTS in VARCHAR2,
645 X_AWARD_AMOUNT in NUMBER,
646 X_PROPOSAL_NUMBER in VARCHAR2,
647 X_LEAD_ORGANIZATION_ID in NUMBER,
648 X_PROPOSAL_STATUS in VARCHAR2,
649 X_PROPOSAL_START_DATE in DATE,
650 X_PROPOSAL_END_DATE in DATE,
651 X_PROPOSAL_TITLE in VARCHAR2,
652 X_PROPOSAL_TYPE_CODE in VARCHAR2,
653 X_AWARD_NUMBER in VARCHAR2,
654 X_ORIGINAL_PROPOSAL_NUMBER in VARCHAR2,
655 X_ORIGINAL_AWARD_NUMBER in VARCHAR2,
656 X_ORIGINAL_PROPOSAL_START_DATE in DATE,
657 X_ORIGINAL_PROPOSAL_END_DATE in DATE,
658 X_ACTIVITY_TYPE_CODE in VARCHAR2,
659 X_SPONSOR_ID in NUMBER,
660 X_FUNDING_SPONSOR_UNIT in VARCHAR2,
661 X_ORIGINAL_SPONSOR_ID in NUMBER,
662 X_SPONSOR_PROPOSAL_NUMBER in VARCHAR2,
663 X_NOTICE_OF_OPPORTUNITY_CODE in VARCHAR2,
664 X_PROGRAM_NUMBER in VARCHAR2,
665 X_PROGRAM_TITLE in VARCHAR2,
666 X_PROGRAM_URL in VARCHAR2,
667 X_SUBMITTING_ORGANIZATION_ID in NUMBER,
668 X_SIGNING_OFFICIAL_ID in NUMBER,
669 X_ADMIN_OFFICIAL_ID in NUMBER,
670 X_DEADLINE_DATE in DATE,
671 X_DEADLINE_TYPE in VARCHAR2,
672 X_LETTER_OF_INTENT_DUE_DATE in DATE,
673 X_ATTRIBUTE_CATEGORY in VARCHAR2,
674 X_ATTRIBUTE1 in VARCHAR2,
675 X_ATTRIBUTE2 in VARCHAR2,
676 X_ATTRIBUTE3 in VARCHAR2,
677 X_ATTRIBUTE4 in VARCHAR2,
678 X_ATTRIBUTE5 in VARCHAR2,
679 X_ATTRIBUTE6 in VARCHAR2,
680 X_ATTRIBUTE7 in VARCHAR2,
681 X_ATTRIBUTE8 in VARCHAR2,
682 X_ATTRIBUTE9 in VARCHAR2,
683 X_ATTRIBUTE10 in VARCHAR2,
684 X_ATTRIBUTE11 in VARCHAR2,
685 X_ATTRIBUTE12 in VARCHAR2,
686 X_ATTRIBUTE13 in VARCHAR2,
687 X_ATTRIBUTE14 in VARCHAR2,
688 X_ATTRIBUTE15 in VARCHAR2,
689 X_MODE in VARCHAR2 default 'R'
690 ) is
691 cursor c1 is select rowid from IGW_PROPOSALS_ALL
692 where PROPOSAL_ID = X_PROPOSAL_ID
693 ;
694 dummy c1%rowtype;
695 begin
696 open c1;
697 fetch c1 into dummy;
698 if (c1%notfound) then
699 close c1;
700 INSERT_ROW (
701 X_ROWID,
702 X_PROPOSAL_ID,
703 X_SPONSOR_ACTION_CODE,
704 X_SPONSOR_ACTION_DATE,
705 X_SPONSOR_ACTION_COMMENTS,
706 X_AWARD_AMOUNT,
707 X_PROPOSAL_NUMBER,
708 X_LEAD_ORGANIZATION_ID,
709 X_PROPOSAL_STATUS,
710 X_PROPOSAL_START_DATE,
711 X_PROPOSAL_END_DATE,
712 X_PROPOSAL_TITLE,
713 X_PROPOSAL_TYPE_CODE,
714 X_AWARD_NUMBER,
715 X_ORIGINAL_PROPOSAL_NUMBER,
716 X_ORIGINAL_AWARD_NUMBER,
717 X_ORIGINAL_PROPOSAL_START_DATE,
718 X_ORIGINAL_PROPOSAL_END_DATE,
719 X_ACTIVITY_TYPE_CODE,
720 X_SPONSOR_ID,
721 X_FUNDING_SPONSOR_UNIT,
722 X_ORIGINAL_SPONSOR_ID,
723 X_SPONSOR_PROPOSAL_NUMBER,
724 X_NOTICE_OF_OPPORTUNITY_CODE,
725 X_PROGRAM_NUMBER,
726 X_PROGRAM_TITLE,
727 X_PROGRAM_URL,
728 X_SUBMITTING_ORGANIZATION_ID,
729 X_SIGNING_OFFICIAL_ID,
730 X_ADMIN_OFFICIAL_ID,
731 X_DEADLINE_DATE,
732 X_DEADLINE_TYPE,
733 X_LETTER_OF_INTENT_DUE_DATE,
734 X_ATTRIBUTE_CATEGORY,
735 X_ATTRIBUTE1,
736 X_ATTRIBUTE2,
737 X_ATTRIBUTE3,
738 X_ATTRIBUTE4,
739 X_ATTRIBUTE5,
740 X_ATTRIBUTE6,
741 X_ATTRIBUTE7,
742 X_ATTRIBUTE8,
743 X_ATTRIBUTE9,
744 X_ATTRIBUTE10,
745 X_ATTRIBUTE11,
746 X_ATTRIBUTE12,
747 X_ATTRIBUTE13,
748 X_ATTRIBUTE14,
749 X_ATTRIBUTE15,
750 X_MODE);
751 return;
752 end if;
753 close c1;
754 UPDATE_ROW (
755 X_ROWID,
756 X_PROPOSAL_ID,
757 X_SPONSOR_ACTION_CODE,
758 X_SPONSOR_ACTION_DATE,
759 X_SPONSOR_ACTION_COMMENTS,
760 X_AWARD_AMOUNT,
761 X_PROPOSAL_NUMBER,
762 X_LEAD_ORGANIZATION_ID,
763 X_PROPOSAL_STATUS,
764 X_PROPOSAL_START_DATE,
765 X_PROPOSAL_END_DATE,
766 X_PROPOSAL_TITLE,
767 X_PROPOSAL_TYPE_CODE,
768 X_AWARD_NUMBER,
769 X_ORIGINAL_PROPOSAL_NUMBER,
770 X_ORIGINAL_AWARD_NUMBER,
771 X_ORIGINAL_PROPOSAL_START_DATE,
772 X_ORIGINAL_PROPOSAL_END_DATE,
773 X_ACTIVITY_TYPE_CODE,
774 X_SPONSOR_ID,
775 X_FUNDING_SPONSOR_UNIT,
776 X_ORIGINAL_SPONSOR_ID,
777 X_SPONSOR_PROPOSAL_NUMBER,
778 X_NOTICE_OF_OPPORTUNITY_CODE,
779 X_PROGRAM_NUMBER,
780 X_PROGRAM_TITLE,
781 X_PROGRAM_URL,
782 X_SUBMITTING_ORGANIZATION_ID,
783 X_SIGNING_OFFICIAL_ID,
784 X_ADMIN_OFFICIAL_ID,
785 X_DEADLINE_DATE,
786 X_DEADLINE_TYPE,
787 X_LETTER_OF_INTENT_DUE_DATE,
788 X_ATTRIBUTE_CATEGORY,
789 X_ATTRIBUTE1,
790 X_ATTRIBUTE2,
791 X_ATTRIBUTE3,
792 X_ATTRIBUTE4,
793 X_ATTRIBUTE5,
794 X_ATTRIBUTE6,
795 X_ATTRIBUTE7,
796 X_ATTRIBUTE8,
797 X_ATTRIBUTE9,
798 X_ATTRIBUTE10,
799 X_ATTRIBUTE11,
800 X_ATTRIBUTE12,
801 X_ATTRIBUTE13,
802 X_ATTRIBUTE14,
803 X_ATTRIBUTE15,
804 X_MODE);
805 end ADD_ROW;
806
807 procedure DELETE_ROW (
808 X_ROWID in VARCHAR2
809 ) is
810 begin
811 delete from IGW_PROPOSALS_ALL
812 where ROWID = X_ROWID;
813 if (sql%notfound) then
814 raise no_data_found;
815 end if;
816 end DELETE_ROW;
817
818 end IGW_PROPOSALS_ALL_PKG;