[Home] [Help]
PACKAGE BODY: APPS.AMS_IBA_PS_FILTERS_PKG
Source
1 package body AMS_IBA_PS_FILTERS_PKG as
2 /* $Header: amstfltb.pls 115.7 2002/01/24 22:27:11 pkm ship $ */
3 procedure INSERT_ROW (
4 P_FILTER_ID in NUMBER,
5 P_OBJECT_VERSION_NUMBER in NUMBER,
6 P_FILTER_REF_CODE in VARCHAR2,
7 P_CONTENT_TYPE in VARCHAR2,
8 P_GROUP_NUM in NUMBER,
9 P_FILTER_NAME in VARCHAR2,
10 P_CREATION_DATE in DATE,
11 P_CREATED_BY in NUMBER,
12 P_LAST_UPDATE_DATE in DATE,
13 P_LAST_UPDATED_BY in NUMBER,
14 P_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 begin
17 insert into AMS_IBA_PS_FILTERS_B (
18 OBJECT_VERSION_NUMBER,
19 FILTER_ID,
20 FILTER_REF_CODE,
21 CONTENT_TYPE,
22 GROUP_NUM,
23 CREATION_DATE,
24 CREATED_BY,
25 LAST_UPDATE_DATE,
26 LAST_UPDATED_BY,
27 LAST_UPDATE_LOGIN
28 ) values (
29 P_OBJECT_VERSION_NUMBER,
30 P_FILTER_ID,
31 P_FILTER_REF_CODE,
32 P_CONTENT_TYPE,
33 P_GROUP_NUM,
34 P_CREATION_DATE,
35 P_CREATED_BY,
36 P_LAST_UPDATE_DATE,
37 P_LAST_UPDATED_BY,
38 P_LAST_UPDATE_LOGIN
39 );
40
41 insert into AMS_IBA_PS_FILTERS_TL (
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN,
47 OBJECT_VERSION_NUMBER,
48 FILTER_ID,
49 FILTER_NAME,
50 LANGUAGE,
51 SOURCE_LANG
52 ) select
53 P_CREATED_BY,
54 P_CREATION_DATE,
55 P_LAST_UPDATED_BY,
56 P_LAST_UPDATE_DATE,
57 P_LAST_UPDATE_LOGIN,
58 P_OBJECT_VERSION_NUMBER,
59 P_FILTER_ID,
60 P_FILTER_NAME,
61 L.LANGUAGE_CODE,
62 userenv('LANG')
63 from FND_LANGUAGES L
64 where L.INSTALLED_FLAG in ('I', 'B')
65 and not exists
66 (select NULL
67 from AMS_IBA_PS_FILTERS_TL T
68 where T.FILTER_ID = P_FILTER_ID
69 and T.LANGUAGE = L.LANGUAGE_CODE);
70
71 end INSERT_ROW;
72
73 procedure LOCK_ROW (
74 X_FILTER_ID in NUMBER,
75 X_OBJECT_VERSION_NUMBER in NUMBER,
76 X_FILTER_REF_CODE in VARCHAR2,
77 X_CONTENT_TYPE in VARCHAR2,
78 X_GROUP_NUM in NUMBER,
79 X_FILTER_NAME in VARCHAR2
80 ) is
81 cursor c is select
82 OBJECT_VERSION_NUMBER,
83 FILTER_REF_CODE,
84 CONTENT_TYPE,
85 GROUP_NUM
86 from AMS_IBA_PS_FILTERS_B
87 where FILTER_ID = X_FILTER_ID
88 for update of FILTER_ID nowait;
89 recinfo c%rowtype;
90
91 cursor c1 is select
92 FILTER_NAME,
93 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
94 from AMS_IBA_PS_FILTERS_TL
95 where FILTER_ID = X_FILTER_ID
96 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
97 for update of FILTER_ID nowait;
98 begin
99 open c;
100 fetch c into recinfo;
101 if (c%notfound) then
102 close c;
103 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
104 app_exception.raise_exception;
105 end if;
106 close c;
107 if (
108 ((recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER)
109 OR ((recinfo.OBJECT_VERSION_NUMBER is null) AND (X_OBJECT_VERSION_NUMBER is null)))
110 AND ((recinfo.FILTER_REF_CODE = X_FILTER_REF_CODE)
111 OR ((recinfo.FILTER_REF_CODE is null) AND (X_FILTER_REF_CODE is null)))
112 AND ((recinfo.CONTENT_TYPE = X_CONTENT_TYPE)
113 OR ((recinfo.CONTENT_TYPE is null) AND (X_CONTENT_TYPE is null)))
114 AND ((recinfo.GROUP_NUM = X_GROUP_NUM)
115 OR ((recinfo.GROUP_NUM is null) AND (X_GROUP_NUM is null)))
116 ) then
117 null;
118 else
119 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
120 app_exception.raise_exception;
121 end if;
122
123 for tlinfo in c1 loop
124 if (tlinfo.BASELANG = 'Y') then
125 if ( (tlinfo.FILTER_NAME = X_FILTER_NAME)
126 ) then
127 null;
128 else
129 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
130 app_exception.raise_exception;
131 end if;
132 end if;
133 end loop;
134 return;
135 end LOCK_ROW;
136
137 procedure UPDATE_ROW (
138 P_FILTER_ID in NUMBER,
139 P_OBJECT_VERSION_NUMBER in NUMBER,
140 P_FILTER_REF_CODE in VARCHAR2,
141 P_CONTENT_TYPE in VARCHAR2,
142 P_GROUP_NUM in NUMBER,
143 P_FILTER_NAME in VARCHAR2,
144 P_LAST_UPDATE_DATE in DATE,
145 P_LAST_UPDATED_BY in NUMBER,
146 P_LAST_UPDATE_LOGIN in NUMBER
147 ) is
148 begin
149 update AMS_IBA_PS_FILTERS_B set
150 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER,
151 FILTER_REF_CODE = P_FILTER_REF_CODE,
152 CONTENT_TYPE = P_CONTENT_TYPE,
153 GROUP_NUM = P_GROUP_NUM,
154 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
155 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
156 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
157 where FILTER_ID = P_FILTER_ID;
158
159 if (sql%notfound) then
160 raise no_data_found;
161 end if;
162
163 update AMS_IBA_PS_FILTERS_TL set
164 FILTER_NAME = P_FILTER_NAME,
165 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
166 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
167 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
168 SOURCE_LANG = userenv('LANG')
169 where FILTER_ID = P_FILTER_ID
170 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
171
172 if (sql%notfound) then
173 raise no_data_found;
174 end if;
175
176 end UPDATE_ROW;
177
178 procedure DELETE_ROW (
179 P_FILTER_ID in NUMBER
180 ) is
181 begin
182 delete from AMS_IBA_PS_FILTERS_TL
183 where FILTER_ID = P_FILTER_ID;
184
185 if (sql%notfound) then
186 raise no_data_found;
187 end if;
188
189 delete from AMS_IBA_PS_FILTERS_B
190 where FILTER_ID = P_FILTER_ID;
191
192 if (sql%notfound) then
193 raise no_data_found;
194 end if;
195 end DELETE_ROW;
196
197 procedure ADD_LANGUAGE
198 is
199 begin
200 delete from AMS_IBA_PS_FILTERS_TL T
201 where not exists
202 (select NULL
203 from AMS_IBA_PS_FILTERS_B B
204 where B.FILTER_ID = T.FILTER_ID
205 );
206
207 update AMS_IBA_PS_FILTERS_TL T set (
208 FILTER_NAME
209 ) = (select
210 B.FILTER_NAME
211 from AMS_IBA_PS_FILTERS_TL B
212 where B.FILTER_ID = T.FILTER_ID
213 and B.LANGUAGE = T.SOURCE_LANG)
214 where (
215 T.FILTER_ID,
216 T.LANGUAGE
217 ) in (select
218 SUBT.FILTER_ID,
219 SUBT.LANGUAGE
220 from AMS_IBA_PS_FILTERS_TL SUBB, AMS_IBA_PS_FILTERS_TL SUBT
221 where SUBB.FILTER_ID = SUBT.FILTER_ID
222 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
223 and (SUBB.FILTER_NAME <> SUBT.FILTER_NAME
224 ));
225
226 insert into AMS_IBA_PS_FILTERS_TL (
227 CREATED_BY,
228 CREATION_DATE,
229 LAST_UPDATED_BY,
230 LAST_UPDATE_DATE,
231 LAST_UPDATE_LOGIN,
232 OBJECT_VERSION_NUMBER,
233 FILTER_ID,
234 FILTER_NAME,
235 LANGUAGE,
236 SOURCE_LANG
237 ) select
238 B.CREATED_BY,
239 B.CREATION_DATE,
240 B.LAST_UPDATED_BY,
241 B.LAST_UPDATE_DATE,
242 B.LAST_UPDATE_LOGIN,
243 B.OBJECT_VERSION_NUMBER,
244 B.FILTER_ID,
245 B.FILTER_NAME,
246 L.LANGUAGE_CODE,
247 B.SOURCE_LANG
248 from AMS_IBA_PS_FILTERS_TL B, FND_LANGUAGES L
249 where L.INSTALLED_FLAG in ('I', 'B')
250 and B.LANGUAGE = userenv('LANG')
251 and not exists
252 (select NULL
253 from AMS_IBA_PS_FILTERS_TL T
254 where T.FILTER_ID = B.FILTER_ID
255 and T.LANGUAGE = L.LANGUAGE_CODE);
256 end ADD_LANGUAGE;
257
258
259 PROCEDURE translate_row (
260 x_filter_id IN NUMBER,
261 x_filter_name IN VARCHAR2,
262 x_owner IN VARCHAR2
263 )
264 IS
265 BEGIN
266 update ams_iba_ps_filters_tl set
267 filter_name = nvl(x_filter_name, filter_name),
268 source_lang = userenv('LANG'),
269 last_update_date = sysdate,
270 last_updated_by = decode(x_owner, 'SEED', 1, 0),
271 last_update_login = 0
272 where filter_id = x_filter_id
273 and userenv('LANG') in (language, source_lang);
274 end TRANSLATE_ROW;
275
276
277 PROCEDURE load_row (
278 x_filter_id IN NUMBER,
279 x_filter_ref_code IN VARCHAR2,
280 x_content_type IN VARCHAR2,
281 x_group_num IN NUMBER,
282 x_filter_name IN VARCHAR2,
283 x_owner IN VARCHAR2
284 )
285 IS
286 l_user_id number := 0;
287 l_obj_verno number;
288 l_dummy_char varchar2(1);
289 l_row_id varchar2(100);
290 l_filter_id number;
291
292 cursor c_obj_verno is
293 select object_version_number
294 from ams_iba_ps_filters_b
295 where filter_id = x_filter_id;
296
297 cursor c_chk_filter_exists is
298 select 'x'
299 from ams_iba_ps_filters_b
300 where filter_id = x_filter_id;
301
302 cursor c_get_filter_id is
303 select ams_iba_ps_filters_b_s.nextval
304 from dual;
305 BEGIN
306 if X_OWNER = 'SEED' then
307 l_user_id := 1;
308 end if;
309
310 open c_chk_filter_exists;
311 fetch c_chk_filter_exists into l_dummy_char;
312 if c_chk_filter_exists%notfound THEN
313 if x_filter_id is null then
314 open c_get_filter_id;
315 fetch c_get_filter_id into l_filter_id;
316 close c_get_filter_id;
317 else
318 l_filter_id := x_filter_id;
319 end if;
320 l_obj_verno := 1;
321
322 AMS_IBA_PS_FILTERS_PKG.INSERT_ROW (
323 p_filter_id => l_filter_id,
324 p_object_version_number => l_obj_verno,
325 p_filter_ref_code => x_filter_ref_code,
326 p_content_type => x_content_type,
327 p_group_num => x_group_num,
328 p_filter_name => x_filter_name,
329 p_creation_date => SYSDATE,
330 p_created_by => l_user_id,
331 p_last_update_date => SYSDATE,
332 p_last_updated_by => l_user_id,
333 p_last_update_login => 0
334 );
335 else
336 open c_obj_verno;
337 fetch c_obj_verno into l_obj_verno;
338 close c_obj_verno;
339 l_filter_id := x_filter_id;
340 AMS_IBA_PS_FILTERS_PKG.UPDATE_ROW (
341 p_filter_id => l_filter_id,
342 p_object_version_number => l_obj_verno+1,
343 p_filter_ref_code => x_filter_ref_code,
344 p_content_type => x_content_type,
345 p_group_num => x_group_num,
346 p_filter_name => x_filter_name,
347 p_last_update_date => SYSDATE,
348 p_last_updated_by => l_user_id,
349 p_last_update_login => 0
350 );
351 end if;
352 close c_chk_filter_exists;
353 END load_row;
354
355
356 end AMS_IBA_PS_FILTERS_PKG;