PACKAGE BODY: APPS.GL_BEST_IND_VALUE

Source

1 PACKAGE BODY gl_best_ind_value AS
2 /* $Header: glubindb.pls 120.7 2005/12/27 21:33:34 vtreiger ship $ */
3 --  -------------------------------------------------
4 --  Functions
5 --  -------------------------------------------------
6 
7    FUNCTION find_ind_value
8       (p_segment_num    IN NUMBER)
9    RETURN VARCHAR2
10    IS
11       l_segment_str    VARCHAR2(3);
12       l_full_segm_str  VARCHAR2(11);
13       l_ind_name       VARCHAR2(30);
14       l_col_name       VARCHAR2(40);
15       l_ind_value      VARCHAR2(30);
16       l_table_owner    VARCHAR2(20);
17       l_schema         VARCHAR2(30);
18       l_status         VARCHAR2(1);
19       l_industry       VARCHAR2(1);
20       l_rval           BOOLEAN;
21 
22 --     cursor tbl_owner is
23 --       select upper(tab.owner)
24 --       from all_tables tab
25 --       where tab.table_name = 'GL_CODE_COMBINATIONS'
26 --       and owner = l_schema;
27 
28      cursor ind_col_name is
29        select c.index_name ind_name, substr(c.column_name,1,40) col_name
30        from all_ind_columns c
31        where
32          c.table_name = 'GL_CODE_COMBINATIONS' and
33          c.table_owner = l_table_owner and
34          c.column_position = 1
35        order by col_name ASC;
36 
37    BEGIN
38 --  --- Get variables values ---
39 
40      l_segment_str := TO_CHAR(p_segment_num);
41      l_segment_str := RTRIM(LTRIM(l_segment_str));
42      l_full_segm_str := 'SEGMENT' || l_segment_str;
43      l_ind_value := '';
44      l_table_owner := '';
45 
46 --  --- Find table owner ---
47      l_rval := fnd_installation.get_app_info(
48                                   'SQLGL', l_status, l_industry, l_schema);
49 
50 --     OPEN tbl_owner;
51 --     FETCH tbl_owner INTO l_table_owner;
52 --     CLOSE tbl_owner;
53 
54      l_table_owner := l_schema;
55 
56 --  --- Loop cursor to find index name ---
57      OPEN ind_col_name;
58 
59      LOOP
60        FETCH ind_col_name INTO l_ind_name, l_col_name;
61        EXIT WHEN ind_col_name%NOTFOUND;
62 
63        IF (l_col_name = l_full_segm_str) THEN
64          l_ind_value := l_ind_name;
65          EXIT;
66        END IF;
67      END LOOP;
68 
69      CLOSE ind_col_name;
70      RETURN(l_ind_value);
71 
72    END find_ind_value;
73 
74 END gl_best_ind_value;