[Home] [Help]
PACKAGE BODY: APPS.OKL_TRANS_CONTRACTS_PVT
Source
1 PACKAGE BODY OKL_TRANS_CONTRACTS_PVT AS
2 /* $Header: OKLRTCTB.pls 120.4 2006/11/13 05:48:42 dpsingh noship $ */
3
4 --Added by dpsingh for LE Uptake
5 CURSOR get_contract_number(p_khr_id NUMBER) IS
6 SELECT CONTRACT_NUMBER
7 FROM OKC_K_HEADERS_B
8 WHERE ID = p_khr_id ;
9
10 -- Added by Santonyr on 03-Dec-2002
11 -- This procedure makes sure that the sum of transaction line
12 -- amount is not greater than transaction amount.
13
14 PROCEDURE Validate_Amount (p_tcnv_rec IN tcnv_rec_type,
15 x_return_status OUT NOCOPY VARCHAR2)
16 IS
17
18 -- Cursor to fetch trx line amount
19 CURSOR txl_amt_csr (l_trx_id NUMBER) IS
20 SELECT SUM(AMOUNT) SUM_AMOUNT
21 FROM OKL_TXL_CNTRCT_LNS
22 WHERE TCN_ID = l_trx_id;
23
24 l_sum_txl_amt NUMBER := 0;
25
26 BEGIN
27
28 x_return_status := OKL_API.G_RET_STS_SUCCESS;
29
30 -- Fetch trx line amount for update mode
31
32 FOR txl_amt_rec IN txl_amt_csr (p_tcnv_rec.ID) LOOP
33 l_sum_txl_amt := NVL(txl_amt_rec.SUM_AMOUNT, 0);
34 END LOOP;
35
36 -- Return Error status if sum of trx line amount is greater than trx amount.
37
38 IF l_sum_txl_amt > NVL(p_tcnv_rec.amount, 0) THEN
39 x_return_status := OKL_API.G_RET_STS_ERROR;
40 END IF;
41
42 END Validate_Amount;
43
44 PROCEDURE create_trx_contracts(p_api_version IN NUMBER
45 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
46 ,x_return_status OUT NOCOPY VARCHAR2
47 ,x_msg_count OUT NOCOPY NUMBER
48 ,x_msg_data OUT NOCOPY VARCHAR2
49 ,p_tcnv_rec IN tcnv_rec_type
50 ,p_tclv_tbl IN tclv_tbl_type
51 ,x_tcnv_rec OUT NOCOPY tcnv_rec_type
52 ,x_tclv_tbl OUT NOCOPY tclv_tbl_type)
53
54 IS
55
56 l_api_version NUMBER := 1.0;
57 l_try_id NUMBER := 0;
58 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
59 l_functional_currency okl_trx_contracts.currency_code%TYPE;
60
61 -- Added by Santonyr on 22-Nov-2002 Multi-Currency
62
63 l_currency_conversion_type okl_k_headers.currency_conversion_type%TYPE;
64 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%TYPE;
65 l_currency_conversion_date okl_k_headers.currency_conversion_date%TYPE;
66 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
67
68 CURSOR try_csr IS
69 SELECT ID
70 FROM OKL_TRX_TYPES_TL
71 WHERE NAME = 'Miscellaneous'
72 AND LANGUAGE = 'US';
73
74 -- Added by Santonyr on 22-Nov-2002. Multi-Currency Changes
75 -- Derived the currency conversion factors from Contracts table
76
77 CURSOR curr_csr (l_khr_id NUMBER) IS
78 SELECT currency_conversion_type, currency_conversion_rate,
79 currency_conversion_date
80 FROM okl_k_headers
81 WHERE id = l_khr_id;
82
83 l_legal_entity_id NUMBER;
84
85 BEGIN
86
87 IF (p_tcnv_rec.khr_id IS NULL) OR
88 (p_tcnv_rec.khr_id = OKL_Api.G_MISS_NUM) THEN
89 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
90 ,p_msg_name => g_required_value
91 ,p_token1 => g_col_name_token
92 ,p_token1_value => 'KHR_ID');
93 x_return_status := OKL_Api.G_RET_STS_ERROR;
94 RAISE OKL_API.G_EXCEPTION_ERROR;
95
96 END IF;
97
98 IF (p_tcnv_rec.Amount IS NULL) OR
99 (p_tcnv_rec.Amount = OKL_Api.G_MISS_NUM) OR
100 (p_tcnv_rec.Amount = 0) THEN
101 OKC_Api.SET_MESSAGE(p_app_name => 'OKC'
102 ,p_msg_name => g_required_value
103 ,p_token1 => g_col_name_token
104 ,p_token1_value => 'AMOUNT');
105 x_return_status := OKL_Api.G_RET_STS_ERROR;
106 RAISE OKL_API.G_EXCEPTION_ERROR;
107
108 END IF;
109
110
111 OPEN try_csr;
112 FETCH try_csr INTO l_try_id;
113
114 IF (try_csr%NOTFOUND) THEN
115 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
116 p_msg_name => 'OKL_MISC_TRX_NOT_FOUND');
117 CLOSE try_csr;
118 RAISE OKL_API.G_EXCEPTION_ERROR;
119 END IF;
120
121 CLOSE try_csr;
122
123 --Added by dpsingh for LE Uptake
124 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_tcnv_rec.khr_id) ;
125 IF l_legal_entity_id IS NOT NULL THEN
126 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
127 ELSE
128 -- get the contract number
129 OPEN get_contract_number(l_tcnv_rec.khr_id);
130 FETCH get_contract_number INTO l_cntrct_number;
131 CLOSE get_contract_number;
132 Okl_Api.set_message(p_app_name => g_app_name,
133 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
134 p_token1 => 'CONTRACT_NUMBER',
135 p_token1_value => l_cntrct_number);
136 RAISE OKL_API.G_EXCEPTION_ERROR;
137 END IF;
138
139 l_tcnv_rec.TRY_ID := l_try_id;
140 l_tcnv_rec.TCN_TYPE := 'MAE';
141
142
143 -- Added by Santonyr on 22-Nov-2002. Multi-Currency Changes
144 -- Derive the currency conversion factors from Contracts table
145
146 -- Fetch the functional currency
147 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
148
149 -- Fetch the currency conversion factors if functional currency is not equal
150 -- to the transaction currency
151
152 IF l_functional_currency <> l_tcnv_rec.currency_code THEN
153
154 -- Fetch the currency conversion factors from Contracts
155 FOR curr_rec IN curr_csr(l_tcnv_rec.khr_id) LOOP
156 l_currency_conversion_type := curr_rec.currency_conversion_type;
157 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
158 l_currency_conversion_date := curr_rec.currency_conversion_date;
159 END LOOP;
160
161 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
162 -- conversion type is not 'USER'.
163
164 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
165 l_currency_conversion_date := l_tcnv_rec.date_transaction_occurred;
166 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
167 (p_from_curr_code => l_tcnv_rec.currency_code,
168 p_to_curr_code => l_functional_currency,
169 p_con_date => l_currency_conversion_date,
170 p_con_type => l_currency_conversion_type);
171
172 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
173
174 END IF; -- End IF for (l_functional_currency <> l_tcnv_rec.currency_code)
175
176 -- Populate the currency conversion factors
177
178 l_tcnv_rec.currency_conversion_type := l_currency_conversion_type;
179 l_tcnv_rec.currency_conversion_rate := l_currency_conversion_rate;
180 l_tcnv_rec.currency_conversion_date := l_currency_conversion_date;
181
182 -- Round the transaction amount
183
184 l_tcnv_rec.amount := okl_accounting_util.cross_currency_round_amount
185 (p_amount => p_tcnv_rec.amount,
186 p_currency_code => l_tcnv_rec.currency_code);
187
188
189 OKL_TRX_CONTRACTS_PUB.create_trx_contracts( p_api_version => l_api_version
190 ,p_init_msg_list => p_init_msg_list
191 ,x_return_status => x_return_status
192 ,x_msg_count => x_msg_count
193 ,x_msg_data => x_msg_data
194 ,p_tcnv_rec => l_tcnv_rec
195 ,p_tclv_tbl => p_tclv_tbl
196 ,x_tcnv_rec => x_tcnv_rec
197 ,x_tclv_tbl => x_tclv_tbl );
198
199 EXCEPTION
200
201 WHEN OKL_API.G_EXCEPTION_ERROR THEN
202 x_return_status := OKL_API.G_RET_STS_ERROR;
203
204 END;
205
206
207
208 PROCEDURE create_trx_contracts(p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
210 x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 p_tcnv_rec IN tcnv_rec_type,
214 x_tcnv_rec OUT NOCOPY tcnv_rec_type)
215 IS
216
217 l_api_version NUMBER := 1.0;
218 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_TRX_CONTRACTS';
219 l_functional_currency okl_trx_contracts.currency_code%TYPE;
220
221 l_try_id NUMBER := 0;
222 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
223
224 -- Added by Santonyr on 22-Nov-2002 Multi-Currency
225
226 l_currency_conversion_type okl_k_headers.currency_conversion_type%TYPE;
227 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%TYPE;
228 l_currency_conversion_date okl_k_headers.currency_conversion_date%TYPE;
229 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
230
231 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
232
233 CURSOR try_csr IS
234 SELECT ID
235 FROM OKL_TRX_TYPES_TL
236 WHERE NAME = 'Miscellaneous'
237 AND LANGUAGE = 'US';
238
239 -- Added by Santonyr on 22-Nov-2002. Multi-Currency Changes
240 -- Derived the currency conversion factors from Contracts table
241
242 CURSOR curr_csr (l_khr_id NUMBER) IS
243 SELECT currency_conversion_type, currency_conversion_rate,
244 currency_conversion_date
245 FROM okl_k_headers
246 WHERE id = l_khr_id;
247
248 --Added by dpsingh for LE Uptake
249 l_legal_entity_id NUMBER;
250
251 BEGIN
252
253 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
254 G_PKG_NAME,
255 p_init_msg_list,
256 l_api_version,
257 p_api_version,
258 '_PVT',
259 x_return_status);
260 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
261 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
262 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
263 RAISE OKL_API.G_EXCEPTION_ERROR;
264 END IF;
265
266 IF (l_tcnv_rec.khr_id IS NULL) OR
267 (l_tcnv_rec.khr_id = OKL_Api.G_MISS_NUM) THEN
268 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
269 ,p_msg_name => g_required_value
270 ,p_token1 => g_col_name_token
271 ,p_token1_value => 'KHR_ID');
272 RAISE OKL_API.G_EXCEPTION_ERROR;
273 END IF;
274
275 IF (l_tcnv_rec.Amount IS NULL) OR
276 (l_tcnv_rec.Amount = OKL_Api.G_MISS_NUM) OR
277 (l_tcnv_rec.Amount = 0) THEN
278 OKL_Api.SET_MESSAGE(p_app_name => 'OKC'
279 ,p_msg_name => g_required_value
280 ,p_token1 => g_col_name_token
281 ,p_token1_value => 'AMOUNT');
282 RAISE OKL_API.G_EXCEPTION_ERROR;
283 END IF;
284
285 /* Commented by Kanti for Bug Number 2335254
286
287
288 IF (l_tcnv_rec.TRX_NUMBER IS NULL) OR
289 (l_tcnv_rec.TRX_NUMBER = OKL_Api.G_MISS_CHAR) THEN
290 OKL_Api.SET_MESSAGE(p_app_name => 'OKL'
291 ,p_msg_name => g_required_value
292 ,p_token1 => g_col_name_token
293 ,p_token1_value => 'TRX_NUMBER');
294 RAISE OKL_API.G_EXCEPTION_ERROR;
295 END IF;
296
297 */
298
299
300 OPEN try_csr;
301 FETCH try_csr INTO l_try_id;
302
303 IF (try_csr%NOTFOUND) THEN
304 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
305 p_msg_name => 'OKL_MISC_TRX_NOT_FOUND');
306 CLOSE try_csr;
307 RAISE OKL_API.G_EXCEPTION_ERROR;
308 END IF;
309
310 CLOSE try_csr;
311
312 --Added by dpsingh for LE Uptake
313 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(l_tcnv_rec.khr_id) ;
314 IF l_legal_entity_id IS NOT NULL THEN
315 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
316 ELSE
317 OPEN get_contract_number(l_tcnv_rec.khr_id);
318 FETCH get_contract_number INTO l_cntrct_number;
319 CLOSE get_contract_number;
320 Okl_Api.set_message(p_app_name => g_app_name,
321 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
322 p_token1 => 'CONTRACT_NUMBER',
323 p_token1_value => l_cntrct_number);
324 RAISE OKL_API.G_EXCEPTION_ERROR;
325 END IF;
326
327 l_tcnv_rec.TRY_ID := l_try_id;
328 l_tcnv_rec.TCN_TYPE := 'MAE';
329 l_tcnv_rec.TSU_CODE := 'ENTERED';
330
331
332 -- Added by Santonyr on 22-Nov-2002. Multi-Currency Changes
333 -- Derive the currency conversion factors from Contracts table
334
335 -- Fetch the functional currency
336 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
337
338 -- Fetch the currency conversion factors if functional currency is not equal
339 -- to the transaction currency
340
341 IF l_functional_currency <> l_tcnv_rec.currency_code THEN
342
343 -- Fetch the currency conversion factors from Contracts
344 FOR curr_rec IN curr_csr(l_tcnv_rec.khr_id) LOOP
345 l_currency_conversion_type := curr_rec.currency_conversion_type;
346 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
347 l_currency_conversion_date := curr_rec.currency_conversion_date;
348 END LOOP;
349
350 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
351 -- conversion type is not 'USER'.
352
353 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
354 l_currency_conversion_date := l_tcnv_rec.date_transaction_occurred;
355 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
356 (p_from_curr_code => l_tcnv_rec.currency_code,
357 p_to_curr_code => l_functional_currency,
358 p_con_date => l_currency_conversion_date,
359 p_con_type => l_currency_conversion_type);
360
361 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
362
363 END IF; -- End IF for (l_functional_currency <> l_tcnv_rec.currency_code)
364
365 -- Populate the currency conversion factors
366
367 l_tcnv_rec.currency_conversion_type := l_currency_conversion_type;
368 l_tcnv_rec.currency_conversion_rate := l_currency_conversion_rate;
369 l_tcnv_rec.currency_conversion_date := l_currency_conversion_date;
370
371 -- Round the transaction amount
372
373 l_tcnv_rec.amount := okl_accounting_util.cross_currency_round_amount
374 (p_amount => p_tcnv_rec.amount,
375 p_currency_code => l_tcnv_rec.currency_code);
376
377
378 OKL_TRX_CONTRACTS_PUB.create_trx_contracts(p_api_version => l_api_version,
379 p_init_msg_list => p_init_msg_list,
380 x_return_status => l_return_status,
381 x_msg_count => x_msg_count,
382 x_msg_data => x_msg_data,
383 p_tcnv_rec => l_tcnv_rec,
384 x_tcnv_rec => x_tcnv_rec);
385
386 x_return_status := l_return_status;
387
388 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
389
390 EXCEPTION
391 WHEN OKL_API.G_EXCEPTION_ERROR THEN
392 x_return_status := OKL_API.HANDLE_EXCEPTIONS
393 (
394 l_api_name,
395 G_PKG_NAME,
396 'OKL_API.G_RET_STS_ERROR',
397 x_msg_count,
398 x_msg_data,
399 '_PVT'
400 );
401 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
402 x_return_status := OKL_API.HANDLE_EXCEPTIONS
403 (
404 l_api_name,
405 G_PKG_NAME,
406 'OKL_API.G_RET_STS_UNEXP_ERROR',
407 x_msg_count,
408 x_msg_data,
409 '_PVT'
410 );
411 WHEN OTHERS THEN
412 x_return_status := OKL_API.HANDLE_EXCEPTIONS
413 (
414 l_api_name,
415 G_PKG_NAME,
416 'OTHERS',
417 x_msg_count,
418 x_msg_data,
419 '_PVT'
420 );
421
422
423 END CREATE_TRX_CONTRACTS;
424
425
426
427 PROCEDURE create_trx_contracts(p_api_version IN NUMBER,
428 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
429 x_return_status OUT NOCOPY VARCHAR2,
430 x_msg_count OUT NOCOPY NUMBER,
431 x_msg_data OUT NOCOPY VARCHAR2,
432 p_tcnv_tbl IN tcnv_tbl_type,
433 x_tcnv_tbl OUT NOCOPY tcnv_tbl_type)
434 IS
435
436
437 l_api_version NUMBER := 1.0;
438 i NUMBER := 0;
439 l_try_id NUMBER := 0;
440
441
442
443 BEGIN
444
445 x_return_status := OKL_API.G_RET_STS_SUCCESS;
446
447 IF (p_tcnv_tbl.COUNT > 0) THEN
448
449 i := p_tcnv_tbl.FIRST;
450
451 LOOP
452
453 create_trx_contracts(p_api_version => l_api_version,
454 p_init_msg_list => p_init_msg_list,
455 x_return_status => x_return_status,
456 x_msg_count => x_msg_count,
457 x_msg_data => x_msg_data,
458 p_tcnv_rec => p_tcnv_tbl(i),
459 x_tcnv_rec => x_tcnv_tbl(i));
460
461 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
462 EXIT;
463 END IF;
464
465 EXIT WHEN (i = p_tcnv_tbl.LAST);
466 i := p_tcnv_tbl.NEXT(i);
467
468 END LOOP;
469
470 END IF;
471
472
473 EXCEPTION
474
475 WHEN OTHERS THEN
476 NULL;
477
478 END CREATE_TRX_CONTRACTS;
479
480
481
482 PROCEDURE create_trx_cntrct_lines(p_api_version IN NUMBER,
483 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
484 x_return_status OUT NOCOPY VARCHAR2,
485 x_msg_count OUT NOCOPY NUMBER,
486 x_msg_data OUT NOCOPY VARCHAR2,
487 p_tclv_rec IN tclv_rec_type,
488 x_tclv_rec OUT NOCOPY tclv_rec_type)
489 IS
490
491 l_api_version NUMBER := 1.0;
492
493 l_tclv_rec TCLV_REC_TYPE := p_tclv_rec;
494
495 BEGIN
496
497 x_return_status := OKL_API.G_RET_STS_SUCCESS;
498
499 IF (l_tclv_rec.AMOUNT IS NULL) OR
500 (l_tclv_rec.AMOUNT = OKL_Api.G_MISS_NUM) OR
501 (l_tclv_rec.AMOUNT = 0) THEN
502 Okl_Api.SET_MESSAGE(p_app_name => g_app_name
503 ,p_msg_name => g_required_value
504 ,p_token1 => g_col_name_token
505 ,p_token1_value => 'AMOUNT');
506 RAISE OKL_API.G_EXCEPTION_ERROR;
507 END IF;
508
509 l_tclv_rec.TCL_TYPE := 'MAE';
510
511 OKL_TRX_CONTRACTS_PUB.create_trx_cntrct_lines(p_api_version => l_api_version,
512 p_init_msg_list => p_init_msg_list,
513 x_return_status => x_return_status,
514 x_msg_count => x_msg_count,
515 x_msg_data => x_msg_data,
516 p_tclv_rec => l_tclv_rec,
517 x_tclv_rec => x_tclv_rec);
518 EXCEPTION
519
520 WHEN OKL_API.G_EXCEPTION_ERROR THEN
521 x_return_status := Okl_Api.G_RET_STS_ERROR;
522
523 END CREATE_TRX_CNTRCT_LINES;
524
525
526
527 PROCEDURE create_trx_cntrct_lines(p_api_version IN NUMBER,
528 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_msg_count OUT NOCOPY NUMBER,
531 x_msg_data OUT NOCOPY VARCHAR2,
532 p_tclv_tbl IN tclv_tbl_type,
533 x_tclv_tbl OUT NOCOPY tclv_tbl_type)
534 IS
535
536 l_api_version NUMBER := 1.0;
537 i NUMBER := 0;
538
539 BEGIN
540
541 IF (p_tclv_tbl.COUNT > 0) THEN
542
543 i := p_tclv_tbl.FIRST;
544
545 LOOP
546
547 create_trx_cntrct_lines(p_api_version => l_api_version,
548 p_init_msg_list => p_init_msg_list,
549 x_return_status => x_return_status,
550 x_msg_count => x_msg_count,
551 x_msg_data => x_msg_data,
552 p_tclv_rec => p_tclv_tbl(i),
553 x_tclv_rec => x_tclv_tbl(i));
554
555 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
556 EXIT;
557 END IF;
558
559 EXIT WHEN (i = p_tclv_tbl.LAST);
560 i := p_tclv_tbl.NEXT(i);
561
562 END LOOP;
563
564 END IF;
565
566 EXCEPTION
567
568 WHEN OTHERS THEN
569 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
570
571 END CREATE_TRX_CNTRCT_LINES;
572
573
574
575 PROCEDURE update_trx_contracts( p_api_version IN NUMBER
576 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
577 ,x_return_status OUT NOCOPY VARCHAR2
578 ,x_msg_count OUT NOCOPY NUMBER
579 ,x_msg_data OUT NOCOPY VARCHAR2
580 ,p_tcnv_rec IN tcnv_rec_type
581 ,p_tclv_tbl IN tclv_tbl_type
582 ,x_tcnv_rec OUT NOCOPY tcnv_rec_type
583 ,x_tclv_tbl OUT NOCOPY tclv_tbl_type)
584 IS
585 l_api_version NUMBER := 1.0;
586 l_return_status VARCHAR2(1);
587
588 -- Added by Santonyr for Multi-Currency
589
590 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
591
592 BEGIN
593
594 -- Added by Santonyr Round the transaction amount
595
596 l_tcnv_rec.amount := okl_accounting_util.cross_currency_round_amount
597 (p_amount => p_tcnv_rec.amount,
598 p_currency_code => l_tcnv_rec.currency_code);
599
600 Validate_Amount (p_tcnv_rec => l_tcnv_rec,
601 x_return_status => l_return_status);
602
603 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
604 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
605 p_msg_name => 'OKL_TRX_AMT_GT_LINE_AMT');
606 RAISE OKL_API.G_EXCEPTION_ERROR;
607 END IF;
608
609 OKL_TRX_CONTRACTS_PUB.update_trx_contracts( p_api_version => l_api_version
610 ,p_init_msg_list => p_init_msg_list
611 ,x_return_status => x_return_status
612 ,x_msg_count => x_msg_count
613 ,x_msg_data => x_msg_data
614 ,p_tcnv_rec => l_tcnv_rec
615 ,p_tclv_tbl => p_tclv_tbl
616 ,x_tcnv_rec => x_tcnv_rec
617 ,x_tclv_tbl => x_tclv_tbl );
618
619
620
621 END;
622
623
624 PROCEDURE update_trx_contracts(p_api_version IN NUMBER,
625 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
626 x_return_status OUT NOCOPY VARCHAR2,
627 x_msg_count OUT NOCOPY NUMBER,
628 x_msg_data OUT NOCOPY VARCHAR2,
629 p_tcnv_rec IN tcnv_rec_type,
630 x_tcnv_rec OUT NOCOPY tcnv_rec_type)
631 IS
632
633 CURSOR chk_csr(v_tcn_id NUMBER, v_source_table VARCHAR2) IS
634 SELECT NVL(COUNT(*),0)
635 FROM OKL_TRNS_ACC_DSTRS
636 WHERE source_id IN (SELECT id FROM OKL_TXL_CNTRCT_LNS
637 WHERE TCN_ID = v_tcn_id)
638 AND source_table = v_source_table;
639
640
641 CURSOR tcl_csr(v_tcn_id NUMBER) IS
642 SELECT ID
643 FROM OKL_TXL_CNTRCT_LNS
644 WHERE TCN_ID = v_tcn_id;
645
646
647 CURSOR tcn_csr(v_tcn_id NUMBER) IS
648 SELECT tsu_code
649 FROM OKL_TRX_CONTRACTS
650 WHERE id = v_tcn_id;
651
652
653 tcl_rec tcl_csr%ROWTYPE;
654 l_source_id_tbl OKL_REVERSAL_PUB.SOURCE_ID_TBL_TYPE;
655 l_tsu_code OKL_TRX_CONTRACTS.TSU_CODE%TYPE;
656 l_source_table OKL_TRNS_ACC_DSTRS.source_table%TYPE := 'OKL_TXL_CNTRCT_LNS';
657 l_acct_date DATE := SYSDATE;
658
659 l_total_dist NUMBER := 0;
660 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TRX_CONTRACTS';
661 l_api_version NUMBER := 1.0;
662 i NUMBER := 0;
663 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
664
665 -- Added by Santonyr for Multi-Currency
666
667 l_tcnv_rec tcnv_rec_type := p_tcnv_rec;
668
669
670
671 BEGIN
672
673 l_return_status := Okc_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 IF (l_return_status = Okc_Api.G_RET_STS_UNEXP_ERROR) THEN
681 RAISE Okc_Api.G_EXCEPTION_UNEXPECTED_ERROR;
682 ELSIF (l_return_status = Okc_Api.G_RET_STS_ERROR) THEN
683 RAISE Okc_Api.G_EXCEPTION_ERROR;
684 END IF;
685
686 -- Allow update only if Status is not cancelled.
687
688 OPEN tcn_csr(p_tcnv_rec.ID);
689 FETCH tcn_csr INTO l_tsu_code;
690 CLOSE tcn_csr;
691
692 IF (l_tsu_code = 'CANCELED') THEN
693
694 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
695 p_msg_name => 'OKL_TRX_CANCELED');
696 RAISE OKL_API.G_EXCEPTION_ERROR;
697
698 END IF;
699
700 -- Added by Santonyr Round the transaction amount
701
702 l_tcnv_rec.amount := okl_accounting_util.cross_currency_round_amount
703 (p_amount => p_tcnv_rec.amount,
704 p_currency_code => l_tcnv_rec.currency_code);
705
706 Validate_Amount (p_tcnv_rec => l_tcnv_rec,
707 x_return_status => l_return_status);
708
709 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
710 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
711 p_msg_name => 'OKL_TRX_AMT_GT_LINE_AMT');
712 RAISE OKL_API.G_EXCEPTION_ERROR;
713 END IF;
714
715
716 OKL_TRX_CONTRACTS_PUB.update_trx_contracts(p_api_version => l_api_version,
717 p_init_msg_list => p_init_msg_list,
718 x_return_status => l_return_status,
719 x_msg_count => x_msg_count,
720 x_msg_data => x_msg_data,
721 p_tcnv_rec => l_tcnv_rec,
722 x_tcnv_rec => x_tcnv_rec);
723
724
725
726 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
727
728 -- If transaction is being canceled, then reverse the accounting
729 -- But we need to make sure that Lines and Accounting actually exist
730
731 IF (p_tcnv_rec.TSU_CODE = 'CANCELED') THEN
732
733 OPEN chk_csr(p_tcnv_rec.ID,l_source_table);
734 FETCH chk_csr INTO l_total_dist;
735 CLOSE chk_csr;
736
737 IF (l_total_dist > 0) THEN
738
739 FOR tcl_rec IN tcl_csr(p_tcnv_rec.ID)
740 LOOP
741 i := i + 1;
742 l_source_id_tbl(i) := tcl_rec.ID;
743 END LOOP;
744
745 OKL_REVERSAL_PUB.REVERSE_ENTRIES(p_api_version => l_api_version,
746 p_init_msg_list => p_init_msg_list,
747 x_return_status => l_return_status,
748 x_msg_count => x_msg_count,
749 x_msg_data => x_msg_data,
750 p_source_table => l_source_table,
751 p_acct_date => l_acct_date,
752 p_source_id_tbl => l_source_id_tbl);
753
754 END IF;
755 END IF;
756
757 END IF;
758
759 x_return_status := l_return_status;
760
761 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
762
763 EXCEPTION
764
765 WHEN OKL_API.G_EXCEPTION_ERROR THEN
766 x_return_status := OKL_API.HANDLE_EXCEPTIONS
767 (
768 l_api_name,
769 G_PKG_NAME,
770 'OKL_API.G_RET_STS_ERROR',
771 x_msg_count,
772 x_msg_data,
773 '_PVT'
774 );
775 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
776 x_return_status := OKL_API.HANDLE_EXCEPTIONS
777 (
778 l_api_name,
779 G_PKG_NAME,
780 'OKL_API.G_RET_STS_UNEXP_ERROR',
781 x_msg_count,
782 x_msg_data,
783 '_PVT'
784 );
785 WHEN OTHERS THEN
786 x_return_status := OKL_API.HANDLE_EXCEPTIONS
787 (
788 l_api_name,
789 G_PKG_NAME,
790 'OTHERS',
791 x_msg_count,
792 x_msg_data,
793 '_PVT'
794 );
795
796 END UPDATE_TRX_CONTRACTS;
797
798
799
800 PROCEDURE update_trx_contracts(p_api_version IN NUMBER,
801 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_tcnv_tbl IN tcnv_tbl_type,
806 x_tcnv_tbl OUT NOCOPY tcnv_tbl_type)
807 IS
808
809 l_api_version NUMBER := 1.0;
810 i NUMBER := 0;
811 l_overall_Status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
812
813 BEGIN
814
815 IF (p_tcnv_tbl.COUNT > 0) THEN
816
817 i := p_tcnv_tbl.FIRST;
818
819 LOOP
820
821 update_trx_contracts(p_api_version => l_api_version,
822 p_init_msg_list => p_init_msg_list,
823 x_return_status => x_return_status,
824 x_msg_count => x_msg_count,
825 x_msg_data => x_msg_data,
826 p_tcnv_rec => p_tcnv_tbl(i),
827 x_tcnv_rec => x_tcnv_tbl(i));
828
829 IF (x_return_status <> OKL_Api.G_RET_STS_SUCCESS) THEN
830 EXIT;
831 END IF;
832
833 EXIT WHEN (i = p_tcnv_tbl.LAST);
834
835 i := p_tcnv_tbl.NEXT(i);
836
837 END LOOP;
838
839 END IF;
840
841 EXCEPTION
842
843 WHEN OTHERS THEN
844 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
845
846 END UPDATE_TRX_CONTRACTS;
847
848
849
850 PROCEDURE update_trx_cntrct_lines(p_api_version IN NUMBER,
851 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
852 x_return_status OUT NOCOPY VARCHAR2,
853 x_msg_count OUT NOCOPY NUMBER,
854 x_msg_data OUT NOCOPY VARCHAR2,
855 p_tclv_rec IN tclv_rec_type,
856 x_tclv_rec OUT NOCOPY tclv_rec_type)
857 IS
858
859 l_api_version NUMBER := 1.0;
860
861 BEGIN
862
863 IF (p_tclv_rec.AMOUNT IS NULL) OR
864 (p_tclv_rec.AMOUNT = OKL_Api.G_MISS_NUM) OR
865 (p_tclv_rec.AMOUNT = 0) THEN
866 OKL_Api.SET_MESSAGE(p_app_name => 'OKL'
867 ,p_msg_name => g_required_value
868 ,p_token1 => g_col_name_token
869 ,p_token1_value => 'AMOUNT');
870 x_return_status := OKL_Api.G_RET_STS_ERROR;
871 RAISE OKL_API.G_EXCEPTION_ERROR;
872 END IF;
873
874
875 OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines(p_api_version => l_api_version,
876 p_init_msg_list => p_init_msg_list,
877 x_return_status => x_return_status,
878 x_msg_count => x_msg_count,
879 x_msg_data => x_msg_data,
880 p_tclv_rec => p_tclv_rec,
881 x_tclv_rec => x_tclv_rec);
882
883 EXCEPTION
884 WHEN OKL_API.G_EXCEPTION_ERROR THEN
885 NULL;
886
887 END;
888
889
890 PROCEDURE update_trx_cntrct_lines(p_api_version IN NUMBER,
891 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
892 x_return_status OUT NOCOPY VARCHAR2,
893 x_msg_count OUT NOCOPY NUMBER,
894 x_msg_data OUT NOCOPY VARCHAR2,
895 p_tclv_tbl IN tclv_tbl_type,
896 x_tclv_tbl OUT NOCOPY tclv_tbl_type)
897 IS
898 l_api_version NUMBER := 1.0;
899
900 BEGIN
901
902 OKL_TRX_CONTRACTS_PUB.update_trx_cntrct_lines(p_api_version => l_api_version,
903 p_init_msg_list => p_init_msg_list,
904 x_return_status => x_return_status,
905 x_msg_count => x_msg_count,
906 x_msg_data => x_msg_data,
907 p_tclv_tbl => p_tclv_tbl,
908 x_tclv_tbl => x_tclv_tbl);
909
910
911
912 END;
913
914 PROCEDURE delete_trx_contracts(p_api_version IN NUMBER,
915 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
916 x_return_status OUT NOCOPY VARCHAR2,
917 x_msg_count OUT NOCOPY NUMBER,
918 x_msg_data OUT NOCOPY VARCHAR2,
919 p_tcnv_rec IN tcnv_rec_type)
920 IS
921
922 l_api_version NUMBER := 1.0;
923
924 BEGIN
925 OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(p_api_version => l_api_version,
926 p_init_msg_list => p_init_msg_list,
927 x_return_status => x_return_status,
928 x_msg_count => x_msg_count,
929 x_msg_data => x_msg_data,
930 p_tcnv_rec => p_tcnv_rec );
931
932
933 END;
934
935
936 PROCEDURE delete_trx_contracts(p_api_version IN NUMBER,
937 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
938 x_return_status OUT NOCOPY VARCHAR2,
939 x_msg_count OUT NOCOPY NUMBER,
940 x_msg_data OUT NOCOPY VARCHAR2,
941 p_tcnv_tbl IN tcnv_tbl_type)
942 IS
943
944 l_api_version NUMBER := 1.0;
945
946 BEGIN
947
948
949 OKL_TRX_CONTRACTS_PUB.delete_trx_contracts(p_api_version => l_api_version,
950 p_init_msg_list => p_init_msg_list,
951 x_return_status => x_return_status,
952 x_msg_count => x_msg_count,
953 x_msg_data => x_msg_data,
954 p_tcnv_tbl => p_tcnv_tbl );
955
956
957 END;
958
959
960 PROCEDURE delete_trx_cntrct_lines(p_api_version IN NUMBER,
961 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
962 x_return_status OUT NOCOPY VARCHAR2,
963 x_msg_count OUT NOCOPY NUMBER,
964 x_msg_data OUT NOCOPY VARCHAR2,
965 p_tclv_rec IN tclv_rec_type)
966 IS
967 l_api_version NUMBER := 1.0;
968
969 BEGIN
970
971 OKL_TRX_CONTRACTS_PUB.delete_trx_cntrct_lines(p_api_version => l_api_version,
972 p_init_msg_list => p_init_msg_list,
973 x_return_status => x_return_status,
974 x_msg_count => x_msg_count,
975 x_msg_data => x_msg_data,
976 p_tclv_rec => p_tclv_rec);
977
978
979
980 END;
981
982
983 PROCEDURE delete_trx_cntrct_lines(p_api_version IN NUMBER,
984 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
985 x_return_status OUT NOCOPY VARCHAR2,
986 x_msg_count OUT NOCOPY NUMBER,
987 x_msg_data OUT NOCOPY VARCHAR2,
988 p_tclv_tbl IN tclv_tbl_type)
989 IS
990 l_api_version NUMBER := 1.0;
991
992 BEGIN
993
994 OKL_TRX_CONTRACTS_PUB.delete_trx_cntrct_lines(p_api_version => l_api_version,
995 p_init_msg_list => p_init_msg_list,
996 x_return_status => x_return_status,
997 x_msg_count => x_msg_count,
998 x_msg_data => x_msg_data,
999 p_tclv_tbl => p_tclv_tbl );
1000
1001 END;
1002
1003
1004 END OKL_TRANS_CONTRACTS_PVT;