1 PACKAGE BODY FND_FLEX_APIS AS
2 /* $Header: AFFFAPIB.pls 120.3 2010/05/25 17:13:54 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;
22 fnd_segment_attribute_types sat
19 BEGIN
20 SELECT s.segment_num INTO this_segment_num
21 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
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.3 2010/05/25 17:13:54 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.3 2010/05/25 17:13:54 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.3 2010/05/25 17:13:54 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.3 2010/05/25 17:13:54 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.3 2010/05/25 17:13:54 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.3 2010/05/25 17:13:54 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);
168 FUNCTION is_descr_setup(x_application_id in NUMBER,
165 END gbl_get_segment_delimiter;
166
167 -- ----------------------------------------------------------------------
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.3 2010/05/25 17:13:54 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
209 FUNCTION is_descr_required(x_application_id in NUMBER,
210 x_desc_flex_name in VARCHAR2)
211 return BOOLEAN
212 IS
213 row_count NUMBER;
214 BEGIN
215 /* Changed existence check logic: Bug 4081024 */
216 SELECT 1
217 INTO row_count
218 FROM fnd_descr_flex_column_usages u, fnd_descr_flex_contexts c
219 WHERE u.application_id = x_application_id
220 AND u.descriptive_flexfield_name = x_desc_flex_name
221 AND c.application_id = u.application_id
222 AND c.descriptive_flexfield_name = u.descriptive_flexfield_name
223 AND c.descriptive_flex_context_code = u.descriptive_flex_context_code
224 AND c.enabled_flag = 'Y'
225 AND u.enabled_flag = 'Y'
226 AND u.required_flag = 'Y'
227 AND '$Header: AFFFAPIB.pls 120.3 2010/05/25 17:13:54 tebarnes ship $' IS NOT NULL
228 AND ROWNUM = 1;
229
230 return (TRUE);
231
232 EXCEPTION
233 WHEN no_data_found THEN
234 return (FALSE);
235
236 END is_descr_required;
237
238
239 -- ----------------------------------------------------------------------
240 -- This is for use from GLOBAL libraries only
241 -- ----------------------------------------------------------------------
242 -- ----------------------------------------------------------------------
243 /* Bug 7046189 - logic changed from checking required_flag to checking
244 * display_flag. If a DFF has enabled and displayed segments defined,
245 * it should be marked as required for display in a folder block
246 * Bug 9074261 - new gbl function created for 7046189 solution */
247
248 FUNCTION gbl_is_descr_required(x_application_id in NUMBER,
249 x_desc_flex_name in VARCHAR2)
250 return BOOLEAN
251 IS
252 row_count NUMBER;
253 BEGIN
254 /* Changed existence check logic: Bug 4081024 */
255 SELECT 1
256 INTO row_count
257 FROM fnd_descr_flex_column_usages u, fnd_descr_flex_contexts c
258 WHERE u.application_id = x_application_id
259 AND u.descriptive_flexfield_name = x_desc_flex_name
260 AND c.application_id = u.application_id
261 AND c.descriptive_flexfield_name = u.descriptive_flexfield_name
262 AND c.descriptive_flex_context_code = u.descriptive_flex_context_code
263 AND c.enabled_flag = 'Y'
264 AND u.enabled_flag = 'Y'
265 AND u.display_flag = 'Y'
266 AND '$Header: AFFFAPIB.pls 120.3 2010/05/25 17:13:54 tebarnes ship $' IS NOT NULL
267 AND ROWNUM = 1;
268
269 return (TRUE);
270
271 EXCEPTION
272 WHEN no_data_found THEN
273 return (FALSE);
274
275 END gbl_is_descr_required;
276
277 -- ----------------------------------------------------------------------
278 PROCEDURE descr_setup_or_required(x_application_id IN NUMBER,
279 x_desc_flex_name IN VARCHAR2,
280 enabled_flag OUT nocopy VARCHAR2,
281 required_flag OUT nocopy VARCHAR2)
282 IS
283 BEGIN
284 if(fnd_flex_apis.is_descr_setup(x_application_id,
285 x_desc_flex_name)) then
286 enabled_flag := 'Y';
287 else
288 enabled_flag := 'N';
289 end if;
290 if(fnd_flex_apis.is_descr_required(x_application_id,
291 x_desc_flex_name)) then
292 required_flag := 'Y';
293 else
294 required_flag := 'N';
295 end if;
296 END descr_setup_or_required;
297
298 -- ----------------------------------------------------------------------
299 -- This function is for use from GLOBAL libraries only
300 -- ----------------------------------------------------------------------
301 PROCEDURE gbl_descr_setup_or_required(x_application_id IN NUMBER,
302 x_desc_flex_name IN VARCHAR2,
303 enabled_flag OUT nocopy VARCHAR2,
304 required_flag OUT nocopy VARCHAR2)
305 IS
306 BEGIN
307 if(fnd_flex_apis.is_descr_setup(x_application_id,
308 x_desc_flex_name)) then
309 enabled_flag := 'Y';
310 else
311 enabled_flag := 'N';
312 end if;
313 if(fnd_flex_apis.gbl_is_descr_required(x_application_id,
314 x_desc_flex_name)) then
315 required_flag := 'Y';
316 else
317 required_flag := 'N';
318 end if;
319 END gbl_descr_setup_or_required;
320
321
322 -- ----------------------------------------------------------------------
323 PROCEDURE get_dff_req_segs_info_private(p_application_id IN NUMBER,
324 p_flexfield_name IN VARCHAR2,
325 p_context_code IN VARCHAR2,
326 px_req_segs_info IN OUT nocopy dff_required_segments_info)
327 IS
328 CURSOR c_required_segments(p_application_id IN NUMBER,
329 p_flexfield_name IN VARCHAR2,
330 p_context_code IN VARCHAR2)
331 IS
332 SELECT end_user_column_name
333 FROM fnd_descr_flex_column_usages
334 WHERE application_id = p_application_id
335 AND descriptive_flexfield_name = p_flexfield_name
336 AND descriptive_flex_context_code = p_context_code
337 AND enabled_flag = 'Y'
338 AND required_flag = 'Y';
339 BEGIN
340 px_req_segs_info.context_code := p_context_code;
341 px_req_segs_info.required_segment_count := 0;
342
343 FOR l_required_segment IN c_required_segments(p_application_id, p_flexfield_name, p_context_code) LOOP
344 px_req_segs_info.required_segment_count := px_req_segs_info.required_segment_count + 1;
345 px_req_segs_info.required_segment_names(px_req_segs_info.required_segment_count) := l_required_segment.end_user_column_name;
346 END LOOP;
347 END get_dff_req_segs_info_private;
348
349 -- ----------------------------------------------------------------------
350 PROCEDURE get_dff_global_req_segs_info(p_application_id IN NUMBER,
351 p_flexfield_name IN VARCHAR2,
352 x_is_context_segment_required OUT nocopy BOOLEAN,
353 x_global_req_segs_info OUT nocopy dff_required_segments_info)
354 IS
355 l_context_required_flag fnd_descriptive_flexs.context_required_flag%TYPE;
356 BEGIN
357 --
358 -- Get the context segment info:
359 --
360 BEGIN
361 SELECT context_required_flag
362 INTO l_context_required_flag
363 FROM fnd_descriptive_flexs
364 WHERE application_id = p_application_id
365 AND descriptive_flexfield_name = p_flexfield_name;
366 EXCEPTION
367 WHEN OTHERS THEN
368 l_context_required_flag := 'N';
369 END;
370
371 IF (l_context_required_flag = 'Y') THEN
372 x_is_context_segment_required := TRUE;
373 ELSE
374 x_is_context_segment_required := FALSE;
375 END IF;
376
377 --
378 -- Get the 'Global Data Elements' segments info:
379 --
380 get_dff_req_segs_info_private(p_application_id, p_flexfield_name, 'Global Data Elements',
381 x_global_req_segs_info);
382
383 END get_dff_global_req_segs_info;
384
385 -- ----------------------------------------------------------------------
386 PROCEDURE get_dff_context_req_segs_info(p_application_id IN NUMBER,
387 p_flexfield_name IN VARCHAR2,
388 p_context_code IN VARCHAR2,
389 x_context_req_segs_info OUT nocopy dff_required_segments_info)
390 IS
391 BEGIN
392 --
393 -- Get the context sensitive segment info for the given context.
394 --
395 get_dff_req_segs_info_private(p_application_id, p_flexfield_name, p_context_code,
396 x_context_req_segs_info);
397
398 END get_dff_context_req_segs_info;
399
400 END FND_FLEX_APIS;
[Home] [Help]