1 package body okc_tree_index as
2 /* $Header: OKCRTRIB.pls 120.0 2005/05/25 19:20:34 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 --
7 -- p_tree_id is only for the case when
8 -- several tree statements included in a single query
9 -- and there is hazard that they will be treated in parallel;
10 -- usually rely on default value of this parameter
11 --
12 --
13 -- default separator is '.', to override use set_separator(p_separator varchar2)
14 --
15 -- default pad character is ' ', to override use set_pad_char(p_pad_char varchar2)
16 --
17 -- use not paded methods only if need
18 --
19
20 TYPE leaf_rec IS RECORD (label varchar2(256));
21 TYPE path_tbl IS TABLE OF leaf_rec INDEX BY BINARY_INTEGER;
22 G_PATH_TBL path_tbl;
23
24 G_SEPARATOR varchar2(3) := '.';
25 G_PAD_CHAR varchar2(3);
26
27 TYPE root_rec IS RECORD (id number);
28 TYPE root_tbl IS TABLE OF root_rec INDEX BY BINARY_INTEGER;
29 G_ROOT_TBL root_tbl;
30
31 TYPE count_rec IS RECORD (idx number, cnt number);
32 TYPE count_tbl IS TABLE OF count_rec INDEX BY BINARY_INTEGER;
33 G_COUNT_TBL count_tbl;
34
35 -- private procedure
36 function get_separator return varchar2 is
37 begin
38 return G_SEPARATOR;
39 end;
40
41 -- private procedure
42 function get_pad_char return varchar2 is
43 begin
44 return G_PAD_CHAR;
45 end;
46
47 -- private procedure
48 function ret_label(p_level number, p_tree_id number) return varchar2 is
49 l_path varchar2(1000);
50 s1 number;
51 s2 number;
52 begin
53 s1 := power(2,p_tree_id-1);
54 s2 := 2*s1;
55 l_path := g_path_tbl(s1).label;
56 for i in 2..p_level loop
57 l_path := l_path||get_separator||g_path_tbl(s1+s2*(i-1)).label;
58 end loop;
59 return l_path;
60 end;
61
62 --
63 -- function get_id constructs tree index for numeric leaf index
64 --
65 -- p_level to pass level from treewalk query
66 -- p_id to pass key entry that will be part of the returned index
67 -- p_tree_id explained at the package top
68
69 function get_id(p_level number, p_id number, p_tree_id number ) return varchar2 is
70 s1 number;
71 s3 number;
72 begin
73 s1 := power(2,p_tree_id-1);
74 s3 := s1+2*s1*(p_level-1);
75 g_path_tbl(s3).label := to_char(p_id);
76 return ret_label(p_level,p_tree_id);
77 end;
78
79 --
80 -- function get_id_lpaded constructs tree index for numeric leaf index
81 -- each key entry will be lpaded up to p_digits size
82 --
83 -- p_level to pass level from treewalk query
84 -- p_id to pass key entry that will be part of the returned index
85 -- p_digits size of key entry lpaded
86 -- p_tree_id explained at the package top
87
88 function get_id_lpaded(p_level number, p_id number, p_digits number, p_tree_id number ) return varchar2 is
89 s1 number;
90 s3 number;
91 begin
92 s1 := power(2,p_tree_id-1);
93 s3 := s1+2*s1*(p_level-1);
94 g_path_tbl(s3).label := lpad(to_char(p_id),p_digits,NVL(get_pad_char,' '));
95 return ret_label(p_level,p_tree_id);
96 end;
97
98 --
99 -- function get_label constructs tree index for character leaf index
100 --
101 -- p_level to pass level from treewalk query
102 -- p_label to pass key entry that will be part of the returned index
103 -- p_tree_id explained at the package top
104
105 function get_label(p_level number, p_label varchar2, p_tree_id number ) return varchar2 is
106 s1 number;
107 s3 number;
108 begin
109 s1 := power(2,p_tree_id-1);
110 s3 := s1+2*s1*(p_level-1);
111 g_path_tbl(s3).label := p_label;
112 return ret_label(p_level,p_tree_id);
113 end;
114
115 --
116 -- function get_label_rpaded constructs tree index for character leaf index
117 -- key entries will be rpaded up to p_characters
118 --
119 -- p_level to pass level from treewalk query
120 -- p_label to pass key entry that will be part of the returned index
121 -- p_characters size of key entry after rpaded
122 -- p_tree_id explained at the package top
123
124 function get_label_rpaded(p_level number, p_label varchar2, p_characters number, p_tree_id number ) return varchar2 is
125 s1 number;
126 s3 number;
127 begin
128 s1 := power(2,p_tree_id-1);
129 s3 := s1+2*s1*(p_level-1);
130 g_path_tbl(s3).label := rpad(p_label,p_characters,NVL(get_pad_char,' '));
131 return ret_label(p_level,p_tree_id);
132 end;
133
134 --
135 -- procedure set_separator
136 --
137 -- to override default separator that is '.'
138 --
139 procedure set_separator(p_separator varchar2) is
140 begin
141 G_SEPARATOR := substr(p_separator,1,3);
142 end;
143
144 --
145 -- procedure set_pad_char
146 --
147 -- to override default pad character that is ' '
148 --
149 procedure set_pad_char(p_pad_char varchar2) is
150 begin
151 G_PAD_CHAR := substr(p_pad_char,1,3);
152 end;
153
154 --
155 -- these procedures are of rare usage
156 --
157 -- set/get root id is only needed for deep nested treewalk queries
158 -- to set something like bind var for the tree root
159 --
160 procedure set_root_id(p_id number,p_tree_id number) is
161 begin
162 G_ROOT_TBL(p_tree_id).id := p_id;
163 end;
164
165 function get_root_id(p_tree_id number ) return number is
166 begin
167 return G_ROOT_TBL(p_tree_id).id;
168 exception when others then
169 return NULL;
170 end;
171
172 --
173 -- function nested_rownum for internal usage only
174 --
175 function nested_rownum(p_instance number, p_idx number, p_cnt number) return number is
176 begin
177 if (p_cnt = 1 or G_COUNT_TBL(p_instance).idx <> p_idx) then
178 G_COUNT_TBL(p_instance).idx := p_idx;
179 G_COUNT_TBL(p_instance).cnt := 1;
180 return 1;
181 end if;
182 G_COUNT_TBL(p_instance).cnt := G_COUNT_TBL(p_instance).cnt + 1;
183 return G_COUNT_TBL(p_instance).cnt;
184 end;
185
186 end;
[Home] [Help]