[Home] [Help]
PACKAGE BODY: APPS.OKL_BPD_ADVANCED_CASH_APP_PVT
Source
1 PACKAGE BODY OKL_BPD_ADVANCED_CASH_APP_PVT AS
2 /* $Header: OKLRAVCB.pls 120.44.12010000.3 2009/01/30 04:16:31 nikshah ship $ */
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 -- End of wraper code generated automatically by Debug code generator
9
10 PROCEDURE log_file(p_message IN VARCHAR2) IS
11 BEGIN
12 FND_FILE.PUT_LINE (FND_FILE.LOG, p_message);
13 END;
14
15 PROCEDURE migrate_Applications ( p_api_version IN NUMBER
16 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
17 ,x_return_status OUT NOCOPY VARCHAR2
18 ,x_msg_count OUT NOCOPY NUMBER
19 ,x_msg_data OUT NOCOPY VARCHAR2
20 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
21 ,p_appl_tbl IN okl_receipts_pvt.appl_tbl_type
22 ,x_appl_tbl OUT NOCOPY okl_receipts_pvt.appl_tbl_type
23 ) IS
24
25 ---------------------------
26 -- DECLARE Local Variables
27 ---------------------------
28
29 l_api_version NUMBER := 1.0;
30 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(2000);
34 l_api_name CONSTANT VARCHAR2(30) := 'migrate_Applications';
35
36 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
37 i NUMBER;
38 j NUMBER;
39 k NUMBER;
40 -------------------------------------------------------------------------------
41 -- DECLARE Record/Table Types
42 -------------------------------------------------------------------------------
43
44 l_new_appl_tbl okl_receipts_pvt.appl_tbl_type;
45 l_old_appl_tbl okl_receipts_pvt.appl_tbl_type;
46 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
47 -------------------------------------------------------------------------------
48 -- DEFINE CURSORS
49 -------------------------------------------------------------------------------
50
51 -- abindal start bug#4897580 --
52
53 -------------------------------------------------------------------------------
54
55
56 CURSOR get_unapp_amt(cp_csh_rcpt_id IN NUMBER) IS
57 SELECT sum(amount_applied)
58 FROM AR_RECEIVABLE_APPLICATIONS_ALL
59 WHERE status = 'UNAPP'
60 AND display = 'Y'
61 AND cash_receipt_id = cp_csh_rcpt_id;
62
63 CURSOR get_existing_Applications(cp_csh_rcpt_id IN NUMBER) IS
64 SELECT customer_trx_id,
65 customer_trx_line_id,
66 (line_Applied + tax_applied) amount_applied
67 FROM okl_Receipt_Applications_uv
68 WHERE cash_receipt_id = cp_csh_rcpt_id;
69
70 BEGIN
71 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
72
73 l_return_status := Okl_Api.START_ACTIVITY(
74 p_api_name => l_api_name,
75 p_pkg_name => G_PKG_NAME,
76 p_init_msg_list => p_init_msg_list,
77 l_api_version => l_api_version,
78 p_api_version => p_api_version,
79 p_api_type => '_PVT',
80 x_return_status => l_return_status);
81
82 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
83 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
84 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
85 RAISE Okl_Api.G_EXCEPTION_ERROR;
86 END IF;
87
88 l_new_appl_tbl := p_appl_tbl;
89 i := 0;
90 log_file('l_new_appl_tbl.count = '||l_new_appl_tbl.count);
91 IF l_new_appl_tbl.count > 0 THEN
92 FOR l_app_rec IN get_existing_Applications(l_cash_receipt_id) LOOP
93 l_old_appl_tbl(i).ar_inv_id := l_app_rec.customer_trx_id;
94 l_old_appl_tbl(i).line_id := l_app_rec.customer_trx_line_id;
95 l_old_appl_tbl(i).amount_to_apply := l_app_rec.amount_applied;
96 i := i + 1;
97 END LOOP;
98 log_file('l_old_appl_tbl.count = '||l_old_appl_tbl.count);
99 IF l_old_appl_tbl.count > 0 THEN
100 -- merge l_old_appl_tbl and l_new_appl_tbl into l_appl_tbl
101 k := 1;
102 FOR i IN l_old_appl_tbl.FIRST..l_old_appl_tbl.LAST LOOP
103 FOR j IN l_new_appl_tbl.FIRST..l_new_appl_tbl.LAST LOOP
104 log_file(' l_new_appl_tbl(j).ar_inv_id = '|| l_new_appl_tbl(j).ar_inv_id);
105 log_file(' l_old_appl_tbl(i).ar_inv_id = '|| l_old_appl_tbl(i).ar_inv_id);
106 log_file(' l_new_appl_tbl(j).line_id = '|| l_new_appl_tbl(j).line_id);
107 log_file(' l_old_appl_tbl(i).line_id = '|| l_old_appl_tbl(i).line_id);
108 log_file(' l_new_appl_tbl(j).amount_to_apply = '|| l_new_appl_tbl(j).amount_to_apply);
109 log_file(' l_old_appl_tbl(i).amount_to_apply = '|| l_old_appl_tbl(i).amount_to_apply);
110 IF l_new_appl_tbl(j).ar_inv_id = l_old_appl_tbl(i).ar_inv_id THEN
111 IF l_old_appl_tbl(i).line_id IS NULL OR l_new_appl_tbl(j).line_id IS NULL THEN
112 l_appl_tbl(k) := l_new_appl_tbl(j);
113 l_new_appl_tbl(j).original_applied_amount := 1;
114 l_old_appl_tbl(i).original_applied_amount := 1;
115 k := k +1;
116 ELSIF l_new_appl_tbl(j).line_id = l_old_appl_tbl(i).line_id THEN
117 l_appl_tbl(k) := l_new_appl_tbl(j);
118 l_appl_tbl(k).amount_to_apply := l_new_appl_tbl(j).amount_to_apply + l_old_appl_tbl(i).amount_to_apply;
119 l_new_appl_tbl(j).original_applied_amount := 1;
120 l_old_appl_tbl(i).original_applied_amount := 1;
121 k := k + 1;
122 END IF;
123 END IF;
124 END LOOP;
125 END LOOP;
126 FOR i IN l_old_appl_tbl.FIRST..l_old_appl_tbl.LAST LOOP
127 log_file('l_old_appl_tbl(i).original_applied_amount = '||l_old_appl_tbl(i).original_applied_amount);
128 IF nvl(l_old_appl_tbl(i).original_applied_amount,-1) <> 1 THEN
129 l_appl_tbl(k) := l_old_appl_tbl(i);
130 k := k + 1;
131 END IF;
132 END LOOP;
133 FOR i IN l_new_appl_tbl.FIRST..l_new_appl_tbl.LAST LOOP
134 log_file('l_new_appl_tbl(i).original_applied_amount = '||l_new_appl_tbl(i).original_applied_amount);
135 IF nvl(l_new_appl_tbl(i).original_applied_amount,-1) <> 1 THEN
136 l_appl_tbl(k) := l_new_appl_tbl(i);
137 k := k + 1;
138 END IF;
139 END LOOP;
140 ELSE
141 l_appl_tbl := l_new_appl_tbl;
142 END IF;
143 END IF;
144
145 x_appl_tbl := l_appl_tbl;
146 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
147
148 Okl_Api.END_ACTIVITY ( x_msg_count => x_msg_count,
149 x_msg_data => x_msg_data);
150
151
152 EXCEPTION
153
154 WHEN G_EXCEPTION_HALT_VALIDATION THEN
155 x_return_status := Okl_Api.G_RET_STS_ERROR;
156
157
158 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
159 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
160 (
161 l_api_name,
162 G_PKG_NAME,
163 Okl_Api.G_RET_STS_ERROR,
164 x_msg_count,
165 x_msg_data,
166 '_PVT'
167 );
168
169 WHEN OTHERS THEN
170 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
171 Okl_api.set_message( p_app_name => g_app_name
172 , p_msg_name => g_unexpected_error
173 , p_token1 => g_sqlcode_token
174 , p_token1_value => SQLCODE
175 , p_token2 => g_sqlerrm_token
176 , p_token2_value => SQLERRM
177 ) ;
178
179 END migrate_Applications;
180 ---------------------------------------------------------------------------
181 -- PROCEDURE process_advance_receipt
182 -- This routine handles receivables interaction.
183 ---------------------------------------------------------------------------
184
185 PROCEDURE process_advance_receipt ( p_api_version IN NUMBER
186 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
187 ,x_return_status OUT NOCOPY VARCHAR2
188 ,x_msg_count OUT NOCOPY NUMBER
189 ,x_msg_data OUT NOCOPY VARCHAR2
190 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
191 ,p_org_id IN NUMBER
192 ,p_appl_tbl IN okl_receipts_pvt.appl_tbl_type
193 ,x_remaining_amt OUT NOCOPY NUMBER
194 ) IS
195
196 ---------------------------
197 -- DECLARE Local Variables
198 ---------------------------
199
200 l_api_version NUMBER := 1.0;
201 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
202 l_return_status VARCHAR2(1);
203 l_msg_count NUMBER;
204 l_msg_data VARCHAR2(2000);
205 l_api_name CONSTANT VARCHAR2(30) := 'process_advance_receipt';
206
207 l_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
208 l_unapp_amt NUMBER;
209 l_org_id NUMBER := p_org_id;
210 l_onacc_amt NUMBER;
211 -------------------------------------------------------------------------------
212 -- DECLARE Record/Table Types
213 -------------------------------------------------------------------------------
214
215 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
216 x_appl_tbl okl_receipts_pvt.appl_tbl_type;
217 l_rcpt_rec okl_receipts_pvt.rcpt_rec_type;
218 -------------------------------------------------------------------------------
219 -- DEFINE CURSORS
220 -------------------------------------------------------------------------------
221
222 -- abindal start bug#4897580 --
223
224 -------------------------------------------------------------------------------
225
226
227 CURSOR get_unapp_amt(cp_csh_rcpt_id IN NUMBER) IS
228 SELECT sum(nvl(amount_applied,0))
229 FROM AR_RECEIVABLE_APPLICATIONS_ALL
230 WHERE status = 'UNAPP'
231 AND cash_receipt_id = cp_csh_rcpt_id;
232
233 CURSOR get_onacc_amt(cp_csh_rcpt_id IN NUMBER) IS
234 SELECT sum(nvl(amount_applied,0))
235 FROM AR_RECEIVABLE_APPLICATIONS_ALL
236 WHERE status = 'ACC'
237 AND cash_receipt_id = cp_csh_rcpt_id;
238
239
240
241 BEGIN
242 log_file('process_advance_receipt start');
243 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
244
245 l_return_status := Okl_Api.START_ACTIVITY(
246 p_api_name => l_api_name,
247 p_pkg_name => G_PKG_NAME,
248 p_init_msg_list => p_init_msg_list,
249 l_api_version => l_api_version,
250 p_api_version => p_api_version,
251 p_api_type => '_PVT',
252 x_return_status => l_return_status);
253
254 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
255 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
256 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
257 RAISE Okl_Api.G_EXCEPTION_ERROR;
258 END IF;
259
260 l_appl_tbl := p_appl_tbl;
261
262 OPEN get_onacc_amt(l_receipt_id);
263 FETCH get_onacc_amt INTO l_onacc_amt;
264 CLOSE get_onacc_amt;
265
266 x_remaining_amt := l_onacc_amt;
267 log_file('l_appl_tbl.count = '||l_appl_tbl.COUNT);
268 IF l_appl_tbl.count > 0 THEN
269 --migrate l_appl_tbl so that it has complete application details for given receipt
270 log_file('calling migrate_applications');
271 migrate_applications( p_api_version => l_api_version
272 ,p_init_msg_list => l_init_msg_list
273 ,x_return_status => l_return_status
274 ,x_msg_count => l_msg_count
275 ,x_msg_data => l_msg_data
276 ,p_receipt_id => l_receipt_id
277 ,p_appl_tbl => l_appl_tbl
278 ,x_appl_tbl => x_appl_tbl
279 );
280 x_return_status := l_return_status;
281 log_file('x_return_status = '||x_return_status);
282 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
283 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
284 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
285 RAISE OKL_API.G_EXCEPTION_ERROR;
286 END IF;
287 l_appl_tbl := x_appl_tbl;
288 log_file('l_appl_tbl.count = '||l_appl_tbl.COUNT);
289 -- unapply on account amount if any
290 IF l_onacc_amt > 0 THEN
291 Ar_receipt_api_pub.unapply_on_account(p_api_version => l_api_version
292 ,p_init_msg_list => l_init_msg_list
293 ,x_return_status => l_return_status
294 ,x_msg_count => l_msg_count
295 ,x_msg_data => l_msg_data
296 ,p_cash_receipt_id => l_receipt_id
297 ,p_reversal_gl_date => null
298 );
299
300 log_file('Ar_receipt_api_pub.unapply_on_account return status = '|| l_return_status);
301 x_return_status := l_return_status;
302
303 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
304 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
305 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
306 RAISE OKL_API.G_EXCEPTION_ERROR;
307 END IF;
308 END IF;
309 --apply the receipt
310 l_rcpt_rec.cash_receipt_id := l_receipt_id;
311 l_rcpt_rec.org_id := p_org_id;
312 log_file('l_rcpt_rec.cash_receipt_id = '||l_rcpt_rec.cash_receipt_id);
313 log_file('l_rcpt_rec.org_id = '||l_rcpt_rec.org_id);
314 IF l_appl_tbl.COUNT > 0 THEN
315 FOR ll IN l_appl_tbl.FIRST..l_appl_tbl.LAST LOOP
316 log_file(' l_appl_tbl('||ll||').ar_inv_id = '|| l_appl_tbl(ll).ar_inv_id);
317 log_file(' l_appl_tbl('||ll||').line_id = '|| l_appl_tbl(ll).line_id);
318 log_file(' l_appl_tbl('||ll||').amount_to_apply = '|| l_appl_tbl(ll).amount_to_apply);
319 END LOOP;
320 END IF;
321 okl_receipts_pvt.handle_receipt( p_api_version => l_api_version
322 ,p_init_msg_list => l_init_msg_list
323 ,x_return_status => l_return_status
324 ,x_msg_count => l_msg_count
325 ,x_msg_data => l_msg_data
326 ,p_rcpt_rec => l_rcpt_rec
327 ,p_appl_tbl => l_appl_tbl
328 ,x_cash_receipt_id => l_receipt_id
329 );
330
331 x_return_status := l_return_status;
332 log_file('okl_receipts_pvt.handle_receipt return status = ' ||x_return_status);
333 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
334 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
335 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
336 RAISE OKL_API.G_EXCEPTION_ERROR;
337 END IF;
338 log_file('*** l_receipt_id = '||l_receipt_id);
339 -- move unapplied amount to on account amount
340 OPEN get_unapp_amt(l_receipt_id);
341 FETCH get_unapp_amt INTO l_unapp_amt;
342 CLOSE get_unapp_amt;
343 log_file('l_unapp_amt = '||l_unapp_amt);
344 x_remaining_amt := l_unapp_amt;
345 IF l_unapp_amt > 0 THEN
346
347 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
348 ,p_init_msg_list => l_init_msg_list
349 ,x_return_status => l_return_status
350 ,x_msg_count => l_msg_count
351 ,x_msg_data => l_msg_data
352 ,p_cash_receipt_id => l_receipt_id
353 ,p_amount_applied => l_unapp_amt
354 -- ,p_apply_date => l_receipt_date
355 ,p_org_id => l_org_id
356 );
357
358 x_return_status := l_return_status;
359
360 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
361 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
362 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
363 RAISE OKL_API.G_EXCEPTION_ERROR;
364 END IF;
365 END IF;
366
367 END IF;
368
369 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
370
371 Okl_Api.END_ACTIVITY ( x_msg_count => x_msg_count,
372 x_msg_data => x_msg_data);
373
374
375 EXCEPTION
376
377 WHEN G_EXCEPTION_HALT_VALIDATION THEN
378 x_return_status := Okl_Api.G_RET_STS_ERROR;
379
380
381 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
382 x_return_status := Okl_Api.HANDLE_EXCEPTIONS
383 (
384 l_api_name,
385 G_PKG_NAME,
386 Okl_Api.G_RET_STS_ERROR,
387 x_msg_count,
388 x_msg_data,
389 '_PVT'
390 );
391
392 WHEN OTHERS THEN
393 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
394 Okl_api.set_message( p_app_name => g_app_name
395 , p_msg_name => g_unexpected_error
396 , p_token1 => g_sqlcode_token
397 , p_token1_value => SQLCODE
398 , p_token2 => g_sqlerrm_token
399 , p_token2_value => SQLERRM
400 ) ;
401
402 END process_advance_receipt;
403
404
405 ---------------------------------------------------------------------------
406 -- PROCEDURE advanced_cash_app
407 -- This routine called from advanced billing api. Looks for advanced
408 -- receipts for newly booked contract
409 ---------------------------------------------------------------------------
410
411 PROCEDURE advanced_cash_app ( p_api_version IN NUMBER
412 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
413 ,x_return_status OUT NOCOPY VARCHAR2
414 ,x_msg_count OUT NOCOPY NUMBER
415 ,x_msg_data OUT NOCOPY VARCHAR2
416 ,p_contract_num IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL
417 ,p_customer_num IN AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL -- HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
418 ,p_receipt_num IN OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT NULL
419 ,p_cross_currency_allowed IN VARCHAR2 DEFAULT 'N'
420 ) IS
421
422
423 ---------------------------
424 -- DECLARE Local Variables
425 ---------------------------
426
427 l_api_version NUMBER := 1.0;
428 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
429 l_return_status VARCHAR2(1);
430 l_msg_count NUMBER;
431 l_msg_data VARCHAR2(2000);
432 l_api_name CONSTANT VARCHAR2(30) := 'advanced_cash_app';
433
434 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
435 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
436 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL;
437 -- l_customer_num AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT p_customer_num;
438 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
439 -- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
440 l_customer_num hz_cust_accounts.account_number%TYPE;
441 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
442 l_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
443 l_receipt_num OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT p_receipt_num;
444 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
445 l_cross_currency_allowed VARCHAR2(1) DEFAULT p_cross_currency_allowed;
446
447 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
448
449 l_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
450 l_remain_rcpt_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
451 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE;
452 l_actual_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
453 l_receipt_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE;
454 l_receipt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE;
455 l_receipt_count NUMBER;
456
457 l_invoice_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
458 l_inv_tot NUMBER := 0;
459
460 --
461 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE;
462 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE;
463 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE;
464 --
465
466
467 l_rct_id NUMBER;
468 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
469 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
470 i NUMBER DEFAULT NULL;
471
472 l_stat_total_rcpt_amt OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT 0;
473 l_stat_num_of_rcpts NUMBER DEFAULT 0;
474 l_stat_num_of_cont NUMBER DEFAULT 0;
475
476 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
477
478 l_exit_loop NUMBER DEFAULT 0;
479 l_exchange_rate_type VARCHAR2(100);
480 l_conversion_rate NUMBER;
481 -------------------------------------------------------------------------------
482 -- DECLARE Record/Table Types
483 -------------------------------------------------------------------------------
484 -- Internal Trans
485
486 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
487 l_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
488
489 l_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
490 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
491
492 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
493 x_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
494
495 x_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
496 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
497
498 -- External Trans
499
500 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
501 l_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
502
503 l_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
504 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
505
506 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
507 x_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
508
509 x_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
510 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
511
512 t_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
513
514 -------------------------------------------------------------------------------
515 -- DEFINE CURSORS
516 -------------------------------------------------------------------------------
517
518 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
519 cursor c_customer_acc_num (cp_account_id number) is
520 select ca.account_number
521 from HZ_CUST_ACCOUNTS ca
522 where ca.cust_account_id = cp_account_id;
523 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
524
525 -- get all advanced receipts for newly booked contract
526 CURSOR c_get_adv_rcpt_for_cont(cp_cont_id IN NUMBER
527 ,cp_currency_code IN VARCHAR2) IS
528 SELECT DISTINCT(a.cash_receipt_id) icr_id,
529 a.amount,
530 a.date_effective,
531 a.check_number,
532 a.currency_code,
533 -- arcash.exchange_rate_date,
534 -- arcash.exchange_rate_type,
535 -- arcash.exchange_rate ,
536 a.id,
537 arcash.receipt_Date
538 FROM OKL_TRX_CSH_RECEIPT_V a,
539 OKL_TXL_RCPT_APPS_V b,
540 ar_cash_receipts_All arcash
541 WHERE a.id = b.rct_id_details
542 AND a.FULLY_APPLIED_FLAG = 'N'
543 AND a.EXPIRED_FLAG = 'N'
544 AND a.receipt_type = 'ADV'
545 AND b.khr_id = cp_cont_id
546 AND a.cash_receipt_id = arcash.cash_receipt_id
547 AND a.currency_code = decode(p_cross_currency_allowed,'N', cp_currency_code ,a.currency_code)
548 ORDER BY receipt_date;
549
550 c_get_adv_rcpt_for_cont_rec c_get_adv_rcpt_for_cont%ROWTYPE;
551
552 -------------------------------------------------------------------------------
553
554 -- verify on account receipt amount
555 CURSOR c_ver_on_acct_amt(cp_csh_rcpt_id IN NUMBER) IS
556 SELECT (unapplied_amount + onaccount_amount) amount_available
557 FROM okl_receipt_Details_uv
558 WHERE cash_Receipt_id = cp_csh_rcpt_id;
559
560 -------------------------------------------------------------------------------
561
562 -- get contract total
563 CURSOR c_open_invs ( cp_contract_num IN VARCHAR2
564 ,cp_customer_num IN VARCHAR2
565 ,cp_currency_code IN VARCHAR2
566 ) IS
567 SELECT lpt.sty_id
568 ,lpt.amount_due_remaining
569 ,lpt.currency_code
570 ,lpt.ar_invoice_number
571 ,lpt.trx_date
572 ,lpt.customer_acct_id
573 ,lpt.khr_id
574 FROM okl_rcpt_cust_cont_balances_uv lpt
575 WHERE lpt.contract_number = cp_contract_num
576 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
577 AND lpt.status = 'OP'
578 AND lpt.amount_due_remaining > 0
579 AND lpt.currency_code = decode(p_cross_currency_allowed,'N', NVL(cp_currency_code, lpt.currency_code),lpt.currency_code);
580
581 c_open_invs_rec c_open_invs%ROWTYPE;
582
583 BEGIN
584 log_file('advanced_Cash_App start');
585 log_file('p_contract_num = '||p_contract_num);
586 log_file('(cust_account_id)p_customer_num = '||p_customer_num);
587 log_file('p_receipt_num = '|| p_Receipt_num);
588 ------------------------------------------------------------
589 -- Start processing
590 ------------------------------------------------------------
591
592 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
593
594 l_return_status := Okl_Api.START_ACTIVITY(
595 p_api_name => l_api_name,
596 p_pkg_name => G_PKG_NAME,
597 p_init_msg_list => p_init_msg_list,
598 l_api_version => l_api_version,
599 p_api_version => p_api_version,
600 p_api_type => '_PVT',
601 x_return_status => l_return_status);
602
603 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
604 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
605 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
606 RAISE Okl_Api.G_EXCEPTION_ERROR;
607 END IF;
608
609 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
610 open c_customer_acc_num (p_customer_num);
611 fetch c_customer_acc_num into l_customer_num;
612 close c_customer_acc_num;
613 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
614
615 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=================================================================================');
616 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ** Start Processing. Please See Error Log for any errored transactions ** ');
617 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=================================================================================');
618
619 IF l_contract_num IS NULL THEN
620 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------------');
621 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - You must specify a contract Number.');
622 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------------');
623 END IF;
624
625 ------------------------------------------------------------
626 -- Handle call from auto billing api ...
627 ------------------------------------------------------------
628
629 IF l_contract_num IS NOT NULL THEN
630
631 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT NUMBER: '||l_contract_num);
632 log_file('BEFORE CUrsor');
633 FOR c_open_invs_rec IN c_open_invs (l_contract_num, l_customer_num, null)
634 LOOP
635 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
636 l_contract_id := c_open_invs_rec.khr_id;
637 l_customer_id := c_open_invs_rec.customer_acct_id;
638 l_invoice_currency_code := c_open_invs_rec.currency_code;
639 log_file('l_inv_tot = '||l_inv_tot);
640 END LOOP;
641 log_file('l_inv_tot = '||l_inv_tot);
642 log_file('l_contract_id = '||l_contract_id);
643 log_file('l_customer_id = '||l_customer_id);
644 IF l_inv_tot = 0 THEN
645 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------');
646 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Following contract has a balance of zero -- unable to apply advanced receipts');
647 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_contract_num);
648 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------');
649 END IF;
650
651 IF l_inv_tot > 0 THEN
652
653 l_receipt_count := 0;
654
655 FOR c_get_adv_rcpt_for_cont_rec IN c_get_adv_rcpt_for_cont (l_contract_id,l_invoice_currency_code)
656 LOOP
657 log_file('c_get_adv_rcpt_for_cont_rec.icr_id = '||c_get_adv_rcpt_for_cont_rec.icr_id);
658 l_inv_tot := 0;
659
660 FOR c_open_invs_rec IN c_open_invs (l_contract_num, l_customer_num, null)
661 LOOP
662 l_invoice_currency_code := c_open_invs_rec.currency_code;
663 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
664 END LOOP;
665
666 IF l_inv_tot = 0 THEN
667 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT '||l_contract_num|| 'now has a zero balance - receipt application complete');
668 l_exit_loop := 1;
669 END IF;
670
671 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' NEW CONTRACT BALANCE: '||l_inv_tot);
672 log_file('New Contract balance = '||l_inv_tot);
673 EXIT WHEN l_exit_loop = 1;
674
675 l_receipt_count := l_receipt_count + 1;
676 l_cash_receipt_id := c_get_adv_rcpt_for_cont_rec.icr_id;
677 l_remittance_amount := c_get_adv_rcpt_for_cont_rec.amount;
678 l_receipt_date := c_get_adv_rcpt_for_cont_rec.date_effective;
679 l_check_number := c_get_adv_rcpt_for_cont_rec.check_number;
680 l_receipt_currency := c_get_adv_rcpt_for_cont_rec.currency_code;
681
682 /* l_currency_conv_date := c_get_adv_rcpt_for_cont_rec.exchange_rate_date;
683 l_currency_conv_type := c_get_adv_rcpt_for_cont_rec.exchange_rate_type;
684 l_currency_conv_rate := c_get_adv_rcpt_for_cont_rec.exchange_rate;
685 */
686 l_rct_id := c_get_adv_rcpt_for_cont_rec.id;
687
688 OPEN c_ver_on_acct_amt(l_cash_receipt_id);
689 FETCH c_ver_on_acct_amt INTO l_actual_remittance_amount;
690 CLOSE c_ver_on_acct_amt;
691 log_file('l_cash_receipt_id = '||l_cash_receipt_id);
692 log_file('l_actual_remittance_amount = '||l_actual_remittance_amount);
693 IF l_actual_remittance_amount = 0 OR l_actual_remittance_amount IS NULL THEN
694
695 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT '||l_check_number|| 'now has zero Balance');
696 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'MOVING ON TO NEXT RECEIPT');
697
698 GOTO NEXT_RECEIPT;
699 END IF;
700
701 IF l_actual_remittance_amount <> l_remittance_amount THEN
702 l_remittance_amount := l_actual_remittance_amount;
703 END IF;
704 log_file('l_remittance_amount = '||l_remittance_amount);
705 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT NUMBER: '||l_check_number);
706 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT AMOUNT: '||l_actual_remittance_amount);
707
708 --following code added by dkagrawa for cross currency
709 IF l_invoice_currency_code <> l_receipt_currency THEN
710 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
711 IF l_exchange_rate_type IS NULL THEN
712 OKL_API.set_message( p_app_name => G_APP_NAME
713 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
714 );
715 RAISE G_EXCEPTION_HALT_VALIDATION;
716 ELSE
717 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
718 ,l_receipt_currency
719 ,l_receipt_date
720 ,l_exchange_rate_type
721 );
722 IF l_conversion_rate IN (0,-1) THEN
723 -- Message Text: No exchange rate defined
724 x_return_status := okl_api.G_RET_STS_ERROR;
725 okl_api.set_message( p_app_name => G_APP_NAME,
726 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
727 RAISE G_EXCEPTION_HALT_VALIDATION;
728 END IF;
729 END IF;
730 l_inv_tot := l_inv_tot*l_conversion_rate;
731 END IF;
732
733 IF l_inv_tot <= l_remittance_amount THEN
734
735 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'No cash application rules required. Balance of contract is less than or equal to receipt amount');
736 log_file('calling apply_rcpt_to_contract_no_rule ');
737 apply_rcpt_to_contract_no_rule ( p_api_version => l_api_version
738 ,p_init_msg_list => l_init_msg_list
739 ,x_return_status => l_return_status
740 ,x_msg_count => l_msg_count
741 ,x_msg_data => l_msg_data
742 ,p_contract_id => l_contract_id
743 ,p_contract_num => l_contract_num
744 ,p_customer_id => l_customer_id
745 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
746 -- ,p_customer_num => l_customer_num
747 ,p_customer_num => p_customer_num-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
748 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
749 ,p_receipt_id => l_cash_receipt_id
750 ,p_receipt_amount => l_remittance_amount
751 ,p_remain_rcpt_amount => l_remain_rcpt_amount
752 ,p_receipt_currency => l_receipt_currency
753 ,p_receipt_date => l_receipt_date
754 ,p_invoice_currency => l_invoice_currency_code
755 ,p_currency_conv_date => l_currency_conv_date
756 ,p_currency_conv_rate => l_currency_conv_rate
757 ,p_currency_conv_type => l_currency_conv_type
758 ,p_xcr_id => l_rct_id
759 ,p_cross_currency_allowed => l_cross_currency_allowed
760 );
761 log_file('l_return_status = '||l_return_status);
762
763 ELSIF l_inv_tot > l_remittance_amount THEN
764
765 -- call procedure to do cash app
766
767 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Cash application rule required. Balance of contract is greater than receipt amount');
768 log_file('calling apply_rcpt_to_contract_w_rule');
769 apply_rcpt_to_contract_w_rule ( p_api_version => l_api_version
770 ,p_init_msg_list => l_init_msg_list
771 ,x_return_status => l_return_status
772 ,x_msg_count => l_msg_count
773 ,x_msg_data => l_msg_data
774 ,p_contract_id => l_contract_id
775 ,p_contract_num => l_contract_num
776 ,p_customer_id => l_customer_id
777 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
778 -- ,p_customer_num => l_customer_num
779 ,p_customer_num => p_customer_num-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
780 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
781 ,p_receipt_id => l_cash_receipt_id
782 ,p_receipt_amount => l_remittance_amount
783 ,p_remain_rcpt_amount => l_remain_rcpt_amount
784 ,p_receipt_currency => l_receipt_currency
785 ,p_receipt_date => l_receipt_date
786 ,p_invoice_currency => l_invoice_currency_code
787 ,p_invoice_total => l_inv_tot
788 ,p_currency_conv_date => l_currency_conv_date
789 ,p_currency_conv_rate => l_currency_conv_rate
790 ,p_currency_conv_type => l_currency_conv_type
791 ,p_xcr_id => l_rct_id
792 ,p_cross_currency_allowed => l_cross_currency_allowed
793 );
794 log_file('l_return_status = '||l_return_status);
795 END IF;
796
797 x_return_status := l_return_status;
798
799 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
800 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
801 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
802 RAISE OKL_API.G_EXCEPTION_ERROR;
803 END IF;
804 log_file(' l_remain_rcpt_amount = '||l_remain_rcpt_amount);
805 IF l_remain_rcpt_amount <> l_actual_remittance_amount THEN -- application took place, move to next contract ...
806
807 l_stat_total_rcpt_amt := l_stat_total_rcpt_amt + (l_actual_remittance_amount - l_remain_rcpt_amount);
808 l_stat_num_of_rcpts := l_stat_num_of_rcpts + 1;
809 l_stat_num_of_cont := l_stat_num_of_cont + 1;
810
811 END IF;
812
813 IF l_remain_rcpt_amount > 0 THEN
814 l_rctv_rec.FULLY_APPLIED_FLAG := 'N';
815 ELSE
816 l_rctv_rec.FULLY_APPLIED_FLAG := 'Y';
817 END IF;
818
819 l_rctv_rec.ID := l_rct_id;
820 log_file('l_rctv_rec.ID = '||l_rctv_rec.ID);
821 OKL_RCT_PVT.update_row( p_api_version => l_api_version
822 ,p_init_msg_list => l_init_msg_list
823 ,x_return_status => l_return_status
824 ,x_msg_count => l_msg_count
825 ,x_msg_data => l_msg_data
826 ,p_rctv_rec => l_rctv_rec
827 ,x_rctv_rec => x_rctv_rec);
828
829 x_return_status := l_return_status;
830
831 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
832 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
833 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
834 RAISE OKL_API.G_EXCEPTION_ERROR;
835 END IF;
836
837 -- commented out for testing purposes.
838 -------------------------------------------------------------------
839 -- COMMIT; -- Need to commit here to update balance of contract ...
840 -------------------------------------------------------------------
841
842 <<NEXT_RECEIPT>>
843
844 NULL;
845
846 END LOOP; -- looping through available advanced receipts for contract ...
847
848 IF l_receipt_count = 0 THEN
849 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '------------------------------------------------------------------');
850 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'There are no advanced receipts for this contract - EXITING PROCESS');
851 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '------------------------------------------------------------------');
852 END IF;
853
854 END IF; -- IF inv_tot > 0
855
856 END IF; -- IF l_contract_num IS NOT NULL
857
858 ------------------------------------------------------------
859 -- END Handle call from auto billing api ...
860 ------------------------------------------------------------
861
862 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
863 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total number of receipts processed: '||l_stat_num_of_rcpts);
864 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total of receipt amounts: '||l_stat_total_rcpt_amt);
865 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total number of contracts that received cash application: '||l_stat_num_of_cont);
866 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
867 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PROCESS COMPLETE: '||SYSDATE);
868 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
869
870 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
871 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ** End Processing. Please See Error Log for any errored transactions ** ');
872 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
873
874 ------------------------------------------------------------
875 -- End processing
876 ------------------------------------------------------------
877
878 Okl_Api.END_ACTIVITY (
879 x_msg_count => x_msg_count,
880 x_msg_data => x_msg_data);
881 log_file('end advanced_Cash_app');
882
883 EXCEPTION
884
885 WHEN G_EXCEPTION_HALT_VALIDATION THEN
886 x_return_status := okl_api.G_RET_STS_ERROR;
887
888
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.G_RET_STS_UNEXP_ERROR;
902 Okl_api.set_message( p_app_name => g_app_name
903 , p_msg_name => g_unexpected_error
904 , p_token1 => g_sqlcode_token
905 , p_token1_value => SQLCODE
906 , p_token2 => g_sqlerrm_token
907 , p_token2_value => SQLERRM
908 ) ;
909
910 END advanced_cash_app;
911
912 ---------------------------------------------------------------------------
913 -- PROCEDURE apply_rcpt_to_contract_no_rule
914 -- Apply receipt to contract. no purpose or cash application rule reqd.
915 -- as receipt amount is greater than contract total
916 ---------------------------------------------------------------------------
917
918 PROCEDURE apply_rcpt_to_contract_no_rule ( p_api_version IN NUMBER
919 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
920 ,x_return_status OUT NOCOPY VARCHAR2
921 ,x_msg_count OUT NOCOPY NUMBER
922 ,x_msg_data OUT NOCOPY VARCHAR2
923 ,p_contract_id IN OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL
924 ,p_contract_num IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL
925 ,p_customer_id IN OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL
926 ,p_customer_num IN AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
927 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
928 ,p_receipt_amount IN AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
929 ,p_remain_rcpt_amount OUT NOCOPY AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
930 ,p_receipt_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
931 ,p_receipt_date IN OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL
932 ,p_invoice_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
933 ,p_currency_conv_date IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT NULL
934 ,p_currency_conv_rate IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT NULL
935 ,p_currency_conv_type IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL
936 ,p_xcr_id IN NUMBER DEFAULT NULL
937 ,p_cross_currency_allowed IN VARCHAR2 DEFAULT 'N'
938 ) IS
939
940 ---------------------------
941 -- DECLARE Local Variables
942 ---------------------------
943
944
945
946 l_api_version NUMBER := 1.0;
947 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
948 l_return_status VARCHAR2(1);
949 l_msg_count NUMBER;
950 l_msg_data VARCHAR2(2000);
951 l_api_name CONSTANT VARCHAR2(30) := 'apply_rcpt_to_contract_no_rule';
952
953 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT p_contract_id;
954 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
955 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT p_customer_id;
956 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
957 -- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
958 l_customer_num hz_cust_accounts.account_number%TYPE;
959 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
960 l_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
961 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_receipt_amount;
962 l_receipt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT p_receipt_date;
963 l_cross_currency_allowed VARCHAR2(1) DEFAULT p_cross_currency_allowed;
964 l_converted_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
965
966
967 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
968
969 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
970 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE;
971 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_receipt_currency;
972 l_receipt_count NUMBER;
973
974 l_invoice_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_invoice_currency;
975
976 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL; -- entered currency code
977
978 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
979 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
980
981 --
982 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_currency_conv_type;
983 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_currency_conv_date;
984 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_currency_conv_rate;
985 --
986
987
988 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
989 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
990
991 l_xcr_id NUMBER DEFAULT p_xcr_id;
992 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
993 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
994 i NUMBER DEFAULT NULL;
995
996 l_counter NUMBER;
997 l_unapply VARCHAR2(3);
998
999 l_record_count NUMBER DEFAULT NULL;
1000 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
1001
1002 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
1003
1004 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
1005 x_appl_tbl okl_receipts_pvt.appl_tbl_type;
1006 l_exchange_rate_type VARCHAR2(100);
1007 l_conversion_rate NUMBER;
1008
1009 -------------------------------------------------------------------------------
1010 -- DEFINE CURSORS
1011 -------------------------------------------------------------------------------
1012 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1013 cursor c_customer_acc_num (cp_account_id number) is
1014 select ca.account_number
1015 from HZ_CUST_ACCOUNTS ca
1016 where ca.cust_account_id = cp_account_id;
1017 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1018
1019
1020 -- get contract total
1021 CURSOR c_open_invs ( cp_contract_num IN VARCHAR2
1022 ,cp_customer_num IN VARCHAR2
1023 ,cp_currency_code IN VARCHAR2
1024 ) IS
1025 SELECT lpt.sty_id
1026 ,lpt.amount_due_remaining
1027 ,lpt.currency_code
1028 ,lpt.AR_INVOICE_NUMBER
1029 ,lpt.trx_date
1030 ,lpt.ar_invoice_id
1031 ,lpt.invoice_line_id
1032 FROM okl_rcpt_cust_cont_balances_uv lpt
1033 WHERE lpt.contract_number = cp_contract_num
1034 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
1035 AND lpt.status = 'OP'
1036 AND lpt.amount_due_remaining > 0
1037 AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
1038
1039 c_open_invs_rec c_open_invs%ROWTYPE;
1040
1041 -------------------------------------------------------------------------------
1042
1043 -- get cash applic rule id
1044 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
1045 SELECT to_number(a.object1_id1)
1046 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
1047 WHERE a.rgp_id = b.id
1048 AND b.rgd_code = 'LABILL'
1049 AND a.rule_information_category = 'LAINVD'
1050 AND a.dnz_chr_id = b.chr_id
1051 AND a.dnz_chr_id = cp_khr_id;
1052
1053 ----------
1054
1055 BEGIN
1056
1057 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1058 open c_customer_acc_num (p_customer_num);
1059 fetch c_customer_acc_num into l_customer_num;
1060 close c_customer_acc_num;
1061 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1062
1063
1064 log_file('apply_rcpt_to_contract_no_rule start');
1065 log_file('l_contract_id = '||l_contract_id);
1066 log_file('l_customer_num = '||l_customer_num);
1067 -- get cash application rule
1068 OPEN c_cash_rle_id_csr (l_contract_id);
1069 FETCH c_cash_rle_id_csr INTO l_cau_id;
1070 CLOSE c_cash_rle_id_csr;
1071 log_file('l_cau_id = '||l_cau_id);
1072 -- don't do cash application if CAR is 'On Account' -- varao start
1073 IF NVL(l_cau_id, 0) = -1 THEN
1074 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'No cash application, ON-ACCOUNT Cash Application Rule.');
1075 p_remain_rcpt_amount := l_receipt_amount;
1076 ELSE -- varao end
1077 i := 0;
1078 ------------------------------------------------------------
1079 -- Convert receipt currency to invoice currency if different
1080 ------------------------------------------------------------
1081
1082 -- get invoice amount due remaining and invoice currency
1083
1084 IF l_invoice_currency_code <> l_receipt_currency_code THEN
1085 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1086 IF l_exchange_rate_type IS NULL THEN
1087 OKL_API.set_message( p_app_name => G_APP_NAME
1088 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1089 );
1090 RAISE G_EXCEPTION_HALT_VALIDATION;
1091 ELSE
1092 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
1093 ,l_receipt_currency_code
1094 ,l_receipt_date
1095 ,l_exchange_rate_type
1096 );
1097 IF l_conversion_rate IN (0,-1) THEN
1098 -- Message Text: No exchange rate defined
1099 x_return_status := okl_api.G_RET_STS_ERROR;
1100 okl_api.set_message( p_app_name => G_APP_NAME,
1101 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1102 RAISE G_EXCEPTION_HALT_VALIDATION;
1103 END IF;
1104 END IF;
1105 log_file('l_conversion_rate '||l_conversion_rate);
1106 l_converted_receipt_amount := (l_receipt_amount / l_conversion_rate);
1107 l_receipt_amount := l_converted_receipt_amount;
1108 log_file('receipt amount in inv currency '||l_receipt_amount);
1109 END IF;
1110
1111 log_file('l_receipt_amount = '||l_receipt_amount);
1112 IF l_contract_num IS NOT NULL THEN
1113 OPEN c_open_invs ( l_contract_num, l_customer_num, l_receipt_currency_code);
1114 LOOP
1115 FETCH c_open_invs INTO c_open_invs_rec;
1116 EXIT WHEN c_open_invs%NOTFOUND OR l_receipt_amount = 0 OR l_receipt_amount IS NULL;
1117
1118 i := i + 1;
1119
1120 l_invoice_currency_code := c_open_invs_rec.currency_code;
1121 l_appl_tbl(i).ar_inv_id := c_open_invs_rec.ar_invoice_id;
1122 l_appl_tbl(i).line_id := c_open_invs_rec.invoice_line_id;
1123 l_appl_tbl(i).amount_to_apply := c_open_invs_rec.amount_due_remaining;
1124 log_file('**** Applications ****');
1125 log_file('l_appl_tbl(i).ar_inv_id = '||l_appl_tbl(i).ar_inv_id);
1126 log_file('l_appl_tbl(i).line_id = '||l_appl_tbl(i).line_id);
1127 log_file('l_appl_tbl(i).amount_to_apply = '||l_appl_tbl(i).amount_to_apply);
1128 IF l_receipt_amount < l_appl_tbl(i).amount_to_apply THEN
1129 l_appl_tbl(i).amount_to_apply := l_receipt_amount;
1130 l_receipt_amount := 0;
1131 ELSE
1132 l_receipt_amount := l_receipt_amount - l_appl_tbl(i).amount_to_apply;
1133 END IF;
1134 log_file('l_appl_tbl(i).amount_to_apply = '||l_appl_tbl(i).amount_to_apply);
1135 END LOOP;
1136 CLOSE c_open_invs;
1137 END IF;
1138 log_file('l_receipt_amount before conversion =' ||l_receipt_amount);
1139 IF l_invoice_currency_code <> l_receipt_currency_code THEN
1140 p_remain_rcpt_amount := l_receipt_amount*l_conversion_rate;
1141 ELSE
1142 p_remain_rcpt_amount := l_receipt_amount;
1143 END IF;
1144 log_file('l_receipt_amount after conversion =' ||p_remain_rcpt_amount);
1145 l_record_count := l_appl_tbl.COUNT;
1146 log_file('l_record_count = '||l_record_count);
1147 IF l_record_count > 0 THEN
1148 log_file('calling process_advance_receipt');
1149 process_advance_receipt( p_api_version => l_api_version
1150 ,p_init_msg_list => l_init_msg_list
1151 ,x_return_status => l_return_status
1152 ,x_msg_count => l_msg_count
1153 ,x_msg_data => l_msg_data
1154 ,p_receipt_id => l_receipt_id
1155 ,p_org_id => l_org_id
1156 ,p_appl_tbl => l_appl_tbl
1157 ,x_remaining_amt => p_remain_rcpt_amount
1158 );
1159 log_file('l_return_status = ' ||l_return_status);
1160 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1161 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1162 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1163 RAISE OKL_API.G_EXCEPTION_ERROR;
1164 END IF;
1165
1166 END IF;
1167
1168 END IF;
1169
1170 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1171 log_file('apply_rcpt_to_contract_no_rule end');
1172 EXCEPTION
1173
1174 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1175 x_return_status := okl_api.G_RET_STS_ERROR;
1176
1177
1178 WHEN okl_api.G_EXCEPTION_ERROR THEN
1179 x_return_status := okl_api.HANDLE_EXCEPTIONS
1180 (
1181 l_api_name,
1182 G_PKG_NAME,
1183 OKL_API.G_RET_STS_ERROR,
1184 x_msg_count,
1185 x_msg_data,
1186 '_PVT'
1187 );
1188
1189 WHEN OTHERS THEN
1190 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
1191 Okl_api.set_message( p_app_name => g_app_name
1192 , p_msg_name => g_unexpected_error
1193 , p_token1 => g_sqlcode_token
1194 , p_token1_value => SQLCODE
1195 , p_token2 => g_sqlerrm_token
1196 , p_token2_value => SQLERRM
1197 ) ;
1198
1199 END apply_rcpt_to_contract_no_rule;
1200
1201 ---------------------------------------------------------------------------
1202 -- PROCEDURE apply_rcpt_to_contract_w_rule
1203 -- Apply receipt to contract w/ either defined purpose or cash application
1204 -- rule
1205 ---------------------------------------------------------------------------
1206
1207 PROCEDURE apply_rcpt_to_contract_w_rule ( p_api_version IN NUMBER
1208 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
1209 ,x_return_status OUT NOCOPY VARCHAR2
1210 ,x_msg_count OUT NOCOPY NUMBER
1211 ,x_msg_data OUT NOCOPY VARCHAR2
1212 ,p_contract_id IN OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL
1213 ,p_contract_num IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL
1214 ,p_customer_id IN OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL
1215 ,p_customer_num IN AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
1216 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
1217 ,p_receipt_amount IN AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
1218 ,p_remain_rcpt_amount OUT NOCOPY AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
1219 ,p_receipt_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
1220 ,p_receipt_date IN OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL
1221 ,p_invoice_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
1222 ,p_invoice_total IN OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL
1223 ,p_currency_conv_date IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT NULL
1224 ,p_currency_conv_rate IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT NULL
1225 ,p_currency_conv_type IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL
1226 ,p_xcr_id IN NUMBER DEFAULT NULL
1227 ,p_cross_currency_allowed IN VARCHAR2 DEFAULT 'N'
1228 ) IS
1229
1230 ---------------------------
1231 -- DECLARE Local Variables
1232 ---------------------------
1233
1234 l_api_version NUMBER := 1.0;
1235 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
1236 l_return_status VARCHAR2(1);
1237 l_msg_count NUMBER;
1238 l_msg_data VARCHAR2(2000);
1239 l_api_name CONSTANT VARCHAR2(30) := 'apply_rcpt_to_contract_w_rule';
1240
1241 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT p_contract_id;
1242 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
1243 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT p_customer_id;
1244 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1245 -- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
1246 l_customer_num hz_cust_accounts.account_number%TYPE;
1247 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1248 l_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
1249 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_receipt_amount;
1250 l_receipt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT p_receipt_date;
1251 l_cross_currency_allowed VARCHAR2(1) DEFAULT p_cross_currency_allowed;
1252
1253 l_converted_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
1254
1255 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
1256
1257 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
1258 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE;
1259 l_expired_flag OKL_TRX_CSH_RECEIPT_V.FULLY_APPLIED_FLAG%TYPE;
1260 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_receipt_currency;
1261
1262 l_receipt_remaining OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
1263 l_amt_due_remaining_tot OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
1264
1265 l_inv_tot OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_invoice_total;
1266 l_temp_val NUMBER := 0;
1267 l_receipt_count NUMBER;
1268
1269 l_appl_tolerance NUMBER := 0;
1270 l_first_prorate_rec NUMBER DEFAULT NULL;
1271 l_order_count NUMBER DEFAULT NULL;
1272
1273 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
1274 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
1275 l_pro_rate_inv_total NUMBER := 0;
1276 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
1277
1278
1279
1280 l_invoice_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_invoice_currency;
1281
1282 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL; -- entered currency code
1283
1284 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
1285 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
1286
1287 --
1288 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_currency_conv_type;
1289 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_currency_conv_date;
1290 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_currency_conv_rate;
1291 --
1292
1293
1294 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1295 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
1296
1297 l_rule_name OKL_CASH_ALLCTN_RLS.NAME%TYPE DEFAULT NULL;
1298 l_check_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
1299 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
1300 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
1301 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
1302 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
1303 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
1304 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1305 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1306 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
1307
1308 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
1309 l_dflt_name OKL_CASH_ALLCTN_RLS.NAME%TYPE DEFAULT NULL;
1310 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
1311 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
1312 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
1313 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1314 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
1315 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
1316
1317 l_purpose_id OKL_TXL_RCPT_APPS_V.STY_ID%TYPE DEFAULT NULL;
1318 l_purpose_amt OKL_TXL_RCPT_APPS_V.AMOUNT%TYPE DEFAULT NULL;
1319 l_purpose_total OKL_TXL_RCPT_APPS_V.AMOUNT%TYPE DEFAULT NULL;
1320
1321 l_applied_running_total OKL_TXL_RCPT_APPS_V.AMOUNT%TYPE DEFAULT NULL;
1322
1323 l_xcr_id NUMBER DEFAULT p_xcr_id;
1324 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
1325 i NUMBER DEFAULT NULL;
1326 k NUMBER := 0;
1327
1328 l_counter NUMBER;
1329 l_unapply VARCHAR2(3);
1330
1331 l_record_count NUMBER DEFAULT NULL;
1332 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
1333 x_onacc_amount NUMBER;
1334 x_unapply_amount NUMBER;
1335 l_exchange_rate_type VARCHAR2(100);
1336 l_conversion_rate NUMBER;
1337
1338 -------------------------------------------------------------------------------
1339 -- DECLARE Record/Table Types
1340 -------------------------------------------------------------------------------
1341
1342 l_autocash_appl_tbl okl_auto_cash_appl_rules_pvt.okl_appl_dtls_tbl_type;
1343 l_appl_tbl okl_receipts_pvt.appl_tbl_type;
1344 -------------------------------------------------------------------------------
1345 -- DEFINE CURSORS
1346 -------------------------------------------------------------------------------
1347 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1348 cursor c_customer_acc_num (cp_account_id number) is
1349 select ca.account_number
1350 from HZ_CUST_ACCOUNTS ca
1351 where ca.cust_account_id = cp_account_id;
1352 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1353
1354
1355 -- nikshah -- Bug # 5484903 Fixed,
1356 -- Changed CURSOR c_open_invs SQL definition
1357 -- get contract total
1358 CURSOR c_open_invs ( cp_contract_num IN VARCHAR2
1359 ,cp_customer_num IN VARCHAR2
1360 ,cp_stream_type_id IN NUMBER
1361 ,cp_currency_code IN VARCHAR2) IS
1362 SELECT lpt.sty_id
1363 ,lpt.amount_due_remaining
1364 ,lpt.currency_code
1365 ,lpt.ar_invoice_number
1366 ,lpt.trx_date
1367 ,lpt.ar_invoice_id
1368 ,lpt.invoice_line_id
1369 FROM okl_rcpt_cust_cont_balances_uv lpt
1370 WHERE lpt.contract_number = cp_contract_num
1371 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
1372 AND lpt.sty_id = NVL (cp_stream_type_id, lpt.sty_id)
1373 AND lpt.status = 'OP'
1374 AND lpt.amount_due_remaining > 0
1375 AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
1376
1377
1378 c_open_invs_rec c_open_invs%ROWTYPE;
1379
1380 CURSOR c_get_cust_acct_num(cp_cash_receipt_id IN NUMBER) IS
1381 SELECT hca.account_number
1382 FROM hz_cust_accounts_all hca,
1383 ar_cash_receipts_all arcash
1384 WHERE hca.cust_account_id = arcash.pay_from_customer
1385 AND arcash.cash_receipt_id = cp_cash_receipt_id;
1386
1387 -------------------------------------------------------------------------------
1388
1389 -- get purpose for advance receipt if any
1390 CURSOR c_get_purpose_for_adv_rcpt(cp_cont_id IN NUMBER, cp_icr_id IN NUMBER) IS
1391 SELECT b.sty_id, b.amount
1392 FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
1393 WHERE a.id = b.rct_id_details
1394 AND a.receipt_type = 'ADV'
1395 AND a.EXPIRED_FLAG = 'N'
1396 AND a.FULLY_APPLIED_FLAG = 'N'
1397 AND a.cash_receipt_id = cp_icr_id
1398 AND b.khr_id = cp_cont_id
1399 AND b.sty_id IS NOT NULL;
1400
1401 -------------------------------------------------------------------------------
1402
1403 -- get stream application order
1404 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
1405 ,cp_cat_id IN NUMBER ) IS
1406 SELECT sty_id
1407 FROM OKL_STRM_TYP_ALLOCS
1408 WHERE stream_allc_type = cp_str_all_type
1409 AND cat_id = cp_cat_id
1410 ORDER BY sequence_number;
1411
1412 -------------------------------------------------------------------------------
1413
1414 -- get cash applic rule id
1415 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
1416 SELECT to_number(a.object1_id1)
1417 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
1418 WHERE a.rgp_id = b.id
1419 AND b.rgd_code = 'LABILL'
1420 AND a.rule_information_category = 'LAINVD'
1421 AND a.dnz_chr_id = b.chr_id
1422 AND a.dnz_chr_id = cp_khr_id;
1423
1424 -------------------------------------------------------------------------------
1425
1426 -- get cash applic rule for contract
1427 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
1428 SELECT ID
1429 ,NAME
1430 ,AMOUNT_TOLERANCE_PERCENT
1431 ,DAYS_PAST_QUOTE_VALID_TOLERANC
1432 ,MONTHS_TO_BILL_AHEAD
1433 ,UNDER_PAYMENT_ALLOCATION_CODE
1434 ,OVER_PAYMENT_ALLOCATION_CODE
1435 ,RECEIPT_MSMTCH_ALLOCATION_CODE
1436 FROM OKL_CASH_ALLCTN_RLS
1437 WHERE CAU_ID = cp_cau_id
1438 AND START_DATE <= trunc(SYSDATE)
1439 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
1440
1441 -------------------------------------------------------------------------------
1442
1443 -- get default cash applic rule for organization
1444 CURSOR c_dflt_cash_applic_rule IS
1445 SELECT ID
1446 ,NAME
1447 ,AMOUNT_TOLERANCE_PERCENT
1448 ,DAYS_PAST_QUOTE_VALID_TOLERANC
1449 ,MONTHS_TO_BILL_AHEAD
1450 ,UNDER_PAYMENT_ALLOCATION_CODE
1451 ,OVER_PAYMENT_ALLOCATION_CODE
1452 ,RECEIPT_MSMTCH_ALLOCATION_CODE
1453 FROM OKL_CASH_ALLCTN_RLS
1454 WHERE default_rule = 'YES'
1455 AND TRUNC(end_date) IS NULL;
1456
1457 -- get default cash applic rule for organization
1458 CURSOR c_get_org_id(cp_khr_id IN NUMBER) IS
1459 SELECT org_id
1460 FROM okc_k_headers_All_b
1461 WHERE id = cp_khr_id;
1462
1463 CURSOR get_onacc_amt(cp_csh_rcpt_id IN NUMBER) IS
1464 SELECT sum(nvl(amount_applied,0))
1465 FROM AR_RECEIVABLE_APPLICATIONS_ALL
1466 WHERE status = 'ACC'
1467 AND cash_receipt_id = cp_csh_rcpt_id;
1468
1469 CURSOR chk_exp_flag(cp_rct_id IN NUMBER) IS
1470 SELECT nvl(expired_flag,'N') INTO l_expired_flag
1471 FROM OKL_TRX_CSH_RECEIPT_V
1472 WHERE cash_receipt_id = cp_rct_id;
1473 -------------------------------------------------------------------------------
1474
1475
1476 BEGIN
1477 log_file('apply_rcpt_to_contract_w_rule start');
1478
1479 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1480 open c_customer_acc_num (p_customer_num);
1481 fetch c_customer_acc_num into l_customer_num;
1482 close c_customer_acc_num;
1483 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1484
1485 -- get default cash application rule
1486 OPEN c_dflt_cash_applic_rule;
1487 FETCH c_dflt_cash_applic_rule INTO l_dflt_cat_id
1488 ,l_dflt_name
1489 ,l_dflt_tolerance
1490 ,l_dflt_days_past_quote_valid
1491 ,l_dflt_months_to_bill_ahead
1492 ,l_dflt_under_payment
1493 ,l_dflt_over_payment
1494 ,l_dflt_receipt_msmtch;
1495 CLOSE c_dflt_cash_applic_rule;
1496
1497 -- get cash application rule
1498 OPEN c_cash_rle_id_csr (l_contract_id);
1499 FETCH c_cash_rle_id_csr INTO l_cau_id;
1500 CLOSE c_cash_rle_id_csr;
1501
1502 IF l_cau_id IS NOT NULL THEN
1503
1504 OPEN c_cash_rule_csr (l_cau_id);
1505 FETCH c_cash_rule_csr INTO l_cat_id
1506 ,l_rule_name
1507 ,l_tolerance
1508 ,l_days_past_quote_valid
1509 ,l_months_to_bill_ahead
1510 ,l_under_payment
1511 ,l_over_payment
1512 ,l_receipt_msmtch;
1513 CLOSE c_cash_rule_csr;
1514
1515 IF l_tolerance IS NULL THEN
1516
1517 l_rule_name := l_dflt_name;
1518 l_cat_id := l_dflt_cat_id;
1519 l_tolerance := l_dflt_tolerance;
1520 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1521 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1522 l_under_payment := l_dflt_under_payment;
1523 l_over_payment := l_dflt_over_payment;
1524 l_receipt_msmtch := l_dflt_receipt_msmtch;
1525 END IF;
1526
1527 ELSE -- use default rule
1528
1529 l_rule_name := l_dflt_name;
1530 l_cat_id := l_dflt_cat_id;
1531 l_tolerance := l_dflt_tolerance;
1532 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1533 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1534 l_under_payment := l_dflt_under_payment;
1535 l_over_payment := l_dflt_over_payment;
1536 l_receipt_msmtch := l_dflt_receipt_msmtch;
1537
1538 END IF;
1539
1540 i := 0;
1541 l_expired_flag := NULL;
1542 IF l_receipt_id IS NOT NULL THEN
1543 OPEN chk_exp_flag(l_receipt_id);
1544 FETCH chk_exp_flag INTO l_expired_flag;
1545 CLOSE chk_exp_flag;
1546 END IF;
1547 IF l_expired_flag IS NULL THEN
1548 l_expired_flag :='N';
1549 END IF;
1550 log_file('l_expired_flag = '||l_expired_flag);
1551 ------------------------------------------------------------
1552 -- Convert receipt currency to invoice currency if different
1553 ------------------------------------------------------------
1554 -- get invoice amount due remaining and invoice currency
1555
1556 IF l_invoice_currency_code <> l_receipt_currency_code THEN
1557 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
1558 IF l_exchange_rate_type IS NULL THEN
1559 OKL_API.set_message( p_app_name => G_APP_NAME
1560 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
1561 );
1562 RAISE G_EXCEPTION_HALT_VALIDATION;
1563 ELSE
1564 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
1565 ,l_receipt_currency_code
1566 ,l_receipt_date
1567 ,l_exchange_rate_type
1568 );
1569 IF l_conversion_rate IN (0,-1) THEN
1570 -- Message Text: No exchange rate defined
1571 x_return_status := okl_api.G_RET_STS_ERROR;
1572 okl_api.set_message( p_app_name => G_APP_NAME,
1573 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
1574 RAISE G_EXCEPTION_HALT_VALIDATION;
1575 END IF;
1576 END IF;
1577 log_file('l_conversion_rate '||l_conversion_rate);
1578 l_converted_receipt_amount := (l_receipt_amount / l_conversion_rate);
1579 l_receipt_amount := l_converted_receipt_amount;
1580 log_file('receipt amount in inv currency '||l_receipt_amount);
1581 END IF;
1582
1583 log_file('l_receipt_id = '||l_receipt_id);
1584
1585 IF l_expired_flag = 'N' AND l_receipt_id IS NOT NULL THEN
1586 l_purpose_total := 0;
1587
1588 OPEN c_get_purpose_for_adv_rcpt(l_contract_id, l_receipt_id);
1589 LOOP
1590 FETCH c_get_purpose_for_adv_rcpt INTO l_purpose_id, l_purpose_amt;
1591 EXIT WHEN c_get_purpose_for_adv_rcpt%NOTFOUND;
1592 l_purpose_total := l_purpose_total + l_purpose_amt;
1593 END LOOP;
1594 CLOSE c_get_purpose_for_adv_rcpt;
1595
1596 IF l_purpose_total > l_receipt_amount THEN
1597 NULL; --ERROR; "EXIT WITH UNEXPECTED ERROR"
1598 END IF;
1599 END IF;
1600
1601 log_file('l_purpose_id = '||l_purpose_id);
1602 IF l_purpose_id IS NOT NULL AND l_expired_flag = 'N' THEN
1603
1604 l_applied_running_total := 0;
1605 l_amt_due_remaining_tot := 0;
1606 l_receipt_remaining := l_receipt_amount;
1607
1608 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ADVANCED ALLOCATION RULE USED');
1609 --Fixed bug 7034283, by nikshah
1610 i := 0;
1611 OPEN c_get_purpose_for_adv_rcpt (l_contract_id, l_receipt_id);
1612 LOOP
1613
1614 FETCH c_get_purpose_for_adv_rcpt INTO l_purpose_id, l_purpose_amt;
1615 EXIT WHEN c_get_purpose_for_adv_rcpt%NOTFOUND
1616 OR l_purpose_amt = 0;
1617 log_file('l_contract_num = '||l_contract_num);
1618 IF l_purpose_amt IS NULL THEN
1619
1620 IF l_contract_num IS NOT NULL THEN
1621 FOR c_open_invs_rcd in c_open_invs(l_contract_num, l_customer_num, l_purpose_id, l_receipt_currency_code) LOOP
1622 l_amt_due_remaining_tot := l_amt_due_remaining_tot + c_open_invs_rcd.amount_due_remaining;
1623 END LOOP;
1624 END IF;
1625 IF l_amt_due_remaining_tot > l_receipt_remaining THEN
1626 l_purpose_amt := l_receipt_remaining;
1627 ELSE
1628 l_purpose_amt := l_amt_due_remaining_tot;
1629 END IF;
1630 l_amt_due_remaining_tot := 0;
1631 ELSE
1632 --dkagrawa added following code to convert purpose amount into invoice currency
1633 IF l_invoice_currency_code <> l_receipt_currency_code THEN
1634 l_purpose_amt := l_purpose_amt/l_conversion_rate;
1635 END IF;
1636 IF l_purpose_amt > l_receipt_remaining THEN
1637 l_purpose_amt := l_receipt_remaining;
1638 END IF;
1639 END IF;
1640 IF l_contract_num IS NOT NULL THEN
1641 OPEN c_open_invs (l_contract_num, l_customer_num, l_purpose_id, l_receipt_currency_code);
1642 LOOP
1643 FETCH c_open_invs INTO c_open_invs_rec;
1644 EXIT WHEN c_open_invs%NOTFOUND
1645 OR l_purpose_amt = 0;
1646
1647 i := i + 1;
1648
1649 l_appl_tbl(i).ar_inv_id := c_open_invs_rec.ar_invoice_id;
1650 l_appl_tbl(i).AMOUNT_TO_APPLY := c_open_invs_rec.amount_due_remaining;
1651 l_appl_tbl(i).line_id := c_open_invs_rec.invoice_line_id;
1652
1653 IF l_appl_tbl(i).AMOUNT_TO_APPLY >= l_purpose_amt THEN
1654 l_appl_tbl(i).AMOUNT_TO_APPLY := l_purpose_amt;
1655 l_purpose_amt := 0;
1656 ELSE
1657 l_appl_tbl(i).AMOUNT_TO_APPLY := c_open_invs_rec.amount_due_remaining;
1658 l_purpose_amt := l_purpose_amt - l_appl_tbl(i).AMOUNT_TO_APPLY;
1659 END IF;
1660
1661 l_applied_running_total := l_applied_running_total + l_appl_tbl(i).AMOUNT_TO_APPLY;
1662
1663 END LOOP;
1664 CLOSE c_open_invs;
1665 END IF;
1666 l_receipt_remaining := l_receipt_amount - l_applied_running_total;
1667
1668 END LOOP;
1669 CLOSE c_get_purpose_for_adv_rcpt;
1670
1671 l_receipt_amount := l_receipt_amount - l_applied_running_total;
1672
1673 ELSE -- purpose is not defined, use cash application rule
1674 log_file('l_cau_id = '||l_cau_id);
1675 -- don't do cash application if CAR is 'On Account' -- abindal start
1676 IF NVL(l_cau_id, 0) = -1 THEN
1677 l_receipt_remaining := l_receipt_amount;
1678 ELSE -- abindal end
1679
1680 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'USING CASH APPLICATIONM RULE');
1681 IF l_customer_num IS NULL THEN
1682 OPEN c_get_cust_acct_num(l_receipt_id);
1683 FETCH c_get_cust_acct_num INTO l_customer_num; -- now this is a real customer number: HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE
1684 CLOSE c_get_cust_acct_num;
1685 END IF;
1686 log_file('calling auto_cashapp_for_contract ');
1687 log_file('l_receipt_amount = '||l_receipt_amount);
1688 okl_auto_cash_appl_rules_pvt.auto_cashapp_for_contract( p_api_version => l_api_version,
1689 p_init_msg_list => l_init_msg_list,
1690 x_return_status => l_return_status,
1691 x_msg_count => l_msg_count,
1692 x_msg_data => l_msg_data,
1693 p_customer_num => l_customer_num, -- HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE
1694 p_contract_num => l_contract_num,
1695 p_currency_code => l_receipt_currency_code,
1696 p_amount_app_to => l_receipt_amount,
1697 p_receipt_date => l_receipt_date,
1698 p_org_id => l_org_id,
1699 x_appl_tbl => l_autocash_appl_tbl,
1700 x_onacc_amount => x_onacc_amount,
1701 x_unapply_amount=> x_unapply_amount);
1702
1703 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1704 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1705 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1706 RAISE OKL_API.G_EXCEPTION_ERROR;
1707 END IF;
1708 log_file('l_autocash_appl_tbl.count = '||l_autocash_appl_tbl.count);
1709 l_applied_running_total := 0;
1710 IF l_autocash_appl_tbl.count > 0 THEN
1711 i := 1;
1712 FOR k IN l_autocash_appl_tbl.FIRST..l_autocash_appl_tbl.lAST LOOP
1713 IF l_autocash_appl_tbl(k).inv_lines_tbl.COUNT > 0 THEN
1714 FOR l IN l_autocash_appl_tbl(k).inv_lines_tbl.FIRST..l_autocash_appl_tbl(k).inv_lines_tbl.LAST LOOP
1715 l_appl_tbl(i).ar_inv_id := l_autocash_appl_tbl(k).inv_hdr_rec.invoice_id;
1716 l_appl_tbl(i).AMOUNT_TO_APPLY := l_autocash_appl_tbl(k).inv_lines_tbl(l).amount_applied;
1717 l_appl_tbl(i).line_id := l_autocash_appl_tbl(k).inv_lines_tbl(l).invoice_line_id;
1718 l_applied_running_total := l_applied_running_total + l_autocash_appl_tbl(k).inv_lines_tbl(l).amount_applied;
1719 log_file('l_appl_tbl(i).ar_inv_id = '||l_appl_tbl(i).ar_inv_id);
1720 log_file('l_appl_tbl(i).AMOUNT_TO_APPLY = '||l_appl_tbl(i).AMOUNT_TO_APPLY);
1721 log_file('l_appl_tbl(i).line_id = '||l_appl_tbl(i).line_id);
1722 i := i +1;
1723 END LOOP;
1724 END IF;
1725 END LOOP;
1726 END IF;
1727 l_receipt_remaining := l_receipt_amount - l_applied_running_total;
1728 END IF; -- 'On Account' CAR
1729
1730 END IF;
1731
1732 IF l_appl_tbl.COUNT > 0 THEN
1733 log_file('calling process_advance_receipt');
1734 process_advance_receipt( p_api_version => l_api_version
1735 ,p_init_msg_list => l_init_msg_list
1736 ,x_return_status => l_return_status
1737 ,x_msg_count => l_msg_count
1738 ,x_msg_data => l_msg_data
1739 ,p_receipt_id => l_receipt_id
1740 ,p_org_id => l_org_id
1741 ,p_appl_tbl => l_appl_tbl
1742 ,x_remaining_amt => p_remain_rcpt_amount
1743 );
1744 log_file('l_return_status = '||l_return_status);
1745 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1746 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1747 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1748 RAISE OKL_API.G_EXCEPTION_ERROR;
1749 END IF;
1750 END IF;
1751 log_file('p_remain_rcpt_amount in invoice currency = '||l_receipt_remaining);
1752 IF l_invoice_currency_code <> l_receipt_currency_code THEN
1753 p_remain_rcpt_amount := l_receipt_remaining*l_conversion_rate;
1754 ELSE
1755 p_remain_rcpt_amount := l_receipt_remaining;
1756 END IF;
1757 log_file('p_remain_rcpt_amount in receipt currency = '||p_remain_rcpt_amount);
1758 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1759
1760 Okl_Api.END_ACTIVITY ( x_msg_count => x_msg_count,
1761 x_msg_data => x_msg_data);
1762
1763 log_file('end apply_rcpt_to_contract_w_rule');
1764 EXCEPTION
1765
1766 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1767 x_return_status := okl_api.G_RET_STS_ERROR;
1768
1769
1770 WHEN okl_api.G_EXCEPTION_ERROR THEN
1771 x_return_status := okl_api.HANDLE_EXCEPTIONS
1772 (
1773 l_api_name,
1774 G_PKG_NAME,
1775 OKL_API.G_RET_STS_ERROR,
1776 x_msg_count,
1777 x_msg_data,
1778 '_PVT'
1779 );
1780
1781 WHEN OTHERS THEN
1782 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1783 Okl_api.set_message( p_app_name => g_app_name
1784 , p_msg_name => g_unexpected_error
1785 , p_token1 => g_sqlcode_token
1786 , p_token1_value => SQLCODE
1787 , p_token2 => g_sqlerrm_token
1788 , p_token2_value => SQLERRM
1789 ) ;
1790
1791 END apply_rcpt_to_contract_w_rule;
1792
1793 ---------------------------------------------------------------------------
1794 -- PROCEDURE reapplic_advanced_cash_app
1795 -- Re application of advanced cash
1796 ---------------------------------------------------------------------------
1797
1798 PROCEDURE reapplic_advanced_cash_app ( p_api_version IN NUMBER
1799 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
1800 ,x_return_status OUT NOCOPY VARCHAR2
1801 ,x_msg_count OUT NOCOPY NUMBER
1802 ,x_msg_data OUT NOCOPY VARCHAR2
1803 ,p_contract_num IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL
1804 ,p_customer_num IN AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
1805 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
1806 ,p_receipt_num IN OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT NULL
1807 ,p_receipt_date_from IN OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL
1808 ,p_receipt_date_to IN OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL
1809 ,p_receipt_type IN VARCHAR2 DEFAULT NULL
1810 ,p_cross_currency_allowed IN VARCHAR2 DEFAULT 'N'
1811 ) IS
1812
1813
1814 ---------------------------
1815 -- DECLARE Local Variables
1816 ---------------------------
1817
1818 l_api_version NUMBER := 1.0;
1819 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
1820 l_return_status VARCHAR2(1);
1821 l_msg_count NUMBER;
1822 l_msg_data VARCHAR2(2000);
1823 l_api_name CONSTANT VARCHAR2(30) := 'reapplic_advanced_cash_app';
1824
1825 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
1826 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
1827 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_ID%TYPE DEFAULT NULL;
1828 -- l_customer_num AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL;
1829 --start:| 13-May-2008 cklee --Fixed bug 7036445 |
1830 l_customer_num hz_cust_accounts.account_number%TYPE;
1831 --end:| 13-May-2008 cklee --Fixed bug 7036445 |
1832
1833 l_customer_acct_id NUMBER DEFAULT p_customer_num;
1834 l_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
1835 l_receipt_num OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT p_receipt_num;
1836 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
1837 l_cross_currency_allowed VARCHAR2(1) DEFAULT p_cross_currency_allowed;
1838
1839 l_receipt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE;
1840 l_receipt_date_from OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_receipt_date_from);
1841 l_receipt_date_to OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_receipt_date_to);
1842
1843 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
1844
1845 l_dflt_days_to_hold_adv_pay OKL_CASH_ALLCTN_RLS.NUM_DAYS_HOLD_ADV_PAY%TYPE;
1846 l_days_to_hold_adv_pay OKL_CASH_ALLCTN_RLS.NUM_DAYS_HOLD_ADV_PAY%TYPE;
1847
1848 l_temp_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
1849 l_temp_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
1850 l_temp_rcpt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT NULL;
1851
1852 l_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
1853 l_remain_rcpt_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
1854 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE;
1855 l_actual_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
1856 l_on_account_bal AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
1857 l_receipt_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE;
1858 l_receipt_count NUMBER;
1859 l_receipt_type VARCHAR2(30) := p_receipt_type;
1860
1861 l_invoice_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
1862 l_inv_tot NUMBER := 0;
1863
1864 --
1865 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE;
1866 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE;
1867 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE;
1868 --
1869
1870
1871 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
1872
1873 l_okl_receipt_id NUMBER;
1874 l_xcr_id NUMBER;
1875 l_rct_id OKL_TRX_CSH_RECEIPT_V.ID%TYPE;
1876 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
1877 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
1878 i NUMBER DEFAULT NULL;
1879
1880
1881 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
1882
1883 l_stat_total_rcpt_amt OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT 0;
1884 l_stat_num_of_rcpts NUMBER DEFAULT 0;
1885 l_stat_num_of_cont NUMBER DEFAULT 0;
1886
1887 l_exit_loop NUMBER DEFAULT 0;
1888 l_exchange_rate_type VARCHAR2(100);
1889 l_conversion_rate NUMBER;
1890 -------------------------------------------------------------------------------
1891 -- DECLARE Record/Table Types
1892 -------------------------------------------------------------------------------
1893 -- Internal Trans
1894
1895 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1896 l_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
1897
1898 l_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
1899 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1900
1901 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
1902 x_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
1903
1904 x_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
1905 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
1906 -------------------------------------------------------------------------------
1907 -- DEFINE CURSORS
1908 -------------------------------------------------------------------------------
1909
1910 CURSOR c_get_cont_for_cust(cp_customer_num IN VARCHAR2,
1911 cp_contract_num IN VARCHAR2) IS
1912 SELECT DISTINCT(lpt.contract_number),
1913 lpt.khr_id,
1914 lpt.start_date,
1915 lpt.currency_code,
1916 lpt.customer_account_number
1917 FROM okl_rcpt_cust_cont_balances_uv lpt
1918 WHERE lpt.customer_account_number = nvl(cp_customer_num,lpt.customer_account_number)
1919 AND lpt.contract_number = NVL(cp_contract_num,lpt.contract_number)
1920 AND lpt.status = 'OP'
1921 AND lpt.amount_due_remaining > 0
1922 AND lpt.org_id = mo_global.get_current_org_id
1923 ORDER BY lpt.start_date;
1924
1925 c_get_cont_for_cust_rec c_get_cont_for_cust%ROWTYPE;
1926
1927 -------------------------------------------------------------------------------
1928
1929 -- get advanced receipt details to apply to given contract
1930 CURSOR c_get_rcpt_dtls(cp_icr_id IN NUMBER) IS
1931 SELECT b.amount,
1932 b.receipt_number,
1933 b.currency_code,
1934 b.receipt_date,
1935 a.id
1936 FROM OKL_TRX_CSH_RECEIPT_V a,
1937 ar_Cash_receipts_all b
1938 WHERE a.fully_applied_flag = 'N'
1939 AND a.expired_flag = 'N'
1940 AND a.receipt_type = 'ADV'
1941 AND a.cash_receipt_id = b.cash_receipt_id
1942 AND a.cash_receipt_id = cp_icr_id;
1943
1944 c_get_rcpt_dtls_rec c_get_rcpt_dtls%ROWTYPE;
1945 -------------------------------------------------------------------------------
1946
1947 -- get all unapplied advanced receipts
1948 CURSOR c_get_all_adv_rcpt IS
1949 SELECT DISTINCT(c.cash_receipt_id) icr_id,
1950 a.id RCT_ID,
1951 c.receipt_date,
1952 b.khr_id
1953 FROM OKL_TRX_CSH_RECEIPT_V a,
1954 OKL_TXL_RCPT_APPS_V b,
1955 AR_CASH_RECEIPTS_ALL c
1956 WHERE a.id = b.rct_id_details
1957 AND a.cash_receipt_id = c.cash_receipt_id
1958 AND a.FULLY_APPLIED_FLAG = 'N'
1959 AND a.receipt_type = 'ADV'
1960 AND a.expired_flag = 'N'
1961 AND b.khr_id IS NOT NULL
1962 ORDER BY c.receipt_date;
1963
1964 c_get_all_adv_rcpt_rec c_get_all_adv_rcpt%ROWTYPE;
1965
1966 -------------------------------------------------------------------------------
1967 /*
1968 -- get all regular and advanced receipts w/balance
1969 CURSOR c_get_all_rcpt_dtls (cp_customer_num IN VARCHAR2) IS
1970 SELECT b.amount,
1971 b.receipt_number,
1972 b.currency_code,
1973 a.ile_id,
1974 a.id,
1975 b.cash_receipt_id icr_id,
1976 b.pay_from_customer customer_number
1977 FROM OKL_TRX_CSH_RECEIPT_V a,
1978 AR_CASH_RECEIPTS_ALL b,
1979 hz_cust_accounts_all hca
1980 WHERE a.cash_receipt_id = b.cash_receipt_id
1981 AND hca.account_number = cp_customer_num
1982 AND b.pay_from_customer = hca.cust_account_id
1983 AND a.fully_applied_flag = 'N';
1984
1985 c_get_all_rcpt_dtls_rec c_get_all_rcpt_dtls%ROWTYPE;*/
1986
1987 -------------------------------------------------------------------------------
1988
1989 -- get customer id from customer number
1990 -- replaced the reference ra_customers to hz_cust_accounts for bug#4891734
1991 CURSOR c_get_cust_id(cp_customer_num IN VARCHAR2) IS
1992 SELECT cust_account_id
1993 FROM hz_cust_accounts
1994 WHERE account_number = cp_customer_num;
1995
1996 -------------------------------------------------------------------------------
1997
1998 -- get all applied receipts for customer within date range
1999 CURSOR c_get_csh_rcpt_id( cp_customer_id IN NUMBER
2000 ,cp_receipt_date_from IN DATE
2001 ,cp_receipt_date_to IN DATE
2002 ,cp_receipt_currency IN VARCHAR2 ) IS
2003 SELECT cash_receipt_id,
2004 currency_code,
2005 amount,
2006 receipt_date,
2007 receipt_number,
2008 decode(currency_code,cp_receipt_currency,1,2) order_by_ccy
2009 FROM AR_CASH_RECEIPTS
2010 WHERE pay_from_customer = cp_customer_id
2011 AND currency_code = decode(p_cross_currency_allowed,'N',cp_receipt_currency,currency_code) --dkagrawa added decode for cross currency support
2012 AND receipt_date >= cp_receipt_date_from
2013 AND receipt_date <= cp_receipt_date_to
2014 ORDER BY order_by_ccy ASC;
2015
2016 c_get_csh_rcpt_id_rec c_get_csh_rcpt_id%ROWTYPE;
2017
2018 -------------------------------------------------------------------------------
2019
2020 -- verify on account receipt amount
2021 CURSOR c_ver_on_acct_amt(cp_csh_rcpt_id IN NUMBER) IS
2022 SELECT (unapplied_amount + onaccount_amount) amount_available
2023 FROM okl_receipt_Details_uv
2024 WHERE cash_receipt_id = cp_csh_rcpt_id;
2025
2026 -------------------------------------------------------------------------------
2027
2028 -- get contract total
2029 CURSOR c_open_invs ( cp_contract_num IN VARCHAR2
2030 ,cp_customer_num IN VARCHAR2
2031 ,cp_stream_type_id IN NUMBER
2032 ,cp_currency_code IN VARCHAR2) IS
2033 SELECT lpt.sty_id
2034 ,lpt.amount_due_remaining
2035 ,lpt.currency_code
2036 ,lpt.ar_invoice_number
2037 ,lpt.trx_date
2038 ,lpt.customer_account_number
2039 ,lpt.customer_acct_id
2040 ,lpt.khr_id
2041 ,lpt.ar_invoice_id
2042 ,lpt.invoice_line_id
2043 FROM okl_rcpt_cust_cont_balances_uv lpt
2044 WHERE lpt.contract_number = cp_contract_num
2045 AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
2046 AND lpt.sty_id = NVL (cp_stream_type_id, lpt.sty_id)
2047 AND lpt.status = 'OP'
2048 AND lpt.amount_due_remaining > 0
2049 AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
2050
2051 c_open_invs_rec c_open_invs%ROWTYPE;
2052
2053 -------------------------------------------------------------------------------
2054
2055 CURSOR c_get_rcpt_id ( cp_receipt_number in VARCHAR2
2056 ,cp_customer_num in VARCHAR2) IS
2057 SELECT a.cash_receipt_id icr_id
2058 FROM AR_CASH_RECEIPTS a,
2059 hz_cust_Accounts_all hca
2060 WHERE a.receipt_number = l_receipt_num
2061 AND a.pay_from_customer = hca.cust_account_id
2062 AND hca.account_number = cp_customer_num;
2063
2064 -------------------------------------------------------------------------------
2065
2066 -- get cash applic rule id
2067 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
2068 SELECT to_number(a.object1_id1)
2069 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
2070 WHERE a.rgp_id = b.id
2071 AND b.rgd_code = 'LABILL'
2072 AND a.rule_information_category = 'LAINVD'
2073 AND a.dnz_chr_id = b.chr_id
2074 AND a.dnz_chr_id = cp_khr_id;
2075
2076 -------------------------------------------------------------------------------
2077
2078 -- get cash applic rule for contract
2079 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
2080 SELECT NUM_DAYS_HOLD_ADV_PAY
2081 FROM OKL_CASH_ALLCTN_RLS
2082 WHERE CAU_ID = cp_cau_id
2083 AND START_DATE <= trunc(SYSDATE)
2084 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
2085
2086 -------------------------------------------------------------------------------
2087
2088 -- get default cash applic rule for organization
2089 CURSOR c_dflt_cash_applic_rule IS
2090 SELECT NUM_DAYS_HOLD_ADV_PAY
2091 FROM OKL_CASH_ALLCTN_RLS
2092 WHERE default_rule = 'YES'
2093 AND TRUNC(end_date) IS NULL;
2094
2095 CURSOR c_get_cust_Acct_num(cp_acct_id in number) IS
2096 SELECT account_number
2097 from hz_cust_accounts
2098 where cust_Account_id = cp_acct_id;
2099 -------------------------------------------------------------------------------
2100
2101 BEGIN
2102 log_file('reapplic_advanced_cash_app start');
2103 log_file('l_customer_acct_id = '||l_customer_acct_id);
2104 log_file('l_contract_num = '||l_contract_num);
2105 log_file('l_receipt_num = '||l_receipt_num);
2106 log_file('l_receipt_date_from = '||l_receipt_date_from);
2107 log_file('l_receipt_date_to = '||l_receipt_date_to);
2108 ------------------------------------------------------------
2109 -- Start processing
2110 ------------------------------------------------------------
2111
2112 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2113
2114 l_return_status := Okl_Api.START_ACTIVITY(
2115 p_api_name => l_api_name,
2116 p_pkg_name => G_PKG_NAME,
2117 p_init_msg_list => p_init_msg_list,
2118 l_api_version => l_api_version,
2119 p_api_version => p_api_version,
2120 p_api_type => '_PVT',
2121 x_return_status => l_return_status);
2122
2123 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2124 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2125 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2126 RAISE Okl_Api.G_EXCEPTION_ERROR;
2127 END IF;
2128
2129 -- get default cash application rule
2130 OPEN c_dflt_cash_applic_rule;
2131 FETCH c_dflt_cash_applic_rule INTO l_dflt_days_to_hold_adv_pay;
2132 CLOSE c_dflt_cash_applic_rule;
2133
2134 IF l_customer_acct_id IS NOT NULL THEN
2135 OPEN c_get_cust_Acct_num(l_customer_acct_id);
2136 FETCH c_get_cust_Acct_num INTO l_customer_num; -- HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE
2137 CLOSE c_get_cust_Acct_num;
2138 END IF;
2139 log_file('l_customer_num = '||l_customer_num);
2140 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=================================================================================');
2141 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' ** Start Processing. Please See Error Log for any errored transactions ** ');
2142 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '=================================================================================');
2143
2144 IF l_customer_num IS NULL AND
2145 l_contract_num IS NULL AND
2146 l_receipt_date_from IS NULL AND
2147 l_receipt_date_to IS NULL AND
2148 l_receipt_num IS NULL THEN
2149 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------');
2150 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - No input parameters specified');
2151 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------');
2152 GOTO END_PROCESS;
2153 END IF;
2154
2155 IF l_receipt_num IS NOT NULL AND l_contract_num IS NULL THEN
2156 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------');
2157 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - You must specify a contract number to apply cash to');
2158 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------');
2159 GOTO END_PROCESS;
2160 END IF;
2161
2162 IF l_customer_num IS NOT NULL AND (l_receipt_date_from IS NULL OR l_receipt_date_to IS NULL) THEN
2163 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------');
2164 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - You must specify start and end dates for receipt');
2165 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------');
2166 GOTO END_PROCESS;
2167 END IF;
2168
2169 IF l_customer_num IS NOT NULL AND (l_receipt_type IS NULL) THEN
2170 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------');
2171 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - You must specify Receipt Type');
2172 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------');
2173 GOTO END_PROCESS;
2174 END IF;
2175
2176
2177
2178 ---------------------------------------------------------------
2179 -- First do a clean up ...
2180 -- Find all expired receipts and make them available to all ...
2181 -- BEGIN CLEAN UP PROCESS
2182 ---------------------------------------------------------------
2183
2184 log_file('clean up starts');
2185 FOR c_get_all_adv_rcpt_rec IN c_get_all_adv_rcpt
2186 LOOP
2187 l_temp_receipt_id := c_get_all_adv_rcpt_rec.icr_id;
2188 l_rct_id := c_get_all_adv_rcpt_rec.rct_id;
2189 l_temp_contract_id := c_get_all_adv_rcpt_rec.khr_id;
2190 l_temp_rcpt_date := c_get_all_adv_rcpt_rec.receipt_date;
2191 log_file('l_temp_receipt_id = '||l_temp_receipt_id);
2192 OPEN c_ver_on_acct_amt(l_temp_receipt_id);
2193 FETCH c_ver_on_acct_amt INTO l_actual_remittance_amount;
2194 CLOSE c_ver_on_acct_amt;
2195 log_file('l_actual_remittance_amount = '||l_actual_remittance_amount);
2196 IF l_actual_remittance_amount > 0 THEN
2197 -- get cash application rule
2198 OPEN c_cash_rle_id_csr (l_temp_contract_id);
2199 FETCH c_cash_rle_id_csr INTO l_cau_id;
2200 CLOSE c_cash_rle_id_csr;
2201
2202 IF l_cau_id IS NOT NULL THEN
2203 OPEN c_cash_rule_csr (l_cau_id);
2204 FETCH c_cash_rule_csr INTO l_days_to_hold_adv_pay;
2205 CLOSE c_cash_rule_csr;
2206
2207 IF l_days_to_hold_adv_pay IS NULL THEN
2208 l_days_to_hold_adv_pay := l_dflt_days_to_hold_adv_pay;
2209 END IF;
2210 ELSE -- use default rule
2211 l_days_to_hold_adv_pay := l_dflt_days_to_hold_adv_pay;
2212 END IF;
2213
2214 IF TRUNC(l_temp_rcpt_date) + l_days_to_hold_adv_pay < TRUNC(SYSDATE) THEN
2215 l_rctv_rec.expired_flag := 'Y';
2216 ELSE
2217 l_rctv_rec.expired_flag := 'N';
2218 END IF;
2219
2220 END IF;
2221
2222 IF l_actual_remittance_amount = 0 OR l_actual_remittance_amount IS NULL THEN
2223 l_rctv_rec.fully_applied_flag := 'Y';
2224 l_rctv_rec.expired_flag := 'Y';
2225 ELSE
2226 l_rctv_rec.fully_applied_flag := 'N';
2227 END IF;
2228 log_file('l_rctv_rec.fully_applied_flag = '||l_rctv_rec.fully_applied_flag);
2229 log_file('l_rctv_rec.expired_flag = '||l_rctv_rec.expired_flag);
2230 l_rctv_rec.id := l_rct_id;
2231
2232 OKL_RCT_PVT.update_row( p_api_version => l_api_version
2233 ,p_init_msg_list => l_init_msg_list
2234 ,x_return_status => l_return_status
2235 ,x_msg_count => l_msg_count
2236 ,x_msg_data => l_msg_data
2237 ,p_rctv_rec => l_rctv_rec
2238 ,x_rctv_rec => x_rctv_rec);
2239
2240 x_return_status := l_return_status;
2241
2242 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2243 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2244 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2245 RAISE OKL_API.G_EXCEPTION_ERROR;
2246 END IF;
2247
2248 END LOOP;
2249
2250 ---------------------------------------------------------------
2251 -- END CLEAN UP PROCESS
2252 ---------------------------------------------------------------
2253
2254 ---------------------------------------------------------------
2255 -- Then check for specified contract
2256 ---------------------------------------------------------------
2257
2258 IF l_contract_num IS NOT NULL AND l_receipt_num IS NOT NULL THEN
2259
2260 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT NUMBER: '||l_contract_num);
2261
2262 IF l_receipt_num IS NOT NULL THEN
2263 l_receipt_id := NULL;
2264 OPEN c_get_rcpt_id(l_receipt_num, l_customer_num);
2265 FETCH c_get_rcpt_id INTO l_receipt_id;
2266 CLOSE c_get_rcpt_id;
2267 END IF;
2268 log_file('l_receipt_id = '||l_receipt_id);
2269 IF l_receipt_id IS NOT NULL THEN
2270 OPEN c_get_rcpt_dtls(l_receipt_id);
2271 FETCH c_get_rcpt_dtls INTO l_remittance_amount
2272 ,l_check_number
2273 ,l_receipt_currency
2274 ,l_receipt_date
2275 ,l_rct_id;
2276 CLOSE c_get_rcpt_dtls;
2277 END IF;
2278
2279 FOR c_open_invs_rec IN c_open_invs (l_contract_num, NULL, NULL, l_receipt_currency)
2280 LOOP
2281 log_file('l_inv_tot = '||l_inv_tot);
2282 l_invoice_currency_code := c_open_invs_rec.currency_code;
2283 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
2284 l_customer_num := c_open_invs_rec.customer_account_number;
2285 l_customer_id := c_open_invs_rec.customer_acct_id;
2286 l_contract_id := c_open_invs_rec.khr_id;
2287 END LOOP;
2288 log_file('l_inv_tot = '||l_inv_tot);
2289 IF l_inv_tot > 0 THEN
2290 IF l_receipt_id IS NOT NULL THEN
2291
2292 OPEN c_ver_on_acct_amt(l_receipt_id);
2293 FETCH c_ver_on_acct_amt INTO l_actual_remittance_amount;
2294 CLOSE c_ver_on_acct_amt;
2295
2296 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT NUMBER: '||l_check_number);
2297 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT AMOUNT: '||l_actual_remittance_amount);
2298
2299 IF l_actual_remittance_amount = 0 THEN
2300 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Receipt number '||l_check_number||' has a zero balance - Cash application cannot continue');
2301 GOTO END_PROCESS;
2302 END IF;
2303
2304 IF l_actual_remittance_amount <> l_remittance_amount THEN
2305 l_remittance_amount := l_actual_remittance_amount;
2306 END IF;
2307 log_file('l_remittance_amount ='||l_remittance_amount);
2308 --following code added by dkagrawa for cross currency
2309 IF l_invoice_currency_code <> l_receipt_currency THEN
2310 l_exchange_rate_type := OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);--FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2311 IF l_exchange_rate_type IS NULL THEN
2312 OKL_API.set_message( p_app_name => G_APP_NAME
2313 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2314 );
2315 RAISE G_EXCEPTION_HALT_VALIDATION;
2316 ELSE
2317 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
2318 ,l_receipt_currency
2319 ,l_receipt_date
2320 ,l_exchange_rate_type
2321 );
2322 IF l_conversion_rate IN (0,-1) THEN
2323 -- Message Text: No exchange rate defined
2324 x_return_status := okl_api.G_RET_STS_ERROR;
2325 okl_api.set_message( p_app_name => G_APP_NAME,
2326 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2327 RAISE G_EXCEPTION_HALT_VALIDATION;
2328 END IF;
2329 END IF;
2330 l_inv_tot := l_inv_tot*l_conversion_rate;
2331 END IF;
2332 IF l_inv_tot <= l_remittance_amount THEN
2333
2334 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'No cash application rules required. Balance of contract is less than or equal to receipt amount');
2335 log_file('calling apply_rcpt_to_contract_no_rule');
2336 apply_rcpt_to_contract_no_rule ( p_api_version => l_api_version
2337 ,p_init_msg_list => l_init_msg_list
2338 ,x_return_status => l_return_status
2339 ,x_msg_count => l_msg_count
2340 ,x_msg_data => l_msg_data
2341 ,p_contract_id => l_contract_id
2342 ,p_contract_num => l_contract_num
2343 ,p_customer_id => l_customer_id
2344 -- ,p_customer_num => l_customer_num
2345 ,p_customer_num => p_customer_num -- cklee 7036445-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
2346 ,p_receipt_id => l_receipt_id
2347 ,p_receipt_amount => l_remittance_amount
2348 ,p_remain_rcpt_amount => l_remain_rcpt_amount
2349 ,p_receipt_currency => l_receipt_currency
2350 ,p_receipt_date => l_receipt_date
2351 ,p_invoice_currency => l_invoice_currency_code
2352 ,p_xcr_id => l_rct_id
2353 ,p_cross_currency_allowed => l_cross_currency_allowed
2354 );
2355 log_file('l_return_status ='||l_return_status);
2356 ELSIF l_inv_tot > l_remittance_amount THEN
2357
2358 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Cash application rule required. Balance of contract is greater than receipt amount');
2359 log_file('calling apply_rcpt_to_contract_w_rule');
2360 apply_rcpt_to_contract_w_rule ( p_api_version => l_api_version
2361 ,p_init_msg_list => l_init_msg_list
2362 ,x_return_status => l_return_status
2363 ,x_msg_count => l_msg_count
2364 ,x_msg_data => l_msg_data
2365 ,p_contract_id => l_contract_id
2366 ,p_contract_num => l_contract_num
2367 ,p_customer_id => l_customer_id
2368 -- ,p_customer_num => l_customer_num
2369 ,p_customer_num => p_customer_num -- cklee 7036445-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
2370 ,p_receipt_id => l_receipt_id
2371 ,p_receipt_amount => l_remittance_amount
2372 ,p_remain_rcpt_amount => l_remain_rcpt_amount
2373 ,p_receipt_currency => l_receipt_currency
2374 ,p_receipt_date => l_receipt_date
2375 ,p_invoice_currency => l_invoice_currency_code
2376 ,p_xcr_id => l_rct_id
2377 ,p_cross_currency_allowed => l_cross_currency_allowed
2378 );
2379 log_file('l_return_status ='||l_return_status);
2380 END IF;
2381
2382 x_return_status := l_return_status;
2383
2384 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2386 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2387 RAISE OKL_API.G_EXCEPTION_ERROR;
2388 END IF;
2389 log_file('l_remain_rcpt_amount = '||l_remain_rcpt_amount);
2390 IF l_remain_rcpt_amount <> l_actual_remittance_amount THEN -- application took place, move to next contract ...
2391
2392 l_stat_total_rcpt_amt := l_stat_total_rcpt_amt + (l_actual_remittance_amount - l_remain_rcpt_amount);
2393 l_stat_num_of_rcpts := l_stat_num_of_rcpts + 1;
2394 l_stat_num_of_cont := l_stat_num_of_cont + 1;
2395
2396 END IF;
2397
2398 IF l_remain_rcpt_amount > 0 THEN
2399 l_rctv_rec.FULLY_APPLIED_FLAG := 'N';
2400 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Receipt number '||l_check_number||' has been partially applied.');
2401 ELSE
2402 l_rctv_rec.FULLY_APPLIED_FLAG := 'Y';
2403 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Receipt number '||l_check_number||' has been fully applied.');
2404 END IF;
2405 -- commented out for testing purposes.
2406 -------------------------------------------------------------------
2407 -- COMMIT; -- Need to commit here to update balance of contract ...
2408 -------------------------------------------------------------------
2409
2410 GOTO END_PROCESS;
2411
2412 ELSE -- we just have contract_number w/no receipt amount
2413 log_file('CALLING advanced_cash_app ');
2414 advanced_cash_app ( p_api_version => l_api_version
2415 ,p_init_msg_list => l_init_msg_list
2416 ,x_return_status => l_return_status
2417 ,x_msg_count => l_msg_count
2418 ,x_msg_data => l_msg_data
2419 ,p_contract_num => l_contract_num
2420 -- ,p_customer_num => l_customer_num
2421 ,p_customer_num => p_customer_num -- cklee 7036445-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
2422 ,p_receipt_num => null
2423 ,p_cross_currency_allowed => l_cross_currency_allowed
2424 );
2425 log_file('l_return_status = '||l_return_status);
2426 x_return_status := l_return_status;
2427
2428 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2429 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2430 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2431 RAISE OKL_API.G_EXCEPTION_ERROR;
2432 END IF;
2433
2434 END IF;
2435 ELSE
2436 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT NUMBER: '||l_contract_num ||' has no balance.');
2437 END IF; -- IF l_inv_tot > 0
2438 GOTO END_PROCESS;
2439
2440 END IF; -- if we have contract number
2441
2442 IF (l_customer_num IS NOT NULL AND l_receipt_type = 'ADV') OR
2443 (l_customer_num IS NULL AND l_contract_num IS NOT NULL)
2444 THEN
2445
2446 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CUSTOMER NUMBER: '||l_customer_num);
2447
2448 FOR c_get_cont_for_cust_rec IN c_get_cont_for_cust (l_customer_num, l_contract_num)
2449 LOOP
2450
2451 l_contract_num := c_get_cont_for_cust_rec.contract_number;
2452 l_contract_id := c_get_cont_for_cust_rec.khr_id;
2453 l_customer_num := c_get_cont_for_cust_rec.customer_account_number;
2454
2455 advanced_cash_app ( p_api_version => l_api_version
2456 ,p_init_msg_list => l_init_msg_list
2457 ,x_return_status => l_return_status
2458 ,x_msg_count => l_msg_count
2459 ,x_msg_data => l_msg_data
2460 ,p_contract_num => l_contract_num
2461 -- ,p_customer_num => l_customer_num
2462 ,p_customer_num => p_customer_num -- cklee 7036445-- note: p_customer_num = HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID indeed
2463 ,p_receipt_num => l_receipt_num
2464 ,p_cross_currency_allowed => l_cross_currency_allowed
2465 );
2466
2467 x_return_status := l_return_status;
2468
2469 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2470 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2471 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2472 RAISE OKL_API.G_EXCEPTION_ERROR;
2473 END IF;
2474
2475 END LOOP;
2476
2477 GOTO END_PROCESS;
2478
2479 END IF;
2480
2481 IF l_customer_num IS NOT NULL AND l_receipt_type = 'ALL' THEN
2482
2483 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2484 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CUSTOMER NUMBER: '||l_customer_num);
2485
2486 -- get contract details first, then loop through receipts report all events on form
2487
2488 OPEN c_get_cust_id(l_customer_num);
2489 FETCH c_get_cust_id INTO l_customer_id;-- note: HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
2490 CLOSE c_get_cust_id;
2491 log_file('l_customer_id = '||l_customer_id);-- note: HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
2492 FOR c_get_cont_for_cust_rec IN c_get_cont_for_cust (l_customer_num, l_contract_num)
2493 LOOP
2494
2495 l_contract_num := c_get_cont_for_cust_rec.contract_number;
2496 l_contract_id := c_get_cont_for_cust_rec.khr_id;
2497 log_file('l_contract_num = '||l_contract_num);
2498 l_inv_tot := 0;
2499 FOR c_open_invs_rec IN c_open_invs (l_contract_num, l_customer_num, NULL, c_get_cont_for_cust_rec.currency_code)
2500 LOOP
2501 l_invoice_currency_code := c_open_invs_rec.currency_code;
2502 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
2503 END LOOP;
2504 log_file('l_inv_tot = '||l_inv_tot);
2505 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '---------------');
2506 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT NUMBER: '||l_contract_num|| ' located for cash application ');
2507 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT BALANCE: '||l_inv_tot);
2508 IF l_inv_tot = 0 THEN
2509 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------');
2510 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Following contract has a balance of zero -- unable to apply receipts');
2511 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, l_contract_num);
2512 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-----------------------------------------------------------------------------');
2513 END IF;
2514 IF l_inv_tot > 0 THEN
2515 FOR c_get_csh_rcpt_id_rec IN c_get_csh_rcpt_id (l_customer_id, l_receipt_date_from, l_receipt_date_to, l_invoice_currency_code)
2516 LOOP
2517
2518 l_exit_loop := 0;
2519
2520 l_receipt_id := c_get_csh_rcpt_id_rec.cash_receipt_id;
2521 l_receipt_date := c_get_csh_rcpt_id_rec.receipt_date;
2522 l_receipt_currency := c_get_csh_rcpt_id_rec.currency_code;
2523 l_check_number := c_get_csh_rcpt_id_rec.receipt_number;
2524 log_file('l_receipt_id = '||l_receipt_id);
2525 log_file('l_check_number = '|| l_check_number);
2526 OPEN c_ver_on_acct_amt(l_receipt_id);
2527 FETCH c_ver_on_acct_amt INTO l_actual_remittance_amount;
2528 CLOSE c_ver_on_acct_amt;
2529
2530 l_remittance_amount := l_actual_remittance_amount;
2531 log_file('l_remittance_amount = '||l_remittance_amount);
2532 IF l_actual_remittance_amount > 0 THEN
2533
2534 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'RECEIPT '||l_check_number|| ' located with balance of '||l_actual_remittance_amount||' '||l_receipt_currency);
2535 IF l_invoice_currency_code <> l_receipt_currency THEN
2536 l_exchange_rate_type :=OKL_RECEIPTS_PVT.cross_currency_rate_type(l_org_id);-- FND_PROFILE.value('AR_CROSS_CURRENCY_RATE_TYPE');
2537 IF l_exchange_rate_type IS NULL THEN
2538 OKL_API.set_message( p_app_name => G_APP_NAME
2539 ,p_msg_name => 'OKL_BPD_CONV_TYPE_NOT_FOUND'
2540 );
2541 RAISE G_EXCEPTION_HALT_VALIDATION;
2542 ELSE
2543 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_invoice_currency_code
2544 ,l_receipt_currency
2545 ,l_receipt_date
2546 ,l_exchange_rate_type
2547 );
2548 IF l_conversion_rate IN (0,-1) THEN
2549 -- Message Text: No exchange rate defined
2550 x_return_status := okl_api.G_RET_STS_ERROR;
2551 okl_api.set_message( p_app_name => G_APP_NAME,
2552 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2553 RAISE G_EXCEPTION_HALT_VALIDATION;
2554 END IF;
2555 END IF;
2556 l_inv_tot := l_inv_tot*l_conversion_rate;
2557 END IF;
2558 IF l_inv_tot <= l_actual_remittance_amount THEN
2559 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'No cash application rules required. Balance of contract is less than or equal to receipt amount');
2560 log_file('calling apply_rcpt_to_contract_no_rule ');
2561 apply_rcpt_to_contract_no_rule ( p_api_version => l_api_version
2562 ,p_init_msg_list => l_init_msg_list
2563 ,x_return_status => l_return_status
2564 ,x_msg_count => l_msg_count
2565 ,x_msg_data => l_msg_data
2566 ,p_contract_id => l_contract_id
2567 ,p_contract_num => l_contract_num
2568 ,p_customer_id => l_customer_id
2569 -- ,p_customer_num => l_customer_num
2570 ,p_customer_num => p_customer_num -- cklee 7036445
2571 ,p_receipt_id => l_receipt_id
2572 ,p_receipt_amount => l_remittance_amount
2573 ,p_remain_rcpt_amount => l_remain_rcpt_amount
2574 ,p_receipt_currency => l_receipt_currency
2575 ,p_receipt_date => l_receipt_date
2576 ,p_invoice_currency => l_invoice_currency_code
2577 ,p_currency_conv_date => l_currency_conv_date
2578 ,p_currency_conv_rate => l_currency_conv_rate
2579 ,p_currency_conv_type => l_currency_conv_type
2580 ,p_xcr_id => l_xcr_id
2581 ,p_cross_currency_allowed => l_cross_currency_allowed
2582 );
2583
2584
2585 ELSIF l_inv_tot > l_actual_remittance_amount THEN
2586
2587 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Cash application rule required. Balance of contract is greater than receipt amount');
2588 log_file('calling apply_rcpt_to_contract_w_rule');
2589 apply_rcpt_to_contract_w_rule ( p_api_version => l_api_version
2590 ,p_init_msg_list => l_init_msg_list
2591 ,x_return_status => l_return_status
2592 ,x_msg_count => l_msg_count
2593 ,x_msg_data => l_msg_data
2594 ,p_contract_id => l_contract_id
2595 ,p_contract_num => l_contract_num
2596 ,p_customer_id => l_customer_id
2597 -- ,p_customer_num => l_customer_num
2598 ,p_customer_num => p_customer_num -- cklee 7036445
2599 ,p_receipt_id => l_receipt_id
2600 ,p_receipt_amount => l_remittance_amount
2601 ,p_remain_rcpt_amount => l_remain_rcpt_amount
2602 ,p_receipt_currency => l_receipt_currency
2603 ,p_receipt_date => l_receipt_date
2604 ,p_invoice_currency => l_invoice_currency_code
2605 ,p_currency_conv_date => l_currency_conv_date
2606 ,p_currency_conv_rate => l_currency_conv_rate
2607 ,p_currency_conv_type => l_currency_conv_type
2608 ,p_xcr_id => l_xcr_id
2609 ,p_cross_currency_allowed => l_cross_currency_allowed
2610 );
2611
2612 END IF;
2613 log_file('l_return_status = '||l_return_status);
2614 x_return_status := l_return_status;
2615
2616 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2617 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2618 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2619 RAISE OKL_API.G_EXCEPTION_ERROR;
2620 END IF;
2621 log_file('l_remain_rcpt_amount = '||l_remain_rcpt_amount);
2622 IF l_remain_rcpt_amount <> l_actual_remittance_amount THEN -- application took place, move to next contract ...
2623
2624 l_stat_total_rcpt_amt := l_stat_total_rcpt_amt + (l_actual_remittance_amount - l_remain_rcpt_amount);
2625 l_stat_num_of_rcpts := l_stat_num_of_rcpts + 1;
2626 l_stat_num_of_cont := l_stat_num_of_cont + 1;
2627 END IF;
2628 l_inv_tot := 0;
2629 FOR c_open_invs_rec IN c_open_invs (l_contract_num, l_customer_num, NULL, c_get_cont_for_cust_rec.currency_code)
2630 LOOP
2631 l_invoice_currency_code := c_open_invs_rec.currency_code;
2632 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining;
2633 END LOOP;
2634 IF l_inv_tot = 0 THEN
2635 l_exit_loop := 1;
2636 END IF;
2637 EXIT WHEN l_exit_loop = 1;
2638 END IF; --check remittance amount
2639 END LOOP; -- receipt loop
2640 END IF; --check if inv_total>0
2641 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
2642
2643 END LOOP; -- contract loop
2644
2645 -- END IF;
2646
2647 END IF;
2648
2649 <<END_PROCESS>>
2650
2651 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
2652 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total number of receipts processed: '||l_stat_num_of_rcpts);
2653 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total of receipt amounts: '||l_stat_total_rcpt_amt);
2654 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Total number of contracts that received cash application: '||l_stat_num_of_cont);
2655 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
2656 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PROCESS COMPLETE: '||SYSDATE);
2657 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '====================================================================================');
2658
2659 ------------------------------------------------------------
2660 -- End processing
2661 ------------------------------------------------------------
2662
2663 Okl_Api.END_ACTIVITY (
2664 x_msg_count => x_msg_count,
2665 x_msg_data => x_msg_data);
2666
2667 log_file('reapplic_advanced_cash_app end');
2668 EXCEPTION
2669
2670 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2671 x_return_status := okl_api.G_RET_STS_ERROR;
2672
2673
2674 WHEN okl_api.G_EXCEPTION_ERROR THEN
2675 x_return_status := okl_api.HANDLE_EXCEPTIONS
2676 (
2677 l_api_name,
2678 G_PKG_NAME,
2679 OKL_API.G_RET_STS_ERROR,
2680 x_msg_count,
2681 x_msg_data,
2682 '_PVT'
2683 );
2684
2685 WHEN OTHERS THEN
2686 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
2687 Okl_api.set_message( p_app_name => g_app_name
2688 , p_msg_name => g_unexpected_error
2689 , p_token1 => g_sqlcode_token
2690 , p_token1_value => SQLCODE
2691 , p_token2 => g_sqlerrm_token
2692 , p_token2_value => SQLERRM
2693 ) ;
2694
2695 END reapplic_advanced_cash_app;
2696
2697 PROCEDURE reapplic_rcpt_w_cntrct ( p_api_version IN NUMBER
2698 ,p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE
2699 ,x_return_status OUT NOCOPY VARCHAR2
2700 ,x_msg_count OUT NOCOPY NUMBER
2701 ,x_msg_data OUT NOCOPY VARCHAR2
2702 ,p_contract_num IN VARCHAR2 DEFAULT NULL
2703 ,p_customer_num IN NUMBER DEFAULT NULL
2704 ,p_cross_currency_allowed IN VARCHAR2 DEFAULT 'N'
2705 )IS
2706
2707 l_api_version NUMBER := 1.0;
2708 l_init_msg_list VARCHAR2(1) := okl_api.g_false;
2709 l_return_status VARCHAR2(1);
2710 l_msg_count NUMBER;
2711 l_msg_data VARCHAR2(2000);
2712 l_api_name CONSTANT VARCHAR2(30) := 'reapplic_rcpt_w_cntrct';
2713
2714 l_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
2715 l_remain_rcpt_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
2716 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE;
2717 l_actual_remittance_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
2718 l_on_account_bal AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE;
2719 l_receipt_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE;
2720 l_receipt_count NUMBER;
2721
2722 l_dflt_days_to_hold_adv_pay OKL_CASH_ALLCTN_RLS.NUM_DAYS_HOLD_ADV_PAY%TYPE;
2723 l_days_to_hold_adv_pay OKL_CASH_ALLCTN_RLS.NUM_DAYS_HOLD_ADV_PAY%TYPE;
2724
2725 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
2726 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
2727 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE DEFAULT NULL;
2728 l_customer_acct_id NUMBER DEFAULT p_customer_num;
2729 l_cross_currency_allowed VARCHAR2(1) DEFAULT p_cross_currency_allowed;
2730
2731 l_temp_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
2732 l_temp_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
2733 l_temp_rcpt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT NULL;
2734
2735 l_xcr_id NUMBER;
2736 l_rct_id OKL_TRX_CSH_RECEIPT_V.ID%TYPE;
2737 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
2738 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
2739 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
2740
2741 l_stat_total_rcpt_amt OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT 0;
2742 l_stat_num_of_rcpts NUMBER DEFAULT 0;
2743 l_stat_num_of_cont NUMBER DEFAULT 0;
2744
2745
2746 -------------------------------------------------------------------------------
2747 -- DECLARE Record/Table Types
2748 -------------------------------------------------------------------------------
2749 -- Internal Trans
2750
2751 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
2752 l_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
2753
2754 l_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
2755 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
2756
2757 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
2758 x_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
2759
2760 x_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
2761 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
2762
2763 -- External Trans
2764
2765 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
2766 l_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
2767
2768 l_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
2769 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
2770
2771 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
2772 x_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
2773
2774 x_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
2775 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
2776
2777 t_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
2778
2779 -------------------------------------------------------------------------------
2780 -- DEFINE CURSORS
2781 -------------------------------------------------------------------------------
2782
2783 -- get all unapplied advanced receipts
2784 CURSOR c_get_all_adv_rcpt IS
2785 SELECT DISTINCT(a.cash_receipt_id) icr_id,
2786 a.id RCT_ID,
2787 c.receipt_date,
2788 b.khr_id
2789 FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b, AR_CASH_RECEIPTS_ALL c
2790 WHERE a.id = b.rct_id_details
2791 AND a.cash_receipt_id = c.cash_receipt_id
2792 AND a.FULLY_APPLIED_FLAG = 'N'
2793 AND a.receipt_type = 'ADV'
2794 AND a.expired_flag = 'N'
2795 AND b.khr_id IS NOT NULL
2796 ORDER BY c.receipt_date;
2797
2798 c_get_all_adv_rcpt_rec c_get_all_adv_rcpt%ROWTYPE;
2799
2800 -------------------------------------------------------------------------------
2801
2802 -- verify on account receipt amount
2803 CURSOR c_ver_on_acct_amt(cp_csh_rcpt_id IN NUMBER) IS
2804 SELECT (unapplied_amount + onaccount_amount) amount_available
2805 FROM okl_receipt_Details_uv
2806 WHERE cash_receipt_id = cp_csh_rcpt_id;
2807
2808
2809 -------------------------------------------------------------------------------
2810
2811 -- get cash applic rule id
2812 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
2813 SELECT to_number(a.object1_id1)
2814 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
2815 WHERE a.rgp_id = b.id
2816 AND b.rgd_code = 'LABILL'
2817 AND a.rule_information_category = 'LAINVD'
2818 AND a.dnz_chr_id = b.chr_id
2819 AND a.dnz_chr_id = cp_khr_id;
2820
2821 -------------------------------------------------------------------------------
2822
2823 -- get cash applic rule for contract
2824 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
2825 SELECT NUM_DAYS_HOLD_ADV_PAY
2826 FROM OKL_CASH_ALLCTN_RLS
2827 WHERE CAU_ID = cp_cau_id
2828 AND START_DATE <= trunc(SYSDATE)
2829 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
2830
2831 -------------------------------------------------------------------------------
2832
2833 -- get open contracts for customer
2834 CURSOR c_get_cont_for_cust(cp_customer_num IN VARCHAR2,
2835 cp_receipt_currency IN VARCHAR2) IS
2836 SELECT DISTINCT(lpt.contract_number),
2837 lpt.khr_id,
2838 lpt.start_date
2839 FROM okl_rcpt_cust_cont_balances_uv lpt
2840 WHERE lpt.customer_account_number = cp_customer_num
2841 AND lpt.status = 'OP'
2842 AND lpt.amount_due_remaining > 0
2843 AND lpt.currency_code = NVL (cp_receipt_currency,lpt.currency_code)
2844 AND lpt.org_id = mo_global.get_current_org_id
2845 ORDER BY lpt.start_date;
2846
2847 c_get_cont_for_cust_rec c_get_cont_for_cust%ROWTYPE;
2848
2849 -------------------------------------------------------------------------------
2850
2851 -- nikshah -- Bug # 5484903 Fixed,
2852 -- Changed CURSOR c_open_invs SQL definition
2853 -- get contract total
2854 CURSOR c_open_invs ( cp_contract_num IN VARCHAR2) IS
2855 SELECT lpt.customer_account_number
2856 FROM okl_rcpt_cust_cont_balances_uv lpt
2857 WHERE lpt.contract_number = cp_contract_num
2858 AND lpt.status = 'OP'
2859 AND lpt.amount_due_remaining > 0
2860 AND rownum < 2;
2861
2862 c_open_invs_rec c_open_invs%ROWTYPE;
2863
2864 -- get default cash applic rule for organization
2865 CURSOR c_dflt_cash_applic_rule IS
2866 SELECT NUM_DAYS_HOLD_ADV_PAY
2867 FROM OKL_CASH_ALLCTN_RLS
2868 WHERE default_rule = 'YES'
2869 AND TRUNC(end_date) IS NULL;
2870
2871 CURSOR c_get_cust_Acct_num(cp_acct_id in number) IS
2872 SELECT account_number
2873 from hz_cust_accounts
2874 where cust_Account_id = cp_acct_id;
2875
2876 -------------------------------------------------------------------------------
2877
2878 BEGIN
2879 log_file('reapplic_rcpt_w_cntrct');
2880 log_file('l_customer_num = '||l_customer_num);
2881 log_file('l_contract_num = '||l_contract_num);
2882 ------------------------------------------------------------
2883 -- Start processing
2884 ------------------------------------------------------------
2885
2886 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2887
2888 l_return_status := Okl_Api.START_ACTIVITY(
2889 p_api_name => l_api_name,
2890 p_pkg_name => G_PKG_NAME,
2891 p_init_msg_list => p_init_msg_list,
2892 l_api_version => l_api_version,
2893 p_api_version => p_api_version,
2894 p_api_type => '_PVT',
2895 x_return_status => l_return_status);
2896
2897 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2898 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2899 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2900 RAISE Okl_Api.G_EXCEPTION_ERROR;
2901 END IF;
2902
2903 IF l_customer_acct_id IS NULL AND
2904 l_contract_num IS NULL THEN
2905 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------');
2906 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'ERROR - No input parameters specified');
2907 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '-------------------------------------');
2908 GOTO END_PROCESS;
2909 END IF;
2910
2911 IF l_customer_acct_id IS NOT NULL THEN
2912 OPEN c_get_cust_Acct_num(l_customer_acct_id);
2913 FETCH c_get_cust_Acct_num INTO l_customer_num;
2914 CLOSE c_get_cust_Acct_num;
2915 END IF;
2916
2917 -- get default cash application rule
2918 OPEN c_dflt_cash_applic_rule;
2919 FETCH c_dflt_cash_applic_rule INTO l_dflt_days_to_hold_adv_pay;
2920 CLOSE c_dflt_cash_applic_rule;
2921
2922 log_file('l_dflt_days_to_hold_adv_pay = '||l_dflt_days_to_hold_adv_pay);
2923 ---------------------------------------------------------------
2924 -- First do a clean up ...
2925 -- Find all expired receipts and make them available to all ...
2926 -- BEGIN CLEAN UP PROCESS
2927 ---------------------------------------------------------------
2928
2929
2930 FOR c_get_all_adv_rcpt_rec IN c_get_all_adv_rcpt
2931 LOOP
2932
2933 l_temp_receipt_id := c_get_all_adv_rcpt_rec.icr_id;
2934 log_file('l_temp_receipt_id = '||l_temp_receipt_id);
2935 -- l_xcr_id := c_get_all_adv_rcpt_rec.xcr_id;
2936 l_rct_id := c_get_all_adv_rcpt_rec.rct_id;
2937 l_temp_contract_id := c_get_all_adv_rcpt_rec.khr_id;
2938 l_temp_rcpt_date := c_get_all_adv_rcpt_rec.receipt_date;
2939
2940 OPEN c_ver_on_acct_amt(l_temp_receipt_id);
2941 FETCH c_ver_on_acct_amt INTO l_actual_remittance_amount;
2942 CLOSE c_ver_on_acct_amt;
2943 log_file('l_actual_remittance_amount = '||l_actual_remittance_amount);
2944 IF l_actual_remittance_amount > 0 THEN
2945
2946 -- get cash application rule
2947 OPEN c_cash_rle_id_csr (l_temp_contract_id);
2948 FETCH c_cash_rle_id_csr INTO l_cau_id;
2949 CLOSE c_cash_rle_id_csr;
2950
2951 IF l_cau_id IS NOT NULL THEN
2952 OPEN c_cash_rule_csr (l_cau_id);
2953 FETCH c_cash_rule_csr INTO l_days_to_hold_adv_pay;
2954 CLOSE c_cash_rule_csr;
2955
2956 IF l_days_to_hold_adv_pay IS NULL THEN
2957 l_days_to_hold_adv_pay := l_dflt_days_to_hold_adv_pay;
2958 END IF;
2959 ELSE -- use default rule
2960 l_days_to_hold_adv_pay := l_dflt_days_to_hold_adv_pay;
2961 END IF;
2962
2963 IF TRUNC(l_temp_rcpt_date) + l_days_to_hold_adv_pay < TRUNC(SYSDATE) THEN
2964 l_rctv_rec.expired_flag := 'Y';
2965 ELSE
2966 l_rctv_rec.expired_flag := 'N';
2967 END IF;
2968
2969 END IF;
2970
2971 IF l_actual_remittance_amount = 0 OR l_actual_remittance_amount IS NULL THEN
2972 l_rctv_rec.fully_applied_flag := 'Y';
2973 l_rctv_rec.expired_flag := 'Y';
2974 ELSE
2975 l_rctv_rec.fully_applied_flag := 'N';
2976 END IF;
2977
2978 l_rctv_rec.id := l_rct_id;
2979 log_file('l_rctv_rec.id = '||l_rctv_rec.id);
2980 log_file('l_rctv_rec.fully_applied_flag = '||l_rctv_rec.fully_applied_flag);
2981 log_file('l_rctv_rec.expired_flag = '||l_rctv_rec.expired_flag);
2982 OKL_RCT_PVT.update_row( p_api_version => l_api_version
2983 ,p_init_msg_list => l_init_msg_list
2984 ,x_return_status => l_return_status
2985 ,x_msg_count => l_msg_count
2986 ,x_msg_data => l_msg_data
2987 ,p_rctv_rec => l_rctv_rec
2988 ,x_rctv_rec => x_rctv_rec);
2989 SELECT count(id) INTO l_rct_id
2990 FROM okl_trx_csh_rcpt_all_b
2991 WHERE receipt_type= 'ADV';
2992 log_file('after update adv count = '|| l_rct_id);
2993 x_return_status := l_return_status;
2994
2995 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2996 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2997 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2998 RAISE OKL_API.G_EXCEPTION_ERROR;
2999 END IF;
3000
3001 END LOOP;
3002
3003 SELECT count(id) INTO l_rct_id
3004 FROM okl_trx_csh_rcpt_all_b
3005 WHERE receipt_type= 'ADV';
3006 log_file('*** adv count = '|| l_rct_id);
3007 FOR c_get_all_adv_rcpt_rec IN c_get_all_adv_rcpt
3008 LOOP
3009 log_file('c_get_all_adv_rcpt_rec.icr_id = '||c_get_all_adv_rcpt_rec.icr_id);
3010 END LOOP;
3011 ---------------------------------------------------------------
3012 -- END CLEAN UP PROCESS
3013 ---------------------------------------------------------------
3014
3015 IF l_contract_num IS NOT NULL THEN
3016
3017 IF l_customer_num IS NULL THEN
3018
3019 OPEN c_open_invs (l_contract_num);
3020 FETCH c_open_invs INTO l_customer_num;
3021 CLOSE c_open_invs;
3022
3023 IF l_customer_num IS NULL THEN
3024 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'CONTRACT '||l_contract_num|| 'HAS NO OPEN INVOICES - EXITING PROCESS ... ');
3025 GOTO END_PROCESS;
3026 END IF;
3027
3028 END IF;
3029 log_file('calling advanced_cash_app');
3030 advanced_cash_app ( p_api_version => l_api_version
3031 ,p_init_msg_list => l_init_msg_list
3032 ,x_return_status => l_return_status
3033 ,x_msg_count => l_msg_count
3034 ,x_msg_data => l_msg_data
3035 ,p_contract_num => l_contract_num
3036 -- ,p_customer_num => l_customer_num
3037 ,p_customer_num => p_customer_num -- cklee 7036445
3038 ,p_receipt_num => NULL
3039 ,p_cross_currency_allowed => l_cross_currency_allowed
3040 );
3041
3042 x_return_status := l_return_status;
3043
3044 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3045 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3046 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3047 RAISE OKL_API.G_EXCEPTION_ERROR;
3048 END IF;
3049
3050 ELSE
3051
3052 FOR c_get_cont_for_cust_rec IN c_get_cont_for_cust (l_customer_num, NULL)
3053 LOOP
3054
3055 l_contract_num := c_get_cont_for_cust_rec.contract_number;
3056 l_contract_id := c_get_cont_for_cust_rec.khr_id;
3057 log_file('l_contract_id = '||l_contract_id);
3058 log_file('l_contract_num ='||l_contract_num);
3059 log_file('advanced_cash_app');
3060 advanced_cash_app ( p_api_version => l_api_version
3061 ,p_init_msg_list => l_init_msg_list
3062 ,x_return_status => l_return_status
3063 ,x_msg_count => l_msg_count
3064 ,x_msg_data => l_msg_data
3065 ,p_contract_num => l_contract_num
3066 -- ,p_customer_num => l_customer_num
3067 ,p_customer_num => p_customer_num -- cklee 7036445
3068 ,p_receipt_num => NULL
3069 ,p_cross_currency_allowed => l_cross_currency_allowed
3070 );
3071
3072 x_return_status := l_return_status;
3073
3074 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3075 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3076 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3077 RAISE OKL_API.G_EXCEPTION_ERROR;
3078 END IF;
3079
3080 END LOOP;
3081
3082 GOTO END_PROCESS;
3083
3084 END IF;
3085
3086 <<END_PROCESS>>
3087
3088 ------------------------------------------------------------
3089 -- End processing
3090 ------------------------------------------------------------
3091
3092 Okl_Api.END_ACTIVITY (
3093 x_msg_count => x_msg_count,
3094 x_msg_data => x_msg_data);
3095 log_file('end reapplic_rcpt_w_cntrct');
3096
3097 EXCEPTION
3098
3099 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3100 x_return_status := okl_api.G_RET_STS_ERROR;
3101
3102
3103 WHEN okl_api.G_EXCEPTION_ERROR THEN
3104 x_return_status := okl_api.HANDLE_EXCEPTIONS
3105 (
3106 l_api_name,
3107 G_PKG_NAME,
3108 OKL_API.G_RET_STS_ERROR,
3109 x_msg_count,
3110 x_msg_data,
3111 '_PVT'
3112 );
3113
3114 WHEN OTHERS THEN
3115 x_return_status := okl_api.G_RET_STS_UNEXP_ERROR;
3116 Okl_api.set_message( p_app_name => g_app_name
3117 , p_msg_name => g_unexpected_error
3118 , p_token1 => g_sqlcode_token
3119 , p_token1_value => SQLCODE
3120 , p_token2 => g_sqlerrm_token
3121 , p_token2_value => SQLERRM
3122 ) ;
3123
3124 END reapplic_rcpt_w_cntrct;
3125 /*
3126
3127 PROCEDURE REAPPLIC_RCPT_W_CNTRCT_CONC ( errbuf OUT NOCOPY VARCHAR2
3128 ,retcode OUT NOCOPY NUMBER
3129 ,p_contract_num IN VARCHAR2 DEFAULT NULL
3130 ,p_customer_acct_id IN NUMBER DEFAULT NULL
3131 ) IS
3132
3133 l_api_version NUMBER := 1;
3134 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3135 l_msg_count NUMBER;
3136 l_msg_data VARCHAR2(450);
3137 l_init_msg_list VARCHAR2(1) := 'T';
3138
3139 l_msg_index_out NUMBER :=0;
3140 l_error_msg_rec Okl_Accounting_Util.Error_message_Type;
3141
3142 l_contract_num VARCHAR2(250) := p_contract_num;
3143 l_customer_acct_id NUMBER := p_customer_acct_id;
3144
3145 l_request_id NUMBER;
3146 l_data varchar2(2000);
3147
3148
3149 CURSOR req_id_csr IS
3150 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
3151 FROM dual;
3152
3153 BEGIN
3154
3155 -- Get the request Id
3156 l_request_id := NULL;
3157 OPEN req_id_csr;
3158 FETCH req_id_csr INTO l_request_id;
3159 CLOSE req_id_csr;
3160
3161 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Process Advanced Monies');
3162 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '************************************');
3163 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Program Run Date: '||SYSDATE||' Request Id: '||l_request_id);
3164 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
3165 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PARAMETERS');
3166 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Contract Number = ' ||l_contract_num);
3167 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Customer Account Id = ' ||l_customer_acct_id);
3168 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '***********************************************');
3169 log_file('calling reapplic_rcpt_w_cntrct');
3170 reapplic_rcpt_w_cntrct ( p_api_version => l_api_version
3171 ,p_init_msg_list => l_init_msg_list
3172 ,x_return_status => l_return_status
3173 ,x_msg_count => l_msg_count
3174 ,x_msg_data => l_msg_data
3175 ,p_contract_num => l_contract_num
3176 ,p_customer_num => l_customer_acct_id
3177 );
3178 log_file('l_return_status= '||l_return_status);
3179 IF (l_return_status = okl_api.g_ret_sts_unexp_error) THEN
3180 fnd_file.put_line(fnd_file.log
3181 ,'Unexpected error in call to OKL_BPD_ADVANCED_CASH_APP_PUB.REAPPLIC_RCPT_W_CNTRCT');
3182 RAISE okl_api.g_exception_unexpected_error;
3183 ELSIF (l_return_status = okl_api.g_ret_sts_error) THEN
3184 fnd_file.put_line(fnd_file.log
3185 ,'Error in call to OKL_BPD_ADVANCED_CASH_APP_PUB.REAPPLIC_RCPT_W_CNTRCT');
3186 END IF;
3187
3188
3189
3190 BEGIN
3191
3192 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_error_msg_rec);
3193 IF (l_error_msg_rec.COUNT > 0) THEN
3194 FOR i IN l_error_msg_rec.FIRST..l_error_msg_rec.LAST
3195 LOOP
3196 FND_FILE.PUT_LINE(FND_FILE.LOG, l_error_msg_rec(i));
3197 FND_FILE.PUT_LINE (FND_FILE.LOG, '----------------------------------------------------------------------------');
3198 END LOOP;
3199 END IF;
3200
3201 EXCEPTION
3202 WHEN OTHERS THEN
3203 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
3204 END;
3205 retcode := 0;
3206 EXCEPTION
3207 WHEN okl_api.g_exception_error THEN
3208 retcode := 2;
3209 -- print the error message in the output file
3210
3211 IF (fnd_msg_pub.count_msg > 0) THEN
3212
3213 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
3214 fnd_msg_pub.get(p_msg_index => l_counter
3215 ,p_encoded => 'F'
3216 ,p_data => l_data
3217 ,p_msg_index_out => l_msg_index_out);
3218 fnd_file.put_line(fnd_file.log, l_data);
3219 END LOOP;
3220
3221 END IF;
3222
3223 WHEN okl_api.g_exception_unexpected_error THEN
3224 retcode := 2;
3225
3226 -- print the error message in the output file
3227
3228 IF (fnd_msg_pub.count_msg > 0) THEN
3229
3230 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
3231 fnd_msg_pub.get(p_msg_index => l_counter
3232 ,p_encoded => 'F'
3233 ,p_data => l_data
3234 ,p_msg_index_out => l_msg_index_out);
3235 fnd_file.put_line(fnd_file.log, l_data);
3236 END LOOP;
3237
3238 END IF;
3239
3240 WHEN OTHERS THEN
3241 retcode := 2;
3242 errbuf := sqlerrm;
3243
3244 -- print the error message in the output file
3245
3246 IF (fnd_msg_pub.count_msg > 0) THEN
3247
3248 FOR l_counter IN 1..fnd_msg_pub.count_msg LOOP
3249 fnd_msg_pub.get(p_msg_index => l_counter
3250 ,p_encoded => 'F'
3251 ,p_data => l_data
3252 ,p_msg_index_out => l_msg_index_out);
3253 fnd_file.put_line(fnd_file.log, l_data);
3254 END LOOP;
3255
3256 END IF;
3257 fnd_file.put_line(fnd_file.log, sqlerrm);
3258 END REAPPLIC_RCPT_W_CNTRCT_CONC;
3259 */
3260 ---------------------------------------------------------------------------
3261 -- PROCEDURE AR_advance_receipt
3262 -- This routine handles receivables interaction.
3263 ---------------------------------------------------------------------------
3264
3265 PROCEDURE AR_advance_receipt ( p_api_version IN NUMBER
3266 ,p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE
3267 ,x_return_status OUT NOCOPY VARCHAR2
3268 ,x_msg_count OUT NOCOPY NUMBER
3269 ,x_msg_data OUT NOCOPY VARCHAR2
3270 ,p_xcav_tbl IN xcav_tbl_type
3271 ,p_receipt_id IN AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL
3272 ,p_receipt_amount IN OUT NOCOPY AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE
3273 ,p_receipt_date IN AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT NULL
3274 ,p_receipt_currency IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
3275 ,p_currency_code IN AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL
3276 ,p_ar_inv_tbl IN OKL_BPD_ADVANCED_BILLING_PVT.ar_inv_tbl_type
3277 ) IS
3278
3279 ---------------------------
3280 -- DECLARE Local Variables
3281 ---------------------------
3282
3283 l_ar_inv_tbl OKL_BPD_ADVANCED_BILLING_PVT.ar_inv_tbl_type DEFAULT p_ar_inv_tbl;
3284
3285 l_api_version NUMBER := 1.0;
3286 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
3287 l_return_status VARCHAR2(1);
3288 l_msg_count NUMBER;
3289 l_msg_data VARCHAR2(2000);
3290 l_api_name CONSTANT VARCHAR2(30) := 'AR_advance_receipt';
3291
3292 l_receipt_date AR_CASH_RECEIPTS_ALL.RECEIPT_DATE%TYPE DEFAULT p_receipt_date;
3293 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_receipt_currency;
3294 l_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_receipt_amount;
3295
3296 l_customer_trx_id AR_PAYMENT_SCHEDULES_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
3297 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT p_receipt_id;
3298
3299 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_currency_code; -- entered currency code
3300
3301 l_applied_amount AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
3302 l_applied_amount_from AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED_FROM%TYPE DEFAULT NULL;
3303
3304 l_prev_applied_amt AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE DEFAULT NULL;
3305
3306 l_receivable_application_id AR_RECEIVABLE_APPLICATIONS_ALL.RECEIVABLE_APPLICATION_ID%TYPE DEFAULT NULL;
3307
3308 l_invoice_balance AR_PAYMENT_SCHEDULES_ALL.AMOUNT_DUE_REMAINING%TYPE DEFAULT NULL;
3309
3310 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
3311 l_apply_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
3312
3313 l_ar_inv_id RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL;
3314 l_ar_inv_date RA_CUSTOMER_TRX_ALL.TRX_DATE%TYPE DEFAULT NULL;
3315
3316 i NUMBER DEFAULT NULL;
3317 l_exit_loop NUMBER DEFAULT 0;
3318
3319 l_counter NUMBER;
3320 l_unapply VARCHAR2(3);
3321
3322 l_record_count NUMBER DEFAULT NULL;
3323
3324 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
3325
3326 -- abindal start bug#4897580 --
3327 l_inv_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
3328 l_rec_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
3329 ar_invoice_num RA_CUSTOMER_TRX_ALL.TRX_NUMBER%TYPE DEFAULT NULL;
3330 ar_invoice_date OKL_BPD_LEASING_PAYMENT_TRX_V.INVOICE_DATE%TYPE DEFAULT NULL;
3331 -- abindal end bug#4897580 --
3332
3333 -------------------------------------------------------------------------------
3334 -- DECLARE Record/Table Types
3335 -------------------------------------------------------------------------------
3336
3337 -- External Trans
3338
3339 l_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
3340 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
3341
3342 x_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
3343 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
3344
3345 -------------------------------------------------------------------------------
3346 -- DEFINE CURSORS
3347 -------------------------------------------------------------------------------
3348
3349 -- abindal start bug#4897580 --
3350
3351 --get gl date from open accounting period
3352 CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
3353 SELECT trunc(cp_date) gl_date, 1 Counter
3354 FROM gl_period_statuses
3355 WHERE application_id = 222
3356 -- BEGIN abindal bug 4356410 --
3357 AND closing_status IN ('F','O')
3358 -- END abindal bug 4356410 --
3359 AND ledger_id = okl_accounting_util.get_set_of_books_id
3360 AND trunc(cp_date) between start_date and end_date
3361 AND adjustment_period_flag = 'N'
3362 UNION
3363 SELECT MAX(end_date) gl_date, 2 Counter
3364 FROM gl_period_statuses
3365 WHERE application_id = 222
3366 AND ledger_id = okl_accounting_util.get_set_of_books_id
3367 AND closing_status IN ('F','O')
3368 AND end_date <= trunc(cp_date)
3369 AND adjustment_period_flag = 'N'
3370 UNION
3371 SELECT MIN(start_date) gl_date, 3 Counter
3372 FROM gl_period_statuses
3373 WHERE application_id = 222
3374 AND ledger_id = okl_accounting_util.get_set_of_books_id
3375 AND closing_status IN ('F','O')
3376 AND start_date >= trunc(cp_date)
3377 AND adjustment_period_flag = 'N'
3378 )
3379 where gl_date is not null
3380 order by counter;
3381
3382 -- abindal end bug#4897580 --
3383
3384 -------------------------------------------------------------------------------
3385
3386 -- verify on account receipt amount
3387 CURSOR c_ver_on_acct_amt(cp_csh_rcpt_id IN NUMBER) IS
3388 SELECT onaccount_amount amount_available
3389 FROM okl_receipt_Details_uv
3390 WHERE cash_receipt_id = cp_csh_rcpt_id;
3391
3392 -------------------------------------------------------------------------------
3393
3394 -- nikshah -- Bug # 5484903 Fixed,
3395 -- Changed c_ver_inv_amt(cp_receivables_invoice_id IN NUMBER) SQL definition
3396 -- verify receivables invoice amount
3397 CURSOR c_ver_inv_amt(cp_receivables_invoice_id IN NUMBER) IS
3398 SELECT amount_due_remaining
3399 FROM AR_PAYMENT_SCHEDULES_ALL
3400 WHERE customer_trx_id = cp_receivables_invoice_id;
3401
3402 -------------------------------------------------------------------------------
3403
3404 -- check receipt applic
3405 CURSOR c_ver_dup_applic( cp_customer_trx_id IN NUMBER
3406 ,cp_cash_receipt_id IN NUMBER) IS
3407 SELECT amount_applied, receivable_application_id
3408 FROM AR_RECEIVABLE_APPLICATIONS_ALL
3409 WHERE applied_customer_trx_id = cp_customer_trx_id
3410 AND cash_receipt_id = cp_cash_receipt_id
3411 AND status = 'APP'
3412 ORDER BY creation_date desc;
3413
3414 -------------------------------------------------------------------------------
3415
3416 -- get the ar invoice date
3417 CURSOR c_get_inv_date(cp_inv_id IN NUMBER) IS
3418 SELECT trx_date,org_id
3419 FROM RA_CUSTOMER_TRX_ALL
3420 WHERE customer_trx_id = cp_inv_id;
3421
3422 -------------------------------------------------------------------------------
3423 CURSOR c_get_inv_lines(cp_inv_id IN NUMBER) IS
3424 SELECT customer_trx_line_id invoice_line_id, amount_due_remaining
3425 FROM ra_customer_trx_lines_All
3426 WHERE customer_trx_id = cp_inv_id
3427 AND line_type ='LINE'
3428 AND nvl(amount_due_remaining,0) > 0;
3429 l_ar_llca_tbl ar_receipt_api_pub.llca_trx_lines_tbl_type;
3430
3431 BEGIN
3432 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3433
3434 l_return_status := Okl_Api.START_ACTIVITY(
3435 p_api_name => l_api_name,
3436 p_pkg_name => G_PKG_NAME,
3437 p_init_msg_list => p_init_msg_list,
3438 l_api_version => l_api_version,
3439 p_api_version => p_api_version,
3440 p_api_type => '_PVT',
3441 x_return_status => l_return_status);
3442
3443 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
3444 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
3445 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
3446 RAISE Okl_Api.G_EXCEPTION_ERROR;
3447 END IF;
3448 l_xcav_tbl := p_xcav_tbl;
3449
3450 l_record_count := l_xcav_tbl.COUNT;
3451
3452 -- abindal start bug#4897580 --
3453 OPEN c_get_gl_date(l_receipt_date);
3454 FETCH c_get_gl_date INTO l_rec_gl_date, l_counter;
3455 -- abindal end bug#4897580 --
3456
3457 IF c_get_gl_date%NOTFOUND THEN
3458 CLOSE c_get_gl_date;
3459
3460 OKC_API.set_message( p_app_name => G_APP_NAME,
3461 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
3462 p_token1 => 'TRX_DATE',
3463 p_token1_value => TRUNC(SYSDATE));
3464
3465 l_return_status := OKC_API.G_RET_STS_ERROR;
3466 RAISE G_EXCEPTION_HALT_VALIDATION;
3467 END IF;
3468 CLOSE c_get_gl_date;
3469
3470 IF l_ar_inv_tbl.COUNT <> 0 THEN
3471
3472 -- unapply receipt from account
3473 -- apply to receipt one by one until it runs out
3474 -- place remaining receipt amount back on account
3475 -- end;
3476
3477 OPEN c_ver_on_acct_amt(l_cash_receipt_id);
3478 FETCH c_ver_on_acct_amt INTO l_receipt_amount;
3479 CLOSE c_ver_on_acct_amt;
3480
3481 IF l_receipt_amount > 0 THEN
3482
3483 Ar_receipt_api_pub.unapply_on_account(p_api_version => l_api_version
3484 ,p_init_msg_list => l_init_msg_list
3485 -- ,p_commit => l_commit
3486 -- ,p_validation_level => l_validation_level
3487 ,x_return_status => l_return_status
3488 ,x_msg_count => l_msg_count
3489 ,x_msg_data => l_msg_data
3490 ,p_cash_receipt_id => l_cash_receipt_id
3491 -- abindal start bug#4897580 --
3492 ,p_reversal_gl_date => null
3493 --,p_reversal_gl_date => l_gl_date
3494 -- abindal end bug#4897580 --
3495 );
3496
3497
3498 x_return_status := l_return_status;
3499
3500 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3501 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3502 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3503 RAISE OKL_API.G_EXCEPTION_ERROR;
3504 END IF;
3505
3506 -- abindal start bug#4897580 --
3507 IF l_receipt_date <= SYSDATE THEN
3508 l_apply_date := TRUNC(SYSDATE);
3509 END IF;
3510
3511 IF l_receipt_date > SYSDATE THEN
3512 l_apply_date := l_receipt_date;
3513 END IF;
3514 -- abindal end bug#4897580 --
3515
3516
3517 i := l_ar_inv_tbl.FIRST;
3518
3519 LOOP
3520
3521 l_customer_trx_id := l_ar_inv_tbl(i).receivables_invoice_id;
3522 -- debug_proc('l_customer_trx_id = '||l_customer_trx_id);
3523 -- varao 14-Feb-2006 bug 5032427 start
3524 OPEN c_get_inv_date(l_customer_trx_id);
3525 FETCH c_get_inv_date INTO l_ar_inv_date,l_org_id;
3526 EXIT WHEN c_get_inv_date%NOTFOUND;
3527 CLOSE c_get_inv_date;
3528
3529 IF l_ar_inv_date > l_receipt_date THEN
3530 IF l_ar_inv_date > SYSDATE THEN
3531 l_apply_date := l_ar_inv_date;
3532 END IF;
3533 END IF;
3534
3535 OPEN c_get_gl_date(l_apply_date);
3536 FETCH c_get_gl_date INTO l_inv_gl_date, l_counter;
3537
3538 IF c_get_gl_date%NOTFOUND THEN
3539 CLOSE c_get_gl_date;
3540 OKC_API.set_message( p_app_name => G_APP_NAME,
3541 p_msg_name =>'OKL_BPD_GL_PERIOD_ERROR',
3542 p_token1 => 'TRX_DATE',
3543 p_token1_value => l_apply_date);
3544
3545 l_return_status := OKC_API.G_RET_STS_ERROR;
3546 RAISE G_EXCEPTION_HALT_VALIDATION;
3547 END IF;
3548 CLOSE c_get_gl_date;
3549 -- varao 14-Feb-2006 bug 5032427 end
3550 i := 0;
3551 l_ar_llca_tbl.DELETE;
3552
3553 FOR line_rec IN c_get_inv_lines(l_customer_trx_id) LOOP
3554 i := i +1;
3555 l_ar_llca_tbl(i).customer_trx_line_id := line_rec.invoice_line_id;
3556 IF line_rec.amount_due_remaining > l_receipt_amount THEN
3557 l_ar_llca_tbl(i).amount_applied := l_receipt_amount;
3558 l_receipt_amount := 0;
3559 ELSE
3560 l_ar_llca_tbl(i).amount_applied := line_rec.amount_due_remaining;
3561 l_receipt_amount := l_receipt_amount - line_rec.amount_due_remaining;
3562 END IF;
3563 EXIT WHEN l_receipt_amount = 0;
3564 END LOOP;
3565 --FOR ll in l_ar_llca_tbl.FIRST..l_ar_llca_tbl.LAST LOOP
3566 -- debug_proc('l_ar_llca_tbl(i).customer_trx_line_id = '||l_ar_llca_tbl(i).customer_trx_line_id);
3567 -- debug_proc('l_ar_llca_tbl(i).amount_applied = '||l_ar_llca_tbl(i).amount_applied);
3568 -- END LOOP;
3569 --debug_proc('l_apply_date =' ||l_apply_date);
3570 --debug_proc('l_inv_gl_date =' ||l_inv_gl_date);
3571 --debug_proc('l_org_id =' ||l_org_id);
3572 --debug_proc('l_cash_receipt_id =' ||l_cash_receipt_id);
3573 --debug_proc('l_customer_trx_id =' ||l_customer_trx_id);
3574 IF l_ar_llca_tbl.COUNT > 0 THEN
3575 AR_RECEIPT_API_PUB.apply_in_detail( p_api_version => l_api_version
3576 ,p_init_msg_list => l_init_msg_list
3577 ,x_return_status => l_return_status
3578 ,x_msg_count => l_msg_count
3579 ,x_msg_data => l_msg_data
3580 ,p_cash_receipt_id => l_cash_receipt_id
3581 ,p_customer_trx_id => l_customer_trx_id
3582 ,p_llca_type => 'L'
3583 ,p_llca_trx_lines_tbl => l_ar_llca_tbl
3584 ,p_apply_date => l_apply_date
3585 ,p_apply_gl_date => l_inv_gl_date
3586 ,p_org_id => l_org_id
3587 );
3588 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3589 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3590 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3591 RAISE OKL_API.G_EXCEPTION_ERROR;
3592 END IF;
3593 END IF;
3594
3595 EXIT WHEN i = l_ar_inv_tbl.LAST OR l_receipt_amount = 0;
3596 i := l_ar_inv_tbl.NEXT(i);
3597
3598 END LOOP;
3599
3600 END IF;
3601
3602 END IF;
3603
3604 IF l_receipt_amount > 0 THEN
3605
3606 Ar_receipt_api_pub.Apply_on_account( p_api_version => l_api_version
3607 ,p_init_msg_list => l_init_msg_list
3608 ,x_return_status => l_return_status
3609 ,x_msg_count => l_msg_count
3610 ,x_msg_data => l_msg_data
3611 ,p_cash_receipt_id => l_cash_receipt_id
3612 -- abindal start bug#4897580 --
3613 --,p_apply_date => TRUNC(SYSDATE) -- l_receipt_date
3614 --,p_apply_gl_date => l_gl_date
3615 ,p_apply_date => l_receipt_date
3616 ,p_apply_gl_date => l_rec_gl_date
3617 -- abindal end bug#4897580 --
3618 );
3619
3620 x_return_status := l_return_status;
3621
3622 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3623 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3624 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
3625 RAISE OKL_API.G_EXCEPTION_ERROR;
3626 END IF;
3627
3628 p_receipt_amount := l_receipt_amount;
3629
3630 END IF;
3631
3632 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3633
3634 EXCEPTION
3635
3636 WHEN G_EXCEPTION_HALT_VALIDATION THEN
3637 x_return_status := OKC_API.G_RET_STS_ERROR;
3638
3639
3640 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3641 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3642 (
3643 l_api_name,
3644 G_PKG_NAME,
3645 OKL_API.G_RET_STS_ERROR,
3646 x_msg_count,
3647 x_msg_data,
3648 '_PVT'
3649 );
3650 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3651 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3652 (
3653 l_api_name,
3654 G_PKG_NAME,
3655 OKL_API.G_RET_STS_UNEXP_ERROR,
3656 x_msg_count,
3657 x_msg_data,
3658 '_PVT'
3659 );
3660
3661 WHEN OTHERS THEN
3662 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3663 Okl_api.set_message( p_app_name => g_app_name
3664 , p_msg_name => g_unexpected_error
3665 , p_token1 => g_sqlcode_token
3666 , p_token1_value => SQLCODE
3667 , p_token2 => g_sqlerrm_token
3668 , p_token2_value => SQLERRM
3669 ) ;
3670
3671 END AR_advance_receipt;
3672
3673
3674 END OKL_BPD_ADVANCED_CASH_APP_PVT;