[Home] [Help]
PACKAGE BODY: APPS.AP_BANK_CHARGE_PKG
Source
1 PACKAGE BODY AP_BANK_CHARGE_PKG AS
2 /* $Header: apsudbcb.pls 120.6 2007/12/24 07:20:13 ppodhiya ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AP_BANK_CHARGE_PKG';
5 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
6 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
7 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
8 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
9 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
10 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
11 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
12
13 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
14 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
15 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
16 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
17 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
18 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
19 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
20 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'AP_BANK_CHARGE_PKG';
21
22 PROCEDURE get_bank_number(
23 P_bank_name IN VARCHAR2,
24 P_bank_number IN OUT NOCOPY VARCHAR2) IS
25
26 CURSOR C_bank(X_bank_name VARCHAR2)IS
27 SELECT bank_number
28 FROM ce_bank_branches_v
29 WHERE bank_name = X_bank_name;
30
31 l_debug_info Varchar2(2000);
32 l_api_name CONSTANT VARCHAR2(100) := 'GET_BANK_NUMBER';
33
34 BEGIN
35
36 l_debug_info := 'Getting the Bank Number';
37 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
38 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
39 END IF;
40
41 OPEN C_bank(P_bank_name);
42 FETCH C_bank INTO P_bank_number;
43 CLOSE C_bank;
44
45 END get_bank_number;
46
47 PROCEDURE get_bank_branch_name(
48 P_bank_branch_id IN NUMBER,
49 P_bank_number IN OUT NOCOPY VARCHAR2,
50 P_branch_number IN OUT NOCOPY VARCHAR2,
51 P_branch_name IN OUT NOCOPY VARCHAR2) IS
52
53 l_debug_info Varchar2(2000);
54 l_api_name CONSTANT VARCHAR2(100) := 'GET_BANK_BRANCH_NAME';
55
56 BEGIN
57
58 l_debug_info := 'Getting Bank Branch Info';
59 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
60 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
61 END IF;
62
63 select bank_number, branch_number, bank_branch_name
64 into P_bank_number, P_branch_number, P_branch_name
65 from ce_bank_branches_v
66 where branch_party_id = p_bank_branch_id;
67
68 exception
69 when NO_DATA_FOUND then
70 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
71 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
72 FND_MESSAGE.set_TOKEN('DEBUG_INFO', 'Bank Branch Info can not be derived');
73 APP_EXCEPTION.RAISE_EXCEPTION;
74 when OTHERS then
75 IF (SQLCODE <> -20001) THEN
76 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
77 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
78 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
79 END IF;
80
81 APP_EXCEPTION.RAISE_EXCEPTION;
82
83 END get_bank_branch_name;
84
85 PROCEDURE CHECK_BANK_COMBINATION(
86 P_transferring_bank_branch_id IN NUMBER,
87 P_transferring_bank_name IN VARCHAR2,
88 P_transferring_bank IN VARCHAR2,
89 P_transferring_branch IN VARCHAR2,
90 P_receiving_bank_branch_id IN NUMBER,
91 P_receiving_bank_name IN VARCHAR2,
92 P_receiving_bank IN VARCHAR2,
93 P_receiving_branch IN VARCHAR2,
94 P_transfer_priority IN VARCHAR2,
95 P_currency_code IN VARCHAR2) IS
96 unique_check NUMBER;
97 RECORD_EXIST EXCEPTION;
98 l_debug_info Varchar2(2000);
99 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_BANK_COMBINATION';
100
101 begin
102
103 l_debug_info := 'Checking Bank Combination';
104 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
105 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
106 END IF;
107
108 unique_check := 0;
109 if (P_transferring_bank_branch_id is NULL) then
110 if (P_receiving_bank_branch_id is NULL) then
111 if (P_transferring_bank = 'ONE') then
112 if (P_receiving_bank = 'ONE') then
113 /*1A1A*/
114 l_debug_info := '*1A1A*';
115 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
116 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
117 END IF;
118 select count(*) into unique_check
119 from ap_bank_charges
120 where transferring_bank_name = P_transferring_bank_name
121 and transferring_branch = P_transferring_branch
122 and receiving_bank_name = P_receiving_bank_name
123 and receiving_branch = P_receiving_branch
124 and transfer_priority = P_transfer_priority
125 and currency_code = P_currency_code;
126 else /*1AAA*/
127 l_debug_info := '*1AAA*';
128 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
129 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
130 END IF;
131
132 select count(*) into unique_check
133 from ap_bank_charges
134 where transferring_bank_name = P_transferring_bank_name
135 and transferring_branch = P_transferring_branch
136 and receiving_bank = P_receiving_bank
137 and receiving_branch = P_receiving_branch
138 and transfer_priority = P_transfer_priority
139 and currency_code = P_currency_code;
140 end if;
141 else
142 if (P_receiving_bank = 'ONE') then
143 /*AA1A*/
144 l_debug_info := '*AA1A*';
145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
146 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
147 END IF;
148
149 select count(*) into unique_check
150 from ap_bank_charges
151 where transferring_bank = P_transferring_bank
152 and transferring_branch = P_transferring_branch
153 and receiving_bank_name = P_receiving_bank_name
154 and receiving_branch = P_receiving_branch
155 and transfer_priority = P_transfer_priority
156 and currency_code = P_currency_code;
157 else /*AAAA*/
158 l_debug_info := '*AAAA*';
159 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
160 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
161 END IF;
162
163 select count(*) into unique_check
164 from ap_bank_charges
165 where transferring_bank = P_transferring_bank
166 and transferring_branch = P_transferring_branch
167 and receiving_bank = P_receiving_bank
168 and receiving_branch = P_receiving_branch
169 and transfer_priority = P_transfer_priority
170 and currency_code = P_currency_code;
171 end if;
172 end if;
173 else /*1A11*/
174 if (P_transferring_bank = 'ONE') then
175 l_debug_info := '*1A11*';
176 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
177 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
178 END IF;
179
180 select count(*) into unique_check
181 from ap_bank_charges
182 where transferring_bank_name = P_transferring_bank_name
183 and transferring_branch = P_transferring_branch
184 and receiving_bank_branch_id = P_receiving_bank_branch_id
185 and transfer_priority = P_transfer_priority
186 and currency_code = P_currency_code
187 /* bug2191861 add check bank_name */
188 and receiving_bank_name = P_receiving_bank_name ;
189 else /*AA11*/
190 l_debug_info := '*AA11*';
191 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
192 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
193 END IF;
194
195 select count(*) into unique_check
196 from ap_bank_charges
197 where transferring_bank = P_transferring_bank
198 and transferring_branch = P_transferring_branch
199 and receiving_bank_branch_id = P_receiving_bank_branch_id
200 and transfer_priority = P_transfer_priority
201 and currency_code = P_currency_code
202 /* bug2191861 add check bank_name */
203 and receiving_bank_name = P_receiving_bank_name ;
204 end if;
205 end if;
206 elsif (P_receiving_bank_branch_id is NULL) then
207 /* 11A1 */
208 if (P_receiving_bank = 'ONE') then
209 l_debug_info := '*11A1*';
210 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
211 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
212 END IF;
213
214 select count(*) into unique_check
215 from ap_bank_charges
216 where transferring_bank_branch_id = P_transferring_bank_branch_id
217 and receiving_bank_name = P_receiving_bank_name
218 and receiving_branch = P_receiving_branch
219 and transfer_priority = P_transfer_priority
220 and currency_code = P_currency_code
221 /* bug2191861 add check bank_name */
222 and transferring_bank_name = P_transferring_bank_name;
223 else
224 l_debug_info := '*111A*';
225 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
226 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
227 END IF;
228 select count(*) into unique_check
229 from ap_bank_charges
230 where transferring_bank_branch_id = P_transferring_bank_branch_id
231 and receiving_bank = P_receiving_bank
232 and receiving_branch = P_receiving_branch
233 and transfer_priority = P_transfer_priority
234 and currency_code = P_currency_code
235 /* bug2191861 add check bank_name */
236 and transferring_bank_name = P_transferring_bank_name;
237 end if/*P_receiving_bank_branch_id*/;
238 else /*1111*/
239 l_debug_info := '*1111*';
240 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
241 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
242 END IF;
243
244 select count(*) into unique_check
245 from ap_bank_charges
246 where transferring_bank_branch_id = P_transferring_bank_branch_id
247 and receiving_bank_branch_id = P_receiving_bank_branch_id
248 and transfer_priority = P_transfer_priority
249 and currency_code = P_currency_code
250 /* bug2191861 add check bank_name */
251 and receiving_bank_name = P_receiving_bank_name
252 and transferring_bank_name = P_transferring_bank_name
253 ;
254 end if/*P_transferring_bank_branch_id*/;
255
256 if (unique_check<> 0) then
257 RAISE RECORD_EXIST;
258 end if;
259 EXCEPTION
260 WHEN RECORD_EXIST THEN
261 FND_MESSAGE.SET_NAME('SQLAP', 'AP_CHARGE_EXIST');
262 APP_EXCEPTION.RAISE_EXCEPTION;
263 WHEN NO_DATA_FOUND THEN
264 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
265 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
266 FND_MESSAGE.set_TOKEN('DEBUG_INFO', 'Transferring and Receiving Bank Combination'||
267 ' is Invalid');
268 APP_EXCEPTION.RAISE_EXCEPTION;
269 WHEN OTHERS then
270 IF (SQLCODE <> -20001) THEN
271 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
272 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
273 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
274 END IF;
275
276 end CHECK_BANK_COMBINATION;
277
278 PROCEDURE CHECK_RANGE_OVERLAP(
279 X_bank_charge_id IN NUMBER) IS
280
281 CURSOR C_lines(P_bank_charge_id NUMBER) IS
282 select trans_amount_from, nvl(trans_amount_to, 99999999999999),
283 start_date,
284 nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
285 from ap_bank_charge_lines
286 where bank_charge_id = P_bank_charge_id;
287
288 P_bank_charge_id NUMBER;
289 v_trans_amount_from ap_bank_charge_lines.trans_amount_from%type;
290 v_trans_amount_to ap_bank_charge_lines.trans_amount_to%type;
291 v_start_date ap_bank_charge_lines.start_date%type;
292 v_end_date ap_bank_charge_lines.end_date%type;
293
294 overlap NUMBER;
295 l_debug_info Varchar2(2000);
296 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_RANGE_OVERLAP';
297
298 AMOUNT_OVERLAP EXCEPTION;
299
300 BEGIN
301
302 l_debug_info := 'Checking Amount Range Overlap';
303 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
304 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
305 END IF;
306
307 overlap :=0;
308 OPEN C_lines(X_bank_charge_id);
309 LOOP
310 FETCH C_lines INTO v_trans_amount_from,
311 v_trans_amount_to,
312 v_start_date,
313 v_end_date;
314 EXIT WHEN C_lines%NOTFOUND;
315
316 l_debug_info := 'Checking Whether any Amount Overlap exists';
317 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
318 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
319 END IF;
320
321 select count(*) INTO overlap
322 from ap_bank_charge_lines
323 where bank_charge_id = X_bank_charge_id
324 and ((trans_amount_from <= v_trans_amount_from
325 and nvl(trans_amount_to, 99999999999999)
326 > v_trans_amount_from)
327 or (trans_amount_from < v_trans_amount_to
328 and nvl(trans_amount_to, 99999999999999)
329 >= v_trans_amount_to))
330 and ((start_date <= v_start_date
331 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
332 v_start_date)
333 or (start_date < v_end_date
334 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
335 v_end_date));
336
337 if (overlap >1) then
338 RAISE AMOUNT_OVERLAP;
339 end if;
340 END LOOP;
341 CLOSE C_lines;
342
343 EXCEPTION
344 WHEN AMOUNT_OVERLAP THEN
345 FND_MESSAGE.SET_NAME('SQLAP', 'AP_GAPS');
346 APP_EXCEPTION.RAISE_EXCEPTION;
347 WHEN OTHERS THEN
348 IF (SQLCODE <> -20001) THEN
349 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
350 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
351 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
352 END IF;
353 APP_EXCEPTION.RAISE_EXCEPTION;
354 END CHECK_RANGE_OVERLAP;
355
356 PROCEDURE CHECK_RANGE_GAP(X_bank_charge_id IN NUMBER) IS
357
358 CURSOR C_lines(P_bank_charge_id NUMBER) IS
359 select trans_amount_from, start_date,
360 nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
361 from ap_bank_charge_lines
362 where bank_charge_id = P_bank_charge_id;
363
364 P_bank_charge_id NUMBER;
365 v_trans_amount_from ap_bank_charge_lines.trans_amount_from%type;
366 v_start_date ap_bank_charge_lines.start_date%type;
367 v_end_date ap_bank_charge_lines.end_date%type;
368
369 AMOUNT_GAP EXCEPTION;
370 START_ZERO EXCEPTION;
371 gap NUMBER;
372 zero_check NUMBER;
373 l_debug_info Varchar2(2000);
374 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_RANGE_GAP';
375
376
377 BEGIN
378
379 l_debug_info := 'Checking Amount Range Gap';
380 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
382 END IF;
383
384 gap :=0;
385 zero_check :=0;
386
387 OPEN C_lines(X_bank_charge_id);
388 LOOP
389 FETCH C_lines INTO v_trans_amount_from,
390 v_start_date,
394 l_debug_info := 'Checking whether Amount Range Gap exists';
391 v_end_date;
392 EXIT WHEN C_lines%NOTFOUND;
393
395 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
396 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
397 END IF;
398
399 if (v_trans_amount_from <> 0) then
400 select count(*) INTO gap
401 from ap_bank_charge_lines
402 where bank_charge_id = X_bank_charge_id
403 and trans_amount_to = v_trans_amount_from
404 and ((start_date <= v_start_date
405 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
406 v_start_date)
407 or (start_date < v_end_date
408 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
409 v_end_date));
410 if (gap = 0) then
411 RAISE AMOUNT_GAP;
412 end if;
413
414 else
415 zero_check := 1;
416 end if;
417 END LOOP;
418 CLOSE C_lines;
419 if (zero_check = 0) then
420 RAISE START_ZERO;
421 end if;
422 EXCEPTION
423 WHEN AMOUNT_GAP THEN
424 FND_MESSAGE.SET_NAME('SQLAP', 'AP_GAPS');
425 APP_EXCEPTION.RAISE_EXCEPTION;
426 WHEN START_ZERO THEN
427 FND_MESSAGE.SET_NAME('SQLAP', 'AP_NEED_ZERO');
428 APP_EXCEPTION.RAISE_EXCEPTION;
429 WHEN OTHERS THEN
430 IF (SQLCODE <> -20001) THEN
431 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
432 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
433 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
434 END IF;
435 APP_EXCEPTION.RAISE_EXCEPTION;
436 END CHECK_RANGE_GAP;
437
438 PROCEDURE CHECK_LAST_RANGE(X_bank_charge_id IN NUMBER) IS
439 CURSOR C_lines(P_bank_charge_id NUMBER) IS
440 select start_date,
441 nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
442 from ap_bank_charge_lines
443 where bank_charge_id = P_bank_charge_id
444 and trans_amount_to is null;
445
446 v_trans_amount_from ap_bank_charge_lines.trans_amount_from%type;
447 v_trans_amount_to ap_bank_charge_lines.trans_amount_to%type;
448 v_start_date ap_bank_charge_lines.start_date%type;
449 v_end_date ap_bank_charge_lines.end_date%type;
450 cursor_check NUMBER;
451 AMOUNT_GAP EXCEPTION;
452 AMOUNT_OVERLAP EXCEPTION;
453 l_debug_info Varchar2(2000);
454 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_LAST_RANGE';
455
456
457 BEGIN
458
459 l_debug_info := 'Checking Amount Last Range ';
460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
461 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
462 END IF;
463
464 cursor_check := 0;
465 OPEN C_lines(X_bank_charge_id);
466 LOOP
467 FETCH C_lines INTO v_start_date, v_end_date;
468 EXIT WHEN C_lines%NOTFOUND;
469
470 l_debug_info := 'Checking whether it is Amount Last Range ';
471 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
472 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
473 END IF;
474
475 cursor_check := 1;
476 select trans_amount_from into v_trans_amount_from
477 from ap_bank_charge_lines
478 where bank_charge_id = X_bank_charge_id
479 and trans_amount_to is null
480 and ((start_date <= v_start_date
481 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
482 v_start_date)
483 or (start_date < v_end_date
484 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
485 v_end_date));
486
487 if SQL%FOUND then
488 l_debug_info := 'Amount Last Range Found ';
489 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
490 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
491 END IF;
492
493 select max(trans_amount_to)
494 into v_trans_amount_to
495 from ap_bank_charge_lines
496 where bank_charge_id = X_bank_charge_id
497 and ((start_date <= v_start_date
498 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
499 v_start_date)
500 or (start_date < v_end_date
501 and nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >=
502 v_end_date));
503 if (v_trans_amount_from <> 0) and
504 (v_trans_amount_from > v_trans_amount_to) then
505 RAISE AMOUNT_GAP;
506 elsif (v_trans_amount_from <> 0) and
507 (v_trans_amount_from < v_trans_amount_to) then
508 RAISE AMOUNT_OVERLAP;
509 end if;
510
511 end if;
512 END LOOP;
513 CLOSE C_lines;
514 if (cursor_check = 0) then
515 RAISE TOO_MANY_ROWS;
516 end if;
517 EXCEPTION
518 WHEN AMOUNT_GAP THEN
519 FND_MESSAGE.SET_NAME('SQLAP', 'AP_GAPS');
520 APP_EXCEPTION.RAISE_EXCEPTION;
521 WHEN AMOUNT_OVERLAP THEN
522 FND_MESSAGE.SET_NAME('SQLAP', 'AP_OVERLAP1');
523 APP_EXCEPTION.RAISE_EXCEPTION;
524 WHEN NO_DATA_FOUND THEN
525 FND_MESSAGE.SET_NAME('SQLAP', 'AP_LAST4');
526 APP_EXCEPTION.RAISE_EXCEPTION;
527 WHEN TOO_MANY_ROWS THEN
528 FND_MESSAGE.SET_NAME('SQLAP', 'AP_LAST5');
532 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 WHEN OTHERS THEN
531 IF (SQLCODE <> -20001) THEN
533 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
534 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
535 END IF;
536 APP_EXCEPTION.RAISE_EXCEPTION;
537
538 END CHECK_LAST_RANGE;
539
540 PROCEDURE GET_BANK_CHARGE(
541 P_bank_charge_bearer IN VARCHAR2,
542 P_transferring_bank_branch_id IN NUMBER,
543 P_receiving_bank_branch_id IN NUMBER,
544 P_transfer_priority IN VARCHAR2,
545 P_currency_code IN VARCHAR2,
546 P_transaction_amount IN NUMBER,
547 P_transaction_date IN DATE,
548 P_bank_charge_standard OUT NOCOPY NUMBER,
549 P_bank_charge_negotiated OUT NOCOPY NUMBER,
550 P_calc_bank_charge_standard OUT NOCOPY NUMBER,
551 P_calc_bank_charge_negotiated OUT NOCOPY NUMBER,
552 P_tolerance_limit OUT NOCOPY NUMBER) IS
553
554
555 CURSOR C_CHARGE_HEADER(
556 X_transferring_bank_branch_id NUMBER,
557 X_transferring_bank_name VARCHAR2,
558 X_receiving_bank_branch_id NUMBER,
559 X_receiving_bank_name VARCHAR2,
560 X_transfer_priority VARCHAR2,
561 X_currency_code VARCHAR2) IS
562 SELECT bc.bank_charge_id,
563 bc.transferring_bank_branch_id,
564 bc.transferring_bank_name,
565 bc.transferring_bank,
566 bc.transferring_branch,
567 bc.receiving_bank_branch_id,
568 bc.receiving_bank_name,
569 bc.receiving_bank,
570 bc.receiving_branch,
571 bc.transfer_priority,
572 bc.currency_code
573 FROM ap_bank_charges bc, ap_bank_charge_lines bcl
574 WHERE ((bc.transferring_bank_branch_id = X_transferring_bank_branch_id
575 -- bug2242764 added bank_name condition
576 AND bc.transferring_bank_name = X_transferring_bank_name)
577 OR (bc.transferring_bank_name = X_transferring_bank_name
578 AND bc.transferring_branch = 'ALL')
579 OR (bc.transferring_bank = 'ALL'
580 AND bc.transferring_branch = 'ALL'))
581 AND ((bc.receiving_bank_branch_id = X_receiving_bank_branch_id
582 -- bug2242764 added bank_name condition
583 AND bc.receiving_bank_name = X_receiving_bank_name )
584 OR (bc.receiving_bank_name = X_receiving_bank_name
585 AND bc.receiving_branch in ('ALL', 'OTHER'))
586 OR (bc.receiving_bank in ('ALL', 'OTHER')
587 AND bc.receiving_branch = 'ALL'))
588 AND (bc.transfer_priority = X_transfer_priority
589 OR bc.transfer_priority = 'AR'
590 OR bc.transfer_priority = 'ANY')
591 AND bc.currency_code = X_currency_code
592 AND bc.bank_charge_id = bcl.bank_charge_id -- Bug 2073366
593 AND bcl.start_date <= P_transaction_date
594 AND nvl(bcl.end_date,
595 to_date('31-12-4712', 'DD-MM-YYYY')) > P_transaction_date;
596
597
598 CURSOR C_get_bank_name(X_bank_branch_id NUMBER) IS
599 SELECT bank_name
600 FROM ce_bank_branches_v
601 WHERE branch_party_id = X_bank_branch_id;
602
603 CURSOR C_precision(X_currency_code VARCHAR2) IS
604 SELECT precision
605 FROM fnd_currencies
606 WHERE currency_code = X_currency_code;
607
608 CURSOR C_CHARGE_LINE(X_bank_charge_id NUMBER,
609 X_transaction_date DATE) IS
610 SELECT trans_amount_from,
611 nvl(trans_amount_to, 99999999999999),
612 bank_charge_standard,
613 bank_charge_negotiated,
614 tolerance_limit
615 FROM ap_bank_charge_lines
616 WHERE bank_charge_id = X_bank_charge_id
617 AND (start_date <= X_transaction_date
618 AND nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
619 X_transaction_date)
620 ORDER BY trans_amount_from desc;
621
622
623 P_bank_charge_id NUMBER;
624 P_transferring_bank_name ce_bank_branches_v.bank_name%TYPE;
625 P_receiving_bank_name ce_bank_branches_v.bank_name%TYPE;
626
627 v_bank_charge_id NUMBER;
628 v_trans_bank_branch_id NUMBER;
629 v_trans_bank_name ce_bank_branches_v.bank_name%TYPE;
630 v_trans_bank ce_bank_branches_v.bank_name%TYPE;
631 v_trans_branch ce_bank_branches_v.bank_branch_name%TYPE;
632 v_recei_bank_branch_id NUMBER;
633 v_recei_bank_name ce_bank_branches_v.bank_name%TYPE;
634 v_recei_bank ce_bank_branches_v.bank_name%TYPE;
635 v_recei_branch ce_bank_branches_v.bank_branch_name%TYPE;
636 v_transfer_priority VARCHAR2(30);
637 v_currency_code VARCHAR2(15);
638
639 priority NUMBER;
640 temp_priority NUMBER;
644 v_transaction_amount NUMBER;
641 temp_bank_charge_id NUMBER;
642 v_precision NUMBER;
643
645 v_trans_amount_from NUMBER;
646 v_trans_amount_to NUMBER;
647 v_bank_charge_standard NUMBER;
648 v_bank_charge_negotiated NUMBER;
649 v_tolerance_limit NUMBER;
650
651 amount_bank_charge NUMBER;
652 NO_BANK_CHARGES EXCEPTION;
653 l_debug_info Varchar2(2000);
654 l_api_name CONSTANT VARCHAR2(100) := 'GET_BANK_CHARGE';
655
656
657 BEGIN
658
659 l_debug_info := 'Get Bank Charge Begin';
660 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
661 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
662 END IF;
663
664 v_precision := 0;
665 P_bank_charge_id := 0;
666 P_bank_charge_standard :=0;
667 P_bank_charge_negotiated :=0;
668 priority := 37;
669 temp_priority :=37;
670 temp_bank_charge_id := 0;
671
672 if (P_bank_charge_bearer is not null) or
673 (P_transfer_priority is null) then
674
675 l_debug_info := 'Opening cursor for transferring bank info';
676 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
677 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
678 END IF;
679 OPEN C_get_bank_name(P_transferring_bank_branch_id);
680 FETCH C_get_bank_name INTO P_transferring_bank_name;
681 CLOSE C_get_bank_name;
682
683 l_debug_info := 'Opening cursor for receiving bank info';
684 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
685 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
686 END IF;
687 OPEN C_get_bank_name(P_receiving_bank_branch_id);
688 FETCH C_get_bank_name INTO P_receiving_bank_name;
689 CLOSE C_get_bank_name;
690
691 l_debug_info := 'Opening cursor for charge header';
692 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
693 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
694 END IF;
695 OPEN C_CHARGE_HEADER(
696 P_transferring_bank_branch_id,
697 P_transferring_bank_name,
698 P_receiving_bank_branch_id,
699 P_receiving_bank_name,
700 P_transfer_priority,
701 P_currency_code);
702
703 LOOP
704 FETCH C_CHARGE_HEADER INTO
705 v_bank_charge_id,
706 v_trans_bank_branch_id,
707 v_trans_bank_name,
708 v_trans_bank,
709 v_trans_branch,
710 v_recei_bank_branch_id,
711 v_recei_bank_name,
712 v_recei_bank,
713 v_recei_branch,
714 v_transfer_priority,
715 v_currency_code;
716 EXIT WHEN C_CHARGE_HEADER%NOTFOUND;
717
718 if((P_bank_charge_bearer is not null) and
719 (v_transfer_priority <>'AR')) or
720 ((P_bank_charge_bearer is null) and
721 (v_transfer_priority ='AR')) then
722 if (v_trans_bank_branch_id is not null) then
723 if (v_recei_bank_branch_id is not null) then
724 temp_priority := 1;
725 temp_bank_charge_id := v_bank_charge_id;
726 elsif(v_recei_bank = 'ONE') then
727 if(v_recei_branch = 'OTHER') then
728 temp_priority := 2;
729 temp_bank_charge_id := v_bank_charge_id;
730 elsif(v_recei_branch = 'ALL') then
731 temp_priority :=3;
732 temp_bank_charge_id :=v_bank_charge_id;
733 end if;
734 elsif(v_recei_bank = 'OTHER') then
735 temp_priority := 4;
736 temp_bank_charge_id := v_bank_charge_id;
737 elsif(v_recei_bank = 'ALL') then
738 temp_priority := 5;
739 temp_bank_charge_id := v_bank_charge_id;
740 end if;
741 elsif(v_trans_bank = 'ONE') then
742 if(v_recei_bank_branch_id is not null) then
743 temp_priority := 6;
744 temp_bank_charge_id := v_bank_charge_id;
745 elsif(v_recei_bank = 'ONE') then
746 temp_priority := 7;
747 temp_bank_charge_id := v_bank_charge_id;
748 elsif(v_recei_bank = 'OTHER') then
749 temp_priority := 8;
750 temp_bank_charge_id := v_bank_charge_id;
751 elsif(v_recei_bank = 'ALL')then
752 temp_priority := 9;
753 temp_bank_charge_id := v_bank_charge_id;
754 end if;
755 elsif(v_trans_bank = 'ALL') then
756 if(v_recei_bank_branch_id is not null) then
757 temp_priority := 10;
758 temp_bank_charge_id := v_bank_charge_id;
759 elsif(v_recei_bank = 'ONE') then
760 temp_priority := 11;
761 temp_bank_charge_id := v_bank_charge_id;
762 elsif(v_recei_bank = 'ALL') then
763 temp_priority := 12;
764 temp_bank_charge_id := v_bank_charge_id;
765 end if;
766 end if;
767
768 if (P_receiving_bank_branch_id is not null) or
772 (v_transfer_priority = 'EXPRESS') then
769 ((temp_priority <> 2) and (temp_priority <> 4) and
770 (temp_priority <> 8)) then
771 if(v_transfer_priority ='AR') or
773 temp_priority := 3*temp_priority -2;
774 elsif(v_transfer_priority = 'NORMAL') then
775 temp_priority := 3*temp_priority -1;
776 else
777 temp_priority := 3*temp_priority;
778 end if;
779
780 if (priority > temp_priority) then
781 priority := temp_priority;
782 P_bank_charge_id := temp_bank_charge_id;
783 end if;
784 end if;
785 end if;
786 END LOOP;
787 CLOSE C_CHARGE_HEADER;
788
789 if (P_bank_charge_id <>0) then
790 /**************************************************************/
791 /* Change the Supplier to appropriate value */
792 /* NOTE: Bank_Charge_Bearer I: Internal */
793 /* S: Supplier/Standard */
794 /* N: Supplier/Negotiated */
795
796 l_debug_info := 'Bank Charge Id exits';
797 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
798 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
799 END IF;
800
801 if(P_transaction_amount is NULL) then
802 v_transaction_amount := 0;
803 else
804 v_transaction_amount := P_transaction_amount;
805 end if;
806 if(P_bank_charge_bearer = 'I') or
807 ((P_bank_charge_bearer is null)and
808 (P_transfer_priority is null)) then
809 l_debug_info := 'Bank Charge Bearer is I';
810 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
811 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
812 END IF;
813
814 SELECT bank_charge_standard,
815 bank_charge_negotiated,
816 tolerance_limit
817 INTO P_bank_charge_standard,
818 P_bank_charge_negotiated,
819 P_tolerance_limit
820 FROM ap_bank_charge_lines
821 WHERE bank_charge_id = P_bank_charge_id
822 AND trans_amount_from <= v_transaction_amount
823 AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
824 AND start_date <= P_transaction_date
825 AND nvl(end_date,
826 to_date('31-12-4712', 'DD-MM-YYYY')) >
827 P_transaction_date;
828 elsif(P_bank_charge_bearer = 'S') or
829 (P_bank_charge_bearer = 'N') then
830
831 l_debug_info := 'Bank Charge Bearer is not I';
832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
833 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
834 END IF;
835
836 OPEN C_CHARGE_LINE(P_bank_charge_id,
837 P_transaction_date);
838
839 LOOP
840
841 FETCH C_CHARGE_LINE INTO v_trans_amount_from,
842 v_trans_amount_to,
843 v_bank_charge_standard,
844 v_bank_charge_negotiated,
845 v_tolerance_limit;
846 EXIT WHEN C_CHARGE_LINE%NOTFOUND;
847
848 /*
849 1542954 fbreslin: Bank charges were not getting calculated correctly in corner
850 cases.Commented below code as amount_bank_charge variable is
851 not used further in the procedure.
852 Also replace amount_bank_charge with v_transaction_amount to.
853
854 if(P_bank_charge_bearer = 'S') then
855 amount_bank_charge :=v_transaction_amount -
856 v_bank_charge_standard;
857 else
858 amount_bank_charge :=v_transaction_amount -
859 v_bank_charge_negotiated;
860 end if;
861 */
862
863 if(v_transaction_amount >= 0 ) then
864 if(v_transaction_amount >= v_trans_amount_from) then
865 if(v_transaction_amount < v_trans_amount_to) then
866 P_bank_charge_standard := v_bank_charge_standard;
867 P_bank_charge_negotiated := v_bank_charge_negotiated;
868 P_tolerance_limit := v_tolerance_limit;
869 EXIT;
870 else
871 P_bank_charge_standard := v_bank_charge_standard;
872 P_bank_charge_negotiated := v_bank_charge_negotiated;
873 OPEN C_precision(P_currency_code);
874 FETCH C_precision INTO v_precision;
875 CLOSE C_precision;
876 if(P_bank_charge_bearer = 'S') then
877 P_calc_bank_charge_standard :=
878 v_transaction_amount - v_trans_amount_to
879 + 1/power(10, v_precision);
880 else
884 end if;
881 P_calc_bank_charge_negotiated :=
882 v_transaction_amount - v_trans_amount_to
883 + 1/power(10, v_precision);
885 P_tolerance_limit := v_tolerance_limit;
886 EXIT;
887 end if;
888 end if;
889 end if;
890 END LOOP;
891 CLOSE C_CHARGE_LINE;
892 if (amount_bank_charge <0) then
893 RAISE NO_BANK_CHARGES;
894 end if;
895 else
896 RAISE NO_BANK_CHARGES;
897 end if;
898 else
899 RAISE NO_BANK_CHARGES;
900 end if;
901 else
902 RAISE NO_BANK_CHARGES;
903 end if;
904
905 P_tolerance_limit := 0 ;
906
907 EXCEPTION
908 WHEN NO_BANK_CHARGES THEN
909 p_tolerance_limit := NULL;
910 FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_CHARGE_FOUND');
911 FND_MSG_PUB.ADD;
912 --APP_EXCEPTION.RAISE_EXCEPTION;
913 WHEN NO_DATA_FOUND THEN
914 FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_CHARGE_FOUND');
915 FND_MSG_PUB.ADD;
916 p_tolerance_limit := NULL;
917 --APP_EXCEPTION.RAISE_EXCEPTION;
918 WHEN OTHERS THEN
919 IF (SQLCODE <> -20001) THEN
920 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
921 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
922 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
923 END IF;
924 p_tolerance_limit := NULL;
925 FND_MSG_PUB.ADD;
926 --APP_EXCEPTION.RAISE_EXCEPTION;
927
928
929 END GET_BANK_CHARGE;
930
931 PROCEDURE CHECK_BANK_CHARGE(
932 P_bank_charge_bearer IN VARCHAR2,
933 P_transferring_bank_branch_id IN NUMBER,
934 P_receiving_bank_branch_id IN NUMBER,
935 P_transfer_priority IN VARCHAR2,
936 P_currency_code IN VARCHAR2,
937 P_transaction_amount IN NUMBER,
938 P_transaction_date IN DATE,
939 P_check_bc_flag OUT NOCOPY VARCHAR2,
940 P_do_not_pay_reason OUT NOCOPY VARCHAR2) IS
941
942
943 CURSOR C_CHARGE_HEADER(
944 X_transferring_bank_branch_id NUMBER,
945 X_transferring_bank_name VARCHAR2,
946 X_receiving_bank_branch_id NUMBER,
947 X_receiving_bank_name VARCHAR2,
948 X_transfer_priority VARCHAR2,
949 X_currency_code VARCHAR2) IS
950 SELECT bc.bank_charge_id,
951 bc.transferring_bank_branch_id,
952 bc.transferring_bank_name,
953 bc.transferring_bank,
954 bc.transferring_branch,
955 bc.receiving_bank_branch_id,
956 bc.receiving_bank_name,
957 bc.receiving_bank,
958 bc.receiving_branch,
959 bc.transfer_priority,
960 bc.currency_code
961 FROM ap_bank_charges bc, ap_bank_charge_lines bcl
962 WHERE ((bc.transferring_bank_branch_id = X_transferring_bank_branch_id
963 -- bug2242764 added bank_name condition
964 AND transferring_bank_name = X_transferring_bank_name)
965 OR (bc.transferring_bank_name = X_transferring_bank_name
966 AND bc.transferring_branch = 'ALL')
967 OR (bc.transferring_bank = 'ALL'
968 AND bc.transferring_branch = 'ALL'))
969 AND ((bc.receiving_bank_branch_id = X_receiving_bank_branch_id
970 -- bug2242764 added bank_name condition
971 AND receiving_bank_name = X_receiving_bank_name)
972 OR (bc.receiving_bank_name = X_receiving_bank_name
973 AND bc.receiving_branch in ('ALL', 'OTHER'))
974 OR (bc.receiving_bank in ('ALL', 'OTHER')
975 AND bc.receiving_branch = 'ALL'))
976 AND (bc.transfer_priority = X_transfer_priority
977 OR bc.transfer_priority = 'AR'
978 OR bc.transfer_priority = 'ANY')
979 AND bc.currency_code = X_currency_code
980 AND bc.bank_charge_id = bcl.bank_charge_id -- Bug 2177997
981 AND bcl.start_date <= P_transaction_date
982 AND nvl(bcl.end_date,
983 to_date('31-12-4712', 'DD-MM-YYYY')) > P_transaction_date;
984
985 CURSOR C_get_bank_name(X_bank_branch_id NUMBER) IS
986 SELECT bank_name
987 FROM ce_bank_branches_v
988 WHERE branch_party_id = X_bank_branch_id;
989
990 CURSOR C_precision(X_currency_code VARCHAR2) IS
991 SELECT precision
992 FROM fnd_currencies
993 WHERE currency_code = X_currency_code;
994
995 CURSOR C_CHARGE_LINE(X_bank_charge_id NUMBER,
996 X_transaction_date DATE) IS
997 SELECT trans_amount_from,
998 nvl(trans_amount_to, 99999999999999),
999 bank_charge_standard,
1000 bank_charge_negotiated,
1001 tolerance_limit
1002 FROM ap_bank_charge_lines
1003 WHERE bank_charge_id = X_bank_charge_id
1004 AND (start_date <= X_transaction_date
1005 AND nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
1006 X_transaction_date)
1007 ORDER BY trans_amount_from desc;
1008
1009
1010 P_bank_charge_id NUMBER;
1011 P_transferring_bank_name ce_bank_branches_v.bank_name%TYPE;
1012 P_receiving_bank_name ce_bank_branches_v.bank_name%TYPE;
1013
1014 v_bank_charge_id NUMBER;
1015 v_trans_bank_branch_id NUMBER;
1016 v_trans_bank_name ce_bank_branches_v.bank_name%TYPE;
1017 v_trans_bank ce_bank_branches_v.bank_name%TYPE;
1018 v_trans_branch ce_bank_branches_v.bank_branch_name%TYPE;
1019 v_recei_bank_branch_id NUMBER;
1020 v_recei_bank_name ce_bank_branches_v.bank_name%TYPE;
1021 v_recei_bank ce_bank_branches_v.bank_name%TYPE;
1022 v_recei_branch ce_bank_branches_v.bank_branch_name%TYPE;
1023 v_transfer_priority VARCHAR2(30);
1024 v_currency_code VARCHAR2(15);
1025
1026 priority NUMBER;
1027 temp_priority NUMBER;
1028 temp_bank_charge_id NUMBER;
1029 v_precision NUMBER;
1030
1031 v_transaction_amount NUMBER;
1032 v_trans_amount_from NUMBER;
1033 v_trans_amount_to NUMBER;
1034 v_bank_charge_standard NUMBER;
1035 v_bank_charge_negotiated NUMBER;
1036 v_tolerance_limit NUMBER;
1037
1038 amount_bank_charge NUMBER;
1039 NO_BANK_CHARGES EXCEPTION;
1040
1041 l_debug_info Varchar2(2000);
1042 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_BANK_CHARGE';
1043
1044
1045 BEGIN
1046
1047 l_debug_info := 'Check Bank Charge Begin';
1048 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1049 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1050 END IF;
1051
1052 v_precision := 0;
1053 P_bank_charge_id := 0;
1054 priority := 37;
1055 temp_priority :=37;
1056 temp_bank_charge_id := 0;
1057 P_check_bc_flag := 'N';
1058 P_do_not_pay_reason := '';
1059
1060 if (P_bank_charge_bearer is not null) or
1061 (P_transfer_priority is null) then
1062 l_debug_info := 'Opening Cursor for transferring bank info';
1063 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1064 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1065 END IF;
1066
1067 OPEN C_get_bank_name(P_transferring_bank_branch_id);
1068 FETCH C_get_bank_name INTO P_transferring_bank_name;
1069 CLOSE C_get_bank_name;
1070
1071 l_debug_info := 'Opening Cursor for receiving bank info';
1072 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1073 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1074 END IF;
1075
1076 OPEN C_get_bank_name(P_receiving_bank_branch_id);
1077 FETCH C_get_bank_name INTO P_receiving_bank_name;
1078 CLOSE C_get_bank_name;
1079
1080 l_debug_info := 'Opening Charge Header Cursor';
1081 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1082 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1083 END IF;
1084
1085 OPEN C_CHARGE_HEADER(
1086 P_transferring_bank_branch_id,
1087 P_transferring_bank_name,
1088 P_receiving_bank_branch_id,
1089 P_receiving_bank_name,
1090 P_transfer_priority,
1091 P_currency_code);
1092
1093 LOOP
1094 FETCH C_CHARGE_HEADER INTO
1095 v_bank_charge_id,
1096 v_trans_bank_branch_id,
1097 v_trans_bank_name,
1098 v_trans_bank,
1099 v_trans_branch,
1100 v_recei_bank_branch_id,
1101 v_recei_bank_name,
1102 v_recei_bank,
1103 v_recei_branch,
1104 v_transfer_priority,
1105 v_currency_code;
1106 EXIT WHEN C_CHARGE_HEADER%NOTFOUND;
1107
1108 if((P_bank_charge_bearer is not null) and
1109 (v_transfer_priority <>'AR')) or
1110 ((P_bank_charge_bearer is null) and
1111 (v_transfer_priority ='AR')) then
1112 if (v_trans_bank_branch_id is not null) then
1113 if (v_recei_bank_branch_id is not null) then
1114 temp_priority := 1;
1115 temp_bank_charge_id := v_bank_charge_id;
1116 elsif(v_recei_bank = 'ONE') then
1117 if(v_recei_branch = 'OTHER') then
1118 temp_priority := 2;
1119 temp_bank_charge_id := v_bank_charge_id;
1120 elsif(v_recei_branch = 'ALL') then
1121 temp_priority :=3;
1122 temp_bank_charge_id :=v_bank_charge_id;
1123 end if;
1124 elsif(v_recei_bank = 'OTHER') then
1128 temp_priority := 5;
1125 temp_priority := 4;
1126 temp_bank_charge_id := v_bank_charge_id;
1127 elsif(v_recei_bank = 'ALL') then
1129 temp_bank_charge_id := v_bank_charge_id;
1130 end if;
1131 elsif(v_trans_bank = 'ONE') then
1132 if(v_recei_bank_branch_id is not null) then
1133 temp_priority := 6;
1134 temp_bank_charge_id := v_bank_charge_id;
1135 elsif(v_recei_bank = 'ONE') then
1136 temp_priority := 7;
1137 temp_bank_charge_id := v_bank_charge_id;
1138 elsif(v_recei_bank = 'OTHER') then
1139 temp_priority := 8;
1140 temp_bank_charge_id := v_bank_charge_id;
1141 elsif(v_recei_bank = 'ALL')then
1142 temp_priority := 9;
1143 temp_bank_charge_id := v_bank_charge_id;
1144 end if;
1145 elsif(v_trans_bank = 'ALL') then
1146 if(v_recei_bank_branch_id is not null) then
1147 temp_priority := 10;
1148 temp_bank_charge_id := v_bank_charge_id;
1149 elsif(v_recei_bank = 'ONE') then
1150 temp_priority := 11;
1151 temp_bank_charge_id := v_bank_charge_id;
1152 elsif(v_recei_bank = 'ALL') then
1153 temp_priority := 12;
1154 temp_bank_charge_id := v_bank_charge_id;
1155 end if;
1156 end if;
1157
1158 if (P_receiving_bank_branch_id is not null) or
1159 ((temp_priority <> 2) and (temp_priority <> 4) and
1160 (temp_priority <> 8)) then
1161 if(v_transfer_priority ='AR') or
1162 (v_transfer_priority = 'EXPRESS') then
1163 temp_priority := 3*temp_priority -2;
1164 elsif(v_transfer_priority = 'NORMAL') then
1165 temp_priority := 3*temp_priority -1;
1166 else
1167 temp_priority := 3*temp_priority;
1168 end if;
1169
1170 if (priority > temp_priority) then
1171 priority := temp_priority;
1172 P_bank_charge_id := temp_bank_charge_id;
1173 end if;
1174 end if;
1175 end if;
1176 END LOOP;
1177 CLOSE C_CHARGE_HEADER;
1178 if (P_bank_charge_id <>0) then
1179 /**************************************************************/
1180 /* Change the Supplier to appropriate value */
1181 /* NOTE: Bank_Charge_Bearer I: Internal */
1182 /* S: Supplier/Standard */
1183 /* N: Supplier/Negotiated */
1184
1185 l_debug_info := 'Bank Charge Id exits';
1186 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1187 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1188 END IF;
1189
1190 if(P_transaction_amount is NULL) then
1191 v_transaction_amount := 0;
1192 else
1193 v_transaction_amount := P_transaction_amount;
1194 end if;
1195 if(P_bank_charge_bearer = 'I') or
1196 ((P_bank_charge_bearer is null)and
1197 (P_transfer_priority is null)) then
1198 l_debug_info := 'Bank Charge Bearer is I';
1199 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1200 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1201 END IF;
1202
1203 SELECT bank_charge_standard,
1204 bank_charge_negotiated,
1205 tolerance_limit
1206 INTO v_bank_charge_standard,
1207 v_bank_charge_negotiated,
1208 v_tolerance_limit
1209 FROM ap_bank_charge_lines
1210 WHERE bank_charge_id = P_bank_charge_id
1211 AND trans_amount_from <= v_transaction_amount
1212 AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
1213 AND start_date <= P_transaction_date
1214 AND nvl(end_date,
1215 to_date('31-12-4712', 'DD-MM-YYYY')) >
1216 P_transaction_date;
1217 P_check_bc_flag := 'Y';
1218 elsif(P_bank_charge_bearer = 'S') or
1219 (P_bank_charge_bearer = 'N') then
1220
1221 l_debug_info := 'Bank Charge Bearer is not I';
1222 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1223 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1224 END IF;
1225
1226
1227 OPEN C_CHARGE_LINE(P_bank_charge_id,
1228 P_transaction_date);
1229 LOOP
1230 FETCH C_CHARGE_LINE INTO v_trans_amount_from,
1231 v_trans_amount_to,
1232 v_bank_charge_standard,
1233 v_bank_charge_negotiated,
1234 v_tolerance_limit;
1235 EXIT WHEN C_CHARGE_LINE%NOTFOUND;
1236 if(P_bank_charge_bearer = 'S') then
1237 amount_bank_charge :=v_transaction_amount -
1238 v_bank_charge_standard;
1242 end if;
1239 else
1240 amount_bank_charge :=v_transaction_amount -
1241 v_bank_charge_negotiated;
1243 if(amount_bank_charge >= 0 ) then
1244 if(amount_bank_charge >= v_trans_amount_from) then
1245 P_check_bc_flag := 'Y';
1246 EXIT;
1247 end if;
1248 end if;
1249 END LOOP;
1250
1251 -- for BUG 1714850
1252 if C_CHARGE_LINE%ROWCOUNT = 0
1253 then
1254 P_check_bc_flag := 'N';
1255 P_do_not_pay_reason := 'NO BANK CHARGE';
1256 end if;
1257
1258 CLOSE C_CHARGE_LINE;
1259 if (amount_bank_charge <0) then
1260 P_check_bc_flag := 'N';
1261 P_do_not_pay_reason := 'BC GREATER THAN AMOUNT';
1262 end if;
1263 else
1264 RAISE NO_BANK_CHARGES;
1265 end if;
1266 else
1267 RAISE NO_BANK_CHARGES;
1268 end if;
1269 else
1270 RAISE NO_BANK_CHARGES;
1271 end if;
1272 EXCEPTION
1273 WHEN NO_BANK_CHARGES THEN
1274 P_check_bc_flag := 'N';
1275 P_do_not_pay_reason := 'NO BANK CHARGE';
1276 WHEN NO_DATA_FOUND THEN
1277 P_check_bc_flag := 'N';
1278 P_do_not_pay_reason := 'NO BANK CHARGE';
1279 WHEN OTHERS THEN
1280 IF (SQLCODE <> -20001) THEN
1281 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
1282 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
1283 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
1284 END IF;
1285 FND_MSG_PUB.ADD;
1286
1287 END CHECK_BANK_CHARGE;
1288
1289
1290 FUNCTION Bank_charge_get_info(
1291 p_selected_check_id IN NUMBER,
1292 p_external_bank_account_id IN NUMBER,
1293 p_currency_code IN VARCHAR2,
1294 p_minimum_accountable_unit OUT nocopy NUMBER,
1295 p_precision OUT nocopy NUMBER,
1296 p_bank_charge_bearer OUT nocopy VARCHAR2,
1297 p_transferring_bank_branch_id OUT nocopy NUMBER,
1298 p_receiving_bank_branch_id OUT nocopy NUMBER,
1299 p_transfer_priority OUT nocopy VARCHAR2,
1300 p_num_of_invoices OUT nocopy NUMBER,
1301 p_calling_sequence IN VARCHAR2,
1302 p_internal_bank_account_id IN NUMBER,
1303 p_supplier_site_id IN NUMBER) RETURN BOOLEAN IS
1304
1305 current_calling_sequence VARCHAR2(2000);
1306 l_debug_info Varchar2(2000);
1307 l_api_name CONSTANT VARCHAR2(100) := 'BANK_CHARGE_GET_INFO';
1308
1309
1310 BEGIN
1311
1312 current_calling_sequence := 'bank_charge_get_info<-'||P_calling_sequence;
1313 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1314 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,current_calling_sequence);
1315 END IF;
1316
1317 l_debug_info := 'Get bank charge bearer from po vendor sites';
1318 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1319 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1320 END IF;
1321
1322
1323 IF p_supplier_site_id IS NOT NULL THEN
1324 SELECT PVS.bank_charge_bearer
1325 INTO p_bank_charge_bearer
1326 FROM iby_hook_payments_t iby,
1327 ap_supplier_sites_all PVS
1328 WHERE iby.payment_id = p_selected_check_id
1329 AND iby.supplier_site_id = PVS.vendor_site_id;
1330 END IF;
1331
1332 l_debug_info := 'Get p_transferring_bank_branch_id';
1333 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1335 END IF;
1336
1337 SELECT ABA.bank_branch_id
1338 INTO p_transferring_bank_branch_id
1339 FROM ce_bank_accounts ABA
1340 WHERE aba.bank_account_id = p_internal_bank_account_id;
1341
1342 l_debug_info := 'Get p_receiving_bank_branch_id';
1343 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1345 END IF;
1346
1347 if p_external_bank_account_id is not null then
1348
1349 SELECT ieb.branch_id
1350 INTO p_receiving_bank_branch_id
1351 FROM iby_ext_bank_accounts ieb
1352 WHERE ieb.ext_bank_account_id = p_external_bank_account_id;
1353
1354 end if;
1355
1356 l_debug_info := 'Get transfer_priority and currency code';
1357 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1358 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1359 END IF;
1360
1361
1362 SELECT AISC.transfer_priority
1363 INTO p_transfer_priority
1364 FROM ap_inv_selection_criteria_ALL AISC,
1365 iby_hook_docs_in_pmt_t IBY
1366 WHERE IBY.CALLING_APP_DOC_UNIQUE_REF1 = AISC.CHECKRUN_ID
1367 and rownum=1;
1368
1369
1370 l_debug_info := 'Get number of invoices for this check';
1371 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1372 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1373 END IF;
1374
1375 SELECT count(*)
1376 INTO p_num_of_invoices
1377 FROM iby_hook_payments_t
1378 WHERE payment_id = p_selected_check_id;
1379
1383 END IF;
1380 l_debug_info := 'Get min_account_unit and precision for currency';
1381 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1382 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1384
1385 SELECT minimum_accountable_unit,
1386 nvl(precision, 0)
1387 INTO p_minimum_accountable_unit,
1388 p_precision
1389 FROM fnd_currencies_vl
1390 WHERE currency_code = p_currency_code;
1391
1392 RETURN (TRUE);
1393
1394 EXCEPTION
1395 WHEN OTHERS then
1396 IF (SQLCODE < 0 ) then
1397 null; --rlandows SRW.MESSAGE('999',SQLERRM);
1398 END IF;
1399
1400 RETURN (FALSE);
1401
1402 END bank_charge_get_info;
1403
1404
1405
1406 FUNCTION Bank_charge_get_amt_due(
1407 p_selected_check_id IN NUMBER,
1408 p_amount_due OUT nocopy NUMBER,
1409 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
1410
1411 debug_info VARCHAR2(200);
1412 current_calling_sequence VARCHAR2(2000);
1413
1414 BEGIN
1415
1416 current_calling_sequence := 'bank_charge_get_amt_due<-'||P_calling_sequence;
1417
1418
1419 debug_info := 'Get p_amount_due';
1420
1421 SELECT sum(decode(dont_pay_flag, 'Y', 0,
1422 document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN,0)))
1423 INTO p_amount_due
1424 FROM iby_hook_docs_in_pmt_t
1425 WHERE payment_id= p_selected_check_id;
1426
1427
1428 RETURN (TRUE);
1429
1430 EXCEPTION
1431
1432 WHEN OTHERS THEN
1433
1434 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1435 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1436 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1437 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1438 ' p_selected_check_id = '||to_char(p_selected_check_id));
1439 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1440 --APP_EXCEPTION.RAISE_EXCEPTION;
1441
1442 RETURN (FALSE);
1443
1444 END bank_charge_get_amt_due;
1445
1446
1447
1448 PROCEDURE ap_JapanBankChargeHook(
1449 p_api_version IN NUMBER,
1450 p_init_msg_list IN VARCHAR2,
1451 p_commit IN VARCHAR2,
1452 x_return_status OUT nocopy VARCHAR2,
1453 x_msg_count OUT nocopy NUMBER,
1454 x_msg_data OUT nocopy VARCHAR2)
1455 is
1456
1457
1458 CURSOR selected_checks IS
1459 SELECT iby.payment_id,
1460 iby.payment_currency_code,
1461 iby.payment_date,
1462 iby.external_bank_account_id,
1463 iby.dont_pay_flag,
1464 iby.internal_bank_account_id,
1465 iby.supplier_site_id
1466 FROM iby_hook_payments_t iby,
1467 ap_system_parameters_all asp --5007989
1468 WHERE dont_pay_flag <> 'Y'
1469 AND nvl(dont_pay_reason_code,'dummy') <> 'OVERFLOW'
1470 AND asp.org_id = iby.org_id
1471 AND nvl(asp.use_bank_charge_flag,'N') = 'Y'
1472 ORDER BY payment_id;
1473
1474
1475 CURSOR adjustment_for_rounding_error (c_selected_check_id NUMBER,
1476 c_rounding_error NUMBER) IS
1477 SELECT PAYMENT_CURR_DISCOUNT_TAKEN
1478 FROM iby_hook_docs_in_pmt_t
1479 WHERE payment_id = c_selected_check_id
1480 AND ABS(document_amount) >= ABS(c_rounding_error)
1481 ORDER BY PAYMENT_CURR_DISCOUNT_TAKEN desc;
1482
1483
1484 l_selected_check_id NUMBER;
1485 l_currency_code VARCHAR2(15);
1486 l_payment_date DATE;
1487 l_external_bank_account_id NUMBER;
1488 l_bank_charge_bearer VARCHAR2(1);
1489 l_transferring_bank_branch_id NUMBER;
1490 l_ok_to_pay_flag VARCHAR2(1);
1491 l_bc_ok_to_pay_flag VARCHAR2(1);
1492 l_bc_dont_pay_reason_code VARCHAR2(25);
1493 l_receiving_bank_branch_id NUMBER;
1494 l_transfer_priority VARCHAR2(25);
1495 l_bank_charge_standard NUMBER;
1496 l_bank_charge_negotiated NUMBER;
1497 l_calc_bank_charge_standard NUMBER;
1498 l_calc_bank_charge_negotiated NUMBER;
1499 l_tolerance_limit NUMBER;
1500 l_best_bank_charge NUMBER;
1501 l_num_of_invoices NUMBER;
1502 l_prorate_bank_charge NUMBER;
1503 l_rounding_error NUMBER;
1504 l_min_account_unit NUMBER;
1505 l_precision NUMBER;
1506 l_amt_due NUMBER;
1507 l_payment_method VARCHAR2(25);
1508 l_max_discount_amount NUMBER;
1509 l_rem_rounding_error_amount NUMBER; /*1649310 */
1510 l_supplier_site_id NUMBER;
1511
1512 bank_charge_failure EXCEPTION;
1513 l_debug_info VARCHAR2(200);
1514 current_calling_sequence VARCHAR2(2000);
1515
1516 l_internal_bank_account_id number;
1517 l_api_name CONSTANT VARCHAR2(30) := 'ap_JapanBankChargeHook';
1518 l_api_version CONSTANT NUMBER := 1.0;
1519
1520
1521 BEGIN
1522 -- Update the calling sequence
1523 --
1524 current_calling_sequence := 'AP_BANK_CHARGE_PKG.AP_JAPANBANKCHARGEHOOK';
1525
1526 -------------------------------------------------------------------------
1527 -- Step 0, Return true and do nothing if use_bank_charge_flag is not 'Y'
1528 -------------------------------------------------------------------------
1529
1530 l_debug_info := 'Creating Savepoint';
1534
1531 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1532 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1533 END IF;
1535 -- Standard Start of API savepoint
1536 SAVEPOINT AP_JAPANBANKCHARGEHOOK;
1537
1538 l_debug_info := 'Checking API Compatibility';
1539 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1540 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1541 END IF;
1542
1543 -- Standard call to check for call compatibility.
1544 IF NOT FND_API.Compatible_API_Call (l_api_version,
1545 p_api_version,
1546 l_api_name,
1547 G_PKG_NAME )
1548 THEN
1549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1550 END IF;
1551
1552 -- Initialize message list if p_init_msg_list is set to TRUE.
1553 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1554 FND_MSG_PUB.initialize;
1555 END IF;
1556
1557 l_debug_info := 'Calling AP Void Pkg.Iby_Void_Check';
1558 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1559 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1560 END IF;
1561
1562 -- Initialize API return status to success
1563 x_return_status := FND_API.G_RET_STS_SUCCESS;
1564
1565 --------------------------------------------
1566 -- Step 1, Open , fetch selected check cursor
1567 --------------------------------------------
1568 l_debug_info := 'Open selected_checks Cursor';
1569
1570
1571 OPEN selected_checks;
1572
1573 LOOP
1574
1575 l_debug_info := 'Fetch selected_checks Cursor';
1576 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1577 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1578 END IF;
1579
1580
1581 FETCH selected_checks
1582 INTO l_selected_check_id,
1583 l_currency_code,
1584 l_payment_date,
1585 l_external_bank_account_id,
1586 l_ok_to_pay_flag,
1587 l_internal_bank_account_id,
1588 l_supplier_site_id;
1589
1590 EXIT WHEN selected_checks%NOTFOUND;
1591
1592
1593 -------------------------------------------------------
1594 -- Step 2, Call Bank_charge_get_info for each check
1595 -------------------------------------------------------
1596 l_debug_info := 'Call Bank_charge_get_info';
1597 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1598 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1599 END IF;
1600
1601
1602 IF (bank_charge_get_info(
1603 l_selected_check_id,
1604 l_external_bank_account_id,
1605 l_currency_code,
1606 l_min_account_unit, -- OUT
1607 l_precision, -- OUT
1608 l_bank_charge_bearer, -- OUT
1609 l_transferring_bank_branch_id, -- OUT
1610 l_receiving_bank_branch_id, -- OUT
1611 l_transfer_priority, -- OUT
1612 l_num_of_invoices, -- OUT, not currently used.
1613 current_calling_sequence,
1614 l_internal_bank_account_id,
1615 l_supplier_site_id) <> TRUE) THEN
1616 x_msg_data := 'Failed to derive transferring/receiving bank/branch info';
1617 l_debug_info := 'Failed to derive transferring/receiving bank/branch info';
1618 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1619 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1620 END IF;
1621 RAISE bank_charge_failure;
1622 END IF;
1623
1624
1625 -------------------------------------------------------
1626 -- Step 3, Call Bank_charge_get_amt_due for each check
1627 -------------------------------------------------------
1628 l_debug_info := 'Call Bank_charge_get_amt_due';
1629 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1630 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1631 END IF;
1632
1633
1634 IF (bank_charge_get_amt_due(l_selected_check_id,
1635 l_amt_due, -- OUT
1636 current_calling_sequence) <> TRUE) THEN
1637 x_msg_data := 'Failed to derive bank charge amount due';
1638 l_debug_info := 'Failed to derive bank charge amount due';
1639 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1640 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1641 END IF;
1642 RAISE bank_charge_failure;
1643 END IF;
1644
1645
1646 -----------------------------------------------------------------------------
1647 -- Step 4
1648 -- Call ap_bank_charge_pkg.check_bank_charge, it will return ok_to_pay_flag to
1649 -- 'N' and dont_pay_reason if bank charge information is insufficient.
1650 -- Since this is a procedure without return value, the exception handler will
1651 -- be different
1652 -----------------------------------------------------------------------------
1653 BEGIN
1654 -----------
1655 -- Step 4.1
1656 -----------
1657 l_debug_info := 'Check all the mandatory parameters for ap_bank_charge_pkg.check_bank_charge';
1658 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1662 IF (l_bank_charge_bearer IS NULL OR
1659 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1660 END IF;
1661
1663 l_transfer_priority IS NULL OR
1664 l_currency_code IS NULL OR
1665 l_amt_due IS NULL OR
1666 l_payment_date IS NULL) THEN
1667 x_msg_data := 'Can not call Check_Bank_Charge function because of mandatory parameter';
1668 l_debug_info := 'Can not call Check_Bank_Charge function because of mandatory parameter';
1669 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1670 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1671 END IF;
1672 RAISE bank_charge_failure;
1673
1674 END IF;
1675 ------------
1676 -- Step 4.2
1677 ------------
1678 l_debug_info := 'Call ap_bank_charge_pkg.check_bank_charge';
1679 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1680 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1681 END IF;
1682
1683 CHECK_BANK_CHARGE(
1684 l_bank_charge_bearer,
1685 l_transferring_bank_branch_id,
1686 l_receiving_bank_branch_id,
1687 l_transfer_priority,
1688 l_currency_code,
1689 l_amt_due,
1690 l_payment_date,
1691 l_bc_ok_to_pay_flag, -- OUT
1692 l_bc_dont_pay_reason_code); -- OUT
1693
1694
1695
1696
1697 EXCEPTION
1698 WHEN OTHERS THEN
1699
1700 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1701 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1702 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1703 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1704 ' l_bank_charge_bearer = ' ||l_bank_charge_bearer
1705 ||' l_transferring_bank_branch_id = '||to_char(l_transferring_bank_branch_id)
1706 ||' l_receiving_bank_branch_id = '||to_char(l_receiving_bank_branch_id)
1707 ||' l_transfer_priority = '||l_transfer_priority
1708 ||' l_currency_code = '||l_currency_code
1709 ||' l_amt_due = '||to_char(l_amt_due)
1710 ||' l_payment_date = '||to_char(l_payment_date) );
1711 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1712 FND_MSG_PUB.ADD;
1713 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1714 --APP_EXCEPTION.RAISE_EXCEPTION;
1715
1716 END;
1717
1718 -------------------------------------------------
1719 -- Step 4.3
1720 -- Update ap_selected_invoice_checks
1721 -------------------------------------------------
1722 if (NVL(l_bc_ok_to_pay_flag,'Y') = 'N') then
1723 l_debug_info := 'Update iby_hook_payments_t if ok_to_pay_flag is N';
1724 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1725 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1726 END IF;
1727
1728 UPDATE iby_hook_payments_t
1729 SET payment_amount = 0,
1730 dont_pay_flag = 'Y',
1731 dont_pay_reason_code = l_bc_dont_pay_reason_code
1732 WHERE payment_id = l_selected_check_id;
1733
1734
1735 l_ok_to_pay_flag := l_bc_ok_to_pay_flag;
1736 else
1737 l_ok_to_pay_flag := 'Y' ; -- Bug 6195497. Added else part.
1738 end if;
1739
1740
1741 --=======================================================================
1742 -- Don't update the tables if ok_to_pay_flag is not 'Y' or 'F', since the
1743 -- payment amount will be zero in this case. or bank_charge_bearer
1744 -- is 'I' (internal)
1745 --=======================================================================
1746 if (l_ok_to_pay_flag <> 'N' AND l_bank_charge_bearer <> 'I') then
1747
1748 -----------------------------------------------------------------------------
1749 -- Step 5
1750 -- Call ap_bank_charge_pkg.get_bank_charge, Since this is a procedure without
1751 -- return value, the exception handler will be different
1752 -----------------------------------------------------------------------------
1753 BEGIN
1754 -----------
1755 -- Step 5.1
1756 -----------
1757 l_debug_info := 'Check all the mandatory parameters for ap_bank_charge_pkg.get_bank_charge';
1758 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1759 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1760 END IF;
1761
1762
1763 IF (l_bank_charge_bearer IS NULL OR
1764 l_transfer_priority IS NULL OR
1765 l_currency_code IS NULL OR
1766 l_amt_due IS NULL OR
1767 l_payment_date IS NULL) THEN
1768
1769 RAISE bank_charge_failure;
1770
1771 END IF;
1772 -----------
1773 -- Step 5.2
1774 -----------
1775 l_debug_info := 'Call ap_bank_charge_pkg.get_bank_charge';
1776 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1777 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1778 END IF;
1779
1780
1781 GET_BANK_CHARGE(
1782 l_bank_charge_bearer,
1783 l_transferring_bank_branch_id,
1784 l_receiving_bank_branch_id,
1785 l_transfer_priority,
1786 l_currency_code,
1787 l_amt_due,
1791 l_calc_bank_charge_standard, -- OUT
1788 l_payment_date,
1789 l_bank_charge_standard, -- OUT
1790 l_bank_charge_negotiated, -- OUT
1792 l_calc_bank_charge_negotiated, -- OUT
1793 l_tolerance_limit); -- OUT, should always be 0.
1794
1795 IF l_tolerance_limit is NULL THEN
1796 Raise Bank_Charge_Failure;
1797 END IF;
1798
1799 EXCEPTION
1800 WHEN OTHERS THEN
1801 CLOSE selected_checks;
1802 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1803 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1804 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1805 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1806 ' l_bank_charge_bearer = ' ||l_bank_charge_bearer
1807 ||' l_transferring_bank_branch_id = '||to_char(l_transferring_bank_branch_id)
1808 ||' l_receiving_bank_branch_id = '||to_char(l_receiving_bank_branch_id)
1809 ||' l_transfer_priority = '||l_transfer_priority
1810 ||' l_currency_code = '||l_currency_code
1811 ||' l_amt_due = '||to_char(l_amt_due)
1812 ||' l_payment_date = '||to_char(l_payment_date) );
1813 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1814 FND_MSG_PUB.ADD;
1815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1816 --APP_EXCEPTION.RAISE_EXCEPTION;
1817
1818
1819 END;
1820
1821 -------------------------------------------------------------
1822 -- Step 6, Get the best deal for bank charge
1823 -------------------------------------------------------------
1824 l_debug_info := 'Get the best bank charge';
1825 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1826 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1827 END IF;
1828
1829
1830 IF (l_bank_charge_bearer = 'S') then
1831 SELECT greatest( nvl(l_bank_charge_standard,0),
1832 nvl(l_calc_bank_charge_standard,0))
1833 INTO l_best_bank_charge
1834 FROM sys.dual;
1835
1836 ELSIF (l_bank_charge_bearer = 'N') then
1837 SELECT greatest( nvl(l_bank_charge_negotiated,0),
1838 nvl(l_calc_bank_charge_negotiated,0))
1839 INTO l_best_bank_charge
1840 FROM sys.dual;
1841 END IF;
1842
1843
1844
1845
1846 ----------------------------------------------------------
1847 -- Step 7 , Update ap_selected_invoice_checks
1848 -- 1, Update discount_amount to the bank_charge_amount and
1849 -- 2, subtract the bank charge amount from the check amount
1850 ----------------------------------------------------------
1851 l_debug_info := 'Update iby_hook_payments_t';
1852 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1853 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1854 END IF;
1855
1856 UPDATE iby_hook_payments_t
1857 SET DISCOUNT_AMOUNT_TAKEN= l_best_bank_charge,
1858 payment_amount = l_amt_due - l_best_bank_charge
1859 WHERE payment_id = l_selected_check_id;
1860
1861
1862
1863 ---------------------------------------------------------
1864 -- Step 8 , Update ap_selected_invoices
1865 -- 1, Update discount_amount to proportion of the
1866 -- bank_charge_amount
1867 -- 2, Subtract the proportion of the bank_charge_amount from
1868 -- payment_amount and proposed_payment_amount
1869 ----------------------------------------------------------
1870 l_debug_info := 'Update iby_hook_docs_in_pmt_t';
1871 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1872 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1873 END IF;
1874
1875
1876 UPDATE iby_hook_docs_in_pmt_t
1877 SET PAYMENT_CURR_DISCOUNT_TAKEN = decode(l_amt_due, 0, 0,
1878 decode(l_min_account_unit,
1879 null, round(l_best_bank_charge *
1880 (document_amount/l_amt_due),
1881 l_precision),
1882 round(l_best_bank_charge *
1883 (document_amount/l_amt_due)/l_min_account_unit) *
1884 l_min_account_unit)
1885 ),
1886 document_amount = (document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN, 0)) -
1887 decode(l_amt_due, 0, 0,
1888 decode(l_min_account_unit,
1889 null, round(l_best_bank_charge *
1890 (document_amount/l_amt_due),
1891 l_precision),
1892 round(l_best_bank_charge *
1893 (document_amount/l_amt_due)/l_min_account_unit) *
1894 l_min_account_unit)
1895 )
1896 WHERE payment_id = l_selected_check_id
1897 AND nvl(dont_pay_flag, 'Y') = 'N';
1898
1899
1900
1901
1902
1903
1904
1905 ---------------------------------------------------------
1906 -- Step 9 , Calculate the rounding error
1907 -- The difference between sum of proposed_payment_amount
1911 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1908 -- for all invoices and the new check_amount
1909 ----------------------------------------------------------
1910 l_debug_info := 'Calculate the rounding error';
1912 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1913 END IF;
1914
1915
1916 SELECT l_amt_due - l_best_bank_charge - SUM(payment_amount)
1917 INTO l_rounding_error
1918 FROM iby_hook_payments_t
1919 WHERE payment_id = l_selected_check_id;
1920
1921
1922 ---------------------------------------------------------
1923 -- Step 10 , subtract the rounding error from the
1924 -- proposed_payment_amount of the first invoice
1925 ----------------------------------------------------------
1926 l_debug_info := 'Fix rounding error';
1927 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1928 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1929 END IF;
1930
1931 IF (l_rounding_error <> 0) then
1932
1933 -- Bug Fix: 1351943
1934 -- The Invoice with the maximum Discount Amount is selected
1935 -- to set-off the rounding error.
1936
1937 SELECT max(ABS(PAYMENT_CURR_DISCOUNT_TAKEN))
1938 INTO l_max_discount_amount
1939 FROM iby_hook_docs_in_pmt_t
1940 WHERE payment_id = l_selected_check_id
1941 AND ABS(document_amount) >= ABS(l_rounding_error);
1942
1943
1944
1945 IF (l_max_discount_amount >= l_rounding_error) THEN
1946
1947 UPDATE iby_hook_docs_in_pmt_t
1948 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_rounding_error,
1949 document_amount = document_amount + l_rounding_error
1950 WHERE payment_id = l_selected_check_id
1951 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
1952 AND ABS(document_amount) >= ABS(l_rounding_error)
1953 AND ROWNUM = 1;
1954
1955
1956
1957 ELSE
1958 /* Rounding Error is greater than the Maximum Discount Amount. The Rounding Error
1959 amount must be split across invoices. */
1960
1961 l_rem_rounding_error_amount := l_rounding_error;
1962 OPEN adjustment_for_rounding_error(l_selected_check_id,
1963 l_rem_rounding_error_amount);
1964
1965 /* Starting the loop which will process the rounding difference. */
1966
1967 WHILE (l_rem_rounding_error_amount > 0) LOOP
1968
1969 /* Selecting the maximum discount amount again since the correction for the Rounding Difference
1970 needs to be spread over multiple invoices. */
1971
1972 FETCH adjustment_for_rounding_error INTO l_max_discount_amount;
1973 EXIT WHEN adjustment_for_rounding_error%NOTFOUND;
1974
1975 /* If the maximum discount amount is less than the rounding error then subtract the maximum
1976 discount amount from the discount amount for that invoice. This will amount to making the discount
1977 amount equal to zero. */
1978
1979 IF (l_max_discount_amount <= l_rem_rounding_error_amount) THEN
1980
1981 UPDATE iby_hook_docs_in_pmt_t
1982 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_max_discount_amount,
1983 document_amount = document_amount + l_max_discount_amount
1984 WHERE payment_id = l_selected_check_id
1985 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
1986 AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
1987 AND ROWNUM = 1;
1988
1989
1990 /* The rounding difference has been adjusted by the Maximum Discount Amount. So now the rounding difference
1991 amount needs to be recalculated. */
1992
1993 l_rem_rounding_error_amount := l_rem_rounding_error_amount -
1994 l_max_discount_amount;
1995 ELSE
1996
1997 /* The Remaining Rounding Error is less than the maximum discount amount. So now use the remaining
1998 rounding error amount as the adjustment amount. */
1999
2000 UPDATE iby_hook_docs_in_pmt_t
2001 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN -
2002 l_rem_rounding_error_amount,
2003 document_amount = document_amount + l_rem_rounding_error_amount
2004 WHERE payment_id = l_selected_check_id
2005 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
2006 AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
2007 AND ROWNUM = 1;
2008
2009
2010 /* The entire rounding error amount has been adjusted in the above step. The loop can now be exited.
2011 The invoice discount amount is subtracted from the rounding error amount to exit the loop. */
2012
2013 l_rem_rounding_error_amount := l_rem_rounding_error_amount -
2014 l_max_discount_amount;
2015 END IF;
2016 END LOOP;
2017 CLOSE adjustment_for_rounding_error;
2018 END IF;
2019 /*1649310 End */
2020
2021 END IF;
2022
2023 end if; -- for (l_ok_to_pay_flag = 'Y')
2024
2025 END LOOP;
2026
2027 l_debug_info := 'Close selected_checks Cursor';
2028 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2029 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2030 END IF;
2031
2032
2033 CLOSE selected_checks;
2034
2035 EXCEPTION
2036
2037 WHEN FND_API.G_EXC_ERROR THEN
2038 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2039 x_return_status := FND_API.G_RET_STS_ERROR ;
2040 FND_MSG_PUB.Count_And_Get
2041 ( p_count => x_msg_count,
2042 p_data => x_msg_data
2043 );
2044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2045 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2046 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2047 FND_MSG_PUB.Count_And_Get
2048 ( p_count => x_msg_count,
2049 p_data => x_msg_data
2050 );
2051
2052 WHEN BANK_CHARGE_FAILURE THEN
2053 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2054 x_return_status := FND_API.G_RET_STS_ERROR ;
2055 IF x_msg_data is NOT NULL THEN
2056 x_msg_count := 1;
2057 ELSE
2058 FND_MSG_PUB.Count_And_Get
2059 ( p_count => x_msg_count,
2060 p_data => x_msg_data
2061 );
2062 END IF;
2063
2064 WHEN OTHERS then
2065
2066 CLOSE selected_checks;
2067
2068 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2070 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2071 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2072 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2073 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2074
2075 IF FND_MSG_PUB.Check_Msg_Level
2076 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2077 FND_MSG_PUB.Add_Exc_Msg
2078 ( G_PKG_NAME,
2079 l_api_name
2080 );
2081 END IF;
2082 FND_MSG_PUB.Count_And_Get
2083 ( p_count => x_msg_count,
2084 p_data => x_msg_data
2085 );
2086
2087 END ap_JapanBankChargeHook;
2088
2089
2090 END AP_BANK_CHARGE_PKG;