1 PACKAGE BODY hr_flex_value_set_info
2 /* $Header: hrfvsinf.pkb 120.4 2012/02/07 10:32:30 shpatro ship $ */
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
367 --
368 --
369 -- Local variables
370 --
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 --
382 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
379 -- Creating a character string for the SQL, so date should be converted to
380 -- character consistently
381 --
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 /* changes for bug#13626638 starts here */
391
392 l_order_clause_position := DBMS_LOB.INSTR(UPPER(l_validation_table.where_and_order_clause),'ORDER BY');
393
394 /* changes for bug#13626638 ends here */
395
396 IF (l_order_clause_position > 0)
397 THEN
398 --
399 -- ORDER BY clause already specified, split into the constituent WHERE and
400 -- ORDER BY statements
401 --
402 /* changes for bug#13626638 starts here */
403
404 l_validation_table.where_clause := DBMS_LOB.SUBSTR(l_validation_table.where_and_order_clause,l_order_clause_position -1,1);
405 l_validation_table.order_clause := DBMS_LOB.SUBSTR(l_validation_table.where_and_order_clause,length(l_validation_table.where_and_order_clause),l_order_clause_position);
406
407 /* changes for bug#13626638 ends here */
408 --
409 ELSE
410 --
411 -- ORDER BY clause NOT specified, so add the default clause
412 --
413 l_validation_table.where_clause := l_validation_table.where_and_order_clause;
414 l_validation_table.order_clause := 'ORDER BY 1';
415 --
416 END IF;
417 --
418 -- Add effective date validation to WHERE clause
419 --
420 IF (l_validation_table.where_clause IS NULL)
421 THEN
422 l_validation_table.where_clause := 'WHERE '||l_effective_date||' BETWEEN NVL('||l_validation_table.start_date_column_name||','||l_effective_date||')'
423 ||' AND NVL('||l_validation_table.end_date_column_name||','||l_effective_date||')';
424 ELSE
425 l_validation_table.where_clause := l_validation_table.where_clause
426 ||' AND '||l_effective_date||' BETWEEN NVL('||l_validation_table.start_date_column_name||','||l_effective_date||')'
427 ||' AND NVL('||l_validation_table.end_date_column_name||','||l_effective_date||')';
428 END IF;
429 --
430 -- Determine the first 3 additional columns, if any
431 --
432 get_additional_column(l_validation_table.additional_quickpick_columns,1,l_additional_column1,p_additional_column1_title,p_additional_column1_width);
433 get_additional_column(l_validation_table.additional_quickpick_columns,2,l_additional_column2,p_additional_column2_title,p_additional_column2_width);
434 get_additional_column(l_validation_table.additional_quickpick_columns,3,l_additional_column3,p_additional_column3_title,p_additional_column3_width);
435 --
436 -- Build up SQL SELECT statements
437 --
438 p_validation_sql := 'SELECT '||l_validation_table.value_column_name
439 ||' ,'||l_validation_table.meaning_column_name
440 ||' ,'||l_validation_table.id_column_name||' AS id'
441 ||' ,'||NVL(l_additional_column1,'NULL')
442 ||' ,'||NVL(l_additional_column2,'NULL')
443 ||' ,'||NVL(l_additional_column3,'NULL')
444 ||' FROM '||l_validation_table.from_clause
445 ||' '||l_validation_table.where_clause
446 ||' '||l_validation_table.order_clause;
447 p_identification_sql := 'SELECT '||l_validation_table.value_column_name
448 ||' ,'||l_validation_table.meaning_column_name
449 ||' ,'||l_validation_table.id_column_name
450 ||' ,NULL'
451 ||' ,NULL'
452 ||' ,NULL'
453 ||' FROM '||l_validation_table.from_clause
454 ||' '||l_validation_table.where_clause
455 ||' '||l_validation_table.identification_clause;
456 --
457 -- Determine if a meaning column has been specified
458 --
459 p_has_meaning := FALSE;
460 IF (l_validation_table.meaning_column_name <> 'NULL')
461 THEN
462 p_has_meaning := TRUE;
463 END IF;
464 --
465 -- Populate other out parameters
466 --
467 p_id_column_type := l_validation_table.id_column_type;
468 --
469 END LOOP;
470 --
471 END get_table_sql;
472 --
473 -- -----------------------------------------------------------------------------
474 -- |--------------------------< get_independent_sql >--------------------------|
475 -- -----------------------------------------------------------------------------
476 -- {Start of Comments}
477 --
478 -- Description
479 -- This procedure returns the details of the SQL to be used for an independent
480 -- value set.
481 --
482 -- Prerequisites
483 -- None.
484 --
485 -- In Parameters
486 -- Name Reqd Type Description
487 -- p_flex_value_set_id Y number Flexfield value set identifier
488 -- p_flex_value_set_name Y varchar2 Flexfield value set name
489 -- p_effective_date Y date Effective date
490 --
491 -- Post Success
492 -- The following out parameters are set:
493 -- Name Type Description
494 -- p_validation_sql varchar2 SQL statement to be used in
495 -- validation. The flexfield should
496 -- have one of the values returned
500 -- one and only one record.
497 -- by the statement.
498 -- p_identification_sql varchar2 SQL statement to be used in
499 -- identification. It should return
501 -- p_has_meaning boolean Does the SQL contain a meaning
502 -- column?
503 --
504 -- Post Failure
505 -- An error is raised.
506 --
507 -- Access Status
508 -- Internal Development Use Only
509 --
510 -- {End of Comments}
511 -- -----------------------------------------------------------------------------
512 PROCEDURE get_independent_sql
513 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
514 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
515 ,p_effective_date IN DATE
516 ,p_validation_sql OUT NOCOPY VARCHAR2
517 ,p_identification_sql OUT NOCOPY VARCHAR2
518 ,p_has_meaning OUT NOCOPY BOOLEAN
519 )
520 IS
521 --
522 -- Local variables
523 --
524 l_effective_date VARCHAR2(32767);
525 l_validation_sql VARCHAR2(32767);
526 l_identification_sql VARCHAR2(32767);
527 --
528 BEGIN
529 --
530 -- Creating a character string for the SQL, so date should be converted to
531 -- character consistently
532 --
533 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
534 --
535 -- Build up SQL SELECT statements
536 --
537 p_validation_sql := 'SELECT ffv.flex_value_meaning'
538 ||' ,ffv.description'
539 ||' ,ffv.flex_value'
540 ||' ,NULL'
541 ||' ,NULL'
542 ||' ,NULL'
543 ||' FROM fnd_flex_values_vl ffv'
544 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
545 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
546 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
547 ||' AND ffv.enabled_flag = ''Y'''
548 ||' ORDER BY ffv.flex_value';
549 p_identification_sql := 'SELECT ffv.flex_value_meaning'
550 ||' ,ffv.description'
551 ||' ,ffv.flex_value'
552 ||' ,NULL'
553 ||' ,NULL'
554 ||' ,NULL'
555 ||' FROM fnd_flex_values_vl ffv'
556 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
557 -- ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
558 -- ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
559 -- ||' AND ffv.enabled_flag = ''Y'''
560 ||' AND ffv.flex_value = :$FLEX$.'||p_flex_value_set_name;
561 --
562 -- bug 4565427 commented out the conditions that validate the date and enabled flag
563 -- in the above sql so that it retrives the already saved value for a person against an information type.
564
565 -- Independent value sets always have a meaning
566 --
567 p_has_meaning := TRUE;
568 --
569 END get_independent_sql;
570 --
571 -- -----------------------------------------------------------------------------
572 -- |---------------------------< get_dependent_sql >---------------------------|
573 -- -----------------------------------------------------------------------------
574 -- {Start of Comments}
575 --
576 -- Description
577 -- This procedure returns the details of the SQL to be used for a dependent
578 -- value set.
579 --
580 -- Prerequisites
581 -- None.
582 --
583 -- In Parameters
584 -- Name Reqd Type Description
585 -- p_flex_value_set_id Y number Flexfield value set identifier
586 -- p_flex_value_set_name Y varchar2 Flexfield value set name
587 -- p_effective_date Y date Effective date
588 --
589 -- Post Success
590 -- The following out parameters are set:
591 -- Name Type Description
592 -- p_validation_sql varchar2 SQL statement to be used in
593 -- validation. The flexfield should
594 -- have one of the values returned
595 -- by the statement.
596 -- p_identification_sql varchar2 SQL statement to be used in
597 -- identification. It should return
598 -- one and only one record.
599 -- p_has_meaning boolean Does the SQL contain a meaning
600 -- column?
601 --
602 -- Post Failure
603 -- An error is raised.
604 --
605 -- Access Status
606 -- Internal Development Use Only
607 --
608 -- {End of Comments}
609 -- -----------------------------------------------------------------------------
610 PROCEDURE get_dependent_sql
611 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
612 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
613 ,p_effective_date IN DATE
614 ,p_validation_sql OUT NOCOPY VARCHAR2
615 ,p_identification_sql OUT NOCOPY VARCHAR2
619 --
616 ,p_has_meaning OUT NOCOPY BOOLEAN
617 )
618 IS
620 -- Local variables
621 --
622 l_effective_date VARCHAR2(32767);
623 l_validation_sql VARCHAR2(32767);
624 l_identification_sql VARCHAR2(32767);
625 l_parent_value_set_name VARCHAR2(32767);
626 --
627 BEGIN
628 --
629 -- Creating a character string for the SQL, so date should be converted to
630 -- character consistently
631 --
632 l_effective_date := 'fnd_date.canonical_to_date('''||fnd_date.date_to_canonical(p_effective_date)||''')';
633 --
634 -- Determine parent value set name
635 --
636 l_parent_value_set_name := ':$FLEX$.'||parent_value_set_name(p_flex_value_set_id);
637 --
638 -- Build up SQL SELECT statements
639 --
640 p_validation_sql := 'SELECT ffv.flex_value_meaning'
641 ||' ,ffv.description'
642 ||' ,ffv.flex_value'
643 ||' ,NULL'
644 ||' ,NULL'
645 ||' ,NULL'
646 ||' FROM fnd_flex_values_vl ffv'
647 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
648 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
649 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
650 ||' AND ffv.enabled_flag = ''Y'''
651 ||' AND ffv.parent_flex_value_low = '||l_parent_value_set_name
652 ||' ORDER BY ffv.flex_value';
653 p_identification_sql := 'SELECT ffv.flex_value_meaning'
654 ||' ,ffv.description'
655 ||' ,ffv.flex_value'
656 ||' ,NULL'
657 ||' ,NULL'
658 ||' ,NULL'
659 ||' FROM fnd_flex_values_vl ffv'
660 ||' WHERE ffv.flex_value_set_id = fnd_number.canonical_to_number('||fnd_number.number_to_canonical(p_flex_value_set_id)||')'
661 ||' AND '||l_effective_date||' BETWEEN NVL(ffv.start_date_active,'||l_effective_date||')'
662 ||' AND NVL(ffv.end_date_active,'||l_effective_date||')'
663 ||' AND ffv.enabled_flag = ''Y'''
664 ||' AND ffv.parent_flex_value_low = '||l_parent_value_set_name
665 ||' AND ffv.flex_value = :$FLEX$.'||p_flex_value_set_name;
666 --
667 -- Dependent value sets always have a meaning
668 --
669 p_has_meaning := TRUE;
670 --
671 END get_dependent_sql;
672 --
673 -- -----------------------------------------------------------------------------
674 -- |--------------------------------< get_sql >--------------------------------|
675 -- -----------------------------------------------------------------------------
676 -- {Start of Comments}
677 --
678 -- Description
679 -- This procedure returns the details of the SQL to be used for a value set.
680 --
681 -- Prerequisites
682 -- None.
683 --
684 -- In Parameters
685 -- Name Reqd Type Description
686 -- p_flex_value_set_id Y number Flexfield value set identifier
687 -- p_flex_value_set_name Y varchar2 Flexfield value set name
688 -- p_effective_date Y date Effective date
689 --
690 -- Post Success
691 -- The following out parameters are set:
692 -- Name Type Description
693 -- p_validation_sql varchar2 SQL statement to be used in
694 -- validation. The flexfield should
695 -- have one of the values returned
696 -- by the statement.
697 -- p_identification_sql varchar2 SQL statement to be used in
698 -- identification. It should return
699 -- one and only one record.
700 -- p_id_column_type varchar2 Datatype of the id column
701 -- p_has_meaning boolean Does the SQL contain a meaning
702 -- column?
703 -- p_additional_column1_title varchar2 Additional column title (1)
704 -- p_additional_column1_width varchar2 Additional column width (1)
705 -- p_additional_column2_title varchar2 Additional column title (2)
706 -- p_additional_column2_width varchar2 Additional column width (2)
707 -- p_additional_column3_title varchar2 Additional column title (3)
708 -- p_additional_column3_width varchar2 Additional column width (3)
709 --
710 -- Post Failure
711 -- An error is raised.
712 --
713 -- Access Status
714 -- Internal Development Use Only
715 --
716 -- {End of Comments}
717 -- -----------------------------------------------------------------------------
718 PROCEDURE get_sql
719 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
720 ,p_flex_value_set_name IN fnd_flex_value_sets.flex_value_set_name%TYPE
721 ,p_validation_type IN fnd_flex_value_sets.validation_type%TYPE
722 ,p_effective_date IN DATE
723 ,p_validation_sql OUT NOCOPY VARCHAR2
724 ,p_identification_sql OUT NOCOPY VARCHAR2
725 ,p_id_column_type OUT NOCOPY VARCHAR2
726 ,p_has_meaning OUT NOCOPY BOOLEAN
727 ,p_additional_column1_title OUT NOCOPY VARCHAR2
728 ,p_additional_column1_width OUT NOCOPY VARCHAR2
729 ,p_additional_column2_title OUT NOCOPY VARCHAR2
733 )
730 ,p_additional_column2_width OUT NOCOPY VARCHAR2
731 ,p_additional_column3_title OUT NOCOPY VARCHAR2
732 ,p_additional_column3_width OUT NOCOPY VARCHAR2
734 IS
735 --
736 -- Local variables
737 --
738 l_table CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'F';
739 l_dependent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'D';
740 l_independent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'I';
741 l_translatable_independent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'X';
742 l_translatable_dependent CONSTANT fnd_flex_value_sets.validation_type%TYPE := 'Y';
743 --
744 BEGIN
745 --
746 -- Get details based on validation type
747 --
748 IF (p_validation_type = l_table)
749 THEN
750 get_table_sql
751 (p_flex_value_set_id => p_flex_value_set_id
752 ,p_flex_value_set_name => p_flex_value_set_name
753 ,p_effective_date => p_effective_date
754 ,p_validation_sql => p_validation_sql
755 ,p_identification_sql => p_identification_sql
756 ,p_id_column_type => p_id_column_type
757 ,p_has_meaning => p_has_meaning
758 ,p_additional_column1_title => p_additional_column1_title
759 ,p_additional_column1_width => p_additional_column1_width
760 ,p_additional_column2_title => p_additional_column2_title
761 ,p_additional_column2_width => p_additional_column2_width -- Bug 3904996
762 ,p_additional_column3_title => p_additional_column3_title
763 ,p_additional_column3_width => p_additional_column3_width
764 );
765 ELSIF (p_validation_type IN (l_independent,l_translatable_independent))
766 THEN
767 get_independent_sql
768 (p_flex_value_set_id => p_flex_value_set_id
769 ,p_flex_value_set_name => p_flex_value_set_name
770 ,p_effective_date => p_effective_date
771 ,p_validation_sql => p_validation_sql
772 ,p_identification_sql => p_identification_sql
773 ,p_has_meaning => p_has_meaning
774 );
775 ELSIF (p_validation_type IN (l_dependent,l_translatable_dependent))
776 THEN
777 get_dependent_sql
778 (p_flex_value_set_id => p_flex_value_set_id
779 ,p_flex_value_set_name => p_flex_value_set_name
780 ,p_effective_date => p_effective_date
781 ,p_validation_sql => p_validation_sql
782 ,p_identification_sql => p_identification_sql
783 ,p_has_meaning => p_has_meaning
784 );
785 ELSE
786 p_validation_sql := NULL;
787 p_identification_sql := NULL;
788 p_has_meaning := FALSE;
789 END IF;
790 --
791 END get_sql;
792 --
793 -- -----------------------------------------------------------------------------
794 -- |-------------------------------< value_set >-------------------------------|
795 -- -----------------------------------------------------------------------------
796 FUNCTION value_set
797 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
798 ,p_effective_date IN DATE
799 )
800 RETURN t_value_set
801 IS
802 --
803 -- Local cursors
804 --
805 CURSOR csr_value_sets
806 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
807 )
808 IS
809 SELECT fvs.flex_value_set_id
810 ,fvs.alphanumeric_allowed_flag
811 ,fvs.flex_value_set_name
812 ,fvs.format_type
813 ,fvs.longlist_flag
814 ,fvs.maximum_size
815 ,fvs.maximum_value
816 ,fvs.minimum_value
817 ,fvs.number_precision
818 ,fvs.numeric_mode_enabled_flag
819 ,fvs.uppercase_only_flag
820 ,fvs.validation_type
821 FROM fnd_flex_value_sets fvs
822 WHERE fvs.flex_value_set_id = p_flex_value_set_id;
823 --
824 -- Local variables
825 --
826 l_value_set t_value_set;
827 --
828 BEGIN
829 --
830 FOR l_flex_value_set IN csr_value_sets
831 (p_flex_value_set_id => p_flex_value_set_id
832 )
833 LOOP
834 l_value_set.flex_value_set_id := l_flex_value_set.flex_value_set_id;
835 l_value_set.alphanumeric_allowed_flag := l_flex_value_set.alphanumeric_allowed_flag;
836 l_value_set.flex_value_set_name := l_flex_value_set.flex_value_set_name;
837 l_value_set.format_type := l_flex_value_set.format_type;
838 l_value_set.longlist_flag := l_flex_value_set.longlist_flag;
839 l_value_set.maximum_size := l_flex_value_set.maximum_size;
840 l_value_set.maximum_value := l_flex_value_set.maximum_value;
841 l_value_set.minimum_value := l_flex_value_set.minimum_value;
842 l_value_set.number_precision := l_flex_value_set.number_precision;
843 l_value_set.numeric_mode_enabled_flag := l_flex_value_set.numeric_mode_enabled_flag;
844 l_value_set.uppercase_only_flag := l_flex_value_set.uppercase_only_flag;
845 l_value_set.validation_type := l_flex_value_set.validation_type;
846 get_sql
847 (p_flex_value_set_id => l_flex_value_set.flex_value_set_id
848 ,p_flex_value_set_name => l_flex_value_set.flex_value_set_name
849 ,p_validation_type => l_flex_value_set.validation_type
850 ,p_effective_date => p_effective_date
851 ,p_validation_sql => l_value_set.validation_sql
852 ,p_identification_sql => l_value_set.identification_sql
853 ,p_id_column_type => l_value_set.id_column_type
857 ,p_additional_column2_title => l_value_set.additional_column2_title
854 ,p_has_meaning => l_value_set.has_meaning
855 ,p_additional_column1_title => l_value_set.additional_column1_title
856 ,p_additional_column1_width => l_value_set.additional_column1_width
858 ,p_additional_column2_width => l_value_set.additional_column2_width
859 ,p_additional_column3_title => l_value_set.additional_column3_title
860 ,p_additional_column3_width => l_value_set.additional_column3_width
861 );
862 END LOOP;
863 --
864 RETURN(l_value_set);
865 --
866 END value_set;
867 --
868 END hr_flex_value_set_info;
[Home] [Help]