DBA Data[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;