[Home] [Help]
PACKAGE BODY: APPS.AMS_MESSAGES_PKG
Source
1 package body AMS_MESSAGES_PKG as
2 /* $Header: amslmsgb.pls 115.4 2002/11/15 21:01:00 abhola ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in OUT NOCOPY VARCHAR2,
5 X_MESSAGE_ID in NUMBER,
6 X_MESSAGE_TYPE_CODE in VARCHAR2,
7 X_OWNER_USER_ID in NUMBER,
8 X_OBJECT_VERSION_NUMBER in NUMBER,
9 X_DATE_EFFECTIVE_FROM in DATE,
10 X_DATE_EFFECTIVE_TO in DATE,
11 X_ACTIVE_FLAG in VARCHAR2,
12 X_MESSAGE_NAME in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from AMS_MESSAGES_B
21 where MESSAGE_ID = X_MESSAGE_ID
22 ;
23 begin
24 insert into AMS_MESSAGES_B (
25 MESSAGE_TYPE_CODE,
26 OWNER_USER_ID,
27 MESSAGE_ID,
28 OBJECT_VERSION_NUMBER,
29 DATE_EFFECTIVE_FROM,
30 DATE_EFFECTIVE_TO,
31 ACTIVE_FLAG,
32 CREATION_DATE,
33 CREATED_BY,
34 LAST_UPDATE_DATE,
35 LAST_UPDATED_BY,
36 LAST_UPDATE_LOGIN
37 ) values (
38 X_MESSAGE_TYPE_CODE,
39 X_OWNER_USER_ID,
40 X_MESSAGE_ID,
41 X_OBJECT_VERSION_NUMBER,
42 X_DATE_EFFECTIVE_FROM,
43 X_DATE_EFFECTIVE_TO,
44 X_ACTIVE_FLAG,
45 X_CREATION_DATE,
46 X_CREATED_BY,
47 X_LAST_UPDATE_DATE,
48 X_LAST_UPDATED_BY,
49 X_LAST_UPDATE_LOGIN
50 );
51
52 insert into AMS_MESSAGES_TL (
53 DESCRIPTION,
54 MESSAGE_ID,
55 LAST_UPDATE_DATE,
56 LAST_UPDATED_BY,
57 CREATION_DATE,
58 CREATED_BY,
59 LAST_UPDATE_LOGIN,
60 MESSAGE_NAME,
61 LANGUAGE,
62 SOURCE_LANG
63 ) select
64 X_DESCRIPTION,
65 X_MESSAGE_ID,
66 X_LAST_UPDATE_DATE,
67 X_LAST_UPDATED_BY,
68 X_CREATION_DATE,
69 X_CREATED_BY,
70 X_LAST_UPDATE_LOGIN,
71 X_MESSAGE_NAME,
72 L.LANGUAGE_CODE,
73 userenv('LANG')
74 from FND_LANGUAGES L
75 where L.INSTALLED_FLAG in ('I', 'B')
76 and not exists
77 (select NULL
78 from AMS_MESSAGES_TL T
79 where T.MESSAGE_ID = X_MESSAGE_ID
80 and T.LANGUAGE = L.LANGUAGE_CODE);
81
82 open c;
83 fetch c into X_ROWID;
84 if (c%notfound) then
85 close c;
86 raise no_data_found;
87 end if;
88 close c;
89
90 end INSERT_ROW;
91
92 procedure LOCK_ROW (
93 X_MESSAGE_ID in NUMBER,
94 X_MESSAGE_TYPE_CODE in VARCHAR2,
95 X_OWNER_USER_ID in NUMBER,
96 X_OBJECT_VERSION_NUMBER in NUMBER,
97 X_DATE_EFFECTIVE_FROM in DATE,
98 X_DATE_EFFECTIVE_TO in DATE,
99 X_ACTIVE_FLAG in VARCHAR2,
100 X_MESSAGE_NAME in VARCHAR2,
101 X_DESCRIPTION in VARCHAR2
102 ) is
103 cursor c is select
104 MESSAGE_TYPE_CODE,
105 OWNER_USER_ID,
106 OBJECT_VERSION_NUMBER,
107 DATE_EFFECTIVE_FROM,
108 DATE_EFFECTIVE_TO,
109 ACTIVE_FLAG
110 from AMS_MESSAGES_B
111 where MESSAGE_ID = X_MESSAGE_ID
112 for update of MESSAGE_ID nowait;
113 recinfo c%rowtype;
114
115 cursor c1 is select
116 MESSAGE_NAME,
117 DESCRIPTION,
118 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
119 from AMS_MESSAGES_TL
120 where MESSAGE_ID = X_MESSAGE_ID
121 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
122 for update of MESSAGE_ID nowait;
123 begin
124 open c;
125 fetch c into recinfo;
126 if (c%notfound) then
127 close c;
128 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
129 app_exception.raise_exception;
130 end if;
131 close c;
132 if ( ((recinfo.MESSAGE_TYPE_CODE = X_MESSAGE_TYPE_CODE)
133 OR ((recinfo.MESSAGE_TYPE_CODE is null) AND (X_MESSAGE_TYPE_CODE is null)))
134 AND ((recinfo.OWNER_USER_ID = X_OWNER_USER_ID)
135 OR ((recinfo.OWNER_USER_ID is null) AND (X_OWNER_USER_ID is null)))
136 AND ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
137 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
138 AND ((recinfo.DATE_EFFECTIVE_FROM = X_DATE_EFFECTIVE_FROM)
139 OR ((recinfo.DATE_EFFECTIVE_FROM is null) AND (X_DATE_EFFECTIVE_FROM is null)))
140 AND ((recinfo.DATE_EFFECTIVE_TO = X_DATE_EFFECTIVE_TO)
141 OR ((recinfo.DATE_EFFECTIVE_TO is null) AND (X_DATE_EFFECTIVE_TO is null)))
142 AND (recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
143 ) then
144 null;
145 else
146 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
147 app_exception.raise_exception;
148 end if;
149
150 for tlinfo in c1 loop
151 if (tlinfo.BASELANG = 'Y') then
152 if ( (tlinfo.MESSAGE_NAME = X_MESSAGE_NAME)
153 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
154 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
155 ) then
156 null;
157 else
158 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159 app_exception.raise_exception;
160 end if;
161 end if;
162 end loop;
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 X_MESSAGE_ID in NUMBER,
168 X_MESSAGE_TYPE_CODE in VARCHAR2,
169 X_OWNER_USER_ID in NUMBER,
170 X_OBJECT_VERSION_NUMBER in NUMBER,
171 X_DATE_EFFECTIVE_FROM in DATE,
172 X_DATE_EFFECTIVE_TO in DATE,
173 X_ACTIVE_FLAG in VARCHAR2,
174 X_MESSAGE_NAME in VARCHAR2,
175 X_DESCRIPTION in VARCHAR2,
176 X_LAST_UPDATE_DATE in DATE,
177 X_LAST_UPDATED_BY in NUMBER,
178 X_LAST_UPDATE_LOGIN in NUMBER
179 ) is
180 begin
181 update AMS_MESSAGES_B set
182 MESSAGE_TYPE_CODE = X_MESSAGE_TYPE_CODE,
183 OWNER_USER_ID = X_OWNER_USER_ID,
184 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
185 DATE_EFFECTIVE_FROM = X_DATE_EFFECTIVE_FROM,
186 DATE_EFFECTIVE_TO = X_DATE_EFFECTIVE_TO,
187 ACTIVE_FLAG = X_ACTIVE_FLAG,
188 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
189 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
190 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
191 where MESSAGE_ID = X_MESSAGE_ID;
192
193 if (sql%notfound) then
194 raise no_data_found;
195 end if;
196
197 update AMS_MESSAGES_TL set
198 MESSAGE_NAME = X_MESSAGE_NAME,
199 DESCRIPTION = X_DESCRIPTION,
200 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
201 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
202 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
203 SOURCE_LANG = userenv('LANG')
204 where MESSAGE_ID = X_MESSAGE_ID
205 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
206
207 if (sql%notfound) then
208 raise no_data_found;
209 end if;
210 end UPDATE_ROW;
211
212 procedure DELETE_ROW (
213 X_MESSAGE_ID in NUMBER
214 ) is
215 begin
216 delete from AMS_MESSAGES_TL
217 where MESSAGE_ID = X_MESSAGE_ID;
218
219 if (sql%notfound) then
220 raise no_data_found;
221 end if;
222
223 delete from AMS_MESSAGES_B
224 where MESSAGE_ID = X_MESSAGE_ID;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229 end DELETE_ROW;
230
231 procedure ADD_LANGUAGE
232 is
233 begin
234 delete from AMS_MESSAGES_TL T
235 where not exists
236 (select NULL
237 from AMS_MESSAGES_B B
238 where B.MESSAGE_ID = T.MESSAGE_ID
239 );
240
241 update AMS_MESSAGES_TL T set (
242 MESSAGE_NAME,
243 DESCRIPTION
244 ) = (select
245 B.MESSAGE_NAME,
246 B.DESCRIPTION
247 from AMS_MESSAGES_TL B
248 where B.MESSAGE_ID = T.MESSAGE_ID
249 and B.LANGUAGE = T.SOURCE_LANG)
250 where (
251 T.MESSAGE_ID,
252 T.LANGUAGE
253 ) in (select
254 SUBT.MESSAGE_ID,
255 SUBT.LANGUAGE
256 from AMS_MESSAGES_TL SUBB, AMS_MESSAGES_TL SUBT
257 where SUBB.MESSAGE_ID = SUBT.MESSAGE_ID
258 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
259 and (SUBB.MESSAGE_NAME <> SUBT.MESSAGE_NAME
260 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
261 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
262 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
263 ));
264
265 insert into AMS_MESSAGES_TL (
266 DESCRIPTION,
267 MESSAGE_ID,
268 LAST_UPDATE_DATE,
269 LAST_UPDATED_BY,
270 CREATION_DATE,
271 CREATED_BY,
272 LAST_UPDATE_LOGIN,
273 MESSAGE_NAME,
274 LANGUAGE,
275 SOURCE_LANG
276 ) select
277 B.DESCRIPTION,
278 B.MESSAGE_ID,
279 B.LAST_UPDATE_DATE,
280 B.LAST_UPDATED_BY,
281 B.CREATION_DATE,
282 B.CREATED_BY,
283 B.LAST_UPDATE_LOGIN,
284 B.MESSAGE_NAME,
285 L.LANGUAGE_CODE,
286 B.SOURCE_LANG
287 from AMS_MESSAGES_TL B, FND_LANGUAGES L
288 where L.INSTALLED_FLAG in ('I', 'B')
289 and B.LANGUAGE = userenv('LANG')
290 and not exists
291 (select NULL
292 from AMS_MESSAGES_TL T
293 where T.MESSAGE_ID = B.MESSAGE_ID
294 and T.LANGUAGE = L.LANGUAGE_CODE);
295 end ADD_LANGUAGE;
296
297 procedure TRANSLATE_ROW(
298 X_MESSAGE_ID in NUMBER,
299 X_MESSAGE_NAME in VARCHAR2,
300 X_DESCRIPTION in VARCHAR2,
301 X_OWNER in VARCHAR2
302 )
303 is
304 begin
305 update ams_messages_tl set
306 message_name = nvl(x_message_name, message_name),
307 description = nvl(x_description, description),
308 source_lang = userenv('LANG'),
309 last_update_date = sysdate,
310 last_updated_by = decode(x_owner, 'SEED', 1, 0),
311 last_update_login = 0
312 where message_id = x_message_id
313 and userenv('LANG') in (language, source_lang);
314 end TRANSLATE_ROW;
315
316 procedure LOAD_ROW(
317 X_MESSAGE_ID in NUMBER,
318 X_MESSAGE_TYPE_CODE in VARCHAR2,
319 X_OWNER_USER_ID in NUMBER,
320 X_OBJECT_VERSION_NUMBER in NUMBER,
321 X_DATE_EFFECTIVE_FROM in DATE,
322 X_DATE_EFFECTIVE_TO in DATE,
323 X_ACTIVE_FLAG in VARCHAR2,
324 X_MESSAGE_NAME in VARCHAR2,
325 X_DESCRIPTION in VARCHAR2,
326 X_OWNER in VARCHAR2
327 )
328 is
329
330 l_user_id number := 0;
331 l_version number;
332 l_msg_id number;
333 l_dummy_char varchar2(1);
334 l_row_id varchar2(100);
335
336 cursor c_version is
337 select object_version_number
338 from ams_messages_b
339 where message_id = X_MESSAGE_ID;
340
341 cursor c_msg_exists is
342 select 'x'
343 from ams_messages_b
344 where message_id = X_MESSAGE_ID;
345
346 cursor c_msg_id is
347 select ams_messages_b_s.nextval
348 from dual;
349
350 BEGIN
351
352 open c_msg_exists;
353 fetch c_msg_exists into l_dummy_char;
354 if c_msg_exists%notfound then
355 close c_msg_exists;
356 if X_MESSAGE_ID is null then
357 open c_msg_id;
358 fetch c_msg_id into l_msg_id;
359 close c_msg_id;
360 else
361 l_msg_id := X_MESSAGE_ID;
362 end if;
363 l_version := 1;
364 AMS_MESSAGES_PKG.INSERT_ROW(
365 X_ROWID => l_row_id,
366 X_MESSAGE_ID => l_msg_id,
367 X_OBJECT_VERSION_NUMBER => l_version,
368 X_MESSAGE_TYPE_CODE => X_MESSAGE_TYPE_CODE,
369 X_OWNER_USER_ID => X_OWNER_USER_ID,
370 X_DATE_EFFECTIVE_FROM => X_DATE_EFFECTIVE_FROM,
371 X_DATE_EFFECTIVE_TO => X_DATE_EFFECTIVE_TO,
372 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
376 X_CREATED_BY => l_user_id,
373 X_MESSAGE_NAME => X_MESSAGE_NAME,
374 X_DESCRIPTION => X_DESCRIPTION,
375 X_CREATION_DATE => SYSDATE,
377 X_LAST_UPDATE_DATE => SYSDATE,
378 X_LAST_UPDATED_BY => l_user_id,
379 X_LAST_UPDATE_LOGIN => 0
380 );
381 else
382 close c_msg_exists;
383 open c_version;
384 fetch c_version into l_version;
385 close c_version;
386 AMS_MESSAGES_PKG.UPDATE_ROW(
387 X_MESSAGE_ID => X_MESSAGE_ID,
388 X_OBJECT_VERSION_NUMBER => l_version + 1,
389 X_MESSAGE_TYPE_CODE => X_MESSAGE_TYPE_CODE,
390 X_OWNER_USER_ID => X_OWNER_USER_ID,
391 X_DATE_EFFECTIVE_FROM => X_DATE_EFFECTIVE_FROM,
392 X_DATE_EFFECTIVE_TO => X_DATE_EFFECTIVE_TO,
393 X_ACTIVE_FLAG => X_ACTIVE_FLAG,
394 X_MESSAGE_NAME => X_MESSAGE_NAME,
395 X_DESCRIPTION => X_DESCRIPTION,
396 X_LAST_UPDATE_DATE => SYSDATE,
397 X_LAST_UPDATED_BY => l_user_id,
398 X_LAST_UPDATE_LOGIN => 0
399 );
400 end if;
401 END LOAD_ROW;
402
403 end AMS_MESSAGES_PKG;