1 Package hr_flexfield_info AUTHID CURRENT_USER as
2 /* $Header: peffinfo.pkh 115.7 2002/12/05 16:31:13 pkakar ship $ */
3
4 TYPE boolean_a IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
5 TYPE segment_description_a IS TABLE OF
6 VARCHAR2(2000) INDEX BY BINARY_INTEGER;
7 TYPE application_column_name_a IS TABLE OF
8 VARCHAR2(2000)
9 INDEX BY BINARY_INTEGER;
10 TYPE segment_name_a IS TABLE OF
11 VARCHAR2(2000)
12 INDEX BY BINARY_INTEGER;
13 TYPE sequence_a IS TABLE OF
14 NUMBER
15 INDEX BY BINARY_INTEGER;
16 TYPE display_size_a IS TABLE OF
17 NUMBER
18 INDEX BY BINARY_INTEGER;
19 TYPE row_prompt_a IS TABLE OF
20 VARCHAR2(2000)
21 INDEX BY BINARY_INTEGER;
22 TYPE column_prompt_a IS TABLE OF
23 VARCHAR2(2000)
24 INDEX BY BINARY_INTEGER;
25 TYPE value_set_a IS TABLE OF
26 NUMBER
27 INDEX BY BINARY_INTEGER;
28 TYPE validation_type_a IS TABLE OF
29 VARCHAR2(2000)
30 INDEX BY BINARY_INTEGER;
31 TYPE default_type_a IS TABLE OF
32 VARCHAR2(2000)
33 INDEX BY BINARY_INTEGER;
34 TYPE default_value_a IS TABLE OF
35 VARCHAR2(2000)
36 INDEX BY BINARY_INTEGER;
37 TYPE parent_segments_a IS TABLE OF
38 VARCHAR2(2000)
39 INDEX BY BINARY_INTEGER;
40 TYPE number_parents_a IS TABLE OF
41 BINARY_INTEGER
42 INDEX BY BINARY_INTEGER;
43 TYPE psegment_pointer_a IS TABLE OF
44 BINARY_INTEGER
45 INDEX BY BINARY_INTEGER;
46 TYPE ak_region_code_a IS TABLE OF
47 fnd_common_lookups.meaning%TYPE
48 INDEX BY BINARY_INTEGER;
49 TYPE number_of_children_a IS TABLE OF
50 NUMBER
51 INDEX BY BINARY_INTEGER;
52 TYPE format_type_a IS TABLE OF
53 fnd_flex_value_sets.format_type%TYPE
54 INDEX BY BINARY_INTEGER;
55 TYPE alphanumeric_allowed_flag_a IS TABLE OF
56 fnd_flex_value_sets.alphanumeric_allowed_flag%TYPE
57 INDEX BY BINARY_INTEGER;
58 TYPE uppercase_only_flag_a IS TABLE OF
59 fnd_flex_value_sets.uppercase_only_flag%TYPE
60 INDEX BY BINARY_INTEGER;
61 TYPE numeric_mode_enabled_flag_a IS TABLE OF
62 fnd_flex_value_sets.numeric_mode_enabled_flag%TYPE
63 INDEX BY BINARY_INTEGER;
64 TYPE maximum_size_a IS TABLE OF
65 fnd_flex_value_sets.maximum_size%TYPE
66 INDEX BY BINARY_INTEGER;
67 TYPE maximum_value_a IS TABLE OF
68 fnd_flex_value_sets.maximum_value%TYPE
69 INDEX BY BINARY_INTEGER;
70 TYPE minimum_value_a IS TABLE OF
71 fnd_flex_value_sets.minimum_value%TYPE
72 INDEX BY BINARY_INTEGER;
73 TYPE sql_text_a IS TABLE OF
74 LONG
75 INDEX BY BINARY_INTEGER;
76 TYPE segment_value_a IS TABLE OF
77 VARCHAR2(2000)
78 INDEX BY BINARY_INTEGER;
79 TYPE segment_value_changed_a IS TABLE OF
80 BOOLEAN
81 INDEX BY BINARY_INTEGER;
82
83 TYPE segments IS RECORD (
84 n_segments binary_integer,
85 segment_name segment_name_a,
86 segment_value segment_value_a,
87 segment_value_changed segment_value_changed_a);
88
89 TYPE hr_segments_info IS RECORD (nsegments BINARY_INTEGER,
90 application_column_name application_column_name_a,
91 segment_name segment_name_a,
92 sequence sequence_a,
93 is_displayed boolean_a,
94 display_size display_size_a,
95 row_prompt row_prompt_a,
96 column_prompt column_prompt_a,
97 is_enabled boolean_a,
98 is_required boolean_a,
99 description segment_description_a,
100 value_set value_set_a,
101 validation_type validation_type_a,
102 default_type default_type_a,
103 default_value default_value_a,
104 number_parents number_parents_a,
105 number_children number_of_children_a,
106 psegment_pointer psegment_pointer_a,
107 parent_segments parent_segments_a,
108 ak_region_code ak_region_code_a,
109 format_type format_type_a,
110 alphanumeric_allowed_flag alphanumeric_allowed_flag_a,
111 uppercase_only_flag uppercase_only_flag_a,
112 numeric_mode_flag numeric_mode_enabled_flag_a,
113 max_size maximum_size_a,
114 max_value maximum_value_a,
115 min_value minimum_value_a,
116 longlist_enabled boolean_a,
117 has_id boolean_a,
118 has_meaning boolean_a,
119 sql_text sql_text_a,
120 sql_txt_descr sql_text_a);
121 --
122 -- ----------------------------------------------------------------------------
123 -- |-------------------------------< Initialize >-----------------------------|
124 -- ----------------------------------------------------------------------------
125 -- {Start Of Comments}
126 --
127 -- Description:
128 -- This procedure initializes the global variables used by the
129 -- HR_FLEXFIELD_INFO package. It should be called before any other call to
130 -- this package.
131 --
132 -- Prerequisites:
133 -- There are no prerequisites to using this procedure.
134 --
135 -- In Parameters:
136 -- Name Reqd Type Description
137 -- None
138 --
139 -- Post Success:
140 -- The global variables are initialized.
141 --
142 -- Post Failure:
143 -- This procedure will not generate any error.
144 --
145 -- Access Status:
146 -- Public.
147 --
148 -- {End of Comments}
149 --
150 procedure initialize;
151 --
152 procedure structure_column_name(
153 p_appl_short_name IN VARCHAR2
154 ,p_flex_name IN VARCHAR2
155 ,p_column_name OUT NOCOPY VARCHAR2
156 ,p_column_name_prompt OUT NOCOPY VARCHAR2
157 ,p_dcontext_field OUT NOCOPY VARCHAR2
158 ,p_default_context_value OUT NOCOPY VARCHAR2);
159 --
160 -- ----------------------------------------------------------------------------
161 -- |-----------------------< get_concatenated_contexts >----------------------|
162 -- ----------------------------------------------------------------------------
163 -- {Start Of Comments}
164 --
165 -- Description:
166 -- This procedure retrieves the context_codes for a descriptive flexfield
167 -- and concatenates with any character that is passed to this procedure
168 -- through p_concatenation_chr input parameter. This procedure was built
169 -- for a particular Self Service WEB requirement.
170 --
171 -- Prerequisites:
172 -- There are no prereqs for this procedure.
173 --
174 -- In Parameters:
175 -- Name Reqd Type Description
176 -- p_appl_short_name Yes varchar2 Application Short Name
177 -- (eg. 'FND','PER',etc.)
178 -- p_flexfield_name Yes varchar2 Flexfield name
179 -- p_enabled_only Yes boolean 'TRUE' for enabled contexts
180 -- and 'FALSE' for all contexts
181 -- p_concatenation_chr Yes varchar2 Character that should be
182 -- used for concatenation
183 -- (eg. '-','#',etc.)
184 --
185 -- Out Parameters:
186 -- Name Type Description
187 -- p_context_list long This out parameter will contain the
188 -- concatenated list of the contexts
189 -- Post Success:
190 -- A string containing the valid contexts are returned. The contexts
191 -- are separated by the concenation character sent in by the calling
192 -- procedure.
193 --
194 -- Post Failure:
195 -- An empty string is returned.
196 --
197 -- Access Status:
198 -- Public.
199 --
200 PROCEDURE get_concatenated_contexts
201 (p_appl_short_name IN
202 fnd_application.application_short_name%TYPE,
203 p_flexfield_name IN
204 fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
205 p_enabled_only IN BOOLEAN,
206 p_concatenation_chr IN VARCHAR2,
207 p_context_list OUT NOCOPY LONG);
208 --
209 -- ----------------------------------------------------------------------------
210 -- |----------------------------< get_segments >------------------------------|
211 -- ----------------------------------------------------------------------------
212 -- {Start Of Comments}
213 --
214 -- Description:
215 -- This procedure retrieves the segment information for a particular context
216 -- of a descriptive flexfield. It makes use of AOL flexfield APIs
217 -- FND_DFLEX.get_contexts.
218 --
219 -- Prerequisites:
220 -- A call to initialize must be made before this procedure will
221 -- function properly.
222 --
223 -- In Parameters:
224 -- Name Reqd Type Description
225 -- p_appl_short_name Yes varchar2 Application Short Name
226 -- (eg. 'FND','PER',etc.)
227 -- p_flexfield_name Yes varchar2 Flexfield name
228 -- p_context_code Yes varchar2 Context Code
229 -- p_enabled_only Yes boolean 'TRUE' for enabled segments
230 -- and 'FALSE' for all segments
231 --
232 -- Out Parameters:
233 -- Name Type Description
234 -- p_segments hr_flexfield_info_pkg. This out parameter will contain
235 -- hr_segments_info information about all segments
236 -- within a particular context
237 -- of the flexfield in the
238 -- following record structure
239 --
240 -- TYPE segments_info IS RECORD (nsegments,
241 -- application_column_name, (The column to which the segment is mapped)
242 -- segment_name, (The name given to the segment)
243 -- sequence, (The order of the display of segments)
244 -- is_displayed, (whether the segment is displayed)
245 -- display_size, (An appropriate display size)
246 -- row_prompt, (The prompt for the segment)
247 -- column_prompt (The prompt for an LOV associated with this segment)
248 -- is_enabled, (Whether the segment is enabled?)
249 -- is_required,(Whether this is a required segment)
250 -- description, (the description of the segment)
251 -- value_set, (The ID of the value set associated with the segment)
252 -- default_type, (The default type of the segment)
253 -- default_value (The default value of the segment)
254 -- number_parents (The number of segments that must be populated before
255 -- this segment can be entered)
256 -- psegment_pointer (The pointer to the parent segments table for this
257 -- segment)
258 -- parent_segments (A list of the parent segments of this segment)
259 -- ak_region_code (The region code need for the Java window LOV)
260 -- format_type (The format type of the segment)
261 -- alphanumeric_allowed_flag (Whether this segment is numbers only)
262 -- uppercase_only_flag (Whether the aphlas in this segment must be
263 -- uppercase)
264 -- numeric_mode_flag (Whether the numbers in this field are right
265 -- justified an zero filled)
269 -- min_value (The minimum value of the segment)
266 -- max_size (The maximum number of characters in the
267 -- segment)
268 -- max_value (The maximum value of the segment)
270 -- longlist_enabled (Whether the LOV is a long list)
271 -- has_id (Whether the value set has an ID column)
272 -- has_meaning (Whether the value set has a meaning column)
273 -- sql_text (The SQL text needed to generate the LOV for
274 -- this segment)
275 -- sql_txt_descr (The SQL text need to convert the value/id into
276 -- a meaning or the id into a value)
277 --
278 -- Post Success:
279 -- The record structure is fully populated with the information on
280 -- the segments for the supplied flexfield, for the supplied context
281 -- procedure.
282 --
283 -- Post Failure:
284 -- A Null record is returned. (Dependant on the behaviour of the
285 -- the flexfield routine)
286 --
287 -- Access Status:
288 -- Public.
289 --
290 --
291 PROCEDURE get_segments
292 (p_appl_short_name IN
293 fnd_application.application_short_name%TYPE,
294 p_flexfield_name IN
295 fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
296 p_context_code IN VARCHAR2,
297 p_enabled_only IN BOOLEAN,
298 p_segments OUT NOCOPY hr_segments_info,
299 p_session_date IN DATE);
300 --
301 -- ----------------------------------------------------------------------------
302 -- |----------------------------< build_sql_text >----------------------------|
303 -- ----------------------------------------------------------------------------
304 -- {Start Of Comments}
305 --
306 -- Description:
307 -- This procedure replaces the application column name bind
308 -- variables in the SQL text with the entered values. Note these values
309 -- must be those the flexfield is expecting to store on the Database, i.e
310 -- the 'VALUE' or 'ID' - not the 'DESCRIPTION'. Each column name must have
311 -- the value associated with it.
312 --
313 -- Prerequisites:
314 -- The SQL text must be that obtained from a call to
315 -- get_segments. It can be either SQL text. You *must* supply all
316 -- possible bind reference data to this procedure.
317 --
318 -- In Parameters:
319 -- Name Req'd Type Description
320 -- p_sql_text Y LONG The SQL text, found from a call
321 -- to get_validation_info
322 -- p_application_short_name Y VARCHAR2 The short name of the calling
323 -- application
324 -- p_application_table_name Y VARCHAR2 The table name associated with
325 -- this flexfield.
326 -- p_column_namex N VARCHAR2 A base table column name
327 -- associated with this flexfield
328 -- p_column_valuex N VARCHAR2 The value associated with the
329 -- xth column
330 --
331 -- Out Parameters:
332 -- Name Type Description
333 --
334 -- p_sql_text LONG The SQL text, with the bind references
335 -- replaced with the actual values.
336 --
337 -- Post Success:
338 -- The SQL text will replace any bind references of the form
339 -- APPLICATION_COLUMN_NAME, with the supplied value.
340 --
341 -- Post Failure:
342 -- This procedure will fail if a bind reference is supplied for which
343 -- there is no value. The bind reference will remain in the SQL text,
344 -- and thus the SQL text will not run against a database without further
345 -- input.
346 --
347 -- Access Status:
348 -- Public.
349
350 PROCEDURE build_sql_text(
351 p_sql_text IN OUT NOCOPY long,
352 p_application_short_name IN fnd_application.application_short_name%TYPE,
353 p_application_table_name IN fnd_tables.table_name%TYPE,
354 p_segment_name_value IN segments);
355
356 PROCEDURE build_sql_text
357 (p_sql_text IN OUT NOCOPY long,
358 p_application_short_name in
359 fnd_application.application_short_name%TYPE,
360 p_application_table_name in fnd_tables.table_name%TYPE,
361 p_column_name1 IN VARCHAR2 default null,
362 p_column_value1 IN VARCHAR2 default null,
363 p_column_name2 IN VARCHAR2 default null,
364 p_column_value2 IN VARCHAR2 default null,
365 p_column_name3 IN VARCHAR2 default null,
366 p_column_value3 IN VARCHAR2 default null,
367 p_column_name4 IN VARCHAR2 default null,
368 p_column_value4 IN VARCHAR2 default null,
369 p_column_name5 IN VARCHAR2 default null,
370 p_column_value5 IN VARCHAR2 default null,
371 p_column_name6 IN VARCHAR2 default null,
372 p_column_value6 IN VARCHAR2 default null,
373 p_column_name7 IN VARCHAR2 default null,
374 p_column_value7 IN VARCHAR2 default null,
375 p_column_name8 IN VARCHAR2 default null,
376 p_column_value8 IN VARCHAR2 default null,
377 p_column_name9 IN VARCHAR2 default null,
378 p_column_value9 IN VARCHAR2 default null,
382 p_column_value11 IN VARCHAR2 default null,
379 p_column_name10 IN VARCHAR2 default null,
380 p_column_value10 IN VARCHAR2 default null,
381 p_column_name11 IN VARCHAR2 default null,
383 p_column_name12 IN VARCHAR2 default null,
384 p_column_value12 IN VARCHAR2 default null,
385 p_column_name13 IN VARCHAR2 default null,
386 p_column_value13 IN VARCHAR2 default null,
387 p_column_name14 IN VARCHAR2 default null,
388 p_column_value14 IN VARCHAR2 default null,
389 p_column_name15 IN VARCHAR2 default null,
390 p_column_value15 IN VARCHAR2 default null,
391 p_column_name16 IN VARCHAR2 default null,
392 p_column_value16 IN VARCHAR2 default null,
393 p_column_name17 IN VARCHAR2 default null,
394 p_column_value17 IN VARCHAR2 default null,
395 p_column_name18 IN VARCHAR2 default null,
396 p_column_value18 IN VARCHAR2 default null,
397 p_column_name19 IN VARCHAR2 default null,
398 p_column_value19 IN VARCHAR2 default null,
399 p_column_name20 IN VARCHAR2 default null,
400 p_column_value20 IN VARCHAR2 default null,
401 p_column_name21 IN VARCHAR2 default null,
402 p_column_value21 IN VARCHAR2 default null,
403 p_column_name22 IN VARCHAR2 default null,
404 p_column_value22 IN VARCHAR2 default null,
405 p_column_name23 IN VARCHAR2 default null,
406 p_column_value23 IN VARCHAR2 default null,
407 p_column_name24 IN VARCHAR2 default null,
408 p_column_value24 IN VARCHAR2 default null,
409 p_column_name25 IN VARCHAR2 default null,
410 p_column_value25 IN VARCHAR2 default null,
411 p_column_name26 IN VARCHAR2 default null,
412 p_column_value26 IN VARCHAR2 default null,
413 p_column_name27 IN VARCHAR2 default null,
414 p_column_value27 IN VARCHAR2 default null,
415 p_column_name28 IN VARCHAR2 default null,
416 p_column_value28 IN VARCHAR2 default null,
417 p_column_name29 IN VARCHAR2 default null,
418 p_column_value29 IN VARCHAR2 default null,
419 p_column_name30 IN VARCHAR2 default null,
420 p_column_value30 IN VARCHAR2 default null);
421 --
422 -- ----------------------------------------------------------------------------
423 -- |---------------------< get_kf_concatenated_structures >-------------------|
424 -- ----------------------------------------------------------------------------
425 -- {Start Of Comments}
426 --
427 -- Description:
428 -- This procedure retrieves the structure codes for a key flexfield,
429 -- and concatenates with any character that is passed to this procedure
430 -- through p_concatenation_chr input parameter.
431 --
432 -- Prerequisites:
433 -- There are no prereqs for this procedure.
434 --
435 -- In Parameters:
436 -- Name Reqd Type Description
437 -- p_appl_short_name Yes varchar2 Application Short Name
438 -- (eg. 'FND','PER',etc.)
439 -- p_id_flex_code Yes varchar2 Flexfield name
440 -- p_enabled_only Yes boolean TRUE for enabled structures
441 -- and FALSE for all structures.
442 -- p_concatenation_chr Yes varchar2 Character that should be
443 -- used for concatenation
444 -- (eg. '-','#',etc.)
445 --
446 -- Out Parameters:
447 -- Name Type Description
448 -- p_structure_list long This out parameter will contain the
449 -- concatenated list of the contexts
450 -- Post Success:
451 -- A string containing the valid structures are returned. The structures
452 -- are separated by the concatenation character sent in by the calling
453 -- procedure.
454 --
455 -- Post Failure:
456 -- An empty string is returned.
457 --
458 -- Access Status:
459 -- Public.
460 --
461 PROCEDURE get_kf_concatenated_structures
462 (p_appl_short_name IN fnd_application.application_short_name%TYPE
463 ,p_id_flex_code IN fnd_id_flex_structures_vl.id_flex_code%TYPE
464 ,p_enabled_only IN BOOLEAN
465 ,p_concatenation_chr IN VARCHAR2
466 ,p_structure_list OUT NOCOPY LONG
467 );
468 --
469 -- ----------------------------------------------------------------------------
470 -- |--------------------------< get_kf_segments >-----------------------------|
471 -- ----------------------------------------------------------------------------
472 -- {Start Of Comments}
473 --
474 -- Description:
475 -- This procedure retrieves the segment information for a particular
476 -- key flexfield structure. It makes use of AOL key flexfield APIs.
477 --
478 -- Prerequisites:
479 -- A call to initialize must be made before each call of this procedure.
480 --
481 -- In Parameters:
482 -- Name Reqd Type Description
483 -- p_appl_short_name Yes varchar2 Application Short Name
487 -- p_enabled_only Yes boolean 'TRUE' for enabled segments
484 -- (eg. 'FND','PER',etc.)
485 -- p_id_flex_code Yes varchar2 Identifies the flexfield.
486 -- p_id_flex_structure_name Yes varchar2 Identifies the structure.
488 -- and 'FALSE' for all segments
489 --
490 -- Out Parameters:
491 -- Name Type Description
492 -- p_segments hr_segments_info This out parameter will contain
493 -- information about all segments
494 -- for a particular structure
495 -- of the flexfield.
496 -- Post Success:
497 -- The record structure is fully populated with the information on
498 -- the segments for the supplied flexfield, for the supplied context
499 -- procedure.
500 --
501 -- Post Failure:
502 -- A Null record is returned. (Dependant on the behaviour of the
503 -- flexfield routine)
504 --
505 -- Access Status:
506 -- Public.
507 --
508 --
509 PROCEDURE get_kf_segments
510 (p_appl_short_name IN fnd_application.application_short_name%TYPE
511 ,p_id_flex_code IN fnd_id_flex_structures_vl.id_flex_code%TYPE
512 ,p_id_flex_structure_name IN
513 fnd_id_flex_structures_vl.id_flex_structure_name%TYPE
514 ,p_enabled_only IN BOOLEAN
515 ,p_segments OUT NOCOPY hr_segments_info
516 ,p_session_date IN DATE
517 );
518 --
519 -- ----------------------------------------------------------------------------
520 -- |-------------------------< gen_ak_web_region_code >-----------------------|
521 -- ----------------------------------------------------------------------------
522 -- {Start Of Comments}
523 --
524 -- Description:
525 -- This function returns the AK_WEB_REGION_CODE lookup value for a
526 -- segment within an instance of a flexfield.
527 --
528 -- Prerequisites:
529 -- There are no prereqs for this function.
530 --
531 -- In Parameters:
532 -- Name Reqd Type Description
533 -- p_flexfield Yes varchar2 The flexfield.
534 -- p_context_or_id_flex_num Yes varchar2 The flexfield instance.
535 -- p_segment Yes varchar2 The flexfield segment.
536 --
537 -- Access Status:
538 -- Public.
539 --
540 --
541 function gen_ak_web_region_code
542 (p_flex_type in varchar2
543 ,p_flexfield in varchar2
544 ,p_context_or_id_flex_num in varchar2
545 ,p_segment in varchar2
546 ) return varchar2;
547
548 end hr_flexfield_info;
[Home] [Help]