[Home] [Help]
PACKAGE BODY: APPS.OKL_CS_LEASE_RENEWAL_PVT
Source
1 PACKAGE BODY OKL_CS_LEASE_RENEWAL_PVT AS
2 /* $Header: OKLRKLRB.pls 120.4 2008/04/22 14:07:35 nikshah ship $ */
3 -------------------------------------------------------------------------------------------------
4 -- GLOBAL MESSAGE CONSTANTS
5 -------------------------------------------------------------------------------------------------
6 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
7 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
8 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_PARENT_TABLE_TOKEN;
9 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_CHILD_TABLE_TOKEN;
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'NO_PARENT_RECORD';
14 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := 'REQUIRED_VALUE';
15
16 ------------------------------------------------------------------------------------
17 -- GLOBAL EXCEPTION
18 ------------------------------------------------------------------------------------
19 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
20 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
21 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
22 G_API_VERSION CONSTANT NUMBER := 1.0;
23 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
24
25 -----------------------------------------------------------------------------------
26 -- GLOBAL VARIABLES
27 -----------------------------------------------------------------------------------
28 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_CS_LEASE_RENEWAL';
29 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
30
31 subtype khrv_rec_type IS OKL_CONTRACT_PUB.khrv_rec_type;
32 subtype chrv_rec_type IS OKL_OKC_MIGRATION_PVT.chrv_rec_type;
33 TYPE kle_rec_type IS RECORD (
34 ID OKL_K_LINES_V.ID%TYPE
35 );
36
37 TYPE kle_tbl_type IS TABLE OF kle_rec_type INDEX BY BINARY_INTEGER;
38
39
40
41 ----------------------------------------------------------------
42 ----------------------------------------------------------------
43
44
45 FUNCTION get_status
46 (p_request_id IN NUMBER)
47 RETURN VARCHAR2
48
49 AS
50
51 CURSOR c_cs_status_csr (a_id IN NUMBER)
52 IS
53 SELECT request_status_code,
54 object1_id1,
55 jtot_object1_code
56 FROM okl_trx_requests
57 where id=a_id;
58
59 CURSOR c_supertrump_csr (a_id IN NUMBER)
60 IS
61 SELECT sis_code
62 FROM okl_stream_interfaces
63 WHERE ID=a_id;
64
65 l_status_code VARCHAR2(80);
66 l_object1_id1 VARCHAR2(40);
67 l_object1_code VARCHAR2(30);
68 l_sis_code VARCHAR2(80);
69
70 BEGIN
71
72 OPEN c_cs_Status_csr(p_request_id);
73 FETCH c_cs_Status_csr INTO l_status_code,l_object1_id1,l_object1_code;
74 CLOSE c_cs_Status_csr;
75
76 IF l_status_code='SUBMITTED_CALCULATION' THEN
77 --Still not processed, Check for the status in Streams table.
78 IF l_object1_code = 'OKL_STREAM_INTERFACES' THEN
79 open c_supertrump_csr(l_object1_id1);
80 FETCH c_supertrump_csr INTO l_sis_code;
81 CLOSE c_supertrump_csr;
82 END IF;
83 l_status_code :=l_sis_code;
84 END IF;
85 RETURN l_status_code;
86
87 END get_status;
88
89
90
91 FUNCTION get_current_lease_values
92 (p_khr_id IN NUMBER)
93 RETURN lease_details_tbl_type
94
95 AS
96
97 -- streams filtered on purpose 'RENT' and amounts summed up
98 -- for user defined streams enhancements, bug 3924303
99 CURSOR c_rent_csr(a_khr_id IN NUMBER)
100 IS
101 select c.amount
102 from
103 okl_streams a,
104 okl_strm_type_b b,
105 okl_strm_elements c
106 where
107 a.sty_id=b.id
108 and b.stream_type_purpose ='RENT'
109 and nvl(b.start_date,sysdate) <= sysdate
110 and nvl(b.end_date,sysdate) >= sysdate
111 and c.stm_id=a.id
112 and a.say_code='CURR'
113 and a.active_yn = 'Y'
114 --multigaap changes
115 AND a.PURPOSE_CODE IS NULL
116 --end multigaap changes
117 and to_char(c.stream_element_date,'MM-YYYY')=to_char(sysdate,'MM-YYYY')
118 and khr_id = a_khr_id;
119
120 CURSOR c_okc_k_hdr_csr(a_khr_id IN NUMBER)
121 IS
122 SELECT start_Date,end_date
123 FROM
124 okc_k_headers_b
125 where id = a_khr_id;
126
127 cursor c_okl_k_hdr_csr(a_khr_id IN NUMBER)
128 IS
129 SELECT term_Duration,after_tax_yield
130 FROM
131 okl_k_headers
132 where id = a_khr_id;
133
134 l_lease_details_tbl lease_details_tbl_type;
135
136 BEGIN
137
138 -- Get current database values
139 OPEN c_rent_csr (p_khr_id);
140 FETCH c_rent_csr INTO l_lease_details_tbl(1).rent;
141 CLOSE c_rent_csr;
142
143 OPEN c_okc_k_hdr_csr (p_khr_id);
144 FETCH c_okc_k_hdr_csr INTO l_lease_details_tbl(1).start_date,
145 l_lease_details_tbl(1).end_date;
146
147 CLOSE c_okc_k_hdr_csr;
148
149 OPEN c_okl_k_hdr_csr (p_khr_id);
150 FETCH c_okl_k_hdr_csr INTO l_lease_details_tbl(1).term_duration,
151 l_lease_details_tbl(1).yield;
152
153 CLOSE c_okl_k_hdr_csr;
154 RETURN(l_lease_details_tbl);
155 END get_current_lease_values;
156
157
158 ------------------------------------------------------------------
159 --Functions to Fetch Data from Tables.
160 -----------------------------------------------------------------
161 ---------------------------------------------------------------------------
162 -- FUNCTION get_chrv_rec for: OKC_K_HEADERS_V
163 ---------------------------------------------------------------------------
164 FUNCTION get_chrv_rec (p_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
165 x_return_status OUT NOCOPY VARCHAR2)
166 RETURN chrv_rec_type IS
167 CURSOR okc_chrv_pk_csr(p_id OKC_K_HEADERS_V.ID%TYPE) IS
168 SELECT ID,
169 OBJECT_VERSION_NUMBER,
170 SFWT_FLAG,
171 CHR_ID_RESPONSE,
172 CHR_ID_AWARD,
173 INV_ORGANIZATION_ID,
174 STS_CODE,
175 QCL_ID,
176 SCS_CODE,
177 CONTRACT_NUMBER,
178 CURRENCY_CODE,
179 CONTRACT_NUMBER_MODIFIER,
180 ARCHIVED_YN,
181 DELETED_YN,
182 CUST_PO_NUMBER_REQ_YN,
183 PRE_PAY_REQ_YN,
184 CUST_PO_NUMBER,
185 SHORT_DESCRIPTION,
186 COMMENTS,
187 DESCRIPTION,
188 DPAS_RATING,
189 COGNOMEN,
190 TEMPLATE_YN,
191 TEMPLATE_USED,
192 DATE_APPROVED,
193 DATETIME_CANCELLED,
194 AUTO_RENEW_DAYS,
195 DATE_ISSUED,
196 DATETIME_RESPONDED,
197 NON_RESPONSE_REASON,
198 NON_RESPONSE_EXPLAIN,
199 RFP_TYPE,
200 CHR_TYPE,
201 KEEP_ON_MAIL_LIST,
202 SET_ASIDE_REASON,
203 SET_ASIDE_PERCENT,
204 RESPONSE_COPIES_REQ,
205 DATE_CLOSE_PROJECTED,
206 DATETIME_PROPOSED,
207 DATE_SIGNED,
208 DATE_TERMINATED,
209 DATE_RENEWED,
210 TRN_CODE,
211 START_DATE,
212 END_DATE,
213 AUTHORING_ORG_ID,
214 BUY_OR_SELL,
215 ISSUE_OR_RECEIVE,
216 ESTIMATED_AMOUNT,
217 ESTIMATED_AMOUNT_RENEWED,
218 CURRENCY_CODE_RENEWED,
219 UPG_ORIG_SYSTEM_REF,
220 UPG_ORIG_SYSTEM_REF_ID,
221 APPLICATION_ID,
222 ORIG_SYSTEM_SOURCE_CODE,
223 ORIG_SYSTEM_ID1,
224 ORIG_SYSTEM_REFERENCE1,
225 ATTRIBUTE_CATEGORY,
226 ATTRIBUTE1,
227 ATTRIBUTE2,
228 ATTRIBUTE3,
229 ATTRIBUTE4,
230 ATTRIBUTE5,
231 ATTRIBUTE6,
232 ATTRIBUTE7,
233 ATTRIBUTE8,
234 ATTRIBUTE9,
235 ATTRIBUTE10,
236 ATTRIBUTE11,
237 ATTRIBUTE12,
238 ATTRIBUTE13,
239 ATTRIBUTE14,
240 ATTRIBUTE15,
241 CREATED_BY,
242 CREATION_DATE,
243 LAST_UPDATED_BY,
244 LAST_UPDATE_DATE,
245 LAST_UPDATE_LOGIN
246 FROM okc_k_headers_v chrv
247 WHERE chrv.id = p_id;
248 x_chrv_rec chrv_rec_type;
249 BEGIN
250 OPEN okc_chrv_pk_csr (p_chr_id);
251 FETCH okc_chrv_pk_csr INTO
252 x_chrv_rec.ID,
253 x_chrv_rec.OBJECT_VERSION_NUMBER,
254 x_chrv_rec.SFWT_FLAG,
255 x_chrv_rec.CHR_ID_RESPONSE,
256 x_chrv_rec.CHR_ID_AWARD,
257 x_chrv_rec.INV_ORGANIZATION_ID,
258 x_chrv_rec.STS_CODE,
259 x_chrv_rec.QCL_ID,
260 x_chrv_rec.SCS_CODE,
261 x_chrv_rec.CONTRACT_NUMBER,
262 x_chrv_rec.CURRENCY_CODE,
263 x_chrv_rec.CONTRACT_NUMBER_MODIFIER,
264 x_chrv_rec.ARCHIVED_YN,
265 x_chrv_rec.DELETED_YN,
266 x_chrv_rec.CUST_PO_NUMBER_REQ_YN,
267 x_chrv_rec.PRE_PAY_REQ_YN,
268 x_chrv_rec.CUST_PO_NUMBER,
269 x_chrv_rec.SHORT_DESCRIPTION,
270 x_chrv_rec.COMMENTS,
271 x_chrv_rec.DESCRIPTION,
272 x_chrv_rec.DPAS_RATING,
273 x_chrv_rec.COGNOMEN,
274 x_chrv_rec.TEMPLATE_YN,
275 x_chrv_rec.TEMPLATE_USED,
276 x_chrv_rec.DATE_APPROVED,
277 x_chrv_rec.DATETIME_CANCELLED,
278 x_chrv_rec.AUTO_RENEW_DAYS,
279 x_chrv_rec.DATE_ISSUED,
280 x_chrv_rec.DATETIME_RESPONDED,
281 x_chrv_rec.NON_RESPONSE_REASON,
282 x_chrv_rec.NON_RESPONSE_EXPLAIN,
283 x_chrv_rec.RFP_TYPE,
284 x_chrv_rec.CHR_TYPE,
285 x_chrv_rec.KEEP_ON_MAIL_LIST,
286 x_chrv_rec.SET_ASIDE_REASON,
287 x_chrv_rec.SET_ASIDE_PERCENT,
288 x_chrv_rec.RESPONSE_COPIES_REQ,
289 x_chrv_rec.DATE_CLOSE_PROJECTED,
290 x_chrv_rec.DATETIME_PROPOSED,
291 x_chrv_rec.DATE_SIGNED,
292 x_chrv_rec.DATE_TERMINATED,
293 x_chrv_rec.DATE_RENEWED,
294 x_chrv_rec.TRN_CODE,
295 x_chrv_rec.START_DATE,
296 x_chrv_rec.END_DATE,
297 x_chrv_rec.AUTHORING_ORG_ID,
298 x_chrv_rec.BUY_OR_SELL,
299 x_chrv_rec.ISSUE_OR_RECEIVE,
300 x_chrv_rec.ESTIMATED_AMOUNT,
301 x_chrv_rec.ESTIMATED_AMOUNT_RENEWED,
302 x_chrv_rec.CURRENCY_CODE_RENEWED,
303 x_chrv_rec.UPG_ORIG_SYSTEM_REF,
304 x_chrv_rec.UPG_ORIG_SYSTEM_REF_ID,
305 x_chrv_rec.APPLICATION_ID,
306 x_chrv_rec.ORIG_SYSTEM_SOURCE_CODE,
307 x_chrv_rec.ORIG_SYSTEM_ID1,
308 x_chrv_rec.ORIG_SYSTEM_REFERENCE1,
309 x_chrv_rec.ATTRIBUTE_CATEGORY,
310 x_chrv_rec.ATTRIBUTE1,
311 x_chrv_rec.ATTRIBUTE2,
312 x_chrv_rec.ATTRIBUTE3,
313 x_chrv_rec.ATTRIBUTE4,
314 x_chrv_rec.ATTRIBUTE5,
315 x_chrv_rec.ATTRIBUTE6,
316 x_chrv_rec.ATTRIBUTE7,
317 x_chrv_rec.ATTRIBUTE8,
318 x_chrv_rec.ATTRIBUTE9,
319 x_chrv_rec.ATTRIBUTE10,
320 x_chrv_rec.ATTRIBUTE11,
321 x_chrv_rec.ATTRIBUTE12,
322 x_chrv_rec.ATTRIBUTE13,
323 x_chrv_rec.ATTRIBUTE14,
324 x_chrv_rec.ATTRIBUTE15,
325 x_chrv_rec.CREATED_BY,
326 x_chrv_rec.CREATION_DATE,
327 x_chrv_rec.LAST_UPDATED_BY,
328 x_chrv_rec.LAST_UPDATE_DATE,
329 x_chrv_rec.LAST_UPDATE_LOGIN;
330 IF okc_chrv_pk_csr%NOTFOUND THEN
331 x_return_status := OKL_API.G_RET_STS_ERROR;
332 END IF;
333 CLOSE okc_chrv_pk_csr;
334 RETURN(x_chrv_rec);
335 EXCEPTION
336 WHEN OTHERS THEN
337 -- store SQL error message on message stack for caller
338 OKL_API.set_message(
339 G_APP_NAME,
340 G_UNEXPECTED_ERROR,
341 'SQLcode',
342 SQLCODE,
343 'SQLerrm',
344 SQLERRM);
345 -- notify caller of an UNEXPECTED error
346 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
347 -- if the cursor is open
348 IF okc_chrv_pk_csr%ISOPEN THEN
349 CLOSE okc_chrv_pk_csr;
350 END IF;
351
352 END get_chrv_rec;
353
354 ---------------------------------------------------------------------------
355 -- FUNCTION get_khrv_rec for: OKL_K_HEADERS_V
356 ---------------------------------------------------------------------------
357 FUNCTION get_khrv_rec (
358 p_khr_id IN OKL_K_HEADERS_V.ID%TYPE,
359 x_return_status OUT NOCOPY VARCHAR2
360 ) RETURN khrv_rec_type IS
361 CURSOR okl_k_headers_v_pk_csr (p_id IN OKL_K_HEADERS_V.ID%TYPE) IS
362 SELECT
363 ID,
364 OBJECT_VERSION_NUMBER,
365 ISG_ID,
366 KHR_ID,
367 PDT_ID,
368 AMD_CODE,
369 DATE_FIRST_ACTIVITY,
370 GENERATE_ACCRUAL_YN,
371 GENERATE_ACCRUAL_OVERRIDE_YN,
372 DATE_REFINANCED,
373 CREDIT_ACT_YN,
374 TERM_DURATION,
375 CONVERTED_ACCOUNT_YN,
376 DATE_CONVERSION_EFFECTIVE,
377 SYNDICATABLE_YN,
378 SALESTYPE_YN,
379 DATE_DEAL_TRANSFERRED,
380 DATETIME_PROPOSAL_EFFECTIVE,
381 DATETIME_PROPOSAL_INEFFECTIVE,
382 DATE_PROPOSAL_ACCEPTED,
383 ATTRIBUTE_CATEGORY,
384 ATTRIBUTE1,
385 ATTRIBUTE2,
386 ATTRIBUTE3,
387 ATTRIBUTE4,
388 ATTRIBUTE5,
389 ATTRIBUTE6,
390 ATTRIBUTE7,
391 ATTRIBUTE8,
392 ATTRIBUTE9,
393 ATTRIBUTE10,
394 ATTRIBUTE11,
395 ATTRIBUTE12,
396 ATTRIBUTE13,
397 ATTRIBUTE14,
398 ATTRIBUTE15,
399 CREATED_BY,
400 CREATION_DATE,
401 LAST_UPDATED_BY,
402 LAST_UPDATE_DATE,
403 LAST_UPDATE_LOGIN,
404 PRE_TAX_YIELD,
405 AFTER_TAX_YIELD,
406 IMPLICIT_INTEREST_RATE,
407 IMPLICIT_NON_IDC_INTEREST_RATE,
408 TARGET_PRE_TAX_YIELD,
409 TARGET_AFTER_TAX_YIELD,
410 TARGET_IMPLICIT_INTEREST_RATE,
411 TARGET_IMPLICIT_NONIDC_INTRATE,
412 DATE_LAST_INTERIM_INTEREST_CAL,
413 DEAL_TYPE,
414 PRE_TAX_IRR,
415 AFTER_TAX_IRR,
416 EXPECTED_DELIVERY_DATE,
417 ACCEPTED_DATE,
418 PREFUNDING_ELIGIBLE_YN,
419 REVOLVING_CREDIT_YN
420 FROM OKL_K_HEADERS_V
421 WHERE OKL_K_HEADERS_V.id = p_id;
422 l_okl_k_headers_v_pk okl_k_headers_v_pk_csr%ROWTYPE;
423 l_khrv_rec khrv_rec_type;
424 BEGIN
425
426 x_return_status := OKL_API.G_RET_STS_SUCCESS;
427 -- Get current database values
428 OPEN okl_k_headers_v_pk_csr (p_khr_id);
429 FETCH okl_k_headers_v_pk_csr INTO
430 l_khrv_rec.ID,
431 l_khrv_rec.OBJECT_VERSION_NUMBER,
432 l_khrv_rec.ISG_ID,
433 l_khrv_rec.KHR_ID,
434 l_khrv_rec.PDT_ID,
435 l_khrv_rec.AMD_CODE,
436 l_khrv_rec.DATE_FIRST_ACTIVITY,
437 l_khrv_rec.GENERATE_ACCRUAL_YN,
438 l_khrv_rec.GENERATE_ACCRUAL_OVERRIDE_YN,
439 l_khrv_rec.DATE_REFINANCED,
440 l_khrv_rec.CREDIT_ACT_YN,
441 l_khrv_rec.TERM_DURATION,
442 l_khrv_rec.CONVERTED_ACCOUNT_YN,
443 l_khrv_rec.DATE_CONVERSION_EFFECTIVE,
444 l_khrv_rec.SYNDICATABLE_YN,
445 l_khrv_rec.SALESTYPE_YN,
446 l_khrv_rec.DATE_DEAL_TRANSFERRED,
447 l_khrv_rec.DATETIME_PROPOSAL_EFFECTIVE,
448 l_khrv_rec.DATETIME_PROPOSAL_INEFFECTIVE,
449 l_khrv_rec.DATE_PROPOSAL_ACCEPTED,
450 l_khrv_rec.ATTRIBUTE_CATEGORY,
451 l_khrv_rec.ATTRIBUTE1,
452 l_khrv_rec.ATTRIBUTE2,
453 l_khrv_rec.ATTRIBUTE3,
454 l_khrv_rec.ATTRIBUTE4,
455 l_khrv_rec.ATTRIBUTE5,
456 l_khrv_rec.ATTRIBUTE6,
457 l_khrv_rec.ATTRIBUTE7,
458 l_khrv_rec.ATTRIBUTE8,
459 l_khrv_rec.ATTRIBUTE9,
460 l_khrv_rec.ATTRIBUTE10,
461 l_khrv_rec.ATTRIBUTE11,
462 l_khrv_rec.ATTRIBUTE12,
463 l_khrv_rec.ATTRIBUTE13,
464 l_khrv_rec.ATTRIBUTE14,
465 l_khrv_rec.ATTRIBUTE15,
466 l_khrv_rec.CREATED_BY,
467 l_khrv_rec.CREATION_DATE,
468 l_khrv_rec.LAST_UPDATED_BY,
469 l_khrv_rec.LAST_UPDATE_DATE,
470 l_khrv_rec.LAST_UPDATE_LOGIN,
471 l_khrv_rec.PRE_TAX_YIELD,
472 l_khrv_rec.AFTER_TAX_YIELD,
473 l_khrv_rec.IMPLICIT_INTEREST_RATE,
474 l_khrv_rec.IMPLICIT_NON_IDC_INTEREST_RATE,
475 l_khrv_rec.TARGET_PRE_TAX_YIELD,
476 l_khrv_rec.TARGET_AFTER_TAX_YIELD,
477 l_khrv_rec.TARGET_IMPLICIT_INTEREST_RATE,
478 l_khrv_rec.TARGET_IMPLICIT_NONIDC_INTRATE,
479 l_khrv_rec.DATE_LAST_INTERIM_INTEREST_CAL,
480 l_khrv_rec.DEAL_TYPE,
481 l_khrv_rec.PRE_TAX_IRR,
482 l_khrv_rec.AFTER_TAX_IRR,
483 l_khrv_rec.EXPECTED_DELIVERY_DATE,
484 l_khrv_rec.ACCEPTED_DATE,
485 l_khrv_rec.PREFUNDING_ELIGIBLE_YN,
486 l_khrv_rec.REVOLVING_CREDIT_YN
487 ;
488 IF okl_k_headers_v_pk_csr%NOTFOUND THEN
489 x_return_status := OKL_API.G_RET_STS_ERROR;
490 END IF;
491 CLOSE okl_k_headers_v_pk_csr;
492 RETURN(l_khrv_rec);
493 EXCEPTION
494 WHEN OTHERS THEN
495 -- store SQL error message on message stack for caller
496 OKL_API.set_message(
497 G_APP_NAME,
498 G_UNEXPECTED_ERROR,
499 'SQLcode',
500 SQLCODE,
501 'SQLerrm',
502 SQLERRM);
503 -- notify caller of an UNEXPECTED error
504 -- if the cursor is open
505 IF okl_k_headers_v_pk_csr%ISOPEN THEN
506 CLOSE okl_k_headers_v_pk_csr;
507 END IF;
508 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
509 END get_khrv_rec;
510
511 PROCEDURE create_working_copy(
512 p_api_version IN NUMBER,
513 p_init_msg_list IN VARCHAR2 := OKL_API.G_FALSE,
514 p_commit IN VARCHAR2 := OKL_API.G_FALSE,
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_msg_count OUT NOCOPY NUMBER,
517 x_msg_data OUT NOCOPY VARCHAR2,
518 p_chr_id IN NUMBER,
519 x_chr_id OUT NOCOPY NUMBER)
520 AS
521 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_WORKING_COPY';
522 l_seq_no NUMBER;
523 l_orig_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
524 l_new_contract_number OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE;
525 l_khrv_rec khrv_rec_type;
526 x_khrv_rec khrv_rec_type;
527 l_chrv_rec chrv_rec_type;
528 x_chrv_rec chrv_rec_type;
529
530
531
532 CURSOR orig_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
533 SELECT contract_number
534 FROM okc_k_headers_v
535 WHERE id = p_chr_id;
536
537
538 BEGIN
539
540 x_return_status := OKL_API.G_RET_STS_SUCCESS;
541
542 --Call start_activity to create savepoint, check compatibility and initialize message list
543
544 x_return_status := OKL_API.START_ACTIVITY(
545 l_api_name
546 ,p_init_msg_list
547 ,'_PVT'
548 ,x_return_status);
549
550 --Check if activity started successfully
551
552 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
553 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
554 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
555 RAISE OKL_API.G_EXCEPTION_ERROR;
556 END IF;
557
558 --Create the working copy here....
559
560 -- Get Sequence Number to generate Contract Number
561 SELECT okl_rbk_seq.nextval
562 INTO l_seq_no
563 FROM DUAL;
564
565 -- Get Contract Number from Original Contract
566 OPEN orig_csr(p_chr_id);
567 FETCH orig_csr INTO l_orig_contract_number;
568
569 IF orig_csr%NOTFOUND THEN
570 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
571 END IF;
572
573 CLOSE orig_csr;
574 l_new_contract_number := l_orig_contract_number||'-LR'||l_seq_no;
575
576 okl_copy_contract_pub.copy_lease_contract(
577 p_api_version => 1.0,
578 p_init_msg_list => OKC_API.G_FALSE,
579 x_return_status => x_return_status,
580 x_msg_count => x_msg_count,
581 x_msg_data => x_msg_data,
582 p_chr_id => p_chr_id,
583 p_contract_number => l_new_contract_number,
584 p_contract_number_modifier => NULL,
585 p_renew_ref_yn => 'N',
586 p_trans_type => 'CRB',
587 x_chr_id => x_chr_id
588 );
589
590 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
591 RAISE OKC_API.G_EXCEPTION_ERROR;
592 END IF;
593 --
594 -- Update Rebook Contract Status to 'NEW'
595 -- and source to 'OKL_REBOOK'
596 --
597 l_khrv_rec.id := x_chr_id;
598 l_chrv_rec.id := x_chr_id;
599 l_chrv_rec.sts_code := 'NEW';
600
601 okl_contract_pub.update_contract_header(
602 p_api_version => 1.0,
603 p_init_msg_list => p_init_msg_list,
604 x_return_status => x_return_status,
605 x_msg_count => x_msg_count,
606 x_msg_data => x_msg_data,
607 p_restricted_update => OKC_API.G_FALSE,
608 p_chrv_rec => l_chrv_rec,
609 p_khrv_rec => l_khrv_rec,
610 x_chrv_rec => x_chrv_rec,
611 x_khrv_rec => x_khrv_rec
612 );
613
614
615 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
616 RAISE OKC_API.G_EXCEPTION_ERROR;
617 END IF;
618
619 --**********************************************************
620 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
621 x_msg_data => x_msg_data);
622 if p_commit= OKC_API.G_TRUE then
623 commit;
624 end if;
625
626 EXCEPTION
627 when OKC_API.G_EXCEPTION_ERROR then
628 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
629 p_api_name => l_api_name,
630 p_pkg_name => G_PKG_NAME,
631 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
632 x_msg_count => x_msg_count,
633 x_msg_data => x_msg_data,
634 p_api_type => '_PVT');
635
636 when OKC_API.G_EXCEPTION_UNEXPECTED_ERROR then
637 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
638 p_api_name => l_api_name,
639 p_pkg_name => G_PKG_NAME,
640 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
641 x_msg_count => x_msg_count,
642 x_msg_data => x_msg_data,
643 p_api_type => '_PVT');
644
645 when OTHERS then
646 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
647 p_api_name => l_api_name,
648 p_pkg_name => G_PKG_NAME,
649 p_exc_name => 'OTHERS',
650 x_msg_count => x_msg_count,
651 x_msg_data => x_msg_data,
652 p_api_type => '_PVT');
653
654 END create_working_copy;
655
656 -----------------------------------------------------------------
657 --API to update the HDR info about the Start date, term and End date for the working copy...
658 ----------------------------------------------------------------
659
660 PROCEDURE update_hdr_info(
661 x_return_status OUT NOCOPY VARCHAR2,
662 x_msg_count OUT NOCOPY NUMBER,
663 x_msg_data OUT NOCOPY VARCHAR2,
664 p_working_copy_chr_id IN OKC_K_HEADERS_V.ID%TYPE,
665 p_start_date IN OKL_K_HEADERS_FULL_V.START_DATE%TYPE,
666 p_end_date IN OKL_K_HEADERS_FULL_V.END_DATE%TYPE,
667 p_term_duration IN OKL_K_HEADERS_FULL_V.TERM_DURATION%TYPE
668 )
669 IS
670
671 l_khrv_rec khrv_rec_type;
672 l_chrv_rec chrv_rec_type;
673 x_khrv_rec khrv_rec_type;
674 x_chrv_rec chrv_rec_type;
675
676 update_failed EXCEPTION;
677 BEGIN
678 x_return_status := OKC_API.G_RET_STS_SUCCESS;
679 l_khrv_rec := get_khrv_rec(
680 p_khr_id => p_working_copy_chr_id,
681 x_return_status => x_return_status
682 );
683 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
684 RAISE update_failed;
685 END IF;
686
687 l_chrv_rec := get_chrv_rec(
688 p_chr_id => p_working_copy_chr_id,
689 x_return_status => x_return_status
690 );
691 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
692 RAISE update_failed;
693 END IF;
694
695
696 l_khrv_rec.term_duration := p_term_duration;
697
698 l_chrv_rec.start_date := p_start_date;
699 l_chrv_rec.end_date := p_end_date;
700
701 okl_contract_pub.update_contract_header(
702 p_api_version => 1.0,
703 p_init_msg_list => OKC_API.G_FALSE,
704 x_return_status => x_return_status,
705 x_msg_count => x_msg_count,
706 x_msg_data => x_msg_data,
707 p_restricted_update => OKC_API.G_FALSE,
708 p_chrv_rec => l_chrv_rec,
709 p_khrv_rec => l_khrv_rec,
710 x_chrv_rec => x_chrv_rec,
711 x_khrv_rec => x_khrv_rec
712 );
713 IF (x_return_status <> Okc_Api.G_RET_STS_SUCCESS) THEN
714 x_return_status := Okc_Api.G_RET_STS_ERROR;
715 RAISE update_failed;
716 END IF;
717
718 --END LOOP;
719
720 --RETURN;
721
722 EXCEPTION
723 WHEN update_failed THEN
724 x_return_status := OKC_API.G_RET_STS_ERROR;
725 END update_hdr_info;
726
727 -------------------------------------------------------------
728 --API to update the residual values for all the assets by a %
729 ------------------------------------------------------------
730 PROCEDURE update_residual_value(
731 x_return_status OUT NOCOPY VARCHAR2,
732 x_msg_count OUT NOCOPY NUMBER,
733 x_msg_data OUT NOCOPY VARCHAR2,
734 p_chr_id IN NUMBER,
735 p_reduce_residual_ptg_by IN NUMBER
736 ) IS
737 l_api_name VARCHAR2(35) := 'update_residual_value';
738 l_api_version NUMBER := 1.0;
739
740 CURSOR rv_csr (p_top_line_id OKC_K_LINES_V.ID%TYPE) IS
741 SELECT residual_value
742 FROM okl_k_lines_full_v
743 WHERE id = p_top_line_id;
744
745 CURSOR kle_id_csr(c_chr_id NUMBER) IS
746 SELECT line.id
747 FROM okl_k_lines_full_v line,
748 okc_line_styles_v style
749 WHERE dnz_chr_id = c_chr_id
750 AND style.lty_code = 'FREE_FORM1'
751 AND line.lse_id = style.id;
752
753 l_old_rv NUMBER;
754 l_new_rv NUMBER;
755 l_reduce_by NUMBER;
756 l_residual_ptg NUMBER;
757 l_klev_tbl klev_tbl_type;
758 l_clev_tbl clev_tbl_type;
759
760 x_klev_tbl klev_tbl_type;
761 x_clev_tbl clev_tbl_type;
762
763 p_kle_tbl kle_tbl_type;
764 l_line_count NUMBER:=0;
765
766 BEGIN
767
768 x_return_status := OKL_API.G_RET_STS_SUCCESS;
769
770 --Should i calculate the new Residual Percentage?
771
772 FOR kle_rec in kle_id_csr(p_chr_id)
773 LOOP
774
775 l_line_count := l_line_count + 1;
776 p_kle_tbl(l_line_count).id := kle_rec.id;
777 END LOOP;
778
779 --Before this populate the kle_tble based on the chr_id passed in.
780
781 FOR i IN 1..p_kle_tbl.COUNT
782
783
784 LOOP
785 l_klev_tbl(i).id := p_kle_tbl(i).id;
786 l_clev_tbl(i).id := p_kle_tbl(i).id;
787
788 l_old_rv := 0;
789 OPEN rv_csr (p_kle_tbl(i).id);
790 FETCH rv_csr INTO l_old_rv;
791 CLOSE rv_csr;
792 IF (l_old_rv <> 0) THEN
793
794 l_reduce_by := p_reduce_residual_ptg_by * l_old_rv/100;
795 l_new_rv := l_old_rv - l_reduce_by;
796
797 END IF;
798
799 l_klev_tbl(i).residual_value := l_new_rv;
800
801 END LOOP;
802
803 okl_contract_pub.update_contract_line(
804 p_api_version => 1.0,
805 p_init_msg_list => OKC_API.G_FALSE,
806 x_return_status => x_return_status,
807 x_msg_count => x_msg_count,
808 x_msg_data => x_msg_data,
809 p_clev_tbl => l_clev_tbl,
810 p_klev_tbl => l_klev_tbl,
811 x_clev_tbl => x_clev_tbl,
812 x_klev_tbl => x_klev_tbl
813 );
814
815
816 RETURN; -- handle error, if any, at calling block
817
818 END update_residual_value;
819
820
821
822
823
824 --*****************************************************88
825
826 --Call the extraction API to extract info for the working copy.
827 -- Populate the parameters, i.e Tweak with the output params....
828
829 --*****************************************************88
830 PROCEDURE calculate(
831 p_api_version IN NUMBER,
832 p_init_msg_list IN VARCHAR2 := OKL_API.G_FALSE,
833 x_return_status OUT NOCOPY VARCHAR2,
834 x_msg_count OUT NOCOPY NUMBER,
835 x_msg_data OUT NOCOPY VARCHAR2,
836 p_trqv_tbl IN okl_trx_requests_pub.trqv_tbl_type,
837 x_trqv_tbl OUT NOCOPY okl_trx_requests_pub.trqv_tbl_type)
838 AS
839 l_api_name CONSTANT VARCHAR2(30) := 'CALCULATE';
840 p_chr_id NUMBER;
841 l_trans_id NUMBER;
842 l_trans_status VARCHAR2(100);
843 l_trqv_tbl okl_trx_requests_pub.trqv_tbl_type;
844 l_upd_trqv_tbl okl_trx_requests_pub.trqv_tbl_type;
845
846 l_csm_lease_header Okl_Create_Streams_Pub.csm_lease_rec_type;
847 l_csm_one_off_fee_tbl Okl_Create_Streams_Pub.csm_one_off_fee_tbl_type;
848 l_csm_periodic_expenses_tbl Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
849 l_csm_yields_tbl Okl_Create_Streams_Pub.csm_yields_tbl_type;
850 l_csm_stream_types_tbl Okl_Create_Streams_Pub.csm_stream_types_tbl_type;
851 l_req_stream_types_tbl Okl_Create_Streams_Pub.csm_stream_types_tbl_type;
852 l_csm_line_details_tbl Okl_Create_Streams_Pub.csm_line_details_tbl_type;
853 l_rents_tbl Okl_Create_Streams_Pub.csm_periodic_expenses_tbl_type;
854 l_reduce_residual_ptg_by NUMBER;
855 l_current_term NUMBER;
856 l_new_term NUMBER;
857 l_current_lease_attribs lease_details_tbl_type;
858
859 l_object_version_number NUMBER;
860 l_request_status_code OKL_TRX_REQUESTS.REQUEST_STATUS_CODE%TYPE;
861
862 CURSOR c_get_req_details(p_req_id NUMBER) IS
863 SELECT OBJECT_VERSION_NUMBER, REQUEST_STATUS_CODE
864 FROM OKL_TRX_REQUESTS
865 WHERE ID = p_req_id;
866
867 BEGIN
868 x_return_status := OKL_API.G_RET_STS_SUCCESS;
869
870 --Call start_activity to create savepoint, check compatibility and initialize message list
871
872 x_return_status := OKL_API.START_ACTIVITY(
873 l_api_name
874 ,p_init_msg_list
875 ,'_PVT'
876 ,x_return_status);
877
878 --Check if activity started successfully
879
880 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
881 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
882 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
883 RAISE OKL_API.G_EXCEPTION_ERROR;
884 END IF;
885 IF p_trqv_tbl(1).id IS NULL THEN
886 -- Call the public API for insertion here.
887 okl_trx_requests_pub.insert_trx_requests(
888 p_api_version => p_api_version,
889 p_init_msg_list =>p_init_msg_list,
890 x_return_status => x_return_status,
891 x_msg_count => x_msg_count,
892 x_msg_data => x_msg_data,
893 p_trqv_tbl => p_trqv_tbl,
894 x_trqv_tbl => x_trqv_tbl);
895
896
897 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
898 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
899 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
900 RAISE OKL_API.G_EXCEPTION_ERROR;
901 END IF;
902 ELSE
903 l_upd_trqv_tbl := p_trqv_tbl;
904
905 --Reset the params before calling the API
906 l_upd_trqv_tbl(1).amount := NULL;
907 l_upd_trqv_tbl(1).request_status_code := 'ENTERED';
908
909 --Call update here.
910 okl_trx_requests_pub.update_trx_requests(
911 p_api_version => p_api_version,
912 p_init_msg_list =>p_init_msg_list,
913 x_return_status => x_return_status,
914 x_msg_count => x_msg_count,
915 x_msg_data => x_msg_data,
916 p_trqv_tbl => l_upd_trqv_tbl,
917 x_trqv_tbl => x_trqv_tbl);
918
919 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
921 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
922 RAISE OKL_API.G_EXCEPTION_ERROR;
923 END IF;
924 END IF;
925
926 p_chr_id := p_trqv_tbl(1).dnz_khr_id;
927 l_current_lease_attribs := get_current_lease_values(p_trqv_tbl(1).parent_khr_id);
928
929 l_new_term := l_current_lease_attribs(1).term_duration + p_trqv_tbl(1).term_duration;
930 -- Update the Hdr info
931 update_hdr_info( x_return_status => x_return_status,
932 x_msg_count => x_msg_count,
933 x_msg_data => x_msg_data,
934 p_working_copy_chr_id => p_chr_id,
935 p_start_date => l_current_lease_attribs(1).start_date,
936 p_end_date => p_trqv_tbl(1).end_date,
937 p_term_duration => l_new_term );
938
939 --This needs to be changed.
940 l_reduce_residual_ptg_by := p_trqv_tbl(1).residual;
941 --Update the Residual Values
942 update_residual_value(
943 x_return_status => x_return_status,
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data,
946 p_chr_id => p_chr_id,
947 p_reduce_residual_ptg_by => l_reduce_residual_ptg_by
948 );
949
950 -- CAll Extraction API
951 OKL_LA_STREAM_PUB.EXTRACT_PARAMS_LEASE(
952 p_api_version => p_api_version,
953 p_init_msg_list => p_init_msg_list,
954 p_chr_id => p_chr_id,
955 x_return_status => x_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data,
958 x_csm_lease_header => l_csm_lease_header,
959 x_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
960 x_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
961 x_csm_yields_tbl => l_csm_yields_tbl,
962 x_req_stream_types_tbl => l_req_stream_types_tbl,
963 x_csm_line_details_tbl => l_csm_line_details_tbl,
964 x_rents_tbl => l_rents_tbl);
965
966 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
967 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
968 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
969 RAISE OKL_API.G_EXCEPTION_ERROR;
970 END IF;
971 --Fine tune the params
972 --x_csm_lease_header
973 l_csm_lease_header.jtot_object1_code := 'OKL_TRX_REQUESTS';
974 l_csm_lease_header.object1_id1 := x_trqv_tbl(1).id;
975 l_csm_lease_header.orp_code := OKL_CREATE_STREAMS_PUB.G_ORP_CODE_RENEWAL;
976
977 --Check the following with Susan.
978 l_csm_lease_header.adjust := 'Rent';
979 l_csm_lease_header.adjustment_method := 'Proportional';
980
981
982
983 --x_csm_yields_tbl
984 l_csm_yields_tbl(3).target_value := p_trqv_tbl(1).yield;
985
986 --x_rents_tbl
987 --Delete the values that are already in the rents table and
988 --repopulate the values.
989
990 l_rents_tbl.delete;
991 l_rents_tbl(1).description := 'RENT';
992 l_rents_tbl(1).number_of_periods := l_current_lease_attribs(1).term_duration;
993 l_rents_tbl(1).amount := NVL(l_current_lease_attribs(1).rent,0);
994 l_rents_tbl(1).lock_level_step := OKL_CREATE_STREAMS_PUB.G_LOCK_AMOUNT;
995
996 --Check the following with Susan
997 l_rents_tbl(1).period := 'M';
998 l_rents_tbl(1).level_index_number := 1;
999 l_rents_tbl(1).level_type := OKL_CREATE_STREAMS_PUB.G_SFE_LEVEL_PAYMENT;
1000 l_rents_tbl(1).advance_or_arrears := OKL_CREATE_STREAMS_PUB.G_ADVANCE;
1001 l_rents_tbl(1).income_or_expense := OKL_CREATE_STREAMS_PUB.G_INCOME;
1002 l_rents_tbl(1).date_start := l_current_lease_attribs(1).start_date;
1003
1004 --Level 2 for new amount:
1005 l_rents_tbl(2).description := 'RENT';
1006 l_rents_tbl(2).number_of_periods := p_trqv_tbl(1).term_duration;
1007 l_rents_tbl(2).amount := 0;
1008 l_rents_tbl(2).lock_level_step := NULL; --OKL_CREATE_STREAMS_PUB.G_LOCK_AMOUNT;
1009 l_rents_tbl(2).query_level_yn := 'Y';
1010
1011 --Check the following with Susan
1012 l_rents_tbl(2).period := 'M';
1013 l_rents_tbl(2).level_index_number := 2;
1014 l_rents_tbl(2).level_type := OKL_CREATE_STREAMS_PUB.G_SFE_LEVEL_PAYMENT;
1015 l_rents_tbl(2).advance_or_arrears := OKL_CREATE_STREAMS_PUB.G_ADVANCE; -- IS this hard coding Okay.. Check with Susan.
1016 l_rents_tbl(2).income_or_expense := OKL_CREATE_STREAMS_PUB.G_INCOME;
1017 l_rents_tbl(2).date_start := p_trqv_tbl(1).start_date;
1018
1019
1020
1021 --Call Supertrump API to submit request.
1022 Okl_Create_Streams_Pub.CREATE_STREAMS_LEASE_RESTR(
1023 p_api_version => p_api_version,
1024 p_init_msg_list => p_init_msg_list,
1025 x_return_status => x_return_status,
1026 x_msg_count => x_msg_count,
1027 x_msg_data => x_msg_data,
1028 p_csm_lease_header => l_csm_lease_header,
1029 p_csm_one_off_fee_tbl => l_csm_one_off_fee_tbl,
1030 p_csm_periodic_expenses_tbl => l_csm_periodic_expenses_tbl,
1031 p_csm_yields_tbl => l_csm_yields_tbl,
1032 p_csm_stream_types_tbl => l_csm_stream_types_tbl,
1033 p_csm_line_details_tbl => l_csm_line_details_tbl,
1034 p_rents_tbl => l_rents_tbl,
1035 x_trans_id => l_trans_id,
1036 x_trans_status => l_trans_status);
1037
1038 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1039 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1040 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1041 RAISE OKL_API.G_EXCEPTION_ERROR;
1042 END IF;
1043
1044 OPEN c_get_req_details(x_trqv_tbl(1).id);
1045 FETCH c_get_req_details INTO l_object_version_number, l_request_status_code;
1046 CLOSE c_get_req_details;
1047
1048 --Now update the Status and the Transaction id in the CS Request Table.
1049 IF l_request_status_code <> 'COMPLETE' THEN
1050 l_trqv_tbl(1).request_status_code := 'PRICING';
1051 END IF;
1052
1053 l_trqv_tbl(1).id := x_trqv_tbl(1).id;
1054 l_trqv_tbl(1).object_version_number := l_object_version_number;
1055 l_trqv_tbl(1).jtot_object1_code := 'OKL_STREAM_INTERFACES';
1056 l_trqv_tbl(1).object1_id1 := l_trans_id;
1057
1058 --Call update here.
1059 okl_trx_requests_pub.update_trx_requests(
1060 p_api_version => p_api_version,
1061 p_init_msg_list =>p_init_msg_list,
1062 x_return_status => x_return_status,
1063 x_msg_count => x_msg_count,
1064 x_msg_data => x_msg_data,
1065 p_trqv_tbl => l_trqv_tbl,
1066 x_trqv_tbl => x_trqv_tbl);
1067
1068 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1069 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1070 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1071 RAISE OKL_API.G_EXCEPTION_ERROR;
1072 END IF;
1073
1074 --Bug # 6595451 ssdeshpa start
1075 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count
1076 ,x_msg_data => x_msg_data);
1077 --Bug # 6595451 ssdeshpa End
1078
1079 EXCEPTION
1080 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1081 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1082 l_api_name,
1083 G_PKG_NAME,
1084 'OKL_API.G_RET_STS_ERROR',
1085 x_msg_count,
1086 x_msg_data,
1087 '_PVT');
1088 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1089 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1090 l_api_name,
1091 G_PKG_NAME,
1092 'OKL_API.G_RET_STS_UNEXP_ERROR',
1093 x_msg_count,
1094 x_msg_data,
1095 '_PVT');
1096 WHEN OTHERS THEN
1097 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1098 l_api_name,
1099 G_PKG_NAME,
1100 'OTHERS',
1101 x_msg_count,
1102 x_msg_data,
1103 '_PVT');
1104
1105
1106 END calculate;
1107
1108 PROCEDURE update_lrnw_request(
1109 p_api_version IN NUMBER
1110 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1111 ,x_return_status OUT NOCOPY VARCHAR2
1112 ,x_msg_count OUT NOCOPY NUMBER
1113 ,x_msg_data OUT NOCOPY VARCHAR2
1114 ,p_trqv_rec IN okl_trx_requests_pub.trqv_rec_type
1115 ,x_trqv_rec OUT NOCOPY okl_trx_requests_pub.trqv_rec_type)
1116 AS
1117
1118 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LRNW_REQUEST';
1119 l_trqv_rec okl_trx_requests_pub.trqv_rec_type := p_trqv_rec;
1120
1121 CURSOR c_obj_vers_csr (a_id NUMBER)
1122 IS
1123 SELECT object_Version_number
1124 FROM okl_trx_requests
1125 WHERE id=a_id;
1126
1127 BEGIN
1128
1129 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1130
1131 --Call start_activity to create savepoint, check compatibility and initialize message list
1132
1133 x_return_status := OKL_API.START_ACTIVITY(
1134 l_api_name
1135 ,p_init_msg_list
1136 ,'_PVT'
1137 ,x_return_status);
1138
1139 --Check if activity started successfully
1140
1141 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1142 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1143 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1144 RAISE OKL_API.G_EXCEPTION_ERROR;
1145 END IF;
1146
1147 OPEN c_obj_vers_csr(l_trqv_rec.id);
1148 FETCH c_obj_vers_csr INTO l_trqv_rec.object_Version_number;
1149 CLOSE c_obj_vers_csr;
1150
1151 --Check the Status that is being passed in and decode that status before callin
1152 -- the update API.
1153 --The status should be present in the FND_LOOKUP.
1154
1155
1156 IF (l_trqv_rec.request_status_code = 'PROCESS_COMPLETE' ) THEN
1157 l_trqv_rec.request_status_code := 'COMPLETE';
1158 ELSIF (l_trqv_rec.request_status_code = 'PROCESS_COMPLETE_ERROR' ) THEN
1159 l_trqv_rec.request_status_code := 'INCOMPLETE';
1160 END IF;
1161
1162
1163 -- Call the public API for updation here.
1164 okl_trx_requests_pub.update_trx_requests(
1165 p_api_version => p_api_version,
1166 p_init_msg_list =>p_init_msg_list,
1167 x_return_status => x_return_status,
1168 x_msg_count => x_msg_count,
1169 x_msg_data => x_msg_data,
1170 p_trqv_rec => l_trqv_rec,
1171 x_trqv_rec => x_trqv_rec);
1172
1173
1174 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1175 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1176 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1177 RAISE OKL_API.G_EXCEPTION_ERROR;
1178 END IF;
1179
1180 EXCEPTION
1181 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1182 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1183 l_api_name,
1184 G_PKG_NAME,
1185 'OKL_API.G_RET_STS_ERROR',
1186 x_msg_count,
1187 x_msg_data,
1188 '_PVT');
1189 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1190 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1191 l_api_name,
1192 G_PKG_NAME,
1193 'OKL_API.G_RET_STS_UNEXP_ERROR',
1194 x_msg_count,
1195 x_msg_data,
1196 '_PVT');
1197 WHEN OTHERS THEN
1198 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1199 l_api_name,
1200 G_PKG_NAME,
1201 'OTHERS',
1202 x_msg_count,
1203 x_msg_data,
1204 '_PVT');
1205
1206
1207 END update_lrnw_request;
1208
1209
1210 END OKL_CS_LEASE_RENEWAL_PVT;