[Home] [Help]
PACKAGE BODY: APPS.IEO_MEDIA_PUB
Source
1 PACKAGE BODY IEO_MEDIA_PUB AS
2 /* $Header: ieopmedb.pls 115.2 2004/03/19 20:20:50 svinamda noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IEO_MEDIA_PUB';
5
6
7
8 PROCEDURE UPDATE_DEVICE_MAP
9 (
10 p_api_version IN NUMBER,
11 p_init_msg_list IN VARCHAR2 ,
12 p_commit IN VARCHAR2,
13 p_server_group_id IN NUMBER,
14 p_media_type IN VARCHAR2,
15 p_device_type IN VARCHAR2,
16 p_device_id IN VARCHAR2,
17 p_is_device_available IN VARCHAR2,
18 p_server_id IN NUMBER,
19 x_return_status OUT NOCOPY VARCHAR2 ,
20 x_msg_count OUT NOCOPY NUMBER ,
21 x_msg_data OUT NOCOPY VARCHAR2
22 )
23 IS
24 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DEVICE_MAP';
25 l_api_version CONSTANT NUMBER := 1.0;
26 x_err_num NUMBER;
27 x_err_msg VARCHAR2(256);
28
29 BEGIN
30 IF NOT FND_API.Compatible_API_Call (l_api_version ,
31 p_api_version ,
32 l_api_name ,
33 G_PKG_NAME )
34 THEN
35 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
36 END IF;
37 -- Initialize message list if p_init_msg_list is set to TRUE.
38 IF FND_API.to_Boolean( p_init_msg_list ) THEN
39 FND_MSG_PUB.initialize;
40 END IF;
41 -- Initialize API return status to failure
42
43 x_return_status := FND_API.G_RET_STS_SUCCESS ;
44 x_msg_count := 0;
45 x_msg_data := null;
46
47 -- API body
48 --dbms_output.put_line('UPDATE_DEVICE_MAP: pre update data');
49 update ieo_media_rt_device_map
50 set server_id = p_server_id, device_available = p_is_device_available
51 where server_group_id = p_server_group_id and
52 media_type = p_media_type and
53 device_type = p_device_type and
54 device_id = p_device_id;
55
56 if sql%notfound then raise NO_DATA_FOUND;
57 end if;
58
59 IF FND_API.To_Boolean( p_commit ) THEN
60 COMMIT WORK;
61 END IF;
62
63 EXCEPTION
64 WHEN NO_DATA_FOUND then
65 -- insert row
66 --dbms_output.put_line('UPDATE_DEVICE_MAP:no data found');
67 insert into ieo_media_rt_device_map
68 (
69 server_group_id, media_type,
70 device_type, device_id,
71 device_available, server_id,
72 device_map_id,
73 created_by, creation_date,
74 last_updated_by, last_update_date, last_update_login
75 )
76 values
77 (
78 p_server_group_id, p_media_type,
79 p_device_type, p_device_id,
80 p_is_device_available, p_server_id,
81 ieo_media_rt_device_map_s1.nextval,
82 1, sysdate, 1, sysdate, 1
83 );
84
85 IF FND_API.To_Boolean( p_commit ) THEN
86 COMMIT WORK;
87 END IF;
88
89 WHEN FND_API.G_EXC_ERROR THEN
90 rollback;
91 x_return_status := FND_API.G_RET_STS_ERROR ;
92 x_msg_count := 1;
93 x_err_num := SQLCODE;
94 x_err_msg := SUBSTR(SQLERRM, 1, 100);
95 x_msg_data := 'UPDATE_DEVICE_MAP: IEO_EXC_ERROR'
96 || ' ErrorCode = ' || x_err_num
97 || ' ErrorMsg = ' || x_err_msg;
98 --dbms_output.put_line(x_msg_data);
99
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 --dbms_output.put_line('Unexpected error');
102 ROLLBACK;
103 x_return_status := FND_API.G_RET_STS_ERROR ;
104 x_msg_count := 1;
105 x_err_num := SQLCODE;
106 x_err_msg := SUBSTR(SQLERRM, 1, 100);
107 x_msg_data := 'UPDATE_DEVICE_MAP: IEO_UNEXPECTED_ERROR'
108 || ' ErrorCode = ' || x_err_num
109 || ' ErrorMsg = ' || x_err_msg;
110 --dbms_output.put_line(x_msg_data);
111 WHEN OTHERS THEN
112 rollback;
113 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
114 x_msg_count := 1;
115 x_err_num := SQLCODE;
116 x_err_msg := SUBSTR(SQLERRM, 1, 100);
117 x_msg_data := 'UPDATE_DEVICE_MAP: IEO_OTHERS_ERROR'
118 || ' ErrorCode = ' || x_err_num
119 || ' ErrorMsg = ' || x_err_msg;
120 --dbms_output.put_line(x_msg_data);
121 END UPDATE_DEVICE_MAP;
122
123
124
125 PROCEDURE GET_DEVICE_LOCATION
126 (
127 p_api_version IN NUMBER,
128 p_init_msg_list IN VARCHAR2,
129 p_commit IN VARCHAR2,
130 p_server_group_name IN VARCHAR2,
131 p_media_type IN VARCHAR2,
132 p_device_type IN VARCHAR2,
133 p_device_id IN VARCHAR2,
134 x_return_status OUT NOCOPY VARCHAR2,
135 x_msg_count OUT NOCOPY NUMBER,
136 x_msg_data OUT NOCOPY VARCHAR2,
137 x_server_id OUT NOCOPY VARCHAR2,
138 x_device_map_id OUT NOCOPY NUMBER
139 )
140 IS
141 l_api_name CONSTANT VARCHAR2(30) := 'GET_DEVICE_LOCATION';
142 l_api_version CONSTANT NUMBER := 1.0;
143 x_err_num NUMBER;
144 x_err_msg VARCHAR2(256);
145 l_server_group_id NUMBER;
146
147 BEGIN
148 IF NOT FND_API.Compatible_API_Call (l_api_version ,
149 p_api_version ,
150 l_api_name ,
151 G_PKG_NAME )
152 THEN
153 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
154 END IF;
155 -- Initialize message list if p_init_msg_list is set to TRUE.
156 IF FND_API.to_Boolean( p_init_msg_list ) THEN
157 FND_MSG_PUB.initialize;
158 END IF;
159 -- Initialize API return status to success
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 x_server_id := null;
162 x_device_map_id := -1;
163 -- API body
164 --dbms_output.put_line('GET_DEVICE_LOCATION: server_group_name = ' || p_server_group_name
165 --|| ' media_type = ' || p_media_type
166 --|| ' device_type = ' || p_device_type
167 --|| ' device_id = '|| p_device_id );
168
169 x_msg_data := 'GET_DEVICE_LOCATION: server_group '
170 || p_server_group_name || ' not found';
171 x_msg_count := 1;
172
173 select server_group_id into l_server_group_id
174 from ieo_svr_groups where group_name = p_server_group_name;
175
176 x_msg_data := 'GET_DEVICE_LOCATION: device not found '
177 || ' media_type = ' || p_media_type
178 || ' device_type = ' || p_device_type
179 || ' device_id = '|| p_device_id;
180 x_msg_count := 1;
181
182 select server_id, device_map_id into x_server_id, x_device_map_id
183 from ieo_media_rt_device_map
184 where server_group_id = l_server_group_id and
185 media_type = p_media_type and
186 device_type = p_device_type and
187 device_id = p_device_id;
188
189 x_msg_data := null;
190 x_msg_count :=0;
191
192 IF FND_API.To_Boolean( p_commit ) THEN
193 COMMIT WORK;
194 END IF;
195
196
197 EXCEPTION
198 WHEN NO_DATA_FOUND THEN
199 IF FND_API.To_Boolean( p_commit ) THEN
200 COMMIT WORK;
201 END IF;
202
203 WHEN FND_API.G_EXC_ERROR THEN
204 rollback;
205 x_return_status := FND_API.G_RET_STS_ERROR ;
206 x_msg_count := 1;
207 x_err_num := SQLCODE;
208 x_err_msg := SUBSTR(SQLERRM, 1, 100);
209 x_msg_data :='UPDATE_ROUTE_RESULT: IEO_EXC_ERROR'
210 || ' ErrorCode = ' || x_err_num
211 || ' ErrorMsg = ' || x_err_msg;
212 --dbms_output.put_line(x_msg_data);
213 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
214 ROLLBACK;
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
216 x_msg_count := 1;
217 x_err_num := SQLCODE;
218 x_err_msg := SUBSTR(SQLERRM, 1, 100);
219 x_msg_data :='UPDATE_ROUTE_RESULT: IEO_UNEXPECTED_ERROR'
220 || ' ErrorCode = ' || x_err_num
221 || ' ErrorMsg = ' || x_err_msg;
222 --dbms_output.put_line(x_msg_data);
223 WHEN OTHERS THEN
224 rollback;
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
226 x_msg_count := 1;
227 x_err_num := SQLCODE;
228 x_err_msg := SUBSTR(SQLERRM, 1, 100);
229 x_msg_data :='UPDATE_ROUTE_RESULT: IEO_OTHERS_ERROR'
230 || ' ErrorCode = ' || x_err_num
231 || ' ErrorMsg = ' || x_err_msg;
232 --dbms_output.put_line(x_msg_data);
233
234 END GET_DEVICE_LOCATION;
235
236
237 PROCEDURE LOCATE_LEAST_LOADED_IN_GROUP
238 (
239 p_api_version IN NUMBER,
240 p_init_msg_list IN VARCHAR2,
241 p_commit IN VARCHAR2,
242 p_server_group_name IN VARCHAR2,
243 p_server_type_uuid IN VARCHAR2,
244 x_return_status OUT NOCOPY VARCHAR2,
245 x_msg_count OUT NOCOPY NUMBER,
246 x_msg_data OUT NOCOPY VARCHAR2,
247 x_server_id OUT NOCOPY VARCHAR2
248 )
249 IS
250 l_api_name CONSTANT VARCHAR2(30) := 'LOCATE_LEAST_LOADED_IN_GROUP';
251 l_api_version CONSTANT NUMBER := 1.0;
252 l_server_group_id NUMBER;
253 x_err_num NUMBER;
254 x_err_msg VARCHAR2(256);
255
256 BEGIN
257 IF NOT FND_API.Compatible_API_Call (l_api_version ,
258 p_api_version ,
259 l_api_name ,
260 G_PKG_NAME )
261 THEN
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264 -- Initialize message list if p_init_msg_list is set to TRUE.
265 IF FND_API.to_Boolean( p_init_msg_list ) THEN
266 FND_MSG_PUB.initialize;
267 END IF;
268
269 x_return_status := FND_API.G_RET_STS_SUCCESS ;
270 x_msg_data := 'LOCATE_LEAST_LOADED_IN_GROUP: server_group '
271 || p_server_group_name || ' not found';
272 x_msg_count := 1;
273 x_server_id := null;
274
275 select server_group_id into l_server_group_id
276 from ieo_svr_groups where group_name = p_server_group_name;
277
278 x_msg_data := 'IEO_SVR_UTIL_PVT.LOCATE_LEAST_LOADED_IN_GROUP failed';
279
280 IEO_SVR_UTIL_PVT.LOCATE_LEAST_LOADED_IN_GROUP
281 (
282 P_GROUP_ID => l_server_group_id,
283 P_SERVER_TYPE_UUID => p_server_type_uuid,
284 P_EXCLUDE_SERVER_ID => null,
285 X_SERVER_ID => x_server_id,
286 P_TIMEOUT_TOLERANCE => 0
287 );
288
289 x_msg_count :=0;
290 x_msg_data := null;
291
292 IF FND_API.To_Boolean( p_commit ) THEN
293 COMMIT WORK;
294 END IF;
295 EXCEPTION
296 -- WHEN AGENT_NOT_FOUND THEN
297 WHEN NO_DATA_FOUND THEN
298 -- return failure - agent not logged on.
299 rollback;
300 x_err_num := SQLCODE;
301 x_err_msg := SUBSTR(SQLERRM, 1, 100);
302 x_msg_data :='LOCATE_LEAST_LOADED_IN_GROUP:'
303 || ' ErrorCode = ' || x_err_num
304 || ' ErrorMsg = ' || x_err_msg;
305 --dbms_output.put_line(x_msg_data);
306
307 WHEN FND_API.G_EXC_ERROR THEN
308 rollback;
309 x_return_status := FND_API.G_RET_STS_ERROR ;
310 x_msg_count := 1;
311 x_err_num := SQLCODE;
312 x_err_msg := SUBSTR(SQLERRM, 1, 100);
313 x_msg_data :='LOCATE_LEAST_LOADED_IN_GROUP: IEO_EXC_ERROR'
314 || ' ErrorCode = ' || x_err_num
315 || ' ErrorMsg = ' || x_err_msg;
316 --dbms_output.put_line(x_msg_data);
317 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
318 rollback;
319 x_return_status := FND_API.G_RET_STS_ERROR ;
320 x_msg_count := 1;
321 x_err_num := SQLCODE;
322 x_err_msg := SUBSTR(SQLERRM, 1, 100);
323 x_msg_data :='LOCATE_LEAST_LOADED_IN_GROUP: IEO_UNEXPECTED_ERROR'
324 || ' ErrorCode = ' || x_err_num
325 || ' ErrorMsg = ' || x_err_msg;
326 --dbms_output.put_line(x_msg_data);
327 WHEN OTHERS THEN
328 rollback;
332 x_err_msg := SUBSTR(SQLERRM, 1, 100);
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
330 x_msg_count := 1;
331 x_err_num := SQLCODE;
333 x_msg_data :='LOCATE_LEAST_LOADED_IN_GROUP: IEO_OTHERS_ERROR'
334 || ' ErrorCode = ' || x_err_num
335 || ' ErrorMsg = ' || x_err_msg;
336 --dbms_output.put_line(x_msg_data);
337 END LOCATE_LEAST_LOADED_IN_GROUP;
338
339
340
341
342
343 PROCEDURE LOCATE_BY_MINOR_LOAD
344 (
345 p_api_version IN NUMBER,
346 p_init_msg_list IN VARCHAR2,
347 p_commit IN VARCHAR2,
348 p_server_group_name IN VARCHAR2,
349 p_server_type_uuid IN VARCHAR2,
350 x_return_status OUT NOCOPY VARCHAR2,
351 x_msg_count OUT NOCOPY NUMBER,
352 x_msg_data OUT NOCOPY VARCHAR2,
353 x_server_id OUT NOCOPY VARCHAR2
354 )
355 IS
356 l_api_name CONSTANT VARCHAR2(30) := 'LOCATE_LEAST_LOADED_IN_GROUP';
357 l_api_version CONSTANT NUMBER := 1.0;
358 l_server_group_id NUMBER;
359 x_err_num NUMBER;
360 x_err_msg VARCHAR2(256);
361
362 BEGIN
363 IF NOT FND_API.Compatible_API_Call (l_api_version ,
364 p_api_version ,
365 l_api_name ,
366 G_PKG_NAME )
367 THEN
368 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
369 END IF;
370 -- Initialize message list if p_init_msg_list is set to TRUE.
371 IF FND_API.to_Boolean( p_init_msg_list ) THEN
372 FND_MSG_PUB.initialize;
373 END IF;
374
375 x_return_status := FND_API.G_RET_STS_SUCCESS ;
376 x_msg_data := 'LOCATE_BY_MINOR_LOAD: server_group '
377 || p_server_group_name || ' not found';
378 x_msg_count := 1;
379 x_server_id := null;
380
381 select server_group_id into l_server_group_id
382 from ieo_svr_groups where group_name = p_server_group_name;
383
384 x_msg_data := 'IEO_SVR_UTIL_PVT.LOCATE_BY_MINOR_LOAD failed';
385
386 IEO_SVR_UTIL_PVT.LOCATE_BY_MINOR_LOAD
387 (
388 P_GROUP_ID => l_server_group_id,
389 P_SERVER_TYPE_UUID => p_server_type_uuid,
390 P_EXCLUDE_SERVER_ID => null,
391 X_SERVER_ID => x_server_id,
392 P_TIMEOUT_TOLERANCE => 0
393 );
394
395 x_msg_count :=0;
396 x_msg_data := null;
397
398 IF FND_API.To_Boolean( p_commit ) THEN
399 COMMIT WORK;
400 END IF;
401 EXCEPTION
402 -- WHEN AGENT_NOT_FOUND THEN
403 WHEN NO_DATA_FOUND THEN
404 -- return failure - agent not logged on.
405 rollback;
406 x_err_num := SQLCODE;
407 x_err_msg := SUBSTR(SQLERRM, 1, 100);
408 x_msg_data :='LOCATE_BY_MINOR_LOAD:'
409 || ' ErrorCode = ' || x_err_num
410 || ' ErrorMsg = ' || x_err_msg;
411 --dbms_output.put_line(x_msg_data);
412
413 WHEN FND_API.G_EXC_ERROR THEN
414 rollback;
415 x_return_status := FND_API.G_RET_STS_ERROR ;
416 x_msg_count := 1;
417 x_err_num := SQLCODE;
418 x_err_msg := SUBSTR(SQLERRM, 1, 100);
419 x_msg_data :='LOCATE_BY_MINOR_LOAD: IEO_EXC_ERROR'
420 || ' ErrorCode = ' || x_err_num
421 || ' ErrorMsg = ' || x_err_msg;
422 --dbms_output.put_line(x_msg_data);
423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
424 rollback;
425 x_return_status := FND_API.G_RET_STS_ERROR ;
426 x_msg_count := 1;
427 x_err_num := SQLCODE;
428 x_err_msg := SUBSTR(SQLERRM, 1, 100);
429 x_msg_data :='LOCATE_BY_MINOR_LOAD: IEO_UNEXPECTED_ERROR'
430 || ' ErrorCode = ' || x_err_num
431 || ' ErrorMsg = ' || x_err_msg;
432 --dbms_output.put_line(x_msg_data);
433 WHEN OTHERS THEN
434 rollback;
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
436 x_msg_count := 1;
437 x_err_num := SQLCODE;
438 x_err_msg := SUBSTR(SQLERRM, 1, 100);
439 x_msg_data :='LOCATE_BY_MINOR_LOAD: IEO_OTHERS_ERROR'
440 || ' ErrorCode = ' || x_err_num
441 || ' ErrorMsg = ' || x_err_msg;
442 --dbms_output.put_line(x_msg_data);
443 END LOCATE_BY_MINOR_LOAD;
444
445
446
447 PROCEDURE LOCATE_BY_MAJOR_LOAD
448 (
449 p_api_version IN NUMBER,
450 p_init_msg_list IN VARCHAR2,
451 p_commit IN VARCHAR2,
452 p_server_group_name IN VARCHAR2,
453 p_server_type_uuid IN VARCHAR2,
454 x_return_status OUT NOCOPY VARCHAR2,
455 x_msg_count OUT NOCOPY NUMBER,
456 x_msg_data OUT NOCOPY VARCHAR2,
457 x_server_id OUT NOCOPY VARCHAR2
458 )
459 IS
460 l_api_name CONSTANT VARCHAR2(30) := 'LOCATE_LEAST_LOADED_IN_GROUP';
461 l_api_version CONSTANT NUMBER := 1.0;
462 l_server_group_id NUMBER;
463 x_err_num NUMBER;
464 x_err_msg VARCHAR2(256);
465
466 BEGIN
467 IF NOT FND_API.Compatible_API_Call (l_api_version ,
468 p_api_version ,
469 l_api_name ,
470 G_PKG_NAME )
471 THEN
472 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
473 END IF;
474 -- Initialize message list if p_init_msg_list is set to TRUE.
475 IF FND_API.to_Boolean( p_init_msg_list ) THEN
476 FND_MSG_PUB.initialize;
477 END IF;
478
479 x_return_status := FND_API.G_RET_STS_SUCCESS ;
483 x_server_id := null;
480 x_msg_data := 'LOCATE_BY_MAJOR_LOAD: server_group '
481 || p_server_group_name || ' not found';
482 x_msg_count := 1;
484
485 select server_group_id into l_server_group_id
486 from ieo_svr_groups where group_name = p_server_group_name;
487
488 x_msg_data := 'IEO_SVR_UTIL_PVT.LOCATE_BY_MAJOR_LOAD failed';
489
490 IEO_SVR_UTIL_PVT.LOCATE_BY_MAJOR_LOAD
491 (
492 P_GROUP_ID => l_server_group_id,
493 P_SERVER_TYPE_UUID => p_server_type_uuid,
494 P_EXCLUDE_SERVER_ID => null,
495 X_SERVER_ID => x_server_id,
496 P_TIMEOUT_TOLERANCE => 0
497 );
498
499 x_msg_count :=0;
500 x_msg_data := null;
501
502 IF FND_API.To_Boolean( p_commit ) THEN
503 COMMIT WORK;
504 END IF;
505 EXCEPTION
506 -- WHEN AGENT_NOT_FOUND THEN
507 WHEN NO_DATA_FOUND THEN
508 -- return failure - agent not logged on.
509 rollback;
510 x_err_num := SQLCODE;
511 x_err_msg := SUBSTR(SQLERRM, 1, 100);
512 x_msg_data :='LOCATE_BY_MAJOR_LOAD:'
513 || ' ErrorCode = ' || x_err_num
514 || ' ErrorMsg = ' || x_err_msg;
515 --dbms_output.put_line(x_msg_data);
516
517 WHEN FND_API.G_EXC_ERROR THEN
518 rollback;
519 x_return_status := FND_API.G_RET_STS_ERROR ;
520 x_msg_count := 1;
521 x_err_num := SQLCODE;
522 x_err_msg := SUBSTR(SQLERRM, 1, 100);
523 x_msg_data :='LOCATE_BY_MAJOR_LOAD: IEO_EXC_ERROR'
524 || ' ErrorCode = ' || x_err_num
525 || ' ErrorMsg = ' || x_err_msg;
526 --dbms_output.put_line(x_msg_data);
527 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
528 rollback;
529 x_return_status := FND_API.G_RET_STS_ERROR ;
530 x_msg_count := 1;
531 x_err_num := SQLCODE;
532 x_err_msg := SUBSTR(SQLERRM, 1, 100);
533 x_msg_data :='LOCATE_BY_MAJOR_LOAD: IEO_UNEXPECTED_ERROR'
534 || ' ErrorCode = ' || x_err_num
535 || ' ErrorMsg = ' || x_err_msg;
536 --dbms_output.put_line(x_msg_data);
537 WHEN OTHERS THEN
538 rollback;
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
540 x_msg_count := 1;
541 x_err_num := SQLCODE;
542 x_err_msg := SUBSTR(SQLERRM, 1, 100);
543 x_msg_data :='LOCATE_BY_MAJOR_LOAD: IEO_OTHERS_ERROR'
544 || ' ErrorCode = ' || x_err_num
545 || ' ErrorMsg = ' || x_err_msg;
546 --dbms_output.put_line(x_msg_data);
547 END LOCATE_BY_MAJOR_LOAD;
548
549
550
551
552 END IEO_MEDIA_PUB;