[Home] [Help]
PACKAGE BODY: APPS.OKL_UPDT_CASH_DTLS
Source
1 PACKAGE BODY okl_updt_cash_dtls AS
2 /* $Header: OKLRCUPB.pls 120.5 2007/08/02 07:11:24 dcshanmu noship $ */
3
4 ---------------------------------------------------------------------------
5 -- PROCEDURE update_cash_details
6 ---------------------------------------------------------------------------
7
8 PROCEDURE update_cash_details ( p_api_version IN NUMBER
9 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
10 ,x_return_status OUT NOCOPY VARCHAR2
11 ,x_msg_count OUT NOCOPY NUMBER
12 ,x_msg_data OUT NOCOPY VARCHAR2
13 ,p_strm_tbl IN okl_cash_dtls_tbl_type
14 ,x_strm_tbl OUT NOCOPY okl_cash_dtls_tbl_type
15 ) IS
16
17 ---------------------------
18 -- DECLARE Local Variables
19 ---------------------------
20
21 l_strm_tbl okl_cash_dtls_tbl_type;
22
23 l_lsm_id OKL_TXL_RCPT_APPS_B.LSM_ID%TYPE;
24 l_rca_id OKL_TXL_RCPT_APPS_B.ID%TYPE DEFAULT NULL;
25 l_rct_id_details OKL_TXL_RCPT_APPS_B.RCT_ID_DETAILS%TYPE DEFAULT NULL;
26 l_xcr_id_details NUMBER DEFAULT NULL;
27 l_cnr_id OKL_TXL_RCPT_APPS_B.CNR_ID%TYPE DEFAULT NULL; -- consolidated bill id
28 l_khr_id OKL_TXL_RCPT_APPS_B.KHR_ID%TYPE DEFAULT NULL; -- contract id
29
30
31 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
32
33 l_conversion_rate AR_CASH_RECEIPTS_ALL.EXCHANGE_RATE%TYPE DEFAULT NULL;
34
35 l_rcpt_amount OKL_TRX_CSH_RECEIPT_B.AMOUNT%TYPE DEFAULT NULL;
36 l_rcpt_currency_code OKL_TRX_CSH_RECEIPT_B.CURRENCY_CODE%TYPE DEFAULT NULL;
37 l_func_rcpt_amount AR_CASH_RECEIPTS_ALL.AMOUNT%TYPE DEFAULT NULL;
38 l_func_currency_code AR_CASH_RECEIPTS_ALL.CURRENCY_CODE%TYPE DEFAULT NULL;
39 l_total_amount_applied AR_RECEIVABLE_APPLICATIONS_ALL.AMOUNT_APPLIED%TYPE;
40
41
42 l_over_pay VARCHAR(1) DEFAULT NULL;
43 l_conc_proc VARCHAR(2) DEFAULT 'NN';
44 l_over_payment_code OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE;
45
46 i NUMBER DEFAULT NULL;
47 j NUMBER DEFAULT NULL;
48
49 l_api_version NUMBER := 1.0;
50 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
51 l_return_status VARCHAR2(1);
52 l_msg_count NUMBER;
53 l_msg_data VARCHAR2(2000);
54
55 l_api_name CONSTANT VARCHAR2(30) := 'update_cash_details';
56
57 ------------------------------
58 -- DECLARE Record/Table Types
59 ------------------------------
60
61 -- Internal Trans
62
63 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
64 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
65
66 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
67 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
68
69 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
70 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
71
72 -------------------
73 -- DECLARE Cursors
74 -------------------
75
76 CURSOR c_ovr_pay_alloc_code IS
77 SELECT OVER_PAYMENT_ALLOCATION_CODE
78 FROM OKL_CASH_ALLCTN_RLS;
79
80
81 ----------
82
83 -- external line info
84 CURSOR c_get_rca_id (cp_xca_id IN NUMBER) IS
85 SELECT rca_id, xcr_id_details
86 FROM okl_xtl_csh_apps_v
87 WHERE id = cp_xca_id;
88
89 ----------
90
91 -- internal line info
92 CURSOR c_get_strm_dtls (cp_rca_id IN NUMBER) IS
93 SELECT rct_id_details, cnr_id, khr_id
94 FROM okl_txl_rcpt_apps_v
95 WHERE id = cp_rca_id;
96
97 ----------
98
99 -- internal line info
100 CURSOR c_get_internal (cp_rct_id_details IN NUMBER) IS
101 SELECT id, lsm_id
102 FROM okl_txl_rcpt_apps_v
103 WHERE rct_id_details = cp_rct_id_details;
104
105 c_get_internal_rec c_get_internal%ROWTYPE;
106
107 ----------
108
109 -- external hdr/ln info
110 CURSOR c_get_external_hdr (cp_xcr_id_details IN NUMBER) IS
111 SELECT a.id, a.rct_id, a.remittance_amount, a.check_number, a.receipt_date,
112 a.gl_date, a.customer_number, a.currency_code, a.org_id, a.exchange_rate_type,
113 a.exchange_rate_date, a.attribute1
114 FROM okl_ext_csh_rcpts_b a, okl_xtl_csh_apps_b b
115 WHERE a.id = b.xcr_id_details
116 AND b.xcr_id_details = cp_xcr_id_details;
117
118 ----------
119
120 -- currency conversion rate
121 CURSOR c_get_conv_rate (cp_xcr_id IN NUMBER) IS
122 SELECT a.exchange_rate
123 FROM okl_ext_csh_rcpts_b a
124 WHERE a.id = cp_xcr_id;
125
126 ----------
127
128 -- get receipt info
129 CURSOR c_get_rcpt_info (cp_rct_id IN NUMBER) IS
130 SELECT a.currency_code, a.amount, -- rcpt currency
131 b.currency_code, b.remittance_amount -- functional currency
132 FROM okl_trx_csh_receipt_b a,
133 okl_ext_csh_rcpts_b b
134 WHERE a.id = cp_rct_id
135 AND a.id = b.rct_id;
136
137 ----------
138
139
140 BEGIN
141
142 l_strm_tbl := p_strm_tbl;
143
144 IF l_strm_tbl.COUNT = 0 THEN
145
146 -- Message Text: no allocation required ...
147 x_return_status := OKC_API.G_RET_STS_ERROR;
148 OKC_API.set_message( p_app_name => G_APP_NAME,
149 p_msg_name =>'OKL_BPD_NO_ALLOC_REQ');
150
151 RAISE G_EXCEPTION_HALT_VALIDATION;
152
153 END IF;
154
155 IF l_strm_tbl(1).asset_id = 1 THEN
156
157
158 OPEN c_get_rca_id(l_strm_tbl(1).xtl_cash_apps_id);
159 FETCH c_get_rca_id INTO l_rca_id, l_xcr_id_details;
160 CLOSE c_get_rca_id;
161
162 -- Check for exceptions
163 IF l_rca_id = NULL THEN
164
165 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
166 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
167
168 END IF;
169
170 OPEN c_get_strm_dtls(l_rca_id);
171 FETCH c_get_strm_dtls INTO l_rct_id_details, l_cnr_id, l_khr_id;
172 CLOSE c_get_strm_dtls;
173
174 -- clear up internal receipt
175 DELETE FROM OKL_TRX_CSH_RECEIPT_B
176 WHERE ID = l_rct_id_details;
177
178 -- clear up internal receipt lines
179 DELETE FROM OKL_TXL_RCPT_APPS_B
180 WHERE RCT_ID_DETAILS = l_rct_id_details;
181
182 -- clear up external receipt
183 DELETE FROM OKL_EXT_CSH_RCPTS_B
184 WHERE ID = l_xcr_id_details;
185
186 -- clear up external receipt lines
187 DELETE FROM OKL_XTL_CSH_APPS_B
188 WHERE XCR_ID_DETAILS = l_xcr_id_details;
189
190 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
191
192 ELSE
193
194 -- get internal lines id to obtain fixed values l_rct_id_details, cnr_id and khr_id.
195 -- only one record is required.
196
197 i := 1;
198
199 OPEN c_get_rca_id(l_strm_tbl(1).xtl_cash_apps_id);
200 FETCH c_get_rca_id INTO l_rca_id, l_xcr_id_details;
201 CLOSE c_get_rca_id;
202
203 -- Check for exceptions
204 IF l_rca_id = NULL THEN
205
206 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
207 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
208
209 END IF;
210
211 OPEN c_get_strm_dtls(l_rca_id);
212 FETCH c_get_strm_dtls INTO l_rct_id_details, l_cnr_id, l_khr_id;
213 CLOSE c_get_strm_dtls;
214
215 -- Check for exceptions
216 IF l_rct_id_details = NULL THEN
217 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
218 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
219 END IF;
220
221 OPEN c_get_rcpt_info(l_rct_id_details);
222 FETCH c_get_rcpt_info INTO l_rcpt_currency_code -- receipt currency
223 ,l_rcpt_amount -- receipt amount
224 ,l_func_currency_code -- invoice currency code ( functional )
225 ,l_func_rcpt_amount; -- invoice amount
226 CLOSE c_get_rcpt_info;
227
228 -- Check for exceptions
229 IF l_rcpt_currency_code = NULL THEN
230 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
231 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
232 END IF;
233
234
235 -- check applied amount < = the receipt amount ...
236 j := 1;
237 l_total_amount_applied := 0;
238
239 LOOP
240 l_total_amount_applied := l_total_amount_applied + l_strm_tbl(j).applied_stream_amount; -- working in functional currency.
241 EXIT WHEN j = (l_strm_tbl.LAST);
242 j := j + 1;
243 END LOOP;
244
245 IF l_func_rcpt_amount < l_total_amount_applied THEN -- in functional currency ...
246 -- Message Text: the amount applied must be equal or less than receipt amount
247 x_return_status := OKC_API.G_RET_STS_ERROR;
248 OKC_API.set_message( p_app_name => G_APP_NAME,
249 p_msg_name =>'OKL_BPD_RCPT_ALLOC_ERR');
250
251 RAISE G_EXCEPTION_HALT_VALIDATION;
252 END IF;
253
254 -- old internal records no longer required.
255
256 i := 1;
257
258 OPEN c_get_internal(l_rct_id_details);
259 LOOP
260 FETCH c_get_internal INTO c_get_internal_rec;
261 EXIT WHEN c_get_internal%NOTFOUND;
262 l_rcav_tbl(i).ID := c_get_internal_rec.id;
263 i := i + 1;
264 END LOOP;
265 CLOSE c_get_internal;
266
267 -- call delete internal
268
269 Okl_Txl_Rcpt_Apps_Pub.delete_txl_rcpt_apps( l_api_version
270 ,l_init_msg_list
271 ,l_return_status
272 ,l_msg_count
273 ,l_msg_data
274 ,l_rcav_tbl
275 );
276
277 x_return_status := l_return_status;
278 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
279 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
280 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
281 RAISE OKL_API.G_EXCEPTION_ERROR;
282 END IF;
283
284 -- prepare new internal transaction records
285
286 i := 1;
287 j := 1;
288
289 -- get conversion rate if there is one.
290 OPEN c_get_conv_rate(l_xcr_id_details);
291 FETCH c_get_conv_rate INTO l_conversion_rate;
292 CLOSE c_get_conv_rate;
293
294 LOOP
295 l_rcav_tbl(i).rct_id_details := l_rct_id_details;
296 l_rcav_tbl(i).cnr_id := l_cnr_id;
297 l_rcav_tbl(i).khr_id := l_khr_id;
298 l_rcav_tbl(i).lsm_id := l_strm_tbl(j).lsm_id;
299
300 IF l_rcpt_currency_code <> l_func_currency_code THEN
301 l_rcav_tbl(i).amount := l_strm_tbl(j).applied_stream_amount / l_conversion_rate; -- in receipt currency
302 ELSE
303 l_rcav_tbl(i).amount := l_strm_tbl(j).applied_stream_amount; -- in functional currency.
304 END IF;
305
306 l_rcav_tbl(i).line_number := i;
307 EXIT WHEN (j = l_strm_tbl.LAST);
308 i := i + 1;
309 j := j + 1;
310 END LOOP;
311
312 Okl_Txl_Rcpt_Apps_Pub.insert_txl_rcpt_apps( l_api_version
313 ,l_init_msg_list
314 ,l_return_status
315 ,l_msg_count
316 ,l_msg_data
317 ,l_rcav_tbl
318 ,x_rcav_tbl
319 );
320
321 x_return_status := l_return_status;
322 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
323 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
324 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
325 RAISE OKL_API.G_EXCEPTION_ERROR;
326 END IF;
327
328 -- update external transaction records
329
330 i := 1;
331 j := 1;
332
333 OPEN c_get_internal(l_rct_id_details);
334 LOOP
335 FETCH c_get_internal INTO l_rca_id, l_lsm_id;
336
337 EXIT WHEN c_get_internal%NOTFOUND;
338 LOOP
339 IF l_strm_tbl(j).lsm_id = l_lsm_id THEN
340
341 l_xcav_tbl(i).ID := l_strm_tbl(j).xtl_cash_apps_id;
342 l_xcav_tbl(i).RCA_ID := l_rca_id;
343
344 IF l_strm_tbl(j).applied_stream_amount NOT IN (0,0.00) THEN
345
346 l_xcav_tbl(i).AMOUNT_APPLIED := l_strm_tbl(j).applied_stream_amount;
347 ELSE
348 l_xcav_tbl(i).AMOUNT_APPLIED := 0;
349 END IF;
350
351 IF l_rcpt_currency_code <> l_func_currency_code THEN
352 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_strm_tbl(j).applied_stream_amount / l_conversion_rate;
353 END IF;
354
355 l_xcav_tbl(i).LSM_ID := l_strm_tbl(j).lsm_id;
356
357 j := 1;
358 EXIT;
359
360 ELSE
361
362 j := j + 1;
363
364 END IF;
365
366 END LOOP;
367
368 i := i + 1;
369
370 END LOOP;
371 CLOSE c_get_internal;
372
373 Okl_Xtl_Csh_Apps_Pub.update_xtl_csh_apps( l_api_version
374 ,l_init_msg_list
375 ,l_return_status
376 ,l_msg_count
377 ,l_msg_data
378 ,l_xcav_tbl
379 ,x_xcav_tbl
380 );
381
382 x_return_status := l_return_status;
383 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
384 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
385 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
386 RAISE OKL_API.G_EXCEPTION_ERROR;
387 END IF;
388
389 -- prepare to call receipt_api.
390
391 OPEN c_get_external_hdr(l_xcr_id_details);
392 LOOP
393 EXIT WHEN c_get_external_hdr%NOTFOUND;
394
395 FETCH c_get_external_hdr INTO l_xcrv_rec.id
396 ,l_xcrv_rec.rct_id
397 ,l_xcrv_rec.remittance_amount
398 ,l_xcrv_rec.check_number
399 ,l_xcrv_rec.receipt_date
400 ,l_xcrv_rec.gl_date
401 ,l_xcrv_rec.customer_number
402 ,l_xcrv_rec.currency_code
403 ,l_xcrv_rec.org_id
404 ,l_xcrv_rec.exchange_rate_type
405 ,l_xcrv_rec.exchange_rate_date
406 ,l_xcrv_rec.attribute1;
407 END LOOP;
408 CLOSE c_get_external_hdr;
409
410 OPEN c_ovr_pay_alloc_code;
411 FETCH c_ovr_pay_alloc_code INTO l_over_payment_code;
412 CLOSE c_ovr_pay_alloc_code;
413
414 IF l_over_payment_code IN ('M','m') THEN
415
416 l_over_pay := 'U'; -- UNAPPLIED;
417 -- just create money against customer and thats it...
418
419 ELSIF l_over_payment_code IN ('B','b') THEN
420
421 l_over_pay := 'O'; -- CUSTOMERS ACCOUNT
422 -- apply money to customers account...
423
424 ELSIF l_over_payment_code IN ('F','f') THEN
425
426 -- KICK OFF PROCESS FOR FUTURE AMOUNTS DUE
427 l_over_pay := 'O'; -- CUSTOMERS ACCOUNT
428
429 END IF;
430
431 okl_cash_receipt.CASH_RECEIPT (p_api_version => l_api_version
432 ,p_init_msg_list => l_init_msg_list
433 ,x_return_status => l_return_status
434 ,x_msg_count => l_msg_count
435 ,x_msg_data => l_msg_data
436 ,p_over_pay => l_over_pay
437 ,p_conc_proc => l_conc_proc
438 ,p_xcrv_rec => l_xcrv_rec
439 ,p_xcav_tbl => l_xcav_tbl
440 ,x_cash_receipt_id => l_cash_receipt_id
441 );
442
443 x_return_status := l_return_status;
444 x_msg_data := l_msg_data;
445 x_msg_count := l_msg_count;
446
447 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
448 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
449 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
450 RAISE OKL_API.G_EXCEPTION_ERROR;
451 END IF;
452
453 -- UPDATE EXT HEADER WITH CASH RECEIPT ID
454
455 l_xcrv_rec.icr_id := l_cash_receipt_id;
456
457 Okl_Xcr_Pub.update_ext_csh_txns( p_api_version
458 ,p_init_msg_list
459 ,x_return_status
460 ,x_msg_count
461 ,x_msg_data
462 ,l_xcrv_rec
463 ,x_xcrv_rec
464 );
465
466 x_return_status := l_return_status;
467 x_msg_data := l_msg_data;
468 x_msg_count := l_msg_count;
469
470 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
471 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
472 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
473 RAISE OKL_API.G_EXCEPTION_ERROR;
474 END IF;
475
476
477 END IF;
478
479
480 -------------------------------------------------------------------------------------------
481 -- clean up redundant records without amounts applied before calling AR
482
483 DELETE
484 FROM OKL_XTL_CSH_APPS_B
485 WHERE AMOUNT_APPLIED = 0
486 AND XCR_ID_DETAILS = l_xcr_id_details;
487
488 -------------------------------------------------------------------------------------------
489
490
491 EXCEPTION
492
493 WHEN G_EXCEPTION_HALT_VALIDATION THEN
494 x_return_status := OKC_API.G_RET_STS_ERROR;
495
496
497 WHEN OKC_API.G_EXCEPTION_ERROR THEN
498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
499 (
500 l_api_name,
501 G_PKG_NAME,
502 'OKC_API.G_RET_STS_ERROR',
503 x_msg_count,
504 x_msg_data,
505 '_PVT'
506 );
507
508 WHEN OTHERS THEN
509 NULL;
510 /*
511 Okl_api.set_message( p_app_name => g_app_name
512 , p_msg_name => g_unexpected_error
513 , p_token1 => g_sqlcode_token
514 , p_token1_value => SQLCODE
515 , p_token2 => g_sqlerrm_token
516 , p_token2_value => SQLERRM
517 ) ;
518 */
519
520 END update_cash_details;
521 END Okl_Updt_Cash_Dtls;