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