DBA Data[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;