1 PACKAGE BODY hri_bpl_flex AS
2 /* $Header: hribflx.pkb 120.1 2005/10/05 07:51:02 jtitmas noship $ */
3
4 /******************************************************************************/
5 /* PRIVATE SECTION */
6 /******************************************************************************/
7
8 g_rtn VARCHAR2(5) := '
9 ';
10
11 /******************************************************************************/
12 /* Returns the LOV sql for a table validated value set */
13 /******************************************************************************/
14 PROCEDURE get_table_value_set_sql
15 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
16 ,p_sql_stmt OUT NOCOPY VARCHAR2
17 ,p_distinct_flag IN VARCHAR2) IS
18
19 /* Code from HR_FLEX_VALUE_SET_INFO (hrfvsinf.pkb) */
20 /* Returns details about table validated value sets */
21 CURSOR csr_validation_tables IS
22 SELECT DECODE(NVL(fvt.id_column_type,fvt.value_column_type)
23 ,'D','fnd_date.date_to_canonical('||
24 NVL(fvt.id_column_name,fvt.value_column_name)||')'
25 ,'N','fnd_number.number_to_canonical('||
26 NVL(fvt.id_column_name,fvt.value_column_name)||')'
27 ,NVL(fvt.id_column_name,fvt.value_column_name)
28 ) AS id_column_name
29 ,DECODE(fvt.id_column_name,
30 NULL, DECODE(fvt.meaning_column_type,
31 'D','fnd_date.date_to_displaydate('||fvt.meaning_column_name||')',
32 fvt.meaning_column_name),
33 DECODE(fvt.value_column_type,
34 'D','fnd_date.date_to_displaydate('||fvt.value_column_name||')',
35 fvt.value_column_name)
36 ) AS value_column_name
37 ,fvt.application_table_name AS from_clause
38 ,fvt.additional_where_clause AS where_and_order_clause
39 FROM fnd_flex_validation_tables fvt
40 WHERE fvt.flex_value_set_id = p_flex_value_set_id;
41
42 l_value_column_name VARCHAR2(240);
43 l_id_column_name VARCHAR2(240);
44 l_from_clause VARCHAR2(240);
45 l_where_and_order_clause VARCHAR2(32000);
46 l_where_clause VARCHAR2(32000);
47 l_distinct_clause VARCHAR2(30);
48
49 /* Whether there is an ORDER BY clause entered */
50 l_order_clause_position NUMBER;
51
52 BEGIN
53
54 /* Bug 3387576 - Add distinct to lov sql if required */
55 IF (p_distinct_flag = 'Y') THEN
56 l_distinct_clause := 'DISTINCT ';
57 END IF;
58
59 /* Get the value set validation definition */
60 OPEN csr_validation_tables;
61 FETCH csr_validation_tables INTO
62 l_id_column_name,
63 l_value_column_name,
64 l_from_clause,
65 l_where_and_order_clause;
66 CLOSE csr_validation_tables;
67
68 /* Split out the order by clause if there is one */
69 l_order_clause_position := INSTR(UPPER(l_where_and_order_clause),'ORDER BY');
70 IF (l_order_clause_position > 0) THEN
71 l_where_clause := SUBSTR(l_where_and_order_clause, 1, l_order_clause_position - 1);
72 ELSE
73 l_where_clause := l_where_and_order_clause;
74 END IF;
75
76 /* If the where clause has any bind variables in then discard it */
77 IF (INSTR(l_where_clause, ':') > 0) THEN
78 l_where_clause := NULL;
79 END IF;
80
81 /* If the where clause is not empty and doesn't begins with WHERE then add it */
82 IF (l_where_clause IS NOT NULL AND
83 UPPER(SUBSTR(l_where_clause, 1, 5)) <> 'WHERE') THEN
84 l_where_clause := g_rtn || 'WHERE ' || l_where_clause;
85 /* If the where clause is not empty and begins with WHERE add a line break */
86 ELSIF (l_where_clause IS NOT NULL) THEN
87 l_where_clause := g_rtn || l_where_clause;
88 END IF;
89
90 /* Put together the SQL statement for the table validated LOV */
91 p_sql_stmt :=
92 'SELECT ' || l_distinct_clause || '
93 ' || l_id_column_name || ' id
94 , ' || l_value_column_name || ' value
95 , hr_general.start_of_time effective_start_date
96 , hr_general.end_of_time effective_end_date
97 , ''1'' || ' || l_value_column_name || ' order_by
98 FROM ' ||
99 l_from_clause ||
100 l_where_clause;
101
102 END get_table_value_set_sql;
103
104 /******************************************************************************/
105 /* Formats the sql for a dependent/independent value set */
106 /******************************************************************************/
107 PROCEDURE get_flex_value_set_sql
108 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
109 ,p_sql_stmt OUT NOCOPY VARCHAR2
110 ,p_distinct_flag IN VARCHAR2) IS
111
112 l_distinct_clause VARCHAR2(30);
113
114 BEGIN
115
116 /* Bug 3387576 - Add distinct to lov sql if required */
117 IF (p_distinct_flag = 'Y') THEN
118 l_distinct_clause := 'DISTINCT ';
119 END IF;
120
121 /* Put together the SQL statement for the flex value set */
122 p_sql_stmt :=
123 'SELECT ' || l_distinct_clause || '
124 tab.flex_value id
125 ,tab.flex_value_meaning value
126 ,NVL(tab.start_date_active, hr_general.start_of_time)
127 effective_start_date
128 , NVL(tab.end_date_active, hr_general.end_of_time)
129 effective_end_date
130 ,''1'' || tab.flex_value_meaning order_by
131 FROM fnd_flex_values_vl tab
132 WHERE tab.flex_value_set_id = ' || to_char(p_flex_value_set_id) || '
133 AND tab.enabled_flag = ''Y''';
134
135 END get_flex_value_set_sql;
136
137 /******************************************************************************/
138 /* PUBLIC SECTION */
139 /******************************************************************************/
140
141 /******************************************************************************/
142 /* Returns sql to be used to create a lov based on a value set */
143 /******************************************************************************/
144 PROCEDURE get_value_set_lov_sql
145 (p_flex_value_set_id IN fnd_flex_value_sets.flex_value_set_id%TYPE
146 ,p_sql_stmt OUT NOCOPY VARCHAR2
147 ,p_distinct_flag IN VARCHAR2) IS
148
149 /* Get the value set details */
150 CURSOR csr_value_sets IS
151 SELECT fvs.validation_type
152 FROM fnd_flex_value_sets fvs
153 WHERE fvs.flex_value_set_id = p_flex_value_set_id;
154
155 l_flex_validation_type VARCHAR2(30);
156 l_sql_stmt VARCHAR2(4000);
157
158 BEGIN
159
160 OPEN csr_value_sets;
161 FETCH csr_value_sets INTO l_flex_validation_type;
162 CLOSE csr_value_sets;
163
164 /* Get details based on validation type */
165 IF (l_flex_validation_type = 'F') THEN
166 get_table_value_set_sql
167 (p_flex_value_set_id => p_flex_value_set_id
168 ,p_sql_stmt => p_sql_stmt
169 ,p_distinct_flag => p_distinct_flag);
170 ELSIF (l_flex_validation_type IN ('I','X','D','Y')) THEN
171 get_flex_value_set_sql
172 (p_flex_value_set_id => p_flex_value_set_id
173 ,p_sql_stmt => p_sql_stmt
174 ,p_distinct_flag => p_distinct_flag);
175 END IF;
176
177 END get_value_set_lov_sql;
178
179 END hri_bpl_flex;
[Home] [Help]