[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GVT_SPSHT_CHG_PKG
Source
1 package body IGS_ST_GVT_SPSHT_CHG_PKG as
2 /* $Header: IGSVI07B.pls 115.3 2002/11/29 04:32:19 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references IGS_ST_GVT_SPSHT_CHG%RowType;
5 new_references IGS_ST_GVT_SPSHT_CHG%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_submission_yr IN NUMBER DEFAULT NULL,
11 x_submission_number IN NUMBER DEFAULT NULL,
12 x_person_id IN NUMBER DEFAULT NULL,
13 x_course_cd IN VARCHAR2 DEFAULT NULL,
14 x_version_number IN NUMBER DEFAULT NULL,
15 x_sequence_number IN NUMBER DEFAULT NULL,
16 x_changed_update_who IN VARCHAR2 DEFAULT NULL,
17 x_changed_update_on IN DATE DEFAULT NULL,
18 x_govt_semester IN NUMBER DEFAULT NULL,
19 x_unit_cd IN VARCHAR2 DEFAULT NULL,
20 x_eftsu IN NUMBER DEFAULT NULL,
21 x_hecs_prexmt_exie IN NUMBER DEFAULT NULL,
22 x_hecs_amount_paid IN NUMBER DEFAULT NULL,
23 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
24 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
25 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
26 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
27 x_prior_degree IN VARCHAR2 DEFAULT NULL,
28 x_prior_post_grad IN VARCHAR2 DEFAULT NULL,
29 x_old_unit_cd IN VARCHAR2 DEFAULT NULL,
30 x_old_eftsu IN NUMBER DEFAULT NULL,
31 x_old_hecs_prexmt_exie IN NUMBER DEFAULT NULL,
32 x_old_hecs_amount_paid IN NUMBER DEFAULT NULL,
33 x_old_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
34 x_old_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
35 x_old_citizenship_cd IN VARCHAR2 DEFAULT NULL,
36 x_old_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
37 x_old_prior_degree IN VARCHAR2 DEFAULT NULL,
38 x_old_prior_post_grad IN VARCHAR2 DEFAULT NULL,
39 x_reported_ind 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 ) as
46
47 CURSOR cur_old_ref_values IS
48 SELECT *
49 FROM IGS_ST_GVT_SPSHT_CHG
50 WHERE rowid = x_rowid;
51
52 BEGIN
53
54 l_rowid := x_rowid;
55
56 -- Code for setting the Old and New Reference Values.
57 -- Populate Old Values.
58 Open cur_old_ref_values;
59 Fetch cur_old_ref_values INTO old_references;
60 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
61 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
62 IGS_GE_MSG_STACK.ADD;
63 App_Exception.Raise_Exception;
64 Close cur_old_ref_values;
65 Return;
66 END IF;
67 Close cur_old_ref_values;
68
69 -- Populate New Values.
70 new_references.submission_yr := x_submission_yr;
71 new_references.submission_number := x_submission_number;
72 new_references.person_id := x_person_id;
73 new_references.course_cd := x_course_cd;
74 new_references.version_number := x_version_number;
75 new_references.sequence_number := x_sequence_number;
76 new_references.changed_update_who := x_changed_update_who;
77 new_references.changed_update_on := x_changed_update_on;
78 new_references.govt_semester := x_govt_semester;
79 new_references.unit_cd := x_unit_cd;
80 new_references.eftsu := x_eftsu;
81 new_references.hecs_prexmt_exie := x_hecs_prexmt_exie;
82 new_references.hecs_amount_paid := x_hecs_amount_paid;
83 new_references.hecs_payment_option := x_hecs_payment_option;
84 new_references.differential_hecs_ind := x_differential_hecs_ind;
85 new_references.citizenship_cd := x_citizenship_cd;
86 new_references.perm_resident_cd := x_perm_resident_cd;
87 new_references.prior_degree := x_prior_degree;
88 new_references.prior_post_grad := x_prior_post_grad;
89 new_references.old_unit_cd := x_old_unit_cd;
90 new_references.old_eftsu := x_old_eftsu;
91 new_references.old_hecs_prexmt_exie := x_old_hecs_prexmt_exie;
92 new_references.old_hecs_amount_paid := x_old_hecs_amount_paid;
93 new_references.old_hecs_payment_option := x_old_hecs_payment_option;
94 new_references.old_differential_hecs_ind := x_old_differential_hecs_ind;
95 new_references.old_citizenship_cd := x_old_citizenship_cd;
96 new_references.old_perm_resident_cd := x_old_perm_resident_cd;
97 new_references.old_prior_degree := x_old_prior_degree;
98 new_references.old_prior_post_grad := x_old_prior_post_grad;
99 new_references.reported_ind := x_reported_ind;
100 IF (p_action = 'UPDATE') THEN
101 new_references.creation_date := old_references.creation_date;
102 new_references.created_by := old_references.created_by;
103 ELSE
104 new_references.creation_date := x_creation_date;
105 new_references.created_by := x_created_by;
106 END IF;
107 new_references.last_update_date := x_last_update_date;
108 new_references.last_updated_by := x_last_updated_by;
109 new_references.last_update_login := x_last_update_login;
110
111 END Set_Column_Values;
112
113 PROCEDURE Check_Parent_Existance as
114 BEGIN
115
116 IF (((old_references.submission_yr = new_references.submission_yr) AND
117 (old_references.submission_number = new_references.submission_number)) OR
118 ((new_references.submission_yr IS NULL) OR
119 (new_references.submission_number IS NULL))) THEN
120 NULL;
121 ELSE
122 IF NOT IGS_ST_GVT_SPSHT_CTL_PKG.Get_PK_For_Validation (
123 new_references.submission_yr,
124 new_references.submission_number
125 )THEN
126 FND_MESSAGE.SET_NAME('FND','FORM_RECORD_DELETED');
127 IGS_GE_MSG_STACK.ADD;
128 APP_EXCEPTION.RAISE_EXCEPTION;
129 END IF;
130 END IF;
131
132 END Check_Parent_Existance;
133
134 FUNCTION Get_PK_For_Validation (
135 x_submission_yr IN NUMBER,
136 x_submission_number IN NUMBER,
137 x_person_id IN NUMBER,
138 x_course_cd IN VARCHAR2,
139 x_sequence_number IN NUMBER
140 ) RETURN BOOLEAN as
141
142 CURSOR cur_rowid IS
143 SELECT rowid
144 FROM IGS_ST_GVT_SPSHT_CHG
145 WHERE submission_yr = x_submission_yr
146 AND submission_number = x_submission_number
147 AND person_id = x_person_id
148 AND course_cd = x_course_cd
149 AND sequence_number = x_sequence_number
150 FOR UPDATE NOWAIT;
151
152 lv_rowid cur_rowid%RowType;
153
154 BEGIN
155
156 Open cur_rowid;
157 Fetch cur_rowid INTO lv_rowid;
158 IF (cur_rowid%FOUND) THEN
159 Close cur_rowid;
160 Return TRUE;
161 ELSE
162 Close cur_rowid;
163 Return FALSE;
164 END IF;
165
166 END Get_PK_For_Validation;
167
168 PROCEDURE GET_FK_IGS_ST_GVT_SPSHT_CTL (
169 x_submission_yr IN NUMBER,
170 x_submission_number IN NUMBER
171 ) as
172
173 CURSOR cur_rowid IS
174 SELECT rowid
175 FROM IGS_ST_GVT_SPSHT_CHG
176 WHERE submission_yr = x_submission_yr
177 AND submission_number = x_submission_number ;
178
179 lv_rowid cur_rowid%RowType;
180
181 BEGIN
182
183 Open cur_rowid;
184 Fetch cur_rowid INTO lv_rowid;
185 IF (cur_rowid%FOUND) THEN
186 Close cur_rowid;
187 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSCH_GSC_FK');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 Return;
191 END IF;
192 Close cur_rowid;
193
194 END GET_FK_IGS_ST_GVT_SPSHT_CTL;
195
196
197 -- procedure to check constraints
198 PROCEDURE CHECK_CONSTRAINTS(
199 column_name IN VARCHAR2 DEFAULT NULL,
200 column_value IN VARCHAR2 DEFAULT NULL
201 ) as
202 BEGIN
203 IF column_name is null THEN
204 NULL;
205 ELSIF upper(column_name) = 'CHANGED_UPDATE_WHO' THEN
206 new_references.changed_update_who := column_value;
207 ELSIF upper(column_name) = 'CITIZENSHIP_CD' THEN
208 new_references.citizenship_cd := column_value;
209 ELSIF upper(column_name) = 'COURSE_CD' THEN
210 new_references.course_cd := column_value;
211 ELSIF upper(column_name) = 'DIFFERENTIAL_HECS_IND' THEN
212 new_references.differential_hecs_ind := column_value;
213 ELSIF upper(column_name) = 'HECS_PAYMENT_OPTION' THEN
214 new_references.hecs_payment_option := column_value;
215 ELSIF upper(column_name) = 'OLD_CITIZENSHIP_CD' THEN
216 new_references.old_citizenship_cd := column_value;
217 ELSIF upper(column_name) = 'OLD_DIFFERENTIAL_HECS_IND' THEN
218 new_references.old_differential_hecs_ind := column_value;
219 ELSIF upper(column_name) = 'OLD_HECS_PAYMENT_OPTION' THEN
220 new_references.old_hecs_payment_option := column_value;
221 ELSIF upper(column_name) = 'OLD_PERM_RESIDENT_CD' THEN
222 new_references.old_perm_resident_cd := column_value;
223 ELSIF upper(column_name) = 'OLD_PRIOR_DEGREE' THEN
224 new_references.old_prior_degree := column_value;
225 ELSIF upper(column_name) = 'OLD_PRIOR_POST_GRAD' THEN
226 new_references.old_prior_post_grad := column_value;
227 ELSIF upper(column_name) = 'OLD_UNIT_CD' THEN
228 new_references.old_unit_cd := column_value;
229 ELSIF upper(column_name) = 'PERM_RESIDENT_CD' THEN
230 new_references.perm_resident_cd := column_value;
231 ELSIF upper(column_name) = 'PRIOR_DEGREE' THEN
232 new_references.prior_degree := column_value;
233 ELSIF upper(column_name) = 'PRIOR_POST_GRAD' THEN
234 new_references.prior_post_grad := column_value;
235 ELSIF upper(column_name) = 'REPORTED_IND' THEN
236 new_references.reported_ind := column_value;
237 ELSIF upper(column_name) = 'UNIT_CD' THEN
238 new_references.unit_cd := column_value;
239 END IF;
240
241 IF upper(column_name) = 'CHANGED_UPDATE_WHO' OR column_name IS NULL THEN
242 IF new_references.changed_update_who <> UPPER(new_references.changed_update_who) THEN
243 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
244 IGS_GE_MSG_STACK.ADD;
245 APP_EXCEPTION.RAISE_EXCEPTION;
246 END IF;
247 END IF;
248 IF upper(column_name) = 'CITIZENSHIP_CD' OR column_name IS NULL THEN
249 IF new_references.citizenship_cd <> UPPER(new_references.citizenship_cd) THEN
250 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
251 IGS_GE_MSG_STACK.ADD;
252 APP_EXCEPTION.RAISE_EXCEPTION;
253 END IF;
254 END IF;
255 IF upper(column_name) = 'COURSE_CD' OR column_name IS NULL THEN
256 IF new_references.course_cd <> UPPER(new_references.course_cd) THEN
257 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
258 IGS_GE_MSG_STACK.ADD;
259 APP_EXCEPTION.RAISE_EXCEPTION;
260 END IF;
261 END IF;
262 IF upper(column_name) = 'DIFFERENTIAL_HECS_IND' OR column_name IS NULL THEN
263 IF new_references.differential_hecs_ind <> UPPER(new_references.differential_hecs_ind) THEN
264 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
265 IGS_GE_MSG_STACK.ADD;
266 APP_EXCEPTION.RAISE_EXCEPTION;
267 END IF;
268 END IF;
269 IF upper(column_name) = 'HECS_PAYMENT_OPTION' OR column_name IS NULL THEN
270 IF new_references.hecs_payment_option <> UPPER(new_references.hecs_payment_option) THEN
271 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
272 IGS_GE_MSG_STACK.ADD;
273 APP_EXCEPTION.RAISE_EXCEPTION;
274 END IF;
275 END IF;
276 IF upper(column_name) = 'OLD_CITIZENSHIP_CD' OR column_name IS NULL THEN
277 IF new_references.old_citizenship_cd <> UPPER(new_references.old_citizenship_cd) THEN
278 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
279 IGS_GE_MSG_STACK.ADD;
280 APP_EXCEPTION.RAISE_EXCEPTION;
281 END IF;
282 END IF;
283 IF upper(column_name) = 'OLD_DIFFERENTIAL_HECS_IND' OR column_name IS NULL THEN
284 IF new_references.old_differential_hecs_ind <> UPPER(new_references.old_differential_hecs_ind) THEN
285 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
286 IGS_GE_MSG_STACK.ADD;
287 APP_EXCEPTION.RAISE_EXCEPTION;
288 END IF;
289 END IF;
290 IF upper(column_name) = 'OLD_HECS_PAYMENT_OPTION' OR column_name IS NULL THEN
291 IF new_references.old_hecs_payment_option <> UPPER(new_references.old_hecs_payment_option) THEN
292 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
293 IGS_GE_MSG_STACK.ADD;
294 APP_EXCEPTION.RAISE_EXCEPTION;
295 END IF;
296 END IF;
297 IF upper(column_name) = 'OLD_PERM_RESIDENT_CD' OR column_name IS NULL THEN
298 IF new_references.old_perm_resident_cd <> UPPER(new_references.old_perm_resident_cd) THEN
299 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
300 IGS_GE_MSG_STACK.ADD;
301 APP_EXCEPTION.RAISE_EXCEPTION;
302 END IF;
303 END IF;
304 IF upper(column_name) = 'OLD_PRIOR_DEGREE' OR column_name IS NULL THEN
305 IF new_references.old_prior_degree <> UPPER(new_references.old_prior_degree) THEN
306 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
307 IGS_GE_MSG_STACK.ADD;
308 APP_EXCEPTION.RAISE_EXCEPTION;
309 END IF;
310 END IF;
311 IF upper(column_name) = 'OLD_PRIOR_POST_GRAD' OR column_name IS NULL THEN
312 IF new_references.old_prior_post_grad <> UPPER(new_references.old_prior_post_grad) THEN
313 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
314 IGS_GE_MSG_STACK.ADD;
315 APP_EXCEPTION.RAISE_EXCEPTION;
316 END IF;
317 END IF;
318 IF upper(column_name) = 'OLD_UNIT_CD' OR column_name IS NULL THEN
319 IF new_references.old_unit_cd <> UPPER(new_references.old_unit_cd) THEN
320 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
321 IGS_GE_MSG_STACK.ADD;
322 APP_EXCEPTION.RAISE_EXCEPTION;
323 END IF;
324 END IF;
325 IF upper(column_name) = 'PERM_RESIDENT_CD' OR column_name IS NULL THEN
326 IF new_references.perm_resident_cd <> UPPER(new_references.perm_resident_cd) THEN
327 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
328 IGS_GE_MSG_STACK.ADD;
329 APP_EXCEPTION.RAISE_EXCEPTION;
330 END IF;
331 END IF;
332 IF upper(column_name) = 'PRIOR_DEGREE' OR column_name IS NULL THEN
333 IF new_references.prior_degree <> UPPER(new_references.prior_degree) THEN
334 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
335 IGS_GE_MSG_STACK.ADD;
336 APP_EXCEPTION.RAISE_EXCEPTION;
337 END IF;
338 END IF;
339 IF upper(column_name) = 'PRIOR_POST_GRAD' OR column_name IS NULL THEN
340 IF new_references.prior_post_grad <> UPPER(new_references.prior_post_grad) THEN
341 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
342 IGS_GE_MSG_STACK.ADD;
343 APP_EXCEPTION.RAISE_EXCEPTION;
344 END IF;
345 END IF;
346 IF upper(column_name) = 'REPORTED_IND' OR column_name IS NULL THEN
347 IF new_references.reported_ind <> UPPER(new_references.reported_ind) THEN
348 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
349 IGS_GE_MSG_STACK.ADD;
350 APP_EXCEPTION.RAISE_EXCEPTION;
351 END IF;
352 END IF;
353 IF upper(column_name) = 'UNIT_CD' OR column_name IS NULL THEN
354 IF new_references.unit_cd <> UPPER(new_references.unit_cd) THEN
355 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
356 IGS_GE_MSG_STACK.ADD;
357 APP_EXCEPTION.RAISE_EXCEPTION;
358 END IF;
359 END IF;
360
361 END CHECK_CONSTRAINTS;
362
363
364 PROCEDURE Before_DML (
365 p_action IN VARCHAR2,
366 x_rowid IN VARCHAR2 DEFAULT NULL,
370 x_course_cd IN VARCHAR2 DEFAULT NULL,
367 x_submission_yr IN NUMBER DEFAULT NULL,
368 x_submission_number IN NUMBER DEFAULT NULL,
369 x_person_id IN NUMBER DEFAULT NULL,
371 x_version_number IN NUMBER DEFAULT NULL,
372 x_sequence_number IN NUMBER DEFAULT NULL,
373 x_changed_update_who IN VARCHAR2 DEFAULT NULL,
374 x_changed_update_on IN DATE DEFAULT NULL,
375 x_govt_semester IN NUMBER DEFAULT NULL,
376 x_unit_cd IN VARCHAR2 DEFAULT NULL,
377 x_eftsu IN NUMBER DEFAULT NULL,
378 x_hecs_prexmt_exie IN NUMBER DEFAULT NULL,
379 x_hecs_amount_paid IN NUMBER DEFAULT NULL,
380 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
381 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
382 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
383 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
384 x_prior_degree IN VARCHAR2 DEFAULT NULL,
385 x_prior_post_grad IN VARCHAR2 DEFAULT NULL,
386 x_old_unit_cd IN VARCHAR2 DEFAULT NULL,
387 x_old_eftsu IN NUMBER DEFAULT NULL,
388 x_old_hecs_prexmt_exie IN NUMBER DEFAULT NULL,
389 x_old_hecs_amount_paid IN NUMBER DEFAULT NULL,
390 x_old_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
391 x_old_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
392 x_old_citizenship_cd IN VARCHAR2 DEFAULT NULL,
393 x_old_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
394 x_old_prior_degree IN VARCHAR2 DEFAULT NULL,
395 x_old_prior_post_grad IN VARCHAR2 DEFAULT NULL,
396 x_reported_ind IN VARCHAR2 DEFAULT NULL,
397 x_creation_date IN DATE DEFAULT NULL,
398 x_created_by IN NUMBER DEFAULT NULL,
399 x_last_update_date IN DATE DEFAULT NULL,
400 x_last_updated_by IN NUMBER DEFAULT NULL,
401 x_last_update_login IN NUMBER DEFAULT NULL
402 ) as
403 BEGIN
404
405 Set_Column_Values (
406 p_action,
407 x_rowid,
408 x_submission_yr,
409 x_submission_number,
410 x_person_id,
411 x_course_cd,
412 x_version_number,
413 x_sequence_number,
414 x_changed_update_who,
415 x_changed_update_on,
416 x_govt_semester,
417 x_unit_cd,
418 x_eftsu,
419 x_hecs_prexmt_exie,
420 x_hecs_amount_paid,
421 x_hecs_payment_option,
422 x_differential_hecs_ind,
423 x_citizenship_cd,
424 x_perm_resident_cd,
425 x_prior_degree,
426 x_prior_post_grad,
427 x_old_unit_cd,
428 x_old_eftsu,
429 x_old_hecs_prexmt_exie,
430 x_old_hecs_amount_paid,
431 x_old_hecs_payment_option,
432 x_old_differential_hecs_ind,
433 x_old_citizenship_cd,
434 x_old_perm_resident_cd,
435 x_old_prior_degree,
436 x_old_prior_post_grad,
437 x_reported_ind,
438 x_creation_date,
439 x_created_by,
440 x_last_update_date,
441 x_last_updated_by,
442 x_last_update_login
443 );
444
445 IF (p_action = 'INSERT') THEN
446 -- Call all the procedures related to Before Insert.
447 Null;
448 IF GET_PK_FOR_VALIDATION(
449 new_references.submission_yr,
450 new_references.submission_number,
451 new_references.person_id,
452 new_references.course_cd,
453 new_references.sequence_number
454 )THEN
455 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
456 IGS_GE_MSG_STACK.ADD;
457 APP_EXCEPTION.RAISE_EXCEPTION;
458 END IF;
459 Check_Constraints;
460 Check_Parent_Existance;
461 ELSIF (p_action = 'UPDATE') THEN
462 -- Call all the procedures related to Before Update.
463 Null;
464 Check_Constraints;
465 Check_Parent_Existance;
466 ELSIF (p_action = 'DELETE') THEN
467 -- Call all the procedures related to Before Delete.
468 Null;
469 ELSIF (p_action = 'VALIDATE_INSERT') THEN
470 -- Call all the procedures related to Before Delete.
471 IF GET_PK_FOR_VALIDATION(
472 new_references.submission_yr,
473 new_references.submission_number,
474 new_references.person_id,
475 new_references.course_cd,
476 new_references.sequence_number
477 )THEN
478 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
479 IGS_GE_MSG_STACK.ADD;
480 APP_EXCEPTION.RAISE_EXCEPTION;
481 END IF;
482 Check_Constraints;
483 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
484 -- Call all the procedures related to Before Delete.
485 check_constraints;
486 ELSIF (p_action = 'VALIDATE_DELETE') THEN
487 -- Call all the procedures related to Before Delete.
488 null;
489
490 END IF;
491
492 END Before_DML;
493
494 PROCEDURE After_DML (
495 p_action IN VARCHAR2,
496 x_rowid IN VARCHAR2
497 ) as
498 BEGIN
499
500 l_rowid := x_rowid;
501
502 END After_DML;
503
504
505 procedure INSERT_ROW (
506 X_ROWID in out NOCOPY VARCHAR2,
507 X_SUBMISSION_YR in NUMBER,
508 X_SUBMISSION_NUMBER in NUMBER,
509 X_PERSON_ID in NUMBER,
510 X_COURSE_CD in VARCHAR2,
514 X_CHANGED_UPDATE_ON in DATE,
511 X_SEQUENCE_NUMBER in NUMBER,
512 X_VERSION_NUMBER in NUMBER,
513 X_CHANGED_UPDATE_WHO in VARCHAR2,
515 X_GOVT_SEMESTER in NUMBER,
516 X_UNIT_CD in VARCHAR2,
517 X_EFTSU in NUMBER,
518 X_HECS_PREXMT_EXIE in NUMBER,
519 X_HECS_AMOUNT_PAID in NUMBER,
520 X_HECS_PAYMENT_OPTION in VARCHAR2,
521 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
522 X_CITIZENSHIP_CD in VARCHAR2,
523 X_PERM_RESIDENT_CD in VARCHAR2,
524 X_PRIOR_DEGREE in VARCHAR2,
525 X_PRIOR_POST_GRAD in VARCHAR2,
526 X_OLD_UNIT_CD in VARCHAR2,
527 X_OLD_EFTSU in NUMBER,
528 X_OLD_HECS_PREXMT_EXIE in NUMBER,
529 X_OLD_HECS_AMOUNT_PAID in NUMBER,
530 X_OLD_HECS_PAYMENT_OPTION in VARCHAR2,
531 X_OLD_DIFFERENTIAL_HECS_IND in VARCHAR2,
532 X_OLD_CITIZENSHIP_CD in VARCHAR2,
533 X_OLD_PERM_RESIDENT_CD in VARCHAR2,
534 X_OLD_PRIOR_DEGREE in VARCHAR2,
535 X_OLD_PRIOR_POST_GRAD in VARCHAR2,
536 X_REPORTED_IND in VARCHAR2,
537 X_MODE in VARCHAR2 default 'R'
538 ) as
539 cursor C is select ROWID from IGS_ST_GVT_SPSHT_CHG
540 where SUBMISSION_YR = X_SUBMISSION_YR
541 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
542 and PERSON_ID = X_PERSON_ID
543 and COURSE_CD = X_COURSE_CD
544 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
545 X_LAST_UPDATE_DATE DATE;
546 X_LAST_UPDATED_BY NUMBER;
547 X_LAST_UPDATE_LOGIN NUMBER;
548 X_REQUEST_ID NUMBER;
549 X_PROGRAM_ID NUMBER;
550 X_PROGRAM_APPLICATION_ID NUMBER;
551 X_PROGRAM_UPDATE_DATE DATE;
552 begin
553 X_LAST_UPDATE_DATE := SYSDATE;
554 if(X_MODE = 'I') then
555 X_LAST_UPDATED_BY := 1;
556 X_LAST_UPDATE_LOGIN := 0;
557 elsif (X_MODE = 'R') then
558 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
559 if X_LAST_UPDATED_BY is NULL then
560 X_LAST_UPDATED_BY := -1;
561 end if;
562 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
563 if X_LAST_UPDATE_LOGIN is NULL then
564 X_LAST_UPDATE_LOGIN := -1;
565 end if;
566 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
567 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
568 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
569 if (X_REQUEST_ID = -1) then
570 X_REQUEST_ID := NULL;
571 X_PROGRAM_ID := NULL;
572 X_PROGRAM_APPLICATION_ID := NULL;
573 X_PROGRAM_UPDATE_DATE := NULL;
574 else
575 X_PROGRAM_UPDATE_DATE := SYSDATE;
576 end if;
577 else
578 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
579 IGS_GE_MSG_STACK.ADD;
580 app_exception.raise_exception;
581 end if;
582
583 Before_DML (
584 p_action => 'INSERT',
585 x_rowid => X_ROWID,
586 x_submission_yr => X_SUBMISSION_YR,
587 x_submission_number => X_SUBMISSION_NUMBER,
588 x_person_id => X_PERSON_ID,
589 x_course_cd =>X_COURSE_CD,
590 x_version_number =>X_VERSION_NUMBER,
591 x_sequence_number => X_SEQUENCE_NUMBER,
592 x_changed_update_who => X_CHANGED_UPDATE_WHO,
593 x_changed_update_on => X_CHANGED_UPDATE_ON,
594 x_govt_semester => X_GOVT_SEMESTER,
595 x_unit_cd => X_UNIT_CD,
596 x_eftsu => X_EFTSU,
597 x_hecs_prexmt_exie => X_HECS_PREXMT_EXIE,
598 x_hecs_amount_paid => X_HECS_AMOUNT_PAID,
599 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
600 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
601 x_citizenship_cd => X_CITIZENSHIP_CD,
602 x_perm_resident_cd => X_PERM_RESIDENT_CD,
603 x_prior_degree => X_PRIOR_DEGREE,
604 x_prior_post_grad => X_PRIOR_POST_GRAD,
605 x_old_unit_cd => X_OLD_UNIT_CD,
606 x_old_eftsu => X_OLD_EFTSU,
607 x_old_hecs_prexmt_exie => X_OLD_HECS_PREXMT_EXIE,
608 x_old_hecs_amount_paid => X_OLD_HECS_AMOUNT_PAID,
609 x_old_hecs_payment_option => X_OLD_HECS_PAYMENT_OPTION,
610 x_old_differential_hecs_ind => X_OLD_DIFFERENTIAL_HECS_IND,
611 x_old_citizenship_cd => X_OLD_CITIZENSHIP_CD,
612 x_old_perm_resident_cd => X_OLD_PERM_RESIDENT_CD,
613 x_old_prior_degree => X_OLD_PRIOR_DEGREE,
614 x_old_prior_post_grad => X_OLD_PRIOR_POST_GRAD,
615 x_reported_ind => NVL(X_REPORTED_IND,'N'),
616 x_creation_date => X_LAST_UPDATE_DATE,
617 x_created_by => X_LAST_UPDATED_BY,
618 x_last_update_date => X_LAST_UPDATE_DATE,
619 x_last_updated_by => X_LAST_UPDATED_BY,
620 x_last_update_login => X_LAST_UPDATE_LOGIN
621 );
622 insert into IGS_ST_GVT_SPSHT_CHG (
623 SUBMISSION_YR,
624 SUBMISSION_NUMBER,
625 PERSON_ID,
626 COURSE_CD,
627 VERSION_NUMBER,
628 SEQUENCE_NUMBER,
629 CHANGED_UPDATE_WHO,
630 CHANGED_UPDATE_ON,
631 GOVT_SEMESTER,
632 UNIT_CD,
633 EFTSU,
634 HECS_PREXMT_EXIE,
635 HECS_AMOUNT_PAID,
636 HECS_PAYMENT_OPTION,
637 DIFFERENTIAL_HECS_IND,
638 CITIZENSHIP_CD,
639 PERM_RESIDENT_CD,
640 PRIOR_DEGREE,
641 PRIOR_POST_GRAD,
642 OLD_UNIT_CD,
643 OLD_EFTSU,
644 OLD_HECS_PREXMT_EXIE,
645 OLD_HECS_AMOUNT_PAID,
646 OLD_HECS_PAYMENT_OPTION,
647 OLD_DIFFERENTIAL_HECS_IND,
648 OLD_CITIZENSHIP_CD,
649 OLD_PERM_RESIDENT_CD,
653 CREATION_DATE,
650 OLD_PRIOR_DEGREE,
651 OLD_PRIOR_POST_GRAD,
652 REPORTED_IND,
654 CREATED_BY,
655 LAST_UPDATE_DATE,
656 LAST_UPDATED_BY,
657 LAST_UPDATE_LOGIN,
658 REQUEST_ID,
659 PROGRAM_ID,
660 PROGRAM_APPLICATION_ID,
661 PROGRAM_UPDATE_DATE
662 ) values (
663 NEW_REFERENCES.SUBMISSION_YR,
664 NEW_REFERENCES.SUBMISSION_NUMBER,
665 NEW_REFERENCES.PERSON_ID,
666 NEW_REFERENCES.COURSE_CD,
667 NEW_REFERENCES.VERSION_NUMBER,
668 NEW_REFERENCES.SEQUENCE_NUMBER,
669 NEW_REFERENCES.CHANGED_UPDATE_WHO,
670 NEW_REFERENCES.CHANGED_UPDATE_ON,
671 NEW_REFERENCES.GOVT_SEMESTER,
672 NEW_REFERENCES.UNIT_CD,
673 NEW_REFERENCES.EFTSU,
674 NEW_REFERENCES.HECS_PREXMT_EXIE,
675 NEW_REFERENCES.HECS_AMOUNT_PAID,
676 NEW_REFERENCES.HECS_PAYMENT_OPTION,
677 NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
678 NEW_REFERENCES.CITIZENSHIP_CD,
679 NEW_REFERENCES.PERM_RESIDENT_CD,
680 NEW_REFERENCES.PRIOR_DEGREE,
681 NEW_REFERENCES.PRIOR_POST_GRAD,
682 NEW_REFERENCES.OLD_UNIT_CD,
683 NEW_REFERENCES.OLD_EFTSU,
684 NEW_REFERENCES.OLD_HECS_PREXMT_EXIE,
685 NEW_REFERENCES.OLD_HECS_AMOUNT_PAID,
686 NEW_REFERENCES.OLD_HECS_PAYMENT_OPTION,
687 NEW_REFERENCES.OLD_DIFFERENTIAL_HECS_IND,
688 NEW_REFERENCES.OLD_CITIZENSHIP_CD,
689 NEW_REFERENCES.OLD_PERM_RESIDENT_CD,
690 NEW_REFERENCES.OLD_PRIOR_DEGREE,
691 NEW_REFERENCES.OLD_PRIOR_POST_GRAD,
692 NEW_REFERENCES.REPORTED_IND,
693 X_LAST_UPDATE_DATE,
694 X_LAST_UPDATED_BY,
695 X_LAST_UPDATE_DATE,
696 X_LAST_UPDATED_BY,
697 X_LAST_UPDATE_LOGIN,
698 X_REQUEST_ID,
699 X_PROGRAM_ID,
700 X_PROGRAM_APPLICATION_ID,
701 X_PROGRAM_UPDATE_DATE
702 );
703
704 open c;
705 fetch c into X_ROWID;
706 if (c%notfound) then
707 close c;
708 raise no_data_found;
709 end if;
710 close c;
711 After_DML (
712 p_action => 'INSERT',
713 x_rowid => X_ROWID
714 );
715 end INSERT_ROW;
716
717 procedure LOCK_ROW (
718 X_ROWID in VARCHAR2,
719 X_SUBMISSION_YR in NUMBER,
720 X_SUBMISSION_NUMBER in NUMBER,
721 X_PERSON_ID in NUMBER,
722 X_COURSE_CD in VARCHAR2,
723 X_SEQUENCE_NUMBER in NUMBER,
724 X_VERSION_NUMBER in NUMBER,
725 X_CHANGED_UPDATE_WHO in VARCHAR2,
726 X_CHANGED_UPDATE_ON in DATE,
727 X_GOVT_SEMESTER in NUMBER,
728 X_UNIT_CD in VARCHAR2,
729 X_EFTSU in NUMBER,
730 X_HECS_PREXMT_EXIE in NUMBER,
731 X_HECS_AMOUNT_PAID in NUMBER,
732 X_HECS_PAYMENT_OPTION in VARCHAR2,
733 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
734 X_CITIZENSHIP_CD in VARCHAR2,
735 X_PERM_RESIDENT_CD in VARCHAR2,
736 X_PRIOR_DEGREE in VARCHAR2,
737 X_PRIOR_POST_GRAD in VARCHAR2,
738 X_OLD_UNIT_CD in VARCHAR2,
739 X_OLD_EFTSU in NUMBER,
740 X_OLD_HECS_PREXMT_EXIE in NUMBER,
741 X_OLD_HECS_AMOUNT_PAID in NUMBER,
742 X_OLD_HECS_PAYMENT_OPTION in VARCHAR2,
743 X_OLD_DIFFERENTIAL_HECS_IND in VARCHAR2,
744 X_OLD_CITIZENSHIP_CD in VARCHAR2,
745 X_OLD_PERM_RESIDENT_CD in VARCHAR2,
746 X_OLD_PRIOR_DEGREE in VARCHAR2,
747 X_OLD_PRIOR_POST_GRAD in VARCHAR2,
748 X_REPORTED_IND in VARCHAR2
749 ) as
750 cursor c1 is select
751 VERSION_NUMBER,
752 CHANGED_UPDATE_WHO,
753 CHANGED_UPDATE_ON,
754 GOVT_SEMESTER,
755 UNIT_CD,
756 EFTSU,
757 HECS_PREXMT_EXIE,
758 HECS_AMOUNT_PAID,
759 HECS_PAYMENT_OPTION,
760 DIFFERENTIAL_HECS_IND,
761 CITIZENSHIP_CD,
762 PERM_RESIDENT_CD,
763 PRIOR_DEGREE,
764 PRIOR_POST_GRAD,
765 OLD_UNIT_CD,
766 OLD_EFTSU,
767 OLD_HECS_PREXMT_EXIE,
768 OLD_HECS_AMOUNT_PAID,
769 OLD_HECS_PAYMENT_OPTION,
770 OLD_DIFFERENTIAL_HECS_IND,
771 OLD_CITIZENSHIP_CD,
772 OLD_PERM_RESIDENT_CD,
773 OLD_PRIOR_DEGREE,
774 OLD_PRIOR_POST_GRAD,
775 REPORTED_IND
776 from IGS_ST_GVT_SPSHT_CHG
777 where ROWID = X_ROWID
778 for update nowait;
779 tlinfo c1%rowtype;
780
781 begin
782 open c1;
783 fetch c1 into tlinfo;
784 if (c1%notfound) then
785 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
786 IGS_GE_MSG_STACK.ADD;
787 app_exception.raise_exception;
788 close c1;
789 return;
790 end if;
791 close c1;
792
793 if ( (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
794 AND ((tlinfo.CHANGED_UPDATE_WHO = X_CHANGED_UPDATE_WHO)
795 OR ((tlinfo.CHANGED_UPDATE_WHO is null)
796 AND (X_CHANGED_UPDATE_WHO is null)))
797 AND ((tlinfo.CHANGED_UPDATE_ON = X_CHANGED_UPDATE_ON)
798 OR ((tlinfo.CHANGED_UPDATE_ON is null)
799 AND (X_CHANGED_UPDATE_ON is null)))
800 AND ((tlinfo.GOVT_SEMESTER = X_GOVT_SEMESTER)
801 OR ((tlinfo.GOVT_SEMESTER is null)
802 AND (X_GOVT_SEMESTER is null)))
803 AND ((tlinfo.UNIT_CD = X_UNIT_CD)
804 OR ((tlinfo.UNIT_CD is null)
805 AND (X_UNIT_CD is null)))
806 AND ((tlinfo.EFTSU = X_EFTSU)
810 OR ((tlinfo.HECS_PREXMT_EXIE is null)
807 OR ((tlinfo.EFTSU is null)
808 AND (X_EFTSU is null)))
809 AND ((tlinfo.HECS_PREXMT_EXIE = X_HECS_PREXMT_EXIE)
811 AND (X_HECS_PREXMT_EXIE is null)))
812 AND ((tlinfo.HECS_AMOUNT_PAID = X_HECS_AMOUNT_PAID)
813 OR ((tlinfo.HECS_AMOUNT_PAID is null)
814 AND (X_HECS_AMOUNT_PAID is null)))
815 AND ((tlinfo.HECS_PAYMENT_OPTION = X_HECS_PAYMENT_OPTION)
816 OR ((tlinfo.HECS_PAYMENT_OPTION is null)
817 AND (X_HECS_PAYMENT_OPTION is null)))
818 AND ((tlinfo.DIFFERENTIAL_HECS_IND = X_DIFFERENTIAL_HECS_IND)
819 OR ((tlinfo.DIFFERENTIAL_HECS_IND is null)
820 AND (X_DIFFERENTIAL_HECS_IND is null)))
821 AND ((tlinfo.CITIZENSHIP_CD = X_CITIZENSHIP_CD)
822 OR ((tlinfo.CITIZENSHIP_CD is null)
823 AND (X_CITIZENSHIP_CD is null)))
824 AND ((tlinfo.PERM_RESIDENT_CD = X_PERM_RESIDENT_CD)
825 OR ((tlinfo.PERM_RESIDENT_CD is null)
826 AND (X_PERM_RESIDENT_CD is null)))
827 AND ((tlinfo.PRIOR_DEGREE = X_PRIOR_DEGREE)
828 OR ((tlinfo.PRIOR_DEGREE is null)
829 AND (X_PRIOR_DEGREE is null)))
830 AND ((tlinfo.PRIOR_POST_GRAD = X_PRIOR_POST_GRAD)
831 OR ((tlinfo.PRIOR_POST_GRAD is null)
832 AND (X_PRIOR_POST_GRAD is null)))
833 AND ((tlinfo.OLD_UNIT_CD = X_OLD_UNIT_CD)
834 OR ((tlinfo.OLD_UNIT_CD is null)
835 AND (X_OLD_UNIT_CD is null)))
836 AND ((tlinfo.OLD_EFTSU = X_OLD_EFTSU)
837 OR ((tlinfo.OLD_EFTSU is null)
838 AND (X_OLD_EFTSU is null)))
839 AND ((tlinfo.OLD_HECS_PREXMT_EXIE = X_OLD_HECS_PREXMT_EXIE)
840 OR ((tlinfo.OLD_HECS_PREXMT_EXIE is null)
841 AND (X_OLD_HECS_PREXMT_EXIE is null)))
842 AND ((tlinfo.OLD_HECS_AMOUNT_PAID = X_OLD_HECS_AMOUNT_PAID)
843 OR ((tlinfo.OLD_HECS_AMOUNT_PAID is null)
844 AND (X_OLD_HECS_AMOUNT_PAID is null)))
845 AND ((tlinfo.OLD_HECS_PAYMENT_OPTION = X_OLD_HECS_PAYMENT_OPTION)
846 OR ((tlinfo.OLD_HECS_PAYMENT_OPTION is null)
847 AND (X_OLD_HECS_PAYMENT_OPTION is null)))
848 AND ((tlinfo.OLD_DIFFERENTIAL_HECS_IND = X_OLD_DIFFERENTIAL_HECS_IND)
849 OR ((tlinfo.OLD_DIFFERENTIAL_HECS_IND is null)
850 AND (X_OLD_DIFFERENTIAL_HECS_IND is null)))
851 AND ((tlinfo.OLD_CITIZENSHIP_CD = X_OLD_CITIZENSHIP_CD)
852 OR ((tlinfo.OLD_CITIZENSHIP_CD is null)
853 AND (X_OLD_CITIZENSHIP_CD is null)))
854 AND ((tlinfo.OLD_PERM_RESIDENT_CD = X_OLD_PERM_RESIDENT_CD)
855 OR ((tlinfo.OLD_PERM_RESIDENT_CD is null)
856 AND (X_OLD_PERM_RESIDENT_CD is null)))
857 AND ((tlinfo.OLD_PRIOR_DEGREE = X_OLD_PRIOR_DEGREE)
858 OR ((tlinfo.OLD_PRIOR_DEGREE is null)
859 AND (X_OLD_PRIOR_DEGREE is null)))
860 AND ((tlinfo.OLD_PRIOR_POST_GRAD = X_OLD_PRIOR_POST_GRAD)
861 OR ((tlinfo.OLD_PRIOR_POST_GRAD is null)
862 AND (X_OLD_PRIOR_POST_GRAD is null)))
863 AND (tlinfo.REPORTED_IND = X_REPORTED_IND)
864 ) then
865 null;
866 else
867 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
868 IGS_GE_MSG_STACK.ADD;
869 app_exception.raise_exception;
870 end if;
871 return;
872 end LOCK_ROW;
873
874 procedure UPDATE_ROW (
875 X_ROWID in VARCHAR2,
876 X_SUBMISSION_YR in NUMBER,
877 X_SUBMISSION_NUMBER in NUMBER,
878 X_PERSON_ID in NUMBER,
879 X_COURSE_CD in VARCHAR2,
880 X_SEQUENCE_NUMBER in NUMBER,
881 X_VERSION_NUMBER in NUMBER,
882 X_CHANGED_UPDATE_WHO in VARCHAR2,
883 X_CHANGED_UPDATE_ON in DATE,
884 X_GOVT_SEMESTER in NUMBER,
885 X_UNIT_CD in VARCHAR2,
886 X_EFTSU in NUMBER,
887 X_HECS_PREXMT_EXIE in NUMBER,
888 X_HECS_AMOUNT_PAID in NUMBER,
889 X_HECS_PAYMENT_OPTION in VARCHAR2,
890 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
891 X_CITIZENSHIP_CD in VARCHAR2,
892 X_PERM_RESIDENT_CD in VARCHAR2,
893 X_PRIOR_DEGREE in VARCHAR2,
894 X_PRIOR_POST_GRAD in VARCHAR2,
895 X_OLD_UNIT_CD in VARCHAR2,
896 X_OLD_EFTSU in NUMBER,
897 X_OLD_HECS_PREXMT_EXIE in NUMBER,
898 X_OLD_HECS_AMOUNT_PAID in NUMBER,
899 X_OLD_HECS_PAYMENT_OPTION in VARCHAR2,
900 X_OLD_DIFFERENTIAL_HECS_IND in VARCHAR2,
901 X_OLD_CITIZENSHIP_CD in VARCHAR2,
902 X_OLD_PERM_RESIDENT_CD in VARCHAR2,
903 X_OLD_PRIOR_DEGREE in VARCHAR2,
904 X_OLD_PRIOR_POST_GRAD in VARCHAR2,
905 X_REPORTED_IND in VARCHAR2,
906 X_MODE in VARCHAR2 default 'R'
907 ) as
908 X_LAST_UPDATE_DATE DATE;
909 X_LAST_UPDATED_BY NUMBER;
910 X_LAST_UPDATE_LOGIN NUMBER;
911 X_REQUEST_ID NUMBER;
912 X_PROGRAM_ID NUMBER;
913 X_PROGRAM_APPLICATION_ID NUMBER;
914 X_PROGRAM_UPDATE_DATE DATE;
915 begin
916 X_LAST_UPDATE_DATE := SYSDATE;
917 if(X_MODE = 'I') then
918 X_LAST_UPDATED_BY := 1;
919 X_LAST_UPDATE_LOGIN := 0;
920 elsif (X_MODE = 'R') then
921 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
922 if X_LAST_UPDATED_BY is NULL then
923 X_LAST_UPDATED_BY := -1;
924 end if;
925 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
926 if X_LAST_UPDATE_LOGIN is NULL then
927 X_LAST_UPDATE_LOGIN := -1;
931 IGS_GE_MSG_STACK.ADD;
928 end if;
929 else
930 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
932 app_exception.raise_exception;
933 end if;
934 Before_DML (
935 p_action => 'UPDATE',
936 x_rowid => X_ROWID,
937 x_submission_yr => X_SUBMISSION_YR,
938 x_submission_number => X_SUBMISSION_NUMBER,
939 x_person_id => X_PERSON_ID,
940 x_course_cd =>X_COURSE_CD,
941 x_version_number =>X_VERSION_NUMBER,
942 x_sequence_number => X_SEQUENCE_NUMBER,
943 x_changed_update_who => X_CHANGED_UPDATE_WHO,
944 x_changed_update_on => X_CHANGED_UPDATE_ON,
945 x_govt_semester => X_GOVT_SEMESTER,
946 x_unit_cd => X_UNIT_CD,
947 x_eftsu => X_EFTSU,
948 x_hecs_prexmt_exie => X_HECS_PREXMT_EXIE,
949 x_hecs_amount_paid => X_HECS_AMOUNT_PAID,
950 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
951 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
952 x_citizenship_cd => X_CITIZENSHIP_CD,
953 x_perm_resident_cd => X_PERM_RESIDENT_CD,
954 x_prior_degree => X_PRIOR_DEGREE,
955 x_prior_post_grad => X_PRIOR_POST_GRAD,
956 x_old_unit_cd => X_OLD_UNIT_CD,
957 x_old_eftsu => X_OLD_EFTSU,
958 x_old_hecs_prexmt_exie => X_OLD_HECS_PREXMT_EXIE,
959 x_old_hecs_amount_paid => X_OLD_HECS_AMOUNT_PAID,
960 x_old_hecs_payment_option => X_OLD_HECS_PAYMENT_OPTION,
961 x_old_differential_hecs_ind => X_OLD_DIFFERENTIAL_HECS_IND,
962 x_old_citizenship_cd => X_OLD_CITIZENSHIP_CD,
963 x_old_perm_resident_cd => X_OLD_PERM_RESIDENT_CD,
964 x_old_prior_degree => X_OLD_PRIOR_DEGREE,
965 x_old_prior_post_grad => X_OLD_PRIOR_POST_GRAD,
966 x_reported_ind => X_REPORTED_IND,
967 x_creation_date => X_LAST_UPDATE_DATE,
968 x_created_by => X_LAST_UPDATED_BY,
969 x_last_update_date => X_LAST_UPDATE_DATE,
970 x_last_updated_by => X_LAST_UPDATED_BY,
971 x_last_update_login => X_LAST_UPDATE_LOGIN
972 );
973
974 if (X_MODE = 'R') then
975 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
976 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
977 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
978 if (X_REQUEST_ID = -1) then
979 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
980 X_PROGRAM_ID := OLD_REFERENCES.PROGRAM_ID;
981 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
982 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
983 else
984 X_PROGRAM_UPDATE_DATE := SYSDATE;
985 end if;
986 end if;
987 update IGS_ST_GVT_SPSHT_CHG set
988 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
989 CHANGED_UPDATE_WHO = NEW_REFERENCES.CHANGED_UPDATE_WHO,
990 CHANGED_UPDATE_ON = NEW_REFERENCES.CHANGED_UPDATE_ON,
991 GOVT_SEMESTER = NEW_REFERENCES.GOVT_SEMESTER,
992 UNIT_CD = NEW_REFERENCES.UNIT_CD,
993 EFTSU = NEW_REFERENCES.EFTSU,
994 HECS_PREXMT_EXIE = NEW_REFERENCES.HECS_PREXMT_EXIE,
995 HECS_AMOUNT_PAID = NEW_REFERENCES.HECS_AMOUNT_PAID,
996 HECS_PAYMENT_OPTION = NEW_REFERENCES.HECS_PAYMENT_OPTION,
997 DIFFERENTIAL_HECS_IND = NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
998 CITIZENSHIP_CD = NEW_REFERENCES.CITIZENSHIP_CD,
999 PERM_RESIDENT_CD = NEW_REFERENCES.PERM_RESIDENT_CD,
1000 PRIOR_DEGREE = NEW_REFERENCES.PRIOR_DEGREE,
1001 PRIOR_POST_GRAD = NEW_REFERENCES.PRIOR_POST_GRAD,
1002 OLD_UNIT_CD = NEW_REFERENCES.OLD_UNIT_CD,
1003 OLD_EFTSU = NEW_REFERENCES.OLD_EFTSU,
1004 OLD_HECS_PREXMT_EXIE = NEW_REFERENCES.OLD_HECS_PREXMT_EXIE,
1005 OLD_HECS_AMOUNT_PAID = NEW_REFERENCES.OLD_HECS_AMOUNT_PAID,
1006 OLD_HECS_PAYMENT_OPTION = NEW_REFERENCES.OLD_HECS_PAYMENT_OPTION,
1007 OLD_DIFFERENTIAL_HECS_IND = NEW_REFERENCES.OLD_DIFFERENTIAL_HECS_IND,
1008 OLD_CITIZENSHIP_CD = NEW_REFERENCES.OLD_CITIZENSHIP_CD,
1009 OLD_PERM_RESIDENT_CD = NEW_REFERENCES.OLD_PERM_RESIDENT_CD,
1010 OLD_PRIOR_DEGREE = NEW_REFERENCES.OLD_PRIOR_DEGREE,
1011 OLD_PRIOR_POST_GRAD = NEW_REFERENCES.OLD_PRIOR_POST_GRAD,
1012 REPORTED_IND = NEW_REFERENCES.REPORTED_IND,
1013 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1014 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1015 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1016 REQUEST_ID = X_REQUEST_ID,
1017 PROGRAM_ID = X_PROGRAM_ID,
1018 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1019 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1020 where ROWID = X_ROWID
1021 ;
1022 if (sql%notfound) then
1023 raise no_data_found;
1024 end if;
1025 After_DML (
1026 p_action => 'UPDATE',
1027 x_rowid => X_ROWID
1028 );
1029 end UPDATE_ROW;
1030
1031 procedure ADD_ROW (
1032 X_ROWID in out NOCOPY VARCHAR2,
1033 X_SUBMISSION_YR in NUMBER,
1034 X_SUBMISSION_NUMBER in NUMBER,
1035 X_PERSON_ID in NUMBER,
1036 X_COURSE_CD in VARCHAR2,
1040 X_CHANGED_UPDATE_ON in DATE,
1037 X_SEQUENCE_NUMBER in NUMBER,
1038 X_VERSION_NUMBER in NUMBER,
1039 X_CHANGED_UPDATE_WHO in VARCHAR2,
1041 X_GOVT_SEMESTER in NUMBER,
1042 X_UNIT_CD in VARCHAR2,
1043 X_EFTSU in NUMBER,
1044 X_HECS_PREXMT_EXIE in NUMBER,
1045 X_HECS_AMOUNT_PAID in NUMBER,
1046 X_HECS_PAYMENT_OPTION in VARCHAR2,
1047 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
1048 X_CITIZENSHIP_CD in VARCHAR2,
1049 X_PERM_RESIDENT_CD in VARCHAR2,
1050 X_PRIOR_DEGREE in VARCHAR2,
1051 X_PRIOR_POST_GRAD in VARCHAR2,
1052 X_OLD_UNIT_CD in VARCHAR2,
1053 X_OLD_EFTSU in NUMBER,
1054 X_OLD_HECS_PREXMT_EXIE in NUMBER,
1055 X_OLD_HECS_AMOUNT_PAID in NUMBER,
1056 X_OLD_HECS_PAYMENT_OPTION in VARCHAR2,
1057 X_OLD_DIFFERENTIAL_HECS_IND in VARCHAR2,
1058 X_OLD_CITIZENSHIP_CD in VARCHAR2,
1059 X_OLD_PERM_RESIDENT_CD in VARCHAR2,
1060 X_OLD_PRIOR_DEGREE in VARCHAR2,
1061 X_OLD_PRIOR_POST_GRAD in VARCHAR2,
1062 X_REPORTED_IND in VARCHAR2,
1063 X_MODE in VARCHAR2 default 'R'
1064 ) as
1065 cursor c1 is select rowid from IGS_ST_GVT_SPSHT_CHG
1066 where SUBMISSION_YR = X_SUBMISSION_YR
1067 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
1068 and PERSON_ID = X_PERSON_ID
1069 and COURSE_CD = X_COURSE_CD
1070 and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1071 ;
1072 begin
1073 open c1;
1074 fetch c1 into X_ROWID;
1075 if (c1%notfound) then
1076 close c1;
1077 INSERT_ROW (
1078 X_ROWID,
1079 X_SUBMISSION_YR,
1080 X_SUBMISSION_NUMBER,
1081 X_PERSON_ID,
1082 X_COURSE_CD,
1083 X_SEQUENCE_NUMBER,
1084 X_VERSION_NUMBER,
1085 X_CHANGED_UPDATE_WHO,
1086 X_CHANGED_UPDATE_ON,
1087 X_GOVT_SEMESTER,
1088 X_UNIT_CD,
1089 X_EFTSU,
1090 X_HECS_PREXMT_EXIE,
1091 X_HECS_AMOUNT_PAID,
1092 X_HECS_PAYMENT_OPTION,
1093 X_DIFFERENTIAL_HECS_IND,
1094 X_CITIZENSHIP_CD,
1095 X_PERM_RESIDENT_CD,
1096 X_PRIOR_DEGREE,
1097 X_PRIOR_POST_GRAD,
1098 X_OLD_UNIT_CD,
1099 X_OLD_EFTSU,
1100 X_OLD_HECS_PREXMT_EXIE,
1101 X_OLD_HECS_AMOUNT_PAID,
1102 X_OLD_HECS_PAYMENT_OPTION,
1103 X_OLD_DIFFERENTIAL_HECS_IND,
1104 X_OLD_CITIZENSHIP_CD,
1105 X_OLD_PERM_RESIDENT_CD,
1106 X_OLD_PRIOR_DEGREE,
1107 X_OLD_PRIOR_POST_GRAD,
1108 X_REPORTED_IND,
1109 X_MODE);
1110 return;
1111 end if;
1112 close c1;
1113 UPDATE_ROW (
1114 X_ROWID,
1115 X_SUBMISSION_YR,
1116 X_SUBMISSION_NUMBER,
1117 X_PERSON_ID,
1118 X_COURSE_CD,
1119 X_SEQUENCE_NUMBER,
1120 X_VERSION_NUMBER,
1121 X_CHANGED_UPDATE_WHO,
1122 X_CHANGED_UPDATE_ON,
1123 X_GOVT_SEMESTER,
1124 X_UNIT_CD,
1125 X_EFTSU,
1126 X_HECS_PREXMT_EXIE,
1127 X_HECS_AMOUNT_PAID,
1128 X_HECS_PAYMENT_OPTION,
1129 X_DIFFERENTIAL_HECS_IND,
1130 X_CITIZENSHIP_CD,
1131 X_PERM_RESIDENT_CD,
1132 X_PRIOR_DEGREE,
1133 X_PRIOR_POST_GRAD,
1134 X_OLD_UNIT_CD,
1135 X_OLD_EFTSU,
1136 X_OLD_HECS_PREXMT_EXIE,
1137 X_OLD_HECS_AMOUNT_PAID,
1138 X_OLD_HECS_PAYMENT_OPTION,
1139 X_OLD_DIFFERENTIAL_HECS_IND,
1140 X_OLD_CITIZENSHIP_CD,
1141 X_OLD_PERM_RESIDENT_CD,
1142 X_OLD_PRIOR_DEGREE,
1143 X_OLD_PRIOR_POST_GRAD,
1144 X_REPORTED_IND,
1145 X_MODE);
1146 end ADD_ROW;
1147
1148 procedure DELETE_ROW (
1149 X_ROWID in VARCHAR2
1150 ) as
1151 begin
1152 Before_DML (
1153 p_action => 'DELETE',
1154 x_rowid => X_ROWID
1155 );
1156 delete from IGS_ST_GVT_SPSHT_CHG
1157 where ROWID = X_ROWID;
1158 if (sql%notfound) then
1159 raise no_data_found;
1160 end if;
1161 After_DML (
1162 p_action => 'DELETE',
1163 x_rowid => X_ROWID
1164 );
1165 end DELETE_ROW;
1166
1167 end IGS_ST_GVT_SPSHT_CHG_PKG;