[Home] [Help]
PACKAGE BODY: APPS.IEU_UWQ_MEDIA_TYPES_PVT
Source
1 PACKAGE BODY IEU_UWQ_MEDIA_TYPES_PVT AS
2 -- $Header: IEUMEDB.pls 120.1 2005/06/28 08:20:01 appldev ship $
3
4
5 -- ===============================================================
6 -- Start of Comments
7 -- Package name
8 -- IEU_UWQ_MEDIA_TYPES_PVT
9 -- Purpose
10 -- To provide easy to use apis for UQW Admin.
11 -- History
12 -- 11-Feb-2002 gpagadal Created.
13 -- NOTE
14 --
15 -- End of Comments
16 -- ===============================================================
17
18
19
20
21 --===================================================================
22 -- NAME
23 -- GET_MEDIA_TYPE_LIST
24 --
25 -- PURPOSE
26 -- Private api to get all media types.
27 --
28 -- NOTES
29 -- 1. UWQ Admin will use this procedure to get all media types
30 --
31 --
32 -- HISTORY
33 -- 11-Feb-2002 GPAGADAL Created
34
35 --===================================================================
36
37 PROCEDURE GET_MEDIA_TYPE_LIST (
38 p_language IN VARCHAR2,
39 p_order_by IN VARCHAR2,
40 p_asc IN VARCHAR2,
41 x_media_type_list OUT NOCOPY SYSTEM.IEU_MEDIA_TYPE_NST,
42 x_return_status OUT NOCOPY VARCHAR2)
43 AS
44 v_cursorID INTEGER;
45
46 v_selectStmt VARCHAR2(32767);
47
48 v_dummy INTEGER;
49
50
51 v_media_type_id IEU_UWQ_MEDIA_TYPES_B.MEDIA_TYPE_ID%type;
52
53 v_tel_reqd_flag IEU_UWQ_MEDIA_TYPES_B.TEL_REQD_FLAG%type;
54
55 v_media_type_name IEU_UWQ_MEDIA_TYPES_TL.MEDIA_TYPE_NAME%type;
56
57 v_media_type_description IEU_UWQ_MEDIA_TYPES_TL.MEDIA_TYPE_DESCRIPTION%type;
58
59 v_cli_plugin_id IEU_UWQ_CLI_MED_PLUGINS.CLI_PLUGIN_ID%type;
60
61 v_cli_plugin_class IEU_UWQ_CLI_MED_PLUGINS.CLI_PLUGIN_CLASS%type;
62
63 v_svr_type_id IEU_UWQ_SVR_MPS_MMAPS.SVR_TYPE_ID%type;
64
65 v_svr_mps_plugin_id IEU_UWQ_SVR_MPS_PLUGINS.SVR_MPS_PLUGIN_ID%type;
66
67 v_svr_plugin_class IEU_UWQ_SVR_MPS_PLUGINS.SVR_PLUGIN_CLASS%type;
68
69 v_media_type_uuid IEU_UWQ_MEDIA_TYPES_B.MEDIA_TYPE_UUID%type;
70
71 v_svr_login_rule_id IEU_UWQ_MEDIA_TYPES_B.SVR_LOGIN_RULE_ID%type;
72
73 v_application_id IEU_UWQ_MEDIA_TYPES_B.APPLICATION_ID%type;
74
75 v_application_name FND_APPLICATION_TL.APPLICATION_NAME%type;
76
77 v_is_server_side VARCHAR2(2);
78
79 v_classfn_query_proc IEU_UWQ_MEDIA_TYPES_B.CLASSIFICATION_QUERY_PROC%type;
80
81
82 BEGIN
83 x_return_status := fnd_api.g_ret_sts_success;
84
85 v_cursorID := DBMS_SQL.OPEN_CURSOR;
86
87
88 v_is_server_side := 'N';
89
90 v_selectStmt := ' select b.MEDIA_TYPE_ID, b.TEL_REQD_FLAG, tl.MEDIA_TYPE_NAME, tl.MEDIA_TYPE_DESCRIPTION, cl.CLI_PLUGIN_ID,' ||
91 ' cl.CLI_PLUGIN_CLASS, svm.SVR_TYPE_ID, svp.SVR_MPS_PLUGIN_ID, svp.SVR_PLUGIN_CLASS, b.MEDIA_TYPE_UUID, ' ||
92 ' b.SVR_LOGIN_RULE_ID, b.APPLICATION_ID,b.CLASSIFICATION_QUERY_PROC from IEU_UWQ_MEDIA_TYPES_B b, IEU_UWQ_MEDIA_TYPES_TL tl, IEU_UWQ_CLI_MED_PLUGINS cl, ' ||
93 ' IEU_UWQ_SVR_MPS_MMAPS svm, IEU_UWQ_SVR_MPS_PLUGINS svp ' ||
94 ' where b.MEDIA_TYPE_ID = tl.MEDIA_TYPE_ID and b.MEDIA_TYPE_ID = cl.MEDIA_TYPE_ID ' ||
95 ' and tl.LANGUAGE = '||''''||p_language||''''||' and b.MEDIA_TYPE_ID = svm.MEDIA_TYPE_ID ' ||
96 ' and svm.SVR_TYPE_ID = svp.SVR_TYPE_ID order by ' || p_order_by || ' ' || p_asc;
97
98 DBMS_SQL.PARSE(v_cursorID, v_selectStmt, DBMS_SQL.V7);
99
100 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 1, v_media_type_id);
101 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 2, v_tel_reqd_flag, 1);
102 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 3, v_media_type_name, 1996);
103 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 4, v_media_type_description,1996);
104 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 5, v_cli_plugin_id);
105 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 6, v_cli_plugin_class, 1996);
106 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 7, v_svr_type_id);
107 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 8, v_svr_mps_plugin_id);
108 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 9, v_svr_plugin_class, 1996);
109 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 10, v_media_type_uuid, 38);
110 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 11, v_svr_login_rule_id);
111 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 12, v_application_id);
112
113 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 13, v_is_server_side, 1);
114 DBMS_SQL.DEFINE_COLUMN(v_cursorID, 14, v_classfn_query_proc, 60);
115
116
117 v_dummy := DBMS_SQL.EXECUTE(v_cursorID);
118
119 x_media_type_list := SYSTEM.IEU_MEDIA_TYPE_NST();
120 loop
121 if DBMS_SQL.FETCH_ROWS(v_cursorID) = 0 then
122 exit;
123 end if;
124
125 DBMS_SQL.COLUMN_VALUE(v_cursorID, 1, v_media_type_id);
126
127 DBMS_SQL.COLUMN_VALUE(v_cursorID, 2, v_tel_reqd_flag);
128
129 DBMS_SQL.COLUMN_VALUE(v_cursorID, 3, v_media_type_name);
130
131 DBMS_SQL.COLUMN_VALUE(v_cursorID, 4, v_media_type_description);
132
133 DBMS_SQL.COLUMN_VALUE(v_cursorID, 5, v_cli_plugin_id);
134
135 DBMS_SQL.COLUMN_VALUE(v_cursorID, 6, v_cli_plugin_class);
136
137 DBMS_SQL.COLUMN_VALUE(v_cursorID, 7, v_svr_type_id);
138
139 DBMS_SQL.COLUMN_VALUE(v_cursorID, 8, v_svr_mps_plugin_id);
140
141 DBMS_SQL.COLUMN_VALUE(v_cursorID, 9, v_svr_plugin_class);
142
143 DBMS_SQL.COLUMN_VALUE(v_cursorID, 10, v_media_type_uuid);
144
145 DBMS_SQL.COLUMN_VALUE(v_cursorID, 11, v_svr_login_rule_id);
146
147 DBMS_SQL.COLUMN_VALUE(v_cursorID, 12, v_application_id);
148
149 DBMS_SQL.COLUMN_VALUE(v_cursorID, 13, v_is_server_side);
150
151 DBMS_SQL.COLUMN_VALUE(v_cursorID, 14, v_classfn_query_proc);
152
153
154
155 x_media_type_list.EXTEND;
156 x_media_type_list(x_media_type_list.LAST) := SYSTEM.IEU_MEDIA_TYPE_OBJ(v_media_type_id,
157 v_tel_reqd_flag ,
158 v_media_type_name ,
159 v_media_type_description,
160 v_cli_plugin_id ,
161 v_cli_plugin_class ,
162 v_svr_type_id ,
163 v_svr_mps_plugin_id ,
164 v_svr_plugin_class,
165 v_media_type_uuid,
166 v_svr_login_rule_id,
167 v_application_id,
168 v_application_name,
169 v_is_server_side,
170 v_classfn_query_proc
171 );
172 end loop;
173
174 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
175
176 EXCEPTION
177 WHEN fnd_api.g_exc_error THEN
178
179 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
180 x_return_status := fnd_api.g_ret_sts_error;
181
182 WHEN fnd_api.g_exc_unexpected_error THEN
183
184 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
185 x_return_status := fnd_api.g_ret_sts_unexp_error;
186
187 WHEN OTHERS THEN
188
189 DBMS_SQL.CLOSE_CURSOR(v_cursorID);
190 x_return_status := fnd_api.g_ret_sts_unexp_error;
191
192 END GET_MEDIA_TYPE_LIST;
193
194 --===================================================================
195 -- NAME
196 -- VALIDATE
197 --
198 -- PURPOSE
199 -- Private api to Validate fields.
200 --
201 -- NOTES
202 -- 1. UWQ Admin will use this procedure to validate fields
203 --
204 --
205 -- HISTORY
206 -- 11-Feb-2002 GPAGADAL Created
207
208 --===================================================================
209
210 PROCEDURE VALIDATE ( x_return_status OUT NOCOPY VARCHAR2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2,
213 rec_obj IN SYSTEM.IEU_MEDIA_TYPE_OBJ,
214 is_create IN boolean
215
216 ) AS
217
218
219 media_name_count NUMBER(5);
220 media_uuid_count NUMBER(5);
221 application_name_count NUMBER(5);
222 l_language VARCHAR2(4);
223
224 l_msg_count NUMBER(2);
225 l_msg_data VARCHAR2(2000);
226 temp_media_name VARCHAR2(1996);
227 temp_char VARCHAR2(1);
228 temp_uuid VARCHAR2(38);
229 temp_cli_class VARCHAR2(1996);
230 temp_svr_class VARCHAR2(1996);
231
232 valid_name boolean;
233
234 valid_uuid boolean;
235
236 valid_cli_class boolean;
237
238 valid_svr_class boolean;
239
240 class_name_count NUMBER(5);
241
242 cli_class_name_count NUMBER(5);
243
244 l_temp_classproc IEU_UWQ_MEDIA_TYPES_B.CLASSIFICATION_QUERY_PROC%type;
245 l_temp_pkg_name IEU_UWQ_MEDIA_TYPES_B.CLASSIFICATION_QUERY_PROC%type;
246
247 l_temp_class_count NUMBER(5);
248
249
250
251
252 BEGIN
253
254 fnd_msg_pub.delete_msg();
255 x_return_status := fnd_api.g_ret_sts_success;
256 FND_MSG_PUB.initialize;
257
258 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
259
260 valid_name := true;
261 valid_uuid := true;
262 valid_cli_class := true;
263 valid_svr_class := true;
264
265
266 if is_create then
267
268 if rec_obj.media_type_name IS NULL then
269
270 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_NAME_NULL');
271 FND_MSG_PUB.Add;
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 else
274 temp_media_name := LTRIM(RTRIM(rec_obj.media_type_name));
275
276 FOR i in 1..LENGTH(temp_media_name) LOOP
277
278 temp_char := substr(temp_media_name, i, 1);
279 if ((temp_char >= '0' and temp_char <= '9') or (temp_char >= 'a' and temp_char <= 'z') or
280 (temp_char >= 'A' and temp_char <= 'Z') or (temp_char = '-') or (temp_char = '_') or
281 (temp_char = '.') or (temp_char = ' ')) then
282
283 x_return_status := fnd_api.g_ret_sts_success;
284
285 else
286 valid_name := false;
287 exit;
288 end if;
289 END LOOP;
290
291 if NOT(valid_name) then
292 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_NAME_ILLEGAL');
293 FND_MSG_PUB.Add;
294 x_return_status := FND_API.G_RET_STS_ERROR;
295 else
296
297 select count(*) into media_name_count from IEU_UWQ_MEDIA_TYPES_TL where lower(MEDIA_TYPE_NAME) like lower(temp_media_name);
298 if media_name_count <> 0 then
299 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_NAME_EXISTS');
300 FND_MSG_PUB.Add;
301 x_return_status := FND_API.G_RET_STS_ERROR;
302 end if;
303 end if;
304
305
306 end if;
307
308 if rec_obj.media_type_uuid IS NULL then
309 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_UUID_NULL');
310 FND_MSG_PUB.Add;
311 x_return_status := FND_API.G_RET_STS_ERROR;
312 else
313
314 temp_uuid := LTRIM(RTRIM(rec_obj.media_type_uuid));
318 temp_char := substr(temp_uuid, i, 1);
315
316 FOR i in 1..LENGTH(temp_uuid) LOOP
317
319 if ((temp_char >= '0' and temp_char <= '9') or (temp_char >= 'a' and temp_char <= 'z') or
320 (temp_char >= 'A' and temp_char <= 'Z') or (temp_char = '-') or (temp_char = '_') or
321 (temp_char = '.')) then
322
323 --x_return_status := fnd_api.g_ret_sts_success;
324 null;
325
326 else
327 valid_uuid := false;
328 exit;
329 end if;
330 END LOOP;
331 if NOT(valid_uuid) then
332 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_UUID_ILLEGAL');
333 FND_MSG_PUB.Add;
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 else
336
337 select count(*) into media_uuid_count from IEU_UWQ_MEDIA_TYPES_B where lower(MEDIA_TYPE_UUID) like lower(temp_uuid);
338
339 if media_uuid_count <> 0 then
340 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_UUID_EXISTS');
341 FND_MSG_PUB.Add;
342 x_return_status := FND_API.G_RET_STS_ERROR;
343 end if;
344 end if;
345 end if;
346
347
348 end if;
349
350
351 if rec_obj.application_name IS NULL then
352 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_APPNAME_NULL');
353 FND_MSG_PUB.Add;
354 x_return_status := FND_API.G_RET_STS_ERROR;
355 else
356 select count(*) into application_name_count from FND_APPLICATION_TL T, FND_APPLICATION B
357 where B.APPLICATION_ID = T.APPLICATION_ID and T.LANGUAGE = l_language and lower(T.APPLICATION_NAME) like lower(rec_obj.application_name);
358
359 if application_name_count = 0 then
360 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_APPNAME_INVALID');
361 FND_MSG_PUB.Add;
362 raise FND_API.G_EXC_ERROR;
363 x_return_status := FND_API.G_RET_STS_ERROR;
364 end if;
365 end if;
366
367
368 if NOT(rec_obj.cli_plugin_class IS NULL) then
369
370 temp_cli_class := LTRIM(RTRIM(rec_obj.cli_plugin_class));
371
372 FOR i in 1..LENGTH(temp_cli_class) LOOP
373
374 temp_char := substr(temp_cli_class, i, 1);
375 if ((temp_char >= '0' and temp_char <= '9') or (temp_char >= 'a' and temp_char <= 'z') or
376 (temp_char >= 'A' and temp_char <= 'Z') or (temp_char = '-') or (temp_char = '_') or
377 (temp_char = '.')) then
378
379 -- x_return_status := fnd_api.g_ret_sts_success;
380 null;
381
382 else
383 valid_cli_class := false;
384 exit;
385 end if;
386 END LOOP;
387
388 if NOT(valid_cli_class) then
389 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_CLI_ILLEGAL');
390 FND_MSG_PUB.Add;
391 x_return_status := FND_API.G_RET_STS_ERROR;
392 end if;
393
394 if (rec_obj.cli_plugin_id IS NULL) then
395 select count(*) into cli_class_name_count from IEU_UWQ_CLI_MED_PLUGINS
396 where lower(CLI_PLUGIN_CLASS) like lower(temp_cli_class);
397
398
399
400 if cli_class_name_count <> 0 then
401
402
403 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_CLI_NAME');
404 FND_MSG_PUB.Add;
405 x_return_status := FND_API.G_RET_STS_ERROR;
406
407 end if;
408 end if;
409
410 end if;
411
412
413
414
415 if NOT(rec_obj.svr_plugin_class IS NULL) then
416
417 temp_svr_class := LTRIM(RTRIM(rec_obj.svr_plugin_class));
418
419 FOR i in 1..LENGTH(temp_svr_class) LOOP
420
421 temp_char := substr(temp_svr_class, i, 1);
422 if ((temp_char >= '0' and temp_char <= '9') or (temp_char >= 'a' and temp_char <= 'z') or
423 (temp_char >= 'A' and temp_char <= 'Z') or (temp_char = '-') or (temp_char = '_') or
424 (temp_char = '.')) then
425
426 --x_return_status := fnd_api.g_ret_sts_success;
427 null;
428
429 else
430 valid_svr_class := false;
431 exit;
432 end if;
433 END LOOP;
434
435 if NOT(valid_svr_class) then
436 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_SVR_ILLEGAL');
437 FND_MSG_PUB.Add;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 end if;
440
441 if (rec_obj.svr_mps_plugin_id IS NULL) then
442
443 if rec_obj.is_server_side = 'Y' then
444
445 select count(*) into class_name_count from IEU_UWQ_SVR_MPS_PLUGINS
446 where lower(SVR_PLUGIN_CLASS) like lower(temp_svr_class);
447
448
449
450 if class_name_count <> 0 then
454 end if;
451 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_SVR_NAME');
452 FND_MSG_PUB.Add;
453 x_return_status := FND_API.G_RET_STS_ERROR;
455 else
456 select count(*) into class_name_count from IEU_CLI_PROV_PLUGINS
457 where lower(PLUGIN_CLASS_NAME) like lower(temp_svr_class);
458
459
460
461 if class_name_count <> 0 then
462 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDIA_SVR_NAME');
463 FND_MSG_PUB.Add;
464 x_return_status := FND_API.G_RET_STS_ERROR;
465 end if;
466
467 end if;
468 end if;
469
470 end if;
471
472
473 -- check the classification_query api
474
475 --commented for seed database
476 /* if NOT(rec_obj.classfn_query_proc IS NULL) then
477
478 l_temp_classproc := rec_obj.classfn_query_proc;
479
480 l_temp_pkg_name := substr(l_temp_classproc,1, ( instr(l_temp_classproc,'.',1,1)-1));
481 begin
482 select count(*) into l_temp_class_count from all_objects where owner = 'APPS' and object_type in('PACKAGE', 'PACKAGE BODY') and status='VALID'and object_name = l_temp_pkg_name;
483
484 if not(l_temp_class_count is null) and (l_temp_class_count <= 0) then
485 FND_MESSAGE.set_name('IEU', 'IEU_PROV_MEDCLASSFN_INVALID');
486 FND_MSG_PUB.Add;
487 x_return_status := FND_API.G_RET_STS_ERROR;
488
489 end if;
490
491 EXCEPTION
492
493 WHEN NO_DATA_FOUND THEN
494 null;
495
496
497 end;
498
499
500 end if;*/
501
502
503 -- Standard call to get message count and if count is 1, get message info.
504 FND_MSG_PUB.Count_And_Get(
505 p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508
509 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
510 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
511 x_msg_data := x_msg_data || ',' || l_msg_data;
512 END LOOP;
513
514
515
516 EXCEPTION
517
518 WHEN FND_API.G_EXC_ERROR THEN
519
520
521 x_return_status := FND_API.G_RET_STS_ERROR;
522 FND_MSG_PUB.Count_And_Get(
523 p_count => x_msg_count,
524 p_data => x_msg_data
525 );
526
527
528 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
529 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
530 x_msg_data := x_msg_data || ',' || l_msg_data;
531 END LOOP;
532
533
534 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
535
536
537
538 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
539 FND_MSG_PUB.Count_And_Get
540 (
541 p_count => x_msg_count,
542 p_data => x_msg_data
543 );
544
545 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
546 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
547 x_msg_data := x_msg_data || ',' || l_msg_data;
548 END LOOP;
549
550 WHEN OTHERS THEN
551
552
553 --Rollback to IEU_UWQ_MEDIA_TYPES_PVT;
554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
555
556 FND_MSG_PUB.Count_And_Get (
557 p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560
561 FOR i in 1..fnd_msg_pub.COUNT_MSG() LOOP
562 FND_MSG_PUB.Get(i, fnd_api.g_true, l_msg_data, l_msg_count);
563 x_msg_data := x_msg_data || ',' || l_msg_data;
564 END LOOP;
565
566
567 END VALIDATE;
568
569 --===================================================================
570 -- NAME
571 -- CREATE_MEDIA_TYPE
572 --
573 -- PURPOSE
574 -- Private api to create media type
575 --
576 -- NOTES
577 -- 1. UWQ Admin will use this procedure to create media type
578 --
579 --
580 -- HISTORY
581 -- 11-Feb-2002 GPAGADAL Created
582 -- 18-Sep-2002 GPAGADAL Updated--added client side media provider
583
584 --===================================================================
585
586
587 PROCEDURE CREATE_MEDIA_TYPE (x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590 rec_obj IN SYSTEM.IEU_MEDIA_TYPE_OBJ
591 ) AS
592
593 l_media_type_id NUMBER(15);
594 l_language VARCHAR2(4);
595 l_cli_plugin_id NUMBER(15);
596 l_svr_mps_mmap_id NUMBER(15);
597 l_svr_mps_plugin_id NUMBER(15);
598
602 l_plugin_med_map_id NUMBER(15);
599 temp_svr_type_id NUMBER(15);
600 temp_cli_plugin_id NUMBER(15);
601 l_plugin_id NUMBER(15);
603
604 BEGIN
605
606 x_return_status := fnd_api.g_ret_sts_success;
607
608 IEU_UWQ_MEDIA_TYPES_PVT.VALIDATE ( x_return_status,
609 x_msg_count,
610 x_msg_data,
611 rec_obj, true);
612 if x_return_status = 'S' then
613
614 select IEU_UWQ_MEDIA_TYPES_B_S2.NEXTVAL into l_media_type_id from sys.dual;
615
616 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
617
618 insert INTO IEU_UWQ_MEDIA_TYPES_B
619 (MEDIA_TYPE_ID,
620 CREATED_BY,
621 CREATION_DATE,
622 LAST_UPDATED_BY,
623 LAST_UPDATE_DATE,
624 LAST_UPDATE_LOGIN,
625 SECURITY_GROUP_ID,
626 OBJECT_VERSION_NUMBER,
627 SIMPLE_BLENDING_ORDER,
628 MEDIA_TYPE_UUID,
629 TEL_REQD_FLAG,
630 APPLICATION_ID,
631 SVR_LOGIN_RULE_ID,
632 CLASSIFICATION_QUERY_PROC,
633 SH_CATEGORY_TYPE ,
634 IMAGE_FILE_NAME,
635 BLENDED_FLAG,
636 BLENDED_DIR
637 )
638 values (
639 l_media_type_id,
640 FND_GLOBAL.USER_ID,
641 SYSDATE,
642 FND_GLOBAL.USER_ID,
643 SYSDATE,
644 FND_GLOBAL.LOGIN_ID,
645 null,
646 1,
647 null,
648 LTRIM(RTRIM(rec_obj.media_type_uuid)),
649 rec_obj.tel_reqd_flag,
650 rec_obj.application_id,
651 rec_obj.svr_login_rule_id,
652 rec_obj.classfn_query_proc,
653 null,
654 null,
655 null,
656 null
657 );
658
659 insert into IEU_UWQ_MEDIA_TYPES_TL
660 ( MEDIA_TYPE_ID,
661 LANGUAGE,
662 CREATED_BY,
663 CREATION_DATE,
664 LAST_UPDATED_BY,
665 LAST_UPDATE_DATE,
666 LAST_UPDATE_LOGIN,
667 MEDIA_TYPE_NAME,
668 SOURCE_LANG,
669 MEDIA_TYPE_DESCRIPTION,
670 SECURITY_GROUP_ID,
671 OBJECT_VERSION_NUMBER
672 )
673 values ( l_media_type_id,
674 l_language,
675 FND_GLOBAL.USER_ID,
676 SYSDATE,
677 FND_GLOBAL.USER_ID,
678 SYSDATE,
679 FND_GLOBAL.LOGIN_ID,
680 LTRIM(RTRIM(rec_obj.media_type_name)),
681 l_language,
682 rec_obj.media_type_description,
683 null,
684 1
685 );
686
687 if NOT (rec_obj.cli_plugin_class IS NULL) then
688
689
690 select IEU_UWQ_CLI_MED_PLUGINS_S2.NEXTVAL into l_cli_plugin_id from sys.dual;
691
692 insert into IEU_UWQ_CLI_MED_PLUGINS
693 ( CLI_PLUGIN_ID,
694 CREATED_BY,
695 CREATION_DATE,
696 LAST_UPDATED_BY,
697 LAST_UPDATE_DATE,
698 LAST_UPDATE_LOGIN,
699 MEDIA_TYPE_ID,
700 CLI_PLUGIN_CLASS,
701 SECURITY_GROUP_ID ,
702 OBJECT_VERSION_NUMBER,
703 APPLICATION_ID
704 )
705 values ( l_cli_plugin_id,
706 FND_GLOBAL.USER_ID,
707 SYSDATE,
708 FND_GLOBAL.USER_ID,
709 SYSDATE,
710 FND_GLOBAL.LOGIN_ID,
711 l_media_type_id,
712 LTRIM(RTRIM(rec_obj.cli_plugin_class)),
713 null,
714 1,
715 rec_obj.application_id
716 );
717
718 end if;
719
720 -- if server side media provider
721 if rec_obj.is_server_side = 'Y' then
722
723 if rec_obj.svr_mps_plugin_id IS NULL then
724
725 select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
726
727 select IEU_UWQ_SVR_MPS_PLUGINS_S2.NEXTVAL into l_svr_mps_plugin_id from sys.dual;
728 insert into IEU_UWQ_SVR_MPS_MMAPS
729 ( SVR_MPS_MMAP_ID,
730 CREATED_BY,
731 CREATION_DATE,
732 LAST_UPDATED_BY,
733 LAST_UPDATE_DATE,
734 LAST_UPDATE_LOGIN,
735 MEDIA_TYPE_ID,
736 SVR_TYPE_ID,
737 MEDIA_TYPE_MAP,
738 SECURITY_GROUP_ID,
739 OBJECT_VERSION_NUMBER
740 )
741 values ( l_svr_mps_mmap_id,
742 FND_GLOBAL.USER_ID,
743 SYSDATE,
744 FND_GLOBAL.USER_ID,
745 SYSDATE,
746 FND_GLOBAL.LOGIN_ID,
747 l_media_type_id,
748 rec_obj.svr_type_id,
749 null,
750 null,
751 1
752 );
753
754 insert into IEU_UWQ_SVR_MPS_PLUGINS
755 ( SVR_MPS_PLUGIN_ID,
756 CREATED_BY,
757 CREATION_DATE,
758 LAST_UPDATED_BY,
762 SVR_PLUGIN_CLASS,
759 LAST_UPDATE_DATE,
760 LAST_UPDATE_LOGIN,
761 SVR_TYPE_ID,
763 SECURITY_GROUP_ID,
764 OBJECT_VERSION_NUMBER,
765 APPLICATION_ID
766 )
767 values ( l_svr_mps_plugin_id,
768 FND_GLOBAL.USER_ID,
769 SYSDATE,
770 FND_GLOBAL.USER_ID,
771 SYSDATE,
772 FND_GLOBAL.LOGIN_ID,
773 rec_obj.svr_type_id,
774 LTRIM(RTRIM(rec_obj.svr_plugin_class)),
775 null,
776 1,
777 rec_obj.application_id
778 );
779
780 else
781
782
783 select svr_type_id into temp_svr_type_id from ieu_uwq_svr_mps_plugins where svr_mps_plugin_id = rec_obj.svr_mps_plugin_id;
784
785
786 select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
787
788 insert into IEU_UWQ_SVR_MPS_MMAPS
789 ( SVR_MPS_MMAP_ID,
790 CREATED_BY,
791 CREATION_DATE,
792 LAST_UPDATED_BY,
793 LAST_UPDATE_DATE,
794 LAST_UPDATE_LOGIN,
795 MEDIA_TYPE_ID,
796 SVR_TYPE_ID,
797 MEDIA_TYPE_MAP,
798 SECURITY_GROUP_ID,
799 OBJECT_VERSION_NUMBER
800 )
801 values ( l_svr_mps_mmap_id,
802 FND_GLOBAL.USER_ID,
803 SYSDATE,
804 FND_GLOBAL.USER_ID,
805 SYSDATE,
806 FND_GLOBAL.LOGIN_ID,
807 l_media_type_id,
808 temp_svr_type_id,
809 null,
810 null,
811 1
812 );
813
814 end if;
815 else
816 --client side media provider
817 select IEU_CLI_PROV_PLUGINS_S1.NEXTVAL into l_plugin_id from sys.dual;
818
819 insert into IEU_CLI_PROV_PLUGINS
820 (PLUGIN_ID,
821 PLUGIN_CLASS_NAME,
822 IS_ACTIVE_FLAG,
823 APPLICATION_ID,
824 OBJECT_VERSION_NUMBER,
825 CREATED_BY,
826 CREATION_DATE,
827 LAST_UPDATED_BY,
828 LAST_UPDATE_DATE,
829 LAST_UPDATE_LOGIN
830 )
831 values (l_plugin_id,
832 LTRIM(RTRIM(rec_obj.svr_plugin_class)),
833 'Y',
834 rec_obj.application_id,
835 1,
836 FND_GLOBAL.USER_ID,
837 SYSDATE,
838 FND_GLOBAL.USER_ID,
839 SYSDATE,
840 FND_GLOBAL.LOGIN_ID
841 );
842
843 select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
844 insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
845 (PLUGIN_MED_MAP_ID,
846 PLUGIN_ID,
847 MEDIA_TYPE_ID,
848 CONDITIONAL_FUNC,
849 OBJECT_VERSION_NUMBER,
850 CREATED_BY,
851 CREATION_DATE,
852 LAST_UPDATE_DATE,
853 LAST_UPDATED_BY,
854 LAST_UPDATE_LOGIN
855 )
856 values (l_plugin_med_map_id,
857 l_plugin_id,
858 l_media_type_id,
859 null,
860 1,
861 FND_GLOBAL.USER_ID,
862 SYSDATE,
863 SYSDATE,
864 FND_GLOBAL.USER_ID,
865 FND_GLOBAL.LOGIN_ID
866 );
867
868 end if;
869 end if;
870
871 COMMIT;
872
873 EXCEPTION
874 WHEN fnd_api.g_exc_error THEN
875
876
877 ROLLBACK;
878 x_return_status := fnd_api.g_ret_sts_error;
879
880 WHEN fnd_api.g_exc_unexpected_error THEN
881
882
883 ROLLBACK;
884 x_return_status := fnd_api.g_ret_sts_unexp_error;
885
886 WHEN OTHERS THEN
887
888
889 ROLLBACK;
890 x_return_status := fnd_api.g_ret_sts_unexp_error;
891
892 END CREATE_MEDIA_TYPE;
893
894
895 --===================================================================
896 -- NAME
900 -- Private api to update media type
897 -- UPDATE_MEDIA_TYPE
898 --
899 -- PURPOSE
901 --
902 -- NOTES
903 -- 1. UWQ Admin will use this procedure to update media type
904 --
905 --
906 -- HISTORY
907 -- 11-Feb-2002 GPAGADAL Created
908 -- 25-Feb-2002 GPAGADAL Updated
909 -- 18-Sep-2002 GPAGADAL Updated
910
911 --===================================================================
912
913
914
915 PROCEDURE UPDATE_MEDIA_TYPE (x_return_status OUT NOCOPY VARCHAR2,
916 x_msg_count OUT NOCOPY NUMBER,
917 x_msg_data OUT NOCOPY VARCHAR2,
918 rec_obj IN SYSTEM.IEU_MEDIA_TYPE_OBJ
919 ) AS
920
921 l_cli_plugin_id NUMBER(15);
922 l_svr_mps_mmap_id NUMBER(15);
923 l_svr_mps_plugin_id NUMBER(15);
924 l_language VARCHAR2(4);
925
926 temp_map_id NUMBER(15);
927 temp_cli_plugin_id NUMBER(15);
928 temp_svr_type_id NUMBER(15);
929
930 l_plugin_id NUMBER(15);
931 l_plugin_med_map_id NUMBER(15);
932 temp_plugin_id NUMBER(15);
933
934
935
936 BEGIN
937 x_return_status := fnd_api.g_ret_sts_success;
938 l_language := FND_GLOBAL.CURRENT_LANGUAGE;
939
940
941
942 IEU_UWQ_MEDIA_TYPES_PVT.VALIDATE ( x_return_status,
943 x_msg_count,
944 x_msg_data,
945 rec_obj, false);
946
947 if x_return_status = 'S' then
948
949
950 update IEU_UWQ_MEDIA_TYPES_B set
951 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
952 LAST_UPDATE_DATE = SYSDATE,
953 LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID,
954 TEL_REQD_FLAG = rec_obj.tel_reqd_flag,
955 APPLICATION_ID = rec_obj.application_id,
956 SVR_LOGIN_RULE_ID = rec_obj.svr_login_rule_id,
957 CLASSIFICATION_QUERY_PROC = rec_obj.classfn_query_proc
958 where MEDIA_TYPE_ID = rec_obj.media_type_id;
959
960
961 update IEU_UWQ_MEDIA_TYPES_TL set
962 LANGUAGE = l_language,
963 LAST_UPDATED_BY =FND_GLOBAL.USER_ID ,
964 LAST_UPDATE_DATE = SYSDATE,
965 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
966 MEDIA_TYPE_NAME = rec_obj.media_type_name,
967 SOURCE_LANG = l_language,
968 MEDIA_TYPE_DESCRIPTION = rec_obj.media_type_description
969 where MEDIA_TYPE_ID = rec_obj.media_type_id
970 and l_language IN (language, source_lang);
971
972
973 if NOT (rec_obj.cli_plugin_class IS NULL) then
974
975 begin
976 select cli_plugin_id into temp_cli_plugin_id from IEU_UWQ_CLI_MED_PLUGINS where media_type_id = rec_obj.media_type_id;
977
978 update IEU_UWQ_CLI_MED_PLUGINS set
979 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
980 LAST_UPDATE_DATE = SYSDATE,
981 LAST_UPDATE_LOGIN =FND_GLOBAL.LOGIN_ID,
982 CLI_PLUGIN_CLASS = rec_obj.cli_plugin_class,
983 MEDIA_TYPE_ID = rec_obj.media_type_id,
984 APPLICATION_ID = rec_obj.application_id
985 where CLI_PLUGIN_ID = temp_cli_plugin_id;
986
987
988 EXCEPTION
989
990 WHEN NO_DATA_FOUND THEN
991
992 select IEU_UWQ_CLI_MED_PLUGINS_S2.NEXTVAL into l_cli_plugin_id from sys.dual;
993 insert into IEU_UWQ_CLI_MED_PLUGINS
994 ( CLI_PLUGIN_ID,
995 CREATED_BY,
996 CREATION_DATE,
997 LAST_UPDATED_BY,
998 LAST_UPDATE_DATE,
999 LAST_UPDATE_LOGIN,
1000 MEDIA_TYPE_ID,
1001 CLI_PLUGIN_CLASS,
1002 SECURITY_GROUP_ID,
1003 OBJECT_VERSION_NUMBER,
1004 APPLICATION_ID
1005 )
1006 values ( l_cli_plugin_id,
1007 FND_GLOBAL.USER_ID,
1008 SYSDATE,
1009 FND_GLOBAL.USER_ID,
1010 SYSDATE,
1011 FND_GLOBAL.LOGIN_ID,
1012 rec_obj.media_type_id,
1013 rec_obj.cli_plugin_class,
1014 null,
1015 1,
1016 rec_obj.application_id
1017 );
1018
1019 end;
1020
1021 else
1022
1023 begin
1024
1025
1026 select cli_plugin_id into temp_cli_plugin_id from IEU_UWQ_CLI_MED_PLUGINS where media_type_id = rec_obj.media_type_id;
1027
1028 delete from IEU_UWQ_CLI_MED_PLUGINS where cli_plugin_id = temp_cli_plugin_id;
1029
1030 if (sql%notfound) then
1031 RAISE no_data_found;
1032
1033 end if;
1034
1035 EXCEPTION
1036
1037 WHEN NO_DATA_FOUND THEN
1038 null;
1039
1040 end;
1041
1042 end if;
1043
1044 --server side media provider
1045
1046 if rec_obj.is_server_side = 'Y' then
1047
1048 if rec_obj.svr_mps_plugin_id IS NULL AND NOT (rec_obj.svr_type_id IS NULL) then
1049
1050 -- select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
1051
1052 select IEU_UWQ_SVR_MPS_PLUGINS_S2.NEXTVAL into l_svr_mps_plugin_id from sys.dual;
1053
1057 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1054
1055
1056 update IEU_UWQ_SVR_MPS_MMAPS set
1058 LAST_UPDATE_DATE = SYSDATE,
1059 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1060 SVR_TYPE_ID = rec_obj.svr_type_id
1061 where MEDIA_TYPE_ID = rec_obj.media_type_id;
1062
1063
1064 insert into IEU_UWQ_SVR_MPS_PLUGINS
1065 ( SVR_MPS_PLUGIN_ID,
1066 CREATED_BY,
1067 CREATION_DATE,
1068 LAST_UPDATED_BY,
1069 LAST_UPDATE_DATE,
1070 LAST_UPDATE_LOGIN,
1071 SVR_TYPE_ID,
1072 SVR_PLUGIN_CLASS,
1073 SECURITY_GROUP_ID,
1074 OBJECT_VERSION_NUMBER,
1075 APPLICATION_ID
1076 )
1077 values ( l_svr_mps_plugin_id,
1078 FND_GLOBAL.USER_ID,
1079 SYSDATE,
1080 FND_GLOBAL.USER_ID,
1081 SYSDATE,
1082 FND_GLOBAL.LOGIN_ID,
1083 rec_obj.svr_type_id,
1084 rec_obj.svr_plugin_class,
1085 null,
1086 1,
1087 rec_obj.application_id
1088 );
1089
1090 elsif NOT (rec_obj.svr_mps_plugin_id IS NULL) AND rec_obj.svr_type_id IS NULL then
1091
1092
1093 begin
1094 select svr_type_id into temp_svr_type_id from ieu_uwq_svr_mps_plugins where svr_mps_plugin_id = rec_obj.svr_mps_plugin_id;
1095
1096
1097 update IEU_UWQ_SVR_MPS_MMAPS set
1098 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1099 LAST_UPDATE_DATE = SYSDATE,
1100 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID,
1101 SVR_TYPE_ID = temp_svr_type_id
1102 where MEDIA_TYPE_ID = rec_obj.media_type_id;
1103
1104 EXCEPTION
1105
1106 WHEN NO_DATA_FOUND THEN
1107 RAISE no_data_found;
1108
1109 end;
1110
1111 if (SQL%NOTFOUND) then
1112
1113 select IEU_UWQ_SVR_MPS_MMAPS_S2.NEXTVAL into l_svr_mps_mmap_id from sys.dual;
1114 insert into IEU_UWQ_SVR_MPS_MMAPS
1115 ( SVR_MPS_MMAP_ID,
1116 CREATED_BY,
1117 CREATION_DATE,
1118 LAST_UPDATED_BY,
1119 LAST_UPDATE_DATE,
1120 LAST_UPDATE_LOGIN,
1121 MEDIA_TYPE_ID,
1122 SVR_TYPE_ID,
1123 MEDIA_TYPE_MAP,
1124 SECURITY_GROUP_ID,
1125 OBJECT_VERSION_NUMBER
1126 )
1127 values ( l_svr_mps_mmap_id,
1128 FND_GLOBAL.USER_ID,
1129 SYSDATE,
1130 FND_GLOBAL.USER_ID,
1131 SYSDATE,
1132 FND_GLOBAL.LOGIN_ID,
1133 rec_obj.media_type_id,
1134 temp_svr_type_id,
1135 null,
1136 null,
1137 1
1138 );
1139
1140 end if;
1141
1142 end if;
1143
1144 --delete an entry in client side media provider
1145 begin
1146
1147 delete from IEU_CLI_PROV_PLUGIN_MED_MAPS where media_type_id = rec_obj.media_type_id;
1148
1149 if (sql%notfound) then
1150 RAISE no_data_found;
1151
1152 end if;
1153
1154 EXCEPTION
1155
1156 WHEN NO_DATA_FOUND THEN
1157 null;
1158
1159 end;
1160
1161 else
1162 -- client side media provider
1163 begin
1164 --select PLUGIN_ID into temp_plugin_id from IEU_CLI_PROV_PLUGIN_MED_MAPS where
1165 -- MEDIA_TYPE_ID = rec_obj.media_type_id;
1166 if NOT(rec_obj.svr_mps_plugin_id IS NULL) then
1167
1168
1169
1170 update IEU_CLI_PROV_PLUGIN_MED_MAPS set
1171 PLUGIN_ID = rec_obj.svr_mps_plugin_id,
1172 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1173 LAST_UPDATE_DATE = SYSDATE,
1174 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1178
1175 where MEDIA_TYPE_ID = rec_obj.media_type_id;
1176
1177 if (sql%notfound) then
1179 select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
1180
1181 insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
1182 (PLUGIN_MED_MAP_ID,
1183 PLUGIN_ID,
1184 MEDIA_TYPE_ID,
1185 CONDITIONAL_FUNC,
1186 OBJECT_VERSION_NUMBER ,
1187 CREATED_BY,
1188 CREATION_DATE,
1189 LAST_UPDATE_DATE,
1190 LAST_UPDATED_BY,
1191 LAST_UPDATE_LOGIN
1192 )
1193 values (l_plugin_med_map_id,
1194 rec_obj.svr_mps_plugin_id,
1195 rec_obj.media_type_id,
1196 null,
1197 null,
1198 FND_GLOBAL.USER_ID,
1199 SYSDATE,
1200 SYSDATE,
1201 FND_GLOBAL.USER_ID,
1202 FND_GLOBAL.LOGIN_ID
1203 );
1204 end if;
1205
1206
1207 else
1208
1209 select IEU_CLI_PROV_PLUGINS_S1.NEXTVAL into l_plugin_id from sys.dual;
1210
1211 insert into IEU_CLI_PROV_PLUGINS
1212 (PLUGIN_ID,
1213 PLUGIN_CLASS_NAME,
1214 IS_ACTIVE_FLAG,
1215 APPLICATION_ID,
1216 OBJECT_VERSION_NUMBER ,
1217 CREATED_BY,
1218 CREATION_DATE,
1219 LAST_UPDATED_BY,
1220 LAST_UPDATE_DATE,
1221 LAST_UPDATE_LOGIN
1222 )
1223 values (l_plugin_id,
1224 LTRIM(RTRIM(rec_obj.svr_plugin_class)),
1225 'Y',
1226 rec_obj.application_id,
1227 1,
1228 FND_GLOBAL.USER_ID,
1229 SYSDATE,
1230 FND_GLOBAL.USER_ID,
1231 SYSDATE,
1232 FND_GLOBAL.LOGIN_ID
1233 );
1234
1235 select IEU_CLI_PROV_PLUGIN_MED_MAP_S1.NEXTVAL into l_plugin_med_map_id from sys.dual;
1236
1237 insert into IEU_CLI_PROV_PLUGIN_MED_MAPS
1238 (PLUGIN_MED_MAP_ID,
1239 PLUGIN_ID,
1240 MEDIA_TYPE_ID,
1241 CONDITIONAL_FUNC,
1242 OBJECT_VERSION_NUMBER ,
1243 CREATED_BY,
1244 CREATION_DATE,
1245 LAST_UPDATE_DATE,
1246 LAST_UPDATED_BY,
1247 LAST_UPDATE_LOGIN
1248 )
1249 values (l_plugin_med_map_id,
1250 l_plugin_id,
1251 rec_obj.media_type_id,
1252 null,
1253 1,
1254 FND_GLOBAL.USER_ID,
1255 SYSDATE,
1256 SYSDATE,
1257 FND_GLOBAL.USER_ID,
1258 FND_GLOBAL.LOGIN_ID
1259 );
1260 end if;
1261
1262
1263
1264 EXCEPTION
1265
1266 WHEN NO_DATA_FOUND THEN
1267 null;
1268
1269
1270 end;
1271
1272 -- delete server side media provider
1273 begin
1274 delete from IEU_UWQ_SVR_MPS_MMAPS where MEDIA_TYPE_ID = rec_obj.media_type_id;
1275
1276 if (sql%notfound) then
1277 RAISE no_data_found;
1278
1279 end if;
1280
1281 EXCEPTION
1282
1283 WHEN NO_DATA_FOUND THEN
1284 null;
1285
1286 end;
1287
1288
1289 end if;
1290
1291
1292 end if;
1293
1294
1295 commit;
1296
1297
1298 EXCEPTION
1299 WHEN fnd_api.g_exc_error THEN
1300 ROLLBACK;
1301 x_return_status := fnd_api.g_ret_sts_error;
1302
1303 WHEN fnd_api.g_exc_unexpected_error THEN
1304 ROLLBACK;
1305 x_return_status := fnd_api.g_ret_sts_unexp_error;
1306
1307 WHEN NO_DATA_FOUND THEN
1308 null;
1309
1310 WHEN OTHERS THEN
1311 ROLLBACK;
1315
1312 x_return_status := fnd_api.g_ret_sts_unexp_error;
1313
1314
1316 END UPDATE_MEDIA_TYPE;
1317
1318
1319 --===================================================================
1320 -- NAME
1321 -- DELETE_MEDIA_TYPE
1322 --
1323 -- PURPOSE
1324 -- Private api to delete media type
1325 --
1326 -- NOTES
1327 -- 1. UWQ Admin will use this procedure to delete media type
1328 --
1329 --
1330 -- HISTORY
1331 -- 11-Feb-2002 GPAGADAL Created
1332 -- 26-Feb-2002 GPAGADAL Updated
1333
1334 --===================================================================
1335
1336
1337 PROCEDURE DELETE_MEDIA_TYPE (
1338 x_media_type_id IN NUMBER
1339 ) is
1340
1341 media_count NUMBER(15);
1342 temp_svr_type_id NUMBER(15);
1343
1344 BEGIN
1345 delete from IEU_UWQ_MEDIA_TYPES_TL
1346 where MEDIA_TYPE_ID = x_media_type_id;
1347
1348
1349 if (sql%notfound) then
1350 null;
1351 end if;
1352
1353 delete from IEU_UWQ_MEDIA_TYPES_B
1354 where MEDIA_TYPE_ID = x_media_type_id;
1355
1356 if (sql%notfound) then
1357 null;
1358 end if;
1359
1360 delete from IEU_UWQ_CLI_MED_PLUGINS
1361 where MEDIA_TYPE_ID = x_media_type_id;
1362
1363 if (sql%notfound) then
1364 null;
1365 end if;
1366
1367
1368 begin
1369
1370 select svr_type_id into temp_svr_type_id from IEU_UWQ_SVR_MPS_MMAPS where MEDIA_TYPE_ID = x_media_type_id;
1371
1372
1373 select count(MEDIA_TYPE_ID) into media_count from IEU_UWQ_SVR_MPS_MMAPS where svr_type_id = temp_svr_type_id;
1374
1375 delete from IEU_UWQ_SVR_MPS_MMAPS
1376 where MEDIA_TYPE_ID = x_media_type_id;
1377
1378
1379 if (sql%notfound) then
1380 null;
1381 end if;
1382
1383
1384
1385 if media_count = 1 then
1386
1387 delete from IEU_UWQ_SVR_MPS_PLUGINS
1388 where svr_type_id = temp_svr_type_id;
1389
1390 if (sql%notfound) then
1391 null;
1392 end if;
1393
1394 end if;
1395
1396 EXCEPTION
1397
1398 WHEN NO_DATA_FOUND THEN
1399 null;
1400
1401 end;
1402
1403
1404
1405
1406 COMMIT;
1407 END DELETE_MEDIA_TYPE;
1408
1409
1410
1411 END IEU_UWQ_MEDIA_TYPES_PVT;
1412