1 PACKAGE BODY hr_flex_value_set_info
2 /* $Header: hrfvsinf.pkb 120.2 2005/09/08 22:49:06 ghshanka noship $ */
3 AS
4 -- -----------------------------------------------------------------------------
5 -- |-------------------------< parent_value_set_name >-------------------------|
6 -- -----------------------------------------------------------------------------
7 -- {Start of Comments}
8 --
9 -- Description
10 -- This function returns the name of the parent flexfield value set for the
11 -- specified flexfield value set.
12 --
13 -- Prerequisites
14 -- None.
15 --
16 -- In Parameters
17 -- Name Reqd Type Description
18 -- p_flex_value_set_id Y number Flexfield value set identifier
19 --
20 -- Post Success
21 -- The name of the parent flexfield value set is returned.
22 --
23 -- Post Failure
24 -- An error is raised.
25 --
26 -- Access Status
27 -- Internal Development Use Only
28 --
29 -- {End of Comments}
30 -- -----------------------------------------------------------------------------
31 FUNCTION parent_value_set_name
32 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
33 )
34 RETURN fnd_flex_value_sets.flex_value_set_name%TYPE
35 IS
36 --
37 -- Local cursors
38 --
39 CURSOR csr_value_sets
40 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
41 )
42 IS
43 SELECT ind.flex_value_set_name parent_value_set_name
44 FROM fnd_flex_value_sets ind
45 ,fnd_flex_value_sets dep
46 WHERE ind.flex_value_set_id = dep.parent_flex_value_set_id
47 AND dep.flex_value_set_id = p_flex_value_set_id;
48 --
49 -- Local variables
50 --
51 l_value_set csr_value_sets%ROWTYPE;
52 --
53 BEGIN
54 --
55 -- Retrive parent flexfield value set name
56 --
57 OPEN csr_value_sets
58 (p_flex_value_set_id => p_flex_value_set_id
59 );
60 FETCH csr_value_sets INTO l_value_set;
61 CLOSE csr_value_sets;
62 --
63 RETURN(l_value_set.parent_value_set_name);
64 --
65 END parent_value_set_name;
66 --
67 -- -----------------------------------------------------------------------------
68 -- |------------------------< additional_column_title >------------------------|
69 -- -----------------------------------------------------------------------------
70 -- {Start of Comments}
71 --
72 -- Description
73 -- This function returns the additional column title. If the column title is
74 -- in a format which suggests it is to be derived from a message:
75 -- APPL=<application_short_name>NAME=<message_name>
76 -- the message text is returned; otherwise the value passed in is returned.
77 --
78 -- Prerequisites
79 -- None.
80 --
81 -- In Parameters
82 -- Name Reqd Type Description
83 -- p_additional_column_title Y varchar2 Additional column title
84 --
85 -- Post Success
86 -- The additional column title is returned.
87 --
88 -- Post Failure
89 -- The value passed in is returned. No error is raised.
90 --
91 -- Access Status
92 -- Internal Development Use Only
93 --
94 -- {End of Comments}
95 -- -----------------------------------------------------------------------------
96 FUNCTION additional_column_title
97 (p_additional_column_title IN VARCHAR2
98 )
99 RETURN VARCHAR2
100 IS
101 --
102 -- Local variables
103 --
104 l_additional_column_title VARCHAR2(2000) := p_additional_column_title;
105 l_application_short_name fnd_application.application_short_name%TYPE;
106 l_message_name fnd_new_messages.message_name%TYPE;
107 l_application_indicator VARCHAR2(30) := 'APPL=';
108 l_message_indicator VARCHAR2(30) := 'NAME=';
109 l_separator VARCHAR2(30) := ';';
110 l_application_indicator_len NUMBER := LENGTH(l_application_indicator);
111 l_message_indicator_len NUMBER := LENGTH(l_message_indicator);
112 l_separator_len NUMBER := LENGTH(l_separator);
113 l_application_indicator_pos NUMBER;
114 l_message_indicator_pos NUMBER;
115 l_separator_pos NUMBER;
116 l_application_short_name_pos NUMBER;
117 l_message_name_pos NUMBER;
118 --
119 BEGIN
120 --
121 -- Determine position of indicators
122 --
123 l_application_indicator_pos := INSTRB(l_additional_column_title,l_application_indicator,1,1);
124 l_message_indicator_pos := INSTRB(l_additional_column_title,l_message_indicator,1,1);
125 l_separator_pos := INSTRB(l_additional_column_title,l_separator,1,1);
126 --
127 -- If indicators are present, suggests additional column title is to be
128 -- derived from a message
129 --
130 IF (l_application_indicator_pos <> 0)
131 AND (l_message_indicator_pos <> 0)
132 AND (l_separator_pos <> 0)
133 THEN
134 l_application_short_name_pos := l_application_indicator_pos + l_application_indicator_len;
135 l_message_name_pos := l_message_indicator_pos + l_message_indicator_len;
136 l_application_short_name := SUBSTRB(l_additional_column_title,l_application_short_name_pos,l_separator_pos-l_application_short_name_pos-1);
137 l_message_name := SUBSTRB(l_additional_column_title,l_message_name_pos);
138 fnd_message.set_name(l_application_short_name,l_message_name);
139 l_additional_column_title := fnd_message.get;
140 END IF;
141 --
142 RETURN(l_additional_column_title);
143 --
144 EXCEPTION
145 --
146 -- Return value passed in on any failure
147 --
148 WHEN OTHERS
149 THEN
150 RETURN(p_additional_column_title);
151 --
152 END additional_column_title;
153 --
154 -- -----------------------------------------------------------------------------
155 -- |-------------------------< get_additional_column >------------------------|
156 -- -----------------------------------------------------------------------------
157 -- {Start of Comments}
158 --
159 -- Description
160 -- This procedure returns the details of the nth additional column specified
161 -- in the additional quickpick columns. The additional columns should be
162 -- specified in the form:
163 -- <additional_column> "<additional_column_title>" (<additional_column_width>) , ...
164 --
165 -- Prerequisites
166 -- None.
167 --
168 -- In Parameters
169 -- Name Reqd Type Description
170 -- p_additional_quickpick_columns Y varchar2 Additional column title
171 -- p_column_number Y number Number of additional column
172 -- for which details should be
173 -- determined.
174 --
175 -- Post Success
176 -- The following out parameters are set:
177 -- Name Type Description
178 -- p_additional_column varchar2 Additional column
179 -- p_additional_column_title varchar2 Additional column title
180 -- p_additional_column_width varchar2 Additional column width
181 --
182 -- Post Failure
183 -- An error is raised.
184 --
185 -- Access Status
186 -- Internal Development Use Only
187 --
188 -- {End of Comments}
189 -- -----------------------------------------------------------------------------
190 PROCEDURE get_additional_column
191 (p_additional_quickpick_columns IN fnd_flex_validation_tables.additional_quickpick_columns%TYPE
192 ,p_column_number IN NUMBER
193 ,p_additional_column OUT NOCOPY VARCHAR2
194 ,p_additional_column_title OUT NOCOPY VARCHAR2
195 ,p_additional_column_width OUT NOCOPY VARCHAR2
196 )
197 IS
198 --
199 -- Local variables
200 --
201 l_additional_quickpick_columns fnd_flex_validation_tables.additional_quickpick_columns%TYPE := p_additional_quickpick_columns;
202 l_additional_column VARCHAR2(2000) := NULL;
203 l_additional_column_title VARCHAR2(2000) := NULL;
204 l_additional_column_width VARCHAR2(2000) := NULL;
205 l_column_number NUMBER := 0;
206 l_first_quote_pos NUMBER;
207 l_second_quote_pos NUMBER;
208 l_first_bracket_pos NUMBER;
209 l_second_bracket_pos NUMBER;
210 l_separator_pos NUMBER;
211 --
212 BEGIN
213 --
214 -- Assume each additional column must have a title in quotes. Cannot use the
215 -- separator character as this may be used in the additional column if it
216 -- is a function eg DECODE(attribute,'N',0,1)
217 --
218 l_first_quote_pos := INSTRB(l_additional_quickpick_columns,'"',1,1);
219 WHILE (l_first_quote_pos <> 0)
220 AND (l_column_number <> p_column_number)
221 LOOP
222 l_column_number := l_column_number + 1;
223 l_second_quote_pos := INSTRB(l_additional_quickpick_columns,'"',l_first_quote_pos+1,1);
224 l_first_bracket_pos := INSTRB(l_additional_quickpick_columns,'(',l_second_quote_pos,1);
225 l_second_bracket_pos := INSTRB(l_additional_quickpick_columns,')',l_second_quote_pos,1);
226 l_separator_pos := INSTRB(l_additional_quickpick_columns,',',l_second_quote_pos,1);
227 --
228 -- Only determine details if this is the column number interested in
229 --
230 IF (l_column_number = p_column_number)
231 THEN
232 l_additional_column := SUBSTRB(l_additional_quickpick_columns,1,l_first_quote_pos-1);
233 l_additionaL_column_title := SUBSTRB(l_additional_quickpick_columns,l_first_quote_pos+1,l_second_quote_pos-l_first_quote_pos-1);
234 l_additional_column_title := additional_column_title(l_additional_column_title);
235 l_additional_column_width := NULL;
236 --
237 -- Additional column width is optional; so only retrieve if it is
238 -- specified ie brackets exist
239 --
240 IF (l_first_bracket_pos <> 0)
241 AND ( (l_first_bracket_pos < l_separator_pos)
242 OR (l_separator_pos = 0))
243 THEN
244 l_additional_column_width := SUBSTRB(l_additional_quickpick_columns,l_first_bracket_pos+1,l_second_bracket_pos-l_first_bracket_pos-1);
245 END IF;
246 END IF;
247 --
248 -- If no separator found must be last additional column specified; so set
249 -- internal variable to NULL
250 --
251 IF (l_separator_pos <> 0)
252 THEN
253 l_additional_quickpick_columns := SUBSTRB(l_additional_quickpick_columns,l_separator_pos+1);
254 ELSE
255 l_additional_quickpick_columns := NULL;
256 END IF;
257 --
258 l_first_quote_pos := INSTRB(l_additional_quickpick_columns,'"',1,1);
259 --
260 END LOOP;
261 --
262 -- Populate out parameters
263 --
264 p_additional_column := l_additional_column;
265 p_additional_column_title := l_additional_column_title;
266 p_additional_column_width := l_additional_column_width;
267 --
268 END get_additional_column;
269 --
270 -- -----------------------------------------------------------------------------
271 -- |-----------------------------< get_table_sql >-----------------------------|
272 -- -----------------------------------------------------------------------------
273 -- {Start of Comments}
274 --
275 -- Description
276 -- This procedure returns the details of the SQL to be used for a value set
277 -- based on a validation table.
278 --
279 -- Prerequisites
280 -- None.
281 --
282 -- In Parameters
283 -- Name Reqd Type Description
284 -- p_flex_value_set_id Y number Flexfield value set identifier
285 -- p_flex_value_set_name Y varchar2 Flexfield value set name
286 -- p_effective_date Y date Effective date
287 --
288 -- Post Success
289 -- The following out parameters are set:
290 -- Name Type Description
291 -- p_validation_sql varchar2 SQL statement to be used in
292 -- validation. The flexfield should
293 -- have one of the values returned
294 -- by the statement.
295 -- p_identification_sql varchar2 SQL statement to be used in
296 -- identification. It should return
297 -- one and only one record.
298 -- p_id_column_type varchar2 Datatype of the id column
299 -- p_has_meaning boolean Does the SQL contain a meaning
300 -- column?
301 -- p_additional_column1_title varchar2 Additional column title (1)
302 -- p_additional_column1_width varchar2 Additional column width (1)
303 -- p_additional_column2_title varchar2 Additional column title (2)
304 -- p_additional_column2_width varchar2 Additional column width (2)
305 -- p_additional_column3_title varchar2 Additional column title (3)
306 -- p_additional_column3_width varchar2 Additional column width (3)
307 --
308 -- Post Failure
309 -- An error is raised.
310 --
311 -- Access Status
312 -- Internal Development Use Only
313 --
314 -- {End of Comments}
315 -- -----------------------------------------------------------------------------
316 PROCEDURE get_table_sql
317 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
318 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
319 ,p_effective_date IN DATE
320 ,p_validation_sql OUT NOCOPY VARCHAR2
321 ,p_identification_sql OUT NOCOPY VARCHAR2
322 ,p_id_column_type OUT NOCOPY VARCHAR2
323 ,p_has_meaning OUT NOCOPY BOOLEAN
324 ,p_additional_column1_title OUT NOCOPY VARCHAR2
325 ,p_additional_column1_width OUT NOCOPY VARCHAR2
326 ,p_additional_column2_title OUT NOCOPY VARCHAR2
327 ,p_additional_column2_width OUT NOCOPY VARCHAR2
328 ,p_additional_column3_title OUT NOCOPY VARCHAR2
329 ,p_additional_column3_width OUT NOCOPY VARCHAR2
330 )
331 IS
332 --
333 --Bug# 3011981 Start Here
334 --Description : Changed the value_column_type to value_column_name to create the dynamic query.
335 --
336 -- Local cursors
337 --
338 CURSOR csr_validation_tables
339 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
340 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
341 )
342 IS
343 SELECT DECODE(fvt.value_column_type
344 ,'D','fnd_date.date_to_displaydate('||fvt.value_column_name||')'
345 ,'N',fvt.value_column_name
346 ,fvt.value_column_name
347 ) AS value_column_name
348 ,NVL(fvt.meaning_column_name,'NULL') AS meaning_column_name
349 ,DECODE(NVL(fvt.id_column_type,fvt.value_column_type)
350 ,'D','fnd_date.date_to_canonical('||NVL(fvt.id_column_name,fvt.value_column_name)||')'
351 ,'N','fnd_number.number_to_canonical('||NVL(fvt.id_column_name,fvt.value_column_name)||')'
352 ,NVL(fvt.id_column_name,fvt.value_column_name)
353 ) AS id_column_name
354 ,NVL(fvt.id_column_type,fvt.value_column_type) AS id_column_type
355 ,'AND '||NVL(fvt.id_column_name,fvt.value_column_name)||' = :$FLEX$.$$'||p_flex_value_set_name AS identification_clause
356 ,fvt.application_table_name AS from_clause
357 ,fvt.additional_where_clause AS where_and_order_clause
358 ,fvt.additional_where_clause AS where_clause
359 ,fvt.additional_where_clause AS order_clause
360 ,fvt.start_date_column_name
361 ,fvt.end_date_column_name
362 ,fvt.additional_quickpick_columns
363 FROM fnd_flex_validation_tables fvt
364 WHERE fvt.flex_value_set_id = p_flex_value_set_id;
365 --
366 --Bug# 3011981 End Here
370 --
367 --
368 --
369 -- Local variables
371 l_effective_date VARCHAR2(32767);
372 l_order_clause_position NUMBER;
373 l_additional_column1 VARCHAR2(2000);
374 l_additional_column2 VARCHAR2(2000);
375 l_additional_column3 VARCHAR2(2000);
376 --
377 BEGIN
378 --
379 -- Creating a character string for the SQL, so date should be converted to
380 -- character consistently
381 --
382 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
383 --
384 FOR l_validation_table IN csr_validation_tables
385 (p_flex_value_set_id => p_flex_value_set_id
386 ,p_flex_value_set_name => p_flex_value_set_name
387 )
388 LOOP
389 --
390 l_order_clause_position := INSTRB(UPPER(l_validation_table.where_and_order_clause),'ORDER BY');
391 IF (l_order_clause_position > 0)
392 THEN
393 --
394 -- ORDER BY clause already specified, split into the constituent WHERE and
395 -- ORDER BY statements
396 --
397 l_validation_table.where_clause := SUBSTRB(l_validation_table.where_and_order_clause,1,l_order_clause_position - 1);
398 l_validation_table.order_clause := SUBSTRB(l_validation_table.where_and_order_clause,l_order_clause_position);
399 --
400 ELSE
401 --
402 -- ORDER BY clause NOT specified, so add the default clause
403 --
404 l_validation_table.where_clause := l_validation_table.where_and_order_clause;
405 l_validation_table.order_clause := 'ORDER BY 1';
406 --
407 END IF;
408 --
409 -- Add effective date validation to WHERE clause
410 --
411 IF (l_validation_table.where_clause IS NULL)
412 THEN
413 l_validation_table.where_clause := 'WHERE '||l_effective_date||' BETWEEN NVL('||l_validation_table.start_date_column_name||','||l_effective_date||')'
414 ||' AND NVL('||l_validation_table.end_date_column_name||','||l_effective_date||')';
415 ELSE
416 l_validation_table.where_clause := l_validation_table.where_clause
417 ||' AND '||l_effective_date||' BETWEEN NVL('||l_validation_table.start_date_column_name||','||l_effective_date||')'
418 ||' AND NVL('||l_validation_table.end_date_column_name||','||l_effective_date||')';
419 END IF;
420 --
421 -- Determine the first 3 additional columns, if any
422 --
423 get_additional_column(l_validation_table.additional_quickpick_columns,1,l_additional_column1,p_additional_column1_title,p_additional_column1_width);
424 get_additional_column(l_validation_table.additional_quickpick_columns,2,l_additional_column2,p_additional_column2_title,p_additional_column2_width);
425 get_additional_column(l_validation_table.additional_quickpick_columns,3,l_additional_column3,p_additional_column3_title,p_additional_column3_width);
426 --
427 -- Build up SQL SELECT statements
428 --
429 p_validation_sql := 'SELECT '||l_validation_table.value_column_name
430 ||' ,'||l_validation_table.meaning_column_name
431 ||' ,'||l_validation_table.id_column_name||' AS id'
432 ||' ,'||NVL(l_additional_column1,'NULL')
433 ||' ,'||NVL(l_additional_column2,'NULL')
434 ||' ,'||NVL(l_additional_column3,'NULL')
435 ||' FROM '||l_validation_table.from_clause
436 ||' '||l_validation_table.where_clause
437 ||' '||l_validation_table.order_clause;
438 p_identification_sql := 'SELECT '||l_validation_table.value_column_name
439 ||' ,'||l_validation_table.meaning_column_name
440 ||' ,'||l_validation_table.id_column_name
441 ||' ,NULL'
442 ||' ,NULL'
443 ||' ,NULL'
444 ||' FROM '||l_validation_table.from_clause
445 ||' '||l_validation_table.where_clause
446 ||' '||l_validation_table.identification_clause;
447 --
448 -- Determine if a meaning column has been specified
449 --
450 p_has_meaning := FALSE;
451 IF (l_validation_table.meaning_column_name <> 'NULL')
452 THEN
453 p_has_meaning := TRUE;
454 END IF;
455 --
456 -- Populate other out parameters
457 --
458 p_id_column_type := l_validation_table.id_column_type;
459 --
460 END LOOP;
461 --
462 END get_table_sql;
463 --
464 -- -----------------------------------------------------------------------------
465 -- |--------------------------< get_independent_sql >--------------------------|
466 -- -----------------------------------------------------------------------------
467 -- {Start of Comments}
468 --
469 -- Description
470 -- This procedure returns the details of the SQL to be used for an independent
471 -- value set.
472 --
473 -- Prerequisites
474 -- None.
475 --
476 -- In Parameters
477 -- Name Reqd Type Description
478 -- p_flex_value_set_id Y number Flexfield value set identifier
479 -- p_flex_value_set_name Y varchar2 Flexfield value set name
483 -- The following out parameters are set:
480 -- p_effective_date Y date Effective date
481 --
482 -- Post Success
484 -- Name Type Description
485 -- p_validation_sql varchar2 SQL statement to be used in
486 -- validation. The flexfield should
487 -- have one of the values returned
488 -- by the statement.
489 -- p_identification_sql varchar2 SQL statement to be used in
490 -- identification. It should return
491 -- one and only one record.
492 -- p_has_meaning boolean Does the SQL contain a meaning
493 -- column?
494 --
495 -- Post Failure
496 -- An error is raised.
497 --
498 -- Access Status
499 -- Internal Development Use Only
500 --
501 -- {End of Comments}
502 -- -----------------------------------------------------------------------------
503 PROCEDURE get_independent_sql
504 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
505 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
506 ,p_effective_date IN DATE
507 ,p_validation_sql OUT NOCOPY VARCHAR2
508 ,p_identification_sql OUT NOCOPY VARCHAR2
509 ,p_has_meaning OUT NOCOPY BOOLEAN
510 )
511 IS
512 --
513 -- Local variables
514 --
515 l_effective_date VARCHAR2(32767);
516 l_validation_sql VARCHAR2(32767);
517 l_identification_sql VARCHAR2(32767);
518 --
519 BEGIN
520 --
521 -- Creating a character string for the SQL, so date should be converted to
522 -- character consistently
523 --
524 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
525 --
526 -- Build up SQL SELECT statements
527 --
528 p_validation_sql := 'SELECT ffv.flex_value_meaning'
529 ||' ,ffv.description'
530 ||' ,ffv.flex_value'
531 ||' ,NULL'
532 ||' ,NULL'
533 ||' ,NULL'
534 ||' FROM fnd_flex_values_vl ffv'
535 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
536 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
537 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
538 ||' AND ffv.enabled_flag = ''Y'''
539 ||' ORDER BY ffv.flex_value';
540 p_identification_sql := 'SELECT ffv.flex_value_meaning'
541 ||' ,ffv.description'
542 ||' ,ffv.flex_value'
543 ||' ,NULL'
544 ||' ,NULL'
545 ||' ,NULL'
546 ||' FROM fnd_flex_values_vl ffv'
547 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
548 -- ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
549 -- ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
550 -- ||' AND ffv.enabled_flag = ''Y'''
551 ||' AND ffv.flex_value = :$FLEX$.'||p_flex_value_set_name;
552 --
553 -- bug 4565427 commented out the conditions that validate the date and enabled flag
554 -- in the above sql so that it retrives the already saved value for a person against an information type.
555
556 -- Independent value sets always have a meaning
557 --
558 p_has_meaning := TRUE;
559 --
560 END get_independent_sql;
561 --
562 -- -----------------------------------------------------------------------------
563 -- |---------------------------< get_dependent_sql >---------------------------|
564 -- -----------------------------------------------------------------------------
565 -- {Start of Comments}
566 --
567 -- Description
568 -- This procedure returns the details of the SQL to be used for a dependent
569 -- value set.
570 --
571 -- Prerequisites
572 -- None.
573 --
574 -- In Parameters
575 -- Name Reqd Type Description
576 -- p_flex_value_set_id Y number Flexfield value set identifier
577 -- p_flex_value_set_name Y varchar2 Flexfield value set name
578 -- p_effective_date Y date Effective date
579 --
580 -- Post Success
581 -- The following out parameters are set:
582 -- Name Type Description
583 -- p_validation_sql varchar2 SQL statement to be used in
584 -- validation. The flexfield should
585 -- have one of the values returned
586 -- by the statement.
587 -- p_identification_sql varchar2 SQL statement to be used in
588 -- identification. It should return
592 --
589 -- one and only one record.
590 -- p_has_meaning boolean Does the SQL contain a meaning
591 -- column?
593 -- Post Failure
594 -- An error is raised.
595 --
596 -- Access Status
597 -- Internal Development Use Only
598 --
599 -- {End of Comments}
600 -- -----------------------------------------------------------------------------
601 PROCEDURE get_dependent_sql
602 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
603 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
604 ,p_effective_date IN DATE
605 ,p_validation_sql OUT NOCOPY VARCHAR2
606 ,p_identification_sql OUT NOCOPY VARCHAR2
607 ,p_has_meaning OUT NOCOPY BOOLEAN
608 )
609 IS
610 --
611 -- Local variables
612 --
613 l_effective_date VARCHAR2(32767);
614 l_validation_sql VARCHAR2(32767);
615 l_identification_sql VARCHAR2(32767);
616 l_parent_value_set_name VARCHAR2(32767);
617 --
618 BEGIN
619 --
620 -- Creating a character string for the SQL, so date should be converted to
621 -- character consistently
622 --
623 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
624 --
625 -- Determine parent value set name
626 --
627 l_parent_value_set_name := ':$FLEX$.'||parent_value_set_name(p_flex_value_set_id);
628 --
629 -- Build up SQL SELECT statements
630 --
631 p_validation_sql := 'SELECT ffv.flex_value_meaning'
632 ||' ,ffv.description'
633 ||' ,ffv.flex_value'
634 ||' ,NULL'
635 ||' ,NULL'
636 ||' ,NULL'
637 ||' FROM fnd_flex_values_vl ffv'
638 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
639 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
640 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
641 ||' AND ffv.enabled_flag = ''Y'''
642 ||' AND ffv.parent_flex_value_low = '||l_parent_value_set_name
643 ||' ORDER BY ffv.flex_value';
644 p_identification_sql := 'SELECT ffv.flex_value_meaning'
645 ||' ,ffv.description'
646 ||' ,ffv.flex_value'
647 ||' ,NULL'
648 ||' ,NULL'
649 ||' ,NULL'
650 ||' FROM fnd_flex_values_vl ffv'
651 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
652 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
653 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
654 ||' AND ffv.enabled_flag = ''Y'''
655 ||' AND ffv.parent_flex_value_low = '||l_parent_value_set_name
656 ||' AND ffv.flex_value = :$FLEX$.'||p_flex_value_set_name;
657 --
658 -- Dependent value sets always have a meaning
659 --
660 p_has_meaning := TRUE;
661 --
662 END get_dependent_sql;
663 --
664 -- -----------------------------------------------------------------------------
665 -- |--------------------------------< get_sql >--------------------------------|
666 -- -----------------------------------------------------------------------------
667 -- {Start of Comments}
668 --
669 -- Description
670 -- This procedure returns the details of the SQL to be used for a value set.
671 --
672 -- Prerequisites
673 -- None.
674 --
675 -- In Parameters
676 -- Name Reqd Type Description
677 -- p_flex_value_set_id Y number Flexfield value set identifier
678 -- p_flex_value_set_name Y varchar2 Flexfield value set name
679 -- p_effective_date Y date Effective date
680 --
681 -- Post Success
682 -- The following out parameters are set:
683 -- Name Type Description
684 -- p_validation_sql varchar2 SQL statement to be used in
685 -- validation. The flexfield should
686 -- have one of the values returned
687 -- by the statement.
688 -- p_identification_sql varchar2 SQL statement to be used in
689 -- identification. It should return
690 -- one and only one record.
691 -- p_id_column_type varchar2 Datatype of the id column
692 -- p_has_meaning boolean Does the SQL contain a meaning
693 -- column?
694 -- p_additional_column1_title varchar2 Additional column title (1)
695 -- p_additional_column1_width varchar2 Additional column width (1)
696 -- p_additional_column2_title varchar2 Additional column title (2)
700 --
697 -- p_additional_column2_width varchar2 Additional column width (2)
698 -- p_additional_column3_title varchar2 Additional column title (3)
699 -- p_additional_column3_width varchar2 Additional column width (3)
701 -- Post Failure
702 -- An error is raised.
703 --
704 -- Access Status
705 -- Internal Development Use Only
706 --
707 -- {End of Comments}
708 -- -----------------------------------------------------------------------------
709 PROCEDURE get_sql
710 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
711 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
712 ,p_validation_type IN fnd_flex_value_sets.validation_type%TYPE
713 ,p_effective_date IN DATE
714 ,p_validation_sql OUT NOCOPY VARCHAR2
715 ,p_identification_sql OUT NOCOPY VARCHAR2
716 ,p_id_column_type OUT NOCOPY VARCHAR2
717 ,p_has_meaning OUT NOCOPY BOOLEAN
718 ,p_additional_column1_title OUT NOCOPY VARCHAR2
719 ,p_additional_column1_width OUT NOCOPY VARCHAR2
720 ,p_additional_column2_title OUT NOCOPY VARCHAR2
721 ,p_additional_column2_width OUT NOCOPY VARCHAR2
722 ,p_additional_column3_title OUT NOCOPY VARCHAR2
723 ,p_additional_column3_width OUT NOCOPY VARCHAR2
724 )
725 IS
726 --
727 -- Local variables
728 --
729 l_table CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'F';
730 l_dependent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'D';
731 l_independent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'I';
732 l_translatable_independent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'X';
733 l_translatable_dependent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'Y';
734 --
735 BEGIN
736 --
737 -- Get details based on validation type
738 --
739 IF (p_validation_type = l_table)
740 THEN
741 get_table_sql
742 (p_flex_value_set_id => p_flex_value_set_id
743 ,p_flex_value_set_name => p_flex_value_set_name
744 ,p_effective_date => p_effective_date
745 ,p_validation_sql => p_validation_sql
746 ,p_identification_sql => p_identification_sql
747 ,p_id_column_type => p_id_column_type
748 ,p_has_meaning => p_has_meaning
749 ,p_additional_column1_title => p_additional_column1_title
750 ,p_additional_column1_width => p_additional_column1_width
751 ,p_additional_column2_title => p_additional_column2_title
752 ,p_additional_column2_width => p_additional_column2_width -- Bug 3904996
753 ,p_additional_column3_title => p_additional_column3_title
754 ,p_additional_column3_width => p_additional_column3_width
755 );
756 ELSIF (p_validation_type IN (l_independent,l_translatable_independent))
757 THEN
758 get_independent_sql
759 (p_flex_value_set_id => p_flex_value_set_id
760 ,p_flex_value_set_name => p_flex_value_set_name
761 ,p_effective_date => p_effective_date
762 ,p_validation_sql => p_validation_sql
763 ,p_identification_sql => p_identification_sql
764 ,p_has_meaning => p_has_meaning
765 );
766 ELSIF (p_validation_type IN (l_dependent,l_translatable_dependent))
767 THEN
768 get_dependent_sql
769 (p_flex_value_set_id => p_flex_value_set_id
770 ,p_flex_value_set_name => p_flex_value_set_name
771 ,p_effective_date => p_effective_date
772 ,p_validation_sql => p_validation_sql
773 ,p_identification_sql => p_identification_sql
774 ,p_has_meaning => p_has_meaning
775 );
776 ELSE
777 p_validation_sql := NULL;
778 p_identification_sql := NULL;
779 p_has_meaning := FALSE;
780 END IF;
781 --
782 END get_sql;
783 --
784 -- -----------------------------------------------------------------------------
785 -- |-------------------------------< value_set >-------------------------------|
786 -- -----------------------------------------------------------------------------
787 FUNCTION value_set
788 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
789 ,p_effective_date IN DATE
790 )
791 RETURN t_value_set
792 IS
793 --
794 -- Local cursors
795 --
796 CURSOR csr_value_sets
797 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
798 )
799 IS
800 SELECT fvs.flex_value_set_id
801 ,fvs.alphanumeric_allowed_flag
802 ,fvs.flex_value_set_name
803 ,fvs.format_type
804 ,fvs.longlist_flag
805 ,fvs.maximum_size
806 ,fvs.maximum_value
807 ,fvs.minimum_value
808 ,fvs.number_precision
809 ,fvs.numeric_mode_enabled_flag
810 ,fvs.uppercase_only_flag
811 ,fvs.validation_type
812 FROM fnd_flex_value_sets fvs
813 WHERE fvs.flex_value_set_id = p_flex_value_set_id;
814 --
815 -- Local variables
816 --
817 l_value_set t_value_set;
818 --
819 BEGIN
820 --
821 FOR l_flex_value_set IN csr_value_sets
822 (p_flex_value_set_id => p_flex_value_set_id
823 )
824 LOOP
825 l_value_set.flex_value_set_id := l_flex_value_set.flex_value_set_id;
826 l_value_set.alphanumeric_allowed_flag := l_flex_value_set.alphanumeric_allowed_flag;
827 l_value_set.flex_value_set_name := l_flex_value_set.flex_value_set_name;
828 l_value_set.format_type := l_flex_value_set.format_type;
829 l_value_set.longlist_flag := l_flex_value_set.longlist_flag;
830 l_value_set.maximum_size := l_flex_value_set.maximum_size;
831 l_value_set.maximum_value := l_flex_value_set.maximum_value;
832 l_value_set.minimum_value := l_flex_value_set.minimum_value;
833 l_value_set.number_precision := l_flex_value_set.number_precision;
834 l_value_set.numeric_mode_enabled_flag := l_flex_value_set.numeric_mode_enabled_flag;
835 l_value_set.uppercase_only_flag := l_flex_value_set.uppercase_only_flag;
836 l_value_set.validation_type := l_flex_value_set.validation_type;
837 get_sql
838 (p_flex_value_set_id => l_flex_value_set.flex_value_set_id
839 ,p_flex_value_set_name => l_flex_value_set.flex_value_set_name
840 ,p_validation_type => l_flex_value_set.validation_type
841 ,p_effective_date => p_effective_date
842 ,p_validation_sql => l_value_set.validation_sql
843 ,p_identification_sql => l_value_set.identification_sql
844 ,p_id_column_type => l_value_set.id_column_type
845 ,p_has_meaning => l_value_set.has_meaning
846 ,p_additional_column1_title => l_value_set.additional_column1_title
847 ,p_additional_column1_width => l_value_set.additional_column1_width
848 ,p_additional_column2_title => l_value_set.additional_column2_title
849 ,p_additional_column2_width => l_value_set.additional_column2_width
850 ,p_additional_column3_title => l_value_set.additional_column3_title
851 ,p_additional_column3_width => l_value_set.additional_column3_width
852 );
853 END LOOP;
854 --
855 RETURN(l_value_set);
856 --
857 END value_set;
858 --
859 END hr_flex_value_set_info;
[Home] [Help]