[Home] [Help]
PACKAGE BODY: APPS.CN_EVENTS_ALL_PKG
Source
1 package body CN_EVENTS_ALL_PKG as
2 /* $Header: cnmlevnb.pls 120.6.12010000.2 2008/10/10 07:18:41 rajukum ship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2,
6 X_EVENT_ID in NUMBER,
7 X_APPLICATION_REPOSITORY_ID in NUMBER,
8 X_DESCRIPTION in VARCHAR2,
9 X_NAME 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 X_ORG_ID in NUMBER)
16 is
17 cursor C is select ROWID from CN_EVENTS_ALL_B
18 where EVENT_ID = X_EVENT_ID
19 AND ORG_ID = X_ORG_ID;
20
21 begin
22 insert into CN_EVENTS_ALL_B (
23 EVENT_ID,
24 APPLICATION_REPOSITORY_ID,
25 DESCRIPTION,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN,
31 ORG_ID,
32 object_Version_number
33 ) values (
34 X_EVENT_ID,
35 X_APPLICATION_REPOSITORY_ID,
36 X_DESCRIPTION,
37 X_CREATION_DATE,
38 X_CREATED_BY,
39 X_LAST_UPDATE_DATE,
40 X_LAST_UPDATED_BY,
41 X_LAST_UPDATE_LOGIN,
42 X_ORG_ID,
43 1);
44
45 insert into CN_EVENTS_ALL_TL (
46 EVENT_ID,
47 NAME,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN,
51 CREATION_DATE,
52 CREATED_BY,
53 LANGUAGE,
54 SOURCE_LANG,
55 ORG_ID
56 ) select
57 X_EVENT_ID,
58 X_NAME,
59 X_LAST_UPDATE_DATE,
60 X_LAST_UPDATED_BY,
61 X_LAST_UPDATE_LOGIN,
62 X_CREATION_DATE,
63 X_CREATED_BY,
64 L.LANGUAGE_CODE,
65 userenv('LANG'),
66 X_ORG_ID
67 from FND_LANGUAGES L
68 where L.INSTALLED_FLAG in ('I', 'B')
69 and not exists
70 (select NULL
71 from CN_EVENTS_ALL_TL T
72 where T.EVENT_ID = X_EVENT_ID
73 and T.LANGUAGE = L.language_code AND
74 ORG_ID = X_ORG_ID);
75
76 OPEN c;
77 FETCH c INTO x_rowid;
78 IF (c%NOTFOUND) THEN
79 CLOSE c;
80 RAISE NO_DATA_FOUND;
81 END IF;
82 CLOSE c;
83
84 end INSERT_ROW;
85
86 procedure LOCK_ROW (
87 X_EVENT_ID in NUMBER,
88 X_APPLICATION_REPOSITORY_ID in NUMBER,
89 X_DESCRIPTION in VARCHAR2,
90 X_NAME in VARCHAR2,
91 X_ORG_ID IN VARCHAR2
92 ) is
93 cursor c is select
94 APPLICATION_REPOSITORY_ID,
95 DESCRIPTION
96 from CN_EVENTS_ALL_B
97 where EVENT_ID = X_EVENT_ID AND
98 ORG_ID = X_ORG_ID
99 for update of EVENT_ID nowait;
100 recinfo c%rowtype;
101
102 cursor c1 is select
103 NAME,
104 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
105 from CN_EVENTS_ALL_TL
106 where EVENT_ID = X_EVENT_ID
107 and userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
108 ORG_ID = X_ORG_ID
109 for update of EVENT_ID nowait;
110 begin
111 open c;
112 fetch c into recinfo;
113 if (c%notfound) then
114 close c;
115 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
116 app_exception.raise_exception;
117 end if;
118 close c;
119 if ( (recinfo.APPLICATION_REPOSITORY_ID = X_APPLICATION_REPOSITORY_ID)
120 AND ((recinfo.DESCRIPTION = X_DESCRIPTION)
121 OR ((recinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
122 ) then
123 null;
124 else
125 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
126 app_exception.raise_exception;
127 end if;
128
129 for tlinfo in c1 loop
130 if (tlinfo.BASELANG = 'Y') then
131 if ( (tlinfo.NAME = X_NAME)
132 ) then
133 null;
134 else
135 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
136 app_exception.raise_exception;
137 end if;
138 end if;
139 end loop;
140 return;
141 end LOCK_ROW;
142
143 procedure UPDATE_ROW (
144 X_EVENT_ID in NUMBER,
145 X_APPLICATION_REPOSITORY_ID in NUMBER,
146 X_DESCRIPTION in VARCHAR2,
147 X_NAME in VARCHAR2,
148 X_LAST_UPDATE_DATE in DATE,
149 X_LAST_UPDATED_BY in NUMBER,
150 X_LAST_UPDATE_LOGIN in NUMBER,
151 X_ORG_ID IN NUMBER,
152 P_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER)
153 IS
154 -- Added For R12
155
156 l_object_version_number CN_EVENTS_ALL_B.OBJECT_VERSION_NUMBER%TYPE;
157
158 CURSOR l_ovn_csr IS
159 SELECT object_version_number
160 FROM CN_EVENTS_ALL_B
161 WHERE EVENT_ID = x_event_id
162 AND org_id = x_org_id;
163 -- Added For R12
164
165 BEGIN
166
167 OPEN l_ovn_csr;
168 FETCH l_ovn_csr INTO l_object_version_number;
169 CLOSE l_ovn_csr;
170
171 P_OBJECT_VERSION_NUMBER := l_object_version_number;
172
173 update CN_EVENTS_ALL_B set
174 APPLICATION_REPOSITORY_ID = X_APPLICATION_REPOSITORY_ID,
175 DESCRIPTION = X_DESCRIPTION,
176 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
177 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
178 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
179 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER
180 where EVENT_ID = x_event_id AND
181 ORG_ID = X_ORG_ID;
182 if (sql%notfound) then
183 raise no_data_found;
184 end if;
185
186 UPDATE CN_EVENTS_ALL_TL
187 SET NAME = X_NAME,
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 SOURCE_LANG = userenv('LANG')
192 WHERE EVENT_ID = X_EVENT_ID
193 AND userenv('LANG') in (LANGUAGE, SOURCE_LANG)
194 AND ORG_ID = X_ORG_ID;
195
196 IF (SQL%NOTFOUND) THEN
197 RAISE no_data_found;
198 END IF;
199 END UPDATE_ROW;
200
201 PROCEDURE DELETE_ROW (
202 X_EVENT_ID in NUMBER,
203 X_ORG_ID IN NUMBER
204 ) is
205 begin
206 delete from CN_EVENTS_ALL_TL
207 where EVENT_ID = X_EVENT_ID AND
208 ORG_ID = X_ORG_ID;
209
210 IF (SQL%NOTFOUND) THEN
211 RAISE no_data_found;
212 END IF;
213
214 delete from CN_EVENTS_ALL_B
215 where EVENT_ID = X_EVENT_ID AND
216 ORG_ID = X_ORG_ID;
217
218 if (sql%notfound) then
219 raise no_data_found;
220 end if;
221 END DELETE_ROW;
222
223 procedure ADD_LANGUAGE
224 is
225 begin
226 delete from CN_EVENTS_ALL_TL T
227 where not exists
228 (select NULL
229 from CN_EVENTS_ALL_B B
230 where B.EVENT_ID = T.event_id
231 and B.ORG_ID = T.ORG_ID);
232
233 update CN_EVENTS_ALL_TL T set (
234 NAME
235 ) = (select
236 B.NAME
237 from CN_EVENTS_ALL_TL B
238 where B.EVENT_ID = T.EVENT_ID
239 and B.LANGUAGE = T.source_lang
240 and B.ORG_ID = T.ORG_ID)
241
242 where (
243 T.EVENT_ID,
244 T.LANGUAGE
245 ) in (select
246 SUBT.EVENT_ID,
247 SUBT.LANGUAGE
248 from CN_EVENTS_ALL_TL SUBB, CN_EVENTS_ALL_TL SUBT
249 where SUBB.EVENT_ID = SUBT.EVENT_ID
250 and SUBB.LANGUAGE = SUBT.source_lang
251 and SUBB.org_id = SUBT.org_id
252 and (SUBB.NAME <> SUBT.name
253 or (SUBB.NAME is null and SUBT.NAME is not null)
254 or (SUBB.NAME is not null and SUBT.NAME is null)
255 ));
256
257 insert into CN_EVENTS_ALL_TL (
258 ORG_ID,
259 EVENT_ID,
260 NAME,
261 LAST_UPDATE_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_LOGIN,
264 CREATION_DATE,
265 CREATED_BY,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.ORG_ID,
270 B.EVENT_ID,
271 B.NAME,
272 B.LAST_UPDATE_DATE,
273 B.LAST_UPDATED_BY,
274 B.LAST_UPDATE_LOGIN,
275 B.CREATION_DATE,
276 B.CREATED_BY,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from CN_EVENTS_ALL_TL B, FND_LANGUAGES L
280 where L.INSTALLED_FLAG in ('I', 'B')
281 and B.LANGUAGE = userenv('LANG')
282 and not exists
283 (select NULL
284 from CN_EVENTS_ALL_TL T
285 where T.EVENT_ID = B.EVENT_ID
286 and T.LANGUAGE = L.language_code
287 and T.ORG_ID = B.ORG_ID);
288 end ADD_LANGUAGE;
289
290 -- --------------------------------------------------------------------+
291 -- Procedure : LOAD_ROW
292 -- Description : Called by FNDLOAD to upload seed datas, this procedure
293 -- only handle seed datas. ORG_ID = -3113
294 -- --------------------------------------------------------------------+
295 PROCEDURE LOAD_ROW
296 ( x_event_id IN NUMBER,
297 x_description IN VARCHAR2,
298 x_application_repository_id IN NUMBER,
299 x_name IN VARCHAR2,
300 x_org_id IN NUMBER,
301 x_owner IN VARCHAR2) IS
302 user_id NUMBER;
303
304 BEGIN
305 -- Validate input data
306 IF (x_event_id IS NULL) OR (x_application_repository_id IS NULL)
307 OR (x_name IS NULL) THEN
308 GOTO end_load_row;
309 END IF;
310
311 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
312 user_id := 1;
313 ELSE
314 user_id := 0;
315 END IF;
316 -- Load The record to _B table
317 UPDATE cn_events_all_b SET
318 description = x_description,
319 application_repository_id = x_application_repository_id,
320 last_update_date = sysdate,
321 last_updated_by = user_id,
322 last_update_login = 0
323 WHERE event_id = x_event_id
324 AND org_id = x_org_id;
325
326 IF (SQL%NOTFOUND) THEN
327 -- Insert new record to _B table
328 INSERT INTO cn_events_all_b
329 (event_id,
330 description,
331 application_repository_id,
332 creation_date,
333 created_by,
334 last_update_date,
335 last_updated_by,
336 last_update_login,
337 org_id
338 ) VALUES
339 (x_event_id,
340 x_description,
341 x_application_repository_id,
342 sysdate,
343 user_id,
344 sysdate,
345 user_id,
346 0,
347 x_org_id
348 );
349 END IF;
350 -- Load The record to _TL table
351 UPDATE cn_events_all_tl SET
352 name = x_name,
353 last_update_date = sysdate,
354 last_updated_by = user_id,
355 last_update_login = 0,
356 source_lang = userenv('LANG')
357 WHERE event_id = x_event_id
358 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG)
359 AND org_id = x_org_id;
360
361 IF (SQL%NOTFOUND) THEN
362 -- Insert new record to _TL table
363 INSERT INTO cn_events_all_tl
364 (event_id,
365 name,
366 creation_date,
367 created_by,
368 last_update_date,
369 last_updated_by,
370 last_update_login,
371 org_id,
372 language,
373 source_lang)
374 SELECT
375 x_event_id,
376 x_name,
377 sysdate,
378 user_id,
379 sysdate,
380 user_id,
381 0,
382 x_org_id,
383 l.language_code,
384 userenv('LANG')
385 FROM fnd_languages l
386 WHERE l.installed_flag IN ('I', 'B')
387 AND NOT EXISTS
388 (SELECT NULL
389 FROM cn_events_all_tl t
390 WHERE t.event_id = x_event_id
391 AND t.language = l.language_code);
392 END IF;
393 << end_load_row >>
394 NULL;
395 END LOAD_ROW ;
396
397 -- --------------------------------------------------------------------+
398 -- Procedure : TRANSLATE_ROW
399 -- Description : Called by FNDLOAD to translate seed datas, this procedure
400 -- only handle seed datas. ORG_ID = -3113
401 -- --------------------------------------------------------------------+
402 PROCEDURE TRANSLATE_ROW
403 ( x_event_id IN NUMBER,
404 x_name IN VARCHAR2,
405 x_owner IN VARCHAR2) IS
406 user_id NUMBER;
407 BEGIN
408 -- Validate input data
412
409 IF (x_event_id IS NULL) OR (x_name IS NULL) THEN
410 GOTO end_translate_row;
411 END IF;
413 IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
414 user_id := 1;
415 ELSE
416 user_id := 0;
417 END IF;
418 -- Update the translation
419 UPDATE cn_events_all_tl SET
420 name = x_name,
421 last_update_date = sysdate,
422 last_updated_by = user_id,
423 last_update_login = 0,
424 source_lang = userenv('LANG')
425 WHERE event_id = x_event_id
426 AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
427
428 << end_translate_row >>
429 NULL;
430 END TRANSLATE_ROW ;
431
432 end CN_EVENTS_ALL_PKG;