1 PACKAGE BODY ZPB_FEM_UTILS_PKG AS
2 /* $Header: ZPBVFEMB.pls 120.12 2007/12/04 14:39:06 mbhat noship $ */
3
4 TYPE epb_curs_type is REF CURSOR;
5
6 TYPE member_hash_type IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(32);
7
8 ----------------------------------------------------------------------------
9 -- GET_MEMBER_NAME
10 --
11 -- Returns a member's name and description given the dimension ID, member ID
12 -- and member valueset. User primarily for views
13 --
14 -- IN: p_dimension_id - The FEM dimension ID
15 -- p_member_id - The member ID
16 -- p_valueset_id - The member valueset ID
17 --
18 -- OUT: The translated (to current language) name of the member
19 ----------------------------------------------------------------------------
20 function GET_MEMBER_NAME (p_dimension_id NUMBER,
21 p_member_id VARCHAR2,
22 p_valueset_id NUMBER)
23 return VARCHAR2 is
24 l_dim_vl_table FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%type;
25 l_dim_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%type;
26 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
27 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
28 l_ret VARCHAR2(150);
29 l_command VARCHAR2(500);
30
31 l_curs EPB_CURS_TYPE;
32 begin
33 if (p_member_id is null or p_dimension_id is null) then
34 return p_member_id;
35 end if;
36
37 select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL,
38 MEMBER_COL, VALUE_SET_REQUIRED_FLAG
39 into l_dim_vl_table, l_dim_name_col, l_dim_col, l_vs_req
40 from FEM_XDIM_DIMENSIONS
41 where DIMENSION_ID = p_dimension_id;
42
43 l_command := 'select '||l_dim_name_col||' from '||l_dim_vl_table||
44 ' where to_char('||l_dim_col||') = '''||p_member_id||'''';
45
46 if (l_vs_req = 'Y') then
47 if (p_valueset_id is null) then
48 return null;
49 end if;
50 l_command := l_command||' and VALUE_SET_ID = '||p_valueset_id;
51 end if;
52
53 open l_curs for l_command;
54 fetch l_curs into l_ret;
55 close l_curs;
56
57 return l_ret;
58
59 end GET_MEMBER_NAME;
60
61 ----------------------------------------------------------------------------
62 -- GET_MEMBER_DESC
63 --
64 -- Returns a member's description and description given the dimension ID,
65 -- member ID and member valueset. User primarily for views
66 --
67 -- IN: p_dimension_id - The FEM dimension ID
68 -- p_member_id - The member ID
69 -- p_valueset_id - The member valueset ID
70 --
71 -- OUT: The translated (to current language) description of the member
72 ----------------------------------------------------------------------------
73 function GET_MEMBER_DESC (p_dimension_id NUMBER,
74 p_member_id VARCHAR2,
75 p_valueset_id NUMBER)
76 return VARCHAR2 is
77 l_dim_vl_table FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%type;
78 l_dim_desc_col FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%type;
79 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
80 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
81 l_ret VARCHAR2(255);
82 l_command VARCHAR2(500);
83
84 l_curs EPB_CURS_TYPE;
85 begin
86 if (p_member_id is null or p_dimension_id is null) then
87 return p_member_id;
88 end if;
89
90 select MEMBER_VL_OBJECT_NAME, MEMBER_DESCRIPTION_COL,
91 MEMBER_COL, VALUE_SET_REQUIRED_FLAG
92 into l_dim_vl_table, l_dim_desc_col, l_dim_col, l_vs_req
93 from FEM_XDIM_DIMENSIONS
94 where DIMENSION_ID = p_dimension_id;
95
96 l_command := 'select '||l_dim_desc_col||' from '||l_dim_vl_table||
97 ' where to_char('||l_dim_col||') = '''||p_member_id||'''';
98
99 if (l_vs_req = 'Y') then
100 if (p_valueset_id is null) then
101 return null;
102 end if;
103 l_command := l_command||' and VALUE_SET_ID = '||p_valueset_id;
104 end if;
105
106 open l_curs for l_command;
107 fetch l_curs into l_ret;
108 close l_curs;
109
110 return l_ret;
111 end GET_MEMBER_DESC;
112
113 ----------------------------------------------------------------------------
114 -- GET_MEMBERS
115 --
116 -- Returns the name, description pair of the dimension members in the given
117 -- dimension. Expected to be used via a TABLE function call. Function is
118 -- pipelined
119 --
120 -- IN: p_dimension_id - The IF of the dimension to get the members from
121 --
122 -- OUT: ZPB_MEMBER_TABLE_T - each dimension member, description pair
123 ----------------------------------------------------------------------------
124 function GET_MEMBERS (p_dimension_id NUMBER)
125 return ZPB_MEMBER_TABLE_T PIPELINED is
126 l_dim_vl_table FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%type;
127 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
128 l_dim_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%type;
129 l_dim_desc_col FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%type;
130 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
131 l_command VARCHAR2(500);
132 l_ret ZPB_MEMBER_TABLE_OBJ;
133
134 l_curs EPB_CURS_TYPE;
135 begin
136 if (p_dimension_id is null) then
137 return;
138 end if;
139
140 l_ret := ZPB_MEMBER_TABLE_OBJ(null, null, null, null);
141
142 select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL, MEMBER_DESCRIPTION_COL,
143 MEMBER_COL, VALUE_SET_REQUIRED_FLAG
144 into l_dim_vl_table, l_dim_name_col, l_dim_desc_col, l_dim_col, l_vs_req
145 from FEM_XDIM_DIMENSIONS
146 where DIMENSION_ID = p_dimension_id;
147
148 l_command := 'select '||l_dim_col||', '||l_dim_name_col||', '||
149 l_dim_desc_col;
150
151 if (l_vs_req = 'Y') then
152 l_command := l_command||', VALUE_SET_ID';
153 else
154 l_command := l_command||', NULL VALUE_SET_ID';
155 end if;
156
157 l_command := l_command||' from '||l_dim_vl_table;
158
159 open l_curs for l_command;
160 loop
161 fetch l_curs into l_ret.MEMBER_ID, l_ret.NAME,
162 l_ret.DESCRIPTION, l_ret.VALUE_SET_ID;
163 exit when l_curs%NOTFOUND;
164 PIPE ROW(l_ret);
165 end loop;
166 close l_curs;
167
168 return;
169 end GET_MEMBERS;
170
171 ----------------------------------------------------------------------------
172 -- GET_VARCHAR_MEMBERS
173 --
174 -- Same as GET_MEMBERS, but returns the members with varchar ID's
175 --
176 -- IN: p_dimension_id - The IF of the dimension to get the members from
177 --
178 -- OUT: ZPB_MEMBER_TABLE_T - each dimension member, description pair
179 ----------------------------------------------------------------------------
180 function GET_VARCHAR_MEMBERS (p_dimension_id NUMBER)
181 return ZPB_VAR_MEMBER_TABLE_T PIPELINED is
182 l_dim_vl_table FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%type;
183 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
184 l_dim_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%type;
185 l_dim_desc_col FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%type;
186 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
187 l_command VARCHAR2(500);
188 l_ret ZPB_VAR_MEMBER_TABLE_OBJ;
189
190 l_curs EPB_CURS_TYPE;
191 begin
192 if (p_dimension_id is null) then
193 return;
194 end if;
195
196 l_ret := ZPB_VAR_MEMBER_TABLE_OBJ(null, null, null, null);
197
198 select MEMBER_VL_OBJECT_NAME, MEMBER_NAME_COL, MEMBER_DESCRIPTION_COL,
199 MEMBER_COL, VALUE_SET_REQUIRED_FLAG
200 into l_dim_vl_table, l_dim_name_col, l_dim_desc_col, l_dim_col, l_vs_req
201 from FEM_XDIM_DIMENSIONS
202 where DIMENSION_ID = p_dimension_id;
203
204 l_command := 'select to_char('||l_dim_col||'), '||l_dim_name_col||', '||
205 l_dim_desc_col;
206
207 if (l_vs_req = 'Y') then
208 l_command := l_command||', VALUE_SET_ID';
209 else
210 l_command := l_command||', NULL VALUE_SET_ID';
211 end if;
212
213 l_command := l_command||' from '||l_dim_vl_table;
214
215 open l_curs for l_command;
216 loop
217 fetch l_curs into l_ret.MEMBER_ID, l_ret.NAME,
218 l_ret.DESCRIPTION, l_ret.VALUE_SET_ID;
219 exit when l_curs%NOTFOUND;
220 PIPE ROW(l_ret);
221 end loop;
222 close l_curs;
223
224 return;
225 end GET_VARCHAR_MEMBERS;
226
227 ----------------------------------------------------------------------------
228 -- GET_FEM_HIER_MEMBERS
229 --
230 -- Returns the name, description of the top level hierarchy members
231 --
232 -- IN: p_hier_vers_id - The hierarchy version ID
233 -- OUT: ZPB_MEMBER_TABLE_T - each dimension member, description pair
234 ----------------------------------------------------------------------------
235 function GET_TOP_HIER_MEMBERS (p_hier_vers_id IN NUMBER)
236 return ZPB_MEMBER_TABLE_T PIPELINED is
237 l_dimension_id FEM_XDIM_DIMENSIONS.DIMENSION_ID%type;
238 l_hier_table FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%type;
239 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
240 l_ret ZPB_MEMBER_TABLE_OBJ;
241 l_command VARCHAR2(2000);
242
243 l_curs EPB_CURS_TYPE;
244 begin
245
246 l_ret := ZPB_MEMBER_TABLE_OBJ(null, null, null, null);
247
248 select A.DIMENSION_ID, A.VALUE_SET_REQUIRED_FLAG, A.HIERARCHY_TABLE_NAME
249 into l_dimension_id, l_vs_req, l_hier_table
250 from FEM_XDIM_DIMENSIONS A, FEM_HIERARCHIES B, FEM_OBJECT_DEFINITION_B C
251 where A.DIMENSION_ID = B.DIMENSION_ID
252 and B.HIERARCHY_OBJ_ID = C.OBJECT_ID
253 and C.OBJECT_DEFINITION_ID = p_hier_vers_id;
254
255 l_command := 'select PARENT_ID, ZPB_FEM_UTILS_PKG.GET_MEMBER_NAME('||
256 l_dimension_id||', PARENT_ID, ';
257 if (l_vs_req = 'Y') then
258 l_command := l_command||'PARENT_VALUE_SET_ID';
259 else
260 l_command := l_command||'null';
261 end if;
262 l_command := l_command||') PARENT_NAME, ZPB_FEM_UTILS_PKG.GET_MEMBER_DESC('||
263 l_dimension_id||', PARENT_ID, ';
264 if (l_vs_req = 'Y') then
265 l_command := l_command||'PARENT_VALUE_SET_ID';
266 else
267 l_command := l_command||'null';
268 end if;
269 l_command := l_command||') PARENT_DESC';
270 if (l_vs_req = 'Y') then
271 l_command := l_command||', PARENT_VALUE_SET_ID';
272 else
273 l_command := l_command||', null PARENT_VALUE_SET_ID';
274 end if;
275 l_command := l_command||' FROM '||l_hier_table||' WHERE PARENT_ID = CHILD_ID
276 and PARENT_DEPTH_NUM = 1
277 and HIERARCHY_OBJ_DEF_ID = '||p_hier_vers_id;
278
279 open l_curs for l_command;
280 loop
281 fetch l_curs into l_ret.MEMBER_ID, l_ret.NAME, l_ret.DESCRIPTION,
282 l_ret.VALUE_SET_ID;
283 exit when l_curs%NOTFOUND;
284
285 PIPE ROW(l_ret);
286 end loop;
287
288 return;
289 end GET_TOP_HIER_MEMBERS;
290
291 ----------------------------------------------------------------------------
292 -- GET_BUSAREA_HIERARCHIES
293 --
294 -- Returns the different hierarchy ID's, version IDs, and whether the
295 -- version should be considered the "effective" version. Function is
296 -- pipelined
297 --
298 -- OUT: ZPB_HIER_VERS_T - each dimension member, description pair
299 ----------------------------------------------------------------------------
300 function GET_BUSAREA_HIERARCHIES(p_business_area in number,
301 p_version_type in varchar2)
302 return ZPB_HIER_VERS_T PIPELINED
303 is
304 l_ret ZPB_HIER_VERS_OBJ;
305 l_business_area NUMBER;
306 l_count NUMBER;
307 cursor hiers is
308 select A.HIERARCHY_ID, C.OBJECT_DEFINITION_ID, A.KEEP_VERSION,
309 A.NUMBER_OF_VERSIONS, A.VERSION_ID,
310 A.LOGICAL_DIM_ID
311 from ZPB_BUSAREA_HIERARCHIES A, ZPB_BUSAREA_VERSIONS B,
312 FEM_OBJECT_DEFINITION_B C
313 where A.VERSION_ID = B.VERSION_ID
314 and B.VERSION_TYPE = p_version_type
315 and B.BUSINESS_AREA_ID = l_business_area
316 and A.HIERARCHY_ID = C.OBJECT_ID
317 and C.EFFECTIVE_START_DATE < sysdate
318 and C.EFFECTIVE_END_DATE > sysdate;
319
320 cursor hier_spec_vers(l_vers number,
321 l_logical_dim_id number,
322 l_hier number) is
323 select HIER_VERSION_ID
324 from ZPB_BUSAREA_HIER_VERSIONS
325 where VERSION_ID = l_vers
326 and LOGICAL_DIM_ID = l_logical_dim_id
327 and HIERARCHY_ID = l_hier;
328
329 cursor hier_last_vers(l_hier number) is
330 select OBJECT_DEFINITION_ID
331 from FEM_OBJECT_DEFINITION_B
332 where OBJECT_ID = l_hier
333 and EFFECTIVE_START_DATE < sysdate
334 order by EFFECTIVE_END_DATE DESC;
335 begin
336 l_ret := ZPB_HIER_VERS_OBJ(null, null, null, null);
337 l_business_area := nvl(p_business_area,
338 sys_context('ZPB_CONTEXT', 'business_area_id'));
339 for each in hiers loop
340
341 l_ret.LOGICAL_DIM_ID := each.LOGICAL_DIM_ID;
342 l_ret.HIERARCHY_ID := each.HIERARCHY_ID;
343 l_ret.VERSION_ID := each.OBJECT_DEFINITION_ID;
344 l_ret.CURRENT_VERSION := 'Y';
345
346 PIPE ROW(l_ret);
347
348 if (each.KEEP_VERSION = 'L') then
349 l_count := 1;
350 for each_vers in hier_last_vers(each.HIERARCHY_ID) loop
351
352 l_ret.LOGICAL_DIM_ID := each.LOGICAL_DIM_ID;
353 l_ret.HIERARCHY_ID := each.HIERARCHY_ID;
354 l_ret.VERSION_ID := each_vers.OBJECT_DEFINITION_ID;
355 l_ret.CURRENT_VERSION := 'N';
356
357 PIPE ROW(l_ret);
358
359 l_count := l_count+1;
360 exit when (l_count > each.NUMBER_OF_VERSIONS);
361 end loop;
362 elsif (each.KEEP_VERSION = 'S') then
363 for each_vers in hier_spec_vers(each.VERSION_ID,
364 each.LOGICAL_DIM_ID,
365 each.HIERARCHY_ID) loop
366
367 l_ret.LOGICAL_DIM_ID := each.LOGICAL_DIM_ID;
368 l_ret.HIERARCHY_ID := each.HIERARCHY_ID;
369 l_ret.VERSION_ID := each_vers.HIER_VERSION_ID;
370 l_ret.CURRENT_VERSION := 'N';
371 PIPE ROW(l_ret);
372
373 end loop;
374 end if;
375 end loop;
376
377 return;
378 end GET_BUSAREA_HIERARCHIES;
379
380 ----------------------------------------------------------------------------
381 -- GET_HIERARCHY_MEMBERS
382 --
383 -- Returns the hierarchy (and hier version) member information for a given
384 -- dimension
385 -- Replaced IN parameter p_dimension_id with p_logical_dim_id
386 -- for "Consistent Dimension"
387 --
388 -- OUT: ZPB_HIER_MEMBER_T - each hierarchy node information
389 ----------------------------------------------------------------------------
390 function GET_HIERARCHY_MEMBERS(p_logical_dim_id IN NUMBER,
391 p_business_area IN NUMBER,
395 l_ret ZPB_HIER_MEMBER_OBJ;
392 p_version_type IN VARCHAR2)
393 return ZPB_HIER_MEMBER_T PIPELINED
394 is
396 l_business_area ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
397
398 cursor cache(p_business_area_id NUMBER, p_logical_dim_id NUMBER) is
399 select HIERARCHY_ID, VERSION_ID, PARENT_ID, CHILD_ID, PARENT_DEPTH,
400 CHILD_DEPTH, PARENT_GROUP, CHILD_GROUP, DISPLAY_ORDER,
401 LOGICAL_DIM_ID
402 from ZPB_HIER_MEMBERS
403 where BUSINESS_AREA_ID = p_business_area_id
404 and LOGICAL_DIM_ID = p_logical_dim_id
405 and PARENT_INCLUDE_TYPE in ('Y', 'A', 'D')
406 and CHILD_INCLUDE_TYPE in ('Y', 'A', 'D');
407 begin
408 l_business_area := nvl(p_Business_area,
409 sys_context('ZPB_CONTEXT', 'business_area_id'));
410
411 l_ret := ZPB_HIER_MEMBER_OBJ(null, null, null, null, null,
412 null, null, null, null, null);
413
414 for each in cache(l_business_area, p_logical_dim_id) loop
415 l_ret := ZPB_HIER_MEMBER_OBJ(each.LOGICAL_DIM_ID, each.HIERARCHY_ID, each.VERSION_ID,
416 each.PARENT_ID, each.CHILD_ID,
417 each.PARENT_DEPTH, each.CHILD_DEPTH,
418 each.PARENT_GROUP, each.CHILD_GROUP,
419 each.DISPLAY_ORDER);
420 PIPE ROW(l_ret);
421 end loop;
422 return;
423
424 end GET_HIERARCHY_MEMBERS;
425
426 ----------------------------------------------------------------------------
427 -- GET_OPERATION
428 --
429 -- Private function to return the symbolic operator given the FND_LOOKUPS
430 -- operator name. Used in GET_LIST_DIM/HIER_MEMBERS
431 --
432 -- IN: p_operator - The FND_LOOKUPS.LOOKUP_CODE name of the operator
433 -- OUT: The symbolic name (<, =, etc) for that operator
434 ----------------------------------------------------------------------------
435 function GET_OPERATION(p_operator IN VARCHAR2) return VARCHAR2
436 is
437 l_ret VARCHAR2(10);
438 begin
439 if (p_operator is null or p_operator = 'EQ') then
440 l_ret := '=';
441 elsif (p_operator = 'GT') then
442 l_ret := '>';
443 elsif (p_operator = 'GE') then
444 l_ret := '>=';
445 elsif (p_operator = 'LT') then
446 l_ret := '<';
447 elsif (p_operator = 'LE') then
448 l_ret := '<=';
449 elsif (p_operator = 'NE') then
450 l_ret := '<>';
451 else l_ret := '=';
452 end if;
453 return l_ret;
454 end GET_OPERATION;
455
456 ----------------------------------------------------------------------------
457 -- GET_LIST_DIM_MEMBERS
458 --
459 -- Returns the members of a list dimension for a business area
460 --
461 -- IN: p_dimension_id - The dimension ID to get the hier members for
462 -- p_business_area - Option Business Area ID. Defaults to sys_context
463 -- p_version_type - Version draft type
464 -- p_logical_dim_id- Logical Dimension ID for "Consistent Dimension"
465 -- OUT: each dimension member ID
466 ----------------------------------------------------------------------------
467 function GET_LIST_DIM_MEMBERS(p_dimension_id IN NUMBER,
468 p_logical_dim_id IN NUMBER,
469 p_business_area IN NUMBER,
470 p_version_type IN VARCHAR2)
471 return ZPB_VAR_MEMBER_TABLE_T PIPELINED is
472 l_curs EPB_CURS_TYPE;
473 l_count NUMBER;
474 l_count2 NUMBER;
475 l_ret ZPB_VAR_MEMBER_TABLE_OBJ;
476 l_vset_id NUMBER;
477 l_command VARCHAR2(16000);
478 l_sel_command VARCHAR2(1000);
479 l_from_command VARCHAR2(4000);
480 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
481 l_dim_vl_table FEM_XDIM_DIMENSIONS.MEMBER_VL_OBJECT_NAME%type;
482 l_attr_table FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%type;
483 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
484 l_dim_name_col FEM_XDIM_DIMENSIONS.MEMBER_NAME_COL%type;
485 l_dim_desc_col FEM_XDIM_DIMENSIONS.MEMBER_DESCRIPTION_COL%type;
486 l_pers_flag FEM_XDIM_DIMENSIONS.HIER_EDITOR_MANAGED_FLAG%type;
487 l_use_cond ZPB_BUSAREA_DIMENSIONS.USE_MEMBER_CONDITIONS%type;
488 l_business_area ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
489 l_operation ZPB_BUSAREA_CONDITIONS.OPERATION%type;
490
491 cursor conditions is
492 select B.ATTRIBUTE_ID, D.VERSION_ID, B.VALUE, B.VALUE_SET_ID,
493 B.ATTRIBUTE_VALUE_COLUMN_NAME COL_NAME, B.OPERATION,
494 B.LOGICAL_DIM_ID, B.DIMENSION_ID
495 from
496 ZPB_BUSAREA_CONDITIONS_V B,
497 ZPB_BUSAREA_VERSIONS C,
498 FEM_DIM_ATTR_VERSIONS_B D
499 where
500 B.ATTRIBUTE_ID = D.ATTRIBUTE_ID
501 and D.DEFAULT_VERSION_FLAG = 'Y'
502 and B.VERSION_ID = C.VERSION_ID
503 and B.LOGICAL_DIM_ID = p_logical_dim_id
504 and C.VERSION_TYPE = p_version_type
505 and C.BUSINESS_AREA_ID = l_business_area
506 and B.DIMENSION_ID = p_dimension_id;
507 begin
508 l_business_area := nvl(p_Business_area,
509 sys_context('ZPB_CONTEXT', 'business_area_id'));
510
514 -- Hardcoded for line account types and ledgers dim
511 l_ret := ZPB_VAR_MEMBER_TABLE_OBJ(null, null, null, null);
512
513 --
515 --
516 if (p_dimension_id = 32 or p_dimension_id = 7) then
517 select A.VALUE_SET_REQUIRED_FLAG,
518 A.MEMBER_VL_OBJECT_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
519 'N', A.MEMBER_DESCRIPTION_COL, A.MEMBER_NAME_COL,
520 A.HIER_EDITOR_MANAGED_FLAG
521 into l_vs_req, l_dim_vl_table, l_dim_col, l_attr_table, l_use_cond,
522 l_dim_desc_col, l_dim_name_col, l_pers_flag
523 from FEM_XDIM_DIMENSIONS A
524 where A.DIMENSION_ID = p_dimension_id;
525 else
526 select A.VALUE_SET_REQUIRED_FLAG,
527 A.MEMBER_VL_OBJECT_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
528 B.USE_MEMBER_CONDITIONS, A.MEMBER_DESCRIPTION_COL, A.MEMBER_NAME_COL,
529 A.HIER_EDITOR_MANAGED_FLAG
530 into l_vs_req, l_dim_vl_table, l_dim_col, l_attr_table, l_use_cond,
531 l_dim_desc_col, l_dim_name_col, l_pers_flag
532 from FEM_XDIM_DIMENSIONS A,
533 ZPB_BUSAREA_DIMENSIONS B,
534 ZPB_BUSAREA_VERSIONS C
535 where A.DIMENSION_ID = p_dimension_id
536 and A.DIMENSION_ID = B.DIMENSION_ID
537 and B.VERSION_ID = C.VERSION_ID
538 and B.LOGICAL_DIM_ID = p_logical_dim_id
539 and C.VERSION_TYPE = p_version_type
540 and C.BUSINESS_AREA_ID = l_business_area;
541 end if;
542
543 if (l_vs_req = 'Y') then
544 select distinct (A.VALUE_SET_ID)
545 into l_vset_id
546 from FEM_GLOBAL_VS_COMBO_DEFS A, ZPB_BUSAREA_LEDGERS B,
547 FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
548 FEM_DIM_ATTR_VERSIONS_B E, ZPB_BUSAREA_VERSIONS F
549 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
550 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
551 and E.DEFAULT_VERSION_FLAG = 'Y'
552 and E.AW_SNAPSHOT_FLAG = 'N'
553 and C.VERSION_ID = E.VERSION_ID
554 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
555 and B.LEDGER_ID = C.LEDGER_ID
556 and B.VERSION_ID = F.VERSION_ID
557 and A.GLOBAL_VS_COMBO_ID = C.DIM_ATTRIBUTE_NUMERIC_MEMBER
558 and A.DIMENSION_ID = p_dimension_id
559 and F.BUSINESS_AREA_ID = l_business_area
560 and F.VERSION_TYPE = p_version_type;
561 l_ret.VALUE_SET_ID := l_vset_id;
562 end if;
563
564 l_sel_command := 'select to_char(A.'||l_dim_col||'), A.'||l_dim_name_col||
565 ', A.'||l_dim_desc_col||' from '||l_dim_vl_table||' A';
566
567 if (p_dimension_id = 7) then
568 l_sel_command := l_sel_command||
569 ', ZPB_BUSAREA_LEDGERS B, ZPB_BUSAREA_VERSIONS C';
570 l_command := l_command||'
571 where A.'||l_dim_col||' = B.LEDGER_ID
572 and B.VERSION_ID = C.VERSION_ID
573 and C.BUSINESS_AREA_ID = '||l_business_area||'
574 and C.VERSION_TYPE = '''||p_version_type||'''';
575 elsif (l_pers_flag = 'Y' or l_vs_req = 'Y') then
576 l_command := l_command||' where ';
577 if (l_pers_flag = 'Y') then
578 l_command := l_command||'
579 A.PERSONAL_FLAG = ''N''
580 and A.ENABLED_FLAG = ''Y''';
581 if (l_vs_req = 'Y') then
582 l_command := l_command||' and ';
583 end if;
584 end if;
585 if (l_vs_req = 'Y') then
586 l_command := l_command||'A.VALUE_SET_ID = '||l_vset_id;
587 end if;
588 elsif (l_use_cond = 'Y') then
589 --
590 -- Case where dimension has no PERSONAL_FLAG column but attr conditions:
591 -- 1=1 is just to make the below logic work right
592 --
593 l_command := l_command||' where 1=1';
594 end if;
595
596 if (l_use_cond = 'Y') then
597 l_count := 1;
598 for each_cond in conditions loop
599 l_command := l_command||' AND A.'||l_dim_col||' = P'||l_count||
600 '.'||l_dim_col||' AND P'||l_count||'.ATTRIBUTE_ID = '||
601 each_cond.ATTRIBUTE_ID||' AND P'||l_count||'.VERSION_ID = '||
602 each_cond.VERSION_ID;
603 if (l_vs_req = 'Y') then
604 l_command := l_command||' AND A.VALUE_SET_ID = P'||
605 l_count||'.VALUE_SET_ID';
606 end if;
607 l_operation := GET_OPERATION(each_cond.OPERATION);
608 if (each_cond.COL_NAME = 'DIM_ATTRIBUTE_NUMERIC_MEMBER' or
609 each_cond.COL_NAME = 'NUMBER_ASSIGN_VALUE') then
610 l_command := l_command||' and P'||l_count||'.'||each_cond.COL_NAME
611 ||l_operation||each_cond.VALUE;
612 elsif (each_cond.COL_NAME = 'DIM_ATTRIBUTE_VARCHAR_MEMBER' or
613 each_cond.COL_NAME = 'VARCHAR_ASSIGN_VALUE') then
614 l_command := l_command||' and P'||l_count||'.'||each_cond.COL_NAME
615 ||l_operation||''''||each_cond.VALUE||'''';
616 else
617 l_command := l_command||' and P'||l_count||'.'||each_cond.COL_NAME
618 ||l_operation||
619 ' to_date('''||each_cond.VALUE||''', ''YYYY/MM/DD'')';
620 end if;
621 l_count := l_count+1;
622 end loop;
623 end if;
624
625 if (l_use_cond = 'Y') then
626 l_count2 := 1;
627 loop
628 l_from_command := l_from_command||', '||l_attr_table||' P'||l_count2;
629 l_count2 := l_count2+1;
630 exit when l_count <= l_count2;
631 end loop;
635 loop
632 end if;
633
634 open l_curs for l_sel_command||l_from_command||l_command;
636 fetch l_curs into l_ret.MEMBER_ID, l_ret.NAME, l_ret.DESCRIPTION;
637 l_ret.VALUE_SET_ID := l_vset_id;
638 exit when l_curs%NOTFOUND;
639
640 --
641 -- Remove the ANY currency. Bug 4523378
642 --
643 if (p_dimension_id <> 9 or l_ret.MEMBER_ID <> 'ANY') then
644 PIPE ROW (l_ret);
645 end if;
646 end loop;
647 return;
648 end GET_LIST_DIM_MEMBERS;
649
650 ----------------------------------------------------------------------------
651 -- INIT_HIER_MEMBER_CACHE
652 --
653 -- Initializes the cache which is used as part of GET_HIERARCHY_MEMBERS.
654 -- Must be called before you call GET_HIERARCHY_MEMBERS. Will initialize
655 -- only the dimension specified
656 --
657 -- IN: p_dimension_id - The dimension ID to get the hier members for
658 -- p_logical_dim_id- Logical Dimension ID for "Consistent Dimension"
659 -- p_business_area - Option Business Area ID. Defaults to sys_context
660 -- p_version_type - Version draft type
661 ----------------------------------------------------------------------------
662 procedure INIT_HIER_MEMBER_CACHE(p_dimension_id IN NUMBER,
663 p_logical_dim_id IN NUMBER,
664 p_business_area IN NUMBER,
665 p_version_type IN VARCHAR2)
666 is
667 l_command VARCHAR2(16000);
668 l_incl_select VARCHAR2(4000);
669 l_incl_sel_cls VARCHAR2(4000);
670 l_pincl_select VARCHAR2(4000);
671 l_cincl_select VARCHAR2(4000);
672 l_incl_where VARCHAR2(4000);
673 l_c_is_included VARCHAR2(1);
674 l_p_is_included VARCHAR2(1);
675 l_count NUMBER;
676 l_cond_count NUMBER;
677 l_count2 NUMBER;
678 l_pipe BOOLEAN;
679 l_dim_col FEM_XDIM_DIMENSIONS.MEMBER_COL%type;
680 l_dim_b_table FEM_XDIM_DIMENSIONS.MEMBER_B_TABLE_NAME%type;
681 l_hier_table FEM_XDIM_DIMENSIONS.HIERARCHY_TABLE_NAME%type;
682 l_attr_table FEM_XDIM_DIMENSIONS.ATTRIBUTE_TABLE_NAME%type;
683 l_vs_req FEM_XDIM_DIMENSIONS.VALUE_SET_REQUIRED_FLAG%type;
684 l_pers_flag FEM_XDIM_DIMENSIONS.HIER_EDITOR_MANAGED_FLAG%type;
685 l_use_cond ZPB_BUSAREA_DIMENSIONS.USE_MEMBER_CONDITIONS%type;
686 l_cond_anc ZPB_BUSAREA_DIMENSIONS.CONDITIONS_INCL_ANC%type;
687 l_cond_desc ZPB_BUSAREA_DIMENSIONS.CONDITIONS_INCL_DESC%type;
688 l_top_mbrs ZPB_BUSAREA_HIERARCHIES.INCLUDE_ALL_TOP_MEMBERS%type;
689 l_operation ZPB_BUSAREA_CONDITIONS.OPERATION%type;
690 l_vset_id FEM_GLOBAL_VS_COMBO_DEFS.VALUE_SET_ID%type;
691 l_ret ZPB_HIER_MEMBER_OBJ;
692
693 l_curs EPB_CURS_TYPE;
694 l_member_hash MEMBER_HASH_TYPE;
695 l_null_hash MEMBER_HASH_TYPE;
696
697 l_business_area ZPB_BUSINESS_AREAS.BUSINESS_AREA_ID%type;
698
699 cursor hiers is
700 select A.HIERARCHY_ID, A.VERSION_ID, A.CURRENT_VERSION,
701 C.INCLUDE_ALL_TOP_MEMBERS, C.INCLUDE_ALL_LEVELS,
702 A.LOGICAL_DIM_ID
703 from table(ZPB_FEM_UTILS_PKG.GET_BUSAREA_HIERARCHIES
704 (l_business_area, p_version_type)) A,
705 ZPB_BUSAREA_HIERARCHIES C,
706 ZPB_BUSAREA_VERSIONS D
707 where
708 A.LOGICAL_DIM_ID = p_logical_dim_id
709 and A.HIERARCHY_ID = C.HIERARCHY_ID
710 and C.VERSION_ID = D.VERSION_ID
711 and D.BUSINESS_AREA_ID = l_business_area
712 and D.VERSION_TYPE = p_version_type
713 order by INCLUDE_ALL_TOP_MEMBERS ASC;
714
715 cursor conditions is
716 select B.ATTRIBUTE_ID, D.VERSION_ID, B.VALUE, B.VALUE_SET_ID,
717 B.ATTRIBUTE_VALUE_COLUMN_NAME COL_NAME, B.OPERATION
718 from
719 ZPB_BUSAREA_CONDITIONS_V B,
720 ZPB_BUSAREA_VERSIONS C,
721 FEM_DIM_ATTR_VERSIONS_B D
722 where
723 B.ATTRIBUTE_ID = D.ATTRIBUTE_ID
724 and D.DEFAULT_VERSION_FLAG = 'Y'
725 and B.VERSION_ID = C.VERSION_ID
726 and B.LOGICAL_DIM_ID = p_logical_dim_id
727 and C.VERSION_TYPE = p_version_type
728 and C.BUSINESS_AREA_ID = l_business_area
729 and B.DIMENSION_ID = p_dimension_id;
730
731 cursor members(p_hierarchy NUMBER,p_hier_vers NUMBER,p_vset VARCHAR2) is
732 select decode(p_vset, 'Y', A.VALUE_SET_ID||'_'||A.MEMBER_ID,
733 A.MEMBER_ID) MEMBER_ID
734 from ZPB_BUSAREA_HIER_MEMBERS A,
735 ZPB_BUSAREA_VERSIONS B
736 where A.HIERARCHY_ID = p_hierarchy
737 and nvl(A.HIER_VERSION_ID, -1) = nvl(p_hier_vers, -1)
738 and A.VERSION_ID = B.VERSION_ID
739 and A.LOGICAL_DIM_ID = p_logical_dim_id
740 and B.VERSION_TYPE = p_version_type
741 and B.BUSINESS_AREA_ID = l_business_area;
742
743 cursor anc_depth (p_hierarchy NUMBER, p_hier_vers NUMBER) is
744 select distinct PARENT_DEPTH
745 from ZPB_HIER_MEMBERS
746 where HIERARCHY_ID = p_hierarchy
747 and nvl(VERSION_ID,-1) = nvl(p_hier_vers,-1)
751 order by PARENT_DEPTH DESC;
748 and BUSINESS_AREA_ID = l_business_area
749 and DIMENSION_ID = p_dimension_id
750 and LOGICAL_DIM_ID = p_logical_dim_id
752
753 begin
754 l_business_area := nvl(p_Business_area,
755 sys_context('ZPB_CONTEXT', 'business_area_id'));
756
757 l_ret := ZPB_HIER_MEMBER_OBJ(null, null, null, null, null,
758 null, null, null, null, null);
759
760 select A.HIERARCHY_TABLE_NAME, A.VALUE_SET_REQUIRED_FLAG,
761 A.MEMBER_B_TABLE_NAME, A.MEMBER_COL, A.ATTRIBUTE_TABLE_NAME,
762 B.USE_MEMBER_CONDITIONS, B.CONDITIONS_INCL_ANC, B.CONDITIONS_INCL_DESC,
763 A.HIER_EDITOR_MANAGED_FLAG
764 into l_hier_table, l_vs_req, l_dim_b_table, l_dim_col, l_attr_table,
765 l_use_cond, l_cond_anc, l_cond_desc, l_pers_flag
766 from FEM_XDIM_DIMENSIONS A,
767 ZPB_BUSAREA_DIMENSIONS B,
768 ZPB_BUSAREA_VERSIONS C
769 where A.DIMENSION_ID = p_dimension_id
770 and A.DIMENSION_ID = B.DIMENSION_ID
771 and B.LOGICAL_DIM_ID = p_logical_dim_id
772 and B.VERSION_ID = C.VERSION_ID
773 and C.VERSION_TYPE = p_version_type
774 and C.BUSINESS_AREA_ID = l_business_area;
775
776 if (l_vs_req = 'Y') then
777 select distinct (A.VALUE_SET_ID)
778 into l_vset_id
779 from FEM_GLOBAL_VS_COMBO_DEFS A, ZPB_BUSAREA_LEDGERS B,
780 FEM_LEDGERS_ATTR C, FEM_DIM_ATTRIBUTES_B D,
781 FEM_DIM_ATTR_VERSIONS_B E, ZPB_BUSAREA_VERSIONS F
782 where D.ATTRIBUTE_VARCHAR_LABEL = 'GLOBAL_VS_COMBO'
783 and D.ATTRIBUTE_ID = E.ATTRIBUTE_ID
784 and E.DEFAULT_VERSION_FLAG = 'Y'
785 and E.AW_SNAPSHOT_FLAG = 'N'
786 and C.VERSION_ID = E.VERSION_ID
787 and C.ATTRIBUTE_ID = D.ATTRIBUTE_ID
788 and B.LEDGER_ID = C.LEDGER_ID
789 and B.VERSION_ID = F.VERSION_ID
790 and A.GLOBAL_VS_COMBO_ID = C.DIM_ATTRIBUTE_NUMERIC_MEMBER
791 and A.DIMENSION_ID = p_dimension_id
792 and F.BUSINESS_AREA_ID = l_business_area
793 and F.VERSION_TYPE = p_version_type;
794 end if;
795
796 --
797 -- If conditions on, then set up a column which will state whether the
798 -- member is directly included by the attribute. Member may be included
799 -- later by child/ancestor condition rule
800 --
801 if (l_use_cond = 'Y') then
802 l_count := 1;
803 for each_cond in conditions loop
804 l_operation := GET_OPERATION(each_cond.OPERATION);
805 l_pincl_select := l_pincl_select||'CASE WHEN P'||l_count||'.'||
806 each_cond.COL_NAME||' '||l_operation;
807 l_cincl_select := l_cincl_select||'CASE WHEN C'||l_count||'.'||
808 each_cond.COL_NAME||' '||l_operation;
809 l_incl_where := l_incl_where||' AND A.PARENT_ID = P'||l_count||'.'||
810 l_dim_col||'(+) AND P'||l_count||'.ATTRIBUTE_ID(+) = '||
811 each_cond.ATTRIBUTE_ID||' AND P'||l_count||'.VERSION_ID(+) = '||
812 each_cond.VERSION_ID||' AND A.CHILD_ID = C'||l_count||'.'||
813 l_dim_col||'(+) AND C'||l_count||'.ATTRIBUTE_ID(+) = '||
814 each_cond.ATTRIBUTE_ID||' AND C'||l_count||'.VERSION_ID(+) = '||
815 each_cond.VERSION_ID;
816 if (l_vs_req = 'Y') then
817 l_incl_where := l_incl_where||' AND A.PARENT_VALUE_SET_ID = P'||
818 l_count||'.VALUE_SET_ID AND A.CHILD_VALUE_SET_ID = C'||l_count||
819 '.VALUE_SET_ID';
820 end if;
821 if (each_cond.COL_NAME = 'DIM_ATTRIBUTE_NUMERIC_MEMBER' or
822 each_cond.COL_NAME = 'NUMBER_ASSIGN_VALUE') then
823 l_pincl_select := l_pincl_select||each_cond.VALUE||' THEN ';
824 l_cincl_select := l_cincl_select||each_cond.VALUE||' THEN ';
825 elsif (each_cond.COL_NAME = 'DIM_ATTRIBUTE_VARCHAR_MEMBER' or
826 each_cond.COL_NAME = 'VARCHAR_ASSIGN_VALUE') then
827 l_pincl_select :=l_pincl_select||''''||each_cond.VALUE||''' THEN ';
828 l_cincl_select :=l_cincl_select||''''||each_cond.VALUE||''' THEN ';
829 else
830 l_pincl_select := l_pincl_select||'to_date('''||
831 each_cond.VALUE||''', ''YYYY/MM/DD'') THEN ';
832 l_cincl_select := l_cincl_select||'to_date('''||
833 each_cond.VALUE||''', ''YYYY/MM/DD'') THEN ';
834 end if;
835 if (l_incl_sel_cls is not null) then
836 l_incl_sel_cls := l_incl_sel_cls||' ELSE ''N'' END';
837 else
838 l_incl_sel_cls := '''Y'' ELSE ''N'' END';
839 end if;
840 l_count := l_count+1;
841 end loop;
842 if (l_count <> 1) then
843 l_incl_select :=l_pincl_select||l_incl_sel_cls||
844 ' PARENT_IS_INCLUDED, '||l_cincl_select||l_incl_sel_cls||
845 ' CHILD_IS_INCLUDED, ';
846 else
847 l_use_cond := 'N'; -- case of bug# 4383969
848 end if;
849
850 l_cond_count := l_count;
851
852 end if;
853 if (l_use_cond <> 'Y') then
854 l_incl_select := '''Y'' PARENT_IS_INCLUDED, ''Y'' CHILD_IS_INCLUDED, ';
855 end if;
856 for each in hiers loop
857 l_ret.HIERARCHY_ID := each.HIERARCHY_ID;
858 l_ret.LOGICAL_DIM_ID := each.LOGICAL_DIM_ID;
859 if (each.CURRENT_VERSION = 'Y') then
860 l_ret.VERSION_ID := null;
864 begin
861 l_top_mbrs := each.INCLUDE_ALL_TOP_MEMBERS;
862 else
863 l_ret.VERSION_ID := each.VERSION_ID;
865 select A.INCLUDE_ALL_TOP_MEMBERS
866 into l_top_mbrs
867 from ZPB_BUSAREA_HIER_VERSIONS A,
868 ZPB_BUSAREA_VERSIONS B
869 where A.VERSION_ID = B.VERSION_ID
870 and B.BUSINESS_AREA_ID = l_business_area
871 and B.VERSION_TYPE = p_version_type
872 and A.HIERARCHY_ID = each.HIERARCHY_ID
873 and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
874 and A.HIER_VERSION_ID = each.VERSION_ID;
875 exception
876 when no_data_found then
877 l_top_mbrs := 'Y';
878 end;
879 end if;
880
881 --
882 -- If top level members, then initialize the member hash with the top
883 -- members. T = top member
884 --
885 l_member_hash := l_null_hash;
886 if (l_top_mbrs = 'N') then
887 for each_member in members(each.HIERARCHY_ID, l_ret.VERSION_ID,
888 l_vs_req) loop
889 l_member_hash(each_member.MEMBER_ID) := 'T';
890 end loop;
891 end if;
892
893 l_command :=
894 'select A.PARENT_DEPTH_NUM,
895 A.CHILD_DEPTH_NUM,
896 A.DISPLAY_ORDER_NUM, '||l_incl_select;
897 if (l_vs_req = 'Y') then
898 l_command :=
899 l_command||'A.PARENT_VALUE_SET_ID||''_''||A.PARENT_ID PARENT_ID,
900 A.CHILD_VALUE_SET_ID||''_''||A.CHILD_ID CHILD_ID,';
901 else
902 l_command := l_command||'to_char(A.PARENT_ID) PARENT_ID,
903 to_char(A.CHILD_ID) CHILD_ID,';
904 end if;
905
906 if (each.CURRENT_VERSION = 'Y') then
907 l_command := l_command||'''H'||each.HIERARCHY_ID||
908 '_LV''||A.PARENT_DEPTH_NUM PARENT_GROUP, ''H'||each.HIERARCHY_ID||
909 '_LV''||A.CHILD_DEPTH_NUM CHILD_GROUP ';
910 else
911 l_command := l_command||'''HV'||each.VERSION_ID||
912 '_LV''||A.PARENT_DEPTH_NUM PARENT_GROUP, ''HV'||each.VERSION_ID||
913 '_LV''||A.CHILD_DEPTH_NUM CHILD_GROUP ';
914 end if;
915
916 l_command := l_command||' FROM '||l_hier_table||' A, '||
917 l_dim_b_table||' B, '||l_dim_b_table||' C';
918 if (l_use_cond = 'Y') then
919 l_count2 := 1;
920 loop
921 l_command := l_command||', '||l_attr_table||' P'||l_count2||
922 ', '||l_attr_table||' C'||l_count2;
923 l_count2 := l_count2+1;
924 exit when l_cond_count <= l_count2;
925 end loop;
926 end if;
927 l_command := l_command||'
928 WHERE A.CHILD_ID = B.'||l_dim_col||'
929 AND A.PARENT_ID = C.'||l_dim_col||'
930 AND C.PERSONAL_FLAG = ''N''
931 AND (A.SINGLE_DEPTH_FLAG = ''Y''
932 OR (A.CHILD_DEPTH_NUM = A.PARENT_DEPTH_NUM
933 AND A.PARENT_DEPTH_NUM = 1))
934 AND A.HIERARCHY_OBJ_DEF_ID = '||each.VERSION_ID;
935 if (each.INCLUDE_ALL_LEVELS <> 'Y') then
936 --
937 -- Only read in members at specified levels:
938 --
939 l_command := l_command||'
940 AND B.DIMENSION_GROUP_ID in
941 (select LEVEL_ID from ZPB_BUSAREA_LEVELS A,
942 ZPB_BUSAREA_VERSIONS B
943 where B.VERSION_TYPE = '''||p_version_type||'''
944 and B.BUSINESS_AREA_ID = '||l_business_area||'
945 and A.VERSION_ID = B.VERSION_ID
946 and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
947 and A.HIERARCHY_ID = '||each.HIERARCHY_ID||')
948 AND C.DIMENSION_GROUP_ID in
949 (select LEVEL_ID from ZPB_BUSAREA_LEVELS A,
950 ZPB_BUSAREA_VERSIONS B
951 where B.VERSION_TYPE = '''||p_version_type||'''
952 and B.BUSINESS_AREA_ID = '||l_business_area||'
953 and A.VERSION_ID = B.VERSION_ID
954 and A.LOGICAL_DIM_ID = '||each.LOGICAL_DIM_ID||'
955 and A.HIERARCHY_ID = '||each.HIERARCHY_ID||')';
956 end if;
957 if (l_pers_flag = 'Y') then
958 --
959 -- No need for enabled flag check. Purposely ignore so that users
960 -- do not disable members in the middle of hierarchies
961 --
962 l_command := l_command||'
963 AND B.PERSONAL_FLAG = ''N''
964 AND C.PERSONAL_FLAG = ''N''';
965 end if;
966 if (l_vs_req = 'Y') then
967 l_command := l_command||'
968 AND A.CHILD_VALUE_SET_ID = B.VALUE_SET_ID
969 AND A.PARENT_VALUE_SET_ID = C.VALUE_SET_ID
970 AND A.CHILD_VALUE_SET_ID = '||l_vset_id||'
971 AND A.PARENT_VALUE_SET_ID = '||l_vset_id;
972 end if;
973 if (l_use_cond = 'Y') then
974 l_command := l_command||l_incl_where;
975 end if;
976
977 l_command := l_command||
978 ' ORDER BY CHILD_DEPTH_NUM ASC, PARENT_IS_INCLUDED DESC';
979
980 --
981 -- open the big SQL query and walk through the results:
982 --
983 open l_curs for l_command;
984 loop
985 fetch l_curs into l_ret.PARENT_DEPTH, l_ret.CHILD_DEPTH,
989 exit when l_curs%NOTFOUND;
986 l_ret.DISPLAY_ORDER, l_p_is_included, l_c_is_included,
987 l_ret.PARENT_ID, l_ret.CHILD_ID, l_ret.PARENT_GROUP,
988 l_ret.CHILD_GROUP;
990
991 l_pipe := false;
992
993 if (l_use_cond <> 'Y' or l_cond_desc <> 'Y') then
994 if (l_top_mbrs = 'N') then -- Members, no Cond
995 if (l_member_hash.EXISTS(l_ret.PARENT_ID) and
996 (l_member_hash(l_ret.PARENT_ID) = 'Y' or
997 l_member_hash(l_ret.PARENT_ID) = 'T')) then
998 l_member_hash(l_ret.CHILD_ID) := 'Y';
999 l_pipe := true;
1000 end if;
1001 else
1002 -- Normal Flow, no conditions/members
1003 l_pipe := true;
1004 end if;
1005 else -- conditions and descendants
1006 if (l_p_is_included = 'Y') then
1007 --
1008 -- Parent meets attribute condition, so add unless sliced
1009 -- out by top level member. If child did not meet condition
1010 -- then mark include type as 'D'
1011 --
1012 if (l_top_mbrs = 'N') then --Cond+members
1013 if (l_member_hash.EXISTS(l_ret.PARENT_ID) and
1014 (l_member_hash(l_ret.PARENT_ID) = 'Y' or
1015 l_member_hash(l_ret.PARENT_ID) = 'T')) then
1016 l_member_hash(l_ret.CHILD_ID) := 'Y';
1017 l_pipe := true;
1018 end if;
1019 else -- Conditions, no top-level members
1020 l_member_hash(l_ret.CHILD_ID) := 'Y';
1021 l_member_hash(l_ret.PARENT_ID) := 'Y';
1022 l_pipe := true;
1023 end if;
1024 if (l_c_is_included <> 'Y') then
1025 l_c_is_included := 'D';
1026 end if;
1027 elsif (l_member_hash.EXISTS(l_ret.PARENT_ID) and
1028 l_member_hash(l_ret.PARENT_ID) = 'Y') then
1029 --
1030 -- The case where the parent does not meet the condition, but
1031 -- was included as a descendant of a member that did meet it
1032 --
1033 l_pipe := true;
1034 l_member_hash(l_ret.CHILD_ID) := 'Y';
1035 l_c_is_included := 'D';
1036 elsif (l_member_hash.EXISTS(l_ret.PARENT_ID) and
1037 l_member_hash(l_ret.PARENT_ID) = 'T') then
1038 --
1039 -- The case where the parent does not meet the condition,
1040 -- nor does any ancestor, but the member is ina hierarchy slice
1041 --
1042 l_pipe := true;
1043 l_member_hash(l_ret.CHILD_ID) := 'T';
1044 l_c_is_included := 'N';
1045 end if;
1046 end if;
1047
1048 if (l_pipe) then
1049 INSERT INTO ZPB_HIER_MEMBERS
1050 (BUSINESS_AREA_ID,
1051 DIMENSION_ID,
1052 LOGICAL_DIM_ID,
1053 HIERARCHY_ID,
1054 VERSION_ID,
1055 PARENT_ID,
1056 CHILD_ID,
1057 PARENT_DEPTH,
1058 CHILD_DEPTH,
1059 PARENT_GROUP,
1060 CHILD_GROUP,
1061 DISPLAY_ORDER,
1062 PARENT_INCLUDE_TYPE,
1063 CHILD_INCLUDE_TYPE)
1064 values
1065 (l_business_area,
1066 p_dimension_id,
1067 l_ret.LOGICAL_DIM_ID,
1068 l_ret.HIERARCHY_ID,
1069 l_ret.VERSION_ID,
1070 l_ret.PARENT_ID,
1071 l_ret.CHILD_ID,
1072 l_ret.PARENT_DEPTH,
1073 l_ret.CHILD_DEPTH,
1074 l_ret.PARENT_GROUP,
1075 l_ret.CHILD_GROUP,
1076 l_ret.DISPLAY_ORDER,
1077 l_p_is_included,
1078 l_c_is_included);
1079 end if;
1080 end loop;
1081
1082 if (each.CURRENT_VERSION = 'Y') then
1083 l_count2 := null;
1084 else
1085 l_count2 := each.VERSION_ID;
1086 end if;
1087
1088 select min(A.PARENT_DEPTH)
1089 into l_count
1090 from ZPB_HIER_MEMBERS A
1091 where A.BUSINESS_AREA_ID = l_business_area
1092 and A.DIMENSION_ID = p_dimension_id
1093 and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1094 and A.HIERARCHY_ID = each.HIERARCHY_ID
1095 and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
1096 if (l_count > 1) then
1097 --
1098 -- Means top levels were chopped off, so reset the depths
1099 -- and create the parent=child rows for top level members
1100 --
1101 update ZPB_HIER_MEMBERS A
1102 set A.PARENT_DEPTH = A.PARENT_DEPTH+1-l_count,
1103 A.CHILD_DEPTH = A.CHILD_DEPTH+1-l_count
1104 where A.BUSINESS_AREA_ID = l_business_area
1105 and A.DIMENSION_ID = p_dimension_id
1106 and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1107 and A.HIERARCHY_ID = each.HIERARCHY_ID
1108 and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
1109
1110 insert into ZPB_HIER_MEMBERS
1114 HIERARCHY_ID,
1111 (BUSINESS_AREA_ID,
1112 DIMENSION_ID,
1113 LOGICAL_DIM_ID,
1115 VERSION_ID,
1116 PARENT_ID,
1117 CHILD_ID,
1118 PARENT_DEPTH,
1119 CHILD_DEPTH,
1120 PARENT_GROUP,
1121 CHILD_GROUP,
1122 DISPLAY_ORDER,
1123 PARENT_INCLUDE_TYPE,
1124 CHILD_INCLUDE_TYPE)
1125 select distinct
1126 l_business_area,
1127 p_dimension_id,
1128 each.LOGICAL_DIM_ID,
1129 each.HIERARCHY_ID,
1130 l_count2,
1131 PARENT_ID,
1132 PARENT_ID,
1133 1,
1134 1,
1135 PARENT_GROUP,
1136 PARENT_GROUP,
1137 1,
1138 PARENT_INCLUDE_TYPE,
1139 PARENT_INCLUDE_TYPE
1140 from ZPB_HIER_MEMBERS A
1141 where A.BUSINESS_AREA_ID = l_business_area
1142 and A.DIMENSION_ID = p_dimension_id
1143 and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1144 and A.HIERARCHY_ID = each.HIERARCHY_ID
1145 and nvl(A.VERSION_ID,-1) = nvl(l_count2,-1)
1146 and A.PARENT_DEPTH = 1
1147 and A.CHILD_DEPTH <> 1
1148 and A.PARENT_ID not in
1149 (select distinct B.PARENT_ID
1150 from ZPB_HIER_MEMBERS B
1151 where B.BUSINESS_AREA_ID = l_business_area
1152 and B.DIMENSION_ID = p_dimension_id
1153 and B.HIERARCHY_ID = each.HIERARCHY_ID
1154 and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1155 and nvl(B.VERSION_ID,-1) = nvl(l_count2,-1)
1156 and B.CHILD_DEPTH = 1
1157 and B.PARENT_DEPTH = 1);
1158
1159 end if;
1160 if (l_use_cond = 'Y' and l_cond_anc = 'Y') then
1161 --
1162 -- Now we have to rewalk the hierarchy bottom-up and flip
1163 -- include type to A for any ancestors:
1164 --
1165 for anc in anc_depth(each.HIERARCHY_ID, l_count2) loop
1166 update ZPB_HIER_MEMBERS
1167 set PARENT_INCLUDE_TYPE = 'A'
1168 where PARENT_DEPTH = anc.PARENT_DEPTH
1169 and PARENT_INCLUDE_TYPE in ('N', 'T')
1170 and CHILD_INCLUDE_TYPE in ('Y', 'A')
1171 and BUSINESS_AREA_ID = l_business_area
1172 and DIMENSION_ID = p_dimension_id
1173 and LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1174 and HIERARCHY_ID = each.HIERARCHY_ID
1175 and nvl(VERSION_ID,-1) = nvl(l_count2,-1);
1176 end loop;
1177
1178 --
1179 -- Update the top level rows (parent_id = child_id) if the
1180 -- top level member was included by ancestor (bug 4573969)
1181 --
1182 update ZPB_HIER_MEMBERS A
1183 set A.PARENT_INCLUDE_TYPE = 'A',
1184 A.CHILD_INCLUDE_TYPE = 'A'
1185 where A.BUSINESS_AREA_ID = l_business_area
1186 and A.DIMENSION_ID = p_dimension_id
1187 and A.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1188 and A.HIERARCHY_ID = each.HIERARCHY_ID
1189 and nvl(A.VERSION_ID,-1) = nvl(l_count2,-1)
1190 and A.PARENT_DEPTH = 1
1191 and A.CHILD_DEPTH = 1
1192 and A.PARENT_ID = A.CHILD_ID
1193 and A.PARENT_INCLUDE_TYPE in ('N', 'T')
1194 and A.PARENT_ID in
1195 (select B.PARENT_ID
1196 from ZPB_HIER_MEMBERS B
1197 where B.BUSINESS_AREA_ID = l_business_area
1198 and B.DIMENSION_ID = p_dimension_id
1199 and B.LOGICAL_DIM_ID = each.LOGICAL_DIM_ID
1200 and B.HIERARCHY_ID = each.HIERARCHY_ID
1201 and nvl(B.VERSION_ID,-1) = nvl(l_count2,-1)
1202 and B.PARENT_DEPTH = 1
1203 and B.PARENT_INCLUDE_TYPE in ('Y', 'A'));
1204 end if;
1205 end loop;
1206 end INIT_HIER_MEMBER_CACHE;
1207
1208 ----------------------------------------------------------------------------
1209 -- INIT_HIER_MEMBER_CACHE
1210 --
1211 -- Initializes the cache which is used as part of GET_HIERARCHY_MEMBERS.
1212 -- Must be called before you call GET_HIERARCHY_MEMBERS. Will initialize
1213 -- for all dimensions of the business area passed in
1214 --
1215 -- IN: p_business_area - Option Business Area ID. Defaults to sys_context
1216 -- p_version_type - Version draft type
1217 ----------------------------------------------------------------------------
1218 procedure INIT_HIER_MEMBER_CACHE(p_business_area in NUMBER,
1219 p_version_type in VARCHAR2)
1220 is
1221 cursor dimensions is
1222 select A.DIMENSION_ID,
1223 A.LOGICAL_DIM_ID
1224 from ZPB_BUSAREA_DIMENSIONS A,
1225 ZPB_BUSAREA_VERSIONS B
1226 where A.VERSION_ID = B.VERSION_ID
1227 and B.BUSINESS_AREA_ID = p_business_area
1228 and B.VERSION_TYPE = p_version_type;
1229 begin
1230 delete from ZPB_HIER_MEMBERS
1231 where BUSINESS_AREA_ID = p_business_area;
1232
1233 for each in dimensions loop
1234
1235 INIT_HIER_MEMBER_CACHE(each.DIMENSION_ID,
1236 each.LOGICAL_DIM_ID,
1237 p_business_area,
1238 p_version_type);
1239 end loop;
1240 end INIT_HIER_MEMBER_CACHE;
1241
1242 end ZPB_FEM_UTILS_PKG;
[Home] [Help]