[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_STDNT_APV_ALT_PKG
Source
1 package body IGS_PS_STDNT_APV_ALT_PKG as
2 /* $Header: IGSPI65B.pls 120.2 2005/07/05 02:37:43 appldev ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_pr_val_scaae.genp_val_sdtt_sess
7 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
8 -------------------------------------------------------------------------------------------
9 l_rowid VARCHAR2(25);
10 old_references IGS_PS_STDNT_APV_ALT%RowType;
11 new_references IGS_PS_STDNT_APV_ALT%RowType;
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_exit_course_cd IN VARCHAR2 DEFAULT NULL,
19 x_exit_version_number IN NUMBER DEFAULT NULL,
20 x_version_number IN NUMBER DEFAULT NULL,
21 x_rqrmnts_complete_ind IN VARCHAR2 DEFAULT NULL,
22 x_rqrmnts_complete_dt IN DATE DEFAULT NULL,
23 x_s_completed_source_type IN VARCHAR2 DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL
29 ) AS
30
31 CURSOR cur_old_ref_values IS
32 SELECT *
33 FROM IGS_PS_STDNT_APV_ALT
34 WHERE rowid = x_rowid;
35
36 BEGIN
37
38 l_rowid := x_rowid;
39
40 -- Code for setting the Old and New Reference Values.
41 -- Populate Old Values.
42 Open cur_old_ref_values;
43 Fetch cur_old_ref_values INTO old_references;
44 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
45 Close cur_old_ref_values;
46 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
47 IGS_GE_MSG_STACK.ADD;
48 App_Exception.Raise_Exception;
49 Return;
50 END IF;
51 Close cur_old_ref_values;
52
53 -- Populate New Values.
54 new_references.person_id := x_person_id;
55 new_references.course_cd := x_course_cd;
56 new_references.exit_course_cd := x_exit_course_cd;
57 new_references.exit_version_number := x_exit_version_number;
58 new_references.version_number := x_version_number;
59 new_references.rqrmnts_complete_ind := x_rqrmnts_complete_ind;
60 new_references.rqrmnts_complete_dt := x_rqrmnts_complete_dt;
61 new_references.s_completed_source_type := x_s_completed_source_type;
62 IF (p_action = 'UPDATE') THEN
63 new_references.creation_date := old_references.creation_date;
64 new_references.created_by := old_references.created_by;
65 ELSE
66 new_references.creation_date := x_creation_date;
67 new_references.created_by := x_created_by;
68 END IF;
69 new_references.last_update_date := x_last_update_date;
70 new_references.last_updated_by := x_last_updated_by;
71 new_references.last_update_login := x_last_update_login;
72
73 END Set_Column_Values;
74
75 PROCEDURE BeforeRowInsertUpdate1(
76 p_inserting IN BOOLEAN DEFAULT FALSE,
77 p_updating IN BOOLEAN DEFAULT FALSE,
78 p_deleting IN BOOLEAN DEFAULT FALSE
79 ) AS
80 v_message_name VARCHAR2(30);
81 BEGIN
82 -- If trigger has not been disabled, perform required processing
83 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_PS_STDNT_APV_ALT') THEN
84 IF p_inserting OR p_updating THEN
85 -- Validate completing an alternative exit IGS_PS_COURSE
86 IF p_inserting OR
87 (p_updating AND
88 new_references.rqrmnts_complete_ind <> old_references.rqrmnts_complete_ind) THEN
89 IF new_references.rqrmnts_complete_ind = 'Y' THEN
90 -- Validate that SCA Status is not 'COMPLETED' or 'UNCONFIRM'.
91 IF IGS_PR_VAL_SCA.prgp_val_sca_status (
92 new_references.person_id,
93 new_references.course_cd,
94 v_message_name) = FALSE THEN
95 Fnd_Message.Set_Name('IGS', v_message_name);
96 IGS_GE_MSG_STACK.ADD;
97 App_Exception.Raise_Exception;
98 END IF;
99 -- Validate that no IGS_PS_UNIT sets are incomplete or un-ended.
100 IF IGS_PR_VAL_SCA.prgp_val_susa_cmplt (
101 new_references.person_id,
102 new_references.course_cd,
103 v_message_name) = FALSE THEN
104 Fnd_Message.Set_Name('IGS', v_message_name);
105 IGS_GE_MSG_STACK.ADD;
106 App_Exception.Raise_Exception;
107 END IF;
108 -- Validate that SCA status is not DISCONTIN, INTERMIT or LAPSED.
109 IF IGS_PR_VAL_SCAAE.prgp_val_sca_cmplt (
110 new_references.person_id,
111 new_references.course_cd,
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 ELSE
118 -- Check that associated IGS_GR_GRADUAND record does not have a status
119 -- of 'GRADUATED' or 'SURRENDER'.
120 IF IGS_PR_VAL_SCA.prgp_val_undo_cmpltn (
121 new_references.person_id,
122 new_references.course_cd,
123 new_references.version_number,
124 new_references.exit_course_cd,
125 new_references.exit_version_number,
126 v_message_name) = FALSE THEN
127 Fnd_Message.Set_Name('IGS', v_message_name);
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131 END IF;
132 END IF;
133 -- Validate completion details
134 IF p_inserting OR
135 (p_updating AND
136 ((new_references.rqrmnts_complete_dt IS NULL AND
137 old_references.rqrmnts_complete_dt IS NOT NULL) OR
138 (new_references.rqrmnts_complete_dt IS NOT NULL AND
139 old_references.rqrmnts_complete_dt IS NULL) OR
140 (new_references.rqrmnts_complete_dt IS NOT NULL AND
141 old_references.rqrmnts_complete_dt <>new_references.rqrmnts_complete_dt))) THEN
142 IF IGS_PR_VAL_SCAAE.prgp_val_scaae_cmplt(
143 new_references.rqrmnts_complete_ind,
144 new_references.rqrmnts_complete_dt,
145 v_message_name) = FALSE THEN
146 Fnd_Message.Set_Name('IGS', v_message_name);
147 IGS_GE_MSG_STACK.ADD;
148 App_Exception.Raise_Exception;
149 END IF;
150 END IF;
151 END IF;
152 END IF;
153
154
155 END BeforeRowInsertUpdate1;
156
157 PROCEDURE Check_Constraints (
158 Column_Name IN VARCHAR2 DEFAULT NULL,
159 Column_Value IN VARCHAR2 DEFAULT NULL
160 )
161 AS
162 BEGIN
163
164 IF column_name is null then
165 NULL;
166 ELSIF upper(Column_name) = 'COURSE_CD' then
167 new_references.course_cd := column_value;
168 ELSIF upper(Column_name) = 'EXIT_COURSE_CD' then
169 new_references.exit_course_cd := column_value;
170 ELSIF upper(Column_name) = 'RQRMNTS_COMPLETE_IND' then
171 new_references.rqrmnts_complete_ind := column_value;
172 ELSIF upper(Column_name) = 'S_COMPLETED_SOURCE_TYPE' then
173 new_references.s_completed_source_type := column_value;
174 END IF;
175
176 IF upper(column_name) = 'COURSE_CD' OR
177 column_name is null Then
178 IF new_references.course_cd <> UPPER(new_references.course_cd) Then
179 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183 END IF;
184
185 IF upper(column_name) = 'EXIT_COURSE_CD' OR
186 column_name is null Then
187 IF new_references.exit_course_cd <> UPPER(new_references.exit_course_cd) Then
188 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
189 IGS_GE_MSG_STACK.ADD;
190 App_Exception.Raise_Exception;
191 END IF;
192 END IF;
193
194 IF upper(column_name) = 'S_COMPLETED_SOURCE_TYPE' OR
195 column_name is null Then
196 IF new_references.s_completed_source_type NOT IN ( 'SYSTEM' , 'MANUAL' ) Then
197 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
198 IGS_GE_MSG_STACK.ADD;
199 App_Exception.Raise_Exception;
200 END IF;
201 END IF;
202
203 IF upper(column_name) = 'RQRMNTS_COMPLETE_IND' OR
204 column_name is null Then
205 IF new_references.rqrmnts_complete_ind NOT IN ( 'Y' , 'N' ) Then
206 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
207 IGS_GE_MSG_STACK.ADD;
208 App_Exception.Raise_Exception;
209 END IF;
210 END IF;
211
212 END check_constraints;
213
214
215 PROCEDURE Check_Parent_Existance AS
216 BEGIN
217
218 IF (((old_references.course_cd = new_references.course_cd) AND
219 (old_references.version_number = new_references.version_number) AND
220 (old_references.exit_course_cd = new_references.exit_course_cd)) OR
221 ((new_references.course_cd IS NULL) OR
222 (new_references.version_number IS NULL) OR
223 (new_references.exit_course_cd IS NULL))) THEN
224 NULL;
225 ELSE
226 IF NOT IGS_PE_ALTERNATV_EXT_PKG.Get_PK_For_Validation (
227 new_references.course_cd,
228 new_references.version_number,
229 new_references.exit_course_cd
230 ) THEN
231 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235 END IF;
236
237 IF (((old_references.exit_course_cd = new_references.exit_course_cd) AND
238 (old_references.exit_version_number = new_references.exit_version_number)) OR
239 ((new_references.exit_course_cd IS NULL) OR
240 (new_references.exit_version_number IS NULL))) THEN
241 NULL;
242 ELSE
243 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
244 new_references.exit_course_cd,
245 new_references.exit_version_number
246 ) THEN
247 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
248 IGS_GE_MSG_STACK.ADD;
249 App_Exception.Raise_Exception;
250 END IF;
251 END IF;
252
253 IF (((old_references.course_cd = new_references.course_cd) AND
254 (old_references.version_number = new_references.version_number)) OR
255 ((new_references.course_cd IS NULL) OR
256 (new_references.version_number IS NULL))) THEN
257 NULL;
258 ELSE
259 IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
260 new_references.course_cd,
261 new_references.version_number
262 ) THEN
263 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268
269 IF (((old_references.person_id = new_references.person_id) AND
270 (old_references.course_cd = new_references.course_cd)) OR
271 ((new_references.person_id IS NULL) OR
272 (new_references.course_cd IS NULL))) THEN
273 NULL;
274 ELSE
275 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
276 new_references.person_id,
277 new_references.course_cd
278 ) THEN
279 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
280 IGS_GE_MSG_STACK.ADD;
281 App_Exception.Raise_Exception;
282 END IF;
283 END IF;
284 END Check_Parent_Existance;
285
286 FUNCTION Get_PK_For_Validation (
287 x_person_id IN NUMBER,
288 x_course_cd IN VARCHAR2,
289 x_exit_course_cd IN VARCHAR2,
290 x_exit_version_number IN NUMBER
291 ) RETURN BOOLEAN AS
292
293 CURSOR cur_rowid IS
294 SELECT rowid
295 FROM IGS_PS_STDNT_APV_ALT
296 WHERE person_id = x_person_id
297 AND course_cd = x_course_cd
298 AND exit_course_cd = x_exit_course_cd
299 AND exit_version_number = x_exit_version_number
300 FOR UPDATE NOWAIT;
301
302 lv_rowid cur_rowid%RowType;
303
304 BEGIN
305
306 Open cur_rowid;
307 Fetch cur_rowid INTO lv_rowid;
308 IF (cur_rowid%FOUND) THEN
309 Close cur_rowid;
310 Return (TRUE);
311 ELSE
312 Close cur_rowid;
313 Return (FALSE);
314 END IF;
315 END Get_PK_For_Validation;
316
317 PROCEDURE GET_FK_IGS_PE_ALTERNATV_EXT (
318 x_course_cd IN VARCHAR2,
319 x_version_number IN NUMBER,
320 x_exit_course_cd IN VARCHAR2
321 ) AS
322
323 CURSOR cur_rowid IS
324 SELECT rowid
325 FROM IGS_PS_STDNT_APV_ALT
326 WHERE course_cd = x_course_cd
327 AND version_number = x_version_number
328 AND exit_course_cd = x_exit_course_cd ;
329
330 lv_rowid cur_rowid%RowType;
331
332 BEGIN
333
334 Open cur_rowid;
335 Fetch cur_rowid INTO lv_rowid;
336 IF (cur_rowid%FOUND) THEN
337 Close cur_rowid;
338 Fnd_Message.Set_Name ('IGS', 'IGS_PS_SCAAE_AE_FK');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 Return;
342 END IF;
343 Close cur_rowid;
344
345 END GET_FK_IGS_PE_ALTERNATV_EXT;
346
347 PROCEDURE GET_FK_IGS_PS_VER (
348 x_course_cd IN VARCHAR2,
349 x_version_number IN NUMBER
350 ) AS
351
352 CURSOR cur_rowid IS
353 SELECT rowid
354 FROM IGS_PS_STDNT_APV_ALT
355 WHERE (exit_course_cd = x_course_cd
356 AND exit_version_number = x_version_number)
357 OR (course_cd = x_course_cd
358 AND version_number = x_version_number);
359
360 lv_rowid cur_rowid%RowType;
361
362 BEGIN
363
364 Open cur_rowid;
365 Fetch cur_rowid INTO lv_rowid;
366 IF (cur_rowid%FOUND) THEN
367 Close cur_rowid;
368 Fnd_Message.Set_Name ('IGS', 'IGS_PS_SCAAE_CRV_EXIT_FK');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 Return;
372 END IF;
373 Close cur_rowid;
374
375 END GET_FK_IGS_PS_VER;
376
377
378 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
379 x_person_id IN NUMBER,
380 x_course_cd IN VARCHAR2
381 ) AS
382
383 CURSOR cur_rowid IS
384 SELECT rowid
385 FROM IGS_PS_STDNT_APV_ALT
386 WHERE person_id = x_person_id
387 AND course_cd = x_course_cd ;
388
389 lv_rowid cur_rowid%RowType;
390
391 BEGIN
392
393 Open cur_rowid;
394 Fetch cur_rowid INTO lv_rowid;
395 IF (cur_rowid%FOUND) THEN
396 Close cur_rowid;
397 Fnd_Message.Set_Name ('IGS', 'IGS_PS_SCAAE_SCA_FK');
398 IGS_GE_MSG_STACK.ADD;
399 App_Exception.Raise_Exception;
400 Return;
401 END IF;
402 Close cur_rowid;
403
404 END GET_FK_IGS_EN_STDNT_PS_ATT;
405
406 PROCEDURE Before_DML (
407 p_action IN VARCHAR2,
408 x_rowid IN VARCHAR2 DEFAULT NULL,
409 x_person_id IN NUMBER DEFAULT NULL,
410 x_course_cd IN VARCHAR2 DEFAULT NULL,
411 x_exit_course_cd IN VARCHAR2 DEFAULT NULL,
412 x_exit_version_number IN NUMBER DEFAULT NULL,
413 x_version_number IN NUMBER DEFAULT NULL,
414 x_rqrmnts_complete_ind IN VARCHAR2 DEFAULT NULL,
415 x_rqrmnts_complete_dt IN DATE DEFAULT NULL,
416 x_s_completed_source_type IN VARCHAR2 DEFAULT NULL,
417 x_creation_date IN DATE DEFAULT NULL,
418 x_created_by IN NUMBER DEFAULT NULL,
419 x_last_update_date IN DATE DEFAULT NULL,
420 x_last_updated_by IN NUMBER DEFAULT NULL,
421 x_last_update_login IN NUMBER DEFAULT NULL
422 ) AS
423 BEGIN
424
425 Set_Column_Values (
426 p_action,
427 x_rowid,
428 x_person_id,
429 x_course_cd,
430 x_exit_course_cd,
431 x_exit_version_number,
432 x_version_number,
433 x_rqrmnts_complete_ind,
434 x_rqrmnts_complete_dt,
435 x_s_completed_source_type,
436 x_creation_date,
437 x_created_by,
438 x_last_update_date,
439 x_last_updated_by,
440 x_last_update_login
441 );
442
443 IF (p_action = 'INSERT') THEN
444 -- Call all the procedures related to Before Insert.
445 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
446 IF Get_PK_For_Validation (
447 new_references.person_id,
448 new_references.course_cd,
449 new_references.exit_course_cd,
450 new_references.exit_version_number
451 ) THEN
452 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
453 IGS_GE_MSG_STACK.ADD;
454 App_Exception.Raise_Exception;
455 END IF;
456 Check_Constraints;
457 Check_Parent_Existance;
458 ELSIF (p_action = 'UPDATE') THEN
459 -- Call all the procedures related to Before Update.
460 BeforeRowInsertUpdate1 ( p_updating => TRUE );
461 Check_Constraints;
462 Check_Parent_Existance;
463 ELSIF (p_action = 'VALIDATE_INSERT') THEN
464 IF Get_PK_For_Validation (
465 new_references.person_id,
466 new_references.course_cd,
467 new_references.exit_course_cd,
468 new_references.exit_version_number
469 ) THEN
470 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
471 IGS_GE_MSG_STACK.ADD;
472 App_Exception.Raise_Exception;
473 END IF;
474 Check_Constraints;
475 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
476 Check_Constraints;
477
478 END IF;
479
480
481 END Before_DML;
482
483 PROCEDURE After_DML (
484 p_action IN VARCHAR2,
485 x_rowid IN VARCHAR2
486 ) AS
487 BEGIN
488
489 l_rowid := x_rowid;
490
491
492 END After_DML;
493
494 procedure INSERT_ROW (
495 X_ROWID in out NOCOPY VARCHAR2,
496 X_PERSON_ID in NUMBER,
497 X_EXIT_COURSE_CD in VARCHAR2,
498 X_EXIT_VERSION_NUMBER in NUMBER,
499 X_COURSE_CD in VARCHAR2,
500 X_VERSION_NUMBER in NUMBER,
501 X_RQRMNTS_COMPLETE_IND in VARCHAR2,
502 X_RQRMNTS_COMPLETE_DT in DATE,
503 X_S_COMPLETED_SOURCE_TYPE in VARCHAR2,
504 X_MODE in VARCHAR2 default 'R'
505 ) as
506 cursor C is select ROWID from IGS_PS_STDNT_APV_ALT
507 where PERSON_ID = X_PERSON_ID
508 and EXIT_COURSE_CD = X_EXIT_COURSE_CD
509 and EXIT_VERSION_NUMBER = X_EXIT_VERSION_NUMBER
510 and COURSE_CD = X_COURSE_CD;
511 X_LAST_UPDATE_DATE DATE;
512 X_LAST_UPDATED_BY NUMBER;
513 X_LAST_UPDATE_LOGIN NUMBER;
514 begin
515 X_LAST_UPDATE_DATE := SYSDATE;
516 if(X_MODE = 'I') then
517 X_LAST_UPDATED_BY := 1;
518 X_LAST_UPDATE_LOGIN := 0;
519 elsif (X_MODE IN ('R', 'S')) then
520 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
521 if X_LAST_UPDATED_BY is NULL then
522 X_LAST_UPDATED_BY := -1;
523 end if;
524 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
525 if X_LAST_UPDATE_LOGIN is NULL then
526 X_LAST_UPDATE_LOGIN := -1;
527 end if;
528 else
529 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
530 IGS_GE_MSG_STACK.ADD;
531 app_exception.raise_exception;
532 end if;
533
534 Before_DML( p_action => 'INSERT',
535 x_rowid => X_ROWID,
536 x_person_id => X_PERSON_ID,
537 x_course_cd => X_COURSE_CD,
538 x_exit_course_cd => X_EXIT_COURSE_CD,
539 x_exit_version_number => X_EXIT_VERSION_NUMBER,
540 x_version_number => X_VERSION_NUMBER,
541 x_rqrmnts_complete_ind => NVL(X_RQRMNTS_COMPLETE_IND,'N'),
542 x_rqrmnts_complete_dt => X_RQRMNTS_COMPLETE_DT,
543 x_s_completed_source_type => X_S_COMPLETED_SOURCE_TYPE,
544 x_creation_date => X_LAST_UPDATE_DATE,
545 x_created_by => X_LAST_UPDATED_BY,
546 x_last_update_date => X_LAST_UPDATE_DATE,
547 x_last_updated_by => X_LAST_UPDATED_BY,
548 x_last_update_login => X_LAST_UPDATE_LOGIN
549 );
550 IF (x_mode = 'S') THEN
551 igs_sc_gen_001.set_ctx('R');
552 END IF;
553 insert into IGS_PS_STDNT_APV_ALT (
554 PERSON_ID,
555 COURSE_CD,
556 EXIT_COURSE_CD,
557 EXIT_VERSION_NUMBER,
558 VERSION_NUMBER,
559 RQRMNTS_COMPLETE_IND,
560 RQRMNTS_COMPLETE_DT,
561 S_COMPLETED_SOURCE_TYPE,
562 CREATION_DATE,
563 CREATED_BY,
564 LAST_UPDATE_DATE,
565 LAST_UPDATED_BY,
566 LAST_UPDATE_LOGIN
567 ) values (
568 NEW_REFERENCES.PERSON_ID,
569 NEW_REFERENCES.COURSE_CD,
570 NEW_REFERENCES.EXIT_COURSE_CD,
571 NEW_REFERENCES.EXIT_VERSION_NUMBER,
572 NEW_REFERENCES.VERSION_NUMBER,
573 NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
574 NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
575 NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
576 X_LAST_UPDATE_DATE,
577 X_LAST_UPDATED_BY,
578 X_LAST_UPDATE_DATE,
579 X_LAST_UPDATED_BY,
580 X_LAST_UPDATE_LOGIN
581 );
582 IF (x_mode = 'S') THEN
583 igs_sc_gen_001.unset_ctx('R');
584 END IF;
585
586
587 open c;
588 fetch c into X_ROWID;
589 if (c%notfound) then
590 close c;
591 raise no_data_found;
592 end if;
593 close c;
594 After_DML(
595 p_action => 'INSERT',
596 x_rowid => X_ROWID
597 );
598
599 EXCEPTION
600 WHEN OTHERS THEN
601 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
602 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
603 fnd_message.set_token ('ERR_CD', SQLCODE);
604 igs_ge_msg_stack.add;
605 igs_sc_gen_001.unset_ctx('R');
606 app_exception.raise_exception;
607 ELSE
608 igs_sc_gen_001.unset_ctx('R');
609 RAISE;
610 END IF;
611
612 end INSERT_ROW;
613
614 procedure LOCK_ROW (
615 X_ROWID in VARCHAR2,
616 X_PERSON_ID in NUMBER,
617 X_EXIT_COURSE_CD in VARCHAR2,
618 X_EXIT_VERSION_NUMBER in NUMBER,
619 X_COURSE_CD in VARCHAR2,
620 X_VERSION_NUMBER in NUMBER,
621 X_RQRMNTS_COMPLETE_IND in VARCHAR2,
622 X_RQRMNTS_COMPLETE_DT in DATE,
623 X_S_COMPLETED_SOURCE_TYPE in VARCHAR2
624 ) as
625 cursor c1 is select
626 VERSION_NUMBER,
627 RQRMNTS_COMPLETE_IND,
628 RQRMNTS_COMPLETE_DT,
629 S_COMPLETED_SOURCE_TYPE
630 from IGS_PS_STDNT_APV_ALT
631 where ROWID = X_ROWID for update nowait;
632 tlinfo c1%rowtype;
633
634 begin
635 open c1;
636 fetch c1 into tlinfo;
637 if (c1%notfound) then
638 close c1;
639 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
640 IGS_GE_MSG_STACK.ADD;
641 app_exception.raise_exception;
642 return;
643 end if;
644 close c1;
645
646 if ( (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
647 AND (tlinfo.RQRMNTS_COMPLETE_IND = X_RQRMNTS_COMPLETE_IND)
648 AND ((tlinfo.RQRMNTS_COMPLETE_DT = X_RQRMNTS_COMPLETE_DT)
649 OR ((tlinfo.RQRMNTS_COMPLETE_DT is null)
650 AND (X_RQRMNTS_COMPLETE_DT is null)))
651 AND ((tlinfo.S_COMPLETED_SOURCE_TYPE = X_S_COMPLETED_SOURCE_TYPE)
652 OR ((tlinfo.S_COMPLETED_SOURCE_TYPE is null)
653 AND (X_S_COMPLETED_SOURCE_TYPE is null)))
654 ) then
655 null;
656 else
657 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
658 IGS_GE_MSG_STACK.ADD;
659 app_exception.raise_exception;
660 end if;
661 return;
662 end LOCK_ROW;
663
664 procedure UPDATE_ROW (
665 X_ROWID in VARCHAR2,
666 X_PERSON_ID in NUMBER,
667 X_EXIT_COURSE_CD in VARCHAR2,
668 X_EXIT_VERSION_NUMBER in NUMBER,
669 X_COURSE_CD in VARCHAR2,
670 X_VERSION_NUMBER in NUMBER,
671 X_RQRMNTS_COMPLETE_IND in VARCHAR2,
672 X_RQRMNTS_COMPLETE_DT in DATE,
673 X_S_COMPLETED_SOURCE_TYPE in VARCHAR2,
674 X_MODE in VARCHAR2 default 'R'
675 ) as
676 X_LAST_UPDATE_DATE DATE;
677 X_LAST_UPDATED_BY NUMBER;
678 X_LAST_UPDATE_LOGIN NUMBER;
679 begin
680 X_LAST_UPDATE_DATE := SYSDATE;
681 if(X_MODE = 'I') then
682 X_LAST_UPDATED_BY := 1;
683 X_LAST_UPDATE_LOGIN := 0;
684 elsif (X_MODE IN ('R', 'S')) then
685 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
686 if X_LAST_UPDATED_BY is NULL then
687 X_LAST_UPDATED_BY := -1;
688 end if;
689 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
690 if X_LAST_UPDATE_LOGIN is NULL then
691 X_LAST_UPDATE_LOGIN := -1;
692 end if;
693 else
694 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
695 IGS_GE_MSG_STACK.ADD;
696 app_exception.raise_exception;
697 end if;
698
699 Before_DML( p_action => 'UPDATE',
700 x_rowid => X_ROWID,
701 x_person_id => X_PERSON_ID,
702 x_course_cd => X_COURSE_CD,
703 x_exit_course_cd => X_EXIT_COURSE_CD,
704 x_exit_version_number => X_EXIT_VERSION_NUMBER,
705 x_version_number => X_VERSION_NUMBER,
706 x_rqrmnts_complete_ind => X_RQRMNTS_COMPLETE_IND,
707 x_rqrmnts_complete_dt => X_RQRMNTS_COMPLETE_DT,
708 x_s_completed_source_type => X_S_COMPLETED_SOURCE_TYPE,
709 x_creation_date => X_LAST_UPDATE_DATE,
710 x_created_by => X_LAST_UPDATED_BY,
711 x_last_update_date => X_LAST_UPDATE_DATE,
712 x_last_updated_by => X_LAST_UPDATED_BY,
713 x_last_update_login => X_LAST_UPDATE_LOGIN
714 );
715 IF (x_mode = 'S') THEN
716 igs_sc_gen_001.set_ctx('R');
717 END IF;
718 update IGS_PS_STDNT_APV_ALT set
719 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
720 RQRMNTS_COMPLETE_IND = NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
721 RQRMNTS_COMPLETE_DT = NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
722 S_COMPLETED_SOURCE_TYPE = NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
723 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
724 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
725 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
726 where ROWID = X_ROWID
727 ;
728 if (sql%notfound) then
729 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
730 igs_ge_msg_stack.add;
731 igs_sc_gen_001.unset_ctx('R');
732 app_exception.raise_exception;
733 end if;
734 IF (x_mode = 'S') THEN
735 igs_sc_gen_001.unset_ctx('R');
736 END IF;
737
738 After_DML(
739 p_action => 'UPDATE',
740 x_rowid => X_ROWID
741 );
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 IF (SQLCODE = (-28115)) THEN
746 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
747 fnd_message.set_token ('ERR_CD', SQLCODE);
748 igs_ge_msg_stack.add;
749 igs_sc_gen_001.unset_ctx('R');
750 app_exception.raise_exception;
751 ELSE
752 igs_sc_gen_001.unset_ctx('R');
753 RAISE;
754 END IF;
755
756 end UPDATE_ROW;
757
758 procedure ADD_ROW (
759 X_ROWID in out NOCOPY VARCHAR2,
760 X_PERSON_ID in NUMBER,
761 X_EXIT_COURSE_CD in VARCHAR2,
762 X_EXIT_VERSION_NUMBER in NUMBER,
763 X_COURSE_CD in VARCHAR2,
764 X_VERSION_NUMBER in NUMBER,
765 X_RQRMNTS_COMPLETE_IND in VARCHAR2,
766 X_RQRMNTS_COMPLETE_DT in DATE,
767 X_S_COMPLETED_SOURCE_TYPE in VARCHAR2,
768 X_MODE in VARCHAR2 default 'R'
769 ) as
770 cursor c1 is select rowid from IGS_PS_STDNT_APV_ALT
771 where PERSON_ID = X_PERSON_ID
772 and EXIT_COURSE_CD = X_EXIT_COURSE_CD
773 and EXIT_VERSION_NUMBER = X_EXIT_VERSION_NUMBER
774 and COURSE_CD = X_COURSE_CD
775 ;
776 begin
777 open c1;
778 fetch c1 into X_ROWID;
779 if (c1%notfound) then
780 close c1;
781 INSERT_ROW (
782 X_ROWID,
783 X_PERSON_ID,
784 X_EXIT_COURSE_CD,
785 X_EXIT_VERSION_NUMBER,
786 X_COURSE_CD,
787 X_VERSION_NUMBER,
788 X_RQRMNTS_COMPLETE_IND,
789 X_RQRMNTS_COMPLETE_DT,
790 X_S_COMPLETED_SOURCE_TYPE,
791 X_MODE);
792 return;
793 end if;
794 close c1;
795 UPDATE_ROW (
796 X_ROWID,
797 X_PERSON_ID,
798 X_EXIT_COURSE_CD,
799 X_EXIT_VERSION_NUMBER,
800 X_COURSE_CD,
801 X_VERSION_NUMBER,
802 X_RQRMNTS_COMPLETE_IND,
803 X_RQRMNTS_COMPLETE_DT,
804 X_S_COMPLETED_SOURCE_TYPE,
805 X_MODE);
806 end ADD_ROW;
807
808 procedure DELETE_ROW (
809 X_ROWID in VARCHAR2,
810 x_mode IN VARCHAR2
811 ) as
812 begin
813 Before_DML( p_action => 'DELETE',
814 x_rowid => X_ROWID
815 );
816 IF (x_mode = 'S') THEN
817 igs_sc_gen_001.set_ctx('R');
818 END IF;
819 delete from IGS_PS_STDNT_APV_ALT
820 where ROWID = X_ROWID;
821 if (sql%notfound) then
822 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
823 igs_ge_msg_stack.add;
824 igs_sc_gen_001.unset_ctx('R');
825 app_exception.raise_exception;
826 end if;
827 IF (x_mode = 'S') THEN
828 igs_sc_gen_001.unset_ctx('R');
829 END IF;
830
831 After_DML(
832 p_action => 'DELETE',
833 x_rowid => X_ROWID
834 );
835
836 end DELETE_ROW;
837
838 end IGS_PS_STDNT_APV_ALT_PKG;