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
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;
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....');
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
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;
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:
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,
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;
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;
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....');
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;
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;
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;
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: