[Home] [Help]
PACKAGE BODY: APPS.QPR_DELETE_ODS_DATA
Source
1 PACKAGE BODY QPR_DELETE_ODS_DATA AS
2 /* $Header: QPRUDODB.pls 120.0 2007/10/11 13:12:44 agbennet noship $ */
3
4 Type num_type is table of number index by pls_integer;
5
6 procedure delete_measure_data(errbuf out nocopy varchar2,
7 retcode out nocopy number,
8 p_instance_id in number,
9 p_measure_code in varchar2,
10 p_from_date in varchar2,
11 p_to_date in varchar2,
12 p_dim_code in varchar2 default 'ALL',
13 p_dummy_dim_code in varchar2 default null,
14 p_dim_value_from in varchar2,
15 p_dim_value_to in varchar2) is
16
17 l_sql varchar2(20000) := '';
18 l_sql1 varchar2(20000) := '';
19 lrows number := 1000;
20 date_from date;
21 date_to date;
22 c_get_meas_data SYS_REFCURSOR;
23 t_meas_data num_type;
24
25 begin
26 date_from := fnd_date.canonical_to_date(p_from_date);
27 date_to := fnd_date.canonical_to_date(p_to_date);
28
29 l_sql := '';
30 l_sql := 'select measure_value_id from qpr_measure_data ';
31 l_sql := l_sql || ' where instance_id = :1 and measure_type_code = :2 ';
32 l_sql := l_sql || ' and time_level_value between :3 and :4';
33
34 case p_dim_code
35 when 'ALL' then
36 l_sql1 := null;
37 when 'ORD' then
38 l_sql1 := ' and ORD_LEVEL_VALUE between ''' || p_dim_value_from;
39 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
40 when 'PRD' then
41 l_sql1 := ' and PRD_LEVEL_VALUE between ''' || p_dim_value_from;
42 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
43 when 'GEO' then
44 l_sql1 := ' and GEO_LEVEL_VALUE between ''' || p_dim_value_from;
45 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
46 when 'CUS' then
47 l_sql1 := ' and CUS_LEVEL_VALUE between ''' || p_dim_value_from;
48 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
49 when 'REP' then
50 l_sql1 := ' and REP_LEVEL_VALUE between ''' || p_dim_value_from;
51 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
52 when 'CHN' then
53 l_sql1 := ' and CHN_LEVEL_VALUE between ''' || p_dim_value_from;
54 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
55 when 'ORG' then
56 l_sql1 := ' and ORG_LEVEL_VALUE between ''' || p_dim_value_from;
57 l_sql1 := l_sql1 || ''' and ''' || p_dim_value_to || '''';
58 else
59 l_sql1 := null;
60 end case;
61
62 l_sql := l_sql || l_sql1;
63
64 fnd_file.put_line( fnd_file.log, 'Sql to execute: ' || l_sql);
65 fnd_file.put_line(fnd_file.log, 'Starting deletion....');
66 open c_get_meas_data for l_sql using p_instance_id, p_measure_code,
67 date_from, date_to;
68 loop
69 fetch c_get_meas_data bulk collect into t_meas_data limit lrows;
70 exit when t_meas_data.count=0;
71 forall i in t_meas_data.first..t_meas_data.last
72 delete qpr_measure_data where measure_value_id = t_meas_data(i);
73 fnd_file.put_line(fnd_file.log, 'Deleted Records = ' || t_meas_data.count);
74 t_meas_data.delete;
75 end loop;
76 commit;
77 fnd_file.put_line(fnd_file.log, 'Deletion Complete....');
78 close c_get_meas_data;
79
80 exception
81 when OTHERS then
82 retcode := 2;
83 errbuf := 'ERROR: ' || substr(SQLERRM,1,1000);
84 fnd_file.put_line(fnd_file.log, 'Unable to delete ODS data');
85 fnd_file.put_line(fnd_file.log, 'ERROR: ' || substr(SQLERRM,1,1000));
86 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
87 rollback;
88 end delete_measure_data;
89
90 procedure delete_dimension_data(errbuf out nocopy varchar2,
91 retcode out nocopy number,
92 p_instance_id in number,
93 p_dim_code in varchar2,
94 p_dummy_dim_code in varchar2 default null,
95 p_dim_value_from in varchar2,
96 p_dim_value_to in varchar2) is
97 l_sql varchar2(20000) := '';
98 l_sql1 varchar2(20000) := '';
99 lrows number := 1000;
100 c_get_dim_data SYS_REFCURSOR;
101 t_dim_data num_type;
102
103 begin
104 l_sql := '';
105 l_sql := 'select dim_value_id from qpr_dimension_values where instance_id = ';
106 l_sql := l_sql || p_instance_id ;
107 if p_dim_value_from is not null and p_dim_value_to is not null then
108 l_sql := l_sql || ' and level1_value between ' ;
109 l_sql := l_sql || p_dim_value_from || ' and ' || p_dim_value_to;
110 end if;
111 if p_dim_code = 'ALL' then
112 l_sql1 := null;
113 else
114 l_sql1 := ' and dim_code = ''' || p_dim_code || '''';
115 end if;
116
117 l_sql := l_sql || l_sql1;
118 fnd_file.put_line( fnd_file.log, 'Sql to execute: ' || l_sql);
119 fnd_file.put_line(fnd_file.log, 'Starting deletion....');
120 open c_get_dim_data for l_sql;
121 loop
122 fetch c_get_dim_data bulk collect into t_dim_data limit lrows;
123 exit when t_dim_data.count=0;
124 forall i in t_dim_data.first..t_dim_data.last
125 delete qpr_dimension_values where dim_value_id = t_dim_data(i);
126 fnd_file.put_line(fnd_file.log, 'Deleted Records = ' || t_dim_data.count);
127 t_dim_data.delete;
128 end loop;
129 commit;
130 fnd_file.put_line(fnd_file.log, 'Deletion Complete....');
131 close c_get_dim_data;
132 exception
133 when OTHERS then
134 retcode := 2;
135 errbuf := 'ERROR: ' || substr(SQLERRM,1,1000);
136 fnd_file.put_line(fnd_file.log, 'Unable to delete ODS data');
137 fnd_file.put_line(fnd_file.log, 'ERROR: ' || substr(SQLERRM,1,1000));
138 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
139 rollback;
140 end delete_dimension_data;
141 END;
142
143