[Home] [Help]
PACKAGE BODY: APPS.CLN_NOTIFICATION_CODES_PKG
Source
1 package body CLN_NOTIFICATION_CODES_PKG as
2 /* $Header: ECXNCTHB.pls 120.1 2005/08/26 07:01:03 nparihar noship $*/
3 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
4 procedure INSERT_ROW (
5 X_ROWID in OUT NOCOPY VARCHAR2,
6 X_NOTIFICATION_ID in NUMBER,
7 X_COLLABORATION_POINT in VARCHAR2,
8 X_NOTIFICATION_CODE in VARCHAR2,
9 X_NOTIFICATION_MESSAGE in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from CLN_NOTIFICATION_CODES_B
17 where NOTIFICATION_ID = X_NOTIFICATION_ID
18 ;
19 begin
20 insert into CLN_NOTIFICATION_CODES_B (
21 NOTIFICATION_ID,
22 COLLABORATION_POINT,
23 NOTIFICATION_CODE,
24 CREATION_DATE,
25 CREATED_BY,
26 LAST_UPDATE_DATE,
27 LAST_UPDATED_BY,
28 LAST_UPDATE_LOGIN
29 ) values (
30 X_NOTIFICATION_ID,
31 X_COLLABORATION_POINT,
32 X_NOTIFICATION_CODE,
33 X_CREATION_DATE,
34 X_CREATED_BY,
35 X_LAST_UPDATE_DATE,
36 X_LAST_UPDATED_BY,
37 X_LAST_UPDATE_LOGIN
38 );
39
40 insert into CLN_NOTIFICATION_CODES_TL (
41 NOTIFICATION_ID,
42 NOTIFICATION_MESSAGE,
43 CREATION_DATE,
44 CREATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATED_BY,
47 LAST_UPDATE_LOGIN,
48 LANGUAGE,
49 SOURCE_LANG
50 ) select
51 X_NOTIFICATION_ID,
52 X_NOTIFICATION_MESSAGE,
53 X_CREATION_DATE,
54 X_CREATED_BY,
55 X_LAST_UPDATE_DATE,
56 X_LAST_UPDATED_BY,
57 X_LAST_UPDATE_LOGIN,
58 L.LANGUAGE_CODE,
59 userenv('LANG')
60 from FND_LANGUAGES L
61 where L.INSTALLED_FLAG in ('I', 'B')
62 and not exists
63 (select NULL
64 from CLN_NOTIFICATION_CODES_TL T
65 where T.NOTIFICATION_ID = X_NOTIFICATION_ID
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 procedure LOCK_ROW (
79 X_NOTIFICATION_ID in NUMBER,
80 X_COLLABORATION_POINT in VARCHAR2,
81 X_NOTIFICATION_CODE in VARCHAR2,
82 X_NOTIFICATION_MESSAGE in VARCHAR2
83 ) is
84 cursor c is select
85 COLLABORATION_POINT,
86 NOTIFICATION_CODE
87 from CLN_NOTIFICATION_CODES_B
88 where NOTIFICATION_ID = X_NOTIFICATION_ID
89 for update of NOTIFICATION_ID nowait;
90 recinfo c%rowtype;
91
92 cursor c1 is select
93 NOTIFICATION_MESSAGE,
94 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
95 from CLN_NOTIFICATION_CODES_TL
96 where NOTIFICATION_ID = X_NOTIFICATION_ID
97 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
98 for update of NOTIFICATION_ID nowait;
99 begin
100 open c;
101 fetch c into recinfo;
102 if (c%notfound) then
103 close c;
104 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
105 app_exception.raise_exception;
106 end if;
107 close c;
108 if ( ((recinfo.COLLABORATION_POINT = X_COLLABORATION_POINT)
109 OR ((recinfo.COLLABORATION_POINT is null) AND (X_COLLABORATION_POINT is null)))
110 AND ((recinfo.NOTIFICATION_CODE = X_NOTIFICATION_CODE)
111 OR ((recinfo.NOTIFICATION_CODE is null) AND (X_NOTIFICATION_CODE is null)))
112 ) then
113 null;
114 else
115 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
116 app_exception.raise_exception;
117 end if;
118
119 for tlinfo in c1 loop
120 if (tlinfo.BASELANG = 'Y') then
121 if ( ((tlinfo.NOTIFICATION_MESSAGE = X_NOTIFICATION_MESSAGE)
122 OR ((tlinfo.NOTIFICATION_MESSAGE is null) AND (X_NOTIFICATION_MESSAGE is null)))
123 ) then
124 null;
125 else
126 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
127 app_exception.raise_exception;
128 end if;
129 end if;
130 end loop;
131 return;
132 end LOCK_ROW;
133
134 procedure UPDATE_ROW (
135 X_NOTIFICATION_ID in NUMBER,
136 X_COLLABORATION_POINT in VARCHAR2,
137 X_NOTIFICATION_CODE in VARCHAR2,
138 X_NOTIFICATION_MESSAGE in VARCHAR2,
139 X_LAST_UPDATE_DATE in DATE,
140 X_LAST_UPDATED_BY in NUMBER,
141 X_LAST_UPDATE_LOGIN in NUMBER
142 ) is
143 begin
144 update CLN_NOTIFICATION_CODES_B set
145 COLLABORATION_POINT = X_COLLABORATION_POINT,
146 NOTIFICATION_CODE = X_NOTIFICATION_CODE,
147 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
148 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
149 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
150 where NOTIFICATION_ID = X_NOTIFICATION_ID;
151
152 if (sql%notfound) then
153 raise no_data_found;
154 end if;
155
156 update CLN_NOTIFICATION_CODES_TL set
157 NOTIFICATION_MESSAGE = X_NOTIFICATION_MESSAGE,
158 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
159 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
160 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
161 SOURCE_LANG = userenv('LANG')
162 where NOTIFICATION_ID = X_NOTIFICATION_ID
163 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
164
165 if (sql%notfound) then
166 raise no_data_found;
167 end if;
168 end UPDATE_ROW;
169
170 procedure DELETE_ROW (
171 X_NOTIFICATION_ID in NUMBER
172 ) is
173 begin
174 delete from CLN_NOTIFICATION_CODES_TL
175 where NOTIFICATION_ID = X_NOTIFICATION_ID;
176
177 if (sql%notfound) then
178 raise no_data_found;
179 end if;
180
181 delete from CLN_NOTIFICATION_CODES_B
182 where NOTIFICATION_ID = X_NOTIFICATION_ID;
183
184 if (sql%notfound) then
185 raise no_data_found;
186 end if;
187 end DELETE_ROW;
188
189 procedure ADD_LANGUAGE
190 is
191 begin
192 delete from CLN_NOTIFICATION_CODES_TL T
193 where not exists
194 (select NULL
195 from CLN_NOTIFICATION_CODES_B B
196 where B.NOTIFICATION_ID = T.NOTIFICATION_ID
197 );
198
199 update CLN_NOTIFICATION_CODES_TL T set (
200 NOTIFICATION_MESSAGE
201 ) = (select
202 B.NOTIFICATION_MESSAGE
203 from CLN_NOTIFICATION_CODES_TL B
204 where B.NOTIFICATION_ID = T.NOTIFICATION_ID
205 and B.LANGUAGE = T.SOURCE_LANG)
206 where (
207 T.NOTIFICATION_ID,
208 T.LANGUAGE
209 ) in (select
210 SUBT.NOTIFICATION_ID,
211 SUBT.LANGUAGE
212 from CLN_NOTIFICATION_CODES_TL SUBB, CLN_NOTIFICATION_CODES_TL SUBT
213 where SUBB.NOTIFICATION_ID = SUBT.NOTIFICATION_ID
214 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
215 and (SUBB.NOTIFICATION_MESSAGE <> SUBT.NOTIFICATION_MESSAGE
216 or (SUBB.NOTIFICATION_MESSAGE is null and SUBT.NOTIFICATION_MESSAGE is not null)
217 or (SUBB.NOTIFICATION_MESSAGE is not null and SUBT.NOTIFICATION_MESSAGE is null)
218 ));
219
220 insert into CLN_NOTIFICATION_CODES_TL (
221 NOTIFICATION_ID,
222 NOTIFICATION_MESSAGE,
223 CREATION_DATE,
224 CREATED_BY,
225 LAST_UPDATE_DATE,
226 LAST_UPDATED_BY,
227 LAST_UPDATE_LOGIN,
228 LANGUAGE,
229 SOURCE_LANG
230 ) select
231 B.NOTIFICATION_ID,
232 B.NOTIFICATION_MESSAGE,
233 B.CREATION_DATE,
234 B.CREATED_BY,
235 B.LAST_UPDATE_DATE,
236 B.LAST_UPDATED_BY,
237 B.LAST_UPDATE_LOGIN,
238 L.LANGUAGE_CODE,
239 B.SOURCE_LANG
240 from CLN_NOTIFICATION_CODES_TL B, FND_LANGUAGES L
241 where L.INSTALLED_FLAG in ('I', 'B')
242 and B.LANGUAGE = userenv('LANG')
243 and not exists
244 (select NULL
245 from CLN_NOTIFICATION_CODES_TL T
246 where T.NOTIFICATION_ID = B.NOTIFICATION_ID
247 and T.LANGUAGE = L.LANGUAGE_CODE);
248 end ADD_LANGUAGE;
249
250 /*----------------------------------------------------------*/
251 /* Translate_row Procedure */
252 /*----------------------------------------------------------*/
253 PROCEDURE translate_row
254 (
255 X_NOTIFICATION_ID IN NUMBER ,
256 X_OWNER IN VARCHAR2 ,
257 X_NOTIFICATION_MESSAGE IN VARCHAR2
258 ) IS
259 BEGIN
260 UPDATE CLN_NOTIFICATION_CODES_TL SET
261 NOTIFICATION_MESSAGE = X_NOTIFICATION_MESSAGE,
262 LAST_UPDATE_DATE = sysdate,
263 LAST_UPDATED_BY = Decode(x_owner, 'SEED', 1, 0),
264 LAST_UPDATE_LOGIN = 0,
265 SOURCE_LANG = userenv('LANG')
266 WHERE NOTIFICATION_ID = fnd_number.canonical_to_number(X_NOTIFICATION_ID)
267 AND userenv('LANG') IN (language, source_lang);
268 END translate_row;
269
270 /*----------------------------------------------------------*/
271 /* Load_Row Procedure */
272 /*----------------------------------------------------------*/
273 PROCEDURE load_row
274 (
275 X_NOTIFICATION_ID IN NUMBER ,
276 X_OWNER IN VARCHAR2,
277 X_COLLABORATION_POINT IN VARCHAR2,
278 X_NOTIFICATION_CODE IN VARCHAR2,
279 X_NOTIFICATION_MESSAGE IN VARCHAR2
280 ) IS
281 BEGIN
282 DECLARE
283 l_notification_id NUMBER;
284 l_user_id NUMBER := 0;
285 l_row_id VARCHAR2(64);
286 l_sysdate DATE;
287 l_notification_code VARCHAR2(100);
288 BEGIN
289 IF (x_owner = 'SEED') THEN
290 l_user_id := 1;
291 END IF;
292 --
293
294 SELECT Sysdate INTO l_sysdate FROM dual;
295 -- l_notification_id := fnd_number.canonical_to_number(X_NOTIFICATION_ID);
296
297 BEGIN
298
299 SELECT NOTIFICATION_ID INTO l_notification_id FROM CLN_NOTIFICATION_CODES_B
300 WHERE NOTIFICATION_CODE = X_NOTIFICATION_CODE
301 AND COLLABORATION_POINT = X_COLLABORATION_POINT;
302
303 EXCEPTION
304 WHEN NO_DATA_FOUND then
305
306 SELECT CLN_NOTIFICATION_CODES_S.NEXTVAL INTO l_notification_id FROM DUAL;
307
308 insert into CLN_NOTIFICATION_CODES_B (
309 NOTIFICATION_ID,
310 COLLABORATION_POINT,
311 NOTIFICATION_CODE,
312 CREATION_DATE,
313 CREATED_BY,
314 LAST_UPDATE_DATE,
315 LAST_UPDATED_BY,
316 LAST_UPDATE_LOGIN
317 )
318 values (
319 l_notification_id,
320 X_COLLABORATION_POINT,
321 X_NOTIFICATION_CODE,
322 l_sysdate,
323 l_user_id,
324 l_sysdate,
325 l_user_id,
326 0
327 );
328
329 insert into CLN_NOTIFICATION_CODES_TL (
330 NOTIFICATION_ID,
331 NOTIFICATION_MESSAGE,
332 CREATION_DATE,
333 CREATED_BY,
334 LAST_UPDATE_DATE,
335 LAST_UPDATED_BY,
336 LAST_UPDATE_LOGIN,
337 LANGUAGE,
338 SOURCE_LANG
339 ) select
340 l_notification_id,
341 X_NOTIFICATION_MESSAGE,
342 l_sysdate,
343 l_user_id,
344 l_sysdate,
345 l_user_id,
349 from FND_LANGUAGES L
346 0,
347 L.LANGUAGE_CODE,
348 userenv('LANG')
350 where L.INSTALLED_FLAG in ('I', 'B')
351 and not exists
352 (select NULL
353 from CLN_NOTIFICATION_CODES_TL T
354 where T.NOTIFICATION_ID = l_notification_id
355 and T.LANGUAGE = L.LANGUAGE_CODE);
356
357 RETURN;
358
359 END;
360
361 update CLN_NOTIFICATION_CODES_TL set
362 NOTIFICATION_MESSAGE = X_NOTIFICATION_MESSAGE,
363 LAST_UPDATE_DATE = l_sysdate,
364 LAST_UPDATED_BY = l_user_id,
365 LAST_UPDATE_LOGIN = 0,
366 SOURCE_LANG = userenv('LANG')
367 where NOTIFICATION_ID = l_notification_id
368 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
369
370 END;
371 commit;
372 END load_row;
373
374
375 end CLN_NOTIFICATION_CODES_PKG;