[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_STDNT_TRN_PKG
Source
1 PACKAGE BODY Igs_Ps_Stdnt_Trn_Pkg AS
2 /* $Header: IGSPI64B.pls 120.0 2005/06/01 16:18:11 appldev noship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_PS_STDNT_TRN%ROWTYPE;
5 new_references IGS_PS_STDNT_TRN%ROWTYPE;
6
7 PROCEDURE AfterRowInsertUpdate2(
8 p_inserting IN BOOLEAN ,
9 p_updating IN BOOLEAN ,
10 p_deleting IN BOOLEAN
11 );
12
13 PROCEDURE Set_Column_Values (
14 p_action IN VARCHAR2,
15 x_rowid IN VARCHAR2 DEFAULT NULL,
16 x_person_id IN NUMBER DEFAULT NULL,
17 x_course_cd IN VARCHAR2 DEFAULT NULL,
18 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
19 x_transfer_dt IN DATE DEFAULT NULL,
20 x_comments IN VARCHAR2 DEFAULT NULL,
21 x_creation_date IN DATE DEFAULT NULL,
22 x_created_by IN NUMBER DEFAULT NULL,
23 x_last_update_date IN DATE DEFAULT NULL,
24 x_last_updated_by IN NUMBER DEFAULT NULL,
25 x_last_update_login IN NUMBER DEFAULT NULL,
26 x_approved_date IN DATE DEFAULT NULL,
27 x_effective_term_cal_type IN VARCHAR2 DEFAULT NULL,
28 x_effective_term_sequence_num IN NUMBER DEFAULT NULL,
29 x_discontinue_source_flag IN VARCHAR2 DEFAULT NULL,
30 x_uooids_to_transfer IN VARCHAR2 DEFAULT NULL,
31 x_susa_to_transfer IN VARCHAR2 DEFAULT NULL,
32 x_transfer_adv_stand_flag IN VARCHAR2 DEFAULT NULL,
33 x_status_date IN DATE ,
34 x_status_flag IN VARCHAR2
35 ) AS
36
37 CURSOR cur_old_ref_values IS
38 SELECT *
39 FROM IGS_PS_STDNT_TRN
40 WHERE ROWID = x_rowid;
41
42 BEGIN
43
44 l_rowid := x_rowid;
45
46 -- Code for setting the Old and New Reference Values.
47 -- Populate Old Values.
48 OPEN cur_old_ref_values;
49 FETCH cur_old_ref_values INTO old_references;
50 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
51 CLOSE cur_old_ref_values;
52 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
53 IGS_GE_MSG_STACK.ADD;
54 App_Exception.Raise_Exception;
55 RETURN;
56 END IF;
57 CLOSE cur_old_ref_values;
58
59 -- Populate New Values.
60 new_references.person_id := x_person_id;
61 new_references.course_cd := x_course_cd;
62 new_references.transfer_course_cd := x_transfer_course_cd;
63 new_references.transfer_dt := x_transfer_dt;
64 new_references.comments := x_comments;
65 new_references.approved_date := x_approved_date;
66 new_references.effective_term_cal_type := x_effective_term_cal_type;
67 new_references.effective_term_sequence_num := x_effective_term_sequence_num;
68 new_references.discontinue_source_flag := x_discontinue_source_flag;
69 new_references.uooids_to_transfer := x_uooids_to_transfer;
70 new_references.susa_to_transfer := x_susa_to_transfer;
71 new_references.transfer_adv_stand_flag := x_transfer_adv_stand_flag;
72 new_references.status_date := x_status_date;
73 new_references.status_flag := x_status_flag;
74
75 IF (p_action = 'UPDATE') THEN
76 new_references.creation_date := old_references.creation_date;
77 new_references.created_by := old_references.created_by;
78 ELSE
79 new_references.creation_date := x_creation_date;
80 new_references.created_by := x_created_by;
81 END IF;
82 new_references.last_update_date := x_last_update_date;
83 new_references.last_updated_by := x_last_updated_by;
84 new_references.last_update_login := x_last_update_login;
85
86 END Set_Column_Values;
87
88 PROCEDURE BeforeRowInsertUpdateDelete1(
89 p_inserting IN BOOLEAN DEFAULT FALSE,
90 p_updating IN BOOLEAN DEFAULT FALSE,
91 p_deleting IN BOOLEAN DEFAULT FALSE
92 ) AS
93 v_message_name VARCHAR2(30);
94 BEGIN
95 -- Insert validation
96 IF p_inserting THEN
97
98 IF Igs_En_Val_Sct.enrp_val_sct_insert (
99 new_references.person_id,
100 new_references.course_cd,
101 new_references.transfer_course_cd,
102 new_references.transfer_dt,
103 v_message_name) = FALSE THEN
104
105 Fnd_Message.Set_Name('IGS', v_message_name);
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109 END IF;
110 END BeforeRowInsertUpdateDelete1;
111
112
113 PROCEDURE Check_Constraints (
114 Column_Name IN VARCHAR2 DEFAULT NULL,
115 Column_Value IN VARCHAR2 DEFAULT NULL
116 )
117 AS
118 BEGIN
119
120 IF column_name IS NULL THEN
121 NULL;
122 ELSIF UPPER(Column_name) = 'COURSE_CD' THEN
123 new_references.course_cd := column_value;
124 ELSIF UPPER(Column_name) = 'TRANSFER_COURSE_CD' THEN
125 new_references.transfer_course_cd := column_value;
126 END IF;
127
128 IF UPPER(column_name) = 'COURSE_CD' OR
129 column_name IS NULL THEN
130 IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
131 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
132 IGS_GE_MSG_STACK.ADD;
133 App_Exception.Raise_Exception;
134 END IF;
135 END IF;
136
137 IF UPPER(column_name) = 'TRANSFER_COURSE_CD' OR
138 column_name IS NULL THEN
139 IF new_references.transfer_course_cd <> UPPER(new_references.transfer_course_cd) THEN
140 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
141 IGS_GE_MSG_STACK.ADD;
142 App_Exception.Raise_Exception;
143 END IF;
144 END IF;
145 END check_constraints;
146
147 PROCEDURE Check_Parent_Existance AS
148 BEGIN
149
150 IF (((old_references.person_id = new_references.person_id) AND
151 (old_references.course_cd = new_references.course_cd)) OR
152 ((new_references.person_id IS NULL) OR
153 (new_references.course_cd IS NULL))) THEN
154 NULL;
155 ELSE
156 IF NOT Igs_En_Stdnt_Ps_Att_Pkg.Get_PK_For_Validation (
157 new_references.person_id,
158 new_references.transfer_course_cd
159 ) THEN
160 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
161 IGS_GE_MSG_STACK.ADD;
162 --Uncommented This
163 App_Exception.Raise_Exception;
164
165 END IF;
166
167 END IF;
168
169 IF (((old_references.person_id = new_references.person_id) AND
170 (old_references.transfer_course_cd = new_references.transfer_course_cd)) OR
171 ((new_references.person_id IS NULL) OR
172 (new_references.transfer_course_cd IS NULL))) THEN
173 NULL;
174 ELSE
175 IF NOT Igs_En_Stdnt_Ps_Att_Pkg.Get_PK_For_Validation (
176 new_references.person_id,
177 new_references.transfer_course_cd
178 ) THEN
179 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183 END IF;
184
185 IF (((old_references.effective_term_cal_type = new_references.effective_term_cal_type) AND
186 (old_references.effective_term_sequence_num = new_references.effective_term_sequence_num)) OR
187 ((new_references.effective_term_cal_type IS NULL) OR
188 (new_references.effective_term_sequence_num IS NULL))) THEN
189 NULL;
190 ELSE
191 IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation(
192 new_references.effective_term_cal_type,
193 new_references.effective_term_sequence_num) THEN
194
195 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
196 IGS_GE_MSG_STACK.ADD;
197 App_Exception.Raise_Exception;
198
199 END IF;
200
201 END IF;
202 END Check_Parent_Existance;
203
204 PROCEDURE Check_Child_Existance AS
205 BEGIN
206
207 IGS_PS_STDNT_UNT_TRN_PKG.GET_FK_IGS_PS_STDNT_TRN (
208 old_references.person_id,
209 old_references.course_cd,
210 old_references.transfer_course_cd,
211 old_references.transfer_dt
212 );
213
214 END Check_Child_Existance;
215
216 FUNCTION Get_PK_For_Validation (
217 x_person_id IN NUMBER,
218 x_course_cd IN VARCHAR2,
219 x_transfer_course_cd IN VARCHAR2,
220 x_transfer_dt IN DATE
221 ) RETURN BOOLEAN AS
222
223 CURSOR cur_rowid IS
224 SELECT ROWID
225 FROM IGS_PS_STDNT_TRN
226 WHERE person_id = x_person_id
227 AND course_cd = x_course_cd
228 AND transfer_course_cd = x_transfer_course_cd
229 AND transfer_dt = x_transfer_dt
230 FOR UPDATE NOWAIT;
231
232 lv_rowid cur_rowid%ROWTYPE;
233
234 BEGIN
235
236 OPEN cur_rowid;
237 FETCH cur_rowid INTO lv_rowid;
238 IF (cur_rowid%FOUND) THEN
239 CLOSE cur_rowid;
240 RETURN (TRUE);
241 ELSE
242 CLOSE cur_rowid;
243 RETURN (FALSE);
244 END IF;
245 END Get_PK_For_Validation;
246
247 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
248 x_person_id IN NUMBER,
249 x_course_cd IN VARCHAR2
250 ) AS
251
252 CURSOR cur_rowid IS
253 SELECT ROWID
254 FROM IGS_PS_STDNT_TRN
255 WHERE (person_id = x_person_id
256 AND course_cd = x_course_cd)
257 OR (person_id = x_person_id
258 AND transfer_course_cd = x_course_cd);
259
260 lv_rowid cur_rowid%ROWTYPE;
261
262 BEGIN
263
264 OPEN cur_rowid;
265 FETCH cur_rowid INTO lv_rowid;
266 IF (cur_rowid%FOUND) THEN
267 CLOSE cur_rowid;
268 Fnd_Message.Set_Name ('IGS', 'IGS_PS_SCT_SCA_FK');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 RETURN;
272 END IF;
273 CLOSE cur_rowid;
274
275 END GET_FK_IGS_EN_STDNT_PS_ATT;
276
277 PROCEDURE Before_DML (
278 p_action IN VARCHAR2,
279 x_rowid IN VARCHAR2 DEFAULT NULL,
280 x_person_id IN NUMBER DEFAULT NULL,
281 x_course_cd IN VARCHAR2 DEFAULT NULL,
282 x_transfer_course_cd IN VARCHAR2 DEFAULT NULL,
283 x_transfer_dt IN DATE DEFAULT NULL,
284 x_comments IN VARCHAR2 DEFAULT NULL,
285 x_creation_date IN DATE DEFAULT NULL,
286 x_created_by IN NUMBER DEFAULT NULL,
287 x_last_update_date IN DATE DEFAULT NULL,
288 x_last_updated_by IN NUMBER DEFAULT NULL,
289 x_last_update_login IN NUMBER DEFAULT NULL,
290 x_approved_date IN DATE DEFAULT NULL,
291 x_effective_term_cal_type IN VARCHAR2 DEFAULT NULL,
292 x_effective_term_sequence_num IN NUMBER DEFAULT NULL,
293 x_discontinue_source_flag IN VARCHAR2 DEFAULT NULL,
294 x_uooids_to_transfer IN VARCHAR2 DEFAULT NULL,
295 x_susa_to_transfer IN VARCHAR2 DEFAULT NULL,
296 x_transfer_adv_stand_flag IN VARCHAR2 DEFAULT NULL,
297 x_status_date IN DATE ,
298 x_status_flag IN VARCHAR2
299
300 ) AS
301 BEGIN
302 Set_Column_Values (
303 p_action,
304 x_rowid,
305 x_person_id,
306 x_course_cd,
307 x_transfer_course_cd,
308 x_transfer_dt,
309 x_comments,
310 x_creation_date,
311 x_created_by,
312 x_last_update_date,
313 x_last_updated_by,
314 x_last_update_login,
315 x_approved_date,
316 x_effective_term_cal_type,
317 x_effective_term_sequence_num,
318 x_discontinue_source_flag,
319 x_uooids_to_transfer,
320 x_susa_to_transfer ,
321 x_transfer_adv_stand_flag,
322 x_status_date,
323 x_status_flag
324 );
325
326 IF (p_action = 'INSERT') THEN
327 -- Call all the procedures related to Before Insert.
328 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
329 IF Get_PK_For_Validation (
330 new_references.person_id,
331 new_references.course_cd,
332 new_references.transfer_course_cd,
333 new_references.transfer_dt
334 ) THEN
335 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
336 IGS_GE_MSG_STACK.ADD;
337 App_Exception.Raise_Exception;
338 END IF;
339 Check_Constraints;
340 Check_Parent_Existance;
341 ELSIF (p_action = 'UPDATE') THEN
342 -- Call all the procedures related to Before Update.
343 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
344 Check_Constraints;
345 Check_Parent_Existance;
346 ELSIF (p_action = 'DELETE') THEN
347 -- Call all the procedures related to Before Delete.
348 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
349 Check_Child_Existance;
350 ELSIF (p_action = 'VALIDATE_INSERT') THEN
351 IF Get_PK_For_Validation (
352 new_references.person_id,
353 new_references.course_cd,
354 new_references.transfer_course_cd,
355 new_references.transfer_dt
356 ) THEN
357 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
358 IGS_GE_MSG_STACK.ADD;
359 App_Exception.Raise_Exception;
360 END IF;
361 Check_Constraints;
362 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
363 Check_Constraints;
364 ELSIF (p_action = 'VALIDATE_DELETE') THEN
365 Check_Child_Existance;
366 END IF;
367 END Before_DML;
368
369 PROCEDURE After_DML (
370 p_action IN VARCHAR2,
371 x_rowid IN VARCHAR2
372 ) AS
373 BEGIN
374
375 l_rowid := x_rowid;
376
377 IF (p_action = 'INSERT') THEN
378
379 -- Call all the procedures related to After Insert.
380 AfterRowInsertUpdate2 ( p_inserting => TRUE,
381 p_updating => FALSE,
382 p_deleting => FALSE
383 );
384 END IF ;
385
386 END After_DML;
387
388
389 PROCEDURE AfterRowInsertUpdate2(
390 p_inserting IN BOOLEAN ,
391 p_updating IN BOOLEAN ,
392 p_deleting IN BOOLEAN
393 ) AS
394
395 CURSOR cur_prog_atmpt(cp_transfer_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
396 cp_person_id igs_en_stdnt_ps_att.person_id%TYPE) IS
397 SELECT person_id,course_cd,version_number,cal_type ,commencement_dt,course_attempt_status ,
398 location_cd,attendance_mode,attendance_type
399 FROM igs_en_stdnt_ps_att
400 WHERE course_cd = cp_transfer_course_cd
401 AND person_id = cp_person_id;
402
403 l_cur_prog_atmpt cur_prog_atmpt%ROWTYPE;
404
405 BEGIN
406
407 -- Bug # 2829275 . UK Correspondence. The TBH needs to be modified to so that program transfer business event is raised whenever a program transfer is done
408
409 IF (p_inserting) THEN
410
411 OPEN cur_prog_atmpt(new_references.course_cd,new_references.person_id);
412 FETCH cur_prog_atmpt INTO l_cur_prog_atmpt;
413 CLOSE cur_prog_atmpt;
414
415
416 igs_en_workflow.progtrans_event (
417 p_personid => new_references.person_id,
418 p_destprogcd => new_references.course_cd,
419 p_progstartdt => l_cur_prog_atmpt.commencement_dt,
420 p_location => l_cur_prog_atmpt.location_cd,
421 p_atten_type => l_cur_prog_atmpt.attendance_type,
422 p_atten_mode => l_cur_prog_atmpt.attendance_mode,
423 p_prog_status => l_cur_prog_atmpt.course_attempt_status,
424 p_trsnfrdt => new_references.transfer_dt,
425 p_sourceprogcd => new_references.transfer_course_cd
426 );
427
428
429 END IF ;
430
431 END AfterRowInsertUpdate2;
432
433 PROCEDURE INSERT_ROW (
434 X_ROWID IN OUT NOCOPY VARCHAR2,
435 X_PERSON_ID IN NUMBER,
436 X_TRANSFER_COURSE_CD IN VARCHAR2,
437 X_TRANSFER_DT IN DATE,
438 X_COURSE_CD IN VARCHAR2,
439 X_COMMENTS IN VARCHAR2,
440 X_MODE IN VARCHAR2,
441 X_APPROVED_DATE IN DATE,
442 X_EFFECTIVE_TERM_CAL_TYPE IN VARCHAR2,
443 X_EFFECTIVE_TERM_SEQUENCE_NUM IN NUMBER,
444 X_DISCONTINUE_SOURCE_FLAG IN VARCHAR2,
445 X_UOOIDS_TO_TRANSFER IN VARCHAR2,
446 X_SUSA_TO_TRANSFER IN VARCHAR2,
447 X_TRANSFER_ADV_STAND_FLAG IN VARCHAR2,
448 X_STATUS_DATE IN DATE ,
449 X_STATUS_FLAG IN VARCHAR2
450 ) AS
451 CURSOR C IS SELECT ROWID FROM IGS_PS_STDNT_TRN
452 WHERE PERSON_ID = X_PERSON_ID
453 AND TRANSFER_COURSE_CD = X_TRANSFER_COURSE_CD
454 AND TRANSFER_DT = X_TRANSFER_DT
455 AND COURSE_CD = X_COURSE_CD;
456 X_LAST_UPDATE_DATE DATE;
457 X_LAST_UPDATED_BY NUMBER;
458 X_LAST_UPDATE_LOGIN NUMBER;
459 BEGIN
460 X_LAST_UPDATE_DATE := SYSDATE;
461 IF(X_MODE = 'I') THEN
462 X_LAST_UPDATED_BY := 1;
463 X_LAST_UPDATE_LOGIN := 0;
464 ELSIF (X_MODE = 'R') THEN
465 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
466 IF X_LAST_UPDATED_BY IS NULL THEN
467 X_LAST_UPDATED_BY := -1;
468 END IF;
469 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
470 IF X_LAST_UPDATE_LOGIN IS NULL THEN
471 X_LAST_UPDATE_LOGIN := -1;
472 END IF;
473 ELSE
474 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 END IF;
478 Before_DML( p_action => 'INSERT',
479 x_rowid => X_ROWID,
480 x_person_id => X_PERSON_ID,
481 x_course_cd => X_COURSE_CD,
482 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
483 x_transfer_dt => X_TRANSFER_DT,
484 x_comments => X_COMMENTS,
485 x_approved_date => X_APPROVED_DATE,
486 x_effective_term_cal_type => X_EFFECTIVE_TERM_CAL_TYPE,
487 x_effective_term_sequence_num => X_EFFECTIVE_TERM_SEQUENCE_NUM,
488 x_discontinue_source_flag => X_DISCONTINUE_SOURCE_FLAG,
489 x_uooids_to_transfer => X_UOOIDS_TO_TRANSFER,
490 x_susa_to_transfer => X_SUSA_TO_TRANSFER,
491 x_transfer_adv_stand_flag => X_TRANSFER_ADV_STAND_FLAG,
492 x_status_date => X_STATUS_DATE,
493 x_status_flag => nvl(X_STATUS_FLAG,'T'),
494 x_creation_date => X_LAST_UPDATE_DATE,
495 x_created_by => X_LAST_UPDATED_BY,
496 x_last_update_date => X_LAST_UPDATE_DATE,
497 x_last_updated_by => X_LAST_UPDATED_BY,
498 x_last_update_login => X_LAST_UPDATE_LOGIN
499 );
500
501 INSERT INTO IGS_PS_STDNT_TRN (
502 PERSON_ID,
503 COURSE_CD,
504 TRANSFER_COURSE_CD,
505 TRANSFER_DT,
506 COMMENTS,
507 APPROVED_DATE,
508 EFFECTIVE_TERM_CAL_TYPE,
509 EFFECTIVE_TERM_SEQUENCE_NUM,
510 DISCONTINUE_SOURCE_FLAG,
511 UOOIDS_TO_TRANSFER,
512 SUSA_TO_TRANSFER,
513 TRANSFER_ADV_STAND_FLAG,
514 STATUS_DATE,
515 STATUS_FLAG,
516 CREATION_DATE,
517 CREATED_BY,
518 LAST_UPDATE_DATE,
519 LAST_UPDATED_BY,
520 LAST_UPDATE_LOGIN
521 ) VALUES (
522 NEW_REFERENCES.PERSON_ID,
523 NEW_REFERENCES.COURSE_CD,
524 NEW_REFERENCES.TRANSFER_COURSE_CD,
525 NEW_REFERENCES.TRANSFER_DT,
526 NEW_REFERENCES.COMMENTS,
527 NEW_REFERENCES.APPROVED_DATE,
528 NEW_REFERENCES.EFFECTIVE_TERM_CAL_TYPE,
529 NEW_REFERENCES.EFFECTIVE_TERM_SEQUENCE_NUM,
530 NEW_REFERENCES.DISCONTINUE_SOURCE_FLAG,
531 NEW_REFERENCES.UOOIDS_TO_TRANSFER,
532 NEW_REFERENCES.SUSA_TO_TRANSFER,
533 NEW_REFERENCES.TRANSFER_ADV_STAND_FLAG,
534 NEW_REFERENCES.STATUS_DATE,
535 NEW_REFERENCES.STATUS_FLAG,
536 X_LAST_UPDATE_DATE,
537 X_LAST_UPDATED_BY,
538 X_LAST_UPDATE_DATE,
539 X_LAST_UPDATED_BY,
540 X_LAST_UPDATE_LOGIN
541 );
542
543 OPEN c;
544 FETCH c INTO X_ROWID;
545 IF (c%NOTFOUND) THEN
546 CLOSE c;
547 RAISE NO_DATA_FOUND;
548 END IF;
549 CLOSE c;
550 After_DML(
551 p_action => 'INSERT',
552 x_rowid => X_ROWID
553 );
554
555 END INSERT_ROW;
556
557 PROCEDURE LOCK_ROW (
558 X_ROWID IN VARCHAR2,
559 X_PERSON_ID IN NUMBER,
560 X_TRANSFER_COURSE_CD IN VARCHAR2,
561 X_TRANSFER_DT IN DATE,
562 X_COURSE_CD IN VARCHAR2,
563 X_COMMENTS IN VARCHAR2,
564 X_APPROVED_DATE IN DATE,
565 X_EFFECTIVE_TERM_CAL_TYPE IN VARCHAR2,
566 X_EFFECTIVE_TERM_SEQUENCE_NUM IN NUMBER,
567 X_DISCONTINUE_SOURCE_FLAG IN VARCHAR2,
568 X_UOOIDS_TO_TRANSFER IN VARCHAR2,
569 X_SUSA_TO_TRANSFER IN VARCHAR2,
570 X_TRANSFER_ADV_STAND_FLAG IN VARCHAR2,
571 X_STATUS_DATE IN DATE ,
572 X_STATUS_FLAG IN VARCHAR2
573 ) AS
574 CURSOR c1 IS SELECT
575 COMMENTS, APPROVED_DATE, EFFECTIVE_TERM_CAL_TYPE, EFFECTIVE_TERM_SEQUENCE_NUM, DISCONTINUE_SOURCE_FLAG,
576 UOOIDS_TO_TRANSFER, SUSA_TO_TRANSFER, TRANSFER_ADV_STAND_FLAG, STATUS_DATE, STATUS_FLAG
577 FROM IGS_PS_STDNT_TRN
578 WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
579 tlinfo c1%ROWTYPE;
580
581 BEGIN
582 OPEN c1;
583 FETCH c1 INTO tlinfo;
584 IF (c1%NOTFOUND) THEN
585 CLOSE c1;
586 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
587 IGS_GE_MSG_STACK.ADD;
588 app_exception.raise_exception;
589 RETURN;
590 END IF;
591 CLOSE c1;
592
593 IF ( ((tlinfo.COMMENTS = X_COMMENTS)
594 OR ((tlinfo.COMMENTS IS NULL)
595 AND (X_COMMENTS IS NULL)))
596
597 AND ((tlinfo.APPROVED_DATE = X_APPROVED_DATE)
598 OR ((tlinfo.APPROVED_DATE IS NULL)
599 AND (X_APPROVED_DATE IS NULL)))
600
601 AND ((tlinfo.EFFECTIVE_TERM_CAL_TYPE = X_EFFECTIVE_TERM_CAL_TYPE)
602 OR ((tlinfo.EFFECTIVE_TERM_CAL_TYPE IS NULL)
603 AND (X_EFFECTIVE_TERM_CAL_TYPE IS NULL)))
604
605 AND ((tlinfo.EFFECTIVE_TERM_SEQUENCE_NUM = X_EFFECTIVE_TERM_SEQUENCE_NUM)
606 OR ((tlinfo.EFFECTIVE_TERM_SEQUENCE_NUM IS NULL)
607 AND (X_EFFECTIVE_TERM_SEQUENCE_NUM IS NULL)))
608
609 AND ((tlinfo.DISCONTINUE_SOURCE_FLAG = X_DISCONTINUE_SOURCE_FLAG)
610 OR ((tlinfo.DISCONTINUE_SOURCE_FLAG IS NULL)
611 AND (X_DISCONTINUE_SOURCE_FLAG IS NULL)))
612
613 AND ((tlinfo.UOOIDS_TO_TRANSFER = X_UOOIDS_TO_TRANSFER)
614 OR ((tlinfo.UOOIDS_TO_TRANSFER IS NULL)
615 AND (X_UOOIDS_TO_TRANSFER IS NULL)))
616
617 AND ((tlinfo.SUSA_TO_TRANSFER = X_SUSA_TO_TRANSFER)
618 OR ((tlinfo.SUSA_TO_TRANSFER IS NULL)
619 AND (X_SUSA_TO_TRANSFER IS NULL)))
620
621 AND ((tlinfo.TRANSFER_ADV_STAND_FLAG = X_TRANSFER_ADV_STAND_FLAG)
622 OR ((tlinfo.TRANSFER_ADV_STAND_FLAG IS NULL)
623 AND (X_TRANSFER_ADV_STAND_FLAG IS NULL)))
624
625
626 AND ((tlinfo.STATUS_DATE = X_STATUS_DATE)
627 OR ((tlinfo.STATUS_DATE IS NULL)
628 AND (X_STATUS_DATE IS NULL)))
629
630 AND ((tlinfo.STATUS_FLAG = X_STATUS_FLAG)
631 OR ((tlinfo.STATUS_FLAG IS NULL)
632 AND (X_STATUS_FLAG IS NULL)))
633
634 ) THEN
635 NULL;
636 ELSE
637 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
638 IGS_GE_MSG_STACK.ADD;
639 app_exception.raise_exception;
640 END IF;
641 RETURN;
642 END LOCK_ROW;
643
644 PROCEDURE UPDATE_ROW (
645 X_ROWID IN VARCHAR2,
646 X_PERSON_ID IN NUMBER,
647 X_TRANSFER_COURSE_CD IN VARCHAR2,
648 X_TRANSFER_DT IN DATE,
649 X_COURSE_CD IN VARCHAR2,
650 X_COMMENTS IN VARCHAR2,
651 X_MODE IN VARCHAR2,
652 X_APPROVED_DATE IN DATE,
653 X_EFFECTIVE_TERM_CAL_TYPE IN VARCHAR2,
654 X_EFFECTIVE_TERM_SEQUENCE_NUM IN NUMBER,
655 X_DISCONTINUE_SOURCE_FLAG IN VARCHAR2,
656 X_UOOIDS_TO_TRANSFER IN VARCHAR2,
657 X_SUSA_TO_TRANSFER IN VARCHAR2,
658 X_TRANSFER_ADV_STAND_FLAG IN VARCHAR2,
659 X_STATUS_DATE IN DATE,
660 X_STATUS_FLAG IN VARCHAR2
661
662 ) AS
663 X_LAST_UPDATE_DATE DATE;
664 X_LAST_UPDATED_BY NUMBER;
665 X_LAST_UPDATE_LOGIN NUMBER;
666 BEGIN
667 X_LAST_UPDATE_DATE := SYSDATE;
668 IF(X_MODE = 'I') THEN
669 X_LAST_UPDATED_BY := 1;
670 X_LAST_UPDATE_LOGIN := 0;
671 ELSIF (X_MODE = 'R') THEN
672 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
673 IF X_LAST_UPDATED_BY IS NULL THEN
674 X_LAST_UPDATED_BY := -1;
675 END IF;
676 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
677 IF X_LAST_UPDATE_LOGIN IS NULL THEN
678 X_LAST_UPDATE_LOGIN := -1;
679 END IF;
680 ELSE
681 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
682 IGS_GE_MSG_STACK.ADD;
683 app_exception.raise_exception;
684 END IF;
685 Before_DML( p_action => 'UPDATE',
686 x_rowid => X_ROWID,
687 x_person_id => X_PERSON_ID,
688 x_course_cd => X_COURSE_CD,
689 x_transfer_course_cd => X_TRANSFER_COURSE_CD,
690 x_transfer_dt => X_TRANSFER_DT,
691 x_comments => X_COMMENTS,
692 x_approved_date => X_APPROVED_DATE,
693 x_effective_term_cal_type => X_EFFECTIVE_TERM_CAL_TYPE,
694 x_effective_term_sequence_num => X_EFFECTIVE_TERM_SEQUENCE_NUM,
695 x_discontinue_source_flag => X_DISCONTINUE_SOURCE_FLAG,
696 x_uooids_to_transfer => X_UOOIDS_TO_TRANSFER,
697 x_susa_to_transfer => X_SUSA_TO_TRANSFER,
698 x_transfer_adv_stand_flag => X_TRANSFER_ADV_STAND_FLAG,
699 x_status_date => X_STATUS_DATE,
700 x_status_flag => nvl(X_STATUS_FLAG, 'T'),
701 x_creation_date => X_LAST_UPDATE_DATE,
702 x_created_by => X_LAST_UPDATED_BY,
703 x_last_update_date => X_LAST_UPDATE_DATE,
704 x_last_updated_by => X_LAST_UPDATED_BY,
705 x_last_update_login => X_LAST_UPDATE_LOGIN
706 );
707
708 UPDATE IGS_PS_STDNT_TRN SET
709 COMMENTS = NEW_REFERENCES.COMMENTS,
710 APPROVED_DATE = NEW_REFERENCES.APPROVED_DATE,
711 EFFECTIVE_TERM_CAL_TYPE = NEW_REFERENCES.EFFECTIVE_TERM_CAL_TYPE,
712 EFFECTIVE_TERM_SEQUENCE_NUM = NEW_REFERENCES.EFFECTIVE_TERM_SEQUENCE_NUM,
713 DISCONTINUE_SOURCE_FLAG = NEW_REFERENCES.DISCONTINUE_SOURCE_FLAG,
714 UOOIDS_TO_TRANSFER = NEW_REFERENCES.UOOIDS_TO_TRANSFER,
715 SUSA_TO_TRANSFER = NEW_REFERENCES.SUSA_TO_TRANSFER,
716 TRANSFER_ADV_STAND_FLAG = NEW_REFERENCES.TRANSFER_ADV_STAND_FLAG,
717 STATUS_DATE = NEW_REFERENCES.STATUS_DATE,
718 STATUS_FLAG = NEW_REFERENCES.STATUS_FLAG,
719 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
720 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
721 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
722 WHERE ROWID = X_ROWID
723 ;
724 IF (SQL%NOTFOUND) THEN
725 RAISE NO_DATA_FOUND;
726 END IF;
727 After_DML(
728 p_action => 'UPDATE',
729 x_rowid => X_ROWID
730 );
731
732 END UPDATE_ROW;
733
734 PROCEDURE ADD_ROW (
735 X_ROWID IN OUT NOCOPY VARCHAR2,
736 X_PERSON_ID IN NUMBER,
737 X_TRANSFER_COURSE_CD IN VARCHAR2,
738 X_TRANSFER_DT IN DATE,
739 X_COURSE_CD IN VARCHAR2,
740 X_COMMENTS IN VARCHAR2,
741 X_MODE IN VARCHAR2,
742 X_APPROVED_DATE IN DATE,
743 X_EFFECTIVE_TERM_CAL_TYPE IN VARCHAR2,
744 X_EFFECTIVE_TERM_SEQUENCE_NUM IN NUMBER,
745 X_DISCONTINUE_SOURCE_FLAG IN VARCHAR2,
746 X_UOOIDS_TO_TRANSFER IN VARCHAR2,
747 X_SUSA_TO_TRANSFER IN VARCHAR2,
748 X_TRANSFER_ADV_STAND_FLAG IN VARCHAR2,
749 X_STATUS_DATE IN DATE,
750 X_STATUS_FLAG IN VARCHAR2
751
752 ) AS
753 CURSOR c1 IS SELECT ROWID FROM IGS_PS_STDNT_TRN
754 WHERE PERSON_ID = X_PERSON_ID
755 AND TRANSFER_COURSE_CD = X_TRANSFER_COURSE_CD
756 AND TRANSFER_DT = X_TRANSFER_DT
757 AND COURSE_CD = X_COURSE_CD
758 ;
759 BEGIN
760 OPEN c1;
761 FETCH c1 INTO X_ROWID;
762 IF (c1%NOTFOUND) THEN
763 CLOSE c1;
764 INSERT_ROW (
765 X_ROWID,
766 X_PERSON_ID,
767 X_TRANSFER_COURSE_CD,
768 X_TRANSFER_DT,
769 X_COURSE_CD,
770 X_COMMENTS,
771 X_MODE,
772 X_APPROVED_DATE,
773 X_EFFECTIVE_TERM_CAL_TYPE,
774 X_EFFECTIVE_TERM_SEQUENCE_NUM,
775 X_DISCONTINUE_SOURCE_FLAG,
776 X_UOOIDS_TO_TRANSFER,
777 X_SUSA_TO_TRANSFER,
778 X_TRANSFER_ADV_STAND_FLAG,
779 X_STATUS_DATE,
780 X_STATUS_FLAG);
781 RETURN;
782 END IF;
783 CLOSE c1;
784 UPDATE_ROW (
785 X_ROWID,
786 X_PERSON_ID,
787 X_TRANSFER_COURSE_CD,
788 X_TRANSFER_DT,
789 X_COURSE_CD,
790 X_COMMENTS,
791 X_MODE,
792 X_APPROVED_DATE,
793 X_EFFECTIVE_TERM_CAL_TYPE,
794 X_EFFECTIVE_TERM_SEQUENCE_NUM,
795 X_DISCONTINUE_SOURCE_FLAG,
796 X_UOOIDS_TO_TRANSFER,
797 X_SUSA_TO_TRANSFER,
798 X_TRANSFER_ADV_STAND_FLAG,
799 X_STATUS_DATE,
800 X_STATUS_FLAG);
801 END ADD_ROW;
802
803 PROCEDURE DELETE_ROW (
804 X_ROWID IN VARCHAR2
805 ) AS
806 BEGIN
807 Before_DML( p_action => 'DELETE',
808 x_rowid => X_ROWID
809 );
810 DELETE FROM IGS_PS_STDNT_TRN
811 WHERE ROWID = X_ROWID;
812 IF (SQL%NOTFOUND) THEN
813 RAISE NO_DATA_FOUND;
814 END IF;
815 After_DML(
816 p_action => 'DELETE',
817 x_rowid => X_ROWID
818 );
819 END DELETE_ROW;
820
821 END Igs_Ps_Stdnt_Trn_Pkg;