[Home] [Help]
PACKAGE BODY: APPS.JTF_GRID_COLS_PKG
Source
1 package body JTF_GRID_COLS_PKG AS
2 /* $Header: JTFGCPKB.pls 120.4 2006/09/20 07:58:17 snellepa ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_GRID_DATASOURCE_NAME in VARCHAR2,
6 X_GRID_COL_ALIAS in VARCHAR2,
7 X_DB_COL_NAME in VARCHAR2,
8 X_DATA_TYPE_CODE in VARCHAR2,
9 X_QUERY_SEQ in NUMBER,
10 X_SORTABLE_FLAG in VARCHAR2,
11 X_SORT_ASC_BY_DEFAULT_FLAG in VARCHAR2,
12 X_VISIBLE_FLAG in VARCHAR2,
13 X_FREEZE_VISIBLE_FLAG in VARCHAR2,
14 X_DISPLAY_SEQ in NUMBER,
15 X_DISPLAY_TYPE_CODE in VARCHAR2,
16 X_DISPLAY_HSIZE in NUMBER,
17 X_HEADER_ALIGNMENT_CODE in VARCHAR2,
18 X_CELL_ALIGNMENT_CODE in VARCHAR2,
19 X_DISPLAY_FORMAT_TYPE_CODE in VARCHAR2,
20 X_DISPLAY_FORMAT_MASK in VARCHAR2,
21 X_CHECKBOX_CHECKED_VALUE in VARCHAR2,
22 X_CHECKBOX_UNCHECKED_VALUE in VARCHAR2,
23 X_CHECKBOX_OTHER_VALUES in VARCHAR2,
24 X_DB_CURRENCY_CODE_COL in VARCHAR2,
25 X_LABEL_TEXT in VARCHAR2,
26 X_CREATION_DATE in DATE,
27 X_CREATED_BY in NUMBER,
28 X_LAST_UPDATE_DATE in DATE,
29 X_LAST_UPDATED_BY in NUMBER,
30 X_LAST_UPDATE_LOGIN in NUMBER,
31 X_QUERY_ALLOWED_FLAG in VARCHAR2,
32 X_VALIDATION_OBJECT_CODE in VARCHAR2,
33 X_QUERY_DISPLAY_SEQ in NUMBER,
34 X_DB_SORT_COLUMN in VARCHAR2,
35 X_FIRE_POST_QUERY_FLAG in VARCHAR2,
36 X_IMAGE_DESCRIPTION_COL in VARCHAR2
37 ) is
38 cursor C is select ROWID from JTF_GRID_COLS_B
39 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
40 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
41 ;
42 begin
43 -- defaulting the query allowed flag since it is eventually going to be a not null column
44
45 insert into JTF_GRID_COLS_B (
46 GRID_DATASOURCE_NAME,
47 GRID_COL_ALIAS,
48 DB_COL_NAME,
49 DATA_TYPE_CODE,
50 QUERY_SEQ,
51 SORTABLE_FLAG,
52 SORT_ASC_BY_DEFAULT_FLAG,
53 VISIBLE_FLAG,
54 FREEZE_VISIBLE_FLAG,
55 DISPLAY_SEQ,
56 DISPLAY_TYPE_CODE,
57 DISPLAY_HSIZE,
58 HEADER_ALIGNMENT_CODE,
59 CELL_ALIGNMENT_CODE,
60 DISPLAY_FORMAT_TYPE_CODE,
61 DISPLAY_FORMAT_MASK,
62 CHECKBOX_CHECKED_VALUE,
63 CHECKBOX_UNCHECKED_VALUE,
64 CHECKBOX_OTHER_VALUES,
65 DB_CURRENCY_CODE_COL,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_LOGIN,
71 QUERY_ALLOWED_FLAG,
72 VALIDATION_OBJECT_CODE,
73 QUERY_DISPLAY_SEQ,
74 DB_SORT_COLUMN,
75 FIRE_POST_QUERY_FLAG,
76 IMAGE_DESCRIPTION_COL
77 ) values (
78 X_GRID_DATASOURCE_NAME,
79 X_GRID_COL_ALIAS,
80 X_DB_COL_NAME,
81 X_DATA_TYPE_CODE,
82 X_QUERY_SEQ,
83 X_SORTABLE_FLAG,
84 X_SORT_ASC_BY_DEFAULT_FLAG,
85 X_VISIBLE_FLAG,
86 X_FREEZE_VISIBLE_FLAG,
87 X_DISPLAY_SEQ,
88 X_DISPLAY_TYPE_CODE,
89 X_DISPLAY_HSIZE,
90 X_HEADER_ALIGNMENT_CODE,
91 X_CELL_ALIGNMENT_CODE,
92 X_DISPLAY_FORMAT_TYPE_CODE,
93 X_DISPLAY_FORMAT_MASK,
94 X_CHECKBOX_CHECKED_VALUE,
95 X_CHECKBOX_UNCHECKED_VALUE,
96 X_CHECKBOX_OTHER_VALUES,
97 X_DB_CURRENCY_CODE_COL,
98 X_CREATION_DATE,
99 X_CREATED_BY,
100 X_LAST_UPDATE_DATE,
101 X_LAST_UPDATED_BY,
102 X_LAST_UPDATE_LOGIN,
103 decode(X_QUERY_ALLOWED_FLAG,NULL,'F',X_QUERY_ALLOWED_FLAG),
104 X_VALIDATION_OBJECT_CODE,
105 X_QUERY_DISPLAY_SEQ,
106 X_DB_SORT_COLUMN,
107 X_FIRE_POST_QUERY_FLAG,
108 X_IMAGE_DESCRIPTION_COL
109 );
110
111 insert into JTF_GRID_COLS_TL (
112 GRID_DATASOURCE_NAME,
113 GRID_COL_ALIAS,
114 LABEL_TEXT,
115 CREATED_BY,
116 CREATION_DATE,
117 LAST_UPDATED_BY,
118 LAST_UPDATE_DATE,
119 LAST_UPDATE_LOGIN,
120 LANGUAGE,
121 SOURCE_LANG
122 ) select
123 X_GRID_DATASOURCE_NAME,
124 X_GRID_COL_ALIAS,
125 X_LABEL_TEXT,
126 X_CREATED_BY,
127 X_CREATION_DATE,
128 X_LAST_UPDATED_BY,
129 X_LAST_UPDATE_DATE,
130 X_LAST_UPDATE_LOGIN,
131 L.LANGUAGE_CODE,
132 userenv('LANG')
133 from FND_LANGUAGES L
134 where L.INSTALLED_FLAG in ('I', 'B')
135 and not exists
136 (select NULL
137 from JTF_GRID_COLS_TL T
138 where T.GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
139 and T.GRID_COL_ALIAS = X_GRID_COL_ALIAS
140 and T.LANGUAGE = L.LANGUAGE_CODE);
141
142 open c;
143 fetch c into X_ROWID;
144 if (c%notfound) then
145 close c;
146 raise no_data_found;
147 end if;
148 close c;
149
150 -- update the last updated by if it is 'SEED'
151 update_header(x_grid_datasource_name, x_last_updated_by, x_last_update_date);
152
153 end INSERT_ROW;
154
155 procedure LOCK_ROW (
156 X_GRID_DATASOURCE_NAME in VARCHAR2,
157 X_GRID_COL_ALIAS in VARCHAR2,
158 X_DB_COL_NAME in VARCHAR2,
159 X_DATA_TYPE_CODE in VARCHAR2,
160 X_QUERY_SEQ in NUMBER,
161 X_SORTABLE_FLAG in VARCHAR2,
162 X_SORT_ASC_BY_DEFAULT_FLAG in VARCHAR2,
163 X_VISIBLE_FLAG in VARCHAR2,
164 X_FREEZE_VISIBLE_FLAG in VARCHAR2,
165 X_DISPLAY_SEQ in NUMBER,
166 X_DISPLAY_TYPE_CODE in VARCHAR2,
167 X_DISPLAY_HSIZE in NUMBER,
168 X_HEADER_ALIGNMENT_CODE in VARCHAR2,
169 X_CELL_ALIGNMENT_CODE in VARCHAR2,
170 X_DISPLAY_FORMAT_TYPE_CODE in VARCHAR2,
171 X_DISPLAY_FORMAT_MASK in VARCHAR2,
172 X_CHECKBOX_CHECKED_VALUE in VARCHAR2,
173 X_CHECKBOX_UNCHECKED_VALUE in VARCHAR2,
174 X_CHECKBOX_OTHER_VALUES in VARCHAR2,
175 X_DB_CURRENCY_CODE_COL in VARCHAR2,
176 X_LABEL_TEXT in VARCHAR2,
177 X_QUERY_ALLOWED_FLAG in VARCHAR2,
178 X_VALIDATION_OBJECT_CODE in VARCHAR2,
179 X_QUERY_DISPLAY_SEQ in NUMBER,
180 X_DB_SORT_COLUMN in VARCHAR2,
181 X_FIRE_POST_QUERY_FLAG in VARCHAR2,
182 X_IMAGE_DESCRIPTION_COL in VARCHAR2
183 ) is
184 cursor c is select
185 DB_COL_NAME,
186 DATA_TYPE_CODE,
187 QUERY_SEQ,
188 SORTABLE_FLAG,
189 SORT_ASC_BY_DEFAULT_FLAG,
190 VISIBLE_FLAG,
191 FREEZE_VISIBLE_FLAG,
192 DISPLAY_SEQ,
193 DISPLAY_TYPE_CODE,
194 DISPLAY_HSIZE,
195 HEADER_ALIGNMENT_CODE,
196 CELL_ALIGNMENT_CODE,
197 DISPLAY_FORMAT_TYPE_CODE,
198 DISPLAY_FORMAT_MASK,
199 CHECKBOX_CHECKED_VALUE,
200 CHECKBOX_UNCHECKED_VALUE,
201 CHECKBOX_OTHER_VALUES,
202 DB_CURRENCY_CODE_COL,
203 QUERY_ALLOWED_FLAG,
204 VALIDATION_OBJECT_CODE,
205 QUERY_DISPLAY_SEQ,
206 DB_SORT_COLUMN,
207 FIRE_POST_QUERY_FLAG,
208 IMAGE_DESCRIPTION_COL
209 from JTF_GRID_COLS_B
210 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
211 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
212 for update of GRID_DATASOURCE_NAME nowait;
213 recinfo c%rowtype;
214
215 cursor c1 is select
216 LABEL_TEXT,
217 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
218 from JTF_GRID_COLS_TL
219 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
220 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
221 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
222 for update of GRID_DATASOURCE_NAME nowait;
223 begin
224 open c;
225 fetch c into recinfo;
226 if (c%notfound) then
227 close c;
228 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
229 app_exception.raise_exception;
230 end if;
231 close c;
232 if ( (recinfo.DB_COL_NAME = X_DB_COL_NAME)
233 AND (recinfo.DATA_TYPE_CODE = X_DATA_TYPE_CODE)
234 AND (recinfo.QUERY_SEQ = X_QUERY_SEQ)
235 AND (recinfo.SORTABLE_FLAG = X_SORTABLE_FLAG)
236 AND (recinfo.SORT_ASC_BY_DEFAULT_FLAG = X_SORT_ASC_BY_DEFAULT_FLAG)
237 AND (recinfo.VISIBLE_FLAG = X_VISIBLE_FLAG)
238 AND (recinfo.FREEZE_VISIBLE_FLAG = X_FREEZE_VISIBLE_FLAG)
239 AND (recinfo.DISPLAY_SEQ = X_DISPLAY_SEQ)
240 AND (recinfo.DISPLAY_TYPE_CODE = X_DISPLAY_TYPE_CODE)
241 AND (recinfo.DISPLAY_HSIZE = X_DISPLAY_HSIZE)
242 AND (recinfo.HEADER_ALIGNMENT_CODE = X_HEADER_ALIGNMENT_CODE)
243 AND (recinfo.CELL_ALIGNMENT_CODE = X_CELL_ALIGNMENT_CODE)
244 AND ((recinfo.DISPLAY_FORMAT_TYPE_CODE = X_DISPLAY_FORMAT_TYPE_CODE)
245 OR ((recinfo.DISPLAY_FORMAT_TYPE_CODE is null) AND (X_DISPLAY_FORMAT_TYPE_CODE is null)))
246 AND ((recinfo.DISPLAY_FORMAT_MASK = X_DISPLAY_FORMAT_MASK)
247 OR ((recinfo.DISPLAY_FORMAT_MASK is null) AND (X_DISPLAY_FORMAT_MASK is null)))
248 AND ((recinfo.CHECKBOX_CHECKED_VALUE = X_CHECKBOX_CHECKED_VALUE)
249 OR ((recinfo.CHECKBOX_CHECKED_VALUE is null) AND (X_CHECKBOX_CHECKED_VALUE is null)))
250 AND ((recinfo.CHECKBOX_UNCHECKED_VALUE = X_CHECKBOX_UNCHECKED_VALUE)
251 OR ((recinfo.CHECKBOX_UNCHECKED_VALUE is null) AND (X_CHECKBOX_UNCHECKED_VALUE is null)))
252 AND ((recinfo.CHECKBOX_OTHER_VALUES = X_CHECKBOX_OTHER_VALUES)
253 OR ((recinfo.CHECKBOX_OTHER_VALUES is null) AND (X_CHECKBOX_OTHER_VALUES is null)))
254 AND ((recinfo.DB_CURRENCY_CODE_COL = X_DB_CURRENCY_CODE_COL)
255 OR ((recinfo.DB_CURRENCY_CODE_COL is null) AND (X_DB_CURRENCY_CODE_COL is null)))
256 AND (recinfo.QUERY_ALLOWED_FLAG = X_QUERY_ALLOWED_FLAG)
257 AND ((recinfo.VALIDATION_OBJECT_CODE = X_VALIDATION_OBJECT_CODE)
258 OR ((recinfo.VALIDATION_OBJECT_CODE is null) AND (X_VALIDATION_OBJECT_CODE is null)))
259 AND ((recinfo.QUERY_DISPLAY_SEQ = X_QUERY_DISPLAY_SEQ)
260 OR ((recinfo.QUERY_DISPLAY_SEQ is null) AND (X_QUERY_DISPLAY_SEQ is null)))
261 AND ((recinfo.DB_SORT_COLUMN = X_DB_SORT_COLUMN)
262 OR ((recinfo.DB_SORT_COLUMN is null) AND (X_DB_SORT_COLUMN is null)))
263 AND ((recinfo.FIRE_POST_QUERY_FLAG = X_FIRE_POST_QUERY_FLAG)
264 OR ((recinfo.FIRE_POST_QUERY_FLAG is null) AND (X_FIRE_POST_QUERY_FLAG is null)))
265 AND ((recinfo.IMAGE_DESCRIPTION_COL = X_IMAGE_DESCRIPTION_COL)
266 OR ((recinfo.IMAGE_DESCRIPTION_COL is null) AND (X_IMAGE_DESCRIPTION_COL is null)))
267 ) then
268 null;
269 else
270 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
271 app_exception.raise_exception;
272 end if;
273
274 for tlinfo in c1 loop
275 if (tlinfo.BASELANG = 'Y') then
276 if ( (tlinfo.LABEL_TEXT = X_LABEL_TEXT)
277 ) then
278 null;
279 else
280 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
281 app_exception.raise_exception;
282 end if;
283 end if;
284 end loop;
285 return;
286 end LOCK_ROW;
287
288 procedure UPDATE_ROW (
289 X_GRID_DATASOURCE_NAME in VARCHAR2,
290 X_GRID_COL_ALIAS in VARCHAR2,
291 X_DB_COL_NAME in VARCHAR2,
292 X_DATA_TYPE_CODE in VARCHAR2,
293 X_QUERY_SEQ in NUMBER,
294 X_SORTABLE_FLAG in VARCHAR2,
295 X_SORT_ASC_BY_DEFAULT_FLAG in VARCHAR2,
296 X_VISIBLE_FLAG in VARCHAR2,
297 X_FREEZE_VISIBLE_FLAG in VARCHAR2,
298 X_DISPLAY_SEQ in NUMBER,
299 X_DISPLAY_TYPE_CODE in VARCHAR2,
300 X_DISPLAY_HSIZE in NUMBER,
301 X_HEADER_ALIGNMENT_CODE in VARCHAR2,
302 X_CELL_ALIGNMENT_CODE in VARCHAR2,
303 X_DISPLAY_FORMAT_TYPE_CODE in VARCHAR2,
304 X_DISPLAY_FORMAT_MASK in VARCHAR2,
305 X_CHECKBOX_CHECKED_VALUE in VARCHAR2,
306 X_CHECKBOX_UNCHECKED_VALUE in VARCHAR2,
307 X_CHECKBOX_OTHER_VALUES in VARCHAR2,
308 X_DB_CURRENCY_CODE_COL in VARCHAR2,
309 X_LABEL_TEXT in VARCHAR2,
310 X_LAST_UPDATE_DATE in DATE,
311 X_LAST_UPDATED_BY in NUMBER,
312 X_LAST_UPDATE_LOGIN in NUMBER,
313 X_QUERY_ALLOWED_FLAG in VARCHAR2,
314 X_VALIDATION_OBJECT_CODE in VARCHAR2,
315 X_QUERY_DISPLAY_SEQ in NUMBER,
316 X_DB_SORT_COLUMN in VARCHAR2,
317 X_FIRE_POST_QUERY_FLAG in VARCHAR2,
318 X_IMAGE_DESCRIPTION_COL in VARCHAR2
319 ) is
320 begin
321 update JTF_GRID_COLS_B set
322 DB_COL_NAME = X_DB_COL_NAME,
323 DATA_TYPE_CODE = X_DATA_TYPE_CODE,
324 QUERY_SEQ = X_QUERY_SEQ,
325 SORTABLE_FLAG = X_SORTABLE_FLAG,
326 SORT_ASC_BY_DEFAULT_FLAG = X_SORT_ASC_BY_DEFAULT_FLAG,
327 VISIBLE_FLAG = X_VISIBLE_FLAG,
328 FREEZE_VISIBLE_FLAG = X_FREEZE_VISIBLE_FLAG,
329 DISPLAY_SEQ = X_DISPLAY_SEQ,
330 DISPLAY_TYPE_CODE = X_DISPLAY_TYPE_CODE,
331 DISPLAY_HSIZE = X_DISPLAY_HSIZE,
332 HEADER_ALIGNMENT_CODE = X_HEADER_ALIGNMENT_CODE,
333 CELL_ALIGNMENT_CODE = X_CELL_ALIGNMENT_CODE,
334 DISPLAY_FORMAT_TYPE_CODE = X_DISPLAY_FORMAT_TYPE_CODE,
335 DISPLAY_FORMAT_MASK = X_DISPLAY_FORMAT_MASK,
336 CHECKBOX_CHECKED_VALUE = X_CHECKBOX_CHECKED_VALUE,
337 CHECKBOX_UNCHECKED_VALUE = X_CHECKBOX_UNCHECKED_VALUE,
338 CHECKBOX_OTHER_VALUES = X_CHECKBOX_OTHER_VALUES,
339 DB_CURRENCY_CODE_COL = X_DB_CURRENCY_CODE_COL,
340 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
343 QUERY_ALLOWED_FLAG = X_QUERY_ALLOWED_FLAG,
344 VALIDATION_OBJECT_CODE = X_VALIDATION_OBJECT_CODE,
345 QUERY_DISPLAY_SEQ = X_QUERY_DISPLAY_SEQ ,
346 DB_SORT_COLUMN = X_DB_SORT_COLUMN,
347 FIRE_POST_QUERY_FLAG = X_FIRE_POST_QUERY_FLAG,
348 IMAGE_DESCRIPTION_COL = X_IMAGE_DESCRIPTION_COL
349 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
350 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
351
352 if (sql%notfound) then
353 raise no_data_found;
354 end if;
355
356 update JTF_GRID_COLS_TL set
357 LABEL_TEXT = X_LABEL_TEXT,
358 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
359 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
360 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
361 SOURCE_LANG = userenv('LANG')
362 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
363 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
367 raise no_data_found;
364 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
365
366 if (sql%notfound) then
368 end if;
369
370 -- update the last updated by if it is 'SEED'
371 update_header(x_grid_datasource_name, x_last_updated_by, x_last_update_date);
372
373 end UPDATE_ROW;
374
375
376 procedure DELETE_ROW (
377 X_GRID_DATASOURCE_NAME in VARCHAR2,
378 X_GRID_COL_ALIAS in VARCHAR2
379 ) is
380 grid_id JTF_CUSTOM_GRID_COLS.custom_grid_id%TYPE;
381 WasError Boolean :=False;
382 begin
383 --null;
384 Begin
385 select custom_grid_id into grid_id from
386 JTF_CUSTOM_GRID_COLS where grid_datasource_name=X_GRID_DATASOURCE_NAME;
387 Exception
388 When Others then
389 WasError:=True;
390 End;
391
392 If Not WasError Then
393 delete from JTF_CUSTOM_GRID_COLS
394 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
395 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
396 and custom_grid_id=grid_id;
397 End If;
398
399 delete from JTF_GRID_COLS_TL
400 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
401 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
402
403 delete from JTF_GRID_COLS_B
404 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
405 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
406
407 if (sql%notfound) then
408 raise no_data_found;
409 end if;
410 end DELETE_ROW;
411
412 procedure DELETE_ALL_ROWS (
413 X_GRID_DATASOURCE_NAME in VARCHAR2
414 ) is
415
416 grid_id JTF_CUSTOM_GRID_COLS.custom_grid_id%TYPE;
417 wasError Boolean :=False;
418 begin
419 -- null;
420
421 Begin
422 SELECT CUSTOM_GRID_ID INTO GRID_ID FROM
423 JTF_CUSTOM_GRID_COLS where grid_datasource_name=X_GRID_DATASOURCE_NAME;
424 Exception
425 When Others Then
426 WasError:=True;
427 End;
428
429 If Not WasError then
430 delete from JTF_CUSTOM_GRID_COLS
431 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
432 and CUSTOM_GRID_ID=GRID_ID;
433 End If;
434
435 delete from JTF_GRID_COLS_TL
436 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME;
437
438 delete from JTF_GRID_COLS_B
439 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME;
440
441 if (sql%notfound) then
442 raise no_data_found;
443 end if;
444 end DELETE_ALL_ROWS;
445
446
447 procedure ADD_LANGUAGE
448 is
449 begin
450 delete from JTF_GRID_COLS_TL T
451 where not exists
452 (select NULL
453 from JTF_GRID_COLS_B B
454 where B.GRID_DATASOURCE_NAME = T.GRID_DATASOURCE_NAME
455 and B.GRID_COL_ALIAS = T.GRID_COL_ALIAS
456 );
457
458 update JTF_GRID_COLS_TL T set (
459 LABEL_TEXT
460 ) = (select
461 B.LABEL_TEXT
462 from JTF_GRID_COLS_TL B
463 where B.GRID_DATASOURCE_NAME = T.GRID_DATASOURCE_NAME
464 and B.GRID_COL_ALIAS = T.GRID_COL_ALIAS
465 and B.LANGUAGE = T.SOURCE_LANG)
466 where (
467 T.GRID_DATASOURCE_NAME,
468 T.GRID_COL_ALIAS,
469 T.LANGUAGE
470 ) in (select
471 SUBT.GRID_DATASOURCE_NAME,
472 SUBT.GRID_COL_ALIAS,
473 SUBT.LANGUAGE
474 from JTF_GRID_COLS_TL SUBB, JTF_GRID_COLS_TL SUBT
475 where SUBB.GRID_DATASOURCE_NAME = SUBT.GRID_DATASOURCE_NAME
476 and SUBB.GRID_COL_ALIAS = SUBT.GRID_COL_ALIAS
477 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
478 and (SUBB.LABEL_TEXT <> SUBT.LABEL_TEXT
479 ));
480
481 insert into JTF_GRID_COLS_TL (
482 GRID_DATASOURCE_NAME,
483 GRID_COL_ALIAS,
484 LABEL_TEXT,
485 CREATED_BY,
486 CREATION_DATE,
487 LAST_UPDATED_BY,
488 LAST_UPDATE_DATE,
489 LAST_UPDATE_LOGIN,
490 LANGUAGE,
491 SOURCE_LANG
492 ) select
493 B.GRID_DATASOURCE_NAME,
494 B.GRID_COL_ALIAS,
495 B.LABEL_TEXT,
496 B.CREATED_BY,
497 B.CREATION_DATE,
498 B.LAST_UPDATED_BY,
499 B.LAST_UPDATE_DATE,
500 B.LAST_UPDATE_LOGIN,
501 L.LANGUAGE_CODE,
502 B.SOURCE_LANG
503 from JTF_GRID_COLS_TL B, FND_LANGUAGES L
504 where L.INSTALLED_FLAG in ('I', 'B')
505 and B.LANGUAGE = userenv('LANG')
506 and not exists
507 (select NULL
508 from JTF_GRID_COLS_TL T
509 where T.GRID_DATASOURCE_NAME = B.GRID_DATASOURCE_NAME
510 and T.GRID_COL_ALIAS = B.GRID_COL_ALIAS
511 and T.LANGUAGE = L.LANGUAGE_CODE);
512 end ADD_LANGUAGE;
513
514
515 procedure LOAD_ROW (X_GRID_DATASOURCE_NAME in VARCHAR2,
516 X_GRID_COL_ALIAS in VARCHAR2,
517 X_DB_COL_NAME in VARCHAR2,
518 X_DATA_TYPE_CODE in VARCHAR2,
519 X_QUERY_SEQ in NUMBER,
520 X_SORTABLE_FLAG in VARCHAR2,
521 X_SORT_ASC_BY_DEFAULT_FLAG in VARCHAR2,
522 X_VISIBLE_FLAG in VARCHAR2,
523 X_FREEZE_VISIBLE_FLAG in VARCHAR2,
524 X_DISPLAY_SEQ in NUMBER,
525 X_DISPLAY_TYPE_CODE in VARCHAR2,
526 X_DISPLAY_HSIZE in NUMBER,
527 X_HEADER_ALIGNMENT_CODE in VARCHAR2,
528 X_CELL_ALIGNMENT_CODE in VARCHAR2,
529 X_DISPLAY_FORMAT_TYPE_CODE in VARCHAR2,
533 X_CHECKBOX_OTHER_VALUES in VARCHAR2,
530 X_DISPLAY_FORMAT_MASK in VARCHAR2,
531 X_CHECKBOX_CHECKED_VALUE in VARCHAR2,
532 X_CHECKBOX_UNCHECKED_VALUE in VARCHAR2,
534 X_DB_CURRENCY_CODE_COL in VARCHAR2,
535 X_LABEL_TEXT in VARCHAR2,
536 X_OWNER in VARCHAR2,
537 X_QUERY_ALLOWED_FLAG in VARCHAR2,
538 X_VALIDATION_OBJECT_CODE in VARCHAR2,
539 X_QUERY_DISPLAY_SEQ in NUMBER,
540 X_DB_SORT_COLUMN in VARCHAR2,
541 X_FIRE_POST_QUERY_FLAG in VARCHAR2,
542 X_IMAGE_DESCRIPTION_COL in VARCHAR2,
543 X_CUSTOM_MODE in VARCHAR2,
544 X_LAST_UPDATE_DATE in VARCHAR2) is
545 row_id varchar2(64);
546 user number;
547 dummy varchar2(1);
548 f_luby number; -- entity owner in file
549 f_ludate date; -- entity update date in file
550 db_luby number; -- entity owner in db
551 db_ludate date; -- entity update date in db
552 cursor c_check_unique is
553 select 'x'
554 from jtf_grid_cols_vl
555 where grid_datasource_name = X_GRID_DATASOURCE_NAME
556 and grid_col_alias <> X_GRID_COL_ALIAS
557 and label_text = X_LABEL_TEXT;
558 begin
559
560 -- Translate owner to file_last_updated_by
561 f_luby := fnd_load_util.owner_id(X_OWNER);
562
563 -- Translate char last_update_date to date
564 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
565
566 begin
567
568
569 select LAST_UPDATED_BY, LAST_UPDATE_DATE
570 into db_luby, db_ludate
571 from JTF_GRID_COLS_B
572 where GRID_DATASOURCE_NAME = x_grid_datasource_name
573 AND GRID_COL_ALIAS = X_GRID_COL_ALIAS;
574
575
576 /*
577 select 'X'
578 into dummy
579 from JTF_GRID_COLS_B
580 where GRID_DATASOURCE_NAME = x_grid_datasource_name;
581 */
582
583 -- Test for customization and version
584 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
585 db_ludate, X_CUSTOM_MODE)) then
586
587 UPDATE_ROW (X_GRID_DATASOURCE_NAME => X_GRID_DATASOURCE_NAME
588 ,X_GRID_COL_ALIAS => X_GRID_COL_ALIAS
589 ,X_DB_COL_NAME => X_DB_COL_NAME
590 ,X_DATA_TYPE_CODE => X_DATA_TYPE_CODE
591 ,X_QUERY_SEQ => X_QUERY_SEQ
592 ,X_SORTABLE_FLAG => X_SORTABLE_FLAG
593 ,X_SORT_ASC_BY_DEFAULT_FLAG => X_SORT_ASC_BY_DEFAULT_FLAG
594 ,X_VISIBLE_FLAG => X_VISIBLE_FLAG
595 ,X_FREEZE_VISIBLE_FLAG => X_FREEZE_VISIBLE_FLAG
596 ,X_DISPLAY_SEQ => X_DISPLAY_SEQ
597 ,X_DISPLAY_TYPE_CODE => X_DISPLAY_TYPE_CODE
598 ,X_DISPLAY_HSIZE => X_DISPLAY_HSIZE
599 ,X_HEADER_ALIGNMENT_CODE => X_HEADER_ALIGNMENT_CODE
600 ,X_CELL_ALIGNMENT_CODE => X_CELL_ALIGNMENT_CODE
601 ,X_DISPLAY_FORMAT_TYPE_CODE => X_DISPLAY_FORMAT_TYPE_CODE
602 ,X_DISPLAY_FORMAT_MASK => X_DISPLAY_FORMAT_MASK
603 ,X_CHECKBOX_CHECKED_VALUE => X_CHECKBOX_CHECKED_VALUE
604 ,X_CHECKBOX_UNCHECKED_VALUE => X_CHECKBOX_UNCHECKED_VALUE
605 ,X_CHECKBOX_OTHER_VALUES => X_CHECKBOX_OTHER_VALUES
606 ,X_DB_CURRENCY_CODE_COL => X_DB_CURRENCY_CODE_COL
607 ,X_LABEL_TEXT => X_LABEL_TEXT
608 ,X_LAST_UPDATE_DATE => f_ludate
609 ,X_LAST_UPDATED_BY => f_luby
610 ,X_LAST_UPDATE_LOGIN => 0
611 ,X_QUERY_ALLOWED_FLAG => X_QUERY_ALLOWED_FLAG
612 ,X_VALIDATION_OBJECT_CODE => X_VALIDATION_OBJECT_CODE
613 ,X_QUERY_DISPLAY_SEQ => X_QUERY_DISPLAY_SEQ
614 ,X_DB_SORT_COLUMN => X_DB_SORT_COLUMN
615 ,X_FIRE_POST_QUERY_FLAG => X_FIRE_POST_QUERY_FLAG
616 ,X_IMAGE_DESCRIPTION_COL => X_IMAGE_DESCRIPTION_COL);
617 end if;
618
619
620 exception
621 when no_data_found then
622 INSERT_ROW (X_ROWID => row_id
623 ,X_GRID_DATASOURCE_NAME => X_GRID_DATASOURCE_NAME
624 ,X_GRID_COL_ALIAS => X_GRID_COL_ALIAS
625 ,X_DB_COL_NAME => X_DB_COL_NAME
626 ,X_DATA_TYPE_CODE => X_DATA_TYPE_CODE
627 ,X_QUERY_SEQ => X_QUERY_SEQ
628 ,X_SORTABLE_FLAG => X_SORTABLE_FLAG
629 ,X_SORT_ASC_BY_DEFAULT_FLAG => X_SORT_ASC_BY_DEFAULT_FLAG
630 ,X_VISIBLE_FLAG => X_VISIBLE_FLAG
631 ,X_FREEZE_VISIBLE_FLAG => X_FREEZE_VISIBLE_FLAG
632 ,X_DISPLAY_SEQ => X_DISPLAY_SEQ
633 ,X_DISPLAY_TYPE_CODE => X_DISPLAY_TYPE_CODE
634 ,X_DISPLAY_HSIZE => X_DISPLAY_HSIZE
635 ,X_HEADER_ALIGNMENT_CODE => X_HEADER_ALIGNMENT_CODE
636 ,X_CELL_ALIGNMENT_CODE => X_CELL_ALIGNMENT_CODE
637 ,X_DISPLAY_FORMAT_TYPE_CODE => X_DISPLAY_FORMAT_TYPE_CODE
638 ,X_DISPLAY_FORMAT_MASK => X_DISPLAY_FORMAT_MASK
639 ,X_CHECKBOX_CHECKED_VALUE => X_CHECKBOX_CHECKED_VALUE
640 ,X_CHECKBOX_UNCHECKED_VALUE => X_CHECKBOX_UNCHECKED_VALUE
641 ,X_CHECKBOX_OTHER_VALUES => X_CHECKBOX_OTHER_VALUES
642 ,X_DB_CURRENCY_CODE_COL => X_DB_CURRENCY_CODE_COL
643 ,X_LABEL_TEXT => X_LABEL_TEXT
644 ,X_CREATION_DATE => f_ludate
645 ,X_CREATED_BY => f_luby
646 ,X_LAST_UPDATE_DATE => f_ludate
647 ,X_LAST_UPDATED_BY => f_luby
648 ,X_LAST_UPDATE_LOGIN => 0
649 ,X_QUERY_ALLOWED_FLAG => X_QUERY_ALLOWED_FLAG
650 ,X_VALIDATION_OBJECT_CODE => X_VALIDATION_OBJECT_CODE
651 ,X_QUERY_DISPLAY_SEQ => X_QUERY_DISPLAY_SEQ
655 end;
652 ,X_DB_SORT_COLUMN => X_DB_SORT_COLUMN
653 ,X_FIRE_POST_QUERY_FLAG => X_FIRE_POST_QUERY_FLAG
654 ,X_IMAGE_DESCRIPTION_COL => X_IMAGE_DESCRIPTION_COL);
656 end LOAD_ROW;
657
658
659 procedure TRANSLATE_ROW(X_GRID_DATASOURCE_NAME in VARCHAR2,
660 X_GRID_COL_ALIAS in VARCHAR2,
661 X_LABEL_TEXT in VARCHAR2,
662 X_OWNER in VARCHAR2,
663 X_CUSTOM_MODE in VARCHAR2,
664 X_LAST_UPDATE_DATE in VARCHAR2) is
665 dummy varchar2(1);
666 f_luby number; -- entity owner in file
667 f_ludate date; -- entity update date in file
668 db_luby number; -- entity owner in db
669 db_ludate date; -- entity update date in db
670 cursor c_check_unique is
671 select 'x'
672 from jtf_grid_cols_vl
673 where grid_datasource_name = X_GRID_DATASOURCE_NAME
674 and grid_col_alias <> X_GRID_COL_ALIAS
675 and label_text = X_LABEL_TEXT;
676 begin
677 -- Translate owner to file_last_updated_by
678 f_luby := fnd_load_util.owner_id(X_OWNER);
679
680 -- Translate char last_update_date to date
681 f_ludate := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
682
683
684 select LAST_UPDATED_BY, LAST_UPDATE_DATE
685 into db_luby, db_ludate
686 from JTF_GRID_COLS_TL
687 where GRID_DATASOURCE_NAME = x_grid_datasource_name
688 and GRID_COL_ALIAS = X_GRID_COL_ALIAS
689 and LANGUAGE = userenv('LANG');
690
691 -- Test for customization and version
692 if (fnd_load_util.upload_test(f_luby, f_ludate, db_luby,
693 db_ludate, X_CUSTOM_MODE)) then
694
695 -- we we need to check that we are not
696 -- going to violate the unique key
697 -- on jtf_grid_cols_tl, if so add an '@'
698 -- character to the label_text and try again
699 -- this will be a recursive call until the
700 -- label is unique
701 open c_check_unique;
702 fetch c_check_unique into dummy;
703 if c_check_unique%FOUND then
704 close c_check_unique;
705 JTF_GRID_COLS_PKG.TRANSLATE_ROW
706 (X_GRID_DATASOURCE_NAME => X_GRID_DATASOURCE_NAME
707 ,X_GRID_COL_ALIAS => X_GRID_COL_ALIAS
708 ,X_LABEL_TEXT => '@'||substrb(X_LABEL_TEXT,1,77)
709 ,X_OWNER => X_OWNER
710 ,X_CUSTOM_MODE => X_CUSTOM_MODE
711 ,X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE);
712 else
713 close c_check_unique;
714 begin
715 update JTF_GRID_COLS_TL
716 set LABEL_TEXT = nvl(X_LABEL_TEXT,LABEL_TEXT)
717 ,LAST_UPDATE_DATE = f_ludate
718 ,LAST_UPDATED_BY = f_luby
719 ,LAST_UPDATE_LOGIN = 0
720 ,SOURCE_LANG = userenv('LANG')
721 where userenv('LANG') in (LANGUAGE,SOURCE_LANG)
722 and GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
723 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
724 exception
725 when no_data_found then
726 -- Do not insert missing translations, skip this row
727 null;
728 end;
729
730 end if;
731 end if;
732 end TRANSLATE_ROW;
733 function getVersion return VARCHAR2 IS
734 begin
735 RETURN('$Header: JTFGCPKB.pls 120.4 2006/09/20 07:58:17 snellepa ship $');
736 end getVersion;
737
738 procedure DELETE_ROW (
739 X_GRID_DATASOURCE_NAME in VARCHAR2,
740 X_LAST_UPDATED_BY in number,
741 X_LAST_UPDATE_DATE in date
742 ) is
743 grid_id JTF_CUSTOM_GRID_COLS.custom_grid_id%TYPE;
744 WasError boolean :=False;
745 begin
746
747 begin
748 select custom_grid_id into grid_id from
749 JTF_CUSTOM_GRID_COLS where grid_datasource_name=X_GRID_DATASOURCE_NAME;
750 exception
751 when others then
752 wasError:=True;
753 end;
754
755 if Not WasError then
756 delete from JTF_CUSTOM_GRID_COLS
757 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
758 and custom_grid_id=grid_id;
759 end If;
760
761 delete from JTF_GRID_COLS_TL
762 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME ;
763
764 delete from JTF_GRID_COLS_B
765 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME ;
766
767 update_header(x_grid_datasource_name, x_last_updated_by, x_last_update_date);
768
769 if (sql%notfound) then
770 raise no_data_found;
771 end if;
772 end DELETE_ROW;
773
774 procedure update_header(p_datasource_name in varchar2,
775 p_owner in number:= fnd_global.user_id,
776 p_last_update_date in date ) is
777 l_header_owner number;
778 begin
779 /* select last_updated_by
780 into l_header_owner
781 from jtf_grid_datasources_b
782 where grid_datasource_name = p_datasource_name;
783
784 -- if header is not 'SEED' and the cols are modified by 'USER' only then
785 -- update the header
786 if (l_header_owner = 1 and p_owner <> 1) then
787 */
788 update jtf_grid_datasources_b
789 set last_updated_by = p_owner,
790 last_update_date = p_last_update_date
791 where grid_Datasource_name = p_datasource_name;
792 end update_header;
793
794
798 X_LAST_UPDATED_BY in NUMBER,
795 procedure DELETE_ROW (
796 X_GRID_DATASOURCE_NAME in VARCHAR2,
797 X_GRID_COL_ALIAS in VARCHAR2,
799 X_LAST_UPDATE_DATE in date
800 --X_CUSTOM_MODE in VARCHAR2 := 'FORCE'
801 ) is
802 begin
803 delete from JTF_CUSTOM_GRID_COLS
804 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
805 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
806
807 delete from JTF_GRID_COLS_TL
808 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
809 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
810
811 delete from JTF_GRID_COLS_B
812 where GRID_DATASOURCE_NAME = X_GRID_DATASOURCE_NAME
813 and GRID_COL_ALIAS = X_GRID_COL_ALIAS;
814
815 update_header(x_grid_datasource_name, x_last_updated_by, x_last_update_date);
816
817 if (sql%notfound) then
818 raise no_data_found;
819 end if;
820
821
822 end;
823
824 end JTF_GRID_COLS_PKG;