[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_TER_EDU_PKG
Source
1 package body IGS_AD_TER_EDU_PKG as
2 /* $Header: IGSAI54B.pls 115.6 2003/10/30 13:20:48 rghosh ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AD_TER_EDU%RowType;
6 new_references IGS_AD_TER_EDU%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_sequence_number IN NUMBER DEFAULT NULL,
13 x_tertiary_edu_lvl_comp IN VARCHAR2 DEFAULT NULL,
14 x_exclusion_ind IN VARCHAR2 DEFAULT NULL,
15 x_institution_cd IN VARCHAR2 DEFAULT NULL,
16 x_institution_name IN VARCHAR2 DEFAULT NULL,
17 x_enrolment_first_yr IN NUMBER DEFAULT NULL,
18 x_enrolment_latest_yr IN NUMBER DEFAULT NULL,
19 x_course_cd IN VARCHAR2 DEFAULT NULL,
20 x_course_title IN VARCHAR2 DEFAULT NULL,
21 x_field_of_study IN VARCHAR2 DEFAULT NULL,
22 x_language_component IN VARCHAR2 DEFAULT NULL,
23 x_student_id IN VARCHAR2 DEFAULT NULL,
24 x_equiv_full_time_yrs_enr IN NUMBER DEFAULT NULL,
25 x_tertiary_edu_lvl_qual IN VARCHAR2 DEFAULT NULL,
26 x_qualification IN VARCHAR2 DEFAULT NULL,
27 x_honours_level IN VARCHAR2 DEFAULT NULL,
28 x_level_of_achievement_type IN VARCHAR2 DEFAULT NULL,
29 x_grade_point_average IN NUMBER DEFAULT NULL,
30 x_language_of_tuition IN VARCHAR2 DEFAULT NULL,
31 x_state_cd IN VARCHAR2 DEFAULT NULL,
32 x_country_cd IN VARCHAR2 DEFAULT NULL,
33 x_notes IN VARCHAR2 DEFAULT NULL,
34 x_creation_date IN DATE DEFAULT NULL,
35 x_created_by IN NUMBER DEFAULT NULL,
36 x_last_update_date IN DATE DEFAULT NULL,
37 x_last_updated_by IN NUMBER DEFAULT NULL,
38 x_last_update_login IN NUMBER DEFAULT NULL
39 ) as
40
41 CURSOR cur_old_ref_values IS
42 SELECT *
43 FROM IGS_AD_TER_EDU
44 WHERE rowid = x_rowid;
45
46 BEGIN
47
48 l_rowid := x_rowid;
49
50 -- Code for setting the Old and New Reference Values.
51 -- Populate Old Values.
52 Open cur_old_ref_values;
53 Fetch cur_old_ref_values INTO old_references;
54 IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
55 Close cur_old_ref_values;
56 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
57 IGS_GE_MSG_STACK.ADD;
58 App_Exception.Raise_Exception;
59 Return;
60 END IF;
61 Close cur_old_ref_values;
62
63 -- Populate New Values.
64 new_references.person_id := x_person_id;
65 new_references.sequence_number := x_sequence_number;
66 new_references.tertiary_edu_lvl_comp := x_tertiary_edu_lvl_comp;
67 new_references.exclusion_ind := x_exclusion_ind;
68 new_references.institution_cd := x_institution_cd;
69 new_references.institution_name := x_institution_name;
70 new_references.enrolment_first_yr := x_enrolment_first_yr;
71 new_references.enrolment_latest_yr := x_enrolment_latest_yr;
72 new_references.course_cd := x_course_cd;
73 new_references.course_title := x_course_title;
74 new_references.field_of_study := x_field_of_study;
75 new_references.language_component := x_language_component;
76 new_references.student_id := x_student_id;
77 new_references.equiv_full_time_yrs_enr := x_equiv_full_time_yrs_enr;
78 new_references.tertiary_edu_lvl_qual := x_tertiary_edu_lvl_qual;
79 new_references.qualification := x_qualification;
80 new_references.honours_level := x_honours_level;
81 new_references.level_of_achievement_type := x_level_of_achievement_type;
82 new_references.grade_point_average := x_grade_point_average;
83 new_references.language_of_tuition := x_language_of_tuition;
84 new_references.state_cd := x_state_cd;
85 new_references.country_cd := x_country_cd;
86 new_references.notes := x_notes;
87 IF (p_action = 'UPDATE') THEN
88 new_references.creation_date := old_references.creation_date;
89 new_references.created_by := old_references.created_by;
90 ELSE
91 new_references.creation_date := x_creation_date;
92 new_references.created_by := x_created_by;
93 END IF;
94 new_references.last_update_date := x_last_update_date;
95 new_references.last_updated_by := x_last_updated_by;
96 new_references.last_update_login := x_last_update_login;
97
98 END Set_Column_Values;
99
100 PROCEDURE BeforeRowInsertUpdate1(
101 p_inserting IN BOOLEAN DEFAULT FALSE,
102 p_updating IN BOOLEAN DEFAULT FALSE,
103 p_deleting IN BOOLEAN DEFAULT FALSE
104 ) as
105 v_message_name VARCHAR2(30);
106 BEGIN
107 --
108 -- Validate Tertiary Education.
109 --
110 IF p_inserting
111 OR (old_references.tertiary_edu_lvl_comp <> new_references.tertiary_edu_lvl_comp) THEN
112 -- Validate tertiary education level of completion
113 IF IGS_AD_VAL_TE.admp_val_telocclosed(
114 new_references.tertiary_edu_lvl_comp,
115 v_message_name) = FALSE THEN
116 Fnd_Message.Set_Name('IGS', v_message_name);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 END IF;
121 IF (p_inserting AND new_references.tertiary_edu_lvl_qual IS NOT NULL)
122 OR (old_references.tertiary_edu_lvl_qual <> new_references.tertiary_edu_lvl_qual)
123 OR (old_references.tertiary_edu_lvl_qual IS NULL
124 AND new_references.tertiary_edu_lvl_qual IS NOT NULL) THEN
125 -- Validate tertiary education level of qualification
126 IF IGS_AD_VAL_TE.admp_val_teloqclosed(
127 new_references.tertiary_edu_lvl_qual,
128 v_message_name) = FALSE THEN
129 Fnd_Message.Set_Name('IGS', v_message_name);
130 IGS_GE_MSG_STACK.ADD;
131 App_Exception.Raise_Exception;
132 END IF;
133 END IF;
134 IF (p_inserting
135 OR (old_references.enrolment_first_yr <> new_references.enrolment_first_yr)
136 OR (old_references.enrolment_first_yr IS NULL
137 AND new_references.enrolment_first_yr IS NOT NULL)
138 OR (old_references.enrolment_latest_yr <> new_references.enrolment_latest_yr)
139 OR (old_references.enrolment_latest_yr IS NULL
140 AND new_references.enrolment_latest_yr IS NOT NULL))
141 AND (new_references.enrolment_first_yr IS NOT NULL
142 AND new_references.enrolment_latest_yr IS NOT NULL) THEN
143 -- Validate enrolment years
144 IF IGS_AD_VAL_TE.admp_val_te_enr_yr(
145 new_references.enrolment_first_yr,
146 new_references.enrolment_latest_yr,
147 v_message_name) = FALSE THEN
148 Fnd_Message.Set_Name('IGS', v_message_name);
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153 IF p_inserting
154 OR (old_references.institution_cd <> new_references.institution_cd)
155 OR (old_references.institution_cd IS NULL AND new_references.institution_cd IS NOT NULL)
156 OR (old_references.institution_name <> new_references.institution_name)
157 OR (old_references.institution_name IS NULL AND new_references.institution_name IS NOT NULL)
158 THEN
159 -- Validate the institution code and name
160 IF IGS_AD_VAL_TE.admp_val_te_inst(
161 new_references.institution_cd,
162 new_references.institution_name,
163 v_message_name) = FALSE THEN
164 Fnd_Message.Set_Name('IGS', v_message_name);
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170
171 END BeforeRowInsertUpdate1;
172
173 procedure Check_Constraints (
174 Column_Name IN VARCHAR2 DEFAULT NULL,
175 Column_Value IN VARCHAR2 DEFAULT NULL
176 )
177 AS
178 BEGIN
179
180 IF Column_Name is null then
181 NULL;
182 ELSIF upper(Column_Name) = 'SEQUENCE_NUMBER' then
183 new_references.sequence_number := igs_ge_number.to_num(column_value);
184 ELSIF upper(Column_Name) = 'ENROLMENT_FIRST_YR' then
185 new_references.enrolment_first_yr := igs_ge_number.to_num(column_value);
186 ELSIF upper(Column_Name) = 'ENROLMENT_LATEST_YR' then
187 new_references.enrolment_latest_yr := igs_ge_number.to_num(column_value);
188 ELSIF upper(Column_Name) = 'EXCLUSION_IND' then
189 new_references.exclusion_ind := column_value;
190 ELSIF upper(Column_Name) = 'COUNTRY_CD' then
191 new_references.country_cd := column_value;
192 ELSIF upper(Column_Name) = 'COURSE_CD' then
193 new_references.course_cd := column_value;
194 ELSIF upper(Column_Name) = 'COURSE_TITLE' then
195 new_references.course_title := column_value;
196 ELSIF upper(Column_Name) = 'FIELD_OF_STUDY' then
197 new_references.field_of_study := column_value;
198 ELSIF upper(Column_Name) = 'HONOURS_LEVEL' then
199 new_references.honours_level := column_value;
200 ELSIF upper(Column_Name) = 'INSTITUTION_CD' then
201 new_references.institution_cd := column_value;
202 ELSIF upper(Column_Name) = 'INSTITUTION_NAME' then
203 new_references.institution_name := column_value;
204 ELSIF upper(Column_Name) = 'LANGUAGE_COMPONENT' then
205 new_references.language_component := column_value;
206 ELSIF upper(Column_Name) = 'LANGUAGE_OF_TUITION' then
207 new_references.language_of_tuition := column_value;
208 ELSIF upper(Column_Name) = 'LEVEL_OF_ACHIEVEMENT_TYPE' then
209 new_references.level_of_achievement_type := column_value;
210 ELSIF upper(Column_Name) = 'QUALIFICATION' then
211 new_references.qualification := column_value;
212 ELSIF upper(Column_Name) = 'STUDENT_ID' then
213 new_references.student_id := column_value;
214 ELSIF upper(Column_Name) = 'TERTIARY_EDU_LVL_COMP' then
215 new_references.tertiary_edu_lvl_comp := column_value;
216 ELSIF upper(Column_Name) = 'TERTIARY_EDU_LVL_QUAL' then
217 new_references.tertiary_edu_lvl_qual := column_value;
218 ELSIF upper(Column_Name) = 'EQUIV_FULL_TIME_YRS_ENR' then
219 new_references.equiv_full_time_yrs_enr := igs_ge_number.to_num(column_value);
220 ELSIF upper(Column_Name) = 'GRADE_POINT_AVERAGE' then
221 new_references.grade_point_average := igs_ge_number.to_num(column_value);
222 ELSIF upper(Column_Name) = 'STATE_CD' then
223 new_references.state_cd := column_value;
224 END IF;
225
226 IF upper(Column_Name) = 'SEQUENCE_NUMBER' OR Column_Name IS NULL THEN
227 IF new_references.sequence_number < 1 OR new_references.sequence_number > 999999 THEN
228 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
229 IGS_GE_MSG_STACK.ADD;
230 App_Exception.Raise_Exception;
231 END IF;
232 END IF;
233 IF upper(Column_Name) = 'ENROLMENT_FIRST_YR' OR Column_Name IS NULL THEN
234 IF new_references.enrolment_first_yr < 1900 OR new_references.enrolment_first_yr > 2050 THEN
235 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
236 IGS_GE_MSG_STACK.ADD;
237 App_Exception.Raise_Exception;
238 END IF;
239 END IF;
240 IF upper(Column_Name) = 'ENROLMENT_LATEST_YR' OR Column_Name IS NULL THEN
241 IF new_references.enrolment_latest_yr < 1900 OR new_references.enrolment_latest_yr > 2050 THEN
242 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247 IF upper(Column_Name) = 'EXCLUSION_IND' OR Column_Name IS NULL THEN
248 IF new_references.exclusion_ind NOT IN ('Y','N') THEN
249 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
250 IGS_GE_MSG_STACK.ADD;
251 App_Exception.Raise_Exception;
252 END IF;
253 END IF;
254 IF upper(Column_Name) = 'COUNTRY_CD' OR Column_Name IS NULL THEN
255 IF new_references.country_cd <> UPPER(new_references.country_cd) THEN
256 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260 END IF;
261 IF upper(Column_Name) = 'COURSE_CD' OR Column_Name IS NULL THEN
262 IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
263 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
264 IGS_GE_MSG_STACK.ADD;
265 App_Exception.Raise_Exception;
266 END IF;
267 END IF;
268 IF upper(Column_Name) = 'COURSE_TITLE' OR Column_Name IS NULL THEN
269 IF new_references.course_title <> UPPER(new_references.course_title) THEN
270 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
271 IGS_GE_MSG_STACK.ADD;
272 App_Exception.Raise_Exception;
273 END IF;
274 END IF;
275 IF upper(Column_Name) = 'FIELD_OF_STUDY' OR Column_Name IS NULL THEN
276 IF new_references.field_of_study <> UPPER(new_references.field_of_study) THEN
277 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
278 IGS_GE_MSG_STACK.ADD;
279 App_Exception.Raise_Exception;
280 END IF;
281 END IF;
282 IF upper(Column_Name) = 'HONOURS_LEVEL' OR Column_Name IS NULL THEN
283 IF new_references.honours_level <> UPPER(new_references.honours_level) THEN
284 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
285 IGS_GE_MSG_STACK.ADD;
286 App_Exception.Raise_Exception;
287 END IF;
288 END IF;
289 IF upper(Column_Name) = 'INSTITUTION_NAME' OR Column_Name IS NULL THEN
290 IF new_references.institution_name <> UPPER(new_references.institution_name) THEN
291 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
292 IGS_GE_MSG_STACK.ADD;
293 App_Exception.Raise_Exception;
294 END IF;
295 END IF;
296 IF upper(Column_Name) = 'LANGUAGE_COMPONENT' OR Column_Name IS NULL THEN
297 IF new_references.language_component <> UPPER(new_references.language_component) THEN
298 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
299 IGS_GE_MSG_STACK.ADD;
300 App_Exception.Raise_Exception;
301 END IF;
302 END IF;
303 IF upper(Column_Name) = 'LANGUAGE_OF_TUITION' OR Column_Name IS NULL THEN
304 IF new_references.language_of_tuition <> UPPER(new_references.language_of_tuition) THEN
305 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
306 IGS_GE_MSG_STACK.ADD;
307 App_Exception.Raise_Exception;
308 END IF;
309 END IF;
310 IF upper(Column_Name) = 'LEVEL_OF_ACHIEVEMENT_TYPE' OR Column_Name IS NULL THEN
311 IF new_references.level_of_achievement_type <> UPPER(new_references.level_of_achievement_type) THEN
312 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
313 IGS_GE_MSG_STACK.ADD;
314 App_Exception.Raise_Exception;
315 END IF;
316 END IF;
317 IF upper(Column_Name) = 'QUALIFICATION' OR Column_Name IS NULL THEN
318 IF new_references.qualification <> UPPER(new_references.qualification) THEN
319 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323 END IF;
324 IF upper(Column_Name) = 'STUDENT_ID' OR Column_Name IS NULL THEN
325 IF new_references.student_id <> UPPER(new_references.student_id) THEN
326 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
327 IGS_GE_MSG_STACK.ADD;
328 App_Exception.Raise_Exception;
329 END IF;
330 END IF;
331 IF upper(Column_Name) = 'TERTIARY_EDU_LVL_COMP' OR Column_Name IS NULL THEN
332 IF new_references.tertiary_edu_lvl_comp <> UPPER(new_references.tertiary_edu_lvl_comp) THEN
333 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
334 IGS_GE_MSG_STACK.ADD;
335 App_Exception.Raise_Exception;
336 END IF;
337 END IF;
338 IF upper(Column_Name) = 'TERTIARY_EDU_LVL_QUAL' OR Column_Name IS NULL THEN
339 IF new_references.tertiary_edu_lvl_qual <> UPPER(new_references.tertiary_edu_lvl_qual) THEN
340 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
341 IGS_GE_MSG_STACK.ADD;
342 App_Exception.Raise_Exception;
343 END IF;
344 END IF;
345 IF upper(Column_Name) = 'EQUIV_FULL_TIME_YRS_ENR' OR Column_Name IS NULL THEN
346 IF new_references.equiv_full_time_yrs_enr < 0 OR new_references.equiv_full_time_yrs_enr > 99.99 THEN
347 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
348 IGS_GE_MSG_STACK.ADD;
349 App_Exception.Raise_Exception;
350 END IF;
351 END IF;
352 IF upper(Column_Name) = 'GRADE_POINT_AVERAGE' OR Column_Name IS NULL THEN
353 IF new_references.grade_point_average < 0 OR new_references.grade_point_average > 999.99 THEN
354 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
355 IGS_GE_MSG_STACK.ADD;
356 App_Exception.Raise_Exception;
357 END IF;
358 END IF;
359 IF upper(Column_Name) = 'STATE_CD' OR Column_Name IS NULL THEN
360 IF new_references.state_cd <> UPPER(new_references.state_cd) THEN
361 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 END IF;
365 END IF;
366
367 END Check_Constraints;
368
369 PROCEDURE Check_Parent_Existance as
370 BEGIN
371
372 IF (((old_references.country_cd = new_references.country_cd)) OR
373 ((new_references.country_cd IS NULL))) THEN
374 NULL;
375 ELSE
376 IF NOT IGS_PE_COUNTRY_CD_PKG.Get_PK_For_Validation (
377 new_references.country_cd
378 ) THEN
379 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
380 IGS_GE_MSG_STACK.ADD;
381 App_Exception.Raise_Exception;
382 END IF;
383 END IF;
384
385 IF (((old_references.field_of_study = new_references.field_of_study)) OR
386 ((new_references.field_of_study IS NULL))) THEN
387 NULL;
388 ELSE
389 IF NOT IGS_PS_FLD_OF_STUDY_PKG.Get_PK_For_Validation (
390 new_references.field_of_study
391 ) THEN
392 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
393 IGS_GE_MSG_STACK.ADD;
394 App_Exception.Raise_Exception;
395 END IF;
396 END IF;
397
398 IF (((old_references.honours_level = new_references.honours_level)) OR
399 ((new_references.honours_level IS NULL))) THEN
400 NULL;
401 ELSE
402 IF NOT IGS_GR_HONOURS_LEVEL_PKG.Get_PK_For_Validation (
403 new_references.honours_level
404 ) THEN
405 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
406 IGS_GE_MSG_STACK.ADD;
407 App_Exception.Raise_Exception;
408 END IF;
409 END IF;
410
411 IF (((old_references.institution_cd = new_references.institution_cd)) OR
412 ((new_references.institution_cd IS NULL))) THEN
413 NULL;
414 ELSE
415 IF NOT IGS_OR_INSTITUTION_PKG.Get_PK_For_Validation (
416 new_references.institution_cd
417 ) THEN
418 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
419 IGS_GE_MSG_STACK.ADD;
420 App_Exception.Raise_Exception;
421 END IF;
422 END IF;
423
424 IF (((old_references.language_component = new_references.language_component)) OR
425 ((new_references.language_component IS NULL))) THEN
426 NULL;
427 ELSE
428 IF NOT IGS_PE_LANGUAGE_CD_PKG.Get_PK_For_Validation (
429 new_references.language_component
430 ) THEN
431 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
432 IGS_GE_MSG_STACK.ADD;
433 App_Exception.Raise_Exception;
434 END IF;
435 END IF;
436
437 IF (((old_references.language_of_tuition = new_references.language_of_tuition)) OR
438 ((new_references.language_of_tuition IS NULL))) THEN
439 NULL;
440 ELSE
441 IF NOT IGS_PE_LANGUAGE_CD_PKG.Get_PK_For_Validation (
442 new_references.language_of_tuition
443 ) THEN
444 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
445 IGS_GE_MSG_STACK.ADD;
446 App_Exception.Raise_Exception;
447 END IF;
448 END IF;
449
450 IF (((old_references.person_id = new_references.person_id)) OR
451 ((new_references.person_id IS NULL))) THEN
452 NULL;
453 ELSE
454 IF NOT IGS_PE_PERSON_PKG.Get_PK_For_Validation (
455 new_references.person_id
456 ) THEN
457 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
458 IGS_GE_MSG_STACK.ADD;
459 App_Exception.Raise_Exception;
460 END IF;
461 END IF;
462
463 IF (((old_references.tertiary_edu_lvl_comp = new_references.tertiary_edu_lvl_comp)) OR
464 ((new_references.tertiary_edu_lvl_comp IS NULL))) THEN
465 NULL;
466 ELSE
467 IF NOT IGS_AD_TER_ED_LV_COM_PKG.Get_PK_For_Validation (
468 new_references.tertiary_edu_lvl_comp,
469 'N'
470 ) THEN
471 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 END IF;
476
477 IF (((old_references.tertiary_edu_lvl_qual = new_references.tertiary_edu_lvl_qual)) OR
478 ((new_references.tertiary_edu_lvl_qual IS NULL))) THEN
479 NULL;
480 ELSE
481 IF NOT IGS_AD_TER_ED_LVL_QF_PKG.Get_PK_For_Validation (
482 new_references.tertiary_edu_lvl_qual,
483 'N'
484 ) THEN
485 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
486 IGS_GE_MSG_STACK.ADD;
487 App_Exception.Raise_Exception;
488 END IF;
489 END IF;
490
491 END Check_Parent_Existance;
492
493 PROCEDURE Check_Child_Existance AS
494 BEGIN
495
496 IGS_AD_TER_ED_UNI_AT_PKG.GET_FK_IGS_AD_TER_EDU (
497 old_references.person_id,
498 old_references.sequence_number
499 );
500
501 END Check_Child_Existance;
502
503 function Get_PK_For_Validation (
504 x_person_id IN NUMBER,
505 x_sequence_number IN NUMBER
506 )return BOOLEAN AS
507
508 CURSOR cur_rowid IS
509 SELECT rowid
510 FROM IGS_AD_TER_EDU
511 WHERE person_id = x_person_id
512 AND sequence_number = x_sequence_number
513 FOR UPDATE NOWAIT;
514
515 lv_rowid cur_rowid%RowType;
516
517 BEGIN
518
519 Open cur_rowid;
520 Fetch cur_rowid INTO lv_rowid;
521 IF (cur_rowid%FOUND) THEN
522 Close cur_rowid;
523 Return(TRUE);
524 ELSE
525 Close cur_rowid;
526 Return(FALSE);
527 END IF;
528
529 END Get_PK_For_Validation;
530
531 PROCEDURE GET_FK_IGS_PE_COUNTRY_CD (
532 x_country_cd IN VARCHAR2
533 ) AS
534
535 CURSOR cur_rowid IS
536 SELECT rowid
537 FROM IGS_AD_TER_EDU
538 WHERE country_cd = x_country_cd ;
539
540 lv_rowid cur_rowid%RowType;
541
542 BEGIN
543
544 Open cur_rowid;
545 Fetch cur_rowid INTO lv_rowid;
546 IF (cur_rowid%FOUND) THEN
547 Close cur_rowid;
548 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_CNC_FK');
549 IGS_GE_MSG_STACK.ADD;
550 App_Exception.Raise_Exception;
551 Return;
552 END IF;
553 Close cur_rowid;
554
555 END GET_FK_IGS_PE_COUNTRY_CD;
556
557
558 PROCEDURE GET_FK_IGS_GR_HONOURS_LEVEL (
559 x_honours_level IN VARCHAR2
560 ) AS
561
562 CURSOR cur_rowid IS
563 SELECT rowid
564 FROM IGS_AD_TER_EDU
565 WHERE honours_level = x_honours_level ;
566
567 lv_rowid cur_rowid%RowType;
568
569 BEGIN
570
571 Open cur_rowid;
572 Fetch cur_rowid INTO lv_rowid;
573 IF (cur_rowid%FOUND) THEN
574 Close cur_rowid;
575 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_HL_FK');
576 IGS_GE_MSG_STACK.ADD;
577 App_Exception.Raise_Exception;
578 Return;
579 END IF;
580 Close cur_rowid;
581
582 END GET_FK_IGS_GR_HONOURS_LEVEL;
583
584 PROCEDURE GET_FK_IGS_OR_INSTITUTION (
585 x_institution_cd IN VARCHAR2
586 ) AS
587
588 CURSOR cur_rowid IS
589 SELECT rowid
590 FROM IGS_AD_TER_EDU
591 WHERE institution_cd = x_institution_cd ;
592
593 lv_rowid cur_rowid%RowType;
594
595 BEGIN
596
597 Open cur_rowid;
598 Fetch cur_rowid INTO lv_rowid;
599 IF (cur_rowid%FOUND) THEN
600 Close cur_rowid;
601 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_INS_FK');
602 IGS_GE_MSG_STACK.ADD;
603 App_Exception.Raise_Exception;
604 Return;
605 END IF;
606 Close cur_rowid;
607
608 END GET_FK_IGS_OR_INSTITUTION;
609
610 PROCEDURE GET_FK_IGS_PE_LANGUAGE_CD (
611 x_language_cd IN VARCHAR2
612 ) AS
613
614 CURSOR cur_rowid IS
615 SELECT rowid
616 FROM IGS_AD_TER_EDU
617 WHERE language_component = x_language_cd
618 OR language_of_tuition = x_language_cd ;
619
620 lv_rowid cur_rowid%RowType;
621
622 BEGIN
623
624 Open cur_rowid;
625 Fetch cur_rowid INTO lv_rowid;
626 IF (cur_rowid%FOUND) THEN
627 Close cur_rowid;
628 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_LC_COMPONENT_FK');
629 IGS_GE_MSG_STACK.ADD;
630 App_Exception.Raise_Exception;
631 Return;
632 END IF;
633 Close cur_rowid;
634
635 END GET_FK_IGS_PE_LANGUAGE_CD;
636
637
638 PROCEDURE GET_FK_IGS_PE_PERSON (
639 x_person_id IN NUMBER
640 ) AS
641
642 CURSOR cur_rowid IS
643 SELECT rowid
644 FROM IGS_AD_TER_EDU
645 WHERE person_id = x_person_id ;
646
647 lv_rowid cur_rowid%RowType;
648
649 BEGIN
650
651 Open cur_rowid;
652 Fetch cur_rowid INTO lv_rowid;
653 IF (cur_rowid%FOUND) THEN
654 Close cur_rowid;
655 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_PE_FK');
656 IGS_GE_MSG_STACK.ADD;
657 App_Exception.Raise_Exception;
658 Return;
659 END IF;
660 Close cur_rowid;
661
662 END GET_FK_IGS_PE_PERSON;
663
664 PROCEDURE GET_FK_IGS_AD_TER_EDU_LV_COM (
665 x_tertiary_edu_lvl_comp IN VARCHAR2
666 ) AS
667
668 CURSOR cur_rowid IS
669 SELECT rowid
670 FROM IGS_AD_TER_EDU
671 WHERE tertiary_edu_lvl_comp = x_tertiary_edu_lvl_comp ;
672
673 lv_rowid cur_rowid%RowType;
674
675 BEGIN
676
677 Open cur_rowid;
678 Fetch cur_rowid INTO lv_rowid;
679 IF (cur_rowid%FOUND) THEN
680 Close cur_rowid;
681 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_TELOC_FK');
682 IGS_GE_MSG_STACK.ADD;
683 App_Exception.Raise_Exception;
684 Return;
685 END IF;
686 Close cur_rowid;
687
688 END GET_FK_IGS_AD_TER_EDU_LV_COM;
689
690 PROCEDURE GET_FK_IGS_AD_TER_EDU_LVL_QF (
691 x_tertiary_edu_lvl_qual IN VARCHAR2
692 ) AS
693
694 CURSOR cur_rowid IS
695 SELECT rowid
696 FROM IGS_AD_TER_EDU
697 WHERE tertiary_edu_lvl_qual = x_tertiary_edu_lvl_qual ;
698
699 lv_rowid cur_rowid%RowType;
700
701 BEGIN
702
703 Open cur_rowid;
704 Fetch cur_rowid INTO lv_rowid;
705 IF (cur_rowid%FOUND) THEN
706 Close cur_rowid;
707 Fnd_Message.Set_Name ('IGS', 'IGS_AD_TE_TELOQ_FK');
708 IGS_GE_MSG_STACK.ADD;
709 App_Exception.Raise_Exception;
710 Return;
711 END IF;
712 Close cur_rowid;
713
714 END GET_FK_IGS_AD_TER_EDU_LVL_QF;
715
716 PROCEDURE Before_DML (
717 p_action IN VARCHAR2,
718 x_rowid IN VARCHAR2 DEFAULT NULL,
719 x_person_id IN NUMBER DEFAULT NULL,
720 x_sequence_number IN NUMBER DEFAULT NULL,
721 x_tertiary_edu_lvl_comp IN VARCHAR2 DEFAULT NULL,
722 x_exclusion_ind IN VARCHAR2 DEFAULT NULL,
723 x_institution_cd IN VARCHAR2 DEFAULT NULL,
724 x_institution_name IN VARCHAR2 DEFAULT NULL,
725 x_enrolment_first_yr IN NUMBER DEFAULT NULL,
726 x_enrolment_latest_yr IN NUMBER DEFAULT NULL,
727 x_course_cd IN VARCHAR2 DEFAULT NULL,
728 x_course_title IN VARCHAR2 DEFAULT NULL,
729 x_field_of_study IN VARCHAR2 DEFAULT NULL,
730 x_language_component IN VARCHAR2 DEFAULT NULL,
731 x_student_id IN VARCHAR2 DEFAULT NULL,
732 x_equiv_full_time_yrs_enr IN NUMBER DEFAULT NULL,
733 x_tertiary_edu_lvl_qual IN VARCHAR2 DEFAULT NULL,
734 x_qualification IN VARCHAR2 DEFAULT NULL,
735 x_honours_level IN VARCHAR2 DEFAULT NULL,
736 x_level_of_achievement_type IN VARCHAR2 DEFAULT NULL,
737 x_grade_point_average IN NUMBER DEFAULT NULL,
738 x_language_of_tuition IN VARCHAR2 DEFAULT NULL,
739 x_state_cd IN VARCHAR2 DEFAULT NULL,
740 x_country_cd IN VARCHAR2 DEFAULT NULL,
741 x_notes IN VARCHAR2 DEFAULT NULL,
742 x_creation_date IN DATE DEFAULT NULL,
743 x_created_by IN NUMBER DEFAULT NULL,
744 x_last_update_date IN DATE DEFAULT NULL,
745 x_last_updated_by IN NUMBER DEFAULT NULL,
746 x_last_update_login IN NUMBER DEFAULT NULL
747 ) AS
748 BEGIN
749
750 Set_Column_Values (
751 p_action,
752 x_rowid,
753 x_person_id,
754 x_sequence_number,
755 x_tertiary_edu_lvl_comp,
756 x_exclusion_ind,
757 x_institution_cd,
758 x_institution_name,
759 x_enrolment_first_yr,
760 x_enrolment_latest_yr,
761 x_course_cd,
762 x_course_title,
763 x_field_of_study,
764 x_language_component,
765 x_student_id,
766 x_equiv_full_time_yrs_enr,
767 x_tertiary_edu_lvl_qual,
768 x_qualification,
769 x_honours_level,
770 x_level_of_achievement_type,
771 x_grade_point_average,
772 x_language_of_tuition,
773 x_state_cd,
774 x_country_cd,
775 x_notes,
776 x_creation_date,
777 x_created_by,
778 x_last_update_date,
779 x_last_updated_by,
780 x_last_update_login
781 );
782
783 IF (p_action = 'INSERT') THEN
784 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
785 IF Get_PK_For_Validation (
786 new_references.person_id,
787 new_references.sequence_number
788 ) THEN
789 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
790 IGS_GE_MSG_STACK.ADD;
791 App_Exception.Raise_Exception;
792 END IF;
793 Check_Constraints;
794 Check_Parent_Existance;
795 ELSIF (p_action = 'UPDATE') THEN
796 BeforeRowInsertUpdate1 ( p_updating => TRUE );
797 Check_Constraints;
798 Check_Parent_Existance;
799 ELSIF (p_action = 'DELETE') THEN
800 Null;
801 Check_Child_Existance;
802 ELSIF (p_action = 'VALIDATE_INSERT') THEN
803 IF Get_PK_For_Validation (
804 new_references.person_id,
805 new_references.sequence_number
806 ) THEN
807 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
808 IGS_GE_MSG_STACK.ADD;
809 App_Exception.Raise_Exception;
810 END IF;
811 Check_Constraints;
812 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
813 Check_Constraints;
814 ELSIF (p_action = 'VALIDATE_DELETE') THEN
815 Check_Child_Existance;
816 END IF;
817
818 END Before_DML;
819
820 PROCEDURE After_DML (
821 p_action IN VARCHAR2,
822 x_rowid IN VARCHAR2
823 )as
824 BEGIN
825 l_rowid := x_rowid;
826 END After_DML;
827
828 procedure INSERT_ROW (
829 X_ROWID in out NOCOPY VARCHAR2,
830 X_PERSON_ID in NUMBER,
831 X_SEQUENCE_NUMBER in NUMBER,
832 X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
833 X_EXCLUSION_IND in VARCHAR2,
834 X_INSTITUTION_CD in VARCHAR2,
835 X_INSTITUTION_NAME in VARCHAR2,
836 X_ENROLMENT_FIRST_YR in NUMBER,
837 X_ENROLMENT_LATEST_YR in NUMBER,
838 X_COURSE_CD in VARCHAR2,
839 X_COURSE_TITLE in VARCHAR2,
840 X_FIELD_OF_STUDY in VARCHAR2,
841 X_LANGUAGE_COMPONENT in VARCHAR2,
842 X_STUDENT_ID in VARCHAR2,
843 X_EQUIV_FULL_TIME_YRS_ENR in NUMBER,
844 X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
845 X_QUALIFICATION in VARCHAR2,
846 X_HONOURS_LEVEL in VARCHAR2,
847 X_LEVEL_OF_ACHIEVEMENT_TYPE in VARCHAR2,
848 X_GRADE_POINT_AVERAGE in NUMBER,
849 X_LANGUAGE_OF_TUITION in VARCHAR2,
850 X_STATE_CD in VARCHAR2,
851 X_COUNTRY_CD in VARCHAR2,
852 X_NOTES in VARCHAR2,
853 X_MODE in VARCHAR2 default 'R'
854 ) as
855 cursor C is select ROWID from IGS_AD_TER_EDU
856 where PERSON_ID = X_PERSON_ID
857 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
858 X_LAST_UPDATE_DATE DATE;
859 X_LAST_UPDATED_BY NUMBER;
860 X_LAST_UPDATE_LOGIN NUMBER;
861 X_REQUEST_ID NUMBER;
862 X_PROGRAM_ID NUMBER;
863 X_PROGRAM_APPLICATION_ID NUMBER;
864 X_PROGRAM_UPDATE_DATE DATE;
865 begin
866 X_LAST_UPDATE_DATE := SYSDATE;
867 if(X_MODE = 'I') then
868 X_LAST_UPDATED_BY := 1;
869 X_LAST_UPDATE_LOGIN := 0;
870 elsif (X_MODE = 'R') then
871 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
872 if X_LAST_UPDATED_BY is NULL then
873 X_LAST_UPDATED_BY := -1;
874 end if;
875 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
876 if X_LAST_UPDATE_LOGIN is NULL then
877 X_LAST_UPDATE_LOGIN := -1;
878 end if;
879 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
880 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
881 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
882 if (X_REQUEST_ID = -1) then
883 X_REQUEST_ID := NULL;
884 X_PROGRAM_ID := NULL;
885 X_PROGRAM_APPLICATION_ID := NULL;
886 X_PROGRAM_UPDATE_DATE := NULL;
887 else
888 X_PROGRAM_UPDATE_DATE := SYSDATE;
889 end if;
890 else
891
892 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
893
894 IGS_GE_MSG_STACK.ADD;
895
896 app_exception.raise_exception;
897 end if;
898 Before_DML (
899 p_action => 'INSERT',
900 x_rowid => X_ROWID,
901 x_person_id => X_PERSON_ID,
902 x_sequence_number => X_SEQUENCE_NUMBER,
903 x_tertiary_edu_lvl_comp => X_TERTIARY_EDU_LVL_COMP,
904 x_exclusion_ind => NVL(X_EXCLUSION_IND,'N'),
905 x_institution_cd => X_INSTITUTION_CD,
906 x_institution_name => X_INSTITUTION_NAME,
907 x_enrolment_first_yr => X_ENROLMENT_FIRST_YR,
908 x_enrolment_latest_yr => X_ENROLMENT_LATEST_YR,
909 x_course_cd => X_COURSE_CD,
910 x_course_title => X_COURSE_TITLE,
911 x_field_of_study => X_FIELD_OF_STUDY,
912 x_language_component => X_LANGUAGE_COMPONENT,
913 x_student_id => X_STUDENT_ID,
914 x_equiv_full_time_yrs_enr => X_EQUIV_FULL_TIME_YRS_ENR,
915 x_tertiary_edu_lvl_qual => X_TERTIARY_EDU_LVL_QUAL,
916 x_qualification => X_QUALIFICATION,
917 x_honours_level => X_HONOURS_LEVEL,
918 x_level_of_achievement_type => X_LEVEL_OF_ACHIEVEMENT_TYPE,
919 x_grade_point_average => X_GRADE_POINT_AVERAGE,
920 x_language_of_tuition => X_LANGUAGE_OF_TUITION,
921 x_state_cd => X_STATE_CD,
922 x_country_cd => X_COUNTRY_CD,
923 x_notes => X_NOTES,
924 x_creation_date => X_LAST_UPDATE_DATE,
925 x_created_by => X_LAST_UPDATED_BY,
926 x_last_update_date => X_LAST_UPDATE_DATE,
927 x_last_updated_by => X_LAST_UPDATED_BY,
928 x_last_update_login => X_LAST_UPDATE_LOGIN
929 );
930 insert into IGS_AD_TER_EDU (
931 PERSON_ID,
932 SEQUENCE_NUMBER,
933 TERTIARY_EDU_LVL_COMP,
934 EXCLUSION_IND,
935 INSTITUTION_CD,
936 INSTITUTION_NAME,
937 ENROLMENT_FIRST_YR,
938 ENROLMENT_LATEST_YR,
939 COURSE_CD,
940 COURSE_TITLE,
941 FIELD_OF_STUDY,
942 LANGUAGE_COMPONENT,
943 STUDENT_ID,
944 EQUIV_FULL_TIME_YRS_ENR,
945 TERTIARY_EDU_LVL_QUAL,
946 QUALIFICATION,
947 HONOURS_LEVEL,
948 LEVEL_OF_ACHIEVEMENT_TYPE,
949 GRADE_POINT_AVERAGE,
950 LANGUAGE_OF_TUITION,
951 STATE_CD,
952 COUNTRY_CD,
953 NOTES,
954 CREATION_DATE,
955 CREATED_BY,
956 LAST_UPDATE_DATE,
957 LAST_UPDATED_BY,
958 LAST_UPDATE_LOGIN,
959 REQUEST_ID,
960 PROGRAM_ID,
961 PROGRAM_APPLICATION_ID,
962 PROGRAM_UPDATE_DATE
963 ) values (
964 NEW_REFERENCES.PERSON_ID,
965 NEW_REFERENCES.SEQUENCE_NUMBER,
966 NEW_REFERENCES.TERTIARY_EDU_LVL_COMP,
967 NEW_REFERENCES.EXCLUSION_IND,
968 NEW_REFERENCES.INSTITUTION_CD,
969 NEW_REFERENCES.INSTITUTION_NAME,
970 NEW_REFERENCES.ENROLMENT_FIRST_YR,
971 NEW_REFERENCES.ENROLMENT_LATEST_YR,
972 NEW_REFERENCES.COURSE_CD,
973 NEW_REFERENCES.COURSE_TITLE,
974 NEW_REFERENCES.FIELD_OF_STUDY,
975 NEW_REFERENCES.LANGUAGE_COMPONENT,
976 NEW_REFERENCES.STUDENT_ID,
977 NEW_REFERENCES.EQUIV_FULL_TIME_YRS_ENR,
978 NEW_REFERENCES.TERTIARY_EDU_LVL_QUAL,
979 NEW_REFERENCES.QUALIFICATION,
980 NEW_REFERENCES.HONOURS_LEVEL,
981 NEW_REFERENCES.LEVEL_OF_ACHIEVEMENT_TYPE,
982 NEW_REFERENCES.GRADE_POINT_AVERAGE,
983 NEW_REFERENCES.LANGUAGE_OF_TUITION,
984 NEW_REFERENCES.STATE_CD,
985 NEW_REFERENCES.COUNTRY_CD,
986 NEW_REFERENCES.NOTES,
987 X_LAST_UPDATE_DATE,
988 X_LAST_UPDATED_BY,
989 X_LAST_UPDATE_DATE,
990 X_LAST_UPDATED_BY,
991 X_LAST_UPDATE_LOGIN,
992 X_REQUEST_ID,
993 X_PROGRAM_ID,
994 X_PROGRAM_APPLICATION_ID,
995 X_PROGRAM_UPDATE_DATE
996 );
997
998 open c;
999 fetch c into X_ROWID;
1000 if (c%notfound) then
1001 close c;
1002 raise no_data_found;
1003 end if;
1004 close c;
1005 After_DML (
1006 p_action => 'INSERT',
1007 x_rowid => X_ROWID
1008 );
1009
1010 end INSERT_ROW;
1011
1012 procedure LOCK_ROW (
1013 X_ROWID in VARCHAR2,
1014 X_PERSON_ID in NUMBER,
1015 X_SEQUENCE_NUMBER in NUMBER,
1016 X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
1017 X_EXCLUSION_IND in VARCHAR2,
1018 X_INSTITUTION_CD in VARCHAR2,
1019 X_INSTITUTION_NAME in VARCHAR2,
1020 X_ENROLMENT_FIRST_YR in NUMBER,
1021 X_ENROLMENT_LATEST_YR in NUMBER,
1022 X_COURSE_CD in VARCHAR2,
1023 X_COURSE_TITLE in VARCHAR2,
1024 X_FIELD_OF_STUDY in VARCHAR2,
1025 X_LANGUAGE_COMPONENT in VARCHAR2,
1026 X_STUDENT_ID in VARCHAR2,
1027 X_EQUIV_FULL_TIME_YRS_ENR in NUMBER,
1028 X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
1029 X_QUALIFICATION in VARCHAR2,
1030 X_HONOURS_LEVEL in VARCHAR2,
1031 X_LEVEL_OF_ACHIEVEMENT_TYPE in VARCHAR2,
1032 X_GRADE_POINT_AVERAGE in NUMBER,
1033 X_LANGUAGE_OF_TUITION in VARCHAR2,
1034 X_STATE_CD in VARCHAR2,
1035 X_COUNTRY_CD in VARCHAR2,
1036 X_NOTES in VARCHAR2
1037 ) as
1038 cursor c1 is select
1039 TERTIARY_EDU_LVL_COMP,
1040 EXCLUSION_IND,
1041 INSTITUTION_CD,
1042 INSTITUTION_NAME,
1043 ENROLMENT_FIRST_YR,
1044 ENROLMENT_LATEST_YR,
1045 COURSE_CD,
1046 COURSE_TITLE,
1047 FIELD_OF_STUDY,
1048 LANGUAGE_COMPONENT,
1049 STUDENT_ID,
1050 EQUIV_FULL_TIME_YRS_ENR,
1051 TERTIARY_EDU_LVL_QUAL,
1052 QUALIFICATION,
1053 HONOURS_LEVEL,
1054 LEVEL_OF_ACHIEVEMENT_TYPE,
1055 GRADE_POINT_AVERAGE,
1056 LANGUAGE_OF_TUITION,
1057 STATE_CD,
1058 COUNTRY_CD,
1059 NOTES
1060 from IGS_AD_TER_EDU
1061 where ROWID = X_ROWID
1062 for update nowait;
1063 tlinfo c1%rowtype;
1064
1065 begin
1066 open c1;
1067 fetch c1 into tlinfo;
1068 if (c1%notfound) then
1069 close c1;
1070 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1071 IGS_GE_MSG_STACK.ADD;
1072 app_exception.raise_exception;
1073 return;
1074 end if;
1075 close c1;
1076
1077 if ( (tlinfo.TERTIARY_EDU_LVL_COMP = X_TERTIARY_EDU_LVL_COMP)
1078 AND (tlinfo.EXCLUSION_IND = X_EXCLUSION_IND)
1079 AND ((tlinfo.INSTITUTION_CD = X_INSTITUTION_CD)
1080 OR ((tlinfo.INSTITUTION_CD is null)
1081 AND (X_INSTITUTION_CD is null)))
1082 AND ((tlinfo.INSTITUTION_NAME = X_INSTITUTION_NAME)
1083 OR ((tlinfo.INSTITUTION_NAME is null)
1084 AND (X_INSTITUTION_NAME is null)))
1085 AND ((tlinfo.ENROLMENT_FIRST_YR = X_ENROLMENT_FIRST_YR)
1086 OR ((tlinfo.ENROLMENT_FIRST_YR is null)
1087 AND (X_ENROLMENT_FIRST_YR is null)))
1088 AND ((tlinfo.ENROLMENT_LATEST_YR = X_ENROLMENT_LATEST_YR)
1089 OR ((tlinfo.ENROLMENT_LATEST_YR is null)
1090 AND (X_ENROLMENT_LATEST_YR is null)))
1091 AND ((tlinfo.COURSE_CD = X_COURSE_CD)
1092 OR ((tlinfo.COURSE_CD is null)
1093 AND (X_COURSE_CD is null)))
1094 AND ((tlinfo.COURSE_TITLE = X_COURSE_TITLE)
1095 OR ((tlinfo.COURSE_TITLE is null)
1096 AND (X_COURSE_TITLE is null)))
1097 AND ((tlinfo.FIELD_OF_STUDY = X_FIELD_OF_STUDY)
1098 OR ((tlinfo.FIELD_OF_STUDY is null)
1099 AND (X_FIELD_OF_STUDY is null)))
1100 AND ((tlinfo.LANGUAGE_COMPONENT = X_LANGUAGE_COMPONENT)
1101 OR ((tlinfo.LANGUAGE_COMPONENT is null)
1102 AND (X_LANGUAGE_COMPONENT is null)))
1103 AND ((tlinfo.STUDENT_ID = X_STUDENT_ID)
1104 OR ((tlinfo.STUDENT_ID is null)
1105 AND (X_STUDENT_ID is null)))
1106 AND ((tlinfo.EQUIV_FULL_TIME_YRS_ENR = X_EQUIV_FULL_TIME_YRS_ENR)
1107 OR ((tlinfo.EQUIV_FULL_TIME_YRS_ENR is null)
1108 AND (X_EQUIV_FULL_TIME_YRS_ENR is null)))
1109 AND ((tlinfo.TERTIARY_EDU_LVL_QUAL = X_TERTIARY_EDU_LVL_QUAL)
1110 OR ((tlinfo.TERTIARY_EDU_LVL_QUAL is null)
1111 AND (X_TERTIARY_EDU_LVL_QUAL is null)))
1112 AND ((tlinfo.QUALIFICATION = X_QUALIFICATION)
1113 OR ((tlinfo.QUALIFICATION is null)
1114 AND (X_QUALIFICATION is null)))
1115 AND ((tlinfo.HONOURS_LEVEL = X_HONOURS_LEVEL)
1116 OR ((tlinfo.HONOURS_LEVEL is null)
1117 AND (X_HONOURS_LEVEL is null)))
1118 AND ((tlinfo.LEVEL_OF_ACHIEVEMENT_TYPE = X_LEVEL_OF_ACHIEVEMENT_TYPE)
1119 OR ((tlinfo.LEVEL_OF_ACHIEVEMENT_TYPE is null)
1120 AND (X_LEVEL_OF_ACHIEVEMENT_TYPE is null)))
1121 AND ((tlinfo.GRADE_POINT_AVERAGE = X_GRADE_POINT_AVERAGE)
1122 OR ((tlinfo.GRADE_POINT_AVERAGE is null)
1123 AND (X_GRADE_POINT_AVERAGE is null)))
1124 AND ((tlinfo.LANGUAGE_OF_TUITION = X_LANGUAGE_OF_TUITION)
1125 OR ((tlinfo.LANGUAGE_OF_TUITION is null)
1126 AND (X_LANGUAGE_OF_TUITION is null)))
1127 AND ((tlinfo.STATE_CD = X_STATE_CD)
1128 OR ((tlinfo.STATE_CD is null)
1129 AND (X_STATE_CD is null)))
1130 AND ((tlinfo.COUNTRY_CD = X_COUNTRY_CD)
1131 OR ((tlinfo.COUNTRY_CD is null)
1132 AND (X_COUNTRY_CD is null)))
1133 AND ((tlinfo.NOTES = X_NOTES)
1134 OR ((tlinfo.NOTES is null)
1135 AND (X_NOTES is null)))
1136 ) then
1137 null;
1138 else
1139 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1140 IGS_GE_MSG_STACK.ADD;
1141 app_exception.raise_exception;
1142 end if;
1143 return;
1144 end LOCK_ROW;
1145
1146 procedure UPDATE_ROW (
1147 X_ROWID in VARCHAR2,
1148 X_PERSON_ID in NUMBER,
1149 X_SEQUENCE_NUMBER in NUMBER,
1150 X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
1151 X_EXCLUSION_IND in VARCHAR2,
1152 X_INSTITUTION_CD in VARCHAR2,
1153 X_INSTITUTION_NAME in VARCHAR2,
1154 X_ENROLMENT_FIRST_YR in NUMBER,
1155 X_ENROLMENT_LATEST_YR in NUMBER,
1156 X_COURSE_CD in VARCHAR2,
1157 X_COURSE_TITLE in VARCHAR2,
1158 X_FIELD_OF_STUDY in VARCHAR2,
1159 X_LANGUAGE_COMPONENT in VARCHAR2,
1160 X_STUDENT_ID in VARCHAR2,
1161 X_EQUIV_FULL_TIME_YRS_ENR in NUMBER,
1162 X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
1163 X_QUALIFICATION in VARCHAR2,
1164 X_HONOURS_LEVEL in VARCHAR2,
1165 X_LEVEL_OF_ACHIEVEMENT_TYPE in VARCHAR2,
1166 X_GRADE_POINT_AVERAGE in NUMBER,
1167 X_LANGUAGE_OF_TUITION in VARCHAR2,
1168 X_STATE_CD in VARCHAR2,
1169 X_COUNTRY_CD in VARCHAR2,
1170 X_NOTES in VARCHAR2,
1171 X_MODE in VARCHAR2 default 'R'
1172 ) as
1173 X_LAST_UPDATE_DATE DATE;
1174 X_LAST_UPDATED_BY NUMBER;
1175 X_LAST_UPDATE_LOGIN NUMBER;
1176 X_REQUEST_ID NUMBER;
1177 X_PROGRAM_ID NUMBER;
1178 X_PROGRAM_APPLICATION_ID NUMBER;
1179 X_PROGRAM_UPDATE_DATE DATE;
1180 begin
1181 X_LAST_UPDATE_DATE := SYSDATE;
1182 if(X_MODE = 'I') then
1183 X_LAST_UPDATED_BY := 1;
1184 X_LAST_UPDATE_LOGIN := 0;
1185 elsif (X_MODE = 'R') then
1186 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1187 if X_LAST_UPDATED_BY is NULL then
1188 X_LAST_UPDATED_BY := -1;
1189 end if;
1190 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1191 if X_LAST_UPDATE_LOGIN is NULL then
1192 X_LAST_UPDATE_LOGIN := -1;
1193 end if;
1194 else
1195 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1196 IGS_GE_MSG_STACK.ADD;
1197 app_exception.raise_exception;
1198 end if;
1199 Before_DML (
1200 p_action => 'UPDATE',
1201 x_rowid => X_ROWID,
1202 x_person_id => X_PERSON_ID,
1203 x_sequence_number => X_SEQUENCE_NUMBER,
1204 x_tertiary_edu_lvl_comp => X_TERTIARY_EDU_LVL_COMP,
1205 x_exclusion_ind => X_EXCLUSION_IND,
1206 x_institution_cd => X_INSTITUTION_CD,
1207 x_institution_name => X_INSTITUTION_NAME,
1208 x_enrolment_first_yr => X_ENROLMENT_FIRST_YR,
1209 x_enrolment_latest_yr => X_ENROLMENT_LATEST_YR,
1210 x_course_cd => X_COURSE_CD,
1211 x_course_title => X_COURSE_TITLE,
1212 x_field_of_study => X_FIELD_OF_STUDY,
1213 x_language_component => X_LANGUAGE_COMPONENT,
1214 x_student_id => X_STUDENT_ID,
1215 x_equiv_full_time_yrs_enr => X_EQUIV_FULL_TIME_YRS_ENR,
1216 x_tertiary_edu_lvl_qual => X_TERTIARY_EDU_LVL_QUAL,
1217 x_qualification => X_QUALIFICATION,
1218 x_honours_level => X_HONOURS_LEVEL,
1219 x_level_of_achievement_type => X_LEVEL_OF_ACHIEVEMENT_TYPE,
1220 x_grade_point_average => X_GRADE_POINT_AVERAGE,
1221 x_language_of_tuition => X_LANGUAGE_OF_TUITION,
1222 x_state_cd => X_STATE_CD,
1223 x_country_cd => X_COUNTRY_CD,
1224 x_notes => X_NOTES,
1225 x_creation_date => X_LAST_UPDATE_DATE,
1226 x_created_by => X_LAST_UPDATED_BY,
1227 x_last_update_date => X_LAST_UPDATE_DATE,
1228 x_last_updated_by => X_LAST_UPDATED_BY,
1229 x_last_update_login => X_LAST_UPDATE_LOGIN
1230 );
1231 if (X_MODE = 'R') then
1232 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1233 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1234 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1235 if (X_REQUEST_ID = -1) then
1236 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1237 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1238 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1239 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1240 else
1241 X_PROGRAM_UPDATE_DATE := SYSDATE;
1242 end if;
1243 end if;
1244
1245 update IGS_AD_TER_EDU set
1246 TERTIARY_EDU_LVL_COMP = NEW_REFERENCES.TERTIARY_EDU_LVL_COMP,
1247 EXCLUSION_IND = NEW_REFERENCES.EXCLUSION_IND,
1248 INSTITUTION_CD = NEW_REFERENCES.INSTITUTION_CD,
1249 INSTITUTION_NAME = NEW_REFERENCES.INSTITUTION_NAME,
1250 ENROLMENT_FIRST_YR = NEW_REFERENCES.ENROLMENT_FIRST_YR,
1251 ENROLMENT_LATEST_YR = NEW_REFERENCES.ENROLMENT_LATEST_YR,
1252 COURSE_CD = NEW_REFERENCES.COURSE_CD,
1253 COURSE_TITLE = NEW_REFERENCES.COURSE_TITLE,
1254 FIELD_OF_STUDY = NEW_REFERENCES.FIELD_OF_STUDY,
1255 LANGUAGE_COMPONENT = NEW_REFERENCES.LANGUAGE_COMPONENT,
1256 STUDENT_ID = NEW_REFERENCES.STUDENT_ID,
1257 EQUIV_FULL_TIME_YRS_ENR = NEW_REFERENCES.EQUIV_FULL_TIME_YRS_ENR,
1258 TERTIARY_EDU_LVL_QUAL = NEW_REFERENCES.TERTIARY_EDU_LVL_QUAL,
1259 QUALIFICATION = NEW_REFERENCES.QUALIFICATION,
1260 HONOURS_LEVEL = NEW_REFERENCES.HONOURS_LEVEL,
1261 LEVEL_OF_ACHIEVEMENT_TYPE = NEW_REFERENCES.LEVEL_OF_ACHIEVEMENT_TYPE,
1262 GRADE_POINT_AVERAGE = NEW_REFERENCES.GRADE_POINT_AVERAGE,
1263 LANGUAGE_OF_TUITION = NEW_REFERENCES.LANGUAGE_OF_TUITION,
1264 STATE_CD = NEW_REFERENCES.STATE_CD,
1265 COUNTRY_CD = NEW_REFERENCES.COUNTRY_CD,
1266 NOTES = NEW_REFERENCES.NOTES,
1267 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1268 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1269 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1270 REQUEST_ID = X_REQUEST_ID,
1271 PROGRAM_ID = X_PROGRAM_ID,
1272 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1273 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1274 where ROWID = X_ROWID
1275 ;
1276 if (sql%notfound) then
1277 raise no_data_found;
1278 end if;
1279 After_DML (
1280 p_action => 'UPDATE',
1281 x_rowid => X_ROWID
1282 );
1283 end UPDATE_ROW;
1284
1285 procedure ADD_ROW (
1286 X_ROWID in out NOCOPY VARCHAR2,
1287 X_PERSON_ID in NUMBER,
1288 X_SEQUENCE_NUMBER in NUMBER,
1289 X_TERTIARY_EDU_LVL_COMP in VARCHAR2,
1290 X_EXCLUSION_IND in VARCHAR2,
1291 X_INSTITUTION_CD in VARCHAR2,
1292 X_INSTITUTION_NAME in VARCHAR2,
1293 X_ENROLMENT_FIRST_YR in NUMBER,
1294 X_ENROLMENT_LATEST_YR in NUMBER,
1295 X_COURSE_CD in VARCHAR2,
1296 X_COURSE_TITLE in VARCHAR2,
1297 X_FIELD_OF_STUDY in VARCHAR2,
1298 X_LANGUAGE_COMPONENT in VARCHAR2,
1299 X_STUDENT_ID in VARCHAR2,
1300 X_EQUIV_FULL_TIME_YRS_ENR in NUMBER,
1301 X_TERTIARY_EDU_LVL_QUAL in VARCHAR2,
1302 X_QUALIFICATION in VARCHAR2,
1303 X_HONOURS_LEVEL in VARCHAR2,
1304 X_LEVEL_OF_ACHIEVEMENT_TYPE in VARCHAR2,
1305 X_GRADE_POINT_AVERAGE in NUMBER,
1306 X_LANGUAGE_OF_TUITION in VARCHAR2,
1307 X_STATE_CD in VARCHAR2,
1308 X_COUNTRY_CD in VARCHAR2,
1309 X_NOTES in VARCHAR2,
1310 X_MODE in VARCHAR2 default 'R'
1311 ) as
1312 cursor c1 is select rowid from IGS_AD_TER_EDU
1313 where PERSON_ID = X_PERSON_ID
1314 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1315 ;
1316 begin
1317 open c1;
1318 fetch c1 into X_ROWID;
1319 if (c1%notfound) then
1320 close c1;
1321 INSERT_ROW (
1322 X_ROWID,
1323 X_PERSON_ID,
1324 X_SEQUENCE_NUMBER,
1325 X_TERTIARY_EDU_LVL_COMP,
1326 X_EXCLUSION_IND,
1327 X_INSTITUTION_CD,
1328 X_INSTITUTION_NAME,
1329 X_ENROLMENT_FIRST_YR,
1330 X_ENROLMENT_LATEST_YR,
1331 X_COURSE_CD,
1332 X_COURSE_TITLE,
1333 X_FIELD_OF_STUDY,
1334 X_LANGUAGE_COMPONENT,
1335 X_STUDENT_ID,
1336 X_EQUIV_FULL_TIME_YRS_ENR,
1337 X_TERTIARY_EDU_LVL_QUAL,
1338 X_QUALIFICATION,
1339 X_HONOURS_LEVEL,
1340 X_LEVEL_OF_ACHIEVEMENT_TYPE,
1341 X_GRADE_POINT_AVERAGE,
1342 X_LANGUAGE_OF_TUITION,
1343 X_STATE_CD,
1344 X_COUNTRY_CD,
1345 X_NOTES,
1346 X_MODE);
1347 return;
1348 end if;
1349 close c1;
1350 UPDATE_ROW (
1351 X_ROWID,
1352 X_PERSON_ID,
1353 X_SEQUENCE_NUMBER,
1354 X_TERTIARY_EDU_LVL_COMP,
1355 X_EXCLUSION_IND,
1356 X_INSTITUTION_CD,
1357 X_INSTITUTION_NAME,
1358 X_ENROLMENT_FIRST_YR,
1359 X_ENROLMENT_LATEST_YR,
1360 X_COURSE_CD,
1361 X_COURSE_TITLE,
1362 X_FIELD_OF_STUDY,
1363 X_LANGUAGE_COMPONENT,
1364 X_STUDENT_ID,
1365 X_EQUIV_FULL_TIME_YRS_ENR,
1366 X_TERTIARY_EDU_LVL_QUAL,
1367 X_QUALIFICATION,
1368 X_HONOURS_LEVEL,
1369 X_LEVEL_OF_ACHIEVEMENT_TYPE,
1370 X_GRADE_POINT_AVERAGE,
1371 X_LANGUAGE_OF_TUITION,
1372 X_STATE_CD,
1373 X_COUNTRY_CD,
1374 X_NOTES,
1375 X_MODE);
1376 end ADD_ROW;
1377
1378 procedure DELETE_ROW (
1379 X_ROWID in VARCHAR2
1380 ) as
1381 begin
1382 Before_DML (
1383 p_action => 'DELETE',
1384 x_rowid => X_ROWID
1385 );
1386 delete from IGS_AD_TER_EDU
1387 where ROWID = X_ROWID;
1388 if (sql%notfound) then
1389 raise no_data_found;
1390 end if;
1391 After_DML (
1392 p_action => 'DELETE',
1393 x_rowid => X_ROWID
1394 );
1395 end DELETE_ROW;
1396
1397 end IGS_AD_TER_EDU_PKG;