[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_SU_ATTEMPT_H_PKG
Source
1 package body IGS_EN_SU_ATTEMPT_H_PKG AS
2 /* $Header: IGSEI37B.pls 120.1 2006/05/10 00:27:48 bdeviset noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_EN_SU_ATTEMPT_H_ALL%RowType;
6 new_references IGS_EN_SU_ATTEMPT_H_ALL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_version_number IN NUMBER DEFAULT NULL,
14 x_cal_type IN VARCHAR2 DEFAULT NULL,
15 x_ci_sequence_number IN NUMBER DEFAULT NULL,
16 x_hist_start_dt IN DATE DEFAULT NULL,
17 x_hist_end_dt IN DATE DEFAULT NULL,
18 x_hist_who IN NUMBER DEFAULT NULL,
19 x_location_cd IN VARCHAR2 DEFAULT NULL,
20 x_unit_class IN VARCHAR2 DEFAULT NULL,
21 x_enrolled_dt IN DATE DEFAULT NULL,
22 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
23 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
24 x_aus_description IN VARCHAR2 DEFAULT NULL,
25 x_discontinued_dt IN DATE DEFAULT NULL,
26 x_rule_waived_dt IN DATE DEFAULT NULL,
27 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
28 x_no_assessment_ind IN VARCHAR2 DEFAULT NULL,
29 x_exam_location_cd IN VARCHAR2 DEFAULT NULL,
30 x_elo_description IN VARCHAR2 DEFAULT NULL,
31 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
32 x_sup_version_number IN NUMBER DEFAULT NULL,
33 x_alternative_title IN VARCHAR2 DEFAULT NULL,
34 x_override_enrolled_cp IN NUMBER DEFAULT NULL,
35 x_override_eftsu IN NUMBER DEFAULT NULL,
36 x_override_achievable_cp IN NUMBER DEFAULT NULL,
37 x_override_outcome_due_dt IN DATE DEFAULT NULL,
38 x_override_credit_reason IN VARCHAR2 DEFAULT NULL,
39 x_dcnt_reason_cd IN VARCHAR2 DEFAULT NULL,
40 x_creation_date IN DATE DEFAULT NULL,
41 x_created_by IN NUMBER DEFAULT NULL,
42 x_last_update_date IN DATE DEFAULT NULL,
43 x_last_updated_by IN NUMBER DEFAULT NULL,
44 x_last_update_login IN NUMBER DEFAULT NULL,
45 x_org_id IN NUMBER DEFAULT NULL,
46 x_grading_schema_code IN VARCHAR2 DEFAULT NULL,
47 x_enr_method_type IN VARCHAR2 DEFAULT NULL,
48 x_administrative_priority IN NUMBER DEFAULT NULL,
49 x_waitlist_dt IN DATE DEFAULT NULL,
50 x_request_id IN NUMBER DEFAULT NULL,
51 x_program_application_id IN NUMBER DEFAULT NULL,
52 x_program_id IN NUMBER DEFAULT NULL,
53 x_program_update_date IN DATE DEFAULT NULL,
54 x_cart IN VARCHAR2 DEFAULT NULL,
55 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
56 x_rsv_seat_ext_id IN NUMBER DEFAULT NULL,
57 x_gs_version_number IN NUMBER DEFAULT NULL,
58 x_failed_unit_rule IN VARCHAR2 DEFAULT NULL,
59 x_deg_aud_detail_id IN NUMBER DEFAULT NULL,
60 x_uoo_id IN NUMBER DEFAULT NULL,
61 x_core_indicator_code IN VARCHAR2 DEFAULT NULL
62 ) AS
63 CURSOR cur_old_ref_values IS
64 SELECT *
65 FROM IGS_EN_SU_ATTEMPT_H_ALL
66 WHERE rowid = x_rowid;
67 BEGIN
68 l_rowid := x_rowid;
69 -- Code for setting the Old and New Reference Values.
70 -- Populate Old Values.
71 Open cur_old_ref_values;
72 Fetch cur_old_ref_values INTO old_references;
73 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
74 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
75 IGS_GE_MSG_STACK.ADD;
76 Close cur_old_ref_values;
77 APP_EXCEPTION.RAISE_EXCEPTION;
78
79 Return;
80 END IF;
81 Close cur_old_ref_values;
82 -- Populate New Values.
83 new_references.person_id := x_person_id;
84 new_references.course_cd := x_course_cd;
85 new_references.unit_cd := x_unit_cd;
86 new_references.version_number := x_version_number;
87 new_references.cal_type := x_cal_type;
88 new_references.ci_sequence_number := x_ci_sequence_number;
89 new_references.hist_start_dt := x_hist_start_dt;
90 new_references.hist_end_dt := x_hist_end_dt;
91 new_references.hist_who := x_hist_who;
92 new_references.location_cd := x_location_cd;
93 new_references.unit_class := x_unit_class;
94 new_references.enrolled_dt := x_enrolled_dt;
95 new_references.unit_attempt_status := x_unit_attempt_status;
96 new_references.administrative_unit_status := x_administrative_unit_status;
97 new_references.aus_description := x_aus_description;
98 new_references.discontinued_dt := x_discontinued_dt;
99 new_references.rule_waived_dt := x_rule_waived_dt;
100 new_references.rule_waived_person_id := x_rule_waived_person_id;
101 new_references.no_assessment_ind := x_no_assessment_ind;
102 new_references.exam_location_cd := x_exam_location_cd;
103 new_references.elo_description := x_elo_description;
104 new_references.sup_unit_cd := x_sup_unit_cd;
105 new_references.sup_version_number := x_sup_version_number;
106 new_references.alternative_title := x_alternative_title;
107 new_references.override_enrolled_cp := x_override_enrolled_cp;
108 new_references.override_eftsu := x_override_eftsu;
109 new_references.override_achievable_cp := x_override_achievable_cp;
110 new_references.override_outcome_due_dt := x_override_outcome_due_dt;
111 new_references.override_credit_reason := x_override_credit_reason;
112 new_references.uoo_id := x_uoo_id;
113 /* enhancement */
114 new_references.dcnt_reason_cd := x_dcnt_reason_cd;
115 IF (p_action = 'UPDATE') THEN
116 new_references.creation_date := old_references.creation_date;
117 new_references.created_by := old_references.created_by;
118 ELSE
119 new_references.creation_date := x_creation_date;
120 new_references.created_by := x_created_by;
121 END IF;
122 new_references.last_update_date := x_last_update_date;
123 new_references.last_updated_by := x_last_updated_by;
124 new_references.last_update_login := x_last_update_login;
125 new_references.org_id := x_org_id;
126 new_references.grading_schema_code:=x_grading_schema_code;
127 new_references.enr_method_type:=x_enr_method_type;
128 new_references.administrative_priority := x_administrative_priority;
129 new_references.waitlist_dt := x_waitlist_dt;
130 new_references.request_id := x_request_id;
131 new_references.program_application_id := x_program_application_id;
132 new_references.program_id := x_program_id;
133 new_references.program_update_date := x_program_update_date;
134 new_references.cart := x_cart;
135 new_references.org_unit_cd := x_org_unit_cd;
136 new_references.rsv_seat_ext_id := x_rsv_seat_ext_id;
137 new_references.gs_version_number := x_gs_version_number;
138 new_references.failed_unit_rule := x_failed_unit_rule;
139 new_references.deg_aud_detail_id := x_deg_aud_detail_id;
140 new_references.core_indicator_code := x_core_indicator_code;
141
142 END Set_Column_Values;
143
144 PROCEDURE Check_Parent_Existance AS
145 BEGIN
146 IF (((old_references.person_id = new_references.person_id) AND
147 (old_references.course_cd = new_references.course_cd)) OR
148 ((new_references.person_id IS NULL) OR
149 (new_references.course_cd IS NULL))) THEN
150 NULL;
151 ELSIF NOT IGS_EN_STDNT_PS_ATT_Pkg.Get_PK_For_Validation (
152 new_references.person_id,
153 new_references.course_cd
154 )THEN
155 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
156 IGS_GE_MSG_STACK.ADD;
157 APP_EXCEPTION.RAISE_EXCEPTION;
158 END IF;
159
160 IF (((old_references.person_id = new_references.person_id) AND
161 (old_references.course_cd = new_references.course_cd) AND
162 (old_references.uoo_id = new_references.uoo_id)) OR
163 ((new_references.person_id IS NULL) OR
164 (new_references.course_cd IS NULL) OR
165 (new_references.uoo_id IS NULL))) THEN
166 NULL;
167 ELSIF NOT IGS_EN_SU_ATTEMPT_Pkg.Get_PK_For_Validation (
168 new_references.person_id,
169 new_references.course_cd,
170 new_references.uoo_id
171 )THEN
172 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
173 IGS_GE_MSG_STACK.ADD;
174 APP_EXCEPTION.RAISE_EXCEPTION;
175 END IF;
176
177
178 END Check_Parent_Existance;
179 -------------------------------------------------------------------------------------------
180 --Change History:
181 --Who When What
182 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
183 -------------------------------------------------------------------------------------------
184 FUNCTION Get_PK_For_Validation (
185 x_person_id IN NUMBER,
186 x_course_cd IN VARCHAR2,
187 x_hist_start_dt IN DATE,
188 x_uoo_id IN NUMBER
189 ) RETURN BOOLEAN AS
190 CURSOR cur_rowid IS
191 SELECT rowid
192 FROM IGS_EN_SU_ATTEMPT_H_ALL
193 WHERE person_id = x_person_id
194 AND course_cd = x_course_cd
195 AND hist_start_dt = x_hist_start_dt
196 AND uoo_id = x_uoo_id
197 FOR UPDATE NOWAIT;
198 lv_rowid cur_rowid%RowType;
199 BEGIN
200 Open cur_rowid;
201 Fetch cur_rowid INTO lv_rowid;
202 IF (cur_rowid%FOUND) THEN
203 Close cur_rowid;
204 Return (TRUE);
205 ELSE
206 Close cur_rowid;
207 Return (FALSE);
208 END IF;
209 END Get_PK_For_Validation;
210 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
211 x_person_id IN NUMBER,
212 x_course_cd IN VARCHAR2
213 ) AS
214 CURSOR cur_rowid IS
215 SELECT rowid
216 FROM IGS_EN_SU_ATTEMPT_H_ALL
217 WHERE person_id = x_person_id
218 AND course_cd = x_course_cd ;
219 lv_rowid cur_rowid%RowType;
220 BEGIN
221 Open cur_rowid;
222 Fetch cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SUAH_SCA_FK');
225 IGS_GE_MSG_STACK.ADD;
226 Close cur_rowid;
227 APP_EXCEPTION.RAISE_EXCEPTION;
228
229 Return;
230 END IF;
231 Close cur_rowid;
232 END GET_FK_IGS_EN_STDNT_PS_ATT;
233
234 PROCEDURE GET_FK_IGS_EN_SU_ATTEMPT (
235 x_person_id IN NUMBER,
236 x_course_cd IN VARCHAR2,
237 x_uoo_id IN NUMBER
238 ) AS
239
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_EN_SU_ATTEMPT_H_ALL
243 WHERE person_id = x_person_id
244 AND course_cd = x_course_cd
245 AND uoo_id = x_uoo_id;
246
247 lv_rowid cur_rowid%RowType;
248
249 BEGIN
250
251 Open cur_rowid;
252 Fetch cur_rowid INTO lv_rowid;
253 IF (cur_rowid%FOUND) THEN
254 Close cur_rowid;
255 Fnd_Message.Set_Name ('IGS', 'IGS_PS_SUT_SUA_TRANSFER_FK');
256 IGS_GE_MSG_STACK.ADD;
257 App_Exception.Raise_Exception;
258 Return;
259 END IF;
260 Close cur_rowid;
261
262 END GET_FK_IGS_EN_SU_ATTEMPT;
263
264 PROCEDURE Before_DML (
265 p_action IN VARCHAR2,
266 x_rowid IN VARCHAR2 DEFAULT NULL,
267 x_person_id IN NUMBER DEFAULT NULL,
268 x_course_cd IN VARCHAR2 DEFAULT NULL,
269 x_unit_cd IN VARCHAR2 DEFAULT NULL,
270 x_version_number IN NUMBER DEFAULT NULL,
271 x_cal_type IN VARCHAR2 DEFAULT NULL,
272 x_ci_sequence_number IN NUMBER DEFAULT NULL,
273 x_hist_start_dt IN DATE DEFAULT NULL,
274 x_hist_end_dt IN DATE DEFAULT NULL,
275 x_hist_who IN NUMBER DEFAULT NULL,
276 x_location_cd IN VARCHAR2 DEFAULT NULL,
277 x_unit_class IN VARCHAR2 DEFAULT NULL,
278 x_enrolled_dt IN DATE DEFAULT NULL,
279 x_unit_attempt_status IN VARCHAR2 DEFAULT NULL,
280 x_administrative_unit_status IN VARCHAR2 DEFAULT NULL,
281 x_aus_description IN VARCHAR2 DEFAULT NULL,
282 x_discontinued_dt IN DATE DEFAULT NULL,
283 x_rule_waived_dt IN DATE DEFAULT NULL,
284 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
285 x_no_assessment_ind IN VARCHAR2 DEFAULT NULL,
286 x_exam_location_cd IN VARCHAR2 DEFAULT NULL,
287 x_elo_description IN VARCHAR2 DEFAULT NULL,
288 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
289 x_sup_version_number IN NUMBER DEFAULT NULL,
290 x_alternative_title IN VARCHAR2 DEFAULT NULL,
291 x_override_enrolled_cp IN NUMBER DEFAULT NULL,
292 x_override_eftsu IN NUMBER DEFAULT NULL,
293 x_override_achievable_cp IN NUMBER DEFAULT NULL,
294 x_override_outcome_due_dt IN DATE DEFAULT NULL,
295 x_override_credit_reason IN VARCHAR2 DEFAULT NULL,
296 x_dcnt_reason_cd IN VARCHAR2 DEFAULT NULL,
297 x_creation_date IN DATE DEFAULT NULL,
298 x_created_by IN NUMBER DEFAULT NULL,
299 x_last_update_date IN DATE DEFAULT NULL,
300 x_last_updated_by IN NUMBER DEFAULT NULL,
301 x_last_update_login IN NUMBER DEFAULT NULL,
302 x_org_id IN NUMBER DEFAULT NULL,
303 x_grading_schema_code IN VARCHAR2 DEFAULT NULL,
304 x_enr_method_type IN VARCHAR2 DEFAULT NULL,
305 x_administrative_priority IN NUMBER DEFAULT NULL,
306 x_waitlist_dt IN DATE DEFAULT NULL,
307 x_request_id IN NUMBER DEFAULT NULL,
308 x_program_application_id IN NUMBER DEFAULT NULL,
309 x_program_id IN NUMBER DEFAULT NULL,
310 x_program_update_date IN DATE DEFAULT NULL,
311 x_cart IN VARCHAR2 DEFAULT NULL,
312 x_org_unit_cd IN VARCHAR2 DEFAULT NULL,
313 x_rsv_seat_ext_id IN NUMBER DEFAULT NULL,
314 x_gs_version_number IN NUMBER DEFAULT NULL,
315 x_failed_unit_rule IN VARCHAR2 DEFAULT NULL,
316 x_deg_aud_detail_id IN NUMBER DEFAULT NULL,
317 x_uoo_id IN NUMBER DEFAULT NULL,
318 x_core_indicator_code IN VARCHAR2 DEFAULT NULL
319 ) AS
320
321 -------------------------------------------------------------------------------------------
322 --Change History:
323 --Who When What
324 --bdeviset 22-Mar-2005 Whenver history record is created for waitlisted status the history start date
325 -- is set to the history end date of the history record having unit attempt status
326 -- as unconfirmed.Bug# 4253954
327 --bdeviset 05-MAY-2006 Modified for bug# 5208930
328 -------------------------------------------------------------------------------------------
329
330 CURSOR c_hist_end_dt(cp_person_id IN NUMBER,
331 cp_course_cd IN VARCHAR2,
332 cp_uoo_id IN NUMBER) IS
333 SELECT HIST_END_DT
334 FROM IGS_EN_SU_ATTEMPT_H_ALL
335 WHERE person_id = cp_person_id
336 AND course_cd = cp_course_cd
337 AND uoo_id = cp_uoo_id
338 ORDER BY HIST_END_DT DESC
339 FOR UPDATE;
340
341 l_hist_end_dt IGS_EN_SU_ATTEMPT_H_ALL.hist_end_dt%TYPE;
342
343 BEGIN
344 Set_Column_Values (
345 p_action,
346 x_rowid,
347 x_person_id,
348 x_course_cd,
349 x_unit_cd,
350 x_version_number,
351 x_cal_type,
352 x_ci_sequence_number,
353 x_hist_start_dt,
354 x_hist_end_dt,
355 x_hist_who,
356 x_location_cd,
357 x_unit_class,
358 x_enrolled_dt,
359 x_unit_attempt_status,
360 x_administrative_unit_status,
361 x_aus_description,
362 x_discontinued_dt,
363 x_rule_waived_dt,
364 x_rule_waived_person_id,
365 x_no_assessment_ind,
366 x_exam_location_cd,
367 x_elo_description,
368 x_sup_unit_cd,
369 x_sup_version_number,
370 x_alternative_title,
371 x_override_enrolled_cp,
372 x_override_eftsu,
373 x_override_achievable_cp,
374 x_override_outcome_due_dt,
375 x_override_credit_reason,
376 x_dcnt_reason_cd,
377 x_creation_date,
378 x_created_by,
379 x_last_update_date,
380 x_last_updated_by,
381 x_last_update_login,
382 x_org_id ,
383 x_grading_schema_code,
384 x_enr_method_type,
385 x_administrative_priority,
386 x_waitlist_dt,
387 x_request_id,
388 x_program_application_id ,
389 x_program_id,
390 x_program_update_date,
391 x_cart,
392 x_org_unit_cd,
393 x_rsv_seat_ext_id ,
394 x_gs_version_number,
395 x_failed_unit_rule,
396 x_deg_aud_detail_id,
397 x_uoo_id,
398 x_core_indicator_code
399 );
400 IF (p_action = 'INSERT') THEN
401
402
403 OPEN c_hist_end_dt(NEW_REFERENCES.person_id,
404 NEW_REFERENCES.course_cd ,
405 NEW_REFERENCES.uoo_id);
406 FETCH c_hist_end_dt INTO l_hist_end_dt;
407
408 IF c_hist_end_dt%FOUND THEN
409 NEW_REFERENCES.hist_start_dt := l_hist_end_dt;
410 IF (NEW_REFERENCES.hist_start_dt = NEW_REFERENCES.hist_end_dt) THEN
411 NEW_REFERENCES.hist_start_dt := NEW_REFERENCES.hist_start_dt - 1/(60*24*60);
412 END IF;
413 END IF;
414 CLOSE c_hist_end_dt;
415
416 -- Call all the procedures related to Before Insert.
417
418 IF Get_PK_For_Validation (
419 NEW_REFERENCES.person_id,
420 NEW_REFERENCES.course_cd ,
421 NEW_REFERENCES.hist_start_dt,
422 NEW_REFERENCES.uoo_id ) THEN
423 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
424 IGS_GE_MSG_STACK.ADD;
425 APP_EXCEPTION.RAISE_EXCEPTION;
426 END IF;
427
428 Check_Constraints;
429
430 Check_Parent_Existance;
431 ELSIF (p_action = 'UPDATE') THEN
432 -- Call all the procedures related to Before Update.
433
434
435 Check_Constraints;
436 Check_Parent_Existance;
437
438 ELSIF (p_action = 'VALIDATE_INSERT') THEN
439 IF Get_PK_For_Validation (
440 NEW_REFERENCES.person_id,
441 NEW_REFERENCES.course_cd,
442 NEW_REFERENCES.hist_start_dt ,
443 NEW_REFERENCES.uoo_id
444 ) THEN
445 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
446 IGS_GE_MSG_STACK.ADD;
447 APP_EXCEPTION.RAISE_EXCEPTION;
448 END IF;
449
450 Check_Constraints;
451 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
452
453 Check_Constraints;
454
455
456 END IF;
457 END Before_DML;
458
459 procedure INSERT_ROW (
460 X_ROWID in out NOCOPY VARCHAR2,
461 X_PERSON_ID in NUMBER,
462 X_COURSE_CD in VARCHAR2,
466 X_HIST_START_DT in DATE,
463 X_UNIT_CD in VARCHAR2,
464 X_CAL_TYPE in VARCHAR2,
465 X_CI_SEQUENCE_NUMBER in NUMBER,
467 X_ELO_DESCRIPTION in VARCHAR2,
468 X_SUP_UNIT_CD in VARCHAR2,
469 X_SUP_VERSION_NUMBER in NUMBER,
470 X_ALTERNATIVE_TITLE in VARCHAR2,
471 X_OVERRIDE_ENROLLED_CP in NUMBER,
472 X_OVERRIDE_EFTSU in NUMBER,
473 X_OVERRIDE_ACHIEVABLE_CP in NUMBER,
474 X_OVERRIDE_OUTCOME_DUE_DT in DATE,
475 X_OVERRIDE_CREDIT_REASON in VARCHAR2,
476 X_VERSION_NUMBER in NUMBER,
477 X_HIST_END_DT in DATE,
478 X_HIST_WHO in NUMBER,
479 X_LOCATION_CD in VARCHAR2,
480 X_UNIT_CLASS in VARCHAR2,
481 X_ENROLLED_DT in DATE,
482 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
483 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
484 X_AUS_DESCRIPTION in VARCHAR2,
485 X_DISCONTINUED_DT in DATE,
486 X_RULE_WAIVED_DT in DATE,
487 X_RULE_WAIVED_PERSON_ID in NUMBER,
488 X_NO_ASSESSMENT_IND in VARCHAR2,
489 X_EXAM_LOCATION_CD in VARCHAR2,
490 X_DCNT_REASON_CD in VARCHAR2,
491 X_MODE in VARCHAR2 default 'R',
492 X_ORG_ID IN NUMBER,
493 X_GRADING_SCHEMA_CODE IN VARCHAR2 DEFAULT NULL,
494 X_ENR_METHOD_TYPE IN VARCHAR2 DEFAULT NULL,
495 X_ADMINISTRATIVE_PRIORITY IN NUMBER DEFAULT NULL,
496 X_WAITLIST_DT IN DATE DEFAULT NULL,
497 X_REQUEST_ID IN NUMBER DEFAULT NULL,
498 X_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
499 X_PROGRAM_ID IN NUMBER DEFAULT NULL,
500 X_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
501 X_CART IN VARCHAR2 DEFAULT NULL,
502 X_ORG_UNIT_CD IN VARCHAR2 DEFAULT NULL,
503 X_RSV_SEAT_EXT_ID IN NUMBER DEFAULT NULL,
504 X_GS_VERSION_NUMBER IN NUMBER DEFAULT NULL,
505 X_FAILED_UNIT_RULE IN VARCHAR2 DEFAULT NULL,
506 X_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
507 X_UOO_ID IN NUMBER,
508 X_CORE_INDICATOR_CODE IN VARCHAR2 DEFAULT NULL
509 ) AS
510 cursor C is select ROWID from IGS_EN_SU_ATTEMPT_H_ALL
511 where PERSON_ID = X_PERSON_ID
512 and COURSE_CD = X_COURSE_CD
513 and HIST_START_DT = new_references.HIST_START_DT
514 and UOO_ID = X_UOO_ID;
515 X_LAST_UPDATE_DATE DATE;
516 X_LAST_UPDATED_BY NUMBER;
517 X_LAST_UPDATE_LOGIN NUMBER;
518 begin
519 X_LAST_UPDATE_DATE := SYSDATE;
520 if(X_MODE = 'I') then
521 X_LAST_UPDATED_BY := 1;
522 X_LAST_UPDATE_LOGIN := 0;
523 elsif (X_MODE = 'R') then
524 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
525 if X_LAST_UPDATED_BY is NULL then
526 X_LAST_UPDATED_BY := -1;
527 end if;
528 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
529 if X_LAST_UPDATE_LOGIN is NULL then
530 X_LAST_UPDATE_LOGIN := -1;
531 end if;
532 else
533 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
534 IGS_GE_MSG_STACK.ADD;
535 APP_EXCEPTION.RAISE_EXCEPTION;
536 end if;
537 before_DML(
538 p_action=>'INSERT',
539 x_rowid=>X_ROWID,
540 x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
541 x_alternative_title=>X_ALTERNATIVE_TITLE,
542 x_aus_description=>X_AUS_DESCRIPTION,
543 x_cal_type=>X_CAL_TYPE,
544 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
545 x_course_cd=>X_COURSE_CD,
546 x_discontinued_dt=>X_DISCONTINUED_DT,
547 x_elo_description=>X_ELO_DESCRIPTION,
548 x_enrolled_dt=>X_ENROLLED_DT,
549 x_exam_location_cd=>X_EXAM_LOCATION_CD,
550 x_hist_end_dt=>X_HIST_END_DT,
551 x_hist_start_dt=>X_HIST_START_DT,
552 x_hist_who=>X_HIST_WHO,
553 x_location_cd=>X_LOCATION_CD,
554 x_no_assessment_ind=>X_NO_ASSESSMENT_IND,
555 x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
556 x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
557 x_override_eftsu=>X_OVERRIDE_EFTSU,
558 x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
559 x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
560 x_person_id=>X_PERSON_ID,
561 x_rule_waived_dt=>X_RULE_WAIVED_DT,
562 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
563 x_sup_unit_cd=>X_SUP_UNIT_CD,
564 x_sup_version_number=>X_SUP_VERSION_NUMBER,
565 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
566 x_unit_cd=>X_UNIT_CD,
567 x_unit_class=>X_UNIT_CLASS,
568 x_version_number=>X_VERSION_NUMBER,
569 x_dcnt_reason_cd => X_DCNT_REASON_CD,
570 x_creation_date=>X_LAST_UPDATE_DATE,
571 x_created_by=>X_LAST_UPDATED_BY,
572 x_last_update_date=>X_LAST_UPDATE_DATE,
573 x_last_updated_by=>X_LAST_UPDATED_BY,
574 x_last_update_login=>X_LAST_UPDATE_LOGIN,
575 x_org_id => igs_ge_gen_003.get_org_id ,
576 x_grading_schema_code =>X_GRADING_SCHEMA_CODE,
577 x_enr_method_type =>X_ENR_METHOD_TYPE ,
578 x_administrative_priority => X_ADMINISTRATIVE_PRIORITY,
579 x_waitlist_dt => X_WAITLIST_DT,
580 x_request_id => X_REQUEST_ID,
581 x_program_application_id => X_PROGRAM_APPLICATION_ID ,
582 x_program_id => X_PROGRAM_ID,
583 x_program_update_date => X_PROGRAM_UPDATE_DATE,
584 x_cart => X_CART,
585 x_org_unit_cd => X_ORG_UNIT_CD,
586 x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
587 x_gs_version_number => X_GS_VERSION_NUMBER,
588 x_failed_unit_rule => X_FAILED_UNIT_RULE,
589 x_deg_aud_detail_id => X_DEG_AUD_DETAIL_ID,
590 x_uoo_id => X_UOO_ID,
594 ELO_DESCRIPTION,
591 x_core_indicator_code => X_CORE_INDICATOR_CODE
592 );
593 insert into IGS_EN_SU_ATTEMPT_H_ALL (
595 SUP_UNIT_CD,
596 SUP_VERSION_NUMBER,
597 ALTERNATIVE_TITLE,
598 OVERRIDE_ENROLLED_CP,
599 OVERRIDE_EFTSU,
600 OVERRIDE_ACHIEVABLE_CP,
601 OVERRIDE_OUTCOME_DUE_DT,
602 OVERRIDE_CREDIT_REASON,
603 PERSON_ID,
604 COURSE_CD,
605 UNIT_CD,
606 VERSION_NUMBER,
607 CAL_TYPE,
608 CI_SEQUENCE_NUMBER,
609 HIST_START_DT,
610 HIST_END_DT,
611 HIST_WHO,
612 LOCATION_CD,
613 UNIT_CLASS,
614 ENROLLED_DT,
615 UNIT_ATTEMPT_STATUS,
616 ADMINISTRATIVE_UNIT_STATUS,
617 AUS_DESCRIPTION,
618 DISCONTINUED_DT,
619 RULE_WAIVED_DT,
620 RULE_WAIVED_PERSON_ID,
621 NO_ASSESSMENT_IND,
622 EXAM_LOCATION_CD,
623 DCNT_REASON_CD,
624 CREATION_DATE,
625 CREATED_BY,
626 LAST_UPDATE_DATE,
627 LAST_UPDATED_BY,
628 LAST_UPDATE_LOGIN,
629 org_id,
630 GRADING_SCHEMA_CODE,
631 ENR_METHOD_TYPE,
632 ADMINISTRATIVE_PRIORITY,
633 WAITLIST_DT,
634 REQUEST_ID,
635 PROGRAM_APPLICATION_ID,
636 PROGRAM_ID,
637 PROGRAM_UPDATE_DATE ,
638 CART,
639 ORG_UNIT_CD,
640 RSV_SEAT_EXT_ID,
641 GS_VERSION_NUMBER,
642 FAILED_UNIT_RULE,
643 DEG_AUD_DETAIL_ID,
644 UOO_ID,
645 CORE_INDICATOR_CODE
646 ) values (
647 new_references.ELO_DESCRIPTION,
648 new_references.SUP_UNIT_CD,
649 new_references.SUP_VERSION_NUMBER,
650 new_references.ALTERNATIVE_TITLE,
651 new_references.OVERRIDE_ENROLLED_CP,
652 new_references.OVERRIDE_EFTSU,
653 new_references.OVERRIDE_ACHIEVABLE_CP,
654 new_references.OVERRIDE_OUTCOME_DUE_DT,
655 new_references.OVERRIDE_CREDIT_REASON,
656 new_references.PERSON_ID,
657 new_references.COURSE_CD,
658 new_references.UNIT_CD,
659 new_references.VERSION_NUMBER,
660 new_references.CAL_TYPE,
661 new_references.CI_SEQUENCE_NUMBER,
662 new_references.HIST_START_DT,
663 new_references.HIST_END_DT,
664 new_references.HIST_WHO,
665 new_references.LOCATION_CD,
666 new_references.UNIT_CLASS,
667 new_references.ENROLLED_DT,
668 new_references.UNIT_ATTEMPT_STATUS,
669 new_references.ADMINISTRATIVE_UNIT_STATUS,
670 new_references.AUS_DESCRIPTION,
671 new_references.DISCONTINUED_DT,
672 new_references.RULE_WAIVED_DT,
673 new_references.RULE_WAIVED_PERSON_ID,
674 new_references.NO_ASSESSMENT_IND,
675 new_references.EXAM_LOCATION_CD,
676 new_references.DCNT_REASON_CD,
677 X_LAST_UPDATE_DATE,
678 X_LAST_UPDATED_BY,
679 X_LAST_UPDATE_DATE,
680 X_LAST_UPDATED_BY,
681 X_LAST_UPDATE_LOGIN,
682 new_references.org_id,
683 new_references.GRADING_SCHEMA_CODE,
684 new_references.ENR_METHOD_TYPE,
685 new_references.ADMINISTRATIVE_PRIORITY,
686 new_references.WAITLIST_DT,
687 new_references.REQUEST_ID,
688 new_references.PROGRAM_APPLICATION_ID,
689 new_references.PROGRAM_ID,
690 new_references.PROGRAM_UPDATE_DATE,
691 new_references.CART,
692 new_references.ORG_UNIT_CD,
693 new_references.RSV_SEAT_EXT_ID,
694 new_references.GS_VERSION_NUMBER,
695 new_references.FAILED_UNIT_RULE,
696 new_references.DEG_AUD_DETAIL_ID,
697 new_references.UOO_ID,
698 new_references.CORE_INDICATOR_CODE
699 );
700 open c;
701 fetch c into X_ROWID;
702 if (c%notfound) then
703 close c;
704 raise no_data_found;
705 end if;
706 close c;
707
708 end INSERT_ROW;
709 procedure LOCK_ROW (
710 X_ROWID in VARCHAR2,
711 X_PERSON_ID in NUMBER,
712 X_COURSE_CD in VARCHAR2,
713 X_UNIT_CD in VARCHAR2,
714 X_CAL_TYPE in VARCHAR2,
715 X_CI_SEQUENCE_NUMBER in NUMBER,
716 X_HIST_START_DT in DATE,
717 X_ELO_DESCRIPTION in VARCHAR2,
718 X_SUP_UNIT_CD in VARCHAR2,
719 X_SUP_VERSION_NUMBER in NUMBER,
720 X_ALTERNATIVE_TITLE in VARCHAR2,
721 X_OVERRIDE_ENROLLED_CP in NUMBER,
722 X_OVERRIDE_EFTSU in NUMBER,
723 X_OVERRIDE_ACHIEVABLE_CP in NUMBER,
724 X_OVERRIDE_OUTCOME_DUE_DT in DATE,
725 X_OVERRIDE_CREDIT_REASON in VARCHAR2,
726 X_VERSION_NUMBER in NUMBER,
727 X_HIST_END_DT in DATE,
728 X_HIST_WHO in NUMBER,
729 X_LOCATION_CD in VARCHAR2,
730 X_UNIT_CLASS in VARCHAR2,
731 X_ENROLLED_DT in DATE,
732 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
733 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
734 X_AUS_DESCRIPTION in VARCHAR2,
735 X_DISCONTINUED_DT in DATE,
736 X_RULE_WAIVED_DT in DATE,
737 X_RULE_WAIVED_PERSON_ID in NUMBER,
738 X_NO_ASSESSMENT_IND in VARCHAR2,
739 X_EXAM_LOCATION_CD in VARCHAR2,
740 X_DCNT_REASON_CD IN VARCHAR2,
741 X_GRADING_SCHEMA_CODE IN VARCHAR2 DEFAULT NULL,
742 X_ENR_METHOD_TYPE IN VARCHAR2 DEFAULT NULL,
743 X_ADMINISTRATIVE_PRIORITY IN NUMBER DEFAULT NULL,
744 X_WAITLIST_DT IN DATE DEFAULT NULL,
745 X_REQUEST_ID IN NUMBER DEFAULT NULL,
746 X_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
750 X_ORG_UNIT_CD IN VARCHAR2 DEFAULT NULL,
747 X_PROGRAM_ID IN NUMBER DEFAULT NULL,
748 X_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
749 X_CART IN VARCHAR2 DEFAULT NULL,
751 X_RSV_SEAT_EXT_ID IN NUMBER DEFAULT NULL,
752 X_GS_VERSION_NUMBER IN NUMBER DEFAULT NULL,
753 X_FAILED_UNIT_RULE IN VARCHAR2 DEFAULT NULL,
754 X_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
755 X_UOO_ID IN NUMBER,
756 X_CORE_INDICATOR_CODE IN VARCHAR2 DEFAULT NULL
757 ) AS
758 cursor c1 is select
759 ELO_DESCRIPTION,
760 SUP_UNIT_CD,
761 SUP_VERSION_NUMBER,
762 ALTERNATIVE_TITLE,
763 OVERRIDE_ENROLLED_CP,
764 OVERRIDE_EFTSU,
765 OVERRIDE_ACHIEVABLE_CP,
766 OVERRIDE_OUTCOME_DUE_DT,
767 OVERRIDE_CREDIT_REASON,
768 VERSION_NUMBER,
769 HIST_END_DT,
770 HIST_WHO,
771 LOCATION_CD,
772 UNIT_CLASS,
773 ENROLLED_DT,
774 UNIT_ATTEMPT_STATUS,
775 ADMINISTRATIVE_UNIT_STATUS,
776 AUS_DESCRIPTION,
777 DISCONTINUED_DT,
778 RULE_WAIVED_DT,
779 RULE_WAIVED_PERSON_ID,
780 NO_ASSESSMENT_IND,
781 EXAM_LOCATION_CD,
782 dcnt_reason_cd,
783 GRADING_SCHEMA_CODE,
784 ENR_METHOD_TYPE,
785 ADMINISTRATIVE_PRIORITY ,
786 WAITLIST_DT,
787 REQUEST_ID,
788 PROGRAM_APPLICATION_ID,
789 PROGRAM_ID,
790 PROGRAM_UPDATE_DATE,
791 CART,
792 ORG_UNIT_CD,
793 RSV_SEAT_EXT_ID,
794 GS_VERSION_NUMBER,
795 FAILED_UNIT_RULE,
796 DEG_AUD_DETAIL_ID,
797 UOO_ID,
798 CORE_INDICATOR_CODE
799 from IGS_EN_SU_ATTEMPT_H_ALL
800 where PERSON_ID = X_PERSON_ID
801 and COURSE_CD = X_COURSE_CD
802 and HIST_START_DT = X_HIST_START_DT
803 and UOO_ID = X_UOO_ID
804 for update of PERSON_ID nowait;
805 tlinfo c1%rowtype;
806 begin
807 open c1;
808 fetch c1 into tlinfo;
809 if (c1%notfound) then
810 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
811 IGS_GE_MSG_STACK.ADD;
812 APP_EXCEPTION.RAISE_EXCEPTION;
813 close c1;
814 return;
815 end if;
816 close c1;
817 if ( ((tlinfo.ELO_DESCRIPTION = X_ELO_DESCRIPTION)
818 OR ((tlinfo.ELO_DESCRIPTION is null)
819 AND (X_ELO_DESCRIPTION is null)))
820 AND ((tlinfo.SUP_UNIT_CD = X_SUP_UNIT_CD)
821 OR ((tlinfo.SUP_UNIT_CD is null)
822 AND (X_SUP_UNIT_CD is null)))
823 AND ((tlinfo.SUP_VERSION_NUMBER = X_SUP_VERSION_NUMBER)
824 OR ((tlinfo.SUP_VERSION_NUMBER is null)
825 AND (X_SUP_VERSION_NUMBER is null)))
826 AND ((tlinfo.ALTERNATIVE_TITLE = X_ALTERNATIVE_TITLE)
827 OR ((tlinfo.ALTERNATIVE_TITLE is null)
828 AND (X_ALTERNATIVE_TITLE is null)))
829 AND ((tlinfo.OVERRIDE_ENROLLED_CP = X_OVERRIDE_ENROLLED_CP)
830 OR ((tlinfo.OVERRIDE_ENROLLED_CP is null)
831 AND (X_OVERRIDE_ENROLLED_CP is null)))
832 AND ((tlinfo.OVERRIDE_EFTSU = X_OVERRIDE_EFTSU)
833 OR ((tlinfo.OVERRIDE_EFTSU is null)
834 AND (X_OVERRIDE_EFTSU is null)))
835 AND ((tlinfo.OVERRIDE_ACHIEVABLE_CP = X_OVERRIDE_ACHIEVABLE_CP)
836 OR ((tlinfo.OVERRIDE_ACHIEVABLE_CP is null)
837 AND (X_OVERRIDE_ACHIEVABLE_CP is null)))
838 AND ((tlinfo.OVERRIDE_OUTCOME_DUE_DT = X_OVERRIDE_OUTCOME_DUE_DT)
839 OR ((tlinfo.OVERRIDE_OUTCOME_DUE_DT is null)
840 AND (X_OVERRIDE_OUTCOME_DUE_DT is null)))
841 AND ((tlinfo.OVERRIDE_CREDIT_REASON = X_OVERRIDE_CREDIT_REASON)
842 OR ((tlinfo.OVERRIDE_CREDIT_REASON is null)
843 AND (X_OVERRIDE_CREDIT_REASON is null)))
844 AND ((tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
845 OR ((tlinfo.VERSION_NUMBER is null)
846 AND (X_VERSION_NUMBER is null)))
847 AND (tlinfo.HIST_END_DT = X_HIST_END_DT)
848 AND (tlinfo.HIST_WHO = X_HIST_WHO)
849 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
850 OR ((tlinfo.LOCATION_CD is null)
851 AND (X_LOCATION_CD is null)))
852 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
853 OR ((tlinfo.UNIT_CLASS is null)
854 AND (X_UNIT_CLASS is null)))
855 AND ((tlinfo.ENROLLED_DT = X_ENROLLED_DT)
856 OR ((tlinfo.ENROLLED_DT is null)
857 AND (X_ENROLLED_DT is null)))
858 AND ((tlinfo.UNIT_ATTEMPT_STATUS = X_UNIT_ATTEMPT_STATUS)
859 OR ((tlinfo.UNIT_ATTEMPT_STATUS is null)
860 AND (X_UNIT_ATTEMPT_STATUS is null)))
861 AND ((tlinfo.ADMINISTRATIVE_UNIT_STATUS = X_ADMINISTRATIVE_UNIT_STATUS)
862 OR ((tlinfo.ADMINISTRATIVE_UNIT_STATUS is null)
863 AND (X_ADMINISTRATIVE_UNIT_STATUS is null)))
864 AND ((tlinfo.AUS_DESCRIPTION = X_AUS_DESCRIPTION)
865 OR ((tlinfo.AUS_DESCRIPTION is null)
866 AND (X_AUS_DESCRIPTION is null)))
867 AND ((tlinfo.DISCONTINUED_DT = X_DISCONTINUED_DT)
868 OR ((tlinfo.DISCONTINUED_DT is null)
869 AND (X_DISCONTINUED_DT is null)))
870 AND ((tlinfo.RULE_WAIVED_DT = X_RULE_WAIVED_DT)
874 OR ((tlinfo.RULE_WAIVED_PERSON_ID is null)
871 OR ((tlinfo.RULE_WAIVED_DT is null)
872 AND (X_RULE_WAIVED_DT is null)))
873 AND ((tlinfo.RULE_WAIVED_PERSON_ID = X_RULE_WAIVED_PERSON_ID)
875 AND (X_RULE_WAIVED_PERSON_ID is null)))
876 AND ((tlinfo.NO_ASSESSMENT_IND = X_NO_ASSESSMENT_IND)
877 OR ((tlinfo.NO_ASSESSMENT_IND is null)
878 AND (X_NO_ASSESSMENT_IND is null)))
879 AND ((tlinfo.EXAM_LOCATION_CD = X_EXAM_LOCATION_CD)
880 OR ((tlinfo.EXAM_LOCATION_CD is null)
881 AND (X_EXAM_LOCATION_CD is null)))
882 AND ((tlinfo.dcnt_reason_cd = X_dcnt_reason_Cd)
883 OR ((tlinfo.dcnt_reason_cd is null)
884 AND (X_dcnt_reason_cd is null)))
885 AND ((tlinfo.GRADING_SCHEMA_CODE = X_GRADING_SCHEMA_CODE)
886 OR ((tlinfo.GRADING_SCHEMA_CODE is null)
887 AND (X_GRADING_SCHEMA_CODE is null)))
888 AND ((tlinfo.ENR_METHOD_TYPE = X_ENR_METHOD_TYPE)
889 OR ((tlinfo.ENR_METHOD_TYPE is null)
890 AND (X_ENR_METHOD_TYPE is null)))
891
892 AND ((tlinfo.ADMINISTRATIVE_PRIORITY = X_ADMINISTRATIVE_PRIORITY)
893 OR ((tlinfo.ADMINISTRATIVE_PRIORITY is null)
894 AND (X_ADMINISTRATIVE_PRIORITY is null)))
895
896 AND ((tlinfo.WAITLIST_DT = X_WAITLIST_DT)
897 OR ((tlinfo.WAITLIST_DT is null)
898 AND (X_WAITLIST_DT is null)))
899
900 AND ((tlinfo.REQUEST_ID = X_REQUEST_ID)
901 OR ((tlinfo.REQUEST_ID is null)
902 AND (X_REQUEST_ID is null)))
903
904 AND ((tlinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID)
905 OR ((tlinfo.PROGRAM_APPLICATION_ID is null)
906 AND (X_PROGRAM_APPLICATION_ID is null)))
907
908 AND ((tlinfo.PROGRAM_ID = X_PROGRAM_ID)
909 OR ((tlinfo.PROGRAM_ID is null)
910 AND (X_PROGRAM_ID is null)))
911
912 AND ((tlinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE)
913 OR ((tlinfo.PROGRAM_UPDATE_DATE is null)
914 AND (X_PROGRAM_UPDATE_DATE is null)))
915
916 AND ((tlinfo.CART = X_CART)
917 OR ((tlinfo.CART is null)
918 AND (X_CART is null)))
919
920 AND ((tlinfo.ORG_UNIT_CD = X_ORG_UNIT_CD)
921 OR ((tlinfo.ORG_UNIT_CD is null)
922 AND (X_ORG_UNIT_CD is null)))
923
924 AND ((tlinfo.RSV_SEAT_EXT_ID = X_RSV_SEAT_EXT_ID)
925 OR ((tlinfo.RSV_SEAT_EXT_ID is null)
926 AND (X_RSV_SEAT_EXT_ID is null)))
927
928 AND ((tlinfo.GS_VERSION_NUMBER = X_GS_VERSION_NUMBER)
929 OR ((tlinfo.GS_VERSION_NUMBER is null)
930 AND (X_GS_VERSION_NUMBER is null)))
931
932 AND ((tlinfo.FAILED_UNIT_RULE = X_FAILED_UNIT_RULE)
933 OR ((tlinfo.FAILED_UNIT_RULE is null)
934 AND (X_FAILED_UNIT_RULE is null)))
935
936 AND ((tlinfo.DEG_AUD_DETAIL_ID = X_DEG_AUD_DETAIL_ID)
937 OR ((tlinfo.DEG_AUD_DETAIL_ID is null)
938 AND (X_DEG_AUD_DETAIL_ID is null)))
939
940 AND ((tlinfo.CORE_INDICATOR_CODE = X_CORE_INDICATOR_CODE)
941 OR (( tlinfo.CORE_INDICATOR_CODE is null)
942 AND (X_CORE_INDICATOR_CODE is null)))
943
944 ) then
945 null;
946 else
947 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
948 IGS_GE_MSG_STACK.ADD;
949 APP_EXCEPTION.RAISE_EXCEPTION;
950 end if;
951 return;
952 end LOCK_ROW;
953 procedure UPDATE_ROW (
954 X_ROWID in VARCHAR2,
955 X_PERSON_ID in NUMBER,
956 X_COURSE_CD in VARCHAR2,
957 X_UNIT_CD in VARCHAR2,
958 X_CAL_TYPE in VARCHAR2,
959 X_CI_SEQUENCE_NUMBER in NUMBER,
960 X_HIST_START_DT in DATE,
961 X_ELO_DESCRIPTION in VARCHAR2,
962 X_SUP_UNIT_CD in VARCHAR2,
963 X_SUP_VERSION_NUMBER in NUMBER,
964 X_ALTERNATIVE_TITLE in VARCHAR2,
965 X_OVERRIDE_ENROLLED_CP in NUMBER,
966 X_OVERRIDE_EFTSU in NUMBER,
967 X_OVERRIDE_ACHIEVABLE_CP in NUMBER,
968 X_OVERRIDE_OUTCOME_DUE_DT in DATE,
969 X_OVERRIDE_CREDIT_REASON in VARCHAR2,
970 X_VERSION_NUMBER in NUMBER,
971 X_HIST_END_DT in DATE,
972 X_HIST_WHO in NUMBER,
973 X_LOCATION_CD in VARCHAR2,
974 X_UNIT_CLASS in VARCHAR2,
975 X_ENROLLED_DT in DATE,
976 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
977 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
978 X_AUS_DESCRIPTION in VARCHAR2,
979 X_DISCONTINUED_DT in DATE,
980 X_RULE_WAIVED_DT in DATE,
981 X_RULE_WAIVED_PERSON_ID in NUMBER,
982 X_NO_ASSESSMENT_IND in VARCHAR2,
983 X_EXAM_LOCATION_CD in VARCHAR2,
984 x_dcnt_reason_cd IN VARCHAR2,
985 X_MODE in VARCHAR2 default 'R',
986 X_GRADING_SCHEMA_CODE IN VARCHAR2 DEFAULT NULL,
987 X_ENR_METHOD_TYPE IN VARCHAR2 DEFAULT NULL,
988 X_ADMINISTRATIVE_PRIORITY IN NUMBER DEFAULT NULL,
989 X_WAITLIST_DT IN DATE DEFAULT NULL,
990 X_REQUEST_ID IN NUMBER DEFAULT NULL,
991 X_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
992 X_PROGRAM_ID IN NUMBER DEFAULT NULL,
993 X_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
994 X_CART IN VARCHAR2 DEFAULT NULL,
998 X_FAILED_UNIT_RULE IN VARCHAR2 DEFAULT NULL,
995 X_ORG_UNIT_CD IN VARCHAR2 DEFAULT NULL,
996 X_RSV_SEAT_EXT_ID IN NUMBER DEFAULT NULL,
997 X_GS_VERSION_NUMBER IN NUMBER DEFAULT NULL,
999 X_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
1000 X_UOO_ID IN NUMBER,
1001 X_CORE_INDICATOR_CODE IN VARCHAR2 DEFAULT NULL
1002 ) AS
1003 X_LAST_UPDATE_DATE DATE;
1004 X_LAST_UPDATED_BY NUMBER;
1005 X_LAST_UPDATE_LOGIN NUMBER;
1006 begin
1007 X_LAST_UPDATE_DATE := SYSDATE;
1008 if(X_MODE = 'I') then
1009 X_LAST_UPDATED_BY := 1;
1010 X_LAST_UPDATE_LOGIN := 0;
1011 elsif (X_MODE = 'R') then
1012 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1013 if X_LAST_UPDATED_BY is NULL then
1014 X_LAST_UPDATED_BY := -1;
1015 end if;
1016 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1017 if X_LAST_UPDATE_LOGIN is NULL then
1018 X_LAST_UPDATE_LOGIN := -1;
1019 end if;
1020 else
1021 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1022 IGS_GE_MSG_STACK.ADD;
1023 APP_EXCEPTION.RAISE_EXCEPTION;
1024 end if;
1025 before_DML(
1026 p_action=>'UPDATE',
1027 x_rowid=>X_ROWID,
1028 x_administrative_unit_status=>X_ADMINISTRATIVE_UNIT_STATUS,
1029 x_alternative_title=>X_ALTERNATIVE_TITLE,
1030 x_aus_description=>X_AUS_DESCRIPTION,
1031 x_cal_type=>X_CAL_TYPE,
1032 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
1033 x_course_cd=>X_COURSE_CD,
1034 x_discontinued_dt=>X_DISCONTINUED_DT,
1035 x_elo_description=>X_ELO_DESCRIPTION,
1036 x_enrolled_dt=>X_ENROLLED_DT,
1037 x_exam_location_cd=>X_EXAM_LOCATION_CD,
1038 x_hist_end_dt=>X_HIST_END_DT,
1039 x_hist_start_dt=>X_HIST_START_DT,
1040 x_hist_who=>X_HIST_WHO,
1041 x_location_cd=>X_LOCATION_CD,
1042 x_no_assessment_ind=>X_NO_ASSESSMENT_IND,
1043 x_override_achievable_cp=>X_OVERRIDE_ACHIEVABLE_CP,
1044 x_override_credit_reason=>X_OVERRIDE_CREDIT_REASON,
1045 x_override_eftsu=>X_OVERRIDE_EFTSU,
1046 x_override_enrolled_cp=>X_OVERRIDE_ENROLLED_CP,
1047 x_override_outcome_due_dt=>X_OVERRIDE_OUTCOME_DUE_DT,
1048 x_person_id=>X_PERSON_ID,
1049 x_rule_waived_dt=>X_RULE_WAIVED_DT,
1050 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
1051 x_sup_unit_cd=>X_SUP_UNIT_CD,
1052 x_sup_version_number=>X_SUP_VERSION_NUMBER,
1053 x_unit_attempt_status=>X_UNIT_ATTEMPT_STATUS,
1054 x_unit_cd=>X_UNIT_CD,
1055 x_unit_class=>X_UNIT_CLASS,
1056 x_version_number=>X_VERSION_NUMBER,
1057 x_dcnt_reason_cd => x_dcnt_reason_cd,
1058 x_creation_date=>X_LAST_UPDATE_DATE,
1059 x_created_by=>X_LAST_UPDATED_BY,
1060 x_last_update_date=>X_LAST_UPDATE_DATE,
1061 x_last_updated_by=>X_LAST_UPDATED_BY,
1062 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1063 x_grading_schema_code =>X_GRADING_SCHEMA_CODE,
1064 x_enr_method_type =>X_ENR_METHOD_TYPE,
1065 x_administrative_priority => X_ADMINISTRATIVE_PRIORITY,
1066 x_waitlist_dt => X_WAITLIST_DT,
1067 x_request_id => X_REQUEST_ID,
1068 x_program_application_id => X_PROGRAM_APPLICATION_ID ,
1069 x_program_id => X_PROGRAM_ID,
1070 x_program_update_date => X_PROGRAM_UPDATE_DATE,
1071 x_cart => X_CART,
1072 x_org_unit_cd => X_ORG_UNIT_CD,
1073 x_rsv_seat_ext_id => X_RSV_SEAT_EXT_ID,
1074 x_gs_version_number => X_GS_VERSION_NUMBER,
1075 x_failed_unit_rule => X_FAILED_UNIT_RULE,
1076 x_deg_aud_detail_id => X_DEG_AUD_DETAIL_ID,
1077 x_uoo_id => X_UOO_ID,
1078 x_core_indicator_code => X_CORE_INDICATOR_CODE
1079 );
1080 update IGS_EN_SU_ATTEMPT_H_ALL set
1081 ELO_DESCRIPTION = new_references.ELO_DESCRIPTION,
1082 SUP_UNIT_CD = new_references.SUP_UNIT_CD,
1083 SUP_VERSION_NUMBER = new_references.SUP_VERSION_NUMBER,
1084 ALTERNATIVE_TITLE = new_references.ALTERNATIVE_TITLE,
1085 OVERRIDE_ENROLLED_CP = new_references.OVERRIDE_ENROLLED_CP,
1086 OVERRIDE_EFTSU = new_references.OVERRIDE_EFTSU,
1087 OVERRIDE_ACHIEVABLE_CP = new_references.OVERRIDE_ACHIEVABLE_CP,
1088 OVERRIDE_OUTCOME_DUE_DT = new_references.OVERRIDE_OUTCOME_DUE_DT,
1089 OVERRIDE_CREDIT_REASON = new_references.OVERRIDE_CREDIT_REASON,
1090 VERSION_NUMBER = new_references.VERSION_NUMBER,
1091 HIST_END_DT = new_references.HIST_END_DT,
1092 HIST_WHO = new_references.HIST_WHO,
1093 LOCATION_CD = new_references.LOCATION_CD,
1094 UNIT_CLASS = new_references.UNIT_CLASS,
1095 ENROLLED_DT = new_references.ENROLLED_DT,
1096 UNIT_ATTEMPT_STATUS = new_references.UNIT_ATTEMPT_STATUS,
1097 ADMINISTRATIVE_UNIT_STATUS = new_references.ADMINISTRATIVE_UNIT_STATUS,
1098 AUS_DESCRIPTION = new_references.AUS_DESCRIPTION,
1099 DISCONTINUED_DT = new_references.DISCONTINUED_DT,
1100 RULE_WAIVED_DT = new_references.RULE_WAIVED_DT,
1101 RULE_WAIVED_PERSON_ID = new_references.RULE_WAIVED_PERSON_ID,
1102 NO_ASSESSMENT_IND = new_references.NO_ASSESSMENT_IND,
1103 EXAM_LOCATION_CD = new_references.EXAM_LOCATION_CD,
1104 DCNT_REASON_CD = new_references.DCNT_REASON_CD,
1105 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1106 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1107 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1108 GRADING_SCHEMA_CODE=new_references.GRADING_SCHEMA_CODE,
1109 ENR_METHOD_TYPE=new_references.ENR_METHOD_TYPE,
1110 ADMINISTRATIVE_PRIORITY = new_references.ADMINISTRATIVE_PRIORITY,
1111 WAITLIST_DT = new_references.WAITLIST_DT,
1115 PROGRAM_UPDATE_DATE = new_references.PROGRAM_UPDATE_DATE,
1112 REQUEST_ID = new_references.REQUEST_ID,
1113 PROGRAM_APPLICATION_ID = new_references.PROGRAM_APPLICATION_ID ,
1114 PROGRAM_ID = new_references.PROGRAM_ID,
1116 CART = new_references.CART,
1117 ORG_UNIT_CD = new_references.ORG_UNIT_CD,
1118 RSV_SEAT_EXT_ID = new_references.RSV_SEAT_EXT_ID,
1119 GS_VERSION_NUMBER = new_references.GS_VERSION_NUMBER,
1120 FAILED_UNIT_RULE = new_references.FAILED_UNIT_RULE,
1121 DEG_AUD_DETAIL_ID = new_references.DEG_AUD_DETAIL_ID,
1122 CORE_INDICATOR_CODE = new_references.CORE_INDICATOR_CODE
1123 where ROWID = X_ROWID
1124 ;
1125 if (sql%notfound) then
1126 raise no_data_found;
1127 end if;
1128
1129 end UPDATE_ROW;
1130 procedure ADD_ROW (
1131 X_ROWID in out NOCOPY VARCHAR2,
1132 X_PERSON_ID in NUMBER,
1133 X_COURSE_CD in VARCHAR2,
1134 X_UNIT_CD in VARCHAR2,
1135 X_CAL_TYPE in VARCHAR2,
1136 X_CI_SEQUENCE_NUMBER in NUMBER,
1137 X_HIST_START_DT in DATE,
1138 X_ELO_DESCRIPTION in VARCHAR2,
1139 X_SUP_UNIT_CD in VARCHAR2,
1140 X_SUP_VERSION_NUMBER in NUMBER,
1141 X_ALTERNATIVE_TITLE in VARCHAR2,
1142 X_OVERRIDE_ENROLLED_CP in NUMBER,
1143 X_OVERRIDE_EFTSU in NUMBER,
1144 X_OVERRIDE_ACHIEVABLE_CP in NUMBER,
1145 X_OVERRIDE_OUTCOME_DUE_DT in DATE,
1146 X_OVERRIDE_CREDIT_REASON in VARCHAR2,
1147 X_VERSION_NUMBER in NUMBER,
1148 X_HIST_END_DT in DATE,
1149 X_HIST_WHO in NUMBER,
1150 X_LOCATION_CD in VARCHAR2,
1151 X_UNIT_CLASS in VARCHAR2,
1152 X_ENROLLED_DT in DATE,
1153 X_UNIT_ATTEMPT_STATUS in VARCHAR2,
1154 X_ADMINISTRATIVE_UNIT_STATUS in VARCHAR2,
1155 X_AUS_DESCRIPTION in VARCHAR2,
1156 X_DISCONTINUED_DT in DATE,
1157 X_RULE_WAIVED_DT in DATE,
1158 X_RULE_WAIVED_PERSON_ID in NUMBER,
1159 X_NO_ASSESSMENT_IND in VARCHAR2,
1160 X_EXAM_LOCATION_CD in VARCHAR2,
1161 x_dcnt_reason_cd IN VARCHAR2,
1162 X_MODE in VARCHAR2 default 'R',
1163 x_org_id IN NUMBER ,
1164 X_GRADING_SCHEMA_CODE IN VARCHAR2 DEFAULT NULL,
1165 X_ENR_METHOD_TYPE IN VARCHAR2 DEFAULT NULL,
1166 X_ADMINISTRATIVE_PRIORITY IN NUMBER DEFAULT NULL,
1167 X_WAITLIST_DT IN DATE DEFAULT NULL,
1168 X_REQUEST_ID IN NUMBER DEFAULT NULL,
1169 X_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
1170 X_PROGRAM_ID IN NUMBER DEFAULT NULL,
1171 X_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
1172 X_CART IN VARCHAR2 DEFAULT NULL,
1173 X_ORG_UNIT_CD IN VARCHAR2 DEFAULT NULL,
1174 X_RSV_SEAT_EXT_ID IN NUMBER DEFAULT NULL,
1175 X_GS_VERSION_NUMBER IN NUMBER DEFAULT NULL,
1176 X_FAILED_UNIT_RULE IN VARCHAR2 DEFAULT NULL,
1177 X_DEG_AUD_DETAIL_ID IN NUMBER DEFAULT NULL,
1178 X_UOO_ID IN NUMBER,
1179 X_CORE_INDICATOR_CODE IN VARCHAR2 DEFAULT NULL
1180 ) as
1181 cursor c1 is select rowid from IGS_EN_SU_ATTEMPT_H_ALL
1182 where PERSON_ID = X_PERSON_ID
1183 and COURSE_CD = X_COURSE_CD
1184 and HIST_START_DT = X_HIST_START_DT
1185 and UOO_ID = X_UOO_ID;
1186
1187 begin
1188 open c1;
1189 fetch c1 into X_ROWID;
1190 if (c1%notfound) then
1191 close c1;
1192 INSERT_ROW (
1193 X_ROWID,
1194 X_PERSON_ID,
1195 X_COURSE_CD,
1196 X_UNIT_CD,
1197 X_CAL_TYPE,
1198 X_CI_SEQUENCE_NUMBER,
1199 X_HIST_START_DT,
1200 X_ELO_DESCRIPTION,
1201 X_SUP_UNIT_CD,
1202 X_SUP_VERSION_NUMBER,
1203 X_ALTERNATIVE_TITLE,
1204 X_OVERRIDE_ENROLLED_CP,
1205 X_OVERRIDE_EFTSU,
1206 X_OVERRIDE_ACHIEVABLE_CP,
1207 X_OVERRIDE_OUTCOME_DUE_DT,
1208 X_OVERRIDE_CREDIT_REASON,
1209 X_VERSION_NUMBER,
1210 X_HIST_END_DT,
1211 X_HIST_WHO,
1212 X_LOCATION_CD,
1213 X_UNIT_CLASS,
1214 X_ENROLLED_DT,
1215 X_UNIT_ATTEMPT_STATUS,
1216 X_ADMINISTRATIVE_UNIT_STATUS,
1217 X_AUS_DESCRIPTION,
1218 X_DISCONTINUED_DT,
1219 X_RULE_WAIVED_DT,
1220 X_RULE_WAIVED_PERSON_ID,
1221 X_NO_ASSESSMENT_IND,
1222 X_EXAM_LOCATION_CD,
1223 X_DCNT_REASON_CD,
1224 X_MODE,
1225 x_org_id,
1226 X_GRADING_SCHEMA_CODE,
1227 X_ENR_METHOD_TYPE,
1228 X_ADMINISTRATIVE_PRIORITY,
1229 X_WAITLIST_DT,
1230 X_REQUEST_ID,
1231 X_PROGRAM_APPLICATION_ID,
1232 X_PROGRAM_ID,
1233 X_PROGRAM_UPDATE_DATE,
1234 X_CART,
1235 X_ORG_UNIT_CD,
1236 X_RSV_SEAT_EXT_ID,
1237 X_GS_VERSION_NUMBER,
1238 X_FAILED_UNIT_RULE ,
1239 X_DEG_AUD_DETAIL_ID,
1240 X_UOO_ID,
1241 X_CORE_INDICATOR_CODE
1242 );
1243 return;
1244 end if;
1245 close c1;
1246 UPDATE_ROW (
1247 X_ROWID,
1248 X_PERSON_ID,
1249 X_COURSE_CD,
1250 X_UNIT_CD,
1251 X_CAL_TYPE,
1252 X_CI_SEQUENCE_NUMBER,
1253 X_HIST_START_DT,
1254 X_ELO_DESCRIPTION,
1255 X_SUP_UNIT_CD,
1256 X_SUP_VERSION_NUMBER,
1257 X_ALTERNATIVE_TITLE,
1258 X_OVERRIDE_ENROLLED_CP,
1259 X_OVERRIDE_EFTSU,
1263 X_VERSION_NUMBER,
1260 X_OVERRIDE_ACHIEVABLE_CP,
1261 X_OVERRIDE_OUTCOME_DUE_DT,
1262 X_OVERRIDE_CREDIT_REASON,
1264 X_HIST_END_DT,
1265 X_HIST_WHO,
1266 X_LOCATION_CD,
1267 X_UNIT_CLASS,
1268 X_ENROLLED_DT,
1269 X_UNIT_ATTEMPT_STATUS,
1270 X_ADMINISTRATIVE_UNIT_STATUS,
1271 X_AUS_DESCRIPTION,
1272 X_DISCONTINUED_DT,
1273 X_RULE_WAIVED_DT,
1274 X_RULE_WAIVED_PERSON_ID,
1275 X_NO_ASSESSMENT_IND,
1276 X_EXAM_LOCATION_CD,
1277 X_DCNT_REASON_CD,
1278 X_MODE,
1279 X_GRADING_SCHEMA_CODE,
1280 X_ENR_METHOD_TYPE,
1281 X_ADMINISTRATIVE_PRIORITY,
1282 X_WAITLIST_DT,
1283 X_REQUEST_ID,
1284 X_PROGRAM_APPLICATION_ID,
1285 X_PROGRAM_ID,
1286 X_PROGRAM_UPDATE_DATE,
1287 X_CART,
1288 X_ORG_UNIT_CD,
1289 X_RSV_SEAT_EXT_ID,
1290 X_GS_VERSION_NUMBER,
1291 X_FAILED_UNIT_RULE ,
1292 X_DEG_AUD_DETAIL_ID,
1293 X_UOO_ID,
1294 X_CORE_INDICATOR_CODE
1295 );
1296 end ADD_ROW;
1297 procedure DELETE_ROW (
1298 X_ROWID in VARCHAR2
1299 ) AS
1300 begin
1301 Before_DML(
1302 p_action => 'DELETE',
1303 x_rowid => X_ROWID
1304 );
1305 delete from IGS_EN_SU_ATTEMPT_H_ALL
1306 where ROWID=X_ROWID;
1307 if (sql%notfound) then
1308 raise no_data_found;
1309 end if;
1310
1311 end DELETE_ROW;
1312
1313 PROCEDURE Check_Constraints (
1314 Column_Name IN VARCHAR2 DEFAULT NULL,
1315 Column_Value IN VARCHAR2 DEFAULT NULL
1316 ) AS
1317
1318 BEGIN
1319 IF column_name is null then
1320 NULL;
1321 ELSIF upper(Column_name) = 'ADMINISTRATIVE_UNIT_STATUS' then
1322 new_references.ADMINISTRATIVE_UNIT_STATUS := column_value;
1323 ELSIF upper(Column_name) = 'ALTERNATIVE_TITLE' then
1324 new_references.ALTERNATIVE_TITLE := column_value;
1325 ELSIF upper(Column_name) = 'CAL_TYPE' then
1326 new_references.CAL_TYPE := column_value;
1327 ELSIF upper(Column_name) = 'COURSE_CD' then
1328 new_references.COURSE_CD := column_value;
1329 ELSIF upper(Column_name) = 'EXAM_LOCATION_CD' then
1330 new_references.EXAM_LOCATION_CD := column_value;
1331 ELSIF upper(Column_name) = 'NO_ASSESSMENT_IND' then
1332 new_references.NO_ASSESSMENT_IND := column_value;
1333 ELSIF upper(Column_name) = 'SUP_UNIT_CD' then
1334 new_references.SUP_UNIT_CD := column_value;
1335 ELSIF upper(Column_name) = 'UNIT_ATTEMPT_STATUS' then
1336 new_references.UNIT_ATTEMPT_STATUS := column_value;
1337 ELSIF upper(Column_name) = 'UNIT_CD' then
1338 new_references.UNIT_CD := column_value;
1339 ELSIF upper(Column_name) = 'UNIT_CLASS' then
1340 new_references.UNIT_CLASS := column_value;
1341 ELSIF upper(Column_name) = 'PERSON_ID' then
1342 new_references.PERSON_ID := igs_ge_number.to_num(column_value);
1343 ELSIF upper(Column_name) = 'LOCATION_CD' then
1344 new_references.LOCATION_CD := column_value;
1345 ELSIF upper(Column_name) = 'VERSION_NUMBER' then
1346 new_references.VERSION_NUMBER := igs_ge_number.to_num(column_value);
1347 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
1348 new_references.CI_SEQUENCE_NUMBER := igs_ge_number.to_num(column_value);
1349 ELSIF upper(Column_name) = 'RULE_WAIVED_PERSON_ID' then
1350 new_references.RULE_WAIVED_PERSON_ID := igs_ge_number.to_num(column_value);
1351 ELSIF upper(Column_name) = 'NO_ASSESSMENT_IND' then
1352 new_references.NO_ASSESSMENT_IND := column_value;
1353 ELSIF upper(Column_name) = 'SUP_VERSION_NUMBER' then
1354 new_references.SUP_VERSION_NUMBER := igs_ge_number.to_num(column_value);
1355 ELSIF upper(Column_name) = 'OVERRIDE_ENROLLED_CP' then
1356 new_references.OVERRIDE_ENROLLED_CP := igs_ge_number.to_num(column_value);
1357 ELSIF upper(Column_name) = 'OVERRIDE_EFTSU' then
1358 new_references.OVERRIDE_EFTSU := igs_ge_number.to_num(column_value);
1359 ELSIF upper(Column_name) = 'OVERRIDE_ACHIEVABLE_CP' then
1360 new_references.OVERRIDE_ACHIEVABLE_CP := igs_ge_number.to_num(column_value);
1361 ELSIF upper(Column_name) = 'CORE_INDICATOR_CODE' then
1362 new_references.CORE_INDICATOR_CODE := column_value;
1363 END IF;
1364
1365 IF upper(column_name) = 'ADMINISTRATIVE_UNIT_STATUS' OR
1366 column_name is null Then
1367 IF new_references.ADMINISTRATIVE_UNIT_STATUS <> UPPER(new_references.ADMINISTRATIVE_UNIT_STATUS) Then
1368 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1369 IGS_GE_MSG_STACK.ADD;
1370 APP_EXCEPTION.RAISE_EXCEPTION;
1371 END IF;
1372 END IF;
1373
1374 IF upper(column_name) = 'ALTERNATIVE_TITLE' OR
1375 column_name is null Then
1376 IF new_references.ALTERNATIVE_TITLE <> UPPER(new_references.ALTERNATIVE_TITLE) Then
1377 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1378 IGS_GE_MSG_STACK.ADD;
1379 APP_EXCEPTION.RAISE_EXCEPTION;
1380 END IF;
1381 END IF;
1382
1383 IF upper(column_name) = 'CAL_TYPE' OR
1384
1385 column_name is null Then
1386 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
1387 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1388 IGS_GE_MSG_STACK.ADD;
1389 APP_EXCEPTION.RAISE_EXCEPTION;
1393 IF upper(column_name) = 'COURSE_CD' OR
1390 END IF;
1391 END IF;
1392
1394 column_name is null Then
1395 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
1396 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1397 IGS_GE_MSG_STACK.ADD;
1398 APP_EXCEPTION.RAISE_EXCEPTION;
1399 END IF;
1400 END IF;
1401
1402 IF upper(column_name) = 'EXAM_LOCATION_CD' OR
1403 column_name is null Then
1404 IF new_references.EXAM_LOCATION_CD <> UPPER(new_references.EXAM_LOCATION_CD) Then
1405 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1406 IGS_GE_MSG_STACK.ADD;
1407 APP_EXCEPTION.RAISE_EXCEPTION;
1408 END IF;
1409 END IF;
1410
1411 IF upper(column_name) = 'NO_ASSESSMENT_IND' OR
1412 column_name is null Then
1413 IF new_references.NO_ASSESSMENT_IND <> UPPER(new_references.NO_ASSESSMENT_IND) Then
1414 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1415 IGS_GE_MSG_STACK.ADD;
1416 APP_EXCEPTION.RAISE_EXCEPTION;
1417 END IF;
1418 END IF;
1419
1420 IF upper(column_name) = 'SUP_UNIT_CD' OR
1421 column_name is null Then
1422 IF new_references.SUP_UNIT_CD <> UPPER(new_references.SUP_UNIT_CD) Then
1423 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1424 IGS_GE_MSG_STACK.ADD;
1425 APP_EXCEPTION.RAISE_EXCEPTION;
1426 END IF;
1427 END IF;
1428
1429 IF upper(column_name) = 'UNIT_ATTEMPT_STATUS' OR
1430 column_name is null Then
1431 IF new_references.UNIT_ATTEMPT_STATUS <> UPPER(new_references.UNIT_ATTEMPT_STATUS) Then
1432 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1433 IGS_GE_MSG_STACK.ADD;
1434 APP_EXCEPTION.RAISE_EXCEPTION;
1435 END IF;
1436 END IF;
1437
1438 IF upper(column_name) = 'LOCATION_CD' OR
1439 column_name is null Then
1440 IF new_references.LOCATION_CD <> UPPER(new_references.LOCATION_CD) Then
1441 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1442 IGS_GE_MSG_STACK.ADD;
1443 APP_EXCEPTION.RAISE_EXCEPTION;
1444 END IF;
1445 END IF;
1446
1447 IF upper(column_name) = 'UNIT_CD' OR
1448 column_name is null Then
1449 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
1450 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1451 IGS_GE_MSG_STACK.ADD;
1452 APP_EXCEPTION.RAISE_EXCEPTION;
1453 END IF;
1454 END IF;
1455
1456 IF upper(column_name) = 'UNIT_CLASS' OR
1457 column_name is null Then
1458 IF new_references.UNIT_CLASS <> UPPER(new_references.UNIT_CLASS) Then
1459 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1460 IGS_GE_MSG_STACK.ADD;
1461 APP_EXCEPTION.RAISE_EXCEPTION;
1462 END IF;
1463 END IF;
1464
1465
1466 IF upper(column_name) = 'PERSON_ID ' OR
1467 column_name is null Then
1468 IF new_references.PERSON_ID < 0 OR new_references.PERSON_ID > 9999999999 Then
1469 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1470 IGS_GE_MSG_STACK.ADD;
1471 APP_EXCEPTION.RAISE_EXCEPTION;
1472 END IF;
1473 END IF;
1474
1475
1476 IF upper(column_name) = 'VERSION_NUMBER ' OR
1477 column_name is null Then
1478 IF new_references.VERSION_NUMBER < 0 OR new_references.VERSION_NUMBER > 999 Then
1479 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1480 IGS_GE_MSG_STACK.ADD;
1481 APP_EXCEPTION.RAISE_EXCEPTION;
1482 END IF;
1483 END IF;
1484
1485 IF upper(column_name) = 'CI_SEQUENCE_NUMBER ' OR
1486 column_name is null Then
1487 IF new_references.CI_SEQUENCE_NUMBER < 1 OR new_references.CI_SEQUENCE_NUMBER > 999999 Then
1488 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1489 IGS_GE_MSG_STACK.ADD;
1490 APP_EXCEPTION.RAISE_EXCEPTION;
1491 END IF;
1492 END IF;
1493
1494
1495 IF upper(column_name) = 'RULE_WAIVED_PERSON_ID' OR
1496 column_name is null Then
1497 IF new_references.RULE_WAIVED_PERSON_ID < 0 OR new_references.RULE_WAIVED_PERSON_ID > 9999999999 Then
1498 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1499 IGS_GE_MSG_STACK.ADD;
1500 APP_EXCEPTION.RAISE_EXCEPTION;
1501 END IF;
1502 END IF;
1503
1504
1505 IF upper(column_name) = 'NO_ASSESSMENT_IND' OR
1506 column_name is null Then
1507 IF new_references.NO_ASSESSMENT_IND NOT IN ('Y','N') Then
1508 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1509 IGS_GE_MSG_STACK.ADD;
1510 APP_EXCEPTION.RAISE_EXCEPTION;
1511 END IF;
1512 END IF;
1513
1514 IF upper(column_name) = 'SUP_VERSION_NUMBER' OR
1515 column_name is null Then
1516 IF new_references.SUP_VERSION_NUMBER < 0 OR new_references.SUP_VERSION_NUMBER > 999 Then
1517 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1518 IGS_GE_MSG_STACK.ADD;
1519 APP_EXCEPTION.RAISE_EXCEPTION;
1520 END IF;
1521 END IF;
1522
1523 IF upper(column_name) = 'OVERRIDE_ENROLLED_CP' OR
1524 column_name is null Then
1525 IF new_references.OVERRIDE_ENROLLED_CP < 0 OR new_references.OVERRIDE_ENROLLED_CP> 999.999 Then
1526 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1527 IGS_GE_MSG_STACK.ADD;
1528 APP_EXCEPTION.RAISE_EXCEPTION;
1529 END IF;
1530 END IF;
1531 IF upper(column_name) = 'OVERRIDE_EFTSU' OR
1532 column_name is null Then
1533 IF new_references.OVERRIDE_EFTSU < 0 OR new_references.OVERRIDE_EFTSU > 9999.999 Then
1534 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1535 IGS_GE_MSG_STACK.ADD;
1536 APP_EXCEPTION.RAISE_EXCEPTION;
1537 END IF;
1538 END IF;
1539
1540 IF upper(column_name) = 'OVERRIDE_ACHIEVABLE_CP' OR
1541 column_name is null Then
1542 IF new_references.OVERRIDE_ACHIEVABLE_CP < 0 OR new_references.OVERRIDE_ACHIEVABLE_CP > 999.999 Then
1543 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1544 IGS_GE_MSG_STACK.ADD;
1545 APP_EXCEPTION.RAISE_EXCEPTION;
1546 END IF;
1547 END IF;
1548
1549
1550 IF upper(column_name) = 'DCNT_REASON_CD' OR
1551 column_name is null Then
1552 IF new_references.DCNT_REASON_CD <> UPPER(new_references.DCNT_REASON_CD) Then
1553 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1554 IGS_GE_MSG_STACK.ADD;
1555 APP_EXCEPTION.RAISE_EXCEPTION;
1556 END IF;
1557 END IF;
1558
1559 IF upper(column_name) = 'CORE_INDICATOR_CODE' OR
1560 column_name is null Then
1561 IF new_references.CORE_INDICATOR_CODE <> UPPER(new_references.CORE_INDICATOR_CODE) Then
1562 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
1563 IGS_GE_MSG_STACK.ADD;
1564 APP_EXCEPTION.RAISE_EXCEPTION;
1565 END IF;
1566 END IF;
1567
1568 END Check_Constraints;
1569
1570
1571 end IGS_EN_SU_ATTEMPT_H_PKG;