[Home] [Help]
PACKAGE BODY: APPS.FND_VSET
Source
1 PACKAGE BODY fnd_vset AS
5 CURSOR value_c(valueset IN valueset_r,
2 /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
3
4
6 enabled IN fnd_flex_values.enabled_flag%TYPE)
7 RETURN value_dr
8 IS
9 SELECT /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
10 flex_value,
11 flex_value, description,
12 start_date_active, end_date_active,
13 parent_flex_value_low
14 FROM fnd_flex_values_vl
15 WHERE flex_value_set_id = valueset.vsid
16 AND enabled_flag = enabled
17 ORDER BY 1;
18
19
20 CURSOR value_d(valueset IN valueset_r,
21 enabled IN fnd_flex_values.enabled_flag%TYPE)
22 RETURN value_dr
23 IS
24 SELECT /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
25 flex_value,
26 flex_value_meaning, description,
27 start_date_active, end_date_active,
28 parent_flex_value_low
29 FROM fnd_flex_values_vl
30 WHERE flex_value_set_id = valueset.vsid
31 AND enabled_flag = enabled
32 ORDER BY 1;
33
34 debug_mode BOOLEAN; -- := false;
35 cursor_handle INTEGER;
36
37 PROCEDURE debug(state IN BOOLEAN) IS
38 BEGIN
39 debug_mode := state;
40 END;
41
42 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
43 IS
44 i INTEGER;
45 m INTEGER;
46 c INTEGER; -- := 75;
47 BEGIN
48 c := 75;
49 execute immediate ('begin dbms' ||
50 '_output' ||
51 '.enable(1000000); end;');
52 m := Ceil(Length(p_debug)/c);
53 FOR i IN 1..m LOOP
54 execute immediate ('begin dbms' ||
55 '_output' ||
56 '.put_line(''' ||
57 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
58 '''); end;');
59 END LOOP;
60 EXCEPTION
61 WHEN OTHERS THEN
62 NULL;
63 END dbms_debug;
64
65 PROCEDURE dbgprint(s IN VARCHAR2) IS
66 BEGIN
67 IF(debug_mode) THEN
68 dbms_debug(s);
69 END IF;
70 END;
71
72 FUNCTION to_boolean(value IN VARCHAR2) RETURN BOOLEAN
73 IS
74 rv BOOLEAN;
75 BEGIN
76 IF(value in ('Y', 'y')) THEN
77 rv := TRUE;
78 ELSE
79 rv := FALSE;
80 END IF;
81 RETURN rv;
82 END;
83
84 FUNCTION to_flag(value IN BOOLEAN) RETURN VARCHAR2
85 IS
86 rv VARCHAR2(1);
87 BEGIN
88 IF(value) THEN
89 rv := 'Y';
90 ELSE
91 rv := 'N';
92 END IF;
93 RETURN rv;
94 END;
95
96
97 PROCEDURE get_valueset(valueset_id IN fnd_flex_values.flex_value_set_id%TYPE,
98 valueset OUT nocopy valueset_r,
99 format OUT nocopy valueset_dr)
100 IS
101 vset valueset_r;
102 fmt valueset_dr;
103 table_info table_r;
104 BEGIN
105 SELECT /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
106 flex_value_set_id, flex_value_set_name,
107 validation_type
108 INTO vset.vsid, vset.name, vset.validation_type
109 FROM fnd_flex_value_sets
110 WHERE flex_value_set_id = valueset_id;
111
112 SELECT /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
113 format_type, alphanumeric_allowed_flag,
114 uppercase_only_flag, numeric_mode_enabled_flag,
115 maximum_size, maximum_value, minimum_value,
116 longlist_flag
117 INTO fmt.format_type, fmt.alphanumeric_allowed_flag,
118 fmt.uppercase_only_flag, fmt.numeric_mode_flag,
119 fmt.max_size, fmt.max_value, fmt.min_value,
120 fmt.longlist_flag
121 FROM fnd_flex_value_sets
122 WHERE flex_value_set_id = valueset_id;
123
124 fmt.longlist_enabled := (fmt.longlist_flag = 'Y');
125 valueset := vset;
126 IF(vset.validation_type = 'F') THEN -- table validated
127 SELECT /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
128 application_table_name, id_column_name, id_column_type,
129 value_column_name, meaning_column_name,
130 additional_where_clause,
131 start_date_column_name, end_date_column_name
132 INTO table_info
133 FROM fnd_flex_validation_tables
134 WHERE flex_value_set_id = vset.vsid;
135 valueset.table_info := table_info;
136 fmt.has_id := (table_info.id_column_name IS NOT NULL);
137 fmt.has_meaning:= (table_info.meaning_column_name IS NOT NULL);
138 ELSE
139 fmt.has_id := FALSE;
140 fmt.has_meaning:= TRUE;
141 END IF;
142 format := fmt;
143 dbgprint('returning valueset:' || vset.name);
144 END;
145
146
147 PROCEDURE make_cursor(valueset IN valueset_r)
148 IS
149 sqlstring VARCHAR2(32767);
150 cols VARCHAR2(1500);
151 dummy_vc VARCHAR2(1);
152 dummy_num NUMBER;
153 dummy_int INTEGER;
154 dummy_date DATE;
155 table_info table_r;
156 /* these are from the tables - should really be doing a select */
157 max_id_size NUMBER; -- := 150;
158 max_val_size NUMBER; -- := 150;
159 max_meaning_size NUMBER; -- := 240;
160 BEGIN
161 max_id_size := 150;
162 max_val_size := 150;
163 max_meaning_size := 240;
164 dbgprint('make_cursor: making new cursor (table) ...');
165 table_info := valueset.table_info;
166 cols :=
167 table_info.start_date_column_name || ', ' ||
168 table_info.end_date_column_name || ', ' ||
169 table_info.value_column_name;
170 IF(table_info.meaning_column_name IS NOT NULL) THEN
171 dbgprint(' using meaning column since it is not null ('
172 || table_info.meaning_column_name || ')');
173 cols := cols || ' , ' || table_info.meaning_column_name || ' ' ||
174 'DESCRIPTION';
175 ELSE
176 cols := cols || ', NULL ';
177 END IF;
178 IF (table_info.id_column_name IS NOT NULL) THEN
179 dbgprint(' using id column since it is not null ('
180 || table_info.id_column_name || ')');
181
182 --
183 -- to_char() conversion function is defined only for
184 -- DATE and NUMBER datatypes.
185 --
186 IF (table_info.id_column_type IN ('D', 'N')) THEN
187 dbgprint(' using to_char(id_column_name). '
188 || 'id_column_type :('||table_info.id_column_type||')');
189 cols := cols || ' , To_char(' || table_info.id_column_name || ')';
190 ELSE
191 dbgprint(' NOT using to_char(id_column_name). '
192 || 'id_column_type :('||table_info.id_column_type||')');
193 cols := cols || ' , ' || table_info.id_column_name || ' ' ||
194 'ID_COL';
195 END IF;
196 ELSE
197 cols := cols || ', NULL ';
198 END IF;
199 sqlstring := 'select ' || cols ||
200 ' from ' || table_info.table_name ||
201 ' ' || table_info.where_clause;
202 dbgprint(' sql stmt = ' || sqlstring);
203 cursor_handle := dbms_sql.open_cursor;
204 dbms_sql.parse(cursor_handle, sqlstring, dbms_sql.native);
205 dbms_sql.define_column(cursor_handle, 1, dummy_date);
206 dbms_sql.define_column(cursor_handle, 2, dummy_date);
207 dbms_sql.define_column(cursor_handle, 3, dummy_vc, max_val_size);
208 dbms_sql.define_column(cursor_handle, 4, dummy_vc, max_meaning_size);
209 dbms_sql.define_column(cursor_handle, 5, dummy_vc, max_id_size);
210 dummy_int := dbms_sql.execute(cursor_handle);
211 END;
212
213 PROCEDURE get_value_init(valueset IN valueset_r,
214 enabled_only IN BOOLEAN)
215 IS
216 BEGIN
217 dbgprint('get_value_init: opening cursor...');
218 IF(valueset.validation_type in ('I', 'D')) THEN
219 IF value_c%isopen THEN
220 CLOSE value_c;
221 END IF;
222 OPEN value_c(valueset, to_flag(enabled_only));
223 ELSIF(valueset.validation_type in ('X', 'Y')) THEN
224 IF value_d%isopen THEN
225 CLOSE value_d;
226 END IF;
227 OPEN value_d(valueset, to_flag(enabled_only));
228 ELSIF(valueset.validation_type = 'F') THEN
229 make_cursor(valueset);
230 END IF;
231 dbgprint('get_value_init: done.');
232 END;
233
234
235 PROCEDURE get_value(valueset IN valueset_r,
236 rowcount OUT nocopy NUMBER,
237 found OUT nocopy BOOLEAN,
238 value OUT nocopy value_dr)
239 IS
240 value_i value_dr;
241 BEGIN
242 dbgprint('get_value: getting a value...');
243 IF(valueset.validation_type in ('I', 'D')) THEN
244 dbgprint('get_value: doing fetch (indep, or dep) ...');
245 FETCH value_c INTO value_i;
246 dbgprint('get_value: assigning values (indep, or dep) ...');
247 value := value_i;
248 found := value_c%found;
249 ELSIF(valueset.validation_type in ('X', 'Y')) THEN
250 dbgprint('get_value: doing fetch (trans indep, or dep) ...');
251 FETCH value_d INTO value_i;
252 dbgprint('get_value: assigning values (trans indep,or dep) ...');
253 value := value_i;
254 found := value_d%found;
255 ELSIF(valueset.validation_type = 'F') THEN
256 dbgprint('get_value: doing fetch (table) ...');
257 found := (dbms_sql.fetch_rows(cursor_handle) > 0);
258 dbgprint('get_value: assigning values (table) ...');
259 dbms_sql.column_value(cursor_handle, 1, value.start_date_active);
260 dbms_sql.column_value(cursor_handle, 2, value.end_date_active);
261 dbms_sql.column_value(cursor_handle, 3, value.value);
262 dbms_sql.column_value(cursor_handle, 4, value.meaning);
263 dbms_sql.column_value(cursor_handle, 5, value.id);
264 END IF;
265 rowcount := NULL;
266 dbgprint('get_value: done.');
267 END;
268
269
270 PROCEDURE get_value_end(valueset IN valueset_r)
271 IS
272 BEGIN
273 dbgprint('get_value_end: closing cursor...');
274 IF(valueset.validation_type in ('I', 'D')) THEN
275 IF value_c%isopen THEN
276 CLOSE value_c;
277 END IF;
278 ELSIF(valueset.validation_type in ('X', 'Y')) THEN
279 IF value_d%isopen THEN
280 CLOSE value_d;
281 END IF;
282 ELSIF(valueset.validation_type = 'F') THEN
283 IF(dbms_sql.is_open(cursor_handle)) THEN
284 dbms_sql.close_cursor(cursor_handle);
285 END IF;
286 END IF;
287 dbgprint('get_value_end: done.');
288 END;
289
290 FUNCTION To_str(val BOOLEAN) RETURN VARCHAR2 IS
291 rv VARCHAR2(100);
292 BEGIN
293 IF(val) THEN
294 rv := 'TRUE';
295 ELSE
296 rv := 'FALSE';
297 END IF;
298 RETURN rv;
299 END;
300
301 PROCEDURE test(vsid IN NUMBER) IS
302 vset valueset_r;
303 fmt valueset_dr;
304 found BOOLEAN;
305 row NUMBER;
306 value value_dr;
307 BEGIN
308 get_valueset(vsid, vset, fmt);
309 get_value_init(vset, TRUE);
310 dbms_debug('valueset=' || vset.name);
311 dbms_debug('type=' || vset.validation_type);
312 dbms_debug('has id=' || To_str(fmt.has_id));
313 dbms_debug('has meaning=' || To_str(fmt.has_meaning));
314 get_value(vset, row, found, value);
315 WHILE(found) LOOP
316 dbms_debug('value=' || value.value ||
317 '; meaning=' || value.meaning ||
318 '; id=' || value.id ||
319 '; dates=' || To_char(value.start_date_active) ||
320 '/' || To_char(value.end_date_active) ||
321 '; ind value=' || value.parent_flex_value_low);
322 get_value(vset, row, found, value);
323 END LOOP;
324 get_value_end(vset);
325 END;
326
327 PROCEDURE test_independent IS
328 BEGIN
329 test(102429);
330 END;
331
332 PROCEDURE test_table IS
333 BEGIN
334 -- test(103473); /* applications */
335 test(103473); /* fnd_flex_values */
336 END;
337
338 BEGIN
339 debug_mode := FALSE;
340
341 END fnd_vset; /* end package */