1 PACKAGE BODY igs_fi_payment_plans AS
2 /* $Header: IGSFI87B.pls 120.4 2006/06/13 08:50:33 sapanigr noship $ */
3 ------------------------------------------------------------------
4 --Created by : vvutukur, Oracle IDC
5 --Date created: 27-Aug-2003
6 --
7 --Purpose: This package contains the Concurrent processes related to
8 -- the Payment Plans.(Created as part of Payment Plans Build,
9 -- Enh#3045007.
10 --Known limitations/enhancements and/or remarks:
11 --
12 --Change History:
13 --Who When What
14 --sapanigr 13-Jun-2006 Bug#5088965.Modified activate_plan
15 --sapanigr 12-Jun-2006 Bug 5068241.Modified assign_students.
16 --abshriva 4-May-2006 Bug 5178077: Modification in CLOSE_STATUS and ACTIVATE_PLAN
17 --vvutukur 03-Feb-2004 Bug#3399850.Modified assign_students.
18 --bannamal 22-jul-2004 Bug#3781266.GSCC warning file.sql.35 was
19 -- fixed. The procedure activate_plan was modified.
20 -------------------------------------------------------------------
21
22 --Declaration of a ref cursor type.
23 TYPE cur_ref IS REF CURSOR;
24
25 e_skip_record EXCEPTION;
26 e_resource_busy EXCEPTION;
27 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
28
29 g_v_ind_yes CONSTANT VARCHAR2(1) := 'Y';
30 g_v_ind_no CONSTANT VARCHAR2(1) := 'N';
31 g_v_planned CONSTANT igs_fi_pp_std_attrs.plan_status_code%TYPE := 'PLANNED';
32 g_v_active CONSTANT igs_fi_pp_std_attrs.plan_status_code%TYPE := 'ACTIVE';
33 g_v_monthly CONSTANT igs_fi_pp_templates.installment_period_code%TYPE := 'MONTHLY';
34 g_v_bi_monthly CONSTANT igs_fi_pp_templates.installment_period_code%TYPE := 'BI_MONTHLY';
35 g_v_quarterly CONSTANT igs_fi_pp_templates.installment_period_code%TYPE := 'QUARTERLY';
36 g_v_line CONSTANT VARCHAR2(100) := '+'||RPAD('-',75,'-')||'+';
37
38 g_v_offset_days CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','OFFSET_DAYS');
39 g_v_person_group CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP');
40 g_v_fee_period CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_PERIOD');
41 g_v_pp_status CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','STATUS');
42 g_v_warning CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','WARNING');
43 g_v_success CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','SUCCESS');
44 g_v_error CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','ERROR');
45 g_v_person CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON');
46 g_d_sysdate CONSTANT DATE := TRUNC(SYSDATE);
47
48 --Cursor for validating person group.
49 CURSOR cur_pers_grp(cp_n_pers_grp_id igs_pe_persid_group_all.group_id%TYPE) IS
50 SELECT 'x'
51 FROM igs_pe_persid_group_all
52 WHERE group_id = cp_n_pers_grp_id
53 AND TRUNC(create_dt) <= g_d_sysdate
54 AND NVL(closed_ind,g_v_ind_no) = g_v_ind_no;
55
56
57 PROCEDURE activate_plan(errbuf OUT NOCOPY VARCHAR2,
58 retcode OUT NOCOPY NUMBER,
59 p_n_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
60 p_v_fee_period IN VARCHAR2,
61 p_n_offset_days IN NUMBER
62 ) AS
63 ------------------------------------------------------------------
64 --Created by : vvutukur, Oracle IDC
65 --Date created: 27-Aug-2003
66 --
67 --Purpose: Concurrent program that activates the Payment Plans
68 -- that are in a planned status for the Student.
69 --
70 --Known limitations/enhancements and/or remarks:
71 --
72 --Change History:
73 --Who When What
74 --sapanigr 13-Jun-2006 Bug#5088965.Message 'IGS_FI_PP_NO_PRC_FEE_SETUP' is now logged
75 -- if fee period parameter is passed as input and no processing fee
76 -- type has been setup for student.
77
78 --abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
79 --bannamal 22-jul-2004 Bug#3781266.Modified the logging status from
80 -- warning to error in case of failure to activate
81 -- the payment plan
82 -------------------------------------------------------------------
83
84 --Declaration of Ref cursor variable type.
85 l_cur_ref cur_ref;
86 l_org_id VARCHAR2(15);
87 --Cursor to validate if the Student has a Payment Plan with the status of ACTIVE.
88 CURSOR cur_pmt_active(cp_n_person_id igs_fi_parties_v.person_id%TYPE) IS
89 SELECT 'x'
90 FROM igs_fi_pp_std_attrs
91 WHERE person_id = cp_n_person_id
92 AND plan_status_code = g_v_active;
93
94 --Cursor to validate if the Student has a Payment Plan with the status of PLANNED.
95 --This cursor also selects the details of the Planned Payment Plans for a Student.
96 CURSOR cur_pmt_planned(cp_n_person_id igs_fi_parties_v.person_id%TYPE) IS
97 SELECT a.rowid,a.*
98 FROM igs_fi_pp_std_attrs a
99 WHERE a.person_id = cp_n_person_id
100 AND a.plan_status_code = g_v_planned
101 AND a.plan_start_date <= g_d_sysdate
102 FOR UPDATE NOWAIT;
103
104 rec_cur_pmt_planned cur_pmt_planned%ROWTYPE;
105
106 --Cursor to validate if the specified Payment Plan has the Start Date
107 --overlapping with the End Date of the previous plan.
108 CURSOR cur_validate_st_dt(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
109 cp_n_std_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE,
110 cp_d_start_dt igs_fi_pp_std_attrs.plan_start_date%TYPE) IS
111 SELECT 'x'
112 FROM igs_fi_pp_std_attrs
113 WHERE person_id = cp_n_person_id
114 AND student_plan_id <> cp_n_std_plan_id
115 AND TRUNC(plan_end_date) >= TRUNC(cp_d_start_dt);
116
117 --Cursor to validate if the Payment Plan has some installments for which the
118 --due date is earlier than the System Date.
119 CURSOR cur_pln_inst(cp_n_std_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE) IS
120 SELECT 'x'
121 FROM igs_fi_pp_instlmnts
122 WHERE student_plan_id = cp_n_std_plan_id
123 AND TRUNC(due_date) < g_d_sysdate
124 AND rownum < 2;
125
126 --Cursor to validate if the Installment Due Date of the first installment is earlier than
127 --the Start Date of the Payment Plan.
128 CURSOR cur_pln_due_dt(cp_n_std_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE) IS
129 SELECT due_date
130 FROM igs_fi_pp_instlmnts
131 WHERE student_plan_id = cp_n_std_plan_id
132 ORDER BY due_date;
133
134 --Cursor to validate if the Fee Type and Fee Calendar Instance combination is an active combination.
135 CURSOR cur_validate_ftci(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE,
136 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
137 cp_n_fee_ci_seq_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE) IS
138 SELECT 'x'
139 FROM igs_fi_f_typ_ca_inst ftci,
140 igs_fi_fee_str_stat fst
141 WHERE ftci.fee_type = cp_v_fee_type
142 AND ftci.fee_cal_type = cp_v_fee_cal_type
143 AND ftci.fee_ci_sequence_number = cp_n_fee_ci_seq_number
144 AND ftci.fee_type_ci_status = fst.fee_structure_status
145 AND fst.s_fee_structure_status = g_v_active;
146
147 l_v_manage_acc igs_fi_control.manage_accounts%TYPE := NULL;
148 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
149 l_v_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE := NULL;
150 l_n_fee_ci_sequence_number igs_fi_inv_int.fee_ci_sequence_number%TYPE := NULL;
151 l_d_due_dt igs_fi_pp_instlmnts.due_date%TYPE;
152
153 l_b_valid_param BOOLEAN := TRUE;
154
155 l_v_var VARCHAR2(1);
156 l_v_sql VARCHAR2(32767);
157 l_v_status VARCHAR2(1);
158
159 l_v_person_number igs_fi_parties_v.person_number%TYPE;
160 l_n_person_id igs_fi_parties_v.person_id%TYPE;
161 l_n_count PLS_INTEGER;
162
163 BEGIN
164 BEGIN
165 l_org_id := NULL;
166 igs_ge_gen_003.set_org_id(l_org_id);
167 EXCEPTION
168 WHEN OTHERS THEN
169 fnd_file.put_line (fnd_file.log, fnd_message.get);
170 retcode:=2;
171 RETURN;
172 END;
173 SAVEPOINT sp_activate_pp;
174 retcode := 0;
175 errbuf := NULL;
176
177 --Log the input parameters.
178 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
179 fnd_file.put_line(fnd_file.log,fnd_message.get||':');
180 fnd_file.new_line(fnd_file.log);
181
182 fnd_file.put_line(fnd_file.log,g_v_person_group||':'||igs_fi_gen_005.finp_get_prsid_grp_code(p_n_person_id_grp));
183 fnd_file.put_line(fnd_file.log,g_v_fee_period||':'||p_v_fee_period);
184 fnd_file.put_line(fnd_file.log,g_v_offset_days||':'||p_n_offset_days);
185
186 fnd_file.put_line(fnd_file.log,g_v_line);
187
188 -- Obtain the value of manage_accounts in the System Options form
189 -- If it is null or 'OTHER', then this process is not available, so error out.
190 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
191 p_v_message_name => l_v_message_name
192 );
193 IF (l_v_manage_acc = 'OTHER' OR l_v_manage_acc IS NULL) THEN
194 fnd_message.set_name('IGS',l_v_message_name);
195 fnd_file.put_line(fnd_file.log,fnd_message.get);
196 retcode := 2;
197 RETURN;
198 END IF;
199
200 --Person Group is a mandatory parameter for this process.
201 --If the parameter p_n_person_id_grp is null, log the error message.
202 IF p_n_person_id_grp IS NULL THEN
203 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
204 fnd_file.put_line(fnd_file.log,fnd_message.get);
205 l_b_valid_param := FALSE;
206 ELSE
207 --Validate if the input parameter person id group is valid.
208 OPEN cur_pers_grp(p_n_person_id_grp);
209 FETCH cur_pers_grp INTO l_v_var;
210 --If not valid, log the error message in the log file.
211 IF cur_pers_grp%NOTFOUND THEN
212 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
213 fnd_message.set_token('PARAMETER',g_v_person_group);
214 fnd_file.put_line(fnd_file.log,fnd_message.get);
215 l_b_valid_param := FALSE;
216 END IF;
217 CLOSE cur_pers_grp;
218 END IF;
219
220 --The Fee Period process parameter is a string of concatenated values.
221 --Derive the values of fee calendar type and fee calendar instance from the fee period string.
222 IF p_v_fee_period IS NOT NULL THEN
223 l_v_fee_cal_type := RTRIM(SUBSTR(p_v_fee_period,102,10));
224 l_n_fee_ci_sequence_number := TO_NUMBER(LTRIM(SUBSTR(p_v_fee_period,113,8)));
225
226 --Validate the Fee Period parameter.
227 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_v_fee_cal_type,
228 p_n_ci_sequence_number => l_n_fee_ci_sequence_number,
229 p_v_s_cal_cat => 'FEE'
230 ) THEN
231 --If Fee Period is not valid, then log the error message in the log file.
232 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
233 fnd_message.set_token('PARAMETER',g_v_fee_period);
234 fnd_file.put_line(fnd_file.log,fnd_message.get);
235 l_b_valid_param := FALSE;
236 END IF;
237 END IF;
238
239 --Validate offset days parameter, whether it contains negative value.
240 IF p_n_offset_days IS NOT NULL AND p_n_offset_days < 0 THEN
241 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
242 fnd_message.set_token('PARAMETER',g_v_offset_days);
243 fnd_file.put_line(fnd_file.log,fnd_message.get);
244 l_b_valid_param := FALSE;
245 END IF;
246
247 --If any of the above parameter validations fail, process should complete with error.
248 IF NOT l_b_valid_param THEN
249 retcode := 2;
250 RETURN;
251 END IF;
252
253 --For the Person Group passed as input to the process, identify all the Persons that are members of this group
254 --using generic function.
255 l_v_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_groupid => p_n_person_id_grp,
256 p_status => l_v_status
257 );
258 --If the sql returned is invalid, then..
259 IF l_v_status <> 'S' THEN
260 --Log the error message and stop the processing.
261 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
262 fnd_file.put_line(fnd_file.log,fnd_message.get);
263 fnd_file.put_line(fnd_file.log,l_v_sql);
264 retcode := 2;
265 RETURN;
266 END IF;
267
268 l_n_count := 1;
269 --Execute the sql statement using ref cursor.
270 OPEN l_cur_ref FOR l_v_sql;
271 LOOP
272 --Fetch the person id in a local variable.
273 FETCH l_cur_ref INTO l_n_person_id;
274 EXIT WHEN l_cur_ref%NOTFOUND;
275
276 l_n_count := l_n_count + 1;
277
278 BEGIN
279
280 --Log the person details.
281 l_v_person_number := igs_fi_gen_008.get_party_number(l_n_person_id);
282 fnd_file.put_line(fnd_file.log,g_v_person||':'||l_v_person_number);
283
284 --Check if the Student has a Payment Plan with ACTIVE status.
285 OPEN cur_pmt_active(l_n_person_id);
286 FETCH cur_pmt_active INTO l_v_var;
287 --If yes, then..
288 IF cur_pmt_active%FOUND THEN
289 CLOSE cur_pmt_active;
290 --no processing needs to be done for the Student.Next Student needs to be picked up for processing.
291 fnd_message.set_name('IGS','IGS_FI_PP_ACT_PAY_PLAN');
292 fnd_file.put_line(fnd_file.log,fnd_message.get);
293 RAISE e_skip_record;
294 END IF;
295 CLOSE cur_pmt_active;
296
297 --Check if the Student has a Payment Plan with the status PLANNED...
298 OPEN cur_pmt_planned(l_n_person_id);
299 FETCH cur_pmt_planned INTO rec_cur_pmt_planned;
300 --If the Student does not have a Payment Plan with status PLANNED...
301 IF cur_pmt_planned%NOTFOUND THEN
302 CLOSE cur_pmt_planned;
303 --Log the message in log file and proceed to next Student in the Person Group passed as input.
304 fnd_message.set_name('IGS','IGS_FI_PP_PLANNED_PAY_PLANS');
305 fnd_file.put_line(fnd_file.log,fnd_message.get);
306 RAISE e_skip_record;
307 END IF;
308 CLOSE cur_pmt_planned;
309
310 --Validate if the Start Date of the Payment Plan is overlapping with the End Date of the previous plan.
311 OPEN cur_validate_st_dt(l_n_person_id,
312 rec_cur_pmt_planned.student_plan_id,
313 rec_cur_pmt_planned.plan_start_date
314 );
315 FETCH cur_validate_st_dt INTO l_v_var;
316 --if yes, log the error message,skip the current Student and process next Student in the Person Group.
317 IF cur_validate_st_dt%FOUND THEN
318 CLOSE cur_validate_st_dt;
319 fnd_message.set_name('IGS','IGS_FI_PP_ACTIVE_START_DATE');
320 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
321 fnd_file.put_line(fnd_file.log,fnd_message.get);
322 RAISE e_skip_record;
323 END IF;
324 CLOSE cur_validate_st_dt;
325
326 --Validate if the Start Date of the Payment Plan is greater than the current System Date.
327 --if yes, log the error message,skip the current Student and process next Student in the Person Group.
328 IF TRUNC(rec_cur_pmt_planned.plan_start_date) > g_d_sysdate THEN
329 fnd_message.set_name('IGS','IGS_FI_PP_NOT_ACTIVE');
330 fnd_message.set_token('START_DATE',rec_cur_pmt_planned.plan_start_date);
331 fnd_message.set_token('PAY_PLAN',rec_cur_pmt_planned.payment_plan_name);
332 fnd_file.put_line(fnd_file.log,fnd_message.get);
333 RAISE e_skip_record;
334 END IF;
335
336 --Validate fi the Payment Plan has some installments for which the due date is earlier than the System Date.
337 OPEN cur_pln_inst(rec_cur_pmt_planned.student_plan_id);
338 FETCH cur_pln_inst INTO l_v_var;
339 --if yes, log the error message,skip the current Student and process next Student in the Person Group.
340 IF cur_pln_inst%FOUND THEN
341 CLOSE cur_pln_inst;
342 fnd_message.set_name('IGS','IGS_FI_PP_INST_DUE_SYSDATE');
343 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
344 fnd_file.put_line(fnd_file.log,fnd_message.get);
345 RAISE e_skip_record;
346 END IF;
347 CLOSE cur_pln_inst;
348
349 --Fetch the due date of the first installment of the Payment Plan.
350 OPEN cur_pln_due_dt(rec_cur_pmt_planned.student_plan_id);
351 FETCH cur_pln_due_dt INTO l_d_due_dt;
352 CLOSE cur_pln_due_dt;
353
354 --Validate if the Installment Due Date of the first installment is earlier than
355 --the Start Date of the Payment Plan.
356 --if yes, log the error message,skip the current Student and process next Student in the Person Group.
357 IF TRUNC(l_d_due_dt) < TRUNC(rec_cur_pmt_planned.plan_start_date) THEN
358 fnd_message.set_name('IGS','IGS_FI_PP_INST_DUE_DATE');
359 fnd_file.put_line(fnd_file.log,fnd_message.get);
360 RAISE e_skip_record;
361 END IF;
362
363 --Validate if the offset days, provided as input to this procees,is greater than the days between the
364 --first installment due date and the System Date.
365 --if offset days is greater, then log the error message,skip the current Student and
366 --process next Student in the Person Group.
367 IF (TRUNC(l_d_due_dt) - g_d_sysdate) < NVL(p_n_offset_days,0) THEN
368 fnd_message.set_name('IGS','IGS_FI_PP_PAY_PLAN_OFFSET');
369 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
370 fnd_message.set_token('PERSON_NUMBER',l_v_person_number);
371 fnd_file.put_line(fnd_file.log,fnd_message.get);
372 RAISE e_skip_record;
373 END IF;
374
375 --If processing fee amt and processing fee type are NOT PROVIDED for the Payment Plan record, then..
376 IF rec_cur_pmt_planned.processing_fee_type IS NULL AND rec_cur_pmt_planned.processing_fee_amt IS NULL THEN
377 --FTCI details are to be passed as NULL, while updating the Payment Plan Record.
378 rec_cur_pmt_planned.fee_cal_type := NULL;
379 rec_cur_pmt_planned.fee_ci_sequence_number := NULL;
380 --..if Fee Period parameter is not null then log should indicate that fee period is skipped for student.
381 IF p_v_fee_period IS NOT NULL THEN
382 fnd_message.set_name('IGS','IGS_FI_PP_NO_PRC_FEE_SETUP');
383 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
384 fnd_file.put_line(fnd_file.log,fnd_message.get);
385 END IF;
386
387 ELSE --If processing fee amt and processing fee type are PROVIDED for the Payment Plan record,
388
389 --If the FTCI details are not provided for the Payment Plan record.
390 IF rec_cur_pmt_planned.fee_cal_type IS NULL AND rec_cur_pmt_planned.fee_ci_sequence_number IS NULL THEN
391 --Check if the Fee Period input parameter to this process has some value.
392 --If Fee Period parameter is also null,then log the error message,skip the current Student and
393 --process next Student in the Person Group.
394 IF p_v_fee_period IS NULL THEN
395 fnd_message.set_name('IGS','IGS_FI_PP_NO_FEE_PERIOD');
396 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
397 fnd_file.put_line(fnd_file.log,fnd_message.get);
398 RAISE e_skip_record;
399 ELSE--Fee Period parameter is not null, then...
400 --Validate the FTCI combination.
401 OPEN cur_validate_ftci(rec_cur_pmt_planned.processing_fee_type,
402 l_v_fee_cal_type,
403 l_n_fee_ci_sequence_number
404 );
405 FETCH cur_validate_ftci INTO l_v_var;
406 --If FTCI combination is not valid, then log the error message,skip the current Student and
407 --process next Student in the Person Group.
408 IF cur_validate_ftci%NOTFOUND THEN
409 CLOSE cur_validate_ftci;
410 fnd_message.set_name('IGS','IGS_FI_PP_NO_FEE_PERIOD_MATCH');
411 fnd_message.set_token('PLAN_NAME',rec_cur_pmt_planned.payment_plan_name);
412 fnd_message.set_token('FEE_TYPE_NAME',rec_cur_pmt_planned.processing_fee_type);
413 fnd_file.put_line(fnd_file.log,fnd_message.get);
414 RAISE e_skip_record;
415 ELSE
416 rec_cur_pmt_planned.fee_cal_type := l_v_fee_cal_type;
417 rec_cur_pmt_planned.fee_ci_sequence_number := l_n_fee_ci_sequence_number;
418 END IF;
419 CLOSE cur_validate_ftci;
420 END IF;
421 END IF;
422 END IF;
423
424 --If all the validations are thru and if the Student need not be skipped from processing,
425 --Update the Payment Plan record to set the Planned Status Code to 'ACTIVE' with appropriate
426 --FTCI details as derived above.
427 BEGIN
428 igs_fi_pp_std_attrs_pkg.update_row(
429 x_rowid => rec_cur_pmt_planned.rowid,
430 x_student_plan_id => rec_cur_pmt_planned.student_plan_id,
431 x_person_id => rec_cur_pmt_planned.person_id,
432 x_payment_plan_name => rec_cur_pmt_planned.payment_plan_name,
433 x_plan_start_date => rec_cur_pmt_planned.plan_start_date,
434 x_plan_end_date => rec_cur_pmt_planned.plan_end_date,
435 x_plan_status_code => g_v_active,
436 x_processing_fee_amt => rec_cur_pmt_planned.processing_fee_amt,
437 x_processing_fee_type => rec_cur_pmt_planned.processing_fee_type,
438 x_fee_cal_type => rec_cur_pmt_planned.fee_cal_type,
439 x_fee_ci_sequence_number => rec_cur_pmt_planned.fee_ci_sequence_number,
440 x_notes => rec_cur_pmt_planned.notes,
441 x_invoice_id => rec_cur_pmt_planned.invoice_id,
442 x_attribute_category => rec_cur_pmt_planned.attribute_category,
443 x_attribute1 => rec_cur_pmt_planned.attribute1,
444 x_attribute2 => rec_cur_pmt_planned.attribute2,
445 x_attribute3 => rec_cur_pmt_planned.attribute3,
446 x_attribute4 => rec_cur_pmt_planned.attribute4,
447 x_attribute5 => rec_cur_pmt_planned.attribute5,
448 x_attribute6 => rec_cur_pmt_planned.attribute6,
449 x_attribute7 => rec_cur_pmt_planned.attribute7,
450 x_attribute8 => rec_cur_pmt_planned.attribute8,
451 x_attribute9 => rec_cur_pmt_planned.attribute9,
452 x_attribute10 => rec_cur_pmt_planned.attribute10,
453 x_attribute11 => rec_cur_pmt_planned.attribute11,
454 x_attribute12 => rec_cur_pmt_planned.attribute12,
455 x_attribute13 => rec_cur_pmt_planned.attribute13,
456 x_attribute14 => rec_cur_pmt_planned.attribute14,
457 x_attribute15 => rec_cur_pmt_planned.attribute15,
458 x_attribute16 => rec_cur_pmt_planned.attribute16,
459 x_attribute17 => rec_cur_pmt_planned.attribute17,
460 x_attribute18 => rec_cur_pmt_planned.attribute18,
461 x_attribute19 => rec_cur_pmt_planned.attribute19,
462 x_attribute20 => rec_cur_pmt_planned.attribute20,
463 x_mode => 'R'
464 );
465
466 --If no error is raised, log the status as Success.
467 fnd_file.put_line(fnd_file.log,g_v_pp_status||':'||g_v_success);
468
469 EXCEPTION
470 WHEN OTHERS THEN
471 --In case TBH throws any errors, log the error status.
472 fnd_file.put_line(fnd_file.log,g_v_pp_status||':'||g_v_error);
473 fnd_file.put_line(fnd_file.log,SQLERRM);
474 retcode := 1;
475 END;
476 EXCEPTION
477 WHEN e_skip_record THEN
478 --log the Status as Warning(as the error message is already being logged as and when validation failed.)
479 --and retcode need to be set to 1 to complete the process also in Warning status.
480 fnd_file.put_line(fnd_file.log,g_v_pp_status||':'||g_v_error);
481 retcode := 1;
482 END;
483 fnd_file.new_line(fnd_file.log);
484 END LOOP;
485 CLOSE l_cur_ref;
486
487 --If person group passed as input does not have any members associated,
488 --then process should log the message ***No Data Found***.
489 IF l_n_count = 1 THEN
490 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
491 fnd_file.put_line(fnd_file.log,fnd_message.get);
492 fnd_file.new_line(fnd_file.log);
493 END IF;
494 fnd_file.put_line(fnd_file.log,g_v_line);
495
496 EXCEPTION
497 --Handle the Locking exception.
498 WHEN e_resource_busy THEN
499 ROLLBACK TO sp_activate_pp;
500 retcode := 2;
501 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
502 fnd_file.put_line(fnd_file.log,fnd_message.get);
503 fnd_file.new_line(fnd_file.log);
504 WHEN OTHERS THEN
505 ROLLBACK TO sp_activate_pp;
506 retcode := 2;
507 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' - '||SQLERRM);
508 END activate_plan;
509
510 PROCEDURE assign_students(errbuf OUT NOCOPY VARCHAR2,
511 retcode OUT NOCOPY NUMBER,
512 p_v_payment_plan_name IN igs_fi_pp_templates.payment_plan_name%TYPE,
513 p_n_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
514 p_v_start_date IN VARCHAR2,
515 p_v_fee_period IN VARCHAR2
516 ) AS
517 ------------------------------------------------------------------
518 --Created by : vvutukur, Oracle IDC
519 --Date created: 27-Aug-2003
520 --
521 --Purpose: Concurrent program that assigns a Payment Plan to a Student.
522 --
523 --Known limitations/enhancements and/or remarks:
524 --
525 --Change History:
526 --Who When What
527 --sapanigr 12-Jun-2006 Bug 5068241. Value assigned to status variable l_v_pers_status while logging
528 -- message IGS_FI_PP_NO_NEW_ASSIGN has been changed from WARNING to ERROR.
529 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
530 --vvutukur 03-Feb-2004 Bug#3399850.Validation of start date parameter is made erroroneous situation
531 -- in stead of warning. Record creation is prevented if validation fails.
532 -------------------------------------------------------------------
533
534 --Ref cursor variable.
535 l_cur_ref cur_ref;
536
537 --Cursor for fetching the payment plan details.
538 CURSOR cur_pmt_plan(cp_v_pmt_plan igs_fi_pp_templates.payment_plan_name%TYPE) IS
539 SELECT *
540 FROM igs_fi_pp_templates
541 WHERE payment_plan_name = cp_v_pmt_plan
542 AND NVL(closed_flag,g_v_ind_no) = g_v_ind_no;
543
544 rec_cur_pmt_plan cur_pmt_plan%ROWTYPE;
545
546 --Cursor for fetching all the installment lines for the payment plan passed as input.
547 CURSOR cur_pp_lines(cp_v_pmt_plan igs_fi_pp_templates.payment_plan_name%TYPE) IS
548 SELECT *
549 FROM igs_fi_pp_tmpl_lns
550 WHERE payment_plan_name = cp_v_pmt_plan;
551
552 --Cursor used to validate if the Person has a Payment Plan with status as Planned.
553 CURSOR cur_pp_planned(cp_n_person_id igs_fi_parties_v.person_id%TYPE) IS
554 SELECT 'x'
555 FROM igs_fi_pp_std_attrs
556 WHERE person_id = cp_n_person_id
557 AND plan_status_code = g_v_planned;
558
559 --Cursor used to validate if the Start Date passed as input to the process is eariler than
560 --the End Date of any plan for the person.
561 CURSOR cur_validate_start_dt(cp_n_person_id igs_fi_parties_v.person_id%TYPE,
562 cp_d_start_date DATE) IS
563 SELECT 'x'
564 FROM igs_fi_pp_std_attrs
565 WHERE person_id = cp_n_person_id
566 AND TRUNC(cp_d_start_date) <= TRUNC(plan_end_date);
567
568 --Declare a local PL/SQL Table to hold certain values for the creation of student payment plan record.
569 TYPE temp_rec_type IS RECORD (
570 plan_line_num igs_fi_pp_tmpl_lns.plan_line_num%TYPE,
571 plan_amount igs_fi_pp_tmpl_lns.plan_amt%TYPE,
572 due_date igs_fi_pp_instlmnts.due_date%TYPE);
573
574 TYPE temp_tbl_type IS TABLE OF temp_rec_type INDEX BY BINARY_INTEGER;
575 l_temp_plsql_tbl temp_tbl_type;
576
577 l_v_manage_acc igs_fi_control.manage_accounts%TYPE := NULL;
578 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
579 l_v_fee_cal_type igs_fi_inv_int.fee_cal_type%TYPE := NULL;
580 l_n_fee_ci_sequence_number igs_fi_inv_int.fee_ci_sequence_number%TYPE := NULL;
581 l_b_valid_param BOOLEAN := TRUE;
582 l_b_planned_plan BOOLEAN := FALSE;
583 l_b_no_rec BOOLEAN := FALSE;
584
585 l_d_due_dt_fst_inst DATE;
586 l_d_prev_due_dt DATE;
587 l_d_start_date DATE;
588 l_v_var VARCHAR2(1);
589 l_rowid ROWID;
590 l_n_installment_id igs_fi_pp_instlmnts.installment_id%TYPE;
591 l_n_student_plan_id igs_fi_pp_instlmnts.student_plan_id%TYPE;
592 l_v_person_number igs_fi_parties_v.person_number%TYPE;
593 l_n_person_id igs_fi_parties_v.person_id%TYPE;
594 l_v_sql VARCHAR2(32767);
595 l_v_status VARCHAR2(1);
596 l_v_pers_status igs_lookup_values.meaning%TYPE;
597 l_v_pers_message fnd_new_messages.message_text%TYPE;
598
599 l_n_count PLS_INTEGER;
600 l_n_cnt PLS_INTEGER;
601 l_n_months PLS_INTEGER;
602
603 l_v_pmt_plan_template CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PAYPLAN_TEMPLATE');
604 l_v_start_date CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','START_DT');
605
606 TYPE dff_seg_values IS RECORD (
607 attribute1 igs_fi_pp_std_attrs.attribute1%TYPE,
608 attribute2 igs_fi_pp_std_attrs.attribute2%TYPE,
609 attribute3 igs_fi_pp_std_attrs.attribute3%TYPE,
610 attribute4 igs_fi_pp_std_attrs.attribute4%TYPE,
611 attribute5 igs_fi_pp_std_attrs.attribute5%TYPE,
612 attribute6 igs_fi_pp_std_attrs.attribute6%TYPE,
613 attribute7 igs_fi_pp_std_attrs.attribute7%TYPE,
614 attribute8 igs_fi_pp_std_attrs.attribute8%TYPE,
615 attribute9 igs_fi_pp_std_attrs.attribute9%TYPE,
616 attribute10 igs_fi_pp_std_attrs.attribute10%TYPE,
617 attribute11 igs_fi_pp_std_attrs.attribute11%TYPE,
618 attribute12 igs_fi_pp_std_attrs.attribute12%TYPE,
619 attribute13 igs_fi_pp_std_attrs.attribute13%TYPE,
620 attribute14 igs_fi_pp_std_attrs.attribute14%TYPE,
621 attribute15 igs_fi_pp_std_attrs.attribute15%TYPE,
622 attribute16 igs_fi_pp_std_attrs.attribute16%TYPE,
623 attribute17 igs_fi_pp_std_attrs.attribute17%TYPE,
624 attribute18 igs_fi_pp_std_attrs.attribute18%TYPE,
625 attribute19 igs_fi_pp_std_attrs.attribute19%TYPE,
626 attribute20 igs_fi_pp_std_attrs.attribute20%TYPE);
627
628 l_dff_seg_values dff_seg_values;
629
630 BEGIN
631
632 SAVEPOINT sp_assign_student;
633 retcode := 0;
634 errbuf := NULL;
635
636 --Parameter p_v_start_date is of type VARCHAR2, converting and truncating the same.
637 IF p_v_start_date IS NOT NULL THEN
638 l_d_start_date := TRUNC(igs_ge_date.igsdate(p_v_start_date));
639 END IF;
640
641 --Log the input parameters.
642 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
643 fnd_file.put_line(fnd_file.log,fnd_message.get||':');
644 fnd_file.new_line(fnd_file.log);
645
646 fnd_file.put_line(fnd_file.log,l_v_pmt_plan_template||':'||p_v_payment_plan_name);
647 fnd_file.put_line(fnd_file.log,g_v_person_group||':'||igs_fi_gen_005.finp_get_prsid_grp_code(p_n_person_id_grp));
648 fnd_file.put_line(fnd_file.log,l_v_start_date||':'||l_d_start_date);
649 fnd_file.put_line(fnd_file.log,g_v_fee_period||':'||p_v_fee_period);
650
651 fnd_file.put_line(fnd_file.log,g_v_line);
652
653 -- Obtain the value of manage_accounts in the System Options form
654 -- If it is null or 'OTHER', then this process is not available, so error out.
655 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
656 p_v_message_name => l_v_message_name
657 );
658 IF (l_v_manage_acc = 'OTHER' OR l_v_manage_acc IS NULL) THEN
659 fnd_message.set_name('IGS',l_v_message_name);
660 fnd_file.put_line(fnd_file.log,fnd_message.get);
661 retcode := 2;
662 RETURN;
663 END IF;
664
665 --Check if the required parameters are passed, if not log the error message.
666 IF p_v_payment_plan_name IS NULL OR p_n_person_id_grp IS NULL OR p_v_start_date IS NULL THEN
667 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
668 fnd_file.put_line(fnd_file.log,fnd_message.get);
669 l_b_valid_param := FALSE;
670 END IF;
671
672 --Validate Payment Plan parameter.
673 OPEN cur_pmt_plan(p_v_payment_plan_name);
674 FETCH cur_pmt_plan INTO rec_cur_pmt_plan;
675 IF cur_pmt_plan%NOTFOUND THEN
676 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
677 fnd_message.set_token('PARAMETER',l_v_pmt_plan_template);
678 fnd_file.put_line(fnd_file.log,fnd_message.get);
679 l_b_valid_param := FALSE;
680 END IF;
681 CLOSE cur_pmt_plan;
682
683 OPEN cur_pers_grp(p_n_person_id_grp);
684 FETCH cur_pers_grp INTO l_v_var;
685 IF cur_pers_grp%NOTFOUND THEN
686 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
687 fnd_message.set_token('PARAMETER',g_v_person_group);
688 fnd_file.put_line(fnd_file.log,fnd_message.get);
689 l_b_valid_param := FALSE;
690 END IF;
691 CLOSE cur_pers_grp;
692
693 --The Fee Period process parameter is a string of concatenated values.
694 --Derive the values of fee calendar type and fee calendar instance from the fee period string.
695 IF p_v_fee_period IS NOT NULL THEN
696 l_v_fee_cal_type := RTRIM(SUBSTR(p_v_fee_period,102,10));
697 l_n_fee_ci_sequence_number := TO_NUMBER(LTRIM(SUBSTR(p_v_fee_period,113,8)));
698
699 --Validate the Fee Period parameters.
700 IF NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_v_fee_cal_type,
701 p_n_ci_sequence_number => l_n_fee_ci_sequence_number,
702 p_v_s_cal_cat => 'FEE'
703 ) THEN
704 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
705 fnd_message.set_token('PARAMETER',g_v_fee_period);
706 fnd_file.put_line(fnd_file.log,fnd_message.get);
707 l_b_valid_param := FALSE;
708 END IF;
709 END IF;
710
711 --Validate if the Descriptive Flexfield for the Student Payment Plan has been setup with some mandatory attributes.
712 fnd_flex_descval.clear_column_values;
713 fnd_flex_descval.set_context_value(context_value => NULL);
714
715 --Set all the attribute column values to NULL.
716 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE1', column_value => '');
717 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE2', column_value => '');
718 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE3', column_value => '');
719 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE4', column_value => '');
720 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE5', column_value => '');
721 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE6', column_value => '');
722 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE7', column_value => '');
723 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE8', column_value => '');
724 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE9', column_value => '');
725 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE10',column_value => '');
726 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE11',column_value => '');
727 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE12',column_value => '');
728 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE13',column_value => '');
729 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE14',column_value => '');
730 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE15',column_value => '');
731 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE16',column_value => '');
732 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE17',column_value => '');
733 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE18',column_value => '');
734 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE19',column_value => '');
735 fnd_flex_descval.set_column_value(column_name => 'ATTRIBUTE20',column_value => '');
736
737 --If DFF is defined with mandatory attributes, then the error message needs to be logged.
738 IF NOT fnd_flex_descval.validate_desccols(appl_short_name => 'IGS',
739 desc_flex_name => 'IGS_FI_PP_STD_ATTRS_FLEX',
740 values_or_ids => 'V',
741 validation_date => SYSDATE) THEN
742 fnd_file.put_line(fnd_file.log,fnd_flex_descval.error_message);
743 l_b_valid_param := FALSE;
744 END IF;
745
746 --For the Person Group passed as input to the process, identify all the Persons that are members of this group
747 --using generic function.
748 l_v_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_groupid => p_n_person_id_grp,
749 p_status => l_v_status
750 );
751 --If the sql returned is invalid, then..
752 IF l_v_status <> 'S' THEN
753 --Log the error message and stop processing.
754 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
755 fnd_file.put_line(fnd_file.log,fnd_message.get);
756 fnd_file.put_line(fnd_file.log,l_v_sql);
757 l_b_valid_param := FALSE;
758 END IF;
759
760 IF NOT l_b_valid_param THEN
761 retcode := 2;
762 RETURN;
763 END IF;
764
765 --Get the due date of the first installment.
766 l_d_due_dt_fst_inst := igs_fi_gen_008.get_start_date(p_d_start_date => l_d_start_date,
767 p_n_due_day => rec_cur_pmt_plan.due_day_of_month,
768 p_v_last_day => rec_cur_pmt_plan.due_end_of_month_flag,
769 p_n_offset_days => rec_cur_pmt_plan.due_cutoff_day
770 );
771
772 l_n_months := 0;
773
774 --Calculate the number of months to be used for calculating the due date of each
775 --installment of a Payment Plan.
776 IF rec_cur_pmt_plan.installment_period_code = g_v_monthly THEN
777 l_n_months := 1;
778 ELSIF rec_cur_pmt_plan.installment_period_code = g_v_bi_monthly THEN
779 l_n_months := 2;
780 ELSIF rec_cur_pmt_plan.installment_period_code = g_v_quarterly THEN
781 l_n_months := 3;
782 END IF;
783
784 l_d_prev_due_dt := NULL;
785
786 l_n_count :=1;
787 --Loop through all the Payment Plan Line records pertaining to the payment plan name passed as input.
788 FOR rec_cur_pp_lines IN cur_pp_lines(rec_cur_pmt_plan.payment_plan_name) LOOP
789
790 l_temp_plsql_tbl(l_n_count).plan_line_num := rec_cur_pp_lines.plan_line_num;
791
792 --For the first record, the due date will be the value identified earlier.ie., l_d_due_dt_fst_inst.
793 IF l_d_prev_due_dt IS NULL THEN
794 l_d_prev_due_dt := l_d_due_dt_fst_inst;
795 ELSE
796 --if the record is not the first record, Due Date needs to be calculated as:
797 --Due Date of the prior record + No. of months as per the INSTALLMENT_PERIOD_CODE.
798 --If the Installment Period Code is Monthly, prior due date + 1 month.
799 --If the Installment Period Code is Bi-Monthly, prior due date + 2 months.
800 --If the Installment Period Code is Quarterly, prior due date + 3 months.
801 l_d_prev_due_dt := ADD_MONTHS(l_d_prev_due_dt,l_n_months);
802 END IF;
803
804 --Store the value of due_date calculated as above in a local variable sothat the variable contains
805 --the due date of last installment of the Payment Plan at the end of the loop.
806 l_temp_plsql_tbl(l_n_count).due_date := l_d_prev_due_dt;
807
808 --If the installment period flag is 'P',plan percent is considered for the calculation of plan amount.
809 IF rec_cur_pmt_plan.installment_method_flag = 'P' THEN
810 l_temp_plsql_tbl(l_n_count).plan_amount := (NVL(rec_cur_pp_lines.plan_percent,0) * NVL(rec_cur_pmt_plan.base_amt,0))/100;
811
812 --If the installment period flag is 'A',plan_amt is equal to plan amount.
813 ELSIF rec_cur_pmt_plan.installment_method_flag = 'A' THEN
814
815 l_temp_plsql_tbl(l_n_count).plan_amount := NVL(rec_cur_pp_lines.plan_amt,0);
816 END IF;
817
818 l_n_count := l_n_count + 1;
819
820 END LOOP;
821
822 --Before processing each person in the Person Group,
823 --capture the values of Descriptive Flexfield Segment Values into record type local variable
824 --as these values are not variable for each person.
825 l_dff_seg_values.attribute1 := fnd_flex_descval.segment_value(1);
826 l_dff_seg_values.attribute2 := fnd_flex_descval.segment_value(2);
827 l_dff_seg_values.attribute3 := fnd_flex_descval.segment_value(3);
828 l_dff_seg_values.attribute4 := fnd_flex_descval.segment_value(4);
829 l_dff_seg_values.attribute5 := fnd_flex_descval.segment_value(5);
830 l_dff_seg_values.attribute6 := fnd_flex_descval.segment_value(6);
831 l_dff_seg_values.attribute7 := fnd_flex_descval.segment_value(7);
832 l_dff_seg_values.attribute8 := fnd_flex_descval.segment_value(8);
833 l_dff_seg_values.attribute9 := fnd_flex_descval.segment_value(9);
834 l_dff_seg_values.attribute10 := fnd_flex_descval.segment_value(10);
835 l_dff_seg_values.attribute11 := fnd_flex_descval.segment_value(11);
836 l_dff_seg_values.attribute12 := fnd_flex_descval.segment_value(12);
837 l_dff_seg_values.attribute13 := fnd_flex_descval.segment_value(13);
838 l_dff_seg_values.attribute14 := fnd_flex_descval.segment_value(14);
839 l_dff_seg_values.attribute15 := fnd_flex_descval.segment_value(15);
840 l_dff_seg_values.attribute16 := fnd_flex_descval.segment_value(16);
841 l_dff_seg_values.attribute17 := fnd_flex_descval.segment_value(17);
842 l_dff_seg_values.attribute18 := fnd_flex_descval.segment_value(18);
843 l_dff_seg_values.attribute19 := fnd_flex_descval.segment_value(19);
844 l_dff_seg_values.attribute20 := fnd_flex_descval.segment_value(20);
845
846 --For Payment Plan passed as input, if processing fee type is not defined, then FTCI information
847 --has to be passed as NULL while creating Student Payment Plan Record.
848 IF rec_cur_pmt_plan.processing_fee_type IS NULL THEN
849 l_v_fee_cal_type := NULL;
850 l_n_fee_ci_sequence_number := NULL;
851 END IF;
852
853 --Execute the sql statement using ref cursor.
854 OPEN l_cur_ref FOR l_v_sql;
855 LOOP
856
857 l_b_planned_plan := FALSE;
858 l_v_pers_status := NULL;
859 l_v_pers_message := NULL;
860
861 --Fetch the person id in a local variable.
862 FETCH l_cur_ref INTO l_n_person_id;
863 EXIT WHEN l_cur_ref%NOTFOUND;
864
865 IF NOT l_b_no_rec THEN
866 l_b_no_rec := TRUE;
867 END IF;
868
869 --Log the person details.
870 l_v_person_number := igs_fi_gen_008.get_party_number(l_n_person_id);
871 fnd_file.put_line(fnd_file.log,g_v_person||':'||l_v_person_number);
872
873 --Validate if the person has a Payment Plan with status as Planned, if so, no further proceesing
874 --required for the person and the process should proceed to the next person member of the group skipping
875 --the person being processed.
876 OPEN cur_pp_planned(l_n_person_id);
877 FETCH cur_pp_planned INTO l_v_var;
878 IF cur_pp_planned%FOUND THEN
879 --Log the error message.
880 fnd_message.set_name('IGS','IGS_FI_PP_NO_NEW_ASSIGN');
881 l_v_pers_message := fnd_message.get;
882 l_b_planned_plan := TRUE;
883 --and retcode need to be set to 1 to complete the process also in Warning status.
884 l_v_pers_status := g_v_error;
885 retcode := 1;
886 END IF;
887 CLOSE cur_pp_planned;
888
889 --Validate if the Start Date passed as input to the process is earlier than the End Date of
890 --any plan for the person.
891 OPEN cur_validate_start_dt(l_n_person_id,l_d_start_date);
892 FETCH cur_validate_start_dt INTO l_v_var;
893 IF cur_validate_start_dt%FOUND THEN
894 --Log the error message.
895 fnd_message.set_name('IGS','IGS_FI_PP_END_DATE_EARLY');
896 fnd_message.set_token('PERSON_NUMBER',l_v_person_number);
897 l_v_pers_message := l_v_pers_message || fnd_message.get;
898 l_b_planned_plan := TRUE;
899 l_v_pers_status := g_v_error;
900 retcode := 1;
901 END IF;
902 CLOSE cur_validate_start_dt;
903
904 IF NOT l_b_planned_plan THEN
905 BEGIN
906 --Nullify the IN/OUT variables l_rowid and l_n_installment_id before inserting a row in
907 --Student Payment Plans Table.
908 l_rowid := NULL;
909 l_n_student_plan_id := NULL;
910 rec_cur_pmt_plan.processing_fee_amt:=igs_fi_gen_gl.get_formatted_amount(rec_cur_pmt_plan.processing_fee_amt);
911 --For the Person being processed, create a record in the IGS_FI_PP_STD_ATTRS table.
912 igs_fi_pp_std_attrs_pkg.insert_row(x_rowid => l_rowid,
913 x_student_plan_id => l_n_student_plan_id,
914 x_person_id => l_n_person_id,
915 x_payment_plan_name => p_v_payment_plan_name,
916 x_plan_start_date => TRUNC(l_d_start_date),
917 x_plan_end_date => l_d_prev_due_dt,
918 x_plan_status_code => g_v_planned,
919 x_processing_fee_amt => rec_cur_pmt_plan.processing_fee_amt,
920 x_processing_fee_type => rec_cur_pmt_plan.processing_fee_type,
921 x_fee_cal_type => l_v_fee_cal_type,
922 x_fee_ci_sequence_number => l_n_fee_ci_sequence_number,
923 x_notes => NULL,
924 x_invoice_id => NULL,
925 x_attribute_category => NULL,
926 x_attribute1 => l_dff_seg_values.attribute1,
927 x_attribute2 => l_dff_seg_values.attribute2,
928 x_attribute3 => l_dff_seg_values.attribute3,
929 x_attribute4 => l_dff_seg_values.attribute4,
930 x_attribute5 => l_dff_seg_values.attribute5,
931 x_attribute6 => l_dff_seg_values.attribute6,
932 x_attribute7 => l_dff_seg_values.attribute7,
933 x_attribute8 => l_dff_seg_values.attribute8,
934 x_attribute9 => l_dff_seg_values.attribute9,
935 x_attribute10 => l_dff_seg_values.attribute10,
936 x_attribute11 => l_dff_seg_values.attribute11,
937 x_attribute12 => l_dff_seg_values.attribute12,
938 x_attribute13 => l_dff_seg_values.attribute13,
939 x_attribute14 => l_dff_seg_values.attribute14,
940 x_attribute15 => l_dff_seg_values.attribute15,
941 x_attribute16 => l_dff_seg_values.attribute16,
942 x_attribute17 => l_dff_seg_values.attribute17,
943 x_attribute18 => l_dff_seg_values.attribute18,
944 x_attribute19 => l_dff_seg_values.attribute19,
945 x_attribute20 => l_dff_seg_values.attribute20,
946 x_mode => 'R'
947 );
948
949 -- Check if there are any records in the temporary PL/SQL table.
950 IF l_temp_plsql_tbl.COUNT > 0 THEN
951
952 l_n_cnt := 0;
953
954 --Loop through the temporary PL/SQL Table for creation of Student Payment Plan Line Records.
955 FOR l_n_cnt IN l_temp_plsql_tbl.FIRST..l_temp_plsql_tbl.LAST LOOP
956
957 IF l_temp_plsql_tbl.EXISTS(l_n_cnt) THEN
958
959 --Nullify the IN/OUT variables l_rowid and l_n_installment_id before inserting
960 --a record in igs_fi_pp_instlmnts table.
961 l_rowid := NULL;
962 l_n_installment_id := NULL;
963
964 l_temp_plsql_tbl(l_n_cnt).plan_amount :=igs_fi_gen_gl.get_formatted_amount(l_temp_plsql_tbl(l_n_cnt).plan_amount);
965 igs_fi_pp_instlmnts_pkg.insert_row(
966 x_rowid => l_rowid,
967 x_installment_id => l_n_installment_id,
968 x_student_plan_id => l_n_student_plan_id,
969 x_installment_line_num => l_temp_plsql_tbl(l_n_cnt).plan_line_num,
970 x_due_day => TO_NUMBER(TO_CHAR(l_temp_plsql_tbl(l_n_cnt).due_date,'DD')),
971 x_due_month_code => TO_CHAR(l_temp_plsql_tbl(l_n_cnt).due_date,'MON'),
972 x_due_year => TO_NUMBER(TO_CHAR(l_temp_plsql_tbl(l_n_cnt).due_date,'YYYY')),
973 x_due_date => TRUNC(l_temp_plsql_tbl(l_n_cnt).due_date),
974 x_installment_amt => NVL(l_temp_plsql_tbl(l_n_cnt).plan_amount,0),
975 x_due_amt => NVL(l_temp_plsql_tbl(l_n_cnt).plan_amount,0),
976 x_penalty_flag => g_v_ind_no,
977 x_mode => 'R'
978 );
979 END IF;
980 END LOOP;
981 END IF;
982
983 --After successful creation of records, log the status as success.
984 IF l_v_pers_status IS NULL THEN
985 l_v_pers_status := g_v_success;
986 END IF;
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 --In case TBH throws any errors, log the error status.
991 l_v_pers_status := g_v_error;
992 l_v_pers_message := l_v_pers_message ||fnd_message.get;
993 END;
994 END IF;
995 fnd_file.put_line(fnd_file.log,g_v_pp_status||':'||l_v_pers_status);
996 IF l_v_pers_message IS NOT NULL THEN
997 fnd_file.put_line(fnd_file.log,LTRIM(l_v_pers_message,'.'));
998 END IF;
999 fnd_file.new_line(fnd_file.log);
1000 END LOOP;
1001 CLOSE l_cur_ref;
1002
1003 --If person group passed as input does not have any members associated,
1004 --then process should log the message ***No Data Found***.
1005 IF NOT l_b_no_rec THEN
1006 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
1007 fnd_file.put_line(fnd_file.log,fnd_message.get);
1008 fnd_file.new_line(fnd_file.log);
1009 END IF;
1010
1011 EXCEPTION
1012 --Handle the Locking exception.
1013 WHEN e_resource_busy THEN
1014 ROLLBACK TO sp_assign_student;
1015 retcode := 2;
1016 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
1017 fnd_file.put_line(fnd_file.log,fnd_message.get);
1018 fnd_file.new_line(fnd_file.log);
1019
1020 WHEN OTHERS THEN
1021 ROLLBACK TO sp_assign_student;
1022 retcode := 2;
1023 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' - '||SQLERRM);
1024 END assign_students;
1025
1026 FUNCTION close_active_pp(p_n_person_id IN igs_fi_parties_v.person_id%TYPE,
1027 p_n_tolerance_threshold IN NUMBER) RETURN NUMBER IS
1028 ------------------------------------------------------------------
1029 --Created by : vvutukur, Oracle IDC
1030 --Date created: 10-Sep-2003
1031 --
1032 --Purpose:
1033 --
1034 --Known limitations/enhancements and/or remarks:
1035 --
1036 --Change History:
1037 --Who When What
1038 --abshriva 12-May-2006 Bug 5217319: Amount Precision change, added API call to allow correct precison into DB
1039 -------------------------------------------------------------------
1040 --Cursor to fetch the Student ACTIVE Payment Plan details.
1041
1042 --The following cursor will fetch a single record(ACTIVE Payment Plan details) if the input parameter to the cursor
1043 --i.e., cp_n_person_id is passed as not null. In case the input parameter to the cursor is passed as null,
1044 --the cursor returns multiple ACTIVE Payment Plan records for different Students.
1045 --(note: One Student Can have only one Active Payment Plan at a time).
1046 CURSOR cur_pp_dtls(cp_n_person_id igs_fi_pp_std_attrs.person_id%TYPE) IS
1047 SELECT a.rowid,a.*
1048 FROM igs_fi_pp_std_attrs a
1049 WHERE (a.person_id = cp_n_person_id OR
1050 cp_n_person_id IS NULL)
1051 AND a.plan_status_code = g_v_active
1052 FOR UPDATE NOWAIT;
1053
1054 rec_cur_pp_dtls cur_pp_dtls%ROWTYPE;
1055
1056 --Cursor to fetch the details of all the Installments of a Student Payment Plan.
1057 CURSOR cur_pp_insts(cp_n_std_pln_id igs_fi_pp_instlmnts.student_plan_id%TYPE) IS
1058 SELECT a.rowid,a.*
1059 FROM igs_fi_pp_instlmnts a
1060 WHERE a.student_plan_id = cp_n_std_pln_id
1061 FOR UPDATE NOWAIT;
1062
1063 l_n_balance igs_fi_pp_instlmnts.due_amt%TYPE;
1064 l_v_closed CONSTANT igs_fi_pp_std_attrs.plan_status_code%TYPE := 'CLOSED';
1065 l_n_inst_bal igs_fi_pp_instlmnts.installment_amt%TYPE;
1066 l_v_end_dt_msg VARCHAR2(2000);
1067 l_n_count NUMBER :=0;
1068 l_v_plan_name CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PLAN_NAME');
1069 l_v_plan_bal CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PLAN_BAL');
1070 l_v_end_dt CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','END_DT');
1071
1072 BEGIN
1073
1074 --Loop through all the persons.
1075 FOR rec_cur_pp_dtls IN cur_pp_dtls(p_n_person_id) LOOP
1076
1077 l_n_count := l_n_count + 1;
1078
1079 --Log the Student Payment Plan details.
1080 fnd_file.put_line(fnd_file.log,g_v_person||': '||igs_fi_gen_008.get_party_number(rec_cur_pp_dtls.person_id));
1081 fnd_file.put_line(fnd_file.log,l_v_plan_name||': '||rec_cur_pp_dtls.payment_plan_name);
1082
1083 --Calculate the Outstanding Balance of the Active Payment Plan of the Student, by calling the generic function.
1084 l_n_balance := igs_fi_gen_008.get_plan_balance(p_n_act_plan_id => rec_cur_pp_dtls.student_plan_id,
1085 p_d_effective_date => NULL
1086 );
1087 --Log the Outstanding Balance.
1088 fnd_file.put_line(fnd_file.log,l_v_plan_bal||': '||l_n_balance);
1089 l_v_end_dt_msg := l_v_end_dt||': ';
1090
1091 --If the Payment Plan Outstanding Balance is less than or equal to the threshold amount parameter,
1092 --passed as input to this process.
1093 IF NVL(l_n_balance,0) <= NVL(p_n_tolerance_threshold,0) THEN
1094 --if so, update the Payment Plan status(plan_status_code column) of igs_fi_pp_std_attrs table to a
1095 --status of 'CLOSED' and also Update the End Date(plan_end_date column) of the Plan with the System Date.
1096 BEGIN
1097 igs_fi_pp_std_attrs_pkg.update_row(
1098 x_rowid => rec_cur_pp_dtls.rowid,
1099 x_student_plan_id => rec_cur_pp_dtls.student_plan_id,
1100 x_person_id => rec_cur_pp_dtls.person_id,
1101 x_payment_plan_name => rec_cur_pp_dtls.payment_plan_name,
1102 x_plan_start_date => rec_cur_pp_dtls.plan_start_date,
1103 x_plan_end_date => g_d_sysdate,
1104 x_plan_status_code => l_v_closed,
1105 x_processing_fee_amt => rec_cur_pp_dtls.processing_fee_amt,
1106 x_processing_fee_type => rec_cur_pp_dtls.processing_fee_type,
1107 x_fee_cal_type => rec_cur_pp_dtls.fee_cal_type,
1108 x_fee_ci_sequence_number => rec_cur_pp_dtls.fee_ci_sequence_number,
1109 x_notes => rec_cur_pp_dtls.notes,
1110 x_invoice_id => rec_cur_pp_dtls.invoice_id,
1111 x_attribute_category => rec_cur_pp_dtls.attribute_category,
1112 x_attribute1 => rec_cur_pp_dtls.attribute1,
1113 x_attribute2 => rec_cur_pp_dtls.attribute2,
1114 x_attribute3 => rec_cur_pp_dtls.attribute3,
1115 x_attribute4 => rec_cur_pp_dtls.attribute4,
1116 x_attribute5 => rec_cur_pp_dtls.attribute5,
1117 x_attribute6 => rec_cur_pp_dtls.attribute6,
1118 x_attribute7 => rec_cur_pp_dtls.attribute7,
1119 x_attribute8 => rec_cur_pp_dtls.attribute8,
1120 x_attribute9 => rec_cur_pp_dtls.attribute9,
1121 x_attribute10 => rec_cur_pp_dtls.attribute10,
1122 x_attribute11 => rec_cur_pp_dtls.attribute11,
1123 x_attribute12 => rec_cur_pp_dtls.attribute12,
1124 x_attribute13 => rec_cur_pp_dtls.attribute13,
1125 x_attribute14 => rec_cur_pp_dtls.attribute14,
1126 x_attribute15 => rec_cur_pp_dtls.attribute15,
1127 x_attribute16 => rec_cur_pp_dtls.attribute16,
1128 x_attribute17 => rec_cur_pp_dtls.attribute17,
1129 x_attribute18 => rec_cur_pp_dtls.attribute18,
1130 x_attribute19 => rec_cur_pp_dtls.attribute19,
1131 x_attribute20 => rec_cur_pp_dtls.attribute20,
1132 x_mode => 'R');
1133
1134 --Loop through all the Payment Plan Installment records and update the installment_amt column to
1135 --a value (installment_amt - due_amt) of the same Payment Plan Installment record.
1136 --Also update the due_amt to 0 for the each record.
1137 FOR rec_cur_pp_insts IN cur_pp_insts(rec_cur_pp_dtls.student_plan_id) LOOP
1138
1139 --Calculate the value to be updated in the column installment_amt.
1140 l_n_inst_bal := NVL(rec_cur_pp_insts.installment_amt,0) - NVL(rec_cur_pp_insts.due_amt,0);
1141
1142 l_n_inst_bal :=igs_fi_gen_gl.get_formatted_amount(l_n_inst_bal);
1143 igs_fi_pp_instlmnts_pkg.update_row(
1144 x_rowid => rec_cur_pp_insts.rowid,
1145 x_installment_id => rec_cur_pp_insts.installment_id,
1146 x_student_plan_id => rec_cur_pp_insts.student_plan_id,
1147 x_installment_line_num => rec_cur_pp_insts.installment_line_num,
1148 x_due_day => rec_cur_pp_insts.due_day,
1149 x_due_month_code => rec_cur_pp_insts.due_month_code,
1150 x_due_year => rec_cur_pp_insts.due_year,
1151 x_due_date => rec_cur_pp_insts.due_date,
1152 x_installment_amt => l_n_inst_bal,
1153 x_due_amt => 0,
1154 x_penalty_flag => rec_cur_pp_insts.penalty_flag,
1155 x_mode => 'R');
1156 END LOOP;
1157 l_v_end_dt_msg := l_v_end_dt_msg||g_d_sysdate;
1158 EXCEPTION
1159 WHEN OTHERS THEN
1160 l_v_end_dt_msg := l_v_end_dt_msg||fnd_message.get;
1161 END;
1162
1163 ELSE--If the outstanding balance for the student is greater than the p_n_tolerance_threshold parameter..
1164
1165 --Against the End Date placeholder,log the message that Payment Plan does not meet the Criteria and
1166 --Remains Active and Process the next Student.
1167
1168 fnd_message.set_name('IGS','IGS_FI_PP_NOT_CLOSED');
1169 l_v_end_dt_msg := l_v_end_dt_msg||fnd_message.get;
1170 END IF;
1171
1172 --Log the end date details.
1173 fnd_file.put_line(fnd_file.log,l_v_end_dt_msg);
1174 fnd_file.new_line(fnd_file.log);
1175
1176 END LOOP;
1177 RETURN l_n_count;
1178
1179 END close_active_pp;
1180
1181 PROCEDURE close_status(errbuf OUT NOCOPY VARCHAR2,
1182 retcode OUT NOCOPY NUMBER,
1183 p_n_tolerance_threshold IN NUMBER,
1184 p_n_person_id_grp IN igs_pe_persid_group_all.group_id%TYPE,
1185 p_v_test_mode IN VARCHAR2
1186 ) AS
1187 ------------------------------------------------------------------
1188 --Created by : vvutukur, Oracle IDC
1189 --Date created: 27-Aug-2003
1190 --
1191 --Purpose: Concurrent process that closes the Payment Plans.
1192 --
1193 --Known limitations/enhancements and/or remarks:
1194 --
1195 --Change History:
1196 --Who When What
1197 --abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
1198 -------------------------------------------------------------------
1199 --Ref cursor variable.
1200 l_cur_ref cur_ref;
1201
1202 l_v_meaning igs_lookup_values.meaning%TYPE;
1203 l_v_sql VARCHAR2(32767);
1204 l_v_status VARCHAR2(1);
1205 l_v_var VARCHAR2(1);
1206 l_b_valid_param BOOLEAN := TRUE;
1207 l_v_manage_acc igs_fi_control.manage_accounts%TYPE := NULL;
1208 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
1209 l_n_person_id igs_fi_parties_v.person_id%TYPE;
1210
1211 l_v_tol_thr CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TOLERANCE_THRESHOLD');
1212 l_v_test_mode CONSTANT igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TEST_MODE');
1213 l_n_count PLS_INTEGER := 0;
1214 l_n_cnt PLS_INTEGER;
1215 l_org_id VARCHAR2(15);
1216 BEGIN
1217
1218 BEGIN
1219 l_org_id := NULL;
1220 igs_ge_gen_003.set_org_id(l_org_id);
1221 EXCEPTION
1222 WHEN OTHERS THEN
1223 fnd_file.put_line (fnd_file.log, fnd_message.get);
1224 retcode:=2;
1225 RETURN;
1226 END;
1227 --Create the savepoint for rollback.
1228 SAVEPOINT sp_close_status;
1229
1230 retcode := 0;
1231 errbuf := NULL;
1232
1233 --Log the input parameters.
1234 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
1235 fnd_file.put_line(fnd_file.log,fnd_message.get||':');
1236 fnd_file.new_line(fnd_file.log);
1237
1238 fnd_file.put_line(fnd_file.log,l_v_tol_thr||':'||p_n_tolerance_threshold);
1239 fnd_file.put_line(fnd_file.log,g_v_person_group||':'||igs_fi_gen_005.finp_get_prsid_grp_code(p_n_person_id_grp));
1240 fnd_file.put_line(fnd_file.log,l_v_test_mode||':'||p_v_test_mode);
1241
1242 fnd_file.new_line(fnd_file.log,1);
1243
1244 -- Obtain the value of manage_accounts in the System Options form
1245 -- If it is null or 'OTHER', then this process is not available, so error out.
1246 igs_fi_com_rec_interface.chk_manage_account( p_v_manage_acc => l_v_manage_acc,
1247 p_v_message_name => l_v_message_name
1248 );
1249 IF (l_v_manage_acc = 'OTHER' OR l_v_manage_acc IS NULL) THEN
1250 fnd_message.set_name('IGS',l_v_message_name);
1251 fnd_file.put_line(fnd_file.log,fnd_message.get);
1252 retcode := 2;
1253 RETURN;
1254 END IF;
1255
1256 --Tolerance Threshold and Test Mode are mandatory parameters to this process.
1257 --If they are null, log the error message in the log file and error out the process.
1258 IF p_n_tolerance_threshold IS NULL OR p_v_test_mode IS NULL THEN
1259 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
1260 fnd_file.put_line(fnd_file.log,fnd_message.get);
1261 l_b_valid_param := FALSE;
1262 END IF;
1263
1264 --Tolerance Threshold parameter cannot be negative, if so, log the error message in the log file
1265 --and error out the process.
1266 IF NVL(p_n_tolerance_threshold,0) < 0 THEN
1267 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1268 fnd_message.set_token('PARAMETER',l_v_tol_thr);
1269 fnd_file.put_line(fnd_file.log,fnd_message.get);
1270 l_b_valid_param := FALSE;
1271 END IF;
1272
1273 --Validate the Person Group input parameter, if passed as not null.
1274 IF p_n_person_id_grp IS NOT NULL THEN
1275 OPEN cur_pers_grp(p_n_person_id_grp);
1276 FETCH cur_pers_grp INTO l_v_var;
1277 --If the person group passed is not valid, then log the error message in the log file and error out the process.
1278 IF cur_pers_grp%NOTFOUND THEN
1279 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1280 fnd_message.set_token('PARAMETER',g_v_person_group);
1281 fnd_file.put_line(fnd_file.log,fnd_message.get);
1282 l_b_valid_param := FALSE;
1283 END IF;
1284 CLOSE cur_pers_grp;
1285 END IF;
1286
1287 --Validate the Test Mode parameter is passed as not null.
1288 IF p_v_test_mode IS NOT NULL THEN
1289 --Validate whether the passed p_v_test_mode parameter is a valid lookup.
1290 l_v_meaning := igs_fi_gen_gl.get_lkp_meaning('VS_AS_YN',p_v_test_mode);
1291 --If it is not a valid lookup, then log the error message in the log file and error out the process.
1292 IF l_v_meaning IS NULL THEN
1293 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
1294 fnd_message.set_token('PARAMETER',l_v_test_mode);
1295 fnd_file.put_line(fnd_file.log,fnd_message.get);
1296 l_b_valid_param := FALSE;
1297 END IF;
1298 END IF;
1299
1300 --If any of the above parameter validations fail, error out the process.
1301 IF NOT l_b_valid_param THEN
1302 retcode := 2;
1303 RETURN;
1304 END IF;
1305
1306 l_n_count := 0;
1307
1308 --If Person Group parameter passed as input to the process is not null, then identify all the Persons
1309 --that are members of this group using generic function.
1310 IF p_n_person_id_grp IS NOT NULL THEN
1311 l_v_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_groupid => p_n_person_id_grp,
1312 p_status => l_v_status
1313 );
1314 --If the sql returned is invalid, then..
1315 IF l_v_status <> 'S' THEN
1316 --Log the error message the stop processing.
1317 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1318 fnd_file.put_line(fnd_file.log,fnd_message.get);
1319 fnd_file.put_line(fnd_file.log,l_v_sql);
1320 retcode := 2;
1321 RETURN;
1322 END IF;
1323
1324 --Execute the sql statement using ref cursor.
1325 OPEN l_cur_ref FOR l_v_sql;
1326 LOOP
1327 --Fetch the person id into local variable.
1328 FETCH l_cur_ref INTO l_n_person_id;
1329 EXIT WHEN l_cur_ref%NOTFOUND;
1330
1331 l_n_count := l_n_count + 1;
1332
1333 --Check if the person has an active payment plan, otherwise, log the error message
1334 --and process the next person in the group.
1335 IF igs_fi_gen_008.chk_active_pay_plan(l_n_person_id) = 'N' THEN
1336
1337 --Log the person number and error message conveying that the person does not have any active payment plan.
1338 fnd_file.put_line(fnd_file.log,g_v_person||': '||igs_fi_gen_008.get_party_number(l_n_person_id));
1339 fnd_message.set_name('IGS','IGS_FI_PP_NO_ACT_PLANS');
1340 fnd_file.put_line(fnd_file.log,fnd_message.get);
1341 fnd_file.new_line(fnd_file.log);
1342 ELSE
1343 --Call the local function for closing the active payment plan record.
1344 l_n_cnt := close_active_pp(p_n_person_id => l_n_person_id,
1345 p_n_tolerance_threshold => p_n_tolerance_threshold);
1346 END IF;
1347 END LOOP;
1348 CLOSE l_cur_ref;
1349
1350 ELSE--if input parameter person id group is passed as null.
1351
1352 --Call the local function for closing the active payment plan records.
1353 --In this case, as person id group is passed as null, this local function
1354 --will return the number of persons processed.
1355 l_n_count := close_active_pp(p_n_person_id => NULL,
1356 p_n_tolerance_threshold => p_n_tolerance_threshold);
1357 END IF;
1358
1359 --If Test Mode flag is not Y, then all the transactions need to be committed else
1360 --rollback to the savepoint.
1361 IF p_v_test_mode <> g_v_ind_yes THEN
1362 COMMIT;
1363 ELSE
1364 IF l_n_count <> 0 THEN
1365 fnd_message.set_name('IGS','IGS_FI_PRC_TEST_RUN');
1366 fnd_file.put_line(fnd_file.log,fnd_message.get);
1367 ROLLBACK TO sp_close_status;
1368 END IF;
1369 END IF;
1370
1371 --If there are no persons to be processed...
1372 --then process should log the message ***No Data Found***.
1373 IF l_n_count = 0 THEN
1374 fnd_message.set_name('IGS','IGS_GE_NO_DATA_FOUND');
1375 fnd_file.put_line(fnd_file.log,fnd_message.get);
1376 fnd_file.new_line(fnd_file.log);
1377 END IF;
1378
1379 fnd_file.new_line(fnd_file.log);
1380
1381 EXCEPTION
1382 --Handle the Locking exception.
1383 WHEN e_resource_busy THEN
1384 ROLLBACK TO sp_close_status;
1385 retcode := 2;
1386 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
1387 fnd_file.put_line(fnd_file.log,fnd_message.get);
1388 fnd_file.new_line(fnd_file.log);
1389
1390 WHEN OTHERS THEN
1391 ROLLBACK TO sp_close_status;
1392 retcode := 2;
1393 fnd_file.put_line(fnd_file.log,fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' - '||SQLERRM);
1394 END close_status;
1395
1396 END igs_fi_payment_plans;
[Home] [Help]