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