1 PACKAGE BODY igs_fi_gen_006 AS
2 /* $Header: IGSFI44B.pls 120.0 2005/06/01 18:30:52 appldev noship $ */
3 --
4 -- who when what
5 -- uudayapr 12-dec-2003 Bug#3080983 modfied the procedure finp_mnt_hecs_pymnt_optn
6 -- vchappid 20-Feb-2003 Bug# 2747335, new function created, validates user-defined
7 -- Person Type Id exists in system.
8 -- vvutukur 19-Dec-2002 Bug#2680885. Modified finp_mnt_hecs_pymnt_optn.
9 -- vvutukur 31-Aug-2002 Bug#2531390.Modified procedures finp_ins_stmnt_o_acc and
10 -- finp_mnt_pymnt_schd,finp_mnt_hecs_pymnt_optn.
11 -- nalkumar 11-Dec-2001 Removed the function finp_mnt_fee_encmb from this package.
12 -- This is as per the SFCR015-HOLDS DLD. Bug:2126091
13 -- Nalin Kumar 16-Jan-2002 Added 'SET VERIFY OFF' before whenever sqlerr... |
14 -- gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.
15
16 /* Bug 1956374
17 Who msrinivi
18 What duplicate removal Pointed genp_val_bus_day to igs_tr_val_tri
19 */
20 PROCEDURE finp_ins_stmnt_o_acc(
21 errbuf out NOCOPY varchar2,
22 retcode out NOCOPY number,
23 p_correspondence_type IN VARCHAR2 ,
24 P_FIN_PERD IN VARCHAR2,
25 P_FEE_PERD IN VARCHAR2,
26 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
27 p_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
28 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
29 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
30 p_person_group_id IN NUMBER ,
31 p_institution_cd IN VARCHAR2 ,
32 p_addr_type IN VARCHAR2 ,
33 P_DATE_OF_ISSUE_C IN VARCHAR2,
34 p_comment IN VARCHAR2 ,
35 p_test_extraction IN VARCHAR2 ,
36 p_org_id NUMBER
37 ) AS
38 /*----------------------------------------------------------------------------
39 || Created By :
40 || Created On :
41 || Purpose :
42 || Known limitations, enhancements or remarks :
43 || Change History :
44 || Who When What
45 || (reverse chronological order - newest change first)
46 || vvutukur 31-Aug-2002 Bug#2531390.Modified message name IGS_GE_OBSOLETE_CONC_PROGRAM to
47 || IGS_GE_OBSOLETE_JOB,as the former message name does not exist.
48 ----------------------------------------------------------------------------*/
49 BEGIN
50 retcode:=0;
51 -- As per the SFCR005, this concurrent program is obsolete and if the user tries
52 -- to run this program, then an error message should be logged into the log file that
53 -- the concurrent program is obsoleted and should not be run
54 FND_MESSAGE.Set_Name('IGS',
55 'IGS_GE_OBSOLETE_JOB');
56 FND_FILE.Put_Line(FND_FILE.Log,
57 FND_MESSAGE.Get);
58 EXCEPTION
59 WHEN OTHERS THEN
60 RETCODE:=2;
61 ERRBUF:=FND_MESSAGE.GET_STRING ('IGS','IGS_GE_UNHANDLED_EXP');
62 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
63 END finp_ins_stmnt_o_acc;
64
65 PROCEDURE finp_mnt_pymnt_schdl(
66 errbuf out NOCOPY varchar2,
67 retcode out NOCOPY number,
68 P_FEE_ASSESSMENT_PERIOD IN VARCHAR2,
69 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
70 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
71 p_fee_category IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
72 p_notification_dt_c IN VARCHAR2,
73 p_num_days_to_notification NUMBER ,
74 p_next_bus_day_ind IN VARCHAR,
75 p_org_id NUMBER
76 ) AS
77 /*----------------------------------------------------------------------------
78 || Created By :
79 || Created On :
80 || Purpose :
81 || Known limitations, enhancements or remarks :
82 || Change History :
83 || Who When What
84 || (reverse chronological order - newest change first)
85 || vvutukur 31-Aug-2002 Bug#2531390.Modified message name IGS_GE_OBSOLETE_CONC_PROGRAM to
86 || IGS_GE_OBSOLETE_JOB,as the former message name does not exist.
87 ----------------------------------------------------------------------------*/
88 BEGIN
89 -- As per the SFCR005, this concurrent program is obsolete and if the user tries
90 -- to run this program, then an error message should be logged into the log file that
91 -- the concurrent program is obsoleted and should not be run
92 FND_MESSAGE.Set_Name('IGS',
93 'IGS_GE_OBSOLETE_JOB');
94 FND_FILE.Put_Line(FND_FILE.Log,
95 FND_MESSAGE.Get);
96 retcode := 0;
97
98 EXCEPTION
99 WHEN OTHERS THEN
100 RETCODE:=2;
101 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
102 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
103 END finp_mnt_pymnt_schdl;
104
105 FUNCTION finp_mnt_hecs_pymnt_optn(
106 p_effective_dt IN DATE ,
107 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
108 p_fee_cal_type IN IGS_CA_INST_ALL.CAL_TYPE%TYPE ,
109 p_fee_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
110 p_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.FEE_CAT%TYPE ,
111 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
112 p_update_ind IN VARCHAR2,
113 p_deferred_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE ,
114 p_upfront_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE ,
115 p_creation_dt IN OUT NOCOPY IGS_GE_S_LOG.creation_dt%TYPE ,
116 p_hecs_payment_type OUT NOCOPY FND_LOOKUP_Values.LOOKUP_CODE%TYPE ,
117 p_message_name OUT NOCOPY VARCHAR2)
118 RETURN BOOLEAN AS
119 /*----------------------------------------------------------------------------
120 || Created By :
121 || Created On :
122 || Purpose :
123 || Known limitations, enhancements or remarks :
124 || Change History :
125 || Who When What
126 || (reverse chronological order - newest change first)
127 uudayapr 12-12-2003 bug#3080983 made the modification to Cursor c_fadv to point to the table
128 IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
129 || vvutukur 19-Dec-2002 Bug#2680885.Commented out cursor c_fpv which selects from igs_fi_fee_pay_v, which
130 || is to be dropped.Instead, cursor c_fpv is redefined selecting 0 from dual. This
131 || function will be obsoleted as part of HECS functionality obsoletion. As this got
132 || deferred for Jan'03 scope,commented out this portion.
133 || vvutukur 30-Aug-2002 Bug#2531390.Removed default value of p_update_ind parameter to avoid
134 || gscc warning.
135 ----------------------------------------------------------------------------*/
136 gv_other_detail VARCHAR2(255);
137 e_resource_busy_exception EXCEPTION;
138 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
139 -- cursor to get student IGS_PS_COURSE HECS payment option details
140 CURSOR c_scho(
141 cp_effective_dt DATE,
142 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
143 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
144 SELECT IGS_EN_STDNTPSHECSOP.*, rowid
145 FROM IGS_EN_STDNTPSHECSOP
146 WHERE person_id = cp_person_id AND
147 course_cd = cp_course_cd AND
148 TRUNC(cp_effective_dt) >= TRUNC(start_dt) AND
149 TRUNC(cp_effective_dt) <= TRUNC(NVL(end_dt, cp_effective_dt))
150 FOR UPDATE OF end_dt NOWAIT;
151 BEGIN -- finp_mnt_hecs_pymnt_optn
152 -- Maintain a student's IGS_PS_COURSE attempt HECS payment option on the basis of
153 -- their assessed liability and any up front payment made.
154 DECLARE
155 CURSOR c_hpo (
156 cp_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE,
157 cp_hecs_payment_type IGS_FI_GOV_HEC_PA_OP.s_hecs_payment_type%TYPE) IS
158 SELECT HECS_PAYMENT_OPTION
159 FROM IGS_FI_HECS_PAY_OPTN hpo,
160 IGS_FI_GOV_HEC_PA_OP ghpo
161 WHERE hpo.HECS_PAYMENT_OPTION = cp_payment_option AND
162 hpo.closed_ind = 'N' AND
163 hpo.GOVT_HECS_PAYMENT_OPTION = ghpo.GOVT_HECS_PAYMENT_OPTION AND
164 ghpo.s_hecs_payment_type = cp_hecs_payment_type;
165 CURSOR c_ghpo(
166 cp_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE) IS
167 SELECT ghpo.s_hecs_payment_type
168 FROM IGS_FI_HECS_PAY_OPTN hpo,
169 IGS_FI_GOV_HEC_PA_OP ghpo
170 WHERE hpo.HECS_PAYMENT_OPTION = cp_payment_option AND
171 hpo.GOVT_HECS_PAYMENT_OPTION = ghpo.GOVT_HECS_PAYMENT_OPTION;
172 -- Modified the Cursor c_fadv to fetch the Data from the table IGS_FI_FEE_AS
173 -- Instead of the the View IGS_FI_FEE_ASS_DEBT_V
174 CURSOR c_fadv IS
175 SELECT SUM(fadv.transaction_amount)
176 FROM IGS_FI_FEE_AS fadv,
177 IGS_FI_FEE_TYPE ft
178 WHERE fadv.person_id = p_person_id
179 AND fadv.fee_cal_type = p_fee_cal_type
180 AND fadv.fee_ci_sequence_number = p_fee_ci_sequence_number
181 AND ((fadv.FEE_CAT = p_fee_cat) OR (fadv.FEE_CAT IS NULL AND p_fee_cat IS NULL))
182 AND ((fadv.course_cd = p_course_cd ) OR (fadv.course_cd IS NULL AND p_course_cd IS NULL))
183 AND fadv.FEE_TYPE = ft.FEE_TYPE
184 AND ft.s_fee_type = 'HECS'
185 AND fadv.logical_delete_dt IS NULL
186 GROUP BY fadv.person_id,fadv.fee_cal_type,fadv.fee_ci_sequence_number
187 HAVING SUM(fadv.transaction_amount) >0;
188
189 CURSOR c_fpv IS
190 SELECT 0
191 FROM dual;
192
193 /* CURSOR c_fpv IS
194 SELECT SUM(fpv.payment_amount)
195 FROM IGS_FI_FEE_PAY_V fpv,
196 IGS_FI_FEE_TYPE ft
197 WHERE fpv.person_id = p_person_id AND
198 fpv.fee_cal_type = p_fee_cal_type AND
199 fpv.fee_ci_sequence_number = p_fee_ci_sequence_number AND
200 fpv.FEE_CAT = p_fee_cat AND
201 fpv.course_cd = p_course_cd AND
202 fpv.payment_amount > 0 AND
203 fpv.FEE_TYPE = ft.FEE_TYPE AND
204 ft.s_fee_type = 'HECS';*/
205 CURSOR c_scho2(
206 cp_end_dt DATE) IS
207 SELECT tax_file_number,
208 tax_file_number_collected_dt,
209 tax_file_certificate_number
210 FROM IGS_EN_STDNTPSHECSOP
211 WHERE person_id = p_person_id AND
212 course_cd = p_course_cd AND
213 start_dt > cp_end_dt AND
214 (tax_file_number IS NOT NULL AND
215 tax_file_invalid_dt IS NULL) OR
216 tax_file_certificate_number IS NOT NULL
217 ORDER BY start_dt;
218 v_hpo_upfront_rec c_hpo%ROWTYPE;
219 v_hpo_deferred_rec c_hpo%ROWTYPE;
220 v_ghpo_rec c_ghpo%ROWTYPE;
221 v_scho_rec c_scho%ROWTYPE;
222 v_assessment_amount_sum NUMBER;
223 v_payment_amount_sum NUMBER;
224 v_tax_file_number IGS_EN_STDNTPSHECSOP.tax_file_number%TYPE;
225 v_tax_file_number_collected_dt
226 IGS_EN_STDNTPSHECSOP.tax_file_number_collected_dt%TYPE;
227 v_tax_file_certificate_number
228 IGS_EN_STDNTPSHECSOP.tax_file_certificate_number%TYPE;
229 v_deferred_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE
230 DEFAULT p_deferred_payment_option;
231 v_upfront_payment_option IGS_FI_HECS_PAY_OPTN.HECS_PAYMENT_OPTION%TYPE
232 DEFAULT p_upfront_payment_option;
233 v_valid_tax_details BOOLEAN DEFAULT FALSE;
234 v_key IGS_GE_S_LOG.key%TYPE DEFAULT NULL;
235 v_text IGS_GE_S_LOG_ENTRY.text%TYPE DEFAULT NULL;
236 v_deferred_option BOOLEAN DEFAULT FALSE;
237 v_effective_dt DATE ;
238 lv_rowid VARCHAR2(25);
239 BEGIN
240 -- Set the default message number
241 p_message_name := null;
242 -- Check Parameters
243 IF p_effective_dt IS NULL OR
244 p_person_id IS NULL OR
245 p_fee_cal_type IS NULL OR
246 p_fee_ci_sequence_number IS NULL OR
247 p_fee_cat IS NULL OR
248 p_course_cd IS NULL OR
249 p_update_ind IS NULL THEN
250 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 IF p_effective_dt > SYSDATE THEN
255 p_message_name:= 'IGS_GE_INVALID_VALUE';
256 RETURN FALSE;
257 END IF;
258 IF p_update_ind = 'Y' THEN
259 -- Validate the deferred payment option parameter
260 IF p_deferred_payment_option IS NULL THEN
261 p_message_name:= 'IGS_AD_ADMPRD_DTALIAS_EXISTS';
262 RETURN FALSE;
263 ELSE
264 OPEN c_hpo(
265 p_deferred_payment_option,
266 'DEFERRED');
267 FETCH c_hpo INTO v_hpo_deferred_rec;
268 IF c_hpo%NOTFOUND THEN
269 CLOSE c_hpo;
270 p_message_name:= 'IGS_AD_ADMPRD_DTALIAS_EXISTS';
271 RETURN FALSE;
272 END IF;
273 CLOSE c_hpo;
274 END IF;
275 -- Validate the upfront payment option parameter
276 IF p_upfront_payment_option IS NULL THEN
277 p_message_name:= 'IGS_GE_INVALID_VALUE';
278 RETURN FALSE;
279 ELSE
280 OPEN c_hpo(
281 p_upfront_payment_option,
282 'UPFRONT_D');
283 FETCH c_hpo INTO v_hpo_upfront_rec;
284 IF c_hpo%NOTFOUND THEN
285 CLOSE c_hpo;
286 p_message_name:= 'IGS_GE_INVALID_VALUE';
287 RETURN FALSE;
288 END IF;
289 CLOSE c_hpo;
290 END IF;
291 END IF;
292 -- Get the HECS payment option matching the effective date.
293 -- IGS_GE_NOTE, later entries may exist.
294 OPEN c_scho(
295 p_effective_dt,
296 p_person_id,
297 p_course_cd);
298 FETCH c_scho INTO v_scho_rec;
299 IF c_scho%NOTFOUND THEN
300 CLOSE c_scho;
301 p_message_name:= 'IGS_GE_INVALID_VALUE';
302 RETURN FALSE;
303 END IF;
304 -- Get the HECS payment option payment type
305 OPEN c_ghpo( v_scho_rec.HECS_PAYMENT_OPTION);
306 FETCH c_ghpo INTO v_ghpo_rec;
307 IF c_ghpo%NOTFOUND THEN
308 CLOSE c_ghpo;
309 CLOSE c_scho;
310 RAISE NO_DATA_FOUND;
311 END IF;
312 CLOSE c_ghpo;
313 p_hecs_payment_type := v_ghpo_rec.s_hecs_payment_type;
314 -- Get the current debt balance.
315 -- IGS_GE_NOTE, multiple fee types could be recognised as HECS fees.
316 OPEN c_fadv;
317 FETCH c_fadv INTO v_assessment_amount_sum;
318 IF c_fadv%NOTFOUND THEN
319 CLOSE c_fadv;
320 CLOSE c_scho;
321 RETURN TRUE;
322 END IF;
323 CLOSE c_fadv;
324 IF v_assessment_amount_sum IS NULL THEN
325 v_assessment_amount_sum := 0;
326 END IF;
327 -- Check current payment balance
328 OPEN c_fpv;
329 FETCH c_fpv INTO v_payment_amount_sum;
330 IF c_fpv%NOTFOUND THEN
331 CLOSE c_fpv;
332 -- Considering no payments have been made, the current payment
333 -- type should be deferred
334 v_deferred_option := TRUE;
335 ELSE
336 CLOSE c_fpv;
337 IF v_payment_amount_sum IS NULL THEN
338 v_payment_amount_sum := 0;
339 END IF;
340 IF v_payment_amount_sum < v_assessment_amount_sum THEN
341 -- Considering full payment has not been made, the current payment
342 -- type should be deferred
343 v_deferred_option := TRUE;
344 END IF;
345 END IF;
346 IF v_deferred_option THEN
347 -- 1.1 Deferred Payment
348 -- Students with UPFRONT_D (up front with discount) can be switched
349 -- to a deferred payment type option when tax file details are known.
350 IF v_ghpo_rec.s_hecs_payment_type = 'UPFRONT_D' THEN
351 -- 1.1.1 Check Tax File Details
352 IF (v_scho_rec.tax_file_number IS NOT NULL AND
353 v_scho_rec.tax_file_invalid_dt IS NULL) OR
354 v_scho_rec.tax_file_certificate_number IS NOT NULL THEN
355 v_tax_file_number := v_scho_rec.tax_file_number;
356 v_tax_file_number_collected_dt := v_scho_rec.tax_file_number_collected_dt;
357 v_tax_file_certificate_number := v_scho_rec.tax_file_certificate_number;
358 v_valid_tax_details := TRUE;
359 ELSE
360 -- Check later payment option details
361 IF v_scho_rec.end_dt IS NOT NULL THEN
362 FOR v_scho_chk_rec IN c_scho2(
363 v_scho_rec.end_dt) LOOP
364 v_tax_file_number := v_scho_chk_rec.tax_file_number;
365 v_tax_file_number_collected_dt :=
366 v_scho_chk_rec.tax_file_number_collected_dt;
367 v_tax_file_certificate_number :=
368 v_scho_chk_rec.tax_file_certificate_number;
369 v_valid_tax_details := TRUE;
370 EXIT;
371 END LOOP;
372 END IF;
373 END IF;
374 IF NOT v_valid_tax_details THEN
375 v_key := igs_ge_date.igschar(p_effective_dt) || '|' ||
376 TO_CHAR(p_person_id) || '|' ||
377 p_fee_cal_type || '|' ||
378 TO_CHAR(p_fee_ci_sequence_number) || '|' ||
379 p_fee_cat || '|' ||
380 p_course_cd;
381 IF p_creation_dt IS NULL THEN
382 -- Initialise the log
383 p_creation_dt := SYSDATE;
384 IGS_GE_GEN_003.GENP_INS_LOG(
385 'HECS-OPT',
386 v_key,
387 p_creation_dt);
388 END IF;
389 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
390 'HECS-OPT',
391 p_creation_dt,
392 v_key,
393 'IGS_GE_INVALID_VALUE',
394 NULL);
395 ELSE -- valid
396 p_hecs_payment_type := 'DEFERRED';
397 IF p_update_ind = 'Y' THEN
398 IF TRUNC(v_scho_rec.start_dt) <> TRUNC(p_effective_dt) THEN
399 -- end the current IGS_EN_STDNTPSHECSOP entry
400 IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
401 x_rowid => v_scho_rec.rowid,
402 x_person_id => v_scho_rec.person_id,
403 x_course_cd => v_scho_rec.course_cd,
404 x_start_dt => v_scho_rec.start_dt,
405 x_end_dt => p_effective_dt - 1,
406 x_hecs_payment_option => v_scho_rec.hecs_payment_option,
407 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
408 x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
409 x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
410 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
411 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
412 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
413 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
414 x_safety_net_ind => v_scho_rec.safety_net_ind,
415 x_tax_file_number => v_scho_rec.tax_file_number,
416 x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
417 x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
418 x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
419 x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
420 x_mode => 'R'
421 );
422 -- Create a new hecs payment option entry for
423 -- the student IGS_PS_COURSE attempt
424 v_effective_dt := p_effective_dt;
425 IGS_EN_STDNTPSHECSOP_Pkg.Insert_Row (
426 x_rowid => lv_rowid,
427 x_person_id => v_scho_rec.person_id,
428 x_course_cd => v_scho_rec.course_cd,
429 x_start_dt => v_effective_dt,
430 x_end_dt => v_scho_rec.end_dt,
431 x_hecs_payment_option => p_upfront_payment_option,
432 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
433 x_diff_hecs_ind_update_who => Null,
434 x_diff_hecs_ind_update_on => Null,
435 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
436 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
437 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
438 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
439 x_safety_net_ind => 'Y',
440 x_tax_file_number => v_tax_file_number,
441 x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
442 x_tax_file_invalid_dt => Null,
443 x_tax_file_certificate_number => v_tax_file_certificate_number,
444 x_diff_hecs_ind_update_comment => Null,
445 x_mode => 'R'
446 );
447 ELSE -- HECS option detail commenced on the effective dt
448 IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
449 x_rowid => v_scho_rec.rowid,
450 x_person_id => v_scho_rec.person_id,
451 x_course_cd => v_scho_rec.course_cd,
452 x_start_dt => v_scho_rec.start_dt,
453 x_end_dt => v_scho_rec.end_dt,
454 x_hecs_payment_option => p_deferred_payment_option,
455 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
456 x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
457 x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
458 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
459 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
460 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
461 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
462 x_safety_net_ind => 'N',
463 x_tax_file_number => v_tax_file_number,
464 x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
465 x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
466 x_tax_file_certificate_number => v_tax_file_certificate_number,
467 x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
468 x_mode => 'R'
469 );
470 END IF;
471 -- log entry
472 v_text := ' ' ||
473 v_scho_rec.HECS_PAYMENT_OPTION || ' ' ||
474 p_deferred_payment_option;
475 v_key := igs_ge_date.igschar(p_effective_dt) || '|' ||
476 TO_CHAR(p_person_id) || '|' ||
477 p_fee_cal_type || '|' ||
478 TO_CHAR(p_fee_ci_sequence_number) || '|' ||
479 p_fee_cat || '|' ||
480 p_course_cd;
481 IF p_creation_dt IS NULL THEN
482 -- Initialise the log
483 p_creation_dt := SYSDATE;
484 IGS_GE_GEN_003.GENP_INS_LOG(
485 'HECS-OPT',
486 v_key,
487 p_creation_dt);
488 END IF;
489 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
490 'HECS-OPT',
491 p_creation_dt,
492 v_key,
493 NULL,
494 v_text);
495 END IF;
496 END IF;
497 END IF;
498 ELSE -- v_deferred_option = FALSE
499 -- 1.2 Up Front Payment
500 IF v_ghpo_rec.s_hecs_payment_type = 'DEFERRED' THEN
501 p_hecs_payment_type := 'UPFRONT_D';
502 IF p_update_ind = 'Y' THEN
503 IF TRUNC(v_scho_rec.start_dt) <> TRUNC(p_effective_dt) THEN
504 -- End the current student IGS_PS_COURSE hecs option entry
505 IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
506 x_rowid => v_scho_rec.rowid,
507 x_person_id => v_scho_rec.person_id,
508 x_course_cd => v_scho_rec.course_cd,
509 x_start_dt => v_scho_rec.start_dt,
510 x_end_dt => p_effective_dt - 1,
511 x_hecs_payment_option => v_scho_rec.hecs_payment_option,
512 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
513 x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
514 x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
515 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
516 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
517 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
518 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
519 x_safety_net_ind => v_scho_rec.safety_net_ind,
520 x_tax_file_number => v_scho_rec.tax_file_number,
521 x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
522 x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
523 x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
524 x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
525 x_mode => 'R'
526 );
527 -- Create a new hecs payment option entry for
528 -- the student IGS_PS_COURSE attempt
529 v_effective_dt := p_effective_dt;
530 IGS_EN_STDNTPSHECSOP_Pkg.Insert_Row (
531 x_rowid => lv_rowid,
532 x_person_id => v_scho_rec.person_id,
533 x_course_cd => v_scho_rec.course_cd,
534 x_start_dt => v_effective_dt,
535 x_end_dt => v_scho_rec.end_dt,
536 x_hecs_payment_option => p_upfront_payment_option,
537 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
538 x_diff_hecs_ind_update_who => Null,
539 x_diff_hecs_ind_update_on => Null,
540 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
541 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
542 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
543 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
544 x_safety_net_ind => 'Y',
545 x_tax_file_number => v_tax_file_number,
546 x_tax_file_number_collected_dt => v_tax_file_number_collected_dt,
547 x_tax_file_invalid_dt => Null,
548 x_tax_file_certificate_number => v_tax_file_certificate_number,
549 x_diff_hecs_ind_update_comment => Null,
550 x_mode => 'R'
551 );
552 ELSE -- HECS option detail commenced on the effective dt
553 IGS_EN_STDNTPSHECSOP_Pkg.Update_Row (
554 x_rowid => v_scho_rec.rowid,
555 x_person_id => v_scho_rec.person_id,
556 x_course_cd => v_scho_rec.course_cd,
557 x_start_dt => v_scho_rec.start_dt,
558 x_end_dt => v_scho_rec.end_dt,
559 x_hecs_payment_option => p_upfront_payment_option,
560 x_differential_hecs_ind => v_scho_rec.differential_hecs_ind,
561 x_diff_hecs_ind_update_who => v_scho_rec.diff_hecs_ind_update_who,
562 x_diff_hecs_ind_update_on => v_scho_rec.diff_hecs_ind_update_on,
563 x_outside_aus_res_ind => v_scho_rec.outside_aus_res_ind,
564 x_nz_citizen_ind => v_scho_rec.nz_citizen_ind,
565 x_nz_citizen_less2yr_ind => v_scho_rec.nz_citizen_less2yr_ind,
566 x_nz_citizen_not_res_ind => v_scho_rec.nz_citizen_not_res_ind,
567 x_safety_net_ind => 'Y',
568 x_tax_file_number => v_scho_rec.tax_file_number,
569 x_tax_file_number_collected_dt => v_scho_rec.tax_file_number_collected_dt,
570 x_tax_file_invalid_dt => v_scho_rec.tax_file_invalid_dt,
571 x_tax_file_certificate_number => v_scho_rec.tax_file_certificate_number,
572 x_diff_hecs_ind_update_comment => v_scho_rec.diff_hecs_ind_update_comments,
573 x_mode => 'R'
574 );
575 END IF;
576 -- log entry
577 v_text := ' ' ||
578 v_scho_rec.HECS_PAYMENT_OPTION || ' ' ||
579 p_upfront_payment_option;
580 v_key := igs_ge_date.igschar(p_effective_dt) || '|' ||
581 TO_CHAR(p_person_id) || '|' ||
582 p_fee_cal_type || '|' ||
583 TO_CHAR(p_fee_ci_sequence_number) || '|' ||
584 p_fee_cat || '|' ||
585 p_course_cd;
586 IF p_creation_dt IS NULL THEN
587 -- Initialise the log
588 p_creation_dt := SYSDATE;
589 IGS_GE_GEN_003.GENP_INS_LOG(
590 'HECS-OPT',
591 v_key,
592 p_creation_dt);
593 END IF;
594 IGS_GE_GEN_003.GENP_INS_LOG_ENTRY(
595 'HECS-OPT',
596 p_creation_dt,
597 v_key,
598 NULL,
599 v_text);
600 END IF;
601 END IF;
602 END IF;
603 CLOSE c_scho;
604 RETURN TRUE;
605 END;
606 EXCEPTION
607 WHEN e_resource_busy_exception THEN
608 CLOSE c_scho;
609 p_message_name:= 'IGS_AD_OFRST_LATE_FEES';
610 RETURN FALSE;
611 WHEN OTHERS THEN
612 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
613 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_006.finp_mnt_hecs_pymnt_optn');
614 IGS_GE_MSG_STACK.ADD;
615 App_Exception.Raise_Exception;
616 END finp_mnt_hecs_pymnt_optn;
617
618 PROCEDURE validate_prsn_id_typ(p_c_usr_alt_prs_id_typ IN VARCHAR2,
619 p_c_unique IN VARCHAR2,
620 p_b_status OUT NOCOPY BOOLEAN,
621 p_c_sys_alt_prs_id_typ OUT NOCOPY VARCHAR2)
622 AS
623 /*----------------------------------------------------------------------------
624 || Created By : Vinay Chappidi
625 || Created On : 20-Feb-2003
626 || Purpose : Validates user-defined person id type
627 || Known limitations, enhancements or remarks :
628 || Change History :
629 || Who When What
630 || gmaheswa 29-Sep-2004 BUG 3787210 Added Closed indicator check for the Alternate Person Id type.
631 ----------------------------------------------------------------------------*/
632
633 CURSOR cur_usr_alt_prs_id_typ(cp_c_usr_alt_prs_id_typ igs_pe_person_id_typ.person_id_type%TYPE,
634 cp_c_unique igs_pe_person_id_typ.unique_ind%TYPE)
635 IS
636 SELECT s_person_id_type
637 FROM igs_pe_person_id_typ
638 WHERE person_id_type = cp_c_usr_alt_prs_id_typ AND
639 NVL(unique_ind,'N') = cp_c_unique AND
640 closed_ind = 'N';
641 l_c_sys_prsn_id_typ igs_pe_person_id_typ.s_person_id_type%TYPE;
642
643 BEGIN
644
645 -- Check if the mandatory parameters are passed to this procedure
646 -- return from the procedure when not passed
647 IF (p_c_usr_alt_prs_id_typ IS NULL OR p_c_unique IS NULL) THEN
648 p_b_status := FALSE;
649 p_c_sys_alt_prs_id_typ := NULL;
650 RETURN;
651 END IF;
652
653 -- Check if the p_c_unique parameter value is other than 'Y' or 'N'
654 -- return from the procedure when any other values are passed
655 IF (p_c_unique NOT IN ('Y','N')) THEN
656 p_b_status := FALSE;
657 p_c_sys_alt_prs_id_typ := NULL;
658 RETURN;
659 END IF;
660
661 -- When all the mandatory validations are successful, check if the user-defined alternate person id type
662 -- exists in the system and assign appropriate values to OUT variables and return from the procedure.
663 OPEN cur_usr_alt_prs_id_typ(p_c_usr_alt_prs_id_typ, p_c_unique);
664 FETCH cur_usr_alt_prs_id_typ INTO l_c_sys_prsn_id_typ;
665 IF (cur_usr_alt_prs_id_typ%NOTFOUND) THEN
666 CLOSE cur_usr_alt_prs_id_typ;
667 p_b_status := FALSE;
668 p_c_sys_alt_prs_id_typ := NULL;
669 ELSE
670 CLOSE cur_usr_alt_prs_id_typ;
671 p_b_status := TRUE;
672 p_c_sys_alt_prs_id_typ := l_c_sys_prsn_id_typ;
673 END IF;
674 END validate_prsn_id_typ;
675 END igs_fi_gen_006;
[Home] [Help]