[Home] [Help]
PACKAGE BODY: APPS.OKL_CONTRACT_STATUS_PVT
Source
1 Package body okl_contract_status_pvt as
2 /* $Header: OKLRSTKB.pls 120.9.12010000.2 2008/10/01 22:45:32 rkuttiya ship $ */
3
4 -------------------------------------------------------------------------------------------------
5 -- GLOBAL MESSAGE CONSTANTS
6 -------------------------------------------------------------------------------------------------
7 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKL_NO_PARENT_RECORD';
8 G_FND_APP CONSTANT VARCHAR2(200) := OKL_API.G_FND_APP;
9 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_REQUIRED_VALUE;
10 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKL_API.G_INVALID_VALUE;
11 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UNEXP_ERROR';
12 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
13 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
14 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKL_CONTRACTS_UPPERCASE_REQ';
15 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKL_API.G_COL_NAME_TOKEN;
16 --
17 G_MISSING_CONTRACT CONSTANT Varchar2(200) := 'OKL_LLA_CONTRACT_NOT_FOUND';
18 G_CONTRACT_ID_TOKEN CONSTANT Varchar2(30) := 'CONTRACT_ID';
19 ------------------------------------------------------------------------------------
20 -- GLOBAL EXCEPTION
21 ------------------------------------------------------------------------------------
22 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
23 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
24 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
25 G_API_VERSION CONSTANT NUMBER := 1.0;
26 G_SCOPE CONSTANT VARCHAR2(4) := '_PVT';
27 G_BULK_BATCH_SIZE CONSTANT NUMBER := 10000;
28
29 TYPE clev_tbl_id_type IS TABLE OF OKC_K_LINES_B.ID%TYPE INDEX BY BINARY_INTEGER;
30 TYPE clev_tbl_sts_type IS TABLE OF OKC_K_LINES_B.STS_CODE%TYPE INDEX BY BINARY_INTEGER;
31 TYPE clev_tbl_start_date_type IS TABLE OF OKC_K_LINES_B.START_DATE%TYPE INDEX BY BINARY_INTEGER;
32 TYPE clev_tbl_end_date_type IS TABLE OF OKC_K_LINES_B.END_DATE%TYPE INDEX BY BINARY_INTEGER;
33 TYPE clev_tbl_currency_code_type IS TABLE OF OKC_K_LINES_B.CURRENCY_CODE%TYPE INDEX BY BINARY_INTEGER;
34
35 -- GLOBAL VARIABLES
36 -----------------------------------------------------------------------------------
37
38
39
40 function areStreamsRequested(p_chr_id NUMBER) RETURN VARCHAR2 is
41
42 l_areStreamsReq VARCHAR2(2) := OKL_API.G_FALSE;
43 l_areStreamsReqRec VARCHAR2(1) := 'N';
44
45 cursor stm_csr( chrId NUMBER ) is
46 select 'Y'
47 from dual
48 where exists (
49 select 'X' from okl_trx_contracts ktx
50 where ktx.KHR_ID = chrId
51 and UPPER(ktx.TSU_CODE) = 'WORKING'
52 --rkuttiya added for 12.1.1 Multi GAAP
53 and ktx.representation_type = 'PRIMARY'
54 --
55 and UPPER(ktx.TCN_TYPE) = 'YIELDS');
56
57 BEGIN
58
59 /*
60 open stm_csr( p_chr_id);
61 fetch stm_csr into l_areStreamsReqRec;
62 close stm_csr;
63
64 if ( l_areStreamsReqRec = 'Y' ) then
65 l_areStreamsReq := OKL_API.G_TRUE;
66 okl_api.set_message(
67 p_app_name => G_APP_NAME,
68 p_msg_name => OKL_CONTRACT_STATUS_PUB.G_STRMS_IN_PROGRESS);
69 end if;
70
71
72 */
73 NULL;
74 return l_areStreamsReq;
75
76 END areStreamsRequested;
77
78 Procedure get_loan_status( p_chr_id IN NUMBER,
79 p_event IN VARCHAR2,
80 okl_status IN VARCHAR2,
81 okc_status IN VARCHAR2,
82 x_PassStatus OUT NOCOPY VARCHAR2,
83 x_FailStatus OUT NOCOPY VARCHAR2,
84 isAllowed OUT NOCOPY BOOLEAN) IS
85
86 --Bug# 4502754
87 --cursor to check for vendor program template
88 CURSOR l_chk_template_csr (p_chr_id IN Number) IS
89 SELECT chr.template_yn,
90 khr.template_type_code
91 FROM okc_k_headers_b chr,
92 okl_k_headers khr
93 WHERE chr.id = p_chr_id
94 AND chr.id = khr.id;
95
96 l_chk_template_rec l_chk_template_csr%ROWTYPE;
97
98 BEGIN
99
100 x_PassStatus := NULL;
101 x_FailStatus := NULL;
102 isAllowed := FALSE;
103
104 --Bug# 4502754
105 OPEN l_chk_template_csr(p_chr_id => p_chr_id);
106 FETCH l_chk_template_csr INTO l_chk_template_rec;
107 CLOSE l_chk_template_csr;
108
109 IF ( p_event = G_K_NEW ) THEN
110 x_PassStatus := 'NEW';
111 x_FailStatus := NULL;
112 isAllowed := TRUE;
113 ELSIF ( p_event = G_K_EDIT ) THEN
114 if( (okc_status = 'ENTERED') OR
115 (okc_status = 'SIGNED') OR
116 (okc_status = 'ACTIVE') ) then
117
118 if(( okl_status = 'PASSED' ) AND
119 (areStreamsRequested( p_chr_id ) = OKL_API.G_TRUE )) then
120 return;
121 end if;
122
123 x_PassStatus := 'INCOMPLETE';
124 x_FailStatus := NULL;
125 isAllowed := TRUE;
126
127 end if;
128 ELSIF ( p_event = G_K_QACHECK ) THEN
129 if((okc_status='ENTERED' OR okc_status='SIGNED') AND
130 (okl_status='NEW' OR okl_status='PASSED' OR okl_status='INCOMPLETE') )then
131 x_PassStatus := 'PASSED';
132 x_FailStatus := 'INCOMPLETE';
133 isAllowed := TRUE;
134 end if;
135 ELSIF ( p_event = G_K_STRMGEN ) THEN
136 if( okc_status='ENTERED'
137 AND (okl_status='PASSED' OR okl_status='COMPLETE')
138 AND (areStreamsRequested(p_chr_id) = OKL_API.G_FALSE)) then
139 x_PassStatus := 'COMPLETE';
140 x_FailStatus := 'PASSED';
141 isAllowed := TRUE;
142 end if;
143 ELSIF ( p_event = G_K_JOURNAL ) THEN
144 if(okc_status='ENTERED' AND okl_status='COMPLETE') then
145 x_PassStatus := 'COMPLETE';
146 x_FailStatus := 'PASSED';
147 isAllowed := TRUE;
148 end if;
149 ELSIF ( p_event = G_K_SUBMIT4APPRVL ) THEN
150
151 --Bug# 4502754
152 -- For Vendor Program Template, allow Submit for
153 -- Approval event when the template status is PASSED
154
155 if (l_chk_template_rec.template_yn = 'Y' AND
156 --Bug# 4874338
157 nvl(l_chk_template_rec.template_type_code,okl_api.g_miss_char) in ('PROGRAM','LEASEAPP')) THEN
158 if(okc_status='ENTERED' AND okl_status='PASSED') then
159 x_PassStatus := 'PENDING_APPROVAL';
160 x_FailStatus := 'PASSED';
161 isAllowed := TRUE;
162 end if;
163 else
164 if(okc_status='ENTERED' AND okl_status='COMPLETE') then
165 x_PassStatus := 'PENDING_APPROVAL';
166 x_FailStatus := 'COMPLETE';
167 isAllowed := TRUE;
168 end if;
169 end if;
170 ELSIF ( p_event = G_K_APPROVAL ) THEN
171 if(okc_status='SIGNED' AND okl_status='PENDING_APPROVAL') then
172 x_PassStatus := 'APPROVED';
173 x_FailStatus := 'PENDING_APPROVAL';
174 isAllowed := TRUE;
175 end if;
176 ELSIF ( p_event = G_K_ACTIVATE ) THEN
177 if(okc_status='SIGNED' AND okl_status='APPROVED') then
178 x_PassStatus := 'BOOKED';
179 x_FailStatus := 'APPROVED';
180 isAllowed := TRUE;
181 end if;
182 END IF;
183
184 END get_loan_status;
185
186 Procedure get_lease_status( p_chr_id IN NUMBER,
187 p_event IN VARCHAR2,
188 okl_status IN VARCHAR2,
189 okc_status IN VARCHAR2,
190 x_PassStatus OUT NOCOPY VARCHAR2,
191 x_FailStatus OUT NOCOPY VARCHAR2,
192 isAllowed OUT NOCOPY BOOLEAN) IS
193
194 BEGIN
195
196 get_loan_status(p_chr_id,
197 p_event,
198 okl_status,
199 okc_status,
200 x_PassStatus,
201 x_FailStatus,
202 isAllowed);
203
204 END get_lease_status;
205
206 Procedure get_contract_status(
207 p_api_version IN NUMBER,
208 p_init_msg_list IN VARCHAR2,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2,
212 x_isAllowed OUT NOCOPY BOOLEAN,
213 x_PassStatus OUT NOCOPY VARCHAR2,
214 x_FailStatus OUT NOCOPY VARCHAR2,
215 p_event IN VARCHAR2,
216 p_chr_id IN VARCHAR2) IS
217
218 l_api_name CONSTANT VARCHAR2(30) := 'RETURN_CONTRACT_STATUS';
219 l_api_version CONSTANT NUMBER := 1;
220 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
221
222
223 CURSOR sts_csr( chrId NUMBER) IS
224 select a.ste_code okc_status,
225 a.code okl_status,
226 b.template_yn template_yn,
227 --Bug# 4502754
228 c.template_type_code
229 from okc_statuses_v a,
230 okc_k_headers_b b,
231 okl_k_headers c
232 where a.code = b.sts_code
233 and b.id = c.id
234 and b.id = chrId;
235
236 CURSOR chr_csr(p_chr_id OKL_K_HEADERS.KHR_ID%TYPE) IS
237 SELECT chr.SCS_CODE class,
238 chr.STS_CODE okc_status
239 FROM OKC_K_HEADERS_B chr,
240 OKL_K_HEADERS khr
241 WHERE chr.id = p_chr_id
242 AND chr.id = khr.id;
243
244 CURSOR Product_csr (p_contract_id IN NUMBER ) IS
245 SELECT pdt.id product_id
246 ,pdt.name product_name
247 ,chr.sts_code okl_status
248 FROM okl_products_v pdt
249 ,okl_k_headers khr
250 ,okc_k_headers_b chr
251 WHERE 1=1
252 AND khr.id = p_contract_id
253 AND khr.pdt_id = pdt.id
254 AND khr.id = chr.id;
255
256 l_Product_rec Product_csr%ROWTYPE;
257 l_chr_rec chr_csr%ROWTYPE;
258 l_sts_rec sts_csr%ROWTYPE;
259
260 l_okl_status VARCHAR2(100);
261 l_okc_status VARCHAR2(100);
262
263 BEGIN
264
265
266
267 x_return_status := OKL_API.G_RET_STS_SUCCESS;
268
269 x_isAllowed := FALSE;
270 x_PassStatus := NULL;
271 x_FailStatus := NULL;
272
273
274 -- 2. type of contract lease/loan
275 OPEN chr_csr(p_chr_id);
276 FETCH chr_csr into l_chr_rec;
277 IF chr_csr%NOTFOUND THEN
278 RAISE G_EXCEPTION_STOP_VALIDATION;
279 END IF;
280 CLOSE chr_csr;
281
282 OPEN sts_csr(p_chr_id);
283 FETCH sts_csr INTO l_sts_rec;
284 IF sts_csr%NOTFOUND THEN
285 Okl_Api.SET_MESSAGE(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'CHR_ID');
286 CLOSE sts_csr;
287 RAISE Okl_Api.G_EXCEPTION_ERROR;
288 END IF;
289 CLOSE sts_csr;
290
291
292 l_okc_status := l_sts_rec.okc_status;
293 l_okl_status := l_sts_rec.okl_status;
294
295
296 --Bug# 4502754
297 -- Allow Vendor Program Templates to be activated
298 IF ((UPPER(l_sts_rec.template_yn) = 'Y') AND
299 --Bug# 4874338
300 (NVL(l_sts_rec.template_type_code,OKL_API.G_MISS_CHAR) not in ('PROGRAM','LEASEAPP')) AND
301 (p_event = G_K_ACTIVATE)) THEN
302 x_return_status := OKL_API.G_RET_STS_ERROR;
303 Okl_Api.SET_MESSAGE(G_APP_NAME, OKL_CONTRACT_STATUS_PUB.G_NO_ACTV_TMPCONTRACT);
304 RAISE Okl_Api.G_EXCEPTION_ERROR;
305 END IF;
306
307
308 IF (INSTR( l_chr_rec.class, 'LEASE') > 0) THEN
309 -- its a lease
310 get_lease_status(p_chr_id,
311 p_event,
312 l_okl_status,
313 l_okc_status,
314 x_PassStatus,
315 x_FailStatus,
316 x_isAllowed );
317
318 ELSIF (INSTR(l_chr_rec.class, 'LOAN') > 0) THEN
319 get_loan_status( p_chr_id,
320 p_event,
321 l_okl_status,
322 l_okc_status,
323 x_PassStatus,
324 x_FailStatus,
325 x_isAllowed );
326 ELSE
327 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
328 END IF;
329
330 if( NOT (x_isAllowed)) then
331 x_return_status := OKL_API.G_RET_STS_ERROR;
332 End If;
333
334 Exception
335 when OKL_API.G_EXCEPTION_ERROR then
336 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
337 p_api_name => l_api_name,
338 p_pkg_name => g_pkg_name,
339 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_api_type => g_api_type);
343
344 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
345 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
346 p_api_name => l_api_name,
347 p_pkg_name => g_pkg_name,
348 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
349 x_msg_count => x_msg_count,
350 x_msg_data => x_msg_data,
351 p_api_type => g_api_type);
352
353 when OTHERS then
354 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
355 p_api_name => l_api_name,
356 p_pkg_name => g_pkg_name,
357 p_exc_name => 'OTHERS',
358 x_msg_count => x_msg_count,
359 x_msg_data => x_msg_data,
360 p_api_type => g_api_type);
361
362
363 END get_contract_status;
364
365 Procedure update_contract_status(
366 p_api_version IN NUMBER,
367 p_init_msg_list IN VARCHAR2,
368 x_return_status OUT NOCOPY VARCHAR2,
369 x_msg_count OUT NOCOPY NUMBER,
370 x_msg_data OUT NOCOPY VARCHAR2,
371 p_khr_status IN VARCHAR2,
372 p_chr_id IN VARCHAR2) IS
373
374 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTRACT_STATUS';
375 l_api_version CONSTANT NUMBER := 1;
376 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
377
378 p_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
379 p_khrv_rec okl_contract_pub.khrv_rec_type;
380 x_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
381 x_khrv_rec okl_contract_pub.khrv_rec_type;
382
383
384 BEGIN
385
386 x_return_status := OKL_API.START_ACTIVITY(
387 p_api_name => l_api_name,
388 p_pkg_name => g_pkg_name,
389 p_init_msg_list => p_init_msg_list,
390 l_api_version => l_api_version,
391 p_api_version => p_api_version,
392 p_api_type => G_API_TYPE,
393 x_return_status => x_return_status);
394
395 -- check if activity started successfully
396 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
397 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
398 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
399 raise OKL_API.G_EXCEPTION_ERROR;
400 End If;
401
402
403 p_chrv_rec.id := p_chr_id;
404 p_chrv_rec.sts_code := p_khr_status;
405
406 p_khrv_rec.id := p_chr_id;
407
408 /*
409 okl_contract_pub.update_contract_header(
410 p_api_version,
411 p_init_msg_list,
412 x_return_status,
413 x_msg_count,
414 x_msg_data,
415 OKL_API.G_FALSE,
416 p_chrv_rec,
417 p_khrv_rec,
418 x_chrv_rec,
419 x_khrv_rec);
420 */
421 okl_okc_migration_pvt.update_contract_header(
422 p_api_version,
423 p_init_msg_list,
424 x_return_status,
425 x_msg_count,
426 x_msg_data,
427 OKL_API.G_FALSE,
428 p_chrv_rec,
429 x_chrv_rec);
430
431 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
432 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
433 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
434 raise OKL_API.G_EXCEPTION_ERROR;
435 End If;
436
437 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
438
439 Exception
440 when OKL_API.G_EXCEPTION_ERROR then
441 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
442 p_api_name => l_api_name,
443 p_pkg_name => g_pkg_name,
444 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
445 x_msg_count => x_msg_count,
446 x_msg_data => x_msg_data,
447 p_api_type => g_api_type);
448
449 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
450 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
451 p_api_name => l_api_name,
452 p_pkg_name => g_pkg_name,
453 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
454 x_msg_count => x_msg_count,
455 x_msg_data => x_msg_data,
456 p_api_type => g_api_type);
457
458 when OTHERS then
459 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
460 p_api_name => l_api_name,
461 p_pkg_name => g_pkg_name,
462 p_exc_name => 'OTHERS',
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data,
465 p_api_type => g_api_type);
466
467
468 END update_contract_status;
469
470 --start of comments
471 --Description : API to cascade contract header status to contrat lines
472 -- will be called before qa check
473 --end of comments
474
475 Procedure cascade_lease_status_old
476 (p_api_version IN NUMBER,
477 p_init_msg_list IN VARCHAR2,
478 x_return_status OUT NOCOPY VARCHAR2,
479 x_msg_count OUT NOCOPY NUMBER,
480 x_msg_data OUT NOCOPY VARCHAR2,
481 p_chr_id IN NUMBER) IS
482
483 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
484 l_api_name CONSTANT VARCHAR2(30) := 'CASCADE_LEASE_STATUS';
485 l_api_version CONSTANT NUMBER := 1.0;
486
487 --cursor to get contract header status
488 CURSOR khr_sts_csr (p_chr_id IN NUMBER) is
489 SELECT STS_CODE,
490 START_DATE,
491 END_DATE,
492 CURRENCY_CODE,
493 SCS_CODE
494 FROM OKC_K_HEADERS_B
495 WHERE ID = p_chr_id;
496
497 l_khr_sts OKC_K_HEADERS_B.STS_CODE%TYPE;
498 l_khr_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
499 l_khr_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
500 l_khr_currency OKC_K_HEADERS_B.CURRENCY_CODE%Type;
501 l_khr_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE;
502
503 --cursor to get contract line status
504 CURSOR kle_sts_csr (p_chr_id IN NUMBER) is
505 SELECT STS_CODE,
506 ID,
507 START_DATE,
508 END_DATE
509 FROM OKC_K_LINES_B
510 WHERE DNZ_CHR_ID = p_chr_id;
511
512 l_kle_sts OKC_K_LINES_B.STS_CODE%TYPE;
513 l_kle_id OKC_K_LINES_B.ID%TYPE;
514 l_kle_start_date OKC_K_LINES_B.START_DATE%TYPE;
515 l_kle_end_date OKC_K_LINES_B.END_DATE%TYPE;
516
517 --cusrsor to get OKC_STATUS check if status not in OKC frozen statuses
518 CURSOR okc_sts_csr (p_okl_sts IN VARCHAR2) IS
519 SELECT ste_code
520 FROM OKC_STATUSES_B
521 WHERE CODE = p_okl_sts
522 AND nvl(START_DATE,sysdate) <= sysdate
523 AND nvl(END_DATE, sysdate+1) > sysdate;
524
525 l_okc_sts OKC_STATUSES_B.STE_CODE%TYPE;
526
527
528 --------------------------------------------------------------------------------
529 --Local Procedure to Copy Khr status to kle
530 --------------------------------------------------------------------------------
531 PROCEDURE cpy_khr_sts_to_kle(p_api_version IN NUMBER,
532 p_init_msg_list IN VARCHAR2,
533 p_kle_id IN NUMBER,
534 p_sts_code IN OKC_STATUSES_B.CODE%TYPE,
535 p_kle_start_date IN DATE,
536 p_khr_start_date IN DATE,
537 p_kle_end_date IN DATE,
538 p_khr_end_date IN DATE,
539 p_currency_code IN OKC_K_LINES_B.CURRENCY_CODE%TYPE,
540 x_return_status OUT NOCOPY VARCHAR2,
541 x_msg_count OUT NOCOPY NUMBER,
542 x_msg_data OUT NOCOPY VARCHAR2) is
543
544 l_start_date OKC_K_LINES_B.START_DATE%TYPE;
545 l_end_date OKC_K_LINES_B.END_DATE%TYPE;
546
547 l_clev_rec okl_okc_migration_pvt.clev_rec_type;
548 x_clev_rec okl_okc_migration_pvt.clev_rec_type;
549
550 Begin
551 If p_kle_start_date is null then
552 l_start_date := p_khr_start_date;
553 ElsIf p_kle_start_date is not null then
554 l_start_date := p_kle_start_date;
555 End If;
556 ---
557 If p_kle_end_date is null then
558 l_end_date := p_khr_end_date;
559 ElsIf p_kle_end_date is not null then
560 l_end_date := p_kle_end_date;
561 End If;
562
563 --call update contract line API
564 l_clev_rec.id := p_kle_id;
565 l_clev_rec.start_date := l_start_date;
566 l_clev_rec.end_date := l_end_date;
567 l_clev_rec.currency_code := p_currency_code;
568 l_clev_rec.sts_code := p_sts_code;
569
570 OKL_OKC_MIGRATION_PVT. update_contract_line(
571 p_api_version => p_api_version,
572 p_init_msg_list => p_init_msg_list,
573 x_return_status => x_return_status,
574 x_msg_count => x_msg_count,
575 x_msg_data => x_msg_data,
576 p_clev_rec => l_clev_rec,
577 x_clev_rec => x_clev_rec);
578
579 Exception
580 When OTHERS Then
581 x_return_status := OKL_API.G_RET_STS_ERROR;
582 End cpy_khr_sts_to_kle;
583 --main
584 Begin
585 --Call OKL_API.START_ACTIVITY
586 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
587 G_PKG_NAME,
588 p_init_msg_list,
589 l_api_version,
590 p_api_version,
591 '_PVT',
592 x_return_status);
593
594 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
595 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
596 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
597 RAISE OKL_API.G_EXCEPTION_ERROR;
598 END IF;
599
600 --fetch contract status
601 OPEN khr_sts_csr (p_chr_id => p_chr_id);
602 FETCH khr_sts_csr into
603 l_khr_sts,
604 l_khr_start_date,
605 l_khr_end_date,
606 l_khr_currency,
607 l_khr_scs_code;
608
609 If khr_sts_csr%NOTFOUND Then
610 --raise appropriate error
611 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
612 p_msg_name => G_MISSING_CONTRACT,
613 p_token1 => G_CONTRACT_ID_TOKEN,
614 p_token1_value => to_char(p_chr_id));
615 RAISE OKL_API.G_EXCEPTION_ERROR;
616 Else
617 If l_khr_scs_code = 'LEASE' Then
618 --fetch line statuses
619 OPEN kle_sts_csr (p_chr_id => p_chr_id);
620 Loop
621 FETCH kle_sts_csr into l_kle_sts, l_kle_id, l_kle_start_date, l_kle_end_date ;
622 Exit When kle_sts_csr%NOTFOUND;
623 IF l_kle_sts is not null Then
624 --fetch okc status for line
625 OPEN okc_sts_csr (p_okl_sts => l_kle_sts);
626 FETCH okc_sts_csr into l_okc_sts;
627 If okc_sts_csr%NOTFOUND Then
628 --raise appropriate error !!??
629 -- means invalid sts_code on line let header status get copied for now??
630 -- copy header status on to lines
631
632 cpy_khr_sts_to_kle(p_api_version => p_api_version,
633 p_init_msg_list => p_init_msg_list,
634 p_kle_id => l_kle_id,
635 p_sts_code => l_khr_sts,
636 p_kle_start_date => l_kle_start_date,
637 p_khr_start_date => l_khr_start_date,
638 p_kle_end_date => l_kle_end_date,
639 p_khr_end_date => l_khr_end_date,
640 p_currency_code => l_khr_currency,
641 x_return_status => x_return_status,
642 x_msg_count => x_msg_count,
643 x_msg_data => x_msg_data);
644
645 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
646 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
647 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
648 RAISE OKL_API.G_EXCEPTION_ERROR;
649 END IF;
650
651 Else
652 --Bug 2522268
653 --If l_okc_sts not in ('HOLD','EXPIRED','TERMINATED','CANCELED') Then
654 If l_okc_sts not in ('HOLD','EXPIRED','TERMINATED','CANCELLED') Then
655 --copy header status on to lines
656 cpy_khr_sts_to_kle(p_api_version => p_api_version,
657 p_init_msg_list => p_init_msg_list,
658 p_kle_id => l_kle_id,
659 p_sts_code => l_khr_sts,
660 p_kle_start_date => l_kle_start_date,
661 p_khr_start_date => l_khr_start_date,
662 p_kle_end_date => l_kle_end_date,
663 p_khr_end_date => l_khr_end_date,
664 p_currency_code => l_khr_currency,
665 x_return_status => x_return_status,
666 x_msg_count => x_msg_count,
667 x_msg_data => x_msg_data);
668
669 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
670 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
671 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
672 RAISE OKL_API.G_EXCEPTION_ERROR;
673 END IF;
674 -- Bug# 2522268
675 --Elsif l_okc_sts in ('HOLD','EXPIRED','TERMINATED','CANCELED') Then
676 Elsif l_okc_sts in ('HOLD','EXPIRED','TERMINATED','CANCELLED') Then
677 --do not copy heaer status on to lines
678 Null;
679 End If;
680 End If;
681 Close okc_sts_csr;
682 Elsif l_kle_sts is null Then
683 --copy header status on to lines
684 cpy_khr_sts_to_kle(p_api_version => p_api_version,
685 p_init_msg_list => p_init_msg_list,
686 p_kle_id => l_kle_id,
687 p_sts_code => l_khr_sts,
688 p_kle_start_date => l_kle_start_date,
689 p_khr_start_date => l_khr_start_date,
690 p_kle_end_date => l_kle_end_date,
691 p_khr_end_date => l_khr_end_date,
692 p_currency_code => l_khr_currency,
693 x_return_status => x_return_status,
694 x_msg_count => x_msg_count,
695 x_msg_data => x_msg_data);
696
697 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
698 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
699 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
700 RAISE OKL_API.G_EXCEPTION_ERROR;
701 END IF;
702 End If; --if kle_sts is not null
703 End Loop; --okc_kle_csr
704 CLOSE kle_sts_csr;
705 End If;---scs_code = 'LEASE'
706 End If; --if khr_sts_csr found
707 CLOSE khr_sts_csr;
708 --Call End Activity
709 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
710 x_msg_data => x_msg_data);
711 EXCEPTION
712 when OKL_API.G_EXCEPTION_ERROR then
713 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
714 p_api_name => l_api_name,
715 p_pkg_name => g_pkg_name,
716 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
717 x_msg_count => x_msg_count,
718 x_msg_data => x_msg_data,
719 p_api_type => g_api_type);
720
721 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
722 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
723 p_api_name => l_api_name,
724 p_pkg_name => g_pkg_name,
725 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
726 x_msg_count => x_msg_count,
727 x_msg_data => x_msg_data,
728 p_api_type => g_api_type);
729
730 when OTHERS then
731 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
732 p_api_name => l_api_name,
733 p_pkg_name => g_pkg_name,
734 p_exc_name => 'OTHERS',
735 x_msg_count => x_msg_count,
736 x_msg_data => x_msg_data,
737 p_api_type => g_api_type);
738
739 End cascade_lease_status_old;
740
741 Procedure cascade_contract_status(
742 p_api_version IN NUMBER,
743 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2,
747 p_clev_id_tbl IN clev_tbl_id_type,
748 p_clev_sts_tbl IN clev_tbl_sts_type ,
749 p_clev_start_date_tbl IN clev_tbl_start_date_type,
750 p_clev_end_date_tbl IN clev_tbl_end_date_type,
751 p_clev_curr_code_tbl IN clev_tbl_currency_code_type )
752 IS
753 l_api_name CONSTANT VARCHAR2(30) := 'CASCADE_CONTRACT_STATUS';
754 l_api_version CONSTANT NUMBER := 1;
755 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
756 l_application_id fnd_application_vl.application_id%type;
757 l_last_update_date okc_k_lines_b.last_update_date%type;
758 l_last_updated_by okc_k_lines_b.last_updated_by%type;
759 l_last_update_login okc_k_lines_b.last_update_login%type;
760 begin
761 null;
762
763 x_return_status := OKL_API.START_ACTIVITY(
764 p_api_name => l_api_name,
765 p_pkg_name => g_pkg_name,
766 p_init_msg_list => p_init_msg_list,
767 l_api_version => l_api_version,
768 p_api_version => p_api_version,
769 p_api_type => G_API_TYPE,
770 x_return_status => x_return_status);
771
772 -- check if activity started successfully
773 If (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
774 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
775 ElSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
776 raise OKL_API.G_EXCEPTION_ERROR;
777 End If;
778
779 select application_id into l_application_id
780 from fnd_application_vl
781 where application_short_name = 'OKL';
782
783 l_last_update_date := sysdate;
784 l_last_updated_by := fnd_global.user_id;
785 l_last_update_login := fnd_global.login_id;
786
787 forall i in p_clev_id_tbl.first..p_clev_id_tbl.last
788 update okc_k_lines_b
789 set sts_code = p_clev_sts_tbl(i),
790 start_date = p_clev_start_date_tbl(i),
791 end_date = p_clev_end_date_tbl(i),
792 currency_code = p_clev_curr_code_tbl(i),
793 program_application_id = l_application_id,
794 program_update_date = sysdate,
795 last_update_date = l_last_update_date,
796 last_updated_by = l_last_updated_by,
797 last_update_login = l_last_update_login
798 where ID = p_clev_id_tbl(i);
799
800 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count, x_msg_data => x_msg_data);
801
802 EXCEPTION
803 when OTHERS then
804 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
805 p_api_name => l_api_name,
806 p_pkg_name => g_pkg_name,
807 p_exc_name => 'OTHERS',
808 x_msg_count => x_msg_count,
809 x_msg_data => x_msg_data,
810 p_api_type => g_api_type);
811
812
813 end;
814
815
816 Procedure cascade_lease_status
817 (p_api_version IN NUMBER,
818 p_init_msg_list IN VARCHAR2,
819 x_return_status OUT NOCOPY VARCHAR2,
820 x_msg_count OUT NOCOPY NUMBER,
821 x_msg_data OUT NOCOPY VARCHAR2,
822 p_chr_id IN NUMBER) IS
823
824 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
825 l_api_name CONSTANT VARCHAR2(30) := 'CASCADE_LEASE_STATUS';
826 l_api_version CONSTANT NUMBER := 1.0;
827
828 --cursor to get contract header status
829 CURSOR khr_sts_csr (p_chr_id IN NUMBER) is
830 SELECT STS_CODE,
831 START_DATE,
832 END_DATE,
833 CURRENCY_CODE,
834 SCS_CODE
835 FROM OKC_K_HEADERS_B
836 WHERE ID = p_chr_id;
837
838 l_khr_sts OKC_K_HEADERS_B.STS_CODE%TYPE;
839 l_khr_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
840 l_khr_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
841 l_khr_currency OKC_K_HEADERS_B.CURRENCY_CODE%Type;
842 l_khr_scs_code OKC_K_HEADERS_B.SCS_CODE%TYPE;
843
844 --cursor to get contract line status
845 CURSOR kle_sts_csr (p_chr_id IN NUMBER) is
846 SELECT A.STS_CODE,
847 A.ID,
848 A.START_DATE,
849 A.END_DATE
850 FROM OKC_K_LINES_B A,
851 OKC_STATUSES_B B
852 WHERE A.DNZ_CHR_ID = p_chr_id
853 AND A.STS_CODE = B.CODE
854 AND B.STE_CODE not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
855 /* -- Bug 5046462: Performance fix by removing UNION part
856 UNION
857 SELECT A.STS_CODE,
858 A.ID,
859 A.START_DATE,
860 A.END_DATE
861 FROM OKC_K_LINES_B A
862 WHERE A.DNZ_CHR_ID = p_chr_id
863 AND NOT EXISTS (select 'x' from OKC_STATUSES_B B where B.CODE = A.STS_CODE);
864 */
865
866 l_kle_sts clev_tbl_sts_type;
867 l_kle_id clev_tbl_id_type;
868 l_kle_start_date clev_tbl_start_date_type;
869 l_kle_end_date clev_tbl_end_date_type;
870
871 l_clev_id_tbl clev_tbl_id_type;
872 l_clev_sts_tbl clev_tbl_sts_type;
873 l_clev_start_date_tbl clev_tbl_start_date_type;
874 l_clev_end_date_tbl clev_tbl_end_date_type;
875 l_clev_currency_code_tbl clev_tbl_currency_code_type;
876
877 l_counter PLS_INTEGER;
878
879 --cusrsor to get OKC_STATUS check if status not in OKC frozen statuses
880 CURSOR okc_sts_csr (p_okl_sts IN VARCHAR2) IS
881 SELECT ste_code
882 FROM OKC_STATUSES_B
883 WHERE CODE = p_okl_sts
884 AND nvl(START_DATE,sysdate) <= sysdate
885 AND nvl(END_DATE, sysdate+1) > sysdate;
886
887 l_okc_sts OKC_STATUSES_B.STE_CODE%TYPE;
888
889
890 Begin
891 --Call OKL_API.START_ACTIVITY
892 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
893 G_PKG_NAME,
894 p_init_msg_list,
895 l_api_version,
896 p_api_version,
897 '_PVT',
898 x_return_status);
899
900 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
901 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
902 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
903 RAISE OKL_API.G_EXCEPTION_ERROR;
904 END IF;
905
906 --dbms_output.put_line('In cascade_lease_status: Before open khr_sts_csr');
907 --fetch contract status
908 OPEN khr_sts_csr (p_chr_id => p_chr_id);
909 FETCH khr_sts_csr into
910 l_khr_sts,
911 l_khr_start_date,
912 l_khr_end_date,
913 l_khr_currency,
914 l_khr_scs_code;
915 --dbms_output.put_line('In cascade_lease_status: After fetch khr_sts_csr');
916
917 If khr_sts_csr%NOTFOUND Then
918 --raise appropriate error
919 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
920 p_msg_name => G_MISSING_CONTRACT,
921 p_token1 => G_CONTRACT_ID_TOKEN,
922 p_token1_value => to_char(p_chr_id));
923 RAISE OKL_API.G_EXCEPTION_ERROR;
924 Else
925 If l_khr_scs_code = 'LEASE' Then
926 --fetch line statuses
927 OPEN kle_sts_csr (p_chr_id => p_chr_id);
928 --dbms_output.put_line('In cascade_lease_status: After open kle_sts_csr');
929 --dbms_output.put_line('In cascade_lease_status: p_chr_id= ' || p_chr_id);
930 l_counter := 1;
931 Loop
932
933 l_kle_sts.DELETE;
934 l_kle_id.DELETE;
935 l_kle_start_date.DELETE;
936 l_kle_end_date.DELETE;
937
938 FETCH kle_sts_csr BULK COLLECT INTO l_kle_sts, l_kle_id, l_kle_start_date, l_kle_end_date LIMIT G_BULK_BATCH_SIZE;
939 --dbms_output.put_line('In cascade_lease_status: After fetch kle_sts_csr');
940 --dbms_output.put_line('In cascade_lease_status: G_BULK_BATCH_SIZE='||G_BULK_BATCH_SIZE);
941 --dbms_output.put_line('In cascade_lease_status: l_kle_sts.COUNT='||l_kle_sts.count);
942 --dbms_output.put_line('In cascade_lease_status: l_kle_sts.first='||l_kle_sts.first);
943 --dbms_output.put_line('In cascade_lease_status: l_kle_sts.last='||l_kle_sts.last);
944
945 if (l_kle_sts.COUNT > 0) then
946 for i in l_kle_sts.first .. l_kle_sts.last LOOP
947
948 --dbms_output.put_line('Inside LOOP' );
949 --dbms_output.put_line('l_khr_sts=' || l_khr_sts);
950 l_clev_sts_tbl(l_counter) := l_khr_sts;
951 --dbms_output.put_line('In cascade_lease_status: After assignment l_khr_sts');
952 l_clev_id_tbl(l_counter) := l_kle_id(i);
953 --dbms_output.put_line('In cascade_lease_status: After assignment l_kle_id');
954
955 If l_kle_start_date(i) is null then
956 l_clev_start_date_tbl(l_counter) := l_khr_start_date;
957 Else
958 l_clev_start_date_tbl(l_counter) := l_kle_start_date(i);
959 End If;
960 --dbms_output.put_line('In cascade_lease_status: After assignment start_date');
961 If l_kle_end_date(i) is null then
962 l_clev_end_date_tbl(l_counter) := l_khr_end_date;
963 Else
964 l_clev_end_date_tbl(l_counter) := l_kle_end_date(i);
965 End If;
966 --dbms_output.put_line('In cascade_lease_status: After assignment end_date');
967 l_clev_currency_code_tbl(l_counter) := l_khr_currency;
968 --dbms_output.put_line('In cascade_lease_status: After assignment currency');
969
970 l_counter := l_counter + 1;
971 --dbms_output.put_line('In cascade_lease_status: After assignment');
972 end LOOP;
973 end if;
974 exit when kle_sts_csr%NOTFOUND;
975 end loop;
976
977 CLOSE kle_sts_csr;
978 --dbms_output.put_line('l_counter=' || l_counter || ' @ ' || to_char(sysdate,'HH24:MI:SS'));
979 If (l_clev_id_tbl.COUNT > 0) then
980 cascade_contract_status(
981 p_api_version => p_api_version,
982 p_init_msg_list => p_init_msg_list,
983 x_return_status => x_return_status,
984 x_msg_count => x_msg_count,
985 x_msg_data => x_msg_data,
986 p_clev_id_tbl => l_clev_id_tbl,
987 p_clev_sts_tbl => l_clev_sts_tbl,
988 p_clev_start_date_tbl => l_clev_start_date_tbl,
989 p_clev_end_date_tbl => l_clev_end_date_tbl,
990 p_clev_curr_code_tbl => l_clev_currency_code_tbl );
991 End If;
992
993 End If;---scs_code = 'LEASE'
994 End If; --if khr_sts_csr found
995 CLOSE khr_sts_csr;
996 --Call End Activity
997 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
998 x_msg_data => x_msg_data);
999 EXCEPTION
1000 when OKL_API.G_EXCEPTION_ERROR then
1001 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1002 p_api_name => l_api_name,
1003 p_pkg_name => g_pkg_name,
1004 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1005 x_msg_count => x_msg_count,
1006 x_msg_data => x_msg_data,
1007 p_api_type => g_api_type);
1008
1009 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1010 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1011 p_api_name => l_api_name,
1012 p_pkg_name => g_pkg_name,
1013 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1014 x_msg_count => x_msg_count,
1015 x_msg_data => x_msg_data,
1016 p_api_type => g_api_type);
1017
1018 when OTHERS then
1019 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1020 p_api_name => l_api_name,
1021 p_pkg_name => g_pkg_name,
1022 p_exc_name => 'OTHERS',
1023 x_msg_count => x_msg_count,
1024 x_msg_data => x_msg_data,
1025 p_api_type => g_api_type);
1026
1027 End cascade_lease_status;
1028
1029 --start of comments
1030 --Description : API to cascade contract status to contract lines after the
1031 -- user edits a the contract
1032 --end of comments
1033 Procedure cascade_lease_status_edit
1034 (p_api_version IN NUMBER,
1035 p_init_msg_list IN VARCHAR2,
1036 x_return_status OUT NOCOPY VARCHAR2,
1037 x_msg_count OUT NOCOPY NUMBER,
1038 x_msg_data OUT NOCOPY VARCHAR2,
1039 p_chr_id IN NUMBER) IS
1040
1041 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1042 l_api_name CONSTANT VARCHAR2(30) := 'CASCADE_LEASE_STATUS_EDIT';
1043 l_api_version CONSTANT NUMBER := 1.0;
1044
1045 --cursor to get contract header status
1046 CURSOR khr_sts_csr (p_chr_id IN NUMBER) is
1047 SELECT STS_CODE,
1048 START_DATE,
1049 END_DATE,
1050 CURRENCY_CODE,
1051 SCS_CODE,
1052 CONTRACT_NUMBER,
1053 TEMPLATE_YN --Bug#4728470
1054 FROM OKC_K_HEADERS_B
1055 WHERE ID = p_chr_id;
1056
1057 l_khr_sts OKC_K_HEADERS_B.STS_CODE%TYPE;
1058 l_khr_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
1059 l_khr_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
1060 l_khr_currency OKC_K_HEADERS_B.CURRENCY_CODE%Type;
1061 l_khr_scs_code OKC_K_HEADERS_B.SCS_CODE%Type;
1062 l_khr_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
1063
1064 --Bug#4728470
1065 l_khr_template_yn OKC_K_HEADERS_B.TEMPLATE_YN%TYPE;
1066
1067 --cusrsor to get OKC_STATUS check if status not in OKC frozen statuses
1068 CURSOR okc_sts_csr (p_okl_sts IN VARCHAR2) IS
1069 SELECT ste_code
1070 FROM OKC_STATUSES_B
1071 WHERE CODE = p_okl_sts
1072 AND nvl(START_DATE,sysdate) <= sysdate
1073 AND nvl(END_DATE, sysdate+1) > sysdate;
1074
1075 l_okc_sts OKC_STATUSES_B.STE_CODE%TYPE;
1076
1077 l_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
1078 x_chrv_rec okl_okc_migration_pvt.chrv_rec_type;
1079
1080 Begin
1081 --Call OKL_API.START_ACTIVITY
1082 l_return_status := OKL_API.START_ACTIVITY( substr(l_api_name,1,26),
1083 G_PKG_NAME,
1084 p_init_msg_list,
1085 l_api_version,
1086 p_api_version,
1087 '_PVT',
1088 x_return_status);
1089
1090 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1091 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1092 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1093 RAISE OKL_API.G_EXCEPTION_ERROR;
1094 END IF;
1095
1096 OPEN khr_sts_csr (p_chr_id => p_chr_id);
1097 FETCH khr_sts_csr into
1098 l_khr_sts,
1099 l_khr_start_date,
1100 l_khr_end_date,
1101 l_khr_currency,
1102 l_khr_scs_code,
1103 l_khr_contract_number,
1104 l_khr_template_yn;
1105 If khr_sts_csr%NOTFOUND Then
1106 --raise appropriate error
1107 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1108 p_msg_name => G_MISSING_CONTRACT,
1109 p_token1 => G_CONTRACT_ID_TOKEN,
1110 p_token1_value => to_char(p_chr_id));
1111 RAISE OKL_API.G_EXCEPTION_ERROR;
1112 Else
1113 If l_khr_scs_code = 'LEASE' Then
1114
1115 --Bug#4728470
1116 If (NVL(l_khr_template_yn,'N') = 'Y' AND FND_PROFILE.VALUE('OKL_ALLOW_K_TEMPLATE_CREATE') = 'N') THEN
1117 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1118 p_msg_name => 'OKL_LLA_TEMPLATE_CREATE');
1119 RAISE OKL_API.G_EXCEPTION_ERROR;
1120 End If;
1121
1122 If l_khr_sts in ('NEW','INCOMPLETE') Then
1123 --do not update statuses
1124 Null;
1125
1126 -- Bug# 3477560 - Do not allow modification when contract status is
1127 -- PENDING_APPROVAL
1128 Elsif l_khr_sts = 'PENDING_APPROVAL' then
1129 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
1130 p_msg_name => 'OKL_LLA_PENDING_APPROVAL',
1131 p_token1 => 'CONTRACT_NUMBER',
1132 p_token1_value => l_khr_contract_number);
1133 RAISE OKL_API.G_EXCEPTION_ERROR;
1134 Else
1135 --get okc statuses
1136 OPEN okc_sts_csr (p_okl_sts => l_khr_sts);
1137 FETCH okc_sts_csr into l_okc_sts;
1138 If okc_sts_csr%NOTFOUND Then
1139 --raise appropriate error !!??
1140 -- means invalid sts_code on line let header status get copied for now??
1141 --update the hdr status to 'Incomplete'
1142
1143 l_chrv_rec.id := p_chr_id;
1144 l_chrv_rec.sts_code := 'INCOMPLETE';
1145
1146 OKL_OKC_MIGRATION_PVT.update_contract_header(
1147 p_api_version => p_api_version,
1148 p_init_msg_list => p_init_msg_list,
1149 x_return_status => x_return_status,
1150 x_msg_count => x_msg_count,
1151 x_msg_data => x_msg_data,
1152 p_chrv_rec => l_chrv_rec,
1153 x_chrv_rec => x_chrv_rec);
1154
1155 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1156 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1157 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1158 RAISE OKL_API.G_EXCEPTION_ERROR;
1159 END IF;
1160
1161 --cascade the status on to lines
1162
1163 cascade_lease_status
1164 (p_api_version => p_api_version,
1165 p_init_msg_list => p_init_msg_list,
1166 x_return_status => x_return_status,
1167 x_msg_count => x_msg_count,
1168 x_msg_data => x_msg_data,
1169 p_chr_id => p_chr_id);
1170
1171 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1172 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1173 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1174 RAISE OKL_API.G_EXCEPTION_ERROR;
1175 END IF;
1176
1177 -- Update the records in the OKL_BOOK_CONTROLLER_TRX table
1178 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
1179 p_api_version => p_api_version,
1180 p_init_msg_list => p_init_msg_list,
1181 x_return_status => x_return_status,
1182 x_msg_count => x_msg_count,
1183 x_msg_data => x_msg_data,
1184 p_khr_id => p_chr_id,
1185 p_prog_short_name => NULL,
1186 p_conc_req_id => NULL,
1187 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
1188
1189 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1190 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1191 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1192 raise OKL_API.G_EXCEPTION_ERROR;
1193 END IF;
1194
1195 Else
1196 --Bug# 2522268
1197 --If l_okc_sts not in ('HOLD','EXPIRED','TERMINATED','CANCELED','ACTIVE') then
1198 If l_okc_sts not in ('HOLD','EXPIRED','TERMINATED','CANCELLED','ACTIVE') then
1199
1200 --update the hdr status to 'Incomplete'
1201 l_chrv_rec.id := p_chr_id;
1202 l_chrv_rec.sts_code := 'INCOMPLETE';
1203
1204 OKL_OKC_MIGRATION_PVT.update_contract_header(
1205 p_api_version => p_api_version,
1206 p_init_msg_list => p_init_msg_list,
1207 x_return_status => x_return_status,
1208 x_msg_count => x_msg_count,
1209 x_msg_data => x_msg_data,
1210 p_chrv_rec => l_chrv_rec,
1211 x_chrv_rec => x_chrv_rec);
1212
1213 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1214 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1215 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1216 RAISE OKL_API.G_EXCEPTION_ERROR;
1217 END IF;
1218
1219 --cascade the status on to lines
1220
1221 cascade_lease_status
1222 (p_api_version => p_api_version,
1223 p_init_msg_list => p_init_msg_list,
1224 x_return_status => x_return_status,
1225 x_msg_count => x_msg_count,
1226 x_msg_data => x_msg_data,
1227 p_chr_id => p_chr_id);
1228
1229 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1230 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1231 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1232 RAISE OKL_API.G_EXCEPTION_ERROR;
1233 END IF;
1234 -- Update the records in the OKL_BOOK_CONTROLLER_TRX table
1235 OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
1236 p_api_version => p_api_version,
1237 p_init_msg_list => p_init_msg_list,
1238 x_return_status => x_return_status,
1239 x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data,
1241 p_khr_id => p_chr_id,
1242 p_prog_short_name => NULL,
1243 p_conc_req_id => NULL,
1244 p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
1245
1246 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1247 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1248 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1249 raise OKL_API.G_EXCEPTION_ERROR;
1250 END IF;
1251
1252 --Bug# 2522268
1253 --Elsif l_okc_sts in ('HOLD','EXPIRED','TERMINATED','CANCELED','ACTIVE') Then
1254 Elsif l_okc_sts in ('HOLD','EXPIRED','TERMINATED','CANCELLED','ACTIVE') Then
1255 --do not change hdr sts and do notcopy heaer status on to lines
1256 Null;
1257 End If;
1258 End If;
1259 Close okc_sts_csr;
1260 End If;
1261 End If;
1262 End If;
1263 CLOSE khr_sts_csr;
1264 --Call End Activity
1265 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1266 x_msg_data => x_msg_data);
1267 EXCEPTION
1268 when OKL_API.G_EXCEPTION_ERROR then
1269 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1270 p_api_name => l_api_name,
1271 p_pkg_name => g_pkg_name,
1272 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1273 x_msg_count => x_msg_count,
1274 x_msg_data => x_msg_data,
1275 p_api_type => g_api_type);
1276
1277 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1278 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1279 p_api_name => l_api_name,
1280 p_pkg_name => g_pkg_name,
1281 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1282 x_msg_count => x_msg_count,
1283 x_msg_data => x_msg_data,
1284 p_api_type => g_api_type);
1285
1286 when OTHERS then
1287 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1288 p_api_name => l_api_name,
1289 p_pkg_name => g_pkg_name,
1290 p_exc_name => 'OTHERS',
1291 x_msg_count => x_msg_count,
1292 x_msg_data => x_msg_data,
1293 p_api_type => g_api_type);
1294
1295 End cascade_lease_status_edit;
1296
1297 End OKL_CONTRACT_STATUS_PVT;