[Home] [Help]
PACKAGE BODY: APPS.FUN_RICH_MESSAGES_PKG
Source
1 package BODY FUN_RICH_MESSAGES_PKG as
2 /* $Header: FUNXTMRULRTMTBB.pls 120.0 2005/06/20 04:30:09 ammishra noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out nocopy VARCHAR2,
6 X_APPLICATION_ID in NUMBER,
7 X_MESSAGE_NAME in VARCHAR2,
8 X_CREATED_BY_MODULE in VARCHAR2,
9 X_MESSAGE_TEXT in CLOB
10 ) is
11 cursor C is select ROWID from FUN_RICH_MESSAGES_B
12 where APPLICATION_ID = X_APPLICATION_ID
13 and MESSAGE_NAME = X_MESSAGE_NAME;
14 begin
15 insert into FUN_RICH_MESSAGES_B (
16 APPLICATION_ID,
17 MESSAGE_NAME,
18 OBJECT_VERSION_NUMBER,
19 CREATED_BY_MODULE,
20 CREATION_DATE,
21 CREATED_BY,
22 LAST_UPDATE_DATE,
23 LAST_UPDATED_BY,
24 LAST_UPDATE_LOGIN
25 ) values (
26 X_APPLICATION_ID,
27 X_MESSAGE_NAME,
28 1,
29 X_CREATED_BY_MODULE,
30 FUN_RULE_UTILITY_PKG.CREATION_DATE,
31 FUN_RULE_UTILITY_PKG.CREATED_BY,
32 FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
33 FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY,
34 FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN
35 );
36
37 insert into FUN_RICH_MESSAGES_TL (
38 APPLICATION_ID,
39 MESSAGE_NAME,
40 MESSAGE_TEXT,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN,
46 LANGUAGE,
47 SOURCE_LANG
48 ) select
49 X_APPLICATION_ID,
50 X_MESSAGE_NAME,
51 X_MESSAGE_TEXT,
52 FUN_RULE_UTILITY_PKG.CREATION_DATE,
53 FUN_RULE_UTILITY_PKG.CREATED_BY,
54 FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
55 FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY,
56 FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN,
57 L.LANGUAGE_CODE,
58 userenv('LANG')
59 from FND_LANGUAGES L
60 where L.INSTALLED_FLAG in ('I', 'B')
61 and not exists
62 (select NULL
63 from FUN_RICH_MESSAGES_TL T
64 where T.APPLICATION_ID = X_APPLICATION_ID
65 and T.MESSAGE_NAME = X_MESSAGE_NAME
66 and T.LANGUAGE = L.LANGUAGE_CODE);
67
68 open c;
69 fetch c into X_ROWID;
70 if (c%notfound) then
71 close c;
72 raise no_data_found;
73 end if;
74 close c;
75
76 end INSERT_ROW;
77
78
79 procedure LOCK_ROW (
80 X_APPLICATION_ID in NUMBER,
81 X_MESSAGE_NAME in VARCHAR2,
82 X_OBJECT_VERSION_NUMBER in NUMBER
83 ) is
84
85 cursor c is select
86 OBJECT_VERSION_NUMBER
87 from FUN_RICH_MESSAGES_B
88 where APPLICATION_ID = X_APPLICATION_ID
89 and MESSAGE_NAME = X_MESSAGE_NAME
90 for update of APPLICATION_ID nowait;
91 recinfo c%rowtype;
92
93 begin
94 open c;
95 fetch c into recinfo;
96 if (c%notfound) then
97 close c;
98 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
99 app_exception.raise_exception;
100 end if;
101 close c;
102 if (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
103 then
104 null;
105 else
106 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
107 app_exception.raise_exception;
108 end if;
109 return;
110 end LOCK_ROW;
111
112
113 procedure UPDATE_ROW (
114 X_APPLICATION_ID in NUMBER,
115 X_MESSAGE_NAME in VARCHAR2,
116 X_CREATED_BY_MODULE in VARCHAR2,
117 X_MESSAGE_TEXT in CLOB
118 ) is
119 begin
120 update FUN_RICH_MESSAGES_B set
121 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER+1,
122 CREATED_BY_MODULE = X_CREATED_BY_MODULE,
123 LAST_UPDATE_DATE = FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
124 LAST_UPDATED_BY = FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY,
125 LAST_UPDATE_LOGIN = FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN
126 where APPLICATION_ID = X_APPLICATION_ID
127 and MESSAGE_NAME = X_MESSAGE_NAME;
128
129 if (sql%notfound) then
130 raise no_data_found;
131 end if;
132
133 update FUN_RICH_MESSAGES_TL set
134 MESSAGE_TEXT = X_MESSAGE_TEXT,
135 LAST_UPDATE_DATE = FUN_RULE_UTILITY_PKG.LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = FUN_RULE_UTILITY_PKG.LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = FUN_RULE_UTILITY_PKG.LAST_UPDATE_LOGIN,
138 SOURCE_LANG = userenv('LANG')
139 where APPLICATION_ID = X_APPLICATION_ID
140 and MESSAGE_NAME = X_MESSAGE_NAME
141 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
142
143 if (sql%notfound) then
144 raise no_data_found;
145 end if;
146 end UPDATE_ROW;
147
148
149 procedure DELETE_ROW (
150 X_APPLICATION_ID in NUMBER,
151 X_MESSAGE_NAME in VARCHAR2
152 ) is
153 begin
154 delete from FUN_RICH_MESSAGES_B
155 where APPLICATION_ID = X_APPLICATION_ID
156 and MESSAGE_NAME = X_MESSAGE_NAME;
157
158 if (sql%notfound) then
159 raise no_data_found;
160 end if;
161
162 delete from FUN_RICH_MESSAGES_TL
163 where APPLICATION_ID = X_APPLICATION_ID
164 and MESSAGE_NAME = X_MESSAGE_NAME;
165
166 if (sql%notfound) then
167 raise no_data_found;
168 end if;
169 end DELETE_ROW;
170
171
172
173 procedure SELECT_ROW (
174 X_APPLICATION_ID in out nocopy NUMBER,
175 X_MESSAGE_NAME in out nocopy VARCHAR2,
176 X_CREATED_BY_MODULE out nocopy VARCHAR2,
177 X_MESSAGE_TEXT out nocopy CLOB
178 ) is
179 begin
180 SELECT created_by_module, message_text
181 INTO x_created_by_module, x_message_text
182 FROM fun_rich_messages_vl;
183 end SELECT_ROW;
184
185
186 procedure ADD_LANGUAGE
187 is
188 begin
189 delete from FUN_RICH_MESSAGES_TL T
190 where not exists
191 (select NULL
192 from FUN_RICH_MESSAGES_B B
193 where B.APPLICATION_ID = T.APPLICATION_ID
194 and B.MESSAGE_NAME = T.MESSAGE_NAME
195 );
196
197 update FUN_RICH_MESSAGES_TL T set
198 ( MESSAGE_TEXT )
199 = (select B.MESSAGE_TEXT
200 from FUN_RICH_MESSAGES_TL B
201 where B.APPLICATION_ID = T.APPLICATION_ID
202 and B.MESSAGE_NAME = T.MESSAGE_NAME
203 and B.LANGUAGE = T.SOURCE_LANG)
204 where (
205 T.APPLICATION_ID,
206 T.MESSAGE_NAME,
207 T.LANGUAGE
208 ) in (
209 select SUBT.APPLICATION_ID,
210 SUBT.MESSAGE_NAME,
211 SUBT.LANGUAGE
212 from FUN_RICH_MESSAGES_TL SUBB, FUN_RICH_MESSAGES_TL SUBT
213 where SUBB.APPLICATION_ID = SUBT.APPLICATION_ID
214 and SUBB.MESSAGE_NAME = SUBT.MESSAGE_NAME
215 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
216 -- and SUBB.MESSAGE_TEXT <> SUBT.MESSAGE_TEXT
217 );
218
219
220 insert into FUN_RICH_MESSAGES_TL (
221 APPLICATION_ID,
222 MESSAGE_NAME,
223 MESSAGE_TEXT,
224 CREATION_DATE,
225 CREATED_BY,
226 LAST_UPDATE_DATE,
227 LAST_UPDATED_BY,
228 LAST_UPDATE_LOGIN,
229 LANGUAGE,
230 SOURCE_LANG
231 ) select /*+ ORDERED */
232 B.APPLICATION_ID,
233 B.MESSAGE_NAME,
234 B.MESSAGE_TEXT,
235 B.CREATION_DATE,
236 B.CREATED_BY,
237 B.LAST_UPDATE_DATE,
238 B.LAST_UPDATED_BY,
239 B.LAST_UPDATE_LOGIN,
240 L.LANGUAGE_CODE,
241 B.SOURCE_LANG
242 from FUN_RICH_MESSAGES_TL B, FND_LANGUAGES L
243 where L.INSTALLED_FLAG in ('I', 'B')
244 and B.LANGUAGE = userenv('LANG')
245 and not exists
246 (select NULL
247 from FUN_RICH_MESSAGES_TL T
248 where T.APPLICATION_ID = B.APPLICATION_ID
249 and T.MESSAGE_NAME = B.MESSAGE_NAME
250 and T.LANGUAGE = L.LANGUAGE_CODE);
251 end ADD_LANGUAGE;
252
253
254 PROCEDURE TRANSLATE_ROW(
255 X_APP_SHORT_NAME in VARCHAR2,
256 X_MESSAGE_NAME in VARCHAR2,
257 X_OWNER in VARCHAR2,
258 X_MESSAGE_TEXT in CLOB,
259 X_CUSTOM_MODE in VARCHAR2,
260 X_LAST_UPDATE_DATE in VARCHAR2
261 )
262 IS
263 appid number;
264
265 f_luby number; -- entity owner in file
266 f_ludate date; -- entity update date in file
267 db_luby number; -- entity owner in db
268 db_ludate date; -- entity update date in db
269 BEGIN
270 SELECT application_id INTO appid
271 FROM fnd_application
272 WHERE application_short_name = X_APP_SHORT_NAME;
273
274 select last_updated_by, last_update_date
275 into db_luby, db_ludate
276 from FUN_RICH_MESSAGES_TL
277 where application_id = appid
278 and message_name = x_message_name
279 and language = userenv('LANG');
280
281 -- c. owners are the same, and file_date > db_date
282 if (fnd_load_util.UPLOAD_TEST(
283 p_file_id => f_luby,
284 p_file_lud => f_ludate,
285 p_db_id => db_luby,
286 p_db_lud => db_ludate,
287 p_custom_mode => x_custom_mode))
288 then
289 update FUN_RICH_MESSAGES_TL
290 set message_text = nvl(x_message_text, message_text)
291 where application_id = appid
292 and message_name = x_message_name
293 and language = userenv('LANG');
294 end if;
295 END TRANSLATE_ROW;
296
297
298
299 procedure LOAD_ROW (
300 X_APP_SHORT_NAME in VARCHAR2,
301 X_MESSAGE_NAME in VARCHAR2,
302 X_MESSAGE_TEXT in CLOB,
303 X_OWNER IN VARCHAR2,
304 X_LAST_UPDATE_DATE IN VARCHAR2,
305 X_CUSTOM_MODE IN VARCHAR2)
306 is
307 appid number;
308
309 row_id varchar2(64);
310 f_luby number; -- entity owner in file
311 f_ludate date; -- entity update date in file
312 db_luby number; -- entity owner in db
313 db_ludate date; -- entity update date in db
314
315 roid number;
316 begin
317 SELECT application_id INTO appid
318 FROM fnd_application
319 WHERE application_short_name = X_APP_SHORT_NAME;
320
321 -- Translate owner to file_last_updated_by
322 f_luby := fnd_load_util.owner_id(x_owner);
323
324 -- Translate char last_update_date to date
325 f_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
326
327 select LAST_UPDATED_BY, LAST_UPDATE_DATE
328 into db_luby, db_ludate
329 from FUN_RICH_MESSAGES_TL
330 where APPLICATION_ID = appid
331 and MESSAGE_NAME = X_MESSAGE_NAME;
332
333
334 if (fnd_load_util.UPLOAD_TEST(
335 p_file_id => f_luby,
336 p_file_lud => f_ludate,
337 p_db_id => db_luby,
338 p_db_lud => db_ludate,
339 p_custom_mode => x_custom_mode))
340 then
341 UPDATE_ROW (
342 appid,
343 X_MESSAGE_NAME,
344 'ORACLE',
345 X_MESSAGE_TEXT);
346 end if;
347
348 EXCEPTION
349 WHEN NO_DATA_FOUND THEN
350 INSERT_ROW (
351 row_id,
352 appid,
353 X_MESSAGE_NAME,
354 'ORACLE',
355 X_MESSAGE_TEXT);
356 end LOAD_ROW;
357
358 end FUN_RICH_MESSAGES_PKG;