1 PACKAGE BODY Igs_En_Stdnt_Ps_Att_Pkg AS
2 /* $Header: IGSEI24B.pls 120.11 2006/06/29 09:58:09 shimitta ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE;
5 new_references IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE;
6
7 TYPE g_prim_prg_record_type IS RECORD(career igs_ps_ver.course_type%TYPE,program_cd igs_ps_ver.course_cd%TYPE);
8 TYPE g_prim_prg_rec_table_type IS TABLE OF g_prim_prg_record_type INDEX BY BINARY_INTEGER;
9 g_primary_prg_rec g_prim_prg_rec_table_type;
10 g_old_key_prg igs_ps_ver.course_cd%TYPE;
11 g_primary_prg_rec_count NUMBER;
12 g_sec_to_prim_first BOOLEAN;
13
14 PROCEDURE beforerowdelete;
15 PROCEDURE AfterRowInsertUpdate5(
16 p_inserting IN BOOLEAN,
17 p_updating IN BOOLEAN,
18 p_deleting IN BOOLEAN
19 ) ;
20 PROCEDURE enrp_ins_upd_term_rec(P_ACTION IN VARCHAR2);
21
22 PROCEDURE Set_Column_Values (
23 p_action IN VARCHAR2,
24 x_rowid IN VARCHAR2,
25 x_org_id IN NUMBER,
26 x_person_id IN NUMBER,
27 x_course_cd IN VARCHAR2,
28 x_version_number IN NUMBER,
29 x_cal_type IN VARCHAR2,
30 x_location_cd IN VARCHAR2,
31 x_attendance_mode IN VARCHAR2,
32 x_attendance_type IN VARCHAR2,
33 x_coo_id IN NUMBER,
34 x_student_confirmed_ind IN VARCHAR2,
35 x_commencement_dt IN DATE,
36 x_course_attempt_status IN VARCHAR2,
37 x_progression_status IN VARCHAR2,
38 x_derived_att_type IN VARCHAR2,
39 x_derived_att_mode IN VARCHAR2,
40 x_provisional_ind IN VARCHAR2,
41 x_discontinued_dt IN DATE,
42 x_discontinuation_reason_cd IN VARCHAR2,
43 x_lapsed_dt IN DATE,
44 x_funding_source IN VARCHAR2,
45 x_exam_location_cd IN VARCHAR2,
46 x_derived_completion_yr IN NUMBER,
47 x_derived_completion_perd IN VARCHAR2,
48 x_nominated_completion_yr IN NUMBER,
49 x_nominated_completion_perd IN VARCHAR2,
50 x_rule_check_ind IN VARCHAR2,
51 x_waive_option_check_ind IN VARCHAR2,
52 x_last_rule_check_dt IN DATE,
53 x_publish_outcomes_ind IN VARCHAR2,
54 x_course_rqrmnt_complete_ind IN VARCHAR2,
55 x_course_rqrmnts_complete_dt IN DATE,
56 x_s_completed_source_type IN VARCHAR2,
57 x_override_time_limitation IN NUMBER,
58 x_advanced_standing_ind IN VARCHAR2,
59 x_fee_cat IN VARCHAR2,
60 x_correspondence_cat IN VARCHAR2,
61 x_self_help_group_ind IN VARCHAR2,
62 x_logical_delete_dt IN DATE,
63 x_adm_admission_appl_number IN NUMBER,
64 x_adm_nominated_course_cd IN VARCHAR2,
65 x_adm_sequence_number IN NUMBER,
66 x_creation_date IN DATE,
67 x_created_by IN NUMBER,
68 x_last_update_date IN DATE,
69 x_last_updated_by IN NUMBER,
70 x_last_update_login IN NUMBER,
71 x_last_date_of_attendance IN DATE,
72 x_dropped_by IN VARCHAR2,
73 x_igs_pr_class_std_id IN NUMBER,
74 x_primary_program_type IN VARCHAR2,
75 x_primary_prog_type_source IN VARCHAR2,
76 x_catalog_cal_type IN VARCHAR2,
77 x_catalog_seq_num IN NUMBER ,
78 x_key_program IN VARCHAR2,
79 x_manual_ovr_cmpl_dt_ind IN VARCHAR2,
80 x_override_cmpl_dt IN DATE ,
81 x_attribute_category IN VARCHAR2,
82 x_attribute1 IN VARCHAR2,
83 x_attribute2 IN VARCHAR2,
84 x_attribute3 IN VARCHAR2,
85 x_attribute4 IN VARCHAR2,
86 x_attribute5 IN VARCHAR2,
87 x_attribute6 IN VARCHAR2,
88 x_attribute7 IN VARCHAR2,
89 x_attribute8 IN VARCHAR2,
90 x_attribute9 IN VARCHAR2,
91 x_attribute10 IN VARCHAR2,
92 x_attribute11 IN VARCHAR2,
93 x_attribute12 IN VARCHAR2,
94 x_attribute13 IN VARCHAR2,
95 x_attribute14 IN VARCHAR2,
96 x_attribute15 IN VARCHAR2,
97 x_attribute16 IN VARCHAR2,
98 x_attribute17 IN VARCHAR2,
99 x_attribute18 IN VARCHAR2,
100 x_attribute19 IN VARCHAR2,
101 x_attribute20 IN VARCHAR2,
102 x_future_dated_trans_flag In VARCHAR2
103 ) AS
104 CURSOR cur_old_ref_values IS
105 SELECT *
106 FROM IGS_EN_STDNT_PS_ATT_ALL
107 WHERE ROWID = x_rowid;
108 BEGIN
109
110 l_rowid := x_rowid;
111 -- Code for setting the Old and New Reference Values.
112 -- Populate Old Values.
113 OPEN cur_old_ref_values;
114 FETCH cur_old_ref_values INTO old_references;
115 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
116 CLOSE cur_old_ref_values;
117 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 RETURN;
121 END IF;
122 CLOSE cur_old_ref_values;
123
124 /**********************************************************************************************/
125 old_references.commencement_dt := TRUNC(old_references.commencement_dt);
126 old_references.discontinued_dt := TRUNC(old_references.discontinued_dt);
127 old_references.lapsed_dt := TRUNC(old_references.lapsed_dt);
128 old_references.last_rule_check_dt := TRUNC(old_references.last_rule_check_dt);
129 old_references.course_rqrmnts_complete_dt := TRUNC(old_references.course_rqrmnts_complete_dt);
130 old_references.logical_delete_dt := TRUNC(old_references.logical_delete_dt);
131 old_references.override_cmpl_dt := TRUNC(old_references.override_cmpl_dt);
132 old_references.last_date_of_attendance := TRUNC(old_references.last_date_of_attendance);
133 /************************************************************************************************/
134
135 -- Populate New Values.
136 -- Populate New Values.
137 -- When the update row of the TBH is called the org_id column is not passed as parameter
138 -- and the before_dml procedure accpets the parameter as default hence it comes into this
139 -- procedure as null. The new_references.org_id is passed to the
140 -- igs_pe_typ_instances_pkg.insert_row call ( which null and not correct)
141 -- Hence modified the condition to pick up the old_references.org_id value if the
142 -- action was update and the x_org_id parameter value is null
143 -- amuthu
144 IF p_action = 'UPDATE' AND x_org_id IS NULL THEN
145 new_references.org_id := old_references.org_id;
146 ELSE
147 new_references.org_id := x_org_id;
148 END IF;
149 new_references.person_id := x_person_id;
150 new_references.course_cd := x_course_cd;
151 new_references.version_number := x_version_number;
152 new_references.cal_type := x_cal_type;
153 new_references.location_cd := x_location_cd;
154 new_references.attendance_mode := x_attendance_mode;
155 new_references.attendance_type := x_attendance_type;
156 new_references.coo_id := x_coo_id;
157 new_references.student_confirmed_ind := x_student_confirmed_ind;
158 new_references.commencement_dt := TRUNC(x_commencement_dt);
159 new_references.course_attempt_status := x_course_attempt_status;
160 new_references.progression_status := x_progression_status;
161 new_references.derived_att_type := x_derived_att_type;
162 new_references.derived_att_mode := x_derived_att_mode;
163 new_references.provisional_ind := x_provisional_ind;
164 new_references.discontinued_dt := TRUNC(x_discontinued_dt);
165 new_references.discontinuation_reason_cd := x_discontinuation_reason_cd;
166 new_references.lapsed_dt := TRUNC(x_lapsed_dt);
167 new_references.funding_source := x_funding_source;
168 new_references.exam_location_cd := x_exam_location_cd;
169 new_references.derived_completion_yr := x_derived_completion_yr;
170 new_references.derived_completion_perd := x_derived_completion_perd;
171 new_references.nominated_completion_yr := x_nominated_completion_yr;
172 new_references.nominated_completion_perd := x_nominated_completion_perd;
173 new_references.rule_check_ind := x_rule_check_ind;
174 new_references.waive_option_check_ind := x_waive_option_check_ind;
175 new_references.last_rule_check_dt := TRUNC(x_last_rule_check_dt);
176 new_references.publish_outcomes_ind := x_publish_outcomes_ind;
177 new_references.course_rqrmnt_complete_ind := x_course_rqrmnt_complete_ind;
178 new_references.course_rqrmnts_complete_dt := TRUNC(x_course_rqrmnts_complete_dt);
179 new_references.s_completed_source_type := x_s_completed_source_type;
180 new_references.override_time_limitation := x_override_time_limitation;
181 new_references.advanced_standing_ind := x_advanced_standing_ind;
182 new_references.fee_cat := x_fee_cat;
183 new_references.correspondence_cat := x_correspondence_cat;
184 new_references.self_help_group_ind := x_self_help_group_ind;
185 new_references.logical_delete_dt := TRUNC(x_logical_delete_dt);
186 new_references.adm_admission_appl_number := x_adm_admission_appl_number;
187 new_references.adm_nominated_course_cd := x_adm_nominated_course_cd;
188 new_references.adm_sequence_number := x_adm_sequence_number;
189 new_references.attribute_category := x_attribute_category;
190 new_references.future_dated_trans_flag := x_future_dated_trans_flag;
191 new_references.attribute1 := x_attribute1;
192 new_references.attribute2 := x_attribute2;
193 new_references.attribute3 := x_attribute3;
194 new_references.attribute4 := x_attribute4;
195 new_references.attribute5 := x_attribute5;
196 new_references.attribute6 := x_attribute6;
197 new_references.attribute7 := x_attribute7;
198 new_references.attribute8 := x_attribute8;
199 new_references.attribute9 := x_attribute9;
200 new_references.attribute10 := x_attribute10;
201 new_references.attribute11 := x_attribute11;
202 new_references.attribute12 := x_attribute12;
203 new_references.attribute13 := x_attribute13;
204 new_references.attribute14 := x_attribute14;
205 new_references.attribute15 := x_attribute15;
206 new_references.attribute16 := x_attribute16;
207 new_references.attribute17 := x_attribute17;
208 new_references.attribute18 := x_attribute18;
209 new_references.attribute19 := x_attribute19;
210 new_references.attribute20 := x_attribute20;
211 IF (p_action = 'UPDATE') THEN
212 new_references.creation_date := old_references.creation_date;
213 new_references.created_by := old_references.created_by;
214 ELSE
215 new_references.creation_date := x_creation_date;
216 new_references.created_by := x_created_by;
217 END IF;
218 new_references.last_update_date := x_last_update_date;
219 new_references.last_updated_by := x_last_updated_by;
220 new_references.last_update_login := x_last_update_login;
221 new_references.last_date_of_attendance:=TRUNC(x_last_date_of_attendance);
222 new_references.dropped_by:=x_dropped_by;
223 new_references.IGS_PR_CLASS_STD_ID := X_IGS_PR_CLASS_STD_ID;
224
225 --added by amuthu for career impact dld
226 -- smaddali modified the logic for populating primary_program_type and source for bug#4240694
227 -- In program mode these two fields should be populated with NULL
228 IF FND_PROFILE.VALUE('CAREER_MODEL_ENABLED') = 'Y' THEN
229 new_references.primary_program_type := X_PRIMARY_PROGRAM_TYPE;
230 new_references.primary_prog_type_source := X_PRIMARY_PROG_TYPE_SOURCE;
231 ELSE
232 new_references.primary_program_type := NULL;
233 new_references.primary_prog_type_source := NULL;
234 END IF;
235
236 new_references.catalog_cal_type := X_CATALOG_CAL_TYPE;
237 new_references.catalog_seq_num := X_CATALOG_SEQ_NUM;
238 new_references.key_program := NVL(X_KEY_PROGRAM,'N');
239 -- added by ayedubat for ENCR015 DLD
240 new_references.manual_ovr_cmpl_dt_ind := X_MANUAL_OVR_CMPL_DT_IND;
241 new_references.override_cmpl_dt := TRUNC(X_OVERRIDE_CMPL_DT);
242
243 END Set_Column_Values;
244
245
246 -- Created By : jbegum
247 -- Created for : Enhancement Bug #1832130
248 -- This procedure gets the last date of attendance
249 -- Last date of attendance is date till which student has active unit attempts associated with that program attempt
250
251 PROCEDURE Get_Last_Dt_Of_Att (
252 x_person_id IN NUMBER,
253 x_course_cd IN VARCHAR2,
254 x_last_date_of_attendance OUT NOCOPY DATE
255 ) AS
256
257 CURSOR cur_unit_atmpt_dis IS
258 SELECT cal_type,ci_sequence_number,discontinued_dt
259 FROM IGS_EN_SU_ATTEMPT
260 WHERE person_id = x_person_id
261 AND course_cd = x_course_cd
262 AND discontinued_dt IS NOT NULL
263 ORDER BY discontinued_dt DESC;
264
265 CURSOR cur_term_cal(p_cal_type VARCHAR2,p_ci_sequence_number NUMBER, p_discontinued_dt DATE) IS
266 SELECT *
267 FROM IGS_CA_TEACH_TO_LOAD_V
268 WHERE teach_cal_type = p_cal_type
269 AND teach_ci_sequence_number = p_ci_sequence_number
270 AND load_start_dt <= TRUNC(p_discontinued_dt)
271 ORDER BY load_start_dt DESC;
272
273 CURSOR cur_unit_atmpt_grd IS
274 SELECT cal_type,ci_sequence_number
275 FROM IGS_EN_SU_ATTEMPT
276 WHERE person_id = x_person_id
277 AND course_cd = x_course_cd
278 AND unit_attempt_status='COMPLETED';
279
280
281 CURSOR cur_term_cal_grd(p_cal_type VARCHAR2,p_ci_sequence_number NUMBER) IS
282 SELECT *
283 FROM IGS_CA_TEACH_TO_LOAD_V
284 WHERE teach_cal_type = p_cal_type
285 AND teach_ci_sequence_number = p_ci_sequence_number
286 ORDER BY load_end_dt DESC;
287
288 lv_cal_type IGS_CA_TEACH_TO_LOAD_V.teach_cal_type%TYPE;
289 lv_ci_sequence_number IGS_CA_TEACH_TO_LOAD_V.teach_ci_sequence_number%TYPE;
290 lv_discontinued_dt IGS_EN_SU_ATTEMPT.discontinued_dt%TYPE;
291
292 cur_unit_atmpt_dis_rec cur_unit_atmpt_dis%ROWTYPE;
293 cur_term_cal_rec cur_term_cal%ROWTYPE;
294 cur_unit_atmpt_grd_rec cur_unit_atmpt_grd%ROWTYPE;
295 cur_term_cal_grd_rec cur_term_cal_grd%ROWTYPE;
296
297 BEGIN
298
299 OPEN cur_unit_atmpt_dis;
300 FETCH cur_unit_atmpt_dis INTO cur_unit_atmpt_dis_rec;
301
302 IF cur_unit_atmpt_dis%FOUND THEN
303
304 lv_cal_type := cur_unit_atmpt_dis_rec.cal_type;
305 lv_ci_sequence_number := cur_unit_atmpt_dis_rec.ci_sequence_number;
306 lv_discontinued_dt := cur_unit_atmpt_dis_rec.discontinued_dt;
307 CLOSE cur_unit_atmpt_dis;
308
309
310 OPEN cur_term_cal(lv_cal_type,lv_ci_sequence_number,lv_discontinued_dt);
311 FETCH cur_term_cal INTO cur_term_cal_rec;
312 IF (cur_term_cal%FOUND) THEN
313
314 x_last_date_of_attendance := lv_discontinued_dt;
315 CLOSE cur_term_cal;
316 ELSE
317 CLOSE cur_term_cal;
318 lv_discontinued_dt := NULL;
319
320 FOR cur_unit_atmpt_grd_rec IN cur_unit_atmpt_grd
321 LOOP
322
323 OPEN cur_term_cal_grd(cur_unit_atmpt_grd_rec.cal_type,cur_unit_atmpt_grd_rec.ci_sequence_number);
324 FETCH cur_term_cal_grd INTO cur_term_cal_grd_rec;
325
326 IF (cur_term_cal_grd%FOUND) THEN
327
328 IF lv_discontinued_dt IS NULL THEN
329 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
330 ELSIF lv_discontinued_dt < cur_term_cal_grd_rec.load_end_dt THEN
331 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
332 END IF;
333 END IF;
334 CLOSE cur_term_cal_grd;
335 END LOOP;
336
337 x_last_date_of_attendance := lv_discontinued_dt;
338 END IF;
339
340 ELSE
341
342 CLOSE cur_unit_atmpt_dis;
343 lv_discontinued_dt := NULL;
344
345 FOR cur_unit_atmpt_grd_rec IN cur_unit_atmpt_grd
346 LOOP
347
348 OPEN cur_term_cal_grd(cur_unit_atmpt_grd_rec.cal_type,cur_unit_atmpt_grd_rec.ci_sequence_number);
349 FETCH cur_term_cal_grd INTO cur_term_cal_grd_rec;
350
351 IF (cur_term_cal_grd%FOUND) THEN
352
353 IF lv_discontinued_dt IS NULL THEN
354 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
355 ELSIF lv_discontinued_dt < cur_term_cal_grd_rec.load_end_dt THEN
356 lv_discontinued_dt := cur_term_cal_grd_rec.load_end_dt;
357 END IF;
358
359 CLOSE cur_term_cal_grd;
360
361 END IF;
362
363 END LOOP;
364
365 x_last_date_of_attendance := lv_discontinued_dt;
366
367
368 END IF;
369
370 END Get_Last_Dt_Of_Att;
371
372 -- Created By : jbegum
373 -- Created for : Enhancement Bug #1832130
374 -- This procedure gets the dropped by value
375 -- Dropped by is the person type of the person who discontinued the last enrolled unit attempt associated with that program attempt
376
377 PROCEDURE Get_Dropped_By (
378 x_course_cd IN VARCHAR2,
379 x_dropped_by OUT NOCOPY VARCHAR2
380 ) AS
381
382 CURSOR cur_per_type IS
383
384 SELECT person_type_code
385 FROM IGS_PE_PERSON_TYPES_V
386 WHERE system_type = 'STAFF';
387
388 BEGIN
389
390 x_dropped_by := igs_en_gen_008.enrp_get_person_type(x_course_cd);
391
392 IF x_dropped_by IS NULL THEN
393 OPEN cur_per_type;
394 FETCH cur_per_type INTO x_dropped_by;
395 CLOSE cur_per_type;
396 END IF;
397
398 END Get_Dropped_By;
399
400 -- Trigger description :-
401 -- "OSS_TST".trg_sca_br_iu
402 -- BEFORE INSERT OR UPDATE
403 -- ON IGS_EN_STDNT_PS_ATT
404 -- FOR EACH ROW
405
406 PROCEDURE BeforeRowInsertUpdate1(
407 p_inserting IN BOOLEAN,
408 p_updating IN BOOLEAN,
409 p_deleting IN BOOLEAN
410 ) AS
411 v_message_name VARCHAR2(30);
412 v_course_attempt_status IGS_EN_STDNT_PS_ATT_ALL.course_attempt_status%TYPE;
413 l_last_date_of_attendance IGS_EN_STDNT_PS_ATT_ALL.last_date_of_attendance%TYPE;
414 l_dropped_by IGS_EN_STDNT_PS_ATT_ALL.dropped_by%TYPE;
415 v_course_type IGS_PS_TYPE.COURSE_TYPE%TYPE;
416
417 CURSOR c_course_type IS
418 SELECT crv.course_type
419 FROM igs_ps_ver crv
420 WHERE course_cd = new_references.course_cd
421 and version_number = new_references.version_number;
422
423 BEGIN
424
425 -- If trigger has not been disabled, perform required processing
426 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNT_PS_ATT_ALL') THEN
427 -- Set course_offering_option key.
428 IF p_inserting THEN
429 IGS_PS_GEN_003.CRSP_GET_COO_KEY (
430 new_references.coo_id,
431 new_references.course_cd,
432 new_references.version_number,
433 new_references.cal_type,
434 new_references.location_cd,
435 new_references.attendance_mode,
436 new_references.attendance_type);
437 END IF;
438 IF p_updating AND
439 ( (old_references.lapsed_dt IS NOT NULL AND
440 new_references.lapsed_dt IS NULL) OR
441 (old_references.lapsed_dt IS NULL AND
442 new_references.lapsed_dt IS NOT NULL) OR
443 (old_references.lapsed_dt <> new_references.lapsed_dt)) THEN
444 -- Validate the lapsed date against the course attempt status and the
445 -- permitted dates.
446
447 IF Igs_En_Val_Sca.enrp_val_sca_lapse(
448 new_references.course_attempt_status,
449 new_references.lapsed_dt,
450 v_message_name,
451 'N') = FALSE THEN
452 fnd_message.set_name('IGS',v_message_name);
453 IGS_GE_MSG_STACK.ADD;
454 app_exception.raise_exception;
455 END IF;
456
457 END IF;
458 IF p_updating THEN
459 IF (old_references.attendance_type <> new_references.attendance_type) AND
460 (old_references.student_confirmed_ind = new_references.student_confirmed_ind)
461 THEN
462 -- Save the rowid so that candidature attendance history can be inserted
463 -- for change of attendance type
464 IF IGS_EN_GEN_009.ENRP_INS_SCA_CAH(
465 OLD_references.person_id,
466 OLD_references.course_cd,
467 OLD_references.student_confirmed_ind,
468 OLD_references.commencement_dt,
469 OLD_references.attendance_type,
470 v_message_name) = FALSE THEN
471 fnd_message.set_name('IGS',v_message_name);
472 IGS_GE_MSG_STACK.ADD;
473 app_exception.raise_exception;
474 END IF;
475 END IF;
476
477 END IF;
478 IF p_inserting OR p_updating THEN
479 -- Validate confirming an admission course transfer
480 IF p_inserting OR
481 (p_updating AND
482 new_references.student_confirmed_ind <> old_references.student_confirmed_ind) THEN
483 IF Igs_En_Val_Sca.enrp_val_trnsfr_acpt(
484 neW_references.person_id,
485 new_references.course_cd,
486 new_references.student_confirmed_ind,
487 new_references.adm_admission_appl_number,
488 new_references.adm_nominated_course_cd,
489 NULL,
490 v_message_name) = FALSE THEN
491 fnd_message.set_name('IGS',v_message_name);
492 IGS_GE_MSG_STACK.ADD;
493 app_exception.raise_exception;
494 END IF;
495 END IF;
496 -- Validate discontinuation details
497 IF p_inserting OR
498 (p_updating AND
499 ((new_references.discontinued_dt IS NULL AND
500 old_references.discontinued_dt IS NOT NULL) OR
501 (new_references.discontinued_dt IS NOT NULL AND
502 old_references.discontinued_dt IS NULL) OR
503 (new_references.discontinued_dt IS NOT NULL AND
504 old_references.discontinued_dt <>new_references.discontinued_dt))) THEN
505 IF Igs_En_Val_Sca.enrp_val_sca_discont(
506 new_references.person_id,
507 new_references.course_cd,
508 new_references.version_number,
509 new_references.course_attempt_status,
510 new_references.discontinuation_reason_cd,
511 new_references.discontinued_dt,
512 new_references.commencement_dt,
513 v_message_name,
514 'N') = FALSE THEN
515 fnd_message.set_name('IGS',v_message_name);
516 IGS_GE_MSG_STACK.ADD;
517 app_exception.raise_exception;
518 END IF;
519 END IF;
520 -- Validate discontinuation reason code
521 IF p_inserting OR
522 (p_updating AND
523 ((new_references.discontinuation_reason_cd IS NULL AND
524 old_references.discontinuation_reason_cd IS NOT NULL) OR
525 (new_references.discontinuation_reason_cd IS NOT NULL AND
526 old_references.discontinuation_reason_cd IS NULL) OR
527 (new_references.discontinuation_reason_cd IS NOT NULL AND
528 old_references.discontinuation_reason_cd <> new_references.discontinuation_reason_cd))) THEN
529 IF Igs_En_Val_Sca.enrp_val_sca_dr(
530 new_references.person_id,
531 new_references.course_cd,
532 new_references.discontinuation_reason_cd,
533 new_references.discontinued_dt,
534 v_message_name,
535 'N') = FALSE THEN
536 fnd_message.set_name('IGS',v_message_name);
537 IGS_GE_MSG_STACK.ADD;
538 app_exception.raise_exception;
539 END IF;
540 END IF;
541 -- Validate completing the course
542 IF p_inserting OR
543 (p_updating AND
544 new_references.course_rqrmnt_complete_ind <> old_references.course_rqrmnt_complete_ind) THEN
545 IF new_references.course_rqrmnt_complete_ind = 'Y' THEN
546 -- Validate that SCA Status is not 'COMPLETED' or 'UNCONFIRM'.
547 IF new_references.course_attempt_status IN ('COMPLETED','UNCONFIRM') THEN
548 Fnd_Message.set_name('IGS','IGS_PR_CANNOT_SET_COMPL_IND');
549 IGS_GE_MSG_STACK.ADD;
550 App_Exception.Raise_exception;
551 END IF;
552 -- Validate that no unit sets are incomplete or un-ended.
553 IF IGS_PR_VAL_SCA.prgp_val_susa_cmplt (
554 new_references.person_id,
555 new_references.course_cd,
556 v_message_name) = FALSE THEN
557 fnd_message.set_name('IGS',v_message_name);
558 IGS_GE_MSG_STACK.ADD;
559 app_exception.raise_exception;
560 END IF;
561
562
563 -- Validate that status is not DISCONTIN, INTERMIT or LAPSED. If so, the
564 -- course requirements complete indicator cannot be set.
565 IF new_references.course_attempt_status = 'DISCONTIN' THEN
566 Fnd_Message.set_name('IGS','IGS_PR_DISCON_DT_BE_LIFTED');
567 IGS_GE_MSG_STACK.ADD;
568 App_Exception.Raise_exception;
569 ELSIF new_references.course_attempt_status = 'LAPSED' THEN
570 Fnd_Message.set_name('IGS','IGS_PR_LAPSED_DT_BE_LIFTED');
571 IGS_GE_MSG_STACK.ADD;
572 App_Exception.Raise_exception;
573 ELSIF new_references.course_attempt_status = 'INTERMIT' THEN
574 Fnd_Message.set_name('IGS','IGS_PR_INTERMISSION_BE_LIFTED');
575 IGS_GE_MSG_STACK.ADD;
576 App_Exception.Raise_exception;
577 END IF;
578 ELSE
579 -- Check that associated IGS_GR_GRADUAND record does not have a status
580 -- of 'GRADUATED' or 'SURRENDER'.
581 IF IGS_PR_VAL_SCA.prgp_val_undo_cmpltn (
582 new_references.person_id,
583 new_references.course_cd,
584 new_references.version_number,
585 NULL,
586 NULL,
587 v_message_name) = FALSE THEN
588 fnd_message.set_name('IGS',v_message_name);
589 IGS_GE_MSG_STACK.ADD;
590 app_exception.raise_exception;
591 END IF;
592 END IF;
593 END IF;
594 -- Validate the IGS_EN_STDNT_PS_ATT_ALL.course_rqrmnts_complete_dt
595 IF p_inserting OR
596 (p_updating AND
597 ((new_references.course_rqrmnts_complete_dt IS NULL AND
598 old_references.course_rqrmnts_complete_dt IS NOT NULL) OR
599 (new_references.course_rqrmnts_complete_dt IS NOT NULL AND
600 old_references.course_rqrmnts_complete_dt IS NULL) OR
601 (new_references.course_rqrmnts_complete_dt IS NOT NULL AND
602 new_references.course_rqrmnts_complete_dt <> old_references.course_rqrmnts_complete_dt))) THEN
603 IF IGS_PR_VAL_SCA.prgp_val_sca_cmpl_dt (
604 new_references.person_id,
605 new_references.course_cd,
606 new_references.commencement_dt,
607 new_references.course_rqrmnts_complete_dt,
608 v_message_name,
609 'N') = FALSE THEN
610 fnd_message.set_name('IGS',v_message_name);
611 IGS_GE_MSG_STACK.ADD;
612 app_exception.raise_exception;
613 END IF;
614 END IF;
615 -- Validate fee category is open
616 IF p_inserting OR
617 (p_updating AND
618 new_references.fee_cat <> old_references.fee_cat) THEN
619 IF Igs_ad_Val_fcm.finp_val_fc_closed(
620 new_references.fee_cat,
621 v_message_name) = FALSE THEN
622 fnd_message.set_name('IGS',v_message_name);
623 IGS_GE_MSG_STACK.ADD;
624 app_exception.raise_exception;
625 END IF;
626 END IF;
627 -- Set course attempt status
628 IF new_references.course_attempt_status IS NULL THEN
629 v_course_attempt_status := 'UNKNOWN';
630 ELSE
631 v_course_attempt_status := new_references.course_attempt_status;
632 END IF;
633
634 IF (NOT IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml) THEN
635 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N'
636 OR new_references.primary_program_type = 'PRIMARY' THEN
637 new_references.course_attempt_status :=
638 IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(
639 new_references.person_id,
640 new_references.course_cd,
641 v_course_attempt_status,
642 new_references.student_confirmed_ind,
643 new_references.discontinued_dt,
644 new_references.lapsed_dt,
645 new_references.course_rqrmnt_complete_ind,
646 new_references.logical_delete_dt);
647 ELSIF new_references.course_attempt_status IN ('INACTIVE','ENROLLED','LAPSED','INTERMIT') THEN
648 OPEN c_course_type;
649 FETCH c_course_type INTO v_course_type;
650 CLOSE c_course_type;
651 new_references.course_attempt_status :=
652 igs_en_career_model.enrp_get_sec_sca_status( new_references.person_id ,
653 new_references.course_cd ,
654 new_references.course_attempt_status,
655 new_references.primary_program_type ,
656 new_references.primary_prog_type_source,
657 v_course_type ,
658 NULL);
659 IF new_references.course_attempt_status IS NULL THEN
660 new_references.course_attempt_status :=
661 IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(
662 new_references.person_id,
663 new_references.course_cd,
664 v_course_attempt_status,
665 new_references.student_confirmed_ind,
666 new_references.discontinued_dt,
667 new_references.lapsed_dt,
668 new_references.course_rqrmnt_complete_ind,
669 new_references.logical_delete_dt);
670 END IF;
671 ELSE
672 new_references.course_attempt_status :=
673 IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(
674 new_references.person_id,
675 new_references.course_cd,
676 v_course_attempt_status,
677 new_references.student_confirmed_ind,
678 new_references.discontinued_dt,
679 new_references.lapsed_dt,
680 new_references.course_rqrmnt_complete_ind,
681 new_references.logical_delete_dt);
682
683 END IF;
684 END IF;
685
686 END IF;
687 END IF;
688
689 -- Created By : jbegum
690 -- Created for : Enhancement Bug #1832130
691 -- When the program attempt status changes from ENROLLED to INACTIVE , INTERMIT , LAPSED , DISCONTIN
692 -- Compute values for fields Last_date_of_attendance and dropped_by before updating the record in the
693 -- table IGS_EN_STDNT_PS_ATT_ALL
694
695 IF p_updating AND old_references.course_attempt_status= 'ENROLLED'
696 AND new_references.course_attempt_status IN ('INACTIVE','INTERMIT','LAPSED','DISCONTIN') THEN
697
698 Get_Last_Dt_Of_Att (new_references.person_id,new_references.course_cd,l_last_date_of_attendance);
699 Get_Dropped_By (new_references.course_cd,l_dropped_by);
700
701 new_references.last_date_of_attendance := l_last_date_of_attendance;
702 new_references.dropped_by := l_dropped_by;
703
704
705 END IF;
706
707 --sanity check to ensure that the course_attempt_status is never null.
708
709 IF p_inserting OR p_updating THEN
710 IF new_references.course_attempt_status IS NULL THEN
711 new_references.course_attempt_status :=
712 IGS_EN_GEN_006.ENRP_GET_SCA_STATUS(
713 new_references.person_id,
714 new_references.course_cd,
715 v_course_attempt_status,
716 new_references.student_confirmed_ind,
717 new_references.discontinued_dt,
718 new_references.lapsed_dt,
719 new_references.course_rqrmnt_complete_ind,
720 new_references.logical_delete_dt);
721 END IF;
722 END IF;
723 END BeforeRowInsertUpdate1;
724 -- Trigger description :-
725 -- "OSS_TST".trg_sca_br_iud_fin
726 -- BEFORE INSERT OR DELETE OR UPDATE
727 -- ON IGS_EN_STDNT_PS_ATT
728 -- FOR EACH ROW
729 PROCEDURE BeforeRowInsertUpdateDelete2(
730 p_inserting IN BOOLEAN,
731 p_updating IN BOOLEAN,
732 p_deleting IN BOOLEAN
733 ) AS
734 v_sequence_number NUMBER;
735 BEGIN
736 -- Log an entry in the IGS_PE_STD_TODO table, indicating that a fee re-assessment
737 -- is required.
738 IF p_inserting OR p_updating THEN
739 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
740 new_references.person_id,
741 'FEE_RECALC',
742 SYSDATE,
743 'Y');
744 ELSE
745 v_sequence_number := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(
746 old_references.person_id,
747 'FEE_RECALC',
748 SYSDATE,
749 'Y');
750 END IF;
751
752 IF p_updating THEN
753 IF new_references.student_confirmed_ind = 'Y'
754 AND old_references.future_dated_trans_flag = 'C'
755 AND new_references.future_dated_trans_flag NOT IN ('Y','S' )THEN
756 new_references.future_dated_trans_flag := 'N';
757 END IF;
758 END IF;
759
760 END BeforeRowInsertUpdateDelete2;
761
762 -- Trigger description :-
763 -- "OSS_TST".trg_sca_ar_iu
764 -- AFTER INSERT OR UPDATE
765 -- ON IGS_EN_STDNT_PS_ATT
766 -- FOR EACH ROW
767 PROCEDURE AfterRowInsertUpdate3(
768 p_inserting IN BOOLEAN,
769 p_updating IN BOOLEAN,
770 p_deleting IN BOOLEAN
771 ) AS
772 v_message_name VARCHAR2(30);
773 v_save_row BOOLEAN := FALSE;
774 r_sca IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE;
775 BEGIN
776 -- mutating table validations / end of processing activities
777 -- If trigger has not been disabled, perform required processing
778 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNT_PS_ATT_ALL') THEN
779 IF(p_updating AND
780 old_references.student_confirmed_ind = 'N' AND
781 new_references.student_confirmed_ind = 'Y') OR
782 (p_inserting AND
783 new_references.student_confirmed_ind = 'Y') THEN
784 -- update of application response status on confirming the offer
785 v_save_row := TRUE;
786 r_sca.student_confirmed_ind := old_references.student_confirmed_ind;
787 END IF;
788 -- validate fee category
789 IF p_inserting OR
790 (p_updating AND
791 new_references.fee_cat <> old_references.fee_cat) THEN
792 v_save_row := TRUE;
793 r_sca.fee_cat := old_references.fee_cat;
794 END IF;
795 IF v_save_row = TRUE THEN
796 r_sca.person_id := new_references.person_id;
797 r_sca.course_cd := new_references.course_cd;
798
799 -- a validation to check whether the fee category which is being updated is used for any fee assessment.
800 -- in case assessment record exists for this program attempt then the fee categoryu could not be changed
801 -- removed call to procedure Igs_En_Val_Sca.enrp_val_sca_fc.
802 -- this validation has been removed as a requirement for the fee calc build (bug# 1851586)
803
804
805 -- Update the offer response status/date
806 IF(p_updating AND
807 old_references.student_confirmed_ind = 'N' AND
808 new_references.student_confirmed_ind = 'Y') OR
809 (p_inserting AND
810 new_references.student_confirmed_ind = 'Y') THEN
811 IF Igs_En_Gen_011.ENRP_UPD_ACAI_ACCEPT(
812 new_references.person_id,
813 new_references.course_cd,
814 new_references.adm_admission_appl_number,
815 new_references.adm_nominated_course_cd,
816 new_references.adm_sequence_number,
817 v_message_name) = FALSE THEN
818 fnd_message.set_name('IGS',v_message_name);
819 IGS_GE_MSG_STACK.ADD;
820 app_exception.raise_exception;
821 END IF;
822 END IF;
823 END IF;
824 END IF;
825
826 END AfterRowInsertUpdate3;
827
828 PROCEDURE AfterRowInsertUpdate5(
829 p_inserting IN BOOLEAN,
830 p_updating IN BOOLEAN,
831 p_deleting IN BOOLEAN
832 ) AS
833
834 BEGIN
835
836 -- Bug # 2829275 . UK Correspondence. The TBH needs to be modified to so that the program discontinuation event is raised when the program attempt is changed .
837
838
839 IF ( p_updating
840 AND ((new_references.location_cd <> old_references.location_cd)
841 OR (new_references.attendance_mode <> old_references.attendance_mode)
842 OR (new_references.attendance_type <> old_references.attendance_type)
843 OR (new_references.cal_type <> old_references.cal_type))) THEN
844
845
846 igs_en_workflow.progofropt_event (
847 p_personid => new_references.person_id,
848 p_programcd => new_references.course_cd,
849 p_locationcd => new_references.location_cd,
850 p_prev_location_cd => old_references.location_cd,
851 p_attndmode => new_references.attendance_mode,
852 p_prev_attndmode => old_references.attendance_mode,
853 p_attndtype => new_references.attendance_type,
854 p_prev_attndtype => old_references.attendance_type
855
856 ) ;
857
858 END IF;
859
860 IF (( (new_references.location_cd <> old_references.location_cd)
861 OR (new_references.attendance_mode <> old_references.attendance_mode)
862 OR (new_references.attendance_type <> old_references.attendance_type))
863 AND (new_references.key_program = old_references.key_program)
864 AND (new_references.key_program = 'Y' ) ) THEN
865 igf_aw_coa_gen.ins_coa_todo(p_person_id => new_references.person_id ,
866 p_calling_module => 'IGSEI24B',
867 p_program_code => new_references.course_cd,
868 p_version_number=>new_references.version_number) ;
869 END IF;
870
871 END AfterRowInsertUpdate5;
872
873 -- This has been added for bug# 1516605
874 -- Changing the Person Type Based on The Program Attempt Status
875
876
877 PROCEDURE AfterRowInsertUpdate4(
878 p_inserting IN BOOLEAN,
879 p_updating IN BOOLEAN,
880 p_deleting IN BOOLEAN
881 ) AS
882 /* HISTORY
883 -- who when what
884 --ayedubat 09-JUL-2002 Added a new cursor,cur_pe_typ_inst to consider the active person type records
885 -- of a student program which are mapped to a closed system type.
886 -- Check whenther the cursor is fetching a record whenever a person type record of a particular
887 -- system type should be end dated and if found, call the TBH to end date the record for the bug:2389552
888 --ayedubat 2nd MAY 2002 Added the code for p_inseting = TRUE as part of the bug fix:2344079
889 -- This will change the Person_Type from Applicant to Student during the pre_enrollment process
890 -- through admissions subsystem.
891 --shimitta 29 JUNE 2006 Modified l_method acording as bug# 2691653
892 */
893 --modified cursor for perf bug 3696781
894 CURSOR cur_typ_id_inst(p_PERSON_ID NUMBER,p_COURSE_CD VARCHAR2,p_PERSON_TYPE_CODE VARCHAR2) IS
895 SELECT pti.*
896 FROM igs_pe_typ_instances_all pti
897 WHERE pti.PERSON_ID = p_PERSON_ID AND
898 pti.COURSE_CD = p_COURSE_CD AND
899 pti.PERSON_TYPE_CODE = p_PERSON_TYPE_CODE AND
900 pti.END_DATE IS NULL;
901
902 CURSOR cur_pers_type(p_system_type varchar2) IS
903 SELECT PERSON_TYPE_CODE
904 FROM igs_pe_person_types
905 WHERE SYSTEM_TYPE = p_system_type AND
906 CLOSED_IND = 'N';
907
908 -- Cursor used to fetch the Person Type Instance record which is being opened
909 -- irrespective of the system person type is closed or not.
910 -- Cursor is being created for the bug fix: 2389552
911 CURSOR cur_pe_typ_inst( p_person_id igs_pe_typ_instances.PERSON_ID%TYPE,
912 p_course_cd igs_pe_typ_instances.course_cd%TYPE,
913 p_system_type igs_pe_person_types.SYSTEM_TYPE%TYPE) IS
914 SELECT pti.rowid row_id ,pti.*
915 FROM igs_pe_typ_instances_all pti,
916 igs_pe_person_types pty
917 WHERE pti.person_id = p_person_id AND
918 pti.course_cd = p_course_cd AND
919 pti.end_date IS NULL AND
920 pty.person_type_code = pti.person_type_code AND
921 pty.system_type = p_system_type;
922
923 CURSOR cur_find_interm(cp_perosn_id igs_pe_person.person_id%TYPE,
924 cp_course_cd igs_ps_ver.course_cd%TYPE) IS
925 SELECT sci.person_id,sci.course_cd,start_dt, sci.end_dt, sci.voluntary_ind,sci.comments ,sci.created_by ,sci.creation_date ,
926 sci.last_updated_by ,sci.last_update_date , sci.last_update_login , sci.intermission_type , sci.approved ,sci.institution_name,
927 sci.max_credit_pts, sci.max_terms, sci.anticipated_credit_points, sci.approver_id
928 FROM igs_en_stdnt_ps_intm sci,
929 igs_en_intm_types eit
930 WHERE sci.course_cd = cp_course_cd
931 AND sci.person_id = cp_perosn_id
932 AND sci.approved = eit.appr_reqd_ind
933 AND eit.intermission_type = sci.intermission_type
934 AND sci.logical_delete_date = TO_DATE ('31-12-4712','DD-MM-YYYY');
935
936
937
938 CURSOR cur_re_canditure(cp_person_id igs_pe_person.person_id%TYPE,
939 cp_course_cd igs_ps_ver.course_cd%TYPE) IS
940 SELECT 1
941 FROM igs_re_candidature
942 WHERE sca_course_cd = cp_course_cd
943 AND person_id = cp_person_id ;
944
945
946
947 cur_pe_typ_inst_rec cur_pe_typ_inst%ROWTYPE;
948
949 v_message_name VARCHAR2(30);
950 v_save_row BOOLEAN := FALSE;
951 r_sca IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE;
952 l_method igs_pe_typ_instances.CREATE_METHOD%TYPE;
953 l_person_type igs_pe_person_types.PERSON_TYPE_CODE%TYPE;
954 l_rowid VARCHAR2(25);
955 l_TYPE_INSTANCE_ID igs_pe_typ_instances.TYPE_INSTANCE_ID%TYPE;
956 cur_typ_id_inst_rec cur_typ_id_inst%ROWTYPE;
957 l_cur_find_interm cur_find_interm%ROWTYPE;
958 ln_cur_re_canditure NUMBER;
959
960 BEGIN
961
962 IF p_inserting THEN
963
964 IF new_references.course_attempt_status = 'INACTIVE' THEN
965
966 l_person_type := NULL;
967 -- Select Person type Code for the System type FORMER_STUDENT
968 OPEN cur_pers_type('APPLICANT');
969 FETCH cur_pers_type INTO l_person_type;
970 CLOSE cur_pers_type;
971 IF l_person_type IS NULL THEN
972 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
973 IGS_GE_MSG_STACK.ADD;
974 App_Exception.Raise_Exception;
975 END IF;
976
977 l_method := 'PERSON_ENROL_UNIT_SECT';
978
979 -- Check any active record found for this student program, with System Person Type,APPLICANT
980 -- This should be considered the active applicants(i.e not end dated) whose system person type is closed
981 OPEN cur_pe_typ_inst( new_references.person_id,
982 new_references.course_cd,
983 'APPLICANT' );
984 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
985 IF cur_pe_typ_inst%FOUND THEN
986
987 igs_pe_typ_instances_pkg.update_row(
988 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
989 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
990 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
991 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
992 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
993 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
994 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
995 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
996 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
997 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
998 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
999 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
1000 X_END_DATE => SYSDATE,
1001 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
1002 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
1003 X_END_METHOD => l_method,
1004 X_MODE => 'R' ,
1005 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code );
1006
1007 END IF;
1008 CLOSE cur_pe_typ_inst;
1009
1010 l_person_type := NULL;
1011 -- Select Person type Code for the System type STUDENT
1012 OPEN cur_pers_type('STUDENT');
1013 FETCH cur_pers_type INTO l_person_type;
1014 CLOSE cur_pers_type;
1015 IF l_person_type IS NULL THEN
1016 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
1017 IGS_GE_MSG_STACK.ADD;
1018 App_Exception.Raise_Exception;
1019 END IF;
1020
1021 OPEN cur_typ_id_inst(new_references.PERSON_ID,new_references.COURSE_CD,l_person_type);
1022 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
1023 IF cur_typ_id_inst%NOTFOUND THEN
1024 igs_pe_typ_instances_pkg.insert_row(
1025 X_ROWID => l_ROWID,
1026 X_PERSON_ID => new_references.PERSON_ID,
1027 X_COURSE_CD => new_references.COURSE_CD,
1028 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
1029 X_PERSON_TYPE_CODE => l_person_type,
1030 X_CC_VERSION_NUMBER => NULL,
1031 X_FUNNEL_STATUS => NULL,
1032 X_ADMISSION_APPL_NUMBER => NULL,
1033 X_NOMINATED_COURSE_CD => NULL,
1034 X_NCC_VERSION_NUMBER => NULL,
1035 X_SEQUENCE_NUMBER => NULL,
1036 X_START_DATE => SYSDATE,
1037 X_END_DATE => NULL,
1038 X_CREATE_METHOD => l_method,
1039 X_ENDED_BY => NULL,
1040 X_END_METHOD => NULL,
1041 X_MODE => 'R',
1042 X_ORG_ID => new_references.ORG_ID ,
1043 X_EMPLMNT_CATEGORY_CODE => NULL
1044 );
1045 END IF;
1046 CLOSE cur_typ_id_inst;
1047
1048 END IF;
1049
1050
1051 ELSIF p_updating THEN
1052
1053 IF new_references.course_attempt_status IN ('ENROLLED','INACTIVE') AND
1054 new_references.course_attempt_status <> old_references.course_attempt_status THEN
1055
1056 l_person_type := NULL;
1057
1058 -- Select Person type Code for the System type FORMER_STUDENT
1059 OPEN cur_pers_type('FORMER_STUDENT');
1060 FETCH cur_pers_type INTO l_person_type;
1061 CLOSE cur_pers_type;
1062 IF l_person_type IS NULL THEN
1063 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
1064 IGS_GE_MSG_STACK.ADD;
1065 App_Exception.Raise_Exception;
1066 END IF;
1067
1068 l_method := 'PERSON_ENROL_UNIT_SECT';
1069
1070 -- Check any active record found for this student program, with System Person Type,FORMER_STUDENT
1071 -- This should be considered the active former students (i.e not end dated) whose system person type is closed
1072 OPEN cur_pe_typ_inst( new_references.person_id,
1073 new_references.COURSE_CD,
1074 'FORMER_STUDENT');
1075
1076 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
1077
1078 IF cur_pe_typ_inst%FOUND THEN
1079
1080 l_method := 'PERSON_ENROLL_PRG';
1081
1082 igs_pe_typ_instances_pkg.update_row(
1083 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
1084 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
1085 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
1086 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
1087 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
1088 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
1089 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
1090 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
1091 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
1092 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
1093 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
1094 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
1095 X_END_DATE => SYSDATE,
1096 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
1097 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
1098 X_END_METHOD => l_method,
1099 X_MODE => 'R' ,
1100 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
1101 END IF;
1102 CLOSE cur_pe_typ_inst;
1103
1104 l_person_type := NULL;
1105 -- Select Person type Code for the System type STUDENT
1106 OPEN cur_pers_type('STUDENT');
1107 FETCH cur_pers_type INTO l_person_type;
1108 CLOSE cur_pers_type;
1109 IF l_person_type IS NULL THEN
1110 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
1111 IGS_GE_MSG_STACK.ADD;
1112 App_Exception.Raise_Exception;
1113 END IF;
1114
1115 OPEN cur_typ_id_inst(new_references.PERSON_ID,new_references.COURSE_CD,l_person_type);
1116 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
1117 IF cur_typ_id_inst%NOTFOUND THEN
1118 igs_pe_typ_instances_pkg.insert_row(
1119 X_ROWID => l_ROWID,
1120 X_PERSON_ID => new_references.PERSON_ID,
1121 X_COURSE_CD => new_references.COURSE_CD,
1122 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
1123 X_PERSON_TYPE_CODE => l_person_type,
1124 X_CC_VERSION_NUMBER => NULL,
1125 X_FUNNEL_STATUS => NULL,
1126 X_ADMISSION_APPL_NUMBER => NULL,
1127 X_NOMINATED_COURSE_CD => NULL,
1128 X_NCC_VERSION_NUMBER => NULL,
1129 X_SEQUENCE_NUMBER => NULL,
1130 X_START_DATE => SYSDATE,
1131 X_END_DATE => NULL,
1132 X_CREATE_METHOD => l_method,
1133 X_ENDED_BY => NULL,
1134 X_END_METHOD => NULL,
1135 X_MODE => 'R',
1136 X_ORG_ID => new_references.ORG_ID,
1137 X_EMPLMNT_CATEGORY_CODE => NULL
1138 );
1139 END IF;
1140 CLOSE cur_typ_id_inst;
1141
1142 ELSIF old_references.course_attempt_status IN ('ENROLLED','INACTIVE') AND
1143 new_references.course_attempt_status IN ('LAPSED','COMPLETED','DISCONTIN') THEN
1144 IF new_references.course_attempt_status = 'LAPSED' THEN
1145 l_method := 'PRG_ATTMPT_ST_LAPSED';
1146 ELSIF new_references.course_attempt_status = 'DISCONTIN' THEN
1147 l_method := 'PERSON_DISCONTINUE_PRG';
1148 -- ElsIf new_references.course_attempt_status = 'COMPLETED' Then
1149 -- l_method := '';
1150 END IF;
1151
1152 l_person_type := NULL;
1153 -- Select Person type Code for the System type STUDENT
1154 OPEN cur_pers_type('STUDENT');
1155 FETCH cur_pers_type INTO l_person_type;
1156 CLOSE cur_pers_type;
1157 IF l_person_type IS NULL THEN
1158 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
1159 IGS_GE_MSG_STACK.ADD;
1160 App_Exception.Raise_Exception;
1161 END IF;
1162
1163 -- Check any active record found for this student program, with System Person Type,STUDENT
1164 -- This should be considered the active students (i.e not end dated) whose system person type is closed
1165 OPEN cur_pe_typ_inst( new_references.person_id,
1166 new_references.COURSE_CD,
1167 'STUDENT' );
1168 FETCH cur_pe_typ_inst INTO cur_pe_typ_inst_rec;
1169 IF cur_pe_typ_inst%FOUND THEN
1170
1171 l_method := 'PERSON_NO_ENROLL_PRG';
1172
1173 igs_pe_typ_instances_pkg.update_row(
1174 X_ROWID => cur_pe_typ_inst_rec.ROW_ID,
1175 X_PERSON_ID => cur_pe_typ_inst_rec.PERSON_ID,
1176 X_COURSE_CD => cur_pe_typ_inst_rec.COURSE_CD,
1177 X_TYPE_INSTANCE_ID => cur_pe_typ_inst_rec.TYPE_INSTANCE_ID,
1178 X_PERSON_TYPE_CODE => cur_pe_typ_inst_rec.PERSON_TYPE_CODE,
1179 X_CC_VERSION_NUMBER => cur_pe_typ_inst_rec.CC_VERSION_NUMBER,
1180 X_FUNNEL_STATUS => cur_pe_typ_inst_rec.FUNNEL_STATUS,
1181 X_ADMISSION_APPL_NUMBER => cur_pe_typ_inst_rec.ADMISSION_APPL_NUMBER,
1182 X_NOMINATED_COURSE_CD => cur_pe_typ_inst_rec.NOMINATED_COURSE_CD,
1183 X_NCC_VERSION_NUMBER => cur_pe_typ_inst_rec.NCC_VERSION_NUMBER,
1184 X_SEQUENCE_NUMBER => cur_pe_typ_inst_rec.SEQUENCE_NUMBER,
1185 X_START_DATE => cur_pe_typ_inst_rec.START_DATE,
1186 X_END_DATE => SYSDATE,
1187 X_CREATE_METHOD => cur_pe_typ_inst_rec.CREATE_METHOD,
1188 X_ENDED_BY => cur_pe_typ_inst_rec.ENDED_BY,
1189 X_END_METHOD => l_method,
1190 X_MODE => 'R' ,
1191 X_EMPLMNT_CATEGORY_CODE => cur_pe_typ_inst_rec.emplmnt_category_code);
1192 END IF;
1193 CLOSE cur_pe_typ_inst;
1194
1195 l_person_type := NULL;
1196 -- Select Person type Code for the System type FORMER_STUDENT
1197 OPEN cur_pers_type('FORMER_STUDENT');
1198 FETCH cur_pers_type INTO l_person_type;
1199 CLOSE cur_pers_type;
1200 IF l_person_type IS NULL THEN
1201 Fnd_Message.Set_Name ('IGS', 'IGS_EN_PERSON_TYPE_NOT_DEF');
1202 IGS_GE_MSG_STACK.ADD;
1203 App_Exception.Raise_Exception;
1204 END IF;
1205
1206 OPEN cur_typ_id_inst(new_references.PERSON_ID,new_references.COURSE_CD,l_person_type);
1207 FETCH cur_typ_id_inst INTO cur_typ_id_inst_rec;
1208 IF cur_typ_id_inst%NOTFOUND THEN
1209 igs_pe_typ_instances_pkg.insert_row(
1210 X_ROWID => l_ROWID,
1211 X_PERSON_ID => new_references.PERSON_ID,
1212 X_COURSE_CD => new_references.COURSE_CD,
1213 X_TYPE_INSTANCE_ID => l_TYPE_INSTANCE_ID,
1214 X_PERSON_TYPE_CODE => l_person_type,
1215 X_CC_VERSION_NUMBER => NULL,
1216 X_FUNNEL_STATUS => NULL,
1217 X_ADMISSION_APPL_NUMBER => NULL,
1218 X_NOMINATED_COURSE_CD => NULL,
1219 X_NCC_VERSION_NUMBER => NULL,
1220 X_SEQUENCE_NUMBER => NULL,
1221 X_START_DATE => SYSDATE,
1222 X_END_DATE => NULL,
1223 X_CREATE_METHOD => l_method,
1224 X_ENDED_BY => NULL,
1225 X_END_METHOD => NULL,
1226 X_MODE => 'R',
1227 X_ORG_ID => new_references.ORG_ID,
1228 X_EMPLMNT_CATEGORY_CODE => NULL
1229 );
1230 END IF;
1231 CLOSE cur_typ_id_inst;
1232 END IF;
1233
1234 -- Bug # 2829275 . UK Correspondence. The business event is raised when the program is DISCONTINUED.
1235
1236 IF ( new_references.course_attempt_status <> old_references.course_attempt_status AND new_references.course_attempt_status = 'DISCONTIN') THEN
1237
1238 igs_en_workflow.progdiscont_event(
1239 p_personid => new_references.person_id ,
1240 p_programcd => new_references.course_cd ,
1241 p_discontindt => new_references.discontinued_dt,
1242 p_discontincd => new_references.discontinuation_reason_cd
1243 );
1244 IF NVL(old_references.key_program,'N') = 'Y' THEN
1245 igf_aw_coa_gen.ins_coa_todo(
1246 p_person_id => new_references.person_id ,
1247 p_calling_module => 'IGSEI24B_D',
1248 p_program_code => new_references.course_cd,
1249 p_version_number => new_references.version_number) ;
1250 END IF;
1251
1252 END IF;
1253
1254 -- Bug # 2829275 . UK Correspondence. The business event is raised when the program is DISCONTINUED.
1255
1256 IF ( new_references.course_attempt_status <> old_references.course_attempt_status AND new_references.course_attempt_status = 'INTERMIT') THEN
1257
1258
1259 OPEN cur_find_interm(new_references.person_id,new_references.course_cd);
1260 FETCH cur_find_interm INTO l_cur_find_interm;
1261 CLOSE cur_find_interm;
1262
1263 IF ( l_cur_find_interm.start_dt <> SYSDATE AND l_cur_find_interm.creation_date <> SYSDATE ) THEN
1264
1265 igs_en_workflow.intermission_event(
1266 p_personid => l_cur_find_interm.person_id,
1267 p_program_cd => l_cur_find_interm.course_cd,
1268 p_intmtype => l_cur_find_interm.intermission_type,
1269 p_startdt => l_cur_find_interm.start_dt,
1270 p_enddt => l_cur_find_interm.end_dt ,
1271 p_inst_name => l_cur_find_interm.institution_name,
1272 p_max_cp => l_cur_find_interm.max_credit_pts ,
1273 p_max_term => l_cur_find_interm.max_terms ,
1274 p_anti_cp => l_cur_find_interm.anticipated_credit_points ,
1275 p_approver => l_cur_find_interm.approver_id
1276 );
1277
1278 END IF;
1279 END IF;
1280
1281 -- The changes are done as per the Enrollments Notifications TD Bug # 3052429
1282 -- Workflow notification is send if :
1283 -- 1. The program status changed from UNCONFIRM to INACTIVE
1284 -- 2. If there is a corresponding record exist in IGS_RE_CANDIDATURE
1285
1286
1287 IF ( new_references.course_attempt_status <> old_references.course_attempt_status
1288 AND new_references.course_attempt_status ='INACTIVE' AND old_references.course_attempt_status = 'UNCONFIRM') THEN
1289
1290 OPEN cur_re_canditure(new_references.person_id,new_references.course_cd);
1291 FETCH cur_re_canditure INTO ln_cur_re_canditure;
1292 CLOSE cur_re_canditure;
1293
1294 IF ( ln_cur_re_canditure = 1 ) THEN
1295
1296 igs_re_workflow.confirm_reg_event (
1297 p_personid => new_references.person_id ,
1298 p_programcd => new_references.course_cd ,
1299 p_spa_start_dt => new_references.commencement_dt ,
1300 p_prog_attempt_stat => new_references.course_attempt_status
1301 );
1302
1303 END IF;
1304 END IF;
1305 END IF;
1306 END AfterRowInsertUpdate4;
1307
1308 -- Trigger description :-
1309 -- "OSS_TST".trg_sca_ar_u_hist
1310 -- AFTER UPDATE
1311 -- ON IGS_EN_STDNT_PS_ATT
1312 -- FOR EACH ROW
1313
1314 -- Modified By : jbegum
1315 -- Modified for : Enhancement Bug #1832130
1316 -- Added four new parameters to the function call IGS_EN_GEN_009.ENRP_INS_SCA_HIST
1317 -- new_references.last_date_of_attendance,
1318 -- old_references.last_date_of_attendance,
1319 -- new_references.dropped_by,
1320 -- old_references.dropped_by
1321
1322 --bdeviset 09-NOV-2004 Bug#4000939 Modified AfterRowUpdate4.
1323
1324 PROCEDURE AfterRowUpdate4(
1325 p_inserting IN BOOLEAN,
1326 p_updating IN BOOLEAN,
1327 p_deleting IN BOOLEAN
1328 ) AS
1329
1330
1331 BEGIN
1332 -- create a history
1333 IGS_EN_GEN_009.ENRP_INS_SCA_HIST(
1334 old_references.person_id,
1335 old_references.course_cd,
1336 new_references.version_number,
1337 old_references.version_number,
1338 new_references.cal_type,
1339 old_references.cal_type,
1340 new_references.location_cd,
1341 old_references.location_cd,
1342 new_references.attendance_mode,
1343 old_references.attendance_mode,
1344 new_references.attendance_type,
1345 old_references.attendance_type,
1346 new_references.student_confirmed_ind,
1347 old_references.student_confirmed_ind,
1348 new_references.commencement_dt,
1349 old_references.commencement_dt,
1350 new_references.course_attempt_status,
1351 old_references.course_attempt_status,
1352 new_references.progression_status,
1353 old_references.progression_status,
1354 new_references.derived_att_type,
1355 old_references.derived_att_type,
1356 new_references.derived_att_mode,
1357 old_references.derived_att_mode,
1358 new_references.provisional_ind,
1359 old_references.provisional_ind,
1360 new_references.discontinued_dt,
1361 old_references.discontinued_dt,
1362 new_references.discontinuation_reason_cd,
1363 old_references.discontinuation_reason_cd,
1364 new_references.lapsed_dt,
1365 old_references.lapsed_dt,
1366 new_references.funding_source,
1367 old_references.funding_source,
1368 new_references.exam_location_cd,
1369 old_references.exam_location_cd,
1370 new_references.derived_completion_yr,
1371 old_references.derived_completion_yr,
1372 new_references.derived_completion_perd,
1373 old_references.derived_completion_perd,
1374 new_references.nominated_completion_yr,
1375 old_references.nominated_completion_yr,
1376 new_references.nominated_completion_perd,
1377 old_references.nominated_completion_perd,
1378 new_references.rule_check_ind,
1379 old_references.rule_check_ind,
1380 new_references.waive_option_check_ind,
1381 old_references.waive_option_check_ind,
1382 new_references.last_rule_check_dt,
1383 old_references.last_rule_check_dt,
1384 new_references.publish_outcomes_ind,
1385 old_references.publish_outcomes_ind,
1386 new_references.course_rqrmnt_complete_ind,
1387 old_references.course_rqrmnt_complete_ind,
1388 new_references.course_rqrmnts_complete_dt,
1389 old_references.course_rqrmnts_complete_dt,
1390 new_references.s_completed_source_type,
1391 old_references.s_completed_source_type,
1392 new_references.override_time_limitation,
1393 old_references.override_time_limitation,
1394 new_references.advanced_standing_ind,
1395 old_references.advanced_standing_ind,
1396 new_references.fee_cat,
1397 old_references.fee_cat,
1398 new_references.self_help_group_ind,
1399 old_references.self_help_group_ind,
1400 new_references.correspondence_cat,
1401 old_references.correspondence_cat,
1402 new_references.adm_admission_appl_number,
1403 old_references.adm_admission_appl_number,
1404 new_references.adm_nominated_course_cd,
1405 old_references.adm_nominated_course_cd,
1406 new_references.adm_sequence_number,
1407 old_references.adm_sequence_number,
1408 new_references.last_updated_by,
1409 old_references.last_updated_by,
1410 new_references.last_update_date,
1411 old_references.last_update_date,
1412 new_references.last_date_of_attendance,
1413 old_references.last_date_of_attendance,
1414 new_references.dropped_by,
1415 old_references.dropped_by ,
1416 new_references.primary_program_type ,
1417 old_references.primary_program_type ,
1418 new_references.primary_prog_type_source ,
1419 old_references.primary_prog_type_source ,
1420 new_references.catalog_cal_type ,
1421 old_references.catalog_cal_type ,
1422 new_references.catalog_seq_num ,
1423 old_references.catalog_seq_num ,
1424 new_references.key_program ,
1425 old_references.key_program,
1426 new_references.override_cmpl_dt,
1427 old_references.override_cmpl_dt,
1428 new_references.manual_ovr_cmpl_dt_ind,
1429 old_references.manual_ovr_cmpl_dt_ind,
1430 new_references.coo_id,
1431 old_references.coo_id,
1432 new_references.igs_pr_class_std_id,
1433 old_references.igs_pr_class_std_id);
1434
1435
1436
1437
1438 END AfterRowUpdate4;
1439
1440 -- Trigger description :-
1441 -- "OSS_TST".trg_sca_as_iu
1442 -- AFTER INSERT OR UPDATE
1443 -- ON IGS_EN_STDNT_PS_ATT
1444 PROCEDURE Check_Constraints(
1445 Column_name IN VARCHAR2,
1446 Column_Value IN VARCHAR2
1447 ) AS
1448 /*----------------------------------------------------------------------------
1449 || Created By :
1450 || Created On :
1451 || Purpose :
1452 || Known limitations, enhancements or remarks :
1453 || Change History :
1454 || Who When What
1455 || (reverse chronological order - newest change first)
1456 || vvutukur 19-May-2002 removed upper check constraint on fee_cat column.bug#2344826.
1457 || kkillams 08-11-2002 Added check constraints for key_program,primary_program_type and
1458 || primary_prog_type_source columns. Legacy Build Bug no: 2661533
1459 ----------------------------------------------------------------------------*/
1460 BEGIN
1461 IF column_name IS NULL THEN
1462 NULL;
1463 ELSIF UPPER(column_name) = 'NOMINATED_COMPLETION_YR' THEN
1464 new_references.nominated_completion_yr := igs_ge_number.to_num(column_value);
1465 ELSIF UPPER(column_name) = 'DERIVED_COMPLETION_PERD' THEN
1466 new_references.derived_completion_perd := column_value;
1467 ELSIF UPPER(column_name) = 'DERIVED_COMPLETION_YR' THEN
1468 new_references.derived_completion_yr := igs_ge_number.to_num(column_value);
1469 ELSIF UPPER(column_name) = 'PROVISIONAL_IND' THEN
1470 new_references.provisional_ind := column_value;
1471 ELSIF UPPER(column_name) = 'STUDENT_CONFIRMED_IND' THEN
1472 new_references.student_confirmed_ind := column_value;
1473 ELSIF UPPER(column_name) = 'ADM_NOMINATED_COURSE_CD' THEN
1474 new_references.adm_nominated_course_cd := column_value;
1475 ELSIF UPPER(column_name) = 'ADVANCED_STANDING_IND' THEN
1476 new_references.advanced_standing_ind := column_value;
1477 ELSIF UPPER(column_name) = 'ATTENDANCE_MODE' THEN
1478 new_references.attendance_mode := column_value;
1479 ELSIF UPPER(column_name) = 'ATTENDANCE_TYPE' THEN
1480 new_references.attendance_type := column_value;
1481 ELSIF UPPER(column_name) = 'CAL_TYPE' THEN
1482 new_references.cal_type := column_value;
1483 ELSIF UPPER(column_name) = 'CORRESPONDENCE_CAT' THEN
1484 new_references.correspondence_cat := column_value;
1485 ELSIF UPPER(column_name) = 'COURSE_ATTEMPT_STATUS' THEN
1486 new_references.course_attempt_status := column_value;
1487 ELSIF UPPER(column_name) = 'COURSE_CD' THEN
1488 new_references.course_cd := column_value;
1489 ELSIF UPPER(column_name) = 'COURSE_RQRMNT_COMPLETE_IND' THEN
1490 new_references.course_rqrmnt_complete_ind := column_value;
1491 ELSIF UPPER(column_name) = 'DERIVED_ATT_MODE' THEN
1492 new_references.derived_att_mode := column_value;
1493 ELSIF UPPER(column_name) = 'DERIVED_ATT_TYPE' THEN
1494 new_references.derived_att_type := column_value;
1495 ELSIF UPPER(column_name) = 'DERIVED_COMPLETION_PERD' THEN
1496 new_references.derived_completion_perd := column_value;
1497 ELSIF UPPER(column_name) = 'DISCONTINUATION_REASON_CD' THEN
1498 new_references.discontinuation_reason_cd := column_value;
1499 ELSIF UPPER(column_name) = 'EXAM_LOCATION_CD' THEN
1500 new_references.exam_location_cd := column_value;
1501 ELSIF UPPER(column_name) = 'FUNDING_SOURCE' THEN
1502 new_references.funding_source := column_value;
1503 ELSIF UPPER(column_name) = 'LOCATION_CD' THEN
1504 new_references.location_cd := column_value;
1505 ELSIF UPPER(column_name) = 'NOMINATED_COMPLETION_PERD' THEN
1506 new_references.nominated_completion_perd := column_value;
1507 ELSIF UPPER(column_name) = 'PUBLISH_OUTCOMES_IND' THEN
1508 new_references.publish_outcomes_ind := column_value;
1509 ELSIF UPPER(column_name) = 'RULE_CHECK_IND' THEN
1510 new_references.rule_check_ind := column_value;
1511 ELSIF UPPER(column_name) = 'SELF_HELP_GROUP_IND' THEN
1512 new_references.self_help_group_ind := column_value;
1513 ELSIF UPPER(column_name) = 'LOCATION_CD' THEN
1514 new_references.location_cd := column_value;
1515 ELSIF UPPER(column_name) = 'WAIVE_OPTION_CHECK_IND' THEN
1516 new_references.waive_option_check_ind := column_value;
1517 ELSIF UPPER(column_name) = 'ADM_SEQUENCE_NUMBER' THEN
1518 new_references.adm_sequence_number := igs_ge_number.to_num(column_value);
1519 ELSIF UPPER(column_name) = 'S_COMPLETED_SOURCE_TYPE' THEN
1520 new_references.s_completed_source_type := column_value;
1521 ELSIF UPPER(column_name) = 'PRIMARY_PROGRAM_TYPE' THEN
1522 new_references.primary_program_type := column_value;
1523 ELSIF UPPER(column_name) = 'OVERRIDE_TIME_LIMITATION' THEN
1524 new_references.override_time_limitation := igs_ge_number.to_num(column_value);
1525 ELSIF UPPER(column_name) = 'MANUAL_OVR_CMPL_DT_IND' THEN
1526 new_references.manual_ovr_cmpl_dt_ind := column_value;
1527 ELSIF UPPER(column_name) = 'KEY_PROGRAM' THEN
1528 new_references.key_program := column_value;
1529 ELSIF UPPER(column_name) = 'PRIMARY_PROG_TYPE_SOURCE' THEN
1530 new_references.primary_prog_type_source := column_value;
1531 END IF;
1532
1533 IF UPPER(column_name) = 'KEY_PROGRAM' OR
1534 Column_name IS NULL THEN
1535 IF new_references.key_program NOT IN ( 'Y' , 'N') THEN
1536 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1537 IGS_GE_MSG_STACK.ADD;
1538 App_Exception.Raise_Exception;
1539 END IF;
1540 END IF;
1541 IF UPPER(column_name) = 'PRIMARY_PROG_TYPE_SOURCE' OR
1542 Column_name IS NULL THEN
1543 IF new_references.primary_prog_type_source NOT IN ( 'MANUAL' , 'SYSTEM') THEN
1544 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1545 IGS_GE_MSG_STACK.ADD;
1546 App_Exception.Raise_Exception;
1547 END IF;
1548 END IF;
1549 IF UPPER(column_name) = 'PRIMARY_PROGRAM_TYPE' OR
1550 Column_name IS NULL THEN
1551 IF new_references.primary_program_type NOT IN ( 'PRIMARY' , 'SECONDARY') THEN
1552 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1553 IGS_GE_MSG_STACK.ADD;
1554 App_Exception.Raise_Exception;
1555 END IF;
1556 END IF;
1557
1558 IF UPPER(column_name) = 'NOMINATED_COMPLETION_YR' OR
1559 Column_name IS NULL THEN
1560 IF new_references.nominated_completion_yr NOT BETWEEN 0 AND 9999 THEN
1561 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1562 IGS_GE_MSG_STACK.ADD;
1563 App_Exception.Raise_Exception;
1564 END IF;
1565 END IF;
1566 IF UPPER(column_name) = 'DERIVED_COMPLETION_PERD' OR
1567 Column_name IS NULL THEN
1568 IF new_references.derived_completion_perd NOT IN ( 'M' , 'E' , 'S' ) THEN
1569 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1570 IGS_GE_MSG_STACK.ADD;
1571 App_Exception.Raise_Exception;
1572 END IF;
1573 END IF;
1574 IF UPPER(column_name) = 'DERIVED_COMPLETION_YR' OR
1575 Column_name IS NULL THEN
1576 IF new_references.derived_completion_yr NOT BETWEEN 0 AND 9999 THEN
1577 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1578 IGS_GE_MSG_STACK.ADD;
1579 App_Exception.Raise_Exception;
1580 END IF;
1581 END IF;
1582 IF UPPER(column_name) = 'PROVISIONAL_IND' OR
1583 Column_name IS NULL THEN
1584 IF new_references.provisional_ind NOT IN ( 'Y' , 'N' ) THEN
1585 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1586 IGS_GE_MSG_STACK.ADD;
1587 App_Exception.Raise_Exception;
1588 END IF;
1589 END IF;
1590 IF UPPER(column_name) = 'STUDENT_CONFIRMED_IND' OR
1591 Column_name IS NULL THEN
1592 IF new_references.student_confirmed_ind NOT IN ( 'Y' , 'N' ) THEN
1593 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1594 IGS_GE_MSG_STACK.ADD;
1595 App_Exception.Raise_Exception;
1596 END IF;
1597 END IF;
1598 IF UPPER(column_name) = 'ADM_NOMINATED_COURSE_CD' OR
1599 Column_name IS NULL THEN
1600 IF new_references.adm_nominated_course_cd <>
1601 UPPER(new_references.adm_nominated_course_cd) THEN
1602 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1603 IGS_GE_MSG_STACK.ADD;
1604 App_Exception.Raise_Exception;
1605 END IF;
1606 END IF;
1607 IF UPPER(column_name) = 'ADVANCED_STANDING_IND' OR
1608 Column_name IS NULL THEN
1609 IF new_references.advanced_standing_ind NOT IN ( 'Y' , 'N' ) THEN
1610 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1611 IGS_GE_MSG_STACK.ADD;
1612 App_Exception.Raise_Exception;
1613 END IF;
1614 END IF;
1615 IF UPPER(column_name) = 'ATTENDANCE_MODE' OR
1616 Column_name IS NULL THEN
1617 IF new_references.attendance_mode <>
1618 UPPER(new_references.attendance_mode) THEN
1619 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1620 IGS_GE_MSG_STACK.ADD;
1621 App_Exception.Raise_Exception;
1622 END IF;
1623 END IF;
1624 IF UPPER(column_name) = 'ATTENDANCE_TYPE' OR
1625 Column_name IS NULL THEN
1626 IF new_references.attendance_type <>
1627 UPPER(new_references.attendance_type) THEN
1628 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1629 IGS_GE_MSG_STACK.ADD;
1630 App_Exception.Raise_Exception;
1631 END IF;
1632 END IF;
1633 IF UPPER(column_name) = 'CAL_TYPE' OR
1634 Column_name IS NULL THEN
1635 IF new_references.cal_type <>
1636 UPPER(new_references.cal_type) THEN
1637 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1638 IGS_GE_MSG_STACK.ADD;
1639 App_Exception.Raise_Exception;
1640 END IF;
1641 END IF;
1642 IF UPPER(column_name) = 'CORRESPONDENCE_CAT' OR
1643 Column_name IS NULL THEN
1644 IF new_references.correspondence_cat <>
1645 UPPER(new_references.correspondence_cat) THEN
1646 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1647 IGS_GE_MSG_STACK.ADD;
1648 App_Exception.Raise_Exception;
1649 END IF;
1650 END IF;
1651 IF UPPER(column_name) = 'COURSE_ATTEMPT_STATUS' OR
1652 Column_name IS NULL THEN
1653 IF new_references.course_attempt_status <>
1654 UPPER(new_references.course_attempt_status) THEN
1655 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1656 IGS_GE_MSG_STACK.ADD;
1657 App_Exception.Raise_Exception;
1658 END IF;
1659 END IF;
1660 IF UPPER(column_name) = 'COURSE_CD' OR
1661 Column_name IS NULL THEN
1662 IF new_references.course_cd <>
1663 UPPER(new_references.course_cd) THEN
1664 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1665 IGS_GE_MSG_STACK.ADD;
1666 App_Exception.Raise_Exception;
1667 END IF;
1668 END IF;
1669 IF UPPER(column_name) = 'COURSE_RQRMNT_COMPLETE_IND' OR
1670 Column_name IS NULL THEN
1671 IF new_references.course_rqrmnt_complete_ind NOT IN ( 'Y' , 'N' ) THEN
1672 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1673 IGS_GE_MSG_STACK.ADD;
1674 App_Exception.Raise_Exception;
1675 END IF;
1676 END IF;
1677 IF UPPER(column_name) = 'DERIVED_ATT_MODE' OR
1678 Column_name IS NULL THEN
1679 IF new_references.derived_att_mode <>
1680 UPPER(new_references.derived_att_mode) THEN
1681 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1682 IGS_GE_MSG_STACK.ADD;
1683 App_Exception.Raise_Exception;
1684 END IF;
1685 END IF;
1686 IF UPPER(column_name) = 'DERIVED_ATT_TYPE' OR
1687 Column_name IS NULL THEN
1688 IF new_references.derived_att_type <>
1689 UPPER(new_references.derived_att_type) THEN
1690 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1691 IGS_GE_MSG_STACK.ADD;
1692 App_Exception.Raise_Exception;
1693 END IF;
1694 END IF;
1695 IF UPPER(column_name) = 'DERIVED_COMPLETION_PERD' OR
1696 Column_name IS NULL THEN
1697 IF new_references.derived_completion_perd <>
1698 UPPER(new_references.derived_completion_perd) THEN
1699 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1700 IGS_GE_MSG_STACK.ADD;
1701 App_Exception.Raise_Exception;
1702 END IF;
1703 END IF;
1704 IF UPPER(column_name) = 'DISCONTINUATION_REASON_CD' OR
1705 Column_name IS NULL THEN
1706 IF new_references.discontinuation_reason_cd <>
1707 UPPER(new_references.discontinuation_reason_cd) THEN
1708 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1709 IGS_GE_MSG_STACK.ADD;
1710 App_Exception.Raise_Exception;
1711 END IF;
1712 END IF;
1713 IF UPPER(column_name) = 'EXAM_LOCATION_CD' OR
1714 Column_name IS NULL THEN
1715 IF new_references.exam_location_cd <>
1716 UPPER(new_references.exam_location_cd) THEN
1717 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1718 IGS_GE_MSG_STACK.ADD;
1719 App_Exception.Raise_Exception;
1720 END IF;
1721 END IF;
1722 IF UPPER(column_name) = 'FUNDING_SOURCE' OR
1723 Column_name IS NULL THEN
1724 IF new_references.funding_source <>
1725 UPPER(new_references.funding_source) THEN
1726 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1727 IGS_GE_MSG_STACK.ADD;
1728 App_Exception.Raise_Exception;
1729 END IF;
1730 END IF;
1731 IF UPPER(column_name) = 'LOCATION_CD' OR
1732 Column_name IS NULL THEN
1733 IF new_references.location_cd <>
1734 UPPER(new_references.location_cd) THEN
1735 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1736 IGS_GE_MSG_STACK.ADD;
1737 App_Exception.Raise_Exception;
1738 END IF;
1739 END IF;
1740 /* IF UPPER(column_name) = 'NOMINATED_COMPLETION_PERD' OR
1741 Column_name IS NULL THEN
1742 IF new_references.nominated_completion_perd NOT IN ( 'M' , 'E' , 'S' ) THEN
1743 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1744 IGS_GE_MSG_STACK.ADD;
1745 App_Exception.Raise_Exception;
1746 END IF;
1747 END IF; */
1748 IF UPPER(column_name) = 'PUBLISH_OUTCOMES_IND' OR
1749 Column_name IS NULL THEN
1750 IF new_references.publish_outcomes_ind NOT IN ( 'Y' , 'N' ) THEN
1751 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1752 IGS_GE_MSG_STACK.ADD;
1753 App_Exception.Raise_Exception;
1754 END IF;
1755 END IF;
1756 IF UPPER(column_name) = 'RULE_CHECK_IND' OR
1757 Column_name IS NULL THEN
1758 IF new_references.rule_check_ind NOT IN ( 'Y' , 'N' ) THEN
1759 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1760 IGS_GE_MSG_STACK.ADD;
1761 App_Exception.Raise_Exception;
1762 END IF;
1763 END IF;
1764 IF UPPER(column_name) = 'SELF_HELP_GROUP_IND' OR
1765 Column_name IS NULL THEN
1766 IF new_references.self_help_group_ind NOT IN ( 'Y' , 'N' ) THEN
1767 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1768 IGS_GE_MSG_STACK.ADD;
1769 App_Exception.Raise_Exception;
1770 END IF;
1771 END IF;
1772 IF UPPER(column_name) = 'LOCATION_CD' OR
1773 Column_name IS NULL THEN
1774 IF new_references.location_cd <>
1775 UPPER(new_references.location_cd) THEN
1776 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1777 IGS_GE_MSG_STACK.ADD;
1778 App_Exception.Raise_Exception;
1779 END IF;
1780 END IF;
1781 IF UPPER(column_name) = 'WAIVE_OPTION_CHECK_IND' OR
1782 Column_name IS NULL THEN
1783 IF new_references.waive_option_check_ind NOT IN ( 'Y' , 'N' ) THEN
1784 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1785 IGS_GE_MSG_STACK.ADD;
1786 App_Exception.Raise_Exception;
1787 END IF;
1788 END IF;
1789 IF UPPER(column_name) = 'ADM_SEQUENCE_NUMBER' OR
1790 Column_name IS NULL THEN
1791 IF new_references.adm_sequence_number NOT BETWEEN 1
1792 AND 999999 THEN
1793 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1794 IGS_GE_MSG_STACK.ADD;
1795 App_Exception.Raise_Exception;
1796 END IF;
1797 END IF;
1798 IF UPPER(column_name) = 'S_COMPLETED_SOURCE_TYPE ' OR
1799 Column_name IS NULL THEN
1800 IF new_references.s_completed_source_type NOT IN ( 'MANUAL' , 'SYSTEM') THEN
1801 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1802 IGS_GE_MSG_STACK.ADD;
1803 App_Exception.Raise_Exception;
1804 END IF;
1805 END IF;
1806 IF UPPER(column_name) = 'OVERRIDE_TIME_LIMITATION' OR
1807 Column_name IS NULL THEN
1808 IF new_references.override_time_limitation NOT BETWEEN 0 AND 9999 THEN
1809 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1810 IGS_GE_MSG_STACK.ADD;
1811 App_Exception.Raise_Exception;
1812 END IF;
1813 END IF;
1814 IF UPPER(column_name) = 'MANUAL_OVR_CMPL_DT_IND' OR
1815 Column_name IS NULL THEN
1816 IF new_references.manual_ovr_cmpl_dt_ind NOT IN ( 'Y' , 'N' ) THEN
1817 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
1818 IGS_GE_MSG_STACK.ADD;
1819 App_Exception.Raise_Exception;
1820 END IF;
1821 END IF;
1822
1823 END Check_Constraints;
1824
1825 PROCEDURE Check_Parent_Existance AS
1826 /*----------------------------------------------------------------------------
1827 || Created By :
1828 || Created On :
1829 || Purpose :
1830 || Known limitations, enhancements or remarks :
1831 || Change History :
1832 || Who When What
1833 || amuthu 07-JAN-03 Added check for Nominated Completion Period
1834 ----------------------------------------------------------------------------*/
1835 BEGIN
1836 IF (((old_references.progression_status = new_references.progression_status)) OR
1837 ((new_references.progression_status IS NULL))) THEN
1838 NULL;
1839 ELSE
1840 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
1841 'PROGRESSION_STATUS',
1842 new_references.progression_status
1843 ) THEN
1844 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1845 IGS_GE_MSG_STACK.ADD;
1846 App_Exception.Raise_Exception;
1847 END IF;
1848 END IF;
1849 IF (((old_references.derived_att_mode = new_references.derived_att_mode)) OR
1850 ((new_references.derived_att_mode IS NULL))) THEN
1851 NULL;
1852 ELSE
1853 IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
1854 new_references.derived_att_mode
1855 ) THEN
1856 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1857 IGS_GE_MSG_STACK.ADD;
1858 App_Exception.Raise_Exception;
1859 END IF;
1860 END IF;
1861 IF (((old_references.course_attempt_status = new_references.course_attempt_status)) OR
1862 ((new_references.course_attempt_status IS NULL))) THEN
1863 NULL;
1864 ELSE
1865 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
1866 'CRS_ATTEMPT_STATUS',
1867 new_references.course_attempt_status
1868 ) THEN
1869 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1870 IGS_GE_MSG_STACK.ADD;
1871 App_Exception.Raise_Exception;
1872 END IF;
1873 END IF;
1874 IF (((old_references.person_id = new_references.person_id) AND
1875 (old_references.adm_admission_appl_number = new_references.adm_admission_appl_number) AND
1876 (old_references.adm_nominated_course_cd = new_references.adm_nominated_course_cd) AND
1877 (old_references.adm_sequence_number = new_references.adm_sequence_number)) OR
1878 ((new_references.person_id IS NULL) OR
1879 (new_references.adm_admission_appl_number IS NULL) OR
1880 (new_references.adm_nominated_course_cd IS NULL) OR
1881 (new_references.adm_sequence_number IS NULL))) THEN
1882 NULL;
1883 ELSE
1884 IF NOT IGS_AD_PS_APPL_INST_PKG.Get_PK_For_Validation (
1885 new_references.person_id,
1886 new_references.adm_admission_appl_number,
1887 new_references.adm_nominated_course_cd,
1888 new_references.adm_sequence_number
1889 ) THEN
1890 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1891 IGS_GE_MSG_STACK.ADD;
1892 App_Exception.Raise_Exception;
1893 END IF;
1894 END IF;
1895 IF (((old_references.derived_att_type = new_references.derived_att_type)) OR
1896 ((new_references.derived_att_type IS NULL))) THEN
1897 NULL;
1898 ELSE
1899 IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
1900 new_references.derived_att_type
1901 ) THEN
1902 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1903 IGS_GE_MSG_STACK.ADD;
1904 App_Exception.Raise_Exception;
1905 END IF ;
1906 END IF;
1907
1908
1909 IF (((old_references.correspondence_cat = new_references.correspondence_cat)) OR
1910 ((new_references.correspondence_cat IS NULL))) THEN
1911 NULL;
1912 ELSE
1913 IF NOT IGS_CO_CAT_PKG.Get_PK_For_Validation (
1914 new_references.correspondence_cat
1915 ) THEN
1916 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1917 IGS_GE_MSG_STACK.ADD;
1918 App_Exception.Raise_Exception;
1919 END IF;
1920 END IF;
1921
1922
1923 IF (((old_references.course_cd = new_references.course_cd) AND
1924 (old_references.version_number = new_references.version_number) AND
1925 (old_references.cal_type = new_references.cal_type) AND
1926 (old_references.location_cd = new_references.location_cd) AND
1927 (old_references.attendance_mode = new_references.attendance_mode) AND
1928 (old_references.attendance_type = new_references.attendance_type)) OR
1929 ((new_references.course_cd IS NULL) OR
1930 (new_references.version_number IS NULL) OR
1931 (new_references.cal_type IS NULL) OR
1932 (new_references.location_cd IS NULL) OR
1933 (new_references.attendance_mode IS NULL) OR
1934 (new_references.attendance_type IS NULL))) THEN
1935 NULL;
1936 ELSE
1937
1938 IF NOT IGS_PS_OFR_OPT_PKG.Get_PK_For_Validation (
1939 new_references.course_cd,
1940 new_references.version_number,
1941 new_references.cal_type,
1942 new_references.location_cd,
1943 new_references.attendance_mode,
1944 new_references.attendance_type
1945 ) THEN
1946
1947 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1948 IGS_GE_MSG_STACK.ADD;
1949 App_Exception.Raise_Exception;
1950 END IF;
1951
1952 END IF;
1953
1954 -- prgoyal 21st May Bug 2383185
1955 IF (((old_references.coo_id = new_references.coo_id)) OR
1956 ((new_references.coo_id IS NULL))) THEN
1957 NULL;
1958 ELSE
1959 IF NOT IGS_PS_OFR_OPT_PKG.Get_UK_For_Validation (
1960 new_references.coo_id
1961 ) THEN
1962 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1963 IGS_GE_MSG_STACK.ADD;
1964 App_Exception.Raise_Exception;
1965 END IF;
1966 END IF;
1967
1968 IF (((old_references.discontinuation_reason_cd = new_references.discontinuation_reason_cd)) OR
1969 ((new_references.discontinuation_reason_cd IS NULL))) THEN
1970 NULL;
1971 ELSE
1972 IF NOT IGS_EN_DCNT_REASONCD_PKG.Get_PK_For_Validation (
1973 new_references.discontinuation_reason_cd
1974 ) THEN
1975 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1976 IGS_GE_MSG_STACK.ADD;
1977 App_Exception.Raise_Exception;
1978 END IF;
1979 END IF;
1980 IF (((old_references.fee_cat = new_references.fee_cat)) OR
1981 ((new_references.fee_cat IS NULL))) THEN
1982 NULL;
1983 ELSE
1984 IF NOT IGS_FI_FEE_CAT_PKG.Get_PK_For_Validation (
1985 new_references.fee_cat
1986 ) THEN
1987 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
1988 IGS_GE_MSG_STACK.ADD;
1989 App_Exception.Raise_Exception;
1990 END IF ;
1991 END IF;
1992 IF (((old_references.funding_source = new_references.funding_source)) OR
1993 ((new_references.funding_source IS NULL))) THEN
1994 NULL;
1995 ELSE
1996 IF NOT IGS_FI_FUND_SRC_PKG.Get_PK_For_Validation (
1997 new_references.funding_source
1998 ) THEN
1999 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2000 IGS_GE_MSG_STACK.ADD;
2001 App_Exception.Raise_Exception;
2002 END IF;
2003 END IF;
2004 IF (((old_references.exam_location_cd = new_references.exam_location_cd)) OR
2005 ((new_references.exam_location_cd IS NULL))) THEN
2006 NULL;
2007 ELSE
2008 IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
2009 new_references.exam_location_cd,
2010 'N'
2011 ) THEN
2012 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2013 IGS_GE_MSG_STACK.ADD;
2014 App_Exception.Raise_Exception;
2015 END IF;
2016 END IF;
2017 IF (((old_references.person_id = new_references.person_id)) OR
2018 ((new_references.person_id IS NULL))) THEN
2019 NULL;
2020 ELSE
2021 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
2022 new_references.person_id
2023 ) THEN
2024 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2025 IGS_GE_MSG_STACK.ADD;
2026 App_Exception.Raise_Exception;
2027 END IF;
2028 END IF;
2029
2030 IF ((old_references.IGS_PR_CLASS_STD_ID = new_references.IGS_PR_CLASS_STD_ID)OR
2031 (new_references.IGS_PR_CLASS_STD_ID IS NULL)) THEN
2032 NULL;
2033 ELSE
2034 IF NOT IGS_PR_CLASS_STD_PKG.Get_PK_For_Validation (
2035 new_references.IGS_PR_CLASS_STD_ID
2036 ) THEN
2037 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2038 IGS_GE_MSG_STACK.ADD;
2039 App_Exception.Raise_Exception;
2040 END IF ;
2041 END IF;
2042
2043 IF ((old_references.NOMINATED_COMPLETION_PERD = new_references.NOMINATED_COMPLETION_PERD)OR
2044 (new_references.NOMINATED_COMPLETION_PERD IS NULL)) THEN
2045 NULL;
2046 ELSE
2047 IF NOT IGS_EN_NOM_CMPL_PRD_PKG.Get_PK_For_Validation (
2048 new_references.NOMINATED_COMPLETION_PERD
2049 ) THEN
2050 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2051 IGS_GE_MSG_STACK.ADD;
2052 App_Exception.Raise_Exception;
2053 END IF ;
2054 END IF;
2055
2056 IF (((old_references.PRIMARY_PROG_TYPE_SOURCE = new_references.PRIMARY_PROG_TYPE_SOURCE)) OR
2057 ((new_references.PRIMARY_PROG_TYPE_SOURCE IS NULL))) THEN
2058 NULL;
2059 ELSE
2060 IF NOT IGS_LOOKUPS_VIEW_Pkg.Get_PK_For_Validation (
2061 'IGS_EN_PP_SOURCE',
2062 new_references.PRIMARY_PROG_TYPE_SOURCE
2063 ) THEN
2064 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
2065 IGS_GE_MSG_STACK.ADD;
2066 App_Exception.Raise_Exception;
2067 END IF;
2068 END IF;
2069
2070 IF (((old_references.CATALOG_CAL_TYPE = new_references.CATALOG_CAL_TYPE) AND
2071 (old_references.CATALOG_SEQ_NUM = new_references.CATALOG_SEQ_NUM)) OR
2072 ((new_references.CATALOG_CAL_TYPE IS NULL) OR
2073 (new_references.CATALOG_SEQ_NUM IS NULL))) THEN
2074 NULL;
2075 ELSIF NOT Igs_Ca_Inst_Pkg.Get_PK_For_Validation (
2076 new_references.CATALOG_CAL_TYPE,
2077 new_references.CATALOG_SEQ_NUM
2078 ) THEN
2079 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
2080 IGS_GE_MSG_STACK.ADD;
2081 App_Exception.Raise_Exception;
2082 END IF;
2083
2084 END Check_Parent_Existance;
2085
2086
2087 PROCEDURE Check_Child_Existance AS
2088 ------------------------------------------------------------------
2089 --Change History:
2090 --Who When What
2091 --vchappid 25-Apr-2002 Bug#2329407, removed the call to IGS_FI_DSB_DTL_ALC_PKG.GET_FK_IGS_EN_STDNT_PS_ATT
2092 -- as table IGS_FI_DSB_DTL_ALC is obsolete in the current fix
2093 --smadathi 22-Jan-2002 Bug No. 2170429.Removed references to IGS_FI_CRS_AT_F_SPN_PKG.GET_FK_IGS_EN_STDNT_PS_ATT
2094 -- call as part of sponsorship DLD
2095 --pmarada 13-feb-2002 Added IGS_HE_ST_SPA_ALL_PKG.GET_UFK_IGS_EN_ST_PS_ATT_AL for HESA requirment.
2096 --nmankodi 04-Nov-2002 Added for Posting External Results TD
2097 --ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_EN_STDNT_PS_ATT for Term Records Build
2098 -------------------------------------------------------------------
2099
2100
2101 BEGIN
2102 IGS_AD_PS_APPL_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2103 old_references.person_id,
2104 old_references.course_cd
2105 );
2106 IGS_RE_CANDIDATURE_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2107 old_references.person_id,
2108 old_references.course_cd
2109 );
2110 IGS_FI_FEE_AS_RT_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2111 old_references.person_id,
2112 old_references.course_cd
2113 );
2114 IGS_GR_GRADUAND_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2115 old_references.person_id,
2116 old_references.course_cd
2117 );
2118 IGS_PR_RU_APPL_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2119 old_references.person_id,
2120 old_references.course_cd
2121 );
2122 IGS_GR_SPECIAL_AWARD_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2123 old_references.person_id,
2124 old_references.course_cd
2125 );
2126 IGS_EN_STDNTPSHECSOP_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2127 old_references.person_id,
2128 old_references.course_cd
2129 );
2130 IGS_EN_STDNT_PS_INTM_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2131 old_references.person_id,
2132 old_references.course_cd
2133 );
2134 IGS_PS_STDNT_TRN_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2135 old_references.person_id,
2136 old_references.course_cd
2137 );
2138 IGS_PS_STDNT_APV_ALT_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2139 old_references.person_id,
2140 old_references.course_cd
2141 );
2142 IGS_AS_SC_ATMPT_ENR_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2143 old_references.person_id,
2144 old_references.course_cd
2145 );
2146 IGS_AS_SC_ATMPT_NOTE_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2147 old_references.person_id,
2148 old_references.course_cd
2149 );
2150 IGS_PR_SDT_PS_PR_MSR_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2151 old_references.person_id,
2152 old_references.course_cd
2153 );
2154 IGS_PS_STDNT_SPL_REQ_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2155 old_references.person_id,
2156 old_references.course_cd
2157 );
2158 IGS_PR_STDNT_PR_CK_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2159 old_references.person_id,
2160 old_references.course_cd
2161 );
2162 IGS_EN_SU_ATTEMPT_H_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2163 old_references.person_id,
2164 old_references.course_cd
2165 );
2166 IGS_EN_SU_ATTEMPT_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2167 old_references.person_id,
2168 old_references.course_cd
2169 );
2170 IGS_AS_SU_SETATMPT_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2171 old_references.person_id,
2172 old_references.course_cd
2173 );
2174 --Added as the following one as part of Term Record Build
2175 --Bug No# 2829263
2176 IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2177 old_references.person_id,
2178 old_references.course_cd
2179 );
2180 --
2181 -- Next call added as per the UK Award Aims DLD.
2182 --
2183 igs_en_spa_awd_aim_pkg.get_fk_igs_en_stdnt_ps_att (
2184 old_references.person_id,
2185 old_references.course_cd
2186 );
2187 --
2188 -- Next call added as per the class Rank Build.
2189 -- Bug# 2639109
2190
2191 igs_pr_cohinst_rank_pkg.get_fk_igs_en_stdnt_ps_att (
2192 old_references.person_id,
2193 old_references.course_cd
2194 );
2195
2196 IGS_AS_ANON_ID_PS_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2197 old_references.person_id,
2198 old_references.course_cd
2199 );
2200
2201 IGS_AS_ANON_ID_US_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2202 old_references.person_id,
2203 old_references.course_cd
2204 );
2205
2206 -- Added the following check chaild existance for the HESA requirment, pmarada
2207 IGS_HE_ST_SPA_ALL_PKG.GET_FK_IGS_EN_STDNT_PS_ATT_ALL(
2208 old_references.person_id,
2209 old_references.course_cd);
2210
2211
2212 IGS_PR_STU_ACAD_STAT_PKG.GET_FK_IGS_EN_STDNT_PS_ATT (
2213 old_references.person_id,
2214 old_references.course_cd
2215 );
2216 IGS_EN_PLAN_UNITS_PKG.GET_FK_IGS_EN_STDNT_PS_ATT(
2217 old_references.person_id,
2218 old_references.course_cd
2219 );
2220
2221 END Check_Child_Existance;
2222
2223
2224 FUNCTION Get_PK_For_Validation (
2225 x_person_id IN NUMBER,
2226 x_course_cd IN VARCHAR2
2227 ) RETURN BOOLEAN AS
2228
2229 -- This function does the check to see whether the record for the
2230 -- mentioned PK exists. No lock is done on a record with a status
2231 -- other than that of status 'UNCONFIRM' since it is not deletable
2232 -- records with status UNCONFIRM deletable and hence they would
2233 -- need to be locked. Since this is the PK hence these columns
2234 -- cannot be updated.
2235
2236 CURSOR cur_rec_exist IS
2237 SELECT course_attempt_status
2238 FROM igs_en_stdnt_ps_att_all
2239 WHERE person_id = x_person_id
2240 AND course_cd = x_course_cd;
2241
2242 CURSOR cur_rowid IS
2243 SELECT ROWID
2244 FROM IGS_EN_STDNT_PS_ATT_ALL
2245 WHERE person_id = x_person_id
2246 AND course_cd = x_course_cd
2247 FOR UPDATE NOWAIT;
2248
2249 lv_rowid cur_rowid%ROWTYPE;
2250 lv_crs_att_status igs_en_stdnt_ps_att_all.course_attempt_status%TYPE;
2251
2252 BEGIN
2253
2254 -- Check whether a record exists for the mentioned PK and get
2255 -- ps attempt status.
2256 OPEN cur_rec_exist;
2257 FETCH cur_rec_exist INTO lv_crs_att_status;
2258
2259 IF cur_rec_exist%FOUND THEN
2260 -- in this case the record exists for the mentioned PK
2261
2262 IF lv_crs_att_status = 'UNCONFIRM' THEN
2263
2264 -- In case the status is unconfirm then a lock is
2265 -- required on the record.
2266 OPEN cur_rowid;
2267 FETCH cur_rowid INTO lv_rowid;
2268 IF (cur_rowid%FOUND) THEN
2269 CLOSE cur_rowid;
2270 CLOSE cur_rec_exist;
2271 RETURN(TRUE);
2272 ELSE
2273 CLOSE cur_rowid;
2274 CLOSE cur_rec_exist;
2275 RETURN(FALSE);
2276 END IF;
2277
2278 ELSE
2279 CLOSE cur_rec_exist;
2280 RETURN(TRUE);
2281 END IF;
2282
2283 ELSE
2284 CLOSE cur_rec_exist;
2285 RETURN(FALSE);
2286 END IF;
2287
2288 END Get_PK_For_Validation;
2289
2290
2291 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW_Prog (
2292 x_progression_status IN VARCHAR2
2293 ) AS
2294 CURSOR cur_rowid IS
2295 SELECT ROWID
2296 FROM IGS_EN_STDNT_PS_ATT_ALL
2297 WHERE progression_status = x_progression_status ;
2298 lv_rowid cur_rowid%ROWTYPE;
2299 BEGIN
2300 OPEN cur_rowid;
2301 FETCH cur_rowid INTO lv_rowid;
2302 IF (cur_rowid%FOUND) THEN
2303 CLOSE cur_rowid;
2304 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_LKUPV_FK');
2305 IGS_GE_MSG_STACK.ADD;
2306 App_Exception.Raise_Exception;
2307 RETURN;
2308 END IF;
2309 CLOSE cur_rowid;
2310 END GET_FK_IGS_LOOKUPS_VIEW_Prog;
2311
2312 PROCEDURE GET_FK_IGS_EN_ATD_MODE (
2313 x_attendance_mode IN VARCHAR2
2314 ) AS
2315 CURSOR cur_rowid IS
2316 SELECT ROWID
2317 FROM IGS_EN_STDNT_PS_ATT_ALL
2318 WHERE derived_att_mode = x_attendance_mode ;
2319 lv_rowid cur_rowid%ROWTYPE;
2320 BEGIN
2321 OPEN cur_rowid;
2322 FETCH cur_rowid INTO lv_rowid;
2323 IF (cur_rowid%FOUND) THEN
2324 CLOSE cur_rowid;
2325 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_AM_FK');
2326 IGS_GE_MSG_STACK.ADD;
2327 App_Exception.Raise_Exception;
2328 RETURN;
2329 END IF;
2330 CLOSE cur_rowid;
2331 END GET_FK_IGS_EN_ATD_MODE;
2332
2333 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW_CAS (
2334 x_course_attempt_status IN VARCHAR2
2335 ) AS
2336 CURSOR cur_rowid IS
2337 SELECT ROWID
2338 FROM IGS_EN_STDNT_PS_ATT_ALL
2339 WHERE course_attempt_status = x_course_attempt_status ;
2340 lv_rowid cur_rowid%ROWTYPE;
2341 BEGIN
2342 OPEN cur_rowid;
2343 FETCH cur_rowid INTO lv_rowid;
2344 IF (cur_rowid%FOUND) THEN
2345 CLOSE cur_rowid;
2346 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_LKUPV_FK');
2347 IGS_GE_MSG_STACK.ADD;
2348 App_Exception.Raise_Exception;
2349 RETURN;
2350 END IF;
2351 CLOSE cur_rowid;
2352 END GET_FK_IGS_LOOKUPS_VIEW_CAS;
2353
2354 PROCEDURE GET_FK_IGS_AD_PS_APPL_INST (
2355 x_person_id IN NUMBER,
2356 x_admission_appl_number IN NUMBER,
2357 x_nominated_course_cd IN VARCHAR2,
2358 x_sequence_number IN NUMBER
2359 ) AS
2360 CURSOR cur_rowid IS
2361 SELECT ROWID
2362 FROM IGS_EN_STDNT_PS_ATT_ALL
2363 WHERE person_id = x_person_id
2364 AND adm_admission_appl_number = x_admission_appl_number
2365 AND adm_nominated_course_cd = x_nominated_course_cd
2366 AND adm_sequence_number = x_sequence_number ;
2367 lv_rowid cur_rowid%ROWTYPE;
2368 BEGIN
2369 OPEN cur_rowid;
2370 FETCH cur_rowid INTO lv_rowid;
2371 IF (cur_rowid%FOUND) THEN
2372 CLOSE cur_rowid;
2373 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_ACAI_FK');
2374 IGS_GE_MSG_STACK.ADD;
2375 App_Exception.Raise_Exception;
2376 RETURN;
2377 END IF;
2378 CLOSE cur_rowid;
2379 END GET_FK_IGS_AD_PS_APPL_INST;
2380
2381 PROCEDURE GET_FK_IGS_EN_ATD_TYPE (
2382 x_attendance_type IN VARCHAR2
2383 ) AS
2384 CURSOR cur_rowid IS
2385 SELECT ROWID
2386 FROM IGS_EN_STDNT_PS_ATT_ALL
2387 WHERE derived_att_type = x_attendance_type ;
2388 lv_rowid cur_rowid%ROWTYPE;
2389 BEGIN
2390 OPEN cur_rowid;
2391 FETCH cur_rowid INTO lv_rowid;
2392 IF (cur_rowid%FOUND) THEN
2393 CLOSE cur_rowid;
2394 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_ATT_FK');
2395 IGS_GE_MSG_STACK.ADD;
2396 App_Exception.Raise_Exception;
2397 RETURN;
2398 END IF;
2399 CLOSE cur_rowid;
2400 END GET_FK_IGS_EN_ATD_TYPE;
2401
2402 PROCEDURE GET_FK_IGS_CO_CAT (
2403 x_correspondence_cat IN VARCHAR2
2404 ) AS
2405 CURSOR cur_rowid IS
2406 SELECT ROWID
2407 FROM IGS_EN_STDNT_PS_ATT_ALL
2408 WHERE correspondence_cat = x_correspondence_cat ;
2409 lv_rowid cur_rowid%ROWTYPE;
2410 BEGIN
2411 OPEN cur_rowid;
2412 FETCH cur_rowid INTO lv_rowid;
2413 IF (cur_rowid%FOUND) THEN
2414 CLOSE cur_rowid;
2415 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_CC_FK');
2416 IGS_GE_MSG_STACK.ADD;
2417 App_Exception.Raise_Exception;
2418 RETURN;
2419 END IF;
2420 CLOSE cur_rowid;
2421 END GET_FK_IGS_CO_CAT;
2422
2423 PROCEDURE GET_FK_IGS_PS_OFR_OPT (
2424 x_course_cd IN VARCHAR2,
2425 x_version_number IN NUMBER,
2426 x_cal_type IN VARCHAR2,
2427 x_location_cd IN VARCHAR2,
2428 x_attendance_mode IN VARCHAR2,
2429 x_attendance_type IN VARCHAR2
2430 ) AS
2431 CURSOR cur_rowid IS
2432 SELECT ROWID
2433 FROM IGS_EN_STDNT_PS_ATT_ALL
2434 WHERE course_cd = x_course_cd
2435 AND version_number = x_version_number
2436 AND cal_type = x_cal_type
2437 AND location_cd = x_location_cd
2438 AND attendance_mode = x_attendance_mode
2439 AND attendance_type = x_attendance_type ;
2440 lv_rowid cur_rowid%ROWTYPE;
2441 BEGIN
2442 OPEN cur_rowid;
2443 FETCH cur_rowid INTO lv_rowid;
2444 IF (cur_rowid%FOUND) THEN
2445 CLOSE cur_rowid;
2446 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_COO_FK');
2447 IGS_GE_MSG_STACK.ADD;
2448 App_Exception.Raise_Exception;
2449 RETURN;
2450 END IF;
2451 CLOSE cur_rowid;
2452 END GET_FK_IGS_PS_OFR_OPT;
2453
2454 PROCEDURE GET_UFK_IGS_PS_OFR_OPT (
2455 x_coo_id IN NUMBER
2456 ) AS
2457 CURSOR cur_rowid IS
2458 SELECT ROWID
2459 FROM IGS_EN_STDNT_PS_ATT_ALL
2460 WHERE coo_id = x_coo_id;
2461 lv_rowid cur_rowid%ROWTYPE;
2462 BEGIN
2463 OPEN cur_rowid;
2464 FETCH cur_rowid INTO lv_rowid;
2465 IF (cur_rowid%FOUND) THEN
2466 CLOSE cur_rowid;
2467 -- code modified by Nishikant - 13JUN2002 - as per bug#2413811
2468 -- set the message IGS_EN_SCA_COO_FK instead of IGS_EN_SCA_COO_UFK
2469 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_COO_FK');
2470 IGS_GE_MSG_STACK.ADD;
2471 App_Exception.Raise_Exception;
2472 RETURN;
2473 END IF;
2474 CLOSE cur_rowid;
2475 END GET_UFK_IGS_PS_OFR_OPT;
2476
2477 PROCEDURE GET_FK_IGS_PS_COURSE (
2478 x_course_cd IN VARCHAR2
2479 ) AS
2480 CURSOR cur_rowid IS
2481 SELECT ROWID
2482 FROM IGS_EN_STDNT_PS_ATT_ALL
2483 WHERE course_cd = x_course_cd ;
2484 lv_rowid cur_rowid%ROWTYPE;
2485 BEGIN
2486 OPEN cur_rowid;
2487 FETCH cur_rowid INTO lv_rowid;
2488 IF (cur_rowid%FOUND) THEN
2489 CLOSE cur_rowid;
2490 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_CRS_FK');
2491 IGS_GE_MSG_STACK.ADD;
2492 App_Exception.Raise_Exception;
2493 RETURN;
2494 END IF;
2495 CLOSE cur_rowid;
2496 END GET_FK_IGS_PS_COURSE;
2497
2498 PROCEDURE GET_FK_IGS_PS_VER (
2499 x_course_cd IN VARCHAR2,
2500 x_version_number IN NUMBER
2501 ) AS
2502 CURSOR cur_rowid IS
2503 SELECT ROWID
2504 FROM IGS_EN_STDNT_PS_ATT_ALL
2505 WHERE course_cd = x_course_cd
2506 AND version_number = x_version_number ;
2507 lv_rowid cur_rowid%ROWTYPE;
2508 BEGIN
2509 OPEN cur_rowid;
2510 FETCH cur_rowid INTO lv_rowid;
2511 IF (cur_rowid%FOUND) THEN
2512 CLOSE cur_rowid;
2513 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_CRV_FK');
2514 IGS_GE_MSG_STACK.ADD;
2515 App_Exception.Raise_Exception;
2516 RETURN;
2517 END IF;
2518 CLOSE cur_rowid;
2519 END GET_FK_IGS_PS_VER;
2520
2521
2522 PROCEDURE GET_FK_IGS_FI_FEE_CAT (
2523 x_fee_cat IN VARCHAR2
2524 ) AS
2525 CURSOR cur_rowid IS
2526 SELECT ROWID
2527 FROM IGS_EN_STDNT_PS_ATT_ALL
2528 WHERE fee_cat = x_fee_cat ;
2529 lv_rowid cur_rowid%ROWTYPE;
2530 BEGIN
2531 OPEN cur_rowid;
2532 FETCH cur_rowid INTO lv_rowid;
2533 IF (cur_rowid%FOUND) THEN
2534 CLOSE cur_rowid;
2535 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_FC_FK');
2536 IGS_GE_MSG_STACK.ADD;
2537 App_Exception.Raise_Exception;
2538 RETURN;
2539 END IF;
2540 CLOSE cur_rowid;
2541 END GET_FK_IGS_FI_FEE_CAT;
2542
2543 PROCEDURE GET_FK_IGS_FI_FUND_SRC (
2544 x_funding_source IN VARCHAR2
2545 ) AS
2546 CURSOR cur_rowid IS
2547 SELECT ROWID
2548 FROM IGS_EN_STDNT_PS_ATT_ALL
2549 WHERE funding_source = x_funding_source ;
2550 lv_rowid cur_rowid%ROWTYPE;
2551 BEGIN
2552 OPEN cur_rowid;
2553 FETCH cur_rowid INTO lv_rowid;
2554 IF (cur_rowid%FOUND) THEN
2555 CLOSE cur_rowid;
2556 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_FS_FK');
2557 IGS_GE_MSG_STACK.ADD;
2558 App_Exception.Raise_Exception;
2559 RETURN;
2560 END IF;
2561 CLOSE cur_rowid;
2562 END GET_FK_IGS_FI_FUND_SRC;
2563
2564 PROCEDURE GET_FK_IGS_AD_LOCATION (
2565 x_location_cd IN VARCHAR2
2566 ) AS
2567 CURSOR cur_rowid IS
2568 SELECT ROWID
2569 FROM IGS_EN_STDNT_PS_ATT_ALL
2570 WHERE exam_location_cd = x_location_cd ;
2571 lv_rowid cur_rowid%ROWTYPE;
2572 BEGIN
2573 OPEN cur_rowid;
2574 FETCH cur_rowid INTO lv_rowid;
2575 IF (cur_rowid%FOUND) THEN
2576 CLOSE cur_rowid;
2577 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_LOC_FK');
2578 IGS_GE_MSG_STACK.ADD;
2579 App_Exception.Raise_Exception;
2580 RETURN;
2581 END IF;
2582 CLOSE cur_rowid;
2583 END GET_FK_IGS_AD_LOCATION;
2584
2585 PROCEDURE GET_FK_IGS_PE_PERSON (
2586 x_person_id IN NUMBER
2587 ) AS
2588 CURSOR cur_rowid IS
2589 SELECT ROWID
2590 FROM IGS_EN_STDNT_PS_ATT_ALL
2591 WHERE person_id = x_person_id ;
2592 lv_rowid cur_rowid%ROWTYPE;
2593 BEGIN
2594 OPEN cur_rowid;
2595 FETCH cur_rowid INTO lv_rowid;
2596 IF (cur_rowid%FOUND) THEN
2597 CLOSE cur_rowid;
2598 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_PE_FK');
2599 IGS_GE_MSG_STACK.ADD;
2600 App_Exception.Raise_Exception;
2601 RETURN;
2602 END IF;
2603 CLOSE cur_rowid;
2604 END GET_FK_IGS_PE_PERSON;
2605
2606 -- added as part of Enroll process Build for class standing dld
2607 -- amuthu 30-JUL-2001
2608 PROCEDURE GET_FK_IGS_PR_CLASS_STD(
2609 X_IGS_PR_CLASS_STD_ID IN NUMBER
2610 ) AS
2611 CURSOR cur_rowid IS
2612 SELECT ROWID
2613 FROM IGS_EN_STDNT_PS_ATT_ALL
2614 WHERE IGS_PR_CLASS_STD_ID = X_IGS_PR_CLASS_STD_ID;
2615
2616 lv_cur cur_rowid%ROWTYPE;
2617
2618 BEGIN
2619 OPEN cur_rowid;
2620 FETCH cur_rowid INTO lv_cur;
2621 IF (cur_rowid%FOUND) THEN
2622 CLOSE cur_rowid;
2623 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_PCS_FK');
2624 IGS_GE_MSG_STACK.ADD;
2625 App_Exception.Raise_Exception;
2626 RETURN;
2627 END IF;
2628 CLOSE cur_rowid;
2629 END GET_FK_IGS_PR_CLASS_STD;
2630
2631
2632 PROCEDURE GET_FK_IGS_CA_INST (
2633 X_CATALOG_CAL_TYPE IN VARCHAR2,
2634 X_CATALOG_SEQ_NUM IN NUMBER
2635 ) AS
2636 CURSOR cur_rowid IS
2637 SELECT ROWID
2638 FROM IGS_EN_STDNT_PS_ATT_ALL
2639 WHERE CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE
2640 AND CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM ;
2641 lv_rowid cur_rowid%ROWTYPE;
2642
2643 BEGIN
2644 OPEN cur_rowid;
2645 FETCH cur_rowid INTO lv_rowid;
2646 IF (cur_rowid%FOUND) THEN
2647 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCA_CI_FK');
2648 Igs_Ge_Msg_Stack.ADD;
2649 CLOSE cur_rowid;
2650 App_Exception.Raise_Exception;
2651 RETURN;
2652 END IF;
2653 CLOSE cur_rowid;
2654 END GET_FK_IGS_CA_INST;
2655
2656
2657 PROCEDURE Before_DML (
2658 p_action IN VARCHAR2,
2659 x_rowid IN VARCHAR2,
2660 x_org_id IN NUMBER,
2661 x_person_id IN NUMBER,
2662 x_course_cd IN VARCHAR2,
2663 x_version_number IN NUMBER,
2664 x_cal_type IN VARCHAR2,
2665 x_location_cd IN VARCHAR2,
2666 x_attendance_mode IN VARCHAR2,
2667 x_attendance_type IN VARCHAR2,
2668 x_coo_id IN NUMBER,
2669 x_student_confirmed_ind IN VARCHAR2,
2670 x_commencement_dt IN DATE,
2671 x_course_attempt_status IN VARCHAR2,
2672 x_progression_status IN VARCHAR2,
2673 x_derived_att_type IN VARCHAR2,
2674 x_derived_att_mode IN VARCHAR2,
2675 x_provisional_ind IN VARCHAR2,
2676 x_discontinued_dt IN DATE,
2677 x_discontinuation_reason_cd IN VARCHAR2,
2678 x_lapsed_dt IN DATE,
2679 x_funding_source IN VARCHAR2,
2680 x_exam_location_cd IN VARCHAR2,
2681 x_derived_completion_yr IN NUMBER,
2682 x_derived_completion_perd IN VARCHAR2,
2683 x_nominated_completion_yr IN NUMBER,
2684 x_nominated_completion_perd IN VARCHAR2,
2685 x_rule_check_ind IN VARCHAR2,
2686 x_waive_option_check_ind IN VARCHAR2,
2687 x_last_rule_check_dt IN DATE,
2688 x_publish_outcomes_ind IN VARCHAR2,
2689 x_course_rqrmnt_complete_ind IN VARCHAR2,
2690 x_course_rqrmnts_complete_dt IN DATE,
2691 x_s_completed_source_type IN VARCHAR2,
2692 x_override_time_limitation IN NUMBER,
2693 x_advanced_standing_ind IN VARCHAR2,
2694 x_fee_cat IN VARCHAR2,
2695 x_correspondence_cat IN VARCHAR2,
2696 x_self_help_group_ind IN VARCHAR2,
2697 x_logical_delete_dt IN DATE,
2698 x_adm_admission_appl_number IN NUMBER,
2699 x_adm_nominated_course_cd IN VARCHAR2,
2700 x_adm_sequence_number IN NUMBER,
2701 x_creation_date IN DATE,
2702 x_created_by IN NUMBER,
2703 x_last_update_date IN DATE,
2704 x_last_updated_by IN NUMBER,
2705 x_last_update_login IN NUMBER,
2706 x_last_date_of_attendance IN DATE,
2707 x_dropped_by IN VARCHAR2,
2708 x_igs_pr_class_std_id IN NUMBER,
2709 x_primary_program_type IN VARCHAR2,
2710 x_primary_prog_type_source IN VARCHAR2,
2711 x_catalog_cal_type IN VARCHAR2,
2712 x_catalog_seq_num IN NUMBER,
2713 x_key_program IN VARCHAR2,
2714 x_manual_ovr_cmpl_dt_ind IN VARCHAR2,
2715 x_override_cmpl_dt IN DATE,
2716 x_attribute_category IN VARCHAR2,
2717 x_attribute1 IN VARCHAR2,
2718 x_attribute2 IN VARCHAR2,
2719 x_attribute3 IN VARCHAR2,
2720 x_attribute4 IN VARCHAR2,
2721 x_attribute5 IN VARCHAR2,
2722 x_attribute6 IN VARCHAR2,
2723 x_attribute7 IN VARCHAR2,
2724 x_attribute8 IN VARCHAR2,
2725 x_attribute9 IN VARCHAR2,
2726 x_attribute10 IN VARCHAR2,
2727 x_attribute11 IN VARCHAR2,
2728 x_attribute12 IN VARCHAR2,
2729 x_attribute13 IN VARCHAR2,
2730 x_attribute14 IN VARCHAR2,
2731 x_attribute15 IN VARCHAR2,
2732 x_attribute16 IN VARCHAR2,
2733 x_attribute17 IN VARCHAR2,
2734 x_attribute18 IN VARCHAR2,
2735 x_attribute19 IN VARCHAR2,
2736 x_attribute20 IN VARCHAR2,
2737 x_future_dated_trans_flag In VARCHAR2
2738 ) AS
2739 BEGIN
2740 Set_Column_Values (
2741 p_action,
2742 x_rowid,
2743 x_org_id,
2744 x_person_id,
2745 x_course_cd,
2746 x_version_number,
2747 x_cal_type,
2748 x_location_cd,
2749 x_attendance_mode,
2750 x_attendance_type,
2751 x_coo_id,
2752 x_student_confirmed_ind,
2753 x_commencement_dt,
2754 x_course_attempt_status,
2755 x_progression_status,
2756 x_derived_att_type,
2757 x_derived_att_mode,
2758 x_provisional_ind,
2759 x_discontinued_dt,
2760 x_discontinuation_reason_cd,
2761 x_lapsed_dt,
2762 x_funding_source,
2763 x_exam_location_cd,
2764 x_derived_completion_yr,
2765 x_derived_completion_perd,
2766 x_nominated_completion_yr,
2767 x_nominated_completion_perd,
2768 x_rule_check_ind,
2769 x_waive_option_check_ind,
2770 x_last_rule_check_dt,
2771 x_publish_outcomes_ind,
2772 x_course_rqrmnt_complete_ind,
2773 x_course_rqrmnts_complete_dt,
2774 x_s_completed_source_type,
2775 x_override_time_limitation,
2776 x_advanced_standing_ind,
2777 x_fee_cat,
2778 x_correspondence_cat,
2779 x_self_help_group_ind,
2780 x_logical_delete_dt,
2781 x_adm_admission_appl_number,
2782 x_adm_nominated_course_cd,
2783 x_adm_sequence_number,
2784 x_creation_date,
2785 x_created_by,
2786 x_last_update_date,
2787 x_last_updated_by,
2788 x_last_update_login,
2789 x_last_date_of_attendance,
2790 x_dropped_by,
2791 X_IGS_PR_CLASS_STD_ID,
2792 x_primary_program_type,
2793 x_primary_prog_type_source,
2794 x_catalog_cal_type,
2795 x_catalog_seq_num,
2796 x_key_program,
2797 x_manual_ovr_cmpl_dt_ind,
2798 x_override_cmpl_dt,
2799 x_attribute_category,
2800 x_attribute1,
2801 x_attribute2,
2802 x_attribute3,
2803 x_attribute4,
2804 x_attribute5,
2805 x_attribute6,
2806 x_attribute7,
2807 x_attribute8,
2808 x_attribute9,
2809 x_attribute10,
2810 x_attribute11,
2811 x_attribute12,
2812 x_attribute13,
2813 x_attribute14,
2814 x_attribute15,
2815 x_attribute16,
2816 x_attribute17,
2817 x_attribute18,
2818 x_attribute19,
2819 x_attribute20,
2820 x_future_dated_trans_flag
2821 );
2822
2823 IF (p_action = 'INSERT') THEN
2824 -- Call all the procedures related to Before Insert.
2825 BeforeRowInsertUpdate1 ( p_inserting => TRUE,
2826 p_updating => FALSE,
2827 p_deleting => FALSE);
2828
2829 BeforeRowInsertUpdateDelete2 ( p_inserting => TRUE,
2830 p_updating => FALSE,
2831 p_deleting => FALSE);
2832 IF Get_PK_For_Validation(
2833 new_references.person_id ,
2834 new_references.course_cd
2835 ) THEN
2836 FND_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
2837 IGS_GE_MSG_STACK.ADD;
2838 App_Exception.Raise_Exception;
2839 END IF;
2840 Check_Constraints;
2841 Check_Parent_Existance;
2842 ELSIF (p_action = 'UPDATE') THEN
2843 -- Call all the procedures related to Before Update.
2844
2845 BeforeRowInsertUpdate1 ( p_inserting => FALSE,
2846 p_updating => TRUE,
2847 p_deleting => FALSE);
2848
2849 BeforeRowInsertUpdateDelete2( p_inserting => FALSE,
2850 p_updating => TRUE,
2851 p_deleting => FALSE);
2852
2853 Check_Constraints;
2854 Check_Parent_Existance;
2855 ELSIF (p_action = 'DELETE') THEN
2856 -- Call all the procedures related to Before Delete.
2857 beforerowdelete;
2858 BeforeRowInsertUpdateDelete2 ( p_inserting => FALSE,
2859 p_updating => FALSE,
2860 p_deleting => TRUE);
2861 Check_Child_Existance;
2862 ELSIF (p_action = 'VALIDATE_INSERT') THEN
2863 IF Get_PK_For_Validation(
2864 new_references.person_id ,
2865 new_references.course_cd
2866 ) THEN
2867 FND_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
2868 IGS_GE_MSG_STACK.ADD;
2869 App_Exception.Raise_Exception;
2870 END IF;
2871 Check_constraints;
2872 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
2873 Check_constraints;
2874 ELSIF (p_action = 'VALIDATE_DELETE') THEN
2875 Check_Child_Existance;
2876 END IF;
2877
2878 -- call the before dml of igs_en_career_model package only if the global variable skip_before_after_dml
2879 -- is false . This variable will be made true in the after dml procedure of igs_en_career_model package
2880 -- to avoid calling before and after dml procedures during recursion of UPDATE_ROW call in the career package
2881 -- donot remove this condition ( included in career_impcat dld)
2882 IF (p_action in ('INSERT','UPDATE')) AND (NOT IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml) THEN
2883
2884
2885 IGS_EN_CAREER_MODEL.SCA_TBH_BEFORE_DML(
2886 p_person_id => new_references.person_id,
2887 p_course_cd => new_references.course_cd,
2888 p_version_number => new_references.version_number,
2889 p_old_course_attempt_status => old_references.course_attempt_status ,
2890 p_new_course_attempt_status => new_references.course_attempt_status ,
2891 p_primary_program_type => new_references.primary_program_type,
2892 p_primary_prog_type_source => new_references.primary_prog_type_source,
2893 p_new_key_program => new_references.key_program
2894 );
2895
2896 END IF;
2897
2898 END Before_DML;
2899
2900
2901 PROCEDURE AFTER_DML_RECURSIVE_CALLS(p_old_references IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE,
2902 p_new_references IGS_EN_STDNT_PS_ATT_ALL%ROWTYPE,
2903 p_action VARCHAR2,
2904 p_skip_before_after_dml BOOLEAN DEFAULT FALSE) IS
2905
2906 CURSOR cur_fut_dt_trnsf(cp_person_id IGS_PS_STDNT_TRN.person_id%TYPE,
2907 cp_program_cd IGS_PS_STDNT_TRN.transfer_course_cd%TYPE) IS
2908 SELECT sct.course_cd,sct.effective_term_cal_type,sct.effective_term_sequence_num
2909 FROM IGS_PS_STDNT_TRN sct, IGS_EN_STDNT_PS_ATT sca
2910 WHERE sct.person_id = cp_person_id
2911 AND sct.transfer_course_cd = cp_program_cd
2912 AND sct.course_cd = sca.course_cd
2913 AND sct.person_id = sca.person_id
2914 AND sca.future_dated_trans_flag = 'Y';
2915
2916 v_eff_term_cal_type IGS_PS_STDNT_TRN.effective_term_cal_type%TYPE;
2917 v_eff_term_seq_num IGS_PS_STDNT_TRN.effective_term_sequence_num%TYPE;
2918 v_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE;
2919 v_errbuf VARCHAR2(20);
2920
2921 BEGIN
2922
2923 IF p_action = 'UPDATE' THEN
2924
2925 -- When a program attempt is intermitted/discontinued/lapsed/unconfirmed and it is the source program for
2926 -- future dated program transfer then the future dated program transfer must be deleted
2927 IF (p_old_references.course_attempt_status IN ('ENROLLED','INACTIVE') AND
2928 p_new_references.course_attempt_status IN ('INTERMIT','DISCONTIN','LAPSED','UNCONFIRM')) THEN
2929
2930 OPEN cur_fut_dt_trnsf(p_new_references.person_id, p_new_references.course_cd);
2931
2932 FETCH cur_fut_dt_trnsf INTO v_course_cd,v_eff_term_cal_type,v_eff_term_seq_num;
2933
2934 IF cur_fut_dt_trnsf%FOUND THEN
2935
2936 IGS_EN_FUTURE_DT_TRANS.cleanup_dest_program (
2937 p_new_references.person_id,
2938 v_course_cd,
2939 v_eff_term_cal_type,
2940 v_eff_term_seq_num,
2941 'CLEANUP'
2942 );
2943
2944 IGS_EN_FUTURE_DT_TRANS.cleanup_dest_program (
2945 p_new_references.person_id,
2946 v_course_cd,
2947 v_eff_term_cal_type,
2948 v_eff_term_seq_num,
2949 'DELETE'
2950 );
2951
2952
2953 END IF;
2954
2955 CLOSE cur_fut_dt_trnsf;
2956
2957 END IF;
2958
2959 END IF;
2960
2961 -- call the after dml of igs_en_career_model package only if the global variable skip_before_after_dml
2962 -- is false . This variable will be made true in the after dml procedure of igs_en_career_model package
2963 -- to avoid calling before and after dml procedures during recursion of UPDATE_ROW call in the career package
2964 -- donot remove this condition ( included in career_impcat dld)
2965 IF (p_action in ('INSERT','UPDATE')) AND (NOT p_skip_before_after_dml) THEN
2966
2967 IGS_EN_CAREER_MODEL.SCA_TBH_AFTER_DML(
2968 p_person_id => p_new_references.person_id,
2969 p_course_cd => p_new_references.course_cd,
2970 p_version_number => p_new_references.version_number,
2971 p_old_course_attempt_status => p_old_references.course_attempt_status ,
2972 p_new_course_attempt_status => p_new_references.course_attempt_status ,
2973 p_primary_prog_type_source => p_new_references.primary_prog_type_source,
2974 p_old_pri_prog_type => p_old_references.primary_program_type,
2975 p_new_pri_prog_type => p_new_references.primary_program_type,
2976 p_old_key_program => p_old_references.key_program
2977 );
2978
2979 END IF;
2980
2981 END AFTER_DML_RECURSIVE_CALLS;
2982
2983
2984 PROCEDURE After_DML (
2985 p_action IN VARCHAR2,
2986 x_rowid IN VARCHAR2
2987 ) AS
2988 -------------------------------------------------------------------------------
2989 -- Bug ID : UK AWARDS AIM DLD (1366899)
2990 -- who when what
2991 -- ayedubat 2nd MAY 2002 Added the call,AfterRowInsertUpdate4 while inserting a record as part of the bug fix:2344079
2992 --
2993 -- ayedubat 26th,Nov,2001 This procedure is modified to add a call
2994 -- to IGS_EN_GEN_009.ENRP_INS_AWARD_AIM Procedure
2995 --
2996 -- Prajeesh 11-Jun-2002 Added the Award Aim insert call in Both Insertrow and updaterow (2364445)
2997 -------------------------------------------------------------------------------
2998 -- Bug ID : 1818617
2999 -- who when what
3000 -- sjadhav jun 28,2001 this procedure is modified to trigger
3001 -- a Concurrent Request (IGFAPJ10) which
3002 -- will create a new record in IGF To
3003 -- Do table
3004 -- ptandon 28-Nov-2003 Included call to the enrp_ins_upd_term_rec
3005 -- procedure and modified the logic related to
3006 -- invocation of business event procedure.
3007 -- As per Term Based Fee Calc build.
3008 -- Enh. Bug# 2829263.
3009 -------------------------------------------------------------------------------
3010
3011 -- Cursor to get the old key program for a student.
3012 CURSOR cur_get_course_cd_key(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
3013 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE)
3014 IS
3015 SELECT course_cd
3016 FROM igs_en_stdnt_ps_att
3017 WHERE person_id = cp_person_id AND
3018 course_cd <> cp_course_cd AND
3019 key_program = 'Y';
3020
3021 -- Cursor to get the Program Type/Career of a program version.
3022 CURSOR cur_get_prog_type(cp_course_cd igs_ps_ver.course_cd%TYPE,
3023 cp_version_num igs_ps_ver.version_number%TYPE)
3024 IS
3025 SELECT course_type
3026 FROM igs_ps_ver
3027 WHERE course_cd = cp_course_cd AND
3028 version_number = cp_version_num;
3029
3030 -- Cursor to get the old primary program in a given career for a student.
3031 CURSOR cur_get_course_cd_prim(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
3032 cp_course_cd igs_en_stdnt_ps_att.course_cd%TYPE,
3033 cp_version_number igs_en_stdnt_ps_att.version_number%TYPE,
3034 cp_course_type igs_ps_ver.course_type%TYPE)
3035 IS
3036 SELECT spa.course_cd
3037 FROM igs_en_stdnt_ps_att spa,
3038 igs_ps_ver pv
3039 WHERE spa.person_id = cp_person_id AND
3040 spa.course_cd <> cp_course_cd AND
3041 spa.primary_program_type = 'PRIMARY' AND
3042 spa.course_cd = pv.course_cd AND
3043 spa.version_number = pv.version_number AND
3044 pv.course_type = cp_course_type;
3045
3046 -- bmerugu added for build 319 to get the secondary program attemps for student.
3047 CURSOR cur_pattempts(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE, cp_course_type igs_ps_ver.course_type%TYPE)
3048 IS
3049 SELECT spa.person_id, spa.course_cd
3050 FROM igs_en_stdnt_ps_att spa,
3051 igs_ps_ver pv
3052 WHERE spa.person_id = cp_person_id AND
3053 spa.primary_program_type = 'SECONDARY' AND
3054 spa.course_cd = pv.course_cd AND
3055 spa.version_number = pv.version_number AND
3056 pv.course_type = cp_course_type;
3057 cur_pattempts_rec cur_pattempts%ROWTYPE;
3058
3059 CURSOR cur_sua_uooid(cp_person_id igs_en_su_attempt_all.person_id%TYPE, cp_course_cd igs_en_su_attempt_all.course_cd%TYPE)
3060 IS
3061 SELECT sua.uoo_id
3062 FROM igs_en_su_attempt_all sua
3063 WHERE sua.person_id = cp_person_id AND
3064 sua.course_cd = cp_course_cd AND
3065 sua.unit_attempt_status = 'UNCONFIRM';
3066 cur_sua_uooid_rec cur_sua_uooid%ROWTYPE;
3067
3068 l_prog_type igs_ps_ver.course_type%TYPE;
3069 l_old_key_prg igs_ps_ver.course_cd%TYPE := NULL;
3070 l_old_prim_prog_cd igs_ps_ver.course_cd%TYPE;
3071 l_spa_term_cal_type igs_ca_inst.cal_type%TYPE;
3072 l_spa_term_sequence_number igs_ca_inst.sequence_number%TYPE;
3073 l_flag BOOLEAN;
3074 --bmerugu added for build 319 dummy variable to call del_sua_for_reopen
3075 l_delflag BOOLEAN;
3076 BEGIN
3077
3078 l_rowid := x_rowid;
3079 IF (p_action = 'INSERT') THEN
3080 AfterRowInsertUpdate3 ( p_inserting => TRUE,
3081 p_updating => FALSE,
3082 p_deleting => FALSE);
3083
3084 AfterRowInsertUpdate4 ( p_inserting => TRUE,
3085 p_updating => FALSE,
3086 p_deleting => FALSE);
3087 --
3088 -- This Code is added as part of the 'UK AWARD AIMS' DLD.
3089 --
3090 -- Call to the Procedure ENRP_INS_AWARD_AIM of the Package IGS_EN_GEN_009
3091 -- to insert a record in Student Program Attempt Award Aim for each default Program Award found.
3092 IF NEW_REFERENCES.COMMENCEMENT_DT IS NOT NULL THEN
3093 IGS_EN_GEN_009.ENRP_INS_AWARD_AIM (
3094 p_person_id => NEW_REFERENCES.PERSON_ID,
3095 p_course_cd => NEW_REFERENCES.COURSE_CD,
3096 p_version_number => NEW_REFERENCES.VERSION_NUMBER,
3097 p_start_dt =>nvl( NEW_REFERENCES.COMMENCEMENT_DT,sysdate)
3098 );
3099 END IF;
3100 --
3101 -- End of the Code added.
3102
3103 enrp_ins_upd_term_rec(p_action);
3104
3105 ELSIF (p_action = 'UPDATE') THEN
3106 -- Call all the procedures related to After Update.
3107
3108 AfterRowInsertUpdate3 ( p_inserting => FALSE,
3109 p_updating => TRUE,
3110 p_deleting => FALSE);
3111
3112 AfterRowInsertUpdate4 ( p_inserting => FALSE,
3113 p_updating => TRUE,
3114 p_deleting => FALSE);
3115
3116 AfterRowUpdate4 ( p_inserting => FALSE,
3117 p_updating => TRUE,
3118 p_deleting => FALSE);
3119
3120 IF NEW_REFERENCES.COMMENCEMENT_DT IS NOT NULL THEN
3121 IGS_EN_GEN_009.ENRP_INS_AWARD_AIM (
3122 p_person_id => NEW_REFERENCES.PERSON_ID,
3123 p_course_cd => NEW_REFERENCES.COURSE_CD,
3124 p_version_number => NEW_REFERENCES.VERSION_NUMBER,
3125 p_start_dt =>nvl( NEW_REFERENCES.COMMENCEMENT_DT,sysdate)
3126 );
3127 END IF;
3128
3129
3130 AfterRowInsertUpdate5 ( p_inserting => FALSE,
3131 p_updating => TRUE,
3132 p_deleting => FALSE);
3133
3134 ELSIF (p_action = 'DELETE') THEN
3135 -- Call all the procedures related to After Delete.
3136 NULL;
3137 END IF;
3138
3139 IF (p_action = 'UPDATE') THEN
3140
3141 l_spa_term_cal_type := igs_en_spa_terms_api.g_spa_term_cal_type;
3142 l_spa_term_sequence_number := igs_en_spa_terms_api.g_spa_term_sequence_number;
3143
3144 -- Invoke the procedure to check whether to create/update the term record.
3145 enrp_ins_upd_term_rec(p_action);
3146
3147 -- If an update is happening to the key program.
3148 IF NVL(new_references.key_program,'N') <> NVL(old_references.key_program,'N') THEN
3149
3150 -- If the program attempt is being set as key.
3151 IF NVL(new_references.key_program,'N') = 'Y' AND NVL(old_references.key_program,'N') = 'N' THEN
3152
3153 l_flag := FALSE;
3154 IF g_old_key_prg IS NULL THEN
3155 -- Get the old key program into g_old_key_prg global variable.
3156 OPEN cur_get_course_cd_key(new_references.person_id,new_references.course_cd);
3157 FETCH cur_get_course_cd_key INTO g_old_key_prg;
3158 CLOSE cur_get_course_cd_key;
3159 l_flag := TRUE;
3160 END IF;
3161 l_old_key_prg := g_old_key_prg;
3162
3163 -- Raise the business event.
3164 IGS_EN_WLST_GEN_PROC.wf_inform_stud
3165 (p_person_id => new_references.person_id,
3166 p_program_cd => new_references.course_cd,
3167 p_version_number => new_references.version_number,
3168 p_program_attempt_status => new_references.course_attempt_status,
3169 p_org_id => new_references.org_id,
3170 p_old_key_program => g_old_key_prg,
3171 p_old_prim_program => NULL,
3172 p_load_cal_type => l_spa_term_cal_type,
3173 p_load_ci_seq_num => l_spa_term_sequence_number
3174 );
3175
3176 -- Resetting the global variable.
3177 IF l_flag = TRUE THEN
3178 g_old_key_prg := '*';
3179 ELSE
3180 g_old_key_prg := NULL;
3181 END IF;
3182
3183 -- The program attempt is being modified to not to be the key any longer then set the
3184 -- value of program code in the global variable g_old_key_prg.
3185 ELSE
3186 IF g_old_key_prg = '*' THEN
3187 g_old_key_prg := NULL;
3188 ELSE
3189 g_old_key_prg := new_references.course_cd;
3190 l_old_key_prg := g_old_key_prg;
3191 END IF;
3192 END IF;
3193
3194 igf_aw_coa_gen.ins_coa_todo(
3195 p_person_id => new_references.person_id ,
3196 p_calling_module => 'IGSEI24B',
3197 p_program_code => new_references.course_cd,
3198 p_version_number=> new_references.version_number) ;
3199
3200 END IF;
3201
3202 -- If an update is happening to the primary program type column.
3203 IF NVL(new_references.primary_program_type,'SECONDARY') <> NVL(old_references.primary_program_type,'SECONDARY') THEN
3204
3205 -- Get the Program Type/Career for the program version.
3206 OPEN cur_get_prog_type(new_references.course_cd,new_references.version_number);
3207 FETCH cur_get_prog_type INTO l_prog_type;
3208 CLOSE cur_get_prog_type;
3209
3210 IF g_primary_prg_rec_count IS NULL THEN
3211 g_primary_prg_rec_count := 0;
3212 END IF;
3213
3214 -- If the program attempt is being set as Primary.
3215 IF NVL(new_references.primary_program_type,'SECONDARY') = 'PRIMARY' AND
3216 NVL(old_references.primary_program_type,'SECONDARY') = 'SECONDARY' THEN
3217
3218 l_old_prim_prog_cd := NULL;
3219
3220 -- Identify the old primary program by looping through the pl/sql table.
3221 FOR i IN 1..g_primary_prg_rec_count LOOP
3222 IF g_primary_prg_rec.exists(i) THEN
3223 IF g_primary_prg_rec(i).career = l_prog_type THEN
3224 l_old_prim_prog_cd := g_primary_prg_rec(i).program_cd;
3225 END IF;
3226 END IF;
3227 END LOOP;
3228
3229 l_flag := FALSE;
3230 -- If no matching record is found.
3231 IF l_old_prim_prog_cd IS NULL THEN
3232 -- Get the old primary program.
3233 OPEN cur_get_course_cd_prim(new_references.person_id,new_references.course_cd,new_references.version_number,l_prog_type);
3234 FETCH cur_get_course_cd_prim INTO l_old_prim_prog_cd;
3235 CLOSE cur_get_course_cd_prim;
3236 l_flag := TRUE;
3237 END IF;
3238
3239 -- Raise the business event.
3240 IGS_EN_WLST_GEN_PROC.wf_inform_stud
3241 (p_person_id => new_references.person_id,
3242 p_program_cd => new_references.course_cd,
3243 p_version_number => new_references.version_number,
3244 p_program_attempt_status => new_references.course_attempt_status,
3245 p_org_id => new_references.org_id,
3246 p_old_key_program => l_old_key_prg,
3247 p_old_prim_program => l_old_prim_prog_cd,
3248 p_load_cal_type => l_spa_term_cal_type,
3249 p_load_ci_seq_num => l_spa_term_sequence_number
3250 );
3251
3252 IF l_flag = TRUE THEN
3253 g_sec_to_prim_first := TRUE;
3254 ELSE
3255
3256 FOR i IN 1..g_primary_prg_rec_count LOOP
3257 IF g_primary_prg_rec.exists(i) THEN
3258 IF g_primary_prg_rec(i).career = l_prog_type THEN
3259 g_primary_prg_rec.delete(i);
3260 END IF;
3261 END IF;
3262 END LOOP;
3263
3264 END IF;
3265
3266 -- If the program attempt is being modified to not to be primary any longer then store the
3267 -- value of the program code in the global pl/sql table g_primary_prg_rec.
3268 ELSE
3269
3270 IF g_sec_to_prim_first = TRUE THEN
3271 g_sec_to_prim_first := FALSE;
3272 ELSE
3273 g_primary_prg_rec_count := g_primary_prg_rec_count + 1;
3274 g_primary_prg_rec(g_primary_prg_rec_count).career := l_prog_type;
3275 g_primary_prg_rec(g_primary_prg_rec_count).program_cd := new_references.course_cd;
3276 END IF;
3277
3278 END IF;
3279
3280 END IF;
3281
3282 END IF;
3283
3284 -- check to ensure that course attempt status is never null
3285 IF (p_action = 'UPDATE') THEN
3286 IF new_references.course_attempt_status IS NULL THEN
3287 Fnd_Message.Set_Name ('IGS','IGS_GE_INVALID_VALUE');
3288 IGS_GE_MSG_STACK.ADD;
3289 App_Exception.Raise_Exception;
3290 END IF;
3291 END IF;
3292
3293 IF p_action = 'INSERT' AND NVL(new_references.key_program,'N') = 'Y' THEN
3294 igf_aw_coa_gen.ins_coa_todo(
3295 p_person_id => new_references.person_id ,
3296 p_calling_module => 'IGSEI24B',
3297 p_program_code => new_references.course_cd,
3298 p_version_number=> new_references.version_number) ;
3299 END IF;
3300
3301 IF p_action = 'DELETE' AND NVL(old_references.key_program,'N') = 'Y' THEN
3302 igf_aw_coa_gen. ins_coa_todo(
3303 p_person_id => old_references.person_id ,
3304 p_calling_module => 'IGSEI24B_D',
3305 p_program_code => old_references.course_cd,
3306 p_version_number=> old_references.version_number) ;
3307 END IF;
3308
3309 --bmerugu added for build 319
3310 IF (p_action='INSERT' OR p_action='UPDATE') THEN
3311 IF (NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N')='Y') THEN
3312 IF (NVL(new_references.primary_program_type,'SECONDARY')='PRIMARY' AND
3313 new_references.student_confirmed_ind='Y') THEN
3314 BEGIN
3315 -- Get the Program Type/Career for the program version.
3316 OPEN cur_get_prog_type(new_references.course_cd,new_references.version_number);
3317 FETCH cur_get_prog_type INTO l_prog_type;
3318 CLOSE cur_get_prog_type;
3319 FOR cur_pattempts_rec IN cur_pattempts(new_references.person_id,l_prog_type) LOOP
3320 FOR cur_sua_uooid_rec IN cur_sua_uooid(cur_pattempts_rec.person_id,cur_pattempts_rec.course_cd) LOOP
3321 --call to delete the unit attemps
3322 l_delflag := IGS_EN_FUTURE_DT_TRANS.del_sua_for_reopen(cur_pattempts_rec.person_id,
3323 cur_pattempts_rec.course_cd,
3324 cur_sua_uooid_rec.uoo_id);
3325 END LOOP;
3326 END LOOP;
3327 END;
3328 END IF; -- end of check if current program is primary
3329 END IF; -- end of career_mode check
3330 END IF; -- end of p_action check
3331
3332
3333 /**********************************************************************************************************/
3334
3335 /* Please don't add any code after the call to IGS_EN_CAREER_MODEL.SCA_TBH_AFTER_DML as this procedure */
3336 /* recursively calls update_row resetting the package variable new_references which might result in some */
3337 /* unexpected behaviour. So, add any new code before this call. */
3338
3339 /**********************************************************************************************************/
3340
3341 AFTER_DML_RECURSIVE_CALLS(p_old_references => old_references,
3342 p_new_references => new_references,
3343 p_action => p_action,
3344 p_skip_before_after_dml => IGS_EN_STDNT_PS_ATT_PKG.skip_before_after_dml);
3345
3346 END After_DML;
3347
3348
3349
3350 PROCEDURE INSERT_ROW (
3351 X_ROWID IN OUT NOCOPY VARCHAR2,
3352 X_ORG_ID IN NUMBER,
3353 X_PERSON_ID IN NUMBER,
3354 X_COURSE_CD IN VARCHAR2,
3355 X_ADVANCED_STANDING_IND IN VARCHAR2,
3356 X_FEE_CAT IN VARCHAR2,
3357 X_CORRESPONDENCE_CAT IN VARCHAR2,
3358 X_SELF_HELP_GROUP_IND IN VARCHAR2,
3359 X_LOGICAL_DELETE_DT IN DATE,
3360 X_ADM_ADMISSION_APPL_NUMBER IN NUMBER,
3361 X_ADM_NOMINATED_COURSE_CD IN VARCHAR2,
3362 X_ADM_SEQUENCE_NUMBER IN NUMBER,
3363 X_VERSION_NUMBER IN NUMBER,
3364 X_CAL_TYPE IN VARCHAR2,
3365 X_LOCATION_CD IN VARCHAR2,
3366 X_ATTENDANCE_MODE IN VARCHAR2,
3367 X_ATTENDANCE_TYPE IN VARCHAR2,
3368 X_COO_ID IN NUMBER,
3369 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
3370 X_COMMENCEMENT_DT IN DATE,
3371 X_COURSE_ATTEMPT_STATUS IN VARCHAR2,
3372 X_PROGRESSION_STATUS IN VARCHAR2,
3373 X_DERIVED_ATT_TYPE IN VARCHAR2,
3374 X_DERIVED_ATT_MODE IN VARCHAR2,
3375 X_PROVISIONAL_IND IN VARCHAR2,
3376 X_DISCONTINUED_DT IN DATE,
3377 X_DISCONTINUATION_REASON_CD IN VARCHAR2,
3378 X_LAPSED_DT IN DATE,
3379 X_FUNDING_SOURCE IN VARCHAR2,
3380 X_EXAM_LOCATION_CD IN VARCHAR2,
3381 X_DERIVED_COMPLETION_YR IN NUMBER,
3382 X_DERIVED_COMPLETION_PERD IN VARCHAR2,
3383 X_NOMINATED_COMPLETION_YR IN NUMBER,
3384 X_NOMINATED_COMPLETION_PERD IN VARCHAR2,
3385 X_RULE_CHECK_IND IN VARCHAR2,
3386 X_WAIVE_OPTION_CHECK_IND IN VARCHAR2,
3387 X_LAST_RULE_CHECK_DT IN DATE,
3388 X_PUBLISH_OUTCOMES_IND IN VARCHAR2,
3389 X_COURSE_RQRMNT_COMPLETE_IND IN VARCHAR2,
3390 X_COURSE_RQRMNTS_COMPLETE_DT IN DATE,
3391 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
3392 X_OVERRIDE_TIME_LIMITATION IN NUMBER,
3393 X_MODE IN VARCHAR2 ,
3394 X_LAST_DATE_OF_ATTENDANCE IN DATE,
3395 X_DROPPED_BY IN VARCHAR2,
3396 X_IGS_PR_CLASS_STD_ID IN NUMBER,
3397 X_PRIMARY_PROGRAM_TYPE IN VARCHAR2,
3398 X_PRIMARY_PROG_TYPE_SOURCE IN VARCHAR2,
3399 X_CATALOG_CAL_TYPE IN VARCHAR2,
3400 X_CATALOG_SEQ_NUM IN NUMBER,
3401 X_KEY_PROGRAM IN VARCHAR2,
3402 X_MANUAL_OVR_CMPL_DT_IND IN VARCHAR2,
3403 X_OVERRIDE_CMPL_DT IN DATE ,
3404 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
3405 X_ATTRIBUTE1 IN VARCHAR2,
3406 X_ATTRIBUTE2 IN VARCHAR2,
3407 X_ATTRIBUTE3 IN VARCHAR2,
3408 X_ATTRIBUTE4 IN VARCHAR2,
3409 X_ATTRIBUTE5 IN VARCHAR2,
3410 X_ATTRIBUTE6 IN VARCHAR2,
3411 X_ATTRIBUTE7 IN VARCHAR2,
3412 X_ATTRIBUTE8 IN VARCHAR2,
3413 X_ATTRIBUTE9 IN VARCHAR2,
3414 X_ATTRIBUTE10 IN VARCHAR2,
3415 X_ATTRIBUTE11 IN VARCHAR2,
3416 X_ATTRIBUTE12 IN VARCHAR2,
3417 X_ATTRIBUTE13 IN VARCHAR2,
3418 X_ATTRIBUTE14 IN VARCHAR2,
3419 X_ATTRIBUTE15 IN VARCHAR2,
3420 X_ATTRIBUTE16 IN VARCHAR2,
3421 X_ATTRIBUTE17 IN VARCHAR2,
3422 X_ATTRIBUTE18 IN VARCHAR2,
3423 X_ATTRIBUTE19 IN VARCHAR2,
3424 x_ATTRIBUTE20 IN VARCHAR2,
3425 X_FUTURE_DATED_TRANS_FLAG IN VARCHAR2
3426 ) AS
3427 /*--------------------------------------------------------------------------------------------
3428 --Change History
3429 --Who When What
3430 --sbaliga 13-feb-2002 Assigned igs_ge_gen_003.get_org_id to x_org_id
3431 -- in call to before_dml as part of SWCR006 build.
3432 -----------------------------------------------------------------------------------------------*/
3433 CURSOR C IS
3434 SELECT ROWID FROM IGS_EN_STDNT_PS_ATT_ALL
3435 WHERE PERSON_ID = X_PERSON_ID
3436 AND COURSE_CD = X_COURSE_CD;
3437 X_LAST_UPDATE_DATE DATE;
3438 X_LAST_UPDATED_BY NUMBER;
3439 X_LAST_UPDATE_LOGIN NUMBER;
3440 X_REQUEST_ID NUMBER;
3441 X_PROGRAM_ID NUMBER;
3442 X_PROGRAM_APPLICATION_ID NUMBER;
3443 X_PROGRAM_UPDATE_DATE DATE;
3444 BEGIN
3445 X_LAST_UPDATE_DATE := SYSDATE;
3446 IF(X_MODE = 'I') THEN
3447 X_LAST_UPDATED_BY := 1;
3448 X_LAST_UPDATE_LOGIN := 0;
3449 ELSIF (X_MODE IN ('R', 'S')) THEN
3450 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3451 IF X_LAST_UPDATED_BY IS NULL THEN
3452 X_LAST_UPDATED_BY := -1;
3453 END IF;
3454 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
3455 IF X_LAST_UPDATE_LOGIN IS NULL THEN
3456 X_LAST_UPDATE_LOGIN := -1;
3457 END IF;
3458 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
3459 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
3460 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
3461 IF (X_REQUEST_ID = -1) THEN
3462 X_REQUEST_ID := NULL;
3463 X_PROGRAM_ID := NULL;
3464 X_PROGRAM_APPLICATION_ID := NULL;
3465 X_PROGRAM_UPDATE_DATE := NULL;
3466 ELSE
3467 X_PROGRAM_UPDATE_DATE := SYSDATE;
3468 END IF;
3469 ELSE
3470 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
3471 IGS_GE_MSG_STACK.ADD;
3472 app_exception.raise_exception;
3473 END IF;
3474
3475
3476 Before_DML (
3477 p_action => 'INSERT',
3478 x_rowid => X_ROWID,
3479 x_org_id => igs_ge_gen_003.get_org_id,
3480 x_person_id => X_PERSON_ID,
3481 x_course_cd => X_COURSE_CD,
3482 x_advanced_standing_ind => NVL(X_ADVANCED_STANDING_IND,'N'),
3483 x_fee_cat => X_FEE_CAT,
3484 x_correspondence_cat => X_CORRESPONDENCE_CAT,
3485 x_self_help_group_ind => NVL(X_SELF_HELP_GROUP_IND,'N'),
3486 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
3487 x_adm_admission_appl_number => X_ADM_ADMISSION_APPL_NUMBER,
3488 x_adm_nominated_course_cd => X_ADM_NOMINATED_COURSE_CD,
3489 x_adm_sequence_number => X_ADM_SEQUENCE_NUMBER,
3490 x_version_number => X_VERSION_NUMBER,
3491 x_cal_type => X_CAL_TYPE,
3492 x_location_cd => X_LOCATION_CD,
3493 x_attendance_mode => X_ATTENDANCE_MODE,
3494 x_attendance_type => X_ATTENDANCE_TYPE,
3495 x_coo_id => X_COO_ID,
3496 x_student_confirmed_ind => NVL(X_STUDENT_CONFIRMED_IND,'N'),
3497 x_commencement_dt => X_COMMENCEMENT_DT,
3498 x_course_attempt_status => X_COURSE_ATTEMPT_STATUS,
3499 x_progression_status => NVL(X_PROGRESSION_STATUS,'GOODSTAND'),
3500 x_derived_att_type => X_DERIVED_ATT_TYPE,
3501 x_derived_att_mode => X_DERIVED_ATT_MODE,
3502 x_provisional_ind => NVL(X_PROVISIONAL_IND,'N'),
3503 x_discontinued_dt => X_DISCONTINUED_DT,
3504 x_discontinuation_reason_cd => X_DISCONTINUATION_REASON_CD,
3505 x_lapsed_dt => X_LAPSED_DT,
3506 x_funding_source => X_FUNDING_SOURCE,
3507 x_exam_location_cd => X_EXAM_LOCATION_CD,
3508 x_derived_completion_yr => X_DERIVED_COMPLETION_YR,
3509 x_derived_completion_perd => X_DERIVED_COMPLETION_PERD,
3510 x_nominated_completion_yr => X_NOMINATED_COMPLETION_YR,
3511 x_nominated_completion_perd => X_NOMINATED_COMPLETION_PERD,
3512 x_rule_check_ind => NVL(X_RULE_CHECK_IND,'Y'),
3513 x_waive_option_check_ind => NVL(X_WAIVE_OPTION_CHECK_IND,'N'),
3514 x_last_rule_check_dt => X_LAST_RULE_CHECK_DT,
3515 x_publish_outcomes_ind => NVL(X_PUBLISH_OUTCOMES_IND,'Y'),
3516 x_course_rqrmnt_complete_ind => NVL(X_COURSE_RQRMNT_COMPLETE_IND,'N'),
3517 x_course_rqrmnts_complete_dt => X_COURSE_RQRMNTS_COMPLETE_DT,
3518 x_s_completed_source_type => NVL(X_S_COMPLETED_SOURCE_TYPE,'MANUAL'),
3519 x_override_time_limitation => X_OVERRIDE_TIME_LIMITATION,
3520 x_creation_date => X_LAST_UPDATE_DATE,
3521 x_created_by => X_LAST_UPDATED_BY,
3522 x_last_update_date => X_LAST_UPDATE_DATE,
3523 x_last_updated_by => X_LAST_UPDATED_BY,
3524 x_last_update_login => X_LAST_UPDATE_LOGIN,
3525 x_last_date_of_attendance =>X_LAST_DATE_OF_ATTENDANCE,
3526 x_dropped_by => X_DROPPED_BY,
3527 X_IGS_PR_CLASS_STD_ID => X_IGS_PR_CLASS_STD_ID,
3528 x_primary_program_type => X_PRIMARY_PROGRAM_TYPE,
3529 x_primary_prog_type_source => X_PRIMARY_PROG_TYPE_SOURCE,
3530 x_catalog_cal_type => X_CATALOG_CAL_TYPE,
3531 x_catalog_seq_num => X_CATALOG_SEQ_NUM,
3532 x_key_program => NVL(X_KEY_PROGRAM,'N'),
3533 x_manual_ovr_cmpl_dt_ind => X_MANUAL_OVR_CMPL_DT_IND,
3534 x_override_cmpl_dt => X_OVERRIDE_CMPL_DT,
3535 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
3536 x_future_dated_trans_flag => NVL(X_FUTURE_DATED_TRANS_FLAG,'N'),
3537 x_attribute1=>X_ATTRIBUTE1,
3538 x_attribute2=>X_ATTRIBUTE2,
3539 x_attribute3=>X_ATTRIBUTE3,
3540 x_attribute4=>X_ATTRIBUTE4,
3541 x_attribute5=>X_ATTRIBUTE5,
3542 x_attribute6=>X_ATTRIBUTE6,
3543 x_attribute7=>X_ATTRIBUTE7,
3544 x_attribute8=>X_ATTRIBUTE8,
3545 x_attribute9=>X_ATTRIBUTE9,
3546 x_attribute10=>X_ATTRIBUTE10,
3547 x_attribute11=>X_ATTRIBUTE11,
3548 x_attribute12=>X_ATTRIBUTE12,
3549 x_attribute13=>X_ATTRIBUTE13,
3550 x_attribute14=>X_ATTRIBUTE14,
3551 x_attribute15=>X_ATTRIBUTE15,
3552 x_attribute16=>X_ATTRIBUTE16,
3553 x_attribute17=>X_ATTRIBUTE17,
3554 x_attribute18=>X_ATTRIBUTE18,
3555 x_attribute19=>X_ATTRIBUTE19,
3556 x_attribute20=>X_ATTRIBUTE20
3557 );
3558
3559
3560 IF (x_mode = 'S') THEN
3561 igs_sc_gen_001.set_ctx('R');
3562 END IF;
3563 INSERT INTO IGS_EN_STDNT_PS_ATT_ALL (
3564 org_id,
3565 ADVANCED_STANDING_IND,
3566 FEE_CAT,
3567 CORRESPONDENCE_CAT,
3568 SELF_HELP_GROUP_IND,
3569 LOGICAL_DELETE_DT,
3570 ADM_ADMISSION_APPL_NUMBER,
3571 ADM_NOMINATED_COURSE_CD,
3572 ADM_SEQUENCE_NUMBER,
3573 PERSON_ID,
3574 COURSE_CD,
3575 VERSION_NUMBER,
3576 CAL_TYPE,
3577 LOCATION_CD,
3578 ATTENDANCE_MODE,
3579 ATTENDANCE_TYPE,
3580 COO_ID,
3581 STUDENT_CONFIRMED_IND,
3582 COMMENCEMENT_DT,
3583 COURSE_ATTEMPT_STATUS,
3584 PROGRESSION_STATUS,
3585 DERIVED_ATT_TYPE,
3586 DERIVED_ATT_MODE,
3587 PROVISIONAL_IND,
3588 DISCONTINUED_DT,
3589 DISCONTINUATION_REASON_CD,
3590 LAPSED_DT,
3591 FUNDING_SOURCE,
3592 EXAM_LOCATION_CD,
3593 DERIVED_COMPLETION_YR,
3594 DERIVED_COMPLETION_PERD,
3595 NOMINATED_COMPLETION_YR,
3596 NOMINATED_COMPLETION_PERD,
3597 RULE_CHECK_IND,
3598 WAIVE_OPTION_CHECK_IND,
3599 LAST_RULE_CHECK_DT,
3600 PUBLISH_OUTCOMES_IND,
3601 COURSE_RQRMNT_COMPLETE_IND,
3602 COURSE_RQRMNTS_COMPLETE_DT,
3603 S_COMPLETED_SOURCE_TYPE,
3604 OVERRIDE_TIME_LIMITATION,
3605 CREATION_DATE,
3606 CREATED_BY,
3607 LAST_UPDATE_DATE,
3608 LAST_UPDATED_BY,
3609 LAST_UPDATE_LOGIN,
3610 REQUEST_ID,
3611 PROGRAM_ID,
3612 PROGRAM_APPLICATION_ID,
3613 PROGRAM_UPDATE_DATE,
3614 LAST_DATE_OF_ATTENDANCE,
3615 DROPPED_BY,
3616 IGS_PR_CLASS_STD_ID,
3617 PRIMARY_PROGRAM_TYPE,
3618 PRIMARY_PROG_TYPE_SOURCE,
3619 CATALOG_CAL_TYPE,
3620 CATALOG_SEQ_NUM,
3621 KEY_PROGRAM,
3622 MANUAL_OVR_CMPL_DT_IND,
3623 OVERRIDE_CMPL_DT,
3624 ATTRIBUTE_CATEGORY,
3625 FUTURE_DATED_TRANS_FLAG,
3626 ATTRIBUTE1,
3627 ATTRIBUTE2,
3628 ATTRIBUTE3,
3629 ATTRIBUTE4,
3630 ATTRIBUTE5,
3631 ATTRIBUTE6,
3632 ATTRIBUTE7,
3633 ATTRIBUTE8,
3634 ATTRIBUTE9,
3635 ATTRIBUTE10,
3636 ATTRIBUTE11,
3637 ATTRIBUTE12,
3638 ATTRIBUTE13,
3639 ATTRIBUTE14,
3640 ATTRIBUTE15,
3641 ATTRIBUTE16,
3642 ATTRIBUTE17,
3643 ATTRIBUTE18,
3644 ATTRIBUTE19,
3645 ATTRIBUTE20
3646 ) VALUES (
3647 NEW_REFERENCES.ORG_ID,
3648 NEW_REFERENCES.ADVANCED_STANDING_IND,
3649 NEW_REFERENCES.FEE_CAT,
3650 NEW_REFERENCES.CORRESPONDENCE_CAT,
3651 NEW_REFERENCES.SELF_HELP_GROUP_IND,
3652 NEW_REFERENCES.LOGICAL_DELETE_DT,
3653 NEW_REFERENCES.ADM_ADMISSION_APPL_NUMBER,
3654 NEW_REFERENCES.ADM_NOMINATED_COURSE_CD,
3655 NEW_REFERENCES.ADM_SEQUENCE_NUMBER,
3656 NEW_REFERENCES.PERSON_ID,
3657 NEW_REFERENCES.COURSE_CD,
3658 NEW_REFERENCES.VERSION_NUMBER,
3659 NEW_REFERENCES.CAL_TYPE,
3660 NEW_REFERENCES.LOCATION_CD,
3661 NEW_REFERENCES.ATTENDANCE_MODE,
3662 NEW_REFERENCES.ATTENDANCE_TYPE,
3663 NEW_REFERENCES.COO_ID,
3664 NEW_REFERENCES.STUDENT_CONFIRMED_IND,
3665 NEW_REFERENCES.COMMENCEMENT_DT,
3666 NEW_REFERENCES.COURSE_ATTEMPT_STATUS,
3667 NEW_REFERENCES.PROGRESSION_STATUS,
3668 NEW_REFERENCES.DERIVED_ATT_TYPE,
3669 NEW_REFERENCES.DERIVED_ATT_MODE,
3670 NEW_REFERENCES.PROVISIONAL_IND,
3671 NEW_REFERENCES.DISCONTINUED_DT,
3672 NEW_REFERENCES.DISCONTINUATION_REASON_CD,
3673 NEW_REFERENCES.LAPSED_DT,
3674 NEW_REFERENCES.FUNDING_SOURCE,
3675 NEW_REFERENCES.EXAM_LOCATION_CD,
3676 NEW_REFERENCES.DERIVED_COMPLETION_YR,
3677 NEW_REFERENCES.DERIVED_COMPLETION_PERD,
3678 NEW_REFERENCES.NOMINATED_COMPLETION_YR,
3679 NEW_REFERENCES.NOMINATED_COMPLETION_PERD,
3680 NEW_REFERENCES.RULE_CHECK_IND,
3681 NEW_REFERENCES.WAIVE_OPTION_CHECK_IND,
3682 NEW_REFERENCES.LAST_RULE_CHECK_DT,
3683 NEW_REFERENCES.PUBLISH_OUTCOMES_IND,
3684 NEW_REFERENCES.COURSE_RQRMNT_COMPLETE_IND,
3685 NEW_REFERENCES.COURSE_RQRMNTS_COMPLETE_DT,
3686 NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
3687 NEW_REFERENCES.OVERRIDE_TIME_LIMITATION,
3688 X_LAST_UPDATE_DATE,
3689 X_LAST_UPDATED_BY,
3690 X_LAST_UPDATE_DATE,
3691 X_LAST_UPDATED_BY,
3692 X_LAST_UPDATE_LOGIN,
3693 X_REQUEST_ID,
3694 X_PROGRAM_ID,
3695 X_PROGRAM_APPLICATION_ID,
3696 X_PROGRAM_UPDATE_DATE,
3697 NEW_REFERENCES.LAST_DATE_OF_ATTENDANCE,
3698 NEW_REFERENCES.DROPPED_BY,
3699 NEW_REFERENCES.IGS_PR_CLASS_STD_ID,
3700 NEW_REFERENCES.PRIMARY_PROGRAM_TYPE,
3701 NEW_REFERENCES.PRIMARY_PROG_TYPE_SOURCE,
3702 NEW_REFERENCES.CATALOG_CAL_TYPE,
3703 NEW_REFERENCES.CATALOG_SEQ_NUM,
3704 NEW_REFERENCES.KEY_PROGRAM,
3705 NEW_REFERENCES.MANUAL_OVR_CMPL_DT_IND,
3706 NEW_REFERENCES.OVERRIDE_CMPL_DT,
3707 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
3708 NEW_REFERENCES.FUTURE_DATED_TRANS_FLAG,
3709 NEW_REFERENCES.ATTRIBUTE1,
3710 NEW_REFERENCES.ATTRIBUTE2,
3711 NEW_REFERENCES.ATTRIBUTE3,
3712 NEW_REFERENCES.ATTRIBUTE4,
3713 NEW_REFERENCES.ATTRIBUTE5,
3714 NEW_REFERENCES.ATTRIBUTE6,
3715 NEW_REFERENCES.ATTRIBUTE7,
3716 NEW_REFERENCES.ATTRIBUTE8,
3717 NEW_REFERENCES.ATTRIBUTE9,
3718 NEW_REFERENCES.ATTRIBUTE10,
3719 NEW_REFERENCES.ATTRIBUTE11,
3720 NEW_REFERENCES.ATTRIBUTE12,
3721 NEW_REFERENCES.ATTRIBUTE13,
3722 NEW_REFERENCES.ATTRIBUTE14,
3723 NEW_REFERENCES.ATTRIBUTE15,
3724 NEW_REFERENCES.ATTRIBUTE16,
3725 NEW_REFERENCES.ATTRIBUTE17,
3726 NEW_REFERENCES.ATTRIBUTE18,
3727 NEW_REFERENCES.ATTRIBUTE19,
3728 NEW_REFERENCES.ATTRIBUTE20
3729 );
3730 IF (x_mode = 'S') THEN
3731 igs_sc_gen_001.unset_ctx('R');
3732 END IF;
3733
3734
3735 OPEN c;
3736 FETCH c INTO X_ROWID;
3737 IF (c%NOTFOUND) THEN
3738 CLOSE c;
3739 RAISE NO_DATA_FOUND;
3740 END IF;
3741 CLOSE c;
3742
3743
3744 After_DML (
3745 p_action => 'INSERT',
3746 x_rowid => X_ROWID
3747 );
3748
3749 EXCEPTION
3750 WHEN OTHERS THEN
3751 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
3752 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
3753 fnd_message.set_token ('ERR_CD', SQLCODE);
3754 igs_ge_msg_stack.add;
3755 igs_sc_gen_001.unset_ctx('R');
3756 app_exception.raise_exception;
3757 ELSE
3758 igs_sc_gen_001.unset_ctx('R');
3759 RAISE;
3760 END IF;
3761
3762 END INSERT_ROW;
3763
3764 PROCEDURE LOCK_ROW (
3765 X_ROWID IN VARCHAR2,
3766 X_PERSON_ID IN NUMBER,
3767 X_COURSE_CD IN VARCHAR2,
3768 X_ADVANCED_STANDING_IND IN VARCHAR2,
3769 X_FEE_CAT IN VARCHAR2,
3770 X_CORRESPONDENCE_CAT IN VARCHAR2,
3771 X_SELF_HELP_GROUP_IND IN VARCHAR2,
3772 X_LOGICAL_DELETE_DT IN DATE,
3773 X_ADM_ADMISSION_APPL_NUMBER IN NUMBER,
3774 X_ADM_NOMINATED_COURSE_CD IN VARCHAR2,
3775 X_ADM_SEQUENCE_NUMBER IN NUMBER,
3776 X_VERSION_NUMBER IN NUMBER,
3777 X_CAL_TYPE IN VARCHAR2,
3778 X_LOCATION_CD IN VARCHAR2,
3779 X_ATTENDANCE_MODE IN VARCHAR2,
3780 X_ATTENDANCE_TYPE IN VARCHAR2,
3781 X_COO_ID IN NUMBER,
3782 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
3783 X_COMMENCEMENT_DT IN DATE,
3784 X_COURSE_ATTEMPT_STATUS IN VARCHAR2,
3785 X_PROGRESSION_STATUS IN VARCHAR2,
3786 X_DERIVED_ATT_TYPE IN VARCHAR2,
3787 X_DERIVED_ATT_MODE IN VARCHAR2,
3788 X_PROVISIONAL_IND IN VARCHAR2,
3789 X_DISCONTINUED_DT IN DATE,
3790 X_DISCONTINUATION_REASON_CD IN VARCHAR2,
3791 X_LAPSED_DT IN DATE,
3792 X_FUNDING_SOURCE IN VARCHAR2,
3793 X_EXAM_LOCATION_CD IN VARCHAR2,
3794 X_DERIVED_COMPLETION_YR IN NUMBER,
3795 X_DERIVED_COMPLETION_PERD IN VARCHAR2,
3796 X_NOMINATED_COMPLETION_YR IN NUMBER,
3797 X_NOMINATED_COMPLETION_PERD IN VARCHAR2,
3798 X_RULE_CHECK_IND IN VARCHAR2,
3799 X_WAIVE_OPTION_CHECK_IND IN VARCHAR2,
3800 X_LAST_RULE_CHECK_DT IN DATE,
3801 X_PUBLISH_OUTCOMES_IND IN VARCHAR2,
3802 X_COURSE_RQRMNT_COMPLETE_IND IN VARCHAR2,
3803 X_COURSE_RQRMNTS_COMPLETE_DT IN DATE,
3804 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
3805 X_OVERRIDE_TIME_LIMITATION IN NUMBER,
3806 X_LAST_DATE_OF_ATTENDANCE IN DATE,
3807 X_DROPPED_BY IN VARCHAR2,
3808 X_IGS_PR_CLASS_STD_ID IN NUMBER,
3809 X_PRIMARY_PROGRAM_TYPE IN VARCHAR2,
3810 X_PRIMARY_PROG_TYPE_SOURCE IN VARCHAR2,
3811 X_CATALOG_CAL_TYPE IN VARCHAR2,
3812 X_CATALOG_SEQ_NUM IN NUMBER ,
3813 X_KEY_PROGRAM IN VARCHAR2,
3814 X_MANUAL_OVR_CMPL_DT_IND IN VARCHAR2,
3815 X_OVERRIDE_CMPL_DT IN DATE,
3816 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
3817 X_ATTRIBUTE1 IN VARCHAR2,
3818 X_ATTRIBUTE2 IN VARCHAR2,
3819 X_ATTRIBUTE3 IN VARCHAR2,
3820 X_ATTRIBUTE4 IN VARCHAR2,
3821 X_ATTRIBUTE5 IN VARCHAR2,
3822 X_ATTRIBUTE6 IN VARCHAR2,
3823 X_ATTRIBUTE7 IN VARCHAR2,
3824 X_ATTRIBUTE8 IN VARCHAR2,
3825 X_ATTRIBUTE9 IN VARCHAR2,
3826 X_ATTRIBUTE10 IN VARCHAR2,
3827 X_ATTRIBUTE11 IN VARCHAR2,
3828 X_ATTRIBUTE12 IN VARCHAR2,
3829 X_ATTRIBUTE13 IN VARCHAR2,
3830 X_ATTRIBUTE14 IN VARCHAR2,
3831 X_ATTRIBUTE15 IN VARCHAR2,
3832 X_ATTRIBUTE16 IN VARCHAR2,
3833 X_ATTRIBUTE17 IN VARCHAR2,
3834 X_ATTRIBUTE18 IN VARCHAR2,
3835 X_ATTRIBUTE19 IN VARCHAR2,
3836 x_ATTRIBUTE20 IN VARCHAR2,
3837 X_FUTURE_DATED_TRANS_FLAG IN VARCHAR2
3838 ) AS
3839 CURSOR c1 IS SELECT
3840 org_id,
3841 ADVANCED_STANDING_IND,
3842 FEE_CAT,
3843 CORRESPONDENCE_CAT,
3844 SELF_HELP_GROUP_IND,
3845 LOGICAL_DELETE_DT,
3846 ADM_ADMISSION_APPL_NUMBER,
3847 ADM_NOMINATED_COURSE_CD,
3848 ADM_SEQUENCE_NUMBER,
3849 VERSION_NUMBER,
3850 CAL_TYPE,
3851 LOCATION_CD,
3852 ATTENDANCE_MODE,
3853 ATTENDANCE_TYPE,
3854 COO_ID,
3855 STUDENT_CONFIRMED_IND,
3856 COMMENCEMENT_DT,
3857 COURSE_ATTEMPT_STATUS,
3858 PROGRESSION_STATUS,
3859 DERIVED_ATT_TYPE,
3860 DERIVED_ATT_MODE,
3861 PROVISIONAL_IND,
3862 DISCONTINUED_DT,
3863 DISCONTINUATION_REASON_CD,
3864 LAPSED_DT,
3865 FUNDING_SOURCE,
3866 EXAM_LOCATION_CD,
3867 DERIVED_COMPLETION_YR,
3868 DERIVED_COMPLETION_PERD,
3869 NOMINATED_COMPLETION_YR,
3870 NOMINATED_COMPLETION_PERD,
3871 RULE_CHECK_IND,
3872 WAIVE_OPTION_CHECK_IND,
3873 LAST_RULE_CHECK_DT,
3874 PUBLISH_OUTCOMES_IND,
3875 COURSE_RQRMNT_COMPLETE_IND,
3876 COURSE_RQRMNTS_COMPLETE_DT,
3877 S_COMPLETED_SOURCE_TYPE,
3878 OVERRIDE_TIME_LIMITATION,
3879 LAST_DATE_OF_ATTENDANCE,
3880 DROPPED_BY,
3881 IGS_PR_CLASS_STD_ID,
3882 PRIMARY_PROGRAM_TYPE,
3883 PRIMARY_PROG_TYPE_SOURCE,
3884 CATALOG_CAL_TYPE,
3885 CATALOG_SEQ_NUM,
3886 KEY_PROGRAM,
3887 MANUAL_OVR_CMPL_DT_IND,
3888 OVERRIDE_CMPL_DT,
3889 ATTRIBUTE_CATEGORY,
3890 FUTURE_DATED_TRANS_FLAG,
3891 ATTRIBUTE1,
3892 ATTRIBUTE2,
3893 ATTRIBUTE3,
3894 ATTRIBUTE4,
3895 ATTRIBUTE5,
3896 ATTRIBUTE6,
3897 ATTRIBUTE7,
3898 ATTRIBUTE8,
3899 ATTRIBUTE9,
3900 ATTRIBUTE10,
3901 ATTRIBUTE11,
3902 ATTRIBUTE12,
3903 ATTRIBUTE13,
3904 ATTRIBUTE14,
3905 ATTRIBUTE15,
3906 ATTRIBUTE16,
3907 ATTRIBUTE17,
3908 ATTRIBUTE18,
3909 ATTRIBUTE19,
3910 ATTRIBUTE20
3911 FROM IGS_EN_STDNT_PS_ATT_ALL
3912 WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
3913 tlinfo c1%ROWTYPE;
3914 BEGIN
3915 OPEN c1;
3916 FETCH c1 INTO tlinfo;
3917 IF (c1%NOTFOUND) THEN
3918 CLOSE c1;
3919 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
3920 IGS_GE_MSG_STACK.ADD;
3921 app_exception.raise_exception;
3922 RETURN;
3923 END IF;
3924 CLOSE c1;
3925 IF ( (tlinfo.ADVANCED_STANDING_IND = X_ADVANCED_STANDING_IND)
3926 AND ((tlinfo.FEE_CAT = X_FEE_CAT)
3927 OR ((tlinfo.FEE_CAT IS NULL)
3928 AND (X_FEE_CAT IS NULL)))
3929 AND ((tlinfo.CORRESPONDENCE_CAT = X_CORRESPONDENCE_CAT)
3930 OR ((tlinfo.CORRESPONDENCE_CAT IS NULL)
3931 AND (X_CORRESPONDENCE_CAT IS NULL)))
3932 AND (tlinfo.SELF_HELP_GROUP_IND = X_SELF_HELP_GROUP_IND)
3933 AND ((TRUNC(tlinfo.LOGICAL_DELETE_DT) = TRUNC(X_LOGICAL_DELETE_DT))
3934 OR ((tlinfo.LOGICAL_DELETE_DT IS NULL)
3935 AND (X_LOGICAL_DELETE_DT IS NULL)))
3936 AND ((tlinfo.ADM_ADMISSION_APPL_NUMBER = X_ADM_ADMISSION_APPL_NUMBER)
3937 OR ((tlinfo.ADM_ADMISSION_APPL_NUMBER IS NULL)
3938 AND (X_ADM_ADMISSION_APPL_NUMBER IS NULL)))
3939 AND ((tlinfo.ADM_NOMINATED_COURSE_CD = X_ADM_NOMINATED_COURSE_CD)
3940 OR ((tlinfo.ADM_NOMINATED_COURSE_CD IS NULL)
3941 AND (X_ADM_NOMINATED_COURSE_CD IS NULL)))
3942 AND ((tlinfo.ADM_SEQUENCE_NUMBER = X_ADM_SEQUENCE_NUMBER)
3943 OR ((tlinfo.ADM_SEQUENCE_NUMBER IS NULL)
3944 AND (X_ADM_SEQUENCE_NUMBER IS NULL)))
3945 AND (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
3946 AND (tlinfo.CAL_TYPE = X_CAL_TYPE)
3947 AND (tlinfo.LOCATION_CD = X_LOCATION_CD)
3948 AND (tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
3949 AND (tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
3950 AND (tlinfo.COO_ID = X_COO_ID)
3951 AND (tlinfo.STUDENT_CONFIRMED_IND = X_STUDENT_CONFIRMED_IND)
3952 AND ((TRUNC(tlinfo.COMMENCEMENT_DT) = TRUNC(X_COMMENCEMENT_DT))
3953 OR ((tlinfo.COMMENCEMENT_DT IS NULL)
3954 AND (X_COMMENCEMENT_DT IS NULL)))
3955 AND ((tlinfo.COURSE_ATTEMPT_STATUS = X_COURSE_ATTEMPT_STATUS)
3956 OR ((tlinfo.COURSE_ATTEMPT_STATUS IS NULL)
3957 AND (X_COURSE_ATTEMPT_STATUS IS NULL)))
3958 AND ((tlinfo.PROGRESSION_STATUS = X_PROGRESSION_STATUS)
3959 OR ((tlinfo.PROGRESSION_STATUS IS NULL)
3960 AND (X_PROGRESSION_STATUS IS NULL)))
3961 AND ((tlinfo.DERIVED_ATT_TYPE = X_DERIVED_ATT_TYPE)
3962 OR ((tlinfo.DERIVED_ATT_TYPE IS NULL)
3963 AND (X_DERIVED_ATT_TYPE IS NULL)))
3964 AND ((tlinfo.DERIVED_ATT_MODE = X_DERIVED_ATT_MODE)
3965 OR ((tlinfo.DERIVED_ATT_MODE IS NULL)
3966 AND (X_DERIVED_ATT_MODE IS NULL)))
3967 AND (tlinfo.PROVISIONAL_IND = X_PROVISIONAL_IND)
3968 AND ((TRUNC(tlinfo.DISCONTINUED_DT) = TRUNC(X_DISCONTINUED_DT))
3969 OR ((tlinfo.DISCONTINUED_DT IS NULL)
3970 AND (X_DISCONTINUED_DT IS NULL)))
3971 AND ((tlinfo.DISCONTINUATION_REASON_CD = X_DISCONTINUATION_REASON_CD)
3972 OR ((tlinfo.DISCONTINUATION_REASON_CD IS NULL)
3973 AND (X_DISCONTINUATION_REASON_CD IS NULL)))
3974 AND ((TRUNC(tlinfo.LAPSED_DT) = TRUNC(X_LAPSED_DT))
3975 OR ((tlinfo.LAPSED_DT IS NULL)
3976 AND (X_LAPSED_DT IS NULL)))
3977 AND ((tlinfo.FUNDING_SOURCE = X_FUNDING_SOURCE)
3978 OR ((tlinfo.FUNDING_SOURCE IS NULL)
3979 AND (X_FUNDING_SOURCE IS NULL)))
3980 AND ((tlinfo.EXAM_LOCATION_CD = X_EXAM_LOCATION_CD)
3981 OR ((tlinfo.EXAM_LOCATION_CD IS NULL)
3982 AND (X_EXAM_LOCATION_CD IS NULL)))
3983 AND ((tlinfo.DERIVED_COMPLETION_YR = X_DERIVED_COMPLETION_YR)
3984 OR ((tlinfo.DERIVED_COMPLETION_YR IS NULL)
3985 AND (X_DERIVED_COMPLETION_YR IS NULL)))
3986 AND ((tlinfo.DERIVED_COMPLETION_PERD = X_DERIVED_COMPLETION_PERD)
3987 OR ((tlinfo.DERIVED_COMPLETION_PERD IS NULL)
3988 AND (X_DERIVED_COMPLETION_PERD IS NULL)))
3989 AND ((tlinfo.NOMINATED_COMPLETION_YR = X_NOMINATED_COMPLETION_YR)
3990 OR ((tlinfo.NOMINATED_COMPLETION_YR IS NULL)
3991 AND (X_NOMINATED_COMPLETION_YR IS NULL)))
3992 AND ((tlinfo.NOMINATED_COMPLETION_PERD = X_NOMINATED_COMPLETION_PERD)
3993 OR ((tlinfo.NOMINATED_COMPLETION_PERD IS NULL)
3994 AND (X_NOMINATED_COMPLETION_PERD IS NULL)))
3995 AND (tlinfo.RULE_CHECK_IND = X_RULE_CHECK_IND)
3996 AND (tlinfo.WAIVE_OPTION_CHECK_IND = X_WAIVE_OPTION_CHECK_IND)
3997 AND ((TRUNC(tlinfo.LAST_RULE_CHECK_DT) = TRUNC(X_LAST_RULE_CHECK_DT))
3998 OR ((tlinfo.LAST_RULE_CHECK_DT IS NULL)
3999 AND (X_LAST_RULE_CHECK_DT IS NULL)))
4000 AND (tlinfo.PUBLISH_OUTCOMES_IND = X_PUBLISH_OUTCOMES_IND)
4001 AND (tlinfo.COURSE_RQRMNT_COMPLETE_IND = X_COURSE_RQRMNT_COMPLETE_IND)
4002 AND ((TRUNC(tlinfo.COURSE_RQRMNTS_COMPLETE_DT) = TRUNC(X_COURSE_RQRMNTS_COMPLETE_DT))
4003 OR ((tlinfo.COURSE_RQRMNTS_COMPLETE_DT IS NULL)
4004 AND (X_COURSE_RQRMNTS_COMPLETE_DT IS NULL)))
4005 AND ((tlinfo.S_COMPLETED_SOURCE_TYPE = X_S_COMPLETED_SOURCE_TYPE)
4006 OR ((tlinfo.S_COMPLETED_SOURCE_TYPE IS NULL)
4007 AND (X_S_COMPLETED_SOURCE_TYPE IS NULL)))
4008 AND ((tlinfo.OVERRIDE_TIME_LIMITATION = X_OVERRIDE_TIME_LIMITATION)
4009 OR ((tlinfo.OVERRIDE_TIME_LIMITATION IS NULL)
4010 AND (X_OVERRIDE_TIME_LIMITATION IS NULL)))
4011 AND ((TRUNC(tlinfo.LAST_DATE_OF_ATTENDANCE) = TRUNC(X_LAST_DATE_OF_ATTENDANCE))
4012 OR ((tlinfo.LAST_DATE_OF_ATTENDANCE IS NULL)
4013 AND (X_LAST_DATE_OF_ATTENDANCE IS NULL)))
4014 AND ((tlinfo.DROPPED_BY = X_DROPPED_BY)
4015 OR ((tlinfo.DROPPED_BY IS NULL)
4016 AND (X_DROPPED_BY IS NULL)))
4017 AND ((tlinfo.IGS_PR_CLASS_STD_ID = X_IGS_PR_CLASS_STD_ID)
4018 OR ((tlinfo.IGS_PR_CLASS_STD_ID IS NULL)
4019 AND (X_IGS_PR_CLASS_STD_ID IS NULL)))
4020 AND ((tlinfo.PRIMARY_PROGRAM_TYPE = X_PRIMARY_PROGRAM_TYPE)
4021 OR ((tlinfo.PRIMARY_PROGRAM_TYPE IS NULL)
4022 AND (X_PRIMARY_PROGRAM_TYPE IS NULL)))
4023 AND ((tlinfo.PRIMARY_PROG_TYPE_SOURCE = X_PRIMARY_PROG_TYPE_SOURCE)
4024 OR ((tlinfo.PRIMARY_PROG_TYPE_SOURCE IS NULL)
4025 AND (X_PRIMARY_PROG_TYPE_SOURCE IS NULL)))
4026 AND ((tlinfo.CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE)
4027 OR ((tlinfo.CATALOG_CAL_TYPE IS NULL)
4028 AND (X_CATALOG_CAL_TYPE IS NULL)))
4029 AND ((tlinfo.CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM)
4030 OR ((tlinfo.CATALOG_SEQ_NUM IS NULL)
4031 AND (X_CATALOG_SEQ_NUM IS NULL)))
4032 AND ((tlinfo.KEY_PROGRAM = X_KEY_PROGRAM)
4033 OR ((tlinfo.KEY_PROGRAM IS NULL)
4034 AND (X_KEY_PROGRAM IS NULL)))
4035 AND ((tlinfo.MANUAL_OVR_CMPL_DT_IND = X_MANUAL_OVR_CMPL_DT_IND)
4036 OR ((tlinfo.MANUAL_OVR_CMPL_DT_IND IS NULL)
4037 AND (X_MANUAL_OVR_CMPL_DT_IND IS NULL)))
4038 AND ((TRUNC(tlinfo.OVERRIDE_CMPL_DT) = TRUNC(X_OVERRIDE_CMPL_DT))
4039 OR ((tlinfo.OVERRIDE_CMPL_DT IS NULL)
4040 AND (X_OVERRIDE_CMPL_DT IS NULL)))
4041 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
4042 OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
4043 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
4044 AND ((tlinfo.FUTURE_DATED_TRANS_FLAG = X_FUTURE_DATED_TRANS_FLAG)
4045 OR ((tlinfo.FUTURE_DATED_TRANS_FLAG IS NULL)
4046 AND (X_FUTURE_DATED_TRANS_FLAG IS NULL)))
4047 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
4048 OR ((tlinfo.ATTRIBUTE1 IS NULL)
4049 AND (X_ATTRIBUTE1 IS NULL)))
4050 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
4051 OR ((tlinfo.ATTRIBUTE2 IS NULL)
4052 AND (X_ATTRIBUTE2 IS NULL)))
4053 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
4054 OR ((tlinfo.ATTRIBUTE3 IS NULL)
4055 AND (X_ATTRIBUTE3 IS NULL)))
4056 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
4057 OR ((tlinfo.ATTRIBUTE4 IS NULL)
4058 AND (X_ATTRIBUTE4 IS NULL)))
4059 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
4060 OR ((tlinfo.ATTRIBUTE5 IS NULL)
4061 AND (X_ATTRIBUTE5 IS NULL)))
4062 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
4063 OR ((tlinfo.ATTRIBUTE6 IS NULL)
4064 AND (X_ATTRIBUTE6 IS NULL)))
4065 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
4066 OR ((tlinfo.ATTRIBUTE7 IS NULL)
4067 AND (X_ATTRIBUTE7 IS NULL)))
4068 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
4069 OR ((tlinfo.ATTRIBUTE8 IS NULL)
4070 AND (X_ATTRIBUTE8 IS NULL)))
4071 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
4072 OR ((tlinfo.ATTRIBUTE9 IS NULL)
4073 AND (X_ATTRIBUTE9 IS NULL)))
4074 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
4075 OR ((tlinfo.ATTRIBUTE10 IS NULL)
4076 AND (X_ATTRIBUTE10 IS NULL)))
4077 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
4078 OR ((tlinfo.ATTRIBUTE11 IS NULL)
4079 AND (X_ATTRIBUTE11 IS NULL)))
4080 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
4081 OR ((tlinfo.ATTRIBUTE12 IS NULL)
4082 AND (X_ATTRIBUTE12 IS NULL)))
4083 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
4084 OR ((tlinfo.ATTRIBUTE13 IS NULL)
4085 AND (X_ATTRIBUTE13 IS NULL)))
4086 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
4087 OR ((tlinfo.ATTRIBUTE14 IS NULL)
4088 AND (X_ATTRIBUTE14 IS NULL)))
4089 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
4090 OR ((tlinfo.ATTRIBUTE15 IS NULL)
4091 AND (X_ATTRIBUTE15 IS NULL)))
4092 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16)
4093 OR ((tlinfo.ATTRIBUTE16 IS NULL)
4094 AND (X_ATTRIBUTE16 IS NULL)))
4095 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17)
4096 OR ((tlinfo.ATTRIBUTE17 IS NULL)
4097 AND (X_ATTRIBUTE17 IS NULL)))
4098 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18)
4099 OR ((tlinfo.ATTRIBUTE18 IS NULL)
4100 AND (X_ATTRIBUTE18 IS NULL)))
4101 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19)
4102 OR ((tlinfo.ATTRIBUTE19 IS NULL)
4103 AND (X_ATTRIBUTE19 IS NULL)))
4104 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20)
4105 OR ((tlinfo.ATTRIBUTE20 IS NULL)
4106 AND (X_ATTRIBUTE20 IS NULL)))
4107 ) THEN
4108 NULL;
4109 ELSE
4110
4111 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
4112 IGS_GE_MSG_STACK.ADD;
4113 app_exception.raise_exception;
4114
4115 END IF;
4116 RETURN;
4117 END LOCK_ROW;
4118
4119 PROCEDURE UPDATE_ROW (
4120 X_ROWID IN VARCHAR2,
4121 X_PERSON_ID IN NUMBER,
4122 X_COURSE_CD IN VARCHAR2,
4123 X_ADVANCED_STANDING_IND IN VARCHAR2,
4124 X_FEE_CAT IN VARCHAR2,
4125 X_CORRESPONDENCE_CAT IN VARCHAR2,
4126 X_SELF_HELP_GROUP_IND IN VARCHAR2,
4127 X_LOGICAL_DELETE_DT IN DATE,
4128 X_ADM_ADMISSION_APPL_NUMBER IN NUMBER,
4129 X_ADM_NOMINATED_COURSE_CD IN VARCHAR2,
4130 X_ADM_SEQUENCE_NUMBER IN NUMBER,
4131 X_VERSION_NUMBER IN NUMBER,
4132 X_CAL_TYPE IN VARCHAR2,
4133 X_LOCATION_CD IN VARCHAR2,
4134 X_ATTENDANCE_MODE IN VARCHAR2,
4135 X_ATTENDANCE_TYPE IN VARCHAR2,
4136 X_COO_ID IN NUMBER,
4137 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
4138 X_COMMENCEMENT_DT IN DATE,
4139 X_COURSE_ATTEMPT_STATUS IN VARCHAR2,
4140 X_PROGRESSION_STATUS IN VARCHAR2,
4141 X_DERIVED_ATT_TYPE IN VARCHAR2,
4142 X_DERIVED_ATT_MODE IN VARCHAR2,
4143 X_PROVISIONAL_IND IN VARCHAR2,
4144 X_DISCONTINUED_DT IN DATE,
4145 X_DISCONTINUATION_REASON_CD IN VARCHAR2,
4146 X_LAPSED_DT IN DATE,
4147 X_FUNDING_SOURCE IN VARCHAR2,
4148 X_EXAM_LOCATION_CD IN VARCHAR2,
4149 X_DERIVED_COMPLETION_YR IN NUMBER,
4150 X_DERIVED_COMPLETION_PERD IN VARCHAR2,
4151 X_NOMINATED_COMPLETION_YR IN NUMBER,
4152 X_NOMINATED_COMPLETION_PERD IN VARCHAR2,
4153 X_RULE_CHECK_IND IN VARCHAR2,
4154 X_WAIVE_OPTION_CHECK_IND IN VARCHAR2,
4155 X_LAST_RULE_CHECK_DT IN DATE,
4156 X_PUBLISH_OUTCOMES_IND IN VARCHAR2,
4157 X_COURSE_RQRMNT_COMPLETE_IND IN VARCHAR2,
4158 X_COURSE_RQRMNTS_COMPLETE_DT IN DATE,
4159 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
4160 X_OVERRIDE_TIME_LIMITATION IN NUMBER,
4161 X_MODE IN VARCHAR2,
4162 X_LAST_DATE_OF_ATTENDANCE IN DATE,
4163 X_DROPPED_BY IN VARCHAR2,
4164 X_IGS_PR_CLASS_STD_ID IN NUMBER,
4165 X_PRIMARY_PROGRAM_TYPE IN VARCHAR2,
4166 X_PRIMARY_PROG_TYPE_SOURCE IN VARCHAR2,
4167 X_CATALOG_CAL_TYPE IN VARCHAR2,
4168 X_CATALOG_SEQ_NUM IN NUMBER,
4169 X_KEY_PROGRAM IN VARCHAR2,
4170 X_MANUAL_OVR_CMPL_DT_IND IN VARCHAR2,
4171 X_OVERRIDE_CMPL_DT IN DATE,
4172 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
4173 X_ATTRIBUTE1 IN VARCHAR2,
4174 X_ATTRIBUTE2 IN VARCHAR2,
4175 X_ATTRIBUTE3 IN VARCHAR2,
4176 X_ATTRIBUTE4 IN VARCHAR2,
4177 X_ATTRIBUTE5 IN VARCHAR2,
4178 X_ATTRIBUTE6 IN VARCHAR2,
4179 X_ATTRIBUTE7 IN VARCHAR2,
4180 X_ATTRIBUTE8 IN VARCHAR2,
4181 X_ATTRIBUTE9 IN VARCHAR2,
4182 X_ATTRIBUTE10 IN VARCHAR2,
4183 X_ATTRIBUTE11 IN VARCHAR2,
4184 X_ATTRIBUTE12 IN VARCHAR2,
4185 X_ATTRIBUTE13 IN VARCHAR2,
4186 X_ATTRIBUTE14 IN VARCHAR2,
4187 X_ATTRIBUTE15 IN VARCHAR2,
4188 X_ATTRIBUTE16 IN VARCHAR2,
4189 X_ATTRIBUTE17 IN VARCHAR2,
4190 X_ATTRIBUTE18 IN VARCHAR2,
4191 X_ATTRIBUTE19 IN VARCHAR2,
4192 x_ATTRIBUTE20 IN VARCHAR2,
4193 X_FUTURE_DATED_TRANS_FLAG IN VARCHAR2
4194 ) AS
4195 X_LAST_UPDATE_DATE DATE;
4196 X_LAST_UPDATED_BY NUMBER;
4197 X_LAST_UPDATE_LOGIN NUMBER;
4198 X_REQUEST_ID NUMBER;
4199 X_PROGRAM_ID NUMBER;
4200 X_PROGRAM_APPLICATION_ID NUMBER;
4201 X_PROGRAM_UPDATE_DATE DATE;
4202 BEGIN
4203
4204 X_LAST_UPDATE_DATE := SYSDATE;
4205 IF(X_MODE = 'I') THEN
4206 X_LAST_UPDATED_BY := 1;
4207 X_LAST_UPDATE_LOGIN := 0;
4208 ELSIF (X_MODE IN ('R', 'S')) THEN
4209 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4210 IF X_LAST_UPDATED_BY IS NULL THEN
4211 X_LAST_UPDATED_BY := -1;
4212 END IF;
4213 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
4214 IF X_LAST_UPDATE_LOGIN IS NULL THEN
4215 X_LAST_UPDATE_LOGIN := -1;
4216 END IF;
4217 ELSE
4218 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
4219 IGS_GE_MSG_STACK.ADD;
4220 app_exception.raise_exception;
4221 END IF;
4222
4223 Before_DML (
4224 p_action => 'UPDATE',
4225 x_rowid => X_ROWID,
4226 x_person_id => X_PERSON_ID,
4227 x_course_cd => X_COURSE_CD,
4228 x_advanced_standing_ind => X_ADVANCED_STANDING_IND,
4229 x_fee_cat => X_FEE_CAT,
4230 x_correspondence_cat => X_CORRESPONDENCE_CAT,
4231 x_self_help_group_ind => X_SELF_HELP_GROUP_IND,
4232 x_logical_delete_dt => X_LOGICAL_DELETE_DT,
4233 x_adm_admission_appl_number => X_ADM_ADMISSION_APPL_NUMBER,
4234 x_adm_nominated_course_cd => X_ADM_NOMINATED_COURSE_CD,
4235 x_adm_sequence_number => X_ADM_SEQUENCE_NUMBER,
4236 x_version_number => X_VERSION_NUMBER,
4237 x_cal_type => X_CAL_TYPE,
4238 x_location_cd => X_LOCATION_CD,
4239 x_attendance_mode => X_ATTENDANCE_MODE,
4240 x_attendance_type => X_ATTENDANCE_TYPE,
4241 x_coo_id => X_COO_ID,
4242 x_student_confirmed_ind => X_STUDENT_CONFIRMED_IND,
4243 x_commencement_dt => X_COMMENCEMENT_DT,
4244 x_course_attempt_status => X_COURSE_ATTEMPT_STATUS,
4245 x_progression_status => NVL(X_PROGRESSION_STATUS,'GOODSTAND'),
4246 x_derived_att_type => X_DERIVED_ATT_TYPE,
4247 x_derived_att_mode => X_DERIVED_ATT_MODE,
4248 x_provisional_ind => X_PROVISIONAL_IND,
4249 x_discontinued_dt => X_DISCONTINUED_DT,
4250 x_discontinuation_reason_cd => X_DISCONTINUATION_REASON_CD,
4251 x_lapsed_dt => X_LAPSED_DT,
4252 x_funding_source => X_FUNDING_SOURCE,
4253 x_exam_location_cd => X_EXAM_LOCATION_CD,
4254 x_derived_completion_yr => X_DERIVED_COMPLETION_YR,
4255 x_derived_completion_perd => X_DERIVED_COMPLETION_PERD,
4256 x_nominated_completion_yr => X_NOMINATED_COMPLETION_YR,
4257 x_nominated_completion_perd => X_NOMINATED_COMPLETION_PERD,
4258 x_rule_check_ind => X_RULE_CHECK_IND,
4259 x_waive_option_check_ind => X_WAIVE_OPTION_CHECK_IND,
4260 x_last_rule_check_dt => X_LAST_RULE_CHECK_DT,
4261 x_publish_outcomes_ind => X_PUBLISH_OUTCOMES_IND,
4262 x_course_rqrmnt_complete_ind => X_COURSE_RQRMNT_COMPLETE_IND,
4263 x_course_rqrmnts_complete_dt => X_COURSE_RQRMNTS_COMPLETE_DT,
4264 x_s_completed_source_type => X_S_COMPLETED_SOURCE_TYPE,
4265 x_override_time_limitation => X_OVERRIDE_TIME_LIMITATION,
4266 x_creation_date => X_LAST_UPDATE_DATE,
4267 x_created_by => X_LAST_UPDATED_BY,
4268 x_last_update_date => X_LAST_UPDATE_DATE,
4269 x_last_updated_by => X_LAST_UPDATED_BY,
4270 x_last_update_login => X_LAST_UPDATE_LOGIN ,
4271 x_last_date_of_attendance =>X_LAST_DATE_OF_ATTENDANCE,
4272 x_dropped_by => X_DROPPED_BY,
4273 X_IGS_PR_CLASS_STD_ID => X_IGS_PR_CLASS_STD_ID,
4274 x_primary_program_type => X_PRIMARY_PROGRAM_TYPE,
4275 x_primary_prog_type_source => X_PRIMARY_PROG_TYPE_SOURCE,
4276 x_catalog_cal_type => X_CATALOG_CAL_TYPE,
4277 x_catalog_seq_num => X_CATALOG_SEQ_NUM ,
4278 x_key_program => X_KEY_PROGRAM,
4279 x_manual_ovr_cmpl_dt_ind => X_MANUAL_OVR_CMPL_DT_IND,
4280 x_override_cmpl_dt => X_OVERRIDE_CMPL_DT,
4281 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
4282 x_future_dated_trans_flag => NVL(X_FUTURE_DATED_TRANS_FLAG,'N'),
4283 x_attribute1=>X_ATTRIBUTE1,
4284 x_attribute2=>X_ATTRIBUTE2,
4285 x_attribute3=>X_ATTRIBUTE3,
4286 x_attribute4=>X_ATTRIBUTE4,
4287 x_attribute5=>X_ATTRIBUTE5,
4288 x_attribute6=>X_ATTRIBUTE6,
4289 x_attribute7=>X_ATTRIBUTE7,
4290 x_attribute8=>X_ATTRIBUTE8,
4291 x_attribute9=>X_ATTRIBUTE9,
4292 x_attribute10=>X_ATTRIBUTE10,
4293 x_attribute11=>X_ATTRIBUTE11,
4294 x_attribute12=>X_ATTRIBUTE12,
4295 x_attribute13=>X_ATTRIBUTE13,
4296 x_attribute14=>X_ATTRIBUTE14,
4297 x_attribute15=>X_ATTRIBUTE15,
4298 x_attribute16=>X_ATTRIBUTE16,
4299 x_attribute17=>X_ATTRIBUTE17,
4300 x_attribute18=>X_ATTRIBUTE18,
4301 x_attribute19=>X_ATTRIBUTE19,
4302 x_attribute20=>X_ATTRIBUTE20
4303 );
4304
4305 IF(X_MODE = 'R') THEN
4306 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
4307 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
4308 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
4309 IF(X_REQUEST_ID=-1) THEN
4310 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
4311 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
4312 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
4313 ELSE
4314 X_PROGRAM_UPDATE_DATE := SYSDATE;
4315 END IF;
4316 END IF;
4317
4318 IF (x_mode = 'S') THEN
4319 igs_sc_gen_001.set_ctx('R');
4320 END IF;
4321 UPDATE IGS_EN_STDNT_PS_ATT_ALL SET
4322 ADVANCED_STANDING_IND = NEW_REFERENCES.ADVANCED_STANDING_IND,
4323 FEE_CAT = NEW_REFERENCES.FEE_CAT,
4324 CORRESPONDENCE_CAT = NEW_REFERENCES.CORRESPONDENCE_CAT,
4325 SELF_HELP_GROUP_IND = NEW_REFERENCES.SELF_HELP_GROUP_IND,
4326 LOGICAL_DELETE_DT = NEW_REFERENCES.LOGICAL_DELETE_DT,
4327 ADM_ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADM_ADMISSION_APPL_NUMBER,
4328 ADM_NOMINATED_COURSE_CD = NEW_REFERENCES.ADM_NOMINATED_COURSE_CD,
4329 ADM_SEQUENCE_NUMBER = NEW_REFERENCES.ADM_SEQUENCE_NUMBER,
4330 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
4331 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
4332 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
4333 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
4334 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
4335 COO_ID = NEW_REFERENCES.COO_ID,
4336 STUDENT_CONFIRMED_IND = NEW_REFERENCES.STUDENT_CONFIRMED_IND,
4337 COMMENCEMENT_DT = NEW_REFERENCES.COMMENCEMENT_DT,
4338 COURSE_ATTEMPT_STATUS = NEW_REFERENCES.COURSE_ATTEMPT_STATUS,
4339 PROGRESSION_STATUS = NEW_REFERENCES.PROGRESSION_STATUS,
4340 DERIVED_ATT_TYPE = NEW_REFERENCES.DERIVED_ATT_TYPE,
4341 DERIVED_ATT_MODE = NEW_REFERENCES.DERIVED_ATT_MODE,
4342 PROVISIONAL_IND = NEW_REFERENCES.PROVISIONAL_IND,
4343 DISCONTINUED_DT = NEW_REFERENCES.DISCONTINUED_DT,
4344 DISCONTINUATION_REASON_CD = NEW_REFERENCES.DISCONTINUATION_REASON_CD,
4345 LAPSED_DT = NEW_REFERENCES.LAPSED_DT,
4346 FUNDING_SOURCE = NEW_REFERENCES.FUNDING_SOURCE,
4347 EXAM_LOCATION_CD = NEW_REFERENCES.EXAM_LOCATION_CD,
4348 DERIVED_COMPLETION_YR = NEW_REFERENCES.DERIVED_COMPLETION_YR,
4349 DERIVED_COMPLETION_PERD = NEW_REFERENCES.DERIVED_COMPLETION_PERD,
4350 NOMINATED_COMPLETION_YR = NEW_REFERENCES.NOMINATED_COMPLETION_YR,
4351 NOMINATED_COMPLETION_PERD = NEW_REFERENCES.NOMINATED_COMPLETION_PERD,
4352 RULE_CHECK_IND = NEW_REFERENCES.RULE_CHECK_IND,
4353 WAIVE_OPTION_CHECK_IND = NEW_REFERENCES.WAIVE_OPTION_CHECK_IND,
4354 LAST_RULE_CHECK_DT = NEW_REFERENCES.LAST_RULE_CHECK_DT,
4355 PUBLISH_OUTCOMES_IND = NEW_REFERENCES.PUBLISH_OUTCOMES_IND,
4356 COURSE_RQRMNT_COMPLETE_IND = NEW_REFERENCES.COURSE_RQRMNT_COMPLETE_IND,
4357 COURSE_RQRMNTS_COMPLETE_DT = NEW_REFERENCES.COURSE_RQRMNTS_COMPLETE_DT,
4358 S_COMPLETED_SOURCE_TYPE = NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
4359 OVERRIDE_TIME_LIMITATION = NEW_REFERENCES.OVERRIDE_TIME_LIMITATION,
4360 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
4361 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
4362 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
4363 REQUEST_ID = X_REQUEST_ID,
4364 PROGRAM_ID = X_PROGRAM_ID,
4365 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
4366 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
4367 LAST_DATE_OF_ATTENDANCE = NEW_REFERENCES.LAST_DATE_OF_ATTENDANCE,
4368 DROPPED_BY = NEW_REFERENCES.DROPPED_BY,
4369 IGS_PR_CLASS_STD_ID = NEW_REFERENCES.IGS_PR_CLASS_STD_ID,
4370 PRIMARY_PROGRAM_TYPE = NEW_REFERENCES.PRIMARY_PROGRAM_TYPE,
4371 PRIMARY_PROG_TYPE_SOURCE = NEW_REFERENCES.PRIMARY_PROG_TYPE_SOURCE,
4372 CATALOG_CAL_TYPE = NEW_REFERENCES.CATALOG_CAL_TYPE,
4373 CATALOG_SEQ_NUM = NEW_REFERENCES.CATALOG_SEQ_NUM,
4374 KEY_PROGRAM = NEW_REFERENCES.KEY_PROGRAM,
4375 MANUAL_OVR_CMPL_DT_IND = NEW_REFERENCES.MANUAL_OVR_CMPL_DT_IND,
4376 OVERRIDE_CMPL_DT = NEW_REFERENCES.OVERRIDE_CMPL_DT,
4377 ATTRIBUTE_CATEGORY = NEW_REFERENCES.ATTRIBUTE_CATEGORY,
4378 FUTURE_DATED_TRANS_FLAG = NEW_REFERENCES.FUTURE_DATED_TRANS_FLAG,
4379 ATTRIBUTE1 = NEW_REFERENCES.ATTRIBUTE1,
4380 ATTRIBUTE2 = NEW_REFERENCES.ATTRIBUTE2,
4381 ATTRIBUTE3 = NEW_REFERENCES.ATTRIBUTE3,
4382 ATTRIBUTE4 = NEW_REFERENCES.ATTRIBUTE4,
4383 ATTRIBUTE5 = NEW_REFERENCES.ATTRIBUTE5,
4384 ATTRIBUTE6 = NEW_REFERENCES.ATTRIBUTE6,
4385 ATTRIBUTE7 = NEW_REFERENCES.ATTRIBUTE7,
4386 ATTRIBUTE8 = NEW_REFERENCES.ATTRIBUTE8,
4387 ATTRIBUTE9 = NEW_REFERENCES.ATTRIBUTE9,
4388 ATTRIBUTE10 = NEW_REFERENCES.ATTRIBUTE10,
4389 ATTRIBUTE11 = NEW_REFERENCES.ATTRIBUTE11,
4390 ATTRIBUTE12 = NEW_REFERENCES.ATTRIBUTE12,
4391 ATTRIBUTE13 = NEW_REFERENCES.ATTRIBUTE13,
4392 ATTRIBUTE14 = NEW_REFERENCES.ATTRIBUTE14,
4393 ATTRIBUTE15 = NEW_REFERENCES.ATTRIBUTE15,
4394 ATTRIBUTE16 = NEW_REFERENCES.ATTRIBUTE16,
4395 ATTRIBUTE17 = NEW_REFERENCES.ATTRIBUTE17,
4396 ATTRIBUTE18 = NEW_REFERENCES.ATTRIBUTE18,
4397 ATTRIBUTE19 = NEW_REFERENCES.ATTRIBUTE19,
4398 ATTRIBUTE20 = NEW_REFERENCES.ATTRIBUTE20
4399 WHERE ROWID = X_ROWID;
4400 IF (SQL%NOTFOUND) THEN
4401 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
4402 igs_ge_msg_stack.add;
4403 igs_sc_gen_001.unset_ctx('R');
4404 app_exception.raise_exception;
4405 END IF;
4406 IF (x_mode = 'S') THEN
4407 igs_sc_gen_001.unset_ctx('R');
4408 END IF;
4409
4410
4411 After_DML (
4412 p_action => 'UPDATE',
4413 x_rowid => X_ROWID
4414 );
4415
4416
4417 EXCEPTION
4418 WHEN OTHERS THEN
4419 IF (SQLCODE = (-28115)) THEN
4420 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
4421 fnd_message.set_token ('ERR_CD', SQLCODE);
4422 igs_ge_msg_stack.add;
4423 igs_sc_gen_001.unset_ctx('R');
4424 app_exception.raise_exception;
4425 ELSE
4426 igs_sc_gen_001.unset_ctx('R');
4427 RAISE;
4428 END IF;
4429
4430 END UPDATE_ROW;
4431
4432 PROCEDURE ADD_ROW (
4433 X_ROWID IN OUT NOCOPY VARCHAR2,
4434 X_ORG_ID IN NUMBER,
4435 X_PERSON_ID IN NUMBER,
4436 X_COURSE_CD IN VARCHAR2,
4437 X_ADVANCED_STANDING_IND IN VARCHAR2,
4438 X_FEE_CAT IN VARCHAR2,
4439 X_CORRESPONDENCE_CAT IN VARCHAR2,
4440 X_SELF_HELP_GROUP_IND IN VARCHAR2,
4441 X_LOGICAL_DELETE_DT IN DATE,
4442 X_ADM_ADMISSION_APPL_NUMBER IN NUMBER,
4443 X_ADM_NOMINATED_COURSE_CD IN VARCHAR2,
4444 X_ADM_SEQUENCE_NUMBER IN NUMBER,
4445 X_VERSION_NUMBER IN NUMBER,
4446 X_CAL_TYPE IN VARCHAR2,
4447 X_LOCATION_CD IN VARCHAR2,
4448 X_ATTENDANCE_MODE IN VARCHAR2,
4449 X_ATTENDANCE_TYPE IN VARCHAR2,
4450 X_COO_ID IN NUMBER,
4451 X_STUDENT_CONFIRMED_IND IN VARCHAR2,
4452 X_COMMENCEMENT_DT IN DATE,
4453 X_COURSE_ATTEMPT_STATUS IN VARCHAR2,
4454 X_PROGRESSION_STATUS IN VARCHAR2,
4455 X_DERIVED_ATT_TYPE IN VARCHAR2,
4456 X_DERIVED_ATT_MODE IN VARCHAR2,
4457 X_PROVISIONAL_IND IN VARCHAR2,
4458 X_DISCONTINUED_DT IN DATE,
4459 X_DISCONTINUATION_REASON_CD IN VARCHAR2,
4460 X_LAPSED_DT IN DATE,
4461 X_FUNDING_SOURCE IN VARCHAR2,
4462 X_EXAM_LOCATION_CD IN VARCHAR2,
4463 X_DERIVED_COMPLETION_YR IN NUMBER,
4464 X_DERIVED_COMPLETION_PERD IN VARCHAR2,
4465 X_NOMINATED_COMPLETION_YR IN NUMBER,
4466 X_NOMINATED_COMPLETION_PERD IN VARCHAR2,
4467 X_RULE_CHECK_IND IN VARCHAR2,
4468 X_WAIVE_OPTION_CHECK_IND IN VARCHAR2,
4469 X_LAST_RULE_CHECK_DT IN DATE,
4470 X_PUBLISH_OUTCOMES_IND IN VARCHAR2,
4471 X_COURSE_RQRMNT_COMPLETE_IND IN VARCHAR2,
4472 X_COURSE_RQRMNTS_COMPLETE_DT IN DATE,
4473 X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
4474 X_OVERRIDE_TIME_LIMITATION IN NUMBER,
4475 X_MODE IN VARCHAR2,
4476 X_LAST_DATE_OF_ATTENDANCE IN DATE,
4477 X_DROPPED_BY IN VARCHAR2,
4478 X_IGS_PR_CLASS_STD_ID IN NUMBER,
4479 X_PRIMARY_PROGRAM_TYPE IN VARCHAR2,
4480 X_PRIMARY_PROG_TYPE_SOURCE IN VARCHAR2,
4481 X_CATALOG_CAL_TYPE IN VARCHAR2,
4482 X_CATALOG_SEQ_NUM IN NUMBER ,
4483 X_KEY_PROGRAM IN VARCHAR2,
4484 X_MANUAL_OVR_CMPL_DT_IND IN VARCHAR2,
4485 X_OVERRIDE_CMPL_DT IN DATE,
4486 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
4487 X_ATTRIBUTE1 IN VARCHAR2,
4488 X_ATTRIBUTE2 IN VARCHAR2,
4489 X_ATTRIBUTE3 IN VARCHAR2,
4490 X_ATTRIBUTE4 IN VARCHAR2,
4491 X_ATTRIBUTE5 IN VARCHAR2,
4492 X_ATTRIBUTE6 IN VARCHAR2,
4493 X_ATTRIBUTE7 IN VARCHAR2,
4494 X_ATTRIBUTE8 IN VARCHAR2,
4495 X_ATTRIBUTE9 IN VARCHAR2,
4496 X_ATTRIBUTE10 IN VARCHAR2,
4497 X_ATTRIBUTE11 IN VARCHAR2,
4498 X_ATTRIBUTE12 IN VARCHAR2,
4499 X_ATTRIBUTE13 IN VARCHAR2,
4500 X_ATTRIBUTE14 IN VARCHAR2,
4501 X_ATTRIBUTE15 IN VARCHAR2,
4502 X_ATTRIBUTE16 IN VARCHAR2,
4503 X_ATTRIBUTE17 IN VARCHAR2,
4504 X_ATTRIBUTE18 IN VARCHAR2,
4505 X_ATTRIBUTE19 IN VARCHAR2,
4506 x_ATTRIBUTE20 IN VARCHAR2,
4507 X_FUTURE_DATED_TRANS_FLAG IN VARCHAR2
4508 ) AS
4509 CURSOR c1 IS SELECT ROWID FROM IGS_EN_STDNT_PS_ATT_ALL
4510 WHERE PERSON_ID = X_PERSON_ID
4511 AND COURSE_CD = X_COURSE_CD;
4512 BEGIN
4513 OPEN c1;
4514 FETCH c1 INTO X_ROWID;
4515 IF (c1%NOTFOUND) THEN
4516 CLOSE c1;
4517 INSERT_ROW (
4518 X_ROWID,
4519 x_org_id,
4520 X_PERSON_ID,
4521 X_COURSE_CD,
4522 X_ADVANCED_STANDING_IND,
4523 X_FEE_CAT,
4524 X_CORRESPONDENCE_CAT,
4525 X_SELF_HELP_GROUP_IND,
4526 X_LOGICAL_DELETE_DT,
4527 X_ADM_ADMISSION_APPL_NUMBER,
4528 X_ADM_NOMINATED_COURSE_CD,
4529 X_ADM_SEQUENCE_NUMBER,
4530 X_VERSION_NUMBER,
4531 X_CAL_TYPE,
4532 X_LOCATION_CD,
4533 X_ATTENDANCE_MODE,
4534 X_ATTENDANCE_TYPE,
4535 X_COO_ID,
4536 X_STUDENT_CONFIRMED_IND,
4537 X_COMMENCEMENT_DT,
4538 X_COURSE_ATTEMPT_STATUS,
4539 X_PROGRESSION_STATUS,
4540 X_DERIVED_ATT_TYPE,
4541 X_DERIVED_ATT_MODE,
4542 X_PROVISIONAL_IND,
4543 X_DISCONTINUED_DT,
4544 X_DISCONTINUATION_REASON_CD,
4545 X_LAPSED_DT,
4546 X_FUNDING_SOURCE,
4547 X_EXAM_LOCATION_CD,
4548 X_DERIVED_COMPLETION_YR,
4549 X_DERIVED_COMPLETION_PERD,
4550 X_NOMINATED_COMPLETION_YR,
4551 X_NOMINATED_COMPLETION_PERD,
4552 X_RULE_CHECK_IND,
4553 X_WAIVE_OPTION_CHECK_IND,
4554 X_LAST_RULE_CHECK_DT,
4555 X_PUBLISH_OUTCOMES_IND,
4556 X_COURSE_RQRMNT_COMPLETE_IND,
4557 X_COURSE_RQRMNTS_COMPLETE_DT,
4558 X_S_COMPLETED_SOURCE_TYPE,
4559 X_OVERRIDE_TIME_LIMITATION,
4560 X_MODE,
4561 X_LAST_DATE_OF_ATTENDANCE,
4562 X_DROPPED_BY,
4563 X_IGS_PR_CLASS_STD_ID,
4564 X_PRIMARY_PROGRAM_TYPE,
4565 X_PRIMARY_PROG_TYPE_SOURCE,
4566 X_CATALOG_CAL_TYPE,
4567 X_CATALOG_SEQ_NUM,
4568 X_KEY_PROGRAM ,
4569 X_MANUAL_OVR_CMPL_DT_IND,
4570 X_OVERRIDE_CMPL_DT,
4571 X_ATTRIBUTE_CATEGORY,
4572 X_FUTURE_DATED_TRANS_FLAG,
4573 X_ATTRIBUTE1,
4574 X_ATTRIBUTE2,
4575 X_ATTRIBUTE3,
4576 X_ATTRIBUTE4,
4577 X_ATTRIBUTE5,
4578 X_ATTRIBUTE6,
4579 X_ATTRIBUTE7,
4580 X_ATTRIBUTE8,
4581 X_ATTRIBUTE9,
4582 X_ATTRIBUTE10,
4583 X_ATTRIBUTE11,
4584 X_ATTRIBUTE12,
4585 X_ATTRIBUTE13,
4586 X_ATTRIBUTE14,
4587 X_ATTRIBUTE15,
4588 X_ATTRIBUTE16,
4589 X_ATTRIBUTE17,
4590 X_ATTRIBUTE18,
4591 X_ATTRIBUTE19,
4592 X_ATTRIBUTE20
4593 );
4594 RETURN;
4595 END IF;
4596 CLOSE c1;
4597
4598 UPDATE_ROW (
4599 X_ROWID,
4600 X_PERSON_ID,
4601 X_COURSE_CD,
4602 X_ADVANCED_STANDING_IND,
4603 X_FEE_CAT,
4604 X_CORRESPONDENCE_CAT,
4605 X_SELF_HELP_GROUP_IND,
4606 X_LOGICAL_DELETE_DT,
4607 X_ADM_ADMISSION_APPL_NUMBER,
4608 X_ADM_NOMINATED_COURSE_CD,
4609 X_ADM_SEQUENCE_NUMBER,
4610 X_VERSION_NUMBER,
4611 X_CAL_TYPE,
4612 X_LOCATION_CD,
4613 X_ATTENDANCE_MODE,
4614 X_ATTENDANCE_TYPE,
4615 X_COO_ID,
4616 X_STUDENT_CONFIRMED_IND,
4617 X_COMMENCEMENT_DT,
4618 X_COURSE_ATTEMPT_STATUS,
4619 X_PROGRESSION_STATUS,
4620 X_DERIVED_ATT_TYPE,
4621 X_DERIVED_ATT_MODE,
4622 X_PROVISIONAL_IND,
4623 X_DISCONTINUED_DT,
4624 X_DISCONTINUATION_REASON_CD,
4625 X_LAPSED_DT,
4626 X_FUNDING_SOURCE,
4627 X_EXAM_LOCATION_CD,
4628 X_DERIVED_COMPLETION_YR,
4629 X_DERIVED_COMPLETION_PERD,
4630 X_NOMINATED_COMPLETION_YR,
4631 X_NOMINATED_COMPLETION_PERD,
4632 X_RULE_CHECK_IND,
4633 X_WAIVE_OPTION_CHECK_IND,
4634 X_LAST_RULE_CHECK_DT,
4635 X_PUBLISH_OUTCOMES_IND,
4636 X_COURSE_RQRMNT_COMPLETE_IND,
4637 X_COURSE_RQRMNTS_COMPLETE_DT,
4638 X_S_COMPLETED_SOURCE_TYPE,
4639 X_OVERRIDE_TIME_LIMITATION,
4640 X_MODE,
4641 X_LAST_DATE_OF_ATTENDANCE,
4642 X_DROPPED_BY,
4643 X_IGS_PR_CLASS_STD_ID,
4644 X_PRIMARY_PROGRAM_TYPE,
4645 X_PRIMARY_PROG_TYPE_SOURCE,
4646 X_CATALOG_CAL_TYPE,
4647 X_CATALOG_SEQ_NUM,
4648 X_KEY_PROGRAM,
4649 X_MANUAL_OVR_CMPL_DT_IND,
4650 X_OVERRIDE_CMPL_DT,
4651 X_ATTRIBUTE_CATEGORY,
4652 X_FUTURE_DATED_TRANS_FLAG,
4653 X_ATTRIBUTE1,
4654 X_ATTRIBUTE2,
4655 X_ATTRIBUTE3,
4656 X_ATTRIBUTE4,
4657 X_ATTRIBUTE5,
4658 X_ATTRIBUTE6,
4659 X_ATTRIBUTE7,
4660 X_ATTRIBUTE8,
4661 X_ATTRIBUTE9,
4662 X_ATTRIBUTE10,
4663 X_ATTRIBUTE11,
4664 X_ATTRIBUTE12,
4665 X_ATTRIBUTE13,
4666 X_ATTRIBUTE14,
4667 X_ATTRIBUTE15,
4668 X_ATTRIBUTE16,
4669 X_ATTRIBUTE17,
4670 X_ATTRIBUTE18,
4671 X_ATTRIBUTE19,
4672 X_ATTRIBUTE20
4673 );
4674 END ADD_ROW;
4675
4676 PROCEDURE DELETE_ROW (X_ROWID IN VARCHAR2,
4677 x_mode IN VARCHAR2)
4678 AS
4679 BEGIN
4680 Before_DML (
4681 p_action => 'DELETE',
4682 x_rowid => X_ROWID
4683 );
4684 IF (x_mode = 'S') THEN
4685 igs_sc_gen_001.set_ctx('R');
4686 END IF;
4687 DELETE FROM IGS_EN_STDNT_PS_ATT_ALL
4688 WHERE ROWID = X_ROWID;
4689 IF (SQL%NOTFOUND) THEN
4690 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
4691 igs_ge_msg_stack.add;
4692 igs_sc_gen_001.unset_ctx('R');
4693 app_exception.raise_exception;
4694 END IF;
4695 IF (x_mode = 'S') THEN
4696 igs_sc_gen_001.unset_ctx('R');
4697 END IF;
4698
4699
4700 After_DML (
4701 p_action => 'DELETE',
4702 x_rowid => X_ROWID
4703 );
4704
4705 END DELETE_ROW;
4706
4707 PROCEDURE beforerowdelete AS
4708 ------------------------------------------------------------------
4709 --Created by : rnirwani
4710 --Date created: 03-Jan-03
4711 --
4712 --Purpose: Validation to ensure that delation is not allowed
4713 --
4714 --
4715 --Known limitations/enhancements and/or remarks:
4716 --
4717 --Change History:
4718 --Who When What
4719 -------------------------------------------------------------------
4720
4721 CURSOR c_spa_stat (cp_person_id igs_en_stdnt_ps_att_all.person_id%TYPE,
4722 cp_course_cd igs_en_stdnt_ps_att_all.course_cd%TYPE) IS
4723 SELECT course_attempt_status
4724 FROM igs_en_stdnt_ps_att_all
4725 WHERE person_id = cp_person_id
4726 AND course_cd = cp_course_cd;
4727
4728 lv_crs_attmpt_stat igs_en_stdnt_ps_att_all.course_attempt_status%TYPE;
4729 BEGIN
4730
4731 -- Deletion is allowed only in case the record has an attempt status of UNCONFIRM.
4732
4733 OPEN c_spa_stat (old_references.person_id,old_references.course_cd);
4734 FETCH c_spa_stat INTO lv_crs_attmpt_stat;
4735 CLOSE c_spa_stat;
4736
4737 IF lv_crs_attmpt_stat <> 'UNCONFIRM' THEN
4738 FND_MESSAGE.SET_NAME('IGS','IGS_EN_CANDEL_UNCNFRM_SPA');
4739 igs_ge_msg_stack.add;
4740 APP_EXCEPTION.RAISE_EXCEPTION;
4741 END IF;
4742
4743 END beforerowdelete;
4744
4745 PROCEDURE enrp_ins_upd_term_rec( P_ACTION IN VARCHAR2)
4746 IS
4747 ------------------------------------------------------------------
4748 --Created by : ptandon , Oracle India
4749 --Date created: 28-NOV-2003
4750 --
4751 --Purpose: This procedure checks whether to create/update term
4752 -- records.
4753 --
4754 --
4755 --Known limitations/enhancements and/or remarks:
4756 --
4757 --Change History:
4758 --Who When What
4759 --stutta 16-Mar-2004 Passing new parameter p_update_rec in call
4760 -- to igs_en_spa_terms_api.create_update_term_rec.
4761 -- Bug # 3421436
4762 --stutta 7-Jan-2005 Allow term record creation whether any attributes
4763 -- change or not if called from PROGRAM_TRANSFER
4764 --stutta 1-Dec-2005 Delete terms when Unconfirming program irrespective
4765 -- of whether its a PRIMARY program or not.Bug#4752409
4766 -------------------------------------------------------------------
4767
4768 -- Cursor to get the effective term calendar.
4769 CURSOR cur_get_effective_term(cp_person_id igs_en_stdnt_ps_att.person_id%TYPE,
4770 cp_program_cd igs_en_stdnt_ps_att.course_cd%TYPE,
4771 cp_prg_completion_dt igs_en_stdnt_ps_att.course_rqrmnts_complete_dt%TYPE) IS
4772 SELECT ci.cal_type, ci.sequence_number
4773 FROM igs_ca_inst ci,
4774 igs_ca_inst_rel cir,
4775 igs_ca_type ct,
4776 igs_en_stdnt_ps_att sca,
4777 igs_ca_stat cs
4778 WHERE sca.person_id = cp_person_id AND
4779 sca.course_cd = cp_program_cd AND
4780 cir.sup_cal_type = sca.cal_type AND
4781 ci.cal_type = cir.sub_cal_type AND
4782 ci.sequence_number = cir.sub_ci_sequence_number AND
4783 ct.cal_type = ci.cal_type AND
4784 ct.s_cal_cat = 'LOAD' AND
4785 cs.cal_status = ci.cal_status AND
4786 cs.s_cal_status = 'ACTIVE' AND
4787 igs_en_gen_015.get_effective_census_date(ci.cal_type,ci.sequence_number,NULL,NULL) > cp_prg_completion_dt
4788 ORDER BY ci.start_dt;
4789
4790 l_message_name VARCHAR2(100);
4791 l_create_update_term BOOLEAN;
4792 l_prg_completion_date igs_en_stdnt_ps_att_all.course_rqrmnts_complete_dt%TYPE;
4793 l_term_cal_type igs_ca_inst.cal_type%TYPE;
4794 l_term_ci_seq_num igs_ca_inst.sequence_number%TYPE;
4795
4796 BEGIN
4797
4798 -- If the system is in program model or the program attempt is primary.
4799 IF NVL(FND_PROFILE.VALUE('CAREER_MODEL_ENABLED'),'N') = 'N' OR new_references.primary_program_type = 'PRIMARY' THEN
4800
4801 l_term_cal_type := NULL;
4802 l_term_ci_seq_num := NULL;
4803 l_create_update_term := FALSE;
4804
4805 IF (P_ACTION = 'UPDATE') THEN
4806 IF (new_references.course_attempt_status = 'COMPLETED' AND new_references.primary_program_type = 'PRIMARY') THEN
4807 OPEN cur_get_effective_term(new_references.person_id,
4808 new_references.course_cd,
4809 new_references.course_rqrmnts_complete_dt);
4810 FETCH cur_get_effective_term INTO l_term_cal_type,l_term_ci_seq_num;
4811 CLOSE cur_get_effective_term;
4812 IF (l_term_cal_type IS NOT NULL AND l_term_ci_seq_num IS NOT NULL) THEN
4813 igs_en_spa_terms_api.create_update_term_rec(
4814 p_person_id => new_references.person_id,
4815 p_program_cd => new_references.course_cd,
4816 p_term_cal_type => l_term_cal_type,
4817 p_term_sequence_number => l_term_ci_seq_num,
4818 p_ripple_frwrd => FALSE, -- ripple forward
4819 p_message_name => l_message_name,
4820 p_update_rec => TRUE);
4821 END IF;
4822
4823 END IF;
4824 END IF;
4825 END IF;
4826
4827 -- if course attempt status is changing (updated) to UNCONFIRM
4828 IF (new_references.course_attempt_status = 'UNCONFIRM' AND old_references.course_attempt_status IS NOT NULL
4829 AND old_references.course_attempt_status <> new_references.course_attempt_status ) THEN
4830 -- For a term which is moving from a confirmed status to an unconfirmed status
4831 -- Delete all future term records from the current term for an unconfirmed program.
4832 igs_en_spa_terms_api.delete_terms_for_program(
4833 p_person_id => new_references.person_id,
4834 p_program_cd => new_references.course_cd);
4835 END IF;
4836
4837
4838
4839 END enrp_ins_upd_term_rec;
4840
4841 END Igs_En_Stdnt_Ps_Att_Pkg;
[Home] [Help]