[Home] [Help]
PACKAGE BODY: APPS.IGS_ST_GVT_STDNT_LBL_PKG
Source
1 package body IGS_ST_GVT_STDNT_LBL_PKG as
2 /* $Header: IGSVI11B.pls 115.3 2002/11/29 04:33:33 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_ST_GVT_STDNT_LBL%RowType;
6 new_references IGS_ST_GVT_STDNT_LBL%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_govt_semester IN NUMBER DEFAULT NULL,
17 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
18 x_govt_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
19 x_total_eftsu IN NUMBER DEFAULT NULL,
20 x_industrial_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_tuition_fee IN NUMBER DEFAULT NULL,
24 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
25 x_birth_dt IN DATE DEFAULT NULL,
26 x_sex IN VARCHAR2 DEFAULT NULL,
27 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
28 x_govt_citizenship_cd IN NUMBER DEFAULT NULL,
29 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
30 x_govt_perm_resident_cd IN NUMBER DEFAULT NULL,
31 x_commencement_dt IN DATE DEFAULT NULL,
32 x_creation_date IN DATE DEFAULT NULL,
33 x_created_by IN NUMBER DEFAULT NULL,
34 x_last_update_date IN DATE DEFAULT NULL,
35 x_last_updated_by IN NUMBER DEFAULT NULL,
36 x_last_update_login IN NUMBER DEFAULT NULL
37 ) AS
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGS_ST_GVT_STDNT_LBL
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 Open cur_old_ref_values;
51 Fetch cur_old_ref_values INTO old_references;
52 IF (cur_old_ref_values%NOTFOUND) AND p_action NOT IN ('INSERT','VALIDATE_INSERT') THEN
53 Close cur_old_ref_values;
54 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 Return;
58 END IF;
59 Close cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.submission_yr := x_submission_yr;
63 new_references.submission_number := x_submission_number;
64 new_references.person_id := x_person_id;
65 new_references.course_cd := x_course_cd;
66 new_references.version_number := x_version_number;
67 new_references.govt_semester := x_govt_semester;
68 new_references.hecs_payment_option := x_hecs_payment_option;
69 new_references.govt_hecs_payment_option := x_govt_hecs_payment_option;
70 new_references.total_eftsu := x_total_eftsu;
71 new_references.industrial_eftsu := x_industrial_eftsu;
72 new_references.hecs_prexmt_exie := x_hecs_prexmt_exie;
73 new_references.hecs_amount_paid := x_hecs_amount_paid;
74 new_references.tuition_fee := x_tuition_fee;
75 new_references.differential_hecs_ind := x_differential_hecs_ind;
76 new_references.birth_dt := x_birth_dt;
77 new_references.sex := x_sex;
78 new_references.citizenship_cd := x_citizenship_cd;
79 new_references.govt_citizenship_cd := x_govt_citizenship_cd;
80 new_references.perm_resident_cd := x_perm_resident_cd;
81 new_references.govt_perm_resident_cd := x_govt_perm_resident_cd;
82 new_references.commencement_dt := x_commencement_dt;
83 IF (p_action = 'UPDATE') THEN
84 new_references.creation_date := old_references.creation_date;
85 new_references.created_by := old_references.created_by;
86 ELSE
87 new_references.creation_date := x_creation_date;
88 new_references.created_by := x_created_by;
89 END IF;
90 new_references.last_update_date := x_last_update_date;
91 new_references.last_updated_by := x_last_updated_by;
92 new_references.last_update_login := x_last_update_login;
93
94 END Set_Column_Values;
95
96 PROCEDURE BeforeRowInsertUpdateDelete1(
97 p_inserting IN BOOLEAN DEFAULT FALSE,
98 p_updating IN BOOLEAN DEFAULT FALSE,
99 p_deleting IN BOOLEAN DEFAULT FALSE
100 ) AS
101 v_message_name VARCHAR2(30);
102 v_transaction_type VARCHAR2(11);
103 v_submission_yr IGS_ST_GVT_STDNT_LBL.submission_yr%TYPE;
104 v_submission_number IGS_ST_GVT_STDNT_LBL.submission_number%TYPE;
105 BEGIN
106 IF p_inserting THEN
107 v_transaction_type := 'p_inserting';
108 v_submission_yr := new_references.submission_yr;
109 v_submission_number := new_references.submission_number;
110 ELSIF p_updating THEN
111 v_transaction_type := 'p_updating';
112 v_submission_yr := new_references.submission_yr;
113 v_submission_number := new_references.submission_number;
114 ELSIF p_deleting THEN
115 v_transaction_type := 'p_deleting';
116 v_submission_yr := old_references.submission_yr;
117 v_submission_number := old_references.submission_number;
118 END IF;
119 IF IGS_ST_VAL_GSE.stap_val_govt_snpsht (
120 v_submission_yr,
121 v_submission_number,
122 v_transaction_type,
123 v_message_name) = FALSE THEN
124 Fnd_Message.Set_Name('IGS', v_message_name);
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128
129 END BeforeRowInsertUpdateDelete1;
130
131 procedure Check_Constraints (
132 Column_Name IN VARCHAR2 DEFAULT NULL,
133 Column_Value IN VARCHAR2 DEFAULT NULL
134 )
135 AS
136 BEGIN
137 IF Column_Name is null then
138 NULL;
139 ELSIF upper(Column_Name) = 'TOTAL_EFTSU' then
140 new_references.total_eftsu := IGS_GE_NUMBER.to_num(column_value);
141 ELSIF upper(Column_Name) = 'HECS_PREXMT_EXIE' then
142 new_references.hecs_prexmt_exie := IGS_GE_NUMBER.to_num(column_value);
143 ELSIF upper(Column_Name) = 'TUITION_FEE' then
144 new_references.tuition_fee := IGS_GE_NUMBER.to_num(column_value);
145 ELSIF upper(Column_Name) = 'HECS_AMOUNT_PAID' then
146 new_references.hecs_amount_paid := IGS_GE_NUMBER.to_num(column_value);
147 ELSIF upper(Column_Name) = 'INDUSTRIAL_EFTSU' then
148 new_references.industrial_eftsu := IGS_GE_NUMBER.to_num(column_value);
149 ELSIF upper(Column_Name) = 'SUBMISSION_YR' then
150 new_references.submission_yr := IGS_GE_NUMBER.to_num(column_value);
151 ELSIF upper(Column_Name) = 'DIFFERENTIAL_HECS_IND' then
152 new_references.differential_hecs_ind := column_value;
153 END IF;
154
155 IF upper(Column_Name) = 'TOTAL_EFTSU' OR Column_Name IS NULL THEN
156 IF new_references.total_eftsu < 0000.000 OR new_references.total_eftsu > 9999.999 THEN
157 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
158 IGS_GE_MSG_STACK.ADD;
159 App_Exception.Raise_Exception;
160 END IF;
161 END IF;
162 IF upper(Column_Name) = 'HECS_PREXMT_EXIE' OR Column_Name IS NULL THEN
163 IF new_references.hecs_prexmt_exie < 0000 OR new_references.hecs_prexmt_exie > 9999 THEN
164 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF upper(Column_Name) = 'TUITION_FEE' OR Column_Name IS NULL THEN
171 IF new_references.tuition_fee < 00000 OR new_references.tuition_fee > 99999 THEN
172 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177 IF upper(Column_Name) = 'HECS_AMOUNT_PAID' OR Column_Name IS NULL THEN
178 IF new_references.hecs_amount_paid < 0000 OR new_references.hecs_amount_paid > 9999 THEN
179 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
180 IGS_GE_MSG_STACK.ADD;
181 App_Exception.Raise_Exception;
182 END IF;
183 END IF;
184 IF upper(Column_Name) = 'INDUSTRIAL_EFTSU' OR Column_Name IS NULL THEN
185 IF new_references.industrial_eftsu < 0000.000 OR new_references.industrial_eftsu > 9999.999 THEN
186 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
187 IGS_GE_MSG_STACK.ADD;
188 App_Exception.Raise_Exception;
189 END IF;
190 END IF;
191 IF upper(Column_Name) = 'SUBMISSION_YR' OR Column_Name IS NULL THEN
192 IF new_references.submission_yr < 0000 OR new_references.submission_yr > 9999 THEN
193 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
194 IGS_GE_MSG_STACK.ADD;
195 App_Exception.Raise_Exception;
196 END IF;
197 END IF;
198 IF upper(Column_Name) = 'DIFFERENTIAL_HECS_IND' OR Column_Name IS NULL THEN
199 IF new_references.differential_hecs_ind NOT IN ('Y','N') THEN
200 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
201 IGS_GE_MSG_STACK.ADD;
202 App_Exception.Raise_Exception;
203 END IF;
204 END IF;
205
206 END Check_Constraints;
207
208 PROCEDURE Check_Parent_Existance AS
209 BEGIN
210
211 IF (((old_references.submission_yr = new_references.submission_yr) AND
212 (old_references.submission_number = new_references.submission_number) AND
213 (old_references.govt_semester = new_references.govt_semester)) OR
214 ((new_references.submission_yr IS NULL) OR
215 (new_references.submission_number IS NULL) OR
216 (new_references.govt_semester IS NULL))) THEN
217 NULL;
218 ELSE
219 IF NOT IGS_ST_GOVT_SEMESTER_PKG.Get_PK_For_Validation (
220 new_references.submission_yr,
221 new_references.submission_number,
222 new_references.govt_semester
223 ) THEN
224 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229
230 END Check_Parent_Existance;
231
232 function Get_PK_For_Validation (
233 x_submission_yr IN NUMBER,
234 x_submission_number IN NUMBER,
235 x_person_id IN NUMBER,
236 x_course_cd IN VARCHAR2,
237 x_govt_semester IN NUMBER
238 )return BOOLEAN AS
239
240 CURSOR cur_rowid IS
241 SELECT rowid
242 FROM IGS_ST_GVT_STDNT_LBL
243 WHERE submission_yr = x_submission_yr
244 AND submission_number = x_submission_number
245 AND person_id = x_person_id
246 AND course_cd = x_course_cd
247 AND govt_semester = x_govt_semester
248 FOR UPDATE NOWAIT;
249
250 lv_rowid cur_rowid%RowType;
251
252 BEGIN
253
254 Open cur_rowid;
255 Fetch cur_rowid INTO lv_rowid;
256 IF (cur_rowid%FOUND) THEN
257 Close cur_rowid;
258 Return(TRUE);
259 ELSE
260 Close cur_rowid;
261 Return(FALSE);
262 END IF;
263
264 END Get_PK_For_Validation;
265
266 PROCEDURE GET_FK_IGS_ST_GOVT_SEMESTER (
267 x_submission_yr IN NUMBER,
268 x_submission_number IN NUMBER,
269 x_govt_semester IN NUMBER
270 ) AS
271
272 CURSOR cur_rowid IS
273 SELECT rowid
274 FROM IGS_ST_GVT_STDNT_LBL
275 WHERE submission_yr = x_submission_yr
276 AND submission_number = x_submission_number
277 AND govt_semester = x_govt_semester ;
278
279 lv_rowid cur_rowid%RowType;
280
281 BEGIN
282
283 Open cur_rowid;
284 Fetch cur_rowid INTO lv_rowid;
285 IF (cur_rowid%FOUND) THEN
286 Close cur_rowid;
287 Fnd_Message.Set_Name ('IGS', 'IGS_ST_GSLI_GSEM_FK');
288 IGS_GE_MSG_STACK.ADD;
289 App_Exception.Raise_Exception;
290 Return;
291 END IF;
292 Close cur_rowid;
293
294 END GET_FK_IGS_ST_GOVT_SEMESTER;
295
296 PROCEDURE Before_DML (
297 p_action IN VARCHAR2,
298 x_rowid IN VARCHAR2 DEFAULT NULL,
299 x_submission_yr IN NUMBER DEFAULT NULL,
300 x_submission_number IN NUMBER DEFAULT NULL,
301 x_person_id IN NUMBER DEFAULT NULL,
302 x_course_cd IN VARCHAR2 DEFAULT NULL,
303 x_version_number IN NUMBER DEFAULT NULL,
304 x_govt_semester IN NUMBER DEFAULT NULL,
305 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
306 x_govt_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
307 x_total_eftsu IN NUMBER DEFAULT NULL,
308 x_industrial_eftsu IN NUMBER DEFAULT NULL,
309 x_hecs_prexmt_exie IN NUMBER DEFAULT NULL,
310 x_hecs_amount_paid IN NUMBER DEFAULT NULL,
311 x_tuition_fee IN NUMBER DEFAULT NULL,
312 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
313 x_birth_dt IN DATE DEFAULT NULL,
314 x_sex IN VARCHAR2 DEFAULT NULL,
315 x_citizenship_cd IN VARCHAR2 DEFAULT NULL,
316 x_govt_citizenship_cd IN NUMBER DEFAULT NULL,
317 x_perm_resident_cd IN VARCHAR2 DEFAULT NULL,
318 x_govt_perm_resident_cd IN NUMBER DEFAULT NULL,
319 x_commencement_dt IN DATE DEFAULT NULL,
320 x_creation_date IN DATE DEFAULT NULL,
321 x_created_by IN NUMBER DEFAULT NULL,
322 x_last_update_date IN DATE DEFAULT NULL,
323 x_last_updated_by IN NUMBER DEFAULT NULL,
324 x_last_update_login IN NUMBER DEFAULT NULL
325 ) AS
326 BEGIN
327
328 Set_Column_Values (
329 p_action,
330 x_rowid,
331 x_submission_yr,
332 x_submission_number,
333 x_person_id,
334 x_course_cd,
335 x_version_number,
336 x_govt_semester,
337 x_hecs_payment_option,
338 x_govt_hecs_payment_option,
339 x_total_eftsu,
340 x_industrial_eftsu,
341 x_hecs_prexmt_exie,
342 x_hecs_amount_paid,
343 x_tuition_fee,
344 x_differential_hecs_ind,
345 x_birth_dt,
346 x_sex,
347 x_citizenship_cd,
348 x_govt_citizenship_cd,
349 x_perm_resident_cd,
350 x_govt_perm_resident_cd,
351 x_commencement_dt,
352 x_creation_date,
353 x_created_by,
354 x_last_update_date,
355 x_last_updated_by,
356 x_last_update_login
357 );
358
359 IF (p_action = 'INSERT') THEN
360 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
361 IF Get_PK_For_Validation (
362 new_references.submission_yr,
363 new_references.submission_number,
364 new_references.person_id,
365 new_references.course_cd,
366 new_references.govt_semester
367 ) THEN
368 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
369 IGS_GE_MSG_STACK.ADD;
370 App_Exception.Raise_Exception;
371 END IF;
372 Check_Constraints;
373 Check_Parent_Existance;
374 ELSIF (p_action = 'UPDATE') THEN
375 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
376 Check_Constraints;
377 Check_Parent_Existance;
378 ELSIF (p_action = 'DELETE') THEN
379 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
380 ELSIF (p_action = 'VALIDATE_INSERT') THEN
381 IF Get_PK_For_Validation (
382 new_references.submission_yr,
383 new_references.submission_number,
384 new_references.person_id,
385 new_references.course_cd,
386 new_references.govt_semester
387 ) THEN
388 Fnd_Message.Set_Name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
389 IGS_GE_MSG_STACK.ADD;
390 App_Exception.Raise_Exception;
391 END IF;
392 Check_Constraints;
393 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
394 Check_Constraints;
395 ELSIF (p_action = 'VALIDATE_DELETE') THEN
396 Null;
397 END IF;
398
399 END Before_DML;
400
401 PROCEDURE After_DML (
402 p_action IN VARCHAR2,
403 x_rowid IN VARCHAR2
404 ) AS
405 BEGIN
406 l_rowid := x_rowid;
407 END After_DML;
408
409 procedure INSERT_ROW (
410 X_ROWID in out NOCOPY VARCHAR2,
411 X_SUBMISSION_YR in NUMBER,
412 X_SUBMISSION_NUMBER in NUMBER,
413 X_PERSON_ID in NUMBER,
414 X_COURSE_CD in VARCHAR2,
415 X_GOVT_SEMESTER in NUMBER,
416 X_VERSION_NUMBER in NUMBER,
417 X_HECS_PAYMENT_OPTION in VARCHAR2,
418 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
419 X_TOTAL_EFTSU in NUMBER,
420 X_INDUSTRIAL_EFTSU in NUMBER,
421 X_HECS_PREXMT_EXIE in NUMBER,
422 X_HECS_AMOUNT_PAID in NUMBER,
423 X_TUITION_FEE in NUMBER,
424 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
425 X_BIRTH_DT in DATE,
426 X_SEX in VARCHAR2,
427 X_CITIZENSHIP_CD in VARCHAR2,
428 X_GOVT_CITIZENSHIP_CD in NUMBER,
429 X_PERM_RESIDENT_CD in VARCHAR2,
430 X_GOVT_PERM_RESIDENT_CD in NUMBER,
431 X_COMMENCEMENT_DT in DATE,
432 X_MODE in VARCHAR2 default 'R'
433 ) AS
434 cursor C is select ROWID from IGS_ST_GVT_STDNT_LBL
435 where SUBMISSION_YR = X_SUBMISSION_YR
436 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
437 and PERSON_ID = X_PERSON_ID
438 and COURSE_CD = X_COURSE_CD
439 and GOVT_SEMESTER = X_GOVT_SEMESTER;
440 X_LAST_UPDATE_DATE DATE;
441 X_LAST_UPDATED_BY NUMBER;
442 X_LAST_UPDATE_LOGIN NUMBER;
443 X_REQUEST_ID NUMBER;
444 X_PROGRAM_ID NUMBER;
445 X_PROGRAM_APPLICATION_ID NUMBER;
446 X_PROGRAM_UPDATE_DATE DATE;
447
448 begin
449 X_LAST_UPDATE_DATE := SYSDATE;
450 if(X_MODE = 'I') then
451 X_LAST_UPDATED_BY := 1;
452 X_LAST_UPDATE_LOGIN := 0;
453 elsif (X_MODE = 'R') then
454 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
455 if X_LAST_UPDATED_BY is NULL then
456 X_LAST_UPDATED_BY := -1;
457 end if;
458 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
459 if X_LAST_UPDATE_LOGIN is NULL then
460 X_LAST_UPDATE_LOGIN := -1;
461 end if;
462 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
463 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
464 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
465 if (X_REQUEST_ID = -1) then
466 X_REQUEST_ID := NULL;
467 X_PROGRAM_ID := NULL;
468 X_PROGRAM_APPLICATION_ID := NULL;
469 X_PROGRAM_UPDATE_DATE := NULL;
470 else
471 X_PROGRAM_UPDATE_DATE := SYSDATE;
472 end if;
473 else
474 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
475 IGS_GE_MSG_STACK.ADD;
476 app_exception.raise_exception;
477 end if;
478 Before_DML (
479 p_action => 'INSERT',
480 x_rowid => X_ROWID,
481 x_submission_yr => X_SUBMISSION_YR,
482 x_submission_number => X_SUBMISSION_NUMBER,
483 x_person_id => X_PERSON_ID,
484 x_course_cd => X_COURSE_CD,
485 x_version_number => X_VERSION_NUMBER,
486 x_govt_semester => X_GOVT_SEMESTER,
487 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
488 x_govt_hecs_payment_option => X_GOVT_HECS_PAYMENT_OPTION,
489 x_total_eftsu => X_TOTAL_EFTSU,
490 x_industrial_eftsu => X_INDUSTRIAL_EFTSU,
491 x_hecs_prexmt_exie => X_HECS_PREXMT_EXIE,
492 x_hecs_amount_paid => X_HECS_AMOUNT_PAID,
493 x_tuition_fee => X_TUITION_FEE,
494 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
495 x_birth_dt => X_BIRTH_DT,
496 x_sex => X_SEX,
497 x_citizenship_cd => X_CITIZENSHIP_CD,
498 x_govt_citizenship_cd => X_GOVT_CITIZENSHIP_CD,
499 x_perm_resident_cd => X_PERM_RESIDENT_CD,
500 x_govt_perm_resident_cd => X_GOVT_PERM_RESIDENT_CD,
501 x_commencement_dt => X_COMMENCEMENT_DT,
502 x_creation_date => X_LAST_UPDATE_DATE,
503 x_created_by => X_LAST_UPDATED_BY,
504 x_last_update_date => X_LAST_UPDATE_DATE,
505 x_last_updated_by => X_LAST_UPDATED_BY,
506 x_last_update_login => X_LAST_UPDATE_LOGIN
507 );
508
509 insert into IGS_ST_GVT_STDNT_LBL (
510 SUBMISSION_YR,
511 SUBMISSION_NUMBER,
512 PERSON_ID,
513 COURSE_CD,
514 VERSION_NUMBER,
515 GOVT_SEMESTER,
516 HECS_PAYMENT_OPTION,
517 GOVT_HECS_PAYMENT_OPTION,
518 TOTAL_EFTSU,
519 INDUSTRIAL_EFTSU,
520 HECS_PREXMT_EXIE,
521 HECS_AMOUNT_PAID,
522 TUITION_FEE,
523 DIFFERENTIAL_HECS_IND,
524 BIRTH_DT,
525 SEX,
526 CITIZENSHIP_CD,
527 GOVT_CITIZENSHIP_CD,
528 PERM_RESIDENT_CD,
529 GOVT_PERM_RESIDENT_CD,
530 COMMENCEMENT_DT,
531 CREATION_DATE,
532 CREATED_BY,
533 LAST_UPDATE_DATE,
534 LAST_UPDATED_BY,
535 LAST_UPDATE_LOGIN,
536 REQUEST_ID,
537 PROGRAM_ID,
538 PROGRAM_APPLICATION_ID,
539 PROGRAM_UPDATE_DATE
540 ) values (
541 NEW_REFERENCES.SUBMISSION_YR,
542 NEW_REFERENCES.SUBMISSION_NUMBER,
543 NEW_REFERENCES.PERSON_ID,
544 NEW_REFERENCES.COURSE_CD,
545 NEW_REFERENCES.VERSION_NUMBER,
546 NEW_REFERENCES.GOVT_SEMESTER,
547 NEW_REFERENCES.HECS_PAYMENT_OPTION,
548 NEW_REFERENCES.GOVT_HECS_PAYMENT_OPTION,
549 NEW_REFERENCES.TOTAL_EFTSU,
550 NEW_REFERENCES.INDUSTRIAL_EFTSU,
551 NEW_REFERENCES.HECS_PREXMT_EXIE,
552 NEW_REFERENCES.HECS_AMOUNT_PAID,
553 NEW_REFERENCES.TUITION_FEE,
554 NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
555 NEW_REFERENCES.BIRTH_DT,
556 NEW_REFERENCES.SEX,
557 NEW_REFERENCES.CITIZENSHIP_CD,
558 NEW_REFERENCES.GOVT_CITIZENSHIP_CD,
559 NEW_REFERENCES.PERM_RESIDENT_CD,
560 NEW_REFERENCES.GOVT_PERM_RESIDENT_CD,
561 NEW_REFERENCES.COMMENCEMENT_DT,
562 X_LAST_UPDATE_DATE,
563 X_LAST_UPDATED_BY,
564 X_LAST_UPDATE_DATE,
565 X_LAST_UPDATED_BY,
566 X_LAST_UPDATE_LOGIN,
567 X_REQUEST_ID,
568 X_PROGRAM_ID,
569 X_PROGRAM_APPLICATION_ID,
570 X_PROGRAM_UPDATE_DATE
571 );
572
573 open c;
574 fetch c into X_ROWID;
575 if (c%notfound) then
576 close c;
577 raise no_data_found;
578 end if;
579 close c;
580
581 After_DML (
582 p_action => 'INSERT',
583 x_rowid => X_ROWID
584 );
585 end INSERT_ROW;
586
587 procedure LOCK_ROW (
588 X_ROWID in VARCHAR2,
589 X_SUBMISSION_YR in NUMBER,
590 X_SUBMISSION_NUMBER in NUMBER,
591 X_PERSON_ID in NUMBER,
592 X_COURSE_CD in VARCHAR2,
593 X_GOVT_SEMESTER in NUMBER,
594 X_VERSION_NUMBER in NUMBER,
595 X_HECS_PAYMENT_OPTION in VARCHAR2,
596 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
597 X_TOTAL_EFTSU in NUMBER,
598 X_INDUSTRIAL_EFTSU in NUMBER,
599 X_HECS_PREXMT_EXIE in NUMBER,
600 X_HECS_AMOUNT_PAID in NUMBER,
601 X_TUITION_FEE in NUMBER,
602 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
603 X_BIRTH_DT in DATE,
604 X_SEX in VARCHAR2,
605 X_CITIZENSHIP_CD in VARCHAR2,
606 X_GOVT_CITIZENSHIP_CD in NUMBER,
607 X_PERM_RESIDENT_CD in VARCHAR2,
608 X_GOVT_PERM_RESIDENT_CD in NUMBER,
609 X_COMMENCEMENT_DT in DATE
610 ) AS
611 cursor c1 is select
612 VERSION_NUMBER,
613 HECS_PAYMENT_OPTION,
614 GOVT_HECS_PAYMENT_OPTION,
615 TOTAL_EFTSU,
616 INDUSTRIAL_EFTSU,
617 HECS_PREXMT_EXIE,
618 HECS_AMOUNT_PAID,
619 TUITION_FEE,
620 DIFFERENTIAL_HECS_IND,
621 BIRTH_DT,
622 SEX,
623 CITIZENSHIP_CD,
624 GOVT_CITIZENSHIP_CD,
625 PERM_RESIDENT_CD,
626 GOVT_PERM_RESIDENT_CD,
627 COMMENCEMENT_DT
628 from IGS_ST_GVT_STDNT_LBL
629 where ROWID = X_ROWID for update nowait;
630 tlinfo c1%rowtype;
631
632 begin
633 open c1;
634 fetch c1 into tlinfo;
635 if (c1%notfound) then
636 close c1;
637 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
638 IGS_GE_MSG_STACK.ADD;
639 app_exception.raise_exception;
640 return;
641 end if;
642 close c1;
643
644 if ( (tlinfo.VERSION_NUMBER = X_VERSION_NUMBER)
645 AND (tlinfo.HECS_PAYMENT_OPTION = X_HECS_PAYMENT_OPTION)
646 AND (tlinfo.GOVT_HECS_PAYMENT_OPTION = X_GOVT_HECS_PAYMENT_OPTION)
647 AND (tlinfo.TOTAL_EFTSU = X_TOTAL_EFTSU)
648 AND (tlinfo.INDUSTRIAL_EFTSU = X_INDUSTRIAL_EFTSU)
649 AND (tlinfo.HECS_PREXMT_EXIE = X_HECS_PREXMT_EXIE)
650 AND (tlinfo.HECS_AMOUNT_PAID = X_HECS_AMOUNT_PAID)
651 AND (tlinfo.TUITION_FEE = X_TUITION_FEE)
652 AND (tlinfo.DIFFERENTIAL_HECS_IND = X_DIFFERENTIAL_HECS_IND)
653 AND ((tlinfo.BIRTH_DT = X_BIRTH_DT)
654 OR ((tlinfo.BIRTH_DT is null)
655 AND (X_BIRTH_DT is null)))
656 AND (tlinfo.SEX = X_SEX)
657 AND (tlinfo.CITIZENSHIP_CD = X_CITIZENSHIP_CD)
658 AND (tlinfo.GOVT_CITIZENSHIP_CD = X_GOVT_CITIZENSHIP_CD)
659 AND (tlinfo.PERM_RESIDENT_CD = X_PERM_RESIDENT_CD)
660 AND (tlinfo.GOVT_PERM_RESIDENT_CD = X_GOVT_PERM_RESIDENT_CD)
661 AND (tlinfo.COMMENCEMENT_DT = X_COMMENCEMENT_DT)
662 ) then
663 null;
664 else
665 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
666 IGS_GE_MSG_STACK.ADD;
667 app_exception.raise_exception;
668 end if;
669 return;
670 end LOCK_ROW;
671
672 procedure UPDATE_ROW (
673 X_ROWID in VARCHAR2,
674 X_SUBMISSION_YR in NUMBER,
675 X_SUBMISSION_NUMBER in NUMBER,
676 X_PERSON_ID in NUMBER,
677 X_COURSE_CD in VARCHAR2,
678 X_GOVT_SEMESTER in NUMBER,
679 X_VERSION_NUMBER in NUMBER,
680 X_HECS_PAYMENT_OPTION in VARCHAR2,
681 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
682 X_TOTAL_EFTSU in NUMBER,
683 X_INDUSTRIAL_EFTSU in NUMBER,
684 X_HECS_PREXMT_EXIE in NUMBER,
685 X_HECS_AMOUNT_PAID in NUMBER,
686 X_TUITION_FEE in NUMBER,
687 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
688 X_BIRTH_DT in DATE,
689 X_SEX in VARCHAR2,
690 X_CITIZENSHIP_CD in VARCHAR2,
691 X_GOVT_CITIZENSHIP_CD in NUMBER,
692 X_PERM_RESIDENT_CD in VARCHAR2,
693 X_GOVT_PERM_RESIDENT_CD in NUMBER,
694 X_COMMENCEMENT_DT in DATE,
695 X_MODE in VARCHAR2 default 'R'
696 ) AS
697 X_LAST_UPDATE_DATE DATE;
698 X_LAST_UPDATED_BY NUMBER;
699 X_LAST_UPDATE_LOGIN NUMBER;
700 X_REQUEST_ID NUMBER;
701 X_PROGRAM_ID NUMBER;
702 X_PROGRAM_APPLICATION_ID NUMBER;
703 X_PROGRAM_UPDATE_DATE DATE;
704
705 begin
706 X_LAST_UPDATE_DATE := SYSDATE;
707 if(X_MODE = 'I') then
708 X_LAST_UPDATED_BY := 1;
709 X_LAST_UPDATE_LOGIN := 0;
710 elsif (X_MODE = 'R') then
711 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
712 if X_LAST_UPDATED_BY is NULL then
713 X_LAST_UPDATED_BY := -1;
714 end if;
715 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
716 if X_LAST_UPDATE_LOGIN is NULL then
717 X_LAST_UPDATE_LOGIN := -1;
718 end if;
719 else
720 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
721 IGS_GE_MSG_STACK.ADD;
722 app_exception.raise_exception;
723 end if;
724 Before_DML (
725 p_action => 'UPDATE',
726 x_rowid => X_ROWID,
727 x_submission_yr => X_SUBMISSION_YR,
728 x_submission_number => X_SUBMISSION_NUMBER,
729 x_person_id => X_PERSON_ID,
730 x_course_cd => X_COURSE_CD,
731 x_version_number => X_VERSION_NUMBER,
732 x_govt_semester => X_GOVT_SEMESTER,
733 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
734 x_govt_hecs_payment_option => X_GOVT_HECS_PAYMENT_OPTION,
735 x_total_eftsu => X_TOTAL_EFTSU,
736 x_industrial_eftsu => X_INDUSTRIAL_EFTSU,
737 x_hecs_prexmt_exie => X_HECS_PREXMT_EXIE,
738 x_hecs_amount_paid => X_HECS_AMOUNT_PAID,
739 x_tuition_fee => X_TUITION_FEE,
740 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
741 x_birth_dt => X_BIRTH_DT,
742 x_sex => X_SEX,
743 x_citizenship_cd => X_CITIZENSHIP_CD,
744 x_govt_citizenship_cd => X_GOVT_CITIZENSHIP_CD,
745 x_perm_resident_cd => X_PERM_RESIDENT_CD,
746 x_govt_perm_resident_cd => X_GOVT_PERM_RESIDENT_CD,
747 x_commencement_dt => X_COMMENCEMENT_DT,
748 x_creation_date => X_LAST_UPDATE_DATE,
749 x_created_by => X_LAST_UPDATED_BY,
750 x_last_update_date => X_LAST_UPDATE_DATE,
751 x_last_updated_by => X_LAST_UPDATED_BY,
752 x_last_update_login => X_LAST_UPDATE_LOGIN
753 );
754 if (X_MODE = 'R') then
755 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
756 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
757 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
758 if (X_REQUEST_ID = -1) then
759 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
760 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
761 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
762 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
763 else
764 X_PROGRAM_UPDATE_DATE := SYSDATE;
765 end if;
766 end if;
767
768 update IGS_ST_GVT_STDNT_LBL set
769 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
770 HECS_PAYMENT_OPTION = NEW_REFERENCES.HECS_PAYMENT_OPTION,
771 GOVT_HECS_PAYMENT_OPTION = NEW_REFERENCES.GOVT_HECS_PAYMENT_OPTION,
772 TOTAL_EFTSU = NEW_REFERENCES.TOTAL_EFTSU,
773 INDUSTRIAL_EFTSU = NEW_REFERENCES.INDUSTRIAL_EFTSU,
774 HECS_PREXMT_EXIE = NEW_REFERENCES.HECS_PREXMT_EXIE,
775 HECS_AMOUNT_PAID = NEW_REFERENCES.HECS_AMOUNT_PAID,
776 TUITION_FEE = NEW_REFERENCES.TUITION_FEE,
777 DIFFERENTIAL_HECS_IND = NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
778 BIRTH_DT = NEW_REFERENCES.BIRTH_DT,
779 SEX = NEW_REFERENCES.SEX,
780 CITIZENSHIP_CD = NEW_REFERENCES.CITIZENSHIP_CD,
781 GOVT_CITIZENSHIP_CD = NEW_REFERENCES.GOVT_CITIZENSHIP_CD,
782 PERM_RESIDENT_CD = NEW_REFERENCES.PERM_RESIDENT_CD,
783 GOVT_PERM_RESIDENT_CD = NEW_REFERENCES.GOVT_PERM_RESIDENT_CD,
784 COMMENCEMENT_DT = NEW_REFERENCES.COMMENCEMENT_DT,
785 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
786 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
787 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
788 REQUEST_ID = X_REQUEST_ID,
789 PROGRAM_ID = X_PROGRAM_ID,
790 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
791 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
792 where ROWID = X_ROWID
793 ;
794 if (sql%notfound) then
795 raise no_data_found;
796 end if;
797 After_DML (
798 p_action => 'UPDATE',
799 x_rowid => X_ROWID
800 );
801 end UPDATE_ROW;
802
803 procedure ADD_ROW (
804 X_ROWID in out NOCOPY VARCHAR2,
805 X_SUBMISSION_YR in NUMBER,
806 X_SUBMISSION_NUMBER in NUMBER,
807 X_PERSON_ID in NUMBER,
808 X_COURSE_CD in VARCHAR2,
809 X_GOVT_SEMESTER in NUMBER,
810 X_VERSION_NUMBER in NUMBER,
811 X_HECS_PAYMENT_OPTION in VARCHAR2,
812 X_GOVT_HECS_PAYMENT_OPTION in VARCHAR2,
813 X_TOTAL_EFTSU in NUMBER,
814 X_INDUSTRIAL_EFTSU in NUMBER,
815 X_HECS_PREXMT_EXIE in NUMBER,
816 X_HECS_AMOUNT_PAID in NUMBER,
817 X_TUITION_FEE in NUMBER,
818 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
819 X_BIRTH_DT in DATE,
820 X_SEX in VARCHAR2,
821 X_CITIZENSHIP_CD in VARCHAR2,
822 X_GOVT_CITIZENSHIP_CD in NUMBER,
823 X_PERM_RESIDENT_CD in VARCHAR2,
824 X_GOVT_PERM_RESIDENT_CD in NUMBER,
825 X_COMMENCEMENT_DT in DATE,
826 X_MODE in VARCHAR2 default 'R'
827 ) AS
828 cursor c1 is select rowid from IGS_ST_GVT_STDNT_LBL
829 where SUBMISSION_YR = X_SUBMISSION_YR
830 and SUBMISSION_NUMBER = X_SUBMISSION_NUMBER
831 and PERSON_ID = X_PERSON_ID
832 and COURSE_CD = X_COURSE_CD
833 and GOVT_SEMESTER = X_GOVT_SEMESTER
834 ;
835 begin
836 open c1;
837 fetch c1 into X_ROWID;
838 if (c1%notfound) then
839 close c1;
840 INSERT_ROW (
841 X_ROWID,
842 X_SUBMISSION_YR,
843 X_SUBMISSION_NUMBER,
844 X_PERSON_ID,
845 X_COURSE_CD,
846 X_GOVT_SEMESTER,
847 X_VERSION_NUMBER,
848 X_HECS_PAYMENT_OPTION,
849 X_GOVT_HECS_PAYMENT_OPTION,
850 X_TOTAL_EFTSU,
851 X_INDUSTRIAL_EFTSU,
852 X_HECS_PREXMT_EXIE,
853 X_HECS_AMOUNT_PAID,
854 X_TUITION_FEE,
855 X_DIFFERENTIAL_HECS_IND,
856 X_BIRTH_DT,
857 X_SEX,
858 X_CITIZENSHIP_CD,
859 X_GOVT_CITIZENSHIP_CD,
860 X_PERM_RESIDENT_CD,
861 X_GOVT_PERM_RESIDENT_CD,
862 X_COMMENCEMENT_DT,
863 X_MODE);
864 return;
865 end if;
866 close c1;
867 UPDATE_ROW (
868 X_ROWID,
869 X_SUBMISSION_YR,
870 X_SUBMISSION_NUMBER,
871 X_PERSON_ID,
872 X_COURSE_CD,
873 X_GOVT_SEMESTER,
874 X_VERSION_NUMBER,
875 X_HECS_PAYMENT_OPTION,
876 X_GOVT_HECS_PAYMENT_OPTION,
877 X_TOTAL_EFTSU,
878 X_INDUSTRIAL_EFTSU,
879 X_HECS_PREXMT_EXIE,
880 X_HECS_AMOUNT_PAID,
881 X_TUITION_FEE,
882 X_DIFFERENTIAL_HECS_IND,
883 X_BIRTH_DT,
884 X_SEX,
885 X_CITIZENSHIP_CD,
886 X_GOVT_CITIZENSHIP_CD,
887 X_PERM_RESIDENT_CD,
888 X_GOVT_PERM_RESIDENT_CD,
889 X_COMMENCEMENT_DT,
890 X_MODE);
891 end ADD_ROW;
892
893 procedure DELETE_ROW (
894 X_ROWID in VARCHAR2
895 ) AS
896 begin
897 Before_DML (
898 p_action => 'DELETE',
899 x_rowid => X_ROWID
900 );
901 delete from IGS_ST_GVT_STDNT_LBL
902 where ROWID = X_ROWID;
903 if (sql%notfound) then
904 raise no_data_found;
905 end if;
906 After_DML (
907 p_action => 'DELETE',
908 x_rowid => X_ROWID
909 );
910 end DELETE_ROW;
911
912 end IGS_ST_GVT_STDNT_LBL_PKG;