[Home] [Help]
PACKAGE BODY: APPS.CE_AUTO_BANK_IMPORT
Source
1 PACKAGE BODY CE_AUTO_BANK_IMPORT AS
2 /* $Header: ceabrimb.pls 120.22.12010000.5 2008/12/16 09:29:13 bkkashya ship $ */
3 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
4 --l_DEBUG varchar2(1) := 'Y';
5
6 CURSOR branch_cursor ( p_bank_branch_id NUMBER,
7 p_bank_account_id NUMBER ) IS
8 SELECT aba.bank_account_id,
9 aba.ACCOUNT_OWNER_ORG_ID,
10 nvl(aba.XTR_USE_ALLOWED_FLAG,'N'),
11 nvl(aba.PAY_USE_ALLOWED_FLAG,'N')
12 --FROM ce_bank_accounts_v aba
13 FROM ce_bank_accts_gt_v aba
14 WHERE aba.bank_branch_id = p_bank_branch_id
15 AND aba.bank_account_id = NVL(p_bank_account_id, aba.bank_account_id);
16 --AND aba.account_classification = 'INTERNAL';
17 --AND aba.account_type = CE_AUTO_BANK_MATCH.get_security_account_type(aba.account_type);
18
19 CURSOR bank_cursor ( p_statement_number_from VARCHAR2,
20 p_statement_number_to VARCHAR2,
21 p_statement_date_from DATE,
22 p_statement_date_to DATE,
23 p_bank_account_id NUMBER) IS
24 SELECT sh.rowid,
25 sh.statement_number,
26 sh.bank_account_num,
27 sh.check_digits,
28 sh.control_begin_balance,
29 sh.control_end_balance,
30 sh.cashflow_balance,
31 sh.int_calc_balance,
32 sh.average_close_ledger_mtd,
33 sh.average_close_ledger_ytd,
34 sh.average_close_available_mtd,
35 sh.average_close_available_ytd,
36 sh.one_day_float,
37 sh.two_day_float,
38 sh.intra_day_flag,
39 sh.subsidiary_flag,
40 sh.control_total_dr,
41 sh.control_total_cr,
42 sh.control_dr_line_count,
43 sh.control_cr_line_count,
44 sh.control_line_count,
45 sh.attribute_category,
46 sh.attribute1,
47 sh.attribute2,
48 sh.attribute3,
49 sh.attribute4,
50 sh.attribute5,
51 sh.attribute6,
52 sh.attribute7,
53 sh.attribute8,
54 sh.attribute9,
55 sh.attribute10,
56 sh.attribute11,
57 sh.attribute12,
58 sh.attribute13,
59 sh.attribute14,
60 sh.attribute15,
61 sh.statement_date,
62 sh.bank_branch_name,
63 sh.bank_name,
64 sh.bank_branch_name,
65 sh.currency_code,
66 --sh.org_id,
67 rsh.statement_number,
68 ba.bank_account_name,
69 ba.currency_code,
70 ba.check_digits
71 FROM ce_statement_headers rsh,
72 ce_statement_headers_int sh,
73 ce_bank_accts_gt_v ba --ce_bank_accounts_v ba
74 WHERE rsh.statement_number(+) = sh.statement_number
75 AND rsh.bank_account_id(+) = p_bank_account_id
76 AND NVL(sh.record_status_flag, 'I') <> 'T'
77 AND sh.statement_number
78 BETWEEN NVL(p_statement_number_from,sh.statement_number)
79 AND NVL(p_statement_number_to,sh.statement_number)
80 AND to_char(sh.statement_date,'J')
81 BETWEEN NVL(to_char(p_statement_date_from,'J'),1)
82 AND NVL(to_char(p_statement_date_to,'J'),3442447)
83 AND sh.bank_account_num = ba.bank_account_num
84 AND ba.bank_account_id = NVL(p_bank_account_id,ba.bank_account_id)
85 AND EXISTS (
86 select null
87 from ce_bank_branches_v bb
88 where bb.branch_party_id = ba.bank_branch_id
89 and bb.bank_name = nvl(sh.bank_name, bb.bank_name)
90 and bb.bank_branch_name =
91 nvl(sh.bank_branch_name, bb.bank_branch_name))
92 ORDER BY sh.bank_account_num, sh.statement_number;
93
94 CURSOR lines_cursor ( p_statement_number VARCHAR2,
95 p_bank_account_num VARCHAR2) IS
96 SELECT distinct l.rowid,
97 l.line_number,
98 l.amount,
99 l.trx_code,
100 l.user_exchange_rate_type,
101 l.currency_code,
102 l.exchange_rate_date,
103 l.trx_date,
104 /* commented for bug 7531187
105 NVL(DECODE(ctc.trx_type,'DEBIT', l.amount,
106 'MISC_DEBIT', l.amount,
107 'NSF', l.amount,
108 'REJECTED', l.amount, 0),0),
109 DECODE(ctc.trx_type,'DEBIT', 1,
110 'MISC_DEBIT', 1,
111 'NSF', 1,
112 'REJECTED', 1, 0),
113 NVL(DECODE(ctc.trx_type,'CREDIT', l.amount,
114 'MISC_CREDIT', l.amount,
115 'STOP', l.amount, 0),0),
116 DECODE(ctc.trx_type,'CREDIT', 1,
117 'MISC_CREDIT', 1,
118 'STOP', 1, 0),
119 */
120
121 -- Added Sweep In and Sweep out in the above commented code - bug 7531187
122 NVL(DECODE(ctc.trx_type,'DEBIT', l.amount,
123 'MISC_DEBIT', l.amount,
124 'NSF', l.amount,
125 'SWEEP_OUT', l.amount,
126 'REJECTED', l.amount, 0),0),
127 DECODE(ctc.trx_type,'DEBIT', 1,
128 'MISC_DEBIT', 1,
129 'NSF', 1,
130 'SWEEP_OUT', 1,
131 'REJECTED', 1, 0),
132 NVL(DECODE(ctc.trx_type,'CREDIT', l.amount,
133 'MISC_CREDIT', l.amount,
134 'SWEEP_IN', l.amount,
135 'STOP', l.amount, 0),0),
136 DECODE(ctc.trx_type,'CREDIT', 1,
137 'MISC_CREDIT', 1,
138 'SWEEP_IN', 1,
139 'STOP', 1, 0),
140
141 ctc.transaction_code_id,--for bug 7194081 --null, --bug 5665539 l.trx_code, --ctc.transaction_code_id,
142 null, --ctc.start_date
143 null, --ctc.end_date
144 gt.conversion_type,
145 gt.user_conversion_type,
146 curr.currency_code
147 FROM fnd_currencies curr,
148 gl_daily_conversion_types gt,
149 ce_transaction_codes ctc,
150 ce_statement_lines_interface l
151 WHERE curr.currency_code(+) = l.currency_code
152 AND gt.user_conversion_type(+) = l.user_exchange_rate_type
153 AND ctc.trx_code(+) = l.trx_code
154 AND ctc.bank_account_id(+) = CE_AUTO_BANK_IMPORT.G_bank_account_id
155
156 -- Code added for bug 7531187
157 AND nvl(ctc.Reconciliation_Sequence,1) = (SELECT nvl(min(ctc2.Reconciliation_Sequence),1)
158 FROM ce_Transaction_Codes ctc2
159 WHERE ctc2.Bank_Account_Id (+) =CE_AUTO_BANK_IMPORT.G_bank_account_id
160 AND ctc2.trx_Code (+) = l.trx_Code)
161 -- End of Code for bug 7531187
162
163 AND l.statement_number = p_statement_number
164 AND l.bank_account_num = p_bank_account_num
165 ORDER BY l.line_number;
166
167
168 FUNCTION body_revision RETURN VARCHAR2 IS
169 BEGIN
170
171 RETURN '$Revision: 120.22.12010000.5 $';
172
173 END body_revision;
174
175 FUNCTION spec_revision RETURN VARCHAR2 IS
176 BEGIN
177
178 RETURN G_spec_revision;
179
180 END spec_revision;
181
182 /* ---------------------------------------------------------------------
183 | PRIVATE PROCEDURE |
184 | header_error |
185 | |
186 | DESCRIPTION |
187 | cover routine to write header errors |
188 -----------------------------------------------------------------------*/
189
190 FUNCTION header_error (error_name VARCHAR2) RETURN BOOLEAN IS
191 BEGIN
192 IF l_DEBUG in ('Y', 'C') THEN
193 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.header_error');
194 END IF;
195 CE_HEADER_INTERFACE_ERRORS_PKG.insert_row(CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
196 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num, error_name);
197 IF l_DEBUG in ('Y', 'C') THEN
198 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.header_error');
199 END IF;
200 RETURN TRUE;
201 END header_error;
202
203 /* ---------------------------------------------------------------------
204 | PRIVATE PROCEDURE |
205 | line_error |
206 | |
207 | DESCRIPTION |
208 | cover routine to write line errors |
209 -----------------------------------------------------------------------*/
210 FUNCTION line_error (line_number NUMBER, error_name VARCHAR2) RETURN BOOLEAN IS
211 x_rowid VARCHAR2(100);
212 BEGIN
213 IF l_DEBUG in ('Y', 'C') THEN
214 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.line_error');
215 END IF;
216 CE_LINE_INTERFACE_ERRORS_PKG.insert_row(
217 x_rowid,
218 CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
219 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num,
220 line_number,
221 error_name);
222 IF l_DEBUG in ('Y', 'C') THEN
223 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.line_error');
224 END IF;
225 RETURN TRUE;
226 END line_error;
227
228
229 /* ---------------------------------------------------------------------
230 | PRIVATE FUNCTION |
231 | check_balance_values |
232 | |
233 | DESCRIPTION |
234 | Balances already exist in balance table. Check if the values |
235 | are the. |
236 | |
237 | CALLED BY |
238 | transfer_header |
239 | |
240 | REQUIRES |
241 | p_bank_account_id Bank Account Id |
242 | |
243 | |
244 --------------------------------------------------------------------- */
245 FUNCTION check_balance_values(p_bank_account_id number)
246 RETURN NUMBER IS
247 l_return number:=0;
248 BEGIN
249 SELECT 1
250 INTO l_return
251 FROM CE_BANK_ACCT_BALANCES
252 WHERE BANK_ACCOUNT_ID = p_bank_account_id
253 AND BALANCE_DATE = trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date)
254 AND NVL(LEDGER_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,0)
255 AND NVL(AVAILABLE_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,0)
256 AND NVL(VALUE_DATED_BALANCE,0) = NVL(CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,0);
257
258 return l_return;
259 EXCEPTION
260 WHEN OTHERS THEN
261 return l_return;
262 END check_balance_values;
263
264
265 /* ---------------------------------------------------------------------
266 | PRIVATE PROCEDURE |
267 | xtr_shared_account
268 | |
269 | DESCRIPTION |
270 | verify the bank account is a shared account or AP-only account |
271 -----------------------------------------------------------------------*/
272 PROCEDURE xtr_shared_account(X_ACCOUNT_RESULT OUT NOCOPY VARCHAR2) IS
273
274 X_RESULT VARCHAR2(100);
275 X_ERROR_MSG VARCHAR2(1000);
276 set_warning boolean;
277 bu_error_found boolean;
278
279 BEGIN
280 IF l_DEBUG in ('Y', 'C') THEN
281 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.xtr_shared_account'||
282 'P_ORG_ID = '|| CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID ||
283 ', G_BANK_ACCOUNT_ID = '|| CE_AUTO_BANK_IMPORT.G_bank_account_id||
284 ' G_cshi_currency_code = '|| CE_AUTO_BANK_IMPORT.G_cshi_currency_code);
285 END IF;
286
287 XTR_WRAPPER_API_P.bank_account_verification(
288 P_ORG_ID => CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID, --CE_AUTO_BANK_IMPORT.G_cshi_org_id,
289 P_CE_BANK_ACCOUNT_ID => CE_AUTO_BANK_IMPORT.G_bank_account_id,
290 P_CURRENCY_CODE => CE_AUTO_BANK_IMPORT.G_cshi_currency_code,
291 P_RESULT => X_RESULT,
295
292 P_ERROR_MSG => X_ERROR_MSG);
293
294 X_ACCOUNT_RESULT := X_RESULT;
296 IF l_DEBUG in ('Y', 'C') THEN
297 cep_standard.debug('xtr_shared_account x_result = ' || x_result||
298 ', x_error_msg = ' || x_error_msg);
299 END IF;
300
301 if (x_account_result = 'XTR1_NOT_SETUP' ) then
302 FND_FILE.put_line(FND_FILE.LOG, X_ERROR_MSG);
303 set_warning :=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Check log file for warning messages');
304 bu_error_found := CE_AUTO_BANK_IMPORT.header_error('CE_XTR_ACCT_NOT_SETUP');
305 update_header_status('E');
306 end if;
307
308 IF l_DEBUG in ('Y', 'C') THEN
309 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.xtr_shared_account');
310 END IF;
311
312 EXCEPTION
313 WHEN OTHERS THEN
314 IF l_DEBUG in ('Y', 'C') THEN
315 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.xtr_shared_account');
316 END IF;
317 RAISE;
318 END xtr_shared_account;
319
320 /* ---------------------------------------------------------------------
321 | PRIVATE PROCEDURE |
322 | transfer_bank_balances |
323 | |
324 | DESCRIPTION |
325 | transfer bank balance to xtr if bank account is a shared account |
326 -----------------------------------------------------------------------*/
327 FUNCTION transfer_bank_balances RETURN BOOLEAN IS
328 X_RESULT VARCHAR2(100);
329 X_ERROR_MSG VARCHAR2(1000);
330 bu_error_found boolean;
331 set_warning boolean;
332 BEGIN
333 IF l_DEBUG in ('Y', 'C') THEN
334 cep_standard.debug('>> CE_AUTO_BANK_IMPORT.transfer_bank_balances');
335 cep_standard.debug('transfer_bank_balances G_CSHI_ORG_ID = '|| CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID);
336 cep_standard.debug('transfer_bank_balances G_BANK_ACCOUNT_ID = '|| CE_AUTO_BANK_IMPORT.G_bank_account_id);
337 cep_standard.debug('transfer_bank_balances G_CSHI_CURRENCY_CODE = '|| CE_AUTO_BANK_IMPORT.G_cshi_currency_code);
338 cep_standard.debug('transfer_bank_balances G_CSHI_STATEMENT_DATE = '|| CE_AUTO_BANK_IMPORT.G_cshi_statement_date);
339 cep_standard.debug('transfer_bank_balances G_CSHI_CONTROL_END_BALANCE = '|| CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance);
340 cep_standard.debug('transfer_bank_balances G_CSHI_CASHFLOW_BALANCE = '|| CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance);
341 cep_standard.debug('transfer_bank_balances G_CSHI_INT_CALC_BALANCE = '|| CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance);
342 cep_standard.debug('transfer_bank_balances G_CSHI_ONE_DAY_FLOAT = '|| CE_AUTO_BANK_IMPORT.G_cshi_one_day_float);
343 cep_standard.debug('transfer_bank_balances G_CSHI_TWO_DAY_FLOAT = '|| CE_AUTO_BANK_IMPORT.G_cshi_two_day_float);
344 END IF;
345
346 bu_error_found := FALSE;
347
348
349
350 XTR_WRAPPER_API_P.bank_balance_upload(
351 P_ORG_ID => CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID, --CE_AUTO_BANK_IMPORT.G_cshi_org_id,
352 P_CE_BANK_ACCOUNT_ID => CE_AUTO_BANK_IMPORT.G_bank_account_id,
353 P_CURRENCY_CODE => CE_AUTO_BANK_IMPORT.G_cshi_currency_code,
354 P_BALANCE_DATE => CE_AUTO_BANK_IMPORT.G_cshi_statement_date,
355 P_BALANCE_AMOUNT_A => CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
356 P_BALANCE_AMOUNT_B => CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
357 P_BALANCE_AMOUNT_C => CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
358 P_ONE_DAY_FLOAT => CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
359 P_TWO_DAY_FLOAT => CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
360 P_RESULT => X_RESULT,
361 P_ERROR_MSG => X_ERROR_MSG);
362 IF l_DEBUG in ('Y', 'C') THEN
363 cep_standard.debug('transfer_bank_balances x_result = '|| x_result);
364 cep_standard.debug('transfer_bank_balances x_error_msg = '|| x_error_msg);
365 END IF;
366 if (x_result = 'XTR3_BU_WARNING') then /* log and import */
367 FND_FILE.put_line(FND_FILE.LOG, X_ERROR_MSG);
368 set_warning :=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Check log file for warning messages');
369 return TRUE;
370 elsif (x_result = 'XTR3_BU_SUCCESS') then /* can import */
371 return TRUE;
372 else /* do not import */
373 FND_FILE.put_line(FND_FILE.LOG, X_ERROR_MSG);
374 set_warning :=FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING', 'Check log file for warning messages');
375 bu_error_found := CE_AUTO_BANK_IMPORT.header_error('CE_BU_FAILED');
376 update_header_status('E');
377 return FALSE;
378 end if;
379 IF l_DEBUG in ('Y', 'C') THEN
380 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.transfer_bank_balances');
381 END IF;
382 EXCEPTION
383 WHEN OTHERS THEN
384 IF l_DEBUG in ('Y', 'C') THEN
385 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.transfer_bank_balances');
386 END IF;
387 RAISE;
388 END transfer_bank_balances;
389
390 /* ---------------------------------------------------------------------
391 | PRIVATE PROCEDURE |
392 | purge_data |
393 | |
394 | DESCRIPTION |
395 | Purge data from interface tables that have been succesfully |
396 | transferred in this run. |
397 | |
401 PROCEDURE purge_data IS
398 | CALLED BY |
399 | import_process |
400 -----------------------------------------------------------------------*/
402 BEGIN
403 IF l_DEBUG in ('Y', 'C') THEN
404 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.purge_data');
405 END IF;
406 DELETE FROM ce_statement_headers_int sh
407 WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
408
409 DELETE FROM ce_statement_lines_interface sl
410 WHERE sl.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
411 AND sl.statement_number = CE_AUTO_BANK_IMPORT.G_cshi_statement_number;
412 IF l_DEBUG in ('Y', 'C') THEN
413 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.purge_data');
414 END IF;
415 EXCEPTION
416 WHEN others THEN
417 IF l_DEBUG in ('Y', 'C') THEN
418 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.purge_data' );
419 RAISE;
420 END IF;
421 END purge_data;
422
423 /* ---------------------------------------------------------------------
424 | PRIVATE PROCEDURE |
425 | archive_lines
426 | |
427 | DESCRIPTION |
428 | Archive transactions in lines tables |
429 | |
430 | CALLED BY |
431 | import_process |
432 --------------------------------------------------------------------- */
433 PROCEDURE archive_lines IS
434 BEGIN
435 IF l_DEBUG in ('Y', 'C') THEN
436 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.archive_lines');
437 END IF;
438 INSERT INTO ce_arch_interface_lines
439 (bank_account_num,
440 statement_number,
441 line_number,
442 trx_date,
443 trx_code,
444 effective_date,
445 trx_text,
446 invoice_text,
447 amount,
448 charges_amount,
449 currency_code,
450 user_exchange_rate_type,
451 exchange_rate_date,
452 exchange_rate,
453 original_amount,
454 bank_trx_number,
455 customer_text,
456 bank_account_text,
457 created_by,
458 creation_date,
459 last_updated_by,
460 last_update_date,
461 attribute_category,
462 attribute1,
463 attribute2,
464 attribute3,
465 attribute4,
466 attribute5,
467 attribute6,
468 attribute7,
469 attribute8,
470 attribute9,
471 attribute10,
472 attribute11,
473 attribute12,
474 attribute13,
475 attribute14,
476 attribute15)
477 SELECT bank_account_num,
478 statement_number,
479 line_number,
480 trx_date,
481 trx_code,
482 effective_date,
483 trx_text,
484 invoice_text,
485 amount,
486 charges_amount,
487 currency_code,
488 user_exchange_rate_type,
489 exchange_rate_date,
490 exchange_rate,
491 original_amount,
492 bank_trx_number,
493 customer_text,
494 bank_account_text,
495 created_by,
496 creation_date,
497 last_updated_by,
498 last_update_date,
499 attribute_category,
500 attribute1,
501 attribute2,
502 attribute3,
503 attribute4,
504 attribute5,
505 attribute6,
506 attribute7,
507 attribute8,
508 attribute9,
509 attribute10,
510 attribute11,
511 attribute12,
512 attribute13,
513 attribute14,
514 attribute15
515 FROM ce_statement_lines_interface
516 WHERE statement_number = CE_AUTO_BANK_IMPORT.G_cshi_statement_number
517 AND bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num;
518 IF l_DEBUG in ('Y', 'C') THEN
519 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.archive_lines');
520 END IF;
521 EXCEPTION
522 WHEN OTHERS THEN
523 IF l_DEBUG in ('Y', 'C') THEN
524 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.archive_lines');
525 END IF;
526 RAISE;
527 END archive_lines;
528
529 /* ---------------------------------------------------------------------
530 | PRIVATE PROCEDURE |
531 | archive_header
532 | |
533 | DESCRIPTION |
534 | Archive transactions interface header tables |
535 | |
536 | CALLED BY |
537 | import_process |
538 ---------------------------------------------------------------------- */
539 PROCEDURE archive_header IS
540 BEGIN
541 IF l_DEBUG in ('Y', 'C') THEN
542 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.archive_header');
543 END IF;
544 INSERT INTO ce_arch_interface_headers
545 (statement_number,
546 bank_account_num,
547 statement_date,
548 check_digits,
549 bank_name,
550 bank_branch_name,
551 control_begin_balance,
552 control_end_balance,
553 cashflow_balance,
554 int_calc_balance,
558 average_close_available_ytd,
555 average_close_ledger_mtd,
556 average_close_ledger_ytd,
557 average_close_available_mtd,
559 one_day_float,
560 two_day_float,
561 intra_day_flag,
562 control_total_dr,
563 control_total_cr,
564 control_dr_line_count,
565 control_cr_line_count,
566 control_line_count,
567 record_status_flag,
568 currency_code,
569 created_by,
570 creation_date,
571 attribute_category,
572 attribute1,
573 attribute2,
574 attribute3,
575 attribute4,
576 attribute5,
577 attribute6,
578 attribute7,
579 attribute8,
580 attribute9,
581 attribute10,
582 attribute11,
583 attribute12,
584 attribute13,
585 attribute14,
586 attribute15,
587 last_updated_by,
588 last_update_date)
589 --org_id)
590 SELECT statement_number,
591 bank_account_num,
592 statement_date,
593 check_digits,
594 bank_name,
595 bank_branch_name,
596 control_begin_balance,
597 control_end_balance,
598 cashflow_balance,
599 int_calc_balance,
600 average_close_ledger_mtd,
601 average_close_ledger_ytd,
602 average_close_available_mtd,
603 average_close_available_ytd,
604 one_day_float,
605 two_day_float,
606 intra_day_flag,
607 control_total_dr,
608 control_total_cr,
609 control_dr_line_count,
610 control_cr_line_count,
611 control_line_count,
612 record_status_flag,
613 currency_code,
614 created_by,
615 creation_date,
616 attribute_category,
617 attribute1,
618 attribute2,
619 attribute3,
620 attribute4,
621 attribute5,
622 attribute6,
623 attribute7,
624 attribute8,
625 attribute9,
626 attribute10,
627 attribute11,
628 attribute12,
629 attribute13,
630 attribute14,
631 attribute15,
632 last_updated_by,
633 last_update_date
634 --org_id
635 FROM ce_statement_headers_int
636 WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
637 IF l_DEBUG in ('Y', 'C') THEN
638 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.archive_header');
639 END IF;
640 EXCEPTION
641 WHEN OTHERS THEN
642 IF l_DEBUG in ('Y', 'C') THEN
643 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.archive_header');
644 END IF;
645 RAISE;
646 END archive_header;
647
648 /* ---------------------------------------------------------------------
649 | PRIVATE PROCEDURE |
650 | get_sequence_info |
651 | |
652 | DESCRIPTION |
653 | get the document sequence information |
654 | |
655 | CALLED BY |
656 | transfer_header, reconcile_trx, reconcile_rbatch |
657 --------------------------------------------------------------------- */
658 FUNCTION get_sequence_info (app_id IN NUMBER,
659 category_code IN VARCHAR2,
660 set_of_books_id IN NUMBER,
661 entry_method IN CHAR,
662 trx_date IN DATE,
663 seq_name IN OUT NOCOPY VARCHAR2,
664 seq_id IN OUT NOCOPY NUMBER,
665 seq_value IN OUT NOCOPY NUMBER) RETURN BOOLEAN IS
666 l_return_code NUMBER;
667 BEGIN
668 IF l_DEBUG in ('Y', 'C') THEN
669 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.get_sequence_info' );
670 END IF;
671 IF (CE_AUTO_BANK_REC.G_sequence_numbering IN ('A','P')) THEN
672 --
673 -- bug# 1062247
674 -- Change FND_SEQNUM.get_next_sequence call to FND_SEQNUM.get_seq_val call
675 --
676 l_return_code := FND_SEQNUM.get_seq_val( app_id,
677 category_code,
678 set_of_books_id,
679 entry_method,
680 trx_date,
681 seq_value,
682 seq_id,
683 'N', 'N');
684
685 IF l_DEBUG in ('Y', 'C') THEN
686 cep_standard.debug('get_sequence_info: ' || '>>CE_AUTO_BANK_IMPORT.dbseqname: '|| seq_name || '----------' ||
687 'get_sequence_info: ' || '>>CE_AUTO_BANK_IMPORT.seq_id: '|| TO_CHAR( seq_id ) || '----------' ||
688 'get_sequence_info: ' || '>>CE_AUTO_BANK_IMPORT.doc_seq_value: '|| TO_CHAR( seq_value ) );
689 END IF;
690 END IF;
691 IF (((NVL(seq_value,0) = 0) OR l_return_code <> 0)
692 AND (CE_AUTO_BANK_REC.G_sequence_numbering = 'A' )) THEN
693 RETURN FALSE;
694 ELSE
695 RETURN TRUE;
696 END IF;
697 IF l_DEBUG in ('Y', 'C') THEN
698 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.get_sequence_info' );
699 END IF;
700 EXCEPTION
701 WHEN OTHERS THEN
702 IF (CE_AUTO_BANK_REC.G_sequence_numbering= 'A') THEN
703 RETURN FALSE;
704 ELSE
705 RETURN TRUE;
706 END IF;
707 END get_sequence_info;
708
709 /* ---------------------------------------------------------------------
710 | PRIVATE PROCEDURE |
711 | lock_statement |
712 | |
713 | DESCRIPTION |
714 | Using the rowid, retrieve the statement details. |
715 | |
716 | CALLED BY |
720 X_statement_number CE_STATEMENT_HEADERS_INT.statement_number%TYPE;
717 | import_prcess |
718 --------------------------------------------------------------------- */
719 FUNCTION lock_statement RETURN BOOLEAN IS
721 BEGIN
722 IF l_DEBUG in ('Y', 'C') THEN
723 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.lock_statement');
724 END IF;
725 SELECT statement_number
726 INTO X_statement_number
727 FROM ce_statement_headers_int
728 WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid
729 FOR UPDATE OF statement_number NOWAIT;
730
731 IF l_DEBUG in ('Y', 'C') THEN
732 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.lock_statement');
733 END IF;
734 RETURN(TRUE);
735 EXCEPTION
736 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
737 IF l_DEBUG in ('Y', 'C') THEN
738 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.lock_statement STATEMENT LOCKED');
739 END IF;
740 return(FALSE);
741 WHEN OTHERS THEN
742 IF l_DEBUG in ('Y', 'C') THEN
743 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.lock_statement');
744 END IF;
745 RAISE;
746 return(FALSE);
747 END lock_statement;
748
749 /* ---------------------------------------------------------------------
750 | PRIVATE PROCEDURE |
751 | lock_statement_line |
752 | |
753 | DESCRIPTION |
754 | Using the rowid, retrieve the statement line details. |
755 | |
756 | CALLED BY |
757 | import_prcess |
758 --------------------------------------------------------------------- */
759 FUNCTION lock_statement_line(csli_rowid IN VARCHAR2) RETURN BOOLEAN IS
760 csli_amount CE_STATEMENT_LINES.amount%TYPE;
761 BEGIN
762 IF l_DEBUG in ('Y', 'C') THEN
763 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.lock_statement_line');
764 END IF;
765 SELECT amount
766 INTO csli_amount
767 FROM ce_statement_lines_interface
768 WHERE rowid = csli_rowid
769 FOR UPDATE OF bank_account_num NOWAIT;
770 IF l_DEBUG in ('Y', 'C') THEN
771 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.lock_statement_line');
772 END IF;
773 RETURN(TRUE);
774 EXCEPTION
775 WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
776 IF l_DEBUG in ('Y', 'C') THEN
777 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.lock_statement_line STATEMENT LOCKED');
778 END IF;
779 return(FALSE);
780 WHEN OTHERS THEN
781 IF l_DEBUG in ('Y', 'C') THEN
782 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.lock_statement_line');
783 END IF;
784 RAISE;
785 return(FALSE);
786 END lock_statement_line;
787
788 /* ---------------------------------------------------------------------
789 | PRIVATE PROCEDURE |
790 | transfer_lines |
791 | |
792 | DESCRIPTION |
793 | Copy the statement lines in CE_STATEMENT_LINES_INTERFACE into |
794 | CE_STATEMENT_LINES for the given statement and bank account |
795 | combination. Record the bank account id and not the bank |
796 | account number. If the currency field is null, populate it |
797 | with the base currency of the bank. |
798 | |
799 | CALLED BY |
800 | import_process |
801 | |
802 | HISTORY |
803 --------------------------------------------------------------------- */
804 PROCEDURE transfer_lines IS
805
806 fixed_relation BOOLEAN;
807 curr_relation VARCHAR2(30);
808
809 BEGIN
810 IF l_DEBUG in ('Y', 'C') THEN
811 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.transfer_lines');
812 cep_standard.debug('func: '||CE_AUTO_BANK_REC.G_functional_currency);
813 END IF;
814
815 IF (G_cshi_intra_day_flag = 'Y' AND CE_AUTO_BANK_REC.G_intra_day_flag = 'Y') THEN
816 INSERT INTO ce_intra_stmt_lines
817 (statement_line_id,
818 statement_header_id,
819 line_number,
820 trx_date,
821 trx_type,
822 trx_code_id,
823 effective_date,
824 bank_trx_number,
825 trx_text,
826 customer_text,
827 invoice_text,
828 bank_account_text,
829 amount,
830 charges_amount,
831 status,
832 currency_code,
833 exchange_rate_type,
834 exchange_rate_date,
835 exchange_rate,
836 original_amount,
837 created_by,
838 creation_date,
839 last_updated_by,
840 last_update_date,
841 attribute_category,
842 attribute1,
843 attribute2,
844 attribute3,
845 attribute4,
846 attribute5,
847 attribute6,
848 attribute7,
849 attribute8,
850 attribute9,
851 attribute10,
852 attribute11,
853 attribute12,
854 attribute13,
855 attribute14,
856 attribute15)
857 SELECT ce_intra_stmt_lines_s.nextval,
858 G_cshi_statement_header_id,
859 csli.line_number,
860 csli.trx_date,
861 ctc.trx_type,
862 ctc.transaction_code_id,
863 NVL(csli.effective_date,csli.trx_date+NVL(ctc.float_days,0)),
864 csli.bank_trx_number,
865 csli.trx_text,
866 csli.customer_text,
867 csli.invoice_text,
868 csli.bank_account_text,
869 csli.amount,
870 csli.charges_amount,
871 '',
872 csli.currency_code,
873 null,
874 csli.exchange_rate_date,
878 sysdate,
875 csli.exchange_rate,
876 csli.original_amount,
877 NVL(FND_GLOBAL.user_id, -1),
879 NVL(FND_GLOBAL.user_id, -1),
880 sysdate,
881 csli.attribute_category,
882 csli.attribute1,
883 csli.attribute2,
884 csli.attribute3,
885 csli.attribute4,
886 csli.attribute5,
887 csli.attribute6,
888 csli.attribute7,
889 csli.attribute8,
890 csli.attribute9,
891 csli.attribute10,
892 csli.attribute11,
893 csli.attribute12,
894 csli.attribute13,
895 csli.attribute14,
896 csli.attribute15
897 FROM ce_transaction_codes ctc,
898 ce_statement_lines_interface csli,
899 ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
900 WHERE ctc.trx_code(+) = csli.trx_code
901 AND NVL(ctc.bank_account_id,aba.bank_account_id) =
902 aba.bank_account_id
903 AND csli.statement_number = G_cshi_statement_number
904 AND csli.bank_account_num = aba.bank_account_num
905 AND aba.bank_account_id = G_bank_account_id;
906 ELSIF (nvl(G_cshi_intra_day_flag,'N') <> 'Y' AND CE_AUTO_BANK_REC.G_intra_day_flag <> 'Y') THEN
907 --Bug 6710502: Added an nvl to subsisdiary flag to handle records upgraded from 11i to R12
908 IF ( nvl(G_cshi_subsidiary_flag,'N') = 'N' ) THEN
909 INSERT INTO ce_statement_lines
910 (statement_line_id,
911 statement_header_id,
912 line_number,
913 trx_date,
914 trx_type,
915 trx_code, --trx_code_id,
916 effective_date,
917 bank_trx_number,
918 trx_text,
919 customer_text,
920 invoice_text,
921 bank_account_text,
922 amount,
923 charges_amount,
924 status,
925 currency_code,
926 exchange_rate_type,
927 exchange_rate_date,
928 exchange_rate,
929 original_amount,
930 created_by,
931 creation_date,
932 last_updated_by,
933 last_update_date,
934 attribute_category,
935 attribute1,
936 attribute2,
937 attribute3,
938 attribute4,
939 attribute5,
940 attribute6,
941 attribute7,
942 attribute8,
943 attribute9,
944 attribute10,
945 attribute11,
946 attribute12,
947 attribute13,
948 attribute14,
949 attribute15)
950 SELECT ce_statement_lines_s.nextval,
951 CE_AUTO_BANK_IMPORT.G_cshi_statement_header_id,
952 csli.line_number,
953 csli.trx_date,
954 (select distinct ctc.trx_type
955 from ce_transaction_codes ctc
956 where ctc.trx_code(+) = csli.trx_code
957 AND NVL(ctc.bank_account_id,aba.bank_account_id) =
958 aba.bank_account_id),
959 csli.trx_code, --ctc.transaction_code_id,
960 --NVL(csli.effective_date,csli.trx_date+NVL(ctc.float_days,0)),
961 NVL(csli.effective_date,csli.trx_date),
962 csli.bank_trx_number,
963 csli.trx_text,
964 csli.customer_text,
965 csli.invoice_text,
966 csli.bank_account_text,
967 csli.amount,
968 csli.charges_amount,
969 'UNRECONCILED',
970 csli.currency_code,
971 decode(gl_currency_api.is_fixed_rate(
972 CE_AUTO_BANK_REC.G_functional_currency,
973 nvl(csli.currency_code,aba.currency_code),
974 nvl(csli.exchange_rate_date,csli.trx_date)),
975 'Y', decode(gdct.conversion_type, NULL,NULL,
976 decode(nvl(csli.currency_code,aba.currency_code),
977 CE_AUTO_BANK_REC.G_functional_currency,
978 gdct.conversion_type,'EMU FIXED')),
979 gdct.conversion_type),
980 csli.exchange_rate_date,
981 csli.exchange_rate,
982 csli.original_amount,
983 NVL(FND_GLOBAL.user_id, -1),
984 sysdate,
985 NVL(FND_GLOBAL.user_id, -1),
986 sysdate,
987 csli.attribute_category,
988 csli.attribute1,
989 csli.attribute2,
990 csli.attribute3,
991 csli.attribute4,
992 csli.attribute5,
993 csli.attribute6,
994 csli.attribute7,
995 csli.attribute8,
996 csli.attribute9,
997 csli.attribute10,
998 csli.attribute11,
999 csli.attribute12,
1000 csli.attribute13,
1001 csli.attribute14,
1002 csli.attribute15
1003 FROM --ce_transaction_codes ctc,
1004 gl_daily_conversion_types gdct,
1005 ce_statement_lines_interface csli,
1006 ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
1007 WHERE gdct.user_conversion_type(+) = csli.user_exchange_rate_type
1008 --AND ctc.trx_code(+) = csli.trx_code
1009 --AND NVL(ctc.bank_account_id,aba.bank_account_id) =
1010 -- aba.bank_account_id
1011 AND csli.statement_number =
1012 CE_AUTO_BANK_IMPORT.G_cshi_statement_number
1013 AND csli.bank_account_num = aba.bank_account_num
1014 AND aba.bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id;
1015 END IF;
1016 END IF;
1017 IF l_DEBUG in ('Y', 'C') THEN
1018 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.transfer_lines');
1019 END IF;
1020 EXCEPTION
1021 WHEN OTHERS THEN
1022 IF l_DEBUG in ('Y', 'C') THEN
1023 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.transfer_lines');
1024 END IF;
1025 RAISE;
1026 END transfer_lines;
1027
1028 /* ---------------------------------------------------------------------
1029 | PRIVATE PROCEDURE |
1030 | transfer_header |
1031 | |
1032 | DESCRIPTION |
1036 | account number. |
1033 | Copy the header line in CE_STATEMENT_HEADER_INTERFACE into |
1034 | CE_STATEMENT_HEADERS for the given statement and bank account |
1035 | combination. Record the bank account id and not the bank |
1037 | |
1038 | CALLED BY |
1039 | import_process |
1040 --------------------------------------------------------------------- */
1041 FUNCTION transfer_header( aba_bank_account_id NUMBER,
1042 aba_bank_account_name VARCHAR2) RETURN BOOLEAN IS
1043 l_dbseqname VARCHAR2(30);
1044 l_doc_seq_id NUMBER;
1045 l_doc_seq_value NUMBER;
1046 x_bal_count NUMBER;
1047 l_valid_seq BOOLEAN;
1048 l_dup_balance BOOLEAN;
1049 l_encoded_message VARCHAR2(255);
1050 l_message_name VARCHAR2(50);
1051 l_app_short_name VARCHAR2(30);
1052 x_temp_rowid VARCHAR2(100);
1053 x_flag VARCHAR2(2);
1054 X_bank_acct_balance_id NUMBER;
1055
1056 BEGIN
1057 IF l_DEBUG in ('Y', 'C') THEN
1058 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.transfer_header '||
1059 ' CE_AUTO_BANK_IMPORT.aba_bank_account_id : '|| aba_bank_account_id||
1060 ', CE_AUTO_BANK_IMPORT.aba_bank_account_name : '|| aba_bank_account_name);
1061 END IF;
1062 --
1063 -- Call the AOL sequence numbering routine to get Seq. number
1064 --
1065 l_valid_seq := CE_AUTO_BANK_IMPORT.get_sequence_info(260,
1066 aba_bank_account_name,
1067 CE_AUTO_BANK_REC.G_set_of_books_id,
1068 'A',
1069 CE_AUTO_BANK_IMPORT.G_cshi_statement_date,
1070 l_dbseqname,
1071 l_doc_seq_id,
1072 l_doc_seq_value );
1073
1074
1075
1076 IF (NOT l_valid_seq) THEN
1077
1078 IF l_DEBUG in ('Y', 'C') THEN
1079 cep_standard.debug('transfer_header not l_valid_seq ');
1080 END IF;
1081
1082 l_valid_seq := CE_AUTO_BANK_IMPORT.header_error('CE_DOC_SEQUENCE_HEADER_ERR');
1083 update_header_status('E');
1084 return FALSE;
1085 ELSE
1086 CE_AUTO_BANK_IMPORT.G_cshi_statement_header_id := NULL;
1087 IF l_DEBUG in ('Y', 'C') THEN
1088 cep_standard.debug('G_cshi_intra_day_flag '|| G_cshi_intra_day_flag ||
1089 ', CE_AUTO_BANK_REC.G_intra_day_flag '|| CE_AUTO_BANK_REC.G_intra_day_flag);
1090 END IF;
1091
1092 IF (G_cshi_intra_day_flag = 'Y' AND CE_AUTO_BANK_REC.G_intra_day_flag = 'Y') THEN
1093 DELETE FROM CE_INTRA_STMT_LINES
1094 WHERE statement_header_id in
1095 (select statement_header_id
1096 from ce_intra_stmt_headers
1097 where statement_number = G_cshi_statement_number
1098 and bank_account_id in
1099 (select bank_account_id from ce_bank_accounts_v
1100 where bank_account_num = G_cshi_bank_account_num));
1101 DELETE FROM CE_INTRA_STMT_HEADERS
1102 WHERE statement_number = G_cshi_statement_number
1103 AND bank_account_id in
1104 (select bank_account_id from ce_bank_accounts_v
1105 where bank_account_num = G_cshi_bank_account_num);
1106
1107 select ce_intra_stmt_headers_s.nextval
1108 into G_cshi_statement_header_id
1109 from sys.dual;
1110 INSERT INTO CE_INTRA_STMT_HEADERS (
1111 statement_header_id,
1112 bank_account_id,
1113 statement_number,
1114 statement_date,
1115 check_digits,
1116 control_begin_balance,
1117 control_end_balance,
1118 cashflow_balance,
1119 int_calc_balance,
1120 one_day_float,
1121 two_day_float,
1122 control_total_dr,
1123 control_total_cr,
1124 control_dr_line_count,
1125 control_cr_line_count,
1126 doc_sequence_id,
1127 doc_sequence_value,
1128 created_by,
1129 creation_date,
1130 last_updated_by,
1131 last_update_date,
1132 attribute_category,
1133 attribute1,
1134 attribute2,
1135 attribute3,
1136 attribute4,
1137 attribute5,
1138 attribute6,
1139 attribute7,
1140 attribute8,
1141 attribute9,
1142 attribute10,
1143 attribute11,
1144 attribute12,
1145 attribute13,
1146 attribute14,
1147 attribute15,
1148 auto_loaded_flag,
1149 statement_complete_flag,
1150 gl_date)
1151 --org_id)
1152 VALUES
1153 (G_cshi_statement_header_id,
1154 aba_bank_account_id,
1155 G_cshi_statement_number,
1156 G_cshi_statement_date,
1157 G_cshi_check_digits,
1158 G_cshi_control_begin_balance,
1159 G_cshi_control_end_balance,
1160 G_cshi_cashflow_balance,
1161 G_cshi_int_calc_balance,
1162 G_cshi_one_day_float,
1163 G_cshi_two_day_float,
1164 G_cshi_control_total_dr,
1165 G_cshi_control_total_cr,
1166 G_cshi_control_dr_line_count,
1167 G_cshi_control_cr_line_count,
1168 l_doc_seq_id,
1169 l_doc_seq_value,
1170 NVL(FND_GLOBAL.user_id,-1),
1171 sysdate,
1172 NVL(FND_GLOBAL.user_id,-1),
1173 sysdate,
1177 G_cshi_attribute3,
1174 G_cshi_attribute_category,
1175 G_cshi_attribute1,
1176 G_cshi_attribute2,
1178 G_cshi_attribute4,
1179 G_cshi_attribute5,
1180 G_cshi_attribute6,
1181 G_cshi_attribute7,
1182 G_cshi_attribute8,
1183 G_cshi_attribute9,
1184 G_cshi_attribute10,
1185 G_cshi_attribute11,
1186 G_cshi_attribute12,
1187 G_cshi_attribute13,
1188 G_cshi_attribute14,
1189 G_cshi_attribute15,
1190 'Y',
1191 'N',
1192 null);
1193 --G_cshi_org_id);
1194 update_header_status('T');
1195 RETURN TRUE;
1196 ELSIF ( nvl(G_cshi_intra_day_flag,'N') <> 'Y' AND CE_AUTO_BANK_REC.G_intra_day_flag <> 'Y') THEN
1197
1198 --check if statement date is less than sysdate
1199 -- cannot import statements with date greater than or equal to sysdate
1200 if (trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date) >= trunc(sysdate)) then
1201 l_dup_balance:= CE_AUTO_BANK_IMPORT.header_error('CE_LOD_STATEMENT_DATE');
1202 RETURN FALSE;
1203 end if;
1204
1205 select count(1)
1206 into x_bal_count
1207 from ce_bank_acct_balances
1208 where bank_account_id = aba_bank_account_id
1209 and balance_date = trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date);
1210
1211 IF x_bal_count > 0 THEN
1212 x_flag:='YI'; --balance already exist when inserting through loader
1213 --check if all the balance values are same, if yes then no log message required
1214 if ( check_balance_values(aba_bank_account_id)=0)then
1215 l_dup_balance:=CE_AUTO_BANK_IMPORT.header_error('CE_DUP_BALANCE');
1216 end if;
1217 ELSE
1218 x_flag:='N';
1219 END IF;
1220
1221 -- bug 6893481: adding an NVL clause to handle cases where subsidiary
1222 -- flag would be null.
1223 IF ( NVL(G_cshi_subsidiary_flag,'N') = 'N' ) THEN
1224 CE_STAT_HDRS_DML_PKG.insert_row (
1225 X_rowid => x_temp_rowid,
1226 X_statement_header_id => CE_AUTO_BANK_IMPORT.G_cshi_statement_header_id,
1227 X_bank_account_id => aba_bank_account_id,
1228 X_statement_number => CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
1229 X_statement_date => trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date),
1230 X_check_digits => CE_AUTO_BANK_IMPORT.G_cshi_check_digits,
1231 X_control_begin_balance => CE_AUTO_BANK_IMPORT.G_cshi_control_begin_balance,
1232 X_control_end_balance => CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
1233 X_cashflow_balance => CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
1234 X_int_calc_balance => CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
1235 X_one_day_float => CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
1236 X_two_day_float => CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
1237 X_control_total_dr => CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr,
1238 X_control_total_cr => CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr,
1239 X_control_dr_line_count => CE_AUTO_BANK_IMPORT.G_cshi_control_dr_line_count,
1240 X_control_cr_line_count => CE_AUTO_BANK_IMPORT.G_cshi_control_cr_line_count,
1241 X_doc_sequence_id => l_doc_seq_id,
1242 X_doc_sequence_value => l_doc_seq_value,
1243 X_created_by => NVL(FND_GLOBAL.user_id,-1),
1244 X_creation_date => sysdate,
1245 X_last_updated_by => NVL(FND_GLOBAL.user_id,-1),
1246 X_last_update_date => sysdate,
1247 X_attribute_category => CE_AUTO_BANK_IMPORT.G_cshi_attribute_category ,
1248 X_attribute1 => CE_AUTO_BANK_IMPORT.G_cshi_attribute1,
1249 X_attribute2 => CE_AUTO_BANK_IMPORT.G_cshi_attribute2,
1250 X_attribute3 => CE_AUTO_BANK_IMPORT.G_cshi_attribute3,
1251 X_attribute4 => CE_AUTO_BANK_IMPORT.G_cshi_attribute4,
1252 X_attribute5 => CE_AUTO_BANK_IMPORT.G_cshi_attribute5,
1253 X_attribute6 => CE_AUTO_BANK_IMPORT.G_cshi_attribute6,
1254 X_attribute7 => CE_AUTO_BANK_IMPORT.G_cshi_attribute7,
1255 X_attribute8 => CE_AUTO_BANK_IMPORT.G_cshi_attribute8,
1256 X_attribute9 => CE_AUTO_BANK_IMPORT.G_cshi_attribute9,
1257 X_attribute10 => CE_AUTO_BANK_IMPORT.G_cshi_attribute10,
1258 X_attribute11 => CE_AUTO_BANK_IMPORT.G_cshi_attribute11,
1259 X_attribute12 => CE_AUTO_BANK_IMPORT.G_cshi_attribute12,
1260 X_attribute13 => CE_AUTO_BANK_IMPORT.G_cshi_attribute13,
1261 X_attribute14 => CE_AUTO_BANK_IMPORT.G_cshi_attribute14,
1262 X_attribute15 => CE_AUTO_BANK_IMPORT.G_cshi_attribute15,
1263 X_auto_loaded_flag => 'Y',
1264 X_statement_complete_flag => 'N',
1265 X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
1266 X_balance_flag => x_flag,
1267 X_average_close_ledger_mtd => CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd,
1268 X_average_close_ledger_ytd => CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd,
1269 X_average_close_available_mtd => CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd,
1270 X_average_close_available_ytd => CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd,
1271 -- 5916290 : GDF Changes
1272 X_bank_acct_balance_id => NULL,
1273 X_global_att_category => NULL,
1274 X_global_attribute1 => NULL,
1275 X_global_attribute2 => NULL,
1276 X_global_attribute3 => NULL,
1277 X_global_attribute4 => NULL,
1278 X_global_attribute5 => NULL,
1279 X_global_attribute6 => NULL,
1280 X_global_attribute7 => NULL,
1281 X_global_attribute8 => NULL,
1282 X_global_attribute9 => NULL,
1286 X_global_attribute13 => NULL,
1283 X_global_attribute10 => NULL,
1284 X_global_attribute11 => NULL,
1285 X_global_attribute12 => NULL,
1287 X_global_attribute14 => NULL,
1288 X_global_attribute15 => NULL,
1289 X_global_attribute16 => NULL,
1290 X_global_attribute17 => NULL,
1291 X_global_attribute18 => NULL,
1292 X_global_attribute19 => NULL,
1293 X_global_attribute20 => NULL
1294 );
1295 ELSE
1296 SELECT CE_BANK_ACCT_BALANCES_S.nextval
1297 INTO X_bank_acct_balance_id
1298 FROM SYS.dual;
1299
1300 INSERT INTO CE_BANK_ACCT_BALANCES
1301 (bank_acct_balance_id,
1302 bank_account_id,
1303 balance_date,
1304 ledger_balance,
1305 available_balance,
1306 value_dated_balance,
1307 one_day_float,
1308 two_day_float,
1309 average_close_ledger_mtd,
1310 average_close_ledger_ytd,
1311 average_close_available_mtd,
1312 average_close_available_ytd,
1313 last_update_date,
1314 last_updated_by,
1315 creation_date,
1316 created_by,
1317 last_update_login,
1318 object_version_number)
1319 values
1320 (X_bank_acct_balance_id,
1321 aba_bank_account_id,
1322 trunc(CE_AUTO_BANK_IMPORT.G_cshi_statement_date),
1323 CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
1324 CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
1325 CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
1326 CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
1327 CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
1328 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd,
1329 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd,
1330 CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd,
1331 CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd,
1332 sysdate,
1333 NVL(FND_GLOBAL.user_id,-1),
1334 sysdate,
1335 NVL(FND_GLOBAL.user_id,-1),
1336 NVL(FND_GLOBAL.user_id,-1),
1337 '1');
1338 END IF;
1339
1340 update_header_status('T');
1341 RETURN TRUE;
1342 ELSE
1343 IF l_DEBUG in ('Y', 'C') THEN
1344 cep_standard.debug('transfer_header return false' );
1345 END IF;
1346 RETURN FALSE;
1347 END IF;
1348 END IF;
1349 IF l_DEBUG in ('Y', 'C') THEN
1350 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.transfer_header');
1351 END IF;
1352 EXCEPTION
1353 WHEN APP_EXCEPTION.application_exception THEN
1354 IF l_DEBUG in ('Y', 'C') THEN
1355 cep_standard.debug('EXCEPTION:CE_AUTO_BANK_IMPORT.transfer_header-APP_EXCEPTION');
1356 END IF;
1357 l_encoded_message := FND_MESSAGE.GET_ENCODED;
1358 IF (l_encoded_message IS NOT NULL) THEN
1359 FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,l_message_name);
1360 ELSE
1361 IF l_DEBUG in ('Y', 'C') THEN
1362 cep_standard.debug('transfer_header: ' || 'No messages on stack');
1363 END IF;
1364 l_message_name := 'OTHER_APP_ERROR';
1365 END IF;
1366 l_valid_seq := CE_AUTO_BANK_IMPORT.header_error('CE_DOC_SEQUENCE_HEADER_ERR');
1367 update_header_status('E');
1368 return FALSE;
1369 WHEN OTHERS THEN
1370 IF l_DEBUG in ('Y', 'C') THEN
1371 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.transfer_header');
1372 END IF;
1373 RAISE;
1374 END transfer_header;
1375
1376
1377 /* ---------------------------------------------------------------------
1378 | PRIVATE PROCEDURE |
1379 | update_header_status |
1380 | |
1381 | DESCRIPTION |
1382 | Errors have been found within the statement header or lines. |
1383 | The statement header record status is updated with 'ERROR'. |
1384 | |
1385 | CALLED BY |
1386 | import_process |
1387 | |
1388 | REQUIRES |
1389 | p_status Status to be updated |
1390 | 'T'ransferred |
1391 | 'E'rror |
1392 --------------------------------------------------------------------- */
1393 PROCEDURE update_header_status(p_status VARCHAR2) IS
1394 BEGIN
1395 IF l_DEBUG in ('Y', 'C') THEN
1396 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.update_header_status');
1397 END IF;
1398 UPDATE ce_statement_headers_int
1399 SET record_status_flag = p_status
1400 WHERE rowid = CE_AUTO_BANK_IMPORT.G_cshi_rowid;
1401 IF l_DEBUG in ('Y', 'C') THEN
1402 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.update_header_status');
1403 END IF;
1404 EXCEPTION
1405 WHEN OTHERS THEN
1406 IF l_DEBUG in ('Y', 'C') THEN
1407 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.update_header_status');
1408 END IF;
1409 RAISE;
1410 END update_header_status;
1411
1412 /* ---------------------------------------------------------------------
1413 | PRIVATE PROCEDURE |
1414 | validate_control_totals |
1415 | |
1416 | DESCRIPTION |
1417 | Many control totals are held within the statement header and |
1418 | may be used to check that all the statement lines have been |
1419 | loaded. |
1420 | |
1421 | CALLED BY |
1422 | import_process |
1423 | |
1424 | RETURNS |
1425 | error_found BOOLEAN |
1429 BEGIN
1426 --------------------------------------------------------------------- */
1427 FUNCTION validate_control_totals RETURN BOOLEAN IS
1428 error_found BOOLEAN;
1430 IF l_DEBUG in ('Y', 'C') THEN
1431 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.validate_control_totals');
1432 END IF;
1433 error_found := FALSE;
1434 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr,CE_AUTO_BANK_IMPORT.G_dr_sum) <> CE_AUTO_BANK_IMPORT.G_dr_sum) THEN
1435 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_CTRL_DR_TOTAL');
1436 END IF;
1437 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr,CE_AUTO_BANK_IMPORT.G_cr_sum) <> CE_AUTO_BANK_IMPORT.G_cr_sum) THEN
1438 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_CTRL_CR_TOTAL');
1439 END IF;
1440 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_dr_line_count,CE_AUTO_BANK_IMPORT.G_dr_count) <> CE_AUTO_BANK_IMPORT.G_dr_count) THEN
1441 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_DR_LINE_COUNT');
1442 END IF;
1443 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_cr_line_count,CE_AUTO_BANK_IMPORT.G_cr_count) <> CE_AUTO_BANK_IMPORT.G_cr_count) THEN
1444 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_CR_LINE_COUNT');
1445 END IF;
1446 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_begin_balance,0) -
1447 NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr,0) +
1448 NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr,0) <> NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,0)) THEN
1449 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_CTRL_END_BALANCE');
1450 END IF;
1451 IF (NVL(CE_AUTO_BANK_IMPORT.G_cshi_control_line_count,CE_AUTO_BANK_IMPORT.G_total_count) <> CE_AUTO_BANK_IMPORT.G_total_count) THEN
1452 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_LINE_COUNT');
1453 END IF;
1454 IF l_DEBUG in ('Y', 'C') THEN
1455 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.validate_control_totals');
1456 END IF;
1457 return error_found;
1458 EXCEPTION
1459 WHEN OTHERS THEN
1460 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.validate_control_totals');
1461 RAISE;
1462 END validate_control_totals;
1463
1464 /* ---------------------------------------------------------------------
1465 | PRIVATE PROCEDURE |
1466 | validate_bank_account |
1467 | |
1468 | CALLED BY |
1469 | header_validation |
1470 --------------------------------------------------------------------- */
1471 PROCEDURE validate_bank_account (aba_bank_currency VARCHAR2,
1472 aba_bank_check_digits VARCHAR2,
1473 error_found OUT NOCOPY BOOLEAN) IS
1474 trx_code_count NUMBER;
1475 aba_bank_account_id NUMBER;
1476 BEGIN
1477 IF l_DEBUG in ('Y', 'C') THEN
1478 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.validate_bank_account');
1479 END IF;
1480 --
1481 -- Bank and bank branch name validations
1482 -- NOTE: We just check for the 'INTERNAL' accounts
1483 --
1484 /* Bug# 599912: internal bank account is already validated when bank_account_id is passed to the package.
1485
1486 SELECT aba.bank_account_id
1487 INTO aba_bank_account_id
1488 FROM ap_bank_branches abb,
1489 ap_bank_accounts aba
1490 WHERE aba.bank_branch_id = abb.bank_branch_id
1491 AND abb.bank_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_name, abb.bank_name)
1492 AND abb.bank_branch_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,abb.bank_branch_name)
1493 AND aba.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
1494 AND aba.account_type = 'INTERNAL';
1495 */
1496 --
1497 -- Check for existence of the Transaction codes
1498 -- We need to do this because of the control total validation relies on these codes
1499 --
1500 SELECT count(*)
1501 INTO trx_code_count
1502 FROM ce_transaction_codes
1503 WHERE bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id;
1504 IF (trx_code_count = 0) THEN
1505 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_NO_TRX_CODES');
1506 END IF;
1507 --
1508 -- Bank account currency code validation
1509 --
1510 IF (aba_bank_currency <> NVL(CE_AUTO_BANK_IMPORT.G_cshi_currency_code,aba_bank_currency)) THEN
1511 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_FOREIGN_CURRENCY');
1512 END IF;
1513 --
1514 -- Check digits validation -- bug 7214921
1515 If CE_AUTO_BANK_IMPORT.G_cshi_check_digits is not null and aba_bank_check_digits is not null Then
1516 IF ( CE_AUTO_BANK_IMPORT.G_cshi_check_digits <> aba_bank_check_digits ) THEN
1517 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_CHECK_DIGITS');
1518 END IF;
1519 End If ;
1520
1521 IF l_DEBUG in ('Y', 'C') THEN
1522 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.validate_bank_account');
1523 END IF;
1524 EXCEPTION
1525 WHEN OTHERS THEN
1526 IF (SQL%NOTFOUND) THEN
1527 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_INVALID_BANK');
1528 IF l_DEBUG in ('Y', 'C') THEN
1529 cep_standard.debug('validate_bank_account: ' || 'error: BANK NOT FOUND');
1530 END IF;
1531 ELSIF (SQL%ROWCOUNT > 0) THEN
1532 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_DUP_BANK');
1533 IF l_DEBUG in ('Y', 'C') THEN
1534 cep_standard.debug('validate_bank_account: ' || 'error: DUPLICATE BANK');
1535 END IF;
1536 ELSE
1537 IF l_DEBUG in ('Y', 'C') THEN
1538 cep_standard.debug('validate_bank_account: ' || 'error: OTHER BANK VALIDATION');
1539 END IF;
1540 RAISE;
1544 /* ---------------------------------------------------------------------
1541 END IF;
1542 END validate_bank_account;
1543
1545 | PRIVATE FUNCTION |
1546 | header_validation |
1547 | |
1548 | DESCRIPTION |
1549 | Validate the statement header records |
1550 | on the interface tables for import errors. If any errors are |
1551 | detected, the statement will not be imported. |
1552 | |
1553 | CALLS |
1554 | validate_bank_account |
1555 | gl_date_found |
1556 | check for existing statement |
1557 | |
1558 | CALLED BY |
1559 | import_process |
1560 --------------------------------------------------------------------- */
1561 FUNCTION header_validation( r_statement_number VARCHAR2,
1562 aba_bank_currency VARCHAR2,
1563 aba_bank_check_digits VARCHAR2,
1564 aba_bank_account_id NUMBER) RETURN BOOLEAN IS
1565 bank_error BOOLEAN;
1566 duplicate_found BOOLEAN;
1567 gl_date_found BOOLEAN;
1568 error_found BOOLEAN;
1569 account_type VARCHAR2(25);
1570 BEGIN
1571 IF l_DEBUG in ('Y', 'C') THEN
1572 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.header_validation');
1573 END IF;
1574 error_found := FALSE;
1575 bank_error := FALSE;
1576 duplicate_found := FALSE;
1577 gl_date_found := TRUE;
1578
1579 /*
1580 --SELECT aba.account_type
1581 SELECT aba.account_classification
1582 INTO account_type
1583 FROM ce_bank_branches_v abb,
1584 ce_bank_accts_gt_v aba --ce_bank_accounts_v aba
1585 WHERE aba.bank_branch_id = abb.branch_party_id
1586 AND abb.bank_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_name, abb.bank_name)
1587 AND abb.bank_branch_name = nvl(CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,abb.bank_branch_name)
1588 AND aba.bank_account_num = CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num
1589 AND aba.bank_account_id = aba_bank_account_id;
1590 */
1591 --
1592 -- 1. Bank account
1593 --
1594 validate_bank_account(aba_bank_currency, aba_bank_check_digits, bank_error);
1595
1596 --
1597 -- 2. The GL date (Must be Open or Future Enterable in AP OR AR)
1598 -- bug 3676745 MO/BA uptake
1599 -- If the bank account is also used for xtr or payroll, do not check GL DATE
1600
1601 IF (CE_AUTO_BANK_REC.G_intra_day_flag <> 'Y') THEN
1602 --IF (account_type <> 'PAYROLL') THEN
1603 IF (CE_AUTO_BANK_IMPORT.G_xtr_use_allowed_flag = 'N' and
1604 CE_AUTO_BANK_IMPORT.G_pay_use_allowed_flag = 'N') THEN
1605 gl_date_found :=
1606 (CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222)
1607 OR CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 200));
1608 IF (NOT gl_date_found) THEN
1609 error_found := CE_AUTO_BANK_IMPORT.header_error('CE_INVALID_PERIOD');
1610 END IF;
1611 END IF;
1612 END IF;
1613 --
1614 -- 3. Already existing in the CE_STATEMENT_HEADERS
1615 --
1616 IF (CE_AUTO_BANK_REC.G_intra_day_flag <> 'Y') THEN
1617 IF (r_statement_number IS NOT NULL) THEN
1618 duplicate_found := CE_AUTO_BANK_IMPORT.header_error('CE_PREV_IMPORT');
1619 END IF;
1620 END IF;
1621
1622 IF (bank_error) THEN
1623 error_found := TRUE;
1624 END IF;
1625 IF (CE_AUTO_BANK_REC.G_intra_day_flag <> 'Y') THEN
1626 IF (duplicate_found OR NOT gl_date_found) THEN
1627 error_found := TRUE;
1628 END IF;
1629 END IF;
1630
1631 IF l_DEBUG in ('Y', 'C') THEN
1632 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.header_validation');
1633 END IF;
1634 RETURN error_found;
1635 EXCEPTION
1636 WHEN OTHERS THEN
1637 IF l_DEBUG in ('Y', 'C') THEN
1638 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.header_validation');
1639 END IF;
1640 RAISE;
1641 RETURN FALSE;
1642 END header_validation;
1643
1644 /* ---------------------------------------------------------------------
1645 | PRIVATE FUNCTION |
1646 | line_validation |
1647 | |
1648 | DESCRIPTION |
1649 | Validate the statement line records |
1650 | on the interface tables for import errors. If any errors are |
1651 | detected, the statement will not be imported. |
1652 | |
1653 | CALLED BY |
1654 | import_process |
1655 | |
1656 | RETURNS |
1657 | aba_bank_account_id Id for bank account being imported |
1658 --------------------------------------------------------------------- */
1659 FUNCTION line_validation( csli_amount NUMBER,
1660 csli_user_exchange_rate_type VARCHAR2,
1661 csli_currency_code VARCHAR2,
1662 csli_exchange_rate_date DATE,
1663 csli_trx_date DATE,
1664 csli_line_number NUMBER,
1665 csli_trx_code VARCHAR2,
1666 aba_bank_currency VARCHAR2,
1667 r_trx_code_id NUMBER,
1668 r_start_date DATE,
1669 r_end_date DATE,
1670 r_exchange_rate_type VARCHAR2,
1671 r_user_conversion_type VARCHAR2,
1672 r_currency_code VARCHAR2) RETURN BOOLEAN IS
1673 error_found BOOLEAN;
1674 fixed_rate_yn VARCHAR2(30);
1675
1676 BEGIN
1677 IF l_DEBUG in ('Y', 'C') THEN
1678 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.line_validation');
1679 END IF;
1683 --
1680 error_found := FALSE;
1681 --
1682 -- 1. Line Amount
1684 IF (csli_amount IS NULL) THEN
1685 error_found := CE_AUTO_BANK_IMPORT.line_error(csli_line_number, 'CE_NO_AMOUNT');
1686 END IF;
1687
1688 --
1689 -- 2. Trx Code
1690 --
1691 -- Null csli_trx_code indicates that the statement interface line is missing
1692 -- a transaction code.
1693 --
1694 IF (csli_trx_code IS NULL) THEN
1695 error_found := CE_AUTO_BANK_IMPORT.line_error( csli_line_number, 'CE_MISSING_TRX_NUM');
1696
1697 --
1698 -- Non-Null csli_trx_code and null r_trx_code_id indicate transaction code is not
1699 -- defined for the bank.
1700 --
1701 -- bug 4435028 BAT, we cannot do this validation because there are multiple rows with same trx_code
1702
1703 --7194081 Uncommented.
1704 ELSIF (r_trx_code_id IS NULL) THEN
1705 error_found := CE_AUTO_BANK_IMPORT.line_error( csli_line_number, 'CE_INVALID_TRX_NUM');
1706
1707 --
1708 -- Check effective date of this transaction code.
1709 -- defined for the bank.
1710 --
1711 -- bug 4435028 BAT, we cannot do this validation because there are multiple rows with same trx_code
1712 -- cannot get start_date and end_date
1713
1714 --ELSIF ( CE_AUTO_BANK_IMPORT.G_cshi_statement_date NOT BETWEEN
1715 -- r_start_date AND r_end_date) THEN
1716 -- NVL(r_start_date, CE_AUTO_BANK_IMPORT.G_cshi_statement_date) AND
1717 -- NVL(r_end_date, CE_AUTO_BANK_IMPORT.G_cshi_statement_date)) THEN
1718
1719 -- error_found := CE_AUTO_BANK_IMPORT.line_error( csli_line_number, 'CE_INVALID_TRX_NUM_DATE');
1720
1721 END IF;
1722
1723 --
1724 -- 3.Exchange rate type
1725 --
1726 IF (csli_user_exchange_rate_type IS NOT NULL AND r_user_conversion_type IS NULL) THEN
1727 error_found := CE_AUTO_BANK_IMPORT.line_error( csli_line_number, 'CE_INVALID_EXCHANGE_TYPE');
1728 END IF;
1729
1730 --
1731 -- 4. Currency code
1732 --
1733 IF (csli_currency_code IS NOT NULL AND r_currency_code IS NULL) THEN
1734 error_found := CE_AUTO_BANK_IMPORT.line_error( csli_line_number, 'CE_INVALID_CURRENCY');
1735 END IF;
1736
1737 --
1738 -- 5. EMU Fixed type is only for valid curr and xdate
1739 --
1740 BEGIN
1741
1742 /* -- bug 3676745, if p_option = 'IMPORT', no org information to get CE_AUTO_BANK_REC.G_functional_currency
1743
1744 IF l_DEBUG in ('Y', 'C') THEN
1745 cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency = '|| CE_AUTO_BANK_REC.G_functional_currency ||
1746 ',CE_AUTO_BANK_REC.G_interface_purge_flag = ' ||CE_AUTO_BANK_REC.G_interface_purge_flag ||
1747 ',CE_AUTO_BANK_REC.G_interface_archive_flag = '|| CE_AUTO_BANK_REC.G_interface_archive_flag );
1748 END IF;
1749 IF (CE_AUTO_BANK_REC.G_org_id is null and
1750 CE_AUTO_BANK_REC.G_legal_entity_id is null and
1751 CE_AUTO_BANK_REC.G_functional_currency is null ) THEN
1752 SELECT g.currency_code,
1753 NVL(s.interface_purge_flag,'N'),
1754 NVL(s.interface_archive_flag,'N')
1755 INTO CE_AUTO_BANK_REC.G_functional_currency,
1756 CE_AUTO_BANK_REC.G_interface_purge_flag ,
1757 CE_AUTO_BANK_REC.G_interface_archive_flag
1758 FROM CE_SYSTEM_PARAMETERS_ALL s,
1759 GL_SETS_OF_BOOKS g,
1760 ce_bank_accts_gt_v ba --ce_bank_accounts_v ba
1761 WHERE ba.bank_account_id = CE_AUTO_BANK_IMPORT.G_bank_account_id
1762 and ba.ACCOUNT_OWNER_ORG_ID = s.legal_entity_id
1763 and s.set_of_books_id = g.set_of_books_id;
1764
1765 END IF;
1766 */
1767 IF l_DEBUG in ('Y', 'C') THEN
1768 cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency = '|| CE_AUTO_BANK_REC.G_functional_currency ||
1769 ',CE_AUTO_BANK_REC.G_interface_purge_flag = ' ||CE_AUTO_BANK_REC.G_interface_purge_flag ||
1770 ',CE_AUTO_BANK_REC.G_interface_archive_flag = '|| CE_AUTO_BANK_REC.G_interface_archive_flag );
1771 END IF;
1772
1773 fixed_rate_yn := gl_currency_api.is_fixed_rate(
1774 CE_AUTO_BANK_REC.G_functional_currency,
1775 nvl(csli_currency_code,aba_bank_currency),
1776 nvl(csli_exchange_rate_date,csli_trx_date));
1777 EXCEPTION
1778 WHEN OTHERS THEN cep_standard.debug('cannot get relationship for the give curr');
1779 fixed_rate_yn := 'N';
1780 END;
1781
1782 IF (fixed_rate_yn = 'N') AND (r_exchange_rate_type = 'EMU FIXED') THEN
1783 IF l_DEBUG in ('Y', 'C') THEN
1784 cep_standard.debug('line_validation: ' || 'emu fixed is not allowed for this curr type');
1785 END IF;
1786 error_found := line_error(csli_line_number, 'CE_EMU_FIXED_NOT_ALLOWED');
1787 END IF;
1788
1789 IF l_DEBUG in ('Y', 'C') THEN
1790 cep_standard.debug('<<CE_AUTO_BANK_IMPORT.line_validation');
1791 END IF;
1792 RETURN error_found;
1793
1794 EXCEPTION
1795 WHEN OTHERS THEN
1796 IF l_DEBUG in ('Y', 'C') THEN
1797 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.line_validation');
1798 END IF;
1799 RAISE;
1800 RETURN FALSE;
1801 END line_validation;
1802
1803 /* ---------------------------------------------------------------------
1804 | PRIVATE PROCEDURE |
1805 | import_process |
1806 | |
1807 | CALLS |
1808 | statement_import |
1809 | |
1810 | CALLED BY |
1811 | CE_AUTO_BANK_REC.statement |
1815 aba_bank_currency CE_BANK_ACCOUNTS.currency_code%TYPE;
1812 --------------------------------------------------------------------- */
1813 PROCEDURE import_process IS
1814 aba_bank_account_id CE_BANK_ACCOUNTS.bank_account_id%TYPE;
1816 aba_bank_account_name CE_BANK_ACCOUNTS.bank_account_name%TYPE;
1817 aba_bank_check_digits CE_BANK_ACCOUNTS.check_digits%TYPE;
1818 csli_line_number CE_STATEMENT_LINES_INTERFACE.line_number%TYPE;
1819 csli_amount CE_STATEMENT_LINES_INTERFACE.amount%TYPE;
1820 csli_trx_code CE_STATEMENT_LINES_INTERFACE.trx_code%TYPE;
1821 csli_user_exchange_rate_type CE_STATEMENT_LINES_INTERFACE.user_exchange_rate_type%TYPE;
1822 csli_currency_code CE_STATEMENT_LINES_INTERFACE.currency_code%TYPE;
1823 csli_exchange_rate_date CE_STATEMENT_LINES_INTERFACE.exchange_rate_date%TYPE;
1824 csli_trx_date CE_STATEMENT_LINES_INTERFACE.trx_date%TYPE;
1825 csli_dr_sum CE_STATEMENT_LINES_INTERFACE.amount%TYPE;
1826 csli_cr_sum CE_STATEMENT_LINES_INTERFACE.amount%TYPE;
1827 csli_dr_count CE_STATEMENT_LINES_INTERFACE.amount%TYPE;
1828 csli_cr_count CE_STATEMENT_LINES_INTERFACE.amount%TYPE;
1829 r_statement_number CE_STATEMENT_HEADERS.statement_number%TYPE;
1830 r_user_conversion_type GL_DAILY_CONVERSION_TYPES.user_conversion_type%TYPE;
1831 r_exchange_rate_type GL_DAILY_CONVERSION_TYPES.conversion_type%TYPE;
1832 r_currency_code FND_CURRENCIES.currency_code%TYPE;
1833 r_trx_code_id CE_TRANSACTION_CODES.transaction_code_id%TYPE;
1834 r_start_date CE_TRANSACTION_CODES.start_date%TYPE;
1835 r_end_date CE_TRANSACTION_CODES.end_date%TYPE;
1836 line_error_found BOOLEAN;
1837 l_error_found BOOLEAN;
1838 header_error_found BOOLEAN;
1839 lock_error_found BOOLEAN;
1840 control_error_found BOOLEAN;
1841 cshi_rowid VARCHAR2(100);
1842 csli_rowid VARCHAR2(100);
1843 l_errbuf varchar2(100);
1844 l_retcode number;
1845 x_account_result varchar2(100);
1846
1847 BEGIN
1848
1849 --FND_FILE.put_line(FND_FILE.LOG, 'ceabrimb');
1850
1851 IF l_DEBUG in ('Y', 'C') THEN
1852 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.import_process ');
1853 END IF;
1854
1855 -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
1856 CEP_STANDARD.init_security;
1857
1858 OPEN branch_cursor( CE_AUTO_BANK_REC.G_bank_branch_id,
1859 CE_AUTO_BANK_REC.G_bank_account_id );
1860 LOOP
1861 FETCH branch_cursor INTO CE_AUTO_BANK_IMPORT.G_bank_account_id,
1862 CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID,
1863 CE_AUTO_BANK_IMPORT.G_xtr_use_allowed_flag,
1864 CE_AUTO_BANK_IMPORT.G_pay_use_allowed_flag;
1865 aba_bank_account_id := CE_AUTO_BANK_IMPORT.G_bank_account_id;
1866
1867 -- 1/20/05 Did not enter organization (le_id and org_id missing)
1868 -- BUG 4914608
1869 /* IF (CE_AUTO_BANK_REC.G_legal_entity_id is null and CE_AUTO_BANK_REC.G_org_id is null) THEN
1870 select ACCOUNT_OWNER_ORG_ID
1871 into X_le_id
1872 from ce_BANK_ACCOUNTS
1873 where BANK_ACCOUNT_ID = aba_bank_account_id; */
1874 --IF (CE_AUTO_BANK_REC.G_legal_entity_id is null and CE_AUTO_BANK_REC.G_org_id is null) THEN
1875 IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) and
1876 (CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID is not null) THEN
1877
1878 CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
1879 CE_AUTO_BANK_REC.G_set_of_books_id,
1880 CE_AUTO_BANK_REC.G_cashbook_begin_date,
1881 CE_AUTO_BANK_REC.G_show_cleared_flag,
1882 CE_AUTO_BANK_REC.G_show_void_payment_flag,
1883 CE_AUTO_BANK_REC.G_line_autocreation_flag,
1884 CE_AUTO_BANK_REC.G_interface_purge_flag,
1885 CE_AUTO_BANK_REC.G_interface_archive_flag,
1886 CE_AUTO_BANK_REC.G_lines_per_commit,
1887 CE_AUTO_BANK_REC.G_functional_currency,
1888 CE_AUTO_BANK_REC.G_sob_short_name,
1889 CE_AUTO_BANK_REC.G_account_period_type,
1890 CE_AUTO_BANK_REC.G_user_exchange_rate_type,
1891 CE_AUTO_BANK_REC.G_chart_of_accounts_id,
1892 CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1893 CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1894 CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1895 CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1896 CE_AUTO_BANK_IMPORT.BA_OWNER_LE_ID
1897 );
1898 END IF;
1899
1900 IF l_DEBUG in ('Y', 'C') THEN
1901 cep_standard.debug('aba_bank_account_id =' ||aba_bank_account_id);
1902 END IF;
1903
1904 EXIT WHEN branch_cursor%NOTFOUND OR branch_cursor%NOTFOUND IS NULL;
1905
1906 OPEN bank_cursor(CE_AUTO_BANK_REC.G_statement_number_from,
1907 CE_AUTO_BANK_REC.G_statement_number_to,
1908 CE_AUTO_BANK_REC.G_statement_date_from,
1909 CE_AUTO_BANK_REC.G_statement_date_to,
1910 CE_AUTO_BANK_IMPORT.G_bank_account_id);
1911 LOOP
1912 FETCH bank_cursor INTO CE_AUTO_BANK_IMPORT.G_cshi_rowid,
1913 CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
1914 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num,
1915 CE_AUTO_BANK_IMPORT.G_cshi_check_digits,
1916 CE_AUTO_BANK_IMPORT.G_cshi_control_begin_balance,
1917 CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance,
1918 CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance,
1919 CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance,
1920 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd,
1921 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd,
1922 CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd,
1923 CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd,
1924 CE_AUTO_BANK_IMPORT.G_cshi_one_day_float,
1928 CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr,
1925 CE_AUTO_BANK_IMPORT.G_cshi_two_day_float,
1926 CE_AUTO_BANK_IMPORT.G_cshi_intra_day_flag,
1927 CE_AUTO_BANK_IMPORT.G_cshi_subsidiary_flag,
1929 CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr,
1930 CE_AUTO_BANK_IMPORT.G_cshi_control_dr_line_count,
1931 CE_AUTO_BANK_IMPORT.G_cshi_control_cr_line_count,
1932 CE_AUTO_BANK_IMPORT.G_cshi_control_line_count,
1933 CE_AUTO_BANK_IMPORT.G_cshi_attribute_category,
1934 CE_AUTO_BANK_IMPORT.G_cshi_attribute1,
1935 CE_AUTO_BANK_IMPORT.G_cshi_attribute2,
1936 CE_AUTO_BANK_IMPORT.G_cshi_attribute3,
1937 CE_AUTO_BANK_IMPORT.G_cshi_attribute4,
1938 CE_AUTO_BANK_IMPORT.G_cshi_attribute5,
1939 CE_AUTO_BANK_IMPORT.G_cshi_attribute6,
1940 CE_AUTO_BANK_IMPORT.G_cshi_attribute7,
1941 CE_AUTO_BANK_IMPORT.G_cshi_attribute8,
1942 CE_AUTO_BANK_IMPORT.G_cshi_attribute9,
1943 CE_AUTO_BANK_IMPORT.G_cshi_attribute10,
1944 CE_AUTO_BANK_IMPORT.G_cshi_attribute11,
1945 CE_AUTO_BANK_IMPORT.G_cshi_attribute12,
1946 CE_AUTO_BANK_IMPORT.G_cshi_attribute13,
1947 CE_AUTO_BANK_IMPORT.G_cshi_attribute14,
1948 CE_AUTO_BANK_IMPORT.G_cshi_attribute15,
1949 CE_AUTO_BANK_IMPORT.G_cshi_statement_date,
1950 CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,
1951 CE_AUTO_BANK_IMPORT.G_cshi_bank_name,
1952 CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name,
1953 CE_AUTO_BANK_IMPORT.G_cshi_currency_code,
1954 --CE_AUTO_BANK_IMPORT.G_cshi_org_id,
1955 r_statement_number,
1956 aba_bank_account_name,
1957 aba_bank_currency,
1958 aba_bank_check_digits;
1959 IF l_DEBUG in ('Y', 'C') THEN
1960 cep_standard.debug('CE_AUTO_BANK_IMPORT.G_cshi_statement_number ='||CE_AUTO_BANK_IMPORT.G_cshi_statement_number);
1961 END IF;
1962
1963
1964 EXIT WHEN bank_cursor%NOTFOUND OR bank_cursor%NOTFOUND IS NULL;
1965 --
1966 -- Delete all the line/header import errors
1967 --
1968 line_error_found := FALSE;
1969 l_error_found := FALSE;
1970 header_error_found := FALSE;
1971 lock_error_found := FALSE;
1972 control_error_found := FALSE;
1973 CE_HEADER_INTERFACE_ERRORS_PKG.delete_row(
1974 CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
1975 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num );
1976 CE_LINE_INTERFACE_ERRORS_PKG.delete_row(
1977 CE_AUTO_BANK_IMPORT.G_cshi_statement_number,
1978 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num, NULL);
1979 IF (lock_statement) THEN
1980 IF l_DEBUG in ('Y', 'C') THEN
1981 cep_standard.debug('aba_bank_account_id =' ||aba_bank_account_id);
1982 END IF;
1983
1984 header_error_found := header_validation(r_statement_number,
1985 aba_bank_currency,
1986 aba_bank_check_digits,
1987 aba_bank_account_id);
1988 CE_AUTO_BANK_IMPORT.G_dr_sum := 0;
1989 CE_AUTO_BANK_IMPORT.G_cr_sum := 0;
1990 CE_AUTO_BANK_IMPORT.G_dr_count := 0;
1991 CE_AUTO_BANK_IMPORT.G_cr_count := 0;
1992 CE_AUTO_BANK_IMPORT.G_total_count := 0;
1993 OPEN lines_cursor(CE_AUTO_BANK_IMPORT.G_cshi_statement_number, CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num);
1994 LOOP
1995 FETCH lines_cursor INTO csli_rowid,
1996 csli_line_number,
1997 csli_amount,
1998 csli_trx_code,
1999 csli_user_exchange_rate_type,
2000 csli_currency_code,
2001 csli_exchange_rate_date,
2002 csli_trx_date,
2003 csli_dr_sum,
2004 csli_dr_count,
2005 csli_cr_sum,
2006 csli_cr_count,
2007 r_trx_code_id,
2008 r_start_date,
2009 r_end_date,
2010 r_exchange_rate_type,
2011 r_user_conversion_type,
2012 r_currency_code;
2013 EXIT WHEN lines_cursor%NOTFOUND OR lines_cursor%NOTFOUND IS NULL;
2014 cep_standard.debug('>>CE_AUTO_BANK_IMPORT.fetch line: ' || csli_line_number);
2015 CE_AUTO_BANK_IMPORT.G_dr_sum := CE_AUTO_BANK_IMPORT.G_dr_sum + csli_dr_sum;
2016 CE_AUTO_BANK_IMPORT.G_cr_sum := CE_AUTO_BANK_IMPORT.G_cr_sum + csli_cr_sum;
2017 CE_AUTO_BANK_IMPORT.G_dr_count := CE_AUTO_BANK_IMPORT.G_dr_count + csli_dr_count;
2018 CE_AUTO_BANK_IMPORT.G_cr_count := CE_AUTO_BANK_IMPORT.G_cr_count + csli_cr_count;
2019 CE_AUTO_BANK_IMPORT.G_total_count := CE_AUTO_BANK_IMPORT.G_total_count + 1;
2020 IF (lock_statement_line(csli_rowid)) THEN
2021 l_error_found := line_validation( csli_amount,
2022 csli_user_exchange_rate_type,
2023 csli_currency_code,
2024 csli_exchange_rate_date,
2025 csli_trx_date,
2026 csli_line_number,
2027 csli_trx_code,
2028 aba_bank_currency,
2029 r_trx_code_id,
2030 r_start_date,
2031 r_end_date,
2032 r_exchange_rate_type,
2033 r_user_conversion_type,
2034 r_currency_code);
2035 IF (l_error_found) THEN
2036 line_error_found := TRUE;
2037 END IF;
2038 ELSE
2039 line_error_found := CE_AUTO_BANK_IMPORT.line_error(csli_line_number, 'CE_STATEMENT_LINT_LOCK');
2040 END IF;
2041 END LOOP; -- lines_cursor
2042 close lines_cursor;
2043 control_error_found := validate_control_totals;
2044 ELSE -- Statement header is locked
2045 lock_error_found := CE_AUTO_BANK_IMPORT.header_error('CE_STATEMENT_HINT_LOCK');
2049 NULL;
2046 END IF; -- Statement header lock
2047 IF (header_error_found OR line_error_found OR lock_error_found) THEN
2048 IF (lock_error_found) THEN
2050 ELSE
2051 update_header_status('E');
2052 END IF;
2053 ELSE
2054
2055 -- bug 3676745 MO/BA uptake
2056 -- if the bank account is defined as a treasury use bank account, the bank balances
2057 -- can be uploaded to treasury and if it is a shared account (bug 4932152)
2058
2059 xtr_shared_account(x_account_result);
2060
2061 IF ((x_account_result = 'XTR1_SHARED' AND nvl(G_cshi_intra_day_flag,'N') <> 'Y') or
2062 (CE_AUTO_BANK_IMPORT.G_xtr_use_allowed_flag = 'Y' AND nvl(G_cshi_intra_day_flag,'N') <> 'Y')) THEN
2063 IF (transfer_bank_balances) THEN
2064 IF (transfer_header(aba_bank_account_id,aba_bank_account_name)) THEN
2065 transfer_lines;
2066 IF (CE_AUTO_BANK_REC.G_interface_archive_flag = 'Y') THEN
2067 archive_header;
2068 archive_lines;
2069 END IF;
2070 IF (CE_AUTO_BANK_REC.G_interface_purge_flag = 'Y') THEN
2071 purge_data;
2072 END IF;
2073 END IF;
2074 END IF;
2075 --ELSIF (x_account_result = 'XTR1_AP' OR nvl(G_cshi_intra_day_flag,'N') = 'Y') THEN
2076 ELSIF (CE_AUTO_BANK_IMPORT.G_xtr_use_allowed_flag = 'N' OR nvl(G_cshi_intra_day_flag,'N') = 'Y') THEN
2077 IF (transfer_header(aba_bank_account_id,aba_bank_account_name)) THEN
2078 transfer_lines;
2079 IF (CE_AUTO_BANK_REC.G_interface_archive_flag = 'Y') THEN
2080 archive_header;
2081 archive_lines;
2082 END IF;
2083 IF (CE_AUTO_BANK_REC.G_interface_purge_flag = 'Y') THEN
2084 purge_data;
2085 END IF;
2086 END IF;
2087 END IF; -- xtr_shared_account
2088
2089 END IF; -- error_found
2090
2091 -- bug 2732755
2092 CE_AUTO_BANK_IMPORT.G_cshi_rowid := null;
2093 CE_AUTO_BANK_IMPORT.G_cshi_statement_number := null;
2094 CE_AUTO_BANK_IMPORT.G_cshi_bank_account_num := null;
2095 CE_AUTO_BANK_IMPORT.G_cshi_check_digits := null;
2096 CE_AUTO_BANK_IMPORT.G_cshi_control_begin_balance := null;
2097 CE_AUTO_BANK_IMPORT.G_cshi_control_end_balance := null;
2098 CE_AUTO_BANK_IMPORT.G_cshi_cashflow_balance := null;
2099 CE_AUTO_BANK_IMPORT.G_cshi_int_calc_balance := null;
2100 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_mtd := null;
2101 CE_AUTO_BANK_IMPORT.G_cshi_close_ledger_ytd := null;
2102 CE_AUTO_BANK_IMPORT.G_cshi_close_available_mtd := null;
2103 CE_AUTO_BANK_IMPORT.G_cshi_close_available_ytd := null;
2104 CE_AUTO_BANK_IMPORT.G_cshi_one_day_float := null;
2105 CE_AUTO_BANK_IMPORT.G_cshi_two_day_float := null;
2106 CE_AUTO_BANK_IMPORT.G_cshi_intra_day_flag := null;
2107 CE_AUTO_BANK_IMPORT.G_cshi_subsidiary_flag := null;
2108 CE_AUTO_BANK_IMPORT.G_cshi_control_total_dr := null;
2109 CE_AUTO_BANK_IMPORT.G_cshi_control_total_cr := null;
2110 CE_AUTO_BANK_IMPORT.G_cshi_control_dr_line_count := null;
2111 CE_AUTO_BANK_IMPORT.G_cshi_control_cr_line_count := null;
2112 CE_AUTO_BANK_IMPORT.G_cshi_control_line_count := null;
2113 CE_AUTO_BANK_IMPORT.G_cshi_attribute_category := null;
2114 CE_AUTO_BANK_IMPORT.G_cshi_attribute1 := null;
2115 CE_AUTO_BANK_IMPORT.G_cshi_attribute2 := null;
2116 CE_AUTO_BANK_IMPORT.G_cshi_attribute3 := null;
2117 CE_AUTO_BANK_IMPORT.G_cshi_attribute4 := null;
2118 CE_AUTO_BANK_IMPORT.G_cshi_attribute5 := null;
2119 CE_AUTO_BANK_IMPORT.G_cshi_attribute6 := null;
2120 CE_AUTO_BANK_IMPORT.G_cshi_attribute7 := null;
2121 CE_AUTO_BANK_IMPORT.G_cshi_attribute8 := null;
2122 CE_AUTO_BANK_IMPORT.G_cshi_attribute9 := null;
2123 CE_AUTO_BANK_IMPORT.G_cshi_attribute10 := null;
2124 CE_AUTO_BANK_IMPORT.G_cshi_attribute11 := null;
2125 CE_AUTO_BANK_IMPORT.G_cshi_attribute12 := null;
2126 CE_AUTO_BANK_IMPORT.G_cshi_attribute13 := null;
2127 CE_AUTO_BANK_IMPORT.G_cshi_attribute14 := null;
2128 CE_AUTO_BANK_IMPORT.G_cshi_attribute15 := null;
2129 CE_AUTO_BANK_IMPORT.G_cshi_statement_date := null;
2130 CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name := null;
2131 CE_AUTO_BANK_IMPORT.G_cshi_bank_name := null;
2132 CE_AUTO_BANK_IMPORT.G_cshi_bank_branch_name := null;
2133 CE_AUTO_BANK_IMPORT.G_cshi_currency_code := null;
2134 --CE_AUTO_BANK_IMPORT.G_cshi_org_id := null;
2135 r_statement_number := null;
2136 aba_bank_account_name := null;
2137 aba_bank_currency := null;
2138 aba_bank_check_digits := null;
2139
2140 END LOOP; -- bank_cursor
2141 close bank_cursor;
2142
2143 END LOOP; -- branch_cursor
2144 close branch_cursor;
2145
2146 EXCEPTION
2147 WHEN OTHERS THEN
2148 IF branch_cursor%ISOPEN THEN
2149 close branch_cursor;
2150 END IF;
2151 IF bank_cursor%ISOPEN THEN
2152 close bank_cursor;
2153 END IF;
2154 IF lines_cursor%ISOPEN THEN
2155 close lines_cursor;
2156 END IF;
2157 IF l_DEBUG in ('Y', 'C') THEN
2158 cep_standard.debug('EXCEPTION: CE_AUTO_BANK_IMPORT.import_process');
2159 END IF;
2160 RAISE;
2161 END import_process;
2162
2163 END CE_AUTO_BANK_IMPORT;