[Home] [Help]
PACKAGE BODY: APPS.BIM_UTL_PKG
Source
1 PACKAGE BODY BIM_UTL_PKG AS
2 /* $Header: bimutlpb.pls 115.8 2004/02/19 10:33:14 kpadiyar noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_UTL_PKG';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimutlpb.pls';
6 l_to_currency VARCHAR2(100) := fnd_profile.value('AMS_DEFAULT_CURR_CODE');
7 l_conversion_type VARCHAR2(30):= fnd_profile.VALUE('AMS_CURR_CONVERSION_TYPE');
8 ---------------------------------------------------------------------
9 -- FUNCTION
10 -- Convert_Currency
11 -- NOTE: Given from currency, from amount, converts to default currency amount.
12 -- Default currency can be get from profile value.
13 -- PARAMETER
14 -- p_from_currency IN VARCHAR2,
15 -- p_to_currency IN VARCHAR2,
16 -- p_from_amount IN NUMBER,
17 -- RETURN NUMBER
18 ---------------------------------------------------------------------
19 FUNCTION convert_currency(
20 p_from_currency VARCHAR2 ,
21 p_from_amount NUMBER) return NUMBER
22 IS
23 l_user_rate CONSTANT NUMBER := 1;
24 l_max_roll_days CONSTANT NUMBER := -1;
25 l_denominator NUMBER; -- Not used in Marketing.
26 l_numerator NUMBER; -- Not used in Marketing.
27 l_to_amount NUMBER;
28 l_rate NUMBER;
29 BEGIN
30
31 -- Conversion type cannot be null in profile
32 IF l_conversion_type IS NULL THEN
33 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
34 fnd_message.set_name('OZF', 'OZF_NO_EXCHANGE_TYPE');
35 fnd_msg_pub.add;
36 END IF;
37 RETURN 0;
38 END IF;
39
40 -- Call the proper GL API to convert the amount.
41 gl_currency_api.convert_closest_amount(
42 x_from_currency => p_from_currency
43 ,x_to_currency => l_to_currency
44 ,x_conversion_date =>sysdate
45 ,x_conversion_type => l_conversion_type
46 ,x_user_rate => l_user_rate
47 ,x_amount => p_from_amount
48 ,x_max_roll_days => l_max_roll_days
49 ,x_converted_amount => l_to_amount
50 ,x_denominator => l_denominator
51 ,x_numerator => l_numerator
52 ,x_rate => l_rate);
53 RETURN (l_to_amount);
54 EXCEPTION
55 WHEN gl_currency_api.no_rate THEN
56 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
57 fnd_message.set_name('OZF', 'OZF_NO_RATE');
58 fnd_msg_pub.add;
59 END IF;
60 RETURN 0;
61 WHEN gl_currency_api.invalid_currency THEN
62 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_error) THEN
63 fnd_message.set_name('OZF', 'OZF_INVALID_CURR');
64 fnd_msg_pub.add;
65 END IF;
66 RETURN 0;
67 WHEN OTHERS THEN
68 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
69 fnd_msg_pub.add_exc_msg('OZF_UTLITY_PVT', 'Convert_curency');
70 END IF;
71 RETURN 0;
72 END convert_currency;
73 -----------------------------------------------------------------------
74 -- PROCEDURE
75 -- LOG_HISTORY
76 --
77 -- Note
78 -- Insert history data for each load
79 --------------------------------------------------------------------------
80 PROCEDURE LOG_HISTORY(
81 p_object VARCHAR2 ,
82 p_start_time DATE ,
83 p_end_time DATE ,
84 x_msg_count OUT NOCOPY NUMBER ,
85 x_msg_data OUT NOCOPY VARCHAR2 ,
86 x_return_status OUT NOCOPY VARCHAR2
87 )
88 IS
89 l_user_id NUMBER := FND_GLOBAL.USER_ID();
90 l_table_name VARCHAR2(100):='bim_rep_history';
91 BEGIN
92 INSERT INTO
93 bim_rep_history
94 (creation_date,
95 last_update_date,
96 created_by,
97 last_updated_by,
98 object_last_updated_date,
99 object,
100 start_date,
101 end_date)
102 VALUES
103 (sysdate,
104 sysdate,
105 l_user_id,
106 l_user_id,
107 sysdate,
108 p_object,
109 p_start_time,
110 p_end_time);
111 EXCEPTION
112 WHEN OTHERS THEN
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 FND_MESSAGE.Set_Name ('AMS', 'AMS_REP_INSERT_ERROR');
115 FND_MESSAGE.Set_token('table_name', l_table_name, FALSE);
116 FND_MSG_PUB.Add;
117 fnd_file.put_line(fnd_file.log,fnd_message.get);
118 END LOG_HISTORY;
119
120 /* This procedure will drop the indexes of table p_table_name, and put
121 the index name and other information into bim_all_indexes. */
122 PROCEDURE DROP_INDEX
123 ( p_table_name IN VARCHAR2
124 )
125 IS
126 l_user_id NUMBER := FND_GLOBAL.USER_ID();
127 l_sysdate DATE := SYSDATE;
128 l_api_version_number CONSTANT NUMBER := 1.0;
129 l_api_name CONSTANT VARCHAR2(30) := 'DROP_INDEX';
130 l_success VARCHAR2(3);
131 l_seq_name VARCHAR(100);
132 l_def_tablespace VARCHAR2(100);
133 l_index_tablespace VARCHAR2(100);
134 l_oracle_username VARCHAR2(100);
135 l_table_name VARCHAR2(100);
136 l_temp_msg VARCHAR2(100);
137
138 /* Following tables are declared for storing information about the indexes */
139 TYPE generic_number_table IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
140 TYPE generic_char_table IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
141
142 l_pct_free generic_number_table;
143 l_ini_trans generic_number_table;
144 l_max_trans generic_number_table;
145 l_initial_extent generic_number_table;
146 l_next_extent generic_number_table;
147 l_min_extents generic_number_table;
148 l_max_extents generic_number_table;
149 l_pct_increase generic_number_table;
150 l_column_position generic_number_table;
151 l_owner generic_char_table;
152 l_uniqueness generic_char_table;
153 l_index_name generic_char_table;
154 l_ind_column_name generic_char_table;
155 l_index_table_name generic_char_table;
156 l_col_num VARCHAR2(1000);
157 is_unique VARCHAR2(30);
158 i NUMBER;
159 j NUMBER;
160 l_count NUMBER;
161
162 l_status VARCHAR2(30);
163 l_industry VARCHAR2(30);
164 l_orcl_schema VARCHAR2(30);
165 l_bol BOOLEAN := fnd_installation.get_app_info ('BIM',l_status,l_industry,l_orcl_schema);
166
167 CURSOR get_ts_name IS
168 SELECT i.tablespace, i.index_tablespace, u.oracle_username
169 FROM fnd_product_installations i, fnd_application a,
170 fnd_oracle_userid u
171 WHERE a.application_short_name = 'BIM'
172 AND a.application_id = i.application_id
173 AND u.oracle_id = i.oracle_id;
174
175 CURSOR get_index_params(p_name VARCHAR2 ,l_schema VARCHAR2) IS
176 SELECT a.owner,a.index_name,b.table_name,a.uniqueness,b.column_name,
177 b.column_position,a.pct_free,a.ini_trans,a.max_trans,
178 a.initial_extent,a.next_extent,a.min_extents,a.max_extents,
179 a.pct_increase
180 FROM all_ind_columns b, all_indexes a
181 WHERE a.index_name = b.index_name
182 AND a.owner = l_schema
183 AND a.owner = b.index_owner
184 AND b.table_name =upper(p_name)
185 ORDER BY a.index_name,b.column_position;
186
187 CURSOR index_count(p_name VARCHAR2) IS
188 SELECT count(*)
189 FROM bim_all_indexes
190 WHERE table_name =upper(p_name);
191
192 BEGIN
193
194 /* Get the tablespace name for the purpose of creating the index on that tablespace */
195 OPEN get_ts_name;
196 FETCH get_ts_name INTO l_def_tablespace, l_index_tablespace, l_oracle_username;
197 CLOSE get_ts_name;
198
199 /* Check whether there is already entried in bim_all_indexes. */
200 OPEN index_count(p_table_name);
201 FETCH index_count into l_count;
202 CLOSE index_count;
203
204 /* Only if there is no entries in table bim_all_indexes, otherwise it means
205 that the indexes are already dropped. */
206 IF l_count = 0 THEN
207
208 /* Retrieve and store INDEX parameters. Then drop the indexes */
209 i := 1;
210 FOR x in get_index_params(p_table_name,l_orcl_schema) LOOP
211 BEGIN
212 l_pct_free(i) := x.pct_free;
213 l_ini_trans(i) := x.ini_trans;
214 l_max_trans(i) := x.max_trans;
215 l_initial_extent(i) := x.initial_extent;
216 l_next_extent(i) := x.next_extent;
217 l_min_extents(i) := x.min_extents;
218 l_max_extents(i) := x.max_extents;
219 l_pct_increase(i) := x.pct_increase;
220 l_owner(i) := x.owner;
221 l_index_name(i) := x.index_name;
222 l_index_table_name(i) := x.table_name;
223 l_ind_column_name(i) := x.column_name;
224 l_uniqueness(i) := x.uniqueness;
225 l_column_position(i) := x.column_position;
226
227 IF (l_column_position(i) = 1) THEN
228 EXECUTE IMMEDIATE 'DROP INDEX '|| l_owner(i) || '.'|| l_index_name(i) ;
229 --dbms_output.put_line('Drop index '|| l_owner(i) || '.'|| l_index_name(i));
230 END IF;
231 i := i + 1;
232 EXCEPTION
233 WHEN OTHERS THEN
234 ams_utility_pvt.write_conc_log('error dropping index:'||sqlerrm(sqlcode));
235 END;
236 END LOOP;
237 --dbms_output.put_line('I:'||i);
238 /* Insert the indexes parameters into bim_all_indexes. */
239
240 j:=1;
241 WHILE(j<i) LOOP
242 -- dbms_output.put_line('index name:'||l_index_name(j));
243 BEGIN
244 IF (j<i-1) and (l_index_name(j) =l_index_name(j+1)) THEN
245 l_col_num :=l_col_num||l_ind_column_name(j)||',';
246 ELSE
247 l_col_num :=l_col_num||l_ind_column_name(j);
248
249 IF (l_uniqueness(j) ='UNIQUE' ) THEN
250 is_unique := l_uniqueness(j);
251 ELSE is_unique:='';
252 END IF;
253 INSERT into bim_all_indexes (
254 owner
255 ,index_name
256 ,table_name
257 ,column_name
258 ,index_tablespace
259 ,pct_free
260 ,ini_trans
261 ,max_trans
262 ,initial_extent
263 ,next_extent
264 ,min_extents
265 ,max_extents
266 ,pct_increase
267 ,uniqueness)
268 SELECT l_owner(j)
269 ,l_index_name(j)
270 ,l_index_table_name(j)
271 ,l_col_num
272 ,l_index_tablespace
273 ,l_pct_free(j)
274 ,l_ini_trans(j)
275 ,l_max_trans(j)
276 ,l_initial_extent(j)
277 ,l_next_extent(j)
278 ,l_min_extents(j)
279 ,l_max_extents(j)
280 ,l_pct_increase(j)
281 ,is_unique
282 FROM DUAL;
283 l_col_num :='';
284 END IF;
285 --dbms_output.put_line('J:'||j);
286 j := j + 1;
287 EXCEPTION
288 WHEN OTHERS THEN
289 ams_utility_pvt.write_conc_log('error inserting into bim_all_index:'||sqlerrm(sqlcode));
290 --dbms_output.put_line('error inserting'||sqlerrm(sqlcode));
291 END;
292 END LOOP;
293 END IF;
294 EXCEPTION
295 WHEN OTHERS THEN
296 ams_utility_pvt.write_conc_log('error in procedure drop_index:'||sqlerrm(sqlcode));
297 END DROP_INDEX;
298
299 PROCEDURE CREATE_INDEX
300 ( p_table_name IN VARCHAR2
301 )
302 IS
303 l_user_id NUMBER := FND_GLOBAL.USER_ID();
304 l_sysdate DATE := SYSDATE;
305 l_api_version_number CONSTANT NUMBER := 1.0;
306 l_api_name CONSTANT VARCHAR2(30) := 'DROP_INDEX';
307 l_count NUMBER;
308 CURSOR get_all_index (p_name VARCHAR2) IS
309 SELECT uniqueness
310 ,owner
311 ,index_name
312 ,table_name
313 ,index_tablespace
314 ,column_name
315 ,pct_free
316 ,ini_trans
317 ,max_trans
318 ,initial_extent
319 ,next_extent
320 ,min_extents
321 ,max_extents
322 ,pct_increase
323 FROM bim_all_indexes
324 WHERE table_name =UPPER(p_name);
325
326 CURSOR index_count(p_name VARCHAR2) IS
327 SELECT count(*)
328 FROM bim_all_indexes
329 WHERE table_name =upper(p_name);
330 BEGIN
331 OPEN index_count(p_table_name);
332 FETCH index_count into l_count;
333 CLOSE index_count;
334
335 IF l_count>0 THEN
336 FOR x in get_all_index(p_table_name) LOOP
337 BEGIN
338 EXECUTE IMMEDIATE 'CREATE INDEX '
339 || x.owner
340 || '.'
341 || x.index_name
342 ||' ON '
343 || x.owner
344 ||'.'
345 || x.table_name
346 || ' ('
347 || x.column_name
348 || ' )'
349 || ' tablespace ' || x.index_tablespace
350 || ' pctfree ' || x.pct_free
351 || ' initrans ' || x.ini_trans
352 || ' maxtrans ' || x.max_trans
353 || ' storage ( '
354 || ' initial ' || x.initial_extent
355 || ' next ' || x.next_extent
356 || ' minextents ' || x.min_extents
357 || ' maxextents ' || x.max_extents
358 || ' pctincrease ' || x.pct_increase
359 || ')'
360 || ' compute statistics';
361 EXCEPTION
362 WHEN OTHERS THEN
363 ams_utility_pvt.write_conc_log('error create index'||sqlerrm(sqlcode));
364 --dbms_output.put_line('error updateing minimum balance'||sqlerrm(sqlcode));
365 END;
366 END LOOP;
367 DELETE bim_all_indexes where table_name =UPPER(p_table_name);
368 END IF;
369 END CREATE_INDEX;
370
371 END BIM_UTL_PKG;