[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GOVT_STDNT_EN_PKG
Source
1 package body IGS_ST_GOVT_STDNT_EN_PKG as
2 /* $Header: IGSVI10B.pls 115.4 2002/11/29 04:33:14 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_ST_GOVT_STDNT_EN%RowType;
6 new_references IGS_ST_GOVT_STDNT_EN%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_submission_yr IN NUMBER DEFAULT NULL,
12 x_submission_number IN NUMBER DEFAULT NULL,
13 x_person_id IN NUMBER DEFAULT NULL,
14 x_course_cd IN VARCHAR2 DEFAULT NULL,
15 x_version_number IN NUMBER DEFAULT NULL,
16 x_birth_dt IN DATE DEFAULT NULL,
17 x_sex IN VARCHAR2 DEFAULT NULL,
18 x_aborig_torres_cd IN VARCHAR2 DEFAULT NULL,
19 x_govt_aborig_torres_cd IN NUMBER DEFAULT NULL,
20 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
21 x_govt_citizenship_cd IN NUMBER DEFAULT NULL,
22 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
23 x_govt_perm_resident_cd IN NUMBER DEFAULT NULL,
24 x_home_location IN VARCHAR2 DEFAULT NULL,
25 x_govt_home_location IN VARCHAR2 DEFAULT NULL,
26 x_term_location IN VARCHAR2 DEFAULT NULL,
27 x_govt_term_location IN VARCHAR2 DEFAULT NULL,
28 x_birth_country_cd IN VARCHAR2 DEFAULT NULL,
29 x_govt_birth_country_cd IN VARCHAR2 DEFAULT NULL,
30 x_yr_arrival IN VARCHAR2 DEFAULT NULL,
31 x_home_language_cd IN VARCHAR2 DEFAULT NULL,
32 x_govt_home_language_cd IN NUMBER DEFAULT NULL,
33 x_prior_ug_inst IN VARCHAR2 DEFAULT NULL,
34 x_govt_prior_ug_inst IN VARCHAR2 DEFAULT NULL,
35 x_prior_other_qual IN VARCHAR2 DEFAULT NULL,
36 x_prior_post_grad IN VARCHAR2 DEFAULT NULL,
37 x_prior_degree IN VARCHAR2 DEFAULT NULL,
38 x_prior_subdeg_notafe IN VARCHAR2 DEFAULT NULL,
39 x_prior_subdeg_tafe IN VARCHAR2 DEFAULT NULL,
40 x_prior_seced_tafe IN VARCHAR2 DEFAULT NULL,
41 x_prior_seced_school IN VARCHAR2 DEFAULT NULL,
42 x_prior_tafe_award IN VARCHAR2 DEFAULT NULL,
43 x_prior_studies_exemption IN NUMBER DEFAULT NULL,
44 x_exemption_institution_cd IN VARCHAR2 DEFAULT NULL,
45 x_govt_exempt_institu_cd IN VARCHAR2 DEFAULT NULL,
46 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
47 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
48 x_attendance_type IN VARCHAR2 DEFAULT NULL,
49 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
50 x_commencement_dt IN DATE DEFAULT NULL,
51 x_major_course IN NUMBER DEFAULT NULL,
52 x_tertiary_entrance_score IN NUMBER DEFAULT NULL,
53 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
54 x_govt_basis_for_adm_type IN VARCHAR2 DEFAULT NULL,
55 x_govt_disability IN VARCHAR2 DEFAULT NULL,
56 x_creation_date IN DATE DEFAULT NULL,
57 x_created_by IN NUMBER DEFAULT NULL,
58 x_last_update_date IN DATE DEFAULT NULL,
59 x_last_updated_by IN NUMBER DEFAULT NULL,
60 x_last_update_login IN NUMBER DEFAULT NULL
61 ) AS
62
63 CURSOR cur_old_ref_values IS
64 SELECT *
65 FROM IGS_ST_GOVT_STDNT_EN
66 WHERE rowid = x_rowid;
67
68 BEGIN
69
70 l_rowid := x_rowid;
71
72 -- Code for setting the Old and New Reference Values.
73 -- Populate Old Values.
74 Open cur_old_ref_values;
75 Fetch cur_old_ref_values INTO old_references;
76 IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
77 Close cur_old_ref_values;
78 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
79 IGS_GE_MSG_STACK.ADD;
80 App_Exception.Raise_Exception;
81 Return;
82 END IF;
83 Close cur_old_ref_values;
84
85 -- Populate New Values.
86 new_references.submission_yr := x_submission_yr;
87 new_references.submission_number := x_submission_number;
88 new_references.person_id := x_person_id;
89 new_references.course_cd := x_course_cd;
90 new_references.version_number := x_version_number;
91 new_references.birth_dt := x_birth_dt;
92 new_references.sex := x_sex;
93 new_references.aborig_torres_cd := x_aborig_torres_cd;
94 new_references.govt_aborig_torres_cd := x_govt_aborig_torres_cd;
95 new_references.citizenship_cd := x_citizenship_cd;
96 new_references.govt_citizenship_cd := x_govt_citizenship_cd;
97 new_references.perm_resident_cd := x_perm_resident_cd;
98 new_references.govt_perm_resident_cd := x_govt_perm_resident_cd;
99 new_references.home_location := x_home_location;
100 new_references.govt_home_location := x_govt_home_location;
101 new_references.term_location := x_term_location;
102 new_references.govt_term_location := x_govt_term_location;
103 new_references.birth_country_cd := x_birth_country_cd;
104 new_references.govt_birth_country_cd := x_govt_birth_country_cd;
105 new_references.yr_arrival := x_yr_arrival;
106 new_references.home_language_cd := x_home_language_cd;
107 new_references.govt_home_language_cd := x_govt_home_language_cd;
108 new_references.prior_ug_inst := x_prior_ug_inst;
109 new_references.govt_prior_ug_inst := x_govt_prior_ug_inst;
110 new_references.prior_other_qual := x_prior_other_qual;
111 new_references.prior_post_grad := x_prior_post_grad;
112 new_references.prior_degree := x_prior_degree;
113 new_references.prior_subdeg_notafe := x_prior_subdeg_notafe;
114 new_references.prior_subdeg_tafe := x_prior_subdeg_tafe;
115 new_references.prior_seced_tafe := x_prior_seced_tafe;
116 new_references.prior_seced_school := x_prior_seced_school;
117 new_references.prior_tafe_award := x_prior_tafe_award;
118 new_references.prior_studies_exemption := x_prior_studies_exemption;
119 new_references.exemption_institution_cd := x_exemption_institution_cd;
120 new_references.govt_exemption_institution_cd := x_govt_exempt_institu_cd;
121 new_references.attendance_mode := x_attendance_mode;
122 new_references.govt_attendance_mode := x_govt_attendance_mode;
123 new_references.attendance_type := x_attendance_type;
124 new_references.govt_attendance_type := x_govt_attendance_type;
125 new_references.commencement_dt := x_commencement_dt;
126 new_references.major_course := x_major_course;
127 new_references.tertiary_entrance_score := x_tertiary_entrance_score;
128 new_references.basis_for_admission_type := x_basis_for_admission_type;
129 new_references.govt_basis_for_admission_type := x_govt_basis_for_adm_type;
130 new_references.govt_disability := x_govt_disability;
131 IF (p_action = 'UPDATE') THEN
132 new_references.creation_date := old_references.creation_date;
133 new_references.created_by := old_references.created_by;
134 ELSE
135 new_references.creation_date := x_creation_date;
136 new_references.created_by := x_created_by;
137 END IF;
138 new_references.last_update_date := x_last_update_date;
139 new_references.last_updated_by := x_last_updated_by;
140 new_references.last_update_login := x_last_update_login;
141
142 END Set_Column_Values;
143
144 PROCEDURE BeforeRowInsertUpdateDelete1(
145 p_inserting IN BOOLEAN DEFAULT FALSE,
146 p_updating IN BOOLEAN DEFAULT FALSE,
147 p_deleting IN BOOLEAN DEFAULT FALSE
148 ) AS
149 v_message_name VARCHAR2(30);
150 v_transaction_type VARCHAR2(11);
151 v_submission_yr IGS_ST_GOVT_STDNT_EN.submission_yr%TYPE;
152 v_submission_number IGS_ST_GOVT_STDNT_EN.submission_number%TYPE;
153 BEGIN
154 IF p_inserting THEN
155 v_transaction_type := 'p_inserting';
156 v_submission_yr := new_references.submission_yr;
157 v_submission_number := new_references.submission_number;
158 ELSIF p_updating THEN
159 v_transaction_type := 'p_updating';
160 v_submission_yr := new_references.submission_yr;
161 v_submission_number := new_references.submission_number;
162 ELSIF p_deleting THEN
163 v_transaction_type := 'p_deleting';
164 v_submission_yr := old_references.submission_yr;
165 v_submission_number := old_references.submission_number;
166 END IF;
167 IF IGS_ST_VAL_GSE.stap_val_govt_snpsht (
168 v_submission_yr,
169 v_submission_number,
170 v_transaction_type,
171 v_message_name) = FALSE THEN
172 Fnd_Message.Set_Name('IGS', v_message_name);
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176
177
178 END BeforeRowInsertUpdateDelete1;
179
180 PROCEDURE Check_Parent_Existance AS
181 BEGIN
182
183 IF (((old_references.submission_yr = new_references.submission_yr) AND
184 (old_references.submission_number = new_references.submission_number)) OR
185 ((new_references.submission_yr IS NULL) OR
186 (new_references.submission_number IS NULL))) THEN
187 NULL;
188 ELSE
189 IF NOT IGS_ST_GVT_SPSHT_CTL_PKG.Get_PK_For_Validation (
190 new_references.submission_yr,
191 new_references.submission_number
192 ) THEN
193 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198
199 END Check_Parent_Existance;
200
201 function Get_PK_For_Validation (
202 x_submission_yr IN NUMBER,
203 x_submission_number IN NUMBER,
204 x_person_id IN NUMBER,
205 x_course_cd IN VARCHAR2
206 )return BOOLEAN AS
207
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM IGS_ST_GOVT_STDNT_EN
211 WHERE submission_yr = x_submission_yr
212 AND submission_number = x_submission_number
213 AND person_id = x_person_id
214 AND course_cd = x_course_cd
215 FOR UPDATE NOWAIT;
216
217 lv_rowid cur_rowid%RowType;
218
219 BEGIN
220
221 Open cur_rowid;
222 Fetch cur_rowid INTO lv_rowid;
223 IF (cur_rowid%FOUND) THEN
224 Close cur_rowid;
225 Return(TRUE);
226 ELSE
227 Close cur_rowid;
228 Return(FALSE);
229 END IF;
230
231 END Get_PK_For_Validation;
232
233 PROCEDURE get_fk_igs_st_gvt_spsht_ctl (
234 x_submission_yr IN NUMBER,
235 x_submission_number IN NUMBER
236 ) AS
237
238 CURSOR cur_rowid IS
239 SELECT rowid
240 FROM IGS_ST_GOVT_STDNT_EN
241 WHERE submission_yr = x_submission_yr
242 AND submission_number = x_submission_number ;
243
244 lv_rowid cur_rowid%RowType;
245
246 BEGIN
247
248 Open cur_rowid;
249 Fetch cur_rowid INTO lv_rowid;
250 IF (cur_rowid%FOUND) THEN
251 Close cur_rowid;
252 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSE_GSC_FK');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 Return;
256 END IF;
257 Close cur_rowid;
258
259 END get_fk_igs_st_gvt_spsht_ctl;
260
261 PROCEDURE Before_DML (
262 p_action IN VARCHAR2,
263 x_rowid IN VARCHAR2 DEFAULT NULL,
264 x_submission_yr IN NUMBER DEFAULT NULL,
265 x_submission_number IN NUMBER DEFAULT NULL,
266 x_person_id IN NUMBER DEFAULT NULL,
267 x_course_cd IN VARCHAR2 DEFAULT NULL,
268 x_version_number IN NUMBER DEFAULT NULL,
269 x_birth_dt IN DATE DEFAULT NULL,
270 x_sex IN VARCHAR2 DEFAULT NULL,
271 x_aborig_torres_cd IN VARCHAR2 DEFAULT NULL,
272 x_govt_aborig_torres_cd IN NUMBER DEFAULT NULL,
273 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
274 x_govt_citizenship_cd IN NUMBER DEFAULT NULL,
275 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
276 x_govt_perm_resident_cd IN NUMBER DEFAULT NULL,
277 x_home_location IN VARCHAR2 DEFAULT NULL,
278 x_govt_home_location IN VARCHAR2 DEFAULT NULL,
279 x_term_location IN VARCHAR2 DEFAULT NULL,
280 x_govt_term_location IN VARCHAR2 DEFAULT NULL,
281 x_birth_country_cd IN VARCHAR2 DEFAULT NULL,
282 x_govt_birth_country_cd IN VARCHAR2 DEFAULT NULL,
283 x_yr_arrival IN VARCHAR2 DEFAULT NULL,
284 x_home_language_cd IN VARCHAR2 DEFAULT NULL,
285 x_govt_home_language_cd IN NUMBER DEFAULT NULL,
286 x_prior_ug_inst IN VARCHAR2 DEFAULT NULL,
287 x_govt_prior_ug_inst IN VARCHAR2 DEFAULT NULL,
288 x_prior_other_qual IN VARCHAR2 DEFAULT NULL,
289 x_prior_post_grad IN VARCHAR2 DEFAULT NULL,
290 x_prior_degree IN VARCHAR2 DEFAULT NULL,
291 x_prior_subdeg_notafe IN VARCHAR2 DEFAULT NULL,
292 x_prior_subdeg_tafe IN VARCHAR2 DEFAULT NULL,
293 x_prior_seced_tafe IN VARCHAR2 DEFAULT NULL,
294 x_prior_seced_school IN VARCHAR2 DEFAULT NULL,
295 x_prior_tafe_award IN VARCHAR2 DEFAULT NULL,
296 x_prior_studies_exemption IN NUMBER DEFAULT NULL,
297 x_exemption_institution_cd IN VARCHAR2 DEFAULT NULL,
298 x_govt_exempt_institu_cd IN VARCHAR2 DEFAULT NULL,
299 x_attendance_mode IN VARCHAR2 DEFAULT NULL,
300 x_govt_attendance_mode IN VARCHAR2 DEFAULT NULL,
301 x_attendance_type IN VARCHAR2 DEFAULT NULL,
302 x_govt_attendance_type IN VARCHAR2 DEFAULT NULL,
303 x_commencement_dt IN DATE DEFAULT NULL,
304 x_major_course IN NUMBER DEFAULT NULL,
305 x_tertiary_entrance_score IN NUMBER DEFAULT NULL,
306 x_basis_for_admission_type IN VARCHAR2 DEFAULT NULL,
307 x_govt_basis_for_adm_type IN VARCHAR2 DEFAULT NULL,
308 x_govt_disability IN VARCHAR2 DEFAULT NULL,
309 x_creation_date IN DATE DEFAULT NULL,
310 x_created_by IN NUMBER DEFAULT NULL,
311 x_last_update_date IN DATE DEFAULT NULL,
312 x_last_updated_by IN NUMBER DEFAULT NULL,
313 x_last_update_login IN NUMBER DEFAULT NULL
314 ) AS
315 BEGIN
316
317 Set_Column_Values (
318 p_action,
319 x_rowid,
320 x_submission_yr,
321 x_submission_number,
322 x_person_id,
323 x_course_cd,
324 x_version_number,
325 x_birth_dt,
326 x_sex,
327 x_aborig_torres_cd,
328 x_govt_aborig_torres_cd,
329 x_citizenship_cd,
330 x_govt_citizenship_cd,
331 x_perm_resident_cd,
332 x_govt_perm_resident_cd,
333 x_home_location,
334 x_govt_home_location,
335 x_term_location,
336 x_govt_term_location,
337 x_birth_country_cd,
338 x_govt_birth_country_cd,
339 x_yr_arrival,
340 x_home_language_cd,
341 x_govt_home_language_cd,
342 x_prior_ug_inst,
343 x_govt_prior_ug_inst,
344 x_prior_other_qual,
345 x_prior_post_grad,
346 x_prior_degree,
347 x_prior_subdeg_notafe,
348 x_prior_subdeg_tafe,
349 x_prior_seced_tafe,
350 x_prior_seced_school,
351 x_prior_tafe_award,
352 x_prior_studies_exemption,
353 x_exemption_institution_cd,
354 x_govt_exempt_institu_cd,
355 x_attendance_mode,
356 x_govt_attendance_mode,
357 x_attendance_type,
358 x_govt_attendance_type,
359 x_commencement_dt,
360 x_major_course,
361 x_tertiary_entrance_score,
362 x_basis_for_admission_type,
363 x_govt_basis_for_adm_type,
367 x_last_update_date,
364 x_govt_disability,
365 x_creation_date,
366 x_created_by,
368 x_last_updated_by,
369 x_last_update_login
370 );
371
372 IF (p_action = 'INSERT') THEN
373 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
374 IF Get_PK_For_Validation (
375 new_references.submission_yr,
376 new_references.submission_number,
377 new_references.person_id,
378 new_references.course_cd
379 ) THEN
380 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
381 IGS_GE_MSG_STACK.ADD;
382 App_Exception.Raise_Exception;
383 END IF;
384 Null;
385 Check_Parent_Existance;
386 ELSIF (p_action = 'UPDATE') THEN
387 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
388 Null;
389 Check_Parent_Existance;
390 ELSIF (p_action = 'DELETE') THEN
391 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
392 ELSIF (p_action = 'VALIDATE_INSERT') THEN
393 IF Get_PK_For_Validation (
394 new_references.submission_yr,
395 new_references.submission_number,
396 new_references.person_id,
397 new_references.course_cd
398 ) THEN
399 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
400 IGS_GE_MSG_STACK.ADD;
401 App_Exception.Raise_Exception;
402 END IF;
403 Null;
404 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
405 Null;
406 ELSIF (p_action = 'VALIDATE_DELETE') THEN
407 Null;
408 END IF;
409
410 END Before_DML;
411
412 PROCEDURE After_DML (
413 p_action IN VARCHAR2,
414 x_rowid IN VARCHAR2
415 ) AS
416 BEGIN
417 l_rowid := x_rowid;
418 END After_DML;
419
420 procedure INSERT_ROW (
421 X_ROWID in out NOCOPY VARCHAR2,
422 X_SUBMISSION_YR in NUMBER,
423 X_SUBMISSION_NUMBER in NUMBER,
424 X_PERSON_ID in NUMBER,
425 X_COURSE_CD in VARCHAR2,
426 X_VERSION_NUMBER in NUMBER,
427 X_BIRTH_DT in DATE,
428 X_SEX in VARCHAR2,
429 X_ABORIG_TORRES_CD in VARCHAR2,
430 X_GOVT_ABORIG_TORRES_CD in NUMBER,
431 X_CITIZENSHIP_CD in VARCHAR2,
432 X_GOVT_CITIZENSHIP_CD in NUMBER,
433 X_PERM_RESIDENT_CD in VARCHAR2,
434 X_GOVT_PERM_RESIDENT_CD in NUMBER,
435 X_HOME_LOCATION in VARCHAR2,
436 X_GOVT_HOME_LOCATION in VARCHAR2,
437 X_TERM_LOCATION in VARCHAR2,
438 X_GOVT_TERM_LOCATION in VARCHAR2,
439 X_BIRTH_COUNTRY_CD in VARCHAR2,
440 X_GOVT_BIRTH_COUNTRY_CD in VARCHAR2,
441 X_YR_ARRIVAL in VARCHAR2,
442 X_HOME_LANGUAGE_CD in VARCHAR2,
443 X_GOVT_HOME_LANGUAGE_CD in NUMBER,
444 X_PRIOR_UG_INST in VARCHAR2,
445 X_GOVT_PRIOR_UG_INST in VARCHAR2,
446 X_PRIOR_OTHER_QUAL in VARCHAR2,
447 X_PRIOR_POST_GRAD in VARCHAR2,
448 X_PRIOR_DEGREE in VARCHAR2,
449 X_PRIOR_SUBDEG_NOTAFE in VARCHAR2,
450 X_PRIOR_SUBDEG_TAFE in VARCHAR2,
451 X_PRIOR_SECED_TAFE in VARCHAR2,
452 X_PRIOR_SECED_SCHOOL in VARCHAR2,
453 X_PRIOR_TAFE_AWARD in VARCHAR2,
454 X_PRIOR_STUDIES_EXEMPTION in NUMBER,
455 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
456 X_GOVT_EXEMPT_INSTITU_CD in VARCHAR2,
457 X_ATTENDANCE_MODE in VARCHAR2,
458 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
459 X_ATTENDANCE_TYPE in VARCHAR2,
460 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
461 X_COMMENCEMENT_DT in DATE,
462 X_MAJOR_COURSE in NUMBER,
463 X_TERTIARY_ENTRANCE_SCORE in NUMBER,
464 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
465 X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
466 X_GOVT_DISABILITY in VARCHAR2,
467 X_MODE in VARCHAR2 default 'R'
468 ) AS
469 cursor C is select ROWID from IGS_ST_GOVT_STDNT_EN
470 where SUBMISSION_YR = X_SUBMISSION_YR
471 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
472 and PERSON_ID = X_PERSON_ID
473 and COURSE_CD = X_COURSE_CD;
474 X_LAST_UPDATE_DATE DATE;
475 X_LAST_UPDATED_BY NUMBER;
476 X_LAST_UPDATE_LOGIN NUMBER;
477 X_REQUEST_ID NUMBER;
478 X_PROGRAM_ID NUMBER;
479 X_PROGRAM_APPLICATION_ID NUMBER;
480 X_PROGRAM_UPDATE_DATE DATE;
481
482 begin
483 X_LAST_UPDATE_DATE := SYSDATE;
484 if(X_MODE = 'I') then
485 X_LAST_UPDATED_BY := 1;
486 X_LAST_UPDATE_LOGIN := 0;
487 elsif (X_MODE = 'R') then
488 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
489 if X_LAST_UPDATED_BY is NULL then
490 X_LAST_UPDATED_BY := -1;
491 end if;
492 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
493 if X_LAST_UPDATE_LOGIN is NULL then
494 X_LAST_UPDATE_LOGIN := -1;
495 end if;
496 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
497 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
498 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
499 if (X_REQUEST_ID = -1) then
500 X_REQUEST_ID := NULL;
501 X_PROGRAM_ID := NULL;
502 X_PROGRAM_APPLICATION_ID := NULL;
503 X_PROGRAM_UPDATE_DATE := NULL;
504 else
505 X_PROGRAM_UPDATE_DATE := SYSDATE;
506 end if;
507 else
508 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
509 IGS_GE_MSG_STACK.ADD;
510 app_exception.raise_exception;
511 end if;
512 Before_DML (
516 x_submission_number => X_SUBMISSION_NUMBER,
513 p_action => 'INSERT',
514 x_rowid => X_ROWID,
515 x_submission_yr => X_SUBMISSION_YR,
517 x_person_id => X_PERSON_ID,
518 x_course_cd => X_COURSE_CD,
519 x_version_number => X_VERSION_NUMBER,
520 x_birth_dt => X_BIRTH_DT,
521 x_sex => X_SEX,
522 x_aborig_torres_cd => X_ABORIG_TORRES_CD,
523 x_govt_aborig_torres_cd => X_GOVT_ABORIG_TORRES_CD,
524 x_citizenship_cd => X_CITIZENSHIP_CD,
525 x_govt_citizenship_cd => X_GOVT_CITIZENSHIP_CD,
526 x_perm_resident_cd => X_PERM_RESIDENT_CD,
527 x_govt_perm_resident_cd => X_GOVT_PERM_RESIDENT_CD,
528 x_home_location => X_HOME_LOCATION,
529 x_govt_home_location => X_GOVT_HOME_LOCATION,
530 x_term_location => X_TERM_LOCATION,
531 x_govt_term_location => X_GOVT_TERM_LOCATION,
532 x_birth_country_cd => X_BIRTH_COUNTRY_CD,
533 x_govt_birth_country_cd => X_GOVT_BIRTH_COUNTRY_CD,
534 x_yr_arrival => X_YR_ARRIVAL,
535 x_home_language_cd => X_HOME_LANGUAGE_CD,
536 x_govt_home_language_cd => X_GOVT_HOME_LANGUAGE_CD,
537 x_prior_ug_inst => X_PRIOR_UG_INST,
538 x_govt_prior_ug_inst => X_GOVT_PRIOR_UG_INST,
539 x_prior_other_qual => X_PRIOR_OTHER_QUAL,
540 x_prior_post_grad => X_PRIOR_POST_GRAD,
541 x_prior_degree => X_PRIOR_DEGREE,
542 x_prior_subdeg_notafe => X_PRIOR_SUBDEG_NOTAFE,
543 x_prior_subdeg_tafe => X_PRIOR_SUBDEG_TAFE,
544 x_prior_seced_tafe => X_PRIOR_SECED_TAFE,
545 x_prior_seced_school => X_PRIOR_SECED_SCHOOL,
546 x_prior_tafe_award => X_PRIOR_TAFE_AWARD,
547 x_prior_studies_exemption => X_PRIOR_STUDIES_EXEMPTION,
548 x_exemption_institution_cd => X_EXEMPTION_INSTITUTION_CD,
549 x_govt_exempt_institu_cd => X_GOVT_EXEMPT_INSTITU_CD,
550 x_attendance_mode => X_ATTENDANCE_MODE,
551 x_govt_attendance_mode => X_GOVT_ATTENDANCE_MODE,
552 x_attendance_type => X_ATTENDANCE_TYPE,
553 x_govt_attendance_type => X_GOVT_ATTENDANCE_TYPE,
554 x_commencement_dt => X_COMMENCEMENT_DT,
555 x_major_course => X_MAJOR_COURSE,
556 x_tertiary_entrance_score => X_TERTIARY_ENTRANCE_SCORE,
557 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
558 x_govt_basis_for_adm_type => X_GOVT_BASIS_FOR_ADM_TYPE,
559 x_govt_disability => X_GOVT_DISABILITY,
560 x_creation_date => X_LAST_UPDATE_DATE,
561 x_created_by => X_LAST_UPDATED_BY,
562 x_last_update_date => X_LAST_UPDATE_DATE,
563 x_last_updated_by => X_LAST_UPDATED_BY,
564 x_last_update_login => X_LAST_UPDATE_LOGIN
565 );
566
567 insert into IGS_ST_GOVT_STDNT_EN (
568 SUBMISSION_YR,
569 SUBMISSION_NUMBER,
570 PERSON_ID,
571 COURSE_CD,
572 VERSION_NUMBER,
573 BIRTH_DT,
574 SEX,
575 ABORIG_TORRES_CD,
576 GOVT_ABORIG_TORRES_CD,
577 CITIZENSHIP_CD,
578 GOVT_CITIZENSHIP_CD,
579 PERM_RESIDENT_CD,
580 GOVT_PERM_RESIDENT_CD,
581 HOME_LOCATION,
582 GOVT_HOME_LOCATION,
583 TERM_LOCATION,
584 GOVT_TERM_LOCATION,
585 BIRTH_COUNTRY_CD,
586 GOVT_BIRTH_COUNTRY_CD,
587 YR_ARRIVAL,
588 HOME_LANGUAGE_CD,
589 GOVT_HOME_LANGUAGE_CD,
590 PRIOR_UG_INST,
591 GOVT_PRIOR_UG_INST,
592 PRIOR_OTHER_QUAL,
593 PRIOR_POST_GRAD,
594 PRIOR_DEGREE,
595 PRIOR_SUBDEG_NOTAFE,
596 PRIOR_SUBDEG_TAFE,
597 PRIOR_SECED_TAFE,
598 PRIOR_SECED_SCHOOL,
599 PRIOR_TAFE_AWARD,
600 PRIOR_STUDIES_EXEMPTION,
601 EXEMPTION_INSTITUTION_CD,
602 GOVT_EXEMPTION_INSTITUTION_CD,
603 ATTENDANCE_MODE,
604 GOVT_ATTENDANCE_MODE,
605 ATTENDANCE_TYPE,
606 GOVT_ATTENDANCE_TYPE,
607 COMMENCEMENT_DT,
608 MAJOR_COURSE,
609 TERTIARY_ENTRANCE_SCORE,
610 BASIS_FOR_ADMISSION_TYPE,
611 GOVT_BASIS_FOR_ADMISSION_TYPE,
612 GOVT_DISABILITY,
613 CREATION_DATE,
614 CREATED_BY,
615 LAST_UPDATE_DATE,
616 LAST_UPDATED_BY,
617 LAST_UPDATE_LOGIN,
618 REQUEST_ID,
619 PROGRAM_ID,
620 PROGRAM_APPLICATION_ID,
621 PROGRAM_UPDATE_DATE
622 ) values (
623 NEW_REFERENCES.SUBMISSION_YR,
624 NEW_REFERENCES.SUBMISSION_NUMBER,
625 NEW_REFERENCES.PERSON_ID,
626 NEW_REFERENCES.COURSE_CD,
627 NEW_REFERENCES.VERSION_NUMBER,
628 NEW_REFERENCES.BIRTH_DT,
629 NEW_REFERENCES.SEX,
630 NEW_REFERENCES.ABORIG_TORRES_CD,
631 NEW_REFERENCES.GOVT_ABORIG_TORRES_CD,
632 NEW_REFERENCES.CITIZENSHIP_CD,
633 NEW_REFERENCES.GOVT_CITIZENSHIP_CD,
634 NEW_REFERENCES.PERM_RESIDENT_CD,
635 NEW_REFERENCES.GOVT_PERM_RESIDENT_CD,
636 NEW_REFERENCES.HOME_LOCATION,
637 NEW_REFERENCES.GOVT_HOME_LOCATION,
638 NEW_REFERENCES.TERM_LOCATION,
639 NEW_REFERENCES.GOVT_TERM_LOCATION,
640 NEW_REFERENCES.BIRTH_COUNTRY_CD,
641 NEW_REFERENCES.GOVT_BIRTH_COUNTRY_CD,
642 NEW_REFERENCES.YR_ARRIVAL,
643 NEW_REFERENCES.HOME_LANGUAGE_CD,
644 NEW_REFERENCES.GOVT_HOME_LANGUAGE_CD,
645 NEW_REFERENCES.PRIOR_UG_INST,
646 NEW_REFERENCES.GOVT_PRIOR_UG_INST,
647 NEW_REFERENCES.PRIOR_OTHER_QUAL,
648 NEW_REFERENCES.PRIOR_POST_GRAD,
652 NEW_REFERENCES.PRIOR_SECED_TAFE,
649 NEW_REFERENCES.PRIOR_DEGREE,
650 NEW_REFERENCES.PRIOR_SUBDEG_NOTAFE,
651 NEW_REFERENCES.PRIOR_SUBDEG_TAFE,
653 NEW_REFERENCES.PRIOR_SECED_SCHOOL,
654 NEW_REFERENCES.PRIOR_TAFE_AWARD,
655 NEW_REFERENCES.PRIOR_STUDIES_EXEMPTION,
656 NEW_REFERENCES.EXEMPTION_INSTITUTION_CD,
657 NEW_REFERENCES.GOVT_EXEMPTION_INSTITUTION_CD,
658 NEW_REFERENCES.ATTENDANCE_MODE,
659 NEW_REFERENCES.GOVT_ATTENDANCE_MODE,
660 NEW_REFERENCES.ATTENDANCE_TYPE,
661 NEW_REFERENCES.GOVT_ATTENDANCE_TYPE,
662 NEW_REFERENCES.COMMENCEMENT_DT,
663 NEW_REFERENCES.MAJOR_COURSE,
664 NEW_REFERENCES.TERTIARY_ENTRANCE_SCORE,
665 NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
666 NEW_REFERENCES.GOVT_BASIS_FOR_ADMISSION_TYPE,
667 NEW_REFERENCES.GOVT_DISABILITY,
668 X_LAST_UPDATE_DATE,
669 X_LAST_UPDATED_BY,
670 X_LAST_UPDATE_DATE,
671 X_LAST_UPDATED_BY,
672 X_LAST_UPDATE_LOGIN,
673 X_REQUEST_ID,
674 X_PROGRAM_ID,
675 X_PROGRAM_APPLICATION_ID,
676 X_PROGRAM_UPDATE_DATE
677 );
678
679 open c;
680 fetch c into X_ROWID;
681 if (c%notfound) then
682 close c;
683 raise no_data_found;
684 end if;
685 close c;
686 After_DML (
687 p_action => 'INSERT',
688 x_rowid => X_ROWID
689 );
690
691 end INSERT_ROW;
692
693 procedure LOCK_ROW (
694 X_ROWID in VARCHAR2,
695 X_SUBMISSION_YR in NUMBER,
696 X_SUBMISSION_NUMBER in NUMBER,
697 X_PERSON_ID in NUMBER,
698 X_COURSE_CD in VARCHAR2,
699 X_VERSION_NUMBER in NUMBER,
700 X_BIRTH_DT in DATE,
701 X_SEX in VARCHAR2,
702 X_ABORIG_TORRES_CD in VARCHAR2,
703 X_GOVT_ABORIG_TORRES_CD in NUMBER,
704 X_CITIZENSHIP_CD in VARCHAR2,
705 X_GOVT_CITIZENSHIP_CD in NUMBER,
706 X_PERM_RESIDENT_CD in VARCHAR2,
707 X_GOVT_PERM_RESIDENT_CD in NUMBER,
708 X_HOME_LOCATION in VARCHAR2,
709 X_GOVT_HOME_LOCATION in VARCHAR2,
710 X_TERM_LOCATION in VARCHAR2,
711 X_GOVT_TERM_LOCATION in VARCHAR2,
712 X_BIRTH_COUNTRY_CD in VARCHAR2,
713 X_GOVT_BIRTH_COUNTRY_CD in VARCHAR2,
714 X_YR_ARRIVAL in VARCHAR2,
715 X_HOME_LANGUAGE_CD in VARCHAR2,
716 X_GOVT_HOME_LANGUAGE_CD in NUMBER,
717 X_PRIOR_UG_INST in VARCHAR2,
718 X_GOVT_PRIOR_UG_INST in VARCHAR2,
719 X_PRIOR_OTHER_QUAL in VARCHAR2,
720 X_PRIOR_POST_GRAD in VARCHAR2,
721 X_PRIOR_DEGREE in VARCHAR2,
722 X_PRIOR_SUBDEG_NOTAFE in VARCHAR2,
723 X_PRIOR_SUBDEG_TAFE in VARCHAR2,
724 X_PRIOR_SECED_TAFE in VARCHAR2,
725 X_PRIOR_SECED_SCHOOL in VARCHAR2,
726 X_PRIOR_TAFE_AWARD in VARCHAR2,
727 X_PRIOR_STUDIES_EXEMPTION in NUMBER,
728 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
729 X_GOVT_EXEMPT_INSTITU_CD in VARCHAR2,
730 X_ATTENDANCE_MODE in VARCHAR2,
731 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
732 X_ATTENDANCE_TYPE in VARCHAR2,
733 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
734 X_COMMENCEMENT_DT in DATE,
735 X_MAJOR_COURSE in NUMBER,
736 X_TERTIARY_ENTRANCE_SCORE in NUMBER,
737 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
738 X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
739 X_GOVT_DISABILITY in VARCHAR2
740 ) AS
741 cursor c1 is select
742 VERSION_NUMBER,
743 BIRTH_DT,
744 SEX,
745 ABORIG_TORRES_CD,
746 GOVT_ABORIG_TORRES_CD,
747 CITIZENSHIP_CD,
748 GOVT_CITIZENSHIP_CD,
749 PERM_RESIDENT_CD,
750 GOVT_PERM_RESIDENT_CD,
751 HOME_LOCATION,
752 GOVT_HOME_LOCATION,
753 TERM_LOCATION,
754 GOVT_TERM_LOCATION,
755 BIRTH_COUNTRY_CD,
756 GOVT_BIRTH_COUNTRY_CD,
757 YR_ARRIVAL,
758 HOME_LANGUAGE_CD,
759 GOVT_HOME_LANGUAGE_CD,
760 PRIOR_UG_INST,
761 GOVT_PRIOR_UG_INST,
762 PRIOR_OTHER_QUAL,
763 PRIOR_POST_GRAD,
764 PRIOR_DEGREE,
765 PRIOR_SUBDEG_NOTAFE,
766 PRIOR_SUBDEG_TAFE,
767 PRIOR_SECED_TAFE,
768 PRIOR_SECED_SCHOOL,
769 PRIOR_TAFE_AWARD,
770 PRIOR_STUDIES_EXEMPTION,
771 EXEMPTION_INSTITUTION_CD,
772 GOVT_EXEMPTION_INSTITUTION_CD,
773 ATTENDANCE_MODE,
774 GOVT_ATTENDANCE_MODE,
775 ATTENDANCE_TYPE,
776 GOVT_ATTENDANCE_TYPE,
777 COMMENCEMENT_DT,
778 MAJOR_COURSE,
779 TERTIARY_ENTRANCE_SCORE,
780 BASIS_FOR_ADMISSION_TYPE,
781 GOVT_BASIS_FOR_ADMISSION_TYPE,
782 GOVT_DISABILITY
783 from IGS_ST_GOVT_STDNT_EN
784 where ROWID = X_ROWID
785 for update nowait;
786 tlinfo c1%rowtype;
787
788 begin
789 open c1;
790 fetch c1 into tlinfo;
791 if (c1%notfound) then
792 close c1;
793 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
794 IGS_GE_MSG_STACK.ADD;
795 app_exception.raise_exception;
796 return;
797 end if;
798 close c1;
799
800 if ( (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
801 AND ((tlinfo.BIRTH_DT = X_BIRTH_DT)
802 OR ((tlinfo.BIRTH_DT is null)
803 AND (X_BIRTH_DT is null)))
807 AND (tlinfo.CITIZENSHIP_CD = X_CITIZENSHIP_CD)
804 AND (tlinfo.SEX = X_SEX)
805 AND (tlinfo.ABORIG_TORRES_CD = X_ABORIG_TORRES_CD)
806 AND (tlinfo.GOVT_ABORIG_TORRES_CD = X_GOVT_ABORIG_TORRES_CD)
808 AND (tlinfo.GOVT_CITIZENSHIP_CD = X_GOVT_CITIZENSHIP_CD)
809 AND (tlinfo.PERM_RESIDENT_CD = X_PERM_RESIDENT_CD)
810 AND (tlinfo.GOVT_PERM_RESIDENT_CD = X_GOVT_PERM_RESIDENT_CD)
811 AND (tlinfo.HOME_LOCATION = X_HOME_LOCATION)
812 AND (tlinfo.GOVT_HOME_LOCATION = X_GOVT_HOME_LOCATION)
813 AND (tlinfo.TERM_LOCATION = X_TERM_LOCATION)
814 AND (tlinfo.GOVT_TERM_LOCATION = X_GOVT_TERM_LOCATION)
815 AND (tlinfo.BIRTH_COUNTRY_CD = X_BIRTH_COUNTRY_CD)
816 AND (tlinfo.GOVT_BIRTH_COUNTRY_CD = X_GOVT_BIRTH_COUNTRY_CD)
817 AND (tlinfo.YR_ARRIVAL = X_YR_ARRIVAL)
818 AND (tlinfo.HOME_LANGUAGE_CD = X_HOME_LANGUAGE_CD)
819 AND (tlinfo.GOVT_HOME_LANGUAGE_CD = X_GOVT_HOME_LANGUAGE_CD)
820 AND (tlinfo.PRIOR_UG_INST = X_PRIOR_UG_INST)
821 AND (tlinfo.GOVT_PRIOR_UG_INST = X_GOVT_PRIOR_UG_INST)
822 AND (tlinfo.PRIOR_OTHER_QUAL = X_PRIOR_OTHER_QUAL)
823 AND (tlinfo.PRIOR_POST_GRAD = X_PRIOR_POST_GRAD)
824 AND (tlinfo.PRIOR_DEGREE = X_PRIOR_DEGREE)
825 AND (tlinfo.PRIOR_SUBDEG_NOTAFE = X_PRIOR_SUBDEG_NOTAFE)
826 AND (tlinfo.PRIOR_SUBDEG_TAFE = X_PRIOR_SUBDEG_TAFE)
827 AND (tlinfo.PRIOR_SECED_TAFE = X_PRIOR_SECED_TAFE)
828 AND (tlinfo.PRIOR_SECED_SCHOOL = X_PRIOR_SECED_SCHOOL)
829 AND (tlinfo.PRIOR_TAFE_AWARD = X_PRIOR_TAFE_AWARD)
830 AND (tlinfo.PRIOR_STUDIES_EXEMPTION = X_PRIOR_STUDIES_EXEMPTION)
831 AND (tlinfo.EXEMPTION_INSTITUTION_CD = X_EXEMPTION_INSTITUTION_CD)
832 AND (tlinfo.GOVT_EXEMPTION_INSTITUTION_CD = X_GOVT_EXEMPT_INSTITU_CD)
833 AND (tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
834 AND (tlinfo.GOVT_ATTENDANCE_MODE = X_GOVT_ATTENDANCE_MODE)
835 AND (tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
836 AND (tlinfo.GOVT_ATTENDANCE_TYPE = X_GOVT_ATTENDANCE_TYPE)
837 AND (tlinfo.COMMENCEMENT_DT = X_COMMENCEMENT_DT)
838 AND (tlinfo.MAJOR_COURSE = X_MAJOR_COURSE)
839 AND (tlinfo.TERTIARY_ENTRANCE_SCORE = X_TERTIARY_ENTRANCE_SCORE)
840 AND (tlinfo.BASIS_FOR_ADMISSION_TYPE = X_BASIS_FOR_ADMISSION_TYPE)
841 AND (tlinfo.GOVT_BASIS_FOR_ADMISSION_TYPE = X_GOVT_BASIS_FOR_ADM_TYPE)
842 AND (tlinfo.GOVT_DISABILITY = X_GOVT_DISABILITY)
843 ) then
844 null;
845 else
846 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
847 IGS_GE_MSG_STACK.ADD;
848 app_exception.raise_exception;
849 end if;
850 return;
851 end LOCK_ROW;
852
853 procedure UPDATE_ROW (
854 X_ROWID in VARCHAR2,
855 X_SUBMISSION_YR in NUMBER,
856 X_SUBMISSION_NUMBER in NUMBER,
857 X_PERSON_ID in NUMBER,
858 X_COURSE_CD in VARCHAR2,
859 X_VERSION_NUMBER in NUMBER,
860 X_BIRTH_DT in DATE,
861 X_SEX in VARCHAR2,
862 X_ABORIG_TORRES_CD in VARCHAR2,
863 X_GOVT_ABORIG_TORRES_CD in NUMBER,
864 X_CITIZENSHIP_CD in VARCHAR2,
865 X_GOVT_CITIZENSHIP_CD in NUMBER,
866 X_PERM_RESIDENT_CD in VARCHAR2,
867 X_GOVT_PERM_RESIDENT_CD in NUMBER,
868 X_HOME_LOCATION in VARCHAR2,
869 X_GOVT_HOME_LOCATION in VARCHAR2,
870 X_TERM_LOCATION in VARCHAR2,
871 X_GOVT_TERM_LOCATION in VARCHAR2,
872 X_BIRTH_COUNTRY_CD in VARCHAR2,
873 X_GOVT_BIRTH_COUNTRY_CD in VARCHAR2,
874 X_YR_ARRIVAL in VARCHAR2,
875 X_HOME_LANGUAGE_CD in VARCHAR2,
876 X_GOVT_HOME_LANGUAGE_CD in NUMBER,
877 X_PRIOR_UG_INST in VARCHAR2,
878 X_GOVT_PRIOR_UG_INST in VARCHAR2,
879 X_PRIOR_OTHER_QUAL in VARCHAR2,
880 X_PRIOR_POST_GRAD in VARCHAR2,
881 X_PRIOR_DEGREE in VARCHAR2,
882 X_PRIOR_SUBDEG_NOTAFE in VARCHAR2,
883 X_PRIOR_SUBDEG_TAFE in VARCHAR2,
884 X_PRIOR_SECED_TAFE in VARCHAR2,
885 X_PRIOR_SECED_SCHOOL in VARCHAR2,
886 X_PRIOR_TAFE_AWARD in VARCHAR2,
887 X_PRIOR_STUDIES_EXEMPTION in NUMBER,
888 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
889 X_GOVT_EXEMPT_INSTITU_CD in VARCHAR2,
890 X_ATTENDANCE_MODE in VARCHAR2,
891 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
892 X_ATTENDANCE_TYPE in VARCHAR2,
893 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
894 X_COMMENCEMENT_DT in DATE,
895 X_MAJOR_COURSE in NUMBER,
896 X_TERTIARY_ENTRANCE_SCORE in NUMBER,
897 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
898 X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
899 X_GOVT_DISABILITY in VARCHAR2,
900 X_MODE in VARCHAR2 default 'R'
901 ) AS
902 X_LAST_UPDATE_DATE DATE;
903 X_LAST_UPDATED_BY NUMBER;
904 X_LAST_UPDATE_LOGIN NUMBER;
905 X_REQUEST_ID NUMBER;
906 X_PROGRAM_ID NUMBER;
907 X_PROGRAM_APPLICATION_ID NUMBER;
908 X_PROGRAM_UPDATE_DATE DATE;
909
910 begin
911 X_LAST_UPDATE_DATE := SYSDATE;
912 if(X_MODE = 'I') then
913 X_LAST_UPDATED_BY := 1;
914 X_LAST_UPDATE_LOGIN := 0;
915 elsif (X_MODE = 'R') then
916 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
917 if X_LAST_UPDATED_BY is NULL then
918 X_LAST_UPDATED_BY := -1;
919 end if;
920 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
921 if X_LAST_UPDATE_LOGIN is NULL then
922 X_LAST_UPDATE_LOGIN := -1;
923 end if;
924 else
925 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
926 IGS_GE_MSG_STACK.ADD;
930 Before_DML (
927 app_exception.raise_exception;
928 end if;
929
931 p_action => 'UPDATE',
932 x_rowid => X_ROWID,
933 x_submission_yr => X_SUBMISSION_YR,
934 x_submission_number => X_SUBMISSION_NUMBER,
935 x_person_id => X_PERSON_ID,
936 x_course_cd => X_COURSE_CD,
937 x_version_number => X_VERSION_NUMBER,
938 x_birth_dt => X_BIRTH_DT,
939 x_sex => X_SEX,
940 x_aborig_torres_cd => X_ABORIG_TORRES_CD,
941 x_govt_aborig_torres_cd => X_GOVT_ABORIG_TORRES_CD,
942 x_citizenship_cd => X_CITIZENSHIP_CD,
943 x_govt_citizenship_cd => X_GOVT_CITIZENSHIP_CD,
944 x_perm_resident_cd => X_PERM_RESIDENT_CD,
945 x_govt_perm_resident_cd => X_GOVT_PERM_RESIDENT_CD,
946 x_home_location => X_HOME_LOCATION,
947 x_govt_home_location => X_GOVT_HOME_LOCATION,
948 x_term_location => X_TERM_LOCATION,
949 x_govt_term_location => X_GOVT_TERM_LOCATION,
950 x_birth_country_cd => X_BIRTH_COUNTRY_CD,
951 x_govt_birth_country_cd => X_GOVT_BIRTH_COUNTRY_CD,
952 x_yr_arrival => X_YR_ARRIVAL,
953 x_home_language_cd => X_HOME_LANGUAGE_CD,
954 x_govt_home_language_cd => X_GOVT_HOME_LANGUAGE_CD,
955 x_prior_ug_inst => X_PRIOR_UG_INST,
956 x_govt_prior_ug_inst => X_GOVT_PRIOR_UG_INST,
957 x_prior_other_qual => X_PRIOR_OTHER_QUAL,
958 x_prior_post_grad => X_PRIOR_POST_GRAD,
959 x_prior_degree => X_PRIOR_DEGREE,
960 x_prior_subdeg_notafe => X_PRIOR_SUBDEG_NOTAFE,
961 x_prior_subdeg_tafe => X_PRIOR_SUBDEG_TAFE,
962 x_prior_seced_tafe => X_PRIOR_SECED_TAFE,
963 x_prior_seced_school => X_PRIOR_SECED_SCHOOL,
964 x_prior_tafe_award => X_PRIOR_TAFE_AWARD,
965 x_prior_studies_exemption => X_PRIOR_STUDIES_EXEMPTION,
966 x_exemption_institution_cd => X_EXEMPTION_INSTITUTION_CD,
967 x_govt_exempt_institu_cd => X_GOVT_EXEMPT_INSTITU_CD,
968 x_attendance_mode => X_ATTENDANCE_MODE,
969 x_govt_attendance_mode => X_GOVT_ATTENDANCE_MODE,
970 x_attendance_type => X_ATTENDANCE_TYPE,
971 x_govt_attendance_type => X_GOVT_ATTENDANCE_TYPE,
972 x_commencement_dt => X_COMMENCEMENT_DT,
973 x_major_course => X_MAJOR_COURSE,
974 x_tertiary_entrance_score => X_TERTIARY_ENTRANCE_SCORE,
975 x_basis_for_admission_type => X_BASIS_FOR_ADMISSION_TYPE,
976 x_govt_basis_for_adm_type => X_GOVT_BASIS_FOR_ADM_TYPE,
977 x_govt_disability => X_GOVT_DISABILITY,
978 x_creation_date => X_LAST_UPDATE_DATE,
979 x_created_by => X_LAST_UPDATED_BY,
980 x_last_update_date => X_LAST_UPDATE_DATE,
981 x_last_updated_by => X_LAST_UPDATED_BY,
982 x_last_update_login => X_LAST_UPDATE_LOGIN
983 );
984
985 if (X_MODE = 'R') then
986 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
987 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
988 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
989 if (X_REQUEST_ID = -1) then
990 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
991 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
992 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
993 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
994 else
995 X_PROGRAM_UPDATE_DATE := SYSDATE;
996 end if;
997 end if;
998
999 update IGS_ST_GOVT_STDNT_EN set
1000 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
1001 BIRTH_DT = NEW_REFERENCES.BIRTH_DT,
1002 SEX = NEW_REFERENCES.SEX,
1003 ABORIG_TORRES_CD = NEW_REFERENCES.ABORIG_TORRES_CD,
1004 GOVT_ABORIG_TORRES_CD = NEW_REFERENCES.GOVT_ABORIG_TORRES_CD,
1005 CITIZENSHIP_CD = NEW_REFERENCES.CITIZENSHIP_CD,
1006 GOVT_CITIZENSHIP_CD = NEW_REFERENCES.GOVT_CITIZENSHIP_CD,
1007 PERM_RESIDENT_CD = NEW_REFERENCES.PERM_RESIDENT_CD,
1008 GOVT_PERM_RESIDENT_CD = NEW_REFERENCES.GOVT_PERM_RESIDENT_CD,
1009 HOME_LOCATION = NEW_REFERENCES.HOME_LOCATION,
1010 GOVT_HOME_LOCATION = NEW_REFERENCES.GOVT_HOME_LOCATION,
1011 TERM_LOCATION = NEW_REFERENCES.TERM_LOCATION,
1012 GOVT_TERM_LOCATION = NEW_REFERENCES.GOVT_TERM_LOCATION,
1013 BIRTH_COUNTRY_CD = NEW_REFERENCES.BIRTH_COUNTRY_CD,
1014 GOVT_BIRTH_COUNTRY_CD = NEW_REFERENCES.GOVT_BIRTH_COUNTRY_CD,
1015 YR_ARRIVAL = NEW_REFERENCES.YR_ARRIVAL,
1016 HOME_LANGUAGE_CD = NEW_REFERENCES.HOME_LANGUAGE_CD,
1017 GOVT_HOME_LANGUAGE_CD = NEW_REFERENCES.GOVT_HOME_LANGUAGE_CD,
1018 PRIOR_UG_INST = NEW_REFERENCES.PRIOR_UG_INST,
1019 GOVT_PRIOR_UG_INST = NEW_REFERENCES.GOVT_PRIOR_UG_INST,
1020 PRIOR_OTHER_QUAL = NEW_REFERENCES.PRIOR_OTHER_QUAL,
1021 PRIOR_POST_GRAD = NEW_REFERENCES.PRIOR_POST_GRAD,
1022 PRIOR_DEGREE = NEW_REFERENCES.PRIOR_DEGREE,
1023 PRIOR_SUBDEG_NOTAFE = NEW_REFERENCES.PRIOR_SUBDEG_NOTAFE,
1024 PRIOR_SUBDEG_TAFE = NEW_REFERENCES.PRIOR_SUBDEG_TAFE,
1025 PRIOR_SECED_TAFE = NEW_REFERENCES.PRIOR_SECED_TAFE,
1026 PRIOR_SECED_SCHOOL = NEW_REFERENCES.PRIOR_SECED_SCHOOL,
1027 PRIOR_TAFE_AWARD = NEW_REFERENCES.PRIOR_TAFE_AWARD,
1028 PRIOR_STUDIES_EXEMPTION = NEW_REFERENCES.PRIOR_STUDIES_EXEMPTION,
1029 EXEMPTION_INSTITUTION_CD = NEW_REFERENCES.EXEMPTION_INSTITUTION_CD,
1030 GOVT_EXEMPTION_INSTITUTION_CD = NEW_REFERENCES.GOVT_EXEMPTION_INSTITUTION_CD,
1031 ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1032 GOVT_ATTENDANCE_MODE = NEW_REFERENCES.GOVT_ATTENDANCE_MODE,
1033 ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1034 GOVT_ATTENDANCE_TYPE = NEW_REFERENCES.GOVT_ATTENDANCE_TYPE,
1035 COMMENCEMENT_DT = NEW_REFERENCES.COMMENCEMENT_DT,
1036 MAJOR_COURSE = NEW_REFERENCES.MAJOR_COURSE,
1037 TERTIARY_ENTRANCE_SCORE = NEW_REFERENCES.TERTIARY_ENTRANCE_SCORE,
1038 BASIS_FOR_ADMISSION_TYPE = NEW_REFERENCES.BASIS_FOR_ADMISSION_TYPE,
1039 GOVT_BASIS_FOR_ADMISSION_TYPE = NEW_REFERENCES.GOVT_BASIS_FOR_ADMISSION_TYPE,
1040 GOVT_DISABILITY = NEW_REFERENCES.GOVT_DISABILITY,
1041 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1042 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1043 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1044 REQUEST_ID = X_REQUEST_ID,
1045 PROGRAM_ID = X_PROGRAM_ID,
1046 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1047 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1048
1049 where ROWID = X_ROWID
1050 ;
1051 if (sql%notfound) then
1052 raise no_data_found;
1053 end if;
1054 After_DML (
1055 p_action => 'UPDATE',
1056 x_rowid => X_ROWID
1057 );
1058 end UPDATE_ROW;
1059
1060 procedure ADD_ROW (
1061 X_ROWID in out NOCOPY VARCHAR2,
1062 X_SUBMISSION_YR in NUMBER,
1063 X_SUBMISSION_NUMBER in NUMBER,
1064 X_PERSON_ID in NUMBER,
1065 X_COURSE_CD in VARCHAR2,
1066 X_VERSION_NUMBER in NUMBER,
1067 X_BIRTH_DT in DATE,
1068 X_SEX in VARCHAR2,
1069 X_ABORIG_TORRES_CD in VARCHAR2,
1070 X_GOVT_ABORIG_TORRES_CD in NUMBER,
1071 X_CITIZENSHIP_CD in VARCHAR2,
1072 X_GOVT_CITIZENSHIP_CD in NUMBER,
1073 X_PERM_RESIDENT_CD in VARCHAR2,
1074 X_GOVT_PERM_RESIDENT_CD in NUMBER,
1075 X_HOME_LOCATION in VARCHAR2,
1076 X_GOVT_HOME_LOCATION in VARCHAR2,
1077 X_TERM_LOCATION in VARCHAR2,
1078 X_GOVT_TERM_LOCATION in VARCHAR2,
1079 X_BIRTH_COUNTRY_CD in VARCHAR2,
1080 X_GOVT_BIRTH_COUNTRY_CD in VARCHAR2,
1081 X_YR_ARRIVAL in VARCHAR2,
1082 X_HOME_LANGUAGE_CD in VARCHAR2,
1083 X_GOVT_HOME_LANGUAGE_CD in NUMBER,
1084 X_PRIOR_UG_INST in VARCHAR2,
1085 X_GOVT_PRIOR_UG_INST in VARCHAR2,
1086 X_PRIOR_OTHER_QUAL in VARCHAR2,
1087 X_PRIOR_POST_GRAD in VARCHAR2,
1088 X_PRIOR_DEGREE in VARCHAR2,
1089 X_PRIOR_SUBDEG_NOTAFE in VARCHAR2,
1090 X_PRIOR_SUBDEG_TAFE in VARCHAR2,
1091 X_PRIOR_SECED_TAFE in VARCHAR2,
1092 X_PRIOR_SECED_SCHOOL in VARCHAR2,
1093 X_PRIOR_TAFE_AWARD in VARCHAR2,
1094 X_PRIOR_STUDIES_EXEMPTION in NUMBER,
1095 X_EXEMPTION_INSTITUTION_CD in VARCHAR2,
1096 X_GOVT_EXEMPT_INSTITU_CD in VARCHAR2,
1097 X_ATTENDANCE_MODE in VARCHAR2,
1098 X_GOVT_ATTENDANCE_MODE in VARCHAR2,
1099 X_ATTENDANCE_TYPE in VARCHAR2,
1100 X_GOVT_ATTENDANCE_TYPE in VARCHAR2,
1101 X_COMMENCEMENT_DT in DATE,
1102 X_MAJOR_COURSE in NUMBER,
1103 X_TERTIARY_ENTRANCE_SCORE in NUMBER,
1104 X_BASIS_FOR_ADMISSION_TYPE in VARCHAR2,
1105 X_GOVT_BASIS_FOR_ADM_TYPE in VARCHAR2,
1106 X_GOVT_DISABILITY in VARCHAR2,
1107 X_MODE in VARCHAR2 default 'R'
1108 ) AS
1109 cursor c1 is select rowid from IGS_ST_GOVT_STDNT_EN
1110 where SUBMISSION_YR = X_SUBMISSION_YR
1111 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
1112 and PERSON_ID = X_PERSON_ID
1113 and COURSE_CD = X_COURSE_CD
1114 ;
1115 begin
1116 open c1;
1117 fetch c1 into X_ROWID;
1118 if (c1%notfound) then
1119 close c1;
1120 INSERT_ROW (
1121 X_ROWID,
1122 X_SUBMISSION_YR,
1123 X_SUBMISSION_NUMBER,
1124 X_PERSON_ID,
1125 X_COURSE_CD,
1126 X_VERSION_NUMBER,
1127 X_BIRTH_DT,
1128 X_SEX,
1129 X_ABORIG_TORRES_CD,
1130 X_GOVT_ABORIG_TORRES_CD,
1131 X_CITIZENSHIP_CD,
1132 X_GOVT_CITIZENSHIP_CD,
1133 X_PERM_RESIDENT_CD,
1134 X_GOVT_PERM_RESIDENT_CD,
1135 X_HOME_LOCATION,
1136 X_GOVT_HOME_LOCATION,
1137 X_TERM_LOCATION,
1138 X_GOVT_TERM_LOCATION,
1139 X_BIRTH_COUNTRY_CD,
1140 X_GOVT_BIRTH_COUNTRY_CD,
1141 X_YR_ARRIVAL,
1142 X_HOME_LANGUAGE_CD,
1143 X_GOVT_HOME_LANGUAGE_CD,
1144 X_PRIOR_UG_INST,
1145 X_GOVT_PRIOR_UG_INST,
1146 X_PRIOR_OTHER_QUAL,
1147 X_PRIOR_POST_GRAD,
1148 X_PRIOR_DEGREE,
1149 X_PRIOR_SUBDEG_NOTAFE,
1150 X_PRIOR_SUBDEG_TAFE,
1151 X_PRIOR_SECED_TAFE,
1152 X_PRIOR_SECED_SCHOOL,
1153 X_PRIOR_TAFE_AWARD,
1154 X_PRIOR_STUDIES_EXEMPTION,
1155 X_EXEMPTION_INSTITUTION_CD,
1156 X_GOVT_EXEMPT_INSTITU_CD,
1157 X_ATTENDANCE_MODE,
1158 X_GOVT_ATTENDANCE_MODE,
1159 X_ATTENDANCE_TYPE,
1160 X_GOVT_ATTENDANCE_TYPE,
1161 X_COMMENCEMENT_DT,
1162 X_MAJOR_COURSE,
1163 X_TERTIARY_ENTRANCE_SCORE,
1164 X_BASIS_FOR_ADMISSION_TYPE,
1165 X_GOVT_BASIS_FOR_ADM_TYPE,
1166 X_GOVT_DISABILITY,
1167 X_MODE);
1168 return;
1169 end if;
1170 close c1;
1171 UPDATE_ROW (
1172 X_ROWID,
1173 X_SUBMISSION_YR,
1174 X_SUBMISSION_NUMBER,
1175 X_PERSON_ID,
1176 X_COURSE_CD,
1177 X_VERSION_NUMBER,
1178 X_BIRTH_DT,
1179 X_SEX,
1180 X_ABORIG_TORRES_CD,
1181 X_GOVT_ABORIG_TORRES_CD,
1182 X_CITIZENSHIP_CD,
1183 X_GOVT_CITIZENSHIP_CD,
1184 X_PERM_RESIDENT_CD,
1185 X_GOVT_PERM_RESIDENT_CD,
1186 X_HOME_LOCATION,
1187 X_GOVT_HOME_LOCATION,
1188 X_TERM_LOCATION,
1189 X_GOVT_TERM_LOCATION,
1190 X_BIRTH_COUNTRY_CD,
1191 X_GOVT_BIRTH_COUNTRY_CD,
1192 X_YR_ARRIVAL,
1193 X_HOME_LANGUAGE_CD,
1194 X_GOVT_HOME_LANGUAGE_CD,
1195 X_PRIOR_UG_INST,
1196 X_GOVT_PRIOR_UG_INST,
1197 X_PRIOR_OTHER_QUAL,
1198 X_PRIOR_POST_GRAD,
1199 X_PRIOR_DEGREE,
1200 X_PRIOR_SUBDEG_NOTAFE,
1201 X_PRIOR_SUBDEG_TAFE,
1202 X_PRIOR_SECED_TAFE,
1203 X_PRIOR_SECED_SCHOOL,
1204 X_PRIOR_TAFE_AWARD,
1205 X_PRIOR_STUDIES_EXEMPTION,
1206 X_EXEMPTION_INSTITUTION_CD,
1207 X_GOVT_EXEMPT_INSTITU_CD,
1208 X_ATTENDANCE_MODE,
1209 X_GOVT_ATTENDANCE_MODE,
1210 X_ATTENDANCE_TYPE,
1211 X_GOVT_ATTENDANCE_TYPE,
1212 X_COMMENCEMENT_DT,
1213 X_MAJOR_COURSE,
1214 X_TERTIARY_ENTRANCE_SCORE,
1215 X_BASIS_FOR_ADMISSION_TYPE,
1216 X_GOVT_BASIS_FOR_ADM_TYPE,
1217 X_GOVT_DISABILITY,
1218 X_MODE);
1219 end ADD_ROW;
1220
1221 procedure DELETE_ROW (
1222 X_ROWID in VARCHAR2
1223 ) AS
1224 begin
1225 Before_DML (
1226 p_action => 'DELETE',
1227 x_rowid => X_ROWID
1228 );
1229 delete from IGS_ST_GOVT_STDNT_EN
1230 where ROWID = X_ROWID;
1231 if (sql%notfound) then
1232 raise no_data_found;
1233 end if;
1234 After_DML (
1235 p_action => 'DELETE',
1236 x_rowid => X_ROWID
1237 );
1238 end DELETE_ROW;
1239
1240 end IGS_ST_GOVT_STDNT_EN_PKG;