[Home] [Help]
PACKAGE BODY: APPS.JG_GL_ACCT_HIER_PKG
Source
1 PACKAGE BODY JG_GL_ACCT_HIER_PKG AS
2 /* $Header: jgglachb.pls 120.3.12010000.2 2008/11/20 19:48:14 pakumare ship $ */
3
4 --
5 -- PRIVATE GLOBAL VARIABLES
6 --
7 g_value_set_id NUMBER;
8 g_acct_level NUMBER;
9
10 -- the original detail account
11 g_detail_acct VARCHAR2(25);
12 -- the detail account after processing (i.e. adding account delimiters)
13 g_detail_level_acct VARCHAR2(40);
14
15 g_level1_acct VARCHAR2(40);
16 g_level1_acct_desc VARCHAR2(240);
17 g_level2_acct VARCHAR2(40);
18 g_level2_acct_desc VARCHAR2(240);
19 g_level3_acct VARCHAR2(40);
20 g_level3_acct_desc VARCHAR2(240);
21 g_level4_acct VARCHAR2(40);
22 g_level4_acct_desc VARCHAR2(240);
23 g_level5_acct VARCHAR2(40);
24 g_level5_acct_desc VARCHAR2(240);
25 g_level6_acct VARCHAR2(40);
26 g_level6_acct_desc VARCHAR2(240);
27 g_level7_acct VARCHAR2(40);
28 g_level7_acct_desc VARCHAR2(240);
29 g_level8_acct VARCHAR2(40);
30 g_level8_acct_desc VARCHAR2(240);
31 g_level9_acct VARCHAR2(40);
32 g_level9_acct_desc VARCHAR2(240);
33 g_level10_acct VARCHAR2(40);
34 g_level10_acct_desc VARCHAR2(240);
35
36 -- account delimiter for Greek report
37 g_delimiter VARCHAR2(1);
38
39 --
40 -- PRIVATE FUNCTIONS
41 --
42
43 PROCEDURE insert_delimiter IS
44 l_width_1 NUMBER;
45 l_width_2 NUMBER;
46 l_width_3 NUMBER;
47 l_width_4 NUMBER;
48 l_width_5 NUMBER;
49 l_width_6 NUMBER;
50 l_width_7 NUMBER;
51 l_width_8 NUMBER;
52 l_width_9 NUMBER;
53 l_width_10 NUMBER;
54 BEGIN
55 IF (g_delimiter IS NULL) THEN
56 RETURN;
57 END IF;
58
59 l_width_1 := 0;
60 l_width_2 := 0;
61 l_width_3 := 0;
62 l_width_4 := 0;
63 l_width_5 := 0;
64 l_width_6 := 0;
65 l_width_7 := 0;
66 l_width_8 := 0;
67 l_width_9 := 0;
68 l_width_10 := 0;
69
70 IF (g_level1_acct IS NOT NULL) THEN
71 l_width_1 := length(g_level1_acct);
72 END IF;
73
74 IF (g_level2_acct IS NOT NULL) THEN
75 l_width_2 := length(g_level2_acct);
76
77 g_level2_acct :=
78 substr(g_level2_acct, 1, l_width_1) ||
79 g_delimiter ||
80 substr(g_level2_acct, l_width_1 + 1);
81
82 g_detail_level_acct :=
83 substr(g_detail_acct, 1, l_width_1) ||
84 g_delimiter ||
85 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
86 g_delimiter ||
87 substr(g_detail_acct, l_width_2 + 1);
88 END IF;
89
90 IF (g_level3_acct IS NOT NULL) THEN
91 l_width_3 := length(g_level3_acct);
92
93 g_level3_acct :=
94 substr(g_level3_acct, 1, l_width_1) ||
95 g_delimiter ||
96 substr(g_level3_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
97 g_delimiter ||
98 substr(g_level3_acct, l_width_2 + 1);
99
100 g_detail_level_acct :=
101 substr(g_detail_acct, 1, l_width_1) ||
102 g_delimiter ||
103 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
104 g_delimiter ||
105 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
106 g_delimiter ||
107 substr(g_detail_acct, l_width_3 + 1);
108 END IF;
109
110 IF (g_level4_acct IS NOT NULL) THEN
111 l_width_4 := length(g_level4_acct);
112
113 g_level4_acct :=
114 substr(g_level4_acct, 1, l_width_1) ||
115 g_delimiter ||
116 substr(g_level4_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
117 g_delimiter ||
118 substr(g_level4_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
119 g_delimiter ||
120 substr(g_level4_acct, l_width_3 + 1);
121
122 g_detail_level_acct :=
123 substr(g_detail_acct, 1, l_width_1) ||
124 g_delimiter ||
125 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
126 g_delimiter ||
127 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
128 g_delimiter ||
129 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
130 g_delimiter ||
131 substr(g_detail_acct, l_width_4 + 1);
132 END IF;
133
134 IF (g_level5_acct IS NOT NULL) THEN
135 l_width_5 := length(g_level5_acct);
136
137 g_level5_acct :=
138 substr(g_level5_acct, 1, l_width_1) ||
139 g_delimiter ||
140 substr(g_level5_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
141 g_delimiter ||
142 substr(g_level5_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
143 g_delimiter ||
144 substr(g_level5_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
145 g_delimiter ||
146 substr(g_level5_acct, l_width_4 + 1);
147
148 g_detail_level_acct :=
149 substr(g_detail_acct, 1, l_width_1) ||
150 g_delimiter ||
151 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
152 g_delimiter ||
153 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
154 g_delimiter ||
155 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
156 g_delimiter ||
157 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
158 g_delimiter ||
159 substr(g_detail_acct, l_width_5 + 1);
160 END IF;
161
162 IF (g_level6_acct IS NOT NULL) THEN
163 l_width_6 := length(g_level6_acct);
164
165 g_level6_acct :=
166 substr(g_level6_acct, 1, l_width_1) ||
167 g_delimiter ||
168 substr(g_level6_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
169 g_delimiter ||
170 substr(g_level6_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
171 g_delimiter ||
172 substr(g_level6_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
173 g_delimiter ||
174 substr(g_level6_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
175 g_delimiter ||
176 substr(g_level6_acct, l_width_5 + 1);
177
178 g_detail_level_acct :=
179 substr(g_detail_acct, 1, l_width_1) ||
180 g_delimiter ||
181 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
182 g_delimiter ||
183 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
184 g_delimiter ||
185 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
186 g_delimiter ||
187 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
188 g_delimiter ||
189 substr(g_detail_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
190 g_delimiter ||
191 substr(g_detail_acct, l_width_6 + 1);
192 END IF;
193
194 IF (g_level7_acct IS NOT NULL) THEN
195 l_width_7 := length(g_level7_acct);
196
197 g_level7_acct :=
198 substr(g_level7_acct, 1, l_width_1) ||
199 g_delimiter ||
200 substr(g_level7_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
201 g_delimiter ||
202 substr(g_level7_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
203 g_delimiter ||
204 substr(g_level7_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
205 g_delimiter ||
206 substr(g_level7_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
207 g_delimiter ||
208 substr(g_level7_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
209 g_delimiter ||
210 substr(g_level7_acct, l_width_6 + 1);
211
212 g_detail_level_acct :=
213 substr(g_detail_acct, 1, l_width_1) ||
214 g_delimiter ||
215 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
216 g_delimiter ||
217 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
218 g_delimiter ||
219 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
220 g_delimiter ||
221 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
222 g_delimiter ||
223 substr(g_detail_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
224 g_delimiter ||
225 substr(g_detail_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
226 g_delimiter ||
227 substr(g_detail_acct, l_width_7 + 1);
228 END IF;
229
230 IF (g_level8_acct IS NOT NULL) THEN
231 l_width_8 := length(g_level8_acct);
232
233 g_level8_acct :=
234 substr(g_level8_acct, 1, l_width_1) ||
235 g_delimiter ||
236 substr(g_level8_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
237 g_delimiter ||
238 substr(g_level8_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
239 g_delimiter ||
240 substr(g_level8_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
241 g_delimiter ||
242 substr(g_level8_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
243 g_delimiter ||
244 substr(g_level8_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
245 g_delimiter ||
246 substr(g_level8_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
247 g_delimiter ||
248 substr(g_level8_acct, l_width_7 + 1);
249
250 g_detail_level_acct :=
251 substr(g_detail_acct, 1, l_width_1) ||
252 g_delimiter ||
253 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
254 g_delimiter ||
255 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
256 g_delimiter ||
257 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
258 g_delimiter ||
259 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
260 g_delimiter ||
261 substr(g_detail_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
262 g_delimiter ||
263 substr(g_detail_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
264 g_delimiter ||
265 substr(g_detail_acct, l_width_7 + 1, l_width_8 - l_width_7) ||
266 g_delimiter ||
267 substr(g_detail_acct, l_width_8 + 1);
268 END IF;
269
270 IF (g_level9_acct IS NOT NULL) THEN
271 l_width_9 := length(g_level9_acct);
272
273 g_level9_acct :=
274 substr(g_level9_acct, 1, l_width_1) ||
275 g_delimiter ||
276 substr(g_level9_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
277 g_delimiter ||
278 substr(g_level9_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
279 g_delimiter ||
280 substr(g_level9_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
281 g_delimiter ||
282 substr(g_level9_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
283 g_delimiter ||
284 substr(g_level9_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
285 g_delimiter ||
286 substr(g_level9_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
287 g_delimiter ||
288 substr(g_level9_acct, l_width_7 + 1, l_width_8 - l_width_7) ||
289 g_delimiter ||
290 substr(g_level9_acct, l_width_8 + 1);
291
292 g_detail_level_acct :=
293 substr(g_detail_acct, 1, l_width_1) ||
294 g_delimiter ||
295 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
296 g_delimiter ||
297 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
298 g_delimiter ||
299 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
300 g_delimiter ||
301 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
302 g_delimiter ||
303 substr(g_detail_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
304 g_delimiter ||
305 substr(g_detail_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
306 g_delimiter ||
307 substr(g_detail_acct, l_width_7 + 1, l_width_8 - l_width_7) ||
308 g_delimiter ||
309 substr(g_detail_acct, l_width_8 + 1, l_width_9 - l_width_8) ||
310 g_delimiter ||
311 substr(g_detail_acct, l_width_9 + 1);
312 END IF;
313
314 IF (g_level10_acct IS NOT NULL) THEN
315 l_width_10 := length(g_level10_acct);
316
317 g_level10_acct :=
318 substr(g_level10_acct, 1, l_width_1) ||
319 g_delimiter ||
320 substr(g_level10_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
321 g_delimiter ||
325 g_delimiter ||
322 substr(g_level10_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
323 g_delimiter ||
324 substr(g_level10_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
326 substr(g_level10_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
327 g_delimiter ||
328 substr(g_level10_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
329 g_delimiter ||
330 substr(g_level10_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
331 g_delimiter ||
332 substr(g_level10_acct, l_width_7 + 1, l_width_8 - l_width_7) ||
333 g_delimiter ||
334 substr(g_level10_acct, l_width_8 + 1, l_width_9 - l_width_8) ||
335 g_delimiter ||
336 substr(g_level10_acct, l_width_9 + 1);
337
338 g_detail_level_acct :=
339 substr(g_detail_acct, 1, l_width_1) ||
340 g_delimiter ||
341 substr(g_detail_acct, l_width_1 + 1, l_width_2 - l_width_1) ||
342 g_delimiter ||
343 substr(g_detail_acct, l_width_2 + 1, l_width_3 - l_width_2) ||
344 g_delimiter ||
345 substr(g_detail_acct, l_width_3 + 1, l_width_4 - l_width_3) ||
346 g_delimiter ||
347 substr(g_detail_acct, l_width_4 + 1, l_width_5 - l_width_4) ||
348 g_delimiter ||
349 substr(g_detail_acct, l_width_5 + 1, l_width_6 - l_width_5) ||
350 g_delimiter ||
351 substr(g_detail_acct, l_width_6 + 1, l_width_7 - l_width_6) ||
352 g_delimiter ||
353 substr(g_detail_acct, l_width_7 + 1, l_width_8 - l_width_7) ||
354 g_delimiter ||
355 substr(g_detail_acct, l_width_8 + 1, l_width_9 - l_width_8) ||
356 g_delimiter ||
357 substr(g_detail_acct, l_width_9 + 1, l_width_10 - l_width_9) ||
358 g_delimiter ||
359 substr(g_detail_acct, l_width_10 + 1);
360 END IF;
361 END insert_delimiter;
362
363
364 PROCEDURE init_levels_by_hier(p_value_set_id IN NUMBER,
365 p_report_acct_level IN NUMBER,
366 p_detail_acct IN VARCHAR2,
367 p_detail_acct_desc IN VARCHAR2,
368 p_acct_delimiter IN VARCHAR2) IS
369 -- With a single top parent, should be able to go from detail up to the
370 -- top parent with an unique path.
371 CURSOR get_parent_vals IS
372 SELECT parent_value, parent_value_description, hierarchy_level
373 FROM JG_ZZ_GL_ACCT_HIER_GT
374 START WITH child_value = p_detail_acct
375 AND flex_value_set_id = p_value_set_id
376 -- AND summary_flag = 'Y'
377 -- AND hierarchy_level IS NOT NULL
378 CONNECT BY PRIOR parent_value = child_value
379 AND flex_value_set_id = p_value_set_id
380 AND summary_flag = 'Y'
381 AND hierarchy_level IS NOT NULL
382 ORDER BY hierarchy_level, parent_value;
383
384 l_parent_value VARCHAR2(25);
385 l_parent_desc VARCHAR2(240);
386 l_hier_level NUMBER;
387 l_prev_rec_level NUMBER;
388 BEGIN
389 IF ( g_value_set_id IS NOT NULL AND g_value_set_id = p_value_set_id
390 AND g_acct_level IS NOT NULL AND g_acct_level = p_report_acct_level
391 AND g_detail_acct IS NOT NULL AND g_detail_acct = p_detail_acct) THEN
392 -- Init request is the same as the last
393 RETURN;
394 END IF;
395
396 g_value_set_id := p_value_set_id;
397 g_acct_level := p_report_acct_level;
398 g_detail_acct := p_detail_acct;
399
400 OPEN get_parent_vals;
401
402 l_hier_level := 0;
403 l_prev_rec_level := 0;
404 LOOP
405 FETCH get_parent_vals INTO l_parent_value, l_parent_desc, l_hier_level;
406
407 IF (get_parent_vals%NOTFOUND AND l_hier_level < p_report_acct_level) THEN
408 -- We haven't filled in all wanted levels, so use the detail as last
409 l_parent_value := p_detail_acct;
410 l_parent_desc := p_detail_acct_desc;
411 l_hier_level := l_hier_level + 1;
412 END IF;
413
414 IF (l_hier_level <> l_prev_rec_level) THEN
415 -- in case there are multiple parents at the same level,
416 -- use the first one retrieved
417 IF (l_hier_level = 1) THEN
418 g_level1_acct := l_parent_value;
419 g_level1_acct_desc := l_parent_desc;
420 ELSIF (l_hier_level = 2) THEN
421 g_level2_acct := l_parent_value;
422 g_level2_acct_desc := l_parent_desc;
423 ELSIF (l_hier_level = 3) THEN
424 g_level3_acct := l_parent_value;
425 g_level3_acct_desc := l_parent_desc;
426 ELSIF (l_hier_level = 4) THEN
427 g_level4_acct := l_parent_value;
428 g_level4_acct_desc := l_parent_desc;
429 ELSIF (l_hier_level = 5) THEN
430 g_level5_acct := l_parent_value;
431 g_level5_acct_desc := l_parent_desc;
432 ELSIF (l_hier_level = 6) THEN
433 g_level6_acct := l_parent_value;
434 g_level6_acct_desc := l_parent_desc;
435 ELSIF (l_hier_level = 7) THEN
436 g_level7_acct := l_parent_value;
437 g_level7_acct_desc := l_parent_desc;
438 ELSIF (l_hier_level = 8) THEN
442 g_level9_acct := l_parent_value;
439 g_level8_acct := l_parent_value;
440 g_level8_acct_desc := l_parent_desc;
441 ELSIF (l_hier_level = 9) THEN
443 g_level9_acct_desc := l_parent_desc;
444 ELSIF (l_hier_level = 10) THEN
445 g_level10_acct := l_parent_value;
446 g_level10_acct_desc := l_parent_desc;
447 END IF;
448
449 l_prev_rec_level := l_hier_level;
450 END IF;
451
452 EXIT WHEN (get_parent_vals%NOTFOUND OR
453 l_hier_level = p_report_acct_level);
454 END LOOP;
455 CLOSE get_parent_vals;
456
457 -- Reset the rest of the levels to null.
458 WHILE (l_hier_level + 1 <= 10) LOOP
459
460 l_hier_level := l_hier_level + 1;
461
462 IF (l_hier_level = 1) THEN
463 g_level1_acct := NULL;
464 g_level1_acct_desc := NULL;
465 ELSIF (l_hier_level = 2) THEN
466 g_level2_acct := NULL;
467 g_level2_acct_desc := NULL;
468 ELSIF (l_hier_level = 3) THEN
469 g_level3_acct := NULL;
470 g_level3_acct_desc := NULL;
471 ELSIF (l_hier_level = 4) THEN
472 g_level4_acct := NULL;
473 g_level4_acct_desc := NULL;
474 ELSIF (l_hier_level = 5) THEN
475 g_level5_acct := NULL;
476 g_level5_acct_desc := NULL;
477 ELSIF (l_hier_level = 6) THEN
478 g_level6_acct := NULL;
479 g_level6_acct_desc := NULL;
480 ELSIF (l_hier_level = 7) THEN
481 g_level7_acct := NULL;
482 g_level7_acct_desc := NULL;
483 ELSIF (l_hier_level = 8) THEN
484 g_level8_acct := NULL;
485 g_level8_acct_desc := NULL;
486 ELSIF (l_hier_level = 9) THEN
487 g_level9_acct := NULL;
488 g_level9_acct_desc := NULL;
489 ELSIF (l_hier_level = 10) THEN
490 g_level10_acct := NULL;
491 g_level10_acct_desc := NULL;
492 END IF;
493 END LOOP;
494
495 -- Insert account delimiters
496 g_delimiter := p_acct_delimiter;
497 insert_delimiter;
498 END init_levels_by_hier;
499
500
501 --
502 -- PUBLIC FUNCTIONS
503 --
504
505 PROCEDURE populate_acct_hier_table(p_value_set_id IN NUMBER,
506 p_top_parent_acct IN VARCHAR2) IS
507 CURSOR top_parent IS
508 SELECT flex_value, description, enabled_flag, summary_flag,
509 substr(compiled_value_attributes,5,1) account_type,
510 flex_value_set_id, start_date_active, end_date_active
511 FROM FND_FLEX_VALUES_VL
512 WHERE flex_value_set_id = p_value_set_id
513 AND DECODE(p_top_parent_acct,'','1',flex_value) = DECODE(p_top_parent_acct,'','1',p_top_parent_acct);
514
515 CURSOR next_level_flex(v_level NUMBER) IS
516 SELECT f.flex_value, f.description, f.enabled_flag, f.summary_flag,
517 substr(f.compiled_value_attributes,5,1) account_type,
518 f.flex_value_set_id, f.start_date_active, f.end_date_active,
519 nvl((SELECT distinct 1 FROM JG_ZZ_GL_ACCT_HIER_GT gt
520 WHERE gt.summary_flag = 'Y'
521 AND nvl(gt.hierarchy_level, -1) = v_level
522 AND gt.child_value = f.flex_value
523 AND EXISTS( SELECT distinct 1 FROM JG_ZZ_GL_ACCT_HIER_GT gt2
524 WHERE gt2.PARENT_VALUE = gt.CHILD_VALUE)),0) record_exists
525 FROM FND_FLEX_VALUES_VL f
526 WHERE flex_value_set_id = p_value_set_id
527 AND flex_value IN (SELECT gt.child_value
528 FROM JG_ZZ_GL_ACCT_HIER_GT gt
529 WHERE gt.summary_flag = 'Y'
530 AND nvl(gt.hierarchy_level, -1) = v_level);
531
532 CURSOR flex_child(v_flex_parent VARCHAR2) IS
533 SELECT b.flex_value, b.summary_flag
534 FROM FND_FLEX_VALUES_VL b, FND_FLEX_VALUE_NORM_HIERARCHY a
535 WHERE a.flex_value_set_id = p_value_set_id
536 AND a.parent_flex_value = v_flex_parent
537 AND b.flex_value_set_id = p_value_set_id
538 AND b.flex_value IN
539 (SELECT c.flex_value
540 FROM FND_FLEX_VALUES c
541 WHERE c.flex_value BETWEEN a.child_flex_value_low
542 AND a.child_flex_value_high
543 AND c.flex_value_set_id = a.flex_value_set_id
544 AND DECODE(a.range_attribute, 'P', 'Y', 'N') = c.summary_flag);
545
546
547 l_level NUMBER;
548 l_num_rows NUMBER;
549 l_done BOOLEAN;
550 l_insert_count NUMBER;
551 BEGIN
552 l_level := 1;
553 l_insert_count := 0;
554
555 -- First level of parent-child relationship from the Top Level Parent
556 FOR val_rec IN top_parent LOOP
557
558 -- Parent value
559 IF (val_rec.summary_flag = 'Y') THEN
560 IF (val_rec.enabled_flag = 'Y') THEN
561
562 FOR child_rec IN flex_child(val_rec.flex_value) LOOP
563 INSERT INTO JG_ZZ_GL_ACCT_HIER_GT
564 (parent_value,
565 parent_value_description,
566 child_value,
567 summary_flag,
568 account_type,
569 hierarchy_level,
570 flex_value_set_id)
571 VALUES
572 (val_rec.flex_value,
573 val_rec.description,
574 child_rec.flex_value,
575 child_rec.summary_flag,
576 val_rec.account_type,
577 l_level,
578 p_value_set_id);
579
580 IF (child_rec.summary_flag = 'Y') THEN
581 l_insert_count := l_insert_count + 1;
582 END IF;
583 END LOOP;
584
585 END IF;
586
587 -- Detail value
588 ELSIF (val_rec.summary_flag = 'N') THEN
589 INSERT INTO JG_ZZ_GL_ACCT_HIER_GT
590 (parent_value,
591 parent_value_description,
592 child_value,
593 summary_flag,
594 account_type,
595 hierarchy_level,
596 flex_value_set_id)
597 VALUES
598 (val_rec.flex_value,
599 val_rec.description,
600 NULL,
601 val_rec.summary_flag,
602 val_rec.account_type,
603 NULL,
604 p_value_set_id);
605
606 END IF;
607
608 l_done := (l_insert_count = 0);
609 END LOOP;
610
611 WHILE (NOT l_done) LOOP
612 -- Possible next level parent exists
613 l_insert_count := 0;
614
615 FOR val_rec IN next_level_flex(l_level) LOOP
616
617 -- Parent value
618 IF (val_rec.summary_flag = 'Y') THEN
619 IF (val_rec.enabled_flag = 'Y') THEN
620
621 FOR child_rec IN flex_child(val_rec.flex_value) LOOP
622
623 IF (val_rec.record_exists = 1) THEN
624 UPDATE JG_ZZ_GL_ACCT_HIER_GT
625 SET hierarchy_level = l_level + 1
626 WHERE parent_value = val_rec.flex_value
627 AND child_value = child_rec.flex_value
628 AND flex_value_set_id = p_value_set_id;
629
630 ELSE
631
632 INSERT INTO JG_ZZ_GL_ACCT_HIER_GT
633 (parent_value,
634 parent_value_description,
635 child_value,
636 summary_flag,
637 account_type,
638 hierarchy_level,
639 flex_value_set_id)
640 VALUES
641 (val_rec.flex_value,
642 val_rec.description,
643 child_rec.flex_value,
644 child_rec.summary_flag,
645 val_rec.account_type,
646 l_level + 1,
647 p_value_set_id);
648 END IF;
649
650 IF (child_rec.summary_flag = 'Y') THEN
651 l_insert_count := l_insert_count + 1;
652 END IF;
653 END LOOP;
654
655 END IF;
656
657 -- Detail value
658 /*ELSIF (val_rec.summary_flag = 'N') THEN
659 INSERT INTO JG_ZZ_GL_ACCT_HIER_GT
660 (parent_value,
661 parent_value_description,
662 child_value,
663 summary_flag,
664 account_type,
665 hierarchy_level,
666 flex_value_set_id)
667 VALUES
668 (val_rec.flex_value,
669 val_rec.description,
670 NULL,
671 val_rec.summary_flag,
672 val_rec.account_type,
673 NULL,
674 p_value_set_id);*/
675
676 END IF;
677 END LOOP;
678
679 l_done := (l_insert_count = 0);
680 l_level := l_level + 1;
681 END LOOP;
682
683 END populate_acct_hier_table;
684
685
686 FUNCTION get_level_acct_value(p_value_set_id IN NUMBER,
687 p_report_acct_level IN NUMBER,
688 p_detail_acct IN VARCHAR2,
689 p_detail_acct_desc IN VARCHAR2,
690 p_acct_delimiter IN VARCHAR2,
691 p_level IN NUMBER)
692 RETURN VARCHAR2 IS
693 l_acct_value VARCHAR2(40);
694 BEGIN
695 init_levels_by_hier(p_value_set_id, p_report_acct_level,
696 p_detail_acct, p_detail_acct_desc,
697 p_acct_delimiter);
698
699 l_acct_value := NULL;
700 IF (p_level = 1) THEN
701 l_acct_value := g_level1_acct;
702 ELSIF (p_level = 2) THEN
703 l_acct_value := g_level2_acct;
704 ELSIF (p_level = 3) THEN
705 l_acct_value := g_level3_acct;
706 ELSIF (p_level = 4) THEN
707 l_acct_value := g_level4_acct;
708 ELSIF (p_level = 5) THEN
709 l_acct_value := g_level5_acct;
710 ELSIF (p_level = 6) THEN
711 l_acct_value := g_level6_acct;
712 ELSIF (p_level = 7) THEN
713 l_acct_value := g_level7_acct;
714 ELSIF (p_level = 8) THEN
715 l_acct_value := g_level8_acct;
716 ELSIF (p_level = 9) THEN
717 l_acct_value := g_level9_acct;
718 ELSIF (p_level = 10) THEN
719 l_acct_value := g_level10_acct;
720 END IF;
721
722 RETURN l_acct_value;
723 END get_level_acct_value;
724
725
726 FUNCTION get_level_acct_desc(p_value_set_id IN NUMBER,
727 p_report_acct_level IN NUMBER,
728 p_detail_acct IN VARCHAR2,
729 p_detail_acct_desc IN VARCHAR2,
730 p_acct_delimiter IN VARCHAR2,
731 p_level IN NUMBER)
732 RETURN VARCHAR2 IS
733 l_acct_val_desc VARCHAR2(240);
734 BEGIN
735 init_levels_by_hier(p_value_set_id, p_report_acct_level,
736 p_detail_acct, p_detail_acct_desc,
737 p_acct_delimiter);
738
739 l_acct_val_desc := NULL;
740 IF (p_level = 1) THEN
741 l_acct_val_desc := g_level1_acct_desc;
742 ELSIF (p_level = 2) THEN
743 l_acct_val_desc := g_level2_acct_desc;
744 ELSIF (p_level = 3) THEN
745 l_acct_val_desc := g_level3_acct_desc;
746 ELSIF (p_level = 4) THEN
747 l_acct_val_desc := g_level4_acct_desc;
748 ELSIF (p_level = 5) THEN
749 l_acct_val_desc := g_level5_acct_desc;
750 ELSIF (p_level = 6) THEN
751 l_acct_val_desc := g_level6_acct_desc;
752 ELSIF (p_level = 7) THEN
753 l_acct_val_desc := g_level7_acct_desc;
754 ELSIF (p_level = 8) THEN
755 l_acct_val_desc := g_level8_acct_desc;
756 ELSIF (p_level = 9) THEN
757 l_acct_val_desc := g_level9_acct_desc;
758 ELSIF (p_level = 10) THEN
759 l_acct_val_desc := g_level10_acct_desc;
760 END IF;
761
762 RETURN l_acct_val_desc;
763 END get_level_acct_desc;
764
765
766 FUNCTION get_delimited_detail_acct(p_value_set_id IN NUMBER,
767 p_report_acct_level IN NUMBER,
768 p_detail_acct IN VARCHAR2,
769 p_detail_acct_desc IN VARCHAR2,
770 p_acct_delimiter IN VARCHAR2)
771 RETURN VARCHAR2 IS
772 BEGIN
773 init_levels_by_hier(p_value_set_id, p_report_acct_level,
774 p_detail_acct, p_detail_acct_desc,
775 p_acct_delimiter);
776
777 RETURN g_detail_level_acct;
778 END get_delimited_detail_acct;
779
780 END JG_GL_ACCT_HIER_PKG;