1 PACKAGE BODY igs_fi_prc_stdnt_dpsts AS
2 /* $Header: IGSFI77B.pls 120.1 2006/02/10 01:29:05 sapanigr noship $ */
3
4 /******************************************************************
5 Created By : Vinay Chappidi
6 Date Created By : 05-DEC-2002
7 Purpose : This package contains the specification for the
8 Process Deposits concurrent request. This process
9 has an out come of either Transferring the Deposit
10 to the Student Account as a Payment or Forfeit the
11 Deposit or take no action on the Deposit transaction.
12 Known limitations,
13 enhancements,
14 remarks :
15 Change History
16 Who When What
17 sapanigr 12-Feb-2006 Bug#5018036 - Modified cursor c_person in validate_person procedure. (for R12 SQL Repository tuning)
18 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
19 Modified prc_stdnt_deposit() - added call to chk_manage_account()
20 vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Modified procedures generate_log,prc_stdnt_deposit.
21 vchappid 29-Jan-2003 Bug# 2729927,In procedure generate_log,log file should log Deposit Number, Amount details
22 only when they have a value
23 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
24 input parameter is an invalid value
25 Bug# 2729984, Incase when the program attempt status is in other than enrolled status
26 incorrect token is being logged. Corrected the token
27 While Selecting Person Group member, start date and end date are checked.
28 Additional issue: Local variables l_c_message_name, l_c_outcome have to be re-initialized
29 before start of each Enrollment Credit
30 vchappid 09-Jan-2003 Bug# 2730010, when a student has program attempt as Enrolled and no action is being
31 taken on the Deposit then different message is logged in the log file.
32 vchappid 06-Jan-2003 Bug# 2729948, message name changed from IGS_FI_PRS_OR_PRSIDGRP to IGS_FI_PRS_PRSIDGRP_NULL
33 whenever user don't provide either person id or person id group as parameter to the request
34 Additionally, Person Group Id parameter is being validated twice.
35 Removed duplicate Person Group ID validation.
36 ******************************************************************/
37
38 g_c_yes CONSTANT VARCHAR2(1) := 'Y';
39 g_c_no CONSTANT VARCHAR2(1) := 'N';
40 g_c_hold CONSTANT VARCHAR2(4) := 'HOLD';
41 g_c_transfer CONSTANT igs_lookup_values.lookup_code%TYPE := 'TRANSFER';
42 g_c_forfeit CONSTANT igs_lookup_values.lookup_code%TYPE := 'FORFEIT';
43 g_c_enrdeposit CONSTANT igs_lookup_values.lookup_code%TYPE := 'ENRDEPOSIT';
44 g_c_othdeposit CONSTANT igs_lookup_values.lookup_code%TYPE := 'OTHDEPOSIT';
45 g_c_sca_enrolled CONSTANT igs_en_stdnt_ps_att.course_attempt_status%TYPE := 'ENROLLED';
46 g_c_sca_other CONSTANT igs_en_stdnt_ps_att.course_attempt_status%TYPE := 'OTHER';
47 g_c_appl_dep_lvl CONSTANT igs_lookup_values.lookup_code%TYPE := 'APPL';
48 g_c_all_dep_lvl CONSTANT igs_lookup_values.lookup_code%TYPE := 'ALL';
49 g_c_prg_dep_lvl CONSTANT igs_lookup_values.lookup_code%TYPE := 'PROGRAM';
50 g_c_prgty_dep_lvl CONSTANT igs_lookup_values.lookup_code%TYPE := 'PROGRAM_TYPE';
51 g_null CONSTANT VARCHAR2(1):= NULL;
52 g_credit_type_id igs_fi_cr_types_all.credit_type_id%TYPE;
53
54 do_nothing EXCEPTION;
55
56 -- Pl/SQL table for storing all Student Program Attempts
57 TYPE stdnt_sca_tab IS TABLE OF igs_ps_ver.course_cd%TYPE
58 INDEX BY BINARY_INTEGER;
59
60 -- Pl/SQL table for storing all Student Program Attempts Version Numbers
61 TYPE stdnt_sca_ver_num_tab IS TABLE OF igs_ps_ver.version_number%TYPE
62 INDEX BY BINARY_INTEGER;
63
64 -- Pl/SQL table for storing all Student Program Attempts Program Types
65 TYPE stdnt_sca_typ_tab IS TABLE OF igs_ps_ver.course_type%TYPE
66 INDEX BY BINARY_INTEGER;
67
68 -- Pl/SQL table for storing all Student Program Attempts Statuses
69 TYPE stdnt_sca_status_tab IS TABLE OF igs_en_stdnt_ps_att.course_attempt_status%TYPE
70 INDEX BY BINARY_INTEGER;
71
72
73 TYPE rec_input_param IS RECORD ( credit_class igs_fi_cr_types.credit_class%TYPE,
74 credit_type_id igs_fi_cr_types.credit_type_id%TYPE,
75 load_cal_type igs_ca_inst.cal_type%TYPE,
76 load_cal_seq_num igs_ca_inst.sequence_number%TYPE,
77 fee_cal_type igs_ca_inst.cal_type%TYPE,
78 fee_cal_seq_num igs_ca_inst.sequence_number%TYPE,
79 gl_date DATE,
80 test_mode VARCHAR2(1));
81
82 FUNCTION check_acad_load_adm_rel(p_c_load_cal_type VARCHAR2,
83 p_n_load_ci_seq_num NUMBER,
84 p_c_acad_cal_type VARCHAR2,
85 p_n_acad_ci_seq_num NUMBER,
86 p_c_adm_cal_type VARCHAR2,
87 p_n_adm_ci_seq_num NUMBER)
88 RETURN VARCHAR2 AS
89 /******************************************************************
90 Created By : Vinay Chappidi
91 Date Created By : 05-DEC-2002
92 Purpose : Checks if the relation Exists between Acad, Load and Admission
93 Known limitations,
94 enhancements,
95 remarks :
96 Change History
97 Who When What
98 ******************************************************************/
99
100
101 CURSOR c_check_rel(cp_c_load_cal_type igs_ca_inst.cal_type%TYPE,cp_n_load_ci_seq_num igs_ca_inst.sequence_number%TYPE,
102 cp_c_acad_cal_type igs_ca_inst.cal_type%TYPE,cp_n_acad_ci_seq_num igs_ca_inst.sequence_number%TYPE,
103 cp_c_adm_cal_type igs_ca_inst.cal_type%TYPE,cp_n_adm_ci_seq_num igs_ca_inst.sequence_number%TYPE)
104 IS
105 SELECT 'X'
106 FROM igs_ca_inst_rel r1
107 WHERE r1.sub_cal_type = cp_c_load_cal_type AND
108 r1.sub_ci_sequence_number = cp_n_load_ci_seq_num AND
109 r1.sup_cal_type = cp_c_acad_cal_type AND
110 r1.sup_ci_sequence_number = cp_n_acad_ci_seq_num AND
111 EXISTS (SELECT 'X'
112 FROM igs_ca_inst_rel r2
113 WHERE r2.sup_cal_type = r1.sup_cal_type AND
114 r2.sup_ci_sequence_number = r1.sup_ci_sequence_number AND
115 r2.sub_cal_type = cp_c_adm_cal_type AND
116 r2.sub_ci_sequence_number = cp_n_adm_ci_seq_num);
117 l_c_temp VARCHAR2(1);
118
119 BEGIN
120
121 OPEN c_check_rel(p_c_load_cal_type,
122 p_n_load_ci_seq_num,
123 p_c_acad_cal_type,
124 p_n_acad_ci_seq_num,
125 p_c_adm_cal_type,
126 p_n_adm_ci_seq_num);
127 FETCH c_check_rel INTO l_c_temp;
128 CLOSE c_check_rel;
129
130 IF l_c_temp IS NOT NULL THEN
131 RETURN 'Y';
132 ELSE
133 RETURN 'N';
134 END IF;
135
136 END check_acad_load_adm_rel;
137
138
139 PROCEDURE check_enrollment(p_n_person_id igs_fi_parties_v.person_id%TYPE,
140 p_tab_course_cd stdnt_sca_tab,
141 p_tab_ver_num stdnt_sca_ver_num_tab,
142 p_tab_sca_typ stdnt_sca_typ_tab,
143 p_rec_input_param rec_input_param,
144 p_c_sca_status igs_en_stdnt_ps_att.course_attempt_status%TYPE,
145 p_c_dep_lvl VARCHAR2,
146 p_c_action OUT NOCOPY igs_lookup_values.lookup_code%TYPE,
147 p_c_sca_att_status OUT NOCOPY igs_lookup_values.lookup_code%TYPE)
148 AS
149 /******************************************************************
150 Created By : Vinay Chappidi
151 Date Created By : 05-DEC-2002
152 Purpose : Checks if student has Enrollment in a set of programs
153 Known limitations,
154 enhancements,
155 remarks :
156 Change History
157 Who When What
158 ******************************************************************/
159
160
161 CURSOR c_sca_status (cp_n_person_id igs_fi_parties_v.person_id%TYPE,
162 cp_c_load_cal_type igs_ca_inst.cal_type%TYPE,
163 cp_n_load_cal_seq_num igs_ca_inst.sequence_number%TYPE,
164 cp_c_sca_status VARCHAR2)
165 IS
166 SELECT spa.course_cd course_cd,
167 spa.version_number version_number,
168 ps.course_type course_type,
169 spa.course_attempt_status course_attempt_status
170 FROM igs_en_stdnt_ps_att spa,
171 igs_ps_ver ps
172 WHERE spa.course_cd = ps.course_cd AND
173 spa.version_number = ps.version_number AND
174 spa.person_id = cp_n_person_id AND
175 (
176 (cp_c_sca_status= g_c_sca_enrolled AND spa.course_attempt_status = 'ENROLLED')
177 OR
178 (
179 cp_c_sca_status = g_c_sca_other
180 AND
181 spa.course_attempt_status IN ('COMPLETED','INTERMIT','INACTIVE','UNCONFIRM','ENROLLED')
182 )
183 )
184 AND
185 (
186 (spa.course_attempt_status ='ENROLLED' AND
187 EXISTS
188 (
189 SELECT 'X'
190 FROM igs_en_su_attempt sua,
191 igs_ca_load_to_teach_v lci
192 WHERE sua.person_id = spa.person_id AND
193 sua.course_cd = spa.course_cd AND
194 sua.cal_type = lci.teach_cal_type AND
195 sua.ci_sequence_number = lci.teach_ci_sequence_number AND
196 (lci.load_cal_type = cp_c_load_cal_type AND
197 lci.load_ci_sequence_number = cp_n_load_cal_seq_num) AND
198 (
199 (cp_c_sca_status= g_c_sca_enrolled AND sua.unit_attempt_status = 'ENROLLED')
200 OR
201 (cp_c_sca_status = g_c_sca_other
202 AND
203 sua.unit_attempt_status IN ('UNCONFIRM','WAITLISTED','COMPLETED')
204 )
205 )
206 )
207 OR
208 (spa.course_attempt_status IN ('COMPLETED','INTERMIT','INACTIVE','UNCONFIRM') AND cp_c_sca_status = g_c_sca_other)
209 )
210 );
211
212 -- pl/sql table structure for storing course codes in which the student has program attempts
213 l_tab_std_sca_attmpts stdnt_sca_tab;
214
215 -- pl/sql table structure for storing version number of the corresponding course code
216 -- in which the student has program attempts
217 l_tab_std_sca_ver_num stdnt_sca_ver_num_tab;
218
219 -- pl/sql table structure for storing program types of the courses in which the student has program attempts
220 l_tab_std_sca_typ stdnt_sca_typ_tab;
221
222 -- pl/sql table structure for storing program status of the courses in which the student has program attempts
223 l_tab_std_sca_stat_typ stdnt_sca_status_tab;
224
225 -- this flag is used if the program attempt match with the one that is found from the above cursor
226 l_b_found BOOLEAN := FALSE;
227 l_c_course_attempt_status igs_lookup_values.lookup_code%TYPE;
228
229 BEGIN
230 -- For the person incontext and for the load calendar instance as selected by the user
231 -- select all program attempts the student has attempted and with the course attempt status decided
232 -- cursor parameter cp_c_sca_status
233 OPEN c_sca_status (p_n_person_id,
234 p_rec_input_param.load_cal_type,
235 p_rec_input_param.load_cal_seq_num,
236 p_c_sca_status);
237 FETCH c_sca_status BULK COLLECT INTO l_tab_std_sca_attmpts,l_tab_std_sca_ver_num,l_tab_std_sca_typ,l_tab_std_sca_stat_typ;
238 CLOSE c_sca_status;
239
240 -- If the deposit level is passed APPL, PROGRAM, ALL then the course codes have to be matched
241 -- else i.e. incase of Deposit Level is Program Type then the course types should be matched
242 -- When matching course codes both course code and version number should be matched
243 -- When a matching record is found, flag l_b_found will be set to TRUE
244 IF p_c_dep_lvl IN (g_c_appl_dep_lvl,g_c_all_dep_lvl,g_c_prg_dep_lvl) THEN
245
246 IF (l_tab_std_sca_attmpts.COUNT <> 0 AND p_tab_course_cd.COUNT <>0) THEN
247
248 FOR i IN l_tab_std_sca_attmpts.FIRST .. l_tab_std_sca_attmpts.LAST
249 LOOP
250 EXIT WHEN l_b_found;
251 FOR j IN p_tab_course_cd.FIRST .. p_tab_course_cd.LAST
252 LOOP
253 EXIT WHEN l_b_found;
254 IF ((p_tab_course_cd(j) = l_tab_std_sca_attmpts(i)) AND (p_tab_ver_num(j) = l_tab_std_sca_ver_num(i) )) THEN
255 l_b_found := TRUE;
256 l_c_course_attempt_status := l_tab_std_sca_stat_typ(i);
257 END IF;
258 END LOOP;
259 END LOOP;
260 END IF;
261 ELSIF p_c_dep_lvl = g_c_prgty_dep_lvl THEN
262 IF (l_tab_std_sca_typ.COUNT <> 0 AND p_tab_sca_typ.COUNT<> 0 ) THEN
263 FOR i IN l_tab_std_sca_typ.FIRST .. l_tab_std_sca_typ.LAST
264 LOOP
265 EXIT WHEN l_b_found;
266 FOR j IN p_tab_sca_typ.FIRST .. p_tab_sca_typ.LAST
267 LOOP
268 EXIT WHEN l_b_found;
269 IF (p_tab_sca_typ(j) = l_tab_std_sca_typ(i)) THEN
270 l_b_found := TRUE;
271 l_c_course_attempt_status := l_tab_std_sca_stat_typ(i);
272 END IF;
273 END LOOP;
274 END LOOP;
275 END IF;
276 END IF;
277
278 -- When the student is found to have Enrolled program attempts then the action indicator is set to 'Transfer'
279 -- and no further processing should be done
280 -- Incase where the student has program attempts in any other status then no action should be taken
281 -- Set the flag to 'Hold'
282 IF l_b_found THEN
283 IF p_c_sca_status = g_c_sca_enrolled THEN
284 p_c_action := g_c_transfer;
285 p_c_sca_att_status := NULL;
286 ELSE
287 p_c_action := g_c_hold;
288 p_c_sca_att_status := l_c_course_attempt_status;
289 END IF;
290 ELSE
291 p_c_action := NULL;
292 p_c_sca_att_status := NULL;
293 END IF;
294 END check_enrollment;
295
296 PROCEDURE generate_log(p_n_person_id igs_fi_parties_v.person_id%TYPE,
297 p_c_credit_number igs_fi_credits.credit_number%TYPE,
298 p_n_amount igs_fi_credits.amount%TYPE,
299 p_c_action VARCHAR2,
300 p_c_payment_cr_num igs_fi_credits.credit_number%TYPE,
301 p_c_message_name fnd_new_messages.message_name%TYPE,
302 p_c_sca_att_status igs_lookup_values.lookup_code%TYPE) AS
303 /******************************************************************
304 Created By : Vinay Chappidi
305 Date Created By : 05-DEC-2002
306 Purpose : Log Messages
307 Known limitations,
308 enhancements,
309 remarks :
310 Change History
311 Who When What
312 vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Added code to set the tokens when payment credit type is invalid.
313 vchappid 29-Jan-2003 Bug# 2729927, log file should log Deposit Number, Amount details only when they have a value
314 vchappid 09-Jan-2003 Bug# 2729984, Incase when the program attempt status is in other than enrolled status
315 incorrect token is being logged. Corrected the token
316 ******************************************************************/
317
318
319 CURSOR c_person(cp_n_person_id igs_fi_parties_v.person_id%TYPE)
320 IS
321 SELECT person_number, full_name
322 FROM igs_fi_parties_v
323 WHERE person_id = cp_n_person_id;
324
325 CURSOR cur_cr_type_names(cp_credit_type_id igs_fi_cr_types_all.credit_type_id%TYPE) IS
326 SELECT credit_type_name,payment_credit_type_name
327 FROM igs_fi_cr_types_v
328 WHERE credit_type_id = cp_credit_type_id;
329
330 rec_cur_cr_type_names cur_cr_type_names%ROWTYPE;
331
332 l_c_person c_person%ROWTYPE;
333 l_c_message_text fnd_new_messages.message_text%TYPE;
334
335 BEGIN
336
337 OPEN c_person(p_n_person_id);
338 FETCH c_person INTO l_c_person;
339 CLOSE c_person;
340
341 IF p_c_message_name IS NOT NULL THEN
342 IF p_c_message_name = 'IGS_FI_DP_SPA_NO_ACTION' THEN
343 fnd_message.set_name('IGS',p_c_message_name);
344 fnd_message.set_token('SPA_STAT',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',p_c_sca_att_status));
345
346 ELSIF p_c_message_name = 'IGS_FI_PCT_DCT_INVALID' THEN
347 OPEN cur_cr_type_names(g_credit_type_id);
348 FETCH cur_cr_type_names INTO rec_cur_cr_type_names;
349 CLOSE cur_cr_type_names;
350 fnd_message.set_name('IGS',p_c_message_name);
351 fnd_message.set_token('PAY_CR_TYPE',rec_cur_cr_type_names.payment_credit_type_name);
352 fnd_message.set_token('DEP_CR_TYPE',rec_cur_cr_type_names.credit_type_name);
353 ELSE
354 fnd_message.set_name('IGS',p_c_message_name);
355 END IF;
356 l_c_message_text:= fnd_message.get;
357 END IF;
358
359 -- Person Number and Deposit Action should be logged each and everytime.
360 -- Deposit Number, Amount and Payment Receipt Number should be printed only when they have a value
361 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
362 fnd_message.set_token('PERSON_NUM',l_c_person.person_number||' '||l_c_person.full_name);
363 fnd_file.put_line(fnd_file.log,fnd_message.get);
364
365 IF p_c_credit_number IS NOT NULL THEN
366 fnd_message.set_name('IGS','IGS_FI_DEP_REC_NUM');
367 fnd_message.set_token('DEP_REC_NUM',p_c_credit_number);
368 fnd_file.put_line(fnd_file.log,fnd_message.get);
369 END IF;
370
371 IF p_n_amount IS NOT NULL THEN
372 fnd_message.set_name('IGS','IGS_FI_DEP_AMOUNT');
373 fnd_message.set_token('DEP_AMNT',p_n_amount);
374 fnd_file.put_line(fnd_file.log,fnd_message.get);
375 END IF;
376
377 fnd_message.set_name('IGS','IGS_FI_DEP_ACTION');
378 fnd_message.set_token('DEP_ACTION',l_c_message_text);
379 fnd_file.put_line(fnd_file.log,fnd_message.get);
380
381 IF p_c_payment_cr_num IS NOT NULL THEN
382 fnd_message.set_name('IGS','IGS_FI_PAYMENT_REC_NUM');
383 fnd_message.set_token('PAYMNT_NUM',p_c_payment_cr_num);
384 fnd_file.put_line(fnd_file.log,fnd_message.get);
385 END IF;
386
387 -- Put a line to seperate from the previous log details
388 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
389 fnd_file.new_line(fnd_file.log);
390
391 END generate_log;
392
393 FUNCTION validate_credit_type(p_c_credit_type_id igs_fi_cr_types.credit_type_id%TYPE,
394 p_c_credit_class igs_fi_cr_types.credit_class%TYPE) RETURN BOOLEAN AS
395 /******************************************************************
396 Created By : Vinay Chappidi
397 Date Created By : 05-DEC-2002
398 Purpose : Validates Credit Type
399 Known limitations,
400 enhancements,
401 remarks :
402 Change History
403 Who When What
404 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
405 input parameter is an invalid value
406 ******************************************************************/
407 CURSOR c_credit_type(cp_credit_type_id IN igs_fi_cr_types.credit_type_id%TYPE)
408 IS
409 SELECT credit_type_name
410 FROM igs_fi_cr_types
411 WHERE credit_type_id = cp_credit_type_id AND
412 credit_class = p_c_credit_class;
413
414 l_b_return_val BOOLEAN := FALSE;
415 l_c_credit_type c_credit_type%ROWTYPE;
416 l_v_message_text fnd_new_messages.message_text%TYPE;
417 BEGIN
418 OPEN c_credit_type(p_c_credit_type_id);
419 FETCH c_credit_type INTO l_c_credit_type;
420 IF c_credit_type%NOTFOUND THEN
421 l_b_return_val := FALSE;
422 ELSE
423 l_v_message_text := l_c_credit_type.credit_type_name;
424 l_b_return_val := TRUE;
425 END IF;
426 CLOSE c_credit_type;
427
428 fnd_message.set_name('IGS','IGS_FI_CREDIT_TYPE');
429 fnd_message.set_token('CREDIT_TYPE',l_v_message_text);
430 fnd_file.put_line(fnd_file.log,fnd_message.get);
431
432 IF NOT l_b_return_val THEN
433 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
434 fnd_message.set_token('PARAMETER','P_N_CREDIT_TYPE_ID');
435 fnd_file.put_line(fnd_file.log,fnd_message.get);
436 END IF;
437
438 RETURN l_b_return_val;
439 END validate_credit_type;
440
441 FUNCTION validate_credit_class(p_c_credit_class IN VARCHAR2) RETURN BOOLEAN AS
442 /******************************************************************
443 Created By : Vinay Chappidi
444 Date Created By : 05-DEC-2002
445 Purpose : validates Credit Class
446 Known limitations,
447 enhancements,
448 remarks :
449 Change History
450 Who When What
451 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
452 input parameter is an invalid value
453 ******************************************************************/
454
455 l_v_message_text fnd_new_messages.message_text%TYPE;
456 l_b_return_val BOOLEAN;
457 BEGIN
458 IF p_c_credit_class NOT IN (g_c_enrdeposit, g_c_othdeposit) THEN
459 l_b_return_val := FALSE;
460 ELSE
461 l_b_return_val := TRUE;
462 END IF;
463
464 fnd_message.set_name('IGS','IGS_FI_CREDIT_CLASS');
465 fnd_message.set_token('CREDIT_CLASS',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CLASS',p_c_credit_class));
466 fnd_file.put_line(fnd_file.log,fnd_message.get);
467
468 IF NOT l_b_return_val THEN
469 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
470 fnd_message.set_token('PARAMETER','P_C_CREDIT_CLASS');
471 fnd_file.put_line(fnd_file.log,fnd_message.get);
472 END IF;
473
474 RETURN l_b_return_val;
475 END validate_credit_class;
476
477
478 FUNCTION validate_person(p_n_person_id igs_pe_person.person_id%TYPE) RETURN BOOLEAN AS
479 /******************************************************************
480 Created By : Vinay Chappidi
481 Date Created By : 05-DEC-2002
482 Purpose : validates Person
483 Known limitations,
484 enhancements,
485 remarks :
486 Change History
487 Who When What
488 sapanigr 12-Feb-2006 Bug#5018036 - Cursor c_person now queries hz_parties instead of
489 igs_fi_parties_v. (R12 SQL Repository tuning)
490 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
491 input parameter is an invalid value
492 ******************************************************************/
493
494 CURSOR c_person (cp_n_person_id hz_parties.party_id%TYPE)
495 IS
496 SELECT party_number
497 FROM hz_parties
498 WHERE party_id = cp_n_person_id;
499
500 l_c_person_number hz_parties.party_number%TYPE;
501 l_v_message_text fnd_new_messages.message_text%TYPE;
502 l_b_return_val BOOLEAN;
503
504 BEGIN
505 OPEN c_person(p_n_person_id);
506 FETCH c_person INTO l_c_person_number;
507 IF c_person%NOTFOUND THEN
508 l_b_return_val := FALSE;
509 ELSE
510 l_v_message_text := l_c_person_number;
511 l_b_return_val := TRUE;
512 END IF;
513 CLOSE c_person;
514
515 fnd_message.set_name('IGS','IGS_FI_PERSON_NUM');
516 fnd_message.set_token('PERSON_NUM',l_v_message_text);
517 fnd_file.put_line(fnd_file.log,fnd_message.get);
518
519 IF NOT l_b_return_val THEN
520 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
521 fnd_message.set_token('PARAMETER','P_N_PERSON_ID');
522 fnd_file.put_line(fnd_file.log,fnd_message.get);
523 END IF;
524
525 RETURN l_b_return_val;
526 END validate_person;
527
528 FUNCTION validate_person_grp(p_n_person_id_grp igs_pe_persid_group.group_id%TYPE) RETURN BOOLEAN AS
529 /******************************************************************
530 Created By : Vinay Chappidi
531 Date Created By : 05-DEC-2002
532 Purpose : validates Person Id Group
533 Known limitations,
534 enhancements,
535 remarks :
536 Change History
537 Who When What
538 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
539 input parameter is an invalid value
540 ******************************************************************/
541
542 CURSOR c_check_prsn_grp
543 IS
544 SELECT group_cd
545 FROM igs_pe_persid_group
546 WHERE group_id= p_n_person_id_grp AND
547 TRUNC(create_dt)<= TRUNC(SYSDATE) AND
548 closed_ind = 'N';
549 l_c_group_cd igs_pe_persid_group.group_cd%TYPE;
550
551 l_b_return_val BOOLEAN;
552 l_v_message_text fnd_new_messages.message_text%TYPE;
553
554 BEGIN
555 OPEN c_check_prsn_grp;
556 FETCH c_check_prsn_grp INTO l_c_group_cd;
557 IF c_check_prsn_grp%NOTFOUND THEN
558 l_b_return_val := FALSE;
559 ELSE
560 l_v_message_text := l_c_group_cd;
561 l_b_return_val := TRUE;
562 END IF;
563 CLOSE c_check_prsn_grp;
564
565 fnd_message.set_name('IGS','IGS_FI_PERSON_GROUP');
566 fnd_message.set_token('PERSON_GRP',l_v_message_text);
567 fnd_file.put_line(fnd_file.log,fnd_message.get);
568
569 IF NOT l_b_return_val THEN
570 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
571 fnd_message.set_token('PARAMETER','P_N_PERSON_ID_GRP');
572 fnd_file.put_line(fnd_file.log,fnd_message.get);
573 END IF;
574 RETURN l_b_return_val;
575 END validate_person_grp;
576
577 FUNCTION validate_term_cal_inst(p_c_load_cal_type IN igs_ca_inst.cal_type%TYPE,
578 p_n_load_seq_number igs_ca_inst.sequence_number%TYPE) RETURN BOOLEAN AS
579 /******************************************************************
580 Created By : Vinay Chappidi
581 Date Created By : 05-DEC-2002
582 Purpose : validates term calendar
583 Known limitations,
584 enhancements,
585 remarks :
586 Change History
587 Who When What
588 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
589 input parameter is an invalid value
590 ******************************************************************/
591
592 CURSOR c_cal_inst (cp_c_cal_type igs_ca_inst.cal_type%TYPE,
593 cp_n_seq_num igs_ca_inst.sequence_number%TYPE)
594 IS
595 SELECT t.cal_type, t.start_dt, t.end_dt
596 FROM igs_ca_inst t, igs_ca_type ty
597 WHERE t.cal_type = ty.cal_type
598 AND ty.s_cal_cat='LOAD'
599 AND t.cal_type = cp_c_cal_type
600 AND t.sequence_number = cp_n_seq_num;
601
602 l_rec_c_cal_inst c_cal_inst%ROWTYPE;
603
604 l_b_ret_status BOOLEAN;
605 l_c_message_text fnd_new_messages.message_text%TYPE;
606
607 BEGIN
608
609 IF (p_c_load_cal_type IS NULL OR p_n_load_seq_number IS NULL ) THEN
610 l_b_ret_status := FALSE;
611 ELSE
612 OPEN c_cal_inst(p_c_load_cal_type, p_n_load_seq_number);
613 FETCH c_cal_inst INTO l_rec_c_cal_inst;
614 IF c_cal_inst%NOTFOUND THEN
615 l_b_ret_status := FALSE;
616 ELSE
617 l_c_message_text := l_rec_c_cal_inst.cal_type||' - '||l_rec_c_cal_inst.start_dt||' - '|| l_rec_c_cal_inst.end_dt;
618 l_b_ret_status := TRUE;
619 END IF;
620 CLOSE c_cal_inst;
621 END IF;
622
623 fnd_message.set_name('IGS','IGS_FI_TERM');
624 fnd_message.set_token('TERM',l_c_message_text);
625 fnd_file.put_line(fnd_file.log, fnd_message.get);
626
627 IF NOT l_b_ret_status THEN
628 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
629 fnd_message.set_token('PARAMETER','P_C_TERM_CAL_INST');
630 fnd_file.put_line(fnd_file.log,fnd_message.get);
631 END IF;
632
633 RETURN l_b_ret_status;
634 END validate_term_cal_inst;
635
636
637 PROCEDURE validate_gl_date(p_d_gl_date IN DATE,
638 p_b_ret_status OUT NOCOPY BOOLEAN,
639 p_c_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE) AS
640 /******************************************************************
641 Created By : Vinay Chappidi
642 Date Created By : 05-DEC-2002
643 Purpose : validates GL Date
644 Known limitations,
645 enhancements,
646 remarks :
647 Change History
648 Who When What
649 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
650 input parameter is an invalid value
651 ******************************************************************/
652
653 l_v_closing_status igs_fi_gl_periods_v.closing_status%TYPE;
654 l_v_message_name fnd_new_messages.message_name%TYPE;
655 l_v_message_text fnd_new_messages.message_text%TYPE;
656 l_b_ret_status BOOLEAN := TRUE;
657 BEGIN
658 --Validate the GL Date.
659 igs_fi_gen_gl.get_period_status_for_date(p_d_date => p_d_gl_date,
660 p_v_closing_status => l_v_closing_status,
661 p_v_message_name => l_v_message_name
662 );
663 IF l_v_message_name IS NOT NULL THEN
664 l_v_message_text := p_d_gl_date;
665 p_c_message_name:= l_v_message_name;
666 p_b_ret_status := FALSE;
667 ELSE
668 IF l_v_closing_status NOT IN ('O','F') THEN
669 l_v_message_text := p_d_gl_date;
670 p_c_message_name := 'IGS_FI_INVALID_GL_DATE';
671 p_b_ret_status := FALSE;
672 ELSE
673 l_v_message_text := p_d_gl_date;
674 p_c_message_name := NULL;
675 p_b_ret_status := TRUE;
676 END IF;
677 END IF;
678 -- when no errors are encountered then return TRUE
679 fnd_message.set_name('IGS','IGS_FI_GL_DATE');
680 fnd_message.set_token('GL_DATE',l_v_message_text);
681 fnd_file.put_line(fnd_file.log,fnd_message.get);
682 END validate_gl_date;
683
684 FUNCTION validate_test_mode(p_c_test_mode VARCHAR2) RETURN BOOLEAN AS
685 /******************************************************************
686 Created By : Vinay Chappidi
687 Date Created By : 05-DEC-2002
688 Purpose : validates test mode
689 Known limitations,
690 enhancements,
691 remarks :
692 Change History
693 Who When What
694 vchappid 09-Jan-2003 Bug# 2729935, Invalid Value token is removed from all parameters when the
695 input parameter is an invalid value
696 ******************************************************************/
697
698 l_b_return_val BOOLEAN;
699 l_v_message_text fnd_new_messages.message_text%TYPE;
700 BEGIN
701 IF p_c_test_mode NOT IN (g_c_yes,g_c_no) THEN
702 l_b_return_val := FALSE;
703 ELSE
704 l_v_message_text := igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_c_test_mode);
705 l_b_return_val := TRUE;
706 END IF;
707
708 fnd_message.set_name('IGS','IGS_FI_TEST_MODE');
709 fnd_message.set_token('TEST_MODE',l_v_message_text);
710 fnd_file.put_line(fnd_file.log,fnd_message.get);
711
712 IF NOT l_b_return_val THEN
713 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
714 fnd_message.set_token('PARAMETER','P_C_TEST_MODE');
715 fnd_file.put_line(fnd_file.log,fnd_message.get);
716 END IF;
717 RETURN l_b_return_val;
718 END validate_test_mode;
719
720
721 PROCEDURE validate_log_parameters(p_rec_input_param rec_input_param,
722 p_n_person_id igs_fi_parties_v.person_id%TYPE,
723 p_n_person_id_grp NUMBER,
724 p_b_ret_status OUT NOCOPY BOOLEAN) AS
725
726 /******************************************************************
727 Created By : Vinay Chappidi
728 Date Created By : 05-DEC-2002
729 Purpose : validates Input Parameters and Logs in the Log file
730 Known limitations,
731 enhancements,
732 remarks :
733 Change History
734 Who When What
735 vchappid 06-Jan-03 Bug# 2729948, message name changed from IGS_FI_PRS_OR_PRSIDGRP to IGS_FI_PRS_PRSIDGRP_NULL
736 whenever user don't provide either person id or person id group as parameter to the request
737 Additionally, Person Group Id parameter is being validated twice.
738 Removed duplicate Person Group ID validation.
739 ******************************************************************/
740
741 l_b_ret_status BOOLEAN := TRUE;
742 l_b_temp BOOLEAN := TRUE;
743 l_c_message_name fnd_new_messages.message_name%TYPE;
744
745 BEGIN
746
747 fnd_message.set_name('IGS','IGS_FI_PROCESS_PARAM');
748 fnd_file.put_line(fnd_file.log,fnd_message.get);
749 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
750 fnd_file.new_line(fnd_file.log);
751
752 -- Check if the Credit Class that is provided is a valid Credit Class
753 IF NOT validate_credit_class(p_rec_input_param.credit_class) THEN
754 l_b_temp := FALSE;
755 END IF;
756
757 -- Check if the Person Id Group provided is a valid value
758 IF (p_n_person_id_grp IS NOT NULL) THEN
759 IF NOT validate_person_grp(p_n_person_id_grp) THEN
760 l_b_temp := FALSE;
761 END IF;
762 END IF;
763
764 -- Check if the Person Id provided is a valid value
765 IF (p_n_person_id IS NOT NULL) THEN
766 IF NOT validate_person(p_n_person_id) THEN
767 l_b_temp := FALSE;
768 END IF;
769 END IF;
770
771 -- Check if the Credit Class that is provided is a valid Credit Class
772 IF p_rec_input_param.credit_type_id IS NOT NULL THEN
773 IF NOT validate_credit_type(p_rec_input_param.credit_type_id, p_rec_input_param.credit_class) THEN
774 l_b_temp := FALSE;
775 END IF;
776 END IF;
777
778 -- Check if the Term Calendar is a valid value
779 IF (p_rec_input_param.load_cal_type IS NOT NULL AND p_rec_input_param.load_cal_seq_num IS NOT NULL) THEN
780 IF NOT validate_term_cal_inst( p_c_load_cal_type => p_rec_input_param.load_cal_type,
781 p_n_load_seq_number => p_rec_input_param.load_cal_seq_num) THEN
782 l_b_temp := FALSE;
783 END IF;
784 END IF;
785
786 -- Check if the GL Date passed is a valid date in the Open or Future periods
787 -- Rec Installed will be checked in the generic procedure so need to check here
788 validate_gl_date(p_d_gl_date => p_rec_input_param.gl_date,
789 p_b_ret_status => l_b_ret_status,
790 p_c_message_name => l_c_message_name
791 );
792
793 -- Should handle the message
794 IF NOT l_b_ret_status THEN
795 l_b_temp := FALSE;
796 END IF;
797
798 -- Check if the Test Mode parameter passed is a valid value
799 IF NOT validate_test_mode(p_rec_input_param.test_mode) THEN
800 l_b_temp := FALSE;
801 END IF;
802
803 IF NOT l_b_temp THEN
804 p_b_ret_status := FALSE;
805 IF l_c_message_name IS NOT NULL THEN
806 IF l_c_message_name = 'IGS_FI_INVALID_GL_DATE' THEN
807 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
808 fnd_message.set_token('GL_DATE',p_rec_input_param.gl_date);
809 ELSE
810 fnd_message.set_name('IGS',l_c_message_name);
811 END IF;
812 END IF;
813 END IF;
814
815 fnd_file.new_line(fnd_file.log);
816 fnd_file.put_line(fnd_file.log,fnd_message.get);
817 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
818 fnd_file.new_line(fnd_file.log);
819
820 -- Check if the mandatory parameters are provided abort the process they are not provided
821 IF (p_rec_input_param.credit_class IS NULL OR p_rec_input_param.test_mode IS NULL OR p_rec_input_param.gl_date IS NULL) THEN
822 fnd_message.set_name('IGS','IGS_UC_NO_MANDATORY_PARAMS');
823 fnd_file.put_line(fnd_file.log, fnd_message.get);
824 p_b_ret_status := FALSE;
825 END IF;
826
827 -- Either Person Id or Person Id Group has to be provided both cannot be provided
828 IF (p_n_person_id IS NOT NULL AND p_n_person_id_grp IS NOT NULL) THEN
829 fnd_message.set_name('IGS','IGS_FI_PRS_OR_PRSIDGRP');
830 fnd_file.put_line(fnd_file.log, fnd_message.get);
831 p_b_ret_status := FALSE;
832 END IF;
833
834 -- Either Person Id or Person Id Group has to be provided both cannot be null
835 IF (p_n_person_id IS NULL AND p_n_person_id_grp IS NULL) THEN
836 fnd_message.set_name('IGS','IGS_FI_PRS_PRSIDGRP_NULL');
837 fnd_file.put_line(fnd_file.log, fnd_message.get);
838 p_b_ret_status := FALSE;
839 END IF;
840
841 -- If the Credit Class provided is ENRDEPOSIT then the term calendar should be mandatory.
842 IF (p_rec_input_param.credit_class = g_c_enrdeposit AND
843 (
844 (p_rec_input_param.load_cal_type IS NULL
845 OR
846 p_rec_input_param.load_cal_seq_num IS NULL
847 )
848 )) THEN
849 fnd_message.set_name('IGS','IGS_FI_DP_TERM_REQD');
850 fnd_file.put_line(fnd_file.log, fnd_message.get);
851 p_b_ret_status := FALSE;
852 END IF;
853 END validate_log_parameters;
854
855 PROCEDURE take_action(p_n_credit_id igs_fi_credits_all.credit_id%TYPE,
856 p_d_gl_date DATE,
857 p_c_action igs_lookup_values.lookup_code%TYPE,
858 p_c_credit_number OUT NOCOPY igs_fi_credits.credit_number%TYPE,
859 p_c_message_name OUT NOCOPY fnd_new_messages.message_name%TYPE)
860 AS
861 /******************************************************************
862 Created By : Vinay Chappidi
863 Date Created By : 05-DEC-2002
864 Purpose : Procedure to transfer or forfeit a deposit
865 Known limitations,
866 enhancements,
867 remarks :
868 Change History
869 Who When What
870 ******************************************************************/
871
872 l_b_ret_status BOOLEAN;
873 l_c_message_name fnd_new_messages.message_name%TYPE;
874 l_c_receipt_number igs_fi_credits.credit_number%TYPE;
875 BEGIN
876 IF p_c_action = g_c_transfer THEN
877 BEGIN
878 SAVEPOINT s_before_action;
879 igs_fi_deposits_prcss.transfer_deposit(p_n_credit_id=> p_n_credit_id,
880 p_d_gl_date => TRUNC(p_d_gl_date),
881 p_b_return_status => l_b_ret_status,
882 p_c_message_name => l_c_message_name,
883 p_c_receipt_number => l_c_receipt_number
884 );
885 IF NOT l_b_ret_status THEN
886 p_c_credit_number := NULL;
887 p_c_message_name := l_c_message_name;
888 ELSE
889 p_c_credit_number :=l_c_receipt_number;
890 p_c_message_name := NULL;
891 END IF;
892
893 EXCEPTION
894 WHEN OTHERS THEN
895 ROLLBACK TO s_before_action;
896 END;
897
898 ELSIF p_c_action = g_c_forfeit THEN
899 BEGIN
900 SAVEPOINT s_before_action;
901 igs_fi_deposits_prcss.forfeit_deposit( p_n_credit_id=> p_n_credit_id,
902 p_d_gl_date => TRUNC(p_d_gl_date),
903 p_b_return_status => l_b_ret_status,
904 p_c_message_name => l_c_message_name);
905 IF NOT l_b_ret_status THEN
906 p_c_message_name := l_c_message_name;
907 ELSE
908 p_c_message_name := NULL;
909 END IF;
910 p_c_credit_number := NULL;
911 EXCEPTION
912 WHEN OTHERS THEN
913 ROLLBACK TO s_before_action;
914 END;
915 END IF;
916 RETURN;
917 END take_action;
918
919 PROCEDURE prcss_oth_dpsts(p_n_person_id IN igs_fi_parties_v.person_id%TYPE, l_rec_input_param rec_input_param) AS
920 /******************************************************************
921 Created By : Vinay Chappidi
922 Date Created By : 05-DEC-2002
923 Purpose : Processess other deposits
924 Known limitations,
925 enhancements,
926 remarks :
927 Change History
928 Who When What
929 vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Assigned credit type id to a global variable as this global variable
930 value is used to fetch the credit type name and payment credit type name in the local procedure generate_log.
931 ******************************************************************/
932
933 CURSOR c_oth_dpsts(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
934 cp_c_cr_type_id igs_fi_cr_types.credit_type_id%TYPE,
935 cp_c_fee_cal_type igs_ca_inst.cal_type%TYPE,
936 cp_n_fee_seq_num igs_ca_inst.sequence_number%TYPE)
937 IS
938 SELECT crd.*
939 FROM igs_fi_credits_all crd,
940 igs_fi_cr_types crt
941 WHERE crd.party_id = cp_n_person_id AND
942 crd.credit_type_id = crt.credit_type_id AND
943 crt.credit_class = g_c_othdeposit AND
944 (
945 (crt.credit_type_id = cp_c_cr_type_id AND cp_c_cr_type_id IS NOT NULL)
946 OR
947 cp_c_cr_type_id IS NULL
948 ) AND
949 (
950 (crd.fee_cal_type = cp_c_fee_cal_type
951 AND
952 crd.fee_ci_sequence_number = cp_n_fee_seq_num
953 )
954 OR
955 (
956 (crd.fee_cal_type IS NULL AND cp_c_fee_cal_type IS NULL)
957 AND
958 (crd.fee_ci_sequence_number IS NULL AND cp_n_fee_seq_num IS NULL)
959 )
960 ) AND
961 crd.status = 'CLEARED';
962
963 l_c_message_name fnd_new_messages.message_name%TYPE;
964 l_c_credit_number igs_fi_credits.credit_number%TYPE;
965 l_b_othdep_exists BOOLEAN := FALSE;
966 BEGIN
967 FOR l_rec_c_oth_dpsts IN c_oth_dpsts(p_n_person_id,l_rec_input_param.credit_type_id,l_rec_input_param.fee_cal_type, l_rec_input_param.fee_cal_seq_num)
968 LOOP
969 IF NOT l_b_othdep_exists THEN
970 l_b_othdep_exists := TRUE;
971 END IF;
972
973
974 g_credit_type_id := l_rec_c_oth_dpsts.credit_type_id;
975
976 IF (l_rec_input_param.test_mode <> 'Y') THEN
977 take_action(l_rec_c_oth_dpsts.credit_id,
978 l_rec_input_param.gl_date,
979 g_c_transfer,
980 l_c_credit_number,
981 l_c_message_name);
982 END IF;
983 IF l_c_message_name IS NOT NULL THEN
984 generate_log(p_n_person_id,
985 l_rec_c_oth_dpsts.credit_number,
986 l_rec_c_oth_dpsts.amount,
987 g_c_hold,
988 g_null,
989 l_c_message_name,
990 g_null);
991 ELSE
992 generate_log(p_n_person_id,
993 l_rec_c_oth_dpsts.credit_number,
994 l_rec_c_oth_dpsts.amount,
995 g_c_transfer,
996 l_c_credit_number,
997 'IGS_FI_DP_TRANSFERRED',
998 g_null);
999 END IF;
1000 END LOOP;
1001
1002 IF NOT l_b_othdep_exists THEN
1003 generate_log(p_n_person_id,
1004 g_null,
1005 g_null,
1006 g_c_hold,
1007 g_null,
1008 'IGS_FI_DP_NO_RECORDS',
1009 g_null);
1010
1011 END IF;
1012 END prcss_oth_dpsts;
1013
1014 PROCEDURE prcss_enr_dpsts(p_n_person_id IN igs_fi_parties_v.person_id%TYPE, l_rec_input_param rec_input_param) AS
1015 /******************************************************************
1016 Created By : Vinay Chappidi
1017 Date Created By : 05-DEC-2002
1018 Purpose : Processess enrollment deposits
1019 Known limitations,
1020 enhancements,
1021 remarks :
1022 Change History
1023 Who When What
1024 vvutukur 10-Apr-2003 Enh#2831554.Internal Credits API Build. Assigned credit type id to a global variable as this global variable
1025 value is used to fetch the credit type name and payment credit type name in the local procedure generate_log.
1026 Modified cursor c_enr_dpsts to select credit type id also.
1027 vchappid 09-Jan-2003 Bug# 2730010, when a student has program attempt as Enrolled and no action is being
1028 taken on the Deposit then different message is logged in the log file.
1029 Additional issue: Local variables l_c_message_name, l_c_outcome have to be re-initialized
1030 before start of each Enrollment Credit
1031
1032 ******************************************************************/
1033
1034 -- Cursor for fetching all Deposit Transactions of Enrolment Deposit Credit Class
1035 -- with 'Cleared' status and matching credit type if provided
1036 CURSOR c_enr_dpsts(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
1037 cp_n_cr_type_id igs_fi_cr_types.credit_type_id%TYPE)
1038 IS
1039 SELECT crd.credit_id,crd.credit_type_id,
1040 TO_NUMBER(crd.source_transaction_ref) source_transaction_ref,
1041 crd.credit_number,
1042 crd.amount
1043 FROM igs_fi_credits crd,
1044 igs_fi_cr_types crt
1045 WHERE crd.party_id = cp_n_person_id AND
1046 crd.credit_type_id = crt.credit_type_id AND
1047 (
1048 (crt.credit_type_id = cp_n_cr_type_id AND cp_n_cr_type_id IS NOT NULL)
1049 OR
1050 cp_n_cr_type_id IS NULL
1051 ) AND
1052 crt.credit_class = g_c_enrdeposit AND
1053 crd.status = 'CLEARED'
1054 ORDER BY crd.credit_number;
1055
1056 -- For each Enrollment Deposit Transaction there wiil be an Admission Application Id Attached
1057 -- Get the Admission Application Number for the Admission Application Id
1058 -- With an Admission Application Id, a unique Admission Application Number can be identified for a
1059 -- person incontext
1060 CURSOR c_adm_appl_number(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
1061 cp_n_appl_id igs_ad_appl.application_id%TYPE)
1062 IS
1063 SELECT admission_appl_number, application_type
1064 FROM igs_ad_appl
1065 WHERE person_id = cp_n_person_id AND
1066 (application_id IS NOT NULL AND application_id = cp_n_appl_id);
1067 l_rec_adm_appl_number c_adm_appl_number%ROWTYPE;
1068
1069 -- Cursor for fetching the deposit level for an Admission Application Type
1070 CURSOR c_deposit_level( cp_c_application_type IN igs_ad_appl.application_type%TYPE)
1071 IS
1072 SELECT NVL(enroll_deposit_level, g_c_appl_dep_lvl) enroll_deposit_level
1073 FROM igs_ad_ss_appl_typ
1074 WHERE admission_application_type = cp_c_application_type AND
1075 closed_ind <> 'Y';
1076 l_rec_deposit_level c_deposit_level%ROWTYPE;
1077
1078
1079 -- Cursor for fetching matching Admission Application Instances for a Admission Application Number
1080 -- for the person incontext, load/term calendar as passed in to this request.
1081 -- If cp_c_criteria parameter value is 'APPL' then all matching instances for a Admission Application Number,
1082 -- the matching load calendar will be selected.
1083 -- If cp_c_criteria parameter value is 'ALL' then all matching instances excluding for a Admission Application Number,
1084 -- the matching load calendar will be selected.
1085 CURSOR c_appl_number (cp_n_person_id igs_fi_parties_v.person_id%TYPE,
1086 cp_c_adm_application_number NUMBER,
1087 cp_load_cal_type igs_ca_inst.cal_type%TYPE,
1088 cp_load_ci_seq_number igs_ca_inst.cal_type%TYPE,
1089 cp_c_criteria igs_lookup_values.lookup_code%TYPE)
1090 IS
1091 SELECT inst.course_cd course_cd,
1092 inst.crv_version_number version_number,
1093 ps.course_type course_type
1094 FROM igs_ad_ps_appl_inst inst,
1095 igs_ad_appl aa,
1096 igs_ps_ver ps,
1097 igs_ad_ofrdfrmt_stat df,
1098 igs_ad_ofr_resp_stat off
1099 WHERE inst.person_id = cp_n_person_id AND
1100 aa.person_id = inst.person_id AND
1101 aa.admission_appl_number = inst.admission_appl_number AND
1102 (
1103 (inst.admission_appl_number = cp_c_adm_application_number AND cp_c_criteria = 'APPL')
1104 OR
1105 (inst.admission_appl_number <> cp_c_adm_application_number AND cp_c_criteria = 'ALL')
1106 ) AND
1107 (inst.course_cd = ps.course_cd AND inst.crv_version_number = ps.version_number) AND
1108 inst.adm_offer_resp_status = off.adm_offer_resp_status AND
1109 inst.adm_offer_dfrmnt_status = df.adm_offer_dfrmnt_status AND
1110 (
1111 (off.s_adm_offer_resp_status = 'DEFERRAL' AND df.s_adm_offer_dfrmnt_status = 'CONFIRM' AND
1112 check_acad_load_adm_rel(cp_load_cal_type,
1113 cp_load_ci_seq_number,
1114 inst.def_acad_cal_type,
1115 inst.def_acad_ci_sequence_num,
1116 inst.deferred_adm_cal_type,
1117 inst.deferred_adm_ci_sequence_num) = 'Y'
1118 )
1119 OR
1120 (off.s_adm_offer_resp_status = 'ACCEPTED'
1121 AND
1122 check_acad_load_adm_rel(cp_load_cal_type,
1123 cp_load_ci_seq_number,
1124 aa.acad_cal_type,
1125 aa.acad_ci_sequence_number,
1126 inst.adm_cal_type,
1127 inst.adm_ci_sequence_number) = 'Y'
1128 )
1129 );
1130
1131 -- Cursor for fetching positive enrollment criteria when defined at program level
1132 CURSOR c_pec_program(cp_c_adm_appl_type igs_ad_deplvl_prg.admission_application_type%TYPE)
1133 IS
1134 SELECT program_code,
1135 version_number,
1136 NULL course_type
1137 FROM igs_ad_deplvl_prg
1138 WHERE admission_application_type = cp_c_adm_appl_type AND
1139 closed_ind='N';
1140
1141 -- Cursor for fetching positive enrollment criteria when defined at program type level
1142 CURSOR c_pec_program_type(cp_c_adm_appl_type igs_ad_deplvl_prgty.admission_application_type%TYPE)
1143 IS
1144 SELECT NULL program_code,
1145 NULL version_number,
1146 program_type
1147 FROM igs_ad_deplvl_prgty
1148 WHERE admission_application_type = cp_c_adm_appl_type AND
1149 closed_ind = 'N';
1150
1151 -- table for holding all initial program attempts details,
1152 l_tab_stdnt_sca_appl stdnt_sca_tab;
1153 l_tab_stdnt_sca_num_appl stdnt_sca_ver_num_tab;
1154 l_tab_std_sca_typ_appl stdnt_sca_typ_tab;
1155
1156 -- table for holding all Positive Enrollment program attempts
1157 l_tab_stdnt_sca_oth stdnt_sca_tab;
1158 l_tab_stdnt_sca_num_oth stdnt_sca_ver_num_tab;
1159 l_tab_std_sca_typ_oth stdnt_sca_typ_tab;
1160
1161 -- Deposits will be forfeited depending on this status if it is FALSE
1162 l_b_dpsts_exist BOOLEAN := FALSE;
1163
1164 -- local boolean variable
1165 l_b_status BOOLEAN;
1166
1167 l_c_action igs_lookup_values.lookup_code%TYPE;
1168 l_c_outcome igs_lookup_values.lookup_code%TYPE := g_c_forfeit;
1169 l_c_criteria igs_lookup_values.lookup_code%TYPE;
1170 l_c_sca_att_status igs_lookup_values.lookup_code%TYPE;
1171
1172 l_c_message_name fnd_new_messages.message_name%TYPE;
1173 l_c_credit_number igs_fi_credits.credit_number%TYPE;
1174
1175 BEGIN
1176
1177 -- Select all records in the Credits Table with a Credit Class of EnrDeposit and with status as Cleared
1178 -- When there are no receipt transactions for the person incontext then message will be logged in the Log
1179 -- file.
1180 FOR l_rec_enr_dpsts IN c_enr_dpsts(p_n_person_id,l_rec_input_param.credit_type_id)
1181 LOOP
1182
1183 -- Re-initialize local variables to the default values as these variables are used in a loop
1184 l_c_message_name := NULL;
1185 l_c_outcome := g_c_forfeit;
1186
1187 g_credit_type_id := l_rec_enr_dpsts.credit_type_id;
1188
1189
1190 IF NOT l_b_dpsts_exist THEN
1191 l_b_dpsts_exist := TRUE;
1192 END IF;
1193
1194 OPEN c_adm_appl_number(p_n_person_id, l_rec_enr_dpsts.source_transaction_ref);
1195 FETCH c_adm_appl_number INTO l_rec_adm_appl_number;
1196 CLOSE c_adm_appl_number;
1197
1198 -- Get the Enrolment Deposit Level for the Admission
1199 OPEN c_deposit_level(l_rec_adm_appl_number.application_type);
1200 FETCH c_deposit_level INTO l_rec_deposit_level;
1201 CLOSE c_deposit_level;
1202
1203 -- Get All the Course Codes for matching Admission Calendar Type and Admission Application Number
1204 -- Default Processing Criteria
1205 -- get all matching application instances load calendar to admission calendar via academic instances
1206 OPEN c_appl_number(p_n_person_id,
1207 l_rec_adm_appl_number.admission_appl_number,
1208 l_rec_input_param.load_cal_type,
1209 l_rec_input_param.load_cal_seq_num,
1210 'APPL');
1211 FETCH c_appl_number BULK COLLECT INTO l_tab_stdnt_sca_appl, l_tab_stdnt_sca_num_appl, l_tab_std_sca_typ_appl;
1212 CLOSE c_appl_number;
1213
1214 -- When there are no matching Admission Instance Records then show the message and process next deposit
1215 -- Else Continue with the Processing
1216 IF l_tab_stdnt_sca_appl.COUNT = 0 THEN
1217 generate_log(p_n_person_id,
1218 l_rec_enr_dpsts.credit_number,
1219 l_rec_enr_dpsts.amount,
1220 g_c_hold,
1221 g_null,
1222 'IGS_FI_DP_TERM_MISMATCH',
1223 g_null);
1224 ELSE
1225
1226 -- Verify if the student is enrolled in any of the above programs. If Enrolled then the action on the deposit
1227 -- will be to transfer the Deposit
1228 -- If the action is not to Transfer then the processing has to be carried forward depending on the Deposit level
1229 check_enrollment(p_n_person_id => p_n_person_id,
1230 p_tab_course_cd => l_tab_stdnt_sca_appl,
1231 p_tab_ver_num => l_tab_stdnt_sca_num_appl,
1232 p_tab_sca_typ => l_tab_std_sca_typ_appl,
1233 p_rec_input_param => l_rec_input_param,
1234 p_c_sca_status => g_c_sca_enrolled,
1235 p_c_dep_lvl => g_c_appl_dep_lvl,
1236 p_c_action => l_c_action,
1237 p_c_sca_att_status => l_c_sca_att_status);
1238
1239 -- Initially Out Come variable is initialized to Forfeit the Deposit Transaction.
1240 -- If the outcome is different from the initial value then that value will be assigned to this local variable
1241 -- If the out come is to Transfer the Deposit then the same is set to this variable and no other procesing
1242 -- will be done
1243 IF l_c_action IS NOT NULL THEN
1244 IF l_c_outcome <> l_c_action THEN
1245 l_c_outcome := l_c_action;
1246 END IF;
1247 END IF;
1248
1249 -- If out come variable is not set to Transfer then only proceed with the processing
1250 -- Positive Enrollment Criteria should be considered only when the Deposit Level defined for the
1251 -- Admission Application Type is other than Application 'APPL'
1252 -- For an Admission Application Type if the deposit level is not set then processing will be
1253 -- carried assuming deposit level as Application 'APPL'
1254 IF l_c_outcome <> g_c_transfer THEN
1255 IF (l_rec_deposit_level.enroll_deposit_level=g_c_prg_dep_lvl) THEN
1256
1257 -- If the Deposit is found to be associated at the Program Level then for the
1258 -- admission application type get all active programs
1259 OPEN c_pec_program(l_rec_adm_appl_number.application_type);
1260 FETCH c_pec_program BULK COLLECT INTO l_tab_stdnt_sca_oth, l_tab_stdnt_sca_num_oth, l_tab_std_sca_typ_oth;
1261 CLOSE c_pec_program;
1262
1263 -- For the above programs check if the student is Enrolled in any of these programs
1264 -- If Enrolled then out come is to transfer the deposit transaction
1265 check_enrollment(p_n_person_id => p_n_person_id,
1266 p_tab_course_cd => l_tab_stdnt_sca_oth,
1267 p_tab_ver_num => l_tab_stdnt_sca_num_oth,
1268 p_tab_sca_typ => l_tab_std_sca_typ_oth,
1269 p_rec_input_param => l_rec_input_param,
1270 p_c_sca_status => g_c_sca_enrolled,
1271 p_c_dep_lvl => g_c_prg_dep_lvl,
1272 p_c_action =>l_c_action,
1273 p_c_sca_att_status => l_c_sca_att_status);
1274
1275 ELSIF (l_rec_deposit_level.enroll_deposit_level=g_c_prgty_dep_lvl) THEN
1276
1277 -- If the Deposit is found to be associated at the Program Type Level then for the
1278 -- admission application type get all active programs types
1279 OPEN c_pec_program_type(l_rec_adm_appl_number.application_type);
1280 FETCH c_pec_program_type BULK COLLECT INTO l_tab_stdnt_sca_oth, l_tab_stdnt_sca_num_oth, l_tab_std_sca_typ_oth;
1281 CLOSE c_pec_program_type;
1282
1283 -- For the above program types check if the student is Enrolled in any of these programs types
1284 -- If Enrolled then out come is to transfer the deposit transaction
1285 check_enrollment(p_n_person_id => p_n_person_id,
1286 p_tab_course_cd => l_tab_stdnt_sca_oth,
1287 p_tab_ver_num => l_tab_stdnt_sca_num_oth,
1288 p_tab_sca_typ => l_tab_std_sca_typ_oth,
1289 p_rec_input_param => l_rec_input_param,
1290 p_c_sca_status => g_c_sca_enrolled,
1291 p_c_dep_lvl => g_c_prgty_dep_lvl,
1292 p_c_action =>l_c_action,
1293 p_c_sca_att_status => l_c_sca_att_status);
1294
1295 ELSIF (l_rec_deposit_level.enroll_deposit_level=g_c_all_dep_lvl) THEN
1296 -- If the Deposit is found to be associated at the ALL Level executing the admission application
1297 -- number derived from the admission application id attached to the deposit transaction,
1298 -- find all other matching program attempts and proceed with this processing
1299 OPEN c_appl_number(p_n_person_id,
1300 l_rec_adm_appl_number.admission_appl_number,
1301 l_rec_input_param.load_cal_type,
1302 l_rec_input_param.load_cal_seq_num,
1303 'ALL');
1304 FETCH c_appl_number BULK COLLECT INTO l_tab_stdnt_sca_oth, l_tab_stdnt_sca_num_oth,l_tab_std_sca_typ_oth;
1305 CLOSE c_appl_number;
1306
1307 -- For the above program attempts check if the student is Enrolled in any of these programs
1308 -- If Enrolled then out come is to transfer the deposit transaction
1309 check_enrollment(p_n_person_id => p_n_person_id,
1310 p_tab_course_cd => l_tab_stdnt_sca_oth,
1311 p_tab_ver_num => l_tab_stdnt_sca_num_oth,
1312 p_tab_sca_typ => l_tab_std_sca_typ_oth,
1313 p_rec_input_param => l_rec_input_param,
1314 p_c_sca_status => g_c_sca_enrolled,
1315 p_c_dep_lvl => g_c_all_dep_lvl,
1316 p_c_action =>l_c_action,
1317 p_c_sca_att_status => l_c_sca_att_status);
1318
1319 END IF;
1320
1321 IF l_c_action IS NOT NULL THEN
1322 IF l_c_outcome <> l_c_action THEN
1323 l_c_outcome := l_c_action;
1324 END IF;
1325 END IF;
1326 END IF;
1327
1328 -- If the out come of the deposit till this point is not to Transfer the Deposit then
1329 -- Proceed further to check if the student has program attempts in any other statuses
1330 IF l_c_outcome <> g_c_transfer THEN
1331
1332 -- For the program attempts attached for the application number derived form the application id
1333 -- attached to a deposit check the student program attempt status is in other than Enrolled Status
1334 -- If the student attempt is in other status then out come is to hold the deposit transaction
1335 check_enrollment(p_n_person_id => p_n_person_id,
1336 p_tab_course_cd => l_tab_stdnt_sca_appl,
1337 p_tab_ver_num => l_tab_stdnt_sca_num_appl,
1338 p_tab_sca_typ => l_tab_std_sca_typ_appl,
1339 p_rec_input_param => l_rec_input_param,
1340 p_c_sca_status => g_c_sca_other,
1341 p_c_dep_lvl => g_c_appl_dep_lvl,
1342 p_c_action =>l_c_action,
1343 p_c_sca_att_status => l_c_sca_att_status);
1344
1345 IF l_c_action IS NOT NULL THEN
1346 IF l_c_outcome <> l_c_action THEN
1347 l_c_outcome := l_c_action;
1348 END IF;
1349 END IF;
1350 END IF;
1351
1352 -- Process should be continued only when the outcome till this point is not set to either Hold or Transfer and
1353 -- the deposit level for the admission application type is other than 'Application'
1354 IF (l_c_outcome NOT IN (g_c_transfer, g_c_hold) AND l_rec_deposit_level.enroll_deposit_level <> g_c_appl_dep_lvl) THEN
1355
1356 -- In this case since we need to compare course codes and version numbers
1357 -- for all deposit levels ALL, APPL and PROGRAM, to the generic procedure
1358 -- pass p_c_dep_lvl as 'APPL' else pass as 'PROGRAM TYPE'
1359 IF l_rec_deposit_level.enroll_deposit_level <> g_c_prgty_dep_lvl THEN
1360 l_c_criteria := g_c_appl_dep_lvl;
1361 ELSE
1362 l_c_criteria := g_c_prgty_dep_lvl;
1363 END IF;
1364
1365 -- For the program attempts as derived from Positive Enrollment Criteria
1366 -- attached to a deposit check the student program attempt status is in other than Enrolled Status
1367 -- If the student attempt is in other status then out come is to hold the deposit transaction
1368 check_enrollment(p_n_person_id => p_n_person_id,
1369 p_tab_course_cd => l_tab_stdnt_sca_oth,
1370 p_tab_ver_num => l_tab_stdnt_sca_num_oth,
1371 p_tab_sca_typ => l_tab_std_sca_typ_oth,
1372 p_rec_input_param => l_rec_input_param,
1373 p_c_sca_status => g_c_sca_other,
1374 p_c_dep_lvl => l_c_criteria,
1375 p_c_action =>l_c_action,
1376 p_c_sca_att_status => l_c_sca_att_status);
1377
1378 IF l_c_action IS NOT NULL THEN
1379 IF l_c_outcome <> l_c_action THEN
1380 l_c_outcome := l_c_action;
1381 END IF;
1382 END IF;
1383 END IF;
1384
1385
1386 IF l_rec_input_param.test_mode <> 'Y' THEN
1387 IF l_c_outcome = g_c_transfer THEN
1388 l_c_action := g_c_transfer;
1389 ELSIF l_c_outcome = g_c_hold THEN
1390 l_c_action := g_c_hold;
1391 ELSIF l_c_outcome = g_c_forfeit THEN
1392 l_c_action := g_c_forfeit;
1393 END IF;
1394 take_action(l_rec_enr_dpsts.credit_id,
1395 l_rec_input_param.gl_date,
1396 l_c_action,
1397 l_c_credit_number,
1398 l_c_message_name);
1399 END IF;
1400
1401 IF l_c_outcome = g_c_transfer THEN
1402 l_c_message_name := NVL(l_c_message_name,'IGS_FI_DP_TRANSFERRED');
1403 ELSIF l_c_outcome = g_c_hold THEN
1404 IF l_c_sca_att_status = 'ENROLLED' THEN
1405 l_c_message_name := NVL(l_c_message_name,'IGS_FI_DP_SPA_SUA_NO_ACTION');
1406 ELSE
1407 l_c_message_name := NVL(l_c_message_name,'IGS_FI_DP_SPA_NO_ACTION');
1408 END IF;
1409 ELSIF l_c_outcome = g_c_forfeit THEN
1410 l_c_message_name := NVL(l_c_message_name,'IGS_FI_DP_FORFEITED');
1411 END IF;
1412
1413 generate_log(p_n_person_id,
1414 l_rec_enr_dpsts.credit_number,
1415 l_rec_enr_dpsts.amount,
1416 l_c_outcome,
1417 l_c_credit_number,
1418 l_c_message_name,
1419 l_c_sca_att_status);
1420 END IF;
1421 END LOOP; -- igs_fi_credits table End Loop
1422 -- If there are no deposits in Cleared Status then log a message
1423 -- Depending on the test mode parameter call to the generic procedure will be made
1424 -- Irrespective of the test mode parameter, messages have to be logged
1425 IF NOT l_b_dpsts_exist THEN
1426 generate_log(p_n_person_id,
1427 g_null,
1428 g_null,
1429 g_null,
1430 g_null,
1431 'IGS_FI_DP_NO_RECORDS',
1432 g_null);
1433 END IF;
1434
1435 RETURN;
1436 END prcss_enr_dpsts;
1437
1438
1439 PROCEDURE prc_stdnt_deposit (errbuf OUT NOCOPY VARCHAR2,
1440 retcode OUT NOCOPY NUMBER,
1441 p_c_credit_class VARCHAR2,
1442 p_n_person_id_grp NUMBER,
1443 p_n_person_id NUMBER,
1444 p_n_credit_type_id NUMBER,
1445 p_c_term_cal_inst VARCHAR2,
1446 p_d_gl_date VARCHAR2,
1447 p_c_test_mode VARCHAR2
1448 ) AS
1449 /******************************************************************
1450 Created By : Vinay Chappidi
1451 Date Created By : 05-DEC-2002
1452 Purpose : Main procedure called from Concurrent Manager
1453 Known limitations,
1454 enhancements,
1455 remarks :
1456 Change History
1457 Who When What
1458 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
1459 Added check for manage_accounts - call to chk_manage_account()
1460 vchappid 09-Jan-2003 Bug# 2729935, While Selecting Person Group member, start date and end date is checked.
1461 ******************************************************************/
1462
1463 CURSOR c_person(cp_n_person_id igs_pe_person.person_id%TYPE,
1464 cp_n_person_grp_id igs_pe_prsid_grp_mem_all.group_id%TYPE)
1465 IS
1466 SELECT person_id
1467 FROM igs_pe_prsid_grp_mem
1468 WHERE group_id = cp_n_person_grp_id AND
1469 TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date,SYSDATE)) AND TRUNC(NVL(end_date,SYSDATE)) AND
1470 cp_n_person_id IS NULL AND
1471 cp_n_person_grp_id IS NOT NULL
1472 UNION
1473 SELECT cp_n_person_id
1474 FROM DUAL
1475 WHERE cp_n_person_id IS NOT NULL AND
1476 cp_n_person_grp_id IS NULL;
1477
1478 l_rec_input_param rec_input_param;
1479
1480 l_c_load_cal_type igs_ca_inst.cal_type%TYPE;
1481 l_n_load_ci_seq_num igs_ca_inst.sequence_number%TYPE;
1482 l_c_fee_cal_type igs_ca_inst.cal_type%TYPE;
1483 l_n_fee_ci_seq_num igs_ca_inst.sequence_number%TYPE;
1484
1485 l_b_ret_status BOOLEAN;
1486
1487 l_c_message_name fnd_new_messages.message_name%TYPE := NULL;
1488
1489 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
1490
1491 BEGIN
1492 -- Initialize retcode to 0 and errbuf to NULL
1493 retcode :=0;
1494 errbuf := NULL;
1495
1496 -- Should Set the org_id
1497 igs_ge_gen_003.set_org_id(NULL);
1498
1499 -- Obtain the value of manage_accounts in the System Options form
1500 -- If it is null or 'OTHER', then this process is not available, so error out.
1501 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1502 p_v_message_name => l_c_message_name
1503 );
1504 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
1505 fnd_message.set_name('IGS',l_c_message_name);
1506 fnd_file.put_line(fnd_file.log,fnd_message.get());
1507 fnd_file.new_line(fnd_file.log);
1508 RAISE do_nothing;
1509 END IF;
1510
1511 IF (p_c_term_cal_inst IS NOT NULL) THEN
1512 l_c_load_cal_type := RTRIM(SUBSTR(p_c_term_cal_inst, 102, 10));
1513 l_n_load_ci_seq_num := TO_NUMBER(LTRIM(SUBSTR(p_c_term_cal_inst, 113,8)));
1514 END IF;
1515
1516 -- Initialize the record type variable with the values that are passed to this request
1517 l_rec_input_param.credit_class := p_c_credit_class;
1518 l_rec_input_param.credit_type_id := p_n_credit_type_id;
1519 l_rec_input_param.load_cal_type := l_c_load_cal_type ;
1520 l_rec_input_param.load_cal_seq_num := l_n_load_ci_seq_num ;
1521 l_rec_input_param.fee_cal_type := NULL ;
1522 l_rec_input_param.fee_cal_seq_num := NULL ;
1523 l_rec_input_param.gl_date := igs_ge_date.igsdate(p_canonical_date => p_d_gl_date);
1524 l_rec_input_param.test_mode := p_c_test_mode;
1525
1526 -- Validate and log input process parameters
1527 validate_log_parameters(p_rec_input_param => l_rec_input_param,
1528 p_n_person_id => p_n_person_id,
1529 p_n_person_id_grp => p_n_person_id_grp,
1530 p_b_ret_status => l_b_ret_status);
1531
1532 -- When the above procedure returns FALSE then raise Exception
1533 IF NOT l_b_ret_status THEN
1534 RAISE do_nothing;
1535 END IF;
1536
1537 BEGIN
1538 -- Initialize the record type input variables with the Fee Calendar Instance only when then term calendar is provided
1539 IF l_rec_input_param.load_cal_type IS NOT NULL AND l_rec_input_param.load_cal_seq_num IS NOT NULL THEN
1540 IF NOT (igs_fi_gen_001.finp_get_lfci_reln(p_cal_type => l_rec_input_param.load_cal_type,
1541 p_ci_sequence_number => l_rec_input_param.load_cal_seq_num,
1542 p_cal_category => 'LOAD',
1543 p_ret_cal_type => l_c_fee_cal_type,
1544 p_ret_ci_sequence_number => l_n_fee_ci_seq_num,
1545 p_message_name => l_c_message_name)) THEN
1546 fnd_message.set_name('IGS',l_c_message_name);
1547 fnd_file.put_line(fnd_file.log, fnd_message.get);
1548 RAISE do_nothing;
1549 END IF;
1550 END IF;
1551 EXCEPTION
1552 WHEN do_nothing THEN
1553 RAISE;
1554 END;
1555
1556 -- Initialize the input record variable with the fee calendar instance
1557 l_rec_input_param.fee_cal_type := l_c_fee_cal_type;
1558 l_rec_input_param.fee_cal_seq_num := l_n_fee_ci_seq_num;
1559
1560 -- Start the processing for the Person or Person Id Group depending on the input parameters
1561 FOR l_person_rec IN c_person(p_n_person_id,p_n_person_id_grp)
1562 LOOP
1563 IF p_c_credit_class = g_c_othdeposit THEN
1564 prcss_oth_dpsts(l_person_rec.person_id, l_rec_input_param);
1565 ELSIF p_c_credit_class = g_c_enrdeposit THEN
1566 prcss_enr_dpsts(l_person_rec.person_id, l_rec_input_param);
1567 END IF;
1568 END LOOP;
1569
1570 IF (l_rec_input_param.test_mode='N') THEN
1571 COMMIT;
1572 ELSE
1573 ROLLBACK;
1574 fnd_message.set_name('IGF', 'IGF_SP_TEST_MODE');
1575 fnd_file.put_line(fnd_file.log, fnd_message.get);
1576 END IF;
1577
1578
1579 EXCEPTION
1580 WHEN do_nothing THEN
1581 retcode :=2;
1582
1583 WHEN OTHERS THEN
1584 retcode := 2;
1585 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1586 errbuf := fnd_message.get;
1587 fnd_file.put_line(fnd_file.log,sqlerrm);
1588 igs_ge_msg_stack.conc_exception_hndl;
1589 END prc_stdnt_deposit;
1590
1591 END igs_fi_prc_stdnt_dpsts;
[Home] [Help]