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