[Home] [Help]
PACKAGE BODY: APPS.XLA_FLEX_UTIL
Source
1 PACKAGE BODY XLA_FLEX_UTIL AS
2 /* $Header: xlauflex.pkb 120.1 2004/02/13 22:55:21 weshen noship $ */
3
4 CURSOR flex_segments_csr(p_chart_of_accounts_id NUMBER)
5 IS
6 SELECT segment_num, application_column_name
7 FROM fnd_id_flex_segments
8 WHERE application_id = 101
9 AND id_flex_code = 'GL#'
10 AND enabled_flag = 'Y'
11 AND id_flex_num = p_chart_of_accounts_id
12 ORDER BY segment_num;
13
14 FUNCTION getsegmentInfo(p_chartofaccountsid IN NUMBER,
15 p_segmentinfo OUT NOCOPY t_segmentinfo
16 ) RETURN BOOLEAN IS
17 CURSOR c_getsegmentnum(c_chartOfAccountsID NUMBER) IS
18 SELECT segment_num
19 FROM fnd_id_flex_segments_vl
20 WHERE application_id = 101
21 AND id_flex_code = 'GL#'
22 AND enabled_flag = 'Y'
23 AND id_flex_num = c_chartOfAccountsID
24 ORDER BY segment_num;
25
26 l_segmentinfo XLA_FLEX_UTIL.t_segmentinfo;
27 l_rownum NUMBER;
28 BEGIN
29 l_rownum := 0;
30 FOR segment_rec IN c_getsegmentnum(p_chartofaccountsid) LOOP
31 l_rownum := l_rownum + 1;
32 l_segmentinfo(segment_rec.segment_num).segment_num := segment_rec.segment_num;
33 l_segmentinfo(segment_rec.segment_num).segment_ordernum := l_rownum;
34 END LOOP;
35 p_segmentinfo := l_segmentinfo;
36 RETURN(TRUE);
37 END getsegmentInfo;
38
39
40 FUNCTION get_flex_segment(p_chart_of_accounts_id IN NUMBER
41 ,p_segment_number IN NUMBER )
42 RETURN VARCHAR2;
43
44
45 /*===========================================================================+
46 | PROCEDURE |
47 | get_account_flex_info |
48 | |
49 | DESCRIPTION |
50 | Gets accounting flexfield information based on the chart of accounts id|
51 | passed. |
52 | |
53 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
54 | xla_debug.print |
55 | |
56 | ARGUMENTS |
57 | p_chart_of_accounts_id IN NUMBER, |
58 | x_segment_delimiter IN OUT NOCOPY VARCHAR2, |
59 | x_enabled_segment_count IN OUT NOCOPY NUMBER, |
60 | x_segment_order_by IN OUT NOCOPY VARCHAR2, |
61 | x_accseg_segment_num IN OUT NOCOPY NUMBER, |
62 | x_accseg_app_col_name IN OUT NOCOPY VARCHAR2, |
63 | x_accseg_left_prompt IN OUT NOCOPY VARCHAR2, |
64 | x_balseg_segment_num IN OUT NOCOPY NUMBER, |
65 | x_balseg_app_col_name IN OUT NOCOPY VARCHAR2, |
66 | x_balseg_left_prompt IN OUT NOCOPY VARCHAR2 |
67 | |
68 | NOTES |
69 | |
70 | MODIFICATION HISTORY |
71 | 30-Oct-98 Mahesh Sabapathy Created |
72 | |
73 +===========================================================================*/
74 PROCEDURE get_account_flex_info (
75 p_chart_of_accounts_id IN NUMBER,
76 x_segment_delimiter IN OUT NOCOPY VARCHAR2,
77 x_enabled_segment_count IN OUT NOCOPY NUMBER,
78 x_segment_order_by IN OUT NOCOPY VARCHAR2,
79 x_accseg_segment_num IN OUT NOCOPY NUMBER,
80 x_accseg_app_col_name IN OUT NOCOPY VARCHAR2,
81 x_accseg_left_prompt IN OUT NOCOPY VARCHAR2,
82 x_balseg_segment_num IN OUT NOCOPY NUMBER,
83 x_balseg_app_col_name IN OUT NOCOPY VARCHAR2,
84 x_balseg_left_prompt IN OUT NOCOPY VARCHAR2) IS
85
86 dummy BOOLEAN := FALSE;
87
88 l_seg_name VARCHAR2(30);
89 l_value_set VARCHAR2(60);
90
91 BEGIN
92
93 -- Identify the natural account and balancing segments
94 dummy := get_segment_number(
95 101, 'GL#', p_chart_of_accounts_id,
96 'GL_ACCOUNT', x_accseg_segment_num);
97 dummy := get_segment_number(
98 101, 'GL#', p_chart_of_accounts_id,
99 'GL_BALANCING', x_balseg_segment_num);
100
101 -- Get the segment delimiter
102 x_segment_delimiter := FND_FLEX_APIS.get_segment_delimiter( 101, 'GL#', p_chart_of_accounts_id);
103
104 -- Count 'em up and string 'em together
105 x_enabled_segment_count := 0;
106 FOR segments_rec IN flex_segments_csr(p_chart_of_accounts_id) LOOP
107 -- How many enabled segs are there?
108 x_enabled_segment_count := flex_segments_csr%ROWCOUNT;
109
110 -- Record the order by string
111 IF flex_segments_csr%ROWCOUNT = 1 THEN
112 x_segment_order_by := segments_rec.application_column_name;
113 ELSE
114 x_segment_order_by := x_segment_order_by||
115 ','||
116 segments_rec.application_column_name;
117 END IF;
118
119 -- If this is either the accseg or balseg, get more info
120 IF segments_rec.segment_num = x_accseg_segment_num THEN
121 IF (FND_FLEX_APIS.get_segment_info(
122 101, 'GL#', p_chart_of_accounts_id,
123 segments_rec.segment_num, x_accseg_app_col_name,
124 l_seg_name, x_accseg_left_prompt, l_value_set)) THEN
125 null;
126 END IF;
127 ELSIF segments_rec.segment_num = x_balseg_segment_num THEN
128 IF (FND_FLEX_APIS.get_segment_info(
129 101, 'GL#', p_chart_of_accounts_id,
130 segments_rec.segment_num, x_balseg_app_col_name,
131 l_seg_name, x_balseg_left_prompt, l_value_set)) THEN
132 null;
133 END IF;
134 END IF;
135 END LOOP;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 app_exception.raise_exception;
140 END get_account_flex_info;
141
142 /*===========================================================================+
143 | FUNCTION |
144 | get_ordered_account |
145 | |
146 | DESCRIPTION |
147 | returns the account ordered by balancing segment, natural account and |
148 | all other segments |
149 | |
150 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
151 | |
152 | ARGUMENTS : IN: p_charts_of_accounts_id |
153 | p_table_alias |
154 | |
155 | NOTES |
156 | |
157 | MODIFICATION HISTORY |
158 | 03-Nov-98 Dirk Stevens Created |
159 | |
160 +===========================================================================*/
161
162 FUNCTION get_ordered_account(
163 p_chart_of_accounts_id IN NUMBER
164 ,p_table_alias IN VARCHAR2 )
165 RETURN VARCHAR2
166 IS
167
168 dummy BOOLEAN := FALSE;
169
170 l_seg_name VARCHAR2(30);
171 l_value_set VARCHAR2(60);
172
173 l_segment_delimiter VARCHAR2(30);
174 l_enabled_segment_count NUMBER;
175
176 l_accseg_segment_num NUMBER;
177 l_balseg_segment_num NUMBER;
178
179 return_value VARCHAR2(2000);
180
181 BEGIN
182
183 -- Identify the natural account and balancing segments
184 dummy := get_segment_number(
185 101, 'GL#', p_chart_of_accounts_id,
186 'GL_ACCOUNT', l_accseg_segment_num);
187 dummy := get_segment_number(
188 101, 'GL#', p_chart_of_accounts_id,
189 'GL_BALANCING', l_balseg_segment_num);
190
191 -- Get the segment delimiter
192 l_segment_delimiter := FND_FLEX_APIS.get_segment_delimiter( 101, 'GL#', p_chart_of_accounts_id);
193
194 -- String balancing segment and natural account segment together
195 return_value := p_table_alias||'.'||get_flex_segment(p_chart_of_accounts_id, l_balseg_segment_num);
196
197 -- Concat the natural account segment
198 return_value := return_value||'||'''||l_segment_delimiter||'''||'||p_table_alias||'.'||get_flex_segment(p_chart_of_accounts_id, l_accseg_segment_num);
199
200 -- Count the rest and string 'em together
201 l_enabled_segment_count := 0;
202
203 FOR segments_rec IN flex_segments_csr(p_chart_of_accounts_id) LOOP
204 -- How many enabled segs are there?
205 l_enabled_segment_count := flex_segments_csr%ROWCOUNT;
206
207
208 IF ( ( segments_rec.segment_num <> l_accseg_segment_num)
209 AND (segments_rec.segment_num <> l_balseg_segment_num) )
210 THEN
211
212 return_value := return_value||'||'''||l_segment_delimiter||'''||'||p_table_alias||'.'||segments_rec.application_column_name;
213 END IF;
214
215 END LOOP;
216
217 RETURN return_value;
218
219 EXCEPTION
220 WHEN OTHERS THEN
221 app_exception.raise_exception;
222 END get_ordered_account;
223
224 FUNCTION get_flex_segment(p_chart_of_accounts_id IN NUMBER
225 ,p_segment_number IN NUMBER )
226 RETURN VARCHAR2
227 IS
228 return_value VARCHAR2(250);
229
230 CURSOR flex_segment_name_csr(p_chart_of_accounts_id NUMBER
231 ,p_segment_number NUMBER)
232 IS
233 SELECT application_column_name
234 FROM fnd_id_flex_segments
235 WHERE application_id = 101
236 AND id_flex_code = 'GL#'
237 AND enabled_flag = 'Y'
238 AND id_flex_num = p_chart_of_accounts_id
239 AND segment_num = p_segment_number;
240
241 flex_segment_name flex_segment_name_csr%ROWTYPE;
242
243 BEGIN
244
245 OPEN flex_segment_name_csr(p_chart_of_accounts_id, p_segment_number);
246 FETCH flex_segment_name_csr INTO flex_segment_name;
247 return_value := flex_segment_name.application_column_name;
248 CLOSE flex_segment_name_csr;
249
250 RETURN return_value;
251
252 END get_flex_segment;
253
254 /*===========================================================================+
255 | FUNCTION |
256 | is_segment_dependent |
257 | |
258 | DESCRIPTION |
259 | returns 'Y' if the segment is dependent |
260 | |
261 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
262 | |
263 | ARGUMENTS : IN: segment |
264 | |
265 | NOTES |
266 | |
267 | MODIFICATION HISTORY |
268 | 15-Apr-99 Dirk Stevens Created |
269 | |
270 +===========================================================================*/
271
272 FUNCTION is_segment_dependent(segment IN NUMBER
273 ,p_ChartOfAccountsID IN NUMBER
274 ,p_flex_code IN VARCHAR2
275 ,p_applicationID IN NUMBER)
276 RETURN VARCHAR2
277 IS
278 returnValue VARCHAR2(240);
279
280 l_dummy_flex_ret_value BOOLEAN;
281
282 l_segment1_column VARCHAR2(240);
283 l_seg_name VARCHAR2(240);
284 l_prompt VARCHAR2(240);
285 l_value_set_name VARCHAR2(240);
286 l_value_set_id NUMBER;
287
288 l_valueset FND_VSET.VALUESET_R;
289 l_format FND_VSET.VALUESET_DR;
290
291 CURSOR get_value_set_id_c(p_ValueSetName VARCHAR2) IS
292 SELECT FLEX_VALUE_SET_ID
293 FROM FND_FLEX_VALUE_SETS
294 WHERE FLEX_VALUE_SET_NAME = p_ValueSetName;
295
296 BEGIN
297
298 l_dummy_flex_ret_value := FND_FLEX_APIS.GET_SEGMENT_INFO(
299 p_applicationID
300 ,p_flex_code
301 ,p_ChartOfAccountsID
302 ,segment
303 ,l_segment1_column
304 ,l_seg_name
305 ,l_prompt
306 ,l_value_set_name);
307
308 -- Now we have the value set name, let's get the id
309 -- Name should be unique
310
311 OPEN get_value_set_id_c(l_value_set_name);
312 FETCH get_value_set_id_c INTO l_value_set_id;
313 CLOSE get_value_set_id_c;
314
315 -- retrieve the dependency information
316
317 FND_VSET.GET_VALUESET(l_value_set_id
318 ,l_valueset
319 ,l_format );
320
321 RETURN l_valueset.validation_type;
322
323 END is_segment_dependent;
324
325 /*===========================================================================+
326 | PROCEDURE |
327 | get_parent_segment |
328 | |
329 | DESCRIPTION |
330 | Returns parent segment number and application column name for a |
331 | given child segment number and structure id. |
332 | |
333 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
334 | |
335 | |
336 | ARGUMENTS |
337 | p_application_id IN NUMBER, |
338 | p_flex_code IN VARCHAR2 |
339 | p_structure_id IN NUMBER |
340 | p_child_segment_num IN NUMBER |
341 | p_parent_segment_num OUT NOCOPY NUMBER |
342 | p_parent_col_name OUT NOCOPY VARCHAR |
343 | |
344 | |
345 | |
346 | NOTES |
350 | |
347 | |
348 | MODIFICATION HISTORY |
349 | 03-Aug-00 Shishir Joshi Created |
351 +===========================================================================*/
352
353 PROCEDURE get_parent_segment(p_application_id IN NUMBER
354 ,p_flex_code IN VARCHAR2
355 ,p_structure_id IN NUMBER
356 ,p_child_segment_num IN NUMBER
357 ,p_parent_segment_num OUT NOCOPY NUMBER
358 ,p_parent_col_name OUT NOCOPY VARCHAR2
359 ) IS
360 BEGIN
361 SELECT DECODE(V.PARENT_FLEX_VALUE_SET_ID,
362 NULL,SG.SEGMENT_Num , SG2.SEGMENT_num) parent_seg_num,
363 DECODE(V.PARENT_FLEX_VALUE_SET_ID,
364 NULL,NULL , SG2.application_column_name) parent_app_col_name
365 INTO p_parent_segment_num,
366 p_parent_col_name
367 FROM FND_ID_FLEXS K,
368 FND_APPLICATION_VL A,
369 FND_ID_FLEX_STRUCTURES_VL S,
370 FND_ID_FLEX_SEGMENTS_VL SG,
371 FND_FLEX_VALUE_SETS V,
372 FND_ID_FLEX_SEGMENTS_VL SG2,
373 FND_FLEX_VALIDATION_TABLES T
374 WHERE K.APPLICATION_ID = A.APPLICATION_ID
375 AND K.APPLICATION_ID = S.APPLICATION_ID
376 AND K.ID_FLEX_CODE = S.ID_FLEX_CODE
377 AND S.ENABLED_FLAG = 'Y'
378 AND K.APPLICATION_ID = SG.APPLICATION_ID
379 AND K.ID_FLEX_CODE = SG.ID_FLEX_CODE
380 AND S.ID_FLEX_NUM = SG.ID_FLEX_NUM
381 AND SG.ENABLED_FLAG = 'Y'
382 AND SG.FLEX_VALUE_SET_ID = V.FLEX_VALUE_SET_ID
383 AND V.PARENT_FLEX_VALUE_SET_ID = SG2.FLEX_VALUE_SET_ID(+)
384 AND K.APPLICATION_ID = NVL(SG2.APPLICATION_ID, K.APPLICATION_ID)
385 AND K.ID_FLEX_CODE = NVL(SG2.ID_FLEX_CODE, K.ID_FLEX_CODE)
386 AND S.ID_FLEX_NUM = NVL(SG2.ID_FLEX_NUM, S.ID_FLEX_NUM)
387 AND NVL(SG2.ENABLED_FLAG, 'Y') = 'Y'
388 AND NVL(SG2.SEGMENT_NUM,0) = (SELECT NVL(MAX(SG3.SEGMENT_NUM),0)
389 FROM FND_ID_FLEX_SEGMENTS SG3
390 WHERE SG3.APPLICATION_ID = K.APPLICATION_ID
391 AND SG3.ID_FLEX_CODE = K.ID_FLEX_CODE
392 AND SG3.ID_FLEX_NUM = S.ID_FLEX_NUM
393 AND SG3.FLEX_VALUE_SET_ID = V.PARENT_FLEX_VALUE_SET_ID
394 AND SG3.SEGMENT_NUM < SG.SEGMENT_NUM
395 AND SG3.ENABLED_FLAG = 'Y')
396 AND V.FLEX_VALUE_SET_ID = T.FLEX_VALUE_SET_ID(+)
397 AND k.application_id = p_application_id
398 AND k.id_flex_code = p_flex_code
399 AND s.id_flex_num = p_structure_id
400 AND sg.segment_num = p_child_segment_num;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 app_exception.raise_exception;
405 END get_parent_segment;
406
407 /*===========================================================================+
408 | FUNCTION |
409 | get_segment_number |
410 | |
411 | DESCRIPTION |
412 | Returns the segment number for given chart of accounts and qualifier. |
413 | This function was added because fnd_flex_apis does not have an api to |
414 | to return the segment number. |
415 | |
416 | EXTERNAL PROCEDURES/FUNCTIONS ACCESSED |
417 | |
418 | |
419 | ARGUMENTS |
420 | p_application_id IN NUMBER, |
421 | p_flex_code IN VARCHAR2 |
422 | p_structure_id IN NUMBER |
423 | p_flex_qual_name IN VARCHAR2 |
424 | p_seg_num OUT NOCOPY NUMBER |
425 | |
426 | |
427 | NOTES |
428 | |
429 | MODIFICATION HISTORY |
430 | 10-May-00 Dimple Shah Created. |
431 | |
432 +===========================================================================*/
433
434 FUNCTION get_segment_number(p_application_id IN NUMBER,
435 p_flex_code IN VARCHAR2,
436 p_structure_id IN NUMBER,
437 p_flex_qual_name IN VARCHAR2,
438 p_seg_num OUT NOCOPY NUMBER)
439 RETURN BOOLEAN
440 IS
441 l_seg_col VARCHAR2(30);
442 l_seg_num NUMBER;
443 dummy BOOLEAN := FALSE;
444 return_value BOOLEAN := FALSE;
445
446 BEGIN
447 dummy := fnd_flex_apis.get_segment_column(p_application_id, p_flex_code,
448 p_structure_id,
452 FOR segments_rec in flex_segments_csr(p_structure_id) LOOP
449 p_flex_qual_name,
450 l_seg_col);
451
453 IF segments_rec.application_column_name = l_seg_col THEN
454 p_seg_num := segments_rec.segment_num;
455 return_value := TRUE;
456 ELSE
457 return_value := FALSE;
458 END IF;
459 END LOOP;
460 RETURN return_value;
461
462 EXCEPTION
463 when others then
464 app_exception.raise_exception;
468 END XLA_FLEX_UTIL;
465
466 END get_segment_number;
467