[Home] [Help]
PACKAGE BODY: APPS.GL_FLEXFIELDS_PKG
Source
1 PACKAGE BODY gl_flexfields_pkg AS
2 /* $Header: glumsflb.pls 120.16 2006/04/03 17:01:52 cma ship $ */
3
4
5 ---
6 --- PRIVATE VARIABLES
7 ---
8
9 --- Position of the segment
10 seg_num NUMBER := null;
11
12 -- Chart of accounts for which the position was gotten
13 last_coa_id NUMBER := null;
14
15 -- Qualifier for which the position was gotten
16 last_qual_text VARCHAR2(100) := null;
17
18 --
19 -- To cache description information for the sake of efficiency
20 -- 2 sets of variables for balancing and drilldown segments.
21 --
22 -- chart of account id
23 g_coa NUMBER := null;
24 -- balancing segment number
25 g_seg_num1 NUMBER := null;
26 -- drilldown segment number
27 g_seg_num2 NUMBER := null;
28 -- balancing segment value
29 g_seg_val1 VARCHAR2(25) := null;
30 -- drilldown segment value
31 g_seg_val2 VARCHAR2(25) := null;
32 -- balancing segment description
33 g_desc1 VARCHAR2(1000) := null;
34 -- drilldown segment description
35 g_desc2 VARCHAR2(1000) := null;
36
37
38 --
39 -- PUBLIC FUNCTIONS
40 --
41
42 -- BugFix: 2831551 Added the application id in the below where clause.
43
44 FUNCTION get_account_segment(coa_id NUMBER) RETURN VARCHAR2 IS
45 CURSOR get_acct_seg IS
46 SELECT fs.segment_name
47 FROM fnd_id_flex_segments fs,
48 fnd_segment_attribute_values av
49 WHERE fs.application_column_name = av.application_column_name
50 AND av.id_flex_code = 'GL#'
51 AND fs.id_flex_code = av.id_flex_code
52 AND av.id_flex_num = coa_id
53 AND fs.application_id = 101
54 AND av.application_id = 101
55 AND fs.id_flex_num = av.id_flex_num
56 AND av.segment_attribute_type='GL_ACCOUNT'
57 AND av.attribute_value='Y';
58 segname VARCHAR2(40);
59 BEGIN
60 OPEN get_acct_seg;
61 FETCH get_acct_seg INTO segname;
62
63 IF get_acct_seg%FOUND THEN
64 CLOSE get_acct_seg;
65 RETURN(segname);
66 ELSE
67 CLOSE get_acct_seg;
68 fnd_message.set_name('SQLGL', 'GL_MISSING_ACCOUNT_SEGMENT');
69 app_exception.raise_exception;
70 END IF;
71
72 EXCEPTION
73 WHEN app_exceptions.application_exception THEN
74 RAISE;
75 WHEN OTHERS THEN
76 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
77 fnd_message.set_token('PROCEDURE',
78 'gl_flexfields_pkg.get_account_segment');
79 RAISE;
80 END get_account_segment;
81
82
83
84 FUNCTION get_description(
85 x_coa_id NUMBER,
86 x_qual_text VARCHAR2,
87 x_segment_val VARCHAR2
88 ) RETURN VARCHAR2 IS
89 BEGIN
90 IF ((seg_num IS NULL)
91 OR (nvl(last_coa_id, -1) <> x_coa_id)
92 OR (nvl(last_qual_text, 'X') <> x_qual_text)
93 ) THEN
94 IF (NOT fnd_flex_apis.get_qualifier_segnum(
95 appl_id => 101,
96 key_flex_code => 'GL#',
97 structure_number => x_coa_id,
98 flex_qual_name => x_qual_text,
99 segment_number => seg_num)
100 ) THEN
101 app_exception.raise_exception;
102 END IF;
103
104 last_coa_id := x_coa_id;
105 last_qual_text := x_qual_text;
106 END IF;
107
108 -- Get the description
109 IF (fnd_flex_keyval.validate_segs(
110 operation => 'CHECK_SEGMENTS',
111 appl_short_name => 'SQLGL',
112 key_flex_code => 'GL#',
113 structure_number => x_coa_id,
114 concat_segments => x_segment_val,
115 displayable => x_qual_text,
116 allow_nulls => TRUE,
117 allow_orphans => TRUE)) THEN
118 null;
119 END IF;
120
121 RETURN(fnd_flex_keyval.segment_description(seg_num));
122 END get_description;
123
124 FUNCTION get_any_seg_description(
125 x_coa_id NUMBER,
126 x_qual_text VARCHAR2,
127 x_segment_val VARCHAR2,
128 x_seg_num NUMBER
129 ) RETURN VARCHAR2 IS
130 BEGIN
131
132 -- Get the description
133 IF (fnd_flex_keyval.validate_segs(
134 operation => 'CHECK_SEGMENTS',
135 appl_short_name => 'SQLGL',
136 key_flex_code => 'GL#',
137 structure_number => x_coa_id,
138 concat_segments => x_segment_val,
139 displayable => x_qual_text,
140 allow_nulls => TRUE,
141 allow_orphans => TRUE)) THEN
142 null;
143 END IF;
144
145 RETURN(fnd_flex_keyval.segment_description(x_seg_num));
146 END get_any_seg_description;
147
148 FUNCTION get_coa_name(coa_id NUMBER) RETURN VARCHAR2 IS
149 coa_name VARCHAR2(30) ;
150 BEGIN
151 SELECT id_flex_structure_name
152 INTO coa_name
153 FROM fnd_id_flex_structures_vl
154 WHERE application_id=101
155 AND id_flex_code='GL#'
156 AND id_flex_num=coa_id;
157
158 RETURN(coa_name);
159 END get_coa_name;
160
161 -- lifted from GL_FORMSINFO package
162 PROCEDURE get_coa_info (x_chart_of_accounts_id IN NUMBER,
163 x_segment_delimiter IN OUT NOCOPY VARCHAR2,
164 x_enabled_segment_count IN OUT NOCOPY NUMBER,
165 x_segment_order_by IN OUT NOCOPY VARCHAR2,
166 x_accseg_segment_num IN OUT NOCOPY NUMBER,
167 x_accseg_app_col_name IN OUT NOCOPY VARCHAR2,
168 x_accseg_left_prompt IN OUT NOCOPY VARCHAR2,
169 x_balseg_segment_num IN OUT NOCOPY NUMBER,
170 x_balseg_app_col_name IN OUT NOCOPY VARCHAR2,
171 x_balseg_left_prompt IN OUT NOCOPY VARCHAR2,
172 x_ieaseg_segment_num IN OUT NOCOPY NUMBER,
173 x_ieaseg_app_col_name IN OUT NOCOPY VARCHAR2,
174 x_ieaseg_left_prompt IN OUT NOCOPY VARCHAR2) IS
175
176 CURSOR seg_count IS
177 SELECT segment_num, application_column_name
178 FROM fnd_id_flex_segments
179 WHERE application_id = 101
180 AND id_flex_code = 'GL#'
181 AND enabled_flag = 'Y'
182 AND id_flex_num = x_chart_of_accounts_id
183 ORDER BY segment_num;
184
185 dumdum BOOLEAN := FALSE;
186 x_seg_name VARCHAR2(30);
187 x_value_set VARCHAR2(60);
188
189 BEGIN
190
191 -- Identify the natural account and balancing segments
192 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
193 101, 'GL#', x_chart_of_accounts_id,
194 'GL_ACCOUNT', x_accseg_segment_num);
195 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
196 101, 'GL#', x_chart_of_accounts_id,
197 'GL_BALANCING', x_balseg_segment_num);
198 dumdum := FND_FLEX_APIS.get_qualifier_segnum(
199 101, 'GL#', x_chart_of_accounts_id,
200 'GL_INTERCOMPANY', x_ieaseg_segment_num);
201
202 -- Get the segment delimiter
203 x_segment_delimiter := FND_FLEX_APIS.get_segment_delimiter(
204 101, 'GL#', x_chart_of_accounts_id);
205
206 -- Count 'em up and string 'em together
207 x_enabled_segment_count := 0;
208 FOR r IN seg_count LOOP
209 -- How many enabled segs are there?
210 x_enabled_segment_count := seg_count%ROWCOUNT;
211 -- Record the order by string
212 IF seg_count%ROWCOUNT = 1 THEN
213 x_segment_order_by := r.application_column_name;
214 ELSE
215 x_segment_order_by := x_segment_order_by||
216 ','||
217 r.application_column_name;
218 END IF;
219 -- If this is either the accseg or balseg, get more info
220 IF r.segment_num = x_accseg_segment_num THEN
221 IF (FND_FLEX_APIS.get_segment_info(
222 101, 'GL#', x_chart_of_accounts_id,
223 r.segment_num, x_accseg_app_col_name,
224 x_seg_name, x_accseg_left_prompt, x_value_set)) THEN
225 null;
226 END IF;
227 ELSIF r.segment_num = x_balseg_segment_num THEN
228 IF (FND_FLEX_APIS.get_segment_info(
229 101, 'GL#', x_chart_of_accounts_id,
230 r.segment_num, x_balseg_app_col_name,
231 x_seg_name, x_balseg_left_prompt, x_value_set)) THEN
232 null;
233 END IF;
234 ELSIF r.segment_num = x_ieaseg_segment_num THEN
235 IF (FND_FLEX_APIS.get_segment_info(
236 101, 'GL#', x_chart_of_accounts_id,
237 r.segment_num, x_ieaseg_app_col_name,
238 x_seg_name, x_ieaseg_left_prompt, x_value_set)) THEN
239 null;
240 END IF;
241 END IF;
242 END LOOP;
243
244 EXCEPTION
245 WHEN OTHERS THEN
246 app_exception.raise_exception;
247 END get_coa_info;
248
249
250 FUNCTION get_sd_description_sql (
251 x_coa_id IN NUMBER,
252 x_pos IN NUMBER,
253 x_seg_num IN NUMBER,
254 x_seg_val IN VARCHAR2 ) RETURN VARCHAR2 IS
255 seg_desc VARCHAR2(1000);
256 BEGIN
257 /* Summarized rows have segment number as '-1'. They don't have any
258 descriptions. So, returns null. */
259 if (x_seg_num = -1) then
260 if (x_pos = 1) then
261 /* caching for balancing */
262 g_coa := x_coa_id;
263 g_seg_num1 := x_seg_num;
264 g_seg_val1 := x_seg_val;
265 g_desc1 := '';
266 else
267 /* caching for drilldown */
268 g_coa := x_coa_id;
269 g_seg_num2 := x_seg_num;
270 g_seg_val2 := x_seg_val;
271 g_desc2 := '';
272 end if;
273 return null;
274 end if;
275
276 /* Check if the current row has the exact same values as the previous
277 one. If so, we just pass back the cached description. */
278 if (x_pos = 1) then
279 if ( g_coa = x_coa_id
280 and g_seg_num1 = x_seg_num
281 and g_seg_val1 = x_seg_val) then
282 return g_desc1;
283 else
284 /* caching for balancing */
285 g_coa := x_coa_id;
286 g_seg_num1 := x_seg_num;
287 g_seg_val1 := x_seg_val;
288 end if;
289 else
290 if ( g_coa = x_coa_id
291 and g_seg_num2 = x_seg_num
292 and g_seg_val2 = x_seg_val) then
293 return g_desc2;
294 else
295 /* caching for drilldown */
296 g_coa := x_coa_id;
297 g_seg_num2 := x_seg_num;
298 g_seg_val2 := x_seg_val;
299 end if;
300 end if;
301
302 /* No match with previous row. Need to get description from SQL. */
303 seg_desc := get_description_sql(x_coa_id, x_seg_num, x_seg_val);
304
305 /* cache up the segment value description */
306 if (x_pos = 1) then
307 g_desc1 := seg_desc;
308 else
309 g_desc2 := seg_desc;
310 end if;
311
312 return seg_desc;
313
314 END get_sd_description_sql;
315
316
317 FUNCTION get_description_sql (
318 x_coa_id IN NUMBER,
319 x_seg_num IN NUMBER,
320 x_seg_val IN VARCHAR2 ) RETURN VARCHAR2 IS
321 v_vsid NUMBER;
322 v_type VARCHAR2(1);
323 v_desc_table VARCHAR2(240);
324 v_val_col VARCHAR2(240);
325 v_desc_col VARCHAR2(240);
326 v_desc_sql VARCHAR2(500);
327 desc_cursor INTEGER;
328 seg_desc VARCHAR2(1000);
329 dummy NUMBER;
330 row_count NUMBER := 0;
331 v_sql_stmt VARCHAR2(2000) ;
332 v_cursor INTEGER;
333 v_return INTEGER;
334
335 l_seg_num number;
336 l_coa_id number;
337 l_seg_val varchar2(240);
338 l_vset_id number;
339
340 BEGIN
341 BEGIN
342 /* Retrieve the value set id and validation type
343 for the segment */
344 SELECT S.flex_value_set_id,
345 VS.validation_type
346 INTO v_vsid,
347 v_type
348 FROM FND_ID_FLEX_SEGMENTS S,
349 FND_FLEX_VALUE_SETS VS
350 WHERE S.id_flex_num = x_coa_id
351 AND S.application_id = 101
352 AND S.id_flex_code = 'GL#'
353 AND S.segment_num = x_seg_num
354 AND S.enabled_flag = 'Y'
355 AND VS.flex_value_set_id = S.flex_value_set_id;
356 EXCEPTION
357 /* Wrong combination of chart of accout id and
358 segment number. */
359 WHEN no_data_found THEN
360 raise INVALID_SEGNUM;
361 END;
362
363 /* Determine the relevant tables to obtain the segment value
364 description. */
365 IF ( v_type = 'F' ) THEN
366 /* table validation segment */
367 SELECT application_table_name,
368 value_column_name,
369 meaning_column_name
370 INTO v_desc_table,
371 v_val_col,
372 v_desc_col
373 FROM FND_FLEX_VALIDATION_TABLES
374 WHERE flex_value_set_id = v_vsid;
375
376 /* if no description column is defined,
377 just return null. */
378 IF ( v_desc_col is null ) THEN
379 return (NULL);
380 END IF;
381 ELSE
382 /* dependent or independent segment */
383 v_desc_table := 'FND_FLEX_VALUES_VL';
384 v_val_col := 'flex_value';
385 v_desc_col := 'description';
386 END IF;
387
388 /* Retrieve the segment value description. */
389 v_desc_sql :=
390 'SELECT ' || v_desc_col ||
391 ' FROM ' || v_desc_table ||
392 ' WHERE ' || v_val_col || ' = :seg_val ';
393 /* For FND_FLEX_VALUES table, we have to filter values by
394 flex_value_set_id */
395 IF ( v_type <> 'F' ) THEN
396 v_desc_sql := v_desc_sql ||
397 'AND flex_value_set_id = :vset_id';
398 END IF;
399
400 BEGIN
401
402 /* Introduced the cursor to fix bug# 3051914 */
403
404 v_cursor := dbms_sql.open_cursor;
405 dbms_sql.parse( v_cursor, v_desc_sql, dbms_sql.native);
406 dbms_sql.bind_variable(v_cursor, 'seg_val' , x_seg_val );
407
408 IF ( v_type <> 'F' ) THEN
409 dbms_sql.bind_variable(v_cursor, 'vset_id' , v_vsid );
410
411 END IF;
412
413
414 dbms_sql.define_column(v_cursor ,1,seg_desc,1000);
415 v_return := dbms_sql.execute (v_cursor ) ;
416 v_return := dbms_sql.fetch_rows ( v_cursor );
417
418 if v_return = 0 then
419 raise no_data_found;
420 end if;
421
422 dbms_sql.column_value(v_cursor,1,seg_desc);
423
424 /* EXECUTE IMMEDIATE v_desc_sql
425 INTO seg_desc
426 USING x_seg_val; */
427
428 dbms_sql.close_cursor(v_cursor);
429
430 EXCEPTION
431 WHEN no_data_found THEN
432 dbms_sql.close_cursor(v_cursor);
433 return (NULL);
434 WHEN OTHERS THEN
435 dbms_sql.close_cursor(v_cursor);
436 return (NULL);
437 END;
438
439 RETURN seg_desc;
440
441 END get_description_sql;
442
443 FUNCTION get_summary_flag (x_value_set_id NUMBER,
444 x_segment_value VARCHAR2) RETURN VARCHAR2 IS
445 sum_flag VARCHAR2(2);
446
447 val_type VARCHAR2(1);
448 val_table VARCHAR2(240);
449 val_col VARCHAR2(240);
450 sum_col VARCHAR2(240);
451 stmt VARCHAR2(500);
452 BEGIN
453 SELECT validation_type
454 INTO val_type
455 FROM fnd_flex_value_sets
456 WHERE flex_value_set_id = x_value_set_id;
457
458 IF (val_type = 'F') THEN
459 -- table validated segment
460 SELECT application_table_name, value_column_name, summary_column_name
461 INTO val_table, val_col, sum_col
462 FROM fnd_flex_validation_tables
463 WHERE flex_value_set_id = x_value_set_id;
464
465 -- if no summary column is defined, return 'N'
466 IF (sum_col = 'N') THEN
467 return ('N');
468 END IF;
469 ELSE
470 -- dependent or independent segment
471 val_table := 'FND_FLEX_VALUES';
472 val_col := 'flex_value';
473 sum_col := 'summary_flag';
474 END IF;
475
476 -- get the summary flag
477 stmt := 'SELECT ' || sum_col ||
478 ' FROM ' || val_table ||
479 ' WHERE ' || val_col || ' = :seg_val';
480 -- for FND_FLEX_VALUES, need to filter by flex_value_set_id
481 IF (val_type <> 'F') THEN
482 stmt := stmt || ' AND flex_value_set_id = :vs_id';
483 EXECUTE IMMEDIATE stmt INTO sum_flag
484 USING x_segment_value, x_value_set_id;
485 ELSE
486 EXECUTE IMMEDIATE stmt INTO sum_flag USING x_segment_value;
487 END IF;
488
489 RETURN sum_flag;
490 END get_summary_flag;
491
492 FUNCTION get_parent_from_children(
493 vs_id IN NUMBER,
494 ancestor IN VARCHAR2,
495 child_low IN VARCHAR2,
496 child_high IN VARCHAR2,
497 parent_num IN NUMBER) RETURN VARCHAR2 IS
498 CURSOR get_single_parent IS
499 SELECT min(parent_flex_value), count(*)
500 FROM fnd_flex_value_norm_hierarchy
501 WHERE flex_value_set_id = vs_id
502 AND child_flex_value_low = child_low
503 AND child_flex_value_high = child_high
504 AND range_attribute = 'C';
505
506 CURSOR get_parent IS
507 SELECT parent_flex_value
508 FROM fnd_flex_value_norm_hierarchy
509 WHERE flex_value_set_id = vs_id
510 AND child_flex_value_low = child_low
511 AND child_flex_value_high = child_high
512 AND range_attribute = 'C'
513 AND (parent_flex_value, child_flex_value_low,
514 child_flex_value_high) IN
515 (SELECT parent_flex_value, child_flex_value_low,
516 child_flex_value_high
517 FROM fnd_flex_value_norm_hierarchy
518 START with flex_value_set_id = vs_id
519 AND parent_flex_value = ancestor
520 CONNECT BY flex_value_set_id = vs_id
521 AND parent_flex_value BETWEEN PRIOR child_flex_value_low
522 AND PRIOR child_flex_value_high
523 AND PRIOR range_attribute = 'P')
524 ORDER BY parent_flex_value;
525
526 flexval VARCHAR2(25);
527 num_possibles NUMBER;
528 last_flexval VARCHAR2(25);
529 BEGIN
530 OPEN get_single_parent;
531 FETCH get_single_parent INTO flexval, num_possibles;
532 CLOSE get_single_parent;
533
534 IF (num_possibles < 2) THEN
535 RETURN(flexval);
536 END IF;
537
538 OPEN get_parent;
539
540 FOR i IN 1..parent_num LOOP
541 FETCH get_parent INTO flexval;
542
543 EXIT WHEN get_parent%NOTFOUND;
544 last_flexval := flexval;
545 END LOOP;
546
547 IF (get_parent%NOTFOUND) THEN
548 CLOSE get_parent;
549 RETURN(last_flexval);
550 ELSE
551 CLOSE get_parent;
552 RETURN(flexval);
553 END IF;
554 END get_parent_from_children;
555
556 FUNCTION Get_Concat_Description(
557 x_coa_id NUMBER,
558 x_ccid NUMBER,
559 x_enforce_value_security VARCHAR2
560 ) RETURN VARCHAR IS
561 l_descp VARCHAR2(4000);
562 l_delimiter VARCHAR2(1);
563 l_num_segs NUMBER;
564 l_security_code VARCHAR2(10);
565 BEGIN
566 IF (x_enforce_value_security = 'N') THEN
567 l_security_code := 'IGNORE';
568 ELSE
569 l_security_code := 'ENFORCE';
570 END IF;
571
572 IF (NOT fnd_flex_keyval.validate_ccid(
573 appl_short_name => 'SQLGL',
574 key_flex_code => 'GL#',
575 structure_number => x_coa_id,
576 combination_id => x_ccid,
577 -- security => 'ENFORCE')) THEN
578 security=>l_security_code)) THEN
579 -- return something unlikely to be valid that the caller can check
580 return ('=====#####=====');
581 END IF;
582
583 l_delimiter := fnd_flex_keyval.segment_delimiter;
584 l_num_segs := fnd_flex_keyval.segment_count;
585 l_descp := '';
586
587 FOR i IN 1..l_num_segs LOOP
588 IF i <> 1 THEN
589 l_descp := l_descp || l_delimiter;
590 END IF;
591 l_descp := l_descp || fnd_flex_keyval.segment_description(i);
592 END LOOP;
593
594 return(l_descp);
595
596 END Get_Concat_Description;
597
598
599 FUNCTION get_qualifier_segnum(
600 x_key_flex_code VARCHAR2,
601 x_chart_of_accounts_id NUMBER,
602 x_flex_qual_name VARCHAR2
603 ) RETURN NUMBER IS
604 l_seg_pos NUMBER;
605 flag BOOLEAN := FALSE;
606 BEGIN
607 flag := FND_FLEX_APIS.get_qualifier_segnum(
608 101,
609 x_key_flex_code,
610 x_chart_of_accounts_id,
611 x_flex_qual_name,
612 l_seg_pos
613 );
614
615 IF (flag = FALSE) THEN
616 RETURN 0;
617 ELSE
618 return l_seg_pos;
619 END IF;
620 END get_qualifier_segnum;
621
622
623 FUNCTION get_validation_error_message(x_coa_id NUMBER,
624 x_ccid NUMBER) RETURN VARCHAR IS
625 BEGIN
626 IF (NOT fnd_flex_keyval.validate_ccid(
627 appl_short_name => 'SQLGL',
628 key_flex_code => 'GL#',
629 structure_number => x_coa_id,
630 combination_id => x_ccid,
631 security => 'ENFORCE')) THEN
632 -- return the error message
633 RETURN fnd_flex_keyval.error_message;
634 END IF;
635
636 RETURN NULL;
637
638 END get_validation_error_message;
639
640 END gl_flexfields_pkg;