[Home] [Help]
PACKAGE BODY: APPS.IBY_CE_BATCH_RECON_PKG
Source
1 PACKAGE BODY IBY_CE_BATCH_RECON_PKG as
2 /* $Header: ibycepib.pls 120.9.12020000.2 2012/07/12 14:48:27 sgogula ship $ */
3
4 --============================================================================
5 -- For reconciliation of IBY payment instructions in CE
6 -- Copied from AP_RECONCILIATION_PKG 115.43
7 -- This API will be called by CE for reconciliation at the batch level with
8 -- R12 payment batches - i.e., IBY payment instructions.
9 -- (CE only supports auto recon at batch level - no manual clearing).
10 -- The API do proration as AP, then call product APIs at transaction level
11 -- so they can execute their business logic.
12 --
13 -- R12 Payment instructions supports multiple currencies and orgs
14 -- with in an instruction; CE will support only single currency for batch
15 -- recons, and orgs will not be considered during CE batch recon.
16 --
17 --============================================================================
18
19 -- Global exception
20 G_abort_it EXCEPTION;
21
22 -- module name used for the application debugging framework
23 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_CE_BATCH_RECON_PKG';
24
25
26
27 FUNCTION Case_Type(X_BANK_CURRENCY IN VARCHAR2,
28 X_PAY_CURRENCY IN VARCHAR2,
29 X_FUNC_CURRENCY IN VARCHAR2
30 ) RETURN VARCHAR2;
31
32 PROCEDURE Payment_Instruction_Clearing(
33 P_PAYMENT_INSTRUCTION_ID IN NUMBER,
34 P_ACCOUNTING_DATE IN DATE,
35 P_CLEARED_DATE IN DATE,
36 P_TRANSACTION_AMOUNT IN NUMBER, -- in bank curr.
37 P_ERROR_AMOUNT IN NUMBER, -- in bank curr.
38 P_CHARGE_AMOUNT IN NUMBER, -- in bank curr.
39 P_CURRENCY_CODE IN VARCHAR2, -- bank curr. code
40 P_EXCHANGE_RATE_TYPE IN VARCHAR2, -- between payment and functional
41 P_EXCHANGE_RATE_DATE IN DATE, -- between payment and functional
42 P_EXCHANGE_RATE IN NUMBER, -- between payment and functional
43 P_MATCHED_FLAG IN VARCHAR2,
44 P_ACTUAL_VALUE_DATE IN DATE,
45 P_PASSIN_MODE IN VARCHAR2, -- passed back to CE
46 P_STATEMENT_LINE_ID IN NUMBER, -- passed back to CE
47 P_STATEMENT_LINE_TYPE IN VARCHAR2, -- passed back to CE
48 P_LAST_UPDATE_DATE IN DATE,
49 P_LAST_UPDATED_BY IN NUMBER,
50 P_LAST_UPDATE_LOGIN IN NUMBER,
51 P_CREATED_BY IN NUMBER,
52 P_CREATION_DATE IN DATE,
53 P_PROGRAM_UPDATE_DATE IN DATE,
54 P_PROGRAM_APPLICATION_ID IN NUMBER,
55 P_PROGRAM_ID IN NUMBER,
56 P_REQUEST_ID IN NUMBER,
57 P_CALLING_SEQUENCE IN VARCHAR2,
58 P_LOGICAL_GROUP_REFERENCE IN VARCHAR2
59 ) AS
60
61 l_trx_id NUMBER;
62 l_app_id NUMBER;
63 l_currency_case VARCHAR2(30);
64 l_status VARCHAR2(30);
65
66 l_bank_trxn_amount NUMBER;
67 l_bank_error_amount NUMBER;
68 l_bank_charge_amount NUMBER;
69
70 l_pmt_currency_code ap_checks.currency_code%TYPE;
71 l_functional_currency_code ap_system_parameters.base_currency_code%TYPE;
72 l_bank_to_base_xrate ap_checks.exchange_rate%TYPE;
73 l_bank_to_base_xrate_type ap_checks.exchange_rate_type%TYPE;
74 l_bank_to_base_xrate_date ap_checks.exchange_date%TYPE;
75 l_errors_pmt_amount NUMBER;
76 l_charges_pmt_amount NUMBER;
77 l_trx_base_amount NUMBER;
78 l_errors_base_amount NUMBER;
79 l_charges_base_amount NUMBER;
80 l_remainder_errors_pmt_amt ap_checks.amount%TYPE;
81 l_remainder_charges_pmt_amt ap_checks.amount%TYPE;
82 l_errors_bank_amount ap_checks.amount%TYPE;
83 l_charges_bank_amount ap_checks.amount%TYPE;
84
85 l_max_pmt_amt ap_checks.amount%TYPE;
86 l_pay_sum_amt ap_checks.amount%TYPE;
87 l_payment_count NUMBER;
88 l_running_total_payments NUMBER := 0;
89 l_runtotal_trx_bank_amount NUMBER := 0;
90 l_runtotal_errors_bank_amount NUMBER := 0;
91 l_runtotal_charges_bank_amount NUMBER := 0;
92 l_check_amount ap_checks.amount%TYPE;
93 l_ind_errors_pmt_amount ap_checks.amount%TYPE;
94 l_ind_charges_pmt_amount ap_checks.amount%TYPE;
95 current_calling_sequence VARCHAR2(2000);
96
97 l_debug_info VARCHAR2(240);
98 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Payment_Instruction_Clearing';
99
100 CURSOR l_ins_pmt_clr_cur IS
101 SELECT distinct check_id,
102 status_lookup_code,
103 amount,
104 200 app_id
105 FROM ap_checks checks, iby_payments_all pmts
106 WHERE checks.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
107 AND checks.payment_id = pmts.payment_id (+)
108 AND Nvl(pmts.logical_group_reference, 'N') = Nvl(P_LOGICAL_GROUP_REFERENCE, nvl(pmts.logical_group_reference,'N'))
109
110 /* Bug 8340931
111 and
112 exists (select 1 from iby_payments_all pay
113 where nvl(logical_group_reference,'N') = nvl(P_LOGICAL_GROUP_REFERENCE,nvl(logical_group_reference,'N'))
114 and checks.payment_instruction_id = pay.payment_instruction_id
115 and checks.payment_id = pay.payment_id)*/
116 AND checks.status_lookup_code not in
117 ('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
118 'UNCONFIRMED SET UP', 'RECONCILED', 'RECONCILED UNACCOUNTED',
119 'ISSUED')
120 UNION ALL
121 SELECT distinct CC.CASHFLOW_ID,
122 CC.CASHFLOW_STATUS_CODE,
123 CC.CASHFLOW_AMOUNT,
124 260 app_id
125 FROM CE_SECURITY_PROFILES_GT le,
126 iby_fd_docs_payable_v docs,
127 iby_fd_payments_v pay,
128 CE_CASHFLOWS CC,
129 CE_CASHFLOW_ACCT_H CCH
130 WHERE pay.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
131 AND CC.CASHFLOW_STATUS_CODE IN ('CREATED', 'CLEARED')
132 AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID
133 AND CCH.CURRENT_RECORD_FLAG = 'Y'
134 AND CCH.EVENT_TYPE in
135 ('CE_STMT_RECORDED', 'CE_BAT_CLEARED', 'CE_BAT_CREATED')
136 and cc.source_trxn_type ='BAT'
137 AND exists
138 (select null
139 from ce_payment_transactions pt
140 where cc.trxn_reference_number = pt.trxn_reference_number
141 and pt.trxn_reference_number = cc.trxn_reference_number
142 and pt.trxn_status_code = 'SETTLED')
143 and cc.trxn_reference_number = docs.calling_app_doc_ref_number
144 and pay.payment_id = docs.payment_id
145 and docs.CALLING_APP_ID = 260
146 AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
147 AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY'
148 ORDER BY 3;
149
150
151 CURSOR l_ins_total_cur IS
152 SELECT count(check_id), sum(amount) , max(amount)
153 FROM ap_checks checks, iby_payments_all pmts
154 WHERE checks.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
155 AND checks.payment_id = pmts.payment_id (+)
156 AND Nvl(pmts.logical_group_reference, 'N') = Nvl(P_LOGICAL_GROUP_REFERENCE, nvl(pmts.logical_group_reference,'N'))
157 /* Bug 8340931
158 AND exists (select 1 from iby_payments_all pay
159 where nvl(logical_group_reference,'N') = nvl(P_LOGICAL_GROUP_REFERENCE,nvl(logical_group_reference,'N'))
160 and checks.payment_instruction_id = pay.payment_instruction_id
161 and checks.payment_id = pay.payment_id) */
162 AND checks.status_lookup_code NOT IN
163 ('VOIDED','SPOILED','OVERFLOW','SETUP','STOP INITIATED',
164 'UNCONFIRMED SET UP', 'RECONCILED', 'RECONCILED UNACCOUNTED',
165 'ISSUED')
166 UNION ALL
167 SELECT count(CC.CASHFLOW_ID), sum(CC.CASHFLOW_AMOUNT) ,
168 max(CC.CASHFLOW_AMOUNT)
169 FROM CE_SECURITY_PROFILES_GT le,
170 iby_fd_docs_payable_v docs,
171 iby_fd_payments_v pay,
172 CE_CASHFLOWS CC,
173 CE_CASHFLOW_ACCT_H CCH
174 WHERE pay.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
175 AND CC.CASHFLOW_STATUS_CODE IN ('CREATED', 'CLEARED')
176 AND CC.CASHFLOW_ID = CCH.CASHFLOW_ID
177 AND CCH.CURRENT_RECORD_FLAG = 'Y'
178 AND CCH.EVENT_TYPE in
179 ('CE_STMT_RECORDED', 'CE_BAT_CLEARED', 'CE_BAT_CREATED')
180 and cc.source_trxn_type ='BAT'
181 AND exists
182 (select null
183 from ce_payment_transactions pt
184 where cc.trxn_reference_number = pt.trxn_reference_number
185 and pt.trxn_reference_number = cc.trxn_reference_number
186 and pt.trxn_status_code = 'SETTLED')
187 and cc.trxn_reference_number = docs.calling_app_doc_ref_number
188 and pay.payment_id = docs.payment_id
189 and docs.CALLING_APP_ID = 260
190 AND CC.CASHFLOW_LEGAL_ENTITY_ID = LE.ORGANIZATION_ID
191 AND LE.ORGANIZATION_TYPE = 'LEGAL_ENTITY';
192
193 begin
194
195 current_calling_sequence := P_CALLING_SEQUENCE ||
196 'IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Clearing';
197
198 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
199 debug_level => FND_LOG.LEVEL_PROCEDURE,
200 module => l_Debug_Module);
201
202 iby_debug_pub.add(debug_msg => 'Input parameters: ',
203 debug_level => FND_LOG.LEVEL_STATEMENT,
204 module => l_Debug_Module);
205
206 iby_debug_pub.add(debug_msg => '============================================',
207 debug_level => FND_LOG.LEVEL_STATEMENT,
208 module => l_Debug_Module);
209
210 iby_debug_pub.add(debug_msg => 'P_PAYMENT_INSTRUCTION_ID: ' || P_PAYMENT_INSTRUCTION_ID,
211 debug_level => FND_LOG.LEVEL_STATEMENT,
212 module => l_Debug_Module);
213
214 iby_debug_pub.add(debug_msg => 'P_ACCOUNTING_DATE: ' || P_ACCOUNTING_DATE,
215 debug_level => FND_LOG.LEVEL_STATEMENT,
216 module => l_Debug_Module);
217
218 iby_debug_pub.add(debug_msg => 'P_CLEARED_DATE: ' || P_CLEARED_DATE,
219 debug_level => FND_LOG.LEVEL_STATEMENT,
220 module => l_Debug_Module);
221
222 iby_debug_pub.add(debug_msg => 'P_TRANSACTION_AMOUNT: ' || P_TRANSACTION_AMOUNT,
223 debug_level => FND_LOG.LEVEL_STATEMENT,
224 module => l_Debug_Module);
225
226 iby_debug_pub.add(debug_msg => 'P_ERROR_AMOUNT: ' || P_ERROR_AMOUNT,
227 debug_level => FND_LOG.LEVEL_STATEMENT,
228 module => l_Debug_Module);
229
230 iby_debug_pub.add(debug_msg => 'P_CHARGE_AMOUNT: ' || P_CHARGE_AMOUNT,
231 debug_level => FND_LOG.LEVEL_STATEMENT,
232 module => l_Debug_Module);
233
234 iby_debug_pub.add(debug_msg => 'P_CURRENCY_CODE: ' || P_CURRENCY_CODE,
235 debug_level => FND_LOG.LEVEL_STATEMENT,
236 module => l_Debug_Module);
237
238 iby_debug_pub.add(debug_msg => 'P_EXCHANGE_RATE_TYPE: ' || P_EXCHANGE_RATE_TYPE,
239 debug_level => FND_LOG.LEVEL_STATEMENT,
240 module => l_Debug_Module);
241
242 iby_debug_pub.add(debug_msg => 'P_EXCHANGE_RATE_DATE: ' || P_EXCHANGE_RATE_DATE,
243 debug_level => FND_LOG.LEVEL_STATEMENT,
244 module => l_Debug_Module);
245
246 iby_debug_pub.add(debug_msg => 'P_EXCHANGE_RATE: ' || P_EXCHANGE_RATE,
247 debug_level => FND_LOG.LEVEL_STATEMENT,
248 module => l_Debug_Module);
249
250 iby_debug_pub.add(debug_msg => 'P_MATCHED_FLAG: ' || P_MATCHED_FLAG,
251 debug_level => FND_LOG.LEVEL_STATEMENT,
252 module => l_Debug_Module);
253
254 iby_debug_pub.add(debug_msg => 'P_ACTUAL_VALUE_DATE: ' || P_ACTUAL_VALUE_DATE,
255 debug_level => FND_LOG.LEVEL_STATEMENT,
256 module => l_Debug_Module);
257
258 iby_debug_pub.add(debug_msg => 'P_PASSIN_MODE: ' || P_PASSIN_MODE,
259 debug_level => FND_LOG.LEVEL_STATEMENT,
260 module => l_Debug_Module);
261
262 iby_debug_pub.add(debug_msg => 'P_STATEMENT_LINE_ID: ' || P_STATEMENT_LINE_ID,
263 debug_level => FND_LOG.LEVEL_STATEMENT,
264 module => l_Debug_Module);
265
266 iby_debug_pub.add(debug_msg => 'P_STATEMENT_LINE_TYPE: ' || P_STATEMENT_LINE_TYPE,
267 debug_level => FND_LOG.LEVEL_STATEMENT,
268 module => l_Debug_Module);
269
270 iby_debug_pub.add(debug_msg => '============================================',
271 debug_level => FND_LOG.LEVEL_STATEMENT,
272 module => l_Debug_Module);
273
274
275 IF (P_PAYMENT_INSTRUCTION_ID IS NULL ) THEN
276 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
277 fnd_message.set_token('API', l_Debug_Module);
278 fnd_message.set_token('PARAM', P_PAYMENT_INSTRUCTION_ID);
279 fnd_message.set_token('REASON', ' parameter cannot be null');
280 FND_MSG_PUB.ADD;
281 RAISE fnd_api.g_exc_unexpected_error;
282 END IF;
283
284
285 l_errors_bank_amount := nvl(P_ERROR_AMOUNT, 0);
286 l_charges_bank_amount := nvl(P_CHARGE_AMOUNT, 0);
287
288 SELECT base_currency_code
289 INTO l_functional_currency_code
290 FROM ap_system_parameters;
291
292 iby_debug_pub.add(debug_msg => 'base_currency_code: ' || l_functional_currency_code,
293 debug_level => FND_LOG.LEVEL_STATEMENT,
294 module => l_Debug_Module);
295
296 SELECT payment_currency_code
297 INTO l_pmt_currency_code
298 FROM iby_pay_instructions_all
299 WHERE payment_instruction_id = P_PAYMENT_INSTRUCTION_ID;
300
301 iby_debug_pub.add(debug_msg => 'payment currency_code: ' || l_pmt_currency_code,
302 debug_level => FND_LOG.LEVEL_STATEMENT,
303 module => l_Debug_Module);
304
305 l_currency_case := Case_Type(P_CURRENCY_CODE,
306 l_pmt_currency_code,
307 l_functional_currency_code
308 );
309
310 iby_debug_pub.add(debug_msg => 'l_currency_case: ' || l_currency_case,
311 debug_level => FND_LOG.LEVEL_STATEMENT,
312 module => l_Debug_Module);
313
314 -- If international or cross currency, convert to payment currency
315 -- the errors and charges before proration.
316 IF (l_currency_case = 'INTERNATIONAL') THEN
317
318 l_remainder_errors_pmt_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
319 l_errors_bank_amount / nvl(P_EXCHANGE_RATE,1),
320 l_pmt_currency_code );
321 l_remainder_charges_pmt_amt := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
322 l_charges_bank_amount / nvl(P_EXCHANGE_RATE,1),
323 l_pmt_currency_code );
324
325 -- Since the bank and base currencies are the same ...
326 l_bank_to_base_xrate_type := NULL;
327 l_bank_to_base_xrate_date := NULL;
328 l_bank_to_base_xrate := NULL;
329
330 ELSIF (l_currency_case = 'FOREIGN') THEN
331
332 l_remainder_errors_pmt_amt := l_errors_bank_amount;
333 l_remainder_charges_pmt_amt := l_charges_bank_amount;
334 l_bank_to_base_xrate_type := P_EXCHANGE_RATE_TYPE;
335 l_bank_to_base_xrate_date := P_EXCHANGE_RATE_DATE;
336 l_bank_to_base_xrate := P_EXCHANGE_RATE;
337
338 ELSIF (l_currency_case = 'DOMESTIC') THEN
339
340 l_remainder_errors_pmt_amt := l_errors_bank_amount;
341 l_remainder_charges_pmt_amt := l_charges_bank_amount;
342 l_bank_to_base_xrate_type := NULL;
343 l_bank_to_base_xrate_date := NULL;
344 l_bank_to_base_xrate := NULL;
345
346 END IF;
347
348 -- Prepare errors and charges for proration. Now the amounts
349 -- should be in payment currency
350 l_errors_pmt_amount := l_remainder_errors_pmt_amt;
351 l_charges_pmt_amount := l_remainder_charges_pmt_amt;
352
353 OPEN l_ins_total_cur;
354 FETCH l_ins_total_cur INTO l_payment_count, l_pay_sum_amt, l_max_pmt_amt;
355 CLOSE l_ins_total_cur;
356
357 l_running_total_payments := 0;
358
359 -- start the main proration loop
360 OPEN l_ins_pmt_clr_cur;
361 LOOP
362
363 FETCH l_ins_pmt_clr_cur INTO
364 l_trx_id,
365 l_status,
366 l_check_amount,
367 l_app_id;
368
369 l_debug_info := 'Inside l_ins_pmt_clr_cur cursor';
370 iby_debug_pub.add(debug_msg => 'l_trx_id: ' || l_trx_id,
371 debug_level => FND_LOG.LEVEL_STATEMENT,
372 module => l_Debug_Module);
373
374 iby_debug_pub.add(debug_msg => 'l_status: ' || l_status,
375 debug_level => FND_LOG.LEVEL_STATEMENT,
376 module => l_Debug_Module);
377
378 iby_debug_pub.add(debug_msg => 'l_check_amount: ' || l_check_amount,
379 debug_level => FND_LOG.LEVEL_STATEMENT,
380 module => l_Debug_Module);
381
382 iby_debug_pub.add(debug_msg => 'l_app_id: ' || l_app_id,
383 debug_level => FND_LOG.LEVEL_STATEMENT,
384 module => l_Debug_Module);
385
386 IF l_ins_pmt_clr_cur%NOTFOUND THEN
387 IF l_ins_pmt_clr_cur%ROWCOUNT < 1 THEN
388 RAISE no_data_found;
389 ELSE -- No more rows
390 EXIT ;
391 END IF;
392 END IF;
393
394 l_running_total_payments := l_running_total_payments + l_check_amount;
395
396 IF (l_pay_sum_amt = 0) THEN
397 l_debug_info := 'Inside l_pay_sum_amt is 0';
398
399 l_ind_errors_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
400 l_errors_pmt_amount/l_payment_count,
401 l_pmt_currency_code );
402 l_ind_charges_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
403 l_charges_pmt_amount/l_payment_count,
404 l_pmt_currency_code );
405
406 ELSIF (l_running_total_payments = l_pay_sum_amt) THEN
407 l_debug_info := 'Inside l_pay_sum_amt is running total';
408
409 l_ind_errors_pmt_amount := l_remainder_errors_pmt_amt;
410 l_ind_charges_pmt_amount := l_remainder_charges_pmt_amt;
411
412 ELSE
413 l_debug_info := 'Inside l_pay_sum_amt is another value';
414
415 l_ind_errors_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
416 l_errors_pmt_amount*l_check_amount
417 /l_pay_sum_amt,
418 l_pmt_currency_code );
419 l_ind_charges_pmt_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY (
420 l_charges_pmt_amount*l_check_amount
421 /l_pay_sum_amt,
422 l_pmt_currency_code );
423
424 END IF ; -- Total payment batch amount is 0
425
426
427 IF (l_currency_case = 'INTERNATIONAL') THEN
428
429 IF (l_running_total_payments = l_pay_sum_amt) THEN
430 l_debug_info := 'Inside Negotiable, International amounts equal';
431
432 l_trx_base_amount := P_TRANSACTION_AMOUNT - l_runtotal_trx_bank_amount;
433 l_errors_base_amount := P_ERROR_AMOUNT
434 - l_runtotal_errors_bank_amount;
435 l_charges_base_amount := P_CHARGE_AMOUNT
436 - l_runtotal_charges_bank_amount;
437 ELSE
438 l_debug_info := 'Inside Negotiable, International amounts not eq';
439
440 l_trx_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
441 (l_check_amount + l_ind_errors_pmt_amount
442 + l_ind_charges_pmt_amount) * nvl(P_EXCHANGE_RATE,1),
443 l_functional_currency_code);
444 l_errors_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
445 l_ind_errors_pmt_amount
446 * nvl(P_EXCHANGE_RATE,1),
447 l_functional_currency_code);
448 l_charges_base_amount := AP_UTILITIES_PKG.AP_ROUND_CURRENCY(
449 l_ind_charges_pmt_amount
450 * nvl(P_EXCHANGE_RATE,1),
451 l_functional_currency_code);
452 END IF;
453
454 l_runtotal_trx_bank_amount := l_runtotal_trx_bank_amount
455 + l_trx_base_amount;
456 l_runtotal_errors_bank_amount := l_runtotal_errors_bank_amount
457 + l_errors_base_amount;
458 l_runtotal_charges_bank_amount := l_runtotal_charges_bank_amount
459 + l_charges_base_amount;
460
461 l_bank_trxn_amount := l_trx_base_amount;
462 l_bank_error_amount := l_errors_base_amount;
463 l_bank_charge_amount := l_charges_base_amount;
464
465 ELSIF (l_currency_case = 'FOREIGN' OR l_currency_case = 'DOMESTIC') THEN
466 l_bank_trxn_amount := l_check_amount + l_ind_errors_pmt_amount + l_ind_charges_pmt_amount;
467 l_bank_error_amount := l_ind_errors_pmt_amount;
468 l_bank_charge_amount := l_ind_charges_pmt_amount;
469
470 END IF;
471
472 IF l_app_id = 200 THEN
473 AP_RECONCILIATION_PKG.recon_payment_history(
474 X_CHECKRUN_ID => NULL,
475 X_CHECK_ID => l_trx_id,
476 X_ACCOUNTING_DATE => P_ACCOUNTING_DATE,
477 X_CLEARED_DATE => P_CLEARED_DATE,
478 X_TRANSACTION_AMOUNT => NVL(l_bank_trxn_amount, 0),
479 X_TRANSACTION_TYPE => 'PAYMENT CLEARING',
480 X_ERROR_AMOUNT => NVL(l_bank_error_amount, 0),
481 X_CHARGE_AMOUNT => NVL(l_bank_charge_amount, 0),
482 X_CURRENCY_CODE => P_CURRENCY_CODE,
483 X_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
484 X_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
485 X_EXCHANGE_RATE => P_EXCHANGE_RATE,
486 X_MATCHED_FLAG => P_MATCHED_FLAG, --bug 13818916, changed hardcoded 'Y' to p_matched_flag
487 X_ACTUAL_VALUE_DATE => P_ACTUAL_VALUE_DATE,
488 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
489 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
490 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
491 X_CREATED_BY => P_CREATED_BY,
492 X_CREATION_DATE => P_CREATION_DATE,
493 X_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
494 X_PROGRAM_APPLICATION_ID=> P_PROGRAM_APPLICATION_ID,
495 X_PROGRAM_ID => P_PROGRAM_ID,
496 X_REQUEST_ID => P_REQUEST_ID,
497 X_CALLING_SEQUENCE => current_calling_sequence
498 );
499 ELSIF l_app_id = 260 THEN
500 CE_CASHFLOW_PKG.clear_cashflow(
501 X_CASHFLOW_ID => l_trx_id,
502 X_TRX_STATUS => l_status,
503 X_actual_value_date => P_ACTUAL_VALUE_DATE,
504 X_ACCOUNTING_DATE => P_ACCOUNTING_DATE,
505 X_CLEARED_DATE => P_CLEARED_DATE,
506 X_CLEARED_AMOUNT => NVL(l_bank_trxn_amount, 0),
507 X_CLEARED_ERROR_AMOUNT => NVL(l_bank_error_amount, 0),
508 X_CLEARED_CHARGE_AMOUNT => NVL(l_bank_charge_amount, 0),
509 X_CLEARED_EXCHANGE_RATE_TYPE => P_EXCHANGE_RATE_TYPE,
510 X_CLEARED_EXCHANGE_RATE_DATE => P_EXCHANGE_RATE_DATE,
511 X_CLEARED_EXCHANGE_RATE => P_EXCHANGE_RATE,
512 X_PASSIN_MODE => P_PASSIN_MODE,
513 X_STATEMENT_LINE_ID => P_STATEMENT_LINE_ID,
514 X_STATEMENT_LINE_TYPE => P_STATEMENT_LINE_TYPE
515 );
516 END IF;
517
518 l_remainder_errors_pmt_amt := l_remainder_errors_pmt_amt
519 - l_ind_errors_pmt_amount;
520 l_remainder_charges_pmt_amt := l_remainder_charges_pmt_amt
521 - l_ind_charges_pmt_amount;
522
523 END LOOP; -- Loop through payments in an instruction
524
525 CLOSE l_ins_pmt_clr_cur;
526
527 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
528 debug_level => FND_LOG.LEVEL_PROCEDURE,
529 module => l_Debug_Module);
530
531
532 EXCEPTION
533 WHEN NO_DATA_FOUND THEN
534 IF l_ins_pmt_clr_cur%ISOPEN THEN
535 CLOSE l_ins_pmt_clr_cur;
536 END IF;
537 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
538 APP_EXCEPTION.RAISE_EXCEPTION;
539 WHEN OTHERS THEN
540 IF l_ins_pmt_clr_cur%ISOPEN THEN
541 CLOSE l_ins_pmt_clr_cur;
542 END IF;
543 RAISE;
544
545 END Payment_Instruction_Clearing;
546
547
548
549
550 PROCEDURE Payment_Instruction_Unclearing(
551 P_PAYMENT_INSTRUCTION_ID IN NUMBER,
552 P_ACCOUNTING_DATE IN DATE,
553 P_MATCHED_FLAG IN VARCHAR2,
554 P_LAST_UPDATE_DATE IN DATE,
555 P_LAST_UPDATED_BY IN NUMBER,
556 P_LAST_UPDATE_LOGIN IN NUMBER,
557 P_CREATED_BY IN NUMBER,
558 P_CREATION_DATE IN DATE,
559 P_PROGRAM_UPDATE_DATE IN DATE,
560 P_PROGRAM_APPLICATION_ID IN NUMBER,
561 P_PROGRAM_ID IN NUMBER,
562 P_REQUEST_ID IN NUMBER,
563 P_CALLING_SEQUENCE IN VARCHAR2
564 ) AS
565
566 -- conditions for AP are
567 -- copied from apreconb 115.44
568 CURSOR l_ins_pmt_unclr_cur IS
569 SELECT chk.check_id,
570 200 app_id
571 FROM iby_payments_all pmt,
572 iby_pay_service_requests ppr,
573 ap_checks_all chk,
574 ap_payment_history apha
575 WHERE pmt.payment_instruction_id = P_PAYMENT_INSTRUCTION_ID
576 AND pmt.payment_service_request_id = ppr.payment_service_request_id
577 AND ppr.calling_app_id = 200
578 AND apha.check_id = chk.check_id
579 AND apha.transaction_type = 'PAYMENT CLEARING'
580 AND chk.status_lookup_code IN
581 ('CLEARED', 'CLEARED BUT UNACCOUNTED',
582 'RECONCILED', 'RECONCILED UNACCOUNTED')
583 AND NOT EXISTS
584 (SELECT aphb.payment_history_id
585 FROM ap_payment_history aphb
586 WHERE aphb.check_id = apha.check_id
587 AND aphb.rev_pmt_hist_id = apha.payment_history_id);
588
589 l_trx_id NUMBER;
590 l_app_id NUMBER;
591 current_calling_sequence VARCHAR2(2000);
592 l_debug_info VARCHAR2(240);
593 l_Debug_Module VARCHAR2(255) := G_DEBUG_MODULE || '.Payment_Instruction_Unclearing';
594
595 begin
596
597 current_calling_sequence := P_CALLING_SEQUENCE ||
598 'IBY_CE_BATCH_RECON_PKG.Payment_Instruction_Unclearing';
599
600 iby_debug_pub.add(debug_msg => 'Enter: ' || l_Debug_Module,
601 debug_level => FND_LOG.LEVEL_PROCEDURE,
602 module => l_Debug_Module);
603
604 iby_debug_pub.add(debug_msg => 'Input parameters: ',
605 debug_level => FND_LOG.LEVEL_STATEMENT,
606 module => l_Debug_Module);
607
608 iby_debug_pub.add(debug_msg => '============================================',
609 debug_level => FND_LOG.LEVEL_STATEMENT,
610 module => l_Debug_Module);
611
612 iby_debug_pub.add(debug_msg => 'P_PAYMENT_INSTRUCTION_ID: ' || P_PAYMENT_INSTRUCTION_ID,
613 debug_level => FND_LOG.LEVEL_STATEMENT,
614 module => l_Debug_Module);
615
616 iby_debug_pub.add(debug_msg => 'P_ACCOUNTING_DATE: ' || P_ACCOUNTING_DATE,
617 debug_level => FND_LOG.LEVEL_STATEMENT,
618 module => l_Debug_Module);
619
620 iby_debug_pub.add(debug_msg => 'P_MATCHED_FLAG: ' || P_MATCHED_FLAG,
621 debug_level => FND_LOG.LEVEL_STATEMENT,
622 module => l_Debug_Module);
623
624 iby_debug_pub.add(debug_msg => '============================================',
625 debug_level => FND_LOG.LEVEL_STATEMENT,
626 module => l_Debug_Module);
627
628
629 IF (P_PAYMENT_INSTRUCTION_ID IS NULL ) THEN
630 fnd_message.set_name('IBY', 'IBY_G_INVALID_PARAM_ERR');
631 fnd_message.set_token('API', l_Debug_Module);
632 fnd_message.set_token('PARAM', P_PAYMENT_INSTRUCTION_ID);
633 fnd_message.set_token('REASON', ' parameter cannot be null');
634 FND_MSG_PUB.ADD;
635 RAISE fnd_api.g_exc_unexpected_error;
636 END IF;
637
638
639 -- start the main loop
640 OPEN l_ins_pmt_unclr_cur;
641 LOOP
642
643 FETCH l_ins_pmt_unclr_cur INTO
644 l_trx_id,
645 l_app_id;
646
647 l_debug_info := 'Inside l_ins_pmt_unclr_cur cursor';
648
649 IF l_ins_pmt_unclr_cur%NOTFOUND THEN
650 IF l_ins_pmt_unclr_cur%ROWCOUNT < 1 THEN
651 RAISE no_data_found;
652 ELSE -- No more rows
653 EXIT ;
654 END IF;
655 END IF;
656
657 IF l_app_id = 200 THEN
658 AP_RECONCILIATION_PKG.recon_payment_history(
659 X_CHECKRUN_ID => NULL,
660 X_CHECK_ID => l_trx_id,
661 X_ACCOUNTING_DATE => P_ACCOUNTING_DATE,
662 X_CLEARED_DATE => NULL,
663 X_TRANSACTION_AMOUNT => NULL,
664 X_TRANSACTION_TYPE => 'PAYMENT UNCLEARING',
665 X_ERROR_AMOUNT => NULL,
666 X_CHARGE_AMOUNT => NULL,
667 X_CURRENCY_CODE => NULL,
668 X_EXCHANGE_RATE_TYPE => NULL,
669 X_EXCHANGE_RATE_DATE => NULL,
670 X_EXCHANGE_RATE => NULL,
671 X_MATCHED_FLAG => P_MATCHED_FLAG,
672 X_ACTUAL_VALUE_DATE => NULL,
673 X_LAST_UPDATE_DATE => P_LAST_UPDATE_DATE,
674 X_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
675 X_LAST_UPDATE_LOGIN => P_LAST_UPDATE_LOGIN,
676 X_CREATED_BY => P_CREATED_BY,
677 X_CREATION_DATE => P_CREATION_DATE,
678 X_PROGRAM_UPDATE_DATE => P_PROGRAM_UPDATE_DATE,
679 X_PROGRAM_APPLICATION_ID=> P_PROGRAM_APPLICATION_ID,
680 X_PROGRAM_ID => P_PROGRAM_ID,
681 X_REQUEST_ID => P_REQUEST_ID,
682 X_CALLING_SEQUENCE => current_calling_sequence
683 );
684 END IF;
685
686 END LOOP; -- Loop through payments in an instruction
687
688 CLOSE l_ins_pmt_unclr_cur;
689
690 iby_debug_pub.add(debug_msg => 'Exit: ' || l_Debug_Module,
691 debug_level => FND_LOG.LEVEL_PROCEDURE,
692 module => l_Debug_Module);
693
694 EXCEPTION
695 WHEN NO_DATA_FOUND THEN
696 IF l_ins_pmt_unclr_cur%ISOPEN THEN
697 CLOSE l_ins_pmt_unclr_cur;
698 END IF;
699 FND_MESSAGE.SET_NAME('SQLAP','AP_RECON_NO_DATA_FOUND');
700 APP_EXCEPTION.RAISE_EXCEPTION;
701 WHEN OTHERS THEN
702 IF l_ins_pmt_unclr_cur%ISOPEN THEN
703 CLOSE l_ins_pmt_unclr_cur;
704 END IF;
705 RAISE;
706
707 end Payment_Instruction_Unclearing;
708
709
710
711 /* *************************************************************
712 * FUNCTION: CASE_TYPE *
713 * This function returns the currency scenario of *
714 * the current payment activity given the scenarios*
715 * defined in the top of this package *
716 ************************************************************* */
717
718 FUNCTION CASE_TYPE(
719 X_BANK_CURRENCY IN VARCHAR2,
720 X_PAY_CURRENCY IN VARCHAR2,
721 X_FUNC_CURRENCY IN VARCHAR2
722 ) RETURN VARCHAR2 IS
723 BEGIN
724
725 IF (x_bank_currency = x_func_currency AND
726 x_bank_currency = x_pay_currency) THEN
727
728 RETURN('DOMESTIC');
729
730 ELSIF (x_bank_currency = x_func_currency AND
731 x_bank_currency <> x_pay_currency) THEN
732
733 RETURN('INTERNATIONAL');
734
735 ELSIF (x_bank_currency <> x_func_currency AND
736 x_bank_currency = x_pay_currency) THEN
737
738 RETURN('FOREIGN');
739
740 /******* This is not valid yet!
741 ELSIF (x_bank_currency <> x_func_currency AND
742 x_bank_currency <> x_pay_currency) THEN
743
744 RETURN('CROSS_CURRENCY');
745 *******/
746
747 ELSE
748
749 raise G_abort_it;
750
751 END IF;
752
753 END CASE_TYPE;
754
755
756 END IBY_CE_BATCH_RECON_PKG;
757
758