1 PACKAGE BODY igs_fi_prc_lockbox AS
2 /* $Header: IGSFI85B.pls 120.3 2006/05/15 06:24:01 svuppala ship $ */
3
4 /******************************************************************
5 Created By : Amit Gairola
6 Date Created By : 12-Jun-2003
7 Purpose : Package Body for the Lockbox Processes
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 svuppala 12-May-2006 Bug 5217319 Added call to format amounts by rounding off to currency precision
13 in the igs_fi_lb_ovfl_errs_pkg.insert_row and igs_fi_lb_rect_errs_pkg.insert_row calls
14 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
15 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
16 svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number.
17 changed logic for credit_number in invoke_credits_api_pvt().
18 shtatiko 27-AUG-2003 Enh# 3045007, Modified valtype2_and_import_rects and initialize.
19 Added two new parameters p_n_credit_type_id, p_n_receipt_amt to invoke_credits_api_pvt
20 And added g_v_inst_payment, g_v_label_bal_amnt and g_v_label_dflt_cr_type.
21 pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds
22 Modified invoke_credits_api_pvt() and
23 valtype2_and_import_rects()
24 agairola 07-Jul-03 Bug: 3032415 Modified validate_type1 procedure
25 ***************************************************************** */
26
27 g_v_ind_no CONSTANT VARCHAR2(5) := 'N';
28 g_v_line_sep CONSTANT VARCHAR2(100) := '+'||RPAD('-',75,'-')||'+';
29 g_v_label_lb_name igs_lookup_values.meaning%TYPE;
30 g_v_noval CONSTANT VARCHAR2(10) := 'NOVALUE';
31 g_n_retcode NUMBER(1) := 0;
32 g_v_gl_date_source igs_fi_lockboxes.gl_date_source_code%TYPE;
33 g_v_user_supp_dt CONSTANT VARCHAR2(30) := 'USER_SUPPLIED_DATE';
34 g_v_deposit_date CONSTANT VARCHAR2(30) := 'DEPOSIT_DATE';
35 g_v_imp_date CONSTANT VARCHAR2(30) := 'IMPORT_DATE';
36 g_v_currency_cd igs_fi_control.currency_cd%TYPE;
37
38 g_b_rec_exists BOOLEAN;
39
40 g_lb_int_tab lb_int_tab;
41 g_t_rec_tab lb_receipt_tab;
42 g_v_app CONSTANT VARCHAR2(5) := 'APP';
43 g_v_enr_deposit CONSTANT VARCHAR2(15) := 'ENRDEPOSIT';
44 g_v_oth_deposit CONSTANT VARCHAR2(15) := 'OTHDEPOSIT';
45 g_v_inst_payment CONSTANT VARCHAR2(30) := 'INSTALLMENT_PAYMENTS';
46 g_v_fee CONSTANT VARCHAR2(5) := 'FEE';
47 g_v_adm CONSTANT VARCHAR2(5) := 'ADM';
48 g_v_success CONSTANT VARCHAR2(10) := 'SUCCESS';
49 g_v_error CONSTANT VARCHAR2(10) := 'ERROR';
50 g_v_todo CONSTANT VARCHAR2(10) := 'TODO';
51 g_v_receipt CONSTANT VARCHAR2(30) := 'RECEIPT';
52 g_v_receipt_oflow CONSTANT VARCHAR2(30) := 'RECEIPT_OFLOW';
53 g_v_batch_header CONSTANT VARCHAR2(30) := 'BATCH_HEADER';
54 g_v_tran_header CONSTANT VARCHAR2(30) := 'TRAN_HEADER';
55 g_v_lock_header CONSTANT VARCHAR2(30) := 'LOCK_HEADER';
56
57 g_v_test_run_val igs_lookup_values.meaning%TYPE;
58 g_v_label_test_run igs_lookup_values.meaning%TYPE;
59 g_v_label_batch igs_lookup_values.meaning%TYPE;
60 g_v_label_item igs_lookup_values.meaning%TYPE;
61 g_v_label_status igs_lookup_values.meaning%TYPE;
62 g_v_label_success igs_lookup_values.meaning%TYPE;
63 g_v_label_err igs_lookup_values.meaning%TYPE;
64 g_v_label_party igs_lookup_values.meaning%TYPE;
65 g_v_label_rec_amnt igs_lookup_values.meaning%TYPE;
66 g_v_label_bal_amnt igs_lookup_values.meaning%TYPE;
67 g_v_label_cr_type igs_lookup_values.meaning%TYPE;
68 g_v_label_dflt_cr_type igs_lookup_values.meaning%TYPE;
69 g_v_label_fee_prd igs_lookup_values.meaning%TYPE;
70 g_v_label_gl_date igs_lookup_values.meaning%TYPE;
71 g_v_label_adm_appl_num igs_lookup_values.meaning%TYPE;
72 g_v_label_charge_code igs_lookup_values.meaning%TYPE;
73 g_v_label_bank_app_amt igs_lookup_values.meaning%TYPE;
74 g_v_label_act_app_amt igs_lookup_values.meaning%TYPE;
75 g_v_label_num_rec igs_lookup_values.meaning%TYPE;
76 g_v_label_cur_rec igs_lookup_values.meaning%TYPE;
77 g_v_label_type1 igs_lookup_values.meaning%TYPE;
78 g_v_cr_desc igs_lookup_values.meaning%TYPE;
79 g_b_log_head BOOLEAN := FALSE;
80 g_v_label_type2 igs_lookup_values.meaning%TYPE;
81
82 g_v_holds_message fnd_new_messages.message_text%TYPE := NULL;
83
84 PROCEDURE log_line(p_v_label VARCHAR2,
85 p_v_value VARCHAR2) AS
86 /******************************************************************
87 Created By : Amit Gairola
88 Date Created By : 12-Jun-2003
89 Purpose : Procedure for logging a single line
90
91 Known limitations,enhancements,remarks:
92 Change History
93 Who When What
94 ***************************************************************** */
95 BEGIN
96 fnd_file.put_line(fnd_file.log,
97 p_v_label||' : '||p_v_value);
98 END log_line;
99
100 PROCEDURE initialize AS
101 /******************************************************************
102 Created By : Amit Gairola
103 Date Created By : 12-Jun-2003
104 Purpose : Procedure for initializing variables
105
106 Known limitations,enhancements,remarks:
107 Change History
108 Who When What
109 ***************************************************************** */
110 l_lb_int_tab lb_int_tab;
111 l_t_rec_tab lb_receipt_tab;
112 BEGIN
113
114 -- Procedure for initializing the global variables and the
115 -- initializing of the PL/SQL tables
116 g_lb_int_tab := l_lb_int_tab;
117 g_t_rec_tab := l_t_rec_tab;
118 g_b_log_head := FALSE;
119 g_n_retcode := 0;
120 g_v_cr_desc := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
121 'LOCKBOX_PAYMENT');
122 g_v_label_test_run := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
123 'TEST_RUN');
124 g_v_label_lb_name := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
125 'LOCKBOX_NUMBER');
126 g_v_label_batch := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
127 'BATCH_NAME');
128 g_v_label_item := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
129 'ITEM_NUMBER');
130 g_v_label_status := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
131 'STATUS');
132 g_v_label_success := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
133 'SUCCESS');
134 g_v_label_err := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
135 'ERROR');
136 g_v_label_party := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
137 'PARTY');
138 g_v_label_rec_amnt := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
139 'REC_AMOUNT');
140 g_v_label_bal_amnt := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
141 'BALANCE_AMOUNT');
142 g_v_label_cr_type := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
143 'CREDIT_TYPE_NAME');
144 g_v_label_dflt_cr_type := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
145 'DFLT_CREDIT_TYPE_NAME');
146 g_v_label_fee_prd := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
147 'FEE_PERIOD');
148 g_v_label_gl_date := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
149 'GL_DATE');
150 g_v_label_adm_appl_num := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
151 'ADM_APPL_NUM');
152 g_v_label_charge_code := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
153 'CHARGE_NUMBER');
154 g_v_label_bank_app_amt := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
155 'BANK_APPL_AMT');
156 g_v_label_act_app_amt := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
157 'ACT_APPL_AMT');
158 g_v_label_num_rec := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
159 'REC_PROCESS');
160 g_v_label_cur_rec := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
161 'AMT_PROCESS');
162 g_v_label_type1 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
163 'TYPE1_ERR');
164 g_v_label_type2 := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX',
165 'TYPE2_ERR');
166 g_b_rec_exists := FALSE;
167 END initialize;
168
169 PROCEDURE log_type2_err(p_v_lockbox_name VARCHAR2,
170 p_v_batch_name VARCHAR2,
171 p_v_item_number VARCHAR2) AS
172 /******************************************************************
173 Created By : Amit Gairola
174 Date Created By : 12-Jun-2003
175 Purpose : Procedure for logging type2 error headers
176
177 Known limitations,enhancements,remarks:
178 Change History
179 Who When What
180 ***************************************************************** */
181 BEGIN
182
183 -- g_b_log_head is a global boolean variable used if the
184 -- Lockbox Name, BatchName, Item Number and status have to be logged
185 -- for type 2 validations
186 IF NOT g_b_log_head THEN
187 fnd_file.new_line(fnd_file.log);
188 log_line(g_v_label_lb_name,
189 p_v_lockbox_name);
190 log_line(g_v_label_batch,
191 p_v_batch_name);
192 log_line(g_v_label_item,
193 p_v_item_number);
194 log_line(g_v_label_status,
195 g_v_label_err);
196 g_b_log_head := TRUE;
197 END IF;
198 END log_type2_err;
199
200 FUNCTION validate_parameters(p_v_lockbox_name VARCHAR2,
201 p_d_gl_date DATE,
202 p_v_test_run VARCHAR2) RETURN BOOLEAN AS
203 /******************************************************************
204 Created By : Amit Gairola
205 Date Created By : 12-Jun-2003
206 Purpose : Procedure for validating parameters
207
208 Known limitations,enhancements,remarks:
209 Change History
210 Who When What
211 ***************************************************************** */
212 CURSOR cur_lockbox(cp_lockbox_name VARCHAR2,
213 cp_ind_yn VARCHAR2) IS
214 SELECT gl_date_source_code
215 FROM igs_fi_lockboxes
216 WHERE lockbox_name = cp_lockbox_name
217 AND closed_flag = cp_ind_yn;
218
219 l_b_val_parm BOOLEAN;
220 l_v_manage_acc igs_fi_control.manage_accounts%TYPE;
221 l_v_message_name fnd_new_messages.message_name%TYPE;
222 l_v_closing_status VARCHAR2(10);
223 l_v_curr_desc fnd_currencies.description%TYPE;
224 l_v_message_text VARCHAR2(2000);
225 BEGIN
226 l_b_val_parm := TRUE;
227 fnd_file.put_line(fnd_file.log,
228 g_v_line_sep);
229 fnd_file.put_line(fnd_file.log,
230 fnd_message.get_string('IGS',
231 'IGS_FI_ANC_LOG_PARM'));
232 fnd_file.new_line(fnd_file.log);
233
234 g_v_test_run_val := igs_fi_gen_gl.get_lkp_meaning('YES_NO',
235 p_v_test_run);
236
237 -- Log the parameters
238 log_line(g_v_label_lb_name,
239 p_v_lockbox_name);
240 log_line(g_v_label_gl_date,
241 p_d_gl_date);
242 log_line(g_v_label_test_run,
243 NVL(g_v_test_run_val,p_v_test_run));
244
245 fnd_file.new_line(fnd_file.log);
246 fnd_file.put_line(fnd_file.log,
247 g_v_line_sep);
248
249 -- Check for Manage Account
250 igs_fi_com_rec_interface.chk_manage_account(p_v_manage_acc => l_v_manage_acc,
251 p_v_message_name => l_v_message_name);
252
253 -- If Manage Accounts is NULL or is OTHER then error has to be raised
254 IF ((l_v_manage_acc IS NULL) OR (l_v_manage_acc = 'OTHER')) THEN
255 l_b_val_parm := FALSE;
256 fnd_message.set_name('IGS',
257 l_v_message_name);
258 fnd_file.put_line(fnd_file.log,
259 fnd_message.get);
260 RETURN l_b_val_parm;
261 END IF;
262
263 l_v_message_name := null;
264 igs_fi_gen_gl.finp_get_cur(p_v_currency_cd => g_v_currency_cd,
265 p_v_curr_desc => l_v_curr_desc,
266 p_v_message_name => l_v_message_text);
267 IF l_v_message_text IS NOT NULL THEN
268 l_b_val_parm := FALSE;
269 fnd_message.set_name('IGS',
270 l_v_message_name);
271 fnd_file.put_line(fnd_file.log,
272 fnd_message.get);
273 END IF;
274
275 -- Validate if the Lockbox Name is valid
276 OPEN cur_lockbox(p_v_lockbox_name,
277 'N');
278 FETCH cur_lockbox INTO g_v_gl_date_source;
279 IF cur_lockbox%NOTFOUND THEN
280 l_b_val_parm := FALSE;
281 fnd_message.set_name('IGS',
282 'IGS_FI_CAPI_INVALID_LOCKBOX');
283 fnd_message.set_token('LOCKBOX_NAME',
284 p_v_lockbox_name);
285 fnd_file.put_line(fnd_file.log,
286 fnd_message.get);
287 END IF;
288 CLOSE cur_lockbox;
289
290 -- If the gl date source is user supplied and gl_date is not passed as input
291 -- then error needs to be raised.
292 IF g_v_gl_date_source = g_v_user_supp_dt THEN
293 IF p_d_gl_date IS NULL THEN
294 l_b_val_parm := FALSE;
295 fnd_message.set_name('IGS',
296 'IGS_FI_GL_DATE_REQD');
297 fnd_file.put_line(fnd_file.log,
298 fnd_message.get);
299 ELSE
300
301 -- If the GL Date is passed then, check if the GL Date is in an open/future period
302 igs_fi_gen_gl.get_period_status_for_date(p_d_date => p_d_gl_date,
303 p_v_closing_status => l_v_closing_status,
304 p_v_message_name => l_v_message_name);
305 IF l_v_message_name IS NOT NULL THEN
306 l_b_val_parm := FALSE;
307 fnd_message.set_name('IGS',
308 l_v_message_name);
309 fnd_file.put_line(fnd_file.log,
310 fnd_message.get);
311 ELSE
312 IF l_v_closing_status NOT IN ('O','F') THEN
313 fnd_message.set_name('IGS',
314 'IGS_FI_INVALID_GL_DATE');
315 fnd_message.set_token('GL_DATE',
316 p_d_gl_date);
317 fnd_file.put_line(fnd_file.log,
318 fnd_message.get);
319 l_b_val_parm := FALSE;
320 END IF;
321 END IF;
322 END IF;
323 ELSE
324
325 -- Log message to the user that the GL Date has been disregarded because the
326 -- gl date source for the lockbox is not user supplied
327 IF p_d_gl_date IS NOT NULL THEN
328 fnd_message.set_name('IGS',
329 'IGS_FI_GL_DATE_DISRGD');
330 fnd_file.put_line(fnd_file.log,
331 fnd_message.get);
332 END IF;
333 END IF;
334
335 -- validate for Test Run
336 IF p_v_test_run NOT IN ('Y','N') THEN
337 l_b_val_parm := FALSE;
338 fnd_message.set_name('IGS',
339 'IGS_GE_INVALID_VALUE');
340 fnd_file.put_line(fnd_file.log,
341 fnd_message.get);
342 END IF;
343
344 fnd_file.put_line(fnd_file.log,
345 g_v_line_sep);
346 RETURN l_b_val_parm;
347 END validate_parameters;
348
349 FUNCTION get_credit_type_name(p_n_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) RETURN VARCHAR2 AS
350 /******************************************************************
351 Created By : Amit Gairola
352 Date Created By : 12-Jun-2003
353 Purpose : Function for getting the credit type name
354
355 Known limitations,enhancements,remarks:
356 Change History
357 Who When What
358 ***************************************************************** */
359 CURSOR cur_crt(cp_credit_type_id igs_fi_cr_types.credit_type_id%TYPE) IS
360 SELECT credit_type_name
361 FROM igs_fi_cr_types
362 WHERE credit_type_id = cp_credit_type_id;
363
364 l_v_cr_type_name igs_fi_cr_types.credit_type_name%TYPE;
365 BEGIN
366 OPEN cur_crt(p_n_credit_type_id);
367 FETCH cur_crt INTO l_v_cr_type_name;
368 CLOSE cur_crt;
369
370 RETURN l_v_cr_type_name;
371 END get_credit_type_name;
372
373 FUNCTION get_fee_period(p_v_cal_type igs_ca_inst.cal_type%TYPE,
374 p_n_cal_seq igs_ca_inst.sequence_number%TYPE) RETURN VARCHAR2 AS
375 /******************************************************************
376 Created By : Amit Gairola
377 Date Created By : 12-Jun-2003
378 Purpose : Function for getting the fee period
379
380 Known limitations,enhancements,remarks:
381 Change History
382 Who When What
383 ***************************************************************** */
384 CURSOR cur_fee_prd(cp_cal_type igs_ca_inst.cal_type%TYPE,
385 cp_cal_seq igs_ca_inst.sequence_number%TYPE) IS
386 SELECT description
387 FROM igs_ca_inst
388 WHERE cal_type = cp_cal_type
389 AND sequence_number = cp_cal_seq;
390
391 l_v_desc igs_ca_inst.description%TYPE;
392 BEGIN
393 IF p_v_cal_type IS NULL OR p_n_cal_seq IS NULL THEN
394 l_v_desc := NULL;
395 ELSE
396 OPEN cur_fee_prd(p_v_cal_type,
397 p_n_cal_seq);
398 FETCH cur_fee_prd INTO l_v_desc;
399 CLOSE cur_fee_prd;
400 END IF;
401
402 RETURN l_v_desc;
403 END get_fee_period;
404
405 FUNCTION get_record_type_meaning(p_v_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE,
406 p_v_record_identifier_cd igs_fi_lockbox_ints.record_identifier_cd%TYPE) RETURN VARCHAR2 AS
407 /******************************************************************
408 Created By : Amit Gairola
409 Date Created By : 12-Jun-2003
410 Purpose : Function for getting the record type meaning
411
412 Known limitations,enhancements,remarks:
413 Change History
414 Who When What
415 ***************************************************************** */
416 CURSOR cur_rc_type(cp_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE,
417 cp_rec_identifier_cd igs_fi_lockbox_ints.record_identifier_cd%TYPE) IS
418 SELECT record_type_code
419 FROM igs_fi_lb_rec_types
420 WHERE lockbox_name = cp_lockbox_name
421 AND record_identifier_cd = cp_rec_identifier_cd;
422
423 l_v_rc_type igs_fi_lb_rec_types.record_type_code%TYPE;
424 BEGIN
425 l_v_rc_type := NULL;
426 OPEN cur_rc_type(p_v_lockbox_name,
427 p_v_record_identifier_cd);
428 FETCH cur_rc_type INTO l_v_rc_type;
429 CLOSE cur_rc_type;
430
431 RETURN NVL(l_v_rc_type, g_v_noval);
432 END get_record_type_meaning;
433
434 FUNCTION populate_lb_interface(p_v_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE) RETURN BOOLEAN AS
435 /******************************************************************
436 Created By : Amit Gairola
437 Date Created By : 12-Jun-2003
438 Purpose : Function for populating the global PL/SQL interface table
439
440 Known limitations,enhancements,remarks:
441 Change History
442 Who When What
443 ***************************************************************** */
444
445 -- Cursor for getting the TODO records for the Lockbox Name passed as input
446 -- from the Interface table
447 CURSOR cur_lb_ints(cp_lockbox_name VARCHAR2,
448 cp_status VARCHAR2) IS
449 SELECT rowid row_id, lb.*
450 FROM igs_fi_lockbox_ints lb
451 WHERE lockbox_name = cp_lockbox_name
452 AND record_status = cp_status
453 FOR UPDATE NOWAIT
454 ORDER BY lockbox_interface_id;
455
456 -- Cursor for getting the TODO records for the Batch Name passed as input
457 -- from the Interface table where lockbox name is null
458 CURSOR cur_lb_batch(cp_batch_name igs_fi_lockbox_ints.batch_name%TYPE,
459 cp_status VARCHAR2) IS
460 SELECT rowid row_id,
461 lb.*
462 FROM igs_fi_lockbox_ints lb
463 WHERE batch_name = cp_batch_name
464 AND record_status = cp_status
465 AND lockbox_name IS NULL
466 FOR UPDATE NOWAIT
467 ORDER BY lockbox_interface_id;
468
469 l_b_upd_err BOOLEAN;
470 l_n_cntr NUMBER(38);
471 l_n_cntr1 NUMBER(38);
472 l_b_batch_head_exists BOOLEAN;
473 l_v_rec_type_meaning igs_fi_lb_rec_types.record_type_code%TYPE;
474 BEGIN
475 l_b_upd_err := FALSE;
476 l_b_batch_head_exists := FALSE;
477
478 l_n_cntr := 0;
479 l_n_cntr1 := 0;
480
481 -- Loop across the TODO records in the Interface table for the lockbox name
482 FOR l_rec_lb_ints IN cur_lb_ints(p_v_lockbox_name,g_v_todo) LOOP
483
484 -- For each record selected, add the record to the PL/SQL table
485 g_b_rec_exists := TRUE;
486
487 l_n_cntr1 := l_n_cntr1+1;
488
489 IF l_n_cntr1 = 1 THEN
490 fnd_file.put_line(fnd_file.log,
491 g_v_line_sep);
492 fnd_file.put_line(fnd_file.log,
493 g_v_label_type1);
494 fnd_file.put_line(fnd_file.log,
495 g_v_line_sep);
496 fnd_file.new_line(fnd_file.log);
497 END IF;
498
499 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).row_id := l_rec_lb_ints.row_id;
500 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_interface_id := l_rec_lb_ints.lockbox_interface_id;
501 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).record_identifier_cd := l_rec_lb_ints.record_identifier_cd;
502 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).record_status := l_rec_lb_ints.record_status;
503 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).deposit_date := l_rec_lb_ints.deposit_date;
504 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).transmission_record_count := l_rec_lb_ints.transmission_record_count;
505 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).transmission_amt := l_rec_lb_ints.transmission_amt;
506 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_name := l_rec_lb_ints.lockbox_name;
507 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_batch_count := l_rec_lb_ints.lockbox_batch_count;
508 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_record_count := l_rec_lb_ints.lockbox_record_count;
509 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_amt := l_rec_lb_ints.lockbox_amt;
510 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_name := l_rec_lb_ints.batch_name;
511 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_record_count := l_rec_lb_ints.batch_record_count;
512 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_amt := l_rec_lb_ints.batch_amt;
513 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).item_number := l_rec_lb_ints.item_number;
514 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).receipt_amt := l_rec_lb_ints.receipt_amt;
515 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).check_cd := l_rec_lb_ints.check_cd;
516 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).party_number := l_rec_lb_ints.party_number;
517 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).payer_name := l_rec_lb_ints.payer_name;
518 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd1 := l_rec_lb_ints.charge_cd1;
519 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd2 := l_rec_lb_ints.charge_cd2;
520 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd3 := l_rec_lb_ints.charge_cd3;
521 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd4 := l_rec_lb_ints.charge_cd4;
522 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd5 := l_rec_lb_ints.charge_cd5;
523 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd6 := l_rec_lb_ints.charge_cd6;
524 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd7 := l_rec_lb_ints.charge_cd7;
525 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd8 := l_rec_lb_ints.charge_cd8;
526 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt1 := l_rec_lb_ints.applied_amt1;
527 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt2 := l_rec_lb_ints.applied_amt2;
528 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt3 := l_rec_lb_ints.applied_amt3;
529 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt4 := l_rec_lb_ints.applied_amt4;
530 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt5 := l_rec_lb_ints.applied_amt5;
531 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt6 := l_rec_lb_ints.applied_amt6;
532 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt7 := l_rec_lb_ints.applied_amt7;
533 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt8 := l_rec_lb_ints.applied_amt8;
534 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).credit_type_cd := l_rec_lb_ints.credit_type_cd;
535 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).fee_cal_instance_cd := l_rec_lb_ints.fee_cal_instance_cd;
536 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).adm_application_id := l_rec_lb_ints.adm_application_id;
537 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute_category := l_rec_lb_ints.attribute_category;
538 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute1 := l_rec_lb_ints.attribute1;
539 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute2 := l_rec_lb_ints.attribute2;
540 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute3 := l_rec_lb_ints.attribute3;
541 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute4 := l_rec_lb_ints.attribute4;
542 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute5 := l_rec_lb_ints.attribute5;
543 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute6 := l_rec_lb_ints.attribute6;
544 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute7 := l_rec_lb_ints.attribute7;
545 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute8 := l_rec_lb_ints.attribute8;
546 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute9 := l_rec_lb_ints.attribute9;
547 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute10 := l_rec_lb_ints.attribute10;
548 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute11 := l_rec_lb_ints.attribute11;
549 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute12 := l_rec_lb_ints.attribute12;
550 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute13 := l_rec_lb_ints.attribute13;
551 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute14 := l_rec_lb_ints.attribute14;
552 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute15 := l_rec_lb_ints.attribute15;
553 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute16 := l_rec_lb_ints.attribute16;
554 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute17 := l_rec_lb_ints.attribute17;
555 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute18 := l_rec_lb_ints.attribute18;
556 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute19 := l_rec_lb_ints.attribute19;
557 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute20 := l_rec_lb_ints.attribute20;
558
559 -- Get the Record Type Meaning for the Lockbox Name and the Record Identifier of the
560 -- Interface table record.
561 l_v_rec_type_meaning := NULL;
562 l_v_rec_type_meaning := get_record_type_meaning(p_v_lockbox_name,
563 l_rec_lb_ints.record_identifier_cd);
564
565 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).system_record_identifier := l_v_rec_type_meaning;
566
567 -- If the record type meaning is NOVALUE, then there is an error and the set of Interface records should be marked as
568 -- Error
569 IF l_v_rec_type_meaning = g_v_noval THEN
570 fnd_message.set_name('IGS','IGS_FI_INVALID_RECORD_ID');
571 fnd_message.set_token('RECORD_IDENTIFIER',
572 l_rec_lb_ints.record_identifier_cd);
573 fnd_message.set_token('LOCKBOX_NAME',
574 p_v_lockbox_name);
575 fnd_file.put_line(fnd_file.log,
576 fnd_message.get);
577 l_b_upd_err := TRUE;
578 END IF;
579
580 -- Check if Batch Headers exist. Will be used in the next step. This has been kept to improve the
581 -- performance in case there are no batch headers
582 IF l_v_rec_type_meaning = g_v_batch_header THEN
583 l_b_batch_head_exists := TRUE;
584 END IF;
585 END LOOP;
586
587 -- If the batch headers exist in the first selection of interface records, then
588 IF l_b_batch_head_exists THEN
589 l_n_cntr := null;
590
591 -- For the batch name in the batch header, loop across the table to select the receipt and receipt overflow
592 -- records that have the same batch name and null lockbox name
593 FOR l_n_cntr IN g_lb_int_tab.FIRST..g_lb_int_tab.LAST LOOP
594 IF g_lb_int_tab.EXISTS(l_n_cntr) THEN
595 IF ((g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_batch_header) AND
596 g_lb_int_tab(l_n_cntr).batch_name IS NOT NULL) THEN
597 FOR l_rec_lb_ints IN cur_lb_batch(g_lb_int_tab(l_n_cntr).batch_name,g_v_todo) LOOP
598 IF NOT g_lb_int_tab.EXISTS(l_rec_lb_ints.lockbox_interface_id) THEN
599 l_v_rec_type_meaning := NULL;
600 l_v_rec_type_meaning := get_record_type_meaning(p_v_lockbox_name,
601 l_rec_lb_ints.record_identifier_cd);
602
603 -- If the record type meaning is NOVALUE, then there is an error and the set of Interface records should be marked as
604 -- Error
605 IF l_v_rec_type_meaning = g_v_noval THEN
606 fnd_message.set_name('IGS','IGS_FI_INVALID_RECORD_ID');
607 fnd_message.set_token('RECORD_IDENTIFIER',
608 l_rec_lb_ints.record_identifier_cd);
609 fnd_message.set_token('LOCKBOX_NAME',
610 p_v_lockbox_name);
611 fnd_file.put_line(fnd_file.log,
612 fnd_message.get);
613 l_b_upd_err := TRUE;
614 END IF;
615
616 -- If the record type meaning is either Receipt/Receipt Overflow/Novalue, then add the record to the
617 -- Interface table
618 IF l_v_rec_type_meaning IN (g_v_receipt,
619 g_v_receipt_oflow,
620 g_v_noval) THEN
621 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).row_id := l_rec_lb_ints.row_id;
622 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_interface_id := l_rec_lb_ints.lockbox_interface_id;
623 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).record_identifier_cd := l_rec_lb_ints.record_identifier_cd;
624 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).record_status := l_rec_lb_ints.record_status;
625 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).deposit_date := l_rec_lb_ints.deposit_date;
626 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).transmission_record_count := l_rec_lb_ints.transmission_record_count;
627 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).transmission_amt := l_rec_lb_ints.transmission_amt;
628 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_name := p_v_lockbox_name;
629 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_batch_count := l_rec_lb_ints.lockbox_batch_count;
630 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_record_count := l_rec_lb_ints.lockbox_record_count;
631 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).lockbox_amt := l_rec_lb_ints.lockbox_amt;
632 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_name := l_rec_lb_ints.batch_name;
633 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_amt := l_rec_lb_ints.batch_amt;
634 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).batch_record_count := l_rec_lb_ints.batch_record_count;
635 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).item_number := l_rec_lb_ints.item_number;
636 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).receipt_amt := l_rec_lb_ints.receipt_amt;
637 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).check_cd := l_rec_lb_ints.check_cd;
638 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).party_number := l_rec_lb_ints.party_number;
639 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).payer_name := l_rec_lb_ints.payer_name;
640 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd1 := l_rec_lb_ints.charge_cd1;
641 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd2 := l_rec_lb_ints.charge_cd2;
642 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd3 := l_rec_lb_ints.charge_cd3;
643 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd4 := l_rec_lb_ints.charge_cd4;
644 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd5 := l_rec_lb_ints.charge_cd5;
645 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd6 := l_rec_lb_ints.charge_cd6;
646 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd7 := l_rec_lb_ints.charge_cd7;
647 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).charge_cd8 := l_rec_lb_ints.charge_cd8;
648 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt1 := l_rec_lb_ints.applied_amt1;
649 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt2 := l_rec_lb_ints.applied_amt2;
650 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt3 := l_rec_lb_ints.applied_amt3;
651 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt4 := l_rec_lb_ints.applied_amt4;
652 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt5 := l_rec_lb_ints.applied_amt5;
653 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt6 := l_rec_lb_ints.applied_amt6;
654 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt7 := l_rec_lb_ints.applied_amt7;
655 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).applied_amt8 := l_rec_lb_ints.applied_amt8;
656 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).credit_type_cd := l_rec_lb_ints.credit_type_cd;
657 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).fee_cal_instance_cd := l_rec_lb_ints.fee_cal_instance_cd;
658 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).adm_application_id := l_rec_lb_ints.adm_application_id;
659 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute_category := l_rec_lb_ints.attribute_category;
660 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute1 := l_rec_lb_ints.attribute1;
661 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute2 := l_rec_lb_ints.attribute2;
662 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute3 := l_rec_lb_ints.attribute3;
663 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute4 := l_rec_lb_ints.attribute4;
664 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute5 := l_rec_lb_ints.attribute5;
665 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute6 := l_rec_lb_ints.attribute6;
666 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute7 := l_rec_lb_ints.attribute7;
667 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute8 := l_rec_lb_ints.attribute8;
668 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute9 := l_rec_lb_ints.attribute9;
669 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute10 := l_rec_lb_ints.attribute10;
670 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute11 := l_rec_lb_ints.attribute11;
671 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute12 := l_rec_lb_ints.attribute12;
672 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute13 := l_rec_lb_ints.attribute13;
673 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute14 := l_rec_lb_ints.attribute14;
674 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute15 := l_rec_lb_ints.attribute15;
675 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute16 := l_rec_lb_ints.attribute16;
676 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute17 := l_rec_lb_ints.attribute17;
677 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute18 := l_rec_lb_ints.attribute18;
678 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute19 := l_rec_lb_ints.attribute19;
679 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).attribute20 := l_rec_lb_ints.attribute20;
680 g_lb_int_tab(l_rec_lb_ints.lockbox_interface_id).system_record_identifier := l_v_rec_type_meaning;
681 END IF;
682 END IF;
683 END LOOP;
684 END IF;
685 END IF;
686 END LOOP;
687 END IF;
688
689 RETURN l_b_upd_err;
690 END populate_lb_interface;
691
692 FUNCTION validate_type1 RETURN VARCHAR2 AS
693 /******************************************************************
694 Created By : Amit Gairola
695 Date Created By : 12-Jun-2003
696 Purpose : Function for type 1 validations
697
698 Known limitations,enhancements,remarks:
699 Change History
700 Who When What
701 agairola 07-Jul-03 Bug: 3032415 Added the check for the negative amount
702 for the receipt and receipt overflow records
703 agairola 01-Jul-03 Bug: 3030453 Added the check for the batch name
704 also when the Amount is being summed up
705 ***************************************************************** */
706 l_t_th_tab lb_int_tab;
707 l_n_th_cntr NUMBER(38) := 0;
708
709 l_t_lh_tab lb_int_tab;
710 l_n_lh_cntr NUMBER(38) := 0;
711
712 l_t_bh_tab lb_int_tab;
713 l_n_bh_cntr NUMBER(38) := 0;
714
715 l_t_rc_tab lb_int_tab;
716 l_n_rc_cntr NUMBER(38) := 0;
717
718 l_t_ro_tab lb_int_tab;
719 l_n_ro_cntr NUMBER(38) := 0;
720
721 l_n_cntr NUMBER(38) := 0;
722 l_n_cntr1 NUMBER(38) := 0;
723 l_v_val_status VARCHAR2(1);
724
725 TYPE t_distinct_batch IS TABLE OF igs_fi_lockbox_ints.batch_name%TYPE
726 INDEX BY BINARY_INTEGER;
727
728 l_t_distinct_batch t_distinct_batch;
729 l_n_receipt_amount igs_fi_credits.amount%TYPE;
730 l_b_distinct_batch_found BOOLEAN := FALSE;
731 l_n_batch_count NUMBER(38);
732 l_n_batch_amount igs_fi_credits.amount%TYPE;
733 l_b_rec_batch BOOLEAN;
734 l_b_batch_unq BOOLEAN;
735 l_b_chg_amt_match BOOLEAN;
736 l_n_amt_appl igs_fi_lockbox_ints.applied_amt1%TYPE;
737 l_b_rc_ro_match BOOLEAN;
738 l_b_ro_chg_appl BOOLEAN;
739 l_n_distinct_batch_count NUMBER(38);
740 l_b_dup_batches BOOLEAN;
741
742 l_b_ro_identified BOOLEAN;
743 BEGIN
744
745 -- Here, in this procedure, the main interface PL/SQL table is broken down into
746 -- different PL/SQL tables in order to segregate the different types of records
747 -- This has been done to improve the performance of the process.
748 l_t_th_tab.DELETE;
749 l_t_bh_tab.DELETE;
750 l_t_lh_tab.DELETE;
751 l_t_rc_tab.DELETE;
752 l_t_ro_tab.DELETE;
753 l_t_distinct_batch.DELETE;
754
755 l_v_val_status := 'S';
756
757 -- If there are any records in the PL/SQL table, then loop across the records
758 -- and for each type of record, add it to the corresponding PL/SQL table
759 IF g_lb_int_tab.COUNT > 0 THEN
760 l_n_cntr := 0;
761 FOR l_n_cntr IN g_lb_int_tab.FIRST..g_lb_int_tab.LAST LOOP
762 IF g_lb_int_tab.EXISTS(l_n_cntr) THEN
763 IF g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_tran_header THEN
764 l_n_th_cntr := l_n_th_cntr + 1;
765 l_t_th_tab(l_n_th_cntr) := g_lb_int_tab(l_n_cntr);
766 ELSIF g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_lock_header THEN
767 l_n_lh_cntr := l_n_lh_cntr + 1;
768 l_t_lh_tab(l_n_lh_cntr) := g_lb_int_tab(l_n_cntr);
769 ELSIF g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_batch_header THEN
770 l_n_bh_cntr := l_n_bh_cntr + 1;
771 l_t_bh_tab(l_n_bh_cntr) := g_lb_int_tab(l_n_cntr);
772 ELSIF g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_receipt THEN
773 l_n_rc_cntr := l_n_rc_cntr + 1;
774 l_t_rc_tab(l_n_rc_cntr) := g_lb_int_tab(l_n_cntr);
775
776 -- Sum up the Receipt Amount
777 l_n_receipt_amount := NVL(l_n_receipt_amount,0) +
778 NVL(g_lb_int_tab(l_n_cntr).receipt_amt,0);
779 ELSIF g_lb_int_tab(l_n_cntr).system_record_identifier = g_v_receipt_oflow THEN
780 l_n_ro_cntr := l_n_ro_cntr + 1;
781 l_t_ro_tab(l_n_ro_cntr) := g_lb_int_tab(l_n_cntr);
782 END IF;
783 END IF;
784 END LOOP;
785 END IF;
786
787 -- If there are records in the Transaction Header PL/SQL table
788 IF l_t_th_tab.COUNT > 0 THEN
789 l_n_cntr := 0;
790
791 -- Loop across the Transaction Header PL/SQL table
792 FOR l_n_cntr IN l_t_th_tab.FIRST..l_t_th_tab.LAST LOOP
793 IF l_t_th_tab.EXISTS(l_n_cntr) THEN
794
795 -- If the transmission record count is not null and the
796 -- transmission record count is not equal to the number of receipts then
797 -- Log the error in the log file. This is a type 1 validation error.
798 IF ((l_t_th_tab(l_n_cntr).transmission_record_count IS NOT NULL) AND
799 (l_t_th_tab(l_n_cntr).transmission_record_count <> l_n_rc_cntr)) THEN
800 l_v_val_status := 'E';
801 fnd_message.set_name('IGS',
802 'IGS_FI_TH_COUNT_MISMATCH');
803 fnd_file.put_line(fnd_file.log,
804 fnd_message.get);
805 END IF;
806
807 -- If the Transmission Amount is not null and the transmission amount is not equal to the
808 -- receipt amount, then this error message is logged in the log file.
809 IF ((l_t_th_tab(l_n_cntr).transmission_amt IS NOT NULL) AND
810 (l_t_th_tab(l_n_cntr).transmission_amt <> l_n_receipt_amount)) THEN
811 l_v_val_status := 'E';
812 fnd_message.set_name('IGS',
813 'IGS_FI_TH_AMOUNT_MISMATCH');
814 fnd_file.put_line(fnd_file.log,
815 fnd_message.get);
816 END IF;
817 END IF;
818 END LOOP;
819 END IF;
820
821 l_n_cntr := 0;
822
823 -- If there are records in the Lockbox Header
824 IF l_t_lh_tab.COUNT > 0 THEN
825 l_n_cntr := 0;
826
827 -- Loop across the Lockbox Header record PL/SQL table
828 FOR l_n_cntr IN l_t_lh_tab.FIRST..l_t_lh_tab.LAST LOOP
829 IF l_t_lh_tab.EXISTS(l_n_cntr) THEN
830
831 -- If the lockbox batch count is not null
832 IF l_t_lh_tab(l_n_cntr).lockbox_batch_count IS NOT NULL THEN
833
834 -- If there are batch headers, then check if the batch header count is
835 -- equal to the lockbox batch count.
836 IF l_n_bh_cntr > 0 THEN
837 IF l_t_lh_tab(l_n_cntr).lockbox_batch_count <> l_n_bh_cntr THEN
838 l_v_val_status := 'E';
839 fnd_message.set_name('IGS',
840 'IGS_FI_LH_COUNT_MISMATCH');
841 fnd_file.put_line(fnd_file.log,
842 fnd_message.get);
843 END IF;
844 ELSE
845
846 -- If there are no batch headers, then if there are any records in the receipts
847 IF l_t_rc_tab.COUNT > 0 THEN
848 l_n_cntr1 := 0;
849
850 -- Loop across all the receipts records and count the distinct batches
851 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
852 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
853
854 -- Logic for counting the distinct batches by using a PL/SQL table
855 IF (l_t_rc_tab(l_n_cntr1).batch_name IS NOT NULL) THEN
856 IF l_t_distinct_batch.COUNT = 0 THEN
857 l_t_distinct_batch(1) := l_t_rc_tab(l_n_cntr1).batch_name;
858 ELSE
859 FOR l_n_cntr2 IN l_t_distinct_batch.FIRST..l_t_distinct_batch.LAST LOOP
860 IF l_t_distinct_batch.EXISTS(l_n_cntr2) THEN
861 IF l_t_distinct_batch(l_n_cntr2) = l_t_rc_tab(l_n_cntr1).batch_name THEN
862 l_b_distinct_batch_found := TRUE;
863 ELSE
864 l_b_distinct_batch_found := FALSE;
865 END IF;
866 END IF;
867 END LOOP;
868
869 IF NOT l_b_distinct_batch_found THEN
870 l_n_distinct_batch_count := l_t_distinct_batch.COUNT;
871 l_t_distinct_batch(l_n_distinct_batch_count+1) := l_t_rc_tab(l_n_cntr1).batch_name;
872 END IF;
873 END IF;
874 ELSE
875
876 -- If the receipt record does not have a batch, then log the error message.
877 l_v_val_status := 'E';
878 fnd_message.set_name('IGS',
879 'IGS_FI_LH_BATCH_MISSING');
880 fnd_file.put_line(fnd_file.log,
881 fnd_message.get);
882 END IF;
883 END IF;
884 END LOOP;
885 ELSE
886 l_v_val_status := 'E';
887 fnd_message.set_name('IGS',
888 'IGS_FI_LH_COUNT_MISMATCH');
889 fnd_file.put_line(fnd_file.log,
890 fnd_message.get);
891 END IF;
892
893 -- If the distinct batch count PL/SQL table has some records and the count does not match the Lockbox Batch Count,
894 -- then it is an error condition
895 IF ((l_t_distinct_batch.COUNT <> l_t_lh_tab(l_n_cntr).lockbox_batch_count) AND
896 (l_t_distinct_batch.COUNT > 0)) THEN
897 l_v_val_status := 'E';
898 fnd_message.set_name('IGS',
899 'IGS_FI_LH_COUNT_MISMATCH');
900 fnd_file.put_line(fnd_file.log,
901 fnd_message.get);
902 END IF;
903 l_t_distinct_batch.DELETE;
904 END IF;
905 END IF;
906
907 -- If the Lockbox Amount is not null and is not equal to the receipt amount, then
908 -- log this as error message
909 IF ((l_t_lh_tab(l_n_cntr).lockbox_amt IS NOT NULL) AND
910 (l_t_lh_tab(l_n_cntr).lockbox_amt <> l_n_receipt_amount)) THEN
911 l_v_val_status := 'E';
912 fnd_message.set_name('IGS',
913 'IGS_FI_LH_AMOUNT_MISMATCH');
914 fnd_file.put_line(fnd_file.log,
915 fnd_message.get);
916 END IF;
917
918 -- If the Lockbox Record Count is not equal to the receipt count, then log this as
919 -- error in the log file
920 IF ((l_t_lh_tab(l_n_cntr).lockbox_record_count IS NOT NULL) AND
921 (l_t_lh_tab(l_n_cntr).lockbox_record_count <> l_n_rc_cntr)) THEN
922 l_v_val_status := 'E';
923 fnd_message.set_name('IGS',
924 'IGS_FI_LH_RECCOUNT_MISMATCH');
925 fnd_file.put_line(fnd_file.log,
926 fnd_message.get);
927 END IF;
928 END IF;
929 END LOOP;
930 END IF;
931
932 l_b_dup_batches := FALSE;
933
934 -- If there are any batch header records then
935 IF l_n_bh_cntr > 0 THEN
936
937 -- Loop across the Batch Header table
938 FOR l_n_cntr IN l_t_bh_tab.FIRST..l_t_bh_tab.LAST LOOP
939
940 -- If the batch name is not null, then
941 IF l_t_bh_tab(l_n_cntr).batch_name IS NOT NULL THEN
942
943 -- Loop across the Batch Header table again to identify if there are any duplicate batches
944 -- If the duplicate batches exist then this is an error and log it in the log file.
945 FOR l_n_cntr1 IN l_t_bh_tab.FIRST..l_t_bh_tab.LAST LOOP
946 IF ((l_t_bh_tab(l_n_cntr1).batch_name IS NOT NULL) AND
947 (l_t_bh_tab(l_n_cntr1).batch_name = l_t_bh_tab(l_n_cntr).batch_name) AND
948 (l_t_bh_tab(l_n_cntr1).lockbox_interface_id <> l_t_bh_tab(l_n_cntr).lockbox_interface_id)) THEN
949 l_v_val_status := 'E';
950 fnd_message.set_name('IGS',
951 'IGS_FI_DUP_BATCHES');
952 fnd_file.put_line(fnd_file.log,
953 fnd_message.get);
954 l_b_dup_batches := TRUE;
955 EXIT;
956 END IF;
957 END LOOP;
958
959 IF l_b_dup_batches THEN
960 EXIT;
961 END IF;
962 END IF;
963 END LOOP;
964
965 -- Loop across the Batch Header table records
966 FOR l_n_cntr IN l_t_bh_tab.FIRST..l_t_bh_tab.LAST LOOP
967 l_n_batch_count := 0;
968 l_n_batch_amount := 0;
969
970 -- If the batch name is not null then
971 IF l_t_bh_tab(l_n_cntr).batch_name IS NOT NULL THEN
972
973 -- If either the batch record count is not null or the batch amount is not
974 -- null, then loop across the receipt record for the same batch name and count
975 -- the receipt record count for the batch and sum up the receipt amount for the batch
976 IF ((l_t_bh_tab(l_n_cntr).batch_record_count IS NOT NULL) OR
977 (l_t_bh_tab(l_n_cntr).batch_amt IS NOT NULL)) THEN
978 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
979 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
980 IF ((l_t_rc_tab(l_n_cntr1).batch_name IS NOT NULL) AND
981 (l_t_rc_tab(l_n_cntr1).batch_name = l_t_bh_tab(l_n_cntr).batch_name)) THEN
982 l_n_batch_count := NVL(l_n_batch_count,0) + 1;
983 l_n_batch_amount := NVL(l_n_batch_amount,0) +
984 NVL(l_t_rc_tab(l_n_cntr1).receipt_amt,0);
985 END IF;
986 END IF;
987 END LOOP;
988
989 -- If the batch count does not match the receipt count, then this is an error condition
990 IF l_n_batch_count > 0 THEN
991 IF ((l_n_batch_count <> l_t_bh_tab(l_n_cntr).batch_record_count) AND
992 (l_t_bh_tab(l_n_cntr).batch_record_count IS NOT NULL)) THEN
993 l_v_val_status := 'E';
994 fnd_message.set_name('IGS',
995 'IGS_FI_BH_COUNT_MISMATCH');
996 fnd_message.set_token('BATCH_NAME',
997 l_t_bh_tab(l_n_cntr).batch_name);
998 fnd_file.put_line(fnd_file.log,
999 fnd_message.get);
1000 END IF;
1001 ELSE
1002
1003 -- If there are no records in the receipts PL/SQL table for the batch name
1004 -- then this is an error condition and log this in the log file
1005 l_v_val_status := 'E';
1006 fnd_message.set_name('IGS',
1007 'IGS_FI_NO_REC_IN_BATCH');
1008 fnd_message.set_token('BATCH_NAME',
1009 l_t_bh_tab(l_n_cntr).batch_name);
1010 fnd_file.put_line(fnd_file.log,
1011 fnd_message.get);
1012 END IF;
1013
1014 -- If the batch amount is not equal to the batch amount of the batch header record then
1015 -- log this in the log file
1016 IF ((l_n_batch_amount <> l_t_bh_tab(l_n_cntr).batch_amt) AND
1017 (l_t_bh_tab(l_n_cntr).batch_amt IS NOT NULL)) THEN
1018 l_v_val_status := 'E';
1019 fnd_message.set_name('IGS',
1020 'IGS_FI_BH_AMOUNT_MISMATCH');
1021 fnd_message.set_token('BATCH_NAME',
1022 l_t_bh_tab(l_n_cntr).batch_name);
1023 fnd_file.put_line(fnd_file.log,
1024 fnd_message.get);
1025 END IF;
1026
1027 l_n_batch_count := 0;
1028 l_n_batch_amount := 0;
1029 END IF;
1030 END IF;
1031 END LOOP;
1032 END IF;
1033
1034 -- If there are any receipt records, then
1035 IF l_n_rc_cntr > 0 THEN
1036
1037 -- Loop across the receipt PL/SQL table
1038 FOR l_n_cntr IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
1039 l_b_rec_batch := FALSE;
1040 IF l_t_rc_tab.EXISTS(l_n_cntr) THEN
1041
1042 -- If there is a batch name mentioned for the receipt record and there are any
1043 -- batch header records
1044 IF l_t_rc_tab(l_n_cntr).batch_name IS NOT NULL AND l_n_bh_cntr > 0 THEN
1045
1046 -- Loop across the batch header records to identify if the batch name mentioned in the
1047 -- receipts record matches the batch name in any batch header record. If it matches, then
1048 -- exit.
1049 FOR l_n_cntr1 IN l_t_bh_tab.FIRST..l_t_bh_tab.LAST LOOP
1050 IF l_t_bh_tab.EXISTS(l_n_cntr1) THEN
1051 IF ((l_t_bh_tab(l_n_cntr1).batch_name IS NOT NULL) AND
1052 (l_t_bh_tab(l_n_cntr1).batch_name = l_t_rc_tab(l_n_cntr).batch_name)) THEN
1053 l_b_rec_batch := TRUE;
1054 EXIT;
1055 END IF;
1056 END IF;
1057 END LOOP;
1058
1059 -- If the batch header record match does not happen then log the message in the log file.
1060 IF NOT l_b_rec_batch THEN
1061 l_v_val_status := 'E';
1062 fnd_message.set_name('IGS',
1063 'IGS_FI_NO_REC_BATCH_LINK');
1064 fnd_file.put_line(fnd_file.log,
1065 fnd_message.get);
1066 END IF;
1067 END IF;
1068
1069 -- If the item number of the receipt record is null, then this has to be logged in the log file
1070 -- as error
1071 IF l_t_rc_tab(l_n_cntr).item_number IS NULL THEN
1072 l_v_val_status := 'E';
1073 fnd_message.set_name('IGS',
1074 'IGS_FI_REC_ITEMS_MISSING');
1075 fnd_file.put_line(fnd_file.log,
1076 fnd_message.get);
1077 END IF;
1078
1079 -- If the receipt amount is less than 0 or the receipt amount is null, then log this in the log file
1080 IF ((l_t_rc_tab(l_n_cntr).receipt_amt <0) OR (l_t_rc_tab(l_n_cntr).receipt_amt IS NULL)) THEN
1081 l_v_val_status := 'E';
1082 fnd_message.set_name('IGS',
1083 'IGS_FI_REC_AMT_NOT_VALID');
1084 fnd_message.set_token('ITEM_NUMBER',
1085 l_t_rc_tab(l_n_cntr).item_number);
1086 fnd_message.set_token('BATCH_NAME',
1087 l_t_rc_tab(l_n_cntr).batch_name);
1088 fnd_message.set_token('LOCKBOX_NAME',
1089 l_t_rc_tab(l_n_cntr).lockbox_name);
1090 fnd_file.put_line(fnd_file.log,
1091 fnd_message.get);
1092 END IF;
1093
1094 -- If the party number in the receipt record is null, then log this as an error in the log file
1095 IF l_t_rc_tab(l_n_cntr).party_number IS NULL THEN
1096 l_v_val_status := 'E';
1097 fnd_message.set_name('IGS',
1098 'IGS_FI_REC_PARTY_MISSING');
1099 fnd_message.set_token('ITEM_NUMBER',
1100 l_t_rc_tab(l_n_cntr).item_number);
1101 fnd_message.set_token('BATCH_NAME',
1102 l_t_rc_tab(l_n_cntr).batch_name);
1103 fnd_message.set_token('LOCKBOX_NAME',
1104 l_t_rc_tab(l_n_cntr).lockbox_name);
1105 fnd_file.put_line(fnd_file.log,
1106 fnd_message.get);
1107 END IF;
1108
1109 -- The following logic determines if the item number as mentioned in the receipt record is unique across
1110 -- the receipts records at the batch level(if batch name is provided) or at the lockbox level.
1111 -- If the records are not unique, then the error is logged in the log file.
1112 l_b_batch_unq := FALSE;
1113 IF l_t_rc_tab.COUNT > 0 THEN
1114 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
1115 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
1116 IF l_t_rc_tab(l_n_cntr).batch_name IS NOT NULL THEN
1117 IF ((l_t_rc_tab(l_n_cntr).item_number IS NOT NULL) AND
1118 (l_t_rc_tab(l_n_cntr1).item_number IS NOT NULL)) THEN
1119 IF ((l_t_rc_tab(l_n_cntr1).batch_name IS NOT NULL) AND
1120 (l_t_rc_tab(l_n_cntr1).batch_name = l_t_rc_tab(l_n_cntr).batch_name) AND
1121 (l_t_rc_tab(l_n_cntr1).item_number = l_t_rc_tab(l_n_cntr).item_number) AND
1122 (l_t_rc_tab(l_n_cntr1).lockbox_interface_id <> l_t_rc_tab(l_n_cntr).lockbox_interface_id)) THEN
1123 l_b_batch_unq := TRUE;
1124 EXIT;
1125 END IF;
1126 END IF;
1127 ELSE
1128 IF ((l_t_rc_tab(l_n_cntr).item_number IS NOT NULL) AND
1129 (l_t_rc_tab(l_n_cntr1).item_number IS NOT NULL)) THEN
1130 IF ((l_t_rc_tab(l_n_cntr1).item_number = l_t_rc_tab(l_n_cntr).item_number) AND
1131 (l_t_rc_tab(l_n_cntr1).lockbox_interface_id <> l_t_rc_tab(l_n_cntr).lockbox_interface_id)) THEN
1132 l_b_batch_unq := TRUE;
1133 EXIT;
1134 END IF;
1135 END IF;
1136 END IF;
1137 END IF;
1138 END LOOP;
1139 END IF;
1140
1141 IF l_b_batch_unq THEN
1142 l_v_val_status := 'E';
1143 fnd_message.set_name('IGS',
1144 'IGS_FI_DUP_ITEM_NUMBER');
1145 fnd_file.put_line(fnd_file.log,
1146 fnd_message.get);
1147 END IF;
1148
1149 -- If any of the combination pairs of the Charge Code(1-8) and Applied Amount(1-8)
1150 -- has the charge code provided but the corresponding applied amount not provided
1151 -- or vice-versa, then this is an error condition and is logged in the log file.
1152 l_b_chg_amt_match := TRUE;
1153 IF (((l_t_rc_tab(l_n_cntr).charge_cd1 IS NOT NULL) AND
1154 (l_t_rc_tab(l_n_cntr).applied_amt1 IS NULL)) OR
1155 ((l_t_rc_tab(l_n_cntr).charge_cd1 IS NULL) AND
1156 (l_t_rc_tab(l_n_cntr).applied_amt1 IS NOT NULL))) THEN
1157 l_b_chg_amt_match := FALSE;
1158 END IF;
1159
1160 IF (((l_t_rc_tab(l_n_cntr).charge_cd2 IS NOT NULL) AND
1161 (l_t_rc_tab(l_n_cntr).applied_amt2 IS NULL)) OR
1162 ((l_t_rc_tab(l_n_cntr).charge_cd2 IS NULL) AND
1163 (l_t_rc_tab(l_n_cntr).applied_amt2 IS NOT NULL))) THEN
1164 l_b_chg_amt_match := FALSE;
1165 END IF;
1166
1167 IF (((l_t_rc_tab(l_n_cntr).charge_cd3 IS NOT NULL) AND
1168 (l_t_rc_tab(l_n_cntr).applied_amt3 IS NULL)) OR
1169 ((l_t_rc_tab(l_n_cntr).charge_cd3 IS NULL) AND
1170 (l_t_rc_tab(l_n_cntr).applied_amt3 IS NOT NULL))) THEN
1171 l_b_chg_amt_match := FALSE;
1172 END IF;
1173
1174 IF (((l_t_rc_tab(l_n_cntr).charge_cd4 IS NOT NULL) AND
1175 (l_t_rc_tab(l_n_cntr).applied_amt4 IS NULL)) OR
1176 ((l_t_rc_tab(l_n_cntr).charge_cd4 IS NULL) AND
1177 (l_t_rc_tab(l_n_cntr).applied_amt4 IS NOT NULL))) THEN
1178 l_b_chg_amt_match := FALSE;
1179 END IF;
1180
1181 IF (((l_t_rc_tab(l_n_cntr).charge_cd5 IS NOT NULL) AND
1182 (l_t_rc_tab(l_n_cntr).applied_amt5 IS NULL)) OR
1183 ((l_t_rc_tab(l_n_cntr).charge_cd5 IS NULL) AND
1184 (l_t_rc_tab(l_n_cntr).applied_amt5 IS NOT NULL))) THEN
1185 l_b_chg_amt_match := FALSE;
1186 END IF;
1187
1188 IF (((l_t_rc_tab(l_n_cntr).charge_cd6 IS NOT NULL) AND
1189 (l_t_rc_tab(l_n_cntr).applied_amt6 IS NULL)) OR
1190 ((l_t_rc_tab(l_n_cntr).charge_cd6 IS NULL) AND
1191 (l_t_rc_tab(l_n_cntr).applied_amt6 IS NOT NULL))) THEN
1192 l_b_chg_amt_match := FALSE;
1193 END IF;
1194
1195 IF (((l_t_rc_tab(l_n_cntr).charge_cd7 IS NOT NULL) AND
1196 (l_t_rc_tab(l_n_cntr).applied_amt7 IS NULL)) OR
1197 ((l_t_rc_tab(l_n_cntr).charge_cd7 IS NULL) AND
1198 (l_t_rc_tab(l_n_cntr).applied_amt7 IS NOT NULL))) THEN
1199 l_b_chg_amt_match := FALSE;
1200 END IF;
1201
1202 IF (((l_t_rc_tab(l_n_cntr).charge_cd8 IS NOT NULL) AND
1203 (l_t_rc_tab(l_n_cntr).applied_amt8 IS NULL)) OR
1204 ((l_t_rc_tab(l_n_cntr).charge_cd8 IS NULL) AND
1205 (l_t_rc_tab(l_n_cntr).applied_amt8 IS NOT NULL))) THEN
1206 l_b_chg_amt_match := FALSE;
1207 END IF;
1208
1209 IF NOT l_b_chg_amt_match THEN
1210 l_v_val_status := 'E';
1211 fnd_message.set_name('IGS',
1212 'IGS_FI_REC_CHG_APPL_MISSING');
1213 fnd_message.set_token('ITEM_NUMBER',
1214 l_t_rc_tab(l_n_cntr).item_number);
1215 fnd_message.set_token('BATCH_NAME',
1216 l_t_rc_tab(l_n_cntr).batch_name);
1217 fnd_message.set_token('LOCKBOX_NAME',
1218 l_t_rc_tab(l_n_cntr).lockbox_name);
1219 fnd_file.put_line(fnd_file.log,
1220 fnd_message.get);
1221 END IF;
1222
1223 -- The following logic checks for the negative amount for the Charge Amount
1224 -- for the receipt record
1225 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt1,0) < 0 THEN
1226 l_v_val_status := 'E';
1227 fnd_message.set_name('IGS',
1228 'IGS_FI_CHG_AMT_NEG');
1229 fnd_message.set_token('ITEM_NUMBER',
1230 l_t_rc_tab(l_n_cntr).item_number);
1231 fnd_message.set_token('BATCH_NAME',
1232 l_t_rc_tab(l_n_cntr).batch_name);
1233 fnd_message.set_token('LOCKBOX_NAME',
1234 l_t_rc_tab(l_n_cntr).lockbox_name);
1235 fnd_message.set_token('CHARGE_CODE',
1236 l_t_rc_tab(l_n_cntr).charge_cd1);
1237 fnd_file.put_line(fnd_file.log,
1238 fnd_message.get);
1239 END IF;
1240
1241 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt2,0) < 0 THEN
1242 l_v_val_status := 'E';
1243 fnd_message.set_name('IGS',
1244 'IGS_FI_CHG_AMT_NEG');
1245 fnd_message.set_token('ITEM_NUMBER',
1246 l_t_rc_tab(l_n_cntr).item_number);
1247 fnd_message.set_token('BATCH_NAME',
1248 l_t_rc_tab(l_n_cntr).batch_name);
1249 fnd_message.set_token('LOCKBOX_NAME',
1250 l_t_rc_tab(l_n_cntr).lockbox_name);
1251 fnd_message.set_token('CHARGE_CODE',
1252 l_t_rc_tab(l_n_cntr).charge_cd2);
1253 fnd_file.put_line(fnd_file.log,
1254 fnd_message.get);
1255 END IF;
1256
1257 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt3,0) < 0 THEN
1258 l_v_val_status := 'E';
1259 fnd_message.set_name('IGS',
1260 'IGS_FI_CHG_AMT_NEG');
1261 fnd_message.set_token('ITEM_NUMBER',
1262 l_t_rc_tab(l_n_cntr).item_number);
1263 fnd_message.set_token('BATCH_NAME',
1264 l_t_rc_tab(l_n_cntr).batch_name);
1265 fnd_message.set_token('LOCKBOX_NAME',
1266 l_t_rc_tab(l_n_cntr).lockbox_name);
1267 fnd_message.set_token('CHARGE_CODE',
1268 l_t_rc_tab(l_n_cntr).charge_cd3);
1269 fnd_file.put_line(fnd_file.log,
1270 fnd_message.get);
1271 END IF;
1272
1273 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt4,0) < 0 THEN
1274 l_v_val_status := 'E';
1275 fnd_message.set_name('IGS',
1276 'IGS_FI_CHG_AMT_NEG');
1277 fnd_message.set_token('ITEM_NUMBER',
1278 l_t_rc_tab(l_n_cntr).item_number);
1279 fnd_message.set_token('BATCH_NAME',
1280 l_t_rc_tab(l_n_cntr).batch_name);
1281 fnd_message.set_token('LOCKBOX_NAME',
1282 l_t_rc_tab(l_n_cntr).lockbox_name);
1283 fnd_message.set_token('CHARGE_CODE',
1284 l_t_rc_tab(l_n_cntr).charge_cd4);
1285 fnd_file.put_line(fnd_file.log,
1286 fnd_message.get);
1287 END IF;
1288
1289 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt5,0) < 0 THEN
1290 l_v_val_status := 'E';
1291 fnd_message.set_name('IGS',
1292 'IGS_FI_CHG_AMT_NEG');
1293 fnd_message.set_token('ITEM_NUMBER',
1294 l_t_rc_tab(l_n_cntr).item_number);
1295 fnd_message.set_token('BATCH_NAME',
1296 l_t_rc_tab(l_n_cntr).batch_name);
1297 fnd_message.set_token('LOCKBOX_NAME',
1298 l_t_rc_tab(l_n_cntr).lockbox_name);
1299 fnd_message.set_token('CHARGE_CODE',
1300 l_t_rc_tab(l_n_cntr).charge_cd5);
1301 fnd_file.put_line(fnd_file.log,
1302 fnd_message.get);
1303 END IF;
1304
1305 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt6,0) < 0 THEN
1306 l_v_val_status := 'E';
1307 fnd_message.set_name('IGS',
1308 'IGS_FI_CHG_AMT_NEG');
1309 fnd_message.set_token('ITEM_NUMBER',
1310 l_t_rc_tab(l_n_cntr).item_number);
1311 fnd_message.set_token('BATCH_NAME',
1312 l_t_rc_tab(l_n_cntr).batch_name);
1313 fnd_message.set_token('LOCKBOX_NAME',
1314 l_t_rc_tab(l_n_cntr).lockbox_name);
1315 fnd_message.set_token('CHARGE_CODE',
1316 l_t_rc_tab(l_n_cntr).charge_cd6);
1317 fnd_file.put_line(fnd_file.log,
1318 fnd_message.get);
1319 END IF;
1320
1321 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt7,0) < 0 THEN
1322 l_v_val_status := 'E';
1323 fnd_message.set_name('IGS',
1324 'IGS_FI_CHG_AMT_NEG');
1325 fnd_message.set_token('ITEM_NUMBER',
1326 l_t_rc_tab(l_n_cntr).item_number);
1327 fnd_message.set_token('BATCH_NAME',
1328 l_t_rc_tab(l_n_cntr).batch_name);
1329 fnd_message.set_token('LOCKBOX_NAME',
1330 l_t_rc_tab(l_n_cntr).lockbox_name);
1331 fnd_message.set_token('CHARGE_CODE',
1332 l_t_rc_tab(l_n_cntr).charge_cd7);
1333 fnd_file.put_line(fnd_file.log,
1334 fnd_message.get);
1335 END IF;
1336
1337 IF NVL(l_t_rc_tab(l_n_cntr).applied_amt8,0) < 0 THEN
1338 l_v_val_status := 'E';
1339 fnd_message.set_name('IGS',
1340 'IGS_FI_CHG_AMT_NEG');
1341 fnd_message.set_token('ITEM_NUMBER',
1342 l_t_rc_tab(l_n_cntr).item_number);
1343 fnd_message.set_token('BATCH_NAME',
1344 l_t_rc_tab(l_n_cntr).batch_name);
1345 fnd_message.set_token('LOCKBOX_NAME',
1346 l_t_rc_tab(l_n_cntr).lockbox_name);
1347 fnd_message.set_token('CHARGE_CODE',
1348 l_t_rc_tab(l_n_cntr).charge_cd8);
1349 fnd_file.put_line(fnd_file.log,
1350 fnd_message.get);
1351 END IF;
1352
1353 -- Sum up the Applied Amount (1-8) of the receipt record
1354 l_n_amt_appl := NVL(l_t_rc_tab(l_n_cntr).applied_amt1,0) +
1355 NVL(l_t_rc_tab(l_n_cntr).applied_amt2,0) +
1356 NVL(l_t_rc_tab(l_n_cntr).applied_amt3,0) +
1357 NVL(l_t_rc_tab(l_n_cntr).applied_amt4,0) +
1358 NVL(l_t_rc_tab(l_n_cntr).applied_amt5,0) +
1359 NVL(l_t_rc_tab(l_n_cntr).applied_amt6,0) +
1360 NVL(l_t_rc_tab(l_n_cntr).applied_amt7,0) +
1361 NVL(l_t_rc_tab(l_n_cntr).applied_amt8,0);
1362
1363 -- In addition to the summed up Applied Amounts from the Charge record,
1364 -- Loop across the receipt overflow records for the receipt record
1365 -- and sum up the applied amount (1-8) for the overflow record
1366 IF l_n_ro_cntr > 0 THEN
1367 l_n_cntr1 := 0;
1368 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
1369 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
1370 l_b_ro_identified := FALSE;
1371 IF l_t_rc_tab(l_n_cntr).batch_name IS NOT NULL THEN
1372 IF ((l_t_ro_tab(l_n_cntr1).batch_name IS NOT NULL) AND
1373 (l_t_ro_tab(l_n_cntr1).batch_name = l_t_rc_tab(l_n_cntr).batch_name) AND
1374 (l_t_ro_tab(l_n_cntr1).item_number IS NOT NULL) AND
1375 (l_t_ro_tab(l_n_cntr1).item_number = l_t_rc_tab(l_n_cntr).item_number)) THEN
1376 l_b_ro_identified := TRUE;
1377 END IF;
1378 ELSE
1379 IF ((l_t_ro_tab(l_n_cntr1).item_number IS NOT NULL) AND
1380 (l_t_ro_tab(l_n_cntr1).batch_name IS NULL) AND
1381 (l_t_ro_tab(l_n_cntr1).item_number = l_t_rc_tab(l_n_cntr).item_number)) THEN
1382 l_b_ro_identified := TRUE;
1383 END IF;
1384 END IF;
1385
1386 IF l_b_ro_identified THEN
1387 l_n_amt_appl := NVL(l_n_amt_appl,0) +
1388 NVL(l_t_ro_tab(l_n_cntr1).applied_amt1,0) +
1389 NVL(l_t_ro_tab(l_n_cntr1).applied_amt2,0) +
1390 NVL(l_t_ro_tab(l_n_cntr1).applied_amt3,0) +
1391 NVL(l_t_ro_tab(l_n_cntr1).applied_amt4,0) +
1392 NVL(l_t_ro_tab(l_n_cntr1).applied_amt5,0) +
1393 NVL(l_t_ro_tab(l_n_cntr1).applied_amt6,0) +
1394 NVL(l_t_ro_tab(l_n_cntr1).applied_amt7,0) +
1395 NVL(l_t_ro_tab(l_n_cntr1).applied_amt8,0);
1396 END IF;
1397 END IF;
1398 END LOOP;
1399 END IF;
1400
1401 -- If the amount to be applied is greater than the receipt amount then log the error in the log file
1402 IF l_n_amt_appl > NVL(l_t_rc_tab(l_n_cntr).receipt_amt,0) THEN
1403 l_v_val_status := 'E';
1404 fnd_message.set_name('IGS',
1405 'IGS_FI_DESG_AMNT_MISMATCH');
1406 fnd_message.set_token('ITEM_NUMBER',
1407 l_t_rc_tab(l_n_cntr).item_number);
1408 fnd_message.set_token('BATCH_NAME',
1409 l_t_rc_tab(l_n_cntr).batch_name);
1410 fnd_message.set_token('LOCKBOX_NAME',
1411 l_t_rc_tab(l_n_cntr).lockbox_name);
1412 fnd_file.put_line(fnd_file.log,
1413 fnd_message.get);
1414 END IF;
1415 END IF;
1416 END LOOP;
1417 END IF;
1418
1419 -- If there are any receipt overflow records, then
1420 IF l_n_ro_cntr > 0 THEN
1421 -- Loop across the receipt overflow record and
1422 FOR l_n_cntr IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
1423 IF l_t_ro_tab.EXISTS(l_n_cntr) THEN
1424
1425 -- If the item number is not null, then log the error message in the log file
1426 IF l_t_ro_tab(l_n_cntr).item_number IS NULL THEN
1427 l_v_val_status := 'E';
1428 fnd_message.set_name('IGS',
1429 'IGS_FI_REC_ITEMS_MISSING');
1430 fnd_file.put_line(fnd_file.log,
1431 fnd_message.get);
1432 END IF;
1433
1434 -- The following logic checks for the negative amount for the Charge Amount
1435 -- for the receipt overflow record
1436 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt1,0) < 0 THEN
1437 l_v_val_status := 'E';
1438 fnd_message.set_name('IGS',
1439 'IGS_FI_CHG_AMT_NEG');
1440 fnd_message.set_token('ITEM_NUMBER',
1441 l_t_ro_tab(l_n_cntr).item_number);
1442 fnd_message.set_token('BATCH_NAME',
1443 l_t_ro_tab(l_n_cntr).batch_name);
1444 fnd_message.set_token('LOCKBOX_NAME',
1445 l_t_ro_tab(l_n_cntr).lockbox_name);
1446 fnd_message.set_token('CHARGE_CODE',
1447 l_t_ro_tab(l_n_cntr).charge_cd1);
1448 fnd_file.put_line(fnd_file.log,
1449 fnd_message.get);
1450 END IF;
1451
1452 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt2,0) < 0 THEN
1453 l_v_val_status := 'E';
1454 fnd_message.set_name('IGS',
1455 'IGS_FI_CHG_AMT_NEG');
1456 fnd_message.set_token('ITEM_NUMBER',
1457 l_t_ro_tab(l_n_cntr).item_number);
1458 fnd_message.set_token('BATCH_NAME',
1459 l_t_ro_tab(l_n_cntr).batch_name);
1460 fnd_message.set_token('LOCKBOX_NAME',
1461 l_t_ro_tab(l_n_cntr).lockbox_name);
1462 fnd_message.set_token('CHARGE_CODE',
1463 l_t_ro_tab(l_n_cntr).charge_cd2);
1464 fnd_file.put_line(fnd_file.log,
1465 fnd_message.get);
1466 END IF;
1467
1468 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt3,0) < 0 THEN
1469 l_v_val_status := 'E';
1470 fnd_message.set_name('IGS',
1471 'IGS_FI_CHG_AMT_NEG');
1472 fnd_message.set_token('ITEM_NUMBER',
1473 l_t_ro_tab(l_n_cntr).item_number);
1474 fnd_message.set_token('BATCH_NAME',
1475 l_t_ro_tab(l_n_cntr).batch_name);
1476 fnd_message.set_token('LOCKBOX_NAME',
1477 l_t_ro_tab(l_n_cntr).lockbox_name);
1478 fnd_message.set_token('CHARGE_CODE',
1479 l_t_ro_tab(l_n_cntr).charge_cd3);
1480 fnd_file.put_line(fnd_file.log,
1481 fnd_message.get);
1482 END IF;
1483
1484 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt4,0) < 0 THEN
1485 l_v_val_status := 'E';
1486 fnd_message.set_name('IGS',
1487 'IGS_FI_CHG_AMT_NEG');
1488 fnd_message.set_token('ITEM_NUMBER',
1489 l_t_ro_tab(l_n_cntr).item_number);
1490 fnd_message.set_token('BATCH_NAME',
1491 l_t_ro_tab(l_n_cntr).batch_name);
1492 fnd_message.set_token('LOCKBOX_NAME',
1493 l_t_ro_tab(l_n_cntr).lockbox_name);
1494 fnd_message.set_token('CHARGE_CODE',
1495 l_t_ro_tab(l_n_cntr).charge_cd4);
1496 fnd_file.put_line(fnd_file.log,
1497 fnd_message.get);
1498 END IF;
1499
1500 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt5,0) < 0 THEN
1501 l_v_val_status := 'E';
1502 fnd_message.set_name('IGS',
1503 'IGS_FI_CHG_AMT_NEG');
1504 fnd_message.set_token('ITEM_NUMBER',
1505 l_t_ro_tab(l_n_cntr).item_number);
1506 fnd_message.set_token('BATCH_NAME',
1507 l_t_ro_tab(l_n_cntr).batch_name);
1508 fnd_message.set_token('LOCKBOX_NAME',
1509 l_t_ro_tab(l_n_cntr).lockbox_name);
1510 fnd_message.set_token('CHARGE_CODE',
1511 l_t_ro_tab(l_n_cntr).charge_cd5);
1512 fnd_file.put_line(fnd_file.log,
1513 fnd_message.get);
1514 END IF;
1515
1516 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt6,0) < 0 THEN
1517 l_v_val_status := 'E';
1518 fnd_message.set_name('IGS',
1519 'IGS_FI_CHG_AMT_NEG');
1520 fnd_message.set_token('ITEM_NUMBER',
1521 l_t_ro_tab(l_n_cntr).item_number);
1522 fnd_message.set_token('BATCH_NAME',
1523 l_t_ro_tab(l_n_cntr).batch_name);
1524 fnd_message.set_token('LOCKBOX_NAME',
1525 l_t_ro_tab(l_n_cntr).lockbox_name);
1526 fnd_message.set_token('CHARGE_CODE',
1527 l_t_ro_tab(l_n_cntr).charge_cd6);
1528 fnd_file.put_line(fnd_file.log,
1529 fnd_message.get);
1530 END IF;
1531
1532 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt7,0) < 0 THEN
1533 l_v_val_status := 'E';
1534 fnd_message.set_name('IGS',
1535 'IGS_FI_CHG_AMT_NEG');
1536 fnd_message.set_token('ITEM_NUMBER',
1537 l_t_ro_tab(l_n_cntr).item_number);
1538 fnd_message.set_token('BATCH_NAME',
1539 l_t_ro_tab(l_n_cntr).batch_name);
1540 fnd_message.set_token('LOCKBOX_NAME',
1541 l_t_ro_tab(l_n_cntr).lockbox_name);
1542 fnd_message.set_token('CHARGE_CODE',
1543 l_t_ro_tab(l_n_cntr).charge_cd7);
1544 fnd_file.put_line(fnd_file.log,
1545 fnd_message.get);
1546 END IF;
1547
1548 IF NVL(l_t_ro_tab(l_n_cntr).applied_amt8,0) < 0 THEN
1549 l_v_val_status := 'E';
1550 fnd_message.set_name('IGS',
1551 'IGS_FI_CHG_AMT_NEG');
1552 fnd_message.set_token('ITEM_NUMBER',
1553 l_t_ro_tab(l_n_cntr).item_number);
1554 fnd_message.set_token('BATCH_NAME',
1555 l_t_ro_tab(l_n_cntr).batch_name);
1556 fnd_message.set_token('LOCKBOX_NAME',
1557 l_t_ro_tab(l_n_cntr).lockbox_name);
1558 fnd_message.set_token('CHARGE_CODE',
1559 l_t_ro_tab(l_n_cntr).charge_cd8);
1560 fnd_file.put_line(fnd_file.log,
1561 fnd_message.get);
1562 END IF;
1563
1564 l_n_cntr1 := 0;
1565 l_b_rc_ro_match := FALSE;
1566
1567 -- If the batch name in the receipt overflow is not null, then check if the receipt overflow record
1568 -- can be associated with a receipt record that has a valid batch name
1569 IF ((l_t_ro_tab(l_n_cntr).batch_name IS NOT NULL) AND
1570 (l_n_rc_cntr > 0)) THEN
1571 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
1572 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
1573 IF (l_t_rc_tab(l_n_cntr1).batch_name IS NOT NULL) THEN
1574 IF ((l_t_rc_tab(l_n_cntr1).batch_name = l_t_ro_tab(l_n_cntr).batch_name) AND
1575 (l_t_rc_tab(l_n_cntr1).item_number = l_t_ro_tab(l_n_cntr).item_number)) THEN
1576 l_b_rc_ro_match := TRUE;
1577 EXIT;
1578 END IF;
1579 END IF;
1580 END IF;
1581 END LOOP;
1582
1583 IF l_b_rc_ro_match THEN
1584 IF l_t_bh_tab.COUNT > 0 THEN
1585 l_b_rc_ro_match := FALSE;
1586 FOR l_n_cntr2 IN l_t_bh_tab.FIRST..l_t_bh_tab.LAST LOOP
1587 IF l_t_bh_tab.EXISTS(l_n_cntr2) THEN
1588 IF l_t_bh_tab(l_n_cntr2).batch_name = l_t_ro_tab(l_n_cntr).batch_name THEN
1589 l_b_rc_ro_match := TRUE;
1590 END IF;
1591 END IF;
1592 END LOOP;
1593 END IF;
1594 END IF;
1595
1596 -- Else if the the batch name is null then validate if the receipt overflow record can be associated
1597 -- with a receipt record with null batch name
1598 ELSIF ((l_t_ro_tab(l_n_cntr).batch_name IS NULL) AND
1599 (l_n_rc_cntr > 0)) THEN
1600 l_b_rc_ro_match := FALSE;
1601 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
1602 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
1603 IF ((l_t_rc_tab(l_n_cntr1).batch_name IS NULL) AND
1604 (l_t_rc_tab(l_n_cntr1).item_number = l_t_ro_tab(l_n_cntr).item_number)) THEN
1605 l_b_rc_ro_match := TRUE;
1606 END IF;
1607 END IF;
1608 END LOOP;
1609
1610 -- Else if there are no receipt records then this is an error message
1611 ELSIF (l_n_rc_cntr = 0) THEN
1612 l_b_rc_ro_match := FALSE;
1613 END IF;
1614
1615 IF NOT l_b_rc_ro_match THEN
1616 l_v_val_status := 'E';
1617 fnd_message.set_name('IGS',
1618 'IGS_FI_NO_OVFL_REC_LINK');
1619 fnd_message.set_token('ITEM_NUMBER',
1620 l_t_ro_tab(l_n_cntr).item_number);
1621 fnd_message.set_token('BATCH_NAME',
1622 l_t_ro_tab(l_n_cntr).batch_name);
1623 fnd_message.set_token('LOCKBOX_NAME',
1624 l_t_ro_tab(l_n_cntr).lockbox_name);
1625 fnd_file.put_line(fnd_file.log,
1626 fnd_message.get);
1627 END IF;
1628
1629 l_b_ro_chg_appl := TRUE;
1630
1631 -- If any of the combination pairs of the Charge Code(1-8) and Applied Amount(1-8)
1632 -- has the charge code provided but the corresponding applied amount not provided
1633 -- or vice-versa, then this is an error condition and is logged in the log file.
1634
1635 IF (((l_t_ro_tab(l_n_cntr).charge_cd1 IS NOT NULL) AND
1636 (l_t_ro_tab(l_n_cntr).applied_amt1 IS NULL))OR
1637 ((l_t_ro_tab(l_n_cntr).charge_cd1 IS NULL) AND
1638 (l_t_ro_tab(l_n_cntr).applied_amt1 IS NOT NULL))) THEN
1639 l_b_ro_chg_appl := FALSE;
1640 END IF;
1641
1642 IF (((l_t_ro_tab(l_n_cntr).charge_cd2 IS NOT NULL) AND
1643 (l_t_ro_tab(l_n_cntr).applied_amt2 IS NULL))OR
1644 ((l_t_ro_tab(l_n_cntr).charge_cd2 IS NULL) AND
1645 (l_t_ro_tab(l_n_cntr).applied_amt2 IS NOT NULL))) THEN
1646 l_b_ro_chg_appl := FALSE;
1647 END IF;
1648
1649 IF (((l_t_ro_tab(l_n_cntr).charge_cd3 IS NOT NULL) AND
1650 (l_t_ro_tab(l_n_cntr).applied_amt3 IS NULL))OR
1651 ((l_t_ro_tab(l_n_cntr).charge_cd3 IS NULL) AND
1652 (l_t_ro_tab(l_n_cntr).applied_amt3 IS NOT NULL))) THEN
1653 l_b_ro_chg_appl := FALSE;
1654 END IF;
1655
1656 IF (((l_t_ro_tab(l_n_cntr).charge_cd4 IS NOT NULL) AND
1657 (l_t_ro_tab(l_n_cntr).applied_amt4 IS NULL))OR
1658 ((l_t_ro_tab(l_n_cntr).charge_cd4 IS NULL) AND
1659 (l_t_ro_tab(l_n_cntr).applied_amt4 IS NOT NULL))) THEN
1660 l_b_ro_chg_appl := FALSE;
1661 END IF;
1662
1663 IF (((l_t_ro_tab(l_n_cntr).charge_cd5 IS NOT NULL) AND
1664 (l_t_ro_tab(l_n_cntr).applied_amt5 IS NULL))OR
1665 ((l_t_ro_tab(l_n_cntr).charge_cd5 IS NULL) AND
1666 (l_t_ro_tab(l_n_cntr).applied_amt5 IS NOT NULL))) THEN
1667 l_b_ro_chg_appl := FALSE;
1668 END IF;
1669
1670 IF (((l_t_ro_tab(l_n_cntr).charge_cd6 IS NOT NULL) AND
1671 (l_t_ro_tab(l_n_cntr).applied_amt6 IS NULL))OR
1672 ((l_t_ro_tab(l_n_cntr).charge_cd6 IS NULL) AND
1673 (l_t_ro_tab(l_n_cntr).applied_amt6 IS NOT NULL))) THEN
1674 l_b_ro_chg_appl := FALSE;
1675 END IF;
1676
1677 IF (((l_t_ro_tab(l_n_cntr).charge_cd7 IS NOT NULL) AND
1678 (l_t_ro_tab(l_n_cntr).applied_amt7 IS NULL))OR
1679 ((l_t_ro_tab(l_n_cntr).charge_cd7 IS NULL) AND
1680 (l_t_ro_tab(l_n_cntr).applied_amt7 IS NOT NULL))) THEN
1681 l_b_ro_chg_appl := FALSE;
1682 END IF;
1683
1684 IF (((l_t_ro_tab(l_n_cntr).charge_cd8 IS NOT NULL) AND
1685 (l_t_ro_tab(l_n_cntr).applied_amt8 IS NULL))OR
1686 ((l_t_ro_tab(l_n_cntr).charge_cd8 IS NULL) AND
1687 (l_t_ro_tab(l_n_cntr).applied_amt8 IS NOT NULL))) THEN
1688 l_b_ro_chg_appl := FALSE;
1689 END IF;
1690
1691 IF NOT l_b_ro_chg_appl THEN
1692 l_v_val_status := 'E';
1693 fnd_message.set_name('IGS',
1694 'IGS_FI_OVFL_REC_NO_CHG_APPL');
1695 fnd_message.set_token('ITEM_NUMBER',
1696 l_t_ro_tab(l_n_cntr).item_number);
1697 fnd_message.set_token('BATCH_NAME',
1698 l_t_ro_tab(l_n_cntr).batch_name);
1699 fnd_message.set_token('LOCKBOX_NAME',
1700 l_t_ro_tab(l_n_cntr).lockbox_name);
1701 fnd_file.put_line(fnd_file.log,
1702 fnd_message.get);
1703 END IF;
1704
1705 -- If none of the charge code(1-8) and applied amount(1-8) is provided
1706 -- then this is an error .
1707 IF ((l_t_ro_tab(l_n_cntr).charge_cd1 IS NULL) AND
1708 (l_t_ro_tab(l_n_cntr).applied_amt1 IS NULL)) AND
1709 ((l_t_ro_tab(l_n_cntr).charge_cd2 IS NULL) AND
1710 (l_t_ro_tab(l_n_cntr).applied_amt2 IS NULL)) AND
1711 ((l_t_ro_tab(l_n_cntr).charge_cd3 IS NULL) AND
1712 (l_t_ro_tab(l_n_cntr).applied_amt3 IS NULL)) AND
1713 ((l_t_ro_tab(l_n_cntr).charge_cd4 IS NULL) AND
1714 (l_t_ro_tab(l_n_cntr).applied_amt4 IS NULL)) AND
1715 ((l_t_ro_tab(l_n_cntr).charge_cd5 IS NULL) AND
1716 (l_t_ro_tab(l_n_cntr).applied_amt5 IS NULL)) AND
1717 ((l_t_ro_tab(l_n_cntr).charge_cd6 IS NULL) AND
1718 (l_t_ro_tab(l_n_cntr).applied_amt6 IS NULL)) AND
1719 ((l_t_ro_tab(l_n_cntr).charge_cd7 IS NULL) AND
1720 (l_t_ro_tab(l_n_cntr).applied_amt7 IS NULL)) AND
1721 ((l_t_ro_tab(l_n_cntr).charge_cd8 IS NULL) AND
1722 (l_t_ro_tab(l_n_cntr).applied_amt8 IS NULL)) THEN
1723 l_v_val_status := 'E';
1724 fnd_message.set_name('IGS',
1725 'IGS_FI_OVFL_REC_NO_CHG_APPL');
1726 fnd_message.set_token('ITEM_NUMBER',
1727 l_t_ro_tab(l_n_cntr).item_number);
1728 fnd_message.set_token('BATCH_NAME',
1729 l_t_ro_tab(l_n_cntr).batch_name);
1730 fnd_message.set_token('LOCKBOX_NAME',
1731 l_t_ro_tab(l_n_cntr).lockbox_name);
1732 fnd_file.put_line(fnd_file.log,
1733 fnd_message.get);
1734 END IF;
1735 END IF;
1736 END LOOP;
1737 END IF;
1738 fnd_file.new_line(fnd_file.log);
1739
1740 fnd_file.put_line(fnd_file.log,
1741 g_v_line_sep);
1742 return l_v_val_status;
1743 END validate_type1;
1744
1745 PROCEDURE update_lbint_status(p_v_status igs_fi_lockbox_ints.record_status%TYPE) AS
1746 /******************************************************************
1747 Created By : Amit Gairola
1748 Date Created By : 12-Jun-2003
1749 Purpose : Procedure for updating the interface table record status.
1750
1751 Known limitations,enhancements,remarks:
1752 Change History
1753 Who When What
1754 ***************************************************************** */
1755 l_n_rec_cntr NUMBER(38);
1756 BEGIN
1757 IF g_lb_int_tab.COUNT > 0 THEN
1758 FOR l_n_rec_cntr IN g_lb_int_tab.FIRST..g_lb_int_tab.LAST LOOP
1759 IF g_lb_int_tab.EXISTS(l_n_rec_cntr) THEN
1760 igs_fi_lockbox_ints_pkg.update_row(x_rowid => g_lb_int_tab(l_n_rec_cntr).row_id,
1761 x_lockbox_interface_id => g_lb_int_tab(l_n_rec_cntr).lockbox_interface_id,
1762 x_record_identifier_cd => g_lb_int_tab(l_n_rec_cntr).record_identifier_cd,
1763 x_record_status => p_v_status,
1764 x_deposit_date => g_lb_int_tab(l_n_rec_cntr).deposit_date,
1765 x_transmission_record_count => g_lb_int_tab(l_n_rec_cntr).transmission_record_count,
1766 x_transmission_amt => g_lb_int_tab(l_n_rec_cntr).transmission_amt,
1767 x_lockbox_name => g_lb_int_tab(l_n_rec_cntr).lockbox_name,
1768 x_lockbox_batch_count => g_lb_int_tab(l_n_rec_cntr).lockbox_batch_count,
1769 x_lockbox_record_count => g_lb_int_tab(l_n_rec_cntr).lockbox_record_count,
1770 x_lockbox_amt => g_lb_int_tab(l_n_rec_cntr).lockbox_amt,
1771 x_batch_name => g_lb_int_tab(l_n_rec_cntr).batch_name,
1772 x_batch_amt => g_lb_int_tab(l_n_rec_cntr).batch_amt,
1773 x_batch_record_count => g_lb_int_tab(l_n_rec_cntr).batch_record_count,
1774 x_item_number => g_lb_int_tab(l_n_rec_cntr).item_number,
1775 x_receipt_amt => g_lb_int_tab(l_n_rec_cntr).receipt_amt,
1776 x_check_cd => g_lb_int_tab(l_n_rec_cntr).check_cd,
1777 x_party_number => g_lb_int_tab(l_n_rec_cntr).party_number,
1778 x_payer_name => g_lb_int_tab(l_n_rec_cntr).payer_name,
1779 x_charge_cd1 => g_lb_int_tab(l_n_rec_cntr).charge_cd1,
1780 x_charge_cd2 => g_lb_int_tab(l_n_rec_cntr).charge_cd2,
1781 x_charge_cd3 => g_lb_int_tab(l_n_rec_cntr).charge_cd3,
1782 x_charge_cd4 => g_lb_int_tab(l_n_rec_cntr).charge_cd4,
1783 x_charge_cd5 => g_lb_int_tab(l_n_rec_cntr).charge_cd5,
1784 x_charge_cd6 => g_lb_int_tab(l_n_rec_cntr).charge_cd6,
1785 x_charge_cd7 => g_lb_int_tab(l_n_rec_cntr).charge_cd7,
1786 x_charge_cd8 => g_lb_int_tab(l_n_rec_cntr).charge_cd8,
1787 x_applied_amt1 => g_lb_int_tab(l_n_rec_cntr).applied_amt1,
1788 x_applied_amt2 => g_lb_int_tab(l_n_rec_cntr).applied_amt2,
1789 x_applied_amt3 => g_lb_int_tab(l_n_rec_cntr).applied_amt3,
1790 x_applied_amt4 => g_lb_int_tab(l_n_rec_cntr).applied_amt4,
1791 x_applied_amt5 => g_lb_int_tab(l_n_rec_cntr).applied_amt5,
1792 x_applied_amt6 => g_lb_int_tab(l_n_rec_cntr).applied_amt6,
1793 x_applied_amt7 => g_lb_int_tab(l_n_rec_cntr).applied_amt7,
1794 x_applied_amt8 => g_lb_int_tab(l_n_rec_cntr).applied_amt8,
1795 x_credit_type_cd => g_lb_int_tab(l_n_rec_cntr).credit_type_cd,
1796 x_fee_cal_instance_cd => g_lb_int_tab(l_n_rec_cntr).fee_cal_instance_cd,
1797 x_adm_application_id => g_lb_int_tab(l_n_rec_cntr).adm_application_id,
1798 x_attribute_category => g_lb_int_tab(l_n_rec_cntr).attribute_category,
1799 x_attribute1 => g_lb_int_tab(l_n_rec_cntr).attribute1,
1800 x_attribute2 => g_lb_int_tab(l_n_rec_cntr).attribute2,
1801 x_attribute3 => g_lb_int_tab(l_n_rec_cntr).attribute3,
1802 x_attribute4 => g_lb_int_tab(l_n_rec_cntr).attribute4,
1803 x_attribute5 => g_lb_int_tab(l_n_rec_cntr).attribute5,
1804 x_attribute6 => g_lb_int_tab(l_n_rec_cntr).attribute6,
1805 x_attribute7 => g_lb_int_tab(l_n_rec_cntr).attribute7,
1806 x_attribute8 => g_lb_int_tab(l_n_rec_cntr).attribute8,
1807 x_attribute9 => g_lb_int_tab(l_n_rec_cntr).attribute9,
1808 x_attribute10 => g_lb_int_tab(l_n_rec_cntr).attribute10,
1809 x_attribute11 => g_lb_int_tab(l_n_rec_cntr).attribute11,
1810 x_attribute12 => g_lb_int_tab(l_n_rec_cntr).attribute12,
1811 x_attribute13 => g_lb_int_tab(l_n_rec_cntr).attribute13,
1812 x_attribute14 => g_lb_int_tab(l_n_rec_cntr).attribute14,
1813 x_attribute15 => g_lb_int_tab(l_n_rec_cntr).attribute15,
1814 x_attribute16 => g_lb_int_tab(l_n_rec_cntr).attribute16,
1815 x_attribute17 => g_lb_int_tab(l_n_rec_cntr).attribute17,
1816 x_attribute18 => g_lb_int_tab(l_n_rec_cntr).attribute18,
1817 x_attribute19 => g_lb_int_tab(l_n_rec_cntr).attribute19,
1818 x_attribute20 => g_lb_int_tab(l_n_rec_cntr).attribute20);
1819 END IF;
1820 END LOOP;
1821 END IF;
1822 END update_lbint_status;
1823
1824 PROCEDURE populate_lb_receipts AS
1825 /******************************************************************
1826 Created By : Amit Gairola
1827 Date Created By : 12-Jun-2003
1828 Purpose : Procedure for populating the receipt and overflow
1829 records
1830
1831 Known limitations,enhancements,remarks:
1832 Change History
1833 Who When What
1834 ***************************************************************** */
1835 l_n_cntr NUMBER(38);
1836 l_n_rec_cntr NUMBER(38);
1837 BEGIN
1838 l_n_rec_cntr := 0;
1839 IF g_lb_int_tab.COUNT > 0 THEN
1840 FOR l_n_cntr IN g_lb_int_tab.FIRST..g_lb_int_tab.LAST LOOP
1841 IF g_lb_int_tab.EXISTS(l_n_cntr) THEN
1842 IF g_lb_int_tab(l_n_cntr).system_record_identifier IN (g_v_receipt,
1843 g_v_receipt_oflow) THEN
1844 l_n_rec_cntr := l_n_rec_cntr + 1;
1845 g_t_rec_tab(l_n_rec_cntr).row_id := g_lb_int_tab(l_n_cntr).row_id;
1846 g_t_rec_tab(l_n_rec_cntr).system_record_identifier := g_lb_int_tab(l_n_cntr).system_record_identifier;
1847 g_t_rec_tab(l_n_rec_cntr).lockbox_interface_id := g_lb_int_tab(l_n_cntr).lockbox_interface_id;
1848 g_t_rec_tab(l_n_rec_cntr).deposit_date := g_lb_int_tab(l_n_cntr).deposit_date;
1849 g_t_rec_tab(l_n_rec_cntr).lockbox_name := g_lb_int_tab(l_n_cntr).lockbox_name;
1850 g_t_rec_tab(l_n_rec_cntr).batch_name := g_lb_int_tab(l_n_cntr).batch_name;
1851 g_t_rec_tab(l_n_rec_cntr).item_number := g_lb_int_tab(l_n_cntr).item_number;
1852 g_t_rec_tab(l_n_rec_cntr).receipt_amt := g_lb_int_tab(l_n_cntr).receipt_amt;
1853 g_t_rec_tab(l_n_rec_cntr).check_cd := g_lb_int_tab(l_n_cntr).check_cd;
1854 g_t_rec_tab(l_n_rec_cntr).party_number := g_lb_int_tab(l_n_cntr).party_number;
1855 g_t_rec_tab(l_n_rec_cntr).mapped_party_id := null;
1856 g_t_rec_tab(l_n_rec_cntr).payer_name := g_lb_int_tab(l_n_cntr).payer_name;
1857 g_t_rec_tab(l_n_rec_cntr).credit_type_cd := g_lb_int_tab(l_n_cntr).credit_type_cd;
1858 g_t_rec_tab(l_n_rec_cntr).mapped_credit_type_id := null;
1859 g_t_rec_tab(l_n_rec_cntr).fee_cal_instance_cd := g_lb_int_tab(l_n_cntr).fee_cal_instance_cd;
1860 g_t_rec_tab(l_n_rec_cntr).mapped_fee_cal_type := null;
1861 g_t_rec_tab(l_n_rec_cntr).mapped_fee_ci_sequence_number := null;
1862 g_t_rec_tab(l_n_rec_cntr).charge_cd1 := g_lb_int_tab(l_n_cntr).charge_cd1;
1863 g_t_rec_tab(l_n_rec_cntr).charge_cd2 := g_lb_int_tab(l_n_cntr).charge_cd2;
1864 g_t_rec_tab(l_n_rec_cntr).charge_cd3 := g_lb_int_tab(l_n_cntr).charge_cd3;
1865 g_t_rec_tab(l_n_rec_cntr).charge_cd4 := g_lb_int_tab(l_n_cntr).charge_cd4;
1866 g_t_rec_tab(l_n_rec_cntr).charge_cd5 := g_lb_int_tab(l_n_cntr).charge_cd5;
1867 g_t_rec_tab(l_n_rec_cntr).charge_cd6 := g_lb_int_tab(l_n_cntr).charge_cd6;
1868 g_t_rec_tab(l_n_rec_cntr).charge_cd7 := g_lb_int_tab(l_n_cntr).charge_cd7;
1869 g_t_rec_tab(l_n_rec_cntr).charge_cd8 := g_lb_int_tab(l_n_cntr).charge_cd8;
1870 g_t_rec_tab(l_n_rec_cntr).applied_amt1 := g_lb_int_tab(l_n_cntr).applied_amt1;
1871 g_t_rec_tab(l_n_rec_cntr).applied_amt2 := g_lb_int_tab(l_n_cntr).applied_amt2;
1872 g_t_rec_tab(l_n_rec_cntr).applied_amt3 := g_lb_int_tab(l_n_cntr).applied_amt3;
1873 g_t_rec_tab(l_n_rec_cntr).applied_amt4 := g_lb_int_tab(l_n_cntr).applied_amt4;
1874 g_t_rec_tab(l_n_rec_cntr).applied_amt5 := g_lb_int_tab(l_n_cntr).applied_amt5;
1875 g_t_rec_tab(l_n_rec_cntr).applied_amt6 := g_lb_int_tab(l_n_cntr).applied_amt6;
1876 g_t_rec_tab(l_n_rec_cntr).applied_amt7 := g_lb_int_tab(l_n_cntr).applied_amt7;
1877 g_t_rec_tab(l_n_rec_cntr).applied_amt8 := g_lb_int_tab(l_n_cntr).applied_amt8;
1878 g_t_rec_tab(l_n_rec_cntr).adm_application_id := g_lb_int_tab(l_n_cntr).adm_application_id;
1879 g_t_rec_tab(l_n_rec_cntr).attribute_category := g_lb_int_tab(l_n_cntr).attribute_category;
1880 g_t_rec_tab(l_n_rec_cntr).attribute1 := g_lb_int_tab(l_n_cntr).attribute1;
1881 g_t_rec_tab(l_n_rec_cntr).attribute2 := g_lb_int_tab(l_n_cntr).attribute2;
1882 g_t_rec_tab(l_n_rec_cntr).attribute3 := g_lb_int_tab(l_n_cntr).attribute3;
1883 g_t_rec_tab(l_n_rec_cntr).attribute4 := g_lb_int_tab(l_n_cntr).attribute4;
1884 g_t_rec_tab(l_n_rec_cntr).attribute5 := g_lb_int_tab(l_n_cntr).attribute5;
1885 g_t_rec_tab(l_n_rec_cntr).attribute6 := g_lb_int_tab(l_n_cntr).attribute6;
1886 g_t_rec_tab(l_n_rec_cntr).attribute7 := g_lb_int_tab(l_n_cntr).attribute7;
1887 g_t_rec_tab(l_n_rec_cntr).attribute8 := g_lb_int_tab(l_n_cntr).attribute8;
1888 g_t_rec_tab(l_n_rec_cntr).attribute9 := g_lb_int_tab(l_n_cntr).attribute9;
1889 g_t_rec_tab(l_n_rec_cntr).attribute10 := g_lb_int_tab(l_n_cntr).attribute10;
1890 g_t_rec_tab(l_n_rec_cntr).attribute11 := g_lb_int_tab(l_n_cntr).attribute11;
1891 g_t_rec_tab(l_n_rec_cntr).attribute12 := g_lb_int_tab(l_n_cntr).attribute12;
1892 g_t_rec_tab(l_n_rec_cntr).attribute13 := g_lb_int_tab(l_n_cntr).attribute13;
1893 g_t_rec_tab(l_n_rec_cntr).attribute14 := g_lb_int_tab(l_n_cntr).attribute14;
1894 g_t_rec_tab(l_n_rec_cntr).attribute15 := g_lb_int_tab(l_n_cntr).attribute15;
1895 g_t_rec_tab(l_n_rec_cntr).attribute16 := g_lb_int_tab(l_n_cntr).attribute16;
1896 g_t_rec_tab(l_n_rec_cntr).attribute17 := g_lb_int_tab(l_n_cntr).attribute17;
1897 g_t_rec_tab(l_n_rec_cntr).attribute18 := g_lb_int_tab(l_n_cntr).attribute18;
1898 g_t_rec_tab(l_n_rec_cntr).attribute19 := g_lb_int_tab(l_n_cntr).attribute19;
1899 g_t_rec_tab(l_n_rec_cntr).attribute20 := g_lb_int_tab(l_n_cntr).attribute20;
1900 g_t_rec_tab(l_n_rec_cntr).receipt_number := null;
1901 g_t_rec_tab(l_n_rec_cntr).record_status := g_v_todo;
1902 g_t_rec_tab(l_n_rec_cntr).eligible_to_apply_yn := 'N';
1903 END IF;
1904 END IF;
1905 END LOOP;
1906 END IF;
1907 END populate_lb_receipts;
1908
1909 PROCEDURE val_charge_number_for_app(p_v_charge_code igs_fi_inv_int.invoice_number%TYPE,
1910 p_v_party_number hz_parties.party_number%TYPE,
1911 p_n_party_id pls_integer,
1912 p_v_fee_type OUT NOCOPY igs_fi_fee_type.fee_type%TYPE,
1913 p_n_invoice_id OUT NOCOPY igs_fi_inv_int.invoice_id%TYPE,
1914 p_v_message_name OUT NOCOPY VARCHAR2) AS
1915 /******************************************************************
1916 Created By : Amit Gairola
1917 Date Created By : 12-Jun-2003
1918 Purpose : Procedure for validating the charge number and party combination
1919
1920 Known limitations,enhancements,remarks:
1921 Change History
1922 Who When What
1923 ***************************************************************** */
1924 CURSOR cur_inv(cp_invoice_number igs_fi_inv_int.invoice_number%TYPE,
1925 cp_party_id igs_fi_inv_int.person_id%TYPE) IS
1926 SELECT invoice_id,
1927 fee_type
1928 FROM igs_fi_inv_int
1929 WHERE person_id = cp_party_id
1930 AND invoice_number = cp_invoice_number;
1931
1932 CURSOR cur_ft(cp_fee_type igs_fi_fee_type.fee_type%TYPE) IS
1933 SELECT designated_payment_flag
1934 FROM igs_fi_fee_type
1935 WHERE fee_type = cp_fee_type;
1936
1937 l_v_fee_type igs_fi_fee_type.fee_type%TYPE;
1938 l_n_inv_id igs_fi_inv_int.invoice_id%TYPE;
1939 l_v_designated_pay_flag igs_fi_fee_type.designated_payment_flag%TYPE;
1940 BEGIN
1941
1942 -- Validate if the Party Id and the charge code exist in the Charges table.
1943 -- If not, then it is an error condition
1944 OPEN cur_inv(p_v_charge_code,
1945 p_n_party_id);
1946 FETCH cur_inv INTO l_n_inv_id,
1947 l_v_fee_type;
1948 IF cur_inv%NOTFOUND THEN
1949 CLOSE cur_inv;
1950 p_n_invoice_id := null;
1951 p_v_fee_type := null;
1952 fnd_message.set_name('IGS',
1953 'IGS_FI_INV_CHG_CODE');
1954 fnd_message.set_token('CHARGE_NUMBER',
1955 p_v_charge_code);
1956 fnd_message.set_token('PARTY_NUMBER',
1957 p_v_party_number);
1958 p_v_message_name := fnd_message.get;
1959 RETURN;
1960 END IF;
1961 CLOSE cur_inv;
1962
1963 -- Validate if the Fee Type has the designated payment flag checked
1964 OPEN cur_ft(l_v_fee_type);
1965 FETCH cur_ft INTO l_v_designated_pay_flag;
1966 CLOSE cur_ft;
1967
1968 IF NVL(l_v_designated_pay_flag,'N') = 'N' THEN
1969 fnd_message.set_name('IGS',
1970 'IGS_FI_CHG_FT_NOT_DESG');
1971 fnd_message.set_token('CHARGE_NUMBER',
1972 p_v_charge_code);
1973 fnd_message.set_token('FEE_TYPE',
1974 l_v_fee_type);
1975 p_v_message_name := fnd_message.get;
1976 END IF;
1977
1978 p_n_invoice_id := l_n_inv_id;
1979 p_v_fee_type := l_v_fee_type;
1980 END val_charge_number_for_app;
1981
1982 PROCEDURE invoke_target_appl(p_n_credit_id PLS_INTEGER,
1983 p_v_charge_code igs_fi_inv_int.invoice_number%TYPE,
1984 p_n_target_invoice_id PLS_INTEGER,
1985 p_n_amount_applied NUMBER,
1986 p_d_gl_date DATE,
1987 p_n_act_amnt_applied OUT NOCOPY NUMBER,
1988 p_n_application_id OUT NOCOPY PLS_INTEGER,
1989 p_v_err_message OUT NOCOPY VARCHAR2) AS
1990 /******************************************************************
1991 Created By : Amit Gairola
1992 Date Created By : 12-Jun-2003
1993 Purpose : Procedure for invoking the applications API
1994
1995 Known limitations,enhancements,remarks:
1996 Change History
1997 Who When What
1998 ***************************************************************** */
1999 CURSOR cur_inv(cp_invoice_id igs_fi_inv_int.invoice_id%TYPE) IS
2000 SELECT invoice_amount_due
2001 FROM igs_fi_inv_int
2002 WHERE invoice_id = cp_invoice_id;
2003
2004
2005 l_n_inv_amt_due igs_fi_inv_int.invoice_amount_due%TYPE;
2006 l_n_app_id igs_fi_applications.application_id%TYPE;
2007 l_n_act_apply_amount igs_fi_applications.amount_applied%TYPE;
2008 l_n_dr_gl_ccid igs_fi_applications.dr_gl_code_ccid%TYPE;
2009 l_n_cr_gl_ccid igs_fi_applications.cr_gl_code_ccid%TYPE;
2010 l_v_dr_acc_cd igs_fi_applications.dr_account_cd%TYPE;
2011 l_v_cr_acc_cd igs_fi_applications.cr_account_cd%TYPE;
2012 l_n_unapp_amount igs_fi_credits.unapplied_amount%TYPE;
2013 l_n_inv_amount_due igs_fi_inv_int.invoice_amount_due%TYPE;
2014 l_v_err_msg VARCHAR2(2000);
2015 l_b_status BOOLEAN := FALSE;
2016 BEGIN
2017
2018 -- Fetch the Invoice Amount Due from the Charges table
2019 OPEN cur_inv(p_n_target_invoice_id);
2020 FETCH cur_inv INTO l_n_inv_amt_due;
2021 CLOSE cur_inv;
2022
2023 -- If the Invoice Amount Due is less than or equal to 0, then log this in the log file
2024 IF l_n_inv_amt_due <= 0 THEN
2025 p_n_act_amnt_applied := null;
2026 p_n_application_id := null;
2027 fnd_message.set_name('IGS',
2028 'IGS_FI_INV_AMT_DUE_NIL');
2029 fnd_message.set_token('CHARGE_NUMBER',
2030 p_v_charge_code);
2031 p_v_err_message := fnd_message.get;
2032 RETURN;
2033 ELSE
2034
2035 -- Else identify if the amount applied is greater than the Invoice Amount Due.
2036 -- If it is, then appply only the Invoice Amount Due
2037 IF p_n_amount_applied > l_n_inv_amt_due THEN
2038 l_n_act_apply_amount := l_n_inv_amt_due;
2039 ELSE
2040 l_n_act_apply_amount := p_n_amount_applied;
2041 END IF;
2042 END IF;
2043
2044 -- Call the API for application creation
2045 l_n_app_id := null;
2046 l_n_unapp_amount := 0;
2047 igs_fi_gen_007.create_application(p_application_id => l_n_app_id,
2048 p_credit_id => p_n_credit_id,
2049 p_invoice_id => p_n_target_invoice_id,
2050 p_amount_apply => l_n_act_apply_amount,
2051 p_appl_type => g_v_app,
2052 p_appl_hierarchy_id => null,
2053 p_validation => 'N',
2054 p_dr_gl_ccid => l_n_dr_gl_ccid,
2055 p_cr_gl_ccid => l_n_cr_gl_ccid,
2056 p_dr_account_cd => l_v_dr_acc_cd,
2057 p_cr_account_cd => l_v_cr_acc_cd,
2058 p_unapp_amount => l_n_unapp_amount,
2059 p_inv_amt_due => l_n_inv_amount_due,
2060 p_err_msg => l_v_err_msg,
2061 p_status => l_b_status,
2062 p_d_gl_date => p_d_gl_date);
2063 IF l_b_status THEN
2064 p_n_act_amnt_applied := l_n_act_apply_amount;
2065 p_n_application_id := l_n_app_id;
2066 p_v_err_message := null;
2067 ELSE
2068 p_n_act_amnt_applied := null;
2069 p_n_application_id := null;
2070 p_v_err_message := fnd_message.get_string('IGS',
2071 l_v_err_msg);
2072 END IF;
2073 END invoke_target_appl;
2074
2075 PROCEDURE insert_lb_errors(p_r_receipt_rec lb_receipt_rec,
2076 p_n_receipt_error_id OUT NOCOPY PLS_INTEGER) AS
2077 /******************************************************************
2078 Created By : Amit Gairola
2079 Date Created By : 12-Jun-2003
2080 Purpose : Procedure for inserting data in igs_fi_lb_rect_errs
2081 table
2082
2083 Known limitations,enhancements,remarks:
2084 Change History
2085 Who When What
2086 svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
2087 in the igs_fi_lb_rect_errs_pkg.insert_row call
2088 ***************************************************************** */
2089 l_v_rowid VARCHAR2(25);
2090 l_n_lockbox_receipt_error_id igs_fi_lb_rect_errs.lockbox_receipt_error_id%TYPE;
2091 BEGIN
2092
2093 -- Create a record in the IGS_FI_LB_RECT_ERRS table
2094 l_v_rowid := null;
2095 l_n_lockbox_receipt_error_id := null;
2096
2097 -- Bug 5217319 Added call to format amount by rounding off to currency precision
2098 igs_fi_lb_rect_errs_pkg.insert_row(x_rowid => l_v_rowid,
2099 x_lockbox_receipt_error_id => l_n_lockbox_receipt_error_id,
2100 x_lockbox_interface_id => p_r_receipt_rec.lockbox_interface_id,
2101 x_item_number => p_r_receipt_rec.item_number,
2102 x_lockbox_name => p_r_receipt_rec.lockbox_name,
2103 x_receipt_amt => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.receipt_amt),
2104 x_batch_name => p_r_receipt_rec.batch_name,
2105 x_party_number => p_r_receipt_rec.party_number,
2106 x_payer_name => p_r_receipt_rec.payer_name,
2107 x_check_cd => p_r_receipt_rec.check_cd,
2108 x_deposit_date => p_r_receipt_rec.deposit_date,
2109 x_credit_type_cd => p_r_receipt_rec.credit_type_cd,
2110 x_fee_cal_instance_cd => p_r_receipt_rec.fee_cal_instance_cd,
2111 x_charge_cd1 => p_r_receipt_rec.charge_cd1,
2112 x_charge_cd2 => p_r_receipt_rec.charge_cd2,
2113 x_charge_cd3 => p_r_receipt_rec.charge_cd3,
2114 x_charge_cd4 => p_r_receipt_rec.charge_cd4,
2115 x_charge_cd5 => p_r_receipt_rec.charge_cd5,
2116 x_charge_cd6 => p_r_receipt_rec.charge_cd6,
2117 x_charge_cd7 => p_r_receipt_rec.charge_cd7,
2118 x_charge_cd8 => p_r_receipt_rec.charge_cd8,
2119 x_applied_amt1 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt1),
2120 x_applied_amt2 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt2),
2121 x_applied_amt3 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt3),
2122 x_applied_amt4 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt4),
2123 x_applied_amt5 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt5),
2124 x_applied_amt6 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt6),
2125 x_applied_amt7 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt7),
2126 x_applied_amt8 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt8),
2127 x_adm_application_id => p_r_receipt_rec.adm_application_id,
2128 x_attribute_category => p_r_receipt_rec.attribute_category,
2129 x_attribute1 => p_r_receipt_rec.attribute1,
2130 x_attribute2 => p_r_receipt_rec.attribute2,
2131 x_attribute3 => p_r_receipt_rec.attribute3,
2132 x_attribute4 => p_r_receipt_rec.attribute4,
2133 x_attribute5 => p_r_receipt_rec.attribute5,
2134 x_attribute6 => p_r_receipt_rec.attribute6,
2135 x_attribute7 => p_r_receipt_rec.attribute7,
2136 x_attribute8 => p_r_receipt_rec.attribute8,
2137 x_attribute9 => p_r_receipt_rec.attribute9,
2138 x_attribute10 => p_r_receipt_rec.attribute10,
2139 x_attribute11 => p_r_receipt_rec.attribute11,
2140 x_attribute12 => p_r_receipt_rec.attribute12,
2141 x_attribute13 => p_r_receipt_rec.attribute13,
2142 x_attribute14 => p_r_receipt_rec.attribute14,
2143 x_attribute15 => p_r_receipt_rec.attribute15,
2144 x_attribute16 => p_r_receipt_rec.attribute16,
2145 x_attribute17 => p_r_receipt_rec.attribute17,
2146 x_attribute18 => p_r_receipt_rec.attribute18,
2147 x_attribute19 => p_r_receipt_rec.attribute19,
2148 x_attribute20 => p_r_receipt_rec.attribute20);
2149 p_n_receipt_error_id := l_n_lockbox_receipt_error_id;
2150 END insert_lb_errors;
2151
2152 PROCEDURE insert_lb_ovfl_errors(p_r_receipt_rec lb_receipt_rec,
2153 p_n_receipt_error_id PLS_INTEGER) AS
2154 /******************************************************************
2155 Created By : Amit Gairola
2156 Date Created By : 12-Jun-2003
2157 Purpose : Procedure for inserting data in igs_fi_lb_ovfl_errs
2158 table
2159
2160 Known limitations,enhancements,remarks:
2161 Change History
2162 Who When What
2163 svuppala 12-May-2006 Bug 5217319 Added call to format amount by rounding off to currency precision
2164 in the igs_fi_lb_ovfl_errs_pkg.insert_row call
2165 ***************************************************************** */
2166 l_v_rowid VARCHAR2(25);
2167 l_n_rec_oflow_err_id igs_fi_lb_ovfl_errs.receipt_overflow_error_id%TYPE;
2168 BEGIN
2169 l_v_rowid := null;
2170 l_n_rec_oflow_err_id := null;
2171
2172 -- Create a record in the IGS_FI_LB_OVFL_ERRS table
2173 -- Bug 5217319 Added call to format amount by rounding off to currency precision
2174 igs_fi_lb_ovfl_errs_pkg.insert_row(x_rowid => l_v_rowid,
2175 x_receipt_overflow_error_id => l_n_rec_oflow_err_id,
2176 x_lockbox_receipt_error_id => p_n_receipt_error_id,
2177 x_charge_cd1 => p_r_receipt_rec.charge_cd1,
2178 x_charge_cd2 => p_r_receipt_rec.charge_cd2,
2179 x_charge_cd3 => p_r_receipt_rec.charge_cd3,
2180 x_charge_cd4 => p_r_receipt_rec.charge_cd4,
2181 x_charge_cd5 => p_r_receipt_rec.charge_cd5,
2182 x_charge_cd6 => p_r_receipt_rec.charge_cd6,
2183 x_charge_cd7 => p_r_receipt_rec.charge_cd7,
2184 x_charge_cd8 => p_r_receipt_rec.charge_cd8,
2185 x_applied_amt1 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt1),
2186 x_applied_amt2 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt2),
2187 x_applied_amt3 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt3),
2188 x_applied_amt4 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt4),
2189 x_applied_amt5 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt5),
2190 x_applied_amt6 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt6),
2191 x_applied_amt7 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt7),
2192 x_applied_amt8 => igs_fi_gen_gl.get_formatted_amount(p_r_receipt_rec.applied_amt8));
2193 END insert_lb_ovfl_errors;
2194
2195 PROCEDURE delete_err_success(p_r_rowid rowid) AS
2196 /******************************************************************
2197 Created By : Amit Gairola
2198 Date Created By : 12-Jun-2003
2199 Purpose : Procedure for deleting the successful records
2200
2201 Known limitations,enhancements,remarks:
2202 Change History
2203 Who When What
2204 ***************************************************************** */
2205 CURSOR cur_lb_err(cp_rowid rowid) IS
2206 SELECT lockbox_receipt_error_id
2207 FROM igs_fi_lb_rect_errs
2208 WHERE rowid = cp_rowid;
2209
2210 CURSOR cur_lb_oflow(cp_lb_rec_err_id igs_fi_lb_ovfl_errs.lockbox_receipt_error_id%TYPE) IS
2211 SELECT rowid row_id
2212 FROM igs_fi_lb_ovfl_errs
2213 WHERE lockbox_receipt_error_id = cp_lb_rec_err_id;
2214
2215 l_n_receipt_error_id igs_fi_lb_rect_errs.lockbox_receipt_error_id%TYPE;
2216 BEGIN
2217
2218 -- For the rowid passed, identifiy the receipt error_id
2219 OPEN cur_lb_err(p_r_rowid);
2220 FETCH cur_lb_err INTO l_n_receipt_error_id;
2221 CLOSE cur_lb_err;
2222
2223 -- For the receipt error id, loop across the records in the Overflow table
2224 FOR l_oflow_rec IN cur_lb_oflow(l_n_receipt_error_id) LOOP
2225
2226 -- delete the records in the Overflow table
2227 igs_fi_lb_ovfl_errs_pkg.delete_row(l_oflow_rec.row_id);
2228 END LOOP;
2229
2230 -- Delete the records in the main IGS_FI_LB_RECT_ERRS errors
2231 igs_fi_lb_rect_errs_pkg.delete_row(p_r_rowid);
2232 END delete_err_success;
2233
2234 PROCEDURE invoke_credits_api_pvt(p_r_receipt_rec lb_receipt_rec,
2235 p_n_credit_type_id NUMBER,
2236 p_n_receipt_amt NUMBER,
2237 p_n_credit_id OUT NOCOPY PLS_INTEGER,
2238 p_v_status OUT NOCOPY VARCHAR2,
2239 p_v_message_text OUT NOCOPY VARCHAR2) AS
2240 /******************************************************************
2241 Created By : Amit Gairola
2242 Date Created By : 12-Jun-2003
2243 Purpose : Procedure for invoking Private Credits API
2244
2245 Known limitations,enhancements,remarks:
2246 Change History
2247 Who When What
2248 pmarada 26-JUL-2005 Enh 3392095, modifed as per tution waiver build, passing p_api_version
2249 parameter value as 2.1 to the igs_fi_credit_pvt.create_credit call
2250 svuppala 9-JUN-2005 Enh 3442712 - Impact of automatic generation of the Receipt Number.
2251 changed logic for credit_number.
2252 shtatiko 26-AUG-2003 Enh# 3045007, Added two new parameters, p_n_credit_type_id and p_n_receipt_amt.
2253 pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds
2254 Added code to get message even if
2255 credits_api returns with status = 'S'
2256 ***************************************************************** */
2257 l_attribute_rec igs_fi_credits_api_pub.attribute_rec_type;
2258 l_credit_rec igs_fi_credit_pvt.credit_rec_type;
2259 l_v_status VARCHAR2(1);
2260 l_n_msg_count NUMBER(10);
2261 l_v_msg_data VARCHAR2(2000);
2262 l_n_credit_id igs_fi_credits.credit_id%TYPE;
2263 l_n_cr_activity_id igs_fi_cr_activities.credit_activity_id%TYPE;
2264 l_v_msg_txt VARCHAR2(2000);
2265 l_v_credit_number igs_fi_credits_all.credit_number%TYPE;
2266
2267
2268
2269 BEGIN
2270
2271 -- Invoke the Credits API for the receipt record
2272 l_attribute_rec.p_attribute_category := p_r_receipt_rec.attribute_category;
2273 l_attribute_rec.p_attribute1 := p_r_receipt_rec.attribute1;
2274 l_attribute_rec.p_attribute2 := p_r_receipt_rec.attribute2;
2275 l_attribute_rec.p_attribute3 := p_r_receipt_rec.attribute3;
2276 l_attribute_rec.p_attribute4 := p_r_receipt_rec.attribute4;
2277 l_attribute_rec.p_attribute5 := p_r_receipt_rec.attribute5;
2278 l_attribute_rec.p_attribute6 := p_r_receipt_rec.attribute6;
2279 l_attribute_rec.p_attribute7 := p_r_receipt_rec.attribute7;
2280 l_attribute_rec.p_attribute8 := p_r_receipt_rec.attribute8;
2281 l_attribute_rec.p_attribute9 := p_r_receipt_rec.attribute9;
2282 l_attribute_rec.p_attribute10 := p_r_receipt_rec.attribute10;
2283 l_attribute_rec.p_attribute11 := p_r_receipt_rec.attribute11;
2284 l_attribute_rec.p_attribute12 := p_r_receipt_rec.attribute12;
2285 l_attribute_rec.p_attribute13 := p_r_receipt_rec.attribute13;
2286 l_attribute_rec.p_attribute14 := p_r_receipt_rec.attribute14;
2287 l_attribute_rec.p_attribute15 := p_r_receipt_rec.attribute15;
2288 l_attribute_rec.p_attribute16 := p_r_receipt_rec.attribute16;
2289 l_attribute_rec.p_attribute17 := p_r_receipt_rec.attribute17;
2290 l_attribute_rec.p_attribute18 := p_r_receipt_rec.attribute18;
2291 l_attribute_rec.p_attribute19 := p_r_receipt_rec.attribute19;
2292 l_attribute_rec.p_attribute20 := p_r_receipt_rec.attribute20;
2293
2294
2295 -- At present the deposit date, batch name and lockbox interface id columns are commented
2296 -- due to the changes pending for the Credits API
2297 l_credit_rec.p_credit_status := 'CLEARED';
2298 l_credit_rec.p_credit_source := null;
2299 l_credit_rec.p_party_id := p_r_receipt_rec.mapped_party_id;
2300 l_credit_rec.p_credit_instrument := 'LOCKBOX';
2301 l_credit_rec.p_description := g_v_cr_desc;
2302 l_credit_rec.p_currency_cd := g_v_currency_cd;
2303 l_credit_rec.p_exchange_rate := 1;
2304 l_credit_rec.p_transaction_date := trunc(sysdate);
2305 l_credit_rec.p_effective_date := trunc(sysdate);
2306 l_credit_rec.p_receipt_lockbox_number := p_r_receipt_rec.lockbox_name;
2307 l_credit_rec.p_fee_cal_type := p_r_receipt_rec.mapped_fee_cal_type;
2308 l_credit_rec.p_fee_ci_sequence_number := p_r_receipt_rec.mapped_fee_ci_sequence_number;
2309 l_credit_rec.p_check_number := p_r_receipt_rec.check_cd;
2310 l_credit_rec.p_source_tran_type := p_r_receipt_rec.source_transaction_type;
2311 l_credit_rec.p_source_tran_ref_number := p_r_receipt_rec.adm_application_id;
2312 l_credit_rec.p_gl_date := p_r_receipt_rec.gl_date;
2313 l_credit_rec.p_deposit_date := p_r_receipt_rec.deposit_date;
2314 l_credit_rec.p_batch_name := p_r_receipt_rec.batch_name;
2315 l_credit_rec.p_lockbox_interface_id := p_r_receipt_rec.lockbox_interface_id;
2316
2317 -- Assign passed Credit Type Id and Receipt Amount.
2318 l_credit_rec.p_credit_type_id := p_n_credit_type_id;
2319 l_credit_rec.p_amount := p_n_receipt_amt;
2320
2321 l_n_credit_id := null;
2322 l_n_cr_activity_id := null;
2323 igs_fi_credit_pvt.create_credit(p_api_version => 2.1,
2324 p_init_msg_list => fnd_api.g_true,
2325 p_commit => fnd_api.g_false,
2326 p_validation_level => fnd_api.g_valid_level_none,
2327 x_return_status => l_v_status,
2328 x_msg_count => l_n_msg_count,
2329 x_msg_data => l_v_msg_data,
2330 p_credit_rec => l_credit_rec,
2331 p_attribute_record => l_attribute_rec,
2332 x_credit_id => l_n_credit_id,
2333 x_credit_activity_id => l_n_cr_activity_id,
2334 x_credit_number => l_v_credit_number);
2335 IF l_v_status = 'S' THEN
2336 p_n_credit_id := l_n_credit_id;
2337 p_v_status := l_v_status;
2338 -- If Holds Release fails, then status = 'S' but msg count will be > 0
2339 -- Show the message on the stack in such a case
2340 IF l_n_msg_count <> 0 THEN
2341 FOR l_n_cntr IN 1..l_n_msg_count LOOP
2342 l_v_msg_txt := fnd_msg_pub.get(p_msg_index => l_n_cntr, p_encoded => 'T');
2343 fnd_message.set_encoded(l_v_msg_txt);
2344 p_v_message_text := p_v_message_text||fnd_message.get;
2345 END LOOP;
2346 END IF;
2347 ELSE
2348 -- If the credits API returns an error, then pass the error message out
2349 p_n_credit_id := null;
2350 p_v_status := l_v_status;
2351 IF l_n_msg_count = 1 THEN
2352 fnd_message.set_encoded(l_v_msg_data);
2353 p_v_message_text := fnd_message.get;
2354 ELSE
2355 FOR l_n_cntr IN 1..l_n_msg_count LOOP
2356 l_v_msg_txt := fnd_msg_pub.get(p_msg_index => l_n_cntr, p_encoded => 'T');
2357 fnd_message.set_encoded(l_v_msg_txt);
2358 p_v_message_text := p_v_message_text||fnd_message.get;
2359 END LOOP;
2360 END IF;
2361 END IF;
2362 END invoke_credits_api_pvt;
2363
2364 PROCEDURE valtype2_and_import_rects(p_t_lb_rec_tab lb_receipt_tab,
2365 p_v_test_run VARCHAR2,
2366 p_d_gl_date DATE,
2367 p_v_invoked_from VARCHAR2) AS
2368 /******************************************************************
2369 Created By : Amit Gairola
2370 Date Created By : 12-Jun-2003
2371 Purpose : Procedure for type2 validations
2372
2373 Known limitations,enhancements,remarks:
2374 Change History
2375 Who When What
2376 shtatiko 27-AUG-2003 Enh# 3045007, Added logic for creating Installment Credits.
2377 pathipat 21-Aug-2003 Enh 3076768 - Auto Release of Holds
2378 Logged message even if invoke_credits_api_pvt() returns Success
2379 for Holds-related actions
2380 agairola 01-Jul-03 Bug: 3030453 changed the call to the l_t_ro_tab.DELETE
2381 to execute only if the receipt number matches the counter
2382 agairola 01-Jul-03 Bug: 3030673 assigned g_n_retcode = 1 when credits API
2383 invocation fails
2384 ***************************************************************** */
2385 l_b_val_err BOOLEAN;
2386
2387 -- In this cursor, HZ_PARTIES has been directly used to improve performance
2388 CURSOR cur_party(cp_party_number hz_parties.party_number%TYPE) IS
2389 SELECT party_id person_id
2390 FROM hz_parties
2391 WHERE party_number = cp_party_number;
2392
2393 CURSOR cur_lb_crt(cp_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE) IS
2394 SELECT default_credit_type_id
2395 FROM igs_fi_lockboxes
2396 WHERE lockbox_name = cp_lockbox_name;
2397
2398 CURSOR cur_fcmap(cp_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE,
2399 cp_bank_cd igs_fi_lb_fcis.bank_cd%TYPE) IS
2400 SELECT fee_cal_type,
2401 fee_ci_sequence_number
2402 FROM igs_fi_lb_fcis
2403 WHERE lockbox_name = cp_lockbox_name
2404 AND bank_cd = cp_bank_cd;
2405
2406 CURSOR cur_lb_crt_map(cp_lockbox_name igs_fi_lb_cr_types.lockbox_name%TYPE,
2407 cp_bank_cd igs_fi_lb_cr_types.bank_cd%TYPE) IS
2408 SELECT credit_type_id
2409 FROM igs_fi_lb_cr_types
2410 WHERE bank_cd = cp_bank_cd
2411 AND lockbox_name = cp_lockbox_name;
2412
2413 l_t_rc_tab lb_receipt_tab;
2414 l_n_rc_cntr NUMBER(38) := 0;
2415
2416 l_t_ro_tab lb_receipt_tab;
2417 l_n_ro_cntr NUMBER(38) := 0;
2418 l_v_credit_class igs_fi_cr_types.credit_class%TYPE;
2419 l_b_ret_stat BOOLEAN;
2420 l_b_ro_rec_found BOOLEAN;
2421 l_n_pay_cr_type_id igs_fi_cr_types.credit_type_id%TYPE;
2422 l_v_closing_status VARCHAR2(5);
2423 l_v_message_name VARCHAR2(2000);
2424 l_v_ld_cal_type igs_ca_inst.cal_type%TYPE;
2425 l_n_ld_seq_num igs_ca_inst.sequence_number%TYPE;
2426 l_v_fee_type igs_fi_fee_type.fee_type%TYPE;
2427 l_n_invoice_id igs_fi_inv_int.invoice_id%TYPE;
2428 l_v_message_text VARCHAR2(2000);
2429 l_b_ro_rec_match BOOLEAN;
2430 l_n_rec_err_id igs_fi_lb_rect_errs.lockbox_receipt_error_id%TYPE;
2431 l_n_credit_id igs_fi_credits.credit_id%TYPE;
2432 l_v_status VARCHAR2(1);
2433 l_n_act_amnt_applied igs_fi_applications.amount_applied%TYPE;
2434 l_n_app_id igs_fi_applications.application_id%TYPE;
2435 l_n_rec_amnt_prc igs_fi_credits.amount%TYPE;
2436 l_n_rec_cntr NUMBER(38);
2437 l_n_receipt_number NUMBER(38);
2438 l_n_cntr NUMBER(38);
2439 l_n_cntr1 NUMBER(38);
2440 l_b_rec_succ BOOLEAN;
2441
2442 l_n_amount_api igs_fi_credits_all.amount%TYPE;
2443 l_n_cr_type_id_api igs_fi_cr_types_all.credit_type_id%TYPE;
2444 l_n_dflt_cr_type_id igs_fi_cr_types_all.credit_type_id%TYPE;
2445 l_v_act_plan_name igs_fi_pp_std_attrs.payment_plan_name%TYPE;
2446 l_n_act_plan_id igs_fi_pp_std_attrs.student_plan_id%TYPE;
2447 l_n_plan_balance igs_fi_pp_instlmnts.due_amt%TYPE;
2448 l_n_diff_amount NUMBER := 0;
2449
2450 BEGIN
2451
2452 -- Log the message for the Type 2 validation errors.
2453 fnd_file.put_line(fnd_file.log,
2454 g_v_line_sep);
2455 fnd_file.put_line(fnd_file.log,
2456 g_v_label_type2);
2457 fnd_message.set_name('IGS',
2458 'IGS_FI_REC_IMP_ERRS');
2459 fnd_file.put_line(fnd_file.log,
2460 fnd_message.get);
2461 fnd_file.put_line(fnd_file.log,
2462 g_v_line_sep);
2463
2464 -- Divide the PL/SQL table into Receipt and Receipt Overflow records.
2465 IF p_t_lb_rec_tab.COUNT > 0 THEN
2466 FOR l_n_cntr IN p_t_lb_rec_tab.FIRST..p_t_lb_rec_tab.LAST LOOP
2467 IF p_t_lb_rec_tab.EXISTS(l_n_cntr) THEN
2468 IF p_t_lb_rec_tab(l_n_cntr).system_record_identifier = g_v_receipt THEN
2469 l_n_rc_cntr := l_n_rc_cntr + 1;
2470 l_t_rc_tab(l_n_rc_cntr) := p_t_lb_rec_tab(l_n_cntr);
2471 ELSE
2472 l_n_ro_cntr := l_n_ro_cntr + 1;
2473 l_t_ro_tab(l_n_ro_cntr) := p_t_lb_rec_tab(l_n_cntr);
2474 END IF;
2475 END IF;
2476 END LOOP;
2477 END IF;
2478
2479 -- The following logic associates a Receipt Overflow to a Receipt by setting the receipt
2480 -- number field in the Receipt Overflow PL/SQL table to the index of the receipt record in
2481 -- the receipt table. If the batch name is present for the receipt overflow, then the
2482 -- combination of batch name and item number is checked. if the batch name is not present,
2483 -- then null batch name and item number is checked.
2484 IF l_n_ro_cntr > 0 THEN
2485 FOR l_n_cntr IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
2486 IF l_t_ro_tab.EXISTS(l_n_cntr) THEN
2487 l_b_ro_rec_found := FALSE;
2488 IF l_t_ro_tab(l_n_cntr).batch_name IS NOT NULL THEN
2489 IF l_n_rc_cntr > 0 THEN
2490 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
2491 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
2492 IF ((l_t_rc_tab(l_n_cntr1).batch_name IS NOT NULL) AND
2493 (l_t_rc_tab(l_n_cntr1).batch_name = l_t_ro_tab(l_n_cntr).batch_name) AND
2494 (l_t_rc_tab(l_n_cntr1).item_number = l_t_ro_tab(l_n_cntr).item_number)) THEN
2495 l_b_ro_rec_found := TRUE;
2496 l_n_receipt_number := l_n_cntr1;
2497 EXIT;
2498 END IF;
2499 END IF;
2500 END LOOP;
2501 END IF;
2502 IF l_b_ro_rec_found THEN
2503 l_t_ro_tab(l_n_cntr).receipt_number := l_n_receipt_number;
2504 END IF;
2505 l_n_receipt_number := null;
2506 ELSE
2507 IF l_n_rc_cntr > 0 THEN
2508 FOR l_n_cntr1 IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
2509 IF l_t_rc_tab.EXISTS(l_n_cntr1) THEN
2510 IF ((l_t_rc_tab(l_n_cntr1).batch_name IS NULL) AND
2511 (l_t_rc_tab(l_n_cntr1).item_number = l_t_ro_tab(l_n_cntr).item_number)) THEN
2512 l_b_ro_rec_found := TRUE;
2513 l_n_receipt_number := l_n_cntr1;
2514 EXIT;
2515 END IF;
2516 END IF;
2517 END LOOP;
2518 END IF;
2519 IF l_b_ro_rec_found THEN
2520 l_t_ro_tab(l_n_cntr).receipt_number := l_n_receipt_number;
2521 END IF;
2522 l_n_receipt_number := null;
2523 END IF;
2524 END IF;
2525 END LOOP;
2526 END IF;
2527
2528 -- Loop across the receipt records
2529 IF l_n_rc_cntr > 0 THEN
2530 FOR l_n_cntr IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
2531 l_b_val_err := FALSE;
2532 g_b_log_head := FALSE;
2533 IF l_t_rc_tab.EXISTS(l_n_cntr) THEN
2534
2535 -- Validate if the party id is a valid party
2536 OPEN cur_party(l_t_rc_tab(l_n_cntr).party_number);
2537 FETCH cur_party INTO l_t_rc_tab(l_n_cntr).mapped_party_id;
2538 IF cur_party%NOTFOUND THEN
2539 l_b_val_err := TRUE;
2540 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2541 l_t_rc_tab(l_n_cntr).batch_name,
2542 l_t_rc_tab(l_n_cntr).item_number);
2543 fnd_message.set_name('IGS',
2544 'IGS_FI_INV_PARTY_NUMBER');
2545 fnd_message.set_token('PARTY_NUMBER',
2546 l_t_rc_tab(l_n_cntr).party_number);
2547 fnd_file.put_line(fnd_file.log,
2548 fnd_message.get);
2549 END IF;
2550 CLOSE cur_party;
2551
2552 -- Validate if the Credit Type is valid credit type
2553 IF l_t_rc_tab(l_n_cntr).credit_type_cd IS NULL THEN
2554
2555 -- Get the default credit type id from the lockbox
2556 OPEN cur_lb_crt(l_t_rc_tab(l_n_cntr).lockbox_name);
2557 FETCH cur_lb_crt INTO l_t_rc_tab(l_n_cntr).mapped_credit_type_id;
2558 CLOSE cur_lb_crt;
2559
2560 -- If the mapped credit type id is null, then it is a type 2 validation error
2561 IF l_t_rc_tab(l_n_cntr).mapped_credit_type_id IS NULL THEN
2562 l_b_val_err := TRUE;
2563 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2564 l_t_rc_tab(l_n_cntr).batch_name,
2565 l_t_rc_tab(l_n_cntr).item_number);
2566 fnd_message.set_name('IGS',
2567 'IGS_FI_NO_DEF_CR_TYPE');
2568 fnd_message.set_token('LOCKBOX_NAME',
2569 l_t_rc_tab(l_n_cntr).lockbox_name);
2570 fnd_file.put_line(fnd_file.log,
2571 fnd_message.get);
2572 END IF;
2573 ELSE
2574
2575 -- For the Credit type code, get the mapped credit type id from the credit type
2576 -- mapping table
2577 OPEN cur_lb_crt_map(l_t_rc_tab(l_n_cntr).lockbox_name,
2578 l_t_rc_tab(l_n_cntr).credit_type_cd);
2579 FETCH cur_lb_crt_map INTO l_t_rc_tab(l_n_cntr).mapped_credit_type_id;
2580
2581 -- If the mapping could not be found, then it is a type 2 validation error
2582 IF cur_lb_crt_map%NOTFOUND THEN
2583 l_b_val_err := TRUE;
2584 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2585 l_t_rc_tab(l_n_cntr).batch_name,
2586 l_t_rc_tab(l_n_cntr).item_number);
2587 fnd_message.set_name('IGS',
2588 'IGS_FI_NO_CR_TYPE_MAP');
2589 fnd_message.set_token('CREDIT_TYPE_BANK_CODE',
2590 l_t_rc_tab(l_n_cntr).credit_type_cd);
2591 fnd_file.put_line(fnd_file.log,
2592 fnd_message.get);
2593 END IF;
2594 CLOSE cur_lb_crt_map;
2595 END IF;
2596
2597 -- If the mapped credit type id is not null then
2598 IF l_t_rc_tab(l_n_cntr).mapped_credit_type_id IS NOT NULL THEN
2599 l_v_credit_class := null;
2600 l_b_ret_stat := null;
2601
2602 -- Validate the Credit Type Id and get the credit class
2603 igs_fi_crdapi_util.validate_credit_type(p_n_credit_type_id => l_t_rc_tab(l_n_cntr).mapped_credit_type_id,
2604 p_v_credit_class => l_v_credit_class,
2605 p_b_return_stat => l_b_ret_stat);
2606 IF NOT l_b_ret_stat THEN
2607 l_b_val_err := TRUE;
2608 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2609 l_t_rc_tab(l_n_cntr).batch_name,
2610 l_t_rc_tab(l_n_cntr).item_number);
2611 fnd_message.set_name('IGS',
2612 'IGS_FI_CAPI_CR_TYPE_INVALID');
2613 fnd_message.set_token('CR_TYPE',
2614 get_credit_type_name(l_t_rc_tab(l_n_cntr).mapped_credit_type_id));
2615 fnd_file.put_line(fnd_file.log,
2616 fnd_message.get);
2617 END IF;
2618
2619 -- If the Credit Class is Enrollment Deposit or Other Deposit, then check if the Charge Code and Charge Amount
2620 -- are provided. If they are provided then it is an error.
2621 -- Added g_v_inst_payment to the following condition as part of Enh# 3045007 as Receipt Record with credit class
2622 -- Installment Payment cannot have designated payments associated with it.
2623 IF l_v_credit_class IN (g_v_enr_deposit,
2624 g_v_oth_deposit,
2625 g_v_inst_payment) THEN
2626 IF ((l_t_rc_tab(l_n_cntr).charge_cd1 IS NOT NULL) AND
2627 (l_t_rc_tab(l_n_cntr).applied_amt1 IS NOT NULL)) OR
2628 ((l_t_rc_tab(l_n_cntr).charge_cd2 IS NOT NULL) AND
2629 (l_t_rc_tab(l_n_cntr).applied_amt2 IS NOT NULL)) OR
2630 ((l_t_rc_tab(l_n_cntr).charge_cd3 IS NOT NULL) AND
2631 (l_t_rc_tab(l_n_cntr).applied_amt3 IS NOT NULL)) OR
2632 ((l_t_rc_tab(l_n_cntr).charge_cd4 IS NOT NULL) AND
2633 (l_t_rc_tab(l_n_cntr).applied_amt4 IS NOT NULL)) OR
2634 ((l_t_rc_tab(l_n_cntr).charge_cd5 IS NOT NULL) AND
2635 (l_t_rc_tab(l_n_cntr).applied_amt5 IS NOT NULL)) OR
2636 ((l_t_rc_tab(l_n_cntr).charge_cd6 IS NOT NULL) AND
2637 (l_t_rc_tab(l_n_cntr).applied_amt6 IS NOT NULL)) OR
2638 ((l_t_rc_tab(l_n_cntr).charge_cd7 IS NOT NULL) AND
2639 (l_t_rc_tab(l_n_cntr).applied_amt7 IS NOT NULL)) OR
2640 ((l_t_rc_tab(l_n_cntr).charge_cd8 IS NOT NULL) AND
2641 (l_t_rc_tab(l_n_cntr).applied_amt8 IS NOT NULL)) THEN
2642 -- Added code to set l_b_val_err as part of Enh# 3045007 so that application does not happen after credit creation.
2643 l_b_val_err := TRUE;
2644 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2645 l_t_rc_tab(l_n_cntr).batch_name,
2646 l_t_rc_tab(l_n_cntr).item_number);
2647 fnd_message.set_name('IGS',
2648 'IGS_FI_DEP_NO_OVFLOW');
2649 fnd_message.set_token('CREDIT_CLASS',
2650 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CLASS', l_v_credit_class));
2651 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
2652 END IF;
2653
2654 -- Also check if there are any overflow records for the receipt in case of Enrollment Deposit or Other Deposit
2655 -- If there are any overflow records then it is a type 2 error.
2656 IF l_n_ro_cntr > 0 THEN
2657 l_b_ro_rec_found := FALSE;
2658 l_n_cntr1 := 0;
2659 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
2660 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
2661 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
2662 l_b_ro_rec_found := TRUE;
2663 EXIT;
2664 END IF;
2665 END IF;
2666 END LOOP;
2667
2668 IF l_b_ro_rec_found THEN
2669 l_b_val_err := TRUE;
2670 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2671 l_t_rc_tab(l_n_cntr).batch_name,
2672 l_t_rc_tab(l_n_cntr).item_number);
2673 fnd_message.set_name('IGS',
2674 'IGS_FI_DEP_NO_OVFLOW');
2675 fnd_message.set_token('CREDIT_CLASS',
2676 igs_fi_gen_gl.get_lkp_meaning('IGS_FI_CREDIT_CLASS', l_v_credit_class));
2677 fnd_file.put_line(fnd_file.log,
2678 fnd_message.get);
2679 END IF;
2680 END IF;
2681
2682 IF l_v_credit_class IN (g_v_enr_deposit,
2683 g_v_oth_deposit ) THEN
2684
2685 -- Validate if the Credit Class is Enrollment Deposit, then the mapped party should be a Student
2686 -- If the creidt class is Other Deposit, then the mapped party should be a person
2687 IF NOT igs_fi_crdapi_util.validate_party_id(p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
2688 p_v_credit_class => l_v_credit_class) THEN
2689 l_b_val_err := TRUE;
2690 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2691 l_t_rc_tab(l_n_cntr).batch_name,
2692 l_t_rc_tab(l_n_cntr).item_number);
2693 IF l_v_credit_class = g_v_enr_deposit THEN
2694 fnd_message.set_name('IGS',
2695 'IGS_FI_PARTY_STUDENT');
2696 fnd_message.set_token('PARTY_NUMBER',
2697 l_t_rc_tab(l_n_cntr).party_number);
2698 fnd_file.put_line(fnd_file.log,
2699 fnd_message.get);
2700 ELSIF l_v_credit_class = g_v_oth_deposit THEN
2701 fnd_message.set_name('IGS',
2702 'IGS_FI_PARTY_PERSON');
2703 fnd_message.set_token('PARTY_NUMBER',
2704 l_t_rc_tab(l_n_cntr).party_number);
2705 fnd_file.put_line(fnd_file.log,
2706 fnd_message.get);
2707 END IF;
2708 END IF;
2709 l_b_ret_stat := null;
2710 l_n_pay_cr_type_id := null;
2711
2712 -- Validate the Payment Credit Type for the Mapped Credit Type
2713 igs_fi_crdapi_util.validate_dep_crtype(p_n_credit_type_id => l_t_rc_tab(l_n_cntr).mapped_credit_type_id,
2714 p_n_pay_credit_type_id => l_n_pay_cr_type_id,
2715 p_b_return_stat => l_b_ret_stat);
2716
2717 -- If the payment credit type is not valid or is closed, then log the error in the log file
2718 IF NOT l_b_ret_stat THEN
2719 l_b_val_err := TRUE;
2720 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2721 l_t_rc_tab(l_n_cntr).batch_name,
2722 l_t_rc_tab(l_n_cntr).item_number);
2723 fnd_message.set_name('IGS',
2724 'IGS_FI_PCT_DCT_INVALID');
2725 fnd_message.set_token('PAY_CR_TYPE',
2726 get_credit_type_name(l_n_pay_cr_type_id));
2727 fnd_message.set_token('DEP_CR_TYPE',
2728 get_credit_type_name(l_t_rc_tab(l_n_cntr).mapped_credit_type_id));
2729 fnd_file.put_line(fnd_file.log,
2730 fnd_message.get);
2731 END IF;
2732
2733 -- If the credit class is ENRDEPOSIT, then validate if the Admission Application Id is valid. If not, log the error in the
2734 -- log file.
2735 IF l_v_credit_class = g_v_enr_deposit THEN
2736 IF NOT igs_fi_crdapi_util.validate_source_tran_ref_num(p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
2737 p_n_source_tran_ref_num => l_t_rc_tab(l_n_cntr).adm_application_id) THEN
2738 l_b_val_err := TRUE;
2739 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2740 l_t_rc_tab(l_n_cntr).batch_name,
2741 l_t_rc_tab(l_n_cntr).item_number);
2742 fnd_message.set_name('IGS',
2743 'IGS_FI_INV_ADM_APPL_ID');
2744 fnd_message.set_token('ADMISSION_APPLICATION_ID',
2745 l_t_rc_tab(l_n_cntr).adm_application_id);
2746 fnd_message.set_token('PARTY_NUMBER',
2747 l_t_rc_tab(l_n_cntr).party_number);
2748 fnd_file.put_line(fnd_file.log,
2749 fnd_message.get);
2750 ELSE
2751 l_t_rc_tab(l_n_cntr).source_transaction_type := g_v_adm;
2752 END IF;
2753 END IF;
2754 END IF;
2755 END IF; -- End if for the Credit Class
2756 END IF; -- Check for the Mapped Credit Id being present
2757
2758 -- If the GL Date Source is Deposit Date, then
2759 IF g_v_gl_date_source = g_v_deposit_date THEN
2760
2761 -- Validate if the Deposit Date is null. If yes, then log this error in the log file.
2762 IF l_t_rc_tab(l_n_cntr).deposit_date IS NULL THEN
2763 l_b_val_err := TRUE;
2764 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2765 l_t_rc_tab(l_n_cntr).batch_name,
2766 l_t_rc_tab(l_n_cntr).item_number);
2767 fnd_message.set_name('IGS',
2768 'IGS_FI_GL_DATE_MISSING');
2769 fnd_file.put_line(fnd_file.log,
2770 fnd_message.get);
2771 ELSE
2772
2773 -- Else, validate if the deposit date is in an Open or Future Period or is in a valid accounting period.
2774 l_t_rc_tab(l_n_cntr).gl_date := trunc(l_t_rc_tab(l_n_cntr).deposit_date);
2775 igs_fi_gen_gl.get_period_status_for_date(p_d_date => l_t_rc_tab(l_n_cntr).gl_date,
2776 p_v_closing_status => l_v_closing_status,
2777 p_v_message_name => l_v_message_name);
2778 IF l_v_message_name IS NOT NULL THEN
2779 l_b_val_err := TRUE;
2780 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2781 l_t_rc_tab(l_n_cntr).batch_name,
2782 l_t_rc_tab(l_n_cntr).item_number);
2783 fnd_message.set_name('IGS',
2784 l_v_message_name);
2785 fnd_file.put_line(fnd_file.log,
2786 fnd_message.get);
2787 ELSIF l_v_closing_status NOT IN ('O','F') THEN
2788 l_b_val_err := TRUE;
2789 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2790 l_t_rc_tab(l_n_cntr).batch_name,
2791 l_t_rc_tab(l_n_cntr).item_number);
2792 fnd_message.set_name('IGS',
2793 'IGS_FI_INVALID_GL_DATE');
2794 fnd_message.set_token('GL_DATE',
2795 l_t_rc_tab(l_n_cntr).gl_date);
2796 fnd_file.put_line(fnd_file.log,
2797 fnd_message.get);
2798 END IF;
2799 END IF;
2800 ELSIF g_v_gl_date_source = g_v_imp_date THEN
2801
2802 -- If the GL Date Source is Import Date, then System Date is used for the gl date
2803 l_t_rc_tab(l_n_cntr).gl_date := trunc(sysdate);
2804 ELSIF g_v_gl_date_source = g_v_user_supp_dt THEN
2805
2806 -- If the GL Date Source is User Supplied Date, then the input parameter p_d_gl_date is taken as GL Date
2807 l_t_rc_tab(l_n_cntr).gl_date := p_d_gl_date;
2808 END IF;
2809
2810 -- If the fee calendar instance code is provided, then
2811 IF l_t_rc_tab(l_n_cntr).fee_cal_instance_cd IS NOT NULL THEN
2812
2813 -- Get the mapped Fee Calendar Instance for the lockbox
2814 OPEN cur_fcmap(l_t_rc_tab(l_n_cntr).lockbox_name,
2815 l_t_rc_tab(l_n_cntr).fee_cal_instance_cd);
2816 FETCH cur_fcmap INTO l_t_rc_tab(l_n_cntr).mapped_fee_cal_type,
2817 l_t_rc_tab(l_n_cntr).mapped_fee_ci_sequence_number;
2818
2819 -- If the mapping is not available, then log the message in the log file.
2820 IF cur_fcmap%NOTFOUND THEN
2821 l_b_val_err := TRUE;
2822 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2823 l_t_rc_tab(l_n_cntr).batch_name,
2824 l_t_rc_tab(l_n_cntr).item_number);
2825 fnd_message.set_name('IGS',
2826 'IGS_FI_NO_FCI_MAP');
2827 fnd_message.set_token('FEE_CAL_INSTANCE_CODE',
2828 l_t_rc_tab(l_n_cntr).fee_cal_instance_cd);
2829 fnd_file.put_line(fnd_file.log,
2830 fnd_message.get);
2831 END IF;
2832 CLOSE cur_fcmap;
2833
2834 -- If the Mapped Fee Cal Type can be derived, then check if it is a valid Fee calendar.
2835 IF ((l_t_rc_tab(l_n_cntr).mapped_fee_cal_type IS NOT NULL) AND
2836 (l_t_rc_tab(l_n_cntr).mapped_fee_ci_sequence_number IS NOT NULL)) THEN
2837 IF NOT igs_fi_crdapi_util.validate_cal_inst(p_v_cal_type => l_t_rc_tab(l_n_cntr).mapped_fee_cal_type,
2838 p_n_ci_sequence_number => l_t_rc_tab(l_n_cntr).mapped_fee_ci_sequence_number,
2839 p_v_s_cal_cat => g_v_fee) THEN
2840 l_b_val_err := TRUE;
2841 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2842 l_t_rc_tab(l_n_cntr).batch_name,
2843 l_t_rc_tab(l_n_cntr).item_number);
2844 fnd_message.set_name('IGS',
2845 'IGS_FI_FCI_INVALID');
2846 fnd_message.set_token('FEE_CAL_INSTANCE_CODE',
2847 l_t_rc_tab(l_n_cntr).fee_cal_instance_cd);
2848 fnd_file.put_line(fnd_file.log,
2849 fnd_message.get);
2850 END IF;
2851
2852 -- Check if the Mapped Fee Calendar Instance has a valid Load Calendar Instance
2853 l_v_ld_cal_type := null;
2854 l_n_ld_seq_num := null;
2855 l_v_message_name := null;
2856 l_b_ret_stat := null;
2857 igs_fi_crdapi_util.validate_fci_lci_reln(p_v_fee_cal_type => l_t_rc_tab(l_n_cntr).mapped_fee_cal_type,
2858 p_n_fee_ci_sequence_number => l_t_rc_tab(l_n_cntr).mapped_fee_ci_sequence_number,
2859 p_v_ld_cal_type => l_v_ld_cal_type,
2860 p_n_ld_ci_sequence_number => l_n_ld_seq_num,
2861 p_v_message_name => l_v_message_name,
2862 p_b_return_stat => l_b_ret_stat);
2863 IF NOT l_b_ret_stat THEN
2864 l_b_val_err := TRUE;
2865 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2866 l_t_rc_tab(l_n_cntr).batch_name,
2867 l_t_rc_tab(l_n_cntr).item_number);
2868 fnd_message.set_name('IGS',
2869 l_v_message_name);
2870 fnd_file.put_line(fnd_file.log,
2871 fnd_message.get);
2872 END IF;
2873 END IF;
2874 END IF;
2875
2876 -- Validate the Receipt Amount
2877 l_v_message_name := null;
2878 l_b_ret_stat := null;
2879 igs_fi_crdapi_util.validate_amount(p_n_amount => l_t_rc_tab(l_n_cntr).receipt_amt,
2880 p_b_return_status => l_b_ret_stat,
2881 p_v_message_name => l_v_message_name);
2882 IF NOT l_b_ret_stat THEN
2883 l_b_val_err := TRUE;
2884 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2885 l_t_rc_tab(l_n_cntr).batch_name,
2886 l_t_rc_tab(l_n_cntr).item_number);
2887 fnd_message.set_name('IGS',
2888 l_v_message_name);
2889 fnd_file.put_line(fnd_file.log,
2890 fnd_message.get);
2891 END IF;
2892
2893 -- Validate the DFF
2894 IF NOT igs_fi_crdapi_util.validate_desc_flex(p_v_attribute_category => l_t_rc_tab(l_n_cntr).attribute_category,
2895 p_v_attribute1 => l_t_rc_tab(l_n_cntr).attribute1,
2896 p_v_attribute2 => l_t_rc_tab(l_n_cntr).attribute2,
2897 p_v_attribute3 => l_t_rc_tab(l_n_cntr).attribute3,
2898 p_v_attribute4 => l_t_rc_tab(l_n_cntr).attribute4,
2899 p_v_attribute5 => l_t_rc_tab(l_n_cntr).attribute5,
2900 p_v_attribute6 => l_t_rc_tab(l_n_cntr).attribute6,
2901 p_v_attribute7 => l_t_rc_tab(l_n_cntr).attribute7,
2902 p_v_attribute8 => l_t_rc_tab(l_n_cntr).attribute8,
2903 p_v_attribute9 => l_t_rc_tab(l_n_cntr).attribute9,
2904 p_v_attribute10 => l_t_rc_tab(l_n_cntr).attribute10,
2905 p_v_attribute11 => l_t_rc_tab(l_n_cntr).attribute11,
2906 p_v_attribute12 => l_t_rc_tab(l_n_cntr).attribute12,
2907 p_v_attribute13 => l_t_rc_tab(l_n_cntr).attribute13,
2908 p_v_attribute14 => l_t_rc_tab(l_n_cntr).attribute14,
2909 p_v_attribute15 => l_t_rc_tab(l_n_cntr).attribute15,
2910 p_v_attribute16 => l_t_rc_tab(l_n_cntr).attribute16,
2911 p_v_attribute17 => l_t_rc_tab(l_n_cntr).attribute17,
2912 p_v_attribute18 => l_t_rc_tab(l_n_cntr).attribute18,
2913 p_v_attribute19 => l_t_rc_tab(l_n_cntr).attribute19,
2914 p_v_attribute20 => l_t_rc_tab(l_n_cntr).attribute20,
2915 p_v_desc_flex_name => 'IGS_FI_CREDITS_ALL_FLEX') THEN
2916 l_b_val_err := TRUE;
2917 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2918 l_t_rc_tab(l_n_cntr).batch_name,
2919 l_t_rc_tab(l_n_cntr).item_number);
2920 fnd_message.set_name('IGS',
2921 'IGS_AD_INVALID_DESC_FLEX');
2922 fnd_file.put_line(fnd_file.log,
2923 fnd_message.get);
2924 END IF;
2925
2926 -- If any of the Charge Codes is not null, then validate if the charge number can be applied and is a valid charge
2927 -- If not then it is an error message.
2928 IF l_t_rc_tab(l_n_cntr).charge_cd1 IS NOT NULL THEN
2929 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2930 l_v_fee_type := null;
2931 l_n_invoice_id := null;
2932 l_v_message_text := null;
2933 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd1,
2934 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
2935 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
2936 p_v_fee_type => l_v_fee_type,
2937 p_n_invoice_id => l_n_invoice_id,
2938 p_v_message_name => l_v_message_text);
2939 IF l_v_message_text IS NOT NULL THEN
2940 l_b_val_err := TRUE;
2941 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2942 l_t_rc_tab(l_n_cntr).batch_name,
2943 l_t_rc_tab(l_n_cntr).item_number);
2944 fnd_file.put_line(fnd_file.log,
2945 l_v_message_text);
2946 ELSE
2947 l_t_rc_tab(l_n_cntr).target_invoice_id1 := l_n_invoice_id;
2948 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2949 END IF;
2950 END IF;
2951
2952 IF l_t_rc_tab(l_n_cntr).charge_cd2 IS NOT NULL THEN
2953 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2954 l_v_fee_type := null;
2955 l_n_invoice_id := null;
2956 l_v_message_text := null;
2957 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd2,
2958 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
2959 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
2960 p_v_fee_type => l_v_fee_type,
2961 p_n_invoice_id => l_n_invoice_id,
2962 p_v_message_name => l_v_message_text);
2963 IF l_v_message_text IS NOT NULL THEN
2964 l_b_val_err := TRUE;
2965 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2966 l_t_rc_tab(l_n_cntr).batch_name,
2967 l_t_rc_tab(l_n_cntr).item_number);
2968 fnd_file.put_line(fnd_file.log,
2969 l_v_message_text);
2970 ELSE
2971 l_t_rc_tab(l_n_cntr).target_invoice_id2 := l_n_invoice_id;
2972 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2973 END IF;
2974 END IF;
2975
2976 IF l_t_rc_tab(l_n_cntr).charge_cd3 IS NOT NULL THEN
2977 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2978 l_v_fee_type := null;
2979 l_n_invoice_id := null;
2980 l_v_message_text := null;
2981 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd3,
2982 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
2983 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
2984 p_v_fee_type => l_v_fee_type,
2985 p_n_invoice_id => l_n_invoice_id,
2986 p_v_message_name => l_v_message_text);
2987 IF l_v_message_text IS NOT NULL THEN
2988 l_b_val_err := TRUE;
2989 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
2990 l_t_rc_tab(l_n_cntr).batch_name,
2991 l_t_rc_tab(l_n_cntr).item_number);
2992 fnd_file.put_line(fnd_file.log,
2993 l_v_message_text);
2994 ELSE
2995 l_t_rc_tab(l_n_cntr).target_invoice_id3 := l_n_invoice_id;
2996 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
2997 END IF;
2998 END IF;
2999
3000 IF l_t_rc_tab(l_n_cntr).charge_cd4 IS NOT NULL THEN
3001 l_v_fee_type := null;
3002 l_n_invoice_id := null;
3003 l_v_message_text := null;
3004 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3005 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd4,
3006 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3007 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3008 p_v_fee_type => l_v_fee_type,
3009 p_n_invoice_id => l_n_invoice_id,
3010 p_v_message_name => l_v_message_text);
3011 IF l_v_message_text IS NOT NULL THEN
3012 l_b_val_err := TRUE;
3013 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3014 l_t_rc_tab(l_n_cntr).batch_name,
3015 l_t_rc_tab(l_n_cntr).item_number);
3016 fnd_file.put_line(fnd_file.log,
3017 l_v_message_text);
3018 ELSE
3019 l_t_rc_tab(l_n_cntr).target_invoice_id4 := l_n_invoice_id;
3020 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3021 END IF;
3022 END IF;
3023
3024 IF l_t_rc_tab(l_n_cntr).charge_cd5 IS NOT NULL THEN
3025 l_v_fee_type := null;
3026 l_n_invoice_id := null;
3027 l_v_message_text := null;
3028 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3029 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd5,
3030 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3031 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3032 p_v_fee_type => l_v_fee_type,
3033 p_n_invoice_id => l_n_invoice_id,
3034 p_v_message_name => l_v_message_text);
3035 IF l_v_message_text IS NOT NULL THEN
3036 l_b_val_err := TRUE;
3037 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3038 l_t_rc_tab(l_n_cntr).batch_name,
3039 l_t_rc_tab(l_n_cntr).item_number);
3040 fnd_file.put_line(fnd_file.log,
3041 l_v_message_text);
3042 ELSE
3043 l_t_rc_tab(l_n_cntr).target_invoice_id5 := l_n_invoice_id;
3044 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3045 END IF;
3046 END IF;
3047
3048 IF l_t_rc_tab(l_n_cntr).charge_cd6 IS NOT NULL THEN
3049 l_v_fee_type := null;
3050 l_n_invoice_id := null;
3051 l_v_message_text := null;
3052 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3053 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd6,
3054 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3055 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3056 p_v_fee_type => l_v_fee_type,
3057 p_n_invoice_id => l_n_invoice_id,
3058 p_v_message_name => l_v_message_text);
3059 IF l_v_message_text IS NOT NULL THEN
3060 l_b_val_err := TRUE;
3061 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3062 l_t_rc_tab(l_n_cntr).batch_name,
3063 l_t_rc_tab(l_n_cntr).item_number);
3064 fnd_file.put_line(fnd_file.log,
3065 l_v_message_text);
3066 ELSE
3067 l_t_rc_tab(l_n_cntr).target_invoice_id6 := l_n_invoice_id;
3068 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3069 END IF;
3070 END IF;
3071
3072 IF l_t_rc_tab(l_n_cntr).charge_cd7 IS NOT NULL THEN
3073 l_v_fee_type := null;
3074 l_n_invoice_id := null;
3075 l_v_message_text := null;
3076 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3077 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd7,
3078 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3079 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3080 p_v_fee_type => l_v_fee_type,
3081 p_n_invoice_id => l_n_invoice_id,
3082 p_v_message_name => l_v_message_text);
3083 IF l_v_message_text IS NOT NULL THEN
3084 l_b_val_err := TRUE;
3085 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3086 l_t_rc_tab(l_n_cntr).batch_name,
3087 l_t_rc_tab(l_n_cntr).item_number);
3088 fnd_file.put_line(fnd_file.log,
3089 l_v_message_text);
3090 ELSE
3091 l_t_rc_tab(l_n_cntr).target_invoice_id1 := l_n_invoice_id;
3092 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3093 END IF;
3094 END IF;
3095
3096 IF l_t_rc_tab(l_n_cntr).charge_cd8 IS NOT NULL THEN
3097 l_v_fee_type := null;
3098 l_n_invoice_id := null;
3099 l_v_message_text := null;
3100 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3101 val_charge_number_for_app(p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd8,
3102 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3103 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3104 p_v_fee_type => l_v_fee_type,
3105 p_n_invoice_id => l_n_invoice_id,
3106 p_v_message_name => l_v_message_text);
3107 IF l_v_message_text IS NOT NULL THEN
3108 l_b_val_err := TRUE;
3109 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3110 l_t_rc_tab(l_n_cntr).batch_name,
3111 l_t_rc_tab(l_n_cntr).item_number);
3112 fnd_file.put_line(fnd_file.log,
3113 l_v_message_text);
3114 ELSE
3115 l_t_rc_tab(l_n_cntr).target_invoice_id8 := l_n_invoice_id;
3116 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3117 END IF;
3118 END IF;
3119
3120 -- Also, loop across the receipt overflow records for the receipt and validate if the Charge Code
3121 -- is not null, then check if charge can be applied and is a valid charge.
3122 IF l_n_ro_cntr > 0 THEN
3123 l_n_cntr1 := 0;
3124 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
3125 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
3126 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
3127 IF l_t_ro_tab(l_n_cntr1).charge_cd1 IS NOT NULL THEN
3128 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3129 l_v_fee_type := null;
3130 l_n_invoice_id := null;
3131 l_v_message_text := null;
3132 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd1,
3133 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3134 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3135 p_v_fee_type => l_v_fee_type,
3136 p_n_invoice_id => l_n_invoice_id,
3137 p_v_message_name => l_v_message_text);
3138 IF l_v_message_text IS NOT NULL THEN
3139 l_b_val_err := TRUE;
3140 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3141 l_t_rc_tab(l_n_cntr).batch_name,
3142 l_t_rc_tab(l_n_cntr).item_number);
3143 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3144 fnd_file.put_line(fnd_file.log,
3145 l_v_message_text);
3146 ELSE
3147 IF l_b_val_err THEN
3148 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3149 ELSE
3150 l_t_ro_tab(l_n_cntr1).target_invoice_id1 := l_n_invoice_id;
3151 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3152 END IF;
3153 END IF;
3154 END IF;
3155
3156 IF l_t_ro_tab(l_n_cntr1).charge_cd2 IS NOT NULL THEN
3157 l_v_fee_type := null;
3158 l_n_invoice_id := null;
3159 l_v_message_text := null;
3160 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3161 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd2,
3162 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3163 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3164 p_v_fee_type => l_v_fee_type,
3165 p_n_invoice_id => l_n_invoice_id,
3166 p_v_message_name => l_v_message_text);
3167 IF l_v_message_text IS NOT NULL THEN
3168 l_b_val_err := TRUE;
3169 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3170 l_t_rc_tab(l_n_cntr).batch_name,
3171 l_t_rc_tab(l_n_cntr).item_number);
3172 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3173 fnd_file.put_line(fnd_file.log,
3174 l_v_message_text);
3175 ELSE
3176 IF l_b_val_err THEN
3177 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3178 ELSE
3179 l_t_ro_tab(l_n_cntr1).target_invoice_id2 := l_n_invoice_id;
3180 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3181 END IF;
3182 END IF;
3183 END IF;
3184
3185 IF l_t_ro_tab(l_n_cntr1).charge_cd3 IS NOT NULL THEN
3186 l_v_fee_type := null;
3187 l_n_invoice_id := null;
3188 l_v_message_text := null;
3189 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3190 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd3,
3191 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3192 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3193 p_v_fee_type => l_v_fee_type,
3194 p_n_invoice_id => l_n_invoice_id,
3195 p_v_message_name => l_v_message_text);
3196 IF l_v_message_text IS NOT NULL THEN
3197 l_b_val_err := TRUE;
3198 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3199 l_t_rc_tab(l_n_cntr).batch_name,
3200 l_t_rc_tab(l_n_cntr).item_number);
3201 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3202 fnd_file.put_line(fnd_file.log,
3203 l_v_message_text);
3204 ELSE
3205 IF l_b_val_err THEN
3206 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3207 ELSE
3208 l_t_ro_tab(l_n_cntr1).target_invoice_id3 := l_n_invoice_id;
3209 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3210 END IF;
3211 END IF;
3212 END IF;
3213
3214 IF l_t_ro_tab(l_n_cntr1).charge_cd4 IS NOT NULL THEN
3215 l_v_fee_type := null;
3216 l_n_invoice_id := null;
3217 l_v_message_text := null;
3218 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3219 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd4,
3220 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3221 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3222 p_v_fee_type => l_v_fee_type,
3223 p_n_invoice_id => l_n_invoice_id,
3224 p_v_message_name => l_v_message_text);
3225 IF l_v_message_text IS NOT NULL THEN
3226 l_b_val_err := TRUE;
3227 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3228 l_t_rc_tab(l_n_cntr).batch_name,
3229 l_t_rc_tab(l_n_cntr).item_number);
3230 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3231 fnd_file.put_line(fnd_file.log,
3232 l_v_message_text);
3233 ELSE
3234 IF l_b_val_err THEN
3235 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3236 ELSE
3237 l_t_ro_tab(l_n_cntr1).target_invoice_id4 := l_n_invoice_id;
3238 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3239 END IF;
3240 END IF;
3241 END IF;
3242 IF l_t_ro_tab(l_n_cntr1).charge_cd5 IS NOT NULL THEN
3243 l_v_fee_type := null;
3244 l_n_invoice_id := null;
3245 l_v_message_text := null;
3246 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3247 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd5,
3248 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3249 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3250 p_v_fee_type => l_v_fee_type,
3251 p_n_invoice_id => l_n_invoice_id,
3252 p_v_message_name => l_v_message_text);
3253 IF l_v_message_text IS NOT NULL THEN
3254 l_b_val_err := TRUE;
3255 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3256 l_t_rc_tab(l_n_cntr).batch_name,
3257 l_t_rc_tab(l_n_cntr).item_number);
3258 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3259 fnd_file.put_line(fnd_file.log,
3260 l_v_message_text);
3261 ELSE
3262 IF l_b_val_err THEN
3263 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3264 ELSE
3265 l_t_ro_tab(l_n_cntr1).target_invoice_id5 := l_n_invoice_id;
3266 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3267 END IF;
3268 END IF;
3269 END IF;
3270
3271 IF l_t_ro_tab(l_n_cntr1).charge_cd6 IS NOT NULL THEN
3272 l_v_fee_type := null;
3273 l_n_invoice_id := null;
3274 l_v_message_text := null;
3275 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3276 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd6,
3277 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3278 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3279 p_v_fee_type => l_v_fee_type,
3280 p_n_invoice_id => l_n_invoice_id,
3281 p_v_message_name => l_v_message_text);
3282 IF l_v_message_text IS NOT NULL THEN
3283 l_b_val_err := TRUE;
3284 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3285 l_t_rc_tab(l_n_cntr).batch_name,
3286 l_t_rc_tab(l_n_cntr).item_number);
3287 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3288 fnd_file.put_line(fnd_file.log,
3289 l_v_message_text);
3290 ELSE
3291 IF l_b_val_err THEN
3292 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3293 ELSE
3294 l_t_ro_tab(l_n_cntr1).target_invoice_id6 := l_n_invoice_id;
3295 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3296 END IF;
3297 END IF;
3298 END IF;
3299
3300 IF l_t_ro_tab(l_n_cntr1).charge_cd7 IS NOT NULL THEN
3301 l_v_fee_type := null;
3302 l_n_invoice_id := null;
3303 l_v_message_text := null;
3304 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3305 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd7,
3306 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3307 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3308 p_v_fee_type => l_v_fee_type,
3309 p_n_invoice_id => l_n_invoice_id,
3310 p_v_message_name => l_v_message_text);
3311 IF l_v_message_text IS NOT NULL THEN
3312 l_b_val_err := TRUE;
3313 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3314 l_t_rc_tab(l_n_cntr).batch_name,
3315 l_t_rc_tab(l_n_cntr).item_number);
3316 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3317 fnd_file.put_line(fnd_file.log,
3318 l_v_message_text);
3319 ELSE
3320 IF l_b_val_err THEN
3321 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3322 ELSE
3323 l_t_ro_tab(l_n_cntr1).target_invoice_id7 := l_n_invoice_id;
3324 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3325 END IF;
3326 END IF;
3327 END IF;
3328
3329 IF l_t_ro_tab(l_n_cntr1).charge_cd8 IS NOT NULL THEN
3330 l_v_fee_type := null;
3331 l_n_invoice_id := null;
3332 l_v_message_text := null;
3333 l_t_rc_tab(l_n_cntr).eligible_to_apply_yn := 'Y';
3334 val_charge_number_for_app(p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd8,
3335 p_v_party_number => l_t_rc_tab(l_n_cntr).party_number,
3336 p_n_party_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3337 p_v_fee_type => l_v_fee_type,
3338 p_n_invoice_id => l_n_invoice_id,
3339 p_v_message_name => l_v_message_text);
3340 IF l_v_message_text IS NOT NULL THEN
3341 l_b_val_err := TRUE;
3342 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3343 l_t_rc_tab(l_n_cntr).batch_name,
3344 l_t_rc_tab(l_n_cntr).item_number);
3345 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3346 fnd_file.put_line(fnd_file.log,
3347 l_v_message_text);
3348 ELSE
3349 IF l_b_val_err THEN
3350 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3351 ELSE
3352 l_t_ro_tab(l_n_cntr1).target_invoice_id8 := l_n_invoice_id;
3353 l_t_ro_tab(l_n_cntr1).eligible_to_apply_yn := 'Y';
3354 END IF;
3355 END IF;
3356 END IF;
3357
3358 IF l_b_val_err THEN
3359 g_n_retcode := 1;
3360 l_t_ro_tab(l_n_cntr1).record_status := g_v_error;
3361 END IF;
3362 END IF;
3363 END IF;
3364 END LOOP;
3365 END IF;
3366
3367 -- If there is any validation error, then update the receipt record status to Error
3368 -- If the test run is No and the process has been invoked from Interface Process, then
3369 -- create record in the Lockbox Error tables.
3370 -- Remove the erroneous records from the PL/SQL table.
3371 IF l_b_val_err THEN
3372 g_n_retcode := 1;
3373 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3374 IF p_v_test_run = 'N' AND p_v_invoked_from = 'I' THEN
3375 l_n_rec_err_id := null;
3376 insert_lb_errors(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
3377 p_n_receipt_error_id => l_n_rec_err_id);
3378 END IF;
3379 l_n_cntr1 := null;
3380 IF l_t_ro_tab.COUNT > 0 THEN
3381 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
3382 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
3383 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
3384 IF p_v_test_run = 'N' AND p_v_invoked_from = 'I' THEN
3385 insert_lb_ovfl_errors(p_r_receipt_rec => l_t_ro_tab(l_n_cntr1),
3386 p_n_receipt_error_id => l_n_rec_err_id);
3387 END IF;
3388 l_t_ro_tab.DELETE(l_n_cntr1);
3389 END IF;
3390 END IF;
3391 END LOOP;
3392 END IF;
3393 l_t_rc_tab.DELETE(l_n_cntr);
3394 l_n_rc_cntr := l_t_rc_tab.COUNT;
3395 l_n_ro_cntr := l_t_ro_tab.COUNT;
3396 ELSE
3397
3398 -- Else if there is no error, then
3399 BEGIN
3400 SAVEPOINT SP_LOCKBOX_TYPE2;
3401 l_n_credit_id := null;
3402 l_v_status := null;
3403 l_v_message_text := null;
3404
3405 IF l_v_credit_class <> g_v_inst_payment THEN
3406 l_n_cr_type_id_api := l_t_rc_tab(l_n_cntr).mapped_credit_type_id;
3407 l_n_amount_api := l_t_rc_tab(l_n_cntr).receipt_amt;
3408 ELSE
3409 -- If Credit Class is Installment Payments then carry on following validations.
3410
3411 -- Check if the person is on Active Payment Plan or Not.
3412 igs_fi_gen_008.get_plan_details ( p_n_person_id => l_t_rc_tab(l_n_cntr).mapped_party_id,
3413 p_n_act_plan_id => l_n_act_plan_id,
3414 p_v_act_plan_name => l_v_act_plan_name );
3415 IF l_v_act_plan_name IS NULL THEN
3416 -- If the person is not on any active Payment Plan then check for existence of Defauly Credit Type Id for the Lockbox in the context
3417 OPEN cur_lb_crt (l_t_rc_tab(l_n_cntr).lockbox_name);
3418 FETCH cur_lb_crt INTO l_n_dflt_cr_type_id;
3419 CLOSE cur_lb_crt;
3420 IF l_n_dflt_cr_type_id IS NULL THEN
3421 -- If Lockbox does not have Default Credit Type associated, then log error.
3422 l_b_val_err := TRUE;
3423 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3424 l_t_rc_tab(l_n_cntr).batch_name,
3425 l_t_rc_tab(l_n_cntr).item_number);
3426 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3427
3428 fnd_message.set_name( 'IGS', 'IGS_FI_PP_NO_DEF_CRT' );
3429 fnd_message.set_token( 'LOCKBOX_NAME', l_t_rc_tab(l_n_cntr).lockbox_name );
3430 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3431 ELSE
3432 -- If Default Credit Type is present then proceed with credit creation.
3433 l_n_cr_type_id_api := l_n_dflt_cr_type_id;
3434 l_t_rc_tab(l_n_cntr).mapped_credit_type_id := l_n_dflt_cr_type_id;
3435 l_n_amount_api := l_t_rc_tab(l_n_cntr).receipt_amt;
3436 END IF;
3437 ELSE
3438 -- If Person is on Active Payment Plan, get the plan balance
3439 l_n_plan_balance := igs_fi_gen_008.get_plan_balance( p_n_act_plan_id => l_n_act_plan_id,
3440 p_d_effective_date => NULL );
3441
3442 -- Check the difference between Receipt Amount of the Receipt record and Plan Balance.
3443 l_n_diff_amount := NVL(l_t_rc_tab(l_n_cntr).receipt_amt, 0) - NVL(l_n_plan_balance, 0);
3444 IF l_n_diff_amount <= 0 THEN
3445 -- Create credit for the given amount with INSTALLMENT_PAYMENTS Credit Class.
3446 l_n_amount_api := l_t_rc_tab(l_n_cntr).receipt_amt;
3447 l_n_cr_type_id_api := l_t_rc_tab(l_n_cntr).mapped_credit_type_id;
3448 ELSE
3449 -- If the receipt amount is greater than plan balance,
3450 -- then, create normal credit record for difference amount using default credit type of lockbox.
3451 -- and Installment credit should be created for plan balance.
3452 OPEN cur_lb_crt (l_t_rc_tab(l_n_cntr).lockbox_name);
3453 FETCH cur_lb_crt INTO l_n_dflt_cr_type_id;
3454 CLOSE cur_lb_crt;
3455 IF l_n_dflt_cr_type_id IS NULL THEN
3456 -- If Lockbox does not have Default Credit Type associated, then log error.
3457 l_b_val_err := TRUE;
3458 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3459 l_t_rc_tab(l_n_cntr).batch_name,
3460 l_t_rc_tab(l_n_cntr).item_number);
3461 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3462
3463 fnd_message.set_name( 'IGS', 'IGS_FI_PP_NO_DEF_CRT' );
3464 fnd_message.set_token( 'LOCKBOX_NAME', l_t_rc_tab(l_n_cntr).lockbox_name );
3465 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
3466 ELSE
3467 -- If Default Credit Type is present then proceed with credit creation for the difference amount.
3468 l_n_cr_type_id_api := l_n_dflt_cr_type_id;
3469 l_n_amount_api := l_n_diff_amount;
3470
3471 -- Assign this extra amount and default credit type to l_t_rc_tab(l_n_cntr) so that this info is logged later on.
3472 l_t_rc_tab(l_n_cntr).balance_amount := l_n_amount_api;
3473 l_t_rc_tab(l_n_cntr).dflt_cr_type_id := l_n_dflt_cr_type_id;
3474
3475 -- Invoke the Credits API for creating the credit
3476 invoke_credits_api_pvt(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
3477 p_n_credit_type_id => l_n_cr_type_id_api, /* Default Credit Type */
3478 p_n_receipt_amt => l_n_amount_api, /* Difference Amount */
3479 p_n_credit_id => l_n_credit_id,
3480 p_v_status => l_v_status,
3481 p_v_message_text => l_v_message_text);
3482 IF l_v_status <> 'S' THEN
3483 l_b_val_err := TRUE;
3484 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3485 l_t_rc_tab(l_n_cntr).batch_name,
3486 l_t_rc_tab(l_n_cntr).item_number);
3487 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3488
3489 fnd_file.put_line(fnd_file.LOG, l_v_message_text);
3490 ELSE
3491 -- Even when status = 'S', there might be a message if holds release failed.
3492 l_b_val_err := FALSE;
3493 l_n_credit_id := NULL;
3494 l_v_status := NULL;
3495 l_v_message_text := NULL;
3496 l_n_cr_type_id_api := l_t_rc_tab(l_n_cntr).mapped_credit_type_id;
3497 l_n_amount_api := l_n_plan_balance;
3498 l_t_rc_tab(l_n_cntr).receipt_amt := l_n_plan_balance;
3499 END IF;
3500 END IF;
3501 END IF; -- End of checking the difference between receipt amount and plan balance.
3502 END IF; -- Checking for person on active payment plan.
3503 END IF; -- Check of Credit Class
3504
3505 IF NOT l_b_val_err THEN
3506 -- Invoke the Credits API for creating the credit
3507 invoke_credits_api_pvt(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
3508 p_n_credit_type_id => l_n_cr_type_id_api,
3509 p_n_receipt_amt => l_n_amount_api,
3510 p_n_credit_id => l_n_credit_id,
3511 p_v_status => l_v_status,
3512 p_v_message_text => l_v_message_text);
3513 IF l_v_status <> 'S' THEN
3514 -- If the credit API returns with an error, then log the error in the log file.
3515 l_b_val_err := TRUE;
3516 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3517 l_t_rc_tab(l_n_cntr).batch_name,
3518 l_t_rc_tab(l_n_cntr).item_number);
3519 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3520
3521 fnd_file.put_line(fnd_file.log,
3522 l_v_message_text);
3523 ELSE
3524 -- Even when status = 'S', there might be a message if holds release failed.
3525 l_b_val_err := FALSE;
3526 l_t_rc_tab(l_n_cntr).credit_id := l_n_credit_id;
3527 l_t_rc_tab(l_n_cntr).holds_released_yn := 'Y';
3528 -- Record status would be success since the record is imported
3529 -- irrespective of holds release failing
3530 l_t_rc_tab(l_n_cntr).record_status := g_v_success;
3531 IF l_v_message_text IS NOT NULL THEN
3532 -- Hence, log the message (Auto Release of Holds build)
3533 l_t_rc_tab(l_n_cntr).holds_released_yn := 'N';
3534 g_v_holds_message := l_v_message_text;
3535 END IF;
3536 END IF;
3537 END IF;
3538
3539 -- If there have been no errors, then
3540 IF NOT l_b_val_err THEN
3541
3542 -- If the eligible to apply flag is set to N, then record status is updated to success
3543 IF l_t_rc_tab(l_n_cntr).eligible_to_apply_yn = 'N' THEN
3544 l_t_rc_tab(l_n_cntr).record_status := g_v_success;
3545 ELSE
3546 -- Else if the eligible to apply flag is set to Y, then for each charge record,
3547 -- Invoke the application procedure. If the application procedure returns error then the whole receipt record is marked as error
3548 IF l_t_rc_tab(l_n_cntr).charge_cd1 IS NOT NULL THEN
3549 l_v_message_text := null;
3550 l_n_act_amnt_applied := null;
3551 l_n_app_id := null;
3552 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3553 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd1,
3554 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id1,
3555 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt1,
3556 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3557 p_n_act_amnt_applied => l_n_act_amnt_applied,
3558 p_n_application_id => l_n_app_id,
3559 p_v_err_message => l_v_message_text);
3560 IF l_n_app_id IS NULL THEN
3561 l_b_val_err := TRUE;
3562 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3563 l_t_rc_tab(l_n_cntr).batch_name,
3564 l_t_rc_tab(l_n_cntr).item_number);
3565 fnd_file.put_line(fnd_file.log,
3566 l_v_message_text);
3567 ELSE
3568 l_t_rc_tab(l_n_cntr).act_applied_amt1 := l_n_act_amnt_applied;
3569 END IF;
3570 END IF;
3571 IF l_t_rc_tab(l_n_cntr).charge_cd2 IS NOT NULL THEN
3572 l_v_message_text := null;
3573 l_n_act_amnt_applied := null;
3574 l_n_app_id := null;
3575 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3576 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd2,
3577 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id2,
3578 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt2,
3579 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3580 p_n_act_amnt_applied => l_n_act_amnt_applied,
3581 p_n_application_id => l_n_app_id,
3582 p_v_err_message => l_v_message_text);
3583 IF l_n_app_id IS NULL THEN
3584 l_b_val_err := TRUE;
3585 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3586 l_t_rc_tab(l_n_cntr).batch_name,
3587 l_t_rc_tab(l_n_cntr).item_number);
3588 fnd_file.put_line(fnd_file.log,
3589 l_v_message_text);
3590 ELSE
3591 l_t_rc_tab(l_n_cntr).act_applied_amt2 := l_n_act_amnt_applied;
3592 END IF;
3593 END IF;
3594
3595 IF l_t_rc_tab(l_n_cntr).charge_cd3 IS NOT NULL THEN
3596 l_v_message_text := null;
3597 l_n_act_amnt_applied := null;
3598 l_n_app_id := null;
3599 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3600 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd3,
3601 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id3,
3602 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt3,
3603 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3604 p_n_act_amnt_applied => l_n_act_amnt_applied,
3605 p_n_application_id => l_n_app_id,
3606 p_v_err_message => l_v_message_text);
3607 IF l_n_app_id IS NULL THEN
3608 l_b_val_err := TRUE;
3609 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3610 l_t_rc_tab(l_n_cntr).batch_name,
3611 l_t_rc_tab(l_n_cntr).item_number);
3612 fnd_file.put_line(fnd_file.log,
3613 l_v_message_text);
3614 ELSE
3615 l_t_rc_tab(l_n_cntr).act_applied_amt3 := l_n_act_amnt_applied;
3616 END IF;
3617 END IF;
3618 IF l_t_rc_tab(l_n_cntr).charge_cd4 IS NOT NULL THEN
3619 l_v_message_text := null;
3620 l_n_act_amnt_applied := null;
3621 l_n_app_id := null;
3622 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3623 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd4,
3624 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id4,
3625 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt4,
3626 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3627 p_n_act_amnt_applied => l_n_act_amnt_applied,
3628 p_n_application_id => l_n_app_id,
3629 p_v_err_message => l_v_message_text);
3630 IF l_n_app_id IS NULL THEN
3631 l_b_val_err := TRUE;
3632 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3633 l_t_rc_tab(l_n_cntr).batch_name,
3634 l_t_rc_tab(l_n_cntr).item_number);
3635 fnd_file.put_line(fnd_file.log,
3636 l_v_message_text);
3637 ELSE
3638 l_t_rc_tab(l_n_cntr).act_applied_amt4 := l_n_act_amnt_applied;
3639 END IF;
3640 END IF;
3641 IF l_t_rc_tab(l_n_cntr).charge_cd5 IS NOT NULL THEN
3642 l_v_message_text := null;
3643 l_n_act_amnt_applied := null;
3644 l_n_app_id := null;
3645 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3646 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd5,
3647 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id5,
3648 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt5,
3649 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3650 p_n_act_amnt_applied => l_n_act_amnt_applied,
3651 p_n_application_id => l_n_app_id,
3652 p_v_err_message => l_v_message_text);
3653 IF l_n_app_id IS NULL THEN
3654 l_b_val_err := TRUE;
3655 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3656 l_t_rc_tab(l_n_cntr).batch_name,
3657 l_t_rc_tab(l_n_cntr).item_number);
3658 fnd_file.put_line(fnd_file.log,
3659 l_v_message_text);
3660 ELSE
3661 l_t_rc_tab(l_n_cntr).act_applied_amt5 := l_n_act_amnt_applied;
3662 END IF;
3663 END IF;
3664 IF l_t_rc_tab(l_n_cntr).charge_cd6 IS NOT NULL THEN
3665 l_v_message_text := null;
3666 l_n_act_amnt_applied := null;
3667 l_n_app_id := null;
3668 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3669 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd6,
3670 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id6,
3671 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt6,
3672 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3673 p_n_act_amnt_applied => l_n_act_amnt_applied,
3674 p_n_application_id => l_n_app_id,
3675 p_v_err_message => l_v_message_text);
3676 IF l_n_app_id IS NULL THEN
3677 l_b_val_err := TRUE;
3678 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3679 l_t_rc_tab(l_n_cntr).batch_name,
3680 l_t_rc_tab(l_n_cntr).item_number);
3681 fnd_file.put_line(fnd_file.log,
3682 l_v_message_text);
3683 ELSE
3684 l_t_rc_tab(l_n_cntr).act_applied_amt6 := l_n_act_amnt_applied;
3685 END IF;
3686 END IF;
3687 IF l_t_rc_tab(l_n_cntr).charge_cd7 IS NOT NULL THEN
3688 l_v_message_text := null;
3689 l_n_act_amnt_applied := null;
3690 l_n_app_id := null;
3691 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3692 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd7,
3693 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id7,
3694 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt7,
3695 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3696 p_n_act_amnt_applied => l_n_act_amnt_applied,
3697 p_n_application_id => l_n_app_id,
3698 p_v_err_message => l_v_message_text);
3699 IF l_n_app_id IS NULL THEN
3700 l_b_val_err := TRUE;
3701 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3702 l_t_rc_tab(l_n_cntr).batch_name,
3703 l_t_rc_tab(l_n_cntr).item_number);
3704 fnd_file.put_line(fnd_file.log,
3705 l_v_message_text);
3706 ELSE
3707 l_t_rc_tab(l_n_cntr).act_applied_amt7 := l_n_act_amnt_applied;
3708 END IF;
3709 END IF;
3710 IF l_t_rc_tab(l_n_cntr).charge_cd8 IS NOT NULL THEN
3711 l_v_message_text := null;
3712 l_n_act_amnt_applied := null;
3713 l_n_app_id := null;
3714 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3715 p_v_charge_code => l_t_rc_tab(l_n_cntr).charge_cd8,
3716 p_n_target_invoice_id => l_t_rc_tab(l_n_cntr).target_invoice_id8,
3717 p_n_amount_applied => l_t_rc_tab(l_n_cntr).applied_amt8,
3718 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3719 p_n_act_amnt_applied => l_n_act_amnt_applied,
3720 p_n_application_id => l_n_app_id,
3721 p_v_err_message => l_v_message_text);
3722 IF l_n_app_id IS NULL THEN
3723 l_b_val_err := TRUE;
3724 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3725 l_t_rc_tab(l_n_cntr).batch_name,
3726 l_t_rc_tab(l_n_cntr).item_number);
3727 fnd_file.put_line(fnd_file.log,
3728 l_v_message_text);
3729 ELSE
3730 l_t_rc_tab(l_n_cntr).act_applied_amt8 := l_n_act_amnt_applied;
3731 END IF;
3732 END IF;
3733
3734 -- For each of the receipt overflow record for the reciept, invoke the Applications procedure. Incase of error
3735 -- the receipt and the receipt overflow would be marked as error
3736 IF l_t_ro_tab.COUNT > 0 THEN
3737 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
3738 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
3739 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
3740 IF l_t_ro_tab(l_n_cntr1).charge_cd1 IS NOT NULL THEN
3741 l_v_message_text := null;
3742 l_n_act_amnt_applied := null;
3743 l_n_app_id := null;
3744 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3745 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd1,
3746 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id1,
3747 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt1,
3748 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3749 p_n_act_amnt_applied => l_n_act_amnt_applied,
3750 p_n_application_id => l_n_app_id,
3751 p_v_err_message => l_v_message_text);
3752 IF l_n_app_id IS NULL THEN
3753 l_b_val_err := TRUE;
3754 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3755 l_t_rc_tab(l_n_cntr).batch_name,
3756 l_t_rc_tab(l_n_cntr).item_number);
3757 fnd_file.put_line(fnd_file.log,
3758 l_v_message_text);
3759 ELSE
3760 l_t_ro_tab(l_n_cntr1).act_applied_amt1 := l_n_act_amnt_applied;
3761 END IF;
3762 END IF;
3763
3764 IF l_t_ro_tab(l_n_cntr1).charge_cd2 IS NOT NULL THEN
3765 l_v_message_text := null;
3766 l_n_act_amnt_applied := null;
3767 l_n_app_id := null;
3768 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3769 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd2,
3770 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id2,
3771 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt2,
3772 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3773 p_n_act_amnt_applied => l_n_act_amnt_applied,
3774 p_n_application_id => l_n_app_id,
3775 p_v_err_message => l_v_message_text);
3776 IF l_n_app_id IS NULL THEN
3777 l_b_val_err := TRUE;
3778 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3779 l_t_rc_tab(l_n_cntr).batch_name,
3780 l_t_rc_tab(l_n_cntr).item_number);
3781 fnd_file.put_line(fnd_file.log,
3782 l_v_message_text);
3783 ELSE
3784 l_t_ro_tab(l_n_cntr1).act_applied_amt2 := l_n_act_amnt_applied;
3785 END IF;
3786 END IF;
3787
3788 IF l_t_ro_tab(l_n_cntr1).charge_cd3 IS NOT NULL THEN
3789 l_v_message_text := null;
3790 l_n_act_amnt_applied := null;
3791 l_n_app_id := null;
3792 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3793 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd3,
3794 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id3,
3795 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt3,
3796 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3797 p_n_act_amnt_applied => l_n_act_amnt_applied,
3798 p_n_application_id => l_n_app_id,
3799 p_v_err_message => l_v_message_text);
3800 IF l_n_app_id IS NULL THEN
3801 l_b_val_err := TRUE;
3802 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3803 l_t_rc_tab(l_n_cntr).batch_name,
3804 l_t_rc_tab(l_n_cntr).item_number);
3805 fnd_file.put_line(fnd_file.log,
3806 l_v_message_text);
3807 ELSE
3808 l_t_ro_tab(l_n_cntr1).act_applied_amt3 := l_n_act_amnt_applied;
3809 END IF;
3810 END IF;
3811
3812 IF l_t_ro_tab(l_n_cntr1).charge_cd4 IS NOT NULL THEN
3813 l_v_message_text := null;
3814 l_n_act_amnt_applied := null;
3815 l_n_app_id := null;
3816 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3817 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd1,
3818 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id4,
3819 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt4,
3820 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3821 p_n_act_amnt_applied => l_n_act_amnt_applied,
3822 p_n_application_id => l_n_app_id,
3823 p_v_err_message => l_v_message_text);
3824 IF l_n_app_id IS NULL THEN
3825 l_b_val_err := TRUE;
3826 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3827 l_t_rc_tab(l_n_cntr).batch_name,
3828 l_t_rc_tab(l_n_cntr).item_number);
3829 fnd_file.put_line(fnd_file.log,
3830 l_v_message_text);
3831 ELSE
3832 l_t_ro_tab(l_n_cntr1).act_applied_amt4 := l_n_act_amnt_applied;
3833 END IF;
3834 END IF;
3835
3836 IF l_t_ro_tab(l_n_cntr1).charge_cd5 IS NOT NULL THEN
3837 l_v_message_text := null;
3838 l_n_act_amnt_applied := null;
3839 l_n_app_id := null;
3840 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3841 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd5,
3842 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id5,
3843 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt5,
3844 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3845 p_n_act_amnt_applied => l_n_act_amnt_applied,
3846 p_n_application_id => l_n_app_id,
3847 p_v_err_message => l_v_message_text);
3848 IF l_n_app_id IS NULL THEN
3849 l_b_val_err := TRUE;
3850 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3851 l_t_rc_tab(l_n_cntr).batch_name,
3852 l_t_rc_tab(l_n_cntr).item_number);
3853 fnd_file.put_line(fnd_file.log,
3854 l_v_message_text);
3855 ELSE
3856 l_t_ro_tab(l_n_cntr1).act_applied_amt5 := l_n_act_amnt_applied;
3857 END IF;
3858 END IF;
3859
3860 IF l_t_ro_tab(l_n_cntr1).charge_cd6 IS NOT NULL THEN
3861 l_v_message_text := null;
3862 l_n_act_amnt_applied := null;
3863 l_n_app_id := null;
3864 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3865 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd6,
3866 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id6,
3867 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt6,
3868 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3869 p_n_act_amnt_applied => l_n_act_amnt_applied,
3870 p_n_application_id => l_n_app_id,
3871 p_v_err_message => l_v_message_text);
3872 IF l_n_app_id IS NULL THEN
3873 l_b_val_err := TRUE;
3874 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3875 l_t_rc_tab(l_n_cntr).batch_name,
3876 l_t_rc_tab(l_n_cntr).item_number);
3877 fnd_file.put_line(fnd_file.log,
3878 l_v_message_text);
3879 ELSE
3880 l_t_ro_tab(l_n_cntr1).act_applied_amt6 := l_n_act_amnt_applied;
3881 END IF;
3882 END IF;
3883 IF l_t_ro_tab(l_n_cntr1).charge_cd7 IS NOT NULL THEN
3884 l_v_message_text := null;
3885 l_n_act_amnt_applied := null;
3886 l_n_app_id := null;
3887 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3888 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd7,
3889 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id7,
3890 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt7,
3891 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3892 p_n_act_amnt_applied => l_n_act_amnt_applied,
3893 p_n_application_id => l_n_app_id,
3894 p_v_err_message => l_v_message_text);
3895 IF l_n_app_id IS NULL THEN
3896 l_b_val_err := TRUE;
3897 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3898 l_t_rc_tab(l_n_cntr).batch_name,
3899 l_t_rc_tab(l_n_cntr).item_number);
3900 fnd_file.put_line(fnd_file.log,
3901 l_v_message_text);
3902 ELSE
3903 l_t_ro_tab(l_n_cntr1).act_applied_amt7 := l_n_act_amnt_applied;
3904 END IF;
3905 END IF;
3906 IF l_t_ro_tab(l_n_cntr1).charge_cd8 IS NOT NULL THEN
3907 l_v_message_text := null;
3908 l_n_act_amnt_applied := null;
3909 l_n_app_id := null;
3910 invoke_target_appl(p_n_credit_id => l_t_rc_tab(l_n_cntr).credit_id,
3911 p_v_charge_code => l_t_ro_tab(l_n_cntr1).charge_cd8,
3912 p_n_target_invoice_id => l_t_ro_tab(l_n_cntr1).target_invoice_id8,
3913 p_n_amount_applied => l_t_ro_tab(l_n_cntr1).applied_amt8,
3914 p_d_gl_date => l_t_rc_tab(l_n_cntr).gl_date,
3915 p_n_act_amnt_applied => l_n_act_amnt_applied,
3916 p_n_application_id => l_n_app_id,
3917 p_v_err_message => l_v_message_text);
3918 IF l_n_app_id IS NULL THEN
3919 l_b_val_err := TRUE;
3920 log_type2_err(l_t_rc_tab(l_n_cntr).lockbox_name,
3921 l_t_rc_tab(l_n_cntr).batch_name,
3922 l_t_rc_tab(l_n_cntr).item_number);
3923 fnd_file.put_line(fnd_file.log,
3924 l_v_message_text);
3925 ELSE
3926 l_t_ro_tab(l_n_cntr1).act_applied_amt8 := l_n_act_amnt_applied;
3927 END IF;
3928 END IF;
3929 END IF;
3930 END IF;
3931 END LOOP;
3932 END IF;
3933
3934 IF l_b_val_err THEN
3935 ROLLBACK TO SP_LOCKBOX_TYPE2;
3936 l_t_rc_tab(l_n_cntr).record_status := g_v_error;
3937 ELSE
3938 l_t_rc_tab(l_n_cntr).record_status := g_v_success;
3939 END IF;
3940 END IF;
3941 END IF;
3942 EXCEPTION
3943 WHEN OTHERS THEN
3944 l_b_val_err := TRUE;
3945 ROLLBACK TO SP_LOCKBOX_TYPE2;
3946 END;
3947
3948 -- If there have been any errors, then
3949 -- If the test run is No and the process has been invoked from Interface Process, then
3950 -- create record in the Lockbox Error tables.
3951 -- Remove the erroneous records from the PL/SQL table.
3952 IF l_b_val_err THEN
3953 g_n_retcode := 1;
3954 IF p_v_test_run = 'N' AND p_v_invoked_from = 'I' THEN
3955 l_n_rec_err_id := null;
3956 insert_lb_errors(p_r_receipt_rec => l_t_rc_tab(l_n_cntr),
3957 p_n_receipt_error_id => l_n_rec_err_id);
3958 END IF;
3959 l_n_cntr1 := null;
3960 IF l_t_ro_tab.COUNT > 0 THEN
3961 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
3962 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
3963 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
3964 IF p_v_test_run = 'N' AND p_v_invoked_from = 'I' THEN
3965 insert_lb_ovfl_errors(p_r_receipt_rec => l_t_ro_tab(l_n_cntr1),
3966 p_n_receipt_error_id => l_n_rec_err_id);
3967 END IF;
3968 l_t_ro_tab.DELETE(l_n_cntr1);
3969 END IF;
3970 END IF;
3971 END LOOP;
3972 END IF;
3973 l_t_rc_tab.DELETE(l_n_cntr);
3974 l_n_rc_cntr := l_t_rc_tab.COUNT;
3975 l_n_ro_cntr := l_t_ro_tab.COUNT;
3976 END IF;
3977 END IF;
3978 l_b_val_err := FALSE;
3979 END IF; -- End if for check of EXISTS
3980 END LOOP;
3981 END IF;
3982
3983 IF l_t_rc_tab.COUNT = 0 THEN
3984 fnd_file.put_line(fnd_file.log,
3985 g_v_line_sep);
3986 END IF;
3987
3988
3989 -- Following logic writes the successfully imported records details in the
3990 -- concurrent manager log file
3991
3992 l_b_rec_succ := FALSE;
3993 IF l_t_rc_tab.COUNT > 0 THEN
3994 -- All erraneous records are deleted from PL/SQL table above, so l_t_rc_tab contains all success records.
3995 l_b_rec_succ := TRUE;
3996 fnd_file.new_line(fnd_file.log);
3997 fnd_file.put_line(fnd_file.log,
3998 g_v_line_sep);
3999 fnd_message.set_name('IGS',
4000 'IGS_FI_REC_IMP_SUCC');
4001 fnd_file.put_line(fnd_file.log,
4002 fnd_message.get);
4003 fnd_file.put_line(fnd_file.log,
4004 g_v_line_sep);
4005 l_n_rec_cntr := 0;
4006 FOR l_n_cntr IN l_t_rc_tab.FIRST..l_t_rc_tab.LAST LOOP
4007 IF l_t_rc_tab.EXISTS(l_n_cntr) THEN
4008 IF l_t_rc_tab(l_n_cntr).record_status = g_v_success THEN
4009 l_n_rec_cntr := l_n_rec_cntr + 1;
4010 l_n_rec_amnt_prc := NVL(l_n_rec_amnt_prc,0) +
4011 NVL(l_t_rc_tab(l_n_cntr).receipt_amt,0);
4012 log_line(g_v_label_lb_name,
4013 l_t_rc_tab(l_n_cntr).lockbox_name);
4014 log_line(g_v_label_batch,
4015 l_t_rc_tab(l_n_cntr).batch_name);
4016 log_line(g_v_label_item,
4017 l_t_rc_tab(l_n_cntr).item_number);
4018 log_line(g_v_label_status,
4019 g_v_label_success);
4020 log_line(g_v_label_party,
4021 l_t_rc_tab(l_n_cntr).party_number);
4022 log_line(g_v_label_rec_amnt,
4023 l_t_rc_tab(l_n_cntr).receipt_amt);
4024 log_line(g_v_label_cr_type,
4025 get_credit_type_name(l_t_rc_tab(l_n_cntr).mapped_credit_type_id));
4026 -- Log amount and credit type name if there is balance amount after creating Installment Credit.
4027 IF l_t_rc_tab(l_n_cntr).dflt_cr_type_id IS NOT NULL THEN
4028 log_line(g_v_label_bal_amnt,
4029 l_t_rc_tab(l_n_cntr).balance_amount);
4030 log_line(g_v_label_dflt_cr_type,
4031 get_credit_type_name(l_t_rc_tab(l_n_cntr).dflt_cr_type_id));
4032 END IF;
4033
4034 log_line(g_v_label_fee_prd,
4035 get_fee_period(l_t_rc_tab(l_n_cntr).mapped_fee_cal_type,
4036 l_t_rc_tab(l_n_cntr).mapped_fee_ci_sequence_number));
4037 log_line(g_v_label_gl_date,
4038 l_t_rc_tab(l_n_cntr).gl_date);
4039 log_line(g_v_label_adm_appl_num,
4040 l_t_rc_tab(l_n_cntr).adm_application_id);
4041 fnd_file.new_line(fnd_file.log);
4042
4043 IF l_t_rc_tab(l_n_cntr).charge_cd1 IS NOT NULL THEN
4044 log_line(g_v_label_charge_code,
4045 l_t_rc_tab(l_n_cntr).charge_cd1);
4046 log_line(g_v_label_bank_app_amt,
4047 l_t_rc_tab(l_n_cntr).applied_amt1);
4048 log_line(g_v_label_act_app_amt,
4049 l_t_rc_tab(l_n_cntr).act_applied_amt1);
4050 fnd_file.new_line(fnd_file.log);
4051 END IF;
4052
4053 IF l_t_rc_tab(l_n_cntr).charge_cd2 IS NOT NULL THEN
4054 log_line(g_v_label_charge_code,
4055 l_t_rc_tab(l_n_cntr).charge_cd2);
4056 log_line(g_v_label_bank_app_amt,
4057 l_t_rc_tab(l_n_cntr).applied_amt2);
4058 log_line(g_v_label_act_app_amt,
4059 l_t_rc_tab(l_n_cntr).act_applied_amt2);
4060 fnd_file.new_line(fnd_file.log);
4061 END IF;
4062
4063 IF l_t_rc_tab(l_n_cntr).charge_cd3 IS NOT NULL THEN
4064 log_line(g_v_label_charge_code,
4065 l_t_rc_tab(l_n_cntr).charge_cd3);
4066 log_line(g_v_label_bank_app_amt,
4067 l_t_rc_tab(l_n_cntr).applied_amt3);
4068 log_line(g_v_label_act_app_amt,
4069 l_t_rc_tab(l_n_cntr).act_applied_amt3);
4070 fnd_file.new_line(fnd_file.log);
4071 END IF;
4072
4073 IF l_t_rc_tab(l_n_cntr).charge_cd4 IS NOT NULL THEN
4074 log_line(g_v_label_charge_code,
4075 l_t_rc_tab(l_n_cntr).charge_cd4);
4076 log_line(g_v_label_bank_app_amt,
4077 l_t_rc_tab(l_n_cntr).applied_amt4);
4078 log_line(g_v_label_act_app_amt,
4079 l_t_rc_tab(l_n_cntr).act_applied_amt4);
4080 fnd_file.new_line(fnd_file.log);
4081 END IF;
4082
4083 IF l_t_rc_tab(l_n_cntr).charge_cd5 IS NOT NULL THEN
4084 log_line(g_v_label_charge_code,
4085 l_t_rc_tab(l_n_cntr).charge_cd5);
4086 log_line(g_v_label_bank_app_amt,
4087 l_t_rc_tab(l_n_cntr).applied_amt5);
4088 log_line(g_v_label_act_app_amt,
4089 l_t_rc_tab(l_n_cntr).act_applied_amt5);
4090 fnd_file.new_line(fnd_file.log);
4091 END IF;
4092
4093 IF l_t_rc_tab(l_n_cntr).charge_cd6 IS NOT NULL THEN
4094 log_line(g_v_label_charge_code,
4095 l_t_rc_tab(l_n_cntr).charge_cd6);
4096 log_line(g_v_label_bank_app_amt,
4097 l_t_rc_tab(l_n_cntr).applied_amt6);
4098 log_line(g_v_label_act_app_amt,
4099 l_t_rc_tab(l_n_cntr).act_applied_amt6);
4100 fnd_file.new_line(fnd_file.log);
4101 END IF;
4102
4103 IF l_t_rc_tab(l_n_cntr).charge_cd7 IS NOT NULL THEN
4104 log_line(g_v_label_charge_code,
4105 l_t_rc_tab(l_n_cntr).charge_cd7);
4106 log_line(g_v_label_bank_app_amt,
4107 l_t_rc_tab(l_n_cntr).applied_amt7);
4108 log_line(g_v_label_act_app_amt,
4109 l_t_rc_tab(l_n_cntr).act_applied_amt7);
4110 fnd_file.new_line(fnd_file.log);
4111 END IF;
4112
4113 IF l_t_rc_tab(l_n_cntr).charge_cd8 IS NOT NULL THEN
4114 log_line(g_v_label_charge_code,
4115 l_t_rc_tab(l_n_cntr).charge_cd8);
4116 log_line(g_v_label_bank_app_amt,
4117 l_t_rc_tab(l_n_cntr).applied_amt8);
4118 log_line(g_v_label_act_app_amt,
4119 l_t_rc_tab(l_n_cntr).act_applied_amt8);
4120 fnd_file.new_line(fnd_file.log);
4121 END IF;
4122
4123 l_n_cntr1 := 0;
4124 IF l_t_ro_tab.COUNT > 0 THEN
4125 FOR l_n_cntr1 IN l_t_ro_tab.FIRST..l_t_ro_tab.LAST LOOP
4126 IF l_t_ro_tab.EXISTS(l_n_cntr1) THEN
4127 IF l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr THEN
4128 IF l_t_ro_tab(l_n_cntr1).charge_cd1 IS NOT NULL THEN
4129 log_line(g_v_label_charge_code,
4130 l_t_ro_tab(l_n_cntr1).charge_cd1);
4131 log_line(g_v_label_bank_app_amt,
4132 l_t_ro_tab(l_n_cntr1).applied_amt1);
4133 log_line(g_v_label_act_app_amt,
4134 l_t_ro_tab(l_n_cntr1).act_applied_amt1);
4135 fnd_file.new_line(fnd_file.log);
4136 END IF;
4137
4138 IF l_t_ro_tab(l_n_cntr1).charge_cd2 IS NOT NULL THEN
4139 log_line(g_v_label_charge_code,
4140 l_t_ro_tab(l_n_cntr1).charge_cd2);
4141 log_line(g_v_label_bank_app_amt,
4142 l_t_ro_tab(l_n_cntr1).applied_amt2);
4143 log_line(g_v_label_act_app_amt,
4144 l_t_ro_tab(l_n_cntr1).act_applied_amt2);
4145 fnd_file.new_line(fnd_file.log);
4146 END IF;
4147
4148 IF l_t_ro_tab(l_n_cntr1).charge_cd3 IS NOT NULL THEN
4149 log_line(g_v_label_charge_code,
4150 l_t_ro_tab(l_n_cntr1).charge_cd3);
4151 log_line(g_v_label_bank_app_amt,
4152 l_t_ro_tab(l_n_cntr1).applied_amt3);
4153 log_line(g_v_label_act_app_amt,
4154 l_t_ro_tab(l_n_cntr1).act_applied_amt3);
4155 fnd_file.new_line(fnd_file.log);
4156 END IF;
4157
4158 IF l_t_ro_tab(l_n_cntr1).charge_cd4 IS NOT NULL THEN
4159 log_line(g_v_label_charge_code,
4160 l_t_ro_tab(l_n_cntr1).charge_cd4);
4161 log_line(g_v_label_bank_app_amt,
4162 l_t_ro_tab(l_n_cntr1).applied_amt4);
4163 log_line(g_v_label_act_app_amt,
4164 l_t_ro_tab(l_n_cntr1).act_applied_amt4);
4165 fnd_file.new_line(fnd_file.log);
4166 END IF;
4167
4168 IF l_t_ro_tab(l_n_cntr1).charge_cd5 IS NOT NULL THEN
4169 log_line(g_v_label_charge_code,
4170 l_t_ro_tab(l_n_cntr1).charge_cd5);
4171 log_line(g_v_label_bank_app_amt,
4172 l_t_ro_tab(l_n_cntr1).applied_amt5);
4173 log_line(g_v_label_act_app_amt,
4174 l_t_ro_tab(l_n_cntr1).act_applied_amt5);
4175 fnd_file.new_line(fnd_file.log);
4176 END IF;
4177
4178 IF l_t_ro_tab(l_n_cntr1).charge_cd6 IS NOT NULL THEN
4179 log_line(g_v_label_charge_code,
4180 l_t_ro_tab(l_n_cntr1).charge_cd6);
4181 log_line(g_v_label_bank_app_amt,
4182 l_t_ro_tab(l_n_cntr1).applied_amt6);
4183 log_line(g_v_label_act_app_amt,
4184 l_t_ro_tab(l_n_cntr1).act_applied_amt6);
4185 fnd_file.new_line(fnd_file.log);
4186 END IF;
4187
4188 IF l_t_ro_tab(l_n_cntr1).charge_cd7 IS NOT NULL THEN
4189 log_line(g_v_label_charge_code,
4190 l_t_ro_tab(l_n_cntr1).charge_cd7);
4191 log_line(g_v_label_bank_app_amt,
4192 l_t_ro_tab(l_n_cntr1).applied_amt7);
4193 log_line(g_v_label_act_app_amt,
4194 l_t_ro_tab(l_n_cntr1).act_applied_amt7);
4195 fnd_file.new_line(fnd_file.log);
4196 END IF;
4197
4198 IF l_t_ro_tab(l_n_cntr1).charge_cd8 IS NOT NULL THEN
4199 log_line(g_v_label_charge_code,
4200 l_t_ro_tab(l_n_cntr1).charge_cd8);
4201 log_line(g_v_label_bank_app_amt,
4202 l_t_ro_tab(l_n_cntr1).applied_amt8);
4203 log_line(g_v_label_act_app_amt,
4204 l_t_ro_tab(l_n_cntr1).act_applied_amt8);
4205 fnd_file.new_line(fnd_file.log);
4206 END IF;
4207 END IF; -- End if for l_t_ro_tab(l_n_cntr1).receipt_number = l_n_cntr
4208 END IF; -- End if for l_t_ro_tab.EXISTS(l_n_cntr1)
4209 END LOOP;
4210 END IF; -- End if for l_t_ro_tab.COUNT > 0
4211
4212 -- If holds could not be released after credit creation,
4213 -- display message conveying the same.
4214 IF l_t_rc_tab(l_n_cntr).holds_released_yn = 'N' THEN
4215 fnd_file.put_line(fnd_file.log,g_v_holds_message);
4216 fnd_file.new_line(fnd_file.log);
4217 END IF;
4218
4219 END IF; -- End if for l_t_rc_tab(l_n_cntr).record_status = g_v_success
4220
4221
4222 -- If the Procedure has been invoked from the Error resolution process and the
4223 -- test run is No, then delete the successful records from the lockbox error tables
4224 IF p_v_invoked_from = 'E' AND p_v_test_run = 'N' THEN
4225 delete_err_success(p_r_rowid => l_t_rc_tab(l_n_cntr).row_id);
4226 END IF;
4227 END IF; -- End if for l_t_rc_tab.EXISTS(l_n_cntr)
4228 END LOOP;
4229 END IF;
4230
4231 -- Log the final summary of transactions
4232 fnd_file.new_line(fnd_file.log,2);
4233 log_line(g_v_label_num_rec,
4234 NVL(l_n_rec_cntr,0));
4235 log_line(g_v_label_cur_rec,
4236 NVL(l_n_rec_amnt_prc,0));
4237 fnd_file.put_line(fnd_file.log,
4238 g_v_line_sep);
4239
4240 l_n_rec_cntr := 0;
4241 l_n_rec_amnt_prc := 0;
4242
4243 -- Setting the g_n_retcode appropriately
4244 IF p_v_invoked_from = 'E' THEN
4245 IF NOT l_b_rec_succ THEN
4246 g_n_retcode := 2;
4247 ELSE
4248 IF g_n_retcode = 0 THEN
4249 g_n_retcode := 0;
4250 ELSE
4251 g_n_retcode := 1;
4252 END IF;
4253 END IF;
4254 END IF;
4255 END valtype2_and_import_rects;
4256
4257 PROCEDURE populate_err_rec( p_v_lockbox_name IN igs_fi_lockboxes.lockbox_name%TYPE,
4258 p_t_err_rec_tab OUT NOCOPY lb_receipt_tab) AS
4259
4260 /******************************************************************
4261 Created By : Shirish Tatikonda
4262 Date Created By : 12-Jun-2003
4263 Purpose : Function for populating the Error Reciept/Overflow records
4264
4265 Known limitations,enhancements,remarks:
4266 Change History
4267 Who When What
4268 ***************************************************************** */
4269 CURSOR cur_err_rec( cp_v_lockbox_name igs_fi_lockboxes.lockbox_name%TYPE ) IS
4270 SELECT ROWID row_id, lre.*
4271 FROM igs_fi_lb_rect_errs lre
4272 WHERE lockbox_name = cp_v_lockbox_name
4273 FOR UPDATE NOWAIT;
4274
4275 l_err_rec cur_err_rec%ROWTYPE;
4276
4277 CURSOR cur_err_ro( cp_n_lre_id igs_fi_lb_ovfl_errs.lockbox_receipt_error_id%TYPE ) IS
4278 SELECT ROWID row_id, loe.*
4279 FROM igs_fi_lb_ovfl_errs loe
4280 WHERE lockbox_receipt_error_id = cp_n_lre_id
4281 FOR UPDATE NOWAIT;
4282
4283 l_err_ro cur_err_ro%ROWTYPE;
4284 l_n_cntr NUMBER := 0;
4285
4286 BEGIN
4287
4288 -- Populate Receipt Records from igs_fi_lb_rect_errs into PL/SQL Table.
4289 FOR l_err_rec IN cur_err_rec( p_v_lockbox_name ) LOOP
4290 l_n_cntr := l_n_cntr + 1;
4291
4292 p_t_err_rec_tab(l_n_cntr).record_status := g_v_todo;
4293 p_t_err_rec_tab(l_n_cntr).row_id := l_err_rec.row_id;
4294 p_t_err_rec_tab(l_n_cntr).lockbox_interface_id := l_err_rec.lockbox_interface_id;
4295 p_t_err_rec_tab(l_n_cntr).system_record_identifier := g_v_receipt;
4296 p_t_err_rec_tab(l_n_cntr).deposit_date := l_err_rec.deposit_date;
4297 p_t_err_rec_tab(l_n_cntr).lockbox_name := l_err_rec.lockbox_name;
4298 p_t_err_rec_tab(l_n_cntr).batch_name := l_err_rec.batch_name;
4299 p_t_err_rec_tab(l_n_cntr).item_number := l_err_rec.item_number;
4300 p_t_err_rec_tab(l_n_cntr).receipt_amt := l_err_rec.receipt_amt;
4301 p_t_err_rec_tab(l_n_cntr).check_cd := l_err_rec.check_cd;
4302 p_t_err_rec_tab(l_n_cntr).party_number := l_err_rec.party_number;
4303 p_t_err_rec_tab(l_n_cntr).mapped_party_id := NULL;
4304 p_t_err_rec_tab(l_n_cntr).payer_name := l_err_rec.payer_name;
4305 p_t_err_rec_tab(l_n_cntr).credit_type_cd := l_err_rec.credit_type_cd;
4306 p_t_err_rec_tab(l_n_cntr).mapped_credit_type_id := NULL;
4307 p_t_err_rec_tab(l_n_cntr).fee_cal_instance_cd := l_err_rec.fee_cal_instance_cd;
4308 p_t_err_rec_tab(l_n_cntr).mapped_fee_cal_type := NULL;
4309 p_t_err_rec_tab(l_n_cntr).mapped_fee_ci_sequence_number := NULL;
4310 p_t_err_rec_tab(l_n_cntr).charge_cd1 := l_err_rec.charge_cd1;
4311 p_t_err_rec_tab(l_n_cntr).charge_cd2 := l_err_rec.charge_cd2;
4312 p_t_err_rec_tab(l_n_cntr).charge_cd3 := l_err_rec.charge_cd3;
4313 p_t_err_rec_tab(l_n_cntr).charge_cd4 := l_err_rec.charge_cd4;
4314 p_t_err_rec_tab(l_n_cntr).charge_cd5 := l_err_rec.charge_cd5;
4315 p_t_err_rec_tab(l_n_cntr).charge_cd6 := l_err_rec.charge_cd6;
4316 p_t_err_rec_tab(l_n_cntr).charge_cd7 := l_err_rec.charge_cd7;
4317 p_t_err_rec_tab(l_n_cntr).charge_cd8 := l_err_rec.charge_cd8;
4318 p_t_err_rec_tab(l_n_cntr).applied_amt1 := l_err_rec.applied_amt1;
4319 p_t_err_rec_tab(l_n_cntr).applied_amt2 := l_err_rec.applied_amt2;
4320 p_t_err_rec_tab(l_n_cntr).applied_amt3 := l_err_rec.applied_amt3;
4321 p_t_err_rec_tab(l_n_cntr).applied_amt4 := l_err_rec.applied_amt4;
4322 p_t_err_rec_tab(l_n_cntr).applied_amt5 := l_err_rec.applied_amt5;
4323 p_t_err_rec_tab(l_n_cntr).applied_amt6 := l_err_rec.applied_amt6;
4324 p_t_err_rec_tab(l_n_cntr).applied_amt7 := l_err_rec.applied_amt7;
4325 p_t_err_rec_tab(l_n_cntr).applied_amt8 := l_err_rec.applied_amt8;
4326 p_t_err_rec_tab(l_n_cntr).adm_application_id := l_err_rec.adm_application_id;
4327 p_t_err_rec_tab(l_n_cntr).attribute_category := l_err_rec.attribute_category;
4328 p_t_err_rec_tab(l_n_cntr).attribute1 := l_err_rec.attribute1;
4329 p_t_err_rec_tab(l_n_cntr).attribute2 := l_err_rec.attribute2;
4330 p_t_err_rec_tab(l_n_cntr).attribute3 := l_err_rec.attribute3;
4331 p_t_err_rec_tab(l_n_cntr).attribute4 := l_err_rec.attribute4;
4332 p_t_err_rec_tab(l_n_cntr).attribute5 := l_err_rec.attribute5;
4333 p_t_err_rec_tab(l_n_cntr).attribute6 := l_err_rec.attribute6;
4334 p_t_err_rec_tab(l_n_cntr).attribute7 := l_err_rec.attribute7;
4335 p_t_err_rec_tab(l_n_cntr).attribute8 := l_err_rec.attribute8;
4336 p_t_err_rec_tab(l_n_cntr).attribute9 := l_err_rec.attribute9;
4337 p_t_err_rec_tab(l_n_cntr).attribute10 := l_err_rec.attribute10;
4338 p_t_err_rec_tab(l_n_cntr).attribute11 := l_err_rec.attribute11;
4339 p_t_err_rec_tab(l_n_cntr).attribute12 := l_err_rec.attribute12;
4340 p_t_err_rec_tab(l_n_cntr).attribute13 := l_err_rec.attribute13;
4341 p_t_err_rec_tab(l_n_cntr).attribute14 := l_err_rec.attribute14;
4342 p_t_err_rec_tab(l_n_cntr).attribute15 := l_err_rec.attribute15;
4343 p_t_err_rec_tab(l_n_cntr).attribute16 := l_err_rec.attribute16;
4344 p_t_err_rec_tab(l_n_cntr).attribute17 := l_err_rec.attribute17;
4345 p_t_err_rec_tab(l_n_cntr).attribute18 := l_err_rec.attribute18;
4346 p_t_err_rec_tab(l_n_cntr).attribute19 := l_err_rec.attribute19;
4347 p_t_err_rec_tab(l_n_cntr).attribute20 := l_err_rec.attribute20;
4348 p_t_err_rec_tab(l_n_cntr).credit_id := NULL;
4349 p_t_err_rec_tab(l_n_cntr).gl_date := NULL;
4350 p_t_err_rec_tab(l_n_cntr).source_transaction_type := NULL;
4351 p_t_err_rec_tab(l_n_cntr).eligible_to_apply_yn := NULL;
4352 p_t_err_rec_tab(l_n_cntr).receipt_number := NULL;
4353
4354 -- Populate Receipt Overflow Records found for each Receipt Record from igs_fi_lb_ovfl_errs into PL/SQL Table.
4355 FOR l_err_ro IN cur_err_ro( l_err_rec.lockbox_receipt_error_id ) LOOP
4356 l_n_cntr := l_n_cntr + 1;
4357
4358 p_t_err_rec_tab(l_n_cntr).record_status := g_v_todo;
4359 p_t_err_rec_tab(l_n_cntr).row_id := l_err_ro.row_id;
4360 p_t_err_rec_tab(l_n_cntr).lockbox_interface_id := NULL;
4361 p_t_err_rec_tab(l_n_cntr).system_record_identifier := g_v_receipt_oflow;
4362 p_t_err_rec_tab(l_n_cntr).deposit_date := NULL;
4363 p_t_err_rec_tab(l_n_cntr).lockbox_name := l_err_rec.lockbox_name;
4364 p_t_err_rec_tab(l_n_cntr).batch_name := l_err_rec.batch_name;
4365 p_t_err_rec_tab(l_n_cntr).item_number := l_err_rec.item_number;
4366 p_t_err_rec_tab(l_n_cntr).receipt_amt := NULL;
4367 p_t_err_rec_tab(l_n_cntr).check_cd := NULL;
4368 p_t_err_rec_tab(l_n_cntr).party_number := NULL;
4369 p_t_err_rec_tab(l_n_cntr).mapped_party_id := NULL;
4370 p_t_err_rec_tab(l_n_cntr).payer_name := NULL;
4371 p_t_err_rec_tab(l_n_cntr).credit_type_cd := NULL;
4372 p_t_err_rec_tab(l_n_cntr).mapped_credit_type_id := NULL;
4373 p_t_err_rec_tab(l_n_cntr).fee_cal_instance_cd := NULL;
4374 p_t_err_rec_tab(l_n_cntr).mapped_fee_cal_type := NULL;
4375 p_t_err_rec_tab(l_n_cntr).mapped_fee_ci_sequence_number := NULL;
4376 p_t_err_rec_tab(l_n_cntr).charge_cd1 := l_err_ro.charge_cd1;
4377 p_t_err_rec_tab(l_n_cntr).charge_cd2 := l_err_ro.charge_cd2;
4378 p_t_err_rec_tab(l_n_cntr).charge_cd3 := l_err_ro.charge_cd3;
4379 p_t_err_rec_tab(l_n_cntr).charge_cd4 := l_err_ro.charge_cd4;
4380 p_t_err_rec_tab(l_n_cntr).charge_cd5 := l_err_ro.charge_cd5;
4381 p_t_err_rec_tab(l_n_cntr).charge_cd6 := l_err_ro.charge_cd6;
4382 p_t_err_rec_tab(l_n_cntr).charge_cd7 := l_err_ro.charge_cd7;
4383 p_t_err_rec_tab(l_n_cntr).charge_cd8 := l_err_ro.charge_cd8;
4384 p_t_err_rec_tab(l_n_cntr).applied_amt1 := l_err_ro.applied_amt1;
4385 p_t_err_rec_tab(l_n_cntr).applied_amt2 := l_err_ro.applied_amt2;
4386 p_t_err_rec_tab(l_n_cntr).applied_amt3 := l_err_ro.applied_amt3;
4387 p_t_err_rec_tab(l_n_cntr).applied_amt4 := l_err_ro.applied_amt4;
4388 p_t_err_rec_tab(l_n_cntr).applied_amt5 := l_err_ro.applied_amt5;
4389 p_t_err_rec_tab(l_n_cntr).applied_amt6 := l_err_ro.applied_amt6;
4390 p_t_err_rec_tab(l_n_cntr).applied_amt7 := l_err_ro.applied_amt7;
4391 p_t_err_rec_tab(l_n_cntr).applied_amt8 := l_err_ro.applied_amt8;
4392 p_t_err_rec_tab(l_n_cntr).adm_application_id := NULL;
4393 p_t_err_rec_tab(l_n_cntr).credit_id := NULL;
4394 p_t_err_rec_tab(l_n_cntr).gl_date := NULL;
4395 p_t_err_rec_tab(l_n_cntr).source_transaction_type := NULL;
4396 p_t_err_rec_tab(l_n_cntr).eligible_to_apply_yn := NULL;
4397 p_t_err_rec_tab(l_n_cntr).receipt_number := NULL;
4398 END LOOP;
4399 END LOOP;
4400
4401 END populate_err_rec;
4402
4403
4404 PROCEDURE import_interface_lockbox(errbuf OUT NOCOPY VARCHAR2,
4405 retcode OUT NOCOPY NUMBER,
4406 p_v_lockbox_name VARCHAR2,
4407 p_d_gl_date VARCHAR2,
4408 p_v_test_run VARCHAR2) AS
4409 /******************************************************************
4410 Created By : Amit Gairola
4411 Date Created By : 12-Jun-2003
4412 Purpose : Main procedure for Concurrent Process
4413
4414 Known limitations,enhancements,remarks:
4415 Change History
4416 Who When What
4417 ***************************************************************** */
4418 l_d_gl_date DATE;
4419 l_v_type1_status VARCHAR2(1);
4420 l_b_prc_err BOOLEAN := FALSE;
4421 e_resource_busy EXCEPTION;
4422
4423 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
4424 BEGIN
4425
4426 -- Create a savepoint
4427 SAVEPOINT SP_LOCKBOX_MAIN;
4428 retcode := 0;
4429
4430 l_d_gl_date := TRUNC(igs_ge_date.igsdate(p_d_gl_date));
4431
4432 -- Call the initialize procedure to initialize the global variables
4433 -- and the PL/SQL tables
4434 initialize;
4435
4436 -- Validate the parameters. Incase of error exit
4437 IF NOT validate_parameters(p_v_lockbox_name,
4438 l_d_gl_date,
4439 p_v_test_run) THEN
4440 retcode := 2;
4441 RETURN;
4442 END IF;
4443
4444 -- Populate the Lockbox Interface PL/SQL table
4445 IF populate_lb_interface(p_v_lockbox_name) THEN
4446 update_lbint_status(g_v_error);
4447 g_n_retcode := 2;
4448 l_b_prc_err := TRUE;
4449 END IF;
4450
4451 -- If there have been no errors and there are records in the Lockbox Interface
4452 -- PL/SQL table
4453 IF (NOT l_b_prc_err) AND g_b_rec_exists THEN
4454
4455 -- Call the procedure for type1 validations
4456 l_v_type1_status := validate_type1;
4457
4458 -- If the procedure returns an error, then the retcode is set to 2
4459 -- and the interface records are set to Error
4460 IF (l_v_type1_status='E') THEN
4461 update_lbint_status(g_v_error);
4462 g_n_retcode := 2;
4463 ELSIF (l_v_type1_status='S') THEN
4464 -- Else the interface records are set to Success
4465 update_lbint_status(g_v_success);
4466 END IF;
4467
4468 -- If type1 validations have been successful
4469 IF l_v_type1_status = 'S' THEN
4470
4471 -- Populate the Receipts PL/SQL table
4472 populate_lb_receipts;
4473
4474 -- Call the Type2 validations procedure
4475 valtype2_and_import_rects(p_t_lb_rec_tab => g_t_rec_tab,
4476 p_v_test_run => p_v_test_run,
4477 p_d_gl_date => l_d_gl_date,
4478 p_v_invoked_from => 'I');
4479 END IF;
4480 END IF;
4481
4482 retcode := g_n_retcode;
4483
4484 -- If the test run is 'Y', then rollback all the transactions
4485 IF p_v_test_run = 'Y' THEN
4486 ROLLBACK TO SP_LOCKBOX_MAIN;
4487
4488 -- If records exist then log the message than the Records have been rolled back
4489 IF g_b_rec_exists THEN
4490 fnd_message.set_name('IGS',
4491 'IGS_FI_PRC_TEST_RUN');
4492 fnd_file.put_line(fnd_file.log,
4493 fnd_message.get);
4494 ELSE
4495
4496 -- Else log no data found
4497 fnd_message.set_name('IGS',
4498 'IGS_GE_NO_DATA_FOUND');
4499 fnd_file.put_line(fnd_file.log,
4500 fnd_message.get);
4501 END IF;
4502 ELSE
4503
4504 -- If the test run is No and records exist, then commit
4505 IF g_b_rec_exists THEN
4506 COMMIT;
4507 ELSE
4508 -- Elsif records do not exist, then log no data found
4509 fnd_message.set_name('IGS',
4510 'IGS_GE_NO_DATA_FOUND');
4511 fnd_file.put_line(fnd_file.log,
4512 fnd_message.get);
4513 END IF;
4514 END IF;
4515
4516 -- Clear the global PL/SQL tables
4517 g_t_rec_tab.DELETE;
4518 g_lb_int_tab.DELETE;
4519 EXCEPTION
4520
4521 -- Handle the Locking exception
4522 WHEN e_resource_busy THEN
4523 ROLLBACK TO SP_LOCKBOX_MAIN;
4524 retcode := 2;
4525 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
4526 fnd_file.put_line(fnd_file.log,fnd_message.get());
4527 fnd_file.new_line(fnd_file.log);
4528 WHEN OTHERS THEN
4529 -- Handling the When Others Condition
4530 retcode := 2;
4531 ROLLBACK TO SP_LOCKBOX_MAIN;
4532 fnd_message.set_name('IGS',
4533 'IGS_GE_UNHANDLED_EXCEPTION');
4534 fnd_file.put_line(fnd_file.log,
4535 fnd_message.get||' - '||sqlerrm);
4536 END import_interface_lockbox;
4537
4538 PROCEDURE import_error_lockbox( errbuf OUT NOCOPY VARCHAR2,
4539 retcode OUT NOCOPY NUMBER,
4540 p_v_lockbox_name IN VARCHAR2,
4541 p_d_gl_date IN VARCHAR2,
4542 p_v_test_run IN VARCHAR2) AS
4543 /******************************************************************
4544 Created By : Shirish Tatikonda
4545 Date Created By : 12-Jun-2003
4546 Purpose : Main Procedure for Import Error Lockbox
4547
4548 Known limitations,enhancements,remarks:
4549 Change History
4550 Who When What
4551 ***************************************************************** */
4552 l_d_gl_date DATE;
4553 l_v_manage_account igs_fi_control.manage_accounts%TYPE;
4554 l_v_message_name fnd_new_messages.message_name%TYPE;
4555 l_t_err_rec_tab lb_receipt_tab;
4556 l_n_record_count NUMBER;
4557 e_resource_busy EXCEPTION;
4558
4559 PRAGMA EXCEPTION_INIT(e_resource_busy, -54);
4560 BEGIN
4561 -- Create a Savepoint
4562 SAVEPOINT SP_LOCKBOX_ERROR;
4563 retcode := 0;
4564
4565 l_d_gl_date := igs_ge_date.igsdate(p_d_gl_date);
4566 -- Call initialize to set all the global variables.
4567 initialize;
4568
4569 -- Log and Validate Parameters
4570 IF NOT validate_parameters(p_v_lockbox_name,
4571 l_d_gl_date,
4572 p_v_test_run) THEN
4573 retcode := 2;
4574 RETURN;
4575 END IF;
4576
4577 -- Populate Receipt and Receipt Overflow records
4578 populate_err_rec( p_v_lockbox_name, l_t_err_rec_tab );
4579 -- Fetch the number of records found
4580 l_n_record_count := l_t_err_rec_tab.COUNT;
4581
4582 -- Pass populated records for Type II validations
4583 IF l_n_record_count > 0 THEN
4584 valtype2_and_import_rects(p_t_lb_rec_tab => l_t_err_rec_tab,
4585 p_v_test_run => p_v_test_run,
4586 p_d_gl_date => l_d_gl_date,
4587 p_v_invoked_from => 'E');
4588 END IF;
4589
4590 retcode := g_n_retcode;
4591
4592 IF( l_n_record_count > 0 ) THEN
4593 -- If Test Run is Y, log generic message saying that all transactions are rolled back
4594 IF( p_v_test_run = 'Y' ) THEN
4595 ROLLBACK TO SP_LOCKBOX_ERROR;
4596 fnd_message.set_name('IGS', 'IGS_FI_PRC_TEST_RUN');
4597 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4598 ELSE
4599 -- If Test Run is N, commit.
4600 COMMIT;
4601 END IF;
4602 ELSE
4603 -- If no records found for processing log the message saying so.
4604 fnd_message.set_name('IGS', 'IGS_GE_NO_DATA_FOUND');
4605 fnd_file.put_line(fnd_file.LOG, fnd_message.get);
4606 END IF;
4607
4608 l_t_err_rec_tab.DELETE;
4609
4610 EXCEPTION
4611 -- Handle the Locking exception
4612 WHEN e_resource_busy THEN
4613 ROLLBACK TO SP_LOCKBOX_ERROR;
4614 retcode := 2;
4615 fnd_message.set_name('IGS','IGS_FI_RFND_REC_LOCK');
4616 fnd_file.put_line(fnd_file.log,fnd_message.get());
4617 fnd_file.new_line(fnd_file.log);
4618
4619 -- Handle Other Exceptions
4620 WHEN OTHERS THEN
4621 retcode := 2;
4622 ROLLBACK TO SP_LOCKBOX_ERROR;
4623 fnd_message.set_name('IGS', 'IGS_GE_UNHANDLED_EXCEPTION');
4624 fnd_file.put_line(fnd_file.LOG, fnd_message.get || ' - ' || SQLERRM);
4625 END import_error_lockbox;
4626 END igs_fi_prc_lockbox;
[Home] [Help]