[Home] [Help]
PACKAGE BODY: APPS.IEM_COMP_RT_STATS_PVT
Source
1 PACKAGE BODY IEM_COMP_RT_STATS_PVT as
2 /* $Header: iemvcrsb.pls 115.2 2002/12/04 02:40:51 chtang noship $ */
3 G_PKG_NAME CONSTANT varchar2(30) :='IEM_COMP_RT_STATS_PVT ';
4
5 PROCEDURE create_item (p_api_version_number IN NUMBER,
6 p_init_msg_list IN VARCHAR2,
7 p_commit IN VARCHAR2,
8 p_type IN VARCHAR2,
9 p_param IN VARCHAR2,
10 p_value IN VARCHAR2,
11 x_return_status OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_msg_data OUT NOCOPY VARCHAR2
14
15 ) is
16 l_api_name VARCHAR2(255):='create_item';
17 l_api_version_number NUMBER:=1.0;
18 l_seq_id number;
19 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
20 l_LAST_UPDATE_DATE DATE:=SYSDATE;
21
22
23 BEGIN
24 -- Standard Start of API savepoint
25 SAVEPOINT create_item_PVT;
26 -- Standard call to check for call compatibility.
27 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
28 p_api_version_number,
29 l_api_name,
30 G_PKG_NAME)
31 THEN
32 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33 END IF;
34 -- Initialize message list if p_init_msg_list is set to TRUE.
35 IF FND_API.to_Boolean( p_init_msg_list )
36 THEN
37 FND_MSG_PUB.initialize;
38 END IF;
39 -- Initialize API return status to SUCCESS
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42
43 SELECT IEM_COMP_RT_STATS_s1.nextval
44 INTO l_seq_id
45 FROM dual;
46
47 INSERT INTO IEM_COMP_RT_STATS
48 (
49 COMP_RT_STATS_ID,
50 TYPE,
51 PARAM,
52 VALUE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_DATE
55 )
56 VALUES
57 (
58 l_seq_id,
59 p_type,
60 p_param,
61 p_value,
62 decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY),
63 l_LAST_UPDATE_DATE
64 );
65
66 -- Standard Check Of p_commit.
67 IF FND_API.To_Boolean(p_commit) THEN
68 COMMIT WORK;
69 END IF;
70 -- Standard callto get message count and if count is 1, get message info.
71 FND_MSG_PUB.Count_And_Get
72 ( p_count => x_msg_count,
73 p_data => x_msg_data
74 );
75
76 EXCEPTION
77
78 WHEN FND_API.G_EXC_ERROR THEN
79 ROLLBACK TO create_item_PVT;
80 x_return_status := FND_API.G_RET_STS_ERROR ;
81 FND_MSG_PUB.Count_And_Get
82 ( p_count => x_msg_count,
83 p_data => x_msg_data
84 );
85
86 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
87 ROLLBACK TO create_item_PVT;
88 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
89 FND_MSG_PUB.Count_And_Get
90 ( p_count => x_msg_count,
91 p_data => x_msg_data
92 );
93
94 WHEN OTHERS THEN
95 ROLLBACK TO create_item_PVT;
96 x_return_status := FND_API.G_RET_STS_ERROR;
97 IF FND_MSG_PUB.Check_Msg_Level
98 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
99 THEN
100 FND_MSG_PUB.Add_Exc_Msg
101 ( G_PKG_NAME ,
102 l_api_name
103 );
104 END IF;
105 FND_MSG_PUB.Count_And_Get
106 ( p_count => x_msg_count ,
107 p_data => x_msg_data
108 );
109
110 END create_item;
111
112 PROCEDURE update_item (p_api_version_number IN NUMBER,
113 p_init_msg_list IN VARCHAR2,
114 p_commit IN VARCHAR2,
115 p_comp_rt_stats_id IN NUMBER,
116 p_type IN VARCHAR2,
117 p_param IN VARCHAR2,
118 p_value IN VARCHAR2,
119 x_return_status OUT NOCOPY VARCHAR2,
120 x_msg_count OUT NOCOPY NUMBER,
121 x_msg_data OUT NOCOPY VARCHAR2
122 ) is
123 l_api_name VARCHAR2(255):='update_item';
124 l_api_version_number NUMBER:=1.0;
125
126 l_LAST_UPDATED_BY NUMBER:=TO_NUMBER (FND_PROFILE.VALUE('USER_ID')) ;
127 l_LAST_UPDATE_DATE DATE:=SYSDATE;
128
129 BEGIN
130 -- Standard Start of API savepoint
131 SAVEPOINT update_item_PVT;
132 -- Standard call to check for call compatibility.
133 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
134 p_api_version_number,
135 l_api_name,
136 G_PKG_NAME)
137 THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140 -- Initialize message list if p_init_msg_list is set to TRUE.
141 IF FND_API.to_Boolean( p_init_msg_list )
142 THEN
143 FND_MSG_PUB.initialize;
144 END IF;
145 -- Initialize API return status to SUCCESS
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147
148 update IEM_COMP_RT_STATS
149 set type=decode(p_type,FND_API.G_MISS_CHAR, null, null, type,p_type),
150 param=decode(p_param,FND_API.G_MISS_CHAR, null, null,param,p_param),
151 value=decode(p_value,FND_API.G_MISS_CHAR, null, null,value,p_value),
152 LAST_UPDATE_DATE = l_LAST_UPDATE_DATE,
153 LAST_UPDATED_BY = decode(l_LAST_UPDATED_BY,null,-1,l_LAST_UPDATED_BY)
154 where comp_rt_stats_id=p_comp_rt_stats_id;
155
156
157 -- Standard Check Of p_commit.
158 IF FND_API.To_Boolean(p_commit) THEN
159 COMMIT WORK;
160 END IF;
161 -- Standard callto get message count and if count is 1, get message info.
162 FND_MSG_PUB.Count_And_Get
163 ( p_count => x_msg_count,
164 p_data => x_msg_data
165 );
166 EXCEPTION
167
168 WHEN FND_API.G_EXC_ERROR THEN
169 ROLLBACK TO update_item_PVT;
170 x_return_status := FND_API.G_RET_STS_ERROR ;
171 FND_MSG_PUB.Count_And_Get
172 ( p_count => x_msg_count,
173 p_data => x_msg_data
174 );
175 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
176 ROLLBACK TO update_item_PVT;
177 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
178 FND_MSG_PUB.Count_And_Get
179 ( p_count => x_msg_count,
180 p_data => x_msg_data
181 );
182 WHEN OTHERS THEN
183 ROLLBACK TO update_item_PVT;
184 x_return_status := FND_API.G_RET_STS_ERROR;
185 IF FND_MSG_PUB.Check_Msg_Level
186 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
187 THEN
188 FND_MSG_PUB.Add_Exc_Msg
189 ( G_PKG_NAME ,
190 l_api_name
191 );
192 END IF;
193 FND_MSG_PUB.Count_And_Get
194 ( p_count => x_msg_count ,
195 p_data => x_msg_data
196 );
197
198 END update_item;
199
200 PROCEDURE delete_item (p_api_version_number IN NUMBER,
201 p_init_msg_list IN VARCHAR2,
202 p_commit IN VARCHAR2,
203 p_comp_rt_stats_id in number,
204 x_return_status OUT NOCOPY VARCHAR2,
205 x_msg_count OUT NOCOPY NUMBER,
206 x_msg_data OUT NOCOPY VARCHAR2
207 ) is
208 l_api_name VARCHAR2(255):='delete_item';
209 l_api_version_number NUMBER:=1.0;
210
211 BEGIN
212 -- Standard Start of API savepoint
213 SAVEPOINT delete_item_PVT;
214 -- Standard call to check for call compatibility.
215 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
216 p_api_version_number,
217 l_api_name,
218 G_PKG_NAME)
219 THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222 -- Initialize message list if p_init_msg_list is set to TRUE.
223 IF FND_API.to_Boolean( p_init_msg_list )
224 THEN
225 FND_MSG_PUB.initialize;
226 END IF;
227 -- Initialize API return status to SUCCESS
228 x_return_status := FND_API.G_RET_STS_SUCCESS;
229
230 delete from IEM_COMP_RT_STATS
231 where comp_rt_stats_id=p_comp_rt_stats_id;
232
233 -- Standard Check Of p_commit.
234 IF FND_API.To_Boolean(p_commit) THEN
235 COMMIT WORK;
236 END IF;
237 -- Standard callto get message count and if count is 1, get message info.
238 FND_MSG_PUB.Count_And_Get
239 ( p_count => x_msg_count,
240 p_data => x_msg_data
241 );
242 EXCEPTION
243 WHEN FND_API.G_EXC_ERROR THEN
244 ROLLBACK TO delete_item_PVT;
245 x_return_status := FND_API.G_RET_STS_ERROR ;
246 FND_MSG_PUB.Count_And_Get
247 ( p_count => x_msg_count,
248 p_data => x_msg_data
249 );
250 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
251 ROLLBACK TO delete_item_PVT;
252 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
253 FND_MSG_PUB.Count_And_Get
254 ( p_count => x_msg_count,
255 p_data => x_msg_data
256 );
257 WHEN OTHERS THEN
258 ROLLBACK TO delete_item_PVT;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 IF FND_MSG_PUB.Check_Msg_Level
261 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
262 THEN
263 FND_MSG_PUB.Add_Exc_Msg
264 ( G_PKG_NAME ,
265 l_api_name
266 );
267 END IF;
268 FND_MSG_PUB.Count_And_Get
269 ( p_count => x_msg_count ,
270 p_data => x_msg_data
271 );
272
273 END delete_item;
274
275 PROCEDURE delete_item_for_cache (p_api_version_number IN NUMBER,
276 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
277 p_commit IN VARCHAR2 := FND_API.G_FALSE,
278 x_return_status OUT NOCOPY VARCHAR2,
279 x_msg_count OUT NOCOPY NUMBER,
280 x_msg_data OUT NOCOPY VARCHAR2
281 ) is
282 l_api_name VARCHAR2(255):='delete_item_for_cache';
283 l_api_version_number NUMBER:=1.0;
284
285 BEGIN
286 -- Standard Start of API savepoint
287 SAVEPOINT delete_item_PVT;
288 -- Standard call to check for call compatibility.
289 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
290 p_api_version_number,
291 l_api_name,
292 G_PKG_NAME)
293 THEN
294 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
295 END IF;
296 -- Initialize message list if p_init_msg_list is set to TRUE.
297 IF FND_API.to_Boolean( p_init_msg_list )
298 THEN
299 FND_MSG_PUB.initialize;
300 END IF;
301 -- Initialize API return status to SUCCESS
302 x_return_status := FND_API.G_RET_STS_SUCCESS;
303
304 delete from iem_comp_rt_stats where type IN ('EMAIL_ACCOUNT', 'AGENT_ACCOUNT',
305 'AGENT', 'IMAP', 'SMTP', 'OES') and sysdate - last_update_date >1;
306
307 -- Standard Check Of p_commit.
308 IF FND_API.To_Boolean(p_commit) THEN
309 COMMIT WORK;
310 END IF;
311 -- Standard callto get message count and if count is 1, get message info.
312 FND_MSG_PUB.Count_And_Get
313 ( p_count => x_msg_count,
314 p_data => x_msg_data
315 );
316 EXCEPTION
317 WHEN FND_API.G_EXC_ERROR THEN
318 ROLLBACK TO delete_item_PVT;
319 x_return_status := FND_API.G_RET_STS_ERROR ;
320 FND_MSG_PUB.Count_And_Get
321 ( p_count => x_msg_count,
322 p_data => x_msg_data
323 );
324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
325 ROLLBACK TO delete_item_PVT;
326 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
327 FND_MSG_PUB.Count_And_Get
328 ( p_count => x_msg_count,
329 p_data => x_msg_data
330 );
331 WHEN OTHERS THEN
332 ROLLBACK TO delete_item_PVT;
333 x_return_status := FND_API.G_RET_STS_ERROR;
334 IF FND_MSG_PUB.Check_Msg_Level
335 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
336 THEN
337 FND_MSG_PUB.Add_Exc_Msg
338 ( G_PKG_NAME ,
339 l_api_name
340 );
341 END IF;
342 FND_MSG_PUB.Count_And_Get
343 ( p_count => x_msg_count ,
344 p_data => x_msg_data
345 );
346
347 END delete_item_for_cache;
348
349 END IEM_COMP_RT_STATS_PVT ;