[Home] [Help]
PACKAGE BODY: APPS.FND_DFLEX
Source
1 PACKAGE BODY fnd_dflex AS
2 /* $Header: AFFFDDUB.pls 120.2.12010000.1 2008/07/25 14:13:42 appldev ship $ */
3
4
5 -- @param en_flag null or not null, null means ignore
6 CURSOR segment_c(context IN context_r,
7 en_flag IN VARCHAR2 DEFAULT NULL) IS
8 SELECT /* $Header: AFFFDDUB.pls 120.2.12010000.1 2008/07/25 14:13:42 appldev ship $ */
9 application_column_name,
10 description,
11 enabled_flag,
12 required_flag,
13 end_user_column_name,
14 column_seq_num,
15 display_flag,
16 display_size,
17 form_left_prompt,
18 form_above_prompt,
19 flex_value_set_id,
20 default_type,
21 default_value
22 FROM fnd_descr_flex_col_usage_vl
23 WHERE application_id = context.flexfield.application_id
24 AND descriptive_flexfield_name = context.flexfield.flexfield_name
25 AND descriptive_flex_context_code = context.context_code
26 AND (en_flag IS NULL OR enabled_flag = 'Y')
27 ORDER BY column_seq_num;
28
29 PROCEDURE dbms_debug(p_debug IN VARCHAR2)
30 IS
31 i INTEGER;
32 m INTEGER;
33 c INTEGER := 75;
34 BEGIN
35 execute immediate ('begin dbms' ||
36 '_output' ||
37 '.enable(1000000); end;');
38 m := Ceil(Length(p_debug)/c);
39 FOR i IN 1..m LOOP
40 execute immediate ('begin dbms' ||
41 '_output' ||
42 '.put_line(''' ||
43 REPLACE(Substr(p_debug, 1+c*(i-1), c), '''', '''''') ||
44 '''); end;');
45 END LOOP;
46 EXCEPTION
47 WHEN OTHERS THEN
48 NULL;
49 END dbms_debug;
50
51 FUNCTION to_boolean(value IN VARCHAR2) RETURN BOOLEAN
52 IS
53 rv BOOLEAN;
54 BEGIN
55 IF(value in ('Y', 'y')) THEN
56 rv := TRUE;
57 ELSE
58 rv := FALSE;
59 END IF;
60 RETURN rv;
61 END;
62
63 /* returns information about the flexfield */
64 PROCEDURE get_flexfield(appl_short_name IN fnd_application.application_short_name%TYPE,
65 flexfield_name IN fnd_descriptive_flexs_vl.descriptive_flexfield_name%TYPE,
66 flexfield OUT nocopy dflex_r,
67 flexinfo OUT nocopy dflex_dr)
68 IS
69 ffld dflex_r;
70 dflex dflex_dr;
71 BEGIN
72 SELECT /* $Header: AFFFDDUB.pls 120.2.12010000.1 2008/07/25 14:13:42 appldev ship $ */
73 a.application_id, df.descriptive_flexfield_name
74 INTO ffld
75 FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
76 WHERE a.application_short_name = appl_short_name
77 AND a.application_id = df.application_id
78 AND df.descriptive_flexfield_name = flexfield_name;
79
80 SELECT /* $Header: AFFFDDUB.pls 120.2.12010000.1 2008/07/25 14:13:42 appldev ship $ */
81 df.title, df.application_table_name, a.application_short_name,
82 df.description, df.concatenated_segment_delimiter,
83 df.default_context_field_name, df.default_context_value,
84 protected_flag,
85 form_context_prompt, context_column_name
86 INTO dflex
87 FROM fnd_application_vl a, fnd_descriptive_flexs_vl df
88 WHERE df.application_id = ffld.application_id
89 AND df.descriptive_flexfield_name = ffld.flexfield_name
90 AND a.application_id = df.table_application_id;
91 flexfield := ffld;
92 flexinfo := dflex;
93 END;
94
95
96 /* returns the contexts in a flexfield */
97 PROCEDURE get_contexts(flexfield IN dflex_r,
98 contexts OUT nocopy contexts_dr)
99 IS
100 CURSOR context_c IS
101 SELECT /* $Header: AFFFDDUB.pls 120.2.12010000.1 2008/07/25 14:13:42 appldev ship $ */
102 descriptive_flex_context_code, descriptive_flex_context_name,
103 description, global_flag, enabled_flag
104 FROM fnd_descr_flex_contexts_vl
105 WHERE application_id = flexfield.application_id
106 AND descriptive_flexfield_name = flexfield.flexfield_name
107 ORDER BY descriptive_flex_context_code;
108 i BINARY_INTEGER := 0;
109 rv contexts_dr;
110 BEGIN
111 rv.global_context := 0;
112 FOR context_rec IN context_c LOOP
113 i := i + 1;
114 rv.context_code(i) := context_rec.descriptive_flex_context_code;
115 rv.context_name(i) := context_rec.descriptive_flex_context_name;
116 rv.context_description(i) := context_rec.description;
117 rv.is_global(i) := to_boolean(context_rec.global_flag);
118 rv.is_enabled(i) := to_boolean(context_rec.enabled_flag);
119 IF(rv.is_global(i) AND rv.is_enabled(i)) THEN
120 rv.global_context := i;
121 END IF;
122 END LOOP;
123 rv.ncontexts := i;
124 contexts := rv;
125 END;
126
127
128 /* since we don't have arrays of structures, provide a way to make a context structure */
129 FUNCTION make_context(flexfield IN dflex_r,
130 context_code IN fnd_descr_flex_contexts.descriptive_flex_context_code%TYPE)
131 RETURN context_r
132 IS
133 rv context_r;
134 BEGIN
135 rv.flexfield := flexfield;
136 rv.context_code := context_code;
137 RETURN rv;
138 END;
139
140
141
142 /* returns information about all the segments in a particular context */
143 PROCEDURE get_segments(context IN context_r,
144 segments OUT nocopy segments_dr,
145 enabled_only IN BOOLEAN)
146 IS
147 i BINARY_INTEGER := 0;
148 rv segments_dr;
149 en_flag VARCHAR2(1);
150 BEGIN
151 IF(enabled_only) THEN
152 en_flag := 'Y';
153 END IF;
154 FOR segment_rec IN segment_c(context, en_flag) LOOP
155 i := i + 1;
156 rv.application_column_name(i) := segment_rec.application_column_name;
157 rv.segment_name(i) := segment_rec.end_user_column_name;
158 rv.sequence(i) := segment_rec.column_seq_num;
159 rv.is_displayed(i) := to_boolean(segment_rec.display_flag);
160 rv.display_size(i) := segment_rec.display_size;
161 rv.row_prompt(i) := segment_rec.form_left_prompt;
162 rv.column_prompt(i) := segment_rec.form_above_prompt;
163 rv.is_required(i) := to_boolean(segment_rec.required_flag);
164 rv.is_enabled(i) := to_boolean(segment_rec.enabled_flag);
165 rv.description(i) := segment_rec.description;
166 rv.value_set(i) := segment_rec.flex_value_set_id;
167 rv.default_type(i) := segment_rec.default_type;
168 rv.default_value(i) := segment_rec.default_value;
169 END LOOP;
170 rv.nsegments := i;
171 segments := rv;
172 END;
173
174
175 PROCEDURE get_segments(context IN context_r,
176 segments OUT nocopy segments_dr)
177 IS
178 BEGIN
179 get_segments(context => context,
180 segments => segments,
181 enabled_only => FALSE);
182 END;
183
184
185 PROCEDURE test IS
186 flexfield dflex_r;
187 flexinfo dflex_dr;
188 contexts contexts_dr;
189 i BINARY_INTEGER;
190 segments segments_dr;
191 BEGIN
192 get_flexfield('FND', 'FND_FLEX_TEST', flexfield, flexinfo);
193
194 dbms_debug('=== FLEXFIELD INFO ===');
195 dbms_debug('title=' || flexinfo.title);
196 dbms_debug('table=' || flexinfo.table_name);
197 dbms_debug('descr=' || flexinfo.description);
198 dbms_debug('delim=' || flexinfo.segment_delimeter);
199 dbms_debug('def_ctx_fld' || flexinfo.default_context_field);
200 dbms_debug('def_ctx_val' || flexinfo.default_context_value);
201 dbms_debug('protect' || flexinfo.protected_flag);
202 dbms_debug('ctx_prmpt' || flexinfo.form_context_prompt);
203 dbms_debug('ctx_col_name' || flexinfo.context_column_name);
204
205
206 dbms_debug('=== ENABLED CONTEXT INFO ===');
207 get_contexts(flexfield, contexts);
208 FOR i IN 1 .. contexts.ncontexts LOOP
209 IF(contexts.is_enabled(i)) THEN
210 dbms_debug(contexts.context_code(i) || ' - ' ||
211 contexts.context_description(i));
212 END IF;
213 END LOOP;
214
215 dbms_debug('=== SEGMENT INFO (for global context) ===');
216 get_segments(make_context(flexfield,
217 contexts.context_code(contexts.global_context)),
218 segments,
219 TRUE);
220 FOR i IN 1 .. segments.nsegments LOOP
221 dbms_debug(segments.segment_name(i) || ' - ' ||
222 segments.application_column_name(i) || ' - ' ||
223 segments.description(i));
224 END LOOP;
225
226 END;
227
228 END fnd_dflex; /* end package */