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