[Home] [Help]
PACKAGE BODY: APPS.AMS_MEDIA_PKG
Source
1 package body AMS_MEDIA_PKG as
2 /* $Header: amslmdab.pls 115.8 2004/01/30 01:38:34 asaha ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_MEDIA_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_MEDIA_TYPE_CODE in VARCHAR2,
8 X_INBOUND_FLAG in VARCHAR2,
9 X_ENABLED_FLAG in VARCHAR2,
10 X_ATTRIBUTE_CATEGORY in VARCHAR2,
11 X_ATTRIBUTE1 in VARCHAR2,
12 X_ATTRIBUTE2 in VARCHAR2,
13 X_ATTRIBUTE3 in VARCHAR2,
14 X_ATTRIBUTE4 in VARCHAR2,
15 X_ATTRIBUTE5 in VARCHAR2,
16 X_ATTRIBUTE6 in VARCHAR2,
17 X_ATTRIBUTE7 in VARCHAR2,
18 X_ATTRIBUTE8 in VARCHAR2,
19 X_ATTRIBUTE9 in VARCHAR2,
20 X_ATTRIBUTE10 in VARCHAR2,
21 X_ATTRIBUTE11 in VARCHAR2,
22 X_ATTRIBUTE12 in VARCHAR2,
23 X_ATTRIBUTE13 in VARCHAR2,
24 X_ATTRIBUTE14 in VARCHAR2,
25 X_ATTRIBUTE15 in VARCHAR2,
26 X_DEDUPE_RULE_ID in VARCHAR2,
27 X_MEDIA_NAME in VARCHAR2,
28 X_DESCRIPTION in VARCHAR2,
29 X_CREATION_DATE in DATE,
30 X_CREATED_BY in NUMBER,
31 X_LAST_UPDATE_DATE in DATE,
32 X_LAST_UPDATED_BY in NUMBER,
33 X_LAST_UPDATE_LOGIN in NUMBER
34 ) is
35 cursor C is select ROWID from AMS_MEDIA_B
36 where MEDIA_ID = X_MEDIA_ID
37 ;
38 begin
39 insert into AMS_MEDIA_B (
40 MEDIA_ID,
41 OBJECT_VERSION_NUMBER,
42 MEDIA_TYPE_CODE,
43 INBOUND_FLAG,
44 ENABLED_FLAG,
45 ATTRIBUTE_CATEGORY,
46 ATTRIBUTE1,
47 ATTRIBUTE2,
48 ATTRIBUTE3,
49 ATTRIBUTE4,
50 ATTRIBUTE5,
51 ATTRIBUTE6,
52 ATTRIBUTE7,
53 ATTRIBUTE8,
54 ATTRIBUTE9,
55 ATTRIBUTE10,
56 ATTRIBUTE11,
57 ATTRIBUTE12,
58 ATTRIBUTE13,
59 ATTRIBUTE14,
60 ATTRIBUTE15,
61 CREATION_DATE,
62 CREATED_BY,
63 LAST_UPDATE_DATE,
64 LAST_UPDATED_BY,
65 LAST_UPDATE_LOGIN,
66 DEDUPE_RULE_ID
67 ) values (
68 X_MEDIA_ID,
69 X_OBJECT_VERSION_NUMBER,
70 X_MEDIA_TYPE_CODE,
71 X_INBOUND_FLAG,
72 X_ENABLED_FLAG,
73 X_ATTRIBUTE_CATEGORY,
74 X_ATTRIBUTE1,
75 X_ATTRIBUTE2,
76 X_ATTRIBUTE3,
77 X_ATTRIBUTE4,
78 X_ATTRIBUTE5,
79 X_ATTRIBUTE6,
80 X_ATTRIBUTE7,
81 X_ATTRIBUTE8,
82 X_ATTRIBUTE9,
83 X_ATTRIBUTE10,
84 X_ATTRIBUTE11,
85 X_ATTRIBUTE12,
86 X_ATTRIBUTE13,
87 X_ATTRIBUTE14,
88 X_ATTRIBUTE15,
89 X_CREATION_DATE,
90 X_CREATED_BY,
91 X_LAST_UPDATE_DATE,
92 X_LAST_UPDATED_BY,
93 X_LAST_UPDATE_LOGIN,
94 X_DEDUPE_RULE_ID
95 );
96
97 insert into AMS_MEDIA_TL (
98 MEDIA_NAME,
99 DESCRIPTION,
100 MEDIA_ID,
101 LAST_UPDATE_DATE,
102 LAST_UPDATED_BY,
103 CREATION_DATE,
104 CREATED_BY,
105 LAST_UPDATE_LOGIN,
106 LANGUAGE,
107 SOURCE_LANG
108 ) select
109 X_MEDIA_NAME,
110 X_DESCRIPTION,
111 X_MEDIA_ID,
112 X_LAST_UPDATE_DATE,
113 X_LAST_UPDATED_BY,
114 X_CREATION_DATE,
115 X_CREATED_BY,
116 X_LAST_UPDATE_LOGIN,
117 L.LANGUAGE_CODE,
118 userenv('LANG')
119 from FND_LANGUAGES L
120 where L.INSTALLED_FLAG in ('I', 'B')
121 and not exists
122 (select NULL
123 from AMS_MEDIA_TL T
124 where T.MEDIA_ID = X_MEDIA_ID
125 and T.LANGUAGE = L.LANGUAGE_CODE);
126
127 open c;
128 fetch c into X_ROWID;
129 if (c%notfound) then
130 close c;
131 raise no_data_found;
132 end if;
133 close c;
134
135 end INSERT_ROW;
136
137 procedure UPDATE_ROW (
138 X_MEDIA_ID in NUMBER,
139 X_OBJECT_VERSION_NUMBER in NUMBER,
140 X_MEDIA_TYPE_CODE in VARCHAR2,
141 X_INBOUND_FLAG in VARCHAR2,
142 X_ENABLED_FLAG in VARCHAR2,
143 X_ATTRIBUTE_CATEGORY in VARCHAR2,
144 X_ATTRIBUTE1 in VARCHAR2,
145 X_ATTRIBUTE2 in VARCHAR2,
146 X_ATTRIBUTE3 in VARCHAR2,
147 X_ATTRIBUTE4 in VARCHAR2,
148 X_ATTRIBUTE5 in VARCHAR2,
149 X_ATTRIBUTE6 in VARCHAR2,
150 X_ATTRIBUTE7 in VARCHAR2,
151 X_ATTRIBUTE8 in VARCHAR2,
152 X_ATTRIBUTE9 in VARCHAR2,
153 X_ATTRIBUTE10 in VARCHAR2,
154 X_ATTRIBUTE11 in VARCHAR2,
155 X_ATTRIBUTE12 in VARCHAR2,
156 X_ATTRIBUTE13 in VARCHAR2,
157 X_ATTRIBUTE14 in VARCHAR2,
158 X_ATTRIBUTE15 in VARCHAR2,
159 X_DEDUPE_RULE_ID in VARCHAR2,
160 X_MEDIA_NAME in VARCHAR2,
161 X_DESCRIPTION in VARCHAR2,
162 X_LAST_UPDATE_DATE in DATE,
163 X_LAST_UPDATED_BY in NUMBER,
164 X_LAST_UPDATE_LOGIN in NUMBER
165 ) is
166 begin
167 update AMS_MEDIA_B set
168 -- OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER ,
169 MEDIA_TYPE_CODE = X_MEDIA_TYPE_CODE,
170 INBOUND_FLAG = X_INBOUND_FLAG
171 -- removed by soagrawa for bug# 2740393 on 08-jan-2003
172 -- should not update the active flag
173 -- ENABLED_FLAG = X_ENABLED_FLAG,
174 -- ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
175 -- ATTRIBUTE1 = X_ATTRIBUTE1,
176 -- ATTRIBUTE2 = X_ATTRIBUTE2,
177 -- ATTRIBUTE3 = X_ATTRIBUTE3,
178 -- ATTRIBUTE4 = X_ATTRIBUTE4,
179 -- ATTRIBUTE5 = X_ATTRIBUTE5,
180 -- ATTRIBUTE6 = X_ATTRIBUTE6,
181 -- ATTRIBUTE7 = X_ATTRIBUTE7,
182 -- ATTRIBUTE8 = X_ATTRIBUTE8,
183 -- ATTRIBUTE9 = X_ATTRIBUTE9,
184 -- ATTRIBUTE10 = X_ATTRIBUTE10,
185 -- ATTRIBUTE11 = X_ATTRIBUTE11,
186 -- ATTRIBUTE12 = X_ATTRIBUTE12,
187 -- ATTRIBUTE13 = X_ATTRIBUTE13,
188 -- ATTRIBUTE14 = X_ATTRIBUTE14,
189 -- ATTRIBUTE15 = X_ATTRIBUTE15,
190 -- LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
191 -- LAST_UPDATED_BY = X_LAST_UPDATED_BY,
192 -- LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
193 -- DEDUPE_RULE_ID = X_DEDUPE_RULE_ID
194 where MEDIA_ID = X_MEDIA_ID;
195
196 if (sql%notfound) then
197 raise no_data_found;
198 end if;
199
200 /* Following code is uncommented by asaha on 28-Jan-2004 to
201 allow Customization to be overridden form _TL table at least
202 in case last owner was seed data itself. The following comments
203 are super-seeded.
204
205 following code is modified by soagrawa on 13-Jan-2003
206 The seeded activities can be updated for name and description
207 */
208 update AMS_MEDIA_TL set
209 MEDIA_NAME = X_MEDIA_NAME,
210 DESCRIPTION = X_DESCRIPTION,
211 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
212 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
213 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
214 SOURCE_LANG = userenv('LANG')
215 where MEDIA_ID = X_MEDIA_ID
216 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
217
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221 end UPDATE_ROW;
222
223 procedure DELETE_ROW (
224 X_MEDIA_ID in NUMBER
225 ) is
226 begin
227 delete from AMS_MEDIA_TL
228 where MEDIA_ID = X_MEDIA_ID;
229
230 if (sql%notfound) then
231 raise no_data_found;
232 end if;
233
234 delete from AMS_MEDIA_B
235 where MEDIA_ID = X_MEDIA_ID;
236
237 if (sql%notfound) then
238 raise no_data_found;
239 end if;
240 end DELETE_ROW;
241
242 procedure ADD_LANGUAGE
243 is
244 begin
245 delete from AMS_MEDIA_TL T
246 where not exists
247 (select NULL
248 from AMS_MEDIA_B B
249 where B.MEDIA_ID = T.MEDIA_ID
250 );
251
252 update AMS_MEDIA_TL T set (
253 MEDIA_NAME,
254 DESCRIPTION
255 ) = (select
256 B.MEDIA_NAME,
257 B.DESCRIPTION
258 from AMS_MEDIA_TL B
259 where B.MEDIA_ID = T.MEDIA_ID
260 and B.LANGUAGE = T.SOURCE_LANG)
261 where (
262 T.MEDIA_ID,
263 T.LANGUAGE
264 ) in (select
265 SUBT.MEDIA_ID,
266 SUBT.LANGUAGE
267 from AMS_MEDIA_TL SUBB, AMS_MEDIA_TL SUBT
268 where SUBB.MEDIA_ID = SUBT.MEDIA_ID
269 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
270 and (SUBB.MEDIA_NAME <> SUBT.MEDIA_NAME
271 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
272 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
273 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
274 ));
275
276 insert into AMS_MEDIA_TL (
277 MEDIA_NAME,
278 DESCRIPTION,
279 MEDIA_ID,
280 LAST_UPDATE_DATE,
281 LAST_UPDATED_BY,
282 CREATION_DATE,
283 CREATED_BY,
284 LAST_UPDATE_LOGIN,
285 LANGUAGE,
286 SOURCE_LANG
287 ) select
288 B.MEDIA_NAME,
289 B.DESCRIPTION,
290 B.MEDIA_ID,
291 B.LAST_UPDATE_DATE,
292 B.LAST_UPDATED_BY,
293 B.CREATION_DATE,
294 B.CREATED_BY,
295 B.LAST_UPDATE_LOGIN,
296 L.LANGUAGE_CODE,
297 B.SOURCE_LANG
298 from AMS_MEDIA_TL B, FND_LANGUAGES L
299 where L.INSTALLED_FLAG in ('I', 'B')
300 and B.LANGUAGE = userenv('LANG')
301 and not exists
302 (select NULL
303 from AMS_MEDIA_TL T
304 where T.MEDIA_ID = B.MEDIA_ID
305 and T.LANGUAGE = L.LANGUAGE_CODE);
306 end ADD_LANGUAGE;
307
308
309 procedure TRANSLATE_ROW(
310 x_media_id in NUMBER
311 , x_media_name in VARCHAR2
312 , x_description in VARCHAR2
313 , x_owner in VARCHAR2
314 ) is
315 begin
316 update AMS_MEDIA_TL set
317 media_name = nvl(x_media_name, media_name),
318 description = nvl(x_description, description),
319 source_lang = userenv('LANG'),
320 last_update_date = sysdate,
321 last_updated_by = decode(x_owner, 'SEED', 1, 0),
322 last_update_login = 0
323 where media_id = x_media_id
324 and userenv('LANG') in (language, source_lang);
325 end TRANSLATE_ROW;
326
327 procedure LOAD_ROW(
328 X_MEDIA_ID IN NUMBER,
329 X_MEDIA_TYPE_CODE in VARCHAR2 DEFAULT NULL,
330 X_INBOUND_FLAG in VARCHAR2 DEFAULT 'N',
331 X_ENABLED_FLAG in VARCHAR2 DEFAULT 'Y',
332 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
333 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL ,
334 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
335 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
336 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
337 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
338 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
339 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
340 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
341 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
342 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
343 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
344 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
345 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
346 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
347 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
348 X_DEDUPE_RULE_ID in VARCHAR2 DEFAULT NULL,
349 X_MEDIA_NAME in VARCHAR2 DEFAULT NULL,
350 X_DESCRIPTION in VARCHAR2 DEFAULT NULL ,
351 X_Owner VARCHAR2
352 ) is
353
354 l_user_id number := 0;
355 l_obj_verno number;
356 l_dummy_char varchar2(1);
357 l_row_id varchar2(100);
358 l_media_id number;
359 l_db_luby_id NUMBER;
360
361 cursor c_db_data_details is
362 select last_updated_by, nvl(object_version_number,1)
363 from AMS_MEDIA_B
364 where media_id = X_MEDIA_ID;
365
366 cursor c_chk_mda_exists is
367 select 'x'
368 from AMS_MEDIA_B
369 where media_id = X_MEDIA_ID;
370
371 cursor c_get_mdaid is
372 select AMS_MEDIA_B_S.nextval
373 from dual;
374
375 BEGIN
376
377 if X_OWNER = 'SEED' then
378 l_user_id := 1;
379 end if;
380
381 open c_chk_mda_exists;
382 fetch c_chk_mda_exists into l_dummy_char;
383 if c_chk_mda_exists%notfound
384 then
385 close c_chk_mda_exists;
386 if X_MEDIA_ID is null
387 then
388 open c_get_mdaid;
389 fetch c_get_mdaid into l_media_id;
390 close c_get_mdaid;
391 else
392 l_media_id := X_MEDIA_ID;
393 end if;
394 l_obj_verno := 1;
395 AMS_MEDIA_PKG.INSERT_ROW(
396 X_ROWID => l_row_id,
397 X_MEDIA_ID => l_media_id,
398 X_OBJECT_VERSION_NUMBER => l_obj_verno,
399 X_MEDIA_TYPE_CODE => X_MEDIA_TYPE_CODE,
400 X_INBOUND_FLAG => X_INBOUND_FLAG,
401 X_ENABLED_FLAG => X_ENABLED_FLAG,
402 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
403 X_ATTRIBUTE1 => X_ATTRIBUTE1,
404 X_ATTRIBUTE2 => X_ATTRIBUTE2,
405 X_ATTRIBUTE3 => X_ATTRIBUTE3,
406 X_ATTRIBUTE4 => X_ATTRIBUTE4,
407 X_ATTRIBUTE5 => X_ATTRIBUTE5,
408 X_ATTRIBUTE6 => X_ATTRIBUTE6,
409 X_ATTRIBUTE7 => X_ATTRIBUTE7,
410 X_ATTRIBUTE8 => X_ATTRIBUTE8,
411 X_ATTRIBUTE9 => X_ATTRIBUTE9,
412 X_ATTRIBUTE10 => X_ATTRIBUTE10,
413 X_ATTRIBUTE11 => X_ATTRIBUTE11,
414 X_ATTRIBUTE12 => X_ATTRIBUTE12,
415 X_ATTRIBUTE13 => X_ATTRIBUTE13,
416 X_ATTRIBUTE14 => X_ATTRIBUTE14,
417 X_ATTRIBUTE15 => X_ATTRIBUTE15,
418 X_DEDUPE_RULE_ID => X_DEDUPE_RULE_ID,
419 X_MEDIA_NAME => X_MEDIA_NAME,
420 X_DESCRIPTION => X_DESCRIPTION,
421 X_CREATION_DATE => SYSDATE,
422 X_CREATED_BY => l_user_id,
423 X_LAST_UPDATE_DATE => SYSDATE,
424 X_LAST_UPDATED_BY => l_user_id,
425 X_LAST_UPDATE_LOGIN => 0
426 );
427 else
428 close c_chk_mda_exists;
429 open c_db_data_details;
430 fetch c_db_data_details into l_db_luby_id,l_obj_verno;
431 close c_db_data_details;
432
433 if ( l_db_luby_id IN (1, 2, 0)) then
434 AMS_MEDIA_PKG.UPDATE_ROW(
435 X_MEDIA_ID => X_MEDIA_ID,
436 X_OBJECT_VERSION_NUMBER => l_obj_verno + 1,
437 X_MEDIA_TYPE_CODE => X_MEDIA_TYPE_CODE,
438 X_INBOUND_FLAG => X_INBOUND_FLAG,
439 X_ENABLED_FLAG => X_ENABLED_FLAG,
440 X_ATTRIBUTE_CATEGORY => X_ATTRIBUTE_CATEGORY,
441 X_ATTRIBUTE1 => X_ATTRIBUTE1,
442 X_ATTRIBUTE2 => X_ATTRIBUTE2,
443 X_ATTRIBUTE3 => X_ATTRIBUTE3,
444 X_ATTRIBUTE4 => X_ATTRIBUTE4,
445 X_ATTRIBUTE5 => X_ATTRIBUTE5,
446 X_ATTRIBUTE6 => X_ATTRIBUTE6,
447 X_ATTRIBUTE7 => X_ATTRIBUTE7,
448 X_ATTRIBUTE8 => X_ATTRIBUTE8,
449 X_ATTRIBUTE9 => X_ATTRIBUTE9,
450 X_ATTRIBUTE10 => X_ATTRIBUTE10,
451 X_ATTRIBUTE11 => X_ATTRIBUTE11,
452 X_ATTRIBUTE12 => X_ATTRIBUTE12,
453 X_ATTRIBUTE13 => X_ATTRIBUTE13,
454 X_ATTRIBUTE14 => X_ATTRIBUTE14,
455 X_ATTRIBUTE15 => X_ATTRIBUTE15,
456 X_DEDUPE_RULE_ID => X_DEDUPE_RULE_ID,
457 X_MEDIA_NAME => X_MEDIA_NAME,
458 X_DESCRIPTION => X_DESCRIPTION,
459 X_LAST_UPDATE_DATE => SYSDATE,
460 X_LAST_UPDATED_BY => l_user_id,
461 X_LAST_UPDATE_LOGIN => 0
462 );
463 end if;
464 end if;
465 END LOAD_ROW;
466
467 end AMS_MEDIA_PKG;