[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_STDNT_PS_INTM_PKG
Source
1 package body IGS_EN_STDNT_PS_INTM_PKG AS
2 /* $Header: IGSEI18B.pls 120.4 2006/04/16 23:49:10 smaddali ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_STDNT_PS_INTM%RowType;
5 new_references IGS_EN_STDNT_PS_INTM%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 ,
10 x_person_id IN NUMBER ,
11 x_course_cd IN VARCHAR2 ,
12 x_start_dt IN DATE ,
13 x_logical_delete_date IN DATE,
14 x_end_dt IN DATE ,
15 x_voluntary_ind IN VARCHAR2 ,
16 x_comments IN VARCHAR2 ,
17 X_INTERMISSION_TYPE in VARCHAR2 ,
18 X_APPROVED in VARCHAR2 ,
19 X_INSTITUTION_NAME in VARCHAR2 ,
20 X_MAX_CREDIT_PTS in NUMBER ,
21 X_MAX_TERMS in NUMBER ,
22 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
23 X_APPROVER_ID in NUMBER ,
24 x_creation_date IN DATE ,
25 x_created_by IN NUMBER ,
26 x_last_update_date IN DATE ,
27 x_last_updated_by IN NUMBER ,
28 x_last_update_login IN NUMBER ,
29 x_attribute_category IN VARCHAR2 ,
30 x_attribute1 IN VARCHAR2 ,
31 x_attribute2 IN VARCHAR2 ,
32 x_attribute3 IN VARCHAR2 ,
33 x_attribute4 IN VARCHAR2 ,
34 x_attribute5 IN VARCHAR2 ,
35 x_attribute6 IN VARCHAR2 ,
36 x_attribute7 IN VARCHAR2 ,
37 x_attribute8 IN VARCHAR2 ,
38 x_attribute9 IN VARCHAR2 ,
39 x_attribute10 IN VARCHAR2 ,
40 x_attribute11 IN VARCHAR2 ,
41 x_attribute12 IN VARCHAR2 ,
42 x_attribute13 IN VARCHAR2 ,
43 x_attribute14 IN VARCHAR2 ,
44 x_attribute15 IN VARCHAR2 ,
45 x_attribute16 IN VARCHAR2 ,
46 x_attribute17 IN VARCHAR2 ,
47 x_attribute18 IN VARCHAR2 ,
48 x_attribute19 IN VARCHAR2 ,
49 x_attribute20 IN VARCHAR2 ,
50 x_COND_RETURN_FLAG IN VARCHAR2
51 ) AS
52
53 CURSOR cur_old_ref_values IS
54 SELECT *
55 FROM IGS_EN_STDNT_PS_INTM
56 WHERE rowid = x_rowid;
57
58 BEGIN
59
60 l_rowid := x_rowid;
61
62 -- Code for setting the Old and New Reference Values.
63 -- Populate Old Values.
64 Open cur_old_ref_values;
65 Fetch cur_old_ref_values INTO old_references;
66 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
67 Close cur_old_ref_values;
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 IGS_GE_MSG_STACK.ADD;
70 App_Exception.Raise_Exception;
71 Return;
72 END IF;
73 Close cur_old_ref_values;
74
75 -- Populate New Values.
76 new_references.person_id := x_person_id;
77 new_references.course_cd := x_course_cd;
78 new_references.start_dt := x_start_dt;
79 new_references.logical_delete_date := x_logical_delete_date;
80 new_references.end_dt := x_end_dt;
81 new_references.voluntary_ind := x_voluntary_ind;
82 new_references.comments := x_comments;
83 NEW_REFERENCES.INTERMISSION_TYPE :=X_INTERMISSION_TYPE ;
84 NEW_REFERENCES.APPROVED := X_APPROVED;
85 NEW_REFERENCES.INSTITUTION_NAME := X_INSTITUTION_NAME;
86 NEW_REFERENCES.MAX_CREDIT_PTS := X_MAX_CREDIT_PTS ;
87 NEW_REFERENCES.MAX_TERMS := X_MAX_TERMS;
88 NEW_REFERENCES.ANTICIPATED_CREDIT_POINTS:= X_ANTICIPATED_CREDIT_POINTS ;
89 NEW_REFERENCES.APPROVER_ID := X_APPROVER_ID;
90 new_references.attribute_category := x_attribute_category;
91 new_references.attribute1 := x_attribute1;
92 new_references.attribute2 := x_attribute2;
93 new_references.attribute3 := x_attribute3;
94 new_references.attribute4 := x_attribute4;
95 new_references.attribute5 := x_attribute5;
96 new_references.attribute6 := x_attribute6;
97 new_references.attribute7 := x_attribute7;
98 new_references.attribute8 := x_attribute8;
99 new_references.attribute9 := x_attribute9;
100 new_references.attribute10 := x_attribute10;
101 new_references.attribute11 := x_attribute11;
102 new_references.attribute12 := x_attribute12;
103 new_references.attribute13 := x_attribute13;
104 new_references.attribute14 := x_attribute14;
105 new_references.attribute15 := x_attribute15;
106 new_references.attribute16 := x_attribute16;
107 new_references.attribute17 := x_attribute17;
108 new_references.attribute18 := x_attribute18;
109 new_references.attribute19 := x_attribute19;
110 new_references.attribute20 := x_attribute20;
111 new_references.COND_RETURN_FLAG := x_COND_RETURN_FLAG;
112
113 IF (p_action = 'UPDATE') THEN
114 new_references.creation_date := old_references.creation_date;
115 new_references.created_by := old_references.created_by;
116 ELSE
117 new_references.creation_date := x_creation_date;
118 new_references.created_by := x_created_by;
119 END IF;
120 new_references.last_update_date := x_last_update_date;
121 new_references.last_updated_by := x_last_updated_by;
122 new_references.last_update_login := x_last_update_login;
123
124 END Set_Column_Values;
125
126 -- Trigger description :-
127 -- TRG_SCI_BR_IUD
128 -- BEFORE INSERT OR UPDATE OR DELETE ON IGS_EN_STDNT_PS_INTM
129 -- REFERENCING
130 -- NEW AS NEW
131 -- OLD AS OLD
132 -- FOR EACH ROW
133
134 PROCEDURE BeforeRowInsertUpdateDelete1(
135 p_inserting IN BOOLEAN ,
136 p_updating IN BOOLEAN ,
137 p_deleting IN BOOLEAN
138 ) AS
139 v_message_name varchar2(30);
140 BEGIN
141 -- If trigger has not been disabled, perform required processing
142 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNT_PS_INTM') THEN
143 IF p_inserting OR p_updating THEN
144 -- Validate that the IGS_PS_COURSE attempt status allows p_inserting/p_updating of
145 -- intermission details.
146 IF IGS_EN_VAL_SCI.enrp_val_sci_alwd (
147 new_references.person_id,
148 new_references.course_cd,
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 -- Validate for start_dt > end_dt
155 IF igs_ad_val_edtl.genp_val_strt_end_dt (
156 new_references.start_dt,
157 new_references.end_dt,
158 v_message_name) = FALSE THEN
159 fnd_message.set_name('IGS',v_message_name);
160 IGS_GE_MSG_STACK.ADD;
161 app_exception.raise_exception;
162 END IF;
163 END IF;
164 END IF;
165
166 END BeforeRowInsertUpdateDelete1;
167
168 -- Trigger description :-
169 -- "OSS_TST".trg_sci_ar_iud
170 -- AFTER INSERT OR DELETE OR UPDATE
171 -- ON IGS_EN_STDNT_PS_INTM
172 -- FOR EACH ROW
173
174 PROCEDURE AfterRowInsertUpdateDelete2(
175 p_inserting IN BOOLEAN ,
176 p_updating IN BOOLEAN ,
177 p_deleting IN BOOLEAN
178 ) AS
179 v_message_name varchar2(30);
180 --v_rowid_saved BOOLEAN := FALSE;
181 BEGIN
182 -- If trigger has not been disabled, perform required processing
183 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNT_PS_INTM') THEN
184 -- Validate for date overlaps.
185 IF p_inserting OR (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
186 NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
187
188 -- Validate IGS_EN_STDNTPSHECSOP date overlaps.
189 IF IGS_EN_VAL_SCI.enrp_val_sci_ovrlp (
190 NEW_REFERENCES.person_id,
191 NEW_REFERENCES.course_cd,
192 NEW_REFERENCES.start_dt,
193 NEW_REFERENCES.end_dt,
194 v_message_name) = FALSE THEN
195 fnd_message.set_name('IGS',v_message_name);
196 IGS_GE_MSG_STACK.ADD;
197 app_exception.raise_exception;
198 END IF;
199 END IF;
200 END IF;
201
202 -- Bug # 2829275 . UK Correspondence.Intermission business event is raised when an intermission record is created or if approved or end_date fields are changed.
203
204 IF( p_inserting OR ( p_updating AND ((new_references.approved <> old_references.approved AND new_references.approved = 'Y')
205 OR (new_references.end_dt <> old_references.end_dt )))) THEN
206
207
208 igs_en_workflow.intermission_event(
209 p_personid => new_references.person_id,
210 p_program_cd => new_references.course_cd,
211 p_intmtype => new_references.intermission_type,
212 p_startdt => new_references.start_dt,
213 p_enddt => new_references.end_dt ,
214 p_inst_name => new_references.institution_name,
215 p_max_cp => new_references.max_credit_pts ,
216 p_max_term => new_references.max_terms ,
217 p_anti_cp => new_references.anticipated_credit_points ,
218 p_approver => new_references.approver_id
219 );
220 END IF ;
221
222 END AfterRowInsertUpdateDelete2;
223
224 PROCEDURE AfterRowInsertUpdateDelete3(
225 p_inserting IN BOOLEAN ,
226 p_updating IN BOOLEAN ,
227 p_deleting IN BOOLEAN
228 ) AS
229 CURSOR c_spi_rconds ( cp_person_id igs_en_spi_rconds.person_id%TYPE,
230 cp_course_cd igs_en_spi_rconds.course_cd%TYPE,
231 cp_start_dt igs_en_spi_rconds.start_dt%TYPE,
232 cp_logical_delete_date igs_en_spi_rconds.logical_delete_date%TYPE) IS
233 SELECT rowid, rc.*
234 FROM igs_en_spi_rconds rc
235 WHERE rc.person_id = cp_person_id
236 AND rc.course_cd = cp_course_cd
237 AND rc.start_dt = cp_start_dt
238 AND rc.logical_delete_date = cp_logical_delete_date;
239
240 BEGIN
241
242 IF p_updating THEN
243 IF old_references.logical_delete_date <> new_references.logical_delete_date THEN
244 -- when the intermission record is being logically deleted, all the child return conditions
245 -- should also be logically deleted.
246 FOR c_spi_rconds_rec IN c_spi_rconds(old_references.person_id, old_references.course_cd,
247 old_references.start_dt, old_references.logical_delete_date) LOOP
248 igs_en_spi_rconds_pkg.update_row( x_rowid => c_spi_rconds_rec.rowid,
249 x_person_id => c_spi_rconds_rec.person_id,
250 x_course_cd => c_spi_rconds_rec.course_cd,
251 x_start_dt => c_spi_rconds_rec.start_dt,
252 x_logical_delete_date => new_references.logical_delete_date,
253 x_return_condition => c_spi_rconds_rec.return_condition,
254 x_status_code => c_spi_rconds_rec.status_code,
255 x_approved_dt => c_spi_rconds_rec.approved_dt,
256 x_approved_by => c_spi_rconds_rec.approved_by,
257 x_mode => 'R' ) ;
258
259 END LOOP;
260
261 END IF;
262 END IF;
263
264 END AfterRowInsertUpdateDelete3;
265
266 procedure Check_constraints(
267 column_name IN VARCHAR2 ,
268 column_value IN VARCHAR2
269 ) AS
270 begin
271 IF column_name is NULL then
272 NULL;
273 ELSIF upper(column_name) = 'VOLUNTARY_IND' then
274 new_references.voluntary_ind := column_value;
275 ELSIF upper(column_name) = 'COURSE_CD' then
276 new_references.course_cd := column_value;
277 ELSIF upper(column_name) = 'APPROVED' then
278 new_references.approved := column_value;
279 END IF;
280
281 IF upper(column_name) = 'VOLUNTARY_IND' OR
282 column_name is null then
283 if new_references.voluntary_ind not IN ('Y','N') OR
284 new_references.voluntary_ind <> upper(new_references.voluntary_ind) then
285 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
286 IGS_GE_MSG_STACK.ADD;
287 App_Exception.Raise_Exception;
288 end if;
289 end if;
290
291 IF upper(column_name) = 'COND_RETURN_FLAG' OR
292 column_name is null THEN
293 IF new_references.COND_RETURN_FLAG NOT IN ('Y','N')
294 AND new_references.COND_RETURN_FLAG IS NOT NULL THEN
295 --
296 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF;
300 END IF;
301
302 IF upper(column_name) = 'APPROVED' OR
303 column_name is null then
304 if new_references.approved NOT IN ('Y','N') THEN
305 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
306 IGS_GE_MSG_STACK.ADD;
307 App_Exception.Raise_Exception;
308 end if;
309 end if;
310
311 IF upper(column_name) = 'COURSE_CD' OR
312 column_name is null then
313 if new_references.course_cd <> upper(new_references.course_cd) then
314 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
315 IGS_GE_MSG_STACK.ADD;
316 App_Exception.Raise_Exception;
317 end if;
318 end if;
319 END check_constraints;
320
321 PROCEDURE Check_Parent_Existance AS
322
323 CURSOR cur_rowid_HP IS
324 SELECT rowid
325 FROM HZ_PARTIES hp
326 WHERE hp.party_id = NEW_REFERENCES.APPROVER_ID;
327
328 CURSOR cur_exists_ioie IS
329 SELECT 'X'
330 FROM hz_parties hp, igs_pe_hz_parties ihp
331 WHERE hp.party_id = ihp.party_id and ihp.inst_org_ind = 'I' AND
332 ihp.oi_govt_institution_cd IS NOT NULL
333 AND IHP.OSS_ORG_UNIT_CD = NEW_REFERENCES.INSTITUTION_NAME
334 UNION ALL
335 SELECT 'X'
336 FROM igs_lookup_values lk
337 WHERE lk.lookup_type = 'OR_INST_EXEMPTIONS' AND
338 lk.enabled_flag = 'Y' AND
339 lookup_code = NEW_REFERENCES.INSTITUTION_NAME ;
340
341 l_exists VARCHAR2(1);
342
343 BEGIN
344
345 IF (((old_references.person_id = new_references.person_id) AND
346 (old_references.course_cd = new_references.course_cd)) OR
347 ((new_references.person_id IS NULL) OR
348 (new_references.course_cd IS NULL))) THEN
349 NULL;
350 ELSE
351 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
352 new_references.person_id,
353 new_references.course_cd
354 ) Then
355 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
356 IGS_GE_MSG_STACK.ADD;
357 App_Exception.Raise_Exception;
358 end if;
359 END IF;
360
361 IF ((old_references.intermission_type = new_references.intermission_type) OR
362 (new_references.intermission_type IS NULL)) THEN
363 NULL;
364 ELSE
365 IF NOT IGS_EN_INTM_TYPES_PKG.Get_UK_For_Validation (
366 new_references.intermission_type
367 ) Then
368 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 end if;
372 END IF;
373
374 IF ((old_references.APPROVER_ID = new_references.APPROVER_ID) OR
375 (new_references.APPROVER_ID IS NULL)) THEN
376 NULL;
377 ELSE
378 Open cur_rowid_HP;
379 Fetch cur_rowid_HP INTO l_rowid;
380 IF (cur_rowid_HP%FOUND) THEN
381 Close cur_rowid_HP;
382 ELSE
383 Close cur_rowid_HP;
384 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
385 IGS_GE_MSG_STACK.ADD;
386 App_Exception.Raise_Exception;
387 END IF;
388 END IF;
389
390 IF ((old_references.INSTITUTION_NAME = new_references.INSTITUTION_NAME) OR
391 (new_references.INSTITUTION_NAME IS NULL)) THEN
392 NULL;
393 ELSE
394 Open cur_exists_ioie;
395 Fetch cur_exists_ioie INTO l_exists;
396 IF (cur_exists_ioie%FOUND) THEN
397 Close cur_exists_ioie;
398 ELSE
399 Close cur_exists_ioie;
400 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
401 IGS_GE_MSG_STACK.ADD;
402 App_Exception.Raise_Exception;
403 END IF;
404 END IF;
405
406 END Check_Parent_Existance;
407
408 FUNCTION Get_PK_For_Validation (
409 x_person_id IN NUMBER,
410 x_course_cd IN VARCHAR2,
411 x_start_dt IN DATE,
412 x_logical_delete_date IN DATE
413 )RETURN BOOLEAN AS
414
415 CURSOR cur_rowid IS
416 SELECT rowid
417 FROM IGS_EN_STDNT_PS_INTM
418 WHERE person_id = x_person_id
419 AND course_cd = x_course_cd
420 AND start_dt = x_start_dt
421 AND logical_delete_date = x_logical_delete_date
422 FOR UPDATE NOWAIT;
423
424 lv_rowid cur_rowid%RowType;
425
426 BEGIN
427
428 Open cur_rowid;
429 Fetch cur_rowid INTO lv_rowid;
430 IF (cur_rowid%FOUND) THEN
431 Close cur_rowid;
432 return(TRUE);
433 else
434 Close cur_rowid;
435 Return(FALSE);
436 END IF;
437
438 END Get_PK_For_Validation;
439
440
441 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
442 x_person_id IN NUMBER,
443 x_course_cd IN VARCHAR2
444 ) AS
445
446 CURSOR cur_rowid IS
447 SELECT rowid
448 FROM IGS_EN_STDNT_PS_INTM
449 WHERE person_id = x_person_id
450 AND course_cd = x_course_cd ;
451
452 lv_rowid cur_rowid%RowType;
453
454 BEGIN
455
456 Open cur_rowid;
457 Fetch cur_rowid INTO lv_rowid;
458 IF (cur_rowid%FOUND) THEN
459 Close cur_rowid;
460 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCI_SCA_FK');
461 IGS_GE_MSG_STACK.ADD;
462 Return;
463 END IF;
464 Close cur_rowid;
465
466 END GET_FK_IGS_EN_STDNT_PS_ATT;
467
468 PROCEDURE Check_Child_Existance AS
469 /*
470 || Created By : Susmitha Tutta
471 || Created On : 20-Mar-2006
472 || Purpose : Checking for child existance
473 || Known limitations, enhancements or remarks :
474 || Change History :
475 || Who When What
476 || (reverse chronological order - newest change first)
477 */
478 BEGIN
479 IGS_EN_SPI_RCONDS_PKG.get_fk_igs_en_stdnt_ps_intm(
480 old_references.person_id,
481 old_references.course_cd,
482 old_references.start_dt,
483 old_references.logical_delete_date
484 );
485 END Check_Child_Existance;
486
487
488 PROCEDURE Before_DML (
489 p_action IN VARCHAR2,
490 x_rowid IN VARCHAR2 ,
491 x_person_id IN NUMBER ,
492 x_course_cd IN VARCHAR2 ,
493 x_start_dt IN DATE ,
494 x_logical_delete_date IN DATE,
495 x_end_dt IN DATE ,
496 x_voluntary_ind IN VARCHAR2 ,
497 x_comments IN VARCHAR2 ,
498 X_INTERMISSION_TYPE in VARCHAR2 ,
499 X_APPROVED in VARCHAR2 ,
500 X_INSTITUTION_NAME in VARCHAR2 ,
501 X_MAX_CREDIT_PTS in NUMBER ,
502 X_MAX_TERMS in NUMBER ,
503 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
504 X_APPROVER_ID in NUMBER ,
505 x_creation_date IN DATE ,
506 x_created_by IN NUMBER ,
507 x_last_update_date IN DATE ,
508 x_last_updated_by IN NUMBER ,
509 x_last_update_login IN NUMBER ,
510 x_attribute_category IN VARCHAR2 ,
511 x_attribute1 IN VARCHAR2 ,
512 x_attribute2 IN VARCHAR2 ,
513 x_attribute3 IN VARCHAR2 ,
514 x_attribute4 IN VARCHAR2 ,
515 x_attribute5 IN VARCHAR2 ,
516 x_attribute6 IN VARCHAR2 ,
517 x_attribute7 IN VARCHAR2 ,
518 x_attribute8 IN VARCHAR2 ,
519 x_attribute9 IN VARCHAR2 ,
520 x_attribute10 IN VARCHAR2 ,
521 x_attribute11 IN VARCHAR2 ,
522 x_attribute12 IN VARCHAR2 ,
523 x_attribute13 IN VARCHAR2 ,
524 x_attribute14 IN VARCHAR2 ,
525 x_attribute15 IN VARCHAR2 ,
526 x_attribute16 IN VARCHAR2 ,
527 x_attribute17 IN VARCHAR2 ,
528 x_attribute18 IN VARCHAR2 ,
529 x_attribute19 IN VARCHAR2 ,
530 x_attribute20 IN VARCHAR2 ,
531 x_COND_RETURN_FLAG IN VARCHAR2
532 ) AS
533 BEGIN
534
535 Set_Column_Values (
536 p_action,
537 x_rowid,
538 x_person_id,
539 x_course_cd,
540 x_start_dt,
541 x_logical_delete_date,
542 x_end_dt,
543 x_voluntary_ind,
544 x_comments,
545 X_INTERMISSION_TYPE,
546 X_APPROVED,
547 X_INSTITUTION_NAME,
548 X_MAX_CREDIT_PTS ,
549 X_MAX_TERMS,
550 X_ANTICIPATED_CREDIT_POINTS,
551 X_APPROVER_ID,
552 x_creation_date,
553 x_created_by,
554 x_last_update_date,
555 x_last_updated_by,
556 x_last_update_login,
557 X_ATTRIBUTE_CATEGORY,
558 X_ATTRIBUTE1,
559 X_ATTRIBUTE2,
560 X_ATTRIBUTE3,
561 X_ATTRIBUTE4,
562 X_ATTRIBUTE5,
563 X_ATTRIBUTE6,
564 X_ATTRIBUTE7,
565 X_ATTRIBUTE8,
566 X_ATTRIBUTE9,
567 X_ATTRIBUTE10,
568 X_ATTRIBUTE11,
569 X_ATTRIBUTE12,
570 X_ATTRIBUTE13,
571 X_ATTRIBUTE14,
572 X_ATTRIBUTE15,
573 X_ATTRIBUTE16,
574 X_ATTRIBUTE17,
575 X_ATTRIBUTE18,
576 X_ATTRIBUTE19,
577 X_ATTRIBUTE20,
578 x_COND_RETURN_FLAG
579 );
580
581 IF (p_action = 'INSERT') THEN
582 -- Call all the procedures related to Before Insert.
583 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE,
584 p_updating => FALSE,
585 p_deleting => FALSE);
586
587 IF Get_PK_For_Validation (
588 new_references.person_id ,
589 new_references.course_cd ,
590 new_references.start_dt,
591 new_references.logical_delete_date
592 ) THEN
593 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
594 IGS_GE_MSG_STACK.ADD;
595 App_Exception.Raise_Exception;
596 end if;
597 Check_constraints;
598 Check_Parent_Existance;
599 ELSIF (p_action = 'UPDATE') THEN
600 -- Call all the procedures related to Before Update.
601 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
602 p_updating => TRUE,
603 p_deleting => FALSE
604 );
605 Check_constraints;
606 Check_Parent_Existance;
607 ELSIF (p_action = 'DELETE') THEN
608 check_child_existance;
609 -- Call all the procedures related to Before Delete.
610 BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE,
611 p_updating => FALSE,
612 p_deleting => TRUE
613 );
614 ELSIF (p_action = 'VALIDATE_INSERT') then
615 IF Get_PK_For_Validation (
616 new_references.person_id,
617 new_references.course_cd,
618 new_references.start_dt,
619 new_references.logical_delete_date
620 ) THEN
621 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
622 IGS_GE_MSG_STACK.ADD;
623 App_Exception.Raise_Exception;
624 end if;
625 Check_constraints;
626 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
627 Check_constraints;
628 ELSIF (p_action = 'VALIDATE_DELETE') THEN
629 check_child_existance;
630 END IF;
631
632 END Before_DML;
633
634 PROCEDURE After_DML (
635 p_action IN VARCHAR2,
636 x_rowid IN VARCHAR2
637 ) AS
638 BEGIN
639
640 l_rowid := x_rowid;
641
642 IF (p_action = 'INSERT') THEN
643 -- Call all the procedures related to After Insert.
644 AfterRowInsertUpdateDelete2 ( p_inserting => TRUE,
645 p_updating => FALSE,
646 p_deleting => FALSE
647 );
648
649 -- AfterStmtInsertUpdateDelete3 ( p_inserting => TRUE );
650 ELSIF (p_action = 'UPDATE') THEN
651
652 -- Call all the procedures related to After Update.
653 AfterRowInsertUpdateDelete2 ( p_inserting => FALSE,
654 p_updating => TRUE,
655 p_deleting => FALSE
656 );
657
658 AfterRowInsertUpdateDelete3 ( p_inserting => FALSE,
659 p_updating => TRUE,
660 p_deleting => FALSE
661 );
662
663
664 -- AfterStmtInsertUpdateDelete3 ( p_updating => TRUE );
665 ELSIF (p_action = 'DELETE') THEN
666 -- Call all the procedures related to After Delete.
667 AfterRowInsertUpdateDelete2 ( p_inserting => FALSE,
668 p_updating => FALSE,
669 p_deleting => TRUE
670 );
671 -- AfterStmtInsertUpdateDelete3 ( p_deleting => TRUE );
672 END IF;
673
674 END After_DML;
675
676
677 procedure INSERT_ROW (
678 X_ROWID in out NOCOPY VARCHAR2,
679 X_PERSON_ID in NUMBER,
680 X_COURSE_CD in VARCHAR2,
681 X_START_DT in DATE,
682 X_LOGICAL_DELETE_DATE in DATE,
683 X_END_DT in DATE,
684 X_VOLUNTARY_IND in VARCHAR2,
685 X_COMMENTS in VARCHAR2,
686 X_INTERMISSION_TYPE in VARCHAR2 ,
687 X_APPROVED in VARCHAR2 ,
688 X_INSTITUTION_NAME in VARCHAR2 ,
689 X_MAX_CREDIT_PTS in NUMBER ,
690 X_MAX_TERMS in NUMBER ,
691 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
692 X_APPROVER_ID in NUMBER ,
693 X_MODE in VARCHAR2 ,
694 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
695 X_ATTRIBUTE1 IN VARCHAR2 ,
696 X_ATTRIBUTE2 IN VARCHAR2 ,
697 X_ATTRIBUTE3 IN VARCHAR2 ,
698 X_ATTRIBUTE4 IN VARCHAR2 ,
699 X_ATTRIBUTE5 IN VARCHAR2 ,
700 X_ATTRIBUTE6 IN VARCHAR2 ,
701 X_ATTRIBUTE7 IN VARCHAR2 ,
702 X_ATTRIBUTE8 IN VARCHAR2 ,
703 X_ATTRIBUTE9 IN VARCHAR2 ,
704 X_ATTRIBUTE10 IN VARCHAR2 ,
705 X_ATTRIBUTE11 IN VARCHAR2 ,
706 X_ATTRIBUTE12 IN VARCHAR2 ,
707 X_ATTRIBUTE13 IN VARCHAR2 ,
708 X_ATTRIBUTE14 IN VARCHAR2 ,
709 X_ATTRIBUTE15 IN VARCHAR2 ,
710 X_ATTRIBUTE16 IN VARCHAR2 ,
711 X_ATTRIBUTE17 IN VARCHAR2 ,
712 X_ATTRIBUTE18 IN VARCHAR2 ,
713 X_ATTRIBUTE19 IN VARCHAR2 ,
714 X_ATTRIBUTE20 IN VARCHAR2 ,
715 x_COND_RETURN_FLAG IN VARCHAR2
716 ) AS
717 cursor C is select ROWID from IGS_EN_STDNT_PS_INTM
718 where PERSON_ID = X_PERSON_ID
719 and COURSE_CD = X_COURSE_CD
720 and START_DT = X_START_DT
721 and LOGICAL_DELETE_DATE = X_LOGICAL_DELETE_DATE;
722 X_LAST_UPDATE_DATE DATE;
723 X_LAST_UPDATED_BY NUMBER;
724 X_LAST_UPDATE_LOGIN NUMBER;
725 begin
726 X_LAST_UPDATE_DATE := SYSDATE;
727 if(X_MODE = 'I') then
728 X_LAST_UPDATED_BY := 1;
729 X_LAST_UPDATE_LOGIN := 0;
730 elsif (X_MODE IN ('R', 'S')) then
731 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
732 if X_LAST_UPDATED_BY is NULL then
733 X_LAST_UPDATED_BY := -1;
734 end if;
735 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
736 if X_LAST_UPDATE_LOGIN is NULL then
737 X_LAST_UPDATE_LOGIN := -1;
738 end if;
739 else
740 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
741 IGS_GE_MSG_STACK.ADD;
742 app_exception.raise_exception;
743 end if;
744
745 Before_DML(
746 p_action => 'INSERT',
747 x_rowid => X_ROWID,
748 x_person_id => X_PERSON_ID,
749 x_course_cd => X_COURSE_CD,
750 x_start_dt => X_START_DT,
751 x_logical_delete_date => X_LOGICAL_DELETE_DATE,
752 x_end_dt => X_END_DT,
753 x_voluntary_ind => X_VOLUNTARY_IND,
754 x_comments => X_COMMENTS,
755 X_INTERMISSION_TYPE =>X_INTERMISSION_TYPE,
756 X_APPROVED =>X_APPROVED,
757 X_INSTITUTION_NAME => X_INSTITUTION_NAME,
758 X_MAX_CREDIT_PTS => X_MAX_CREDIT_PTS,
759 X_MAX_TERMS => X_MAX_TERMS,
760 X_ANTICIPATED_CREDIT_POINTS => X_ANTICIPATED_CREDIT_POINTS,
761 X_APPROVER_ID => X_APPROVER_ID,
762 x_creation_date => X_LAST_UPDATE_DATE,
763 x_created_by => X_LAST_UPDATED_BY,
764 x_last_update_date =>X_LAST_UPDATE_DATE,
765 x_last_updated_by => X_LAST_UPDATED_BY,
766 x_last_update_login => X_LAST_UPDATE_LOGIN,
767 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
768 x_attribute1=>X_ATTRIBUTE1,
769 x_attribute2=>X_ATTRIBUTE2,
770 x_attribute3=>X_ATTRIBUTE3,
771 x_attribute4=>X_ATTRIBUTE4,
772 x_attribute5=>X_ATTRIBUTE5,
773 x_attribute6=>X_ATTRIBUTE6,
774 x_attribute7=>X_ATTRIBUTE7,
775 x_attribute8=>X_ATTRIBUTE8,
776 x_attribute9=>X_ATTRIBUTE9,
777 x_attribute10=>X_ATTRIBUTE10,
778 x_attribute11=>X_ATTRIBUTE11,
779 x_attribute12=>X_ATTRIBUTE12,
780 x_attribute13=>X_ATTRIBUTE13,
781 x_attribute14=>X_ATTRIBUTE14,
782 x_attribute15=>X_ATTRIBUTE15,
783 x_attribute16=>X_ATTRIBUTE16,
784 x_attribute17=>X_ATTRIBUTE17,
785 x_attribute18=>X_ATTRIBUTE18,
786 x_attribute19=>X_ATTRIBUTE19,
787 x_attribute20=>X_ATTRIBUTE20,
788 x_COND_RETURN_FLAG => x_COND_RETURN_FLAG
789 );
790
791 IF (x_mode = 'S') THEN
792 igs_sc_gen_001.set_ctx('R');
793 END IF;
794 insert into IGS_EN_STDNT_PS_INTM (
795 PERSON_ID,
796 COURSE_CD,
797 START_DT,
798 LOGICAL_DELETE_DATE,
799 END_DT,
800 VOLUNTARY_IND,
801 COMMENTS,
802 INTERMISSION_TYPE ,
803 APPROVED ,
804 INSTITUTION_NAME,
805 MAX_CREDIT_PTS ,
806 MAX_TERMS ,
807 ANTICIPATED_CREDIT_POINTS ,
808 APPROVER_ID,
809 CREATION_DATE,
810 CREATED_BY,
811 LAST_UPDATE_DATE,
812 LAST_UPDATED_BY,
813 LAST_UPDATE_LOGIN,
814 ATTRIBUTE_CATEGORY,
815 ATTRIBUTE1,
816 ATTRIBUTE2,
817 ATTRIBUTE3,
818 ATTRIBUTE4,
819 ATTRIBUTE5,
820 ATTRIBUTE6,
821 ATTRIBUTE7,
822 ATTRIBUTE8,
823 ATTRIBUTE9,
824 ATTRIBUTE10,
825 ATTRIBUTE11,
826 ATTRIBUTE12,
827 ATTRIBUTE13,
828 ATTRIBUTE14,
829 ATTRIBUTE15,
830 ATTRIBUTE16,
831 ATTRIBUTE17,
832 ATTRIBUTE18,
833 ATTRIBUTE19,
834 ATTRIBUTE20,
835 COND_RETURN_FLAG
836 ) values (
837 NEW_REFERENCES.PERSON_ID,
838 NEW_REFERENCES.COURSE_CD,
839 NEW_REFERENCES.START_DT,
840 NEW_REFERENCES.LOGICAL_DELETE_DATE,
841 NEW_REFERENCES.END_DT,
842 NEW_REFERENCES.VOLUNTARY_IND,
843 NEW_REFERENCES.COMMENTS,
844 NEW_REFERENCES.INTERMISSION_TYPE ,
845 NEW_REFERENCES.APPROVED ,
846 NEW_REFERENCES.INSTITUTION_NAME,
847 NEW_REFERENCES.MAX_CREDIT_PTS ,
848 NEW_REFERENCES.MAX_TERMS ,
849 NEW_REFERENCES.ANTICIPATED_CREDIT_POINTS ,
850 NEW_REFERENCES.APPROVER_ID,
851 X_LAST_UPDATE_DATE,
852 X_LAST_UPDATED_BY,
853 X_LAST_UPDATE_DATE,
854 X_LAST_UPDATED_BY,
855 X_LAST_UPDATE_LOGIN,
856 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
857 NEW_REFERENCES.ATTRIBUTE1,
858 NEW_REFERENCES.ATTRIBUTE2,
859 NEW_REFERENCES.ATTRIBUTE3,
860 NEW_REFERENCES.ATTRIBUTE4,
861 NEW_REFERENCES.ATTRIBUTE5,
862 NEW_REFERENCES.ATTRIBUTE6,
863 NEW_REFERENCES.ATTRIBUTE7,
864 NEW_REFERENCES.ATTRIBUTE8,
865 NEW_REFERENCES.ATTRIBUTE9,
866 NEW_REFERENCES.ATTRIBUTE10,
867 NEW_REFERENCES.ATTRIBUTE11,
868 NEW_REFERENCES.ATTRIBUTE12,
869 NEW_REFERENCES.ATTRIBUTE13,
870 NEW_REFERENCES.ATTRIBUTE14,
871 NEW_REFERENCES.ATTRIBUTE15,
872 NEW_REFERENCES.ATTRIBUTE16,
873 NEW_REFERENCES.ATTRIBUTE17,
874 NEW_REFERENCES.ATTRIBUTE18,
875 NEW_REFERENCES.ATTRIBUTE19,
876 NEW_REFERENCES.ATTRIBUTE20,
877 NEW_REFERENCES.COND_RETURN_FLAG
878 );
879 IF (x_mode = 'S') THEN
880 igs_sc_gen_001.unset_ctx('R');
881 END IF;
882
883
884 open c;
885 fetch c into X_ROWID;
886 if (c%notfound) then
887 close c;
888 raise no_data_found;
889 end if;
890 close c;
891
892 After_DML(
893 p_action => 'INSERT',
894 x_rowid => X_ROWID
895 );
896
897 EXCEPTION
898 WHEN OTHERS THEN
899 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
900 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
901 fnd_message.set_token ('ERR_CD', SQLCODE);
902 igs_ge_msg_stack.add;
903 igs_sc_gen_001.unset_ctx('R');
904 app_exception.raise_exception;
905 ELSE
906 igs_sc_gen_001.unset_ctx('R');
907 RAISE;
908 END IF;
909
910 end INSERT_ROW;
911
912 procedure LOCK_ROW (
913 X_ROWID in VARCHAR2,
914 X_PERSON_ID in NUMBER,
915 X_COURSE_CD in VARCHAR2,
916 X_START_DT in DATE,
917 X_LOGICAL_DELETE_DATE in DATE,
918 X_END_DT in DATE,
919 X_VOLUNTARY_IND in VARCHAR2,
920 X_COMMENTS in VARCHAR2,
921 X_INTERMISSION_TYPE in VARCHAR2 ,
922 X_APPROVED in VARCHAR2 ,
923 X_INSTITUTION_NAME in VARCHAR2 ,
924 X_MAX_CREDIT_PTS in NUMBER ,
925 X_MAX_TERMS in NUMBER ,
926 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
927 X_APPROVER_ID in NUMBER ,
928 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
929 X_ATTRIBUTE1 IN VARCHAR2 ,
930 X_ATTRIBUTE2 IN VARCHAR2 ,
931 X_ATTRIBUTE3 IN VARCHAR2 ,
932 X_ATTRIBUTE4 IN VARCHAR2 ,
933 X_ATTRIBUTE5 IN VARCHAR2 ,
934 X_ATTRIBUTE6 IN VARCHAR2 ,
935 X_ATTRIBUTE7 IN VARCHAR2 ,
936 X_ATTRIBUTE8 IN VARCHAR2 ,
937 X_ATTRIBUTE9 IN VARCHAR2 ,
938 X_ATTRIBUTE10 IN VARCHAR2 ,
939 X_ATTRIBUTE11 IN VARCHAR2 ,
940 X_ATTRIBUTE12 IN VARCHAR2 ,
941 X_ATTRIBUTE13 IN VARCHAR2 ,
942 X_ATTRIBUTE14 IN VARCHAR2 ,
943 X_ATTRIBUTE15 IN VARCHAR2 ,
944 X_ATTRIBUTE16 IN VARCHAR2 ,
945 X_ATTRIBUTE17 IN VARCHAR2 ,
946 X_ATTRIBUTE18 IN VARCHAR2 ,
947 X_ATTRIBUTE19 IN VARCHAR2 ,
948 X_ATTRIBUTE20 IN VARCHAR2 ,
949 X_COND_RETURN_FLAG IN VARCHAR2
950 )AS
951 cursor c1 is select
952 LOGICAL_DELETE_DATE,
953 END_DT,
954 VOLUNTARY_IND,
955 COMMENTS,
956 INTERMISSION_TYPE,
957 APPROVED,
958 INSTITUTION_NAME,
959 MAX_CREDIT_PTS,
960 MAX_TERMS,
961 ANTICIPATED_CREDIT_POINTS,
962 APPROVER_ID,
963 ATTRIBUTE_CATEGORY,
964 ATTRIBUTE1,
965 ATTRIBUTE2,
966 ATTRIBUTE3,
967 ATTRIBUTE4,
968 ATTRIBUTE5,
969 ATTRIBUTE6,
970 ATTRIBUTE7,
971 ATTRIBUTE8,
972 ATTRIBUTE9,
973 ATTRIBUTE10,
974 ATTRIBUTE11,
975 ATTRIBUTE12,
976 ATTRIBUTE13,
977 ATTRIBUTE14,
978 ATTRIBUTE15,
979 ATTRIBUTE16,
980 ATTRIBUTE17,
981 ATTRIBUTE18,
982 ATTRIBUTE19,
983 ATTRIBUTE20,
984 COND_RETURN_FLAG
985 from IGS_EN_STDNT_PS_INTM
986 where ROWID = X_ROWID
987 for update nowait;
988 tlinfo c1%rowtype;
989
990 begin
991 open c1;
992 fetch c1 into tlinfo;
993 if (c1%notfound) then
994 close c1;
995 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
996 IGS_GE_MSG_STACK.ADD;
997 app_exception.raise_exception;
998 return;
999 end if;
1000 close c1;
1001
1002 if ( (tlinfo.LOGICAL_DELETE_DATE = X_LOGICAL_DELETE_DATE)
1003 AND (tlinfo.END_DT = X_END_DT)
1004 AND (tlinfo.VOLUNTARY_IND = X_VOLUNTARY_IND)
1005 AND ((tlinfo.COMMENTS = X_COMMENTS)
1006 OR ((tlinfo.COMMENTS is null)
1007 AND (X_COMMENTS is null)))
1008 AND ((tlinfo.INTERMISSION_TYPE = X_INTERMISSION_TYPE)
1009 OR ((tlinfo.INTERMISSION_TYPE is null)
1010 AND (X_INTERMISSION_TYPE is null)))
1011 AND ((tlinfo.APPROVED = X_APPROVED)
1012 OR ((tlinfo.APPROVED is null)
1013 AND (X_APPROVED is null)))
1014 AND ((tlinfo.INSTITUTION_NAME = X_INSTITUTION_NAME)
1015 OR ((tlinfo.INSTITUTION_NAME is null)
1016 AND (X_INSTITUTION_NAME is null)))
1017 AND ((tlinfo.MAX_CREDIT_PTS = X_MAX_CREDIT_PTS)
1018 OR ((tlinfo.MAX_CREDIT_PTS is null)
1019 AND (X_MAX_CREDIT_PTS is null)))
1020 AND ((tlinfo.MAX_TERMS = X_MAX_TERMS)
1021 OR ((tlinfo.MAX_TERMS is null)
1022 AND (X_MAX_TERMS is null)))
1023 AND ((tlinfo.ANTICIPATED_CREDIT_POINTS = X_ANTICIPATED_CREDIT_POINTS)
1024 OR ((tlinfo.ANTICIPATED_CREDIT_POINTS is null)
1025 AND (X_ANTICIPATED_CREDIT_POINTS is null)))
1026 AND ((tlinfo.APPROVER_ID = X_APPROVER_ID)
1027 OR ((tlinfo.APPROVER_ID is null)
1028 AND (X_APPROVER_ID is null)))
1029 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
1030 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
1031 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
1032 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
1033 OR ((tlinfo.ATTRIBUTE1 IS NULL)
1034 AND (X_ATTRIBUTE1 IS NULL)))
1035 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
1036 OR ((tlinfo.ATTRIBUTE2 IS NULL)
1037 AND (X_ATTRIBUTE2 IS NULL)))
1038 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
1039 OR ((tlinfo.ATTRIBUTE3 IS NULL)
1040 AND (X_ATTRIBUTE3 IS NULL)))
1041 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
1042 OR ((tlinfo.ATTRIBUTE4 IS NULL)
1043 AND (X_ATTRIBUTE4 IS NULL)))
1044 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
1045 OR ((tlinfo.ATTRIBUTE5 IS NULL)
1046 AND (X_ATTRIBUTE5 IS NULL)))
1047 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
1048 OR ((tlinfo.ATTRIBUTE6 IS NULL)
1049 AND (X_ATTRIBUTE6 IS NULL)))
1050 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
1051 OR ((tlinfo.ATTRIBUTE7 IS NULL)
1052 AND (X_ATTRIBUTE7 IS NULL)))
1053 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
1054 OR ((tlinfo.ATTRIBUTE8 IS NULL)
1055 AND (X_ATTRIBUTE8 IS NULL)))
1056 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
1057 OR ((tlinfo.ATTRIBUTE9 IS NULL)
1058 AND (X_ATTRIBUTE9 IS NULL)))
1059 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
1060 OR ((tlinfo.ATTRIBUTE10 IS NULL)
1061 AND (X_ATTRIBUTE10 IS NULL)))
1062 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
1063 OR ((tlinfo.ATTRIBUTE11 IS NULL)
1064 AND (X_ATTRIBUTE11 IS NULL)))
1065 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
1066 OR ((tlinfo.ATTRIBUTE12 IS NULL)
1067 AND (X_ATTRIBUTE12 IS NULL)))
1068 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
1069 OR ((tlinfo.ATTRIBUTE13 IS NULL)
1070 AND (X_ATTRIBUTE13 IS NULL)))
1071 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
1072 OR ((tlinfo.ATTRIBUTE14 IS NULL)
1073 AND (X_ATTRIBUTE14 IS NULL)))
1074 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
1075 OR ((tlinfo.ATTRIBUTE15 IS NULL)
1076 AND (X_ATTRIBUTE15 IS NULL)))
1077 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
1078 OR ((tlinfo.ATTRIBUTE16 IS NULL)
1079 AND (X_ATTRIBUTE16 IS NULL)))
1080 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
1081 OR ((tlinfo.ATTRIBUTE17 IS NULL)
1082 AND (X_ATTRIBUTE17 IS NULL)))
1083 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
1084 OR ((tlinfo.ATTRIBUTE18 IS NULL)
1085 AND (X_ATTRIBUTE18 IS NULL)))
1086 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
1087 OR ((tlinfo.ATTRIBUTE19 IS NULL)
1088 AND (X_ATTRIBUTE19 IS NULL)))
1089 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
1090 OR ((tlinfo.ATTRIBUTE20 IS NULL)
1091 AND (X_ATTRIBUTE20 IS NULL)))
1092 AND (tlinfo.COND_RETURN_FLAG = X_COND_RETURN_FLAG)
1093 ) then
1094 null;
1095 else
1096 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1097 IGS_GE_MSG_STACK.ADD;
1098 app_exception.raise_exception;
1099 end if;
1100 return;
1101 end LOCK_ROW;
1102
1103 procedure UPDATE_ROW (
1104 X_ROWID in VARCHAR2,
1105 X_PERSON_ID in NUMBER,
1106 X_COURSE_CD in VARCHAR2,
1107 X_START_DT in DATE,
1108 X_LOGICAL_DELETE_DATE in DATE,
1109 X_END_DT in DATE,
1110 X_VOLUNTARY_IND in VARCHAR2,
1111 X_COMMENTS in VARCHAR2,
1112 X_INTERMISSION_TYPE in VARCHAR2 ,
1113 X_APPROVED in VARCHAR2 ,
1114 X_INSTITUTION_NAME in VARCHAR2 ,
1115 X_MAX_CREDIT_PTS in NUMBER ,
1116 X_MAX_TERMS in NUMBER ,
1117 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
1118 X_APPROVER_ID in NUMBER ,
1119 X_MODE in VARCHAR2 ,
1120 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1121 X_ATTRIBUTE1 IN VARCHAR2 ,
1122 X_ATTRIBUTE2 IN VARCHAR2 ,
1123 X_ATTRIBUTE3 IN VARCHAR2 ,
1124 X_ATTRIBUTE4 IN VARCHAR2 ,
1125 X_ATTRIBUTE5 IN VARCHAR2 ,
1126 X_ATTRIBUTE6 IN VARCHAR2 ,
1127 X_ATTRIBUTE7 IN VARCHAR2 ,
1128 X_ATTRIBUTE8 IN VARCHAR2 ,
1129 X_ATTRIBUTE9 IN VARCHAR2 ,
1130 X_ATTRIBUTE10 IN VARCHAR2 ,
1131 X_ATTRIBUTE11 IN VARCHAR2 ,
1132 X_ATTRIBUTE12 IN VARCHAR2 ,
1133 X_ATTRIBUTE13 IN VARCHAR2 ,
1134 X_ATTRIBUTE14 IN VARCHAR2 ,
1135 X_ATTRIBUTE15 IN VARCHAR2 ,
1136 X_ATTRIBUTE16 IN VARCHAR2 ,
1137 X_ATTRIBUTE17 IN VARCHAR2 ,
1138 X_ATTRIBUTE18 IN VARCHAR2 ,
1139 X_ATTRIBUTE19 IN VARCHAR2 ,
1140 X_ATTRIBUTE20 IN VARCHAR2 ,
1141 X_COND_RETURN_FLAG IN VARCHAR2
1142 ) AS
1143 X_LAST_UPDATE_DATE DATE;
1144 X_LAST_UPDATED_BY NUMBER;
1145 X_LAST_UPDATE_LOGIN NUMBER;
1146 begin
1147 X_LAST_UPDATE_DATE := SYSDATE;
1148 if(X_MODE = 'I') then
1149 X_LAST_UPDATED_BY := 1;
1150 X_LAST_UPDATE_LOGIN := 0;
1151 elsif (X_MODE IN ('R', 'S')) then
1152 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1153 if X_LAST_UPDATED_BY is NULL then
1154 X_LAST_UPDATED_BY := -1;
1155 end if;
1156 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1157 if X_LAST_UPDATE_LOGIN is NULL then
1158 X_LAST_UPDATE_LOGIN := -1;
1159 end if;
1160 else
1161 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1162 IGS_GE_MSG_STACK.ADD;
1163 app_exception.raise_exception;
1164 end if;
1165
1166 Before_DML(
1167 p_action => 'UPDATE',
1168 x_rowid => X_ROWID,
1169 x_person_id => X_PERSON_ID,
1170 x_course_cd => X_COURSE_CD,
1171 x_start_dt => X_START_DT,
1172 x_logical_delete_date => X_LOGICAL_DELETE_DATE,
1173 x_end_dt => X_END_DT,
1174 x_voluntary_ind => X_VOLUNTARY_IND,
1175 x_comments => X_COMMENTS,
1176 X_INTERMISSION_TYPE =>X_INTERMISSION_TYPE,
1177 X_APPROVED =>X_APPROVED,
1178 X_INSTITUTION_NAME => X_INSTITUTION_NAME,
1179 X_MAX_CREDIT_PTS => X_MAX_CREDIT_PTS,
1180 X_MAX_TERMS => X_MAX_TERMS,
1181 X_ANTICIPATED_CREDIT_POINTS => X_ANTICIPATED_CREDIT_POINTS,
1182 X_APPROVER_ID => X_APPROVER_ID,
1183 x_creation_date => X_LAST_UPDATE_DATE,
1184 x_created_by => X_LAST_UPDATED_BY,
1185 x_last_update_date =>X_LAST_UPDATE_DATE,
1186 x_last_updated_by => X_LAST_UPDATED_BY,
1187 x_last_update_login => X_LAST_UPDATE_LOGIN,
1188 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1189 x_attribute1=>X_ATTRIBUTE1,
1190 x_attribute2=>X_ATTRIBUTE2,
1191 x_attribute3=>X_ATTRIBUTE3,
1192 x_attribute4=>X_ATTRIBUTE4,
1193 x_attribute5=>X_ATTRIBUTE5,
1194 x_attribute6=>X_ATTRIBUTE6,
1195 x_attribute7=>X_ATTRIBUTE7,
1196 x_attribute8=>X_ATTRIBUTE8,
1197 x_attribute9=>X_ATTRIBUTE9,
1198 x_attribute10=>X_ATTRIBUTE10,
1199 x_attribute11=>X_ATTRIBUTE11,
1200 x_attribute12=>X_ATTRIBUTE12,
1201 x_attribute13=>X_ATTRIBUTE13,
1202 x_attribute14=>X_ATTRIBUTE14,
1203 x_attribute15=>X_ATTRIBUTE15,
1204 x_attribute16=>X_ATTRIBUTE16,
1205 x_attribute17=>X_ATTRIBUTE17,
1206 x_attribute18=>X_ATTRIBUTE18,
1207 x_attribute19=>X_ATTRIBUTE19,
1208 x_attribute20=>X_ATTRIBUTE20,
1209 X_COND_RETURN_FLAG => X_COND_RETURN_FLAG
1210 );
1211
1212 IF (x_mode = 'S') THEN
1213 igs_sc_gen_001.set_ctx('R');
1214 END IF;
1215 update IGS_EN_STDNT_PS_INTM set
1216 LOGICAL_DELETE_DATE = NEW_REFERENCES.LOGICAL_DELETE_DATE,
1217 END_DT = NEW_REFERENCES.END_DT,
1218 VOLUNTARY_IND = NEW_REFERENCES.VOLUNTARY_IND,
1219 COMMENTS = NEW_REFERENCES.COMMENTS,
1220 INTERMISSION_TYPE = NEW_REFERENCES.INTERMISSION_TYPE,
1221 APPROVED = NEW_REFERENCES.APPROVED,
1222 INSTITUTION_NAME= NEW_REFERENCES.INSTITUTION_NAME,
1223 MAX_CREDIT_PTS = NEW_REFERENCES.MAX_CREDIT_PTS ,
1224 MAX_TERMS = NEW_REFERENCES.MAX_TERMS,
1225 ANTICIPATED_CREDIT_POINTS = NEW_REFERENCES.ANTICIPATED_CREDIT_POINTS,
1226 APPROVER_ID= NEW_REFERENCES.APPROVER_ID,
1227 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1228 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1229 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1230 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1231 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
1232 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
1233 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
1234 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
1235 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
1236 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
1237 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
1238 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
1239 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
1240 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
1241 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
1242 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
1243 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
1244 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
1245 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
1246 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
1247 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
1248 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
1249 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
1250 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20,
1251 COND_RETURN_FLAG = NEW_REFERENCES.COND_RETURN_FLAG
1252 where ROWID = X_ROWID
1253 ;
1254 if (sql%notfound) then
1255 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1256 igs_ge_msg_stack.add;
1257 igs_sc_gen_001.unset_ctx('R');
1258 app_exception.raise_exception;
1259 end if;
1260 IF (x_mode = 'S') THEN
1261 igs_sc_gen_001.unset_ctx('R');
1262 END IF;
1263
1264
1265
1266 After_DML(
1267 p_action => 'UPDATE',
1268 x_rowid => X_ROWID
1269 );
1270 EXCEPTION
1271 WHEN OTHERS THEN
1272 IF (SQLCODE = (-28115)) THEN
1273 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1274 fnd_message.set_token ('ERR_CD', SQLCODE);
1275 igs_ge_msg_stack.add;
1276 igs_sc_gen_001.unset_ctx('R');
1277 app_exception.raise_exception;
1278 ELSE
1279 igs_sc_gen_001.unset_ctx('R');
1280 RAISE;
1281 END IF;
1282
1283 end UPDATE_ROW;
1284
1285 procedure ADD_ROW (
1286 X_ROWID in out NOCOPY VARCHAR2,
1287 X_PERSON_ID in NUMBER,
1288 X_COURSE_CD in VARCHAR2,
1289 X_START_DT in DATE,
1290 X_LOGICAL_DELETE_DATE in DATE,
1291 X_END_DT in DATE,
1292 X_VOLUNTARY_IND in VARCHAR2,
1293 X_COMMENTS in VARCHAR2,
1294 X_INTERMISSION_TYPE in VARCHAR2 ,
1295 X_APPROVED in VARCHAR2 ,
1296 X_INSTITUTION_NAME in VARCHAR2 ,
1297 X_MAX_CREDIT_PTS in NUMBER ,
1298 X_MAX_TERMS in NUMBER ,
1299 X_ANTICIPATED_CREDIT_POINTS in NUMBER ,
1300 X_APPROVER_ID in NUMBER ,
1301 X_MODE in VARCHAR2 ,
1302 X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
1303 X_ATTRIBUTE1 IN VARCHAR2 ,
1304 X_ATTRIBUTE2 IN VARCHAR2 ,
1305 X_ATTRIBUTE3 IN VARCHAR2 ,
1306 X_ATTRIBUTE4 IN VARCHAR2 ,
1307 X_ATTRIBUTE5 IN VARCHAR2 ,
1308 X_ATTRIBUTE6 IN VARCHAR2 ,
1309 X_ATTRIBUTE7 IN VARCHAR2 ,
1310 X_ATTRIBUTE8 IN VARCHAR2 ,
1311 X_ATTRIBUTE9 IN VARCHAR2 ,
1312 X_ATTRIBUTE10 IN VARCHAR2 ,
1313 X_ATTRIBUTE11 IN VARCHAR2 ,
1314 X_ATTRIBUTE12 IN VARCHAR2 ,
1315 X_ATTRIBUTE13 IN VARCHAR2 ,
1316 X_ATTRIBUTE14 IN VARCHAR2 ,
1317 X_ATTRIBUTE15 IN VARCHAR2 ,
1318 X_ATTRIBUTE16 IN VARCHAR2 ,
1319 X_ATTRIBUTE17 IN VARCHAR2 ,
1320 X_ATTRIBUTE18 IN VARCHAR2 ,
1321 X_ATTRIBUTE19 IN VARCHAR2 ,
1322 X_ATTRIBUTE20 IN VARCHAR2 ,
1323 X_COND_RETURN_FLAG IN VARCHAR2
1324 ) AS
1325 cursor c1 is select rowid from IGS_EN_STDNT_PS_INTM
1326 where PERSON_ID = X_PERSON_ID
1327 and COURSE_CD = X_COURSE_CD
1328 and START_DT = X_START_DT
1329 and LOGICAL_DELETE_DATE = X_LOGICAL_DELETE_DATE
1330 ;
1331
1332 begin
1333 open c1;
1334 fetch c1 into X_ROWID;
1335 if (c1%notfound) then
1336 close c1;
1337 INSERT_ROW (
1338 X_ROWID,
1339 X_PERSON_ID,
1340 X_COURSE_CD,
1341 X_START_DT,
1342 X_LOGICAL_DELETE_DATE,
1343 X_END_DT,
1344 X_VOLUNTARY_IND,
1345 X_COMMENTS,
1346 X_INTERMISSION_TYPE,
1347 X_APPROVED,
1348 X_INSTITUTION_NAME,
1349 X_MAX_CREDIT_PTS,
1350 X_MAX_TERMS,
1351 X_ANTICIPATED_CREDIT_POINTS,
1352 X_APPROVER_ID,
1353 X_MODE,
1354 X_ATTRIBUTE_CATEGORY,
1355 X_ATTRIBUTE1,
1356 X_ATTRIBUTE2,
1357 X_ATTRIBUTE3,
1358 X_ATTRIBUTE4,
1359 X_ATTRIBUTE5,
1360 X_ATTRIBUTE6,
1361 X_ATTRIBUTE7,
1362 X_ATTRIBUTE8,
1363 X_ATTRIBUTE9,
1364 X_ATTRIBUTE10,
1365 X_ATTRIBUTE11,
1366 X_ATTRIBUTE12,
1367 X_ATTRIBUTE13,
1368 X_ATTRIBUTE14,
1369 X_ATTRIBUTE15,
1370 X_ATTRIBUTE16,
1371 X_ATTRIBUTE17,
1372 X_ATTRIBUTE18,
1373 X_ATTRIBUTE19,
1374 X_ATTRIBUTE20,
1375 X_COND_RETURN_FLAG
1376 );
1377 return;
1378 end if;
1379 close c1;
1380 UPDATE_ROW (
1381 X_ROWID,
1382 X_PERSON_ID,
1383 X_COURSE_CD,
1384 X_START_DT,
1385 X_LOGICAL_DELETE_DATE,
1386 X_END_DT,
1387 X_VOLUNTARY_IND,
1388 X_COMMENTS,
1389 X_INTERMISSION_TYPE,
1390 X_APPROVED,
1391 X_INSTITUTION_NAME,
1392 X_MAX_CREDIT_PTS,
1393 X_MAX_TERMS,
1394 X_ANTICIPATED_CREDIT_POINTS,
1395 X_APPROVER_ID,
1396 X_MODE,
1397 X_ATTRIBUTE_CATEGORY,
1398 X_ATTRIBUTE1,
1399 X_ATTRIBUTE2,
1400 X_ATTRIBUTE3,
1401 X_ATTRIBUTE4,
1402 X_ATTRIBUTE5,
1403 X_ATTRIBUTE6,
1404 X_ATTRIBUTE7,
1405 X_ATTRIBUTE8,
1406 X_ATTRIBUTE9,
1407 X_ATTRIBUTE10,
1408 X_ATTRIBUTE11,
1409 X_ATTRIBUTE12,
1410 X_ATTRIBUTE13,
1411 X_ATTRIBUTE14,
1412 X_ATTRIBUTE15,
1413 X_ATTRIBUTE16,
1414 X_ATTRIBUTE17,
1415 X_ATTRIBUTE18,
1416 X_ATTRIBUTE19,
1417 X_ATTRIBUTE20,
1418 X_COND_RETURN_FLAG
1419 );
1420 end ADD_ROW;
1421
1422 procedure DELETE_ROW (
1423 X_ROWID in VARCHAR2,
1424 x_mode IN VARCHAR2
1425 ) AS
1426 begin
1427
1428 Before_DML(
1429 p_action => 'DELETE',
1430 x_rowid => X_ROWID
1431 );
1432
1433 IF (x_mode = 'S') THEN
1434 igs_sc_gen_001.set_ctx('R');
1435 END IF;
1436 delete from IGS_EN_STDNT_PS_INTM
1437 where ROWID = X_ROWID;
1438 if (sql%notfound) then
1439 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1440 igs_ge_msg_stack.add;
1441 igs_sc_gen_001.unset_ctx('R');
1442 app_exception.raise_exception;
1443 end if;
1444 IF (x_mode = 'S') THEN
1445 igs_sc_gen_001.unset_ctx('R');
1446 END IF;
1447
1448
1449
1450 After_DML(
1451 p_action => 'DELETE',
1452 x_rowid => X_ROWID
1453 );
1454
1455
1456 end DELETE_ROW;
1457
1458 end IGS_EN_STDNT_PS_INTM_PKG;