[Home] [Help]
PACKAGE BODY: APPS.FND_APPTREE
Source
1 PACKAGE BODY fnd_apptree AS
2 /* $Header: AFTREEB.pls 120.2 2005/10/24 06:09:54 mzasowsk ship $ */
3
4 PROCEDURE get_folder_properties (l_folder_id number,
5 l_obj_name OUT NOCOPY /* file.sql.39 change */ varchar2,
6 l_node_label OUT NOCOPY /* file.sql.39 change */ varchar2,
7 l_folder_type OUT NOCOPY /* file.sql.39 change */ varchar2,
8 l_value OUT NOCOPY /* file.sql.39 change */ varchar2,
9 l_sequence OUT NOCOPY /* file.sql.39 change */ number,
10 l_parent_folder_id OUT NOCOPY /* file.sql.39 change */ number,
11 l_user_id OUT NOCOPY /* file.sql.39 change */ number,
12 l_public_flag OUT NOCOPY /* file.sql.39 change */ varchar2) IS
13 cursor props is
14 select obj_name, node_label, folder_type,
15 value, sequence, parent_folder_id, created_by, public_flag
16 from fnd_tree_folders
17 where folder_id = l_folder_id;
18 BEGIN
19 open props;
20 fetch props into l_obj_name, l_node_label, l_folder_type,
21 l_value, l_sequence, l_parent_folder_id, l_user_id, l_public_flag;
22 if props%NOTFOUND then
23 l_obj_name := 'NOT_FOUND';
24 end if;
25 close props;
26 END get_folder_properties;
27
28 FUNCTION insert_folder(l_obj_name VARCHAR2,
29 l_node_label VARCHAR2,
30 l_folder_type VARCHAR2,
31 l_value VARCHAR2,
32 l_parent_folder_id NUMBER,
33 l_public_flag VARCHAR2,
34 l_language VARCHAR2,
35 l_user_id NUMBER,
36 after_folder_id NUMBER default null) RETURN NUMBER IS
37 PRAGMA AUTONOMOUS_TRANSACTION;
38 l_next_seq number;
39 l_folder_id number;
40 l_after_seq number;
41 cursor max_seq_null_parent is
42 select max(sequence)
43 from fnd_tree_folders
44 where created_by = l_user_id
45 and parent_folder_id is null
46 and obj_name = l_obj_name;
47 cursor max_seq_in_parent is
48 select max(sequence)
49 from fnd_tree_folders
50 where parent_folder_id = l_parent_folder_id;
51 cursor seq_of_folder is
52 select sequence
53 from fnd_tree_folders
54 where folder_id = after_folder_id;
55 cursor next_seq_null_parent is
56 select min(sequence)
57 from fnd_tree_folders
58 where created_by = l_user_id
59 and parent_folder_id is null
60 and obj_name = l_obj_name
61 and sequence > l_after_seq;
62 cursor next_seq_in_parent is
63 select min(sequence)
64 from fnd_tree_folders
65 where parent_folder_id = l_parent_folder_id
66 and sequence > l_after_seq;
67 cursor next_folder_id is
68 select fnd_tree_folders_s.nextval
69 from dual;
70 BEGIN
71 --
72 -- Determine the next sequence number.
73 --
74 if after_folder_id is null then
75 --
76 -- Find last sequenced entry in that parent
77 --
78 if l_parent_folder_id is null then
79 open max_seq_null_parent;
80 fetch max_seq_null_parent into l_next_seq;
81 if max_seq_null_parent%NOTFOUND then
82 l_next_seq := 0;
83 end if;
84 close max_seq_null_parent;
85 else
86 open max_seq_in_parent;
87 fetch max_seq_in_parent into l_next_seq;
88 if max_seq_in_parent%NOTFOUND then
89 l_next_seq := 0;
90 end if;
91 close max_seq_in_parent;
92 end if;
93 l_next_seq := nvl(l_next_seq, 0.0) + 1.0;
94 else
95 --
96 -- Find next sequenced entry beyond after_folder_id
97 -- within that parent
98 --
99 open seq_of_folder;
100 fetch seq_of_folder into l_after_seq;
101 if seq_of_folder%NOTFOUND then
102 close seq_of_folder;
103 raise NO_DATA_FOUND;
104 end if;
105 close seq_of_folder;
106
107 if l_parent_folder_id is null then
108 open next_seq_null_parent;
109 fetch max_seq_null_parent into l_next_seq;
110 if next_seq_null_parent%NOTFOUND then
111 l_next_seq := null;
112 end if;
113 close next_seq_null_parent;
114 else
115 open next_seq_in_parent;
116 fetch next_seq_in_parent into l_next_seq;
117 if next_seq_in_parent%NOTFOUND then
118 l_next_seq := null;
119 end if;
120 close next_seq_in_parent;
121 end if;
122 if l_next_seq is null then
123 l_next_seq := l_after_seq + 1.0;
124 else
125 l_next_seq := (l_next_seq + l_after_seq) / 2.0;
126 end if;
127 end if;
128 --
129 -- Get the folder_id from the sequence
130 --
131 open next_folder_id;
132 fetch next_folder_id into l_folder_id;
133 close next_folder_id;
134 --
135 insert into fnd_tree_folders(
136 folder_id,
137 obj_name,
138 node_label,
139 folder_type,
140 value,
141 sequence,
142 parent_folder_id,
143 public_flag,
144 language,
145 creation_date,
146 created_by,
147 last_update_date,
148 last_updated_by)
149 values (
150 l_folder_id,
151 l_obj_name,
152 l_node_label,
153 l_folder_type,
154 l_value,
155 l_next_seq,
156 l_parent_folder_id,
157 l_public_flag,
158 l_language,
159 sysdate,
160 l_user_id,
161 sysdate,
162 l_user_id);
163 COMMIT;
164 return l_folder_id;
165 EXCEPTION
166 when others then
167 return -1;
168 END insert_folder;
169
170 PROCEDURE update_folder (l_folder_id number,
171 l_user_id number,
172 l_obj_name varchar2 default 'APPTREE_NULL',
173 l_node_label VARCHAR2 default 'APPTREE_NULL',
174 l_folder_type VARCHAR2 default 'APPTREE_NULL',
175 l_value VARCHAR2 default 'APPTREE_NULL',
176 l_sequence NUMBER default -99,
177 l_parent_folder_id NUMBER default -99,
178 l_public_flag VARCHAR2 default 'APPTREE_NULL',
179 l_language VARCHAR2 default 'APPTREE_NULL') IS
180 PRAGMA AUTONOMOUS_TRANSACTION;
181 BEGIN
182 update fnd_tree_folders
183 set
184 obj_name = decode(l_obj_name, 'APPTREE_NULL', obj_name, l_obj_name),
185 node_label = decode(l_node_label, 'APPTREE_NULL', node_label, l_node_label),
186 folder_type = decode(l_folder_type, 'APPTREE_NULL', folder_type, l_folder_type),
187 value = decode(l_value, 'APPTREE_NULL', value, l_value),
188 sequence = decode(l_sequence, -99, sequence, l_sequence),
189 parent_folder_id = decode(l_parent_folder_id, -99, parent_folder_id, l_parent_folder_id),
190 public_flag = decode(l_public_flag, 'APPTREE_NULL', public_flag, l_public_flag),
191 language = decode(l_language, 'APPTREE_NULL', language, l_language),
192 last_update_date = sysdate,
193 last_updated_by = l_user_id
194 where
195 folder_id = l_folder_id;
196 COMMIT;
197 END update_folder;
198 FUNCTION unique_name(requested_folder_name varchar2,
199 l_parent_folder_id number,
200 l_obj_name varchar2,
201 l_user_id number) return varchar2 is
202 trouble varchar2(1) := null;
203 cursor matches is
204 select '1'
205 from fnd_tree_folders
206 where ((l_parent_folder_id is null and parent_folder_id is null)
207 or (l_parent_folder_id is not null and parent_folder_id = l_parent_folder_id))
208 and obj_name = l_obj_name
209 and created_by = l_user_id
210 and folder_type IN ('Q', 'F')
211 and node_label = requested_folder_name;
212 BEGIN
213 open matches;
214 fetch matches into trouble;
215 if matches%found then
216 close matches;
217 return 'N';
218 else
219 close matches;
220 return 'Y';
221 end if;
222 END unique_name;
223
224 PROCEDURE delete_folder(l_folder_id number) is
225 PRAGMA AUTONOMOUS_TRANSACTION;
226 BEGIN
227 delete
228 from fnd_tree_folders f
229 where f.folder_id in
230 (select h.folder_id
231 from fnd_tree_folders h
232 start with h.folder_id = l_folder_id
233 connect by prior h.folder_id = h.parent_folder_id);
234 --
235 commit;
236 END delete_folder;
237
238 FUNCTION move_folder( l_folder_id number
239 ,l_parent_folder_id number
240 ,after_folder_id number
241 ,l_user_id number
242 ,l_obj_name varchar) return number is
243 PRAGMA AUTONOMOUS_TRANSACTION;
244 l_next_seq number;
245 l_after_seq number;
246 cursor max_seq_null_parent is
247 select max(sequence)
248 from fnd_tree_folders
249 where created_by = l_user_id
250 and parent_folder_id is null
251 and obj_name = l_obj_name;
252 cursor max_seq_in_parent is
253 select max(sequence)
254 from fnd_tree_folders
255 where parent_folder_id = l_parent_folder_id;
256 cursor seq_of_folder is
257 select sequence
258 from fnd_tree_folders
259 where folder_id = after_folder_id;
260 cursor next_seq_null_parent is
261 select min(sequence)
262 from fnd_tree_folders
263 where created_by = l_user_id
264 and parent_folder_id is null
265 and obj_name = l_obj_name
266 and sequence > l_after_seq;
267 cursor next_seq_in_parent is
268 select min(sequence)
269 from fnd_tree_folders
270 where parent_folder_id = l_parent_folder_id
271 and sequence > l_after_seq;
272 BEGIN
273 --
274 -- Determine the next sequence number.
275 --
276 if after_folder_id is null then
277 --
278 -- Find last sequenced entry in that parent
279 --
280 if l_parent_folder_id is null then
281 open max_seq_null_parent;
282 fetch max_seq_null_parent into l_next_seq;
283 if max_seq_null_parent%NOTFOUND then
284 l_next_seq := 0;
285 end if;
286 close max_seq_null_parent;
287 else
288 open max_seq_in_parent;
289 fetch max_seq_in_parent into l_next_seq;
290 if max_seq_in_parent%NOTFOUND then
291 l_next_seq := 0;
292 end if;
293 close max_seq_in_parent;
294 end if;
295 l_next_seq := nvl(l_next_seq, 0.0) + 1.0;
296 else
297 --
298 -- Find next sequenced entry beyond after_folder_id
299 -- within that parent
300 --
301 open seq_of_folder;
302 fetch seq_of_folder into l_after_seq;
303 if seq_of_folder%NOTFOUND then
304 close seq_of_folder;
305 raise NO_DATA_FOUND;
306 end if;
307 close seq_of_folder;
308
309 if l_parent_folder_id is null then
310 open next_seq_null_parent;
311 fetch max_seq_null_parent into l_next_seq;
312 if next_seq_null_parent%NOTFOUND then
313 l_next_seq := null;
314 end if;
315 close next_seq_null_parent;
316 else
317 open next_seq_in_parent;
318 fetch next_seq_in_parent into l_next_seq;
319 if next_seq_in_parent%NOTFOUND then
320 l_next_seq := null;
321 end if;
322 close next_seq_in_parent;
323 end if;
324 if l_next_seq is null then
325 l_next_seq := l_after_seq + 1.0;
326 else
327 l_next_seq := (l_next_seq + l_after_seq) / 2.0;
328 end if;
329 end if;
330
331 UPDATE fnd_tree_folders
332 SET parent_folder_id = l_parent_folder_id
333 ,sequence = l_next_seq
334 ,last_update_date = sysdate
335 ,last_updated_by = l_user_id
336 WHERE folder_id = l_folder_id;
337 COMMIT;
338 return l_folder_id;
339 EXCEPTION
340 when others then
341 return -1;
342 end move_folder;
343
344
345 END fnd_apptree;