[Home] [Help]
PACKAGE BODY: APPS.CE_BANK_STATEMENT_LOADER
Source
1 PACKAGE BODY CE_BANK_STATEMENT_LOADER AS
2 /* $Header: cebsldrb.pls 120.34.12010000.2 2008/08/10 14:27:08 csutaria ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 --l_DEBUG varchar2(1) := 'Y';
6
7 /*bug5100563*/
8 n number:=0;
9 G_last_val2 varchar2(30);
10 G_last_val3 varchar2(30);
11 G_last_val4 varchar2(30);
12 /*bug5100563*/
13
14 --bug5124547
15 G_lcnt number :=0;
16
17
18 /* 2421690
19 Start of Code Fix */
20
21 FUNCTION body_revision RETURN VARCHAR2 IS
22 BEGIN
23
24 RETURN '$Revision: 120.34.12010000.2 $';
25
26 END body_revision;
27
28 FUNCTION spec_revision RETURN VARCHAR2 IS
29 BEGIN
30
31 RETURN G_spec_revision;
32
33 END spec_revision;
34
35 /* End of Code Fix */
36
37 --
38 -- The following Procedures and Functions are shared by BAI2 format and Non-BAI2 format loading.
39 --
40 -- Init_Hdr_Rec Insert_Hdr Lookup_Pos
41 -- Init_Line_Rec Insert_Line Lookup_Val
42 --
43 -- The following Procedures and Functions are for BAI2 format only.
44 --
45 -- Load_BAI2 Decode_Line_BAI
46 -- Decode_Hdr_BAI
47 --
48 -- The following Procedures and Functions are for Non-BAI2 format only.
49 --
50 -- Load_Others Decode_Line_Other Hdr_Or_Line
51 -- Decode_Hdr_Other
52 --
53
54
55
56 /* ---------------------------------------------------------------------
57 | PRIVATE PROCEDURE |
58 | Init_Hdr_Rec |
59 | |
60 | DESCRIPTION |
61 | Initialize Header variables after Insertion of the Header |
62 | Record. |
63 | |
64 | CALLED BY |
65 | Load_BAI2, Load_Others |
66 | REQUIRES |
67 | |
68 | HISTORY |
69 | 19-MAY-1999 Created BHCHUNG |
70 --------------------------------------------------------------------- */
71 PROCEDURE Init_Hdr_Rec IS
72 BEGIN
73 G_bank_name := NULL;
74 G_bank_branch_name := NULL;
75 G_statement_date := NULL;
76 G_control_begin_balance := NULL;
77 G_control_end_balance := NULL;
78 G_cashflow_balance := NULL;
79 G_int_calc_balance := NULL;
80 G_average_close_ledger_mtd :=NULL;
81 G_average_close_ledger_ytd :=NULL;
82 G_average_close_available_mtd :=NULL;
83 G_average_close_available_ytd :=NULL;
84 G_one_day_float := NULL;
85 G_two_day_float := NULL;
86 G_control_total_dr := NULL;
87 G_control_total_cr := NULL;
88 G_control_dr_line_count := NULL;
89 G_control_cr_line_count := NULL;
90 G_control_line_count := NULL;
91 G_check_digits := NULL;
92 G_hdr_currency_code := NULL;
93 G_hdr_precision := NULL;
94 END Init_Hdr_Rec;
95
96 /* ---------------------------------------------------------------------
97 | PRIVATE PROCEDURE |
98 | Init_Line_Rec |
99 | |
100 | DESCRIPTION |
101 | Initialize Line variables after Insertion of the Line |
102 | Record. |
103 | |
104 | CALLED BY |
105 | Load_BAI2, Load_Others |
106 | REQUIRES |
107 | |
108 | HISTORY |
109 | 19-MAY-1999 Created BHCHUNG |
110 --------------------------------------------------------------------- */
111 PROCEDURE Init_Line_Rec IS
112 BEGIN
113 G_line_number := NULL;
114 G_trx_date := NULL;
115 G_trx_code := NULL;
116 G_effective_date := NULL;
117 G_trx_text := NULL;
118 G_invoice_text := NULL;
119 G_amount := NULL;
120 G_line_currency_code := NULL;
121 G_exchange_rate := NULL;
122 G_bank_trx_number := NULL;
123 G_customer_text := NULL;
124 G_user_exchange_rate_type := NULL;
125 G_exchange_rate_date := NULL;
126 G_original_amount := NULL;
127 G_charges_amount := NULL;
128 G_bank_account_text := NULL;
129 G_line_precision := NULL;
130 END Init_Line_Rec;
131
132 PROCEDURE Remove_Return_Char IS
133 BEGIN
134 UPDATE CE_STMT_INT_TMP
135 SET REC_NO = rtrim(REC_NO, '
'),
136 REC_ID_NO = rtrim(REC_ID_NO, '
'),
137 COLUMN1 = rtrim(COLUMN1, '
'),
138 COLUMN2 = rtrim(COLUMN2, '
'),
139 COLUMN3 = rtrim(COLUMN3, '
'),
140 COLUMN4 = rtrim(COLUMN4, '
'),
141 COLUMN5 = rtrim(COLUMN5, '
'),
142 COLUMN6 = rtrim(COLUMN6, '
'),
143 COLUMN7 = rtrim(COLUMN7, '
'),
144 COLUMN8 = rtrim(COLUMN8, '
'),
145 COLUMN9 = rtrim(COLUMN9, '
'),
146 COLUMN10 = rtrim(COLUMN10, '
'),
147 COLUMN11 = rtrim(COLUMN11, '
'),
148 COLUMN12 = rtrim(COLUMN12, '
'),
149 COLUMN13 = rtrim(COLUMN13, '
'),
150 COLUMN14 = rtrim(COLUMN14, '
'),
151 COLUMN15 = rtrim(COLUMN15, '
'),
152 COLUMN16 = rtrim(COLUMN16, '
'),
153 COLUMN17 = rtrim(COLUMN17, '
'),
154 COLUMN18 = rtrim(COLUMN18, '
'),
155 COLUMN19 = rtrim(COLUMN19, '
'),
156 COLUMN20 = rtrim(COLUMN20, '
'),
157 COLUMN21 = rtrim(COLUMN21, '
'),
158 COLUMN22 = rtrim(COLUMN22, '
'),
159 COLUMN23 = rtrim(COLUMN23, '
'),
160 COLUMN24 = rtrim(COLUMN24, '
'),
161 COLUMN25 = rtrim(COLUMN25, '
'),
162 COLUMN26 = rtrim(COLUMN26, '
'),
163 COLUMN27 = rtrim(COLUMN27, '
'),
164 COLUMN28 = rtrim(COLUMN28, '
'),
165 COLUMN29 = rtrim(COLUMN29, '
'),
166 COLUMN30 = rtrim(COLUMN30, '
'),
167 COLUMN31 = rtrim(COLUMN31, '
'),
168 COLUMN32 = rtrim(COLUMN32, '
'),
169 COLUMN33 = rtrim(COLUMN33, '
'),
170 COLUMN34 = rtrim(COLUMN34, '
'),
171 COLUMN35 = rtrim(COLUMN35, '
');
172 END Remove_Return_Char;
173
174 /* ---------------------------------------------------------------------
175 | PRIVATE PROCEDURE |
176 | CONV_TO_DATE |
177 | |
178 | DESCRIPTION |
179 | Convert string to date. If the conversion fails then log |
180 | error. |
181 | |
182 | CALLED BY |
183 | |
184 | REQUIRES |
185 | |
186 | HISTORY |
187 | 19-AUG-2000 Created BHCHUNG |
188 --------------------------------------------------------------------- */
189
190 FUNCTION CONV_TO_DATE(X_date VARCHAR2) RETURN DATE IS
191 BEGIN
192 RETURN TO_DATE(X_date, G_date_format);
193 EXCEPTION
194 WHEN OTHERS THEN
195 FND_MESSAGE.set_name('CE', 'CE_CANNOT_CONVERT_DATE');
196 FND_MESSAGE.set_token('DATE', X_date);
197 FND_MESSAGE.set_token('FORMAT', G_date_format);
198 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(G_rec_no, 0), fnd_message.get, 'E');
199 END;
200
201
202 FUNCTION CONV_TIMESTAMP(X_date VARCHAR2) RETURN DATE IS
203 BEGIN
204 RETURN TO_DATE(X_date, G_date_format || ' ' || G_timestamp_format);
205 EXCEPTION
206 WHEN OTHERS THEN
207 FND_MESSAGE.set_name('CE', 'CE_CANNOT_CONVERT_DATE');
208 FND_MESSAGE.set_token('DATE', X_date);
209 FND_MESSAGE.set_token('FORMAT', G_date_format);
210 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(G_rec_no, 0), fnd_message.get, 'E');
211 END;
212
213
214 FUNCTION Is_Number (X_string VARCHAR2) RETURN BOOLEAN IS
215 l_dummy NUMBER;
216 BEGIN
217 l_dummy := to_number(X_string);
218 RETURN TRUE;
219 EXCEPTION
220 WHEN OTHERS THEN
221 RETURN FALSE;
222 END;
223
224
225 /* ---------------------------------------------------------------------
226 | PRIVATE FUNCTION |
227 | Valid_Statement |
228 | |
229 | DESCRIPTION |
230 | Check if this statement is the one user wants to |
231 | import/autoreconcile. |
232 | |
233 | CALLED BY |
234 | Insert_Hdr, Insert_Line |
235 | REQUIRES |
236 | |
237 | HISTORY |
238 | 19-MAY-1999 Created BHCHUNG |
239 --------------------------------------------------------------------- */
240 FUNCTION Valid_Statement RETURN BOOLEAN IS
241 l_cnt NUMBER := 1;
242 BEGIN
243 --
244 -- Check if this statement is the one user wants to import/autoreconcile.
245 --
246 IF G_bank_account_id IS NOT NULL THEN
247 SELECT count(*)
248 INTO l_cnt
249 FROM CE_BANK_ACCOUNTS --FROM AP_BANK_ACCOUNTS_ALL
250 WHERE bank_account_id = G_bank_account_id
251 AND bank_account_num = G_bank_account_num;
252 ELSIF G_bank_branch_id IS NOT NULL THEN
253 SELECT count(*)
254 INTO l_cnt
255 FROM CE_BANK_BRANCHES_V --FROM AP_BANK_BRANCHES
256 WHERE branch_party_id = G_bank_branch_id
257 AND bank_branch_name = G_bank_branch_name;
258 END IF;
259
260 IF l_cnt = 0 THEN
261 RETURN FALSE;
262 ELSE
263 RETURN TRUE;
264 END IF;
265 END Valid_Statement;
266
267
268 /* ---------------------------------------------------------------------
269 | PRIVATE PROCEDURE |
270 | Delete_Orphaned_Lines |
271 | |
272 | DESCRIPTION |
273 | Delete Orphaned Lines. |
274 | |
275 | CALLED BY |
276 | Load_Others |
277 | REQUIRES |
278 | |
279 | HISTORY |
280 | 12-12-2001 Created HEHAN |
281 --------------------------------------------------------------------- */
282 PROCEDURE Delete_Orphaned_Lines IS
283 l_bank_account_num ce_statement_lines_interface.bank_account_num%TYPE;
284 l_statement_number ce_statement_lines_interface.statement_number%TYPE;
285 h_rec_cnt NUMBER;
286
287 CURSOR l_cursor IS
288 SELECT bank_account_num, statement_number
289 FROM ce_statement_lines_interface;
290
291 BEGIN
292 OPEN l_cursor;
293 LOOP
294 fetch l_cursor into l_bank_account_num, l_statement_number;
295 EXIT WHEN l_cursor%NOTFOUND;
296
297 SELECT count (1)
298 INTO h_rec_cnt
299 FROM ce_statement_headers_int
300 WHERE bank_account_num = l_bank_account_num
301 AND statement_number = l_statement_number;
302
303 IF h_rec_cnt = 0 THEN
304 DELETE FROM ce_statement_lines_interface
305 WHERE bank_account_num = l_bank_account_num
306 AND statement_number = l_statement_number;
307
308 END IF;
309 END LOOP;
310 CLOSE l_cursor;
311 END Delete_Orphaned_Lines;
312
313 /* ---------------------------------------------------------------------
314 | PRIVATE PROCEDURE |
315 | Validate_Subsidiary_Account |
316 | |
317 | DESCRIPTION |
318 | Check if the bank account is of type Subsidiary |
319 | |
320 | CALLED BY |
321 | Insert_Hdr |
322 | REQUIRES |
323 | |
324 | HISTORY |
325 | 25-MAY-2005 Created JIKUMAR |
326 --------------------------------------------------------------------- */
327 PROCEDURE Validate_Subsidiary_Account IS
328 l_rec_cnt NUMBER;
329
330 BEGIN
331 SELECT count(1)
332 INTO l_rec_cnt
333 FROM CE_BANK_ACCOUNTS BA,
334 CE_BANK_BRANCHES_V BB
335 WHERE BA.ACCOUNT_CLASSIFICATION = 'SUBSIDIARY' AND
336 BB.branch_party_id = BA.bank_branch_id AND
337 BA.bank_account_num = G_bank_account_num AND
338 BB.BANK_BRANCH_NAME = nvl(G_bank_branch_name,BB.BANK_BRANCH_NAME);
339
340 if l_rec_cnt = 1 THEN
341 G_subsidiary_flag := 'Y';
342 else
343 G_subsidiary_flag := 'N';
344 end if;
345
346 EXCEPTION
347 WHEN OTHERS THEN
348 G_subsidiary_flag := 'N';
349 END Validate_Subsidiary_Account;
350
351 /* ---------------------------------------------------------------------
352 | PRIVATE PROCEDURE |
353 | Insert_Hdr |
354 | |
355 | DESCRIPTION |
356 | Insert Header Record to Interface Table. |
357 | |
358 | CALLED BY |
359 | Load_BAI2, Load_Others |
360 | REQUIRES |
361 | |
362 | HISTORY |
363 | 19-MAY-1999 Created BHCHUNG |
364 --------------------------------------------------------------------- */
365 PROCEDURE Insert_Hdr IS
366 l_rec_cnt NUMBER;
367 l_err NUMBER;
368 l_request_id NUMBER;
369 l_program_name VARCHAR2(30);
370 l_debug_file VARCHAR2(30);
371 l_statement_number VARCHAR2(50);
372 G_conc_req_id NUMBER;
373 l_req_data VARCHAR(30);
374 ldr_exception EXCEPTION;
375
376 errbuf VARCHAR2(256);
377 retcode NUMBER;
378
379 CURSOR C_error IS
380 SELECT count(*)
381 FROM CE_SQLLDR_ERRORS
382 WHERE statement_number = G_statement_number
383 AND bank_account_num = G_bank_account_num;
384 BEGIN
385 IF l_DEBUG in ('Y', 'C') THEN
386 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Hdr');
387
391 cep_standard.debug('Bank Name -'||G_bank_name||'.');
388 cep_standard.debug('Statement Number -'||G_statement_number||'.');
389 cep_standard.debug('Bank Account Num -'||G_bank_account_num||'.');
390 cep_standard.debug('Statement_date -'||G_statement_date||'.');
392 cep_standard.debug('Bank Branch Name -'||G_bank_branch_name||'.');
393 cep_standard.debug('Begin Bal -'||G_control_begin_balance||'.');
394 cep_standard.debug('End Bal -'||G_control_end_balance||'.');
395 cep_standard.debug('Cashflow Bal -'||G_cashflow_balance||'.');
396 cep_standard.debug('Int Calc Bal -'||G_int_calc_balance||'.');
397 cep_standard.debug('Average close ledger mtd -'||G_average_close_ledger_mtd||'.');
398 cep_standard.debug('Average close ledger ytd -'||G_average_close_ledger_ytd||'.');
399 cep_standard.debug('Average close available mtd -'||G_average_close_available_mtd||'.');
400 cep_standard.debug('Average close available ytd -'||G_average_close_available_ytd||'.');
401 cep_standard.debug('One Day Float -'||G_one_day_float||'.');
402 cep_standard.debug('Two Day Float -'||G_two_day_float||'.');
403 cep_standard.debug('Total DR -'||G_control_total_dr||'.');
404 cep_standard.debug('Total CR -'||G_control_total_cr||'.');
405 cep_standard.debug('Total DR Line cnt -'||G_control_dr_line_count||'.');
406 cep_standard.debug('Total CR Line cnt -'||G_control_cr_line_count||'.');
407 cep_standard.debug('Currency Code -'||G_hdr_currency_code||'.');
408 cep_standard.debug('Bank Account ID -'||to_char(G_sub_account_id)||'.');
409 cep_standard.debug('Bank Branch ID -'||to_char(G_sub_branch_id)||'.');
410 cep_standard.debug('GL Date -'||to_char(G_gl_date, 'DD-MON-YY')||'.');
411 cep_standard.debug('Org ID -'||G_org_id||'.');
412 cep_standard.debug('Format Type -'||G_format_type||'.');
413 END IF;
414
415 IF G_format_type = 'SWIFT940' THEN
416 l_statement_number := G_statement_number || ' - ' || to_char(G_statement_date);
417
418 /* Bug 3417789 added the IF condition - start code fix*/
419 IF (trunc(sysdate) <> trunc(G_statement_date)) THEN
420 SELECT count(1)
421 INTO G_total_line_deleted
422 FROM ce_statement_lines_interface
423 WHERE statement_number = l_statement_number
424 AND bank_account_num = G_bank_account_num;
425
426 DELETE ce_statement_lines_interface
427 WHERE statement_number = l_statement_number
428 AND bank_account_num = G_bank_account_num;
429
430 UPDATE ce_statement_lines_interface
431 SET statement_number = l_statement_number
432 WHERE statement_number = G_statement_number || ' - ' || to_char(sysdate);
433 END IF;
434 /* Bug 3417789 - End code fix */
435 ELSE
436 l_statement_number := G_statement_number;
437 END IF;
438
439 IF Valid_statement THEN
440 --
441 -- Overwrite the existing bank statement of the same bank account and statement number.
442 --
443 -- bug 3676745 MO and BA uptake
444 /*
445 IF G_org_id is not null THEN
446 SELECT count(1)
447 INTO l_rec_cnt
448 FROM ce_statement_headers_int
449 WHERE bank_account_num = G_bank_account_num
450 AND statement_number = l_statement_number
451 AND nvl(org_id,G_org_id) = G_org_id;
452 ELSE*/
453 SELECT count(1)
454 INTO l_rec_cnt
455 FROM ce_statement_headers_int
456 WHERE bank_account_num = G_bank_account_num
457 AND statement_number = l_statement_number;
458 --END IF;
459 IF l_rec_cnt > 0 THEN
460 /*IF G_org_id is not null THEN
461 DELETE FROM ce_statement_headers_int
462 WHERE bank_account_num = G_bank_account_num
463 AND statement_number = l_statement_number
464 AND nvl(org_id,G_org_id) = G_org_id;
465 ELSE*/
466 DELETE FROM ce_statement_headers_int
467 WHERE bank_account_num = G_bank_account_num
468 AND statement_number = l_statement_number;
469 --END IF;
470 G_total_hdr_deleted := G_total_hdr_deleted + l_rec_cnt;
471 END IF;
472
473 -- bug 4337623 added for subsidiary bank accounts
474 validate_subsidiary_account();
475
476 INSERT INTO ce_statement_headers_int(
477 STATEMENT_NUMBER,
478 BANK_ACCOUNT_NUM,
479 STATEMENT_DATE,
480 BANK_NAME,
481 BANK_BRANCH_NAME,
482 CONTROL_BEGIN_BALANCE,
483 CONTROL_END_BALANCE,
484 CASHFLOW_BALANCE,
485 INT_CALC_BALANCE,
486 AVERAGE_CLOSE_LEDGER_MTD,
487 AVERAGE_CLOSE_LEDGER_YTD,
488 AVERAGE_CLOSE_AVAILABLE_MTD,
489 AVERAGE_CLOSE_AVAILABLE_YTD,
490 ONE_DAY_FLOAT,
491 TWO_DAY_FLOAT,
492 CONTROL_TOTAL_DR,
493 CONTROL_TOTAL_CR,
494 CONTROL_DR_LINE_COUNT,
495 CONTROL_CR_LINE_COUNT,
496 CONTROL_LINE_COUNT,
497 CHECK_DIGITS,
498 RECORD_STATUS_FLAG,
499 CURRENCY_CODE,
500 CREATED_BY,
501 CREATION_DATE ,
502 LAST_UPDATED_BY,
503 LAST_UPDATE_DATE,
504 --ORG_ID,
505 INTRA_DAY_FLAG,
506 SUBSIDIARY_FLAG)
507 VALUES(
508 rtrim(l_statement_number),
509 rtrim(G_bank_account_num),
510 G_statement_date,
511 rtrim(G_bank_name),
512 rtrim(G_bank_branch_name),
513 G_control_begin_balance,
514 G_control_end_balance,
515 G_cashflow_balance,
519 G_average_close_available_mtd,
516 G_int_calc_balance,
517 G_average_close_ledger_mtd,
518 G_average_close_ledger_ytd,
520 G_average_close_available_ytd,
521 G_one_day_float,
522 G_two_day_float,
523 G_control_total_dr,
524 G_control_total_cr,
525 G_control_dr_line_count,
526 G_control_cr_line_count,
527 G_control_line_count,
528 G_check_digits,
529 'N',
530 rtrim(rtrim(G_hdr_currency_code),'/'),
531 G_user_id,
532 SYSDATE,
533 G_user_id,
534 SYSDATE,
535 --G_org_id,
536 G_intra_day_flag,
537 G_subsidiary_flag);
538
539 --
540 -- Submit concurrent program for import and auto-reconciliation process.
541 --
542 IF G_process_option <> 'LOAD' THEN
543 --
544 -- The program will be submitted only if there is no error.
545 --
546 /* 3019931
547 Import should be done only for those records in the
548 statement interface that belong to the current Org */
549 --IF (FND_PROFILE.value('ORG_ID') = G_org_id) THEN
550 OPEN C_error;
551 FETCH C_error INTO l_rec_cnt;
552 CLOSE C_error;
553
554 IF l_rec_cnt = 0 THEN
555 IF G_process_option = 'ZALL' THEN
556 l_program_name := 'ARPLABIR';
557 ELSIF G_process_option = 'IMPORT' THEN
558 l_program_name := 'ARPLABIM';
559 END IF;
560
561 cep_standard.debug('Process Option -'||G_process_option);
562 cep_standard.debug('Program Name -'||l_program_name);
563
564 l_req_data := fnd_conc_global.request_data;
565
566 if(l_req_data IS NOT NULL)THEN
567 G_conc_req_id := to_number(l_req_data);
568 END IF;
569
570 IF G_debug_file IS NOT NULL THEN
571 l_debug_file := G_debug_file || '-REC';
572 END IF;
573 -- pass both org_id and legal_entity_id to CE_AUTO_BANK_REC for Import/AutoRecon
574 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
575 'CE',l_program_name,'','',NULL,
576 G_process_option,
577 to_char(G_sub_branch_id),
578 to_char(G_sub_account_id),
579 l_statement_number,
580 l_statement_number,
581 '',
582 '',
583 to_char(G_gl_date, 'YYYY/MM/DD HH24:MI:SS'),
584 G_org_id,
585 '',
586 to_char(G_receivables_trx_id),
587 to_char(G_payment_method_id),
588 G_nsf_handling,
589 G_display_debug,
590 G_debug_path,
591 l_debug_file,
592 G_intra_day_flag,
593 fnd_global.local_chr(0),
594 '','',
595 '','','','','','','','','','',
596 '','','','','','','','','','',
597 '','','','','','','','','','',
598 '','','','','','','','','','',
599 '','','','','','','','','','',
600 '','','','','','','','','','',
601 '','','','','','','','','','',
602 '','','','','','','','','','');
603 IF l_request_id = 0 THEN
604 IF l_DEBUG in ('Y', 'C') THEN
605 cep_standard.debug(FND_MESSAGE.get);
606 cep_standard.debug('EXCEPTION: Fail to submit cuncurrent request for '|| l_program_name);
607 END IF;
608 RAISE ldr_exception;
609 END IF;
610 ELSE
611 FND_MESSAGE.set_name('CE', 'CE_ERROR_EXIST');
612 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'W');
613 END IF;
614
615 -- END IF; --org_id
616 END IF;
617 END IF;
618 IF l_DEBUG in ('Y', 'C') THEN
619 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Insert_Hdr');
620 END IF;
621 EXCEPTION
622 WHEN ldr_exception THEN
623 RAISE;
624 WHEN OTHERS THEN
625 l_err := SQLCODE;
626 IF l_DEBUG in ('Y', 'C') THEN
627 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Hdr - '|| to_char(l_err));
628 END IF;
629 RAISE;
630 END Insert_Hdr;
631
632 /* ---------------------------------------------------------------------
633 | PRIVATE PROCEDURE |
634 | Get_Precision |
635 | |
636 | DESCRIPTION |
637 | |
638 | |
639 | CALLED BY |
640 | |
641 | REQUIRES |
642 | |
643 | HISTORY |
644 | 19-MAY-1999 Created BHCHUNG |
645 --------------------------------------------------------------------- */
646
647 FUNCTION Get_Precision(X_precision NUMBER) RETURN NUMBER IS
648 l_precision NUMBER;
649 BEGIN
650 IF nvl(X_precision, 0) = 0 THEN
651 l_precision := 1;
652 ELSIF X_precision = 1 THEN
653 l_precision := 10;
654 ELSIF X_precision = 2 THEN
655 l_precision := 100;
656 ELSIF X_precision = 3 THEN
657 l_precision := 1000;
658 ELSIF X_precision = 4 THEN
659 l_precision := 10000;
660 ELSIF X_precision = 5 THEN
661 l_precision := 100000;
662 END IF;
663
664 RETURN l_precision;
665 END Get_Precision;
666
667
668 /* ---------------------------------------------------------------------
669 | PRIVATE PROCEDURE |
670 | Find_Formatted_String |
671 | |
672 | DESCRIPTION |
673 | Find Bank Transaction number in TRX_TEXT |
677 | REQUIRES |
674 | |
675 | CALLED BY |
676 | Insert Line, Get_Formatted_Sting |
678 | |
679 | HISTORY |
680 | 19-MAY-1999 Created BHCHUNG |
681 --------------------------------------------------------------------- */
682 FUNCTION Find_Formatted_String(X_format VARCHAR2,
683 X_trx_text varchar2) RETURN VARCHAR2 IS
684 l_str VARCHAR2(150);
685 l_len NUMBER;
686 l_pos1 NUMBER;
687 l_pos2 NUMBER;
688 l_flag NUMBER;
689 l_fixed VARCHAR2(30);
690 l_format VARCHAR2(1);
691 l_fmt VARCHAR2(150);
692 l_string VARCHAR2(1);
693 l_tmp_str VARCHAR2(255);
694 l_tmp_str2 VARCHAR2(255);
695 BEGIN
696 l_fmt := replace(replace(X_format, '('),')');
697 l_len := LENGTH(l_fmt);
698 l_pos1 := instr(X_format, '(');
699
700 IF l_pos1 = 1 THEN -- the format has no indicator
701 l_flag := 1;
702 l_tmp_str := rtrim(ltrim(substr(X_trx_text, instr(X_trx_text, ' ', -1))));
703
704 IF LENGTH(l_tmp_str) = l_len THEN
705 WHILE ( l_len > 0 ) LOOP
706 IF ( ( Is_Number(substr(l_tmp_str, l_len, 1))
707 AND
708 substr(l_fmt, l_len, 1) IN ('a', 'A') )
709 OR
710 ( NOT Is_Number(substr(l_tmp_str, l_len, 1))
711 AND
712 substr(l_fmt, l_len, 1) IN ('n', 'N') ) ) THEN
713 l_flag := 0;
714 EXIT; -- Not in the format
715 END IF;
716 l_len := l_len-1;
717 END LOOP;
718
719 IF l_flag = 1 THEN
720 RETURN l_tmp_str;
721 ELSE
722 IF l_tmp_str = X_trx_text THEN -- Format was not found.
723 RETURN NULL;
724 END IF;
725 RETURN Find_Formatted_String( X_format, substr(X_trx_text, 1, instr(X_trx_text, ' ', -1)-1) );
726 END IF;
727 ELSE
728 IF l_tmp_str = X_trx_text THEN -- Format was not found.
729 RETURN NULL;
730 END IF;
731 RETURN Find_Formatted_String( X_format, substr(X_trx_text, 1, instr(X_trx_text, ' ', -1)-1) );
732 END IF;
733 ELSE -- the format has indicator
734 l_fixed := substr(X_format, 1, l_pos1-1);
735 l_pos2 := instr(X_trx_text, l_fixed);
736
737 IF l_pos2 = 0 THEN
738 RETURN NULL; -- couldn't find format in TRX_TEXT.
739 END IF;
740
741 IF instr(X_format, '~') <> 0 THEN
742 l_tmp_str := substr(X_trx_text, l_pos2 + LENGTH(l_fixed)) || ' .';
743 RETURN substr(l_tmp_str, 1 , instr(l_tmp_str, ' ')-1);
744 END IF;
745
746 l_str := substr(X_trx_text, l_pos2, l_len);
747
748 l_pos1 := l_pos1 + 1;
749 l_format := substr(X_format, l_pos1, 1);
750 l_string := substr(l_str, l_pos1 - 1, 1);
751 WHILE ( l_format <> ')' ) LOOP
752 IF ( (l_format in ('A','a') AND Is_Number(l_string))
753 OR
754 (l_format in ('N','n') AND NOT Is_Number(l_string)) ) THEN
755 RETURN Find_Formatted_String(X_format, REPLACE(X_trx_text, l_str));
756 END IF;
757 l_pos1 := l_pos1 + 1;
758 l_format := substr(X_format, l_pos1, 1);
759 l_string := substr(l_str, l_pos1 - 1, 1);
760 END LOOP;
761 END IF;
762
763 IF G_include_indicator = 'Y' THEN
764 RETURN l_str;
765 ELSE
766 RETURN substr(l_str, LENGTH(l_fixed)+1);
767 END IF;
768 END Find_Formatted_String;
769
770
771 /* ---------------------------------------------------------------------
772 | PRIVATE PROCEDURE |
773 | Get_Formatted_Sting |
774 | |
775 | DESCRIPTION |
776 | Decode Bank Trx Number format and call Find_Bank_Trx_Number |
777 | |
778 | CALLED BY |
779 | Lookup_val, LOAD_BAI2 |
780 | REQUIRES |
781 | |
782 | HISTORY |
783 | 19-MAY-1999 Created BHCHUNG |
784 --------------------------------------------------------------------- */
785
786 FUNCTION Get_Formatted_String(X_string VARCHAR2) RETURN VARCHAR2 IS
787 l_pos NUMBER;
788 l_format VARCHAR2(50);
789 l_tmp_format VARCHAR2(150);
790 l_return VARCHAR2(255);
791 l_return_tmp VARCHAR2(255);
792 l_concatenate_format_flag VARCHAR2(1);
793 BEGIN
794 IF l_DEBUG in ('Y', 'C') THEN
795 cep_standard.debug('>> Get_Formatted_String' );
796 cep_standard.debug('X_string = '|| X_string );
797 END IF;
798
799
800 l_tmp_format := LTRIM(RTRIM(G_predefined_format));
801 l_pos := INSTR(l_tmp_format, ',');
802 l_return := NULL;
803 l_concatenate_format_flag := nvl(G_concatenate_format_flag, 'N');
804 --l_concatenate_format_flag := 'Y';
805
806 IF l_DEBUG in ('Y', 'C') THEN
807 cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
808 cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
809 cep_standard.debug('l_pos = '||l_pos||'.');
810
811 END IF;
812
813
814 WHILE l_pos <> 0 LOOP
815 l_format := LTRIM(RTRIM(SUBSTR(l_tmp_format, 1, l_pos-1)));
816
817 -- DBMS_OUTPUT.put_line('l_format 1: '||l_format);
818 cep_standard.debug('l_format 1 = '||l_format||'.');
819
820 IF (l_concatenate_format_flag = 'Y') THEN
821 IF l_return IS NOT NULL THEN
825 cep_standard.debug('l_return not null = '||l_return||'.');
822 l_return := l_return || '/' || Find_Formatted_String(l_tmp_format, x_string);
823
824 IF l_DEBUG in ('Y', 'C') THEN
826 END IF;
827 ELSE
828 l_return := Find_Formatted_String(l_tmp_format, x_string);
829
830 IF l_DEBUG in ('Y', 'C') THEN
831 cep_standard.debug('l_return null = '||l_return||'.');
832 END IF;
833 END IF;
834 ELSE
835 l_return := Find_Formatted_String(l_format, X_string);
836
837 cep_standard.debug('concat = N - l_return = '||l_return||'.');
838
839 IF l_return IS NOT NULL THEN
840 --l_return := Find_Formatted_String(l_format, X_string);
841 --RETURN l_return;
842 IF l_DEBUG in ('Y', 'C') THEN
843 cep_standard.debug('concat = N - l_return not null = '||l_return||'.');
844 END IF;
845
846 RETURN rtrim(l_return, '/');
847
848 END IF;
849 END IF;
850
851 IF l_return IS NOT NULL THEN
852 cep_standard.debug('l_return = '||l_return||'.');
853 END IF;
854
855 l_tmp_format := SUBSTR(l_tmp_format, l_pos+1);
856 l_pos := INSTR(l_tmp_format, ',');
857 END LOOP;
858
859 IF l_DEBUG in ('Y', 'C') THEN
860 cep_standard.debug('l_tmp_format 2 = '||l_tmp_format||'.');
861 END IF;
862
863 IF (l_concatenate_format_flag = 'Y') THEN
864 IF l_return IS NOT NULL THEN
865 l_return := l_return || '/' || Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), x_string);
866 ELSE
867 l_return := Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), x_string);
868 END IF;
869 ELSE
870 --RETURN rtrim(Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), X_string), '/');
871 l_return := rtrim(Find_Formatted_String(LTRIM(RTRIM(l_tmp_format)), X_string), '/');
872 END IF;
873
874 IF l_DEBUG in ('Y', 'C') THEN
875 l_return_tmp := rtrim(l_return, '/');
876 cep_standard.debug('l_return_tmp = '||l_return_tmp||'.');
877 END IF;
878
879 RETURN rtrim(l_return, '/');
880
881 END Get_Formatted_String;
882
883
884 /* ---------------------------------------------------------------------
885 | PRIVATE PROCEDURE |
886 | Insert_Line |
887 | |
888 | DESCRIPTION |
889 | Insert Line Record to Interface Table. |
890 | |
891 | CALLED BY |
892 | Load_BAI2, Load_Others |
893 | REQUIRES |
894 | |
895 | HISTORY |
896 | 19-MAY-1999 Created BHCHUNG |
897 --------------------------------------------------------------------- */
898 PROCEDURE Insert_Line IS
899 l_rec_cnt NUMBER;
900 l_err NUMBER;
901 l_row_id ROWID;
902 l_statement_number VARCHAR2(50);
903 BEGIN
904 IF l_DEBUG in ('Y', 'C') THEN
905 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Insert_Line');
906
907 cep_standard.debug('Line_number -'||to_char(G_line_number)||'.');
908 cep_standard.debug('Statement Number -'||G_statement_number||'.');
909 cep_standard.debug('bank Account Num -'||G_bank_account_num||'.');
910 cep_standard.debug('Trx_date -'||G_trx_date||'.');
911 cep_standard.debug('Trx_code -'||G_trx_code||'.');
912 cep_standard.debug('Amount -'||to_char(G_amount)||'.');
913 cep_standard.debug('Trx Text -'||G_trx_text||'.');
914 cep_standard.debug('Customer Text -'||G_customer_text||'.');
915 cep_standard.debug('Invoice Text -'||G_invoice_text||'.');
916 cep_standard.debug('Bank Account Text -'||G_bank_account_text||'.');
917 cep_standard.debug('Effective Date -'||G_effective_date||'.');
918 cep_standard.debug('Currency Code -'||G_line_currency_code||'.');
919 cep_standard.debug('Exchange Rate -'||G_exchange_rate||'.');
920 cep_standard.debug('Bank Trx Number -'||G_bank_trx_number||'.');
921 cep_standard.debug('Created By -'||G_user_id||'.');
922 cep_standard.debug('Creation Date -'||to_char(SYSDATE)||'.');
923 cep_standard.debug('User Ex Rate -'||G_user_exchange_rate_type||'.');
924 cep_standard.debug('Exchange rate Date-'||G_exchange_rate_date||'.');
925 cep_standard.debug('Original_Amount -'||G_original_amount||'.');
926 cep_standard.debug('Charges_Amount -'||G_charges_amount||'.');
927
928 cep_standard.debug('Insert Line'||to_char(G_line_number));
929 END IF;
930
931 IF G_format_type = 'SWIFT940' THEN
932 l_statement_number := G_statement_number || ' - ' || to_char(sysdate);
933 ELSE
934 l_statement_number := G_statement_number;
935 END IF;
936
937 IF Valid_Statement THEN
938 --
939 -- Overwrite the existing bank statement of the same bank account and statement number.
940 --
941 SELECT count(*)
942 INTO l_rec_cnt
943 FROM ce_statement_lines_interface
944 WHERE bank_account_num = G_bank_account_num
945 AND statement_number = l_statement_number
946 AND line_number = G_line_number;
947
948 IF l_rec_cnt > 0 THEN
949 DELETE FROM ce_statement_lines_interface
950 WHERE bank_account_num = G_bank_account_num
951 AND statement_number = l_statement_number
952 AND line_number = G_line_number;
953 G_total_line_deleted := G_total_line_deleted + l_rec_cnt;
954 END IF;
955
956 G_invoice_text := rtrim(rtrim(G_invoice_text),'/');
957 G_trx_text := rtrim(rtrim(rtrim(G_trx_text),'/'));
961
958 G_bank_account_text := rtrim(rtrim(G_bank_account_text),'/');
959 G_customer_text := rtrim(rtrim(G_customer_text),'/');
960 G_bank_trx_number := rtrim(rtrim(G_bank_trx_number),'/');
962 CE_STAT_LINES_INF_PKG.Insert_Row(l_row_id,
963 rtrim(G_bank_account_num),
964 rtrim(l_statement_number),
965 to_number(rtrim(G_line_number)),
966 rtrim(G_trx_date),
967 rtrim(G_trx_code),
968 rtrim(G_effective_date),
969 G_trx_text,
970 G_invoice_text,
971 G_bank_account_text,
972 to_number(G_amount),
973 to_number(rtrim(G_charges_amount)),
974 rtrim(G_line_currency_code),
975 to_number(G_exchange_rate),
976 rtrim(G_user_exchange_rate_type),
977 rtrim(G_exchange_rate_date),
978 to_number(rtrim(G_original_amount)),
979 G_bank_trx_number,
980 G_customer_text,
981 to_number(G_user_id),
982 SYSDATE,
983 to_number(G_user_id),
984 SYSDATE,
985 NULL,
986 NULL, NULL, NULL, NULL, NULL,
987 NULL, NULL, NULL, NULL, NULL,
988 NULL, NULL, NULL, NULL, NULL);
989 END IF;
990 IF l_DEBUG in ('Y', 'C') THEN
991 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Insert_Line');
992 END IF;
993 EXCEPTION
994 WHEN OTHERS THEN
995 l_err := SQLCODE;
996 IF l_DEBUG in ('Y', 'C') THEN
997 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Insert_Line - '|| to_char(l_err));
998 END IF;
999 RAISE;
1000 END Insert_Line;
1001
1002 /* ---------------------------------------------------------------------
1003 | PRIVATE PROCEDURE |
1004 | Get_Value |
1005 | |
1006 | DESCRIPTION |
1007 | Returns the value of the position. Created to prevent using |
1008 | dynamic SQL. |
1009 | |
1010 | CALLED BY |
1011 | Lookup_Val |
1012 | REQUIRES |
1013 | |
1014 | HISTORY |
1015 | 15-JUL-2002 Created BYLEUNG |
1016 --------------------------------------------------------------------- */
1017 FUNCTION Get_Value(X_pos IN NUMBER,
1018 X_rec_no IN NUMBER) RETURN VARCHAR2 IS
1019 l_result VARCHAR2(255);
1020 BEGIN
1021 IF (X_pos = 1) THEN
1022 SELECT substr(rtrim(ltrim(column1)),1,255)
1023 INTO l_result
1024 FROM ce_stmt_int_tmp
1025 WHERE rec_no = X_rec_no;
1026 ELSIF (X_pos = 2) THEN
1027 SELECT rtrim(ltrim(column2))
1028 INTO l_result
1029 FROM ce_stmt_int_tmp
1030 WHERE rec_no = X_rec_no;
1031 ELSIF (X_pos = 3) THEN
1032 SELECT rtrim(ltrim(column3))
1033 INTO l_result
1034 FROM ce_stmt_int_tmp
1035 WHERE rec_no = X_rec_no;
1036 ELSIF (X_pos = 4) THEN
1037 SELECT rtrim(ltrim(column4))
1038 INTO l_result
1039 FROM ce_stmt_int_tmp
1040 WHERE rec_no = X_rec_no;
1041 ELSIF (X_pos = 5) THEN
1042 SELECT rtrim(ltrim(column5))
1043 INTO l_result
1044 FROM ce_stmt_int_tmp
1045 WHERE rec_no = X_rec_no;
1046 ELSIF (X_pos = 6) THEN
1047 SELECT rtrim(ltrim(column6))
1048 INTO l_result
1049 FROM ce_stmt_int_tmp
1050 WHERE rec_no = X_rec_no;
1051 ELSIF (X_pos = 7) THEN
1052 SELECT rtrim(ltrim(column7))
1053 INTO l_result
1054 FROM ce_stmt_int_tmp
1055 WHERE rec_no = X_rec_no;
1056 ELSIF (X_pos = 8) THEN
1057 SELECT rtrim(ltrim(column8))
1058 INTO l_result
1059 FROM ce_stmt_int_tmp
1060 WHERE rec_no = X_rec_no;
1061 ELSIF (X_pos = 9) THEN
1062 SELECT rtrim(ltrim(column9))
1063 INTO l_result
1064 FROM ce_stmt_int_tmp
1065 WHERE rec_no = X_rec_no;
1066 ELSIF (X_pos = 10) THEN
1067 SELECT rtrim(ltrim(column10))
1068 INTO l_result
1069 FROM ce_stmt_int_tmp
1070 WHERE rec_no = X_rec_no;
1071 ELSIF (X_pos = 11) THEN
1072 SELECT rtrim(ltrim(column11))
1073 INTO l_result
1074 FROM ce_stmt_int_tmp
1075 WHERE rec_no = X_rec_no;
1076 ELSIF (X_pos = 12) THEN
1077 SELECT rtrim(ltrim(column12))
1078 INTO l_result
1079 FROM ce_stmt_int_tmp
1080 WHERE rec_no = X_rec_no;
1081 ELSIF (X_pos = 13) THEN
1082 SELECT rtrim(ltrim(column13))
1083 INTO l_result
1084 FROM ce_stmt_int_tmp
1085 WHERE rec_no = X_rec_no;
1086 ELSIF (X_pos = 14) THEN
1087 SELECT rtrim(ltrim(column14))
1088 INTO l_result
1089 FROM ce_stmt_int_tmp
1090 WHERE rec_no = X_rec_no;
1091 ELSIF (X_pos = 15) THEN
1092 SELECT rtrim(ltrim(column15))
1093 INTO l_result
1094 FROM ce_stmt_int_tmp
1095 WHERE rec_no = X_rec_no;
1096 ELSIF (X_pos = 16) THEN
1097 SELECT rtrim(ltrim(column16))
1098 INTO l_result
1099 FROM ce_stmt_int_tmp
1100 WHERE rec_no = X_rec_no;
1101 ELSIF (X_pos = 17) THEN
1102 SELECT rtrim(ltrim(column17))
1103 INTO l_result
1104 FROM ce_stmt_int_tmp
1105 WHERE rec_no = X_rec_no;
1106 ELSIF (X_pos = 18) THEN
1110 WHERE rec_no = X_rec_no;
1107 SELECT rtrim(ltrim(column18))
1108 INTO l_result
1109 FROM ce_stmt_int_tmp
1111 ELSIF (X_pos = 19) THEN
1112 SELECT rtrim(ltrim(column19))
1113 INTO l_result
1114 FROM ce_stmt_int_tmp
1115 WHERE rec_no = X_rec_no;
1116 ELSIF (X_pos = 20) THEN
1117 SELECT rtrim(ltrim(column20))
1118 INTO l_result
1119 FROM ce_stmt_int_tmp
1120 WHERE rec_no = X_rec_no;
1121 ELSIF (X_pos = 21) THEN
1122 SELECT rtrim(ltrim(column21))
1123 INTO l_result
1124 FROM ce_stmt_int_tmp
1125 WHERE rec_no = X_rec_no;
1126 ELSIF (X_pos = 22) THEN
1127 SELECT rtrim(ltrim(column22))
1128 INTO l_result
1129 FROM ce_stmt_int_tmp
1130 WHERE rec_no = X_rec_no;
1131 ELSIF (X_pos = 23) THEN
1132 SELECT rtrim(ltrim(column23))
1133 INTO l_result
1134 FROM ce_stmt_int_tmp
1135 WHERE rec_no = X_rec_no;
1136 ELSIF (X_pos = 24) THEN
1137 SELECT rtrim(ltrim(column24))
1138 INTO l_result
1139 FROM ce_stmt_int_tmp
1140 WHERE rec_no = X_rec_no;
1141 ELSIF (X_pos = 25) THEN
1142 SELECT rtrim(ltrim(column25))
1143 INTO l_result
1144 FROM ce_stmt_int_tmp
1145 WHERE rec_no = X_rec_no;
1146 ELSIF (X_pos = 26) THEN
1147 SELECT rtrim(ltrim(column26))
1148 INTO l_result
1149 FROM ce_stmt_int_tmp
1150 WHERE rec_no = X_rec_no;
1151 ELSIF (X_pos = 27) THEN
1152 SELECT rtrim(ltrim(column27))
1153 INTO l_result
1154 FROM ce_stmt_int_tmp
1155 WHERE rec_no = X_rec_no;
1156 ELSIF (X_pos = 28) THEN
1157 SELECT rtrim(ltrim(column28))
1158 INTO l_result
1159 FROM ce_stmt_int_tmp
1160 WHERE rec_no = X_rec_no;
1161 ELSIF (X_pos = 29) THEN
1162 SELECT rtrim(ltrim(column29))
1163 INTO l_result
1164 FROM ce_stmt_int_tmp
1165 WHERE rec_no = X_rec_no;
1166 ELSIF (X_pos = 30) THEN
1167 SELECT rtrim(ltrim(column30))
1168 INTO l_result
1169 FROM ce_stmt_int_tmp
1170 WHERE rec_no = X_rec_no;
1171 ELSIF (X_pos = 31) THEN
1172 SELECT rtrim(ltrim(column31))
1173 INTO l_result
1174 FROM ce_stmt_int_tmp
1175 WHERE rec_no = X_rec_no;
1176 ELSIF (X_pos = 32) THEN
1177 SELECT rtrim(ltrim(column32))
1178 INTO l_result
1179 FROM ce_stmt_int_tmp
1180 WHERE rec_no = X_rec_no;
1181 ELSIF (X_pos = 33) THEN
1182 SELECT rtrim(ltrim(column33))
1183 INTO l_result
1184 FROM ce_stmt_int_tmp
1185 WHERE rec_no = X_rec_no;
1186 ELSIF (X_pos = 34) THEN
1187 SELECT rtrim(ltrim(column34))
1188 INTO l_result
1189 FROM ce_stmt_int_tmp
1190 WHERE rec_no = X_rec_no;
1191 ELSIF (X_pos = 35) THEN
1192 SELECT rtrim(ltrim(column35))
1193 INTO l_result
1194 FROM ce_stmt_int_tmp
1195 WHERE rec_no = X_rec_no;
1196 END IF;
1197
1198 --
1199 -- If format is entered then search for the target string.
1200 --
1201 IF G_predefined_format IS NOT NULL THEN
1202 l_result := Get_Formatted_String(nvl(l_result,'x'));
1203 END IF;
1204
1205 return(l_result);
1206 EXCEPTION
1207 WHEN OTHERS THEN
1208 IF l_DEBUG in ('Y', 'C') THEN
1209 cep_standard.debug('EXCEPTION: Get_Value - X_pos '|| to_char(X_pos)||', X_rec_no '|| to_char(X_rec_no));
1210 END IF;
1211 RAISE;
1212 END Get_Value;
1213
1214 /* ---------------------------------------------------------------------
1215 | PRIVATE FUNCTION |
1216 | Lookup_Pos |
1217 | |
1218 | DESCRIPTION |
1219 | Returns the position of the column. |
1220 | |
1221 | CALLED BY |
1222 | Lookup_val |
1223 | REQUIRES |
1224 | |
1225 | HISTORY |
1226 | 19-MAY-1999 Created BHCHUNG |
1227 --------------------------------------------------------------------- */
1228 PROCEDURE Lookup_Pos(X_hdr_line IN VARCHAR2,
1229 X_column IN VARCHAR2,
1230 X_position OUT NOCOPY NUMBER) IS
1231 CURSOR C_h IS
1232 SELECT nvl(position,0),
1233 format,
1234 include_format_ind,
1235 concatenate_format_flag
1236 FROM ce_bank_stmt_map_hdr_v
1237 WHERE map_id = G_map_id
1238 AND column_name = X_column;
1239
1240 CURSOR C_l IS
1241 SELECT nvl(position,0),
1242 format,
1243 include_format_ind,
1244 concatenate_format_flag
1245 FROM ce_bank_stmt_map_line_v
1246 WHERE map_id = G_map_id
1247 AND column_name = X_column;
1248
1249 l_pos NUMBER;
1250 l_err NUMBER;
1251 BEGIN
1252 IF X_hdr_line = 'H' THEN
1253 OPEN C_h;
1254 FETCH C_h INTO X_position, G_predefined_format, G_include_indicator, G_concatenate_format_flag;
1255 CLOSE C_h;
1256 ELSE
1257 OPEN C_l;
1258 FETCH C_l INTO X_position, G_predefined_format, G_include_indicator, G_concatenate_format_flag;
1259 CLOSE C_l;
1260 END IF;
1261 EXCEPTION
1262 WHEN NO_DATA_FOUND THEN
1263 X_position := 0;
1264 WHEN OTHERS THEN
1265 l_err := SQLCODE;
1266 IF l_DEBUG in ('Y', 'C') THEN
1267 cep_standard.debug('EXCEPTION: Lookup_Pos - '|| to_char(l_err));
1268 END IF;
1269 RAISE;
1273 l_str VARCHAR2(50);
1270 END Lookup_Pos;
1271
1272 FUNCTION Find_Last_Column_Pos(X_rec_no NUMBER) RETURN NUMBER IS
1274 BEGIN
1275 SELECT substr(column1,1,1)||substr(column2,1,1)||substr(column3,1,1)||substr(column4,1,1)||substr(column5,1,1)||
1276 substr(column6,1,1)||substr(column7,1,1)||substr(column8,1,1)||substr(column9,1,1)||substr(column10,1,1)||
1277 substr(column11,1,1)||substr(column12,1,1)||substr(column13,1,1)||substr(column14,1,1)||substr(column15,1,1)||
1278 substr(column16,1,1)||substr(column17,1,1)||substr(column18,1,1)||substr(column19,1,1)||substr(column20,1,1)||
1279 substr(column21,1,1)||substr(column22,1,1)||substr(column23,1,1)||substr(column24,1,1)||substr(column25,1,1)||
1280 substr(column26,1,1)||substr(column27,1,1)||substr(column28,1,1)||substr(column29,1,1)||substr(column30,1,1)||
1281 substr(column31,1,1)||substr(column32,1,1)||substr(column33,1,1)||substr(column34,1,1)||substr(column35,1,1)
1282 INTO l_str
1283 FROM ce_stmt_int_tmp
1284 WHERE rec_no = X_rec_no;
1285
1286 RETURN LENGTH(l_str);
1287 END Find_Last_Column_Pos;
1288
1289
1290 /* ---------------------------------------------------------------------
1291 | PRIVATE FUNCTION |
1292 | Lookup_Val |
1293 | |
1294 | DESCRIPTION |
1295 | Locates the taget data using mapping info and returns it. |
1296 | |
1297 | CALLED BY |
1298 | Load_BAI2 |
1299 | REQUIRES |
1300 | |
1301 | HISTORY |
1302 | 19-MAY-1999 Created BHCHUNG |
1303 --------------------------------------------------------------------- */
1304 FUNCTION Lookup_Val(X_hdr_line VARCHAR2,
1305 X_rec_no NUMBER,
1306 X_column VARCHAR2,
1307 X_rec_len NUMBER DEFAULT 0) RETURN VARCHAR2 IS
1308 l_pos NUMBER;
1309 l_value VARCHAR2(255);
1310 BEGIN
1311 Lookup_Pos(X_hdr_line, X_column, l_pos);
1312 IF l_pos = 0 THEN
1313 l_value := NULL;
1314 ELSE
1315 IF l_pos < 0 THEN
1316 IF X_rec_len = 0 THEN
1317 l_pos := Find_Last_Column_Pos(X_rec_no) + 1 + l_pos;
1318 ELSE
1319 l_pos := X_rec_len + l_pos;
1320 END IF;
1321 END IF;
1322 l_value := Get_Value(l_pos, X_rec_no);
1323 END IF;
1324
1325 RETURN l_value;
1326 END Lookup_Val;
1327
1328 /* ---------------------------------------------------------------------
1329 | PRIVATE FUNCTION |
1330 | Hdr_Or_Line |
1331 | |
1332 | DESCRIPTION |
1333 | Find out NOCOPY if the given record is header or line info. |
1334 | |
1335 | CALLED BY |
1336 | |
1337 | REQUIRES |
1338 | |
1339 | HISTORY |
1340 | 19-MAY-1999 Created BHCHUNG |
1341 --------------------------------------------------------------------- */
1342 FUNCTION Hdr_Or_Line(X_rec_id VARCHAR2) RETURN VARCHAR2 IS
1343 l_hdr NUMBER;
1344 l_line NUMBER;
1345 BEGIN
1346 SELECT count(*)
1347 INTO l_hdr
1348 FROM CE_BANK_STMT_MAP_HDR
1349 WHERE map_id = G_map_id
1350 AND rec_id_no = X_rec_id;
1351
1352 SELECT count(*)
1353 INTO l_line
1354 FROM CE_BANK_STMT_MAP_LINE
1355 WHERE map_id = G_map_id
1356 AND rec_id_no = X_rec_id;
1357
1358 IF (l_hdr > 0 AND l_line > 0) THEN
1359 RETURN 'B';
1360 ELSIF l_hdr > 0 THEN
1361 RETURN 'H';
1362 ELSIF l_line > 0 THEN
1363 RETURN 'L';
1364 ELSE
1365 RETURN 'N';
1366 END IF;
1367 END Hdr_Or_Line;
1368
1369
1370 /* ---------------------------------------------------------------------
1371 | PRIVATE FUNCTION |
1372 | Valid_Bank_Account |
1373 | |
1374 | DESCRIPTION |
1375 | validate the bank account. |
1376 | |
1377 | CALLED BY |
1378 | Load_BAI2 |
1379 | REQUIRES |
1380 | |
1381 | HISTORY |
1382 | 19-MAY-1999 Created BHCHUNG |
1383 --------------------------------------------------------------------- */
1384 FUNCTION Valid_Bank_Account RETURN BOOLEAN IS
1385 CURSOR C_bank IS
1386 SELECT BB.bank_name,
1387 BB.bank_branch_name,
1388 BB.branch_party_id,
1389 BA.bank_account_id,
1390 BA.currency_code
1391 --BA.org_id
1392 FROM CE_BANK_ACCOUNTS BA, --FROM AP_BANK_ACCOUNTS_ALL BA,
1393 CE_BANK_BRANCHES_V BB --AP_BANK_BRANCHES BB
1394 WHERE BB.branch_party_id = BA.bank_branch_id
1395 AND BA.bank_account_num = G_bank_account_num
1396 AND NVL(BA.account_classification,'DUMMY') = 'INTERNAL'; --bug 6511845
1397 --AND NVL(BA.account_type,'DUMMY') = 'INTERNAL'; --bug 3368534 added the AND condition.
1398
1399 l_cnt NUMBER;
1400 BEGIN
1401 IF G_bank_account_num IS NULL THEN
1402 FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
1403 FND_MESSAGE.set_token('FIELD', 'BANK_ACCOUNT_NUM');
1404 CE_SQLLDR_ERRORS_PKG.insert_row(NVL(G_statement_number, 'XXXXXXXXXXX'), NVL(G_bank_account_num, 'XXXXXXXXXXX'),
1405 G_rec_no, fnd_message.get, 'E');
1406 RETURN FALSE;
1407 END IF;
1408
1409 SELECT count(*)
1410 INTO l_cnt
1411 FROM CE_BANK_ACCOUNTS --FROM AP_BANK_ACCOUNTS_ALL
1412 WHERE BANK_ACCOUNT_NUM = G_bank_account_num
1416 IF l_cnt = 0 THEN -- Bank account is not setup.
1413 and NVL(ACCOUNT_CLASSIFICATION,'DUMMY') = 'INTERNAL'; -- Bug 6511845
1414 --and NVL(ACCOUNT_TYPE,'DUMMY') = 'INTERNAL'; -- Bug 3368534 added this line.
1415
1417 FND_MESSAGE.set_name('CE', 'CE_BANK_ACCNT_NOT_DEFINED');
1418 FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
1419 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'E');
1420 RETURN FALSE;
1421 ELSIF l_cnt > 1 THEN -- There are more than one bank with this account number.
1422 FND_MESSAGE.set_name('CE', 'CE_TOO_MANY_BANK_ACCNT');
1423 FND_MESSAGE.set_token('BANK_ACCNT',G_bank_account_num);
1424 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'W');
1425 ELSE
1426 IF ( G_bank_name IS NULL OR G_bank_branch_name IS NULL OR G_hdr_currency_code IS NULL) THEN
1427 OPEN C_bank;
1428 FETCH C_bank INTO G_bank_name, G_bank_branch_name, G_sub_branch_id, G_sub_account_id, G_hdr_currency_code; --, G_org_id;
1429 CLOSE C_bank;
1430 END IF;
1431
1432 END IF;
1433
1434 RETURN TRUE;
1435 END Valid_Bank_Account;
1436
1437 /* ---------------------------------------------------------------------
1438 | PRIVATE FUNCTION |
1439 | Decode_Line_BAI |
1440 | |
1441 | DESCRIPTION |
1442 | Decode record 16 and populate TEXT information. |
1443 | |
1444 | CALLED BY |
1445 | |
1446 | REQUIRES |
1447 | |
1448 | HISTORY |
1449 | 19-MAY-1999 Created BHCHUNG |
1450 --------------------------------------------------------------------- */
1451 PROCEDURE Decode_Line_BAI(X_rec_no NUMBER) IS
1452 CURSOR C_rec IS
1453 SELECT column3, column4
1454 FROM ce_stmt_int_tmp
1455 WHERE rec_no = X_rec_no;
1456
1457 l_val1 VARCHAR2(100);
1458 l_val2 VARCHAR2(100);
1459 l_val3 VARCHAR2(100);
1460 l_rec_len NUMBER;
1461 l_err NUMBER;
1462
1463 l_str VARCHAR2(2000);
1464 BEGIN
1465 IF l_DEBUG in ('Y', 'C') THEN
1466 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_BAI');
1467 END IF;
1468
1469 OPEN C_rec;
1470 FETCH C_rec
1471 INTO l_val1, l_val2;
1472 CLOSE C_rec;
1473
1474 IF l_val1 = 'S' THEN
1475 l_rec_len := 10;
1476 ELSIF l_val1 = 'V' THEN
1477 l_rec_len := 9;
1478 G_effective_date := CONV_TO_DATE(l_val2);
1479 ELSIF l_val1 = 'D' THEN
1480 l_rec_len := 8 + to_number(l_val2) * 2;
1481 ELSE
1482 l_rec_len := 7;
1483 END IF;
1484
1485 G_trx_text := substr(Lookup_Val('L', X_rec_no, 'TRX_TEXT', l_rec_len), 1, 255);
1486 G_invoice_text := substr(Lookup_Val('L', X_rec_no, 'INVOICE_TEXT', l_rec_len), 1, 30);
1487 G_customer_text := substr(Lookup_Val('L', X_rec_no, 'CUSTOMER_TEXT', l_rec_len), 1, 80);
1488 G_bank_account_text := substr(Lookup_Val('L', X_rec_no, 'BANK_ACCOUNT_TEXT', l_rec_len), 1, 30);
1489 G_bank_trx_number := substr(Lookup_Val('L', X_rec_no, 'BANK_TRX_NUMBER', l_rec_len), 1, 240);
1490
1491 G_trx_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'TRX_DATE', l_rec_len));
1492 -- G_effective_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'EFFECTIVE_DATE', l_rec_len));
1493 G_line_currency_code := substr(Lookup_Val('L', X_rec_no, 'CURRENCY_CODE', l_rec_len), 1, 15);
1494 G_exchange_rate := to_number(Lookup_Val('L', X_rec_no, 'EXCHANGE_RATE', l_rec_len));
1495 G_user_exchange_rate_type := substr(Lookup_Val('L', X_rec_no, 'USER_EXCHANGE_RATE_TYPE', l_rec_len), 1, 30);
1496 G_exchange_rate_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, 'EXCHANGE_RATE_DATE', l_rec_len));
1497 G_original_amount := to_number(Lookup_Val('L', X_rec_no, 'ORIGINAL_AMOUNT', l_rec_len)) / G_line_precision;
1498 G_charges_amount := to_number(Lookup_Val('L', X_rec_no, 'CHARGES_AMOUNT', l_rec_len)) / G_line_precision;
1499
1500 IF l_DEBUG in ('Y', 'C') THEN
1501 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Line_BAI');
1502 END IF;
1503 EXCEPTION
1504 WHEN OTHERS THEN
1505 l_err := SQLCODE;
1506 FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
1507 FND_MESSAGE.set_token('ERR', to_char(l_err));
1508 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
1509 IF l_DEBUG in ('Y', 'C') THEN
1510 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_BAI - '|| to_char(l_err));
1511 END IF;
1512 END Decode_Line_BAI;
1513
1514 /* ---------------------------------------------------------------------
1515 | PRIVATE PROCEDURE |
1516 | Find_Columns |
1517 | |
1518 | DESCRIPTION |
1519 | Find consecutive 5 columns. Created to prevent using dynamic |
1520 | SQL. |
1521 | |
1522 | CALLED BY |
1523 | Decode_Hdr_BAI |
1524 | |
1525 | REQUIRES |
1526 | |
1527 | HISTORY |
1528 | 15-JUL-2002 Created BYLEUNG |
1529 --------------------------------------------------------------------- */
1530 PROCEDURE Find_Columns( X_cnt IN NUMBER,
1531 X_rec_no IN NUMBER,
1532 X_col1 OUT NOCOPY VARCHAR2,
1533 X_col2 OUT NOCOPY VARCHAR2,
1534 X_col3 OUT NOCOPY VARCHAR2,
1535 X_col4 OUT NOCOPY VARCHAR2,
1536 X_col5 OUT NOCOPY VARCHAR2) IS
1537 BEGIN
1538 IF (X_cnt = 1) THEN
1539 SELECT column1, column2, column3, column4, column5
1543 ELSIF (X_cnt = 2) THEN
1540 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1541 FROM ce_stmt_int_tmp
1542 WHERE rec_no = X_rec_no;
1544 SELECT column2, column3, column4, column5, column6
1545 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1546 FROM ce_stmt_int_tmp
1547 WHERE rec_no = X_rec_no;
1548 ELSIF (X_cnt = 3) THEN
1549 SELECT column3, column4, column5, column6, column7
1550 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1551 FROM ce_stmt_int_tmp
1552 WHERE rec_no = X_rec_no;
1553 ELSIF (X_cnt = 4) THEN
1554 SELECT column4, column5, column6, column7, column8
1555 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1556 FROM ce_stmt_int_tmp
1557 WHERE rec_no = X_rec_no;
1558 ELSIF (X_cnt = 5) THEN
1559 SELECT column5, column6, column7, column8, column9
1560 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1561 FROM ce_stmt_int_tmp
1562 WHERE rec_no = X_rec_no;
1563 ELSIF (X_cnt = 6) THEN
1564 SELECT column6, column7, column8, column9, column10
1565 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1566 FROM ce_stmt_int_tmp
1567 WHERE rec_no = X_rec_no;
1568 ELSIF (X_cnt = 7) THEN
1569 SELECT column7, column8, column9, column10, column11
1570 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1571 FROM ce_stmt_int_tmp
1572 WHERE rec_no = X_rec_no;
1573 ELSIF (X_cnt = 8) THEN
1574 SELECT column8, column9, column10, column11, column12
1575 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1576 FROM ce_stmt_int_tmp
1577 WHERE rec_no = X_rec_no;
1578 ELSIF (X_cnt = 9) THEN
1579 SELECT column9, column10, column11, column12, column13
1580 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1581 FROM ce_stmt_int_tmp
1582 WHERE rec_no = X_rec_no;
1583 ELSIF (X_cnt = 10) THEN
1584 SELECT column10, column11, column12, column13, column14
1585 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1586 FROM ce_stmt_int_tmp
1587 WHERE rec_no = X_rec_no;
1588 ELSIF (X_cnt = 11) THEN
1589 SELECT column11, column12, column13, column14, column15
1590 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1591 FROM ce_stmt_int_tmp
1592 WHERE rec_no = X_rec_no;
1593 ELSIF (X_cnt = 12) THEN
1594 SELECT column12, column13, column14, column15, column16
1595 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1596 FROM ce_stmt_int_tmp
1597 WHERE rec_no = X_rec_no;
1598 ELSIF (X_cnt = 13) THEN
1599 SELECT column13, column14, column15, column16, column17
1600 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1601 FROM ce_stmt_int_tmp
1602 WHERE rec_no = X_rec_no;
1603 ELSIF (X_cnt = 14) THEN
1604 SELECT column14, column15, column16, column17, column18
1605 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1606 FROM ce_stmt_int_tmp
1607 WHERE rec_no = X_rec_no;
1608 ELSIF (X_cnt = 15) THEN
1609 SELECT column15, column16, column17, column18, column19
1610 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1611 FROM ce_stmt_int_tmp
1612 WHERE rec_no = X_rec_no;
1613 ELSIF (X_cnt = 16) THEN
1614 SELECT column16, column17, column18, column19, column20
1615 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1616 FROM ce_stmt_int_tmp
1617 WHERE rec_no = X_rec_no;
1618 ELSIF (X_cnt = 17) THEN
1619 SELECT column17, column18, column19, column20, column21
1620 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1621 FROM ce_stmt_int_tmp
1622 WHERE rec_no = X_rec_no;
1623 ELSIF (X_cnt = 18) THEN
1624 SELECT column18, column19, column20, column21, column22
1625 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1626 FROM ce_stmt_int_tmp
1627 WHERE rec_no = X_rec_no;
1628 ELSIF (X_cnt = 19) THEN
1629 SELECT column19, column20, column21, column22, column23
1630 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1631 FROM ce_stmt_int_tmp
1632 WHERE rec_no = X_rec_no;
1633 ELSIF (X_cnt = 20) THEN
1634 SELECT column20, column21, column22, column23, column24
1635 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1636 FROM ce_stmt_int_tmp
1637 WHERE rec_no = X_rec_no;
1638 ELSIF (X_cnt = 21) THEN
1639 SELECT column21, column22, column23, column24, column25
1640 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1641 FROM ce_stmt_int_tmp
1642 WHERE rec_no = X_rec_no;
1643 ELSIF (X_cnt = 22) THEN
1644 SELECT column22, column23, column24, column25, column26
1645 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1646 FROM ce_stmt_int_tmp
1647 WHERE rec_no = X_rec_no;
1648 ELSIF (X_cnt = 23) THEN
1649 SELECT column23, column24, column25, column26, column27
1650 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1651 FROM ce_stmt_int_tmp
1652 WHERE rec_no = X_rec_no;
1653 ELSIF (X_cnt = 24) THEN
1654 SELECT column24, column25, column26, column27, column28
1655 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1656 FROM ce_stmt_int_tmp
1657 WHERE rec_no = X_rec_no;
1658 ELSIF (X_cnt = 25) THEN
1659 SELECT column25, column26, column27, column28, column29
1660 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1661 FROM ce_stmt_int_tmp
1662 WHERE rec_no = X_rec_no;
1663 ELSIF (X_cnt = 26) THEN
1664 SELECT column26, column27, column28, column29, column30
1665 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1666 FROM ce_stmt_int_tmp
1670 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1667 WHERE rec_no = X_rec_no;
1668 ELSIF (X_cnt = 27) THEN
1669 SELECT column27, column28, column29, column30, column31
1671 FROM ce_stmt_int_tmp
1672 WHERE rec_no = X_rec_no;
1673 ELSIF (X_cnt = 28) THEN
1674 SELECT column28, column29, column30, column31, column32
1675 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1676 FROM ce_stmt_int_tmp
1677 WHERE rec_no = X_rec_no;
1678 ELSIF (X_cnt = 29) THEN
1679 SELECT column29, column30, column31, column32, column33
1680 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1681 FROM ce_stmt_int_tmp
1682 WHERE rec_no = X_rec_no;
1683 ELSIF (X_cnt = 30) THEN
1684 SELECT column30, column31, column32, column33, column34
1685 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1686 FROM ce_stmt_int_tmp
1687 WHERE rec_no = X_rec_no;
1688 ELSIF (X_cnt = 31) THEN
1689 SELECT column31, column32, column33, column34, column35
1690 INTO X_col1, X_col2, X_col3, X_col4, X_col5
1691 FROM ce_stmt_int_tmp
1692 WHERE rec_no = X_rec_no;
1693 ELSIF (X_cnt = 32) THEN
1694 SELECT column32, column33, column34, column35
1695 INTO X_col1, X_col2, X_col3, X_col4
1696 FROM ce_stmt_int_tmp
1697 WHERE rec_no = X_rec_no;
1698 END IF;
1699
1700 EXCEPTION
1701 WHEN OTHERS THEN
1702 IF l_DEBUG in ('Y', 'C') THEN
1703 cep_standard.debug('EXCEPTION: Find_Columns - X_cnt '|| to_char(X_cnt)||', X_rec_no '|| to_char(X_rec_no));
1704 END IF;
1705 RAISE;
1706 END Find_Columns;
1707
1708 /* ---------------------------------------------------------------------
1709 | PRIVATE FUNCTION |
1710 | Decode_Hdr_BAI |
1711 | |
1712 | DESCRIPTION |
1713 | Decode record 03 and 88 and populate CONTROL information. |
1714 | |
1715 | CALLED BY |
1716 | Load_BAI2 |
1717 | REQUIRES |
1718 | |
1719 | HISTORY |
1720 | 19-MAY-1999 Created BHCHUNG |
1721 --------------------------------------------------------------------- */
1722 PROCEDURE Decode_Hdr_BAI(X_rec_id VARCHAR2,
1723 X_rec_no NUMBER) IS
1724 l_cnt NUMBER;
1725 l_cursor_id INTEGER;
1726 l_exec_id INTEGER;
1727 l_query VARCHAR2(1000);
1728 l_val1 VARCHAR2(100);
1729 l_val2 VARCHAR2(100);
1730 l_val3 VARCHAR2(100);
1731 l_val4 VARCHAR2(100);
1732 l_val5 VARCHAR2(100);
1733 l_temp_val1 VARCHAR2(100);
1734 l_temp_val2 VARCHAR2(100);
1735 l_temp_val3 VARCHAR2(100);
1736 l_temp_val4 VARCHAR2(100);
1737 l_temp_val5 VARCHAR2(100);
1738 l_err NUMBER;
1739 BEGIN
1740 IF l_DEBUG in ('Y', 'C') THEN
1741 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI');
1742 END IF;
1743
1744 IF X_rec_id in ('3','03') THEN
1745 l_cnt := 3;
1746 ELSIF X_rec_id = '88' THEN
1747 /*bug5124547*/
1748 if G_lcnt <> 0 then
1749 l_cnt := 1+G_lcnt;
1750 G_lcnt :=0;
1751 else
1752 l_cnt := 1;
1753 end if;
1754 /*bug5124547*/
1755 END IF;
1756 WHILE (l_cnt <= 32) LOOP
1757 Find_Columns(l_cnt, X_rec_no, l_val1, l_val2, l_val3, l_val4, l_val5);
1758
1759 /* bug 3771128. Remove the '/' character.Check if the last
1760 read value is a trx_code i.e., g_last_val1 is not null then
1761 swap the values so that the values are correctly placed in
1762 the corresponding variables. This will simulate the 88 record
1763 as being started with a trx_code. - Start*/
1764 /*bug5100563*/
1765 IF n =1 then
1766 l_val5 := l_val4;
1767 l_val4 := l_val3;
1768 l_val3 := l_val2;
1769 l_val2 := l_val1;
1770 l_val1 := G_last_val1;
1771 l_cnt := l_cnt -1;
1772 n:=0;
1773 ELSIF n =2 then
1774 l_val5 := l_val3;
1775 l_val4 := l_val2;
1776 l_val3 := l_val1;
1777 l_val2 := G_last_val2;
1778 l_val1 := G_last_val1;
1779 l_cnt := l_cnt -2;
1780 n:=0;
1781 ELSIF n =3 then
1782 l_val5 := l_val2;
1783 l_val4 := l_val1;
1784 l_val3 := G_last_val3;
1785 l_val2 := G_last_val2;
1786 l_val1 := G_last_val1;
1787 l_cnt := l_cnt -3;
1788 n:=0;
1789 END IF;
1790 if (instr(l_val1,'/') >0) then
1791 l_val1 := rtrim(rtrim(l_val1),'/');
1792 G_last_val1:= l_val1;
1793 n:=1;
1794 exit;
1795 elsif (instr(l_val2,'/') >0) then
1796 l_val1 := rtrim(rtrim(l_val1),'/');
1797 l_val2 := rtrim(rtrim(l_val2),'/');
1798 G_last_val1:= l_val1;
1799 G_last_val2:= l_val2;
1800 n:=2;
1801 exit;
1802 elsif (instr(l_val3,'/') >0) then
1803 l_val1 := rtrim(rtrim(l_val1),'/');
1804 l_val2 := rtrim(rtrim(l_val2),'/');
1805 l_val3 := rtrim(rtrim(l_val3),'/');
1806 G_last_val1:= l_val1;
1807 G_last_val2:= l_val2;
1808 G_last_val3:= l_val3;
1809 n:=3;
1810 exit;
1811 end if;
1812 /*bug5100563*/
1813 IF l_val1 = '010' THEN
1814 G_control_begin_balance := to_number(l_val2)/G_hdr_precision;
1815 ELSIF l_val1 = '015' THEN
1816 G_control_end_balance := to_number(l_val2)/G_hdr_precision;
1817 ELSIF l_val1 = '045' THEN
1818 G_cashflow_balance := to_number(l_val2)/G_hdr_precision;
1819 G_int_calc_balance := to_number(l_val2)/G_hdr_precision;
1820 ELSIF l_val1 = '020' THEN
1824 ELSIF l_val1 = '050' THEN
1821 G_average_close_ledger_mtd := to_number(l_val2)/G_hdr_precision;
1822 ELSIF l_val1 = '025' THEN
1823 G_average_close_ledger_ytd := to_number(l_val2)/G_hdr_precision;
1825 G_average_close_available_mtd := to_number(l_val2)/G_hdr_precision;
1826 ELSIF l_val1 = '055' THEN
1827 G_average_close_available_ytd := to_number(l_val2)/G_hdr_precision;
1828 ELSIF l_val1 = '072' THEN
1829 G_one_day_float := to_number(l_val2)/G_hdr_precision;
1830 ELSIF l_val1 = '074' THEN
1831 G_two_day_float := to_number(l_val2)/G_hdr_precision;
1832 ELSIF l_val1 = '400' THEN
1833 G_control_total_dr := to_number(l_val2)/G_hdr_precision;
1834 /*Bug 3164477 added the following 2 lines for dr line count*/
1835 G_control_dr_line_count := to_number(l_val3);
1836 select decode(G_control_dr_line_count,null,
1837 G_control_line_count,
1838 nvl(G_control_line_count,0)+G_control_dr_line_count)
1839 into G_control_line_count from dual;
1840 ELSIF l_val1 = '100' THEN
1841 G_control_total_cr := to_number(l_val2)/G_hdr_precision;
1842 /*Bug 3164477 added the following 2 lines for cr line count*/
1843 G_control_cr_line_count := to_number(l_val3);
1844 select decode(G_control_cr_line_count,null,
1845 G_control_line_count,
1846 nvl(G_control_line_count,0)+G_control_cr_line_count)
1847 into G_control_line_count from dual;
1848 END IF;
1849
1850
1851 IF l_val4 = 'S' THEN
1852 /*bug5124547*/
1853 Find_Columns(l_cnt+4,X_rec_no, l_temp_val1, l_temp_val2, l_temp_val3, l_temp_val4, l_temp_val5);
1854 if (instr(l_temp_val1,'/')>0) then
1855 G_lcnt :=2;
1856 elsif (instr(l_temp_val2,'/')>0) then
1857 G_lcnt :=1;
1858 elsif (instr(l_temp_val3,'/')>0) then
1859 G_lcnt :=0;
1860 end if;
1861 l_cnt := l_cnt + 7;
1862 /*bug5124547*/
1863 ELSIF l_val4 = 'V' THEN
1864 l_cnt := l_cnt + 6;
1865 ELSIF l_val4 = 'D' THEN
1866 l_cnt := l_cnt + 5 + to_number(l_val5) * 2;
1867 ELSE
1868 l_cnt := l_cnt + 4;
1869 END IF;
1870 END LOOP;
1871
1872 IF l_DEBUG in ('Y', 'C') THEN
1873 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI');
1874 END IF;
1875 EXCEPTION -- Bug 3608257 added this EXCEPTION section
1876 WHEN OTHERS THEN
1877 l_err := SQLCODE;
1878 FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
1879 FND_MESSAGE.set_token('ERR', to_char(l_err));
1880 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
1881 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_BAI - '|| to_char(l_err));
1882 END Decode_Hdr_BAI;
1883
1884 /* ---------------------------------------------------------------------
1885 | PRIVATE FUNCTION |
1886 | Load_BAI2 |
1887 | |
1888 | DESCRIPTION |
1889 | Loading engine for BAI2 format. |
1890 | |
1891 | CALLED BY |
1892 | Load_Bank_Statement |
1893 | REQUIRES |
1894 | |
1895 | HISTORY |
1896 | 19-MAY-1999 Created BHCHUNG |
1897 --------------------------------------------------------------------- */
1898 PROCEDURE Load_BAI2 IS
1899
1900 CURSOR C_tmp_tbl IS
1901 SELECT rec_no, rec_id_no, column1, rtrim(replace(column2,' '),'/') col2, column6, rtrim(replace(column8,' '),'/') col8
1902 FROM ce_stmt_int_tmp
1903 ORDER BY rec_no;
1904
1905 CURSOR C_trx_text IS
1906 SELECT column_name,
1907 format,
1908 include_format_ind,
1909 concatenate_format_flag
1910 FROM ce_bank_stmt_map_line
1911 WHERE map_id = G_map_id
1912 AND position = -1;
1913
1914 l_control_total_cr NUMBER := 0; -- Calculate total credit amount.
1915 l_control_total_dr NUMBER := 0; -- Calculate total debit amount.
1916 l_control_total_cnt NUMBER := 0; -- Count total number of records in 03,16,88,49.
1917 l_rec_cnt NUMBER; -- Count total number of records in 03,16,88,49.
1918 l_cnt NUMBER;
1919
1920 l_currency_code VARCHAR2(15);
1921 l_line_cnt NUMBER;
1922 l_last_rid VARCHAR2(2);
1923 l_err NUMBER;
1924 l_statement_date DATE;
1925 l_bank_trx_num VARCHAR2(240);
1926 l_process_this_record BOOLEAN := TRUE; /* 2643505 */
1927 l_rec ce_stmt_int_tmp%rowtype; -- Bug 3228203
1928
1929 BEGIN
1930 IF l_DEBUG in ('Y', 'C') THEN
1931 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_BAI');
1932 END IF;
1933
1934 FOR C_rec IN C_tmp_tbl LOOP
1935 G_rec_no := C_rec.rec_no;
1936
1937 IF l_DEBUG in ('Y', 'C') THEN
1938 cep_standard.debug('CE_BANK_STATEMENT_LOADER.Load_BAI - G_rec_no = '||G_rec_no );
1939 END IF;
1940
1941 IF ( G_rec_no = 1 AND
1942 C_rec.rec_id_no <> '01' ) THEN
1943 FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
1944 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
1945 EXIT;
1946 ELSIF C_rec.rec_id_no = '01' THEN
1947 Init_Hdr_Rec;
1948 Init_Line_Rec;
1949 l_line_cnt := 0;
1950 l_last_rid := '1';
1951 IF nvl(C_rec.col8,'X') <> '2' THEN
1952 FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
1956 ELSIF C_rec.rec_id_no = '02' THEN
1953 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
1954 EXIT;
1955 END IF;
1957
1958 IF l_DEBUG in ('Y', 'C') THEN
1959 cep_standard.debug('>>C_rec.rec_id_no = 02');
1960 END IF;
1961
1962 G_statement_number := lookup_val('H', C_rec.rec_no, 'STATEMENT_NUMBER');
1963
1964 IF l_DEBUG in ('Y', 'C') THEN
1965 cep_standard.debug('G_statement_number = '|| G_statement_number);
1966 END IF;
1967
1968 G_statement_date := CONV_TIMESTAMP(lookup_val('H', C_rec.rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', C_rec.rec_no, 'STATEMENT_TIMESTAMP'));
1969
1970 IF l_DEBUG in ('Y', 'C') THEN
1971 cep_standard.debug('G_statement_date = '|| G_statement_date);
1972 END IF;
1973
1974 IF G_statement_number IS NULL THEN
1975 FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
1976 FND_MESSAGE.set_token('FIELD', 'STATEMENT_NUMBER');
1977 CE_SQLLDR_ERRORS_PKG.insert_row('XXXXXXXXXXX' , NVL(G_bank_account_num, 'XXXXXXXXXXX'),
1978 C_rec.rec_no, fnd_message.get);
1979 EXIT;
1980 END IF;
1981
1982 IF G_statement_date IS NULL THEN
1983 FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISS_REQ_FIELD');
1984 FND_MESSAGE.set_token('FIELD', 'STATEMENT_DATE');
1985 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, NVL(G_bank_account_num, 'XXXXXXXXXXX'),
1986 C_rec.rec_no, fnd_message.get);
1987 EXIT;
1988 END IF;
1989
1990 l_statement_date := G_statement_date;
1991 l_currency_code := C_rec.column6;
1992 l_last_rid := '2';
1993
1994 -- Added for p2p
1995 IF (G_gl_date_source = 'STATEMENT') THEN
1996 G_gl_date := trunc(G_statement_date);
1997 END IF;
1998 IF l_DEBUG in ('Y', 'C') THEN
1999 cep_standard.debug('<<C_rec.rec_id_no = 02');
2000 END IF;
2001
2002 ELSIF C_rec.rec_id_no = '03' THEN
2003 l_rec_cnt := 1;
2004
2005 G_bank_account_num := lookup_val('H', C_rec.rec_no, 'BANK_ACCOUNT_NUM');
2006 G_hdr_precision := Get_Precision(to_number(Lookup_Val('H', C_rec.rec_no, 'PRECISION')));
2007 IF ( G_hdr_precision = 1 AND G_precision <> 1 )THEN
2008 G_hdr_precision := G_precision;
2009 ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
2010 G_precision := G_hdr_precision;
2011 END IF;
2012
2013 G_hdr_currency_code := nvl(lookup_val('H', C_rec.rec_no, 'CURRENCY_CODE'), l_currency_code);
2014
2015 /* 2643505 Added */
2016 IF Valid_Bank_Account THEN
2017 l_process_this_record := TRUE;
2018 ELSE
2019 l_process_this_record := FALSE;
2020 END IF;
2021 /* 2643505 End Code Added */
2022
2023 Decode_Hdr_BAI(C_rec.rec_id_no, C_rec.rec_no);
2024
2025 l_last_rid := '3';
2026 ELSIF C_rec.rec_id_no = '16' THEN
2027 Init_Line_Rec;
2028 l_line_cnt := l_line_cnt + 1;
2029 l_rec_cnt := l_rec_cnt + 1;
2030
2031 G_line_number := l_line_cnt;
2032
2033 G_line_precision := Get_Precision(to_number(Lookup_Val('L', C_rec.rec_no, 'PRECISION')));
2034 IF ( G_line_precision = 1 AND G_precision <> 1 )THEN
2035 G_line_precision := G_precision;
2036 END IF;
2037
2038 G_trx_code := lookup_val('L', C_rec.rec_no, 'TRX_CODE');
2039 G_amount := to_number(lookup_val('L', C_rec.rec_no, 'AMOUNT'))/G_line_precision;
2040
2041 IF l_DEBUG in ('Y', 'C') THEN
2042 cep_standard.debug('Decode_Line_BAI(C_rec.rec_no) = ' );
2043 END IF;
2044
2045 Decode_Line_BAI(C_rec.rec_no);
2046
2047 IF (to_number(G_trx_code) BETWEEN 100 AND 399) THEN
2048 l_control_total_cr := l_control_total_cr + G_amount;
2049 /* 2933873
2050 New specifications state that transaction codes upto 890
2051 can be used for Debit
2052 ELSIF (to_number(G_trx_code) BETWEEN 400 AND 699) THEN */
2053 ELSIF (to_number(G_trx_code) BETWEEN 400 AND 760) THEN
2054 l_control_total_dr := l_control_total_dr + G_amount;
2055 END IF;
2056
2057 IF G_trx_date IS NULL THEN
2058 G_trx_date := NVL(G_statement_date, l_statement_date);
2059 END IF;
2060
2061 l_last_rid := '16';
2062 /* 2643505 Added the IF Condition */
2063 IF (l_process_this_record) THEN
2064 Insert_Line;
2065 END IF;
2066
2067 ELSIF C_rec.rec_id_no = '88' THEN
2068 l_rec_cnt := l_rec_cnt + 1;
2069
2070 IF l_last_rid = '3' THEN
2071 Decode_Hdr_BAI(C_rec.rec_id_no, C_rec.rec_no);
2072 ELSIF l_last_rid = '16' THEN
2073 /* 2643505 Added the IF Condition */
2074 IF l_process_this_record THEN
2075 /* Bug3228203 added the following select stmt and modified
2076 the next update stmt.*/
2077 SELECT * INTO l_rec FROM ce_stmt_int_tmp
2078 WHERE rec_id_no = C_rec.rec_id_no AND rec_no = C_rec.rec_no;
2079
2080 /* Bug 6792668 Added rtrim */
2081
2082 UPDATE ce_statement_lines_interface
2083 SET trx_text = rtrim(substr(ltrim(rtrim(trx_text ||' '|| C_rec.column1)) ||
2084 decode(l_rec.column2,null,null,','||ltrim(rtrim(l_rec.column2))) ||
2088 decode(l_rec.column6,null,null,','||ltrim(rtrim(l_rec.column6))) ||
2085 decode(l_rec.column3,null,null,','||ltrim(rtrim(l_rec.column3))) ||
2086 decode(l_rec.column4,null,null,','||ltrim(rtrim(l_rec.column4))) ||
2087 decode(l_rec.column5,null,null,','||ltrim(rtrim(l_rec.column5))) ||
2089 decode(l_rec.column7,null,null,','||ltrim(rtrim(l_rec.column7))) ||
2090 decode(l_rec.column8,null,null,','||ltrim(rtrim(l_rec.column8))) ||
2091 decode(l_rec.column9,null,null,','||ltrim(rtrim(l_rec.column9))) ||
2092 decode(l_rec.column10,null,null,','||ltrim(rtrim(l_rec.column10))) ||
2093 decode(l_rec.column11,null,null,','||ltrim(rtrim(l_rec.column11))) ||
2094 decode(l_rec.column12,null,null,','||ltrim(rtrim(l_rec.column12))) ||
2095 decode(l_rec.column13,null,null,','||ltrim(rtrim(l_rec.column13))) ||
2096 decode(l_rec.column14,null,null,','||ltrim(rtrim(l_rec.column14))) ||
2097 decode(l_rec.column15,null,null,','||ltrim(rtrim(l_rec.column15))) ||
2098 decode(l_rec.column16,null,null,','||ltrim(rtrim(l_rec.column16))) ||
2099 decode(l_rec.column17,null,null,','||ltrim(rtrim(l_rec.column17))) ||
2100 decode(l_rec.column18,null,null,','||ltrim(rtrim(l_rec.column18))) ||
2101 decode(l_rec.column19,null,null,','||ltrim(rtrim(l_rec.column19))) ||
2102 decode(l_rec.column20,null,null,','||ltrim(rtrim(l_rec.column20))) ||
2103 decode(l_rec.column21,null,null,','||ltrim(rtrim(l_rec.column21))) ||
2104 decode(l_rec.column22,null,null,','||ltrim(rtrim(l_rec.column22))) ||
2105 decode(l_rec.column23,null,null,','||ltrim(rtrim(l_rec.column23))) ||
2106 decode(l_rec.column24,null,null,','||ltrim(rtrim(l_rec.column24))) ||
2107 decode(l_rec.column25,null,null,','||ltrim(rtrim(l_rec.column25))) ||
2108 decode(l_rec.column26,null,null,','||ltrim(rtrim(l_rec.column26))) ||
2109 decode(l_rec.column27,null,null,','||ltrim(rtrim(l_rec.column27))) ||
2110 decode(l_rec.column28,null,null,','||ltrim(rtrim(l_rec.column28))) ||
2111 decode(l_rec.column29,null,null,','||ltrim(rtrim(l_rec.column29))) ||
2112 decode(l_rec.column30,null,null,','||ltrim(rtrim(l_rec.column30))) ||
2113 decode(l_rec.column31,null,null,','||ltrim(rtrim(l_rec.column31))) ||
2114 decode(l_rec.column32,null,null,','||ltrim(rtrim(l_rec.column32))) ||
2115 decode(l_rec.column33,null,null,','||ltrim(rtrim(l_rec.column33))) ||
2116 decode(l_rec.column34,null,null,','||ltrim(rtrim(l_rec.column34))) ||
2117 decode(l_rec.column35,null,null,','||ltrim(rtrim(l_rec.column35)))
2118 ,1,255))
2119 WHERE bank_account_num = G_bank_account_num
2120 AND statement_number = G_statement_number
2121 AND line_number = l_line_cnt;
2122
2123 FOR C_rec1 IN C_trx_text LOOP
2124 G_predefined_format := C_rec1.format;
2125 G_include_indicator := C_rec1.include_format_ind;
2126 G_concatenate_format_flag := C_rec1.concatenate_format_flag;
2127
2128 IF l_DEBUG in ('Y', 'C') THEN
2129 cep_standard.debug('C_rec.rec_id_no = 88 and l_last_rid = 16 ' );
2130 cep_standard.debug('G_predefined_format = '||G_predefined_format||'.');
2131 cep_standard.debug('G_include_indicator = '||G_include_indicator||'.');
2132 cep_standard.debug('G_concatenate_format_flag = '||G_concatenate_format_flag||'.');
2133 END IF;
2134
2135 IF ( C_rec1.column_name = 'BANK_TRX_NUMBER' AND
2136 G_bank_trx_number IS NULL ) THEN
2137 G_bank_trx_number := Get_Formatted_String(C_rec.column1);
2138 ELSIF ( C_rec1.column_name = 'INVOICE_TEXT'AND
2139 G_invoice_text IS NULL ) THEN
2140 G_invoice_text := Get_Formatted_String(C_rec.column1);
2141 ELSIF ( C_rec1.column_name = 'CUSTOMER_TEXT'AND
2142 G_customer_text IS NULL ) THEN
2143 G_customer_text := Get_Formatted_String(C_rec.column1);
2144 ELSIF ( C_rec1.column_name = 'BANK_ACCOUNT_TEXT'AND
2145 G_bank_account_text IS NULL ) THEN
2146 G_bank_account_text := Get_Formatted_String(C_rec.column1);
2147 ELSIF ( C_rec1.column_name = 'CURRENCY_CODE'AND
2148 G_line_currency_code IS NULL ) THEN
2149 G_line_currency_code := Get_Formatted_String(C_rec.column1);
2150 ELSIF ( C_rec1.column_name = 'USER_EXCHANGE_RATE_TYPE'AND
2151 G_user_exchange_rate_type IS NULL ) THEN
2152 G_user_exchange_rate_type := Get_Formatted_String(C_rec.column1);
2153 ELSIF ( C_rec1.column_name = 'EXCHANGE_RATE_DATE'AND
2154 G_exchange_rate_date IS NULL ) THEN
2155 G_exchange_rate_date := Get_Formatted_String(C_rec.column1);
2156 ELSIF ( C_rec1.column_name = 'EXCHANGE_RATE'AND
2157 G_exchange_rate IS NULL ) THEN
2158 G_exchange_rate := Get_Formatted_String(C_rec.column1);
2159 ELSIF ( C_rec1.column_name = 'ORIGINAL_AMOUNT'AND
2160 G_original_amount IS NULL ) THEN
2161 G_original_amount := Get_Formatted_String(C_rec.column1);
2162 ELSIF ( C_rec1.column_name = 'CHARGES_AMOUNT'AND
2163 G_charges_amount IS NULL ) THEN
2164 G_charges_amount := Get_Formatted_String(C_rec.column1);
2165 END IF;
2166
2167 IF l_DEBUG in ('Y', 'C') THEN
2168 cep_standard.debug('C_rec1.column_name = '||C_rec1.column_name||'.');
2169 cep_standard.debug('G_bank_trx_number = '||G_bank_trx_number||'.');
2170 cep_standard.debug('G_invoice_text = '||G_invoice_text ||'.');
2171 cep_standard.debug('G_customer_text = '||G_customer_text ||'.');
2172 cep_standard.debug('G_bank_account_text = '||G_bank_account_text ||'.');
2173 cep_standard.debug('G_line_currency_code = '||G_line_currency_code ||'.');
2177 cep_standard.debug('G_original_amount = '||G_original_amount ||'.');
2174 cep_standard.debug('G_user_exchange_rate_type = '||G_user_exchange_rate_type ||'.');
2175 cep_standard.debug('G_exchange_rate_date = '||G_exchange_rate_date ||'.');
2176 cep_standard.debug('G_exchange_rate = '||G_exchange_rate ||'.');
2178 cep_standard.debug('G_charges_amount = '||G_charges_amount||'.');
2179 END IF;
2180
2181 UPDATE ce_statement_lines_interface
2182 SET bank_trx_number = G_bank_trx_number,
2183 invoice_text = G_invoice_text,
2184 customer_text = G_customer_text,
2185 bank_account_text = G_bank_account_text,
2186 currency_code = G_line_currency_code,
2187 user_exchange_rate_type = G_user_exchange_rate_type,
2188 exchange_rate_date = G_exchange_rate_date,
2189 exchange_rate = G_exchange_rate,
2190 original_amount = G_original_amount,
2191 charges_amount = G_charges_amount
2192 WHERE bank_account_num = G_bank_account_num
2193 AND statement_number = G_statement_number
2194 AND line_number = l_line_cnt;
2195 END LOOP;
2196 END IF; /* 2643505 Added */
2197 END IF;
2198 ELSIF C_rec.rec_id_no = '49' THEN
2199 l_rec_cnt := l_rec_cnt + 1;
2200 l_line_cnt := 0;
2201
2202 IF (nvl(to_number(C_rec.col2), l_rec_cnt) <> l_rec_cnt) THEN
2203 FND_MESSAGE.set_name('CE', 'CE_SQLLDR_MISSING_RECORD');
2204 FND_MESSAGE.set_token('GIVEN', C_rec.col2);
2205 FND_MESSAGE.set_token('COUNTED', to_char(l_rec_cnt));
2206 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get);
2207 END IF;
2208
2209 G_statement_date := nvl(G_statement_date, l_statement_date);
2210 /* 2643505 Added the IF Condition */
2211 IF l_process_this_record THEN
2212 Insert_Hdr;
2213 END IF;
2214 l_process_this_record := TRUE;
2215 /*2643505 End Code Added */
2216 Init_Hdr_Rec;
2217 l_control_total_cr := 0;
2218 l_control_total_dr := 0;
2219 ELSIF C_rec.rec_id_no NOT IN ('98', '99') THEN
2220 FND_MESSAGE.set_name('CE', 'CE_INVALID_BAI2');
2221 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, C_rec.rec_no, fnd_message.get, 'E');
2222 EXIT;
2223 END IF;
2224
2225 END LOOP;
2226 IF l_DEBUG in ('Y', 'C') THEN
2227 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_BAI');
2228 END IF;
2229 EXCEPTION
2230 WHEN OTHERS THEN
2231 l_err := SQLCODE;
2232 IF l_DEBUG in ('Y', 'C') THEN
2233 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_BAI - '|| to_char(l_err) ||
2234 ' Rec no - '|| nvl(G_rec_no, 0));
2235 END IF;
2236 RAISE;
2237 END Load_BAI2;
2238
2239 /* ---------------------------------------------------------------------
2240 | PRIVATE FUNCTION |
2241 | Decode_Hdr_Other |
2242 | |
2243 | DESCRIPTION |
2244 | Load header information for NON-BAI2 format. |
2245 | |
2246 | CALLED BY |
2247 | |
2248 | REQUIRES |
2249 | |
2250 | HISTORY |
2251 | 19-MAY-1999 Created BHCHUNG |
2252 --------------------------------------------------------------------- */
2253 PROCEDURE Decode_Hdr_Other(X_rec_no NUMBER,
2254 X_column_name VARCHAR2) IS
2255 l_err NUMBER;
2256 BEGIN
2257 IF l_DEBUG in ('Y', 'C') THEN
2258 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other');
2259 END IF;
2260
2261 IF X_column_name = 'STATEMENT_NUMBER' THEN
2262 G_statement_number := Lookup_Val('H', X_rec_no, X_column_name);
2263 ELSIF X_column_name = 'BANK_ACCOUNT_NUM' THEN
2264 G_bank_account_num := Lookup_Val('H', X_rec_no, X_column_name);
2265 ELSIF X_column_name = 'STATEMENT_DATE' THEN
2266 IF G_statement_date IS NULL THEN
2267 G_statement_date := CONV_TO_DATE(Lookup_Val('H', X_rec_no, X_column_name));
2268 END IF;
2269 ELSIF X_column_name = 'STATEMENT_TIMESTAMP' THEN
2270 G_statement_date := CONV_TIMESTAMP( Lookup_Val('H', X_rec_no, 'STATEMENT_DATE') || ' ' || lookup_val('H', X_rec_no, 'STATEMENT_TIMESTAMP'));
2271 ELSIF X_column_name = 'BANK_NAME' THEN
2272 IF G_bank_name IS NULL THEN
2273 G_bank_name := Lookup_Val('H', X_rec_no, X_column_name);
2274 END IF;
2275 ELSIF X_column_name = 'BANK_BRANCH_NAME' THEN
2276 IF G_bank_branch_name IS NULL THEN
2277 G_bank_branch_name := Lookup_Val('H', X_rec_no, X_column_name);
2278 END IF;
2279 ELSIF X_column_name = 'CONTROL_BEGIN_BALANCE' THEN
2280 IF l_DEBUG in ('Y', 'C') THEN
2281 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2282 END IF;
2283 G_control_begin_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2284 ELSIF X_column_name = 'CONTROL_END_BALANCE' THEN
2285 IF l_DEBUG in ('Y', 'C') THEN
2286 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2287 END IF;
2288 G_control_end_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2289 ELSIF X_column_name = 'AVAILABLE_BALANCE' THEN
2290 IF l_DEBUG in ('Y', 'C') THEN
2294 ELSIF X_column_name = 'VALUE_DATED_BALANCE' THEN
2291 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2292 END IF;
2293 G_cashflow_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2295 IF l_DEBUG in ('Y', 'C') THEN
2296 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2297 END IF;
2298 G_int_calc_balance := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2299 ELSIF X_column_name = 'ONE_DAY_FLOAT' THEN
2300 IF l_DEBUG in ('Y', 'C') THEN
2301 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2302 END IF;
2303 G_one_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2304 ELSIF X_column_name = 'TWO_DAY_FLOAT' THEN
2305 IF l_DEBUG in ('Y', 'C') THEN
2306 cep_standard.debug('Decode_Hdr_Other: ' || 'G_hdr_precision: ' || to_char(G_hdr_precision));
2307 END IF;
2308 G_two_day_float := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2309 ELSIF X_column_name = 'CONTROL_TOTAL_DR' THEN
2310 G_control_total_dr := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2311 ELSIF X_column_name = 'CONTROL_TOTAL_CR' THEN
2312 G_control_total_cr := fnd_number.canonical_to_number(Lookup_Val('H', X_rec_no, X_column_name)) / G_hdr_precision;
2313 ELSIF X_column_name = 'CONTROL_DR_LINE_COUNT' THEN
2314 G_control_dr_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
2315 ELSIF X_column_name = 'CONTROL_CR_LINE_COUNT' THEN
2316 G_control_cr_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
2317 ELSIF X_column_name = 'CONTROL_LINE_COUNT' THEN
2318 G_control_line_count := to_number(Lookup_Val('H', X_rec_no, X_column_name));
2319 ELSIF X_column_name = 'CURRENCY_CODE' THEN
2320 G_hdr_currency_code := Lookup_Val('H', X_rec_no, X_column_name);
2321 ELSIF X_column_name = 'CHECK_DIGITS' THEN
2322 G_check_digits := Lookup_Val('H', X_rec_no, X_column_name);
2323 END IF;
2324
2325 -- Added for p2p
2326 IF G_gl_date_source = 'STATEMENT' THEN
2327 G_gl_date := G_statement_date;
2328 END IF;
2329
2330 IF l_DEBUG in ('Y', 'C') THEN
2331 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other');
2332 END IF;
2333 EXCEPTION
2334 WHEN OTHERS THEN
2335 l_err := SQLCODE;
2336 FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2337 FND_MESSAGE.set_token('ERR', to_char(l_err));
2338 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
2339 IF l_DEBUG in ('Y', 'C') THEN
2340 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Hdr_Other - '|| to_char(l_err));
2341 END IF;
2342 END Decode_Hdr_Other;
2343
2344 /* ---------------------------------------------------------------------
2345 | PRIVATE FUNCTION |
2346 | Decode_Line_Other |
2347 | |
2348 | DESCRIPTION |
2349 | Load line information for NON-BAI2 format. |
2350 | |
2351 | CALLED BY |
2352 | |
2353 | REQUIRES |
2354 | |
2355 | HISTORY |
2356 | 19-MAY-1999 Created BHCHUNG |
2357 --------------------------------------------------------------------- */
2358 PROCEDURE Decode_Line_Other(X_rec_no NUMBER,
2359 X_column_name VARCHAR2) IS
2360 l_err NUMBER;
2361 REQ_FIELD_MISSING EXCEPTION;
2362 BEGIN
2363 IF l_DEBUG in ('Y', 'C') THEN
2364 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Decode_Line_Other');
2365 END IF;
2366
2367 IF X_column_name = 'LINE_NUMBER' THEN
2368 G_line_number := NVL(to_number(Lookup_Val('L', X_rec_no, X_column_name)), G_line_number);
2369 ELSIF X_column_name = 'TRX_DATE' THEN
2370 G_trx_date := NVL(CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name)), G_statement_date);
2371 ELSIF X_column_name = 'TRX_CODE' THEN
2372 G_trx_code := Lookup_Val('L', X_rec_no, X_column_name);
2373 ELSIF X_column_name = 'EFFECTIVE_DATE' THEN
2374 G_effective_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name));
2375 ELSIF X_column_name = 'TRX_TEXT' THEN
2376 G_trx_text := Lookup_Val('L', X_rec_no, X_column_name);
2377 ELSIF X_column_name = 'INVOICE_TEXT' THEN
2378 G_invoice_text := Lookup_Val('L', X_rec_no, X_column_name);
2379 ELSIF X_column_name = 'AMOUNT' THEN
2380 G_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name)) / G_line_precision;
2381 ELSIF X_column_name = 'CURRENCY_CODE' THEN
2382 G_line_currency_code := Lookup_Val('L', X_rec_no, X_column_name);
2383 ELSIF X_column_name = 'EXCHANGE_RATE' THEN
2384 G_exchange_rate := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name));
2385 ELSIF X_column_name = 'BANK_TRX_NUMBER' THEN
2386 G_bank_trx_number := Lookup_Val('L', X_rec_no, X_column_name);
2387 ELSIF X_column_name = 'CUSTOMER_TEXT' THEN
2388 G_customer_text := Lookup_Val('L', X_rec_no, X_column_name);
2389 ELSIF X_column_name = 'USER_EXCHANGE_RATE_TYPE' THEN
2390 G_user_exchange_rate_type := Lookup_Val('L', X_rec_no, X_column_name);
2391 ELSIF X_column_name = 'EXCHANGE_RATE_DATE' THEN
2392 G_exchange_rate_date := CONV_TO_DATE(Lookup_Val('L', X_rec_no, X_column_name));
2396 G_charges_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name)) / G_line_precision;
2393 ELSIF X_column_name = 'ORIGINAL_AMOUNT' THEN
2394 G_original_amount := fnd_number.canonical_to_number(Lookup_Val('L', X_rec_no, X_column_name)) / G_line_precision;
2395 ELSIF X_column_name = 'CHARGES_AMOUNT' THEN
2397 ELSIF X_column_name = 'BANK_ACCOUNT_TEXT' THEN
2398 G_bank_account_text := Lookup_Val('L', X_rec_no, X_column_name);
2399 END IF;
2400 IF l_DEBUG in ('Y', 'C') THEN
2401 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Decode_Line_Other');
2402 END IF;
2403 EXCEPTION
2404 WHEN OTHERS THEN
2405 l_err := SQLCODE;
2406 FND_MESSAGE.set_name('CE', 'CE_RECORD_FAIL');
2407 FND_MESSAGE.set_token('ERR', to_char(l_err));
2408 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, nvl(X_rec_no, 0), fnd_message.get);
2409 IF l_DEBUG in ('Y', 'C') THEN
2410 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Decode_Line_Other - '||to_char(l_err));
2411 END IF;
2412 END Decode_Line_Other;
2413
2414 FUNCTION Get_Main_Rec_ID (X_hdr_or_line VARCHAR2) RETURN VARCHAR2 IS
2415 CURSOR C_line_rec_id IS
2416 SELECT distinct(rec_id_no) recID
2417 FROM ce_bank_stmt_map_line
2418 WHERE map_id = G_map_id;
2419
2420 CURSOR C_hdr_rec_id IS
2421 SELECT distinct(rec_id_no) recID
2422 FROM ce_bank_stmt_map_hdr
2423 WHERE map_id = G_map_id;
2424
2425 l_lowest_rec_no NUMBER := 10000000;
2426 l_min_rec_no NUMBER;
2427 l_rec_id VARCHAR2(30);
2428 l_err NUMBER;
2429 BEGIN
2430 IF X_hdr_or_Line = 'L' THEN
2431 FOR C_rec IN C_line_rec_id LOOP
2432 SELECT MIN(rec_no)
2433 INTO l_min_rec_no
2434 FROM ce_stmt_int_tmp
2435 WHERE rec_id_no = C_rec.recID;
2436
2437 IF l_lowest_rec_no > l_min_rec_no THEN
2438 l_lowest_rec_no := l_min_rec_no;
2439 l_rec_id := C_rec.recID;
2440 END IF;
2441 END LOOP;
2442 ELSE
2443 FOR C_rec IN C_hdr_rec_id LOOP
2444 SELECT MIN(rec_no)
2445 INTO l_min_rec_no
2446 FROM ce_stmt_int_tmp
2447 WHERE rec_id_no = C_rec.recID;
2448
2449 IF l_lowest_rec_no > l_min_rec_no THEN
2450 l_lowest_rec_no := l_min_rec_no;
2451 l_rec_id := C_rec.recID;
2452 END IF;
2453 END LOOP;
2454 END IF;
2455
2456 RETURN (l_rec_id);
2457 EXCEPTION
2458 WHEN OTHERS THEN
2459 l_err := SQLCODE;
2460 IF X_hdr_or_Line = 'L' THEN
2461 IF l_DEBUG in ('Y', 'C') THEN
2462 cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
2463 the main line information - '|| to_char(l_err) ||
2464 ' Rec no - '|| nvl(G_rec_no, 0));
2465 END IF;
2466 ELSE
2467 IF l_DEBUG in ('Y', 'C') THEN
2468 cep_standard.debug('Get_Main_Rec_ID: ' || 'EXCEPTION: Fail finding the record id of major line which contains
2469 the main header information - ' || to_char(l_err) ||
2470 ' Rec no - '|| nvl(G_rec_no, 0));
2471 END IF;
2472 END IF;
2473 RAISE;
2474 END Get_Main_Rec_ID;
2475
2476 PROCEDURE Load_Others IS
2477 CURSOR C_tmp_tbl IS
2478 SELECT rec_no, rec_id_no
2479 FROM CE_STMT_INT_TMP
2480 ORDER BY rec_no;
2481
2482 CURSOR C_hdr_tbl(p_rec_id VARCHAR2) IS
2483 SELECT column_name, position
2484 FROM CE_BANK_STMT_MAP_HDR
2485 WHERE map_id = G_map_id
2486 AND rec_id_no = p_rec_id;
2487
2488 CURSOR C_line_tbl(p_rec_id VARCHAR2) IS
2489 SELECT column_name, position
2490 FROM CE_BANK_STMT_MAP_LINE
2491 WHERE map_id = G_map_id
2492 AND rec_id_no = p_rec_id;
2493
2494 l_hdr_flag NUMBER := 0;
2495 l_line_flag NUMBER := 0;
2496 l_line_num NUMBER;
2497
2498 l_line_rec_id VARCHAR2(30);
2499 l_hdr_rec_id VARCHAR2(30);
2500
2501 l_hdr_or_line VARCHAR2(1);
2502 l_old_rec_type VARCHAR2(1) := 'H';
2503 l_err NUMBER;
2504 l_process_this_record BOOLEAN := TRUE; /* 2831725 */
2505
2506 INVALID_ACCOUNT EXCEPTION;
2507 BEGIN
2508 IF l_DEBUG in ('Y', 'C') THEN
2509 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Others');
2510 END IF;
2511
2512 l_line_rec_id := Get_Main_Rec_ID('L');
2513 l_hdr_rec_id := Get_Main_Rec_ID('H');
2514
2515 FOR C_rec IN C_tmp_tbl LOOP
2516 G_rec_no := C_rec.rec_no;
2517
2518 l_hdr_or_line := Hdr_Or_Line(C_rec.rec_id_no);
2519
2520 IF l_hdr_or_line = 'H' THEN
2521 IF l_old_rec_type = 'L' THEN
2522 /* 2831725 Added the IF Condition below */
2523 IF (l_process_this_record) THEN
2524 Insert_Line;
2525 END IF;
2526 Init_Line_Rec;
2527 END IF;
2528 l_old_rec_type := 'H';
2529
2530 IF ( C_rec.rec_id_no = l_hdr_rec_id AND l_hdr_flag = 1 ) THEN
2531 /* 2831725 Added the IF Condition below */
2532 IF (l_process_this_record) THEN
2533 Insert_Hdr;
2534 END IF;
2535 Init_Hdr_Rec;
2536 l_line_flag := 0;
2537 END IF;
2538
2539 --
2540 -- Get precision first
2544 G_hdr_precision := G_precision;
2541 --
2542 G_hdr_precision := Get_Precision(to_number(Lookup_Val('H', G_rec_no, 'PRECISION')));
2543 IF ( G_hdr_precision = 1 AND G_precision <> 1 )THEN
2545 ELSIF ( G_hdr_precision <> 1 AND G_precision = 1 )THEN
2546 G_precision := G_hdr_precision;
2547 END IF;
2548 IF l_DEBUG in ('Y', 'C') THEN
2549 cep_standard.debug('G_precision: ' || to_char(G_precision));
2550 cep_standard.debug('G_hdr_precision: ' || to_char(G_hdr_precision));
2551 END IF;
2552
2553 FOR C_hdr IN C_hdr_tbl(C_rec.rec_id_no) LOOP
2554 Decode_Hdr_Other(C_rec.rec_no, C_hdr.column_name);
2555 /* 2831725 Code Added begins */
2556 IF ( C_hdr.column_name = 'BANK_ACCOUNT_NUM' ) THEN
2557 IF (NOT Valid_Bank_Account ) THEN
2558 l_process_this_record := FALSE;
2559 ELSE
2560 l_process_this_record := TRUE;
2561 END IF;
2562 /*RAISE INVALID_ACCOUNT;*/
2563 END IF;
2564 /* 2831725 Added Code Ends */
2565 IF C_rec.rec_id_no = l_hdr_rec_id THEN
2566 l_hdr_flag := 1;
2567 END IF;
2568 END LOOP;
2569 l_line_num := 1;
2570 ELSIF l_hdr_or_line = 'L' THEN
2571 l_old_rec_type := 'L';
2572 IF ( C_rec.rec_id_no = l_line_rec_id AND l_line_flag = 1 ) THEN
2573 /* 2831725 Added the IF Condition below */
2574 IF (l_process_this_record) THEN
2575 Insert_Line;
2576 END IF;
2577 Init_Line_Rec;
2578 END IF;
2579
2580 --
2581 -- Get precision first
2582 --
2583 G_line_precision := Get_Precision(to_number(Lookup_Val('L', G_rec_no, 'PRECISION')));
2584 IF ( G_line_precision = 1 AND G_precision <> 1 )THEN
2585 G_line_precision := G_precision;
2586 END IF;
2587
2588 FOR C_line IN C_line_tbl(C_rec.rec_id_no) LOOP
2589 Decode_Line_Other(C_rec.rec_no, C_line.column_name);
2590 END LOOP;
2591
2592 IF C_rec.rec_id_no = l_line_rec_id THEN
2593 IF G_line_number IS NULL THEN
2594 G_line_number := l_line_num;
2595 END IF;
2596 l_line_flag := 1;
2597 l_line_num := l_line_num + 1;
2598 END IF;
2599 ELSIF l_hdr_or_line = 'B' THEN
2600 IF l_DEBUG in ('Y', 'C') THEN
2601 cep_standard.debug('This rec_id '|| C_rec.rec_id_no ||' is assigned for both header and line');
2602 END IF;
2603 FND_MESSAGE.set_name('CE', 'CE_RECID_IN_HDR_LINE');
2604 FND_MESSAGE.set_token('RECID', C_rec.rec_id_no);
2605 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, G_rec_no, fnd_message.get, 'E');
2606 EXIT;
2607 END IF;
2608 END LOOP;
2609
2610 --
2611 -- Insert any remain line or header record
2612 --
2613 IF G_amount IS NOT NULL THEN
2614 /* 2831725 Added the IF Condition below */
2615 IF (l_process_this_record) THEN
2616 Insert_Line;
2617 END IF;
2618 END IF;
2619
2620 IF G_statement_date IS NOT NULL THEN
2621 /* 2831725 Added the IF Condition below */
2622 IF (l_process_this_record) THEN
2623 Insert_Hdr;
2624 END IF;
2625 END IF;
2626 IF l_DEBUG in ('Y', 'C') THEN
2627 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_Others');
2628 END IF;
2629 EXCEPTION
2630 WHEN INVALID_ACCOUNT THEN
2631 null;
2632 WHEN OTHERS THEN
2633 l_err := SQLCODE;
2634 IF l_DEBUG in ('Y', 'C') THEN
2635 cep_standard.debug('EXCEPTION: CE_BANK_STATEMENT_LOADER.Load_Others - '||to_char(l_err)||
2636 'Rec no - '|| nvl(G_rec_no, 0));
2637 END IF;
2638 RAISE;
2639 END Load_Others;
2640
2641 PROCEDURE Decode_Line_SWIFT IS
2642 CURSOR C_tmp_tbl IS
2643 SELECT rec_no,
2644 column35
2645 FROM CE_STMT_INT_TMP
2646 WHERE rec_id_no = '61'
2647 ORDER BY rec_no;
2648
2649 l_col2 VARCHAR2(255);
2650 l_col3 VARCHAR2(255);
2651 l_col4 VARCHAR2(255);
2652 l_col5 VARCHAR2(255);
2653 l_col6 VARCHAR2(255);
2654 l_col7 VARCHAR2(255);
2655 l_col8 VARCHAR2(255);
2656
2657 l_tmp VARCHAR2(255);
2658 l_str VARCHAR2(255);
2659 l_cd VARCHAR2(1);
2660
2661 l_pos NUMBER;
2662 l_pos1 NUMBER;
2663 l_pos2 NUMBER;
2664 l_pos3 NUMBER;
2665 BEGIN
2666 FOR C_rec IN C_tmp_tbl LOOP
2667 l_str := C_rec.column35;
2668
2669 -- Get Entry Date
2670 l_tmp := SUBSTR(l_str, 1, 4);
2671 IF Is_Number(l_tmp) THEN
2672 l_col2 := l_tmp;
2673 l_str := SUBSTR(l_str, 5);
2674 ELSE
2675 l_col2 := NULL;
2676 END IF;
2677
2678 -- Get Debit/Credit Mark
2679 l_tmp := SUBSTR(l_str, 1, 1);
2680 IF l_tmp = 'R' THEN
2681 l_col3 := SUBSTR(l_str, 1, 2);
2682 l_str := SUBSTR(l_str, 3);
2683 IF SUBSTR(l_col3, 2, 1) = 'C' THEN
2684 l_cd := 'D';
2685 ELSE
2686 l_cd := 'C';
2687 END IF;
2688 ELSE
2689 l_col3 := l_tmp;
2690 l_str := SUBSTR(l_str, 2);
2691 l_cd := l_tmp;
2692 END IF;
2693
2694 -- Get Fund Code
2695 l_tmp := SUBSTR(l_str, 1, 1);
2696 IF NOT Is_Number(l_tmp) THEN
2697 l_col4 := l_tmp;
2701 END IF;
2698 l_str := SUBSTR(l_str, 2);
2699 ELSE
2700 l_col4 := NULL;
2702
2703 -- Get Amount
2704 /*Bug 4127039*/
2705
2706 l_pos1 := INSTR(l_str,'N');
2707 l_pos2 := INSTR(l_str,'S');
2708 l_pos3 := INSTR(l_str,'F');
2709
2710 IF l_pos1 = 0 THEN l_pos1 := 9999999; END IF;
2711 IF l_pos2 = 0 THEN l_pos2 := 9999999; END IF;
2712 IF l_pos3 = 0 THEN l_pos3 := 9999999; END IF;
2713
2714 l_pos := LEAST(l_pos1,l_pos2,l_pos3);
2715
2716 l_col5 := replace(SUBSTR(l_str, 1, l_pos - 1), ',', '.');
2717
2718 -- IF l_col3 IN ('D', 'RC') THEN
2719 -- l_col5 := '-' || l_col5;
2720 -- END IF;
2721 l_str := SUBSTR(l_str, l_pos + 1);
2722
2723 -- Get Trx Codes
2724 l_col6 := SUBSTR(l_str, 1, 3) || l_cd;
2725 l_str := SUBSTR(l_str, 4);
2726
2727 -- Get Reference to Account Owner and Accounting Service Institution's Reference
2728 l_pos := INSTR(l_str, '//');
2729 IF l_pos = 0 THEN
2730 l_col7 := l_str;
2731 l_col8 := NULL;
2732 ELSE
2733 l_col7 := SUBSTR(l_str, 1, l_pos - 1);
2734 l_col8 := SUBSTR(l_str, l_pos + 2);
2735 END IF;
2736
2737 UPDATE CE_STMT_INT_TMP
2738 SET column2 = l_col2,
2739 column3 = l_col3,
2740 column4 = l_col4,
2741 column5 = l_col5,
2742 column6 = l_col6,
2743 column7 = l_col7,
2744 column8 = l_col8
2745 WHERE rec_no = C_rec.rec_no;
2746 END LOOP;
2747 END Decode_Line_SWIFT;
2748
2749 PROCEDURE Decode_Description IS
2750 CURSOR C_tmp_tbl IS
2751 SELECT rec_no,
2752 column1
2753 FROM CE_STMT_INT_TMP
2754 WHERE rec_id_no = '9'
2755 ORDER BY rec_no;
2756
2757 CURSOR C_tmp_tbl2 IS
2758 SELECT rec_no,
2759 column1
2760 FROM CE_STMT_INT_TMP
2761 WHERE rec_id_no = '86'
2762 ORDER BY rec_no;
2763
2764 l_rec_no NUMBER;
2765 l_rec_id VARCHAR2(30);
2766 BEGIN
2767 FOR C_rec IN C_tmp_tbl LOOP
2768 SELECT MAX(rec_no)
2769 INTO l_rec_no
2770 FROM CE_STMT_INT_TMP
2771 WHERE rec_no < C_rec.rec_no
2772 AND rec_id_no <> '9';
2773
2774 /* Bug 4041064 added the following IF */
2775 IF l_rec_no IS NOT NULL THEN
2776 SELECT rec_id_no
2777 INTO l_rec_id
2778 FROM CE_STMT_INT_TMP
2779 WHERE rec_no = l_rec_no;
2780
2781 IF l_rec_id = '61' THEN
2782 UPDATE CE_STMT_INT_TMP
2783 SET rec_id_no = '61A'
2784 WHERE rec_no = C_rec.rec_no;
2785 ELSIF l_rec_id = '86' THEN
2786 UPDATE CE_STMT_INT_TMP
2787 SET column1 = column1 || ' ' || C_rec.column1
2788 WHERE rec_no = l_rec_no;
2789
2790 DELETE FROM CE_STMT_INT_TMP
2791 WHERE rec_no = C_rec.rec_no;
2792 END IF;
2793 END IF; --4041064
2794 END LOOP;
2795
2796 FOR C_rec IN C_tmp_tbl2 LOOP
2797 SELECT rec_id_no
2798 INTO l_rec_id
2799 FROM CE_STMT_INT_TMP
2800 WHERE rec_no = C_rec.rec_no - 1;
2801
2802 IF l_rec_id = '61' THEN
2803 UPDATE CE_STMT_INT_TMP -- This 86 record comes after 61.
2804 SET rec_id_no = '61A' -- Marks it as 61A.
2805 WHERE rec_no = C_rec.rec_no;
2806 ELSIF l_rec_id = '61A' THEN
2807 UPDATE CE_STMT_INT_TMP -- Comes after supplementary details (61A).
2808 SET column1 = column1 || ' ' -- Concatinate 86 to 61A.
2809 || C_rec.column1
2810 WHERE rec_no = C_rec.rec_no - 1;
2811
2812 DELETE FROM CE_STMT_INT_TMP -- Delete 86.
2813 WHERE rec_no = C_rec.rec_no;
2814 END IF;
2815 END LOOP;
2816
2817 END Decode_Description;
2818
2819 PROCEDURE Load_SWIFT940 IS
2820 BEGIN
2821 Decode_Line_SWIFT;
2822 Decode_Description;
2823 Load_Others;
2824 END Load_SWIFT940;
2825
2826 PROCEDURE Load_Bank_Statement(errbuf OUT NOCOPY VARCHAR2,
2827 retcode OUT NOCOPY NUMBER,
2828 X_MAP_ID NUMBER,
2829 X_REQUEST_ID NUMBER,
2830 X_data_file VARCHAR2,
2831 X_process_option VARCHAR2,
2832 X_gl_date VARCHAR2,
2833 X_org_id VARCHAR2,
2834 X_receivables_trx_id NUMBER,
2835 X_payment_method_id NUMBER,
2836 X_nsf_handling VARCHAR2,
2837 X_display_debug VARCHAR2,
2838 X_debug_path VARCHAR2,
2839 X_debug_file VARCHAR2,
2840 X_bank_branch_id NUMBER,
2841 X_bank_account_id NUMBER,
2842 X_intra_day_flag VARCHAR2 DEFAULT 'N',
2843 X_gl_date_source VARCHAR2 DEFAULT NULL) IS
2844 l_return BOOLEAN;
2845
2846 l_phase VARCHAR2(30);
2847 l_status VARCHAR2(30);
2848 l_dev_phase VARCHAR2(30);
2849 l_dev_status VARCHAR2(30);
2850 l_message VARCHAR2(1000);
2851
2852 l_cnt NUMBER;
2853 l_err NUMBER;
2854 l_format_type VARCHAR2(30);
2855 l_precision NUMBER;
2856 l_request_id NUMBER := X_REQUEST_ID;
2857 SQL_LOADER_ERROR EXCEPTION;
2858 INVALID_DATA_FILE EXCEPTION;
2859 BEGIN
2860
2864 G_intra_day_flag := X_intra_day_flag;
2861 G_data_file_name := X_data_file;
2862 G_process_option := X_process_option;
2863 G_gl_date := to_date(X_gl_date,'YYYY/MM/DD HH24:MI:SS');
2865 -- modified for p2p
2866 G_gl_date_source := X_gl_date_source;
2867
2868 G_receivables_trx_id := X_receivables_trx_id;
2869 G_payment_method_id := X_payment_method_id;
2870 G_nsf_handling := X_nsf_handling;
2871 G_display_debug := X_display_debug;
2872 G_debug_path := X_debug_path;
2873 G_debug_file := X_debug_file;
2874 G_bank_branch_id := X_bank_branch_id;
2875 G_bank_account_id := X_bank_account_id;
2876 G_org_id := X_org_id;
2877
2878 G_map_id := X_MAP_ID;
2879 G_user_id := FND_GLOBAL.user_id;
2880
2881 IF (G_gl_date_source = 'USER') THEN
2882 G_gl_date := to_date(X_gl_date,'YYYY/MM/DD HH24:MI:SS');
2883 ELSIF (G_gl_date_source = 'SYSTEM') THEN
2884 G_gl_date := SYSDATE;
2885 END IF;
2886
2887 IF l_DEBUG in ('Y', 'C') THEN
2888 cep_standard.enable_debug(G_debug_path,
2889 G_debug_file || '-LBS');
2890 cep_standard.debug('>>CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
2891 END IF;
2892 --
2893 -- Wait until SQL*Loader program is completed.
2894 --
2895 IF (l_request_id IS NOT NULL) THEN
2896 LOOP
2897 l_return := FND_CONCURRENT.wait_for_request(
2898 l_request_id,
2899 10,
2900 300,
2901 l_phase,
2902 l_status,
2903 l_dev_phase,
2904 l_dev_status,
2905 l_message);
2906
2907 l_return := FND_CONCURRENT.get_request_status(
2908 l_request_id,
2909 NULL,
2910 NULL,
2911 l_phase,
2912 l_status,
2913 l_dev_phase,
2914 l_dev_status,
2915 l_message);
2916
2917 IF (NVL(l_dev_phase,'ERROR') = 'COMPLETE') THEN
2918 EXIT;
2919 END IF;
2920 END LOOP;
2921
2922 IF (NVL(l_dev_status, 'ERROR') NOT IN ('NORMAL', 'WARNING')) THEN
2923 RAISE SQL_LOADER_ERROR;
2924 END IF;
2925 END IF;
2926
2927 --
2928 -- Check if the CE_STAT_INT_TMP was populated by SQL*Loader.
2929 --
2930 SELECT count(*)
2931 INTO l_cnt
2932 FROM CE_STMT_INT_TMP;
2933 IF l_cnt = 0 THEN
2934 RAISE INVALID_DATA_FILE;
2935 END IF;
2936
2937 Remove_Return_Char;
2938
2939 SELECT format_type,
2940 precision,
2941 date_format,
2942 timestamp_format
2943 INTO G_format_type,
2944 l_precision,
2945 G_date_format,
2946 G_timestamp_format
2947 FROM ce_bank_stmt_int_map_v
2948 WHERE map_id = X_MAP_ID;
2949
2950 --
2951 -- assign devision factor depending on the precision.
2952 --
2953 G_precision := Get_Precision(nvl(l_precision, 0));
2954
2955 IF l_DEBUG in ('Y', 'C') THEN
2956 cep_standard.debug('Load_Bank_Statement: ' || 'Format Type:'||l_format_type);
2957 cep_standard.debug('>>G_format_type= '||G_format_type);
2958 END IF;
2959
2960
2961 IF G_format_type = 'BAI2' THEN
2962 IF l_DEBUG in ('Y', 'C') THEN
2963 cep_standard.debug('>>call Load_BAI2');
2964 END IF;
2965 Load_BAI2;
2966 ELSIF G_format_type = 'SWIFT940' THEN
2967 IF l_DEBUG in ('Y', 'C') THEN
2968 cep_standard.debug('>>call Load_SWIFT940');
2969 END IF;
2970 Load_SWIFT940;
2971 ELSE
2972 IF l_DEBUG in ('Y', 'C') THEN
2973 cep_standard.debug('>>call Load_Others');
2974 END IF;
2975 Load_Others;
2976 END IF;
2977 Delete_Orphaned_Lines;
2978
2979 IF ( nvl(G_total_hdr_deleted, 0) <> 0 ) THEN
2980 FND_MESSAGE.set_name('CE', 'CE_EXIST_HDR_DELETED');
2981 FND_MESSAGE.set_token('CNT', to_char(G_total_hdr_deleted));
2982 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get);
2983 END IF;
2984 IF ( nvl(G_total_line_deleted, 0) <> 0 ) THEN
2985 FND_MESSAGE.set_name('CE', 'CE_EXIST_LINE_DELETED');
2986 FND_MESSAGE.set_token('CNT', to_char(G_total_line_deleted));
2987 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get);
2988 END IF;
2989
2990 CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
2991
2992 IF l_DEBUG in ('Y', 'C') THEN
2993 cep_standard.debug('<<CE_BANK_STATEMENT_LOADER.Load_Bank_Statement');
2994 END IF;
2995 IF G_display_debug = 'Y' THEN
2996 cep_standard.disable_debug(G_display_debug);
2997 END IF;
2998 EXCEPTION
2999 WHEN SQL_LOADER_ERROR THEN
3000 IF l_DEBUG in ('Y', 'C') THEN
3001 cep_standard.debug('EXCEPTION: Load_Bank_Statement - Sql*Loader was not finished');
3002 END IF;
3003 FND_MESSAGE.set_name('CE', 'CE_SQLLOADER_FAIL');
3004 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'E');
3005 CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
3006 WHEN INVALID_DATA_FILE THEN
3007 FND_MESSAGE.set_name('CE', 'CE_INVALID_DATA_FILE');
3008 FND_MESSAGE.set_token('DATA_FILE',G_data_file_name);
3009 CE_SQLLDR_ERRORS_PKG.insert_row(G_statement_number, G_bank_account_num, 0, fnd_message.get, 'E');
3010 CE_BANK_STMT_SQL_LDR.Print_Report(G_map_id, G_data_file_name);
3011 WHEN OTHERS THEN
3012 l_err := SQLCODE;
3013 IF l_DEBUG in ('Y', 'C') THEN
3014 cep_standard.debug('EXCEPTION: Load_Bank_Statement - '||to_char(l_err));
3015 END IF;
3016 RAISE;
3017 END Load_Bank_Statement;
3018
3019 END CE_BANK_STATEMENT_LOADER;