[Home] [Help]
PACKAGE BODY: APPS.JTF_PREFAB_STATISTICS_PUB
Source
1 Package Body JTF_PREFAB_STATISTICS_PUB AS
2 /* $Header: jtfprefabstb.pls 120.3 2005/10/28 00:22:56 emekala ship $ */
3
4 -- global variables --
5 G_PKG_NAME CONSTANT VARCHAR2(30):='JTF_PREFAB_STATISTICS_PUB';
6 G_FILE_NAME CONSTANT VARCHAR2(16):='jtfprefabstb.pls';
7
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10
11 PROCEDURE INSERT_STATISTICS(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15
16 p_policy_id IN jtf_prefab_statistics.policy_id%TYPE,
17 p_wsh_po_id IN jtf_prefab_statistics.wsh_po_id%TYPE,
18 p_start_time IN jtf_prefab_statistics.start_time%TYPE,
19 p_end_time IN jtf_prefab_statistics.end_time%TYPE,
20 p_last_update_time IN jtf_prefab_statistics.last_update_time%TYPE,
21 p_pages_last_run IN jtf_prefab_statistics.pages_last_run%TYPE,
22 p_pages_crawled IN jtf_prefab_statistics.pages_crawled%TYPE,
23 p_refresh_rate IN jtf_prefab_statistics.refresh_rate%TYPE,
24 p_system_status IN jtf_prefab_statistics.system_status%TYPE,
25 p_error_status IN jtf_prefab_statistics.error_status%TYPE,
26 p_depth IN jtf_prefab_statistics.depth%TYPE,
27 p_disk_used IN jtf_prefab_statistics.disk_used%TYPE,
28 p_avg_mem IN jtf_prefab_statistics.avg_mem%TYPE,
29 p_avg_cpu IN jtf_prefab_statistics.avg_cpu%TYPE,
30 p_hit_rate IN jtf_prefab_statistics.hit_rate%TYPE,
31
32 p_object_version_number OUT NOCOPY NUMBER,
33
34 x_return_status OUT NOCOPY VARCHAR2,
35 x_msg_count OUT NOCOPY NUMBER,
36 x_msg_data OUT NOCOPY VARCHAR2
37 ) AS
38 -- local variables --
39 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_STATISTICS';
40 l_api_version NUMBER := p_api_version;
41
42 l_statistics_id NUMBER := NULL;
43 CURSOR statistics_id IS SELECT jtf_prefab_statistics_s.NEXTVAL FROM sys.dual;
44
45 BEGIN
46 -- Standard Start of API savepoint
47 SAVEPOINT INSERT_STATISTICS;
48
49 -- Standard call to check for call compatibility.
50 IF NOT FND_API.Compatible_API_Call (
51 l_api_version,
52 p_api_version,
53 l_api_name,
54 G_PKG_NAME)
55 THEN
56 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
57 END IF;
58
59 -- Initialize message list if p_init_msg_list is set to TRUE.
60 IF FND_API.To_Boolean( p_init_msg_list ) THEN
61 FND_MSG_PUB.initialize;
62 END IF;
63
64 -- Initialize API return status to success
65 x_return_status := FND_API.G_RET_STS_SUCCESS;
66
67 -- real logic --
68 ----------------
69 -- Use Sequence as the unique key
70 OPEN statistics_id;
71 FETCH statistics_id INTO l_statistics_id;
72 CLOSE statistics_id;
73
74 p_object_version_number := 1;
75
76 INSERT INTO jtf_prefab_statistics (statistics_id,
77 created_by,
78 creation_date,
79 last_updated_by,
80 last_update_date,
81 last_update_login,
82 object_version_number,
83 -- security_group_id,
84 policy_id,
85 wsh_po_id,
86 start_time,
87 end_time,
88 last_update_time,
89 pages_last_run,
90 pages_crawled,
91 refresh_rate,
92 system_status,
93 error_status,
94 depth,
95 disk_used,
96 avg_mem,
97 avg_cpu,
98 hit_rate)
99 VALUES (l_statistics_id,
100 G_USER_ID,
101 SYSDATE,
102 G_USER_ID,
103 SYSDATE,
104 G_LOGIN_ID,
105 p_object_version_number,
106 -- NULL,
107 p_policy_id,
108 p_wsh_po_id,
109 p_start_time,
110 p_end_time,
111 p_last_update_time,
112 p_pages_last_run,
113 p_pages_crawled,
114 p_refresh_rate,
115 p_system_status,
116 p_error_status,
117 p_depth,
118 p_disk_used,
119 p_avg_mem,
120 p_avg_cpu,
121 p_hit_rate);
122
123 -----------------------
124 -- end of real logic --
125
126 -- Standard check of p_commit.
127 IF (FND_API.To_Boolean(p_commit)) THEN
128 COMMIT WORK;
129 END IF;
130
131 -- Standard call to get message count and if count is 1, get message info.
132 FND_MSG_PUB.Count_And_Get(
133 p_count => x_msg_count,
134 p_data => x_msg_data );
135
136 EXCEPTION
137 WHEN FND_API.G_EXC_ERROR THEN
138 ROLLBACK TO INSERT_STATISTICS;
139 x_return_status := FND_API.G_RET_STS_ERROR ;
140
141 FND_MSG_PUB.Count_And_Get(
142 p_count => x_msg_count,
143 p_data => x_msg_data );
144
145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
146 ROLLBACK TO INSERT_STATISTICS;
147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
148
149 FND_MSG_PUB.Count_And_Get(
150 p_count => x_msg_count,
151 p_data => x_msg_data );
152
153 WHEN OTHERS THEN
154 ROLLBACK TO INSERT_STATISTICS;
155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
156
157 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
158 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
159 END IF;
160
161 FND_MSG_PUB.Count_And_Get(
162 p_count => x_msg_count,
163 p_data => x_msg_data );
164
165 END INSERT_STATISTICS;
166
167 PROCEDURE UPDATE_STATISTICS(
168 p_api_version IN NUMBER,
169 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
170 p_commit IN VARCHAR2 := FND_API.G_FALSE,
171
172 p_policy_id IN jtf_prefab_statistics.policy_id%TYPE,
173 p_wsh_po_id IN jtf_prefab_statistics.wsh_po_id%TYPE,
174 p_start_time IN jtf_prefab_statistics.start_time%TYPE,
175 p_end_time IN jtf_prefab_statistics.end_time%TYPE,
176 p_last_update_time IN jtf_prefab_statistics.last_update_time%TYPE,
177 p_pages_last_run IN jtf_prefab_statistics.pages_last_run%TYPE,
178 p_pages_crawled IN jtf_prefab_statistics.pages_crawled%TYPE,
179 p_refresh_rate IN jtf_prefab_statistics.refresh_rate%TYPE,
180 p_system_status IN jtf_prefab_statistics.system_status%TYPE,
181 p_error_status IN jtf_prefab_statistics.error_status%TYPE,
182 p_depth IN jtf_prefab_statistics.depth%TYPE,
183 p_disk_used IN jtf_prefab_statistics.disk_used%TYPE,
184 p_avg_mem IN jtf_prefab_statistics.avg_mem%TYPE,
185 p_avg_cpu IN jtf_prefab_statistics.avg_cpu%TYPE,
186 p_hit_rate IN jtf_prefab_statistics.hit_rate%TYPE,
187
188 p_object_version_number IN OUT NOCOPY NUMBER,
189
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2
193 ) AS
194 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_STATISTICS';
195 l_api_version NUMBER := p_api_version;
196
197 l_object_version NUMBER := NULL;
198
199 -- l_um_row JTF_XML_URL_MAPPINGS_B%ROWTYPE;
200 BEGIN
201 -- Standard Start of API savepoint
202 SAVEPOINT UPDATE_STATISTICS;
203
204 -- Standard call to check for call compatibility.
205 IF NOT FND_API.Compatible_API_Call (
206 l_api_version,
207 p_api_version,
208 l_api_name,
209 G_PKG_NAME)
210 THEN
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213
214 -- Initialize message list if p_init_msg_list is set to TRUE.
215 IF FND_API.To_Boolean( p_init_msg_list ) THEN
216 FND_MSG_PUB.initialize;
217 END IF;
218
219 -- Initialize API return status to success
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221
222 -- real logic --
223 ----------------
224 /*
225 SELECT object_version_number INTO l_object_version
226 FROM jtf_prefab_statistics
227 WHERE application_id = p_application_id;
228
229 -- checking for object version number
230 if (l_object_version IS NULL OR l_object_version > p_OBJ_VER_NUMBER) THEN
231 RAISE FND_API.G_EXC_ERROR;
232 ELSE
233 p_obj_ver_number := p_obj_ver_number + 1;
234 END IF;
235 */
236
237 UPDATE jtf_prefab_statistics
238 SET last_updated_by = G_USER_ID,
239 last_update_date = SYSDATE,
240 last_update_login = G_LOGIN_ID,
241 object_version_number = p_object_version_number,
242 start_time = p_start_time,
243 end_time = p_end_time,
244 last_update_time = p_last_update_time,
245 pages_last_run = p_pages_last_run,
246 pages_crawled = p_pages_crawled,
247 refresh_rate = p_refresh_rate,
248 system_status = p_system_status,
249 error_status = p_error_status,
250 depth = p_depth,
251 disk_used = p_disk_used,
252 avg_mem = p_avg_mem,
253 avg_cpu = p_avg_cpu,
254 hit_rate = p_hit_rate
255 WHERE policy_id = p_policy_id AND wsh_po_id = p_wsh_po_id;
256
257 -----------------------
258 -- end of real logic --
259
260 -- Standard check of p_commit.
261 IF (FND_API.To_Boolean(p_commit)) THEN
262 COMMIT WORK;
263 END IF;
264
265 -- Standard call to get message count and if count is 1, get message info.
266 FND_MSG_PUB.Count_And_Get(
267 p_count => x_msg_count,
268 p_data => x_msg_data );
269
270 EXCEPTION
271 WHEN FND_API.G_EXC_ERROR THEN
272 ROLLBACK TO UPDATE_STATISTICS;
273 x_return_status := FND_API.G_RET_STS_ERROR ;
274
275 FND_MSG_PUB.Count_And_Get(
276 p_count => x_msg_count,
277 p_data => x_msg_data );
278
279 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
280 ROLLBACK TO UPDATE_STATISTICS;
281 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
282
283 FND_MSG_PUB.Count_And_Get(
284 p_count => x_msg_count,
285 p_data => x_msg_data );
286
287 WHEN OTHERS THEN
288 ROLLBACK TO UPDATE_STATISTICS;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
290
291 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
292 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
293 END IF;
294
295 FND_MSG_PUB.Count_And_Get(
296 p_count => x_msg_count,
297 p_data => x_msg_data );
298
299 END UPDATE_STATISTICS;
300
301 PROCEDURE DELETE_STATISTICS(
302 p_api_version IN NUMBER,
303 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
304 p_commit IN VARCHAR2 := FND_API.G_FALSE,
305
306 p_policy_id IN NUMBER,
307 p_wsh_po_id IN NUMBER,
308
309 p_object_version_number IN NUMBER,
310
311 x_return_status OUT NOCOPY VARCHAR2,
312 x_msg_count OUT NOCOPY NUMBER,
313 x_msg_data OUT NOCOPY VARCHAR2
314 ) AS
315 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_STATISTICS';
316 l_api_version CONSTANT NUMBER := p_api_version;
317
318 l_object_version NUMBER := NULL;
319 BEGIN
320 -- Standard Start of API savepoint
321 SAVEPOINT DELETE_STATISTICS;
322
323 -- Standard call to check for call compatibility.
324 IF NOT FND_API.Compatible_API_Call (
325 l_api_version,
326 p_api_version,
327 l_api_name,
328 G_PKG_NAME)
329 THEN
330 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
331 END IF;
332
333 -- Initialize message list if p_init_msg_list is set to TRUE.
334 IF FND_API.To_Boolean( p_init_msg_list ) THEN
335 FND_MSG_PUB.initialize;
336 END IF;
337
338 -- Initialize API return status to success
339 x_return_status := FND_API.G_RET_STS_SUCCESS;
340
341 -- real logic --
342 ----------------
343 /*
344 SELECT object_version_number INTO l_object_version
345 FROM jtf_prefab_statistics
346 WHERE application_id = p_application_id;
347
348 -- checking for object version number
349 IF (l_object_version IS NULL OR l_object_version > p_obj_ver_number) THEN
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352 */
353 DELETE FROM jtf_prefab_statistics
354 WHERE policy_id = p_policy_id AND wsh_po_id = p_wsh_po_id;
355
356 -----------------------
357 -- end of real logic --
358
359 -- Standard check of p_commit.
360 IF (FND_API.To_Boolean(p_commit)) THEN
361 COMMIT WORK;
362 END IF;
363
364 -- Standard call to get message count and if count is 1, get message info.
365 FND_MSG_PUB.Count_And_Get(
366 p_count => x_msg_count,
367 p_data => x_msg_data );
368
369 EXCEPTION
370 WHEN FND_API.G_EXC_ERROR THEN
371 ROLLBACK TO DELETE_STATISTICS;
372 x_return_status := FND_API.G_RET_STS_ERROR ;
373
374 FND_MSG_PUB.Count_And_Get(
375 p_count => x_msg_count,
376 p_data => x_msg_data );
377
378 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
379 ROLLBACK TO DELETE_STATISTICS;
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
381
382 FND_MSG_PUB.Count_And_Get(
383 p_count => x_msg_count,
384 p_data => x_msg_data );
385
386 WHEN OTHERS THEN
387 ROLLBACK TO DELETE_STATISTICS;
388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
389
390 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
391 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
392 END IF;
393
394 FND_MSG_PUB.Count_And_Get(
395 p_count => x_msg_count,
396 p_data => x_msg_data );
397
398 END DELETE_STATISTICS;
399
400 PROCEDURE SELECT_STATISTICS(
401 p_api_version IN NUMBER,
402 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
403 p_commit IN VARCHAR2 := FND_API.G_FALSE,
404
405 p_policy_id IN jtf_prefab_statistics.policy_id%TYPE,
406 p_wsh_po_id IN jtf_prefab_statistics.wsh_po_id%TYPE,
407 p_start_time OUT NOCOPY jtf_prefab_statistics.start_time%TYPE,
408 p_end_time OUT NOCOPY jtf_prefab_statistics.end_time%TYPE,
409 p_last_update_time OUT NOCOPY jtf_prefab_statistics.last_update_time%TYPE,
410 p_pages_last_run OUT NOCOPY jtf_prefab_statistics.pages_last_run%TYPE,
411 p_pages_crawled OUT NOCOPY jtf_prefab_statistics.pages_crawled%TYPE,
412 p_refresh_rate OUT NOCOPY jtf_prefab_statistics.refresh_rate%TYPE,
413 p_system_status OUT NOCOPY jtf_prefab_statistics.system_status%TYPE,
414 p_error_status OUT NOCOPY jtf_prefab_statistics.error_status%TYPE,
415 p_depth OUT NOCOPY jtf_prefab_statistics.depth%TYPE,
416 p_disk_used OUT NOCOPY jtf_prefab_statistics.disk_used%TYPE,
417 p_avg_mem OUT NOCOPY jtf_prefab_statistics.avg_mem%TYPE,
418 p_avg_cpu OUT NOCOPY jtf_prefab_statistics.avg_cpu%TYPE,
419 p_hit_rate OUT NOCOPY jtf_prefab_statistics.hit_rate%TYPE,
420
421 p_object_version_number OUT NOCOPY NUMBER,
422 p_row_count OUT NOCOPY NUMBER,
423
424 x_return_status OUT NOCOPY VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2
427 ) AS
428 -- local variables --
429 l_api_name CONSTANT VARCHAR2(30) := 'SELECT_STATISTICS';
430 l_api_version NUMBER := p_api_version;
431 BEGIN
432 -- Standard Start of API savepoint
433 SAVEPOINT SELECT_STATISTICS;
434
435 -- Standard call to check for call compatibility.
436 IF NOT FND_API.Compatible_API_Call (
437 l_api_version,
438 p_api_version,
439 l_api_name,
440 G_PKG_NAME)
441 THEN
442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
443 END IF;
444
445 -- Initialize message list if p_init_msg_list is set to TRUE.
446 IF FND_API.To_Boolean( p_init_msg_list ) THEN
447 FND_MSG_PUB.initialize;
448 END IF;
449
450 -- Initialize API return status to success
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452
453 -- real logic --
454 ----------------
455
456 SELECT start_time, end_time, last_update_time,
457 pages_last_run, pages_crawled, refresh_rate,
458 system_status, error_status, depth, disk_used,
459 avg_mem, avg_cpu, hit_rate,
460 object_version_number
461 INTO p_start_time, p_end_time, p_last_update_time,
462 p_pages_last_run, p_pages_crawled, p_refresh_rate,
463 p_system_status, p_error_status, p_depth, p_disk_used,
464 p_avg_mem, p_avg_cpu, p_hit_rate,
465 p_object_version_number
466 FROM jtf_prefab_statistics
467 WHERE policy_id = p_policy_id AND wsh_po_id = p_wsh_po_id;
468
469 p_row_count := SQL%ROWCOUNT;
470
471 -----------------------
472 -- end of real logic --
473
474 -- Standard check of p_commit.
475 IF (FND_API.To_Boolean(p_commit)) THEN
476 COMMIT WORK;
477 END IF;
478
479 -- Standard call to get message count and if count is 1, get message info.
480 FND_MSG_PUB.Count_And_Get(
481 p_count => x_msg_count,
482 p_data => x_msg_data );
483
484 EXCEPTION
485 WHEN FND_API.G_EXC_ERROR THEN
486 ROLLBACK TO SELECT_STATISTICS;
487 x_return_status := FND_API.G_RET_STS_ERROR ;
488
489 FND_MSG_PUB.Count_And_Get(
490 p_count => x_msg_count,
491 p_data => x_msg_data );
492
493 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
494 ROLLBACK TO SELECT_STATISTICS;
495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
496
497 FND_MSG_PUB.Count_And_Get(
498 p_count => x_msg_count,
499 p_data => x_msg_data );
500
501 WHEN OTHERS THEN
502 ROLLBACK TO SELECT_STATISTICS;
503 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
504
505 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
506 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
507 END IF;
508
509 FND_MSG_PUB.Count_And_Get(
510 p_count => x_msg_count,
511 p_data => x_msg_data );
512
513 END SELECT_STATISTICS;
514
515 END JTF_PREFAB_STATISTICS_PUB;