[Home] [Help]
PACKAGE BODY: APPS.OKL_AM_UTIL_PVT
Source
1 PACKAGE BODY OKL_AM_UTIL_PVT AS
2 /* $Header: OKLRAMUB.pls 120.39.12010000.2 2008/09/09 21:36:35 rkuttiya ship $ */
3
4
5 -- Global Private Variables and Types
6
7 TYPE lenchk_rec_type IS RECORD (
8 VName VARCHAR2(30),
9 CName VARCHAR2(30),
10 CDType VARCHAR2(20),
11 CLength number,
12 CScale number);
13
14 TYPE lenchk_tbl_type IS TABLE OF lenchk_rec_type
15 INDEX by BINARY_INTEGER;
16
17 G_lenchk_tbl lenchk_tbl_type;
18 g_ptm_code VARCHAR2(50);
19
20 -- Start of comments
21 --
22 -- Procedure Name : get_rule_chr_id
23 -- Description : Depending on Quote Type, returns contract_id
24 -- of either Lease contract or its Program
25 -- Business Rules :
26 -- Parameters : quote header record
27 -- Version : 1.0
28 -- End of comments
29
30 FUNCTION get_rule_chr_id (
31 p_qtev_rec IN qtev_rec_type)
32 RETURN NUMBER IS
33
34 CURSOR l_program_csr (cp_chr_id NUMBER) IS
35 SELECT h.khr_id
36 FROM okl_k_headers h
37 WHERE h.id = cp_chr_id;
38
39 l_formula_chr_id NUMBER;
40
41 BEGIN
42
43 IF p_qtev_rec.qtp_code LIKE 'TER_RECOURSE%' THEN
44 OPEN l_program_csr (p_qtev_rec.khr_id);
45 FETCH l_program_csr INTO l_formula_chr_id;
46 CLOSE l_program_csr;
47 ELSE
48 l_formula_chr_id := p_qtev_rec.khr_id;
49 END IF;
50
51 RETURN l_formula_chr_id;
52
53 EXCEPTION
54
55 WHEN OTHERS THEN
56 IF l_program_csr%ISOPEN THEN
57 CLOSE l_program_csr;
58 END IF;
59 RETURN NULL;
60
61 END get_rule_chr_id;
62
63
64 -- Start of comments
65 --
66 -- Procedure Name : initialize_txn_rec
67 -- Description : Initialize transaction record for IB calls
68 -- Business Rules :
69 -- Parameters : transaction record
70 -- Version : 1.0
71 -- End of comments
72
73 -- Fulfillment Specific Subtypes
74
75 PROCEDURE initialize_txn_rec (
76 px_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec) IS
77 BEGIN
78
79 px_txn_rec.transaction_id := NULL;
80 px_txn_rec.transaction_date := SYSDATE;
81 px_txn_rec.source_transaction_date := SYSDATE;
82 px_txn_rec.transaction_type_id := 1;
83 px_txn_rec.txn_sub_type_id := NULL;
84 px_txn_rec.source_group_ref_id := NULL;
85 px_txn_rec.source_group_ref := '';
86 px_txn_rec.source_header_ref_id := NULL;
87 px_txn_rec.source_header_ref := '';
88 px_txn_rec.source_line_ref_id := NULL;
89 px_txn_rec.source_line_ref := '';
90 px_txn_rec.source_dist_ref_id1 := NULL;
91 px_txn_rec.source_dist_ref_id2 := NULL;
92 px_txn_rec.inv_material_transaction_id := NULL;
93 px_txn_rec.transaction_quantity := NULL;
94 px_txn_rec.transaction_uom_code := '';
95 px_txn_rec.transacted_by := NULL;
96 px_txn_rec.transaction_status_code := '';
97 px_txn_rec.transaction_action_code := '';
98 px_txn_rec.message_id := NULL;
99 px_txn_rec.context := '';
100 px_txn_rec.attribute1 := '';
101 px_txn_rec.attribute2 := '';
102 px_txn_rec.attribute3 := '';
103 px_txn_rec.attribute4 := '';
104 px_txn_rec.attribute5 := '';
105 px_txn_rec.attribute6 := '';
106 px_txn_rec.attribute7 := '';
107 px_txn_rec.attribute8 := '';
108 px_txn_rec.attribute9 := '';
109 px_txn_rec.attribute10 := '';
110 px_txn_rec.attribute11 := '';
111 px_txn_rec.attribute12 := '';
112 px_txn_rec.attribute13 := '';
113 px_txn_rec.attribute14 := '';
114 px_txn_rec.attribute15 := '';
115 px_txn_rec.object_version_number := NULL;
116 px_txn_rec.split_reason_code := '';
117
118 END initialize_txn_rec;
119
120
121 -- Start of comments
122 --
123 -- Procedure Name : get_okl_org_id
124 -- Description : Return system org_id
125 -- Business Rules :
126 -- Parameters : none
127 -- Version : 1.0
128 -- End of comments
129
130 FUNCTION get_okl_org_id RETURN NUMBER IS
131 BEGIN
132 RETURN (okc_context.get_okc_org_id);
133 -- the same as: RETURN (sys_context('OKC_CONTEXT','ORG_ID'));
134 END get_okl_org_id;
135
136
137 -- Start of comments
138 --
139 -- Procedure Name : get_chr_org_id
140 -- Description : Return contract org_id
141 -- Business Rules :
142 -- Parameters : contract id
143 -- Version : 1.0
144 -- End of comments
145
146 FUNCTION get_chr_org_id (p_chr_id IN NUMBER) RETURN NUMBER IS
147
148 -- Get contract org_id
149 CURSOR l_chr_csr (cp_chr_id NUMBER) IS
150 SELECT h.authoring_org_id
151 FROM okc_k_headers_b h
152 WHERE h.id = cp_chr_id;
153
154 l_result okc_k_headers_b.authoring_org_id%TYPE;
155
156 BEGIN
157
158 OPEN l_chr_csr (p_chr_id);
159 FETCH l_chr_csr INTO l_result;
160 CLOSE l_chr_csr;
161
162 RETURN l_result;
163
164 EXCEPTION
165
166 WHEN OTHERS THEN
167 IF (l_chr_csr%ISOPEN) THEN
168 CLOSE l_chr_csr;
169 END IF;
170 RETURN NULL;
171
172 END get_chr_org_id;
173
174
175 -- Start of comments
176 --
177 -- Procedure Name : get_chr_currency
178 -- Description : Return contract currency_code
179 -- Business Rules :
180 -- Parameters : contract id
181 -- Version : 1.0
182 -- End of comments
183
184 FUNCTION get_chr_currency (p_chr_id IN NUMBER) RETURN VARCHAR2 IS
185
186 -- Get contract org_id
187 CURSOR l_chr_csr (cp_chr_id NUMBER) IS
188 SELECT h.currency_code
189 FROM okc_k_headers_b h
190 WHERE h.id = cp_chr_id;
191
192 l_result okc_k_headers_b.currency_code%TYPE;
193
194 BEGIN
195
196 OPEN l_chr_csr (p_chr_id);
197 FETCH l_chr_csr INTO l_result;
198 CLOSE l_chr_csr;
199
200 RETURN l_result;
201
202 EXCEPTION
203
204 WHEN OTHERS THEN
205 IF (l_chr_csr%ISOPEN) THEN
206 CLOSE l_chr_csr;
207 END IF;
208 RETURN NULL;
209
210 END get_chr_currency;
211
212
213 -- Start of comments
214 --
215 -- Procedure Name : get_asset_quantity
216 -- Description : Return asset quantity
217 -- Business Rules :
218 -- Parameters : contract line id
219 -- Version : 1.0
220 -- End of comments
221
222 FUNCTION get_asset_quantity (p_cle_id IN NUMBER) RETURN NUMBER IS
223
224 -- Get asset quantity
225 CURSOR l_quantity_csr (cp_cle_id NUMBER) IS
226 SELECT cim.number_of_items asset_quantity
227 FROM okc_k_lines_b cle,
228 okc_line_styles_b lse,
229 okc_k_items cim
230 WHERE cle.cle_id = cp_cle_id
231 AND lse.id = cle.lse_id
232 AND lse.lty_code = 'ITEM'
233 AND cim.cle_id = cle.id;
234
235 l_result okc_k_items.number_of_items%TYPE := NULL;
236
237 BEGIN
238
239 OPEN l_quantity_csr (p_cle_id);
240 FETCH l_quantity_csr INTO l_result;
241 CLOSE l_quantity_csr;
242
243 RETURN l_result;
244
245 EXCEPTION
246
247 WHEN OTHERS THEN
248 IF (l_quantity_csr%ISOPEN) THEN
249 CLOSE l_quantity_csr;
250 END IF;
251 RETURN NULL;
252
253 END get_asset_quantity;
254
255
256 -- Start of comments
257 --
258 -- Procedure Name : get_currency_info
259 -- Description : Gets information about currency
260 -- Business Rules :
261 -- Parameters : currency code
262 -- Version : 1.0
263 -- End of comments
264
265 PROCEDURE get_currency_info (
266 p_currency_code IN VARCHAR2,
267 x_precision OUT NOCOPY NUMBER,
268 x_min_acc_unit OUT NOCOPY NUMBER) IS
269
270 -- Get currency attributes
271 CURSOR l_curr_csr (cp_currency_code VARCHAR2) IS
272 SELECT c.minimum_accountable_unit, c.precision
273 FROM fnd_currencies c
274 WHERE c.currency_code = cp_currency_code;
275
276 BEGIN
277
278 OPEN l_curr_csr (p_currency_code);
279 FETCH l_curr_csr INTO x_min_acc_unit, x_precision;
280 CLOSE l_curr_csr;
281
282 EXCEPTION
283
284 WHEN OTHERS THEN
285 IF (l_curr_csr%ISOPEN) THEN
286 CLOSE l_curr_csr;
287 END IF;
288 x_precision := NULL;
289 x_min_acc_unit := NULL;
290
291 END get_currency_info;
292
293
294 -- Start of comments
295 --
296 -- Procedure Name : get_ak_attribute
297 -- Description : Returns attribute label
298 -- Business Rules :
299 -- Parameters : attribute code
300 -- Version : 1.0
301 -- End of comments
302
303 FUNCTION get_ak_attribute (
304 p_code IN VARCHAR2)
305 RETURN VARCHAR2 IS
306
307 l_attr_label ak_attributes_vl.attribute_label_long%TYPE := NULL;
308
309 CURSOR l_attribute_csr (cp_code VARCHAR2) IS
310 SELECT attribute_label_long
311 FROM ak_attributes_vl attr
312 WHERE attr.attribute_application_id = 540
313 AND attr.attribute_code = cp_code;
314
315 BEGIN
316
317 OPEN l_attribute_csr (p_code);
318 FETCH l_attribute_csr INTO l_attr_label;
319 CLOSE l_attribute_csr;
320
321 RETURN (l_attr_label);
322
323 EXCEPTION
324
325 WHEN OTHERS THEN
326
327 IF l_attribute_csr%ISOPEN THEN
328 CLOSE l_attribute_csr;
329 END IF;
330
331 RETURN (NULL);
332
333 END get_ak_attribute;
334
335
336 -- Start of comments
337 --
338 -- Procedure Name : get_trx_msgs_yn
339 -- Description : Indicates if any messages exist
340 -- Business Rules :
341 -- Parameters : Source table name, source id
342 -- Version : 1.0
343 -- End of comments
344
345 FUNCTION get_trx_msgs_yn (
346 p_trx_table IN VARCHAR2,
347 p_trx_id IN NUMBER)
348 RETURN VARCHAR2 IS
349
350 l_msg_count NUMBER := 0;
351 l_result VARCHAR2(1);
352
353 CURSOR l_messages_csr (cp_trx_table VARCHAR2, cp_trx_id NUMBER) IS
354 SELECT count (*)
355 FROM okl_trx_msgs m
356 WHERE m.trx_source_table = cp_trx_table
357 AND m.trx_id = cp_trx_id;
358
359 BEGIN
360
361 OPEN l_messages_csr (p_trx_table, p_trx_id);
362 FETCH l_messages_csr INTO l_msg_count;
363 CLOSE l_messages_csr;
364
365 IF l_msg_count = 0 THEN
366 l_result := 'N';
367 ELSE
368 l_result := 'Y';
369 END IF;
370
371 RETURN (l_result);
372
373 EXCEPTION
374
375 WHEN OTHERS THEN
376
377 IF l_messages_csr%ISOPEN THEN
378 CLOSE l_messages_csr;
379 END IF;
380
381 RETURN (NULL);
382
383 END get_trx_msgs_yn;
384
385
386 -- Start of comments
387 --
388 -- Procedure Name : get_quote_amount
389 -- Description : Return quote amount : Modified to get tax amount from tax entity, and original quote amount excluding tax lines
390 -- Business Rules :
391 -- Parameters : Quote id
392 -- Version : 1.0
393 -- End of comments
394
395 FUNCTION get_quote_amount (
396 p_quote_id IN NUMBER)
397 RETURN NUMBER IS
398
399 l_result NUMBER := 0;
400
401 CURSOR l_q_lines_csr (cp_quote_id NUMBER) IS
402 SELECT sum (nvl (l.amount,0))
403 FROM okl_txl_qte_lines_all_b l
404 WHERE l.qte_id = cp_quote_id
405 AND l.qlt_code <> 'AMCTAX'; -- rmunjulu sales_tax_enhancement exclude tax line as tax is coming from tax entities
406
407 -- rmunjulu sales_tax_enhancement
408 l_tax_amount NUMBER;
409
410 BEGIN
411
412 OPEN l_q_lines_csr (p_quote_id);
413 FETCH l_q_lines_csr INTO l_result;
414 CLOSE l_q_lines_csr;
415
416 -- rmunjulu sales_tax_enhancement Get the tax amount
417 l_tax_amount := get_tax_amount (p_quote_id);
418
419 -- rmunjulu sales_tax_enhancement
420 l_result := nvl(l_result,0) + nvl(l_tax_amount,0);
421
422 RETURN (NVL (l_result, 0));
423
424 EXCEPTION
425
426 WHEN OTHERS THEN
427
428 IF l_q_lines_csr%ISOPEN THEN
429 CLOSE l_q_lines_csr;
430 END IF;
431
432 RETURN (0);
433
434 END get_quote_amount;
435
436
437 -- Start of comments
438 --
439 -- Procedure Name : get_lookup_meaning
440 -- Description : Returns lookup meaning
441 -- Business Rules :
442 -- Parameters : lookup type, lookup code, validate flag
443 -- Version : 1.0
444 -- End of comments
445
446 FUNCTION get_lookup_meaning (
447 p_lookup_type IN VARCHAR2,
448 p_lookup_code IN VARCHAR2,
449 p_validate_yn IN VARCHAR2)
450 RETURN VARCHAR2 IS
451
452 l_lookup_rec fnd_lookups%ROWTYPE;
453 l_meaning fnd_lookups.meaning%TYPE := NULL;
454 l_sysdate DATE := SYSDATE ;
455
456 CURSOR l_lookup_csr
457 (cp_lookup_type VARCHAR2,
458 cp_lookup_code VARCHAR2) IS
459 SELECT *
460 FROM fnd_lookups fndlup
461 WHERE fndlup.lookup_type = cp_lookup_type
462 AND fndlup.lookup_code = cp_lookup_code;
463
464 BEGIN
465
466 OPEN l_lookup_csr (p_lookup_type, p_lookup_code);
467 FETCH l_lookup_csr INTO l_lookup_rec;
468 CLOSE l_lookup_csr;
469
470 l_meaning := l_lookup_rec.meaning;
471
472 IF p_validate_yn = 'Y' AND l_meaning IS NOT NULL THEN
473 IF l_lookup_rec.enabled_flag = 'N'
474 OR l_sysdate < NVL (l_lookup_rec.start_date_active, l_sysdate)
475 OR l_sysdate > NVL (l_lookup_rec.end_date_active, l_sysdate)
476 THEN
477 l_meaning := NULL;
478 END IF;
479 END IF;
480
481 RETURN (l_meaning);
482
483 EXCEPTION
484
485 WHEN OTHERS THEN
486
487 IF l_lookup_csr%ISOPEN THEN
488 CLOSE l_lookup_csr;
489 END IF;
490
491 RETURN (NULL);
492
493 END get_lookup_meaning;
494
495
496 -- Start of comments
497 --
498 -- Procedure Name : set_token
499 -- Description : Return full description for message tokens
500 -- Business Rules :
501 -- Parameters : token type, token value
502 -- Version : 1.0
503 -- History : 07-FEB-03 DAPATEL 115.47 2780466 - Modified message token
504 -- value when a message for no operand value is set by create
505 -- quote.
506 -- End of comments
507
508 FUNCTION set_token (
509 p_token1_type IN VARCHAR2,
510 p_token1_value IN VARCHAR2,
511 p_token2_type IN VARCHAR2,
512 p_token2_value IN VARCHAR2,
513 p_token2_new_value IN VARCHAR2)
514 RETURN VARCHAR2 IS
515
516 -- Get operand description
517 CURSOR l_operand_csr
518 (cp_formula_name VARCHAR2
519 ,cp_operand_name VARCHAR2) IS
520 SELECT o.description
521 FROM okl_formulae_v f,
522 okl_fmla_oprnds_v fo,
523 okl_operands_v o
524 WHERE f.name = cp_formula_name
525 AND f.start_date <= SYSDATE
526 AND NVL (f.end_date, SYSDATE) >= SYSDATE
527 AND fo.fma_id = f.id
528 AND fo.label = cp_operand_name
529 AND o.id = fo.opd_id;
530
531 -- Get rule description
532 CURSOR l_rule_csr (cp_rule_code VARCHAR2) IS
533 SELECT rd.meaning
534 FROM okc_rule_defs_v rd
535 WHERE rd.application_id = 540
536 AND rd.rule_code = cp_rule_code;
537
538 l_lookup_type fnd_lookups.lookup_type%TYPE := NULL;
539 l_token_value fnd_lookups.description%TYPE := NULL;
540
541 BEGIN
542
543 IF p_token1_type IS NOT NULL
544 AND p_token1_value IS NOT NULL
545 AND p_token2_type IS NULL
546 AND p_token2_value IS NULL THEN
547
548 IF p_token1_type = 'RULE' THEN
549 OPEN l_rule_csr (p_token1_value);
550 FETCH l_rule_csr INTO l_token_value;
551 CLOSE l_rule_csr;
552 ELSIF p_token1_type = 'GROUP' THEN
553 l_lookup_type := 'OKC_RULE_GROUP_DEF';
554 ELSIF p_token1_type = 'QLT_CODE' THEN
555 l_lookup_type := 'OKL_QUOTE_LINE_TYPE';
556 ELSIF p_token1_type = 'QTP_CODE' THEN
557 l_lookup_type := 'OKL_QUOTE_TYPE';
558 ELSIF p_token1_type = 'QUOTE_PARTY_TYPE' THEN
559 l_lookup_type := 'OKL_QUOTE_PARTY_TYPE';
560 ELSIF p_token1_type = 'FORMULA' THEN
561 l_token_value := p_token1_value;
562 END IF;
563
564 IF l_lookup_type IS NOT NULL THEN
565 l_token_value := get_lookup_meaning
566 (l_lookup_type, p_token1_value, 'N');
567 END IF;
568
569 --2780466 - Commented out
570 -- IF l_token_value IS NOT NULL THEN
571 -- l_token_value := '"' || l_token_value || '"';
572 -- END IF;
573
574 l_token_value := NVL (l_token_value, p_token1_value);
575
576 END IF;
577
578 IF p_token1_type IS NOT NULL
579 AND p_token1_value IS NOT NULL
580 AND p_token2_type IS NOT NULL
581 AND p_token2_value IS NOT NULL THEN
582
583 IF p_token1_type = 'FORMULA'
584 AND p_token2_type = 'OPERAND' THEN
585
586 -- OPEN l_operand_csr (p_token1_value, p_token2_value);
587 -- FETCH l_operand_csr INTO l_token_value;
588 -- CLOSE l_operand_csr;
589
590 --2780466 - Modified message token to display rule value for operand
591 OPEN l_rule_csr (p_token2_value);
592 FETCH l_rule_csr INTO l_token_value;
593 CLOSE l_rule_csr;
594
595 --2780466 - Commented out
596 --l_token_value := p_token2_new_value;
597
598 --2780466 - Commented out
599 -- IF l_token_value IS NOT NULL THEN
600 -- l_token_value := '"' || l_token_value || '"';
601 -- END IF;
602
603 END IF;
604
605 l_token_value := NVL (l_token_value, p_token2_new_value);
606
607 END IF;
608 /*
609 IF l_token_value IS NOT NULL
610 AND l_token_value NOT LIKE '"%"' THEN
611 l_token_value := '"' || l_token_value || '"';
612 END IF;
613 */
614 RETURN l_token_value;
615
616 EXCEPTION
617
618 WHEN OTHERS THEN
619
620 -- store SQL error message on message stack for caller
621 OKL_API.SET_MESSAGE (
622 p_app_name => G_APP_NAME
623 ,p_msg_name => G_UNEXPECTED_ERROR
624 ,p_token1 => G_SQLCODE_TOKEN
625 ,p_token1_value => sqlcode
626 ,p_token2 => G_SQLERRM_TOKEN
627 ,p_token2_value => sqlerrm);
628
629 IF (l_operand_csr%ISOPEN) THEN
630 CLOSE l_operand_csr;
631 END IF;
632
633 IF (l_rule_csr%ISOPEN) THEN
634 CLOSE l_rule_csr;
635 END IF;
636
637 RETURN NVL (p_token2_new_value, p_token1_value);
638
639 END set_token;
640
641
642 -- Start of comments
643 --
644 -- Procedure Name : get_transaction_id
645 -- Description : Gets transaction type id for transaction name
646 -- Business Rules :
647 -- Parameters : transaction name
648 -- Version : 1.0
649 -- End of comments
650
651 PROCEDURE get_transaction_id (
652 p_try_name IN VARCHAR2,
653 p_language IN VARCHAR2 DEFAULT 'US',
654 x_return_status OUT NOCOPY VARCHAR2,
655 x_try_id OUT NOCOPY NUMBER) IS
656
657 -- Cursor to get the try_id for the name passed
658 CURSOR l_try_id_csr (
659 cp_try_name IN VARCHAR2,
660 cp_language IN VARCHAR2) IS
661 SELECT id
662 FROM okl_trx_types_tl t
663 WHERE Upper (t.name) LIKE Upper (cp_try_name)
664 AND t.language = Upper (cp_language);
665
666 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
667 l_try_id NUMBER := NULL;
668
669 BEGIN
670
671 -- Get the try_id for the name passed
672 OPEN l_try_id_csr (p_try_name, p_language);
673 FETCH l_try_id_csr INTO l_try_id;
674 IF l_try_id_csr%NOTFOUND THEN
675 l_return_status := OKL_API.G_RET_STS_ERROR;
676 END IF;
677 CLOSE l_try_id_csr;
678
679 x_return_status := l_return_status;
680 x_try_id := l_try_id;
681
682 EXCEPTION
683
684 WHEN OTHERS THEN
685
686 IF l_try_id_csr%ISOPEN THEN
687 CLOSE l_try_id_csr;
688 END IF;
689
690 -- store SQL error message on message stack for caller
691 OKL_API.SET_MESSAGE (
692 p_app_name => G_APP_NAME
693 ,p_msg_name => G_UNEXPECTED_ERROR
694 ,p_token1 => G_SQLCODE_TOKEN
695 ,p_token1_value => sqlcode
696 ,p_token2 => G_SQLERRM_TOKEN
697 ,p_token2_value => sqlerrm);
698
699 -- notify caller of an UNEXPECTED error
700 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
701
702 END get_transaction_id;
703
704
705 -- Start of comments
706 --
707 -- Procedure Name : get_stream_type_id
708 -- Description : Gets stream type id for stream type code
709 -- Business Rules :
710 -- Parameters : transaction name
711 -- Version : 1.0
712 -- End of comments
713
714 PROCEDURE get_stream_type_id (
715 p_sty_code IN VARCHAR2,
716 x_return_status OUT NOCOPY VARCHAR2,
717 x_sty_id OUT NOCOPY NUMBER) IS
718
719 -- Cursor to get the sty_id for the code passed
720 CURSOR l_sty_id_csr (cp_sty_code IN VARCHAR2) IS
721 SELECT id
722 FROM okl_strm_type_b s
723 WHERE s.code = cp_sty_code;
724
725 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
726 l_sty_id NUMBER := NULL;
727
728 BEGIN
729
730 -- Get the sty_id for the code passed
731 OPEN l_sty_id_csr (p_sty_code);
732 FETCH l_sty_id_csr INTO l_sty_id;
733 IF l_sty_id_csr%NOTFOUND THEN
734 l_return_status := OKL_API.G_RET_STS_ERROR;
735 END IF;
736 CLOSE l_sty_id_csr;
737
738 x_return_status := l_return_status;
739 x_sty_id := l_sty_id;
740
741 EXCEPTION
742
743 WHEN OTHERS THEN
744
745 IF l_sty_id_csr%ISOPEN THEN
746 CLOSE l_sty_id_csr;
747 END IF;
748
749 -- store SQL error message on message stack for caller
750 OKL_API.SET_MESSAGE (
751 p_app_name => G_APP_NAME
752 ,p_msg_name => G_UNEXPECTED_ERROR
753 ,p_token1 => G_SQLCODE_TOKEN
754 ,p_token1_value => sqlcode
755 ,p_token2 => G_SQLERRM_TOKEN
756 ,p_token2_value => sqlerrm);
757
758 -- notify caller of an UNEXPECTED error
759 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
760
761 END get_stream_type_id;
762
763
764 -- Start of comments
765 --
766 -- Procedure Name : set_message
767 -- Description : Put messages on stack
768 -- Business Rules :
769 -- Parameters : application, message name, tokens
770 -- Version : 1.0
771 -- End of comments
772
773 PROCEDURE set_message (
774 p_app_name IN VARCHAR2,
775 p_msg_name IN VARCHAR2,
776 p_msg_level IN NUMBER,
777 p_token1 IN VARCHAR2,
778 p_token1_value IN VARCHAR2,
779 p_token2 IN VARCHAR2,
780 p_token2_value IN VARCHAR2,
781 p_token3 IN VARCHAR2,
782 p_token3_value IN VARCHAR2,
783 p_token4 IN VARCHAR2,
784 p_token4_value IN VARCHAR2,
785 p_token5 IN VARCHAR2,
786 p_token5_value IN VARCHAR2,
787 p_token6 IN VARCHAR2,
788 p_token6_value IN VARCHAR2,
789 p_token7 IN VARCHAR2,
790 p_token7_value IN VARCHAR2,
791 p_token8 IN VARCHAR2,
792 p_token8_value IN VARCHAR2,
793 p_token9 IN VARCHAR2,
794 p_token9_value IN VARCHAR2,
795 p_token10 IN VARCHAR2,
796 p_token10_value IN VARCHAR2) IS
797
798 l_token1_value VARCHAR2(256);
799 l_token2_value VARCHAR2(256);
800 l_token3_value VARCHAR2(256);
801 l_token4_value VARCHAR2(256);
802 l_token5_value VARCHAR2(256);
803 l_token6_value VARCHAR2(256);
804 l_token7_value VARCHAR2(256);
805 l_token8_value VARCHAR2(256);
806 l_token9_value VARCHAR2(256);
807 l_token10_value VARCHAR2(256);
808
809 BEGIN
810
811 IF fnd_msg_pub.check_msg_level (p_msg_level) THEN
812
813 -- ************************
814 -- Check independent tokens
815 -- ************************
816
817 l_token1_value := set_token (p_token1, p_token1_value);
818 l_token2_value := set_token (p_token2, p_token2_value);
819 l_token3_value := set_token (p_token3, p_token3_value);
820 l_token4_value := set_token (p_token4, p_token4_value);
821 l_token5_value := set_token (p_token5, p_token5_value);
822 l_token6_value := set_token (p_token6, p_token6_value);
823 l_token7_value := set_token (p_token7, p_token7_value);
824 l_token8_value := set_token (p_token8, p_token8_value);
825 l_token9_value := set_token (p_token9, p_token9_value);
826 l_token10_value := set_token (p_token10, p_token10_value);
827
828 -- ************************************
829 -- Check tokens which have parent token
830 -- ************************************
831
832 l_token2_value := set_token
833 (p_token1, p_token1_value, p_token2, p_token2_value, l_token2_value);
834 l_token4_value := set_token
835 (p_token3, p_token3_value, p_token4, p_token4_value, l_token4_value);
836 l_token6_value := set_token
837 (p_token5, p_token5_value, p_token6, p_token6_value, l_token6_value);
838 l_token8_value := set_token
839 (p_token7, p_token7_value, p_token8, p_token8_value, l_token8_value);
840 l_token10_value := set_token
841 (p_token9, p_token9_value, p_token10, p_token10_value, l_token10_value);
842
843 -- **************
844 -- Create message
845 -- **************
846
847 OKL_API.SET_MESSAGE(
848 p_app_name => p_app_name
849 ,p_msg_name => p_msg_name
850 ,p_token1 => p_token1
851 ,p_token1_value => l_token1_value
852 ,p_token2 => p_token2
853 ,p_token2_value => l_token2_value
854 ,p_token3 => p_token3
855 ,p_token3_value => l_token3_value
856 ,p_token4 => p_token4
857 ,p_token4_value => l_token4_value
858 ,p_token5 => p_token5
859 ,p_token5_value => l_token5_value
860 ,p_token6 => p_token6
861 ,p_token6_value => l_token6_value
862 ,p_token7 => p_token7
863 ,p_token7_value => l_token7_value
864 ,p_token8 => p_token8
865 ,p_token8_value => l_token8_value
866 ,p_token9 => p_token9
867 ,p_token9_value => l_token9_value
868 ,p_token10 => p_token10
869 ,p_token10_value => l_token10_value);
870
871 END IF;
872
873 EXCEPTION
874
875 WHEN OTHERS THEN
876
877 -- store SQL error message on message stack for caller
878 OKL_API.SET_MESSAGE (
879 p_app_name => G_APP_NAME
880 ,p_msg_name => G_UNEXPECTED_ERROR
881 ,p_token1 => G_SQLCODE_TOKEN
882 ,p_token1_value => sqlcode
883 ,p_token2 => G_SQLERRM_TOKEN
884 ,p_token2_value => sqlerrm);
885
886 END set_message;
887
888
889 -- Start of comments
890 --
891 -- Procedure Name : set_invalid_rule_message
892 -- Description : Add message indicating invalid rule setup
893 -- Business Rules :
894 -- Parameters : contract, contract line, rule group, rule code
895 -- Version : 1.0
896 -- End of comments
897
898 PROCEDURE set_invalid_rule_message (
899 p_rgd_code IN VARCHAR2,
900 p_rdf_code IN VARCHAR2) IS
901
902 -- Get rule description
903 CURSOR l_rule_csr (cp_rule_code VARCHAR2) IS
904 SELECT rd.meaning
905 FROM okc_rule_defs_v rd
906 WHERE rd.application_id = 540
907 AND rd.rule_code = cp_rule_code;
908
909 l_rule_meaning okc_rule_defs_v.meaning%TYPE;
910 l_group_meaning fnd_lookups.meaning%TYPE;
911 l_label_value VARCHAR2(2000) := NULL;
912
913 BEGIN
914
915 l_label_value := get_ak_attribute ('OKL_' || p_rgd_code || '-' || p_rdf_code);
916
917 IF l_label_value IS NULL THEN
918 l_label_value := get_ak_attribute ('OKL_' || p_rgd_code);
919 END IF;
920
921 IF l_label_value IS NULL THEN
922
923 OPEN l_rule_csr (p_rdf_code);
924 FETCH l_rule_csr INTO l_rule_meaning;
925 CLOSE l_rule_csr;
926
927 l_group_meaning := get_lookup_meaning ('OKC_RULE_GROUP_DEF', p_rgd_code, 'N');
928
929 l_label_value := l_group_meaning || ' - ' || l_rule_meaning;
930
931 END IF;
932
933 OKL_API.SET_MESSAGE (
934 p_app_name => G_APP_NAME
935 ,p_msg_name => 'OKL_AM_INVALID_RULE_SETUP'
936 ,p_token1 => 'LABEL'
937 ,p_token1_value => l_label_value);
938
939 EXCEPTION
940
941 WHEN OTHERS THEN
942
943 IF (l_rule_csr%ISOPEN) THEN
944 CLOSE l_rule_csr;
945 END IF;
946
947 -- store SQL error message on message stack for caller
948 OKL_API.SET_MESSAGE (
949 p_app_name => G_APP_NAME
950 ,p_msg_name => G_UNEXPECTED_ERROR
951 ,p_token1 => G_SQLCODE_TOKEN
952 ,p_token1_value => sqlcode
953 ,p_token2 => G_SQLERRM_TOKEN
954 ,p_token2_value => sqlerrm);
955
956 END set_invalid_rule_message;
957
958
959 -- Start of comments
960 --
961 -- Procedure Name : get_rule_record
962 -- Description : Get rule information for a rule
963 -- Business Rules :
964 -- Parameters : contract, contract line, rule group, rule code
965 -- Version : 1.0
966 -- End of comments
967
968 PROCEDURE get_rule_record (
969 p_rgd_code IN VARCHAR2,
970 p_rdf_code IN VARCHAR2,
971 p_chr_id IN NUMBER,
972 p_cle_id IN NUMBER,
973 p_rgd_id IN NUMBER,
974 p_message_yn IN BOOLEAN,
975 x_rulv_rec OUT NOCOPY okl_rule_pub.rulv_rec_type,
976 x_return_status OUT NOCOPY VARCHAR2) IS
977
978 l_rgpv_tbl okl_rule_pub.rgpv_tbl_type;
979 l_rulv_tbl okl_rule_pub.rulv_tbl_type;
980
981 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
982 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
983
984 l_api_version CONSTANT NUMBER := g_api_version;
985 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
986 l_msg_data VARCHAR2(2000);
987
988 l_rg_count NUMBER;
989 l_rule_count NUMBER;
990
991 l_no_rule_data EXCEPTION;
992
993 --gboomina Bug 4734134 - Added - to get SCS_CODE - Start
994 l_msg_name VARCHAR2(30);
995 l_scs_code okc_k_headers_b.scs_code%type;
996 -- cursor to get scs_code
997 CURSOR scs_code_csr IS
998 SELECT scs_code
999 FROM okc_k_headers_b
1000 WHERE id = p_chr_id;
1001 --gboomina Bug 4734134 - End
1002 BEGIN
1003
1004 -- *****************
1005 -- Get Rule Category
1006 -- *****************
1007
1008 IF p_rgd_id IS NOT NULL THEN
1009
1010 l_rgpv_tbl(1).id := p_rgd_id;
1011
1012 ELSE
1013
1014 okl_rule_apis_pub.get_contract_rgs (
1015 p_api_version => l_api_version,
1016 p_init_msg_list => OKL_API.G_FALSE,
1017 p_chr_id => p_chr_id,
1018 p_cle_id => p_cle_id,
1019 p_rgd_code => p_rgd_code,
1020 x_return_status => l_return_status,
1021 x_msg_count => l_msg_count,
1022 x_msg_data => l_msg_data,
1023 x_rgpv_tbl => l_rgpv_tbl,
1024 x_rg_count => l_rg_count);
1025
1026 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1027 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1028 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1029 RAISE OKL_API.G_EXCEPTION_ERROR;
1030 ELSIF (NVL (l_rg_count, 0) <> 1) THEN
1031 RAISE l_no_rule_data;
1032 END IF;
1033
1034 END IF;
1035
1036 -- ***************
1037 -- Get Rule Record
1038 -- ***************
1039
1040 okl_rule_apis_pub.get_contract_rules (
1041 p_api_version => l_api_version,
1042 p_init_msg_list => OKL_API.G_FALSE,
1043 p_rgpv_rec => l_rgpv_tbl(1),
1044 p_rdf_code => p_rdf_code,
1045 x_return_status => l_return_status,
1046 x_msg_count => l_msg_count,
1047 x_msg_data => l_msg_data,
1048 x_rulv_tbl => l_rulv_tbl,
1049 x_rule_count => l_rule_count);
1050
1051 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1052 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1053 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1054 RAISE OKL_API.G_EXCEPTION_ERROR;
1055 ELSIF (NVL (l_rule_count, 0) <> 1) THEN
1056 RAISE l_no_rule_data;
1057 END IF;
1058
1059 x_rulv_rec := l_rulv_tbl(1);
1060 x_return_status := l_overall_status;
1061
1062 EXCEPTION
1063
1064 WHEN l_no_rule_data THEN
1065 IF p_message_yn THEN
1066 -- Unable to complete process due to missing
1067 -- information (RULE rule in GROUP group)
1068
1069 --gboomina Bug 4734134 - Changing the Error msg appropriate to
1070 -- Contract and Vendor Program based on SCS_CODE - Start
1071
1072 -- get scs_code
1073 FOR x IN scs_code_csr
1074 LOOP
1075 l_scs_code := x.scs_code;
1076 END LOOP;
1077
1078 IF l_scs_code = 'PROGRAM' THEN
1079 l_msg_name := 'OKL_AM_NO_VP_RULE_DATA';
1080 ELSE
1081 l_msg_name := 'OKL_AM_NO_RULE_DATA' ;
1082 END IF;
1083
1084 set_message (
1085 p_app_name => G_APP_NAME
1086 ,p_msg_name => l_msg_name
1087 ,p_token1 => 'GROUP'
1088 ,p_token1_value => p_rgd_code
1089 ,p_token2 => 'RULE'
1090 ,p_token2_value => p_rdf_code);
1091 --gboomina Bug 4734134 - End
1092 END IF;
1093
1094 x_return_status := OKL_API.G_RET_STS_ERROR;
1095
1096 WHEN OTHERS THEN
1097
1098 -- error message will come from called APIs
1099 -- notify caller of an UNEXPECTED error
1100 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1101
1102 END get_rule_record;
1103
1104
1105 -- Start of comments
1106 --
1107 -- Procedure Name : get_rule_record
1108 -- Description : Get rule information for a rule and return the message stack
1109 -- Business Rules :
1110 -- Parameters : contract, contract line, rule group, rule code
1111 -- Version : 1.0
1112 -- End of comments
1113
1114 PROCEDURE get_rule_record (
1115 p_rgd_code IN VARCHAR2,
1116 p_rdf_code IN VARCHAR2,
1117 p_chr_id IN NUMBER,
1118 p_cle_id IN NUMBER,
1119 p_message_yn IN BOOLEAN,
1120 x_rulv_rec OUT NOCOPY okl_rule_pub.rulv_rec_type,
1121 x_return_status OUT NOCOPY VARCHAR2,
1122 x_msg_count OUT NOCOPY VARCHAR2,
1123 x_msg_data OUT NOCOPY VARCHAR2) IS
1124
1125 BEGIN
1126
1127 get_rule_record (
1128 p_rgd_code => p_rgd_code,
1129 p_rdf_code => p_rdf_code,
1130 p_chr_id => p_chr_id,
1131 p_cle_id => p_cle_id,
1132 x_rulv_rec => x_rulv_rec,
1133 x_return_status => x_return_status,
1134 p_message_yn => p_message_yn);
1135
1136 okc_api.end_activity (
1137 x_msg_count => x_msg_count,
1138 x_msg_data => x_msg_data);
1139
1140 EXCEPTION
1141
1142 WHEN OTHERS THEN
1143
1144 -- error message will come from called APIs
1145 -- notify caller of an UNEXPECTED error
1146 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1147
1148 END get_rule_record;
1149
1150
1151 -- Start of comments
1152 --
1153 -- Procedure Name : get_bill_to_address
1154 -- Description : Return Bill_To address record for a contract
1155 -- Business Rules :
1156 -- Parameters : contract id
1157 -- History : RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1158 -- : RMUNJULU 22-JAN-04 3394507 corrected cursor to get correct Bill To Address
1159 -- and pass it properly to the next cursor
1160 -- Version : 1.0
1161 -- End of comments
1162
1163 PROCEDURE get_bill_to_address (
1164 p_contract_id IN NUMBER,
1165 p_message_yn IN BOOLEAN,
1166 x_bill_to_address_rec OUT NOCOPY okx_cust_site_uses_v%ROWTYPE,
1167 x_return_status OUT NOCOPY VARCHAR2) IS
1168
1169 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1170 -- Get Bill to Values for LESSEE
1171 CURSOR l_bto_values_csr(p_chr_id IN NUMBER) IS
1172 SELECT CHR.bill_to_site_use_id, -- RMUNJULU 3394507 get bill to site id from K Header
1173 CPL.ROLE party_role,
1174 CPL.jtot_object1_code jtot_object1_code,
1175 CPL.object1_id1 object1_id1,
1176 CPL.object1_id2 object1_id2
1177 FROM OKC_K_HEADERS_B CHR,
1178 OKC_K_PARTY_ROLES_V CPL
1179 WHERE CHR.id = p_chr_id
1180 AND CHR.id = CPL.chr_id
1181 AND CPL.rle_code = 'LESSEE';
1182
1183
1184 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1185 -- Removed cp_id2 from parameters and from WHERE
1186 -- Select bill_to record
1187 CURSOR l_bill_to_address_csr (cp_id1 NUMBER) IS
1188 SELECT *
1189 FROM okx_cust_site_uses_v cst
1190 WHERE cst.id1 = cp_id1;
1191 -- AND cst.id2 = cp_id2;
1192
1193 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1194 l_rulv_rec okl_rule_pub.rulv_rec_type;
1195 l_bill_to_address_rec okx_cust_site_uses_v%ROWTYPE;
1196
1197 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1198 l_bto_values_rec l_bto_values_csr%ROWTYPE;
1199 l_party_name VARCHAR2(320);
1200
1201
1202 BEGIN
1203
1204 /* -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1205 get_rule_record (
1206 p_rgd_code => 'LABILL',
1207 p_rdf_code => 'BTO',
1208 p_chr_id => p_contract_id,
1209 p_cle_id => NULL,
1210 p_message_yn => TRUE,
1211 x_rulv_rec => l_rulv_rec,
1212 x_return_status => l_return_status);
1213
1214 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1215 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1216 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1217 RAISE OKL_API.G_EXCEPTION_ERROR;
1218 END IF;
1219 */
1220
1221 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1222 -- Added code to get BTO values from table not rule
1223 OPEN l_bto_values_csr
1224 (p_contract_id);
1225 FETCH l_bto_values_csr INTO l_bto_values_rec;
1226
1227 IF l_bto_values_csr%NOTFOUND THEN
1228
1229 -- Set the message
1230 OKL_API.set_message (
1231 p_app_name => G_APP_NAME
1232 ,p_msg_name => G_INVALID_VALUE1
1233 ,p_token1 => 'COL_NAME'
1234 ,p_token1_value => 'contract_id');
1235
1236 l_return_status := OKL_API.G_RET_STS_ERROR;
1237
1238 ELSIF l_bto_values_rec.bill_to_site_use_id IS NULL THEN
1239
1240 -- Get party name
1241 l_party_name := get_jtf_object_name
1242 (l_bto_values_rec.jtot_object1_code
1243 ,l_bto_values_rec.object1_id1
1244 ,l_bto_values_rec.object1_id2);
1245 -- Billing information is not found for party PARTY having role PARTY_ROLE.
1246 -- Set the message
1247 OKC_API.SET_MESSAGE (
1248 p_app_name => G_APP_NAME,
1249 p_msg_name => 'OKL_AM_NO_BILLING_INFO_NEW',
1250 p_token1 => 'PARTY',
1251 p_token1_value => l_party_name,
1252 p_token2 => 'PARTY_ROLE',
1253 p_token2_value => l_bto_values_rec.party_role);
1254
1255 l_return_status := OKL_API.G_RET_STS_ERROR;
1256 END IF;
1257
1258 CLOSE l_bto_values_csr;
1259
1260 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1261 -- Removed l_rulv_rec.object1_id2 from passing to cursor
1262 OPEN l_bill_to_address_csr
1263 (l_bto_values_rec.bill_to_site_use_id); -- RMUNJULU 3394507 Pass the right bill to site id
1264 FETCH l_bill_to_address_csr INTO l_bill_to_address_rec;
1265
1266 IF (l_bill_to_address_csr%NOTFOUND) THEN
1267 l_return_status := Okl_Api.G_RET_STS_ERROR;
1268 END IF;
1269
1270 CLOSE l_bill_to_address_csr;
1271
1272 x_return_status := l_return_status;
1273 x_bill_to_address_rec := l_bill_to_address_rec;
1274
1275 EXCEPTION
1276
1277 WHEN OTHERS THEN
1278
1279 IF l_bill_to_address_csr%ISOPEN THEN
1280 CLOSE l_bill_to_address_csr;
1281 END IF;
1282
1283 -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
1284 IF l_bto_values_csr%ISOPEN THEN
1285 CLOSE l_bto_values_csr;
1286 END IF;
1287
1288 IF p_message_yn THEN
1289 -- store SQL error message on message stack for caller
1290 OKL_API.SET_MESSAGE (
1291 p_app_name => G_APP_NAME
1292 ,p_msg_name => G_UNEXPECTED_ERROR
1293 ,p_token1 => G_SQLCODE_TOKEN
1294 ,p_token1_value => sqlcode
1295 ,p_token2 => G_SQLERRM_TOKEN
1296 ,p_token2_value => sqlerrm);
1297 END IF;
1298
1299 -- notify caller of an UNEXPECTED error
1300 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1301
1302 END get_bill_to_address;
1303
1304
1305 -- Start of comments
1306 --
1307 -- Procedure Name : get_formula_value
1308 -- Description : Request Formula Engine to execute a formula
1309 -- Business Rules :
1310 -- Parameters : formula_name, contract, contract line
1311 -- Version : 1.0
1312 -- End of comments
1313
1314 PROCEDURE get_formula_value (
1315 p_formula_name IN OKL_FORMULAE_B.name%TYPE,
1316 p_chr_id IN OKC_K_HEADERS_B.id%TYPE,
1317 p_cle_id IN OKL_K_LINES.id%TYPE,
1318 p_additional_parameters IN
1319 okl_execute_formula_pub.ctxt_val_tbl_type,
1320 x_formula_value OUT NOCOPY NUMBER,
1321 x_return_status OUT NOCOPY VARCHAR2) IS
1322
1323 l_api_version CONSTANT NUMBER := g_api_version;
1324 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1325 l_msg_data VARCHAR2(2000);
1326 l_formula_value NUMBER := 0;
1327 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1329
1330 BEGIN
1331
1332 okl_execute_formula_pub.execute (
1333 p_api_version => l_api_version,
1334 p_init_msg_list => OKL_API.G_FALSE,
1335 x_return_status => l_return_status,
1336 x_msg_count => l_msg_count,
1337 x_msg_data => l_msg_data,
1338 p_formula_name => p_formula_name,
1339 p_contract_id => p_chr_id,
1340 p_line_id => p_cle_id,
1341 p_additional_parameters => p_additional_parameters,
1342 x_value => l_formula_value);
1343
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 x_formula_value := l_formula_value;
1351 x_return_status := l_overall_status;
1352
1353 EXCEPTION
1354
1355 WHEN OTHERS THEN
1356
1357 -- error message will come from called APIs
1358 -- notify caller of an UNEXPECTED error
1359 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1360
1361 END get_formula_value;
1362
1363
1364 -- Start of comments
1365 --
1366 -- Procedure Name : get_formula_string
1367 -- Description : Return formula string of a formula
1368 -- It can be used for validation - if NULL
1369 -- is returned, then a formula does not
1370 -- exist or can not be evaluated
1371 -- Business Rules :
1372 -- Parameters : formula name
1373 -- Version : 1.0
1374 -- End of comments
1375
1376 FUNCTION get_formula_string (
1377 p_formula_name IN VARCHAR2)
1378 RETURN VARCHAR2 IS
1379
1380 -- Extract evaluation string for a formula
1381 CURSOR l_formula_csr
1382 (cp_formula_name IN okl_formulae_v.name%TYPE) IS
1383 SELECT f.formula_string
1384 FROM okl_formulae_v f
1385 WHERE f.name = cp_formula_name
1386 AND f.start_date <= SYSDATE
1387 AND NVL (f.end_date, sysdate) >= SYSDATE;
1388
1389 l_formula_string okl_formulae_v.formula_string%TYPE := NULL;
1390
1391 BEGIN
1392
1393 OPEN l_formula_csr (p_formula_name);
1394 FETCH l_formula_csr INTO l_formula_string;
1395 CLOSE l_formula_csr;
1396
1397 RETURN l_formula_string;
1398
1399 EXCEPTION
1400
1401 WHEN OTHERS THEN
1402
1403 -- store SQL error message on message stack for caller
1404 OKL_API.SET_MESSAGE (
1405 p_app_name => G_APP_NAME
1406 ,p_msg_name => G_UNEXPECTED_ERROR
1407 ,p_token1 => G_SQLCODE_TOKEN
1408 ,p_token1_value => sqlcode
1409 ,p_token2 => G_SQLERRM_TOKEN
1410 ,p_token2_value => sqlerrm);
1411
1412 IF (l_formula_csr%ISOPEN) THEN
1413 CLOSE l_formula_csr;
1414 END IF;
1415
1416 RETURN NULL;
1417
1418 END get_formula_string;
1419
1420
1421 -- Start of comments
1422 --
1423 -- Procedure Name : process_massages
1424 -- Description : Save messages from stack into
1425 -- transaction message table
1426 -- Business Rules :
1427 -- Parameters : source table name, referenced id
1428 -- Version : 1.0
1429 -- End of comments
1430
1431 PROCEDURE process_messages(
1432 p_trx_source_table IN OKL_TRX_MSGS.trx_source_table%TYPE,
1433 p_trx_id IN OKL_TRX_MSGS.trx_id%TYPE,
1434 x_return_status OUT NOCOPY VARCHAR2) IS
1435
1436 px_error_rec okl_api.error_rec_type;
1437 lp_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1438 lx_tmgv_tbl okl_trx_msgs_pub.tmgv_tbl_type;
1439
1440 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1441 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1442
1443 l_api_version CONSTANT NUMBER := g_api_version;
1444 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
1445 l_msg_data VARCHAR2(2000);
1446
1447 l_seq INTEGER := NVL(lp_tmgv_tbl.LAST, 0) + 1;
1448 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
1449 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
1450
1451 BEGIN
1452
1453 -- ***************************
1454 -- Get messages from the stack
1455 -- ***************************
1456
1457 LOOP
1458
1459 fnd_msg_pub.get(
1460 p_msg_index => l_msg_idx,
1461 p_encoded => fnd_api.g_false,
1462 p_data => px_error_rec.msg_data,
1463 p_msg_index_out => px_error_rec.msg_count);
1464
1465 IF (px_error_rec.msg_count IS NOT NULL) THEN
1466 lp_tmgv_tbl(l_seq).sequence_number := l_seq;
1467 lp_tmgv_tbl(l_seq).message_text := px_error_rec.msg_data;
1468 lp_tmgv_tbl(l_seq).trx_source_table := p_trx_source_table;
1469 lp_tmgv_tbl(l_seq).trx_id := p_trx_id;
1470 END IF;
1471
1472 EXIT WHEN ((px_error_rec.msg_count = last_msg_idx)
1473 OR (px_error_rec.msg_count IS NULL));
1474
1475 l_msg_idx := FND_MSG_PUB.G_NEXT;
1476 l_seq := l_seq + 1;
1477
1478 END LOOP;
1479
1480 -- **************************
1481 -- Save messagess in TRX_MSGS
1482 -- **************************
1483
1484 IF (lp_tmgv_tbl.COUNT > 0) THEN
1485
1486 OKL_TRX_MSGS_PUB.insert_trx_msgs (
1487 p_api_version => l_api_version,
1488 p_init_msg_list => OKL_API.G_FALSE,
1489 x_return_status => l_return_status,
1490 x_msg_count => l_msg_count,
1491 x_msg_data => l_msg_data,
1492 p_tmgv_tbl => lp_tmgv_tbl,
1493 x_tmgv_tbl => lx_tmgv_tbl);
1494
1495 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1496 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1497 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1498 RAISE OKL_API.G_EXCEPTION_ERROR;
1499 END IF;
1500
1501 END IF;
1502
1503 x_return_status := l_overall_status;
1504
1505 EXCEPTION
1506
1507 WHEN OTHERS THEN
1508 -- store SQL error message on message stack for caller
1509 OKL_API.SET_MESSAGE (
1510 p_app_name => G_APP_NAME
1511 ,p_msg_name => G_UNEXPECTED_ERROR
1512 ,p_token1 => G_SQLCODE_TOKEN
1513 ,p_token1_value => sqlcode
1514 ,p_token2 => G_SQLERRM_TOKEN
1515 ,p_token2_value => sqlerrm);
1516
1517 -- notify caller of an UNEXPECTED error
1518 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1519
1520 END process_messages;
1521
1522
1523 -- Start of comments
1524 --
1525 -- Procedure Name : get_object_details
1526 -- Description : Return details of JTF object
1527 -- Business Rules :
1528 -- Parameters : code, id1, id2, select columns, where clause
1529 -- Version : 1.0
1530 -- End of comments
1531
1532 PROCEDURE get_object_details (
1533 p_object_code IN VARCHAR2,
1534 p_object_id1 IN VARCHAR2,
1535 p_object_id2 IN VARCHAR2,
1536 p_check_status IN VARCHAR2,
1537 p_other_select IN select_tbl_type,
1538 p_other_where IN where_tbl_type,
1539 x_object_tbl OUT NOCOPY jtf_object_tbl_type,
1540 x_return_status OUT NOCOPY VARCHAR2) IS
1541
1542 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1543 l_cnt NUMBER := 0;
1544
1545 TYPE object_curs_type IS REF CURSOR;
1546 l_object_curs object_curs_type;
1547 l_object_tbl jtf_object_tbl_type;
1548
1549 l_from_table jtf_objects_b.from_table%TYPE;
1550 l_where_clause jtf_objects_b.where_clause%TYPE;
1551 l_query_string VARCHAR2(4000) := NULL;
1552
1553 -- Get the Object definition parameters required to build the query
1554 CURSOR l_object_csr (cp_object_code IN VARCHAR2) IS
1555 SELECT ob.from_table,
1556 ob.where_clause
1557 FROM jtf_objects_b ob
1558 WHERE ob.object_code = cp_object_code;
1559
1560 BEGIN
1561
1562 IF p_object_id1 IS NULL AND p_other_where.COUNT = 0 THEN
1563 null; -- invalid parameters
1564 END IF;
1565
1566 IF okl_context.get_okc_org_id IS NULL THEN
1567 -- Read from profile
1568 okl_context.set_okc_org_context (NULL, NULL);
1569 END IF;
1570
1571 OPEN l_object_csr (p_object_code);
1572 FETCH l_object_csr INTO l_from_table, l_where_clause;
1573 CLOSE l_object_csr;
1574
1575 l_query_string := l_query_string ||
1576 'SELECT ''' || p_object_code || ''', ' ||
1577 p_object_code || '.ID1, ' ||
1578 p_object_code || '.ID2, ' ||
1579 p_object_code || '.NAME, ' ||
1580 p_object_code || '.DESCRIPTION, ' ;
1581
1582 IF p_other_select.COUNT > 0 THEN
1583
1584 FOR l_ind IN p_other_select.FIRST..p_other_select.LAST LOOP
1585 l_query_string := l_query_string ||
1586 ' REPLACE ( ' ||
1587 p_object_code || '.' || p_other_select (l_ind) ||
1588 ', ''' || G_DELIM || ''',''' || G_DELIM || G_DELIM || ''') ' ||
1589 '|| ''' || G_DELIM || ''' ||';
1590 END LOOP;
1591 l_query_string := RTRIM (l_query_string, '|| ''' || G_DELIM || ''' ||');
1592
1593 ELSE
1594 l_query_string := l_query_string || 'NULL';
1595 END IF;
1596
1597 l_query_string := l_query_string ||
1598 ' FROM ' || l_from_table ||
1599 ' WHERE (' || NVL (l_where_clause, '1=1') || ')';
1600
1601 IF p_object_id1 IS NOT NULL THEN
1602 l_query_string := l_query_string ||
1603 --Added by rajnisku 6669820
1604 ' AND ' || p_object_code || '.ID1 = :1' ||
1605 --end rajnisku bug 6669820
1606 ' AND NVL (' || p_object_code || '.ID2, ''#'') = ' ||
1607 'NVL (''' || p_object_id2 || ''', ''#'')';
1608
1609
1610 ELSIF p_other_where.COUNT > 0 THEN
1611 FOR l_ind IN p_other_where.FIRST..p_other_where.LAST LOOP
1612 l_query_string := l_query_string ||
1613 'AND ' || p_object_code ||
1614 '.' || p_other_where(l_ind).column_name ||
1615 ' ' || p_other_where(l_ind).operation ||
1616 ' ''' || p_other_where(l_ind).condition_value || '''';
1617 END LOOP;
1618 END IF;
1619
1620 IF p_check_status = 'Y' THEN
1621 l_query_string := l_query_string ||
1622 ' AND NVL (' || p_object_code || '.STATUS, ''A'') = ''A''' ||
1623 ' AND NVL (' || p_object_code || '.START_DATE_ACTIVE, SYSDATE) <= SYSDATE' ||
1624 ' AND NVL (' || p_object_code || '.END_DATE_ACTIVE, SYSDATE) >= SYSDATE' ;
1625 END IF;
1626 --Added by rajnisku for bug 6488267
1627 IF p_object_id1 IS NOT NULL THEN
1628 OPEN l_object_curs FOR l_query_string USING p_object_id1;
1629 LOOP
1630 l_cnt := l_cnt + 1;
1631 FETCH l_object_curs INTO l_object_tbl(l_cnt);
1632 EXIT WHEN l_object_curs%NOTFOUND;
1633 END LOOP;
1634 ELSE
1635 OPEN l_object_curs FOR l_query_string;
1636 LOOP
1637 l_cnt := l_cnt + 1;
1638 FETCH l_object_curs INTO l_object_tbl(l_cnt);
1639 EXIT WHEN l_object_curs%NOTFOUND;
1640 END LOOP;
1641 END IF;
1642 CLOSE l_object_curs;
1643 --end rajnisku for bug 6669820
1644
1645 /* -- rmunjulu 6902328 - do not need this piece of code as it is taken care in the ELSE of above IF
1646 Otherwise it is causing issue with the data fetched and Termination quote Send Quote WF - Validate Request fails
1647
1648 OPEN l_object_curs FOR l_query_string;
1649 LOOP
1650 l_cnt := l_cnt + 1;
1651 FETCH l_object_curs INTO l_object_tbl(l_cnt);
1652 EXIT WHEN l_object_curs%NOTFOUND;
1653 END LOOP;
1654 CLOSE l_object_curs;
1655 */
1656
1657 x_object_tbl := l_object_tbl;
1658 x_return_status := l_return_status;
1659
1660 EXCEPTION
1661
1662 WHEN OTHERS THEN
1663
1664 -- error message will come from called APIs
1665 -- notify caller of an UNEXPECTED error
1666 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1667
1668 END get_object_details;
1669
1670
1671 -- Start of comments
1672 --
1673 -- Procedure Name : get_jtf_object_name
1674 -- Description : Return Name of JTF Object
1675 -- Business Rules :
1676 -- Parameters : code, id1, id2
1677 -- Version : 1.0
1678 -- End of comments
1679
1680 FUNCTION get_jtf_object_name (
1681 p_object_code IN VARCHAR2,
1682 p_object_id1 IN VARCHAR2,
1683 p_object_id2 IN VARCHAR2)
1684 RETURN VARCHAR2 IS
1685
1686 l_object_tbl jtf_object_tbl_type;
1687 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1688
1689 BEGIN
1690
1691 get_object_details (
1692 p_object_code => p_object_code,
1693 p_object_id1 => p_object_id1,
1694 p_object_id2 => p_object_id2,
1695 x_object_tbl => l_object_tbl,
1696 x_return_status => l_return_status);
1697
1698 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1699 RETURN l_object_tbl(1).name;
1700 ELSE
1701 RETURN NULL;
1702 END IF;
1703
1704 EXCEPTION
1705
1706 WHEN OTHERS THEN
1707
1708 RETURN NULL;
1709
1710 END get_jtf_object_name;
1711
1712
1713 -- Start of comments
1714 --
1715 -- Procedure Name : get_jtf_object_column
1716 -- Description : Return a value of a column in JTF Object
1717 -- Business Rules :
1718 -- Parameters : column, code, id1, id2
1719 -- Version : 1.0
1720 -- End of comments
1721
1722 FUNCTION get_jtf_object_column (
1723 p_column IN VARCHAR2,
1724 p_object_code IN VARCHAR2,
1725 p_object_id1 IN VARCHAR2,
1726 p_object_id2 IN VARCHAR2)
1727 RETURN VARCHAR2 IS
1728
1729 l_object_tbl jtf_object_tbl_type;
1730 l_other_cols select_tbl_type;
1731 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1732
1733 BEGIN
1734
1735 l_other_cols(1) := p_column;
1736
1737 okl_am_util_pvt.get_object_details (
1738 p_object_code => p_object_code,
1739 p_object_id1 => p_object_id1,
1740 p_object_id2 => p_object_id2,
1741 p_other_select => l_other_cols,
1742 x_object_tbl => l_object_tbl,
1743 x_return_status => l_return_status);
1744
1745 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1746 RETURN l_object_tbl(1).other_values;
1747 ELSE
1748 RETURN NULL;
1749 END IF;
1750
1751 EXCEPTION
1752
1753 WHEN OTHERS THEN
1754
1755 RETURN NULL;
1756
1757 END get_jtf_object_column;
1758
1759
1760 -- Start of comments
1761 --
1762 -- Procedure Name : get_rule_field_value
1763 -- Description : Return Name of JTF Object pointed by Contract Rule
1764 -- Business Rules :
1765 -- Parameters : Rule Group, Rule Code, Contract Id, Line Id, Object Type
1766 -- Note : Unable to use Rules APIs since this function is called
1767 -- from SQL. SQL does not allow SAVEPOINT.
1768 -- Version : 1.0
1769 -- End of comments
1770
1771 FUNCTION get_rule_field_value (
1772 p_rgd_code IN VARCHAR2,
1773 p_rdf_code IN VARCHAR2,
1774 p_chr_id IN NUMBER,
1775 p_cle_id IN NUMBER,
1776 p_object_type IN VARCHAR2)
1777 RETURN VARCHAR2 IS
1778
1779 -- Get rule
1780 CURSOR l_rule_csr (
1781 cp_rgd_code IN VARCHAR2,
1782 cp_rdf_code IN VARCHAR2,
1783 cp_dnz_chr_id IN NUMBER,
1784 cp_chr_id IN NUMBER,
1785 cp_cle_id IN NUMBER) IS
1786 SELECT rdf.object1_id1,
1787 rdf.object2_id1,
1788 rdf.object3_id1,
1789 rdf.object1_id2,
1790 rdf.object2_id2,
1791 rdf.object3_id2,
1792 rdf.jtot_object1_code,
1793 rdf.jtot_object2_code,
1794 rdf.jtot_object3_code
1795 FROM okc_rule_groups_b rgp,
1796 okc_rules_b rdf
1797 WHERE rgp.chr_id = cp_chr_id
1798 AND rgp.cle_id = cp_cle_id
1799 AND (cp_chr_id= -9999 or rgp.dnz_chr_id = cp_dnz_chr_id)
1800 AND rgp.rgd_code = cp_rgd_code
1801 AND rdf.rgp_id = rgp.id
1802 AND rdf.rule_information_category = cp_rdf_code;
1803
1804 l_rule_rec l_rule_csr%ROWTYPE;
1805 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1806
1807 l_chr_id NUMBER;
1808 l_cle_id NUMBER;
1809 l_dnz_chr_id NUMBER;
1810
1811 l_object_code VARCHAR2(30);
1812 l_object_id1 VARCHAR2(40);
1813 l_object_id2 VARCHAR2(200);
1814
1815 BEGIN
1816
1817 IF p_chr_id IS NULL AND p_cle_id IS NOT NULL THEN
1818 l_chr_id := -9999;
1819 l_cle_id := p_cle_id;
1820 l_dnz_chr_id := -9999;
1821 ELSIF p_chr_id IS NULL AND p_cle_id IS NULL THEN
1822 l_chr_id := -9999;
1823 l_cle_id := -9999;
1824 l_dnz_chr_id := -9999;
1825 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NULL THEN
1826 l_chr_id := p_chr_id;
1827 l_cle_id := -9999;
1828 l_dnz_chr_id := p_chr_id;
1829 ELSIF p_chr_id IS NOT NULL AND p_cle_id IS NOT NULL THEN
1830 l_chr_id := -9999;
1831 l_cle_id := p_cle_id;
1832 l_dnz_chr_id := p_chr_id;
1833 END IF;
1834
1835 OPEN l_rule_csr (p_rgd_code, p_rdf_code, l_dnz_chr_id, l_chr_id, l_cle_id);
1836 FETCH l_rule_csr INTO l_rule_rec;
1837 IF l_rule_csr%NOTFOUND THEN
1838 l_return_status := OKL_API.G_RET_STS_ERROR;
1839 END IF;
1840 CLOSE l_rule_csr;
1841
1842 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1843
1844 IF p_object_type = 'OBJECT1' THEN
1845 l_object_code := l_rule_rec.jtot_object1_code;
1846 l_object_id1 := l_rule_rec.object1_id1;
1847 l_object_id2 := l_rule_rec.object1_id2;
1848
1849 ELSIF p_object_type = 'OBJECT2' THEN
1850 l_object_code := l_rule_rec.jtot_object2_code;
1851 l_object_id1 := l_rule_rec.object2_id1;
1852 l_object_id2 := l_rule_rec.object2_id2;
1853
1854 ELSIF p_object_type = 'OBJECT3' THEN
1855 l_object_code := l_rule_rec.jtot_object3_code;
1856 l_object_id1 := l_rule_rec.object3_id1;
1857 l_object_id2 := l_rule_rec.object3_id2;
1858
1859 ELSE
1860 l_return_status := OKL_API.G_RET_STS_ERROR;
1861 END IF;
1862
1863 END IF;
1864
1865 IF l_return_status = OKL_API.G_RET_STS_SUCCESS THEN
1866 RETURN get_jtf_object_name
1867 (l_object_code, l_object_id1, l_object_id2);
1868 ELSE
1869 RETURN NULL;
1870 END IF;
1871
1872 EXCEPTION
1873
1874 WHEN OTHERS THEN
1875 IF (l_rule_csr%ISOPEN) THEN
1876 CLOSE l_rule_csr;
1877 END IF;
1878
1879 END get_rule_field_value;
1880
1881
1882 -- Start of comments
1883 --
1884 -- Procedure Name : get_program_partner
1885 -- Description : Return contract program partner
1886 -- Business Rules :
1887 -- Parameters : contract id
1888 -- Version : 1.0
1889 -- End of comments
1890
1891 FUNCTION get_program_partner (p_chr_id IN NUMBER) RETURN VARCHAR2 IS
1892
1893 -- Get contract program partner
1894 CURSOR l_partner_csr (cp_chr_id NUMBER) IS
1895 SELECT kpr.jtot_object1_code,
1896 kpr.object1_id1,
1897 kpr.object1_id2
1898 FROM okl_k_headers khr,
1899 okc_k_headers_b par,
1900 okc_k_party_roles_b kpr
1901 WHERE khr.id = cp_chr_id
1902 AND par.id = khr.khr_id
1903 AND par.scs_code = 'PROGRAM'
1904 AND kpr.chr_id = par.id
1905 AND kpr.rle_code = 'OKL_VENDOR'
1906 AND kpr.object1_id1 IS NOT NULL;
1907
1908 l_partner_rec l_partner_csr%ROWTYPE;
1909
1910 BEGIN
1911
1912 OPEN l_partner_csr (p_chr_id);
1913 FETCH l_partner_csr INTO l_partner_rec;
1914 CLOSE l_partner_csr;
1915
1916 RETURN get_jtf_object_name
1917 (l_partner_rec.jtot_object1_code
1918 ,l_partner_rec.object1_id1
1919 ,l_partner_rec.object1_id2);
1920
1921 EXCEPTION
1922
1923 WHEN OTHERS THEN
1924 IF (l_partner_csr%ISOPEN) THEN
1925 CLOSE l_partner_csr;
1926 END IF;
1927 RETURN NULL;
1928
1929 END get_program_partner;
1930
1931
1932 -- Start Fulfillment Specific
1933
1934 ------------------------------------------------------------------------------
1935 -- Start of comments
1936 --
1937 -- Procedure Name :get_content_id
1938 -- Description :Private Fulfillment Procedure, returns template content id
1939 -- Business Rules :
1940 -- Parameters :
1941 -- Version : 1.0
1942 -- End of comments
1943 ------------------------------------------------------------------------------
1944 PROCEDURE get_content_id (
1945 p_ptm_code IN VARCHAR2,
1946 x_content_id OUT NOCOPY NUMBER,
1947 x_subject OUT NOCOPY VARCHAR2,
1948 x_return_status OUT NOCOPY VARCHAR2,
1949 x_msg_count OUT NOCOPY NUMBER,
1950 x_msg_data OUT NOCOPY VARCHAR2 ) IS
1951
1952 --Changed the following cursor to query on the uv instead of
1953 --the _v because the Template for the particular org
1954 --should be picked up.
1955 --Changed by rvaduri for bug 3571668
1956
1957 CURSOR c_content_csr(c_ptm_code VARCHAR2) IS
1958
1959 SELECT opt.jtf_amv_item_id, opt.email_subject_line
1960 FROM OKL_CS_PROCESS_TMPLTS_UV opt
1961 WHERE opt.ptm_code = c_ptm_code
1962 AND opt.start_date < sysdate
1963 AND nvl(opt.end_date, sysdate+1) > sysdate;
1964
1965 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1966 l_content_id jtf_amv_items_vl.item_id%type;
1967 l_default_subject okl_process_tmplts_v.email_subject_line%type;
1968
1969 BEGIN
1970
1971 OPEN c_content_csr(p_ptm_code);
1972 FETCH c_content_csr INTO l_content_id,
1973 l_default_subject;
1974 IF c_content_csr%NOTFOUND THEN
1975
1976 OKL_API.set_message( p_app_name => 'OKL',
1977 p_msg_name => 'OKL_AM_MISSING_PTM_CODE',
1978 p_token1 => 'PTM_CODE',
1979 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
1980 p_lookup_code => p_ptm_code));
1981 l_return_status := OKC_API.G_RET_STS_ERROR;
1982
1983 END IF;
1984 CLOSE c_content_csr;
1985
1986 x_return_status := l_return_status;
1987 x_content_id := l_content_id;
1988 x_subject := l_default_subject;
1989
1990 EXCEPTION
1991
1992 WHEN OTHERS THEN
1993 OKL_API.SET_MESSAGE (
1994 p_app_name => G_APP_NAME
1995 ,p_msg_name => G_UNEXPECTED_ERROR
1996 ,p_token1 => G_SQLCODE_TOKEN
1997 ,p_token1_value => sqlcode
1998 ,p_token2 => G_SQLERRM_TOKEN
1999 ,p_token2_value => sqlerrm);
2000
2001 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2002
2003 END get_content_id;
2004
2005 ------------------------------------------------------------------------------
2006 -- Start of comments
2007 --
2008 -- Procedure Name :get_agent_details
2009 -- Description :Private Fulfillment Procedure, returns sender details
2010 -- Business Rules :
2011 -- Parameters :
2012 -- Version : 1.0
2013 -- End of comments
2014 ------------------------------------------------------------------------------
2015
2016 PROCEDURE get_agent_details( p_agent_id IN NUMBER,
2017 x_agent_id OUT NOCOPY NUMBER,
2018 x_email OUT NOCOPY VARCHAR2,
2019 x_server_id OUT NOCOPY VARCHAR2,
2020 x_return_status OUT NOCOPY VARCHAR2,
2021 x_msg_count OUT NOCOPY NUMBER,
2022 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2023
2024 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2025
2026 CURSOR c_agent_csr (c_agent_id NUMBER) IS
2027 SELECT nvl(ppf.email_address , fu.email_address) email
2028 FROM fnd_user fu,
2029 per_people_f ppf
2030 WHERE fu.employee_id = ppf.person_id (+)
2031 AND fu.user_id = c_agent_id;
2032
2033
2034 CURSOR c_server_csr(c_agent_id NUMBER, c_server_id NUMBER) IS
2035 -- Re-written cursor due to Bug 3375932
2036 SELECT server.server_name server_name
2037 FROM jtf_fm_group_fnd_user_v users
2038 , jtf_fm_group groups
2039 , jtf_fm_service server
2040 WHERE users.user_id = c_agent_id
2041 AND users.group_id = groups.group_id
2042 AND server.server_id = groups.server_id
2043 AND server.server_id = c_server_id;
2044
2045
2046 CURSOR c_server_name_csr(c_server_id NUMBER) IS
2047 SELECT server.server_name server_name
2048 FROM jtf_fm_service server
2049 WHERE server.server_id = c_server_id;
2050
2051 l_agent_id NUMBER;
2052 l_email VARCHAR2(1000);
2053 l_server_id NUMBER;
2054 l_server_name VARCHAR2(50);
2055
2056 l_user_name VARCHAR2(100);
2057 l_user_desc VARCHAR2(100);
2058 l_ptm_meaning VARCHAR2(250);
2059
2060 BEGIN
2061
2062 l_server_id := fnd_profile.value('OKL_FM_SERVER');
2063 l_email := fnd_profile.value('OKL_EMAIL_IDENTITY');
2064
2065 okl_am_wf.get_notification_agent(itemtype =>'',
2066 itemkey =>'',
2067 actid =>NULL,
2068 funcmode =>'',
2069 p_user_id => p_agent_id,
2070 x_name => l_user_name,
2071 x_description => l_user_desc);
2072 IF l_email IS NULL THEN
2073
2074 OPEN c_agent_csr(p_agent_id);
2075 FETCH c_agent_csr INTO l_email;
2076 CLOSE c_agent_csr;
2077
2078 IF l_email IS NULL THEN
2079
2080 OKL_API.set_message( p_app_name => 'OKL',
2081 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2082 p_token1 => 'PTM_MEANING',
2083 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2084 p_lookup_code => g_ptm_code
2085 )
2086 );
2087
2088 OKL_API.set_message( p_app_name => 'OKL',
2089 p_msg_name => 'OKL_AM_FM_AGENT',
2090 p_token1 => 'USERNAME',
2091 p_token1_value => l_user_name);
2092
2093 l_return_status := OKC_API.G_RET_STS_ERROR;
2094 END IF;
2095 END IF;
2096
2097 IF l_server_id IS NULL THEN
2098
2099 OKL_API.set_message( p_app_name => 'OKL',
2100 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2101 p_token1 => 'PTM_MEANING',
2102 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2103 p_lookup_code => g_ptm_code
2104 )
2105 );
2106
2107 OKL_API.set_message( p_app_name => 'OKL',
2108 p_msg_name => 'OKL_AM_FM_SERVER_NOT_FOUND');
2109
2110 l_return_status := OKC_API.G_RET_STS_ERROR;
2111
2112 ELSE
2113
2114 OPEN c_server_csr(p_agent_id, l_server_id);
2115 FETCH c_server_csr INTO l_server_name;
2116 CLOSE c_server_csr;
2117
2118 IF l_server_name IS NULL THEN -- This agent is not associated to the server
2119
2120 OKL_API.set_message( p_app_name => 'OKL',
2121 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2122 p_token1 => 'PTM_MEANING',
2123 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2124 p_lookup_code => g_ptm_code
2125 )
2126 );
2127 OPEN c_server_name_csr(l_server_id);
2128 FETCH c_server_name_csr INTO l_server_name;
2129 CLOSE c_server_name_csr;
2130
2131 OKL_API.set_message( p_app_name => 'OKL',
2132 p_msg_name => 'OKL_AM_FM_AGENT_NOT_FOUND',
2133 p_token1 => 'USERNAME',
2134 p_token1_value => l_user_name,
2135 p_token2 => 'SERVER_NAME',
2136 p_token2_value => l_server_name);
2137
2138 l_return_status := OKC_API.G_RET_STS_ERROR;
2139 END IF;
2140 END IF;
2141
2142 x_return_status := l_return_status;
2143 x_agent_id := p_agent_id;
2144 x_email := l_email;
2145 x_server_id := l_server_id;
2146
2147 EXCEPTION
2148
2149 WHEN OTHERS THEN
2150
2151 OKL_API.SET_MESSAGE (
2152 p_app_name => G_APP_NAME
2153 ,p_msg_name => G_UNEXPECTED_ERROR
2154 ,p_token1 => G_SQLCODE_TOKEN
2155 ,p_token1_value => sqlcode
2156 ,p_token2 => G_SQLERRM_TOKEN
2157 ,p_token2_value => sqlerrm);
2158
2159 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2160
2161 END get_agent_details;
2162
2163 ------------------------------------------------------------------------------
2164 -- Start of comments
2165 --
2166 -- Procedure Name :get_recipient_details
2167 -- Description :Private Fulfillment Procedure, returns recipient details
2168 -- Business Rules :
2169 -- Parameters :
2170 -- Version : 1.0
2171 -- End of comments
2172 ------------------------------------------------------------------------------
2173
2174 PROCEDURE get_recipient_details (
2175 p_recipient_id IN VARCHAR2,
2176 p_recipient_type IN VARCHAR2,
2177 p_expand_roles IN VARCHAR2,
2178 x_email OUT NOCOPY recipient_tbl,
2179 x_return_status OUT NOCOPY VARCHAR2,
2180 x_msg_count OUT NOCOPY NUMBER,
2181 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2182
2183 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2184 l_content_id jtf_amv_items_vl.item_id%type;
2185 l_default_subject okl_process_tmplts_v.email_subject_line%type;
2186
2187 l_email_tbl recipient_tbl;
2188 l_party_object_tbl okl_am_parties_pvt.party_object_tbl_type;
2189 i NUMBER := 0;
2190 j number;
2191 l_email varchar2(300);
2192 BEGIN
2193
2194 -- check expand roles, make sure we are not at contact level already
2195 -- IF p_expand_roles = 'N' and p_recipient_type NOT IN ('PC', 'VC');
2196
2197 -- Get email addresses for recipients
2198 okl_am_parties_pvt.get_party_details (
2199 p_id_code => p_recipient_type,
2200 p_id_value => p_recipient_id,
2201 x_party_object_tbl => l_party_object_tbl,
2202 x_return_status => l_return_status);
2203
2204 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2205
2206 OKL_API.set_message( p_app_name => 'OKL',
2207 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2208 p_token1 => 'PTM_MEANING',
2209 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2210 p_lookup_code => g_ptm_code
2211 )
2212 );
2213
2214 OKL_API.set_message( p_app_name => 'OKL',
2215 p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2216 p_token1 => 'PARTY_TYPE',
2217 p_token1_value => p_recipient_type,
2218 p_token2 => 'PARTY_ID',
2219 p_token2_value => p_recipient_id);
2220
2221 ELSIF (l_party_object_tbl.COUNT > 0) THEN
2222
2223 i := l_party_object_tbl.FIRST;
2224 LOOP
2225
2226 l_email := nvl(l_party_object_tbl(i).pcp_email, l_party_object_tbl(i).c_email);
2227
2228 IF l_email IS NOT NULL THEN
2229
2230 l_email_tbl(i) := nvl(l_party_object_tbl(i).pcp_email, l_party_object_tbl(i).c_email);
2231
2232 END IF;
2233
2234 EXIT WHEN (i = l_party_object_tbl.LAST);
2235 i := l_party_object_tbl.NEXT(i);
2236
2237 END LOOP;
2238
2239 ELSE
2240 OKL_API.set_message( p_app_name => 'OKL',
2241 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2242 p_token1 => 'PTM_MEANING',
2243 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2244 p_lookup_code => g_ptm_code
2245 )
2246 );
2247
2248 OKL_API.set_message( p_app_name => 'OKL',
2249 p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2250 p_token1 => 'PARTY_TYPE',
2251 p_token1_value => p_recipient_type,
2252 p_token2 => 'PARTY_ID',
2253 p_token2_value => p_recipient_id);
2254
2255 l_return_status := OKC_API.G_RET_STS_ERROR;
2256 END IF;
2257
2258 j := l_email_tbl.FIRST;
2259
2260 IF (j IS NOT NULL) AND (l_email_tbl(j) <> OKL_API.G_MISS_CHAR AND l_email_tbl(j) IS NOT NULL) THEN
2261
2262 x_email := l_email_tbl;
2263
2264 ELSE
2265 OKL_API.set_message( p_app_name => 'OKL',
2266 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2267 p_token1 => 'PTM_MEANING',
2268 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2269 p_lookup_code => g_ptm_code
2270 )
2271 );
2272
2273 OKL_API.set_message( p_app_name => 'OKL',
2274 p_msg_name => 'OKL_AM_INVALID_RECIPIENT',
2275 p_token1 => 'PARTY_TYPE',
2276 p_token1_value => p_recipient_type,
2277 p_token2 => 'PARTY_ID',
2278 p_token2_value => p_recipient_id);
2279
2280 l_return_status := OKC_API.G_RET_STS_ERROR;
2281 END IF;
2282
2283 x_return_status := l_return_status;
2284
2285 EXCEPTION
2286
2287 WHEN OTHERS THEN
2288
2289 OKL_API.SET_MESSAGE (
2290 p_app_name => G_APP_NAME
2291 ,p_msg_name => G_UNEXPECTED_ERROR
2292 ,p_token1 => G_SQLCODE_TOKEN
2293 ,p_token1_value => sqlcode
2294 ,p_token2 => G_SQLERRM_TOKEN
2295 ,p_token2_value => sqlerrm);
2296
2297 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2298
2299 END get_recipient_details;
2300
2301 ------------------------------------------------------------------------------
2302 -- Start of comments
2303 --
2304 -- Procedure Name :EXECUTE_FULFILLMENT_REQUEST
2305 -- Description :Public Fulfillment Procedure,calls okl fulfillment wrapper
2306 -- Business Rules :
2307 -- Parameters :
2308 -- Version : 1.0
2309 -- End of comments
2310 ------------------------------------------------------------------------------
2311 PROCEDURE EXECUTE_FULFILLMENT_REQUEST (
2312 p_api_version IN NUMBER
2313 , p_init_msg_list IN VARCHAR2
2314 , x_return_status OUT NOCOPY VARCHAR2
2315 , x_msg_count OUT NOCOPY NUMBER
2316 , x_msg_data OUT NOCOPY VARCHAR2
2317 , p_ptm_code IN VARCHAR2
2318 , p_agent_id IN NUMBER
2319 , p_transaction_id IN NUMBER
2320 , p_recipient_type IN VARCHAR2
2321 , p_recipient_id IN VARCHAR2
2322 , p_expand_roles IN VARCHAR2
2323 , p_subject_line IN VARCHAR2
2324 , p_sender_email IN VARCHAR2
2325 , p_recipient_email IN VARCHAR2
2326 , p_pt_bind_names IN p_bind_var_tbl
2327 , p_pt_bind_values IN p_bind_val_tbl
2328 , p_pt_bind_types IN p_bind_type_tbl
2329 ) IS
2330
2331 l_api_version CONSTANT NUMBER := G_API_VERSION;
2332 l_msg_count NUMBER := OKL_API.G_MISS_NUM;
2333 l_msg_data VARCHAR2(8000);
2334
2335 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2336 l_agent_id NUMBER;
2337 l_content_id NUMBER;
2338 l_default_subject VARCHAR2(2000);
2339 l_default_sender VARCHAR2(2000);
2340 l_agent_email VARCHAR2(1000);
2341 l_recipient_email recipient_tbl;
2342 l_loop_counter NUMBER;
2343
2344
2345 l_bind_var_tbl p_bind_var_tbl;
2346 l_bind_val_tbl p_bind_val_tbl;
2347 l_bind_type_tbl p_bind_type_tbl;
2348 l_request_id NUMBER;
2349 l_email VARCHAR2(1000);
2350 l_server_id NUMBER;
2351 l_api_name CONSTANT VARCHAR2(300) := 'exe_ful_req';
2352
2353 i number;
2354 j number;
2355 k number;
2356 BEGIN
2357
2358 --Check API version, initialize message list and create savepoint.
2359
2360 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2361 G_PKG_NAME,
2362 p_init_msg_list,
2363 l_api_version,
2364 p_api_version,
2365 '_PVT',
2366 x_return_status);
2367
2368
2369
2370 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2371 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2372 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2373 RAISE OKL_API.G_EXCEPTION_ERROR;
2374 END IF;
2375
2376 -- Check all mandatory parameters have been passed
2377 IF p_ptm_code IS NULL OR p_ptm_code = OKL_API.G_MISS_CHAR THEN
2378 OKC_API.SET_MESSAGE (
2379 p_app_name => 'OKC'
2380 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2381 ,p_token1 => G_COL_NAME_TOKEN
2382 ,p_token1_value => 'P_PTM_CODE');
2383
2384 l_return_status := OKL_API.G_RET_STS_ERROR;
2385 ELSE
2386 g_ptm_code := p_ptm_code;
2387 END IF;
2388
2389 IF p_agent_id IS NULL OR p_agent_id = OKL_API.G_MISS_NUM THEN
2390 OKC_API.SET_MESSAGE (
2391 p_app_name => 'OKC'
2392 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2393 ,p_token1 => G_COL_NAME_TOKEN
2394 ,p_token1_value => 'P_AGENT_ID');
2395
2396 l_return_status := OKL_API.G_RET_STS_ERROR;
2397 END IF;
2398
2399 -- check mandatory parameters for the call type made
2400
2401 IF p_transaction_id IS NOT NULL AND p_transaction_id <> OKL_API.G_MISS_NUM THEN
2402
2403 IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2404
2405 IF p_recipient_type IS NULL OR p_recipient_type = OKL_API.G_MISS_CHAR THEN
2406 OKC_API.SET_MESSAGE (
2407 p_app_name => 'OKC'
2408 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2409 ,p_token1 => G_COL_NAME_TOKEN
2410 ,p_token1_value => 'P_RECIPIENT_TYPE');
2411
2412 l_return_status := OKL_API.G_RET_STS_ERROR;
2413 END IF;
2414
2415 IF p_recipient_id IS NULL OR p_recipient_id = OKL_API.G_MISS_CHAR THEN
2416 OKC_API.SET_MESSAGE (
2417 p_app_name => 'OKC'
2418 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2419 ,p_token1 => G_COL_NAME_TOKEN
2420 ,p_token1_value => 'P_RECIPIENT_ID');
2421
2422 l_return_status := OKL_API.G_RET_STS_ERROR;
2423 END IF;
2424 END IF;
2425 ELSE -- Called from AM Send Fulfillment screen
2426
2427 IF p_pt_bind_names.COUNT > 0 THEN
2428
2429 IF p_pt_bind_names(p_pt_bind_names.FIRST) IS NULL OR p_pt_bind_names(p_pt_bind_names.FIRST)= OKL_API.G_MISS_CHAR THEN
2430 OKC_API.SET_MESSAGE (
2431 p_app_name => 'OKC'
2432 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2433 ,p_token1 => G_COL_NAME_TOKEN
2434 ,p_token1_value => 'P_PT_BIND_NAMES');
2435
2436 l_return_status := OKL_API.G_RET_STS_ERROR;
2437 END IF;
2438 ELSE
2439
2440 OKC_API.SET_MESSAGE (
2441 p_app_name => 'OKC'
2442 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2443 ,p_token1 => G_COL_NAME_TOKEN
2444 ,p_token1_value => 'P_TRANSACTION_ID');
2445
2446 l_return_status := OKL_API.G_RET_STS_ERROR;
2447 END IF;
2448
2449 IF p_recipient_email IS NULL OR p_recipient_email = OKL_API.G_MISS_CHAR THEN
2450 OKC_API.SET_MESSAGE (
2451 p_app_name => 'OKC'
2452 ,p_msg_name => OKC_API.G_REQUIRED_VALUE
2453 ,p_token1 => G_COL_NAME_TOKEN
2454 ,p_token1_value => 'P_RECIPIENT_EMAIL');
2455
2456 l_return_status := OKL_API.G_RET_STS_ERROR;
2457 END IF;
2458
2459 END IF;
2460
2461 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2462 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2463 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2464 RAISE OKL_API.G_EXCEPTION_ERROR;
2465 END IF;
2466
2467 -- validate content and get default subject line
2468 get_content_id( p_ptm_code => p_ptm_code,
2469 x_content_id => l_content_id,
2470 x_subject => l_default_subject,
2471 x_return_status => l_return_status,
2472 x_msg_count => l_msg_count,
2473 x_msg_data => l_msg_data );
2474
2475
2476 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2477 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2478 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2479 RAISE OKL_API.G_EXCEPTION_ERROR;
2480 END IF;
2481
2482 -- Check if a subject line is given, if not use the default line returned from get_content_id
2483 IF length(trim(p_subject_line)) = 0 OR trim(p_subject_line) <> OKL_API.G_MISS_CHAR THEN
2484 l_default_subject := p_subject_line;
2485 END IF;
2486
2487 -- get agent id, from agents email
2488 get_agent_details( x_agent_id => l_agent_id,
2489 p_agent_id => p_agent_id,
2490 x_email => l_agent_email,
2491 x_server_id => l_server_id,
2492 x_return_status => l_return_status,
2493 x_msg_count => l_msg_count,
2494 x_msg_data => l_msg_data);
2495
2496 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2497 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2498 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2499 RAISE OKL_API.G_EXCEPTION_ERROR;
2500 END IF;
2501
2502
2503 -- Check if a sender email is given, if not use the email returned from get_agent_details
2504 IF length(trim(p_sender_email))= 0 OR trim(p_sender_email) <> OKL_API.G_MISS_CHAR THEN
2505 l_agent_email := p_sender_email;
2506 END IF;
2507
2508
2509 IF p_recipient_email IS NOT NULL AND p_recipient_email <> OKL_API.G_MISS_CHAR THEN
2510
2511 l_recipient_email(1) := p_recipient_email;
2512
2513 ELSE
2514 -- get recipient(s) email
2515 get_recipient_details (
2516 p_recipient_id => p_recipient_id,
2517 p_recipient_type => p_recipient_type,
2518 p_expand_roles => p_expand_roles,
2519 x_email => l_recipient_email,
2520 x_return_status => l_return_status,
2521 x_msg_count => l_msg_count,
2522 x_msg_data => l_msg_data);
2523
2524 END IF;
2525
2526 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2527 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2528 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2529 RAISE OKL_API.G_EXCEPTION_ERROR;
2530 END IF;
2531
2532 -- compile fulfillment bind parameters
2533
2534 IF p_pt_bind_names.COUNT > 0 AND p_pt_bind_values.COUNT > 0 AND p_pt_bind_types.COUNT > 0 THEN
2535
2536 i := p_pt_bind_names.FIRST;
2537 j := p_pt_bind_values.FIRST;
2538 k := p_pt_bind_types.FIRST;
2539
2540 IF p_pt_bind_names(i) <> OKL_API.G_MISS_CHAR AND p_pt_bind_names(i) IS NOT NULL
2541 AND p_pt_bind_values(j) <> OKL_API.G_MISS_CHAR AND p_pt_bind_values(j) IS NOT NULL
2542 AND p_pt_bind_types(k) <> OKL_API.G_MISS_CHAR AND p_pt_bind_types(k) IS NOT NULL THEN
2543 l_bind_var_tbl := p_pt_bind_names;
2544 l_bind_val_tbl := p_pt_bind_values;
2545 l_bind_type_tbl := p_pt_bind_types;
2546
2547 ELSE
2548 l_bind_var_tbl(1) := 'p_id';
2549 l_bind_val_tbl(1) := to_char(p_transaction_id);
2550 l_bind_type_tbl(1) := 'NUMBER';
2551 END IF;
2552 ELSE
2553
2554 l_bind_var_tbl(1) := 'p_id';
2555 l_bind_val_tbl(1) := to_char(p_transaction_id);
2556 l_bind_type_tbl(1) := 'NUMBER';
2557 END IF;
2558
2559 -- loop if more than 0 recipient
2560 IF (l_recipient_email.COUNT > 0) THEN
2561
2562 l_loop_counter := l_recipient_email.FIRST;
2563
2564 l_email := l_recipient_email(l_loop_counter);
2565
2566 LOOP
2567
2568 okl_fulfillment_pvt.create_fulfillment(
2569 p_api_version => p_api_version,
2570 p_init_msg_list => FND_API.G_FALSE,
2571 p_agent_id => l_agent_id,
2572 p_server_id => l_server_id,
2573 p_content_id => l_content_id,
2574 p_from => l_agent_email,
2575 p_subject => l_default_subject,
2576 p_email => l_email,
2577 p_bind_var => l_bind_var_tbl,
2578 p_bind_val => l_bind_val_tbl,
2579 p_bind_var_type => l_bind_type_tbl,
2580 p_commit => FND_API.G_FALSE,
2581 x_request_id => l_request_id,
2582 x_return_status => l_return_status,
2583 x_msg_count => l_msg_count,
2584 x_msg_data => l_msg_data);
2585
2586
2587 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2588
2589 OKL_API.set_message( p_app_name => 'OKL',
2590 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2591 p_token1 => 'PTM_MEANING',
2592 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2593 p_lookup_code => g_ptm_code
2594 )
2595 );
2596
2597 OKL_API.set_message(
2598 p_app_name => 'OKL',
2599 p_msg_name => 'OKL_AM_UNEXP_FM_ERROR',
2600 p_token1 => 'EMAIL',
2601 p_token1_value => l_recipient_email(l_loop_counter));
2602
2603 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604
2605 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2606
2607 OKL_API.set_message( p_app_name => 'OKL',
2608 p_msg_name => 'OKL_AM_FM_DEFAULT_ERROR',
2609 p_token1 => 'PTM_MEANING',
2610 p_token1_value => get_lookup_meaning(p_lookup_type => 'OKL_PROCESSES',
2611 p_lookup_code => g_ptm_code
2612 )
2613 );
2614 OKL_API.set_message(
2615 p_app_name => 'OKL',
2616 p_msg_name => 'OKL_AM_EXP_FM_ERROR',
2617 p_token1 => 'EMAIL',
2618 p_token1_value => l_recipient_email(l_loop_counter));
2619
2620 RAISE OKL_API.G_EXCEPTION_ERROR;
2621 END IF;
2622
2623 EXIT WHEN (l_loop_counter = l_recipient_email.LAST);
2624 l_loop_counter := l_recipient_email.NEXT(l_loop_counter );
2625 l_email := l_recipient_email(l_loop_counter);
2626 END LOOP;
2627 END IF;
2628
2629 x_return_status:= l_return_status;
2630 x_msg_count := l_msg_count;
2631 x_msg_data := l_msg_data;
2632
2633 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2634
2635 EXCEPTION
2636 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2637 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2638 (
2639 l_api_name,
2640 G_PKG_NAME,
2641 'OKL_API.G_RET_STS_ERROR',
2642 x_msg_count,
2643 x_msg_data,
2644 '_PVT'
2645 );
2646 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2647 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2648 (
2649 l_api_name,
2650 G_PKG_NAME,
2651 'OKL_API.G_RET_STS_UNEXP_ERROR',
2652 x_msg_count,
2653 x_msg_data,
2654 '_PVT'
2655 );
2656 WHEN OTHERS THEN
2657 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2658 (
2659 l_api_name,
2660 G_PKG_NAME,
2661 'OTHERS',
2662 x_msg_count,
2663 x_msg_data,
2664 '_PVT'
2665 );
2666
2667 END EXECUTE_FULFILLMENT_REQUEST;
2668 -- End Fulfillment Specific
2669
2670
2671 /* Procedure add_view populates the global table for checking lengths.
2672 x_return_status has 'S' if successful else 'E'
2673 */
2674 ----------------------------------------------------------------------------
2675 -- Procedure to add a view for checking length into global table
2676 ----------------------------------------------------------------------------
2677 Procedure add_view(
2678 p_view_name IN VARCHAR2,
2679 x_return_status OUT NOCOPY VARCHAR2) IS
2680 cursor av_csr is select table_name,Column_Name ,data_type,data_length,NVL(data_precision,OKC_API.G_MISS_NUM)
2681 data_precision,NVL(data_scale,0) data_scale
2682 FROM user_tab_columns
2683 WHERE table_name = UPPER( p_view_name) and (data_type='VARCHAR2' OR data_type='NUMBER');
2684 var1 av_csr%rowtype;
2685 i number:=1;
2686 found Boolean:=FALSE;
2687 Begin
2688 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2689 i:=G_lenchk_tbl.First;
2690 If G_lenchk_tbl.Count>0 Then
2691 Loop
2692 if (UPPER(p_view_name)=UPPER(G_lenchk_tbl(i).vname)) Then
2693 found:=TRUE;
2694 Exit;
2695 End if;
2696 Exit when i=G_lenchk_tbl.Last;
2697 i:=G_lenchk_tbl.Next(i);
2698 End Loop;
2699 End if;
2700 If NOT found Then
2701 OPEN av_csr;
2702 i:=G_lenchk_tbl.count;
2703 LOOP
2704 FETCH av_csr into var1;
2705 EXIT WHEN av_csr%NOTFOUND;
2706 i:=i+1;
2707 G_lenchk_tbl(i).vname:=var1.table_name;
2708 G_lenchk_tbl(i).cname:=var1.column_name;
2709 G_lenchk_tbl(i).cdtype:=var1.data_type;
2710 if var1.data_type='NUMBER' Then
2711 G_lenchk_tbl(i).clength:=var1.data_precision;
2712 G_lenchk_tbl(i).cscale:=var1.data_scale;
2713 else
2714 G_lenchk_tbl(i).clength:=var1.data_length;
2715 end if;
2716 END LOOP;
2717 If av_csr%ROWCOUNT<1 Then
2718 x_return_status:=OKC_API.G_RET_STS_ERROR;
2719 OKC_API.SET_MESSAGE(p_app_name => G_APP2_NAME,
2720 p_msg_name => G_NOTFOUND,
2721 p_token1 => G_VIEW_TOKEN,
2722 p_token1_value => UPPER(p_view_name));
2723
2724 End If;
2725
2726 CLOSE av_csr;
2727 End If;
2728
2729 Exception
2730 when others then
2731 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
2732 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2733 p_msg_name => G_UNEXPECTED_ERROR,
2734 p_token1 => G_SQLCODE_TOKEN,
2735 p_token1_value => sqlcode,
2736 p_token2 => G_SQLERRM_TOKEN,
2737 p_token2_value => sqlerrm);
2738
2739 End add_view;
2740
2741
2742 ----------------------------------------------------------------------------
2743 -- checks length of a number column (private procedure)
2744 ----------------------------------------------------------------------------
2745 Procedure checknumlen(
2746 p_view_name IN VARCHAR2,
2747 p_col_name IN VARCHAR2,
2748 p_col_value IN Number,
2749 x_return_status OUT NOCOPY VARCHAR2,
2750 ind IN Number) IS
2751 i Number:=ind;
2752 l_pre Number :=0;
2753 l_scale Number :=0;
2754 l_str_pos Varchar2(40):='';
2755 l_pos Number :=0;
2756 l_neg Number :=0;
2757 l_value Number :=0;
2758 l_val varchar2(64):='.';
2759 cursor c1 is select value from v$nls_parameters where parameter='NLS_NUMERIC_CHARACTERS';
2760 Begin
2761 -- get the character specified for decimal right now in the database
2762 open c1;
2763 fetch c1 into l_val;
2764 close c1;
2765 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
2766 l_value:=NVL(p_col_value,0);
2767 IF (G_lenchk_tbl(i).clength=OKC_API.G_MISS_NUM) Then
2768 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2769 ELSE
2770 l_pre:=G_lenchk_tbl(i).clength-ABS(G_lenchk_tbl(i).cscale);
2771 for j in 1..l_pre loop
2772 l_str_pos:=l_str_pos||'9';
2773 end loop;
2774 l_scale:=G_lenchk_tbl(i).cscale;
2775 If (l_scale>0) Then
2776 --l_str_pos:=l_str_pos||'.';
2777 l_str_pos:=l_str_pos||substr(l_val,1,1);
2778 for j in 1..l_scale loop
2779 l_str_pos:=l_str_pos||'9';
2780 end loop;
2781 ElsIf (l_scale<0) Then
2782 for j in 1..ABS(l_scale) loop
2783 l_str_pos:=l_str_pos||'0';
2784 end loop;
2785 end if;
2786 l_pos:=to_number(l_str_pos);
2787 l_neg:=(-1)*l_pos;
2788 if l_value<=l_pos and l_value>=l_neg then
2789 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2790 else
2791 x_return_status:=OKC_API.G_RET_STS_ERROR;
2792 OKC_API.SET_MESSAGE(p_app_name => G_APP2_NAME,
2793 p_msg_name => G_LEN_CHK,
2794 p_token1 => G_COL_NAME_TOKEN,
2795 p_token1_value => p_col_name,
2796 p_token2 => 'COL_LEN',
2797 p_token2_value => G_lenchk_tbl(i).clength||','||ABS(G_lenchk_tbl(i).cscale));
2798 end if;
2799 End If;
2800 EXCEPTION
2801 when others then
2802 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2803 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2804 p_msg_name => G_UNEXPECTED_ERROR,
2805 p_token1 => G_SQLCODE_TOKEN,
2806 p_token1_value => sqlcode,
2807 p_token2 => G_SQLERRM_TOKEN,
2808 p_token2_value => sqlerrm);
2809 End checknumlen;
2810
2811
2812 /* Procedure check_length checks the length of the passed in value of column
2813 x_return_status has 'S' if length is less than or equal to maximum length for that column
2814 x_return_status has 'E' if length is more than maximum length for that column
2815 x_return_status has 'U' if it cannot find the column in the global table populated trough add_view
2816 */
2817 ----------------------------------------------------------------------------
2818 -- checks length of a varchar2 column
2819 ----------------------------------------------------------------------------
2820 Procedure check_length(
2821 p_view_name IN VARCHAR2,
2822 p_col_name IN VARCHAR2,
2823 p_col_value IN VARCHAR2,
2824 x_return_status OUT NOCOPY VARCHAR2) IS
2825 i number:=0;
2826 col_len number:=0;
2827 Begin
2828 x_return_status:=OKC_API.G_RET_STS_UNEXP_ERROR;
2829 i:=G_lenchk_tbl.First;
2830 Loop
2831 if ((UPPER(p_view_name)=UPPER(G_lenchk_tbl(i).vname)) and
2832 (UPPER(p_col_name)=UPPER(G_lenchk_tbl(i).cname)) ) Then
2833 If (UPPER(G_lenchk_tbl(i).cdtype)='VARCHAR2') Then
2834 col_len:=nvl(length(p_col_value),0);
2835 if col_len<=TRUNC((G_lenchk_tbl(i).CLength)/3) then
2836 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
2837 else
2838 x_return_status:= OKC_API.G_RET_STS_ERROR;
2839 OKC_API.SET_MESSAGE(p_app_name => G_APP2_NAME,
2840 p_msg_name => G_LEN_CHK,
2841 p_token1 => G_COL_NAME_TOKEN,
2842 p_token1_value => p_col_name,
2843 p_token2 => 'COL_LEN',
2844 p_token2_value => '('||trunc((G_lenchk_tbl(i).clength)/3)||')');
2845 end if;
2846 ElsIf (UPPER(G_lenchk_tbl(i).cdtype)='NUMBER') Then
2847 checknumlen(p_view_name,p_col_name,to_number(p_col_value),x_return_status,i);
2848
2849 End If;
2850 Exit;
2851 End if;
2852 Exit when i=G_lenchk_tbl.Last;
2853 i:=G_lenchk_tbl.Next(i);
2854 End Loop;
2855
2856 EXCEPTION
2857 when others then
2858 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2859
2860 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
2861 p_msg_name => G_UNEXPECTED_ERROR,
2862 p_token1 => G_SQLCODE_TOKEN,
2863 p_token1_value => sqlcode,
2864 p_token2 => G_SQLERRM_TOKEN,
2865 p_token2_value => sqlerrm);
2866 End check_length;
2867
2868
2869 ----------------------------------------------------------------------------
2870 -- checks length of a number column
2871 ----------------------------------------------------------------------------
2872 Procedure check_length(
2873 p_view_name IN VARCHAR2,
2874 p_col_name IN VARCHAR2,
2875 p_col_value IN NUMBER,
2876 x_return_status OUT NOCOPY VARCHAR2) IS
2877 Begin
2878 check_length(p_view_name,p_col_name, to_char(p_col_value) , x_return_status);
2879 End check_length;
2880
2881
2882 -- Start of comments
2883 --
2884 -- Function Name : get_wf_event_name
2885 -- Description : Get the event name for the workflow
2886 -- Business Rules :
2887 -- Parameters : p_wf_process_type -- 8 letter shortcode of the WF process
2888 -- p_wf_process_name -- internal name of the WF process
2889 -- Version : 1.0
2890 --
2891 -- End of comments
2892 FUNCTION get_wf_event_name(
2893 p_wf_process_type IN VARCHAR2,
2894 p_wf_process_name IN VARCHAR2,
2895 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 AS
2896
2897 -- Cursor to get the event name of WF
2898 CURSOR okl_get_event_name_csr ( p_process_type IN VARCHAR2,
2899 p_process_name IN VARCHAR2) IS
2900 SELECT WFEV.display_name
2901 FROM WF_EVENTS_VL WFEV,
2902 WF_EVENT_SUBSCRIPTIONS WFES
2903 WHERE WFEV.guid = WFES.event_filter_guid
2904 AND WFES.wf_process_type = p_process_type
2905 AND UPPER(WFES.wf_process_name) = UPPER(p_process_name);
2906
2907 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2908 l_name VARCHAR2(200);
2909
2910 BEGIN
2911
2912 OPEN okl_get_event_name_csr ( p_wf_process_type, p_wf_process_name);
2913 FETCH okl_get_event_name_csr INTO l_name;
2914 CLOSE okl_get_event_name_csr;
2915
2916 x_return_status := l_return_status;
2917
2918 RETURN l_name;
2919
2920 EXCEPTION
2921 WHEN OTHERS THEN
2922 IF okl_get_event_name_csr%ISOPEN THEN
2923 CLOSE okl_get_event_name_csr;
2924 END IF;
2925
2926 OKL_API.set_message(p_app_name => g_app_name,
2927 p_msg_name => g_unexpected_error,
2928 p_token1 => g_sqlcode_token,
2929 p_token1_value => sqlcode,
2930 p_token2 => g_sqlerrm_token,
2931 p_token2_value => sqlerrm);
2932
2933 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2934
2935 x_return_status := l_return_status;
2936
2937 RETURN l_return_status;
2938
2939 END get_wf_event_name;
2940
2941
2942
2943
2944 -- Start of comments
2945 --
2946 -- Function Name : get_contract_quotes
2947 -- Description : Get the accepted quotes for the contract
2948 -- Business Rules :
2949 -- Parameters : p_khr_id -- contract id
2950 -- x_quote_tbl -- Quote details table
2951 -- x_return_status -- return status
2952 -- History : RMUNJULU -- Bug # 2484327 Created
2953 -- Version : 1.0
2954 --
2955 -- End of comments
2956 PROCEDURE get_contract_quotes (
2957 p_khr_id IN NUMBER,
2958 x_quote_tbl OUT NOCOPY quote_tbl_type,
2959 x_return_status OUT NOCOPY VARCHAR2) IS
2960
2961
2962 -- Get the accepted quotes for the contract -- both full and partial
2963 CURSOR get_qte_csr ( p_khr_id IN NUMBER ) IS
2964 SELECT QTE.id id,
2965 QTE.quote_number quote_number,
2966 KHR.contract_number contract_number,
2967 QTE.partial_yn partial_yn,
2968 QTE.qst_code qst_code,
2969 QTE.qtp_code qtp_code
2970 FROM OKL_TRX_QUOTES_V QTE,
2971 OKC_K_HEADERS_V KHR
2972 WHERE QTE.khr_id = KHR.id
2973 AND NVL(QTE.accepted_yn,'N') = 'Y'
2974 AND NVL(QTE.consolidated_yn,'N') = 'N' -- non consolidated
2975 AND QTE.khr_id = p_khr_id;
2976
2977
2978
2979
2980 l_quote_tbl quote_tbl_type;
2981 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2982 i NUMBER;
2983
2984
2985 BEGIN
2986
2987
2988 i := 1;
2989
2990 --Loop thru the quotes for the contract and set the quote_tbl
2991 FOR get_qte_rec IN get_qte_csr(p_khr_id) LOOP
2992
2993 l_quote_tbl(i).id := get_qte_rec.id;
2994 l_quote_tbl(i).quote_number := get_qte_rec.quote_number;
2995 l_quote_tbl(i).contract_number := get_qte_rec.contract_number;
2996 l_quote_tbl(i).partial_yn := get_qte_rec.partial_yn;
2997 l_quote_tbl(i).qst_code := get_qte_rec.qst_code;
2998 l_quote_tbl(i).qtp_code := get_qte_rec.qtp_code;
2999
3000 i := i + 1;
3001
3002 END LOOP;
3003
3004 -- Set the return variables
3005 x_quote_tbl := l_quote_tbl;
3006 x_return_status := l_return_status;
3007
3008 EXCEPTION
3009
3010 WHEN OTHERS THEN
3011
3012 IF get_qte_csr%ISOPEN THEN
3013 CLOSE get_qte_csr;
3014 END IF;
3015
3016 OKL_API.set_message(p_app_name => g_app_name,
3017 p_msg_name => g_unexpected_error,
3018 p_token1 => g_sqlcode_token,
3019 p_token1_value => sqlcode,
3020 p_token2 => g_sqlerrm_token,
3021 p_token2_value => sqlerrm);
3022
3023 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3024
3025
3026 END get_contract_quotes;
3027
3028
3029 -- Start of comments
3030 --
3031 -- Function Name : get_line_quotes
3032 -- Description : Get the accepted quotes for the asset
3033 -- Business Rules :
3034 -- Parameters : p_kle_id -- Line id
3035 -- x_quote_tbl -- Quote details table
3036 -- x_return_status -- return status
3037 -- History : RMUNJULU -- Bug # 2484327 Created
3038 -- Version : 1.0
3039 --
3040 -- End of comments
3041 PROCEDURE get_line_quotes (
3042 p_kle_id IN NUMBER,
3043 x_quote_tbl OUT NOCOPY quote_tbl_type,
3044 x_return_status OUT NOCOPY VARCHAR2) IS
3045
3046
3047 -- Get the accepted quotes for the asset -- both full and partial
3048 CURSOR get_qte_csr ( p_kle_id IN NUMBER ) IS
3049 SELECT QTE.id id,
3050 QTE.quote_number quote_number,
3051 KHR.contract_number contract_number,
3052 QTE.partial_yn partial_yn,
3053 QTE.qst_code qst_code,
3054 QTE.qtp_code qtp_code
3055 FROM OKL_TRX_QUOTES_V QTE,
3056 OKL_TXL_QUOTE_LINES_V TQL,
3057 OKC_K_LINES_V KLE,
3058 OKC_K_HEADERS_V KHR
3059 WHERE TQL.kle_id = KLE.id
3060 AND TQL.qte_id = QTE.id
3061 AND KLE.chr_id = KHR.id
3062 AND TQL.qlt_code = 'AMCFIA'
3063 AND NVL(QTE.accepted_yn,'N') = 'Y'
3064 AND NVL(QTE.consolidated_yn,'N') = 'N' -- non consolidated
3065 AND TQL.kle_id = p_kle_id;
3066
3067
3068 l_quote_tbl quote_tbl_type;
3069 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3070 i NUMBER;
3071
3072
3073 BEGIN
3074
3075
3076 i := 1;
3077
3078 --Loop thru the quotes for the asset and set the quote_tbl
3079 FOR get_qte_rec IN get_qte_csr(p_kle_id) LOOP
3080
3081 l_quote_tbl(i).id := get_qte_rec.id;
3082 l_quote_tbl(i).quote_number := get_qte_rec.quote_number;
3083 l_quote_tbl(i).contract_number := get_qte_rec.contract_number;
3084 l_quote_tbl(i).partial_yn := get_qte_rec.partial_yn;
3085 l_quote_tbl(i).qst_code := get_qte_rec.qst_code;
3086 l_quote_tbl(i).qtp_code := get_qte_rec.qtp_code;
3087
3088 i := i + 1;
3089
3090 END LOOP;
3091
3092 -- Set the return variables
3093 x_quote_tbl := l_quote_tbl;
3094 x_return_status := l_return_status;
3095
3096
3097 EXCEPTION
3098
3099 WHEN OTHERS THEN
3100
3101 IF get_qte_csr%ISOPEN THEN
3102 CLOSE get_qte_csr;
3103 END IF;
3104
3105 OKL_API.set_message(p_app_name => g_app_name,
3106 p_msg_name => g_unexpected_error,
3107 p_token1 => g_sqlcode_token,
3108 p_token1_value => sqlcode,
3109 p_token2 => g_sqlerrm_token,
3110 p_token2_value => sqlerrm);
3111
3112 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3113
3114 END get_line_quotes;
3115
3116
3117
3118
3119 -- Start of comments
3120 --
3121 -- Function Name : get_contract_transactions
3122 -- Description : Get unprocessed termination transactions for the contract
3123 -- Business Rules :
3124 -- Parameters : p_khr_id -- Contract id
3125 -- x_trn_tbl -- transactions details table
3126 -- x_return_status -- return status
3127 -- History : RMUNJULU -- Bug # 2484327 Created
3128 -- nikshah -- Bug # 5484903 Fixed,
3129 -- Changed CURSOR get_trn_csr ( p_khr_id IN NUMBER ) SQL definition
3130 -- akrangan - Changed tsu_code to tmt_status_code in get_trn_csr
3131 -- Version : 1.0
3132 --
3133 -- End of comments
3134 PROCEDURE get_contract_transactions (
3135 p_khr_id IN NUMBER,
3136 x_trn_tbl OUT NOCOPY trn_tbl_type,
3137 x_return_status OUT NOCOPY VARCHAR2) IS
3138
3139
3140
3141 -- Get the unprocessed termination transactions for the contract -- both full and partial
3142 CURSOR get_trn_csr ( p_khr_id IN NUMBER ) IS
3143 select tcn.id id,
3144 tcn.trx_number trx_number,
3145 tcn.tmt_status_code tsu_code, --akrangan changed tsu_code to tmt_status_code
3146 tcn.tcn_type tcn_type,
3147 khr.contract_number contract_number,
3148 qteb.quote_number quote_number,
3149 qteb.partial_yn partial_yn,
3150 qteb.qst_code qst_code,
3151 qteb.qtp_code qtp_code
3152 from okl_trx_contracts tcn,
3153 okl_trx_quotes_all_b qteb,
3154 okl_trx_quotes_tl qte,
3155 okc_k_headers_all_b khr
3156 where tcn.khr_id = khr.id
3157 and tcn.qte_id = qteb.id(+)
3158 and qteb.id = qte.id(+)
3159 and qte.language(+) = userenv('LANG')
3160 and tcn.tcn_type in ('TMT','ALT','EVG')-- akrangan bug 5354501 fix added 'EVG'
3161 and tcn.tmt_status_code not in ('CANCELED','PROCESSED') --akrangan changed
3162 --tsu_code to tmt_status code
3163 and tcn.khr_id = p_khr_id
3164 --rkuttiya added for 12.1.1 multi gaap project
3165 and tcn.representation_type = 'PRIMARY';
3166 --
3167
3168
3169 l_trn_tbl trn_tbl_type;
3170 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3171 i NUMBER;
3172
3173
3174 BEGIN
3175
3176
3177 i := 1;
3178
3179 --Loop thru the quotes for the contract and set the trn_tbl
3180 FOR get_trn_rec IN get_trn_csr(p_khr_id) LOOP
3181
3182 l_trn_tbl(i).id := get_trn_rec.id;
3183 l_trn_tbl(i).trx_number := get_trn_rec.trx_number;
3184 l_trn_tbl(i).tsu_code := get_trn_rec.tsu_code;
3185 l_trn_tbl(i).tcn_type := get_trn_rec.tcn_type;
3186 l_trn_tbl(i).contract_number := get_trn_rec.contract_number;
3187 l_trn_tbl(i).quote_number := get_trn_rec.quote_number;
3188 l_trn_tbl(i).partial_yn := get_trn_rec.partial_yn;
3189 l_trn_tbl(i).qst_code := get_trn_rec.qst_code;
3190 l_trn_tbl(i).qtp_code := get_trn_rec.qtp_code;
3191
3192 i := i + 1;
3193
3194 END LOOP;
3195
3196 -- Set the return variables
3197 x_trn_tbl := l_trn_tbl;
3198 x_return_status := l_return_status;
3199
3200
3201 EXCEPTION
3202
3203 WHEN OTHERS THEN
3204
3205 IF get_trn_csr%ISOPEN THEN
3206 CLOSE get_trn_csr;
3207 END IF;
3208
3209 OKL_API.set_message(p_app_name => g_app_name,
3210 p_msg_name => g_unexpected_error,
3211 p_token1 => g_sqlcode_token,
3212 p_token1_value => sqlcode,
3213 p_token2 => g_sqlerrm_token,
3214 p_token2_value => sqlerrm);
3215
3216 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3217
3218 END get_contract_transactions;
3219
3220
3221
3222
3223 -- Start of comments
3224 --
3225 -- Function Name : get_line_transactions
3226 -- Description : Get unprocessed termination transactions for asset
3227 -- transactions for the Line
3228 -- Business Rules :
3229 -- Parameters : p_kle_id -- Line id
3230 -- x_trn_tbl -- transactions details table
3231 -- x_return_status -- return status
3232 -- History : RMUNJULU -- Bug # 2484327 Created
3233 -- AKRANGAN -- Changed tssu_code to tmt_status_cde in get_trn_csr
3234 -- Version : 1.0
3235 --
3236 -- End of comments
3237 PROCEDURE get_line_transactions (
3238 p_kle_id IN NUMBER,
3239 x_trn_tbl OUT NOCOPY trn_tbl_type,
3240 x_return_status OUT NOCOPY VARCHAR2) IS
3241
3242
3243 -- Get the unprocessed termination transactions for the asset
3244 CURSOR get_trn_csr ( p_kle_id IN NUMBER ) IS
3245 SELECT TCN.id id,
3246 TCN.trx_number trx_number,
3247 TCN.tmt_status_code tsu_code, --akrangan changed tsu_code to tmt_status_code
3248 TCN.tcn_type tcn_type,
3249 QTE.quote_number quote_number,
3250 QTE.partial_yn partial_yn,
3251 QTE.qst_code qst_code,
3252 QTE.qtp_code qtp_code
3253 FROM OKL_TRX_CONTRACTS TCN,
3254 OKL_TRX_QUOTES_B QTE,
3255 OKL_TXL_QUOTE_LINES_B TQL
3256 WHERE TCN.qte_id = QTE.id
3257 AND TQL.qte_id = QTE.id
3258 AND TCN.tcn_type IN ('TMT','ALT' , 'EVG')-- akrangan bug 5354501 fix added 'EVG'
3259 --rkuttiya added for 12.1.1 multi gaap project
3260 AND TCN.representation_type = 'PRIMARY'
3261 --
3262 AND TQL.qlt_code = 'AMCFIA'
3263 AND TCN.tmt_status_code NOT IN ( 'PROCESSED','CANCELED') --akrangan changed tsu_code to tmt_status_code
3264 AND TQL.kle_id = p_kle_id;
3265
3266
3267 l_trn_tbl trn_tbl_type;
3268 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3269 i NUMBER;
3270
3271 BEGIN
3272
3273
3274 i := 1;
3275
3276 --Loop thru the quotes for the asset and set the quote_tbl
3277 FOR get_trn_rec IN get_trn_csr(p_kle_id) LOOP
3278
3279 l_trn_tbl(i).id := get_trn_rec.id;
3280 l_trn_tbl(i).trx_number := get_trn_rec.trx_number;
3281 l_trn_tbl(i).tsu_code := get_trn_rec.tsu_code;
3282 l_trn_tbl(i).tcn_type := get_trn_rec.tcn_type;
3283 l_trn_tbl(i).quote_number := get_trn_rec.quote_number;
3284 l_trn_tbl(i).partial_yn := get_trn_rec.partial_yn;
3285 l_trn_tbl(i).qst_code := get_trn_rec.qst_code;
3286 l_trn_tbl(i).qtp_code := get_trn_rec.qtp_code;
3287
3288 i := i + 1;
3289
3290
3291 END LOOP;
3292
3293 -- Set the return variables
3294 x_trn_tbl := l_trn_tbl;
3295 x_return_status := l_return_status;
3296
3297
3298 EXCEPTION
3299
3300 WHEN OTHERS THEN
3301
3302 IF get_trn_csr%ISOPEN THEN
3303 CLOSE get_trn_csr;
3304 END IF;
3305
3306 OKL_API.set_message(p_app_name => g_app_name,
3307 p_msg_name => g_unexpected_error,
3308 p_token1 => g_sqlcode_token,
3309 p_token1_value => sqlcode,
3310 p_token2 => g_sqlerrm_token,
3311 p_token2_value => sqlerrm);
3312
3313 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3314
3315 END get_line_transactions;
3316
3317
3318
3319 -- Start of comments
3320 --
3321 -- Function Name : get_non_trn_contract_quotes
3322 -- Description : Get accepted non transaction quotes for the Contract
3323 -- Business Rules :
3324 -- Parameters : p_khr_id -- Contract id
3325 -- x_quote_tbl -- quote details table
3326 -- x_return_status -- return status
3327 -- History : RMUNJULU -- Bug # 2484327 Created
3328 -- : RMUNJULU 15-MAR-04 3485854 Added code to reset the l_id value in loop
3329 -- Version : 1.0
3330 --
3331 -- End of comments
3332 PROCEDURE get_non_trn_contract_quotes (
3333 p_khr_id IN NUMBER,
3334 x_quote_tbl OUT NOCOPY quote_tbl_type,
3335 x_return_status OUT NOCOPY VARCHAR2) IS
3336
3337
3338 -- Get the accepted quotes for the contract -- both full and partial
3339 CURSOR get_qte_csr ( p_khr_id IN NUMBER ) IS
3340 SELECT QTE.id id,
3341 QTE.quote_number quote_number,
3342 KHR.contract_number contract_number,
3343 QTE.partial_yn partial_yn,
3344 QTE.qst_code qst_code,
3345 QTE.qtp_code qtp_code
3346 FROM OKL_TRX_QUOTES_V QTE,
3347 OKC_K_HEADERS_V KHR
3348 WHERE QTE.khr_id = KHR.id
3349 AND NVL(QTE.accepted_yn,'N') = 'Y'
3350 AND QTE.qtp_code LIKE 'TER%' -- ansethur 07-aug-2007 Added for bug 5932098
3351 AND NVL(QTE.consolidated_yn,'N') = 'N' -- non consolidated
3352 AND QTE.khr_id = p_khr_id;
3353
3354
3355 -- Get the transaction for the quote -- both full and partial
3356 CURSOR get_trn_csr ( p_qte_id IN NUMBER) IS
3357 SELECT TRX.id id
3358 FROM OKL_TRX_CONTRACTS TRX
3359 WHERE TRX.tcn_type IN ('TMT','ALT' , 'EVG')-- akrangan bug 5354501 fix added 'EVG'
3360 --rkuttiya added for 12.1.1 multi gaap
3361 AND TRX.representation_type = 'PRIMARY'
3362 --
3363 AND TRX.qte_id = p_qte_id;
3364
3365
3366 l_quote_tbl quote_tbl_type;
3367 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3368 i NUMBER;
3369 l_id NUMBER := -9999;
3370
3371 BEGIN
3372
3373
3374 i := 1;
3375
3376 --Loop thru the quotes for the contract
3377 FOR get_qte_rec IN get_qte_csr(p_khr_id) LOOP
3378
3379 -- RMUNJULU 15-MAR-04 3485854 reset the l_id value in loop
3380 l_id := -9999;
3381
3382 -- get the transaction for the quote
3383 OPEN get_trn_csr(get_qte_rec.id);
3384 FETCH get_trn_csr INTO l_id;
3385 CLOSE get_trn_csr;
3386
3387
3388 -- If no transaction present for the quote then set it in the quote_tbl
3389 IF l_id IS NULL OR l_id = -9999 THEN
3390
3391 l_quote_tbl(i).id := get_qte_rec.id;
3392 l_quote_tbl(i).quote_number := get_qte_rec.quote_number;
3393 l_quote_tbl(i).contract_number := get_qte_rec.contract_number;
3394 l_quote_tbl(i).partial_yn := get_qte_rec.partial_yn;
3395 l_quote_tbl(i).qst_code := get_qte_rec.qst_code;
3396 l_quote_tbl(i).qtp_code := get_qte_rec.qtp_code;
3397
3398 i := i + 1;
3399
3400 END IF;
3401
3402 END LOOP;
3403
3404 -- Set the return variables
3405 x_quote_tbl := l_quote_tbl;
3406 x_return_status := l_return_status;
3407
3408
3409 EXCEPTION
3410
3411 WHEN OTHERS THEN
3412
3413 IF get_qte_csr%ISOPEN THEN
3414 CLOSE get_qte_csr;
3415 END IF;
3416
3417 IF get_trn_csr%ISOPEN THEN
3418 CLOSE get_trn_csr;
3419 END IF;
3420
3421 OKL_API.set_message(p_app_name => g_app_name,
3422 p_msg_name => g_unexpected_error,
3423 p_token1 => g_sqlcode_token,
3424 p_token1_value => sqlcode,
3425 p_token2 => g_sqlerrm_token,
3426 p_token2_value => sqlerrm);
3427
3428 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3429
3430 END get_non_trn_contract_quotes;
3431
3432
3433
3434 -- Start of comments
3435 --
3436 -- Procedure Name : get_currency_code
3437 -- Description : Returns the currency code for a given ORG
3438 -- Business Rules :
3439 -- Parameters : p_org_id
3440 -- Version : 1.0
3441 -- History : 10-DEC-02 DAPATEL - Created
3442 -- nikshah -- Bug # 5484903 Fixed,
3443 -- Changed CURSOR l_curr_csr (p_org_id NUMBER) SQL definition
3444 -- End of comments
3445
3446 FUNCTION get_currency_code(p_org_id IN NUMBER)
3447 RETURN VARCHAR2 IS
3448
3449 l_curr_code VARCHAR2(15);
3450
3451 -- Get currency code for the set_of_books
3452 CURSOR l_curr_csr (p_org_id NUMBER) IS
3453 SELECT gl.currency_code
3454 FROM GL_LEDGERS_PUBLIC_V gl, HR_ALL_ORGANIZATION_UNITS O, HR_ORGANIZATION_INFORMATION O3
3455 WHERE gl.ledger_id = O3.ORG_INFORMATION3
3456 AND o.organization_id = p_org_id
3457 AND O.ORGANIZATION_ID = O3.ORGANIZATION_ID
3458 AND O3.ORG_INFORMATION_CONTEXT = 'Operating Unit Information';
3459
3460 BEGIN
3461
3462 OPEN l_curr_csr (p_org_id);
3463 FETCH l_curr_csr INTO l_curr_code;
3464 CLOSE l_curr_csr;
3465
3466 RETURN l_curr_code;
3467
3468 EXCEPTION
3469
3470 WHEN OTHERS THEN
3471 IF (l_curr_csr%ISOPEN) THEN
3472 CLOSE l_curr_csr;
3473 END IF;
3474 RETURN (NULL);
3475
3476 END get_currency_code;
3477
3478
3479 -- Start of comments
3480 --
3481 -- Procedure Name : get_functional_currency
3482 -- Description : Returns the functional currency code for the user ORG
3483 -- Business Rules :
3484 -- Parameters :
3485 -- Version : 1.0
3486 -- History : 10-DEC-02 DAPATEL - Created
3487 -- End of comments
3488
3489 FUNCTION get_functional_currency
3490 RETURN VARCHAR2 IS
3491 l_org_id NUMBER;
3492 BEGIN
3493
3494 l_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
3495 RETURN get_currency_code(l_org_id);
3496
3497 EXCEPTION
3498
3499 WHEN OTHERS THEN
3500 RETURN (NULL);
3501
3502 END get_functional_currency;
3503
3504
3505 -- Start of comments
3506 --
3507 -- Procedure Name : get_func_currency_org
3508 -- Description : Return the functional currency code and ORG ID
3509 -- Business Rules :
3510 -- Parameters : Input parameters : p_chr_id
3511 -- : Output parameters : x_org_id, x_currency_code
3512 -- Version : 1.0
3513 -- History : 10-DEC-02 DAPATEL - Created
3514 -- End of comments
3515
3516 PROCEDURE get_func_currency_org(x_org_id OUT NOCOPY NUMBER
3517 ,x_currency_code OUT NOCOPY VARCHAR2) IS
3518
3519 BEGIN
3520
3521 x_org_id := MO_GLOBAL.GET_CURRENT_ORG_ID();
3522 x_currency_code := get_currency_code(x_org_id);
3523
3524 EXCEPTION
3525 WHEN OTHERS THEN
3526 -- unexpected error
3527 OKC_API.set_message(p_app_name => 'OKC',
3528 p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3529 p_token1 => g_sqlcode_token,
3530 p_token1_value => sqlcode,
3531 p_token2 => g_sqlerrm_token,
3532 p_token2_value => sqlerrm);
3533 END get_func_currency_org;
3534
3535
3536 -- Start of comments
3537 --
3538 -- Procedure Name : get_chr_currency_org
3539 -- Description : Return the contract currency code and ORG ID for a given Contract ID
3540 -- Business Rules :
3541 -- Parameters : Input parameters : p_chr_id
3542 -- : Output parameters : x_org_id, x_currency_code
3543 -- Version : 1.0
3544 -- History : 10-DEC-02 DAPATEL - Created
3545 -- End of comments
3546
3547 PROCEDURE get_chr_currency_org(p_chr_id IN NUMBER
3548 ,x_org_id OUT NOCOPY NUMBER
3549 ,x_currency_code OUT NOCOPY VARCHAR2) IS
3550
3551 BEGIN
3552 x_org_id := get_chr_org_id(p_chr_id => p_chr_id);
3553 x_currency_code := get_chr_currency(p_chr_id => p_chr_id);
3554
3555 EXCEPTION
3556 WHEN OTHERS THEN
3557 -- unexpected error
3558 OKC_API.set_message(p_app_name => 'OKC',
3559 p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3560 p_token1 => g_sqlcode_token,
3561 p_token1_value => sqlcode,
3562 p_token2 => g_sqlerrm_token,
3563 p_token2_value => sqlerrm);
3564 END get_chr_currency_org;
3565
3566
3567 -- Start of comments
3568 --
3569 -- Function Name : get_user_profile_option_name
3570 -- Description : This function returns the user profile option name for a profile
3571 -- Business Rules :
3572 -- Parameters : Input parameters : p_profile_option_name
3573 -- : Output parameters : x_return_status
3574 -- Version : 1.0
3575 -- History : 10-DEC-02 DAPATEL - Created
3576 -- End of comments
3577
3578 FUNCTION get_user_profile_option_name(p_profile_option_name IN VARCHAR2,
3579 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
3580
3581 CURSOR l_profileoptionsvl_csr IS
3582 SELECT user_profile_option_name
3583 FROM fnd_profile_options_vl
3584 WHERE profile_option_name = p_profile_option_name;
3585
3586 l_user_profile_name VARCHAR2(240);
3587 BEGIN
3588 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3589
3590 OPEN l_profileoptionsvl_csr;
3591 FETCH l_profileoptionsvl_csr INTO l_user_profile_name;
3592 IF l_profileoptionsvl_csr%NOTFOUND THEN
3593 x_return_status := OKL_API.G_RET_STS_ERROR;
3594 END IF;
3595 CLOSE l_profileoptionsvl_csr;
3596
3597 RETURN l_user_profile_name;
3598
3599 EXCEPTION
3600 WHEN OTHERS THEN
3601
3602 IF l_profileoptionsvl_csr%ISOPEN THEN
3603 CLOSE l_profileoptionsvl_csr;
3604 END IF;
3605 -- unexpected error
3606 OKL_API.set_message(p_app_name => 'OKC',
3607 p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3608 p_token1 => g_sqlcode_token,
3609 p_token1_value => sqlcode,
3610 p_token2 => g_sqlerrm_token,
3611 p_token2_value => sqlerrm);
3612 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3613 RETURN NULL;
3614 END get_user_profile_option_name;
3615
3616 -- Start of comments
3617 --
3618 -- Function Name : convert_to_contract_currency
3619 -- Description : This function converts an amount to the contract currency
3620 -- Business Rules :
3621 -- Parameters : Input parameters : p_khr_id
3622 -- : : p_trx_date
3623 -- : : p_amount
3624 -- : Output parameters: RETURN NUMBER
3625 -- Version : 1.0
3626 -- History : 23-DEC-02 DAPATEL 2667636 Created for multi-currency
3627 -- : 07-FEB-03 DAPATEL 115.47 2780466 - Modified
3628 -- : okl_accounting_util usage to use
3629 -- : new procedures containing error handling.
3630 -- End of comments
3631 FUNCTION convert_to_contract_currency(p_khr_id IN NUMBER
3632 ,p_trx_date IN DATE
3633 ,p_amount IN NUMBER) RETURN NUMBER IS
3634
3635 l_contract_currency VARCHAR2(15);
3636 l_currency_conversion_type VARCHAR2(30);
3637 l_currency_conversion_rate NUMBER;
3638 l_currency_conversion_date DATE;
3639 l_converted_amount NUMBER;
3640 l_return_status VARCHAR2(3);
3641
3642 BEGIN
3643
3644 OKL_ACCOUNTING_UTIL.convert_to_contract_currency
3645 (p_khr_id => p_khr_id,
3646 p_from_currency => NULL,
3647 p_transaction_date => NVL(p_trx_date, sysdate),
3648 p_amount => p_amount,
3649 x_return_status => l_return_status,
3650 x_contract_currency => l_contract_currency,
3651 x_currency_conversion_type => l_currency_conversion_type,
3652 x_currency_conversion_rate => l_currency_conversion_rate,
3653 x_currency_conversion_date => l_currency_conversion_date,
3654 x_converted_amount => l_converted_amount);
3655
3656 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3657 l_converted_amount := 0;
3658 END IF;
3659 RETURN l_converted_amount;
3660
3661 EXCEPTION
3662
3663 WHEN OTHERS THEN
3664
3665 -- unexpected error
3666 OKL_API.set_message(p_app_name => 'OKC',
3667 p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3668 p_token1 => g_sqlcode_token,
3669 p_token1_value => sqlcode,
3670 p_token2 => g_sqlerrm_token,
3671 p_token2_value => sqlerrm);
3672 RETURN 0;
3673 END convert_to_contract_currency;
3674
3675
3676 -- Start of comments
3677 --
3678 -- Function Name : get_all_term_quotes_for_line
3679 -- Description : Get all termination quotes for the asset
3680 -- Business Rules :
3681 -- Parameters : p_kle_id -- Line id
3682 -- x_quote_tbl -- Quote details table
3683 -- x_return_status -- return status
3684 -- History : RMUNJULU 30-DEC-02 2699412 Created
3685 -- nikshah -- Bug # 5484903 Fixed,
3686 -- Changed CURSOR get_qte_csr ( p_kle_id IN NUMBER ) SQL definition
3687 -- Version : 1.0
3688 --
3689 -- End of comments
3690 PROCEDURE get_all_term_quotes_for_line (
3691 p_kle_id IN NUMBER,
3692 x_quote_tbl OUT NOCOPY quote_tbl_type,
3693 x_return_status OUT NOCOPY VARCHAR2) IS
3694
3695
3696 -- Get all non-consolidated quotes for the asset -- both full and partial
3697 CURSOR get_qte_csr ( p_kle_id IN NUMBER ) IS
3698 SELECT QTE.id id,
3699 QTE.quote_number quote_number,
3700 KHR.contract_number contract_number,
3701 QTE.partial_yn partial_yn,
3702 QTE.consolidated_yn consolidated_yn,
3703 QTE.qst_code qst_code,
3704 QTE.qtp_code qtp_code
3705 FROM OKL_TRX_QUOTES_B QTE,
3706 OKL_TXL_QTE_LINES_ALL_B TQL,
3707 OKC_K_LINES_B KLE,
3708 OKC_K_HEADERS_ALL_B KHR
3709 WHERE TQL.kle_id = KLE.id
3710 AND TQL.qte_id = QTE.id
3711 AND KLE.chr_id = KHR.id
3712 AND TQL.qlt_code = 'AMCFIA'
3713 AND QTE.qtp_code LIKE 'TER%'
3714 AND TQL.kle_id = p_kle_id;
3715
3716
3717 l_quote_tbl quote_tbl_type;
3718 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3719 i NUMBER;
3720
3721
3722 BEGIN
3723
3724
3725 i := 1;
3726
3727 --Loop thru the quotes for the asset and set the quote_tbl
3728 FOR get_qte_rec IN get_qte_csr(p_kle_id) LOOP
3729
3730 l_quote_tbl(i).id := get_qte_rec.id;
3731 l_quote_tbl(i).quote_number := get_qte_rec.quote_number;
3732 l_quote_tbl(i).contract_number := get_qte_rec.contract_number;
3733 l_quote_tbl(i).partial_yn := get_qte_rec.partial_yn;
3734 l_quote_tbl(i).consolidated_yn := get_qte_rec.consolidated_yn;
3735 l_quote_tbl(i).qst_code := get_qte_rec.qst_code;
3736 l_quote_tbl(i).qtp_code := get_qte_rec.qtp_code;
3737
3738 i := i + 1;
3739
3740 END LOOP;
3741
3742 -- Set the return variables
3743 x_quote_tbl := l_quote_tbl;
3744 x_return_status := l_return_status;
3745
3746
3747 EXCEPTION
3748
3749 WHEN OTHERS THEN
3750
3751 IF get_qte_csr%ISOPEN THEN
3752 CLOSE get_qte_csr;
3753 END IF;
3754
3755 OKL_API.set_message(p_app_name => g_app_name,
3756 p_msg_name => g_unexpected_error,
3757 p_token1 => g_sqlcode_token,
3758 p_token1_value => sqlcode,
3759 p_token2 => g_sqlerrm_token,
3760 p_token2_value => sqlerrm);
3761
3762 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3763
3764 END get_all_term_quotes_for_line;
3765
3766
3767 -- Start of comments
3768 --
3769 -- Function Name : get_net_investment
3770 -- Description : This function calculates the net investment value
3771 -- Business Rules :
3772 -- Parameters : Input parameters : contract id, contract line id
3773 -- : Output parameters : x_return_status
3774 -- Version : 1.0
3775 -- History : SECHAWLA 14-FEB-03 2749690 - Created
3776 -- SECHAWLA 14-JUN-04 3449645 - Added check for LEASEST
3777 -- rmunjulu 17-May-05 4299668 - Changed to call New Formula for OP Leases.
3778 -- rmunjulu LOANS_ENHACEMENTS
3779 -- RMUNJULU 4699340 CALCULATE NET INVESTMENT FOR DF LEASES USING LINE_ASSET_NET_INVESTMENT
3780 -- prasjain 6030917 Added new parameter p_proration_factor to the function
3781 -- adding proration for all other cases which is not using formula LINE_ASSET_NET_INVESTMENT
3782 -- sending l_proration_factor as additional parameter to this formula LINE_ASSET_NET_INVESTMENT
3783 -- End of comments
3784
3785 FUNCTION get_net_investment( p_khr_id IN NUMBER,
3786 p_kle_id IN NUMBER,
3787 p_quote_id IN NUMBER, -- rmunjulu LOANS_ENHANCEMENT
3788 p_message_yn IN BOOLEAN,
3789 p_proration_factor IN NUMBER DEFAULT NULL,
3790 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
3791
3792 l_deal_type okl_k_headers.deal_type%TYPE;
3793 l_formula_name VARCHAR2(150) ;
3794 l_net_investment okl_txl_quote_lines_b.asset_value%TYPE;
3795 l_asset_value ak_attributes_vl.attribute_label_long%TYPE;
3796
3797 --SECHAWLA 09-AUG-05 4304230 : new declarations
3798 l_asset_net_book_value okl_txl_quote_lines_b.asset_value%TYPE;
3799 l_sts_code VARCHAR2(30);
3800
3801 -- This cursor is used to get the deal type for a contract.
3802 CURSOR l_oklheaders_csr(p_khr_id IN NUMBER) IS
3803 SELECT chr.sts_code,khr.deal_type
3804 FROM okl_k_headers khr, okc_k_headers_b chr
3805 WHERE khr.id = p_khr_id
3806 AND KHR.ID = CHR.ID;
3807
3808 -- SECHAWLA 09-AUG-05 4304230 : new declaraions end
3809
3810 /* SECHAWLA 09-AUG-05 4304230
3811 -- This cursor is used to get the deal type for a contract.
3812 CURSOR l_oklheaders_csr(p_khr_id IN NUMBER) IS
3813 SELECT deal_type
3814 FROM okl_k_headers
3815 WHERE id = p_khr_id;
3816 */
3817
3818 -- -- rmunjulu LOANS_ENHANCEMENT
3819 l_add_params okl_execute_formula_pub.ctxt_val_tbl_type;
3820
3821 -- rmunjulu bug 4699340
3822 l_date_effective_from DATE;
3823
3824 -- rmunjulu bug 4699340
3825 CURSOR get_qte_date_csr (p_quote_id IN NUMBER) IS
3826 SELECT date_effective_from
3827 FROM OKL_TRX_QUOTES_B
3828 WHERE id = p_quote_id;
3829
3830 -- Start : Bug 6030917 : prasjain
3831 l_proration_factor NUMBER;
3832 l_proration_flag VARCHAR2(1) := 'N';
3833 -- End : Bug 6030917 : prasjain
3834
3835 BEGIN
3836
3837 x_return_status := OKL_API.G_RET_STS_SUCCESS ;
3838
3839 OPEN l_oklheaders_csr(p_khr_id);
3840 FETCH l_oklheaders_csr INTO l_sts_code, l_deal_type; -- SECHAWLA 09-AUG-05 4304230 : added sts_code
3841 IF l_oklheaders_csr%NOTFOUND THEN
3842 OKC_API.set_message( p_app_name => 'OKC',
3843 p_msg_name => G_INVALID_VALUE,
3844 p_token1 => G_COL_NAME_TOKEN,
3845 p_token1_value => 'KHR_ID');
3846 x_return_status := OKL_API.G_RET_STS_ERROR;
3847 RETURN 0;
3848 END IF;
3849 CLOSE l_oklheaders_csr;
3850
3851 -- -- rmunjulu LOANS_ENHANCEMENT set the operands for formula engine with quote_id
3852 l_add_params(1).name := 'quote_id';
3853 l_add_params(1).value := to_char(p_quote_id);
3854
3855 l_add_params(2).name := 'QUOTE_ID';
3856 l_add_params(2).value := to_char(p_quote_id);
3857
3858 -- rmunjulu bug 4699340
3859 OPEN get_qte_date_csr (p_quote_id);
3860 FETCH get_qte_date_csr INTO l_date_effective_from;
3861 CLOSE get_qte_date_csr;
3862
3863 -- rmunjulu bug 4699340
3864 l_add_params(3).name := 'quote_effective_from_date';
3865 l_add_params(3).value := to_char(l_date_effective_from,'MM/DD/YYYY');
3866
3867 -- Start : Bug 6030917 : prasjain
3868 l_proration_factor := p_proration_factor;
3869 -- End : Bug 6030917 : prasjain
3870
3871 IF l_deal_type = 'LEASEOP' THEN
3872 --l_formula_name := 'CONTRACT_NET_INVESTMENT_OP';
3873 l_formula_name := 'ASSET_NET_INVESTMENT_OP'; -- rmunjulu 4299668
3874 ELSIF l_deal_type IN ('LEASEDF', 'LEASEST') THEN -- SECHAWLA 14-JUN-04 3449645 : Added check for LEASEST
3875 -- -- SECHAWLA 09-AUG-05 4304230 - added the following condition to use diff formula
3876 -- to calcualte net investment when contract goes to evergreen status.
3877 IF l_sts_code ='EVERGREEN' THEN
3878 l_formula_name := 'ASSET_NET_BOOK_VALUE';
3879 ELSE
3880 l_formula_name := 'LINE_ASSET_NET_INVESTMENT'; --'CONTRACT_NET_INVESTMENT_DF'; --rmunjulu bug 4699340 use this formula which calculates based on future values.
3881 -- Start : Bug 6030917 : prasjain
3882 -- adding additional parameter to the formula
3883 l_add_params(4).name := 'proration_factor';
3884 l_add_params(4).value := to_char(l_proration_factor);
3885 l_proration_flag := 'Y';
3886 -- End : Bug 6030917 : prasjain
3887 END IF;
3888
3889 ElSIF l_deal_type LIKE 'LOAN%' THEN
3890 -- l_formula_name := 'CONTRACT_NET_INVESTMENT_LOAN';
3891 l_formula_name := 'ASSET_NET_INVESTMENT_LOAN'; -- rmunjulu LOANS_ENHACEMENTS -- call this new formula
3892 END IF;
3893
3894 okl_am_util_pvt.get_formula_value(
3895 p_formula_name => l_formula_name,
3896 p_chr_id => p_khr_id,
3897 p_cle_id => p_kle_id,
3898 p_additional_parameters => l_add_params, -- rmunjulu LOANS_ENHANCEMENT
3899 x_formula_value => l_net_investment,
3900 x_return_status => x_return_status);
3901
3902 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
3903 IF p_message_yn THEN
3904 l_asset_value := get_ak_attribute(p_code => 'OKL_NET_INVESTMENT');
3905 -- Unable to calculate ASSET_VALUE
3906 OKL_API.set_message( p_app_name => 'OKL',
3907 p_msg_name => 'OKL_AM_FORMULA_ERROR',
3908 p_token1 => 'ASSET_VALUE',
3909 p_token1_value => l_asset_value);
3910 END IF;
3911 RETURN 0;
3912 END IF;
3913
3914 IF l_net_investment IS NULL THEN
3915 l_net_investment := 0;
3916 END IF;
3917
3918 -- Start : Bug 6030917 : prasjain
3919 IF l_proration_flag <> 'Y'
3920 AND l_proration_factor < 1 THEN
3921 l_net_investment := l_net_investment * l_proration_factor;
3922 END IF;
3923 -- End : Bug 6030917 : prasjain
3924
3925 RETURN l_net_investment;
3926
3927 EXCEPTION
3928 WHEN OTHERS THEN
3929 IF l_oklheaders_csr%ISOPEN THEN
3930 CLOSE l_oklheaders_csr;
3931 END IF;
3932 -- unexpected error
3933 OKL_API.set_message(p_app_name => 'OKC',
3934 p_msg_name => 'OKC_CONTRACTS_UNEXPECTED_ERROR',
3935 p_token1 => g_sqlcode_token,
3936 p_token1_value => sqlcode,
3937 p_token2 => g_sqlerrm_token,
3938 p_token2_value => sqlerrm);
3939
3940 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3941
3942 RETURN 0;
3943 END get_net_investment;
3944 --
3945 -- BAKUCHIB Bug 2757368 start
3946 --
3947 --------------------------------------------------------------------------------
3948 -- Start of Commnets
3949 -- Badrinath Kuchibholta
3950 -- Procedure Name : Get Party Name
3951 -- Description : Returns the Name of the Party Role
3952 -- Business Rules :
3953 -- Parameters : P_chr_id, p_kle_id(optional), p_rle_code
3954 -- Version : 1.0
3955 -- History : BAKUCHIB 19-FEB-2003 - 2757368 created
3956 -- BAKUCHIB 21-FEB-2303 - 2757368 - Modified to reomve
3957 -- Message stack population of invaid value, and removed
3958 -- checking of OKL_API.g_miss_* validation in the
3959 -- c_party_csr%not_found if condition. Added logic in
3960 -- exception section to close the cursor if open.
3961 -- End of Commnets
3962
3963 FUNCTION get_party_name(
3964 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
3965 p_rle_code IN OKC_K_PARTY_ROLES_B.RLE_CODE%TYPE,
3966 p_kle_id IN OKL_K_HEADERS.ID%TYPE)
3967 RETURN VARCHAR2 IS
3968 lv_object1_id1 OKC_K_PARTY_ROLES_B.OBJECT1_ID1%TYPE;
3969 lv_object1_id2 OKC_K_PARTY_ROLES_B.OBJECT1_ID2%TYPE;
3970 lv_object1_code OKC_K_PARTY_ROLES_B.JTOT_OBJECT1_CODE%TYPE;
3971 lv_name VARCHAR2(2000) := NULL;
3972 excp_party_error EXCEPTION;
3973
3974 -- Get the Party role details
3975 CURSOR c_party_csr(p_chr_id OKC_K_HEADERS_B.ID%TYPE,
3976 p_kle_id OKL_K_LINES.ID%TYPE,
3977 p_rle_code OKC_K_PARTY_ROLES_B.RLE_CODE%TYPE)
3978 IS
3979 SELECT object1_id1,
3980 object1_id2,
3981 jtot_object1_code
3982 FROM okc_k_party_roles_b
3983 WHERE dnz_chr_id = p_chr_id
3984 AND rle_code = p_rle_code
3985 AND nvl(cle_id,1) = nvl(p_Kle_id,nvl(cle_id,1));
3986
3987 BEGIN
3988 IF (p_chr_id IS NULL OR
3989 p_chr_id = OKL_API.G_MISS_NUM) OR
3990 (p_rle_code IS NULL OR
3991 p_rle_code = OKL_API.G_MISS_CHAR) THEN
3992 RAISE excp_party_error;
3993 END IF;
3994 OPEN c_party_csr(p_chr_id => p_chr_id,
3995 p_kle_id => p_kle_id,
3996 p_rle_code => p_rle_code);
3997 FETCH c_party_csr INTO lv_object1_id1,
3998 lv_object1_id2,
3999 lv_object1_code;
4000 IF c_party_csr%NOTFOUND THEN
4001 RAISE excp_party_error;
4002 END IF;
4003 CLOSE c_party_csr;
4004 IF lv_object1_id1 IS NOT NULL AND
4005 lv_object1_code IS NOT NULL THEN
4006 lv_name := OKL_AM_UTIL_PVT.get_jtf_object_name(
4007 p_object_code => lv_object1_code,
4008 p_object_id1 => lv_object1_id1,
4009 p_object_id2 => lv_object1_id2);
4010 ELSE
4011 RAISE excp_party_error;
4012 END IF;
4013 RETURN lv_name;
4014 EXCEPTION
4015 WHEN excp_party_error THEN
4016 IF c_party_csr%ISOPEN THEN
4017 CLOSE c_party_csr;
4018 END IF;
4019 RETURN NULL;
4020 WHEN OTHERS THEN
4021 IF c_party_csr%ISOPEN THEN
4022 CLOSE c_party_csr;
4023 END IF;
4024 RETURN NULL;
4025 END get_party_name;
4026 --
4027 -- BAKUCHIB Bug 2757368 end
4028 --
4029
4030
4031 -- Start of comments
4032 --
4033 -- Function Name : get_all_term_quotes_for_contract
4034 -- Description : Get all termination quotes for the contract
4035 -- Business Rules :
4036 -- Parameters : p_khr_id -- Line id
4037 -- x_quote_tbl -- Quote details table
4038 -- x_return_status -- return status
4039 -- History : SPILLIAIP 06-OCT-03 3115478 Created
4040 -- Version : 1.0
4041 --
4042 -- End of comments
4043 PROCEDURE get_all_term_qte_for_contract (
4044 p_khr_id IN NUMBER,
4045 x_quote_tbl OUT NOCOPY quote_tbl_type,
4046 x_return_status OUT NOCOPY VARCHAR2) IS
4047
4048
4049 -- Get all non-consolidated quotes for the asset -- both full and partial
4050 CURSOR get_qte_csr ( p_khr_id IN NUMBER ) IS
4051 SELECT QTE.id id,
4052 QTE.quote_number quote_number,
4053 KHR.contract_number contract_number,
4054 QTE.partial_yn partial_yn,
4055 QTE.consolidated_yn consolidated_yn,
4056 QTE.qst_code qst_code,
4057 QTE.qtp_code qtp_code
4058 FROM OKL_TRX_QUOTES_B QTE,
4059 OKC_K_HEADERS_B KHR
4060 WHERE qte.khr_id = khr.id
4061 AND qte.khr_id = p_khr_id
4062 AND QTE.qtp_code LIKE 'TER%';
4063
4064
4065 l_quote_tbl quote_tbl_type;
4066 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
4067 i NUMBER;
4068
4069
4070 BEGIN
4071
4072
4073 i := 1;
4074 --Loop thru the quotes for the asset and set the quote_tbl
4075 FOR get_qte_rec IN get_qte_csr(p_khr_id) LOOP
4076
4077 l_quote_tbl(i).id := get_qte_rec.id;
4078 l_quote_tbl(i).quote_number := get_qte_rec.quote_number;
4079 l_quote_tbl(i).contract_number := get_qte_rec.contract_number;
4080 l_quote_tbl(i).partial_yn := get_qte_rec.partial_yn;
4081 l_quote_tbl(i).consolidated_yn := get_qte_rec.consolidated_yn;
4082 l_quote_tbl(i).qst_code := get_qte_rec.qst_code;
4083 l_quote_tbl(i).qtp_code := get_qte_rec.qtp_code;
4084
4085 i := i + 1;
4086
4087 END LOOP;
4088
4089 -- Set the return variables
4090 x_quote_tbl := l_quote_tbl;
4091 x_return_status := l_return_status;
4092
4093
4094 EXCEPTION
4095
4096 WHEN OTHERS THEN
4097
4098 IF get_qte_csr%ISOPEN THEN
4099 CLOSE get_qte_csr;
4100 END IF;
4101
4102 OKL_API.set_message(p_app_name => g_app_name,
4103 p_msg_name => g_unexpected_error,
4104 p_token1 => g_sqlcode_token,
4105 p_token1_value => sqlcode,
4106 p_token2 => g_sqlerrm_token,
4107 p_token2_value => sqlerrm);
4108
4109 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
4110
4111 END get_all_term_qte_for_contract;
4112
4113 -- Start of comments
4114 --
4115 -- Function Name : get_actual_asset_residual
4116 -- Description : Get actual residual value for an asset
4117 -- Business Rules : Should be called with only terminated line
4118 -- Should be for OP/DF/SALES type Lease contract only -- which has FA values
4119 -- Parameters : p_khr_id -- Contract id
4120 -- p_kle_id -- Line Id
4121 -- History : RMUNJULU 18-MAY-04 3510740 Created
4122 -- rmunjulu 3816891 FORWARDPORT changed logic when termination with purchase
4123 -- rmunjulu 3816891 call formula ASSET_DF_TERMINATION_NIV for DF/ST term with/without purchase
4124 -- rmunjulu 4399736 For LOANS residual is the net investment calculated on quote
4125 -- nikshah -- Bug # 5484903 Fixed,
4126 -- Changed CURSOR get_acc_deprn1_csr (p_kle_id IN NUMBER) SQL definition
4127 -- akrangan -- changed tsu_code to tmt_status_code in get_quote_type_csr
4128 -- Version : 1.0
4129 --
4130 -- End of comments
4131 FUNCTION get_actual_asset_residual (
4132 p_khr_id IN NUMBER,
4133 p_kle_id IN NUMBER) RETURN NUMBER IS
4134
4135 -- get asset termination date
4136 CURSOR get_term_date_csr (p_kle_id IN NUMBER) IS
4137 SELECT kle.date_terminated
4138 FROM OKC_K_LINES_B kle
4139 WHERE kle.id = p_kle_id;
4140
4141 -- get termination trn for the contract and asset
4142 CURSOR get_quote_type_csr (p_khr_id IN NUMBER, p_kle_id IN NUMBER) IS
4143 SELECT qte.id,
4144 qte.qtp_code, -- quote type
4145 qte.date_accepted, -- quote acceptance date
4146 nvl(tql.asset_value,0) net_investment -- rmunjulu 4399736
4147 FROM OKL_TRX_CONTRACTS trn,
4148 OKL_TRX_QUOTES_B qte,
4149 OKL_TXL_QUOTE_LINES_B tql
4150 WHERE trn.khr_id = p_khr_id
4151 AND trn.tmt_status_code <> 'CANCELED' --akrangan changed tsu_code to tmt_status_code
4152 AND trn.qte_id = qte.id
4153 --rkuttiya added for 12.1.1 Multi GAAAP Project
4154 AND trn.representation_type = 'PRIMARY'
4155 --
4156 AND qte.id = tql.qte_id
4157 AND tql.qlt_code = 'AMCFIA'
4158 AND tql.kle_id = p_kle_id;
4159
4160 -- get purchase amount for asset
4161 CURSOR get_purchase_amt_csr (p_qte_id IN NUMBER, p_kle_id IN NUMBER) IS
4162 SELECT tql.amount
4163 FROM OKL_TXL_QUOTE_LINES_B tql
4164 WHERE tql.qte_id = p_qte_id
4165 AND tql.kle_id = p_kle_id
4166 AND tql.qlt_code = 'AMBPOC';
4167
4168 -- get purchase amount for asset
4169 CURSOR get_khr_deal_csr (p_khr_id IN NUMBER) IS
4170 SELECT khr.deal_type
4171 FROM OKL_K_HEADERS khr
4172 WHERE khr.id = p_khr_id;
4173
4174 -- get accumulated depreciation for the asset on the termination date(AVSINGH)
4175 -- if the FA book period is closed
4176 CURSOR get_acc_deprn_csr (p_kle_id IN NUMBER, p_ter_date IN DATE) IS
4177 SELECT fds.deprn_reserve deprn_amt
4178 FROM fa_deprn_summary fds,
4179 fa_deprn_periods fdp,
4180 fa_book_controls fbc,
4181 fa_calendar_periods fcp,
4182 okc_k_items_v itm,
4183 okc_k_lines_b kle,
4184 okc_line_styles_v lse
4185 WHERE fdp.book_type_code = fds.book_type_code
4186 AND fdp.period_counter = fds.period_counter
4187 AND fbc.book_class = 'CORPORATE'
4188 AND fds.book_type_code = fbc.book_type_code
4189 AND fds.asset_id = itm.object1_id1
4190 AND itm.cle_id = kle.id
4191 AND kle.cle_id = p_kle_id
4192 AND kle.lse_id = lse.id
4193 AND lse.lty_code = 'FIXED_ASSET'
4194 AND fbc.deprn_calendar = fcp.calendar_type
4195 AND fcp.period_name = fdp.period_name
4196 AND TRUNC(p_ter_date) BETWEEN fcp.START_DATE AND fcp.end_date;
4197
4198 -- get accumulated depreciation for the asset on the termination date(AVSINGH)
4199 -- if the FA Book period is open
4200 CURSOR get_acc_deprn1_csr (p_kle_id IN NUMBER) IS
4201 select fds.deprn_reserve deprn_amt
4202 from fa_deprn_summary fds,
4203 fa_deprn_periods fdp,
4204 fa_book_controls fbc,
4205 okc_k_items itm,
4206 okc_k_lines_b kle,
4207 okc_line_styles_b lse
4208 where fdp.book_type_code = fds.book_type_code
4209 and fdp.period_counter - 1 = fds.period_counter
4210 and fdp.period_close_date is null
4211 and fbc.book_class = 'CORPORATE'
4212 and fds.book_type_code = fbc.book_type_code
4213 and fds.asset_id = itm.object1_id1
4214 and itm.cle_id = kle.id
4215 and kle.cle_id = p_kle_id
4216 and kle.lse_id = lse.id
4217 and lse.lty_code = 'FIXED_ASSET';
4218
4219 -- get asset cost
4220 CURSOR get_asset_cost_csr (p_kle_id IN NUMBER) IS
4221 SELECT fab.original_cost
4222 FROM fa_books fab,
4223 fa_book_controls fbc,
4224 okc_k_items_v itm,
4225 okc_k_lines_b kle,
4226 okc_line_styles_v lse
4227 WHERE fbc.book_class = 'CORPORATE'
4228 AND fab.book_type_code = fbc.book_type_code
4229 AND fab.asset_id = itm.object1_id1
4230 AND itm.cle_id = kle.id
4231 AND kle.cle_id = p_kle_id
4232 AND kle.lse_id = lse.id
4233 AND lse.lty_code = 'FIXED_ASSET'
4234 AND fab.transaction_header_id_out IS NULL;
4235
4236 -- get deprn cost from off-lease trn (SECHAWLA)
4237 CURSOR get_deprn_cost_csr (p_kle_id IN NUMBER) IS
4238 SELECT depreciation_cost
4239 FROM okl_txl_assets_b
4240 WHERE kle_id = p_kle_id
4241 AND tal_type = 'AML'
4242 AND ROWNUM < 2;
4243
4244 -- rmunjulu 3735773
4245 -- get asset residual value (RMUNJULU)
4246 CURSOR get_asset_residual_csr (p_kle_id IN NUMBER) IS
4247 SELECT nvl(residual_value,0) residual_value
4248 FROM okl_k_lines
4249 WHERE id = p_kle_id;
4250
4251 -- rmunjulu 3735773
4252 -- get asset cost for corporate book (RMUNJULU)
4253 CURSOR get_corp_book_cost_csr (p_kle_id IN NUMBER) IS
4254 SELECT a.COST cost
4255 FROM okx_asset_lines_v o, fa_books a, fa_book_controls b
4256 WHERE o.parent_line_id = p_kle_id
4257 AND o.asset_id = a.asset_id
4258 AND a.book_type_code = b.book_type_code
4259 AND a.date_ineffective IS NULL
4260 AND a.transaction_header_id_out IS NULL
4261 AND b.book_class = 'CORPORATE';
4262
4263 l_date_terminated DATE;
4264 l_quote_id NUMBER;
4265 l_quote_type OKL_TRX_QUOTES_B.qtp_code%TYPE;
4266 l_asset_residual NUMBER;
4267 l_deprn_amt NUMBER;
4268 l_deal_type OKL_K_HEADERS.deal_type%TYPE;
4269 l_cost NUMBER;
4270 l_period_open VARCHAR2(1);
4271
4272 Expected_error EXCEPTION;
4273
4274 -- rmunjulu 3735773
4275 l_residual NUMBER;
4276 l_corp_book_cost NUMBER;
4277
4278 l_params okl_execute_formula_pub.ctxt_val_tbl_type;
4279 l_formula_name VARCHAR2(300);
4280 l_return_status VARCHAR2(3);
4281
4282 -- rmunjulu 4399736
4283 l_net_investment NUMBER;
4284
4285 BEGIN
4286
4287 -- Get the termination date for the asset
4288 -- Get the termination quote type for asset(with/without purchase)
4289 -- If with purchase then
4290 -- Get the purchase amount quote line
4291 -- If without purchase and NON OP LEASE then
4292 -- Get the Off-lease trn value
4293 -- If without purchase and OP LEASE then
4294 -- get the net book value on the termination date
4295
4296 -- Check for data validity
4297 IF p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM
4298 OR p_khr_id IS NULL OR p_khr_id = OKL_API.G_MISS_NUM THEN
4299
4300 RAISE Expected_Error;
4301
4302 END IF;
4303
4304 l_asset_residual := 0;
4305 l_cost := 0;
4306 l_deprn_amt := 0;
4307
4308 -- rmunjulu 3735773
4309 l_residual := 0;
4310 l_corp_book_cost := 0;
4311
4312 l_formula_name := 'ASSET_DF_TERMINATION_NIV';
4313
4314 -- Get the termination date for the asset
4315 FOR get_term_date_rec IN get_term_date_csr (p_kle_id) LOOP
4316 l_date_terminated := get_term_date_rec.date_terminated;
4317 END LOOP;
4318
4319 -- Get the termination quote type for asset(with/without purchase)
4320 FOR get_quote_type_rec IN get_quote_type_csr (p_khr_id, p_kle_id) LOOP
4321 l_quote_id := get_quote_type_rec.id;
4322 l_quote_type := get_quote_type_rec.qtp_code;
4323 l_net_investment := get_quote_type_rec.net_investment; -- rmunjulu 4399736
4324 --l_date_terminated := get_quote_type_rec.date_accepted;
4325 END LOOP;
4326
4327 -- If termination with purchase
4328 IF l_quote_type IN ('TER_MAN_PURCHASE',
4329 'TER_PURCHASE',
4330 'TER_RECOURSE',
4331 'TER_ROLL_PURCHASE') THEN -- with purchase
4332
4333 -- rmunjulu 3735773
4334 -- Get the deal type for the contract
4335 FOR get_khr_deal_rec IN get_khr_deal_csr (p_khr_id) LOOP
4336 l_deal_type := get_khr_deal_rec.deal_type;
4337 END LOOP;
4338
4339 -- rmunjulu 3735773
4340 -- If DF/ST Lease then the asset residual is residual value
4341 IF l_deal_type IN ('LEASEDF', 'LEASEST') THEN -- non OP lease
4342
4343 /* -- rmunjulu This piece of logic now resides in seeded formula, so call formula to get value
4344 -- Same logic as used in Retirement to update corporate book cost
4345 -- get the asset residual value
4346 FOR get_asset_residual_rec IN get_asset_residual_csr (p_kle_id) LOOP
4347 l_residual := get_asset_residual_rec.residual_value;
4348 END LOOP;
4349
4350 -- get the asset cost from corporate book -- will be 0 normally
4351 FOR get_corp_book_cost_rec IN get_corp_book_cost_csr (p_kle_id) LOOP
4352 l_corp_book_cost := get_corp_book_cost_rec.cost;
4353 END LOOP;
4354
4355 -- asset residual will be same calculation used in asset disposal
4356 l_asset_residual := l_residual - l_corp_book_cost + l_corp_book_cost;
4357 */
4358
4359 -- rmunjulu Call the seeded formula to get residual value
4360 l_params(1).name := 'QUOTE_ID';
4361 l_params(1).value := l_quote_id;
4362
4363 get_formula_value (
4364 p_formula_name => l_formula_name,
4365 p_chr_id => p_khr_id,
4366 p_cle_id => p_kle_id,
4367 p_additional_parameters => l_params,
4368 x_formula_value => l_asset_residual,
4369 x_return_status => l_return_status);
4370
4371 IF l_asset_residual IS NULL THEN
4372
4373 l_asset_residual := 0;
4374 END IF;
4375
4376 ELSIF l_deal_type IN ('LEASEOP') THEN -- OP Lease
4377
4378 -- -- Get the purchase amount quote line which will be the asset residual
4379 -- FOR get_purchase_amt_rec IN get_purchase_amt_csr (l_quote_id, p_kle_id) LOOP
4380 -- l_asset_residual := get_purchase_amt_rec.amount;
4381 -- END LOOP;
4382
4383 -- rmunjulu 3735773 -- use the same logic as used for term without purchase
4384 -- Calculate the Net Book Value for the asset on termination date
4385
4386 -- Net Book Value = Asset Cost - Accumulated Deprn
4387
4388 -- Get the deprn amount
4389 OPEN get_acc_deprn_csr(p_kle_id, l_date_terminated);
4390 FETCH get_acc_deprn_csr INTO l_deprn_amt;
4391 IF get_acc_deprn_csr%NOTFOUND THEN
4392 l_period_open := 'Y';
4393 END IF;
4394 CLOSE get_acc_deprn_csr;
4395
4396 -- if FA Book period is open get it from there
4397 IF l_period_open = 'Y' THEN
4398 -- Get the deprn amount
4399 FOR get_acc_deprn1_rec IN get_acc_deprn1_csr (p_kle_id) LOOP
4400 l_deprn_amt := get_acc_deprn1_rec.deprn_amt;
4401 END LOOP;
4402 END IF;
4403
4404 -- Get the asset cost
4405 FOR get_asset_cost_rec IN get_asset_cost_csr (p_kle_id) LOOP
4406 l_cost := get_asset_cost_rec.original_cost;
4407 END LOOP;
4408
4409 -- Calculate net book value which will be residual
4410 l_asset_residual := l_cost - l_deprn_amt;
4411
4412 ELSIF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN -- rmunjulu 4399736
4413
4414 -- get residual value as the one populated on the quote
4415 l_asset_residual := l_net_investment;
4416
4417 ELSE
4418
4419 l_asset_residual := 0;
4420
4421 END IF;
4422
4423 ELSE -- without purchase
4424
4425 -- Get the deal type for the contract
4426 FOR get_khr_deal_rec IN get_khr_deal_csr (p_khr_id) LOOP
4427 l_deal_type := get_khr_deal_rec.deal_type;
4428 END LOOP;
4429
4430 IF l_deal_type IN ('LEASEDF', 'LEASEST') THEN -- NON OP LEASE
4431
4432 /* -- rmunjulu This piece of logic now resides in seeded formula, so call formula to get value
4433 -- Get the Off-lease trn value
4434 FOR get_deprn_cost_rec IN get_deprn_cost_csr (p_kle_id ) LOOP
4435
4436 l_asset_residual := get_deprn_cost_rec.depreciation_cost;
4437
4438 END LOOP;
4439 */
4440 -- rmunjulu Call the seeded formula to get residual value
4441 l_params(1).name := 'QUOTE_ID';
4442 l_params(1).value := l_quote_id;
4443
4444 get_formula_value (
4445 p_formula_name => l_formula_name,
4446 p_chr_id => p_khr_id,
4447 p_cle_id => p_kle_id,
4448 p_additional_parameters => l_params,
4449 x_formula_value => l_asset_residual,
4450 x_return_status => l_return_status);
4451
4452 IF l_asset_residual IS NULL THEN
4453
4454 l_asset_residual := 0;
4455 END IF;
4456
4457 ELSIF l_deal_type IN ('LEASEOP') THEN -- OP LEASE
4458
4459 -- Calculate the Net Book Value for the asset on termination date
4460
4461 -- Net Book Value = Asset Cost - Accumulated Deprn
4462
4463 -- Get the deprn amount
4464 OPEN get_acc_deprn_csr(p_kle_id, l_date_terminated);
4465 FETCH get_acc_deprn_csr INTO l_deprn_amt;
4466 IF get_acc_deprn_csr%NOTFOUND THEN
4467 l_period_open := 'Y';
4468 END IF;
4469 CLOSE get_acc_deprn_csr;
4470
4471 -- if FA Book period is open get it from there
4472 IF l_period_open = 'Y' THEN
4473 -- Get the deprn amount
4474 FOR get_acc_deprn1_rec IN get_acc_deprn1_csr (p_kle_id) LOOP
4475 l_deprn_amt := get_acc_deprn1_rec.deprn_amt;
4476 END LOOP;
4477 END IF;
4478
4479 -- Get the asset cost
4480 FOR get_asset_cost_rec IN get_asset_cost_csr (p_kle_id) LOOP
4481 l_cost := get_asset_cost_rec.original_cost;
4482 END LOOP;
4483
4484 -- Calculate net book value which will be residual
4485 l_asset_residual := l_cost - l_deprn_amt;
4486
4487 ELSIF l_deal_type IN ('LOAN','LOAN-REVOLVING') THEN -- rmunjulu 4399736
4488
4489 -- get residual value as the one populated on the quote
4490 l_asset_residual := l_net_investment;
4491
4492 ELSE
4493
4494 l_asset_residual := 0;
4495
4496 END IF;
4497
4498 END IF;
4499
4500 RETURN l_asset_residual;
4501
4502 EXCEPTION
4503 WHEN Expected_error THEN
4504 IF get_acc_deprn_csr%ISOPEN THEN
4505 CLOSE get_acc_deprn_csr;
4506 END IF;
4507 RETURN 0;
4508
4509 WHEN OTHERS THEN
4510 IF get_acc_deprn_csr%ISOPEN THEN
4511 CLOSE get_acc_deprn_csr;
4512 END IF;
4513 RETURN 0;
4514
4515 END get_actual_asset_residual;
4516
4517 -- Start of comments
4518 --
4519 -- Function Name : get_anticipated_bill
4520 -- Description : Get anticipated bill total for quote
4521 -- Business Rules : Dependent on new table okl_txd_qte_antcpt_bill
4522 -- Parameters : p_qte_id -- Quote Id
4523 -- History : RMUNJULU EDAT CREATED
4524 -- Version : 1.0
4525 --
4526 -- End of comments
4527 FUNCTION get_anticipated_bill (p_qte_id IN NUMBER) RETURN NUMBER IS
4528
4529 -- get sum of anticipated billing for quote
4530 CURSOR get_ant_bill_csr(p_qte_id IN NUMBER) IS
4531 SELECT nvl(sum(tqa.amount),0) amount
4532 FROM okl_txd_qte_antcpt_bill tqa
4533 WHERE tqa.qte_id = p_qte_id;
4534
4535 l_ant_bill number;
4536
4537 BEGIN
4538
4539 l_ant_bill := 0;
4540
4541 -- get anticipated billing
4542 OPEN get_ant_bill_csr(p_qte_id);
4543 FETCH get_ant_bill_csr INTO l_ant_bill;
4544 CLOSE get_ant_bill_csr;
4545
4546 RETURN l_ant_bill;
4547
4548 EXCEPTION
4549 WHEN OTHERS THEN
4550 IF get_ant_bill_csr%ISOPEN THEN
4551 CLOSE get_ant_bill_csr;
4552 END IF;
4553 RETURN 0;
4554
4555 END get_anticipated_bill;
4556
4557 -- Start of comments
4558 --
4559 -- Function Name : get_asset_net_book_value
4560 -- Description : Get Net Book value from FA for a particular date
4561 -- Business Rules : Should be for OP/DF/SALES type Lease contract only -- which has FA values
4562 -- Parameters : p_kle_id -- Financial Line Id
4563 -- p_transaction_date Date for which you want FA NBV
4564 -- History : RMUNJULU 4299668 Created
4565 -- nikshah -- Bug # 5484903 Fixed,
4566 -- Changed CURSOR get_acc_deprn1_csr (p_kle_id IN NUMBER) SQL definition
4567 -- Version : 1.0
4568 --
4569 -- End of comments
4570 FUNCTION get_asset_net_book_value (
4571 p_kle_id IN NUMBER,
4572 p_transaction_date IN DATE DEFAULT NULL) RETURN NUMBER IS
4573
4574 -- get accumulated depreciation for the asset on the termination date(AVSINGH)
4575 -- if the FA book period is closed
4576 CURSOR get_acc_deprn_csr (p_kle_id IN NUMBER, p_trn_date IN DATE) IS
4577 SELECT nvl(fds.deprn_reserve,0) deprn_amt
4578 FROM fa_deprn_summary fds,
4579 fa_deprn_periods fdp,
4580 fa_book_controls fbc,
4581 fa_calendar_periods fcp,
4582 okc_k_items_v itm,
4583 okc_k_lines_b kle,
4584 okc_line_styles_v lse
4585 WHERE fdp.book_type_code = fds.book_type_code
4586 AND fdp.period_counter = fds.period_counter
4587 AND fbc.book_class = 'CORPORATE'
4588 AND fds.book_type_code = fbc.book_type_code
4589 AND fds.asset_id = itm.object1_id1
4590 AND itm.cle_id = kle.id
4591 AND kle.cle_id = p_kle_id
4592 AND kle.lse_id = lse.id
4593 AND lse.lty_code = 'FIXED_ASSET'
4594 AND fbc.deprn_calendar = fcp.calendar_type
4595 AND fcp.period_name = fdp.period_name
4596 AND TRUNC(p_trn_date) BETWEEN fcp.start_date AND fcp.end_date;
4597
4598 -- get accumulated depreciation for the asset on the termination date(AVSINGH)
4599 -- if the FA Book period is open
4600 CURSOR get_acc_deprn1_csr (p_kle_id IN NUMBER) IS
4601 SELECT nvl(fds.deprn_reserve,0) deprn_amt
4602 FROM fa_deprn_summary fds,
4603 fa_deprn_periods fdp,
4604 fa_book_controls fbc,
4605 okc_k_items itm,
4606 okc_k_lines_b kle,
4607 okc_line_styles_b lse
4608 WHERE fdp.book_type_code = fds.book_type_code
4609 AND fdp.period_counter - 1 = fds.period_counter
4610 AND fdp.period_close_date IS NULL
4611 AND fbc.book_class = 'CORPORATE'
4612 AND fds.book_type_code = fbc.book_type_code
4613 AND fds.asset_id = itm.object1_id1
4614 AND itm.cle_id = kle.id
4615 AND kle.cle_id = p_kle_id
4616 AND kle.lse_id = lse.id
4617 AND lse.lty_code = 'FIXED_ASSET';
4618
4619 -- get asset cost
4620 CURSOR get_asset_cost_csr (p_kle_id IN NUMBER) IS
4621 SELECT nvl(fab.cost,0) current_cost
4622 FROM fa_books fab,
4623 fa_book_controls fbc,
4624 okc_k_items_v itm,
4625 okc_k_lines_b kle,
4626 okc_line_styles_v lse
4627 WHERE fbc.book_class = 'CORPORATE'
4628 AND fab.book_type_code = fbc.book_type_code
4629 AND fab.asset_id = itm.object1_id1
4630 AND itm.cle_id = kle.id
4631 AND kle.cle_id = p_kle_id
4632 AND kle.lse_id = lse.id
4633 AND lse.lty_code = 'FIXED_ASSET'
4634 AND fab.transaction_header_id_out IS NULL;
4635
4636 l_deprn_amt NUMBER;
4637 l_cost NUMBER;
4638 l_period_open VARCHAR2(1);
4639 l_nbv NUMBER;
4640 l_return_status VARCHAR2(3);
4641
4642 Expected_error EXCEPTION;
4643
4644 BEGIN
4645
4646 -- Check for data validity
4647 IF p_kle_id IS NULL OR p_kle_id = OKL_API.G_MISS_NUM THEN
4648
4649 RAISE Expected_Error;
4650
4651 END IF;
4652
4653 l_deprn_amt := 0;
4654 l_cost := 0;
4655 l_nbv := 0;
4656
4657 -- Net Book Value = Asset Cost - Accumulated Deprn
4658 -- Get the deprn amount for that date
4659 OPEN get_acc_deprn_csr(p_kle_id, nvl(p_transaction_date,sysdate));
4660 FETCH get_acc_deprn_csr INTO l_deprn_amt;
4661 -- if not found then that means the transaction dates period is open
4662 IF get_acc_deprn_csr%NOTFOUND THEN
4663 l_period_open := 'Y';
4664 END IF;
4665 CLOSE get_acc_deprn_csr;
4666
4667 -- if FA Book period is open get it from there
4668 IF nvl(l_period_open,'N') = 'Y' THEN
4669 -- Get the deprn amount
4670 OPEN get_acc_deprn1_csr (p_kle_id);
4671 FETCH get_acc_deprn1_csr INTO l_deprn_amt;
4672 CLOSE get_acc_deprn1_csr;
4673 END IF;
4674
4675 -- Get the asset cost
4676 OPEN get_asset_cost_csr (p_kle_id);
4677 FETCH get_asset_cost_csr INTO l_cost;
4678 CLOSE get_asset_cost_csr;
4679
4680 -- Calculate net book value
4681 l_nbv := l_cost - l_deprn_amt;
4682
4683 RETURN l_nbv;
4684
4685 EXCEPTION
4686 WHEN Expected_error THEN
4687 IF get_acc_deprn_csr%ISOPEN THEN
4688 CLOSE get_acc_deprn_csr;
4689 END IF;
4690 IF get_acc_deprn1_csr%ISOPEN THEN
4691 CLOSE get_acc_deprn1_csr;
4692 END IF;
4693 IF get_asset_cost_csr%ISOPEN THEN
4694 CLOSE get_asset_cost_csr;
4695 END IF;
4696 RETURN NULL;
4697
4698 WHEN OTHERS THEN
4699 IF get_acc_deprn_csr%ISOPEN THEN
4700 CLOSE get_acc_deprn_csr;
4701 END IF;
4702 IF get_acc_deprn1_csr%ISOPEN THEN
4703 CLOSE get_acc_deprn1_csr;
4704 END IF;
4705 IF get_asset_cost_csr%ISOPEN THEN
4706 CLOSE get_asset_cost_csr;
4707 END IF;
4708 RETURN NULL;
4709
4710 END get_asset_net_book_value;
4711
4712 -- rmunjulu Sales_Tax_Enhancement
4713 -- This function returns the tax amount for the tax TRX_ID
4714 -- TRX_ID can be quote_id, ar_inv_trx_id
4715 FUNCTION get_tax_amount (
4716 p_tax_trx_id IN NUMBER) RETURN NUMBER IS
4717
4718 -- p_tax_trx_id can be Quote Id, Ar Inv Trx Id
4719 CURSOR get_tax_amount_csr (p_tax_trx_id IN NUMBER) IS
4720 SELECT SUM(TAX.tax_amt) tax_amount
4721 FROM OKL_TAX_SOURCES TXS,
4722 OKL_TAX_TRX_DETAILS TAX
4723 WHERE TXS.trx_id = p_tax_trx_id
4724 AND TAX.txs_id = TXS.id;
4725
4726 l_tax_amount NUMBER;
4727
4728 BEGIN
4729
4730 -- Get the tax amount
4731 OPEN get_tax_amount_csr (p_tax_trx_id);
4732 FETCH get_tax_amount_csr INTO l_tax_amount;
4733 CLOSE get_tax_amount_csr;
4734
4735 RETURN l_tax_amount;
4736
4737 EXCEPTION
4738 WHEN OTHERS THEN
4739 IF get_tax_amount_csr%ISOPEN THEN
4740 CLOSE get_tax_amount_csr;
4741 END IF;
4742 RETURN null;
4743
4744 END get_tax_amount;
4745
4746 -- rmunjulu loans_enhancements get product details
4747 -- gets the contract product details such as deal type, revenue recognition method,
4748 -- interest calculation basis and tax owner
4749 PROCEDURE get_contract_product_details (
4750 p_khr_id IN NUMBER,
4751 x_deal_type OUT NOCOPY VARCHAR2,
4752 x_rev_rec_method OUT NOCOPY VARCHAR2,
4753 x_int_cal_basis OUT NOCOPY VARCHAR2,
4754 x_tax_owner OUT NOCOPY VARCHAR2,
4755 x_return_status OUT NOCOPY VARCHAR2) IS
4756
4757 CURSOR get_prd_details_csr (p_khr_id IN NUMBER) IS
4758 SELECT PQY.NAME QUALITY,
4759 QVE.VALUE VALUE,
4760 KHR.DEAL_TYPE DEAL_TYPE
4761 FROM OKL_PDT_PQY_VALS PQV ,
4762 OKL_PDT_PQYS PDQ ,
4763 OKL_PRODUCTS PDT,
4764 OKL_PQY_VALUES QVE,
4765 OKL_PDT_QUALITYS PQY,
4766 OKL_K_HEADERS KHR
4767 WHERE KHR.ID = p_khr_id
4768 AND PQV.PDT_ID = PDT.ID
4769 AND PQV.PDQ_ID = PDQ.ID
4770 AND PQV.QVE_ID = QVE.ID
4771 AND QVE.PQY_ID = PQY.ID
4772 AND PDQ.PQY_ID = PQY.ID
4773 AND PDT.PTL_ID = PDQ.PTL_ID
4774 AND PDT.ID = KHR.PDT_ID
4775 AND PQY.NAME IN ( 'INTEREST_CALCULATION_BASIS', 'REVENUE_RECOGNITION_METHOD', 'TAXOWNER');
4776
4777 BEGIN
4778
4779 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4780
4781 FOR get_prd_details_rec IN get_prd_details_csr(p_khr_id) LOOP
4782 x_deal_type := get_prd_details_rec.deal_type;
4783 IF get_prd_details_rec.quality = 'INTEREST_CALCULATION_BASIS' THEN
4784 x_int_cal_basis := get_prd_details_rec.value;
4785 ELSIF get_prd_details_rec.quality = 'REVENUE_RECOGNITION_METHOD' THEN
4786 x_rev_rec_method := get_prd_details_rec.value;
4787 ELSIF get_prd_details_rec.quality = 'TAXOWNER' THEN
4788 x_tax_owner := get_prd_details_rec.value;
4789 END IF;
4790 END LOOP;
4791
4792 IF x_deal_type IS NULL THEN
4793 x_return_status := OKL_API.G_RET_STS_ERROR;
4794 END IF;
4795
4796 EXCEPTION
4797
4798 WHEN OTHERS THEN
4799
4800 OKL_API.set_message(p_app_name => g_app_name,
4801 p_msg_name => g_unexpected_error,
4802 p_token1 => g_sqlcode_token,
4803 p_token1_value => sqlcode,
4804 p_token2 => g_sqlerrm_token,
4805 p_token2_value => sqlerrm);
4806
4807 x_return_status := OKL_API.G_RET_STS_ERROR;
4808
4809 END get_contract_product_details;
4810
4811 -- rmunjulu LOANS_ENHANCEMENTS get excess loan payment amount
4812 FUNCTION get_excess_loan_payment (
4813 p_khr_id IN NUMBER,
4814 x_return_status OUT NOCOPY VARCHAR2) RETURN NUMBER IS
4815
4816 l_loan_refund_amount NUMBER := 0;
4817 l_deal_type VARCHAR2(300);
4818 l_rev_rec_method VARCHAR2(300);
4819 l_int_cal_basis VARCHAR2(300);
4820 l_tax_owner VARCHAR2(300);
4821
4822 BEGIN
4823
4824 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4825
4826 -- Get the contract product details
4827 OKL_AM_UTIL_PVT.get_contract_product_details(
4828 p_khr_id => p_khr_id,
4829 x_deal_type => l_deal_type,
4830 x_rev_rec_method => l_rev_rec_method,
4831 x_int_cal_basis => l_int_cal_basis,
4832 x_tax_owner => l_tax_owner,
4833 x_return_status => x_return_status);
4834
4835 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4836 RAISE OKL_API.G_EXCEPTION_ERROR;
4837 END IF;
4838
4839 -- get refunds only for EstandAct and Act cases
4840 IF l_deal_type LIKE 'LOAN%'
4841 AND l_rev_rec_method IN ('ESTIMATED_AND_BILLED','ACTUAL') THEN
4842
4843 l_loan_refund_amount := OKL_VARIABLE_INT_UTIL_PVT.get_excess_loan_payment(
4844 x_return_status => x_return_status,
4845 p_khr_id => p_khr_id);
4846
4847 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
4848 RAISE OKL_API.G_EXCEPTION_ERROR;
4849 END IF;
4850 END IF;
4851 RETURN l_loan_refund_amount;
4852
4853 EXCEPTION
4854
4855 WHEN OKL_API.G_EXCEPTION_ERROR THEN
4856
4857 x_return_status := OKL_API.G_RET_STS_ERROR;
4858
4859 WHEN OTHERS THEN
4860
4861 OKL_API.set_message(p_app_name => g_app_name,
4862 p_msg_name => g_unexpected_error,
4863 p_token1 => g_sqlcode_token,
4864 p_token1_value => sqlcode,
4865 p_token2 => g_sqlerrm_token,
4866 p_token2_value => sqlerrm);
4867 x_return_status := OKL_API.G_RET_STS_ERROR;
4868
4869 END get_excess_loan_payment;
4870
4871 -- rmunjulu BUYOUT check full termination transaction being processed.
4872 -- akrangan changed tsu_code to tmt_status_code
4873 FUNCTION check_full_term_in_progress (
4874 p_khr_id IN NUMBER,
4875 x_return_status OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
4876
4877 CURSOR get_termination_trn_csr (p_khr_id IN NUMBER) IS
4878 SELECT 'Y'
4879 FROM DUAL
4880 WHERE EXISTS (SELECT id
4881 FROM OKL_TRX_CONTRACTS
4882 WHERE khr_id = p_khr_id
4883 AND tmt_status_code NOT IN ('PROCESSED', 'CANCELED') --akrangan changed
4884 --tsu_code to tmt_status_code
4885 AND tcn_type IN ('TMT')
4886 --rkuttiya added for 12.1.1 Multi GAAP Project
4887 AND representation_type = 'PRIMARY');
4888 --
4889
4890 l_term_in_progress VARCHAR2(3);
4891
4892 BEGIN
4893
4894 x_return_status := OKL_API.G_RET_STS_SUCCESS;
4895
4896 OPEN get_termination_trn_csr (p_khr_id);
4897 FETCH get_termination_trn_csr INTO l_term_in_progress;
4898 CLOSE get_termination_trn_csr;
4899
4900 RETURN nvl(l_term_in_progress,'N');
4901
4902 EXCEPTION
4903
4904 WHEN OTHERS THEN
4905
4906 OKL_API.set_message(p_app_name => g_app_name,
4907 p_msg_name => g_unexpected_error,
4908 p_token1 => g_sqlcode_token,
4909 p_token1_value => sqlcode,
4910 p_token2 => g_sqlerrm_token,
4911 p_token2_value => sqlerrm);
4912 x_return_status := OKL_API.G_RET_STS_ERROR;
4913 RETURN NULL;
4914
4915 END check_full_term_in_progress;
4916 --asawanka added
4917 FUNCTION get_latest_alc_tax (
4918 p_top_line_id IN NUMBER) RETURN NUMBER IS
4919
4920 CURSOR get_tax_amount_csr (cp_top_line_id IN NUMBER) IS
4921 SELECT tax_amount
4922 FROM okl_cs_alc_summary_uv
4923 WHERE dnz_cle_id = cp_top_line_id
4924 AND tsu_code = 'PROCESSED';
4925
4926 l_tax_amount NUMBER;
4927
4928 BEGIN
4929
4930 -- Get the tax amount
4931 OPEN get_tax_amount_csr (p_top_line_id);
4932 -- FIRST ROW will have latest tax amount for latest processed alc transaction
4933 FETCH get_tax_amount_csr INTO l_tax_amount;
4934 IF get_tax_amount_csr%NOTFOUND THEN
4935 l_tax_amount := NULL;
4936 END IF;
4937 CLOSE get_tax_amount_csr;
4938
4939 RETURN l_tax_amount;
4940
4941 EXCEPTION
4942 WHEN OTHERS THEN
4943 IF get_tax_amount_csr%ISOPEN THEN
4944 CLOSE get_tax_amount_csr;
4945 END IF;
4946 RETURN null;
4947
4948 END get_latest_alc_tax;
4949 --asawanka added
4950 FUNCTION get_latest_alc_req_id (
4951 p_top_line_id IN NUMBER) RETURN NUMBER IS
4952
4953 CURSOR get_req_id_csr (cp_top_line_id IN NUMBER) IS
4954 SELECT request_id
4955 FROM okl_cs_alc_summary_uv
4956 WHERE dnz_cle_id = cp_top_line_id
4957 AND tsu_code = 'PROCESSED';
4958
4959 l_req_id NUMBER;
4960
4961 BEGIN
4962
4963 -- Get the tax amount
4964 OPEN get_req_id_csr (p_top_line_id);
4965 -- FIRST ROW will have latest request_id for latest processed alc transaction
4966 FETCH get_req_id_csr INTO l_req_id;
4967 IF get_req_id_csr%NOTFOUND THEN
4968 l_req_id := NULL;
4969 END IF;
4970 CLOSE get_req_id_csr;
4971
4972 RETURN l_req_id;
4973
4974 EXCEPTION
4975 WHEN OTHERS THEN
4976 IF get_req_id_csr%ISOPEN THEN
4977 CLOSE get_req_id_csr;
4978 END IF;
4979 RETURN null;
4980
4981 END get_latest_alc_req_id;
4982 --asawanka added, modified zrehman
4983 FUNCTION get_latest_alc_serialized_flag (
4984 p_top_line_id IN NUMBER) RETURN VARCHAR2 IS
4985
4986 CURSOR check_item_csr (p_line_id IN NUMBER) IS -- p_line_id is FREE_FORM1
4987 SELECT mtl.serial_number_control_code
4988 FROM okc_k_lines_b line,
4989 okc_line_styles_b style,
4990 okc_k_items kitem,
4991 mtl_system_items mtl
4992 WHERE line.lse_id = style.id
4993 AND style.lty_code = 'ITEM'
4994 AND line.id = kitem.cle_id
4995 AND kitem.jtot_object1_code = 'OKX_SYSITEM'
4996 AND kitem.object1_id1 = mtl.inventory_item_id
4997 AND kitem.object1_id2 = TO_CHAR(mtl.organization_id)
4998 AND line.cle_id = p_line_id;
4999 l_count NUMBER :=0;
5000
5001 l_ser_flg VARCHAR2(3);
5002
5003 BEGIN
5004
5005 OPEN check_item_csr(p_top_line_id);
5006 FETCH check_item_csr INTO l_count;
5007 CLOSE check_item_csr;
5008
5009 IF l_count = 1 THEN
5010 l_ser_flg := 'N';
5011 ELSE
5012 l_ser_flg := 'Y';
5013
5014 END IF;
5015 RETURN l_ser_flg;
5016
5017 EXCEPTION
5018 WHEN OTHERS THEN
5019 IF check_item_csr%ISOPEN THEN
5020 CLOSE check_item_csr;
5021 END IF;
5022 RETURN null;
5023
5024 END get_latest_alc_serialized_flag;
5025 --asawanka added
5026 FUNCTION get_latest_alc_eff_date (
5027 p_top_line_id IN NUMBER) RETURN DATE IS
5028
5029 CURSOR get_eff_Date_csr (cp_top_line_id IN NUMBER) IS
5030 SELECT DATE_EFFECTIVE
5031 FROM okl_cs_alc_summary_uv
5032 WHERE dnz_cle_id = cp_top_line_id
5033 AND tsu_code = 'PROCESSED';
5034
5035 l_eff_date DATE;
5036
5037 BEGIN
5038
5039 -- Get the effective date
5040 OPEN get_eff_Date_csr (p_top_line_id);
5041 -- FIRST ROW will have latest effective date for latest processed alc transaction
5042 FETCH get_eff_Date_csr INTO l_eff_date;
5043 IF get_eff_Date_csr%NOTFOUND THEN
5044 l_eff_date := NULL;
5045 END IF;
5046 CLOSE get_eff_Date_csr;
5047
5048 RETURN l_eff_date;
5049
5050 EXCEPTION
5051 WHEN OTHERS THEN
5052 IF get_eff_Date_csr%ISOPEN THEN
5053 CLOSE get_eff_Date_csr;
5054 END IF;
5055 RETURN null;
5056
5057 END get_latest_alc_eff_date;
5058 --asawanka added
5059 FUNCTION get_latest_alc_req_sts (
5060 p_top_line_id IN NUMBER) RETURN VARCHAR2 IS
5061
5062 CURSOR get_req_sts_csr (cp_top_line_id IN NUMBER) IS
5063 SELECT TSU_CODE
5064 FROM okl_cs_alc_summary_uv
5065 WHERE dnz_cle_id = cp_top_line_id;
5066
5067 l_req_sts VARCHAR2(30);
5068
5069 BEGIN
5070
5071 -- Get the status
5072 OPEN get_req_sts_csr (p_top_line_id);
5073 -- FIRST ROW will have latest request_sts
5074 FETCH get_req_sts_csr INTO l_req_sts;
5075 IF get_req_sts_csr%NOTFOUND THEN
5076 l_req_sts := NULL;
5077 END IF;
5078 CLOSE get_req_sts_csr;
5079
5080 RETURN l_req_sts;
5081
5082 EXCEPTION
5083 WHEN OTHERS THEN
5084 IF get_req_sts_csr%ISOPEN THEN
5085 CLOSE get_req_sts_csr;
5086 END IF;
5087 RETURN null;
5088
5089 END get_latest_alc_req_sts;
5090
5091 -- added by zrehman to get TRX_ID for Non-Serialized Asset
5092 FUNCTION get_latest_alc_trx_id (
5093 p_top_line_id IN NUMBER) RETURN NUMBER IS
5094
5095 CURSOR get_trx_id_csr (cp_top_line_id IN NUMBER) IS
5096 SELECT trx.Id
5097 FROM Okl_trx_Assets trx,
5098 Okl_txl_Itm_Insts txl,
5099 Okl_trx_Types_v Try
5100 WHERE trx.Id = txl.tAs_Id
5101 AND trx.Try_Id = Try.Id
5102 AND Try.NAME = 'Asset Relocation'
5103 AND trx.tsu_code = 'PROCESSED'
5104 AND txl.dnz_cle_id = cp_top_line_id
5105 ORDER BY trx.Date_tRans_OccurRed DESC;
5106 l_trx_id NUMBER := NULL;
5107 l_ser_flg VARCHAR2(3);
5108
5109 BEGIN
5110 -- Get whether is serialized or not
5111 l_ser_flg := get_latest_alc_serialized_flag(p_top_line_id);
5112 IF (l_ser_flg IS NOT null AND l_ser_flg <> 'Y' ) THEN
5113 OPEN get_trx_id_csr(p_top_line_id);
5114 FETCH get_trx_id_csr INTO l_trx_id;
5115 CLOSE get_trx_id_csr;
5116 END IF;
5117
5118 RETURN l_trx_id;
5119
5120 EXCEPTION
5121 WHEN OTHERS THEN
5122 IF get_trx_id_csr%ISOPEN THEN
5123 CLOSE get_trx_id_csr;
5124 END IF;
5125 RETURN null;
5126
5127 END get_latest_alc_trx_id;
5128
5129 -- rbruno bug 6185552 start
5130 -- Start of comments
5131 -- Function Name : get_fa_nbv
5132 -- Description : Get Net Book value per unit from FA for a particular asset
5133 -- End of comments
5134
5135 FUNCTION get_fa_nbv (
5136 p_chr_id IN OKC_K_HEADERS_B.ID%TYPE
5137 ,p_asset_id IN NUMBER
5138 ) RETURN NUMBER IS
5139
5140 CURSOR get_rel_date(p_khr_id IN NUMBER) IS
5141 SELECT start_date
5142 FROM okc_k_headers_b
5143 WHERE id = p_khr_id;
5144
5145 CURSOR get_book_type_code(p_ast_id IN NUMBER) IS
5146 SELECT fbc.book_type_code
5147 FROM fa_deprn_summary fds,
5148 fa_book_controls fbc
5149 WHERE fbc.book_class = 'CORPORATE'
5150 AND fds.book_type_code = fbc.book_type_code
5151 AND fds.asset_id = p_ast_id
5152 and rownum = 1;
5153
5154 CURSOR get_asset_units(p_ast_id IN NUMBER) IS
5155 SELECT CURRENT_UNITS
5156 FROM fa_additions
5157 WHERE asset_id = p_ast_id;
5158
5159 l_book_type_code fa_book_controls.book_type_code%TYPE;
5160 rel_date DATE;
5161 l_units NUMBER := 0;
5162
5163 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
5164 x_msg_count NUMBER := OKL_API.G_MISS_NUM;
5165 x_msg_data VARCHAR(2000);
5166 l_api_name CONSTANT VARCHAR2(30) := 'GET_NBV';
5167 l_api_version CONSTANT NUMBER := 1.0;
5168 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
5169 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
5170 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
5171 l_nbv NUMBER;
5172 l_converted_amount NUMBER;
5173 l_contract_currency OKL_K_HEADERS_FULL_V.currency_code%TYPE;
5174 l_currency_conversion_type OKL_K_HEADERS_FULL_V.currency_conversion_type%TYPE;
5175 l_currency_conversion_rate OKL_K_HEADERS_FULL_V.currency_conversion_rate%TYPE;
5176 l_currency_conversion_date OKL_K_HEADERS_FULL_V.currency_conversion_date%TYPE;
5177 p_init_msg_list VARCHAR(1) := 'T';
5178
5179 BEGIN
5180 IF p_chr_id IS NULL OR p_chr_id = OKL_API.G_MISS_NUM THEN
5181 OKL_API.set_message (
5182 p_app_name => G_APP_NAME
5183 ,p_msg_name => G_INVALID_VALUE1
5184 ,p_token1 => 'COL_NAME'
5185 ,p_token1_value => 'Contract Id');
5186
5187 Raise OKL_API.G_EXCEPTION_ERROR;
5188 END IF;
5189
5190 IF p_asset_id IS NULL OR p_asset_id = OKL_API.G_MISS_NUM THEN
5191 OKL_API.set_message (
5192 p_app_name => G_APP_NAME
5193 ,p_msg_name => G_INVALID_VALUE1
5194 ,p_token1 => 'COL_NAME'
5195 ,p_token1_value => 'Asset Id');
5196
5197 Raise OKL_API.G_EXCEPTION_ERROR;
5198 END IF;
5199
5200 open get_rel_date(p_chr_id);
5201 fetch get_rel_date into rel_date;
5202 close get_rel_date;
5203
5204 open get_book_type_code(p_asset_id);
5205 fetch get_book_type_code into l_book_type_code;
5206 close get_book_type_code ;
5207
5208 open get_asset_units(p_asset_id);
5209 fetch get_asset_units into l_units;
5210 close get_asset_units;
5211
5212 l_asset_hdr_rec.asset_id := p_asset_id;
5213 l_asset_hdr_rec.book_type_code := l_book_type_code;
5214
5215 if NOT fa_cache_pkg.fazcbc(x_book => l_asset_hdr_rec.book_type_code) then
5216 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5217 p_msg_name => 'OKL_LLA_FA_CACHE_ERROR'
5218 );
5219 Raise OKL_API.G_EXCEPTION_ERROR;
5220 end if;
5221
5222 -- To fetch Asset Current Cost
5223 if not FA_UTIL_PVT.get_asset_fin_rec
5224 (p_asset_hdr_rec => l_asset_hdr_rec,
5225 px_asset_fin_rec => l_asset_fin_rec,
5226 p_transaction_header_id => NULL,
5227 p_mrc_sob_type_code => 'P'
5228 ) then
5229
5230 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5231 p_msg_name => 'OKL_LLA_FA_ASSET_FIN_REC_ERROR'
5232 );
5233 Raise OKL_API.G_EXCEPTION_ERROR;
5234 end if;
5235
5236 -- To fetch Depreciation Reserve
5237 if not FA_UTIL_PVT.get_asset_deprn_rec
5238 (p_asset_hdr_rec => l_asset_hdr_rec ,
5239 px_asset_deprn_rec => l_asset_deprn_rec,
5240 p_period_counter => NULL,
5241 p_mrc_sob_type_code => 'P'
5242 ) then
5243 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
5244 p_msg_name => 'OKL_LLA_FA_DEPRN_REC_ERROR'
5245 );
5246 Raise OKL_API.G_EXCEPTION_ERROR;
5247 end if;
5248
5249 l_nbv := l_asset_fin_rec.cost - l_asset_deprn_rec.deprn_reserve;
5250
5251 l_converted_amount := 0;
5252 OKL_ACCOUNTING_UTIL.CONVERT_TO_CONTRACT_CURRENCY(
5253 p_khr_id => p_chr_id,
5254 p_from_currency => NULL,
5255 p_transaction_date => rel_date,
5256 p_amount => l_nbv,
5257 x_return_status => x_return_status,
5258 x_contract_currency => l_contract_currency,
5259 x_currency_conversion_type => l_currency_conversion_type,
5260 x_currency_conversion_rate => l_currency_conversion_rate,
5261 x_currency_conversion_date => l_currency_conversion_date,
5262 x_converted_amount => l_converted_amount);
5263
5264 IF(x_return_status <> Okl_Api.G_RET_STS_SUCCESS) THEN
5265 Okl_Api.Set_Message(p_app_name => Okl_Api.G_APP_NAME,
5266 p_msg_name => 'OKL_CONV_TO_FUNC_CURRENCY_FAIL');
5267
5268 RAISE Okl_Api.G_EXCEPTION_ERROR;
5269 END IF;
5270
5271 l_nbv := l_converted_amount;
5272 if (l_units > 1) then
5273 l_nbv := l_nbv/l_units;
5274 end if;
5275
5276 RETURN l_nbv;
5277 --Call end Activity
5278 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
5279 EXCEPTION
5280
5281 WHEN OTHERS THEN
5282 null;
5283 END get_fa_nbv;
5284 --rbruno bug 6185552 end
5285
5286 END OKL_AM_UTIL_PVT;