1 package body ar_cmgt_ame_utils as
2 /* $Header: ARCMGAUB.pls 120.0 2005/08/24 21:11:24 bsarkar noship $ */
3
4 FUNCTION get_credit_limit ( transactionIdIn IN NUMBER)
5 return NUMBER AS
6 l_credit_limit NUMBER;
7 BEGIN
8 SELECT recommendation_value2
9 INTO l_credit_limit
10 FROM ar_cmgt_cf_recommends
11 WHERE case_folder_id = transactionIdIn
12 AND credit_recommendation = 'CREDIT_LIMIT';
13
14 return(l_credit_limit);
15 EXCEPTION
16 WHEN NO_DATA_FOUND THEN
17 return(null);
18 WHEN OTHERS THEN
19 return(null);
20
21 END;
22
23 FUNCTION get_txn_credit_limit ( transactionIdIn IN NUMBER)
24 return NUMBER AS
25 l_credit_limit NUMBER;
26 BEGIN
27 SELECT recommendation_value2
28 INTO l_credit_limit
29 FROM ar_cmgt_cf_recommends
30 WHERE case_folder_id = transactionIdIn
31 AND credit_recommendation = 'TXN_CREDIT_LIMIT';
32
33 return(l_credit_limit);
34 EXCEPTION
35 WHEN NO_DATA_FOUND THEN
36 return(null);
37 WHEN OTHERS THEN
38 return(null);
39
40 END;
41
42 FUNCTION get_score ( transactionIdIn IN NUMBER)
43 return NUMBER AS
44 l_score NUMBER;
45 BEGIN
46 SELECT SUM(SCORE)
47 INTO l_score
48 FROM ar_cmgt_cf_dtls
49 WHERE case_folder_id = transactionIdIn;
50
51 return(l_score);
52 EXCEPTION
53 WHEN NO_DATA_FOUND THEN
54 return(null);
55 WHEN OTHERS THEN
56 return(null);
57
58 END;
59
60 FUNCTION get_credit_classification ( transactionIdIn IN NUMBER)
61 return VARCHAR2 AS
62 l_credit_classification VARCHAR2(30);
63 BEGIN
64 SELECT recommendation_value1
65 INTO l_credit_classification
66 FROM ar_cmgt_cf_recommends
67 WHERE case_folder_id = transactionIdIn
68 AND credit_recommendation = 'CLASSIFICATION';
69
70 return(l_credit_classification);
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 return(null);
74 WHEN OTHERS THEN
75 return(null);
76
77 END;
78
79
80 FUNCTION get_credit_limit_currency ( transactionIdIn IN NUMBER)
81 return VARCHAR2 AS
82 l_credit_currency ar_cmgt_case_folders.limit_currency%type;
83 BEGIN
84 SELECT limit_currency
85 INTO l_credit_currency
86 FROM ar_cmgt_case_folders
87 WHERE case_folder_id = transactionIdIn;
88
89
90 return(l_credit_currency);
91
92 EXCEPTION
93 WHEN NO_DATA_FOUND THEN
94 return(null);
95 WHEN OTHERS THEN
96 return(null);
97
98 END;
99
100 FUNCTION get_exchange_rate_type ( transactionIdIn IN NUMBER)
101 return VARCHAR2 AS
102 l_exchange_rate_type ar_cmgt_case_folders.exchange_rate_type%type;
103 BEGIN
104 SELECT exchange_rate_type
105 INTO l_exchange_rate_type
106 FROM ar_cmgt_case_folders
107 WHERE case_folder_id = transactionIdIn;
108
109 return(l_exchange_rate_type);
110
111 EXCEPTION
112 WHEN NO_DATA_FOUND THEN
113 return(null);
114 WHEN OTHERS THEN
115 return(null);
116
117 END;
118
119 FUNCTION get_amount_requested ( transactionIdIn IN NUMBER)
120 return NUMBER AS
121 l_amount_requested ar_cmgt_credit_requests.limit_amount%type;
122 BEGIN
123 select nvl(a.limit_amount,a.trx_amount)
124 into l_amount_requested
125 from ar_cmgt_credit_requests a,
126 ar_cmgt_case_folders b
127 WHERE b.case_folder_id = transactionIdIn
128 AND a.credit_request_id = b.credit_request_id
129 AND b.type = 'CASE';
130
131 return(l_amount_requested);
132
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 return (null);
136 WHEN OTHERS THEN
137 return(null);
138
139 END;
140
141 END ar_cmgt_ame_utils;
[Home] [Help]