[Home] [Help]
PACKAGE BODY: APPS.OKL_CASH_RECEIPT
Source
1 PACKAGE BODY OKL_CASH_RECEIPT AS
2 /* $Header: OKLRRTCB.pls 120.20 2007/08/03 17:17:14 nikshah noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE cash_receipt
6 ---------------------------------------------------------------------------
7
8 PROCEDURE CASH_RECEIPT (p_api_version IN NUMBER := 1.0
9 ,p_init_msg_list IN VARCHAR2 := OKC_API.G_FALSE
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ,p_over_pay IN VARCHAR2
14 ,p_conc_proc IN VARCHAR2
15 ,p_xcrv_rec IN xcrv_rec_type
16 ,p_xcav_tbl IN xcav_tbl_type
17 ,x_cash_receipt_id OUT NOCOPY NUMBER
18 ) IS
19
20 ------------------------------
21 -- DECLARE Local variables
22 ------------------------------
23
24 i NUMBER DEFAULT 1;
25 l_record_count NUMBER DEFAULT NULL;
26
27 l_api_version NUMBER := 1.0;
28 l_init_msg_list VARCHAR2(1);
29
30 l_return_status VARCHAR2(1);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(2000);
33
34 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
35 l_func_amount_applied_tot NUMBER := 0;
36 l_rcpt_amount_applied_tot NUMBER := 0;
37 l_amount_unapplied NUMBER := 0;
38
39 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
40 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
41
42 l_exchange_rate AR_CASH_RECEIPTS_ALL.EXCHANGE_RATE%TYPE DEFAULT NULL;
43 l_exchange_rate_type AR_CASH_RECEIPTS_ALL.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL;
44 l_exchange_rate_date AR_CASH_RECEIPTS_ALL.EXCHANGE_DATE%TYPE DEFAULT NULL;
45
46 l_over_pay VARCHAR2(1) DEFAULT NULL;
47 l_conc_proc VARCHAR2(2) DEFAULT p_conc_proc;
48 l_counter NUMBER;
49
50 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT NULL;
51 l_ile_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL;
52 l_iba_id OKL_TRX_CSH_RECEIPT_V.IBA_ID%TYPE DEFAULT NULL;
53 l_orig_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
54 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
55 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
56
57 ar_invoice_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
58 ar_invoice_date OKL_BPD_LEASING_PAYMENT_TRX_V.INVOICE_DATE%TYPE DEFAULT NULL;
59
60 l_inv_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
61 l_rec_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
62 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
63
64 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
65 l_customer_site_use_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_SITE_USE_ID%TYPE DEFAULT NULL;
66
67 l_api_name CONSTANT VARCHAR2(30) := 'Cash_Receipt';
68
69 -- BEGIN abindal bug 4316610 --
70 l_applic_month VARCHAR2(10);
71 l_gl_month VARCHAR2(10);
72 -- END abindal bug 4316610 --
73
74 ------------------------------
75 -- DECLARE Record/Table Types
76 ------------------------------
77
78 l_xcrv_rec Okl_Extrn_Pvt.xcrv_rec_type;
79 l_xcav_tbl Okl_Extrn_Pvt.xcav_tbl_type;
80
81 ------------------------------
82 -- DECLARE Cursors
83 ------------------------------
84
85 -- get receipt info
86 CURSOR c_get_rcpt_info (cp_rct_id IN NUMBER) IS
87 SELECT a.irm_id
88 ,a.iba_id
89 ,a.ile_id
90 ,a.amount -- rcpt currency
91 ,a.currency_code
92 FROM okl_trx_csh_receipt_b a
93 WHERE a.id = cp_rct_id;
94
95 ----------
96
97 --get gl date from open accounting period
98 CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
99 SELECT trunc(cp_date) gl_date, 1 Counter
100 FROM gl_period_statuses
101 WHERE application_id = 222
102 -- BEGIN abindal bug 4356410 --
103 AND closing_status IN ('F','O')
104 -- END abindal bug 4356410 --
105 AND ledger_id = okl_accounting_util.get_set_of_books_id
106 AND trunc(cp_date) between start_date and end_date
107 AND adjustment_period_flag = 'N'
108 UNION
109 SELECT MAX(end_date) gl_date, 2 Counter
110 FROM gl_period_statuses
111 WHERE application_id = 222
112 AND ledger_id = okl_accounting_util.get_set_of_books_id
113 AND closing_status IN ('F','O')
114 AND end_date <= trunc(cp_date)
115 AND adjustment_period_flag = 'N'
116 UNION
117 SELECT MIN(start_date) gl_date, 3 Counter
118 FROM gl_period_statuses
119 WHERE application_id = 222
120 AND ledger_id = okl_accounting_util.get_set_of_books_id
121 AND closing_status IN ('F','O')
122 AND start_date >= trunc(cp_date)
123 AND adjustment_period_flag = 'N'
124 )
125 where gl_date is not null
126 order by counter;
127 ----------
128
129
130 BEGIN
131
132 l_over_pay := p_over_pay;
133 l_xcrv_rec := p_xcrv_rec;
134 l_xcav_tbl := p_xcav_tbl;
135
136 IF l_conc_proc = 'YC' THEN
137 l_init_msg_list := fnd_api.g_false;
138 ELSE
139 l_init_msg_list := fnd_api.g_true;
140 END IF;
141
142 l_functional_currency := okl_accounting_util.get_func_curr_code;
143
144 -- BEGIN abindal bug 4356410 --
145 OPEN c_get_gl_date(p_xcrv_rec.receipt_date);
146 -- END abindal bug 4356410 --
147 FETCH c_get_gl_date INTO l_rec_gl_date, l_counter;
148
149 IF c_get_gl_date%NOTFOUND THEN
150 CLOSE c_get_gl_date;
151
152 OKC_API.set_message( p_app_name => G_APP_NAME,
153 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
154 p_token1 => 'TRX_DATE',
155 p_token1_value => TRUNC(SYSDATE));
156
157 l_return_status := OKC_API.G_RET_STS_ERROR;
158 RAISE G_EXCEPTION_HALT_VALIDATION;
159 END IF;
160 CLOSE c_get_gl_date;
161
162 IF l_xcrv_rec.remittance_amount = 0 OR
163 l_xcrv_rec.remittance_amount IS NULL THEN
164 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
165 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
166 END IF;
167
168 -- get IRM_ID, ILE_ID ...
169
170 OPEN c_get_rcpt_info(l_xcrv_rec.rct_id);
171 FETCH c_get_rcpt_info INTO l_irm_id
172 ,l_iba_id
173 ,l_ile_id
174 ,l_orig_rcpt_amount -- receipt currency
175 ,l_currency_code;
176 CLOSE c_get_rcpt_info;
177
178 IF l_functional_currency <> l_currency_code THEN -- dealing with currency ...
179 -- IF l_currency_code <> l_xcrv_rec.currency_code THEN -- bv
180
181 l_exchange_rate_type := l_xcrv_rec.exchange_rate_type;
182
183 IF l_exchange_rate_type IN ('Corporate', 'Spot') THEN
184 l_exchange_rate_date := l_xcrv_rec.exchange_rate_date;
185 l_exchange_rate := NULL;
186 ELSE
187 l_exchange_rate_date := NULL;
188 l_exchange_rate := l_xcrv_rec.attribute1;
189 END IF;
190
191 END IF;
192
193 l_record_count := l_xcav_tbl.COUNT;
194
195 IF l_record_count > 0 THEN
196 i := l_xcav_tbl.FIRST;
197 LOOP -- in functional currency ...
198 l_func_amount_applied_tot := l_func_amount_applied_tot + l_xcav_tbl(i).AMOUNT_APPLIED;
199 IF l_currency_code <> l_xcrv_rec.currency_code THEN -- dealing with currency ...
200 l_rcpt_amount_applied_tot := l_rcpt_amount_applied_tot + l_xcav_tbl(i).AMOUNT_APPLIED_FROM;
201 END IF;
202 EXIT WHEN (i = l_xcav_tbl.LAST);
203 i := i + 1;
204 END LOOP;
205 END IF;
206
207 IF l_record_count >= 0 THEN
208 i := l_xcav_tbl.FIRST;
209
210 IF l_record_count > 0 THEN
211
212 SELECT receivables_invoice_id INTO l_customer_trx_id
213 FROM okl_cnsld_ar_strms_v
214 WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
215
216 SELECT bill_to_site_use_id INTO l_customer_site_use_id
217 FROM ra_customer_trx_all
218 WHERE customer_trx_id = l_customer_trx_id;
219
220 l_customer_trx_id := NULL;
221
222 END IF;
223
224 ----------------------------------------------------------------
225 -- when dealing with cross currencies, daily exchange rates must
226 -- be defined.
227 ----------------------------------------------------------------
228
229 Ar_receipt_api_pub.Create_cash( p_api_version => l_api_version
230 ,p_init_msg_list => l_init_msg_list
231 ,x_return_status => l_return_status
232 ,x_msg_count => l_msg_count
233 ,x_msg_data => l_msg_data
234 ,p_receipt_number => l_xcrv_rec.check_number
235 ,p_receipt_date => l_xcrv_rec.receipt_date
236 ,p_customer_site_use_id => l_customer_site_use_id
237 ,p_customer_number => l_xcrv_rec.customer_number
238 ,p_amount => l_orig_rcpt_amount -- in receipt currency ...
239 ,p_currency_code => l_currency_code
240
241 ,p_exchange_rate_type => l_exchange_rate_type -- daily exchge rate required ...
242 ,p_exchange_rate => l_exchange_rate -- daily exchge rate required ...
243 ,p_exchange_rate_date => l_exchange_rate_date -- daily exchge rate required ...
244
245 ,p_gl_date => l_rec_gl_date
246 ,p_receipt_method_id => l_irm_id
247 ,p_cr_id => l_cash_receipt_id -- OUT
248 ,p_org_id => mo_global.get_current_org_id()
249 );
250
251 x_return_status := l_return_status;
252
253 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
254 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
255 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
256 RAISE OKL_API.G_EXCEPTION_ERROR;
257 END IF;
258
259 END IF;
260
261 IF l_record_count > 0 THEN -- LOOP APPLY
262
263 i := l_xcav_tbl.FIRST;
264 LOOP
265
266 ar_invoice_num := NULL;
267
268 SELECT receivables_invoice_id INTO l_customer_trx_id
269 FROM okl_cnsld_ar_strms_v
270 WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
271
272 /* Modified select statement to address bug 4510824 */
273 SELECT max(trx_date), invoice_number INTO l_xcav_tbl(i).trx_date, ar_invoice_num
274 FROM okl_xtl_csh_apps_v
275 WHERE lsm_id = l_xcav_tbl(i).lsm_id
276 AND rca_id = l_xcav_tbl(i).rca_id
277 GROUP BY invoice_number;
278
279 /* added to address bug 4208639 */
280 SELECT invoice_date INTO ar_invoice_date
281 FROM OKL_BPD_LEASING_PAYMENT_TRX_V
282 WHERE receivables_invoice_number = ar_invoice_num;
283
284 IF l_currency_code = l_xcrv_rec.currency_code THEN
285 l_applied_amount := l_xcav_tbl(i).amount_applied;
286 l_applied_amount_from := NULL;
287 ELSE
288 l_applied_amount := l_xcav_tbl(i).amount_applied;
289 l_applied_amount_from := l_xcav_tbl(i).amount_applied_from;
290 END IF;
291
292 -- bug 4208639 start
293 IF l_xcrv_rec.receipt_date <= SYSDATE THEN
294 l_apply_date := TRUNC(SYSDATE);
295 END IF;
296
297 IF l_xcrv_rec.receipt_date > SYSDATE THEN
298 l_apply_date := l_xcrv_rec.receipt_date;
299 END IF;
300
301 IF ar_invoice_date > l_xcrv_rec.receipt_date THEN
302 IF ar_invoice_date > SYSDATE THEN
303 l_apply_date := ar_invoice_date;
304 END IF;
305 END IF;
306 -- bug 4208639 end
307
308 OPEN c_get_gl_date(l_apply_date);
309 FETCH c_get_gl_date INTO l_inv_gl_date, l_counter;
310
311 IF c_get_gl_date%NOTFOUND THEN
312 CLOSE c_get_gl_date;
313 OKC_API.set_message( p_app_name => G_APP_NAME,
314 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
315 p_token1 => 'TRX_DATE',
316 p_token1_value => TRUNC(l_xcav_tbl(i).trx_date));
317
318 l_return_status := OKC_API.G_RET_STS_ERROR;
319 RAISE G_EXCEPTION_HALT_VALIDATION;
320 END IF;
321 CLOSE c_get_gl_date;
322
323 IF l_applied_amount = 0 THEN
324 NULL;
325 ELSE
326 Ar_receipt_api_pub.apply( p_api_version => l_api_version
327 ,p_init_msg_list => l_init_msg_list
328 ,x_return_status => l_return_status
329 ,x_msg_count => l_msg_count
330 ,x_msg_data => l_msg_data
331 ,p_customer_trx_id => l_customer_trx_id
332 ,p_amount_applied => l_applied_amount -- in func/rcpt currency.
333 ,p_amount_applied_from => l_applied_amount_from -- in rcpt_currency
334 ,p_apply_gl_date => l_inv_gl_date
335 ,p_apply_date => l_apply_date
336 ,p_cash_receipt_id => l_cash_receipt_id
337 ,p_org_id => mo_global.get_current_org_id()
338 );
339
340 x_return_status := l_return_status;
341
342 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
343 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
344 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
345 RAISE OKL_API.G_EXCEPTION_ERROR;
346 END IF;
347
348 END IF;
349
350 EXIT WHEN (i = l_xcav_tbl.LAST);
351
352 i := i + 1;
353
354 END LOOP;
355
356 END IF;
357
358 IF l_func_amount_applied_tot < l_orig_rcpt_amount THEN
359
360 IF l_over_pay = 'O' THEN -- APPLY TO ON ACCOUNT IF SELECTED.
361
362 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
363 ,p_init_msg_list => l_init_msg_list
364 ,x_return_status => l_return_status
365 ,x_msg_count => l_msg_count
366 ,x_msg_data => l_msg_data
367 ,p_cash_receipt_id => l_cash_receipt_id
368 -- ,p_amount_applied => l_amount_unapplied -- not required. we will just write off remaining rcpt.
369 ,p_apply_date => l_xcrv_rec.receipt_date
370 ,p_apply_gl_date => l_rec_gl_date
371 ,p_org_id => mo_global.get_current_org_id()
372 );
373
374 x_return_status := l_return_status;
375 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
376 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
377 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
378 RAISE OKL_API.G_EXCEPTION_ERROR;
379 END IF;
380
381 END IF; -- ELSE LEAVE CASH AS UNAPPLIED.
382
383 END IF;
384
385 x_cash_receipt_id := l_cash_receipt_id;
386 x_return_status := l_return_status;
387
388 EXCEPTION
389
390 WHEN G_EXCEPTION_HALT_VALIDATION THEN
391 x_return_status := OKC_API.G_RET_STS_ERROR;
392
393
394 WHEN OKC_API.G_EXCEPTION_ERROR THEN
395 x_return_status := OKC_API.HANDLE_EXCEPTIONS
396 (
397 l_api_name,
398 G_PKG_NAME,
399 'OKC_API.G_RET_STS_ERROR',
400 x_msg_count,
401 x_msg_data,
402 '_PVT'
403 );
404
405 WHEN OTHERS THEN
406 NULL;
407 /* -- fix bug number 2439881
408 Okl_api.set_message( p_app_name => g_app_name
409 , p_msg_name => g_unexpected_error
410 , p_token1 => g_sqlcode_token
411 , p_token1_value => SQLCODE
412 , p_token2 => g_sqlerrm_token
413 , p_token2_value => SQLERRM
414 ) ;
415
416 */
417 END CASH_RECEIPT;
418
419 PROCEDURE CREATE_RECEIPT (p_api_version IN NUMBER := 1.0
420 ,p_init_msg_list IN VARCHAR2 := OKC_API.G_FALSE
421 ,x_return_status OUT NOCOPY VARCHAR2
422 ,x_msg_count OUT NOCOPY NUMBER
423 ,x_msg_data OUT NOCOPY VARCHAR2
424 ,p_rcpt_rec IN OKL_CASH_APPL_RULES.rcpt_rec_type
425 ,x_cash_receipt_id OUT NOCOPY NUMBER
426 ) IS
427
428 ------------------------------
429 -- DECLARE Local variables
430 ------------------------------
431
432 l_api_version NUMBER := 1.0;
433 l_init_msg_list VARCHAR2(1);
434 l_return_status VARCHAR2(1);
435 l_msg_count NUMBER;
436 l_msg_data VARCHAR2(2000);
437
438 l_cash_receipt_id AR_CASH_RECEIPTS.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
439
440 l_exchange_rate AR_CASH_RECEIPTS.EXCHANGE_RATE%TYPE := p_rcpt_rec.exchange_rate;
441 l_exchange_rate_type AR_CASH_RECEIPTS.EXCHANGE_RATE_TYPE%TYPE := p_rcpt_rec.exchange_rate_type;
442 l_exchange_rate_date AR_CASH_RECEIPTS.EXCHANGE_DATE%TYPE := p_rcpt_rec.exchange_date;
443 l_functional_currency AR_CASH_RECEIPTS.CURRENCY_CODE%TYPE DEFAULT NULL;
444
445 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RECEIPT';
446
447 -- BEGIN abindal bug 4316610 --
448 l_applic_month VARCHAR2(10);
449 l_gl_month VARCHAR2(10);
450 -- END abindal bug 4316610 --
451
452 ------------------------------
453 -- DECLARE Record/Table Types
454 ------------------------------
455
456 l_rcpt_rec OKL_CASH_APPL_RULES.rcpt_rec_type;
457 l_attribute_rec AR_RECEIPT_API_PUB.attribute_rec_type;
458
459
460 BEGIN
461 SAVEPOINT create_receipt;
462 l_rcpt_rec := p_rcpt_rec;
463 l_attribute_rec.attribute_category := l_rcpt_rec.dff_attribute_category;
464 l_attribute_rec.attribute1 := l_rcpt_rec.dff_attribute1;
465 l_attribute_rec.attribute2 := l_rcpt_rec.dff_attribute2;
466 l_attribute_rec.attribute3 := l_rcpt_rec.dff_attribute3;
467 l_attribute_rec.attribute4 := l_rcpt_rec.dff_attribute4;
468 l_attribute_rec.attribute5 := l_rcpt_rec.dff_attribute5;
469 l_attribute_rec.attribute6 := l_rcpt_rec.dff_attribute6;
470 l_attribute_rec.attribute7 := l_rcpt_rec.dff_attribute7;
471 l_attribute_rec.attribute8 := l_rcpt_rec.dff_attribute8;
472 l_attribute_rec.attribute9 := l_rcpt_rec.dff_attribute9;
473 l_attribute_rec.attribute10 := l_rcpt_rec.dff_attribute10;
474 l_attribute_rec.attribute11 := l_rcpt_rec.dff_attribute11;
475 l_attribute_rec.attribute12 := l_rcpt_rec.dff_attribute12;
476 l_attribute_rec.attribute13 := l_rcpt_rec.dff_attribute13;
477 l_attribute_rec.attribute14 := l_rcpt_rec.dff_attribute14;
478 l_attribute_rec.attribute15 := l_rcpt_rec.dff_attribute15;
479
480
481 l_functional_currency := okl_accounting_util.get_func_curr_code;
482 IF l_functional_currency <> l_rcpt_rec.currency_code THEN
483
484 l_exchange_rate_type := l_rcpt_rec.exchange_rate_type;
485
486 IF l_exchange_rate_type IN ('Corporate', 'Spot') THEN
487 l_exchange_rate_date := l_rcpt_rec.exchange_date;
488 l_exchange_rate := NULL;
489 ELSE
490 l_exchange_rate_date := NULL;
491 l_exchange_rate := l_rcpt_rec.exchange_rate;
492 END IF;
493
494 END IF;
495
496 IF l_exchange_rate_type = 'NONE' THEN
497 l_exchange_rate_type := NULL;
498 END IF;
499
500 Ar_receipt_api_pub.Create_cash( p_api_version => l_api_version
501 ,p_init_msg_list => l_init_msg_list
502 ,x_return_status => l_return_status
503 ,x_msg_count => l_msg_count
504 ,x_msg_data => l_msg_data
505 ,p_receipt_number => l_rcpt_rec.RECEIPT_NUMBER
506 ,p_receipt_date => l_rcpt_rec.receipt_date
507 ,p_customer_number => l_rcpt_rec.customer_number
508 ,p_amount => l_rcpt_rec.AMOUNT -- in receipt currency ...
509 ,p_currency_code => l_rcpt_rec.CURRENCY_CODE
510 ,p_exchange_rate_type => l_exchange_rate_type -- daily exchge rate required ...
511 ,p_exchange_rate => l_exchange_rate -- daily exchge rate required ...
512 ,p_exchange_rate_date => l_exchange_rate_date -- daily exchge rate required ...
513 ,p_gl_date => l_rcpt_rec.GL_DATE
514 ,p_receipt_method_id => l_rcpt_rec.RECEIPT_METHOD_ID
515 ,p_attribute_rec => l_attribute_rec
516 ,p_remittance_bank_account_id => l_rcpt_rec.REMITTANCE_BANK_ACCOUNT_ID
517 ,p_remittance_bank_account_num => l_rcpt_rec.REMITTANCE_BANK_ACCOUNT_NUM
518 ,p_remittance_bank_account_name => l_rcpt_rec.REMITTANCE_BANK_ACCOUNT_NAME
519 ,p_payment_trxn_extension_id => l_rcpt_rec.PAYMENT_TRX_EXTENSION_ID
520 ,p_cr_id => l_cash_receipt_id -- OUT
521 ,p_org_id => l_rcpt_rec.org_id
522 );
523 x_return_status := l_return_status;
524
525 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
527 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
528 RAISE OKL_API.G_EXCEPTION_ERROR;
529 END IF;
530
531 x_cash_receipt_id := l_cash_receipt_id;
532 x_return_status := l_return_status;
533 EXCEPTION
534
535 WHEN G_EXCEPTION_HALT_VALIDATION THEN
536 ROLLBACK TO create_receipt;
537 x_return_status := OKL_API.G_RET_STS_ERROR;
538
539 WHEN OKC_API.G_EXCEPTION_ERROR THEN
540 ROLLBACK TO create_receipt;
541 x_return_status := OKL_API.HANDLE_EXCEPTIONS
542 (
543 l_api_name,
544 G_PKG_NAME,
545 'OKC_API.G_RET_STS_ERROR',
546 x_msg_count,
547 x_msg_data,
548 '_PVT'
549 );
550
551 END CREATE_RECEIPT;
552
553
554 PROCEDURE PAYMENT_RECEIPT (p_api_version IN NUMBER := 1.0
555 ,p_init_msg_list IN VARCHAR2 := OKC_API.G_FALSE
556 ,x_return_status OUT NOCOPY VARCHAR2
557 ,x_msg_count OUT NOCOPY NUMBER
558 ,x_msg_data OUT NOCOPY VARCHAR2
559 ,p_over_pay IN VARCHAR2
560 ,p_conc_proc IN VARCHAR2
561 ,p_xcrv_rec IN xcrv_rec_type
562 ,p_xcav_tbl IN xcav_tbl_type
563 ,x_cash_receipt_id OUT NOCOPY NUMBER
564 ) IS
565
566 ------------------------------
567 -- DECLARE Local variables
568 ------------------------------
569
570 i NUMBER DEFAULT 1;
571 l_record_count NUMBER DEFAULT NULL;
572
573 l_api_version NUMBER := 1.0;
574 l_init_msg_list VARCHAR2(1);
575
576 l_return_status VARCHAR2(1);
577 l_msg_count NUMBER;
578 l_msg_data VARCHAR2(2000);
579
580 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
581 l_func_amount_applied_tot NUMBER := 0;
582 l_rcpt_amount_applied_tot NUMBER := 0;
583 l_amount_unapplied NUMBER := 0;
584
585 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
586 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
587
588 l_exchange_rate AR_CASH_RECEIPTS_ALL.EXCHANGE_RATE%TYPE DEFAULT NULL;
589 l_exchange_rate_type AR_CASH_RECEIPTS_ALL.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL;
590 l_exchange_rate_date AR_CASH_RECEIPTS_ALL.EXCHANGE_DATE%TYPE DEFAULT NULL;
591
592 l_over_pay VARCHAR2(1) DEFAULT NULL;
593 l_conc_proc VARCHAR2(2) DEFAULT p_conc_proc;
594 l_counter NUMBER;
595
596 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT NULL;
597 l_ile_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL;
598 l_iba_id OKL_TRX_CSH_RECEIPT_V.IBA_ID%TYPE DEFAULT NULL;
599 l_orig_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
600 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
601 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
602
603 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
604 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
605
606 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
607 l_customer_site_use_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_SITE_USE_ID%TYPE DEFAULT NULL;
608
609 l_api_name CONSTANT VARCHAR2(30) := 'Cash_Receipt';
610
611 -- BEGIN abindal bug 4316610 --
612 l_applic_month VARCHAR2(10);
613 l_gl_month VARCHAR2(10);
614 -- END abindal bug 4316610 --
615
616
617 ------------------------------
618 -- DECLARE Record/Table Types
619 ------------------------------
620
621 l_xcrv_rec Okl_Extrn_Pvt.xcrv_rec_type;
622 l_xcav_tbl Okl_Extrn_Pvt.xcav_tbl_type;
623
624 ------------------------------
625 -- DECLARE Cursors
626 ------------------------------
627
628 -- get receipt info
629 CURSOR c_get_rcpt_info (cp_rct_id IN NUMBER) IS
630 SELECT a.irm_id
631 ,a.iba_id
632 ,a.ile_id
633 ,a.amount -- rcpt currency
634 ,a.currency_code
635 FROM okl_trx_csh_receipt_b a
636 WHERE a.id = cp_rct_id;
637
638 ----------
639
640 --get gl date from open accounting period
641 CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
642 SELECT end_date gl_date, 1 Counter
643 FROM gl_period_statuses
644 WHERE application_id = 222
645 AND ledger_id = okl_accounting_util.get_set_of_books_id
646 AND trunc(cp_date) between start_date and end_date
647 AND adjustment_period_flag = 'N'
648 UNION
649 SELECT MAX(end_date) gl_date, 2 Counter
650 FROM gl_period_statuses
651 WHERE application_id = 222
652 AND ledger_id = okl_accounting_util.get_set_of_books_id
653 AND closing_status IN ('F','O')
654 AND end_date <= trunc(cp_date)
655 AND adjustment_period_flag = 'N'
656 UNION
657 SELECT MIN(end_date) gl_date, 3 Counter
658 FROM gl_period_statuses
659 WHERE application_id = 222
660 AND ledger_id = okl_accounting_util.get_set_of_books_id
661 AND closing_status IN ('F','O')
662 AND start_date >= trunc(cp_date)
663 AND adjustment_period_flag = 'N'
664 )
665 where gl_date is not null
666 order by counter;
667
668 ----------
669 -- BEGIN abindal bug 4316610 --
670 --get gl date from open accounting period -- min date
671 CURSOR c_get_gl_date_start(cp_date IN DATE) IS SELECT * from (
672 SELECT start_date gl_date, 1 Counter
673 FROM gl_period_statuses
674 WHERE application_id = 222
675 AND ledger_id = okl_accounting_util.get_set_of_books_id
676 AND trunc(cp_date) between start_date and end_date
677 AND adjustment_period_flag = 'N'
678 UNION
679 SELECT MAX(start_date) gl_date, 2 Counter
680 FROM gl_period_statuses
681 WHERE application_id = 222
682 AND ledger_id = okl_accounting_util.get_set_of_books_id
683 AND closing_status IN ('F','O')
684 AND end_date <= trunc(cp_date)
685 AND adjustment_period_flag = 'N'
686 UNION
687 SELECT MIN(start_date) gl_date, 3 Counter
688 FROM gl_period_statuses
689 WHERE application_id = 222
690 AND ledger_id = okl_accounting_util.get_set_of_books_id
691 AND closing_status IN ('F','O')
692 AND start_date >= trunc(cp_date)
693 AND adjustment_period_flag = 'N'
694 )
695 where gl_date is not null
696 order by counter;
697 -- END abindal bug 4316610 --
698 ----------
699
700
701 BEGIN
702
703 l_over_pay := p_over_pay;
704 l_xcrv_rec := p_xcrv_rec;
705 l_xcav_tbl := p_xcav_tbl;
706
707
708 IF l_conc_proc = 'YC' THEN
709 l_init_msg_list := fnd_api.g_false;
710 ELSE
711 l_init_msg_list := fnd_api.g_true;
712 END IF;
713
714 l_functional_currency := okl_accounting_util.get_func_curr_code;
715
716 OPEN c_get_gl_date(SYSDATE);
717 FETCH c_get_gl_date INTO l_gl_date, l_counter;
718
719 IF c_get_gl_date%NOTFOUND THEN
720 CLOSE c_get_gl_date;
721
722 OKC_API.set_message( p_app_name => G_APP_NAME,
723 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
724 p_token1 => 'TRX_DATE',
725 p_token1_value => TRUNC(SYSDATE));
726
727 l_return_status := OKC_API.G_RET_STS_ERROR;
728 RAISE G_EXCEPTION_HALT_VALIDATION;
729 END IF;
730 CLOSE c_get_gl_date;
731
732 -- BEGIN abindal bug 4316610 --
733
734 SELECT TO_CHAR(sysdate, 'MONTH') INTO l_applic_month
735 FROM DUAL;
736
737 SELECT TO_CHAR(l_gl_date, 'MONTH') INTO l_gl_month
738 FROM DUAL;
739
740 IF l_gl_month = l_applic_month THEN
741 l_gl_date := TRUNC(SYSDATE);
742 END IF;
743
744 IF TRUNC(l_gl_date) > TRUNC(SYSDATE) THEN
745 OPEN c_get_gl_date_start(SYSDATE); -- min
746 FETCH c_get_gl_date_start INTO l_gl_date, l_counter;
747
748 IF c_get_gl_date_start%NOTFOUND THEN
749 CLOSE c_get_gl_date_start;
750
751 OKC_API.set_message( p_app_name => G_APP_NAME,
752 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
753 p_token1 => 'TRX_DATE',
754 p_token1_value => TRUNC(SYSDATE));
755
756 l_return_status := OKC_API.G_RET_STS_ERROR;
757 RAISE G_EXCEPTION_HALT_VALIDATION;
758 END IF;
759 CLOSE c_get_gl_date_start;
760 END IF;
761
762 -- END abindal bug 4316610 --
763
764
765
766 IF l_xcrv_rec.remittance_amount = 0 OR
767 l_xcrv_rec.remittance_amount IS NULL THEN
768 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
769 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
770 END IF;
771
772 -- get IRM_ID, ILE_ID ...
773
774 OPEN c_get_rcpt_info(l_xcrv_rec.rct_id);
775 FETCH c_get_rcpt_info INTO l_irm_id
776 ,l_iba_id
777 ,l_ile_id
778 ,l_orig_rcpt_amount -- receipt currency
779 ,l_currency_code;
780 CLOSE c_get_rcpt_info;
781
782 IF l_functional_currency <> l_currency_code THEN -- dealing with currency ...
783 -- IF l_currency_code <> l_xcrv_rec.currency_code THEN -- bv
784
785 l_exchange_rate_type := l_xcrv_rec.exchange_rate_type;
786
787 IF l_exchange_rate_type IN ('Corporate', 'Spot') THEN
788 l_exchange_rate_date := l_xcrv_rec.exchange_rate_date;
789 l_exchange_rate := NULL;
790 ELSE
791 l_exchange_rate_date := NULL;
792 l_exchange_rate := l_xcrv_rec.attribute1;
793 END IF;
794
795 END IF;
796
797 l_record_count := l_xcav_tbl.COUNT;
798
799 IF l_record_count > 0 THEN
800 i := l_xcav_tbl.FIRST;
801 LOOP -- in functional currency ...
802 l_func_amount_applied_tot := l_func_amount_applied_tot + l_xcav_tbl(i).AMOUNT_APPLIED;
803 IF l_currency_code <> l_xcrv_rec.currency_code THEN -- dealing with currency ...
804 l_rcpt_amount_applied_tot := l_rcpt_amount_applied_tot + l_xcav_tbl(i).AMOUNT_APPLIED_FROM;
805 END IF;
806 EXIT WHEN (i = l_xcav_tbl.LAST);
807 i := i + 1;
808 END LOOP;
809 END IF;
810
811 IF l_record_count > 0 THEN
812 i := l_xcav_tbl.FIRST;
813
814 ----------------------------------------------------------------
815
816 SELECT receivables_invoice_id INTO l_customer_trx_id
817 FROM okl_cnsld_ar_strms_v
818 WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
819
820 SELECT bill_to_site_use_id INTO l_customer_site_use_id
821 FROM ra_customer_trx_all
822 WHERE customer_trx_id = l_customer_trx_id;
823
824 -- l_customer_trx_id := NULL;
825
826 SELECT trx_date INTO l_xcav_tbl(i).trx_date
827 FROM okl_xtl_csh_apps_v
828 WHERE lsm_id = l_xcav_tbl(i).lsm_id
829 AND rca_id = l_xcav_tbl(i).rca_id;
830
831 IF l_currency_code = l_xcrv_rec.currency_code THEN
832 l_applied_amount := l_xcav_tbl(i).amount_applied;
833 l_applied_amount_from := NULL;
834 ELSE
835 l_applied_amount := l_xcav_tbl(i).amount_applied;
836 l_applied_amount_from := l_xcav_tbl(i).amount_applied_from;
837 END IF;
838
839 IF l_xcav_tbl(i).trx_date > l_xcrv_rec.receipt_date THEN
840 l_apply_date := l_xcav_tbl(i).trx_date;
841 --l_gl_date := l_xcav_tbl(i).trx_date;
842 OPEN c_get_gl_date(l_xcav_tbl(i).trx_date);
843 FETCH c_get_gl_date INTO l_gl_date, l_counter;
844
845 IF c_get_gl_date%NOTFOUND THEN
846 CLOSE c_get_gl_date;
847
848 OKC_API.set_message( p_app_name => G_APP_NAME,
849 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
850 p_token1 => 'TRX_DATE',
851 p_token1_value => TRUNC(l_xcav_tbl(i).trx_date));
852
853 l_return_status := OKC_API.G_RET_STS_ERROR;
854 RAISE G_EXCEPTION_HALT_VALIDATION;
855 END IF;
856 CLOSE c_get_gl_date;
857 ELSE
858 l_apply_date := l_xcrv_rec.receipt_date;
859 --l_gl_date := l_xcrv_rec.receipt_date;
860 END IF;
861 -------------------------------------------------------------
862
863 Ar_receipt_api_pub.Create_and_apply( p_api_version => l_api_version
864 ,p_init_msg_list => l_init_msg_list
865 ,x_return_status => l_return_status
866 ,x_msg_count => l_msg_count
867 ,x_msg_data => l_msg_data
868 ,p_customer_site_use_id => l_customer_site_use_id
869 ,p_customer_number => l_xcrv_rec.customer_number
870 ,p_customer_trx_id => l_customer_trx_id
871 ,p_amount => l_orig_rcpt_amount
872 ,p_amount_applied => l_applied_amount
873 ,p_amount_applied_from => l_applied_amount_from
874 ,p_receipt_number => NULL -- l_xcrv_rec.check_number
875 ,p_currency_code => l_currency_code
876 ,p_exchange_rate_type => l_exchange_rate_type
877 ,p_exchange_rate => l_exchange_rate
878 ,p_exchange_rate_date => l_exchange_rate_date
879 ,p_receipt_date => l_xcrv_rec.receipt_date
880 ,p_gl_date => l_gl_date
881 ,p_apply_date => l_apply_date
882 ,p_apply_gl_date => l_gl_date
883 ,p_receipt_method_id => l_irm_id
884 ,p_customer_bank_account_id => l_iba_id
885 ,p_cr_id => l_cash_receipt_id -- OUT
886 ,p_org_id => mo_global.get_current_org_id()
887 );
888
889
890 x_return_status := l_return_status;
891
892 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
893 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
894 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
895 RAISE OKL_API.G_EXCEPTION_ERROR;
896 END IF;
897
898 END IF;
899
900 -- Get the total amount applied.
901
902 IF i <> l_xcav_tbl.LAST THEN
903
904 -- IF l_record_count > 1 THEN -- LOOP APPLY
905
906 i := i + 1;
907 LOOP
908
909 SELECT receivables_invoice_id INTO l_customer_trx_id
910 FROM okl_cnsld_ar_strms_v
911 WHERE okl_cnsld_ar_strms_v.id = l_xcav_tbl(i).lsm_id;
912
913 SELECT trx_date INTO l_xcav_tbl(i).trx_date
914 FROM okl_xtl_csh_apps_v
915 WHERE lsm_id = l_xcav_tbl(i).lsm_id
916 AND rca_id = l_xcav_tbl(i).rca_id;
917
918 IF l_currency_code = l_xcrv_rec.currency_code THEN
919 l_applied_amount := l_xcav_tbl(i).amount_applied;
920 l_applied_amount_from := NULL;
921 ELSE
922 l_applied_amount := l_xcav_tbl(i).amount_applied;
923 l_applied_amount_from := l_xcav_tbl(i).amount_applied_from;
924 END IF;
925
926 IF l_xcav_tbl(i).trx_date > l_xcrv_rec.receipt_date THEN
927 l_apply_date := l_xcav_tbl(i).trx_date;
928 --l_gl_date := l_xcav_tbl(i).trx_date;
929 OPEN c_get_gl_date(l_xcav_tbl(i).trx_date);
930 FETCH c_get_gl_date INTO l_gl_date, l_counter;
931
932 IF c_get_gl_date%NOTFOUND THEN
933 CLOSE c_get_gl_date;
934 OKC_API.set_message( p_app_name => G_APP_NAME,
935 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
936 p_token1 => 'TRX_DATE',
937 p_token1_value => TRUNC(l_xcav_tbl(i).trx_date));
938
939 l_return_status := OKC_API.G_RET_STS_ERROR;
940 RAISE G_EXCEPTION_HALT_VALIDATION;
941 END IF;
942 CLOSE c_get_gl_date;
943 ELSE
944 l_apply_date := l_xcrv_rec.receipt_date;
945 --l_gl_date := l_xcrv_rec.receipt_date;
946 END IF;
947
948
949 IF l_applied_amount = 0 THEN
950 NULL;
951 ELSE
952 Ar_receipt_api_pub.apply( p_api_version => l_api_version
953 ,p_init_msg_list => l_init_msg_list
954 ,x_return_status => l_return_status
955 ,x_msg_count => l_msg_count
956 ,x_msg_data => l_msg_data
957 ,p_customer_trx_id => l_customer_trx_id
958 ,p_amount_applied => l_applied_amount -- in func/rcpt currency.
959 ,p_amount_applied_from => l_applied_amount_from -- in rcpt_currency
960 ,p_apply_gl_date => l_gl_date
961 ,p_apply_date => l_apply_date
962 ,p_cash_receipt_id => l_cash_receipt_id
963 ,p_org_id => mo_global.get_current_org_id()
964 );
965
966 x_return_status := l_return_status;
967
968 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
969 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
970 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
971 RAISE OKL_API.G_EXCEPTION_ERROR;
972 END IF;
973
974 END IF;
975
976 EXIT WHEN (i = l_xcav_tbl.LAST);
977
978 i := i + 1;
979
980 END LOOP;
981
982 END IF;
983
984 IF l_func_amount_applied_tot < l_orig_rcpt_amount THEN
985 /*
986 IF l_currency_code = l_xcrv_rec.currency_code THEN -- functional currency ...
987 l_amount_unapplied := l_xcrv_rec.remittance_amount - l_func_amount_applied_tot;
988 ELSE
989 l_amount_unapplied := l_orig_rcpt_amount - l_rcpt_amount_applied_tot; -- in receipt currency ...
990 END IF;
991 */
992 IF l_over_pay = 'O' THEN -- APPLY TO ON ACCOUNT IF SELECTED.
993
994 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
995 ,p_init_msg_list => l_init_msg_list
996 ,x_return_status => l_return_status
997 ,x_msg_count => l_msg_count
998 ,x_msg_data => l_msg_data
999 ,p_cash_receipt_id => l_cash_receipt_id
1000 -- ,p_amount_applied => l_amount_unapplied -- not required. we will just write off remaining rcpt.
1001 ,p_apply_date => l_xcrv_rec.receipt_date
1002 ,p_apply_gl_date => l_gl_date
1003 ,p_org_id => mo_global.get_current_org_id()
1004 );
1005
1006 x_return_status := l_return_status;
1007 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1008 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1009 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1010 RAISE OKL_API.G_EXCEPTION_ERROR;
1011 END IF;
1012
1013 END IF; -- ELSE LEAVE CASH AS UNAPPLIED.
1014
1015 END IF;
1016
1017 x_cash_receipt_id := l_cash_receipt_id;
1018 x_return_status := l_return_status;
1019
1020 EXCEPTION
1021
1022 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1023 x_return_status := OKC_API.G_RET_STS_ERROR;
1024
1025
1026 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1027 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1028 (
1029 l_api_name,
1030 G_PKG_NAME,
1031 'OKC_API.G_RET_STS_ERROR',
1032 x_msg_count,
1033 x_msg_data,
1034 '_PVT'
1035 );
1036
1037 WHEN OTHERS THEN
1038 NULL;
1039 /* -- fix bug number 2439881
1040 Okl_api.set_message( p_app_name => g_app_name
1041 , p_msg_name => g_unexpected_error
1042 , p_token1 => g_sqlcode_token
1043 , p_token1_value => SQLCODE
1044 , p_token2 => g_sqlerrm_token
1045 , p_token2_value => SQLERRM
1046 ) ;
1047
1048 */
1049 END PAYMENT_RECEIPT;
1050
1051 END OKL_CASH_RECEIPT;