1 PACKAGE BODY wf_item_attributes_vl_pub AS
2 /* $Header: wfdefb.pls 120.1 2005/07/02 03:43:48 appldev ship $ */
3
4 /*===========================================================================
5 PACKAGE NAME: wf_item_attributes_vl_pub
6
7 DESCRIPTION:
8
9 OWNER: GKELLNER
10
11 TABLES/RECORDS:
12
13 PROCEDURES/FUNCTIONS:
14
15 ============================================================================*/
16
17 /*===========================================================================
18 PROCEDURE NAME: fetch_item_attributes
19
20 DESCRIPTION: Fetches all the attributes for a given item type
21 into a p_wf_item_attributes_vl_tbl table based on the
22 item type internal eight character name. This function
23 can also retrieve a single item attribute definition if
24 the internal name along with the item type name is
25 provided. This is especially useful if you wish to
26 display the details for a single attribute when it
27 is referenced from some drilldown mechanism.
28
29 ============================================================================*/
30 PROCEDURE fetch_item_attributes
31 (p_item_type IN VARCHAR2,
32 p_name IN VARCHAR2,
33 p_wf_item_attributes_vl_tbl
34 OUT NOCOPY wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type) IS
35
36 /*===========================================================================
37
38 CURSOR NAME: c_fetch_item_attributes
39
40 DESCRIPTION: Fetches all attributes for the given item_type
41
42 You'll notice that we are selecting the attribute
43 display name twice. The second occurrence is simply a
44 placeholder in the record so that I can fill in that column
45 with the lookup type display name if this attribute is
46 validated based on a lookup type.
47
48 PARAMETERS:
49
50 c_item_type IN Internal name of the item type
51
52 ============================================================================*/
53 CURSOR c_fetch_item_attributes (c_item_type IN VARCHAR2) IS
54 SELECT
55 row_id,
56 item_type,
57 name,
58 sequence,
59 type,
60 protect_level,
61 custom_level,
62 subtype,
63 format,
64 display_name lookup_type_display_name,
65 display_name lookup_code_display_name,
66 text_default,
67 number_default,
68 date_default,
69 display_name,
70 description
71 FROM wf_item_attributes_vl
72 WHERE item_type = c_item_type
73 ORDER BY sequence;
74
75 l_record_num NUMBER := 0;
76
77 BEGIN
78
79 /*
80 ** Make sure all the required parameters are set
81 */
82 IF (p_item_type IS NULL) THEN
83
84 return;
85
86 END IF;
87
88 /*
89 ** Check if the caller has passed a specific attribute_name to search for.
90 ** If so then just get the row corresponding to that item_type and
91 ** attribute_name. If not then get all rows for that item_type.
92 */
93 IF (p_name IS NOT NULL) THEN
94
95 BEGIN
96 SELECT row_id,
97 item_type,
98 name,
99 sequence,
100 type,
101 protect_level,
102 custom_level,
103 subtype,
104 format,
105 display_name lookup_type_display_name,
106 display_name lookup_code_display_name,
107 text_default,
108 number_default,
109 date_default,
110 display_name,
111 description
112 INTO p_wf_item_attributes_vl_tbl(1)
113 FROM wf_item_attributes_vl
114 WHERE item_type = p_item_type
115 AND name = p_name;
116
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 NULL;
120 WHEN OTHERS THEN
121 RAISE;
122 END;
123
124 ELSE
125
126 OPEN c_fetch_item_attributes (p_item_type);
127
128 /*
129 ** Loop through all the lookup_code rows for the given lookup_type
130 ** filling in the p_wf_lookups_tbl
131 */
132 LOOP
133
134 l_record_num := l_record_num + 1;
135
136 FETCH c_fetch_item_attributes INTO
137 p_wf_item_attributes_vl_tbl(l_record_num);
138
139 EXIT WHEN c_fetch_item_attributes%NOTFOUND;
140
141 /*
142 ** If the validation for this attribute is a lookup then go get the
143 ** display name for that lookup and put it in the
144 ** lookup_type_display_name record element
145 */
146 IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN
147
148 wf_lookup_types_pub.fetch_lookup_display(
149 p_wf_item_attributes_vl_tbl(l_record_num).format,
150 p_wf_item_attributes_vl_tbl(l_record_num).text_default,
151 p_wf_item_attributes_vl_tbl(l_record_num).lookup_type_display_name,
152 p_wf_item_attributes_vl_tbl(l_record_num).lookup_code_display_name);
153 END IF;
154
155 END LOOP;
156
157 CLOSE c_fetch_item_attributes;
158
159 END IF;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 Wf_Core.Context('wf_item_attributes_vl_pub',
164 'fetch_item_attributes',
165 p_item_type,
166 p_name);
167
168 wf_item_definition.Error;
169
170 END fetch_item_attributes;
171
172 /*===========================================================================
173 PROCEDURE NAME: fetch_item_attribute_display
174
175 DESCRIPTION: fetch the item attribute display name based on a item
176 type name and an internal item attribute name
177
178 ============================================================================*/
179 PROCEDURE fetch_item_attribute_display (p_item_type IN VARCHAR2,
180 p_internal_name IN VARCHAR2,
181 p_display_name OUT NOCOPY VARCHAR2) IS
182
183 l_display_name VARCHAR2(80);
184 l_wf_item_attributes_vl_tbl
185 wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type;
186
187 BEGIN
188
189 /*
190 ** Fetch the item attribute record associated with this internal name
191 */
192 fetch_item_attributes (p_item_type,
193 p_internal_name,
194 l_wf_item_attributes_vl_tbl);
195
196 /*
197 ** See if you found a row. If not, proide the user with feedback
198 */
199 IF (l_wf_item_attributes_vl_tbl.count < 1) THEN
200
201 l_display_name := p_internal_name||' '||
202 '<B> -- '||wf_core.translate ('WFITD_UNDEFINED')||'</B>';
203
204 ELSE
205
206 l_display_name := l_wf_item_attributes_vl_tbl(1).display_name;
207
208 END IF;
209
210 p_display_name := l_display_name;
211
212 EXCEPTION
213 WHEN OTHERS THEN
214 Wf_Core.Context('wf_item_attributes_pub',
215 'fetch_item_attribute_display',
216 p_internal_name);
217
218 END fetch_item_attribute_display;
219
220 /*===========================================================================
221 PROCEDURE NAME: draw_item_attribute_list
222
223 DESCRIPTION: Shows the display name of an item attribute as a
224 html view as a part of a hierical summary list of
225 an item type. This function uses the htp to
226 generate its html output.
227
228 ============================================================================*/
229 PROCEDURE draw_item_attribute_list
230 (p_wf_item_attributes_vl_tbl
231 IN wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type,
232 p_effective_date IN DATE,
233 p_indent_level IN NUMBER) IS
234
235 l_record_num NUMBER;
236 ii NUMBER := 0;
237
238 BEGIN
239
240 /*
241 ** Create the the attributes title. Indent it to the level specified
242 */
243 wf_item_definition_util_pub.draw_summary_section_title(
244 wf_core.translate('WFITD_ATTRIBUTES'),
245 p_indent_level);
246
247 /*
248 ** Print out all item attribute display names in the pl*sql table
249 */
250 FOR l_record_num IN 1..p_wf_item_attributes_vl_tbl.count LOOP
251
252 /*
253 ** The creation of the anchor from the summary frame to the detail
254 ** frame was very complex so I've extracted the function into its
255 ** own routine.
256 */
257 wf_item_definition_util_pub.create_hotlink_to_details (
258 p_wf_item_attributes_vl_tbl(l_record_num).item_type,
259 p_effective_date,
260 'ATTRIBUTE',
261 p_wf_item_attributes_vl_tbl(l_record_num).name,
262 p_wf_item_attributes_vl_tbl(l_record_num).display_name,
263 NULL,
264 p_indent_level+1);
265
266 END LOOP;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 Wf_Core.Context('wf_item_attributes_vl_pub', 'draw_item_attribute_list');
271 wf_item_definition.Error;
272
273 END draw_item_attribute_list;
274
275
276 /*===========================================================================
277 PROCEDURE NAME: draw_item_attribute_details
278
279 DESCRIPTION: Shows all the details of an item attrribute as a
280 html view. This function uses the htp to
281 generate its html output.
282 MODIFICATION LOG:
283 06-JUN-2001 JWSMITH BUG 1819232 - added summary attr for table tag for ADA
284
285 ============================================================================*/
286 PROCEDURE draw_item_attribute_details
287 (p_wf_item_attributes_vl_tbl IN wf_item_attributes_vl_pub.wf_item_attributes_vl_tbl_type,
288 p_indent_level IN NUMBER) IS
289
290 l_record_num NUMBER;
291 ii NUMBER := 0;
292
293 BEGIN
294
295 /*
296 ** Draw the section title for the item type detail section
297 */
298 wf_item_definition_util_pub.draw_detail_section_title (
299 wf_core.translate('WFITD_ATTRIBUTE_DETAILS'),
300 0);
301
302 /*
303 ** Print out all item attribute display names in the pl*sql table
304 */
305 FOR l_record_num IN 1..p_wf_item_attributes_vl_tbl.count LOOP
306
307 /*
308 ** Open a new table for each attribute so you can control the spacing
309 ** between each attribute
310 */
311 htp.tableOpen(cattributes=>'border=0 cellpadding=2 cellspacing=0
312 summary= "' || wf_core.translate('WFITD_ATTRIBUTE_DETAILS') || '"');
313
314 /*
315 ** Create the target for the hotlink from the summary view. Also
316 ** create the first row in the table which is always the display
317 ** name for the object.
318 */
319 wf_item_definition_util_pub.create_details_hotlink_target (
320 'ATTRIBUTE',
321 p_wf_item_attributes_vl_tbl(l_record_num).name,
322 p_wf_item_attributes_vl_tbl(l_record_num).display_name,
323 wf_core.translate('WFITD_ATTRIBUTE_NAME'),
324 0);
325
326 /*
327 ** Create the internal name row in the table.
328 */
329 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
330 wf_core.translate('WFITD_INTERNAL_NAME'),
331 p_wf_item_attributes_vl_tbl(l_record_num).name);
332
333 /*
334 ** Create the description row in the table
335 */
336 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
337 wf_core.translate('DESCRIPTION'),
338 p_wf_item_attributes_vl_tbl(l_record_num).description);
339
340 /*
341 ** Create the attribute type row in the table. I've named the
342 ** translated resource so that all I have to do is add
343 ** WFITD_ATTR_TYPE_ to the type of resource and I get the
344 ** translated string.
345 */
346 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
347 wf_core.translate('WFITD_ATTRIBUTE_TYPE'),
348 wf_core.translate('WFITD_ATTR_TYPE_'||
349 p_wf_item_attributes_vl_tbl(l_record_num).type));
350
351 /*
352 ** Create the length/format/lookup type row in the table.
353 ** If the type is VARCHAR2 then show a length prompt
354 ** If the type is NUMBER/DATE then show format prompt
355 ** If the type is LOOKUP then show lookup type prompt
356 ** If it is any other type then don't show the row at all
357 */
358 IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'VARCHAR2') THEN
359
360 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
361 wf_core.translate('LENGTH'),
362 p_wf_item_attributes_vl_tbl(l_record_num).format);
363
364 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type IN ('NUMBER', 'DATE')) THEN
365
366 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
367 wf_core.translate('FORMAT'),
368 p_wf_item_attributes_vl_tbl(l_record_num).format);
369
370 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type IN ('URL','DOCUMENT')) THEN
371 /*
372 ** If it is URL or DOCUMENT, indicate where the resulting page should be displayed
373 */
374 IF (NVL(p_wf_item_attributes_vl_tbl(l_record_num).format, '_top') = '_top') THEN
375 wf_item_definition_util_pub.draw_detail_prompt_value_pair
376 (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_TOP'));
377 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_blank') THEN
378 wf_item_definition_util_pub.draw_detail_prompt_value_pair
379 (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_BLANK'));
380 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_self') THEN
381 wf_item_definition_util_pub.draw_detail_prompt_value_pair
382 (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_SELF'));
383 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).format = '_parent') THEN
384 wf_item_definition_util_pub.draw_detail_prompt_value_pair
385 (wf_core.translate('WFITD_FRAME_TARGET'), wf_core.translate('WFITD_PARENT'));
386 END IF;
387
388 ELSIF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN
389
390 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
391 wf_core.translate('LOOKUP'),
392 p_wf_item_attributes_vl_tbl(l_record_num).lookup_type_display_name);
393
394 END IF;
395
396 /*
397 ** Create the default value row in the table. If the attribute type is based on
398 ** a lookup then the default value must be one of the lookup codes. If so print
399 ** the lookup code that was fetch, If this is any other attribute type then
400 ** nvl on text value. If there is no text value then try the number
401 ** default. If there is no number default then try the date.
402 */
403 IF (p_wf_item_attributes_vl_tbl(l_record_num).type = 'LOOKUP') THEN
404
405 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
406 wf_core.translate('WFITD_DEFAULT_VALUE'),
407 p_wf_item_attributes_vl_tbl(l_record_num).lookup_code_display_name);
408
409 ELSE
410
411 wf_item_definition_util_pub.draw_detail_prompt_value_pair (
412 wf_core.translate('WFITD_DEFAULT_VALUE'),
413 NVL(p_wf_item_attributes_vl_tbl(l_record_num).text_default,
414 NVL(TO_CHAR(p_wf_item_attributes_vl_tbl(l_record_num).number_default),
415 TO_CHAR(p_wf_item_attributes_vl_tbl(l_record_num).date_default))));
416
417 END IF;
418 htp.tableRowClose;
419
420 /*
421 ** Call function to print the customization/protection levels
422 */
423 wf_item_definition_util_pub.draw_custom_protect_details(
424 p_wf_item_attributes_vl_tbl(l_record_num).custom_level,
425 p_wf_item_attributes_vl_tbl(l_record_num).protect_level);
426
427 /*
428 ** Table is created so close it out
429 */
430 htp.tableClose;
431
432 /*
433 ** Draw a line between each attribute definition
434 ** if this is not the last item in the list
435 */
436 IF (l_record_num <> p_wf_item_attributes_vl_tbl.count) THEN
437
438 htp.p ('<HR noshade size="1">');
439
440 END IF;
441
442 END LOOP;
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 Wf_Core.Context('wf_item_attributes_vl_pub', 'draw_item_attribute_details');
447 wf_item_definition.Error;
448
449 END draw_item_attribute_details;
450
451
452 END wf_item_attributes_vl_pub;
[Home] [Help]