1 PACKAGE BODY igs_fi_gen_001 AS
2 /* $Header: IGSFI01B.pls 120.3 2006/02/23 20:50:48 skharida noship $ */
3
4 /******************************************************************
5 Change History
6 Who When What
7 skharida 23-Feb-2006 After Code Review: Modified finpl_val_trig_group() Bug# 5018036,
8 skharida 15-Feb-2006 Modified finpl_val_trig_group() Bug# 5018036, (version 12.1)
9 uudyapr 06-Jan-2004 ENh#3167098 Added the function CHECK_STDNT_PRG_ATT_LIABLE .
10 uudayapr 12-dec-2003 Bug#3080983 Modified the Functions finp_get_hecs_fee and finp_get_tuition_fee
11 uudayapr 16-oct-2003 Enh#3117341 audit and special fees built. Modification done in
12 function finp_get_fee_trigger.
13 vvutukur 15-Jul-2003 Enh#3038511.FICR106 Build. Modified procedure finp_get_total_planned_credits.
14 vvutukur 1-Dec-2002 Enh#2584986.Modifications done in function finp_get_currency,finp_get_fps_start_dt.
15 SMVK 13-Sep-2002 Bug#2531390. Restored the functions finp_get_fps_end_dt,FINP_GET_FDF_END_DT,
16 FINP_GET_FDF_ST_DT and finp_get_fps_start_dt which are obsolete as the part of same bug.
17 The functions have been modified to return null to make the views which are using to compile
18 vvutukur 02-Sep-2002 Bug#2531390.Removed function finp_get_fps_end_dt,as this is not used
19 anywhere in the system.(resulted in as impact of modification done
20 in IGSFI31B.pls as part of this bug 2531390.
21 smvk 28-Aug-2002 Bug#2531390.Removed the functions FINP_GET_FDF_END_DT, FINP_GET_FDF_ST_DT (SFCR005_Cleanup_Build)
22 vvutukur 26-Aug-2002 Bug#2531390.Removed the function finp_get_fps_start_dt.
23 jbegum 26-Aug-2002 As part of Enh Bug#2531390 the procedure finp_get_overdue_dtl was removed.
24 rnirwani 06-May-02 Bug# 2345570
25 When selecting from view IGS_FI_FEE_TRG_GRP_V, replacing column trigger_type with trigger_type_code.
26 This change has been made in the procedure finpl_val_trig_group
27 rnirwani 25-Apr-02 Obsoleting the procedure finp_get_dj_totals,
28 since this is not being used.
29 Bug# 2329407
30
31 SYkrishn 02-APR-2002 Bug 2293676
32 Added functions finp_get_planned_credits_ind and
33 finp_get_total_planned_credits
34
35 schodava 21-Jan-2002 Enh # 2187247
36 Added functions FINP_GET_LFCI_RELN
37 and FINP_CHK_LFCI_RELN
38 Modified functions finp_get_hecs_amt_pd,
39 finp_get_hecs_fee, finp_get_tuition_fee
40
41
42 ******************************************************************/
43 FUNCTION check_stdnt_prg_att_liable(
44 p_n_person_id IN PLS_INTEGER,
45 p_v_course_cd IN VARCHAR2,
46 p_n_course_version IN PLS_INTEGER,
47 p_v_fee_cat IN VARCHAR2,
48 p_v_fee_type IN VARCHAR2,
49 p_v_s_fee_trigger_cat IN VARCHAR2,
50 p_v_fee_cal_type IN VARCHAR2,
51 p_n_fee_ci_seq_number IN PLS_INTEGER,
52 p_n_adm_appl_number IN NUMBER,
53 p_v_adm_nom_course_cd IN VARCHAR2,
54 p_n_adm_seq_number IN NUMBER,
55 p_d_commencement_dt IN DATE,
56 p_d_disc_dt IN DATE,
57 p_v_cal_type IN VARCHAR2,
58 p_v_location_cd IN VARCHAR2,
59 p_v_attendance_mode IN VARCHAR2,
60 p_v_attendance_type IN VARCHAR2
61 ) RETURN VARCHAR2 AS
62 /*----------------------------------------------------------------------------
63 || Created By : UMESH UDAYAPRAKASH
64 || Created On : 06-JAN-2004
65 || Purpose :Function To Identify Whther A Student Program Attempt Is Liable
66 || For A Fee Category Fee Liability
67 || Known limitations, enhancements or remarks :
68 || Change History :
69 || Who When What
70 || (reverse chronological order - newest change first)
71 ----------------------------------------------------------------------------*/
72 CURSOR c_fcfldate IS
73 SELECT TRUNC(da1.alias_val) start_dt_alias_val,
74 TRUNC(da2.alias_val) end_dt_alias_val
75 FROM igs_fi_f_cat_fee_lbl_v fcflv,
76 igs_ca_da_inst_v da1,
77 igs_ca_da_inst_v da2
78 WHERE da1.dt_alias = fcflv.start_dt_alias
79 AND da1.sequence_number = fcflv.start_dai_sequence_number
80 AND da1.cal_type = fcflv.fee_cal_type
81 AND da1.ci_sequence_number = fcflv.fee_ci_sequence_number
82 AND da1.alias_val IS NOT NULL
83 AND da2.dt_alias = fcflv.end_dt_alias
84 AND da2.sequence_number = fcflv.end_dai_sequence_number
85 AND da2.cal_type = fcflv.fee_cal_type
86 AND da2.ci_sequence_number = fcflv.fee_ci_sequence_number
87 AND da2.alias_val IS NOT NULL
88 AND fcflv.fee_cat = p_v_fee_cat
89 AND fcflv.fee_type = p_v_fee_type
90 AND fcflv.fee_cal_type = p_v_fee_cal_type
91 AND fcflv.fee_ci_sequence_number = p_n_fee_ci_seq_number;
92
93 l_c_fcfldate c_fcfldate%ROWTYPE;
94 l_d_start_dt DATE;
95 l_d_end_dt DATE;
96 l_d_commencement_dt DATE;
97 l_v_trigger_fired igs_fi_fee_type_all.s_fee_trigger_cat%TYPE;
98 BEGIN
99 IF (p_n_person_id IS NULL OR
100 p_v_course_cd IS NULL OR
101 p_n_course_version IS NULL OR
102 p_v_fee_cat IS NULL OR
103 p_v_fee_type IS NULL OR
104 p_v_s_fee_trigger_cat IS NULL OR
105 p_v_fee_cal_type IS NULL OR
106 p_n_fee_ci_seq_number IS NULL OR
107 p_v_cal_type IS NULL OR
108 p_v_location_cd IS NULL OR
109 p_v_attendance_mode IS NULL OR
110 p_v_attendance_type IS NULL )THEN
111
112 RETURN 'FALSE'; -- If Mandatory Parameter Are Not Provided Return False
113 ELSE
114 OPEN c_fcfldate;
115 FETCH c_fcfldate INTO l_c_fcfldate;
116 --If No Matching FCFL records are found then return from the Function.
117 IF (c_fcfldate%NOTFOUND) THEN
118 CLOSE c_fcfldate;
119 RETURN 'FALSE';
120 END IF;
121 CLOSE c_fcfldate;
122 l_d_commencement_dt := p_d_commencement_dt;
123 IF p_d_commencement_dt IS NULL THEN
124 --Derive the Commencement Date if commencement date Parameter is Not Provided.
125 l_d_commencement_dt := igs_en_gen_002.enrp_get_acad_comm( p_acad_cal_type => NULL,
126 p_acad_ci_sequence_number => NULL,
127 p_person_id => p_n_person_id,
128 p_course_cd => p_v_course_cd,
129 p_adm_admission_appl_number => p_n_adm_appl_number,
130 p_adm_nominated_course_cd => p_v_adm_nom_course_cd,
131 p_adm_sequence_number => p_n_adm_seq_number,
132 p_chk_adm_prpsd_comm_ind => 'Y');
133 IF l_d_commencement_dt IS NULL THEN
134 RETURN 'FALSE';
135 END IF;
136 END IF; --End Of Commencement Date Is Null Check
137
138 IF l_d_commencement_dt > SYSDATE THEN
139 l_d_commencement_dt := TRUNC(l_d_commencement_dt);
140 IF NOT ((l_d_commencement_dt >= l_c_fcfldate.start_dt_alias_val) AND (l_d_commencement_dt <= l_c_fcfldate.end_dt_alias_val)) THEN
141 RETURN 'FALSE';
142 END IF;
143 END IF; --END OF SYSDATE COMPARISION.
144
145 IF p_d_disc_dt IS NOT NULL THEN
146 IF NOT ((TRUNC(p_d_disc_dt) >= l_c_fcfldate.start_dt_alias_val) AND (TRUNC(p_d_disc_dt) <= l_c_fcfldate.end_dt_alias_val)) THEN
147 RETURN 'FALSE';
148 END IF;
149 END IF; -- END OF p_d_disc_dt CHECK.
150 IF p_v_s_fee_trigger_cat = 'INSTITUTN' THEN
151 RETURN 'TRUE'; -- If the FeeTrigger Category is Institution then Return True.
152 ELSE
153 --Call finp_get_fee_trigger function with the Input PArameter it will Return the Trigger Fired.
154 l_v_trigger_fired := igs_fi_gen_001.finp_get_fee_trigger( p_fee_cat => p_v_fee_cat,
155 p_fee_cal_type => p_v_fee_cal_type,
156 p_fee_ci_sequence_number => p_n_fee_ci_seq_number,
157 p_fee_type => p_v_fee_type,
158 p_s_fee_trigger_cat => p_v_s_fee_trigger_cat,
159 p_person_id => p_n_person_id,
160 p_course_cd => p_v_course_cd,
161 p_version_number => p_n_course_version,
162 p_cal_type => p_v_cal_type,
163 p_location_cd => p_v_location_cd,
164 p_attendance_mode => p_v_attendance_mode,
165 p_attendance_type => p_v_attendance_type);
166
167 --If No Trigger Has Been Fired then Return False
168 IF l_v_trigger_fired IS NULL THEN
169 RETURN 'FALSE';
170 ELSE
171 RETURN 'TRUE';
172 END IF;
173 END IF;
174 END IF; -- END OF THE PARAMETER VALIDATION.
175 END check_stdnt_prg_att_liable;
176
177
178 FUNCTION finp_get_currency(
179 p_fee_cal_type IN IGS_CA_TYPE.CAL_TYPE%TYPE ,
180 p_fee_ci_sequence_num IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE ,
181 p_s_relation_type IN VARCHAR2,
182 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE ,
183 p_fee_category IN IGS_FI_FEE_CAT_ALL.FEE_CAT%TYPE )
184 RETURN VARCHAR2 AS
185 /*----------------------------------------------------------------------------
186 || Created By :
187 || Created On :
188 || Purpose :
189 || Known limitations, enhancements or remarks :
190 || Change History :
191 || Who When What
192 || (reverse chronological order - newest change first)
193 || vvutukur 1-Dec-2002 Enh#2584986.Removed the references to igs_fi_cur. Instead referenced the currency
194 || that is set up in System Options Form.Also removed the references to igs_fi_fee_pay_schd
195 || as the same had been obsoleted.
196 ----------------------------------------------------------------------------*/
197
198 BEGIN
199 DECLARE
200 --Cursor to fetch the currency code that is setup in System Options Form.
201 CURSOR cur_ctrl IS
202 SELECT currency_cd
203 FROM igs_fi_control;
204
205 --Cursor to fetch the currency code value for a fee category
206 CURSOR c_fc (cp_fee_cat IGS_FI_FEE_CAT.FEE_CAT%TYPE) IS
207 SELECT currency_cd
208 FROM IGS_FI_FEE_CAT fc
209 WHERE FEE_CAT = cp_fee_cat;
210 -- this cursor finds the relation type value for a fee ass rate
211 CURSOR c_far ( cp_fee_type IGS_FI_FEE_AS_RATE.FEE_TYPE%TYPE,
212 cp_fee_cal_type IGS_FI_FEE_AS_RATE.fee_cal_type%TYPE,
213 cp_fee_ci_seq_num
214 IGS_FI_FEE_AS_RATE.fee_ci_sequence_number%TYPE,
215 cp_fee_cat IGS_FI_FEE_CAT.FEE_CAT%TYPE) IS
216 SELECT DISTINCT
217 s_relation_type
218 FROM IGS_FI_FEE_AS_RATE
219 WHERE FEE_TYPE = cp_fee_type AND
220 fee_cal_type = cp_fee_cal_type AND
221 fee_ci_sequence_number = cp_fee_ci_seq_num AND
222 NVL(FEE_CAT, cp_fee_cat) = cp_fee_cat;
223
224 v_currency_cd igs_fi_control.currency_cd%TYPE;
225 v_s_relation_type IGS_FI_FEE_AS_RATE.s_relation_type%TYPE;
226 l_ctrl_currency igs_fi_control.currency_cd%TYPE;
227
228 BEGIN
229 OPEN cur_ctrl;
230 FETCH cur_ctrl INTO l_ctrl_currency;
231 CLOSE cur_ctrl;
232
233 -- Determine where to source the currency code
234 IF NVL(p_s_relation_type, 'NULL') <> 'NULL' THEN
235 IF p_s_relation_type = 'FTCI' THEN
236 --Return the currency code value that is set up in System Options Form.
237 RETURN l_ctrl_currency;
238 ELSE
239 IF NVL(p_fee_category, 'NULL') <> 'NULL' THEN
240 -- get fee category currency.
241 OPEN c_fc (p_fee_category);
242 FETCH c_fc INTO v_currency_cd;
243 CLOSE c_fc;
244 IF v_currency_cd IS NULL THEN
245 --Return the currency code value that is set up in System Options Form.
246 RETURN l_ctrl_currency;
247 END IF;
248 RETURN v_currency_cd;
249 ELSE
250 --Return the currency code value that is set up in System Options Form.
251 RETURN l_ctrl_currency;
252 END IF;
253 END IF;
254 ELSE
255 IF NVL(p_fee_cal_type, 'NULL') <> 'NULL' AND
256 NVL(p_fee_ci_sequence_num, 0) <> 0 AND
257 NVL(p_fee_type, 'NULL') <> 'NULL' AND
258 NVL(p_fee_category, 'NULL') <> 'NULL' THEN
259 OPEN c_far ( p_fee_type,
260 p_fee_cal_type,
261 p_fee_ci_sequence_num,
262 p_fee_category);
263 FETCH c_far INTO v_s_relation_type;
264 CLOSE c_far;
265 IF v_s_relation_type = 'FTCI' THEN
266 --Return the currency code value that is set up in System Options Form.
267 RETURN l_ctrl_currency;
268 ELSE
269 -- get fee category currency.
270 OPEN c_fc (p_fee_category);
271 FETCH c_fc INTO v_currency_cd;
272 CLOSE c_fc;
273 IF v_currency_cd IS NULL THEN
274 --Return the currency code value that is set up in System Options Form.
275 RETURN l_ctrl_currency;
276 END IF;
277 RETURN v_currency_cd;
278 END IF;
279 ELSE
280 IF NVL(p_fee_category, 'NULL') <> 'NULL' THEN
281 -- get fee category currency.
282 OPEN c_fc (p_fee_category);
283 FETCH c_fc INTO v_currency_cd;
284 CLOSE c_fc;
285 IF v_currency_cd IS NULL THEN
286 --Return the currency code value that is set up in System Options Form.
287 RETURN l_ctrl_currency;
288 END IF;
289 RETURN v_currency_cd;
290 ELSE
291 --Return the currency code value that is set up in System Options Form.
292 RETURN l_ctrl_currency;
293 END IF;
294 END IF;
295 END IF;
296 END;
297 END finp_get_currency;
298 --
299 --
300 FUNCTION finp_get_fas_man_ind(
301 p_person_id IN NUMBER ,
302 p_fee_type IN VARCHAR2 ,
303 p_fee_cal_type IN VARCHAR2 ,
304 p_fee_ci_sequence_number IN NUMBER ,
305 p_fee_cat IN VARCHAR2 ,
306 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
307 p_transaction_cat IN VARCHAR2 )
308 RETURN VARCHAR2 AS
309
310 BEGIN
311 DECLARE
312 CURSOR c_fee_ass (cp_person_id IGS_FI_FEE_AS.person_id%TYPE,
313 cp_fee_type IGS_FI_FEE_AS.FEE_TYPE%TYPE,
314 cp_fee_cal_type IGS_FI_FEE_AS.fee_cal_type%TYPE,
315 cp_fee_ci_sequence_number IGS_FI_FEE_AS.fee_ci_sequence_number%TYPE,
316 cp_fee_cat IGS_FI_FEE_AS.FEE_CAT%TYPE,
317 cp_course_cd IGS_FI_FEE_AS.course_cd%TYPE,
318 cp_transaction_cat IGS_LOOKUPS_view.transaction_cat%TYPE) IS
319 SELECT fas.s_transaction_type
320 FROM IGS_FI_FEE_AS fas,
321 IGS_LOOKUPS_view strty
322 WHERE fas.person_id = cp_person_id AND
323 fas.fee_type = cp_fee_type AND
324 fas.fee_cal_type = cp_fee_cal_type AND
325 NVL(fas.FEE_CAT, ' ') = NVL(cp_fee_cat, ' ') AND
326 NVL(fas.course_cd, ' ') = NVL(cp_course_cd, ' ') AND
327 fas.logical_delete_dt IS NULL AND
328 fas.fee_ci_sequence_number = cp_fee_ci_sequence_number AND
329 strty.lookup_code = fas.s_transaction_type AND
330 strty.lookup_type = 'TRANSACTION_TYPE' AND
331 strty.transaction_cat = cp_transaction_cat AND
332 strty.system_generated_ind = 'N';
333 v_transaction_type IGS_FI_FEE_AS.S_TRANSACTION_TYPE%TYPE;
334 BEGIN
335 -- attempt to find manual entry transactions
336 OPEN c_fee_ass(p_person_id,
337 p_fee_type,
338 p_fee_cal_type,
339 p_fee_ci_sequence_number,
340 p_fee_cat,
341 p_course_cd,
342 p_transaction_cat);
343 LOOP
344 FETCH c_fee_ass INTO v_transaction_type;
345 IF (c_fee_ass%NOTFOUND) THEN
346 CLOSE c_fee_ass;
347 RETURN 'N';
348 ELSE
349 CLOSE c_fee_ass;
350 RETURN 'Y';
351 END IF;
352 END LOOP;
353 END;
354 END finp_get_fas_man_ind;
355 --
356 FUNCTION finp_get_fcfl_dai(
357 p_dt_alias_column_name IN VARCHAR2 ,
358 p_dai_seq_num_column_name IN VARCHAR2 ,
359 p_get_column_name IN VARCHAR2 ,
360 p_fee_cat IN IGS_FI_F_CAT_FEE_LBL_ALL.FEE_CAT%TYPE ,
361 p_fee_cal_type IN IGS_FI_F_CAT_FEE_LBL_ALL.fee_cal_type%TYPE ,
362 p_fee_ci_sequence_number IN IGS_FI_F_CAT_FEE_LBL_ALL.fee_ci_sequence_number%TYPE ,
363 p_fee_type IN IGS_FI_F_CAT_FEE_LBL_ALL.FEE_TYPE%TYPE )
364 RETURN VARCHAR2 AS
365
366 BEGIN
367 DECLARE
368 -- cursor to get dt alias's for the fee type calendar instance
369 CURSOR c_ftci_dai (cp_dt_alias_column_name user_tab_columns.column_name%TYPE,
370 cp_dai_seq_num_column_name user_tab_columns.column_name%TYPE,
371 cp_fee_cal_type IGS_FI_F_CAT_FEE_LBL.fee_cal_type%TYPE ,
372 cp_fee_ci_sequence_number IGS_FI_F_CAT_FEE_LBL.fee_ci_sequence_number%TYPE,
373 cp_fee_type IGS_FI_F_CAT_FEE_LBL.FEE_TYPE%TYPE ) IS
374 SELECT DECODE(cp_dt_alias_column_name,
375 'START_DT_ALIAS', ftci.start_dt_alias,
376 'END_DT_ALIAS', ftci.end_dt_alias,
377 'RETRO_DT_ALIAS', ftci.retro_dt_alias),
378 DECODE(cp_dai_seq_num_column_name,
379 'START_DAI_SEQUENCE_NUMBER', ftci.start_dai_sequence_number,
380 'END_DAI_SEQUENCE_NUMBER', ftci.end_dai_sequence_number,
381 'RETRO_DAI_SEQUENCE_NUMBER', ftci.retro_dai_sequence_number)
382 FROM IGS_FI_F_TYP_CA_INST ftci
383 WHERE ftci.FEE_TYPE = cp_fee_type AND
384 ftci.fee_cal_type = cp_fee_cal_type AND
385 ftci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
386 -- cursor to get dt alias's for the fee category calendar instance
387 CURSOR c_fcci_dai (cp_dt_alias_column_name user_tab_columns.column_name%TYPE,
388 cp_dai_seq_num_column_name user_tab_columns.column_name%TYPE,
389 cp_fee_cat IGS_FI_F_CAT_FEE_LBL.FEE_CAT%TYPE ,
390 cp_fee_cal_type IGS_FI_F_CAT_FEE_LBL.fee_cal_type%TYPE ,
391 cp_fee_ci_sequence_number
392 IGS_FI_F_CAT_FEE_LBL.fee_ci_sequence_number%TYPE) IS
393 SELECT DECODE(cp_dt_alias_column_name,
394 'START_DT_ALIAS', fcci.start_dt_alias,
395 'END_DT_ALIAS', fcci.end_dt_alias,
396 'RETRO_DT_ALIAS', fcci.retro_dt_alias),
397 DECODE(cp_dai_seq_num_column_name,
398 'START_DAI_SEQUENCE_NUMBER', fcci.start_dai_sequence_number,
399 'END_DAI_SEQUENCE_NUMBER', fcci.end_dai_sequence_number,
400 'RETRO_DAI_SEQUENCE_NUMBER', fcci.retro_dai_sequence_number)
401 FROM IGS_FI_F_CAT_CA_INST fcci
402 WHERE fcci.FEE_CAT = cp_fee_cat AND
403 fcci.fee_cal_type = cp_fee_cal_type AND
404 fcci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
405 v_ftci_alias_value IGS_CA_DA_INST_V.alias_val%TYPE;
406 v_ftci_dt_alias IGS_CA_DA.DT_ALIAS%TYPE;
407 v_ftci_dai_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
408 v_fcci_alias_value IGS_CA_DA_INST_V.alias_val%TYPE;
409 v_fcci_dt_alias IGS_CA_DA.DT_ALIAS%TYPE;
410 v_fcci_dai_seq_num IGS_CA_DA_INST.sequence_number%TYPE;
411 BEGIN
412 -- get the required dt alias from the fee type calendar instance
413 OPEN c_ftci_dai (p_dt_alias_column_name,
414 p_dai_seq_num_column_name,
415 p_fee_cal_type,
416 p_fee_ci_sequence_number,
417 p_fee_type);
418 FETCH c_ftci_dai INTO v_ftci_dt_alias,
419 v_ftci_dai_seq_num;
420 CLOSE c_ftci_dai;
421 IF v_ftci_dt_alias IS NOT NULL THEN
422 v_ftci_alias_value := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(
423 v_ftci_dt_alias,
424 v_ftci_dai_seq_num,
425 p_fee_cal_type,
426 p_fee_ci_sequence_number);
427 ELSE
428 v_ftci_alias_value := NULL;
429 END IF;
430 -- get the required dt alias from the fee category calendar instance
431 OPEN c_fcci_dai (p_dt_alias_column_name,
432 p_dai_seq_num_column_name,
433 p_fee_cat,
434 p_fee_cal_type,
435 p_fee_ci_sequence_number);
436 FETCH c_fcci_dai INTO v_fcci_dt_alias,
437 v_fcci_dai_seq_num;
438 CLOSE c_fcci_dai;
439 IF v_fcci_dt_alias IS NOT NULL THEN
440 v_fcci_alias_value := IGS_CA_GEN_001.CALP_GET_ALIAS_VAL(v_fcci_dt_alias,
441 v_fcci_dai_seq_num,
442 p_fee_cal_type,
443 p_fee_ci_sequence_number);
444 ELSE
445 v_ftci_alias_value := NULL;
446 END IF;
447 IF p_dt_alias_column_name = 'START_DT_ALIAS' THEN
448 -- return the latest start dt alias detail
449 IF v_ftci_alias_value > v_fcci_alias_value THEN
450 IF p_get_column_name = p_dt_alias_column_name THEN
451 RETURN v_ftci_dt_alias;
452 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
453 RETURN TO_CHAR(v_ftci_dai_seq_num);
454 END IF;
455 ELSE
456 IF p_get_column_name = p_dt_alias_column_name THEN
457 RETURN v_fcci_dt_alias;
458 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
459 RETURN TO_CHAR(v_fcci_dai_seq_num);
460 END IF;
461 END IF;
462 ELSIF p_dt_alias_column_name = 'END_DT_ALIAS' THEN
463 -- return the earlist end dt alias detail
464 IF v_ftci_alias_value < v_fcci_alias_value THEN
465 IF p_get_column_name = p_dt_alias_column_name THEN
466 RETURN v_ftci_dt_alias;
467 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
468 RETURN TO_CHAR(v_ftci_dai_seq_num);
469 END IF;
470 ELSE
471 IF p_get_column_name = p_dt_alias_column_name THEN
472 RETURN v_fcci_dt_alias;
473 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
474 RETURN TO_CHAR(v_fcci_dai_seq_num);
475 END IF;
476 END IF;
477 ELSIF p_dt_alias_column_name = 'RETRO_DT_ALIAS' THEN
478 -- return the earliest retro dt alias detail defined
479 IF v_ftci_alias_value IS NULL AND
480 v_fcci_alias_value IS NULL THEN
481 RETURN NULL;
482 END IF;
483 IF NVL(v_ftci_alias_value, v_fcci_alias_value) <
484 NVL(v_fcci_alias_value, v_ftci_alias_value) THEN
485 IF p_get_column_name = p_dt_alias_column_name THEN
486 RETURN NVL(v_ftci_dt_alias, v_fcci_dt_alias);
487 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
488 RETURN TO_CHAR(NVL(v_ftci_dai_seq_num, v_fcci_dai_seq_num));
489 END IF;
490 ELSE
491 IF p_get_column_name = p_dt_alias_column_name THEN
492 RETURN NVL(v_fcci_dt_alias, v_ftci_dt_alias);
493 ELSIF p_get_column_name = p_dai_seq_num_column_name THEN
494 RETURN TO_CHAR(NVL(v_fcci_dai_seq_num, v_ftci_dai_seq_num));
495 END IF;
496 END IF;
497 END IF;
498 RETURN NULL;
499 END;
500 END finp_get_fcfl_dai;
501 --
502 FUNCTION finp_get_fdf_end_dt(
503 p_fee_type IN VARCHAR2 ,
504 p_fee_cal_type IN VARCHAR2 ,
505 p_fee_ci_sequence_number IN NUMBER ,
506 p_override_formula IN NUMBER ,
507 p_fee_cat IN VARCHAR2 )
508 RETURN DATE AS
509
510 BEGIN
511 return NULL;
512 END finp_get_fdf_end_dt;
513
514 --
515 FUNCTION finp_get_fdf_st_dt(
516 p_fee_type IN VARCHAR2 ,
517 p_fee_cal_type IN VARCHAR2 ,
518 p_fee_ci_sequence_number IN NUMBER ,
519 p_override_formula IN NUMBER ,
520 p_fee_cat IN VARCHAR2 )
521 RETURN DATE AS
522
523 BEGIN
524 return NULL;
525 END finp_get_fdf_st_dt;
526 --
527 FUNCTION finp_get_fee_trigger(
528 p_fee_cat IN IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE ,
529 p_fee_cal_type IN IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE ,
530 p_fee_ci_sequence_number IN IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE ,
531 p_fee_type IN IGS_FI_F_CAT_FEE_LBL_ALL.FEE_TYPE%TYPE ,
532 p_s_fee_trigger_cat IN IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE ,
533 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
534 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
535 p_version_number IN IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
536 p_cal_type IN IGS_EN_STDNT_PS_ATT_ALL.CAL_TYPE%TYPE ,
537 p_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.location_cd%TYPE ,
538 p_attendance_mode IN IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_MODE%TYPE ,
539 p_attendance_type IN IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_TYPE%TYPE )
540 RETURN VARCHAR2 AS
541
542 BEGIN
543 DECLARE
544 CURSOR c_ft IS
545 SELECT ft.s_fee_trigger_cat
546 FROM IGS_FI_FEE_TYPE ft
547 WHERE ft.FEE_TYPE = p_fee_type;
548 CURSOR c_ctft IS
549 SELECT ctft.COURSE_TYPE
550 FROM IGS_PS_TYPE_FEE_TRG ctft,
551 IGS_PS_VER cv
552 WHERE ctft.FEE_CAT = p_fee_cat AND
553 ctft.fee_cal_type = p_fee_cal_type AND
554 ctft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
555 ctft.FEE_TYPE = p_fee_type AND
556 cv.course_cd = p_course_cd AND
557 cv.version_number = p_version_number AND
558 cv.COURSE_TYPE = ctft.COURSE_TYPE AND
559 ctft.logical_delete_dt IS NULL;
560 CURSOR c_cgft IS
561 SELECT cgft.course_group_cd
562 FROM IGS_PS_GRP_FEE_TRG cgft,
563 IGS_PS_GRP_MBR cgm
564 WHERE cgft.FEE_CAT = p_fee_cat AND
565 cgft.fee_cal_type = p_fee_cal_type AND
566 cgft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
567 cgft.FEE_TYPE = p_fee_type AND
568 cgm.course_cd = p_course_cd AND
569 cgm.version_number = p_version_number AND
570 cgm.course_group_cd = cgft.course_group_cd AND
571 cgft.logical_delete_dt IS NULL;
572 CURSOR c_cft IS
573 SELECT cft.fee_trigger_group_number
574 FROM IGS_PS_FEE_TRG cft
575 WHERE cft.FEE_CAT = p_fee_cat AND
576 cft.fee_cal_type = p_fee_cal_type AND
577 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
578 cft.FEE_TYPE = p_fee_type AND
579 cft.course_cd = p_course_cd AND
580 (cft.version_number IS NULL OR
581 cft.version_number = p_version_number) AND
582 p_cal_type LIKE NVL(cft.CAL_TYPE, '%') AND
583 p_location_cd LIKE NVL(cft.location_cd, '%') AND
584 p_attendance_mode LIKE NVL(cft.ATTENDANCE_MODE, '%') AND
585 p_attendance_type LIKE NVL(cft.ATTENDANCE_TYPE, '%') AND
586 cft.logical_delete_dt IS NULL;
587 CURSOR c_uft IS
588 SELECT uft.unit_cd,
589 uft.version_number,
590 uft.CAL_TYPE,
591 uft.ci_sequence_number,
592 uft.location_cd,
593 uft.UNIT_CLASS,
594 uft.fee_trigger_group_number
595 FROM IGS_FI_UNIT_FEE_TRG uft
596 WHERE uft.FEE_CAT = p_fee_cat AND
597 uft.fee_cal_type = p_fee_cal_type AND
598 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
599 uft.FEE_TYPE = p_fee_type AND
600 uft.logical_delete_dt IS NULL;
601 CURSOR c_sua (cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
602 cp_version_number
603 IGS_EN_SU_ATTEMPT.version_number%TYPE,
604 cp_cal_type IGS_EN_SU_ATTEMPT.CAL_TYPE%TYPE,
605 cp_ci_sequence_number
606 IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
607 cp_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE,
608 cp_unit_class IGS_EN_SU_ATTEMPT.UNIT_CLASS%TYPE) IS
609 SELECT 'X'
610 FROM IGS_EN_SU_ATTEMPT sua,
611 IGS_LOOKUPS_view suas
612 WHERE sua.person_id = p_person_id AND
613 sua.course_cd = p_course_cd AND
614 sua.unit_cd = cp_unit_cd AND
615 (cp_version_number IS NULL OR
616 sua.version_number = cp_version_number) AND
617 (cp_cal_type IS NULL OR
618 sua.CAL_TYPE = cp_cal_type) AND
619 (cp_ci_sequence_number IS NULL OR
620 sua.ci_sequence_number = cp_ci_sequence_number) AND
621 (cp_location_cd IS NULL OR
622 sua.location_cd = cp_location_cd) AND
623 (cp_unit_class IS NULL OR
624 sua.UNIT_CLASS = cp_unit_class) AND
625 suas.lookup_code = sua.unit_attempt_status AND
626 suas.lookup_type = 'UNIT_ATTEMPT_STATUS' AND
627 suas.fee_ass_ind = 'Y';
628 CURSOR c_usft IS
629 SELECT usft.unit_set_cd,
630 usft.version_number,
631 usft.fee_trigger_group_number
632 FROM IGS_EN_UNITSETFEETRG usft
633 WHERE usft.FEE_CAT = p_fee_cat AND
634 usft.fee_cal_type = p_fee_cal_type AND
635 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
636 usft.FEE_TYPE = p_fee_type AND
637 usft.logical_delete_dt IS NULL;
638 CURSOR c_susa (cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
639 cp_version_number IGS_AS_SU_SETATMPT.us_version_number%TYPE) IS
640 SELECT 'X'
641 FROM IGS_AS_SU_SETATMPT susa
642 WHERE susa.person_id = p_person_id AND
643 susa.course_cd = p_course_cd AND
644 susa.unit_set_cd = cp_unit_set_cd AND
645 susa.us_version_number = cp_version_number AND
646 susa.student_confirmed_ind = 'Y' AND
647 (susa.selection_dt IS NOT NULL AND
648 TRUNC(SYSDATE) >= TRUNC(susa.selection_dt)) AND
649 (susa.end_dt IS NULL OR
650 TRUNC(SYSDATE) <= TRUNC(susa.end_dt)) AND
651 (susa.rqrmnts_complete_dt IS NULL OR
652 TRUNC(SYSDATE) <= TRUNC(susa.rqrmnts_complete_dt));
653 v_s_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
654 v_check CHAR(1);
655 --------------------------------------------------------------------------------
656 FUNCTION finpl_val_trig_group (p_fee_trigger_group_number
657 IGS_FI_FEE_TRG_GRP.fee_trigger_group_number%TYPE)
658 RETURN BOOLEAN AS
659 BEGIN
660 -- validate the fee trigger group members match the student
661 DECLARE
662 CURSOR c_ftgv_course IS
663 SELECT lkp.lookup_code trigger_type_code,
664 cft.course_cd code,
665 cft.version_number
666 FROM IGS_PS_FEE_TRG cft,
667 IGS_PS_VER crv,
668 IGS_LOOKUP_VALUES lkp
669 WHERE cft.FEE_CAT = p_fee_cat AND
670 cft.fee_cal_type = p_fee_cal_type AND
671 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
672 cft.FEE_TYPE = p_fee_type AND
673 cft.fee_trigger_group_number = p_fee_trigger_group_number AND
674 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
675 lkp.lookup_code = 'COURSE' AND
676 cft.fee_trigger_group_number IS NOT NULL AND
677 cft.logical_delete_dt IS NULL AND
678 cft.course_cd = crv.course_cd AND
679 (cft.version_number = crv.version_number OR
680 (cft.version_number IS NULL AND
681 crv.version_number = ( SELECT MAX(crv2.version_number)
682 FROM IGS_PS_VER crv2
683 WHERE crv2.course_cd = crv.course_cd)));
684 CURSOR c_ftgv_unit IS
685 SELECT lkp.lookup_code trigger_type_code,
686 uft.unit_cd code,
687 uft.version_number
688 FROM IGS_FI_UNIT_FEE_TRG uft,
689 IGS_PS_UNIT_VER uv,
690 IGS_LOOKUP_VALUES lkp
691 WHERE uft.FEE_CAT = p_fee_cat AND
692 uft.fee_cal_type = p_fee_cal_type AND
693 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
694 uft.FEE_TYPE = p_fee_type AND
695 uft.fee_trigger_group_number = p_fee_trigger_group_number AND
696 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
697 lkp.lookup_code = 'UNIT' AND
698 uft.fee_trigger_group_number IS NOT NULL AND
699 uft.logical_delete_dt IS NULL AND
700 uft.unit_cd = uv.unit_cd AND
701 (uft.version_number = uv.version_number OR
702 (uft.version_number IS NULL AND
703 uv.version_number = ( SELECT MAX(uv2.version_number)
704 FROM IGS_PS_UNIT_VER uv2
705 WHERE uv2.unit_cd = uv.unit_cd)));
706 CURSOR c_ftgv_unitset IS
707 SELECT usft.unit_set_cd code, usft.version_number,
708 lkp.lookup_code trigger_type_code
709 FROM IGS_EN_UNITSETFEETRG usft,
710 IGS_EN_UNIT_SET us,
711 IGS_LOOKUP_VALUES lkp
712 WHERE usft.FEE_CAT = p_fee_cat AND
713 usft.fee_cal_type = p_fee_cal_type AND
714 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
715 usft.FEE_TYPE = p_fee_type AND
716 usft.fee_trigger_group_number = p_fee_trigger_group_number AND
717 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
718 lkp.lookup_code = 'UNITSET' AND
719 usft.fee_trigger_group_number IS NOT NULL AND
720 usft.logical_delete_dt IS NULL AND
721 usft.unit_set_cd = us.unit_set_cd AND
722 usft.version_number = us.version_number;
723
724 CURSOR c_ftg_uft (
725 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
726 cp_version_number
727 IGS_EN_SU_ATTEMPT.version_number%TYPE)IS
728 SELECT uft.unit_cd,
729 uft.version_number,
730 uft.CAL_TYPE,
731 uft.ci_sequence_number,
732 uft.location_cd,
733 uft.UNIT_CLASS,
734 uft.fee_trigger_group_number
735 FROM IGS_FI_UNIT_FEE_TRG uft
736 WHERE uft.FEE_CAT = p_fee_cat AND
737 uft.fee_cal_type = p_fee_cal_type AND
738 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
739 uft.FEE_TYPE = p_fee_type AND
740 uft.unit_cd = cp_unit_cd AND
741 (uft.version_number IS NULL OR
742 uft.version_number = cp_version_number) AND
743 uft.logical_delete_dt IS NULL;
744 CURSOR c_ftg_usft (
745 cp_unit_set_cd
746 IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
747 cp_version_number
748 IGS_AS_SU_SETATMPT.us_version_number%TYPE) IS
749 SELECT usft.unit_set_cd,
750 usft.version_number,
751 usft.fee_trigger_group_number
752 FROM IGS_EN_UNITSETFEETRG usft
753 WHERE usft.FEE_CAT = p_fee_cat AND
754 usft.fee_cal_type = p_fee_cal_type AND
755 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
756 usft.FEE_TYPE = p_fee_type AND
757 usft.unit_set_cd = cp_unit_set_cd AND
758 usft.version_number = cp_version_number AND
759 usft.logical_delete_dt IS NULL;
760 v_trigger_group_fired BOOLEAN;
761 BEGIN
762 -- check the fee trigger group members
763 v_trigger_group_fired := TRUE;
764 FOR v_ftgv_rec IN c_ftgv_course LOOP
765 -- check for matching student IGS_PS_COURSE attempt
766 IF (v_ftgv_rec.code <> p_course_cd OR
767 NVL(v_ftgv_rec.version_number, p_version_number) <>
768 p_version_number) THEN
769 v_trigger_group_fired := FALSE;
770 RETURN v_trigger_group_fired;
771 END IF;
772 END LOOP;
773 FOR v_ftgv_rec IN c_ftgv_unit LOOP
774 FOR v_ftg_uft_rec IN c_ftg_uft ( v_ftgv_rec.code,
775 v_ftgv_rec.version_number) LOOP
776 -- check for matching student IGS_PS_UNIT attempt
777 OPEN c_sua ( v_ftg_uft_rec.unit_cd,
778 v_ftg_uft_rec.version_number,
779 v_ftg_uft_rec.CAL_TYPE,
780 v_ftg_uft_rec.ci_sequence_number,
781 v_ftg_uft_rec.location_cd,
782 v_ftg_uft_rec.UNIT_CLASS);
783 FETCH c_sua INTO v_check;
784 IF (c_sua%NOTFOUND) THEN
785 CLOSE c_sua;
786 v_trigger_group_fired := FALSE;
787 RETURN v_trigger_group_fired;
788 END IF;
789 CLOSE c_sua;
790 END LOOP;
791 END LOOP;
792 FOR v_ftgv_rec IN c_ftgv_unitset LOOP
793 FOR v_ftg_usft_rec IN c_ftg_usft ( v_ftgv_rec.code,
794 v_ftgv_rec.version_number) LOOP
795 -- check for matching student IGS_PS_UNIT set attempt
796 OPEN c_susa ( v_ftg_usft_rec.unit_set_cd,
797 v_ftg_usft_rec.version_number);
798 FETCH c_susa INTO v_check;
799 IF (c_susa%NOTFOUND) THEN
800 CLOSE c_susa;
801 v_trigger_group_fired := FALSE;
802 RETURN v_trigger_group_fired;
803 END IF;
804 CLOSE c_susa;
805 END LOOP;
806 END LOOP;
807 RETURN v_trigger_group_fired;
808 END;
809 END finpl_val_trig_group;
810 --------------------------------------------------------------------------------
811 BEGIN -- finp_get_fee_trigger
812 -- This routine checks the students enrolment details to test
813 -- for matching a fee trigger.
814 IF p_s_fee_trigger_cat IS NULL THEN
815 OPEN c_ft;
816 FETCH c_ft INTO v_s_fee_trigger_cat;
817 CLOSE c_ft;
818 ELSE
819 v_s_fee_trigger_cat := p_s_fee_trigger_cat;
820 END IF;
821 -- added AUDIT and SPECIAL In the If condition.
822 IF (v_s_fee_trigger_cat = 'INSTITUTN') THEN
823 -- IGS_GE_NOTE, IGS_OR_INSTITUTION fees have no triggers - they always apply.
824 -- Trigger Fired
825 RETURN 'INSTITUTN';
826 ELSIF (v_s_fee_trigger_cat = 'COURSE') THEN
827 FOR v_ctft_rec IN c_ctft LOOP
828 -- Trigger Fired
829 RETURN 'CTFT';
830 END LOOP;
831 FOR v_cgft_rec IN c_cgft LOOP
832 -- Trigger Fired
833 RETURN 'CGFT';
834 END LOOP;
835 FOR v_cft_rec IN c_cft LOOP
836 -- Trigger Fired
837 RETURN 'CFT';
838 END LOOP;
839 -- added AUDIT In the If condition.
840 ELSIF (v_s_fee_trigger_cat IN ('UNIT','AUDIT')) THEN
841 FOR v_uft_rec IN c_uft LOOP
842 OPEN c_sua ( v_uft_rec.unit_cd,
843 v_uft_rec.version_number,
844 v_uft_rec.CAL_TYPE,
845 v_uft_rec.ci_sequence_number,
846 v_uft_rec.location_cd,
847 v_uft_rec.UNIT_CLASS);
848 FETCH c_sua INTO v_check;
849 IF (c_sua%FOUND) THEN
850 CLOSE c_sua;
851 -- Trigger Fired
852 IF(v_s_fee_trigger_cat = 'UNIT') THEN
853 RETURN 'UFT';
854 ELSE
855 RETURN 'AUDIT';
856 END IF;
857 END IF;
858 CLOSE c_sua;
859 END LOOP;
860 ELSIF (v_s_fee_trigger_cat = 'UNITSET') THEN
861 FOR v_usft_rec IN c_usft LOOP
862 OPEN c_susa ( v_usft_rec.unit_set_cd,
863 v_usft_rec.version_number);
864 FETCH c_susa INTO v_check;
865 IF (c_susa%FOUND) THEN
866 CLOSE c_susa;
867 -- Trigger Fired
868 RETURN 'USFT';
869 END IF;
870 CLOSE c_susa;
871 END LOOP;
872 ELSIF (v_s_fee_trigger_cat = 'COMPOSITE') THEN
873 -- check IGS_PS_COURSE fee triggers
874 FOR v_cft_rec IN c_cft LOOP
875 IF (v_cft_rec.fee_trigger_group_number IS NULL) THEN
876 -- Trigger Fired
877 RETURN 'CFT';
878 ELSE
879 -- check the fee trigger group members
880 IF (finpl_val_trig_group(v_cft_rec.fee_trigger_group_number) = TRUE) THEN
881 -- Trigger Fired
882 RETURN 'COMPOSITE';
883 END IF;
884 END IF;
885 END LOOP;
886 -- check IGS_PS_UNIT fee triggers
887 FOR v_uft_rec IN c_uft LOOP
888 IF (v_uft_rec.fee_trigger_group_number IS NOT NULL) THEN
889 -- check the fee trigger group members
890 IF (finpl_val_trig_group(v_uft_rec.fee_trigger_group_number) = TRUE) THEN
891 -- Trigger Fired
892 RETURN 'COMPOSITE';
893 END IF;
894 ELSE
895 OPEN c_sua (v_uft_rec.unit_cd,
896 v_uft_rec.version_number,
897 v_uft_rec.CAL_TYPE,
898 v_uft_rec.ci_sequence_number,
899 v_uft_rec.location_cd,
900 v_uft_rec.UNIT_CLASS);
901 FETCH c_sua INTO v_check;
902 IF (c_sua%FOUND) THEN
903 CLOSE c_sua;
904 -- Trigger Fired
905 RETURN 'UFT';
906 END IF;
907 CLOSE c_sua;
908 END IF;
909 END LOOP;
910 -- check IGS_PS_UNIT set fee triggers
911 FOR v_usft_rec IN c_usft LOOP
912 IF (v_usft_rec.fee_trigger_group_number IS NOT NULL) THEN
913 -- check the fee trigger group members
914 IF (finpl_val_trig_group(v_usft_rec.fee_trigger_group_number) = TRUE) THEN
915 -- Trigger Fired
916 RETURN 'COMPOSITE';
917 END IF;
918 ELSE
919 OPEN c_susa ( v_usft_rec.unit_set_cd,
920 v_usft_rec.version_number);
921 FETCH c_susa INTO v_check;
922 IF (c_susa%FOUND) THEN
923 CLOSE c_susa;
924 -- Trigger Fired
925 RETURN 'USFT';
926 END IF;
927 CLOSE c_susa;
928 END IF;
929 END LOOP;
930 END IF;
931 -- Trigger did not fire
932 RETURN NULL;
933 END;
934 END finp_get_fee_trigger;
935 --
936 --
937
938 FUNCTION finp_get_fps_end_dt(
939 p_fee_cal_type IN IGS_FI_F_TYP_CA_INST_ALL.fee_cal_type%TYPE,
940 p_fee_ci_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.fee_ci_sequence_number%TYPE,
941 p_s_relation_type IN VARCHAR2 ,
942 p_fee_type IN IGS_FI_F_TYP_CA_INST_ALL.FEE_TYPE%TYPE,
943 p_fee_cat IN IGS_FI_FEE_CAT_ALL.FEE_CAT%TYPE ,
944 p_schedule_number IN NUMBER,
945 p_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.START_DT_ALIAS%TYPE,
946 p_dai_sequence_num IN NUMBER )
947 RETURN DATE AS
948
949 BEGIN
950 return NULL;
951 END finp_get_fps_end_dt;
952 --
953 --
954
955 FUNCTION finp_get_fps_start_dt(
956 p_fee_cal_type IN IGS_FI_F_TYP_CA_INST_ALL.fee_cal_type%TYPE,
957 p_fee_ci_sequence_num IN IGS_FI_F_TYP_CA_INST_ALL.fee_ci_sequence_number%TYPE,
958 p_s_relation_type IN VARCHAR2 ,
959 p_fee_type IN IGS_FI_F_TYP_CA_INST_ALL.FEE_TYPE%TYPE,
960 p_fee_cat IN IGS_FI_FEE_CAT_ALL.FEE_CAT%TYPE,
961 p_schedule_number IN NUMBER,
962 p_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.START_DT_ALIAS%TYPE,
963 p_dai_sequence_num IN NUMBER )
964 RETURN DATE AS
965 /*----------------------------------------------------------------------------
966 || Created By :
967 || Created On :
968 || Purpose :
969 || Known limitations, enhancements or remarks :
970 || Change History :
971 || Who When What
972 || (reverse chronological order - newest change first)
973 || VVUTUKUR 1-DEC-2002 Enh#2584986.Removed references to igs_fi_fee_pay_schd.
974 ----------------------------------------------------------------------------*/
975 BEGIN
976 return NULL;
977 END finp_get_fps_start_dt;
978 --
979 FUNCTION finp_get_frtns_end_dt(
980 p_fee_cal_type IN IGS_FI_FEE_RET_SCHD.fee_cal_type%TYPE ,
981 p_fee_ci_sequence_num IN IGS_FI_FEE_RET_SCHD.fee_ci_sequence_number%TYPE ,
982 p_s_relation_type IN IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE ,
983 p_fee_type IN IGS_FI_FEE_RET_SCHD.FEE_TYPE%TYPE ,
984 p_fee_cat IN IGS_FI_FEE_RET_SCHD.FEE_CAT%TYPE ,
985 p_dt_alias IN IGS_FI_FEE_RET_SCHD.DT_ALIAS%TYPE ,
986 p_dai_sequence_num IN NUMBER )
987 RETURN DATE AS
988
989 BEGIN
990 DECLARE
991 -- this cursor finds the next retention schedule entry
992 -- date alias instance value
993 CURSOR c_frtns (cp_fee_cal_type IGS_FI_FEE_RET_SCHD.fee_cal_type%TYPE,
994 cp_fee_ci_sequence_num IGS_FI_FEE_RET_SCHD.fee_ci_sequence_number%TYPE,
995 cp_s_relation_type IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE,
996 cp_fee_type IGS_FI_FEE_RET_SCHD.FEE_TYPE%TYPE,
997 cp_fee_cat IGS_FI_FEE_RET_SCHD.FEE_CAT%TYPE,
998 cp_alias_val IGS_CA_DA_INST_V.alias_val%TYPE) IS
999 SELECT daiv.alias_val
1000 FROM IGS_FI_FEE_RET_SCHD frtns,
1001 IGS_CA_DA_INST_V daiv
1002 WHERE frtns.fee_cal_type = cp_fee_cal_type AND
1003 frtns.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
1004 frtns.s_relation_type = cp_s_relation_type AND
1005 NVL(frtns.FEE_TYPE,'NULL') = NVL(cp_fee_type,'NULL') AND
1006 NVL(frtns.FEE_CAT,'NULL') = NVL(cp_fee_cat,'NULL') AND
1007 daiv.DT_ALIAS =frtns.DT_ALIAS AND
1008 daiv.sequence_number = frtns.dai_sequence_number AND
1009 daiv.CAL_TYPE = frtns.fee_cal_type AND
1010 daiv.ci_sequence_number =frtns.fee_ci_sequence_number AND
1011 daiv.alias_val > cp_alias_val
1012 ORDER BY daiv.alias_val ASC;
1013 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
1014 -------------------------------------------------------------------------------
1015 FUNCTION finl_get_ass_end_dt(
1016 p_fee_cal_type IN IGS_FI_FEE_RET_SCHD.fee_cal_type%TYPE,
1017 p_fee_ci_sequence_num IN IGS_FI_FEE_RET_SCHD.fee_ci_sequence_number%TYPE,
1018 p_s_relation_type IN IGS_FI_FEE_RET_SCHD.s_relation_type%TYPE,
1019 p_fee_type IN IGS_FI_FEE_RET_SCHD.FEE_TYPE%TYPE,
1020 p_fee_cat IN IGS_FI_FEE_RET_SCHD.FEE_CAT%TYPE)
1021 RETURN DATE AS
1022 BEGIN
1023 DECLARE
1024 -- this cursor finds the fee type cal instance end date alias instance value
1025 CURSOR c_ftci ( cp_fee_type IGS_FI_F_TYP_CA_INST.FEE_TYPE%TYPE,
1026 cp_fee_cal_type IGS_FI_F_TYP_CA_INST.fee_cal_type%TYPE,
1027 cp_fee_ci_sequence_num IGS_FI_F_TYP_CA_INST.fee_ci_sequence_number%TYPE) IS
1028 SELECT daiv.alias_val
1029 FROM IGS_CA_DA_INST_V daiv,
1030 IGS_FI_F_TYP_CA_INST ftci
1031 WHERE ftci.FEE_TYPE = cp_fee_type AND
1032 ftci.fee_cal_type = cp_fee_cal_type AND
1033 ftci.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
1034 daiv.DT_ALIAS = ftci.end_dt_alias AND
1035 daiv.sequence_number = ftci.end_dai_sequence_number AND
1036 daiv.CAL_TYPE = ftci.fee_cal_type AND
1037 daiv.ci_sequence_number = ftci.fee_ci_sequence_number;
1038 -- this cursor finds the fee cat cal instance end date alias instance value
1039 CURSOR c_fcci ( cp_fee_cat IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE,
1040 cp_fee_cal_type IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE,
1041 cp_fee_ci_sequence_num IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE) IS
1042 SELECT daiv.alias_val
1043 FROM IGS_CA_DA_INST_V daiv,
1044 IGS_FI_F_CAT_CA_INST fcci
1045 WHERE fcci.FEE_CAT = cp_fee_cat AND
1046 fcci.fee_cal_type = cp_fee_cal_type AND
1047 fcci.fee_ci_sequence_number = cp_fee_ci_sequence_num AND
1048 daiv.DT_ALIAS = fcci.end_dt_alias AND
1049 daiv.sequence_number = fcci.end_dai_sequence_number AND
1050 daiv.CAL_TYPE = fcci.fee_cal_type AND
1051 daiv.ci_sequence_number = fcci.fee_ci_sequence_number;
1052 v_alias_val IGS_CA_DA_INST_V.alias_val%TYPE;
1053 BEGIN
1054 -- check the retention schedule relationship
1055 IF p_s_relation_type = 'FTCI' THEN
1056 -- retention schedule is for a fee type cal instance
1057 OPEN c_ftci( p_fee_type,
1058 p_fee_cal_type,
1059 p_fee_ci_sequence_num);
1060 FETCH c_ftci INTO v_alias_val;
1061 CLOSE c_ftci;
1062 RETURN v_alias_val;
1063 ELSE
1064 -- retention schedule is for a fee cat cal instance or fee cat fee liability
1065 OPEN c_fcci( p_fee_cat,
1066 p_fee_cal_type,
1067 p_fee_ci_sequence_num);
1068 FETCH c_fcci INTO v_alias_val;
1069 CLOSE c_fcci;
1070 RETURN v_alias_val;
1071 END IF;
1072 END;
1073 END finl_get_ass_end_dt;
1074 -------------------------------------------------------------------------------
1075 BEGIN
1076 -- get the alias value for the current schedule
1077 v_alias_val := IGS_CA_GEN_001.calp_get_alias_val(p_dt_alias,
1078 p_dai_sequence_num,
1079 p_fee_cal_type,
1080 p_fee_ci_sequence_num);
1081 -- attempt to find the next retention schedule entry
1082 OPEN c_frtns(p_fee_cal_type,
1083 p_fee_ci_sequence_num,
1084 p_s_relation_type,
1085 p_fee_type,
1086 p_fee_cat,
1087 v_alias_val);
1088 FETCH c_frtns INTO v_alias_val;
1089 IF (c_frtns%NOTFOUND) THEN
1090 -- The fee assessment end date is used
1091 CLOSE c_frtns;
1092 RETURN finl_get_ass_end_dt(p_fee_cal_type,
1093 p_fee_ci_sequence_num,
1094 p_s_relation_type,
1095 p_fee_type,
1096 p_fee_cat);
1097 ELSE
1098 -- end alias value is the day prior to the next schedule
1099 CLOSE c_frtns;
1100 RETURN (v_alias_val - 1);
1101 END IF;
1102 END;
1103 END finp_get_frtns_end_dt;
1104 --
1105 FUNCTION finp_get_hecs_amt_pd(
1106 p_load_cal_type IN VARCHAR2 ,
1107 p_load_ci_sequence_number IN NUMBER ,
1108 p_person_id IN NUMBER ,
1109 p_course_cd IN VARCHAR2 )
1110 RETURN NUMBER AS
1111 /******************************************************************
1112 Change History
1113 Who When What
1114 schodava 21-Jan-2002 Enh # 2187247
1115 SFCR021 - LCI-FCI Relation
1116 ******************************************************************/
1117
1118 -- finp_get_hecs_amt_pd
1119 -- Routine to calculate the HECS amount paid. DETYA element 381.
1120 -- If no amount has been paid a zero value is returned.
1121
1122 cst_hecs CONSTANT VARCHAR2(10) := 'HECS';
1123 cst_payment CONSTANT VARCHAR2(10) := 'PAYMENT';
1124 cst_discount CONSTANT VARCHAR2(10) := 'DISCOUNT';
1125 v_fp_total NUMBER;
1126 v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1127 v_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1128 v_message_name fnd_new_messages.message_name%TYPE;
1129
1130 CURSOR c_fp_total(cp_fee_cal_type IN igs_ca_inst.cal_type%TYPE,
1131 cp_fee_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE) IS
1132 SELECT SUM(fas.transaction_amount/fas.exchange_rate)
1133 FROM IGS_FI_FEE_TYPE ft,
1134 IGS_FI_FEE_AS fas
1135 WHERE ft.s_fee_type = cst_hecs AND
1136 fas.person_id = p_person_id AND
1137 fas.course_cd = p_course_cd AND
1138 fas.FEE_TYPE = ft.FEE_TYPE AND
1139 fas.S_TRANSACTION_TYPE in
1140 (SELECT strty.lookup_code
1141 FROM IGS_LOOKUPS_view strty
1142 WHERE strty.lookup_code = fas.S_TRANSACTION_TYPE AND
1143 strty.lookup_type = 'TRANSACTION_TYPE' AND
1144 strty.transaction_cat = cst_payment) AND
1145 fas.S_TRANSACTION_TYPE <> cst_discount AND
1146 fas.logical_delete_dt IS NULL AND
1147 fas.fee_cal_type = cp_fee_cal_type AND
1148 fas.fee_ci_sequence_number = cp_fee_ci_sequence_number;
1149 BEGIN
1150 -- Enh # 2187247
1151 -- Derive the related Fee Calendar Instance for the passed Load
1152 -- Calendar Instance
1153
1154 IF igs_fi_gen_001.finp_get_lfci_reln(p_load_cal_type,
1155 p_load_ci_sequence_number,
1156 'LOAD',
1157 v_fee_cal_type,
1158 v_fee_ci_sequence_number,
1159 v_message_name) = TRUE THEN
1160 -- Get the total payments received for fee category fee liability assessments
1161 -- associated with a HECS fee with respect to the load calendar.
1162 OPEN c_fp_total(v_fee_cal_type,
1163 v_fee_ci_sequence_number);
1164 FETCH c_fp_total INTO v_fp_total;
1165 CLOSE c_fp_total;
1166 IF v_fp_total IS NULL THEN
1167 -- Record not found
1168 v_fp_total := 0;
1169 END IF;
1170 RETURN v_fp_total;
1171 ELSE
1172 -- Enh # 2187247
1173 -- The LCI cannot be determined
1174 v_fp_total := 0;
1175 END IF;
1176 RETURN v_fp_total;
1177 EXCEPTION
1178 WHEN OTHERS THEN
1179 IF c_fp_total%ISOPEN THEN
1180 CLOSE c_fp_total;
1181 END IF;
1182 RAISE;
1183 END finp_get_hecs_amt_pd;
1184 --
1185 FUNCTION finp_get_hecs_fee(
1186 p_load_cal_type IN VARCHAR2 ,
1187 p_load_ci_sequence_number IN NUMBER ,
1188 p_person_id IN NUMBER ,
1189 p_course_cd IN VARCHAR2 )
1190 RETURN NUMBER AS
1191 /******************************************************************
1192 Change History
1193 Who When What
1194 uudayapr 12-12-2003 bug#3080983 made the modification to the
1195 Cursor c_fp_total to point to the table
1196 IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
1197 schodava 21-Jan-2002 Enh # 2187247
1198 SFCR021 - LCI-FCI Relation
1199 ******************************************************************/
1200 -- finp_get_hecs_fee
1201 -- Routine to determine the HECS Fee assessed for a student IGS_PS_COURSE attempt.
1202
1203 cst_hecs CONSTANT VARCHAR2(4) := 'HECS';
1204 v_fp_total NUMBER;
1205 v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1206 v_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1207 v_message_name fnd_new_messages.message_name%TYPE;
1208 --modification to the Cursor c_fp_total to point to the table IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
1209 CURSOR c_fp_total(cp_fee_cal_type IN igs_ca_inst.cal_type%TYPE,
1210 cp_fee_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE) IS
1211 SELECT SUM(fadv.transaction_amount/fadv.exchange_rate) local_assessment_amount
1212 FROM IGS_FI_FEE_TYPE ft,
1213 IGS_FI_FEE_AS fadv
1214 WHERE ft.s_fee_type = cst_hecs
1215 AND fadv.person_id = p_person_id
1216 AND ((fadv.course_cd = p_course_cd) OR ( fadv.course_cd IS NULL AND p_course_cd IS NULL))
1217 AND fadv.FEE_TYPE = ft.FEE_TYPE
1218 AND fadv.fee_cal_type = cp_fee_cal_type
1219 AND fadv.fee_ci_sequence_number = cp_fee_ci_sequence_number
1220 AND fadv.logical_delete_dt is NULL;
1221
1222 BEGIN
1223 -- Enh # 2187247
1224 -- Derive the related Fee Calendar Instance for the passed Load
1225 -- Calendar Instance
1226
1227 IF igs_fi_gen_001.finp_get_lfci_reln(p_load_cal_type,
1228 p_load_ci_sequence_number,
1229 'LOAD',
1230 v_fee_cal_type,
1231 v_fee_ci_sequence_number,
1232 v_message_name) = TRUE THEN
1233
1234 -- Get the total assessed for fee category fee liability assessments
1235 -- associated with a HECS fee with respect to the load calendar.
1236 OPEN c_fp_total(v_fee_cal_type,
1237 v_fee_ci_sequence_number);
1238 FETCH c_fp_total INTO v_fp_total;
1239 CLOSE c_fp_total;
1240 IF v_fp_total IS NULL THEN
1241 -- Record not found
1242 v_fp_total := 0;
1243 END IF;
1244 RETURN v_fp_total;
1245 ELSE
1246 -- Enh # 2187247
1247 -- The LCI cannot be determined
1248 v_fp_total := 0;
1249 END IF;
1250 RETURN v_fp_total;
1251 EXCEPTION
1252 WHEN OTHERS THEN
1253 IF c_fp_total%ISOPEN THEN
1254 CLOSE c_fp_total;
1255 END IF;
1256 RAISE;
1257 END finp_get_hecs_fee;
1258 --
1259 FUNCTION finp_get_hecs_pymnt_optn(
1260 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
1261 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
1262 p_effective_dt IN DATE ,
1263 p_fee_cal_type IN IGS_CA_INST_ALL.CAL_TYPE%TYPE ,
1264 p_fee_ci_sequence_number IN IGS_CA_INST_ALL.sequence_number%TYPE ,
1265 p_start_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.start_dt_alias%TYPE ,
1266 p_start_dai_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.start_dai_sequence_number%TYPE,
1267 p_end_dt_alias IN IGS_FI_F_TYP_CA_INST_ALL.end_dt_alias%TYPE ,
1268 p_end_dai_sequence_number IN IGS_FI_F_TYP_CA_INST_ALL.end_dai_sequence_number%
1269 TYPE )
1270
1271 RETURN varchar2 AS
1272
1273 BEGIN
1274 DECLARE
1275 -- cursor to get student IGS_PS_COURSE HECS payment option details
1276 CURSOR c_scho(
1277 cp_effective_dt DATE,
1278 cp_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1279 cp_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE) IS
1280 SELECT HECS_PAYMENT_OPTION
1281 FROM IGS_EN_STDNTPSHECSOP
1282 WHERE person_id = cp_person_id AND
1283 course_cd = cp_course_cd AND
1284 TRUNC(cp_effective_dt) >= TRUNC(start_dt) AND
1285 TRUNC(cp_effective_dt) <= TRUNC(NVL(end_dt, cp_effective_dt));
1286 v_start_dt DATE;
1287 v_end_dt DATE;
1288 v_effective_dt DATE;
1289 v_hecs_payment_option IGS_EN_STDNTPSHECSOP.HECS_PAYMENT_OPTION%TYPE;
1290 BEGIN -- finp_get_hecs_pymnt_optn
1291 -- Get HECS payment option relevant to a fee assessment period
1292 -- Check Parameters
1293 IF p_person_id IS NULL OR
1294 p_course_cd IS NULL OR
1295 p_effective_dt IS NULL OR
1296 p_fee_cal_type IS NULL OR
1297 p_fee_ci_sequence_number IS NULL OR
1298 p_start_dt_alias IS NULL OR
1299 p_start_dai_sequence_number IS NULL OR
1300 p_end_dt_alias IS NULL OR
1301 p_end_dai_sequence_number IS NULL THEN
1302 RETURN NULL;
1303 END IF;
1304 v_start_dt := IGS_CA_GEN_001.calp_get_alias_val(
1305 p_start_dt_alias,
1306 p_start_dai_sequence_number,
1307 p_fee_cal_type,
1308 p_fee_ci_sequence_number);
1309 IF v_start_dt IS NULL THEN
1310 RETURN NULL;
1311 END IF;
1312 v_end_dt := IGS_CA_GEN_001.calp_get_alias_val(
1313 p_end_dt_alias,
1314 p_end_dai_sequence_number,
1315 p_fee_cal_type,
1316 p_fee_ci_sequence_number);
1317 IF v_end_dt IS NULL THEN
1318 RETURN NULL;
1319 END IF;
1320 IF v_start_dt > p_effective_dt THEN
1321 v_effective_dt := v_start_dt;
1322 ELSIF v_end_dt < p_effective_dt THEN
1323 v_effective_dt := v_end_dt;
1324 ELSE
1325 v_effective_dt := TRUNC(p_effective_dt);
1326 END IF;
1327 -- Get the HECS payment option matching the effective date.
1328 -- IGS_GE_NOTE, later entries may exist.
1329 OPEN c_scho(
1330 v_effective_dt,
1331 p_person_id,
1332 p_course_cd);
1333 FETCH c_scho INTO v_hecs_payment_option;
1334 IF c_scho%NOTFOUND THEN
1335 CLOSE c_scho;
1336 RETURN NULL;
1337 END IF;
1338 CLOSE c_scho;
1339 RETURN v_hecs_payment_option;
1340 END;
1341 END finp_get_hecs_pymnt_optn;
1342
1343 --
1344 FUNCTION finp_get_tuition_fee(
1345 p_load_cal_type IN VARCHAR2 ,
1346 p_load_ci_sequence_number IN NUMBER ,
1347 p_person_id IN NUMBER ,
1348 p_course_cd IN VARCHAR2 )
1349 RETURN NUMBER AS
1350 /******************************************************************
1351 Change History
1352 Who When What
1353 uudayapr 12-12-2003 bug#3080983 made the modification to v_loc_ass_amt
1354 declartion as number instead
1355 IGS_FI_FEE_ASS_DEBT_V.local_assessment_amount%TYPE
1356 and the Cursor c_local_ass_amt to point to the table
1357 IGS_FI_FEE_AS instead IGS_FI_FEE_ASS_DEBT_V view.
1358 Modified teh cst_tuition instialization to TUTNFEE
1359 instead of TUITION .
1360
1361 schodava 21-Jan-2002 Enh # 2187247
1362 SFCR021 - LCI-FCI Relation
1363 ******************************************************************/
1364
1365 cst_tuition VARCHAR2(7) := 'TUTNFEE';
1366 v_loc_ass_amt NUMBER;
1367 v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1368 v_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1369 v_message_name fnd_new_messages.message_name%TYPE;
1370
1371 CURSOR c_local_ass_amt(cp_fee_cal_type IN igs_ca_inst.cal_type%TYPE,
1372 cp_fee_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE) IS
1373 SELECT SUM(fadv.transaction_amount/fadv.exchange_rate) local_assessment_amount
1374 FROM IGS_FI_FEE_TYPE ft,
1375 IGS_FI_FEE_AS fadv
1376 WHERE ft.s_fee_type = cst_tuition
1377 AND fadv.person_id = p_person_id
1378 AND ((fadv.course_cd = p_course_cd ) OR (fadv.course_cd IS NULL AND p_course_cd IS NULL))
1379 AND fadv.FEE_TYPE = ft.FEE_TYPE
1380 AND fadv.fee_cal_type = cp_fee_cal_type
1381 AND fadv.fee_ci_sequence_number = cp_fee_ci_sequence_number
1382 AND fadv.logical_delete_dt is NULL;
1383 BEGIN
1384 -- This module determines the tuition fee (DEETYA Element 391).
1385 -- If a tuition fee is not charged, or the student is in a place
1386 -- fully-funded by an employer, then a 0 value is returned.
1387 -- This routine has been developed for use by the statistics
1388 -- sub-system (STAP_INS_GOVT_SNPSHT).
1389 -- It will need to be re-written when the finance sub-system
1390 -- is developed. It will require a look up on the IGS_FI_FEE_AS
1391 -- table tuition fee not charged or the student is in a place fully-
1392 -- funded by an employer.
1393 -- Get the total assessed for fee category fee liability assessments
1394 -- associated with a TUITION fee with respect to the load calendar.
1395
1396 -- Enh # 2187247
1397 -- Derive the related Fee Calendar Instance for the passed Load
1398 -- Calendar Instance
1399 IF igs_fi_gen_001.finp_get_lfci_reln(p_load_cal_type,
1400 p_load_ci_sequence_number,
1401 'LOAD',
1402 v_fee_cal_type,
1403 v_fee_ci_sequence_number,
1404 v_message_name) = TRUE THEN
1405 OPEN c_local_ass_amt(v_fee_cal_type,
1406 v_fee_ci_sequence_number);
1407 FETCH c_local_ass_amt INTO v_loc_ass_amt;
1408 IF (c_local_ass_amt%FOUND) THEN
1409 IF v_loc_ass_amt IS NOT NULL THEN
1410 CLOSE c_local_ass_amt;
1411 RETURN v_loc_ass_amt;
1412 ELSE
1413 CLOSE c_local_ass_amt;
1414 RETURN 0;
1415 END IF;
1416 ELSE
1417 CLOSE c_local_ass_amt;
1418 RETURN 0;
1419 END IF;
1420 ELSE
1421 -- Enh # 2187247
1422 -- The LCI cannot be determined
1423 v_loc_ass_amt := 0;
1424 END IF;
1425 RETURN v_loc_ass_amt;
1426
1427 END finp_get_tuition_fee;
1428
1429 FUNCTION finp_get_lfci_reln(
1430 /******************************************************************
1431 Created By : schodava
1432 Date Created By : 18-Jan-2002
1433 Purpose : This function is used to identify the LCI in the
1434 case when FCI is passed and vice versa.
1435 Enh # 2187247
1436 Known limitations,
1437 enhancements,
1438 remarks :
1439 Change History
1440 Who When What
1441 ******************************************************************/
1442 p_cal_type IN igs_ca_inst.cal_type%TYPE,
1443 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1444 p_cal_category IN igs_ca_type.s_cal_cat%TYPE,
1445 p_ret_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
1446 p_ret_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
1447 p_message_name OUT NOCOPY FND_NEW_MESSAGES.MESSAGE_NAME%TYPE)
1448 RETURN BOOLEAN AS
1449 -- To derive the subordinate Load Calendar Instance uniquely linked
1450 -- to the passed superior Fee calendar Instance
1451
1452 CURSOR c_cal_inst_rel IS
1453 SELECT sub_cal_type,
1454 sub_ci_sequence_number,
1455 sup_cal_type,
1456 sup_ci_sequence_number
1457 FROM igs_ca_inst_rel cir,
1458 igs_ca_type ct1,
1459 igs_ca_type ct2
1460 WHERE cir.sub_cal_type = ct1.cal_type
1461 AND cir.sup_cal_type = ct2.cal_type
1462 AND ct1.s_cal_cat IN ('LOAD','FEE')
1463 AND ct2.s_cal_cat IN ('LOAD','FEE')
1464 AND ((sub_cal_type = p_cal_type
1465 AND sub_ci_sequence_number = p_ci_sequence_number)
1466 OR (sup_cal_type = p_cal_type
1467 AND sup_ci_sequence_number = p_ci_sequence_number));
1468
1469 BEGIN
1470
1471 IF p_cal_category NOT IN ('FEE','LOAD') THEN
1472 p_message_name := 'IGS_AD_INVALID_PARAM_COMB';
1473 RETURN FALSE;
1474 END IF;
1475
1476 FOR l_c_cal_inst_rel IN c_cal_inst_rel LOOP
1477 IF p_cal_category = 'FEE' AND p_ci_sequence_number <> l_c_cal_inst_rel.sub_ci_sequence_number THEN
1478 p_ret_cal_type := l_c_cal_inst_rel.sub_cal_type;
1479 p_ret_ci_sequence_number := l_c_cal_inst_rel.sub_ci_sequence_number;
1480 p_message_name := NULL;
1481 RETURN TRUE;
1482 ELSIF p_cal_category = 'LOAD' AND p_ci_sequence_number <> l_c_cal_inst_rel.sup_ci_sequence_number THEN
1483 p_ret_cal_type := l_c_cal_inst_rel.sup_cal_type;
1484 p_ret_ci_sequence_number := l_c_cal_inst_rel.sup_ci_sequence_number;
1485 p_message_name := NULL;
1486 RETURN TRUE;
1487 END IF;
1488 END LOOP;
1489 p_message_name := 'IGS_FI_NO_RELN_EXISTS';
1490 RETURN FALSE;
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 IF c_cal_inst_rel%ISOPEN THEN
1494 CLOSE c_cal_inst_rel;
1495 END IF;
1496 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
1497 RAISE;
1498 END finp_get_lfci_reln;
1499
1500 FUNCTION finp_chk_lfci_reln(
1501 /******************************************************************
1502 Created By : schodava
1503 Date Created By : 18-Jan-2002
1504 Purpose : This function is used to check if there exists
1505 a relation between the passed LCI/FCI with
1506 any FCI/LCI
1507 (Enh # 2187247)
1508 Known limitations,
1509 enhancements,
1510 remarks :
1511 Change History
1512 Who When What
1513 ******************************************************************/
1514 p_cal_type IN igs_ca_inst.cal_type%TYPE,
1515 p_ci_sequence_number IN igs_ca_inst.sequence_number%TYPE,
1516 p_cal_category IN igs_ca_type.s_cal_cat%TYPE)
1517 RETURN VARCHAR2 AS
1518
1519 l_cal_type igs_ca_inst.cal_type%TYPE;
1520 l_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1521 l_message_name FND_NEW_MESSAGES.MESSAGE_NAME%TYPE;
1522
1523 BEGIN
1524 IF igs_fi_gen_001.FINP_GET_LFCI_RELN(
1525 p_cal_type,
1526 p_ci_sequence_number,
1527 p_cal_category,
1528 l_cal_type,
1529 l_ci_sequence_number,
1530 l_message_name) THEN
1531 RETURN 'TRUE';
1532 ELSE
1533 RETURN 'FALSE';
1534 END IF;
1535 EXCEPTION
1536 WHEN OTHERS THEN
1537 APP_EXCEPTION.RAISE_EXCEPTION;
1538 END finp_chk_lfci_reln;
1539
1540 FUNCTION finp_get_planned_credits_ind(
1541 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE)
1542
1543 RETURN VARCHAR2 AS
1544 /******************************************************************
1545 Created By : SYkrishn
1546 Date Created By : 02-APR-2002
1547 Purpose : This function is used to derive the value of Planned_credits_ind
1548 from the table IGS_FI_CONTROL- Return N if Null
1549 Enh # 2293676
1550 Known limitations,
1551 enhancements,
1552 remarks :
1553 Change History
1554 Who When What
1555 ******************************************************************/
1556
1557 --Cursor to get the planned credits indicator
1558 CURSOR c_pln_credits_ind IS
1559 SELECT planned_credits_ind
1560 FROM igs_fi_control;
1561
1562 l_v_pln_credits_ind igs_fi_control_all.planned_credits_ind%TYPE;
1563
1564 BEGIN
1565 OPEN c_pln_credits_ind;
1566 FETCH c_pln_credits_ind INTO l_v_pln_credits_ind;
1567
1568 --IF no record found then return appropriate error
1569 IF c_pln_credits_ind%NOTFOUND THEN
1570 p_message_name := 'IGS_FI_NO_PC_SETUP';
1571 ELSE
1572 p_message_name := NULL;
1573 END IF;
1574 CLOSE c_pln_credits_ind;
1575 RETURN NVL(l_v_pln_credits_ind,'N');
1576 EXCEPTION
1577 WHEN OTHERS THEN
1578 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
1579 RAISE;
1580 END finp_get_planned_credits_ind;
1581
1582 FUNCTION finp_get_total_planned_credits(
1583
1584 p_person_id IN igs_fi_parties_v.person_id%TYPE,
1585 p_start_date IN DATE,
1586 p_end_date IN DATE,
1587 p_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE)
1588
1589 RETURN NUMBER AS
1590 /******************************************************************
1591 Created By : SYkrishn
1592 Date Created By : 02-APR-2002
1593 Purpose : This function is used to sum up all the planned credits in FA based on the parameters input
1594 Enh # 2293676
1595 Known limitations,
1596 enhancements,
1597 remarks :
1598 Change History
1599 Who When What
1600 vvutukur 15-Jul-2003 Enh#3038511.FICR106 Build. Modified cursors c_sum_pln_bw_dates,c_sum_pln_till_date
1601 to exclude the planned credits for which the Award Year status is not OPEN.
1602 sarakshi 23-sep-2002 Enh#2564643,removed the reference of subaccount and all its usage in this function
1603 ******************************************************************/
1604 CURSOR c_person_id IS
1605 SELECT person_id
1606 FROM igs_fi_parties_v
1607 WHERE person_id = p_person_id;
1608
1609
1610 CURSOR c_sum_pln_bw_dates IS
1611 SELECT
1612 SUM(disb.disb_net_amt )
1613 FROM
1614 igf_aw_awd_disb disb,
1615 igf_aw_award awd,
1616 igf_aw_fund_mast fmast,
1617 igf_ap_fa_base_rec base,
1618 igf_ap_batch_aw_map bm
1619 WHERE disb.award_id = awd.award_id
1620 AND awd.fund_id = fmast.fund_id
1621 AND awd.base_id = base.base_id
1622 AND fmast.ci_cal_type = bm.ci_cal_type
1623 AND fmast.ci_sequence_number = bm.ci_sequence_number
1624 AND awd.award_status ='ACCEPTED'
1625 AND disb.trans_type = 'P'
1626 AND disb.show_on_bill = 'Y'
1627 AND base.person_id = p_person_id
1628 AND TRUNC(disb.disb_date) BETWEEN TRUNC(p_start_date) and TRUNC(p_end_date)
1629 AND bm.award_year_status_code = 'O';
1630
1631 CURSOR c_sum_pln_till_date IS
1632 SELECT
1633 SUM(disb.disb_net_amt )
1634 FROM
1635 igf_aw_awd_disb disb,
1636 igf_aw_award awd,
1637 igf_aw_fund_mast fmast,
1638 igf_ap_fa_base_rec base,
1639 igf_ap_batch_aw_map bm
1640 WHERE disb.award_id = awd.award_id
1641 AND awd.fund_id = fmast.fund_id
1642 AND awd.base_id = base.base_id
1643 AND fmast.ci_cal_type = bm.ci_cal_type
1644 AND fmast.ci_sequence_number = bm.ci_sequence_number
1645 AND awd.award_status ='ACCEPTED'
1646 AND disb.trans_type = 'P'
1647 AND disb.show_on_bill = 'Y'
1648 AND base.person_id = p_person_id
1649 AND TRUNC(disb.disb_date) <= TRUNC(p_end_date)
1650 AND bm.award_year_status_code = 'O';
1651
1652 l_n_person_id igs_fi_parties_v.person_id%TYPE;
1653 l_n_sum_pln_credits igf_aw_awd_disb.disb_net_amt%TYPE;
1654
1655 BEGIN
1656
1657 IF (p_person_id IS NULL OR p_end_date IS NULL) THEN
1658 p_message_name := 'IGS_GE_INSUFFICIENT_PARAMETER';
1659 RETURN 0;
1660 END IF;
1661
1662
1663 IF TRUNC(p_end_date) > TRUNC(sysdate) THEN
1664 p_message_name := 'IGS_EN_ENDDT_LE_CURR_DT';
1665 RETURN 0;
1666 END IF;
1667
1668 IF p_start_date IS NOT NULL THEN
1669 IF TRUNC(p_start_date) > TRUNC(p_end_date) THEN
1670 p_message_name := 'IGS_FI_END_DT_LESS_THAN_ST_DT';
1671 RETURN 0;
1672 END IF;
1673 END IF;
1674
1675 OPEN c_person_id;
1676 FETCH c_person_id INTO l_n_person_id;
1677 IF c_person_id%NOTFOUND THEN
1678 p_message_name := 'IGS_AD_INVALID_PERSON';
1679 CLOSE c_person_id;
1680 RETURN 0;
1681 END IF;
1682 CLOSE c_person_id;
1683
1684
1685 IF p_start_date IS NULL THEN
1686 OPEN c_sum_pln_till_date;
1687 FETCH c_sum_pln_till_date INTO l_n_sum_pln_credits;
1688 CLOSE c_sum_pln_till_date;
1689 ELSE
1690 OPEN c_sum_pln_bw_dates;
1691 FETCH c_sum_pln_bw_dates INTO l_n_sum_pln_credits;
1692 CLOSE c_sum_pln_bw_dates;
1693 END IF;
1694
1695 RETURN NVL(l_n_sum_pln_credits,0);
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699 p_message_name := 'IGS_GE_UNHANDLED_EXCEPTION';
1700 RAISE;
1701 END finp_get_total_planned_credits;
1702
1703 END igs_fi_gen_001;
[Home] [Help]