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