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