[Home] [Help]
PACKAGE BODY: APPS.IGS_EN_STDNTPSHECSOP_PKG
Source
1 package body IGS_EN_STDNTPSHECSOP_PKG AS
2 /* $Header: IGSEI17B.pls 115.4 2002/11/28 23:35:22 nsidana ship $ */
3 -------------------------------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --smadathi 28-AUG-2001 Bug No. 1956374 .The call to igs_en_val_scho.genp_val_strt_end_dt
7 -- is changed to igs_ad_val_edtl.genp_val_strt_end_dt
8 --smadathi 24-AUG-2001 Bug No. 1956374 .The call to igs_en_val_scho.genp_val_sdtt_sess
9 -- is changed to igs_as_val_suaap.genp_val_sdtt_sess
10 -------------------------------------------------------------------------------------------
11 l_rowid VARCHAR2(25);
12 old_references IGS_EN_STDNTPSHECSOP%RowType;
13 new_references IGS_EN_STDNTPSHECSOP%RowType;
14
15
16
17 PROCEDURE Set_Column_Values (
18 p_action IN VARCHAR2,
19 x_rowid IN VARCHAR2 DEFAULT NULL,
20 x_person_id IN NUMBER DEFAULT NULL,
21 x_course_cd IN VARCHAR2 DEFAULT NULL,
22 x_start_dt IN DATE DEFAULT NULL,
23 x_end_dt IN DATE DEFAULT NULL,
24 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
25 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
26 x_diff_hecs_ind_update_who IN VARCHAR2 DEFAULT NULL,
27 x_diff_hecs_ind_update_on IN DATE DEFAULT NULL,
28 x_outside_aus_res_ind IN VARCHAR2 DEFAULT NULL,
29 x_nz_citizen_ind IN VARCHAR2 DEFAULT NULL,
30 x_nz_citizen_less2yr_ind IN VARCHAR2 DEFAULT NULL,
31 x_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT NULL,
32 x_safety_net_ind IN VARCHAR2 DEFAULT NULL,
33 x_tax_file_number IN NUMBER DEFAULT NULL,
34 x_tax_file_number_collected_dt IN DATE DEFAULT NULL,
35 x_tax_file_invalid_dt IN DATE DEFAULT NULL,
36 x_tax_file_certificate_number IN NUMBER DEFAULT NULL,
37 x_diff_hecs_ind_update_comment IN VARCHAR2 DEFAULT NULL,
38 x_creation_date IN DATE DEFAULT NULL,
39 x_created_by IN NUMBER DEFAULT NULL,
40 x_last_update_date IN DATE DEFAULT NULL,
41 x_last_updated_by IN NUMBER DEFAULT NULL,
42 x_last_update_login IN NUMBER DEFAULT NULL
43 ) AS
44
45 CURSOR cur_old_ref_values IS
46 SELECT *
47 FROM IGS_EN_STDNTPSHECSOP
48 WHERE rowid = x_rowid;
49
50 BEGIN
51
52 l_rowid := x_rowid;
53
54 -- Code for setting the Old and New Reference Values.
55 -- Populate Old Values.
56 Open cur_old_ref_values;
57 Fetch cur_old_ref_values INTO old_references;
58 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
59 Close cur_old_ref_values;
60 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
61 IGS_GE_MSG_STACK.ADD;
62 App_Exception.Raise_Exception;
63 Return;
64 END IF;
65 Close cur_old_ref_values;
66
67 -- Populate New Values.
68 new_references.person_id := x_person_id;
69 new_references.course_cd := x_course_cd;
70 new_references.start_dt := x_start_dt;
71 new_references.end_dt := x_end_dt;
72 new_references.hecs_payment_option := x_hecs_payment_option;
73 new_references.differential_hecs_ind := x_differential_hecs_ind;
74 new_references.diff_hecs_ind_update_who := x_diff_hecs_ind_update_who;
75 new_references.diff_hecs_ind_update_on := x_diff_hecs_ind_update_on;
76 new_references.outside_aus_res_ind := x_outside_aus_res_ind;
77 new_references.nz_citizen_ind := x_nz_citizen_ind;
78 new_references.nz_citizen_less2yr_ind := x_nz_citizen_less2yr_ind;
79 new_references.nz_citizen_not_res_ind := x_nz_citizen_not_res_ind;
80 new_references.safety_net_ind := x_safety_net_ind;
81 new_references.tax_file_number := x_tax_file_number;
82 new_references.tax_file_number_collected_dt := x_tax_file_number_collected_dt;
83 new_references.tax_file_invalid_dt := x_tax_file_invalid_dt;
84 new_references.tax_file_certificate_number := x_tax_file_certificate_number;
85 new_references.diff_hecs_ind_update_comments := x_diff_hecs_ind_update_comment;
86 IF (p_action = 'UPDATE') THEN
87 new_references.creation_date := old_references.creation_date;
88 new_references.created_by := old_references.created_by;
89 ELSE
90 new_references.creation_date := x_creation_date;
91 new_references.created_by := x_created_by;
92 END IF;
93 new_references.last_update_date := x_last_update_date;
94 new_references.last_updated_by := x_last_updated_by;
95 new_references.last_update_login := x_last_update_login;
96
97 END Set_Column_Values;
98
99
100 -- Trigger description :-
101 -- "OSS_TST".trg_scho_br_iud
102 -- BEFORE INSERT OR DELETE OR UPDATE
103 -- ON IGS_EN_STDNTPSHECSOP
104 -- FOR EACH ROW
105
106 PROCEDURE BeforeRowInsertUpdateDelete1(
107 p_inserting IN BOOLEAN DEFAULT FALSE,
108 p_updating IN BOOLEAN DEFAULT FALSE,
109 p_deleting IN BOOLEAN DEFAULT FALSE
110 ) AS
111 v_message_name varchar2(30);
112 v_return_type VARCHAR2(1);
113 v_return_val IGS_PE_STD_TODO.sequence_number%TYPE;
114 cst_error CONSTANT VARCHAR2(1) := 'E';
115 BEGIN
116 -- IMPORTANT IGS_GE_NOTE!
117 -- If making any changes to functionality associated with
118 -- IGS_EN_STDNTPSHECSOP, consider if this should be replicated
119 -- in the validation associated with the merging of ID's.
120 -- Refer to enrp_val_ps_scho_mrg.
121 -- If trigger has not been disabled, perform required processing
122 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNTPSHECSOP') THEN
123 IF p_inserting THEN
124 -- Validate if the insert is allowed.
125 IF IGS_EN_VAL_SCHO.enrp_val_scho_insert (
126 new_references.person_id,
127 new_references.course_cd,
128 v_message_name) = FALSE THEN
129 fnd_message.set_name('IGS',v_message_name);
130 IGS_GE_MSG_STACK.ADD;
131 app_exception.raise_exception;
132
133 END IF;
134 -- Create an entry on the IGS_PE_STD_TODO table.
135 v_return_val := IGS_GE_GEN_003.GENP_INS_STDNT_TODO(new_references.person_id, 'FEE_RECALC', NULL,'Y');
136 END IF;
137 IF p_deleting THEN
138 -- Validate if the delete is allowed.
139 IF IGS_EN_VAL_SCHO.enrp_val_scho_trgdel (
140 old_references.person_id,
141 old_references.course_cd,
142 old_references.start_dt,
143 v_message_name) = FALSE THEN
144 fnd_message.set_name('IGS',v_message_name);
145 IGS_GE_MSG_STACK.ADD;
146 app_exception.raise_exception;
147 END IF;
148 END IF;
149 IF p_updating THEN
150 -- Validate if the update is allowed.
151 IF (NVL(old_references.differential_hecs_ind, 'NULL') <>
152 NVL(new_references.differential_hecs_ind, 'NULL') ) OR
153 (NVL(old_references.outside_aus_res_ind, 'NULL') <>
154 NVL(new_references.outside_aus_res_ind, 'NULL') ) OR
155 (NVL(old_references.nz_citizen_ind, 'NULL') <>
156 NVL(new_references.nz_citizen_ind, 'NULL') ) OR
157 (NVL(old_references.nz_citizen_less2yr_ind, 'NULL') <>
158 NVL(new_references.nz_citizen_less2yr_ind, 'NULL') ) OR
159 (NVL(old_references.nz_citizen_not_res_ind, 'NULL') <>
160 NVL(new_references.nz_citizen_not_res_ind, 'NULL') ) THEN
161 IF IGS_EN_VAL_SCHO.enrp_val_scho_update (
162 old_references.start_dt,
163 v_message_name) = FALSE THEN
164 fnd_message.set_name('IGS',v_message_name);
165 IGS_GE_MSG_STACK.ADD;
166 app_exception.raise_exception;
167 END IF;
168 END IF;
169 END IF;
170 IF p_inserting OR p_updating THEN
171 -- Set audit details.
172 --new_references.last_updated_by := USER;
173 --new_references.last_update_date := SYSDATE;
174 -- Validate START DATE AND END DATE.
175 -- Because start date is part of the key it will be set and is not
176 -- updateable, so only need to check the end date is not null.
177 IF new_references.end_dt IS NOT NULL AND
178 (p_inserting OR
179 NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
180 new_references.end_dt) THEN
181 IF igs_ad_val_edtl.genp_val_strt_end_dt (
182 new_references.start_dt,
183 new_references.end_dt,
184 v_message_name) = FALSE THEN
185 fnd_message.set_name('IGS',v_message_name);
186 IGS_GE_MSG_STACK.ADD;
187 app_exception.raise_exception;
188 END IF;
189 END IF;
190 -- Validate END DATE.
191 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
192 NVL(new_references.hecs_payment_option, 'NULL')) OR
193 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
194 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
195 new_references.end_dt >= TRUNC(SYSDATE)) THEN
196 IF IGS_EN_VAL_SCHO.enrp_val_scho_expire (
197 new_references.person_id,
198 new_references.course_cd,
199 new_references.start_dt,
200 new_references.end_dt,
201 new_references.hecs_payment_option,
202 v_message_name) = FALSE THEN
203 fnd_message.set_name('IGS',v_message_name);
204 IGS_GE_MSG_STACK.ADD;
205 app_exception.raise_exception;
206 END IF;
207 END IF;
208 -- Validate HECS PAYMENT OPTION closed indicator.
209 IF NVL(old_references.hecs_payment_option, 'NULL') <>
210 NVL(new_references.hecs_payment_option, 'NULL') THEN
211 IF IGS_EN_VAL_SCHO.enrp_val_hpo_closed (
212 new_references.hecs_payment_option,
213 v_message_name) = FALSE THEN
214 fnd_message.set_name('IGS',v_message_name);
215 IGS_GE_MSG_STACK.ADD;
216 app_exception.raise_exception;
217 END IF;
218 END IF;
219 -- Validate TAX FILE NUMBER INVALID DATE.
220 IF IGS_EN_VAL_SCHO.enrp_val_tfn_invalid (
221 new_references.tax_file_number,
222 new_references.tax_file_invalid_dt,
223 v_message_name) = FALSE THEN
224 fnd_message.set_name('IGS',v_message_name);
225 IGS_GE_MSG_STACK.ADD;
226 app_exception.raise_exception;
227 END IF;
228 -- Validate TAX FILE NUMBER CERTIFICATE NUMBER.
229 IF IGS_EN_VAL_SCHO.enrp_val_tfn_crtfct (
230 new_references.tax_file_number,
231 new_references.tax_file_invalid_dt,
232 new_references.tax_file_certificate_number,
233 v_message_name) = FALSE THEN
234 fnd_message.set_name('IGS',v_message_name);
235 IGS_GE_MSG_STACK.ADD;
236 app_exception.raise_exception;
237 END IF;
238 -- Validate the VISA INDICATORS.
239 IF (NVL(old_references.outside_aus_res_ind, 'NULL') <>
240 NVL(new_references.outside_aus_res_ind, 'NULL')) OR
241 (NVL(old_references.nz_citizen_ind, 'NULL') <>
242 NVL(new_references.nz_citizen_ind, 'NULL')) OR
243 (NVL(old_references.nz_citizen_less2yr_ind, 'NULL') <>
244 NVL(new_references.nz_citizen_less2yr_ind, 'NULL')) OR
245 (NVL(old_references.nz_citizen_not_res_ind, 'NULL') <>
246 NVL(new_references.nz_citizen_not_res_ind, 'NULL')) THEN
247 IF IGS_EN_VAL_SCHO.enrp_val_scho_visa (
248 new_references.outside_aus_res_ind,
249 new_references.nz_citizen_ind,
250 new_references.nz_citizen_less2yr_ind,
251 new_references.nz_citizen_not_res_ind,
252 v_message_name) = FALSE THEN
253 fnd_message.set_name('IGS',v_message_name);
254 IGS_GE_MSG_STACK.ADD;
255 app_exception.raise_exception;
256 END IF;
257 END IF;
258 -- Cross-table validations.
259 -- IGS_EN_STDNTPSHECSOP and IGS_PE_STATISTICS and IGS_PS_COURSE VERSION.
260 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
261 -- and the IGS_PS_COURSE type of the IGS_PS_COURSE version for the student IGS_PS_COURSE attempt.
262 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
263 NVL(new_references.hecs_payment_option, 'NULL')) THEN
264 IF IGS_EN_VAL_SCHO.enrp_val_hpo_crs_typ (
265 new_references.person_id,
266 new_references.course_cd,
267 new_references.hecs_payment_option,
268 v_message_name,
269 v_return_type) = FALSE THEN
270 IF v_return_type = cst_error THEN
271 fnd_message.set_name('IGS',v_message_name);
272 IGS_GE_MSG_STACK.ADD;
273 app_exception.raise_exception;
274 END IF;
275 END IF;
276 END IF;
277 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
278 -- and the special IGS_PS_COURSE type of the IGS_PS_COURSE version for the student IGS_PS_COURSE
279 -- attempt.
280 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
281 NVL(new_references.hecs_payment_option, 'NULL')) THEN
282 IF IGS_EN_VAL_SCHO.enrp_val_hpo_spc_crs (
283 new_references.person_id,
284 new_references.course_cd,
285 new_references.hecs_payment_option,
286 v_message_name,
287 v_return_type) = FALSE THEN
288 IF v_return_type = cst_error THEN
289 fnd_message.set_name('IGS',v_message_name);
290 IGS_GE_MSG_STACK.ADD;
291 app_exception.raise_exception;
292 END IF;
293 END IF;
294 END IF;
295 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
296 -- the IGS_PS_COURSE type of the IGS_PS_COURSE version for the student IGS_PS_COURSE attempt,
297 -- and the IGS_PE_PERSON statistics citizenship code.
298 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
299 NVL(new_references.hecs_payment_option, 'NULL')) OR
300 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
301 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
302 (new_references.end_dt >= TRUNC(SYSDATE) OR
303 new_references.end_dt IS NULL)) THEN
304 IF IGS_EN_VAL_SCHO.enrp_val_hpo_crs_cic (
305 new_references.person_id,
306 new_references.course_cd,
307 new_references.start_dt,
308 new_references.end_dt,
309 new_references.hecs_payment_option,
310 NULL,
311 NULL,
312 NULL,
313 v_message_name,
314 v_return_type) = FALSE THEN
315 IF v_return_type = cst_error THEN
316 fnd_message.set_name('IGS',v_message_name);
317 IGS_GE_MSG_STACK.ADD;
318 app_exception.raise_exception;
319 END IF;
320 END IF;
321 END IF;
322 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
323 -- and the IGS_PE_PERSON statistics citizenship code.
324 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
325 NVL(new_references.hecs_payment_option, 'NULL')) OR
326 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
327 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
328 (new_references.end_dt >= TRUNC(SYSDATE) OR
329 new_references.end_dt IS NULL)) THEN
330 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic (
331 new_references.person_id,
332 new_references.course_cd,
333 new_references.start_dt,
334 new_references.end_dt,
335 new_references.hecs_payment_option,
336 NULL,
337 NULL,
338 NULL,
339 v_message_name,
340 v_return_type) = FALSE THEN
341 IF v_return_type = cst_error THEN
342 fnd_message.set_name('IGS',v_message_name);
343 IGS_GE_MSG_STACK.ADD;
344 app_exception.raise_exception;
345 END IF;
346 END IF;
347 END IF;
348 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
349 -- and the IGS_PE_PERSON statistics citizenship code and permanent resident code.
350 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
351 NVL(new_references.hecs_payment_option, 'NULL')) OR
352 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
353 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
354 (new_references.end_dt >= TRUNC(SYSDATE) OR
355 new_references.end_dt IS NULL)) THEN
356 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic_prc (
357 new_references.person_id,
358 new_references.course_cd,
359 new_references.start_dt,
360 new_references.end_dt,
361 new_references.hecs_payment_option,
362 NULL,
363 NULL,
364 NULL,
365 NULL,
366 v_message_name,
367 v_return_type) = FALSE THEN
368 IF v_return_type = cst_error THEN
369 fnd_message.set_name('IGS',v_message_name);
370 IGS_GE_MSG_STACK.ADD;
371 app_exception.raise_exception;
372 END IF;
373 END IF;
374 END IF;
375 -- Validate the student IGS_PS_COURSE attempt HECS option visa indicators,
376 -- and the IGS_PE_PERSON statistics citizenship code and permanent resident code.
377 IF (NVL(old_references.outside_aus_res_ind, 'NULL') <>
378 NVL(new_references.outside_aus_res_ind, 'NULL')) OR
379 (NVL(old_references.nz_citizen_ind, 'NULL') <>
380 NVL(new_references.nz_citizen_ind, 'NULL')) OR
381 (NVL(old_references.nz_citizen_less2yr_ind, 'NULL') <>
382 NVL(new_references.nz_citizen_less2yr_ind, 'NULL')) OR
383 (NVL(old_references.nz_citizen_not_res_ind, 'NULL') <>
384 NVL(new_references.nz_citizen_not_res_ind, 'NULL')) OR
385 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
386 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
387 (new_references.end_dt >= TRUNC(SYSDATE) OR
388 new_references.end_dt IS NULL)) THEN
389 IF IGS_EN_VAL_SCHO.enrp_val_vis_cic_prc (
390 new_references.person_id,
391 new_references.course_cd,
392 new_references.start_dt,
393 new_references.end_dt,
394 new_references.outside_aus_res_ind,
395 new_references.nz_citizen_ind,
396 new_references.nz_citizen_less2yr_ind,
397 new_references.nz_citizen_not_res_ind,
398 NULL,
399 NULL,
400 NULL,
401 NULL,
402 v_message_name,
403 v_return_type) = FALSE THEN
404 IF v_return_type = cst_error THEN
405 fnd_message.set_name('IGS',v_message_name);
406 IGS_GE_MSG_STACK.ADD;
407 app_exception.raise_exception;
408 END IF;
409 END IF;
410 END IF;
411 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
412 -- the student IGS_PS_COURSE attempt HECS option visa indicators,
413 -- and the IGS_PE_PERSON statistics citizenship code and permanent resident code.
414 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
415 NVL(new_references.hecs_payment_option, 'NULL')) OR
416 (NVL(old_references.outside_aus_res_ind, 'NULL') <>
417 NVL(new_references.outside_aus_res_ind, 'NULL')) OR
418 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
419 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
420 (new_references.end_dt >= TRUNC(SYSDATE) OR
421 new_references.end_dt IS NULL)) THEN
422 IF IGS_EN_VAL_SCHO.enrp_val_ho_cic_prc (
423 new_references.person_id,
424 new_references.course_cd,
425 new_references.start_dt,
426 new_references.end_dt,
427 new_references.hecs_payment_option,
428 new_references.outside_aus_res_ind,
429 NULL,
430 NULL,
431 NULL,
432 NULL,
433 v_message_name,
434 v_return_type) = FALSE THEN
435 IF v_return_type = cst_error THEN
436 fnd_message.set_name('IGS',v_message_name);
437 IGS_GE_MSG_STACK.ADD;
438 app_exception.raise_exception;
439 END IF;
440 END IF;
441 END IF;
442 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
443 -- the student IGS_PS_COURSE attempt HECS option visa indicators,
444 -- and the IGS_PE_PERSON statistics citizenship code.
445 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
446 NVL(new_references.hecs_payment_option, 'NULL')) OR
447 (NVL(old_references.nz_citizen_ind, 'NULL') <>
448 NVL(new_references.nz_citizen_ind, 'NULL')) OR
449 (NVL(old_references.outside_aus_res_ind, 'NULL') <>
450 NVL(new_references.outside_aus_res_ind, 'NULL')) OR
451 (NVL(old_references.nz_citizen_less2yr_ind, 'NULL') <>
452 NVL(new_references.nz_citizen_less2yr_ind, 'NULL')) OR
453 (NVL(old_references.nz_citizen_not_res_ind, 'NULL') <>
454 NVL(new_references.nz_citizen_not_res_ind, 'NULL')) OR
455 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
456 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
457 (new_references.end_dt >= TRUNC(SYSDATE) OR
458 new_references.end_dt IS NULL)) THEN
459 IF IGS_EN_VAL_SCHO.enrp_val_hpo_vis_cic (
460 new_references.person_id,
461 new_references.course_cd,
462 new_references.start_dt,
463 new_references.end_dt,
464 new_references.hecs_payment_option,
465 new_references.outside_aus_res_ind,
466 new_references.nz_citizen_ind,
467 new_references.nz_citizen_less2yr_ind,
468 new_references.nz_citizen_not_res_ind,
469 NULL,
470 NULL,
471 NULL,
472 NULL,
473 v_message_name,
474 v_return_type) = FALSE THEN
475 IF v_return_type = cst_error THEN
476 fnd_message.set_name('IGS',v_message_name);
477 IGS_GE_MSG_STACK.ADD;
478 app_exception.raise_exception;
479 END IF;
480 END IF;
481 END IF;
482 -- Validate the student IGS_PS_COURSE attempt HECS option HECS payment option,
483 -- and the IGS_PE_PERSON statistics citizenship code and other IGS_PE_PERSON statistics
484 -- values including year of arrival and term IGS_AD_LOCATION country and postcode.
485 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
486 NVL(new_references.hecs_payment_option, 'NULL')) OR
487 (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
488 NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) AND
489 (new_references.end_dt >= TRUNC(SYSDATE) OR
490 new_references.end_dt IS NULL)) THEN
491 IF IGS_EN_VAL_SCHO.enrp_val_hpo_cic_ps (
492 new_references.person_id,
493 new_references.course_cd,
494 new_references.start_dt,
495 new_references.end_dt,
496 new_references.hecs_payment_option,
497 NULL,
498 NULL,
499 NULL,
500 NULL,
501 NULL,
502 NULL,
503 NULL,
504 v_message_name,
505 v_return_type) = FALSE THEN
506 IF v_return_type = cst_error THEN
507 fnd_message.set_name('IGS',v_message_name);
508 IGS_GE_MSG_STACK.ADD;
509 app_exception.raise_exception;
510 END IF;
511 END IF;
512 END IF;
513 END IF;
514 END IF;
515
516
517 END BeforeRowInsertUpdateDelete1;
518
519 -- Trigger description :-
520 -- "OSS_TST".trg_scho_ar_iu
521 -- AFTER INSERT OR UPDATE
522 -- ON IGS_EN_STDNTPSHECSOP
523 -- FOR EACH ROW
524
525 PROCEDURE AfterRowInsertUpdate2(
526 p_inserting IN BOOLEAN DEFAULT FALSE,
527 p_updating IN BOOLEAN DEFAULT FALSE,
528 p_deleting IN BOOLEAN DEFAULT FALSE
529 ) AS
530 v_message_name varchar2(30);
531 v_rowid_saved BOOLEAN := FALSE;
532 v_return_type VARCHAR2(1);
533 BEGIN
534 -- If trigger has not been disabled, perform required processing
535 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNTPSHECSOP') THEN
536 -- Validate for open ended student IGS_PS_COURSE HECS option records.
537 IF new_references.end_dt IS NULL THEN
538 v_rowid_saved := TRUE;
539 END IF;
540 -- Validate for date overlaps.
541 IF p_inserting OR (NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01')) <>
542 NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
543 IF v_rowid_saved = FALSE THEN
544 v_rowid_saved := TRUE;
545 END IF;
546 END IF;
547 -- Validate TAX FILE NUMBER.
548 IF (new_references.tax_file_number IS NOT NULL AND
549 NVL(old_references.tax_file_number,0) <> new_references.tax_file_number) THEN
550 IF v_rowid_saved = FALSE THEN
551 v_rowid_saved := TRUE;
552 END IF;
553 END IF;
554 -- Validate HECS PAYMENT OPTION and other fields.
555 IF (NVL(old_references.hecs_payment_option, 'NULL') <>
556 NVL(new_references.hecs_payment_option, 'NULL')) OR
557 (NVL(old_references.outside_aus_res_ind, 'NULL') <>
558 NVL(new_references.outside_aus_res_ind, 'NULL')) OR
559 (NVL(old_references.nz_citizen_ind, 'NULL') <>
560 NVL(new_references.nz_citizen_ind, 'NULL')) OR
561 (NVL(old_references.nz_citizen_less2yr_ind, 'NULL') <>
562 NVL(new_references.nz_citizen_less2yr_ind, 'NULL')) OR
563 (NVL(old_references.nz_citizen_not_res_ind, 'NULL') <>
564 NVL(new_references.nz_citizen_not_res_ind, 'NULL')) OR
565 (NVL(old_references.safety_net_ind, 'NULL') <>
566 NVL(new_references.safety_net_ind, 'NULL')) OR
567 (NVL(old_references.tax_file_number,0) <> NVL(new_references.tax_file_number,0)) OR
568 (NVL(old_references.tax_file_number_collected_dt,
569 IGS_GE_DATE.IGSDATE('1900/01/01')) <>
570 NVL(new_references.tax_file_number_collected_dt,
571 IGS_GE_DATE.IGSDATE('1900/01/01'))) OR
572 (NVL(old_references.tax_file_certificate_number,0) <>
573 NVL(new_references.tax_file_certificate_number,0)) THEN
574 IF v_rowid_saved = FALSE THEN
575 v_rowid_saved := TRUE;
576 -- Cannot call enrp_val_scho_hpo because tax file number is a parameter
577 -- and the form handles the update of tax file number in 2 update
578 -- statements.
579 END IF;
580 END IF;
581 END IF;
582
583 -- The following code has been added to validate the rows without causing mutaion
584 -- For all the 4 cases above where the rowid has been inserted the processing is done below
585 -- Dt: 8-Nov-99
586 IF v_rowid_saved = TRUE THEN
587 -- If trigger has not been disabled, perform required processing
588 IF igs_as_val_suaap.genp_val_sdtt_sess('IGS_EN_STDNTPSHECSOP') THEN
589 -- Validate for open ended IGS_EN_STDNTPSHECSOP records.
590 IF New_References.end_dt IS NULL THEN
591 IF IGS_EN_VAL_SCHO.enrp_val_scho_open (
592 New_References.person_id,
593 New_References.course_cd,
594 New_References.start_dt,
595 v_message_name) = FALSE THEN
596 fnd_message.set_name('IGS',v_message_name);
597 IGS_GE_MSG_STACK.ADD;
598 app_exception.raise_exception;
599 END IF;
600 END IF;
601 -- Validate IGS_EN_STDNTPSHECSOP date overlaps.
602 IF IGS_EN_VAL_SCHO.enrp_val_scho_ovrlp (
603 New_References.person_id,
604 New_References.course_cd,
605 New_References.start_dt,
606 New_References.end_dt,
607 v_message_name) = FALSE THEN
608 fnd_message.set_name('IGS',v_message_name);
609 IGS_GE_MSG_STACK.ADD;
610 app_exception.raise_exception;
611 END IF;
612 -- Validate TAX FILE NUMBER.
613 IF IGS_EN_VAL_SCHO.enrp_val_scho_tfn(
614 New_References.person_id,
615 New_References.course_cd,
616 New_References.start_dt,
617 New_References.tax_file_number,
618 v_message_name,
619 v_return_type) = FALSE THEN
620 IF v_return_type = cst_error THEN
621 fnd_message.set_name('IGS',v_message_name);
622 IGS_GE_MSG_STACK.ADD;
623 app_exception.raise_exception;
624 END IF;
625 END IF;
626 -- Validate HECS PAYMENT OPTION and other fields.
627 IF IGS_EN_VAL_SCHO.enrp_val_scho_hpo (
628 New_References.hecs_payment_option,
629 New_References.outside_aus_res_ind,
630 New_References.nz_citizen_ind,
631 New_References.nz_citizen_less2yr_ind,
632 New_References.nz_citizen_not_res_ind,
633 New_References.safety_net_ind,
634 New_References.tax_file_number,
635 New_References.tax_file_number_collected_dt,
636 New_References.tax_file_certificate_number,
637 New_References.differential_hecs_ind,
638 v_message_name,
639 v_return_type) = FALSE THEN
640 IF v_return_type = cst_error THEN
641 fnd_message.set_name('IGS',v_message_name);
642 IGS_GE_MSG_STACK.ADD;
643 app_exception.raise_exception;
644 END IF;
645 END IF;
646 END IF;
647 END IF;
648
649
650 END AfterRowInsertUpdate2;
651
652 procedure Check_constraints(
653 column_name IN VARCHAR2 DEFAULT NULL,
654 column_value IN VARCHAR2 DEFAULT NULL
655 ) AS
656 begin
657 If column_name is null then
658 NULL;
659 elsif upper(column_name) = 'DIFFERENTIAL_HECS_IND' then
660 new_references.differential_hecs_ind := column_value;
661 elsif upper(column_name) = 'OUTSIDE_AUS_RES_IND' then
662 new_references.outside_aus_res_ind := column_value;
663 elsif upper(column_name) = 'NZ_CITIZEN_IND' then
664 new_references.nz_citizen_ind := column_value;
665 elsif upper(column_name) = 'NZ_CITIZEN_LESS2YR_IND' then
666 new_references.nz_citizen_less2yr_ind := column_value;
667 elsif upper(column_name) = 'NZ_CITIZEN_NOT_RES_IND' then
668 new_references.nz_citizen_not_res_ind := column_value;
669 elsif upper(column_name) = 'SAFETY_NET_IND' then
670 new_references.safety_net_ind := column_value;
671 elsif upper(column_name) = 'TAX_FILE_CERTIFICATE_NUMBER' then
672 new_references.tax_file_certificate_number := igs_ge_number.to_num(column_value);
673 elsif upper(column_name) = 'COURSE_CD' then
674 new_references.course_cd := column_value;
675 elsif upper(column_name) = 'DIFF_HECS_IND_UPDATE_WHO' then
676 new_references.diff_hecs_ind_update_who := column_value;
677 elsif upper(column_name) = 'HECS_PAYMENT_OPTION' then
678 new_references.hecs_payment_option := column_value;
679 end if;
680
681 if upper(column_name) = 'DIFFERENTIAL_HECS_IND' OR
682 column_name is null then
683 if new_references.differential_hecs_ind not IN ('Y','N') OR
684 new_references.differential_hecs_ind <> upper(new_references.differential_hecs_ind) then
685 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
686 IGS_GE_MSG_STACK.ADD;
687 App_Exception.Raise_Exception;
688 end if;
689 end if;
690 if upper(column_name) = 'OUTSIDE_AUS_RES_IND' OR
691 column_name is null then
692 if new_references.outside_aus_res_ind not IN ('Y','N') OR
693 new_references.outside_aus_res_ind <> upper(new_references.outside_aus_res_ind) then
694 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
695 IGS_GE_MSG_STACK.ADD;
696 App_Exception.Raise_Exception;
697 end if;
698 end if;
699 if upper(column_name) = 'NZ_CITIZEN_IND' OR
700 column_name is null then
701 if new_references.nz_citizen_ind not IN ('Y','N') OR
702 new_references.nz_citizen_ind <> upper(new_references.nz_citizen_ind) then
703 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
704 IGS_GE_MSG_STACK.ADD;
705 App_Exception.Raise_Exception;
706 end if;
707 end if;
708 if upper(column_name) = 'NZ_CITIZEN_LESS2YR_IND' OR
709 column_name is null then
710 if new_references.nz_citizen_less2yr_ind not IN ('Y','N') OR
711 new_references.nz_citizen_less2yr_ind <> upper(new_references.nz_citizen_less2yr_ind) then
712 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
713 IGS_GE_MSG_STACK.ADD;
714 App_Exception.Raise_Exception;
715 end if;
716 end if;
717 if upper(column_name) = 'NZ_CITIZEN_NOT_RES_IND' OR
718 column_name is null then
719 if new_references.nz_citizen_not_res_ind not IN ('Y','N') OR
720 new_references.nz_citizen_not_res_ind <> upper(new_references.nz_citizen_not_res_ind) then
721 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
722 IGS_GE_MSG_STACK.ADD;
723 App_Exception.Raise_Exception;
724 end if;
725 end if;
726 if upper(column_name) = 'SAFETY_NET_IND' OR
727 column_name is null then
728 if new_references.safety_net_ind not IN ('Y','N') OR
729 new_references.safety_net_ind <> upper(new_references.safety_net_ind) then
730 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
731 IGS_GE_MSG_STACK.ADD;
732 App_Exception.Raise_Exception;
733 end if;
734 end if;
735 if upper(column_name) = 'TAX_FILE_CERTIFICATE_NUMBER' OR
736 column_name is null then
737 if new_references.tax_file_certificate_number < 0 OR
738 new_references.tax_file_certificate_number > 9999999999 then
739 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
740 IGS_GE_MSG_STACK.ADD;
741 App_Exception.Raise_Exception;
742 end if;
743 end if;
744 if upper(column_name) = 'COURSE_CD' OR
745 column_name is null then
746 if new_references.course_cd <> upper(new_references.course_cd) then
747 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
748 IGS_GE_MSG_STACK.ADD;
749 App_Exception.Raise_Exception;
750 end if;
751 end if;
752 if upper(column_name) = 'DIFF_HECS_IND_UPDATE_WHO' OR
753 column_name is null then
754 if new_references.diff_hecs_ind_update_who <>
755 upper(new_references.diff_hecs_ind_update_who) then
756 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
757 IGS_GE_MSG_STACK.ADD;
758 App_Exception.Raise_Exception;
759 end if;
760 end if;
761 if upper(column_name) = 'HECS_PAYMENT_OPTION' OR
762 column_name is null then
763 if new_references.hecs_payment_option <>upper(new_references.hecs_payment_option) then
764 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
765 IGS_GE_MSG_STACK.ADD;
766 App_Exception.Raise_Exception;
767 end if;
768 end if;
769 END check_constraints;
770 PROCEDURE Check_Parent_Existance AS
771 BEGIN
772
773 IF (((old_references.hecs_payment_option = new_references.hecs_payment_option)) OR
774 ((new_references.hecs_payment_option IS NULL))) THEN
775 NULL;
776 ELSE
777 IF NOT IGS_FI_HECS_PAY_OPTN_PKG.Get_PK_For_Validation (
778 new_references.hecs_payment_option
779 )then
780 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
781 IGS_GE_MSG_STACK.ADD;
782 App_Exception.Raise_Exception;
783 end if;
784 END IF;
785
786 IF (((old_references.person_id = new_references.person_id) AND
787 (old_references.course_cd = new_references.course_cd)) OR
788 ((new_references.person_id IS NULL) OR
789 (new_references.course_cd IS NULL))) THEN
790 NULL;
791 ELSE
792 IF NOT IGS_EN_STDNT_PS_ATT_PKG.Get_PK_For_Validation (
793 new_references.person_id,
794 new_references.course_cd
795 )then
796 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
797 IGS_GE_MSG_STACK.ADD;
798 App_Exception.Raise_Exception;
799 end if;
800 END IF;
801
802 END Check_Parent_Existance;
803
804 FUNCTION Get_PK_For_Validation (
805 x_person_id IN NUMBER,
806 x_course_cd IN VARCHAR2,
807 x_start_dt IN DATE
808 ) RETURN BOOLEAN AS
809
810 CURSOR cur_rowid IS
811 SELECT rowid
812 FROM IGS_EN_STDNTPSHECSOP
813 WHERE person_id = x_person_id
814 AND course_cd = x_course_cd
815 AND start_dt = x_start_dt
816 FOR UPDATE NOWAIT;
817
818 lv_rowid cur_rowid%RowType;
819
820 BEGIN
821
822 Open cur_rowid;
823 Fetch cur_rowid INTO lv_rowid;
824 IF (cur_rowid%FOUND) THEN
825 Close cur_rowid;
826 return(TRUE);
827 else
828 Close cur_rowid;
829 Return(FALSE);
830 END IF;
831
832 END Get_PK_For_Validation;
833
834 PROCEDURE GET_FK_IGS_FI_HECS_PAY_OPTN (
835 x_hecs_payment_option IN VARCHAR2
836 ) AS
837
838 CURSOR cur_rowid IS
839 SELECT rowid
840 FROM IGS_EN_STDNTPSHECSOP
841 WHERE hecs_payment_option = x_hecs_payment_option ;
842
843 lv_rowid cur_rowid%RowType;
844
845 BEGIN
846
847 Open cur_rowid;
848 Fetch cur_rowid INTO lv_rowid;
849 IF (cur_rowid%FOUND) THEN
850 Close cur_rowid;
851 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCHO_HPO_FK');
852 IGS_GE_MSG_STACK.ADD;
853 App_Exception.Raise_Exception;
854 Return;
855 END IF;
856 Close cur_rowid;
857
858 END GET_FK_IGS_FI_HECS_PAY_OPTN;
859
860 PROCEDURE GET_FK_IGS_EN_STDNT_PS_ATT (
861 x_person_id IN NUMBER,
862 x_course_cd IN VARCHAR2
863 ) AS
864
865 CURSOR cur_rowid IS
866 SELECT rowid
867 FROM IGS_EN_STDNTPSHECSOP
868 WHERE person_id = x_person_id
869 AND course_cd = x_course_cd ;
870
871 lv_rowid cur_rowid%RowType;
872
873 BEGIN
874
875 Open cur_rowid;
876 Fetch cur_rowid INTO lv_rowid;
877 IF (cur_rowid%FOUND) THEN
878 Close cur_rowid;
879 Fnd_Message.Set_Name ('IGS', 'IGS_EN_SCHO_SCA_FK');
880 IGS_GE_MSG_STACK.ADD;
881 App_Exception.Raise_Exception;
882 Return;
883 END IF;
884 Close cur_rowid;
885
886 END GET_FK_IGS_EN_STDNT_PS_ATT;
887
888 PROCEDURE Before_DML (
889 p_action IN VARCHAR2,
890 x_rowid IN VARCHAR2 DEFAULT NULL,
891 x_person_id IN NUMBER DEFAULT NULL,
892 x_course_cd IN VARCHAR2 DEFAULT NULL,
893 x_start_dt IN DATE DEFAULT NULL,
894 x_end_dt IN DATE DEFAULT NULL,
895 x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
896 x_differential_hecs_ind IN VARCHAR2 DEFAULT NULL,
897 x_diff_hecs_ind_update_who IN VARCHAR2 DEFAULT NULL,
898 x_diff_hecs_ind_update_on IN DATE DEFAULT NULL,
899 x_outside_aus_res_ind IN VARCHAR2 DEFAULT NULL,
900 x_nz_citizen_ind IN VARCHAR2 DEFAULT NULL,
901 x_nz_citizen_less2yr_ind IN VARCHAR2 DEFAULT NULL,
902 x_nz_citizen_not_res_ind IN VARCHAR2 DEFAULT NULL,
903 x_safety_net_ind IN VARCHAR2 DEFAULT NULL,
904 x_tax_file_number IN NUMBER DEFAULT NULL,
905 x_tax_file_number_collected_dt IN DATE DEFAULT NULL,
906 x_tax_file_invalid_dt IN DATE DEFAULT NULL,
907 x_tax_file_certificate_number IN NUMBER DEFAULT NULL,
908 x_diff_hecs_ind_update_comment IN VARCHAR2 DEFAULT NULL,
909 x_creation_date IN DATE DEFAULT NULL,
910 x_created_by IN NUMBER DEFAULT NULL,
911 x_last_update_date IN DATE DEFAULT NULL,
912 x_last_updated_by IN NUMBER DEFAULT NULL,
913 x_last_update_login IN NUMBER DEFAULT NULL
914 ) AS
915 BEGIN
916
917 Set_Column_Values (
918 p_action,
919 x_rowid,
920 x_person_id,
921 x_course_cd,
922 x_start_dt,
923 x_end_dt,
924 x_hecs_payment_option,
925 x_differential_hecs_ind,
926 x_diff_hecs_ind_update_who,
927 x_diff_hecs_ind_update_on,
928 x_outside_aus_res_ind,
929 x_nz_citizen_ind,
930 x_nz_citizen_less2yr_ind,
931 x_nz_citizen_not_res_ind,
932 x_safety_net_ind,
933 x_tax_file_number,
934 x_tax_file_number_collected_dt,
935 x_tax_file_invalid_dt,
936 x_tax_file_certificate_number,
937 x_diff_hecs_ind_update_comment,
938 x_creation_date,
939 x_created_by,
940 x_last_update_date,
941 x_last_updated_by,
942 x_last_update_login
943 );
944
945 IF (p_action = 'INSERT') THEN
946 -- Call all the procedures related to Before Insert.
947 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
948 IF get_pk_for_validation(
949 new_references.person_id,
950 new_references.course_cd,
951 new_references.start_dt
952 ) then
953 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
954 IGS_GE_MSG_STACK.ADD;
955 App_Exception.Raise_Exception;
956 end if;
957 Check_constraints;
958 Check_Parent_Existance;
959 ELSIF (p_action = 'UPDATE') THEN
960 -- Call all the procedures related to Before Update.
961 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
962 Check_constraints;
963 Check_Parent_Existance;
964 ELSIF (p_action = 'DELETE') THEN
965 -- Call all the procedures related to Before Delete.
966 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
967 ELSIF (p_action = 'VALIDATE_INSERT') then
968 IF get_pk_for_validation(
969 new_references.person_id,
970 new_references.course_cd,
971 new_references.start_dt
972 ) then
973 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
974 IGS_GE_MSG_STACK.ADD;
975 App_Exception.Raise_Exception;
976 end if;
977 Check_constraints;
978 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
979 Check_constraints;
980 ELSIF (p_action = 'VALIDATE_DELETE') THEN
981 null;
982 END IF;
983
984 END Before_DML;
985
986 PROCEDURE After_DML (
987 p_action IN VARCHAR2,
988 x_rowid IN VARCHAR2
989 ) AS
990 BEGIN
991
992 l_rowid := x_rowid;
993
994 IF (p_action = 'INSERT') THEN
995 -- Call all the procedures related to After Insert.
996 AfterRowInsertUpdate2 ( p_inserting => TRUE );
997 -- AfterStmtInsertUpdate3 ( p_inserting => TRUE );
998 ELSIF (p_action = 'UPDATE') THEN
999 -- Call all the procedures related to After Update.
1000 AfterRowInsertUpdate2 ( p_updating => TRUE );
1001 -- AfterStmtInsertUpdate3 ( p_updating => TRUE );
1002 ELSIF (p_action = 'DELETE') THEN
1003 -- Call all the procedures related to After Delete.
1004 Null;
1005 END IF;
1006
1007 END After_DML;
1008
1009
1010 procedure INSERT_ROW (
1011 X_ROWID in out NOCOPY VARCHAR2,
1012 X_PERSON_ID in NUMBER,
1013 X_COURSE_CD in VARCHAR2,
1014 X_START_DT in out NOCOPY DATE,
1015 X_END_DT in DATE,
1016 X_HECS_PAYMENT_OPTION in VARCHAR2,
1017 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
1018 X_DIFF_HECS_IND_UPDATE_WHO in VARCHAR2,
1019 X_DIFF_HECS_IND_UPDATE_ON in DATE,
1020 X_OUTSIDE_AUS_RES_IND in VARCHAR2,
1021 X_NZ_CITIZEN_IND in VARCHAR2,
1022 X_NZ_CITIZEN_LESS2YR_IND in VARCHAR2,
1023 X_NZ_CITIZEN_NOT_RES_IND in VARCHAR2,
1024 X_SAFETY_NET_IND in VARCHAR2,
1025 X_TAX_FILE_NUMBER in NUMBER,
1026 X_TAX_FILE_NUMBER_COLLECTED_DT in DATE,
1027 X_TAX_FILE_INVALID_DT in DATE,
1028 X_TAX_FILE_CERTIFICATE_NUMBER in NUMBER,
1029 X_DIFF_HECS_IND_UPDATE_COMMENT in VARCHAR2,
1030 X_MODE in VARCHAR2 default 'R'
1031 ) AS
1032 cursor C is select ROWID from IGS_EN_STDNTPSHECSOP
1033 where PERSON_ID = X_PERSON_ID
1034 and COURSE_CD = X_COURSE_CD
1035 and START_DT = NEW_REFERENCES.START_DT;
1036 X_LAST_UPDATE_DATE DATE;
1037 X_LAST_UPDATED_BY NUMBER;
1038 X_LAST_UPDATE_LOGIN NUMBER;
1039
1040 X_REQUEST_ID NUMBER;
1041 X_PROGRAM_ID NUMBER;
1042 X_PROGRAM_APPLICATION_ID NUMBER;
1043 X_PROGRAM_UPDATE_DATE DATE;
1044
1045 begin
1046 X_LAST_UPDATE_DATE := SYSDATE;
1047 if(X_MODE = 'I') then
1048 X_LAST_UPDATED_BY := 1;
1049 X_LAST_UPDATE_LOGIN := 0;
1050 elsif (X_MODE = 'R') then
1051
1052 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1053 if X_LAST_UPDATED_BY is NULL then
1054 X_LAST_UPDATED_BY := -1;
1055 end if;
1056 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1057 if X_LAST_UPDATE_LOGIN is NULL then
1058 X_LAST_UPDATE_LOGIN := -1;
1059 end if;
1060
1061 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1062 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1063 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1064
1065 if (X_REQUEST_ID = -1) then
1066 X_REQUEST_ID := NULL;
1067 X_PROGRAM_ID := NULL;
1068 X_PROGRAM_APPLICATION_ID := NULL;
1069 X_PROGRAM_UPDATE_DATE := NULL;
1070 else
1071 X_PROGRAM_UPDATE_DATE := SYSDATE;
1072 end if;
1073
1074 else
1075 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1076 IGS_GE_MSG_STACK.ADD;
1077 app_exception.raise_exception;
1078 end if;
1079
1080 Before_DML(
1081 p_action => 'INSERT',
1082 x_rowid => X_ROWID,
1083 x_person_id => X_PERSON_ID,
1084 x_course_cd => X_COURSE_CD,
1085 x_start_dt => X_START_DT,
1086 x_end_dt => X_END_DT,
1087 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
1088 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
1089 x_diff_hecs_ind_update_who => X_DIFF_HECS_IND_UPDATE_WHO,
1090 x_diff_hecs_ind_update_on => X_DIFF_HECS_IND_UPDATE_ON,
1091 x_outside_aus_res_ind => X_OUTSIDE_AUS_RES_IND,
1092 x_nz_citizen_ind => X_NZ_CITIZEN_IND,
1093 x_nz_citizen_less2yr_ind => X_NZ_CITIZEN_LESS2YR_IND,
1094 x_nz_citizen_not_res_ind => X_NZ_CITIZEN_NOT_RES_IND,
1095 x_safety_net_ind => X_SAFETY_NET_IND,
1096 x_tax_file_number => X_TAX_FILE_NUMBER,
1097 x_tax_file_number_collected_dt => X_TAX_FILE_NUMBER_COLLECTED_DT,
1098 x_tax_file_invalid_dt => X_TAX_FILE_INVALID_DT,
1099 x_tax_file_certificate_number => X_TAX_FILE_CERTIFICATE_NUMBER,
1100 x_diff_hecs_ind_update_comment => X_DIFF_HECS_IND_UPDATE_COMMENT,
1101 x_creation_date => X_LAST_UPDATE_DATE,
1102 x_created_by => X_LAST_UPDATED_BY,
1103 x_last_update_date =>X_LAST_UPDATE_DATE,
1104 x_last_updated_by => X_LAST_UPDATED_BY,
1105 x_last_update_login => X_LAST_UPDATE_LOGIN
1106 );
1107
1108 insert into IGS_EN_STDNTPSHECSOP (
1109 PERSON_ID,
1110 COURSE_CD,
1111 START_DT,
1112 END_DT,
1113 HECS_PAYMENT_OPTION,
1114 DIFFERENTIAL_HECS_IND,
1115 DIFF_HECS_IND_UPDATE_WHO,
1116 DIFF_HECS_IND_UPDATE_ON,
1117 OUTSIDE_AUS_RES_IND,
1118 NZ_CITIZEN_IND,
1119 NZ_CITIZEN_LESS2YR_IND,
1120 NZ_CITIZEN_NOT_RES_IND,
1121 SAFETY_NET_IND,
1122 TAX_FILE_NUMBER,
1123 TAX_FILE_NUMBER_COLLECTED_DT,
1124 TAX_FILE_INVALID_DT,
1125 TAX_FILE_CERTIFICATE_NUMBER,
1126 DIFF_HECS_IND_UPDATE_COMMENTS,
1127 CREATION_DATE,
1128 CREATED_BY,
1129 LAST_UPDATE_DATE,
1130 LAST_UPDATED_BY,
1131 LAST_UPDATE_LOGIN,
1132 REQUEST_ID,
1133 PROGRAM_ID,
1134 PROGRAM_APPLICATION_ID,
1135 PROGRAM_UPDATE_DATE
1136 ) values (
1137 NEW_REFERENCES.PERSON_ID,
1138 NEW_REFERENCES.COURSE_CD,
1139 NEW_REFERENCES.START_DT,
1140 NEW_REFERENCES.END_DT,
1141 NEW_REFERENCES.HECS_PAYMENT_OPTION,
1142 NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
1143 NEW_REFERENCES.DIFF_HECS_IND_UPDATE_WHO,
1144 NEW_REFERENCES.DIFF_HECS_IND_UPDATE_ON,
1145 NEW_REFERENCES.OUTSIDE_AUS_RES_IND,
1146 NEW_REFERENCES.NZ_CITIZEN_IND,
1147 NEW_REFERENCES.NZ_CITIZEN_LESS2YR_IND,
1148 NEW_REFERENCES.NZ_CITIZEN_NOT_RES_IND,
1149 NEW_REFERENCES.SAFETY_NET_IND,
1150 NEW_REFERENCES.TAX_FILE_NUMBER,
1151 NEW_REFERENCES.TAX_FILE_NUMBER_COLLECTED_DT,
1152 NEW_REFERENCES.TAX_FILE_INVALID_DT,
1153 NEW_REFERENCES.TAX_FILE_CERTIFICATE_NUMBER,
1154 NEW_REFERENCES.DIFF_HECS_IND_UPDATE_COMMENTS,
1155 X_LAST_UPDATE_DATE,
1156 X_LAST_UPDATED_BY,
1157 X_LAST_UPDATE_DATE,
1158 X_LAST_UPDATED_BY,
1159 X_LAST_UPDATE_LOGIN,
1160 X_REQUEST_ID,
1161 X_PROGRAM_ID,
1162 X_PROGRAM_APPLICATION_ID,
1163 X_PROGRAM_UPDATE_DATE
1164 );
1165
1166 open c;
1167 fetch c into X_ROWID;
1168 if (c%notfound) then
1169 close c;
1170 raise no_data_found;
1171 end if;
1172 close c;
1173
1174 After_DML(
1175 p_action => 'INSERT',
1176 x_rowid => X_ROWID
1177 );
1178
1179 end INSERT_ROW;
1180
1181 procedure LOCK_ROW (
1182 X_ROWID in VARCHAR2,
1183 X_PERSON_ID in NUMBER,
1184 X_COURSE_CD in VARCHAR2,
1185 X_START_DT in DATE,
1186 X_END_DT in DATE,
1187 X_HECS_PAYMENT_OPTION in VARCHAR2,
1188 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
1189 X_DIFF_HECS_IND_UPDATE_WHO in VARCHAR2,
1190 X_DIFF_HECS_IND_UPDATE_ON in DATE,
1191 X_OUTSIDE_AUS_RES_IND in VARCHAR2,
1192 X_NZ_CITIZEN_IND in VARCHAR2,
1193 X_NZ_CITIZEN_LESS2YR_IND in VARCHAR2,
1194 X_NZ_CITIZEN_NOT_RES_IND in VARCHAR2,
1195 X_SAFETY_NET_IND in VARCHAR2,
1196 X_TAX_FILE_NUMBER in NUMBER,
1197 X_TAX_FILE_NUMBER_COLLECTED_DT in DATE,
1198 X_TAX_FILE_INVALID_DT in DATE,
1199 X_TAX_FILE_CERTIFICATE_NUMBER in NUMBER,
1200 X_DIFF_HECS_IND_UPDATE_COMMENT in VARCHAR2
1201 ) AS
1202 cursor c1 is select
1203 END_DT,
1204 HECS_PAYMENT_OPTION,
1205 DIFFERENTIAL_HECS_IND,
1206 DIFF_HECS_IND_UPDATE_WHO,
1207 DIFF_HECS_IND_UPDATE_ON,
1208 OUTSIDE_AUS_RES_IND,
1209 NZ_CITIZEN_IND,
1210 NZ_CITIZEN_LESS2YR_IND,
1211 NZ_CITIZEN_NOT_RES_IND,
1212 SAFETY_NET_IND,
1213 TAX_FILE_NUMBER,
1214 TAX_FILE_NUMBER_COLLECTED_DT,
1215 TAX_FILE_INVALID_DT,
1216 TAX_FILE_CERTIFICATE_NUMBER,
1217 DIFF_HECS_IND_UPDATE_COMMENTS
1218 from IGS_EN_STDNTPSHECSOP
1219 where ROWID = X_ROWID
1220 for update nowait;
1221 tlinfo c1%rowtype;
1222
1223 begin
1224 open c1;
1225 fetch c1 into tlinfo;
1226 if (c1%notfound) then
1227 close c1;
1228 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1229 IGS_GE_MSG_STACK.ADD;
1230 app_exception.raise_exception;
1231 return;
1232 end if;
1233 close c1;
1234
1235 if ( ((tlinfo.END_DT = X_END_DT)
1236 OR ((tlinfo.END_DT is null)
1237 AND (X_END_DT is null)))
1238 AND (tlinfo.HECS_PAYMENT_OPTION = X_HECS_PAYMENT_OPTION)
1239 AND (tlinfo.DIFFERENTIAL_HECS_IND = X_DIFFERENTIAL_HECS_IND)
1240 AND ((tlinfo.DIFF_HECS_IND_UPDATE_WHO = X_DIFF_HECS_IND_UPDATE_WHO)
1241 OR ((tlinfo.DIFF_HECS_IND_UPDATE_WHO is null)
1242 AND (X_DIFF_HECS_IND_UPDATE_WHO is null)))
1243 AND ((tlinfo.DIFF_HECS_IND_UPDATE_ON = X_DIFF_HECS_IND_UPDATE_ON)
1244 OR ((tlinfo.DIFF_HECS_IND_UPDATE_ON is null)
1245 AND (X_DIFF_HECS_IND_UPDATE_ON is null)))
1246 AND (tlinfo.OUTSIDE_AUS_RES_IND = X_OUTSIDE_AUS_RES_IND)
1247 AND (tlinfo.NZ_CITIZEN_IND = X_NZ_CITIZEN_IND)
1248 AND (tlinfo.NZ_CITIZEN_LESS2YR_IND = X_NZ_CITIZEN_LESS2YR_IND)
1249 AND (tlinfo.NZ_CITIZEN_NOT_RES_IND = X_NZ_CITIZEN_NOT_RES_IND)
1250 AND (tlinfo.SAFETY_NET_IND = X_SAFETY_NET_IND)
1251 AND ((tlinfo.TAX_FILE_NUMBER = X_TAX_FILE_NUMBER)
1252 OR ((tlinfo.TAX_FILE_NUMBER is null)
1253 AND (X_TAX_FILE_NUMBER is null)))
1254 AND ((tlinfo.TAX_FILE_NUMBER_COLLECTED_DT = X_TAX_FILE_NUMBER_COLLECTED_DT)
1255 OR ((tlinfo.TAX_FILE_NUMBER_COLLECTED_DT is null)
1256 AND (X_TAX_FILE_NUMBER_COLLECTED_DT is null)))
1257 AND ((tlinfo.TAX_FILE_INVALID_DT = X_TAX_FILE_INVALID_DT)
1258 OR ((tlinfo.TAX_FILE_INVALID_DT is null)
1259 AND (X_TAX_FILE_INVALID_DT is null)))
1260 AND ((tlinfo.TAX_FILE_CERTIFICATE_NUMBER = X_TAX_FILE_CERTIFICATE_NUMBER)
1261 OR ((tlinfo.TAX_FILE_CERTIFICATE_NUMBER is null)
1262 AND (X_TAX_FILE_CERTIFICATE_NUMBER is null)))
1263 AND ((tlinfo.DIFF_HECS_IND_UPDATE_COMMENTS = X_DIFF_HECS_IND_UPDATE_COMMENT)
1264 OR ((tlinfo.DIFF_HECS_IND_UPDATE_COMMENTS is null)
1265 AND (X_DIFF_HECS_IND_UPDATE_COMMENT is null)))
1266 ) then
1267 null;
1268 else
1269 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1270 IGS_GE_MSG_STACK.ADD;
1271 app_exception.raise_exception;
1272 end if;
1273 return;
1274 end LOCK_ROW;
1275
1276 procedure UPDATE_ROW (
1277 X_ROWID in VARCHAR2,
1278 X_PERSON_ID in NUMBER,
1279 X_COURSE_CD in VARCHAR2,
1280 X_START_DT in DATE,
1281 X_END_DT in DATE,
1282 X_HECS_PAYMENT_OPTION in VARCHAR2,
1283 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
1284 X_DIFF_HECS_IND_UPDATE_WHO in VARCHAR2,
1285 X_DIFF_HECS_IND_UPDATE_ON in DATE,
1286 X_OUTSIDE_AUS_RES_IND in VARCHAR2,
1287 X_NZ_CITIZEN_IND in VARCHAR2,
1288 X_NZ_CITIZEN_LESS2YR_IND in VARCHAR2,
1289 X_NZ_CITIZEN_NOT_RES_IND in VARCHAR2,
1290 X_SAFETY_NET_IND in VARCHAR2,
1291 X_TAX_FILE_NUMBER in NUMBER,
1292 X_TAX_FILE_NUMBER_COLLECTED_DT in DATE,
1293 X_TAX_FILE_INVALID_DT in DATE,
1294 X_TAX_FILE_CERTIFICATE_NUMBER in NUMBER,
1295 X_DIFF_HECS_IND_UPDATE_COMMENT in VARCHAR2,
1296 X_MODE in VARCHAR2 default 'R'
1297 ) AS
1298 X_LAST_UPDATE_DATE DATE;
1299 X_LAST_UPDATED_BY NUMBER;
1300 X_LAST_UPDATE_LOGIN NUMBER;
1301
1302 X_REQUEST_ID NUMBER;
1303 X_PROGRAM_ID NUMBER;
1304 X_PROGRAM_APPLICATION_ID NUMBER;
1305 X_PROGRAM_UPDATE_DATE DATE;
1306
1307 begin
1308 X_LAST_UPDATE_DATE := SYSDATE;
1309 if(X_MODE = 'I') then
1310 X_LAST_UPDATED_BY := 1;
1311 X_LAST_UPDATE_LOGIN := 0;
1312 elsif (X_MODE = 'R') then
1313 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1314 if X_LAST_UPDATED_BY is NULL then
1315 X_LAST_UPDATED_BY := -1;
1316 end if;
1317 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1318 if X_LAST_UPDATE_LOGIN is NULL then
1319 X_LAST_UPDATE_LOGIN := -1;
1320 end if;
1321 else
1322 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1323 IGS_GE_MSG_STACK.ADD;
1324 app_exception.raise_exception;
1325 end if;
1326
1327
1328
1329
1330 Before_DML(
1331 p_action => 'UPDATE',
1332 x_rowid => X_ROWID,
1333 x_person_id => X_PERSON_ID,
1334 x_course_cd => X_COURSE_CD,
1335 x_start_dt => X_START_DT,
1336 x_end_dt => X_END_DT,
1337 x_hecs_payment_option => X_HECS_PAYMENT_OPTION,
1338 x_differential_hecs_ind => X_DIFFERENTIAL_HECS_IND,
1339 x_diff_hecs_ind_update_who => X_DIFF_HECS_IND_UPDATE_WHO,
1340 x_diff_hecs_ind_update_on => X_DIFF_HECS_IND_UPDATE_ON,
1341 x_outside_aus_res_ind => X_OUTSIDE_AUS_RES_IND,
1342 x_nz_citizen_ind => X_NZ_CITIZEN_IND,
1343 x_nz_citizen_less2yr_ind => X_NZ_CITIZEN_LESS2YR_IND,
1344 x_nz_citizen_not_res_ind => X_NZ_CITIZEN_NOT_RES_IND,
1345 x_safety_net_ind => X_SAFETY_NET_IND,
1346 x_tax_file_number => X_TAX_FILE_NUMBER,
1347 x_tax_file_number_collected_dt => X_TAX_FILE_NUMBER_COLLECTED_DT,
1348 x_tax_file_invalid_dt => X_TAX_FILE_INVALID_DT,
1349 x_tax_file_certificate_number => X_TAX_FILE_CERTIFICATE_NUMBER,
1350 x_diff_hecs_ind_update_comment => X_DIFF_HECS_IND_UPDATE_COMMENT,
1351 x_creation_date => X_LAST_UPDATE_DATE,
1352 x_created_by => X_LAST_UPDATED_BY,
1353 x_last_update_date =>X_LAST_UPDATE_DATE,
1354 x_last_updated_by => X_LAST_UPDATED_BY,
1355 x_last_update_login => X_LAST_UPDATE_LOGIN
1356 );
1357
1358
1359 if (X_MODE = 'R') then
1360 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1361 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1362 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1363 if (X_REQUEST_ID = -1) then
1364 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1365 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1366 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1367 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1368 else
1369 X_PROGRAM_UPDATE_DATE := SYSDATE;
1370 end if;
1371 end if;
1372
1373
1374 update IGS_EN_STDNTPSHECSOP set
1375 END_DT = X_END_DT,
1376 HECS_PAYMENT_OPTION = NEW_REFERENCES.HECS_PAYMENT_OPTION,
1377 DIFFERENTIAL_HECS_IND = NEW_REFERENCES.DIFFERENTIAL_HECS_IND,
1378 DIFF_HECS_IND_UPDATE_WHO = NEW_REFERENCES.DIFF_HECS_IND_UPDATE_WHO,
1379 DIFF_HECS_IND_UPDATE_ON = NEW_REFERENCES.DIFF_HECS_IND_UPDATE_ON,
1380 OUTSIDE_AUS_RES_IND = NEW_REFERENCES.OUTSIDE_AUS_RES_IND,
1381 NZ_CITIZEN_IND = NEW_REFERENCES.NZ_CITIZEN_IND,
1382 NZ_CITIZEN_LESS2YR_IND = NEW_REFERENCES.NZ_CITIZEN_LESS2YR_IND,
1383 NZ_CITIZEN_NOT_RES_IND = NEW_REFERENCES.NZ_CITIZEN_NOT_RES_IND,
1384 SAFETY_NET_IND = NEW_REFERENCES.SAFETY_NET_IND,
1385 TAX_FILE_NUMBER = NEW_REFERENCES.TAX_FILE_NUMBER,
1386 TAX_FILE_NUMBER_COLLECTED_DT = NEW_REFERENCES.TAX_FILE_NUMBER_COLLECTED_DT,
1387 TAX_FILE_INVALID_DT = NEW_REFERENCES.TAX_FILE_INVALID_DT,
1388 TAX_FILE_CERTIFICATE_NUMBER = NEW_REFERENCES.TAX_FILE_CERTIFICATE_NUMBER,
1389 DIFF_HECS_IND_UPDATE_COMMENTS = NEW_REFERENCES.DIFF_HECS_IND_UPDATE_COMMENTS,
1390 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1391 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1392 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1393 REQUEST_ID = X_REQUEST_ID,
1394 PROGRAM_ID = X_PROGRAM_ID,
1395 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1396 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
1397 where ROWID = X_ROWID;
1398
1399 if (sql%notfound) then
1400 raise no_data_found;
1401 end if;
1402
1403
1404 After_DML(
1405 p_action => 'UPDATE',
1406 x_rowid => X_ROWID
1407 );
1408
1409
1410 end UPDATE_ROW;
1411
1412 procedure ADD_ROW (
1413 X_ROWID in out NOCOPY VARCHAR2,
1414 X_PERSON_ID in NUMBER,
1415 X_COURSE_CD in VARCHAR2,
1416 X_START_DT in out NOCOPY DATE,
1417 X_END_DT in DATE,
1418 X_HECS_PAYMENT_OPTION in VARCHAR2,
1419 X_DIFFERENTIAL_HECS_IND in VARCHAR2,
1420 X_DIFF_HECS_IND_UPDATE_WHO in VARCHAR2,
1421 X_DIFF_HECS_IND_UPDATE_ON in DATE,
1422 X_OUTSIDE_AUS_RES_IND in VARCHAR2,
1423 X_NZ_CITIZEN_IND in VARCHAR2,
1424 X_NZ_CITIZEN_LESS2YR_IND in VARCHAR2,
1425 X_NZ_CITIZEN_NOT_RES_IND in VARCHAR2,
1426 X_SAFETY_NET_IND in VARCHAR2,
1427 X_TAX_FILE_NUMBER in NUMBER,
1428 X_TAX_FILE_NUMBER_COLLECTED_DT in DATE,
1429 X_TAX_FILE_INVALID_DT in DATE,
1430 X_TAX_FILE_CERTIFICATE_NUMBER in NUMBER,
1431 X_DIFF_HECS_IND_UPDATE_COMMENT in VARCHAR2,
1432 X_MODE in VARCHAR2 default 'R'
1433 ) AS
1434 cursor c1 is select rowid from IGS_EN_STDNTPSHECSOP
1435 where PERSON_ID = X_PERSON_ID
1436 and COURSE_CD = X_COURSE_CD
1437 and START_DT = nvl(X_START_DT,SYSDATE)
1438 ;
1439
1440 begin
1441 open c1;
1442 fetch c1 into X_ROWID;
1443 if (c1%notfound) then
1444 close c1;
1445 INSERT_ROW (
1446 X_ROWID,
1447 X_PERSON_ID,
1448 X_COURSE_CD,
1449 X_START_DT,
1450 X_END_DT,
1451 X_HECS_PAYMENT_OPTION,
1452 X_DIFFERENTIAL_HECS_IND,
1453 X_DIFF_HECS_IND_UPDATE_WHO,
1454 X_DIFF_HECS_IND_UPDATE_ON,
1455 X_OUTSIDE_AUS_RES_IND,
1456 X_NZ_CITIZEN_IND,
1457 X_NZ_CITIZEN_LESS2YR_IND,
1458 X_NZ_CITIZEN_NOT_RES_IND,
1459 X_SAFETY_NET_IND,
1460 X_TAX_FILE_NUMBER,
1461 X_TAX_FILE_NUMBER_COLLECTED_DT,
1462 X_TAX_FILE_INVALID_DT,
1463 X_TAX_FILE_CERTIFICATE_NUMBER,
1464 X_DIFF_HECS_IND_UPDATE_COMMENT,
1465 X_MODE);
1466 return;
1467 end if;
1468 close c1;
1469 UPDATE_ROW (
1470 X_ROWID,
1471 X_PERSON_ID,
1472 X_COURSE_CD,
1473 X_START_DT,
1474 X_END_DT,
1475 X_HECS_PAYMENT_OPTION,
1476 X_DIFFERENTIAL_HECS_IND,
1477 X_DIFF_HECS_IND_UPDATE_WHO,
1478 X_DIFF_HECS_IND_UPDATE_ON,
1479 X_OUTSIDE_AUS_RES_IND,
1480 X_NZ_CITIZEN_IND,
1481 X_NZ_CITIZEN_LESS2YR_IND,
1482 X_NZ_CITIZEN_NOT_RES_IND,
1483 X_SAFETY_NET_IND,
1484 X_TAX_FILE_NUMBER,
1485 X_TAX_FILE_NUMBER_COLLECTED_DT,
1486 X_TAX_FILE_INVALID_DT,
1487 X_TAX_FILE_CERTIFICATE_NUMBER,
1488 X_DIFF_HECS_IND_UPDATE_COMMENT,
1489 X_MODE);
1490 end ADD_ROW;
1491
1492 procedure DELETE_ROW (
1493 X_ROWID in VARCHAR2
1494 ) AS
1495 begin
1496
1497 Before_DML(
1498 p_action => 'DELETE',
1499 x_rowid => X_ROWID
1500 );
1501
1502
1503 delete from IGS_EN_STDNTPSHECSOP
1504 where ROWID = X_ROWID;
1505 if (sql%notfound) then
1506 raise no_data_found;
1507 end if;
1508
1509 After_DML(
1510 p_action => 'DELETE',
1511 x_rowid => X_ROWID
1512 );
1513
1514
1515 end DELETE_ROW;
1516
1517 end IGS_EN_STDNTPSHECSOP_PKG;