[Home] [Help]
PACKAGE BODY: APPS.OKL_TRANSACTION_PVT
Source
1 PACKAGE BODY OKL_TRANSACTION_PVT AS
2 /* $Header: OKLRTXNB.pls 120.9.12010000.2 2008/10/01 22:53:36 rkuttiya 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 CURSOR trx_csr(p_chr_id_new OKC_K_HEADERS_V.ID%TYPE) IS
310 SELECT id
311 FROM OKL_TRX_CONTRACTS
312 WHERE khr_id_new = p_chr_id_new
313 --rkuttiya added for 12.1.1 Multi GAAP
314 AND representation_type = 'PRIMARY';
315 --
316
317 l_tcnv_rec tcnv_rec_type;
318
319 BEGIN
320 IF (G_DEBUG_ENABLED = 'Y') THEN
321 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
322 END IF;
323
324 x_return_status := OKC_API.G_RET_STS_SUCCESS;
325 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
326 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
327 END IF;
328 -- call START_ACTIVITY to create savepoint, check compatibility
329 -- and initialize message list
330 x_return_status := OKC_API.START_ACTIVITY(
331 p_api_name => l_api_name,
332 p_pkg_name => G_PKG_NAME,
333 p_init_msg_list => p_init_msg_list,
334 l_api_version => l_api_version,
335 p_api_version => p_api_version,
336 p_api_type => G_API_TYPE,
337 x_return_status => x_return_status);
338
339 -- check if activity started successfully
340 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
341 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
342 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
343 RAISE OKC_API.G_EXCEPTION_ERROR;
344 END IF;
345
346 FOR trx_rec IN trx_csr(p_chr_id)
347 LOOP
348 l_tcnv_rec.id := trx_rec.id;
349 l_tcnv_rec.tsu_code := p_status; --'PROCESSED';
350 END LOOP;
351
352 Okl_Trx_Contracts_Pub.update_trx_contracts(
353 p_api_version => p_api_version,
354 p_init_msg_list => p_init_msg_list,
355 x_return_status => x_return_status,
356 x_msg_count => x_msg_count,
357 x_msg_data => x_msg_data,
358 p_tcnv_rec => l_tcnv_rec,
359 x_tcnv_rec => x_tcnv_rec
360 );
361
362 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
363 RAISE Okc_Api.G_EXCEPTION_ERROR;
364 END IF;
365
366 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
367 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
368 x_msg_data => x_msg_data);
369
370 EXCEPTION
371 WHEN OKC_API.G_EXCEPTION_ERROR THEN
372 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
373 p_api_name => l_api_name,
374 p_pkg_name => G_PKG_NAME,
375 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
376 x_msg_count => x_msg_count,
377 x_msg_data => x_msg_data,
378 p_api_type => G_API_TYPE);
379
380 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
381 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
382 p_api_name => l_api_name,
383 p_pkg_name => G_PKG_NAME,
384 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
385 x_msg_count => x_msg_count,
386 x_msg_data => x_msg_data,
387 p_api_type => G_API_TYPE);
388
389 WHEN OTHERS THEN
390 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
391 p_api_name => l_api_name,
392 p_pkg_name => G_PKG_NAME,
393 p_exc_name => 'OTHERS',
394 x_msg_count => x_msg_count,
395 x_msg_data => x_msg_data,
396 p_api_type => G_API_TYPE);
397 END update_trx_status;
398
399 ------------------------------------------------------------------------------
400 -- PROCEDURE check_contract_securitized
401 --
402 -- This procedure checks whether the contract is securitized and returns x_return_status
403 -- 'F' - if securitized
404 -- 'S' - if it is not securitized
405 --
406 -- Calls:
407 -- Called By:
408 ------------------------------------------------------------------------------
409 PROCEDURE check_contract_securitized(
410 p_api_version IN NUMBER,
411 p_init_msg_list IN VARCHAR2,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_count OUT NOCOPY NUMBER,
414 x_msg_data OUT NOCOPY VARCHAR2,
415 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
416 p_trx_date IN DATE
417 ) IS
418
419 l_api_name VARCHAR2(35) := 'check_contract_securitized';
420 l_proc_name VARCHAR2(35) := 'CHECK_CONTRACT_SECURITIZED';
421 l_api_version CONSTANT NUMBER := 1;
422
423 l_contract_secu VARCHAR2(1);
424 l_inv_agmt_chr_id_tbl inv_agmt_chr_id_tbl_type;
425 BEGIN
426 IF (G_DEBUG_ENABLED = 'Y') THEN
427 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
428 END IF;
429
430 x_return_status := OKL_API.G_RET_STS_SUCCESS;
431 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
432 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
433 END IF;
434
435 okl_securitization_pvt.check_khr_securitized(
436 p_api_version => 1.0,
437 p_init_msg_list => OKC_API.G_FALSE,
438 x_return_status => x_return_status,
439 x_msg_count => x_msg_count,
440 x_msg_data => x_msg_data,
441 p_khr_id => p_chr_id,
442 p_effective_date => p_trx_date,
443 x_value => l_contract_secu,
444 x_inv_agmt_chr_id_tbl => l_inv_agmt_chr_id_tbl
445 );
446
447 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
448 IF (l_contract_secu = OKL_API.G_TRUE) THEN
449 okl_api.set_message(
450 G_APP_NAME,
451 G_LLA_SECU_ERROR
452 );
453 x_return_status := OKL_API.G_RET_STS_ERROR;
454 END IF;
455 END IF;
456
457 RETURN;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
462 p_api_name => l_api_name,
463 p_pkg_name => G_PKG_NAME,
464 p_exc_name => 'OTHERS',
465 x_msg_count => x_msg_count,
466 x_msg_data => x_msg_data,
467 p_api_type => G_API_TYPE);
468
469 END check_contract_securitized;
470
471 ------------------------------------------------------------------------------
472 -- PROCEDURE check_contract_securitized
473 --
474 -- This procedure checks whether the asset residual value is securitized
475 -- The value in x_return_status will be
476 -- 'F' - if securitized
477 -- 'S' - if it is not securitized
478 --
479 -- Calls:
480 -- Called By:
481 ------------------------------------------------------------------------------
482 PROCEDURE check_contract_securitized(
483 p_api_version IN NUMBER,
484 p_init_msg_list IN VARCHAR2,
485 x_return_status OUT NOCOPY VARCHAR2,
486 x_msg_count OUT NOCOPY NUMBER,
487 x_msg_data OUT NOCOPY VARCHAR2,
488 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
489 p_cle_id IN OKC_K_LINES_V.ID%TYPE,
490 p_stream_type_class IN okl_strm_type_b.stream_type_subclass%TYPE,
491 p_trx_date IN DATE
492 ) IS
493
494 l_api_name VARCHAR2(35) := 'check_contract_securitized';
495 l_proc_name VARCHAR2(35) := 'CHECK_CONTRACT_SECURITIZED';
496 l_api_version CONSTANT NUMBER := 1;
497
498 l_asset_secu VARCHAR2(1);
499 l_inv_agmt_chr_id_tbl inv_agmt_chr_id_tbl_type;
500
501 BEGIN
502 IF (G_DEBUG_ENABLED = 'Y') THEN
503 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
504 END IF;
505
506 x_return_status := OKL_API.G_RET_STS_SUCCESS;
507 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
508 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
509 END IF;
510
511 okl_securitization_pvt.check_kle_securitized(
512 p_api_version => p_api_version,
513 p_init_msg_list => OKL_API.G_FALSE,
514 x_return_status => x_return_status,
515 x_msg_count => x_msg_count,
516 x_msg_data => x_msg_data,
517 p_kle_id => p_cle_id,
518 p_effective_date => p_trx_date,
519 p_stream_type_subclass => p_stream_type_class,
520 x_value => l_asset_secu,
521 x_inv_agmt_chr_id_tbl => l_inv_agmt_chr_id_tbl
522 );
523
524 IF (x_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
525 IF (l_asset_secu = OKL_API.G_TRUE) THEN
526 okl_api.set_message(
527 G_APP_NAME,
528 G_LLA_SECU_ERROR
529 );
530 x_return_status := OKL_API.G_RET_STS_ERROR;
531 END IF;
532 END IF;
533
534 RETURN;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
539 p_api_name => l_api_name,
540 p_pkg_name => G_PKG_NAME,
541 p_exc_name => 'OTHERS',
542 x_msg_count => x_msg_count,
543 x_msg_data => x_msg_data,
544 p_api_type => G_API_TYPE);
545
546 END check_contract_securitized;
547
548 ------------------------------------------------------------------------------
549 -- PROCEDURE create_transaction
550 --
551 -- This procedure creates Transaction as a first step to REBOOKing
552 --
553 -- Calls:
554 -- Called By:
555 ------------------------------------------------------------------------------
556 PROCEDURE create_transaction(
557 p_api_version IN NUMBER,
558 p_init_msg_list IN VARCHAR2,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_msg_count OUT NOCOPY NUMBER,
561 x_msg_data OUT NOCOPY VARCHAR2,
562 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
563 p_new_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
564 p_reason_code IN VARCHAR2,
565 p_description IN VARCHAR2,
566 p_trx_date IN DATE,
567 p_trx_type IN VARCHAR2, -- 'REBOOK' or 'SPLIT'
568 x_tcnv_rec OUT NOCOPY tcnv_rec_type
569 ) IS
570
571 l_api_name VARCHAR2(35) := 'create_transaction';
572 l_proc_name VARCHAR2(35) := 'CREATE_TRANSACTION';
573 l_api_version CONSTANT NUMBER := 1;
574
575 l_tcnv_rec tcnv_rec_type;
576 l_out_tcnv_rec tcnv_rec_type;
577
578 CURSOR con_eff_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
579 SELECT start_date,
580 end_date
581 FROM okc_k_headers_b
582 WHERE id = p_chr_id;
583
584 CURSOR check_date_csr (p_start_date DATE,
585 p_end_date DATE,
586 p_trx_date DATE) IS
587 SELECT 'Y'
588 FROM DUAL
589 WHERE p_trx_date BETWEEN p_start_date AND p_end_date;
590
591 l_con_start_date DATE;
592 l_con_end_date DATE;
593 l_date_valid VARCHAR2(1);
594
595 --akrangan start
596 --CURSOR FOR CHECKING WHETHER A ASSET LOCATION CHANGE TRANSACTION IS
597 --CREATED FOR THE ASSETS USED IN THE CONTRACT
598 CURSOR chk_asst_loc_khr_csr(p_chr_id NUMBER)
599 IS
600 SELECT 'N'
601 FROM okc_k_lines_b okc ,
602 okc_line_styles_b ols,
603 okl_trx_assets trx ,
604 okl_txl_itm_insts itm
605 WHERE okc.dnz_chr_id = p_chr_id
606 AND okc.id = itm.kle_id
607 AND okc.lse_id = ols.id
608 AND ols.lty_code = 'INST_ITEM'
609 AND trx.tsu_code = 'ENTERED'
610 AND trx.tas_type = 'ALG'
611 AND trx.id = itm.tas_id ;
612
613 l_rebook_allowed VARCHAR2(1) := 'Y';
614 l_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
615 --akrangan end
616 /*
617 -- mvasudev, 08/30/2004
618 -- Added PROCEDURE to enable Business Event
619 */
620 PROCEDURE raise_business_event(x_return_status OUT NOCOPY VARCHAR2
621 )
622 IS
623 l_parameter_list wf_parameter_list_t;
624 BEGIN
625
626 IF (p_trx_type = 'SPLIT') THEN
627
628 wf_event.AddParameterToList(G_WF_ITM_SRC_CONTRACT_ID,p_chr_id,l_parameter_list);
629 wf_event.AddParameterToList(G_WF_ITM_DEST_CONTRACT_ID,p_new_chr_id,l_parameter_list);
630 wf_event.AddParameterToList(G_WF_ITM_TRX_DATE,fnd_date.date_to_canonical(p_trx_date),l_parameter_list);
631 wf_event.AddParameterToList(G_WF_ITM_CONTRACT_PROCESS,G_KHR_PROCESS_SPLIT_CONTRACT,l_parameter_list);
632
633 OKL_WF_PVT.raise_event (p_api_version => p_api_version,
634 p_init_msg_list => p_init_msg_list,
635 x_return_status => x_return_status,
636 x_msg_count => x_msg_count,
637 x_msg_data => x_msg_data,
638 p_event_name => G_WF_EVT_KHR_SPLIT_REQ,
639 p_parameters => l_parameter_list);
640
641
642 END IF;
643
644 EXCEPTION
645 WHEN OTHERS THEN
646 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
647 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
648 END raise_business_event;
649
650
651 /*
652 -- mvasudev, 08/30/2004
653 -- END, PROCEDURE to enable Business Event
654 */
655
656
657
658 BEGIN -- main process begins here
659
660 x_return_status := OKC_API.G_RET_STS_SUCCESS;
661 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
662 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
663 END IF;
664 -- call START_ACTIVITY to create savepoint, check compatibility
665 -- and initialize message list
666 x_return_status := OKC_API.START_ACTIVITY(
667 p_api_name => l_api_name,
668 p_pkg_name => G_PKG_NAME,
669 p_init_msg_list => p_init_msg_list,
670 l_api_version => l_api_version,
671 p_api_version => p_api_version,
672 p_api_type => G_API_TYPE,
673 x_return_status => x_return_status);
674
675 -- check if activity started successfully
676 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
677 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
678 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
679 RAISE OKC_API.G_EXCEPTION_ERROR;
680 END IF;
681
682 IF (p_trx_type NOT IN ('REBOOK','SPLIT')) THEN
683 okl_api.set_message(
684 G_APP_NAME,
685 G_LLA_INVALID_TRX_TYPE,
686 'TRX_TYPE',
687 p_trx_type
688 );
689 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
690 END IF;
691
692 IF (p_trx_type = 'SPLIT') THEN
693 --
694 -- Check for securitization during split contract
695 --
696 check_contract_securitized(
697 p_api_version => p_api_version,
698 p_init_msg_list => OKL_API.G_FALSE,
699 x_return_status => x_return_status,
700 x_msg_count => x_msg_count,
701 x_msg_data => x_msg_data,
702 p_chr_id => p_chr_id,
703 p_trx_date => p_trx_date
704 );
705
706 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
707 RAISE OKC_API.G_EXCEPTION_ERROR;
708 END IF;
709 END IF;
710
711 IF (p_trx_type = 'REBOOK') THEN
712 --akrangan start
713 --If there are any Asset Location Transaction is
714 --in progress for asset lines in the contract
715 --Rebook will not allowed in that case.
716 OPEN chk_asst_loc_khr_csr(p_chr_id);
717 FETCH chk_asst_loc_khr_csr INTO l_rebook_allowed;
718 CLOSE chk_asst_loc_khr_csr;
719 IF l_rebook_allowed = 'N' THEN
720
721 OPEN contract_num_csr(p_chr_id);
722 FETCH contract_num_csr INTO l_contract_number;
723 CLOSE contract_num_csr;
724
725 okl_api.set_message(
726 G_APP_NAME,
727 'OKL_TX_AST_LOC_RBK_NOTALLWED',
728 'CONTRACT_NUMBER',
729 l_contract_number
730 );
731
732 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
733 END IF;
734 --akrangan end
735 validate_rebook_reason(
736 x_return_status => x_return_status,
737 x_msg_count => x_msg_count,
738 x_msg_data => x_msg_data,
739 p_rebook_reason_code => p_reason_code
740 );
741
742 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
743 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
744 END IF;
745 END IF;
746
747 --
748 -- Check for Transaction Date, it is mandetory
749 --
750 IF (p_trx_date IS NULL) THEN
751 okl_api.set_message(
752 G_APP_NAME,
753 G_LLA_MISSING_TRX_DATE
754 );
755 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
756 END IF;
757
758 --
759 -- Transaction date should be within contract effectivity
760 -- Bug# 2504598
761 --
762 OPEN con_eff_csr (p_chr_id);
763 FETCH con_eff_csr INTO l_con_start_date,
764 l_con_end_date;
765 CLOSE con_eff_csr;
766
767 l_date_valid := 'N';
768 OPEN check_date_csr (l_con_start_date,
769 l_con_end_date,
770 p_trx_date);
771
772 FETCH check_date_csr INTO l_date_valid;
773 CLOSE check_date_csr;
774
775 IF (l_date_valid = 'N') THEN
776 okl_api.set_message(
777 G_APP_NAME,
778 G_LLA_WRONG_TRX_DATE,
779 'START_DATE',
780 l_con_start_date,
781 'END_DATE',
782 l_con_end_date
783 );
784 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
785 END IF;
786
787 /* Bug 4766555 : Code commented here and moved to
788 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - Start
789 IF (p_trx_type = G_REBOOK_TRX) THEN
790 -- check if the rebook transaction is allowed for the contract for this rebook date
791 OKL_K_RATE_PARAMS_PVT.check_rebook_allowed(
792 p_api_version => p_api_version
793 , p_init_msg_list => OKL_API.G_FALSE
794 , x_return_status => x_return_status
795 , x_msg_count => x_msg_count
796 , x_msg_data => x_msg_data
797 , p_chr_id => p_chr_id
798 , p_rebook_date => p_trx_date);
799 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
800 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
801 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
802 RAISE OKC_API.G_EXCEPTION_ERROR;
803 END IF;
804 END IF; -- end of check for rebook transaction
805 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - End
806 */
807
808 populate_transaction_rec(
809 x_return_status => x_return_status,
810 x_msg_count => x_msg_count,
811 x_msg_data => x_msg_data,
812 p_chr_id => p_chr_id,
813 p_new_chr_id => p_new_chr_id,
814 p_reason_code => p_reason_code,
815 p_description => p_description,
816 p_trx_date => p_trx_date,
817 p_trx_type => p_trx_type,
818 x_tcnv_rec => l_tcnv_rec
819 );
820
821 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
822 RAISE OKC_API.G_EXCEPTION_ERROR;
823 END IF;
824
825 -- Create Transaction Header only
826 okl_trx_contracts_pub.create_trx_contracts(
827 p_api_version => 1.0,
828 p_init_msg_list => p_init_msg_list,
829 x_return_status => x_return_status,
830 x_msg_count => x_msg_count,
831 x_msg_data => x_msg_data,
832 p_tcnv_rec => l_tcnv_rec,
833 x_tcnv_rec => l_out_tcnv_rec
834 );
835
836 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
837 RAISE OKC_API.G_EXCEPTION_ERROR;
838 END IF;
839
840 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - Start
841 -- apaul 4766555. Code moved here.
842 IF (p_trx_type = G_REBOOK_TRX) THEN
843 -- check if the rebook transaction is allowed for the contract for this rebook date
844 OKL_K_RATE_PARAMS_PVT.check_rebook_allowed(
845 p_api_version => p_api_version
846 , p_init_msg_list => OKL_API.G_FALSE
847 , x_return_status => x_return_status
848 , x_msg_count => x_msg_count
849 , x_msg_data => x_msg_data
850 , p_chr_id => p_chr_id
851 , p_rebook_date => p_trx_date);
852 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
853 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
855 RAISE OKC_API.G_EXCEPTION_ERROR;
856 END IF;
857 END IF; -- end of check for rebook transaction
858 -- smadhava Bug# 4542290 - 24-Aug-2005 - Added - End
859
860 x_tcnv_rec := l_out_tcnv_rec;
861
862 /*
863 -- mvasudev, 08/30/2004
864 -- Code change to enable Business Event
865 */
866 raise_business_event(x_return_status => x_return_status);
867 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
868 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
869 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
870 RAISE OKL_API.G_EXCEPTION_ERROR;
871 END IF;
872
873 /*
874 -- mvasudev, 08/30/2004
875 -- END, Code change to enable Business Event
876 */
877
878 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
879 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
880 x_msg_data => x_msg_data);
881
882 EXCEPTION
883 WHEN OKC_API.G_EXCEPTION_ERROR THEN
884 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
885 p_api_name => l_api_name,
886 p_pkg_name => G_PKG_NAME,
887 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
888 x_msg_count => x_msg_count,
889 x_msg_data => x_msg_data,
890 p_api_type => G_API_TYPE);
891
892 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
893 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
894 p_api_name => l_api_name,
895 p_pkg_name => G_PKG_NAME,
896 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
897 x_msg_count => x_msg_count,
898 x_msg_data => x_msg_data,
899 p_api_type => G_API_TYPE);
900
901 WHEN OTHERS THEN
902 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
903 p_api_name => l_api_name,
904 p_pkg_name => G_PKG_NAME,
905 p_exc_name => 'OTHERS',
906 x_msg_count => x_msg_count,
907 x_msg_data => x_msg_data,
908 p_api_type => G_API_TYPE);
909
910 END create_transaction;
911
912 ------------------------------------------------------------------------------
913 -- PROCEDURE abandon_revisions
914 --
915 -- This procedure abandons created transaction and corresponding contracts
916 --
917 -- Calls:
918 -- Called By:
919 ------------------------------------------------------------------------------
920 PROCEDURE abandon_revisions(
921 p_api_version IN NUMBER,
922 p_init_msg_list IN VARCHAR2,
923 x_return_status OUT NOCOPY VARCHAR2,
924 x_msg_count OUT NOCOPY NUMBER,
925 x_msg_data OUT NOCOPY VARCHAR2,
926 p_rev_tbl IN rev_tbl_type,
927 p_contract_status IN VARCHAR2,
928 p_tsu_code IN VARCHAR2,
929 --akrangan added for ebtax rebook changes starts
930 p_source_trx_id IN NUMBER DEFAULT NULL ,
931 p_source_trx_name IN VARCHAR2 DEFAULT NULL
932 --akrangan added for ebtax rebook changes ends
933 ) IS
934
935 l_api_name VARCHAR2(17) := 'abandon_revisions';
936 l_proc_name VARCHAR2(17) := 'ABANDON_REVISIONS';
937 l_api_version CONSTANT NUMBER := 1;
938 i NUMBER := 0;
939
940 l_rev_tbl rev_tbl_type := p_rev_tbl;
941 x_tcnv_rec tcnv_rec_type;
942
943 l_chrv_rec chrv_rec_type;
944 x_chrv_rec chrv_rec_type;
945
946 --akrangan added for ebtax rebook changes starts
947 l_source_trx_id NUMBER;
948 l_source_trx_name VARCHAR2(150);
949 l_source_table VARCHAR2(30) := 'OKL_TRX_CONTRACTS';
950 l_prbk_tcnv_rec tcnv_rec_type;
951 x_prbk_tcnv_rec tcnv_rec_type;
952 --akrangan added for ebtax rebook changes ends
953
954 BEGIN -- main process begins here
955 IF (G_DEBUG_ENABLED = 'Y') THEN
956 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
957 END IF;
958
959 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
960 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE call ....');
961 END IF;
962
963 x_return_status := OKC_API.G_RET_STS_SUCCESS;
964 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
965 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
966 END IF;
967 -- call START_ACTIVITY to create savepoint, check compatibility
968 -- and initialize message list
969 x_return_status := OKC_API.START_ACTIVITY(
970 p_api_name => l_api_name,
971 p_pkg_name => G_PKG_NAME,
972 p_init_msg_list => p_init_msg_list,
973 l_api_version => l_api_version,
974 p_api_version => p_api_version,
975 p_api_type => G_API_TYPE,
976 x_return_status => x_return_status);
977
978 -- check if activity started successfully
979 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
980 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
981 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
982 RAISE OKC_API.G_EXCEPTION_ERROR;
983 END IF;
984
985 LOOP
986 i := i + 1;
987
988 l_chrv_rec.id := l_rev_tbl(i).chr_id;
989 l_chrv_rec.sts_code := p_contract_status;
990
991 update_trx_status(
992 p_api_version => l_api_version,
993 p_init_msg_list => p_init_msg_list,
994 x_return_status => x_return_status,
995 x_msg_count => x_msg_count,
996 x_msg_data => x_msg_data,
997 p_chr_id => l_rev_tbl(i).chr_id,
998 p_status => p_tsu_code,
999 x_tcnv_rec => x_tcnv_rec
1000 );
1001
1002 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1003 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Trx UPDATE status: '|| x_return_status);
1004 END IF;
1005
1006 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1007 RAISE OKC_API.G_EXCEPTION_ERROR;
1008 END IF;
1009
1010 --akrangan ebtax rebook impacts starts
1011 IF ( p_source_trx_id IS NULL ) AND ( p_source_trx_name IS NULL ) THEN
1012 l_source_trx_id := x_tcnv_rec.id;
1013 l_source_trx_name := 'Rebook';
1014 -- Bug 6379268
1015 ELSIF (p_source_trx_id IS NOT NULL) AND (p_source_trx_name IS NOT NULL) THEN
1016 l_source_trx_id := p_source_trx_id;
1017 l_source_trx_name := p_source_trx_name;
1018
1019 -- Cancel 'Pre-Rebook' transaction
1020 IF l_source_trx_name = 'Pre-Rebook' THEN
1021 l_prbk_tcnv_rec.id := l_source_trx_id;
1022 l_prbk_tcnv_rec.tsu_code := p_tsu_code;
1023
1024 Okl_Trx_Contracts_Pub.update_trx_contracts(
1025 p_api_version => p_api_version,
1026 p_init_msg_list => p_init_msg_list,
1027 x_return_status => x_return_status,
1028 x_msg_count => x_msg_count,
1029 x_msg_data => x_msg_data,
1030 p_tcnv_rec => l_prbk_tcnv_rec,
1031 x_tcnv_rec => x_prbk_tcnv_rec
1032 );
1033
1034 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1035 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Cancel Pre-Rebook transaction status: '|| x_return_status);
1036 END IF;
1037
1038 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
1039 RAISE Okc_Api.G_EXCEPTION_ERROR;
1040 END IF;
1041 END IF;
1042 ELSE
1043 l_source_trx_id := NULL;
1044 l_source_trx_name := NULL;
1045 END IF;
1046
1047 -- Bug 6379268
1048 IF (l_source_trx_id IS NOT NULL) AND (l_source_trx_name IS NOT NULL) THEN
1049 ---cancel tax lines call
1050 OKL_PROCESS_SALES_TAX_PVT.cancel_document_tax(
1051 p_api_version => l_api_version,
1052 p_init_msg_list => p_init_msg_list,
1053 x_return_status => x_return_status,
1054 x_msg_count => x_msg_count,
1055 x_msg_data => x_msg_data,
1056 p_source_trx_id => l_source_trx_id,
1057 p_source_trx_name => l_source_trx_name,
1058 p_source_table => l_source_table
1059 );
1060 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1061 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Canel Document Tax status: '|| x_return_status);
1062 END IF;
1063
1064 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1065 RAISE OKC_API.G_EXCEPTION_ERROR;
1066 END IF;
1067 END IF;
1068 --akrangan ebtax rebook impacts ends
1069
1070 okl_okc_migration_pvt.update_contract_header(
1071 p_api_version => l_api_version,
1072 p_init_msg_list => p_init_msg_list,
1073 x_return_status => x_return_status,
1074 x_msg_count => x_msg_count,
1075 x_msg_data => x_msg_data,
1076 p_restricted_update => 'F',
1077 p_chrv_rec => l_chrv_rec,
1078 x_chrv_rec => x_chrv_rec
1079 );
1080 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1081 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Contract Header UPDATE status: '|| x_return_status);
1082 END IF;
1083
1084 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1085 RAISE OKC_API.G_EXCEPTION_ERROR;
1086 END IF;
1087
1088 okl_contract_status_pub.cascade_lease_status(
1089 p_api_version => l_api_version,
1090 p_init_msg_list => p_init_msg_list,
1091 x_return_status => x_return_status,
1092 x_msg_count => x_msg_count,
1093 x_msg_data => x_msg_data,
1094 p_chr_id => l_chrv_rec.id
1095 );
1096 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1097 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER Contract Line UPDATE status: '|| x_return_status);
1098 END IF;
1099
1100 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1101 RAISE OKC_API.G_EXCEPTION_ERROR;
1102 END IF;
1103
1104
1105 EXIT WHEN (i >= l_rev_tbl.last);
1106
1107 END LOOP;
1108
1109 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data);
1111
1112 EXCEPTION
1113 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1114 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1115 p_api_name => l_api_name,
1116 p_pkg_name => G_PKG_NAME,
1117 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1118 x_msg_count => x_msg_count,
1119 x_msg_data => x_msg_data,
1120 p_api_type => G_API_TYPE);
1121
1122 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1123 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1124 p_api_name => l_api_name,
1125 p_pkg_name => G_PKG_NAME,
1126 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1127 x_msg_count => x_msg_count,
1128 x_msg_data => x_msg_data,
1129 p_api_type => G_API_TYPE);
1130
1131 WHEN OTHERS THEN
1132 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1133 p_api_name => l_api_name,
1134 p_pkg_name => G_PKG_NAME,
1135 p_exc_name => 'OTHERS',
1136 x_msg_count => x_msg_count,
1137 x_msg_data => x_msg_data,
1138 p_api_type => G_API_TYPE);
1139
1140 END abandon_revisions;
1141
1142 ------------------------------------------------------------------------------
1143 -- PROCEDURE create_service_transaction
1144 --
1145 -- This procedure creates Transaction (LINK/DELINK) during service integration
1146 --
1147 -- Calls:
1148 -- Called By:
1149 ------------------------------------------------------------------------------
1150 PROCEDURE create_service_transaction(
1151 p_api_version IN NUMBER,
1152 p_init_msg_list IN VARCHAR2,
1153 x_return_status OUT NOCOPY VARCHAR2,
1154 x_msg_count OUT NOCOPY NUMBER,
1155 x_msg_data OUT NOCOPY VARCHAR2,
1156 p_lease_id IN OKC_K_HEADERS_V.ID%TYPE,
1157 p_service_id IN OKC_K_HEADERS_V.ID%TYPE,
1158 p_description IN VARCHAR2,
1159 p_trx_date IN DATE,
1160 p_status IN VARCHAR2,
1161 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1162 ) IS
1163
1164 l_api_name VARCHAR2(35) := 'create_service_transaction';
1165 l_proc_name VARCHAR2(35) := 'CREATE_SERVICE_TRANSACTION';
1166 l_api_version CONSTANT NUMBER := 1;
1167
1168 l_tcnv_rec tcnv_rec_type;
1169 l_out_tcnv_rec tcnv_rec_type;
1170
1171 CURSOR try_csr IS
1172 SELECT id
1173 FROM okl_trx_types_tl
1174 WHERE LANGUAGE = 'US'
1175 AND name = 'Service Integration';
1176
1177 CURSOR con_header_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1178 SELECT currency_code,
1179 authoring_org_id
1180 FROM okl_k_headers_full_v
1181 WHERE id = p_chr_id;
1182
1183 l_try_id NUMBER;
1184 service_txn_failed EXCEPTION;
1185 --Added by dpsingh for LE Uptake
1186 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1187 l_legal_entity_id NUMBER;
1188
1189 BEGIN -- main process begins here
1190 IF (G_DEBUG_ENABLED = 'Y') THEN
1191 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1192 END IF;
1193
1194 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1195 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1196 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1197 END IF;
1198 -- call START_ACTIVITY to create savepoint, check compatibility
1199 -- and initialize message list
1200 x_return_status := OKC_API.START_ACTIVITY(
1201 p_api_name => l_api_name,
1202 p_pkg_name => G_PKG_NAME,
1203 p_init_msg_list => p_init_msg_list,
1204 l_api_version => l_api_version,
1205 p_api_version => p_api_version,
1206 p_api_type => G_API_TYPE,
1207 x_return_status => x_return_status);
1208
1209 -- check if activity started successfully
1210 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1212 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1213 RAISE OKC_API.G_EXCEPTION_ERROR;
1214 END IF;
1215
1216 l_tcnv_rec := NULL;
1217 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1218 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER START...');
1219 END IF;
1220 --
1221 -- Check for Transaction Date, it is mandetory
1222 --
1223 IF (p_trx_date IS NULL) THEN
1224 okl_api.set_message(
1225 G_APP_NAME,
1226 G_LLA_MISSING_TRX_DATE
1227 );
1228 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1229 END IF;
1230
1231 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1232 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Afer DATE CHECK');
1233 END IF;
1234
1235 l_try_id := NULL;
1236
1237 OPEN try_csr;
1238 FETCH try_csr INTO l_try_id;
1239 CLOSE try_csr;
1240
1241 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1242 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER getting try id');
1243 END IF;
1244
1245 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1246 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Try ID: '||l_try_id);
1247 END IF;
1248
1249 l_tcnv_rec.try_id := l_try_id;
1250 l_tcnv_rec.tsu_code := p_status;
1251 l_tcnv_rec.description := p_description;
1252 l_tcnv_rec.tcn_type := 'SER';
1253 l_tcnv_rec.khr_id := p_lease_id; -- OKL Contract ID
1254 l_tcnv_rec.chr_id := p_service_id; -- OKS Contract ID
1255 --l_tcnv_rec.khr_id_new := p_new_chr_id;
1256 l_tcnv_rec.date_transaction_occurred := p_trx_date;
1257 --Added by dpsingh for LE Uptake
1258 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_lease_id) ;
1259 IF l_legal_entity_id IS NOT NULL THEN
1260 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
1261 ELSE
1262 -- get the contract number
1263 OPEN contract_num_csr(p_lease_id);
1264 FETCH contract_num_csr INTO l_cntrct_number;
1265 CLOSE contract_num_csr;
1266 Okl_Api.set_message(p_app_name => g_app_name,
1267 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1268 p_token1 => 'CONTRACT_NUMBER',
1269 p_token1_value => l_cntrct_number);
1270 RAISE OKL_API.G_EXCEPTION_ERROR;
1271 END IF;
1272 FOR con_header_rec IN con_header_csr (p_lease_id)
1273 LOOP
1274 l_tcnv_rec.org_id := con_header_rec.authoring_org_id;
1275 l_tcnv_rec.currency_code := con_header_rec.currency_code;
1276 END LOOP;
1277
1278 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1279 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE calling okl_trx_contracts_pub.create_trx_contracts');
1280 END IF;
1281 -- Create Transaction Header only
1282 okl_trx_contracts_pub.create_trx_contracts(
1283 p_api_version => 1.0,
1284 p_init_msg_list => p_init_msg_list,
1285 x_return_status => x_return_status,
1286 x_msg_count => x_msg_count,
1287 x_msg_data => x_msg_data,
1288 p_tcnv_rec => l_tcnv_rec,
1289 x_tcnv_rec => l_out_tcnv_rec
1290 );
1291
1292 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1293 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER calling okl_trx_contracts_pub.create_trx_contracts');
1294 END IF;
1295
1296 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1297 RAISE OKC_API.G_EXCEPTION_ERROR;
1298 END IF;
1299
1300 x_tcnv_rec := l_out_tcnv_rec;
1301
1302 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1303 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done...');
1304 END IF;
1305
1306 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1307 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1308 x_msg_data => x_msg_data);
1309
1310 EXCEPTION
1311
1312 WHEN service_txn_failed THEN
1313 IF try_csr%ISOPEN THEN
1314 CLOSE try_csr;
1315 END IF;
1316 x_return_status := OKL_API.G_RET_STS_ERROR;
1317
1318 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1319 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1320 p_api_name => l_api_name,
1321 p_pkg_name => G_PKG_NAME,
1322 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1323 x_msg_count => x_msg_count,
1324 x_msg_data => x_msg_data,
1325 p_api_type => G_API_TYPE);
1326
1327 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1328 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1329 p_api_name => l_api_name,
1330 p_pkg_name => G_PKG_NAME,
1331 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1332 x_msg_count => x_msg_count,
1333 x_msg_data => x_msg_data,
1334 p_api_type => G_API_TYPE);
1335
1336 WHEN OTHERS THEN
1337 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1338 p_api_name => l_api_name,
1339 p_pkg_name => G_PKG_NAME,
1340 p_exc_name => 'OTHERS',
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data,
1343 p_api_type => G_API_TYPE);
1344
1345 END create_service_transaction;
1346
1347
1348 ------------------------------------------------------------------------------
1349 -- PROCEDURE create_ppd_transaction
1350 --
1351 -- This procedure creates PPD Transaction initiated from customer service
1352 --
1353 -- Calls:
1354 -- Called By:
1355 ------------------------------------------------------------------------------
1356 PROCEDURE create_ppd_transaction(
1357 p_api_version IN NUMBER,
1358 p_init_msg_list IN VARCHAR2,
1359 x_return_status OUT NOCOPY VARCHAR2,
1360 x_msg_count OUT NOCOPY NUMBER,
1361 x_msg_data OUT NOCOPY VARCHAR2,
1362 p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
1363 p_trx_date IN DATE,
1364 p_trx_type IN VARCHAR2,
1365 p_reason_code IN VARCHAR2,
1366 x_tcnv_rec OUT NOCOPY tcnv_rec_type
1367 ) IS
1368
1369 l_api_name VARCHAR2(35) := 'create_ppd_transaction';
1370 l_proc_name VARCHAR2(35) := 'CREATE_PPD_TRANSACTION';
1371 l_api_version CONSTANT NUMBER := 1;
1372
1373 l_tcnv_rec tcnv_rec_type;
1374 l_out_tcnv_rec tcnv_rec_type;
1375
1376 CURSOR try_csr IS
1377 SELECT id
1378 FROM okl_trx_types_tl
1379 WHERE LANGUAGE = 'US'
1380 AND name = 'Principal Paydown';
1381
1382 CURSOR con_header_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
1383 SELECT currency_code,
1384 authoring_org_id
1385 FROM okl_k_headers_full_v
1386 WHERE id = p_chr_id;
1387
1388 l_try_id NUMBER;
1389 ppd_txn_failed EXCEPTION;
1390 --Added by dpsingh for LE Uptake
1391 l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1392 l_legal_entity_id NUMBER;
1393
1394 BEGIN -- main process begins here
1395 IF (G_DEBUG_ENABLED = 'Y') THEN
1396 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1397 END IF;
1398
1399 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1400 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1401 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,l_proc_name);
1402 END IF;
1403 -- call START_ACTIVITY to create savepoint, check compatibility
1404 -- and initialize message list
1405 x_return_status := OKC_API.START_ACTIVITY(
1406 p_api_name => l_api_name,
1407 p_pkg_name => G_PKG_NAME,
1408 p_init_msg_list => p_init_msg_list,
1409 l_api_version => l_api_version,
1410 p_api_version => p_api_version,
1411 p_api_type => G_API_TYPE,
1412 x_return_status => x_return_status);
1413
1414 -- check if activity started successfully
1415 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1418 RAISE OKC_API.G_EXCEPTION_ERROR;
1419 END IF;
1420
1421 l_tcnv_rec := NULL;
1422 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1423 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER START...');
1424 END IF;
1425 --
1426 -- Check for Transaction Date, it is mandetory
1427 --
1428 IF (p_trx_date IS NULL) THEN
1429 okl_api.set_message(
1430 G_APP_NAME,
1431 G_LLA_MISSING_TRX_DATE
1432 );
1433 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1434 END IF;
1435
1436 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1437 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Afer DATE CHECK');
1438 END IF;
1439
1440 l_try_id := NULL;
1441
1442 OPEN try_csr;
1443 FETCH try_csr INTO l_try_id;
1444 IF try_csr%NOTFOUND THEN
1445 RAISE ppd_txn_failed;
1446 END IF;
1447 CLOSE try_csr;
1448
1449 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1450 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER getting try id');
1451 END IF;
1452
1453 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1454 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Try ID: '||l_try_id);
1455 END IF;
1456
1457 l_tcnv_rec.try_id := l_try_id;
1458 l_tcnv_rec.tsu_code := 'ENTERED';
1459 l_tcnv_rec.description := 'Principal Paydown';
1460 l_tcnv_rec.tcn_type := 'PPD';
1461 l_tcnv_rec.khr_id := p_chr_id;
1462 l_tcnv_rec.date_transaction_occurred := p_trx_date;
1463 --Added by dpsingh for LE Uptake
1464 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_chr_id) ;
1465 IF l_legal_entity_id IS NOT NULL THEN
1466 l_tcnv_rec.legal_entity_id := l_legal_entity_id;
1467 ELSE
1468 -- get the contract number
1469 OPEN contract_num_csr(p_chr_id);
1470 FETCH contract_num_csr INTO l_cntrct_number;
1471 CLOSE contract_num_csr;
1472 Okl_Api.set_message(p_app_name => g_app_name,
1473 p_msg_name => 'OKL_LE_NOT_EXIST_CNTRCT',
1474 p_token1 => 'CONTRACT_NUMBER',
1475 p_token1_value => l_cntrct_number);
1476 RAISE OKL_API.G_EXCEPTION_ERROR;
1477 END IF;
1478 FOR con_header_rec IN con_header_csr (p_chr_id)
1479 LOOP
1480 l_tcnv_rec.org_id := con_header_rec.authoring_org_id;
1481 l_tcnv_rec.currency_code := con_header_rec.currency_code;
1482 END LOOP;
1483
1484 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1485 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'BEFORE calling okl_trx_contracts_pub.create_trx_contracts');
1486 END IF;
1487 -- Create Transaction Header only
1488 okl_trx_contracts_pub.create_trx_contracts(
1489 p_api_version => 1.0,
1490 p_init_msg_list => p_init_msg_list,
1491 x_return_status => x_return_status,
1492 x_msg_count => x_msg_count,
1493 x_msg_data => x_msg_data,
1494 p_tcnv_rec => l_tcnv_rec,
1495 x_tcnv_rec => l_out_tcnv_rec
1496 );
1497
1498 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1499 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AFTER calling okl_trx_contracts_pub.create_trx_contracts');
1500 END IF;
1501
1502 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1503 RAISE OKC_API.G_EXCEPTION_ERROR;
1504 END IF;
1505
1506 x_tcnv_rec := l_out_tcnv_rec;
1507
1508 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1509 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done...');
1510 END IF;
1511
1512 --++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1513 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data);
1515
1516 EXCEPTION
1517
1518 WHEN ppd_txn_failed THEN
1519 IF try_csr%ISOPEN THEN
1520 CLOSE try_csr;
1521 END IF;
1522 x_return_status := OKL_API.G_RET_STS_ERROR;
1523
1524 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1525 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1526 p_api_name => l_api_name,
1527 p_pkg_name => G_PKG_NAME,
1528 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
1529 x_msg_count => x_msg_count,
1530 x_msg_data => x_msg_data,
1531 p_api_type => G_API_TYPE);
1532
1533 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1534 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1535 p_api_name => l_api_name,
1536 p_pkg_name => G_PKG_NAME,
1537 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
1538 x_msg_count => x_msg_count,
1539 x_msg_data => x_msg_data,
1540 p_api_type => G_API_TYPE);
1541
1542 WHEN OTHERS THEN
1543 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1544 p_api_name => l_api_name,
1545 p_pkg_name => G_PKG_NAME,
1546 p_exc_name => 'OTHERS',
1547 x_msg_count => x_msg_count,
1548 x_msg_data => x_msg_data,
1549 p_api_type => G_API_TYPE);
1550
1551 END create_ppd_transaction;
1552 END OKL_TRANSACTION_PVT;