[Home] [Help]
PACKAGE BODY: APPS.ZPB_OLAP_VIEWS_PKG
Source
1 package body ZPB_OLAP_VIEWS_PKG as
2 /* $Header: ZPBVOLVB.pls 120.25 2007/12/04 14:39:43 mbhat ship $ */
3
4 -------------------------------------------------------------------------------
5 -- BUILD_ATTRIBUTE_MAP - Builds the limit map, column list and type
6 -- statement for the attributes of a dimension
7 -- IN:
8 -- p_aw - The AW holding the dimension
9 -- p_dim - The dimension ID
10 -- p_statement - The CREATE TYPE statement
11 -- p_cols - The list of columns for the view
12 --
13 -------------------------------------------------------------------------------
14 procedure BUILD_ATTRIBUTE_MAP (p_aw in varchar2,
15 p_dim in varchar2,
16 p_statement in out NOCOPY varchar2,
17 p_cols in out NOCOPY varchar2,
18 p_modelCmd in out NOCOPY varchar2)
19 is
20 l_attr_ecm zpb_ecm.attr_ecm;
21 l_attrs varchar2(1000);
22 l_attr varchar2(32);
23 l_col varchar2(64);
24 l_aw varchar2(60);
25 i number;
26 j number;
27 l_global_ecm zpb_ecm.global_ecm;
28 l_global_attr zpb_ecm.global_attr_ecm;
29 begin
30 l_global_ecm := zpb_ecm.get_global_ecm(p_aw);
31 l_global_attr := zpb_ecm.get_global_attr_ecm (p_aw);
32 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
33
34 zpb_aw.execute('push oknullstatus '||l_aw||l_global_ecm.AttrDim);
35 zpb_aw.execute('oknullstatus = yes');
36 zpb_aw.execute('lmt '||l_aw||l_global_ecm.AttrDim||' to '||
37 l_aw||l_global_attr.DomainDimRel||' eq lmt ('||
38 l_aw||l_global_ecm.DimDim||' to '''||p_dim||''')');
39
40 l_attrs := zpb_aw.interp ('shw CM.GETDIMVALUES('''||l_aw||
41 l_global_ecm.AttrDim||''' YES)');
42
43 if (l_attrs <> 'NA') then
44 i := 1;
45 loop
46 j := instr (l_attrs, ' ', i);
47 if (j = 0) then
48 l_attr := substr (l_attrs, i);
49 else
50 l_attr := substr (l_attrs, i, j-i);
51 i := j+1;
52 end if;
53
54 l_attr_ecm := zpb_ecm.get_attr_ecm(l_attr, l_global_attr, p_aw);
55 l_col := zpb_metadata_names.get_attribute_column (p_dim, l_attr);
56
57 p_statement := p_statement||l_col||' varchar2(160),';
58 p_cols := p_cols||l_col||',';
59 p_modelCmd := p_modelCmd||l_col||',';
60
61 exit when j = 0;
62 end loop;
63 end if;
64
65 zpb_aw.execute('pop oknullstatus '||l_global_ecm.AttrDim);
66 end BUILD_ATTRIBUTE_MAP;
67
68 -------------------------------------------------------------------------------
69 -- BUILD_BEGIN_MAP
70 --
71 -- Procedure to build the beginning portions of the type object and procedure
72 -- object's commands for a dimension.
73 --
74 -- IN OUT: p_statement (varchar2) - The variable pointing to the object command
75 -- p_proc (varchar2) - The variable pointing to the procedure
76 -- object command
77 -- IN: p_dimID (varchar2) - The ID for the dimension in the DimDim
78 --
79 -------------------------------------------------------------------------------
80 procedure BUILD_BEGIN_MAP (p_statement in out NOCOPY varchar2,
81 p_cols in out NOCOPY varchar2,
82 p_dimID in varchar2)
83 is
84 l_dimCol varchar2(32) :=zpb_metadata_names.get_dimension_column(p_dimID);
85 begin
86 p_statement := 'OBJECT ('||l_dimCol||' varchar2(60), ';
87 p_cols := l_dimCol||',';
88
89 end BUILD_BEGIN_MAP;
90
91 -------------------------------------------------------------------------------
92 -- BUILD_END_MAP
93 --
94 -- Procedure to build the end portions of the type object and procedure
95 -- object's commands for a dimension.
96 --
97 -- IN OUT: p_statement (varchar2) - The currently built type object command
98 -- p_cols (varchar2) - The currently built list of columns
99 -- IN: p_aw (varchar2) - The AW name
100 -- p_dim (varchar2) - The dimension ID in the DimDim
101 --
102 -------------------------------------------------------------------------------
103 procedure BUILD_END_MAP (p_aw in varchar2,
104 p_dim in varchar2,
105 p_statement in out NOCOPY varchar2,
106 p_cols in out NOCOPY varchar2,
107 p_modelCmd in out NOCOPY varchar2)
108 is
109 l_dim varchar2(16);
110 l_dimCol varchar2(32);
111 l_shortCol varchar2(32);
112 l_longCol varchar2(32);
113 l_codeCol varchar2(32);
114 l_enddateCol varchar2(32);
115 l_lnaggTmCol varchar2(32);
116 l_lnaggOtCol varchar2(32);
117 l_timespanCol varchar2(32);
118 l_calendarCol varchar2(32);
119 l_aw varchar2(30);
120 l_count number;
121 l_dim_data zpb_ecm.dimension_data;
122 begin
123 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
124 l_dim_data := zpb_ecm.get_dimension_data(p_dim, p_aw);
125 l_dim := l_dim_data.ExpObj;
126 l_shortCol := zpb_metadata_names.get_dim_short_name_column(p_dim);
127 l_longCol := zpb_metadata_names.get_dim_long_name_column(p_dim);
128 l_codeCol := zpb_metadata_names.get_dim_code_column(p_dim);
129
130 p_statement := p_statement||l_longCol||' varchar2(255)';
131 p_cols := p_cols||l_longCol;
132 p_modelCmd := p_modelCmd||l_longCol;
133
134 p_statement := p_statement||', '||l_shortCol||' varchar2(150)';
135 p_cols := p_cols||','||l_shortCol;
136 p_modelCmd := p_modelCmd||','||l_shortCol;
137
138 p_statement := p_statement||', '||l_codeCol||' varchar2(150)';
139 p_cols := p_cols||','||l_codeCol;
140 p_modelCmd := p_modelCmd||','||l_codeCol;
141
142 if (l_dim_data.Type = 'TIME') then
143 l_enddateCol := zpb_metadata_names.get_dim_enddate_column(p_dim);
144 l_timespanCol := zpb_metadata_names.get_dim_timespan_column(p_dim);
145 l_calendarCol := zpb_metadata_names.get_dim_calendar_column(p_dim);
146 p_statement := p_statement||', '||l_enddateCol||' date, '||
147 l_timespanCol||' number, '||l_calendarCol||' number';
148 p_cols := p_cols||','||l_enddateCol||','||l_timespanCol||','||
149 l_calendarCol;
150 p_modelCmd := p_modelCmd||','||l_enddateCol||','||l_timespanCol||','||
151 l_calendarCol;
152 end if;
153
154 if (l_dim_data.Type = 'LINE') then
155 l_lnaggTmCol := zpb_metadata_names.get_dim_aggtime_column(p_dim);
156 l_lnaggOtCol := zpb_metadata_names.get_dim_aggother_column(p_dim);
157 p_statement := p_statement||', '||l_lnaggTmCol||' varchar2(150), ' ||
158 l_lnaggOtCol || ' varchar2(150)';
159 p_cols := p_cols||','||l_lnaggTmCol|| ','||l_lnaggOtCol;
160 p_modelCmd := p_modelCmd||','||l_lnaggTmCol|| ','||l_lnaggOtCol;
161 end if;
162
163 p_modelCmd := p_modelCmd||') RULES UPDATE SEQUENTIAL ORDER()';
164 p_statement := p_statement||')';
165
166 end BUILD_END_MAP;
167
168 -------------------------------------------------------------------------------
169 -- CALL_DDL
170 --
171 -- Executes the DDL commands to generate the mapping structures. Used in
172 -- build_map().
173 --
174 -- IN: p_dimView (varchar2) - The name of the dimension view to build
175 -- p_aw (varchar2) - The actual name of the AW which stores the data
176 -- p_objComm (varchar2) - The command to create the type object
177 -- p_lmap (varchar2) - The AW qualified name of the limit map
178 -- p_olapCmd (varchar2) - The OLAP DML command that is executed before data is fetched
179 -------------------------------------------------------------------------------
180 procedure CALL_DDL (p_dimView in varchar2,
181 p_aw in varchar2,
182 p_objComm in varchar2,
183 p_lmap in varchar2,
184 p_cols in varchar2,
185 p_modelCmd in varchar2 default null,
186 p_olapCmd in varchar2 default null)
187 is
188 l_dimTable varchar2(200) := zpb_metadata_names.get_view_table(p_dimView);
189 l_dimObj varchar2(200) :=zpb_metadata_names.get_view_object(p_dimView);
190 l_version PRODUCT_COMPONENT_VERSION.VERSION%type;
191 l_aw varchar2(30);
192 l_schemaName varchar2(32);
193 l_model varchar2(16000);
194 l_olapCmd varchar2(1000);
195 begin
196
197 zpb_log.write ('zpb_olap_views_pkg.call_ddl.begin',
198 'Creating '||p_dimView||' structures');
199
200 if (p_aw <> 'PERSONAL' and p_aw <> 'EXPRESS' and instr(p_aw, '.') = 0) then
201 l_aw := zpb_aw.get_schema||'.'||p_aw;
202 else
203 l_aw := p_aw;
204 end if;
205
206 --
207 -- Build object
208 --
209 zpb_log.write_statement ('zpb_olap_views_pkg.call_ddl',
210 'Executing: '||p_objComm);
211
212 execute immediate 'create type '||l_dimObj||' as '||p_objComm;
213
214 --
215 -- Build table:
216 --
217 execute immediate
218 'create type '||l_dimTable||' as table of '||l_dimObj;
219
220 --
221 -- Build View:
222 --
223 select sys_context('USERENV', 'CURRENT_SCHEMA') into l_schemaName from dual;
224
225 l_olapCmd := p_olapCmd;
226 if (p_modelCmd is not null) then
227 select distinct(VERSION)
228 into l_version
229 from PRODUCT_COMPONENT_VERSION
230 where PRODUCT like '%Enterprise Edition%';
231 if (instr(l_version, '10.') = 1) then
232 l_model := p_modelCmd;
233 if (instr(l_version, '10.2') = 1 and instr(p_olapCmd, 'AW') = 1) then
234 l_olapCmd := '';
235 end if;
236 end if;
237 end if;
238
239 execute immediate
240 'create or replace view '||p_dimView||' as select '||p_cols||
241 ' from table(CAST (OLAP_TABLE('''||l_aw||
242 ' DURATION SESSION'', '''|| l_schemaName || '.' || l_dimTable||
243 ''', '''||l_olapCmd||''', '||p_lmap||') AS '||l_dimTable||'))'||
244 l_model;
245
246 zpb_log.write ('zpb_build_metadata.call_ddl.end',
247 'Finished creating '||p_dimView||' structures');
248 end CALL_DDL;
249
250 -------------------------------------------------------------------------------
251 -- DROP_VIEW
252 --
253 -- Drops the view and its corresponding objects
254 --
255 -- IN: p_view (varchar2) - The name of the view
256 --
257 -------------------------------------------------------------------------------
258 procedure DROP_VIEW (p_view in varchar2)
259 is
260 begin
261 begin
262 execute immediate 'drop view '||p_view;
263 exception when others then
264 null;
265 end;
266 begin
267 execute immediate 'drop type '||
268 zpb_metadata_names.get_view_table(p_view);
269 exception when others then
270 null;
271 end;
272 begin
273 execute immediate 'drop type '||
274 zpb_metadata_names.get_view_object(p_view);
275 exception when others then
276 null;
277 end;
278
279 end DROP_VIEW;
280
281 -------------------------------------------------------------------------------
282 -- BEGIN GLOBAL FUNCTION DECLARATIONS:
283 -------------------------------------------------------------------------------
284
285 -------------------------------------------------------------------------------
286 -- COMPILE_VIEWS
287 --
288 -- Recompiles views that have become INVALID, usually due to the recompilation/
289 -- patch of this file.
290 --
291 -------------------------------------------------------------------------------
292 procedure COMPILE_VIEWS is
293 cursor comp is
294 select 'alter view '||object_name||' compile' CMD
295 from USER_OBJECTS
296 where STATUS = 'INVALID'
297 and OBJECT_NAME like 'ZPB%_V'
298 and OBJECT_TYPE = 'VIEW';
299 begin
300 for each in comp loop
301 begin
302 execute immediate each.cmd;
303 --exception
304 -- when others then
305 -- null;
306 end;
307 end loop;
308 end COMPILE_VIEWS;
309 -------------------------------------------------------------------------------
310 -- CREATE_ATTRIBUTE_VIEWS
311 --
312 -- Builds the SQL mapping structures for an AW's attributes
313 --
314 -- IN:
315 -- p_aw (varchar2) - The name of the AW
316 -- p_type (varchar2) - The type of the AW (SHARED or PERSONAL)
317 -- p_attributes (varchar2) - list of attr IDs in Attrdim. If null,
318 -- all attributes are built
319 -------------------------------------------------------------------------------
320 procedure CREATE_ATTRIBUTE_VIEWS (p_aw in varchar2,
321 p_type in varchar2,
322 p_attributes in varchar2)
323 is
324
325 l_attrView varchar2(60);
326 l_attr varchar2(30);
327 l_aw varchar2(60);
328 i number;
329 j number;
330 l_attrs varchar2(5000);
331 l_attrObj varchar2(60);
332 l_cols varchar2(4000);
333 l_statement varchar2(16000);
334 l_proc varchar2(16000);
335 l_gidCol varchar2(30);
336 l_pgidCol varchar2(30);
337 l_parentCol varchar2(30);
338 l_levelCol varchar2(30);
339 l_shortCol varchar2(30);
340 l_longCol varchar2(30);
341 l_membCol varchar2(30);
342
343 l_global_ecm zpb_ecm.global_ecm;
344 l_attr_ecm zpb_ecm.global_attr_ecm;
345
346 begin
347 zpb_log.write ('zpb_olap_views_pkg.create_attribute_views.begin',
348 'Begin create_attribute_views');
349 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
350 l_global_ecm := zpb_ecm.get_global_ecm(p_aw);
351 l_attr_ecm := zpb_ecm.get_global_attr_ecm(p_aw);
352
353 if (p_attributes is null) then
354 zpb_aw.execute ('lmt '||l_aw||l_global_ecm.AttrDim||' to all');
355 l_attrs := ZPB_AW.INTERP ('shw CM.GETDIMVALUES('''||l_aw||
356 l_global_ecm.AttrDim||''' yes)');
357 else
358 l_attrs := p_attributes;
359 end if;
360
361 --
362 -- Get the attributes and loop over them:
363 --
364 i := 1;
365 loop
366 j := instr (l_attrs, ' ', i);
367 if (j = 0) then
368 l_attr := substr (l_attrs, i);
369 else
370 l_attr := substr (l_attrs, i, j-i);
371 i := j+1;
372 end if;
373
374 l_membCol := zpb_metadata_names.get_dimension_column(l_attr);
375 l_longCol := zpb_metadata_names.get_dim_long_name_column(l_attr);
376 l_shortCol := zpb_metadata_names.get_dim_short_name_column(l_attr);
377 l_gidCol := zpb_metadata_names.get_dim_gid_column(l_attr);
378 l_pgidCol := zpb_metadata_names.get_dim_pgid_column(l_attr);
379 l_parentCol := zpb_metadata_names.get_dim_parent_column(l_attr);
380
381 --
382 -- Create a "null" hierarchy. This will be the view used if the
383 -- dimension has no hierarchy, as well as the view used by Java tier
384 -- for labels of dimension members:
385 --
386 l_attrView := zpb_metadata_names.get_dimension_view
387 (p_aw, p_type, l_attr);
388 DROP_VIEW (l_attrView);
389
390 l_attrObj :=
391 zpb_aw.interp ('shw '||l_aw||l_global_ecm.ExpObjVar||' ('||
392 l_aw||l_global_ecm.DimDim||' '||
393 l_aw||l_attr_ecm.RangeDimRel||' ('||l_global_ecm.AttrDim||
394 ' '''||l_attr||'''))');
395
396 BUILD_BEGIN_MAP (l_statement, l_cols, l_attr);
397
398 l_levelCol := zpb_metadata_names.get_level_column(l_attr, 0);
399 l_statement := l_statement||l_parentCol||' varchar2(16),'||
400 l_gidCol||' number(10), '||l_pgidCol||' number(10), '||
401 l_levelCol||' varchar2(60), '||l_longCol||' varchar2(200), '||
402 l_shortCol||' varchar2(200))';
403
404 l_cols := l_cols||' NULL '||l_parentCol||',0 '||l_gidCol||',NULL '||
405 l_pgidCol||','||l_levelCol||','||l_longCol||','||l_shortCol;
406
407 l_proc := 'DIMENSION '||l_membCol||' FROM '||l_attrObj||'
408 WITH ATTRIBUTE '||l_shortCol||' FROM '||
409 zpb_aw.interp ('shw obj(property ''LDSCVAR'' '''||l_attrObj||''')')||'
410 ATTRIBUTE '||l_longCol||' FROM '||
411 zpb_aw.interp ('shw obj(property ''LDSCVAR'' '''||l_attrObj||''')')||'
412 ATTRIBUTE '||l_levelCol||' FROM '||l_attrObj;
413 l_proc := ''''||l_proc||'''';
414
415 if (p_type = 'PERSONAL') then
416 call_ddl (l_attrView, 'PERSONAL', l_statement, l_proc, l_cols);
417 else
418 call_ddl (l_attrView, p_aw, l_statement, l_proc, l_cols);
419 end if;
420
421 exit when j = 0;
422 end loop;
423
424 zpb_log.write ('zpb_olap_views_pkg.create_attribute_views.end',
425 'End create_attribute_views');
426
427 end CREATE_ATTRIBUTE_VIEWS;
428
429 -------------------------------------------------------------------------------
430 -- CREATE_CUBE_VIEW
431 --
432 -- Builds the SQL view for an empty cube
433 --
434 -- IN:
435 -- p_aw (varchar2) - The name of the AW holding the cube
436 -- p_awType (varchar2) - PERSONAL or SHARED: the AW type
437 -- p_view (varchar2) - The name of the view to create
438 -- p_lmap (varchar2) - The name of the LMAP variable to use for the view
439 -- p_colVar (varchar2) - The name of the COLCOUNTVAR variable
440 -- p_dims (varchar2) - Space sparated string of dim ID's (in the DimDim)
441 -- that defined the shape of the cube
442 -- p_mode (varchar2) - When creating a shared cube this procedure updates the shared
443 -- limit as well as created the cube.
444 -- When creating a personal cube these two actions must be
445 -- broken apart, and this param specifies which one is being performed
446 -------------------------------------------------------------------------------
447 procedure CREATE_CUBE_VIEW (p_aw IN VARCHAR2,
448 p_awType IN VARCHAR2,
449 p_view IN VARCHAR2,
450 p_lmap IN VARCHAR2,
451 p_colVar IN VARCHAR2,
452 p_dims IN VARCHAR2,
453 p_mode IN VARCHAR2)
454 is
455 i number;
456 j number;
457 hi number;
458 hj number;
459 l_count number;
460 l_gid boolean;
461 l_aw varchar2(30);
462 l_dim varchar2(30);
463 l_dimCol varchar2(30);
464 l_gidCol varchar2(30);
465 l_hiers varchar2(500);
466 l_hier varchar2(30);
467 l_preCmd varchar2(1000);
468 l_statement varchar2(32767);
469 l_cols varchar2(32767);
470 l_lmap varchar2(32000);
471 l_glbl_ecm zpb_ecm.global_ecm;
472 l_dim_data zpb_ecm.dimension_data;
473 l_dim_ecm zpb_ecm.dimension_ecm;
474 l_relView varchar2(30);
475 l_mapQual varchar2(30);
476 l_frmcpr varchar2(16);
477
478 l_cubes_command varchar2(1000);
479 l_cubes_of_shape number;
480 begin
481 i := 1;
482 l_count := 0;
483 l_gid := false;
484 l_statement := 'OBJECT(';
485 l_aw := p_aw||'!';
486 l_glbl_ecm := zpb_ecm.get_global_ecm(p_aw);
487
488 -- need to do this to avoid a GCC hard-coded schema warning
489 l_frmcpr := 'FRM';
490 l_frmcpr := l_frmcpr || '.';
491 l_frmcpr := l_frmcpr || 'CPR';
492
493 -- limit maps should point to personal only for personal cubes
494 if p_mode = 'DEFAULT' and p_awType<>'PERSONAL' then
495 l_mapQual := 'SHARED!';
496 else
497 l_mapQual :='PERSONAL!';
498 end if;
499
500 -- for first of shape shared and personal shared cubes add a COL_DF_CPR column for currently
501 -- processing run measures
502 l_cubes_of_shape :=0;
503
504 if(p_awType<>'PERSONAL') then
505 if (ZPB_AW.INTERPBOOL ('shw SC.FIRSTVIEW(''' || p_view || ''')')) then
506 l_cubes_of_shape :=1;
507 else
508 l_cubes_of_shape :=2;
509 end if;
510 end if;
511
512 loop
513 j := instr (p_dims, ' ', i);
514 if (j = 0) then
515 l_dim := substr (p_dims, i);
516 else
517 l_dim := substr (p_dims, i, j-i);
518 i := j+1;
519 end if;
520
521 l_dim_data := zpb_ecm.get_dimension_data(l_dim, p_aw);
522 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_dim, p_aw);
523
524 l_dimCol := zpb_metadata_names.get_dimension_column(l_dim);
525 l_count := l_count + 1;
526
527 l_statement := l_statement||l_dimCol||' VARCHAR2(32)';
528 l_cols := l_cols||l_dimCol||',';
529
530 l_lmap := l_lmap||'\nDIMENSION '||l_dimCol||' FROM '|| l_mapQual ||
531 l_dim_data.ExpObj;
532
533 l_preCmd := l_preCmd||' \\\'''||l_dim_data.ExpObj||'\\\''';
534
535 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')')
536 <> '0') then
537 l_lmap := l_lmap ||'\nWITH ';
538 zpb_aw.execute ('lmt '||l_aw||l_dim_ecm.HierDim||' to all');
539 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES ('''||l_aw||
540 l_dim_ecm.HierDim||''' YES)');
541 hi := 1;
542 loop
543 hj := instr (l_hiers, ' ', hi);
544 if (hj = 0) then
545 l_hier := substr (l_hiers, hi);
546 else
547 l_hier := substr (l_hiers, hi, hj-hi);
548 hi := hj+1;
549 end if;
550
551 l_gidCol := zpb_metadata_names.get_dim_gid_column(l_dim,l_hier);
552 l_statement := l_statement||', '||l_gidCol||' NUMBER';
553 l_cols := l_cols||l_gidCol||',';
554
555 l_lmap := l_lmap||'HIERARCHY '|| l_mapQual ||l_dim_ecm.ParentRel||
556 ' ('|| l_mapQual ||l_dim_ecm.HierDim||' \'''||l_hier||'\'')'||
557 '\nGID '||l_gidCol||' FROM ' || l_mapQual ||'GID.'||
558 l_dim_ecm.NameFragment;
559 l_count := l_count + 1;
560 exit when hj=0;
561 l_lmap := l_lmap||'\n';
562 end loop;
563 elsif (l_gid = false) then
564 l_gidCol := zpb_metadata_names.get_dim_gid_column;
565 l_statement := l_statement||', '||l_gidCol||' NUMBER';
566 l_cols := l_cols||'0 '||l_gidCol||',';
567 l_gid := true;
568 l_count := l_count + 1;
569 end if;
570
571 exit when j=0;
572 l_statement := l_statement||', ';
573 end loop;
574
575 l_lmap := l_lmap||'\nPREDMLCMD \''call MD.LMT.DIMS(joinlines('||l_preCmd||
576 '), \\\'''||p_aw||'\\\'')\''';
577 l_lmap := l_lmap||'\nPOSTDMLCMD \''call MD.LMT.DIMS.POST(joinlines('||l_preCmd||
578 '), \\\'''||p_aw||'\\\'')\''';
579
580 -- add CPR measure column if this is the first cube of its shape
581 if l_cubes_of_shape = 1 then
582 l_statement := l_statement || ', COL_DF_CPR NUMBER' ;
583 l_cols := l_cols||'COL_DF_CPR,';
584 l_count := l_count + 1;
585 l_lmap := l_lmap ||'\nMEASURE COL_DF_CPR FROM '||l_mapQual||
586 l_frmcpr;
587 end if;
588
589 if (p_awType = 'SHARED') then
590 j := 3;
591 else
592 j := 7;
593 end if;
594
595 l_count := trunc((254-l_count)/j);
596 i := l_count;
597 loop
598 l_statement := l_statement||', COL_DF_'||i||' NUMBER, COL_AN_'||i||
599 ' VARCHAR2(2050), COL_FMT_'||i||' VARCHAR2(1000)';
600 l_cols := l_cols||'COL_DF_'||i||',COL_AN_'||i||',COL_FMT_'||i;
601 if (p_awType <> 'SHARED') then
602 l_statement := l_statement||', COL_TG_'||i||' NUMBER, COL_TT_'||i||
603 ' NUMBER(1,0), COL_IL_'||i||' NUMBER(1,0), COL_WS_'||i||
604 ' VARCHAR2(60)';
605 l_cols := l_cols||',COL_TG_'||i||',COL_TT_'||i||
606 ',COL_IL_'||i||',COL_WS_'||i;
607 end if;
608 i := i - 1;
609 exit when i=0;
610 l_cols := l_cols||',';
611 end loop;
612
613 --
614 -- Set up the LMAP variable:
615 --
616
617 -- do not update limit map and col count var when creating personal cube views
618 -- as no personal is attached at the time
619 if p_mode <> 'PERSONAL_JUST_CREATE' then
620
621 ZPB_AW.EXECUTE (p_lmap||' = '''||l_lmap||'''');
622
623 --
624 -- Set up the column count information
625 --
626 ZPB_AW.EXECUTE (p_colVar||' = '||l_count);
627
628 end if;
629
630 -- do not drop and re-create the view if this is called for the sole reason
631 -- of updating personal l-map for dimension columns (on personal start-up)
632 if p_mode <> 'PERSONAL_JUST_LMAP' then
633
634 -- set up p_view for personal cube view
635 if p_mode = 'PERSONAL_JUST_CREATE' then
636 l_relView := p_view || '_PRS';
637 l_lmap :='''&('||p_lmap||' ('||'PERSONAL!'||l_glbl_ecm.MeasViewDim||' '''''||
638 p_view||'''''))''';
639 else
640 l_relView := p_view;
641 l_lmap := '''&('||p_lmap||' ('||l_aw||l_glbl_ecm.MeasViewDim||' '''''||
642 p_view||'''''))''';
643 end if;
644
645 DROP_VIEW (l_relView);
646 CALL_DDL (l_relView, p_aw, l_statement||')', l_lmap, l_cols);
647 end if;
648
649 zpb_log.write ('zpb_olap_views_pkg.create_cube_view.end',
650 'End create_cube_view');
651
652 end CREATE_CUBE_VIEW;
653
654 -------------------------------------------------------------------------------
655 -- CREATE_DIMENSION_VIEWS
656 --
657 -- Builds the SQL views which expose the dimensions
658 --
659 -- IN:
660 -- p_aw (varchar2) - The name of the data AW
661 -- p_type (varchar2) - The AW type (PERSONAL or SHARED)
662 -- p_dimension (varchar2) - A dimension to build dimension views. If null,
663 -- all dimensions are built
664 -- p_hierarchy (varchar2) - The hierarchy to build the view. If null,
665 -- all hierarchies
666 -------------------------------------------------------------------------------
667 procedure CREATE_DIMENSION_VIEWS (p_aw in varchar2,
668 p_type in varchar2,
669 p_dimension in varchar2 default null,
670 p_hierarchy in varchar2 default null)
671 is
672 l_dimView varchar2(64);
673 l_ecmDim varchar2(16);
674 l_hier varchar2(64);
675 l_level varchar2(64);
676 l_aw varchar2(60);
677 i number;
678 j number;
679 hi number;
680 hj number;
681 li number;
682 lj number;
683 l_length number;
684 l_dims varchar2(512);
685 l_hiers varchar2(512);
686 l_levels varchar2(4000);
687 l_hierLevels varchar2(4000);
688 l_cols varchar2(4000);
689 l_statement varchar2(16000);
690 l_modelCmd varchar2(16000);
691 l_lmap varchar2(200);
692 l_gidCol varchar2(32);
693 l_pgidCol varchar2(32);
694 l_parentCol varchar2(32);
695 l_levelCol varchar2(32);
696 l_levelRelCol varchar2(32);
697 l_orderCol varchar2(32);
698
699 l_global_ecm zpb_ecm.global_ecm;
700 l_dim_ecm zpb_ecm.dimension_ecm;
701 l_dim_data zpb_ecm.dimension_data;
702
703 begin
704 zpb_log.write ('zpb_olap_views_pkg.create_dimension_views.begin',
705 'Begin create_dimension_views');
706 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
707 l_global_ecm := zpb_ecm.get_global_ecm(p_aw);
708
709 if (p_dimension is null) then
710 ZPB_AW.EXECUTE ('lmt '||l_aw||l_global_ecm.DimDim||' to '||l_aw||
711 l_global_ecm.IsDataDimVar ||' eq YES');
712 l_dims := ZPB_AW.INTERP ('shw CM.GETDIMVALUES('''||l_aw||
713 l_global_ecm.DimDim||''' yes)');
714 else
715 l_dims := p_dimension;
716 end if;
717
718 ZPB_AW.EXECUTE ('push oknullstatus');
719 ZPB_AW.EXECUTE ('oknullstatus = yes');
720
721 --
722 -- Get the dimensions and loop over them:
723 --
724 i := 1;
725 loop
726 j := instr (l_dims, ' ', i);
727 if (j = 0) then
728 l_ecmDim := substr (l_dims, i);
729 else
730 l_ecmDim := substr (l_dims, i, j-i);
731 i := j+1;
732 end if;
733
734 l_dim_data := zpb_ecm.get_dimension_data(l_ecmDim, p_aw);
735 l_dim_ecm := zpb_ecm.get_dimension_ecm(l_ecmDim, p_aw);
736
737 l_gidCol := zpb_metadata_names.get_dim_gid_column(l_ecmDim);
738 l_pgidCol := zpb_metadata_names.get_dim_pgid_column(l_ecmDim);
739 l_parentCol := zpb_metadata_names.get_dim_parent_column(l_ecmDim);
740 l_orderCol := zpb_metadata_names.get_dim_order_column(l_ecmDim);
741
742 if (ZPB_AW.INTERP('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')')
743 <> '0') then
744
745 hi := 1;
746 if (p_hierarchy is null) then
747 l_hiers := ZPB_AW.INTERP('shw CM.GETDIMVALUES('''||l_aw||
748 l_dim_ecm.HierDim||''')');
749 else
750 l_hiers := p_hierarchy;
751 end if;
752
753 ZPB_AW.EXECUTE ('push '||l_aw||l_dim_ecm.LevelDim||' '||l_aw||
754 l_dim_ecm.HierDim||' '||l_aw||l_dim_data.ExpObj);
755
756 loop
757 hj := instr (l_hiers, ' ', hi);
758 if (hj = 0) then
759 l_hier := substr (l_hiers, hi);
760 else
761 l_hier := substr (l_hiers, hi, hj-hi);
762 hi := hj+1;
763 end if;
764
765 BUILD_BEGIN_MAP (l_statement, l_cols, l_ecmDim);
766
767 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.HierDim||' to '''||
768 l_hier||'''');
769
770 l_statement := l_statement||l_parentCol||' varchar2(32),'||
771 l_gidCol||' number(10), '||l_pgidCol||' number(10), '||
772 l_orderCol||' number, ';
773
774 l_cols := l_cols||l_parentCol||','||l_gidCol||','||
775 l_pgidCol||','||l_orderCol||',';
776
777 l_modelCmd := ' MODEL DIMENSION BY ('||
778 zpb_metadata_names.get_dimension_column(l_ecmDim)||', '||
779 l_gidCol||') MEASURES ('||l_parentCol||', '||
780 l_orderCol||', '||l_pgidCol||',';
781
782 --
783 -- Get the Levels:
784 --
785 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.LevelDim||
786 ' to '||l_aw||l_dim_ecm.HierLevelVS);
787 ZPB_AW.EXECUTE('sort '||l_aw||l_dim_ecm.LevelDim||
788 ' a '||l_aw||l_dim_ecm.LevelDepthVar);
789 l_hierLevels := ' '||ZPB_AW.INTERP('shw CM.GETDIMVALUES('''||l_aw||
790 l_dim_ecm.LevelDim||''', YES)')||' ';
791
792 ZPB_AW.EXECUTE('lmt '||l_aw||l_dim_ecm.LevelDim||' to all');
793 ZPB_AW.EXECUTE('sort '||l_aw||l_dim_ecm.LevelDim||
794 ' a '||l_aw||l_dim_ecm.LevelDepthVar);
795 l_levels := ZPB_AW.INTERP ('shw CM.GETDIMVALUES('''||l_aw||
796 l_dim_ecm.LevelDim||''', YES)');
797
798 li := 1;
799 loop
800 lj := instr (l_levels, ' ', li);
801 if (lj = 0) then
802 l_level := substr (l_levels, li);
803 else
804 l_level := substr (l_levels, li, lj-li);
805 li := lj+1;
806 end if;
807 ZPB_AW.EXECUTE ('lmt '||l_aw||l_dim_data.ExpObj||' to '||
808 l_aw||l_dim_ecm.HOrderVS);
809 ZPB_AW.EXECUTE ('lmt '||l_aw||l_dim_data.ExpObj||' keep '||
810 l_aw||l_dim_ecm.LevelRel||' '''||l_level||
811 '''');
812 l_length := to_number(ZPB_AW.INTERP('shw convert (statlen ('||
813 l_aw||l_dim_data.ExpObj||') text 0 no no)'));
814
815 if (instr (l_hierLevels, ' '||l_level||' ') > 0 and
816 l_length > 0) then
817 l_levelCol := zpb_metadata_names.get_level_column(l_ecmDim,
818 l_level);
819 l_statement := l_statement||l_levelCol||' varchar2(32), ';
820 l_cols := l_cols||l_levelCol||',';
821 l_modelCmd := l_modelCmd||l_levelCol||',';
822 end if;
823
824 exit when lj = 0;
825 end loop;
826
827 BUILD_ATTRIBUTE_MAP (p_aw, l_ecmDim, l_statement, l_cols,
828 l_modelCmd);
829 BUILD_END_MAP (p_aw, l_ecmDim, l_statement, l_cols, l_modelCmd);
830
831 if (p_type = 'SHARED') then
832 l_dimView := zpb_metadata_names.get_dimension_view (p_aw,
833 'SHARED',
834 l_ecmDim,
835 l_hier);
836 DROP_VIEW (l_dimView);
837
838 l_lmap := '''&('||l_aw||l_dim_ecm.HierLimitMapVar||' ('||
839 l_aw||l_dim_ecm.HierDim||' '''''||l_hier||'''''))''';
840
841 call_ddl (l_dimView, 'EXPRESS', l_statement, l_lmap, l_cols,
842 l_modelCmd, 'AW ATTACH SHARED');
843 end if;
844
845 l_dimView := zpb_metadata_names.get_dimension_view (p_aw,
846 'PERSONAL',
847 l_ecmDim,
848 l_hier);
849 DROP_VIEW (l_dimView);
850
851 l_lmap := '''&(PERSONAL!'||l_dim_ecm.HierLimitMapVar||' ('||
852 'PERSONAL!'||l_dim_ecm.HierDim||' '''''||l_hier||'''''))''';
853 call_ddl (l_dimView, 'EXPRESS', l_statement, l_lmap, l_cols,
854 l_modelCmd, 'AW ATTACH PERSONAL');
855
856 exit when hj = 0;
857 end loop;
858
859 ZPB_AW.EXECUTE ('pop '||l_aw||l_dim_ecm.LevelDim||' '||l_aw||
860 l_dim_ecm.HierDim||' '||l_aw||l_dim_data.ExpObj);
861
862 end if;
863
864 --
865 -- Create a "null" hierarchy. This will be the view used if the
866 -- dimension has no hierarchy, as well as the view used by Java tier
867 -- for labels of dimension members:
868 --
869 BUILD_BEGIN_MAP (l_statement, l_cols, l_ecmDim);
870
871 l_levelCol := zpb_metadata_names.get_level_column(l_ecmDim, 0);
872 l_levelrelCol := zpb_metadata_names.get_levelrel_column(l_ecmDim);
873
874 l_statement := l_statement||l_parentCol||' number(10),'||
875 l_gidCol||' number(10), '||l_pgidCol||' number(10), '||
876 l_levelCol||' varchar2(32), '||l_levelRelCol||' varchar2(16), ';
877
878 l_cols := l_cols||' NULL '||l_parentCol||',0 '||l_gidCol||',NULL '||
879 l_pgidCol||','||l_levelCol||',';
880 if (ZPB_AW.INTERP('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')')
881 <> '0') then
882 l_cols := l_cols||l_levelRelCol||',';
883 else
884 l_cols := l_cols||'NULL '||l_levelRelCol||',';
885 end if;
886
887 l_modelCmd := ' MODEL DIMENSION BY ('||
888 zpb_metadata_names.get_dimension_column(l_ecmDim)||', '||
889 l_gidCol||') MEASURES ('||l_parentCol||', '||
890 l_levelCol||','||l_levelRelCol||', '||l_pgidCol||',';
891
892 BUILD_ATTRIBUTE_MAP(p_aw, l_ecmDim, l_statement, l_cols, l_modelCmd);
893
894 BUILD_END_MAP (p_aw, l_ecmDim, l_statement, l_cols, l_modelCmd);
895
896 if (p_type = 'SHARED') then
897 l_dimView := zpb_metadata_names.get_dimension_view
898 (p_aw, 'SHARED', l_ecmDim);
899 DROP_VIEW (l_dimView);
900
901 l_lmap := '''&('||l_aw||l_dim_ecm.LimitMapVar||')''';
902 call_ddl (l_dimView, 'EXPRESS', l_statement, l_lmap, l_cols,
903 l_modelCmd, 'AW ATTACH SHARED');
904 end if;
905
906 l_dimView := zpb_metadata_names.get_dimension_view
907 (p_aw, 'PERSONAL',l_ecmDim);
908 DROP_VIEW (l_dimView);
909
910 l_lmap := '''&(PERSONAL!'||l_dim_ecm.LimitMapVar||')''';
911 call_ddl (l_dimView, 'EXPRESS', l_statement, l_lmap, l_cols,
912 l_modelCmd, 'AW ATTACH PERSONAL');
913
914 exit when j = 0;
915 end loop;
916
917 if (p_type = 'SHARED') then
918 CREATE_ATTRIBUTE_VIEWS (p_aw, 'SHARED');
919 CREATE_ATTRIBUTE_VIEWS (p_aw, 'PERSONAL');
920 end if;
921
922 ZPB_AW.EXECUTE ('call DB.BUILD.LMAP ('''||zpb_aw.get_schema||'.'||
923 p_aw||''')');
924 ZPB_AW.EXECUTE ('pop oknullstatus');
925
926 zpb_log.write ('zpb_olap_views_pkg.create_dimension_views.end',
927 'End create_dimension_views');
928
929 end CREATE_DIMENSION_VIEWS;
930
931 -------------------------------------------------------------------------------
932 -- CREATE_SECURITY_VIEW
933 --
934 -- IN: p_aw - The AW
935 -- p_measures - A space-separated list of measures, valid entries are
936 -- ('OWNERMAP', 'SECWRITEMAP.F', 'SECFULLSCPVW')
937 -- p_measView - The name of the measure view
938 -- p_dims - Space-separated list of dimensions
939 -------------------------------------------------------------------------------
940 procedure CREATE_SECURITY_VIEW (p_aw in varchar2,
941 p_measures in varchar2,
942 p_measView in varchar2,
943 p_dims in varchar2)
944 is
945 l_dimName varchar2(64);
946 l_ecmDim varchar2(16);
947 l_aw varchar2(30);
948 i number;
949 j number;
950 hi number;
951 hj number;
952 l_gid boolean;
953 l_cols varchar2(16000);
954 l_hiers varchar2(512);
955 l_hier varchar2(32);
956 l_dimCol varchar2(32);
957 l_gidCol varchar2(32);
958 l_measCol varchar2(32);
959 l_measType varchar2(32);
960 l_measName varchar2(32);
961 l_measure varchar2(128);
962 l_measState varchar2(16000);
963 l_measProc varchar2(16000);
964 l_objName varchar2(60);
965 l_instType varchar2(30);
966 l_type varchar2(30);
967 l_preCmd varchar2(4000);
968
969 l_dim_ecm zpb_ecm.dimension_ecm;
970 l_dim_data zpb_ecm.dimension_data;
971
972 begin
973
974 zpb_log.write ('zpb_olap_views_pkg.create_security_views.begin',
975 'Building '||p_measures||' for view '||p_measView||
976 ' with dims ('||p_dims||')');
977
978 l_aw := zpb_aw.get_schema||'.'||p_aw||'!';
979
980 l_measState := 'OBJECT (';
981
982 l_objName := 'OBJECT NAME'' NA NA NA';
983
984 i := 1;
985 loop
986 j := instr (p_measures, ' ', i);
987 if (j = 0) then
988 l_measure := substr (p_measures, i);
989 else
990 l_measure := substr (p_measures, i, j-i);
991 i := j+1;
992 end if;
993
994 if (l_measure = 'SECFULLSCPVW.F') then
995 l_measName := 'SECFULLSCPVW.F';
996 l_measCol := zpb_metadata_names.get_full_scope_column;
997 l_measType := 'VARCHAR2(1)';
998 elsif (l_measure = 'OWNERMAP') then
999 l_measName := 'SECOWNMAP2.F';
1000 l_measCol := zpb_metadata_names.get_ownermap_column;
1001 l_measType := 'VARCHAR2(8)';
1002 else
1003 ZPB_LOG.WRITE ('zpb_olap_view_pkg.create_security_views',
1004 'Invalid measure type: '||l_measure);
1005 return;
1006 end if;
1007
1008 l_measProc := l_measProc||'
1009 MEASURE '||l_measCol||' FROM '||l_measName||' ';
1010 l_measState := l_measState||l_measCol||' '||l_measType||', ';
1011 l_cols := l_cols||l_measCol||',';
1012
1013 exit when j = 0;
1014 end loop;
1015
1016 --
1017 -- Loop over the Measure dimensions:
1018 --
1019 l_gid := false;
1020 i := 1;
1021 loop
1022 j := instr (p_dims, ' ', i);
1023 if (j = 0) then
1024 l_ecmDim := substr (p_dims, i);
1025 else
1026 l_ecmDim := substr (p_dims, i, j-i);
1027 i := j+1;
1028 end if;
1029
1030 l_dim_data := zpb_ecm.get_dimension_data (l_ecmDim, p_aw);
1031 l_dim_ecm := zpb_ecm.get_dimension_ecm (l_ecmDim, p_aw);
1032 --
1033 -- Create the obj type:
1034 --
1035 l_dimCol := zpb_metadata_names.get_dimension_column(l_ecmDim);
1036 l_measState := l_measState||l_dimCol||' VARCHAR2(32)';
1037
1038 l_measProc := l_measProc||'
1039 DIMENSION '||l_dimCol||' FROM '||l_dim_data.ExpObj||' ';
1040
1041 l_preCmd := l_preCmd||' \'''''||l_dim_data.ExpObj||'\''''';
1042 l_cols := l_cols||l_dimCol||',';
1043
1044 if (zpb_aw.interp('shw obj(dimmax '''||l_aw||l_dim_ecm.HierDim||''')')
1045 <> '0') then
1046 l_measProc := l_measProc||'
1047 WITH ';
1048 zpb_aw.execute ('lmt '||l_aw||l_dim_ecm.HierDim||' to all');
1049 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES ('''||l_aw||
1050 l_dim_ecm.HierDim||''' YES)');
1051 hi := 1;
1052 loop
1053 hj := instr (l_hiers, ' ', hi);
1054 if (hj = 0) then
1055 l_hier := substr (l_hiers, hi);
1056 else
1057 l_hier := substr (l_hiers, hi, hj-hi);
1058 hi := hj+1;
1059 end if;
1060
1061 l_gidCol := zpb_metadata_names.get_dim_gid_column(l_ecmDim,
1062 l_hier);
1063 l_measState := l_measState||', '||l_gidCol||' NUMBER';
1064 l_cols := l_cols||l_gidCol||',';
1065
1066 l_measProc := l_measProc||'HIERARCHY '||l_dim_ecm.ParentRel||
1067 ' ('||l_dim_ecm.HierDim||' '''''||l_hier||''''')
1068 GID '||l_gidCol||' FROM GID.'||l_dim_ecm.NameFragment;
1069 exit when hj=0;
1070 l_measProc := l_measProc||'
1071 ';
1072 end loop;
1073 elsif (l_gid = false) then
1074 l_gidCol := zpb_metadata_names.get_dim_gid_column;
1075 l_measState := l_measState||', '||l_gidCol||' NUMBER';
1076 l_cols := l_cols||'0 '||l_gidCol||',';
1077 l_gid := true;
1078 end if;
1079
1080 exit when j = 0;
1081 l_measState := l_measState||', ';
1082 end loop;
1083
1084 drop_view (p_measView);
1085
1086 l_measState := l_measState||') ';
1087 l_measProc := l_measProc||'
1088 PREDMLCMD ''''call MD.LMT.DIMS(joinlines('||l_preCmd||'), \'''''||
1089 zpb_aw.get_schema||'.'||p_aw||'\'''')''''
1090 POSTDMLCMD ''''call MD.LMT.DIMS.POST(joinlines('||l_preCmd||'), \'''''||
1091 zpb_aw.get_schema||'.'||p_aw||'\'''')''''';
1092
1093 --
1094 -- Remove trailing comma:
1095 --
1096 l_cols := substr(l_cols, 1, length(l_cols)-1);
1097 l_measProc := ''''||l_measProc||'''';
1098
1099 call_ddl (p_measView, p_aw, l_measState, l_measProc, l_cols);
1100 zpb_log.write ('zpb_olap_views_pkg.create_security_views.end',
1101 'End create_security_views');
1102
1103 end CREATE_SECURITY_VIEW;
1104
1105 -------------------------------------------------------------------------------
1106 -- CREATE_VIEW_STRUCTURES
1107 --
1108 -- Builds the views on the shared AW for exposing EPB-specific
1109 -- information to the middle tier
1110 --
1111 -- IN: p_dataAw (varchar2) - The actual name of the data AW
1112 -- p_annotAw (varchar2) - The actual name of the annotation AW
1113 --
1114 -------------------------------------------------------------------------------
1115 procedure CREATE_VIEW_STRUCTURES (p_dataAW in varchar2,
1116 p_annotAW in varchar2)
1117 is
1118 l_dimName varchar2(30);
1119 l_dimView varchar2(30);
1120 l_secView varchar2(30);
1121 l_scopeView varchar2(30);
1122 l_dataExcView varchar2(30);
1123 l_view varchar2(30);
1124 l_solveTbl varchar2(30);
1125 l_aw varchar2(30);
1126 l_ecmDim varchar2(30);
1127 l_schema varchar2(30);
1128 l_dims varchar2(500);
1129 l_hiers varchar2(1000);
1130 l_hier varchar2(30);
1131 l_cols varchar2(1000);
1132 l_col varchar2(30);
1133 i number;
1134 j number;
1135 hi number;
1136 hj number;
1137 l_count number;
1138 l_statement varchar2(16000);
1139 l_proc varchar2(16000);
1140 l_global_ecm zpb_ecm.global_ecm;
1141 l_dim_ecm zpb_ecm.dimension_ecm;
1142 l_dim_data zpb_ecm.dimension_data;
1143 begin
1144 zpb_log.write ('zpb_olap_view_pkg.create_view_structures.begin',
1145 'Building AW metadata views');
1146
1147 l_global_ecm := zpb_ecm.get_global_ecm (p_dataAw);
1148 l_schema := zpb_aw.get_schema;
1149 l_aw := l_schema||'.'||p_dataAw||'!';
1150
1151 --
1152 -- Build Security Scoping Status table
1153 --
1154 l_scopeView := zpb_metadata_names.get_scope_status_view(p_dataAw);
1155 DROP_VIEW (l_scopeView);
1156
1157 l_statement := 'OBJECT ('||
1158 'SECENTITY VARCHAR2(10),'||
1159 'DIMDIM VARCHAR2(10),'||
1160 'SCOPESTAT VARCHAR2(100))';
1161
1162 l_proc := '''MEASURE SCOPESTAT FROM SECSCPSTAT.F
1163 DIMENSION SECENTITY FROM SECENTITY
1164 DIMENSION DIMDIM FROM '||l_global_ecm.DimDim||'''';
1165
1166 call_ddl (l_scopeView, p_dataAw, l_statement, l_proc, '*', null,
1167 'limit '||l_global_ecm.Dimdim||' to '||
1168 l_global_ecm.IsDataDimVar||' eq yes');
1169
1170 --
1171 -- Data Exception views
1172 --
1173 l_dataExcView := zpb_metadata_names.get_data_exception_view(p_dataAw);
1174 DROP_VIEW (l_dataExcView);
1175
1176 l_statement := 'OBJECT (QDR VARCHAR2(1000),
1177 DATA_VALUE NUMBER,
1178 TARGET_VALUE NUMBER,
1179 TARGET_TYPE VARCHAR2(30),
1180 VARIANCE NUMBER,
1181 VARIANCE_PCT NUMBER,
1182 ENTRY NUMBER)';
1183
1184 l_proc := '''MEASURE QDR FROM DC.EXCEPT.QDR
1185 MEASURE DATA_VALUE FROM DC.EXCEPT.DATAVAL
1186 MEASURE TARGET_VALUE FROM DC.EXCEPT.TARGETVAL
1187 MEASURE TARGET_TYPE FROM DC.EXCEPT.TARGETTYPE
1188 MEASURE VARIANCE FROM DC.EXCEPT.VARIANCE
1189 MEASURE VARIANCE_PCT FROM DC.EXCEPT.VARIANCEPCT
1190 DIMENSION ENTRY FROM DC.EXCEPT.ENTRY''';
1191
1192 call_ddl (l_dataExcView, 'PERSONAL', l_statement, l_proc, '*');
1193
1194 --
1195 -- Generic view used for exception check SQL definitions
1196 --
1197 l_dataExcView := zpb_metadata_names.get_exception_check_tbl(p_dataAw);
1198
1199 begin
1200 execute immediate 'drop synonym '||l_dataExcView;
1201 execute immediate 'drop table '||zpb_aw.get_schema||'.'||l_dataExcView;
1202 exception
1203 when others then
1204 null;
1205 end;
1206
1207 l_statement := 'create table '||zpb_aw.get_schema||'.'||l_dataExcView||'(';
1208 l_dims := zpb_aw.interp ('shw CM.GETDATADIMS');
1209
1210 i := 1;
1211 loop
1212 j := instr (l_dims, ' ', i);
1213 if (j = 0) then
1214 l_ecmDim := substr (l_dims, i);
1215 else
1216 l_ecmDim := substr (l_dims, i, j-i);
1217 i := j+1;
1218 end if;
1219
1220 l_dim_ecm := zpb_ecm.get_dimension_ecm (l_ecmDim, p_dataAw);
1221
1222 l_col := zpb_metadata_names.get_dimension_column(l_ecmDim);
1223 l_statement := l_statement||l_col||' VARCHAR2(32), ';
1224 l_cols := l_cols||l_col||', ';
1225
1226 zpb_aw.execute ('lmt '||l_aw||l_dim_ecm.HierDim||' to all');
1227 l_hiers := zpb_aw.interp ('shw CM.GETDIMVALUES ('''||l_aw||
1228 l_dim_ecm.HierDim||''' YES)');
1229 hi := 1;
1230 loop
1231 hj := instr (l_hiers, ' ', hi);
1232 if (hj = 0) then
1233 l_hier := substr (l_hiers, hi);
1234 else
1235 l_hier := substr (l_hiers, hi, hj-hi);
1236 hi := hj+1;
1237 end if;
1238
1239 l_col := zpb_metadata_names.get_dim_gid_column(l_ecmDim, l_hier);
1240 l_statement := l_statement||l_col||' NUMBER, ';
1241 l_cols := l_cols||l_col||', ';
1242
1243 exit when hj=0;
1244 end loop;
1245 exit when j=0;
1246 end loop;
1247
1248 --
1249 -- Add the null hierarchy GID column for good measure. May not be needed:
1250 --
1251 l_col := zpb_metadata_names.get_dim_gid_column;
1252 l_statement := l_statement||l_col||' NUMBER, ';
1253 l_cols := l_cols||'0 '||l_col||', ';
1254
1255 l_col := zpb_metadata_names.get_exception_column;
1256 l_statement := l_statement||l_col||' NUMBER)';
1257 l_cols := l_cols||l_col;
1258
1259 execute immediate l_statement;
1260
1261 execute immediate 'create synonym '||l_dataExcView||' for '||
1262 zpb_aw.get_schema||'.'||l_dataExcView;
1263
1264 --
1265 -- Create the 4 currency view tables:
1266 --
1267 if (zpb_aw.interp ('shw lmt('||l_aw||l_global_ecm.DimDim||' to '||l_aw||
1268 l_global_ecm.DimTypeRel||' eq ''FROM_CURRENCY'')')<>'NA') then
1269 l_statement := 'OBJECT(MEMBER_ID VARCHAR2(30), MEMBER_CODE VARCHAR2(15),'
1270 ||'MEMBER_NAME VARCHAR2(80), MEMBER_DESC VARCHAR2(240))';
1271 l_proc := '''DIMENSION MEMBER_ID FROM TO.CURRENCY
1272 WITH ATTRIBUTE MEMBER_CODE FROM TO.CURRENCY.SNAME
1273 ATTRIBUTE MEMBER_NAME FROM TO.CURRENCY.MNAME
1274 ATTRIBUTE MEMBER_DESC FROM TO.CURRENCY.NAME''';
1275
1276 l_view := zpb_metadata_names.get_to_currency_view(p_dataAw);
1277 drop_view (l_view);
1278 call_ddl (l_view, zpb_aw.get_schema||'.'||p_dataAw, l_statement, l_proc, '*');
1279
1280 l_statement := 'OBJECT(MEMBER_ID VARCHAR2(30), MEMBER_CODE VARCHAR2(15),'
1281 ||'MEMBER_NAME VARCHAR2(80), MEMBER_DESC VARCHAR2(240))';
1282 l_proc := '''DIMENSION MEMBER_ID FROM RATES
1283 WITH ATTRIBUTE MEMBER_CODE FROM RATES.SNAME
1284 ATTRIBUTE MEMBER_NAME FROM RATES.MNAME
1285 ATTRIBUTE MEMBER_DESC FROM RATES.NAME''';
1286
1287 l_view := zpb_metadata_names.get_exch_rates_view(p_dataAw);
1288 drop_view (l_view);
1289 call_ddl (l_view, zpb_aw.get_schema||'.'||p_dataAw, l_statement, l_proc, '*');
1290
1291 l_statement := 'OBJECT(MEMBER_ID VARCHAR2(30), MEMBER_CODE VARCHAR2(100),'
1292 ||'MEMBER_NAME VARCHAR2(100), MEMBER_DESC VARCHAR2(240))';
1293 l_proc := '''DIMENSION MEMBER_ID FROM SCENARIO
1294 WITH ATTRIBUTE MEMBER_CODE FROM SCENARIO.SNAME
1295 ATTRIBUTE MEMBER_NAME FROM SCENARIO.MNAME
1296 ATTRIBUTE MEMBER_DESC FROM SCENARIO.NAME''';
1297
1298 l_view := zpb_metadata_names.get_exch_scenario_view(p_dataAw);
1299 drop_view (l_view);
1300 call_ddl (l_view, zpb_aw.get_schema||'.'||p_dataAw, l_statement, l_proc, '*');
1301 end if;
1302
1303 zpb_log.write ('zpb_olap_view_pkg.create_view_structures.end',
1304 'Done building AW metadata views');
1305 end CREATE_VIEW_STRUCTURES;
1306
1307 -------------------------------------------------------------------------------
1308 -- GET_LIMITMAP - Returns the limitmap for a dimension given
1309 -- DEPRECATED! Only left in to simplify upgrade of dev env's
1310 --
1311 -- IN:
1312 -- p_type (varchar2) - The AW type (either 'SHARED' or 'PERSONAL')
1313 -- p_dim (varchar2) - The dimension (the physical AW object)
1314 -- p_hier (varchar2) - The hierarchy ID, null denotes no hierarchy
1315 --
1316 -- OUT:
1317 -- The limitmap for the parameters specified
1318 -------------------------------------------------------------------------------
1319 function GET_LIMITMAP (p_type in varchar2,
1320 p_dim in varchar2,
1321 p_hier in varchar2)
1322 return varchar2 is
1323 l_msg_cnt number;
1324 l_ret_stat varchar2(30);
1325 l_buff varchar2(500);
1326 l_persAw ZPB_USERS.PERSONAL_AW%type;
1327 l_ba_id number;
1328 l_shadow number;
1329 l_last_upd date;
1330 l_reattach boolean;
1331 l_personal_alias_flag varchar2(1);
1332 begin
1333 --
1334 -- First, initialize the session. Concurrent req's have already been
1335 -- initialized at least for the shared AW
1336 -- and ZPB_CURRENT_USER_V is not valid for them:
1337 --
1338 l_personal_alias_flag := ZPB_AW_STATUS.GET_PERSONAL_ALIAS_FLAG();
1339 if (not (FND_GLOBAL.CONC_REQUEST_ID > 0)) then
1340
1341 -- if zpb_current_user_v is not initialized and we get no data here,
1342 -- we must be in an open-sql session - only shared AW used and it is
1343 -- already attached
1344 begin
1345 select BUSINESS_AREA_ID, SHADOW_ID
1346 into l_ba_id, l_shadow
1347 from ZPB_CURRENT_USER_V;
1348 exception when others then
1349 null;
1350 end;
1351
1352 --
1353 -- Check to see if shared attached, and its the right shared/bus area:
1354 --
1355 l_reattach := false;
1356 if (p_type = 'SHARED' and
1357 ZPB_AW.INTERPBOOL ('shw aw(attached ''SHARED'')')) then
1358 if (ZPB_AW.INTERPBOOL ('shw exists(''SHARED!AW.ATTACH.TIME'')')) then
1359 l_buff := ZPB_AW.INTERP('shw SHARED!AW.ATTACH.TIME');
1360 --
1361 -- Comment out until we resolve bug# 4887248
1362 --
1363 /*
1364 select LAST_AW_UPDATE
1365 into l_last_upd
1366 from ZPB_BUSINESS_AREAS
1367 where BUSINESS_AREA_ID = l_ba_id;
1368
1369 if (l_last_upd is not null and
1370 upper(l_buff) <> 'NA' and
1371 to_date(l_buff, 'YYYY/MM/DD HH24:MI:SS') < l_last_upd) then
1372 l_reattach := true;
1373 end if;
1374 */
1375 else
1376 --
1377 -- Cover upgrade cases, when AW.ATTACH.TIME is not in the AW
1378 --
1379 l_reattach := false;
1380 end if;
1381 end if;
1382 if (l_reattach or
1383 sys_context('ZPB_CONTEXT', 'business_area_id') is null or
1384 sys_context('ZPB_CONTEXT', 'business_area_id') <> l_ba_id or
1385 (p_type = 'SHARED' and
1386 not ZPB_AW.INTERPBOOL ('shw aw(attached ''SHARED'')'))) then
1387
1388 ZPB_AW.INITIALIZE (p_api_version => 1.0,
1389 x_return_status => l_ret_stat,
1390 x_msg_count => l_msg_cnt,
1391 x_msg_data => l_buff,
1392 p_business_area_id => l_ba_id,
1393 p_shadow_id => l_shadow);
1394 end if;
1395 else
1396 l_ba_id := sys_context('ZPB_CONTEXT', 'business_area_id');
1397 l_shadow := nvl(sys_context('ZPB_CONTEXT', 'shadow_id'),
1398 fnd_global.user_id);
1399 end if;
1400
1401 if (upper(p_type) = 'PERSONAL') then
1402 select ZPB_AW.GET_SCHEMA||'.'||PERSONAL_AW
1403 into l_persAw
1404 from ZPB_USERS
1405 where BUSINESS_AREA_ID = l_ba_id
1406 and USER_ID = l_shadow;
1407
1408 --
1409 -- Check to see if personal attached, and it is the right personal
1410 --
1411 if (l_personal_alias_flag <> 'Y' and not ZPB_AW.INTERPBOOL
1412 ('shw aw(attached ''PERSONAL'') and aw(attached '''||l_persAw||
1413 ''') and aw(name ''PERSONAL'') eq aw(name '''||l_persAw||''')'))
1414 then
1415 ZPB_AW.INITIALIZE_USER (p_api_version => 1.0,
1416 x_return_status => l_ret_stat,
1417 x_msg_count => l_msg_cnt,
1418 x_msg_data => l_buff,
1419 p_user => l_shadow,
1420 p_business_area_id => l_ba_id,
1421 p_attach_readwrite => FND_API.G_FALSE);
1422 end if;
1423 end if;
1424
1425 if (p_hier is null) then
1426 return zpb_aw.interp
1427 ('shw &joinchars('''||p_type||'!'' obj(property ''LIMITMAPVAR'' '''||
1428 p_dim||'''))');
1429 else
1430 l_buff := p_type||'!'||zpb_aw.interp('shw obj(property ''HIERDIM'' '''||
1431 p_dim||''')');
1432 return zpb_aw.interp
1433 ('shw &joinchars('''||p_type||'!'' obj(property ''LIMITMAPVAR'' '''||
1434 l_buff||''')) ('||l_buff||' '''||p_hier||''')');
1435 end if;
1436 end GET_LIMITMAP;
1437
1438 -------------------------------------------------------------------------------
1439 -- INITIALIZE - Initializes the session to run SQL queries against the OLAP
1440 -- views. This is only needed for sessions that have not had
1441 -- a normal OLAP startup called (ie, Apps sessions)
1442 --
1443 -- IN:
1444 -- p_type (varchar2) - The AW type (either 'SHARED' or 'PERSONAL')
1445 -------------------------------------------------------------------------------
1446 procedure INITIALIZE (p_type in varchar2)
1447 is
1448 l_msg_cnt number;
1449 l_ret_stat varchar2(30);
1450 l_buff varchar2(500);
1451 l_persAw ZPB_USERS.PERSONAL_AW%type;
1452 l_ba_id number;
1453 l_shadow number;
1454 l_last_upd date;
1455 l_reattach boolean;
1456 l_personal_alias_flag varchar2(1);
1457 l_code_aw VARCHAR2(100);
1458 l_code_aw_attach BOOLEAN;
1459 begin
1460 --
1461 -- First, initialize the session. Concurrent req's have already been
1462 -- initialized at least for the shared AW
1463 -- and ZPB_CURRENT_USER_V is not valid for them:
1464 --
1465 begin
1466 select BUSINESS_AREA_ID, SHADOW_ID
1467 into l_ba_id, l_shadow
1468 from ZPB_CURRENT_USER_V;
1469 exception when others then
1470 null;
1471 end;
1472
1473 -- check if code aw is attached
1474 l_code_aw := ZPB_AW.GET_SCHEMA||'.'||ZPB_AW.GET_CODE_AW( l_shadow ) ;
1475 l_code_aw_attach := false;
1476 if NOT ZPB_AW.INTERPBOOL ('shw aw(attached '''|| l_code_aw ||''')') then
1477 l_code_aw_attach := true;
1478 end if;
1479
1480
1481 --
1482 -- Check to see if shared attached, and its the right shared/bus area:
1483 --
1484 l_reattach := false;
1485 if (p_type = 'SHARED' and
1486 ZPB_AW.INTERPBOOL ('shw aw(attached ''SHARED'')')) then
1487 if (ZPB_AW.INTERPBOOL ('shw exists(''SHARED!AW.ATTACH.TIME'')')) then
1488 l_buff := ZPB_AW.INTERP('shw SHARED!AW.ATTACH.TIME');
1489 --
1490 -- Comment out until we resolve bug# 4887248
1491 --
1492 /*
1493 select LAST_AW_UPDATE
1494 into l_last_upd
1495 from ZPB_BUSINESS_AREAS
1496 where BUSINESS_AREA_ID = l_ba_id;
1497
1498 if (l_last_upd is not null and
1499 upper(l_buff) <> 'NA' and
1500 to_date(l_buff, 'YYYY/MM/DD HH24:MI:SS') < l_last_upd) then
1501 l_reattach := true;
1502 end if;
1503 */
1504 end if;
1505 end if;
1506 if (l_reattach or
1507 sys_context('ZPB_CONTEXT', 'business_area_id') is null or
1508 sys_context('ZPB_CONTEXT', 'business_area_id') <> l_ba_id or
1509 l_code_aw_attach or
1510 (p_type = 'SHARED' and
1511 not ZPB_AW.INTERPBOOL ('shw aw(attached ''SHARED'')'))) then
1512
1513 ZPB_AW.INITIALIZE (p_api_version => 1.0,
1514 x_return_status => l_ret_stat,
1515 x_msg_count => l_msg_cnt,
1516 x_msg_data => l_buff,
1517 p_business_area_id => l_ba_id,
1518 p_shadow_id => l_shadow);
1519 end if;
1520
1521 if (upper(p_type) = 'PERSONAL') then
1522 select ZPB_AW.GET_SCHEMA||'.'||PERSONAL_AW
1523 into l_persAw
1524 from ZPB_USERS
1525 where BUSINESS_AREA_ID = l_ba_id
1526 and USER_ID = l_shadow;
1527
1528 --
1529 -- Check to see if personal attached, and it is the right personal
1530 --
1531 l_personal_alias_flag := ZPB_AW_STATUS.GET_PERSONAL_ALIAS_FLAG();
1532 if (l_personal_alias_flag <> 'Y' and not ZPB_AW.INTERPBOOL
1533 ('shw aw(attached ''PERSONAL'') and aw(attached '''||l_persAw||
1534 ''') and aw(name ''PERSONAL'') eq aw(name '''||l_persAw||''')'))
1535 then
1536 ZPB_AW.INITIALIZE_USER (p_api_version => 1.0,
1537 x_return_status => l_ret_stat,
1538 x_msg_count => l_msg_cnt,
1539 x_msg_data => l_buff,
1540 p_user => l_shadow,
1541 p_business_area_id => l_ba_id,
1542 p_attach_readwrite => FND_API.G_FALSE);
1543 end if;
1544 end if;
1545
1546 -- for bug 5019035
1547 -- commit;
1548
1549 end INITIALIZE;
1550
1551 -------------------------------------------------------------------------------
1552 -- REMOVE_DIMENSION_VIEW
1553 --
1554 -- IN:
1555 -- p_aw - The AW storing the dimension
1556 -- p_type - PERSONAL or SHARED, the AW type
1557 -- p_dim - The dimension ID in the DimDim
1558 -- p_hierarchy - The hierarchy ID in the HierDim
1559 -- Removes the view for the dimension's hierarchy.
1560 -------------------------------------------------------------------------------
1561 procedure REMOVE_DIMENSION_VIEW (p_aw in varchar2,
1562 p_type in varchar2,
1563 p_dim in varchar2,
1564 p_hierarchy in varchar2)
1565 is
1566 begin
1567 DROP_VIEW (ZPB_METADATA_NAMES.GET_DIMENSION_VIEW
1568 (p_aw, p_type, p_dim, p_hierarchy));
1569 end REMOVE_DIMENSION_VIEW;
1570 -------------------------------------------------------------------------------
1571 -- REMOVE_BUSAREA_VIEWS
1572 --
1573 -- Removes all SQL views for a business area
1574 --
1575 -- IN: p_business_area - The Business Area ID
1576 --
1577 -------------------------------------------------------------------------------
1578 procedure REMOVE_BUSAREA_VIEWS (p_business_area in NUMBER)
1579 is
1580 l_aw ZPB_BUSINESS_AREAS.DATA_AW%type;
1581
1582 -- b 5751055 bkport from 5658636
1583 cursor c_views is
1584 select view_name from user_views
1585 where view_name like l_aw||'\_%' escape '\' or
1586 view_name like l_aw||'PRS\_%' escape '\' or
1587 view_name like 'ZPB' || p_business_area ||'PRS\_%' escape '\' or
1588 view_name like 'ZPB' || p_business_area || '\_D%' escape '\';
1589
1590
1591 cursor c_users is
1592 select user_id
1593 from ZPB_USERS
1594 where BUSINESS_AREA_ID = p_business_area;
1595 begin
1596 select DATA_AW
1597 into l_aw
1598 from ZPB_BUSINESS_AREAS
1599 where BUSINESS_AREA_ID = p_business_area;
1600
1601 for each in c_views loop
1602 drop_view(each.view_name);
1603 end loop;
1604
1605 for each in c_users loop
1606 REMOVE_USER_VIEWS(each.user_id, p_business_area);
1607 end loop;
1608 end REMOVE_BUSAREA_VIEWS;
1609
1610 -------------------------------------------------------------------------------
1611
1612 -------------------------------------------------------------------------------
1613 -- REMOVE_USER_VIEWS
1614 --
1615 -- Removes all relational views for the user
1616 -- IN: p_user varchar2 - The user ID
1617 --
1618 -------------------------------------------------------------------------------
1619 procedure REMOVE_USER_VIEWS (p_user in varchar2,
1620 p_business_area in NUMBER)
1621 is
1622 l_aw varchar2(30);
1623
1624 cursor user_views is
1625 select view_name
1626 from user_views
1627 where view_name like l_aw||'\_%' escape '\';
1628
1629 begin
1630 l_aw := zpb_aw.get_personal_aw(p_user, p_business_area);
1631
1632 for each in user_views loop
1633 drop_view(each.view_name);
1634 end loop;
1635
1636 end REMOVE_USER_VIEWS;
1637
1638 end ZPB_OLAP_VIEWS_PKG;