eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.FND_VSET

Source


1 PACKAGE BODY fnd_vset AS
2 /* $Header: AFFFVDUB.pls 120.3.12010000.1 2008/07/25 14:14:50 appldev ship $ */
3 
4 
5 CURSOR value_c(valueset IN valueset_r,
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 */