[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.2 2008/09/10 19:22:27 rkuttiya 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
441 BEGIN
442 IF(LENGTH(RTRIM(p_date_out)) > 0) THEN
443 p_date_out := get_display_date(p_date_out,G_DISPLAY_MASK);
444 END IF;
445 RETURN p_date_out;
446 EXCEPTION
447 WHEN OTHERS THEN
448 RETURN(p_date_char);
449 END;
450
451 FUNCTION get_display_date
452 (p_date_char IN VARCHAR2,
453 p_date_mask IN VARCHAR2)
454 RETURN VARCHAR2
455 AS
456 -- p_date := p_date_char;
457 -- p_mask := p_date_mask;
458 l_date DATE;
459 l_date_out VARCHAR2(30);
460
461 BEGIN
462 IF(LENGTH(RTRIM(p_date_char)) > 0) THEN
463 -- p_date_out := to_char(to_date(p_date_out,g_canonical_mask), p_date_mask);
464 -- p_date_out := to_char(FND_DATE.canonical_to_date(p_date_out), p_date_mask);
465
466 --l_date := FND_DATE.canonical_to_date('2003/02/10');
467 --l_date_out := to_char(l_date,'DD-MON-YYYY');
468 l_date := FND_DATE.canonical_to_date(p_date_char);
469 l_date_out := TO_CHAR(l_date,p_date_mask);
470 END IF;
471 RETURN l_date_out;
472 EXCEPTION
473 WHEN OTHERS THEN
474 RETURN(p_date_char);
475 END;
476
477
478 FUNCTION convert_date
479 (p_date_in_char IN VARCHAR2,
480 p_date_in_mask IN VARCHAR2,
481 p_date_out_mask IN VARCHAR2)
482 RETURN VARCHAR2
483 AS
484 -- p_date := p_date_char;
485 -- p_mask := p_date_mask;
486 p_date_out VARCHAR2(15);
487
488 BEGIN
489 p_date_out := TO_CHAR(TO_DATE(p_date_in_char,p_date_in_mask), p_date_out_mask);
490 RETURN p_date_out;
491 EXCEPTION
492 WHEN OTHERS THEN
493 RETURN(NULL);
494 END;
495
496 /*
497 -- mvasudev, 08/17/2004
498 Added the following functions for Business Events Enabling
499 */
500 FUNCTION check_mass_rebook_contract(
501 p_chr_id IN NUMBER)
502 RETURN VARCHAR2
503 AS
504 --cursor to check if the contract is selected for Mass Rebook
505 CURSOR l_chk_mass_rbk_csr
506 IS
507 SELECT '1'
508 FROM okc_k_headers_b chrb,
509 okl_trx_contracts ktrx
510 WHERE chrb.ID = p_chr_id
511 AND ktrx.khr_id = chrb.id
512 AND ktrx.tsu_code = 'ENTERED'
513 AND ktrx.rbr_code IS NOT NULL
514 AND ktrx.tcn_type = 'TRBK'
515 --rkuttiya added for 12.1.1 Multi GAAP
516 AND ktrx.representation_type = 'PRIMARY'
517 --
518 AND EXISTS (SELECT '1'
519 FROM okl_rbk_selected_contract rbk_khr
520 WHERE rbk_khr.khr_id = chrb.id
521 AND rbk_khr.status <> 'PROCESSED');
522
523
524 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
525
526 BEGIN
527
528 FOR l_chk_mass_rbk_rec IN l_chk_mass_rbk_csr
529 LOOP
530 l_ret_value := OKL_API.G_TRUE;
531 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
532 END LOOP;
533
534 RETURN l_ret_value;
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 RETURN(NULL);
539 END check_mass_rebook_contract;
540
541 FUNCTION check_rebook_contract(
542 p_chr_id IN NUMBER)
543 RETURN VARCHAR2
544 AS
545
546 --cursor to check if the contract is rebooked contract
547 CURSOR l_chk_rbk_csr
548 IS
549 SELECT '1'
550 FROM okc_k_headers_b chrb,
551 okl_trx_contracts ktrx
552 WHERE ktrx.khr_id_new = chrb.id
553 AND ktrx.tsu_code = 'ENTERED'
554 AND ktrx.rbr_code IS NOT NULL
555 AND ktrx.tcn_type = 'TRBK'
556 --rkuttiya added for 12.1.1 Multi GAAP
557 AND ktrx.representation_type = 'PRIMARY'
558 --
559 AND chrb.id = p_chr_id
560 AND chrb.orig_system_source_codE = 'OKL_REBOOK';
561
562 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
563
564 BEGIN
565
566 FOR l_chk_rbk_rec IN l_chk_rbk_csr
567 LOOP
568 l_ret_value := OKL_API.G_TRUE;
569 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
570 END LOOP;
571
572 RETURN l_ret_value;
573
574 EXCEPTION
575 WHEN OTHERS THEN
576 RETURN(NULL);
577 END check_rebook_contract;
578
579 FUNCTION check_release_contract(
580 p_chr_id IN NUMBER)
581 RETURN VARCHAR2
582 AS
583
584 --cursor to check if contract is a re-lease contract
585 CURSOR l_chk_rel_khr_csr
586 IS
587 SELECT '1'
588 FROM okc_k_headers_b chrb
589 WHERE chrb.id = p_chr_id
590 AND NVL(chrb.orig_system_source_code,'XXXX') = 'OKL_RELEASE';
591
592 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
593
594 BEGIN
595
596 FOR l_chk_rel_khr_rec IN l_chk_rel_khr_csr
597 LOOP
598 l_ret_value := OKL_API.G_TRUE;
599 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
600 END LOOP;
601
602 RETURN l_ret_value;
603
604 EXCEPTION
605 WHEN OTHERS THEN
606 RETURN(NULL);
607 END check_release_contract;
608
609 FUNCTION check_release_assets(
610 p_chr_id IN NUMBER)
611 RETURN VARCHAR2
612 AS
613
614 --cursor to check if contract has re-lease assets
615 CURSOR l_chk_rel_ast_csr IS
616 SELECT '1'
617 FROM okc_k_headers_b chrb
618 WHERE NVL(chrb.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
619 AND chrb.ID = p_chr_id
620 AND EXISTS (SELECT '1'
621 FROM okc_rules_b rul
622 WHERE rul.dnz_chr_id = chrb.id
623 AND rul.rule_information_category = 'LARLES'
624 AND NVL(rule_information1,'N') = 'Y');
625
626
627 l_ret_value VARCHAR2(1) := OKL_API.G_FALSE;
628
629 BEGIN
630
631 FOR l_chk_rel_ast_rec IN l_chk_rel_ast_csr
632 LOOP
633 l_ret_value := OKL_API.G_TRUE;
634 EXIT WHEN l_ret_value = OKL_API.G_TRUE;
635 END LOOP;
636
637 RETURN l_ret_value;
638
639 EXCEPTION
640 WHEN OTHERS THEN
641 RETURN(NULL);
642 END check_release_assets;
643
644 FUNCTION check_split_contract(
645 p_chr_id IN NUMBER)
646 RETURN VARCHAR2
647 AS
648 l_ret_value VARCHAR2(1) DEFAULT '0';
649 BEGIN
650 -- NEED TO CODE LATER ??
651 RETURN OKL_API.G_FALSE;
652 EXCEPTION
653 WHEN OTHERS THEN
654 RETURN(NULL);
655 END check_split_contract;
656
657 FUNCTION check_new_contract(
658 p_chr_id IN NUMBER)
659 RETURN VARCHAR2
660 AS
661 l_ret_value VARCHAR2(1) DEFAULT '0';
662 BEGIN
663
664 l_ret_value := check_mass_rebook_contract(p_chr_id);
665 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
666 RETURN OKL_API.G_FALSE;
667 -- not a mass rebook
668 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
669 l_ret_value := check_rebook_contract(p_chr_id);
670 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
671 RETURN OKL_API.G_FALSE;
672 -- not a rebook
673 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
674 l_ret_value := check_release_contract(p_chr_id);
675 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
676 RETURN OKL_API.G_FALSE;
677 -- not a release contract
678 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
679 l_ret_value := check_release_assets(p_chr_id);
680 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
681 RETURN OKL_API.G_FALSE;
682 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
683 l_ret_value := check_split_contract(p_chr_id);
684 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
685 RETURN OKL_API.G_FALSE;
686 ELSE
687 -- not a release asset contract
688 RETURN OKL_API.G_TRUE;
689 END IF;
690 END IF;
691 END IF;
692 END IF;
693 END IF;
694
695 EXCEPTION
696 WHEN OTHERS THEN
697 RETURN(NULL);
698 END check_new_contract;
699
700 FUNCTION get_contract_process(
701 p_chr_id IN NUMBER)
702 RETURN VARCHAR2
703 AS
704 l_ret_value VARCHAR2(1) DEFAULT '0';
705 l_process VARCHAR2(20);
706
707 BEGIN
708
709 l_ret_value := check_mass_rebook_contract(p_chr_id);
710 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
711 RETURN G_KHR_PROCESS_MASS_REBOOK;
712 -- not a mass rebook
713 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
714 l_ret_value := check_rebook_contract(p_chr_id);
715 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
716 RETURN G_KHR_PROCESS_REBOOK;
717 -- not a rebook
718 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
719 l_ret_value := check_release_contract(p_chr_id);
720 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
721 RETURN G_KHR_PROCESS_RELEASE_CONTRACT;
722 -- not a release contract
723 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
724 l_ret_value := check_release_assets(p_chr_id);
725 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
726 RETURN G_KHR_PROCESS_RELEASE_ASSETS;
727 ELSIF (l_ret_value=OKL_API.G_FALSE) THEN
728 l_ret_value := check_split_contract(p_chr_id);
729 IF (l_ret_value IS NOT NULL) AND (l_ret_value = OKL_API.G_TRUE) THEN
730 RETURN G_KHR_PROCESS_SPLIT_CONTRACT;
731 ELSE
732 -- not a release asset contract
733 RETURN G_KHR_PROCESS_NEW;
734 END IF;
735 END IF;
736 END IF;
737 END IF;
738 END IF;
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 RETURN(NULL);
743 END get_contract_process;
744
745 FUNCTION is_lease_contract(
746 p_chr_id okc_k_headers_b.id%TYPE)
747 RETURN VARCHAR2
748 IS
749 CURSOR l_okl_chr_scs_csr
750 IS
751 SELECT scs_code
752 FROM okc_k_headers_b
753 WHERE id = p_chr_id;
754
755 l_scs_code okc_k_headers_b.scs_code%TYPE := NULL;
756 l_return_value VARCHAR2(1):= OKL_API.G_FALSE;
757 BEGIN
758 FOR l_okl_chr_scs_rec IN l_okl_chr_scs_csr
759 LOOP
760 IF (l_okl_chr_scs_rec.scs_code IS NOT NULL AND l_okl_chr_scs_rec.scs_code = 'LEASE') THEN
761 l_return_value := OKL_API.G_TRUE;
762 END IF;
763 END LOOP;
764
765 RETURN l_return_value;
766
767 END is_lease_contract;
768
769 /* -- end, mvasudev, 08/17/2004 */
770
771 -- mvasudev,| 07-25-2005 cklee/mvasudev -- Fixed 11.5.9 Bug#4392051/okl.h 4437938 |
772
773 /* rajose
774 The following function is called for calculation of contract end date and payment
775 structure end date.
776 P_start_day i/p parameter is the differentiating factor.
777 If p_start_day is null the logic for calculating contract end date is followed else logic for
778 payment structure end dates is followed.
779 If p_start_day is passed its mandatory to pass the contract end date, as the contract end
780 date is used to check whether the payment structure end date has reached the end date of the contract.
781 If contract end dated has not reached or contract end date is not passed, the end date calculation
782 of the payment structure follows OKL G logic of add_months(start_date,period) -1.
783 */
784 FUNCTION calculate_end_date(
785 p_start_date IN DATE,
786 p_months IN NUMBER,
787 p_start_day IN NUMBER DEFAULT NULL,
788 p_contract_end_date IN DATE DEFAULT NULL --Bug#5441811
789 )
790 RETURN DATE
791 IS
792 l_next_start_date DATE;
793
794 l_next_start_day NUMBER;
795 l_next_start_month NUMBER;
796 l_next_start_year NUMBER;
797 l_start_month NUMBER;
798
799 l_start_last_day NUMBER;
800 l_next_start_last_day NUMBER;
801
802 l_end_date DATE;
803 l_start_day NUMBER;
804 l_end_day NUMBER;
805
806 --Bug 6007644
807 l_return_status VARCHAR2(1);
808 l_temp_day NUMBER;
809 l_temp_month NUMBER;
810 l_temp_year NUMBER;
811 --end Bug 6007644
812
813 BEGIN
814
815 -- Bug 6007644
816 OKL_STREAM_GENERATOR_PVT.add_months_new(p_start_date => p_start_date,
817 p_months_after => p_months,
818 x_date => l_end_date,
819 x_return_status => l_return_status);
820
821 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
822 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
823 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
824 RAISE OKL_API.G_EXCEPTION_ERROR;
825 END IF;
826 --end Bug 6007644
827
828 IF p_start_day IS NOT NULL THEN
829
830 --Bug 6007644
831 IF p_start_day = 31 THEN
832 l_end_date := LAST_DAY(l_end_date);
833 END IF;
834
835 IF(p_start_day in(29, 30)) THEN
836 l_temp_month := to_char(l_end_date, 'MM');
837 l_temp_year := to_char(l_end_date, 'YYYY');
838 IF(l_temp_month = 2) THEN
839 IF mod(l_temp_year,400 ) = 0 OR (mod(l_temp_year, 100) <> 0 AND mod(l_temp_year,4) = 0)
840 THEN
841 -- Leap Year is divisible by 4, but not with 100 except for the years which are divisible by 400
842 -- Like 1900 is not leap year, but 2000 is a leap year
843 l_temp_day := 29;
844 ELSE
845 -- Its a non Leap Year
846 l_temp_day := 28;
847 END IF;
848 ELSE
849 l_temp_day := p_start_day;
850 END IF;
851 l_end_date := to_date(l_temp_day || '-' || l_temp_month || '-' || l_temp_year, 'DD-MM-YYYY');
852 END IF;
853 --end Bug 6007644
854
855 END IF;
856
857 -- Bug 6007644
858 l_end_date := l_end_date - 1;
859 -- end Bug 6007644
860
861 RETURN (l_end_date);
862
863 EXCEPTION
864 WHEN OTHERS THEN
865 RETURN(NULL);
866 END calculate_end_date;
867 -- end,| 07-25-2005 cklee/mvasudev -- Fixed 11.5.9 Bug#4392051/okl.h 4437938 |
868
869 --Bug# 4959361
870 PROCEDURE check_line_update_allowed(p_api_version IN NUMBER,
871 p_init_msg_list IN VARCHAR2,
872 x_return_status OUT NOCOPY VARCHAR2,
873 x_msg_count OUT NOCOPY NUMBER,
874 x_msg_data OUT NOCOPY VARCHAR2,
875 p_cle_id IN NUMBER) IS
876
877 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
878 l_api_name CONSTANT VARCHAR2(30) := 'CHECK_LINE_UPDATE_ALLOWED';
879 l_api_version CONSTANT NUMBER := 1.0;
880
881 --cursor to check line status
882 CURSOR l_cle_csr(p_cle_id IN NUMBER)
883 IS
884 SELECT cle.sts_code,
885 cle.dnz_chr_id
886 FROM okc_k_lines_b cle
887 WHERE cle.id = p_cle_id;
888
889 l_cle_rec l_cle_csr%ROWTYPE;
890 l_chk_rebook_chr VARCHAR2(1);
891
892 BEGIN
893 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
894 -- Call start_activity to create savepoint, check compatibility
895 -- and initialize message list
896 l_return_status := Okl_Api.START_ACTIVITY(
897 p_api_name => l_api_name,
898 p_pkg_name => g_pkg_name,
899 p_init_msg_list => p_init_msg_list,
900 l_api_version => l_api_version,
901 p_api_version => p_api_version,
902 p_api_type => '_PVT',
903 x_return_status => x_return_status);
904 -- Check if activity started successfully
905 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
906 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF (x_return_status = Okl_Api.G_RET_STS_ERROR) THEN
908 RAISE Okl_Api.G_EXCEPTION_ERROR;
909 END IF;
910
911 OPEN l_cle_csr(p_cle_id => p_cle_id);
912 FETCH l_cle_csr INTO l_cle_rec;
913 CLOSE l_cle_csr;
914
915 l_chk_rebook_chr := OKL_LLA_UTIL_PVT.check_rebook_contract(p_chr_id => l_cle_rec.dnz_chr_id);
916
917 IF (l_chk_rebook_chr = OKL_API.G_TRUE AND l_cle_rec.sts_code = 'TERMINATED') THEN
918 OKL_API.set_message(p_app_name => G_APP_NAME,
919 p_msg_name => 'OKL_LA_RBK_TER_LINE_UPDATE');
920 RAISE OKL_API.G_EXCEPTION_ERROR;
921 END IF;
922
923 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
924 x_msg_data => x_msg_data);
925
926 EXCEPTION
927 when OKL_API.G_EXCEPTION_ERROR then
928
929 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
930 p_api_name => l_api_name,
931 p_pkg_name => G_PKG_NAME,
932 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_api_type => G_API_TYPE);
936
937 when OKL_API.G_EXCEPTION_UNEXPECTED_ERROR then
938
939 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
940 p_api_name => l_api_name,
941 p_pkg_name => G_PKG_NAME,
942 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
943 x_msg_count => x_msg_count,
944 x_msg_data => x_msg_data,
945 p_api_type => G_API_TYPE);
946
947 when OTHERS then
948
949 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
950 p_api_name => l_api_name,
951 p_pkg_name => G_PKG_NAME,
952 p_exc_name => 'OTHERS',
953 x_msg_count => x_msg_count,
954 x_msg_data => x_msg_data,
955 p_api_type => G_API_TYPE);
956 END check_line_update_allowed;
957 --Bug# 4959361
958 --added by asawanka
959 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
960 -- p_khr_id is contract id
961 FUNCTION get_asset_location(
962 p_kle_id IN NUMBER,
963 p_khr_id IN NUMBER)
964 RETURN VARCHAR2 IS
965 CURSOR l_khr_status_csr IS
966 SELECT STS_CODE
967 FROM okc_k_headers_all_b
968 WHERE ID = p_khr_id;
969
970 CURSOR l_get_booked_Astloc_csr IS
971 SELECT SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,HL.ADDRESS1,HL.ADDRESS2,HL.ADDRESS3,
972 HL.ADDRESS4,HL.CITY,HL.COUNTY,HL.STATE,HL.PROVINCE,HL.POSTAL_CODE,NULL,HL.COUNTRY,
973 NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
974 FROM HZ_LOCATIONS HL,
975 CSI_ITEM_INSTANCES CSI,
976 OKC_K_ITEMS CIM
977 WHERE CIM.CLE_ID = (SELECT A.ID
978 FROM OKC_K_LINES_V A,
979 OKC_LINE_STYLES_B B
980 WHERE CLE_ID = (SELECT A.ID
981 FROM OKC_K_LINES_V A,
982 OKC_LINE_STYLES_B B
983 WHERE CLE_ID = p_kle_id
984 AND A.LSE_ID = B.ID
985 AND A.dnz_chr_id = p_khr_id
986 AND B.LTY_CODE = 'FREE_FORM2')
987 AND A.LSE_ID = B.ID
988 AND A.dnz_chr_id = p_khr_id
989 AND B.LTY_CODE = 'INST_ITEM')
990 AND CIM.DNZ_CHR_ID = p_khr_id
991 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
992 AND CIM.OBJECT1_ID2 = '#'
993 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
994 AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
995 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
996 UNION
997 SELECT SUBSTR(ARP_ADDR_LABEL_PKG.FORMAT_ADDRESS(NULL,HL.ADDRESS1,HL.ADDRESS2,HL.ADDRESS3,
998 HL.ADDRESS4,HL.CITY,HL.COUNTY,HL.STATE,HL.PROVINCE,HL.POSTAL_CODE,NULL,HL.COUNTRY,
999 NULL, NULL,NULL,NULL,NULL,NULL,NULL,'N','N',80,1,1),1,80) DESCRIPTION
1000 FROM HZ_LOCATIONS HL,
1001 HZ_PARTY_SITES HPS,
1002 CSI_ITEM_INSTANCES CSI,
1003 OKC_K_ITEMS CIM
1004 WHERE CIM.CLE_ID = (SELECT A.ID
1005 FROM OKC_K_LINES_V A,
1006 OKC_LINE_STYLES_B B
1007 WHERE CLE_ID = (SELECT A.ID
1008 FROM OKC_K_LINES_V A,
1009 OKC_LINE_STYLES_B B
1010 WHERE CLE_ID = p_kle_id
1011 AND A.LSE_ID = B.ID
1012 AND A.dnz_chr_id = p_khr_id
1013 AND B.LTY_CODE = 'FREE_FORM2')
1014 AND A.LSE_ID = B.ID
1015 AND A.dnz_chr_id = p_khr_id
1016 AND B.LTY_CODE = 'INST_ITEM')
1017 AND CIM.DNZ_CHR_ID = p_khr_id
1018 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1019 AND CIM.OBJECT1_ID2 = '#'
1020 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1021 AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
1022 AND HPS.LOCATION_ID = HL.LOCATION_ID
1023 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES';
1024
1025 CURSOR l_get_nonbooked_Astloc_csr IS
1026 SELECT B.DESCRIPTION
1027 FROM OKX_PARTY_SITE_USES_V B
1028 WHERE B.ID1 = (SELECT A.OBJECT_ID1_NEW
1029 FROM OKL_TXL_ITM_INSTS_V A
1030 WHERE A.KLE_ID = (SELECT A.ID
1031 FROM OKC_K_LINES_V A,
1032 OKC_LINE_STYLES_B B
1033 WHERE CLE_ID = (SELECT A.ID
1034 FROM OKC_K_LINES_V A,
1035 OKC_LINE_STYLES_B B
1036 WHERE CLE_ID = p_kle_id
1037 AND A.LSE_ID = B.ID
1038 AND A.dnz_chr_id = p_khr_id
1039 AND B.LTY_CODE = 'FREE_FORM2')
1040 AND A.LSE_ID = B.ID
1041 AND A.dnz_chr_id = p_khr_id
1042 AND B.LTY_CODE = 'INST_ITEM'))
1043 AND B.ID2 = '#';
1044 l_khr_sts VARCHAR2(240);
1045 l_asset_loc VARCHAR2(240) := NULL;
1046
1047 BEGIN
1048 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1049 RETURN NULL;
1050 END IF;
1051
1052 OPEN l_khr_status_csr;
1053 FETCH l_khr_status_csr INTO l_khr_sts;
1054 CLOSE l_khr_status_csr;
1055
1056 IF l_khr_sts = 'BOOKED' THEN
1057 OPEN l_get_booked_Astloc_csr;
1058 FETCH l_get_booked_Astloc_csr INTO l_asset_loc;
1059 CLOSE l_get_booked_Astloc_csr;
1060 ELSE
1061 OPEN l_get_nonbooked_Astloc_csr;
1062 FETCH l_get_nonbooked_Astloc_csr INTO l_asset_loc;
1063 CLOSE l_get_nonbooked_Astloc_csr;
1064 END IF;
1065
1066 RETURN l_asset_loc;
1067
1068 EXCEPTION
1069 WHEN OTHERS THEN
1070 RETURN NULL;
1071 END get_asset_location;
1072 --added by asawanka
1073 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
1074 -- p_khr_id is contract id
1075 FUNCTION get_ast_install_loc_id(
1076 p_kle_id IN NUMBER,
1077 p_khr_id IN NUMBER)
1078 RETURN NUMBER IS
1079 CURSOR l_khr_status_csr IS
1080 SELECT STS_CODE
1081 FROM okc_k_headers_all_b
1082 WHERE ID = p_khr_id;
1083
1084 CURSOR l_get_booked_Astloc_csr IS
1085 SELECT psu.party_site_use_id
1086 FROM HZ_LOCATIONS HL,
1087 CSI_ITEM_INSTANCES CSI,
1088 OKC_K_ITEMS CIM,
1089 hz_party_site_uses psu,
1090 hz_party_sites hps
1091 WHERE CIM.CLE_ID in (SELECT A.ID
1092 FROM OKC_K_LINES_V A,
1093 OKC_LINE_STYLES_B B
1094 WHERE CLE_ID in (SELECT A.ID
1095 FROM OKC_K_LINES_V A,
1096 OKC_LINE_STYLES_B B
1097 WHERE CLE_ID = p_kle_id
1098 AND A.LSE_ID = B.ID
1099 AND A.dnz_chr_id = p_khr_id
1100 AND B.LTY_CODE = 'FREE_FORM2')
1101 AND A.LSE_ID = B.ID
1102 AND A.dnz_chr_id = p_khr_id
1103 AND B.LTY_CODE = 'INST_ITEM')
1104 AND CIM.DNZ_CHR_ID = p_khr_id
1105 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1106 AND CIM.OBJECT1_ID2 = '#'
1107 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1108 AND CSI.INSTALL_LOCATION_ID = HL.LOCATION_ID
1109 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_LOCATIONS'
1110 AND psu.site_use_type ='INSTALL_AT'
1111 AND psu.party_site_id = hps.party_site_id
1112 AND hps.location_id = hl.location_id
1113 UNION
1114 SELECT psu.party_site_use_id
1115 FROM HZ_LOCATIONS HL,
1116 HZ_PARTY_SITES HPS,
1117 HZ_PARTY_SITE_USES PSU,
1118 CSI_ITEM_INSTANCES CSI,
1119 OKC_K_ITEMS CIM
1120 WHERE CIM.CLE_ID in (SELECT A.ID
1121 FROM OKC_K_LINES_V A,
1122 OKC_LINE_STYLES_B B
1123 WHERE CLE_ID in (SELECT A.ID
1124 FROM OKC_K_LINES_V A,
1125 OKC_LINE_STYLES_B B
1126 WHERE CLE_ID = p_kle_id
1127 AND A.LSE_ID = B.ID
1128 AND A.dnz_chr_id = p_khr_id
1129 AND B.LTY_CODE = 'FREE_FORM2')
1130 AND A.LSE_ID = B.ID
1131 AND A.dnz_chr_id = p_khr_id
1132 AND B.LTY_CODE = 'INST_ITEM')
1133 AND CIM.DNZ_CHR_ID = p_khr_id
1134 AND CIM.OBJECT1_ID1 = CSI.INSTANCE_ID
1135 AND CIM.OBJECT1_ID2 = '#'
1136 AND CIM.JTOT_OBJECT1_CODE = 'OKX_IB_ITEM'
1137 AND CSI.INSTALL_LOCATION_ID = HPS.PARTY_SITE_ID
1138 AND HPS.LOCATION_ID = HL.LOCATION_ID
1139 AND CSI.INSTALL_LOCATION_TYPE_CODE = 'HZ_PARTY_SITES'
1140 AND psu.party_site_id = hps.party_site_id
1141 AND psu.site_use_type = 'INSTALL_AT';
1142
1143 CURSOR l_get_nonbooked_Astloc_csr IS
1144 SELECT A.OBJECT_ID1_NEW
1145 FROM OKL_TXL_ITM_INSTS_V A
1146 WHERE A.KLE_ID IN (SELECT A.ID
1147 FROM OKC_K_LINES_V A,
1148 OKC_LINE_STYLES_B B
1149 WHERE CLE_ID IN (SELECT A.ID
1150 FROM OKC_K_LINES_V A,
1151 OKC_LINE_STYLES_B B
1152 WHERE CLE_ID = p_kle_id
1153 AND A.LSE_ID = B.ID
1154 AND A.dnz_chr_id = p_khr_id
1155 AND B.LTY_CODE = 'FREE_FORM2')
1156 AND A.LSE_ID = B.ID
1157 AND A.dnz_chr_id = p_khr_id
1158 AND B.LTY_CODE = 'INST_ITEM');
1159 l_khr_sts VARCHAR2(240);
1160 l_asset_loc NUMBER := NULL;
1161
1162 BEGIN
1163 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1164 RETURN NULL;
1165 END IF;
1166
1167 OPEN l_khr_status_csr;
1168 FETCH l_khr_status_csr INTO l_khr_sts;
1169 CLOSE l_khr_status_csr;
1170
1171 IF l_khr_sts = 'BOOKED' THEN
1172 OPEN l_get_booked_Astloc_csr;
1173 FETCH l_get_booked_Astloc_csr INTO l_asset_loc;
1174 CLOSE l_get_booked_Astloc_csr;
1175 ELSE
1176 OPEN l_get_nonbooked_Astloc_csr;
1177 FETCH l_get_nonbooked_Astloc_csr INTO l_asset_loc;
1178 CLOSE l_get_nonbooked_Astloc_csr;
1179 END IF;
1180
1181 RETURN l_asset_loc;
1182
1183 EXCEPTION
1184 WHEN OTHERS THEN
1185 RETURN NULL;
1186 END get_ast_install_loc_id;
1187 --added by asawanka
1188 -- p_kle_id is top line id. select id from okc_k_lines_b where cle_id is null
1189 -- p_khr_id is contract id
1190 FUNCTION get_booked_asset_number(
1191 p_kle_id IN NUMBER,
1192 p_khr_id IN NUMBER)
1193 RETURN VARCHAR2 IS
1194 CURSOR l_khr_status_csr IS
1195 SELECT STS_CODE
1196 FROM okc_k_headers_all_b
1197 WHERE ID = p_khr_id;
1198
1199 CURSOR l_get_booked_astnum_csr IS
1200 SELECT FAV.ASSET_NUMBER ASSETNUMBER
1201 FROM OKC_K_LINES_V CLE_FIN
1202 , OKC_LINE_STYLES_B LSE_FIN
1203 , OKC_K_LINES_B CLE_FA
1204 , OKC_LINE_STYLES_B LSE_FA
1205 , OKC_K_ITEMS CIM_FA
1206 , FA_ADDITIONS_B FAV
1207 WHERE CLE_FIN.CLE_ID IS NULL
1208 AND CLE_FIN.id = p_kle_id
1209 AND CLE_FIN.DNZ_CHR_ID = p_khr_id
1210 AND LSE_FIN.ID = CLE_FIN.LSE_ID
1211 AND LSE_FIN.LTY_CODE = 'FREE_FORM1'
1212 AND CLE_FA.CLE_ID = CLE_FIN.ID
1213 AND CLE_FA.LSE_ID = LSE_FA.ID
1214 AND LSE_FA.LTY_CODE = 'FIXED_ASSET'
1215 AND CIM_FA.CLE_ID = CLE_FA.ID
1216 AND CIM_FA.OBJECT1_ID1 = FAV.ASSET_ID
1217 AND CIM_FA.OBJECT1_ID2 = '#' ;
1218
1219 l_asset_num VARCHAR2(240);
1220 l_khr_sts VARCHAR2(30);
1221 BEGIN
1222 IF p_khr_id IS NULL OR p_kle_id is NULL THEN
1223 RETURN NULL;
1224 END IF;
1225
1226 OPEN l_khr_status_csr;
1227 FETCH l_khr_status_csr INTO l_khr_sts;
1228 CLOSE l_khr_status_csr;
1229
1230 IF l_khr_sts = 'BOOKED' THEN
1231 OPEN l_get_booked_Astnum_csr;
1232 FETCH l_get_booked_Astnum_csr INTO l_asset_num;
1233 CLOSE l_get_booked_Astnum_csr;
1234 ELSE
1235 l_asset_num := NULL;
1236 END IF;
1237
1238 RETURN l_asset_num;
1239
1240 EXCEPTION
1241 WHEN OTHERS THEN
1242 RETURN NULL;
1243 END get_booked_asset_number;
1244
1245 -- Added procedure as part of Bug#6651871 to create Pay Site for Supplier start
1246
1247 PROCEDURE create_pay_site(
1248 party_id IN NUMBER,
1249 party_site_id IN NUMBER := NULL, -- added to create pay site
1250 p_org_id IN NUMBER, -- added to create pay site
1251 p_api_version IN NUMBER,
1252 p_init_msg_list IN VARCHAR2,
1253 x_return_status OUT NOCOPY VARCHAR2,
1254 x_msg_count OUT NOCOPY NUMBER,
1255 x_msg_data OUT NOCOPY VARCHAR2
1256 )
1257 IS
1258 l_proc_name VARCHAR2(35) := 'create_pay_site';
1259 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1260 l_vendor_site_rec_type AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
1261 l_vendor_site_rec_upd AP_VENDOR_PUB_PKG.r_vendor_site_rec_type;
1262 l_vendor_site_id NUMBER;
1263 l_party_site_id NUMBER;
1264 l_location_id NUMBER;
1265 l_vendor_id NUMBER;
1266 l_party_site_number VARCHAR2(30);
1267 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1268 l_msg_count NUMBER;
1269 l_msg_data VARCHAR2(200);
1270 l_address_line1 VARCHAR2(240);
1271 l_city VARCHAR2(60);
1272 l_state VARCHAR2(60);
1273 l_zip VARCHAR2(60);
1274 l_country VARCHAR2(60);
1275 l_county VARCHAR2(60);
1276 l_address_style VARCHAR2(30);
1277 l_province VARCHAR2(60);
1278
1279 CURSOR get_party_site_info(p_party_id NUMBER)
1280 IS
1281 select hzps.party_site_id
1282 ,hzps.location_id
1283 ,aps.vendor_id
1284 ,hzps.party_site_number
1285 ,hzl.address1
1286 ,hzl.city
1287 ,hzl.state
1288 ,hzl.postal_code
1289 ,hzl.country
1290 ,hzl.county
1291 ,hzl.address_style
1292 ,hzl.province
1293 from
1294 hz_party_sites hzps
1295 ,hz_parties hz
1296 ,ap_suppliers aps
1297 ,hz_locations hzl
1298 where
1299 hzps.party_id = hz.party_id
1300 and hzps.IDENTIFYING_ADDRESS_FLAG = 'Y'
1301 and hz.party_id = aps.party_id
1302 and hz.party_id = p_party_id
1303 and hzps.location_id = hzl.location_id;
1304
1305
1306 CURSOR get_location_id(p_party_site_id NUMBER)
1307 IS
1308 select
1309 hzps.location_id
1310 ,hzps.party_site_number
1311 ,aps.vendor_id
1312 ,hzl.address1
1313 ,hzl.city
1314 ,hzl.state
1315 ,hzl.postal_code
1316 ,hzl.country
1317 ,hzl.county
1318 ,hzl.address_style
1319 ,hzl.province
1320 from
1321 hz_party_sites hzps
1322 ,ap_suppliers aps
1323 ,hz_locations hzl
1324 where
1325 hzps.party_id = aps.party_id
1326 and party_site_id = p_party_site_id
1327 and hzps.location_id = hzl.location_id;
1328
1329 BEGIN
1330 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1331 -- call START_ACTIVITY to create savepoint, check compatibility
1332 -- and initialize message list
1333 x_return_status := OKL_API.START_ACTIVITY(
1334 p_api_name => l_api_name,
1335 p_pkg_name => G_PKG_NAME,
1336 p_init_msg_list => p_init_msg_list,
1337 l_api_version => l_api_version,
1338 p_api_version => p_api_version,
1339 p_api_type => G_API_TYPE,
1340 x_return_status => l_return_status);
1341
1342 -- check if activity started successfully
1343 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1344 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1346 RAISE OKL_API.G_EXCEPTION_ERROR;
1347 END IF;
1348
1349 IF(party_site_id IS NOT NULL AND party_site_id <> OKL_API.G_MISS_NUM) THEN
1350 l_party_site_id := party_site_id;
1351 OPEN get_location_id(l_party_site_id);
1352 FETCH get_location_id
1353 INTO l_location_id, l_party_site_number, l_vendor_id, l_address_line1,
1354 l_city, l_state, l_zip, l_country, l_county, l_address_style, l_province;
1355 CLOSE get_location_id;
1356 ELSE
1357 OPEN get_party_site_info(party_id);
1358 FETCH get_party_site_info
1359 INTO l_party_site_id, l_location_id, l_vendor_id, l_party_site_number,l_address_line1,
1360 l_city, l_state, l_zip, l_country, l_county, l_address_style, l_province;
1361 CLOSE get_party_site_info;
1362 END IF;
1363
1364 l_vendor_site_rec_type.org_id := p_org_id;
1365 l_vendor_site_rec_type.party_site_id := l_party_site_id;
1366 l_vendor_site_rec_type.location_id := l_location_id;
1367 l_vendor_site_rec_type.vendor_id := l_vendor_id;
1368 l_vendor_site_rec_type.VENDOR_SITE_CODE := substr(l_party_site_number, 0, 14);
1369 l_vendor_site_rec_type.PURCHASING_SITE_FLAG := 'N';
1370 l_vendor_site_rec_type.PRIMARY_PAY_SITE_FLAG := 'N';
1371 l_vendor_site_rec_type.PAY_SITE_FLAG := 'Y';
1372
1373
1374 POS_VENDOR_PUB_PKG.Create_Vendor_Site
1375 (
1376 p_vendor_site_rec => l_vendor_site_rec_type,
1377 x_return_status => l_return_status,
1378 x_msg_count => l_msg_count,
1379 x_msg_data => l_msg_data,
1380 x_vendor_site_id => l_vendor_site_id,
1381 x_party_site_id => l_party_site_id,
1382 x_location_id => l_location_id
1383 );
1384 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1385 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1386 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1387 RAISE OKL_API.G_EXCEPTION_ERROR;
1388 END IF;
1389
1390 l_vendor_site_rec_upd.vendor_site_id := l_vendor_site_id;
1391 l_vendor_site_rec_upd.party_site_id := l_party_site_id;
1392 l_vendor_site_rec_upd.vendor_id := l_vendor_id;
1393 l_vendor_site_rec_upd.ADDRESS_LINE1 := l_address_line1;
1394 l_vendor_site_rec_upd.CITY := l_city;
1395 l_vendor_site_rec_upd.STATE := l_state;
1396 l_vendor_site_rec_upd.ZIP := l_zip;
1397 l_vendor_site_rec_upd.COUNTRY := l_country;
1398 l_vendor_site_rec_upd.COUNTY := l_county;
1399 l_vendor_site_rec_upd.ADDRESS_STYLE := l_address_style;
1400 l_vendor_site_rec_upd.PROVINCE := l_province;
1401
1402 POS_VENDOR_PUB_PKG.Update_Vendor_Site
1403 (
1404 p_vendor_site_rec => l_vendor_site_rec_upd,
1405 x_return_status => l_return_status,
1406 x_msg_count => l_msg_count,
1407 x_msg_data => l_msg_data
1408 );
1409
1410 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1411 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1413 RAISE OKL_API.G_EXCEPTION_ERROR;
1414 END IF;
1415
1416
1417 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1418 x_msg_data => x_msg_data);
1419 EXCEPTION
1420 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1421 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1422 p_api_name => l_api_name,
1423 p_pkg_name => G_PKG_NAME,
1424 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1425 x_msg_count => x_msg_count,
1426 x_msg_data => x_msg_data,
1427 p_api_type => G_API_TYPE);
1428
1429 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1430 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1431 p_api_name => l_api_name,
1432 p_pkg_name => G_PKG_NAME,
1433 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1434 x_msg_count => x_msg_count,
1435 x_msg_data => x_msg_data,
1436 p_api_type => G_API_TYPE);
1437
1438 WHEN OTHERS THEN
1439 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1440 p_api_name => l_api_name,
1441 p_pkg_name => G_PKG_NAME,
1442 p_exc_name => 'OTHERS',
1443 x_msg_count => x_msg_count,
1444 x_msg_data => x_msg_data,
1445 p_api_type => G_API_TYPE);
1446 END create_pay_site;
1447
1448 -- Added procedure as part of Bug#6651871 to create Pay Site for Supplier end
1449
1450 -- Added procedure as part of Bug#6636587 to Create Vendor for a Party in TCA start
1451
1452 PROCEDURE create_related_vendor(
1453 party_id IN NUMBER,
1454 party_site_id IN NUMBER := NULL, -- added to create pay site
1455 p_org_id IN NUMBER , -- added to create pay site
1456 p_api_version IN NUMBER,
1457 p_init_msg_list IN VARCHAR2,
1458 x_return_status OUT NOCOPY VARCHAR2,
1459 x_msg_count OUT NOCOPY NUMBER,
1460 x_msg_data OUT NOCOPY VARCHAR2
1461 )
1462 IS
1463 l_proc_name VARCHAR2(35) := 'create_related_vendor';
1464 l_api_version CONSTANT VARCHAR2(30) := p_api_version;
1465 l_vendor_rec APPS.AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE;
1466 l_vendor_id NUMBER;
1467 l_party_id NUMBER;
1468 l_party_site_id NUMBER;
1469 l_org_id NUMBER;
1470 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1471 l_init_msg_list VARCHAR2(1) DEFAULT OKL_API.G_FALSE;
1472 l_msg_count NUMBER;
1473 l_msg_data VARCHAR2(200);
1474
1475
1476 BEGIN
1477 x_return_status := OKL_API.G_RET_STS_SUCCESS;
1478 -- call START_ACTIVITY to create savepoint, check compatibility
1479 -- and initialize message list
1480 x_return_status := OKL_API.START_ACTIVITY(
1481 p_api_name => l_api_name,
1482 p_pkg_name => G_PKG_NAME,
1483 p_init_msg_list => p_init_msg_list,
1484 l_api_version => l_api_version,
1485 p_api_version => p_api_version,
1486 p_api_type => G_API_TYPE,
1487 x_return_status => x_return_status);
1488
1489 -- check if activity started successfully
1490 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1491 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1492 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1493 RAISE OKL_API.G_EXCEPTION_ERROR;
1494 END IF;
1495
1496 l_party_site_id := party_site_id;
1497 l_org_id := p_org_id;
1498 l_vendor_rec.PARTY_ID := party_id;
1499
1500 pos_vendor_pub_pkg.create_vendor(
1501 p_vendor_rec => l_vendor_rec,
1502 x_return_status => l_return_status,
1503 x_msg_count => l_msg_count,
1504 x_msg_data => l_msg_data ,
1505 x_vendor_id => l_vendor_id,
1506 x_party_id => l_party_id);
1507
1508 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1509 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1510 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1511 RAISE OKL_API.G_EXCEPTION_ERROR;
1512 END IF;
1513
1514 create_pay_site(
1515 party_id => l_party_id,
1516 party_site_id => l_party_site_id,
1517 p_org_id => l_org_id,
1518 p_api_version => l_api_version,
1519 p_init_msg_list => l_init_msg_list,
1520 x_return_status => l_return_status,
1521 x_msg_count => l_msg_count,
1522 x_msg_data => l_msg_data
1523 );
1524
1525 IF(l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1527 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1528 RAISE OKL_API.G_EXCEPTION_ERROR;
1529 END IF;
1530
1531 OKL_API.END_ACTIVITY(x_msg_count => x_msg_count,
1532 x_msg_data => x_msg_data);
1533
1534
1535
1536 EXCEPTION
1537 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1538 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1539 p_api_name => l_api_name,
1540 p_pkg_name => G_PKG_NAME,
1541 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
1542 x_msg_count => x_msg_count,
1543 x_msg_data => x_msg_data,
1544 p_api_type => G_API_TYPE);
1545
1546 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1547 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1548 p_api_name => l_api_name,
1549 p_pkg_name => G_PKG_NAME,
1550 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
1551 x_msg_count => x_msg_count,
1552 x_msg_data => x_msg_data,
1553 p_api_type => G_API_TYPE);
1554
1555 WHEN OTHERS THEN
1556 x_return_status := OKL_API.HANDLE_EXCEPTIONS(
1557 p_api_name => l_api_name,
1558 p_pkg_name => G_PKG_NAME,
1559 p_exc_name => 'OTHERS',
1560 x_msg_count => x_msg_count,
1561 x_msg_data => x_msg_data,
1562 p_api_type => G_API_TYPE);
1563
1564 END create_related_vendor;
1565 -- Added procedure as part of Bug#6636587 to Create Vendor for a Party in TCA start
1566
1567 -- Added to create Pay Site for Supplier end
1568 END Okl_Lla_Util_Pvt;