[Home] [Help]
PACKAGE BODY: APPS.FND_TABLESPACES_PUB
Source
1 PACKAGE BODY FND_TABLESPACES_PUB AS
2 /* $Header: fndptblb.pls 115.3 2004/04/16 20:26:04 sakhtar noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'FND_TABLESPACES_PUB';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'fndptblb.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_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_TABLESPACE_TYPE IN VARCHAR2,
15 P_TABLESPACE IN VARCHAR2,
16 X_Return_Status OUT NOCOPY VARCHAR2,
17 X_Msg_Count OUT NOCOPY NUMBER,
18 X_Msg_Data OUT NOCOPY VARCHAR2)
19 IS
20 l_api_name CONSTANT VARCHAR2(30) := 'Create_Tablespaces';
21 l_api_version_number CONSTANT NUMBER := 1.0;
22 l_row_id VARCHAR2(4000);
23 BEGIN
24
25 -- Standard Start of API savepoint
26 SAVEPOINT CREATE_TABLESPACES_PUB;
27
28 -- Standard call to check for call compatibility.
29 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
30 p_api_version_number,
31 l_api_name,
32 G_PKG_NAME)
33 THEN
34 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
35 END IF;
36
37
38 -- Initialize message list if p_init_msg_list is set to TRUE.
39 IF FND_API.to_Boolean( p_init_msg_list )
40 THEN
41 FND_MSG_PUB.initialize;
42 END IF;
43
44 -- Initialize API return status to SUCCESS
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 VALIDATE_TABLESPACES(
48 P_Init_Msg_List => FND_API.G_FALSE,
49 P_Validation_mode => AS_UTILITY_PVT.G_CREATE,
50 P_TABLESPACE_TYPE => p_tablespace_type,
51 P_TABLESPACE => p_tablespace,
52 X_Return_Status => x_return_status,
53 X_Msg_Count => x_msg_count,
54 X_Msg_Data => x_msg_data);
55 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
56 RAISE FND_API.G_EXC_ERROR;
57 END IF;
58
59 FND_TABLESPACES_PKG.INSERT_ROW(
60 X_ROWID => l_row_id,
61 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
62 P_TABLESPACE => P_TABLESPACE,
63 P_CUSTOM_FLAG => 'C',
64 P_CREATION_DATE => sysdate,
65 P_CREATED_BY => G_USER_ID,
66 P_LAST_UPDATE_DATE => sysdate,
67 P_LAST_UPDATED_BY => G_USER_ID,
68 P_LAST_UPDATE_LOGIN => G_LOGIN_ID );
69
70 -- Standard check for p_commit
71 IF FND_API.to_Boolean( p_commit )
72 THEN
73 COMMIT WORK;
74 END IF;
75
76 -- Standard call to get message count and if count is 1, get message info.
77 FND_MSG_PUB.Count_And_Get
78 ( p_count => x_msg_count,
79 p_data => x_msg_data);
80
81 EXCEPTION
82 WHEN FND_API.G_EXC_ERROR THEN
83 ROLLBACK TO CREATE_TABLESPACES_PUB;
84 x_return_status := FND_API.G_RET_STS_ERROR ;
85 FND_MSG_PUB.Count_And_Get
86 (p_count => x_msg_count,
87 p_data => x_msg_data);
88
89 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
90 ROLLBACK TO CREATE_TABLESPACES_PUB;
91 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
92 FND_MSG_PUB.Count_And_Get
93 (p_count => x_msg_count,
94 p_data => x_msg_data);
95
96 WHEN OTHERS THEN
97 ROLLBACK TO CREATE_TABLESPACES_PUB;
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
99 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
100 THEN
101 FND_MSG_PUB.Add_Exc_Msg
102 ( G_PKG_NAME,
103 l_api_name);
104 END IF;
105 FND_MSG_PUB.Count_And_Get
106 (p_count => x_msg_count,
107 p_data => x_msg_data);
108 END CREATE_TABLESPACES;
109
110
111 PROCEDURE UPDATE_TABLESPACES(
112 P_Api_Version_Number IN NUMBER,
113 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
114 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
115 P_TABLESPACE_TYPE IN VARCHAR2,
116 P_TABLESPACE IN VARCHAR2,
117 X_Return_Status OUT NOCOPY VARCHAR2,
118 X_Msg_Count OUT NOCOPY NUMBER,
119 X_Msg_Data OUT NOCOPY VARCHAR2)
120 IS
121 l_api_name CONSTANT VARCHAR2(30) := 'Update_Tablespaces';
122 l_api_version_number CONSTANT NUMBER := 1.0;
123 l_row_id VARCHAR2(4000);
124 BEGIN
125
126 -- Standard Start of API savepoint
127 SAVEPOINT UPDATE_TABLESPACES_PUB;
128
129 -- Standard call to check for call compatibility.
130 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
131 p_api_version_number,
132 l_api_name,
133 G_PKG_NAME)
134 THEN
135 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
136 END IF;
137
138
139 -- Initialize message list if p_init_msg_list is set to TRUE.
140 IF FND_API.to_Boolean( p_init_msg_list )
141 THEN
142 FND_MSG_PUB.initialize;
143 END IF;
144
145 -- Initialize API return status to SUCCESS
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 VALIDATE_TABLESPACES(
149 P_Init_Msg_List => FND_API.G_FALSE,
150 P_Validation_mode => AS_UTILITY_PVT.G_UPDATE,
151 P_TABLESPACE_TYPE => p_tablespace_type,
152 P_TABLESPACE => p_tablespace,
153 X_Return_Status => x_return_status,
154 X_Msg_Count => x_msg_count,
155 X_Msg_Data => x_msg_data);
156 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
157 RAISE FND_API.G_EXC_ERROR;
158 END IF;
159
160 FND_TABLESPACES_PKG.UPDATE_ROW(
161 P_TABLESPACE_TYPE => P_TABLESPACE_TYPE,
162 P_TABLESPACE => P_TABLESPACE,
163 P_CUSTOM_FLAG => 'C',
164 P_LAST_UPDATE_DATE => sysdate,
165 P_LAST_UPDATED_BY => G_USER_ID,
166 P_LAST_UPDATE_LOGIN => G_LOGIN_ID );
167
168 -- Standard check for p_commit
169 IF FND_API.to_Boolean( p_commit )
170 THEN
171 COMMIT WORK;
172 END IF;
173
174 -- Standard call to get message count and if count is 1, get message info.
175 FND_MSG_PUB.Count_And_Get
176 ( p_count => x_msg_count,
177 p_data => x_msg_data);
178
179 EXCEPTION
180 WHEN FND_API.G_EXC_ERROR THEN
181 ROLLBACK TO UPDATE_TABLESPACES_PUB;
182 x_return_status := FND_API.G_RET_STS_ERROR ;
183 FND_MSG_PUB.Count_And_Get
184 (p_count => x_msg_count,
185 p_data => x_msg_data);
186
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188 ROLLBACK TO UPDATE_TABLESPACES_PUB;
189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
190 FND_MSG_PUB.Count_And_Get
191 (p_count => x_msg_count,
192 p_data => x_msg_data);
193
194 WHEN OTHERS THEN
195 ROLLBACK TO UPDATE_TABLESPACES_PUB;
196 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
197 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
198 THEN
199 FND_MSG_PUB.Add_Exc_Msg
200 ( G_PKG_NAME,
201 l_api_name);
202 END IF;
203 FND_MSG_PUB.Count_And_Get
204 (p_count => x_msg_count,
205 p_data => x_msg_data);
206 END UPDATE_TABLESPACES;
207
208
209 PROCEDURE VALIDATE_TABLESPACES (
210 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
211 P_Validation_mode IN VARCHAR2,
212 P_TABLESPACE_TYPE IN VARCHAR2,
213 P_TABLESPACE IN VARCHAR2,
214 X_Return_Status OUT NOCOPY VARCHAR2,
215 X_Msg_Count OUT NOCOPY NUMBER,
216 X_Msg_Data OUT NOCOPY VARCHAR2)
217 IS
218 cursor c1 is
219 select 1
220 from fnd_tablespaces
221 where tablespace_type = p_tablespace_type;
222 l_dummy NUMBER;
223 BEGIN
224
225 -- validate NOT NULL column
226 IF (p_TABLESPACE_TYPE is NULL OR p_TABLESPACE_TYPE = FND_API.G_MISS_CHAR)
227 THEN
228 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
229 FND_MESSAGE.Set_Token('ROUTINE', 'FND_TABLESPACES_PUB');
230 FND_MESSAGE.Set_Token('FIELD', 'Tablespace Type');
231 FND_MSG_PUB.Add;
232 x_return_status := FND_API.G_RET_STS_ERROR;
233 END IF;
234
235 -- validate NOT NULL column
236 IF (p_TABLESPACE is NULL OR p_TABLESPACE = FND_API.G_MISS_CHAR)
237 THEN
238 FND_MESSAGE.Set_Name('FND', 'OATM_REQUIRED_ENTRY');
239 FND_MESSAGE.Set_Token('ROUTINE', 'FND_TABLESPACES_PUB');
240 FND_MESSAGE.Set_Token('FIELD', 'Tablespace Name');
241 FND_MSG_PUB.Add;
242 x_return_status := FND_API.G_RET_STS_ERROR;
243 END IF;
244
245 IF (p_validation_mode = AS_UTILITY_PVT.G_CREATE)
246 THEN
247 IF (p_TABLESPACE_TYPE is not NULL AND p_TABLESPACE_TYPE <> FND_API.G_MISS_CHAR)
248 THEN
249 OPEN c1;
250 FETCH c1 INTO l_dummy;
251 if c1%FOUND then
252 FND_MESSAGE.Set_Name('FND', 'OATM_NO_INSERT');
253 FND_MESSAGE.Set_Token('ROUTINE', 'FND_TABLESPACES_PUB');
254 FND_MESSAGE.Set_Token('FIELD_NAME', 'Tablespace Type');
255 FND_MESSAGE.Set_Token('FIELD_VALUE', p_TABLESPACE_TYPE);
256 FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_TABLESPACES');
257 FND_MSG_PUB.Add;
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 end if;
260 CLOSE c1;
261 END IF;
262 ELSIF (p_validation_mode = AS_UTILITY_PVT.G_UPDATE)
263 THEN
264 IF (p_TABLESPACE_TYPE is not NULL AND p_TABLESPACE_TYPE <> FND_API.G_MISS_CHAR)
265 THEN
266 OPEN c1;
267 FETCH c1 INTO l_dummy;
268 if c1%NOTFOUND then
269 FND_MESSAGE.Set_Name('FND', 'OATM_NO_UPDATE');
270 FND_MESSAGE.Set_Token('ROUTINE', 'FND_TABLESPACES_PUB');
271 FND_MESSAGE.Set_Token('FIELD_NAME', 'Tablespace Type');
272 FND_MESSAGE.Set_Token('FIELD_VALUE', p_TABLESPACE_TYPE);
273 FND_MESSAGE.Set_Token('TABLE_NAME', 'FND_TABLESPACES');
274 FND_MSG_PUB.Add;
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 end if;
277 CLOSE c1;
278 END IF;
279 END IF;
280
281 -- Standard call to get message count and if count is 1, get message info.
282 FND_MSG_PUB.Count_And_Get
283 ( p_count => x_msg_count,
284 p_data => x_msg_data
285 );
286 END VALIDATE_TABLESPACES;
287
288
289 END FND_TABLESPACES_PUB;