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