[Home] [Help]
PACKAGE BODY: APPS.FND_OBJECT_TABLESPACES_PUB
Source
1 PACKAGE BODY FND_OBJECT_TABLESPACES_PUB AS
2 /* $Header: fndpobjb.pls 115.3 2004/04/16 20:24:18 sakhtar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'FND_OBJECT_TABLESPACES_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'fndpobjb.pls';
6
7 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9
10 PROCEDURE CREATE_OBJECT_TABLESPACES (
11 P_Api_Version_Number IN NUMBER,
12 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
13 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
14 P_application_short_name in varchar2,
15 P_object_name in varchar2,
16 P_tablespace_type in varchar2,
17 P_object_type in varchar2 := 'TABLE',
18 X_Return_Status OUT NOCOPY VARCHAR2,
19 X_Msg_Count OUT NOCOPY NUMBER,
20 X_Msg_Data OUT NOCOPY VARCHAR2)
21 IS
22 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_OBJECT_TABLESPACES';
23 l_api_version_number CONSTANT NUMBER := 1.0;
24 l_row_id VARCHAR2(4000);
25 l_application_id NUMBER;
26 l_oracle_username FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
27 BEGIN
28
29 -- Standard Start of API savepoint
30 SAVEPOINT CREATE_OBJECT_TABLESPACES_PUB;
31
32 -- Standard call to check for call compatibility.
33 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
34 p_api_version_number,
35 l_api_name,
36 G_PKG_NAME)
37 THEN
38 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
39 END IF;
40
41
42 -- Initialize message list if p_init_msg_list is set to TRUE.
43 IF FND_API.to_Boolean( p_init_msg_list )
44 THEN
45 FND_MSG_PUB.initialize;
46 END IF;
47
48 -- Initialize API return status to SUCCESS
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50
51 VALIDATE_OBJECT_TABLESPACES(
52 P_Init_Msg_List => FND_API.G_FALSE,
53 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
54 P_application_short_name => p_APPLICATION_SHORT_NAME,
55 P_OBJECT_NAME => P_OBJECT_NAME,
56 P_OBJECT_TYPE => P_OBJECT_TYPE,
57 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
58 x_application_id => l_application_id,
59 x_oracle_username => l_oracle_username,
60 X_Return_Status => x_return_status,
61 X_Msg_Count => x_msg_count,
62 X_Msg_Data => x_msg_data);
63 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
64 RAISE FND_API.G_EXC_ERROR;
65 END IF;
66
67 FND_OBJECT_TABLESPACES_PKG.INSERT_ROW(
68 X_ROWID => l_row_id,
69 P_APPLICATION_ID => l_application_id,
70 P_OBJECT_NAME => P_OBJECT_NAME,
71 P_OBJECT_TYPE => P_OBJECT_TYPE,
72 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
73 P_CUSTOM_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
74 P_OBJECT_SOURCE => null ,
75 P_ORACLE_USERNAME => l_oracle_username,
76 P_CUSTOM_FLAG => 'C',
77 P_CREATION_DATE => sysdate,
78 P_CREATED_BY => G_USER_ID,
79 P_LAST_UPDATE_DATE => sysdate,
80 P_LAST_UPDATED_BY => G_USER_ID,
81 P_LAST_UPDATE_LOGIN => G_LOGIN_ID );
82
83 -- Standard check for p_commit
84 IF FND_API.to_Boolean( p_commit )
85 THEN
86 COMMIT WORK;
87 END IF;
88
89 -- Standard call to get message count and if count is 1, get message info.
90 FND_MSG_PUB.Count_And_Get
91 ( p_count => x_msg_count,
92 p_data => x_msg_data);
93
94 EXCEPTION
95 WHEN FND_API.G_EXC_ERROR THEN
96 ROLLBACK TO CREATE_OBJECT_TABLESPACES_PUB;
97 x_return_status := FND_API.G_RET_STS_ERROR ;
98 FND_MSG_PUB.Count_And_Get
99 (p_count => x_msg_count,
100 p_data => x_msg_data);
101
102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
103 ROLLBACK TO CREATE_OBJECT_TABLESPACES_PUB;
104 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
105 FND_MSG_PUB.Count_And_Get
106 (p_count => x_msg_count,
107 p_data => x_msg_data);
108
109 WHEN OTHERS THEN
110 ROLLBACK TO CREATE_OBJECT_TABLESPACES_PUB;
111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
112 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
113 THEN
114 FND_MSG_PUB.Add_Exc_Msg
115 ( G_PKG_NAME,
116 l_api_name);
117 END IF;
118 FND_MSG_PUB.Count_And_Get
119 (p_count => x_msg_count,
120 p_data => x_msg_data);
121
122 END CREATE_OBJECT_TABLESPACES;
123
124
125 PROCEDURE UPDATE_OBJECT_TABLESPACES (
126 P_Api_Version_Number IN NUMBER,
127 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
128 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
129 P_application_short_name in varchar2,
130 P_object_name in varchar2,
131 P_tablespace_type in varchar2,
132 P_object_type in varchar2 := 'TABLE',
133 X_Return_Status OUT NOCOPY VARCHAR2,
134 X_Msg_Count OUT NOCOPY NUMBER,
135 X_Msg_Data OUT NOCOPY VARCHAR2)
136 IS
137 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OBJECT_TABLESPACES';
138 l_api_version_number CONSTANT NUMBER := 1.0;
139 l_row_id VARCHAR2(4000);
140 l_application_id NUMBER;
141 l_oracle_username FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
142 BEGIN
143
144 -- Standard Start of API savepoint
145 SAVEPOINT UPDATE_OBJECT_TABLESPACES_PUB;
146
147 -- Standard call to check for call compatibility.
148 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
149 p_api_version_number,
150 l_api_name,
151 G_PKG_NAME)
152 THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155
156
157 -- Initialize message list if p_init_msg_list is set to TRUE.
158 IF FND_API.to_Boolean( p_init_msg_list )
159 THEN
160 FND_MSG_PUB.initialize;
161 END IF;
162
163 -- Initialize API return status to SUCCESS
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 VALIDATE_OBJECT_TABLESPACES(
167 P_Init_Msg_List => FND_API.G_FALSE,
168 P_Validation_mode => AS_UTILITY_PVT.G_UPDATE,
169 P_application_short_name => p_APPLICATION_SHORT_NAME,
170 P_OBJECT_NAME => P_OBJECT_NAME,
171 P_OBJECT_TYPE => P_OBJECT_TYPE,
172 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
173 x_application_id => l_application_id,
174 x_oracle_username => l_oracle_username,
175 X_Return_Status => x_return_status,
176 X_Msg_Count => x_msg_count,
177 X_Msg_Data => x_msg_data);
178 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
179 RAISE FND_API.G_EXC_ERROR;
180 END IF;
181
182 FND_OBJECT_TABLESPACES_PKG.UPDATE_ROW(
183 P_APPLICATION_ID => l_application_id,
184 P_OBJECT_NAME => p_OBJECT_NAME,
185 P_OBJECT_TYPE => p_OBJECT_TYPE,
186 P_TABLESPACE_TYPE => NULL,
187 P_CUSTOM_TABLESPACE_TYPE => p_TABLESPACE_TYPE,
188 P_OBJECT_SOURCE => NULL,
189 P_ORACLE_USERNAME => l_oracle_username,
190 P_CUSTOM_FLAG => 'C',
191 P_LAST_UPDATE_DATE => sysdate,
192 P_LAST_UPDATED_BY => G_USER_ID,
193 P_LAST_UPDATE_LOGIN => G_LOGIN_ID);
194
195 -- Standard check for p_commit
196 IF FND_API.to_Boolean( p_commit )
197 THEN
198 COMMIT WORK;
199 END IF;
200
201 -- Standard call to get message count and if count is 1, get message info.
202 FND_MSG_PUB.Count_And_Get
203 ( p_count => x_msg_count,
204 p_data => x_msg_data);
205
206 EXCEPTION
207 WHEN FND_API.G_EXC_ERROR THEN
208 ROLLBACK TO UPDATE_OBJECT_TABLESPACES_PUB;
209 x_return_status := FND_API.G_RET_STS_ERROR ;
210 FND_MSG_PUB.Count_And_Get
211 (p_count => x_msg_count,
212 p_data => x_msg_data);
213
214 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
215 ROLLBACK TO UPDATE_OBJECT_TABLESPACES_PUB;
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
217 FND_MSG_PUB.Count_And_Get
218 (p_count => x_msg_count,
219 p_data => x_msg_data);
220
221 WHEN OTHERS THEN
222 ROLLBACK TO UPDATE_OBJECT_TABLESPACES_PUB;
223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
224 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
225 THEN
226 FND_MSG_PUB.Add_Exc_Msg
227 ( G_PKG_NAME,
228 l_api_name);
229 END IF;
230 FND_MSG_PUB.Count_And_Get
231 (p_count => x_msg_count,
232 p_data => x_msg_data);
233
234 END UPDATE_OBJECT_TABLESPACES;
235
236
237 PROCEDURE VALIDATE_OBJECT_TABLESPACES (
238 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
239 P_Validation_mode IN VARCHAR2,
240 P_application_short_name in varchar2,
241 P_object_name in varchar2,
242 P_tablespace_type in varchar2,
243 P_object_type in varchar2,
244 x_application_id OUT NOCOPY NUMBER,
245 x_oracle_username OUT NOCOPY VARCHAR2,
246 X_Return_Status OUT NOCOPY VARCHAR2,
247 X_Msg_Count OUT NOCOPY NUMBER,
248 X_Msg_Data OUT NOCOPY VARCHAR2)
249 IS
250 cursor c1 is
251 select 1
252 from fnd_tablespaces
253 where tablespace_type = p_tablespace_type;
254
255 cursor c2 is
256 select application_id
257 from fnd_application
258 where application_short_name = p_application_short_name;
259
260 cursor c4(l_app_id NUMBER) is
261 select oracle_username
262 from fnd_product_installations fpi,
263 fnd_oracle_userid fou
264 where fpi.oracle_id = fou.oracle_id
265 and fpi.application_id = l_app_id;
266
267 cursor c5(l_app_id NUMBER) is
268 select 1
269 from fnd_object_tablespaces
270 where application_id = l_app_id
271 and object_name = p_object_name;
272
273 cursor c3(l_oracle_user VARCHAR2) is
274 select 1
275 from dba_tables dt
276 where dt.owner = l_oracle_user
277 AND dt.table_name = p_object_name
278 AND EXISTS (select 1 from fnd_oracle_userid fou
279 where fou.oracle_username = dt.owner
280 and read_only_flag IN ('E','A','U','K','M'))
281 AND NVL(dt.temporary, 'N') = 'N'
282 AND NVL(dt.iot_type, 'X') NOT IN ('IOT', 'IOT_OVERFLOW')
283 AND NOT EXISTS ( select ds.table_name
284 from all_snapshots ds
285 where ds.owner = dt.owner
286 and ds.table_name = dt.table_name)
287 AND NOT EXISTS ( select dsl.log_table
288 from all_snapshot_logs dsl
289 where dsl.log_owner = dt.owner
290 and dsl.log_table = dt.table_name)
291 AND NOT EXISTS ( select dqt.queue_table
292 from all_queue_tables dqt
293 where dqt.owner = dt.owner
294 and dqt.queue_table = dt.table_name)
295 AND dt.table_name NOT LIKE 'AQ$%'
296 AND dt.table_name NOT LIKE 'DR$%'
297 AND dt.table_name NOT LIKE 'RUPD$%'
298 AND dt.table_name NOT LIKE 'MDRT%$';
299 l_dummy NUMBER;
300 l_application_id NUMBER;
301 l_oracle_username FND_ORACLE_USERID.ORACLE_USERNAME%TYPE;
302 BEGIN
303
304 -- validate NOT NULL column
305 IF (p_APPLICATION_SHORT_NAME is NULL OR p_APPLICATION_SHORT_NAME = FND_API.G_MISS_CHAR)
306 THEN
307 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
308 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
309 FND_MESSAGE.Set_Token('FIELD', 'Application Short Name');
310 FND_MSG_PUB.Add;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 END IF;
313
314 -- validate NOT NULL column
315 IF (p_OBJECT_NAME is NULL OR p_OBJECT_NAME = FND_API.G_MISS_CHAR)
316 THEN
317 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
318 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
319 FND_MESSAGE.Set_Token('FIELD', 'Object Name');
320 FND_MSG_PUB.Add;
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 END IF;
323
324 -- validate NOT NULL column
325 IF (p_TABLESPACE_TYPE is NULL OR p_TABLESPACE_TYPE = FND_API.G_MISS_CHAR)
326 THEN
327 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
328 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
329 FND_MESSAGE.Set_Token('FIELD', 'Tablespace Type');
330 FND_MSG_PUB.Add;
331 x_return_status := FND_API.G_RET_STS_ERROR;
332 END IF;
333
334 IF (p_OBJECT_TYPE is NULL OR p_OBJECT_TYPE = FND_API.G_MISS_CHAR)
335 THEN
336 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
337 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
338 FND_MESSAGE.Set_Token('FIELD', 'Object Type');
339 FND_MSG_PUB.Add;
340 x_return_status := FND_API.G_RET_STS_ERROR;
341 END IF;
342
343 IF (p_APPLICATION_SHORT_NAME is not NULL AND p_APPLICATION_SHORT_NAME <> FND_API.G_MISS_CHAR)
344 THEN
345 OPEN c2;
346 FETCH c2 INTO l_application_id;
347 if c2%NOTFOUND then
348 FND_MESSAGE.Set_Name('FND', 'OATM_INVALID_ENTRY');
349 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
350 FND_MESSAGE.Set_Token('FIELD_NAME', 'Application Short Name');
351 FND_MESSAGE.Set_Token('FIELD_VALUE', p_APPLICATION_SHORT_NAME);
352 FND_MSG_PUB.Add;
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 end if;
355 CLOSE c2;
356 END IF;
357
358 IF (p_OBJECT_TYPE is NOT NULL OR p_OBJECT_TYPE <> FND_API.G_MISS_CHAR)
359 THEN
360 if p_OBJECT_TYPE <> 'TABLE' then
361 FND_MESSAGE.Set_Name('FND', 'OATM_INVALID_ENTRY');
362 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
363 FND_MESSAGE.Set_Token('FIELD_NAME', 'Object Type');
364 FND_MESSAGE.Set_Token('FIELD_VALUE', p_OBJECT_TYPE);
365 FND_MSG_PUB.Add;
366 x_return_status := FND_API.G_RET_STS_ERROR;
367 end if;
368 END IF;
369
370 IF (p_validation_mode = AS_UTILITY_PVT.G_CREATE)
371 THEN
372 IF (p_OBJECT_NAME is not NULL AND p_OBJECT_NAME <> FND_API.G_MISS_CHAR)
373 THEN
374 OPEN c4(l_application_id);
375 FETCH c4 INTO l_oracle_username;
376 CLOSE c4;
377
378 OPEN c5(l_application_id);
379 FETCH c5 INTO l_dummy;
380 if c5%FOUND then
381 FND_MESSAGE.Set_Name('FND', 'OATM_NO_INSERT');
382 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
383 FND_MESSAGE.Set_Token('FIELD_NAME', 'Object Name');
384 FND_MESSAGE.Set_Token('FIELD_VALUE', p_OBJECT_NAME);
385 FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_OBJECT_TABLESPACES');
386 FND_MSG_PUB.Add;
387 x_return_status := FND_API.G_RET_STS_ERROR;
388 end if;
389 CLOSE c5;
390
391 OPEN c3(l_oracle_username);
392 FETCH c3 INTO l_dummy;
393 if c3%NOTFOUND then
394 FND_MESSAGE.Set_Name('FND', 'OATM_INVALID_ENTRY');
395 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
396 FND_MESSAGE.Set_Token('FIELD_NAME', 'Object Name');
397 FND_MESSAGE.Set_Token('FIELD_VALUE', p_OBJECT_NAME);
398 FND_MSG_PUB.Add;
399 x_return_status := FND_API.G_RET_STS_ERROR;
400 end if;
401 CLOSE c3;
402 END IF;
403 ELSIF (p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
404 THEN
405 IF (p_OBJECT_NAME is not NULL AND p_OBJECT_NAME <> FND_API.G_MISS_CHAR)
406 THEN
407 OPEN c4(l_application_id);
408 FETCH c4 INTO l_oracle_username;
409 CLOSE c4;
410
411 OPEN c3(l_oracle_username);
412 FETCH c3 INTO l_dummy;
413 if c3%NOTFOUND then
414 FND_MESSAGE.Set_Name('FND', 'OATM_NO_UPDATE');
415 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
416 FND_MESSAGE.Set_Token('FIELD_NAME', 'Object Name');
417 FND_MESSAGE.Set_Token('FIELD_VALUE', p_OBJECT_NAME);
418 FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_OBJECT_TABLESPACES');
419 FND_MSG_PUB.Add;
420 x_return_status := FND_API.G_RET_STS_ERROR;
421 end if;
422 CLOSE c3;
423 END IF;
424 END IF;
425
426 IF (p_TABLESPACE_TYPE is not NULL AND p_TABLESPACE_TYPE <> FND_API.G_MISS_CHAR)
427 THEN
428 OPEN c1;
429 FETCH c1 INTO l_dummy;
430 if c1%NOTFOUND then
431 FND_MESSAGE.Set_Name('FND', 'OATM_NO_UPDATE');
432 FND_MESSAGE.Set_Token('ROUTINE', 'FND_OBJECT_TABLESPACES_PUB');
433 FND_MESSAGE.Set_Token('FIELD_NAME', 'Tablespace Type');
434 FND_MESSAGE.Set_Token('FIELD_VALUE', p_TABLESPACE_TYPE);
435 FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_TABLESPACES');
436 FND_MSG_PUB.Add;
437 x_return_status := FND_API.G_RET_STS_ERROR;
438 end if;
439 CLOSE c1;
440 END IF;
441
442 x_application_id := l_application_id;
443 x_oracle_username := l_oracle_username;
444
445 -- Standard call to get message count and if count is 1, get message info.
446 FND_MSG_PUB.Count_And_Get
447 ( p_count => x_msg_count,
448 p_data => x_msg_data
449 );
450 END VALIDATE_OBJECT_TABLESPACES;
451
452 END FND_OBJECT_TABLESPACES_PUB;