[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_MAN_RCT_HANDLE_PVT
Source
1 PACKAGE BODY OKL_BPD_MAN_RCT_HANDLE_PVT AS
2 /* $Header: OKLRMRHB.pls 120.8 2007/08/02 07:12:11 dcshanmu noship $ */
3
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 -- Message constants
10 G_MSG_BPD_INV_APPL_AMT VARCHAR2(30) := 'OKL_BPD_INV_APPL_AMT';
11 G_MSG_BPD_NO_AMT_TO_APPLY VARCHAR2(30) := 'OKL_BPD_NO_AMT_TO_APPLY';
12 G_MSG_BPD_APP_NEG_UNAPP VARCHAR2(30) := 'OKL_BPD_RCPT_ALLOC_ERR';
13 ------------------------------------------------------------------------------
14 -- Start of Comments
15 -- Created By : abindal
16 -- Procedure Name : man_receipt_apply
17 -- Description : Procedure functions to apply the receipts to the relevant
18 -- invoices using the AR procedures. The steps involved are:
19 -- (i) Insert a row in OKL_EXT_CSH_APPS_B/TL table
20 -- (i) Place all the On-Acc Amt in UnApply Amt
21 -- (ii) Unapply the previous applied amount. And add the current
22 -- applied amount to the previous applied amount.
23 -- (iv) Apply the receipt amount to all the invoices
24 -- (v) If there is any amount left on the receipt, place it
25 -- on account.
26 -- (vi) If there is any part of the intial UnApply amount that
27 -- is not used, return the part unused to UnApply Amount.
28 -- (vii) If the receipt is Adanced, update the FULLY_APPLIED_FLAG
29 -- of the OKL_EXT_CSH_APPS_B table
30 -- Dependencies :
31 -- Parameters :
32 -- Version : 1.0
33 -- End of Comments
34 -----------------------------------------------------------------------------
35 PROCEDURE man_receipt_apply ( p_api_version IN NUMBER
36 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
37 ,x_return_status OUT NOCOPY VARCHAR2
38 ,x_msg_count OUT NOCOPY NUMBER
39 ,x_msg_data OUT NOCOPY VARCHAR2
40 ,p_xcav_tbl IN xcav_tbl_type
41 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL -- cash receipt id
42 ,p_receipt_amount IN AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
43 ,p_receipt_date IN AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT NULL
44 ,p_receipt_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
45 ) IS
46
47 ---------------------------
48 -- DECLARE Local Variables
49 ---------------------------
50
51 l_api_version NUMBER := 1.0;
52 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
53 l_return_status VARCHAR2(1);
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(2000);
56 l_api_name CONSTANT VARCHAR2(30) := 'man_receipt_apply';
57
58 l_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
59
60 l_receipt_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT NULL;
61 l_receipt_type OKL_TRX_CSH_RECEIPT_V.RECEIPT_TYPE%TYPE DEFAULT NULL;
62 l_receipt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT p_receipt_date;
63 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_receipt_currency;
64 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
65
66 l_xcr_id NUMBER;
67
68 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
69 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
70
71 l_trans_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL; -- entered currency code
72
73 l_init_on_acc_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
74 l_tot_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT 0;
75 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
76 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
77 l_tot_amount_to_apply AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT 0;
78 l_unapplied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
79 l_outstanding_amt AR_PAYMENT_SCHEDULES_ALL.AMOUNT_DUE_REMAINING%TYPE DEFAULT 0;
80
81 l_prev_applied_amt AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
82 l_remaining_amt NUMBER DEFAULT NULL;
83
84 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
85
86 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
87 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
88
89 i NUMBER DEFAULT NULL;
90 l_exit_loop NUMBER DEFAULT 0;
91
92 l_counter NUMBER;
93 l_unapply VARCHAR2(3);
94
95 l_record_count NUMBER DEFAULT NULL;
96 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
97 l_chk_on_acc_amt AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
98 l_apply_amt_error_flag BOOLEAN := FALSE;
99 -------------------------------------------------------------------------------
100 -- DECLARE Record/Table Types
101 -------------------------------------------------------------------------------
102
103 -- External Trans
104 lp_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
105 lx_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
106
107 lp_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
108 lx_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
109
110 -------------------------------------------------------------------------------
111 -- DEFINE CURSORS
112 -------------------------------------------------------------------------------
113 --get gl date from open accounting period
114 CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
115 SELECT trunc(cp_date) gl_date, 1 Counter
116 FROM gl_period_statuses
117 WHERE application_id = 222
118 -- BEGIN abindal bug 4356410 --
119 AND closing_status IN ('F','O')
120 -- END abindal bug 4356410 --
121 AND ledger_id = okl_accounting_util.get_set_of_books_id
122 AND trunc(cp_date) between start_date and end_date
123 AND adjustment_period_flag = 'N'
124 UNION
125 SELECT MAX(end_date) gl_date, 2 Counter
126 FROM gl_period_statuses
127 WHERE application_id = 222
128 AND ledger_id = okl_accounting_util.get_set_of_books_id
129 AND closing_status IN ('F','O')
130 AND end_date <= trunc(cp_date)
131 AND adjustment_period_flag = 'N'
132 UNION
133 SELECT MIN(start_date) gl_date, 3 Counter
134 FROM gl_period_statuses
135 WHERE application_id = 222
136 AND ledger_id = okl_accounting_util.get_set_of_books_id
137 AND closing_status IN ('F','O')
138 AND start_date >= trunc(cp_date)
139 AND adjustment_period_flag = 'N'
140 )
141 where gl_date is not null
142 order by counter;
143 -------------------------------------------------------------------------------
144 -- Get the receipt information
145 CURSOR c_get_rcpt_details(cp_cash_rcpt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE) IS
146 SELECT
147 RCT.CHECK_NUMBER RECEIPT_NUMBER
148 , RCT.RECEIPT_TYPE
149 , RCT.AMOUNT
150 , NULL ID
151 FROM
152 OKL_TRX_CSH_RECEIPT_V RCT
153 -- , OKL_EXT_CSH_RCPTS_V XCR
154 WHERE
155 -- XCR.RCT_ID = RCT.ID
156 -- AND XCR.ICR_ID = cp_cash_rcpt_id;
157 RCT.CASH_RECEIPT_ID = cp_cash_rcpt_id;
158 -------------------------------------------------------------------------------
159
160 -- verify on account receipt amount
161 CURSOR c_ver_on_acct_amt(cp_csh_rcpt_id IN NUMBER) IS
162 SELECT NVL(SUM(AMOUNT_APPLIED),0)
163 FROM AR_RECEIVABLE_APPLICATIONS_ALL
164 WHERE STATUS = 'ACC'
165 AND CASH_RECEIPT_ID = cp_csh_rcpt_id;
166
167 -------------------------------------------------------------------------------
168
169 -- verify unapplied receipt amount
170 CURSOR c_ver_unapp_amt(cp_csh_rcpt_id IN NUMBER) IS
171 SELECT NVL(SUM(AMOUNT_APPLIED),0)
172 FROM AR_RECEIVABLE_APPLICATIONS_ALL
173 WHERE STATUS = 'UNAPP'
174 AND CASH_RECEIPT_ID = cp_csh_rcpt_id;
175
176 -------------------------------------------------------------------------------
177
178 -- check receipt applic
179 CURSOR c_ver_dup_applic( cp_customer_trx_id IN NUMBER
180 ,cp_cash_receipt_id IN NUMBER) IS
181 SELECT NVL(AMOUNT_APPLIED,0), NVL(RECEIVABLE_APPLICATION_ID,0)
182 FROM AR_RECEIVABLE_APPLICATIONS_ALL
183 WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
184 AND CASH_RECEIPT_ID = cp_cash_receipt_id
185 AND STATUS = 'APP'
186 ORDER BY CREATION_DATE desc;
187
188 -------------------------------------------------------------------------------
189 -- Gets the Invoice details for the stream
190 CURSOR c_get_rec_inv_dtls( cp_stream_id IN OKL_CNSLD_AR_STRMS_V.ID%TYPE ) IS
191 SELECT CNSLD.RECEIVABLES_INVOICE_ID
192 FROM OKL_CNSLD_AR_STRMS_V CNSLD
193 WHERE CNSLD.ID = cp_stream_id;
194 -------------------------------------------------------------------------------
195
196 -- Obtain the payment details for the invoice
197 CURSOR c_get_pymt_dtls(cp_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE) IS
198 SELECT
199 APS.AMOUNT_DUE_REMAINING
200 FROM
201 AR_PAYMENT_SCHEDULES_ALL APS
202 WHERE
203 APS.CUSTOMER_TRX_ID = cp_customer_trx_id;
204 -------------------------------------------------------------------------------
205 BEGIN
206 x_return_status := OKL_API.G_RET_STS_SUCCESS;
207
208 IF(NVL(l_debug_enabled,'N')='Y') THEN
209 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRMRHB.pls call MAN_RECEIPT_APPLY');
210 END IF;
211
212 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
213 x_return_status := OKL_API.START_ACTIVITY(
214 p_api_name => l_api_name
215 , p_pkg_name => G_PKG_NAME
216 , p_init_msg_list => p_init_msg_list
217 , l_api_version => l_api_version
218 , p_api_version => p_api_version
219 , p_api_type => G_API_TYPE
220 , x_return_status => x_return_status);
221 -- check if activity started successfully
222 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
223 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
224 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
225 RAISE OKL_API.G_EXCEPTION_ERROR;
226 END IF;
227
228 lp_xcav_tbl := p_xcav_tbl;
229
230 l_record_count := lp_xcav_tbl.COUNT;
231
232 -- Obtain the receipt details
233 OPEN c_get_rcpt_details(l_cash_receipt_id);
234 FETCH c_get_rcpt_details INTO l_receipt_number
235 , l_receipt_type
236 , l_receipt_amount
237 , l_xcr_id;
238 CLOSE c_get_rcpt_details;
239
240 -- Obtain the On-Account Amount on the receipt before start of processing
241 OPEN c_ver_on_acct_amt(l_cash_receipt_id);
242 FETCH c_ver_on_acct_amt INTO l_init_on_acc_amount;
243 CLOSE c_ver_on_acct_amt;
244 IF l_init_on_acc_amount IS NULL THEN
245 l_init_on_acc_amount := 0;
246 END IF;
247
248 OPEN c_ver_unapp_amt(l_cash_receipt_id);
249 FETCH c_ver_unapp_amt INTO l_unapplied_amount;
250 CLOSE c_ver_unapp_amt;
251 IF l_unapplied_amount IS NULL THEN
252 l_unapplied_amount := 0;
253 END IF;
254
255 l_unapply := 'N';
256 l_tot_applied_amount := 0;
257
258 IF l_record_count > 0 THEN
259
260 ------------------------------------------
261 -- Validations
262 ------------------------------------------
263 l_tot_amount_to_apply := 0;
264 FOR i IN lp_xcav_tbl.FIRST..lp_xcav_tbl.LAST
265 LOOP
266 -- Get the receivable invoice ID
267 OPEN c_get_rec_inv_dtls(lp_xcav_tbl(i).lsm_id);
268 FETCH c_get_rec_inv_dtls INTO l_customer_trx_id;
269 CLOSE c_get_rec_inv_dtls;
270
271 OPEN c_get_pymt_dtls(l_customer_trx_id);
272 FETCH c_get_pymt_dtls INTO l_outstanding_amt;
273 CLOSE c_get_pymt_dtls;
274
275 l_tot_amount_to_apply := l_tot_amount_to_apply +
276 lp_xcav_tbl(i).amount_applied;
277
278 IF (lp_xcav_tbl(i).amount_applied > l_outstanding_amt ) THEN
279 OKL_API.set_message( p_app_name => G_APP_NAME,
280 p_msg_name => G_MSG_BPD_INV_APPL_AMT,
281 p_token1 => 'RCT',
282 p_token1_value => l_receipt_number,
283 p_token2 => 'INV',
284 p_token2_value => lp_xcav_tbl(i).invoice_number);
285 l_apply_amt_error_flag := TRUE;
286 END IF;
287
288 END LOOP; -- end of for loop
289
290 -- If there is no amount to apply
291 IF ( l_tot_amount_to_apply = 0) THEN
292 OKL_API.set_message( p_app_name => G_APP_NAME,
293 p_msg_name => G_MSG_BPD_NO_AMT_TO_APPLY,
294 p_token1 => 'RCT',
295 p_token1_value => l_receipt_number
296 );
297 RAISE OKL_API.G_EXCEPTION_ERROR;
298 END IF;
299
300 -- If there are records with applied amount exceeding the outstanding amount
301 IF (l_apply_amt_error_flag) THEN
302 RAISE OKL_API.G_EXCEPTION_ERROR;
303 END IF;
304
305 -- If the total amount to be applied is greater then the sum of on-account amount
306 -- and unapplied amount then throw an error.
307 if(l_tot_amount_to_apply > (l_init_on_acc_amount + l_unapplied_amount)) THEN
308 OKL_API.set_message( p_app_name => G_APP_NAME,
309 p_msg_name => G_MSG_BPD_APP_NEG_UNAPP
310 );
311 RAISE OKL_API.G_EXCEPTION_ERROR;
312 END IF;
313
314 i := lp_xcav_tbl.FIRST;
315 LOOP
316 l_trans_currency_code := lp_xcav_tbl(i).invoice_currency_code;
317
318 OPEN c_get_rec_inv_dtls(lp_xcav_tbl(i).lsm_id);
319 FETCH c_get_rec_inv_dtls INTO l_customer_trx_id;
320 CLOSE c_get_rec_inv_dtls;
321
322 ----------------------------------------------------------
323 -- Initialization of Cash appln Record p_xcav_rec begin
324 ----------------------------------------------------------
325
326 IF l_receipt_currency_code = l_trans_currency_code THEN
327 l_applied_amount := lp_xcav_tbl(i).amount_applied;
328 l_applied_amount_from := NULL;
329 ELSE
330 l_applied_amount := lp_xcav_tbl(i).amount_applied;
331 -- Convert receipt currency to invoice currency if different
332 l_conversion_rate := okl_accounting_util.get_curr_con_rate
333 ( l_receipt_currency_code
334 ,l_trans_currency_code
335 ,trunc(SYSDATE)
336 ,'Corporate'
337 );
338
339 IF l_conversion_rate IN (0,-1) THEN
340 -- Message Text: No exchange rate defined
341 x_return_status := OKL_API.G_RET_STS_ERROR;
342 OKL_API.set_message( p_app_name => G_APP_NAME,
343 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
344 RAISE OKL_API.G_EXCEPTION_ERROR;
345 END IF;
346
347 l_applied_amount_from := lp_xcav_tbl(i).amount_applied/l_conversion_rate;
348
349 lp_xcav_tbl(i).amount_applied_from := l_applied_amount_from;
350 lp_xcav_tbl(i).trans_to_receipt_rate := l_conversion_rate;
351 END IF; -- end of check for receipt currency = transaction currency
352
353 IF (lp_xcav_tbl(i).trx_date >= l_receipt_date) AND (lp_xcav_tbl(i).trx_date >= SYSDATE) THEN
354 l_apply_date := lp_xcav_tbl(i).trx_date;
355 ELSIF (l_receipt_date >= lp_xcav_tbl(i).trx_date) AND (l_receipt_date >= SYSDATE) THEN
356 l_apply_date := l_receipt_date;
357 ELSE
358 l_apply_date := SYSDATE;
359 END IF; -- end of comparison of system date, receipt date and AR Invoice transaction date
360
361 OPEN c_get_gl_date(l_apply_date);
362 FETCH c_get_gl_date INTO l_gl_date, l_counter;
363
364 IF c_get_gl_date%NOTFOUND THEN
365 CLOSE c_get_gl_date;
366 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME,
367 p_msg_name => 'OKL_BPD_GL_PERIOD_ERROR',
368 p_token1 => 'TRX_DATE',
369 p_token1_value => TRUNC(lp_xcav_tbl(i).trx_date));
370
371 l_return_status := OKL_API.G_RET_STS_ERROR;
372 RAISE OKL_API.G_EXCEPTION_ERROR;
373 END IF;
374 CLOSE c_get_gl_date;
375
376 lp_xcav_tbl(i).xcr_id_details := l_xcr_id;
377 lp_xcav_tbl(i).org_id := l_org_id;
378 ----------------------------------------------------------
379 -- Initialization of Cash appln Record p_xcav_rec end
380 ----------------------------------------------------------
381 OKL_XCA_PVT.insert_row( p_api_version => l_api_version
382 ,p_init_msg_list => l_init_msg_list
383 ,x_return_status => l_return_status
384 ,x_msg_count => l_msg_count
385 ,x_msg_data => l_msg_data
386 ,p_xcav_rec => lp_xcav_tbl(i)
387 ,x_xcav_rec => lx_xcav_rec
388 );
389
390 x_return_status := l_return_status;
391 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
392 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
393 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
394 RAISE OKL_API.G_EXCEPTION_ERROR;
395 END IF;
396
397 --------------------------------------------------------
398 -- AR processing begin
399 --------------------------------------------------------
400 -- Process only if there is atleast some amount to apply
401 IF l_applied_amount = 0 THEN
402 NULL;
403 ELSE
404 -- unapply cash from customers account once only
405 IF l_unapply = 'N' THEN
406 -- Curosr to check if there is any Amount on account to unapply
407 OPEN c_ver_on_acct_amt (l_cash_receipt_id);
408 FETCH c_ver_on_acct_amt INTO l_chk_on_acc_amt;
409 CLOSE c_ver_on_acct_amt ;
410
411 -- Unapply only if there is any amount on account
412 IF NVL(l_chk_on_acc_amt,0) <> 0 THEN
413 AR_RECEIPT_API_PUB.unapply_on_account(
414 p_api_version => l_api_version
415 ,p_init_msg_list => p_init_msg_list
416 ,x_return_status => l_return_status
417 ,x_msg_count => l_msg_count
418 ,x_msg_data => l_msg_data
419 ,p_cash_receipt_id => l_cash_receipt_id
420 ,p_reversal_gl_date => null
421 );
422
423 x_return_status := l_return_status;
424
425 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
427 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
428 RAISE OKL_API.G_EXCEPTION_ERROR;
429 END IF;
430 END IF;
431 l_unapply := 'Y';
432 END IF; -- end of check for unApply flag
433
434 OPEN c_ver_dup_applic (l_customer_trx_id, l_cash_receipt_id);
435 FETCH c_ver_dup_applic INTO l_prev_applied_amt, l_receivable_application_id;
436 CLOSE c_ver_dup_applic;
437
438 IF l_prev_applied_amt > 0 AND l_receivable_application_id IS NOT NULL THEN
439
440 AR_RECEIPT_API_PUB.Unapply( p_api_version => l_api_version
441 ,p_init_msg_list => l_init_msg_list
442 ,x_return_status => l_return_status
443 ,x_msg_count => l_msg_count
444 ,x_msg_data => l_msg_data
445 ,p_cash_receipt_id => l_cash_receipt_id
446 ,p_customer_trx_id => l_customer_trx_id
447 ,p_receivable_application_id => l_receivable_application_id
448 ,p_reversal_gl_date => null
449 );
450
451 x_return_status := l_return_status;
452
453 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
454 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
455 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
456 RAISE OKL_API.G_EXCEPTION_ERROR;
457 END IF;
458
459 l_applied_amount := l_applied_amount + l_prev_applied_amt;
460
461 END IF;
462
463 AR_RECEIPT_API_PUB.apply( p_api_version => l_api_version
464 ,p_init_msg_list => l_init_msg_list
465 ,x_return_status => l_return_status
466 ,x_msg_count => l_msg_count
467 ,x_msg_data => l_msg_data
468 ,p_customer_trx_id => l_customer_trx_id
469 ,p_amount_applied => l_applied_amount -- in func/rcpt currency.
470 ,p_amount_applied_from => l_applied_amount_from -- in rcpt_currency
471 ,p_apply_gl_date => l_gl_date
472 ,p_apply_date => l_apply_date
473 ,p_cash_receipt_id => l_cash_receipt_id
474 );
475
476 x_return_status := l_return_status;
477
478 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
479 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
480 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
481 RAISE OKL_API.G_EXCEPTION_ERROR;
482 END IF;
483
484 -- Add up the applied amounts for calculating the unused part
485 -- of On-Account amount at the end of the process.
486 l_tot_applied_amount := l_tot_applied_amount + lp_xcav_tbl(i).amount_applied;
487 END IF; -- end of check if the amount to be applied is not zero
488
489 EXIT WHEN (i = lp_xcav_tbl.LAST);
490
491 i := i + 1;
492
493 END LOOP;
494
495 END IF;
496 l_remaining_amt := l_init_on_acc_amount - l_tot_applied_amount;
497
498 -- Get the part of the intial On_account amount which was not used during
499 -- application process, from the Unapplied amount. This is done to return any
500 -- amount that was on account during the start of this procedure that was not
501 -- used during the application of receipts to invoices.
502 IF l_remaining_amt > 0 THEN
503
504 OPEN c_get_gl_date(l_receipt_date);
505 FETCH c_get_gl_date INTO l_gl_date, l_counter;
506
507 IF c_get_gl_date%NOTFOUND THEN
508 CLOSE c_get_gl_date;
509 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME,
510 p_msg_name => 'OKL_BPD_GL_PERIOD_ERROR',
511 p_token1 => 'TRX_DATE',
512 p_token1_value => TRUNC(lp_xcav_tbl(i).trx_date));
513
514 l_return_status := OKL_API.G_RET_STS_ERROR;
515 RAISE OKL_API.G_EXCEPTION_ERROR;
516 END IF;
517 CLOSE c_get_gl_date;
518
519 AR_RECEIPT_API_PUB.Apply_on_account( p_api_version => l_api_version
520 ,p_init_msg_list => l_init_msg_list
521 ,x_return_status => l_return_status
522 ,x_msg_count => l_msg_count
523 ,x_msg_data => l_msg_data
524 ,p_cash_receipt_id => l_cash_receipt_id
525 ,p_amount_applied => l_remaining_amt
526 ,p_apply_date => l_receipt_date
527 ,p_apply_gl_date => l_gl_date
528 );
529
530 x_return_status := l_return_status;
531
532 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
533 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
534 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
535 RAISE OKL_API.G_EXCEPTION_ERROR;
536 END IF;
537
538 END IF; -- end of check for unused On-Acc amount
539
540 -- Check if the receipt is of type ADVANCED
541 IF l_receipt_type = 'ADV' THEN
542 lp_xcrv_rec.id := l_xcr_id;
543 -- Check if all the amount has been applied for the receipt
544 IF l_receipt_amount = l_applied_amount THEN
545 lp_xcrv_rec.fully_applied_flag := 'Y';
546 ELSE
547 lp_xcrv_rec.fully_applied_flag := 'N';
548 END IF;
549 -- Update the FULLY_APPLIED_FLAG colum for Advance receipts
550 OKL_XCR_PUB.UPDATE_EXT_CSH_TXNS( p_api_version => l_api_version
551 ,p_init_msg_list => l_init_msg_list
552 ,x_return_status => l_return_status
553 ,x_msg_count => l_msg_count
554 ,x_msg_data => l_msg_data
555 ,p_xcrv_rec => lp_xcrv_rec
556 ,x_xcrv_rec => lx_xcrv_rec
557 );
558
559 x_return_status := l_return_status;
560 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
561 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
562 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
563 RAISE OKL_API.G_EXCEPTION_ERROR;
564 END IF;
565 END IF; -- end of check for Advance receipt
566
567 -- commit the savepoint
568 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
569
570 IF(NVL(l_debug_enabled,'N')='Y') THEN
571 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRMRHB.pls call MAN_RECEIPT_APPLY');
572 END IF;
573
574 EXCEPTION
575 WHEN OKL_API.G_EXCEPTION_ERROR THEN
576 x_return_status := OKL_API.HANDLE_EXCEPTIONS
577 (
578 l_api_name,
579 G_PKG_NAME,
580 'OKL_API.G_RET_STS_ERROR',
581 x_msg_count,
582 x_msg_data,
583 '_PVT'
584 );
585
586 WHEN OTHERS THEN
587 x_return_status := OKL_API.HANDLE_EXCEPTIONS
588 (
589 l_api_name,
590 G_PKG_NAME,
591 'OTHERS',
592 x_msg_count,
593 x_msg_data,
594 '_PVT'
595 );
596 Okl_api.set_message( p_app_name => g_app_name
597 , p_msg_name => g_unexpected_error
598 , p_token1 => g_sqlcode_token
599 , p_token1_value => SQLCODE
600 , p_token2 => g_sqlerrm_token
601 , p_token2_value => SQLERRM
602 ) ;
603
604 END man_receipt_apply;
605
606
607
608
609 ------------------------------------------------------------------------------
610 -- Start of Comments
611 -- Created By : abindal
612 -- Procedure Name : man_receipt_unapply
613 -- Description : Procedure functions to unapply the invioce amount for a
614 -- corresponding receipt using the AR procedures.
615 -- The steps involved are:
616 -- (i) Insert a row in OKL_EXT_CSH_APPS_B/TL table
617 -- (ii) Unapply the invoice amount and add it to the receipt
618 -- unapplied amount.
619 -- (iii) If the receipt is Adanced, update the FULLY_APPLIED_FLAG
620 -- of the OKL_EXT_CSH_APPS_B table
621 -- Dependencies :
622 -- Parameters :
623 -- Version : 1.0
624 -- End of Comments
625 -----------------------------------------------------------------------------
626 PROCEDURE man_receipt_unapply ( p_api_version IN NUMBER
627 ,p_init_msg_list IN VARCHAR2 DEFAULT OkL_Api.G_FALSE
628 ,x_return_status OUT NOCOPY VARCHAR2
629 ,x_msg_count OUT NOCOPY NUMBER
630 ,x_msg_data OUT NOCOPY VARCHAR2
631 ,p_xcav_tbl IN xcav_tbl_type
632 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL -- cash receipt id
633 ,p_receipt_date IN AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT NULL
634 ) IS
635
636 ---------------------------
637 -- DECLARE Local Variables
638 ---------------------------
639
640 l_api_version NUMBER := 1.0;
641 l_init_msg_list VARCHAR2(1) := OKL_API.G_FALSE;
642 l_return_status VARCHAR2(1);
643 l_msg_count NUMBER;
644 l_msg_data VARCHAR2(2000);
645 l_api_name CONSTANT VARCHAR2(30) := 'man_receipt_unapply';
646
647 l_receipt_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT NULL;
648 l_receipt_type OKL_TRX_CSH_RECEIPT_V.RECEIPT_TYPE%TYPE DEFAULT NULL;
649 l_receipt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT p_receipt_date;
650 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
651 l_receipt_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
652 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
653 l_total_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
654 l_trans_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL; -- entered currency code
655 l_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
656 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
657
658 l_xcr_id NUMBER;
659 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
660 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
661
662 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
663 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
664 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
665 i NUMBER DEFAULT NULL;
666 l_counter NUMBER;
667 l_record_count NUMBER DEFAULT NULL;
668 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
669
670 -------------------------------------------------------------------------------
671 -- DECLARE Record/Table Types
672 -------------------------------------------------------------------------------
673
674 -- External Trans
675 lp_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
676 lx_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
677
678 lp_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
679 lx_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
680
681 -------------------------------------------------------------------------------
682 -- DEFINE CURSORS
683 -------------------------------------------------------------------------------
684 -- Get the receipt information
685 CURSOR c_get_rcpt_details(cp_cash_rcpt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE) IS
686 SELECT
687 RCT.CHECK_NUMBER RECEIPT_NUMBER
688 , RCT.RECEIPT_TYPE
689 , RCT.AMOUNT
690 , NULL ID
691 ,RCT.CURRENCY_CODE
692 FROM
693 OKL_TRX_CSH_RECEIPT_V RCT
694 -- , OKL_EXT_CSH_RCPTS_V XCR
695 WHERE
696 -- XCR.RCT_ID = RCT.ID
697 -- AND XCR.ICR_ID = cp_cash_rcpt_id;
698 RCT.CASH_RECEIPT_ID = cp_cash_rcpt_id;
699 -------------------------------------------------------------------------------
700
701 -- check receipt applic
702 CURSOR c_ver_dup_applic( cp_customer_trx_id IN NUMBER
703 ,cp_cash_receipt_id IN NUMBER) IS
704 SELECT NVL(RECEIVABLE_APPLICATION_ID,0)
705 FROM AR_RECEIVABLE_APPLICATIONS_ALL
706 WHERE APPLIED_CUSTOMER_TRX_ID = cp_customer_trx_id
707 AND CASH_RECEIPT_ID = cp_cash_receipt_id
708 AND STATUS = 'APP'
709 ORDER BY CREATION_DATE desc;
710
711 -------------------------------------------------------------------------------
712 -- Gets the Invoice details for the stream
713 CURSOR c_get_rec_inv_dtls( cp_stream_id IN OKL_CNSLD_AR_STRMS_V.ID%TYPE ) IS
714 SELECT CNSLD.RECEIVABLES_INVOICE_ID
715 FROM OKL_CNSLD_AR_STRMS_V CNSLD
716 WHERE CNSLD.ID = cp_stream_id;
717 -------------------------------------------------------------------------------
718
719 -- verify receipt applied amount
720 CURSOR c_ver_app_amt(cp_csh_rcpt_id IN NUMBER) IS
721 SELECT NVL(SUM(AMOUNT_APPLIED),0)
722 FROM AR_RECEIVABLE_APPLICATIONS_ALL
723 WHERE STATUS = 'APP'
724 AND CASH_RECEIPT_ID = cp_csh_rcpt_id;
725 -------------------------------------------------------------------------------
726
727 BEGIN
728 x_return_status := OKL_API.G_RET_STS_SUCCESS;
729
730 IF(NVL(l_debug_enabled,'N')='Y') THEN
731 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'begin debug OKLRMRHB.pls call MAN_RECEIPT_UNAPPLY');
732 END IF;
733
734 -- call START_ACTIVITY to create savepoint, check compatibility and initialize message list
735 x_return_status := OKL_API.START_ACTIVITY(
736 p_api_name => l_api_name
737 , p_pkg_name => G_PKG_NAME
738 , p_init_msg_list => p_init_msg_list
739 , l_api_version => l_api_version
740 , p_api_version => p_api_version
741 , p_api_type => G_API_TYPE
742 , x_return_status => x_return_status);
743 -- check if activity started successfully
744 IF(x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
746 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
747 RAISE OKL_API.G_EXCEPTION_ERROR;
748 END IF;
749
750 lp_xcav_tbl := p_xcav_tbl;
751
752 l_record_count := lp_xcav_tbl.COUNT;
753
754 -- Obtain the receipt details
755 OPEN c_get_rcpt_details(l_cash_receipt_id);
756 FETCH c_get_rcpt_details INTO l_receipt_number
757 , l_receipt_type
758 , l_receipt_amount
759 , l_xcr_id
760 , l_receipt_currency;
761 CLOSE c_get_rcpt_details;
762
763 i := lp_xcav_tbl.FIRST;
764 LOOP
765
766 l_trans_currency_code := lp_xcav_tbl(i).invoice_currency_code;
767 OPEN c_get_rec_inv_dtls(lp_xcav_tbl(i).lsm_id);
768 FETCH c_get_rec_inv_dtls INTO l_customer_trx_id;
769 CLOSE c_get_rec_inv_dtls;
770
771 ----------------------------------------------------------
772 -- Initialization of Cash appln Record p_xcav_rec begin
773 ----------------------------------------------------------
774 IF l_receipt_currency = l_trans_currency_code THEN
775 l_applied_amount := lp_xcav_tbl(i).amount_applied ;
776 l_applied_amount_from := NULL;
777 ELSE
778 l_applied_amount := lp_xcav_tbl(i).amount_applied;
779 -- Convert receipt currency to invoice currency if different
780 l_conversion_rate := okl_accounting_util.get_curr_con_rate
781 ( l_receipt_currency
782 ,l_trans_currency_code
783 ,trunc(SYSDATE)
784 ,'Corporate'
785 );
786
787 IF l_conversion_rate IN (0,-1) THEN
788 -- Message Text: No exchange rate defined
789 x_return_status := OKL_API.G_RET_STS_ERROR;
790 OKL_API.set_message( p_app_name => G_APP_NAME,
791 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
792 RAISE OKL_API.G_EXCEPTION_ERROR;
793 END IF;
794
795 l_applied_amount_from := lp_xcav_tbl(i).amount_applied/l_conversion_rate;
796
797 lp_xcav_tbl(i).amount_applied_from := l_applied_amount_from * -1;
798 lp_xcav_tbl(i).trans_to_receipt_rate := l_conversion_rate;
799 END IF; -- end of check for receipt currency = transaction currency
800
801 lp_xcav_tbl(i).amount_applied := l_applied_amount * -1;
802 lp_xcav_tbl(i).xcr_id_details := l_xcr_id;
803 lp_xcav_tbl(i).org_id := l_org_id;
804
805 OKL_XCA_PVT.insert_row( p_api_version => l_api_version
806 ,p_init_msg_list => l_init_msg_list
807 ,x_return_status => l_return_status
808 ,x_msg_count => l_msg_count
809 ,x_msg_data => l_msg_data
810 ,p_xcav_rec => lp_xcav_tbl(i)
811 ,x_xcav_rec => lx_xcav_rec
812 );
813
814 x_return_status := l_return_status;
815 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
816 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
817 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
818 RAISE OKL_API.G_EXCEPTION_ERROR;
819 END IF;
820
821 --------------------------------------------------------
822 -- AR processing begin
823 --------------------------------------------------------
824
825 OPEN c_ver_dup_applic (l_customer_trx_id, l_cash_receipt_id);
826 FETCH c_ver_dup_applic INTO l_receivable_application_id;
827 CLOSE c_ver_dup_applic;
828
829 AR_RECEIPT_API_PUB.Unapply( p_api_version => l_api_version
830 ,p_init_msg_list => l_init_msg_list
831 ,x_return_status => l_return_status
832 ,x_msg_count => l_msg_count
833 ,x_msg_data => l_msg_data
834 ,p_cash_receipt_id => l_cash_receipt_id
835 ,p_customer_trx_id => l_customer_trx_id
836 ,p_receivable_application_id => l_receivable_application_id
837 ,p_reversal_gl_date => null
838 );
839
840 x_return_status := l_return_status;
841 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
842 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
843 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
844 RAISE OKL_API.G_EXCEPTION_ERROR;
845 END IF;
846
847 EXIT WHEN (i = lp_xcav_tbl.LAST);
848 i := i + 1;
849 END LOOP;
850
851 -- Check if the receipt is of type ADVANCED
852 IF l_receipt_type = 'ADV' THEN
853 lp_xcrv_rec.id := l_xcr_id;
854 OPEN c_ver_app_amt(l_cash_receipt_id);
855 FETCH c_ver_app_amt INTO l_total_applied_amount;
856 CLOSE c_ver_app_amt;
857 IF(l_total_applied_amount = l_receipt_amount) THEN
858 lp_xcrv_rec.fully_applied_flag := 'Y';
859 ELSE
860 lp_xcrv_rec.fully_applied_flag := 'N';
861 END IF;
862
863 -- Update the FULLY_APPLIED_FLAG colum for Advance receipts
864 OKL_XCR_PUB.UPDATE_EXT_CSH_TXNS( p_api_version => l_api_version
865 ,p_init_msg_list => l_init_msg_list
866 ,x_return_status => l_return_status
867 ,x_msg_count => l_msg_count
868 ,x_msg_data => l_msg_data
869 ,p_xcrv_rec => lp_xcrv_rec
870 ,x_xcrv_rec => lx_xcrv_rec
871 );
872
873 x_return_status := l_return_status;
874 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
875 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
876 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
877 RAISE OKL_API.G_EXCEPTION_ERROR;
878 END IF;
879 END IF; -- end of check for Advance receipt
880
881 -- commit the savepoint
882 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
883
884 IF(NVL(l_debug_enabled,'N')='Y') THEN
885 okl_debug_pub.log_debug(FND_LOG.LEVEL_PROCEDURE,l_module,'end debug OKLRMRHB.pls call MAN_RECEIPT_UNAPPLY');
886 END IF;
887
888 EXCEPTION
889 WHEN OKL_API.G_EXCEPTION_ERROR THEN
890 x_return_status := OKL_API.HANDLE_EXCEPTIONS
891 (
892 l_api_name,
893 G_PKG_NAME,
894 'OKL_API.G_RET_STS_ERROR',
895 x_msg_count,
896 x_msg_data,
897 '_PVT'
898 );
899
900 WHEN OTHERS THEN
901 x_return_status := OKL_API.HANDLE_EXCEPTIONS
902 (
903 l_api_name,
904 G_PKG_NAME,
905 'OTHERS',
906 x_msg_count,
907 x_msg_data,
908 '_PVT'
909 );
910 Okl_api.set_message( p_app_name => g_app_name
911 , p_msg_name => g_unexpected_error
912 , p_token1 => g_sqlcode_token
913 , p_token1_value => SQLCODE
914 , p_token2 => g_sqlerrm_token
915 , p_token2_value => SQLERRM
916 ) ;
917
918 END man_receipt_unapply;
919
920 END OKL_BPD_MAN_RCT_HANDLE_PVT;