[Home] [Help]
PACKAGE BODY: APPS.QA_DEVICE_PUB
Source
1 PACKAGE BODY qa_device_pub AS
2 /* $Header: qadvpubb.pls 120.5.12010000.2 2009/04/30 07:50:24 skolluku ship $ */
3
4 --
5 -- Safe Globals
6 --
7 g_user_name_cache fnd_user.user_name%TYPE := NULL;
8 g_user_id_cache NUMBER;
9 g_pkg_name CONSTANT VARCHAR2(30):= 'qa_device_pub';
10
11 --
12 -- General utility functions
13 --
14
15 FUNCTION get_user_id(p_name IN VARCHAR2) RETURN NUMBER IS
16 --
17 -- Decode user name from fnd_user table.
18 --
19 id NUMBER;
20
21 CURSOR user_cursor IS
22 SELECT user_id
23 FROM fnd_user
24 WHERE user_name = p_name;
25 BEGIN
26 IF p_name IS NULL THEN
27 RETURN nvl(fnd_global.user_id, -1);
28 END IF;
29
30 --
31 -- It is very common for the same user to call the
32 -- APIs successively.
33 --
34 IF g_user_name_cache = p_name THEN
35 RETURN g_user_id_cache;
36 END IF;
37
38 OPEN user_cursor;
39 FETCH user_cursor INTO id;
40 IF user_cursor%NOTFOUND THEN
41 CLOSE user_cursor;
42 RETURN -1;
43 END IF;
44 CLOSE user_cursor;
45
46 g_user_name_cache := p_name;
47 g_user_id_cache := id;
48
49 RETURN id;
50 END get_user_id;
51
52
53 PROCEDURE set_device_data(
54 p_api_version IN NUMBER,
55 p_init_msg_list IN VARCHAR2,
56 p_validation_level IN NUMBER,
57 p_user_name IN VARCHAR2,
58 p_device_source IN VARCHAR2,
59 p_device_name IN VARCHAR2,
60 p_device_data IN VARCHAR2,
61 p_device_event_time IN DATE,
62 p_quality_code IN NUMBER,
63 p_commit IN VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2,
66 x_return_status OUT NOCOPY VARCHAR2) IS
67
68 l_api_version NUMBER := 1.0;
69 l_user_id NUMBER;
70 l_api_name CONSTANT VARCHAR2(30) := 'set_device_data';
71
72 BEGIN
73
74 -- Standard call to check for call compatibility.
75 IF NOT fnd_api.compatible_api_call(
76 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
77 RAISE fnd_api.g_exc_unexpected_error;
78 END IF;
79
80 -- Initialize message list if p_init_msg_list is set to TRUE.
81 IF fnd_api.to_boolean(p_init_msg_list) THEN
82 fnd_msg_pub.initialize;
83 END IF;
84
85 -- Initialize API return status to success
86 x_return_status := fnd_api.g_ret_sts_success;
87
88 l_user_id := get_user_id(p_user_name);
89 IF l_user_id = -1 THEN
90 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
91 fnd_msg_pub.add();
92 raise fnd_api.g_exc_error;
93 END IF;
94
95 UPDATE qa_device_data_values
96 SET event_data = p_device_data,
97 event_time = systimestamp,
98 event_generation_time = p_device_event_time,
99 quality_code = p_quality_code,
100 last_updated_by = l_user_id,
101 last_update_login = l_user_id,
102 last_update_date = sysdate
103 WHERE device_name = p_device_name
104 AND device_source = p_device_source;
105
106 IF sql%rowcount = 0 THEN
107 fnd_message.set_name('QA', 'QA_DEV_INSERT_FAILED');
108 fnd_message.set_token('DEVNAME',p_device_name);
109 fnd_msg_pub.add();
110 raise fnd_api.g_exc_error;
111 END IF;
112
113 IF fnd_api.to_boolean(p_commit) THEN
114 COMMIT;
115 END IF;
116 EXCEPTION
117
118 WHEN fnd_api.g_exc_error THEN
119 x_return_status := fnd_api.g_ret_sts_error;
120 fnd_msg_pub.count_and_get(
121 p_count => x_msg_count,
122 p_data => x_msg_data
123 );
124
125 WHEN fnd_api.g_exc_unexpected_error THEN
126 x_return_status := fnd_api.g_ret_sts_unexp_error;
127 fnd_msg_pub.count_and_get(
128 p_count => x_msg_count,
129 p_data => x_msg_data
130 );
131
132 WHEN others THEN
133 fnd_message.set_name('QA', 'QA_DEV_INSERT_FAILED');
134 fnd_message.set_token('DEVNAME',p_device_name);
135 fnd_msg_pub.add();
136 x_return_status := fnd_api.g_ret_sts_error;
137 fnd_msg_pub.count_and_get(
138 p_count => x_msg_count,
139 p_data => x_msg_data
140 );
141
142 END set_device_data;
143
144
145
146 PROCEDURE set_device_data_bulk(
147 p_api_version IN NUMBER,
148 p_init_msg_list IN VARCHAR2,
149 p_validation_level IN NUMBER,
150 p_user_name IN VARCHAR2,
151 p_device_source IN VARCHAR2,
152 p_device_name IN VARCHAR2_TABLE,
153 p_device_data IN VARCHAR2_TABLE,
154 p_device_event_time IN DATE_TABLE,
155 p_quality_code IN NUMBER_TABLE,
156 p_commit IN VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159 x_return_status OUT NOCOPY VARCHAR2) IS
160
161 l_api_version NUMBER := 1.0;
162 l_user_id NUMBER;
163 l_api_name CONSTANT VARCHAR2(30) := 'set_device_data_bulk';
164
165 l_err_device_names VARCHAR2(2000) := NULL;
166
167 BEGIN
168
169 -- Standard call to check for call compatibility.
170 IF NOT fnd_api.compatible_api_call(
171 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
172 RAISE fnd_api.g_exc_unexpected_error;
173 END IF;
174
175 -- Initialize message list if p_init_msg_list is set to TRUE.
176 IF fnd_api.to_boolean(p_init_msg_list) THEN
177 fnd_msg_pub.initialize;
178 END IF;
179
180 -- Initialize API return status to success
181 x_return_status := fnd_api.g_ret_sts_success;
182
183 l_user_id := get_user_id(p_user_name);
184 IF l_user_id = -1 THEN
185 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
186 fnd_msg_pub.add();
187 raise fnd_api.g_exc_error;
188 END IF;
189
190 -- Check if data for all devices has been passed.
191 IF p_device_name.COUNT <> p_device_data.COUNT
192 OR p_device_name.COUNT <> p_device_event_time.COUNT
193 OR p_device_name.COUNT <> p_quality_code.COUNT THEN
194 fnd_message.set_name('QA', 'QA_DEV_INCOMPLETE_DATA');
195 fnd_msg_pub.add();
196 raise fnd_api.g_exc_error;
197 END IF;
198
199 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
200 UPDATE qa_device_data_values
201 SET event_data = p_device_data(i),
202 event_time = systimestamp,
203 event_generation_time = p_device_event_time(i),
204 quality_code = p_quality_code(i),
205 last_updated_by = l_user_id,
206 last_update_login = l_user_id,
207 last_update_date = sysdate
208 WHERE device_name = p_device_name(i)
209 AND device_source = p_device_source;
210
211 FOR cntr IN p_device_name.FIRST..p_device_name.LAST
212 LOOP
213 IF sql%bulk_rowcount(cntr) = 0 THEN
214 IF l_err_device_names IS NULL THEN
215 l_err_device_names := p_device_name(cntr);
216 ELSE
217 l_err_device_names := l_err_device_names || ',' || p_device_name(cntr);
218 END IF;
219 END IF;
220 END LOOP;
221
222 IF fnd_api.to_boolean(p_commit) THEN
223 COMMIT;
224 END IF;
225
226 IF l_err_device_names IS NOT NULL THEN
227 fnd_message.set_name('QA', 'QA_DEV_INSERT_FAILED');
228 fnd_message.set_token('DEVNAME',l_err_device_names);
229 fnd_msg_pub.add();
230 raise fnd_api.g_exc_error;
231 END IF;
232 EXCEPTION
233
234 WHEN fnd_api.g_exc_error THEN
235 x_return_status := fnd_api.g_ret_sts_error;
236 fnd_msg_pub.count_and_get(
237 p_count => x_msg_count,
238 p_data => x_msg_data
239 );
240
241 WHEN fnd_api.g_exc_unexpected_error THEN
242 x_return_status := fnd_api.g_ret_sts_unexp_error;
243 fnd_msg_pub.count_and_get(
244 p_count => x_msg_count,
245 p_data => x_msg_data
246 );
247
248 WHEN others THEN
249 FOR err_cntr IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
250 IF l_err_device_names IS NULL THEN
251 l_err_device_names := p_device_name(SQL%BULK_EXCEPTIONS(err_cntr).ERROR_INDEX);
252 ELSE
253 l_err_device_names := l_err_device_names || ',' || p_device_name(SQL%BULK_EXCEPTIONS(err_cntr).ERROR_INDEX);
254 END IF;
255 END LOOP;
256 fnd_message.set_name('QA', 'QA_DEV_INSERT_FAILED');
257 fnd_message.set_token('DEVNAME',l_err_device_names);
258 fnd_msg_pub.add();
259 x_return_status := fnd_api.g_ret_sts_error;
260 fnd_msg_pub.count_and_get(
261 p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264
265 END set_device_data_bulk;
266
267
268 PROCEDURE add_device_info_bulk(
269 p_api_version IN NUMBER,
270 p_init_msg_list IN VARCHAR2,
271 p_validation_level IN NUMBER,
272 p_user_name IN VARCHAR2,
273 p_device_source IN VARCHAR2,
274 p_device_name IN VARCHAR2_TABLE,
275 p_device_desc IN VARCHAR2000_TABLE,
276 p_expiration IN NUMBER_TABLE,
277 p_commit IN VARCHAR2,
278 x_msg_count OUT NOCOPY NUMBER,
279 x_msg_data OUT NOCOPY VARCHAR2,
280 x_return_status OUT NOCOPY VARCHAR2) IS
281
282 l_api_version NUMBER := 1.0;
283 l_user_id NUMBER;
284 l_api_name CONSTANT VARCHAR2(30) := 'add_device_info_bulk';
285 exists_count NUMBER;
286 BEGIN
287
288 -- Standard call to check for call compatibility.
289 IF NOT fnd_api.compatible_api_call(
290 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
291 RAISE fnd_api.g_exc_unexpected_error;
292 END IF;
293
294 -- Initialize message list if p_init_msg_list is set to TRUE.
295 IF fnd_api.to_boolean(p_init_msg_list) THEN
296 fnd_msg_pub.initialize;
297 END IF;
298
299 -- Initialize API return status to success
300 x_return_status := fnd_api.g_ret_sts_success;
301
302 l_user_id := get_user_id(p_user_name);
303 IF l_user_id = -1 THEN
304 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
305 fnd_msg_pub.add();
306 raise fnd_api.g_exc_error;
307 END IF;
308
309 -- Check if data for all devices has been passed.
310 IF p_device_name.COUNT <> p_device_desc.COUNT OR p_device_name.COUNT <> p_expiration.COUNT THEN
311 fnd_message.set_name('QA', 'QA_DEV_INCOMPLETE_DATA');
312 fnd_msg_pub.add();
313 raise fnd_api.g_exc_error;
314 END IF;
315
316 --
317 -- Bug 7661085
318 -- Trimming the length of the elapsed time to 15 characters.
319 -- skolluku
320 --
321 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
322 UPDATE qa_device_info
323 SET enabled_flag = 1,
324 description = p_device_desc(i),
325 elapsed_time = to_number(substr(p_expiration(i), 1, 15)),
326 last_updated_by = l_user_id,
327 last_update_login = l_user_id,
328 last_update_date = sysdate
329 WHERE device_name = p_device_name(i)
330 AND sensor_alias = p_device_source;
331
332 --
333 -- Bug 7661085
334 -- Trimming the length of the elapsed time to 15 characters.
335 -- skolluku
336 --
337 FOR cntr IN p_device_name.FIRST..p_device_name.LAST LOOP
338 IF SQL%BULK_ROWCOUNT(cntr) = 0 THEN
339 -- Insert device if it does not exist.
340 INSERT INTO qa_device_info(
341 device_id,
342 device_name,
343 description,
344 sensor_alias,
345 elapsed_time,
346 override_flag,
347 enabled_flag,
348 created_by,
349 creation_date,
350 last_update_login,
351 last_update_date,
352 last_updated_by)
353 VALUES(
354 qa_device_info_s.nextval,
355 p_device_name(cntr),
356 p_device_desc(cntr),
357 p_device_source,
358 to_number(substr(p_expiration(cntr), 1, 15)),
359 2,
360 1,
361 l_user_id,
362 sysdate,
363 l_user_id,
364 sysdate,
365 l_user_id);
366 END IF;
367
368 SELECT COUNT(device_name)
369 INTO exists_count
370 FROM qa_device_data_values
371 WHERE device_name = p_device_name(cntr)
372 AND device_source = p_device_source;
373
374 IF exists_count = 0 THEN
375 --Insert a new row for the device in QA_DEVICE_DATA_VALUES table if it is not already present.
376 INSERT INTO qa_device_data_values(
377 device_name,
378 device_source,
379 event_data,
380 event_time,
381 event_generation_time,
382 quality_code,
383 created_by,
384 creation_date,
385 last_update_login,
386 last_update_date,
387 last_updated_by)
388 VALUES(
389 p_device_name(cntr),
390 p_device_source,
391 '-1',
392 systimestamp,
393 sysdate,
394 -1,
395 l_user_id,
396 sysdate,
397 l_user_id,
398 sysdate,
399 l_user_id);
400 END IF;
401 END LOOP;
402
403
404 IF fnd_api.to_boolean(p_commit) THEN
405 COMMIT;
406 END IF;
407
408 EXCEPTION
409
410 WHEN fnd_api.g_exc_error THEN
411 x_return_status := fnd_api.g_ret_sts_error;
412 fnd_msg_pub.count_and_get(
413 p_count => x_msg_count,
414 p_data => x_msg_data
415 );
416
417 WHEN fnd_api.g_exc_unexpected_error THEN
418 x_return_status := fnd_api.g_ret_sts_unexp_error;
419 fnd_msg_pub.count_and_get(
420 p_count => x_msg_count,
421 p_data => x_msg_data
422 );
423
424 END add_device_info_bulk;
425
426
427 PROCEDURE delete_device_info_bulk(
428 p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2,
430 p_validation_level IN NUMBER,
431 p_user_name IN VARCHAR2,
432 p_device_source IN VARCHAR2,
433 p_device_name IN VARCHAR2_TABLE,
434 p_commit IN VARCHAR2,
435 x_msg_count OUT NOCOPY NUMBER,
436 x_msg_data OUT NOCOPY VARCHAR2,
437 x_return_status OUT NOCOPY VARCHAR2) IS
438
439 l_api_version NUMBER := 1.0;
440 l_user_id NUMBER;
441 l_api_name CONSTANT VARCHAR2(30) := 'delete_device_info_bulk';
442
443 l_init_msg_list VARCHAR2(10);
444 l_validation_level NUMBER;
445 l_commit VARCHAR2(10);
446
447 BEGIN
448
449 -- Standard call to check for call compatibility.
450 IF NOT fnd_api.compatible_api_call(
451 l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
452 RAISE fnd_api.g_exc_unexpected_error;
453 END IF;
454
455 -- Initialize message list if p_init_msg_list is set to TRUE.
456 IF fnd_api.to_boolean(p_init_msg_list) THEN
457 fnd_msg_pub.initialize;
458 END IF;
459
460 -- Initialize API return status to success
461 x_return_status := fnd_api.g_ret_sts_success;
462
463 l_user_id := get_user_id(p_user_name);
464 IF l_user_id = -1 THEN
465 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
466 fnd_msg_pub.add();
467 raise fnd_api.g_exc_error;
468 END IF;
469
470 --Disable all required devices
471 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
472 UPDATE qa_device_info
473 SET enabled_flag = 2,
474 last_updated_by = l_user_id,
475 last_update_login = l_user_id,
476 last_update_date = sysdate
477 WHERE device_name = p_device_name(i)
478 AND sensor_alias = p_device_source;
479
480 --Remove corresponding row from qa_device_data_values
481 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
482 DELETE FROM qa_device_data_values
483 WHERE device_name = p_device_name(i)
484 AND device_source = p_device_source;
485
486 IF fnd_api.to_boolean(p_commit) THEN
487 COMMIT;
488 END IF;
489
490 EXCEPTION
491
492 WHEN fnd_api.g_exc_error THEN
493 x_return_status := fnd_api.g_ret_sts_error;
494 fnd_msg_pub.count_and_get(
495 p_count => x_msg_count,
496 p_data => x_msg_data
497 );
498
499 WHEN fnd_api.g_exc_unexpected_error THEN
500 x_return_status := fnd_api.g_ret_sts_unexp_error;
501 fnd_msg_pub.count_and_get(
502 p_count => x_msg_count,
503 p_data => x_msg_data
504 );
505
506 END delete_device_info_bulk;
507
508
509 END qa_device_pub;