1 PACKAGE BODY igs_fi_gen_004 AS
2 /* $Header: IGSFI04B.pls 120.15 2006/06/22 12:55:55 abshriva ship $ */
3
4 /* Who When What
5 abshriva 22-JUN-2006 Bug 5070074 Modifcation made in procedure 'finp_prc_enr_fee_ass()'
6 abshriva 04-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
7 abshriva 05-Dec-2005 Bug:4721566.Modification made in procedure 'finp_prc_enr_fee_ass()'
8 abshriva 05-Dec-2005 Bug:4701695 Modification made in procedure 'finp_prc_enr_fee_ass()'
9 pathipat 30-Sep-2005 Bug 4570538: FEE ASSESSMENTS FROM TO DO ENTRIES IGNORES PARM FEE ASSESSMENT PERIOD
10 Modified finp_prc_enr_fa_todo
11 bannamal 26-Aug-2005 Enh 3392095: Tuition Waiver build
12 bannamal 01-Aug-2005 Enh 3392088: Campus Privilege Fees build
13 bannamal 27-May-2005 Fee Calculation Performance Enhancement. Changes done as per TD.
14 shtatiko 29-JUL-2004 Bug# 2734512, Modified message handling in finp_prc_enr_fa_todo
15 vvutukur 03-Feb-2004 Enh#3167098.FICR112 Build. Modified finp_prc_cfar.
16 shtatiko 24-DEC-2003 Enh# 3167098, Modified finp_prc_sca_unconf, finp_prc_enr_fee_ass, finpl_prc_reverse_fee_assess
17 uudayapr 15-DEC-2003 Bug#3080983 Modified the Procedure finp_prc_enr_fee_ass,finp_prc_sca_unconf
18 and the input parameter of procedure finpl_prc_reverse_fee_assess, finpl_prc_this_crs_liable,
19 finpl_prc_another_crs_liable to use IGS_FI_FEE_AS instead of IGS_FI_FEE_ASS_DEBT_V.
20 shtatiko 25-NOV-2003 Bug# 3230754, Modified finp_prc_enr_fa_todo.
21 pathipat 04-Nov-2003 Bug: 3151102 - Modified finp_prc_enr_fee_ass() Removed conditions before
22 setting p_create_dt to v_create_dt
23 pathipat 17-Oct-2003 Bug: 3151102 - Modified finp_prc_enr_fee_ass() - Added begin-end block for exception handling
24 pathipat 07-Oct-2003 Bug 3122652: Modified finp_prc_enr_fee_ass() - Added validation for person id group
25 pathipat 23-Sep-2003 Bug: 3151102 - Modified finp_prc_enr_fee_ass()
26 pathipat 09-Sep-2003 Bug 3122652: Modified finp_prc_enr_fee_ass() - Replaced call to igf_ap_ss_pkg.get_pid() with
27 call to igs_pe_dynamic_persid_group.igs_get_dynamic_sql()
28 Removed commented out code
29 knaraset 12-May-2003 Modified cursor c_get_todo_ref_csr to select uoo_id in procedure finp_prc_fa_ref_todo,
30 also added uoo_id in TBH call to todo_ref, as part of MUS build bug 2829262
31 shtatiko 06-MAY-2003 Enh# 2831569, Modified finp_prc_enr_fee_ass and finp_prc_enr_fa_todo.
32 shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE parameter with more meaningful messages.
33 Affected procedures are finp_prc_enr_fa_todo, finp_prc_enr_fee_ass and finp_prc_cfar
34 vchappid 21-Jan-2003 Bug#2711202, in the procedure finp_prc_enr_fa_todo,for the in-out variable for p_creation_dt
35 parameter in the fee assessment call should be passed as v_creation_dt which is defined in the
36 Main Procedure Call instead of the local variable defined in the local procedure finp_prc_fa_ref_todo
37 vchappid 06-Jan-2003 Bug# 2660155, In procedure finp_prc_enr_fee_ass, Modified code to identify distinct persons when user don't
38 provide Person Id or Person ID Group as an input value to the request.
39 vchappid 02-Jan-2003 Bug# 2727402, Unhandled Exception should not occur when GL Date passed is invalid
40 vchappid 11-Nov-02 Bug# 2584986, GL- Interface Build New Date parameter.
41 p_d_gl_date is added to the finp_prc_enr_fa_todo, finp_prc_enr_fee_ass
42 procedure specification
43 In the procedure finp_prc_sca_unconf, in the local procedure finpl_prc_reverse_fee_assess
44 a new gl_date parameter is passed as system date to the procedure igs_fi_prc_fee_ass.finp_ins_enr_fee_ass call
45 vchappid 17-Oct-02 Enh bug#2595962.Modified procedures finp_prc_fa_ref_todo,
46 finp_prc_enr_fa_todo,finp_prc_enr_fee_ass and
47 finpl_prc_reverse_fee_assess.
48 jbegum 06-jun-02 As part of bug fix of bug #2318488 the local procedure finp_prc_hecs_pymnt_optn is being
49 obsoleted
50
51 vchappid 24-May-2002 Bug#2228743, in the local procedure finpl_prc_reverse_fee_assess of finp_prc_sca_unconf
52 fee assessment call has been changed to pass course cd in case of program approach, course type
53 incase of the career approach and will pass null in the case of primary_career approach
54
55 vchappid 21-May-2002 Bug#2374754, removed the clause 'for update NOWAIT' form the cursor c_get_todo_ref_csr
56 in the local procedure finp_prc_fa_ref_todo, removed the commented code,
57 In the fee assessment call process mode parameter is incorrectly passed as NULL,
58 'ACTUAL' is passed instead of NULL, re-initialized the process next record variable
59 to FASLE, it might have been set to TRUE while processing the previously fetched record
60 Log messages format is changed
61
62 rnirwani 05-May-02 Bug#2329407 removeed reference to IGS_FI_DSBR_SPSHT
63 rnirwani 25-Apr-02 Bug# 2329407 Modified Procedure: finp_prc_enr_fa_todo
64 decalaration of cursor variable of cursor c_fee_cal_instance
65 was pointed to fee calendar from fin calendar
66
67 Modified Procedure: finp_prc_enr_fee_ass
68 decalaration of cursor variable of cursor c_fee_cal_instance
69 was pointed to fee calendar from fin calendar
70
71 Modified the procedure finpl_prc_reverse_fee_assess
72 Removed the parameters : p_fin_cal_type, p_fin_ci_sequence_number.
73
74 Modified the procedure finp_prc_sca_unconf
75 Altered cursor c_fasdv to not select fin calendar caloumns
76 Removed passage of fin calendar to invocation of procedure
77 finpl_prc_reverse_fee_assess
78
79 schodava 01-APR-2002 Enh # 2280971
80 Modified procedure finp_prc_enr_fa_todo
81 New local procedure finpl_prc_fa_ref_todo
82 added.
83 smadathi 03-JAN-2002 Bug 2170429 : removed the private procedure
84 finpl_prc_end_fee_sponsorship and all the references to it.
85 vchappid 02-Jan-2002 Enh # 2162747 : Removed the reference to parameter p_fin_cal,
86 p_c_career parameter is introduced in the fee Assessment routine
87 schodava 28-NOV-2001 Enh # 2122257 : Implements the CR for 'Fee Category Change'
88 Change in Procedure finp_prc_sca_unconf
89 */
90
91 g_d_sysdate CONSTANT DATE := TRUNC(SYSDATE);
92 g_v_ind_no CONSTANT VARCHAR2(1) := 'N';
93
94 FUNCTION finp_prc_cfar(
95 p_person_id IGS_FI_FEE_AS_RT.person_id%TYPE ,
96 p_course_cd IGS_FI_FEE_AS_RT.course_cd%TYPE ,
97 p_commencement_dt IGS_FI_FEE_AS_RT.start_dt%TYPE ,
98 p_completion_dt IGS_FI_FEE_AS_RT.end_dt%TYPE ,
99 p_message_name OUT NOCOPY VARCHAR2 )
100 RETURN BOOLEAN AS
101 ------------------------------------------------------------------
102 --Created by :
103 --Date created:
104 --
105 --Purpose:
106 --
107 --
108 --Known limitations/enhancements and/or remarks:
109 --
110 --Change History:
111 --Who When What
112 --vvutukur 03-Feb-2004 Enh#3167098.FICR112 Build. Modified c_scafcflrv.
113 ------------------------------------------------------------------
114
115 BEGIN --finp_prc_cfar
116 --This routine is used to control the creation of contract fee assessment
117 --rate records from TUITION fee liabilities
118 DECLARE
119 v_exit_loop BOOLEAN := FALSE;
120 v_rec_found BOOLEAN := FALSE;
121 cst_tuition CONSTANT IGS_FI_F_CAT_FEE_LBL_SCA_RT_V.s_fee_type%TYPE := 'TUITION';
122 cst_active
123 CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE :='ACTIVE';
124 cst_planned
125 CONSTANT IGS_FI_FEE_STR_STAT.s_fee_structure_status%TYPE :='PLANNED';
126 v_previous_fee_type IGS_FI_FEE_TYPE.FEE_TYPE%TYPE;
127
128 CURSOR c_scafcflrv( cp_v_s_fee_structure_status igs_fi_fee_str_stat.s_fee_structure_status%TYPE,
129 cp_v_closed_ind igs_fi_fee_type.closed_ind%TYPE,
130 cp_v_s_fee_trigger_cat igs_fi_fee_type.s_fee_trigger_cat%TYPE,
131 cp_v_s_fee_type1 igs_fi_fee_type.s_fee_type%TYPE,
132 cp_v_s_fee_type2 igs_fi_fee_type.s_fee_type%TYPE,
133 cp_v_s_fee_type3 igs_fi_fee_type.s_fee_type%TYPE,
134 cp_v_chk_spa_liable VARCHAR2,
135 cp_v_s_relation_type1 igs_fi_fee_as_rate.s_relation_type%TYPE,
136 cp_v_s_relation_type2 igs_fi_fee_as_rate.s_relation_type%TYPE
137 ) IS
138 SELECT fcflv.fee_type fee_type,
139 far.chg_rate chg_rate,
140 fcflv.fee_cal_type fee_cal_type,
141 igs_ca_gen_001.calp_get_alias_val(fcflv.start_dt_alias,
142 fcflv.start_dai_sequence_number,
143 fcflv.fee_cal_type,
144 fcflv.fee_ci_sequence_number
145 ) start_dt,
146 far.location_cd chg_rate_location_cd,
147 far.attendance_type chg_rate_attendance_type,
148 far.attendance_mode chg_rate_attendance_mode
149 FROM igs_en_stdnt_ps_att spa,
150 igs_fi_f_cat_fee_lbl_v fcflv,
151 igs_fi_fee_str_stat fsst,
152 igs_fi_fee_as_rate far,
153 igs_fi_fee_type ft,
154 igs_fi_fee_as_rt cfar
155 WHERE spa.person_id = p_person_id
156 AND spa.course_cd = p_course_cd
157 AND spa.fee_cat = fcflv.fee_cat
158 AND fcflv.fee_liability_status = fsst.fee_structure_status
159 AND fsst.s_fee_structure_status = cp_v_s_fee_structure_status
160 AND fcflv.fee_type = ft.fee_type
161 AND ft.closed_ind = cp_v_closed_ind
162 AND ft.s_fee_trigger_cat <> cp_v_s_fee_trigger_cat
163 AND ft.s_fee_type in (cp_v_s_fee_type1, cp_v_s_fee_type2, cp_v_s_fee_type3)
164 AND igs_fi_gen_001.check_stdnt_prg_att_liable (spa.person_id,
165 spa.course_cd,
166 spa.version_number,
167 spa.fee_cat,
168 fcflv.fee_type,
169 ft.s_fee_trigger_cat,
170 fcflv.fee_cal_type,
171 fcflv.fee_ci_sequence_number,
172 spa.adm_admission_appl_number,
173 spa.adm_nominated_course_cd,
174 spa.adm_sequence_number,
175 spa.commencement_dt,
176 spa.discontinued_dt,
177 spa.cal_type,
178 spa.location_cd,
179 spa.attendance_mode,
180 spa.attendance_type) = cp_v_chk_spa_liable
181 AND far.fee_type = fcflv.fee_type
182 AND far.fee_cal_type = fcflv.fee_cal_type
183 AND far.fee_ci_sequence_number = fcflv.fee_ci_sequence_number
184 AND (
185 (far.fee_cat = fcflv.fee_cat AND far.s_relation_type = cp_v_s_relation_type1)
186 OR
187 (far.fee_cat is NULL and far.s_relation_type = cp_v_s_relation_type2)
188 )
189 AND far.logical_delete_dt is NULL
190 AND NVL(far.location_cd (+), spa.location_cd) = spa.location_cd
191 AND NVL(far.attendance_type (+),spa.attendance_type) = spa.attendance_type
192 AND NVL(far.attendance_mode (+),spa.attendance_mode) = spa.attendance_mode
193 AND NVL(far.course_cd (+), spa.course_cd) = spa.course_cd
194 AND (
195 (cfar.person_id = spa.person_id AND cfar.course_cd = spa.course_cd AND cfar.fee_type <> fcflv.fee_type)
196 OR
197 (cfar.person_id <> spa.person_id AND cfar.course_cd <> spa.course_cd)
198 )
199 UNION
200 SELECT fcflv.fee_type fee_type,
201 far.chg_rate chg_rate,
202 fcflv.fee_cal_type fee_cal_type,
203 igs_ca_gen_001.calp_get_alias_val(fcflv.start_dt_alias,
204 fcflv.start_dai_sequence_number,
205 fcflv.fee_cal_type,
206 fcflv.fee_ci_sequence_number
207 ) start_dt,
208 far.location_cd chg_rate_location_cd,
209 far.attendance_type chg_rate_attendance_type,
210 far.attendance_mode chg_rate_attendance_mode
211 FROM igs_en_spa_terms spt,
212 igs_en_stdnt_ps_att spa,
213 igs_fi_f_cat_fee_lbl_v fcflv,
214 igs_fi_fee_str_stat fsst,
215 igs_fi_fee_as_rate far,
216 igs_fi_fee_type ft,
217 igs_fi_fee_as_rt cfar
218 WHERE spt.person_id = p_person_id
219 AND spt.program_cd = p_course_cd
220 AND spt.person_id = spa.person_id
221 AND spt.program_cd = spa.course_cd
222 AND spt.fee_cat = fcflv.fee_cat
223 AND fcflv.fee_liability_status = fsst.fee_structure_status
224 AND fsst.s_fee_structure_status = cp_v_s_fee_structure_status
225 AND fcflv.fee_type = ft.fee_type
226 AND ft.closed_ind = cp_v_closed_ind
227 AND ft.s_fee_trigger_cat <> cp_v_s_fee_trigger_cat
228 AND ft.s_fee_type in (cp_v_s_fee_type1, cp_v_s_fee_type2, cp_v_s_fee_type3)
229 AND igs_fi_gen_001.check_stdnt_prg_att_liable (spt.person_id,
230 spt.program_cd,
231 spt.program_version,
232 spt.fee_cat,
233 fcflv.fee_type,
234 ft.s_fee_trigger_cat,
235 fcflv.fee_cal_type,
236 fcflv.fee_ci_sequence_number,
237 spa.adm_admission_appl_number,
238 spa.adm_nominated_course_cd,
239 spa.adm_sequence_number,
240 spa.commencement_dt,
241 spa.discontinued_dt,
242 spa.cal_type,
243 spt.location_cd,
244 spt.attendance_mode,
245 spt.attendance_type) = cp_v_chk_spa_liable
246 AND far.fee_type = fcflv.fee_type
247 AND far.fee_cal_type = fcflv.fee_cal_type
248 AND far.fee_ci_sequence_number = fcflv.fee_ci_sequence_number
249 AND (
250 (far.fee_cat = fcflv.fee_cat and far.s_relation_type = cp_v_s_relation_type1)
251 OR
252 (far.fee_cat is NULL and far.s_relation_type = cp_v_s_relation_type2)
253 )
254 AND far.logical_delete_dt is NULL
255 AND NVL(far.location_cd (+), spt.location_cd) = spt.location_cd
256 AND NVL(far.attendance_type (+),spt.attendance_type) = spt.attendance_type
257 AND NVL(far.attendance_mode (+),spt.attendance_mode) = spt.attendance_mode
258 AND NVL(far.course_cd (+), spt.program_cd) = spt.program_cd
259 AND (
260 (cfar.person_id = spt.person_id AND cfar.course_cd = spt.program_cd AND cfar.fee_type <> fcflv.fee_type)
261 OR
262 (cfar.person_id <> spt.person_id AND cfar.course_cd <> spt.program_cd)
263 )
264 ORDER BY 1,4;
265
266
267 BEGIN
268 p_message_name := Null;
269 IF p_person_id IS NULL OR
270 p_course_cd IS NULL OR
271 p_commencement_dt IS NULL OR
272 p_completion_dt IS NULL THEN
273 -- Replaced message IGS_GE_INVALID_VALUE with IGS_FI_PARAMETER_NULL
274 Fnd_Message.Set_Name ('IGS', 'IGS_FI_PARAMETER_NULL');
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
277 END IF;
278 v_previous_fee_type := NULL;
279 FOR v_scafcflrv_rec IN c_scafcflrv('ACTIVE','N','INSTITUTN','OTHER','TUTNFEE','TUITION','TRUE','FCFL','FTCI') LOOP
280 v_rec_found := TRUE;
281 IF v_previous_fee_type IS NULL OR
282 v_previous_fee_type <> v_scafcflrv_rec.FEE_TYPE THEN
283 --Create a contract using the current default fee assessment rate
284 IF IGS_FI_GEN_003.finp_ins_cfar(
285 p_person_id,
286 p_course_cd,
287 v_scafcflrv_rec.FEE_TYPE,
288 v_scafcflrv_rec.start_dt,
289 p_completion_dt,
290 v_scafcflrv_rec.chg_rate_location_cd,
291 v_scafcflrv_rec.chg_rate_attendance_type,
292 v_scafcflrv_rec.chg_rate_attendance_mode,
293 v_scafcflrv_rec.chg_rate,
294 'N',
295 p_message_name) = FALSE THEN
296 v_exit_loop := TRUE;
297 EXIT;
298 END IF;
299 END IF;
300 v_previous_fee_type := v_scafcflrv_rec.FEE_TYPE;
301 END LOOP;
302 IF v_rec_found = FALSE THEN
303 p_message_name := 'IGS_FI_NO_CONTRACT_FEE_RATES';
304 RETURN FALSE;
305 END IF;
306 IF v_exit_loop THEN
307 RETURN FALSE;
308 END IF;
309 RETURN TRUE;
310 EXCEPTION
311 WHEN OTHERS THEN
312 IF c_scafcflrv%ISOPEN THEN
313 CLOSE c_scafcflrv;
314 END IF;
315 RAISE;
316 END;
317 EXCEPTION
318 WHEN OTHERS THEN
319 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
320 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINP_PRC_CFAR');
321 IGS_GE_MSG_STACK.ADD;
322 App_Exception.Raise_Exception;
323 END finp_prc_cfar;
324 --
325 PROCEDURE finp_prc_disb_jnl(
326 errbuf out NOCOPY varchar2,
327 retcode out NOCOPY NUMBER,
328 p_fin_period IN VARCHAR2 ,
329 p_fee_period IN VARCHAR2,
330 p_fee_type IN IGS_FI_FEE_TYPE_all.fee_type%TYPE ,
331 p_snapshot_create_dt_C IN DATE,
332 p_income_type IN VARCHAR2 ,
333 p_ignore_prior_journals IN CHAR ,
334 p_percent_disbursement IN NUMBER,
335 p_org_id NUMBER
336 ) AS
337 BEGIN
338 retcode:=0;
339
340 -- As per SFCR005, this concurrent program is obsolete and if the user
341 -- tries to run this program then an error message should be logged into the log
342 -- file that the concurrent program is obsolete and should not be run.
343 FND_MESSAGE.Set_Name('IGS',
344 'IGS_GE_OBSOLETE_JOB');
345 FND_FILE.Put_Line(FND_FILE.Log,
346 FND_MESSAGE.Get);
347 EXCEPTION
348 WHEN OTHERS THEN
349 RETCODE:=2;
350 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
351 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
352 END finp_prc_disb_jnl;
353 --
354 PROCEDURE finp_prc_disb_snpsht(
355 errbuf out NOCOPY varchar2,
356 retcode out NOCOPY NUMBER,
357 p_fin_period IN VARCHAR2,
358 p_fee_period IN VARCHAR2,
359 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE,
360 p_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE,
361 p_org_id NUMBER
362 ) AS
363 BEGIN
364 retcode:=0;
365 -- As per SFCR005, this concurrent program is obsolete and if the user
366 -- tries to run this program then an error message should be logged into the log
367 -- file that the concurrent program is obsolete and should not be run.
368 FND_MESSAGE.Set_Name('IGS',
369 'IGS_GE_OBSOLETE_JOB');
370 FND_FILE.Put_Line(FND_FILE.Log,
371 FND_MESSAGE.Get);
372 EXCEPTION
373 WHEN OTHERS THEN
374 RETCODE:=2;
375 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
376 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
377 END finp_prc_disb_snpsht;
378 --
379 PROCEDURE finp_prc_enr_fa_todo(
380 errbuf out NOCOPY varchar2,
381 retcode out NOCOPY number,
382 P_FEE_CAL IN VARCHAR2 ,
383 p_org_id NUMBER,
384 p_d_gl_date IN VARCHAR2
385 ) AS
386
387 /* Who When What
388 pathipat 30-Sep-2005 Bug 4570538: FEE ASSESSMENTS FROM TO DO ENTRIES IGNORES PARM FEE ASSESSMENT PERIOD
389 Modified logic to invoke finp_prc_fa_ref_todo
390 shtatiko 29-JUL-2004 Bug# 2734512, Modified message handling.
391 shtatiko 25-NOV-2003 Bug# 3230754, Modified finp_prc_fa_ref_todo. Added check for logical delete date is
392 added in cursor c_get_todo_ref_csr's where clause.
393 shtatiko 18-NOV-2003 Enh# 3117341, Added check for profile 'IGS: Charge tuition for Audited Student Attempt'
394 shtatiko 08-MAY-2003 Enh# 2831569, Added Check for Manage Accounts System Option before running the process.
395 shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE with more meaningful messages.
396 vchappid 21-Jan-2003 Bug#2711202, for the in-out variable for p_creation_dt parameter in the fee assessment call
397 should be passed as v_creation_dt which is defined in the Main Procedure Call instead of the
398 local variable defined in the local procedure finp_prc_fa_ref_todo
399 vchappid 02-Jan-2003 Bug#2727402, Unhandled Exception should not occur when GL Date passed is invalid
400 vchappid 21-May-2002 Bug#2374754, removed the clause 'for update NOWAIT' form the cursor c_get_todo_ref_csr
401 in the local procedure finp_prc_fa_ref_todo, removed the commented code,
402 In the fee assessment call process mode parameter is incorrectly passed as NULL,
403 'ACTUAL' is passed instead of NULL, re-initialized the process next record variable
404 to FASLE, it might have been set to TRUE while processing the previously fetched record
405 */
406
407 p_fee_cal_type igs_ca_inst.cal_type%TYPE ;
408 p_fee_ci_sequence_num igs_ca_inst.sequence_number%TYPE ;
409
410 l_v_message_name fnd_new_messages.message_name%TYPE;
411 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
412 l_n_waiver_amount NUMBER;
413
414 BEGIN -- finp_prc_enr_fa_todo
415 -- Module to control processing fee assessments from entries in the student
416 -- todo table
417 --Block for Parameter Validation/Splitting of Parameters
418
419 igs_ge_gen_003.set_org_id(p_org_id);
420
421 retcode:=0;
422 BEGIN
423 p_fee_cal_type := RTRIM(SUBSTR(p_fee_cal, 102, 10));
424 p_fee_ci_sequence_num := TO_NUMBER(LTRIM(SUBSTR(p_fee_cal, 113, 8)));
425
426 -- Get the value of "Manage Accounts" System Option value.
427 -- If this value is NULL then this process cannot run. Added as part of Enh# 2831569.
428 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
429 p_v_message_name => l_v_message_name );
430 IF l_v_manage_accounts IS NULL THEN
431 fnd_message.set_name ( 'IGS', l_v_message_name );
432 fnd_file.put_line (fnd_file.log, ' ');
433 fnd_file.put_line (fnd_file.log, fnd_message.get);
434 fnd_file.put_line (fnd_file.log, ' ');
435 retcode :=2;
436 RETURN;
437 END IF;
438
439 END;
440 --End of Block for Parameter Validation/Splitting of Parameters
441 DECLARE
442 cst_fee_recalc CONSTANT VARCHAR2(10) := 'FEE_RECALC';
443 v_dummy VARCHAR2(1);
444 v_record_found BOOLEAN := FALSE;
445 v_creation_dt DATE;
446 v_message_name VARCHAR2(30);
447 l_rpt_person_id hz_parties.party_id%TYPE;
448 l_return_status VARCHAR2(1);
449 l_msg_data fnd_new_messages.message_name%TYPE;
450 l_fee_cal_type igs_ca_inst.cal_type%TYPE;
451 l_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
452 l_d_gl_date DATE;
453 l_c_closing_status igs_fi_gl_periods_v.closing_status%TYPE;
454 l_n_msg_count NUMBER(10);
455 l_v_msg_data VARCHAR2(1000);
456
457 l_v_include_audit VARCHAR2(1);
458 l_v_message fnd_new_messages.message_name%TYPE;
459 l_v_load_cal_type igs_ca_inst_all.cal_type%TYPE;
460 l_n_load_ci_seq_num igs_ca_inst_all.sequence_number%TYPE;
461
462 CURSOR c_fee_cal_instance (
463 cp_fee_cal_type IGS_FI_F_TYP_CA_INST.fee_cal_type%TYPE,
464 cp_fee_ci_sequence_number IGS_FI_F_TYP_CA_INST.fee_ci_sequence_number%TYPE)
465 IS
466 SELECT 'x'
467 FROM IGS_FI_F_TYP_CA_INST ftci
468 WHERE ftci.fee_cal_type = cp_fee_cal_type AND
469 ftci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
470 -- Modified the cursor
471 -- removed the 'DISTINCT' clause, added for update of clause
472 CURSOR c_student_todo IS
473 SELECT std.rowid,
474 std.person_id,
475 std.s_student_todo_type,
476 std.sequence_number,
477 std.todo_dt
478 FROM IGS_PE_STD_TODO std
479 WHERE std.s_student_todo_type = cst_fee_recalc AND
480 std.logical_delete_dt IS NULL
481 ORDER BY std.person_id;
482
483 -- Cursor to check if all REF records have been processed
484 CURSOR cur_chk_todo_ref(cp_n_person_id igs_pe_std_todo_ref.person_id%TYPE,
485 cp_v_todo_type igs_pe_std_todo_ref.s_student_todo_type%TYPE,
486 cp_n_seq_num igs_pe_std_todo_ref.sequence_number%TYPE) IS
487 SELECT 'x'
488 FROM igs_pe_std_todo_ref
489 WHERE person_id = cp_n_person_id
490 AND s_student_todo_type = cp_v_todo_type
491 AND sequence_number = cp_n_seq_num
492 AND logical_delete_dt IS NULL;
493
494 l_v_todo_ref_exists VARCHAR2(1);
495
496
497 PROCEDURE finp_prc_fa_ref_todo(
498 p_person_id IN hz_parties.party_id%TYPE,
499 p_sequence_number IN igs_pe_std_todo_ref.sequence_number%TYPE,
500 p_fee_cal_type IN igs_pe_std_todo_ref.cal_type%TYPE,
501 p_fee_ci_sequence_number IN igs_pe_std_todo_ref.ci_sequence_number%TYPE,
502 x_return_status OUT NOCOPY VARCHAR2,
503 x_msg_data OUT NOCOPY VARCHAR2,
504 p_v_load_cal_type IN igs_ca_inst_all.cal_type%TYPE,
505 p_n_load_ci_seq_num IN igs_ca_inst_all.sequence_number%TYPE
506 ) AS
507 /*************************************************************
508 Who When What
509
510 pathipat 30-Sep-2005 Bug 4570538: FEE ASSESSMENTS FROM TO DO ENTRIES
511 IGNORES PARM FEE ASSESSMENT PERIOD
512 Modified cursor c_get_todo_ref_csr to add join with
513 cal_type and sequence_number
514 **************************************************************/
515
516 rpt_load_cal_type igs_ca_inst.cal_type%TYPE;
517 rpt_load_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
518 l_message fnd_new_messages.message_name%TYPE;
519 l_message_name fnd_new_messages.message_name%TYPE;
520 l_n_waiver_amount NUMBER;
521 l_v_person_number hz_parties.party_number%TYPE;
522
523 CURSOR c_get_todo_ref_csr(cp_person_id igs_pe_std_todo_ref.person_id%TYPE,
524 cp_sequence_number igs_pe_std_todo_ref.sequence_number%TYPE,
525 cp_v_load_cal_type igs_ca_inst_all.cal_type%TYPE,
526 cp_n_load_ci_seq_num igs_ca_inst_all.sequence_number%TYPE ) IS
527 SELECT rowid,
528 person_id,
529 s_student_todo_type,
530 sequence_number,
531 reference_number,
532 cal_type,
533 ci_sequence_number,
534 course_cd,
535 unit_cd,
536 other_reference,
537 logical_delete_dt,
538 uoo_id
539 FROM igs_pe_std_todo_ref
540 WHERE person_id = cp_person_id
541 AND sequence_number = cp_sequence_number
542 AND s_student_todo_type = cst_fee_recalc
543 AND (cal_type = cp_v_load_cal_type OR cp_v_load_cal_type IS NULL)
544 AND (ci_sequence_number = cp_n_load_ci_seq_num OR cp_n_load_ci_seq_num IS NULL)
545 AND logical_delete_dt IS NULL -- Added this as part of Bug# 3230754
546 ORDER BY cal_type, ci_sequence_number;
547
548 BEGIN -- finp_prc_fa_ref_todo
549 -- Local procedure to reduce the possbility of Self Service version of fee
550 -- assessment from todo process igs_fi_ss_acct_payment.finp_calc_fees_todo
551 -- (IGSFI63B.pls) to experience a lock from the todo reference table.
552
553 -- Initialize return status
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555
556 -- Initialize the repeat variable
557 rpt_load_cal_type := 'NULL';
558 rpt_load_ci_sequence_number := 0;
559
560 FOR lp_todo_ref_rec IN c_get_todo_ref_csr(p_person_id,
561 p_sequence_number,
562 p_v_load_cal_type,
563 p_n_load_ci_seq_num) LOOP
564
565 BEGIN
566 IF (rpt_load_cal_type <> lp_todo_ref_rec.cal_type) OR
567 (rpt_load_ci_sequence_number <> lp_todo_ref_rec.ci_sequence_number) THEN
568
569 l_v_person_number := igs_fi_gen_008.get_party_number(p_person_id);
570 fnd_file.put_line(fnd_file.log,'');
571 fnd_file.put(fnd_file.log, l_v_person_number||': ');
572
573 -- get FCI from LCI
574 IF (p_v_load_cal_type IS NULL AND p_n_load_ci_seq_num IS NULL ) THEN
575 IF igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => lp_todo_ref_rec.cal_type,
576 p_ci_sequence_number => lp_todo_ref_rec.ci_sequence_number,
577 p_cal_category => 'LOAD',
578 p_ret_cal_type => l_fee_cal_type,
579 p_ret_ci_sequence_number => l_fee_ci_sequence_number,
580 p_message_name => l_message) = FALSE THEN
581 -- Code to add to stack has been added as part of 2734512
582 fnd_message.set_name('IGS',l_message);
583 igs_ge_msg_stack.add;
584 x_msg_data := l_message;
585 RAISE fnd_api.g_exc_error;
586 END IF;
587 ELSE
588 l_fee_cal_type := p_fee_cal_type;
589 l_fee_ci_sequence_number := p_fee_ci_sequence_number;
590 END IF;
591 -- Bug# 3230754, Removed check for Logical Delete Date as that is added in the cursor itself.
592 -- Call the Fee Assessment routine
593 fnd_msg_pub.initialize; -- Added as part of 2734512
594
595 IF (igs_fi_prc_fee_ass.finp_ins_enr_fee_ass(
596 p_effective_dt => SYSDATE,
597 p_person_id => p_person_id,
598 p_course_cd => NULL,
599 p_fee_category => NULL,
600 p_fee_cal_type => l_fee_cal_type,
601 p_fee_ci_sequence_num => l_fee_ci_sequence_number,
602 p_fee_type => NULL,
603 p_trace_on => 'N',
604 p_test_run => 'N',
605 p_creation_dt => v_creation_dt,
606 p_message_name => l_message_name,
607 p_process_mode => 'ACTUAL',
608 p_c_career => NULL,
609 p_d_gl_date => l_d_gl_date,
610 p_v_wav_calc_flag => 'N',
611 p_n_waiver_amount => l_n_waiver_amount) = FALSE) THEN
612 fnd_message.set_name ('IGS', l_message_name);
613 IF l_message_name = 'IGS_FI_NO_CENSUS_DT_SETUP' THEN
614 fnd_message.set_token('ALT_CD', igs_fi_prc_fee_ass.g_v_load_alt_code);
615 END IF;
616 igs_ge_msg_stack.add;
617 x_msg_data := l_message_name;
618 RAISE FND_API.G_EXC_ERROR;
619 ELSE
620 -- If call is success, action off child record
621 igs_pe_std_todo_ref_pkg.update_row(
622 x_rowid => lp_todo_ref_rec.rowid,
623 x_person_id => lp_todo_ref_rec.person_id ,
624 x_s_student_todo_type => lp_todo_ref_rec.s_student_todo_type ,
625 x_sequence_number => lp_todo_ref_rec.sequence_number ,
626 x_reference_number => lp_todo_ref_rec.reference_number,
627 x_cal_type => lp_todo_ref_rec.cal_type,
628 x_ci_sequence_number => lp_todo_ref_rec.ci_sequence_number,
629 x_course_cd => lp_todo_ref_rec.course_cd,
630 x_unit_cd => lp_todo_ref_rec.unit_cd,
631 x_other_reference => lp_todo_ref_rec.other_reference,
632 x_logical_delete_dt => SYSDATE,
633 x_mode => 'R',
634 x_uoo_id => lp_todo_ref_rec.uoo_id
635 );
636
637 fnd_file.put_line(fnd_file.log, fnd_message.get_string('IGS','IGS_FI_SUCC_TODO_REC'));
638
639 END IF; -- End if for core fee asssessment routine
640 END IF; -- End if for repeat load calendar record
641
642 -- Assignment of repeat indicator variables
643 rpt_load_cal_type := lp_todo_ref_rec.cal_type;
644 rpt_load_ci_sequence_number:= lp_todo_ref_rec.ci_sequence_number;
645
646 EXCEPTION
647 WHEN FND_API.G_EXC_ERROR THEN
648 ROLLBACK;
649 x_return_status := FND_API.G_RET_STS_ERROR;
650 RETURN;
651 WHEN OTHERS THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 RETURN;
654 END;
655 END LOOP; -- End of the loop for the child record
656
657 COMMIT;
658
659 EXCEPTION
660 WHEN OTHERS THEN
661 ROLLBACK;
662 l_msg_data := 'IGS_GE_UNHANDLED_EXCEPTION';
663 x_return_status := fnd_api.g_ret_sts_error;
664 fnd_file.put_line(fnd_file.log,substr(sqlerrm,1,300));
665
666 END finp_prc_fa_ref_todo; -- end of local procedure
667
668 BEGIN
669
670 l_rpt_person_id := 0;
671 -- Main Begin of the public procedure finp_prc_enr_fa_todo
672 -- Validate parameters
673 IF(p_fee_cal_type IS NOT NULL) THEN
674 FOR v_fee_cal_instance_rec IN c_fee_cal_instance(
675 p_fee_cal_type,
676 p_fee_ci_sequence_num) LOOP
677 v_record_found := TRUE;
678 END LOOP;
679 IF(v_record_found = FALSE) THEN
680 -- Replaced IGS_GE_INVALID_VALUE with IGS_FI_INVALID_PARAMETER, Bug# 2765239
681 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER' );
682 fnd_message.set_token('PARAMETER', igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'FEE_CAL_TYPE') );
683 fnd_file.put_line (fnd_file.log, fnd_message.get);
684 RETURN;
685 ELSE
686 v_record_found := FALSE;
687 END IF;
688 END IF;
689
690 -- When the Gl-Date parameter is not null then do the validation for checking the period status.
691 -- If the period status is not in Open or Future periods then error out
692 -- when the procedure returns a message name it is presumed as if an error has occurred
693 -- show the message name that is returned from the general procedure and error out.
694 IF p_d_gl_date IS NOT NULL THEN
695 l_d_gl_date := igs_ge_date.igsdate(p_d_gl_date);
696 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_d_gl_date,
697 p_v_closing_status => l_c_closing_status,
698 p_v_message_name => v_message_name);
699 IF v_message_name IS NOT NULL THEN
700 fnd_message.set_name('IGS', v_message_name);
701 fnd_file.put_line(fnd_file.log, fnd_message.get);
702 retcode :=2;
703 RETURN;
704 ELSIF l_c_closing_status NOT IN ('O','F') THEN
705 fnd_message.set_name('IGS', 'IGS_FI_INVALID_GL_DATE');
706 fnd_message.set_token('GL_DATE',l_d_gl_date);
707 fnd_file.put_line (fnd_file.log, fnd_message.get);
708 retcode :=2;
709 RETURN;
710 END IF;
711 ELSE
712 fnd_message.set_name('IGS', 'IGS_UC_NO_MANDATORY_PARAMS');
713 fnd_file.put_line (fnd_file.log, fnd_message.get);
714 retcode :=2;
715 RETURN;
716 END IF;
717
718 -- Obtain the value of the profile 'IGS: Charge tuition for Audited Student Attempt'
719 -- If this is not defined, then log error message
720 -- Added as part of Enh# 3117341, Audit Special Fees.
721 l_v_include_audit := fnd_profile.value('IGS_FI_CHARGE_AUDIT_FEES');
722 IF l_v_include_audit IS NULL THEN
723 fnd_message.set_name('IGS', 'IGS_FI_SP_FEE_NO_PROFILE');
724 fnd_file.put_line (fnd_file.log, fnd_message.get);
725 retcode :=2;
726 RETURN;
727 END IF;
728
729 IF (p_fee_cal IS NOT NULL) THEN
730 IF igs_fi_gen_001.finp_get_lfci_reln (p_cal_type => p_fee_cal_type,
731 p_ci_sequence_number => p_fee_ci_sequence_num,
732 p_cal_category => 'FEE',
733 p_ret_cal_type => l_v_load_cal_type,
734 p_ret_ci_sequence_number => l_n_load_ci_seq_num,
735 p_message_name => l_v_message) = FALSE THEN
736 fnd_message.set_name ('IGS', l_v_message);
737 igs_ge_msg_stack.add;
738 RAISE fnd_api.g_exc_error;
739 END IF;
740 ELSE
741 l_v_load_cal_type := NULL;
742 l_n_load_ci_seq_num := NULL;
743 END IF;
744
745 -- Call fee assessment routine from todo entries
746 FOR v_student_todo_rec IN c_student_todo LOOP
747
748 -- Must perform a commit or rollback before calling fee assessment routine
749 COMMIT;
750
751 -- Check to filter the same person id records in the loop
752 IF (l_rpt_person_id <> v_student_todo_rec.person_id) THEN
753 -- assignment of repeat variable rpt_person_id
754 l_rpt_person_id := v_student_todo_rec.person_id;
755 -- Call the new local procedure finpl_prc_fa_ref_todo
756 BEGIN
757 finp_prc_fa_ref_todo( p_person_id => v_student_todo_rec.person_id,
758 p_sequence_number => v_student_todo_rec.sequence_number,
759 p_fee_cal_type => p_fee_cal_type,
760 p_fee_ci_sequence_number => p_fee_ci_sequence_num,
761 x_return_status => l_return_status,
762 x_msg_data => l_msg_data,
763 p_v_load_cal_type => l_v_load_cal_type,
764 p_n_load_ci_seq_num => l_n_load_ci_seq_num);
765 EXCEPTION
766 WHEN OTHERS THEN
767 fnd_file.put_line(fnd_file.log,SUBSTR(SQLERRM,1,300));
768 END;
769
770 IF l_return_status = fnd_api.g_ret_sts_error THEN
771 -- Bug# 2734512, Added following message handling
772 igs_ge_msg_stack.conc_exception_hndl;
773 IF (l_msg_data IS NULL) THEN
774 fnd_msg_pub.count_and_get( p_count => l_n_msg_count,
775 p_data => l_v_msg_data);
776 IF l_n_msg_count = 1 THEN
777 fnd_message.set_encoded(l_v_msg_data);
778 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
779 ELSIF l_n_msg_count > 1 THEN
780 FOR l_var IN 1 .. l_n_msg_count LOOP
781 fnd_message.set_encoded(fnd_msg_pub.get);
782 fnd_file.put_line ( fnd_file.LOG, fnd_message.get );
783 END LOOP;
784 END IF;
785 END IF;
786 END IF;
787 END IF; -- End if for repeat person id check
788
789 -- Clear the todo entries for the current IGS_PE_PERSON by setting the logical
790 -- delete date
791 -- NOTE that multiple todo entries may exist for the same IGS_PE_PERSON, they are
792 -- all updated.
793
794 -- Check if all child TODO_REF records have been updated before updating the parent TODO record.
795 -- If any TODO_REF record is still not actioned off, then parent should not be updated.
796 OPEN cur_chk_todo_ref(v_student_todo_rec.person_id, 'FEE_RECALC', v_student_todo_rec.sequence_number);
797 FETCH cur_chk_todo_ref INTO l_v_todo_ref_exists;
798 IF cur_chk_todo_ref%NOTFOUND THEN
799 CLOSE cur_chk_todo_ref;
800 BEGIN
801 igs_pe_std_todo_pkg.update_row(
802 x_rowid => v_student_todo_rec.rowid,
803 x_person_id => v_student_todo_rec.person_id ,
804 x_s_student_todo_type => v_student_todo_rec.s_student_todo_type ,
805 x_sequence_number => v_student_todo_rec.sequence_number ,
806 x_todo_dt => v_student_todo_rec.todo_dt ,
807 x_logical_delete_dt => SYSDATE,
808 x_mode => 'R');
809 EXCEPTION
810 WHEN OTHERS THEN
811 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET_STRING ('IGS', 'IGS_GE_UNHANDLED_EXCEPTION'));
812 FND_FILE.Put_Line(FND_FILE.Log,substr(sqlerrm,1,300));
813 END;
814 -- assignment of repeat variable rpt_person_id
815 l_rpt_person_id := v_student_todo_rec.person_id;
816 ELSE
817 CLOSE cur_chk_todo_ref;
818 END IF;
819 END LOOP; -- End loop for header
820
821 COMMIT;
822
823 RETURN;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 RETCODE:=2;
828 FND_FILE.Put_Line(FND_FILE.Log,substr(sqlerrm,1,300));
829 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
830 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
831 END;
832 END finp_prc_enr_fa_todo;
833 --
834
835 --Removed the IN parameter p_predictive_ass_ind from the procedure finp_prc_enr_fee_ass.
836 PROCEDURE finp_prc_enr_fee_ass(
837 errbuf OUT NOCOPY VARCHAR2,
838 retcode OUT NOCOPY NUMBER,
839 p_person_id IN VARCHAR2,
840 p_person_grp_id IN VARCHAR2,
841 p_course_cd IN IGS_PS_COURSE.course_cd%TYPE ,
842 p_fee_cal IN VARCHAR2,
843 p_fee_category IN IGS_EN_STDNT_PS_ATT_ALL.FEE_CAT%TYPE,
844 p_fee_type IN IGS_FI_FEE_TYPE_ALL.FEE_TYPE%TYPE,
845 p_trace_on IN VARCHAR2,
846 p_test_run IN VARCHAR2,
847 p_org_id NUMBER,
848 p_process_mode IN VARCHAR2,
849 p_c_career IN igs_ps_ver.course_type%TYPE,
850 p_d_gl_date IN VARCHAR2,
851 p_comments IN VARCHAR2
852 ) AS
853
854 /* Who When What
855 abshriva 22-JUN-2006 Bug 5070074 Modified code handling condition when trace_on,test_run and gl_date is null
856 abshriva 04-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
857 abshriva 05-DEC-2005 Bug 4721566 Made the code modification so that log message corresponding to Test Run
858 parameter 'Yes' is displayed in log file
859 abshriva 05-DEC-2005 Bug:4701695 Made the code modification so that 'Fee calculation method' and 'term'message
860 is displayed only once in log file on execution of 'Process Fee Assessment'
861 shtatiko 03-JAN-2004 Enh# 3167098, Providing Persin Id or Person Group is made mandatory. Initial and Combined Processing
862 modes are made functionally obsolete.
863 uudayapr 15-dec-2003 Bug#3080983 Modified the Cursor c_fee_ass_debt to select data from IGS_FI_FEE_AS instead of
864 IGS_FI_FEE_ASS_DEBT_V .
865 shtatiko 18-NOV-2003 Enh# 3117341, Added check for profile 'IGS: Charge tuition for Audited Student Attempt'
866 pathipat 04-Nov-2003 Bug: 3151102 - Removed conditions before setting p_create_dt to v_create_dt
867 pathipat 17-Oct-2003 Bug: 3151102 - Added begin-end block for exception handling
868 pathipat 07-Oct-2003 Bug 3122652 - Logged messg IGF_AP_INVALID_QUERY if any error occurs
869 while obtaining the dynamic sql for the person id group, added validation for
870 person id group
871 pathipat 23-Sep-2003 Bug: 3151102 - Called finp_ins_enr_fee_ass in a begin-end block
872 with exception handling when called for a Person ID Group.
873 pathipat 09-Sep-2003 Bug 3122652: Replaced call to igf_ap_ss_pkg.get_pid() with
874 call to igs_pe_dynamic_persid_group.igs_get_dynamic_sql()
875 Increased length of l_dynamic_sql to 32767 from 2000
876 shtatiko 28-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option.
877 Implemeted Dynamic Person Group feature for group id parameter.
878 shtatiko 30-JAN-2003 Bug# 2765239, Replaced IGS_GE_INVALID_VALUE message with more meaningful messages
879 vchappid 06-Jan-2003 Bug# 2660155, Modified code to identify distinct persons when user don't provide
880 Person Id or Person ID Group as an input value to the request.
881 */
882 -- prameters process mode , init process prior calendar instance
883 -- and person id group have been added as a part
884 -- of the build for fee calc undertaken in July 2001.
885 -- Bug# 1851586
886
887 BEGIN -- finp_prc_enr_fee_ass
888 -- Module to control processing fee assessments
889
890 DECLARE
891 v_message_name VARCHAR2(30);
892 v_record_found BOOLEAN := FALSE;
893 v_create_dt DATE;
894 l_c_closing_status igs_fi_gl_periods_v.closing_status%TYPE;
895 l_n_person_id hz_parties.party_id%TYPE;
896 l_v_fee_cal_type igs_ca_inst_all.cal_type%TYPE;
897 l_n_fee_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
898 l_n_person_grp_id igs_pe_prsid_grp_mem_v.group_id%TYPE;
899 l_d_gl_date DATE;
900 l_v_load_cal_type igs_ca_inst_all.cal_type%TYPE;
901 l_n_load_ci_sequence_number igs_ca_inst_all.sequence_number%TYPE;
902 l_b_fci_lci BOOLEAN := FALSE;
903 l_org_id VARCHAR2(15);
904
905 CURSOR c_fee_cal_instance (
906 cp_fee_type IGS_FI_F_TYP_CA_INST.FEE_TYPE%TYPE,
907 cp_fee_cal_type IGS_FI_F_TYP_CA_INST.fee_cal_type%TYPE,
908 cp_fee_ci_sequence_number
909 IGS_FI_F_TYP_CA_INST.fee_ci_sequence_number%TYPE) IS
910 SELECT 'x'
911 FROM IGS_FI_F_TYP_CA_INST ftci
912 WHERE ftci.FEE_TYPE = cp_fee_type AND
913 ftci.fee_cal_type = cp_fee_cal_type AND
914 ftci.fee_ci_sequence_number = cp_fee_ci_sequence_number;
915
916 -- selecting the members of the given person id group such that the membership has not ended.
917 -- Removed l_c_grp_members as Person Groups are implemented as Dynamic Groups as per Enh# 2831569.
918
919 -- Record of person_id to get the values of
920 TYPE person_grp_rec_type IS RECORD ( p_n_person_id igs_pe_prsid_grp_mem.person_id%TYPE );
921 rec_person_grp person_grp_rec_type;
922
923 -- REF CURSOR for dynamic person group.
924 TYPE person_grp_ref_cur_type IS REF CURSOR;
925 c_ref_person_grp person_grp_ref_cur_type;
926 l_dynamic_sql VARCHAR2(32767);
927 l_v_status VARCHAR2(10);
928
929 l_v_message_name fnd_new_messages.message_name%TYPE;
930 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
931
932 l_v_person_number igs_fi_parties_v.person_number%TYPE := NULL;
933
934 CURSOR cur_pers_grp(cp_n_pers_grp_id igs_pe_persid_group_all.group_id%TYPE) IS
935 SELECT 'x'
936 FROM igs_pe_persid_group_all
937 WHERE group_id = cp_n_pers_grp_id
938 AND TRUNC(create_dt) <= g_d_sysdate
939 AND NVL(closed_ind, g_v_ind_no) = g_v_ind_no;
940 l_c_var VARCHAR2(10) := NULL;
941
942 l_v_include_audit VARCHAR2(1);
943 l_v_wav_calc_flag VARCHAR2(1);
944 l_n_waiver_amount NUMBER;
945
946 CURSOR cur_wav_calc_flag ( cp_v_fee_cat igs_fi_f_cat_fee_lbl_all.fee_cat%TYPE,
947 cp_v_fee_cal_type igs_fi_f_cat_fee_lbl_all.fee_cal_type%TYPE,
948 cp_n_fee_ci_seq_num igs_fi_f_cat_fee_lbl_all.fee_ci_sequence_number%TYPE,
949 cp_v_fee_type igs_fi_f_cat_fee_lbl_all.fee_type%TYPE) IS
950 SELECT waiver_calc_flag
951 FROM igs_fi_f_cat_fee_lbl_all
952 WHERE fee_cat = cp_v_fee_cat OR cp_v_fee_cat IS NULL
953 AND fee_cal_type = cp_v_fee_cal_type
954 AND fee_ci_sequence_number = cp_n_fee_ci_seq_num
955 AND fee_type = cp_v_fee_type;
956
957 CURSOR cur_fee_calc_mthd
958 IS
959 SELECT fee_calc_mthd_code
960 FROM igs_fi_control;
961 l_fee_calc_mthd_code igs_fi_control.fee_calc_mthd_code%TYPE;
962
963 BEGIN
964 BEGIN
965 l_org_id := NULL;
966 igs_ge_gen_003.set_org_id(l_org_id);
967 EXCEPTION
968 WHEN OTHERS THEN
969 fnd_file.put_line (fnd_file.log, fnd_message.get);
970 retcode :=2;
971 RETURN;
972 END;
973 retcode := 0;
974 l_n_person_id := TO_NUMBER(p_person_id);
975 l_v_fee_cal_type := RTRIM(SUBSTR(p_fee_cal,102,10));
976 l_n_fee_ci_sequence_number := TO_NUMBER(LTRIM(SUBSTR(p_fee_cal,113,8)));
977 l_n_person_grp_id := TO_NUMBER(p_person_grp_id);
978 l_d_gl_date := igs_ge_date.igsdate(p_canonical_date => p_d_gl_date);
979
980
981 -- Get the value of "Manage Accounts" System Option value.
982 -- If this value is NULL then this process cannot run. Added as part of Enh# 2831569.
983 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
984 p_v_message_name => l_v_message_name );
985 IF l_v_manage_accounts IS NULL THEN
986 fnd_message.set_name ( 'IGS', l_v_message_name );
987 igs_ge_msg_stack.ADD;
988 app_exception.raise_exception (NULL, NULL, fnd_message.get);
989 END IF;
990
991 -- Validate parameters
992 -- Process can take only one parameter between Person Number and Person Group ID. Added as part of Enh# 2831569.
993 IF ( (l_n_person_id IS NOT NULL) AND (l_n_person_grp_id IS NOT NULL) ) THEN
994 fnd_message.set_name ( 'IGS', 'IGS_FI_PRS_PRSIDGRP_NULL') ;
995 igs_ge_msg_stack.ADD;
996 app_exception.raise_exception(null, null, fnd_message.get);
997 END IF;
998
999 -- Enh# 3167098, Either of the two parameters must be specified.
1000 IF l_n_person_id IS NULL AND
1001 l_n_person_grp_id IS NULL THEN
1002 fnd_message.set_name ( 'IGS', 'IGS_FI_PRS_PRSIDGRP_NULL') ;
1003 igs_ge_msg_stack.ADD;
1004 app_exception.raise_exception(null, null, fnd_message.get);
1005 END IF;
1006
1007 -- Validate the person id group
1008 IF l_n_person_grp_id IS NOT NULL THEN
1009 OPEN cur_pers_grp(l_n_person_grp_id);
1010 FETCH cur_pers_grp INTO l_c_var;
1011 IF cur_pers_grp%NOTFOUND THEN
1012 CLOSE cur_pers_grp;
1013 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1014 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP'));
1015 igs_ge_msg_stack.ADD;
1016 app_exception.raise_exception(null, null, fnd_message.get);
1017 END IF;
1018 CLOSE cur_pers_grp;
1019 END IF;
1020
1021 IF(p_trace_on IS NULL ) THEN
1022 fnd_file.new_line(fnd_file.log);
1023 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER');
1024 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'TRACE_ON'));
1025 fnd_file.put_line (fnd_file.log, fnd_message.get);
1026 fnd_file.new_line(fnd_file.log);
1027 retcode:=2;
1028 RETURN;
1029 END IF;
1030
1031 IF( p_test_run IS NULL ) THEN
1032 fnd_file.new_line(fnd_file.log);
1033 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER');
1034 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'TEST_RUN'));
1035 fnd_file.put_line (fnd_file.log, fnd_message.get);
1036 retcode:=2;
1037 RETURN;
1038 END IF;
1039
1040 IF( l_d_gl_date IS NULL ) THEN
1041 fnd_file.new_line(fnd_file.log);
1042 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER');
1043 fnd_message.set_token('PARAMETER', igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'GL_DATE'));
1044 fnd_file.put_line (fnd_file.log, fnd_message.get);
1045 retcode:=2;
1046 RETURN;
1047 END IF;
1048
1049 -- ensuring that fee calendar instance is not null as a change implemented in
1050 -- fee calc build july-2001 (bug: 1851586)
1051 IF l_v_fee_cal_type IS NULL OR l_n_fee_ci_sequence_number IS NULL THEN
1052 Fnd_Message.Set_Name ('IGS', 'IGS_FI_PARAMETER_NULL');
1053 IGS_GE_MSG_STACK.ADD;
1054 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
1055 RETURN;
1056 END IF;
1057
1058 -- Added as part of Tuition Waivers.
1059 IF (p_fee_type IS NOT NULL) THEN
1060 OPEN cur_wav_calc_flag( p_fee_category, l_v_fee_cal_type, l_n_fee_ci_sequence_number, p_fee_type );
1061 FETCH cur_wav_calc_flag INTO l_v_wav_calc_flag;
1062 CLOSE cur_wav_calc_flag;
1063 IF (l_v_wav_calc_flag = 'Y') THEN
1064 fnd_message.set_name('IGS', 'IGS_FI_WAV_FEE_TYPE');
1065 igs_ge_msg_stack.add;
1066 app_exception.raise_exception(Null, Null, fnd_message.get);
1067 RETURN;
1068 END IF;
1069 END IF;
1070
1071 -- When the Gl-Date parameter is not null then do the validation for checking the period status.
1072 -- If the period status is not in Open or Future periods then error out
1073 -- when the procedure returns a message name it is presumed as if an error has occurred
1074 -- show the message name that is returned from the general procedure and error out.
1075 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_d_gl_date,
1076 p_v_closing_status => l_c_closing_status,
1077 p_v_message_name => v_message_name);
1078 IF v_message_name IS NOT NULL THEN
1079 FND_MESSAGE.SET_NAME('IGS', v_message_name);
1080 IGS_GE_MSG_STACK.ADD;
1081 APP_EXCEPTION.RAISE_EXCEPTION;
1082 ELSIF l_c_closing_status NOT IN ('O','F') THEN
1083 FND_MESSAGE.SET_NAME('IGS', 'IGS_FI_INVALID_GL_DATE');
1084 FND_MESSAGE.SET_TOKEN('GL_DATE',l_d_gl_date);
1085 IGS_GE_MSG_STACK.ADD;
1086 APP_EXCEPTION.RAISE_EXCEPTION;
1087 END IF;
1088
1089 -- validate fee assessment period
1090 IF (l_v_fee_cal_type IS NOT NULL AND
1091 p_fee_type IS NOT NULL) THEN
1092 FOR v_fee_cal_instance_rec IN c_fee_cal_instance(
1093 p_fee_type,
1094 l_v_fee_cal_type,
1095 l_n_fee_ci_sequence_number) LOOP
1096 v_record_found := TRUE;
1097 END LOOP;
1098 IF(v_record_found = FALSE) THEN
1099 Fnd_Message.Set_Name ('IGS', 'IGS_FI_NO_FEE_CAL_INS');
1100 IGS_GE_MSG_STACK.ADD;
1101 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
1102 RETURN;
1103 ELSE
1104 v_record_found := FALSE;
1105 END IF;
1106 END IF;
1107
1108 --Added the value 'PREDICTIVE' in the valid list of values the parameter
1109 --p_process_mode should have.
1110
1111 -- validating for the new parameters added as a part of the fee clac build in july-2001
1112 -- validate that p_process_mode is not null and have only one of the defined values.
1113 -- fee calc build july-2001 (bug: 1851586)
1114
1115 -- Enh# 3167098, Removed INITIAL and COMBINED modes.
1116 IF NVL(p_process_mode, 'NULL') NOT IN ('ACTUAL', 'PREDICTIVE') THEN
1117 fnd_message.set_name('IGS', 'IGS_FI_INVALID_PARAMETER' );
1118 fnd_message.set_token('PARAMETER', igs_ge_gen_004.genp_get_lookup ( 'IGS_FI_LOCKBOX', 'PROCESS_MODE' ) );
1119 IGS_GE_MSG_STACK.ADD;
1120 App_Exception.Raise_Exception(Null, Null, fnd_message.get);
1121 RETURN;
1122 END IF;
1123
1124 -- Obtain the value of the profile 'IGS: Charge tuition for Audited Student Attempt'
1125 -- If this is not defined, then log error message
1126 -- Added as part of Enh# 3117341, Audit Special Fees.
1127 l_v_include_audit := fnd_profile.value('IGS_FI_CHARGE_AUDIT_FEES');
1128 IF l_v_include_audit IS NULL THEN
1129 fnd_message.set_name('IGS', 'IGS_FI_SP_FEE_NO_PROFILE');
1130 igs_ge_msg_stack.ADD;
1131 app_exception.raise_exception(null, null, fnd_message.get);
1132 END IF;
1133
1134 fnd_file.new_line(fnd_file.log);
1135 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'RUN_COMMENT') || ': ' || p_comments);
1136 fnd_message.set_name('IGS', 'IGS_FI_PERSON_NUM');
1137 IF l_n_person_id IS NOT NULL THEN
1138 fnd_message.set_token('PERSON_NUM',igs_fi_gen_008.get_party_number(l_n_person_id));
1139 ELSE
1140 fnd_message.set_token('PERSON_NUM',l_n_person_id);
1141 END IF;
1142 fnd_file.put_line (fnd_file.log, fnd_message.get);
1143 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_ACCT_ENTITIES', 'PS') || ': ' || p_course_cd);
1144 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_ASS_PERIOD') || ': ' || SUBSTR(p_fee_cal,1,40));
1145 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_CAT') || ': ' || p_fee_category);
1146 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_TYPE') || ': ' || p_fee_type);
1147 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'TRACE_ON') || ': ' || igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_trace_on));
1148 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'TEST_RUN') || ': ' || igs_fi_gen_gl.get_lkp_meaning('YES_NO', p_test_run));
1149 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'PROCESS_MODE') || ': ' || igs_fi_gen_gl.get_lkp_meaning('IGS_FI_PROCESS_MODE', p_process_mode));
1150 fnd_message.set_name('IGS', 'IGS_FI_PERSON_GROUP');
1151 IF l_n_person_grp_id IS NOT NULL THEN
1152 fnd_message.set_token('PERSON_GRP',igs_fi_gen_005.finp_get_prsid_grp_code(l_n_person_grp_id));
1153 ELSE
1154 fnd_message.set_token('PERSON_GRP',l_n_person_grp_id);
1155 END IF;
1156 fnd_file.put_line (fnd_file.log, fnd_message.get);
1157 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CAREER') || ': ' || p_c_career);
1158 fnd_message.set_name('IGS', 'IGS_FI_GL_DATE');
1159 fnd_message.set_token('GL_DATE', TO_CHAR(l_d_gl_date, 'DD-MON-YYYY'));
1160 fnd_file.put_line (fnd_file.log, fnd_message.get);
1161
1162 -- case in which the person id is not provided but the person group id is provided.
1163 IF l_n_person_grp_id is NOT NULL AND l_n_person_id is NULL THEN
1164 -- Get the select query for REF CURSOR by calling igs_pe_dynamic_persid_group.igs_get_dynamic_sql
1165 l_dynamic_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(l_n_person_grp_id,l_v_status );
1166 IF l_v_status <> 'S' THEN
1167 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1168 igs_ge_msg_stack.add;
1169 app_exception.raise_exception(NULL,NULL,fnd_message.get);
1170 END IF;
1171
1172
1173 IF (p_trace_on = 'Y') THEN
1174 fnd_file.put_line( fnd_file.log, RPAD('=', '79', '=') );
1175 OPEN cur_fee_calc_mthd;
1176 FETCH cur_fee_calc_mthd INTO l_fee_calc_mthd_code;
1177 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_CALC_MTHD') || ': ' || igs_fi_gen_gl.get_lkp_meaning('IGS_FI_FEE_CALC_MTHD',l_fee_calc_mthd_code));
1178 CLOSE cur_fee_calc_mthd;
1179 l_b_fci_lci := igs_fi_gen_001.finp_get_lfci_reln( l_v_fee_cal_type,
1180 l_n_fee_ci_sequence_number,
1181 'FEE',
1182 l_v_load_cal_type,
1183 l_n_load_ci_sequence_number,
1184 l_v_message_name);
1185 IF l_b_fci_lci=TRUE THEN
1186 fnd_file.put_line (fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_AS_ALL_TERM', 'TERM') || ': ' || igs_ca_gen_001.calp_get_alt_cd(l_v_load_cal_type,l_n_load_ci_sequence_number));
1187 END IF;
1188 fnd_file.put_line( fnd_file.log, RPAD('=', '79', '=') );
1189 END IF;
1190
1191 -- Open the REF CURSOR for above derived SQL statement ( l_dynamic_sql )
1192 OPEN c_ref_person_grp FOR l_dynamic_sql;
1193 -- looping across all the valid person ids in the group.
1194 LOOP
1195 FETCH c_ref_person_grp INTO rec_person_grp;
1196 EXIT WHEN c_ref_person_grp%NOTFOUND;
1197
1198 -- Call fee assessment routine
1199 BEGIN
1200 -- Removed the parameter p_predictive_ass_ind from call to
1201 -- igs_fi_prc_fee_ass.finp_ins_enr_fee_ass
1202 IF(igs_fi_prc_fee_ass.finp_ins_enr_fee_ass( SYSDATE,
1203 rec_person_grp.p_n_person_id,
1204 p_course_cd,
1205 p_fee_category,
1206 l_v_fee_cal_type,
1207 l_n_fee_ci_sequence_number,
1208 p_fee_type,
1209 p_trace_on,
1210 p_test_run,
1211 v_create_dt,
1212 v_message_name,
1213 p_process_mode,
1214 p_c_career,
1215 l_d_gl_date,
1216 'N',
1217 l_n_waiver_amount) = FALSE) THEN
1218 Fnd_Message.Set_Name ('IGS', v_message_name);
1219 IF v_message_name = 'IGS_FI_NO_CENSUS_DT_SETUP' THEN
1220 fnd_message.set_token('ALT_CD', igs_fi_prc_fee_ass.g_v_load_alt_code);
1221 END IF;
1222 IGS_GE_MSG_STACK.ADD;
1223 App_Exception.Raise_Exception(Null, Null, fnd_message.get );
1224 END IF;
1225 EXCEPTION
1226 WHEN OTHERS THEN
1227 -- If any exception is raised for a person, log person_number
1228 -- and continue processing for next person
1229 retcode:=1;
1230 l_v_person_number := igs_fi_gen_008.get_party_number(rec_person_grp.p_n_person_id);
1231 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
1232 fnd_message.set_token('PERSON_NUM',l_v_person_number);
1233 igs_ge_msg_stack.add;
1234 END;
1235
1236 END LOOP;
1237
1238 ELSIF l_n_person_grp_id is NULL AND l_n_person_id is NOT NULL THEN
1239 -- case in which the person id is might be provided or be null
1240
1241 -- Call fee assessment routine
1242 IF(igs_fi_prc_fee_ass.finp_ins_enr_fee_ass(
1243 SYSDATE,
1244 l_n_person_id,
1245 p_course_cd,
1246 p_fee_category,
1247 l_v_fee_cal_type,
1248 l_n_fee_ci_sequence_number,
1249 p_fee_type,
1250 p_trace_on,
1251 p_test_run,
1252 v_create_dt,
1253 v_message_name,
1254 p_process_mode,
1255 p_c_career,
1256 l_d_gl_date,
1257 'N',
1258 l_n_waiver_amount) = FALSE) THEN
1259 Fnd_Message.Set_Name ('IGS', v_message_name);
1260 IF v_message_name = 'IGS_FI_NO_CENSUS_DT_SETUP' THEN
1261 fnd_message.set_token('ALT_CD', igs_fi_prc_fee_ass.g_v_load_alt_code);
1262 END IF;
1263 IGS_GE_MSG_STACK.ADD;
1264 App_Exception.Raise_Exception(Null, Null, fnd_message.get );
1265 END IF;
1266
1267 END IF;
1268 IF p_test_run = 'Y' THEN
1269 fnd_message.set_name('IGS',
1270 'IGS_FI_PRC_TEST_RUN');
1271 fnd_file.put_line(fnd_file.log,
1272 fnd_message.get);
1273 END IF;
1274
1275 RETURN;
1276
1277 END;
1278 EXCEPTION
1279 WHEN OTHERS THEN
1280 retcode:=2;
1281 errbuf:=fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1282 igs_ge_msg_stack.conc_exception_hndl;
1283 END finp_prc_enr_fee_ass;
1284
1285 --
1286 PROCEDURE finp_prc_hecs_pymnt_optn(
1287 errbuf out NOCOPY varchar2,
1288 retcode out NOCOPY NUMBER,
1289 p_effective_dt_C IN OUT NOCOPY VARCHAR2 ,
1290 P_fee_assessment_period IN VARCHAR2,
1291 p_person_id IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
1292 p_fee_cat IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
1293 p_course_cd IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
1294 p_deferred_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE,
1295 p_upfront_payment_option IGS_FI_HECS_PAY_OPTN.hecs_payment_option%TYPE,
1296 p_org_id NUMBER
1297 ) AS
1298 p_effective_dt DATE;
1299 p_fee_cal_type igs_ca_inst.cal_type%TYPE ;
1300 p_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
1301 BEGIN
1302 -- finp_prc_hecs_pymnt_optn
1303 -- Routine to control processing student's IGS_PS_COURSE attempt HECS payment option
1304 -- on the basis of their assessed liability and any up front payments made
1305 --Block for Parameter Validation/Splitting of Parameters
1306
1307
1308 --As part of bug fix of bug #2318488 the following code has been added
1309 retcode:=0;
1310 FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_OBSOLETE_JOB');
1311 FND_FILE.PUT_LINE (FND_FILE.LOG, FND_MESSAGE.GET);
1312
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 RETCODE:=2;
1316 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1317 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1318 END finp_prc_hecs_pymnt_optn;
1319 --
1320 PROCEDURE finp_prc_penalties(
1321 errbuf out NOCOPY varchar2,
1322 retcode out NOCOPY number,
1323 p_effective_dt_C IN VARCHAR2,
1324 P_fee_assessment_period IN VARCHAR2,
1325 p_person_id IN IGS_EN_STDNT_PS_ATT_ALL.person_id%TYPE ,
1326 p_fee_type IN IGS_FI_FEE_TYPE_ALL.fee_type%TYPE ,
1327 p_fee_cat IN IGS_EN_STDNT_PS_ATT_ALL.fee_cat%TYPE ,
1328 p_course_cd IN IGS_EN_STDNT_PS_ATT_ALL.course_cd%TYPE ,
1329 p_pending_fee_encmb_status IN VARCHAR2,
1330 p_n_authorising_person_id IN NUMBER,
1331 p_org_id NUMBER
1332 ) AS
1333 BEGIN
1334
1335 retcode:=0;
1336 -- As per SFCR005, this concurrent program is obsolete and if the user
1337 -- tries to run this program then an error message should be logged into the log
1338 -- file that the concurrent program is obsolete and should not be run.
1339 FND_MESSAGE.Set_Name('IGS',
1340 'IGS_GE_OBSOLETE_JOB');
1341 FND_FILE.Put_Line(FND_FILE.Log,
1342 FND_MESSAGE.Get);
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 RETCODE:=2;
1346 ERRBUF:=FND_MESSAGE.GET_STRING('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1347 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1348 END finp_prc_penalties;
1349 --
1350 PROCEDURE finp_prc_sca_unconf(
1351 p_person_id IN NUMBER ,
1352 p_course_cd IN VARCHAR2 ,
1353 p_course_attempt_status IN VARCHAR2 ,
1354 p_fee_cat IN VARCHAR2 ,
1355 p_log_creation_dt IN DATE ,
1356 p_key IN VARCHAR2 ,
1357 p_admission_appl_number IN NUMBER ,
1358 p_nominated_course_cd IN VARCHAR2 ,
1359 p_acai_sequence_number IN NUMBER ,
1360 p_fee_ass_log_creation_dt IN OUT NOCOPY DATE ,
1361 p_delete_sca_ind OUT NOCOPY VARCHAR2 )
1362 AS
1363 /*************************************************************
1364 Created By :
1365 Date Created By :
1366 Purpose :
1367 Know limitations, enhancements or remarks
1368 Change History
1369 Who When What
1370 shtatiko 24-DEC-2003 Enh# 3167098, Modified finpl_prc_this_crs_liable and finpl_prc_another_crs_liable
1371 uudayapr 15-dec-2003 Bug#3080983 Modified the cursor c_fasdv to fetch Data from IGS_FI_FEE_AS instead of IGS_FI_FEE_ASS_DEBT_V.
1372 vchappid 24-May-2002 Bug#2228743, in the local procedure finpl_prc_reverse_fee_assess of finp_prc_sca_unconf
1373 fee assessment call has been changed to pass course cd in case of program approach, course type
1374 incase of the career approach and will pass null in the case of primary_career approach
1375 SCHODAVA 28-NOV-2001 Enh # 2122257
1376 (SFCR015 : Change In Fee Category)
1377 Modified local procedures FINPL_PRC_THIS_CRS_LIABLE
1378 and FINPL_PRC_ANOTHER_CRS_LIABLE
1379 (reverse chronological order - newest change first)
1380 ***************************************************************/
1381
1382 BEGIN -- finp_prc_sca_unconf
1383 -- Process finance details for unconfirmed student IGS_PS_COURSE attempts. This
1384 -- routine is called from ADMP_DEL_SCA_UNCONF when deleting unconfirmed
1385 -- student IGS_PS_COURSE attempts.
1386 -- IGS_GE_NOTE: The call to IGS_FI_PRC_FEE_ASS.finp_ins_enr_fee performs a commit,
1387 -- this means that all outstanding transactions will be committed.
1388 DECLARE
1389 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1390 e_resource_busy EXCEPTION;
1391 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
1392 v_delete_sca_ind VARCHAR2(1) := 'Y';
1393 v_log_entry_ind VARCHAR2(1) := 'N';
1394 CURSOR c_fas IS
1395 SELECT DISTINCT fas.course_cd
1396 FROM IGS_FI_FEE_AS fas
1397 WHERE fas.person_id = p_person_id;
1398 --Modifed to fetch data from IGS_FI_FEE_AS insted of IGS_FI_FEE_ASS_DEBT_V.
1399 CURSOR c_fasdv (cp_fee_ass_course_cd IGS_FI_FEE_AS.course_cd%TYPE) IS
1400 SELECT fasdv.course_cd,
1401 fasdv.fee_cal_type,
1402 fasdv.fee_ci_sequence_number,
1403 fasdv.FEE_TYPE,
1404 fasdv.FEE_CAT,
1405 SUM(fasdv.transaction_amount) assessment_amount
1406 FROM IGS_FI_FEE_AS fasdv
1407 WHERE fasdv.person_id = p_person_id
1408 AND (fasdv.course_cd = cp_fee_ass_course_cd
1409 OR
1410 (fasdv.course_cd IS NULL AND cp_fee_ass_course_cd IS NULL)
1411 )
1412 AND fasdv.logical_delete_dt IS NULL
1413 GROUP BY fasdv.course_cd,
1414 fasdv.fee_cal_type,
1415 fasdv.fee_ci_sequence_number,
1416 fasdv.FEE_TYPE,
1417 fasdv.FEE_CAT ;
1418
1419 PROCEDURE finpl_prc_ins_log_entry (
1420 p_message_name VARCHAR2,
1421 p_sca_deleted_ind VARCHAR2)
1422 AS
1423 BEGIN -- finpl_prc_ins_log_entry
1424 -- create a log entry
1425 DECLARE
1426 cst_del_un_sca CONSTANT VARCHAR2(10) := 'DEL-UN-SCA';
1427 BEGIN
1428 IGS_GE_GEN_003.genp_ins_log_entry (
1429 cst_del_un_sca,
1430 p_log_creation_dt,
1431 p_sca_deleted_ind || '|' || p_key,
1432 p_message_name,
1433 NULL);
1434 v_log_entry_ind := 'Y';
1435 END;
1436 EXCEPTION
1437 WHEN OTHERS THEN
1438 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1439 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINP_PRC_INS_LOG_ENTRY');
1440 IGS_GE_MSG_STACK.ADD;
1441 App_Exception.Raise_Exception;
1442 END finpl_prc_ins_log_entry;
1443 PROCEDURE finpl_prc_end_fee_contract(
1444 p_person_id IGS_FI_FEE_AS_RT.person_id%TYPE,
1445 p_course_cd IGS_FI_FEE_AS_RT.course_cd%TYPE)
1446 AS
1447 BEGIN -- finpl_prc_end_fee_contract
1448 -- End the fee contract
1449 DECLARE
1450 CURSOR c_cfar IS
1451 SELECT
1452 cfar.ROWID,
1453 cfar.PERSON_ID,
1454 cfar.COURSE_CD,
1455 cfar.FEE_TYPE,
1456 cfar.START_DT,
1457 cfar.END_DT,
1458 cfar.LOCATION_CD,
1459 cfar.ATTENDANCE_TYPE,
1460 cfar.ATTENDANCE_MODE,
1461 cfar.CHG_RATE,
1462 cfar.LOWER_NRML_RATE_OVRD_IND
1463 FROM IGS_FI_FEE_AS_RT cfar
1464 WHERE cfar.person_id = p_person_id AND
1465 cfar.course_cd = p_course_cd
1466 FOR UPDATE OF cfar.end_dt NOWAIT;
1467 BEGIN
1468 FOR v_cfar_rec IN c_cfar LOOP
1469 IGS_FI_FEE_AS_RT_PKG.UPDATE_ROW(
1470 X_ROWID => v_cfar_rec.ROWID ,
1471 X_PERSON_ID => v_cfar_rec.PERSON_ID ,
1472 X_COURSE_CD => v_cfar_rec.COURSE_CD,
1473 X_FEE_TYPE => v_cfar_rec.FEE_TYPE ,
1474 X_START_DT => v_cfar_rec.START_DT ,
1475 X_END_DT => v_cfar_rec.start_dt,
1476 X_LOCATION_CD => v_cfar_rec.LOCATION_CD,
1477 X_ATTENDANCE_TYPE => v_cfar_rec.ATTENDANCE_TYPE,
1478 X_ATTENDANCE_MODE => v_cfar_rec.ATTENDANCE_MODE,
1479 X_CHG_RATE => v_cfar_rec.CHG_RATE ,
1480 X_LOWER_NRML_RATE_OVRD_IND => v_cfar_rec.LOWER_NRML_RATE_OVRD_IND ,
1481 X_MODE => 'R');
1482 END LOOP;
1483 EXCEPTION
1484 WHEN e_resource_busy THEN
1485 IF c_cfar%ISOPEN THEN
1486 CLOSE c_cfar;
1487 END IF;
1488 finpl_prc_ins_log_entry(
1489 4722,
1490 'N');
1491 END;
1492 EXCEPTION
1493 WHEN OTHERS THEN
1494 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1495 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINP_PRC_END_FEE_CONTRACT');
1496 IGS_GE_MSG_STACK.ADD;
1497 App_Exception.Raise_Exception;
1498 END finpl_prc_end_fee_contract;
1499
1500 PROCEDURE finpl_prc_reverse_fee_assess (
1501 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1502 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1503 p_fee_cal_type IGS_FI_FEE_AS.fee_cal_type%TYPE,
1504 p_fee_ci_sequence_number IGS_FI_FEE_AS.fee_ci_sequence_number%TYPE,
1505 p_fee_type IGS_FI_FEE_AS.FEE_TYPE%TYPE,
1506 p_fee_cat IGS_EN_STDNT_PS_ATT.FEE_CAT%TYPE)
1507 AS
1508
1509 CURSOR cur_fee_calc_mthd
1510 IS
1511 SELECT fee_calc_mthd_code
1512 FROM igs_fi_control;
1513 l_fee_calc_mthd_code igs_fi_control.fee_calc_mthd_code%TYPE;
1514
1515 CURSOR cur_career (cp_course_cd IN igs_ps_ver.course_cd%type)
1516 IS
1517 SELECT course_type
1518 FROM igs_ps_ver
1519 WHERE course_cd = cp_course_cd;
1520 l_course_type igs_ps_ver.course_type%TYPE;
1521 l_course_cd igs_ps_ver.course_cd%TYPE;
1522 l_n_waiver_amount NUMBER;
1523
1524
1525 BEGIN -- finpl_prc_reverse_fee_assess
1526 -- Reverse the fee assessment
1527 DECLARE
1528 v_message_name VARCHAR2(30);
1529 BEGIN
1530 OPEN cur_fee_calc_mthd;
1531 FETCH cur_fee_calc_mthd INTO l_fee_calc_mthd_code;
1532 CLOSE cur_fee_calc_mthd;
1533
1534 -- if the calculation method is career then pass the career/ course type, pass null to course cd
1535 IF (l_fee_calc_mthd_code = 'CAREER') THEN
1536 OPEN cur_career(p_course_cd);
1537 FETCH cur_career INTO l_course_type;
1538 CLOSE cur_career;
1539 l_course_cd := NULL;
1540
1541 -- if the calculation method is program then pass the course cd and pass null to the career parameter
1542 ELSIF (l_fee_calc_mthd_code = 'PROGRAM') THEN
1543 l_course_type := NULL;
1544 l_course_cd := p_course_cd;
1545 -- if the calculation method is primary career then pass the career/ course type and course cd as null
1546 ELSIF (l_fee_calc_mthd_code = 'PRIMARY_CAREER') THEN
1547 l_course_type := NULL;
1548 l_course_cd := NULL;
1549 END IF;
1550
1551
1552
1553 -- IGS_GE_NOTE: Must perform a commit before calling
1554 -- IGS_FI_PRC_FEE_ASS.finp_ins_enr_fee_ass because the first statement of
1555 -- IGS_FI_PRC_FEE_ASS.finp_ins_enr_fee_ass is to alter the rollback segment,
1556 -- which requires all the outstanding transactions to be committed or rolled back
1557 COMMIT;
1558
1559 -- Enh# 3167098, Removed call to igs_en_gen_002.enrp_get_acad_comm which was used to derive the commencement date.
1560 -- This is removed as SYSDATE is passed to effective date.
1561
1562 -- Removed the parameter p_predictive_ass_ind from call to
1563 -- igs_fi_prc_fee_ass.finp_ins_enr_fee_ass
1564 IF NOT igs_fi_prc_fee_ass.finp_ins_enr_fee_ass (
1565 TRUNC(SYSDATE), -- effective date
1566 p_person_id,
1567 l_course_cd,
1568 p_fee_cat,
1569 p_fee_cal_type,
1570 p_fee_ci_sequence_number,
1571 p_fee_type,
1572 'N', -- trace on
1573 'N', -- test run
1574 p_fee_ass_log_creation_dt,
1575 v_message_name,
1576 'ACTUAL', -- Process Mode
1577 l_course_type,
1578 TRUNC(SYSDATE),
1579 'N',
1580 l_n_waiver_amount
1581 ) THEN
1582 finpl_prc_ins_log_entry(
1583 v_message_name,
1584 'N');
1585 END IF;
1586 END;
1587 EXCEPTION
1588 WHEN OTHERS THEN
1589 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1590 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINPL_PRC_REVERSE_FEE_ASSESS');
1591 IGS_GE_MSG_STACK.ADD;
1592 App_Exception.Raise_Exception;
1593 END finpl_prc_reverse_fee_assess;
1594 --Modified the declartion of datatype based on IGS_FI_FEE_ASS_DEBT_V to point to IGS_FI_FEE_AS.
1595 FUNCTION finpl_prc_this_crs_liable (
1596 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1597 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1598 p_fee_cat IGS_EN_STDNT_PS_ATT.FEE_CAT%TYPE,
1599 p_fee_type IGS_FI_FEE_AS.FEE_TYPE%TYPE,
1600 p_fee_cal_type IGS_FI_FEE_AS.fee_cal_type%TYPE,
1601 p_fee_ci_sequence_number IGS_FI_FEE_AS.fee_ci_sequence_number%TYPE)
1602 RETURN VARCHAR2 AS
1603 BEGIN -- finpl_prc_this_crs_liable
1604 -- Check if this IGS_PS_COURSE is liable for the fees
1605 DECLARE
1606 v_dummy VARCHAR2(1);
1607
1608 -- Enh # 2122257
1609 -- SFCR015 : Change in Fee Category
1610 -- Modified the cursor c_sfv
1611 -- Enh# 3167098, Removed usage of igs_fi_f_cat_cal_rel and igs_fi_cng_fcat_lbl_sca_pr_v
1612 CURSOR c_sfv IS
1613 SELECT 'X'
1614 FROM IGS_FI_F_CAT_FEE_LBL_SCA_V sfv
1615 WHERE sfv.person_id = p_person_id
1616 AND sfv.course_cd = p_course_cd
1617 AND sfv.FEE_CAT = p_fee_cat
1618 AND sfv.FEE_TYPE = p_fee_type
1619 AND sfv.fee_cal_type = p_fee_cal_type
1620 AND sfv.fee_ci_sequence_number = p_fee_ci_sequence_number;
1621
1622 BEGIN
1623 OPEN c_sfv;
1624 FETCH c_sfv INTO v_dummy;
1625 IF c_sfv%FOUND THEN
1626 CLOSE c_sfv;
1627 RETURN 'Y';
1628 ELSE
1629 CLOSE c_sfv;
1630 RETURN 'N';
1631 END IF;
1632 EXCEPTION
1633 WHEN OTHERS THEN
1634 IF c_sfv%ISOPEN THEN
1635 CLOSE c_sfv;
1636 END IF;
1637 RAISE;
1638 END;
1639 EXCEPTION
1640 WHEN OTHERS THEN
1641 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1642 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINPL_PRC_THIS_CRS_LIABLE');
1643 IGS_GE_MSG_STACK.ADD;
1644 App_Exception.Raise_Exception;
1645 END finpl_prc_this_crs_liable;
1646 --Modified the declartion of datatype based on IGS_FI_FEE_ASS_DEBT_V to point to IGS_FI_FEE_AS.
1647 FUNCTION finpl_prc_another_crs_liable (
1648 p_person_id IGS_EN_STDNT_PS_ATT.person_id%TYPE,
1649 p_course_cd IGS_EN_STDNT_PS_ATT.course_cd%TYPE,
1650 p_fee_cal_type IGS_FI_FEE_AS.fee_cal_type%TYPE,
1651 p_fee_ci_sequence_number IGS_FI_FEE_AS.fee_ci_sequence_number%TYPE,
1652 p_fee_type IGS_FI_FEE_AS.FEE_TYPE%TYPE)
1653 RETURN VARCHAR2 AS
1654 BEGIN -- finpl_prc_another_crs_liable
1655 -- Check if another IGS_PS_COURSE is liable for the fees
1656 DECLARE
1657 cst_unconfirm CONSTANT VARCHAR2(10) := 'UNCONFIRM';
1658 v_dummy VARCHAR2(1);
1659
1660 -- Enh # 2122257
1661 -- SFCR015 : Change in Fee Category
1662 -- Modified the cursor c_sfv
1663 -- Enh# 3167098, Removed usage of igs_fi_f_cat_cal_rel and igs_fi_cng_fcat_lbl_sca_pr_v
1664 CURSOR c_sfv IS
1665 SELECT 'X'
1666 FROM IGS_FI_F_CAT_FEE_LBL_SCA_V sfv
1667 WHERE sfv.person_id = p_person_id
1668 AND sfv.course_cd <> p_course_cd
1669 AND sfv.FEE_TYPE = p_fee_type
1670 AND sfv.fee_cal_type = p_fee_cal_type
1671 AND sfv.fee_ci_sequence_number = p_fee_ci_sequence_number
1672 AND (sfv.fee_ass_ind = 'Y'
1673 OR sfv.course_attempt_status = cst_unconfirm);
1674
1675 BEGIN
1676 OPEN c_sfv;
1677 FETCH c_sfv INTO v_dummy;
1678 IF c_sfv%FOUND THEN
1679 CLOSE c_sfv;
1680 RETURN 'Y';
1681 ELSE
1682 CLOSE c_sfv;
1683 RETURN 'N';
1684 END IF;
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 IF c_sfv%ISOPEN THEN
1688 CLOSE c_sfv;
1689 END IF;
1690 RAISE;
1691 END;
1692 EXCEPTION
1693 WHEN OTHERS THEN
1694 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1695 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINPL_PRC_ANOTHER_CRS_LIABLE');
1696 IGS_GE_MSG_STACK.ADD;
1697 App_Exception.Raise_Exception;
1698 END finpl_prc_another_crs_liable;
1699 PROCEDURE finpl_prc_delete_fee_contract (
1700 p_person_id IGS_FI_FEE_AS_RT.person_id%TYPE,
1701 p_course_cd IGS_FI_FEE_AS_RT.course_cd%TYPE)
1702 AS
1703 BEGIN -- finpl_prc_delete_fee_contract
1704 -- Delete the fee contract
1705 DECLARE
1706 CURSOR c_cfar IS
1707 SELECT cfar.person_id , cfar.ROWID
1708 FROM IGS_FI_FEE_AS_RT cfar
1709 WHERE cfar.person_id = p_person_id AND
1710 cfar.course_cd = p_course_cd
1711 FOR UPDATE OF cfar.person_id NOWAIT;
1712 BEGIN
1713 FOR v_cfar_rec IN c_cfar LOOP
1714 IGS_FI_FEE_AS_RT_PKG.DELETE_ROW(X_ROWID =>v_cfar_rec.ROWID);
1715 END LOOP;
1716 EXCEPTION
1717 WHEN e_resource_busy THEN
1718 finpl_prc_ins_log_entry(
1719 4724,
1720 'N');
1721 v_delete_sca_ind := 'N';
1722 WHEN OTHERS THEN
1723 IF c_cfar%ISOPEN THEN
1724 CLOSE c_cfar;
1725 END IF;
1726 RAISE;
1727 END;
1728 EXCEPTION
1729 WHEN OTHERS THEN
1730 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1731 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINPL_PRC_DELETE_FEE_CONTRACT');
1732 IGS_GE_MSG_STACK.ADD;
1733 App_Exception.Raise_Exception;
1734 END finpl_prc_delete_fee_contract;
1735 BEGIN
1736 -- Validate input parameters
1737 IF p_person_id IS NULL OR
1738 p_course_cd IS NULL OR
1739 p_course_attempt_status IS NULL OR
1740 p_log_creation_dt IS NULL OR
1741 p_key IS NULL THEN
1742 RETURN;
1743 END IF;
1744 -- Initialise the output parameter
1745 p_delete_sca_ind := 'Y';
1746 -- Only process unconfirmed IGS_PS_COURSE attempts
1747 IF p_course_attempt_status <> cst_unconfirm THEN
1748 RETURN;
1749 END IF;
1750 -- Check if a fee assessment exists for the IGS_PE_PERSON
1751 FOR v_fas_rec IN c_fas LOOP
1752 IF v_fas_rec.course_cd = p_course_cd OR
1753 v_fas_rec.course_cd IS NULL THEN
1754 IF v_fas_rec.course_cd = p_course_cd THEN
1755 v_delete_sca_ind := 'N';
1756 END IF;
1757 -- Check if an assessed debt liability exists for the IGS_PS_COURSE attempt
1758 FOR v_fasdv_rec IN c_fasdv(
1759 v_fas_rec.course_cd) LOOP
1760 IF v_fasdv_rec.course_cd IS NOT NULL THEN
1761 -- Process this IGS_PS_COURSE attempt
1762 finpl_prc_end_fee_contract(
1763 p_person_id,
1764 p_course_cd);
1765 IF v_fasdv_rec.assessment_amount <> 0 THEN
1766 finpl_prc_reverse_fee_assess(
1767 p_person_id,
1768 p_course_cd,
1769 v_fasdv_rec.fee_cal_type,
1770 v_fasdv_rec.fee_ci_sequence_number,
1771 v_fasdv_rec.FEE_TYPE,
1772 p_fee_cat);
1773 END IF;
1774 ELSE
1775 -- Process IGS_OR_INSTITUTION fees
1776 IF v_fasdv_rec.assessment_amount <> 0 THEN
1777 IF finpl_prc_this_crs_liable (
1778 p_person_id,
1779 p_course_cd,
1780 p_fee_cat,
1781 v_fasdv_rec.FEE_TYPE,
1782 v_fasdv_rec.fee_cal_type,
1783 v_fasdv_rec.fee_ci_sequence_number) = 'Y' THEN
1784 IF finpl_prc_another_crs_liable (
1785 p_person_id,
1786 p_course_cd,
1787 v_fasdv_rec.fee_cal_type,
1788 v_fasdv_rec.fee_ci_sequence_number,
1789 v_fasdv_rec.FEE_TYPE) = 'N' THEN
1790 v_delete_sca_ind := 'N';
1791 finpl_prc_end_fee_contract(
1792 p_person_id,
1793 p_course_cd);
1794 finpl_prc_reverse_fee_assess(
1795 p_person_id,
1796 p_course_cd,
1797 v_fasdv_rec.fee_cal_type,
1798 v_fasdv_rec.fee_ci_sequence_number,
1799 v_fasdv_rec.FEE_TYPE,
1800 p_fee_cat);
1801 END IF;
1802 END IF;
1803 END IF;
1804 END IF;
1805 END LOOP; -- c_fasdv
1806 END IF;
1807 END LOOP; -- c_fas
1808 IF v_delete_sca_ind = 'Y' THEN
1809 finpl_prc_delete_fee_contract (
1810 p_person_id,
1811 p_course_cd);
1812 END IF;
1813 IF v_delete_sca_ind = 'N' AND
1814 v_log_entry_ind = 'N' THEN
1815 finpl_prc_ins_log_entry(
1816 4741,
1817 'N');
1818 END IF;
1819 p_delete_sca_ind := v_delete_sca_ind;
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 IF c_fas%ISOPEN THEN
1823 CLOSE c_fas;
1824 END IF;
1825 IF c_fasdv%ISOPEN THEN
1826 CLOSE c_fasdv;
1827 END IF;
1828 RAISE;
1829 END;
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 Fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1833 Fnd_Message.Set_Token('NAME','IGS_FI_GEN_004.FINP_PRC_SCA_UNCONF');
1834 IGS_GE_MSG_STACK.ADD;
1835 App_Exception.Raise_Exception;
1836 END finp_prc_sca_unconf;
1837 END igs_fi_gen_004;
[Home] [Help]