1 PACKAGE BODY igs_fi_prc_disc_sua AS
2 /* $Header: IGSFI64B.pls 120.6 2006/06/19 09:36:28 gurprsin noship $ */
3 /*=======================================================================+
4 | Oracle India, IDC , Hyderabad. |
5 | |
6 +=======================================================================+
7 | |
8 | DESCRIPTION |
9 | PL/SQL body for package: IGS_FI_PRC_DISC_SUA
10 | |
11 | NOTES |
12 | This package accesses the charges table to get the unit records |
13 | where the student has a positive balance for i.e., the student |
14 | has not paid for the units he has enrolled into.Check has been |
15 | done for waiving also. If balance type parameter is specified in |
16 | the as a parameter, exclusion rules will also be checked |
17 | whether the charge is excluded for the specified invoice of |
18 | particular unit. If the parameter test run is passed as NO |
19 | to this package, then Drop/Discontinue the unit attempts and log |
20 | appropriate messages.If the parameter test run is YES, then does |
21 | not do the actual dropping of unit but gives the log file of all |
22 | units that will qualify for dropping/discontinuation for the |
23 | students. |
24 | |
25 | HISTORY |
26 | Who When What |
27 | gurprsin 14-Jun-2006 Bug 5123583, Added a cursor and code logic to |
28 | display calendar instance description in the |
29 | log file. |
30 | Modified the format and associated |
31 | the code logic in 'drop_disc_sua_non_payment' |
32 | to display the output in the log file. |
33 | sapanigr 01-Jun-2006 Bug#5251760. Review comments addressed |
34 | sapanigr 31-May-2006 Bug#5251760. Modified cursor cur_person_unit_outstdng_chrgs |
35 | in drop_disc_sua_non_payment as part of R12 |
36 | Repository Performance tuning for xBuild3. |
37 | sapanigr 13-Feb-2006 Bug#5018036. Modified cursor cur_person in |
38 | drop_disc_sua_non_payment and global cursor |
39 | cur_dcnt_reason_cd as part of R12 |
40 | Repository Performance tuning. |
41 | rmaddipa 22-jul-2004 Bug#3776195. Obsoleted get_lookup_meaning, |
42 | modified procedure drop_disc_sua_non_payment. |
43 | modified function validate_input_parameters |
44 | vvutukur 19-Sep-2003 Enh#3045007.Payment Plans Build.Modified the |
45 | procedure drop_disc_sua_non_payment. |
46 | shtaitko 21-Jul-2003 Bug# 3037137,Modified drop_disc_sua_non_payment|
47 | pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes to person id |
48 | group views - Modified drop_disc_sua_non_payment|
49 | pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build |
50 | Added check for manage_accounts - call to chk_manage_account()
51 | svenkata 27-Dec-2002 Added 4 new parameters with defauly value of 'N' to the call to the routine
52 igs_ss_enr_details.drop_selected_units as a part of technical impact.Bug#2686793
53 | shtatiko 03-sep-2002 Enh# 2562745 Modified validate_input_parameters|
54 | modified drop_disc_sua_non_payment. |
55 | vvutukur 23-Sep-2002 Enh#2564643.Modified drop_disc_sua_non_payment.|
56 | Removed DEFAULT clause from package body. |
57 | vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name |
58 | has been modified |
59 SYKRISHN 30_APR-2002 Bug 2348883- Modified cursor cur_fee_ci
60 to compare fee type ci status with the system status
61 and not with the user entered status - Using Fee Structure status
62 In function validate_input_parameters
63 | vvutukur 23-apr-2002 modified the log file to show meanings & |
64 | descriptions for person group,balance type|
65 | and discontinuation reason code , test run|
66 | schodava 30-Jan-2002 Enh # 2187247 |
67 | Modified PROCEDURE |
68 | drop_disc_sua_non_payment |
69 | vvutukur 20-dec-2001 Created the file for Unit Drop for Non |
70 | Payment Build: Bug # 2153205 |
71 *=======================================================================*/
72
73 g_fee_type_ci_status CONSTANT VARCHAR2(10) := 'ACTIVE';
74 g_lookup_type CONSTANT VARCHAR2(30) := 'IGS_FI_BALANCE_TYPE';
75 g_lookup_type_tr CONSTANT VARCHAR2(30) := 'VS_AS_YN';
76 g_exclude_type CONSTANT VARCHAR2(10) := 'CHARGE';
77 g_transaction_type CONSTANT VARCHAR2(30) := 'ASSESSMENT';
78 g_yes_ind CONSTANT VARCHAR2(1) := 'Y';
79 g_no_ind CONSTANT VARCHAR2(1) := 'N';
80
81
82 -- Cursor for validation of the Discontinuation Reason Code
83 CURSOR cur_dcnt_reason_cd(cp_dcnt_reason_cd IN VARCHAR2) IS
84 SELECT description
85 -- Bug#5018036: Replaced igs_en_dcnt_reasoncd_v by igs_en_dcnt_reasoncd_all
86 FROM igs_en_dcnt_reasoncd_all
87 WHERE discontinuation_reason_cd = cp_dcnt_reason_cd
88 AND closed_ind = g_no_ind
89 AND dcnt_unit_ind = g_yes_ind;
90
91 -- Fuction for validating all the Input Paramters
92 FUNCTION validate_input_parameters(
93 p_person_id IN igs_pe_person.person_id%TYPE,
94 p_person_id_grp IN igs_pe_prsid_grp_mem_v.group_id%TYPE,
95 p_fee_period IN VARCHAR2,
96 p_fee_cal_type IN igs_fi_inv_int.fee_cal_type%TYPE,
97 p_fee_ci_sequence_number IN igs_fi_inv_int.fee_ci_sequence_number%TYPE,
98 p_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
99 p_dcnt_reason_cd IN igs_en_dcnt_reasoncd_v.discontinuation_reason_cd%TYPE,
100 p_test_run IN VARCHAR2
101 ) RETURN BOOLEAN IS
102 /***************************************************
103 Created By : [email protected]
104 Date Created By : 02-Jan-2002
105 Purpose : Function for validation of the Input parameters
106 Known Limitations, enhancements or remarks : None
107 Change History
108 Who When Why
109 rmaddipa 22-jul-2004 Bug#3776195 Removed the code for logging of input parameters
110 shtatiko 03-sep-2002 Enh#2562745 Modified cur_balance_lkup_type to exclude balance types INSTALLMENT and OTHER.
111 vvutukur 23-Sep-2002 Enh#2564643.Removed DEFAULT clause for parameter p_test_run.
112 vchappid 13-Jun-2002 Bug#2411529, Incorrectly used message name has been modified
113 SYKRISHN 30_APR-2002 Bug 2348883- Modified cursor cur_fee_ci to compare fee type ci status with the system status
114 and not with the user entered status - Using Fee Structure status
115 agairola 02-Jan-2002 For modification of logic
116 (reverse chronological order - newest change first)
117 ****************************************************/
118
119 -- Cursor for validating the Person Id
120 CURSOR cur_person_id(cp_person_id IN igs_pe_person.person_id%TYPE) IS
121 SELECT party_number
122 FROM hz_parties
123 WHERE party_id = cp_person_id;
124
125 -- Cursor for validation of the Person Id Group
126 CURSOR cur_person_id_group(cp_person_id_grp IN NUMBER) IS
127 SELECT group_cd
128 FROM igs_pe_persid_group
129 WHERE group_id = cp_person_id_grp
130 AND closed_ind = g_no_ind;
131
132 -- Cursor for validation of the Fee Calendar Instance
133 CURSOR cur_fee_ci(cp_fee_cal_type IN VARCHAR2,
134 cp_fee_ci_sequence_number IN NUMBER) IS
135 SELECT 'x'
136 FROM igs_fi_f_typ_ca_inst fcc,
137 igs_fi_fee_str_stat fss
138 WHERE fcc.fee_type_ci_status = fss.fee_structure_status
139 AND fss.s_fee_structure_status = g_fee_type_ci_status
140 AND fcc.fee_cal_type=cp_fee_cal_type
141 AND fcc.fee_ci_sequence_number = cp_fee_ci_sequence_number;
142
143 -- Cursor for validation of the Balance Type
144 -- This cursor has been changed to exclude INSTALLMENT and OTHER balance types as part of Enh# 2562745.
145 CURSOR cur_balance_lkup_type(cp_balance_type IN VARCHAR2) IS
146 SELECT meaning
147 FROM igs_lookup_values
148 WHERE lookup_type = g_lookup_type
149 AND lookup_code = cp_balance_type
150 AND lookup_code NOT IN ('STANDARD', 'INSTALLMENT', 'OTHER')
151 AND sysdate BETWEEN NVL(start_date_active, sysdate) AND
152 NVL(end_date_active, sysdate)
153 AND enabled_flag='Y';
154
155 CURSOR cur_test_run(cp_test_run IN VARCHAR2) IS
156 SELECT meaning
157 FROM igs_lookups_view
158 WHERE lookup_type = g_lookup_type_tr
159 AND lookup_code = cp_test_run;
160
161 l_var VARCHAR2(1);
162 l_flag BOOLEAN;
163 rec_cur_person_id cur_person_id%ROWTYPE;
164 l_test_run igs_lookups_view.meaning%TYPE;
165 l_balance_type_meaning igs_lookups_view.meaning%TYPE;
166 l_dcnt_reason_cd_desc igs_en_dcnt_reasoncd_v.description%TYPE;
167 l_person_grp_cd igs_pe_persid_group.group_cd%TYPE;
168
169 BEGIN
170 l_flag := TRUE;
171
172 -- If the person id is not null, then validate if the person id is a valid
173 -- person id
174
175 IF(p_person_id IS NOT NULL) THEN
176 -- Check if the Person ID is valid.
177 OPEN cur_person_id(p_person_id);
178 FETCH cur_person_id INTO rec_cur_person_id;
179
180 IF cur_person_id%NOTFOUND THEN
181 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PERSON_ID');
182 fnd_file.put_line(fnd_file.log, FND_MESSAGE.GET);
183 l_flag := FALSE;
184 END IF;
185 CLOSE cur_person_id;
186 ELSE
187 fnd_file.new_line(fnd_file.log);
188 END IF;
189 -- If the Person Id Group is not null, then validate if the Person Id Group is invalid
190
191 IF p_person_id_grp IS NOT NULL THEN
192 -- Check if the Person ID Group is valid.
193 OPEN cur_person_id_group(p_person_id_grp );
194 FETCH cur_person_id_group INTO l_person_grp_cd;
195
196 IF cur_person_id_group%NOTFOUND THEN
197 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PARAMETER');
198 FND_MESSAGE.SET_TOKEN('PARAMETER','P_PERSON_ID_GRP');
199 fnd_file.put_line(fnd_file.log, fnd_message.get);
200 l_flag := FALSE;
201 END IF;
202 CLOSE cur_person_id_group;
203 ELSE
204 fnd_file.new_line(fnd_file.log);
205 END IF;
206
207
208 -- Validate if the Person Id and the Person ID Group both are not passed
209 -- simultaneously
210 IF ((p_person_id_grp IS NOT NULL) AND (p_person_id IS NOT NULL)) THEN
211 -- Return FALSE if both the parameters are NOT NULL.
212 FND_MESSAGE.SET_NAME('IGS','IGS_FI_PRS_OR_PRSIDGRP');
213 fnd_file.put_line(fnd_file.log, fnd_message.get);
214 l_flag := FALSE;
215 END IF;
216
217
218 -- Validate if the Fee Calendar Instance is valid
219 OPEN cur_fee_ci(p_fee_cal_type,
220 p_fee_ci_sequence_number);
221 FETCH cur_fee_ci INTO l_var;
222
223 IF cur_fee_ci%NOTFOUND THEN
224 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_FEE_CAL_TYPE');
225 fnd_file.put_line(fnd_file.log,fnd_message.get);
226 l_flag := FALSE;
227 END IF;
228 CLOSE cur_fee_ci;
229
230
231 -- Validate if the Balance Type is valid
232 OPEN cur_balance_lkup_type(p_balance_type);
233 FETCH cur_balance_lkup_type INTO l_balance_type_meaning;
234
235
236 IF p_balance_type IS NOT NULL AND cur_balance_lkup_type%NOTFOUND THEN
237 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PARAMETER');
238 FND_MESSAGE.SET_TOKEN('PARAMETER','P_BALANCE_TYPE');
239 fnd_file.put_line(fnd_file.log, fnd_message.get);
240 l_flag := FALSE;
241 END IF;
242 CLOSE cur_balance_lkup_type;
243
244
245 -- Validate if the Discontinuation Reason is valid
246 OPEN cur_dcnt_reason_cd(p_dcnt_reason_cd);
247 FETCH cur_dcnt_reason_cd INTO l_dcnt_reason_cd_desc;
248
249
250 IF cur_dcnt_reason_cd%NOTFOUND THEN
251 FND_MESSAGE.SET_NAME('IGS','IGS_EN_DISCONT_REAS_CD_CLOS');
252 fnd_file.put_line(fnd_file.log, fnd_message.get);
253 l_flag := FALSE;
254 END IF;
255 CLOSE cur_dcnt_reason_cd;
256
257 -- Validate if the Discontinuation Reason is valid
258 OPEN cur_test_run(p_test_run);
259 FETCH cur_test_run INTO l_test_run;
260
261 IF cur_test_run%NOTFOUND THEN
262 FND_MESSAGE.SET_NAME('IGS','IGS_FI_INVALID_PARAMETER');
263 FND_MESSAGE.SET_TOKEN('PARAMETER','P_TEST_RUN');
264 fnd_file.put_line(fnd_file.log, fnd_message.get);
265 l_flag := FALSE;
266 END IF;
267 CLOSE cur_test_run;
268 fnd_file.new_line(fnd_file.log);
269
270 RETURN l_flag;
271 END validate_input_parameters;
272
273 PROCEDURE drop_disc_sua_non_payment(
274 ERRBUF OUT NOCOPY VARCHAR2,
275 RETCODE OUT NOCOPY NUMBER,
276 p_person_id IN igs_pe_person.person_id%type,
277 p_person_id_grp IN igs_pe_prsid_grp_mem_v.group_id%type,
278 p_FEE_PERIOD IN VARCHAR2,
279 p_balance_type IN igs_fi_balance_rules.balance_name%type,
280 p_dcnt_reason_cd IN igs_en_dcnt_reasoncd_v.discontinuation_reason_cd%type,
281 p_test_run IN VARCHAR2
282 ) AS
283
284
285 /***************************************************
286 Created By : [email protected]
287 Date Created By : 02-Jan-2002
288 Purpose : Function for validation of the Input parameters
289 Known Limitations, enhancements or remarks : None
290 Change History
291 Who When What
292 gurprsin 14-Jun-2006 Bug 5123583, Added a cursor and code logic to
293 display calendar instance description in the log file.
294 Modified the format and associated
295 the code logic to display the output in the log file.
296
297 sapanigr 01-Jun-2006 Bug#5251760. Review comments addressed. Minor modifications.
298 sapanigr 31-May-2006 Bug#5251760. Removed unions in cur_person_unit_outstdng_chrgs to form
299 three separate cursors. Modified related code accordingly.
300 sapanigr 13-Feb-2006 Bug#5018036. Modified cursor cur_person. Query now uses igs_pe_person_base_v
301 instead of igs_pe_person.
302 rmaddipa 22-jul-2004 Bug#3776195 Replaced the call APP_EXCEPTION.RAISE_EXCEPTION with a
303 "retcode := 2;" statement and "RETURN;" to remove the 'Unhandled Exception'
304 in the log,
305 Added the code for logging the input parameters
306 stutta 27-Oct-2003 Build #3052438. Passed additional parameter p_sub_unit to funcion call
307 igs_en_gen_004.enrp_dropall_unit
308 vvutukur 19-Sep-2003 Enh#3045007.Payment Plans Build.Added validation to check if the Student on
309 an Active Payment plan. If the student is on an active payment plan, then
310 the unit is not considered for dropping/discontinuing.
311 shtatiko 21-JUL-2003 Bug# 3037137, Replaced call to igs_ss_en_wrappers.drop_selected_units
312 with igs_en_gen_004.enrp_dropall_unit
313 pathipat 23-Jun-2003 Bug: 3018104 - Impact of changes to person id group views
314 Modified cur_unit_outstdng_chrgs - replaced igs_pe_prsid_grp_mem_v
315 with igs_pe_prsid_grp_mem
316 pathipat 24-Apr-2003 Enh 2831569 - Commercial Receivables build
317 Added check for manage_accounts - call to chk_manage_account()
318 svenkata 27-Dec-2002 Added 4 new parameters with defauly value of 'N' to the call to the routine
319 igs_ss_enr_details.drop_selected_units as a part of technical impact.Bug#2686793
320 shtatiko 04-Oct-2002 Enh# 2562745, Added calls to FINP_GET_BALANCE_RULE.
321 Added a parameter in the invocation of check_exclusion_rules.
322 vvutukur 23-Sep-2002 Enh#2564643.Removed references to subaccount_id from cursor
323 cur_unit_outstdng_chrgs and its usage and also removed
324 DEFAULT clause from package body as a gscc fix.
325 schodava 30-Jan-2002 Enh # 2187247
326 SFCR021 - FCI-LCI Relation
327 agairola 02-Jan-2002 For modification of logic
328 (reverse chronological order - newest change first)
329 ****************************************************/
330
331 -- Cursor for getting the details for the charges for the person which have the Amount Due as greater than zero
332 -- and which have been created due to the Fee Assessment Run and have the Unit Section details
333 -- and the records do not have waiver details
334
335 CURSOR cur_person_chrgs(cp_n_person_id IN hz_parties.party_id%type,
336 cp_v_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
337 cp_v_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
338 cp_n_fee_ci_sequence_number IN igs_fi_inv_int_all.fee_ci_sequence_number%TYPE
339 ) IS
340 SELECT hd.person_id person_id,
341 ln.uoo_id uoo_id,
342 hd.course_cd,
343 hd.invoice_creation_date invoice_creation_date,
344 hd.fee_type,
345 hd.fee_cal_type,
346 hd.fee_ci_sequence_number,
347 hd.invoice_id,
348 hd.invoice_amount_due
349 FROM igs_fi_inv_int_all hd,
350 igs_fi_invln_int_v ln
351 WHERE hd.invoice_id = ln.invoice_id
352 AND hd.transaction_type = g_transaction_type
353 AND hd.person_id = cp_n_person_id
354 AND hd.invoice_amount_due > 0
355 AND hd.fee_cal_type = cp_v_fee_cal_type
356 AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
357 AND ln.uoo_id IS NOT NULL
358 AND hd.course_cd IS NOT NULL
359 AND NOT EXISTS (SELECT 'X'
360 FROM igs_fi_inv_wav_det wd
361 WHERE wd.invoice_id = hd.invoice_id
362 AND wd.balance_type = cp_v_balance_type
363 AND (wd.end_dt is not null AND sysdate
364 BETWEEN wd.start_dt and wd.end_dt))
365 ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
366
367 CURSOR cur_person_group_chrgs(cp_n_person_id_grp IN igs_pe_prsid_grp_mem_all.group_id%TYPE,
368 cp_v_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
369 cp_v_fee_cal_type IN igs_fi_inv_int_all.fee_cal_type%TYPE,
370 cp_n_fee_ci_sequence_number IN igs_fi_inv_int_all.fee_ci_sequence_number%TYPE
371 ) IS
372 SELECT hd.person_id person_id,
373 ln.uoo_id uoo_id,
374 hd.course_cd,
375 hd.invoice_creation_date invoice_creation_date,
376 hd.fee_type,
377 hd.fee_cal_type,
378 hd.fee_ci_sequence_number,
379 hd.invoice_id,
380 hd.invoice_amount_due
381 FROM igs_fi_inv_int_all hd,
382 igs_fi_invln_int_v ln
383 WHERE hd.invoice_id = ln.invoice_id
384 AND hd.transaction_type = g_transaction_type
385 AND hd.person_id IN (SELECT person_id
386 FROM igs_pe_prsid_grp_mem
387 WHERE group_id = cp_n_person_id_grp
388 AND ((end_date IS NULL) OR (TRUNC(end_date) >= TRUNC(SYSDATE))))
389 AND hd.invoice_amount_due > 0
390 AND hd.fee_cal_type = cp_v_fee_cal_type
391 AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
392 AND ln.uoo_id IS NOT NULL
393 AND hd.course_cd IS NOT NULL
394 AND NOT EXISTS (SELECT 'X'
395 FROM igs_fi_inv_wav_det wd
396 WHERE wd.invoice_id = hd.invoice_id
397 AND wd.balance_type = cp_v_balance_type
398 AND (wd.end_dt is not null AND sysdate
399 BETWEEN wd.start_dt and wd.end_dt))
400 ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
401
402
403 CURSOR cur_all_person_chrgs(cp_v_balance_type IN igs_fi_balance_rules.balance_name%TYPE,
404 cp_v_fee_cal_type IN Igs_fi_inv_int_all.fee_cal_type%TYPE,
405 cp_n_fee_ci_sequence_number IN Igs_fi_inv_int_all.fee_ci_sequence_number%TYPE
406 ) IS
407 SELECT hd.person_id person_id,
408 ln.uoo_id uoo_id,
409 hd.course_cd,
410 hd.invoice_creation_date invoice_creation_date,
411 hd.fee_type,
412 hd.fee_cal_type,
413 hd.fee_ci_sequence_number,
414 hd.invoice_id,
415 hd.invoice_amount_due
416 FROM igs_fi_inv_int_all hd,
417 igs_fi_invln_int_v ln
418 WHERE hd.invoice_id = ln.invoice_id
419 AND hd.transaction_type = g_transaction_type
420 AND hd.invoice_amount_due > 0
421 AND hd.fee_cal_type = cp_v_fee_cal_type
422 AND hd.fee_ci_sequence_number = cp_n_fee_ci_sequence_number
423 AND ln.uoo_id IS NOT NULL
424 AND hd.course_cd IS NOT NULL
425 AND NOT EXISTS (SELECT 'X'
426 FROM igs_fi_inv_wav_det wd
427 WHERE wd.invoice_id = hd.invoice_id
428 AND wd.balance_type = cp_v_balance_type
429 AND (wd.end_dt is not null AND sysdate
430 BETWEEN wd.start_dt and wd.end_dt))
431 ORDER BY person_id,course_cd, uoo_id,invoice_creation_date;
432
433 --Cursor to fetch the person details
434 CURSOR cur_person(cp_person_id IN igs_pe_person_base_v.person_id%TYPE) IS
435 SELECT person_number,
436 first_name || ' ' || last_name person_name
437 FROM igs_pe_person_base_v
438 WHERE person_id = cp_person_id;
439
440 -- Enh # 2187247
441 -- Modified the cursor for fetching the unit attempt status
442 -- Cursor for fetching the Enrolment details
443 CURSOR cur_enrl_dt(cp_person_id IN igs_en_su_attempt.person_id%TYPE,
444 cp_course_cd IN igs_en_su_attempt.course_cd%TYPE,
445 cp_uoo_id IN igs_en_su_attempt.uoo_id%TYPE) IS
446 SELECT enrolled_dt,
447 unit_cd,
448 version_number,
449 cal_type,
450 ci_sequence_number,
451 location_cd,
452 unit_class,
453 uoo_id,
454 unit_attempt_status
455 FROM igs_en_su_attempt
456 WHERE person_id = cp_person_id
457 AND course_cd = cp_course_cd
458 AND uoo_id = cp_uoo_id;
459
460 -- Cursor to fetch the Course details
461 -- Enh # 2187247
462 -- Modified the cursor
463 -- Removed the subquery as it was redundant
464 CURSOR cur_course_version(p_person_id IN igs_pe_person.person_id%TYPE,
465 p_course_cd IN igs_fi_inv_int.course_cd%TYPE
466 ) IS
467 SELECT ps.course_cd, ps.version_number
468 FROM igs_en_stdnt_ps_att ps
469 WHERE ps.person_id = p_person_id
470 AND ps.course_cd = p_course_cd;
471
472 -- Bug 5123583, Created the Cursor to fetch Teaching calendar description
473 CURSOR cur_get_cal_inst_desc(p_v_cal_type IN igs_ca_inst_all.cal_type%TYPE,
474 p_n_sequence_number IN igs_ca_inst_all.sequence_number%TYPE
475 ) IS
476 SELECT description
477 FROM igs_ca_inst_all ca
478 WHERE ca.cal_type = p_v_cal_type
479 AND ca.sequence_number = p_n_sequence_number;
480
481 -- Declaration of local variables
482
483 l_uoo_id igs_fi_invln_int.uoo_id%TYPE;
484 l_person_id igs_pe_person.person_id%TYPE;
485 l_course_cd igs_fi_inv_int.course_cd%TYPE;
486 l_cnt NUMBER;
487 l_str VARCHAR2(4000);
488 l_message VARCHAR2(2000);
489 l_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE;
490 l_fee_ci_sequence_number igs_fi_inv_int.fee_ci_sequence_number%TYPE;
491 l_ld_cal_type igs_ca_inst.cal_type%TYPE;
492 l_ld_ci_sequence_number Igs_ca_inst.sequence_number%TYPE;
493 l_person_number igs_pe_person.person_number%TYPE;
494 l_enrolled_dt igs_en_su_attempt.enrolled_dt%TYPE;
495 l_person_name igs_pe_person.full_name%TYPE;
496 l_balance_rule_id igs_fi_balance_rules.balance_rule_id%TYPE;
497 l_last_conversion_date igs_fi_balance_rules.last_conversion_date%TYPE;
498 l_version_number igs_fi_balance_rules.version_number%TYPE;
499
500 l_flag BOOLEAN := TRUE;
501 l_enr_dtls cur_enrl_dt%ROWTYPE;
502 l_str_enrl VARCHAR2(4000);
503
504 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE := NULL;
505 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
506
507 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
508 l_v_act_plan_name igs_fi_pp_std_attrs.payment_plan_name%TYPE;
509 l_n_pp_person_id igs_fi_parties_v.person_id%TYPE;
510 e_skip_record EXCEPTION;
511 l_b_proceed BOOLEAN;
512
513 l_v_dcnt_reason_cd_desc igs_en_dcnt_reasoncd_v.description%TYPE;
514
515 --Bug 5123583, variable declared to fetch calendar description from cur_get_cal_inst_desc.
516 l_v_cal_inst_desc igs_ca_inst_all.description%TYPE := NULL;
517
518
519 TYPE tab_chrgs_rec IS TABLE OF cur_person_chrgs%ROWTYPE INDEX BY BINARY_INTEGER;
520 v_tab_chrgs_rec tab_chrgs_rec;
521 rec_chrgs cur_person_chrgs%ROWTYPE;
522
523 BEGIN
524
525 -- Set the Org Id
526 igs_ge_gen_003.set_org_id(NULL);
527
528 retcode:= 0;
529 -- logging input parameters
530 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
531 fnd_file.put_line(fnd_file.log,fnd_message.get);
532
533 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')|| ': ');
534 fnd_file.put_line(fnd_file.log,igs_fi_gen_008.get_party_number(p_person_id));
535
536 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP')|| ': ');
537 fnd_file.put_line(fnd_file.log,igs_fi_gen_005.finp_get_prsid_grp_code(p_person_id_grp));
538
539 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_PERIOD')|| ': ');
540 fnd_file.put_line(fnd_file.log,p_fee_period);
541
542 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','BALANCE_TYPE')|| ': ');
543 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning(g_lookup_type,p_balance_type));
544
545 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','DCNT_REASON_CD')|| ': ');
546 OPEN cur_dcnt_reason_cd(p_dcnt_reason_cd);
547 FETCH cur_dcnt_reason_cd INTO l_v_dcnt_reason_cd_desc;
548 CLOSE cur_dcnt_reason_cd;
549 fnd_file.put_line(fnd_file.log,l_v_dcnt_reason_cd_desc);
550
551 fnd_file.put(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TEST_MODE')|| ': ');
552 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_test_run));
553 -- end of logging the input parameters
554
555
556 -- Obtain the value of manage_accounts in the System Options form
557 -- If it is null or 'OTHER', then this process is not available, so error out.
558 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
559 p_v_message_name => l_v_message_name
560 );
561 IF (l_v_manage_acc = 'OTHER') OR (l_v_manage_acc IS NULL) THEN
562 fnd_message.set_name('IGS',l_v_message_name);
563 fnd_file.put_line(fnd_file.log,fnd_message.get());
564 fnd_file.new_line(fnd_file.log);
565 retcode := 2;
566 RETURN;
567 END IF;
568
569 -- Create the savepoint for rollback
570 SAVEPOINT s_disc_drop_units;
571
572 -- The person number and person name should be fetched for logging into
573 -- the log file of the concurrent manager.
574 OPEN cur_person(p_person_id);
575 FETCH cur_person INTO l_person_number,l_person_name;
576 CLOSE cur_person;
577
578 -- The fee period parameter should be split to extract the
579 -- Fee Calendar Type and Fee Calendar Instance
580 IF p_fee_period IS NOT NULL THEN
581 l_fee_cal_type := RTRIM(SUBSTR(p_fee_period, 102, 10));
582 l_fee_ci_sequence_number := TO_NUMBER(LTRIM(SUBSTR(p_fee_period, 113,8)));
583 END IF;
584
585 -- Enh # 2187247
586 -- Invoke the function to derive the Load Calendar Intance
587 -- related to the fee calendar instance.
588 -- There can be only one such ACTIVE load calendar instance
589
590 IF igs_fi_gen_001.finp_get_lfci_reln(
591 p_cal_type => l_fee_cal_type,
592 p_ci_sequence_number => l_fee_ci_sequence_number,
593 p_cal_category => 'FEE',
594 p_ret_cal_type => l_ld_cal_type,
595 p_ret_ci_sequence_number=> l_ld_ci_sequence_number,
596 p_message_name => l_message
597 ) = FALSE THEN
598 FND_MESSAGE.SET_NAME('IGS', l_message);
599 fnd_file.put_line(fnd_file.log,fnd_message.get);
600 retcode:=2;
601 RETURN;
602 END IF;
603
604 -- Call the procedure for validation of the input parameters
605 IF NOT igs_fi_prc_disc_sua.validate_input_parameters(
606 p_person_id => p_person_id,
607 p_person_id_grp => p_person_id_grp,
608 p_fee_period => p_fee_period,
609 p_fee_cal_type => l_fee_cal_type,
610 p_fee_ci_sequence_number => l_fee_ci_sequence_number,
611 p_balance_type => p_balance_type,
612 p_dcnt_reason_cd => p_dcnt_reason_cd,
613 p_test_run => p_test_run) THEN
614
615 -- If the validation procedure returns false, then raise the exception as some
616 -- of the validations have failed and have been logged in the log file of the
617 -- Concurrent Manager
618 retcode:=2;
619 RETURN;
620 END IF;
621
622 -- Initialize the local variables
623 l_uoo_id :=0;
624 l_person_id := 0;
625 l_course_cd := NULL;
626 l_cnt := 0;
627 l_balance_rule_id := 0;
628 l_last_conversion_date := null;
629 l_version_number := 0;
630
631 --Added as part of Enh Bug# 2562745. Get the balance_rule_id by calling finp_get_balance_rule and
632 --use the value in the call to check_exclusion_rules.
633
634 IF p_balance_type IS NOT NULL THEN
635 IF p_balance_type = 'HOLDS' THEN
636 IGS_FI_GEN_007.FINP_GET_BALANCE_RULE ( p_v_balance_type => 'HOLDS',
637 p_v_action => 'ACTIVE',
638 p_n_balance_rule_id => l_balance_rule_id,
639 p_d_last_conversion_date => l_last_conversion_date,
640 p_n_version_number => l_version_number );
641 IF l_version_number = 0 THEN
642 fnd_file.new_line(fnd_file.log);
643 FND_MESSAGE.SET_NAME('IGS','IGS_FI_CANNOT_CRT_TXN');
644 fnd_file.put_line(fnd_file.log, fnd_message.get);
645 retcode:=2;
646 RETURN;
647 END IF;
648 ELSIF p_balance_type = 'FEE' THEN
649 IGS_FI_GEN_007.FINP_GET_BALANCE_RULE ( p_v_balance_type => 'FEE',
650 p_v_action => 'MAX',
651 p_n_balance_rule_id => l_balance_rule_id,
652 p_d_last_conversion_date => l_last_conversion_date,
653 p_n_version_number => l_version_number );
654 END IF;
655 END IF;
656
657 -- Displaying appropriate message in the log file based on the
658 -- Test Run Parameter
659 IF p_test_run = g_yes_ind THEN
660 FND_MESSAGE.SET_NAME('IGS','IGS_FI_WILL_DROP_UNITS');
661 fnd_file.put_line(fnd_file.log,fnd_message.get);
662 ELSE
663 FND_MESSAGE.SET_NAME('IGS','IGS_FI_HAVE_DROP_UNITS');
664 fnd_file.put_line(fnd_file.log,fnd_message.get );
665 END IF;
666
667 --Bug 5123583, Removed the logic to display header like person number, enrollement date etc. used in the log file.
668
669 -- Open person cursors to loop across the persons in context */
670
671 IF p_person_id_grp IS NOT NULL AND p_person_id IS NULL THEN
672 OPEN cur_person_group_chrgs(p_person_id_grp, p_balance_type, l_fee_cal_type, l_fee_ci_sequence_number);
673 FETCH cur_person_group_chrgs BULK COLLECT INTO v_tab_chrgs_rec;
674 CLOSE cur_person_group_chrgs;
675 ELSIF p_person_id IS NOT NULL AND p_person_id_grp IS NULL THEN
676 OPEN cur_person_chrgs(p_person_id, p_balance_type, l_fee_cal_type, l_fee_ci_sequence_number);
677 FETCH cur_person_chrgs BULK COLLECT INTO v_tab_chrgs_rec;
678 CLOSE cur_person_chrgs;
679 ELSE
680 OPEN cur_all_person_chrgs(p_balance_type, l_fee_cal_type, l_fee_ci_sequence_number);
681 FETCH cur_all_person_chrgs BULK COLLECT INTO v_tab_chrgs_rec;
682 CLOSE cur_all_person_chrgs;
683 END IF;
684
685 IF v_tab_chrgs_rec.COUNT > 0 THEN
686 -- Loop across all charges identified
687 FOR l_n_cntr IN v_tab_chrgs_rec.FIRST..v_tab_chrgs_rec.LAST
688 LOOP
689 IF v_tab_chrgs_rec.EXISTS(l_n_cntr) THEN
690 rec_chrgs := v_tab_chrgs_rec(l_n_cntr);
691
692 BEGIN
693
694 --If the person being processed in not checked for active payment plan existence earlier...
695 IF l_n_pp_person_id IS NULL OR rec_chrgs.person_id <> l_n_pp_person_id THEN
696
697 l_b_proceed := TRUE;
698
699 --Capture the person id in a local variable.
700 l_n_pp_person_id := rec_chrgs.person_id;
701
702 --Get the Student's Active Payment Plan Details.
703 igs_fi_gen_008.get_plan_details(p_n_person_id => rec_chrgs.person_id,
704 p_n_act_plan_id => l_n_act_plan_id,
705 p_v_act_plan_name => l_v_act_plan_name
706 );
707 --If an active payment plan exists for the person being processed, skip the record and process for
708 --the next person.
709 IF l_n_act_plan_id IS NOT NULL THEN
710 l_b_proceed := FALSE;
711 OPEN cur_person(rec_chrgs.person_id);
712 FETCH cur_person INTO l_person_number,l_person_name;
713 CLOSE cur_person;
714 --Bug 5123583, Removed the RPAD used earlier to display 6 spaces.
715 l_str := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||l_person_number;
716 RAISE e_skip_record;
717 END IF;
718 END IF;
719
720 IF l_b_proceed THEN
721 l_flag := TRUE;
722
723 -- If the Person Id in the local variable is different from the Person Id of the
724 -- charge record being processed, then reset the Course Code and the Uoo_Id variables
725 IF l_person_id <> rec_chrgs.person_id THEN
726 l_course_cd := NULL;
727 l_uoo_id := 0;
728 END IF;
729
730 -- Reset the Uoo_Id variable if the course code is null or the course code is different
731 -- from the local variable value
732 IF ((l_course_cd IS NULL) OR (l_course_cd <> rec_chrgs.course_cd)) THEN
733 l_uoo_id := 0;
734 END IF;
735
736 -- If the Uoo_Id in the local variable is not the same for the Uoo_Id of the record
737 -- then the further processing needs to be done
738 IF (l_uoo_id <> rec_chrgs.uoo_id) THEN
739
740 -- Enh #2187247
741 -- Added a validation to process a UOO ID only if the UOO ID
742 -- is of 'ENROLLED' or 'INVALID' status
743 OPEN cur_enrl_dt(rec_chrgs.person_id,
744 rec_chrgs.course_cd,
745 rec_chrgs.uoo_id);
746 FETCH cur_enrl_dt INTO l_enr_dtls;
747 CLOSE cur_enrl_dt;
748
749 IF l_enr_dtls.unit_attempt_status NOT IN ('ENROLLED','INVALID') THEN
750 l_flag := FALSE;
751 END IF;
752
753
754 -- If the balance type passed is not null, then
755
756 IF p_balance_type IS NOT NULL THEN
757 -- Check if the balance rules for exclusion are applicable
758 -- Added balance_rule_id parameter to the call as part of Enh Bug# 2562745
759 IF igs_fi_prc_balances.check_exclusion_rules( p_balance_type => p_balance_type,
760 p_balance_date => rec_chrgs.invoice_creation_date,
761 p_source_type => g_exclude_type,
762 p_source_id => rec_chrgs.invoice_id,
763 p_message_name => l_message,
764 p_balance_rule_id => l_balance_rule_id ) THEN
765
766 -- If the record is excluded, then set the Local flag variable to FALSE
767 l_flag := FALSE;
768 END IF;
769 END IF;
770
771 -- If the flag is set to TRUE i.e. the record has not been excluded, then
772 -- the process for dropping of the unit attempt has to be called.
773 IF l_flag THEN
774 l_uoo_id := rec_chrgs.uoo_id;
775
776
777 -- Enh # 2187247 Uncommenting the call to the Enrolments API
778 -- as a part of Build of SFCR021.
779 -- The following code has been commented because the Load Calendar is not available
780 -- After the SFCR021 is built, this code should be uncommented and the functionality for
781 -- dropping the units should be tested for the dropping of the units
782 -- The API which provides the Load Calendar based on the Fee Calendar is not put in the code
783 -- as this will be available only after SFCR021 build
784
785 IF p_test_run <> 'Y' THEN
786 FOR rec_cur_course_version IN cur_course_version(rec_chrgs.person_id,
787 rec_chrgs.course_cd) LOOP
788
789 -- Enh # 2187247
790 -- Changed this call from positional notation to named notation
791 -- Added a new parameter p_admin_unit_status
792
793 -- Bug# 3037137, Replaced call to igs_ss_en_wrappers.drop_selected_units with igs_en_gen_004.enrp_dropall_unit
794 -- to avoid unnecessary validations carried out in drop_selected_units.
795 -- Build # 3052438, Added parameter p_sub_unit to function call.
796 igs_en_gen_004.enrp_dropall_unit(
797 p_person_id => rec_chrgs.person_id,
798 p_cal_type => l_ld_cal_type,
799 p_ci_sequence_number => l_ld_ci_sequence_number,
800 p_dcnt_reason_cd => p_dcnt_reason_cd,
801 p_admin_unit_sta => NULL,
802 p_effective_date => SYSDATE,
803 p_program_cd => rec_cur_course_version.course_cd,
804 p_uoo_id => TO_CHAR(rec_chrgs.uoo_id),
805 p_sub_unit => 'N'
806 );
807
808 END LOOP;
809 END IF;
810
811 -- Log the details of unit drop in the log file of the concurrent manager
812 -- Enh # 2187247
813 -- Changed the IF condition below from p_failed_uoo_ids to l_return_status
814 -- Bug# 3037137. Removed check for l_return_status as it is no longer used.
815 OPEN cur_person(rec_chrgs.person_id);
816 FETCH cur_person INTO l_person_number,
817 l_person_name;
818 CLOSE cur_person;
819
820 -- Prepare the string for logging of the details in the log file.
821 --Bug# 5123583, Changed the separtor used to display unit details from '-' to '/'.
822 --Fetching the teaching calendar instance description.
823 OPEN cur_get_cal_inst_desc(l_enr_dtls.cal_type, l_enr_dtls.ci_sequence_number);
824 FETCH cur_get_cal_inst_desc into l_v_cal_inst_desc;
825 CLOSE cur_get_cal_inst_desc;
826
827 l_str_enrl := l_enr_dtls.unit_cd||'/'||To_Char(l_enr_dtls.version_number)||'/'||
828 l_v_cal_inst_desc||'/'||
829 l_enr_dtls.location_cd||'/'||l_enr_dtls.unit_class;
830
831 l_v_cal_inst_desc := NULL;
832
833 l_cnt := l_cnt + 1;
834 --Bug 5123583, Removed the code to display all data through l_str local variable.
835 --Changed the tabular form of output to show records by person number.
836
837 --Printing output in the log file.
838 fnd_file.put_line(fnd_file.log, igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||l_person_number);
839
840 fnd_message.set_name('IGS', 'IGS_FI_PERSON_NAME');
841 fnd_file.put_line(fnd_file.log, fnd_message.get || ': ' || l_person_name);
842
843 fnd_file.put_line(fnd_file.log,igs_fi_gen_gl.get_lkp_meaning('IGS_FI_TRIGGER_GROUP','COURSE')|| ': ' || rec_chrgs.course_cd);
844
845 fnd_message.set_name('IGS', 'IGS_FI_UNIT_DTLS');
846 fnd_file.put_line(fnd_file.log, fnd_message.get || ': ' || l_str_enrl);
847
848 l_str_enrl := NULL;
849
850 fnd_message.set_name('IGS', 'IGS_FI_ENR_DATE');
851 fnd_file.put_line(fnd_file.log, fnd_message.get || ': ' || TRUNC(l_enr_dtls.enrolled_dt));
852
853 --for separating one person details with the others
854 fnd_file.new_line(fnd_file.log);
855 END IF;
856 END IF;
857 l_person_id := rec_chrgs.person_id;
858 l_course_cd := rec_chrgs.course_cd;
859 END IF;
860
861 EXCEPTION
862 WHEN e_skip_record THEN
863 fnd_message.set_name('IGS','IGS_FI_PP_NO_UNIT_DROP');
864 fnd_file.new_line(fnd_file.log);
865 fnd_file.put_line(fnd_file.log,l_str||' - '||fnd_message.get);
866 fnd_file.new_line(fnd_file.log);
867 END;
868 END IF; -- end of condition v_tab_chrgs_rec.EXISTS(l_n_cntr)
869 END LOOP;
870 END IF;
871
872 -- If there were no units identified for dropping, then log the appropriate message in the
873 -- log file of the concurrent manager.
874 IF l_cnt = 0 THEN
875 fnd_file.new_line(fnd_file.log);
876 FND_MESSAGE.SET_NAME('IGS','IGS_FI_NO_DROP_UNITS');
877 fnd_file.put_line(fnd_file.log, fnd_message.get);
878 END IF;
879
880 -- If the test run flag is not Y, then the transactions need to be committed else
881 -- rollback to the savepoint
882 IF p_test_run <> g_yes_ind THEN
883 COMMIT;
884 ELSE
885 ROLLBACK TO s_disc_drop_units;
886 END IF;
887
888 EXCEPTION
889 WHEN OTHERS THEN
890 ROLLBACK TO s_disc_drop_units;
891 retcode := 2;
892 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
893 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
894 END drop_disc_sua_non_payment;
895 END igs_fi_prc_disc_sua;
[Home] [Help]