1 package WF_LOV AUTHID CURRENT_USER as
2 /* $Header: wflovs.pls 115.27 2002/11/11 06:15:40 rosthoma ship $ */
3
4 /*===========================================================================
5
6 PL*SQL TABLE NAME: wf_lov_define_rec
7
8 DESCRIPTION: Stores list of values definition information
9
10
11 total_rows is the number of rows that match the search criteria.
12 You need to execute a count before the real query every time you
13 get rows since we are in a stateless environment.
14
15 add_attr_title1..5 columns are for extra attributes that you wish
16 to display in the lov
17
18 ============================================================================*/
19
20 TYPE wf_lov_define_rec IS RECORD
21 (
22 total_rows NUMBER,
23 add_attr1_title VARCHAR2(80),
24 add_attr2_title VARCHAR2(80),
25 add_attr3_title VARCHAR2(80),
26 add_attr4_title VARCHAR2(80),
27 add_attr5_title VARCHAR2(80)
28 );
29
30 /*===========================================================================
31
32 PL*SQL TABLE NAME: wf_lov_value_rec
33
34 DESCRIPTION: Stores list of values information
35
36 Hidden Key value is what gets returned to the hidden value on the
37 notification form.
38
39 Display value is what gets returned to the displayed field on the
40 notification form. If there is no need for a hidden primary key value
41 then make both these values the same.
42
43 add_attr_value1..5 columns are for extra attributes that you wish
44 to display in the lov
45 ============================================================================*/
46
47 TYPE wf_lov_value_rec IS RECORD
48 (
49 hidden_key VARCHAR2(320),
50 display_value VARCHAR2(4000),
51 add_attr1_value VARCHAR2(320),
52 add_attr2_value VARCHAR2(240),
53 add_attr3_value VARCHAR2(240),
54 add_attr4_value VARCHAR2(240),
55 add_attr5_value VARCHAR2(240)
56 );
57
58 TYPE wf_lov_values_tbl IS TABLE OF
59 wf_lov.wf_lov_value_rec
60 INDEX BY BINARY_INTEGER;
61
62
63 /*===========================================================================
64
65 GLOBAL PL*SQL RECORD NAME: g_define_rec
66
67 DESCRIPTION: Stores list of values definition information
68
69 You cannot pass plsql tables or records the execute immediate so you
70 need all procedures that generate LOV data to use this global to set
71 up the total number of rows in the list and the column headers.
72 ============================================================================*/
73 g_define_rec wf_lov.wf_lov_define_rec;
74
75 /*===========================================================================
76
77 GLOBAL PL*SQL RECORD NAME: g_value_tbl
78
79 DESCRIPTION: Stores list of values row information
80
81 You cannot pass plsql tables or records the execute immediate so you
82 need all procedures that generate LOV data to use this global to set
83 up the rows you can choose from in the LOV.
84 ============================================================================*/
85 g_value_tbl wf_lov.wf_lov_values_tbl;
86
87 /*===========================================================================
88 PROCEDURE NAME: display_lov
89
90 DESCRIPTION: Display the two frame lov list
91
92 PARAMETERS:
93
94 ============================================================================*/
95 procedure display_lov
96 (
97 p_lov_name in varchar2 default null,
98 p_display_name in varchar2 default null,
99 p_validation_callback in varchar2 default null,
100 p_dest_hidden_field in varchar2 default null,
101 p_dest_display_field in varchar2 default null,
102 p_current_value in varchar2 default null,
103 p_param1 in varchar2 default null,
104 p_param2 in varchar2 default null,
105 p_param3 in varchar2 default null,
106 p_param4 in varchar2 default null,
107 p_param5 in varchar2 default null,
108 p_display_key in varchar2 default 'N');
109
110 /*===========================================================================
111 PROCEDURE NAME: display_lov_find
112
113 DESCRIPTION: Display the find criteria for the lov
114
115 PARAMETERS:
116
117 ============================================================================*/
118 procedure display_lov_find (
119 p_lov_name in varchar2 default null,
120 p_display_name in varchar2 default null,
121 p_validation_callback in varchar2 default null,
122 p_dest_hidden_field in varchar2 default null,
123 p_dest_display_field in varchar2 default null,
124 p_current_value in varchar2 default null,
125 p_autoquery in varchar2 default 'Y',
126 p_display_key in varchar2 default 'N');
127
128 /*===========================================================================
129 PROCEDURE NAME: display_lov_details
130
131 DESCRIPTION: Calls the validation callback to get the content
132 and display an HTML LOV.
133
134 PARAMETERS:
135
136 ============================================================================*/
137 procedure display_lov_details (
138 p_lov_name in varchar2 default null,
139 p_display_name in varchar2 default null,
140 p_validation_callback in varchar2 default null,
141 p_dest_hidden_field in varchar2 default null,
142 p_dest_display_field in varchar2 default null,
143 p_current_value in varchar2 default null,
144 p_start_row in varchar2 default '1',
145 p_autoquery in varchar2 default 'Y',
146 p_param1 in varchar2 default null,
147 p_param2 in varchar2 default null,
148 p_param3 in varchar2 default null,
149 p_param4 in varchar2 default null,
150 p_param5 in varchar2 default null,
151 p_display_key in varchar2 default 'N');
152
153 /*===========================================================================
154 PROCEDURE NAME: OpenLovWinHtml
155
156 DESCRIPTION: Generates javascript required to run the HTML LOV.
157 Insert the javascript statements in the header of
158 the Document that will call the LOV window.
159
160 PARAMETERS:
161
162 p_jscript_tag - Tells the procedure whether you want to include the
163 <SCRIPT> tag in the header or not. ICX cannot have
164 extra <SCRIPT> and </SCRIPT> tags in their header
165 so this must be eliminated. Values are Y or N.
166
167 ============================================================================*/
168 procedure OpenLovWinHtml(p_jscript_tag IN Varchar2 DEFAULT 'Y');
169
170 /*===========================================================================
171 FUNCTION NAME: GenerateLovURL
172
173 DESCRIPTION: Generates the URL syntax required to launch
174 the lov window for the given field.
175
176 PARAMETERS:
177
178 form_name IN - The name of the HTML form where you would like
179 to return the selected value from the LOV.
180
181 Example:
182
183 opener.document.WF_PREF
184
185 where WF_PREF is the form name given in the
186 <FORM...> tag
187
188 NOTE: The Form name is CASE sensitive.
189 Also if your are calling the LOV from a HTML window
190 using frames then the syntax for the form name
191 should be:
192
193 opener.parent.opener.bottomframe.document.WF_PREF
194
195 where bottomframe is the name of the frame given
196 in the <FRAME...> tag and WF_PREF is the form
197 name given in the <FORM...> tag
198
199
200 query_plsql IN - PLSQL procedure to call to generate LOV column headers,
201 column data, display vs. non display columns, and
202 column order. The output format for this function
203 is very specific and must make use of the htp.p
204 function to provide this information.
205
206 The signature for the LOV data definition procedure
207 must include a p_titles_only and a p_find_criteria
208 parameter. The p_titles_only parameter is used for
209 longlist LOV's and must be implemented in the
210 procedure as a mechanism for fetching the definition
211 information for the LOV. The p_find_criteria is
212 populated by the LOV window from the Find field and
213 allows you to restrict the data that is shown in the
214 LOV window.
215
216 Example:
217
218 procedure FetchLOVData (
219 p_titles_only IN VARCHAR2 DEFAULT NULL,
220 p_find_criteria IN VARCHAR2 DEFAULT NULL);
221
222
223 The above function is called when the LOV is
224 initially envoked or when the user clicks on the
225 Find button. The data that is defined in this
226 function should be in the following format:
227
228 HTP.P ('Lov Title');
229 HTP.P ('Number of Columns in LOV');
230 HTP.P ('Number of Rows'); -- See NOTE A/B Below
231
232 The title, # columns, and # rows information should
233 then be followed by a list of column names and
234 % space that column should consume in the LOV.
235 The column name value should be a translated string
236 since this will be displayed as the column header.
237 The % should be defined as a whole
238 number value. The total percentages for all
239 columns in your LOV should add up 100%.
240 If you have a column that you want
241 to include as a non display column then the %
242 space should be 0. This is important for
243 unique id'select that represent the data being
244 displayed.
245
246 The first column in the list is the key column.
247 Although you control the search and the sort criteria
248 it is assumed that the first column in the list will
249 be the column that the list is sorted and queried on.
250 The user can reorder the columns in the list but this
251 is strictly for viewing convenience. The auto
252 reduction mechanism within the LOV will only ever
253 operate on the first column.
254
255 HTP.P ('Column1 Name');
256 HTP.P ('% of space the column1 should consume');
257 HTP.P ('Column2 Name');
258 HTP.P ('% of space the column2 should consume');
259 HTP.P ('Column...');
260 HTP.P ('% of space the column... should consume');
261
262 The column title and space information should
263 then be followed by the actual LOV data.
264 You should open a cursor for the results of
265 your query and return the results in the cursor
266 loop.
267
268 Example:
269
270 if (p_titles_only = 'N') then
271
272 open c_user_lov (p_find_criteria); -- See NOTE B
273
274 loop
275
276 fetch c_user_lov into
277 l_name, l_display_name;
278
279 exit when c_user_lov%notfound;
280
281 HTP.P (l_name);
282 HTP.P (l_display_name);
283
284 end loop;
285
286 end if;
287
288
289 NOTE A:
290
291 To fill in the Number of Rows value you need to
292 implement a select in your FetchLOVData function
293 to get the count of the
294 number of rows that will be returned by the query.
295
296 Example:
297
298 select count(1)
299 into x_count
300 from your_tables
301 where UPPER(your_first_table_column) LIKE
302 UPPER(p_find_criteria)||'%'
303
304 NOTE B:
305
306 If you are defining a LONGLIST LOV then your
307 count and fetch query should be consistent with how
308 FORMS constructs LOV queries to ensure optimal
309 performance
310
311 select count(1)
312 into x_count
313 from your_tables
314 where UPPER(your_first_table_column) LIKE
315 UPPER(p_find_criteria)||'%'
316 and (your_first_table_column LIKE
317 LOWER(SUBSTR(p_find_criteria, 1, 2))||'%'
318 or your_first_table_column LIKE
319 LOWER(SUBSTR(p_find_criteria, 1, 1))||
320 UPPER(SUBSTR(p_find_criteria, 2, 1))||'%'
321 or your_first_table_column LIKE
322 INITCAP(SUBSTR(p_find_criteria, 1, 2))||'%'
323 or your_first_table_column LIKE
324 UPPER(SUBSTR(p_find_criteria, 1, 2))||'%')
325
326
327 -- The resulting select would parse out to look
328 -- something like the following:
329
330 select count(1)
331 into x_count
332 from fnd_form_vl
333 where upper(form_name) like 'FND%'
334 and (form_name like 'fn%'
335 or form_name like 'fN%'
336 or form_name like 'Fn%'
337 or form_name like 'FN%')
338
339
340 query_params IN - Any extra parameters that you would like to hardcode
341 and pass to this specific implementation of the LOV
342 defined in the query_plsql routine. This gives you
343 an opportunity to have one query_plsql routine that
344 may vary slightly based on the implementation of the
345 LOV on a specific form
346
347 column_names IN - Comma separated list of field names on your form
348 where you would like to return the values. The column
349 names are based the name you give your entry fields
350 using the <INPUT..> tag. Do not include any spaces
351 in your list of columns. If you don't care about a
352 column that is presented in the LOV then use a column
353 name of NULL.
354
355 Example:
356
357 If your query_plsql function creates a three column
361 to discard the language short name then the syntax
358 LOV that includes a list of languages,
359 language short names and territories but you only
360 care about the language and the territory and want
362 might be:
363
364 p_language,NULL,p_territory
365
366
367 longlist IN - Defines whether this a longlist LOV or not.
368
369 Values: Y/N
370
371 If you define a list to be a longlist then the
372 LOV window is initially display with just the column
373 headers and no data. The user must then enter some
374 text in the Find Criteria and click on Find to
375 display any data to choose from. If the LOV is not
376 defined as a longlist then the columns and data are
377 immediately displayed when the window is envoked.
378
379 callback IN - Name of javascript function to envoke when the user
380 clicks on OK from LOV window. If you would like to
381 envoke a javascript function to perform other special
382 functionality within your HTML form you can pass a
383 javascript function to execute when the OK button is
384 pressed. This javascript function should be defined
385 in the header of the window where the LOV will be
386 envoked. That javascript function should be defined
387 to take the same number of parameters as you have
388 defined columns in your query_plsql routine. We
389 will take the javascript function and append the
390 selected values from the LOV data selected as a
391 comma delimited list and then execute the javascript
392 function.
393
394 callback_params IN - Column values that you would like to pass to
395 javascript callback function. This parameter is
396 passed in the format of col#,col# where col# is
397 the column number in the list that you define that
398 you would like to pass as a parmater. For example
399 If you would like to pass column 2 as the first
400 parameter, and column 3 as the second parameter,
401 and not pass column 1 to the javascript function
402 then the callback_params value should be 1,2. Note
403 that the column numbering starts with 0...
404
405
406 width IN - The width in pixels of the LOV Window
407
408 height IN - The height in pixels of the LOV Window
409 prompt IN - String to be displayed when MouseOver
410
411
412 ============================================================================*/
413 function GenerateLovURL (p_form_name IN Varchar2,
414 p_query_plsql IN Varchar2,
415 p_query_params IN Varchar2,
416 p_column_names IN Varchar2,
417 p_longlist IN Varchar2,
418 p_callback IN Varchar2 DEFAULT NULL,
419 p_callback_params IN Varchar2 DEFAULT NULL,
420 p_init_find_field IN Varchar2 DEFAULT NULL,
421 p_width IN Varchar2,
422 p_height IN Varchar2,
423 p_prompt IN Varchar2 DEFAULT NULL,
424 p_window_title IN Varchar2 DEFAULT NULL)
425 return VARCHAR2;
426
427 /*===========================================================================
428 PRIVATE PROCEDURES
429
430 DESCRIPTION: Do not call these procedures directly since their
431 prototype may change.
432
433 ============================================================================*/
434 procedure Error;
435
436 procedure LovApplet(doc_name varchar2,
437 column_names varchar2,
438 query_params varchar2,
439 query_plsql varchar2,
440 callback varchar2 default null,
441 callback_params varchar2 default null,
442 longlist varchar2,
443 initial_find varchar2 default null,
444 width varchar2,
445 height varchar2,
446 window_title varchar2 default null);
447
448 /*
449 ** This procedure is a combination of display_lov, display_lov_find and
450 ** display_lov_details for new UI design with no frame
451 */
452 procedure display_lov_no_frame
453 (
454 p_lov_name in varchar2 default null,
455 p_display_name in varchar2 default null,
456 p_validation_callback in varchar2 default null,
457 p_dest_hidden_field in varchar2 default null,
458 p_dest_display_field in varchar2 default null,
459 p_current_value in varchar2 default null,
460 p_start_row in varchar2 default null,
461 p_autoquery in varchar2 default 'Y',
462 p_language in varchar2 default 'US'
463 );
464
465 /*
466 ** Bug 1380107. Pass in the non-translated key p_display_name_key
467 ** instead of the translated multibyte value in p_display_name.
468 ** This routine will use the key to find the translated value and call
469 ** the procedure display_lov_no_frame.
470 */
471 procedure display_lov_no_frame_key
472 (
473 p_lov_name in varchar2 default null,
474 p_display_name_key in varchar2 default null,
475 p_validation_callback in varchar2 default null,
476 p_dest_hidden_field in varchar2 default null,
477 p_dest_display_field in varchar2 default null,
478 p_current_value in varchar2 default null,
479 p_start_row in varchar2 default null,
480 p_autoquery in varchar2 default 'Y',
481 p_language in varchar2 default 'US'
482 );
483
484 /* Bug 1904844
485
486 procedure display_lov_key
487 (
488 p_lov_name in varchar2 default null,
489 p_display_name_key in varchar2 default null,
490 p_validation_callback in varchar2 default null,
491 p_dest_hidden_field in varchar2 default null,
492 p_dest_display_field in varchar2 default null,
493 p_current_value in varchar2 default null,
494 p_param1 in varchar2 default null,
495 p_param2 in varchar2 default null,
496 p_param3 in varchar2 default null,
497 p_param4 in varchar2 default null,
498 p_param5 in varchar2 default null
499 ); */
500
501 end WF_LOV;
[Home] [Help]