[Home] [Help]
PACKAGE BODY: APPS.OKL_PAYMENT_PVT
Source
1 PACKAGE BODY OKL_PAYMENT_PVT AS
2 /* $Header: OKLRPAYB.pls 120.21 2007/10/11 16:03:11 varangan noship $ */
3
4 SUBTYPE rctv_rec_type IS Okl_Rct_Pvt.rctv_rec_type;
5 SUBTYPE rcav_tbl_type IS Okl_Rca_Pvt.rcav_tbl_type;
6
7 SUBTYPE rcpt_rec_type IS OKL_RECEIPTS_PVT.rcpt_rec_type;
8 SUBTYPE appl_tbl_type IS OKL_RECEIPTS_PVT.appl_tbl_type;
9
10 ---------------------------------------------------------------------------
11 -- PROCEDURE qc
12 ---------------------------------------------------------------------------
13 PROCEDURE qc IS
14 BEGIN
15 NULL;
16 END qc;
17
18 ---------------------------------------------------------------------------
19 -- PROCEDURE change_version
20 ---------------------------------------------------------------------------
21 PROCEDURE change_version IS
22 BEGIN
23 NULL;
24 END change_version;
25
26 ---------------------------------------------------------------------------
27 -- PROCEDURE api_copy
28 ---------------------------------------------------------------------------
29 PROCEDURE api_copy IS
30 BEGIN
31 NULL;
32 END api_copy;
33 ---------------------------------------------------------------------------
34 -- PROCEDURE GET_ORG_ID
35 ---------------------------------------------------------------------------
36 -- Get Org ID for a contract
37 FUNCTION GET_ORG_ID(
38 p_contract_id IN NUMBER,
39 x_org_id OUT NOCOPY NUMBER
40 )
41 RETURN VARCHAR2 AS
42 -- get org_id for contract
43 CURSOR get_org_id_csr (p_contract_id IN VARCHAR2) IS
44 SELECT authoring_org_id
45 FROM okc_k_headers_b
46 WHERE id = p_contract_id;
47
48 l_api_version NUMBER;
49 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
50 l_msg_count NUMBER;
51 l_msg_data VARCHAR2(2000);
52
53 BEGIN
54
55 OPEN get_org_id_csr(p_contract_id);
56 FETCH get_org_id_csr INTO x_org_id;
57 CLOSE get_org_id_csr;
58
59 RETURN l_return_status;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 OKC_API.SET_MESSAGE( p_app_name => G_APP_NAME
64 ,p_msg_name => G_UNEXPECTED_ERROR
65 ,p_token1 => G_SQLCODE_TOKEN
66 ,p_token1_value => SQLCODE
67 ,p_token2 => G_SQLERRM_TOKEN
68 ,p_token2_value => SQLERRM);
69 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
70 RETURN(l_return_status);
71
72 END GET_ORG_ID;
73
74 ---------------------------------------------------------------------------
75 -- PROCEDURE CREATE_INTERNAL_TRANS
76 ---------------------------------------------------------------------------
77 PROCEDURE CREATE_INTERNAL_TRANS(
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
80 p_customer_id IN NUMBER,
81 p_contract_id IN NUMBER,
82 p_contract_num IN VARCHAR2 DEFAULT NULL,
83 p_payment_method_id IN NUMBER,
84 p_payment_ref_number IN VARCHAR2,
85 p_payment_amount IN NUMBER,
86 p_currency_code IN VARCHAR2,
87 p_payment_date IN DATE,
88 x_payment_id OUT NOCOPY NUMBER,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 )
93 IS
94
95 l_api_version CONSTANT NUMBER := 1;
96 l_api_name CONSTANT VARCHAR2(30) := 'OKL_PAYMENT_PVT';
97 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
98 i NUMBER := 1;
99 l_check NUMBER := 0;
100 l_org_id NUMBER;
101 l_rctv_rec rctv_rec_type;
102 x_rctv_rec rctv_rec_type;
103 l_rcav_tbl rcav_tbl_type;
104 x_rcav_tbl rcav_tbl_type;
105
106 CURSOR get_receipt_method_csr(p_payment_method_id NUMBER) IS
107 SELECT 1
108 FROM ar_receipt_methods
109 WHERE receipt_method_id = p_payment_method_id;
110
111 --jsanju 07/09 as per IEX requirements ( bug #3040085)
112 cursor c_get_exchange_info (p_contract_id IN NUMBER) IS
113 -- converting into upper because of the BPD code, they are looking for
114 -- upper conversion_types - CORPORATE, SPOT ,USER in okl_cash_appl_rules.
115 -- handle_manual_pay
116 select UPPER(currency_conversion_type),
117 currency_conversion_rate,
118 currency_conversion_date
119 from okl_k_headers
120 where id =p_contract_id;
121
122 l_functional_currency okl_trx_contracts.currency_code%TYPE;
123
124 BEGIN
125
126 l_return_status := okl_api.START_ACTIVITY(l_api_name,
127 G_PKG_NAME,
128 p_init_msg_list,
129 l_api_version,
130 p_api_version,
131 '_PAYMENT',
132 x_return_status);
133
134 /* Processing Starts */
135 -- Check if Customer ID is null
136 IF p_customer_id IS NULL THEN
137 OKL_API.set_message(p_app_name => G_APP_NAME,
138 p_msg_name => G_CUSTOMER_ID_NULL );
139 RAISE okl_api.G_EXCEPTION_ERROR;
140 END IF;
141
142 -- Check if Contract ID is null
143 IF p_contract_id IS NULL THEN
144 OKL_API.set_message(p_app_name => G_APP_NAME,
145 p_msg_name => G_CONTRACT_ID_NULL );
146 RAISE okl_api.G_EXCEPTION_ERROR;
147 END IF;
148
149 -- Get org Id for the contract
150 --l_return_status := get_org_id(p_contract_id, l_org_id);
151
152 l_org_id :=mo_global.get_current_org_id();
153 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
154 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
155 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
156 RAISE okl_api.G_EXCEPTION_ERROR;
157 END IF;
158
159 -- get payment method
160 OPEN get_receipt_method_csr(p_payment_method_id);
161 FETCH get_receipt_method_csr INTO l_check;
162 CLOSE get_receipt_method_csr;
163
164 -- Check if payment_method is null
165 IF (l_check <> 1) THEN
166 OKL_API.set_message(p_app_name => G_APP_NAME,
167 p_msg_name => G_PAYMENT_METHOD_INVALID );
168 RAISE okl_api.G_EXCEPTION_ERROR;
169 END IF;
170
171 -- Create record in Internal Transaction Table.
172 -- CREATE HEADER REC
173 -- l_rctv_rec.IBA_ID := l_iba_id; -- bank account id
174 l_rctv_rec.IRM_ID := p_payment_method_id; -- receipts method id (HARD CODED FOR NOW)
175 l_rctv_rec.ILE_ID := p_customer_id;
176 l_rctv_rec.CHECK_NUMBER := p_payment_ref_number;
177 l_rctv_rec.AMOUNT := p_payment_amount;
178 l_rctv_rec.CURRENCY_CODE := p_currency_code;
179 l_rctv_rec.DATE_EFFECTIVE := SYSDATE;
180 l_rctv_rec.ORG_ID := l_org_id;
181 i := 1;
182
183 -- populate the currency conversion fields
184 -- get the 3 fields from okl_k_headers for a contract
185 --for bug #(3040085)
186 --jsanju 07/10
187 -- populate this only if receipt currency is diff from
188 -- functional currency.
189 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
190 if l_functional_currency <> p_currency_code THEN
191 /* OPEN c_get_exchange_info (p_contract_id);
192 FETCH c_get_exchange_info INTO l_rctv_rec.exchange_rate_type,
193 l_rctv_rec.exchange_rate,
194 l_rctv_rec.exchange_rate_date ;
195 CLOSE c_get_exchange_info;
196 */
197 -- The values should be coming in from the UI.
198 -- so for the time being hard coded to 'CORPORATE'
199 --07/15
200 l_rctv_rec.exchange_rate_type := 'CORPORATE';
201 End if;
202
203 /*
204 l_rctv_rec.exchange_rate_type := 'CORPORATE';
205 l_rctv_rec.exchange_rate :=.53;
206 l_rctv_rec.exchange_rate_date := '01-JAN-03';
207
208 IF (l_rctv_rec.exchange_rate_type IS NULL) AND (l_rctv_rec.exchange_rate IS NULL)
209 AND (l_rctv_rec.exchange_rate_date IS NULL) THEN
210 OKL_API.set_message(p_app_name => G_APP_NAME,
211 p_msg_name => G_PAYMENT_METHOD_INVALID );
212 RAISE okl_api.G_EXCEPTION_ERROR;
213 END IF;
214 */
215
216 l_rcav_tbl(i).CNR_ID := NULL;
217 l_rcav_tbl(i).KHR_ID := p_contract_id;
218 -- l_rcav_tbl(i).LLN_ID := l_lln_id; -- consolidated ar lines id
219 -- l_rcav_tbl(i).LSM_ID := l_lsm_id; -- consolidated ar streams id
220 l_rcav_tbl(i).ILE_ID := p_customer_id;
221 l_rcav_tbl(i).AMOUNT := p_payment_amount;
222 l_rcav_tbl(i).LINE_NUMBER := i;
223 l_rcav_tbl(i).ORG_ID := l_org_id;
224
225 Okl_Rct_Pub.create_internal_trans
226 (
227 p_api_version
228 ,p_init_msg_list
229 ,x_return_status
230 ,x_msg_count
231 ,x_msg_data
232 ,l_rctv_rec
233 ,l_rcav_tbl
234 ,x_rctv_rec
235 ,x_rcav_tbl
236 );
237
238 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
239 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
240 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
241 RAISE okl_api.G_EXCEPTION_ERROR;
242 END IF;
243
244 x_payment_id := x_rctv_rec.id;
245 x_return_status := l_return_status;
246
247 /* Processing Ends */
248
249 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
250
251 EXCEPTION
252 WHEN okl_api.G_EXCEPTION_ERROR THEN
253 x_return_status := okl_api.HANDLE_EXCEPTIONS
254 (
255 l_api_name,
256 G_PKG_NAME,
257 'okl_api.G_RET_STS_ERROR',
258 x_msg_count,
259 x_msg_data,
260 '_PAYMENT'
261 );
262 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
263 x_return_status :=okl_api.HANDLE_EXCEPTIONS
264 (
265 l_api_name,
266 G_PKG_NAME,
267 'okl_api.G_RET_STS_UNEXP_ERROR',
268 x_msg_count,
269 x_msg_data,
270 '_PAYMENT'
271 );
272 WHEN OTHERS THEN
273 x_return_status :=okl_api.HANDLE_EXCEPTIONS
274 (
275 l_api_name,
276 G_PKG_NAME,
277 'OTHERS',
278 x_msg_count,
279 x_msg_data,
280 '_PAYMENT'
281 );
282 END CREATE_INTERNAL_TRANS;
283
284 ---------------------------------------------------------------------------
285 -- PROCEDURE CREATE_INTERNAL_TRANS
286 ---------------------------------------------------------------------------
287 PROCEDURE CREATE_INTERNAL_TRANS(
288 p_api_version IN NUMBER,
289 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
290 p_customer_id IN NUMBER,
291 p_invoice_id IN NUMBER,
292 p_payment_method_id IN NUMBER,
293 p_payment_ref_number IN VARCHAR2,
294 p_payment_amount IN NUMBER,
295 p_currency_code IN VARCHAR2,
296 p_payment_date IN DATE,
297 x_payment_id OUT NOCOPY NUMBER,
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2
301 )
302 IS
303
304 l_api_version CONSTANT NUMBER := 1;
305 l_api_name CONSTANT VARCHAR2(30) := 'OKL_PAYMENT_PVT';
306 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
307 i NUMBER := 1;
308 l_check NUMBER := 0;
309 l_org_id NUMBER;
310 l_rctv_rec rctv_rec_type;
311 x_rctv_rec rctv_rec_type;
312 l_rcav_tbl rcav_tbl_type;
313 x_rcav_tbl rcav_tbl_type;
314
315 G_CUSTOMER_ID_NULL CONSTANT VARCHAR2(200) := 'OKL_CUSTOMER_ID_NULL';
316 G_INVOICE_ID_NULL CONSTANT VARCHAR2(200) := 'OKL_INVOICE_ID_NULL';
317 G_PAYMENT_METHOD_NULL CONSTANT VARCHAR2(200) := 'OKL_PAYMENT_METHOD_NULL';
318 G_PAYMENT_METHOD_INVALID CONSTANT VARCHAR2(200) := 'OKL_PAYMENT_METHOD_INVALID';
319
320 CURSOR get_receipt_method_csr(p_payment_method_id NUMBER) IS
321 SELECT 1
322 FROM ar_receipt_methods
323 WHERE receipt_method_id = p_payment_method_id;
324
325 --jsanju 07/09 as per IEX requirements ( bug #3040085)
326 cursor c_get_exchange_info (p_invoice_id IN NUMBER) IS
327 select upper(chr.currency_conversion_type),
328 chr.currency_conversion_rate,
329 chr.currency_conversion_date
330 from okl_k_headers chr,
331 okl_cnsld_ar_lines_b lln,
332 okl_cnsld_ar_strms_b strm
333 where strm.khr_id =chr.id
334 and lln.cnr_id =p_invoice_id
335 and strm.lln_id =lln.id
336 and rownum <2
337 group by chr.currency_conversion_type,
338 chr.currency_conversion_rate,
339 chr.currency_conversion_date
340 Order by chr.currency_conversion_type;
341
342 l_functional_currency okl_trx_contracts.currency_code%TYPE;
343
344 BEGIN
345
346 l_return_status := okl_api.START_ACTIVITY(l_api_name,
347 G_PKG_NAME,
348 p_init_msg_list,
349 l_api_version,
350 p_api_version,
351 '_PAYMENT',
352 x_return_status);
353
354 /* Processing Starts */
355 -- Check if Customer ID is null
356 IF p_customer_id IS NULL THEN
357 OKL_API.set_message(p_app_name => G_APP_NAME,
358 p_msg_name => G_CUSTOMER_ID_NULL );
359 RAISE okl_api.G_EXCEPTION_ERROR;
360 END IF;
361
362 -- Check if Contract ID is null
363 IF p_invoice_id IS NULL THEN
364 OKL_API.set_message(p_app_name => G_APP_NAME,
365 p_msg_name => G_INVOICE_ID_NULL );
366 RAISE okl_api.G_EXCEPTION_ERROR;
367 END IF;
368
369 -- Get org Id for OKL
370 l_org_id :=mo_global.get_current_org_id();
371
372 -- get payment method
373 OPEN get_receipt_method_csr(p_payment_method_id);
374 FETCH get_receipt_method_csr INTO l_check;
375 CLOSE get_receipt_method_csr;
376
377 -- Check if payment_method is null
378 IF (l_check <> 1) THEN
379 OKL_API.set_message(p_app_name => G_APP_NAME,
380 p_msg_name => G_PAYMENT_METHOD_INVALID );
381 RAISE okl_api.G_EXCEPTION_ERROR;
382 END IF;
383
384
385 -- populate the currency conversion fields
386 -- get the 3 fields from okl_k_headers for a contract
387 --for bug #(3040085)
388 --jsanju 07/10
389 -- populate this only if receipt currency is diff from
390 -- functional currency.
391 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
392 if l_functional_currency <> p_currency_code THEN
393 OPEN c_get_exchange_info (p_invoice_id);
394 /* OPEN c_get_exchange_info (p_contract_id);
395 FETCH c_get_exchange_info INTO l_rctv_rec.exchange_rate_type,
396 l_rctv_rec.exchange_rate,
397 l_rctv_rec.exchange_rate_date ;
398 CLOSE c_get_exchange_info;
399 */
400 -- The values should be coming in from the UI.
401 -- so for the time being hard coded to 'CORPORATE'
402 --07/15/03
403 l_rctv_rec.exchange_rate_type := 'CORPORATE';
404
405 End if;
406
407 /*
408 l_rctv_rec.exchange_rate_type := 'CORPORATE';
409 l_rctv_rec.exchange_rate :=.53;
410 l_rctv_rec.exchange_rate_date := '01-JAN-03';
411
412 IF (l_rctv_rec.exchange_rate_type IS NULL) AND (l_rctv_rec.exchange_rate IS NULL)
413 AND (l_rctv_rec.exchange_rate_date IS NULL) THEN
414 OKL_API.set_message(p_app_name => G_APP_NAME,
415 p_msg_name => G_PAYMENT_METHOD_INVALID );
416 RAISE okl_api.G_EXCEPTION_ERROR;
417 END IF;
418 */
419
420 -- Create record in Internal Transaction Table.
421 -- CREATE HEADER REC
422 -- l_rctv_rec.IBA_ID := l_iba_id; -- bank account id
423 l_rctv_rec.IRM_ID := p_payment_method_id; -- receipts method id (HARD CODED FOR NOW)
424 l_rctv_rec.ILE_ID := p_customer_id;
425 l_rctv_rec.CHECK_NUMBER := p_payment_ref_number;
426 l_rctv_rec.AMOUNT := p_payment_amount;
427 l_rctv_rec.CURRENCY_CODE := p_currency_code;
428 l_rctv_rec.DATE_EFFECTIVE := SYSDATE;
429 l_rctv_rec.ORG_ID := l_org_id;
430 i := 1;
431
432 l_rcav_tbl(i).CNR_ID := p_invoice_id;
433 l_rcav_tbl(i).KHR_ID := NULL;
434 -- l_rcav_tbl(i).LLN_ID := l_lln_id; -- consolidated ar lines id
435 -- l_rcav_tbl(i).LSM_ID := l_lsm_id; -- consolidated ar streams id
436 l_rcav_tbl(i).ILE_ID := p_customer_id;
437 l_rcav_tbl(i).AMOUNT := p_payment_amount;
438 l_rcav_tbl(i).LINE_NUMBER := i;
439 l_rcav_tbl(i).ORG_ID := l_org_id;
440
441 Okl_Rct_Pub.create_internal_trans
442 (
443 p_api_version
444 ,p_init_msg_list
445 ,x_return_status
446 ,x_msg_count
447 ,x_msg_data
448 ,l_rctv_rec
449 ,l_rcav_tbl
450 ,x_rctv_rec
451 ,x_rcav_tbl
452 );
453
454 IF (l_return_status = okl_api.G_RET_STS_UNEXP_ERROR) THEN
455 RAISE okl_api.G_EXCEPTION_UNEXPECTED_ERROR;
456 ELSIF (l_return_status = okl_api.G_RET_STS_ERROR) THEN
457 RAISE okl_api.G_EXCEPTION_ERROR;
458 END IF;
459
460 x_payment_id := x_rctv_rec.id;
461 x_return_status := l_return_status;
462
463 /* Processing Ends */
464
465 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
466
467 EXCEPTION
468 WHEN okl_api.G_EXCEPTION_ERROR THEN
469 x_return_status := okl_api.HANDLE_EXCEPTIONS
470 (
471 l_api_name,
472 G_PKG_NAME,
473 'okl_api.G_RET_STS_ERROR',
474 x_msg_count,
475 x_msg_data,
476 '_PAYMENT'
477 );
478 WHEN okl_api.G_EXCEPTION_UNEXPECTED_ERROR THEN
479 x_return_status :=okl_api.HANDLE_EXCEPTIONS
480 (
481 l_api_name,
482 G_PKG_NAME,
483 'okl_api.G_RET_STS_UNEXP_ERROR',
484 x_msg_count,
485 x_msg_data,
486 '_PAYMENT'
487 );
488 WHEN OTHERS THEN
489 x_return_status :=okl_api.HANDLE_EXCEPTIONS
490 (
491 l_api_name,
492 G_PKG_NAME,
493 'OTHERS',
494 x_msg_count,
495 x_msg_data,
496 '_PAYMENT'
497 );
498 END CREATE_INTERNAL_TRANS;
499
500
501 ---------------------------------------------------------------------------
502 -- PROCEDURE CREATE_PAYMENTS
503 ---------------------------------------------------------------------------
504 PROCEDURE CREATE_PAYMENTS(
505 p_api_version IN NUMBER,
506 p_init_msg_list IN VARCHAR2 DEFAULT okl_api.G_FALSE,
507 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
508 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_msg_count OUT NOCOPY NUMBER,
511 x_msg_data OUT NOCOPY VARCHAR2,
512 p_receipt_rec IN receipt_rec_type,
513 p_payment_tbl IN payment_tbl_type,
514 x_payment_ref_number OUT NOCOPY AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER%TYPE,
515 x_cash_receipt_id OUT NOCOPY NUMBER
516 )
517
518 IS
519
520 l_api_version CONSTANT NUMBER := 1.0;
521 l_api_name CONSTANT VARCHAR2(30) := 'OKL_PAYMENT_PVT';
522 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
523 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
524 l_msg_count NUMBER;
525 l_msg_data VARCHAR2(2000);
526 i NUMBER := 1;
527 l_counter NUMBER := 0;
528 l_check NUMBER := 0;
529 l_commit VARCHAR2(1);
530 l_validation_level NUMBER;
531 l_customer_site_use_id NUMBER;
532 l_payment_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL;
533 l_rct_id OKL_TRX_CSH_RECEIPT_V.ID%TYPE;
534 l_xcr_id NUMBER;
535 l_cash_receipt_id NUMBER;
536 l_cons_bill_id OKL_CNSLD_AR_HDRS_V.ID%TYPE;
537 l_cons_bill_num OKL_CNSLD_AR_HDRS_V.CONSOLIDATED_INVOICE_NUMBER%TYPE;
538 l_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE;
539 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE;
540 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE;
541 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE;
542 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE;
543 l_payment_ref_number AR_CASH_RECEIPTS_ALL.RECEIPT_NUMBER%TYPE DEFAULT NULL;
544 l_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE;
545 l_contract_id OKC_K_HEADERS_B.ID%TYPE;
546 l_contract_num OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
547 l_cust_acct_id NUMBER;
548 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE;
549 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE;
550 l_receipt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE;
551 l_rcpt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_receipt_rec.PAYMENT_DATE);
552 l_org_id Number DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
553 l_rcpt_rec rcpt_rec_type;
554 l_appl_tbl appl_tbl_type;
555 l_customer_trx_id NUMBER;
556 l_payment_trxn_extension_id NUMBER;
557 l_remit_bank_acct_id NUMBER;
558 l_payment_channel_code varchar2(240);
559
560 --Getting Trx Date
561 /*
562 Cursor c_get_date (l_orgid Number,l_customer_trx_id Number) Is
563 Select Trx_date apply_date
564 from ra_customer_trx_all
565 where customer_trx_id=l_customer_trx_id
566 and org_id=l_orgid;
567 */
568 ------------------------------------------------------------------------------
569
570 CURSOR get_rct_id ( cp_amount IN NUMBER
571 ,cp_bank_acct_id IN NUMBER
572 ,cp_cust_id IN NUMBER
573 ,cp_rcpt_date IN DATE
574 ,cp_irm_id IN NUMBER) IS
575
576 SELECT ID INTO l_rct_id
577 FROM OKL_TRX_CSH_RECEIPT_V
578 WHERE AMOUNT = cp_amount
579 AND IBA_ID = cp_bank_acct_id
580 AND ILE_ID = cp_cust_id
581 AND DATE_EFFECTIVE = cp_rcpt_date
582 AND IRM_ID = cp_irm_id
583 ORDER BY CREATION_DATE DESC;
584
585 CURSOR c_get_invoice_org_id(ar_inv_id IN NUMBER) IS
586 SELECT ORG_ID
587 FROM RA_CUSTOMER_TRX_ALL
588 WHERE CUSTOMER_TRX_ID = ar_inv_id;
589
590 CURSOR c_get_cons_inv_org_id(cons_inv_id IN NUMBER) IS
591 SELECT ORG_ID
592 FROM OKL_CNSLD_AR_HDRS_ALL_B
593 WHERE ID = cons_inv_id;
594
595 CURSOR c_get_receipt_method_id(p_org_id IN NUMBER, cp_payment_channel_code IN VARCHAR2) IS
596 SELECT receipt_method_id
597 FROM okl_pmt_channel_methods_All
598 WHERE ORG_ID = p_org_id
599 AND payment_channel_code = cp_payment_channel_code;
600
601 CURSOR c_get_remittance_details(cp_org_id IN NUMBER, cp_irm_id IN NUMBER) IS
602 SELECT bank_account_id
603 FROM okl_bpd_rcpt_mthds_uv
604 WHERE ORG_ID = cp_org_id
605 AND RECEIPT_METHOD_ID = cp_irm_id;
606
607 CURSOR c_get_payment_channel(cp_trx_extn_id IN NUMBER) IS
608 SELECT PAYMENT_CHANNEL_CODE
609 FROM IBY_FNDCPT_TX_EXTENSIONS
610 WHERE TRXN_EXTENSION_ID = cp_trx_extn_id;
611 ------------------------------------------------------------------------------
612
613 /*
614 CURSOR get_icr_id (cp_rct_id IN NUMBER) IS
615
616 SELECT ID, ICR_ID
617 FROM OKL_EXT_CSH_RCPTS_V
618 WHERE RCT_ID = cp_rct_id;
619 */
620 ------------------------------------------------------------------------------
621
622
623 BEGIN
624
625 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
626 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PAYMENT_PVT.CREATE_PAYMENTS','Begin(+)');
627 END IF;
628
629 --Print Input Variables
630 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
631
632 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
633 'p_receipt_rec.p_currency_code :'||p_receipt_rec.currency_code);
634
635 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
636 'p_currency_conv_type :'||p_receipt_rec.currency_conv_type);
637
638 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
639 'p_currency_conv_date :'||p_receipt_rec.currency_conv_date);
640
641 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
642 'p_currency_conv_rate :'||p_receipt_rec.currency_conv_rate);
643
644 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
645 'p_irm_id :'||p_receipt_rec.irm_id);
646
647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
648 'p_contract_id :'||p_receipt_rec.contract_id);
649
650 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
651 'p_contract_num :'||p_receipt_rec.contract_num);
652
653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
654 'p_customer_id :'||p_receipt_rec.cust_acct_id);
655
656 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
657 'p_customer_num :'||p_receipt_rec.customer_num);
658
659 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
660 'p_gl_date :'||p_receipt_rec.gl_date);
661
662 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
663 'p_payment_date :'||p_receipt_rec.payment_date);
664
665 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
666 'p_customer_site_use_id :'||p_receipt_rec.customer_site_use_id);
667
668 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'OKL_PAYMENT_PVT.CREATE_PAYMENTS.',
669 'p_expiration_date :'||p_receipt_rec.expiration_date);
670
671 END IF;
672
673 l_return_status := okl_api.START_ACTIVITY(l_api_name,
674 G_PKG_NAME,
675 p_init_msg_list,
676 l_api_version,
677 p_api_version,
678 '_PVT',
679 x_return_status);
680
681 l_currency_code := p_receipt_rec.currency_code;
682 l_currency_conv_type := p_receipt_rec.currency_conv_type;
683 l_currency_conv_date := p_receipt_rec.currency_conv_date;
684 l_currency_conv_rate := p_receipt_rec.currency_conv_rate;
685 l_payment_ref_number := NULL;
686 l_cust_acct_id := p_receipt_rec.cust_acct_id;
687 l_customer_num := p_receipt_rec.customer_num;
688 l_gl_date := p_receipt_rec.gl_date;
689 l_receipt_date := p_receipt_rec.payment_date;
690 l_customer_site_use_id := p_receipt_rec.customer_site_use_id;
691 l_commit := p_commit;
692 l_payment_trxn_extension_id := p_receipt_rec.payment_trxn_extension_id;
693 l_irm_id :=p_receipt_rec.irm_id;
694 l_remit_bank_acct_id :=p_receipt_rec.rem_bank_acc_id;
695
696 -- Setting SYSDATE as receipt date if it is null
697 If l_rcpt_date Is Null Then
698 l_rcpt_date := TRUNC(SYSDATE);
699 End If;
700
701 i := p_payment_tbl.FIRST;
702
703 --Populate receipt header record
704 l_rcpt_rec.cash_receipt_id := NULL;
705 l_rcpt_rec.amount := l_rcpt_amount;
706 l_rcpt_rec.currency_code := l_currency_code;
707 l_rcpt_rec.customer_number := l_customer_num;
708 l_rcpt_rec.customer_id := l_cust_acct_id;
709 l_rcpt_rec.receipt_date := l_receipt_date;
710 l_rcpt_rec.gl_date := l_receipt_date;
711 l_rcpt_rec.payment_trx_extension_id := l_payment_trxn_extension_id;
712 l_rcpt_rec.exchange_rate_type := l_currency_conv_type;
713 l_rcpt_rec.exchange_rate := l_currency_conv_rate;
714 l_rcpt_rec.exchange_date := l_currency_conv_date;
715 l_rcpt_rec.receipt_method_id := l_irm_id;
716 l_rcpt_rec.create_mode := 'UNAPPLIED';
717 l_rcpt_rec.receipt_method_id :=l_irm_id;
718 l_rcpt_rec.remittance_bank_account_id :=l_remit_bank_acct_id;
719
720
721 l_counter := 0;
722 --If payment table is not empty
723 IF (p_payment_tbl.COUNT > 0) THEN
724 --Get the org id from either consolidated invoice or AR invoice
725 IF (p_payment_tbl(i).con_inv_id IS NOT NULL) THEN
726 OPEN c_get_cons_inv_org_id(p_payment_tbl(i).con_inv_id);
727 FETCH c_get_cons_inv_org_id INTO l_org_id;
728 CLOSE c_get_cons_inv_org_id;
729 ELSIF (p_payment_tbl(i).ar_inv_id IS NOT NULL) THEN
730 OPEN c_get_invoice_org_id(p_payment_tbl(i).ar_inv_id);
731 FETCH c_get_invoice_org_id INTO l_org_id;
732 CLOSE c_get_invoice_org_id;
733 END IF;
734
735 IF l_org_id IS NOT NULL THEN
736 l_rcpt_rec.org_id := l_org_id;
737 mo_global.init('M');
738 MO_GLOBAL.set_policy_context('S',l_org_id);
739 END IF;
740
741
742 OPEN c_get_payment_channel(l_payment_trxn_extension_id);
743 FETCH c_get_payment_channel INTO l_payment_channel_code;
744 CLOSE c_get_payment_channel;
745 If l_irm_id Is Null Then
746 --Get receipt method id
747 OPEN c_get_receipt_method_id(l_org_id,l_payment_channel_code);
748 FETCH c_get_receipt_method_id INTO l_irm_id;
749 CLOSE c_get_receipt_method_id;
750 l_rcpt_rec.receipt_method_id := l_irm_id;
751 End If;
752
753 If ((l_remit_bank_acct_id Is Null) And (l_irm_id Is Not Null)) Then
754 --Get remittance bank details
755 OPEN c_get_remittance_details(l_org_id, l_irm_id);
756 FETCH c_get_remittance_details INTO l_remit_bank_acct_id;
757 CLOSE c_get_remittance_details;
758 l_rcpt_rec.remittance_bank_account_id := l_remit_bank_acct_id;
759 End If;
760
761 IF l_irm_id IS NULL THEN
762 OKC_API.set_message( p_app_name => G_APP_NAME,
763 p_msg_name =>'OKL_BPD_RCPT_MTHD_NULL');
764 RAISE G_EXCEPTION_HALT_VALIDATION;
765 END IF;
766
767 --If it needs to be applied for AR Invoice header then handle it
768 IF (p_payment_tbl.COUNT = 1 AND p_payment_tbl(i).line_id IS NULL) THEN
769 --Apply against consolidated invoice
770 IF p_payment_tbl(i).CON_INV_ID IS NOT NULL THEN
771 l_appl_tbl(0).con_inv_id := p_payment_tbl(i).con_inv_id;
772 l_appl_tbl(0).amount_to_apply := p_payment_tbl(i).amount;
773 --Apply against AR Invoice
774 ELSIF p_payment_tbl(i).AR_INV_ID IS NOT NULL THEN
775 l_appl_tbl(0).ar_inv_id := p_payment_tbl(i).ar_inv_id;
776 l_appl_tbl(0).amount_to_apply := p_payment_tbl(i).amount;
777 END IF;
778 l_rcpt_amount := p_payment_tbl(i).amount;
779 --Else it needs to be applied for selected invoice lines
780 ELSE
781 l_rcpt_amount := 0;
782 FOR i IN p_payment_tbl.FIRST..p_payment_tbl.LAST
783 LOOP
784 l_appl_tbl(l_counter).ar_inv_id := p_payment_tbl(i).ar_inv_id;
785 l_appl_tbl(l_counter).line_id := p_payment_tbl(i).line_id;
786 l_appl_tbl(l_counter).amount_to_apply := p_payment_tbl(i).amount;
787 l_appl_tbl(l_counter).original_applied_amount := 0;
788 l_rcpt_amount := l_rcpt_amount + p_payment_tbl(i).amount;
789 l_counter := l_counter + 1;
790 END LOOP;
791 END IF;
792 END IF;
793 l_rcpt_rec.amount := l_rcpt_amount;
794 l_rcpt_rec.customer_bank_account_id := NULL;
795 OKL_RECEIPTS_PVT.handle_receipt( p_api_version => l_api_version
796 ,p_init_msg_list => l_init_msg_list
797 ,x_return_status => l_return_status
798 ,x_msg_count => l_msg_count
799 ,x_msg_data => l_msg_data
800 ,p_rcpt_rec => l_rcpt_rec
801 ,p_appl_tbl => l_appl_tbl
802 ,x_cash_receipt_id => l_cash_receipt_id);
803
804 --Set back policy context to M
805 LOOP
806 l_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
807 IF l_msg_data is NULL Then
808 EXIT;
809 END if;
810 END loop;
811 MO_GLOBAL.set_policy_context('M',-1);
812 x_return_status := l_return_status;
813
814 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
815 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
816 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
817 RAISE OKL_API.G_EXCEPTION_ERROR;
818 END IF;
819
820 IF l_cash_receipt_id IS NOT NULL THEN
821 l_payment_ref_number := OKL_PAYMENT_PUB.get_ar_receipt_number(l_cash_receipt_id);
822 END IF;
823
824 x_msg_data := l_msg_data;
825 x_msg_count := l_msg_count;
826 x_payment_ref_number := l_payment_ref_number;
827 x_cash_receipt_id := l_cash_receipt_id;
828 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
829
830 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'OKL_PAYMENT_PVT.CREATE_PAYMENTS','end(-)');
832 END IF;
833
834 EXCEPTION
835
836 WHEN G_EXCEPTION_HALT_VALIDATION THEN
837 x_return_status := OKC_API.G_RET_STS_ERROR;
838
839 WHEN OKL_API.G_EXCEPTION_ERROR THEN
840 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
841 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PAYMENT.CREATE_PAYMENTS ',
842 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
843 END IF;
844
845 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
846 l_api_name,
847 G_PKG_NAME,
848 'OKL_API.G_RET_STS_ERROR',
849 x_msg_count,
850 x_msg_data,
851 '_PVT');
852
853 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
854 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
855 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PAYMENT.CREATE_PAYMENTS ',
856 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
857 END IF;
858
859 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
860 l_api_name,
861 G_PKG_NAME,
862 'OKL_API.G_RET_STS_UNEXP_ERROR',
863 x_msg_count,
864 x_msg_data,
865 '_PVT');
866
867 WHEN OTHERS THEN
868
869 IF (FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'OKL_PAYMENT.CREATE_PAYMENTS ',
871 'EXCEPTION :'||sqlerrm);
872 END IF;
873
874 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
875 l_api_name,
876 G_PKG_NAME,
877 'OTHERS',
878 x_msg_count,
879 x_msg_data,
880 '_PVT');
881
882 END CREATE_PAYMENTS;
883 END OKL_PAYMENT_PVT;