[Home] [Help]
PACKAGE BODY: APPS.QPR_COLLECT_CURRENCY_DATA
Source
1 PACKAGE BODY QPR_COLLECT_CURRENCY_DATA AS
2 /* $Header: QPRUCCRB.pls 120.0 2007/10/11 13:08:13 agbennet noship $ */
3 type char15_type is table of varchar2(15) index by PLS_INTEGER;
4 type num_type is table of number index by PLS_INTEGER;
5 type date_type is table of date index by PLS_INTEGER;
6
7 type currency_type is record(FROM_CURRENCY char15_type,
8 TO_CURRENCY char15_type,
9 CONVERSION_DATE date_type,
10 CONVERSION_RATE num_type,
11 CONVERSION_CLASS char15_type);
12 r_curr_data currency_type;
13 CURR_RATE_SRC_TBL constant varchar2(30) := 'QPR_SR_CURRENCY_RATES_V';
14
15 procedure insert_curr_rate_data(p_instance_id in number) is
16 request_id number;
17 sys_date date:= sysdate;
18 user_id number:= fnd_global.user_id;
19 login_id number:= fnd_global.conc_login_id;
20 prg_appl_id number:= fnd_global.prog_appl_id;
21 prg_id number:= fnd_global.conc_program_id;
22 begin
23 fnd_profile.get('CONC_REQUEST_ID', request_id);
24 forall i in r_curr_data.FROM_CURRENCY.first..r_curr_data.FROM_CURRENCY.last
25 insert into QPR_CURRENCY_RATES(CURR_CONV_ID, FROM_CURRENCY, TO_CURRENCY,
26 CONVERSION_DATE, CONVERSION_RATE,
27 CONVERSION_CLASS, INSTANCE_ID,
28 CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE,
29 LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
30 PROGRAM_APPLICATION_ID, PROGRAM_ID,
31 REQUEST_ID)
32 values(QPR_CURRENCY_RATES_S.nextval,
33 r_curr_data.FROM_CURRENCY(i), r_curr_data.TO_CURRENCY(i),
34 r_curr_data.CONVERSION_DATE(i),
35 r_curr_data.CONVERSION_RATE(i),
36 r_curr_data.CONVERSION_CLASS(i),
37 p_instance_id, sys_date, user_id, sys_date, user_id,
38 login_id, prg_appl_id, prg_id, request_id) ;
39 exception
40 when OTHERS then
41 fnd_file.put_line(fnd_file.log, 'ERROR INSERTING CURRENCT RATE DATA...');
42 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
43 raise;
44 end insert_curr_rate_data;
45
46 procedure collect_currency_rates(errbuf out nocopy varchar2,
47 retcode out nocopy number,
48 p_instance_id in number,
49 p_date_from in varchar2,
50 p_date_to in varchar2) is
51 bfound boolean := false;
52 nrows number := 1000;
53 date_from date;
54 date_to date;
55 src_table varchar2(200);
56 s_sql varchar2(1000);
57 s_conv_type varchar2(30);
58 c_get_curr_rate SYS_REFCURSOR;
59 begin
60 src_table := CURR_RATE_SRC_TBL || qpr_sr_util.get_dblink(p_instance_id);
61 date_from := FND_DATE.canonical_to_date(p_date_from);
62 date_to := FND_DATE.canonical_to_date(p_date_to);
63
64 if nvl(qpr_sr_util.read_parameter('QPR_PULL_CURR_CONV_TO_ODS'), 'N')= 'Y' then
65 delete QPR_CURRENCY_RATES
66 where INSTANCE_ID = p_instance_id
67 and CONVERSION_DATE between date_from and date_to;
68
69 s_sql := 'select FROM_CURRENCY, TO_CURRENCY,CONVERSION_DATE, ';
70 s_sql := s_sql || 'CONVERSION_RATE,CONVERSION_CLASS from ' || src_table;
71 s_sql := s_sql || ' where CONVERSION_TYPE IS NULL or CONVERSION_TYPE = :1 ' ;
72 s_sql := s_sql || ' and CONVERSION_DATE between :2 and :3' ;
73
74 s_conv_type := nvl(qpr_sr_util.read_parameter('QPR_CONVERSION_TYPE'),
75 'Spot');
76
77 open c_get_curr_rate for s_sql using s_conv_type, date_from, date_to;
78 loop
79 fetch c_get_curr_rate bulk collect into r_curr_data limit nrows;
80 exit when r_curr_data.FROM_CURRENCY.count = 0;
81 fnd_file.put_line(fnd_file.log,
82 'Record count: ' || r_curr_data.FROM_CURRENCY.count);
83 insert_curr_rate_data(p_instance_id);
84 bfound := true;
85 end loop;
86 commit;
87
88 if bfound = false then
89 fnd_file.put_line(fnd_file.log,
90 'No data retrieved from source for given date range');
91 end if;
92 else
93 fnd_file.put_line(fnd_file.log,
94 'Rates not fetched to ODS as parameter QPR_PULL_CURR_CONV_TO_ODS is No/not set');
95 end if;
96 exception
97 when OTHERS then
98 retcode := -1;
99 errbuf := 'ERROR: ' || substr(sqlerrm, 1, 1000);
100 fnd_file.put_line(fnd_file.log, substr(sqlerrm, 1, 1000));
101 fnd_file.put_line(fnd_file.log, 'CANNOT POPULATE CURRENCY RATES');
102 rollback;
103 end collect_currency_rates;
104 END;
105