1 PACKAGE BODY jtf_gridDB as
2 /* $Header: JTFGRDDB.pls 120.2 2006/01/19 03:14:44 snellepa ship $ */
3 -----------------------
4 -- Version 11.5.3 - 2.03 09-Aug-2000
5 -----------------------
6 fetchSize number := 50;
7 -- Outgoing persistance versions
8 tabVersion varchar2(5) := '1';
9 recVersion varchar2(5) := '1';
10 colDefVersion varchar2(5) := '1';
11 serverInitDate date := sysdate;
12
13 -- Incoming persistance versions
14 clientTabVersion varchar2(5) := '1';
15 clientColDefVersion varchar2(5) := '1';
16
17 lineFeed varchar2(10) := jtf_dbstring_utils.getLineFeed;
18 nullValue varchar2(10) := jtf_dbstring_utils.getNullString;
19
20 tableDefs tabDefTabType;
21 columnDefs colDefTabType;
22 temp_columnDefs colDefTabType;
23
24 INVALID_DATASOURCE exception;
25 INVALID_GRID exception;
26 INVALID_PROPERTY exception;
27
28 type bindVarRecType is record
29 (
30 gridName varchar2(256)
31 ,variableName varchar2(60)
32 ,variableDataType varchar2(30) -- C, D, N (Character, Date or Number)
33 ,variableCharValue varchar2(2000)
34 ,variableDateValue date
35 ,variableNumberValue number
36 );
37
38 type bindVarTabType is table of bindVarRecType
39 index by binary_integer;
40
41 bindVariables bindVarTabType;
42
43 USER_UI_DEFAULT_FOLDER constant varchar2(80) := 'USER_DEFAULT';
44
45 -----------------------------------------------------------------------------
46 -- VARIABLES FOR ERROR_TYPES, make sure handleErrors() can handle any additions.
47 -----------------------------------------------------------------------------
48 INTERNAL_ERROR constant pls_integer := 1;
49 APPLICATION_ERROR constant pls_integer := 2;
50 INVALID_PROPERTY_ERROR constant pls_integer := 3;
51 INVALID_GRID_ERROR constant pls_integer := 4;
52 INIT_ERROR constant pls_integer := 5;
53 MAXLENGTH_EXCEEDED_ERROR constant pls_integer := 6;
54 INVALID_DATASOURCE_ERROR constant pls_integer := 7;
55 MISSING_SORT_COL_ERROR constant pls_integer := 8;
56 INVALID_COLUMNALIAS_ERROR constant pls_integer := 9;
57 ILLEGAL_SORT_COLUMN_ERROR constant pls_integer := 10;
58
59 -- separator for image name and image description
60 IMAGE_SEPARATOR varchar2(1) := '*';
61
62 procedure raise_error is
63 begin
64 app_exception.raise_exception;
65 end raise_error;
66
67 -----------------------------------------------------------------------------
68 -- Description:
69 -----------------------------------------------------------------------------
70 procedure validateServer(p_date in date, gridName in varchar2) is
71 begin
72 if p_date is not NULL then
73 if serverInitDate > p_date then
74 -- dbms_output.put_line('in validation server');
75 FND_MESSAGE.SET_NAME('JTF', 'JTF_GRID_SERVER_VALIDATION');
76 FND_MESSAGE.SET_TOKEN('GRID', gridName, FALSE);
77 -- FND_MSG_PUB.ADD;
78 raise_error;
79 end if;
80 end if;
81 end;
82
83 ----------------------------------------------------------------------------
84 function findTableDefIndex(gridName in varchar2) return binary_integer is
85 i binary_integer;
86 begin
87 i := tableDefs.FIRST;
88 while i is not null loop
89 if tableDefs(i).gridName = gridName then
90 return i;
91 end if;
92 i := tableDefs.next(i);
93 end loop;
94 return null;
95 /*
96 exception
97 when OTHERS then
98 handleErrors(INTERNAL_ERROR,'findTableDefIndex',gridName,null,SQLERRM);
99 */
100 end findTableDefIndex;
101
102 -----------------------------------------------------------------------------
103 -- Description:
104 --
105 -- handle Error messages
106 --
107 -- Notes: MUST NEVER BE CALLED FROM OUTSIDE OF THIS PACKAGE!
108 --
109 -- @param method The method where the error occurred.
110 -- @param tableIndex the index pointing to the row in tableDefs for the given grid
111 -- @param message The error message
112 -- @param SQLError The SQLERRM error message
113 -----------------------------------------------------------------------------
114
115 procedure handleErrors(errorType in pls_integer
116 ,method in varchar2
117 ,tableIndex in pls_integer
118 ,message in varchar2
119 ,SQLError in varchar2) is
120 begin
121 if errorType = INVALID_GRID_ERROR
122 or tableIndex is null
123 or tableIndex = 0 then
124 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
125 fnd_message.set_token('SOURCE','JTF_GRIDDB');
126 fnd_message.set_token('MSG','Internal Error: jtf_grid.handleErrors() was called from jtf_griddb.'||method||' with the following information:'
127 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
128 ||lineFeed||lineFeed||'The error is in the form or the JTF_GRID package.');
129 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
130 elsif errorType = INTERNAL_ERROR then
131 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
132 fnd_message.set_token('SOURCE','JTF_GRIDDB');
133 fnd_message.set_token('MSG','An unexpected error occurred in jtf_griddb.'||method
134 ||lineFeed||lineFeed||'grid: <'||tableDefs(tableIndex).gridName|| '>'
135 ||lineFeed||'datasource: <'||tableDefs(tableIndex).grid_datasource_name||'>'
136 ||lineFeed||lineFeed||'The following information is available:'
137 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
138 ||lineFeed||lineFeed||'The error is in the form or the JTF_GRID package.');
139 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
140 elsif errorType = INVALID_PROPERTY_ERROR then
141 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
142 fnd_message.set_token('SOURCE','JTF_GRIDDB');
143 fnd_message.set_token('MSG','Application Error: An invalid propertyType was passed to jtf_griddb.'||method
144 ||lineFeed||lineFeed||'grid: <'||tableDefs(tableIndex).gridName|| '>'
145 ||lineFeed||'datasource: <'||tableDefs(tableIndex).grid_datasource_name||'>'
146 ||lineFeed||lineFeed||'The following information is available:'
147 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
148 ||lineFeed||lineFeed||' The error is in the form.');
149 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
150 elsif errorType = APPLICATION_ERROR then
151 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
152 fnd_message.set_token('SOURCE','JTF_GRIDDB');
153 fnd_message.set_token('MSG','Application Error: jtf_griddb.'||method||' reports that an Application Error has occurred for:'
154 ||lineFeed||lineFeed||'grid: <'||tableDefs(tableIndex).gridName|| '>'
155 ||lineFeed||'datasource: <'||tableDefs(tableIndex).grid_datasource_name||'>'
156 ||lineFeed||lineFeed||'The following information is available:'
157 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
158 ||lineFeed||lineFeed||'The error is either in the form or the metadata definition.');
159 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
160 elsif errorType = MAXLENGTH_EXCEEDED_ERROR then
161 fnd_message.set_name('JTF','JTF_GRID_EXCEED_MAXLENGTH');
162 fnd_message.set_token('MSG','Application Error: jtf_griddb.'||method||' reports: A record of data for:'
163 ||lineFeed||lineFeed||'grid: <'||tableDefs(tableIndex).gridName|| '>'
164 ||lineFeed||'datasource: <'||tableDefs(tableIndex).grid_dataSource_name||'>'
165 ||lineFeed||lineFeed||'exceeds the current maxlength ('||to_char(jtf_dbstring_utils.getMaxStringLength)||' bytes / record). Reduce the number of columns defined in the datasource (metadata definition) or display partial column values.');
166 elsif errorType = MISSING_SORT_COL_ERROR then
167 fnd_message.set_name('JTF','JTF_GRID_MISSING_SORT_COLUMN');
168 fnd_message.set_token('SOURCE','jtf_griddb.'||method);
169 fnd_message.set_token('DATASOURCE',tableDefs(tableIndex).grid_dataSource_name);
170 fnd_message.set_token('GRIDNAME',tableDefs(tableIndex).gridName);
171 elsif errorType = INVALID_COLUMNALIAS_ERROR then
172 fnd_message.set_name('JTF','JTF_GRID_INVALID_COLUMNALIAS');
173 fnd_message.set_token('SOURCE','jtf_griddb.'||method);
174 fnd_message.set_token('DATASOURCE',tableDefs(tableIndex).grid_dataSource_name);
175 fnd_message.set_token('COLUMN_ALIAS',nvl(message,'<'||nullValue||'>'));
176 fnd_message.set_token('GRIDNAME',tableDefs(tableIndex).gridName);
177 elsif errorType = ILLEGAL_SORT_COLUMN_ERROR then
178 fnd_message.set_name('JTF','JTF_GRID_ILLEGAL_SORT_COLUMN');
179 fnd_message.set_token('SOURCE','jtf_griddb.'||method);
180 fnd_message.set_token('DATASOURCE',tableDefs(tableIndex).grid_dataSource_name);
181 fnd_message.set_token('COLUMN_ALIAS',nvl(message,'<'||nullValue||'>'));
182 fnd_message.set_token('GRIDNAME',tableDefs(tableIndex).gridName);
183 else
184 -- We should never get this message
185 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
186 fnd_message.set_token('SOURCE','JTF_GRIDDB');
187 fnd_message.set_token('MSG','Internal Error: jtf_gridd.handleErrors() was called from jtf_griddb.'||method||' with an invalid errorType for:'
188 ||lineFeed||lineFeed||'grid: <'||tableDefs(tableIndex).gridName|| '>'
189 ||lineFeed||'datasource: <'||tableDefs(tableIndex).grid_dataSource_name||'>'
190 ||lineFeed||lineFeed||'The following information is available:'
191 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
192 ||lineFeed||lineFeed||'The error is in the form or the JTF_GRID package.');
193 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
194 end if;
195 raise_error;
196 end handleErrors;
197
198 -----------------------------------------------------------------------------
199 -- Description:
200 --
201 -- handle Error messages
202 --
203 -- Notes: MUST NEVER BE CALLED FROM OUTSIDE OF THIS PACKAGE!
204 --
205 -- @param method The method where the error occurred.
206 -- @param gridName The block.item where the error occurred
207 -- @param message The error message
208 -- @param SQLError The SQLERRM error message
209 -----------------------------------------------------------------------------
210
211 procedure handleErrors(errorType in pls_integer
212 ,method in varchar2
213 ,gridName in varchar2
214 ,message in varchar2
215 ,SQLError in varchar2) is
216 tableIndex pls_integer;
217 begin
218 if errorType = INVALID_GRID_ERROR then
219 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
220 fnd_message.set_token('SOURCE','JTF_GRIDDB');
221 fnd_message.set_token('MSG','Application Error: jtf_griddb.'||method||' reports that'
222 ||lineFeed||lineFeed||'grid: <'||nvl(rtrim(gridName),nullValue)||'>'
223 ||lineFeed||lineFeed||'has not been initialized. A grid must be specified using BLOCK.ITEM_NAME in UPPERCASE. The error is in the form.');
224 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
225 raise_error;
226 elsif errorType = INIT_ERROR then
227 fnd_message.set_name('JTF','JTF_GRID_DB_ERRORS');
228 fnd_message.set_token('SOURCE','JTF_GRIDDB');
229 fnd_message.set_token('MSG','An unexpected error occurred in jtf_griddb.'||method||' for:'
230 ||lineFeed||lineFeed||'grid: <'||nvl(rtrim(gridName),nullValue)|| '>'
231 ||lineFeed||lineFeed||'The following information is available:'
232 ||lineFeed||lineFeed||nvl(message,'<'||nullValue||'>')
233 ||lineFeed||lineFeed||'The error is either in the form or the metadata definition.');
234 fnd_message.set_token('SQLERROR',nvl(SQLError,'<'||nullValue||'>'));
235 raise_error;
236 elsif errorType = INVALID_DATASOURCE_ERROR then
237 fnd_message.set_name('JTF','JTF_GRID_INVALID_DATASOURCE');
238 fnd_message.set_token('SOURCE','jtf_griddb.'||method);
239 fnd_message.set_token('DATASOURCE',nvl(message,'<'||nullValue||'>'));
240 fnd_message.set_token('GRIDNAME',nvl(rtrim(gridName),nullValue||'>'));
241 raise_error;
242 else
243 tableIndex := findTableDefIndex(gridName);
244 if tableIndex is null
245 or tableIndex = 0 then
246 -- recursive call if we can't find the gridName, this means that the grid has not been initialized.
247 handleErrors(INVALID_GRID_ERROR,method,gridName,message,SQLError);
248 else
249 handleErrors(errorType,method,tableIndex,message,SQLError);
250 end if;
251 end if;
252 end handleErrors;
253
254 function getFetchSize return number is
255 begin
256 return fetchSize;
257 end getFetchSize;
258
259 procedure setFetchSize(rows in number) is
260 begin
261 fetchSize := rows;
262 end setFetchSize;
263 --------------------------------------------------------------------------
264
265 function findTableDefIndex(gridName in varchar2, p_serverInitDate in date) return binary_integer is
266 i binary_integer;
267 begin
268 validateServer(p_serverInitDate, gridName);
269 return findTableDefIndex(gridName);
270 end findTableDefIndex;
271 ----------------------------------------------------------------------------
272
273 -- Find the binary_integer that points to the row holding the meta data for the
274 -- next column for the current grid.
275 -- if colIndex is null or 0 then this function will return the first column
276 -- returns null is there are no more columns
277 function findNextColumnDefIndex(gridName in varchar2, colIndex in binary_integer) return binary_integer is
278 i binary_integer;
279 begin
280 if colIndex is null
281 or colIndex = 0 then
282 i := columnDefs.FIRST;
283 else
284 i := columnDefs.NEXT(colIndex);
285 end if;
286
287 while i is not null loop
288 if columnDefs(i).gridName = gridName then
289 return i;
290 end if;
291 i := columnDefs.next(i);
292 end loop;
293 return null;
294 /*
295 exception
296 when OTHERS then
297 handleErrors(INTERNAL_ERROR,'findNextColumnDefIndex',gridName,
298 ' colIndex = <'||nvl(to_char(colIndex),nullValue)||'>'||lineFeed
299 ,SQLERRM);
300 */
301 end findNextColumnDefIndex;
302 -------------------------------------------------------------------------------
303 ----------------------------------------------------------------------------
304
305 -- Find the Column Alias for the given columnIndex within the current spreadtable, this is not the
306 -- index pointing to a row in the columnDef collection, but rather the sequence number of the column
307 -- for the grid. The columnIndex a 1 based index.
308 function findColumnAlias(gridName in varchar2,columnIndex in integer) return varchar2 is
309 i binary_integer;
310 j integer := 0;
311 begin
312 i := findNextColumnDefIndex(gridName,0);
313 while i is not null loop
314 j := j + 1;
315 if j = columnIndex then
316 return columnDefs(i).grid_col_alias;
317 end if;
318 i := findNextColumnDefIndex(gridName,i);
319 end loop;
320 return null;
321 /*
322 exception
323 when OTHERS then
324 handleErrors(INTERNAL_ERROR,'findColumnAlias',gridName,
325 ' columnIndex = <'||nvl(to_char(columnIndex),nullValue)||'>'||lineFeed
326 ,SQLERRM);
327 */
328 end findColumnAlias;
329 -----------------------------------------------------------------------------
330 -- Find the next index pointing to a bindVariable for the given grid
331 function findNextBindVariable(gridName in varchar2, bindIndex in binary_integer) return binary_integer is
332 i binary_integer;
333 begin
334 if bindIndex is not null
335 or bindIndex > 0 then
336 i := bindVariables.NEXT(bindIndex);
337 else
338 i := bindVariables.FIRST;
339 end if;
340
341 while i is not null loop
342 if bindVariables(i).gridName = gridName then
343 return i;
344 end if;
345 i := bindVariables.NEXT(i);
346 end loop;
347 return null;
348 /*
349 exception
350 when others then
351 handleErrors(INTERNAL_ERROR,'findNextBindVariable',gridName,
352 ' bindIndex = <'||nvl(to_char(bindIndex),nullValue)||'>'||lineFeed
353 ,SQLERRM);
354 */
355 end findNextBindVariable;
356
357
358
359 function findBindVariableIndex(gridName in varchar2, variableName in varchar2) return binary_integer is
360 i binary_integer;
361 begin
362 if bindVariables.COUNT = 0 then
363 return 1;
364 else
365 i := findNextBindVariable(gridName,null);
366 while i is not null loop
367 if bindVariables(i).variableName = variableName then
368 return i;
369 else
370 i := findNextBindVariable(gridName,i);
371 end if;
372 end loop;
373 -- if we get this far, the bindvariable did not exist in the collection, we need to find an empty spot.
374 i := 1;
375 if bindVariables.LAST <> bindVariables.COUNT then
376 while i < tableDefs.LAST loop
377 if bindVariables.EXISTS(i) then
378 i := i + 1;
379 else
380 return i;
381 end if;
382 end loop;
383 else
384 return (bindVariables.LAST + 1);
385 end if;
386 end if;
387 -- should never get this far
388 return (nvl(bindVariables.LAST,0) + 1);
389 /*
390 exception
391 when OTHERS then
392 handleErrors(INTERNAL_ERROR,'findBindVariableIndex',gridName,
393 ' variableName = <'||nvl(variableName,nullValue)||'>'||lineFeed
394 ,SQLERRM);
395 */
396 end findBindVariableIndex;
397
398
399 function bindVariablesCount(gridName varchar2) return number is
400 i binary_integer;
401 bindVarCount binary_integer := 0;
402 begin
403 if bindVariables.COUNT = 0 then
404 return bindVarCount;
405 else
406 i := findNextBindVariable(gridName,null);
407 while i is not null loop
408 bindVarCount := bindVarCount + 1;
409 i := findNextBindVariable(gridName,i);
410 end loop;
411 return bindVarCount;
412 end if;
413 end;
414
415
416 -- Find the binary_integer that points to the row holding the meta data for the
417 -- given column_alias and grid.
418 function findColumnDefIndex(gridName in varchar2
419 ,grid_col_alias in varchar2) return binary_integer is
420 i binary_integer;
421 begin
422 i := findNextColumnDefIndex(gridName,0);
423 while i is not null loop
424 if columnDefs(i).grid_col_alias = grid_col_alias then
425 return i;
426 end if;
427 i := findNextColumnDefIndex(gridName,i);
428 end loop;
429 return null;
430 /*
431 exception
432 when OTHERS then
433 handleErrors(INTERNAL_ERROR,'findColumnDefIndex',gridName,
434 ' grid_col_alias = <'||nvl(grid_col_alias,nullValue)||'>'||lineFeed
435 ,SQLERRM);
436 */
437 end findColumnDefIndex;
438
439
440 function findColumnDefIndex(gridName in varchar2
441 ,grid_col_alias in varchar2
442 ,p_serverInitDate in date) return binary_integer is
443 begin
444 validateServer(p_serverInitDate, gridName);
445 return findColumnDefIndex(gridName, grid_col_alias);
446 end findColumnDefIndex;
447
448
449 -- Find the Column Index for the given column within the current spreadtable, this is not the
450 -- index pointing to a row in the columnDef collection, but rather the sequence number of the column
451 -- for the grid.
452 -- This returned value is a 1 based index
453 function findColumnIndex(gridName in varchar2,grid_col_alias in varchar2) return integer is
454 i binary_integer;
455 j integer := 0;
456 begin
457 i := findNextColumnDefIndex(gridName,0);
458 while i is not null loop
459 j := j + 1;
460 if columnDefs(i).grid_col_alias = grid_col_alias then
461 return j;
462 end if;
463 i := findNextColumnDefIndex(gridName,i);
464 end loop;
465 return null;
466 /*
467 exception
468 when OTHERS then
469 handleErrors(INTERNAL_ERROR,'findColumnIndex',gridName,
470 ' grid_col_alias = <'||nvl(grid_col_alias,nullValue)||'>'||lineFeed
471 ,SQLERRM);
472 */
473 end findColumnIndex;
474
475 function findColumnIndex(gridName in varchar2,grid_col_alias in varchar2, p_serverInitDate in date) return integer is
476 begin
477 validateServer(p_serverInitDate, gridName);
478 return findColumnIndex(gridName, grid_col_alias);
479 end findColumnIndex;
480
481 -----------------------------------------------------------------------------
482 function findColumnAlias(gridName in varchar2,columnIndex in integer, p_serverInitDate in date) return varchar2 is
483 begin
484 validateServer(p_serverInitDate, gridName);
485 return findColumnAlias(gridName,columnIndex);
486 end findColumnAlias;
487
488
489
490 function isMoreRowsAvailable(gridName in varchar2
491 ,p_serverInitDate in date) return varchar2 is
492 i binary_integer;
493 begin
494 validateServer(p_serverInitDate, gridName);
495 i := findTableDefIndex(gridName);
496 if i is not null then
497 return tableDefs(i).moreRowsExists;
498 else
499 return null;
500 end if;
501 /*
502 exception
503 when OTHERS then
504 handleErrors(INTERNAL_ERROR,'isMoreRowsAvailable',gridName,null,SQLERRM);
505 */
506 end isMoreRowsAvailable;
507
508 function convertColumnToString(columnDefIndex in binary_integer) return varchar2 is
509 begin
510 if columnDefs(columnDefIndex).data_type_code = 'C' then
511 return columnDefs(columnDefIndex).db_col_name;
512 elsif columnDefs(columnDefIndex).data_type_code = 'N' then
513 if columnDefs(columnDefIndex).display_format_mask is not null then
514 return 'to_char('|| columnDefs(columnDefIndex).db_col_name || ','''||columnDefs(columnDefIndex).display_format_mask||''')';
515 elsif columnDefs(columnDefIndex).display_format_type_code = 'CUR'
516 and columnDefs(columnDefIndex).db_currency_code_col is not null then
517 return 'to_char('|| columnDefs(columnDefIndex).db_col_name || ',fnd_currency.get_format_mask('||columnDefs(columnDefIndex).db_currency_code_col||',jtf_dbstring_utils.getCurrencyFormatLength))';
518 else
519 return 'to_char('|| columnDefs(columnDefIndex).db_col_name || ')' ;
520 end if;
521 elsif columnDefs(columnDefIndex).data_type_code = 'D' then
522 if columnDefs(columnDefIndex).display_format_type_code = 'DAT' then
523 return 'fnd_date.date_to_displaydate('|| columnDefs(columnDefIndex).db_col_name || ')';
524 else
525 return 'fnd_date.date_to_displayDT('|| columnDefs(columnDefIndex).db_col_name || ')';
526 end if;
527 else
528 return columnDefs(columnDefIndex).db_col_name;
529 end if;
530 end convertColumnToString;
531 /*
532 --03/13/2001 added this procedure to define columns based on their datatype.
533 procedure defineColumn(gridName in varchar2) is
534 begin
535
536 end;
537 */
538 -- Builds a piece of the ORDER BY clause for the dynamic SQL statement
539 -- 03/13/2001 modified this procedure to use grid_col_alias and not the
540 -- db_col_name to enable having unions in the where clause.
541 function buildOrderByClause(gridName in varchar2, grid_sort_col_alias in varchar2, orderClause in varchar2) return varchar2 is
542 m_orderClause varchar2(600) := null;
543 i binary_integer;
544 begin
545 if grid_sort_col_alias is not null then
546 /* continue to have this check even though we don't use db_col anymore*/
547 i := findColumnDefIndex(gridName,grid_sort_col_alias);
548 if i is not null then
549 if orderClause is not null then
550 --m_orderClause := orderClause ||', '||columnDefs(i).db_col_name;
551 m_orderClause := orderClause ||','||nvl(columnDefs(i).db_sort_column, columnDefs(i).SQL_colAlias);
552 else
553 --m_orderClause := columnDefs(i).db_col_name;
554 m_orderClause := nvl(columnDefs(i).db_sort_column, columnDefs(i).SQL_colAlias);
555 end if;
556 if columnDefs(i).sort_asc_by_default_flag = 'T' then
557 m_orderClause := m_orderClause ||' ASC';
558 else
559 m_orderClause := m_orderClause ||' DESC';
560 end if;
561 end if;
562 else
563 return orderClause;
564 end if;
565 return m_orderClause;
566 end buildOrderByClause;
567
568 function prepareSQL(gridName in varchar2, tableIndex in binary_integer) return boolean is
569
570 dynSQL jtf_dbstring_utils.maxString%TYPE := null;
571 fromClause varchar2(200) := null;
572 whereClause varchar2(4000):= null;
573 orderClause varchar2(600) := null;
574 i pls_integer;
575 j pls_integer := 1;
576 begin
577 -- Get the first colIndex
578 i := findNextColumnDefIndex(gridName,0);
579
580 if i is null then
581 return false;
582 end if;
583
584 dynSQL := 'SELECT ';
585 while i is not null loop
586 -- keeps track of the generated SQL column alias for each column in the metadata. Needed to create a proper sort order using aliases.
587 columnDefs(i).SQL_colAlias := 'COL'||to_char(j);
588 if columnDefs(i).fire_post_query_flag = 'T' then
589 -- dynSQL := dynSQL ||'NULL '||columnDefs(i).grid_col_alias;
590 dynSQL := dynSQL ||'NULL COL'||to_char(j);
591 else
592 -- dynSQL := dynSQL || convertColumnToString(i);
593 -- 03/13/01 modified the select statement to accept the db_col_name
594 -- dynSQL := dynSQL || columnDefs(i).db_col_name || ' '||columnDefs(i).grid_col_alias;
595
596 dynSQL := dynSQL || columnDefs(i).db_col_name || ' COL'||to_char(j);
597 -- if the column is a curreny column then select teh currency code also
598 if columnDefs(i).data_type_code = 'N'
599 and columnDEfs(i).display_format_type_code = 'CUR' then
600 -- should include this even if currency code col isNULL as we expect it when we define the columns etc.
601 -- dynSQL := dynSQL ||','||columnDefs(i).db_currency_code_col||' '||columnDefs(i).grid_col_alias||'_CODE';
602 j := j + 1;
603 dynSQL := dynSQL ||','||columnDefs(i).db_currency_code_col|| ' COL'||to_char(j);
604 elsif columnDefs(i).data_type_code = 'I'
605 and columnDefs(i).image_Description_col is not NULL then
606 -- dynSQL := dynSQL ||','||columnDefs(i).image_Description_col||' '||columnDefs(i).grid_col_alias||'_DESC';
607 j :=j + 1;
608 dynSQL := dynSQL ||','||columnDefs(i).image_Description_col|| ' COL'||to_char(j);
609 -- in case of images you always want to sort by the description if
610 -- available. hence, mapping the SQL_colAlias of the image column to
611 -- the column alias of the description column
612
613 columnDefs(i).SQL_colAlias := 'COL'||to_char(j);
614
615 end if;
616
617 end if; -- post_query_flag = 'T'
618 i := findNextColumnDefIndex(gridName,i);
619 -- add a comma unless this is the last column
620 if i is not null then
621 dynSQL := dynSQL ||', ';
622 j := j + 1;
623 end if;
624 end loop;
625
626 fromClause := ' FROM '|| tableDefs(tableIndex).db_view_name;
627
628 if rtrim(tableDefs(tableIndex).where_clause) is not null then
629 whereClause := ' WHERE '||tableDefs(tableIndex).where_clause;
630 end if;
631
632 orderClause := buildOrderByClause(gridName,tableDefs(tableIndex).grid_sort_col_alias1,null);
633 orderClause := buildOrderByClause(gridName,tableDefs(tableIndex).grid_sort_col_alias2,orderClause);
634 orderClause := buildOrderByClause(gridName,tableDefs(tableIndex).grid_sort_col_alias3,orderClause);
635 if orderClause is not null then
636 orderClause := ' ORDER BY '|| orderClause;
637 end if;
638
639 tableDefs(tableIndex).SQLStatement := dynSQL || fromClause || whereClause || orderClause;
640 -- everything went well
641 --dbms_output.put_line(tableDefs(tableIndex).SQLStatement);
642 return true;
643 exception
644 when OTHERS then
645 -- handleErrors(INTERNAL_ERROR,'prepareSQL',gridName,null
646 -- ' tableIndex = <'||nvl(to_char(tableIndex),nullValue)||'>'||lineFeed
647 -- ,SQLERRM);
648 return false;
649
650 end prepareSQL;
651
652 procedure handleBindVariables(gridName in varchar2, tableIndex in binary_integer) is
653 i binary_integer;
654 begin
655 i := findNextBindVariable(gridName,null);
656 while i is not null loop
657 if bindVariables(i).variableDataType = 'C' then
658 dbms_sql.bind_variable(tableDefs(tableIndex).SQLCursor,bindVariables(i).variableName,bindVariables(i).variableCharValue);
659 elsif bindVariables(i).variableDataType = 'D' then
660 dbms_sql.bind_variable(tableDefs(tableIndex).SQLCursor,bindVariables(i).variableName,bindVariables(i).variableDateValue);
661 elsif bindVariables(i).variableDataType = 'N' then
662 dbms_sql.bind_variable(tableDefs(tableIndex).SQLCursor,bindVariables(i).variableName,bindVariables(i).variableNumberValue);
663 else
664 null;
665 -- Unknown datatype, should never get here
666 end if;
667 i := findNextBindVariable(gridName,i);
668 end loop;
669 exception
670 when others then
671 if dbms_sql.is_open(tableDefs(tableIndex).SQLCursor) then
672 dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
673 tableDefs(tableIndex).SQLCursor := null;
674 end if;
675 raise;
676 -- handleErrors(INTERNAL_ERROR,'handleBindVariables',gridName,
677 -- ' bindVariable = <'||nvl(bindVariables(i).variableName,nullValue)||'>'||lineFeed
678 -- ,SQLERRM);
679
680 end handleBindVariables;
681
682 function execSQL(gridName in varchar2, tableIndex in binary_integer) return varchar2 is
683 rows integer;
684 -- colVal varchar2(4000);
685 ignore integer;
686
687 -- 03/13/01 need additional columns to remove function calls
688 charColVal varchar2(4000);
689 ImageCharColVal varchar2(4000);
690 numberColVal number;
691 dateColVal date;
692 columnDefIndex pls_integer := 0;
693 i pls_integer := 0;
694 j pls_integer := 1;
695
696 begin
697 -- CURSOR MGMT REWRITE
698
699 -- If we don't have a handle to the cursor or it is closed for some reason
700 -- we reopen it and make sure that the sql statement will be parsed as well
701 -- as having any bind variables bound
702 if tableDefs(tableIndex).SQLCursor is null
703 or not dbms_sql.is_open(tableDefs(tableIndex).SQLCursor) then
704 tableDefs(tableIndex).SQLCursor := dbms_sql.open_cursor;
705 tableDefs(tableIndex).hasBindVarsChanged := 'T';
706 tableDefs(tableIndex).hasWhereClauseChanged := 'T';
707 end if;
708
709 -- If we need to refresh the query AND the whereClause has changed for
710 -- some reason -> we recreate the where clause and parse it
711 if tableDefs(tableIndex).refreshFlag = 'T'
712 and tableDefs(tableIndex).hasWhereClauseChanged = 'T' then
713
714 -- If we fail to build up the sql statement we close the
715 -- cursor straight away.
716 if prepareSQL(gridName,tableIndex) = false then
717 dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
718 tableDefs(tableIndex).SQLCursor := null;
719 return null;
720 -- If the SQL statement is OK, we parse and define the columns
721 else
722 dbms_sql.parse(tableDefs(tableIndex).SQLCursor, tableDefs(tableIndex).SQLStatement, dbms_sql.native);
723 ----------------------------------------------------------
724 for i in 1..temp_columnDefs.count loop
725 --dbms_output.put_line('i si ' ||i||columnDefs(i).data_type_code);
726 if temp_columnDefs(i).data_type_code = 'C' then
727 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor,j, charColVal, 4000);
728 elsif temp_columnDefs(i).data_type_code = 'N' then
729 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor,j, NumberColVal);
730 if temp_columnDefs(i).display_format_type_code = 'CUR' then
731 j := j + 1;
732 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor, j, charColVal, 4000);
733 end if;
734 elsif temp_columnDefs(i).data_type_code = 'D' then
735 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor, j, dateColVal);
736 elsif temp_columnDefs(i).data_type_code = 'I' then
737 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor,j, charColVal, 4000);
738 if temp_columnDefs(i).image_description_col is not NULL then
739 j := j + 1;
740 dbms_sql.define_column(tableDefs(tableIndex).SQLCursor,j, CharColVal, 4000);
741 end if;
742 end if;
743 j := j + 1;
744 end loop;
745 ----------------------------------------------------------------------
746 end if;
747 end if;
748
749 -- If we need to refresh the query AND the bind variables has changed for
750 -- some reason -> rebind the variables. Note if the WhereClause was changed we need
751 -- to rebind
752 if tableDefs(tableIndex).refreshFlag = 'T'
753 and (tableDefs(tableIndex).hasBindVarsChanged = 'T'
754 or tableDefs(tableIndex).hasWhereClauseChanged = 'T') then
755 handleBindVariables(gridName,tableIndex);
756
757 tableDefs(tableIndex).hasBindVarsChanged := 'F';
758 tableDefs(tableIndex).hasWhereClauseChanged := 'F';
759 end if;
760
761 -- If we need to refresh the query for whatever reason -> execute the query
762 if tableDefs(tableIndex).refreshFlag = 'T' then
763 ignore := dbms_sql.execute(tableDefs(tableIndex).SQLCursor);
764 tableDefs(tableIndex).refreshFlag := 'F';
765 end if;
766
767 -- Fetch one row and write the result to the stream
768 rows := dbms_sql.fetch_rows(tableDefs(tableIndex).SQLCursor);
769 if rows > 0 then
770 jtf_dbstream_utils.clearOutputStream;
771
772 /* the retrieved values are no longer only varchar2
773 for i in 1..tableDefs(tableIndex).colCount loop
774 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, i, colVal);
775 jtf_dbstream_utils.writeString(colVal);
776 end loop;
777 */
778 -----------------------------------------------
779 j := 1;
780 for i in 1..temp_columnDefs.count loop
781 --dbms_output.put_line('i value is '||i||' datatype is '||columnDefs(i).data_type_code);
782 if temp_columnDefs(i).data_type_code = 'C' then
783 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, j, CharColVal);
784 jtf_dbstream_utils.writeString(charColVal);
785 elsif temp_columnDefs(i).data_type_code = 'N' then
786 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor,j, NumberColVal);
787 if temp_columnDefs(i).display_format_mask is not null then
788 jtf_dbstream_utils.writeString(to_char(NumberColVal,temp_columnDefs(i).display_format_mask));
789 elsif temp_columnDefs(i).display_format_type_code = 'CUR'
790 and temp_columnDefs(i).db_currency_code_col is not null then
791 j := j + 1;
792 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, j, charColVal);
793 jtf_dbstream_utils.writeCurrency(NumberColVal, charColVal);
794 else
795 jtf_dbstream_utils.writeNumber(NumberColVal);
796 end if;
797 elsif temp_columnDefs(i).data_type_code = 'D' then
798 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, j, dateColVal);
799 if temp_columnDefs(i).display_format_type_code = 'DAT' then
800 jtf_dbstream_utils.writeDate(DateColVal);
801 else
802 jtf_dbstream_utils.writeDateTime(DateColVal);
803 end if;
804 elsif temp_columnDefs(i).data_type_code = 'I' then
805 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, j, CharColVal);
806 imageCharColVal := CharColVal;
807 if temp_columnDefs(i).image_description_col is not NULL then
808 j := j + 1;
809 dbms_sql.column_value(tableDefs(tableIndex).SQLCursor, j, CharColVal);
810 jtf_dbstream_utils.writeString(ImageCharColVal||IMAGE_SEPARATOR||CharColVal);
811 else
812 jtf_dbstream_utils.writeString(ImageCharColVal||IMAGE_SEPARATOR);
813 end if;
814 end if;
815 j := j + 1;
816 end loop;
817 -------------------------------------------------------------
818 else
819 -- We don't close the cursor nowadays, only if the grid is initialized
820 -- with a different datasource will it be closed and reopened
821 -- dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
822 return null;
823 end if;
824 -- CURSOR MGMT REWRITE END
825 if jtf_dbstream_utils.isLongOutputStream then
826 handleErrors(MAXLENGTH_EXCEEDED_ERROR,'execSQL',gridName,
827 ' SQL Statement being executed: <'||nvl(tableDefs(tableIndex).SQLStatement,nullValue)||'>'||lineFeed
828 ,null);
829 end if;
830 return jtf_dbstream_utils.getOutputStream;
831 exception
832 when others then
833 if dbms_sql.is_open(tableDefs(tableIndex).SQLCursor) then
834 dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
835 tableDefs(tableIndex).SQLCursor := null;
836 end if;
837 raise;
838 -- handleErrors(INTERNAL_ERROR,'execSQL',gridName,null
839 -- ' SQL Statement: <'||nvl(tableDefs(tableIndex).SQLStatement,nullValue)||'>'||lineFeed||
840 -- ' tableIndex = <'||nvl(to_char(tableIndex),nullValue)||'>'||lineFeed||
841 -- ' rows = <'||nvl(to_char(rows),nullValue)||'>'||lineFeed||
842 -- ' colVal = <'||nvl(colVal,nullValue)||'>'||lineFeed||
843 -- ' rowVal = <'||nvl(replace(rowVal,separator,readableSeparator),nullValue)||'>'||lineFeed
844 -- ,SQLERRM);
845 end execSQL;
846
847 function fetchDataSet(gridName in varchar2
848 ,p_serverInitDate in date) return dataSet%TYPE is
849 j binary_integer;
850 tempRec jtf_dbstring_utils.maxString%TYPE;
851 NOT_INITIALIZED Exception;
852 rowCount pls_integer;
853 colCount varchar2(6);
854 i pls_integer;
855 k pls_integer := 1;
856 begin
857 validateServer(p_serverInitDate, gridName);
858 dataSet.delete;
859 j := findTableDefIndex(gridName);
860 if j is null then
861 raise NOT_INITIALIZED;
862 end if;
863
864 if tableDefs(j).moreRowsExists = 'T' then
865 rowCount := tableDefs(j).rowCount;
866 colCount := to_char(tableDefs(j).colCount);
867 -- before fetching populate a temp_colDefs table with teh column definitions
868 -- of this grid. this will reduce unnecessary searches in execSQL
869 temp_columnDefs.DELETE;
870 i := findNextColumnDefIndex(gridname, 0);
871 while i is not NULL loop
872 temp_columnDefs(k) := columnDefs(i);
873 i := findNextColumnDefIndex(gridName, i);
874 k := k + 1;
875 end loop;
876 -- dbms_output.put_line('fetch size is ' || tableDefs(j).fetchSize);
877 for i in 1..nvl(tableDefs(j).fetchSize, fetchSize) loop
878 tempRec := execSQL(gridName,j);
879 if tempRec is not null then
880 rowCount := rowCount + 1;
881 jtf_dbstream_utils.clearOutputStream;
882 -- 02/28/01 commented out the rowCount to enable inserts/deletes into the
883 -- spreadtable. The midtier will add this to the stream before it sends it
884 -- off to the client.
885 -- jtf_dbstream_utils.writeInt(rowCount);
886 jtf_dbstream_utils.writeString(recVersion);
887 jtf_dbstream_utils.writeString(colCount);
888 if jtf_dbstream_utils.isLongOutputStream then
889 handleErrors(MAXLENGTH_EXCEEDED_ERROR,'fetchDataSet',gridName,
890 ' SQL Statement being executed: <'||nvl(tableDefs(j).SQLStatement,nullValue)||'>'||lineFeed
891 ,null);
892 end if;
893 -- don't write tempRec to the stream, as it is already
894 -- formatted by execSQL
895 dataSet(i) := jtf_dbstream_utils.getOutputStream||tempRec;
896 else
897 tableDefs(j).moreRowsExists := 'F';
898 exit;
899 end if;
900 end loop;
901 tableDefs(j).rowCount := rowCount;
902 end if;
903 return dataSet;
904 exception
905 when NOT_INITIALIZED then
906 handleErrors(INVALID_GRID_ERROR,'fetchDataSet',gridName,null,null);
907 return dataSet;
908 /*
909 when OTHERS then
910 handleErrors(INTERNAL_ERROR,'fetchDataSet',gridName,
911 ' tempRec = <'||nvl(tempRec,nullValue)||'>'||lineFeed||
912 ' rowCount = <'||nvl(to_char(rowCount),nullValue)||'>'||lineFeed||
913 ' colCount = <'||nvl(colCount,nullValue)||'>'||lineFeed
914 ,SQLERRM);
915 */
916 end fetchDataSet;
917
918
919 -----------------------------------------------------------------------------
920
921 procedure refresh(gridName in varchar2,tableIndex in binary_integer) is
922 begin
923 if tableIndex is null then
924 raise INVALID_GRID;
925 end if;
926 -- CURSOR MGMR REWRITE
927 -- if tableDefs(tableIndex).moreRowsExists = 'T'
928 -- and tableDefs(tableIndex).SQLCursor is not null
929 -- and dbms_sql.is_open(tableDefs(tableIndex).SQLCursor) then
930 -- dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
931 -- end if;
932 -- tableDefs(tableIndex).SQLCursor := null;
933 -- CURSOR MGMR REWRITE END
934 tableDefs(tableIndex).moreRowsExists := 'T';
935 tableDefs(tableIndex).rowCount := 0;
936
937 -- CURSOR MGMT REWRITE
938 -- This will make sure we validate whether the WhereClause or
939 -- Bindvariables has changed in execSQL
940 tableDefs(tableIndex).refreshFlag := 'T';
941 -- CURSOR MGMT REWRITE END
942
943 exception
944 when INVALID_GRID then
945 handleErrors(INVALID_GRID_ERROR,'refresh',gridName,null,null);
946 /*
947 when OTHERS then
948 handleErrors(INTERNAL_ERROR,'refresh',gridName,null,SQLERRM);
949 */
950 end refresh;
951
952
953 function isTabColsInMemory(gridName in varchar2) return boolean is
954 i binary_integer;
955 begin
956 i := findNextColumnDefIndex(gridName,0);
957 if i is not null then
958 return true;
959 else
960 return false;
961 end if;
962 /*
963 exception
964 when OTHERS then
965 handleErrors(INTERNAL_ERROR,'isTabColsInMemory',gridName,null,SQLERRM);
966 */
967 end isTabColsInMemory;
968
969 -- Retrieve top level Meta Data information from the
970 -- database
971 function getTableDefFromDB(gridName in varchar2, dataSource in varchar2) return binary_integer is
972
973 cursor tableDef(gridName in varchar2, dataSource in varchar2) is
974 select gridName
975 ,grd.title_text
976 ,grc.cols -- ColCount
977 ,0 -- Current Row Count
978 ,'T' -- more Rows exists
979 ,null -- SQL statement
980 ,null -- SQL cursor
981 ,grd.grid_datasource_name
982 ,grd.db_view_name
983 ,grd.default_row_height
984 ,grd.max_queried_rows
985 ,grd.where_clause
986 ,gsc.grid_sort_col_alias1
987 ,gsc.grid_sort_col_alias2
988 ,gsc.grid_sort_col_alias3
989 ,'209,219,245' -- Ignore the col value, std color will be used nvl(grd.alt_color_code,'255,255,255')
990 ,1 -- nvl(grd.alt_color_interval,0)
991 ,null
992 ,null
993 ,null
994 ,null
995 ,'T' -- whereClauseChanged
996 ,'T' -- bindVarsChanged
997 ,'T' -- refreshFlag
998 ,fetch_Size -- fetchSize
999 from jtf_grid_datasources_vl grd
1000 ,jtf_grid_sort_cols gsc
1001 ,(
1002 select grid_datasource_name
1003 ,count(*) cols
1004 from jtf_grid_cols_b
1005 group by grid_datasource_name
1006 ) grc
1007 where grd.grid_datasource_name = dataSource
1008 and grd.grid_datasource_name = gsc.grid_datasource_name(+)
1009 and grd.grid_datasource_name = grc.grid_datasource_name;
1010
1011 -- load the default folder as defined in jtf_def_custom_grids
1012 cursor defaultFolderDef(dataSource in varchar2) is
1013 select cgs.custom_grid_id
1014 ,cgs.custom_grid_name
1015 ,cgs.default_row_height
1016 ,cgs.where_clause
1017 ,cgs.grid_sort_col_alias1
1018 ,cgs.grid_sort_col_alias2
1019 ,cgs.grid_sort_col_alias3
1020 ,cgs.public_flag
1021 ,cgs.created_by -- this is the owner
1022 from jtf_custom_grids cgs
1023 ,jtf_def_custom_grids dcg
1024 where dcg.grid_datasource_name = dataSource
1025 and dcg.created_by = fnd_global.user_id
1026 and dcg.custom_grid_id = cgs.custom_grid_id
1027 and cgs.language = userenv('LANG');
1028
1029 i binary_integer;
1030 INVALID_DATASOURCE exception;
1031
1032 l_custom_grid_id number;
1033 l_custom_grid_name varchar2(80);
1034 l_default_row_height number(2);
1035 l_where_clause varchar2(2000);
1036 l_grid_sort_col_alias1 varchar2(30);
1037 l_grid_sort_col_alias2 varchar2(30);
1038 l_grid_sort_col_alias3 varchar2(30);
1039 l_public_flag varchar2(1);
1040 l_created_by number(15); -- this is the owner
1041
1042
1043 begin
1044 if gridName is null
1045 or dataSource is null then
1046 return null;
1047 end if;
1048
1049 i := nvl(tableDefs.LAST,0) + 1;
1050
1051 open tableDef(gridName,dataSource);
1052 fetch tableDef into tableDefs(i);
1053 if tableDef%NOTFOUND then
1054 close tableDef;
1055 raise INVALID_DATASOURCE;
1056 end if;
1057 close tableDef;
1058
1059 open defaultFolderDef(dataSource);
1060 fetch defaultFolderDef into
1061 l_custom_grid_id
1062 ,l_custom_grid_name
1063 ,l_default_row_height
1064 ,l_where_clause
1065 ,l_grid_sort_col_alias1
1066 ,l_grid_sort_col_alias2
1067 ,l_grid_sort_col_alias3
1068 ,l_public_flag
1069 ,l_created_by;
1070
1071 if defaultFolderDef%FOUND then
1072 tableDefs(i).custom_grid_id := l_custom_grid_id;
1073 tableDefs(i).custom_grid_name := l_custom_grid_name;
1074 if l_default_row_height is not null then
1075 tableDefs(i).default_row_height := l_default_row_height;
1076 end if;
1077 if l_where_clause is not null then
1078 tableDefs(i).where_clause := l_where_clause;
1079 end if;
1080 tableDefs(i).grid_sort_col_alias1 := l_grid_sort_col_alias1;
1081 tableDefs(i).grid_sort_col_alias2 := l_grid_sort_col_alias2;
1082 tableDefs(i).grid_sort_col_alias3 := l_grid_sort_col_alias3;
1083 tableDefs(i).public_flag := l_public_flag;
1084 tableDefs(i).owner := l_created_by;
1085 end if;
1086 close defaultFolderDef;
1087
1088 return i;
1089
1090 exception
1091 when INVALID_DATASOURCE then
1092 handleErrors(INVALID_DATASOURCE_ERROR,'getTableDefFromDB',gridName,dataSource,null);
1093 return null;
1094 /*
1095 when OTHERS then
1096 handleErrors(INTERNAL_ERROR,'getTableDefFromDB',gridName,
1097 ' dataSource = <'||nvl(dataSource,nullValue)||'>'||lineFeed
1098 ,SQLERRM);
1099 return null;
1100 */
1101 end getTableDefFromDB;
1102
1103 -- Retrieve column level Meta Data from the database
1104 procedure getColDefsFromDB(gridName in varchar2, dataSource in varchar2, tableIndex in binary_integer) is
1105
1106 cursor columnDef(gridName in varchar2, dataSource in varchar2) is
1107 select gridName
1108 ,grc.grid_datasource_name
1109 ,grc.grid_col_alias
1110 ,grc.db_col_name
1111 ,grc.data_type_code
1112 ,grc.query_seq
1113 ,grc.sortable_flag
1114 ,grc.sort_asc_by_default_flag
1115 ,grc.visible_flag
1116 ,grc.freeze_visible_flag
1117 ,null -- this needs to be converted to display_index
1118 ,grc.display_type_code
1119 ,grc.display_format_type_code
1120 ,grc.display_hsize
1121 ,grc.header_alignment_code
1122 ,grc.cell_alignment_code
1123 ,grc.display_format_mask
1124 ,grc.checkbox_checked_value
1125 ,grc.checkbox_unchecked_value
1126 ,nvl(grc.checkbox_other_values,'F')
1127 ,grc.db_currency_code_col
1128 ,null -- currency_column_alias
1129 ,grc.label_text
1130 ,grc.db_sort_column
1131 ,grc.fire_post_query_flag
1132 ,grc.image_description_col
1133 ,null -- SQL column alias
1134 from jtf_grid_cols_vl grc
1135 where grc.grid_datasource_name = dataSource
1136 order by grc.query_seq;
1137
1138 cursor columnFolderDef(gridName in varchar2, dataSource in varchar2,x_custom_grid_id in number) is
1139 select gridName
1140 ,grc.grid_datasource_name
1141 ,grc.grid_col_alias
1142 ,grc.db_col_name
1143 ,grc.data_type_code
1144 ,grc.query_seq
1145 ,grc.sortable_flag
1146 ,decode(grc.sortable_flag,'F',grc.sort_asc_by_default_flag,nvl(cgc.sort_asc_by_default_flag,grc.sort_asc_by_default_flag)) -- if false override customized value
1147 ,decode(grc.freeze_visible_flag,'T',grc.visible_flag,nvl(cgc.visible_flag,grc.visible_flag)) -- If true, override customized value
1148 ,grc.freeze_visible_flag
1149 ,null -- this needs to be converted to display_index
1150 ,grc.display_type_code
1151 ,grc.display_format_type_code
1152 ,nvl(cgc.display_hsize,grc.display_hsize)
1153 ,grc.header_alignment_code
1154 ,grc.cell_alignment_code
1155 ,grc.display_format_mask
1156 ,grc.checkbox_checked_value
1157 ,grc.checkbox_unchecked_value
1158 ,nvl(grc.checkbox_other_values,'F')
1159 ,grc.db_currency_code_col
1160 ,null -- currency_column_alias
1161 ,nvl(cgc.label_text,grc.label_text)
1162 ,grc.db_sort_column
1163 ,grc.fire_post_query_flag
1164 ,grc.image_description_col
1165 ,null -- sql column alias
1166 from jtf_grid_cols_vl grc
1167 ,jtf_custom_grid_cols cgc
1168 where grc.grid_datasource_name = dataSource
1169 and grc.grid_datasource_name = cgc.grid_datasource_name(+)
1170 and grc.grid_col_alias = cgc.grid_col_alias(+)
1171 and cgc.custom_grid_id(+) = x_custom_grid_id
1172 order by grc.query_seq;
1173
1174
1175 cursor displaySeq(dataSource in varchar2) is
1176 select grc.grid_col_alias
1177 from jtf_grid_cols_b grc
1178 where grc.grid_datasource_name = datasource
1179 order by grc.display_seq;
1180
1181 cursor folderDisplaySeq(dataSource in varchar2, x_custom_grid_id in number) is
1182 select grc.grid_col_alias
1183 from jtf_grid_cols_vl grc
1184 ,jtf_custom_grid_cols cgc
1185 where grc.grid_datasource_name = datasource
1186 and grc.grid_datasource_name = cgc.grid_datasource_name(+)
1187 and grc.grid_col_alias = cgc.grid_col_alias(+)
1188 and cgc.custom_grid_id(+) = x_custom_grid_id
1189 order by cgc.display_seq;
1190
1191
1192 cursor currency_col_alias(x_dataSource in varchar2,x_db_currency_code_col in varchar2) is
1193 select grc.grid_col_alias
1194 from jtf_grid_cols_b grc
1195 where grc.grid_datasource_name = x_dataSource
1196 and grc.db_col_name = x_db_currency_code_col;
1197
1198 INVALID_DATASOURCE exception;
1199 NO_DATASOURCE exception;
1200
1201 i binary_integer;
1202
1203 display_index integer := 0;
1204 grid_col_alias varchar2(30);
1205 begin
1206 if gridName is null
1207 or dataSource is null then
1208 raise NO_DATASOURCE;
1209 end if;
1210
1211 i := nvl(columnDefs.LAST,0) + 1;
1212
1213 -- if we are using the default settings
1214 if tableDefs(tableIndex).custom_grid_id is null then
1215 open columnDef(gridName,dataSource);
1216 fetch columnDef into columnDefs(i);
1217
1218 if columnDef%NOTFOUND then
1219 close columnDef;
1220 raise INVALID_DATASOURCE;
1221 end if;
1222
1223 while columnDef%FOUND loop
1224 -- if db_currency_code_col is not null then we search the metadata definition
1225 -- for a spreadtablecolumn based on this database column. If we find one,the
1226 -- developer will be able to use jtf_grid.getColumnNumberValue to retrieve the numeric value.
1227 -- If we don't find one, we have no idea (in the midtier) how the value is formatted, hence
1228 -- the developer will have to do the conversion themselves, ie using jtf_grid.getColumnCharValue
1229 if columnDefs(i).db_currency_code_col is not null then
1230 open currency_col_alias(dataSource,columnDefs(i).db_currency_code_col);
1231 fetch currency_col_alias into columnDefs(i).db_currency_col_alias;
1232 close currency_col_alias;
1233 end if;
1234 i := i + 1;
1235 fetch columnDef into columnDefs(i);
1236 end loop;
1237 close columnDef;
1238
1239 open displaySeq(dataSource);
1240 fetch displaySeq into grid_col_alias;
1241 while displaySeq%FOUND loop
1242 i := findColumnDefIndex(gridName,grid_col_alias);
1243 columnDefs(i).display_index := display_index;
1244
1245 fetch displaySeq into grid_col_alias;
1246 display_index := display_index + 1;
1247 end loop;
1248 close displaySeq;
1249
1250 -- if we are using a folder
1251 else
1252 open columnFolderDef(gridName,dataSource,tableDefs(tableIndex).custom_grid_id);
1253 fetch columnFolderDef into columnDefs(i);
1254
1255 if columnFolderDef%NOTFOUND then
1256 close columnFolderDef;
1257 raise INVALID_DATASOURCE;
1258 end if;
1259
1260 while columnFolderDef%FOUND loop
1261 -- if db_currency_code_col is not null then we search the metadata definition
1262 -- for a spreadtablecolumn based on this database column. If we find one,the
1263 -- developer will be able to use jtf_grid.getColumnNumberValue to retrieve the numeric value.
1264 -- If we don't find one, we have no idea (in the midtier) how the value is formatted, hence
1265 -- the developer will have to do the conversion themselves, ie using jtf_grid.getColumnCharValue
1266 if columnDefs(i).db_currency_code_col is not null then
1267 open currency_col_alias(dataSource,columnDefs(i).db_currency_code_col);
1268 fetch currency_col_alias into columnDefs(i).db_currency_col_alias;
1269 close currency_col_alias;
1270 end if;
1271 i := i + 1;
1272 fetch columnFolderDef into columnDefs(i);
1273 end loop;
1274 close columnFolderDef;
1275
1276 ------------------------------------
1277 -- IS THERE A MORE EFFICIENT WAY OF DOING THIS (GETTING THE DISPLAY_INDEX) THAN
1278 -- MAKING TWO PASSES AND SEARCHING THROUGH THE MEMORY
1279 ------------------------------------
1280 open folderDisplaySeq(dataSource,tableDefs(tableIndex).custom_grid_id);
1281 fetch folderDisplaySeq into grid_col_alias;
1282 while folderDisplaySeq%FOUND loop
1283 i := findColumnDefIndex(gridName,grid_col_alias);
1284 columnDefs(i).display_index := display_index;
1285
1286 fetch folderDisplaySeq into grid_col_alias;
1287 display_index := display_index + 1;
1288 end loop;
1289 close folderDisplaySeq;
1290 end if;
1291
1292 exception
1293 when NO_DATASOURCE then
1294 handleErrors(INVALID_DATASOURCE_ERROR,'getColDefsFromDB',gridName,dataSource,null);
1295 when INVALID_DATASOURCE then
1296 handleErrors(INVALID_DATASOURCE_ERROR,'getColDefsFromDB',gridName,dataSource,null);
1297 /*
1298 when OTHERS then
1299 handleErrors(INTERNAL_ERROR,'getColDefsFromDB',gridName,
1300 ' dataSource = <'||nvl(dataSource,nullValue)||'>',SQLERRM);
1301 */
1302 end getColDefsFromDB;
1303
1304 -- Check to see whether the sort columns as defined in tableDefs also
1305 -- exists in columnDefs. This could get corrupted if:
1306 -- 1. The end user sorts on a few columns.
1307 -- 2. Saves the customizations.
1308 -- 3. The columns that the end user sorted on are deleted from the
1309 -- metadata definition.
1310 --
1311 -- Here we check whether they exist in the metadata definition, if not we load
1312 -- the default sort columns and apply these instead. As a final step we
1313 -- update the customization data to reflect the default sort order.
1314 procedure repairSortColumns(gridName in varchar2
1315 ,tableIndex in binary_integer
1316 ,dataSource in varchar2) is
1317 PRAGMA AUTONOMOUS_TRANSACTION;
1318
1319 cursor sortCols(dataSource in varchar2) is
1320 select gsc.grid_sort_col_alias1
1321 ,gsc.grid_sort_col_alias2
1322 ,gsc.grid_sort_col_alias3
1323 from jtf_grid_sort_cols gsc
1324 where gsc.grid_datasource_name = dataSource;
1325
1326 l_grid_sort_col_alias1 varchar2(30);
1327 l_grid_sort_col_alias2 varchar2(30);
1328 l_grid_sort_col_alias3 varchar2(30);
1329
1330 begin
1331 open sortCols(dataSource);
1332 fetch sortCols into l_grid_sort_col_alias1, l_grid_sort_col_alias2, l_grid_sort_col_alias3;
1333 if sortCols%NOTFOUND then
1334 /* close sortCols;
1335
1336 handleErrors(MISSING_SORT_COL_ERROR,'repairSortColumns',tableIndex,dataSource,null);
1337 return;
1338 */
1339 l_grid_sort_col_alias1 := NULL;
1340 l_grid_sort_col_alias2 := NULL;
1341 l_grid_sort_col_alias3 := NULL;
1342 end if;
1343 close sortCols;
1344 tableDefs(tableIndex).grid_sort_col_alias1 := l_grid_sort_col_alias1;
1345 tableDefs(tableIndex).grid_sort_col_alias2 := l_grid_sort_col_alias2;
1346 tableDefs(tableIndex).grid_sort_col_alias3 := l_grid_sort_col_alias3;
1347 update jtf_custom_grids
1348 set grid_sort_col_alias1 = l_grid_sort_col_alias1
1349 ,grid_sort_col_alias2 = l_grid_sort_col_alias2
1350 ,grid_sort_col_alias3 = l_grid_sort_col_alias3
1351 where custom_grid_id = tableDefs(tableIndex).custom_grid_id;
1352 commit;
1353
1354 exception
1355 when others then
1356 rollback;
1357 raise;
1358 end repairSortColumns;
1359
1360 -- Check that the columnAlias exist in columnDefs
1361 -- if columnAlias is null then we return true no matter what.
1362 procedure validateColumn (gridName in varchar2
1363 ,tableIndex in binary_integer
1364 ,columnAlias in varchar2
1365 ,raiseError in boolean
1366 ,errorType in pls_integer
1367 ,caller in varchar2
1368 ,broken in out nocopy boolean) is
1369 INVALID_COLUMN exception;
1370 begin
1371 if columnAlias is null or broken then
1372 return;
1373 end if;
1374 if findColumnDefIndex(gridName,columnAlias) is null then
1375 broken := true;
1376 if raiseError then
1377 raise INVALID_COLUMN;
1378 end if;
1379 end if;
1380 exception
1381 when INVALID_COLUMN then
1382 broken := true;
1383 handleErrors(errorType,caller,tableIndex,columnAlias,null);
1384 end validateColumn;
1385
1386 -- Check to see whether the sort columns as defined in tableDefs also
1387 -- exists in columnDefs. This could get corrupted if:
1388 -- 1. The end user sorts on a few columns.
1389 -- 2. Saves the customizations.
1390 -- 3. The columns that the end user sorted on are deleted from the
1391 -- metadata definition.
1392 -- 4. The columndefinition for the column used in the sort order was deleted from
1393 -- JTF_GRID_COLS_B
1394 -- If corrupt we return false, the caller is responsible for calling repairSortColumns
1395 -- to do the dirty work.
1396 procedure validateSortColumns(gridName in varchar2
1397 ,tableIndex in binary_integer
1398 ,raiseError in boolean
1399 ,broken in out nocopy boolean) is
1400 columnAlias varchar2(30);
1401 begin
1402 -- If the sort_col_alias is not null, and we get null back from findColumDefIndex
1403 -- then something is wrong and we need to rectify the situation.
1404 columnAlias := tableDefs(tableIndex).grid_sort_col_alias1;
1405 validateColumn(gridName,tableIndex,columnAlias,raiseError,ILLEGAL_SORT_COLUMN_ERROR,'validateSortColumns',broken);
1406 columnAlias := tableDefs(tableIndex).grid_sort_col_alias2;
1407 validateColumn(gridName,tableIndex,columnAlias,raiseError,ILLEGAL_SORT_COLUMN_ERROR,'validateSortColumns',broken);
1408 columnAlias := tableDefs(tableIndex).grid_sort_col_alias3;
1409 validateColumn(gridName,tableIndex,columnAlias,raiseError,ILLEGAL_SORT_COLUMN_ERROR,'validateSortColumns',broken);
1410 end validateSortColumns;
1411
1412 -- add the sort order to the current stream. Caller is responsible for clearing and
1413 -- retrieving the stream.
1414 procedure serializeSortOrder(gridName in varchar2
1415 ,tableIndex in binary_integer
1416 ,includeSortDirection in boolean) is
1417 sortCols integer;
1418 j binary_integer;
1419 broken boolean := false;
1420 begin
1421 -- we check to see that the sort columns are not corrupt.
1422 -- if we are not using the default metadata definition we try to repair the metadata definition.
1423 if tableDefs(tableIndex).custom_grid_id is not null then
1424 -- don't raise an error yet, we may be able to repair the broken defintion
1425 validateSortColumns(gridName,tableIndex,false,broken);
1426 if broken then
1427 repairSortColumns(gridName,tableIndex,tableDefs(tableIndex).grid_dataSource_name);
1428 end if;
1429 end if;
1430 broken := false;
1431 -- for non default metadata definitions we now check whether we could repair the metadata, if not
1432 -- we raise an error.
1433 -- for detault metadata definitions we check for the first time and immediately raise the error
1434 -- as there is no hope of repairing the definition.
1435 validateSortColumns(gridName,tableIndex,true,broken);
1436 if broken then
1437 return;
1438 end if;
1439
1440 -- SERIALIZE THE SORTORDER
1441 -- Serialization requires: number of sorted cols, lowest prio sorted col to highest prio
1442 sortCols := 0;
1443 if tableDefs(tableIndex).grid_sort_col_alias1 is not null then
1444 sortCols := sortCols + 1;
1445 end if;
1446 if tableDefs(tableIndex).grid_sort_col_alias2 is not null then
1447 sortCols := sortCols + 1;
1448 end if;
1449 if tableDefs(tableIndex).grid_sort_col_alias3 is not null then
1450 sortCols := sortCols + 1;
1451 end if;
1452 jtf_dbstream_utils.writeInt(sortCols);
1453 if tableDefs(tableIndex).grid_sort_col_alias3 is not null then
1454 jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias3) - 1); -- -1 since we need a 0 based index
1455 if includeSortDirection then
1456 j := findColumnDefIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias3);
1457 if j is not null then
1458 jtf_dbstream_utils.writeString(columnDefs(j).sort_asc_by_default_flag);
1459 else
1460 -- redundant, this was checked in validateSortColumns, but for good measure....
1461 handleErrors(ILLEGAL_SORT_COLUMN_ERROR,'serializeSortOrder',tableIndex,tableDefs(tableIndex).grid_sort_col_alias3,null);
1462 end if;
1463 end if;
1464 sortCols := sortCols + 1;
1465 end if;
1466 if tableDefs(tableIndex).grid_sort_col_alias2 is not null then
1467 jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias2) - 1); -- -1 since we need a 0 based index
1468 if includeSortDirection then
1469 j := findColumnDefIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias2);
1470 if j is not null then
1471 jtf_dbstream_utils.writeString(columnDefs(j).sort_asc_by_default_flag);
1472 else
1473 -- redundant, this was checked in validateSortColumns, but for good measure....
1474 handleErrors(ILLEGAL_SORT_COLUMN_ERROR,'serializeSortOrder',tableIndex,tableDefs(tableIndex).grid_sort_col_alias2,null);
1475 end if;
1476 end if;
1477 sortCols := sortCols + 1;
1478 end if;
1479 if tableDefs(tableIndex).grid_sort_col_alias1 is not null then
1480 jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias1) - 1); -- -1 since we need a 0 based index
1481 if includeSortDirection then
1482 j := findColumnDefIndex(gridName,tableDefs(tableIndex).grid_sort_col_alias1);
1483 if j is not null then
1484 jtf_dbstream_utils.writeString(columnDefs(j).sort_asc_by_default_flag);
1485 else
1486 -- redundant, this was checked in validateSortColumns, but for good measure....
1487 handleErrors(ILLEGAL_SORT_COLUMN_ERROR,'serializeSortOrder',tableIndex,tableDefs(tableIndex).grid_sort_col_alias1,null);
1488 end if;
1489 end if;
1490 sortCols := sortCols + 1;
1491 end if;
1492 -- END SORTORDER
1493 end serializeSortOrder;
1494
1495 -- Build and return a string representing the serialized form of the spreadtable Meta Data
1496 procedure getSerializedTableDef(gridName in varchar2
1497 ,dataSource in varchar2
1498 ,tableIndex out nocopy binary_integer
1499 ,outPutStream out NOCOPY jtf_dbstream_utils.streamType) is
1500
1501 INVALID_DATASOURCE exception;
1502 i binary_integer;
1503 j binary_integer;
1504
1505 sortCols integer;
1506 startPos integer;
1507 endPos integer;
1508 begin
1509 if gridName is null
1510 or dataSource is null then
1511 jtf_dbstream_utils.clearOutputStream;
1512 outPutStream := jtf_dbstream_utils.getLongOutputStream;
1513 tableIndex := null;
1514 end if;
1515 i := findTableDefIndex(gridName);
1516 if i is null then
1517 i := getTableDefFromDB(gridName,dataSource);
1518 end if;
1519 if i is null then
1520 raise INVALID_DATASOURCE;
1521 end if;
1522
1523 jtf_dbstream_utils.clearOutputStream;
1524 -- SERIALIZE THE TABLEDEF
1525 jtf_dbstream_utils.writeString(tabVersion);
1526 -- Boolean to indicate whether the spreadtable is being initialized
1527 -- using an end users customizations.
1528 if tableDefs(i).custom_grid_id is null then
1529 jtf_dbstream_utils.writeBoolean(false);
1530 else
1531 jtf_dbstream_utils.writeBoolean(true);
1532 end if;
1533 -- HACK UP THE COLOR CODE IN THREE PARTS (R,G,B)
1534 startPos := 1;
1535 for x in 1..2 loop
1536 endPos := instr(tableDefs(i).alt_color_code,',',startPos);
1537 jtf_dbstream_utils.writeString(substr(tableDefs(i).alt_color_code,startPos,endPos-startPos));
1538 startPos := endPos + 1;
1539 end loop;
1540 jtf_dbstream_utils.writeString(substr(tableDefs(i).alt_color_code,startPos));
1541 -- END COLOR CODE
1542 jtf_dbstream_utils.writeInt(tableDefs(i).alt_color_interval);
1543 jtf_dbstream_utils.writeInt(tableDefs(i).default_row_height);
1544 jtf_dbstream_utils.writeString(tableDefs(i).title_text);
1545 jtf_dbstream_utils.writeInt(tableDefs(i).colCount);
1546 -- END TABLEDEF
1547
1548 if not isTabColsInMemory(gridName) then
1549 getColDefsFromDB(gridName,dataSource,i);
1550 end if;
1551 j := findNextColumnDefIndex(gridName,0);
1552
1553 -- SERIALIZE THE COLDEF
1554 while j is not null loop
1555 jtf_dbstream_utils.writeString(colDefVersion);
1556 jtf_dbstream_utils.writeString(columnDefs(j).grid_col_alias);
1557 jtf_dbstream_utils.writeInt(columnDefs(j).display_hsize);
1558 jtf_dbstream_utils.writeString(columnDefs(j).label_text);
1559 jtf_dbstream_utils.writeString(columnDefs(j).header_alignment_code);
1560 jtf_dbstream_utils.writeString(columnDefs(j).cell_alignment_code);
1561 jtf_dbstream_utils.writeString(columnDefs(j).data_type_code);
1562 jtf_dbstream_utils.writeInt(columnDefs(j).display_index);
1563 jtf_dbstream_utils.writeString(columnDefs(j).checkbox_checked_value);
1564 jtf_dbstream_utils.writeString(columnDefs(j).checkbox_unchecked_value);
1565 jtf_dbstream_utils.writeString(columnDefs(j).checkbox_other_values);
1566 jtf_dbstream_utils.writeString(columnDefs(j).display_type_code);
1567 jtf_dbstream_utils.writeString(columnDefs(j).visible_flag);
1568 jtf_dbstream_utils.writeString(columnDefs(j).freeze_visible_flag);
1569 jtf_dbstream_utils.writeString(columnDefs(j).sortable_flag);
1570 jtf_dbstream_utils.writeString(columnDefs(j).sort_asc_by_default_flag);
1571 j := findNextColumnDefIndex(gridName,j);
1572 end loop;
1573 -- END COLDEF
1574
1575 serializeSortOrder(gridName,i,false);
1576 -------------------
1577 -- The code below has been moved to serializeSortOrder
1578 -------------------
1579
1580 -- if we are not using the default metadata definition
1581 -- we check to see that the sort columns are not corrupt.
1582 -- if tableDefs(i).custom_grid_id is not null then
1583 -- validateSortColumns(gridName,i,dataSource);
1584 -- end if;
1585
1586 -- SERIALIZE THE SORTORDER
1587 -- Serialization requires: number of sorted cols, lowest prio sorted col to highest prio
1588 -- sortCols := 0;
1589 -- if tableDefs(i).grid_sort_col_alias1 is not null then
1590 -- sortCols := sortCols + 1;
1591 -- end if;
1592 -- if tableDefs(i).grid_sort_col_alias2 is not null then
1593 -- sortCols := sortCols + 1;
1594 -- end if;
1595 -- if tableDefs(i).grid_sort_col_alias3 is not null then
1596 -- sortCols := sortCols + 1;
1597 -- end if;
1598 -- jtf_dbstream_utils.writeInt(sortCols);
1599 -- if tableDefs(i).grid_sort_col_alias3 is not null then
1600 -- jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(i).grid_sort_col_alias3) - 1); -- -1 since we need a 0 based index
1601 -- sortCols := sortCols + 1;
1602 -- end if;
1603 -- if tableDefs(i).grid_sort_col_alias2 is not null then
1604 -- jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(i).grid_sort_col_alias2) - 1); -- -1 since we need a 0 based index
1605 -- sortCols := sortCols + 1;
1606 -- end if;
1607 -- if tableDefs(i).grid_sort_col_alias1 is not null then
1608 -- jtf_dbstream_utils.writeInt(findColumnIndex(gridName,tableDefs(i).grid_sort_col_alias1) - 1); -- -1 since we need a 0 based index
1609 -- sortCols := sortCols + 1;
1610 -- end if;
1611 -- END SORTORDER
1612 tableIndex := i;
1613 outPutStream := jtf_dbstream_utils.getLongOutputStream;
1614 exception
1615 when INVALID_DATASOURCE then
1616 handleErrors(INVALID_DATASOURCE_ERROR,'getSerializedTableDef',gridName,dataSource,null);
1617 /* when OTHERS then
1618 handleErrors(INTERNAL_ERROR,'getSerializedTableDef',gridName,
1619 ' dataSource = <'||nvl(dataSource,nullValue)||'>'||lineFeed||
1620 ' stream = <'||nvl(jtf_dbstream_utils.getOutputStream,nullValue)||'>'||lineFeed
1621 ,SQLERRM);
1622 */
1623 end getSerializedTableDef;
1624
1625
1626 -- Take a serialized stream, deserialize it, and then save it into the database.
1627 -- NOTE: This implementation only supports phase 1 of this functionality
1628 procedure saveSerializedTableDef(gridName in varchar2
1629 ,dataSource in varchar2
1630 ,customGridId in out nocopy number
1631 ,customGridName in out nocopy varchar2
1632 ,defaultFlag in boolean
1633 ,publicFlag in boolean
1634 ,inputStream jtf_dbstream_utils.streamType
1635 ,successFlag out nocopy boolean
1636 ,p_serverInitDate in date) is
1637
1638 PRAGMA AUTONOMOUS_TRANSACTION;
1639
1640 i binary_integer;
1641 j binary_integer;
1642
1643 sortCols integer;
1644 startPos integer;
1645 endPos integer;
1646
1647 l_tabVersion varchar2(5);
1648 l_colVersion varchar2(5);
1649 l_custom_grid_id number;
1650 l_custom_grid_name varchar2(80);
1651 l_default_row_height number(2);
1652 l_where_clause varchar2(2000);
1653 l_grid_sort_col_alias1 varchar2(30);
1654 l_grid_sort_col_alias2 varchar2(30);
1655 l_grid_sort_col_alias3 varchar2(30);
1656 l_public_flag varchar2(1) := jtf_dbstring_utils.getBooleanString(publicFlag);
1657 l_grid_col_alias varchar2(30);
1658 l_sort_asc_by_default varchar2(1);
1659 l_visible_flag varchar2(1);
1660 l_display_seq number(3);
1661 l_display_hsize number(6);
1662 l_label_text varchar2(80);
1663
1664 cursor folderColumnExists(x_custom_grid_id in number
1665 ,x_grid_datasource_name in varchar2
1666 ,x_grid_col_alias in varchar2) is
1667 select label_text
1668 from jtf_custom_grid_cols
1669 where custom_grid_id = x_custom_grid_id
1670 and grid_datasource_name = x_grid_datasource_name
1671 and grid_col_alias = x_grid_col_alias;
1672
1673 folder_col_label varchar2(80);
1674 sortCount integer;
1675 begin
1676 validateServer(p_serverInitDate, NULL);
1677 i := findTableDefIndex(gridName);
1678 if i is null then
1679 raise INVALID_GRID;
1680 end if;
1681
1682 if inputStream.COUNT = 0 then
1683 successFlag := false;
1684 return;
1685 elsif inputStream.COUNT = 1 then
1686 jtf_dbstream_utils.setInputStream(inputStream(inputStream.FIRST));
1687 else
1688 jtf_dbstream_utils.setLongInputStream(inputStream);
1689 end if;
1690 --handleErrors(INTERNAL_ERROR,'saveSerializedTableDef',gridName,inputStream(inputStream.FIRST),to_char(inputStream.COUNT));
1691
1692 l_tabVersion := jtf_dbstream_utils.readString;
1693 -- this is the current version
1694 if l_tabVersion = clientTabVersion then
1695 l_default_row_height := jtf_dbstream_utils.readInt;
1696 l_custom_grid_name := nvl(customGridName,USER_UI_DEFAULT_FOLDER); -- Use the default name until phase 2.
1697
1698 sortCount := jtf_dbstream_utils.readInt;
1699 -- Something really fishy has happened, we should always have one sort column.
1700 if sortCount = 0 then
1701 l_grid_sort_col_alias1 := tableDefs(i).grid_sort_col_alias1;
1702 end if;
1703 if sortCount > 0 then
1704 l_grid_sort_col_alias1 := findColumnAlias(gridName,(jtf_dbstream_utils.readInt + 1)); -- +1 since this is coming from
1705 end if;
1706 if sortCount > 1 then
1707 l_grid_sort_col_alias2 := findColumnAlias(gridName,(jtf_dbstream_utils.readInt + 1)); -- the client which uses 0 based
1708 end if;
1709 if sortCount > 2 then
1710 l_grid_sort_col_alias3 := findColumnAlias(gridName,(jtf_dbstream_utils.readInt + 1)); -- indexes
1711 end if;
1712 l_where_clause := null; -- null for now, only use the default.
1713
1714
1715 -- the folder already exists
1716 -- NOTE: For phase 2 we need a lot of checks here, phase 1 we only
1717 -- support one set of customizations / user and datasource
1718 --
1719 if tableDefs(i).custom_grid_id is null
1720 and customGridId is null then
1721
1722 select jtf_custom_grids_s.nextval
1723 into l_custom_grid_id
1724 from dual;
1725
1726 insert into jtf_custom_grids
1727 (custom_grid_id
1728 ,grid_datasource_name
1729 ,custom_grid_name
1730 ,language
1731 ,grid_sort_col_alias1
1732 ,grid_sort_col_alias2
1733 ,grid_sort_col_alias3
1734 ,public_flag
1735 ,created_by
1736 ,creation_date
1737 ,last_updated_by
1738 ,last_update_date
1739 ,last_update_login
1740 ,where_clause
1741 )
1742 values
1743 (l_custom_grid_id
1744 ,datasource
1745 ,l_custom_grid_name
1746 ,userenv('LANG')
1747 ,l_grid_sort_col_alias1
1748 ,l_grid_sort_col_alias2
1749 ,l_grid_sort_col_alias3
1750 ,l_public_flag
1751 ,fnd_global.user_id
1752 ,sysdate
1753 ,fnd_global.user_id
1754 ,sysdate
1755 ,fnd_global.login_id
1756 ,l_where_clause
1757 );
1758
1759 -- NOTE: This will have to change for phase 2
1760 insert into jtf_def_custom_grids
1761 (grid_datasource_name
1762 ,created_by
1763 ,creation_date
1764 ,last_updated_by
1765 ,last_update_date
1766 ,last_update_login
1767 ,custom_grid_id
1768 ,language
1769 )
1770 values
1771 (datasource
1772 ,fnd_global.user_id
1773 ,sysdate
1774 ,fnd_global.user_id
1775 ,sysdate
1776 ,fnd_global.login_id
1777 ,l_custom_grid_id
1778 ,userenv('LANG')
1779 );
1780 else
1781 -- NOTE: For phase 2 we need several checks here
1782 if customGridId is null then
1783 l_custom_grid_id := tableDefs(i).custom_grid_id;
1784 else
1785 l_custom_grid_id := customGridId;
1786 end if;
1787 update jtf_custom_grids
1788 set custom_grid_name = l_custom_grid_name
1789 ,grid_sort_col_alias1 = l_grid_sort_col_alias1
1790 ,grid_sort_col_alias2 = l_grid_sort_col_alias2
1791 ,grid_sort_col_alias3 = l_grid_sort_col_alias3
1792 ,public_flag = l_public_flag
1793 ,last_updated_by = fnd_global.user_id
1794 ,last_update_date = sysdate
1795 ,last_update_login = fnd_global.login_id
1796 ,where_clause = l_where_clause
1797 where custom_grid_id = l_custom_grid_id;
1798 end if;
1799 -- also update the information we hold in tableDefs
1800 tableDefs(i).custom_grid_id := l_custom_grid_id;
1801 tableDefs(i).custom_grid_name := l_custom_grid_name;
1802 tableDefs(i).grid_sort_col_alias1 := l_grid_sort_col_alias1;
1803 tableDefs(i).grid_sort_col_alias2 := l_grid_sort_col_alias2;
1804 tableDefs(i).grid_sort_col_alias3 := l_grid_sort_col_alias3;
1805 tableDefs(i).public_flag := l_public_flag;
1806 tableDefs(i).owner := fnd_global.user_id;
1807 end if;
1808
1809 -- END TABLEDEF
1810 -- DESERIALIZE THE COLDEF
1811 for k in 1..tableDefs(i).colCount loop
1812 l_colVersion := jtf_dbstream_utils.readString;
1813 if l_colVersion = clientColDefVersion then
1814 l_grid_col_alias := jtf_dbstream_utils.readString;
1815 l_display_hsize := jtf_dbstream_utils.readInt;
1816 l_label_text := jtf_dbstream_utils.readString;
1817 l_display_seq := jtf_dbstream_utils.readInt;
1818 l_visible_flag := jtf_dbstream_utils.readString;
1819 l_sort_asc_by_default := jtf_dbstream_utils.readString;
1820
1821 open folderColumnExists(l_custom_grid_id,datasource,l_grid_col_alias);
1822 fetch folderColumnExists into folder_col_label;
1823
1824 j := findColumnDefIndex(gridName,l_grid_col_alias);
1825
1826 -- We don't save the label if it hasn't changed
1827 if folder_col_label is null
1828 and l_label_text = columnDefs(j).label_text then
1829 l_label_text := null;
1830 end if;
1831
1832 if folderColumnExists%NOTFOUND then
1833 close folderColumnExists;
1834 insert into jtf_custom_grid_cols
1835 (custom_grid_id
1836 ,grid_datasource_name
1837 ,grid_col_alias
1838 ,sort_asc_by_default_flag
1839 ,visible_flag
1840 ,display_seq
1841 ,display_hsize
1842 ,created_by
1843 ,creation_date
1844 ,last_updated_by
1845 ,last_update_date
1846 ,last_update_login
1847 ,label_text
1848 )
1849 values
1850 (l_custom_grid_id
1851 ,datasource
1852 ,l_grid_col_alias
1853 ,l_sort_asc_by_default
1854 ,l_visible_flag
1855 ,l_display_seq
1856 ,l_display_hsize
1857 ,fnd_global.user_id
1858 ,sysdate
1859 ,fnd_global.user_id
1860 ,sysdate
1861 ,fnd_global.login_id
1862 ,l_label_text
1863 );
1864 else
1865 close folderColumnExists;
1866 update jtf_custom_grid_cols
1867 set sort_asc_by_default_flag = l_sort_asc_by_default
1868 ,visible_flag = l_visible_flag
1869 ,display_seq = l_display_seq
1870 ,display_hsize = l_display_hsize
1871 ,last_updated_by = fnd_global.user_id
1872 ,last_update_date = sysdate
1873 ,last_update_login = fnd_global.login_id
1874 ,label_text = l_label_text
1875 where custom_grid_id = l_custom_grid_id
1876 and grid_datasource_name = dataSource
1877 and grid_col_alias = l_grid_col_alias;
1878 end if;
1879 end if;
1880 columnDefs(j).sort_asc_by_default_flag := l_sort_asc_by_default;
1881 columnDefs(j).visible_flag := l_visible_flag;
1882 columnDefs(j).display_index := l_display_seq;
1883 columnDefs(j).display_hsize := l_display_hsize;
1884 columnDefs(j).label_text := l_label_text;
1885 end loop;
1886 -- END COLDEF
1887 commit;
1888 customGridId := l_custom_grid_id;
1889 customGridName := l_custom_grid_name;
1890 successFlag := true;
1891 exception
1892 when INVALID_GRID then
1893 rollback;
1894 successFlag := false;
1895 handleErrors(INVALID_GRID_ERROR,'saveSerializedTableDef',gridName,null,null);
1896 when OTHERS then
1897 handleErrors(INTERNAL_ERROR,'saveSerializedTableDef',gridName,null,SQLERRM);
1898 rollback;
1899 successFlag := false;
1900 -- handleErrors(INTERNAL_ERROR,'saveSerializedTableDef',gridName,
1901 -- ' dataSource = <'||nvl(dataSource,nullValue)||'>'||lineFeed||
1902 -- ' custom_grid_id = <'||nvl(to_char(l_custom_grid_id),nullValue)||'>'||lineFeed||
1903 -- ' custom_grid_name = <'||nvl(l_custom_grid_name,nullValue)||'>'||lineFeed||
1904 -- ' default_flag = <'||nvl(jtf_dbstring_utils.getBooleanString(defaultFlag),nullValue)||'>'||lineFeed||
1905 -- ' public_flag = <'||nvl(l_public_flag,nullValue)||'>'||lineFeed
1906 -- null,SQLERRM);
1907 -- return false;
1908 end saveSerializedTableDef;
1909
1910
1911 -- Delete the given set of customizations. Remove all references to it
1912 -- from JTF_DEF_CUSTOM_GRIDS.
1913 -- If customGridId is null we delete the current set.
1914 function deleteCustomizations(gridName in varchar2
1915 ,customGridId in number
1916 ,p_serverInitDate in date) return boolean is
1917
1918 PRAGMA AUTONOMOUS_TRANSACTION;
1919 i binary_integer;
1920 l_custom_grid_id number;
1921 begin
1922 validateServer(p_serverInitDate, gridName);
1923 i := findTableDefIndex(gridName);
1924 if i is null then
1925 raise INVALID_GRID;
1926 end if;
1927 if customGridId is null
1928 and tableDefs(i).custom_grid_id is null then
1929 return true;
1930 end if;
1931 -- If customGridId is null we delete the current set.
1932 l_custom_grid_id := nvl(customGridId,tableDefs(i).custom_grid_id);
1933 delete from jtf_def_custom_grids
1934 where custom_grid_id = l_custom_grid_id;
1935 delete from jtf_custom_grid_cols
1936 where custom_grid_id = l_custom_grid_id;
1937 delete from jtf_custom_grids
1938 where custom_grid_id = l_custom_grid_id;
1939 tableDefs(i).custom_grid_id := null;
1940 tableDefs(i).custom_grid_name := null;
1941 tableDefs(i).public_flag := null;
1942 tableDefs(i).owner := null;
1943 commit;
1944 return true;
1945 exception
1946 when INVALID_GRID then
1947 rollback;
1948 handleErrors(INVALID_GRID_ERROR,'deleteCustomizations',gridName,null,null);
1949 when others then
1950 handleErrors(INTERNAL_ERROR,'saveSerializedTableDef',gridName,null,SQLERRM);
1951 rollback;
1952 return false;
1953 end deleteCustomizations;
1954
1955
1956 -- Set the new sort orders, and then refresh the grid
1957 procedure setSortCol(gridName in varchar2
1958 ,col_alias1 in varchar2
1959 ,sort_asc_flag1 in varchar2
1960 ,col_alias2 in varchar2
1961 ,sort_asc_flag2 in varchar2
1962 ,col_alias3 in varchar2
1963 ,sort_asc_flag3 in varchar2
1964 ,p_serverInitDate in date) is
1965
1966 oldDataSource varchar2(30);
1967 i binary_integer;
1968 j binary_integer;
1969 begin
1970 validateServer(p_serverInitDate, gridName);
1971 i := findTableDefIndex(gridName);
1972 if i is null then
1973 raise INVALID_GRID;
1974 end if;
1975 tableDefs(i).grid_sort_col_alias1 := col_alias1;
1976 tableDefs(i).grid_sort_col_alias2 := col_alias2;
1977 tableDefs(i).grid_sort_col_alias3 := col_alias3;
1978 -- CURSOR MGMT REWRITE
1979 tableDefs(i).hasWhereClauseChanged := 'T';
1980 -- CURSOR MGMT REWRITE END
1981 if col_alias1 is not null
1982 and sort_asc_flag1 is not null then
1983 j := findColumnDefIndex(gridName,col_alias1);
1984 if j is not null then
1985 columnDefs(j).sort_asc_by_default_flag := sort_asc_flag1;
1986 end if;
1987 end if;
1988 if col_alias2 is not null
1989 and sort_asc_flag2 is not null then
1990 j := findColumnDefIndex(gridName,col_alias2);
1991 if j is not null then
1992 columnDefs(j).sort_asc_by_default_flag := sort_asc_flag2;
1993 end if;
1994 end if;
1995 if col_alias3 is not null
1996 and sort_asc_flag3 is not null then
1997 j := findColumnDefIndex(gridName,col_alias3);
1998 if j is not null then
1999 columnDefs(j).sort_asc_by_default_flag := sort_asc_flag3;
2000 end if;
2001 end if;
2002
2003 refresh(gridName, i);
2004
2005 exception
2006 when INVALID_GRID then
2007 handleErrors(INVALID_GRID_ERROR,'setSortCol',gridName,null,null);
2008 /*
2009 when OTHERS then
2010 handleErrors(INTERNAL_ERROR,'setSortCol',gridName,
2011 ' col_alias1 = <'||nvl(col_alias1,nullValue)||'>'||lineFeed||
2012 ' sort_asc_flag1 = <'||nvl(sort_asc_flag1,nullValue)||'>'||lineFeed||
2013 ' col_alias2 = <'||nvl(col_alias2,nullValue)||'>'||lineFeed||
2014 ' sort_asc_flag2 = <'||nvl(sort_asc_flag2,nullValue)||'>'||lineFeed||
2015 ' col_alias3 = <'||nvl(col_alias3,nullValue)||'>'||lineFeed||
2016 ' sort_asc_flag3 = <'||nvl(sort_asc_flag3,nullValue)||'>'||lineFeed
2017 ,SQLERRM);
2018 */
2019 end setSortCol;
2020
2021 procedure deleteColDef(gridName in varchar2) is
2022 i binary_integer;
2023 begin
2024 i := findNextColumnDefIndex(gridName,0);
2025 if i is null then
2026 return;
2027 end if;
2028
2029 while i is not null loop
2030 columnDefs.delete(i);
2031 i := findNextColumnDefIndex(gridName,i);
2032 end loop;
2033 /*
2034 exception
2035 when OTHERS then
2036 handleErrors(INTERNAL_ERROR,'deleteColDef',gridName,null,SQLERRM);
2037 */
2038 end deleteColDef;
2039
2040
2041
2042 procedure reset(gridName in varchar2, tableIndex in binary_integer) is
2043 begin
2044 if tableIndex is null then
2045 raise INVALID_GRID;
2046 end if;
2047
2048 -- If the package is has already been
2049 -- used to retrive data, reset it to
2050 -- its initial state
2051 --if tableDefs(tableIndex).moreRowsExists = 'T'
2052 -- 4282028
2053 if tableDefs(tableIndex).SQLCursor is not null
2054 and dbms_sql.is_open(tableDefs(tableIndex).SQLCursor) then
2055 dbms_sql.close_cursor(tableDefs(tableIndex).SQLCursor);
2056 end if;
2057
2058 -- if this function is being called then it passes server validation
2059 -- ideally this procedure/function should be called after remove all bind
2060 -- variables
2061 removeAllBindVariables(gridName, serverInitDate);
2062
2063 tableDefs.delete(tableIndex);
2064 deleteColDef(gridName);
2065
2066 exception
2067 when INVALID_GRID then
2068 handleErrors(INVALID_GRID_ERROR,'reset',gridName,null,null);
2069 /*
2070 when OTHERS then
2071 handleErrors(INTERNAL_ERROR,'reset',gridName,null,SQLERRM);
2072 */
2073 end reset;
2074
2075
2076 procedure reset(gridName in varchar2, p_serverInitDate in date) is
2077 i binary_integer;
2078 begin
2079 validateServer(p_serverInitDate, gridName);
2080
2081 i := findTableDefIndex(gridName);
2082 reset(gridName, i);
2083 end reset;
2084
2085 procedure init(gridName in varchar2
2086 ,dataSource in varchar2
2087 ,customGridId in out nocopy number
2088 ,customGridName in out nocopy varchar2
2089 ,outPutStream out nocopy jtf_dbstream_utils.streamType
2090 ,x_serverInitDate out nocopy date ) is
2091 i binary_integer;
2092 begin
2093 x_serverInitDate := serverInitDate;
2094 --dbms_output.put_line(to_char(x_serverInitDate, 'HH:MI:SS')|| 'sysdate is ' ||to_char(sysdate, 'HH:MI:SS'));
2095 i := findTableDefIndex(gridName);
2096 if i is not null then
2097 reset(gridName,i);
2098 end if;
2099 getSerializedTableDef(gridName,dataSource,i,outPutStream);
2100 customGridId := tableDefs(i).custom_grid_id;
2101 customGridName := tableDefs(i).custom_grid_name;
2102 /*
2103 exception
2104 when OTHERS then
2105 handleErrors(INIT_ERROR,'init',gridName,
2106 ' dataSource = <'||nvl(dataSource,nullValue)||'>'||lineFeed
2107 ,SQLERRM);
2108 */
2109 end init;
2110
2111 -------------------------------------------------------------------------
2112 procedure refresh(gridName in varchar2, p_serverInitDate in date) is
2113 i binary_integer;
2114 begin
2115 validateServer(p_serverInitDate, gridName);
2116 i := findTableDefIndex(gridName);
2117 if i is null then
2118 raise INVALID_GRID;
2119 end if;
2120 refresh(gridName,i);
2121 exception
2122 when INVALID_GRID then
2123 handleErrors(INVALID_GRID_ERROR,'refresh',gridName,null,null);
2124 /*
2125 when OTHERS then
2126 handleErrors(INTERNAL_ERROR,'refresh',gridName,null,SQLERRM);
2127 */
2128 end refresh;
2129
2130
2131 function getWhereClause(gridName in varchar2, p_serverInitDate in date) return varchar2 is
2132 i binary_integer;
2133 begin
2134 validateServer(p_serverInitDate, gridName);
2135 i := findTableDefIndex(gridName);
2136 if i is null then
2137 raise INVALID_GRID;
2138 end if;
2139 return tableDefs(i).where_clause;
2140 exception
2141 when INVALID_GRID then
2142 handleErrors(INVALID_GRID_ERROR,'getWhereClause',gridName,null,null);
2143 end getWhereClause;
2144
2145 procedure setWhereClause(gridName in varchar2, whereClause in varchar2
2146 ,p_serverInitDate in date) is
2147 i binary_integer;
2148 begin
2149 validateServer(p_serverInitDate, gridName);
2150 i := findTableDefIndex(gridName);
2151 if i is null then
2152 raise INVALID_GRID;
2153 end if;
2154 -- If the whereclause hasn't changed, do nothing
2155 if (tableDefs(i).where_clause is null
2156 and whereClause is null)
2157 or (tableDefs(i).where_clause is not null
2158 and whereClause is not null
2159 and tableDefs(i).where_clause = whereClause) then
2160 null;
2161 -- Otherwise, set the new whereclause and the flag
2162 -- indicating that things has changed and need reparsing
2163 else
2164 tableDefs(i).where_clause := whereClause;
2165 tableDefs(i).hasWhereClauseChanged := 'T';
2166 end if;
2167 refresh(gridName,i);
2168 exception
2169 when INVALID_GRID then
2170 handleErrors(INVALID_GRID_ERROR,'setWhereClause',gridName,null,null);
2171 end setWhereClause;
2172
2173 function getSQLStatement(gridName in varchar2 ,p_serverInitDate in date) return varchar2 is
2174 i binary_integer;
2175 begin
2176 validateServer(p_serverInitDate, gridName);
2177 i := findTableDefIndex(gridName);
2178 if i is null then
2179 raise INVALID_GRID;
2180 end if;
2181 return tableDefs(i).SQLStatement;
2182 exception
2183 when INVALID_GRID then
2184 handleErrors(INVALID_GRID_ERROR,'getSQLStatement',gridName,null,null);
2185 end getSQLStatement;
2186
2187 -- CURSOR MGMT REWRITE
2188 procedure setHasBindVarsChanged(gridName in varchar2) is
2189 i binary_integer;
2190 begin
2191 i := findTableDefIndex(gridName);
2192 if i is null then
2193 raise INVALID_GRID;
2194 end if;
2195 tableDefs(i).hasBindVarsChanged := 'T';
2196 exception
2197 when INVALID_GRID then
2198 handleErrors(INVALID_GRID_ERROR,'setHasBindVariablesChanged',gridName,null,null);
2199 end setHasBindVarsChanged;
2200 -- CURSOR MGMT REWRITE END
2201
2202 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in varchar2) is
2203 i binary_integer;
2204 begin
2205 i := findBindVariableIndex(gridName, variableName);
2206 bindVariables(i).gridName := gridName;
2207 bindVariables(i).variableName := variableName;
2208 bindVariables(i).variableDataType := 'C';
2209 bindVariables(i).variableCharValue := variableValue;
2210 bindVariables(i).variableDateValue := null;
2211 bindVariables(i).variableNumberValue := null;
2212 -- CURSOR MGMT REWRITE
2213 setHasBindVarsChanged(gridName);
2214 -- CURSOR MGMT REWRITE END
2215
2216 end setBindVariable;
2217
2218 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in varchar2 ,p_serverInitDate in date) is
2219 begin
2220 validateServer(p_serverInitDate, gridName);
2221 setBindVariable(gridName, variableName, variableValue);
2222 end setBindVariable;
2223
2224
2225 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in date) is
2226 i binary_integer;
2227 begin
2228 i := findBindVariableIndex(gridName, variableName);
2229 bindVariables(i).gridName := gridName;
2230 bindVariables(i).variableName := variableName;
2231 bindVariables(i).variableDataType := 'D';
2232 bindVariables(i).variableDateValue := variableValue;
2233 bindVariables(i).variableCharValue := null;
2234 bindVariables(i).variableNumberValue := null;
2235 -- CURSOR MGMT REWRITE
2236 setHasBindVarsChanged(gridName);
2237 -- CURSOR MGMT REWRITE END
2238 end setBindVariable;
2239
2240 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in date, p_serverInitDate in date) is
2241 begin
2242 validateServer(p_serverInitDate, gridName);
2243 setBindVariable(gridName, variableName, variableValue);
2244 end setBindVariable;
2245
2246
2247
2248 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in number) is
2249 i binary_integer;
2250 begin
2251 i := findBindVariableIndex(gridName, variableName);
2252 bindVariables(i).gridName := gridName;
2253 bindVariables(i).variableName := variableName;
2254 bindVariables(i).variableDataType := 'N';
2255 bindVariables(i).variableNumberValue := variableValue;
2256 bindVariables(i).variableDateValue := null;
2257 bindVariables(i).variableCharValue := null;
2258
2259 -- CURSOR MGMT REWRITE
2260 setHasBindVarsChanged(gridName);
2261 -- CURSOR MGMT REWRITE END
2262 end setBindVariable;
2263
2264 procedure setBindVariable(gridName in varchar2, variableName in varchar2, variableValue in number, p_serverInitDate in date) is
2265 begin
2266 validateServer(p_serverInitDate, gridName);
2267 setBindVariable(gridName, variableName, variableValue);
2268 end setBindVariable;
2269
2270 function getCharBindVariableValue(gridName in varchar2, variableName in varchar2,p_serverInitDate in date) return varchar2 is
2271 i binary_integer;
2272 bind_val varchar2(10) := NULL;
2273 begin
2274 validateServer(p_serverInitDate, gridName);
2275 i := findBindVariableIndex(gridName, variableName);
2276
2277 -- if the bind variable does not exist then one is created.
2278 -- if it exists but is not of type character then raise an error.
2279
2280 IF not bindVariables.EXISTS(i) THEN
2281 setBindVariable(gridName, variableName, bind_val);
2282 return bind_val;
2283
2284 ELSIF bindVariables(i).variableDataType <> 'C' THEN
2285
2286 handleErrors(APPLICATION_ERROR, 'getCharBindVariableValue', gridName,
2287 'Bind Variable '||replace(variableName,':')|| ' of type Char does not exist'||linefeed, null);
2288
2289 end if;
2290 return bindVariables(i).variableCharValue;
2291
2292 /* 08/24 this procedure returns error if the bind variable is not found
2293 -- CURSOR MGMT REWRITE
2294 if not bindVariables.EXISTS(i) then
2295 setHasBindVarsChanged(gridName);
2296 end if;
2297 -- CURSOR MGMT REWRITE END
2298
2299 -- this might be a new one, so assign the proper values just in case, and return the value
2300 bindVariables(i).gridName := gridName;
2301 bindVariables(i).variableName := variableName;
2302 bindVariables(i).variableDataType := 'C';
2303 return bindVariables(i).variableCharValue;
2304 */
2305 end getCharBindVariableValue;
2306
2307 function getDateBindVariableValue(gridName in varchar2, variableName in varchar2,p_serverInitDate in date) return date is
2308 i binary_integer;
2309 bind_val date;
2310 begin
2311 validateServer(p_serverInitDate, gridName);
2312 i := findBindVariableIndex(gridName, variableName);
2313
2314 -- if the bind variable does not exist then one is created.
2315 -- if it exists but is not of type date then raise an error.
2316
2317 if not bindVariables.EXISTS(i) then
2318 setBindVariable(gridName, variableName, bind_val);
2319 return bind_val;
2320
2321 elsif bindVariables(i).variableDataType <> 'D' then
2322
2323 handleErrors(APPLICATION_ERROR, 'getDateBindVariableValue',i ,
2324 'Bind Variable '||replace(variableName,':')|| ' of type Date does not exist'
2325 ||linefeed,null);
2326
2327 end if;
2328
2329 return bindVariables(i).variableDateValue;
2330
2331 /* 08/24 this procedure returns error if the bind variable is not found
2332 -- CURSOR MGMT REWRITE
2333 if not bindVariables.EXISTS(i) then
2334 setHasBindVarsChanged(gridName);
2335 end if;
2336 -- CURSOR MGMT REWRITE END
2337
2338
2339 -- this might be a new one, so assign the proper values just in case, and return the value
2340 bindVariables(i).gridName := gridName;
2341 bindVariables(i).variableName := variableName;
2342 bindVariables(i).variableDataType := 'D';
2343 return bindVariables(i).variableDateValue;
2344 */
2345 end getDateBindVariableValue;
2346
2347 function getNumberBindVariableValue(gridName in varchar2, variableName in varchar2,p_serverInitDate in date) return number is
2348 i binary_integer;
2349 bind_val number;
2350 begin
2351 validateServer(p_serverInitDate, gridName);
2352 i := findBindVariableIndex(gridName, variableName);
2353
2354 -- if the bind variable does not exist then one is created.
2355 -- if it exists but is not of type number then raise an error.
2356
2357 if not bindVariables.EXISTS(i) then
2358 setBindVariable(gridName, variableName, bind_val);
2359 return bind_val;
2360
2361 elsif bindVariables(i).variableDataType <> 'N' then
2362
2363 handleErrors(APPLICATION_ERROR, 'getNumberBindVariableValue', gridName,
2364 'Bind Variable '||replace(variableName,':')|| ' of type Integer does not exist'||linefeed, null);
2365
2366 end if;
2367
2368 return bindVariables(i).variableNumberValue;
2369
2370 /* this procedure returns error if the bind variable is not found 08/24
2371 -- CURSOR MGMT REWRITE
2372 if not bindVariables.EXISTS(i) then
2373 setHasBindVarsChanged(gridName);
2374 end if;
2375 -- CURSOR MGMT REWRITE END
2376
2377
2378 -- this might be a new one, so assign the proper values just in case, and return the value
2379 bindVariables(i).gridName := gridName;
2380 bindVariables(i).variableName := variableName;
2381 bindVariables(i).variableDataType := 'N';
2382 return bindVariables(i).variableNumberValue;
2383 */
2384 end getNumberBindVariableValue;
2385
2386 procedure removeAllBindVariables(gridName in varchar2) is
2387 i binary_integer;
2388 begin
2389 i := findNextBindVariable(gridName, null);
2390 while i is not null loop
2391 bindVariables.DELETE(i);
2392 i := findNextBindVariable(gridName, i);
2393 end loop;
2394 -- CURSOR MGMT REWRITE
2395 setHasBindVarsChanged(gridName);
2396 -- CURSOR MGMT REWRITE END
2397
2398 end removeAllBindVariables;
2399
2400
2401 procedure removeAllBindVariables(gridName in varchar2,p_serverInitDate in date) is
2402 begin
2403 validateServer(p_serverInitDate, gridName);
2404 removeAllBindVariables(gridName);
2405 end removeAllBindVariables;
2406
2407 function getCharProperty(gridName in varchar2, propertyType in varchar2, p_serverInitDate in date) return varchar2 is
2408 i binary_integer;
2409 j binary_integer;
2410 begin
2411 validateServer(p_serverInitDate, gridName);
2412 i := findTableDefIndex(gridName);
2413 if i is null then
2414 raise INVALID_GRID;
2415 end if;
2416 if propertyType = 'TITLE_TEXT' then
2417 return tableDefs(i).title_text;
2418 elsif propertyType = 'SERIALIZED_SORT_ORDER' then
2419 jtf_dbstream_utils.clearOutputStream;
2420 serializeSortOrder(gridName,i,true);
2421 -- we can safely do this as there is no way the stream can exceed 32k
2422 return jtf_dbstream_utils.getOutputStream;
2423
2424 elsif propertyType = 'ALL_BIND_VARIABLES' then
2425
2426 jtf_dbstream_utils.clearOutputStream;
2427 -- the first value will be the number of bind variables that are
2428 -- being passed.
2429
2430 jtf_dbstream_utils.writeNumber(bindVariablesCount(gridName));
2431
2432 if bindVariables.COUNT <> 0 then
2433 j := findNextBindVariable(gridName,null);
2434
2435 while j is not null loop
2436 jtf_dbstream_utils.writeString(bindVariables(j).variableName);
2437 jtf_dbstream_utils.writeString(bindVariables(j).variableDataType);
2438 if bindVariables(j).variableDataType = 'C' then
2439 jtf_dbstream_utils.writeString(bindVariables(j).variableCharValue);
2440 elsif bindVariables(j).variableDataType = 'N' then
2441 jtf_dbstream_utils.writeNumber(bindVariables(j).variableNumberValue);
2442 elsif bindVariables(j).variableDataType = 'D' then
2443 jtf_dbstream_utils.writeDateTime(bindVariables(j).variableDateValue);
2444 end if;
2445
2446 j := findNextBindVariable(gridName,j);
2447
2448 end loop;
2449 end if;
2450 if jtf_dbstream_utils.isLongOutputStream then
2451 handleErrors(MAXLENGTH_EXCEEDED_ERROR,'getCharProperty',
2452 gridName,' Get all bind variables'||lineFeed,null);
2453 end if;
2454 return jtf_dbstream_utils.getOutputStream;
2455
2456 else
2457 raise INVALID_PROPERTY;
2458 end if;
2459 return null;
2460 exception
2461 when INVALID_GRID then
2462 handleErrors(INVALID_GRID_ERROR,'getCharProperty',gridName,null,null);
2463 when INVALID_PROPERTY then
2464 handleErrors(INVALID_PROPERTY_ERROR,'getCharProperty',gridName,
2465 ' propertyType = <'||nvl(propertyType,nullValue)||'>'||lineFeed
2466 ,null);
2467 end getCharProperty;
2468
2469 procedure setCharProperty(gridName in varchar2, propertyType in varchar2, propertyValue in varchar2, p_serverInitDate in date) is
2470 begin
2471 null;
2472 end setCharProperty;
2473
2474 ---
2475 -- return a collection of all columndefinitions for the given grid
2476 ---
2477 function getColumnDefs(gridName in varchar2, p_serverInitDate in date) return colDefTabType is
2478 colDef colDefTabType;
2479 i binary_integer;
2480 begin
2481 validateServer(p_serverInitDate, gridName);
2482 i := findNextColumnDefIndex(gridName,0);
2483 while i is not null loop
2484 colDef(nvl(colDef.LAST,0)+1) := columnDefs(i);
2485 i := findNextColumnDefIndex(gridName,i);
2486 end loop;
2487 return colDef;
2488 end getColumnDefs;
2489
2490 ----------------------------------------------------------------------
2491 /** Fuction getVersion returns the header information for this file */
2492 FUNCTION getVersion RETURN VARCHAR2 IS
2493 BEGIN
2494 RETURN('$Header: JTFGRDDB.pls 120.2 2006/01/19 03:14:44 snellepa ship $');
2495 END;
2496
2497 -----------------------------------------------------------------------
2498 procedure addGridInfo(p_gridIndex IN NUMBER,
2499 l_debug_tbl IN OUT NOCOPY JTF_DEBUG_PUB.debug_tbl_type)
2500 IS
2501 i number;
2502 bind_count number;
2503 l_len_sql number;
2504 len number := 1;
2505 sql_statement jtf_dbstring_utils.maxString%TYPE;
2506 whereClause varchar2(4000);
2507 k binary_integer;
2508 bind_var binary_integer;
2509
2510 BEGIN
2511
2512 i := l_debug_tbl.count;
2513 i := i + 1;
2514 l_debug_tbl(i).debug_message := ' ';
2515 i := i + 1;
2516 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('GRID INDEX',
2517 p_gridIndex);
2518 i := i + 1;
2519 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('GRID NAME',
2520 TableDefs(p_gridIndex).gridName);
2521 i := i + 1;
2522 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('TITLE TEXT',
2523 TableDefs(p_gridIndex).title_text);
2524 i := i + 1;
2525 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('DATASOURCE',
2526 TableDefs(p_gridIndex).grid_datasource_name);
2527 i := i + 1;
2528 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('DB VIEW NAME',
2529 TableDefs(p_gridIndex).db_view_name);
2530
2531 l_len_sql := length(TableDefs(p_gridIndex).SQLStatement);
2532 sql_statement := TableDefs(p_gridIndex).SQLStatement;
2533 i := i + 1;
2534 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('LAST SQL STATEMENT',substr(sql_statement,len, JTF_DEBUG_PUB.FILE_LINESIZE - JTF_DEBUG_PUB.pad_length));
2535 len := len + (JTF_DEBUG_PUB.FILE_LINESIZE - JTF_DEBUG_PUB.pad_length);
2536
2537 while l_len_sql > len loop
2538 i := i + 1;
2539 l_debug_tbl(i).debug_message := substr(sql_statement,len, JTF_DEBUG_PUB.FILE_LINESIZE);
2540 len := len + JTF_DEBUG_PUB.FILE_LINESIZE;
2541 end loop;
2542
2543 i := i + 1;
2544 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('SQL CURSOR',
2545 TableDefs(p_gridIndex).SQLCursor);
2546 i := i + 1;
2547 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('REFRESH FLAG',
2548 TableDefs(p_gridIndex).refreshFlag);
2549 -- where clause also greater than the fileline size
2550 len := 1;
2551 whereClause := TableDefs(p_gridIndex).where_clause;
2552 i := i + 1;
2553 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('CURRENT WHERE CLAUSE', substr(whereClause,len,JTF_DEBUG_PUB.FILE_LINESIZE - JTF_DEBUG_PUB.PAD_LENGTH));
2554 len := len + (JTF_DEBUG_PUB.FILE_LINESIZE - JTF_DEBUG_PUB.pad_length);
2555
2556 while (length(whereClause) > len) loop
2557 i := i + 1;
2558 l_debug_tbl(i).debug_message := substr(whereClause,len, JTF_DEBUG_PUB.FILE_LINESIZE);
2559 len := len + JTF_DEBUG_PUB.FILE_LINESIZE;
2560 end loop;
2561
2562 i := i + 1;
2563 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('WHERE CLAUSE CHANGED', TableDefs(p_gridIndex).hasWhereClauseChanged);
2564 i := i + 1;
2565 bind_count := bindVariablesCount(TableDefs(p_gridIndex).gridName);
2566 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('BIND VARIABLES COUNT', bind_count);
2567
2568 if bind_count > 0 then
2569 bind_var := bindVariables.FIRST;
2570 while bind_var is not NULL LOOP
2571 if bindVariables(bind_var).gridName
2572 = TableDefs(p_gridIndex).gridName then
2573 i := i + 1;
2574 l_debug_tbl(i).debug_message := ' ';
2575 i := i + 1;
2576 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('BIND NAME'), bindVariables(bind_var).variableName);
2577 i := i + 1;
2578 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('BIND TYPE'), bindVariables(bind_var).variableDataType);
2579 i := i + 1;
2580 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('CHAR BIND VALUE'), bindVariables(bind_var).variableCharValue);
2581 i := i + 1;
2582 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatDate(JTF_DEBUG_PUB.FormatIndent('DATE BIND VALUE'), bindVariables(bind_var).variableDateValue);
2583 i := i + 1;
2584 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber(JTF_DEBUG_PUB.FormatIndent('NUMBER BIND VALUE'), bindVariables(bind_var).variableNumberValue);
2585
2586 end if;
2587 bind_var := bindVariables.NEXT(bind_var);
2588 END LOOP;
2589 end if;
2590 i := i + 1;
2591 l_debug_tbl(i).debug_message := 'SORT COLUMNS';
2592 i := i + 1;
2593 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('SORT COLUMN1'),
2594 TableDefs(p_gridIndex).grid_sort_col_alias1);
2595 i := i + 1;
2596 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('SORT COLUMN2'),
2597 TableDefs(p_gridIndex).grid_sort_col_alias2);
2598 i := i + 1;
2599 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('SORT COLUMN3'),
2600 TableDefs(p_gridIndex).grid_sort_col_alias3);
2601 i := i + 1;
2602 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('FETCH SIZE',
2603 getFetchSize);
2604 i := i + 1;
2605 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('MORE ROWS EXIST',
2606 TableDefs(p_gridIndex).moreRowsExists);
2607 i := i + 1;
2608 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('CURRENT ROW COUNT',
2609 TableDefs(p_gridIndex).rowCount);
2610 i := i + 1;
2611 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('COLUMN COUNT',
2612 TableDefs(p_gridIndex).colCount);
2613 i := i + 1;
2614 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('LINES OF TEXT PER ROW',
2615 TableDefs(p_gridIndex).default_row_height);
2616 i := i + 1;
2617 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('CUSTOM GRID ID',
2618 TableDefs(p_gridIndex).custom_grid_id);
2619 i := i + 1;
2620 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('CUSTOM GRID NAME',
2621 TableDefs(p_gridIndex).custom_grid_name);
2622 i := i + 1;
2623 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber('CUSTOM GRID OWNER',
2624 TableDefs(p_gridIndex).owner);
2625 i := i + 1;
2626 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar('PUBLIC FLAG',
2627 TableDefs(p_gridIndex).public_flag);
2628 i := i + 1;
2629 l_debug_tbl(i).debug_message := ' ';
2630 i := i + 1;
2631 l_debug_tbl(i).debug_message := 'COLUMN INFORMATION FOR GRID '||TableDefs(p_gridIndex).gridname;
2632
2633 k := columndefs.FIRST;
2634 while k is not NULL LOOP
2635 if columndefs(k).gridname = TableDefs(p_gridIndex).gridname then
2636 i := i + 1;
2637 l_debug_tbl(i).debug_message := ' ';
2638 i := i + 1;
2639 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('COLUMN INDEX'), k);
2640 i := i + 1;
2641 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('GRID COL ALIAS'), columndefs(k).grid_col_alias);
2642 i := i + 1;
2643 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DB COL ALIAS'), columndefs(k).db_col_name);
2644 i := i + 1;
2645 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DATA TYPE CODE'), columndefs(k).data_type_code);
2646 i := i + 1;
2647 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber(JTF_DEBUG_PUB.FormatIndent('QUERY SEQ'), columndefs(k).query_seq);
2648 i := i + 1;
2649 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('SORTABLE FLAG'), columndefs(k).sortable_flag);
2650 i := i + 1;
2651 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('VISIBLE FLAG'), columndefs(k).visible_flag);
2652 i := i + 1;
2653 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('FREEZE VISIBLE STATE'), columndefs(k).freeze_visible_flag);
2654 i := i + 1;
2655 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber(JTF_DEBUG_PUB.FormatIndent('DISPLAY INDEX'), columndefs(k).display_index);
2656 i := i + 1;
2657 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DISPLAY TYPE'), columndefs(k).display_type_code);
2658 i := i + 1;
2659 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DISPLAY FORMAT TYPE'), columndefs(k).display_format_type_code);
2660 i := i + 1;
2661 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatNumber(JTF_DEBUG_PUB.FormatIndent('DISPLAY WIDTH'), columndefs(k).display_hsize);
2662 i := i + 1;
2663 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('HEADER ALIGNMENT '), columndefs(k).header_alignment_code);
2664 i := i + 1;
2665 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('CELL ALIGNMENT'), columndefs(k).cell_alignment_code);
2666 i := i + 1;
2667 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DISPLAY FORMAT MASK'), columndefs(k).display_format_mask);
2668 i := i + 1;
2669 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('CHECK BOX CHECKED VALUE'), columndefs(k).checkbox_checked_value);
2670 i := i + 1;
2671 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('CHECK BOX UNCHECKED VALUE'), columndefs(k).checkbox_unchecked_value);
2672 i := i + 1;
2673 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('CHECK BOX OTHER VALUE'), columndefs(k).checkbox_other_values);
2674 i := i + 1;
2675 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DB CURRENCY CODE COL'), columndefs(k).db_currency_code_col);
2676 i := i + 1;
2677 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('DB CURRENCY COL ALIAS'), columndefs(k).db_currency_col_alias);
2678 i := i + 1;
2679 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatChar(JTF_DEBUG_PUB.FormatIndent('LABEL TEXT'), columndefs(k).label_text);
2680
2681 end if;
2682 k := columnDefs.NEXT(k);
2683 end loop;
2684
2685 END addGridInfo;
2686
2687
2688 -------------------------------------------------------------------------------
2689 procedure debug(p_debug_tbl IN JTF_DEBUG_PUB.debug_tbl_type
2690 := JTF_DEBUG_PUB.G_MISS_DEBUG_TBL,
2691 p_gridname IN VARCHAR2 := NULL,
2692 x_path OUT NOCOPY varchar2,
2693 x_filename OUT NOCOPY varchar2,
2694 X_Return_Status OUT NOCOPY VARCHAR2,
2695 X_Msg_Count OUT NOCOPY NUMBER,
2696 X_Msg_Data OUT NOCOPY VARCHAR2) IS
2697
2698 l_debug_tbl JTF_DEBUG_PUB.debug_tbl_type;
2699 i number := 1;
2700 j binary_integer := 0;
2701 k binary_integer := 1;
2702 all_info binary_integer := 1;
2703 all_info_IDX number;
2704
2705 BEGIN
2706
2707 /** Adding server information to the local debug table */
2708
2709 if fnd_log.test(FND_LOG.LEVEL_EVENT, 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.GLOBAL.SERVER') then
2710 l_debug_tbl(i).debug_message := 'SERVER INFORMATION';
2711 i := i + 1;
2712 l_debug_tbl(i).debug_message := ' ';
2713 i := i + 1;
2714 l_debug_tbl(i).debug_message := 'SOURCE CODE INFORMATION';
2715 i := i + 1;
2716 l_debug_tbl(i).debug_message := JTF_GRIDDB.getVersion;
2717 i := i + 1;
2718 l_debug_tbl(i).debug_message := JTF_DBSTRING_UTILS.getVersion;
2719 i := i + 1;
2720 l_debug_tbl(i).debug_message := JTF_DBSTREAM_UTILS.getVersion;
2721 i := i + 1;
2722 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.getVersion;
2723 i := i + 1;
2724 l_debug_tbl(i).debug_message := ' ';
2725 i := i + 1;
2726 l_debug_tbl(i).debug_message := 'GRID NAMES AND DATASOURCES';
2727
2728 k := TableDefs.FIRST;
2729 WHILE k is not NULL LOOP
2730 i := i + 1;
2731 l_debug_tbl(i).debug_message
2732 := JTF_DEBUG_PUB.FormatChar(TableDefs(k).gridName,
2733 TableDefs(k).grid_datasource_name);
2734 k := TableDefs.NEXT(k);
2735 END LOOP;
2736
2737 i := i + 1;
2738 l_debug_tbl(i).debug_message := ' ';
2739
2740 For IDX in 1..l_debug_tbl.count LOOP
2741 l_debug_tbl(IDX).module_name
2742 := 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.GLOBAL.SERVER';
2743 END LOOP;
2744
2745 end if; --source
2746
2747 -- current grid information
2748 if fnd_log.test(FND_LOG.LEVEL_EVENT, 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.CURRENT.SERVER') then
2749 all_info_IDX := l_debug_tbl.count + 1;
2750 if p_gridName is not NULL then
2751 i := i + 1;
2752 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatSeperator;
2753 i := i + 1;
2754 l_debug_tbl(i).debug_message := 'CURRENT GRID INFORMATION';
2755
2756 j := findTableDefIndex(p_gridName);
2757 if j is not null then
2758 addGridInfo(j, l_debug_tbl);
2759 end if;
2760 end if;
2761
2762 For IDX in all_info_IDX..l_debug_tbl.count LOOP
2763 l_debug_tbl(IDX).module_name
2764 := 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.CURRENT.SERVER';
2765 END LOOP;
2766
2767 end if; -- current grid
2768
2769 -- all grid information
2770
2771 if fnd_log.test(FND_LOG.LEVEL_EVENT, 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.ALL.SERVER') then
2772 all_info_IDX := l_debug_tbl.count + 1;
2773 /* i := i + 1;
2774 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatSeperator;
2775 i := i + 1;
2776 l_debug_tbl(i).debug_message := 'INFORMATION OF ALL OTHER GRIDS';*/
2777 i := i + 1;
2778 l_debug_tbl(i).debug_message := ' ';
2779
2780 all_info := TableDefs.FIRST;
2781 WHILE all_info is not NULL LOOP
2782 IF all_info <> j THEN -- current info is already passed
2783 addGridInfo(all_info, l_debug_tbl);
2784 END IF;
2785 all_info := TableDefs.NEXT(all_info);
2786 END LOOP;
2787 i := i + 1;
2788 l_debug_tbl(i).debug_message := JTF_DEBUG_PUB.FormatSeperator;
2789
2790 For IDX in all_info_IDX..l_debug_tbl.count LOOP
2791 l_debug_tbl(IDX).module_name
2792 := 'JTF.GRID.PLSQL.JTFGRDDB.DEBUG.ALL.SERVER';
2793 END LOOP;
2794 end if; -- all grid
2795
2796 -- add midtier/client info
2797
2798 all_info_IDX := l_debug_tbl.count + 1;
2799 l_debug_tbl(all_info_IDX).debug_message := ' ';
2800 all_info_IDX := all_info_IDX + 1;
2801 FOR i in 1..p_debug_tbl.count LOOP
2802 l_debug_tbl(all_info_IDX) := p_debug_tbl(i);
2803 all_info_IDX := all_info_IDX + 1;
2804 END LOOP;
2805
2806 -- the module name was split up to avoid GSCC error
2807 JTF_DEBUG_PUB.Debug( p_debug_tbl => l_debug_tbl,
2808 p_module => 'J'||'TF.GRID%',
2809 x_path => x_path,
2810 x_filename => x_filename,
2811 x_msg_count => x_msg_count,
2812 X_MSG_DATA => x_msg_data,
2813 X_RETURN_STATUS => x_return_status
2814 );
2815
2816 END debug;
2817 -----------------------------------------------------------------------------
2818 function getGridFetchSize(gridName in varchar2, p_serverInitDate in date) return number is
2819 begin
2820 validateServer(p_serverInitDate, gridName);
2821 return tableDefs(findTableDefIndex(gridName)).fetchSize;
2822 end getGridFetchSize;
2823
2824 -----------------------------------------------------------------------------
2825 procedure setGridFetchSize(gridName in varchar2, rows in number, p_serverInitDate in date) is
2826 begin
2827 validateServer(p_serverInitDate, gridName);
2828 tableDefs(findTableDefIndex(gridName)).fetchSize := rows;
2829 end setGridFetchSize;
2830
2831 -----------------------------------------------------------------------------
2832 procedure getSortCol(gridName in varchar2
2833 ,p_serverInitDate in date
2834 ,sort_col1 out nocopy varchar2
2835 ,sort_asc_flag1 out nocopy varchar2
2836 ,sort_col2 out nocopy varchar2
2837 ,sort_asc_flag2 out nocopy varchar2
2838 ,sort_col3 out nocopy varchar2
2839 ,sort_asc_flag3 out nocopy varchar2
2840 ) is
2841 i pls_integer;
2842 j pls_integer;
2843 begin
2844 validateServer(p_serverInitDate, gridName);
2845 i := findTableDefIndex(gridName);
2846 if i is null then
2847 raise INVALID_GRID;
2848 end if;
2849 sort_col1 := tableDefs(i).grid_sort_col_alias1;
2850 sort_col2 := tableDefs(i).grid_sort_col_alias2 ;
2851 sort_col3 := tableDefs(i).grid_sort_col_alias3 ;
2852
2853 if sort_col1 is not null then
2854 j := findColumnDefIndex(gridName,sort_col1);
2855 if j is not null then
2856 sort_asc_flag1 := columnDefs(j).sort_asc_by_default_flag ;
2857 end if;
2858 end if;
2859 if sort_col2 is not null then
2860 j := findColumnDefIndex(gridName,sort_col2);
2861 if j is not null then
2862 sort_asc_flag2 := columnDefs(j).sort_asc_by_default_flag ;
2863 end if;
2864 end if;
2865 if sort_col3 is not null then
2866 j := findColumnDefIndex(gridName,sort_col3);
2867 if j is not null then
2868 sort_asc_flag3 := columnDefs(j).sort_asc_by_default_flag ;
2869 end if;
2870 end if;
2871
2872 exception
2873 when INVALID_GRID then
2874 handleErrors(INVALID_GRID_ERROR,'getSortCol',gridName,null,null);
2875 end getSortCol;
2876
2877
2878 procedure getTableDefInfo(gridName in varchar2
2879 ,p_serverInitDate in date
2880 ,x_sort_col1 out nocopy varchar2
2881 ,x_sort_col2 out nocopy varchar2
2882 ,x_sort_col3 out nocopy varchar2
2883 ,x_fetchSize out nocopy number
2884 ) IS
2885 i pls_integer;
2886 BEGIN
2887 validateServer(p_serverInitDate, gridName);
2888 i := findTableDefIndex(gridName);
2889 if i is null then
2890 raise INVALID_GRID;
2891 end if;
2892 x_sort_col1 := tableDefs(i).grid_sort_col_alias1;
2893 x_sort_col2 := tableDefs(i).grid_sort_col_alias2 ;
2894 x_sort_col3 := tableDefs(i).grid_sort_col_alias3 ;
2895
2896 -- sort_Col1 can NULL only if sort_col2 and sort_col3 are null so we don't have
2897 -- to worry about sort_col1
2898
2899 if x_sort_col2 is NULL and x_sort_col3 is not NULL then
2900 x_sort_col2 := x_sort_col3;
2901 x_sort_col3 := NULL;
2902 end if;
2903
2904 x_fetchSize := tableDefs(i).fetchSize;
2905
2906 exception
2907 when INVALID_GRID then
2908 handleErrors(INVALID_GRID_ERROR,'getTableDefIndex',gridName,null,null);
2909 END getTableDefInfo;
2910
2911 /** this function is invoked only from populate and refresh methods.
2912 this function catches any "invalid package" exception and tries to recompile
2913 them before throwing an error.
2914 */
2915
2916 function fetchFirstSet(gridName in varchar2
2917 ,p_serverInitDate in date) return dataSet%TYPE is
2918 j pls_integer;
2919 begin
2920 return fetchDataSet(gridName
2921 ,p_serverInitDate);
2922 exception
2923 when OTHERS then
2924 if (SQLCODE = -4068) then
2925 --if (SQLCODE = -4068 or SQLCODE = -4061 or SQLCODE = -4065 or SQLCODE = -6508 or SQLCODE = -1003) then
2926 j := findTableDefIndex(gridName);
2927 tableDefs(j).refreshFlag := 'T';
2928 tableDefs(j).hasWhereClauseChanged := 'T';
2929 dataSet := fetchDataSet(gridName, p_serverInitDate);
2930 return dataSet;
2931 else
2932 raise;
2933 end if;
2934 end;
2935
2936 END jtf_gridDB;
[Home] [Help]