[Home] [Help]
PACKAGE BODY: APPS.OKL_LLA_UTIL_PVT
Source
1 PACKAGE BODY Okl_Lla_Util_Pvt AS
2 /* $Header: OKLRLAUB.pls 120.18.12010000.8 2009/09/29 17:22:09 racheruv ship $ */
3 /* *********************************************** */
4 --G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
5 --G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
6 G_EXCEPTION_HALT_PROCESSING EXCEPTION;
7 G_EXCEPTION_STOP_VALIDATION EXCEPTION;
8
9
10 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_LLA_UTIL_PVT';
11 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
12 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
13 l_api_name VARCHAR2(35) := 'LLA_UTIL';
14
15 FUNCTION get_number
16 (p_amount_in IN VARCHAR2)
17 RETURN VARCHAR2
18 AS
19 l_amount_out VARCHAR2(150) := p_amount_in;
20 BEGIN
21 SELECT REPLACE(REPLACE(p_amount_in,SUBSTR(value,2,1)),SUBSTR(value,1,1),'.')
22 -- select replace(replace(p_amount_in,substr(',.',2,1)),substr(',.',1,1),'.')
23 INTO l_amount_out
24 FROM v$nls_parameters
25 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';
26 RETURN(l_amount_out);
27 EXCEPTION
28 WHEN OTHERS THEN
29 RETURN(p_amount_in);
30 END;
31
32 -------------------------------------------------------------------------------
33 -- FUNCTION get_lookup_meaning
34 -------------------------------------------------------------------------------
35 -- Start of comments
36 --
37 -- Function Name : get_lookup_meaning
38 -- Description : This function returns the lookup meaning for specified
39 -- : lookup_code and lookup_type
40 --
41 -- Business Rules :
42 --
43 -- Parameters :
44 -- Version : 1.0
45 -- History : 21-FEB-2007 asahoo created
46 -- End of comments
47 FUNCTION get_lookup_meaning(p_lookup_type FND_LOOKUPS.LOOKUP_TYPE%TYPE
48 ,p_lookup_code FND_LOOKUPS.LOOKUP_CODE%TYPE)
49 RETURN VARCHAR2 IS
50 CURSOR fnd_lookup_csr(p_lookup_type fnd_lookups.lookup_type%TYPE
51 ,p_lookup_code fnd_lookups.lookup_code%TYPE) IS
52 SELECT MEANING
53 FROM FND_LOOKUPS FND
54 WHERE FND.LOOKUP_TYPE = p_lookup_type
55 AND FND.LOOKUP_CODE = p_lookup_code;
56 l_return_value FND_LOOKUPS.MEANING%TYPE := NULL;
57 BEGIN
58 IF (p_lookup_type IS NOT NULL AND p_lookup_code IS NOT NULL) THEN
59 OPEN fnd_lookup_csr(p_lookup_type, p_lookup_code);
60 FETCH fnd_lookup_csr INTO l_return_value;
61 CLOSE fnd_lookup_csr;
62 END IF;
63 RETURN l_return_value;
64 END get_lookup_meaning;
65
66
67 PROCEDURE format_round_amount(
68 p_api_version IN NUMBER,
69 p_init_msg_list IN VARCHAR2,
70 x_return_status OUT NOCOPY VARCHAR2,
71 x_msg_count OUT NOCOPY NUMBER,
72 x_msg_data OUT NOCOPY VARCHAR2,
73 p_amount IN VARCHAR2,
74 p_currency_code IN VARCHAR2,
75 x_amount OUT NOCOPY VARCHAR2) IS
76
77
78 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
79 l_proc_name VARCHAR2(35) := 'FORMAT_ROUND_AMOUNT';
80 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
81
82 BEGIN
83
84 x_return_status := OKC_API.G_RET_STS_SUCCESS;
85 -- call START_ACTIVITY to create savepoint, check compatibility
86 -- and initialize message list
87 x_return_status := OKC_API.START_ACTIVITY(
88 p_api_name => l_api_name,
89 p_pkg_name => G_PKG_NAME,
90 p_init_msg_list => p_init_msg_list,
91 l_api_version => l_api_version,
92 p_api_version => p_api_version,
93 p_api_type => G_API_TYPE,
94 x_return_status => x_return_status);
95
96 -- check if activity started successfully
97 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
98 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
99 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
100 RAISE OKC_API.G_EXCEPTION_ERROR;
101 END IF;
102
103
104 x_amount := OKL_ACCOUNTING_UTIL.cc_round_format_amount(p_amount => TO_NUMBER(get_number(p_amount)),
105 p_currency_code => p_currency_code);
106
107
108 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
109 x_msg_data => x_msg_data);
110
111 EXCEPTION
112 WHEN OKC_API.G_EXCEPTION_ERROR THEN
113 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
114 p_api_name => l_api_name,
115 p_pkg_name => G_PKG_NAME,
116 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
117 x_msg_count => x_msg_count,
118 x_msg_data => x_msg_data,
119 p_api_type => G_API_TYPE);
120
121 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
122 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
123 p_api_name => l_api_name,
124 p_pkg_name => G_PKG_NAME,
125 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
126 x_msg_count => x_msg_count,
127 x_msg_data => x_msg_data,
128 p_api_type => G_API_TYPE);
129
130 WHEN OTHERS THEN
131 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
132 p_api_name => l_api_name,
133 p_pkg_name => G_PKG_NAME,
134 p_exc_name => 'OTHERS',
135 x_msg_count => x_msg_count,
136 x_msg_data => x_msg_data,
137 p_api_type => G_API_TYPE);
138
139 END format_round_amount;
140
141
142 PROCEDURE format_round_amount(
143 p_api_version IN NUMBER,
144 p_init_msg_list IN VARCHAR2,
145 x_return_status OUT NOCOPY VARCHAR2,
146 x_msg_count OUT NOCOPY NUMBER,
147 x_msg_data OUT NOCOPY VARCHAR2,
148 p_amount IN VARCHAR2,
149 p_currency_code IN VARCHAR2,
150 p_org_id IN VARCHAR2,
151 x_amount OUT NOCOPY VARCHAR2) IS
152
153
154 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
155 l_proc_name VARCHAR2(35) := 'FORMAT_ROUND_AMOUNT';
156 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
157
158 BEGIN
159
160 x_return_status := OKC_API.G_RET_STS_SUCCESS;
161 -- call START_ACTIVITY to create savepoint, check compatibility
162 -- and initialize message list
163 x_return_status := OKC_API.START_ACTIVITY(
164 p_api_name => l_api_name,
165 p_pkg_name => G_PKG_NAME,
166 p_init_msg_list => p_init_msg_list,
167 l_api_version => l_api_version,
168 p_api_version => p_api_version,
169 p_api_type => G_API_TYPE,
170 x_return_status => x_return_status);
171
172 -- check if activity started successfully
173 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
174 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
175 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
176 RAISE OKC_API.G_EXCEPTION_ERROR;
177 END IF;
178
179
180 x_amount := OKL_ACCOUNTING_UTIL.cc_round_format_amount(p_amount => TO_NUMBER(get_number(p_amount)),
181 p_currency_code => p_currency_code);
182
183
184 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
185 x_msg_data => x_msg_data);
186
187 EXCEPTION
188 WHEN OKC_API.G_EXCEPTION_ERROR THEN
189 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
190 p_api_name => l_api_name,
191 p_pkg_name => G_PKG_NAME,
192 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
193 x_msg_count => x_msg_count,
194 x_msg_data => x_msg_data,
195 p_api_type => G_API_TYPE);
196
197 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
198 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
199 p_api_name => l_api_name,
200 p_pkg_name => G_PKG_NAME,
201 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
202 x_msg_count => x_msg_count,
203 x_msg_data => x_msg_data,
204 p_api_type => G_API_TYPE);
205
206 WHEN OTHERS THEN
207 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
208 p_api_name => l_api_name,
209 p_pkg_name => G_PKG_NAME,
210 p_exc_name => 'OTHERS',
211 x_msg_count => x_msg_count,
212 x_msg_data => x_msg_data,
213 p_api_type => G_API_TYPE);
214
215 END format_round_amount;
216
217
218
219 PROCEDURE round_amount(
220 p_api_version IN NUMBER,
221 p_init_msg_list IN VARCHAR2,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2,
225 p_amount IN VARCHAR2,
226 p_currency_code IN VARCHAR2,
227 x_amount OUT NOCOPY VARCHAR2) IS
228
229
230 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
231 l_proc_name VARCHAR2(35) := 'ROUND_AMOUNT';
232 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
233
234 BEGIN
235
236 x_return_status := OKC_API.G_RET_STS_SUCCESS;
237 -- call START_ACTIVITY to create savepoint, check compatibility
238 -- and initialize message list
239 x_return_status := OKC_API.START_ACTIVITY(
240 p_api_name => l_api_name,
241 p_pkg_name => G_PKG_NAME,
242 p_init_msg_list => p_init_msg_list,
243 l_api_version => l_api_version,
244 p_api_version => p_api_version,
245 p_api_type => G_API_TYPE,
246 x_return_status => x_return_status);
247
248 -- check if activity started successfully
249 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
250 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
251 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
252 RAISE OKC_API.G_EXCEPTION_ERROR;
253 END IF;
254
255 x_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
256 p_amount => TO_NUMBER(get_number(p_amount)),
257 p_currency_code => p_currency_code);
258
259
260 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
261 x_msg_data => x_msg_data);
262
263 EXCEPTION
264 WHEN OKC_API.G_EXCEPTION_ERROR THEN
265 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
266 p_api_name => l_api_name,
267 p_pkg_name => G_PKG_NAME,
268 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
269 x_msg_count => x_msg_count,
270 x_msg_data => x_msg_data,
271 p_api_type => G_API_TYPE);
272
273 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
274 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
275 p_api_name => l_api_name,
276 p_pkg_name => G_PKG_NAME,
277 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data,
280 p_api_type => G_API_TYPE);
281
282 WHEN OTHERS THEN
283 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
284 p_api_name => l_api_name,
285 p_pkg_name => G_PKG_NAME,
286 p_exc_name => 'OTHERS',
287 x_msg_count => x_msg_count,
288 x_msg_data => x_msg_data,
289 p_api_type => G_API_TYPE);
290
291 END round_amount;
292
293
294 PROCEDURE round_amount(
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_amount IN VARCHAR2,
301 p_currency_code IN VARCHAR2,
302 p_org_id IN VARCHAR2,
303 x_amount OUT NOCOPY VARCHAR2) IS
304
305
306 --l_api_name VARCHAR2(35) := 'RGRP_RULES_PROCESS';
307 l_proc_name VARCHAR2(35) := 'ROUND_AMOUNT';
308 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
309
310 BEGIN
311
312 x_return_status := OKC_API.G_RET_STS_SUCCESS;
313 -- call START_ACTIVITY to create savepoint, check compatibility
314 -- and initialize message list
315 x_return_status := OKC_API.START_ACTIVITY(
316 p_api_name => l_api_name,
317 p_pkg_name => G_PKG_NAME,
318 p_init_msg_list => p_init_msg_list,
319 l_api_version => l_api_version,
320 p_api_version => p_api_version,
321 p_api_type => G_API_TYPE,
322 x_return_status => x_return_status);
323
324 -- check if activity started successfully
325 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
326 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
327 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
328 RAISE OKC_API.G_EXCEPTION_ERROR;
329 END IF;
330
331
332 x_amount := OKL_ACCOUNTING_UTIL.cross_currency_round_amount(
333 p_amount => TO_NUMBER(get_number(p_amount)),
334 p_currency_code => p_currency_code);
335
336
337 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
338 x_msg_data => x_msg_data);
339
340 EXCEPTION
341 WHEN OKC_API.G_EXCEPTION_ERROR THEN
342 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
343 p_api_name => l_api_name,
344 p_pkg_name => G_PKG_NAME,
345 p_exc_name => 'OKC_API.G_RET_STS_ERROR',
346 x_msg_count => x_msg_count,
347 x_msg_data => x_msg_data,
348 p_api_type => G_API_TYPE);
349
350 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
351 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
352 p_api_name => l_api_name,
353 p_pkg_name => G_PKG_NAME,
354 p_exc_name => 'OKC_API.G_RET_STS_UNEXP_ERROR',
355 x_msg_count => x_msg_count,
356 x_msg_data => x_msg_data,
357 p_api_type => G_API_TYPE);
358
359 WHEN OTHERS THEN
360 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
361 p_api_name => l_api_name,
362 p_pkg_name => G_PKG_NAME,
363 p_exc_name => 'OTHERS',
364 x_msg_count => x_msg_count,
365 x_msg_data => x_msg_data,
366 p_api_type => G_API_TYPE);
367
368 END round_amount;
369
370 FUNCTION get_canonical_date
371 (p_date_char IN VARCHAR2)
372 RETURN VARCHAR2
373 AS
374 -- p_date := p_date_char;
375 -- p_mask := p_date_mask;
376 p_date_out VARCHAR2(15) := p_date_char;
377
378 BEGIN
379 --p_canonical_date := to_char(to_date(p_date_char,G_DISPLAY_MASK), g_canonical_mask);
380 IF(LENGTH(RTRIM(p_date_out)) > 0) THEN
381 p_date_out := get_canonical_date(p_date_out,G_DISPLAY_MASK);
382 END IF;
383 RETURN p_date_out;
384 EXCEPTION
385 WHEN OTHERS THEN
386 RETURN(p_date_char);
387 END;
388
389
390 FUNCTION get_canonical_date
391 (p_date_char IN VARCHAR2,
392 p_date_mask IN VARCHAR2)
393 RETURN VARCHAR2
394 AS
395 -- p_date := p_date_char;
396 -- p_mask := p_date_mask;
397 p_date_out VARCHAR2(15) := p_date_char;
398
399 BEGIN
400 IF(LENGTH(RTRIM(p_date_out)) > 0) THEN
401 p_date_out := FND_DATE.date_to_canonical(TO_DATE(p_date_out,p_date_mask));
402 END IF;
403 RETURN p_date_out;
404 EXCEPTION
405 WHEN OTHERS THEN
406 RETURN(p_date_char);
407 END;
408
409
410 FUNCTION validate_get_canonical_date
411 (p_date_char IN VARCHAR2)
412 RETURN VARCHAR2
413 AS
414 -- p_date := p_date_char;
415 -- p_mask := p_date_mask;
416 p_date_out VARCHAR2(30) := p_date_char;
417 p_date DATE;
418
419 BEGIN
420 --p_canonical_date := to_char(to_date(p_date_char,G_DISPLAY_MASK), g_canonical_mask);
421 IF(LENGTH(RTRIM(p_date_out)) > 0) THEN
422 p_date := TO_DATE(p_date_out,G_DISPLAY_MASK);
423 -- p_date_out := to_char(p_date,g_canonical_mask);
424 p_date_out := FND_DATE.date_to_canonical(p_date);
425 END IF;
426 RETURN p_date_out;
427 EXCEPTION
428 WHEN OTHERS THEN
429 RETURN(OKL_API.G_FALSE);
430 END;
431
432
433 FUNCTION get_display_date
434 (p_date_char IN VARCHAR2)
435 RETURN VARCHAR2
436 AS
437 -- p_date := p_date_char;
438 -- p_mask := p_date_mask;
439 p_date_out VARCHAR2(30) := p_date_char;
440 l_display_mask VARCHAR2(15) := fnd_profile.value('ICX_DATE_FORMAT_MASK'); -- Added for bug fix 8429670
441 BEGIN
442 IF(LENGTH(RTRIM(p_date_out)) > 0) THEN
443 -- p_date_out := get_display_date(p_date_out,G_DISPLAY_MASK); --8429670
444 p_date_out := get_display_date(p_date_out,l_display_mask); --8429670
445 END IF;
446 RETURN p_date_out;
447 EXCEPTION
448 WHEN OTHERS THEN
449 RETURN(p_date_char);
450 END;
451
452 FUNCTION get_display_date
453 (p_date_char IN VARCHAR2,
454 p_date_mask IN VARCHAR2)
455 RETURN VARCHAR2
456 AS
457 -- p_date := p_date_char;
458 -- p_mask := p_date_mask;
459 l_date DATE;
460 l_date_out VARCHAR2(30);
461
462 BEGIN
463 IF(LENGTH(RTRIM(p_date_char)) > 0) THEN
464 -- p_date_out := to_char(to_date(p_date_out,g_canonical_mask), p_date_mask);
465 -- p_date_out := to_char(FND_DATE.canonical_to_date(p_date_out), p_date_mask);
466
467 --l_date := FND_DATE.canonical_to_date('2003/02/10');
468 --l_date_out := to_char(l_date,'DD-MON-YYYY');
469 l_date := FND_DATE.canonical_to_date(p_date_char);
470 l_date_out := TO_CHAR(l_date,p_date_mask);
471 END IF;
472 RETURN l_date_out;
473 EXCEPTION
474 WHEN OTHERS THEN
475 RETURN(p_date_char);
476 END;
477
478
479 FUNCTION convert_date
480 (p_date_in_char IN VARCHAR2,
481 p_date_in_mask IN VARCHAR2,
482 p_date_out_mask IN VARCHAR2)
483 RETURN VARCHAR2
484 AS
485 -- p_date := p_date_char;
486 -- p_mask := p_date_mask;
487 p_date_out VARCHAR2(15);
488
489 BEGIN
490 p_date_out := TO_CHAR(TO_DATE(p_date_in_char,p_date_in_mask), p_date_out_mask);
491 RETURN p_date_out;
492 EXCEPTION
493 WHEN OTHERS THEN
494 RETURN(NULL);
495 END;
496
497 /*
498 -- mvasudev, 08/17/2004
499 Added the following functions for Business Events Enabling
500 */
501 FUNCTION check_mass_rebook_contract(
502 p_chr_id IN NUMBER)
503 RETURN VARCHAR2
504 AS
505 --cursor to check if the contract is selected for Mass Rebook
506 CURSOR l_chk_mass_rbk_csr
507 IS
508 SELECT '1'
509 FROM okc_k_headers_b chrb,
510 okl_trx_contracts ktrx
511 WHERE chrb.ID = p_chr_id
512 AND ktrx.khr_id = chrb.id
513 AND ktrx.tsu_code = 'ENTERED'
514 AND ktrx.rbr_code IS NOT NULL
515 AND ktrx.tcn_type = 'TRBK'
516 --rkuttiya added for 12.1.1 Multi GAAP
517 AND ktrx.representation_type = 'PRIMARY'
518 --
519 AND EXISTS (SELECT '1'
520 FROM okl_rbk_selected_contract rbk_khr
521 WHERE rbk_khr.khr_id = chrb.id
522 AND rbk_khr.status <> 'PROCESSED');
523
524
525 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
526
527 BEGIN
528
529 FOR l_chk_mass_rbk_rec IN l_chk_mass_rbk_csr
530 LOOP
531 l_ret_value := OKL_API.G_TRUE;
532 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
533 END LOOP;
534
535 RETURN l_ret_value;
536
537 EXCEPTION
538 WHEN OTHERS THEN
539 RETURN(NULL);
540 END check_mass_rebook_contract;
541
542 FUNCTION check_rebook_contract(
543 p_chr_id IN NUMBER)
544 RETURN VARCHAR2
545 AS
546
547 --cursor to check if the contract is rebooked contract
548 CURSOR l_chk_rbk_csr
549 IS
550 SELECT '1'
551 FROM okc_k_headers_b chrb,
552 okl_trx_contracts ktrx
553 WHERE ktrx.khr_id_new = chrb.id
554 AND ktrx.tsu_code = 'ENTERED'
555 AND ktrx.rbr_code IS NOT NULL
556 AND ktrx.tcn_type = 'TRBK'
557 --rkuttiya added for 12.1.1 Multi GAAP
558 AND ktrx.representation_type = 'PRIMARY'
559 --
560 AND chrb.id = p_chr_id
561 AND chrb.orig_system_source_codE = 'OKL_REBOOK';
562
563 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
564
565 BEGIN
566
567 FOR l_chk_rbk_rec IN l_chk_rbk_csr
568 LOOP
569 l_ret_value := OKL_API.G_TRUE;
570 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
571 END LOOP;
572
573 RETURN l_ret_value;
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 RETURN(NULL);
578 END check_rebook_contract;
579
580 FUNCTION check_release_contract(
581 p_chr_id IN NUMBER)
582 RETURN VARCHAR2
583 AS
584
585 --cursor to check if contract is a re-lease contract
586 CURSOR l_chk_rel_khr_csr
587 IS
588 SELECT '1'
589 FROM okc_k_headers_b chrb
590 WHERE chrb.id = p_chr_id
591 AND NVL(chrb.orig_system_source_code,'XXXX') = 'OKL_RELEASE';
592
593 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
594
595 BEGIN
596
597 FOR l_chk_rel_khr_rec IN l_chk_rel_khr_csr
598 LOOP
599 l_ret_value := OKL_API.G_TRUE;
600 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
601 END LOOP;
602
603 RETURN l_ret_value;
604
605 EXCEPTION
606 WHEN OTHERS THEN
607 RETURN(NULL);
608 END check_release_contract;
609
610 FUNCTION check_release_assets(
611 p_chr_id IN NUMBER)
612 RETURN VARCHAR2
613 AS
614
615 --cursor to check if contract has re-lease assets
616 CURSOR l_chk_rel_ast_csr IS
617 SELECT '1'
618 FROM okc_k_headers_b chrb
619 WHERE NVL(chrb.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
620 AND chrb.ID = p_chr_id
621 AND EXISTS (SELECT '1'
622 FROM okc_rules_b rul
623 WHERE rul.dnz_chr_id = chrb.id
624 AND rul.rule_information_category = 'LARLES'
625 AND NVL(rule_information1,'N') = 'Y');
626
627
628 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
629
630 BEGIN
631
632 FOR l_chk_rel_ast_rec IN l_chk_rel_ast_csr
633 LOOP
634 l_ret_value := OKL_API.G_TRUE;
635 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
636 END LOOP;
637
638 RETURN l_ret_value;
639
640 EXCEPTION
641 WHEN OTHERS THEN
642 RETURN(NULL);
643 END check_release_assets;
644
645 FUNCTION check_split_contract(
646 p_chr_id IN NUMBER)
647 RETURN VARCHAR2
648 AS
649 l_ret_value VARCHAR2(1) DEFAULT '0';
650 BEGIN
651 -- NEED TO CODE LATER ??
652 RETURN OKL_API.G_FALSE;
653 EXCEPTION
654 WHEN OTHERS THEN
655 RETURN(NULL);
656 END check_split_contract;
657
658 FUNCTION check_new_contract(
659 p_chr_id IN NUMBER)
660 RETURN VARCHAR2
661 AS
662 l_ret_value VARCHAR2(1) DEFAULT '0';
663 BEGIN
664
665 l_ret_value := check_mass_rebook_contract(p_chr_id);
666 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
667 RETURN OKL_API.G_FALSE;
668 -- not a mass rebook
669 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
670 l_ret_value := check_rebook_contract(p_chr_id);
671 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
672 RETURN OKL_API.G_FALSE;
673 -- not a rebook
674 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
675 l_ret_value := check_release_contract(p_chr_id);
676 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
677 RETURN OKL_API.G_FALSE;
678 -- not a release contract
679 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
680 l_ret_value := check_release_assets(p_chr_id);
681 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
682 RETURN OKL_API.G_FALSE;
683 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
684 l_ret_value := check_split_contract(p_chr_id);
685 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
686 RETURN OKL_API.G_FALSE;
687 ELSE
688 -- not a release asset contract
689 RETURN OKL_API.G_TRUE;
690 END IF;
691 END IF;
692 END IF;
693 END IF;
694 END IF;
695
696 EXCEPTION
697 WHEN OTHERS THEN
698 RETURN(NULL);
699 END check_new_contract;
700
701 FUNCTION get_contract_process(
702 p_chr_id IN NUMBER)
703 RETURN VARCHAR2
704 AS
705 l_ret_value VARCHAR2(1) DEFAULT '0';
706 l_process VARCHAR2(20);
707
708 BEGIN
709
710 l_ret_value := check_mass_rebook_contract(p_chr_id);
711 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
712 RETURN G_KHR_PROCESS_MASS_REBOOK;
713 -- not a mass rebook
714 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
715 l_ret_value := check_rebook_contract(p_chr_id);
716 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
717 RETURN G_KHR_PROCESS_REBOOK;
718 -- not a rebook
719 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
720 l_ret_value := check_release_contract(p_chr_id);
721 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
722 RETURN G_KHR_PROCESS_RELEASE_CONTRACT;
723 -- not a release contract
724 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
725 l_ret_value := check_release_assets(p_chr_id);
726 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
727 RETURN G_KHR_PROCESS_RELEASE_ASSETS;
728 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
729 l_ret_value := check_split_contract(p_chr_id);
730 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
731 RETURN G_KHR_PROCESS_SPLIT_CONTRACT;
732 ELSE
733 -- not a release asset contract
734 RETURN G_KHR_PROCESS_NEW;
735 END IF;
736 END IF;
737 END IF;
738 END IF;
739 END IF;
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 RETURN(NULL);
744 END get_contract_process;
745
746 FUNCTION is_lease_contract(
747 p_chr_id okc_k_headers_b.id%TYPE)
748 RETURN VARCHAR2
749 IS
750 CURSOR l_okl_chr_scs_csr
751 IS
752 SELECT scs_code
753 FROM okc_k_headers_b
754 WHERE id = p_chr_id;
755
756 l_scs_code okc_k_headers_b.scs_code%TYPE := NULL;
757 l_return_value VARCHAR2(1):= OKL_API.G_FALSE;
758 BEGIN
759 FOR l_okl_chr_scs_rec IN l_okl_chr_scs_csr
760 LOOP
761 IF (l_okl_chr_scs_rec.scs_code IS NOT NULL AND l_okl_chr_scs_rec.scs_code = 'LEASE') THEN
762 l_return_value := OKL_API.G_TRUE;
763 END IF;
764 END LOOP;
765
766 RETURN l_return_value;
767
768 END is_lease_contract;
769
770 /* -- end, mvasudev, 08/17/2004 */
771
772 -- mvasudev,| 07-25-2005 cklee/mvasudev -- Fixed 11.5.9 Bug#4392051/okl.h 4437938 |
773
774 /* rajose
775 The following function is called for calculation of contract end date and payment
776 structure end date.
777 P_start_day i/p parameter is the differentiating factor.
778 If p_start_day is null the logic for calculating contract end date is followed else logic for
779 payment structure end dates is followed.
780 If p_start_day is passed its mandatory to pass the contract end date, as the contract end
781 date is used to check whether the payment structure end date has reached the end date of the contract.
782 If contract end dated has not reached or contract end date is not passed, the end date calculation
783 of the payment structure follows OKL G logic of add_months(start_date,period) -1.
784 */
785 FUNCTION calculate_end_date(
786 p_start_date IN DATE,
787 p_months IN NUMBER,
788 p_start_day IN NUMBER DEFAULT NULL,
789 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
790 )
791 RETURN DATE
792 IS
793 l_next_start_date DATE;
794
795 l_next_start_day NUMBER;
796 l_next_start_month NUMBER;
797 l_next_start_year NUMBER;
798 l_start_month NUMBER;
799
800 l_start_last_day NUMBER;
801 l_next_start_last_day NUMBER;
802
803 l_end_date DATE;
804 l_start_day NUMBER;
805 l_end_day NUMBER;
806
807 --Bug 6007644
808 l_return_status VARCHAR2(1);
809 l_temp_day NUMBER;
810 l_temp_month NUMBER;
811 l_temp_year NUMBER;
812 --end Bug 6007644
813
814 BEGIN
815
816 -- Bug 6007644
817 OKL_STREAM_GENERATOR_PVT.add_months_new(p_start_date => p_start_date,
818 p_months_after => p_months,
819 x_date => l_end_date,
820 x_return_status => l_return_status);
821
822 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
823 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
824 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
825 RAISE OKL_API.G_EXCEPTION_ERROR;
826 END IF;
827 --end Bug 6007644
828
829 IF p_start_day IS NOT NULL THEN
830
831 --Bug 6007644
832 IF p_start_day = 31 THEN
833 l_end_date := LAST_DAY(l_end_date);
834 END IF;
835
836 IF(p_start_day in(29, 30)) THEN
837 l_temp_month := to_char(l_end_date, 'MM');
838 l_temp_year := to_char(l_end_date, 'YYYY');
839 IF(l_temp_month = 2) THEN
840 IF mod(l_temp_year,400 ) = 0 OR (mod(l_temp_year, 100) <> 0 AND mod(l_temp_year,4) = 0)
841 THEN
842 -- Leap Year is divisible by 4, but not with 100 except for the years which are divisible by 400
843 -- Like 1900 is not leap year, but 2000 is a leap year
844 l_temp_day := 29;
845 ELSE
846 -- Its a non Leap Year
847 l_temp_day := 28;
848 END IF;
849 ELSE
850 l_temp_day := p_start_day;
851 END IF;
852 l_end_date := to_date(l_temp_day || '-' || l_temp_month || '-' || l_temp_year, 'DD-MM-YYYY');
853 END IF;
854 --end Bug 6007644
855
856 END IF;
857
858 -- Bug 6007644
859 l_end_date := l_end_date - 1;
860 -- end Bug 6007644
861
862 RETURN (l_end_date);
863
864 EXCEPTION
865 WHEN OTHERS THEN
866 RETURN(NULL);
867 END calculate_end_date;
868 -- end,| 07-25-2005 cklee/mvasudev -- Fixed 11.5.9 Bug#4392051/okl.h 4437938 |
869
870 --Bug# 4959361
871 PROCEDURE check_line_update_allowed(p_api_version IN NUMBER,
872 p_init_msg_list IN VARCHAR2,
873 x_return_status OUT NOCOPY VARCHAR2,
874 x_msg_count OUT NOCOPY NUMBER,
875 x_msg_data OUT NOCOPY VARCHAR2,
876 p_cle_id IN NUMBER) IS
877
878 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
879 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_LINE_UPDATE_ALLOWED';
880 l_api_version CONSTANT NUMBER := 1.0;
881
882 --cursor to check line status
883 CURSOR l_cle_csr(p_cle_id IN NUMBER)
884 IS
885 SELECT cle.sts_code,
886 cle.dnz_chr_id
887 FROM okc_k_lines_b cle
888 WHERE cle.id = p_cle_id;
889
890 l_cle_rec l_cle_csr%ROWTYPE;
891 l_chk_rebook_chr VARCHAR2(1);
892
893 BEGIN
894 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
895 -- Call start_activity to create savepoint, check compatibility
896 -- and initialize message list
897 l_return_status := Okl_Api.START_ACTIVITY(
898 p_api_name => l_api_name,
899 p_pkg_name => g_pkg_name,
900 p_init_msg_list => p_init_msg_list,
901 l_api_version => l_api_version,
902 p_api_version => p_api_version,
903 p_api_type => '_PVT',
904 x_return_status => x_return_status);
905 -- Check if activity started successfully
906 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
907 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
908 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
909 RAISE Okl_Api.G_EXCEPTION_ERROR;
910 END IF;
911
912 OPEN l_cle_csr(p_cle_id => p_cle_id);
913 FETCH l_cle_csr INTO l_cle_rec;
914 CLOSE l_cle_csr;
915
916 l_chk_rebook_chr := OKL_LLA_UTIL_PVT.check_rebook_contract(p_chr_id => l_cle_rec.dnz_chr_id);
917
918 IF (l_chk_rebook_chr = OKL_API.G_TRUE AND l_cle_rec.sts_code = 'TERMINATED') THEN
919 OKL_API.set_message(p_app_name => G_APP_NAME,
920 p_msg_name => 'OKL_LA_RBK_TER_LINE_UPDATE');
921 RAISE OKL_API.G_EXCEPTION_ERROR;
922 END IF;
923
924 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
925 x_msg_data => x_msg_data);
926
927 EXCEPTION
928 when OKL_API.G_EXCEPTION_ERROR then
929
930 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
931 p_api_name => l_api_name,
932 p_pkg_name => G_PKG_NAME,
933 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
934 x_msg_count => x_msg_count,
935 x_msg_data => x_msg_data,
936 p_api_type => G_API_TYPE);
937
938 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
939
940 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
941 p_api_name => l_api_name,
942 p_pkg_name => G_PKG_NAME,
943 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
944 x_msg_count => x_msg_count,
945 x_msg_data => x_msg_data,
946 p_api_type => G_API_TYPE);
947
948 when OTHERS then
949
950 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
951 p_api_name => l_api_name,
952 p_pkg_name => G_PKG_NAME,
953 p_exc_name => 'OTHERS',
954 x_msg_count => x_msg_count,
955 x_msg_data => x_msg_data,
956 p_api_type => G_API_TYPE);
957 END check_line_update_allowed;
958 --Bug# 4959361
959 --added by asawanka
960 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
961 -- p_khr_id is contract id
962 FUNCTION get_asset_location(
963 p_kle_id IN NUMBER,
964 p_khr_id IN NUMBER)
965 RETURN VARCHAR2 IS
966 CURSOR l_khr_status_csr IS
967 SELECT STS_CODE
968 FROM okc_k_headers_all_b
969 WHERE ID = p_khr_id;
970
971 CURSOR l_get_booked_Astloc_csr IS
972 SELECT SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,HL.ADDRESS1,HL.ADDRESS2,HL.ADDRESS3,
973 HL.ADDRESS4,HL.CITY,HL.COUNTY,HL.STATE,HL.PROVINCE,HL.POSTAL_CODE,NULL,HL.COUNTRY,
974 NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
975 FROM HZ_LOCATIONS HL,
976 CSI_ITEM_INSTANCES CSI,
977 OKC_K_ITEMS CIM
978 WHERE CIM.CLE_ID = (SELECT A.ID
979 FROM OKC_K_LINES_V A,
980 OKC_LINE_STYLES_B B
981 WHERE CLE_ID = (SELECT A.ID
982 FROM OKC_K_LINES_V A,
983 OKC_LINE_STYLES_B B
984 WHERE CLE_ID = p_kle_id
985 AND A.LSE_ID = B.ID
986 AND A.dnz_chr_id = p_khr_id
987 AND B.LTY_CODE = 'FREE_FORM2')
988 AND A.LSE_ID = B.ID
989 AND A.dnz_chr_id = p_khr_id
990 AND B.LTY_CODE = 'INST_ITEM')
991 AND CIM.DNZ_CHR_ID = p_khr_id
992 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
993 AND CIM.OBJECT1_ID2 = '#'
994 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
995 AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
996 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
997 UNION
998 SELECT SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,HL.ADDRESS1,HL.ADDRESS2,HL.ADDRESS3,
999 HL.ADDRESS4,HL.CITY,HL.COUNTY,HL.STATE,HL.PROVINCE,HL.POSTAL_CODE,NULL,HL.COUNTRY,
1000 NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
1001 FROM HZ_LOCATIONS HL,
1002 HZ_PARTY_SITES HPS,
1003 CSI_ITEM_INSTANCES CSI,
1004 OKC_K_ITEMS CIM
1005 WHERE CIM.CLE_ID = (SELECT A.ID
1006 FROM OKC_K_LINES_V A,
1007 OKC_LINE_STYLES_B B
1008 WHERE CLE_ID = (SELECT A.ID
1009 FROM OKC_K_LINES_V A,
1010 OKC_LINE_STYLES_B B
1011 WHERE CLE_ID = p_kle_id
1012 AND A.LSE_ID = B.ID
1013 AND A.dnz_chr_id = p_khr_id
1014 AND B.LTY_CODE = 'FREE_FORM2')
1015 AND A.LSE_ID = B.ID
1016 AND A.dnz_chr_id = p_khr_id
1017 AND B.LTY_CODE = 'INST_ITEM')
1018 AND CIM.DNZ_CHR_ID = p_khr_id
1019 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1020 AND CIM.OBJECT1_ID2 = '#'
1021 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1022 AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
1023 AND HPS.LOCATION_ID = HL.LOCATION_ID
1024 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES';
1025
1026 CURSOR l_get_nonbooked_Astloc_csr IS
1027 SELECT B.DESCRIPTION
1028 FROM OKX_PARTY_SITE_USES_V B
1029 WHERE B.ID1 = (SELECT A.OBJECT_ID1_NEW
1030 FROM OKL_TXL_ITM_INSTS_V A
1031 WHERE A.KLE_ID = (SELECT A.ID
1032 FROM OKC_K_LINES_V A,
1033 OKC_LINE_STYLES_B B
1034 WHERE CLE_ID = (SELECT A.ID
1035 FROM OKC_K_LINES_V A,
1036 OKC_LINE_STYLES_B B
1037 WHERE CLE_ID = p_kle_id
1038 AND A.LSE_ID = B.ID
1039 AND A.dnz_chr_id = p_khr_id
1040 AND B.LTY_CODE = 'FREE_FORM2')
1041 AND A.LSE_ID = B.ID
1042 AND A.dnz_chr_id = p_khr_id
1043 AND B.LTY_CODE = 'INST_ITEM'))
1044 AND B.ID2 = '#';
1045 l_khr_sts VARCHAR2(240);
1046 l_asset_loc VARCHAR2(240) := NULL;
1047
1048 BEGIN
1049 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1050 RETURN NULL;
1051 END IF;
1052
1053 OPEN l_khr_status_csr;
1054 FETCH l_khr_status_csr INTO l_khr_sts;
1055 CLOSE l_khr_status_csr;
1056
1057 IF l_khr_sts = 'BOOKED' THEN
1058 OPEN l_get_booked_Astloc_csr;
1059 FETCH l_get_booked_Astloc_csr INTO l_asset_loc;
1060 CLOSE l_get_booked_Astloc_csr;
1061 ELSE
1062 OPEN l_get_nonbooked_Astloc_csr;
1063 FETCH l_get_nonbooked_Astloc_csr INTO l_asset_loc;
1064 CLOSE l_get_nonbooked_Astloc_csr;
1065 END IF;
1066
1067 RETURN l_asset_loc;
1068
1069 EXCEPTION
1070 WHEN OTHERS THEN
1071 RETURN NULL;
1072 END get_asset_location;
1073 --added by asawanka
1074 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
1075 -- p_khr_id is contract id
1076 FUNCTION get_ast_install_loc_id(
1077 p_kle_id IN NUMBER,
1078 p_khr_id IN NUMBER)
1079 RETURN NUMBER IS
1080 CURSOR l_khr_status_csr IS
1081 SELECT STS_CODE
1082 FROM okc_k_headers_all_b
1083 WHERE ID = p_khr_id;
1084
1085 CURSOR l_get_booked_Astloc_csr IS
1086 SELECT psu.party_site_use_id
1087 FROM HZ_LOCATIONS HL,
1088 CSI_ITEM_INSTANCES CSI,
1089 OKC_K_ITEMS CIM,
1090 hz_party_site_uses psu,
1091 hz_party_sites hps
1092 WHERE CIM.CLE_ID in (SELECT A.ID
1093 FROM OKC_K_LINES_V A,
1094 OKC_LINE_STYLES_B B
1095 WHERE CLE_ID in (SELECT A.ID
1096 FROM OKC_K_LINES_V A,
1097 OKC_LINE_STYLES_B B
1098 WHERE CLE_ID = p_kle_id
1099 AND A.LSE_ID = B.ID
1100 AND A.dnz_chr_id = p_khr_id
1101 AND B.LTY_CODE = 'FREE_FORM2')
1102 AND A.LSE_ID = B.ID
1103 AND A.dnz_chr_id = p_khr_id
1104 AND B.LTY_CODE = 'INST_ITEM')
1105 AND CIM.DNZ_CHR_ID = p_khr_id
1106 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1107 AND CIM.OBJECT1_ID2 = '#'
1108 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1109 AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
1110 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
1111 AND psu.site_use_type ='INSTALL_AT'
1112 AND psu.party_site_id = hps.party_site_id
1113 AND hps.location_id = hl.location_id
1114 UNION
1115 SELECT psu.party_site_use_id
1116 FROM HZ_LOCATIONS HL,
1117 HZ_PARTY_SITES HPS,
1118 HZ_PARTY_SITE_USES PSU,
1119 CSI_ITEM_INSTANCES CSI,
1120 OKC_K_ITEMS CIM
1121 WHERE CIM.CLE_ID in (SELECT A.ID
1122 FROM OKC_K_LINES_V A,
1123 OKC_LINE_STYLES_B B
1124 WHERE CLE_ID in (SELECT A.ID
1125 FROM OKC_K_LINES_V A,
1126 OKC_LINE_STYLES_B B
1127 WHERE CLE_ID = p_kle_id
1128 AND A.LSE_ID = B.ID
1129 AND A.dnz_chr_id = p_khr_id
1130 AND B.LTY_CODE = 'FREE_FORM2')
1131 AND A.LSE_ID = B.ID
1132 AND A.dnz_chr_id = p_khr_id
1133 AND B.LTY_CODE = 'INST_ITEM')
1134 AND CIM.DNZ_CHR_ID = p_khr_id
1135 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1136 AND CIM.OBJECT1_ID2 = '#'
1137 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1138 AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
1139 AND HPS.LOCATION_ID = HL.LOCATION_ID
1140 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
1141 AND psu.party_site_id = hps.party_site_id
1142 AND psu.site_use_type = 'INSTALL_AT';
1143
1144 CURSOR l_get_nonbooked_Astloc_csr IS
1145 SELECT A.OBJECT_ID1_NEW
1146 FROM OKL_TXL_ITM_INSTS_V A
1147 WHERE A.KLE_ID IN (SELECT A.ID
1148 FROM OKC_K_LINES_V A,
1149 OKC_LINE_STYLES_B B
1150 WHERE CLE_ID IN (SELECT A.ID
1151 FROM OKC_K_LINES_V A,
1152 OKC_LINE_STYLES_B B
1153 WHERE CLE_ID = p_kle_id
1154 AND A.LSE_ID = B.ID
1155 AND A.dnz_chr_id = p_khr_id
1156 AND B.LTY_CODE = 'FREE_FORM2')
1157 AND A.LSE_ID = B.ID
1158 AND A.dnz_chr_id = p_khr_id
1159 AND B.LTY_CODE = 'INST_ITEM');
1160 l_khr_sts VARCHAR2(240);
1161 l_asset_loc NUMBER := NULL;
1162
1163 BEGIN
1164 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1165 RETURN NULL;
1166 END IF;
1167
1168 OPEN l_khr_status_csr;
1169 FETCH l_khr_status_csr INTO l_khr_sts;
1170 CLOSE l_khr_status_csr;
1171
1172 IF l_khr_sts = 'BOOKED' THEN
1173 OPEN l_get_booked_Astloc_csr;
1174 FETCH l_get_booked_Astloc_csr INTO l_asset_loc;
1175 CLOSE l_get_booked_Astloc_csr;
1176 ELSE
1177 OPEN l_get_nonbooked_Astloc_csr;
1178 FETCH l_get_nonbooked_Astloc_csr INTO l_asset_loc;
1179 CLOSE l_get_nonbooked_Astloc_csr;
1180 END IF;
1181
1182 RETURN l_asset_loc;
1183
1184 EXCEPTION
1185 WHEN OTHERS THEN
1186 RETURN NULL;
1187 END get_ast_install_loc_id;
1188 --added by asawanka
1189 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
1190 -- p_khr_id is contract id
1191 FUNCTION get_booked_asset_number(
1192 p_kle_id IN NUMBER,
1193 p_khr_id IN NUMBER)
1194 RETURN VARCHAR2 IS
1195 CURSOR l_khr_status_csr IS
1196 SELECT STS_CODE
1197 FROM okc_k_headers_all_b
1198 WHERE ID = p_khr_id;
1199
1200 CURSOR l_get_booked_astnum_csr IS
1201 SELECT FAV.ASSET_NUMBER ASSETNUMBER
1202 FROM OKC_K_LINES_V CLE_FIN
1203 , OKC_LINE_STYLES_B LSE_FIN
1204 , OKC_K_LINES_B CLE_FA
1205 , OKC_LINE_STYLES_B LSE_FA
1206 , OKC_K_ITEMS CIM_FA
1207 , FA_ADDITIONS_B FAV
1208 WHERE CLE_FIN.CLE_ID IS NULL
1209 AND CLE_FIN.id = p_kle_id
1210 AND CLE_FIN.DNZ_CHR_ID = p_khr_id
1211 AND LSE_FIN.ID = CLE_FIN.LSE_ID
1212 AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
1213 AND CLE_FA.CLE_ID = CLE_FIN.ID
1214 AND CLE_FA.LSE_ID = LSE_FA.ID
1215 AND LSE_FA.LTY_CODE = 'FIXED_ASSET'
1216 AND CIM_FA.CLE_ID = CLE_FA.ID
1217 AND CIM_FA.OBJECT1_ID1 = FAV.ASSET_ID
1218 AND CIM_FA.OBJECT1_ID2 = '#' ;
1219
1220 l_asset_num VARCHAR2(240);
1221 l_khr_sts VARCHAR2(30);
1222 BEGIN
1223 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1224 RETURN NULL;
1225 END IF;
1226
1227 OPEN l_khr_status_csr;
1228 FETCH l_khr_status_csr INTO l_khr_sts;
1229 CLOSE l_khr_status_csr;
1230
1231 IF l_khr_sts = 'BOOKED' THEN
1232 OPEN l_get_booked_Astnum_csr;
1233 FETCH l_get_booked_Astnum_csr INTO l_asset_num;
1234 CLOSE l_get_booked_Astnum_csr;
1235 ELSE
1236 l_asset_num := NULL;
1237 END IF;
1238
1239 RETURN l_asset_num;
1240
1241 EXCEPTION
1242 WHEN OTHERS THEN
1243 RETURN NULL;
1244 END get_booked_asset_number;
1245
1246 -- Added procedure as part of Bug#6651871 to create Pay Site for Supplier start
1247
1248 PROCEDURE create_pay_site(
1249 party_id IN NUMBER,
1250 party_site_id IN NUMBER := NULL, -- added to create pay site
1251 p_org_id IN NUMBER, -- added to create pay site
1252 p_api_version IN NUMBER,
1253 p_init_msg_list IN VARCHAR2,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_count OUT NOCOPY NUMBER,
1256 x_msg_data OUT NOCOPY VARCHAR2
1257 )
1258 IS
1259 l_proc_name VARCHAR2(35) := 'create_pay_site';
1260 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1261 l_vendor_site_rec_type AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
1262 l_vendor_site_rec_upd AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
1263 l_vendor_site_id NUMBER;
1264 l_party_site_id NUMBER;
1265 l_location_id NUMBER;
1266 l_vendor_id NUMBER;
1267 l_party_site_number VARCHAR2(30);
1268 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1269 l_msg_count NUMBER;
1270 l_msg_data VARCHAR2(200);
1271 l_address_line1 VARCHAR2(240);
1272 l_city VARCHAR2(60);
1273 l_state VARCHAR2(60);
1274 l_zip VARCHAR2(60);
1275 l_country VARCHAR2(60);
1276 l_county VARCHAR2(60);
1277 l_address_style VARCHAR2(30);
1278 l_province VARCHAR2(60);
1279
1280 CURSOR get_party_site_info(p_party_id NUMBER)
1281 IS
1282 select hzps.party_site_id
1283 ,hzps.location_id
1284 ,aps.vendor_id
1285 ,hzps.party_site_number
1286 ,hzl.address1
1287 ,hzl.city
1288 ,hzl.state
1289 ,hzl.postal_code
1290 ,hzl.country
1291 ,hzl.county
1292 ,hzl.address_style
1293 ,hzl.province
1294 from
1295 hz_party_sites hzps
1296 ,hz_parties hz
1297 ,ap_suppliers aps
1298 ,hz_locations hzl
1299 where
1300 hzps.party_id = hz.party_id
1301 and hzps.IDENTIFYING_ADDRESS_FLAG = 'Y'
1302 and hz.party_id = aps.party_id
1303 and hz.party_id = p_party_id
1304 and hzps.location_id = hzl.location_id;
1305
1306
1307 CURSOR get_location_id(p_party_site_id NUMBER)
1308 IS
1309 select
1310 hzps.location_id
1311 ,hzps.party_site_number
1312 ,aps.vendor_id
1313 ,hzl.address1
1314 ,hzl.city
1315 ,hzl.state
1316 ,hzl.postal_code
1317 ,hzl.country
1318 ,hzl.county
1319 ,hzl.address_style
1320 ,hzl.province
1321 from
1322 hz_party_sites hzps
1323 ,ap_suppliers aps
1324 ,hz_locations hzl
1325 where
1326 hzps.party_id = aps.party_id
1327 and party_site_id = p_party_site_id
1328 and hzps.location_id = hzl.location_id;
1329
1330 BEGIN
1331 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1332 -- call START_ACTIVITY to create savepoint, check compatibility
1333 -- and initialize message list
1334 x_return_status := OKL_API.START_ACTIVITY(
1335 p_api_name => l_api_name,
1336 p_pkg_name => G_PKG_NAME,
1337 p_init_msg_list => p_init_msg_list,
1338 l_api_version => l_api_version,
1339 p_api_version => p_api_version,
1340 p_api_type => G_API_TYPE,
1341 x_return_status => l_return_status);
1342
1343 -- check if activity started successfully
1344 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1345 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1346 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1347 RAISE OKL_API.G_EXCEPTION_ERROR;
1348 END IF;
1349
1350 IF(party_site_id IS NOT NULL AND party_site_id <> OKL_API.G_MISS_NUM) THEN
1351 l_party_site_id := party_site_id;
1352 OPEN get_location_id(l_party_site_id);
1353 FETCH get_location_id
1354 INTO l_location_id, l_party_site_number, l_vendor_id, l_address_line1,
1355 l_city, l_state, l_zip, l_country, l_county, l_address_style, l_province;
1356 CLOSE get_location_id;
1357 ELSE
1358 OPEN get_party_site_info(party_id);
1359 FETCH get_party_site_info
1360 INTO l_party_site_id, l_location_id, l_vendor_id, l_party_site_number,l_address_line1,
1361 l_city, l_state, l_zip, l_country, l_county, l_address_style, l_province;
1362 CLOSE get_party_site_info;
1363 END IF;
1364
1365 l_vendor_site_rec_type.org_id := p_org_id;
1366 l_vendor_site_rec_type.party_site_id := l_party_site_id;
1367 l_vendor_site_rec_type.location_id := l_location_id;
1368 l_vendor_site_rec_type.vendor_id := l_vendor_id;
1369 l_vendor_site_rec_type.VENDOR_SITE_CODE := substr(l_party_site_number, 0, 14);
1370 l_vendor_site_rec_type.PURCHASING_SITE_FLAG := 'N';
1371 l_vendor_site_rec_type.PRIMARY_PAY_SITE_FLAG := 'N';
1372 l_vendor_site_rec_type.PAY_SITE_FLAG := 'Y';
1373
1374
1375 POS_VENDOR_PUB_PKG.Create_Vendor_Site
1376 (
1377 p_vendor_site_rec => l_vendor_site_rec_type,
1378 x_return_status => l_return_status,
1379 x_msg_count => l_msg_count,
1380 x_msg_data => l_msg_data,
1381 x_vendor_site_id => l_vendor_site_id,
1382 x_party_site_id => l_party_site_id,
1383 x_location_id => l_location_id
1384 );
1385 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1386 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1387 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1388 RAISE OKL_API.G_EXCEPTION_ERROR;
1389 END IF;
1390
1391 l_vendor_site_rec_upd.vendor_site_id := l_vendor_site_id;
1392 l_vendor_site_rec_upd.party_site_id := l_party_site_id;
1393 l_vendor_site_rec_upd.vendor_id := l_vendor_id;
1394 l_vendor_site_rec_upd.ADDRESS_LINE1 := l_address_line1;
1395 l_vendor_site_rec_upd.CITY := l_city;
1396 l_vendor_site_rec_upd.STATE := l_state;
1397 l_vendor_site_rec_upd.ZIP := l_zip;
1398 l_vendor_site_rec_upd.COUNTRY := l_country;
1399 l_vendor_site_rec_upd.COUNTY := l_county;
1400 l_vendor_site_rec_upd.ADDRESS_STYLE := l_address_style;
1401 l_vendor_site_rec_upd.PROVINCE := l_province;
1402
1403 POS_VENDOR_PUB_PKG.Update_Vendor_Site
1404 (
1405 p_vendor_site_rec => l_vendor_site_rec_upd,
1406 x_return_status => l_return_status,
1407 x_msg_count => l_msg_count,
1408 x_msg_data => l_msg_data
1409 );
1410
1411 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1412 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1413 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1414 RAISE OKL_API.G_EXCEPTION_ERROR;
1415 END IF;
1416
1417
1418 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1419 x_msg_data => x_msg_data);
1420 EXCEPTION
1421 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1422 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1423 p_api_name => l_api_name,
1424 p_pkg_name => G_PKG_NAME,
1425 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1426 x_msg_count => x_msg_count,
1427 x_msg_data => x_msg_data,
1428 p_api_type => G_API_TYPE);
1429
1430 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1431 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1432 p_api_name => l_api_name,
1433 p_pkg_name => G_PKG_NAME,
1434 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1435 x_msg_count => x_msg_count,
1436 x_msg_data => x_msg_data,
1437 p_api_type => G_API_TYPE);
1438
1439 WHEN OTHERS THEN
1440 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1441 p_api_name => l_api_name,
1442 p_pkg_name => G_PKG_NAME,
1443 p_exc_name => 'OTHERS',
1444 x_msg_count => x_msg_count,
1445 x_msg_data => x_msg_data,
1446 p_api_type => G_API_TYPE);
1447 END create_pay_site;
1448
1449 -- Added procedure as part of Bug#6651871 to create Pay Site for Supplier end
1450
1451 -- Added procedure as part of Bug#6636587 to Create Vendor for a Party in TCA start
1452
1453 PROCEDURE create_related_vendor(
1454 party_id IN NUMBER,
1455 party_site_id IN NUMBER := NULL, -- added to create pay site
1456 p_org_id IN NUMBER , -- added to create pay site
1457 p_api_version IN NUMBER,
1458 p_init_msg_list IN VARCHAR2,
1459 x_return_status OUT NOCOPY VARCHAR2,
1460 x_msg_count OUT NOCOPY NUMBER,
1461 x_msg_data OUT NOCOPY VARCHAR2
1462 )
1463 IS
1464 l_proc_name VARCHAR2(35) := 'create_related_vendor';
1465 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1466 l_vendor_rec APPS.AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE;
1467 l_vendor_id NUMBER;
1468 l_party_id NUMBER;
1469 l_party_site_id NUMBER;
1470 l_org_id NUMBER;
1471 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1472 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
1473 l_msg_count NUMBER;
1474 l_msg_data VARCHAR2(200);
1475
1476
1477 BEGIN
1478 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1479 -- call START_ACTIVITY to create savepoint, check compatibility
1480 -- and initialize message list
1481 x_return_status := OKL_API.START_ACTIVITY(
1482 p_api_name => l_api_name,
1483 p_pkg_name => G_PKG_NAME,
1484 p_init_msg_list => p_init_msg_list,
1485 l_api_version => l_api_version,
1486 p_api_version => p_api_version,
1487 p_api_type => G_API_TYPE,
1488 x_return_status => x_return_status);
1489
1490 -- check if activity started successfully
1491 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1492 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1493 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1494 RAISE OKL_API.G_EXCEPTION_ERROR;
1495 END IF;
1496
1497 l_party_site_id := party_site_id;
1498 l_org_id := p_org_id;
1499 l_vendor_rec.PARTY_ID := party_id;
1500
1501 pos_vendor_pub_pkg.create_vendor(
1502 p_vendor_rec => l_vendor_rec,
1503 x_return_status => l_return_status,
1504 x_msg_count => l_msg_count,
1505 x_msg_data => l_msg_data ,
1506 x_vendor_id => l_vendor_id,
1507 x_party_id => l_party_id);
1508
1509 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1510 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1511 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1512 RAISE OKL_API.G_EXCEPTION_ERROR;
1513 END IF;
1514
1515 create_pay_site(
1516 party_id => l_party_id,
1517 party_site_id => l_party_site_id,
1518 p_org_id => l_org_id,
1519 p_api_version => l_api_version,
1520 p_init_msg_list => l_init_msg_list,
1521 x_return_status => l_return_status,
1522 x_msg_count => l_msg_count,
1523 x_msg_data => l_msg_data
1524 );
1525
1526 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1527 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1528 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1529 RAISE OKL_API.G_EXCEPTION_ERROR;
1530 END IF;
1531
1532 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1533 x_msg_data => x_msg_data);
1534
1535
1536
1537 EXCEPTION
1538 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1539 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1540 p_api_name => l_api_name,
1541 p_pkg_name => G_PKG_NAME,
1542 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1543 x_msg_count => x_msg_count,
1544 x_msg_data => x_msg_data,
1545 p_api_type => G_API_TYPE);
1546
1547 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1548 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1549 p_api_name => l_api_name,
1550 p_pkg_name => G_PKG_NAME,
1551 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1552 x_msg_count => x_msg_count,
1553 x_msg_data => x_msg_data,
1554 p_api_type => G_API_TYPE);
1555
1556 WHEN OTHERS THEN
1557 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1558 p_api_name => l_api_name,
1559 p_pkg_name => G_PKG_NAME,
1560 p_exc_name => 'OTHERS',
1561 x_msg_count => x_msg_count,
1562 x_msg_data => x_msg_data,
1563 p_api_type => G_API_TYPE);
1564
1565 END create_related_vendor;
1566 -- Added procedure as part of Bug#6636587 to Create Vendor for a Party in TCA start
1567
1568 -- Added to create Pay Site for Supplier end
1569
1570 --Bug# 8370699
1571 FUNCTION get_last_activation_date(
1572 p_chr_id IN NUMBER)
1573 RETURN DATE
1574 AS
1575 --cursor to fetch last activation date
1576 CURSOR l_last_activation_date_csr(p_chr_id IN NUMBER)
1577 IS
1578 SELECT MAX(ktrx.transaction_date)
1579 FROM okc_k_headers_b chrb,
1580 okl_trx_contracts ktrx
1581 WHERE chrb.id = p_chr_id
1582 AND ktrx.khr_id = chrb.id
1583 AND ktrx.tsu_code = 'PROCESSED'
1584 AND ktrx.tcn_type IN ('TRBK','SPA','BKG','REL')
1585 AND ktrx.representation_type = 'PRIMARY';
1586
1587 l_last_activation_date DATE;
1588
1589 BEGIN
1590
1591 OPEN l_last_activation_date_csr(p_chr_id => p_chr_id);
1592 FETCH l_last_activation_date_csr INTO l_last_activation_date;
1593 CLOSE l_last_activation_date_csr;
1594
1595 RETURN l_last_activation_date;
1596
1597 EXCEPTION
1598 WHEN OTHERS THEN
1599 RETURN(NULL);
1600 END get_last_activation_date;
1601
1602 --==================================================================
1603 -- New procedure added to establish external ID for the contract
1604 -- where new lines are added.
1605 --==================================================================
1606 /*
1607 procedure update_external_id (p_chr_id in number,
1608 x_return_status OUT NOCOPY VARCHAR2) IS
1609
1610 cursor get_line_details_csr(p_chr_id in number) IS
1611 SELECT KLE.ID ID
1612 FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
1613 WHERE CLE.DNZ_CHR_ID = p_chr_id
1614 AND CLE.ID = KLE.ID
1615 AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
1616 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
1617 AND kle.orig_contract_line_id is null;
1618
1619 cursor get_vendor_extid_csr(p_chr_id in number) is
1620 SELECT vDtls.ID
1621 FROM okl_party_payment_hdr vHdr,
1622 okl_party_payment_dtls vDtls,
1623 okc_k_lines_b cle,
1624 okl_k_lines kle
1625 WHERE vDtls.payment_hdr_id = vHdr.id
1626 AND vHdr.CLE_ID = cle.id
1627 AND vHdr.DNZ_CHR_ID = p_chr_id
1628 AND vHdr.PASSTHRU_TERM = 'BASE'
1629 AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
1630 AND cle.lse_id = 52
1631 AND cle.id = kle.id
1632 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
1633 and kle.fee_type = 'PASSTHROUGH'
1634 and vDtls.orig_contract_line_id is null;
1635
1636 TYPE EXTR_ID_TBL is table of number index by binary_integer;
1637 T_EXTR_ID_TBL EXTR_ID_TBL;
1638 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EXTERNAL_ID';
1639 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1640 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
1641 l_msg_count NUMBER;
1642 l_msg_data VARCHAR2(200);
1643 BEGIN
1644 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1645 -- Call start_activity to create savepoint, check compatibility
1646 -- and initialize message list
1647 x_return_status := OKL_API.START_ACTIVITY (
1648 l_api_name
1649 ,l_init_msg_list
1650 ,'_PVT'
1651 ,x_return_status);
1652 -- Check if activity started successfully
1653 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1654 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1655 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1656 RAISE OKL_API.G_EXCEPTION_ERROR;
1657 END IF;
1658
1659 OPEN get_line_details_csr(p_chr_id);
1660 FETCH get_line_details_csr bulk collect into t_extr_id_tbl;
1661 CLOSE get_line_details_csr;
1662
1663 if t_extr_id_tbl.count > 0 then
1664 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1665 update okl_k_lines
1666 set orig_contract_line_id = t_extr_id_tbl(i)
1667 where id = t_extr_id_tbl(i)
1668 and orig_contract_line_id is null;
1669 end if;
1670
1671 t_extr_id_tbl.delete;
1672
1673 open get_vendor_extid_csr(p_chr_id);
1674 fetch get_vendor_extid_csr bulk collect into t_extr_id_tbl;
1675 close get_vendor_extid_csr;
1676
1677 if t_extr_id_tbl.count > 0 then
1678 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1679 update okl_party_payment_dtls
1680 set orig_contract_line_id = t_extr_id_tbl(i)
1681 where id = t_extr_id_tbl(i)
1682 and orig_contract_line_id is null;
1683 end if;
1684
1685 t_extr_id_tbl.delete;
1686
1687 exception
1688 WHEN OKL_API.G_EXCEPTION_ERROR then
1689 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1690 l_api_name,
1691 G_PKG_NAME,
1692 'OKL_API.G_RET_STS_ERROR',
1693 l_msg_count,
1694 l_msg_data,
1695 '_PVT');
1696 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1697 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1698 l_api_name,
1699 G_PKG_NAME,
1700 'OKL_API.G_RET_STS_UNEXP_ERROR',
1701 l_msg_count,
1702 l_msg_data,
1703 '_PVT');
1704 WHEN OTHERS then
1705 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1706 l_api_name,
1707 G_PKG_NAME,
1708 'OTHERS',
1709 l_msg_count,
1710 l_msg_data,
1711 '_PVT');
1712 END update_external_id;
1713 */
1714
1715 --==================================================================
1716 -- New procedure added to establish external ID for the contract
1717 -- where new lines are added.
1718 --==================================================================
1719 --==================================================================
1720 -- New procedure added to establish external ID for the contract
1721 -- where new lines are added.
1722 --==================================================================
1723 procedure update_external_id (p_chr_id in number,
1724 x_return_status OUT NOCOPY VARCHAR2) IS
1725
1726 cursor get_line_details_csr(p_chr_id in number) IS
1727 SELECT KLE.ID ID
1728 FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
1729 WHERE CLE.DNZ_CHR_ID = p_chr_id
1730 AND CLE.ID = KLE.ID
1731 AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
1732 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
1733 AND kle.orig_contract_line_id is null;
1734
1735 cursor get_upg_line_details_csr(p_orig_chr_id in number) IS
1736 SELECT kle.id, kle.orig_contract_line_id
1737 FROM OKL_K_LINES KLE, OKC_K_LINES_B CLE
1738 WHERE CLE.DNZ_CHR_ID = p_orig_chr_id
1739 AND CLE.ID = KLE.ID
1740 AND CLE.LSE_ID in (33, 52, 53, 70) -- 48
1741 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED');
1742
1743
1744 cursor get_vendor_extid_csr(p_chr_id in number) is
1745 SELECT vDtls.ID
1746 FROM okl_party_payment_hdr vHdr,
1747 okl_party_payment_dtls vDtls,
1748 okc_k_lines_b cle,
1749 okl_k_lines kle,
1750 okc_k_headers_all_b chr
1751 WHERE vDtls.payment_hdr_id = vHdr.id
1752 AND vHdr.CLE_ID = cle.id
1753 AND vHdr.DNZ_CHR_ID = p_chr_id
1754 AND vHdr.PASSTHRU_TERM = 'BASE'
1755 AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
1756 AND cle.lse_id = 52
1757 AND cle.id = kle.id
1758 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
1759 and kle.fee_type = 'PASSTHROUGH'
1760 and vDtls.orig_contract_line_id is null;
1761
1762 TYPE EXTR_ID_TBL is table of number index by binary_integer;
1763 T_EXTR_ID_TBL EXTR_ID_TBL;
1764 t_orig_chr_id_tbl extr_id_tbl;
1765 t_orig_cle_id_tbl extr_id_tbl;
1766 t_vendor_id_tbl extr_id_tbl;
1767
1768 l_orig_chr_id number;
1769
1770 cursor get_orig_vendor_dtls(p_orig_chr_id in number) IS
1771 SELECT vDtls.orig_contract_line_id, vDtls.vendor_id, cle.id
1772 FROM okl_party_payment_hdr vHdr,
1773 okl_party_payment_dtls vDtls,
1774 okc_k_lines_b cle,
1775 okl_k_lines kle
1776 WHERE vDtls.payment_hdr_id = vHdr.id
1777 AND vHdr.CLE_ID = cle.id
1778 AND vHdr.PASSTHRU_TERM = 'BASE'
1779 AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
1780 AND cle.lse_id = 52
1781 AND cle.id = kle.id
1782 and kle.fee_type = 'PASSTHROUGH'
1783 AND cle.sts_code NOT IN ('HOLD', 'EXPIRED', 'CANCELLED','ABANDONED', 'TERMINATED')
1784 AND vHdr.DNZ_CHR_ID = p_orig_chr_id;
1785
1786 l_orig_pymnt_dtl_id number;
1787
1788 CURSOR is_rbk_on_csr( p_khr_id IN NUMBER)
1789 IS
1790 SELECT 'Y' online_rebook_in_progress,
1791 orig_chr.id
1792 FROM okc_k_headers_all_b rbk_chr,
1793 okc_k_headers_all_b orig_chr,
1794 okl_trx_contracts_all trx
1795 WHERE rbk_chr.id = p_khr_id
1796 AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
1797 AND trx.khr_id_new = rbk_chr.id
1798 AND trx.tsu_code = 'ENTERED'
1799 AND trx.tcn_type = 'TRBK'
1800 AND rbk_chr.orig_system_id1 = orig_chr.id;
1801
1802 l_rbk_in_progress VARCHAR2(1);
1803
1804 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_EXTERNAL_ID';
1805 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1806 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
1807 l_msg_count NUMBER;
1808 l_msg_data VARCHAR2(200);
1809 BEGIN
1810 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1811 -- Call start_activity to create savepoint, check compatibility
1812 -- and initialize message list
1813 x_return_status := OKL_API.START_ACTIVITY (
1814 l_api_name
1815 ,l_init_msg_list
1816 ,'_PVT'
1817 ,x_return_status);
1818 -- Check if activity started successfully
1819 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1820 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1821 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1822 RAISE OKL_API.G_EXCEPTION_ERROR;
1823 END IF;
1824
1825 -- find out if an online rebook process is in progress
1826 -- if the call is coming from the 'Price' button, the processing is different.
1827 open is_rbk_on_csr(p_chr_id);
1828 fetch is_rbk_on_csr into l_rbk_in_progress, l_orig_chr_id;
1829 close is_rbk_on_csr;
1830
1831 -- There are 2 scenarios present for establishing the exernal_id:
1832 -- a. Upgrade calls to establish the external_id for the first time
1833 -- b. Price button on the revision page. There are 2 variations in this:
1834 -- ( i) Pricing happens for a new or an already upgraded contract
1835 -- (ii) Pricing for an online rebook copy happens after the contract is upgraded using the 'Upgrade' button
1836 --
1837 -- In the case of (a.) above, the line_id will be set as the external_id, orig_system_id1 will be null
1838 -- In the case of (b. i):
1839 -- For new, external_id will be the line_id
1840 -- For upgraded, only new lines added during the revision will have external_id. in this case, it'll be the line_id
1841 -- In the case of (b. ii):
1842 -- The original contract gets upgraded, and external_id is established.
1843 -- The rebook copy is not in sync with the original for external_ids.
1844 -- In this case, the original_system_id1 needs to updated to the rebook copy.
1845
1846 if l_rbk_in_progress is null then
1847
1848 OPEN get_line_details_csr(p_chr_id);
1849 FETCH get_line_details_csr bulk collect into t_extr_id_tbl;
1850 CLOSE get_line_details_csr;
1851
1852 if t_extr_id_tbl.count > 0 then
1853 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1854 update okl_k_lines
1855 set orig_contract_line_id = t_extr_id_tbl(i)
1856 where id = t_extr_id_tbl(i)
1857 and orig_contract_line_id is null;
1858 end if;
1859
1860 t_extr_id_tbl.delete;
1861
1862 elsif l_rbk_in_progress = 'Y' then
1863 open get_upg_line_details_csr(l_orig_chr_id);
1864 fetch get_upg_line_details_csr bulk collect into t_orig_cle_id_tbl, t_extr_id_tbl;
1865 close get_upg_line_details_csr;
1866
1867 if t_extr_id_tbl.count > 0 then
1868 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1869 update okl_k_lines a
1870 set orig_contract_line_id = t_extr_id_tbl(i)
1871 where id in (select id FROM okc_k_lines_b b
1872 where orig_system_id1 = t_orig_cle_id_tbl(i)
1873 and dnz_chr_id = p_chr_id)
1874 and orig_contract_line_id is null;
1875 end if;
1876
1877 t_extr_id_tbl.delete;
1878 t_orig_cle_id_tbl.delete;
1879
1880 -- if any new lines are added, then update the external ids for them.
1881 OPEN get_line_details_csr(p_chr_id);
1882 FETCH get_line_details_csr bulk collect into t_extr_id_tbl;
1883 CLOSE get_line_details_csr;
1884
1885 if t_extr_id_tbl.count > 0 then
1886 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1887 update okl_k_lines
1888 set orig_contract_line_id = t_extr_id_tbl(i)
1889 where id = t_extr_id_tbl(i)
1890 and orig_contract_line_id is null;
1891 end if;
1892
1893 t_extr_id_tbl.delete;
1894
1895 end if;
1896
1897 if l_rbk_in_progress is null then
1898
1899 open get_vendor_extid_csr(p_chr_id);
1900 fetch get_vendor_extid_csr bulk collect into t_extr_id_tbl;
1901 close get_vendor_extid_csr;
1902
1903 -- if the upgrade is happening on the original contract or a new line is added
1904 if t_extr_id_tbl.count > 0 then
1905 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1906 update okl_party_payment_dtls
1907 set orig_contract_line_id = t_extr_id_tbl(i)
1908 where id = t_extr_id_tbl(i)
1909 and orig_contract_line_id is null;
1910 end if;
1911
1912 elsif l_rbk_in_progress = 'Y' then
1913
1914 open get_orig_vendor_dtls(l_orig_chr_id);
1915 fetch get_orig_vendor_dtls bulk collect into t_extr_id_tbl, t_vendor_id_tbl, t_orig_cle_id_tbl;
1916 close get_orig_vendor_dtls;
1917
1918 if t_extr_id_tbl.count > 0 then
1919 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1920 update okl_party_payment_dtls a
1921 set orig_contract_line_id = t_extr_id_tbl(i)
1922 where id in (
1923 SELECT vDtls.id
1924 FROM okl_party_payment_hdr vHdr,
1925 okl_party_payment_dtls vDtls,
1926 okc_k_lines_b cle
1927 WHERE vDtls.payment_hdr_id = vHdr.id
1928 AND vHdr.CLE_ID = cle.id
1929 AND vHdr.PASSTHRU_TERM = 'BASE'
1930 AND vHdr.DNZ_CHR_ID = cle.dnz_chr_id
1931 AND cle.lse_id = 52
1932 AND vHdr.DNZ_CHR_ID = p_chr_id
1933 and cle.orig_system_id1 = t_orig_cle_id_tbl(i)
1934 and vdtls.vendor_id = t_vendor_id_tbl(i)
1935 and orig_contract_line_id is null);
1936 end if;
1937
1938 t_extr_id_tbl.delete;
1939 t_orig_cle_id_tbl.delete;
1940 t_orig_chr_id_tbl.delete;
1941 t_vendor_id_tbl.delete;
1942
1943 open get_vendor_extid_csr(p_chr_id);
1944 fetch get_vendor_extid_csr bulk collect into t_extr_id_tbl;
1945 close get_vendor_extid_csr;
1946
1947 -- if a new line is added
1948 if t_extr_id_tbl.count > 0 then
1949 forall i in t_extr_id_tbl.first..t_extr_id_tbl.last
1950 update okl_party_payment_dtls
1951 set orig_contract_line_id = t_extr_id_tbl(i)
1952 where id = t_extr_id_tbl(i)
1953 and orig_contract_line_id is null;
1954 end if;
1955
1956 t_extr_id_tbl.delete;
1957
1958 end if; -- l_rbk_in_progress = 'Y'
1959
1960 t_extr_id_tbl.delete;
1961 t_orig_cle_id_tbl.delete;
1962 t_orig_chr_id_tbl.delete;
1963 t_vendor_id_tbl.delete;
1964
1965 exception
1966 WHEN OKL_API.G_EXCEPTION_ERROR then
1967 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1968 l_api_name,
1969 G_PKG_NAME,
1970 'OKL_API.G_RET_STS_ERROR',
1971 l_msg_count,
1972 l_msg_data,
1973 '_PVT');
1974 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
1975 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1976 l_api_name,
1977 G_PKG_NAME,
1978 'OKL_API.G_RET_STS_UNEXP_ERROR',
1979 l_msg_count,
1980 l_msg_data,
1981 '_PVT');
1982 WHEN OTHERS then
1983 x_return_status :=OKL_API.HANDLE_EXCEPTIONS(
1984 l_api_name,
1985 G_PKG_NAME,
1986 'OTHERS',
1987 l_msg_count,
1988 l_msg_data,
1989 '_PVT');
1990 END update_external_id;
1991
1992 --Bug# 8756653
1993 PROCEDURE check_rebook_upgrade(p_api_version IN NUMBER,
1994 p_init_msg_list IN VARCHAR2,
1995 x_return_status OUT NOCOPY VARCHAR2,
1996 x_msg_count OUT NOCOPY NUMBER,
1997 x_msg_data OUT NOCOPY VARCHAR2,
1998 p_chr_id IN NUMBER,
1999 p_rbk_chr_id IN NUMBER DEFAULT NULL) IS
2000
2001 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2002 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_REBOOK_UPGRADE';
2003 l_api_version CONSTANT NUMBER := 1.0;
2004
2005
2006 CURSOR l_chr_upg_csr(p_chr_id IN NUMBER)
2007 IS
2008 SELECT 'Y' chr_upgraded_yn
2009 FROM okl_stream_trx_data
2010 WHERE orig_khr_id = p_chr_id
2011 AND last_trx_state = 'Y';
2012
2013 CURSOR l_acct_sys_op_csr IS
2014 SELECT amort_inc_adj_rev_dt_yn
2015 FROM okl_sys_acct_opts;
2016
2017 CURSOR l_chr_csr(p_chr_id IN NUMBER) IS
2018 SELECT contract_number
2019 FROM okc_k_headers_all_b
2020 WHERE id = p_chr_id;
2021
2022 CURSOR l_rbk_chr_upg_csr(p_rbk_chr_id IN NUMBER)
2023 IS
2024 SELECT 'Y' chr_upgraded_yn
2025 FROM okl_stream_trx_data
2026 WHERE khr_id = p_rbk_chr_id
2027 AND transaction_state is not null;
2028
2029 l_chr_upgraded_yn VARCHAR2(1);
2030 l_pricing_engine okl_st_gen_tmpt_sets.pricing_engine%TYPE;
2031 l_amort_inc_adj_rev_dt_yn okl_sys_acct_opts.amort_inc_adj_rev_dt_yn%TYPE;
2032 l_contract_number okc_k_headers_all_b.contract_number%TYPE;
2033
2034 BEGIN
2035 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2036 -- Call start_activity to create savepoint, check compatibility
2037 -- and initialize message list
2038 l_return_status := Okl_Api.START_ACTIVITY(
2039 p_api_name => l_api_name,
2040 p_pkg_name => g_pkg_name,
2041 p_init_msg_list => p_init_msg_list,
2042 l_api_version => l_api_version,
2043 p_api_version => p_api_version,
2044 p_api_type => '_PVT',
2045 x_return_status => x_return_status);
2046 -- Check if activity started successfully
2047 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2049 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2050 RAISE Okl_Api.G_EXCEPTION_ERROR;
2051 END IF;
2052
2053 l_amort_inc_adj_rev_dt_yn := 'N';
2054 OPEN l_acct_sys_op_csr;
2055 FETCH l_acct_sys_op_csr INTO l_amort_inc_adj_rev_dt_yn;
2056 CLOSE l_acct_sys_op_csr;
2057
2058 IF (NVL(l_amort_inc_adj_rev_dt_yn,'N') = 'Y') THEN
2059
2060 OKL_STREAMS_UTIL.get_pricing_engine(p_khr_id => p_chr_id,
2061 x_pricing_engine => l_pricing_engine,
2062 x_return_status => x_return_status);
2063
2064 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) then
2065 raise OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) then
2067 raise OKL_API.G_EXCEPTION_ERROR;
2068 END IF;
2069
2070 IF (l_pricing_engine = 'EXTERNAL') THEN
2071
2072 l_chr_upgraded_yn := 'N';
2073 OPEN l_chr_upg_csr(p_chr_id => p_chr_id);
2074 FETCH l_chr_upg_csr INTO l_chr_upgraded_yn;
2075 CLOSE l_chr_upg_csr;
2076
2077 IF (NVL(l_chr_upgraded_yn,'N') = 'N') THEN
2078
2079 OPEN l_chr_csr(p_chr_id => p_chr_id);
2080 FETCH l_chr_csr INTO l_contract_number;
2081 CLOSE l_chr_csr;
2082
2083 OKL_API.set_message(p_app_name => G_APP_NAME,
2084 p_msg_name => 'OKL_LA_CONTRACT_NOT_UPGRADED',
2085 p_token1 => 'CONTRACT_NUMBER',
2086 p_token1_value => l_contract_number);
2087 RAISE OKL_API.G_EXCEPTION_ERROR;
2088
2089 ELSE
2090
2091 IF p_rbk_chr_id IS NOT NULL THEN
2092 l_chr_upgraded_yn := 'N';
2093 OPEN l_rbk_chr_upg_csr(p_rbk_chr_id => p_rbk_chr_id);
2094 FETCH l_rbk_chr_upg_csr INTO l_chr_upgraded_yn;
2095 CLOSE l_rbk_chr_upg_csr;
2096
2097 IF (NVL(l_chr_upgraded_yn,'N') = 'N') THEN
2098
2099 OPEN l_chr_csr(p_chr_id => p_chr_id);
2100 FETCH l_chr_csr INTO l_contract_number;
2101 CLOSE l_chr_csr;
2102
2103 OKL_API.set_message(p_app_name => G_APP_NAME,
2104 p_msg_name => 'OKL_LA_RBK_NOT_PRICED_UPG',
2105 p_token1 => 'CONTRACT_NUMBER',
2106 p_token1_value => l_contract_number);
2107 RAISE OKL_API.G_EXCEPTION_ERROR;
2108 END IF;
2109 END IF;
2110
2111 END IF;
2112
2113 END IF;
2114 END IF;
2115
2116 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
2117 x_msg_data => x_msg_data);
2118
2119 EXCEPTION
2120 when OKL_API.G_EXCEPTION_ERROR then
2121
2122 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2123 p_api_name => l_api_name,
2124 p_pkg_name => G_PKG_NAME,
2125 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
2126 x_msg_count => x_msg_count,
2127 x_msg_data => x_msg_data,
2128 p_api_type => G_API_TYPE);
2129
2130 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
2131
2132 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2133 p_api_name => l_api_name,
2134 p_pkg_name => G_PKG_NAME,
2135 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
2136 x_msg_count => x_msg_count,
2137 x_msg_data => x_msg_data,
2138 p_api_type => G_API_TYPE);
2139
2140 when OTHERS then
2141
2142 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
2143 p_api_name => l_api_name,
2144 p_pkg_name => G_PKG_NAME,
2145 p_exc_name => 'OTHERS',
2146 x_msg_count => x_msg_count,
2147 x_msg_data => x_msg_data,
2148 p_api_type => G_API_TYPE);
2149 END check_rebook_upgrade;
2150
2151 END Okl_Lla_Util_Pvt;