[Home] [Help]
PACKAGE BODY: APPS.CSM_NEW_MESSAGES_PKG
Source
1 PACKAGE BODY CSM_NEW_MESSAGES_PKG AS
2 /* $Header: csmlnmgb.pls 120.2 2006/05/09 05:52:12 utekumal noship $ */
3
4 PROCEDURE INSERT_ROW (
5 X_MESSAGE_ID NUMBER,
6 X_MESSAGE_NAME VARCHAR2,
7 X_MESSAGE_TYPE VARCHAR2,
8 X_MESSAGE_LENGTH NUMBER,
9 X_UPDATABLE VARCHAR2,
10 X_MESSAGE_TEXT VARCHAR2,
11 X_DESCRIPTION VARCHAR2,
12 X_OWNER VARCHAR2
13 )
14 IS
15
16 BEGIN
17 --Insert into base table
18 INSERT INTO CSM_NEW_MESSAGES
19 (MESSAGE_ID,
20 MESSAGE_NAME,
21 MESSAGE_TYPE,
22 MESSAGE_LENGTH,
23 UPDATABLE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY)
28 VALUES(X_MESSAGE_ID,
29 X_MESSAGE_NAME,
30 X_MESSAGE_TYPE,
31 X_MESSAGE_LENGTH,
32 X_UPDATABLE,
33 SYSDATE,
34 DECODE(X_OWNER,'SEED',1,0),
35 SYSDATE,
36 DECODE(X_OWNER,'SEED',1,0)
37 );
38
39
40 --Insert into TL table
41 INSERT INTO CSM_NEW_MESSAGES_TL
42 (MESSAGE_ID,
43 MESSAGE_NAME,
44 MESSAGE_TEXT,
45 DESCRIPTION,
46 LANGUAGE,
47 SOURCE_LANGUAGE,
48 CREATION_DATE,
49 CREATED_BY,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY)
52 SELECT X_MESSAGE_ID,
53 X_MESSAGE_NAME,
54 X_MESSAGE_TEXT,
55 X_DESCRIPTION,
56 L.LANGUAGE_CODE,
57 userenv('LANG'),
58 SYSDATE,
59 DECODE(X_OWNER,'SEED',1,0),
60 SYSDATE,
61 DECODE(X_OWNER,'SEED',1,0)
62 FROM FND_LANGUAGES L
63 WHERE L.INSTALLED_FLAG in ('I', 'B')
64 AND NOT EXISTS
65 (SELECT NULL
66 FROM CSM_NEW_MESSAGES_TL T
67 WHERE T.MESSAGE_NAME = X_MESSAGE_NAME);
68
69
70 END INSERT_ROW;
71
72 PROCEDURE ADD_LANGUAGE
73 IS
74 BEGIN
75 --deleting obsolete TL records
76 DELETE FROM CSM_NEW_MESSAGES_TL TL
77 WHERE NOT EXISTS (SELECT 1 FROM CSM_NEW_MESSAGES B
78 WHERE B.MESSAGE_ID=TL.MESSAGE_ID);
79
80 --no need for TL table update since we are taking care of it in UPDATE_ROW api itself.
81
82
83 --insert TL records for new language
84 INSERT INTO CSM_NEW_MESSAGES_TL
85 (MESSAGE_ID,
86 MESSAGE_NAME,
87 MESSAGE_TEXT,
88 DESCRIPTION,
89 LANGUAGE,
90 SOURCE_LANGUAGE,
91 CREATION_DATE,
92 CREATED_BY,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY)
95 SELECT B.MESSAGE_ID,
96 B.MESSAGE_NAME,
97 B.MESSAGE_TEXT,
98 B.DESCRIPTION,
99 L.LANGUAGE_CODE,
100 B.SOURCE_LANGUAGE,
101 B.CREATION_DATE,
102 B.CREATED_BY,
103 B.LAST_UPDATE_DATE,
104 B.LAST_UPDATED_BY
105 FROM CSM_NEW_MESSAGES_TL B,
106 FND_LANGUAGES L
107 WHERE B.LANGUAGE = userenv('LANG')
108 AND L.INSTALLED_FLAG in ('I', 'B')
109 AND NOT EXISTS
110 (SELECT NULL
111 FROM CSM_NEW_MESSAGES_TL T
112 WHERE T.MESSAGE_NAME = B.MESSAGE_NAME
113 AND T.LANGUAGE = L.LANGUAGE_CODE);
114
115 END ADD_LANGUAGE;
116
117 PROCEDURE UPDATE_ROW(
118 X_MESSAGE_NAME VARCHAR2,
119 X_MESSAGE_TYPE VARCHAR2,
120 X_MESSAGE_LENGTH NUMBER,
121 X_UPDATABLE VARCHAR2,
122 X_MESSAGE_TEXT VARCHAR2,
123 X_DESCRIPTION VARCHAR2,
124 X_OWNER VARCHAR2
125 )
126
127 IS
128
129 BEGIN
130 --Update base Table
131 UPDATE CSM_NEW_MESSAGES
132 SET MESSAGE_TYPE = X_MESSAGE_TYPE,
133 MESSAGE_LENGTH = X_MESSAGE_LENGTH,
134 UPDATABLE = X_UPDATABLE,
135 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0),
136 LAST_UPDATE_DATE = SYSDATE
137 WHERE MESSAGE_NAME = X_MESSAGE_NAME;
138
139 --Update TL Table
140 UPDATE CSM_NEW_MESSAGES_TL
141 SET MESSAGE_TEXT = X_MESSAGE_TEXT,
142 DESCRIPTION = X_DESCRIPTION,
143 SOURCE_LANGUAGE = userenv('LANG'),
144 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0),
145 LAST_UPDATE_DATE = SYSDATE
146 WHERE MESSAGE_NAME = X_MESSAGE_NAME
147 AND userenv('LANG') in (LANGUAGE, SOURCE_LANGUAGE);
148
149 END UPDATE_ROW;
150
151
152 PROCEDURE LOAD_ROW(
153 X_MESSAGE_NAME VARCHAR2,
154 X_MESSAGE_TYPE VARCHAR2,
155 X_MESSAGE_LENGTH NUMBER,
156 X_UPDATABLE VARCHAR2,
157 X_MESSAGE_TEXT VARCHAR2,
158 X_DESCRIPTION VARCHAR2,
159 X_OWNER VARCHAR2
160 )
161 IS
162
163 CURSOR c_message_exists(b_message_name VARCHAR2) IS
164 SELECT 1
165 FROM CSM_NEW_MESSAGES CNM
166 WHERE CNM.MESSAGE_NAME = b_message_name;
167
168 l_exists NUMBER;
169
170 X_MESSAGE_ID NUMBER;
171
172 BEGIN
173
174 OPEN c_message_exists(X_MESSAGE_NAME);
175 FETCH c_message_exists INTO l_exists;
176 CLOSE c_message_exists;
177
178 IF l_exists IS NULL THEN
179
180 SELECT CSM_NEW_MESSAGES_S.NEXTVAL into X_MESSAGE_ID FROM dual;
181
182 Insert_Row(
183 X_MESSAGE_ID,
184 X_MESSAGE_NAME,
185 X_MESSAGE_TYPE,
186 X_MESSAGE_LENGTH,
187 X_UPDATABLE,
188 X_MESSAGE_TEXT,
189 X_DESCRIPTION,
190 X_OWNER );
191
192
193 ELSE
194 Update_Row(
195 X_MESSAGE_NAME,
196 X_MESSAGE_TYPE,
197 X_MESSAGE_LENGTH,
198 X_UPDATABLE,
199 X_MESSAGE_TEXT,
200 X_DESCRIPTION,
201 X_OWNER );
202
203 END IF;
204
205
206 END LOAD_ROW;
207
208
209 PROCEDURE TRANSLATE_ROW(
210 X_MESSAGE_NAME VARCHAR2,
211 X_MESSAGE_TYPE VARCHAR2,
212 X_MESSAGE_LENGTH NUMBER,
213 X_UPDATABLE VARCHAR2,
214 X_MESSAGE_TEXT VARCHAR2,
215 X_DESCRIPTION VARCHAR2,
216 X_OWNER VARCHAR2
217 )
218 IS
219
220 CURSOR c_message_exists(b_message_name VARCHAR2) IS
221 SELECT 1
222 FROM CSM_NEW_MESSAGES CNM
223 WHERE CNM.MESSAGE_NAME = b_message_name;
224
225 l_exists NUMBER;
226
227 BEGIN
228
229 OPEN c_message_exists(X_MESSAGE_NAME);
230 FETCH c_message_exists INTO l_exists;
231 CLOSE c_message_exists;
232
233 IF l_exists IS NOT NULL THEN
234
235 UPDATE CSM_NEW_MESSAGES_TL SET
236 MESSAGE_TEXT = nvl(X_MESSAGE_TEXT, MESSAGE_TEXT),
237 DESCRIPTION = nvl(X_DESCRIPTION, DESCRIPTION),
238 LAST_UPDATE_DATE = SYSDATE,
239 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0),
240 SOURCE_LANGUAGE = userenv('LANG')
241 WHERE MESSAGE_NAME = X_MESSAGE_NAME
242 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANGUAGE);
243
244 END IF;
245
246 END TRANSLATE_ROW;
247
248
249 PROCEDURE INSERT_ROW_PERZ (
250 X_MESSAGE_ID NUMBER,
251 X_MESSAGE_NAME VARCHAR2,
252 X_LEVEL_ID NUMBER,
253 X_LEVEL_VALUE NUMBER,
254 X_LANGUAGE VARCHAR2,
255 X_MESSAGE_TEXT VARCHAR2,
256 X_DESCRIPTION VARCHAR2,
257 X_OWNER VARCHAR2
258 )
259
260 IS
261
262 errmsg varchar2(4000);
263
264 BEGIN
265 --Insert into Perz table
266 INSERT INTO CSM_NEW_MESSAGES_PERZ
267 (MESSAGE_ID,
268 MESSAGE_NAME,
269 LEVEL_ID,
270 LEVEL_VALUE,
271 LANGUAGE,
272 MESSAGE_TEXT,
273 DESCRIPTION,
274 CREATION_DATE,
275 CREATED_BY,
276 LAST_UPDATE_DATE,
277 LAST_UPDATED_BY)
278 VALUES(X_MESSAGE_ID,
279 X_MESSAGE_NAME,
280 X_LEVEL_ID,
281 X_LEVEL_VALUE,
282 X_LANGUAGE,
283 X_MESSAGE_TEXT,
284 X_DESCRIPTION,
285 SYSDATE,
286 DECODE(X_OWNER,'SEED',1,0),
287 SYSDATE,
288 DECODE(X_OWNER,'SEED',1,0)
289 );
290
291
292 END INSERT_ROW_PERZ;
293
294
295 PROCEDURE UPDATE_ROW_PERZ(
296 X_MESSAGE_NAME VARCHAR2,
297 X_LEVEL_ID NUMBER,
298 X_LEVEL_VALUE NUMBER,
299 X_LANGUAGE VARCHAR2,
300 X_MESSAGE_TEXT VARCHAR2,
301 X_DESCRIPTION VARCHAR2,
302 X_OWNER VARCHAR2
303 )
304
305 IS
306
307 BEGIN
308 --Update base Table
309 UPDATE CSM_NEW_MESSAGES_PERZ
310 SET MESSAGE_TEXT = X_MESSAGE_TEXT,
311 DESCRIPTION = X_DESCRIPTION,
312 LAST_UPDATED_BY = DECODE(X_OWNER,'SEED',1,0),
313 LAST_UPDATE_DATE = SYSDATE
314 WHERE MESSAGE_NAME = X_MESSAGE_NAME
315 AND LEVEL_ID = X_LEVEL_ID
316 AND LEVEL_VALUE = X_LEVEL_VALUE
317 AND LANGUAGE = X_LANGUAGE;
318
319 END UPDATE_ROW_PERZ;
320
321
322 PROCEDURE LOAD_ROW_PERZ(
323 X_MESSAGE_NAME VARCHAR2,
324 X_LEVEL_ID NUMBER,
325 X_LEVEL_VALUE NUMBER,
326 X_LANGUAGE VARCHAR2,
327 X_MESSAGE_TEXT VARCHAR2,
328 X_DESCRIPTION VARCHAR2,
329 X_OWNER VARCHAR2
330 )
331 IS
332
333 CURSOR c_message_exists(b_message_name VARCHAR2, b_level_id NUMBER, b_level_value NUMBER, b_language VARCHAR2) IS
334 SELECT 1
335 FROM CSM_NEW_MESSAGES_PERZ PERZ
336 WHERE PERZ.MESSAGE_NAME = b_message_name
337 AND PERZ.LEVEL_ID = b_level_id
338 AND PERZ.LEVEL_VALUE = b_level_value
339 AND PERZ.LANGUAGE = b_language;
340
341
342 CURSOR c_get_msg_id(b_message_name VARCHAR2) IS
343 SELECT MESSAGE_ID
344 FROM CSM_NEW_MESSAGES CNM
345 WHERE CNM.MESSAGE_NAME = b_message_name;
346
347 l_exists NUMBER;
348
349 X_MESSAGE_ID NUMBER;
350
351 BEGIN
352
353 OPEN c_message_exists(X_MESSAGE_NAME, X_LEVEL_ID, X_LEVEL_VALUE, X_LANGUAGE);
354 FETCH c_message_exists INTO l_exists;
355 CLOSE c_message_exists;
356
357 OPEN c_get_msg_id(X_MESSAGE_NAME);
358 FETCH c_get_msg_id INTO X_MESSAGE_ID;
359 CLOSE c_get_msg_id;
360
361 IF X_MESSAGE_ID IS NULL THEN
362 RETURN;
363 END IF;
364
365 IF l_exists IS NULL THEN
366
367 INSERT_ROW_PERZ(
368 X_MESSAGE_ID,
369 X_MESSAGE_NAME,
370 X_LEVEL_ID,
371 X_LEVEL_VALUE,
372 X_LANGUAGE,
373 X_MESSAGE_TEXT,
374 X_DESCRIPTION,
375 X_OWNER );
376
377
378 ELSE
379 UPDATE_ROW_PERZ(
380 X_MESSAGE_NAME,
381 X_LEVEL_ID,
382 X_LEVEL_VALUE,
383 X_LANGUAGE,
384 X_MESSAGE_TEXT,
385 X_DESCRIPTION,
386 X_OWNER );
387 END IF;
388
389 END LOAD_ROW_PERZ;
390
391
392 END CSM_NEW_MESSAGES_PKG;