[Home] [Help]
PACKAGE BODY: APPS.JTF_PREFAB_FILTERS_PKG
Source
1 package body JTF_PREFAB_FILTERS_PKG as
2 /* $Header: jtfpreffltrtb.pls 120.0 2005/11/08 21:56:41 emekala noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_FILTER_ID in NUMBER,
6 X_OBJECT_VERSION_NUMBER in NUMBER,
7 X_SECURITY_GROUP_ID in NUMBER,
8 X_APPLICATION_ID in NUMBER,
9 X_FILTER_NAME in VARCHAR2,
10 X_FILTER_STRING in VARCHAR2,
11 X_EXCLUSION_FLAG in VARCHAR2,
12 X_ENABLED_FLAG in VARCHAR2,
13 X_DESCRIPTION in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER
19 ) is
20 cursor C is select ROWID from JTF_PREFAB_FILTERS_B
21 where FILTER_ID = X_FILTER_ID
22 ;
23 begin
24 insert into JTF_PREFAB_FILTERS_B (
25 FILTER_ID,
26 OBJECT_VERSION_NUMBER,
27 -- SECURITY_GROUP_ID,
28 APPLICATION_ID,
29 FILTER_NAME,
30 FILTER_STRING,
31 EXCLUSION_FLAG,
32 ENABLED_FLAG,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN
38 ) values (
39 X_FILTER_ID,
40 X_OBJECT_VERSION_NUMBER,
41 -- X_SECURITY_GROUP_ID,
42 X_APPLICATION_ID,
43 X_FILTER_NAME,
44 X_FILTER_STRING,
45 X_EXCLUSION_FLAG,
46 X_ENABLED_FLAG,
47 X_CREATION_DATE,
48 X_CREATED_BY,
49 X_LAST_UPDATE_DATE,
50 X_LAST_UPDATED_BY,
51 X_LAST_UPDATE_LOGIN
52 );
53
54 insert into JTF_PREFAB_FILTERS_TL (
55 FILTER_ID,
56 DESCRIPTION,
57 CREATED_BY,
58 CREATION_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_DATE,
61 LAST_UPDATE_LOGIN,
62 -- SECURITY_GROUP_ID,
63 LANGUAGE,
64 SOURCE_LANG
65 ) select
66 X_FILTER_ID,
67 X_DESCRIPTION,
68 X_CREATED_BY,
69 X_CREATION_DATE,
70 X_LAST_UPDATED_BY,
71 X_LAST_UPDATE_DATE,
72 X_LAST_UPDATE_LOGIN,
73 -- X_SECURITY_GROUP_ID,
74 L.LANGUAGE_CODE,
75 userenv('LANG')
76 from FND_LANGUAGES L
77 where L.INSTALLED_FLAG in ('I', 'B')
78 and not exists
79 (select NULL
80 from JTF_PREFAB_FILTERS_TL T
81 where T.FILTER_ID = X_FILTER_ID
82 and T.LANGUAGE = L.LANGUAGE_CODE);
83
84 open c;
85 fetch c into X_ROWID;
86 if (c%notfound) then
87 close c;
88 raise no_data_found;
89 end if;
90 close c;
91
92 end INSERT_ROW;
93
94 procedure LOCK_ROW (
95 X_FILTER_ID in NUMBER,
96 X_OBJECT_VERSION_NUMBER in NUMBER,
97 X_SECURITY_GROUP_ID in NUMBER,
98 X_APPLICATION_ID in NUMBER,
99 X_FILTER_NAME in VARCHAR2,
100 X_FILTER_STRING in VARCHAR2,
101 X_EXCLUSION_FLAG in VARCHAR2,
102 X_ENABLED_FLAG in VARCHAR2,
103 X_DESCRIPTION in VARCHAR2
104 ) is
105 cursor c is select
106 OBJECT_VERSION_NUMBER,
107 -- SECURITY_GROUP_ID,
108 APPLICATION_ID,
109 FILTER_NAME,
110 FILTER_STRING,
111 EXCLUSION_FLAG,
112 ENABLED_FLAG
113 from JTF_PREFAB_FILTERS_B
114 where FILTER_ID = X_FILTER_ID
115 for update of FILTER_ID nowait;
116 recinfo c%rowtype;
117
118 cursor c1 is select
119 DESCRIPTION,
120 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
121 from JTF_PREFAB_FILTERS_TL
122 where FILTER_ID = X_FILTER_ID
123 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
124 for update of FILTER_ID nowait;
125 begin
126 open c;
127 fetch c into recinfo;
128 if (c%notfound) then
129 close c;
130 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
131 app_exception.raise_exception;
132 end if;
133 close c;
134 if ( (recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
135 -- AND ((recinfo.SECURITY_GROUP_ID = X_SECURITY_GROUP_ID)
136 -- OR ((recinfo.SECURITY_GROUP_ID is null) AND (X_SECURITY_GROUP_ID is null)))
137 AND (recinfo.APPLICATION_ID = X_APPLICATION_ID)
138 AND (recinfo.FILTER_NAME = X_FILTER_NAME)
139 AND (recinfo.FILTER_STRING = X_FILTER_STRING)
140 AND (recinfo.EXCLUSION_FLAG = X_EXCLUSION_FLAG)
141 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
142 ) then
143 null;
144 else
145 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
146 app_exception.raise_exception;
147 end if;
148
149 for tlinfo in c1 loop
150 if (tlinfo.BASELANG = 'Y') then
151 if ( (tlinfo.DESCRIPTION = X_DESCRIPTION)
152 ) then
153 null;
154 else
155 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
156 app_exception.raise_exception;
157 end if;
158 end if;
159 end loop;
160 return;
161 end LOCK_ROW;
162
163 procedure UPDATE_ROW (
164 X_FILTER_ID in NUMBER,
165 X_OBJECT_VERSION_NUMBER in NUMBER,
166 X_SECURITY_GROUP_ID in NUMBER,
167 X_APPLICATION_ID in NUMBER,
168 X_FILTER_NAME in VARCHAR2,
169 X_FILTER_STRING in VARCHAR2,
170 X_EXCLUSION_FLAG in VARCHAR2,
171 X_ENABLED_FLAG in VARCHAR2,
172 X_DESCRIPTION in VARCHAR2,
173 X_LAST_UPDATE_DATE in DATE,
174 X_LAST_UPDATED_BY in NUMBER,
175 X_LAST_UPDATE_LOGIN in NUMBER
176 ) is
177 begin
178 update JTF_PREFAB_FILTERS_B set
179 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
180 -- SECURITY_GROUP_ID = X_SECURITY_GROUP_ID,
181 APPLICATION_ID = X_APPLICATION_ID,
182 FILTER_NAME = X_FILTER_NAME,
183 FILTER_STRING = X_FILTER_STRING,
184 EXCLUSION_FLAG = X_EXCLUSION_FLAG,
185 ENABLED_FLAG = X_ENABLED_FLAG,
186 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
187 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
188 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
189 where FILTER_ID = X_FILTER_ID;
190
191 if (sql%notfound) then
192 raise no_data_found;
193 end if;
194
195 update JTF_PREFAB_FILTERS_TL set
196 DESCRIPTION = X_DESCRIPTION,
197 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
198 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
199 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
200 SOURCE_LANG = userenv('LANG')
201 where FILTER_ID = X_FILTER_ID
202 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
203
204 if (sql%notfound) then
205 raise no_data_found;
206 end if;
207 end UPDATE_ROW;
208
209 procedure DELETE_ROW (
210 X_FILTER_ID in NUMBER
211 ) is
212 begin
213 delete from JTF_PREFAB_FILTERS_TL
214 where FILTER_ID = X_FILTER_ID;
215
216 if (sql%notfound) then
217 raise no_data_found;
218 end if;
219
220 delete from JTF_PREFAB_FILTERS_B
221 where FILTER_ID = X_FILTER_ID;
222
223 if (sql%notfound) then
224 raise no_data_found;
225 end if;
226 end DELETE_ROW;
227
228 procedure ADD_LANGUAGE
229 is
230 begin
231 delete from JTF_PREFAB_FILTERS_TL T
232 where not exists
233 (select NULL
234 from JTF_PREFAB_FILTERS_B B
235 where B.FILTER_ID = T.FILTER_ID
236 );
237
238 update JTF_PREFAB_FILTERS_TL T set (
239 DESCRIPTION
240 ) = (select
241 B.DESCRIPTION
242 from JTF_PREFAB_FILTERS_TL B
243 where B.FILTER_ID = T.FILTER_ID
244 and B.LANGUAGE = T.SOURCE_LANG)
245 where (
246 T.FILTER_ID,
247 T.LANGUAGE
248 ) in (select
249 SUBT.FILTER_ID,
250 SUBT.LANGUAGE
251 from JTF_PREFAB_FILTERS_TL SUBB, JTF_PREFAB_FILTERS_TL SUBT
252 where SUBB.FILTER_ID = SUBT.FILTER_ID
253 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
254 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
255 ));
256
257 insert into JTF_PREFAB_FILTERS_TL (
258 FILTER_ID,
259 DESCRIPTION,
260 CREATED_BY,
261 CREATION_DATE,
262 LAST_UPDATED_BY,
263 LAST_UPDATE_DATE,
264 LAST_UPDATE_LOGIN,
265 -- SECURITY_GROUP_ID,
266 LANGUAGE,
267 SOURCE_LANG
268 ) select
269 B.FILTER_ID,
270 B.DESCRIPTION,
271 B.CREATED_BY,
272 B.CREATION_DATE,
273 B.LAST_UPDATED_BY,
274 B.LAST_UPDATE_DATE,
275 B.LAST_UPDATE_LOGIN,
276 -- B.SECURITY_GROUP_ID,
277 L.LANGUAGE_CODE,
278 B.SOURCE_LANG
279 from JTF_PREFAB_FILTERS_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 JTF_PREFAB_FILTERS_TL T
285 where T.FILTER_ID = B.FILTER_ID
286 and T.LANGUAGE = L.LANGUAGE_CODE);
287 end ADD_LANGUAGE;
288
289 procedure LOAD_ROW (
290 X_OBJECT_VERSION_NUMBER in NUMBER,
291 X_SECURITY_GROUP_ID in NUMBER,
292 X_APPLICATION_ID in NUMBER,
293 X_FILTER_NAME in VARCHAR2,
294 X_FILTER_STRING in VARCHAR2,
295 X_EXCLUSION_FLAG in VARCHAR2,
296 X_ENABLED_FLAG in VARCHAR2,
297 X_DESCRIPTION in VARCHAR2,
298 X_LAST_UPDATE_DATE in DATE,
299 X_OWNER in VARCHAR2,
300 X_CUSTOM_MODE in VARCHAR2
301 ) is
302 --****** local variables ******
303 l_row_id VARCHAR2(255);
304
305 f_luby NUMBER;
306 f_ludate DATE;
307 db_luby NUMBER;
308 db_ludate DATE;
309 l_filter_id NUMBER;
310
311 cursor c is select nvl(max(FILTER_ID), 0) from jtf_prefab_filters_b where FILTER_ID < 10000;
312 l_pseudo_seq NUMBER := NULL;
313 begin
314
315 if (X_OWNER = 'SEED') then
316 f_luby := 1;
317 else
318 f_luby := 0;
319 end if;
320
321 f_ludate := X_LAST_UPDATE_DATE;
322
323 begin
324 SELECT FILTER_ID, LAST_UPDATED_BY, LAST_UPDATE_DATE
325 INTO l_filter_id, db_luby, db_ludate
326 FROM JTF_PREFAB_FILTERS_B
327 WHERE APPLICATION_ID = X_APPLICATION_ID AND
328 FILTER_NAME = X_FILTER_NAME;
329
330 -- **** Entry is there, check if it's legal to update ****
331 IF ((X_CUSTOM_MODE = 'FORCE') OR
332 ((f_luby = 0) AND (db_luby = 1)) OR
333 ((f_luby = db_luby) AND (f_ludate > db_ludate))
334 )
335 then
336 -- **** call Update row ****
337 JTF_PREFAB_FILTERS_PKG.UPDATE_ROW (
338 X_FILTER_ID => l_filter_id,
339 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
340 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
341 X_APPLICATION_ID => X_APPLICATION_ID,
342 X_FILTER_NAME => X_FILTER_NAME,
343 X_FILTER_STRING => X_FILTER_STRING,
344 X_EXCLUSION_FLAG => X_EXCLUSION_FLAG,
345 X_ENABLED_FLAG => X_ENABLED_FLAG,
346 X_DESCRIPTION => X_DESCRIPTION,
347 X_LAST_UPDATE_DATE => f_ludate,
348 X_LAST_UPDATED_BY => f_luby,
349 X_LAST_UPDATE_LOGIN => 0);
350 end if;
351 exception
352 when no_data_found then
353 -- **** generate pseudo sequence ***
354 OPEN c;
355 FETCH c INTO l_pseudo_seq;
356 CLOSE c;
357
358 JTF_PREFAB_FILTERS_PKG.INSERT_ROW (
359 X_ROWID => l_row_id,
360 X_FILTER_ID => (l_pseudo_seq + 1),
361 X_OBJECT_VERSION_NUMBER => X_OBJECT_VERSION_NUMBER,
362 X_SECURITY_GROUP_ID => X_SECURITY_GROUP_ID,
363 X_APPLICATION_ID => X_APPLICATION_ID,
364 X_FILTER_NAME => X_FILTER_NAME,
365 X_FILTER_STRING => X_FILTER_STRING,
366 X_EXCLUSION_FLAG => X_EXCLUSION_FLAG,
367 X_ENABLED_FLAG => X_ENABLED_FLAG,
368 X_DESCRIPTION => X_DESCRIPTION,
369 X_CREATION_DATE => f_ludate,
370 X_CREATED_BY => f_luby,
371 X_LAST_UPDATE_DATE => f_ludate,
372 X_LAST_UPDATED_BY => f_luby,
373 X_LAST_UPDATE_LOGIN => 0);
374 end;
375
376 end LOAD_ROW;
377
378 procedure TRANSLATE_ROW (
379 X_APPLICATION_ID in NUMBER,
380 X_FILTER_NAME in VARCHAR2,
381 X_DESCRIPTION in VARCHAR2,
382 X_LAST_UPDATE_DATE in DATE,
383 X_OWNER in VARCHAR2,
384 X_CUSTOM_MODE in VARCHAR2
385 ) is
386 -- **** local variables *****
387 f_luby NUMBER;
388 f_ludate DATE;
389 db_luby NUMBER;
390 db_ludate DATE;
391 l_filter_id NUMBER;
392 begin
393
394 if (X_OWNER = 'SEED') then
395 f_luby := 1;
396 else
397 f_luby := 0;
398 end if;
399
400 f_ludate := X_LAST_UPDATE_DATE;
401
402 begin
403 SELECT tl.FILTER_ID, tl.LAST_UPDATED_BY, tl.LAST_UPDATE_DATE
404 INTO l_filter_id, db_luby, db_ludate
405 FROM JTF_PREFAB_FILTERS_B b, JTF_PREFAB_FILTERS_TL tl
406 WHERE b.FILTER_ID = tl.FILTER_ID AND
407 b.APPLICATION_ID = X_APPLICATION_ID AND
408 b.FILTER_NAME = X_FILTER_NAME AND
409 tl.LANGUAGE = userenv('LANG');
410
411 if ((X_CUSTOM_MODE = 'FORCE') OR
412 ((f_luby = 0) AND (db_luby = 1)) OR
413 ((f_luby = db_luby) AND (f_ludate > db_ludate))
414 )
415 then
416 update JTF_PREFAB_FILTERS_TL set
417 DESCRIPTION = nvl(X_DESCRIPTION, DESCRIPTION),
418 LAST_UPDATE_DATE = f_ludate,
419 LAST_UPDATED_BY = f_luby,
420 LAST_UPDATE_LOGIN = 0,
421 SOURCE_LANG = userenv('LANG')
425 exception
422 where userenv('LANG') in (LANGUAGE, SOURCE_LANG) and
423 FILTER_ID = l_filter_id;
424 end if;
426 when no_data_found then null;
427 end;
428 end TRANSLATE_ROW;
429
430 end JTF_PREFAB_FILTERS_PKG;