1 PACKAGE BODY igs_fi_prc_sp_fees AS
2 /* $Header: IGSFI89B.pls 120.9 2006/06/28 06:15:49 akandreg ship $ */
3 /************************************************************************
4 Created By : Priya Athipatla
5 Date Created By : 15-Oct-2003
6 Purpose : Core Routine for Special Fees - Invoked from the Concurrent
7 Process and Self Service package
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 akandreg 27-Jun-2006 Bug 5104339 -Modified procedure assess_fees and validate_params
13 skharida 16-JUN-06 Bug 5094077 - Modified the procedure validate_params to output correct log messages.
14 akandreg 09-Jun-2006 Bug 5107755 - Replaced the cursor c_get_alt_code by the cursor c_get_alt_code_desc,
15 which queries both alternate code and description so that both of them
16 can be logged into the log file.
17
18 akandreg 25-May-2006 Bug 5134636 - Modified process_special_fees
19 Added new functions fisp_lock_records , fisp_insert_record
20 abshriva 17-May-2006 Bug 5113295 - Modified assess_fees_pvt: Added invocation of function chk_unit_prg_transfer
21 abshriva 12-MAy-2006 Bug 5217319 Amount precision change in assess_fees_pvt
22 abshriva 5 May-2006 Bug 5178077: Modification done in assess_fees
23 uudayapr 14-Sep-2005 Bug 4609164 - Modfied call_charges_api to passes the Unit level values
24 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
25 Impact of Charges API version Number change
26 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
27 svuppala 29-MAR-05 Bug 4240402 Timezone impact; Truncating the time part in calling place of the table handlers
28 of the table IGS_FI_SPECIAL_FEES.
29 Modified the sysdate entries as Trunc(Sysdate).
30 uudayapr 21-Mar-05 Bug#4224392 Modified call_charges_api
31 rmaddipa 20-Sep-04 Enh#3880438 Modified assess_fees_pvt
32 rmaddipa 26-July-04 Enh#3787816 Manual Reversal Build
33 Modified assess_fees_pvt
34 *************************************************************************/
35
36 g_v_seperator CONSTANT VARCHAR2(1) := '-';
37 g_v_retention CONSTANT VARCHAR2(10) := 'RETENTION';
38 g_v_special CONSTANT VARCHAR2(10) := 'SPECIAL';
39 g_v_yes CONSTANT VARCHAR2(1) := 'Y';
40 g_v_no CONSTANT VARCHAR2(1) := 'N';
41 g_v_sua_status CONSTANT VARCHAR2(20) := 'UNIT_ATTEMPT_STATUS';
42 g_v_alternatecode igs_ca_inst.alternate_code%TYPE;
43
44 FUNCTION fisp_lock_records(p_n_person_id IN igs_fi_spa_fee_prds.person_id%TYPE,
45 p_v_course_cd IN igs_fi_spa_fee_prds.course_cd%TYPE,
46 p_v_fee_cal_type IN igs_fi_spa_fee_prds.fee_cal_type%TYPE,
47 p_n_fee_ci_sequence_number IN igs_fi_spa_fee_prds.fee_ci_sequence_number%TYPE) RETURN BOOLEAN;
48
49 PROCEDURE log_details(p_v_person_number IN hz_parties.party_number%TYPE,
50 p_v_fee_period IN VARCHAR2,
51 p_v_unit_section_desc IN VARCHAR2,
52 p_v_fee_type IN igs_fi_fee_type.fee_type%TYPE)AS
53 /******************************************************************
54 Created By : Priya Athipatla
55 Date Created By : 15-Oct-2003
56 Purpose : Logs details as follows-
57 Person Number: <Value>
58 Fee Assessment Period: <Value>
59 Unit Section: <Value>
60 Fee Type: <Value>
61 Known limitations,enhancements,remarks:
62 Change History
63 Who When What
64 akandreg 09-Jun-2006 Bug 5107755 - Modified signature of the method by passing parameter
65 p_v_fee_period instead of p_v_fee_period_alt_code.
66
67 ******************************************************************/
68 BEGIN
69 -- Seperator: ------------
70 fnd_message.set_name('IGS','IGS_FI_END_DATE');
71 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
72 fnd_msg_pub.add;
73 -- Person Number:
74 fnd_message.set_name('IGS','IGS_FI_END_DATE');
75 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||p_v_person_number);
76 fnd_msg_pub.add;
77 --Logging Fee Assessment Period.
78 fnd_message.set_name('IGS','IGS_FI_END_DATE');
79 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_ASS_PERIOD')||': '|| p_v_fee_period );
80 fnd_msg_pub.add;
81 -- Unit Section:
82 fnd_message.set_name('IGS','IGS_FI_END_DATE');
83 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','USEC')||': '||p_v_unit_section_desc);
84 fnd_msg_pub.add;
85 -- Fee Type:
86 IF p_v_fee_type IS NOT NULL THEN
87 fnd_message.set_name('IGS','IGS_FI_END_DATE');
88 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_TYPE')||': '||p_v_fee_type);
89 fnd_msg_pub.add;
90 END IF;
91 END log_details;
92
93
94 PROCEDURE call_charges_api(p_n_person_id IN hz_parties.party_id%TYPE,
95 p_v_fee_type IN igs_fi_f_typ_ca_inst.fee_type%TYPE,
96 p_v_fee_cal_type IN igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
97 p_n_fee_ci_sequence_number IN igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
98 p_v_course_cd IN igs_ps_ver.course_cd%TYPE,
99 p_n_uoo_id IN igs_en_su_attempt.uoo_id%TYPE,
100 p_n_amount IN igs_fi_invln_int.amount%TYPE,
101 p_v_transaction_type IN igs_fi_inv_int.transaction_type%TYPE,
102 p_v_currency_cd IN igs_fi_control.currency_cd%TYPE,
103 p_d_gl_date IN igs_fi_invln_int.gl_date%TYPE,
104 p_n_source_invoice_id IN igs_fi_inv_int.invoice_id%TYPE,
105 p_v_sua_status IN igs_en_su_attempt.unit_attempt_status%TYPE,
106 p_n_invoice_id OUT NOCOPY igs_fi_inv_int.invoice_id%TYPE,
107 p_v_ret_status OUT NOCOPY VARCHAR2) AS
108 /******************************************************************
109 Created By : Priya Athipatla
110 Date Created By : 15-Oct-2003
111 Purpose : Invokes Charges API for creating a charge
112 Known limitations,enhancements,remarks:
113 Change History
114 Who When What
115 uudayapr 14-Sep-2005 Bug 4609164 -Added the Cursor c_unit_level_detail to retive the unit level details
116 to be passed to the Charges Api.
117 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
118 Impact of Charges API version Number change
119 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
120 uudayapr 21-MAR-05 Bug# 4224392 Added the cursors c_org_unit_cd to retreive the
121 Org_cd and location cd and pass it to the create_charge.
122 ******************************************************************/
123 CURSOR cur_fee_type_desc(cp_v_fee_type igs_fi_fee_type.fee_type%TYPE) IS
124 SELECT description
125 FROM igs_fi_fee_type
126 WHERE fee_type = cp_v_fee_type;
127
128 --Cursor to select the org unit cd and location cd from igs_en_su_attempt
129 CURSOR c_org_unit_cd(cp_person_id IN igs_en_su_attempt_all.person_id%TYPE,
130 cp_course_cd in igs_en_su_attempt_all.course_cd%TYPE,
131 cp_uoo_id IN igs_en_su_attempt_all.uoo_id%TYPE
132 ) IS
133 SELECT org_unit_cd ,location_cd
134 FROM igs_en_su_attempt su
135 WHERE su.person_id = cp_person_id
136 AND su.course_cd = cp_course_cd
137 AND su.uoo_id = cp_uoo_id;
138
139 --Cursor to select the org unit cd whene it is not identified in the c_org_unit_cd cursor.
140
141 CURSOR c_org_unit_sec_cd(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
142 SELECT owner_org_unit_cd
143 FROM igs_ps_unit_ofr_opt uoo
144 WHERE uoo_id = cp_uoo_id;
145
146 --cursor to select the Unit Program Type Level, Unit Class and Unit Mode attributes when creating
147 --a Special Fee charge.
148 CURSOR c_unit_level_detail(cp_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
149 SELECT uv.unit_type_id,
150 asuc.unit_class,
151 asuc.unit_mode,
152 uv.unit_level
153 FROM igs_ps_unit_ver uv,
154 igs_ps_unit_ofr_opt_all uoo,
155 igs_as_unit_class asuc
156 WHERE uv.unit_cd = uoo.unit_cd
157 AND uv.version_number = uoo.version_number
158 AND asuc.unit_class = uoo.unit_class
159 AND uoo.uoo_id = cp_uoo_id;
160
161 l_v_fee_type_desc igs_fi_fee_type.description%TYPE := NULL;
162 l_rec_chg_header igs_fi_charges_api_pvt.header_rec_type;
163 l_rec_chg_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
164 l_rec_chg_line_id_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
165 l_v_return_status VARCHAR2(1) := NULL;
166 l_n_msg_count NUMBER := 0;
167 l_v_msg_data VARCHAR2(4000) := NULL;
168
169 --local parameters to hold the org unit code and loaction code values from the cursor.
170 l_rec_cur_org_unit_cd c_org_unit_cd%ROWTYPE;
171 l_v_derived_org_unit_cd igs_en_su_attempt_all.org_unit_cd%TYPE;
172 --CUROSR
173 l_c_unit_level_detail c_unit_level_detail%ROWTYPE;
174
175 l_n_waiver_amount NUMBER;
176
177 BEGIN
178
179 OPEN cur_fee_type_desc(p_v_fee_type);
180 FETCH cur_fee_type_desc INTO l_v_fee_type_desc;
181 CLOSE cur_fee_type_desc;
182
183 -- To derive the org unit code and location code from igs_en_su_attempt table
184 l_v_derived_org_unit_cd := NULL;
185 OPEN c_org_unit_cd(p_n_person_id,
186 p_v_course_cd,
187 p_n_uoo_id);
188 FETCH c_org_unit_cd INTO l_rec_cur_org_unit_cd;
189 CLOSE c_org_unit_cd;
190
191 --if org unit code is not derived from the student unit attempts then derive it from the
192 --Unit Section table of IGS_PS_UNIT_OFR_OPT.
193 IF l_rec_cur_org_unit_cd.org_unit_cd IS NULL THEN
194 OPEN c_org_unit_sec_cd(p_n_uoo_id);
195 FETCH c_org_unit_sec_cd INTO l_v_derived_org_unit_cd;
196 CLOSE c_org_unit_sec_cd;
197 ELSE
198 l_v_derived_org_unit_cd := l_rec_cur_org_unit_cd.org_unit_cd;
199 END IF;
200 --Code Logic for Getting the Unit level details.
201 OPEN c_unit_level_detail(p_n_uoo_id);
202 FETCH c_unit_level_detail INTO l_c_unit_level_detail;
203 CLOSE c_unit_level_detail;
204
205 l_rec_chg_header.p_person_id := p_n_person_id;
206 l_rec_chg_header.p_fee_type := p_v_fee_type;
207 l_rec_chg_header.p_fee_cal_type := p_v_fee_cal_type;
208 l_rec_chg_header.p_fee_ci_sequence_number := p_n_fee_ci_sequence_number;
209 l_rec_chg_header.p_course_cd := p_v_course_cd;
210 l_rec_chg_header.p_invoice_amount := p_n_amount;
211 l_rec_chg_header.p_transaction_type := p_v_transaction_type;
212 l_rec_chg_header.p_currency_cd := p_v_currency_cd;
213 l_rec_chg_header.p_invoice_creation_date := TRUNC(SYSDATE);
214 l_rec_chg_header.p_effective_date := TRUNC(SYSDATE);
215 l_rec_chg_header.p_source_transaction_id := p_n_source_invoice_id;
216 l_rec_chg_header.p_invoice_desc := l_v_fee_type_desc;
217
218 l_rec_chg_line_tbl(1).p_uoo_id := p_n_uoo_id;
219 l_rec_chg_line_tbl(1).p_d_gl_date := p_d_gl_date;
220 l_rec_chg_line_tbl(1).p_amount := p_n_amount;
221 l_rec_chg_line_tbl(1).p_description := l_v_fee_type_desc;
222 l_rec_chg_line_tbl(1).p_unit_attempt_status := p_v_sua_status;
223 -- Set the value of Location Code and org unit code
224 l_rec_chg_line_tbl(1).p_location_cd := l_rec_cur_org_unit_cd.location_cd;
225 l_rec_chg_line_tbl(1).p_org_unit_cd := l_v_derived_org_unit_cd;
226 --setting the Values for the Unit level details
227 l_rec_chg_line_tbl(1).p_unit_type_id := l_c_unit_level_detail.unit_type_id;
228 l_rec_chg_line_tbl(1).p_unit_class := l_c_unit_level_detail.unit_class;
229 l_rec_chg_line_tbl(1).p_unit_mode := l_c_unit_level_detail.unit_mode;
230 l_rec_chg_line_tbl(1).p_unit_level := l_c_unit_level_detail.unit_level;
231
232 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
233 p_init_msg_list => 'F',
234 p_commit => 'F',
235 p_validation_level => 100,
236 p_header_rec => l_rec_chg_header,
237 p_line_tbl => l_rec_chg_line_tbl,
238 x_invoice_id => p_n_invoice_id,
239 x_line_id_tbl => l_rec_chg_line_id_tbl,
240 x_return_status => p_v_ret_status,
241 x_msg_count => l_n_msg_count,
242 x_msg_data => l_v_msg_data,
243 x_waiver_amount => l_n_waiver_amount);
244
245 END call_charges_api;
246
247
248 PROCEDURE assess_fees_pvt(p_n_person_id IN PLS_INTEGER,
249 p_v_person_number IN VARCHAR2,
250 p_v_course_cd IN VARCHAR2,
251 p_n_uoo_id IN PLS_INTEGER,
252 p_v_fee_cal_type IN VARCHAR2,
253 p_n_fee_ci_sequence_number IN PLS_INTEGER,
254 p_v_fee_period IN VARCHAR2,
255 p_v_load_cal_type IN VARCHAR2,
256 p_n_load_ci_sequence_number IN PLS_INTEGER,
257 p_d_gl_date IN DATE,
258 p_b_log_messages IN BOOLEAN DEFAULT TRUE,
259 x_return_status OUT NOCOPY VARCHAR2,
260 x_msg_count OUT NOCOPY NUMBER,
261 x_msg_data OUT NOCOPY VARCHAR2) AS
262 /******************************************************************
263 Created By : Priya Athipatla
264 Date Created By : 15-Oct-2003
265 Purpose : Main routine for assessing Special Fees
266 Known limitations,enhancements,remarks:
267 Change History
268 Who When What
269 akandreg 09-Jun-2006 Bug 5107755 - Modified signature of the method by passing parameter
270 p_v_fee_period instead of p_v_fee_period_alt_code. Also passed p_v_fee_period
271 instead of p_v_fee_period_alt_code to log_details procedure log_details.
272
273 abshriva 17-May-2006 Bug 5113295 - Added invocation of function chk_unit_prg_transfer
274 Modified cursor cur_sua_status - selected dcnt_reason_cd
275 abshriva 12-May-2006 Bug 5217319:- Amount Precision change, added API call to allow correct precison into DB
276 rmaddipa 20-Sep-04 Enh#3880438 Retention Enhancement. Modified to incorporate
277 teaching period level, unit section level, Complete withdrawal retention rules.
278 rmaddipa 26-July-04 Enh#3787816
279 Modified to prevent re-assessment of manually
280 reversed charges.
281 Obsoleted the cursor CUR_FEE_DECLINED
282 ******************************************************************/
283
284 TYPE sp_fees_rec_type IS RECORD( person_id hz_parties.party_id%TYPE,
285 course_cd igs_ps_ver.course_cd%TYPE,
286 uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
287 fee_type igs_fi_fee_type.fee_type%TYPE,
288 fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
289 fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
290 old_amount igs_fi_special_fees.fee_amt%TYPE,
291 new_amount igs_fi_special_fees.fee_amt%TYPE,
292 invoice_id igs_fi_inv_int.invoice_id%TYPE);
293
294 TYPE sp_fees_tab IS TABLE OF sp_fees_rec_type INDEX BY BINARY_INTEGER;
295
296 -- plsql table initialization
297 l_sp_fees_tbl sp_fees_tab;
298
299 -- Cursor to determine sum of Special Fees for a student from the special fees table
300 -- This does not include the Retention Fees
301 CURSOR cur_get_sum_sp_fees(cp_n_person_id hz_parties.party_id%TYPE,
302 cp_v_course_cd igs_ps_ver.course_cd%TYPE,
303 cp_n_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
304 cp_v_fee_cal_type igs_fi_f_typ_ca_inst.fee_cal_type%TYPE,
305 cp_n_fee_ci_sequence_number igs_fi_f_typ_ca_inst.fee_ci_sequence_number%TYPE,
306 cp_v_transaction_type_cd igs_fi_special_fees.s_transaction_type_code%TYPE) IS
307 SELECT fee_type,
308 invoice_id,
309 SUM(fee_amt) fee_amt
310 FROM igs_fi_special_fees
311 WHERE person_id = cp_n_person_id
312 AND course_cd = cp_v_course_cd
313 AND uoo_id = cp_n_uoo_id
314 AND fee_cal_type = cp_v_fee_cal_type
315 AND fee_ci_sequence_number = cp_n_fee_ci_sequence_number
316 AND s_transaction_type_code <> cp_v_transaction_type_cd
317 GROUP BY fee_type, invoice_id;
318
319 -- Cursor to determine if the current unit attempt is assessable or not
320 CURSOR cur_unit_load(cp_n_person_id hz_parties.party_id%TYPE,
321 cp_v_course_cd igs_ps_ver.course_cd%TYPE,
322 cp_n_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE,
323 cp_v_unit_att_status igs_lookups_view.lookup_type%TYPE,
324 cp_v_fee_ass_ind igs_lookups_view.fee_ass_ind%TYPE) IS
325 SELECT sua.cal_type,
326 sua.ci_sequence_number, sua.discontinued_dt,
327 sua.administrative_unit_status, sua.unit_attempt_status,
328 sua.no_assessment_ind
329 FROM igs_en_su_attempt sua,
330 igs_lookups_view lkp
331 WHERE sua.person_id = cp_n_person_id
332 AND sua.course_cd = cp_v_course_cd
333 AND sua.uoo_id = cp_n_uoo_id
334 AND lkp.lookup_type = cp_v_unit_att_status
335 AND lkp.fee_ass_ind = cp_v_fee_ass_ind
336 AND sua.unit_attempt_status = lkp.lookup_code;
337
338 -- Cursor to determine the SUA status
339 CURSOR cur_sua_status(cp_n_person_id igs_en_su_attempt.person_id%TYPE,
340 cp_n_uoo_id igs_en_su_attempt.uoo_id%TYPE,
341 cp_v_course_cd igs_en_su_attempt_all.course_cd%TYPE) IS
342 SELECT sua.unit_attempt_status,
343 sua.discontinued_dt,
344 sua.dcnt_reason_cd
345 FROM igs_en_su_attempt sua
346 WHERE sua.person_id = cp_n_person_id
347 AND sua.uoo_id = cp_n_uoo_id
348 AND sua.course_cd = cp_v_course_cd;
349
350 -- Cursor to obtain all fee types from the Special Fees Rate setup
351 CURSOR cur_usec_sp_fees(cp_n_uoo_id igs_ps_unit_ofr_opt.uoo_id%TYPE) IS
352 SELECT sp_fee_amt,
353 fee_type
354 FROM igs_ps_usec_sp_fees
355 WHERE uoo_id = cp_n_uoo_id
356 AND closed_flag = g_v_no;
357
358 -- Variables to hold transactional values
359 l_rec_unit_load cur_unit_load%ROWTYPE;
360 l_v_currency_cd igs_fi_control.currency_cd%TYPE := NULL;
361 l_v_currency_desc fnd_currencies_tl.name%TYPE := NULL;
362 l_v_message_name fnd_new_messages.message_name%TYPE := NULL;
363 l_n_invoice_id igs_fi_inv_int.invoice_id%TYPE := NULL;
364 l_n_source_invoice_id igs_fi_inv_int.invoice_id%TYPE := NULL;
365 l_n_special_fee_id igs_fi_special_fees.special_fee_id%TYPE := NULL;
366 l_n_net_amount igs_fi_special_fees.fee_amt%TYPE := 0.0;
367 l_n_retention_amt igs_fi_special_fees.fee_amt%TYPE := 0.0;
368 l_v_unit_section_desc VARCHAR2(4000) := NULL;
369
370 -- Temporary variables
371 l_n_counter PLS_INTEGER := 0;
372 l_v_temp VARCHAR2(1) := NULL;
373 l_v_load_incurred VARCHAR2(1) := NULL;
374 l_v_ret_status VARCHAR2(1) := NULL;
375 l_b_unit_assessable BOOLEAN := FALSE;
376 l_rowid ROWID := NULL;
377 e_expected_error EXCEPTION;
378
379 l_b_no_data_found BOOLEAN := FALSE;
380
381 l_v_invoice_number igs_fi_inv_int_all.invoice_number%TYPE;
382 l_b_chg_decl_rev BOOLEAN;
383
384 l_v_ret_level igs_fi_f_typ_ca_inst_all.retention_level_code%TYPE;
385 l_v_complete_withdr_ret igs_fi_f_typ_ca_inst_all.complete_ret_flag%TYPE;
386 l_v_sua_status igs_en_su_attempt.unit_attempt_status%TYPE := NULL;
387 l_d_disc_dt igs_en_su_attempt.discontinued_dt%TYPE := NULL;
388
389 -- Cursor to get optional payment Indicator for a given fee type
390 CURSOR cur_optional_payment_ind(cp_fee_type igs_fi_fee_type_all.fee_type%TYPE) IS
391 SELECT optional_payment_ind
392 FROM igs_fi_fee_type
393 WHERE fee_type = cp_fee_type;
394
395 l_v_optional_payment_ind igs_fi_fee_type_all.optional_payment_ind%TYPE;
396 l_v_unit_transferred VARCHAR2(1);
397
398 l_v_disc_reason igs_en_su_attempt.dcnt_reason_cd%TYPE;
399
400 BEGIN
401 x_return_status := 'S';
402
403 -- Initialize the stack if log messages = True
404 IF p_b_log_messages THEN
405 fnd_msg_pub.initialize;
406 END IF;
407
408 -- If any of the mandatory parameters have not been provided, log the message
409 IF (p_n_person_id IS NULL) OR(p_v_course_cd IS NULL) OR (p_n_uoo_id IS NULL) OR
410 (p_v_fee_cal_type IS NULL) OR (p_n_fee_ci_sequence_number IS NULL) OR
411 (p_v_load_cal_type IS NULL) OR (p_n_load_ci_sequence_number IS NULL) OR
412 (p_d_gl_date IS NULL) OR (p_b_log_messages IS NULL) THEN
413 IF p_b_log_messages THEN
414 fnd_message.set_name('IGS','IGS_FI_PARAMETER_NULL');
415 fnd_msg_pub.add;
416 END IF;
417 RAISE e_expected_error;
418 END IF;
419
420 -- Determine the Unit Section description if logging is enabled
421 IF p_b_log_messages THEN
422 l_v_unit_section_desc := igs_fi_gen_apint.get_unit_section_desc(p_n_uoo_id => p_n_uoo_id);
423 END IF;
424
425 -- Determine the Currency Code and description
426 igs_fi_gen_gl.finp_get_cur(p_v_currency_cd => l_v_currency_cd,
427 p_v_curr_desc => l_v_currency_desc,
428 p_v_message_name => l_v_message_name);
429 IF l_v_message_name IS NOT NULL THEN
430 IF p_b_log_messages THEN
431 fnd_message.set_name('IGS',l_v_message_name);
432 fnd_msg_pub.add;
433 END IF;
434 RAISE e_expected_error;
435 END IF;
436
437 -- Loop through existing records of the special fees table
438 FOR l_rec_get_sum_sp_fees IN cur_get_sum_sp_fees(cp_n_person_id => p_n_person_id,
439 cp_v_course_cd => p_v_course_cd,
440 cp_n_uoo_id => p_n_uoo_id,
441 cp_v_fee_cal_type => p_v_fee_cal_type,
442 cp_n_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
443 cp_v_transaction_type_cd => g_v_retention)
444 LOOP
445 -- If sum of special fees is greater than zero, initialize the plsql table
446 IF l_rec_get_sum_sp_fees.fee_amt > 0.0 THEN
447 l_sp_fees_tbl(l_n_counter).person_id := p_n_person_id;
448 l_sp_fees_tbl(l_n_counter).course_cd := p_v_course_cd;
449 l_sp_fees_tbl(l_n_counter).uoo_id := p_n_uoo_id;
450 l_sp_fees_tbl(l_n_counter).fee_type := l_rec_get_sum_sp_fees.fee_type;
451 l_sp_fees_tbl(l_n_counter).fee_cal_type := p_v_fee_cal_type;
452 l_sp_fees_tbl(l_n_counter).fee_ci_sequence_number := p_n_fee_ci_sequence_number;
453 l_sp_fees_tbl(l_n_counter).old_amount := l_rec_get_sum_sp_fees.fee_amt;
454 l_sp_fees_tbl(l_n_counter).new_amount := 0.0;
455 l_sp_fees_tbl(l_n_counter).invoice_id := l_rec_get_sum_sp_fees.invoice_id;
456 l_n_counter := l_n_counter + 1;
457 END IF; -- End of check for fee_amt > 0
458 END LOOP; -- End loop for records cursor cur_get_sum_sp_fees
459
460 -- For the current unit attempt, determine if the unit is fee assessable
461 OPEN cur_unit_load(cp_n_person_id => p_n_person_id,
462 cp_v_course_cd => p_v_course_cd,
463 cp_n_uoo_id => p_n_uoo_id,
464 cp_v_unit_att_status => g_v_sua_status,
465 cp_v_fee_ass_ind => g_v_yes);
466 FETCH cur_unit_load INTO l_rec_unit_load;
467 IF cur_unit_load%FOUND THEN
468 l_b_unit_assessable := TRUE;
469 ELSE
470 l_b_unit_assessable := FALSE;
471 l_b_no_data_found := TRUE;
472 END IF;
473 CLOSE cur_unit_load;
474
475 IF l_b_unit_assessable THEN
476 -- If load is incurred, EN api returns 'Y', else returns 'N'
477 -- The parameter p_include_audit to be uncommented when the EN api is available
478 -- after the modifications.
479 --removed the comment p_include_audit as there should not be any commented code
480 l_v_load_incurred := igs_en_prc_load.enrp_get_load_apply(p_teach_cal_type => l_rec_unit_load.cal_type,
481 p_teach_sequence_number => l_rec_unit_load.ci_sequence_number,
482 p_discontinued_dt => l_rec_unit_load.discontinued_dt,
483 p_administrative_unit_status => l_rec_unit_load.administrative_unit_status,
484 p_unit_attempt_status => l_rec_unit_load.unit_attempt_status,
485 p_no_assessment_ind => l_rec_unit_load.no_assessment_ind,
486 p_load_cal_type => p_v_load_cal_type,
487 p_load_sequence_number => p_n_load_ci_sequence_number,
488 p_include_audit => g_v_yes);
489
490 IF (l_v_load_incurred = g_v_yes) THEN
491 -- If there are any records in the pl/sql table, then initialize new amount to the old amount
492 IF l_sp_fees_tbl.COUNT > 0 THEN
493 FOR l_n_tbl_cnt IN l_sp_fees_tbl.FIRST .. l_sp_fees_tbl.LAST LOOP
494 IF l_sp_fees_tbl.EXISTS(l_n_tbl_cnt) THEN
495 l_sp_fees_tbl(l_n_tbl_cnt).new_amount := l_sp_fees_tbl(l_n_tbl_cnt).old_amount;
496 END IF;
497 END LOOP;
498 ELSE
499 -- If there are no records in the plsql table, fetch all fee types from
500 -- the Special Fees rate setup information and for each record, initialize
501 -- the plsql table
502 l_n_counter := 0;
503 FOR l_rec_usec_sp_fees IN cur_usec_sp_fees(cp_n_uoo_id => p_n_uoo_id) LOOP
504 l_sp_fees_tbl(l_n_counter).person_id := p_n_person_id;
505 l_sp_fees_tbl(l_n_counter).course_cd := p_v_course_cd;
506 l_sp_fees_tbl(l_n_counter).uoo_id := p_n_uoo_id;
507 l_sp_fees_tbl(l_n_counter).fee_type := l_rec_usec_sp_fees.fee_type;
508 l_sp_fees_tbl(l_n_counter).fee_cal_type := p_v_fee_cal_type;
509 l_sp_fees_tbl(l_n_counter).fee_ci_sequence_number := p_n_fee_ci_sequence_number;
510 l_sp_fees_tbl(l_n_counter).old_amount := 0.0;
511 l_sp_fees_tbl(l_n_counter).new_amount := l_rec_usec_sp_fees.sp_fee_amt;
512 l_sp_fees_tbl(l_n_counter).invoice_id := NULL;
513 l_n_counter := l_n_counter + 1;
514 END LOOP;
515
516 IF l_n_counter = 0 THEN
517 l_b_no_data_found := TRUE;
518 END IF;
519 END IF; -- End for table count > 0
520 ELSE
521 l_b_no_data_found := TRUE;
522 END IF; -- End for load_incurred = g_v_yes
523
524 END IF; -- End for unit_assessable = True
525
526 IF l_sp_fees_tbl.COUNT > 0 THEN
527 FOR l_n_tbl_cnt IN l_sp_fees_tbl.FIRST .. l_sp_fees_tbl.LAST LOOP
528 IF l_sp_fees_tbl.EXISTS(l_n_tbl_cnt) THEN
529
530 -- Log context information in the log file
531 IF p_b_log_messages THEN
532 log_details(p_v_person_number => p_v_person_number,
533 p_v_fee_period => p_v_fee_period,
534 p_v_unit_section_desc => l_v_unit_section_desc,
535 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type);
536 END IF;
537
538 -- Check if the fee has already been declined.
539 -- If declined, that charge has to be skipped while processing
540 l_b_chg_decl_rev:=FALSE;
541 IF (l_sp_fees_tbl(l_n_tbl_cnt).invoice_id IS NOT NULL) THEN
542 igs_fi_gen_008.chk_chg_adj(p_n_person_id => NULL,
543 p_v_location_cd => NULL,
544 p_v_course_cd => NULL,
545 p_v_fee_cal_type => NULL,
546 p_v_fee_cat => NULL,
547 p_n_fee_ci_sequence_number => NULL,
548 p_v_fee_type => NULL,
549 p_n_uoo_id => NULL,
550 p_v_transaction_type => NULL,
551 p_n_invoice_id => l_sp_fees_tbl(l_n_tbl_cnt).invoice_id,
552 p_v_invoice_num => l_v_invoice_number,
553 p_b_chg_decl_rev => l_b_chg_decl_rev);
554
555 END IF;
556 IF (l_b_chg_decl_rev) THEN
557 -- Charge is reversed or declined. skip the record
558 IF p_b_log_messages THEN
559 -- Message that the fee has been declined, so no further processing would happen
560 fnd_message.set_name('IGS','IGS_FI_SP_FEE_DECLINED');
561 fnd_message.set_token('INVOICE_NUM',l_v_invoice_number);
562 fnd_msg_pub.add;
563 END IF;
564 ELSE
565 -- Charge not reversed or declined.
566 -- Continue normal processing for charges that are not declined
567 l_n_net_amount := l_sp_fees_tbl(l_n_tbl_cnt).new_amount - l_sp_fees_tbl(l_n_tbl_cnt).old_amount;
568
569 -- If net amount <> 0 then charge has to be either created or reversed
570 IF l_n_net_amount <> 0.0 THEN
571 -- If net amount is negative, then already created special charge has to be reversed
572 -- Check if Retention applies to this charge
573
574 l_n_source_invoice_id := l_sp_fees_tbl(l_n_tbl_cnt).invoice_id;
575
576 --Get the optional payment indicator for the fee type
577 OPEN cur_optional_payment_ind(cp_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type);
578 FETCH cur_optional_payment_ind INTO l_v_optional_payment_ind;
579 CLOSE cur_optional_payment_ind;
580
581 IF (l_n_net_amount < 0.0 AND l_v_optional_payment_ind = 'N') THEN
582 -- Get student unit attempt status and discontinued date
583 OPEN cur_sua_status(l_sp_fees_tbl(l_n_tbl_cnt).person_id,
584 l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
585 l_sp_fees_tbl(l_n_tbl_cnt).course_cd);
586 FETCH cur_sua_status INTO l_v_sua_status, l_d_disc_dt, l_v_disc_reason;
587 CLOSE cur_sua_status;
588
589 IF (l_v_sua_status <> 'INVALID') THEN
590 -- Check if the unit attempt has been dropped due to a Program Transfer, in which case retention
591 -- need not be calculated.
592 l_v_unit_transferred := igs_fi_gen_008.chk_unit_prg_transfer(l_v_disc_reason);
593 -- If the unit was not part of a Program Transfer (function returns N), calculate retention
594 IF (l_v_unit_transferred = 'N') THEN
595 -- Get the retention level and complete withdrawal retention flag
596 igs_fi_gen_008.get_retention_params(p_v_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
597 p_n_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
598 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
599 p_v_ret_level => l_v_ret_level,
600 p_v_complete_withdr_ret => l_v_complete_withdr_ret
601 );
602
603 IF (l_v_ret_level = 'FEE_PERIOD') THEN
604 l_n_retention_amt := igs_fi_gen_008.get_fee_retention_amount(p_v_fee_cat => NULL,
605 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
606 p_v_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
607 p_n_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
608 p_n_diff_amount => ABS(l_n_net_amount));
609 ELSIF (l_v_ret_level = 'TEACH_PERIOD') THEN
610 l_n_retention_amt := igs_fi_gen_008.get_special_retention_amt(p_n_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
611 p_v_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
612 p_n_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
613 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
614 p_d_effective_date => l_d_disc_dt,
615 p_n_diff_amount => l_n_net_amount);
616 END IF;
617
618 IF l_n_retention_amt > 0.0 THEN
619 -- If retention amount is greater than 0, a retention charge has to be created.
620 call_charges_api(p_n_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
621 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
622 p_v_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
623 p_n_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
624 p_v_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
625 p_n_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
626 p_n_amount => l_n_retention_amt,
627 p_v_transaction_type => g_v_retention,
628 p_v_currency_cd => l_v_currency_cd,
629 p_d_gl_date => p_d_gl_date,
630 p_n_source_invoice_id => NULL,
631 p_v_sua_status => l_v_sua_status,
632 p_n_invoice_id => l_n_invoice_id,
633 p_v_ret_status => l_v_ret_status);
634 IF l_v_ret_status <> 'S' THEN
635 -- Message that no transactions have been carried out due to some error
636 fnd_message.set_name('IGS','IGS_FI_SP_NO_CHARGE');
637 fnd_message.set_token('PERSON_NUMBER',p_v_person_number);
638 fnd_msg_pub.add;
639 RAISE e_expected_error;
640 END IF;
641
642 -- After creation of retention charge, insert a record into the Special Fees table
643 -- Modified transaction_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
644 l_n_special_fee_id := NULL;
645 l_rowid := NULL;
646 igs_fi_special_fees_pkg.insert_row ( x_rowid => l_rowid,
647 x_special_fee_id => l_n_special_fee_id,
648 x_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
649 x_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
650 x_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
651 x_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
652 x_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
653 x_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
654 x_fee_amt => igs_fi_gen_gl.get_formatted_amount(l_n_retention_amt),
655 x_transaction_date => TRUNC(SYSDATE),
656 x_s_transaction_type_code => g_v_retention,
657 x_invoice_id => l_n_invoice_id,
658 x_mode => 'R'
659 );
660 END IF; -- End for retention_amt > 0
661 END IF; -- End for l_v_unit_transferred = 'N'
662 END IF; --unit_attempt_status <> 'INVALID'
663 END IF; -- End if for l_n_net_amount < 0
664 IF l_n_net_amount > 0.0 THEN
665 l_n_source_invoice_id := NULL;
666 END IF;
667
668 -- If net_amount > 0, new charge is created, in which case source_invoice_id is NULL
669 -- If net_amount < 0, already existing charge is reversed, in which case invoice_id is
670 -- passed as the source_invoice_id
671 l_v_ret_status := NULL;
672 call_charges_api(p_n_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
673 p_v_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
674 p_v_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
675 p_n_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
676 p_v_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
677 p_n_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
678 p_n_amount => l_n_net_amount,
679 p_v_transaction_type => g_v_special,
680 p_v_currency_cd => l_v_currency_cd,
681 p_d_gl_date => p_d_gl_date,
682 p_n_source_invoice_id => l_n_source_invoice_id,
683 p_v_sua_status => l_v_sua_status,
684 p_n_invoice_id => l_n_invoice_id,
685 p_v_ret_status => l_v_ret_status
686 );
687 IF l_v_ret_status <> 'S' THEN
688 -- Message that no transactions have been carried out due to some error
689 fnd_message.set_name('IGS','IGS_FI_SP_NO_CHARGE');
690 fnd_message.set_token('PERSON_NUMBER',p_v_person_number);
691 fnd_msg_pub.add;
692 RAISE e_expected_error;
693 END IF;
694
695 -- After creation of a special charge, insert a record into the Special Fees table
696 -- Modified transaction_date entry as Trunc(Sysdate) as part of Bug 4240402: Timezone impact
697 l_n_special_fee_id := NULL;
698 l_rowid := NULL;
699 igs_fi_special_fees_pkg.insert_row ( x_rowid => l_rowid,
700 x_special_fee_id => l_n_special_fee_id,
701 x_person_id => l_sp_fees_tbl(l_n_tbl_cnt).person_id,
702 x_course_cd => l_sp_fees_tbl(l_n_tbl_cnt).course_cd,
703 x_uoo_id => l_sp_fees_tbl(l_n_tbl_cnt).uoo_id,
704 x_fee_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_type,
705 x_fee_cal_type => l_sp_fees_tbl(l_n_tbl_cnt).fee_cal_type,
706 x_fee_ci_sequence_number => l_sp_fees_tbl(l_n_tbl_cnt).fee_ci_sequence_number,
707 x_fee_amt => igs_fi_gen_gl.get_formatted_amount(l_n_net_amount),
708 x_transaction_date => TRUNC(SYSDATE),
709 x_s_transaction_type_code => g_v_special,
710 x_invoice_id => NVL(l_n_invoice_id,l_n_source_invoice_id),
711 x_mode => 'R'
712 );
713
714 -- Log messages for any creation/reversal of charges
715 IF p_b_log_messages THEN
716 IF l_n_net_amount > 0.0 THEN
717 -- Message that a new charge has been created
718 fnd_message.set_name('IGS','IGS_FI_SPECIAL_FEE_CREATED');
719 fnd_message.set_token('INVOICE_NUMBER',igs_fi_gen_008.get_invoice_number(l_n_invoice_id));
720 fnd_message.set_token('AMT',l_n_net_amount);
721 fnd_msg_pub.add;
722 ELSIF l_n_net_amount < 0.0 THEN
723 -- Message that the charge has been reversed
724 fnd_message.set_name('IGS','IGS_FI_SPECIAL_FEE_REVERSED');
725 fnd_message.set_token('INVOICE_NUMBER',igs_fi_gen_008.get_invoice_number(l_sp_fees_tbl(l_n_tbl_cnt).invoice_id));
726 fnd_message.set_token('AMT',l_sp_fees_tbl(l_n_tbl_cnt).old_amount);
727 fnd_msg_pub.add;
728 IF l_n_retention_amt > 0.0 THEN
729 -- Message that a retention charge has been created
730 fnd_message.set_name('IGS','IGS_FI_RET_TRANSACTION_AMT');
731 fnd_message.set_token('AMOUNT',l_n_retention_amt);
732 fnd_msg_pub.add;
733 END IF;
734 END IF; -- End of check for l_n_net_amount
735 END IF; -- End of log_messages = 'TRUE'
736
737 ELSE
738 IF p_b_log_messages THEN
739 -- Message that there is no change in the charge amount
740 fnd_message.set_name('IGS','IGS_FI_SP_FEE_NO_CHANGE');
741 fnd_msg_pub.add;
742 END IF;
743 END IF; -- End for net_amount <> 0
744
745 END IF; -- End for l_b_chg_decl_rev
746
747 END IF; -- End if for plsql record EXISTS
748
749 END LOOP; -- End loop for all records in plsql table
750
751 ELSE
752 IF p_b_log_messages AND l_b_no_data_found THEN
753 log_details(p_v_person_number => p_v_person_number,
754 p_v_fee_period => p_v_fee_period,
755 p_v_unit_section_desc => l_v_unit_section_desc,
756 p_v_fee_type => NULL);
757 fnd_message.set_name('IGF', 'IGF_AP_NO_DATA_FOUND');
758 fnd_msg_pub.add;
759 END IF;
760 END IF; -- End for tbl count > 0
761
762 fnd_msg_pub.count_and_get(p_count => x_msg_count,
763 p_data => x_msg_data);
764
765 EXCEPTION
766 WHEN e_expected_error THEN
767 ROLLBACK;
768 x_return_status := 'E';
769 -- If FND logging is enabled, then send message to the FND log table
770 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level) THEN
771
772 -- Get the message from fnd_msg_pub and put it onto fnd_message stack so that it is logged in fnd_log_messages.
773 fnd_msg_pub.count_and_get(p_count => x_msg_count,
774 p_data => x_msg_data);
775 IF (x_msg_count = 1) THEN
776 fnd_message.set_encoded(x_msg_data);
777 ELSIF (x_msg_count > 1) THEN
778 x_msg_data := fnd_msg_pub.get(p_msg_index=>fnd_msg_pub.G_LAST);
779 fnd_message.set_encoded(x_msg_data);
780 END IF;
781
782 -- Log message in FND tables, but do not pop the message from the Stack, hence pass False
783 fnd_log.message(fnd_log.level_error, 'igs.patch.115.sql.igs_fi_prc_sp_fees.assess_fees_pvt',FALSE);
784 ELSE
785 fnd_msg_pub.count_and_get(p_count => x_msg_count,
786 p_data => x_msg_data);
787 END IF;
788 WHEN OTHERS THEN
789 ROLLBACK;
790 x_return_status := 'U';
791 -- If FND logging is enabled, log message as Unexpected error
792 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
793 -- Get the message from fnd_msg_pub and put it onto fnd_message stack so that it is logged in fnd_log_messages.
794 fnd_msg_pub.count_and_get(p_count => x_msg_count,
795 p_data => x_msg_data);
796 IF (x_msg_count = 1) THEN
797 fnd_message.set_encoded(x_msg_data);
798 ELSIF (x_msg_count > 1) THEN
799 x_msg_data := fnd_msg_pub.get(p_msg_index=>fnd_msg_pub.G_LAST);
800 fnd_message.set_encoded(x_msg_data);
801 END IF;
802 fnd_log.message(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.assess_fees_pvt',FALSE);
803 fnd_log.string(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.assess_fees_pvt',SQLERRM);
804 ELSE
805 fnd_msg_pub.count_and_get(p_count => x_msg_count,
806 p_data => x_msg_data);
807 END IF;
808 END assess_fees_pvt;
809
810 PROCEDURE validate_params( p_n_person_id IN igs_pe_person_base_v.person_id%TYPE,
811 p_n_person_grp_id IN igs_pe_persid_group_v.group_id%TYPE,
812 p_v_fee_period IN VARCHAR2,
813 p_v_test_run IN VARCHAR2,
814 p_d_gl_date IN VARCHAR2,
815 p_v_fee_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
816 p_n_fee_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
817 p_v_ld_cal_type OUT NOCOPY igs_ca_inst.cal_type%TYPE,
818 p_n_ld_ci_sequence_number OUT NOCOPY igs_ca_inst.sequence_number%TYPE,
819 p_val_status OUT NOCOPY BOOLEAN) IS
820 ------------------------------------------------------------------
821 --Created by :Umesh Udayaprakash, Oracle India (in)
822 --Date created: 17-OCT-2003
823 --
824 --Purpose: To Validate the input parameters and log message to the
825 -- Log File.
826 --
827 --
828 --Known limitations/enhancements and/or remarks:
829 --
830 --Change History:
831 --Who When What
832 --akandreg 27-Jun-2006 Bug 5104339: Modified token value passed to 'IGS_FI_INVALID_GL_DATE' using
833 -- igs_ge_date.igsdate().
834 --skharida 16-JUN-06 Bug 5094077 changed the log msg when both person number and person grp
835 -- are given as parameter
836 -------------------------------------------------------------------
837
838 --Cursor for Checking the peson_id
839 CURSOR c_person_id (cp_person_id igs_pe_person_base_v.person_id%TYPE) IS
840 SELECT person_number
841 FROM IGS_PE_PERSON_BASE_V
842 WHERE person_id = cp_person_id;
843
844 --Cursor for checking Person Group
845
846 CURSOR c_person_grp_id(cp_person_grp_id igs_pe_persid_group_v.group_id%TYPE) IS
847 SELECT 'X'
848 FROM igs_pe_persid_group_v
849 WHERE group_id = cp_person_grp_id
850 AND closed_ind = 'N';
851
852 l_b_parameter_val_status BOOLEAN;
853 l_person_id c_person_id%ROWTYPE;
854 l_person_grp_id c_person_grp_id%ROWTYPE;
855
856 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
857 l_n_ld_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
858 l_v_message_name fnd_new_messages.message_name%TYPE;
859 l_b_return_stat BOOLEAN;
860 l_v_closing_status igs_fi_gl_periods_v.closing_status%TYPE;
861
862 l_v_fee_cal_type igs_ca_inst.cal_type%TYPE;
863 l_n_fee_ci_sequence_number igs_ca_inst.sequence_number%TYPE;
864
865 BEGIN
866
867 l_b_parameter_val_status := TRUE;
868 l_v_fee_cal_type := NULL;
869 l_n_fee_ci_sequence_number := NULL;
870 l_v_ld_cal_type := NULL;
871 l_n_ld_ci_sequence_number := NULL;
872 fnd_file.new_line(fnd_file.log,1);
873 -- To Check Whether The Person Id Is Valid If It Is Provided
874 IF (p_n_person_id IS NOT NULL) THEN
875 OPEN c_person_id(p_n_person_id);
876 FETCH c_person_id INTO l_person_id;
877 IF c_person_id%NOTFOUND THEN
878 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
879 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON'));
880 fnd_file.put_line(fnd_file.log,fnd_message.get);
881 l_b_parameter_val_status := FALSE;
882 END IF;
883 CLOSE c_person_id;
884 END IF;
885
886 -- To Check Whether The Person Group Id Is Valid If It Is Provided
887
888 IF (p_n_person_grp_id IS NOT NULL) THEN
889 OPEN c_person_grp_id(p_n_person_grp_id);
890 FETCH c_person_grp_id INTO l_person_grp_id;
891 IF c_person_grp_id%NOTFOUND THEN
892 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
893 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP'));
894 fnd_file.put_line(fnd_file.log,fnd_message.get);
895 l_b_parameter_val_status := FALSE;
896 END IF;
897 CLOSE c_person_grp_id;
898 END IF;
899
900 IF p_v_fee_period IS NOT NULL THEN
901 l_v_fee_cal_type := RTRIM(SUBSTR(p_v_fee_period,1,10));
902 l_n_fee_ci_sequence_number := TO_NUMBER(RTRIM(SUBSTR(p_v_fee_period,12)));
903
904 IF igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_v_fee_cal_type,
905 p_n_ci_sequence_number => l_n_fee_ci_sequence_number,
906 p_v_s_cal_cat => 'FEE') THEN
907 -- Call To The Procedure To Check Whether The Fee Calendar Instance Has
908 -- One To One Relation With Load Calendar Instance
909 igs_fi_crdapi_util.validate_fci_lci_reln(p_v_fee_cal_type => l_v_fee_cal_type,
910 p_n_fee_ci_sequence_number => l_n_fee_ci_sequence_number,
911 p_v_ld_cal_type => l_v_ld_cal_type ,
912 p_n_ld_ci_sequence_number => l_n_ld_ci_sequence_number ,
913 p_v_message_name => l_v_message_name ,
914 p_b_return_stat =>l_b_return_stat);
915 IF NOT l_b_return_stat THEN
916 fnd_message.set_name('IGS',l_v_message_name);
917 fnd_file.put_line(fnd_file.log,fnd_message.get);
918 l_b_parameter_val_status := FALSE;
919 END IF;
920 ELSE
921 -- The Message 'Invalid Fee Period Parameters Passed To The Process.' Is Logged If
922 -- The Function Returns False.
923 fnd_message.set_name('IGS','IGS_FI_FCI_NOTFOUND');
924 fnd_file.put_line(fnd_file.log,fnd_message.get);
925 l_b_parameter_val_status := FALSE;
926 END IF;
927 END IF; -- end of p_v_fee_period validation
928
929 -- To Validate The Parameter Test Run
930 IF (p_v_test_run IS NULL) OR (p_d_gl_date IS NULL ) THEN
931 fnd_message.set_name('IGS','IGS_UC_NO_MANDATORY_PARAMS');
932 fnd_file.put_line(fnd_file.log,fnd_message.get);
933 l_b_parameter_val_status := FALSE;
934 END IF;
935
936 IF p_v_test_run NOT IN ('Y','N') THEN
937 fnd_message.set_name('IGS','IGS_FI_INVALID_PARAMETER');
938 fnd_message.set_token('PARAMETER',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TEST_RUN'));
939 fnd_file.put_line(fnd_file.log,fnd_message.get);
940 l_b_parameter_val_status := FALSE;
941 END IF;
942 -- To Validate The Parameter Gl Date
943
944 igs_fi_gen_gl.get_period_status_for_date(p_d_date => igs_ge_date.igsdate(p_d_gl_date),
945 p_v_closing_status => l_v_closing_status,
946 p_v_message_name => l_v_message_name);
947 IF l_v_closing_status NOT IN ('O','F') THEN
948 fnd_message.set_name('IGS','IGS_FI_INVALID_GL_DATE');
949 fnd_message.set_token('GL_DATE',igs_ge_date.igsdate(p_d_gl_date));
950 fnd_file.put_line(fnd_file.log,fnd_message.get);
951 l_b_parameter_val_status := FALSE;
952 END IF;
953
954 -- To Check Whether Both Person_id And Person Group Values Are Provided.
955 IF (p_n_person_id IS NOT NULL) AND (p_n_person_grp_id IS NOT NULL)THEN
956 fnd_message.set_name('IGS','IGS_FI_NO_PERS_PGRP');
957 fnd_file.put_line(fnd_file.log,fnd_message.get);
958 l_b_parameter_val_status := FALSE;
959 END IF;
960 --Assign The Values To The Out Parameters.
961 p_v_fee_cal_type :=l_v_fee_cal_type;
962 p_n_fee_ci_sequence_number :=l_n_fee_ci_sequence_number;
963 p_v_ld_cal_type := l_v_ld_cal_type;
964 p_n_ld_ci_sequence_number := l_n_ld_ci_sequence_number;
965 p_val_status := l_b_parameter_val_status;
966
967 END validate_params;
968
969 PROCEDURE assess_fees( errbuf OUT NOCOPY VARCHAR2,
970 retcode OUT NOCOPY NUMBER,
971 p_n_person_id IN NUMBER,
972 p_n_person_grp_id IN NUMBER,
973 p_v_fee_period IN VARCHAR2,
974 p_v_test_run IN VARCHAR2,
975 p_d_gl_date IN VARCHAR2 ) IS
976
977 ------------------------------------------------------------------
978 --Created by :Umesh Udayaprakash, Oracle India (in)
979 --Date created: 17-OCT-2003
980 --
981 --Purpose: To Validate the input parameters and log message to the
982 -- Log File and call the Process_special_fees procedure.
983 --
984 --
985 --Known limitations/enhancements and/or remarks:
986 --
987 --Change History:
988 --Who When What
989 --akandreg 27-Jun-2006 Bug 5104339 -Made code modification to change display format of GL date in log file by
990 -- using igs_ge_date.igsdate().
991 --akandreg 09-Jun-2006 Bug 5107755 - Replaced the cursor c_get_alt_code by cursor c_get_alt_code_desc,
992 -- which queries both alternate code and description so that both of them
993 -- can be logged into the log file.
994 --abshriva 5-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
995 -------------------------------------------------------------------
996 TYPE person_grp_ref_cur_type IS REF CURSOR;
997 c_ref_person_grp person_grp_ref_cur_type;
998
999 l_n_person_id igs_pe_std_todo.person_id%TYPE;
1000 l_dynamic_sql VARCHAR2(32767);
1001 l_v_status VARCHAR2(1);
1002 l_v_manage_acc igs_fi_control_all.manage_accounts%TYPE;
1003 l_v_message_name fnd_new_messages.message_name%TYPE;
1004 -- Out Parameters from the Process_special_fees procedure.
1005 l_b_recs_found BOOLEAN;
1006 l_b_person_grp_data_found BOOLEAN;
1007 l_v_return_status VARCHAR2(1);
1008 l_b_validate_parm_status BOOLEAN;
1009 l_v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1010 l_n_fee_ci_seq_number igs_ca_inst.sequence_number%TYPE;
1011 l_v_load_cal_type igs_ca_inst.cal_type%TYPE;
1012 l_n_load_ci_seq_number igs_ca_inst.sequence_number%TYPE;
1013 l_org_id VARCHAR2(15);
1014 --Cursor For Getting The Alternate Code and Description For The Fee Period.
1015 CURSOR c_get_alt_code_desc(cp_v_cal_type igs_ca_inst.cal_type%TYPE,
1016 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1017 SELECT alternate_code, description
1018 FROM igs_ca_inst
1019 WHERE cal_type = cp_v_cal_type
1020 AND sequence_number = cp_n_sequence_number;
1021 l_c_alt_code_desc c_get_alt_code_desc%ROWTYPE;
1022 --Cursor For Getting The Group Code For The Group Id
1023 CURSOR c_get_person_grp (c_group_id igs_pe_persid_group_v.group_id%TYPE) IS
1024 SELECT group_cd
1025 FROM igs_pe_persid_group_v
1026 WHERE group_id = c_group_id;
1027 l_c_get_person_grp c_get_person_grp%ROWTYPE;
1028 l_v_alt_code_msg VARCHAR2(500) DEFAULT NULL;
1029
1030 BEGIN
1031 BEGIN
1032 l_org_id := NULL;
1033 igs_ge_gen_003.set_org_id(l_org_id);
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 fnd_file.put_line (fnd_file.log, fnd_message.get);
1037 retcode:=2;
1038 RETURN;
1039 END;
1040 retcode := 0;
1041 l_b_person_grp_data_found :=FALSE;
1042 --Logging of all the Parameter to the Log File.
1043 --Logging Person Number.
1044 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1045 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||igs_fi_gen_008.get_party_number(p_n_person_id));
1046 fnd_file.put_line(fnd_file.log,fnd_message.get);
1047
1048 --Logging Person Group.
1049 OPEN c_get_person_grp(p_n_person_grp_id);
1050 FETCH c_get_person_grp INTO l_c_get_person_grp;
1051 CLOSE c_get_person_grp;
1052 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1053 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON_GROUP')||': '||l_c_get_person_grp.group_cd);
1054 fnd_file.put_line(fnd_file.log,fnd_message.get);
1055
1056 --Logging Fee Assesment Period.
1057 OPEN c_get_alt_code_desc(cp_v_cal_type => RTRIM(SUBSTR(p_v_fee_period,1,10)),
1058 cp_n_sequence_number => TO_NUMBER(RTRIM(SUBSTR(p_v_fee_period,12))));
1059 FETCH c_get_alt_code_desc INTO l_c_alt_code_desc ;
1060 CLOSE c_get_alt_code_desc;
1061 --storing the Alternate Code into the Global Variable for passing it to the assess_fees_pvt procedure
1062 g_v_alternatecode := l_c_alt_code_desc.alternate_code;
1063 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1064
1065 IF l_c_alt_code_desc.alternate_code IS NOT NULL THEN
1066 l_v_alt_code_msg := ' ( ' || l_c_alt_code_desc.alternate_code || ' ) ';
1067 END IF;
1068 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_ASS_PERIOD')||': '||l_c_alt_code_desc.description || l_v_alt_code_msg );
1069 fnd_file.put_line(fnd_file.log,fnd_message.get);
1070 --Logging Test run.
1071 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1072 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','TEST_RUN')||': '||igs_fi_gen_gl.get_lkp_meaning('YES_NO',p_v_test_run));
1073 fnd_file.put_line(fnd_file.log,fnd_message.get);
1074
1075 --Logging GL Date.
1076 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1077 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','GL_DATE')||': '||igs_ge_date.igsdate(p_d_gl_date));
1078 fnd_file.put_line(fnd_file.log,fnd_message.get);
1079
1080 igs_fi_com_rec_interface.chk_manage_account(p_v_manage_acc => l_v_manage_acc,
1081 p_v_message_name => l_v_message_name);
1082
1083 IF l_v_manage_acc IS NULL THEN
1084 fnd_message.set_name('IGS',l_v_message_name);
1085 fnd_file.put_line(fnd_file.log,fnd_message.get);
1086 retcode := 2;
1087 RETURN;
1088 END IF;
1089 -- To Validate The Parameters And Log The Message.
1090 validate_params( p_n_person_id => p_n_person_id,
1091 p_n_person_grp_id => p_n_person_grp_id,
1092 p_v_fee_period => p_v_fee_period,
1093 p_v_test_run => p_v_test_run,
1094 p_d_gl_date => p_d_gl_date,
1095 p_v_fee_cal_type => l_v_fee_cal_type,
1096 p_n_fee_ci_sequence_number => l_n_fee_ci_seq_number,
1097 p_v_ld_cal_type => l_v_load_cal_type,
1098 p_n_ld_ci_sequence_number => l_n_load_ci_seq_number,
1099 p_val_status => l_b_validate_parm_status) ;
1100
1101 IF l_b_validate_parm_status THEN
1102 -- If Person Id Has Been Given or Both person_id and person_group is not provided.
1103 IF (p_n_person_id IS NOT NULL) OR (p_n_person_id IS NULL AND p_n_person_grp_id IS NULL) THEN
1104 process_special_fees(p_n_person_id => p_n_person_id,
1105 p_v_fee_cal_type => l_v_fee_cal_type,
1106 p_n_fee_ci_seq_number => l_n_fee_ci_seq_number,
1107 p_v_load_cal_type => l_v_load_cal_type,
1108 p_n_load_ci_seq_number => l_n_load_ci_seq_number,
1109 p_d_gl_date => igs_ge_date.igsdate(p_d_gl_date),
1110 p_v_test_run => p_v_test_run,
1111 p_b_log_messages => TRUE,
1112 p_b_recs_found => l_b_recs_found,
1113 p_v_return_status => l_v_return_status);
1114 IF NOT (l_b_recs_found ) THEN
1115 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1116 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1117 fnd_file.put_line(fnd_file.log,fnd_message.get);
1118 fnd_message.set_name('IGF','IGF_AP_NO_DATA_FOUND');
1119 fnd_file.put_line(fnd_file.log,fnd_message.get);
1120 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1121 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1122 fnd_file.put_line(fnd_file.log,fnd_message.get);
1123 END IF;
1124
1125 IF l_v_return_status = 'W' THEN
1126 retcode := 1;
1127 END IF;
1128 END IF ;
1129
1130 -- If The Person Group Id Has Been Given As Parameter
1131 IF p_n_person_grp_id IS NOT NULL THEN
1132 l_dynamic_sql := igs_pe_dynamic_persid_group.igs_get_dynamic_sql(p_n_person_grp_id,l_v_status );
1133
1134 IF l_v_status <> 'S' THEN
1135 --Log the error message and stop the processing.
1136 fnd_message.set_name('IGF','IGF_AP_INVALID_QUERY');
1137 fnd_file.put_line(fnd_file.log,fnd_message.get);
1138 retcode := 2;
1139 RETURN;
1140 END IF;
1141 OPEN c_ref_person_grp FOR l_dynamic_sql;
1142 -- Looping Across All The Valid Person Ids In The Group.
1143 LOOP
1144 FETCH c_ref_person_grp INTO l_n_person_id;
1145 EXIT WHEN c_ref_person_grp%NOTFOUND;
1146 process_special_fees(p_n_person_id =>l_n_person_id,
1147 p_v_fee_cal_type =>l_v_fee_cal_type,
1148 p_n_fee_ci_seq_number =>l_n_fee_ci_seq_number,
1149 p_v_load_cal_type =>l_v_load_cal_type,
1150 p_n_load_ci_seq_number =>l_n_load_ci_seq_number,
1151 p_d_gl_date =>igs_ge_date.igsdate(p_d_gl_date),
1152 p_v_test_run =>p_v_test_run,
1153 p_b_log_messages =>TRUE,
1154 p_b_recs_found =>l_b_recs_found,
1155 p_v_return_status =>l_v_return_status);
1156 IF NOT (l_b_person_grp_data_found) AND l_b_recs_found THEN
1157 l_b_person_grp_data_found := TRUE;
1158 END IF;
1159 IF l_v_return_status = 'W' THEN
1160 retcode := 1;
1161 END IF;
1162 END LOOP;
1163 CLOSE c_ref_person_grp;
1164 IF NOT (l_b_person_grp_data_found) THEN
1165 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1166 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1167 fnd_file.put_line(fnd_file.log,fnd_message.get);
1168 fnd_message.set_name('IGF','IGF_AP_NO_DATA_FOUND');
1169 fnd_file.put_line(fnd_file.log,fnd_message.get);
1170 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1171 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1172 fnd_file.put_line(fnd_file.log,fnd_message.get);
1173 END IF;
1174 END IF; -- End Of Person Group id based derivation
1175 --To Log The Message If The Process Is A Test Run .
1176 IF p_v_test_run = 'Y' THEN
1177 fnd_message.set_name('IGS','IGS_FI_PRC_TEST_RUN');
1178 fnd_file.put_line(fnd_file.log,fnd_message.get);
1179 END IF;
1180 ELSE
1181 retcode :=2;
1182 END IF;
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 retcode := 2;
1186 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1187 fnd_log.string(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.assess_fees',SQLERRM);
1188 END IF;
1189 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION') || ' : ' || SQLERRM;
1190 igs_ge_msg_stack.conc_exception_hndl;
1191 END assess_fees;
1192
1193 PROCEDURE log_error_message(p_v_person_number hz_parties.party_number%TYPE,
1194 p_v_fee_period VARCHAR2,
1195 p_uooid igs_pe_std_todo_ref.uoo_id%TYPE,
1196 p_v_message_name VARCHAR2 ) IS
1197 ------------------------------------------------------------------
1198 --Created by :Umesh Udayaprakash, Oracle India (in)
1199 --Date created: 21-OCT-2003
1200 --
1201 --Purpose: To Log The Error Message
1202 --
1203 --
1204 --Known limitations/enhancements and/or remarks:
1205 --
1206 --Change History:
1207 --Who When What
1208 --akandreg 09-Jun-2006 Bug 5107755 - Modified signature of the method by passing parameter
1209 -- p_v_fee_period instead of p_v_fee_period_alt_code.
1210
1211 -------------------------------------------------------------------
1212 l_unit_section VARCHAR2(4000);
1213 BEGIN
1214 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1215 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||p_v_person_number);
1216 fnd_file.put_line(fnd_file.log,fnd_message.get);
1217 --To Log The Fee Assesment Period.
1218 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1219 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','FEE_ASS_PERIOD')||': '||p_v_fee_period);
1220 fnd_file.put_line(fnd_file.log,fnd_message.get);
1221 -- To Log The Unit Section .
1222 l_unit_section := igs_fi_gen_apint.get_unit_section_desc( p_n_uoo_id =>p_uooid,
1223 p_v_unit_cd =>NULL,
1224 p_n_version_number =>NULL,
1225 p_v_cal_type =>NULL,
1226 p_n_ci_sequence_number =>NULL,
1227 p_v_location_cd =>NULL,
1228 p_v_unit_class =>NULL );
1229 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1230 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','USEC')||': '||l_unit_section);
1231 fnd_file.put_line(fnd_file.log,fnd_message.get);
1232 -- To Log The Message
1233 fnd_message.set_name('IGS',p_v_message_name);
1234 fnd_file.put_line(fnd_file.log,fnd_message.get);
1235 -- Logging of Separator
1236 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1237 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1238 fnd_file.put_line(fnd_file.log,fnd_message.get);
1239 END log_error_message;
1240
1241 PROCEDURE process_special_fees(p_n_person_id IN PLS_INTEGER,
1242 p_v_fee_cal_type IN VARCHAR2,
1243 p_n_fee_ci_seq_number IN PLS_INTEGER,
1244 p_v_load_cal_type IN VARCHAR2,
1245 p_n_load_ci_seq_number IN PLS_INTEGER,
1246 p_d_gl_date IN DATE,
1247 p_v_test_run IN VARCHAR2,
1248 p_b_log_messages IN BOOLEAN,
1249 p_b_recs_found OUT NOCOPY BOOLEAN,
1250 p_v_return_status OUT NOCOPY VARCHAR2) IS
1251 ------------------------------------------------------------------
1252 --Created by :Umesh Udayaprakash, Oracle India (in)
1253 --Date created: 21-OCT-2003
1254 --
1255 --Purpose:To identify the records to be processed by the assess_fees_pvt procedure
1256 --
1257 --
1258 --Known limitations/enhancements and/or remarks:
1259 --
1260 --Change History:
1261 --Who When What
1262 --akandreg 09-Jun-2006 Bug 5107755 - Replaced the cursor c_get_alt_code by cursor c_get_alt_code_desc,
1263 -- which queries both alternate code and description so that both of them
1264 -- can be logged into the log file. Also passed l_v_fee_period instead
1265 -- of l_v_alt_code_desc for log_details and assess_fees_pvt procedures.
1266 --akandreg 25-May-2006 Bug 5134636 - Modified exception section for Lock exception
1267 -- to include logging of Person Number
1268 -- Added call to new function fisp_lock_records
1269 -- Added logic to lock records before processing
1270 -------------------------------------------------------------------
1271 -- Exception raised when a lock could not be obtained in the Temp table.
1272 e_lock_exception EXCEPTION;
1273 PRAGMA EXCEPTION_INIT(e_lock_exception, -54);
1274
1275 CURSOR c_get_todo_recs(cp_n_person_id igs_pe_std_todo.person_id%TYPE, cp_v_todo_type igs_pe_std_todo.s_student_todo_type%TYPE ) IS
1276 SELECT igs_pe_std_todo.rowid , igs_pe_std_todo.*
1277 FROM igs_pe_std_todo
1278 WHERE (person_id = cp_n_person_id OR cp_n_person_id IS NULL)
1279 AND s_student_todo_type = cp_v_todo_type
1280 AND logical_delete_dt is NULL;
1281
1282
1283 CURSOR c_get_todo_ref_recs(cp_n_person_id igs_pe_std_todo.person_id%TYPE,
1284 cp_n_sequence_number igs_pe_std_todo_ref.sequence_number%TYPE,
1285 cp_v_todo_type igs_pe_std_todo.s_student_todo_type%TYPE,
1286 cp_v_ld_cal_type igs_pe_std_todo_ref.cal_type%TYPE,
1287 cp_n_ld_seq_number igs_pe_std_todo_ref.ci_sequence_number%TYPE) IS
1288 SELECT tref.rowid , tref.*
1289 FROM igs_pe_std_todo_ref tref
1290 WHERE tref.person_id = cp_n_person_id
1291 AND tref.sequence_number = cp_n_sequence_number
1292 AND tref.s_student_todo_type = cp_v_todo_type
1293 AND tref.logical_delete_dt IS NULL
1294 AND (
1295 (tref.cal_type = cp_v_ld_cal_type)
1296 OR
1297 (cp_v_ld_cal_type IS NULL)
1298 )
1299 AND
1300 ( tref.ci_sequence_number = cp_n_ld_seq_number
1301 OR
1302 (cp_n_ld_seq_number IS NULL)
1303 );
1304
1305 --Cursor To Get The Alternate Code For The Cal_type And sequence number
1306 CURSOR c_get_alt_code_desc(cp_v_cal_type igs_ca_inst.cal_type%TYPE,
1307 cp_n_sequence_number igs_ca_inst.sequence_number%TYPE) IS
1308 SELECT alternate_code,description
1309 FROM igs_ca_inst
1310 WHERE cal_type = cp_v_cal_type
1311 AND sequence_number = cp_n_sequence_number;
1312 CURSOR c_check_child_exists(cp_n_person_id igs_pe_std_todo.person_id%TYPE,
1313 cp_n_sequence_number igs_pe_std_todo_ref.sequence_number%TYPE,
1314 cp_v_todo_type igs_pe_std_todo.s_student_todo_type%TYPE ) IS
1315 SELECT 'X'
1316 FROM igs_pe_std_todo_ref tref
1317 WHERE tref.person_id = cp_n_person_id
1318 AND tref.s_student_todo_type = cp_v_todo_type
1319 AND tref.sequence_number = cp_n_sequence_number
1320 AND logical_delete_dt is NULL;
1321 l_check_child_exists c_check_child_exists%ROWTYPE;
1322 l_c_get_todo_recs c_get_todo_recs%ROWTYPE;
1323 l_c_get_todo_ref_recs c_get_todo_ref_recs%ROWTYPE;
1324 l_c_alt_code_desc c_get_alt_code_desc%ROWTYPE;
1325 l_v_alt_code_desc igs_ca_inst.alternate_code%TYPE;
1326 l_v_ci_desc igs_ca_inst.description%TYPE;
1327
1328 l_v_person_number hz_parties.party_number%TYPE;
1329 l_unit_section VARCHAR2(4000);
1330
1331 l_message_name fnd_new_messages.message_name%TYPE;
1332 -- To identify whether any records are found for given the input criteria.
1333 l_v_return_status VARCHAR2(1);
1334 l_count NUMBER(5);
1335 l_msg VARCHAR2(2000);
1336 l_v_fee_cal_type igs_ca_inst.cal_type%TYPE;
1337 l_n_fee_ci_sequence_number PLS_INTEGER;
1338 l_n_msg_count NUMBER;
1339 l_v_msg_data VARCHAR2(32767);
1340 l_b_rel_exists BOOLEAN;
1341 l_b_error BOOLEAN;
1342 l_v_fee_period VARCHAR2(4000);
1343 BEGIN
1344 -- Used to check whether any records are found for the input criteria.
1345 p_b_recs_found := FALSE;
1346 l_v_fee_cal_type := p_v_fee_cal_type;
1347 l_n_fee_ci_sequence_number := p_n_fee_ci_seq_number;
1348 -- Set The Error And Success Flags To Null
1349 l_b_error := FALSE;
1350 l_v_alt_code_desc:= g_v_alternatecode; --Storing The Alternate Code Description Into The Local Variable
1351 FOR l_c_get_todo_recs IN c_get_todo_recs( cp_n_person_id => p_n_person_id,
1352 cp_v_todo_type => 'SPECIAL_FEE' )
1353 LOOP
1354 IF p_b_log_messages = TRUE THEN
1355 l_v_person_number := IGS_FI_GEN_008.GET_PARTY_NUMBER(l_c_get_todo_recs.person_id);
1356 END IF; -- End Of P_b_log_messages
1357 FOR l_c_get_todo_ref_recs IN c_get_todo_ref_recs(cp_n_person_id => l_c_get_todo_recs.person_id ,
1358 cp_n_sequence_number => l_c_get_todo_recs.sequence_number,
1359 cp_v_todo_type => l_c_get_todo_recs.s_student_todo_type ,
1360 cp_v_ld_cal_type => p_v_load_cal_type,
1361 cp_n_ld_seq_number => p_n_load_ci_seq_number )
1362 LOOP
1363 --If fee period is provided
1364 -- Before processing, obtain a lock in table IGS_FI_SPA_FEE_PRDS for the given Person-Course-Fee Period.
1365 IF (l_v_fee_cal_type IS NOT NULL) AND (l_n_fee_ci_sequence_number IS NOT NULL) THEN
1366 IF NOT fisp_lock_records (p_n_person_id => l_c_get_todo_ref_recs.person_id,
1367 p_v_course_cd => l_c_get_todo_ref_recs.course_cd,
1368 p_v_fee_cal_type => l_v_fee_cal_type,
1369 p_n_fee_ci_sequence_number => l_n_fee_ci_sequence_number) THEN
1370 -- If lock could not be obtained, error out.
1371 RAISE e_lock_exception;
1372 END IF;
1373 END IF;
1374 p_b_recs_found := TRUE;
1375 IF (l_c_get_todo_ref_recs.cal_type IS NOT NULL AND l_c_get_todo_ref_recs.ci_sequence_number IS NOT NULL) THEN
1376 l_b_rel_exists := TRUE;
1377 IF (p_v_fee_cal_type IS NULL) OR (p_n_fee_ci_seq_number IS NULL ) THEN
1378 -- To Check Whether One To One Relation
1379 -- The Function Will Return False When No Relation Is Found
1380 IF NOT igs_fi_gen_001.finp_get_lfci_reln ( p_cal_type => l_c_get_todo_ref_recs.cal_type,
1381 p_ci_sequence_number => l_c_get_todo_ref_recs.ci_sequence_number,
1382 p_cal_category => 'LOAD',
1383 p_ret_cal_type => l_v_fee_cal_type,
1384 p_ret_ci_sequence_number => l_n_fee_ci_sequence_number,
1385 p_message_name =>l_message_name) THEN
1386 -- If P_b_log_messages Is True Then Log To The Log File
1387 l_b_rel_exists := FALSE;
1388 l_b_error := TRUE;
1389 IF p_b_log_messages THEN
1390 -- to log the person number
1391 log_error_message( p_v_person_number =>l_v_person_number,
1392 p_v_fee_period =>NULL,
1393 p_uooid =>l_c_get_todo_ref_recs.uoo_id,
1394 p_v_message_name =>l_message_name);
1395 END IF;
1396 END IF ; --igs_fi_gen_001
1397
1398 -- Before processing, obtain a lock in table IGS_FI_SPA_FEE_PRDS for the given Person-Course-Fee Period.
1399 -- if fee period is not provided
1400 IF NOT fisp_lock_records (p_n_person_id => l_c_get_todo_ref_recs.person_id,
1401 p_v_course_cd => l_c_get_todo_ref_recs.course_cd,
1402 p_v_fee_cal_type => l_v_fee_cal_type,
1403 p_n_fee_ci_sequence_number => l_n_fee_ci_sequence_number) THEN
1404 -- If lock could not be obtained, error out.
1405 RAISE e_lock_exception;
1406 END IF;
1407
1408 -- To Get The Alt Code and description
1409 IF p_b_log_messages AND l_b_rel_exists = TRUE THEN
1410 OPEN c_get_alt_code_desc(cp_v_cal_type => l_v_fee_cal_type,
1411 cp_n_sequence_number => l_n_fee_ci_sequence_number);
1412 FETCH c_get_alt_code_desc INTO l_c_alt_code_desc ;
1413 l_v_alt_code_desc :=l_c_alt_code_desc.alternate_code;
1414 l_v_ci_desc := l_c_alt_code_desc.description;
1415 -- for logging fee period
1416 l_v_fee_period := l_v_ci_desc ;
1417 IF l_v_alt_code_desc IS NOT NULL THEN
1418 l_v_fee_period := l_v_fee_period || ' ( ' || l_v_alt_code_desc || ' ) ';
1419 END IF;
1420 CLOSE c_get_alt_code_desc;
1421 END IF;
1422 -- If condition to check whether the Load calendar instance is active
1423 IF l_b_rel_exists = TRUE AND (NOT igs_fi_crdapi_util.validate_cal_inst( p_v_cal_type => l_c_get_todo_ref_recs.cal_type,
1424 p_n_ci_sequence_number => l_c_get_todo_ref_recs.CI_SEQUENCE_NUMBER,
1425 p_v_s_cal_cat => 'LOAD') ) THEN
1426 l_b_rel_exists := FALSE;
1427 l_b_error := TRUE;
1428 IF p_b_log_messages THEN
1429 log_error_message(p_v_person_number =>l_v_person_number,
1430 p_v_fee_period => l_v_fee_period,
1431 p_uooid =>l_c_get_todo_ref_recs.uoo_id,
1432 p_v_message_name =>'IGS_FI_LOAD_CAL_NOT_ACTIVE');
1433 END IF; --End Of Logging Mesage
1434 END IF; -- To Check Whether The Load Calendar Is Active Or Not.
1435
1436 IF l_b_rel_exists = TRUE AND (NOT igs_fi_crdapi_util.validate_cal_inst(p_v_cal_type => l_v_fee_cal_type,
1437 p_n_ci_sequence_number => l_n_fee_ci_sequence_number,
1438 p_v_s_cal_cat =>'FEE') ) THEN
1439 l_b_rel_exists := FALSE;
1440 l_b_error := TRUE;
1441 IF p_b_log_messages THEN
1442 log_error_message(p_v_person_number =>l_v_person_number,
1443 p_v_fee_period => l_v_fee_period,
1444 p_uooid =>l_c_get_todo_ref_recs.uoo_id,
1445 p_v_message_name =>'IGS_FI_FCI_NOTFOUND');
1446 END IF;
1447 END IF; -- End If Part Of Check For Validation Fee Instance Is Active Or Not
1448 END IF; -- End If For The Check Of Fee Calendar Instance Variable.
1449 -- To Check Whether The Fee Calendar Instance Is Active Or Not
1450 -- igs_fi_crdapi_util.validate_cal_inst Returns False When The It Is Not Active.
1451 IF l_b_rel_exists THEN
1452 assess_fees_pvt(p_n_person_id => l_c_get_todo_ref_recs.person_id,
1453 p_v_person_number => l_v_person_number,
1454 p_v_course_cd => l_c_get_todo_ref_recs.course_cd,
1455 p_n_uoo_id => l_c_get_todo_ref_recs.uoo_id,
1456 p_v_fee_cal_type => l_v_fee_cal_type,
1457 p_n_fee_ci_sequence_number => l_n_fee_ci_sequence_number,
1458 p_v_fee_period => l_v_fee_period,
1459 p_v_load_cal_type => l_c_get_todo_ref_recs.cal_type,
1460 p_n_load_ci_sequence_number => l_c_get_todo_ref_recs.ci_sequence_number,
1461 p_d_gl_date => p_d_gl_date,
1462 p_b_log_messages => p_b_log_messages,
1463 x_return_status => l_v_return_status,
1464 x_msg_count => l_n_msg_count,
1465 x_msg_data => l_v_msg_data);
1466 -- If The Return Status From Assess_fees_pvt Is S Then Processing Is Sucessfull
1467 IF l_v_return_status = 'S' THEN
1468 -- If The Special Fees Co Routine Has Been Processed Sucessfully The Update The Logical Delete Date To Sysdate.
1469 igs_pe_std_todo_ref_pkg.update_row (x_rowid =>l_c_get_todo_ref_recs.rowid,
1470 x_person_id =>l_c_get_todo_ref_recs.person_id,
1471 x_s_student_todo_type =>l_c_get_todo_ref_recs.s_student_todo_type,
1472 x_sequence_number =>l_c_get_todo_ref_recs.sequence_number,
1473 x_reference_number =>l_c_get_todo_ref_recs.reference_number,
1474 x_cal_type =>l_c_get_todo_ref_recs.cal_type,
1475 x_ci_sequence_number =>l_c_get_todo_ref_recs.ci_sequence_number,
1476 x_course_cd =>l_c_get_todo_ref_recs.course_cd,
1477 x_unit_cd =>l_c_get_todo_ref_recs.unit_cd,
1478 x_other_reference =>l_c_get_todo_ref_recs.other_reference,
1479 x_logical_delete_dt =>SYSDATE,
1480 x_uoo_id =>l_c_get_todo_ref_recs.uoo_id );
1481 ELSE
1482 --To Indicate Some Of The Record Has Error Out
1483 l_b_error := TRUE;
1484 END IF; -- End if of Check For The L_return_status
1485 -- Code To Unravel The Message Stack And Put It In The Log File.
1486 IF l_n_msg_count = 1 THEN
1487 fnd_message.set_encoded(l_v_msg_data);
1488 fnd_file.put_line(fnd_file.log,fnd_message.get);
1489 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1490 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1491 fnd_file.put_line(fnd_file.log,fnd_message.get);
1492 ELSIF l_n_msg_count <> 0 THEN
1493 FOR l_count IN 1 .. l_n_msg_count LOOP
1494 l_msg := fnd_msg_pub.get(p_msg_index => l_count, p_encoded => 'T');
1495 fnd_message.set_encoded(l_msg);
1496 fnd_file.put_line(fnd_file.log,fnd_message.get);
1497 END LOOP;
1498 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1499 fnd_message.set_token('END_DATE',RPAD(g_v_seperator,77,g_v_seperator));
1500 fnd_file.put_line(fnd_file.log,fnd_message.get);
1501 END IF;
1502 END IF; --end if of l_b_rel_exists
1503 END IF;
1504 END LOOP; -- End Loop Of Cursor c_get_todo_ref_recs
1505 -- This Flag Will Be True If All The Records Identified By The Cursor Are Processed Sucessfully By The
1506 -- Assess_fee_pvt Procedure Then Update The Igs_pe_std_todo Table Logical_delete Date With The System Date.
1507 OPEN c_check_child_exists(cp_n_person_id =>l_c_get_todo_recs.person_id,
1508 cp_n_sequence_number =>l_c_get_todo_recs.sequence_number,
1509 cp_v_todo_type =>'SPECIAL_FEE' );
1510 FETCH c_check_child_exists INTO l_check_child_exists;
1511 IF c_check_child_exists%NOTFOUND THEN
1512 igs_pe_std_todo_pkg.update_row(x_rowid =>l_c_get_todo_recs.rowid,
1513 x_person_id =>l_c_get_todo_recs.person_id,
1514 x_s_student_todo_type =>l_c_get_todo_recs.s_student_todo_type,
1515 x_sequence_number =>l_c_get_todo_recs.sequence_number,
1516 x_todo_dt =>l_c_get_todo_recs.todo_dt ,
1517 x_logical_delete_dt =>SYSDATE );
1518 END IF;
1519 CLOSE c_check_child_exists;
1520 IF p_v_test_run = 'N' THEN
1521 COMMIT;
1522 ELSE
1523 ROLLBACK;
1524 END IF;
1525 END LOOP ;--End Loop Of Cursor c_get_todo_recs
1526 --Code for setting the return status
1527 -- 1) If all the records are processed sucessfully the return status is sucess
1528 -- 2) If all the records have errored out then the return status is Error
1529 -- 3) If some of the Records have completed sucessfully and some errored out the status is warning.
1530 IF l_b_error = FALSE THEN
1531 p_v_return_status := 'S';
1532 ELSE
1533 p_v_return_status := 'W';
1534 END IF;
1535 EXCEPTION
1536 WHEN e_lock_exception THEN
1537 fnd_message.set_name('IGS','IGS_FI_END_DATE');
1538 fnd_message.set_token('END_DATE',igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX','PERSON')||': '||l_v_person_number);
1539 fnd_file.put_line(fnd_file.log,fnd_message.get);
1540 fnd_message.set_name('IGS', 'IGS_FI_RFND_REC_LOCK');
1541 fnd_file.put_line(fnd_file.log,fnd_message.get);
1542 fnd_file.new_line(fnd_file.log);
1543 -- Set status to 'Warning'
1544 p_v_return_status := 'W';
1545 WHEN OTHERS THEN
1546 fnd_Message.Set_Name ('IGS', 'IGS_GE_UNHANDLED_EXP');
1547 fnd_Message.Set_Token('NAME','igs_fi_prc_sp_fees.process_special_fees-'||SUBSTR(sqlerrm,1,500));
1548 igs_ge_msg_stack.ADD;
1549 App_Exception.Raise_Exception;
1550
1551 END process_special_fees;
1552
1553
1554 FUNCTION fisp_insert_record(p_n_person_id IN igs_fi_spa_fee_prds.person_id%TYPE,
1555 p_v_course_cd IN igs_fi_spa_fee_prds.course_cd%TYPE,
1556 p_v_fee_cal_type IN igs_fi_spa_fee_prds.fee_cal_type%TYPE,
1557 p_n_fee_ci_sequence_number IN igs_fi_spa_fee_prds.fee_ci_sequence_number%TYPE)
1558 RETURN BOOLEAN
1559 IS
1560 PRAGMA AUTONOMOUS_TRANSACTION;
1561 /*************************************************************
1562 Created By : akandreg
1563 Date Created By : 25-May-2006
1564 Purpose : This function locks the record in the table IGS_FI_SPA_FEE_PRDS
1565 based on the combination of Person-Course-Fee Period that is
1566 passed as the input parameters. Added as a fix to prevent
1567 concurrent running of multiple instances of the process.
1568
1569 Returns TRUE if locking was successful, FALSE otherwise.
1570
1571 Know limitations, enhancements or remarks
1572 Change History
1573 Who When What
1574 ***************************************************************/
1575
1576 l_rowid ROWID;
1577
1578 BEGIN
1579
1580 l_rowid := NULL;
1581 igs_fi_spa_fee_prds_pkg.insert_row ( x_rowid => l_rowid,
1582 x_person_id => p_n_person_id,
1583 x_course_cd => p_v_course_cd,
1584 x_fee_cal_type => p_v_fee_cal_type,
1585 x_fee_ci_sequence_number => p_n_fee_ci_sequence_number,
1586 x_mode => 'R',
1587 x_transaction_type => 'SPECIAL'
1588 );
1589
1590 --commiting
1591 COMMIT;
1592
1593 RETURN TRUE;
1594
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1598 fnd_log.string(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.fisp_insert_record',SQLERRM);
1599 END IF;
1600 RETURN FALSE;
1601
1602 END fisp_insert_record;
1603
1604 FUNCTION fisp_lock_records(p_n_person_id IN igs_fi_spa_fee_prds.person_id%TYPE,
1605 p_v_course_cd IN igs_fi_spa_fee_prds.course_cd%TYPE,
1606 p_v_fee_cal_type IN igs_fi_spa_fee_prds.fee_cal_type%TYPE,
1607 p_n_fee_ci_sequence_number IN igs_fi_spa_fee_prds.fee_ci_sequence_number%TYPE)
1608 RETURN BOOLEAN IS
1609 /*************************************************************
1610 Created By : akandreg
1611 Date Created By : 25-May-2006
1612 Purpose : Bug 5134636. This function locks the record in the table IGS_FI_SPA_FEE_PRDS
1613 based on the combination of Person-Course-Fee Period that is
1614 passed as the input parameters. Added as a fix to prevent
1615 concurrent running of multiple instances of the process.
1616
1617 Returns TRUE if locking was successful, FALSE otherwise.
1618
1619 Know limitations, enhancements or remarks
1620 Change History
1621 Who When What
1622 ***************************************************************/
1623
1624 CURSOR cur_fee_spa (cp_person_id igs_fi_spa_fee_prds.person_id%TYPE,
1625 cp_course_cd igs_fi_spa_fee_prds.course_cd%TYPE,
1626 cp_fee_cal_type igs_fi_spa_fee_prds.fee_cal_type%TYPE,
1627 cp_fee_ci_sequence_number igs_fi_spa_fee_prds.fee_ci_sequence_number%TYPE,
1628 cp_transaction_type igs_fi_spa_fee_prds.transaction_type%TYPE) IS
1629 SELECT 'x'
1630 FROM igs_fi_spa_fee_prds
1631 WHERE person_id = cp_person_id
1632 AND course_cd = cp_course_cd
1633 AND fee_cal_type = cp_fee_cal_type
1634 AND fee_ci_sequence_number = cp_fee_ci_sequence_number
1635 AND transaction_type = cp_transaction_type
1636 FOR UPDATE NOWAIT;
1637
1638 l_v_dummy VARCHAR2(2) := NULL; -- Dummy variable to hold the value selected in cur_fee_spa
1639
1640 BEGIN
1641
1642 OPEN cur_fee_spa(p_n_person_id,
1643 p_v_course_cd,
1644 p_v_fee_cal_type,
1645 p_n_fee_ci_sequence_number,
1646 'SPECIAL');
1647 FETCH cur_fee_spa INTO l_v_dummy;
1648 IF cur_fee_spa%NOTFOUND THEN
1649 -- If the record does not exist in igs_fi_spa_fee_period table, then insert into the table.
1650 CLOSE cur_fee_spa;
1651 -- Call autonomous function to insert into IGS_FI_SPA_FEE_PRDS
1652 IF fisp_insert_record(p_n_person_id,
1653 p_v_course_cd,
1654 p_v_fee_cal_type,
1655 p_n_fee_ci_sequence_number) THEN
1656 -- After insertion (if insertion was successful), lock the record
1657 OPEN cur_fee_spa(p_n_person_id,
1658 p_v_course_cd,
1659 p_v_fee_cal_type,
1660 p_n_fee_ci_sequence_number,
1661 'SPECIAL');
1662 FETCH cur_fee_spa INTO l_v_dummy;
1663 CLOSE cur_fee_spa;
1664 RETURN TRUE;
1665 ELSE
1666 -- Insertion failed, return FALSE
1667 RETURN FALSE;
1668 END IF;
1669 ELSE
1670 -- If record exists in table igs_fi_spa_fee_period, then lock the record.
1671 CLOSE cur_fee_spa;
1672 RETURN TRUE;
1673 END IF; -- End if for cursor cur_fee_spa NOTFOUND
1674
1675 EXCEPTION
1676 WHEN OTHERS THEN
1677 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) THEN
1678 fnd_log.string(fnd_log.level_unexpected, 'igs.patch.115.sql.igs_fi_prc_sp_fees.fisp_lock_records',SQLERRM);
1679 END IF;
1680 RETURN FALSE;
1681
1682 END fisp_lock_records;
1683
1684 END igs_fi_prc_sp_fees;
[Home] [Help]