[Home] [Help]
PACKAGE BODY: APPS.GL_CRM_UTILITIES_PKG
Source
1 PACKAGE BODY GL_CRM_UTILITIES_PKG AS
2 /* $Header: glcrmutb.pls 120.12.12010000.2 2008/08/13 12:00:43 kmotepal ship $ */
3 PROCEDURE change_flag(
4 flag BOOLEAN) IS
5 BEGIN
6 enable_trigger := flag;
7 END change_flag;
8
9 -------------------------------------------------------------------
10 PROCEDURE print_report_title IS
11 c_text_files VARCHAR2(200);
12 BEGIN
13 fnd_file.put_line(fnd_file.output, ' ');
14 fnd_message.set_name('SQLGL', 'CRM0023');
15 c_text_files :=
16 RPAD(fnd_message.get || ' '
17 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MM:SS'),
18 36, ' ');
19 fnd_message.set_name('SQLGL', 'CRM0025');
20 c_text_files := c_text_files || LPAD(fnd_message.get, 48, ' ');
21 fnd_message.set_name('SQLGL', 'CRM0024');
22 c_text_files :=
23 c_text_files || LPAD(fnd_message.get || ' ' || page_count, 49, ' ');
24 fnd_file.put_line(fnd_file.output, c_text_files);
25 page_line_count := page_line_count + 2;
26 END print_report_title;
27
28 -------------------------------------------------------------------
29 PROCEDURE print_validation_failure IS
30 c_text_files VARCHAR2(200);
31 BEGIN
32 fnd_file.put_line(fnd_file.output, ' ');
33 fnd_file.put_line(fnd_file.output, ' ');
34 fnd_message.set_name('SQLGL', 'CRM0026');
35 fnd_file.put_line(fnd_file.output,
36 LPAD(' ', 45, '=') || fnd_message.get
37 || RPAD(' ', 45, '='));
38 fnd_file.put_line(fnd_file.output, ' ');
39 fnd_message.set_name('SQLGL', 'CRM0032');
40 c_text_files := RPAD(fnd_message.get, 31, ' ');
41 fnd_message.set_name('SQLGL', 'CRM0033');
42 c_text_files := c_text_files || RPAD(fnd_message.get, 15, ' ');
43 fnd_message.set_name('SQLGL', 'CRM0034');
44 c_text_files := c_text_files || RPAD(fnd_message.get, 15, ' ');
45 fnd_message.set_name('SQLGL', 'CRM0035');
46 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
47 fnd_message.set_name('SQLGL', 'CRM0036');
48 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
49 fnd_message.set_name('SQLGL', 'CRM0037');
50 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
51 fnd_message.set_name('SQLGL', 'CRM0038');
52 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
53 fnd_message.set_name('SQLGL', 'CRM0039');
54 c_text_files := c_text_files || RPAD(fnd_message.get, 14, ' ');
55 fnd_file.put_line(fnd_file.output, c_text_files);
56 fnd_file.put_line(fnd_file.output,
57 LPAD(' ', 31, '-') || LPAD(' ', 15, '-')
58 || LPAD(' ', 15, '-') || LPAD(' ', 16, '-')
59 || LPAD(' ', 16, '-') || LPAD(' ', 13, '-')
60 || LPAD(' ', 13, '-') || LPAD('-', 14, '-'));
61 page_line_count := page_line_count + 6;
62 END print_validation_failure;
63
64 -------------------------------------------------------------------
65 PROCEDURE print_delete_user_rate_warning IS
66 c_text_files VARCHAR2(200);
67 BEGIN
68 fnd_file.put_line(fnd_file.output, ' ');
69 fnd_file.put_line(fnd_file.output, ' ');
70 fnd_message.set_name('SQLGL', 'CRM0027');
71 fnd_file.put_line(fnd_file.output,
72 LPAD(' ', 45, '=') || fnd_message.get
73 || RPAD(' ', 46, '='));
74 fnd_file.put_line(fnd_file.output, ' ');
75 fnd_message.set_name('SQLGL', 'CRM0032');
76 c_text_files := RPAD(fnd_message.get, 31, ' ');
77 fnd_message.set_name('SQLGL', 'CRM0040');
78 c_text_files := c_text_files || RPAD(fnd_message.get, 30, ' ');
79 fnd_message.set_name('SQLGL', 'CRM0035');
80 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
81 fnd_message.set_name('SQLGL', 'CRM0036');
82 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
83 fnd_message.set_name('SQLGL', 'CRM0037');
84 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
85 fnd_message.set_name('SQLGL', 'CRM0041');
86 c_text_files := c_text_files || RPAD(fnd_message.get, 26, ' ');
87 fnd_file.put_line(fnd_file.output, c_text_files);
88 fnd_file.put_line(fnd_file.output,
89 LPAD(' ', 31, '-') || LPAD(' ', 30, '-')
90 || LPAD(' ', 16, '-') || LPAD(' ', 16, '-')
91 || LPAD(' ', 13, '-') || LPAD('-', 26, '-'));
92 page_line_count := page_line_count + 6;
93 END print_delete_user_rate_warning;
94
95 -------------------------------------------------------------------
96 PROCEDURE print_override_user_rate_warn IS
97 c_text_files VARCHAR2(200);
98 BEGIN
99 fnd_file.put_line(fnd_file.output, ' ');
100 fnd_file.put_line(fnd_file.output, ' ');
101 fnd_message.set_name('SQLGL', 'CRM0028');
102 fnd_file.put_line(fnd_file.output,
103 LPAD(' ', 45, '=') || fnd_message.get
104 || RPAD(' ', 46, '='));
105 fnd_file.put_line(fnd_file.output, ' ');
106 fnd_message.set_name('SQLGL', 'CRM0032');
107 c_text_files := RPAD(fnd_message.get, 31, ' ');
108 fnd_message.set_name('SQLGL', 'CRM0040');
109 c_text_files := c_text_files || RPAD(fnd_message.get, 30, ' ');
110 fnd_message.set_name('SQLGL', 'CRM0035');
111 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
112 fnd_message.set_name('SQLGL', 'CRM0036');
113 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
114 fnd_message.set_name('SQLGL', 'CRM0037');
115 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
116 fnd_message.set_name('SQLGL', 'CRM0041');
117 c_text_files := c_text_files || RPAD(fnd_message.get, 26, ' ');
118 fnd_file.put_line(fnd_file.output, c_text_files);
119 fnd_file.put_line(fnd_file.output,
120 LPAD(' ', 31, '-') || LPAD(' ', 30, '-')
121 || LPAD(' ', 16, '-') || LPAD(' ', 16, '-')
122 || LPAD(' ', 13, '-') || LPAD('-', 26, '-'));
123 page_line_count := page_line_count + 6;
124 END print_override_user_rate_warn;
125
126 -------------------------------------------------------------------
127 PROCEDURE print_delete_sys_rate_warning IS
128 c_text_files VARCHAR2(200);
129 BEGIN
130 fnd_file.put_line(fnd_file.output, ' ');
131 fnd_file.put_line(fnd_file.output, ' ');
132 fnd_message.set_name('SQLGL', 'CRM0029');
133 fnd_file.put_line(fnd_file.output,
134 LPAD(' ', 43, '=') || fnd_message.get
135 || RPAD(' ', 44, '='));
136 fnd_file.put_line(fnd_file.output, ' ');
137 fnd_message.set_name('SQLGL', 'CRM0032');
138 c_text_files := RPAD(fnd_message.get, 31, ' ');
139 fnd_message.set_name('SQLGL', 'CRM0040');
140 c_text_files := c_text_files || RPAD(fnd_message.get, 30, ' ');
141 fnd_message.set_name('SQLGL', 'CRM0035');
142 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
143 fnd_message.set_name('SQLGL', 'CRM0036');
144 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
145 fnd_message.set_name('SQLGL', 'CRM0037');
146 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
147 fnd_message.set_name('SQLGL', 'CRM0041');
148 c_text_files := c_text_files || RPAD(fnd_message.get, 26, ' ');
149 fnd_file.put_line(fnd_file.output, c_text_files);
150 fnd_file.put_line(fnd_file.output,
151 LPAD(' ', 31, '-') || LPAD(' ', 30, '-')
152 || LPAD(' ', 16, '-') || LPAD(' ', 16, '-')
153 || LPAD(' ', 13, '-') || LPAD('-', 26, '-'));
154 page_line_count := page_line_count + 6;
155 END print_delete_sys_rate_warning;
156
157 -------------------------------------------------------------------
158 PROCEDURE print_override_sys_rate_warn IS
159 c_text_files VARCHAR2(200);
160 BEGIN
161 fnd_file.put_line(fnd_file.output, ' ');
162 fnd_file.put_line(fnd_file.output, ' ');
163 fnd_message.set_name('SQLGL', 'CRM0030');
164 fnd_file.put_line(fnd_file.output,
165 LPAD(' ', 43, '=') || fnd_message.get
166 || RPAD(' ', 44, '='));
167 fnd_file.put_line(fnd_file.output, ' ');
168 fnd_message.set_name('SQLGL', 'CRM0032');
169 c_text_files := RPAD(fnd_message.get, 31, ' ');
170 fnd_message.set_name('SQLGL', 'CRM0040');
171 c_text_files := c_text_files || RPAD(fnd_message.get, 30, ' ');
172 fnd_message.set_name('SQLGL', 'CRM0035');
173 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
174 fnd_message.set_name('SQLGL', 'CRM0036');
175 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
176 fnd_message.set_name('SQLGL', 'CRM0037');
177 c_text_files := c_text_files || LPAD(fnd_message.get || ' ', 13, ' ');
178 fnd_message.set_name('SQLGL', 'CRM0041');
179 c_text_files := c_text_files || RPAD(fnd_message.get, 26, ' ');
180 fnd_file.put_line(fnd_file.output, c_text_files);
181 fnd_file.put_line(fnd_file.output,
182 LPAD(' ', 31, '-') || LPAD(' ', 30, '-')
183 || LPAD(' ', 16, '-') || LPAD(' ', 16, '-')
184 || LPAD(' ', 13, '-') || LPAD('-', 26, '-'));
185 page_line_count := page_line_count + 6;
186 END print_override_sys_rate_warn;
187
188 -------------------------------------------------------------------
189 PROCEDURE print_missing_pivot_rate IS
190 c_text_files VARCHAR2(200);
191 BEGIN
192 fnd_file.put_line(fnd_file.output, ' ');
193 fnd_file.put_line(fnd_file.output, ' ');
194 fnd_message.set_name('SQLGL', 'CRM0031');
195 fnd_file.put_line(fnd_file.output,
196 LPAD(' ', 51, '=') || fnd_message.get
197 || RPAD(' ', 51, '='));
198 fnd_file.put_line(fnd_file.output, ' ');
199 fnd_message.set_name('SQLGL', 'CRM0032');
200 c_text_files := RPAD(fnd_message.get, 31, ' ');
201 fnd_message.set_name('SQLGL', 'CRM0040');
202 c_text_files := c_text_files || RPAD(fnd_message.get, 30, ' ');
203 fnd_message.set_name('SQLGL', 'CRM0042');
204 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
205 fnd_message.set_name('SQLGL', 'CRM0043');
206 c_text_files := c_text_files || RPAD(fnd_message.get, 16, ' ');
207 fnd_file.put_line(fnd_file.output, c_text_files);
208 fnd_file.put_line(fnd_file.output,
209 LPAD(' ', 31, '-') || LPAD(' ', 30, '-')
210 || LPAD(' ', 16, '-') || LPAD('-', 16, '-'));
211 page_line_count := page_line_count + 6;
212 END print_missing_pivot_rate;
213
214 -------------------------------------------------------------------
215 PROCEDURE print_validation_failure_codes IS
216 BEGIN
217 fnd_file.put_line(fnd_file.output, ' ');
218 fnd_file.put_line(fnd_file.output, ' ');
219 fnd_message.set_name('SQLGL', 'CRM0022');
220 fnd_file.put_line(fnd_file.output,
221 LPAD(' ', 61, '=') || fnd_message.get
222 || RPAD(' ', 61, '='));
223 fnd_file.put_line(fnd_file.output, ' ');
224 fnd_message.set_name('SQLGL', 'CRM0021');
225 fnd_file.put_line(fnd_file.output, fnd_message.get);
226 fnd_file.put_line(fnd_file.output, RPAD('-', 133, '-'));
227 fnd_message.set_name('SQLGL', 'CRM0007');
228 fnd_file.put_line(fnd_file.output, ' VF01 ' || fnd_message.get);
229 fnd_message.set_name('SQLGL', 'CRM0008');
230 fnd_file.put_line(fnd_file.output, ' VF02 ' || fnd_message.get);
231 fnd_message.set_name('SQLGL', 'CRM0009');
232 fnd_file.put_line(fnd_file.output, ' VF03 ' || fnd_message.get);
233 fnd_message.set_name('SQLGL', 'CRM0010');
234 fnd_file.put_line(fnd_file.output, ' VF04 ' || fnd_message.get);
235 fnd_message.set_name('SQLGL', 'CRM0011');
236 fnd_file.put_line(fnd_file.output, ' VF05 ' || fnd_message.get);
237 fnd_message.set_name('SQLGL', 'CRM0012');
238 fnd_file.put_line(fnd_file.output, ' VF06 ' || fnd_message.get);
239 fnd_message.set_name('SQLGL', 'CRM0013');
240 fnd_file.put_line(fnd_file.output, ' VF07 ' || fnd_message.get);
241 fnd_message.set_name('SQLGL', 'CRM0014');
242 fnd_file.put_line(fnd_file.output, ' VF08 ' || fnd_message.get);
243 fnd_message.set_name('SQLGL', 'CRM0015');
244 fnd_file.put_line(fnd_file.output, ' VF09 ' || fnd_message.get);
245 fnd_message.set_name('SQLGL', 'CRM0016');
246 fnd_file.put_line(fnd_file.output, ' VF10 ' || fnd_message.get);
247 fnd_message.set_name('SQLGL', 'CRM0017');
248 fnd_file.put_line(fnd_file.output, ' VF11 ' || fnd_message.get);
249 fnd_message.set_name('SQLGL', 'CRM0018');
250 fnd_file.put_line(fnd_file.output, ' VF12 ' || fnd_message.get);
251 fnd_message.set_name('SQLGL', 'CRM0019');
252 fnd_file.put_line(fnd_file.output, ' VF13 ' || fnd_message.get);
253 fnd_message.set_name('SQLGL', 'CRM0020');
254 fnd_file.put_line(fnd_file.output, ' VF14 ' || fnd_message.get);
255 fnd_message.set_name('SQLGL', 'CRM0044');
256 fnd_file.put_line(fnd_file.output, ' VF15 ' || fnd_message.get);
257 END print_validation_failure_codes;
258
259 -------------------------------------------------------------------
260 PROCEDURE insert_cross_rate_set(
261 p_conversion_type IN VARCHAR2,
262 p_contra_currency IN VARCHAR2,
263 p_login_user IN NUMBER) IS
264 existed_curr_rec curr_rec;
265 x_pivot_currency VARCHAR2(15);
266 BEGIN
267 SELECT pivot_currency
268 INTO x_pivot_currency
269 FROM gl_cross_rate_rules
270 WHERE conversion_type = p_conversion_type;
271
272 SELECT DISTINCT from_currency
273 BULK COLLECT INTO existed_curr_rec.r_from_curr
274 FROM gl_cross_rate_rule_dtls
275 WHERE conversion_type = p_conversion_type;
276
277 --IF (existed_curr_rec.r_from_curr.COUNT = 0) THEN
278 -- modify this, always insert the dummy rows, we can use those rows in VO.
279 INSERT INTO gl_cross_rate_rule_dtls
280 (conversion_type, from_currency, to_currency,
281 enabled_flag, last_update_date, last_updated_by,
282 creation_date, created_by, last_update_login)
283 VALUES (p_conversion_type, p_contra_currency, p_contra_currency,
284 'N', SYSDATE, p_login_user,
285 SYSDATE, p_login_user, p_login_user);
286
287 IF (existed_curr_rec.r_from_curr.COUNT > 0) THEN
288 /* at least one contra currency is existed */
289 FORALL i IN 1 .. existed_curr_rec.r_from_curr.COUNT
290 INSERT INTO gl_cross_rate_rule_dtls
291 (conversion_type, from_currency,
292 to_currency, enabled_flag, last_update_date,
293 last_updated_by, creation_date, created_by,
294 last_update_login)
295 VALUES (p_conversion_type, p_contra_currency,
296 existed_curr_rec.r_from_curr(i), 'Y', SYSDATE,
297 p_login_user, SYSDATE, p_login_user,
298 p_login_user);
299 FORALL i IN 1 .. existed_curr_rec.r_from_curr.COUNT
300 INSERT INTO gl_cross_rate_rule_dtls
301 (conversion_type, from_currency,
302 to_currency, enabled_flag, last_update_date,
303 last_updated_by, creation_date, created_by,
304 last_update_login)
305 VALUES (p_conversion_type, existed_curr_rec.r_from_curr(i),
306 p_contra_currency, 'Y', SYSDATE,
307 p_login_user, SYSDATE, p_login_user,
308 p_login_user);
309 END IF;
310 END insert_cross_rate_set;
311
312 -------------------------------------------------------------------
313 PROCEDURE update_cross_rate_set(
314 p_conversion_type IN VARCHAR2,
315 p_new_contra_currency IN VARCHAR2,
316 p_old_contra_currency IN VARCHAR2,
317 p_login_user IN NUMBER) IS
318 x_pivot_currency VARCHAR2(15);
319 BEGIN
320 SELECT pivot_currency
321 INTO x_pivot_currency
322 FROM gl_cross_rate_rules
323 WHERE conversion_type = p_conversion_type;
324
325 UPDATE gl_cross_rate_rule_dtls
326 SET from_currency = p_new_contra_currency,
327 last_update_date = SYSDATE,
328 last_updated_by = p_login_user,
329 last_update_login = p_login_user
330 WHERE conversion_type = p_conversion_type
331 AND from_currency = p_old_contra_currency;
332
333 UPDATE gl_cross_rate_rule_dtls
334 SET to_currency = p_new_contra_currency,
335 last_update_date = SYSDATE,
336 last_updated_by = p_login_user,
337 last_update_login = p_login_user
338 WHERE conversion_type = p_conversion_type
339 AND to_currency = p_old_contra_currency;
340 END update_cross_rate_set;
341
342 -------------------------------------------------------------------
343 PROCEDURE delete_cross_rate_set(
344 p_conversion_type IN VARCHAR2,
345 p_contra_currency IN VARCHAR2) IS
346 BEGIN
347 DELETE FROM gl_cross_rate_rule_dtls
348 WHERE conversion_type = p_conversion_type
349 AND ( from_currency = p_contra_currency
350 OR to_currency = p_contra_currency);
351 END delete_cross_rate_set;
352
353 -------------------------------------------------------------------
354 -- Created the Procedure for raising Business Events for Daily --
355 -- Rates Insert, Update and Delete Bug 4758732 JVARKEY --
356 -------------------------------------------------------------------
357 PROCEDURE raise_dr_buz_events(
358 p_from_currency VARCHAR2,
359 p_to_currency VARCHAR2,
360 p_from_conversion_date DATE,
361 p_to_conversion_date DATE,
362 p_conversion_type VARCHAR2,
363 p_conversion_rate NUMBER,
364 p_inverse_conversion_rate NUMBER,
365 p_mode_flag VARCHAR2) IS
366
367 ekey VARCHAR2(100);
368
369 BEGIN
370
371 ekey := p_from_currency||':'||p_to_currency||':'||p_conversion_type||':'
372 ||to_char(p_from_conversion_date,'RRDDDSSSSS')||':'
373 ||to_char(p_to_conversion_date,'RRDDDSSSSS')||':'
374 ||to_char(sysdate, 'RRDDDSSSSS');
375
376 IF (p_mode_flag = 'D') THEN
377
378 -- Raise the remove conversion event
379 gl_business_events.raise(
380 p_event_name =>
381 'oracle.apps.gl.CurrencyConversionRates.dailyRate.remove',
382 p_event_key => ekey,
383 p_parameter_name1 => 'FROM_CURRENCY',
384 p_parameter_value1 => p_from_currency,
385 p_parameter_name2 => 'TO_CURRENCY',
386 p_parameter_value2 => p_to_currency,
387 p_parameter_name3 => 'FROM_CONVERSION_DATE',
388 p_parameter_value3 => to_char(p_from_conversion_date,'YYYY/MM/DD'),
389 p_parameter_name4 => 'TO_CONVERSION_DATE',
390 p_parameter_value4 => to_char(p_to_conversion_date,'YYYY/MM/DD'),
391 p_parameter_name5 => 'CONVERSION_TYPE',
392 p_parameter_value5 => p_conversion_type);
393
394 ELSE
395
396 -- Raise the specify conversion event
397 gl_business_events.raise(
398 p_event_name =>
399 'oracle.apps.gl.CurrencyConversionRates.dailyRate.specify',
400 p_event_key => ekey,
401 p_parameter_name1 => 'FROM_CURRENCY',
402 p_parameter_value1 => p_from_currency,
403 p_parameter_name2 => 'TO_CURRENCY',
404 p_parameter_value2 => p_to_currency,
405 p_parameter_name3 => 'FROM_CONVERSION_DATE',
406 p_parameter_value3 => to_char(p_from_conversion_date,'YYYY/MM/DD'),
407 p_parameter_name4 => 'TO_CONVERSION_DATE',
408 p_parameter_value4 => to_char(p_to_conversion_date,'YYYY/MM/DD'),
409 p_parameter_name5 => 'CONVERSION_TYPE',
410 p_parameter_value5 => p_conversion_type,
411 p_parameter_name6 => 'CONVERSION_RATE',
412 p_parameter_value6 => to_char(p_conversion_rate,
413 '99999999999999999999.99999999999999999999'),
414 p_parameter_name7 => 'INVERSE_CONVERSION_RATE',
415 p_parameter_value7 => to_char(p_inverse_conversion_rate,
416 '99999999999999999999.99999999999999999999'));
417
418 END IF;
419
420 END raise_dr_buz_events;
421
422 -------------------------------------------------------------------
423 PROCEDURE daily_rates_import(
424 errbuf OUT NOCOPY VARCHAR2,
425 retcode OUT NOCOPY NUMBER) IS
426 ab_used VARCHAR2(1);
427 run_program VARCHAR2(1);
428 euro_code VARCHAR2(30);
429 RESULT BOOLEAN;
430 set_completion_status_result BOOLEAN;
431 user_id NUMBER;
432 req_id NUMBER;
433 rows_need_calculation NUMBER;
434 daily_rate_validation_failure daily_rate_interface_rec;
435 sys_daily_rate_cannot_delete daily_rate_rec;
436 usr_daily_rate_cannot_delete daily_rate_rec;
437 sys_daily_rate_cannot_override daily_rate_rec;
438 usr_daily_rate_cannot_override daily_rate_rec;
439 daily_rate_missing_base_rate daily_rate_rec;
440 p_pivot_currency VARCHAR2(15);
441 golden_rule_flag VARCHAR2(15);
442 l_launch_rate_change VARCHAR2(1) := 'N';
443
444 --Bug 4758732 JVARKEY Cursor to raise business events
445 CURSOR raise_buz_events IS
446 SELECT dri.from_currency,
447 dri.to_currency,
448 dri.from_conversion_date,
449 dri.to_conversion_date,
450 dct.conversion_type,
451 dri.conversion_rate,
452 NVL(dri.inverse_conversion_rate, 1/conversion_rate) inverse_conversion_rate,
453 dri.mode_flag
454 FROM gl_daily_rates_interface dri,
455 gl_daily_conversion_types dct
456 WHERE mode_flag IN ('I', 'D', 'T', 'N')
457 AND dct.user_conversion_type = dri.user_conversion_type;
458
459 BEGIN
460 -- Validate the following:
461 --
462 -- o Conversion_type exists,
463 -- o Conversion_rate is not a negative number
464 -- o Inverse_conversion_rate is not a negative number
465 -- o From_Currency and To_Currency:
466 -- a. Currency exists in the FND_CURRENCIES table
467 -- b. Currency is enabled
468 -- c. Currency is not a statistical currency
469 -- d. Currency is not out of date
470 -- e. Currency is not an EMU currency
471 -- o Range of dates specified does not exceeds 366 days
472 --
473 -- If there is any error, an appropriate error_code will be set.
474 gl_message.func_ent('Daily Rates Import');
475
476 -- GL_CRM_UTILITIES_PKG.DEBUG_MODE := TRUE;
477
478 UPDATE gl_daily_rates_interface ri
479 SET ERROR_CODE =
480 (SELECT DECODE
481 (ct.ROWID,
482 NULL, 'NONEXISTANT_CONVERSION_TYPE',
483 DECODE
484 (LEAST(TRUNC(ri2.to_conversion_date)
485 - TRUNC(ri2.from_conversion_date),
486 367),
487 367, 'DATE_RANGE_TOO_LARGE',
488 DECODE
489 (LEAST(ri.conversion_rate, 0),
490 ri.conversion_rate, 'NEGATIVE_CONVERSION_RATE',
491 DECODE
492 (LEAST
493 (NVL(ri.inverse_conversion_rate,
494 1),
495 0),
496 ri.inverse_conversion_rate, 'NEGATIVE_INVERSE_RATE',
497 DECODE
498 (from_curr.ROWID,
499 NULL, 'NONEXISTANT_FROM_CURRENCY',
500 DECODE
501 (from_curr.enabled_flag,
502 'N', 'DISABLED_FROM_CURRENCY',
503 -- Bug 4222440 JVARKEY Error the never enabled currency
504 'X', 'DISABLED_FROM_CURRENCY',
505 DECODE
506 (from_curr.currency_flag,
507 'N', 'STATISTICAL_FROM_CURRENCY',
508 DECODE
509 (from_curr.currency_code,
510 'STAT', 'STATISTICAL_FROM_CURRENCY',
511 DECODE
512 (SIGN
513 (TRUNC(SYSDATE)
514 - NVL
515 (TRUNC
516 (from_curr.start_date_active),
517 TRUNC
518 (SYSDATE))),
519 -1, 'OUT_OF_DATE_FROM_CURRENCY',
520 DECODE
521 (SIGN
522 (TRUNC
523 (SYSDATE)
524 - NVL
525 (TRUNC
526 (from_curr.end_date_active),
527 TRUNC
528 (SYSDATE))),
529 1, 'OUT_OF_DATE_FROM_CURRENCY',
530 DECODE
531 (DECODE
532 (from_curr.derive_type,
533 'EMU', SIGN
534 (TRUNC
535 (from_curr.derive_effective)
536 - TRUNC
537 (ri2.to_conversion_date)),
538 1),
539 -1, 'EMU_FROM_CURRENCY',
540 0, 'EMU_FROM_CURRENCY',
541 DECODE
542 (to_curr.ROWID,
543 NULL, 'NONEXISTANT_TO_CURRENCY',
544 DECODE
545 (to_curr.enabled_flag,
546 'N', 'DISABLED_TO_CURRENCY',
547 -- Bug 4222440 JVARKEY Error the never enabled currency
548 'X', 'DISABLED_TO_CURRENCY',
549 DECODE
550 (to_curr.currency_flag,
551 'N', 'STATISTICAL_TO_CURRENCY',
552 DECODE
553 (to_curr.currency_code,
554 'STAT', 'STATISTICAL_TO_CURRENCY',
555 DECODE
556 (SIGN
557 (TRUNC
558 (SYSDATE)
559 - NVL
560 (TRUNC
561 (to_curr.start_date_active),
562 TRUNC
563 (SYSDATE))),
564 -1, 'OUT_OF_DATE_TO_CURRENCY',
565 DECODE
566 (SIGN
567 (TRUNC
568 (SYSDATE)
569 - NVL
570 (TRUNC
571 (to_curr.end_date_active),
572 TRUNC
573 (SYSDATE))),
574 1, 'OUT_OF_DATE_TO_CURRENCY',
575 DECODE
576 (DECODE
577 (to_curr.derive_type,
578 'EMU', SIGN
579 (TRUNC
580 (to_curr.derive_effective)
581 - TRUNC
582 (ri2.to_conversion_date)),
583 1),
584 -1, 'EMU_TO_CURRENCY',
585 0, 'EMU_TO_CURRENCY',
586 ''))))))))))))))))))
587 FROM gl_daily_rates_interface ri2,
588 gl_daily_conversion_types ct,
589 fnd_currencies from_curr,
590 fnd_currencies to_curr
591 WHERE ri2.ROWID = ri.ROWID
592 AND ct.user_conversion_type(+) = ri2.user_conversion_type
593 AND from_curr.currency_code(+) = ri2.from_currency
594 AND to_curr.currency_code(+) = ri2.to_currency)
595 WHERE ri.mode_flag IN('I', 'D', 'T', 'N');
596
597 -- added N and T for Treasury team
598 -- N - no override GL rates
599 -- T - Treasury Insert (override existing GL rates).
600 /*
601 -- Update mode flag to 'X' for each erroneous row
602 UPDATE gl_daily_rates_interface
603 SET mode_flag = 'X'
604 WHERE mode_flag IN('I', 'D') AND ERROR_CODE IS NOT NULL;
605
606 -- Update mode flag to 'X' for each erroneous row from Treasury
607 UPDATE gl_daily_rates_interface
608 SET mode_flag = 'F'
609 WHERE mode_flag IN('T', 'N') AND ERROR_CODE IS NOT NULL;
610 */
611
612 UPDATE GL_DAILY_RATES_INTERFACE T1
613 SET T1.error_code = 'DUPLICATE_ROWS'
614 WHERE
615 (T1.USER_CONVERSION_TYPE,
616 T1.FROM_CONVERSION_DATE, T1.TO_CONVERSION_DATE)
617 IN
618 (SELECT T2.USER_CONVERSION_TYPE,
619 T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
620 FROM GL_DAILY_RATES_INTERFACE T2
621 WHERE (T2.FROM_CURRENCY = T1.FROM_CURRENCY
622 OR T2.FROM_CURRENCY = T1.TO_CURRENCY)
623 AND (T2.TO_CURRENCY = T1.TO_CURRENCY
624 OR T2.TO_CURRENCY = T1.FROM_CURRENCY)
625 GROUP BY T2.USER_CONVERSION_TYPE,
626 T2.FROM_CONVERSION_DATE, T2.TO_CONVERSION_DATE
627 HAVING count(*) > 1)
628 AND mode_flag IN ('I', 'D', 'T', 'N');
629
630 -- Update mode flag to X/F for each erroneous row
631 UPDATE gl_daily_rates_interface ri
632 SET mode_flag =
633 Decode(mode_flag,'T','F','N','F','I','X','D','X',mode_flag)
634 WHERE (mode_flag IN('T', 'N','I','D') AND ERROR_CODE IS NOT NULL)
635 OR (mode_flag = 'N'
636 AND (EXISTS (SELECT 1 FROM gl_daily_rates dr,
637 gl_daily_conversion_types ct
638 WHERE dr.from_currency = ri.from_currency
639 AND dr.to_currency = ri.to_currency
640 AND dr.conversion_type = ct.conversion_type
641 AND ct.user_conversion_type = ri.user_conversion_type
642 AND dr.conversion_date BETWEEN ri.from_conversion_date
643 AND ri.to_conversion_date)
644 OR EXISTS (SELECT 1 FROM gl_daily_rates dr,
645 gl_daily_conversion_types ct
646 WHERE dr.from_currency = ri.to_currency
647 AND dr.to_currency = ri.from_currency
648 AND dr.conversion_type = ct.conversion_type
649 AND ct.user_conversion_type = ri.user_conversion_type
650 AND dr.conversion_date BETWEEN ri.from_conversion_date
651 AND ri.to_conversion_date)));
652
653 IF DEBUG_MODE THEN
654 fnd_file.put_line(fnd_file.LOG,
655 SQL%ROWCOUNT || ' Error records');
656 END IF;
657
658 UPDATE gl_daily_rates_interface
659 SET inverse_conversion_rate = 1 / conversion_rate
660 WHERE inverse_conversion_rate IS NULL AND conversion_rate > 0;
661
662 IF DEBUG_MODE THEN
663 fnd_file.put_line
664 (fnd_file.LOG,
665 'Finish Validation on GL_DAILY_RATES_INTERFACE table.');
666 END IF;
667
668 SELECT from_currency,
669 to_currency,
670 from_conversion_date,
671 to_conversion_date,
672 user_conversion_type,
673 conversion_rate,
674 inverse_conversion_rate,
675 DECODE(ERROR_CODE,
676 'NONEXISTANT_CONVERSION_TYPE', 'VF01',
677 'DATE_RANGE_TOO_LARGE', 'VF02',
678 'NEGATIVE_CONVERSION_RATE', 'VF03',
679 'NEGATIVE_INVERSE_RATE', 'VF04',
680 'NONEXISTANT_FROM_CURRENCY', 'VF05',
681 'DISABLED_FROM_CURRENCY', 'VF06',
682 'STATISTICAL_FROM_CURRENCY', 'VF07',
683 'OUT_OF_DATE_FROM_CURRENCY', 'VF08',
684 'EMU_FROM_CURRENCY', 'VF09',
685 'NONEXISTANT_TO_CURRENCY', 'VF10',
686 'DISABLED_TO_CURRENCY', 'VF11',
687 'STATISTICAL_TO_CURRENCY', 'VF12',
688 'OUT_OF_DATE_TO_CURRENCY', 'VF13',
689 'EMU_TO_CURRENCY', 'VF14',
690 'DUPLICATE_ROWS','VF15',
691 'VF16')
692 BULK COLLECT INTO daily_rate_validation_failure.r_from_curr,
693 daily_rate_validation_failure.r_to_curr,
694 daily_rate_validation_failure.r_from_date,
695 daily_rate_validation_failure.r_to_date,
696 daily_rate_validation_failure.r_type,
697 daily_rate_validation_failure.r_rate,
698 daily_rate_validation_failure.r_inverse_rate,
699 daily_rate_validation_failure.r_error_code
700 FROM gl_daily_rates_interface
701 WHERE mode_flag IN('X', 'F');
702
703 FOR i IN 1 .. daily_rate_validation_failure.r_from_curr.COUNT LOOP
704 IF page_line_count = 1 THEN
705 gl_crm_utilities_pkg.print_report_title;
706 gl_crm_utilities_pkg.print_validation_failure;
707 END IF;
708
709 page_line_count := page_line_count + 1;
710 fnd_file.put_line
711 (fnd_file.output,
712 RPAD(daily_rate_validation_failure.r_type(i), 31, ' ')
713 || RPAD(daily_rate_validation_failure.r_from_date(i), 15,
714 ' ')
715 || RPAD(daily_rate_validation_failure.r_to_date(i), 15,
716 ' ')
717 || RPAD(daily_rate_validation_failure.r_from_curr(i), 16,
718 ' ')
719 || RPAD(daily_rate_validation_failure.r_to_curr(i), 16,
720 ' ')
721 || LPAD(daily_rate_validation_failure.r_rate(i) || ' ',
722 13, ' ')
723 || LPAD
724 (daily_rate_validation_failure.r_inverse_rate(i)
725 || ' ',
726 13, ' ')
727 || RPAD(daily_rate_validation_failure.r_error_code(i),
728 14, ' '));
729
730 IF page_line_count >= page_line_numbers - 2 THEN
731 page_line_count := 1;
732 page_count := page_count + 1;
733 fnd_file.put_line(fnd_file.output, ' ');
734 fnd_file.put_line(fnd_file.output, ' ');
735 END IF;
736 END LOOP;
737
738 IF DEBUG_MODE THEN
739 IF page_count * page_line_count = 1 THEN
740 fnd_file.put_line(fnd_file.LOG, 'No Validation Failure.');
741 ELSE
742 fnd_file.put_line(fnd_file.LOG,
743 page_count * page_line_count - 1
744 || ' rows failed validation.');
745 END IF;
746 END IF;
747
748 -- those validation already done by the Web ADI spreadsheet or OA pages
749 -- we do this because customer may use sqlloader to upload this to
750 -- interface table and then run Concurrent Program via SRS
751
752 -- Check if average balances is used in the system
753
754 --- will run the gl_rate_rates here, which will remove all the gl_daily_rates lines.
755 fnd_profile.get('GL_CRM_CR_OVERRIDE', golden_rule_flag);
756
757 IF DEBUG_MODE THEN
758 gl_message.write_log('CRM0004', 1, 'VALUE', golden_rule_flag);
759 END IF;
760
761 IF (golden_rule_flag = 'SYSTEM') OR(golden_rule_flag = 'BOTH') THEN
762 IF DEBUG_MODE THEN
763 fnd_file.put_line
764 (fnd_file.LOG,
765 'Searching system rates that cannot be override.');
766 END IF;
767
768 /*********************************************************************************
769 Bug 4641250 JVARKEY Changed the following query
770 *********************************************************************************/
771 SELECT dr.from_currency,
772 dr.to_currency,
773 gldct.user_conversion_type,
774 dr.conversion_date,
775 dr.conversion_rate,
776 dr.rate_source_code
777 BULK COLLECT INTO sys_daily_rate_cannot_delete.r_from_curr,
778 sys_daily_rate_cannot_delete.r_to_curr,
779 sys_daily_rate_cannot_delete.r_type,
780 sys_daily_rate_cannot_delete.r_conversion_date,
781 sys_daily_rate_cannot_delete.r_rate,
782 sys_daily_rate_cannot_delete.r_rate_source_code
783 FROM gl_daily_rates dr,
784 gl_daily_conversion_types gldct,
785 gl_row_multipliers rm,
786 gl_daily_conversion_types ct,
787 gl_daily_rates_interface ri
788 WHERE ri.mode_flag = 'D'
789 AND ct.user_conversion_type = ri.user_conversion_type || ''
790 AND rm.multiplier BETWEEN 1 AND (TRUNC(ri.to_conversion_date)
791 - TRUNC(ri.from_conversion_date)
792 + 1)
793 AND ((dr.from_currency = ri.from_currency
794 AND dr.to_currency = ri.to_currency)
795 OR (dr.from_currency = ri.to_currency
796 AND dr.to_currency = ri.from_currency))
797 AND dr.conversion_type = ct.conversion_type
798 AND dr.conversion_date = TRUNC(ri.from_conversion_date)+rm.multiplier-1
799 AND dr.rate_source_code = 'SYSTEM'
800 AND dr.conversion_type = gldct.conversion_type;
801
802 IF DEBUG_MODE THEN
803 IF sys_daily_rate_cannot_delete.r_from_curr.COUNT > 0 THEN
804 fnd_file.put_line
805 (fnd_file.LOG,
806 sys_daily_rate_cannot_delete.r_from_curr.COUNT
807 || ' system rates found (which cannot be deleted)!');
808 ELSE
809 fnd_file.put_line
810 (fnd_file.LOG,
811 'No system rate found (which cannot be deleted)!');
812 END IF;
813 END IF;
814
815 FOR i IN 1 .. sys_daily_rate_cannot_delete.r_from_curr.COUNT LOOP
816 IF page_line_count = 1 THEN
817 gl_crm_utilities_pkg.print_report_title;
818 gl_crm_utilities_pkg.print_delete_user_rate_warning;
819 ELSIF i = 1 THEN
820 gl_crm_utilities_pkg.print_delete_user_rate_warning;
821 END IF;
822
823 page_line_count := page_line_count + 1;
824 fnd_file.put_line
825 (fnd_file.output,
826 RPAD(sys_daily_rate_cannot_delete.r_type(i), 31, ' ')
827 || RPAD
828 (sys_daily_rate_cannot_delete.r_conversion_date(i),
829 30, ' ')
830 || RPAD(sys_daily_rate_cannot_delete.r_from_curr(i), 16,
831 ' ')
832 || RPAD(sys_daily_rate_cannot_delete.r_to_curr(i), 16,
833 ' ')
834 || LPAD(sys_daily_rate_cannot_delete.r_rate(i) || ' ',
835 13, ' ')
836 || RPAD
837 (sys_daily_rate_cannot_delete.r_rate_source_code(i),
838 26, ' '));
839
840 IF page_line_count >= page_line_numbers - 2 THEN
841 page_line_count := 1;
842 page_count := page_count + 1;
843 fnd_file.put_line(fnd_file.output, ' ');
844 fnd_file.put_line(fnd_file.output, ' ');
845 END IF;
846 END LOOP;
847
848 /*********************************************************************************
849 Bug 4641250 JVARKEY Changed the following query
850 *********************************************************************************/
851
852 -- Bug 4746397 JVARKEY Changed the array variables
853 SELECT dr.from_currency,
854 dr.to_currency,
855 gldct.user_conversion_type,
856 dr.conversion_date,
857 dr.conversion_rate,
858 dr.rate_source_code
859 BULK COLLECT INTO sys_daily_rate_cannot_override.r_from_curr,
860 sys_daily_rate_cannot_override.r_to_curr,
861 sys_daily_rate_cannot_override.r_type,
862 sys_daily_rate_cannot_override.r_conversion_date,
863 sys_daily_rate_cannot_override.r_rate,
864 sys_daily_rate_cannot_override.r_rate_source_code
865 FROM gl_daily_rates dr,
866 gl_daily_conversion_types gldct,
867 gl_row_multipliers rm,
868 gl_daily_conversion_types ct,
869 gl_daily_rates_interface ri
870 WHERE ct.user_conversion_type = ri.user_conversion_type || ''
871 AND rm.multiplier BETWEEN 1 AND (TRUNC(ri.to_conversion_date)
872 - TRUNC(ri.from_conversion_date)
873 + 1)
874 AND ((dr.from_currency = ri.from_currency
875 AND dr.to_currency = ri.to_currency
876 AND ri.mode_flag in ('I','T'))
877 OR (dr.from_currency = ri.to_currency
878 AND dr.to_currency = ri.from_currency
879 AND ri.mode_flag = 'I'))
880 AND dr.conversion_type = ct.conversion_type
881 AND dr.conversion_date = TRUNC(ri.from_conversion_date)+rm.multiplier-1
882 AND dr.rate_source_code = 'SYSTEM'
883 AND dr.conversion_type = gldct.conversion_type;
884
885 FOR i IN 1 .. sys_daily_rate_cannot_override.r_from_curr.COUNT LOOP
886 IF page_line_count = 1 THEN
887 gl_crm_utilities_pkg.print_report_title;
888 gl_crm_utilities_pkg.print_override_user_rate_warn;
889 ELSIF i = 1 THEN
890 gl_crm_utilities_pkg.print_override_user_rate_warn;
891 END IF;
892
893 page_line_count := page_line_count + 1;
894 fnd_file.put_line
895 (fnd_file.output,
896 RPAD(sys_daily_rate_cannot_override.r_type(i), 31, ' ')
897 || RPAD
898 (sys_daily_rate_cannot_override.r_conversion_date(i),
899 30, ' ')
900 || RPAD(sys_daily_rate_cannot_override.r_from_curr(i), 16,
901 ' ')
902 || RPAD(sys_daily_rate_cannot_override.r_to_curr(i), 16,
903 ' ')
904 || LPAD(sys_daily_rate_cannot_override.r_rate(i) || ' ',
905 13, ' ')
906 || RPAD
907 (sys_daily_rate_cannot_override.r_rate_source_code(i),
908 26, ' '));
909
910 IF page_line_count >= page_line_numbers - 2 THEN
911 page_line_count := 1;
912 page_count := page_count + 1;
913 fnd_file.put_line(fnd_file.output, ' ');
914 fnd_file.put_line(fnd_file.output, ' ');
915 END IF;
916 END LOOP;
917
918 IF DEBUG_MODE THEN
919 fnd_file.put_line
920 (fnd_file.LOG,
921 'Ended finding system rates that cannot be override.');
922 END IF;
923 END IF;
924
925 -- Update used_for_ab_translation = 'Y' if the currency and
926 -- conversion type is used in average translation in the system
927 -- R12 Change in the following update
928 UPDATE gl_daily_rates_interface ri
929 SET used_for_ab_translation =
930 ( SELECT nvl(max('Y'), 'N')
931 FROM gl_daily_conversion_types ct,
932 gl_ledgers led,
933 gl_ledger_relationships rel
934 WHERE ct.user_conversion_type = ri.user_conversion_type
935 AND rel.source_ledger_id = led.ledger_id
936 AND rel.target_ledger_id = led.ledger_id
937 AND rel.target_ledger_category_code = 'ALC'
938 AND rel.application_id = 101
939 AND led.currency_code IN (ri.from_currency, ri.to_currency)
940 AND rel.target_currency_code IN (ri.from_currency, ri.to_currency)
941 AND ( led.daily_translation_rate_type = ct.conversion_type
942 OR nvl(rel.alc_period_average_rate_type,
943 led.period_average_rate_type) = ct.conversion_type
944 OR nvl(rel.alc_period_end_rate_type,
945 led.period_end_rate_type) = ct.conversion_type)
946 AND ri.mode_flag IN ('I', 'D', 'T', 'N'));
947
948 BEGIN
949
950 SELECT 'Y'
951 INTO l_launch_rate_change
952 FROM gl_daily_rates_interface
953 WHERE used_for_ab_translation = 'Y'
954 AND ROWNUM < 2;
955
956 EXCEPTION
957
958 WHEN OTHERS THEN
959
960 l_launch_rate_change := 'N';
961
962 END;
963
964 IF DEBUG_MODE THEN
965 fnd_file.put_line
966 (fnd_file.LOG,
967 'Marking D for rates meant to delete with types used for ab translation');
968 END IF;
969
970 UPDATE gl_daily_rates dr
971 SET status_code = 'D'
972 WHERE ( (dr.rate_source_code IS NULL)
973 OR ( dr.rate_source_code IN('USER', 'TREASURY')
974 AND golden_rule_flag <> 'USER')
975 OR golden_rule_flag = 'USER')
976 AND (dr.from_currency,
977 dr.to_currency,
978 dr.conversion_type,
979 dr.conversion_date) IN(
980 SELECT ri.from_currency, ri.to_currency, --direct rate
981 ct.conversion_type,
982 TRUNC(ri.from_conversion_date) + rm.multiplier
983 - 1
984 FROM gl_row_multipliers rm,
985 gl_daily_conversion_types ct,
986 gl_daily_rates_interface ri
987 WHERE ri.mode_flag = 'D'
988 AND ri.used_for_ab_translation = 'Y'
989 AND ct.user_conversion_type =
990 ri.user_conversion_type || ''
991 AND rm.multiplier BETWEEN 1
992 AND TRUNC
993 (ri.to_conversion_date)
994 - TRUNC
995 (ri.from_conversion_date)
996 + 1
997 UNION ALL
998 SELECT ri.to_currency, ri.from_currency, --inverse rate
999 ct.conversion_type,
1000 TRUNC(ri.from_conversion_date) + rm.multiplier
1001 - 1
1002 FROM gl_row_multipliers rm,
1003 gl_daily_conversion_types ct,
1004 gl_daily_rates_interface ri
1005 WHERE ri.mode_flag = 'D'
1006 AND ri.used_for_ab_translation = 'Y'
1007 AND ct.user_conversion_type =
1008 ri.user_conversion_type || ''
1009 AND rm.multiplier BETWEEN 1
1010 AND TRUNC
1011 (ri.to_conversion_date)
1012 - TRUNC
1013 (ri.from_conversion_date)
1014 + 1
1015 );
1016
1017 -- Delete existing rows with conversion rate in GL_DAILY_RATES
1018 IF DEBUG_MODE THEN
1019 fnd_file.put_line(fnd_file.LOG,
1020 SQL%ROWCOUNT || ' rows marked');
1021 fnd_file.put_line
1022 (fnd_file.LOG,
1023 'Deleting rates meant for insert and for deleted rates with types not used for ab translation');
1024 END IF;
1025
1026 DELETE gl_daily_rates dr
1027 WHERE ( (dr.rate_source_code IS NULL)
1028 OR ( dr.rate_source_code IN('USER', 'TREASURY')
1029 AND golden_rule_flag <> 'USER')
1030 OR golden_rule_flag = 'USER')
1031 AND (dr.from_currency,
1032 dr.to_currency,
1033 dr.conversion_type,
1034 dr.conversion_date) IN(
1035 SELECT ri.from_currency, ri.to_currency, --direct rates
1036 ct.conversion_type,
1037 TRUNC(ri.from_conversion_date)
1038 + rm.multiplier - 1
1039 FROM gl_row_multipliers rm,
1040 gl_daily_conversion_types ct,
1041 gl_daily_rates_interface ri
1042 WHERE ( ri.mode_flag IN('I', 'T')
1043 OR ( ri.mode_flag = 'D'
1044 AND ri.used_for_ab_translation <> 'Y'))
1045 AND ct.user_conversion_type =
1046 ri.user_conversion_type || ''
1047 AND rm.multiplier BETWEEN 1
1048 AND TRUNC
1049 (ri.to_conversion_date)
1050 - TRUNC
1051 (ri.from_conversion_date)
1052 + 1
1053 UNION ALL
1054 SELECT ri.to_currency, ri.from_currency, --inverse rates
1055 ct.conversion_type,
1056 TRUNC(ri.from_conversion_date)
1057 + rm.multiplier - 1
1058 FROM gl_row_multipliers rm,
1059 gl_daily_conversion_types ct,
1060 gl_daily_rates_interface ri
1061 WHERE ( ri.mode_flag IN('I', 'T')
1062 OR ( ri.mode_flag = 'D'
1063 AND ri.used_for_ab_translation <> 'Y'))
1064 AND ct.user_conversion_type =
1065 ri.user_conversion_type || ''
1066 AND rm.multiplier BETWEEN 1
1067 AND TRUNC
1068 (ri.to_conversion_date)
1069 - TRUNC
1070 (ri.from_conversion_date)
1071 + 1
1072 );
1073
1074 IF DEBUG_MODE THEN
1075 fnd_file.put_line(fnd_file.LOG,
1076 SQL%ROWCOUNT || ' rows deleted');
1077 END IF;
1078
1079 BEGIN
1080 -- Insert all rows with conversion rate
1081 IF DEBUG_MODE THEN
1082 fnd_file.put_line(fnd_file.LOG, 'Insert all rates.');
1083 END IF;
1084
1085 INSERT INTO gl_daily_rates
1086 (from_currency, to_currency, conversion_date,
1087 conversion_type, conversion_rate, status_code,
1088 creation_date, created_by, last_update_date,
1089 last_updated_by, last_update_login, CONTEXT,
1090 attribute1, attribute2, attribute3, attribute4,
1091 attribute5, attribute6, attribute7, attribute8,
1092 attribute9, attribute10, attribute11, attribute12,
1093 attribute13, attribute14, attribute15,
1094 rate_source_code)
1095 SELECT ri.from_currency, ri.to_currency, --direct rates
1096 TRUNC(ri.from_conversion_date) + rm.multiplier - 1,
1097 ct.conversion_type, ri.conversion_rate,
1098 DECODE(ri.used_for_ab_translation, 'Y', 'O', 'C'),
1099 SYSDATE, NVL(ri.user_id, 1), SYSDATE,
1100 NVL(ri.user_id, 1), 1, ri.CONTEXT, ri.attribute1,
1101 ri.attribute2, ri.attribute3, ri.attribute4,
1102 ri.attribute5, ri.attribute6, ri.attribute7,
1103 ri.attribute8, ri.attribute9, ri.attribute10,
1104 ri.attribute11, ri.attribute12, ri.attribute13,
1105 ri.attribute14, ri.attribute15,
1106 DECODE(ri.mode_flag, 'T', 'TREASURY', 'N', 'TREASURY', 'USER')
1107 FROM gl_row_multipliers rm,
1108 gl_daily_conversion_types ct,
1109 gl_daily_rates_interface ri
1110 WHERE ri.mode_flag IN('I', 'T', 'N')
1111 AND ct.user_conversion_type = ri.user_conversion_type || ''
1112 AND rm.multiplier BETWEEN 1
1113 AND TRUNC(ri.to_conversion_date)
1114 - TRUNC(ri.from_conversion_date)
1115 + 1
1116 AND NOT EXISTS(
1117 SELECT 1
1118 FROM gl_daily_rates dr
1119 WHERE dr.from_currency = ri.from_currency
1120 AND dr.to_currency = ri.to_currency
1121 AND dr.conversion_type = ct.conversion_type
1122 AND dr.conversion_date =
1123 TRUNC(ri.from_conversion_date)
1124 + rm.multiplier - 1)
1125 UNION ALL
1126 SELECT ri.to_currency, ri.from_currency, --inverse rates
1127 TRUNC(ri.from_conversion_date) + rm.multiplier - 1,
1128 ct.conversion_type,
1129 NVL(ri.inverse_conversion_rate, 1 / ri.conversion_rate),
1130 DECODE(ri.used_for_ab_translation, 'Y', 'O', 'C'),
1131 SYSDATE, NVL(ri.user_id, 1), SYSDATE,
1132 NVL(ri.user_id, 1), 1, ri.CONTEXT, ri.attribute1,
1133 ri.attribute2, ri.attribute3, ri.attribute4,
1134 ri.attribute5, ri.attribute6, ri.attribute7,
1135 ri.attribute8, ri.attribute9, ri.attribute10,
1136 ri.attribute11, ri.attribute12, ri.attribute13,
1137 ri.attribute14, ri.attribute15,
1138 DECODE(ri.mode_flag, 'T', 'TREASURY', 'N', 'TREASURY', 'USER')
1139 FROM gl_row_multipliers rm,
1140 gl_daily_conversion_types ct,
1141 gl_daily_rates_interface ri
1142 WHERE ri.mode_flag IN('I', 'T', 'N')
1143 AND ct.user_conversion_type = ri.user_conversion_type || ''
1144 AND rm.multiplier BETWEEN 1
1145 AND TRUNC(ri.to_conversion_date)
1146 - TRUNC(ri.from_conversion_date)
1147 + 1
1148 AND NOT EXISTS(
1149 SELECT 1
1150 FROM gl_daily_rates dr
1151 WHERE dr.from_currency = ri.to_currency
1152 AND dr.to_currency = ri.from_currency
1153 AND dr.conversion_type = ct.conversion_type
1154 AND dr.conversion_date =
1155 TRUNC(ri.from_conversion_date)
1156 + rm.multiplier - 1);
1157
1158 IF DEBUG_MODE THEN
1159 fnd_file.put_line(fnd_file.LOG,
1160 SQL%ROWCOUNT || ' rows inserted');
1161 END IF;
1162
1163 EXCEPTION
1164 WHEN OTHERS THEN
1165 ROLLBACK;
1166 fnd_file.put_line
1167 (fnd_file.LOG,
1168 'Error: Duplicate Row or Overlapping Date Range found.');
1169 fnd_file.put_line
1170 (fnd_file.output,
1171 'Error: Duplicate Row or Overlapping Date Range found.');
1172 set_completion_status_result :=
1173 fnd_concurrent.set_completion_status
1174 ('ERROR',
1175 'Error: Duplicate Row or Overlapping Date Range found.');
1176 END;
1177
1178 -- Bug 4758732 JVARKEY Call to raise Business Events
1179 IF DEBUG_MODE THEN
1180 fnd_file.put_line(fnd_file.LOG, 'Firing Business events');
1181 END IF;
1182
1183 FOR dri_rec in raise_buz_events LOOP
1184
1185 raise_dr_buz_events(dri_rec.from_currency, dri_rec.to_currency,
1186 dri_rec.from_conversion_date, dri_rec.to_conversion_date,
1187 dri_rec.conversion_type, dri_rec.conversion_rate,
1188 dri_rec.inverse_conversion_rate, dri_rec.mode_flag);
1189
1190 END LOOP;
1191
1192 --- Cross Rates Calculation ---
1193 SELECT COUNT(*)
1194 INTO rows_need_calculation
1195 FROM gl_daily_rates_interface
1196 WHERE mode_flag IN('I', 'D', 'T', 'N')
1197 AND user_conversion_type IN(
1198 SELECT user_conversion_type
1199 FROM gl_daily_conversion_types gct,
1200 gl_cross_rate_rules gcrs
1201 WHERE gct.conversion_type = gcrs.conversion_type)
1202 AND rownum < 2;
1203
1204 IF DEBUG_MODE THEN
1205 IF rows_need_calculation = 0 THEN
1206 fnd_file.put_line(fnd_file.LOG,
1207 'NO need cross rate calculation.');
1208 ELSE
1209 fnd_file.put_line(fnd_file.LOG,
1210 'Cross rates calculation needed.');
1211 END IF;
1212 END IF;
1213
1214 IF rows_need_calculation > 0 THEN
1215 -- Delete all valid rows in GL_DAILY_RATES_INTERFACE and do not need
1216 -- crossing rates calculation
1217 DELETE FROM gl_daily_rates_interface
1218 WHERE mode_flag IN('I', 'D', 'T', 'N')
1219 AND user_conversion_type NOT IN(
1220 SELECT user_conversion_type
1221 FROM gl_daily_conversion_types gct,
1222 gl_cross_rate_rules gcrs
1223 WHERE gct.conversion_type = gcrs.conversion_type);
1224
1225 gl_message.func_ent('Cross Rates Calculation');
1226
1227 -- select all the lines who can be calculate cross rate from the gl_daily_rates table
1228 IF DEBUG_MODE THEN
1229 fnd_file.put_line(fnd_file.LOG, 'Clean gl_cross_rate_temp.');
1230 END IF;
1231
1232 DELETE FROM gl_cross_rate_temp;
1233
1234 IF DEBUG_MODE THEN
1235 fnd_file.put_line
1236 (fnd_file.LOG,
1237 'Copy rows from interface table to gl_cross_rate_temp.');
1238 END IF;
1239
1240 INSERT INTO gl_cross_rate_temp
1241 (conversion_type, pivot_currency, from_currency,
1242 to_currency, from_conversion_date,
1243 to_conversion_date, conversion_rate,
1244 inverse_conversion_rate, mode_flag,
1245 used_for_ab_translation)
1246 SELECT gldct.conversion_type,
1247 DECODE(gldri.from_currency,
1248 glcrs.pivot_currency, gldri.from_currency,
1249 gldri.to_currency),
1250 DECODE(gldri.from_currency,
1251 glcrs.pivot_currency, gldri.to_currency,
1252 gldri.from_currency),
1253 glcrsd.to_currency, gldri.from_conversion_date,
1254 gldri.to_conversion_date,
1255 DECODE(gldri.from_currency,
1256 glcrs.pivot_currency, gldri.conversion_rate,
1257 gldri.inverse_conversion_rate),
1258 DECODE(gldri.from_currency,
1259 glcrs.pivot_currency, gldri.inverse_conversion_rate,
1260 gldri.conversion_rate),
1261 DECODE(gldri.mode_flag, 'D', 'D', 'I'),
1262 gldri.used_for_ab_translation
1263 FROM gl_daily_rates_interface gldri,
1264 gl_daily_conversion_types gldct,
1265 gl_cross_rate_rules glcrs,
1266 gl_cross_rate_rule_dtls glcrsd
1267 WHERE gldri.mode_flag IN('I', 'D', 'T', 'N')
1268 AND gldri.user_conversion_type = gldct.user_conversion_type
1269 AND gldct.conversion_type = glcrs.conversion_type
1270 AND ( ( (gldri.from_currency = glcrs.pivot_currency)
1271 AND (gldri.to_currency IN(
1272 SELECT DISTINCT from_currency
1273 FROM gl_cross_rate_rule_dtls glcrsd2
1274 WHERE glcrs.conversion_type =
1275 glcrsd2.conversion_type
1276 --AND glcrs.pivot_currency = glcrsd2.pivot_currency
1277 AND glcrsd2.enabled_flag = 'Y')))
1278 OR ( (gldri.to_currency = glcrs.pivot_currency)
1279 AND (gldri.from_currency IN(
1280 SELECT DISTINCT from_currency
1281 FROM gl_cross_rate_rule_dtls glcrsd3
1282 WHERE glcrs.conversion_type =
1283 glcrsd3.conversion_type
1284 --AND glcrs.pivot_currency = glcrsd3.pivot_currency
1285 AND glcrsd3.enabled_flag = 'Y'))))
1286 AND glcrsd.conversion_type = gldct.conversion_type
1287 AND glcrs.pivot_currency =
1288 DECODE(gldri.from_currency,
1289 glcrs.pivot_currency, gldri.from_currency,
1290 gldri.to_currency)
1291 AND glcrsd.from_currency =
1292 DECODE(gldri.from_currency,
1293 glcrs.pivot_currency, gldri.to_currency,
1294 gldri.from_currency)
1295 AND glcrsd.enabled_flag = 'Y';
1296
1297 IF DEBUG_MODE THEN
1298 fnd_file.put_line(fnd_file.LOG, 'Update the used for ab translation');
1299 END IF;
1300
1301 UPDATE gl_cross_rate_temp ri
1302 SET used_for_ab_translation =
1303 ( SELECT nvl(max('Y'), 'N')
1304 FROM gl_daily_conversion_types ct,
1305 gl_ledgers led,
1306 gl_ledger_relationships rel
1307 WHERE ct.conversion_type = ri.conversion_type
1308 AND rel.source_ledger_id = led.ledger_id
1309 AND rel.target_ledger_id = led.ledger_id
1310 AND rel.target_ledger_category_code = 'ALC'
1311 AND rel.application_id = 101
1312 AND led.currency_code IN (ri.from_currency, ri.to_currency)
1313 AND rel.target_currency_code IN (ri.from_currency, ri.to_currency)
1314 AND ( led.daily_translation_rate_type = ct.conversion_type
1315 OR nvl(rel.alc_period_average_rate_type,
1316 led.period_average_rate_type) = ct.conversion_type
1317 OR nvl(rel.alc_period_end_rate_type,
1318 led.period_end_rate_type) = ct.conversion_type)
1319 AND ri.mode_flag IN ('I', 'D', 'T', 'N'));
1320
1321 IF (l_launch_rate_change = 'N') THEN
1322
1323 BEGIN
1324
1325 SELECT 'Y'
1326 INTO l_launch_rate_change
1327 FROM gl_cross_rate_temp
1328 WHERE used_for_ab_translation = 'Y'
1329 AND ROWNUM < 2;
1330
1331 EXCEPTION
1332
1333 WHEN OTHERS THEN
1334
1335 l_launch_rate_change := 'N';
1336
1337 END;
1338
1339 END IF;
1340
1341 IF ((golden_rule_flag = 'USER') OR(golden_rule_flag = 'BOTH')) THEN
1342 -- user defined rates overrides, user defined rates rules
1343 IF DEBUG_MODE THEN
1344 fnd_file.put_line
1345 (fnd_file.LOG,
1346 'User Rate Rule or Both Rule; Checking if any user-defined rates cannot been cross-deleted.');
1347 END IF;
1348
1349 SELECT DISTINCT gldr.from_currency,
1350 gldr.to_currency,
1351 gldct.user_conversion_type,
1352 gldr.conversion_date,
1353 gldr.conversion_rate,
1354 gldr.rate_source_code
1355 BULK COLLECT INTO usr_daily_rate_cannot_delete.r_from_curr,
1356 usr_daily_rate_cannot_delete.r_to_curr,
1357 usr_daily_rate_cannot_delete.r_type,
1358 usr_daily_rate_cannot_delete.r_conversion_date,
1359 usr_daily_rate_cannot_delete.r_rate,
1360 usr_daily_rate_cannot_delete.r_rate_source_code
1361 FROM gl_daily_rates gldr,
1362 gl_daily_conversion_types gldct,
1363 gl_cross_rate_temp glcrt,
1364 gl_row_multipliers glrm
1365 WHERE gldr.conversion_type = gldct.conversion_type
1366 AND ( (gldr.rate_source_code IS NULL)
1367 OR ( (gldr.rate_source_code IS NOT NULL)
1368 AND (gldr.rate_source_code IN
1369 ('USER', 'TREASURY'))))
1370 AND glcrt.mode_flag = 'D'
1371 AND ( ( (gldr.from_currency =
1372 glcrt.from_currency)
1373 AND (gldr.to_currency = glcrt.to_currency))
1374 OR ( (gldr.to_currency =
1375 glcrt.from_currency)
1376 AND (gldr.from_currency =
1377 glcrt.to_currency)))
1378 AND gldr.conversion_type = glcrt.conversion_type
1379 AND gldr.conversion_date =
1380 TRUNC(glcrt.from_conversion_date)
1381 + glrm.multiplier - 1
1382 AND glrm.multiplier BETWEEN 1
1383 AND TRUNC
1384 (glcrt.to_conversion_date)
1385 - TRUNC
1386 (glcrt.from_conversion_date)
1387 + 1;
1388
1389 FOR i IN 1 .. usr_daily_rate_cannot_delete.r_from_curr.COUNT LOOP
1390 IF page_line_count = 1 THEN
1391 gl_crm_utilities_pkg.print_report_title;
1392 gl_crm_utilities_pkg.print_delete_sys_rate_warning;
1393 ELSIF i = 1 THEN
1394 gl_crm_utilities_pkg.print_delete_sys_rate_warning;
1395 END IF;
1396
1397 page_line_count := page_line_count + 1;
1398 fnd_file.put_line
1399 (fnd_file.output,
1400 RPAD(usr_daily_rate_cannot_delete.r_type(i), 31, ' ')
1401 || RPAD
1402 (usr_daily_rate_cannot_delete.r_conversion_date(i),
1403 30, ' ')
1404 || RPAD(usr_daily_rate_cannot_delete.r_from_curr(i), 16,
1405 ' ')
1406 || RPAD(usr_daily_rate_cannot_delete.r_to_curr(i), 16,
1407 ' ')
1408 || LPAD(usr_daily_rate_cannot_delete.r_rate(i) || ' ',
1409 13, ' ')
1410 || RPAD
1411 (usr_daily_rate_cannot_delete.r_rate_source_code(i),
1412 26, ' '));
1413
1414 IF page_line_count >= page_line_numbers - 2 THEN
1415 page_line_count := 1;
1416 page_count := page_count + 1;
1417 fnd_file.put_line(fnd_file.output, ' ');
1418 fnd_file.put_line(fnd_file.output, ' ');
1419 END IF;
1420 END LOOP;
1421
1422 SELECT DISTINCT gldr.from_currency,
1423 gldr.to_currency,
1424 gldct.user_conversion_type,
1425 gldr.conversion_date,
1426 gldr.conversion_rate,
1427 gldr.rate_source_code
1428 BULK COLLECT INTO usr_daily_rate_cannot_override.r_from_curr,
1429 usr_daily_rate_cannot_override.r_to_curr,
1430 usr_daily_rate_cannot_override.r_type,
1431 usr_daily_rate_cannot_override.r_conversion_date,
1432 usr_daily_rate_cannot_override.r_rate,
1433 usr_daily_rate_cannot_override.r_rate_source_code
1434 FROM gl_daily_rates gldr,
1435 gl_daily_conversion_types gldct,
1436 gl_cross_rate_temp glcrt,
1437 gl_row_multipliers glrm
1438 WHERE gldr.conversion_type = gldct.conversion_type
1439 AND ( (gldr.rate_source_code IS NULL)
1440 OR ( (gldr.rate_source_code IS NOT NULL)
1441 AND (gldr.rate_source_code IN
1442 ('USER', 'TREASURY'))))
1443 AND glcrt.mode_flag in ('I', 'T', 'N')
1444 AND ( ( (gldr.from_currency =
1445 glcrt.from_currency)
1446 AND (gldr.to_currency = glcrt.to_currency))
1447 OR ( (gldr.to_currency =
1448 glcrt.from_currency)
1449 AND (gldr.from_currency =
1450 glcrt.to_currency)))
1451 AND gldr.conversion_type = glcrt.conversion_type
1452 AND gldr.conversion_date =
1453 TRUNC(glcrt.from_conversion_date)
1454 + glrm.multiplier - 1
1455 AND glrm.multiplier BETWEEN 1
1456 AND TRUNC
1457 (glcrt.to_conversion_date)
1458 - TRUNC
1459 (glcrt.from_conversion_date)
1460 + 1;
1461
1462 FOR i IN 1 .. usr_daily_rate_cannot_override.r_from_curr.COUNT LOOP
1463 IF page_line_count = 1 THEN
1464 gl_crm_utilities_pkg.print_report_title;
1465 gl_crm_utilities_pkg.print_override_sys_rate_warn;
1466 ELSIF i = 1 THEN
1467 gl_crm_utilities_pkg.print_override_sys_rate_warn;
1468 END IF;
1469
1470 page_line_count := page_line_count + 1;
1471 fnd_file.put_line
1472 (fnd_file.output,
1473 RPAD(usr_daily_rate_cannot_override.r_type(i), 31, ' ')
1474 || RPAD
1475 (usr_daily_rate_cannot_override.r_conversion_date
1476 (i),
1477 30, ' ')
1478 || RPAD(usr_daily_rate_cannot_override.r_from_curr(i),
1479 16, ' ')
1480 || RPAD(usr_daily_rate_cannot_override.r_to_curr(i), 16,
1481 ' ')
1482 || LPAD(usr_daily_rate_cannot_override.r_rate(i) || ' ',
1483 13, ' ')
1484 || RPAD
1485 (usr_daily_rate_cannot_override.r_rate_source_code
1486 (i),
1487 26, ' '));
1488
1489 IF page_line_count >= page_line_numbers - 2 THEN
1490 page_line_count := 1;
1491 page_count := page_count + 1;
1492 fnd_file.put_line(fnd_file.output, ' ');
1493 fnd_file.put_line(fnd_file.output, ' ');
1494 END IF;
1495 END LOOP;
1496 END IF;
1497
1498 -- if golden rule as user override, that's user rates rile, we can only delete SYSTEM rates
1499 IF DEBUG_MODE THEN
1500 fnd_file.put_line
1501 (fnd_file.LOG,
1502 'Mark D for Cross Rates');
1503 END IF;
1504
1505 -- For each row with conversion rate in
1506 -- GL_DAILY_RATES_INTERFACE where mode = 'D',
1507 -- set status_code to 'D' in the corresponding row in GL_DAILY_RATES.
1508 UPDATE gl_daily_rates gldr
1509 SET status_code = 'D'
1510 WHERE ( (gldr.rate_source_code IS NOT NULL
1511 AND gldr.rate_source_code = 'SYSTEM'
1512 AND golden_rule_flag <> 'SYSTEM')
1513 OR golden_rule_flag = 'SYSTEM')
1514 AND (gldr.from_currency,
1515 gldr.to_currency,
1516 gldr.conversion_type,
1517 gldr.conversion_date) IN(
1518 SELECT glcrt.from_currency, glcrt.to_currency, --direct rates
1519 glcrt.conversion_type,
1520 TRUNC(glcrt.from_conversion_date)
1521 + glrm.multiplier - 1
1522 FROM gl_row_multipliers glrm,
1523 gl_cross_rate_temp glcrt,
1524 gl_daily_rates gldr
1525 WHERE glcrt.mode_flag = 'D'
1526 AND glcrt.used_for_ab_translation = 'Y'
1527 AND gldr.from_currency = glcrt.from_currency
1528 AND gldr.to_currency = glcrt.to_currency
1529 AND gldr.conversion_type = glcrt.conversion_type
1530 AND gldr.conversion_date =
1531 TRUNC(glcrt.from_conversion_date)
1532 + glrm.multiplier - 1
1533 AND glrm.multiplier BETWEEN 1
1534 AND TRUNC
1535 (glcrt.to_conversion_date)
1536 - TRUNC
1537 (glcrt.from_conversion_date)
1538 + 1
1539 UNION ALL
1540 SELECT glcrt.to_currency, glcrt.from_currency, -- inverse rates
1541 glcrt.conversion_type,
1542 TRUNC(glcrt.from_conversion_date)
1543 + glrm.multiplier - 1
1544 FROM gl_row_multipliers glrm,
1545 gl_cross_rate_temp glcrt,
1546 gl_daily_rates gldr
1547 WHERE glcrt.mode_flag = 'D'
1548 AND glcrt.used_for_ab_translation = 'Y'
1549 AND gldr.to_currency = glcrt.from_currency
1550 AND gldr.from_currency = glcrt.to_currency
1551 AND gldr.conversion_type = glcrt.conversion_type
1552 AND gldr.conversion_date =
1553 TRUNC(glcrt.from_conversion_date)
1554 + glrm.multiplier - 1
1555 AND glrm.multiplier BETWEEN 1
1556 AND TRUNC
1557 (glcrt.to_conversion_date)
1558 - TRUNC
1559 (glcrt.from_conversion_date)
1560 + 1
1561 );
1562
1563 IF DEBUG_MODE THEN
1564 fnd_file.put_line(fnd_file.LOG,
1565 SQL%ROWCOUNT || ' rows marked.');
1566 fnd_file.put_line
1567 (fnd_file.LOG,
1568 'Delete for Cross Rates');
1569 END IF;
1570
1571 DELETE FROM gl_daily_rates gldr
1572 WHERE ( (gldr.rate_source_code IS NOT NULL
1573 AND gldr.rate_source_code = 'SYSTEM'
1574 AND golden_rule_flag <> 'SYSTEM')
1575 OR golden_rule_flag = 'SYSTEM')
1576 AND (gldr.from_currency,
1577 gldr.to_currency,
1578 gldr.conversion_type,
1579 gldr.conversion_date) IN(
1580 SELECT glcrt.from_currency, glcrt.to_currency,
1581 glcrt.conversion_type,
1582 TRUNC(glcrt.from_conversion_date)
1583 + glrm.multiplier - 1
1584 FROM gl_row_multipliers glrm,
1585 gl_cross_rate_temp glcrt,
1586 gl_daily_rates gldr
1587 WHERE ( glcrt.mode_flag in ('I', 'T')
1588 OR ( glcrt.mode_flag = 'D'
1589 AND glcrt.used_for_ab_translation <>
1590 'Y'))
1591 AND gldr.from_currency = glcrt.from_currency
1592 AND gldr.to_currency = glcrt.to_currency
1593 AND gldr.conversion_type =
1594 glcrt.conversion_type
1595 AND gldr.conversion_date =
1596 TRUNC(glcrt.from_conversion_date)
1597 + glrm.multiplier - 1
1598 AND glrm.multiplier BETWEEN 1
1599 AND TRUNC
1600 (glcrt.to_conversion_date)
1601 - TRUNC
1602 (glcrt.from_conversion_date)
1603 + 1
1604 UNION ALL
1605 SELECT glcrt.to_currency, glcrt.from_currency,
1606 glcrt.conversion_type,
1607 TRUNC(glcrt.from_conversion_date)
1608 + glrm.multiplier - 1
1609 FROM gl_row_multipliers glrm,
1610 gl_cross_rate_temp glcrt,
1611 gl_daily_rates gldr
1612 WHERE ( glcrt.mode_flag in ('I', 'T')
1613 OR ( glcrt.mode_flag = 'D'
1614 AND glcrt.used_for_ab_translation <>
1615 'Y'))
1616 AND gldr.to_currency = glcrt.from_currency
1617 AND gldr.from_currency = glcrt.to_currency
1618 AND gldr.conversion_type =
1619 glcrt.conversion_type
1620 AND gldr.conversion_date =
1621 TRUNC(glcrt.from_conversion_date)
1622 + glrm.multiplier - 1
1623 AND glrm.multiplier BETWEEN 1
1624 AND TRUNC
1625 (glcrt.to_conversion_date)
1626 - TRUNC
1627 (glcrt.from_conversion_date)
1628 + 1
1629 );
1630
1631 IF DEBUG_MODE THEN
1632 fnd_file.put_line(fnd_file.LOG,
1633 SQL%ROWCOUNT || ' rows deleted.');
1634 END IF;
1635
1636 UPDATE gl_cross_rate_temp rt
1637 SET mode_flag = 'F'
1638 WHERE mode_flag = 'N'
1639 AND (EXISTS (SELECT 1 FROM gl_daily_rates dr
1640 WHERE dr.from_currency = rt.from_currency
1641 AND dr.to_currency = rt.to_currency
1642 AND dr.conversion_type = rt.conversion_type
1643 AND dr.conversion_date BETWEEN rt.from_conversion_date
1644 AND rt.to_conversion_date)
1645 OR EXISTS (SELECT 1 FROM gl_daily_rates dr
1646 WHERE dr.from_currency = rt.to_currency
1647 AND dr.to_currency = rt.from_currency
1648 AND dr.conversion_type = rt.conversion_type
1649 AND dr.conversion_date BETWEEN rt.from_conversion_date
1650 AND rt.to_conversion_date));
1651
1652 BEGIN
1653 IF DEBUG_MODE THEN
1654 fnd_file.put_line(fnd_file.LOG,
1655 'creating all cross rates ....');
1656 END IF;
1657
1658 INSERT INTO gl_daily_rates
1659 (from_currency, to_currency, conversion_date,
1660 conversion_type, conversion_rate, status_code,
1661 creation_date, created_by, last_update_date,
1662 last_updated_by, last_update_login,
1663 rate_source_code)
1664 SELECT glcrt.from_currency, glcrt.to_currency,
1665 TRUNC(glcrt.from_conversion_date) + glrm.multiplier
1666 - 1,
1667 glcrt.conversion_type,
1668 glcrt.inverse_conversion_rate * gldr.conversion_rate,
1669 DECODE(glcrt.used_for_ab_translation, 'Y', 'O', 'C'),
1670 SYSDATE, 1, SYSDATE, 1, 1, 'SYSTEM'
1671 FROM gl_row_multipliers glrm,
1672 gl_cross_rate_temp glcrt,
1673 gl_daily_rates gldr
1674 WHERE glcrt.mode_flag in ('I', 'T', 'N')
1675 AND gldr.from_currency = glcrt.pivot_currency
1676 AND gldr.to_currency = glcrt.to_currency
1677 AND gldr.conversion_type = glcrt.conversion_type
1678 AND gldr.conversion_date =
1679 TRUNC(glcrt.from_conversion_date)
1680 + glrm.multiplier - 1
1681 AND glrm.multiplier BETWEEN 1
1682 AND TRUNC
1683 (glcrt.to_conversion_date)
1684 - TRUNC
1685 (glcrt.from_conversion_date)
1686 + 1
1687 AND ( NOT EXISTS(
1688 SELECT 1
1689 FROM gl_daily_rates dr
1690 WHERE dr.from_currency = glcrt.from_currency
1691 AND dr.to_currency = glcrt.to_currency
1692 AND dr.conversion_type =
1693 glcrt.conversion_type
1694 AND dr.conversion_date =
1695 TRUNC(glcrt.from_conversion_date)
1696 + glrm.multiplier - 1)
1697 OR NOT EXISTS(
1698 SELECT 1
1699 FROM gl_daily_rates dr
1700 WHERE dr.from_currency = glcrt.to_currency
1701 AND dr.to_currency = glcrt.from_currency
1702 AND dr.conversion_type =
1703 glcrt.conversion_type
1704 AND dr.conversion_date =
1705 TRUNC(glcrt.from_conversion_date)
1706 + glrm.multiplier - 1))
1707 UNION
1708 SELECT glcrt.to_currency, glcrt.from_currency,
1709 TRUNC(glcrt.from_conversion_date) + glrm.multiplier
1710 - 1,
1711 glcrt.conversion_type,
1712 glcrt.conversion_rate * gldr.conversion_rate,
1713 DECODE(glcrt.used_for_ab_translation, 'Y', 'O', 'C'),
1714 SYSDATE, 1, SYSDATE, 1, 1, 'SYSTEM'
1715 FROM gl_row_multipliers glrm,
1716 gl_cross_rate_temp glcrt,
1717 gl_daily_rates gldr
1718 WHERE glcrt.mode_flag in ('I', 'T', 'N')
1719 AND gldr.to_currency = glcrt.pivot_currency
1720 AND gldr.from_currency = glcrt.to_currency
1721 AND gldr.conversion_type = glcrt.conversion_type
1722 AND gldr.conversion_date =
1723 TRUNC(glcrt.from_conversion_date)
1724 + glrm.multiplier - 1
1725 AND glrm.multiplier BETWEEN 1
1726 AND TRUNC
1727 (glcrt.to_conversion_date)
1728 - TRUNC
1729 (glcrt.from_conversion_date)
1730 + 1
1731 AND ( NOT EXISTS(
1732 SELECT 1
1733 FROM gl_daily_rates dr
1734 WHERE dr.from_currency = glcrt.from_currency
1735 AND dr.to_currency = glcrt.to_currency
1736 AND dr.conversion_type =
1737 glcrt.conversion_type
1738 AND dr.conversion_date =
1739 TRUNC(glcrt.from_conversion_date)
1740 + glrm.multiplier - 1)
1741 OR NOT EXISTS(
1742 SELECT 1
1743 FROM gl_daily_rates dr
1744 WHERE dr.from_currency = glcrt.to_currency
1745 AND dr.to_currency = glcrt.from_currency
1746 AND dr.conversion_type =
1747 glcrt.conversion_type
1748 AND dr.conversion_date =
1749 TRUNC(glcrt.from_conversion_date)
1750 + glrm.multiplier - 1));
1751
1752
1753 IF DEBUG_MODE THEN
1754 fnd_file.put_line(fnd_file.LOG,
1755 SQL%ROWCOUNT || ' rows inserted.');
1756 END IF;
1757 EXCEPTION
1758 WHEN OTHERS THEN
1759 ROLLBACK;
1760 gl_message.write_log('CRM0002', 0);
1761 fnd_file.put_line
1762 (fnd_file.LOG,
1763 'Error: Duplicate Row or Overlapping Date Range found.');
1764 fnd_file.put_line
1765 (fnd_file.output,
1766 'Error: Duplicate Row or Overlapping Date Range found.');
1767 set_completion_status_result :=
1768 fnd_concurrent.set_completion_status
1769 ('ERROR',
1770 'Error: Duplicate Row or Overlapping Date Range found.');
1771 END;
1772
1773 gl_message.func_succ('Cross Rates Calculation');
1774 END IF;
1775
1776 ---- End Calculation
1777
1778 DELETE FROM gl_daily_rates_interface
1779 WHERE mode_flag IN('I', 'D', 'T', 'N');
1780
1781 -- Launch the Rate Change Program if needed
1782 IF (l_launch_rate_change = 'Y') THEN
1783
1784 IF DEBUG_MODE THEN
1785 fnd_file.put_line(fnd_file.LOG,
1786 'Launching Rate Change Program');
1787 END IF;
1788
1789 RESULT := fnd_request.set_mode(TRUE);
1790
1791 -- Launch concurrent request to run the Rate Change Program
1792 req_id :=
1793 fnd_request.submit_request('SQLGL', 'GLTTRC', '', '', FALSE,
1794 'D', '', CHR(0), '', '', '', '', '',
1795 '', '', '', '', '', '', '', '', '',
1796 '', '', '', '', '', '', '', '', '',
1797 '', '', '', '', '', '', '', '', '',
1798 '', '', '', '', '', '', '', '', '',
1799 '', '', '', '', '', '', '', '', '',
1800 '', '', '', '', '', '', '', '', '',
1801 '', '', '', '', '', '', '', '', '',
1802 '', '', '', '', '', '', '', '', '',
1803 '', '', '', '', '', '', '', '', '',
1804 '', '', '', '', '', '', '', '', '',
1805 '', '');
1806 END IF;
1807
1808 IF page_count * page_line_count > 1 THEN
1809 gl_crm_utilities_pkg.print_validation_failure_codes;
1810 set_completion_status_result :=
1811 fnd_concurrent.set_completion_status
1812 ('WARNING',
1813 'Exceptions occurs, please check the output for details.');
1814 END IF;
1815
1816 DELETE FROM gl_daily_rates_interface
1817 WHERE mode_flag IN('I', 'D', 'T', 'N');
1818 END daily_rates_import;
1819
1820 -------------------------------------------------------------------
1821 FUNCTION submit_conc_request
1822 RETURN NUMBER IS
1823 RESULT NUMBER := -1;
1824 BEGIN
1825 -- Submit the request to run Rate Change concurrent program
1826 RESULT :=
1827 fnd_request.submit_request('SQLGL', 'GLDRICCP', '', '', FALSE,
1828 CHR(0), '', '', '', '', '', '', '', '',
1829 '', '', '', '', '', '', '', '', '', '',
1830 '', '', '', '', '', '', '', '', '', '',
1831 '', '', '', '', '', '', '', '', '', '',
1832 '', '', '', '', '', '', '', '', '', '',
1833 '', '', '', '', '', '', '', '', '', '',
1834 '', '', '', '', '', '', '', '', '', '',
1835 '', '', '', '', '', '', '', '', '', '',
1836 '', '', '', '', '', '', '', '', '', '',
1837 '', '', '', '', '', '', '', '', '', '',
1838 '');
1839 RETURN(RESULT);
1840 END submit_conc_request;
1841 END GL_CRM_UTILITIES_PKG;