[Home] [Help]
PACKAGE BODY: APPS.AP_BANK_CHARGE_PKG
Source
1 PACKAGE BODY AP_BANK_CHARGE_PKG AS
2 /* $Header: apsudbcb.pls 120.13 2011/07/29 18:40:58 bgoyal 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,
391 v_end_date;
392 EXIT WHEN C_lines%NOTFOUND;
393
394 l_debug_info := 'Checking whether Amount Range Gap exists';
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');
529 APP_EXCEPTION.RAISE_EXCEPTION;
530 WHEN OTHERS THEN
531 IF (SQLCODE <> -20001) THEN
532 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
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;
641 temp_bank_charge_id NUMBER;
642 v_precision NUMBER;
643
644 v_transaction_amount NUMBER;
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
696 l_debug_info := 'Parameters for charge header -- P_transferring_bank_branch_id -- '||to_char(P_transferring_bank_branch_id)
697 ||' P_transferring_bank_name -- '||to_char(P_transferring_bank_name)||' P_receiving_bank_branch_id -- '||
698 to_char(P_receiving_bank_branch_id)||' P_receiving_bank_name -- '||to_char(P_receiving_bank_name)||
699 ' P_transfer_priority -- '||to_char(P_transfer_priority)||' P_currency_code -- '||to_char(P_currency_code);
700 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
701 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
702 END IF;
703
704 OPEN C_CHARGE_HEADER(
705 P_transferring_bank_branch_id,
706 P_transferring_bank_name,
707 P_receiving_bank_branch_id,
708 P_receiving_bank_name,
709 P_transfer_priority,
710 P_currency_code);
711 l_debug_info := 'Opened cursor for charge header';
712 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
713 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
714 END IF;
715
716 LOOP
717 FETCH C_CHARGE_HEADER INTO
718 v_bank_charge_id,
719 v_trans_bank_branch_id,
720 v_trans_bank_name,
721 v_trans_bank,
722 v_trans_branch,
723 v_recei_bank_branch_id,
724 v_recei_bank_name,
725 v_recei_bank,
726 v_recei_branch,
727 v_transfer_priority,
728 v_currency_code;
729 l_debug_info := 'fetch cursor for charge header -- 1';
730 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
731 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
732 END IF;
733
734 EXIT WHEN C_CHARGE_HEADER%NOTFOUND;
735
736 l_debug_info := 'fetch cursor for charge header -- 2';
737 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
738 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
739 END IF;
740
741 if((P_bank_charge_bearer is not null) and
742 (v_transfer_priority <>'AR')) or
743 ((P_bank_charge_bearer is null) and
744 (v_transfer_priority ='AR')) then
745 if (v_trans_bank_branch_id is not null) then
746 if (v_recei_bank_branch_id is not null) then
747 temp_priority := 1;
748 temp_bank_charge_id := v_bank_charge_id;
749 elsif(v_recei_bank = 'ONE') then
750 if(v_recei_branch = 'OTHER') then
751 temp_priority := 2;
752 temp_bank_charge_id := v_bank_charge_id;
753 elsif(v_recei_branch = 'ALL') then
754 temp_priority :=3;
755 temp_bank_charge_id :=v_bank_charge_id;
756 end if;
757 elsif(v_recei_bank = 'OTHER') then
758 temp_priority := 4;
759 temp_bank_charge_id := v_bank_charge_id;
760 elsif(v_recei_bank = 'ALL') then
761 temp_priority := 5;
762 temp_bank_charge_id := v_bank_charge_id;
763 end if;
764 elsif(v_trans_bank = 'ONE') then
765 if(v_recei_bank_branch_id is not null) then
766 temp_priority := 6;
767 temp_bank_charge_id := v_bank_charge_id;
768 elsif(v_recei_bank = 'ONE') then
769 temp_priority := 7;
770 temp_bank_charge_id := v_bank_charge_id;
771 elsif(v_recei_bank = 'OTHER') then
772 temp_priority := 8;
773 temp_bank_charge_id := v_bank_charge_id;
774 elsif(v_recei_bank = 'ALL')then
775 temp_priority := 9;
776 temp_bank_charge_id := v_bank_charge_id;
777 end if;
778 elsif(v_trans_bank = 'ALL') then
779 if(v_recei_bank_branch_id is not null) then
780 temp_priority := 10;
781 temp_bank_charge_id := v_bank_charge_id;
782 elsif(v_recei_bank = 'ONE') then
783 temp_priority := 11;
784 temp_bank_charge_id := v_bank_charge_id;
785 elsif(v_recei_bank = 'ALL') then
786 temp_priority := 12;
787 temp_bank_charge_id := v_bank_charge_id;
788 end if;
789 end if;
790
791 if (P_receiving_bank_branch_id is not null) or
792 ((temp_priority <> 2) and (temp_priority <> 4) and
793 (temp_priority <> 8)) then
794 if(v_transfer_priority ='AR') or
795 (v_transfer_priority = 'EXPRESS') then
796 temp_priority := 3*temp_priority -2;
797 elsif(v_transfer_priority = 'NORMAL') then
798 temp_priority := 3*temp_priority -1;
799 else
800 temp_priority := 3*temp_priority;
801 end if;
802
803 if (priority > temp_priority) then
804 priority := temp_priority;
805 P_bank_charge_id := temp_bank_charge_id;
806 end if;
807 end if;
808 end if;
809 END LOOP;
810 CLOSE C_CHARGE_HEADER;
811 l_debug_info := 'close cursor for charge header - p_bank_charge_id '||to_char(P_bank_charge_id);
812 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
813 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
814 END IF;
815
816 if (P_bank_charge_id <>0) then
817 /**************************************************************/
818 /* Change the Supplier to appropriate value */
819 /* NOTE: Bank_Charge_Bearer I: Internal */
820 /* S: Supplier/Standard */
821 /* N: Supplier/Negotiated */
822
823 l_debug_info := 'Bank Charge Id exits';
824 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
825 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
826 END IF;
827
828 if(P_transaction_amount is NULL) then
829 v_transaction_amount := 0;
830 else
831 v_transaction_amount := P_transaction_amount;
832 end if;
833 if(P_bank_charge_bearer = 'I') or
834 ((P_bank_charge_bearer is null)and
835 (P_transfer_priority is null)) then
836 l_debug_info := 'Bank Charge Bearer is I';
837 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
838 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
839 END IF;
840
841 SELECT bank_charge_standard,
842 bank_charge_negotiated,
843 tolerance_limit
844 INTO P_bank_charge_standard,
845 P_bank_charge_negotiated,
846 P_tolerance_limit
847 FROM ap_bank_charge_lines
848 WHERE bank_charge_id = P_bank_charge_id
849 AND trans_amount_from <= v_transaction_amount
850 AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
851 AND start_date <= P_transaction_date
852 AND nvl(end_date,
853 to_date('31-12-4712', 'DD-MM-YYYY')) >
854 P_transaction_date;
855 elsif(P_bank_charge_bearer = 'S') or
856 (P_bank_charge_bearer = 'N') then
857
858 l_debug_info := 'Bank Charge Bearer is not I';
859 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
860 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
861 END IF;
862
863 OPEN C_CHARGE_LINE(P_bank_charge_id,
864 P_transaction_date);
865
866 LOOP
867
868 FETCH C_CHARGE_LINE INTO v_trans_amount_from,
869 v_trans_amount_to,
870 v_bank_charge_standard,
871 v_bank_charge_negotiated,
872 v_tolerance_limit;
873 EXIT WHEN C_CHARGE_LINE%NOTFOUND;
874
875 /*
876 1542954 fbreslin: Bank charges were not getting calculated correctly in corner
877 cases.Commented below code as amount_bank_charge variable is
878 not used further in the procedure.
879 Also replace amount_bank_charge with v_transaction_amount to.
880
881 if(P_bank_charge_bearer = 'S') then
882 amount_bank_charge :=v_transaction_amount -
883 v_bank_charge_standard;
884 else
885 amount_bank_charge :=v_transaction_amount -
886 v_bank_charge_negotiated;
887 end if;
888 */
889
890 if(v_transaction_amount >= 0 ) then
891 if(v_transaction_amount >= v_trans_amount_from) then
892 if(v_transaction_amount < v_trans_amount_to) then
893 P_bank_charge_standard := v_bank_charge_standard;
894 P_bank_charge_negotiated := v_bank_charge_negotiated;
895 P_tolerance_limit := v_tolerance_limit;
896 EXIT;
897 else
898 P_bank_charge_standard := v_bank_charge_standard;
899 P_bank_charge_negotiated := v_bank_charge_negotiated;
900 OPEN C_precision(P_currency_code);
901 FETCH C_precision INTO v_precision;
902 CLOSE C_precision;
903 if(P_bank_charge_bearer = 'S') then
904 P_calc_bank_charge_standard :=
905 v_transaction_amount - v_trans_amount_to
906 + 1/power(10, v_precision);
907 else
908 P_calc_bank_charge_negotiated :=
909 v_transaction_amount - v_trans_amount_to
910 + 1/power(10, v_precision);
911 end if;
912 P_tolerance_limit := v_tolerance_limit;
913 EXIT;
914 end if;
915 end if;
916 end if;
917 END LOOP;
918 CLOSE C_CHARGE_LINE;
919 if (amount_bank_charge <0) then
920 l_debug_info := 'amount_bank_charge is negative - '||to_char(amount_bank_charge);
921 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
922 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
923 END IF;
924
925 RAISE NO_BANK_CHARGES;
926 end if;
927 else
928 l_debug_info := 'P_bank_charge_bearer is not valid - '||to_char(P_bank_charge_bearer);
929 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
930 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
931 END IF;
932
933 RAISE NO_BANK_CHARGES;
934 end if;
935 else
936 l_debug_info := 'P_bank_charge_id is not valid - '||to_char(P_bank_charge_id);
937 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
938 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
939 END IF;
940
941 NULL;
942 -- RAISE NO_BANK_CHARGES; bug8253986
943 end if;
944 else
945 l_debug_info := 'P_bank_charge_bearer is null - '||to_char(P_bank_charge_bearer)||
946 ' or P_transfer_priority is not null -- '||to_char(P_transfer_priority);
947 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
948 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
949 END IF;
950
951 RAISE NO_BANK_CHARGES;
952 end if;
953
954 -- B# 8340655
955 --P_tolerance_limit := 0 ;
956 IF (P_tolerance_limit is NULL) THEN
957 P_tolerance_limit := 0 ;
958 END IF ;
959
960 EXCEPTION
961 WHEN NO_BANK_CHARGES THEN
962 p_tolerance_limit := NULL;
963 FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_CHARGE_FOUND');
964 FND_MSG_PUB.ADD;
965 --APP_EXCEPTION.RAISE_EXCEPTION;
966 WHEN NO_DATA_FOUND THEN
967 FND_MESSAGE.SET_NAME('SQLAP', 'AP_NO_CHARGE_FOUND');
968 FND_MSG_PUB.ADD;
969 p_tolerance_limit := NULL;
970 --APP_EXCEPTION.RAISE_EXCEPTION;
971 WHEN OTHERS THEN
972 IF (SQLCODE <> -20001) THEN
973 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
974 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
975 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
976 END IF;
977 p_tolerance_limit := NULL;
978 FND_MSG_PUB.ADD;
979 --APP_EXCEPTION.RAISE_EXCEPTION;
980
981
982 END GET_BANK_CHARGE;
983
984 PROCEDURE CHECK_BANK_CHARGE(
985 P_bank_charge_bearer IN VARCHAR2,
989 P_currency_code IN VARCHAR2,
986 P_transferring_bank_branch_id IN NUMBER,
987 P_receiving_bank_branch_id IN NUMBER,
988 P_transfer_priority IN VARCHAR2,
990 P_transaction_amount IN NUMBER,
991 P_transaction_date IN DATE,
992 P_check_bc_flag OUT NOCOPY VARCHAR2,
993 P_do_not_pay_reason OUT NOCOPY VARCHAR2) IS
994
995
996 CURSOR C_CHARGE_HEADER(
997 X_transferring_bank_branch_id NUMBER,
998 X_transferring_bank_name VARCHAR2,
999 X_receiving_bank_branch_id NUMBER,
1000 X_receiving_bank_name VARCHAR2,
1001 X_transfer_priority VARCHAR2,
1002 X_currency_code VARCHAR2) IS
1003 SELECT bc.bank_charge_id,
1004 bc.transferring_bank_branch_id,
1005 bc.transferring_bank_name,
1006 bc.transferring_bank,
1007 bc.transferring_branch,
1008 bc.receiving_bank_branch_id,
1009 bc.receiving_bank_name,
1010 bc.receiving_bank,
1011 bc.receiving_branch,
1012 bc.transfer_priority,
1013 bc.currency_code
1014 FROM ap_bank_charges bc, ap_bank_charge_lines bcl
1015 WHERE ((bc.transferring_bank_branch_id = X_transferring_bank_branch_id
1016 -- bug2242764 added bank_name condition
1017 AND transferring_bank_name = X_transferring_bank_name)
1018 OR (bc.transferring_bank_name = X_transferring_bank_name
1019 AND bc.transferring_branch = 'ALL')
1020 OR (bc.transferring_bank = 'ALL'
1021 AND bc.transferring_branch = 'ALL'))
1022 AND ((bc.receiving_bank_branch_id = X_receiving_bank_branch_id
1023 -- bug2242764 added bank_name condition
1024 AND receiving_bank_name = X_receiving_bank_name)
1025 OR (bc.receiving_bank_name = X_receiving_bank_name
1026 AND bc.receiving_branch in ('ALL', 'OTHER'))
1027 OR (bc.receiving_bank in ('ALL', 'OTHER')
1028 AND bc.receiving_branch = 'ALL'))
1029 AND (bc.transfer_priority = X_transfer_priority
1030 OR bc.transfer_priority = 'AR'
1031 OR bc.transfer_priority = 'ANY')
1032 AND bc.currency_code = X_currency_code
1033 AND bc.bank_charge_id = bcl.bank_charge_id -- Bug 2177997
1034 AND bcl.start_date <= P_transaction_date
1035 AND nvl(bcl.end_date,
1036 to_date('31-12-4712', 'DD-MM-YYYY')) > P_transaction_date;
1037
1038 CURSOR C_get_bank_name(X_bank_branch_id NUMBER) IS
1039 SELECT bank_name
1040 FROM ce_bank_branches_v
1041 WHERE branch_party_id = X_bank_branch_id;
1042
1043 CURSOR C_precision(X_currency_code VARCHAR2) IS
1044 SELECT precision
1045 FROM fnd_currencies
1046 WHERE currency_code = X_currency_code;
1047
1048 CURSOR C_CHARGE_LINE(X_bank_charge_id NUMBER,
1049 X_transaction_date DATE) IS
1050 SELECT trans_amount_from,
1051 nvl(trans_amount_to, 99999999999999),
1052 bank_charge_standard,
1053 bank_charge_negotiated,
1054 tolerance_limit
1055 FROM ap_bank_charge_lines
1056 WHERE bank_charge_id = X_bank_charge_id
1057 AND (start_date <= X_transaction_date
1058 AND nvl(end_date, to_date('31-12-4712', 'DD-MM-YYYY')) >
1059 X_transaction_date)
1060 ORDER BY trans_amount_from desc;
1061
1062
1063 P_bank_charge_id NUMBER;
1064 P_transferring_bank_name ce_bank_branches_v.bank_name%TYPE;
1065 P_receiving_bank_name ce_bank_branches_v.bank_name%TYPE;
1066
1067 v_bank_charge_id NUMBER;
1068 v_trans_bank_branch_id NUMBER;
1069 v_trans_bank_name ce_bank_branches_v.bank_name%TYPE;
1070 v_trans_bank ce_bank_branches_v.bank_name%TYPE;
1071 v_trans_branch ce_bank_branches_v.bank_branch_name%TYPE;
1072 v_recei_bank_branch_id NUMBER;
1073 v_recei_bank_name ce_bank_branches_v.bank_name%TYPE;
1074 v_recei_bank ce_bank_branches_v.bank_name%TYPE;
1075 v_recei_branch ce_bank_branches_v.bank_branch_name%TYPE;
1076 v_transfer_priority VARCHAR2(30);
1077 v_currency_code VARCHAR2(15);
1078
1079 priority NUMBER;
1080 temp_priority NUMBER;
1081 temp_bank_charge_id NUMBER;
1082 v_precision NUMBER;
1083
1084 v_transaction_amount NUMBER;
1085 v_trans_amount_from NUMBER;
1086 v_trans_amount_to NUMBER;
1087 v_bank_charge_standard NUMBER;
1088 v_bank_charge_negotiated NUMBER;
1089 v_tolerance_limit NUMBER;
1090
1091 amount_bank_charge NUMBER;
1092 NO_BANK_CHARGES EXCEPTION;
1093
1094 l_debug_info Varchar2(2000);
1095 l_api_name CONSTANT VARCHAR2(100) := 'CHECK_BANK_CHARGE';
1096
1097
1098 BEGIN
1099
1100 l_debug_info := 'Check Bank Charge Begin';
1101 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1102 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1103 END IF;
1104
1105 v_precision := 0;
1106 P_bank_charge_id := 0;
1107 priority := 37;
1108 temp_priority :=37;
1109 temp_bank_charge_id := 0;
1110 P_check_bc_flag := 'N';
1111 P_do_not_pay_reason := '';
1112
1113 if (P_bank_charge_bearer is not null) or
1114 (P_transfer_priority is null) then
1115 l_debug_info := 'Opening Cursor for transferring bank info';
1116 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1117 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1118 END IF;
1119
1120 OPEN C_get_bank_name(P_transferring_bank_branch_id);
1121 FETCH C_get_bank_name INTO P_transferring_bank_name;
1122 CLOSE C_get_bank_name;
1123
1124 l_debug_info := 'Opening Cursor for receiving bank info';
1125 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1126 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1127 END IF;
1128
1129 OPEN C_get_bank_name(P_receiving_bank_branch_id);
1130 FETCH C_get_bank_name INTO P_receiving_bank_name;
1131 CLOSE C_get_bank_name;
1132
1133 l_debug_info := 'Opening Charge Header Cursor';
1134 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1135 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1136 END IF;
1137
1138 l_debug_info := 'Parameters for charge header 1 -- P_transferring_bank_branch_id -- '||to_char(P_transferring_bank_branch_id)
1139 ||' P_transferring_bank_name -- '||to_char(P_transferring_bank_name)||' P_receiving_bank_branch_id -- '||
1140 to_char(P_receiving_bank_branch_id)||' P_receiving_bank_name -- '||to_char(P_receiving_bank_name)||
1141 ' P_transfer_priority -- '||to_char(P_transfer_priority)||' P_currency_code -- '||to_char(P_currency_code);
1142 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1143 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1144 END IF;
1145
1146 OPEN C_CHARGE_HEADER(
1147 P_transferring_bank_branch_id,
1148 P_transferring_bank_name,
1149 P_receiving_bank_branch_id,
1150 P_receiving_bank_name,
1151 P_transfer_priority,
1152 P_currency_code);
1153
1154 l_debug_info := 'Opened charge header cursor';
1155 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1156 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1157 END IF;
1158
1159 LOOP
1160 FETCH C_CHARGE_HEADER INTO
1161 v_bank_charge_id,
1162 v_trans_bank_branch_id,
1163 v_trans_bank_name,
1164 v_trans_bank,
1165 v_trans_branch,
1166 v_recei_bank_branch_id,
1167 v_recei_bank_name,
1168 v_recei_bank,
1169 v_recei_branch,
1170 v_transfer_priority,
1171 v_currency_code;
1172
1173 l_debug_info := 'Fetched data from C_charge_header';
1174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1175 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1176 END IF;
1177
1178 EXIT WHEN C_CHARGE_HEADER%NOTFOUND;
1179
1180 if((P_bank_charge_bearer is not null) and
1181 (v_transfer_priority <>'AR')) or
1182 ((P_bank_charge_bearer is null) and
1183 (v_transfer_priority ='AR')) then
1184 if (v_trans_bank_branch_id is not null) then
1185 if (v_recei_bank_branch_id is not null) then
1186 temp_priority := 1;
1187 temp_bank_charge_id := v_bank_charge_id;
1188 elsif(v_recei_bank = 'ONE') then
1189 if(v_recei_branch = 'OTHER') then
1190 temp_priority := 2;
1191 temp_bank_charge_id := v_bank_charge_id;
1192 elsif(v_recei_branch = 'ALL') then
1193 temp_priority :=3;
1194 temp_bank_charge_id :=v_bank_charge_id;
1195 end if;
1196 elsif(v_recei_bank = 'OTHER') then
1197 temp_priority := 4;
1198 temp_bank_charge_id := v_bank_charge_id;
1199 elsif(v_recei_bank = 'ALL') then
1200 temp_priority := 5;
1201 temp_bank_charge_id := v_bank_charge_id;
1202 end if;
1203 elsif(v_trans_bank = 'ONE') then
1204 if(v_recei_bank_branch_id is not null) then
1205 temp_priority := 6;
1206 temp_bank_charge_id := v_bank_charge_id;
1207 elsif(v_recei_bank = 'ONE') then
1208 temp_priority := 7;
1209 temp_bank_charge_id := v_bank_charge_id;
1210 elsif(v_recei_bank = 'OTHER') then
1211 temp_priority := 8;
1212 temp_bank_charge_id := v_bank_charge_id;
1216 end if;
1213 elsif(v_recei_bank = 'ALL')then
1214 temp_priority := 9;
1215 temp_bank_charge_id := v_bank_charge_id;
1217 elsif(v_trans_bank = 'ALL') then
1218 if(v_recei_bank_branch_id is not null) then
1219 temp_priority := 10;
1220 temp_bank_charge_id := v_bank_charge_id;
1221 elsif(v_recei_bank = 'ONE') then
1222 temp_priority := 11;
1223 temp_bank_charge_id := v_bank_charge_id;
1224 elsif(v_recei_bank = 'ALL') then
1225 temp_priority := 12;
1226 temp_bank_charge_id := v_bank_charge_id;
1227 end if;
1228 end if;
1229
1230 if (P_receiving_bank_branch_id is not null) or
1231 ((temp_priority <> 2) and (temp_priority <> 4) and
1232 (temp_priority <> 8)) then
1233 if(v_transfer_priority ='AR') or
1234 (v_transfer_priority = 'EXPRESS') then
1235 temp_priority := 3*temp_priority -2;
1236 elsif(v_transfer_priority = 'NORMAL') then
1237 temp_priority := 3*temp_priority -1;
1238 else
1239 temp_priority := 3*temp_priority;
1240 end if;
1241
1242 if (priority > temp_priority) then
1243 priority := temp_priority;
1244 P_bank_charge_id := temp_bank_charge_id;
1245 end if;
1246 end if;
1247 end if;
1248 END LOOP;
1249 CLOSE C_CHARGE_HEADER;
1250
1251 l_debug_info := 'P_BANK_CHARGE_ID EXISTS -- '||to_char(P_bank_charge_id);
1252 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1253 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1254 END IF;
1255
1256 if (P_bank_charge_id <>0) then
1257 /**************************************************************/
1258 /* Change the Supplier to appropriate value */
1259 /* NOTE: Bank_Charge_Bearer I: Internal */
1260 /* S: Supplier/Standard */
1261 /* N: Supplier/Negotiated */
1262
1263 l_debug_info := 'Bank Charge Id exits';
1264 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1265 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1266 END IF;
1267
1268 if(P_transaction_amount is NULL) then
1269 v_transaction_amount := 0;
1270 else
1271 v_transaction_amount := P_transaction_amount;
1272 end if;
1273 if(P_bank_charge_bearer = 'I') or
1274 ((P_bank_charge_bearer is null)and
1275 (P_transfer_priority is null)) then
1276 l_debug_info := 'Bank Charge Bearer is I';
1277 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1278 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1279 END IF;
1280
1281 SELECT bank_charge_standard,
1282 bank_charge_negotiated,
1283 tolerance_limit
1284 INTO v_bank_charge_standard,
1285 v_bank_charge_negotiated,
1286 v_tolerance_limit
1287 FROM ap_bank_charge_lines
1288 WHERE bank_charge_id = P_bank_charge_id
1289 AND trans_amount_from <= v_transaction_amount
1290 AND nvl(trans_amount_to, 99999999999999) > v_transaction_amount
1291 AND start_date <= P_transaction_date
1292 AND nvl(end_date,
1293 to_date('31-12-4712', 'DD-MM-YYYY')) >
1294 P_transaction_date;
1295 P_check_bc_flag := 'Y';
1296 elsif(P_bank_charge_bearer = 'S') or
1297 (P_bank_charge_bearer = 'N') then
1298
1299 l_debug_info := 'Bank Charge Bearer is not I';
1300 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1301 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1302 END IF;
1303
1304
1305 OPEN C_CHARGE_LINE(P_bank_charge_id,
1306 P_transaction_date);
1307 LOOP
1308 FETCH C_CHARGE_LINE INTO v_trans_amount_from,
1309 v_trans_amount_to,
1310 v_bank_charge_standard,
1311 v_bank_charge_negotiated,
1312 v_tolerance_limit;
1313 EXIT WHEN C_CHARGE_LINE%NOTFOUND;
1314 if(P_bank_charge_bearer = 'S') then
1315 amount_bank_charge :=v_transaction_amount -
1316 v_bank_charge_standard;
1317 else
1318 amount_bank_charge :=v_transaction_amount -
1319 v_bank_charge_negotiated;
1320 end if;
1321 if(amount_bank_charge >= 0 ) then
1322 if(amount_bank_charge >= v_trans_amount_from) then
1323 P_check_bc_flag := 'Y';
1324 EXIT;
1325 end if;
1326 end if;
1327 END LOOP;
1328
1329 -- for BUG 1714850
1330 if C_CHARGE_LINE%ROWCOUNT = 0
1331 then
1332 P_check_bc_flag := 'N';
1333 P_do_not_pay_reason := 'NO BANK CHARGE';
1334 end if;
1335
1336 CLOSE C_CHARGE_LINE;
1337 if (amount_bank_charge <0) then
1338 P_check_bc_flag := 'N';
1339 P_do_not_pay_reason := 'BC GREATER THAN AMOUNT';
1340 end if;
1341 else
1342 l_debug_info := 'P_bank_charge_bearer is not valid - '||to_char(P_bank_charge_bearer);
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
1348 RAISE NO_BANK_CHARGES;
1349 end if;
1350 else
1351
1352 l_debug_info := 'P_bank_charge_id is not valid - '||to_char(P_bank_charge_id);
1353 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1354 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1355 END IF;
1356
1357 P_check_bc_flag := 'Y';
1358 -- RAISE NO_BANK_CHARGES; bug8253986
1359 end if;
1360 else
1361
1362 l_debug_info := 'P_bank_charge_bearer is null - '||to_char(P_bank_charge_bearer)||
1363 ' or P_transfer_priority is not null -- '||to_char(P_transfer_priority);
1364 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1365 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1366 END IF;
1367
1368 RAISE NO_BANK_CHARGES;
1369 end if;
1370 EXCEPTION
1371 WHEN NO_BANK_CHARGES THEN
1372 P_check_bc_flag := 'N';
1373 P_do_not_pay_reason := 'NO BANK CHARGE';
1374 WHEN NO_DATA_FOUND THEN
1375 P_check_bc_flag := 'N';
1376 P_do_not_pay_reason := 'NO BANK CHARGE';
1377 WHEN OTHERS THEN
1378 IF (SQLCODE <> -20001) THEN
1379 FND_MESSAGE.set_NAME('SQLAP','AP_DEBUG');
1380 FND_MESSAGE.set_TOKEN('ERROR',SQLERRM);
1381 FND_MESSAGE.set_TOKEN('DEBUG_INFO',l_debug_info);
1382 END IF;
1383 FND_MSG_PUB.ADD;
1384
1385 END CHECK_BANK_CHARGE;
1386
1387
1388 FUNCTION Bank_charge_get_info(
1389 p_selected_check_id IN NUMBER,
1390 p_external_bank_account_id IN NUMBER,
1391 p_currency_code IN VARCHAR2,
1392 p_minimum_accountable_unit OUT nocopy NUMBER,
1393 p_precision OUT nocopy NUMBER,
1394 p_bank_charge_bearer OUT nocopy VARCHAR2,
1395 p_transferring_bank_branch_id OUT nocopy NUMBER,
1396 p_receiving_bank_branch_id OUT nocopy NUMBER,
1397 p_transfer_priority OUT nocopy VARCHAR2,
1398 p_num_of_invoices OUT nocopy NUMBER,
1399 p_calling_sequence IN VARCHAR2,
1400 p_internal_bank_account_id IN NUMBER,
1401 p_supplier_site_id IN NUMBER) RETURN BOOLEAN IS
1402
1403 current_calling_sequence VARCHAR2(2000);
1404 l_debug_info Varchar2(2000);
1405 l_api_name CONSTANT VARCHAR2(100) := 'BANK_CHARGE_GET_INFO';
1406
1407
1408 BEGIN
1409
1410 current_calling_sequence := 'bank_charge_get_info<-'||P_calling_sequence;
1414
1411 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1412 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,current_calling_sequence);
1413 END IF;
1415 l_debug_info := 'Get bank charge bearer from po vendor sites';
1416 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1417 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1418 END IF;
1419
1420 -- Bug7014739. Added nvl.
1421 IF p_supplier_site_id IS NOT NULL THEN
1422 SELECT nvl(PVS.bank_charge_bearer, 'I')
1423 INTO p_bank_charge_bearer
1424 FROM iby_hook_payments_t iby,
1425 ap_supplier_sites_all PVS
1426 WHERE iby.payment_id = p_selected_check_id
1427 AND iby.supplier_site_id = PVS.vendor_site_id;
1428 END IF;
1429
1430 l_debug_info := 'Get p_transferring_bank_branch_id';
1431 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1432 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1433 END IF;
1434
1435 SELECT ABA.bank_branch_id
1436 INTO p_transferring_bank_branch_id
1437 FROM ce_bank_accounts ABA
1438 WHERE aba.bank_account_id = p_internal_bank_account_id;
1439
1440 l_debug_info := 'Get p_receiving_bank_branch_id';
1441 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1442 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1443 END IF;
1444
1445 if p_external_bank_account_id is not null then
1446
1447 SELECT ieb.branch_id
1448 INTO p_receiving_bank_branch_id
1449 FROM iby_ext_bank_accounts ieb
1450 WHERE ieb.ext_bank_account_id = p_external_bank_account_id;
1451
1452 end if;
1453
1454 l_debug_info := 'Get transfer_priority and currency code';
1455 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1456 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1457 END IF;
1458
1459 /* Commented for bug#12769134 Start
1460 SELECT AISC.transfer_priority
1461 INTO p_transfer_priority
1462 FROM ap_inv_selection_criteria_ALL AISC,
1463 iby_hook_docs_in_pmt_t IBY
1464 WHERE IBY.CALLING_APP_DOC_UNIQUE_REF1 = AISC.CHECKRUN_ID
1465 and rownum=1;
1466 Commented for bug#12769134 End */
1467
1468 /* Added for bug#12769134 Start */
1469 SELECT /*+ FIRST_ROWS */ AISC.TRANSFER_PRIORITY
1470 INTO p_transfer_priority
1471 FROM AP_INV_SELECTION_CRITERIA_ALL AISC
1472 WHERE EXISTS
1473 ( SELECT 1
1474 FROM IBY_HOOK_DOCS_IN_PMT_T IBY
1475 WHERE IBY.CALLING_APP_DOC_UNIQUE_REF1 = AISC.CHECKRUN_ID
1476 );
1477 /* Added for bug#12769134 End */
1478
1479 l_debug_info := 'Get number of invoices for this check';
1480 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1481 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1482 END IF;
1483
1484 SELECT count(*)
1485 INTO p_num_of_invoices
1486 FROM iby_hook_payments_t
1487 WHERE payment_id = p_selected_check_id;
1488
1489 l_debug_info := 'Get min_account_unit and precision for currency';
1490 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1491 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1492 END IF;
1493
1494 SELECT minimum_accountable_unit,
1495 nvl(precision, 0)
1496 INTO p_minimum_accountable_unit,
1497 p_precision
1498 FROM fnd_currencies_vl
1499 WHERE currency_code = p_currency_code;
1500
1501 RETURN (TRUE);
1502
1503 EXCEPTION
1504 WHEN OTHERS then
1505 IF (SQLCODE < 0 ) then
1506 null; --rlandows SRW.MESSAGE('999',SQLERRM);
1507 END IF;
1508
1509 RETURN (FALSE);
1510
1511 END bank_charge_get_info;
1512
1513
1514
1515 FUNCTION Bank_charge_get_amt_due(
1516 p_selected_check_id IN NUMBER,
1517 p_amount_due OUT nocopy NUMBER,
1518 p_calling_sequence IN VARCHAR2) RETURN BOOLEAN IS
1519
1520 debug_info VARCHAR2(200);
1521 current_calling_sequence VARCHAR2(2000);
1522
1523 BEGIN
1524
1525 current_calling_sequence := 'bank_charge_get_amt_due<-'||P_calling_sequence;
1526
1527
1528 debug_info := 'Get p_amount_due';
1529
1530 SELECT sum(decode(dont_pay_flag, 'Y', 0,
1531 document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN,0)))
1532 INTO p_amount_due
1533 FROM iby_hook_docs_in_pmt_t
1534 WHERE payment_id= p_selected_check_id;
1535
1536
1537 RETURN (TRUE);
1538
1539 EXCEPTION
1540
1541 WHEN OTHERS THEN
1542
1543 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1544 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1545 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1546 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1547 ' p_selected_check_id = '||to_char(p_selected_check_id));
1548 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
1549 --APP_EXCEPTION.RAISE_EXCEPTION;
1550
1551 RETURN (FALSE);
1552
1553 END bank_charge_get_amt_due;
1554
1555
1556
1557 PROCEDURE ap_JapanBankChargeHook(
1561 x_return_status OUT nocopy VARCHAR2,
1558 p_api_version IN NUMBER,
1559 p_init_msg_list IN VARCHAR2,
1560 p_commit IN VARCHAR2,
1562 x_msg_count OUT nocopy NUMBER,
1563 x_msg_data OUT nocopy VARCHAR2)
1564 is
1565
1566
1567 CURSOR selected_checks IS
1568 SELECT iby.payment_id,
1569 iby.payment_currency_code,
1570 iby.payment_date,
1571 iby.external_bank_account_id,
1572 iby.dont_pay_flag,
1573 iby.internal_bank_account_id,
1574 iby.supplier_site_id
1575 FROM iby_hook_payments_t iby,
1576 ap_system_parameters_all asp --5007989
1577 WHERE dont_pay_flag <> 'Y'
1578 AND nvl(dont_pay_reason_code,'dummy') <> 'OVERFLOW'
1579 AND asp.org_id = iby.org_id
1580 AND nvl(asp.use_bank_charge_flag,'N') = 'Y'
1581 ORDER BY payment_id;
1582
1583
1584 CURSOR adjustment_for_rounding_error (c_selected_check_id NUMBER,
1585 c_rounding_error NUMBER) IS
1586 SELECT PAYMENT_CURR_DISCOUNT_TAKEN
1587 FROM iby_hook_docs_in_pmt_t
1588 WHERE payment_id = c_selected_check_id
1589 AND ABS(document_amount) >= ABS(c_rounding_error)
1590 ORDER BY PAYMENT_CURR_DISCOUNT_TAKEN desc;
1591
1592
1593 l_selected_check_id NUMBER;
1594 l_currency_code VARCHAR2(15);
1595 l_payment_date DATE;
1596 l_external_bank_account_id NUMBER;
1597 l_bank_charge_bearer VARCHAR2(1);
1598 l_transferring_bank_branch_id NUMBER;
1599 l_ok_to_pay_flag VARCHAR2(1);
1600 l_bc_ok_to_pay_flag VARCHAR2(1);
1601 l_bc_dont_pay_reason_code VARCHAR2(25);
1602 l_receiving_bank_branch_id NUMBER;
1603 l_transfer_priority VARCHAR2(25);
1604 l_bank_charge_standard NUMBER;
1605 l_bank_charge_negotiated NUMBER;
1606 l_calc_bank_charge_standard NUMBER;
1607 l_calc_bank_charge_negotiated NUMBER;
1608 l_tolerance_limit NUMBER;
1609 l_best_bank_charge NUMBER;
1610 l_num_of_invoices NUMBER;
1611 l_prorate_bank_charge NUMBER;
1612 l_rounding_error NUMBER;
1613 l_min_account_unit NUMBER;
1614 l_precision NUMBER;
1615 l_amt_due NUMBER;
1616 l_payment_method VARCHAR2(25);
1617 l_max_discount_amount NUMBER;
1618 l_rem_rounding_error_amount NUMBER; /*1649310 */
1619 l_supplier_site_id NUMBER;
1620
1621 bank_charge_failure EXCEPTION;
1622 l_debug_info VARCHAR2(200);
1623 current_calling_sequence VARCHAR2(2000);
1624
1625 l_internal_bank_account_id number;
1626 l_api_name CONSTANT VARCHAR2(30) := 'ap_JapanBankChargeHook';
1627 l_api_version CONSTANT NUMBER := 1.0;
1628
1629
1630 BEGIN
1631 -- Update the calling sequence
1632 --
1633 current_calling_sequence := 'AP_BANK_CHARGE_PKG.AP_JAPANBANKCHARGEHOOK';
1634
1635 -------------------------------------------------------------------------
1636 -- Step 0, Return true and do nothing if use_bank_charge_flag is not 'Y'
1637 -------------------------------------------------------------------------
1638
1639 l_debug_info := 'Creating Savepoint';
1640 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1641 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1642 END IF;
1643
1644 -- Standard Start of API savepoint
1645 SAVEPOINT AP_JAPANBANKCHARGEHOOK;
1646
1647 l_debug_info := 'Checking API Compatibility';
1648 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1649 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1650 END IF;
1651
1652 -- Standard call to check for call compatibility.
1653 IF NOT FND_API.Compatible_API_Call (l_api_version,
1654 p_api_version,
1655 l_api_name,
1656 G_PKG_NAME )
1657 THEN
1658 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1659 END IF;
1660
1661 -- Initialize message list if p_init_msg_list is set to TRUE.
1662 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1663 FND_MSG_PUB.initialize;
1664 END IF;
1665
1666 l_debug_info := 'Calling AP Void Pkg.Iby_Void_Check';
1667 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1668 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1669 END IF;
1670
1671 -- Initialize API return status to success
1672 x_return_status := FND_API.G_RET_STS_SUCCESS;
1673
1674 --------------------------------------------
1675 -- Step 1, Open , fetch selected check cursor
1676 --------------------------------------------
1677 l_debug_info := 'Open selected_checks Cursor';
1678
1679
1680 OPEN selected_checks;
1681
1682 LOOP
1683
1684 l_debug_info := 'Fetch selected_checks Cursor';
1685 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1686 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1687 END IF;
1688
1689
1690 FETCH selected_checks
1691 INTO l_selected_check_id,
1692 l_currency_code,
1693 l_payment_date,
1694 l_external_bank_account_id,
1695 l_ok_to_pay_flag,
1696 l_internal_bank_account_id,
1697 l_supplier_site_id;
1698
1699 EXIT WHEN selected_checks%NOTFOUND;
1700
1701 -- Bug 10180027. Bank charge need not be calculated for
1702 -- invoices which does not have a supplier site setup.
1703 -- E.g. customer refunds, loans etc. Continue with the
1704 -- next iteration if supplier site id is null.
1705
1706 IF (l_supplier_site_id is not null) THEN -- l_supplier_site_id
1707
1708 -------------------------------------------------------
1709 -- Step 2, Call Bank_charge_get_info for each check
1710 -------------------------------------------------------
1711 l_debug_info := 'Call Bank_charge_get_info';
1712 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1713 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1714 END IF;
1715
1716
1717 IF (bank_charge_get_info(
1718 l_selected_check_id,
1719 l_external_bank_account_id,
1720 l_currency_code,
1721 l_min_account_unit, -- OUT
1722 l_precision, -- OUT
1723 l_bank_charge_bearer, -- OUT
1724 l_transferring_bank_branch_id, -- OUT
1725 l_receiving_bank_branch_id, -- OUT
1726 l_transfer_priority, -- OUT
1727 l_num_of_invoices, -- OUT, not currently used.
1728 current_calling_sequence,
1729 l_internal_bank_account_id,
1730 l_supplier_site_id) <> TRUE) THEN
1731 x_msg_data := 'Failed to derive transferring/receiving bank/branch info';
1732 l_debug_info := 'Failed to derive transferring/receiving bank/branch info';
1733 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1734 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1735 END IF;
1736 RAISE bank_charge_failure;
1737 END IF;
1738
1739
1740 -------------------------------------------------------
1741 -- Step 3, Call Bank_charge_get_amt_due for each check
1742 -------------------------------------------------------
1743 l_debug_info := 'Call Bank_charge_get_amt_due';
1744 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1745 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1746 END IF;
1747
1748
1749 IF (bank_charge_get_amt_due(l_selected_check_id,
1750 l_amt_due, -- OUT
1751 current_calling_sequence) <> TRUE) THEN
1752 x_msg_data := 'Failed to derive bank charge amount due';
1753 l_debug_info := 'Failed to derive bank charge amount due';
1754 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1755 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1756 END IF;
1757 RAISE bank_charge_failure;
1758 END IF;
1759
1760
1764 -- 'N' and dont_pay_reason if bank charge information is insufficient.
1761 -----------------------------------------------------------------------------
1762 -- Step 4
1763 -- Call ap_bank_charge_pkg.check_bank_charge, it will return ok_to_pay_flag to
1765 -- Since this is a procedure without return value, the exception handler will
1766 -- be different
1767 -----------------------------------------------------------------------------
1768 BEGIN
1769 -----------
1770 -- Step 4.1
1771 -----------
1772 l_debug_info := 'Check all the mandatory parameters for ap_bank_charge_pkg.check_bank_charge';
1773 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1774 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1775 END IF;
1776
1777 IF (l_bank_charge_bearer IS NULL OR
1778 l_transfer_priority IS NULL OR
1779 l_currency_code IS NULL OR
1780 l_amt_due IS NULL OR
1781 l_payment_date IS NULL) THEN
1782 x_msg_data := 'Can not call Check_Bank_Charge function because of mandatory parameter';
1783 l_debug_info := 'Can not call Check_Bank_Charge function because of mandatory parameter';
1784 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1785 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1786 END IF;
1787 RAISE bank_charge_failure;
1788
1789 END IF;
1790 ------------
1791 -- Step 4.2
1792 ------------
1793 l_debug_info := 'Call ap_bank_charge_pkg.check_bank_charge';
1794 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1795 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1796 END IF;
1797
1798 CHECK_BANK_CHARGE(
1799 l_bank_charge_bearer,
1800 l_transferring_bank_branch_id,
1801 l_receiving_bank_branch_id,
1802 l_transfer_priority,
1803 l_currency_code,
1804 l_amt_due,
1805 l_payment_date,
1806 l_bc_ok_to_pay_flag, -- OUT
1807 l_bc_dont_pay_reason_code); -- OUT
1808
1809
1810
1811
1812 EXCEPTION
1813 WHEN OTHERS THEN
1814
1815 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1816 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1817 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1818 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1819 ' l_bank_charge_bearer = ' ||l_bank_charge_bearer
1820 ||' l_transferring_bank_branch_id = '||to_char(l_transferring_bank_branch_id)
1821 ||' l_receiving_bank_branch_id = '||to_char(l_receiving_bank_branch_id)
1822 ||' l_transfer_priority = '||l_transfer_priority
1823 ||' l_currency_code = '||l_currency_code
1824 ||' l_amt_due = '||to_char(l_amt_due)
1825 ||' l_payment_date = '||to_char(l_payment_date) );
1826 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1827 FND_MSG_PUB.ADD;
1828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1829 --APP_EXCEPTION.RAISE_EXCEPTION;
1830
1831 END;
1832
1833 -------------------------------------------------
1834 -- Step 4.3
1835 -- Update ap_selected_invoice_checks
1836 -------------------------------------------------
1837 if (NVL(l_bc_ok_to_pay_flag,'Y') = 'N') then
1838 l_debug_info := 'Update iby_hook_payments_t if ok_to_pay_flag is N';
1839 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1840 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1841 END IF;
1842
1843 UPDATE iby_hook_payments_t
1844 SET payment_amount = 0,
1845 dont_pay_flag = 'Y',
1846 dont_pay_reason_code = l_bc_dont_pay_reason_code
1847 WHERE payment_id = l_selected_check_id;
1848
1849
1850 l_ok_to_pay_flag := l_bc_ok_to_pay_flag;
1851 else
1852 l_ok_to_pay_flag := 'Y' ; -- Bug 6195497. Added else part.
1853 end if;
1854
1855
1856 --=======================================================================
1857 -- Don't update the tables if ok_to_pay_flag is not 'Y' or 'F', since the
1858 -- payment amount will be zero in this case. or bank_charge_bearer
1859 -- is 'I' (internal)
1860 --=======================================================================
1861 if (l_ok_to_pay_flag <> 'N' AND l_bank_charge_bearer <> 'I') then
1862
1863 -----------------------------------------------------------------------------
1864 -- Step 5
1865 -- Call ap_bank_charge_pkg.get_bank_charge, Since this is a procedure without
1866 -- return value, the exception handler will be different
1867 -----------------------------------------------------------------------------
1868 BEGIN
1869 -----------
1870 -- Step 5.1
1871 -----------
1872 l_debug_info := 'Check all the mandatory parameters for ap_bank_charge_pkg.get_bank_charge';
1873 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1874 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1875 END IF;
1876
1877
1878 IF (l_bank_charge_bearer IS NULL OR
1879 l_transfer_priority IS NULL OR
1880 l_currency_code IS NULL OR
1881 l_amt_due IS NULL OR
1882 l_payment_date IS NULL) THEN
1886 END IF;
1883
1884 RAISE bank_charge_failure;
1885
1887 -----------
1888 -- Step 5.2
1889 -----------
1890 l_debug_info := 'Call ap_bank_charge_pkg.get_bank_charge';
1891 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1892 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1893 END IF;
1894
1895
1896 GET_BANK_CHARGE(
1897 l_bank_charge_bearer,
1898 l_transferring_bank_branch_id,
1899 l_receiving_bank_branch_id,
1900 l_transfer_priority,
1901 l_currency_code,
1902 l_amt_due,
1903 l_payment_date,
1904 l_bank_charge_standard, -- OUT
1905 l_bank_charge_negotiated, -- OUT
1906 l_calc_bank_charge_standard, -- OUT
1907 l_calc_bank_charge_negotiated, -- OUT
1908 l_tolerance_limit); -- OUT, should always be 0.
1909
1910 IF l_tolerance_limit is NULL THEN
1911 Raise Bank_Charge_Failure;
1912 END IF;
1913
1914 EXCEPTION
1915 WHEN OTHERS THEN
1916 CLOSE selected_checks;
1917 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
1918 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
1919 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
1920 FND_MESSAGE.SET_TOKEN('PARAMETERS',
1921 ' l_bank_charge_bearer = ' ||l_bank_charge_bearer
1922 ||' l_transferring_bank_branch_id = '||to_char(l_transferring_bank_branch_id)
1923 ||' l_receiving_bank_branch_id = '||to_char(l_receiving_bank_branch_id)
1924 ||' l_transfer_priority = '||l_transfer_priority
1925 ||' l_currency_code = '||l_currency_code
1926 ||' l_amt_due = '||to_char(l_amt_due)
1927 ||' l_payment_date = '||to_char(l_payment_date) );
1928 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
1929 FND_MSG_PUB.ADD;
1930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1931 --APP_EXCEPTION.RAISE_EXCEPTION;
1932
1933
1934 END;
1935
1936 -------------------------------------------------------------
1937 -- Step 6, Get the best deal for bank charge
1938 -------------------------------------------------------------
1939 l_debug_info := 'Get the best bank charge';
1940 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1941 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1942 END IF;
1943
1944
1945 IF (l_bank_charge_bearer = 'S') then
1946 SELECT greatest( nvl(l_bank_charge_standard,0),
1947 nvl(l_calc_bank_charge_standard,0))
1948 INTO l_best_bank_charge
1949 FROM sys.dual;
1950
1951 ELSIF (l_bank_charge_bearer = 'N') then
1952 SELECT greatest( nvl(l_bank_charge_negotiated,0),
1953 nvl(l_calc_bank_charge_negotiated,0))
1954 INTO l_best_bank_charge
1955 FROM sys.dual;
1956 END IF;
1957
1958
1959
1960
1961 ----------------------------------------------------------
1962 -- Step 7 , Update ap_selected_invoice_checks
1963 -- 1, Update discount_amount to the bank_charge_amount and
1964 -- 2, subtract the bank charge amount from the check amount
1965 ----------------------------------------------------------
1966 l_debug_info := 'Update iby_hook_payments_t';
1967 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1968 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1969 END IF;
1970
1971 UPDATE iby_hook_payments_t
1972 SET DISCOUNT_AMOUNT_TAKEN= l_best_bank_charge,
1973 payment_amount = l_amt_due - l_best_bank_charge
1974 WHERE payment_id = l_selected_check_id;
1975
1976
1977
1978 ---------------------------------------------------------
1979 -- Step 8 , Update ap_selected_invoices
1980 -- 1, Update discount_amount to proportion of the
1981 -- bank_charge_amount
1982 -- 2, Subtract the proportion of the bank_charge_amount from
1983 -- payment_amount and proposed_payment_amount
1984 ----------------------------------------------------------
1985 l_debug_info := 'Update iby_hook_docs_in_pmt_t';
1986 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1987 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
1988 END IF;
1989
1990
1991 UPDATE iby_hook_docs_in_pmt_t
1992 SET PAYMENT_CURR_DISCOUNT_TAKEN = decode(l_amt_due, 0, 0,
1993 decode(l_min_account_unit,
1994 null, round(l_best_bank_charge *
1995 (document_amount/l_amt_due),
1996 l_precision),
1997 round(l_best_bank_charge *
1998 (document_amount/l_amt_due)/l_min_account_unit) *
1999 l_min_account_unit)
2000 ),
2001 document_amount = (document_amount + nvl(PAYMENT_CURR_DISCOUNT_TAKEN, 0)) -
2002 decode(l_amt_due, 0, 0,
2003 decode(l_min_account_unit,
2004 null, round(l_best_bank_charge *
2005 (document_amount/l_amt_due),
2006 l_precision),
2007 round(l_best_bank_charge *
2008 (document_amount/l_amt_due)/l_min_account_unit) *
2009 l_min_account_unit)
2010 )
2011 WHERE payment_id = l_selected_check_id
2012 AND nvl(dont_pay_flag, 'Y') = 'N';
2013
2014
2015
2016
2017
2018
2022 -- The difference between sum of proposed_payment_amount
2019
2020 ---------------------------------------------------------
2021 -- Step 9 , Calculate the rounding error
2023 -- for all invoices and the new check_amount
2024 ----------------------------------------------------------
2025 l_debug_info := 'Calculate the rounding error';
2026 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2027 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2028 END IF;
2029
2030
2031 /*Bug7394744 Changed query to make sure rounding amount is calculated properly*/
2032 SELECT l_amt_due - l_best_bank_charge - SUM(document_amount)
2033 INTO l_rounding_error
2034 FROM iby_hook_docs_in_pmt_t
2035 WHERE payment_id = l_selected_check_id;
2036
2037
2038 ---------------------------------------------------------
2039 -- Step 10 , subtract the rounding error from the
2040 -- proposed_payment_amount of the first invoice
2041 ----------------------------------------------------------
2042 l_debug_info := 'Fix rounding error';
2043 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2044 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2045 END IF;
2046
2047 IF (l_rounding_error <> 0) then
2048
2049 -- Bug Fix: 1351943
2050 -- The Invoice with the maximum Discount Amount is selected
2051 -- to set-off the rounding error.
2052
2053 SELECT max(ABS(PAYMENT_CURR_DISCOUNT_TAKEN))
2054 INTO l_max_discount_amount
2055 FROM iby_hook_docs_in_pmt_t
2056 WHERE payment_id = l_selected_check_id
2057 AND ABS(document_amount) >= ABS(l_rounding_error);
2058
2059
2060
2061 IF (l_max_discount_amount >= l_rounding_error) THEN
2062
2063 UPDATE iby_hook_docs_in_pmt_t
2064 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_rounding_error,
2065 document_amount = document_amount + l_rounding_error
2066 WHERE payment_id = l_selected_check_id
2067 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
2068 AND ABS(document_amount) >= ABS(l_rounding_error)
2069 AND ROWNUM = 1;
2070
2071
2072
2073 ELSE
2074 /* Rounding Error is greater than the Maximum Discount Amount. The Rounding Error
2075 amount must be split across invoices. */
2076
2077 l_rem_rounding_error_amount := l_rounding_error;
2078 OPEN adjustment_for_rounding_error(l_selected_check_id,
2079 l_rem_rounding_error_amount);
2080
2081 /* Starting the loop which will process the rounding difference. */
2082
2083 WHILE (l_rem_rounding_error_amount > 0) LOOP
2084
2085 /* Selecting the maximum discount amount again since the correction for the Rounding Difference
2086 needs to be spread over multiple invoices. */
2087
2088 FETCH adjustment_for_rounding_error INTO l_max_discount_amount;
2089 EXIT WHEN adjustment_for_rounding_error%NOTFOUND;
2090
2091 /* If the maximum discount amount is less than the rounding error then subtract the maximum
2092 discount amount from the discount amount for that invoice. This will amount to making the discount
2093 amount equal to zero. */
2094
2095 IF (l_max_discount_amount <= l_rem_rounding_error_amount) THEN
2096
2097 UPDATE iby_hook_docs_in_pmt_t
2098 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN - l_max_discount_amount,
2099 document_amount = document_amount + l_max_discount_amount
2100 WHERE payment_id = l_selected_check_id
2101 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
2102 AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
2103 AND ROWNUM = 1;
2104
2105
2106 /* The rounding difference has been adjusted by the Maximum Discount Amount. So now the rounding difference
2107 amount needs to be recalculated. */
2108
2109 l_rem_rounding_error_amount := l_rem_rounding_error_amount -
2110 l_max_discount_amount;
2111 ELSE
2112
2113 /* The Remaining Rounding Error is less than the maximum discount amount. So now use the remaining
2114 rounding error amount as the adjustment amount. */
2115
2116 UPDATE iby_hook_docs_in_pmt_t
2117 SET PAYMENT_CURR_DISCOUNT_TAKEN = PAYMENT_CURR_DISCOUNT_TAKEN -
2118 l_rem_rounding_error_amount,
2119 document_amount = document_amount + l_rem_rounding_error_amount
2120 WHERE payment_id = l_selected_check_id
2121 AND ABS(PAYMENT_CURR_DISCOUNT_TAKEN) = l_max_discount_amount
2122 AND ABS(document_amount) >= ABS(l_rem_rounding_error_amount)
2123 AND ROWNUM = 1;
2124
2125
2126 /* The entire rounding error amount has been adjusted in the above step. The loop can now be exited.
2127 The invoice discount amount is subtracted from the rounding error amount to exit the loop. */
2128
2129 l_rem_rounding_error_amount := l_rem_rounding_error_amount -
2130 l_max_discount_amount;
2131 END IF;
2132 END LOOP;
2133 CLOSE adjustment_for_rounding_error;
2134 END IF;
2135 /*1649310 End */
2136
2137 END IF;
2138
2139 end if; -- for (l_ok_to_pay_flag = 'Y')
2140
2141 END IF ; -- l_supplier_site_id is not null. Bug 10180027.
2142
2143 END LOOP;
2144
2145 l_debug_info := 'Close selected_checks Cursor';
2146 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2147 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name,l_debug_info);
2148 END IF;
2149
2150
2151 CLOSE selected_checks;
2152
2153 EXCEPTION
2154
2155 WHEN FND_API.G_EXC_ERROR THEN
2156 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2157 x_return_status := FND_API.G_RET_STS_ERROR ;
2158 FND_MSG_PUB.Count_And_Get
2159 ( p_count => x_msg_count,
2160 p_data => x_msg_data
2161 );
2162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2163 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2165 FND_MSG_PUB.Count_And_Get
2166 ( p_count => x_msg_count,
2167 p_data => x_msg_data
2168 );
2169
2170 WHEN BANK_CHARGE_FAILURE THEN
2171 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2172 x_return_status := FND_API.G_RET_STS_ERROR ;
2173 IF x_msg_data is NOT NULL THEN
2174 x_msg_count := 1;
2175 ELSE
2176 FND_MSG_PUB.Count_And_Get
2177 ( p_count => x_msg_count,
2178 p_data => x_msg_data
2179 );
2180 END IF;
2181
2182 WHEN OTHERS then
2183
2184 CLOSE selected_checks;
2185
2186 ROLLBACK TO AP_JAPANBANKCHARGEHOOK;
2187 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2188 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
2189 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
2190 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', current_calling_sequence);
2191 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
2192
2193 IF FND_MSG_PUB.Check_Msg_Level
2194 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2195 FND_MSG_PUB.Add_Exc_Msg
2196 ( G_PKG_NAME,
2197 l_api_name
2198 );
2199 END IF;
2200 FND_MSG_PUB.Count_And_Get
2201 ( p_count => x_msg_count,
2202 p_data => x_msg_data
2203 );
2204
2205 END ap_JapanBankChargeHook;
2206
2207
2208 END AP_BANK_CHARGE_PKG;