[Home] [Help]
PACKAGE BODY: APPS.QA_DEVICE_PUB
Source
1 PACKAGE BODY qa_device_pub AS
2 /* $Header: qadvpubb.pls 120.5.12010000.1 2008/07/25 09:19:18 appldev 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 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
317 UPDATE qa_device_info
318 SET enabled_flag = 1,
319 description = p_device_desc(i),
320 elapsed_time = p_expiration(i),
321 last_updated_by = l_user_id,
322 last_update_login = l_user_id,
323 last_update_date = sysdate
324 WHERE device_name = p_device_name(i)
325 AND sensor_alias = p_device_source;
326
327 FOR cntr IN p_device_name.FIRST..p_device_name.LAST LOOP
328 IF SQL%BULK_ROWCOUNT(cntr) = 0 THEN
329 -- Insert device if it does not exist.
330 INSERT INTO qa_device_info(
331 device_id,
332 device_name,
333 description,
334 sensor_alias,
335 elapsed_time,
336 override_flag,
337 enabled_flag,
338 created_by,
339 creation_date,
340 last_update_login,
341 last_update_date,
342 last_updated_by)
343 VALUES(
344 qa_device_info_s.nextval,
345 p_device_name(cntr),
346 p_device_desc(cntr),
347 p_device_source,
348 p_expiration(cntr),
349 2,
350 1,
351 l_user_id,
352 sysdate,
353 l_user_id,
354 sysdate,
355 l_user_id);
356 END IF;
357
358 SELECT COUNT(device_name)
359 INTO exists_count
360 FROM qa_device_data_values
361 WHERE device_name = p_device_name(cntr)
362 AND device_source = p_device_source;
363
364 IF exists_count = 0 THEN
365 --Insert a new row for the device in QA_DEVICE_DATA_VALUES table if it is not already present.
366 INSERT INTO qa_device_data_values(
367 device_name,
368 device_source,
369 event_data,
370 event_time,
371 event_generation_time,
372 quality_code,
373 created_by,
374 creation_date,
375 last_update_login,
376 last_update_date,
377 last_updated_by)
378 VALUES(
379 p_device_name(cntr),
380 p_device_source,
381 '-1',
382 systimestamp,
383 sysdate,
384 -1,
385 l_user_id,
386 sysdate,
387 l_user_id,
388 sysdate,
389 l_user_id);
390 END IF;
391 END LOOP;
392
393
394 IF fnd_api.to_boolean(p_commit) THEN
395 COMMIT;
396 END IF;
397
398 EXCEPTION
399
400 WHEN fnd_api.g_exc_error THEN
401 x_return_status := fnd_api.g_ret_sts_error;
402 fnd_msg_pub.count_and_get(
403 p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406
407 WHEN fnd_api.g_exc_unexpected_error THEN
408 x_return_status := fnd_api.g_ret_sts_unexp_error;
409 fnd_msg_pub.count_and_get(
410 p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413
414 END add_device_info_bulk;
415
416
417 PROCEDURE delete_device_info_bulk(
418 p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2,
420 p_validation_level IN NUMBER,
421 p_user_name IN VARCHAR2,
422 p_device_source IN VARCHAR2,
423 p_device_name IN VARCHAR2_TABLE,
424 p_commit IN VARCHAR2,
425 x_msg_count OUT NOCOPY NUMBER,
426 x_msg_data OUT NOCOPY VARCHAR2,
427 x_return_status OUT NOCOPY VARCHAR2) IS
428
429 l_api_version NUMBER := 1.0;
430 l_user_id NUMBER;
431 l_api_name CONSTANT VARCHAR2(30) := 'delete_device_info_bulk';
432
433 l_init_msg_list VARCHAR2(10);
434 l_validation_level NUMBER;
435 l_commit VARCHAR2(10);
436
437 BEGIN
438
439 -- Standard call to check for call compatibility.
440 IF NOT fnd_api.compatible_api_call(
441 l_api_version, p_api_version, l_api_name, g_pkg_name) 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 l_user_id := get_user_id(p_user_name);
454 IF l_user_id = -1 THEN
455 fnd_message.set_name('QA', 'QA_API_INVALID_USER');
456 fnd_msg_pub.add();
457 raise fnd_api.g_exc_error;
458 END IF;
459
460 --Disable all required devices
461 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
462 UPDATE qa_device_info
463 SET enabled_flag = 2,
464 last_updated_by = l_user_id,
465 last_update_login = l_user_id,
466 last_update_date = sysdate
467 WHERE device_name = p_device_name(i)
468 AND sensor_alias = p_device_source;
469
470 --Remove corresponding row from qa_device_data_values
471 FORALL i IN p_device_name.FIRST..p_device_name.LAST SAVE EXCEPTIONS
472 DELETE FROM qa_device_data_values
473 WHERE device_name = p_device_name(i)
474 AND device_source = p_device_source;
475
476 IF fnd_api.to_boolean(p_commit) THEN
477 COMMIT;
478 END IF;
479
480 EXCEPTION
481
482 WHEN fnd_api.g_exc_error THEN
483 x_return_status := fnd_api.g_ret_sts_error;
484 fnd_msg_pub.count_and_get(
485 p_count => x_msg_count,
486 p_data => x_msg_data
487 );
488
489 WHEN fnd_api.g_exc_unexpected_error THEN
490 x_return_status := fnd_api.g_ret_sts_unexp_error;
491 fnd_msg_pub.count_and_get(
492 p_count => x_msg_count,
493 p_data => x_msg_data
494 );
495
496 END delete_device_info_bulk;
497
498
499 END qa_device_pub;