[Home] [Help]
PACKAGE BODY: APPS.OKL_TRANSACTION_PVT
Source
1 PACKAGE BODY OKL_TRANSACTION_PVT AS
2 /* $Header: OKLRTXNB.pls 120.12 2010/07/01 03:52:04 rpillay ship $*/
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 -- Global Variables
9 G_INIT_NUMBER NUMBER := -9999;
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_TRANSACTION_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13
14 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - Start
15 G_REBOOK_TRX CONSTANT VARCHAR(30) := 'REBOOK';
16 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - End
17
18 -- subtype tcnv_rec_type IS OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
19
20 /*
21 -- mvasudev, 08/17/2004
22 -- Added Constants to enable Business Event
23 */
24 G_WF_EVT_KHR_SPLIT_REQ CONSTANT VARCHAR2(60) := 'oracle.apps.okl.la.lease_contract.split_contract_requested';
25
26 G_WF_ITM_CONTRACT_PROCESS CONSTANT VARCHAR2(20) := 'CONTRACT_PROCESS';
27 G_WF_ITM_SRC_CONTRACT_ID CONSTANT VARCHAR2(20) := 'SOURCE_CONTRACT_ID';
28 G_WF_ITM_DEST_CONTRACT_ID CONSTANT VARCHAR2(25) := 'DESTINATION_CONTRACT_ID';
29 G_WF_ITM_TRX_DATE CONSTANT VARCHAR2(20) := 'TRANSACTION_DATE';
30
31 G_KHR_PROCESS_SPLIT_CONTRACT CONSTANT VARCHAR2(14) := Okl_Lla_Util_Pvt.G_KHR_PROCESS_SPLIT_CONTRACT;
32
33 ------------------------------------------------------------------------------
34 -- PROCEDURE Report_Error
35 -- It is a generalized routine to display error on Concurrent Manager Log file
36 -- Calls:
37 -- Called by:
38 ------------------------------------------------------------------------------
39 --Added by dpsingh for LE uptake
40 CURSOR contract_num_csr (p_ctr_id1 NUMBER) IS
41 SELECT contract_number
42 FROM OKC_K_HEADERS_B
43 WHERE id = p_ctr_id1;
44
45 PROCEDURE Report_Error(
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2
48 ) IS
49
50 x_msg_index_out NUMBER;
51 x_msg_out VARCHAR2(2000);
52
53 BEGIN
54
55 okl_api.end_activity(
56 X_msg_count => x_msg_count,
57 X_msg_data => x_msg_data
58 );
59
60 FOR i IN 1..x_msg_count
61 LOOP
62 FND_MSG_PUB.GET(
63 p_msg_index => i,
64 p_encoded => FND_API.G_FALSE,
65 p_data => x_msg_data,
66 p_msg_index_out => x_msg_index_out
67 );
68
69 END LOOP;
70 RETURN;
71 EXCEPTION
72 WHEN OTHERS THEN
73 NULL;
74 END Report_Error;
75
76 ------------------------------------------------------------------------------
77 -- PROCEDURE validate_rebook_reason
78 --
79 -- This procedure validate rebook reason code
80 --
81 -- Calls:
82 -- Called By:
83 ------------------------------------------------------------------------------
84 PROCEDURE validate_rebook_reason(
85 x_return_status OUT NOCOPY VARCHAR2,
86 x_msg_count OUT NOCOPY NUMBER,
87 x_msg_data OUT NOCOPY VARCHAR2,
88 p_rebook_reason_code IN VARCHAR2
89 ) IS
90
91 l_proc_name VARCHAR2(35) := 'VALIDATE_REBOOK_REASON';
92 l_dummy VARCHAR2(1);
93
94 CURSOR rebook_csr (p_rebook_reason_code VARCHAR2) IS
95 SELECT 'X'
96 FROM FND_LOOKUPS
97 WHERE lookup_type = 'OKL_REBOOK_REASON'
98 AND lookup_code = p_rebook_reason_code;
99
100 rebook_failed EXCEPTION;
101
102 BEGIN
103 IF (G_DEBUG_ENABLED = 'Y') THEN
104 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
105 END IF;
106 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
107 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
108 END IF;
109 x_return_status := OKC_API.G_RET_STS_SUCCESS;
110
111 OPEN rebook_csr(p_rebook_reason_code);
112 FETCH rebook_csr INTO l_dummy;
113 IF rebook_csr%NOTFOUND THEN
114 RAISE rebook_failed;
115 END IF;
116 CLOSE rebook_csr;
117
118 RETURN;
119
120 EXCEPTION
121 WHEN rebook_failed THEN
122 okl_api.set_message(
123 G_APP_NAME,
124 G_INVALID_VALUE,
125 'COL_NAME',
126 'REBOOK REASON'
127 );
128 x_return_status := OKC_API.G_RET_STS_ERROR;
129
130 END validate_rebook_reason;
131
132 ------------------------------------------------------------------------------
133 -- PROCEDURE populate_transaction_rec
134 --
135 -- This procedure populate transaction records, tcnv_rec
136 --
137 -- Calls:
138 -- Called By:
139 ------------------------------------------------------------------------------
140 PROCEDURE populate_transaction_rec(
141 x_return_status OUT NOCOPY VARCHAR2,
142 x_msg_count OUT NOCOPY NUMBER,
143 x_msg_data OUT NOCOPY VARCHAR2,
144 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
145 p_new_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
146 p_reason_code IN VARCHAR2,
147 p_description IN VARCHAR2,
148 p_trx_date IN DATE,
149 p_trx_type IN VARCHAR2,
150 x_tcnv_rec OUT NOCOPY tcnv_rec_type
151 ) IS
152 l_proc_name VARCHAR2(35) := 'POPULATE_TRANSACTION_REC';
153 l_try_id NUMBER;
154 l_id NUMBER;
155 l_currency_code OKL_K_HEADERS_FULL_V.CURRENCY_CODE%TYPE;
156 l_org_id OKL_K_HEADERS_FULL_V.AUTHORING_ORG_ID%TYPE;
157
158 CURSOR con_header_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
159 SELECT currency_code,
160 authoring_org_id
161 FROM okl_k_headers_full_v
162 WHERE id = p_chr_id;
163
164 CURSOR try_csr(p_trx_type VARCHAR2) IS
165 SELECT id
166 FROM okl_trx_types_tl
167 WHERE LANGUAGE = 'US'
168 AND name = DECODE(p_trx_type,'REBOOK','Rebook',
169 'SPLIT', 'Split Contract',
170 'Error');
171 populate_failed EXCEPTION;
172
173 --Added by dpsingh for LE uptake
174 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
175 l_legal_entity_id NUMBER;
176
177 BEGIN
178 IF (G_DEBUG_ENABLED = 'Y') THEN
179 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
180 END IF;
181 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
182 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
183 END IF;
184 x_return_status := OKC_API.G_RET_STS_SUCCESS;
185
186 OPEN con_header_csr(p_chr_id);
187 FETCH con_header_csr INTO l_currency_code,
188 l_org_id;
189 IF con_header_csr%NOTFOUND THEN
190 okl_api.set_message(
191 G_APP_NAME,
192 G_LLA_CHR_ID
193 );
194 RAISE populate_failed;
195 END IF;
196
197 CLOSE con_header_csr;
198
199 /* Not required as TAPI is generating TRX_NUMBER
200
201 SELECT okl_txn_number_s.nextval
202 INTO l_id
203 FROM dual;
204
205 x_tcnv_rec.trx_number := l_id;
206 */
207
208 IF (p_trx_type = 'REBOOK') THEN
209 x_tcnv_rec.rbr_code := p_reason_code;
210 END IF;
211
212 OPEN try_csr (p_trx_type);
213 FETCH try_csr INTO l_try_id;
214 IF try_csr%NOTFOUND THEN
215 okl_api.set_message(
216 G_APP_NAME,
217 G_LLA_NO_TRY,
218 'TRX_TYPE',
219 p_trx_type
220 );
221 RAISE populate_failed;
222 END IF;
223
224 CLOSE try_csr;
225 x_tcnv_rec.try_id := l_try_id;
226
227 x_tcnv_rec.tsu_code := 'ENTERED';
228 x_tcnv_rec.description := p_description;
229
230 IF (p_trx_type = 'REBOOK') THEN
231 x_tcnv_rec.tcn_type := 'TRBK';
232 ELSE
233 x_tcnv_rec.tcn_type := 'SPLC';
234 END IF;
235
236 x_tcnv_rec.khr_id := p_chr_id;
237 x_tcnv_rec.khr_id_old := p_chr_id;
238 x_tcnv_rec.khr_id_new := p_new_chr_id;
239 x_tcnv_rec.currency_code := l_currency_code;
240 x_tcnv_rec.date_transaction_occurred := p_trx_date;
241 x_tcnv_rec.org_id := l_org_id;
242 --Added by dpsingh for LE Uptake
243 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_chr_id) ;
244 IF l_legal_entity_id IS NOT NULL THEN
245 x_tcnv_rec.legal_entity_id := l_legal_entity_id;
246 ELSE
247 -- get the contract number
248 OPEN contract_num_csr(p_chr_id);
249 FETCH contract_num_csr INTO l_cntrct_number;
250 CLOSE contract_num_csr;
251 Okl_Api.set_message(p_app_name => g_app_name,
252 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
253 p_token1 => 'CONTRACT_NUMBER',
254 p_token1_value => l_cntrct_number);
255 RAISE OKL_API.G_EXCEPTION_ERROR;
256 END IF;
257 RETURN;
258
259
260 EXCEPTION
261 WHEN populate_failed THEN
262
263 IF try_csr%ISOPEN THEN
264 CLOSE try_csr;
265 END IF;
266
267 IF con_header_csr%ISOPEN THEN
268 CLOSE con_header_csr;
269 END IF;
270
271 x_return_status := OKC_API.G_RET_STS_ERROR;
272
273 WHEN OTHERS THEN
274 okl_api.set_message(
275 G_APP_NAME,
276 G_UNEXPECTED_ERROR,
277 'OKL_SQLCODE',
278 SQLCODE,
279 'OKL_SQLERRM',
280 SQLERRM || ': '||G_PKG_NAME||'.'||l_proc_name
281 );
282 x_return_status := OKC_API.G_RET_STS_ERROR;
283
284 END populate_transaction_rec;
285
286 ------------------------------------------------------------------------------
287 -- PROCEDURE update_trx_status
288 --
289 -- This procedure updates Transaction Status for a transaction
290 --
291 -- Calls:
292 -- Called By:
293 ------------------------------------------------------------------------------
294 PROCEDURE update_trx_status(
295 p_api_version IN NUMBER,
296 p_init_msg_list IN VARCHAR2,
297 x_return_status OUT NOCOPY VARCHAR2,
298 x_msg_count OUT NOCOPY NUMBER,
299 x_msg_data OUT NOCOPY VARCHAR2,
300 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
301 p_status IN VARCHAR2,
302 x_tcnv_rec OUT NOCOPY tcnv_rec_type
303 ) IS
304
305 l_api_name VARCHAR2(35) := 'update_trx_status';
306 l_proc_name VARCHAR2(35) := 'UPDATE_TRX_STATUS';
307 l_api_version CONSTANT NUMBER := 1;
308
309 -- Bug# 9851889
310 -- Modified cursor to pick up Rebook and Re-lease Contract transactions
311 CURSOR trx_csr(p_chr_id_new OKC_K_HEADERS_V.ID%TYPE) IS
312 SELECT trx.id, trx.representation_type --sechawla 01-oct-09 8967918 : added representation type
313 FROM okl_trx_contracts trx,
314 okl_trx_types_tl try
315 WHERE trx.khr_id_new = p_chr_id_new
316 AND try.id = trx.try_id
317 AND try.language = 'US'
318 AND try.name in ('Rebook','Release')
319 --rkuttiya added for 12.1.1 Multi GAAP
320 --AND representation_type = 'PRIMARY'; --sechawla 01-oct-09 8967918
321 --When this API is called from upgrade script OKLTXRBKUG.sql, to cancel the
322 --in process rebook transactions, MGAAP upgrade to update the representation_type
323 --has not happened as yet. Hence representation_type is null at that point, which
324 --cause upgrade to fail. Moreover, this condition is not needed. At the time of upgrade 11i > r12
325 --there will be only one transaction for Primary, there won't be any secondary rep transactions
326 AND trx.tcn_type IN ('TRBK','MAE'); --sechawla 01-oct-09 8967918 : added this condition to pick only main rebook
327 --rebook transaction. Tax rebook transaction (PRBK) which is created once rebook
328 --copy is validated, need not be picked here, as that one is handled separately
329 --inside abandon_revisions procedure
330 --
331
332 l_tcnv_rec tcnv_rec_type;
333 lx_tcnv_rec tcnv_rec_type; --sechawla 01-oct-09 8967918
334
335 BEGIN
336 IF (G_DEBUG_ENABLED = 'Y') THEN
337 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
338 END IF;
339
340 x_return_status := OKC_API.G_RET_STS_SUCCESS;
341 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
342 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
343 END IF;
344 -- call START_ACTIVITY to create savepoint, check compatibility
345 -- and initialize message list
346 x_return_status := OKC_API.START_ACTIVITY(
347 p_api_name => l_api_name,
348 p_pkg_name => G_PKG_NAME,
349 p_init_msg_list => p_init_msg_list,
350 l_api_version => l_api_version,
351 p_api_version => p_api_version,
352 p_api_type => G_API_TYPE,
353 x_return_status => x_return_status);
354
355 -- check if activity started successfully
356 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
358 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
359 RAISE OKC_API.G_EXCEPTION_ERROR;
360 END IF;
361
362 FOR trx_rec IN trx_csr(p_chr_id)
363 LOOP
364 l_tcnv_rec.id := trx_rec.id;
365 l_tcnv_rec.tsu_code := p_status; --CANCELED
366 --END LOOP; --sechawla 01-oct-09 8967918 : moved update within the loop
367
368 Okl_Trx_Contracts_Pub.update_trx_contracts(
369 p_api_version => p_api_version,
370 p_init_msg_list => p_init_msg_list,
371 x_return_status => x_return_status,
372 x_msg_count => x_msg_count,
373 x_msg_data => x_msg_data,
374 p_tcnv_rec => l_tcnv_rec,
375 x_tcnv_rec => lx_tcnv_rec
376 );
377
378 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
379 RAISE Okc_Api.G_EXCEPTION_ERROR;
380 END IF;
381
382 IF (trx_rec.representation_type IS NULL --upgrade case, only 1 record will exist
383 OR
384 trx_rec.representation_type = 'PRIMARY') --UI case
385 THEN
386 x_tcnv_rec := lx_tcnv_rec; --need to return the record for primary only
387 END IF;
388
389 END LOOP; --sechawla 01-oct-09 8967918
390
391 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
392 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
393 x_msg_data => x_msg_data);
394
395 EXCEPTION
396 WHEN OKC_API.G_EXCEPTION_ERROR THEN
397 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
398 p_api_name => l_api_name,
399 p_pkg_name => G_PKG_NAME,
400 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
401 x_msg_count => x_msg_count,
402 x_msg_data => x_msg_data,
403 p_api_type => G_API_TYPE);
404
405 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
406 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
407 p_api_name => l_api_name,
408 p_pkg_name => G_PKG_NAME,
409 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
410 x_msg_count => x_msg_count,
411 x_msg_data => x_msg_data,
412 p_api_type => G_API_TYPE);
413
414 WHEN OTHERS THEN
415 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
416 p_api_name => l_api_name,
417 p_pkg_name => G_PKG_NAME,
418 p_exc_name => 'OTHERS',
419 x_msg_count => x_msg_count,
420 x_msg_data => x_msg_data,
421 p_api_type => G_API_TYPE);
422 END update_trx_status;
423
424 ------------------------------------------------------------------------------
425 -- PROCEDURE check_contract_securitized
426 --
427 -- This procedure checks whether the contract is securitized and returns x_return_status
428 -- 'F' - if securitized
429 -- 'S' - if it is not securitized
430 --
431 -- Calls:
432 -- Called By:
433 ------------------------------------------------------------------------------
434 PROCEDURE check_contract_securitized(
435 p_api_version IN NUMBER,
436 p_init_msg_list IN VARCHAR2,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_count OUT NOCOPY NUMBER,
439 x_msg_data OUT NOCOPY VARCHAR2,
440 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
441 p_trx_date IN DATE
442 ) IS
443
444 l_api_name VARCHAR2(35) := 'check_contract_securitized';
445 l_proc_name VARCHAR2(35) := 'CHECK_CONTRACT_SECURITIZED';
446 l_api_version CONSTANT NUMBER := 1;
447
448 l_contract_secu VARCHAR2(1);
449 l_inv_agmt_chr_id_tbl inv_agmt_chr_id_tbl_type;
450 BEGIN
451 IF (G_DEBUG_ENABLED = 'Y') THEN
452 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
453 END IF;
454
455 x_return_status := OKL_API.G_RET_STS_SUCCESS;
456 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
457 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
458 END IF;
459
460 okl_securitization_pvt.check_khr_securitized(
461 p_api_version => 1.0,
462 p_init_msg_list => OKC_API.G_FALSE,
463 x_return_status => x_return_status,
464 x_msg_count => x_msg_count,
465 x_msg_data => x_msg_data,
466 p_khr_id => p_chr_id,
467 p_effective_date => p_trx_date,
468 x_value => l_contract_secu,
469 x_inv_agmt_chr_id_tbl => l_inv_agmt_chr_id_tbl
470 );
471
472 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
473 IF (l_contract_secu = OKL_API.G_TRUE) THEN
474 okl_api.set_message(
475 G_APP_NAME,
476 G_LLA_SECU_ERROR
477 );
478 x_return_status := OKL_API.G_RET_STS_ERROR;
479 END IF;
480 END IF;
481
482 RETURN;
483
484 EXCEPTION
485 WHEN OTHERS THEN
486 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
487 p_api_name => l_api_name,
488 p_pkg_name => G_PKG_NAME,
489 p_exc_name => 'OTHERS',
490 x_msg_count => x_msg_count,
491 x_msg_data => x_msg_data,
492 p_api_type => G_API_TYPE);
493
494 END check_contract_securitized;
495
496 ------------------------------------------------------------------------------
497 -- PROCEDURE check_contract_securitized
498 --
499 -- This procedure checks whether the asset residual value is securitized
500 -- The value in x_return_status will be
501 -- 'F' - if securitized
502 -- 'S' - if it is not securitized
503 --
504 -- Calls:
505 -- Called By:
506 ------------------------------------------------------------------------------
507 PROCEDURE check_contract_securitized(
508 p_api_version IN NUMBER,
509 p_init_msg_list IN VARCHAR2,
510 x_return_status OUT NOCOPY VARCHAR2,
511 x_msg_count OUT NOCOPY NUMBER,
512 x_msg_data OUT NOCOPY VARCHAR2,
513 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
514 p_cle_id IN OKC_K_LINES_V.ID%TYPE,
515 p_stream_type_class IN okl_strm_type_b.stream_type_subclass%TYPE,
516 p_trx_date IN DATE
517 ) IS
518
519 l_api_name VARCHAR2(35) := 'check_contract_securitized';
520 l_proc_name VARCHAR2(35) := 'CHECK_CONTRACT_SECURITIZED';
521 l_api_version CONSTANT NUMBER := 1;
522
523 l_asset_secu VARCHAR2(1);
524 l_inv_agmt_chr_id_tbl inv_agmt_chr_id_tbl_type;
525
526 BEGIN
527 IF (G_DEBUG_ENABLED = 'Y') THEN
528 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
529 END IF;
530
531 x_return_status := OKL_API.G_RET_STS_SUCCESS;
532 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
533 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
534 END IF;
535
536 okl_securitization_pvt.check_kle_securitized(
537 p_api_version => p_api_version,
538 p_init_msg_list => OKL_API.G_FALSE,
539 x_return_status => x_return_status,
540 x_msg_count => x_msg_count,
541 x_msg_data => x_msg_data,
542 p_kle_id => p_cle_id,
543 p_effective_date => p_trx_date,
544 p_stream_type_subclass => p_stream_type_class,
545 x_value => l_asset_secu,
546 x_inv_agmt_chr_id_tbl => l_inv_agmt_chr_id_tbl
547 );
548
549 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
550 IF (l_asset_secu = OKL_API.G_TRUE) THEN
551 okl_api.set_message(
552 G_APP_NAME,
553 G_LLA_SECU_ERROR
554 );
555 x_return_status := OKL_API.G_RET_STS_ERROR;
556 END IF;
557 END IF;
558
559 RETURN;
560
561 EXCEPTION
562 WHEN OTHERS THEN
563 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
564 p_api_name => l_api_name,
565 p_pkg_name => G_PKG_NAME,
566 p_exc_name => 'OTHERS',
567 x_msg_count => x_msg_count,
568 x_msg_data => x_msg_data,
569 p_api_type => G_API_TYPE);
570
571 END check_contract_securitized;
572
573 ------------------------------------------------------------------------------
574 -- PROCEDURE create_transaction
575 --
576 -- This procedure creates Transaction as a first step to REBOOKing
577 --
578 -- Calls:
579 -- Called By:
580 ------------------------------------------------------------------------------
581 PROCEDURE create_transaction(
582 p_api_version IN NUMBER,
583 p_init_msg_list IN VARCHAR2,
584 x_return_status OUT NOCOPY VARCHAR2,
585 x_msg_count OUT NOCOPY NUMBER,
586 x_msg_data OUT NOCOPY VARCHAR2,
587 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
588 p_new_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
589 p_reason_code IN VARCHAR2,
590 p_description IN VARCHAR2,
591 p_trx_date IN DATE,
592 p_trx_type IN VARCHAR2, -- 'REBOOK' or 'SPLIT'
593 x_tcnv_rec OUT NOCOPY tcnv_rec_type
594 ) IS
595
596 l_api_name VARCHAR2(35) := 'create_transaction';
597 l_proc_name VARCHAR2(35) := 'CREATE_TRANSACTION';
598 l_api_version CONSTANT NUMBER := 1;
599
600 l_tcnv_rec tcnv_rec_type;
601 l_out_tcnv_rec tcnv_rec_type;
602
603 CURSOR con_eff_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
604 SELECT start_date,
605 end_date
606 FROM okc_k_headers_b
607 WHERE id = p_chr_id;
608
609 CURSOR check_date_csr (p_start_date DATE,
610 p_end_date DATE,
611 p_trx_date DATE) IS
612 SELECT 'Y'
613 FROM DUAL
614 WHERE p_trx_date BETWEEN p_start_date AND p_end_date;
615
616 l_con_start_date DATE;
617 l_con_end_date DATE;
618 l_date_valid VARCHAR2(1);
619
620 --akrangan start
621 --CURSOR FOR CHECKING WHETHER A ASSET LOCATION CHANGE TRANSACTION IS
622 --CREATED FOR THE ASSETS USED IN THE CONTRACT
623 CURSOR chk_asst_loc_khr_csr(p_chr_id NUMBER)
624 IS
625 SELECT 'N'
626 FROM okc_k_lines_b okc ,
627 okc_line_styles_b ols,
628 okl_trx_assets trx ,
629 okl_txl_itm_insts itm
630 WHERE okc.dnz_chr_id = p_chr_id
631 AND okc.id = itm.kle_id
632 AND okc.lse_id = ols.id
633 AND ols.lty_code = 'INST_ITEM'
634 AND trx.tsu_code = 'ENTERED'
635 AND trx.tas_type = 'ALG'
636 AND trx.id = itm.tas_id ;
637
638 l_rebook_allowed VARCHAR2(1) := 'Y';
639 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
640 --akrangan end
641 /*
642 -- mvasudev, 08/30/2004
643 -- Added PROCEDURE to enable Business Event
644 */
645 PROCEDURE raise_business_event(x_return_status OUT NOCOPY VARCHAR2
646 )
647 IS
648 l_parameter_list wf_parameter_list_t;
649 BEGIN
650
651 IF (p_trx_type = 'SPLIT') THEN
652
653 wf_event.AddParameterToList(G_WF_ITM_SRC_CONTRACT_ID,p_chr_id,l_parameter_list);
654 wf_event.AddParameterToList(G_WF_ITM_DEST_CONTRACT_ID,p_new_chr_id,l_parameter_list);
655 wf_event.AddParameterToList(G_WF_ITM_TRX_DATE,fnd_date.date_to_canonical(p_trx_date),l_parameter_list);
656 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS,G_KHR_PROCESS_SPLIT_CONTRACT,l_parameter_list);
657
658 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
659 p_init_msg_list => p_init_msg_list,
660 x_return_status => x_return_status,
661 x_msg_count => x_msg_count,
662 x_msg_data => x_msg_data,
663 p_event_name => G_WF_EVT_KHR_SPLIT_REQ,
664 p_parameters => l_parameter_list);
665
666
667 END IF;
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
672 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
673 END raise_business_event;
674
675
676 /*
677 -- mvasudev, 08/30/2004
678 -- END, PROCEDURE to enable Business Event
679 */
680
681
682
683 BEGIN -- main process begins here
684
685 x_return_status := OKC_API.G_RET_STS_SUCCESS;
686 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
687 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
688 END IF;
689 -- call START_ACTIVITY to create savepoint, check compatibility
690 -- and initialize message list
691 x_return_status := OKC_API.START_ACTIVITY(
692 p_api_name => l_api_name,
693 p_pkg_name => G_PKG_NAME,
694 p_init_msg_list => p_init_msg_list,
695 l_api_version => l_api_version,
696 p_api_version => p_api_version,
697 p_api_type => G_API_TYPE,
698 x_return_status => x_return_status);
699
700 -- check if activity started successfully
701 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
702 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
703 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
704 RAISE OKC_API.G_EXCEPTION_ERROR;
705 END IF;
706
707 IF (p_trx_type NOT IN ('REBOOK','SPLIT')) THEN
708 okl_api.set_message(
709 G_APP_NAME,
710 G_LLA_INVALID_TRX_TYPE,
711 'TRX_TYPE',
712 p_trx_type
713 );
714 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
715 END IF;
716
717 IF (p_trx_type = 'SPLIT') THEN
718 --
719 -- Check for securitization during split contract
720 --
721 check_contract_securitized(
722 p_api_version => p_api_version,
723 p_init_msg_list => OKL_API.G_FALSE,
724 x_return_status => x_return_status,
725 x_msg_count => x_msg_count,
726 x_msg_data => x_msg_data,
727 p_chr_id => p_chr_id,
728 p_trx_date => p_trx_date
729 );
730
731 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
732 RAISE OKC_API.G_EXCEPTION_ERROR;
733 END IF;
734 END IF;
735
736 IF (p_trx_type = 'REBOOK') THEN
737 --akrangan start
738 --If there are any Asset Location Transaction is
739 --in progress for asset lines in the contract
740 --Rebook will not allowed in that case.
741 OPEN chk_asst_loc_khr_csr(p_chr_id);
742 FETCH chk_asst_loc_khr_csr INTO l_rebook_allowed;
743 CLOSE chk_asst_loc_khr_csr;
744 IF l_rebook_allowed = 'N' THEN
745
746 OPEN contract_num_csr(p_chr_id);
747 FETCH contract_num_csr INTO l_contract_number;
748 CLOSE contract_num_csr;
749
750 okl_api.set_message(
751 G_APP_NAME,
752 'OKL_TX_AST_LOC_RBK_NOTALLWED',
753 'CONTRACT_NUMBER',
754 l_contract_number
755 );
756
757 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
758 END IF;
759 --akrangan end
760 validate_rebook_reason(
761 x_return_status => x_return_status,
762 x_msg_count => x_msg_count,
763 x_msg_data => x_msg_data,
764 p_rebook_reason_code => p_reason_code
765 );
766
767 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
768 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
769 END IF;
770 END IF;
771
772 --
773 -- Check for Transaction Date, it is mandetory
774 --
775 IF (p_trx_date IS NULL) THEN
776 okl_api.set_message(
777 G_APP_NAME,
778 G_LLA_MISSING_TRX_DATE
779 );
780 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
781 END IF;
782
783 --
784 -- Transaction date should be within contract effectivity
785 -- Bug# 2504598
786 --
787 OPEN con_eff_csr (p_chr_id);
788 FETCH con_eff_csr INTO l_con_start_date,
789 l_con_end_date;
790 CLOSE con_eff_csr;
791
792 l_date_valid := 'N';
793 OPEN check_date_csr (l_con_start_date,
794 l_con_end_date,
795 p_trx_date);
796
797 FETCH check_date_csr INTO l_date_valid;
798 CLOSE check_date_csr;
799
800 IF (l_date_valid = 'N') THEN
801 okl_api.set_message(
802 G_APP_NAME,
803 G_LLA_WRONG_TRX_DATE,
804 'START_DATE',
805 l_con_start_date,
806 'END_DATE',
807 l_con_end_date
808 );
809 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
810 END IF;
811
812 /* Bug 4766555 : Code commented here and moved to
813 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - Start
814 IF (p_trx_type = G_REBOOK_TRX) THEN
815 -- check if the rebook transaction is allowed for the contract for this rebook date
816 OKL_K_RATE_PARAMS_PVT.check_rebook_allowed(
817 p_api_version => p_api_version
818 , p_init_msg_list => OKL_API.G_FALSE
819 , x_return_status => x_return_status
820 , x_msg_count => x_msg_count
821 , x_msg_data => x_msg_data
822 , p_chr_id => p_chr_id
823 , p_rebook_date => p_trx_date);
824 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
825 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
826 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
827 RAISE OKC_API.G_EXCEPTION_ERROR;
828 END IF;
829 END IF; -- end of check for rebook transaction
830 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - End
831 */
832
833 populate_transaction_rec(
834 x_return_status => x_return_status,
835 x_msg_count => x_msg_count,
836 x_msg_data => x_msg_data,
837 p_chr_id => p_chr_id,
838 p_new_chr_id => p_new_chr_id,
839 p_reason_code => p_reason_code,
840 p_description => p_description,
841 p_trx_date => p_trx_date,
842 p_trx_type => p_trx_type,
843 x_tcnv_rec => l_tcnv_rec
844 );
845
846 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
847 RAISE OKC_API.G_EXCEPTION_ERROR;
848 END IF;
849
850 -- Create Transaction Header only
851 okl_trx_contracts_pub.create_trx_contracts(
852 p_api_version => 1.0,
853 p_init_msg_list => p_init_msg_list,
854 x_return_status => x_return_status,
855 x_msg_count => x_msg_count,
856 x_msg_data => x_msg_data,
857 p_tcnv_rec => l_tcnv_rec,
858 x_tcnv_rec => l_out_tcnv_rec
859 );
860
861 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
862 RAISE OKC_API.G_EXCEPTION_ERROR;
863 END IF;
864
865 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - Start
866 -- apaul 4766555. Code moved here.
867 IF (p_trx_type = G_REBOOK_TRX) THEN
868 -- check if the rebook transaction is allowed for the contract for this rebook date
869 OKL_K_RATE_PARAMS_PVT.check_rebook_allowed(
870 p_api_version => p_api_version
871 , p_init_msg_list => OKL_API.G_FALSE
872 , x_return_status => x_return_status
873 , x_msg_count => x_msg_count
874 , x_msg_data => x_msg_data
875 , p_chr_id => p_chr_id
876 , p_rebook_date => p_trx_date);
877 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
878 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
879 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
880 RAISE OKC_API.G_EXCEPTION_ERROR;
881 END IF;
882 END IF; -- end of check for rebook transaction
883 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - End
884
885 x_tcnv_rec := l_out_tcnv_rec;
886
887 /*
888 -- mvasudev, 08/30/2004
889 -- Code change to enable Business Event
890 */
891 raise_business_event(x_return_status => x_return_status);
892 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
893 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
894 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
895 RAISE OKL_API.G_EXCEPTION_ERROR;
896 END IF;
897
898 /*
899 -- mvasudev, 08/30/2004
900 -- END, Code change to enable Business Event
901 */
902
903 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
904 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
905 x_msg_data => x_msg_data);
906
907 EXCEPTION
908 WHEN OKC_API.G_EXCEPTION_ERROR THEN
909 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
910 p_api_name => l_api_name,
911 p_pkg_name => G_PKG_NAME,
912 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
913 x_msg_count => x_msg_count,
914 x_msg_data => x_msg_data,
915 p_api_type => G_API_TYPE);
916
917 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
918 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
919 p_api_name => l_api_name,
920 p_pkg_name => G_PKG_NAME,
921 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
922 x_msg_count => x_msg_count,
923 x_msg_data => x_msg_data,
924 p_api_type => G_API_TYPE);
925
926 WHEN OTHERS THEN
927 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
928 p_api_name => l_api_name,
929 p_pkg_name => G_PKG_NAME,
930 p_exc_name => 'OTHERS',
931 x_msg_count => x_msg_count,
932 x_msg_data => x_msg_data,
933 p_api_type => G_API_TYPE);
934
935 END create_transaction;
936
937 ------------------------------------------------------------------------------
938 -- PROCEDURE abandon_revisions
939 --
940 -- This procedure abandons created transaction and corresponding contracts
941 --
942 -- Calls:
943 -- Called By: This API is called from 2 places -
944 -- 1) At the time of upgrade from 11i > r12, to cancel pending rebook transactions : OKLTXRBKUG.sql
945 -- 2) When rebook copy is abandoned from the UI
946 ------------------------------------------------------------------------------
947 PROCEDURE abandon_revisions(
948 p_api_version IN NUMBER,
949 p_init_msg_list IN VARCHAR2,
950 x_return_status OUT NOCOPY VARCHAR2,
951 x_msg_count OUT NOCOPY NUMBER,
952 x_msg_data OUT NOCOPY VARCHAR2,
953 p_rev_tbl IN rev_tbl_type,
954 p_contract_status IN VARCHAR2,
955 p_tsu_code IN VARCHAR2,
956 --akrangan added for ebtax rebook changes starts : these paramaters are sent by the upgrade script
957 p_source_trx_id IN NUMBER DEFAULT NULL ,
958 p_source_trx_name IN VARCHAR2 DEFAULT NULL
959 --akrangan added for ebtax rebook changes ends : these paramaters are sent by the upgrade script
960 ) IS
961
962 l_api_name VARCHAR2(17) := 'abandon_revisions';
963 l_proc_name VARCHAR2(17) := 'ABANDON_REVISIONS';
964 l_api_version CONSTANT NUMBER := 1;
965 i NUMBER := 0;
966
967 l_rev_tbl rev_tbl_type := p_rev_tbl;
968 x_tcnv_rec tcnv_rec_type;
969
970 l_chrv_rec chrv_rec_type;
971 x_chrv_rec chrv_rec_type;
972
973 --akrangan added for ebtax rebook changes starts
974 l_source_trx_id NUMBER;
975 l_source_trx_name VARCHAR2(150);
976 l_source_table VARCHAR2(30) := 'OKL_TRX_CONTRACTS';
977 l_prbk_tcnv_rec tcnv_rec_type;
978 x_prbk_tcnv_rec tcnv_rec_type;
979 --akrangan added for ebtax rebook changes ends
980
981 BEGIN -- main process begins here
982 IF (G_DEBUG_ENABLED = 'Y') THEN
983 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
984 END IF;
985
986 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
987 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE call ....');
988 END IF;
989
990 x_return_status := OKC_API.G_RET_STS_SUCCESS;
991 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
992 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
993 END IF;
994 -- call START_ACTIVITY to create savepoint, check compatibility
995 -- and initialize message list
996 x_return_status := OKC_API.START_ACTIVITY(
997 p_api_name => l_api_name,
998 p_pkg_name => G_PKG_NAME,
999 p_init_msg_list => p_init_msg_list,
1000 l_api_version => l_api_version,
1001 p_api_version => p_api_version,
1002 p_api_type => G_API_TYPE,
1003 x_return_status => x_return_status);
1004
1005 -- check if activity started successfully
1006 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1007 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1008 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1009 RAISE OKC_API.G_EXCEPTION_ERROR;
1010 END IF;
1011
1012 LOOP
1013 i := i + 1;
1014
1015 l_chrv_rec.id := l_rev_tbl(i).chr_id;
1016 l_chrv_rec.sts_code := p_contract_status;
1017
1018 update_trx_status(
1019 p_api_version => l_api_version,
1020 p_init_msg_list => p_init_msg_list,
1021 x_return_status => x_return_status,
1022 x_msg_count => x_msg_count,
1023 x_msg_data => x_msg_data,
1024 p_chr_id => l_rev_tbl(i).chr_id,
1025 p_status => p_tsu_code,
1026 x_tcnv_rec => x_tcnv_rec --record for the primary rep
1027 );
1028
1029 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1030 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Trx UPDATE status: '|| x_return_status);
1031 END IF;
1032
1033 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1034 RAISE OKC_API.G_EXCEPTION_ERROR;
1035 END IF;
1036
1037 --akrangan ebtax rebook impacts starts
1038 IF ( p_source_trx_id IS NULL ) AND ( p_source_trx_name IS NULL ) THEN -- called from rebook abandon UI
1039
1040 --Bug# 9851889
1041 -- When called from Rebook/Re-lease Contract Abandon UI, we need to cancel tax lines only if the call is for
1042 -- abandoning a rebook transaction.
1043 IF (x_tcnv_rec.tcn_type = 'TRBK') THEN
1044 l_source_trx_id := x_tcnv_rec.id;
1045 l_source_trx_name := 'Rebook';
1046 ELSE
1047 l_source_trx_id := NULL;
1048 l_source_trx_name := NULL;
1049 END IF;
1050
1051 -- Bug 6379268
1052 ELSIF (p_source_trx_id IS NOT NULL) AND (p_source_trx_name IS NOT NULL) THEN --called from upgrade, copy contract was validated and PRBK trx exists
1053 l_source_trx_id := p_source_trx_id;
1054 l_source_trx_name := p_source_trx_name;
1055
1056 -- Cancel 'Pre-Rebook' transaction
1057 IF l_source_trx_name = 'Pre-Rebook' THEN
1058 l_prbk_tcnv_rec.id := l_source_trx_id;
1059 l_prbk_tcnv_rec.tsu_code := p_tsu_code;
1060
1061 Okl_Trx_Contracts_Pub.update_trx_contracts(
1062 p_api_version => p_api_version,
1063 p_init_msg_list => p_init_msg_list,
1064 x_return_status => x_return_status,
1065 x_msg_count => x_msg_count,
1066 x_msg_data => x_msg_data,
1067 p_tcnv_rec => l_prbk_tcnv_rec,
1068 x_tcnv_rec => x_prbk_tcnv_rec
1069 );
1070
1071 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1072 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Cancel Pre-Rebook transaction status: '|| x_return_status);
1073 END IF;
1074
1075 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1076 RAISE Okc_Api.G_EXCEPTION_ERROR;
1077 END IF;
1078 END IF;
1079 ELSE
1080 -- p_source_trx_id IS NULL and p_source_trx_name IS NOT NULL
1081 -- called from upgrade script. rebook copy is created but not validated yet, hence PRBK trx does not exist
1082 l_source_trx_id := NULL;
1083 l_source_trx_name := NULL;
1084 END IF;
1085
1086 -- Bug 6379268
1087 IF (l_source_trx_id IS NOT NULL) AND (l_source_trx_name IS NOT NULL) THEN
1088 ---cancel tax lines call
1089 OKL_PROCESS_SALES_TAX_PVT.cancel_document_tax(
1090 p_api_version => l_api_version,
1091 p_init_msg_list => p_init_msg_list,
1092 x_return_status => x_return_status,
1093 x_msg_count => x_msg_count,
1094 x_msg_data => x_msg_data,
1095 p_source_trx_id => l_source_trx_id,
1096 p_source_trx_name => l_source_trx_name,
1097 p_source_table => l_source_table
1098 );
1099 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1100 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Canel Document Tax status: '|| x_return_status);
1101 END IF;
1102
1103 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1104 RAISE OKC_API.G_EXCEPTION_ERROR;
1105 END IF;
1106 END IF;
1107 --akrangan ebtax rebook impacts ends
1108
1109 okl_okc_migration_pvt.update_contract_header(
1110 p_api_version => l_api_version,
1111 p_init_msg_list => p_init_msg_list,
1112 x_return_status => x_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data,
1115 p_restricted_update => 'F',
1116 p_chrv_rec => l_chrv_rec,
1117 x_chrv_rec => x_chrv_rec
1118 );
1119 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1120 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Contract Header UPDATE status: '|| x_return_status);
1121 END IF;
1122
1123 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1124 RAISE OKC_API.G_EXCEPTION_ERROR;
1125 END IF;
1126
1127 okl_contract_status_pub.cascade_lease_status(
1128 p_api_version => l_api_version,
1129 p_init_msg_list => p_init_msg_list,
1130 x_return_status => x_return_status,
1131 x_msg_count => x_msg_count,
1132 x_msg_data => x_msg_data,
1133 p_chr_id => l_chrv_rec.id
1134 );
1135 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1136 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Contract Line UPDATE status: '|| x_return_status);
1137 END IF;
1138
1139 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1140 RAISE OKC_API.G_EXCEPTION_ERROR;
1141 END IF;
1142
1143
1144 EXIT WHEN (i >= l_rev_tbl.last);
1145
1146 END LOOP;
1147
1148 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data);
1150
1151 EXCEPTION
1152 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1153 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1154 p_api_name => l_api_name,
1155 p_pkg_name => G_PKG_NAME,
1156 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1157 x_msg_count => x_msg_count,
1158 x_msg_data => x_msg_data,
1159 p_api_type => G_API_TYPE);
1160
1161 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1162 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1163 p_api_name => l_api_name,
1164 p_pkg_name => G_PKG_NAME,
1165 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1166 x_msg_count => x_msg_count,
1167 x_msg_data => x_msg_data,
1168 p_api_type => G_API_TYPE);
1169
1170 WHEN OTHERS THEN
1171 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1172 p_api_name => l_api_name,
1173 p_pkg_name => G_PKG_NAME,
1174 p_exc_name => 'OTHERS',
1175 x_msg_count => x_msg_count,
1176 x_msg_data => x_msg_data,
1177 p_api_type => G_API_TYPE);
1178
1179 END abandon_revisions;
1180
1181 ------------------------------------------------------------------------------
1182 -- PROCEDURE create_service_transaction
1183 --
1184 -- This procedure creates Transaction (LINK/DELINK) during service integration
1185 --
1186 -- Calls:
1187 -- Called By:
1188 ------------------------------------------------------------------------------
1189 PROCEDURE create_service_transaction(
1190 p_api_version IN NUMBER,
1191 p_init_msg_list IN VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 p_lease_id IN OKC_K_HEADERS_V.ID%TYPE,
1196 p_service_id IN OKC_K_HEADERS_V.ID%TYPE,
1197 p_description IN VARCHAR2,
1198 p_trx_date IN DATE,
1199 p_status IN VARCHAR2,
1200 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1201 ) IS
1202
1203 l_api_name VARCHAR2(35) := 'create_service_transaction';
1204 l_proc_name VARCHAR2(35) := 'CREATE_SERVICE_TRANSACTION';
1205 l_api_version CONSTANT NUMBER := 1;
1206
1207 l_tcnv_rec tcnv_rec_type;
1208 l_out_tcnv_rec tcnv_rec_type;
1209
1210 CURSOR try_csr IS
1211 SELECT id
1212 FROM okl_trx_types_tl
1213 WHERE LANGUAGE = 'US'
1214 AND name = 'Service Integration';
1215
1216 CURSOR con_header_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1217 SELECT currency_code,
1218 authoring_org_id
1219 FROM okl_k_headers_full_v
1220 WHERE id = p_chr_id;
1221
1222 l_try_id NUMBER;
1223 service_txn_failed EXCEPTION;
1224 --Added by dpsingh for LE Uptake
1225 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1226 l_legal_entity_id NUMBER;
1227
1228 BEGIN -- main process begins here
1229 IF (G_DEBUG_ENABLED = 'Y') THEN
1230 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1231 END IF;
1232
1233 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1234 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1235 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1236 END IF;
1237 -- call START_ACTIVITY to create savepoint, check compatibility
1238 -- and initialize message list
1239 x_return_status := OKC_API.START_ACTIVITY(
1240 p_api_name => l_api_name,
1241 p_pkg_name => G_PKG_NAME,
1242 p_init_msg_list => p_init_msg_list,
1243 l_api_version => l_api_version,
1244 p_api_version => p_api_version,
1245 p_api_type => G_API_TYPE,
1246 x_return_status => x_return_status);
1247
1248 -- check if activity started successfully
1249 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1251 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1252 RAISE OKC_API.G_EXCEPTION_ERROR;
1253 END IF;
1254
1255 l_tcnv_rec := NULL;
1256 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1257 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER START...');
1258 END IF;
1259 --
1260 -- Check for Transaction Date, it is mandetory
1261 --
1262 IF (p_trx_date IS NULL) THEN
1263 okl_api.set_message(
1264 G_APP_NAME,
1265 G_LLA_MISSING_TRX_DATE
1266 );
1267 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1268 END IF;
1269
1270 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1271 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Afer DATE CHECK');
1272 END IF;
1273
1274 l_try_id := NULL;
1275
1276 OPEN try_csr;
1277 FETCH try_csr INTO l_try_id;
1278 CLOSE try_csr;
1279
1280 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1281 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER getting try id');
1282 END IF;
1283
1284 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1285 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Try ID: '||l_try_id);
1286 END IF;
1287
1288 l_tcnv_rec.try_id := l_try_id;
1289 l_tcnv_rec.tsu_code := p_status;
1290 l_tcnv_rec.description := p_description;
1291 l_tcnv_rec.tcn_type := 'SER';
1292 l_tcnv_rec.khr_id := p_lease_id; -- OKL Contract ID
1293 l_tcnv_rec.chr_id := p_service_id; -- OKS Contract ID
1294 --l_tcnv_rec.khr_id_new := p_new_chr_id;
1295 l_tcnv_rec.date_transaction_occurred := p_trx_date;
1296 --Added by dpsingh for LE Uptake
1297 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_lease_id) ;
1298 IF l_legal_entity_id IS NOT NULL THEN
1299 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
1300 ELSE
1301 -- get the contract number
1302 OPEN contract_num_csr(p_lease_id);
1303 FETCH contract_num_csr INTO l_cntrct_number;
1304 CLOSE contract_num_csr;
1305 Okl_Api.set_message(p_app_name => g_app_name,
1306 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1307 p_token1 => 'CONTRACT_NUMBER',
1308 p_token1_value => l_cntrct_number);
1309 RAISE OKL_API.G_EXCEPTION_ERROR;
1310 END IF;
1311 FOR con_header_rec IN con_header_csr (p_lease_id)
1312 LOOP
1313 l_tcnv_rec.org_id := con_header_rec.authoring_org_id;
1314 l_tcnv_rec.currency_code := con_header_rec.currency_code;
1315 END LOOP;
1316
1317 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1318 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE calling okl_trx_contracts_pub.create_trx_contracts');
1319 END IF;
1320 -- Create Transaction Header only
1321 okl_trx_contracts_pub.create_trx_contracts(
1322 p_api_version => 1.0,
1323 p_init_msg_list => p_init_msg_list,
1324 x_return_status => x_return_status,
1325 x_msg_count => x_msg_count,
1326 x_msg_data => x_msg_data,
1327 p_tcnv_rec => l_tcnv_rec,
1328 x_tcnv_rec => l_out_tcnv_rec
1329 );
1330
1331 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1332 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER calling okl_trx_contracts_pub.create_trx_contracts');
1333 END IF;
1334
1335 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1336 RAISE OKC_API.G_EXCEPTION_ERROR;
1337 END IF;
1338
1339 x_tcnv_rec := l_out_tcnv_rec;
1340
1341 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1342 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done...');
1343 END IF;
1344
1345 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1346 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1347 x_msg_data => x_msg_data);
1348
1349 EXCEPTION
1350
1351 WHEN service_txn_failed THEN
1352 IF try_csr%ISOPEN THEN
1353 CLOSE try_csr;
1354 END IF;
1355 x_return_status := OKL_API.G_RET_STS_ERROR;
1356
1357 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1358 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1359 p_api_name => l_api_name,
1360 p_pkg_name => G_PKG_NAME,
1361 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1362 x_msg_count => x_msg_count,
1363 x_msg_data => x_msg_data,
1364 p_api_type => G_API_TYPE);
1365
1366 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1367 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1368 p_api_name => l_api_name,
1369 p_pkg_name => G_PKG_NAME,
1370 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1371 x_msg_count => x_msg_count,
1372 x_msg_data => x_msg_data,
1373 p_api_type => G_API_TYPE);
1374
1375 WHEN OTHERS THEN
1376 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1377 p_api_name => l_api_name,
1378 p_pkg_name => G_PKG_NAME,
1379 p_exc_name => 'OTHERS',
1380 x_msg_count => x_msg_count,
1381 x_msg_data => x_msg_data,
1382 p_api_type => G_API_TYPE);
1383
1384 END create_service_transaction;
1385
1386
1387 ------------------------------------------------------------------------------
1388 -- PROCEDURE create_ppd_transaction
1389 --
1390 -- This procedure creates PPD Transaction initiated from customer service
1391 --
1392 -- Calls:
1393 -- Called By:
1394 ------------------------------------------------------------------------------
1395 PROCEDURE create_ppd_transaction(
1396 p_api_version IN NUMBER,
1397 p_init_msg_list IN VARCHAR2,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2,
1401 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1402 p_trx_date IN DATE,
1403 p_trx_type IN VARCHAR2,
1404 p_reason_code IN VARCHAR2,
1405 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1406 ) IS
1407
1408 l_api_name VARCHAR2(35) := 'create_ppd_transaction';
1409 l_proc_name VARCHAR2(35) := 'CREATE_PPD_TRANSACTION';
1410 l_api_version CONSTANT NUMBER := 1;
1411
1412 l_tcnv_rec tcnv_rec_type;
1413 l_out_tcnv_rec tcnv_rec_type;
1414
1415 CURSOR try_csr IS
1416 SELECT id
1417 FROM okl_trx_types_tl
1418 WHERE LANGUAGE = 'US'
1419 AND name = 'Principal Paydown';
1420
1421 CURSOR con_header_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1422 SELECT currency_code,
1423 authoring_org_id
1424 FROM okl_k_headers_full_v
1425 WHERE id = p_chr_id;
1426
1427 l_try_id NUMBER;
1428 ppd_txn_failed EXCEPTION;
1429 --Added by dpsingh for LE Uptake
1430 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1431 l_legal_entity_id NUMBER;
1432
1433 BEGIN -- main process begins here
1434 IF (G_DEBUG_ENABLED = 'Y') THEN
1435 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1436 END IF;
1437
1438 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1439 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1440 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1441 END IF;
1442 -- call START_ACTIVITY to create savepoint, check compatibility
1443 -- and initialize message list
1444 x_return_status := OKC_API.START_ACTIVITY(
1445 p_api_name => l_api_name,
1446 p_pkg_name => G_PKG_NAME,
1447 p_init_msg_list => p_init_msg_list,
1448 l_api_version => l_api_version,
1449 p_api_version => p_api_version,
1450 p_api_type => G_API_TYPE,
1451 x_return_status => x_return_status);
1452
1453 -- check if activity started successfully
1454 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1455 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1456 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1457 RAISE OKC_API.G_EXCEPTION_ERROR;
1458 END IF;
1459
1460 l_tcnv_rec := NULL;
1461 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1462 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER START...');
1463 END IF;
1464 --
1465 -- Check for Transaction Date, it is mandetory
1466 --
1467 IF (p_trx_date IS NULL) THEN
1468 okl_api.set_message(
1469 G_APP_NAME,
1470 G_LLA_MISSING_TRX_DATE
1471 );
1472 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1473 END IF;
1474
1475 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1476 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Afer DATE CHECK');
1477 END IF;
1478
1479 l_try_id := NULL;
1480
1481 OPEN try_csr;
1482 FETCH try_csr INTO l_try_id;
1483 IF try_csr%NOTFOUND THEN
1484 RAISE ppd_txn_failed;
1485 END IF;
1486 CLOSE try_csr;
1487
1488 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1489 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER getting try id');
1490 END IF;
1491
1492 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1493 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Try ID: '||l_try_id);
1494 END IF;
1495
1496 l_tcnv_rec.try_id := l_try_id;
1497 l_tcnv_rec.tsu_code := 'ENTERED';
1498 l_tcnv_rec.description := 'Principal Paydown';
1499 l_tcnv_rec.tcn_type := 'PPD';
1500 l_tcnv_rec.khr_id := p_chr_id;
1501 l_tcnv_rec.date_transaction_occurred := p_trx_date;
1502 --Added by dpsingh for LE Uptake
1503 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_chr_id) ;
1504 IF l_legal_entity_id IS NOT NULL THEN
1505 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
1506 ELSE
1507 -- get the contract number
1508 OPEN contract_num_csr(p_chr_id);
1509 FETCH contract_num_csr INTO l_cntrct_number;
1510 CLOSE contract_num_csr;
1511 Okl_Api.set_message(p_app_name => g_app_name,
1512 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1513 p_token1 => 'CONTRACT_NUMBER',
1514 p_token1_value => l_cntrct_number);
1515 RAISE OKL_API.G_EXCEPTION_ERROR;
1516 END IF;
1517 FOR con_header_rec IN con_header_csr (p_chr_id)
1518 LOOP
1519 l_tcnv_rec.org_id := con_header_rec.authoring_org_id;
1520 l_tcnv_rec.currency_code := con_header_rec.currency_code;
1521 END LOOP;
1522
1523 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1524 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE calling okl_trx_contracts_pub.create_trx_contracts');
1525 END IF;
1526 -- Create Transaction Header only
1527 okl_trx_contracts_pub.create_trx_contracts(
1528 p_api_version => 1.0,
1529 p_init_msg_list => p_init_msg_list,
1530 x_return_status => x_return_status,
1531 x_msg_count => x_msg_count,
1532 x_msg_data => x_msg_data,
1533 p_tcnv_rec => l_tcnv_rec,
1534 x_tcnv_rec => l_out_tcnv_rec
1535 );
1536
1537 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1538 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER calling okl_trx_contracts_pub.create_trx_contracts');
1539 END IF;
1540
1541 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1542 RAISE OKC_API.G_EXCEPTION_ERROR;
1543 END IF;
1544
1545 x_tcnv_rec := l_out_tcnv_rec;
1546
1547 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1548 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done...');
1549 END IF;
1550
1551 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1552 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1553 x_msg_data => x_msg_data);
1554
1555 EXCEPTION
1556
1557 WHEN ppd_txn_failed THEN
1558 IF try_csr%ISOPEN THEN
1559 CLOSE try_csr;
1560 END IF;
1561 x_return_status := OKL_API.G_RET_STS_ERROR;
1562
1563 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1564 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1565 p_api_name => l_api_name,
1566 p_pkg_name => G_PKG_NAME,
1567 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1568 x_msg_count => x_msg_count,
1569 x_msg_data => x_msg_data,
1570 p_api_type => G_API_TYPE);
1571
1572 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1573 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1574 p_api_name => l_api_name,
1575 p_pkg_name => G_PKG_NAME,
1576 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data,
1579 p_api_type => G_API_TYPE);
1580
1581 WHEN OTHERS THEN
1582 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1583 p_api_name => l_api_name,
1584 p_pkg_name => G_PKG_NAME,
1585 p_exc_name => 'OTHERS',
1586 x_msg_count => x_msg_count,
1587 x_msg_data => x_msg_data,
1588 p_api_type => G_API_TYPE);
1589
1590 END create_ppd_transaction;
1591 END OKL_TRANSACTION_PVT;