[Home] [Help]
PACKAGE BODY: APPS.IGS_RE_SCHOLARSHIP_PKG
Source
1 package body IGS_RE_SCHOLARSHIP_PKG as
2 /* $Header: IGSRI11B.pls 120.1 2005/07/04 00:41:46 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The call to igs_re_val_sch.genp_val_strt_end_dt
7 -- is changed to igs_ad_val_edtl.genp_val_strt_end_dt
8 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_re_val_sch.genp_val_sdtt_sess
9 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
10 -------------------------------------------------------------------------------------------
11 l_rowid VARCHAR2(25);
12 old_references IGS_RE_SCHOLARSHIP_ALL%RowType;
13 new_references IGS_RE_SCHOLARSHIP_ALL%RowType;
14
15 PROCEDURE Set_Column_Values (
16 p_action IN VARCHAR2,
17 x_rowid IN VARCHAR2 DEFAULT NULL,
18 x_person_id IN NUMBER DEFAULT NULL,
19 x_ca_sequence_number IN NUMBER DEFAULT NULL,
20 x_scholarship_type IN VARCHAR2 DEFAULT NULL,
21 x_start_dt IN DATE DEFAULT NULL,
22 x_end_dt IN DATE DEFAULT NULL,
23 x_dollar_value IN NUMBER DEFAULT NULL,
24 x_description IN VARCHAR2 DEFAULT NULL,
25 x_other_benefits IN VARCHAR2 DEFAULT NULL,
26 x_conditions IN VARCHAR2 DEFAULT NULL,
27 x_creation_date IN DATE DEFAULT NULL,
28 x_created_by IN NUMBER DEFAULT NULL,
29 x_last_update_date IN DATE DEFAULT NULL,
30 x_last_updated_by IN NUMBER DEFAULT NULL,
31 x_last_update_login IN NUMBER DEFAULT NULL ,
32 x_org_id IN NUMBER DEFAULT NULL
33 ) AS
34
35 CURSOR cur_old_ref_values IS
36 SELECT *
37 FROM IGS_RE_SCHOLARSHIP_ALL
38 WHERE rowid = x_rowid;
39
40 BEGIN
41
42 l_rowid := x_rowid;
43
44 -- Code for setting the Old and New Reference Values.
45 -- Populate Old Values.
46 Open cur_old_ref_values;
47 Fetch cur_old_ref_values INTO old_references;
48 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
49 Close cur_old_ref_values;
50 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
51 IGS_GE_MSG_STACK.ADD;
52 App_Exception.Raise_Exception;
53 Return;
54 END IF;
55 Close cur_old_ref_values;
56
57 -- Populate New Values.
58 new_references.person_id := x_person_id;
59 new_references.ca_sequence_number := x_ca_sequence_number;
60 new_references.scholarship_type := x_scholarship_type;
61 new_references.start_dt := x_start_dt;
62 new_references.end_dt := x_end_dt;
63 new_references.dollar_value := x_dollar_value;
64 new_references.description := x_description;
65 new_references.other_benefits := x_other_benefits;
66 new_references.conditions := x_conditions;
67 new_references.org_id := x_org_id ;
68 IF (p_action = 'UPDATE') THEN
69 new_references.creation_date := old_references.creation_date;
70 new_references.created_by := old_references.created_by;
71 ELSE
72 new_references.creation_date := x_creation_date;
73 new_references.created_by := x_created_by;
74 END IF;
75 new_references.last_update_date := x_last_update_date;
76 new_references.last_updated_by := x_last_updated_by;
77 new_references.last_update_login := x_last_update_login;
78
79 END Set_Column_Values;
80
81 PROCEDURE BeforeRowInsertUpdate1(
82 p_inserting IN BOOLEAN DEFAULT FALSE,
83 p_updating IN BOOLEAN DEFAULT FALSE,
84 p_deleting IN BOOLEAN DEFAULT FALSE
85 ) AS
86 v_message_name VARCHAR2(30);
87 BEGIN
88 -- Turn off trigger validation when performing insert of IGS_RE_CANDIDATURE details
89 -- as a result of IGS_PS_COURSE transfer
90 IF igs_as_val_suaap.genp_val_sdtt_sess('ENRP_INS_CA_TRNSFR') THEN
91 -- Validate that inserts are allowed
92 IF p_inserting THEN
93 -- Validate if IGS_RE_SCHOLARSHIP type is closed
94 IF IGS_RE_VAL_SCH.resp_val_scht_closed ( new_references.scholarship_type,
95 v_message_name) = FALSE THEN
96 Fnd_Message.Set_Name ('IGS', v_message_name);
97 IGS_GE_MSG_STACK.ADD;
98 App_Exception.Raise_Exception;
99 END IF;
100 END IF;
101 -- Validate that inserts/updates are allowed
102 IF p_inserting OR p_updating THEN
103 -- Validate if start date is before end date
104 IF igs_ad_val_edtl.genp_val_strt_end_dt ( new_references.start_dt,
105 new_references.end_dt,
106 v_message_name) = FALSE THEN
107 Fnd_Message.Set_Name ('IGS', v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 App_Exception.Raise_Exception;
110 END IF;
111 END IF;
112 END IF;
113
114
115 END BeforeRowInsertUpdate1;
116
117 PROCEDURE AfterRowInsertUpdate2(
118 p_inserting IN BOOLEAN DEFAULT FALSE,
119 p_updating IN BOOLEAN DEFAULT FALSE,
120 p_deleting IN BOOLEAN DEFAULT FALSE
121 ) AS
122 v_message_name VARCHAR2(30);
123
124 BEGIN
125 IF p_inserting OR p_updating THEN
126 -- Save the rowid of the current row.
127 -- Validate dates do not overlap
128 IF IGS_RE_VAL_SCH.resp_val_sch_ovrlp (
129 new_references.person_id,
130 new_references.ca_sequence_number,
131 new_references.scholarship_type,
132 new_references.start_dt,
133 new_references.end_dt,
134 v_message_name) = FALSE THEN
135 Fnd_Message.Set_Name ('IGS', v_message_name);
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140 END AfterRowInsertUpdate2;
141
142 PROCEDURE Check_Constraints (
143 Column_Name in VARCHAR2 DEFAULT NULL ,
144 Column_Value in VARCHAR2 DEFAULT NULL
145 ) AS
146 BEGIN
147
148 IF Column_Name is null then
149 NULL;
150 ELSIF upper(Column_name) = 'SCHOLARSHIP_TYPE' THEN
151 new_references.SCHOLARSHIP_TYPE := COLUMN_VALUE ;
152 ELSIF upper(Column_name) = 'CA_SEQUENCE_NUMBER' THEN
153 new_references.CA_SEQUENCE_NUMBER := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
154 ELSIF upper(Column_name) = 'DOLLAR_VALUE' THEN
155 new_references.DOLLAR_VALUE := IGS_GE_NUMBER.to_num(COLUMN_VALUE) ;
156 END IF;
157
158 IF upper(column_name) = 'SCHOLARSHIP_TYPE' OR COLUMN_NAME IS NULL THEN
159 IF new_references.SCHOLARSHIP_TYPE <> upper(NEW_REFERENCES.SCHOLARSHIP_TYPE) then
160 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception ;
163 END IF;
164 END IF;
165 IF upper(column_name) = 'CA_SEQUENCE_NUMBER' OR COLUMN_NAME IS NULL THEN
166 IF new_references.CA_SEQUENCE_NUMBER < 1 OR new_references.CA_SEQUENCE_NUMBER > 999999 then
167 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception ;
170 END IF;
171 END IF;
172 IF upper(column_name) = 'DOLLAR_VALUE' OR COLUMN_NAME IS NULL THEN
173 IF new_references.DOLLAR_VALUE < 0 OR new_references.DOLLAR_VALUE > 999999.99 then
174 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception ;
177 END IF;
178 END IF;
179 END Check_Constraints ;
180
181
182 PROCEDURE Check_Parent_Existance AS
183 BEGIN
184
185 IF (((old_references.person_id = new_references.person_id) AND
186 (old_references.ca_sequence_number = new_references.ca_sequence_number)) OR
187 ((new_references.person_id IS NULL) OR
188 (new_references.ca_sequence_number IS NULL))) THEN
189 NULL;
190 ELSE
191 IF NOT IGS_RE_CANDIDATURE_PKG.Get_PK_For_Validation (
192 new_references.person_id,
193 new_references.ca_sequence_number
194 ) THEN
195 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198 END IF;
199
200 END IF;
201
202 IF (((old_references.scholarship_type = new_references.scholarship_type)) OR
203 ((new_references.scholarship_type IS NULL))) THEN
204 NULL;
205 ELSE
206 IF NOT IGS_RE_SCHL_TYPE_PKG.Get_PK_For_Validation (
207 new_references.scholarship_type
208 ) THEN
209 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
210 IGS_GE_MSG_STACK.ADD;
211 App_Exception.Raise_Exception;
212 END IF;
213
214 END IF;
215
216 END Check_Parent_Existance;
217
218 FUNCTION Get_PK_For_Validation (
219 x_person_id IN NUMBER,
220 x_ca_sequence_number IN NUMBER,
221 x_scholarship_type IN VARCHAR2,
222 x_start_dt IN DATE
223 ) RETURN BOOLEAN
224 AS
225
226 CURSOR cur_rowid IS
227 SELECT rowid
228 FROM IGS_RE_SCHOLARSHIP_ALL
229 WHERE person_id = x_person_id
230 AND ca_sequence_number = x_ca_sequence_number
231 AND scholarship_type = x_scholarship_type
232 AND start_dt = x_start_dt
233 FOR UPDATE NOWAIT;
234
235 lv_rowid cur_rowid%RowType;
236
237 BEGIN
238
239 Open cur_rowid;
240 Fetch cur_rowid INTO lv_rowid;
241 IF (cur_rowid%FOUND) THEN
242 Close cur_rowid;
243 RETURN(TRUE);
244 ELSE
245 Close cur_rowid;
246 RETURN(FALSE);
247 END IF;
248
249 END Get_PK_For_Validation;
250
251 PROCEDURE GET_FK_IGS_RE_CANDIDATURE (
252 x_person_id IN NUMBER,
253 x_sequence_number IN NUMBER
254 ) AS
255
256 CURSOR cur_rowid IS
257 SELECT rowid
258 FROM IGS_RE_SCHOLARSHIP_ALL
259 WHERE person_id = x_person_id
260 AND ca_sequence_number = x_sequence_number ;
261
262 lv_rowid cur_rowid%RowType;
263
264 BEGIN
265
266 Open cur_rowid;
267 Fetch cur_rowid INTO lv_rowid;
268 IF (cur_rowid%FOUND) THEN
269 Close cur_rowid;
270 Fnd_Message.Set_Name ('IGS', 'IGS_RE_SCH_CA_FK');
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 Return;
274 END IF;
275 Close cur_rowid;
276
277 END GET_FK_IGS_RE_CANDIDATURE;
278
279 PROCEDURE GET_FK_IGS_RE_SCHL_TYPE (
280 x_scholarship_type IN VARCHAR2
281 ) AS
282
283 CURSOR cur_rowid IS
284 SELECT rowid
285 FROM IGS_RE_SCHOLARSHIP_ALL
286 WHERE scholarship_type = x_scholarship_type ;
287
288 lv_rowid cur_rowid%RowType;
289
290 BEGIN
291
292 Open cur_rowid;
293 Fetch cur_rowid INTO lv_rowid;
294 IF (cur_rowid%FOUND) THEN
295 Close cur_rowid;
296 Fnd_Message.Set_Name ('IGS', 'IGS_RE_SCH_SCHT_FK');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 Return;
300 END IF;
301 Close cur_rowid;
302
303 END GET_FK_IGS_RE_SCHL_TYPE;
304
305 PROCEDURE Before_DML (
306 p_action IN VARCHAR2,
307 x_rowid IN VARCHAR2 DEFAULT NULL,
308 x_person_id IN NUMBER DEFAULT NULL,
309 x_ca_sequence_number IN NUMBER DEFAULT NULL,
310 x_scholarship_type IN VARCHAR2 DEFAULT NULL,
311 x_start_dt IN DATE DEFAULT NULL,
312 x_end_dt IN DATE DEFAULT NULL,
313 x_dollar_value IN NUMBER DEFAULT NULL,
314 x_description IN VARCHAR2 DEFAULT NULL,
315 x_other_benefits IN VARCHAR2 DEFAULT NULL,
316 x_conditions IN VARCHAR2 DEFAULT NULL,
317 x_creation_date IN DATE DEFAULT NULL,
318 x_created_by IN NUMBER DEFAULT NULL,
319 x_last_update_date IN DATE DEFAULT NULL,
320 x_last_updated_by IN NUMBER DEFAULT NULL,
321 x_last_update_login IN NUMBER DEFAULT NULL,
322 x_org_id IN NUMBER DEFAULT NULL
323 ) AS
324 BEGIN
325
326 Set_Column_Values (
327 p_action,
328 x_rowid,
329 x_person_id,
330 x_ca_sequence_number,
331 x_scholarship_type,
332 x_start_dt,
333 x_end_dt,
334 x_dollar_value,
335 x_description,
336 x_other_benefits,
337 x_conditions,
338 x_creation_date,
339 x_created_by,
340 x_last_update_date,
341 x_last_updated_by,
342 x_last_update_login ,
343 x_org_id
344 );
345
346 IF (p_action = 'INSERT') THEN
347 -- Call all the procedures related to Before Insert.
348 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
349 IF Get_PK_For_Validation (
350 new_references.person_id,
351 new_references.ca_sequence_number,
352 new_references.scholarship_type,
353 new_references.start_dt
354 ) THEN
355 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 END IF;
359 Check_Constraints;
360 Check_Parent_Existance;
361 ELSIF (p_action = 'UPDATE') THEN
362 -- Call all the procedures related to Before Update.
363 BeforeRowInsertUpdate1 ( p_updating => TRUE );
364 Check_Constraints;
365 Check_Parent_Existance;
366 ELSIF (p_action = 'VALIDATE_INSERT') THEN
367 IF Get_PK_For_Validation (
368 new_references.person_id,
369 new_references.ca_sequence_number,
370 new_references.scholarship_type,
371 new_references.start_dt
372 ) THEN
373 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
374 IGS_GE_MSG_STACK.ADD;
375 App_Exception.Raise_Exception;
376 END IF;
377 Check_Constraints;
378 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
379 Check_Constraints;
380 END IF;
381
382 END Before_DML;
383
384 PROCEDURE After_DML (
385 p_action IN VARCHAR2,
386 x_rowid IN VARCHAR2
387 ) AS
388 BEGIN
389
390 l_rowid := x_rowid;
391 IF (p_action = 'INSERT') THEN
392 -- Call all the procedures related to After Insert.
393 AfterRowInsertUpdate2 ( p_inserting => TRUE );
394 END IF;
395 END After_DML;
396
397 procedure INSERT_ROW (
398 X_ROWID in out NOCOPY VARCHAR2,
399 X_PERSON_ID in NUMBER,
400 X_CA_SEQUENCE_NUMBER in NUMBER,
401 X_SCHOLARSHIP_TYPE in VARCHAR2,
402 X_START_DT in DATE,
403 X_END_DT in DATE,
404 X_DOLLAR_VALUE in NUMBER,
405 X_DESCRIPTION in VARCHAR2,
406 X_OTHER_BENEFITS in VARCHAR2,
407 X_CONDITIONS in VARCHAR2,
408 X_MODE in VARCHAR2 default 'R',
409 X_ORG_ID in NUMBER
410 ) as
411 cursor C is select ROWID from IGS_RE_SCHOLARSHIP_ALL
412 where PERSON_ID = X_PERSON_ID
413 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
414 and SCHOLARSHIP_TYPE = X_SCHOLARSHIP_TYPE
415 and START_DT = X_START_DT;
416 X_LAST_UPDATE_DATE DATE;
417 X_LAST_UPDATED_BY NUMBER;
418 X_LAST_UPDATE_LOGIN NUMBER;
419 begin
420 X_LAST_UPDATE_DATE := SYSDATE;
421 if(X_MODE = 'I') then
422 X_LAST_UPDATED_BY := 1;
423 X_LAST_UPDATE_LOGIN := 0;
424 elsif (X_MODE IN ('R', 'S')) then
425 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
426 if X_LAST_UPDATED_BY is NULL then
427 X_LAST_UPDATED_BY := -1;
428 end if;
429 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
430 if X_LAST_UPDATE_LOGIN is NULL then
431 X_LAST_UPDATE_LOGIN := -1;
432 end if;
433 else
434 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
435 IGS_GE_MSG_STACK.ADD;
436 app_exception.raise_exception;
437 end if;
438
439 Before_DML (
440 p_action => 'INSERT',
441 x_rowid => X_ROWID,
442 x_person_id => X_PERSON_ID,
443 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
444 x_scholarship_type => X_SCHOLARSHIP_TYPE,
445 x_start_dt => X_START_DT,
446 x_end_dt => X_END_DT,
447 x_dollar_value => X_DOLLAR_VALUE,
448 x_description => X_DESCRIPTION,
449 x_other_benefits => X_OTHER_BENEFITS,
450 x_conditions => X_CONDITIONS,
451 x_created_by => X_LAST_UPDATED_BY ,
452 x_creation_date => X_LAST_UPDATE_DATE,
453 x_last_updated_by => X_LAST_UPDATED_BY,
454 x_last_update_date => X_LAST_UPDATE_DATE,
455 x_last_update_login => X_LAST_UPDATE_LOGIN,
456 x_org_id => igs_ge_gen_003.get_org_id
457 );
458
459 IF (x_mode = 'S') THEN
460 igs_sc_gen_001.set_ctx('R');
461 END IF;
462 insert into IGS_RE_SCHOLARSHIP_ALL (
463 PERSON_ID,
464 CA_SEQUENCE_NUMBER,
465 SCHOLARSHIP_TYPE,
466 START_DT,
467 END_DT,
468 DOLLAR_VALUE,
469 DESCRIPTION,
470 OTHER_BENEFITS,
471 CONDITIONS,
472 CREATION_DATE,
473 CREATED_BY,
474 LAST_UPDATE_DATE,
475 LAST_UPDATED_BY,
476 LAST_UPDATE_LOGIN,
477 ORG_ID
478 ) values (
479 NEW_REFERENCES.PERSON_ID,
480 NEW_REFERENCES.CA_SEQUENCE_NUMBER,
481 NEW_REFERENCES.SCHOLARSHIP_TYPE,
482 NEW_REFERENCES.START_DT,
483 NEW_REFERENCES.END_DT,
484 NEW_REFERENCES.DOLLAR_VALUE,
485 NEW_REFERENCES.DESCRIPTION,
486 NEW_REFERENCES.OTHER_BENEFITS,
487 NEW_REFERENCES.CONDITIONS,
488 X_LAST_UPDATE_DATE,
489 X_LAST_UPDATED_BY,
490 X_LAST_UPDATE_DATE,
491 X_LAST_UPDATED_BY,
492 X_LAST_UPDATE_LOGIN,
493 NEW_REFERENCES.ORG_ID
494 );
495 IF (x_mode = 'S') THEN
496 igs_sc_gen_001.unset_ctx('R');
497 END IF;
498
499
500 open c;
501 fetch c into X_ROWID;
502 if (c%notfound) then
503 close c;
504 raise no_data_found;
505 end if;
506 close c;
507
508 After_DML (
509 p_action => 'INSERT',
510 x_rowid => X_ROWID
511 );
512
513
514 EXCEPTION
515 WHEN OTHERS THEN
516 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
517 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
518 fnd_message.set_token ('ERR_CD', SQLCODE);
519 igs_ge_msg_stack.add;
520 igs_sc_gen_001.unset_ctx('R');
521 app_exception.raise_exception;
522 ELSE
523 igs_sc_gen_001.unset_ctx('R');
524 RAISE;
525 END IF;
526
527 end INSERT_ROW;
528
529 procedure LOCK_ROW (
530 X_ROWID in VARCHAR2,
531 X_PERSON_ID in NUMBER,
532 X_CA_SEQUENCE_NUMBER in NUMBER,
533 X_SCHOLARSHIP_TYPE in VARCHAR2,
534 X_START_DT in DATE,
535 X_END_DT in DATE,
536 X_DOLLAR_VALUE in NUMBER,
537 X_DESCRIPTION in VARCHAR2,
538 X_OTHER_BENEFITS in VARCHAR2,
539 X_CONDITIONS in VARCHAR2
540 ) as
541 cursor c1 is select
542 END_DT,
543 DOLLAR_VALUE,
544 DESCRIPTION,
545 OTHER_BENEFITS,
546 CONDITIONS
547 from IGS_RE_SCHOLARSHIP_ALL
548 where ROWID = X_ROWID
549 for update nowait;
550 tlinfo c1%rowtype;
551
552 begin
553 open c1;
554 fetch c1 into tlinfo;
555 if (c1%notfound) then
556 close c1;
557 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
558 app_exception.raise_exception;
559 return;
560 end if;
561 close c1;
562
563 if ( ((tlinfo.END_DT = X_END_DT)
564 OR ((tlinfo.END_DT is null)
565 AND (X_END_DT is null)))
566 AND ((tlinfo.DOLLAR_VALUE = X_DOLLAR_VALUE)
567 OR ((tlinfo.DOLLAR_VALUE is null)
568 AND (X_DOLLAR_VALUE is null)))
569 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
570 OR ((tlinfo.DESCRIPTION is null)
571 AND (X_DESCRIPTION is null)))
572 AND ((tlinfo.OTHER_BENEFITS = X_OTHER_BENEFITS)
573 OR ((tlinfo.OTHER_BENEFITS is null)
574 AND (X_OTHER_BENEFITS is null)))
575 AND ((tlinfo.CONDITIONS = X_CONDITIONS)
576 OR ((tlinfo.CONDITIONS is null)
577 AND (X_CONDITIONS is null)))
578
579 ) then
580 null;
581 else
582 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
583 app_exception.raise_exception;
584 end if;
585 return;
586 end LOCK_ROW;
587
588 procedure UPDATE_ROW (
589 X_ROWID in VARCHAR2,
590 X_PERSON_ID in NUMBER,
591 X_CA_SEQUENCE_NUMBER in NUMBER,
592 X_SCHOLARSHIP_TYPE in VARCHAR2,
593 X_START_DT in DATE,
594 X_END_DT in DATE,
595 X_DOLLAR_VALUE in NUMBER,
596 X_DESCRIPTION in VARCHAR2,
597 X_OTHER_BENEFITS in VARCHAR2,
598 X_CONDITIONS in VARCHAR2,
599 X_MODE in VARCHAR2 default 'R'
600 ) as
601 X_LAST_UPDATE_DATE DATE;
602 X_LAST_UPDATED_BY NUMBER;
603 X_LAST_UPDATE_LOGIN NUMBER;
604 begin
605 X_LAST_UPDATE_DATE := SYSDATE;
606 if(X_MODE = 'I') then
607 X_LAST_UPDATED_BY := 1;
608 X_LAST_UPDATE_LOGIN := 0;
609 elsif (X_MODE IN ('R', 'S')) then
610 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
611 if X_LAST_UPDATED_BY is NULL then
612 X_LAST_UPDATED_BY := -1;
613 end if;
614 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
615 if X_LAST_UPDATE_LOGIN is NULL then
616 X_LAST_UPDATE_LOGIN := -1;
617 end if;
618 else
619 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
620 IGS_GE_MSG_STACK.ADD;
621 app_exception.raise_exception;
622 end if;
623
624 Before_DML (
625 p_action => 'UPDATE',
626 x_rowid => X_ROWID,
627 x_person_id => X_PERSON_ID,
628 x_ca_sequence_number => X_CA_SEQUENCE_NUMBER,
629 x_scholarship_type => X_SCHOLARSHIP_TYPE,
630 x_start_dt => X_START_DT,
631 x_end_dt => X_END_DT,
632 x_dollar_value => X_DOLLAR_VALUE,
633 x_description => X_DESCRIPTION,
634 x_other_benefits => X_OTHER_BENEFITS,
635 x_conditions => X_CONDITIONS,
636 x_created_by => X_LAST_UPDATED_BY ,
637 x_creation_date => X_LAST_UPDATE_DATE,
638 x_last_updated_by => X_LAST_UPDATED_BY,
639 x_last_update_date => X_LAST_UPDATE_DATE,
640 x_last_update_login => X_LAST_UPDATE_LOGIN
641 );
642 IF (x_mode = 'S') THEN
643 igs_sc_gen_001.set_ctx('R');
644 END IF;
645 update IGS_RE_SCHOLARSHIP_ALL set
646 END_DT = NEW_REFERENCES.END_DT,
647 DOLLAR_VALUE = NEW_REFERENCES.DOLLAR_VALUE,
648 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
649 OTHER_BENEFITS = NEW_REFERENCES.OTHER_BENEFITS,
650 CONDITIONS = NEW_REFERENCES.CONDITIONS,
651 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
652 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
653 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
654 where ROWID = X_ROWID
655 ;
656 if (sql%notfound) then
657 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
658 igs_ge_msg_stack.add;
659 igs_sc_gen_001.unset_ctx('R');
660 app_exception.raise_exception;
661 end if;
662 IF (x_mode = 'S') THEN
663 igs_sc_gen_001.unset_ctx('R');
664 END IF;
665
666
667 EXCEPTION
668 WHEN OTHERS THEN
669 IF (SQLCODE = (-28115)) THEN
670 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
671 fnd_message.set_token ('ERR_CD', SQLCODE);
672 igs_ge_msg_stack.add;
673 igs_sc_gen_001.unset_ctx('R');
674 app_exception.raise_exception;
675 ELSE
676 igs_sc_gen_001.unset_ctx('R');
677 RAISE;
678 END IF;
679
680 end UPDATE_ROW;
681
682 procedure ADD_ROW (
683 X_ROWID in out NOCOPY VARCHAR2,
684 X_PERSON_ID in NUMBER,
685 X_CA_SEQUENCE_NUMBER in NUMBER,
686 X_SCHOLARSHIP_TYPE in VARCHAR2,
687 X_START_DT in DATE,
688 X_END_DT in DATE,
689 X_DOLLAR_VALUE in NUMBER,
690 X_DESCRIPTION in VARCHAR2,
691 X_OTHER_BENEFITS in VARCHAR2,
692 X_CONDITIONS in VARCHAR2,
693 X_MODE in VARCHAR2 default 'R',
694 X_ORG_ID in NUMBER
695 ) as
696 cursor c1 is select rowid from IGS_RE_SCHOLARSHIP_ALL
697 where PERSON_ID = X_PERSON_ID
698 and CA_SEQUENCE_NUMBER = X_CA_SEQUENCE_NUMBER
699 and SCHOLARSHIP_TYPE = X_SCHOLARSHIP_TYPE
700 and START_DT = X_START_DT
701 ;
702 begin
703 open c1;
704 fetch c1 into X_ROWID;
705 if (c1%notfound) then
706 close c1;
707 INSERT_ROW (
708 X_ROWID,
709 X_PERSON_ID,
710 X_CA_SEQUENCE_NUMBER,
711 X_SCHOLARSHIP_TYPE,
712 X_START_DT,
713 X_END_DT,
714 X_DOLLAR_VALUE,
715 X_DESCRIPTION,
716 X_OTHER_BENEFITS,
717 X_CONDITIONS,
718 X_MODE,
719 X_ORG_ID);
720 return;
721 end if;
722 close c1;
723 UPDATE_ROW (
724 X_ROWID,
725 X_PERSON_ID,
726 X_CA_SEQUENCE_NUMBER,
727 X_SCHOLARSHIP_TYPE,
728 X_START_DT,
729 X_END_DT,
730 X_DOLLAR_VALUE,
731 X_DESCRIPTION,
732 X_OTHER_BENEFITS,
733 X_CONDITIONS,
734 X_MODE);
735 end ADD_ROW;
736
737 procedure DELETE_ROW (
738 X_ROWID in VARCHAR2,
739 x_mode IN VARCHAR2
740 ) as
741 begin
742
743 Before_DML (
744 p_action => 'DELETE',
745 x_rowid => X_ROWID
746 );
747
748 IF (x_mode = 'S') THEN
749 igs_sc_gen_001.set_ctx('R');
750 END IF;
751 delete from IGS_RE_SCHOLARSHIP_ALL
752 where ROWID = X_ROWID;
753 if (sql%notfound) then
754 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
755 igs_ge_msg_stack.add;
756 igs_sc_gen_001.unset_ctx('R');
757 app_exception.raise_exception;
758 end if;
759 IF (x_mode = 'S') THEN
760 igs_sc_gen_001.unset_ctx('R');
761 END IF;
762
763
764 end DELETE_ROW;
765
766 end IGS_RE_SCHOLARSHIP_PKG;