[Home] [Help]
PACKAGE BODY: APPS.OKL_BTCH_CASH_APPLIC
Source
1 PACKAGE BODY okl_btch_cash_applic AS
2 /* $Header: OKLRBAPB.pls 120.12 2008/01/14 14:26:45 akrangan noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 l_module VARCHAR2(40) := 'LEASE.RECEIVABLES.SETUP';
5 l_debug_enabled CONSTANT VARCHAR2(10) := okl_debug_pub.check_log_enabled;
6 l_level_procedure NUMBER;
7 is_debug_procedure_on BOOLEAN;
8
9 -- End of wraper code generated automatically by Debug code generator
10
11 -- Start of comments
12 --
13 -- Function Name : validate_batch_lines
14 -- Description : validate_batch_lines
15 -- Business Rules :
16 -- Parameters :
17 -- Version : 1.0
18 -- History : AKRANGAN created.
19 --
20 -- End of comments
21
22 PROCEDURE validate_batch_lines(p_api_version IN NUMBER,
23 p_init_msg_list IN VARCHAR2,
24 p_batch_tbl IN okl_btch_dtls_tbl_type,
25 p_batch_exists IN VARCHAR2,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2) IS
29 --local variables declaration
30 l_api_name CONSTANT VARCHAR2(30) := 'validate_batch_lines';
31 l_api_version CONSTANT NUMBER := p_api_version;
32 l_return_status VARCHAR2(1);
33 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
34 l_msg_count NUMBER;
35 l_msg_data VARCHAR2(2000);
36 l_batch_tbl okl_btch_dtls_tbl_type := p_batch_tbl;
37 l_batch_exists VARCHAR2(1) := p_batch_exists;
38 l_counter NUMBER;
39 i NUMBER;
40 l_no_stor_rcpts VARCHAR2(1) := 'Y';
41 l_submitted_batch_total NUMBER := 0;
42 l_check_exists VARCHAR2(90) DEFAULT NULL;
43
44 --local cursors declaration
45 -- get batch irm_id
46 CURSOR c_get_btc_irm_id(cp_btc_id IN VARCHAR2) IS
47 SELECT irm_id,
48 batch_total,
49 date_gl_requested
50 FROM okl_trx_csh_batch_v a
51 WHERE a.id = cp_btc_id;
52
53 -- get org_id for contract
54 CURSOR c_get_org_id(cp_contract_num IN VARCHAR2) IS
55 SELECT authoring_org_id
56 FROM okc_k_headers_b
57 WHERE contract_number = cp_contract_num;
58
59 -- get new batch details
60 CURSOR c_get_btc_dtls(cp_btc_id IN VARCHAR2) IS
61 SELECT date_gl_requested,
62 a.irm_id,
63 'x',
64 trx_status_code
65 FROM okl_trx_csh_batch_v a,
66 okl_trx_csh_receipt_v b
67 WHERE a.id = cp_btc_id
68 AND a.id = b.btc_id
69 AND rownum = 1;
70
71 -- check for unique check number
72 CURSOR c_unique_check(cp_check_number IN VARCHAR2, cp_amount IN NUMBER, cp_customer_id IN NUMBER, cp_receipt_date IN DATE) IS
73 SELECT check_number
74 FROM okl_trx_csh_receipt_v a
75 WHERE a.check_number = cp_check_number
76 AND a.amount = cp_amount
77 AND a.ile_id = cp_customer_id
78 AND a.date_effective = trunc(cp_receipt_date);
79
80 --start code by pgomes on 03/05/2003
81 CURSOR l_khr_curr_csr(cp_khr_id IN NUMBER) IS
82 SELECT currency_code FROM okl_k_headers_full_v WHERE id = cp_khr_id;
83
84 CURSOR l_inv_curr_csr(cp_consolidated_invoice_id IN NUMBER) IS
85 SELECT currency_code
86 FROM okl_cnsld_ar_hdrs_b
87 WHERE id = cp_consolidated_invoice_id;
88
89 l_temp_currency_code okl_k_headers_full_v.currency_code%TYPE;
90
91 --end code by pgomes on 03/05/2003
92 CURSOR l_ar_inv_curr_csr(cp_invoice_id IN NUMBER) IS
93 SELECT invoice_currency_code
94 FROM ra_customer_trx_all
95 WHERE customer_trx_id = cp_invoice_id;
96 BEGIN
97 l_msg_count := 0;
98 -- Initialize API return status to success
99 l_return_status := okl_api.start_activity(l_api_name,
100 g_pkg_name,
101 l_init_msg_list,
102 l_api_version,
103 l_api_version,
104 '_PVT',
105 l_return_status);
106
107 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
108 THEN
109 RAISE okl_api.g_exception_unexpected_error;
110 ELSIF (l_return_status = okl_api.g_ret_sts_error)
111 THEN
112 RAISE okl_api.g_exception_error;
113 END IF;
114
115 l_counter := 0;
116 i := l_batch_tbl.FIRST;
117
118 LOOP
119 -- check for missing columns
120 IF l_batch_tbl(i).customer_number IS NULL AND
121 l_batch_tbl(i).check_number IS NULL AND
122 l_batch_tbl(i).amount IS NULL AND
123 l_batch_tbl.COUNT = 1
124 THEN
125 l_return_status := okl_api.g_ret_sts_error;
126 EXIT;
127 ELSIF l_batch_tbl(i).customer_number IS NULL AND
128 l_batch_tbl(i).check_number IS NULL AND
129 l_batch_tbl(i).amount IS NULL AND
130 l_batch_tbl.COUNT > 1
131 THEN
132 NULL; -- means we have empty lines in a table
133 ELSIF l_batch_tbl(i).customer_number IS NULL OR
134 l_batch_tbl(i).check_number IS NULL OR
135 l_batch_tbl(i).amount IS NULL
136 THEN
137 -- Message Text: Please enter all mandatory fields
138 x_return_status := okl_api.g_ret_sts_error;
139 okl_api.set_message(p_app_name => 'OKL',
140 p_msg_name => 'OKL_BPD_MISSING_FIELDS');
141 RAISE okl_api.g_exception_error;
142 END IF;
143
144 --akrangan cross currency feature modification begin
145 /*
146 --commenting the following code
147 --start code by pgomes on 03/05/2003
148 IF (nvl(l_batch_tbl(i).khr_id, okl_api.g_miss_num) <>
149 okl_api.g_miss_num)
150 THEN
151 OPEN l_khr_curr_csr(l_batch_tbl(i).khr_id);
152
153 FETCH l_khr_curr_csr
154 INTO l_temp_currency_code;
155
156 CLOSE l_khr_curr_csr;
157
158
159 IF (l_temp_currency_code <> l_batch_tbl(i).currency_code)
160 THEN
161 okc_api.set_message(p_app_name => g_app_name,
162 p_msg_name => 'OKL_BPD_BTCH_RCPT_KHR_CURR_ERR');
163 RAISE okl_api.g_exception_error;
164 END IF;
165 ELSIF (nvl(l_batch_tbl(i).consolidated_invoice_id, okl_api.g_miss_num) <>
166 okl_api.g_miss_num)
167 THEN
168 OPEN l_inv_curr_csr(l_batch_tbl(i).consolidated_invoice_id);
169
170 FETCH l_inv_curr_csr
171 INTO l_temp_currency_code;
172
173 CLOSE l_inv_curr_csr;
174
175 IF (l_temp_currency_code <> l_batch_tbl(i).currency_code)
176 THEN
177 okc_api.set_message(p_app_name => g_app_name,
178 p_msg_name => 'OKL_BPD_BTCH_RCPT_INV_CURR_ERR');
179 RAISE okl_api.g_exception_error;
180 END IF;
181 ELSIF (nvl(l_batch_tbl(i).ar_invoice_id, okl_api.g_miss_num) <>
182 okl_api.g_miss_num)
183 THEN
184 OPEN l_ar_inv_curr_csr(l_batch_tbl(i).ar_invoice_id);
185
186 FETCH l_ar_inv_curr_csr
187 INTO l_temp_currency_code;
188
189 CLOSE l_ar_inv_curr_csr;
190
191 IF (l_temp_currency_code <> l_batch_tbl(i).currency_code)
192 THEN
193 okc_api.set_message(p_app_name => g_app_name,
194 p_msg_name => 'OKL_BPD_BTCH_RCPT_INV_CURR_ERR');
195 RAISE g_exception_halt_validation;
196 END IF;
197 END IF;
198 */
199 --akrangan cross currency feature modification end
200 --end code by pgomes on 03/05/2003
201 IF l_batch_tbl(i).amount <= 0
202 THEN
203 -- Message Text: The receipt must not have a value of zero
204 x_return_status := okl_api.g_ret_sts_error;
205 okl_api.set_message(p_app_name => g_app_name,
206 p_msg_name => 'OKL_OP_BTCH_RCT_AMT_POS');
207 RAISE okl_api.g_exception_error;
208 END IF;
209
210 IF l_batch_exists <> 'x'
211 THEN
212 -- creating new batch of receipts
213 OPEN c_unique_check(l_batch_tbl(i).check_number,
214 l_batch_tbl(i).amount,
215 l_batch_tbl(i).ile_id,
216 trunc(l_batch_tbl(i).receipt_date));
217
218 FETCH c_unique_check
219 INTO l_check_exists;
220
221 CLOSE c_unique_check;
222
223 IF l_check_exists IS NOT NULL
224 THEN
225 -- Message Text: Check number already exists for customer.
226 x_return_status := okl_api.g_ret_sts_error;
227 okc_api.set_message(p_app_name => g_app_name,
228 p_msg_name => 'OKL_BPD_CHECK_EXISTS',
229 p_token1 => 'CHECK_NUMBER',
230 p_token1_value => l_check_exists);
231 RAISE okl_api.g_exception_error;
232 END IF;
233 END IF;
234
235 -- count batch lines and add receipts
236 IF l_batch_tbl(i).consolidated_invoice_id IS NOT NULL OR
237 l_batch_tbl(i).khr_id IS NOT NULL OR
238 l_batch_tbl(i).ar_invoice_id IS NOT NULL
239 THEN
240 l_counter := l_counter + 1;
241 l_submitted_batch_total := l_submitted_batch_total + l_batch_tbl(i)
242 .amount;
243 END IF;
244
245 EXIT WHEN(i = l_batch_tbl.LAST);
246 i := i + 1;
247 END LOOP;
248
249 --set output variables
250 x_return_status := l_return_status;
251 x_msg_count := l_msg_count;
252 x_msg_data := l_msg_data;
253 EXCEPTION
254 WHEN okl_api.g_exception_error THEN
255 x_return_status := okl_api.g_ret_sts_unexp_error;
256 x_msg_count := l_msg_count;
257 x_msg_data := l_msg_data;
258 WHEN okl_api.g_exception_unexpected_error THEN
259 x_return_status := okl_api.g_ret_sts_error;
260 x_msg_count := l_msg_count;
261 x_msg_data := l_msg_data;
262 WHEN OTHERS THEN
263 x_return_status := okl_api.g_ret_sts_unexp_error;
264 okl_api.set_message(p_app_name => g_app_name,
265 p_msg_name => 'OKL_DB_ERROR',
266 p_token1 => 'PROG_NAME',
267 p_token1_value => 'validate_batch_lines',
268 p_token2 => 'SQLCODE',
269 p_token2_value => SQLCODE,
270 p_token3 => 'SQLERRM',
271 p_token3_value => SQLERRM);
272 x_msg_count := l_msg_count;
273 x_msg_data := l_msg_data;
274 END validate_batch_lines;
275
276 -- Start of comments
277 --
278 -- Function Name : update_batch_lines
279 -- Description : update_batch_lines procedure processes
280 -- existing batch lines and updates
281 -- Business Rules :
282 -- Parameters :
283 -- Version : 1.0
284 -- History : AKRANGAN created.
285 --
286 -- End of comments
287
288 PROCEDURE update_batch_lines(p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2,
290 p_batch_exists IN VARCHAR2,
291 p_batch_tbl IN okl_btch_dtls_tbl_type,
292 x_return_status OUT NOCOPY VARCHAR2,
293 x_msg_count OUT NOCOPY NUMBER,
294 x_msg_data OUT NOCOPY VARCHAR2) IS
295 --local variables declaration
296 l_api_name CONSTANT VARCHAR2(30) := 'update_batch_lines';
297 l_api_version CONSTANT NUMBER := 1.0;
298 l_return_status VARCHAR2(1);
299 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
300 l_msg_count NUMBER;
301 l_msg_data VARCHAR2(2000);
302 l_batch_tbl okl_btch_dtls_tbl_type := p_batch_tbl;
303 lx_batch_tbl okl_btch_dtls_tbl_type;
304 l_rctv_rec okl_rct_pvt.rctv_rec_type;
305 l_rcav_tbl okl_rca_pvt.rcav_tbl_type;
306 lx_rctv_rec okl_rct_pvt.rctv_rec_type;
307 lx_rcav_tbl okl_rca_pvt.rcav_tbl_type;
308 l_date_gl_requested DATE DEFAULT NULL;
309 l_irm_id NUMBER DEFAULT NULL;
310 l_consolidated_invoice_number VARCHAR2(90) DEFAULT NULL;
311 l_contract_number VARCHAR2(120) DEFAULT NULL;
312 l_customer_number VARCHAR2(90) DEFAULT NULL;
313 l_org_id NUMBER := mo_global.get_current_org_id();
314
315 --local cursors defined here
316 --cursor to get batch level details from db
317 CURSOR c_get_btc_dtls(cp_btc_id IN VARCHAR2) IS
318 SELECT date_gl_requested,
319 a.irm_id
320 FROM okl_trx_csh_batch_v a,
321 okl_trx_csh_receipt_v b
322 WHERE a.id = cp_btc_id
323 AND a.id = b.btc_id
324 AND rownum = 1;
325 --cursor to get receipt application id
326 CURSOR c_get_receipt_appln_id(p_receipt_id IN NUMBER) IS
327 SELECT id
328 FROM OKL_TXL_RCPT_APPS_B
329 WHERE rct_id_details = p_receipt_id;
330
331 BEGIN
332 l_msg_count := 0;
333 -- Initialize API return status to success
334 l_return_status := okl_api.start_activity(l_api_name,
335 g_pkg_name,
336 l_init_msg_list,
337 l_api_version,
338 l_api_version,
339 '_PVT',
340 l_return_status);
341
342 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
343 THEN
344 RAISE okl_api.g_exception_unexpected_error;
345 ELSIF (l_return_status = okl_api.g_ret_sts_error)
346 THEN
347 RAISE okl_api.g_exception_error;
348 END IF;
349
350 --Step 1
351 --Validate the Batch Line
352 --call validate_batch_lines API
353 validate_batch_lines(p_api_version => l_api_version,
354 p_init_msg_list => l_init_msg_list,
355 p_batch_exists => p_batch_exists,
356 p_batch_tbl => l_batch_tbl,
357 x_return_status => l_return_status,
358 x_msg_count => l_msg_count,
359 x_msg_data => l_msg_data);
360
361 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
362 THEN
363 RAISE okl_api.g_exception_unexpected_error;
364 ELSIF (l_return_status = okl_api.g_ret_sts_error)
365 THEN
366 RAISE okl_api.g_exception_error;
367 END IF;
368
369 --Step 2
370 --Prepare Receipt Rec and Table
371 OPEN c_get_btc_dtls(l_batch_tbl(1).btc_id);
372
373 FETCH c_get_btc_dtls
374 INTO l_date_gl_requested, l_irm_id;
375
376 CLOSE c_get_btc_dtls;
377
378 -- i := l_batch_tbl.FIRST;
379 FOR i IN l_batch_tbl.FIRST .. l_batch_tbl.LAST
380 LOOP
381 l_consolidated_invoice_number := l_batch_tbl(i)
382 .consolidated_invoice_number;
383 l_contract_number := l_batch_tbl(i).contract_number;
384 l_customer_number := l_batch_tbl(i).customer_number;
385 -- Update Record in Internal Transaction Table.
386
387 -- Prepare HEADER REC AND ONE LINE RECORD
388 l_rctv_rec.id := l_batch_tbl(i).id;
389 l_rctv_rec.btc_id := l_batch_tbl(i).btc_id;
390 l_rctv_rec.irm_id := l_irm_id;
391 l_rctv_rec.ile_id := l_batch_tbl(i).ile_id;
392 l_rctv_rec.check_number := l_batch_tbl(i).check_number;
393 l_rctv_rec.amount := l_batch_tbl(i).amount;
394 l_rctv_rec.currency_code := l_batch_tbl(i).currency_code;
395 l_rctv_rec.gl_date := l_date_gl_requested;
396 l_rctv_rec.date_effective := l_batch_tbl(i).receipt_date;
397 l_rctv_rec.org_id := l_org_id;
398 l_rctv_rec.rcpt_status_code := 'SUBMITTED';
399 -- passing DFF attributes to receipt record
400 l_rctv_rec.attribute_category := l_batch_tbl(i).dff_attribute_category;
401 l_rctv_rec.attribute1 := l_batch_tbl(i).dff_attribute1;
402 l_rctv_rec.attribute2 := l_batch_tbl(i).dff_attribute2;
403 l_rctv_rec.attribute3 := l_batch_tbl(i).dff_attribute3;
404 l_rctv_rec.attribute4 := l_batch_tbl(i).dff_attribute4;
405 l_rctv_rec.attribute5 := l_batch_tbl(i).dff_attribute5;
406 l_rctv_rec.attribute6 := l_batch_tbl(i).dff_attribute6;
407 l_rctv_rec.attribute7 := l_batch_tbl(i).dff_attribute7;
408 l_rctv_rec.attribute8 := l_batch_tbl(i).dff_attribute8;
409 l_rctv_rec.attribute9 := l_batch_tbl(i).dff_attribute9;
410 l_rctv_rec.attribute10 := l_batch_tbl(i).dff_attribute10;
411 l_rctv_rec.attribute11 := l_batch_tbl(i).dff_attribute11;
412 l_rctv_rec.attribute12 := l_batch_tbl(i).dff_attribute12;
413 l_rctv_rec.attribute13 := l_batch_tbl(i).dff_attribute13;
414 l_rctv_rec.attribute14 := l_batch_tbl(i).dff_attribute14;
415 l_rctv_rec.attribute15 := l_batch_tbl(i).dff_attribute15;
416 OPEN c_get_receipt_appln_id(l_batch_tbl(i).id);
417 FETCH c_get_receipt_appln_id INTO l_rcav_tbl(1).ID;
418 CLOSE c_get_receipt_appln_id;
419 l_rcav_tbl(1).rct_id_details := l_batch_tbl(i).id;
420 l_rcav_tbl(1).cnr_id := l_batch_tbl(i).consolidated_invoice_id;
421 l_rcav_tbl(1).ar_invoice_id := l_batch_tbl(i).ar_invoice_id;
422 l_rcav_tbl(1).khr_id := l_batch_tbl(i).khr_id;
423 l_rcav_tbl(1).ile_id := l_batch_tbl(i).ile_id;
424 l_rcav_tbl(1).amount := l_batch_tbl(i).amount;
425 l_rcav_tbl(1).line_number := 1;
426 l_rcav_tbl(1).org_id := l_org_id;
427
428 -- Start of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
429 IF (is_debug_procedure_on)
430 THEN
431 BEGIN
432 okl_debug_pub.log_debug(l_level_procedure,
433 l_module,
434 'Begin Debug OKLRBAPB.pls call Okl_Rct_Pub.update_internal_trans ');
435 END;
436 END IF;
437
438 --Step 3
439 --Update the Receipt and Receipt Appln Line
440 --Call the Update API
441 okl_rct_pub.update_internal_trans(l_api_version,
442 l_init_msg_list,
443 l_return_status,
444 l_msg_count,
445 l_msg_data,
446 l_rctv_rec,
447 l_rcav_tbl,
448 lx_rctv_rec,
449 lx_rcav_tbl);
450
451 IF (is_debug_procedure_on)
452 THEN
453 BEGIN
454 okl_debug_pub.log_debug(l_level_procedure,
455 l_module,
456 'End Debug OKLRBAPB.pls call Okl_Rct_Pub.create_internal_trans ');
457 END;
458 END IF;
459
460 -- End of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
461 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
462 THEN
463 RAISE okl_api.g_exception_unexpected_error;
464 ELSIF (l_return_status = okl_api.g_ret_sts_error)
465 THEN
466 RAISE okl_api.g_exception_error;
467 END IF;
468
469 -- EXIT WHEN (i = l_batch_tbl.LAST);
470 --i := i + 1;
471 END LOOP;
472
473 --set output variables
474 x_return_status := l_return_status;
475 x_msg_count := l_msg_count;
476 x_msg_data := l_msg_data;
477 EXCEPTION
478 WHEN okl_api.g_exception_error THEN
479 x_return_status := okl_api.g_ret_sts_unexp_error;
480 x_msg_count := l_msg_count;
481 x_msg_data := l_msg_data;
482 WHEN okl_api.g_exception_unexpected_error THEN
483 x_return_status := okl_api.g_ret_sts_error;
484 x_msg_count := l_msg_count;
485 x_msg_data := l_msg_data;
486 WHEN OTHERS THEN
487 x_return_status := okl_api.g_ret_sts_unexp_error;
488 okl_api.set_message(p_app_name => 'OKL',
489 p_msg_name => 'OKL_DB_ERROR',
490 p_token1 => 'PROG_NAME',
491 p_token1_value => 'update_batch_lines',
492 p_token2 => 'SQLCODE',
493 p_token2_value => SQLCODE,
494 p_token3 => 'SQLERRM',
495 p_token3_value => SQLERRM);
496 x_msg_count := l_msg_count;
497 x_msg_data := l_msg_data;
498 END update_batch_lines;
499
500 -- Start of comments
501 --
502 -- Function Name : insert_batch_lines
503 -- Description : insert_batch_lines procedure inserts
504 -- new batch lines
505 -- Business Rules :
506 -- Parameters :
507 -- Version : 1.0
508 -- History : AKRANGAN created.
509 --
510 -- End of comments
511
512 PROCEDURE insert_batch_lines(p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2,
514 p_batch_exists IN VARCHAR2,
515 p_batch_tbl IN okl_btch_dtls_tbl_type,
516 x_return_status OUT NOCOPY VARCHAR2,
517 x_msg_count OUT NOCOPY NUMBER,
518 x_msg_data OUT NOCOPY VARCHAR2) IS
519 --local variables declaration
520 l_api_name CONSTANT VARCHAR2(30) := 'insert_batch_lines';
521 l_api_version CONSTANT NUMBER := 1.0;
522 l_return_status VARCHAR2(1);
523 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
524 l_msg_count NUMBER;
525 l_msg_data VARCHAR2(2000);
526 l_batch_tbl okl_btch_dtls_tbl_type := p_batch_tbl;
527 lx_batch_tbl okl_btch_dtls_tbl_type;
528 l_rctv_rec okl_rct_pvt.rctv_rec_type;
529 l_rcav_tbl okl_rca_pvt.rcav_tbl_type;
530 lx_rctv_rec okl_rct_pvt.rctv_rec_type;
531 lx_rcav_tbl okl_rca_pvt.rcav_tbl_type;
532 l_date_gl_requested DATE DEFAULT NULL;
533 l_irm_id NUMBER DEFAULT NULL;
534 l_consolidated_invoice_number VARCHAR2(90) DEFAULT NULL;
535 l_contract_number VARCHAR2(120) DEFAULT NULL;
536 l_customer_number VARCHAR2(90) DEFAULT NULL;
537 l_org_id NUMBER := mo_global.get_current_org_id
538 ();
539
540 --local cursors defined here
541 --cursor to get batch level details from db
542 CURSOR c_get_btc_dtls(cp_btc_id IN VARCHAR2) IS
543 SELECT date_gl_requested,
544 a.irm_id
545 FROM okl_trx_csh_batch_v a,
546 okl_trx_csh_receipt_v b
547 WHERE a.id = cp_btc_id
548 AND a.id = b.btc_id
549 AND rownum = 1;
550 BEGIN
551 l_msg_count := 0;
552 -- Initialize API return status to success
553 l_return_status := okl_api.start_activity(l_api_name,
554 g_pkg_name,
555 l_init_msg_list,
556 l_api_version,
557 l_api_version,
558 '_PVT',
559 l_return_status);
560
561 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
562 THEN
563 RAISE okl_api.g_exception_unexpected_error;
564 ELSIF (l_return_status = okl_api.g_ret_sts_error)
565 THEN
566 RAISE okl_api.g_exception_error;
567 END IF;
568
569 --Step 1
570 --Validate the Batch Line
571 --call validate_batch_lines API
572 validate_batch_lines(p_api_version => l_api_version,
573 p_init_msg_list => l_init_msg_list,
574 p_batch_exists => p_batch_exists,
575 p_batch_tbl => l_batch_tbl,
576 x_return_status => l_return_status,
577 x_msg_count => l_msg_count,
578 x_msg_data => l_msg_data);
579
580 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
581 THEN
582 RAISE okl_api.g_exception_unexpected_error;
583 ELSIF (l_return_status = okl_api.g_ret_sts_error)
584 THEN
585 RAISE okl_api.g_exception_error;
586 END IF;
587
588 --Step 2
589 --Prepare Receipt Rec and Table
590 OPEN c_get_btc_dtls(l_batch_tbl(1).btc_id);
591
592 FETCH c_get_btc_dtls
593 INTO l_date_gl_requested, l_irm_id;
594
595 CLOSE c_get_btc_dtls;
596
597 --i := l_batch_tbl.FIRST;
598 FOR i IN l_batch_tbl.FIRST .. l_batch_tbl.LAST
599 LOOP
600 l_consolidated_invoice_number := l_batch_tbl(i)
601 .consolidated_invoice_number;
602 l_contract_number := l_batch_tbl(i).contract_number;
603 l_customer_number := l_batch_tbl(i).customer_number;
604 -- Update Record in Internal Transaction Table.
605
606 -- Prepare HEADER REC AND ONE LINE RECORD
607 l_rctv_rec.id := l_batch_tbl(i).id;
608 l_rctv_rec.btc_id := l_batch_tbl(i).btc_id;
609 l_rctv_rec.irm_id := l_irm_id;
610 l_rctv_rec.ile_id := l_batch_tbl(i).ile_id;
611 l_rctv_rec.check_number := l_batch_tbl(i).check_number;
612 l_rctv_rec.amount := l_batch_tbl(i).amount;
613 l_rctv_rec.currency_code := l_batch_tbl(i).currency_code;
614 l_rctv_rec.gl_date := l_date_gl_requested;
615 l_rctv_rec.date_effective := l_batch_tbl(i).receipt_date;
616 l_rctv_rec.org_id := l_org_id;
617 l_rctv_rec.rcpt_status_code := 'SUBMITTED';
618 -- passing DFF attributes to receipt record
619 l_rctv_rec.attribute_category := l_batch_tbl(i).dff_attribute_category;
620 l_rctv_rec.attribute1 := l_batch_tbl(i).dff_attribute1;
621 l_rctv_rec.attribute2 := l_batch_tbl(i).dff_attribute2;
622 l_rctv_rec.attribute3 := l_batch_tbl(i).dff_attribute3;
623 l_rctv_rec.attribute4 := l_batch_tbl(i).dff_attribute4;
624 l_rctv_rec.attribute5 := l_batch_tbl(i).dff_attribute5;
625 l_rctv_rec.attribute6 := l_batch_tbl(i).dff_attribute6;
626 l_rctv_rec.attribute7 := l_batch_tbl(i).dff_attribute7;
627 l_rctv_rec.attribute8 := l_batch_tbl(i).dff_attribute8;
628 l_rctv_rec.attribute9 := l_batch_tbl(i).dff_attribute9;
629 l_rctv_rec.attribute10 := l_batch_tbl(i).dff_attribute10;
630 l_rctv_rec.attribute11 := l_batch_tbl(i).dff_attribute11;
631 l_rctv_rec.attribute12 := l_batch_tbl(i).dff_attribute12;
632 l_rctv_rec.attribute13 := l_batch_tbl(i).dff_attribute13;
633 l_rctv_rec.attribute14 := l_batch_tbl(i).dff_attribute14;
634 l_rctv_rec.attribute15 := l_batch_tbl(i).dff_attribute15;
635 l_rcav_tbl(1).rct_id_details := l_batch_tbl(i).id;
636 l_rcav_tbl(1).cnr_id := l_batch_tbl(i).consolidated_invoice_id;
637 l_rcav_tbl(1).ar_invoice_id := l_batch_tbl(i).ar_invoice_id;
638 l_rcav_tbl(1).khr_id := l_batch_tbl(i).khr_id;
639 l_rcav_tbl(1).ile_id := l_batch_tbl(i).ile_id;
640 l_rcav_tbl(1).amount := l_batch_tbl(i).amount;
641 l_rcav_tbl(1).line_number := 1;
642 l_rcav_tbl(1).org_id := l_org_id;
643
644 -- Start of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
645 IF (is_debug_procedure_on)
646 THEN
647 BEGIN
648 okl_debug_pub.log_debug(l_level_procedure,
649 l_module,
650 'Begin Debug OKLRBAPB.pls call Okl_Rct_Pub.update_internal_trans ');
651 END;
652 END IF;
653
654 --Step 3
655 --Insert the Receipt and Receipt Appln Line
656 --Call the Insert API
657 okl_rct_pub.create_internal_trans(l_api_version,
658 l_init_msg_list,
659 l_return_status,
660 l_msg_count,
661 l_msg_data,
662 l_rctv_rec,
663 l_rcav_tbl,
664 lx_rctv_rec,
665 lx_rcav_tbl);
666
667 IF (is_debug_procedure_on)
668 THEN
669 BEGIN
670 okl_debug_pub.log_debug(l_level_procedure,
671 l_module,
672 'End Debug OKLRBAPB.pls call Okl_Rct_Pub.create_internal_trans ');
673 END;
674 END IF;
675
676 -- End of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
677 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
678 THEN
679 RAISE okl_api.g_exception_unexpected_error;
680 ELSIF (l_return_status = okl_api.g_ret_sts_error)
681 THEN
682 RAISE okl_api.g_exception_error;
683 END IF;
684
685 --EXIT WHEN (i = l_batch_tbl.LAST);
686 --i := i + 1;
687 END LOOP;
688
689 --set output variables
690 x_return_status := l_return_status;
691 x_msg_count := l_msg_count;
692 x_msg_data := l_msg_data;
693 EXCEPTION
694 WHEN okl_api.g_exception_error THEN
695 x_return_status := okl_api.g_ret_sts_unexp_error;
696 x_msg_count := l_msg_count;
697 x_msg_data := l_msg_data;
698 WHEN okl_api.g_exception_unexpected_error THEN
699 x_return_status := okl_api.g_ret_sts_error;
700 x_msg_count := l_msg_count;
701 x_msg_data := l_msg_data;
702 WHEN OTHERS THEN
703 x_return_status := okl_api.g_ret_sts_unexp_error;
704 okl_api.set_message(p_app_name => 'OKL',
705 p_msg_name => 'OKL_DB_ERROR',
706 p_token1 => 'PROG_NAME',
707 p_token1_value => 'insert_batch_lines',
708 p_token2 => 'SQLCODE',
709 p_token2_value => SQLCODE,
710 p_token3 => 'SQLERRM',
711 p_token3_value => SQLERRM);
712 x_msg_count := l_msg_count;
713 x_msg_data := l_msg_data;
714 END insert_batch_lines;
715
716 -- Start of comments
717 --
718 -- Function Name : process_batch_lines
719 -- Description : process_batch_lines procedure processes
720 -- existing and new batch lines and updates ,inserts or
721 -- Business Rules :
722 -- Parameters :
723 -- Version : 1.0
724 -- History : AKRANGAN created.
725 --
726 -- End of comments
727
728 PROCEDURE process_batch_lines(p_api_version IN NUMBER,
729 p_init_msg_list IN VARCHAR2,
730 p_batch_exists IN VARCHAR2,
731 p_batch_tbl IN okl_btch_dtls_tbl_type,
732 x_return_status OUT NOCOPY VARCHAR2,
733 x_msg_count OUT NOCOPY NUMBER,
734 x_msg_data OUT NOCOPY VARCHAR2) IS
735 --declare local cursors here
736 --cursor for existing receipts in the batch,
737 CURSOR get_rct_id(cp_btc_id IN NUMBER, cp_receipt_id IN NUMBER) IS
738 SELECT rct.id
739 FROM okl_trx_csh_receipt_b rct
740 WHERE rct.btc_id = cp_btc_id
741 AND rct.id = cp_receipt_id;
742
743 --cursor for existing receipt applications
744 CURSOR get_rca_id(cp_rct_id IN NUMBER) IS
745 SELECT rca.id
746 FROM okl_txl_rcpt_apps_b rca
747 WHERE rca.rct_id_details = cp_rct_id;
748
749 --local variables declaration
750 get_rct_id_rec get_rct_id%ROWTYPE;
751 get_rca_id_rec get_rca_id%ROWTYPE;
752 l_batch_exists VARCHAR2(1) := p_batch_exists ;
753 l_api_name CONSTANT VARCHAR2(30) := 'process_batch_lines';
754 l_api_version CONSTANT NUMBER := 1.0;
755 l_return_status VARCHAR2(1);
756 l_init_msg_list VARCHAR2(1) := p_init_msg_list;
757 l_msg_count NUMBER;
758 l_msg_data VARCHAR2(2000);
759 l_receipt_id NUMBER;
760 l_batch_tbl okl_btch_dtls_tbl_type := p_batch_tbl;
761 i NUMBER;
762 j NUMBER;
763 k NUMBER;
764 m NUMBER := 0;
765 n NUMBER := 0;
766 l_del_batch_tbl okl_btch_dtls_tbl_type;
767 l_ins_batch_tbl okl_btch_dtls_tbl_type;
768 l_upd_batch_tbl okl_btch_dtls_tbl_type;
769 BEGIN
770 l_msg_count := 0;
771 -- Initialize API return status to success
772 l_return_status := okl_api.start_activity(l_api_name,
773 g_pkg_name,
774 l_init_msg_list,
775 l_api_version,
776 l_api_version,
777 '_PVT',
778 l_return_status);
779
780 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
781 THEN
782 RAISE okl_api.g_exception_unexpected_error;
783 ELSIF (l_return_status = okl_api.g_ret_sts_error)
784 THEN
785 RAISE okl_api.g_exception_error;
786 END IF;
787
788 --Step 1
789 --process the existing receipt rows in the batch
790 --prepare batch tables to be updated and deleted
791 --open the source batch tbl
792 IF l_batch_tbl.COUNT > 0
793 THEN
794 i := l_batch_tbl.FIRST;
795
796 LOOP
797 --open receipt source cursor
798 OPEN get_rct_id(l_batch_tbl(i).btc_id, l_batch_tbl(i).id);
799
800 FETCH get_rct_id
801 INTO l_receipt_id;
802
803 IF get_rct_id%NOTFOUND
804 THEN
805 l_receipt_id := NULL;
806 END IF;
807
808 CLOSE get_rct_id;
809
810 --check validity of receipt
811 IF l_receipt_id IS NULL
812 THEN
813 m := m + 1;
814 --populate the receipt batch to be inserted.
815 l_ins_batch_tbl(m) := l_batch_tbl(i);
816 ELSE
817 --populate the receipt batch to be inserted
818 n := n + 1;
819 l_upd_batch_tbl(n) := l_batch_tbl(i);
820 END IF;
821
822 EXIT WHEN(i = l_batch_tbl.LAST);
823 i := l_batch_tbl.NEXT(i);
824 END LOOP;
825 END IF;
826
827 --Step 2
828 --Update the Batch Lines which already existing and
829 --and is not removed from the UI
830 --Call the Update API
831 IF l_upd_batch_tbl.COUNT > 0
832 THEN
833 update_batch_lines(p_api_version => l_api_version,
834 p_init_msg_list => p_init_msg_list,
835 p_batch_exists => p_batch_exists,
836 p_batch_tbl => l_upd_batch_tbl,
837 x_return_status => l_return_status,
838 x_msg_count => l_msg_count,
839 x_msg_data => l_msg_data);
840
841 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
842 THEN
843 RAISE okl_api.g_exception_unexpected_error;
844 ELSIF (l_return_status = okl_api.g_ret_sts_error)
845 THEN
846 RAISE okl_api.g_exception_error;
847 END IF;
848 END IF;
849
850 --Step 3
851 --Validate new lines
852 --and insert them
853 --call the new batch api
854 IF l_ins_batch_tbl.COUNT > 0
855 THEN
856 insert_batch_lines(p_api_version => l_api_version,
857 p_init_msg_list => p_init_msg_list,
858 p_batch_exists => p_batch_exists,
859 p_batch_tbl => l_ins_batch_tbl,
860 x_return_status => l_return_status,
861 x_msg_count => l_msg_count,
862 x_msg_data => l_msg_data);
863
864 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
865 THEN
866 RAISE okl_api.g_exception_unexpected_error;
867 ELSIF (l_return_status = okl_api.g_ret_sts_error)
868 THEN
869 RAISE okl_api.g_exception_error;
870 END IF;
871 END IF;
872
873 --set output variables
874 x_return_status := l_return_status;
875 x_msg_count := l_msg_count;
876 x_msg_data := l_msg_data;
877 EXCEPTION
878 WHEN okl_api.g_exception_error THEN
879 x_return_status := okl_api.g_ret_sts_unexp_error;
880 x_msg_count := l_msg_count;
881 x_msg_data := l_msg_data;
882 WHEN okl_api.g_exception_unexpected_error THEN
883 x_return_status := okl_api.g_ret_sts_error;
884 x_msg_count := l_msg_count;
885 x_msg_data := l_msg_data;
886 WHEN OTHERS THEN
887 x_return_status := okl_api.g_ret_sts_unexp_error;
888 okl_api.set_message(p_app_name => 'OKL',
889 p_msg_name => 'OKL_DB_ERROR',
890 p_token1 => 'PROG_NAME',
891 p_token1_value => 'process_batch_lines',
892 p_token2 => 'SQLCODE',
893 p_token2_value => SQLCODE,
894 p_token3 => 'SQLERRM',
895 p_token3_value => SQLERRM);
896 x_msg_count := l_msg_count;
897 x_msg_data := l_msg_data;
898 END process_batch_lines;
899
900 ---------------------------------------------------------------------------
901 -- PROCEDURE handle_manual_pay
902 ---------------------------------------------------------------------------
903 PROCEDURE handle_batch_pay(p_api_version IN NUMBER,
904 p_init_msg_list IN VARCHAR2,
905 x_return_status OUT NOCOPY VARCHAR2,
906 x_msg_count OUT NOCOPY NUMBER,
907 x_msg_data OUT NOCOPY VARCHAR2,
908 p_btch_tbl IN okl_btch_dtls_tbl_type,
909 x_btch_tbl OUT NOCOPY okl_btch_dtls_tbl_type) IS
910 ---------------------------
911 -- DECLARE Local Variables
912 ---------------------------
913 l_api_version NUMBER := 1;
914 l_init_msg_list VARCHAR2(1);
915 l_return_status VARCHAR2(1);
916 l_msg_count NUMBER := 0;
917 l_msg_data VARCHAR2(2000);
918 l_rct_id NUMBER DEFAULT NULL;
919 l_rca_id NUMBER DEFAULT NULL;
920 l_btc_id NUMBER DEFAULT NULL;
921 l_irm_id NUMBER DEFAULT NULL;
922 l_consolidated_invoice_number VARCHAR2(90) DEFAULT NULL;
923 l_ar_invoice_id NUMBER DEFAULT NULL;
924 l_currency_code VARCHAR2(15) DEFAULT NULL;
925 l_check_number VARCHAR2(90) DEFAULT NULL;
926 l_receipt_date DATE DEFAULT NULL;
927 l_date_gl_requested DATE DEFAULT NULL;
928 l_amount NUMBER(14, 3) DEFAULT NULL;
929 l_ile_id NUMBER DEFAULT NULL;
930 l_consolidated_invoice_id NUMBER DEFAULT NULL;
931 l_khr_id NUMBER DEFAULT NULL;
932 l_contract_number VARCHAR2(120) DEFAULT NULL;
933 l_customer_number VARCHAR2(90) DEFAULT NULL;
934 --l_batch_qty NUMBER(15) DEFAULT NULL;
935 l_batch_total NUMBER(14, 3) DEFAULT NULL;
936 l_batch_exists VARCHAR2(2) DEFAULT NULL;
937 l_check_exists VARCHAR2(90) DEFAULT NULL;
938 l_no_stor_rcpts VARCHAR2(2) DEFAULT 'Y';
939 l_batch_status VARCHAR2(30) DEFAULT NULL;
940 i NUMBER DEFAULT NULL;
941 j NUMBER DEFAULT NULL;
942 k NUMBER DEFAULT NULL;
943 counter NUMBER DEFAULT NULL;
944 l_submitted_batch_total NUMBER DEFAULT NULL;
945 l_api_name CONSTANT VARCHAR2(30) := 'handle_batch_pay';
946 ------------------------------
947 -- DECLARE Record/Table Types
948 ------------------------------
949
950 -- Internal Trans
951 l_btch_tbl okl_btch_dtls_tbl_type;
952 l_org_id NUMBER DEFAULT mo_global.get_current_org_id();
953 -- Internal Trans
954 l_btcv_rec okl_btc_pvt.btcv_rec_type;
955 x_btcv_rec okl_btc_pvt.btcv_rec_type;
956 l_rctv_rec okl_rct_pvt.rctv_rec_type;
957 l_rctv_tbl okl_rct_pvt.rctv_tbl_type;
958 l_rcav_tbl okl_rca_pvt.rcav_tbl_type;
959 -- abindal start bug# 4695618 --
960 lv_rcav_tbl okl_rca_pvt.rcav_tbl_type;
961 -- abindal end bug# 4695618 --
962 x_rctv_rec okl_rct_pvt.rctv_rec_type;
963 x_rcav_tbl okl_rca_pvt.rcav_tbl_type;
964 --added by akrangan
965 l_trx_status_code VARCHAR2(50);
966 l_sub_batch_sts VARCHAR2(50) := 'SUBMITTED';
967 l_err_batch_sts VARCHAR2(50);
968
969 -------------------
970 -- DECLARE Cursors
971 -------------------
972
973 ----------
974
975 -- get batch irm_id
976 CURSOR c_get_btc_irm_id(cp_btc_id IN VARCHAR2) IS
977 SELECT irm_id,
978 batch_total,
979 date_gl_requested
980 FROM okl_trx_csh_batch_v a
981 WHERE a.id = cp_btc_id;
982
983 ----------
984
985 -- get org_id for contract
986 CURSOR c_get_org_id(cp_contract_num IN VARCHAR2) IS
987 SELECT authoring_org_id
988 FROM okc_k_headers_b
989 WHERE contract_number = cp_contract_num;
990
991 ----------
992
993 -- get new batch details
994 CURSOR c_get_btc_dtls(cp_btc_id IN VARCHAR2) IS
995 SELECT 'x'
996 FROM okl_trx_csh_batch_v a,
997 okl_trx_csh_receipt_v b
998 WHERE a.id = cp_btc_id
999 AND a.id = b.btc_id
1000 AND rownum = 1;
1001
1002 ----------
1003
1004 -- check for unique check number
1005 CURSOR c_unique_check(cp_check_number IN VARCHAR2, cp_amount IN NUMBER, cp_customer_id IN NUMBER, cp_receipt_date IN DATE) IS
1006 SELECT check_number
1007 FROM okl_trx_csh_receipt_v a
1008 WHERE a.check_number = cp_check_number
1009 AND a.amount = cp_amount
1010 AND a.ile_id = cp_customer_id
1011 AND a.date_effective = trunc(cp_receipt_date);
1012
1013 ----------
1014
1015 -- get the rct_id's ready for deletion.
1016 CURSOR get_rct_id(cp_btc_id IN NUMBER) IS
1017 SELECT rct.id
1018 FROM okl_trx_csh_receipt_b rct
1019 WHERE rct.btc_id = cp_btc_id;
1020
1021 get_rct_id_rec get_rct_id%ROWTYPE;
1022
1023 ----------
1024
1025 -- get the rct_id's ready for deletion.
1026 CURSOR get_rca_id(cp_rct_id IN NUMBER) IS
1027 SELECT rca.id
1028 FROM okl_txl_rcpt_apps_b rca
1029 WHERE rca.rct_id_details = cp_rct_id;
1030
1031 get_rca_id_rec get_rca_id%ROWTYPE;
1032
1033 ----------
1034
1035 --start code by pgomes on 03/05/2003
1036 CURSOR l_khr_curr_csr(cp_khr_id IN NUMBER) IS
1037 SELECT currency_code FROM okl_k_headers_full_v WHERE id = cp_khr_id;
1038
1039 CURSOR l_inv_curr_csr(cp_consolidated_invoice_id IN NUMBER) IS
1040 SELECT currency_code
1041 FROM okl_cnsld_ar_hdrs_b
1042 WHERE id = cp_consolidated_invoice_id;
1043
1044 l_temp_currency_code okl_k_headers_full_v.currency_code%TYPE;
1045
1046 --end code by pgomes on 03/05/2003
1047 CURSOR l_ar_inv_curr_csr(cp_invoice_id IN NUMBER) IS
1048 SELECT invoice_currency_code
1049 FROM ra_customer_trx_all
1050 WHERE customer_trx_id = cp_invoice_id;
1051 BEGIN
1052 l_return_status := okl_api.start_activity(l_api_name,
1053 g_pkg_name,
1054 p_init_msg_list,
1055 l_api_version,
1056 p_api_version,
1057 '_PVT',
1058 l_return_status);
1059
1060 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
1061 THEN
1062 RAISE okl_api.g_exception_unexpected_error;
1063 ELSIF (l_return_status = okl_api.g_ret_sts_error)
1064 THEN
1065 RAISE okl_api.g_exception_error;
1066 END IF;
1067
1068 l_btch_tbl := p_btch_tbl;
1069 l_submitted_batch_total := 0;
1070 i := 1;
1071 l_btc_id := l_btch_tbl(i).btc_id;
1072
1073 --akrangan moved the cursor location above
1074 -- check to see if batch exists, if it does obtain details ....
1075 OPEN c_get_btc_dtls(l_btc_id);
1076
1077 FETCH c_get_btc_dtls
1078 INTO l_batch_exists;
1079
1080 CLOSE c_get_btc_dtls;
1081
1082 process_batch_lines(p_api_version => p_api_version,
1083 p_init_msg_list => p_init_msg_list,
1084 p_batch_tbl => l_btch_tbl,
1085 p_batch_exists => l_batch_exists,
1086 x_return_status => l_return_status,
1087 x_msg_count => x_msg_count,
1088 x_msg_data => x_msg_data);
1089 IF (l_return_status = okl_api.g_ret_sts_unexp_error)
1090 THEN
1091 RAISE okl_api.g_exception_unexpected_error;
1092 ELSIF (l_return_status = okl_api.g_ret_sts_error)
1093 THEN
1094 RAISE okl_api.g_exception_error;
1095 END IF;
1096
1097 okl_api.end_activity(x_msg_count, x_msg_data);
1098 -- NEED TO HANDLE ERRORS AT THIS POINT --
1099 EXCEPTION
1100 WHEN g_exception_halt_validation THEN
1101 x_return_status := okl_api.g_ret_sts_error;
1102 x_return_status := okl_api.handle_exceptions(l_api_name,
1103 g_pkg_name,
1104 'OKL_API.G_RET_STS_ERROR',
1105 x_msg_count,
1106 x_msg_data,
1107 '_PVT');
1108 WHEN okl_api.g_exception_error THEN
1109 x_return_status := okl_api.handle_exceptions(l_api_name,
1110 g_pkg_name,
1111 'OKL_API.G_RET_STS_ERROR',
1112 x_msg_count,
1113 x_msg_data,
1114 '_PVT');
1115 WHEN okl_api.g_exception_unexpected_error THEN
1116 x_return_status := okl_api.handle_exceptions(l_api_name,
1117 g_pkg_name,
1118 'OKL_API.G_RET_STS_ERROR',
1119 x_msg_count,
1120 x_msg_data,
1121 '_PVT');
1122 WHEN OTHERS THEN
1123 x_return_status := okl_api.g_ret_sts_unexp_error;
1124 okl_api.set_message(p_app_name => g_app_name,
1125 p_msg_name => g_unexpected_error,
1126 p_token1 => g_sqlcode_token,
1127 p_token1_value => SQLCODE,
1128 p_token2 => g_sqlerrm_token,
1129 p_token2_value => SQLERRM);
1130 END handle_batch_pay;
1131 END okl_btch_cash_applic;