1 PACKAGE BODY FND_FLEX_APIS AS
2 /* $Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $ */
3
4 -- ------------------------------------------------------------------------
5 -- Gets the segment number corresponding to the **UNIQUE** qualifier
6 -- name entered. Segment number is the display order of the segment
7 -- not to be confused with the SEGMENT_NUM column of the
8 -- FND_ID_FLEX_SEGMENTS table. Returns TRUE segment_number if ok,
9 -- or FALSE and sets error using FND_MESSAGES on error.
10 -- ------------------------------------------------------------------------
11 FUNCTION get_qualifier_segnum(appl_id IN NUMBER,
12 key_flex_code IN VARCHAR2,
13 structure_number IN NUMBER,
14 flex_qual_name IN VARCHAR2,
15 segment_number OUT nocopy NUMBER)
16 RETURN BOOLEAN
17 IS
18 this_segment_num NUMBER;
19 BEGIN
20 SELECT s.segment_num INTO this_segment_num
21 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
22 fnd_segment_attribute_types sat
23 WHERE s.application_id = appl_id
24 AND s.id_flex_code = key_flex_code
25 AND s.id_flex_num = structure_number
26 AND s.enabled_flag = 'Y'
27 AND s.application_column_name = sav.application_column_name
28 AND sav.application_id = appl_id
29 AND sav.id_flex_code = key_flex_code
30 AND sav.id_flex_num = structure_number
31 AND sav.attribute_value = 'Y'
32 AND sav.segment_attribute_type = sat.segment_attribute_type
33 AND sat.application_id = appl_id
34 AND sat.id_flex_code = key_flex_code
35 AND sat.unique_flag = 'Y'
36 AND sat.segment_attribute_type = flex_qual_name
37 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
38
39 SELECT count(segment_num) INTO segment_number
40 FROM fnd_id_flex_segments
41 WHERE application_id = appl_id
42 AND id_flex_code = key_flex_code
43 AND id_flex_num = structure_number
44 AND enabled_flag = 'Y'
45 AND segment_num <= this_segment_num
46 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
47
48 return(TRUE);
49 EXCEPTION
50 WHEN OTHERS then
51 return(FALSE);
52 END get_qualifier_segnum;
53
54 -- ----------------------------------------------------------------------
55 FUNCTION get_segment_column(x_application_id in NUMBER,
56 x_id_flex_code in VARCHAR2,
57 x_id_flex_num in NUMBER,
58 x_seg_attr_type in VARCHAR2,
59 x_app_column_name in out nocopy VARCHAR2)
60 return BOOLEAN
61 IS
62 BEGIN
63 SELECT application_column_name
64 INTO x_app_column_name
65 FROM fnd_segment_attribute_values
66 WHERE application_id = x_application_id
67 AND id_flex_code = x_id_flex_code
68 AND id_flex_num = x_id_flex_num
69 AND segment_attribute_type = x_seg_attr_type
70 AND attribute_value = 'Y'
71 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
72
73 RETURN (TRUE);
74 EXCEPTION
75 WHEN OTHERS THEN
76 RETURN (FALSE);
77 END get_segment_column;
78
79 -- ----------------------------------------------------------------------
80 FUNCTION get_segment_info(x_application_id in NUMBER,
81 x_id_flex_code in VARCHAR2,
82 x_id_flex_num in NUMBER,
83 x_seg_num in NUMBER,
84 x_appcol_name out nocopy VARCHAR2,
85 x_seg_name out nocopy VARCHAR2,
86 x_prompt out nocopy VARCHAR2,
87 x_value_set_name out nocopy VARCHAR2)
88 RETURN BOOLEAN
89 IS
90 BEGIN
91 SELECT application_column_name, segment_name,
92 form_left_prompt, flex_value_set_name
93 INTO x_appcol_name, x_seg_name,
94 x_prompt, x_value_set_name
95 FROM fnd_id_flex_segments_vl s, fnd_flex_value_sets vs
96 WHERE s.application_id = x_application_id
97 AND s.id_flex_code = x_id_flex_code
98 AND s.id_flex_num = x_id_flex_num
99 AND s.segment_num = x_seg_num
100 AND s.enabled_flag = 'Y'
101 AND s.flex_value_set_id = vs.flex_value_set_id
102 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
103
104 RETURN (TRUE);
105 EXCEPTION
106 WHEN OTHERS THEN
107 RETURN (FALSE);
108 END get_segment_info;
109
110 -- ----------------------------------------------------------------------
111 FUNCTION get_enabled_segment_num(x_application_id in NUMBER,
112 x_conc_prog_name in VARCHAR2,
113 x_num_of_segments out nocopy NUMBER)
114 RETURN BOOLEAN
115 IS
116 BEGIN
117 SELECT COUNT(*)
118 INTO x_num_of_segments
119 FROM fnd_descr_flex_column_usages
120 WHERE application_id = x_application_id
121 AND descriptive_flexfield_name = '$SRS$.'||x_conc_prog_name
122 AND enabled_flag = 'Y'
123 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
124
125 return (TRUE);
126 EXCEPTION
127 WHEN OTHERS THEN
128 RETURN (FALSE);
129 END get_enabled_segment_num;
130
131 -- ----------------------------------------------------------------------
132 FUNCTION get_segment_delimiter(x_application_id in NUMBER,
133 x_id_flex_code in VARCHAR2,
134 x_id_flex_num in NUMBER)
135 return VARCHAR2
136 IS
137 delimiter VARCHAR2(1) default NULL;
138 BEGIN
139 SELECT concatenated_segment_delimiter
140 INTO delimiter
141 FROM fnd_id_flex_structures
142 WHERE application_id = x_application_id
143 AND id_flex_code = x_id_flex_code
144 AND id_flex_num = x_id_flex_num
145 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL;
146
147 RETURN delimiter;
148
149 EXCEPTION
150 WHEN OTHERS THEN
151 RETURN NULL;
152 END get_segment_delimiter;
153
154 -- ----------------------------------------------------------------------
155 -- This function is for use from GLOBAL libraries only
156 -- ----------------------------------------------------------------------
157 FUNCTION gbl_get_segment_delimiter(x_application_id in NUMBER,
158 x_id_flex_code in VARCHAR2,
159 x_id_flex_num in NUMBER)
160 RETURN VARCHAR2
161 IS
162 BEGIN
163 return get_segment_delimiter(x_application_id, x_id_flex_code,
164 x_id_flex_num);
165 END gbl_get_segment_delimiter;
166
167 -- ----------------------------------------------------------------------
168 FUNCTION is_descr_setup(x_application_id in NUMBER,
169 x_desc_flex_name in VARCHAR2)
170 return BOOLEAN
171 IS
172 row_count NUMBER;
173 BEGIN
174 /* Changed existence check logic: Bug 4081024 */
175 SELECT 1
176 INTO row_count
177 FROM fnd_descr_flex_column_usages u, fnd_descr_flex_contexts c
178 WHERE u.application_id = x_application_id
179 AND u.descriptive_flexfield_name = x_desc_flex_name
180 AND c.application_id = u.application_id
181 AND c.descriptive_flexfield_name = u.descriptive_flexfield_name
182 AND c.descriptive_flex_context_code = u.descriptive_flex_context_code
183 AND c.enabled_flag = 'Y'
184 AND u.enabled_flag = 'Y'
185 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL
186 AND ROWNUM = 1;
187
188 return (TRUE);
189
190 EXCEPTION
191 WHEN no_data_found THEN
192 return (FALSE);
193
194 END is_descr_setup;
195
196 -- ----------------------------------------------------------------------
197 -- This is for use from GLOBAL libraries only
198 -- ----------------------------------------------------------------------
199 FUNCTION gbl_is_descr_setup(x_application_id in NUMBER,
200 x_desc_flex_name in VARCHAR2)
201 return BOOLEAN
202 IS
203 BEGIN
204 return is_descr_setup(x_application_id, x_desc_flex_name);
205 END gbl_is_descr_setup;
206
207 -- ----------------------------------------------------------------------
208 /* Bug 7046189 - logic changed from checking required_flag to checking
209 * display_flag. If a DFF has enabled and displayed segments defined,
210 * it should be marked as required for display in a folder block */
211
212 FUNCTION is_descr_required(x_application_id in NUMBER,
213 x_desc_flex_name in VARCHAR2)
214 return BOOLEAN
215 IS
216 row_count NUMBER;
217 BEGIN
218 /* Changed existence check logic: Bug 4081024 */
219 SELECT 1
220 INTO row_count
221 FROM fnd_descr_flex_column_usages u, fnd_descr_flex_contexts c
222 WHERE u.application_id = x_application_id
223 AND u.descriptive_flexfield_name = x_desc_flex_name
224 AND c.application_id = u.application_id
225 AND c.descriptive_flexfield_name = u.descriptive_flexfield_name
226 AND c.descriptive_flex_context_code = u.descriptive_flex_context_code
227 AND c.enabled_flag = 'Y'
228 AND u.enabled_flag = 'Y'
229 AND u.display_flag = 'Y'
230 AND '$Header: AFFFAPIB.pls 120.1.12010000.2 2008/10/24 20:55:06 tebarnes ship $' IS NOT NULL
231 AND ROWNUM = 1;
232
233 return (TRUE);
234
235 EXCEPTION
236 WHEN no_data_found THEN
237 return (FALSE);
238
239 END is_descr_required;
240
241 -- ----------------------------------------------------------------------
242 PROCEDURE descr_setup_or_required(x_application_id IN NUMBER,
243 x_desc_flex_name IN VARCHAR2,
244 enabled_flag OUT nocopy VARCHAR2,
245 required_flag OUT nocopy VARCHAR2)
246 IS
247 BEGIN
248 if(fnd_flex_apis.is_descr_setup(x_application_id,
249 x_desc_flex_name)) then
250 enabled_flag := 'Y';
251 else
252 enabled_flag := 'N';
253 end if;
254 if(fnd_flex_apis.is_descr_required(x_application_id,
255 x_desc_flex_name)) then
256 required_flag := 'Y';
257 else
258 required_flag := 'N';
259 end if;
260 END descr_setup_or_required;
261
262 -- ----------------------------------------------------------------------
263 -- This function is for use from GLOBAL libraries only
264 -- ----------------------------------------------------------------------
265 PROCEDURE gbl_descr_setup_or_required(x_application_id IN NUMBER,
266 x_desc_flex_name IN VARCHAR2,
267 enabled_flag OUT nocopy VARCHAR2,
268 required_flag OUT nocopy VARCHAR2)
269 IS
270 BEGIN
271 descr_setup_or_required(x_application_id, x_desc_flex_name,
272 enabled_flag, required_flag);
273 END gbl_descr_setup_or_required;
274
275
276 -- ----------------------------------------------------------------------
277 PROCEDURE get_dff_req_segs_info_private(p_application_id IN NUMBER,
278 p_flexfield_name IN VARCHAR2,
279 p_context_code IN VARCHAR2,
280 px_req_segs_info IN OUT nocopy dff_required_segments_info)
281 IS
282 CURSOR c_required_segments(p_application_id IN NUMBER,
283 p_flexfield_name IN VARCHAR2,
284 p_context_code IN VARCHAR2)
285 IS
286 SELECT end_user_column_name
287 FROM fnd_descr_flex_column_usages
288 WHERE application_id = p_application_id
289 AND descriptive_flexfield_name = p_flexfield_name
290 AND descriptive_flex_context_code = p_context_code
291 AND enabled_flag = 'Y'
292 AND required_flag = 'Y';
293 BEGIN
294 px_req_segs_info.context_code := p_context_code;
295 px_req_segs_info.required_segment_count := 0;
296
297 FOR l_required_segment IN c_required_segments(p_application_id, p_flexfield_name, p_context_code) LOOP
298 px_req_segs_info.required_segment_count := px_req_segs_info.required_segment_count + 1;
299 px_req_segs_info.required_segment_names(px_req_segs_info.required_segment_count) := l_required_segment.end_user_column_name;
300 END LOOP;
301 END get_dff_req_segs_info_private;
302
303 -- ----------------------------------------------------------------------
304 PROCEDURE get_dff_global_req_segs_info(p_application_id IN NUMBER,
305 p_flexfield_name IN VARCHAR2,
306 x_is_context_segment_required OUT nocopy BOOLEAN,
307 x_global_req_segs_info OUT nocopy dff_required_segments_info)
308 IS
309 l_context_required_flag fnd_descriptive_flexs.context_required_flag%TYPE;
310 BEGIN
311 --
312 -- Get the context segment info:
313 --
314 BEGIN
315 SELECT context_required_flag
316 INTO l_context_required_flag
317 FROM fnd_descriptive_flexs
318 WHERE application_id = p_application_id
319 AND descriptive_flexfield_name = p_flexfield_name;
320 EXCEPTION
321 WHEN OTHERS THEN
322 l_context_required_flag := 'N';
323 END;
324
325 IF (l_context_required_flag = 'Y') THEN
326 x_is_context_segment_required := TRUE;
327 ELSE
328 x_is_context_segment_required := FALSE;
329 END IF;
330
331 --
332 -- Get the 'Global Data Elements' segments info:
333 --
334 get_dff_req_segs_info_private(p_application_id, p_flexfield_name, 'Global Data Elements',
335 x_global_req_segs_info);
336
337 END get_dff_global_req_segs_info;
338
339 -- ----------------------------------------------------------------------
340 PROCEDURE get_dff_context_req_segs_info(p_application_id IN NUMBER,
341 p_flexfield_name IN VARCHAR2,
342 p_context_code IN VARCHAR2,
343 x_context_req_segs_info OUT nocopy dff_required_segments_info)
344 IS
345 BEGIN
346 --
347 -- Get the context sensitive segment info for the given context.
348 --
349 get_dff_req_segs_info_private(p_application_id, p_flexfield_name, p_context_code,
350 x_context_req_segs_info);
351
352 END get_dff_context_req_segs_info;
353
354 END FND_FLEX_APIS;
[Home] [Help]