[Home] [Help]
PACKAGE BODY: APPS.GL_EXCH_RATES_SYNC_PKG
Source
1 PACKAGE BODY GL_EXCH_RATES_SYNC_PKG AS
2 /* $Header: glexrasb.pls 120.0.12010000.5 2009/02/05 11:43:25 sommukhe noship $ */
3
4 PROCEDURE get_cur_conv_rates(
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 p_from_currency IN VARCHAR2 DEFAULT NULL,
8 p_to_currency IN VARCHAR2 DEFAULT NULL,
9 p_from_date IN DATE,
10 p_to_date IN DATE DEFAULT SYSDATE,
11 p_conversion_rate_type IN VARCHAR2 DEFAULT NULL,
12 p_cur_conv_rates OUT NOCOPY GL_CUR_CONV_RATE_OBJ_TBL
13 )
14 IS
15 --------------------------------------------------
16 --------****Declaring Local Variables****---------
17 --------------------------------------------------
18 l_dir_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
19 l_cur_conv_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
20 l_cur_conv_inv_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
21 l_cr_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
22 l_dir_cr_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
23 l_inv_cr_rates GL_CUR_CONV_RATE_OBJ_TBL := GL_CUR_CONV_RATE_OBJ_TBL();
24 l_pivot_currency gl_cross_rate_rules.pivot_currency%TYPE;
25 l_description gl_cross_rate_rules.description%TYPE;
26 l_contra_currency gl_daily_rates.from_currency%TYPE;
27 l_conversion_type gl_daily_conversion_types.conversion_type%TYPE;
28 l_to_date DATE;
29 l_n_dir_cntr NUMBER(7);
30 l_n_inv_cntr NUMBER(7);
31 l_n_dir_cr_cntr NUMBER(7);
32 l_n_inv_cr_cntr NUMBER(7);
33
34 --Cursor to get the Pivot Currency for the given Conversion Type
35 CURSOR c_pivot_curr IS
36 SELECT pivot_currency,description
37 FROM gl_cross_rate_rules
38 WHERE conversion_type = l_conversion_type
39 AND (pivot_currency = p_from_currency OR pivot_currency = p_to_currency);
40
41 --Cursor to get the Contra Currency of Conversion Type for the Entered Parameters
42 CURSOR c_con_curr IS
43 SELECT cr_cur_tbl.from_currency
44 FROM
45 (SELECT DISTINCT from_currency from_currency
46 FROM gl_cross_rate_rule_dtls
47 WHERE conversion_type = l_conversion_type
48 AND enabled_flag = 'Y')cr_cur_tbl
49 WHERE (cr_cur_tbl.from_currency = p_from_currency OR cr_cur_tbl.from_currency = p_to_currency);
50
51 --Cursor to fetch the Conversion Type
52 CURSOR c_get_conv_type IS
53 SELECT conversion_type
54 FROM gl_daily_conversion_types
55 WHERE user_conversion_type = p_conversion_rate_type;
56
57 --Cursor to Fetch the Direct Rates and Inverse Rates for the Entered Parameters
58 CURSOR c_get_rates(c_from_currency VARCHAR2,
59 c_to_currency VARCHAR2,
60 c_from_date DATE,
61 c_to_date DATE,
62 c_conversion_rate_type VARCHAR2) IS
63 SELECT gldr.from_currency from_currency,
64 gldr.to_currency to_currency,
65 gldr.conversion_date conversion_date,
66 gdct.user_conversion_type conversion_type,
67 gldr.conversion_rate conversion_rate,
68 1/conversion_rate inv_conv_rate,
69 gldr.status_code status_code,
70 gldr.rate_source_code rate_source_code,
71 --decode(glcrs.pivot_currency,gldr.from_currency,gldr.from_currency,gldr.to_currency,gldr.to_currency,null) pivot_currency,
72 null pivot_currency,
73 --decode(glcrs.pivot_currency,gldr.from_currency,glcrs.description,gldr.to_currency,glcrs.description,null) description
74 null description
75 FROM gl_daily_rates gldr,
76 gl_cross_rate_rules glcrs,
77 gl_daily_conversion_types gdct
78 WHERE gldr.conversion_date between c_from_date and c_to_date
79 AND nvl2(c_conversion_rate_type,gldr.conversion_type,1) = nvl(c_conversion_rate_type,1)
80 AND gldr.conversion_type = glcrs.conversion_type(+)
81 AND gdct.conversion_type = gldr.conversion_type
82 AND nvl2(c_from_currency,gldr.from_currency,1) = nvl(c_from_currency,1)
83 AND nvl2(c_to_currency,gldr.to_currency,1) = nvl(c_to_currency,1);
84
85 --Cursor to Fetch the Corss Rates for the Entered parameters
86 CURSOR c_cr_rates(c_from_currency VARCHAR2,
87 c_to_currency VARCHAR2,
88 c_from_date DATE,
89 c_to_date DATE,
90 c_conversion_rate_type VARCHAR2,
91 c_contra_currency VARCHAR2) IS
92 SELECT gldr.from_currency from_currency,
93 gldr.to_currency to_currency,
94 gldr.conversion_date conversion_date,
95 gdct.user_conversion_type conversion_type,
96 gldr.conversion_rate conversion_rate,
97 1/conversion_rate inv_conv_rate,
98 gldr.status_code status_code,
99 gldr.rate_source_code rate_source_code,
100 --decode(glcrs.pivot_currency,gldr.from_currency,gldr.from_currency,gldr.to_currency,gldr.to_currency,null) pivot_currency,
101 l_pivot_currency pivot_currency,
102 --decode(glcrs.pivot_currency,gldr.from_currency,glcrs.description,gldr.to_currency,glcrs.description,null) description
103 l_description description
104 FROM gl_daily_rates gldr,
105 gl_cross_rate_rules glcrs,
106 gl_daily_conversion_types gdct
107 WHERE gldr.conversion_date between c_from_date and c_to_date
108 AND nvl2(c_conversion_rate_type,gldr.conversion_type,1) = nvl(c_conversion_rate_type,1)
109 AND gldr.conversion_type = glcrs.conversion_type
110 AND gdct.conversion_type = gldr.conversion_type
111 AND gldr.from_currency = c_contra_currency
112 AND gldr.to_currency IN (SELECT DISTINCT from_currency
113 FROM gl_cross_rate_rule_dtls
114 WHERE nvl2(c_conversion_rate_type,conversion_type,1) = nvl(c_conversion_rate_type,1)
115 AND enabled_flag = 'Y');
116
117 --Cursor to Fetch the Inverse cross rates for the Entered Parameters
118 CURSOR c_inv_cr_rates(c_from_currency VARCHAR2,
119 c_to_currency VARCHAR2,
120 c_from_date DATE,
121 c_to_date DATE,
122 c_conversion_rate_type VARCHAR2,
123 c_contra_currency VARCHAR2) IS
124 SELECT gldr.from_currency from_currency,
125 gldr.to_currency to_currency,
126 gldr.conversion_date conversion_date,
127 gdct.user_conversion_type conversion_type,
128 gldr.conversion_rate conversion_rate,
129 1/conversion_rate inv_conv_rate,
130 gldr.status_code status_code,
131 gldr.rate_source_code rate_source_code,
132 --decode(glcrs.pivot_currency,gldr.from_currency,gldr.from_currency,gldr.to_currency,gldr.to_currency,null) pivot_currency,
133 l_pivot_currency pivot_currency,
134 --decode(glcrs.pivot_currency,gldr.from_currency,glcrs.description,gldr.to_currency,glcrs.description,null) description
135 l_description description
136 FROM gl_daily_rates gldr,
137 gl_cross_rate_rules glcrs,
138 gl_daily_conversion_types gdct
139 WHERE gldr.conversion_date between c_from_date and c_to_date
140 AND nvl2(c_conversion_rate_type,gldr.conversion_type,1) = nvl(c_conversion_rate_type,1)
141 AND gldr.conversion_type = glcrs.conversion_type
142 AND gdct.conversion_type = gldr.conversion_type
143 AND gldr.to_currency = c_contra_currency
144 AND gldr.from_currency IN (SELECT DISTINCT from_currency
145 FROM gl_cross_rate_rule_dtls
146 WHERE nvl2(c_conversion_rate_type,conversion_type,1) = nvl(c_conversion_rate_type,1)
147 AND enabled_flag = 'Y');
148
149
150 BEGIN
151 l_n_dir_cntr := 0;
152 l_n_inv_cntr := 0;
153 l_n_dir_cr_cntr := 0;
154 l_n_inv_cr_cntr := 0;
155
156
157
158
159 l_conversion_type := p_conversion_rate_type;
160
161
162 fnd_file.put_line( fnd_file.log,'Fetching the Daily Rates for the entered Currencies ');
163
164 IF p_to_date IS NULL THEN
165 l_to_date := SYSDATE;
166 ELSE
167 l_to_date := p_to_date;
168 END IF;
169
170 --For the Direct Rates
171 FOR rec_c_get_rates IN c_get_rates(p_from_currency,
172 p_to_currency,
173 p_from_date,
174 l_to_date,
175 p_conversion_rate_type)
176 LOOP
177 l_cur_conv_rates.EXTEND;
178 l_n_dir_cntr := l_n_dir_cntr+1;
179 l_cur_conv_rates(l_n_dir_cntr) := GL_CUR_CONV_RATE_OBJ(rec_c_get_rates.from_currency, rec_c_get_rates.to_currency,
180 rec_c_get_rates.conversion_date, rec_c_get_rates.conversion_type,
181 rec_c_get_rates.conversion_rate, rec_c_get_rates.inv_conv_rate,
182 rec_c_get_rates.status_code, rec_c_get_rates.rate_source_code,
183 rec_c_get_rates.pivot_currency, rec_c_get_rates.description);
184
185 END LOOP;
186
187 --For the Inverse Rates
188 FOR rec_c_get_inv_rates IN c_get_rates(p_to_currency,
189 p_from_currency,
190 p_from_date,
191 l_to_date,
192 p_conversion_rate_type)
193 LOOP
194 l_cur_conv_inv_rates.EXTEND;
195 l_n_inv_cntr := l_n_inv_cntr+1;
196 l_cur_conv_inv_rates(l_n_inv_cntr) := GL_CUR_CONV_RATE_OBJ(rec_c_get_inv_rates.from_currency, rec_c_get_inv_rates.to_currency,
197 rec_c_get_inv_rates.conversion_date, rec_c_get_inv_rates.conversion_type,
198 rec_c_get_inv_rates.conversion_rate, rec_c_get_inv_rates.inv_conv_rate,
199 rec_c_get_inv_rates.status_code, rec_c_get_inv_rates.rate_source_code,
200 rec_c_get_inv_rates.pivot_currency, rec_c_get_inv_rates.description);
201
202 END LOOP;
203
204 l_dir_rates.EXTEND;
205 l_dir_rates := l_cur_conv_rates MULTISET UNION ALL l_cur_conv_inv_rates;
206
207
208 --Open the cursr for the Pivot Currency
209 OPEN c_pivot_curr;
210 FETCH c_pivot_curr INTO l_pivot_currency,l_description;
211
212 --Open the cursr for the Contra Currency
213 OPEN c_con_curr;
214 FETCH c_con_curr INTO l_contra_currency;
215 IF (c_con_curr%FOUND AND c_pivot_curr%FOUND) THEN
216
217 --For Cross Rates
218 FOR rec_c_cr_rates IN c_cr_rates(p_from_currency,
219 p_to_currency,
220 p_from_date,
221 l_to_date,
222 p_conversion_rate_type,
223 l_contra_currency)
224 LOOP
225 l_dir_cr_rates.EXTEND;
226 l_n_dir_cr_cntr := l_n_dir_cr_cntr+1;
227 l_dir_cr_rates(l_n_dir_cr_cntr) := GL_CUR_CONV_RATE_OBJ(rec_c_cr_rates.from_currency, rec_c_cr_rates.to_currency,
228 rec_c_cr_rates.conversion_date, rec_c_cr_rates.conversion_type,
229 rec_c_cr_rates.conversion_rate, rec_c_cr_rates.inv_conv_rate,
230 rec_c_cr_rates.status_code, rec_c_cr_rates.rate_source_code,
231 rec_c_cr_rates.pivot_currency, rec_c_cr_rates.description);
232 END LOOP;
233
234 --For Inverse of Cross Rates
235 FOR rec_c_inv_cr_rates IN c_inv_cr_rates(p_from_currency,
236 p_to_currency,
237 p_from_date,
238 l_to_date,
239 p_conversion_rate_type,
240 l_contra_currency)
241 LOOP
242 l_inv_cr_rates.EXTEND;
243 l_n_inv_cr_cntr := l_n_inv_cr_cntr+1;
244 l_inv_cr_rates(l_n_inv_cr_cntr) := GL_CUR_CONV_RATE_OBJ(rec_c_inv_cr_rates.from_currency, rec_c_inv_cr_rates.to_currency,
245 rec_c_inv_cr_rates.conversion_date, rec_c_inv_cr_rates.conversion_type,
246 rec_c_inv_cr_rates.conversion_rate, rec_c_inv_cr_rates.inv_conv_rate,
247 rec_c_inv_cr_rates.status_code, rec_c_inv_cr_rates.rate_source_code,
248 rec_c_inv_cr_rates.pivot_currency, rec_c_inv_cr_rates.description);
249 END LOOP;
250 fnd_file.put_line( fnd_file.log,'Assigning Cross Rates to the OUT Parameter');
251 l_cr_rates.EXTEND;
252 l_cr_rates := l_dir_cr_rates MULTISET UNION ALL l_inv_cr_rates;
253
254 --Assigning the Direct Rates and Cross Rates to the OUT parameter of Object Type.
255 p_cur_conv_rates := l_dir_rates MULTISET UNION ALL l_cr_rates;
256 CLOSE c_con_curr;
257 CLOSE c_pivot_curr;
258
259 ELSE
260 --Assinging the Direct Rates to the OUT parameter if Cross Rate Records are not there.
261 p_cur_conv_rates := l_dir_rates;
262 fnd_file.put_line( fnd_file.log,'There Are No Cross Rates for the entered currencies');
263 CLOSE c_con_curr;
264 CLOSE c_pivot_curr;
265 END IF;
266
267
268 END get_cur_conv_rates;
269
270 END GL_EXCH_RATES_SYNC_PKG;