[Home] [Help]
PACKAGE BODY: APPS.OKL_INT_EXT_CSH_APP_PVT
Source
1 PACKAGE BODY OKL_INT_EXT_CSH_APP_PVT AS
2 /* $Header: OKLRIECB.pls 120.22.12010000.2 2008/09/02 09:38:23 nikshah ship $ */
3
4 -- Start of comments
5 --
6 -- Function Name : populate_error_messages
7 -- Description : populates error messages into OKL_VALIDATION_RESULTS_B and
8 -- OKL_VALIDATION_RESULTS_TL tables.
9 -- Business Rules :
10 -- Parameters :
11 -- Version : 1.0
12 -- History : AKRANGAN created.
13 --
14 -- End of comments
15 PROCEDURE populate_error_messages (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2,
18 p_error_tbl IN okl_vlr_pvt.vlrv_tbl_type,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2
22 ) IS
23 --local variables declaration
24 l_api_name CONSTANT VARCHAR2 (30) := 'populate_error_messages';
25 l_api_version CONSTANT NUMBER := 1.0;
26 l_return_status VARCHAR2 (1);
27 l_init_msg_list VARCHAR2 (1) := p_init_msg_list;
28 l_msg_count NUMBER;
29 l_msg_data VARCHAR2 (2000);
30 l_error_tbl okl_vlr_pvt.vlrv_tbl_type := p_error_tbl;
31 lx_error_tbl okl_vlr_pvt.vlrv_tbl_type;
32 BEGIN
33 -- Standard Start of API savepoint
34 SAVEPOINT pop_err_msgs_pvt;
35 l_msg_count := 0;
36 -- Initialize API return status to success
37 l_return_status :=
38 okl_api.start_activity (l_api_name,
39 g_pkg_name,
40 l_init_msg_list,
41 l_api_version,
42 l_api_version,
43 '_PVT',
44 l_return_status
45 );
46
47 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
48 RAISE okl_api.g_exception_unexpected_error;
49 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
50 RAISE okl_api.g_exception_error;
51 END IF;
52
53 --Step 1
54 --insert all new error stack errors
55 -- Call the TAPI to insert all the errored values
56 okl_vlr_pvt.insert_row (p_api_version => l_api_version,
57 p_init_msg_list => l_init_msg_list,
58 x_return_status => l_return_status,
59 x_msg_count => l_msg_count,
60 x_msg_data => l_msg_data,
61 p_vlrv_tbl => l_error_tbl,
62 x_vlrv_tbl => lx_error_tbl
63 );
64
65 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
66 RAISE okl_api.g_exception_unexpected_error;
67 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
68 RAISE okl_api.g_exception_error;
69 END IF;
70
71 --Step 3
72 --set output variables
73 x_return_status := l_return_status;
74 x_msg_count := l_msg_count;
75 x_msg_data := l_msg_data;
76 EXCEPTION
77 WHEN okl_api.g_exception_error THEN
78 ROLLBACK TO pop_err_msgs_pvt;
79 x_return_status := okl_api.g_ret_sts_unexp_error;
80 x_msg_count := l_msg_count;
81 x_msg_data := l_msg_data;
82 WHEN okl_api.g_exception_unexpected_error THEN
83 ROLLBACK TO pop_err_msgs_pvt;
84 x_return_status := okl_api.g_ret_sts_error;
85 x_msg_count := l_msg_count;
86 x_msg_data := l_msg_data;
87 WHEN OTHERS THEN
88 ROLLBACK TO pop_err_msgs_pvt;
89 x_return_status := okl_api.g_ret_sts_unexp_error;
90 okl_api.set_message (p_app_name => 'OKL',
91 p_msg_name => 'OKL_DB_ERROR',
92 p_token1 => 'PROG_NAME',
93 p_token1_value => 'populate_error_messages',
94 p_token2 => 'SQLCODE',
95 p_token2_value => SQLCODE,
96 p_token3 => 'SQLERRM',
97 p_token3_value => SQLERRM
98 );
99 x_msg_count := l_msg_count;
100 x_msg_data := l_msg_data;
101 END populate_error_messages;
102
103 PROCEDURE process_batch(p_batch_id IN NUMBER,
104 px_error_tbl IN OUT NOCOPY okl_vlr_pvt.vlrv_tbl_type,
105 x_trx_status_code OUT NOCOPY VARCHAR2,
106 x_return_status OUT NOCOPY VARCHAR2)
107 IS
108 --AKRANGAN ADDED FOR BATCH RECEIPTS CROSS CURR FUNCTIONALITY BEGIN
109 --CURSOR FOR IDENTIFYING DEBIT DOC CURRENCY
110 CURSOR c_deb_doc_curr( p_debit_doc_id IN NUMBER)
111 IS
112 SELECT CURRENCY_CODE
113 from okc_k_headers_all_b
114 WHERE id = p_debit_doc_id
115 UNION
116 SELECT CURRENCY_CODE
117 from OKL_CNSLD_AR_HDRS_ALL_B
118 WHERE id = p_debit_doc_id
119 UNION
120 SELECT INVOICE_CURRENCY_CODE
121 FROM RA_CUSTOMER_TRX_ALL
122 WHERE CUSTOMER_TRX_ID = p_debit_doc_id;
123 --AKRANGAN ADDED FOR BATCH RECEIPTS CROSS CURR FUNCTIONALITY END
124
125 -- retrieve all receipts for each batch at status 'SUBMITTED'
126 CURSOR c_get_batch_receipts (cp_btc_id IN NUMBER) IS
127 SELECT rct.ID,
128 rca.ID,
129 rct.ile_id,
130 btc.irm_id,
131 rct.check_number,
132 rct.currency_code,
133 btc.currency_conversion_type,
134 btc.currency_conversion_rate,
135 btc.currency_conversion_date,
136 rct.amount,
137 btc.date_entered,
138 rca.cnr_id,
139 rca.khr_id,
140 rca.ar_invoice_id,
141 rca.org_id,
142 btc.date_gl_requested, --modified by akrangan for bug#6642533
143 rct.date_effective,
144 btc.remit_bank_id,
145 rct.attribute_category,
146 rct.attribute1,
147 rct.attribute2,
148 rct.attribute3,
149 rct.attribute4,
150 rct.attribute5,
151 rct.attribute6,
152 rct.attribute7,
153 rct.attribute8,
154 rct.attribute9,
155 rct.attribute10,
156 rct.attribute11,
157 rct.attribute12,
158 rct.attribute13,
159 rct.attribute14,
160 rct.attribute15
161 FROM okl_trx_csh_batch_v btc,
162 okl_trx_csh_receipt_v rct,
163 okl_txl_rcpt_apps_v rca
164 WHERE btc.ID = rct.btc_id
165 AND rct.ID = rca.rct_id_details
166 AND rct.btc_id = cp_btc_id;
167
168 -- get customer account number
169 CURSOR c_get_cust_acct_num (c_acct_id IN NUMBER) IS
170 SELECT account_number
171 FROM hz_cust_accounts
172 WHERE cust_account_id = c_acct_id;
173
174 i NUMBER := 0;
175
176 l_amount okl_trx_csh_receipt_v.amount%TYPE
177 DEFAULT NULL;
178 l_api_version NUMBER DEFAULT 1.0;
179 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
180 l_ar_inv_id okl_txl_rcpt_apps_v.ar_invoice_id%TYPE
181 DEFAULT NULL;
182 l_attribute_category okl_trx_csh_receipt_v.attribute_category%TYPE
183 DEFAULT NULL;
184 l_attribute1 okl_trx_csh_receipt_v.attribute1%TYPE
185 DEFAULT NULL;
186 l_attribute2 okl_trx_csh_receipt_v.attribute2%TYPE
187 DEFAULT NULL;
188 l_attribute3 okl_trx_csh_receipt_v.attribute3%TYPE
189 DEFAULT NULL;
190 l_attribute4 okl_trx_csh_receipt_v.attribute4%TYPE
191 DEFAULT NULL;
192 l_attribute5 okl_trx_csh_receipt_v.attribute5%TYPE
193 DEFAULT NULL;
194 l_attribute6 okl_trx_csh_receipt_v.attribute6%TYPE
195 DEFAULT NULL;
196 l_attribute7 okl_trx_csh_receipt_v.attribute7%TYPE
197 DEFAULT NULL;
198 l_attribute8 okl_trx_csh_receipt_v.attribute8%TYPE
199 DEFAULT NULL;
200 l_attribute9 okl_trx_csh_receipt_v.attribute9%TYPE
201 DEFAULT NULL;
202 l_attribute10 okl_trx_csh_receipt_v.attribute10%TYPE
203 DEFAULT NULL;
204 l_attribute11 okl_trx_csh_receipt_v.attribute11%TYPE
205 DEFAULT NULL;
206 l_attribute12 okl_trx_csh_receipt_v.attribute12%TYPE
207 DEFAULT NULL;
208 l_attribute13 okl_trx_csh_receipt_v.attribute13%TYPE
209 DEFAULT NULL;
210 l_attribute14 okl_trx_csh_receipt_v.attribute14%TYPE
211 DEFAULT NULL;
212 l_attribute15 okl_trx_csh_receipt_v.attribute15%TYPE
213 DEFAULT NULL;
214 l_btc_id okl_trx_csh_batch_v.ID%TYPE DEFAULT NULL;
215 l_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
216 l_check_number okl_trx_csh_receipt_v.check_number%TYPE
217 DEFAULT NULL;
218 l_cnr_id okl_txl_rcpt_apps_v.cnr_id%TYPE DEFAULT NULL;
219 l_conversion_rate NUMBER;
220 l_counter NUMBER;
221 l_currency_code okl_trx_csh_receipt_v.currency_code%TYPE
222 DEFAULT NULL;
223 l_cust_num ar_cash_receipts_all.pay_from_customer%TYPE
224 DEFAULT NULL;
225 l_date_effective okl_trx_csh_receipt_v.date_effective%TYPE
226 DEFAULT NULL;
227 l_debit_doc_id NUMBER;
228 l_error VARCHAR2 (2) DEFAULT NULL;
229 l_exchange_rate_type VARCHAR2(30);
230 l_currency_conv_type okl_trx_csh_receipt_v.exchange_rate_type%TYPE
231 DEFAULT NULL;
232 l_currency_conv_date okl_trx_csh_receipt_v.exchange_rate_date%TYPE
233 DEFAULT NULL;
234 l_currency_conv_rate okl_trx_csh_receipt_v.exchange_rate%TYPE
235 DEFAULT NULL;
236 l_gl_date okl_trx_csh_receipt_v.gl_date%TYPE
237 DEFAULT NULL;
238 l_ile_id okl_trx_csh_receipt_v.ile_id%TYPE
239 DEFAULT NULL;
240 l_init_msg_list VARCHAR2 (1);
241 l_inv_tot NUMBER DEFAULT 0;
242 l_invoice_currency_code okl_trx_csh_receipt_v.currency_code%TYPE
243 DEFAULT NULL;
244 l_irm_id okl_trx_csh_receipt_v.irm_id%TYPE
245 DEFAULT NULL;
246 l_khr_id okl_txl_rcpt_apps_v.khr_id%TYPE DEFAULT NULL;
247 l_msg_count NUMBER;
248 l_msg_data VARCHAR2 (2000);
249 l_msg_index_out NUMBER;
250 l_org_id okl_txl_rcpt_apps_v.org_id%TYPE DEFAULT NULL;
251 l_rca_id okl_txl_rcpt_apps_v.ID%TYPE DEFAULT NULL;
252 l_rcpt_rec okl_receipts_pvt.rcpt_rec_type;
253 l_rcpt_status_code okl_trx_csh_receipt_v.rcpt_status_code%TYPE;
254 l_rct_id okl_trx_csh_receipt_v.ID%TYPE DEFAULT NULL;
255 l_receipt_currency okl_trx_csh_receipt_v.currency_code%TYPE
256 DEFAULT NULL;
257 l_remit_bank_id NUMBER;
258 l_return_status VARCHAR2 (1);
259 l_trx_status_code okl_trx_csh_batch_v.trx_status_code%TYPE := 'PROCESSED';
260 l_validation_text VARCHAR2 (2000);
261
262 BEGIN
263 -- Standard Start of API savepoint
264 SAVEPOINT process_batch_pvt;
265 l_btc_id := p_batch_id;
266 IF px_error_tbl.COUNT > 0 THEN
267 i := px_error_tbl.LAST;
268 ELSE
269 i := 0;
270 END IF;
271
272 OPEN c_get_batch_receipts (l_btc_id);
273 LOOP
274 -- loop through batch receipts
275 FETCH c_get_batch_receipts
276 INTO l_rct_id,
277 l_rca_id,
278 l_ile_id,
279 l_irm_id,
280 l_check_number,
281 l_currency_code,
282 l_currency_conv_type,
283 l_currency_conv_rate,
284 l_currency_conv_date,
285 l_amount,
286 l_date_effective,
287 l_cnr_id,
288 l_khr_id,
289 l_ar_inv_id,
290 l_org_id,
291 l_gl_date,
292 l_date_effective,
293 l_remit_bank_id,
294 l_attribute_category,
295 l_attribute1,
296 l_attribute2,
297 l_attribute3,
298 l_attribute4,
299 l_attribute5,
300 l_attribute6,
301 l_attribute7,
302 l_attribute8,
303 l_attribute9,
304 l_attribute10,
305 l_attribute11,
306 l_attribute12,
307 l_attribute13,
308 l_attribute14,
309 l_attribute15;
310
311 IF c_get_batch_receipts%NOTFOUND THEN
312 -- No Internal Batch Payment Transactions Found for batch l_batch_name
313 okc_api.set_message (p_app_name => g_app_name,
314 p_msg_name => 'OKL_BPD_NO_INT_RCPTS'
315 );
316 EXIT; -- exit out with nothing to process.
317 END IF;
318
319 LOOP
320 -- only one receipt record
321 IF l_ile_id IS NULL
322 OR l_check_number IS NULL
323 OR l_currency_code IS NULL
324 OR l_amount IS NULL
325 OR l_amount = 0
326 OR l_irm_id IS NULL
327 OR ( l_cnr_id IS NULL
328 AND l_khr_id IS NULL
329 AND l_ar_inv_id IS NULL ) THEN
330 -- Missing mandatory fields for batch cash application process
331 fnd_file.put_line
332 (fnd_file.LOG,
333 'Some of the mandatory fields are missing - Batch'
334 );
335 fnd_file.put_line (fnd_file.LOG, 'ILE_ID = ' || l_ile_id);
336 fnd_file.put_line (fnd_file.LOG,
337 'CHECK_NUMBER = ' || l_check_number
338 );
339 fnd_file.put_line (fnd_file.LOG,
340 'CURRENCY_CODE = ' || l_currency_code
341 );
342 fnd_file.put_line (fnd_file.LOG, 'AMOUNT = ' || l_amount);
343 fnd_file.put_line (fnd_file.LOG, 'CNR_ID = ' || l_cnr_id);
344 fnd_file.put_line (fnd_file.LOG, 'KHR_ID = ' || l_khr_id);
345 fnd_file.put_line (fnd_file.LOG, 'IRM_ID = ' || l_irm_id);
346 /* okc_api.set_message
347 (p_app_name => g_app_name,
348 p_msg_name => 'OKL_BPD_MAND_CASH_APP_FLDS',
349 p_token1 => 'ILE_ID',
350 p_token1_value => l_ile_id,
351 p_token2 => 'CHECK_NUMBER',
352 p_token2_value => l_check_number,
353 p_token3 => 'CURRENCY_CODE',
354 p_token3_value => l_currency_code,
355 p_token4 => 'AMOUNT',
356 p_token4_value => l_amount,
357 p_token5 => 'CNR_ID',
358 p_token5_value => l_cnr_id,
359 p_token6 => 'KHR_ID',
360 p_token6_value => l_khr_id,
361 p_token7 => 'IRM_ID',
362 p_token7_value => l_irm_id
363 );*/
364 l_error := 'E';
365
366 IF (fnd_msg_pub.count_msg > 0) THEN
367 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
368 LOOP
369 i := i + 1;
370 px_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
371 := 'RECEIPT_BATCH';
372 px_error_tbl (i).parent_object_id /* BATCH_ID*/
373 := l_btc_id;
374 px_error_tbl (i).validation_id /* RECEIPT_ID*/
375 := l_rct_id;
376 px_error_tbl (i).result_code /* ERROR */
377 := 'ERROR';
378 fnd_msg_pub.get (p_msg_index => l_counter,
379 p_encoded => 'F',
380 p_data => l_validation_text,
381 p_msg_index_out => l_msg_index_out
382 );
383 px_error_tbl (i).validation_text := l_validation_text;
384 END LOOP;
385 END IF;
386
387 l_rcpt_status_code := 'FAILED';
388 l_trx_status_code := 'ERROR';
389 EXIT;
390 END IF;
391
392 -- populate the header and the table records to call Handle receipts method
393 OPEN c_get_cust_acct_num (l_ile_id);
394
395 FETCH c_get_cust_acct_num
396 INTO l_cust_num;
397
398 CLOSE c_get_cust_acct_num;
399
400 /* OPEN c_get_rem_bank(l_irm_id,l_currency_code);
401 FETCH c_get_rem_bank INTO l_remit_bank_id;
402 CLOSE c_get_rem_bank;*/
403
404 --akrangan modification for cross currency begin
405 --find out debit doc currency
406 IF l_khr_id IS NOT NULL THEN
407 l_debit_doc_id := l_khr_id;
408 ELSIF l_cnr_id IS NOT NULL THEN
409 l_debit_doc_id := l_cnr_id;
410 ELSIF l_ar_inv_id IS NOT NULL THEN
411 l_debit_doc_id := l_khr_id;
412 END IF;
413
414 OPEN c_deb_doc_curr (l_debit_doc_id);
415 FETCH c_deb_doc_curr INTO l_invoice_currency_code;
416 CLOSE c_deb_doc_curr;
417
418 l_receipt_currency := l_currency_code ;
419 --recipt to invoice currency conversion code
420 IF l_invoice_currency_code <> l_receipt_currency THEN
421 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
422 IF l_exchange_rate_type IS NULL THEN
423 OKL_API.set_message( p_app_name => G_APP_NAME
424 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
425 );
426 IF (fnd_msg_pub.count_msg > 0) THEN
427 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
428 LOOP
429 i := i + 1;
430 px_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
431 := 'RECEIPT_BATCH';
432 px_error_tbl (i).parent_object_id /* BATCH_ID*/
433 := l_btc_id;
434 px_error_tbl (i).validation_id /* RECEIPT_ID*/
435 := l_rct_id;
436 px_error_tbl (i).result_code /* ERROR */
437 := 'ERROR';
438 fnd_msg_pub.get (p_msg_index => l_counter,
439 p_encoded => 'F',
440 p_data => l_validation_text,
441 p_msg_index_out => l_msg_index_out
442 );
443 px_error_tbl (i).validation_text := l_validation_text;
444 END LOOP;
445 END IF;
446 RAISE G_EXCEPTION_HALT_VALIDATION;
447 ELSE
448 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
449 ,l_receipt_currency
450 ,l_date_effective
451 ,l_exchange_rate_type
452 );
453 IF l_conversion_rate IN (0,-1) THEN
454 -- Message Text: No exchange rate defined
455 x_return_status := okl_api.G_RET_STS_ERROR;
456 okl_api.set_message( p_app_name => G_APP_NAME,
457 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
458 IF (fnd_msg_pub.count_msg > 0) THEN
459 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
460 LOOP
461 i := i + 1;
462 px_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
463 := 'RECEIPT_BATCH';
464 px_error_tbl (i).parent_object_id /* BATCH_ID*/
465 := l_btc_id;
466 px_error_tbl (i).validation_id /* RECEIPT_ID*/
467 := l_rct_id;
468 px_error_tbl (i).result_code /* ERROR */
469 := 'ERROR';
470 fnd_msg_pub.get (p_msg_index => l_counter,
471 p_encoded => 'F',
472 p_data => l_validation_text,
473 p_msg_index_out => l_msg_index_out
474 );
475 px_error_tbl (i).validation_text := l_validation_text;
476 END LOOP;
477 END IF;
478 RAISE G_EXCEPTION_HALT_VALIDATION;
479 END IF;
480 END IF;
481 l_inv_tot := l_amount * l_conversion_rate;
482 END IF;
483
484 l_rcpt_rec.cash_receipt_id := NULL;
485 l_rcpt_rec.amount := l_amount ;
486 l_rcpt_rec.currency_code := l_currency_code;
487 l_rcpt_rec.customer_number := l_cust_num; --cust acct number
488 -- l_rcpt_rec.CUSTOMER_ID := l_ile_id; --cust acct id -- Commented for Regression in Customer Bank Account
489 l_rcpt_rec.receipt_number := l_check_number;
490 l_rcpt_rec.receipt_date := l_date_effective;
491 l_rcpt_rec.exchange_rate_type := l_currency_conv_type;
492 l_rcpt_rec.exchange_rate := l_currency_conv_rate;
493 l_rcpt_rec.exchange_date := l_currency_conv_date;
494 l_rcpt_rec.remittance_bank_account_id := l_remit_bank_id;
495 l_rcpt_rec.receipt_method_id := l_irm_id;
496 l_rcpt_rec.org_id := l_org_id;
497 l_rcpt_rec.gl_date := l_gl_date;
498 l_rcpt_rec.create_mode := 'UNAPPLIED';
499 l_rcpt_rec.create_mode := 'UNAPPLIED';
500 l_rcpt_rec.dff_attribute_category := l_attribute_category;
501 l_rcpt_rec.dff_attribute1 := l_attribute1;
502 l_rcpt_rec.dff_attribute2 := l_attribute2;
503 l_rcpt_rec.dff_attribute3 := l_attribute3;
504 l_rcpt_rec.dff_attribute4 := l_attribute4;
505 l_rcpt_rec.dff_attribute5 := l_attribute5;
506 l_rcpt_rec.dff_attribute6 := l_attribute6;
507 l_rcpt_rec.dff_attribute7 := l_attribute7;
508 l_rcpt_rec.dff_attribute8 := l_attribute8;
509 l_rcpt_rec.dff_attribute9 := l_attribute9;
510 l_rcpt_rec.dff_attribute10 := l_attribute10;
511 l_rcpt_rec.dff_attribute11 := l_attribute11;
512 l_rcpt_rec.dff_attribute12 := l_attribute12;
513 l_rcpt_rec.dff_attribute13 := l_attribute13;
514 l_rcpt_rec.dff_attribute14 := l_attribute14;
515 l_rcpt_rec.dff_attribute15 := l_attribute15;
516 l_rcpt_rec.customer_bank_account_id := NULL;
517 -- Included for Customer Bank Account Regression
518 l_appl_tbl (0).ar_inv_id := l_ar_inv_id;
519 l_appl_tbl (0).con_inv_id := l_cnr_id;
520 l_appl_tbl (0).contract_id := l_khr_id;
521 l_appl_tbl (0).amount_to_apply := l_inv_tot;
522 l_appl_tbl (0).amount_applied_from := l_amount;
523
524 --akrangan modification for cross currency end
525 -- call handle receipts
526 okl_receipts_pvt.handle_receipt
527 (p_api_version => l_api_version,
528 p_init_msg_list => l_init_msg_list,
529 x_return_status => l_return_status,
530 x_msg_count => l_msg_count,
531 x_msg_data => l_msg_data,
532 p_rcpt_rec => l_rcpt_rec,
533 p_appl_tbl => l_appl_tbl,
534 x_cash_receipt_id => l_cash_receipt_id
535 );
536
537 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
538 okl_api.set_message
539 (p_app_name => g_app_name,
540 p_msg_name => 'OKL_DB_ERROR',
541 p_token1 => 'PROG_NAME',
542 p_token1_value => 'int_ext_csh_app',
543 p_token2 => 'SQLCODE',
544 p_token2_value => SQLCODE,
545 p_token3 => 'SQLERRM',
546 p_token3_value => SQLERRM
547 );
548
549 IF (fnd_msg_pub.count_msg > 0) THEN
550 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
551 LOOP
552 i := i + 1;
553 px_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
554 := 'RECEIPT_BATCH';
555 px_error_tbl (i).parent_object_id /* BATCH_ID*/
556 := l_btc_id;
557 px_error_tbl (i).validation_id /* RECEIPT_ID*/
558 := l_rct_id;
559 px_error_tbl (i).result_code /* ERROR */
560 := 'ERROR';
561 fnd_msg_pub.get
562 (p_msg_index => l_counter,
563 p_encoded => 'F',
564 p_data => px_error_tbl (i).validation_text,
565 p_msg_index_out => l_msg_index_out
566 );
567 END LOOP;
568 END IF;
569 l_rcpt_status_code := 'FAILED';
570 l_trx_status_code := 'ERROR';
571 EXIT;
572
573 END IF;
574
575 IF (l_return_status = okl_api.g_ret_sts_error) THEN
576 /* okc_api.set_message (p_app_name => g_app_name,
577 p_msg_name => 'OKL_BPD_CASH_APP_FAIL',
578 p_token1 => 'CUSTOMER_NUM',
579 p_token1_value => l_cust_num,
580 p_token2 => 'CONS_BILL_NUM',
581 p_token2_value => l_cnr_id,
582 p_token3 => 'CONTRACT_NUM',
583 p_token3_value => l_khr_id
584 );*/
585 l_error := 'E';
586 l_rcpt_status_code := 'FAILED';
587 l_trx_status_code := 'ERROR';
588
589 IF (fnd_msg_pub.count_msg > 0) THEN
590 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
591 LOOP
592 i := i + 1;
593 px_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
594 := 'RECEIPT_BATCH';
595 px_error_tbl (i).parent_object_id /* BATCH_ID*/
596 := l_btc_id;
597 px_error_tbl (i).validation_id /* RECEIPT_ID*/
598 := l_rct_id;
599 px_error_tbl (i).result_code /* ERROR */
600 := 'ERROR';
601 fnd_msg_pub.get
602 (p_msg_index => l_counter,
603 p_encoded => 'F',
604 p_data => px_error_tbl (i).validation_text,
605 p_msg_index_out => l_msg_index_out
606 );
607 END LOOP;
608 END IF;
609
610 EXIT;
611 END IF;
612
613 -- enter into log file that cash app was sucessful for this batch customer/contract/cons bill
614 -- and update receipt status.
615 /* okc_api.set_message (p_app_name => g_app_name,
616 p_msg_name => 'OKL_BPD_CASH_APP_SUCC',
617 p_token1 => 'CUSTOMER_NUM',
618 p_token1_value => l_cust_num,
619 p_token2 => 'CONS_BILL_NUM',
620 p_token2_value => l_cnr_id,
621 p_token3 => 'CONTRACT_NUM',
622 p_token3_value => l_khr_id
623 );*/
624 l_rcpt_status_code := 'PROCESSED';
625 EXIT;
626 END LOOP; -- end only one receipt record
627
628 -- update transaction receipt status ...
629 UPDATE okl_trx_csh_receipt_b
630 SET rcpt_status_code = l_rcpt_status_code
631 WHERE ID = l_rct_id;
632
633 -- Update Ar receipt Id , if it successfully created
634 IF (l_return_status = okl_api.g_ret_sts_success) THEN
635 UPDATE okl_trx_csh_receipt_b
636 SET ID = l_cash_receipt_id
637 WHERE ID = l_rct_id;
638
639 UPDATE okl_trx_csh_receipt_tl
640 SET ID = l_cash_receipt_id
641 WHERE ID = l_rct_id;
642
643 UPDATE okl_txl_rcpt_apps_b
644 SET rct_id_details = l_cash_receipt_id
645 WHERE rct_id_details = l_rct_id;
646 END IF;
647 END LOOP; -- end looping receipts
648 CLOSE c_get_batch_receipts;
649 IF l_trx_status_code = 'ERROR' THEN
650 ROLLBACK TO process_batch_pvt;
651 END IF;
652 x_return_status := OKL_API.G_RET_STS_SUCCESS;
653 x_trx_status_code := l_trx_status_code;
654 EXCEPTION
655 WHEN OTHERS THEN
656 ROLLBACK TO process_batch_pvt;
657 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
658 x_trx_status_code := 'ERROR';
659 END process_batch;
660
661 ---------------------------------------------------------------------------
662 -- PROCEDURE int_ext_csh_app
663 ---------------------------------------------------------------------------
664 PROCEDURE int_ext_csh_app (
665 p_api_version IN NUMBER,
666 p_init_msg_list IN VARCHAR2,
667 x_return_status OUT NOCOPY VARCHAR2,
668 x_msg_count OUT NOCOPY NUMBER,
669 x_msg_data OUT NOCOPY VARCHAR2
670 ) IS
671 ------------------------------
672 -- DECLARE Local variables
673 ------------------------------
674 l_api_version NUMBER DEFAULT 1.0;
675 l_init_msg_list VARCHAR2 (1);
676 l_return_status VARCHAR2 (1);
677 l_msg_count NUMBER;
678 l_msg_data VARCHAR2 (2000);
679 l_trx_status_code okl_trx_csh_batch_v.trx_status_code%TYPE;
680 l_rcpt_status_code okl_trx_csh_receipt_v.rcpt_status_code%TYPE;
681 l_btc_id okl_trx_csh_batch_v.ID%TYPE DEFAULT NULL;
682 l_btc_name okl_trx_csh_batch_v.NAME%TYPE DEFAULT NULL;
683 l_rct_id okl_trx_csh_receipt_v.ID%TYPE DEFAULT NULL;
684 l_rca_id okl_txl_rcpt_apps_v.ID%TYPE DEFAULT NULL;
685 l_ile_id okl_trx_csh_receipt_v.ile_id%TYPE
686 DEFAULT NULL;
687 l_irm_id okl_trx_csh_receipt_v.irm_id%TYPE
688 DEFAULT NULL;
689 l_check_number okl_trx_csh_receipt_v.check_number%TYPE
690 DEFAULT NULL;
691 l_currency_code okl_trx_csh_receipt_v.currency_code%TYPE
692 DEFAULT NULL;
693 l_currency_conv_type okl_trx_csh_receipt_v.exchange_rate_type%TYPE
694 DEFAULT NULL;
695 l_currency_conv_date okl_trx_csh_receipt_v.exchange_rate_date%TYPE
696 DEFAULT NULL;
697 l_currency_conv_rate okl_trx_csh_receipt_v.exchange_rate%TYPE
698 DEFAULT NULL;
699 l_amount okl_trx_csh_receipt_v.amount%TYPE
700 DEFAULT NULL;
701 l_date_effective okl_trx_csh_receipt_v.date_effective%TYPE
702 DEFAULT NULL;
703 l_gl_date okl_trx_csh_receipt_v.gl_date%TYPE
704 DEFAULT NULL;
705 l_cnr_id okl_txl_rcpt_apps_v.cnr_id%TYPE DEFAULT NULL;
706 l_khr_id okl_txl_rcpt_apps_v.khr_id%TYPE DEFAULT NULL;
707 l_ar_inv_id okl_txl_rcpt_apps_v.ar_invoice_id%TYPE
708 DEFAULT NULL;
709 l_cash_receipt_id ar_cash_receipts_all.cash_receipt_id%TYPE;
710 l_org_id okl_txl_rcpt_apps_v.org_id%TYPE DEFAULT NULL;
711 l_cust_num ar_cash_receipts_all.pay_from_customer%TYPE
712 DEFAULT NULL;
713 l_remit_bank_id NUMBER;
714 l_curr_con_type okl_trx_csh_batch_v.currency_conversion_type%TYPE
715 DEFAULT NULL;
716 l_curr_con_rate okl_trx_csh_batch_v.currency_conversion_rate%TYPE
717 DEFAULT NULL;
718 l_curr_con_date okl_trx_csh_batch_v.currency_conversion_date%TYPE
719 DEFAULT NULL;
720 l_customer_num ar_cash_receipts_all.pay_from_customer%TYPE
721 DEFAULT NULL;
722 l_cons_bill_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE
723 DEFAULT NULL;
724 l_cons_bill_num_log okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE
725 DEFAULT NULL;
726 l_contract_num okc_k_headers_v.contract_number%TYPE
727 DEFAULT NULL;
728 l_contract_num_log okc_k_headers_v.contract_number%TYPE
729 DEFAULT NULL;
730 l_comments okl_trx_csh_receipt_tl.description%TYPE
731 DEFAULT NULL;
732 l_amount_due_remaining ar_payment_schedules_all.amount_due_remaining%TYPE
733 DEFAULT NULL;
734 l_bank_account_id okl_trx_csh_receipt_v.iba_id%TYPE
735 DEFAULT NULL;
736 l_tolerance okl_cash_allctn_rls.amount_tolerance_percent%TYPE;
737 l_days_past_quote_valid okl_cash_allctn_rls.days_past_quote_valid_toleranc%TYPE;
738 l_months_to_bill_ahead okl_cash_allctn_rls.months_to_bill_ahead%TYPE;
739 l_under_payment okl_cash_allctn_rls.under_payment_allocation_code%TYPE;
740 l_over_payment okl_cash_allctn_rls.over_payment_allocation_code%TYPE;
741 l_receipt_msmtch okl_cash_allctn_rls.receipt_msmtch_allocation_code%TYPE;
742 l_attribute_category okl_trx_csh_receipt_v.attribute_category%TYPE
743 DEFAULT NULL;
744 l_attribute1 okl_trx_csh_receipt_v.attribute1%TYPE
745 DEFAULT NULL;
746 l_attribute2 okl_trx_csh_receipt_v.attribute2%TYPE
747 DEFAULT NULL;
748 l_attribute3 okl_trx_csh_receipt_v.attribute3%TYPE
749 DEFAULT NULL;
750 l_attribute4 okl_trx_csh_receipt_v.attribute4%TYPE
751 DEFAULT NULL;
752 l_attribute5 okl_trx_csh_receipt_v.attribute5%TYPE
753 DEFAULT NULL;
754 l_attribute6 okl_trx_csh_receipt_v.attribute6%TYPE
755 DEFAULT NULL;
756 l_attribute7 okl_trx_csh_receipt_v.attribute7%TYPE
757 DEFAULT NULL;
758 l_attribute8 okl_trx_csh_receipt_v.attribute8%TYPE
759 DEFAULT NULL;
760 l_attribute9 okl_trx_csh_receipt_v.attribute9%TYPE
761 DEFAULT NULL;
762 l_attribute10 okl_trx_csh_receipt_v.attribute10%TYPE
763 DEFAULT NULL;
764 l_attribute11 okl_trx_csh_receipt_v.attribute11%TYPE
765 DEFAULT NULL;
766 l_attribute12 okl_trx_csh_receipt_v.attribute12%TYPE
767 DEFAULT NULL;
768 l_attribute13 okl_trx_csh_receipt_v.attribute13%TYPE
769 DEFAULT NULL;
770 l_attribute14 okl_trx_csh_receipt_v.attribute14%TYPE
771 DEFAULT NULL;
772 l_attribute15 okl_trx_csh_receipt_v.attribute15%TYPE
773 DEFAULT NULL;
774 l_inv_tot NUMBER DEFAULT 0;
775 l_error VARCHAR2 (2) DEFAULT NULL;
776 l_create_receipt_flag VARCHAR2 (2) DEFAULT 'YC';
777 -- indicates create ar receipt and concurrent
778 -- process for cash application routine.
779 ------------------------------
780 -- DECLARE Record/Table Types
781 ------------------------------
782 l_btcv_rec okl_btc_pvt.btcv_rec_type;
783 l_btcv_tbl okl_btc_pvt.btcv_tbl_type;
784 x_btcv_rec okl_btc_pvt.btcv_rec_type;
785 x_btcv_tbl okl_btc_pvt.btcv_tbl_type;
786 l_rctv_rec okl_rct_pvt.rctv_rec_type;
787 l_rctv_tbl okl_rct_pvt.rctv_tbl_type;
788 x_rctv_rec okl_rct_pvt.rctv_rec_type;
789 x_rctv_tbl okl_rct_pvt.rctv_tbl_type;
790 l_rcav_rec okl_rca_pvt.rcav_rec_type;
791 l_rcav_tbl okl_rca_pvt.rcav_tbl_type;
792 x_rcav_rec okl_rca_pvt.rcav_rec_type;
793 x_rcav_tbl okl_rca_pvt.rcav_tbl_type;
794 l_rcpt_rec okl_receipts_pvt.rcpt_rec_type;
795 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
796 --error message table declaration
797 --added by akrangan start
798 i NUMBER := 0;
799 l_error_tbl okl_vlr_pvt.vlrv_tbl_type;
800 l_msg_index_out NUMBER;
801
802 l_counter NUMBER;
803 --added by akrangan end
804 l_old_error_tbl okl_vlr_pvt.vlrv_tbl_type;
805
806 -----------------------------
807 -- DECLARE Exceptions
808 ------------------------------
809
810 ------------------------------
811 -- DECLARE Cursors
812 ------------------------------
813
814 -- get internal payment transaction records that have no external
815 -- These payments are not attached to a batch, meaning the internal
816 -- transaction table was populated directly.
817 CURSOR c_get_int_recs IS
818 SELECT rct.ID,
819 rca.ID,
820 rct.ile_id,
821 rct.irm_id,
822 rct.check_number,
823 rct.currency_code,
824 rct.exchange_rate,
825 rct.exchange_rate_date,
826 rct.exchange_rate_type,
827 rct.amount,
828 rct.date_effective,
829 rca.cnr_id,
830 rca.khr_id,
831 rca.ar_invoice_id,
832 rca.org_id,
833 rct.gl_date,
834 rct.date_effective
835 FROM okl_trx_csh_receipt_v rct, okl_txl_rcpt_apps_v rca
836 WHERE rct.ID = rca.rct_id_details
837 AND rct.btc_id IS NULL
838 AND rct.btc_id = -1; --to be reviewed and tested later
839
840 -- sosharma changed
841
842 ----------
843
844 -- retrieve all batches at status 'SUBMITTED'
845 CURSOR c_get_batches IS
846 SELECT btc.ID,
847 btc.currency_conversion_type,
848 btc.currency_conversion_rate,
849 btc.currency_conversion_date
850 FROM okl_trx_csh_batch_v btc
851 WHERE btc.trx_status_code IN ('SUBMITTED', 'RESUBMITTED');
852
853 --akrangan added resubmitted sts chk
854
855 ----------
856
857 -- get redundant batches, i.e. batches with no receipt headers
858 CURSOR c_get_redund_batch IS
859 SELECT btc.ID, btc.NAME
860 FROM okl_trx_csh_batch_v btc
861 WHERE creation_date < (SYSDATE - 7)
862 AND btc.trx_status_code IN
863 ('WORKING', 'RESUBMITTED') --akrangan added
864 AND btc.ID NOT IN (SELECT btc_id
865 FROM okl_trx_csh_receipt_v
866 WHERE btc_id = btc.ID);
867
868 -- get remittance bank
869 CURSOR c_get_rem_bank (
870 c_rcpt_method_id IN NUMBER,
871 c_curr_code IN VARCHAR2
872 ) IS
873 SELECT bank_account_id
874 FROM okl_bpd_rcpt_mthds_uv rcpt
875 WHERE rcpt.currency_code = c_curr_code
876 AND rcpt.receipt_method_id = c_rcpt_method_id;
877
878 -- get customer account number
879 CURSOR c_get_cust_acct_num (c_acct_id IN NUMBER) IS
880 SELECT account_number
881 FROM hz_cust_accounts
882 WHERE cust_account_id = c_acct_id;
883
884 -----------------
885 --cursor for getting old messages
886 CURSOR c_get_previous_errors (p_batch_id IN NUMBER) IS
887 SELECT vb.ID,
888 vb.object_version_number,
889 vb.attribute_category,
890 vb.attribute1,
891 vb.attribute2,
892 vb.attribute3,
893 vb.attribute4,
894 vb.attribute5,
895 vb.attribute6,
896 vb.attribute7,
897 vb.attribute8,
898 vb.attribute9,
899 vb.attribute10,
900 vb.attribute11,
901 vb.attribute12,
902 vb.attribute13,
903 vb.attribute14,
904 vb.attribute15,
905 vb.parent_object_code,
906 vb.parent_object_id,
907 vb.validation_id,
908 vb.result_code,
909 vl.validation_text
910 FROM okl_validation_results_b vb,
911 okl_validation_results_tl vl,
912 okl_trx_csh_batch_v btc
913 WHERE vb.ID = vl.ID
914 AND vl.LANGUAGE = USERENV ('LANG')
915 AND vb.parent_object_code = 'RECEIPT_BATCH'
916 AND vb.parent_object_id = btc.ID
917 AND btc.ID = p_batch_id;
918
919 --AKRANGAN ADDED FOR BATCH RECEIPTS CROSS CURR FUNCTIONALITY BEGIN
920 --CURSOR FOR IDENTIFYING DEBIT DOC CURRENCY
921 CURSOR c_deb_doc_curr( p_debit_doc_id IN NUMBER)
922 IS
923 SELECT CURRENCY_CODE
924 from okc_k_headers_all_b
925 WHERE id = p_debit_doc_id
926 UNION
927 SELECT CURRENCY_CODE
928 from OKL_CNSLD_AR_HDRS_ALL_B
929 WHERE id = p_debit_doc_id
930 UNION
931 SELECT INVOICE_CURRENCY_CODE
932 FROM RA_CUSTOMER_TRX_ALL
933 WHERE CUSTOMER_TRX_ID = p_debit_doc_id;
934 --NEW LOCAL VARIABLES ADDED FOR PROVIDING CROSS CURR FUNCTIONALITY
935 l_receipt_currency VARCHAR2(100);
936 l_invoice_currency_code VARCHAR2(100);
937 l_receipt_date DATE;
938 l_exchange_rate_type VARCHAR2(100);
939 l_conversion_rate NUMBER;
940 l_debit_doc_id NUMBER;
941 --AKRANGAN ADDED FOR BATCH RECEIPTS CROSS CURR FUNCTIONALITY END
942
943 BEGIN
944 --------------------------------------------------------------------
945 -- Start by processing receipts that are not attached to a batch ....
946 --------------------------------------------------------------------
947 OPEN c_get_int_recs;
948
949 LOOP
950 FETCH c_get_int_recs
951 INTO l_rct_id,
952 l_rca_id,
953 l_ile_id,
954 l_irm_id,
955 l_check_number,
956 l_currency_code,
957 l_currency_conv_rate,
958 l_currency_conv_date,
959 l_currency_conv_type,
960 l_amount,
961 l_date_effective,
962 l_cnr_id,
963 l_khr_id,
964 l_ar_inv_id,
965 l_org_id,
966 l_gl_date,
967 l_date_effective;
968
969 IF c_get_int_recs%NOTFOUND THEN
970 -- No Internal Payment Transactions Found
971 /* okc_api.set_message (p_app_name => g_app_name,
972 p_msg_name => 'OKL_BPD_NO_INT_RCPTS'
973 );*/
974 fnd_file.put_line (fnd_file.LOG,
975 'No Internal Payment Transactions Found'
976 );
977 EXIT; -- exit out with nothing to process.
978 END IF;
979
980 LOOP
981 IF l_ile_id IS NULL
982 OR l_check_number IS NULL
983 OR l_currency_code IS NULL
984 OR l_amount IS NULL
985 OR l_amount = 0
986 OR l_irm_id IS NULL
987 OR (l_cnr_id IS NULL AND l_khr_id IS NULL
988 AND l_ar_inv_id IS NULL
989 ) THEN
990 -- Missing mandatory fields for cash application process
991 fnd_file.put_line (fnd_file.LOG,
992 'Some of the mandatory fields are missing.'
993 );
994 fnd_file.put_line (fnd_file.LOG, 'ILE_ID = ' || l_ile_id);
995 fnd_file.put_line (fnd_file.LOG,
996 'CHECK_NUMBER = ' || l_check_number
997 );
998 fnd_file.put_line (fnd_file.LOG,
999 'CURRENCY_CODE = ' || l_currency_code
1000 );
1001 fnd_file.put_line (fnd_file.LOG, 'AMOUNT = ' || l_amount);
1002 fnd_file.put_line (fnd_file.LOG, 'CNR_ID = ' || l_cnr_id);
1003 fnd_file.put_line (fnd_file.LOG, 'KHR_ID = ' || l_khr_id);
1004 fnd_file.put_line (fnd_file.LOG, 'IRM_ID = ' || l_irm_id);
1005 /* okc_api.set_message
1006 (p_app_name => g_app_name,
1007 p_msg_name => 'OKL_BPD_MAND_CASH_APP_FLDS',
1008 p_token1 => 'ILE_ID',
1009 p_token1_value => l_ile_id,
1010 p_token2 => 'CHECK_NUMBER',
1011 p_token2_value => l_check_number,
1012 p_token3 => 'CURRENCY_CODE',
1013 p_token3_value => l_currency_code,
1014 p_token4 => 'AMOUNT',
1015 p_token4_value => l_amount,
1016 p_token5 => 'CNR_ID',
1017 p_token5_value => l_cnr_id,
1018 p_token6 => 'KHR_ID',
1019 p_token6_value => l_khr_id,
1020 p_token7 => 'IRM_ID',
1021 p_token7_value => l_irm_id
1022 );*/
1023 l_error := 'E';
1024 EXIT;
1025 END IF;
1026
1027 -- populate the header and the table records to call Handle receipts method
1028 OPEN c_get_cust_acct_num (l_ile_id);
1029
1030 FETCH c_get_cust_acct_num
1031 INTO l_cust_num;
1032
1033 CLOSE c_get_cust_acct_num;
1034
1035 OPEN c_get_rem_bank (l_irm_id, l_currency_code);
1036
1037 FETCH c_get_rem_bank
1038 INTO l_remit_bank_id;
1039
1040 CLOSE c_get_rem_bank;
1041
1042
1043 --akrangan modification for cross currency begin
1044 --find out debit doc currency
1045 IF l_khr_id IS NOT NULL THEN
1046 l_debit_doc_id := l_khr_id;
1047 ELSIF l_cnr_id IS NOT NULL THEN
1048 l_debit_doc_id := l_cnr_id;
1049 ELSIF l_ar_inv_id IS NOT NULL THEN
1050 l_debit_doc_id := l_khr_id;
1051 END IF;
1052 OPEN c_deb_doc_curr (l_debit_doc_id);
1053 FETCH c_deb_doc_curr
1054 INTO l_invoice_currency_code;
1055 CLOSE c_deb_doc_curr;
1056 l_receipt_currency := l_currency_code ;
1057 --recipt to invoice currency conversion code
1058 IF l_invoice_currency_code <> l_receipt_currency THEN
1059 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1060 IF l_exchange_rate_type IS NULL THEN
1061 OKL_API.set_message( p_app_name => G_APP_NAME
1062 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1063 );
1064 RAISE G_EXCEPTION_HALT_VALIDATION;
1065 ELSE
1066 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
1067 ,l_receipt_currency
1068 ,l_date_effective
1069 ,l_exchange_rate_type
1070 );
1071 IF l_conversion_rate IN (0,-1) THEN
1072 -- Message Text: No exchange rate defined
1073 x_return_status := okl_api.G_RET_STS_ERROR;
1074 okl_api.set_message( p_app_name => G_APP_NAME,
1075 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1076 RAISE G_EXCEPTION_HALT_VALIDATION;
1077 END IF;
1078 END IF;
1079 l_inv_tot := l_amount * l_conversion_rate;
1080 END IF;
1081 l_rcpt_rec.cash_receipt_id := NULL;
1082 l_rcpt_rec.amount := l_amount ;
1083 l_rcpt_rec.currency_code := l_currency_code;
1084 l_rcpt_rec.customer_number := l_cust_num; --cust acct number
1085 -- l_rcpt_rec.CUSTOMER_ID := l_ile_id; --cust acct id -- Commented for Regression in Customer Bank Account
1086 l_rcpt_rec.receipt_number := l_check_number;
1087 l_rcpt_rec.receipt_date := l_date_effective;
1088 l_rcpt_rec.exchange_rate_type := l_currency_conv_type;
1089 l_rcpt_rec.exchange_rate := l_currency_conv_rate;
1090 l_rcpt_rec.exchange_date := l_currency_conv_date;
1091 l_rcpt_rec.remittance_bank_account_id := l_remit_bank_id;
1092 l_rcpt_rec.receipt_method_id := l_irm_id;
1093 l_rcpt_rec.org_id := l_org_id;
1094 l_rcpt_rec.gl_date := l_gl_date;
1095 l_rcpt_rec.create_mode := 'UNAPPLIED';
1096 l_rcpt_rec.create_mode := 'UNAPPLIED';
1097 l_rcpt_rec.dff_attribute_category := l_attribute_category;
1098 l_rcpt_rec.dff_attribute1 := l_attribute1;
1099 l_rcpt_rec.dff_attribute2 := l_attribute2;
1100 l_rcpt_rec.dff_attribute3 := l_attribute3;
1101 l_rcpt_rec.dff_attribute4 := l_attribute4;
1102 l_rcpt_rec.dff_attribute5 := l_attribute5;
1103 l_rcpt_rec.dff_attribute6 := l_attribute6;
1104 l_rcpt_rec.dff_attribute7 := l_attribute7;
1105 l_rcpt_rec.dff_attribute8 := l_attribute8;
1106 l_rcpt_rec.dff_attribute9 := l_attribute9;
1107 l_rcpt_rec.dff_attribute10 := l_attribute10;
1108 l_rcpt_rec.dff_attribute11 := l_attribute11;
1109 l_rcpt_rec.dff_attribute12 := l_attribute12;
1110 l_rcpt_rec.dff_attribute13 := l_attribute13;
1111 l_rcpt_rec.dff_attribute14 := l_attribute14;
1112 l_rcpt_rec.dff_attribute15 := l_attribute15;
1113 l_rcpt_rec.customer_bank_account_id := NULL;
1114 -- Included for Customer Bank Account Regression
1115 l_appl_tbl (0).ar_inv_id := l_ar_inv_id;
1116 l_appl_tbl (0).con_inv_id := l_cnr_id;
1117 l_appl_tbl (0).contract_id := l_khr_id;
1118 l_appl_tbl (0).amount_to_apply := l_inv_tot;
1119 l_appl_tbl (0).amount_applied_from := l_amount;
1120 --akrangan modification for cross currency end
1121
1122
1123 -- call handle receipts
1124 okl_receipts_pvt.handle_receipt
1125 (p_api_version => l_api_version,
1126 p_init_msg_list => l_init_msg_list,
1127 x_return_status => l_return_status,
1128 x_msg_count => l_msg_count,
1129 x_msg_data => l_msg_data,
1130 p_rcpt_rec => l_rcpt_rec,
1131 p_appl_tbl => l_appl_tbl,
1132 x_cash_receipt_id => l_cash_receipt_id
1133 );
1134
1135 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1136 /* okc_api.set_message (p_app_name => g_app_name,
1137 p_msg_name => 'OKL_BPD_CASH_APP_FAIL',
1138 p_token1 => 'CUSTOMER_NUM',
1139 p_token1_value => l_cust_num,
1140 p_token2 => 'CONS_BILL_NUM',
1141 p_token2_value => l_cnr_id,
1142 p_token3 => 'CONTRACT_NUM',
1143 p_token3_value => l_khr_id
1144 );*/
1145 l_error := 'E';
1146 EXIT;
1147 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1148 /* okc_api.set_message (p_app_name => g_app_name,
1149 p_msg_name => 'OKL_BPD_CASH_APP_FAIL',
1150 p_token1 => 'CUSTOMER_NUM',
1151 p_token1_value => l_cust_num,
1152 p_token2 => 'CONS_BILL_NUM',
1153 p_token2_value => l_cnr_id,
1154 p_token3 => 'CONTRACT_NUM',
1155 p_token3_value => l_khr_id
1156 );*/
1157 l_error := 'E';
1158 EXIT;
1159 END IF;
1160
1161 -- enter into log file that cash app was sucessful for this customer/contract/cons bill
1162 /* okc_api.set_message (p_app_name => g_app_name,
1163 p_msg_name => 'OKL_BPD_CASH_APP_SUCC',
1164 p_token1 => 'CUSTOMER_NUM',
1165 p_token1_value => l_cust_num,
1166 p_token2 => 'CONS_BILL_NUM',
1167 p_token2_value => l_cnr_id,
1168 p_token3 => 'CONTRACT_NUM',
1169 p_token3_value => l_khr_id
1170 );*/
1171 EXIT;
1172 END LOOP;
1173 END LOOP;
1174
1175 CLOSE c_get_int_recs;
1176
1177 ---------------------------------------
1178 -- End manual receipt creation process .
1179 ---------------------------------------
1180 -------------------------------
1181 -- process 'SUBMITTED' batches.
1182 -------------------------------
1183 OPEN c_get_batches;
1184
1185 LOOP
1186 -- loop through batches
1187 FETCH c_get_batches
1188 INTO l_btc_id,
1189 l_currency_conv_type,
1190 l_currency_conv_rate,
1191 l_currency_conv_date;
1192
1193 IF c_get_batches%NOTFOUND THEN
1194 -- No 'SUBMITTED' batches to process.
1195 okc_api.set_message (p_app_name => g_app_name,
1196 p_msg_name => 'OKL_BPD_NO_BATCH_PRO'
1197 );
1198 EXIT; -- exit loop and close cursor
1199 END IF;
1200
1201 /*
1202 IF l_currency_conv_type = 'User' THEN
1203 l_currency_conv_type := 'USER';
1204 ELSIF l_currency_conv_type = 'Spot' THEN
1205 l_currency_conv_type := 'SPOT';
1206 ELSIF l_currency_conv_type = 'Corporate' THEN
1207 l_currency_conv_type := 'CORPORATE';
1208 END IF;
1209
1210 */
1211 l_trx_status_code := 'PROCESSED'; -- initialize batch status
1212 l_return_status := 'S';
1213
1214 process_batch(p_batch_id => l_btc_id,
1215 px_error_tbl => l_error_tbl,
1216 x_trx_status_code => l_trx_status_code,
1217 x_return_status => l_return_status);
1218 --Step 1
1219 --delete all old messages
1220 l_old_error_tbl.DELETE;
1221
1222 OPEN c_get_previous_errors (l_btc_id);
1223
1224 FETCH c_get_previous_errors
1225 BULK COLLECT INTO l_old_error_tbl;
1226
1227 CLOSE c_get_previous_errors;
1228
1229 IF l_old_error_tbl.COUNT > 0 THEN
1230 -- Call the TAPI to delete all the old errored values
1231 okl_vlr_pvt.delete_row (p_api_version => l_api_version,
1232 p_init_msg_list => l_init_msg_list,
1233 x_return_status => l_return_status,
1234 x_msg_count => l_msg_count,
1235 x_msg_data => l_msg_data,
1236 p_vlrv_tbl => l_old_error_tbl
1237 );
1238
1239 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
1240 RAISE okl_api.g_exception_unexpected_error;
1241 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
1242 RAISE okl_api.g_exception_error;
1243 END IF;
1244 END IF;
1245
1246 l_btcv_rec.ID := l_btc_id;
1247 l_btcv_rec.trx_status_code := l_trx_status_code;
1248 okl_trx_csh_batch_pub.update_trx_csh_batch (l_api_version,
1249 l_init_msg_list,
1250 l_return_status,
1251 l_msg_count,
1252 l_msg_data,
1253 l_btcv_rec,
1254 x_btcv_rec
1255 );
1256
1257 IF l_error_tbl.COUNT > 0 THEN
1258 i := l_error_tbl.LAST;
1259 END IF;
1260 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
1261 OR (l_return_status = okl_api.g_ret_sts_error) THEN
1262 IF (fnd_msg_pub.count_msg > 0) THEN
1263 FOR l_counter IN 1 .. fnd_msg_pub.count_msg
1264 LOOP
1265 i := i + 1;
1266 l_error_tbl (i).parent_object_code /* RECEIPT_BATCH*/
1267 := 'RECEIPT_BATCH';
1268 l_error_tbl (i).parent_object_id /* BATCH_ID*/
1269 := l_btc_id;
1270 l_error_tbl (i).validation_id /* RECEIPT_ID*/
1271 := l_rct_id;
1272 l_error_tbl (i).result_code /* ERROR */
1273 := 'ERROR';
1274 fnd_msg_pub.get (p_msg_index => l_counter,
1275 p_encoded => 'F',
1276 p_data => l_error_tbl (i).validation_text,
1277 p_msg_index_out => l_msg_index_out
1278 );
1279 END LOOP;
1280 END IF;
1281 END IF;
1282 COMMIT;
1283 -- update batch status ...
1284 END LOOP; -- end looping batches
1285
1286 CLOSE c_get_batches;
1287
1288 --------------------------------------
1289 -- End processing 'SUBMITTED' batches.
1290 --------------------------------------
1291
1292 -- While we're here, clear up delinquent batch's i.e. batch's without receipt headers/lines ...
1293 OPEN c_get_redund_batch;
1294
1295 LOOP
1296 FETCH c_get_redund_batch
1297 INTO l_btc_id, l_btc_name;
1298
1299 IF c_get_redund_batch%NOTFOUND THEN
1300 -- No delinquent batches to delete.
1301 okc_api.set_message (p_app_name => g_app_name,
1302 p_msg_name => 'OKL_BPD_NO_BATCH_DEL'
1303 );
1304 EXIT; -- exit loop and close cursor
1305 END IF;
1306
1307 l_btcv_rec.ID := l_btc_id;
1308 okl_trx_csh_batch_pub.delete_trx_csh_batch (l_api_version,
1309 l_init_msg_list,
1310 l_return_status,
1311 l_msg_count,
1312 l_msg_data,
1313 l_btcv_rec
1314 );
1315
1316 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
1317 OR (l_return_status = okl_api.g_ret_sts_error) THEN
1318 -- problems deleting delinquent batches.
1319 okc_api.set_message (p_app_name => g_app_name,
1320 p_msg_name => 'OKL_BPD_BATCH_FAIL_DEL',
1321 p_token1 => 'BATCH_NAME',
1322 p_token1_value => l_btcv_rec.NAME
1323 );
1324 l_error := 'E';
1325 ELSE
1326 -- delinquent batch deleted sucessfully.
1327 okc_api.set_message (p_app_name => g_app_name,
1328 p_msg_name => 'OKL_BPD_BATCH_SUCC',
1329 p_token1 => 'BATCH_NAME',
1330 p_token1_value => l_btcv_rec.NAME
1331 );
1332 END IF;
1333 END LOOP;
1334
1335 CLOSE c_get_redund_batch;
1336
1337 --populate all the stacked messages into the table
1338 IF l_error_tbl.COUNT > 0 THEN
1339 populate_error_messages (p_api_version => l_api_version,
1340 p_init_msg_list => l_init_msg_list,
1341 p_error_tbl => l_error_tbl,
1342 x_return_status => l_return_status,
1343 x_msg_count => l_msg_count,
1344 x_msg_data => l_msg_data
1345 );
1346 END IF;
1347
1348 IF l_error = 'E' THEN
1349 okc_api.set_message (p_app_name => g_app_name,
1350 p_msg_name => 'OKL_CONTRACTS_UNEXPECTED_ERROR'
1351 );
1352 ELSE
1353 okc_api.set_message (p_app_name => g_app_name,
1354 p_msg_name => 'OKL_CONFIRM_PROCESS'
1355 );
1356 END IF;
1357
1358 x_return_status := l_return_status;
1359 x_msg_count := l_msg_count;
1360 x_msg_data := l_msg_data;
1361 EXCEPTION
1362 WHEN OTHERS THEN
1363 x_return_status := okl_api.g_ret_sts_unexp_error;
1364 x_msg_count := l_msg_count;
1365 x_msg_data := l_msg_data;
1366 END int_ext_csh_app;
1367 END okl_int_ext_csh_app_pvt;