1 PACKAGE BODY FEM_DIS_UTL_PKG AS
2 /* $Header: fem_dis_utl.plb 120.1 2005/10/27 05:30:28 appldev noship $ */
3
4 c_conversion_type CONSTANT VARCHAR2(20) := 'Spot';
5 e_no_end_date_def_err EXCEPTION;
6
7 FUNCTION Visual_Trace_URL(
8 p_function_name IN VARCHAR2,
9 p_other_params IN VARCHAR2 DEFAULT NULL
10 ) RETURN VARCHAR2 IS
11
12 v_func_id NUMBER;
13 v_session_id NUMBER;
14 v_url VARCHAR2(256);
15
16 BEGIN
17
18 SELECT FUNCTION_ID
19 INTO v_func_id
20 FROM FND_FORM_FUNCTIONS
21 WHERE FUNCTION_NAME = Visual_Trace_URL.p_function_name;
22
23
24 IF FND_LOG.Level_Procedure >= FND_LOG.G_Current_Runtime_Level THEN
25 FND_LOG.String(FND_LOG.Level_Procedure,
26 'fem.plsql.fem_dis_utl_pkg.visual_trace_url.start',
27 'Executing function: '|| p_function_name ||
28 ', other_params: ' || p_other_params);
29 END IF;
30
31 v_session_id := FND_PROFILE.Value('ICX_SESSION_ID');
32
33 IF v_session_id IS NOT NULL THEN
34
35 FND_SESSION_MANAGEMENT.InitializeSSWAGlobals(
36 p_session_id => v_session_id,
37 p_resp_appl_id => fnd_global.resp_appl_id,
38 p_responsibility_id => fnd_global.resp_id,
39 p_security_group_id => fnd_global.security_group_id,
40 p_function_id => v_func_id
41 );
42
43 END IF;
44
45 v_url := FND_RUN_FUNCTION.Get_Run_Function_URL(
46 P_FUNCTION_ID => v_func_id,
47 P_RESP_APPL_ID => fnd_global.resp_appl_id,
48 P_RESP_ID => fnd_global.resp_id,
49 P_SECURITY_GROUP_ID => fnd_global.security_group_id,
50 P_PARAMETERS => p_other_params
51 );
52
53 RETURN(v_url);
54
55 EXCEPTION
56 WHEN NO_DATA_FOUND THEN
57 RETURN(NULL);
58 END;
59
60 PROCEDURE get_exchange_rate(
61 p_from_cur IN VARCHAR2,
62 p_to_cur IN VARCHAR2,
63 p_cal_period IN NUMBER,
64 p_from_val IN NUMBER,
65 x_to_val OUT NOCOPY NUMBER,
66 x_dtor OUT NOCOPY NUMBER,
67 x_ntor OUT NOCOPY NUMBER
68 )
69 IS
70 l_end_date DATE := NULL;
71 l_start_date DATE := NULL;
72 l_conv_rate NUMBER := NULL;
73
74 BEGIN
75
76 BEGIN
77 --get the end date from cal period id
78 SELECT A.date_assign_value
79 INTO l_end_date
80 FROM fem_cal_periods_attr A,
81 fem_dim_attributes_b B,
82 fem_dim_attr_versions_b C
83 WHERE A.cal_period_id = p_cal_period
84 AND A.attribute_id = B.attribute_id
85 AND B.attribute_varchar_label = 'CAL_PERIOD_END_DATE'
86 AND C.attribute_id = A.attribute_id
87 AND C.version_id = A.version_id
88 AND C.default_version_flag = 'Y';
89
90 --get the start date from cal period id
91 SELECT A.date_assign_value
92 INTO l_start_date
93 FROM fem_cal_periods_attr A,
94 fem_dim_attributes_b B,
95 fem_dim_attr_versions_b C
96 WHERE A.cal_period_id = p_cal_period
97 AND A.attribute_id = B.attribute_id
98 AND B.attribute_varchar_label = 'CAL_PERIOD_START_DATE'
99 AND C.attribute_id = A.attribute_id
100 AND C.version_id = A.version_id
101 AND C.default_version_flag = 'Y';
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 RAISE e_no_end_date_def_err;
106 END;
107 -- This api rounds the TO value to precision and minimum accountable unit of
108 -- the TO currency
109 IF(l_end_date IS NOT NULL) THEN
110
111 GL_CURRENCY_API.convert_closest_amount(
112 x_from_currency => p_from_cur,
113 x_to_currency => p_to_cur,
114 x_conversion_date => l_end_date,
115 x_conversion_type => c_conversion_type,
116 x_user_rate => NULL,
117 x_amount => p_from_val,
118 x_max_roll_days => (l_end_date - l_start_date),
119 x_converted_amount => x_to_val,
120 x_denominator => x_dtor,
121 x_numerator => x_ntor,
122 x_rate => l_conv_rate
123 );
124 ELSE
125 RAISE e_no_end_date_def_err;
126 END IF;
127
128 END get_exchange_rate;
129
130 FUNCTION get_converted_amount(
131 p_from_currency IN VARCHAR2,
132 p_to_currency IN VARCHAR2,
133 p_cal_period_id IN NUMBER,
134 p_from_value IN NUMBER
135 )
136 RETURN NUMBER
137 IS
138 l_denominator NUMBER;
139 l_numerator NUMBER;
140 x_to_value NUMBER;
141 BEGIN
142
143 -- Check if both currencies are identical
144 IF ( p_from_currency = p_to_currency ) THEN
145 RETURN p_from_value;
146 ELSIF( p_from_currency IS NULL OR p_to_currency IS NULL ) THEN
147 RAISE gl_currency_api.INVALID_CURRENCY;
148 END IF;
149
150 --Get the converted amount
151 get_exchange_rate(
152 p_from_cur => p_from_currency,
153 p_to_cur => p_to_currency,
154 p_cal_period => p_cal_period_id,
155 p_from_val => p_from_value,
156 x_to_val => x_to_value,
157 x_ntor => l_numerator,
158 x_dtor => l_denominator
159 );
160
161 RETURN x_to_value;
162
163 EXCEPTION
164 WHEN VALUE_ERROR THEN
165 --x_ret_code := FND_API.G_FALSE;
166 x_to_value := NULL;
167 RETURN x_to_value;
168
169 WHEN gl_currency_api.INVALID_CURRENCY THEN
170 --x_ret_code := FND_API.G_FALSE;
171 x_to_value := NULL;
172 RETURN x_to_value;
173
174 WHEN gl_currency_api.NO_RATE THEN
175 --x_ret_code := FND_API.G_FALSE;
176 x_to_value := NULL;
177 RETURN x_to_value;
178
179 WHEN e_no_end_date_def_err THEN
180 --x_ret_code := -1;
181 x_to_value := NULL;
182 RETURN x_to_value;
183
184 WHEN OTHERS THEN
185 --x_ret_code := FND_API.G_FALSE;
186 x_to_value := NULL;
187 RETURN x_to_value;
188
189 END get_converted_amount;
190
191 /****************************************************************************
192
193 Get Dim Attribute Value
194
195 This API is a wrapper over the FEM_DIM_ATTRIBUTES_UTIL_PKG and returns the
196 member value of an attribute assignment of either a dimension member or a
197 dimension member/value set combination. If an attribute version is not
198 specified, the default is used.
199
200 ******************************************************************************/
201
202
203 FUNCTION Get_Dim_Attribute_Value(
204 p_dimension_varchar_label IN VARCHAR2,
205 p_attribute_varchar_label IN VARCHAR2,
206 p_member_id IN NUMBER,
207 p_value_set_id IN NUMBER DEFAULT NULL,
208 p_attr_version_display_code IN VARCHAR2 DEFAULT NULL,
209 p_return_attr_assign_mbr_id IN VARCHAR2 DEFAULT NULL
210 ) RETURN VARCHAR2
211 IS
212
213 x_return_status VARCHAR2(100);
214 x_msg_count NUMBER;
215 x_msg_data VARCHAR2(1000);
216 l_attribute_value VARCHAR2(150);
217
218 BEGIN
219
220 l_attribute_value:=FEM_DIM_ATTRIBUTES_UTIL_PKG.Get_Dim_Attribute_Value (
221 x_return_status => x_return_status,
222 x_msg_count => x_msg_count,
223 x_msg_data => x_msg_data,
224 p_dimension_varchar_label => p_dimension_varchar_label,
225 p_attribute_varchar_label => p_attribute_varchar_label,
226 p_member_id => p_member_id,
227 p_value_set_id => p_value_set_id,
228 p_attr_version_display_code => p_attr_version_display_code,
229 p_return_attr_assign_mbr_id => p_return_attr_assign_mbr_id
230 );
231
232 RETURN l_attribute_value;
233
234 END Get_Dim_Attribute_Value;
235
236 /*******************************************************************************
237
238 Get_Relative_cal_period_name
239
240 Given a base calendar period ID and an offset count this API returns the period
241 name for the offset period. The API retrieves the Relative Cal Period ID info
242 from the FEM_DIMENSION_UTIL_PKG.
243
244 *******************************************************************************/
245
246 FUNCTION Get_Relative_cal_period_name(p_base_cal_period_id NUMBER,
247 p_offset NUMBER)
248 RETURN VARCHAR2 IS
249 x_return_status VARCHAR2(100);
250 x_msg_count NUMBER;
251 x_msg_data VARCHAR2(1000);
252 l_prev_cal_period_name VARCHAR2(1000);
253 l_prev_cal_period_id NUMBER;
254
255 CURSOR c_cal_period_name(p_period_id NUMBER ) IS
256 SELECT cal_period_name INTO l_prev_cal_period_name
257 FROM fem_cal_periods_vl
258 WHERE cal_period_id =p_period_id;
259
260 BEGIN
261 l_prev_cal_period_id:= FEM_DIMENSION_UTIL_PKG.Relative_Cal_Period_ID (
262 x_return_status => x_return_status ,
263 x_msg_count =>x_msg_count ,
264 x_msg_data =>x_msg_data ,
265 p_per_num_offset => p_offset,
266 p_base_cal_period_id => p_base_cal_period_id
267 );
268
269 OPEN c_cal_period_name (l_prev_cal_period_id);
270 FETCH c_cal_period_name INTO l_prev_cal_period_name;
271 CLOSE c_cal_period_name;
272
273 RETURN l_prev_cal_period_name;
274
275 EXCEPTION when others THEN
276 IF c_cal_period_name%isopen THEN
277 CLOSE c_cal_period_name;
278 END IF;
279 RETURN null;
280 END Get_Relative_cal_period_name;
281
282 END FEM_DIS_UTL_PKG;
[Home] [Help]