1 PACKAGE BODY igs_fi_gen_005 AS
2 /* $Header: IGSFI05B.pls 120.3 2006/02/23 21:16:19 skharida noship $ */
3 /******** ------------------------------------------------------------------
4 --Change History:
5 --Who When What
6 --skharida 24-Feb-2006 After Code Review: Modified finpl_val_trig_group() Bug# 5018036
7 --skharida 15-Feb-2006 Modified finpl_val_trig_group() Bug# 5018036, (version 12.1)
8 --bannamal 27-May-2005 Fee Calculation Performance Enhancement. Changes done as per TD.
9 --pathipat 21-Sep-2004 Enh 3880438 - Retention Enhancements
10 -- Removed function get_retention_amount
11 -- pathipat 14-Oct-2003 Enh 3117341 - Audit and Special Fees TD
12 -- Modified finp_val_fee_trigger(), added function get_retention_amount
13 --pathipat 18-Apr-2003 Enh:2831569 - Commercial Receivables build
14 -- Modified finp_get_acct_meth()
15 -- pradhakr 15-Jan-2003 Added one more paramter no_assessment_ind to
16 -- the call enrp_get_load_apply as an impact, following
17 -- the modification of the package Igs_En_Prc_Load.
18 -- Changes wrt ENCR026. Bug# 2743459
19 --smadathi 03-Jan-2003 Bug 2684895. Created new generic function
20 -- finp_get_prsid_grp_code which returns group code
21 sarakshi 13-sep-2002 Enh#2564643,removed the function validate_psa
22 rnirwani 06-May-02 Bug# 2345570, in the usage of the view IGS_FI_FEE_TRG_GRP_V replaced the column trigger_type with trigger_type_code.
23 modification done in procedure finpl_val_trig_group
24 --smadathi 26-feb-2002 bug 2238413. procedure finp_get_receivables_inst modified
25 --jbegum 08-Feb-2002 Bug 2201081.Added function validate_psa.
26 --sarakshi 24-jan-2002 Bug 2195715.Added function finp_get_acct_meth
27 --schodava 29-Jan-2002 Enh # 2187247
28 -- SFCR021: FCI-LCI Relation
29 -- Modified function finp_val_fee_lblty
30 --smadathi 22-Jan-2002 Bug 2170429. Procedure FINP_SET_FSS_EXPIRED
31 -- removed.
32 --msrinivi Bug 1956374. duplicate removal Pointed genp_val_bus_day to igs_tr_val_tri
33 ------------------------------------------------------------------*****/
34
35 g_v_yes CONSTANT VARCHAR2(10) := 'Y';
36 g_v_no CONSTANT VARCHAR2(10) := 'N';
37 g_v_sua_status CONSTANT VARCHAR2(30) := 'UNIT_ATTEMPT_STATUS';
38
39 FUNCTION finp_val_fee_lblty(
40 p_person_id IN NUMBER ,
41 p_course_cd IN VARCHAR2 ,
42 p_fee_cat IN VARCHAR2 ,
43 p_fee_type IN VARCHAR2 ,
44 p_effective_dt IN DATE ,
45 p_message_name OUT NOCOPY VARCHAR2 )
46 RETURN BOOLEAN AS
47 /******************************************************************
48 Created By :
49 Date Created By :
50 Purpose :
51 Known limitations,
52 enhancements,
53 remarks :
54 Change History
55 Who When What
56 schodava 21-Jan-2002 Enh # 2187247
57 Cursor c_ftcmav removed
58 Function finp_get_lci_fci_relation
59 invoked.
60 ******************************************************************/
61
62 gv_other_detail VARCHAR2(255);
63 gr_scaeh IGS_AS_SC_ATTEMPT_H_ALL%ROWTYPE;
64 gv_data_found BOOLEAN;
65 gt_suaeh_table IGS_FI_GET_SUAEH.t_suaeh_dtl;
66 gv_table_index BINARY_INTEGER;
67 lv_param_values VARCHAR2(1080);
68
69 BEGIN -- finp_val_fee_lblty
70 -- This routine validates whether or not a student's IGS_PS_COURSE attempt
71 -- is liable for fees. The routine returns TRUE if the student's IGS_PS_COURSE
72 -- attempt is liable for fees and FALSE if the student's IGS_PS_COURSE attempt
73 -- is not liable for fees. If the fee type is passed as an input
74 -- parameter, the routine will check if the student's IGS_PS_COURSE attempt is
75 -- liable for the specified fee type.
76
77 DECLARE
78
79 cst_active CONSTANT VARCHAR2(10) := 'ACTIVE';
80 cst_institutn CONSTANT VARCHAR2(10) := 'INSTITUTN';
81 v_liability BOOLEAN := FALSE;
82 v_load_found BOOLEAN := FALSE;
83 v_dummy CHAR(1);
84 v_trigger_fired igs_lookups_view.lookup_code%TYPE;
85 v_index BINARY_INTEGER;
86 r_suaeh IGS_FI_GET_SUAEH.r_t_suaeh_dtl;
87 v_ret_cal_type igs_ca_type.cal_type%TYPE;
88 v_ret_ci_sequence_number igs_ca_type.cal_type%TYPE;
89 v_message_name fnd_new_messages.message_name%TYPE;
90
91 -- cursor to find the ACTIVE fee liabilities on the effective date
92
93 CURSOR c_ftci IS
94
95 SELECT ftci.FEE_TYPE,
96 ftci.fee_cal_type,
97 ftci.fee_ci_sequence_number,
98 ft.s_fee_trigger_cat
99 FROM IGS_FI_F_TYP_CA_INST ftci,
100 IGS_FI_FEE_STR_STAT fss,
101 IGS_FI_FEE_TYPE ft
102 WHERE (p_fee_type IS NULL OR
103 ftci.FEE_TYPE = p_fee_type) AND
104 fss.FEE_STRUCTURE_STATUS = ftci.fee_type_ci_status AND
105 fss.s_fee_structure_status = cst_active AND
106 ft.FEE_TYPE = ftci.FEE_TYPE AND
107 ftci.FEE_TYPE IN
108 (SELECT FEE_TYPE
109 FROM IGS_FI_F_CAT_FEE_LBL_V fcflv,
110 IGS_FI_FEE_STR_STAT fss
111 WHERE fcflv.FEE_CAT = p_fee_cat AND
112 fcflv.fee_cal_type = ftci.fee_cal_type AND
113 fcflv.fee_ci_sequence_number = ftci.fee_ci_sequence_number AND
114 fcflv.FEE_TYPE = ftci.FEE_TYPE AND
115 fss.FEE_STRUCTURE_STATUS = fcflv.fee_liability_status AND
116 fss.s_fee_structure_status = cst_active AND
117 p_effective_dt BETWEEN
118 IGS_CA_GEN_001.calp_get_alias_val(fcflv.start_dt_alias,
119 fcflv.start_dai_sequence_number,
120 ftci.fee_cal_type,
121 ftci.fee_ci_sequence_number) AND
122 IGS_CA_GEN_001.calp_get_alias_val(fcflv.end_dt_alias,
123 fcflv.end_dai_sequence_number,
124 ftci.fee_cal_type,
125 ftci.fee_ci_sequence_number));
126
127 -- cursor find the charge method apportionments applicable to the
128 -- fee cat fee liability
129 -- Enh # 2187247 cursor removed and functionality replaced by the call to the function
130 -- igs_fi_gen_001.finp_get_lcfi_reln
131
132 -- check if the IGS_PS_COURSE attempt status is fee assessible
133
134 CURSOR c_scas ( cp_course_attempt_status IGS_LOOKUPS_VIEW.lookup_code%TYPE ) IS
135
136 SELECT 'x'
137 FROM IGS_LOOKUPS_view scas
138 WHERE scas.lookup_code = cp_course_attempt_status AND
139 scas.lookup_type = 'CRS_ATTEMPT_STATUS' AND
140 scas.fee_ass_ind = 'Y';
141
142 -- check if the IGS_PS_UNIT attempt status is fee assessible
143
144 CURSOR c_suas ( cp_unit_attempt_status IGS_LOOKUPS_VIEW.lookup_code%TYPE) IS
145
146 SELECT 'x'
147 FROM IGS_LOOKUPS_view suas
148 WHERE suas.lookup_code = cp_unit_attempt_status AND
149 suas.lookup_type = 'UNIT_ATTEMPT_STATUS' AND
150 suas.fee_ass_ind = 'Y';
151
152 BEGIN -- finp_val_fee_lblty
153 -- Set the default message number
154
155
156
157 p_message_name := Null;
158 -- check parameters
159
160 IF p_person_id IS NULL OR
161 p_course_cd IS NULL OR
162 p_fee_cat IS NULL OR
163 p_effective_dt IS NULL THEN
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
167 END IF;
168
169 -- Find the ACTIVE fee cat fee liabilities
170
171 FOR v_ftci IN c_ftci LOOP
172 -- check if the fee type is a liability for the sca
173 v_liability := FALSE;
174 -- get sca effective history data
175 IGS_FI_GET_SCAEH.finp_get_scaeh(
176 p_person_id,
177 p_course_cd,
178 p_effective_dt,
179 gv_data_found,
180 gr_scaeh);
181 IF gv_data_found = TRUE THEN
182 -- check if the IGS_PS_COURSE status is fee assessible
183 OPEN c_scas (gr_scaeh.course_attempt_status);
184 FETCH c_scas INTO v_dummy;
185 IF (c_scas%FOUND) THEN
186 -- check if a fee trigger is fired
187 IF (v_ftci.s_fee_trigger_cat = cst_institutn OR
188 finp_val_fee_trigger(
189 gr_scaeh.FEE_CAT,
190 v_ftci.fee_cal_type,
191 v_ftci.fee_ci_sequence_number,
192 v_ftci.FEE_TYPE,
193 v_ftci.s_fee_trigger_cat,
194 p_effective_dt,
195 gr_scaeh.person_id,
196 gr_scaeh.course_cd,
197 gr_scaeh.version_number,
198 gr_scaeh.CAL_TYPE,
199 gr_scaeh.location_cd,
200 gr_scaeh.ATTENDANCE_MODE,
201 gr_scaeh.ATTENDANCE_TYPE,
202 v_trigger_fired) = TRUE) THEN
203 v_liability := TRUE;
204 END IF;
205 END IF;
206 CLOSE c_scas;
207 END IF;
208 IF v_liability = TRUE THEN
209 -- check if load is incurred for a IGS_PS_UNIT attempt within any of the
210 -- liability charge method apportionments
211 -- Find the liability charge method apportionments
212
213 -- Enh # 2187247
214 -- SFCR021 : FCI-LCI Relation
215 -- Invoke the function FINP_GET_LFCI_RELN
216 -- to derive the Load Calendar Instance of the passed Fee calendar instance
217
218 IF IGS_FI_GEN_001.FINP_GET_LFCI_RELN(
219 v_ftci.fee_cal_type,
220 v_ftci.fee_ci_sequence_number,
221 'FEE',
222 v_ret_cal_type,
223 v_ret_ci_sequence_number,
224 v_message_name) = TRUE THEN
225
226 -- get sua effective history data
227 IGS_FI_GET_SUAEH.finp_get_suaeh(
228 p_person_id,
229 p_course_cd,
230 NULL, -- IGS_PS_UNIT cd
231 p_effective_dt,
232 gv_table_index,
233 gt_suaeh_table);
234 IF gv_table_index > 0 THEN
235 FOR v_index IN 1..gv_table_index
236 LOOP
237 r_suaeh := gt_suaeh_table(v_index);
238 -- check if the IGS_PS_UNIT status is fee assessible
239 OPEN c_suas (r_suaeh.unit_attempt_status);
240 FETCH c_suas INTO v_dummy;
241 IF (c_suas%FOUND) THEN
242 CLOSE c_suas;
243 -- Check if load is incured
244 -- Added parameter p_include_audit
245 IF IGS_EN_PRC_LOAD.enrp_get_load_apply(
246 p_teach_cal_type => r_suaeh.CAL_TYPE,
247 p_teach_sequence_number => r_suaeh.ci_sequence_number,
248 p_discontinued_dt => r_suaeh.discontinued_dt,
249 p_administrative_unit_status => r_suaeh.ADMINISTRATIVE_UNIT_STATUS,
250 p_unit_attempt_status => r_suaeh.unit_attempt_status,
251 p_no_assessment_ind => r_suaeh.no_assessment_ind,
252 p_load_cal_type => v_ret_cal_type,
253 p_load_sequence_number => v_ret_ci_sequence_number,
254 p_include_audit => 'N') = 'Y' THEN
255 -- Set that load was found
256 v_load_found := TRUE;
257 EXIT;
258 END IF;
259 ELSE
260 CLOSE c_suas;
261 END IF;
262 END LOOP;
263 IF v_load_found THEN
264 EXIT;
265 END IF;
266 END IF;
267 ELSE -- The FINP_GET_LFCI_RELN function returns FALSE
268 p_message_name := v_message_name;
269 END IF; -- For the function FINP_GET_LFCI_RELN
270 IF v_load_found THEN
271 EXIT;
272 END IF;
273 END IF;
274 END LOOP;
275 IF NOT v_load_found THEN
276 p_message_name := 'IGS_FI_STUD_PRG_ATTEMPT_NL';
277 END IF;
278 RETURN v_load_found;
279 RETURN TRUE;
280 EXCEPTION
281 WHEN OTHERS THEN
282 IF c_ftci%ISOPEN THEN
283 CLOSE c_ftci;
284 END IF;
285 IF c_scas%ISOPEN THEN
286 CLOSE c_scas;
287 END IF;
288 IF c_suas%ISOPEN THEN
289 CLOSE c_suas;
290 END IF;
291 APP_EXCEPTION.RAISE_EXCEPTION;
292 END;
293
294 EXCEPTION
295 WHEN OTHERS THEN
296 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
297 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GEN_005.FINP_VAL_FEE_LBLTY');
298 IGS_GE_MSG_STACK.ADD;
299 lv_param_values := to_char(p_person_id)||','||
300 p_course_cd||','||p_fee_cat||','||
301 p_fee_type||','||
302 fnd_date.date_to_displaydt(p_effective_dt);
303 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PARAMETERS');
304 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
305 IGS_GE_MSG_STACK.ADD;
306 App_Exception.Raise_Exception;
307 END finp_val_fee_lblty;
308
309
310
311 --
312 FUNCTION finp_val_fee_trigger(
313 p_fee_cat IN IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE ,
314 p_fee_cal_type IN IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE ,
315 p_fee_ci_sequence_number IN IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE ,
316 p_fee_type IN IGS_FI_F_CAT_FEE_LBL_ALL.FEE_TYPE%TYPE ,
317 p_s_fee_trigger_cat IN IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE ,
318 p_effective_dt IN DATE ,
319 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
320 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
321 p_version_number IN IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
322 p_cal_type IN IGS_EN_STDNT_PS_ATT_ALL.CAL_TYPE%TYPE ,
323 p_location_cd IN IGS_EN_STDNT_PS_ATT_ALL.location_cd%TYPE ,
324 p_attendance_mode IN IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_MODE%TYPE ,
325 p_attendance_type IN IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_TYPE%TYPE ,
326 p_trigger_fired OUT NOCOPY VARCHAR2 )
327 RETURN BOOLEAN AS
328 /***************************************************************************/
329 -- Change History:
330 -- Who When What
331 -- pathipat 14-Oct-2003 Enh 3117341 - Audit and Special Fees TD
332 -- Modified cursor c_sua, added code for
333 -- fee trigger type 'AUDIT'
334 /***************************************************************************/
335 gv_table_index BINARY_INTEGER;
336 lv_param_values VARCHAR2(1080);
337 BEGIN
338 DECLARE
339 CURSOR c_ft IS
340
341 SELECT ft.s_fee_trigger_cat
342 FROM IGS_FI_FEE_TYPE ft
343 WHERE ft.FEE_TYPE = p_fee_type;
344
345 CURSOR c_ctft IS
346
347 SELECT ctft.COURSE_TYPE
348 FROM IGS_PS_TYPE_FEE_TRG ctft,
349 IGS_PS_VER cv
350 WHERE ctft.FEE_CAT = p_fee_cat AND
351 ctft.fee_cal_type = p_fee_cal_type AND
352 ctft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
353 ctft.FEE_TYPE = p_fee_type AND
354 cv.course_cd = p_course_cd AND
355 cv.version_number = p_version_number AND
356 cv.COURSE_TYPE = ctft.COURSE_TYPE AND
357 ctft.logical_delete_dt IS NULL;
358
359 CURSOR c_cgft IS
360
361 SELECT cgft.course_group_cd
362 FROM IGS_PS_GRP_FEE_TRG cgft,
363 IGS_PS_GRP_MBR cgm
364 WHERE cgft.FEE_CAT = p_fee_cat AND
365 cgft.fee_cal_type = p_fee_cal_type AND
366 cgft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
367 cgft.FEE_TYPE = p_fee_type AND
368 cgm.course_cd = p_course_cd AND
369 cgm.version_number = p_version_number AND
370 cgm.course_group_cd = cgft.course_group_cd AND
371 cgft.logical_delete_dt IS NULL;
372
373 CURSOR c_cft IS
374
375 SELECT cft.fee_trigger_group_number
376 FROM IGS_PS_FEE_TRG cft
377 WHERE cft.FEE_CAT = p_fee_cat AND
378 cft.fee_cal_type = p_fee_cal_type AND
379 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
380 cft.FEE_TYPE = p_fee_type AND
381 cft.course_cd = p_course_cd AND
382 (cft.version_number IS NULL OR
383 cft.version_number = p_version_number) AND
384 p_cal_type LIKE NVL(cft.CAL_TYPE, '%') AND
385 p_location_cd LIKE NVL(cft.location_cd, '%') AND
386 p_attendance_mode LIKE NVL(cft.ATTENDANCE_MODE, '%') AND
387 p_attendance_type LIKE NVL(cft.ATTENDANCE_TYPE, '%') AND
388 cft.logical_delete_dt IS NULL;
389
390 CURSOR c_uft IS
391
392 SELECT uft.unit_cd,
393 uft.version_number,
394 uft.CAL_TYPE,
395 uft.ci_sequence_number,
396 uft.location_cd,
397 uft.UNIT_CLASS,
398 uft.fee_trigger_group_number
399 FROM IGS_FI_UNIT_FEE_TRG uft
400 WHERE uft.FEE_CAT = p_fee_cat AND
401 uft.fee_cal_type = p_fee_cal_type AND
402 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
403 uft.FEE_TYPE = p_fee_type AND
404 uft.logical_delete_dt IS NULL;
405
406 CURSOR c_sua (cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
407
408 cp_version_number
409 IGS_EN_SU_ATTEMPT.version_number%TYPE,
410 cp_cal_type IGS_EN_SU_ATTEMPT.CAL_TYPE%TYPE,
411 cp_ci_sequence_number
412 IGS_EN_SU_ATTEMPT.ci_sequence_number%TYPE,
413 cp_location_cd IGS_EN_SU_ATTEMPT.location_cd%TYPE,
414 cp_unit_class IGS_EN_SU_ATTEMPT.UNIT_CLASS%TYPE,
415 cp_v_audit_only VARCHAR2) IS
416 SELECT 'X'
417 FROM IGS_EN_SU_ATTEMPT sua,
418 IGS_LOOKUPS_view suas
419 WHERE sua.person_id = p_person_id AND
420 sua.course_cd = p_course_cd AND
421 ( sua.unit_cd = cp_unit_cd OR cp_unit_cd IS NULL) AND
422 (cp_version_number IS NULL OR
423 sua.version_number = cp_version_number) AND
424 (cp_cal_type IS NULL OR
425 sua.CAL_TYPE = cp_cal_type) AND
426 (cp_ci_sequence_number IS NULL OR
427 sua.ci_sequence_number = cp_ci_sequence_number) AND
428 (cp_location_cd IS NULL OR
429 sua.location_cd = cp_location_cd) AND
430 (cp_unit_class IS NULL OR
431 sua.UNIT_CLASS = cp_unit_class) AND
432 suas.lookup_code = sua.unit_attempt_status AND
433 suas.lookup_type = g_v_sua_status AND
434 suas.fee_ass_ind = g_v_yes AND
435 ( ( sua.no_assessment_ind = g_v_yes AND
436 cp_v_audit_only = g_v_yes
437 )
438 OR cp_v_audit_only = g_v_no
439 );
440
441 CURSOR c_usft IS
442
443 SELECT usft.unit_set_cd,
444 usft.version_number,
445 usft.fee_trigger_group_number
446 FROM IGS_EN_UNITSETFEETRG usft
447 WHERE usft.FEE_CAT = p_fee_cat AND
448 usft.fee_cal_type = p_fee_cal_type AND
449 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
450 usft.FEE_TYPE = p_fee_type AND
451 usft.logical_delete_dt IS NULL;
452
453 CURSOR c_susa (cp_unit_set_cd IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
454 cp_version_number IGS_AS_SU_SETATMPT.us_version_number%TYPE) IS
455
456 SELECT 'X'
457 FROM IGS_AS_SU_SETATMPT susa
458 WHERE susa.person_id = p_person_id AND
459 susa.course_cd = p_course_cd AND
460 susa.unit_set_cd = cp_unit_set_cd AND
461 susa.us_version_number = cp_version_number AND
462 susa.student_confirmed_ind = g_v_yes AND
463 (susa.selection_dt IS NOT NULL AND
464 TRUNC(p_effective_dt) >= TRUNC(susa.selection_dt)) AND
465 (susa.end_dt IS NULL OR
466 TRUNC(p_effective_dt) <= TRUNC(susa.end_dt)) AND
467 (susa.rqrmnts_complete_dt IS NULL OR
468 TRUNC(p_effective_dt) <= TRUNC(susa.rqrmnts_complete_dt));
469 v_s_fee_trigger_cat IGS_FI_FEE_TYPE.s_fee_trigger_cat%TYPE;
470 v_check VARCHAR2(1);
471
472 -- Cursor to find out if the Student has atleast one auditable unit
473 CURSOR c_sua_audit_one (cp_person_id NUMBER,
474 cp_course_cd VARCHAR2) IS
475 SELECT 'X'
476 FROM igs_en_su_attempt sua,
477 igs_lookups_view suas
478 WHERE sua.person_id = p_person_id
479 AND sua.course_cd = p_course_cd
480 AND suas.lookup_type = g_v_sua_status
481 AND suas.lookup_code = sua.unit_attempt_status
482 AND suas.fee_ass_ind = g_v_yes
483 AND sua.no_assessment_ind = g_v_yes;
484
485 l_b_fee_trigger_found BOOLEAN := FALSE;
486
487 FUNCTION finpl_val_trig_group (p_fee_trigger_group_number
488 IGS_FI_FEE_TRG_GRP.fee_trigger_group_number%TYPE)
489 RETURN BOOLEAN AS
490 BEGIN
491 -- validate the fee trigger group members match the student
492 DECLARE
493 CURSOR c_ftgv_course IS
494 SELECT lkp.lookup_code trigger_type_code,
495 cft.course_cd code,
496 cft.version_number
497 FROM IGS_PS_FEE_TRG cft,
498 IGS_PS_VER crv,
499 IGS_LOOKUP_VALUES lkp
500 WHERE cft.FEE_CAT = p_fee_cat AND
501 cft.fee_cal_type = p_fee_cal_type AND
502 cft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
503 cft.FEE_TYPE = p_fee_type AND
504 cft.fee_trigger_group_number = p_fee_trigger_group_number AND
505 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
506 lkp.lookup_code = 'COURSE' AND
507 cft.fee_trigger_group_number IS NOT NULL AND
508 cft.logical_delete_dt IS NULL AND
509 cft.course_cd = crv.course_cd AND
510 (cft.version_number = crv.version_number OR
511 (cft.version_number IS NULL AND
512 crv.version_number = ( SELECT MAX(crv2.version_number)
513 FROM IGS_PS_VER crv2
514 WHERE crv2.course_cd = crv.course_cd)));
515 CURSOR c_ftgv_unit IS
516 SELECT lkp.lookup_code trigger_type_code,
517 uft.unit_cd code,
518 uft.version_number
519 FROM IGS_FI_UNIT_FEE_TRG uft,
520 IGS_PS_UNIT_VER uv,
521 IGS_LOOKUP_VALUES lkp
522 WHERE uft.FEE_CAT = p_fee_cat AND
523 uft.fee_cal_type = p_fee_cal_type AND
524 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
525 uft.FEE_TYPE = p_fee_type AND
526 uft.fee_trigger_group_number = p_fee_trigger_group_number AND
527 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
528 lkp.lookup_code = 'UNIT' AND
529 uft.fee_trigger_group_number IS NOT NULL AND
530 uft.logical_delete_dt IS NULL AND
531 uft.unit_cd = uv.unit_cd AND
532 (uft.version_number = uv.version_number OR
533 (uft.version_number IS NULL AND
534 uv.version_number = ( SELECT MAX(uv2.version_number)
535 FROM IGS_PS_UNIT_VER uv2
536 WHERE uv2.unit_cd = uv.unit_cd)));
537 CURSOR c_ftgv_unitset IS
538 SELECT usft.unit_set_cd code, usft.version_number,
539 lkp.lookup_code trigger_type_code
540 FROM IGS_EN_UNITSETFEETRG usft,
541 IGS_EN_UNIT_SET us,
542 IGS_LOOKUP_VALUES lkp
543 WHERE usft.FEE_CAT = p_fee_cat AND
544 usft.fee_cal_type = p_fee_cal_type AND
545 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
546 usft.FEE_TYPE = p_fee_type AND
547 usft.fee_trigger_group_number = p_fee_trigger_group_number AND
548 lkp.lookup_type = 'IGS_FI_TRIGGER_GROUP' AND
549 lkp.lookup_code = 'UNITSET' AND
550 usft.fee_trigger_group_number IS NOT NULL AND
551 usft.logical_delete_dt IS NULL AND
552 usft.unit_set_cd = us.unit_set_cd AND
553 usft.version_number = us.version_number;
554 CURSOR c_ftg_uft (
555 cp_unit_cd IGS_EN_SU_ATTEMPT.unit_cd%TYPE,
556 cp_version_number
557 IGS_EN_SU_ATTEMPT.version_number%TYPE)IS
558 SELECT uft.unit_cd,
559 uft.version_number,
560 uft.CAL_TYPE,
561 uft.ci_sequence_number,
562 uft.location_cd,
563 uft.UNIT_CLASS,
564 uft.fee_trigger_group_number
565 FROM IGS_FI_UNIT_FEE_TRG uft
566 WHERE uft.FEE_CAT = p_fee_cat AND
567 uft.fee_cal_type = p_fee_cal_type AND
568 uft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
569 uft.FEE_TYPE = p_fee_type AND
570 uft.unit_cd = cp_unit_cd AND
571 (uft.version_number IS NULL OR
572 uft.version_number = cp_version_number) AND
573 uft.logical_delete_dt IS NULL;
574 CURSOR c_ftg_usft (
575 cp_unit_set_cd
576 IGS_AS_SU_SETATMPT.unit_set_cd%TYPE,
577 cp_version_number
578 IGS_AS_SU_SETATMPT.us_version_number%TYPE) IS
579 SELECT usft.unit_set_cd,
580 usft.version_number,
581 usft.fee_trigger_group_number
582 FROM IGS_EN_UNITSETFEETRG usft
583 WHERE usft.FEE_CAT = p_fee_cat AND
584 usft.fee_cal_type = p_fee_cal_type AND
585 usft.fee_ci_sequence_number = p_fee_ci_sequence_number AND
586 usft.FEE_TYPE = p_fee_type AND
587 usft.unit_set_cd = cp_unit_set_cd AND
588 usft.version_number = cp_version_number AND
589 usft.logical_delete_dt IS NULL;
590 v_trigger_group_fired BOOLEAN;
591 BEGIN
592 -- check the fee trigger group members
593 v_trigger_group_fired := TRUE;
594 FOR v_ftgv_rec IN c_ftgv_course LOOP
595 -- check for matching student IGS_PS_COURSE attempt
596 IF (v_ftgv_rec.code <> p_course_cd OR
597 NVL(v_ftgv_rec.version_number, p_version_number) <>
598 p_version_number) THEN
599 v_trigger_group_fired := FALSE;
600 RETURN v_trigger_group_fired;
601 END IF;
602 END LOOP;
603 FOR v_ftgv_rec IN c_ftgv_unit LOOP
604 FOR v_ftg_uft_rec IN c_ftg_uft ( v_ftgv_rec.code,
605 v_ftgv_rec.version_number) LOOP
606 -- check for matching student IGS_PS_UNIT attempt
607 OPEN c_sua ( v_ftg_uft_rec.unit_cd,
608 v_ftg_uft_rec.version_number,
609 v_ftg_uft_rec.CAL_TYPE,
610 v_ftg_uft_rec.ci_sequence_number,
611 v_ftg_uft_rec.location_cd,
612 v_ftg_uft_rec.UNIT_CLASS,
613 'N');
614 FETCH c_sua INTO v_check;
615 IF (c_sua%NOTFOUND) THEN
616 CLOSE c_sua;
617 v_trigger_group_fired := FALSE;
618 RETURN v_trigger_group_fired;
619 END IF;
620 CLOSE c_sua;
621 END LOOP;
622 END LOOP;
623 FOR v_ftgv_rec IN c_ftgv_unitset LOOP
624 FOR v_ftg_usft_rec IN c_ftg_usft ( v_ftgv_rec.code,
625 v_ftgv_rec.version_number) LOOP
626 -- check for matching student IGS_PS_UNIT set attempt
627 OPEN c_susa ( v_ftg_usft_rec.unit_set_cd,
628 v_ftg_usft_rec.version_number);
629 FETCH c_susa INTO v_check;
630 IF (c_susa%NOTFOUND) THEN
631 CLOSE c_susa;
632 v_trigger_group_fired := FALSE;
633 RETURN v_trigger_group_fired;
634 END IF;
635 CLOSE c_susa;
636 END LOOP;
637 END LOOP;
638 RETURN v_trigger_group_fired;
639 END;
640 END finpl_val_trig_group;
641 --------------------------------------------------------------------------------
642 -- Begin for finp_val_fee_trigger
643 BEGIN
644 -- This routine checks the students enrolment details to test
645 -- for matching a fee trigger.
646 -- Check if enrolment history is being used
647
648 IF p_s_fee_trigger_cat IS NULL THEN
649 OPEN c_ft;
650 FETCH c_ft INTO v_s_fee_trigger_cat;
651 CLOSE c_ft;
652 ELSE
653 v_s_fee_trigger_cat := p_s_fee_trigger_cat;
654 END IF;
655 IF (v_s_fee_trigger_cat = 'INSTITUTN') THEN
656 -- IGS_GE_NOTE, IGS_OR_INSTITUTION fees have no triggers - they always apply.
657 -- Trigger Fired
658 p_trigger_fired := 'INSTITUTN';
659 RETURN TRUE;
660 ELSIF (v_s_fee_trigger_cat = 'COURSE') THEN
661 FOR v_ctft_rec IN c_ctft LOOP
662 -- Trigger Fired
663 p_trigger_fired := 'CTFT';
664 RETURN TRUE;
665 END LOOP;
666 FOR v_cgft_rec IN c_cgft LOOP
667 -- Trigger Fired
668 p_trigger_fired := 'CGFT';
669 RETURN TRUE;
670 END LOOP;
671 FOR v_cft_rec IN c_cft LOOP
672 -- Trigger Fired
673 p_trigger_fired := 'CFT';
674 RETURN TRUE;
675 END LOOP;
676 ELSIF (v_s_fee_trigger_cat = 'UNIT') THEN
677 FOR v_uft_rec IN c_uft
678 LOOP
679 OPEN c_sua ( v_uft_rec.unit_cd,
680 v_uft_rec.version_number,
681 v_uft_rec.CAL_TYPE,
682 v_uft_rec.ci_sequence_number,
683 v_uft_rec.location_cd,
684 v_uft_rec.UNIT_CLASS,
685 g_v_no);
686 FETCH c_sua INTO v_check;
687 IF (c_sua%FOUND) THEN
688 CLOSE c_sua;
689 -- Trigger Fired
690 p_trigger_fired := 'UFT';
691 RETURN TRUE;
692 END IF;
693 CLOSE c_sua;
694
695 END LOOP;
696 ELSIF (v_s_fee_trigger_cat = 'UNITSET') THEN
697 FOR v_usft_rec IN c_usft
698 LOOP
699 OPEN c_susa ( v_usft_rec.unit_set_cd,
700 v_usft_rec.version_number);
701 FETCH c_susa INTO v_check;
702 IF (c_susa%FOUND) THEN
703 CLOSE c_susa;
704 -- Trigger Fired
705 p_trigger_fired := 'USFT';
706 RETURN TRUE;
707 END IF;
708 CLOSE c_susa;
709 END LOOP;
710 ELSIF (v_s_fee_trigger_cat = 'COMPOSITE') THEN
711 -- check IGS_PS_COURSE fee triggers
712 FOR v_cft_rec IN c_cft
713 LOOP
714 IF (v_cft_rec.fee_trigger_group_number IS NULL) THEN
715 -- Trigger Fired
716 p_trigger_fired := 'CFT';
717 RETURN TRUE;
718 ELSE
719 -- check the fee trigger group members
720 IF (finpl_val_trig_group(v_cft_rec.fee_trigger_group_number) = TRUE) THEN
721 -- Trigger Fired
722 p_trigger_fired := 'COMPOSITE';
723 RETURN TRUE;
724 END IF;
725 END IF;
726 END LOOP;
727 -- check IGS_PS_UNIT fee triggers
728 FOR v_uft_rec IN c_uft
729 LOOP
730 IF (v_uft_rec.fee_trigger_group_number IS NOT NULL) THEN
731 -- check the fee trigger group members
732 IF (finpl_val_trig_group(v_uft_rec.fee_trigger_group_number) = TRUE) THEN
733 -- Trigger Fired
734 p_trigger_fired := 'COMPOSITE';
735 RETURN TRUE;
736 END IF;
737 ELSE
738 OPEN c_sua (v_uft_rec.unit_cd,
739 v_uft_rec.version_number,
740 v_uft_rec.CAL_TYPE,
741 v_uft_rec.ci_sequence_number,
742 v_uft_rec.location_cd,
743 v_uft_rec.UNIT_CLASS,
744 g_v_no);
745 FETCH c_sua INTO v_check;
746 IF (c_sua%FOUND) THEN
747 CLOSE c_sua;
748 -- Trigger Fired
749 p_trigger_fired := 'UFT';
750 RETURN TRUE;
751 END IF;
752 CLOSE c_sua;
753
754 END IF;
755 END LOOP;
756 -- check IGS_PS_UNIT set fee triggers
757 FOR v_usft_rec IN c_usft
758 LOOP
759 IF (v_usft_rec.fee_trigger_group_number IS NOT NULL) THEN
760 -- check the fee trigger group members
761 IF (finpl_val_trig_group(v_usft_rec.fee_trigger_group_number) = TRUE) THEN
762 -- Trigger Fired
763 p_trigger_fired := 'COMPOSITE';
764 RETURN TRUE;
765 END IF;
766 ELSE
767 OPEN c_susa ( v_usft_rec.unit_set_cd,
768 v_usft_rec.version_number);
769 FETCH c_susa INTO v_check;
770 IF (c_susa%FOUND) THEN
771 CLOSE c_susa;
772 -- Trigger Fired
773 p_trigger_fired := 'USFT';
774 RETURN TRUE;
775 END IF;
776 CLOSE c_susa;
777 END IF;
778 END LOOP;
779
780 -- For System Fee Trigger type of Audit
781 ELSIF (v_s_fee_trigger_cat = 'AUDIT') THEN
782 l_b_fee_trigger_found := FALSE;
783 -- Check if any unit fee triggers have been defined
784 FOR v_uft_rec IN c_uft LOOP
785 -- Set flag if trigger is found
786 l_b_fee_trigger_found := TRUE;
787
788 -- Check if any of the triggers found are for auditable units
789 -- (auditable indicator to be 'Y')
790 OPEN c_sua (v_uft_rec.unit_cd,
791 v_uft_rec.version_number,
792 v_uft_rec.cal_type,
793 v_uft_rec.ci_sequence_number,
794 v_uft_rec.location_cd,
795 v_uft_rec.unit_class,
796 g_v_yes);
797 FETCH c_sua INTO v_check;
798 IF (c_sua%FOUND) THEN
799 CLOSE c_sua;
800 -- Trigger Fired
801 p_trigger_fired := 'AUDIT';
802 RETURN TRUE;
803 END IF;
804 CLOSE c_sua;
805 END LOOP;
806
807 -- If triggers were found, but none were auditable, return False
808 IF l_b_fee_trigger_found THEN
809 p_trigger_fired := NULL;
810 RETURN FALSE;
811 END IF;
812
813 -- If Unit Triggers are not found or there is no auditable unit trigger, then
814 -- check if student has any auditable unit attempts effective as on
815 -- the effective date
816
817 OPEN c_sua_audit_one(p_person_id, p_course_cd);
818 FETCH c_sua_audit_one INTO v_check;
819 IF c_sua_audit_one%FOUND THEN
820 CLOSE c_sua_audit_one;
821 p_trigger_fired := 'AUDIT';
822 RETURN TRUE;
823 END IF;
824 CLOSE c_sua_audit_one;
825 END IF;
826
827 -- Trigger did not fire
828 p_trigger_fired := NULL;
829 RETURN FALSE;
830
831 END;
832
833 EXCEPTION
834 WHEN OTHERS THEN
835 FND_MESSAGE.SET_NAME('IGS','IGS_GE_UNHANDLED_EXP');
836 FND_MESSAGE.SET_TOKEN('NAME','IGS_FI_GEN_005.FINP_VAL_FEE_TRIGGER');
837 IGS_GE_MSG_STACK.ADD;
838 lv_param_values := p_fee_cat||','||
839 p_fee_cal_type||','||to_char(p_fee_ci_sequence_number)||','||
840 p_fee_type||','||p_s_fee_trigger_cat||','||
841 fnd_date.date_to_displaydt(p_effective_dt)||','||
842 to_char(p_person_id)||','||
843 p_course_cd||','||to_char(p_version_number)||','||
844 p_cal_type||','||p_location_cd||','||
845 p_attendance_mode||','||p_attendance_type;
846
847 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PARAMETERS');
848 FND_MESSAGE.SET_TOKEN('VALUE',lv_param_values);
849 IGS_GE_MSG_STACK.ADD;
850 App_Exception.Raise_Exception;
851
852 END finp_val_fee_trigger;
853
854 --
855 FUNCTION fins_val_fee_trigger(
856 p_fee_cat IGS_FI_F_CAT_CA_INST.FEE_CAT%TYPE ,
857 p_fee_cal_type IGS_FI_F_CAT_CA_INST.fee_cal_type%TYPE ,
858 p_fee_ci_sequence_number IGS_FI_F_CAT_CA_INST.fee_ci_sequence_number%TYPE ,
859 p_fee_type IGS_FI_F_CAT_FEE_LBL_ALL.FEE_TYPE%TYPE ,
860 p_s_fee_trigger_cat IGS_FI_FEE_TYPE_ALL.s_fee_trigger_cat%TYPE ,
861 p_effective_dt DATE ,
862 p_person_id IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
863 p_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
864 p_version_number IGS_EN_STDNT_PS_ATT_ALL.version_number%TYPE ,
865 p_cal_type IGS_EN_STDNT_PS_ATT_ALL.CAL_TYPE%TYPE ,
866 p_location_cd IGS_EN_STDNT_PS_ATT_ALL.location_cd%TYPE ,
867 p_attendance_mode IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_MODE%TYPE ,
868 p_attendance_type IGS_EN_STDNT_PS_ATT_ALL.ATTENDANCE_TYPE%TYPE )
869 RETURN CHAR AS
870 BEGIN
871 DECLARE
872 v_trigger_fired igs_lookups_view.lookup_code%TYPE;
873 BEGIN
874 IF finp_val_fee_trigger(p_fee_cat,
875 p_fee_cal_type,
876 p_fee_ci_sequence_number,
877 p_fee_type,
878 p_s_fee_trigger_cat,
879 p_effective_dt,
880 p_person_id,
881 p_course_cd,
882 p_version_number,
883 p_cal_type,
884 p_location_cd,
885 p_attendance_mode,
886 p_attendance_type,
887 v_trigger_fired) = TRUE THEN
888 RETURN 'Y';
889 ELSE
890 RETURN 'N';
891 END IF;
892 END;
893 END fins_val_fee_trigger;
894 --
895 --
896 PROCEDURE finp_set_pymnt_schdl(
897 errbuf out NOCOPY varchar2,
898 retcode out NOCOPY number,
899 P_FEE_ASSESSMENT_PERIOD IN VARCHAR2,
900 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
901 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
902 p_fee_category IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
903 p_grace_days IN NUMBER ,
904 p_effective_dt_c IN VARCHAR2 ,
905 p_notification_dt_c IN VARCHAR2 ,
906 p_include_man_entries IN VARCHAR2 ,
907 p_next_bus_day IN VARCHAR2 ,
908 p_org_id NUMBER
909 ) AS
910 BEGIN -- finp_set_pymnt_schdl
911 -- As per the SFCR005, this concurrent program has been obsoleted
912 -- If the User is trying to run this concurrent program, then the error message
913 -- should be written to the log file that the concurrent program has been obsoleted
914 -- and cannot be run
915 retcode:=0;
916 FND_MESSAGE.Set_Name('IGS',
917 'IGS_GE_OBSOLETE_JOB');
918 FND_FILE.Put_Line(FND_FILE.Log,
919 FND_MESSAGE.Get);
920 EXCEPTION
921 WHEN OTHERS THEN
922 RETCODE:=2;
923 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
924 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
925 END finp_set_pymnt_schdl;
926
927 FUNCTION finp_get_receivables_inst RETURN IGS_FI_CONTROL.Rec_Installed%TYPE AS
928 ------------------------------------------------------------------
929 --
930 --Change History:
931 --Who When What
932 --smadathi 27-Feb-2002 Bug 2238413. Exception will be raised if
933 -- no record has been found in igs_fi_control table
934 -------------------------------------------------------------------
935 lv_rec_installed IGS_FI_CONTROL.Rec_Installed%TYPE;
936
937 -- Cursor fro getting the value of the Rec_Installed flag
938 -- in the table IGS_FI_CONTROL
939 CURSOR cur_ctrl IS
940 SELECT rec_installed
941 FROM igs_fi_control;
942 BEGIN
943
944 -- Open the cursor and fetch the value of the REC_INSTALLED flag
945 OPEN cur_ctrl;
946 FETCH cur_ctrl INTO lv_rec_installed;
947 -- If no records are found in IGS_FI_CONTROL table
948 -- exception is raised.
949 IF cur_ctrl%NOTFOUND THEN
950 CLOSE cur_ctrl;
951 FND_MESSAGE.SET_NAME('IGS','IGS_FI_SYSTEM_OPT_SETUP');
952 IGS_GE_MSG_STACK.ADD;
953 APP_EXCEPTION.RAISE_EXCEPTION;
954 END IF;
955 CLOSE cur_ctrl;
956
957 -- If the records are not found, then this means that a Receivables system is not
958 -- installed
959 -- if the value of lv_rec_installed is
960 -- N : No receivables system is installed
961 -- Y : Oracle Accounts Receivables is installed
962 lv_rec_installed := NVL(lv_rec_installed,'N');
963 RETURN lv_rec_installed;
964 END finp_get_receivables_inst;
965
966 FUNCTION finp_get_acct_meth RETURN igs_fi_control.accounting_method%TYPE AS
967 /*|| Created By :Sarakshi
968 || Created On :02-Feb-2002
969 || Purpose : For returning the accounting method.
970 || Known limitations, enhancements or remarks :
971 || Change History :
972 || Who When What
973 || pathipat 18-Apr-2003 Enh:2831569 - Commercial Receivables build
974 || Added code for manage_accounts.
975 || (reverse chronological order - newest change first) */
976
977 CURSOR cur_acc IS
978 SELECT manage_accounts,accounting_method
979 FROM igs_fi_control;
980
981 l_accounting_method igs_fi_control.accounting_method%TYPE;
982 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
983
984 BEGIN
985 OPEN cur_acc;
986 FETCH cur_acc INTO l_v_manage_accounts, l_accounting_method;
987 IF cur_acc%FOUND THEN
988 CLOSE cur_acc;
989 -- If manage_accounts = Other, then return Accrual as the
990 -- accounting method for internal processing.
991 IF l_v_manage_accounts = 'OTHER' THEN
992 l_accounting_method := 'ACCRUAL';
993 END IF;
994 RETURN l_accounting_method;
995 ELSE
996 CLOSE cur_acc;
997 RETURN NULL;
998 END IF;
999 END finp_get_acct_meth;
1000
1001 FUNCTION finp_get_prsid_grp_code(p_n_group_id igs_pe_persid_group.group_id%TYPE)
1002 RETURN VARCHAR2 AS
1003 ------------------------------------------------------------------
1004 --Created by : Sanil Madathil, Oracle IDC
1005 --Date created: 03 jan 2003
1006 --
1007 --Purpose: This generic function returns group code for the person group id
1008 -- passed as parameter
1009 --
1010 --
1011 --
1012 --Known limitations/enhancements and/or remarks:
1013 --
1014 --Change History:
1015 --Who When What
1016 ------------------------------------------------------------------
1017
1018 CURSOR c_igs_pe_prsid_group (cp_n_group_id igs_pe_persid_group.group_id%TYPE) IS
1019 SELECT group_cd
1020 FROM igs_pe_persid_group
1021 WHERE group_id = cp_n_group_id;
1022
1023 l_c_group_cd igs_pe_persid_group.group_cd%TYPE := NULL;
1024
1025 BEGIN
1026
1027 -- if person group id passed is NULL, the function returns null value
1028 -- if person group id passed is invalid, the function returns group id value itself
1029 -- if person group id passed is valid, the function returns group code
1030 IF p_n_group_id IS NULL THEN
1031 RETURN NULL;
1032 ELSE
1033 OPEN c_igs_pe_prsid_group(cp_n_group_id => p_n_group_id);
1034 FETCH c_igs_pe_prsid_group INTO l_c_group_cd;
1035 IF c_igs_pe_prsid_group%NOTFOUND
1036 THEN
1037 CLOSE c_igs_pe_prsid_group;
1038 RETURN p_n_group_id;
1039 END IF;
1040 CLOSE c_igs_pe_prsid_group;
1041 RETURN l_c_group_cd;
1042 END IF;
1043 END finp_get_prsid_grp_code;
1044
1045
1046 END igs_fi_gen_005;
[Home] [Help]