[Home] [Help]
PACKAGE BODY: APPS.WF_WL_VIEWS_PKG
Source
1 package body WF_WL_VIEWS_PKG as
2 /* $Header: wfwlvwsb.pls 120.0.12020000.4 2013/05/15 21:24:52 alsosa noship $ */
3 g_mode varchar2(10);
4
5 procedure LoaderSetMode(x_mode in varchar2) is
6 begin
7 g_mode := x_mode;
8 end LoaderSetMode;
9
10 function is_update_allowed(X_CUSTOM_LEVEL_NEW in varchar2,
11 X_CUSTOM_LEVEL_OLD in varchar2) return varchar2
12 is
13 begin
14 -- Cannot overwrite data with a higher customization level
15 if X_CUSTOM_LEVEL_NEW = 'U' then
16 if X_CUSTOM_LEVEL_OLD in ('C','L') then
17 return ('N'); -- Error will be logged
18 elsif X_CUSTOM_LEVEL_OLD = 'U' then
19 return ('Y'); -- Return Y. Update is based on the caller
20 end if;
21 elsif X_CUSTOM_LEVEL_NEW = 'L' then
22 if X_CUSTOM_LEVEL_OLD = 'C' then
23 return('N'); -- Error will be logged
24 elsif X_CUSTOM_LEVEL_OLD = 'U' then
25 return('Y'); -- Override it
26 else
27 return('Y'); -- Customization Level is L
28 end if;
29 elsif X_CUSTOM_LEVEL_NEW = 'C' then
30 return('Y'); -- Override the values in the database irrespective of the value
31 end if;
32 end is_update_allowed;
33
34 function FIND_VIEW_KEY(x_view_id in number) return varchar2 is
35 l_view_key WF_WL_VIEWS.VIEW_KEY%TYPE;
36 begin
37 if x_view_id is null then
38 return null;
39 end if;
40 select VIEW_KEY into l_view_key
41 from WF_WL_VIEWS
42 where VIEW_ID=x_view_id;
43 return l_view_key;
44 exception
45 when others then
46 raise_application_error (-20000, 'WF_WL_VIEWS_PKG.FIND_VIEW_KEY: unable to find viewid '||l_view_key);
47 end FIND_VIEW_KEY;
48
49 function FIND_VIEW_ID(X_KEY in VARCHAR2) return number is
50 l_view_id number;
51 begin
52 select VIEW_ID into l_view_id
53 from WF_WL_VIEWS
54 where VIEW_KEY=X_KEY;
55 return l_view_id;
56 exception
57 when others then
58 raise_application_error (-20000, 'WF_WL_VIEWS_PKG.FIND_VIEW_ID: unable to find view '||X_KEY);
59 end FIND_VIEW_ID;
60
61 function fetch_custom_level (p_view_key in varchar2) return varchar2 is
62 l_custom_level varchar2(1);
63 l_view_id number := FIND_VIEW_ID(p_view_key);
64 begin
65 select CUSTOMIZATION_LEVEL into l_custom_level
66 from WF_WL_VIEWS
67 where VIEW_ID=l_view_id;
68 return l_custom_level;
69 end fetch_custom_level;
70
71 procedure INSERT_VIEW_BY_KEY (X_VIEW_KEY in VARCHAR2,
72 X_PARENT_VIEW_ID in NUMBER,
73 X_VIEW_TYPE in VARCHAR2,
74 X_APPLICATION_MODULE in VARCHAR2,
75 X_AM_IMPL_CLASS in VARCHAR2,
76 X_AM_IMPL_METHOD in VARCHAR2,
77 X_VO_IMPL_CLASS in VARCHAR2,
78 X_VO_IMPL_METHOD in VARCHAR2,
79 X_VO_INSTANCE in VARCHAR2,
80 X_PLSQL_API in VARCHAR2,
81 X_SQL_TEXT in VARCHAR2,
82 X_CUSTOMIZATION_LEVEL in VARCHAR2,
83 X_SEQUENCE in NUMBER,
84 X_STATUS in VARCHAR2,
85 X_TITLE in VARCHAR2,
86 X_DESCRIPTION in VARCHAR2,
87 X_OWNER IN VARCHAR2,
88 X_LAST_UPDATE_DATE IN VARCHAR2)
89 is
90 cursor c_newrow is select ROWID from WF_WL_VIEWS
91 where VIEW_KEY = X_VIEW_KEY;
92 l_rowid varchar2(30);
93 l_luby number;
94 l_ludate date;
95 begin
96 -- Translate owner to file_last_updated_by
97 l_luby := fnd_load_util.OWNER_ID(x_owner);
98 -- Translate char last_update_date to date
99 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
100
101 insert into WF_WL_VIEWS (VIEW_ID,
102 VIEW_KEY,
103 PARENT_VIEW_ID,
104 VIEW_TYPE,
105 APPLICATION_MODULE,
106 AM_IMPL_CLASS,
107 AM_IMPL_METHOD,
108 VO_IMPL_CLASS,
109 VO_IMPL_METHOD,
110 VO_INSTANCE,
111 PLSQL_API,
112 SQL_TEXT,
113 CUSTOMIZATION_LEVEL,
114 SEQUENCE,
115 STATUS,
116 CREATION_DATE,
117 CREATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATED_BY,
120 LAST_UPDATE_LOGIN,
121 SECURITY_GROUP_ID)
122 values (WF_WL_VIEWS_S.NEXTVAL,
123 X_VIEW_KEY,
124 X_PARENT_VIEW_ID,
125 X_VIEW_TYPE,
126 X_APPLICATION_MODULE,
127 X_AM_IMPL_CLASS,
128 X_AM_IMPL_METHOD,
129 X_VO_IMPL_CLASS,
130 X_VO_IMPL_METHOD,
131 X_VO_INSTANCE,
132 X_PLSQL_API,
133 X_SQL_TEXT,
134 X_CUSTOMIZATION_LEVEL,
135 X_SEQUENCE,
136 X_STATUS,
137 l_ludate, --X_CREATION_DATE,
138 l_luby, -- X_CREATED_BY,
139 l_ludate, -- X_LAST_UPDATE_DATE,
140 l_luby, -- X_LAST_UPDATED_BY,
141 0, -- X_LAST_UPDATE_LOGIN,
142 FND_GLOBAL.SECURITY_GROUP_ID);
143 insert into WF_WL_VIEWS_TL (VIEW_ID, TITLE,
144 DESCRIPTION,
145 LANGUAGE,
146 SOURCE_LANG,
147 CREATION_DATE,
148 CREATED_BY,
149 LAST_UPDATE_DATE,
150 LAST_UPDATED_BY,
151 LAST_UPDATE_LOGIN,
152 SECURITY_GROUP_ID)
153 select WF_WL_VIEWS_S.CURRVAL,
154 X_TITLE, X_DESCRIPTION,
155 L.LANGUAGE_CODE,
156 userenv('LANG'),
157 l_ludate,
158 l_luby,
159 l_ludate,
160 l_luby,
161 0,
162 FND_GLOBAL.SECURITY_GROUP_ID
163 from FND_LANGUAGES L
164 where L.INSTALLED_FLAG in ('I', 'B')
165 and not exists
166 (select NULL
167 from WF_WL_VIEWS_TL TL, WF_WL_VIEWS B
168 where B.VIEW_ID = TL.VIEW_ID and
169 B.VIEW_KEY = X_VIEW_KEY and
170 TL.LANGUAGE = L.LANGUAGE_CODE);
171
172 open c_newrow;
173 fetch c_newrow into l_rowid ;
174 if (c_newrow%notfound) then
175 close c_newrow;
176 raise_application_error (-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_BY_KEY: unable to create view '||X_VIEW_KEY);
177 end if;
178 close c_newrow;
179 end INSERT_VIEW_BY_KEY;
180
181 procedure UPDATE_VIEW_BY_KEY (X_VIEW_KEY in VARCHAR2,
182 X_PARENT_VIEW in VARCHAR2,
183 X_VIEW_TYPE in VARCHAR2,
184 X_APPLICATION_MODULE in VARCHAR2,
185 X_AM_IMPL_CLASS in VARCHAR2,
186 X_AM_IMPL_METHOD in VARCHAR2,
187 X_VO_IMPL_CLASS in VARCHAR2,
188 X_VO_IMPL_METHOD in VARCHAR2,
189 X_VO_INSTANCE in VARCHAR2,
190 X_PLSQL_API in VARCHAR2,
191 X_SQL_TEXT in VARCHAR2,
192 X_CUSTOMIZATION_LEVEL in VARCHAR2,
193 X_SEQUENCE in NUMBER,
194 X_STATUS in VARCHAR2,
195 X_TITLE in VARCHAR2,
196 X_DESCRIPTION in VARCHAR2,
197 X_OWNER IN VARCHAR2,
198 X_LAST_UPDATE_DATE IN VARCHAR2) is
199 l_custom_level varchar2(1);
200 l_parent_view_id WF_WL_VIEWS.PARENT_VIEW_ID%TYPE;
201 l_luby number;
202 l_ludate date;
203 begin
204 -- Translate owner to file_last_updated_by
205 l_luby := fnd_load_util.OWNER_ID(x_owner);
206 -- Translate char last_update_date to date
207 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
208
209 --Look for a parent view ID only if a parent view is given in X_PARENT_VIEW
210 if X_PARENT_VIEW is not null then
211 l_parent_view_id := FIND_VIEW_ID (X_PARENT_VIEW);
212 end if;
213 if g_mode='FORCE' or (X_CUSTOMIZATION_LEVEL = 'C' and g_mode = 'UPGRADE') then
214 update WF_WL_VIEWS B
215 set B.VIEW_TYPE = X_VIEW_TYPE,
216 B.PARENT_VIEW_ID = l_parent_view_id,
217 B.APPLICATION_MODULE = X_APPLICATION_MODULE,
218 B.AM_IMPL_CLASS = X_AM_IMPL_CLASS,
219 B.AM_IMPL_METHOD = X_AM_IMPL_METHOD,
220 B.VO_IMPL_CLASS = X_VO_IMPL_CLASS,
221 B.VO_IMPL_METHOD = X_VO_IMPL_METHOD,
222 B.VO_INSTANCE = X_VO_INSTANCE,
223 B.PLSQL_API = X_PLSQL_API,
224 B.SQL_TEXT = X_SQL_TEXT,
225 B.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
226 B.SEQUENCE = X_SEQUENCE,
227 B.STATUS = X_STATUS,
228 B.LAST_UPDATE_DATE = l_ludate, -- X_LAST_UPDATE_DATE,
229 B.LAST_UPDATED_BY = l_luby, -- X_LAST_UPDATED_BY,
230 B.LAST_UPDATE_LOGIN = 0
231 where B.VIEW_KEY = X_VIEW_KEY;
232 if (sql%notfound) then
233 raise no_data_found;
234 end if;
235 else
236 -- In FNDLOAD there are only two modes: FORCE and UPGRADE. If we are here it means
237 -- that g_mode='UPGRADE' and X_CUSTOMIZATION_LEVEL<>'C'
238 l_custom_level := fetch_custom_level(X_VIEW_KEY);
239 if is_update_allowed(X_CUSTOMIZATION_LEVEL, l_custom_level) = 'Y' then
240 update WF_WL_VIEWS B
241 set B.STATUS = X_STATUS,
242 B.LAST_UPDATE_DATE = l_ludate, -- X_LAST_UPDATE_DATE,
243 B.LAST_UPDATED_BY = l_luby, -- X_LAST_UPDATED_BY,
244 B.LAST_UPDATE_LOGIN = 0
245 where B.VIEW_KEY = X_VIEW_KEY;
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249 else
250 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.UPDATE_VIEW_BY_KEY unable to update view '||X_VIEW_KEY||' due to customization');
251 end if;
252 end if;
253
254 TRANSLATE_VIEW_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_BY_KEY.X_VIEW_KEY ,
255 X_TITLE => UPDATE_VIEW_BY_KEY.X_TITLE ,
256 X_DESCRIPTION => UPDATE_VIEW_BY_KEY.X_DESCRIPTION,
257 X_OWNER => UPDATE_VIEW_BY_KEY.X_OWNER ,
258 X_LAST_UPDATE_DATE => UPDATE_VIEW_BY_KEY.X_LAST_UPDATE_DATE);
259
260 exception
261 when no_data_found then
262 INSERT_VIEW_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_BY_KEY.X_VIEW_KEY ,
263 X_PARENT_VIEW_ID => l_parent_view_id ,
264 X_VIEW_TYPE => UPDATE_VIEW_BY_KEY.X_VIEW_TYPE ,
265 X_APPLICATION_MODULE => UPDATE_VIEW_BY_KEY.X_APPLICATION_MODULE ,
266 X_AM_IMPL_CLASS => UPDATE_VIEW_BY_KEY.X_AM_IMPL_CLASS ,
267 X_AM_IMPL_METHOD => UPDATE_VIEW_BY_KEY.X_AM_IMPL_METHOD ,
268 X_VO_IMPL_CLASS => UPDATE_VIEW_BY_KEY.X_VO_IMPL_CLASS ,
269 X_VO_IMPL_METHOD => UPDATE_VIEW_BY_KEY.X_VO_IMPL_METHOD ,
270 X_VO_INSTANCE => UPDATE_VIEW_BY_KEY.X_VO_INSTANCE ,
271 X_PLSQL_API => UPDATE_VIEW_BY_KEY.X_PLSQL_API ,
272 X_SQL_TEXT => UPDATE_VIEW_BY_KEY.X_SQL_TEXT ,
273 X_CUSTOMIZATION_LEVEL => UPDATE_VIEW_BY_KEY.X_CUSTOMIZATION_LEVEL,
274 X_SEQUENCE => UPDATE_VIEW_BY_KEY.X_SEQUENCE ,
275 X_STATUS => UPDATE_VIEW_BY_KEY.X_STATUS ,
276 X_TITLE => UPDATE_VIEW_BY_KEY.X_TITLE ,
277 X_DESCRIPTION => UPDATE_VIEW_BY_KEY.X_DESCRIPTION ,
278 X_OWNER => UPDATE_VIEW_BY_KEY.X_OWNER ,
279 X_LAST_UPDATE_DATE => UPDATE_VIEW_BY_KEY.X_LAST_UPDATE_DATE );
280 end UPDATE_VIEW_BY_KEY;
281
282 procedure ADD_VIEW_ATTR_LANGUAGE is
283 begin
284 insert into WF_WL_VIEW_ATTRIBUTES_TL (VIEW_ID,
285 ATTRIBUTE_NAME,
286 PROMPT,
287 DESCRIPTION,
288 LANGUAGE,
289 SOURCE_LANG,
290 CREATION_DATE,
291 CREATED_BY,
292 LAST_UPDATE_DATE,
293 LAST_UPDATED_BY,
294 LAST_UPDATE_LOGIN)
295 select B.VIEW_ID,
296 B.ATTRIBUTE_NAME,
297 B.PROMPT,
298 B.DESCRIPTION,
299 L.LANGUAGE_CODE,
300 B.SOURCE_LANG,
301 B.CREATION_DATE,
302 B.CREATED_BY,
303 B.LAST_UPDATE_DATE,
304 B.LAST_UPDATED_BY,
305 B.LAST_UPDATE_LOGIN
306 from WF_WL_VIEW_ATTRIBUTES_TL B, FND_LANGUAGES L
307 where L.INSTALLED_FLAG in ('I', 'B')
308 and B.LANGUAGE = userenv('LANG')
309 and not exists
310 (select NULL
311 from WF_WL_VIEW_ATTRIBUTES_TL TL
312 where TL.VIEW_ID = B.VIEW_ID and
313 TL.ATTRIBUTE_NAME = B.ATTRIBUTE_NAME and
314 TL.LANGUAGE = L.LANGUAGE_CODE);
315 exception
316 when others then
317 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.ADD_VIEW_ATTR_LANGUAGE unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
318 end ADD_VIEW_ATTR_LANGUAGE;
319
320 procedure ADD_LANGUAGE is
321 l_view_id WF_WL_VIEWS_TL.VIEW_ID%TYPE;
322 l_language FND_LANGUAGES.LANGUAGE_CODE%TYPE;
323 begin
324 begin
325 insert into WF_WL_VIEWS_TL (VIEW_ID,
326 TITLE,
327 DESCRIPTION,
328 LANGUAGE,
329 SOURCE_LANG,
330 CREATION_DATE,
331 CREATED_BY,
332 LAST_UPDATE_DATE,
333 LAST_UPDATED_BY,
334 LAST_UPDATE_LOGIN,
335 SECURITY_GROUP_ID)
336 select B.VIEW_ID,
337 B.TITLE,
338 B.DESCRIPTION,
339 L.LANGUAGE_CODE,
340 B.SOURCE_LANG,
341 B.CREATION_DATE,
342 B.CREATED_BY,
343 B.LAST_UPDATE_DATE,
344 B.LAST_UPDATED_BY,
345 B.LAST_UPDATE_LOGIN,
346 B.SECURITY_GROUP_ID
347 from WF_WL_VIEWS_TL B, FND_LANGUAGES L
348 where L.INSTALLED_FLAG in ('I', 'B')
349 and B.LANGUAGE = userenv('LANG')
350 and not exists
351 (select NULL
352 from WF_WL_VIEWS_TL T
353 where B.VIEW_ID = T.VIEW_ID
354 and T.LANGUAGE = L.LANGUAGE_CODE);
355 exception
356 when others then
357 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.ADD_LANGUAGE unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
358 end;
359 ADD_VIEW_ATTR_LANGUAGE;
360 end ADD_LANGUAGE;
361
362 procedure TRANSLATE_VIEW_BY_KEY (X_VIEW_KEY in VARCHAR2,
363 X_TITLE in VARCHAR2,
364 X_DESCRIPTION in VARCHAR2,
365 X_OWNER IN VARCHAR2,
366 X_LAST_UPDATE_DATE IN VARCHAR2) is
367 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
368 l_luby number;
369 l_ludate date;
370 begin
371 -- Translate owner to file_last_updated_by
372 l_luby := fnd_load_util.OWNER_ID(x_owner);
373 -- Translate char last_update_date to date
374 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
375
376 UPDATE WF_WL_VIEWS_TL TL
377 SET TL.TITLE = X_TITLE,
378 TL.DESCRIPTION = X_DESCRIPTION,
379 TL.SOURCE_LANG = userenv('LANG'),
380 TL.LAST_UPDATE_DATE = l_ludate,
381 TL.LAST_UPDATED_BY = l_luby
382 WHERE TL.VIEW_ID = l_view_id and
383 userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
384
385 IF (sql%rowcount=0) THEN
386 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.TRANSLATE_VIEW_BY_KEY unable to translate view '||X_VIEW_KEY||'. Ensure base language is uploaded first');
387 END IF;
388
389 end TRANSLATE_VIEW_BY_KEY;
390
391 procedure INSERT_VIEW_ATTR_BY_KEY (X_VIEW_KEY in VARCHAR2,
392 X_ATTRIBUTE_NAME in VARCHAR2,
393 X_ATTRIBUTE_TYPE in VARCHAR2,
394 X_RENDER in VARCHAR2,
395 X_SEQUENCE in NUMBER ,
396 X_CUSTOMIZATION_LEVEL in VARCHAR2,
397 X_PROMPT in VARCHAR2,
398 X_DESCRIPTION in VARCHAR2,
399 X_OWNER IN VARCHAR2,
400 X_LAST_UPDATE_DATE IN VARCHAR2)
401 is
402 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
403 l_row_id varchar2(50);
404 cursor c_newrow is
405 select ROWID from WF_WL_VIEW_ATTRIBUTES
406 where VIEW_ID = l_view_id and
407 ATTRIBUTE_NAME = X_ATTRIBUTE_NAME;
408 l_luby number;
409 l_ludate date;
410 begin
411 -- Translate owner to file_last_updated_by
412 l_luby := fnd_load_util.OWNER_ID(x_owner);
413 -- Translate char last_update_date to date
414 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
415 insert into WF_WL_VIEW_ATTRIBUTES (VIEW_ID ,
416 ATTRIBUTE_NAME ,
417 ATTRIBUTE_TYPE ,
418 RENDER ,
419 SEQUENCE ,
420 CUSTOMIZATION_LEVEL,
421 CREATION_DATE,
422 CREATED_BY,
423 LAST_UPDATE_DATE,
424 LAST_UPDATED_BY,
425 LAST_UPDATE_LOGIN,
426 SECURITY_GROUP_ID)
427 values (l_view_id ,
428 X_ATTRIBUTE_NAME ,
429 X_ATTRIBUTE_TYPE ,
430 X_RENDER ,
431 X_SEQUENCE ,
432 X_CUSTOMIZATION_LEVEL,
433 l_ludate,
434 l_luby,
435 l_ludate,
436 l_luby,
437 0, --LAST_UPDATE_LOGIN,
438 FND_GLOBAL.SECURITY_GROUP_ID);
439 insert into WF_WL_VIEW_ATTRIBUTES_TL (VIEW_ID,
440 ATTRIBUTE_NAME,
441 PROMPT,
442 DESCRIPTION,
443 LANGUAGE,
444 SOURCE_LANG,
445 CREATION_DATE,
446 CREATED_BY,
447 LAST_UPDATE_DATE,
448 LAST_UPDATED_BY,
449 LAST_UPDATE_LOGIN)
450 select l_view_id,
451 X_ATTRIBUTE_NAME,
452 X_PROMPT,
453 X_DESCRIPTION,
454 L.LANGUAGE_CODE,
455 userenv('LANG'),
456 l_ludate,
457 l_luby,
458 l_ludate,
459 l_luby,
460 0
461 from FND_LANGUAGES L
462 where L.INSTALLED_FLAG in ('I', 'B')
463 and not exists
464 (select NULL
465 from WF_WL_VIEW_ATTRIBUTES_TL TL
466 where TL.VIEW_ID = l_view_id and
467 TL.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME and
468 TL.LANGUAGE = L.LANGUAGE_CODE);
469
470 open c_newrow;
471 fetch c_newrow into l_row_id;
472 if (c_newrow%notfound) then
473 close c_newrow;
474 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_ATTR_BY_KEY unable to create attribute '||X_ATTRIBUTE_NAME||' for view '||X_VIEW_KEY);
475 end if;
476 close c_newrow;
477 end INSERT_VIEW_ATTR_BY_KEY;
478
479 procedure UPDATE_VIEW_ATTR_BY_KEY (X_VIEW_KEY in VARCHAR2,
480 X_ATTRIBUTE_NAME in VARCHAR2,
481 X_ATTRIBUTE_TYPE in VARCHAR2,
482 X_RENDER in VARCHAR2,
483 X_SEQUENCE in NUMBER ,
484 X_CUSTOMIZATION_LEVEL in VARCHAR2,
485 X_PROMPT in VARCHAR2,
486 X_DESCRIPTION in VARCHAR2,
487 X_OWNER IN VARCHAR2,
488 X_LAST_UPDATE_DATE IN VARCHAR2)
489 is
490 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
491 l_luby number;
492 l_ludate date;
493 begin
494 -- Translate owner to file_last_updated_by
495 l_luby := fnd_load_util.OWNER_ID(x_owner);
496 -- Translate char last_update_date to date
497 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
498 update WF_WL_VIEW_ATTRIBUTES B
499 set B.ATTRIBUTE_TYPE = X_ATTRIBUTE_TYPE ,
500 B.RENDER = X_RENDER ,
501 B.SEQUENCE = X_SEQUENCE ,
502 B.CUSTOMIZATION_LEVEL = X_CUSTOMIZATION_LEVEL,
503 B.LAST_UPDATE_DATE = l_ludate,
504 B.LAST_UPDATED_BY = l_luby,
505 B.LAST_UPDATE_LOGIN = 0
506 where B.VIEW_ID = l_view_id and
507 B.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME;
508 if (sql%rowcount=0) then
509 --The new record does not exist, thus create it
510 INSERT_VIEW_ATTR_BY_KEY (X_VIEW_KEY ,
511 X_ATTRIBUTE_NAME ,
512 X_ATTRIBUTE_TYPE ,
513 X_RENDER ,
514 X_SEQUENCE ,
515 X_CUSTOMIZATION_LEVEL,
516 X_PROMPT ,
517 X_DESCRIPTION ,
518 X_OWNER ,
519 X_LAST_UPDATE_DATE);
520 else
521 --The record exists, thus update its translations
522 TRANSLATE_VIEW_ATTR_BY_KEY (X_VIEW_KEY => UPDATE_VIEW_ATTR_BY_KEY.X_VIEW_KEY ,
523 X_ATTRIBUTE_NAME => UPDATE_VIEW_ATTR_BY_KEY.X_ATTRIBUTE_NAME ,
524 X_PROMPT => UPDATE_VIEW_ATTR_BY_KEY.X_PROMPT ,
525 X_DESCRIPTION => UPDATE_VIEW_ATTR_BY_KEY.X_DESCRIPTION ,
526 X_OWNER => UPDATE_VIEW_ATTR_BY_KEY.X_OWNER ,
527 X_LAST_UPDATE_DATE => UPDATE_VIEW_ATTR_BY_KEY.X_LAST_UPDATE_DATE);
528 end if;
529 end UPDATE_VIEW_ATTR_BY_KEY;
530
531 procedure TRANSLATE_VIEW_ATTR_BY_KEY (X_VIEW_KEY in VARCHAR2,
532 X_ATTRIBUTE_NAME in VARCHAR2,
533 X_PROMPT in VARCHAR2,
534 X_DESCRIPTION in VARCHAR2,
535 X_OWNER in VARCHAR2,
536 X_LAST_UPDATE_DATE in VARCHAR2)
537 is
538 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
539 l_luby number;
540 l_ludate date;
541 begin
542 -- Translate owner to file_last_updated_by
543 l_luby := fnd_load_util.OWNER_ID(x_owner);
544 -- Translate char last_update_date to date
545 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
546
547 UPDATE WF_WL_VIEW_ATTRIBUTES_TL TL
548 SET TL.PROMPT = X_PROMPT,
549 TL.DESCRIPTION = X_DESCRIPTION,
550 TL.SOURCE_LANG = userenv('LANG'),
551 TL.LAST_UPDATE_DATE = l_ludate,
552 TL.LAST_UPDATED_BY = l_luby,
553 TL.LAST_UPDATE_LOGIN = 0
554 WHERE TL.VIEW_ID = l_view_id and
555 TL.ATTRIBUTE_NAME = X_ATTRIBUTE_NAME and
556 userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
557 IF (sql%rowcount=0) THEN
558 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.TRANSLATE_VIEW_ATTR_BY_KEY unable to translate attribute '
559 ||X_ATTRIBUTE_NAME||' for view '||X_VIEW_KEY||'. Ensure base language is uploaded first');
560 END IF;
561 end TRANSLATE_VIEW_ATTR_BY_KEY;
562
563 procedure INSERT_VIEW_PARAM_BY_KEY (X_VIEW_KEY in VARCHAR2,
564 X_PARAMETER_NAME in VARCHAR2,
565 X_PARAMETER_SEQ in NUMBER ,
566 X_PARAMETER_TYPE in VARCHAR2,
567 X_PARAMETER_VALUE in VARCHAR2,
568 X_OWNER in VARCHAR2,
569 X_LAST_UPDATE_DATE in VARCHAR2) is
570 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
571 l_row_id varchar2(50);
572 cursor c_newrow is
573 select ROWID from WF_WL_VIEW_PARAMS
574 where VIEW_ID = l_view_id and
575 PARAMETER_NAME = X_PARAMETER_NAME;
576 l_luby number;
577 l_ludate date;
578 begin
579 -- Translate owner to file_last_updated_by
580 l_luby := fnd_load_util.OWNER_ID(x_owner);
581 -- Translate char last_update_date to date
582 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
583
584 insert into WF_WL_VIEW_PARAMS (VIEW_ID ,
585 PARAMETER_NAME,
586 PARAMETER_SEQ ,
587 PARAMETER_TYPE,
588 PARAMETER_VALUE,
589 CREATION_DATE,
590 CREATED_BY,
591 LAST_UPDATE_DATE,
592 LAST_UPDATED_BY,
593 LAST_UPDATE_LOGIN,
594 SECURITY_GROUP_ID)
595 values (l_view_id ,
596 X_PARAMETER_NAME ,
597 X_PARAMETER_SEQ ,
598 X_PARAMETER_TYPE ,
599 X_PARAMETER_VALUE,
600 l_ludate,
601 l_luby,
602 l_ludate,
603 l_luby,
604 0, --LAST_UPDATE_LOGIN,
605 FND_GLOBAL.SECURITY_GROUP_ID);
606
607 open c_newrow;
608 fetch c_newrow into l_row_id;
609 if (c_newrow%notfound) then
610 close c_newrow;
611 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_VIEW_PARAM_BY_KEY unable to create parameter '||X_PARAMETER_NAME||' for view '||X_VIEW_KEY);
612 end if;
613 close c_newrow;
614 end INSERT_VIEW_PARAM_BY_KEY;
615
616 procedure UPDATE_VIEW_PARAM_BY_KEY (X_VIEW_KEY in VARCHAR2,
617 X_PARAMETER_NAME in VARCHAR2,
618 X_PARAMETER_SEQ in NUMBER ,
619 X_PARAMETER_TYPE in VARCHAR2,
620 X_PARAMETER_VALUE in VARCHAR2,
621 X_OWNER in VARCHAR2,
622 X_LAST_UPDATE_DATE in VARCHAR2) is
623 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
624 l_luby number;
625 l_ludate date;
626 begin
627 -- Translate owner to file_last_updated_by
628 l_luby := fnd_load_util.OWNER_ID(x_owner);
629 -- Translate char last_update_date to date
630 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
631
632 update WF_WL_VIEW_PARAMS B
633 set B.PARAMETER_SEQ = X_PARAMETER_SEQ ,
634 B.PARAMETER_TYPE = X_PARAMETER_TYPE ,
635 B.PARAMETER_VALUE = X_PARAMETER_VALUE,
636 B.LAST_UPDATE_DATE = l_ludate,
637 B.LAST_UPDATED_BY = l_luby,
638 B.LAST_UPDATE_LOGIN = 0
639 where VIEW_ID = l_view_id and
640 PARAMETER_NAME= X_PARAMETER_NAME;
641 if (sql%rowcount=0) then
642 --The new record does not exist, thus create it
643 INSERT_VIEW_PARAM_BY_KEY (X_VIEW_KEY ,
644 X_PARAMETER_NAME ,
645 X_PARAMETER_SEQ ,
646 X_PARAMETER_TYPE ,
647 X_PARAMETER_VALUE,
648 X_OWNER,
649 X_LAST_UPDATE_DATE);
650 end if;
651 end UPDATE_VIEW_PARAM_BY_KEY;
652
653 procedure INSERT_REGION_VIEW_BY_KEY (X_REGION_KEY in VARCHAR2,
654 X_VIEW_KEY in VARCHAR2,
655 X_OWNER in VARCHAR2,
656 X_LAST_UPDATE_DATE in VARCHAR2) is
657 l_region_id number := WF_WL_REGIONS_PKG.FIND_REGION_ID(X_REGION_KEY);
658 l_view_id number := FIND_VIEW_ID(X_VIEW_KEY);
659 l_luby number;
660 l_ludate date;
661 begin
662 -- Translate owner to file_last_updated_by
663 l_luby := fnd_load_util.OWNER_ID(x_owner);
664 -- Translate char last_update_date to date
665 l_ludate := nvl(to_date(x_last_update_date, 'YYYY/MM/DD'), sysdate);
666
667 insert into WF_WL_REGION_VIEWS (REGION_ID,
668 VIEW_ID,
669 CREATION_DATE,
670 CREATED_BY,
671 LAST_UPDATE_DATE,
672 LAST_UPDATED_BY,
673 LAST_UPDATE_LOGIN,
674 SECURITY_GROUP_ID)
675 values (l_region_id,
676 l_view_id,
677 l_ludate,
678 l_luby,
679 l_ludate,
680 l_luby,
681 0, --LAST_UPDATE_LOGIN,
682 FND_GLOBAL.SECURITY_GROUP_ID);
683 exception
684 when DUP_VAL_ON_INDEX then
685 null; --The combination already exists. Nothing else to do
686 when others then
687 raise_application_error(-20000, 'WF_WL_VIEWS_PKG.INSERT_REGION_VIEW_BY_KEY unable to create region view '||X_REGION_KEY||'/'||X_VIEW_KEY);
688 end INSERT_REGION_VIEW_BY_KEY;
689 end WF_WL_VIEWS_PKG;