[Home] [Help]
PACKAGE BODY: APPS.HR_ID_FLEX_STRUCTURE_INFO
Source
1 PACKAGE BODY hr_id_flex_structure_info
2 /* $Header: hrkfsinf.pkb 115.2 2002/12/11 14:27:51 hjonnala ship $ */
3 AS
4 --
5 TYPE t_segment_header IS RECORD
6 (application_id fnd_id_flex_structures.application_id%TYPE
7 ,id_flex_code fnd_id_flex_structures.id_flex_code%TYPE
8 ,id_flex_num fnd_id_flex_structures.id_flex_num%TYPE
9 ,effective_date DATE
10 ,first_index_number NUMBER
11 ,last_index_number NUMBER
12 );
13 TYPE t_segment_headers IS TABLE OF t_segment_header;
14 --
15 g_application_id1 fnd_id_flex_structures.application_id%TYPE;
16 g_id_flex_code1 fnd_id_flex_structures.id_flex_code%TYPE;
17 g_id_flex_num1 fnd_id_flex_structures.id_flex_num%TYPE;
18 g_effective_date1 DATE;
19 g_segments1 t_segments := t_segments();
20 g_application_id2 fnd_id_flex_structures.application_id%TYPE;
21 g_id_flex_code2 fnd_id_flex_structures.id_flex_code%TYPE;
22 g_id_flex_num2 fnd_id_flex_structures.id_flex_num%TYPE;
23 g_effective_date2 DATE;
24 g_segments2 t_segments := t_segments();
25 g_application_short_name3 fnd_application.application_short_name%TYPE;
26 g_application_id3 fnd_application.application_id%TYPE;
27 --
28 g_segment_headers t_segment_headers := t_segment_headers();
29 g_segments t_segments := t_segments();
30 --
31 -- -----------------------------------------------------------------------------
32 -- |------------------------------< add_segments >-----------------------------|
33 -- -----------------------------------------------------------------------------
34 PROCEDURE add_segments
35 (p_application_id IN fnd_id_flex_structures.application_id%TYPE
36 ,p_id_flex_code IN fnd_id_flex_structures.id_flex_code%TYPE
37 ,p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE
38 ,p_effective_date IN DATE
39 ,p_segments IN t_segments
40 )
41 IS
42 --
43 l_index_number NUMBER;
44 --
45 BEGIN
46 --
47 g_segment_headers.EXTEND;
48 g_segment_headers(g_segment_headers.LAST).application_id := p_application_id;
49 g_segment_headers(g_segment_headers.LAST).id_flex_code := p_id_flex_code;
50 g_segment_headers(g_segment_headers.LAST).id_flex_num := p_id_flex_num;
51 g_segment_headers(g_segment_headers.LAST).effective_date := p_effective_date;
52 l_index_number := p_segments.FIRST;
53 WHILE (l_index_number IS NOT NULL)
54 LOOP
55 g_segments.EXTEND;
56 IF (g_segment_headers(g_segment_headers.LAST).first_index_number IS NULL)
57 THEN
58 g_segment_headers(g_segment_headers.LAST).first_index_number := g_segments.LAST;
59 END IF;
60 g_segments(g_segments.LAST) := p_segments(l_index_number);
61 l_index_number := p_segments.NEXT(l_index_number);
62 END LOOP;
63 g_segment_headers(g_segment_headers.LAST).last_index_number := g_segments.LAST;
64 --
65 END add_segments;
66 --
67 -- -----------------------------------------------------------------------------
68 -- |------------------------------< get_segments >-----------------------------|
69 -- -----------------------------------------------------------------------------
70 PROCEDURE get_segments
71 (p_application_id IN fnd_id_flex_structures.application_id%TYPE
72 ,p_id_flex_code IN fnd_id_flex_structures.id_flex_code%TYPE
73 ,p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE
74 ,p_effective_date IN DATE
75 ,p_segments OUT NOCOPY t_segments
76 ,p_segments_exist OUT NOCOPY BOOLEAN
77 )
78 IS
79 --
80 l_segments t_segments := t_segments();
81 l_segments_exist BOOLEAN := FALSE;
82 l_index_number NUMBER;
83 l_first_index_number NUMBER;
84 l_last_index_number NUMBER;
85 --
86 BEGIN
87 --
88 l_index_number := g_segment_headers.FIRST;
89 WHILE (l_index_number IS NOT NULL)
90 AND (NOT l_segments_exist)
91 LOOP
92 IF (g_segment_headers(l_index_number).application_id = p_application_id)
93 AND (g_segment_headers(l_index_number).id_flex_code = p_id_flex_code)
94 AND (NVL(g_segment_headers(l_index_number).id_flex_num,hr_api.g_number) = NVL(p_id_flex_num,hr_api.g_number))
95 AND (g_segment_headers(l_index_number).effective_date = p_effective_date)
96 THEN
97 l_first_index_number := g_segment_headers(l_index_number).first_index_number;
98 l_last_index_number := g_segment_headers(l_index_number).last_index_number;
99 l_segments_exist := TRUE;
100 END IF;
101 l_index_number := g_segment_headers.NEXT(l_index_number);
102 END LOOP;
103 --
104 IF (l_segments_exist)
105 THEN
106 l_index_number := l_first_index_number;
107 WHILE (l_index_number IS NOT NULL)
108 AND (l_index_number <= l_last_index_number)
109 LOOP
110 l_segments.EXTEND;
111 l_segments(l_segments.LAST) := g_segments(l_index_number);
112 l_index_number := g_segments.NEXT(l_index_number);
113 END LOOP;
114 END IF;
115 --
116 p_segments := l_segments;
117 p_segments_exist := l_segments_exist;
118 --
119 END get_segments;
120 --
121 -- -----------------------------------------------------------------------------
122 -- |-----------------------------< used_segments >-----------------------------|
123 -- -----------------------------------------------------------------------------
124 -- {Start of Comments}
125 --
126 -- Description
127 -- This function returns a table containing the details of columns used by a
128 -- key flexfield structure.
129 --
130 -- Prerequisites
131 -- None.
132 --
133 -- In Parameters
134 -- Name Reqd Type Description
135 -- p_application_id Y number Application identifier
136 -- p_id_flex_code Y varchar2 Key flexfield code
137 -- p_id_flex_num Y number Key flexfield structure number
138 -- p_effective_date Y date Effective date
139 --
140 -- Post Success
141 -- A table containg the details of columns used by a key flexfield structure
142 -- is returned.
143 --
144 -- Post Failure
145 -- An error is raised.
146 --
147 -- Access Status
148 -- Internal Development Use Only
149 --
150 -- {End of Comments}
151 -- -----------------------------------------------------------------------------
152 FUNCTION used_segments
153 (p_application_id IN fnd_id_flex_structures.application_id%TYPE
154 ,p_id_flex_code IN fnd_id_flex_structures.id_flex_code%TYPE
155 ,p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE
156 ,p_effective_date IN DATE
157 )
158 RETURN t_segments
159 IS
160 --
161 -- Local cursors
162 --
163 CURSOR csr_id_flex_segments
164 (p_application_id IN fnd_id_flex_segments.application_id%TYPE
165 ,p_id_flex_code IN fnd_id_flex_segments.id_flex_code%TYPE
166 ,p_id_flex_num IN fnd_id_flex_segments.id_flex_num%TYPE
167 )
168 IS
169 SELECT ifs.application_column_name
170 ,ifs.segment_num
171 ,ifs.concatenation_description_len
172 ,ifs.default_type
173 ,ifs.default_value
174 ,ifs.display_flag
175 ,ifs.display_size
176 ,ifs.enabled_flag
177 ,ifs.flex_value_set_id
178 ,ifs.form_above_prompt
179 ,ifs.form_left_prompt
180 ,ifs.maximum_description_len
181 ,ifs.required_flag
182 ,ifs.segment_name
183 FROM fnd_id_flex_segments_vl ifs
184 ,fnd_id_flex_structures fst
185 WHERE ifs.application_id = fst.application_id
186 AND ifs.id_flex_code = fst.id_flex_code
187 AND ifs.id_flex_num = fst.id_flex_num
188 AND fst.application_id = p_application_id
189 AND fst.id_flex_code = p_id_flex_code
190 AND fst.id_flex_num = p_id_flex_num
191 AND fst.enabled_flag = 'Y';
192 --
193 -- Local variables
194 --
195 l_used_segments t_segments := t_segments();
196 l_value_set hr_flex_value_set_info.t_value_set;
197 --
198 BEGIN
199 --
200 FOR l_id_flex_segment IN csr_id_flex_segments
201 (p_application_id => p_application_id
202 ,p_id_flex_code => p_id_flex_code
203 ,p_id_flex_num => p_id_flex_num
204 )
205 LOOP
206 --
207 -- Retrieve value set details for segment
208 --
209 l_value_set := hr_flex_value_set_info.value_set
210 (p_flex_value_set_id => l_id_flex_segment.flex_value_set_id
211 ,p_effective_date => p_effective_date
212 );
213 --
214 -- Add segment to table
215 --
216 l_used_segments.EXTEND;
217 l_used_segments(l_used_segments.LAST).column_name := l_id_flex_segment.application_column_name;
218 l_used_segments(l_used_segments.LAST).sequence := l_id_flex_segment.segment_num;
219 l_used_segments(l_used_segments.LAST).concatenation_description_len := l_id_flex_segment.concatenation_description_len;
220 l_used_segments(l_used_segments.LAST).default_type := l_id_flex_segment.default_type;
221 l_used_segments(l_used_segments.LAST).default_value := l_id_flex_segment.default_value;
222 l_used_segments(l_used_segments.LAST).display_flag := l_id_flex_segment.display_flag;
223 l_used_segments(l_used_segments.LAST).display_size := l_id_flex_segment.display_size;
224 l_used_segments(l_used_segments.LAST).enabled_flag := l_id_flex_segment.enabled_flag;
225 l_used_segments(l_used_segments.LAST).flex_value_set_id := l_id_flex_segment.flex_value_set_id;
226 l_used_segments(l_used_segments.LAST).form_above_prompt := l_id_flex_segment.form_above_prompt;
227 l_used_segments(l_used_segments.LAST).form_left_prompt := l_id_flex_segment.form_left_prompt;
228 l_used_segments(l_used_segments.LAST).maximum_description_len := l_id_flex_segment.maximum_description_len;
229 l_used_segments(l_used_segments.LAST).required_flag := l_id_flex_segment.required_flag;
230 l_used_segments(l_used_segments.LAST).segment_name := l_id_flex_segment.segment_name;
231 l_used_segments(l_used_segments.LAST).additional_column1_title := l_value_set.additional_column1_title;
232 l_used_segments(l_used_segments.LAST).additional_column1_width := l_value_set.additional_column1_width;
233 l_used_segments(l_used_segments.LAST).additional_column2_title := l_value_set.additional_column2_title;
234 l_used_segments(l_used_segments.LAST).additional_column2_width := l_value_set.additional_column2_width;
235 l_used_segments(l_used_segments.LAST).additional_column3_title := l_value_set.additional_column3_title;
236 l_used_segments(l_used_segments.LAST).additional_column3_width := l_value_set.additional_column3_width;
237 l_used_segments(l_used_segments.LAST).alphanumeric_allowed_flag := l_value_set.alphanumeric_allowed_flag;
238 l_used_segments(l_used_segments.LAST).flex_value_set_name := l_value_set.flex_value_set_name;
239 l_used_segments(l_used_segments.LAST).format_type := l_value_set.format_type;
240 l_used_segments(l_used_segments.LAST).identification_sql := l_value_set.identification_sql;
241 l_used_segments(l_used_segments.LAST).id_column_type := l_value_set.id_column_type;
242 l_used_segments(l_used_segments.LAST).has_meaning := l_value_set.has_meaning;
243 l_used_segments(l_used_segments.LAST).longlist_flag := l_value_set.longlist_flag;
244 l_used_segments(l_used_segments.LAST).maximum_size := l_value_set.maximum_size;
245 l_used_segments(l_used_segments.LAST).maximum_value := l_value_set.maximum_value;
246 l_used_segments(l_used_segments.LAST).minimum_value := l_value_set.minimum_value;
247 l_used_segments(l_used_segments.LAST).numeric_mode_enabled_flag := l_value_set.numeric_mode_enabled_flag;
248 l_used_segments(l_used_segments.LAST).number_precision := l_value_set.number_precision;
249 l_used_segments(l_used_segments.LAST).uppercase_only_flag := l_value_set.uppercase_only_flag;
250 l_used_segments(l_used_segments.LAST).validation_sql := l_value_set.validation_sql;
251 l_used_segments(l_used_segments.LAST).validation_type := l_value_set.validation_type;
252 --
253 END LOOP;
254 --
255 RETURN(l_used_segments);
256 --
257 END used_segments;
258 --
259 -- -----------------------------------------------------------------------------
260 -- |--------------------------------< segments >-------------------------------|
261 -- -----------------------------------------------------------------------------
262 FUNCTION segments
263 (p_application_id IN fnd_id_flex_structures.application_id%TYPE
264 ,p_id_flex_code IN fnd_id_flex_structures.id_flex_code%TYPE
265 ,p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE
266 ,p_effective_date IN DATE
267 )
268 RETURN t_segments
269 IS
270 --
271 -- Local variables
272 --
273 l_segments t_segments := t_segments();
274 l_segments_exist BOOLEAN := FALSE;
275 l_all_segments hr_id_flex_info.t_segments := hr_id_flex_info.t_segments();
276 l_used_segments t_segments := t_segments();
277 l_all_index_number NUMBER;
278 l_used_index_number NUMBER;
279 l_used_segment_found BOOLEAN;
280 --
281 BEGIN
282 --
283 get_segments
284 (p_application_id => p_application_id
285 ,p_id_flex_code => p_id_flex_code
286 ,p_id_flex_num => p_id_flex_num
287 ,p_effective_date => p_effective_date
288 ,p_segments => l_segments
289 ,p_segments_exist => l_segments_exist
290 );
291 --
292 IF (l_segments_exist)
293 THEN
294 --
295 NULL;
296 --
297 ELSE
298 --
299 -- Retrieve all segments available for this key flexfield
300 --
301 l_all_segments := hr_id_flex_info.segments
302 (p_application_id => p_application_id
303 ,p_id_flex_code => p_id_flex_code
304 );
305 --
306 -- Retrieve segments used by the key flexfield structure
307 --
308 l_used_segments := used_segments
309 (p_application_id => p_application_id
310 ,p_id_flex_code => p_id_flex_code
311 ,p_id_flex_num => p_id_flex_num
312 ,p_effective_date => p_effective_date
313 );
314 --
315 -- For all segments available for this key flexfield
316 --
317 l_all_index_number := l_all_segments.FIRST;
318 WHILE (l_all_index_number IS NOT NULL)
319 LOOP
320 --
321 -- Determine if the avaiable segment is actually used by the key flexfield structure
322 --
323 l_used_segment_found := FALSE;
324 l_used_index_number := l_used_segments.FIRST;
325 WHILE (l_used_index_number IS NOT NULL)
326 AND (NOT l_used_segment_found)
327 LOOP
328 IF (l_all_segments(l_all_index_number).column_name = l_used_segments(l_used_index_number).column_name)
329 THEN
330 l_used_segment_found := TRUE;
331 ELSE
332 l_used_index_number := l_used_segments.NEXT(l_used_index_number);
333 END IF;
334 END LOOP;
335 --
336 -- Add used segment details, if segment is used by the key flexfield
337 -- structure; otherwise just add the segments basic details to table
338 --
339 l_segments.EXTEND;
340 IF (l_used_segment_found)
341 THEN
342 l_segments(l_segments.LAST).column_name := l_used_segments(l_used_index_number).column_name;
343 l_segments(l_segments.LAST).sequence := l_used_segments(l_used_index_number).sequence;
344 l_segments(l_segments.LAST).additional_column1_title := l_used_segments(l_used_index_number).additional_column1_title;
345 l_segments(l_segments.LAST).additional_column1_width := l_used_segments(l_used_index_number).additional_column1_width;
346 l_segments(l_segments.LAST).additional_column2_title := l_used_segments(l_used_index_number).additional_column2_title;
347 l_segments(l_segments.LAST).additional_column2_width := l_used_segments(l_used_index_number).additional_column2_width;
348 l_segments(l_segments.LAST).additional_column3_title := l_used_segments(l_used_index_number).additional_column3_title;
349 l_segments(l_segments.LAST).additional_column3_width := l_used_segments(l_used_index_number).additional_column3_width;
350 l_segments(l_segments.LAST).alphanumeric_allowed_flag := l_used_segments(l_used_index_number).alphanumeric_allowed_flag;
351 l_segments(l_segments.LAST).concatenation_description_len := l_used_segments(l_used_index_number).concatenation_description_len;
352 l_segments(l_segments.LAST).default_type := l_used_segments(l_used_index_number).default_type;
353 l_segments(l_segments.LAST).default_value := l_used_segments(l_used_index_number).default_value;
354 l_segments(l_segments.LAST).display_flag := l_used_segments(l_used_index_number).display_flag;
355 l_segments(l_segments.LAST).display_size := l_used_segments(l_used_index_number).display_size;
356 l_segments(l_segments.LAST).enabled_flag := l_used_segments(l_used_index_number).enabled_flag;
357 l_segments(l_segments.LAST).flex_value_set_id := l_used_segments(l_used_index_number).flex_value_set_id;
358 l_segments(l_segments.LAST).flex_value_set_name := l_used_segments(l_used_index_number).flex_value_set_name;
359 l_segments(l_segments.LAST).format_type := l_used_segments(l_used_index_number).format_type;
360 l_segments(l_segments.LAST).form_above_prompt := l_used_segments(l_used_index_number).form_above_prompt;
361 l_segments(l_segments.LAST).form_left_prompt := l_used_segments(l_used_index_number).form_left_prompt;
362 l_segments(l_segments.LAST).identification_sql := l_used_segments(l_used_index_number).identification_sql;
363 l_segments(l_segments.LAST).id_column_type := l_used_segments(l_used_index_number).id_column_type;
364 l_segments(l_segments.LAST).has_meaning := l_used_segments(l_used_index_number).has_meaning;
365 l_segments(l_segments.LAST).longlist_flag := l_used_segments(l_used_index_number).longlist_flag;
366 l_segments(l_segments.LAST).maximum_description_len := l_used_segments(l_used_index_number).maximum_description_len;
367 l_segments(l_segments.LAST).maximum_size := l_used_segments(l_used_index_number).maximum_size;
368 l_segments(l_segments.LAST).maximum_value := l_used_segments(l_used_index_number).maximum_value;
369 l_segments(l_segments.LAST).minimum_value := l_used_segments(l_used_index_number).minimum_value;
370 l_segments(l_segments.LAST).numeric_mode_enabled_flag := l_used_segments(l_used_index_number).numeric_mode_enabled_flag;
371 l_segments(l_segments.LAST).required_flag := l_used_segments(l_used_index_number).required_flag;
372 l_segments(l_segments.LAST).segment_name := l_used_segments(l_used_index_number).segment_name;
373 l_segments(l_segments.LAST).uppercase_only_flag := l_used_segments(l_used_index_number).uppercase_only_flag;
374 l_segments(l_segments.LAST).validation_sql := l_used_segments(l_used_index_number).validation_sql;
375 l_segments(l_segments.LAST).validation_type := l_used_segments(l_used_index_number).validation_type;
376 ELSE
377 l_segments(l_segments.LAST).column_name := l_all_segments(l_all_index_number).column_name;
378 END IF;
379 --
380 l_all_index_number := l_all_segments.NEXT(l_all_index_number);
381 --
382 END LOOP;
383 --
384 add_segments
385 (p_application_id => p_application_id
386 ,p_id_flex_code => p_id_flex_code
387 ,p_id_flex_num => p_id_flex_num
388 ,p_effective_date => p_effective_date
389 ,p_segments => l_segments
390 );
391 --
392 END IF;
393 --
394 RETURN(l_segments);
395 --
396 END segments;
397 --
398 -- -----------------------------------------------------------------------------
399 -- |------------------------------< segments_pst >-----------------------------|
400 -- -----------------------------------------------------------------------------
401 FUNCTION segments_pst
402 (p_application_short_name IN fnd_application.application_short_name%TYPE
403 ,p_id_flex_code IN fnd_id_flex_structures.id_flex_code%TYPE
404 ,p_id_flex_num IN fnd_id_flex_structures.id_flex_num%TYPE
405 ,p_effective_date IN DATE
406 )
407 RETURN t_segments_pst
408 IS
409 --
410 -- Local cursors
411 --
412 CURSOR csr_applications
413 (p_application_short_name IN fnd_application.application_short_name%TYPE
414 )
415 IS
416 SELECT app.application_id
417 FROM fnd_application app
418 WHERE app.application_short_name = p_application_short_name;
419 --
420 -- Local variables
421 --
422 l_segments_pst t_segments_pst;
423 l_application_id fnd_application.application_id%TYPE;
424 l_segments t_segments := t_segments();
425 l_index_number NUMBER;
426 --
427 BEGIN
428 --
429 IF (p_application_short_name = g_application_short_name3)
430 THEN
431 --
432 l_application_id := g_application_id3;
433 --
434 ELSE
435 --
436 OPEN csr_applications
437 (p_application_short_name => p_application_short_name
438 );
439 FETCH csr_applications INTO l_application_id;
440 CLOSE csr_applications;
441 --
442 g_application_short_name3 := p_application_short_name;
443 g_application_id3 := l_application_id;
444 --
445 END IF;
446 --
447 l_segments := segments
448 (p_application_id => l_application_id
449 ,p_id_flex_code => p_id_flex_code
450 ,p_id_flex_num => p_id_flex_num
451 ,p_effective_date => p_effective_date
452 );
453 --
454 l_index_number := l_segments.FIRST;
455 WHILE (l_index_number IS NOT NULL)
456 LOOP
457 l_segments_pst(l_index_number) := l_segments(l_index_number);
458 l_index_number := l_segments.NEXT(l_index_number);
459 END LOOP;
460 --
461 RETURN(l_segments_pst);
462 --
463 END segments_pst;
464 --
465 END hr_id_flex_structure_info;