[Home] [Help]
PACKAGE BODY: APPS.ZPB_ECM
Source
1 PACKAGE BODY ZPB_ECM AS
2 /* $Header: zpbecm.plb 120.6 2007/12/04 15:26:41 mbhat ship $ */
3
4 m_olapSchema varchar2(4) := upper(zpb_aw.get_schema)||'.';
5
6 TYPE global_ecm_hash IS TABLE OF GLOBAL_ECM INDEX BY VARCHAR2(30);
7 m_global_ecm_hash global_ecm_hash;
8
9 TYPE dimension_ecm_hash IS TABLE OF DIMENSION_ECM INDEX BY VARCHAR2(60);
10 m_dim_ecm_hash dimension_ecm_hash;
11
12 -------------------------------------------------------------------------------
13 -- GET_PROP
14 --
15 -------------------------------------------------------------------------------
16 function GET_PROP (p_property in varchar2,
17 p_object in varchar2)
18 return varchar2
19 is
20 begin
21 return zpb_aw.interp('shw obj(property '''||p_property||''' '''||
22 p_object||''')');
23 end GET_PROP;
24
25 -------------------------------------------------------------------------------
26 -- GET_AGGREGATION_ECM
27 --
28 -------------------------------------------------------------------------------
29 function GET_AGGREGATION_ECM (p_aw in varchar2)
30 return AGGR_ECM is
31 l_aw varchar2(30);
32 l_aggrEcm aggr_ecm;
33 l_lineType varchar2(30);
34 l_global_ecm global_ecm;
35 begin
36 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
37 l_aw := p_aw;
38 else
39 l_aw := m_olapSchema||p_aw;
40 end if;
41 zpb_aw.execute ('aw attach '||l_aw);
42 l_global_ecm := get_global_ecm(p_aw);
43 l_aw := l_aw||'!';
44
45 l_lineType := l_aw||l_global_ecm.LineTypeDim;
46
47 l_aggrEcm.AggLtRel := get_prop ('AGGLTREL', l_lineType);
48 l_aggrEcm.AggLtBaseVar := get_prop ('AGGLTBASEVAR', l_lineType);
49 l_aggrEcm.AggLtdRel := get_prop ('AGGLTDREL', l_lineType);
50 l_aggrEcm.AggLtdBaseVar := get_prop ('AGGLTDBASEVAR', l_lineType);
51 l_aggrEcm.LdscVar := get_prop ('LDSCVAR',
52 l_aw||l_global_ecm.AggTypeDim);
53
54 return l_aggrEcm;
55 end GET_AGGREGATION_ECM;
56
57 -------------------------------------------------------------------------------
58 -- GET_ANNOTATION_ECM
59 --
60 -------------------------------------------------------------------------------
61 function GET_ANNOTATION_ECM (p_aw in varchar2)
62 return ANNOT_ECM is
63 l_aw varchar2(30);
64 l_annotEcm annot_ecm;
65 l_annEntry varchar2(30);
66 l_global_ecm global_ecm;
67 begin
68 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
69 l_aw := p_aw;
70 else
71 l_aw := m_olapSchema||p_aw;
72 end if;
73 l_global_ecm := get_global_ecm(p_aw);
74 l_aw := l_aw||'!';
75
76 l_annEntry := l_global_ecm.AnnEntryDim;
77
78 l_annotEcm.CellsObjDim := get_prop('CELLSOBJDIM', l_annEntry);
79 l_annotEcm.CellsShapeRel := get_prop('CELLSSHAPEREL', l_annEntry);
80 l_annotEcm.CountFrm := get_prop('COUNTFRM', l_annEntry);
81 l_annotEcm.DataVar := get_prop('DATAVAR', l_annEntry);
82 l_annotEcm.DateVar := get_prop('DATEVAR', l_annEntry);
83 l_annotEcm.LastDataFrm := get_prop('LASTDATAFRM', l_annEntry);
84 l_annotEcm.LastDateFrm := get_prop('LASTDATEFRM', l_annEntry);
85 l_annotEcm.LastRdscFrm := get_prop('LASTRDSCFRM', l_annEntry);
86 l_annotEcm.LastUserFrm := get_prop('LASTUSERFRM', l_annEntry);
87 l_annotEcm.LookupObjDim := get_prop('LOOKUPOBJDIM', l_annEntry);
88 l_annotEcm.LookupShapeRel := get_prop('LOOKUPSHAPEREL', l_annEntry);
89 l_annotEcm.ReasonCodeDim := get_prop('REASONCODEDIM', l_annEntry);
90 l_annotEcm.ReasonCodeLdsc := get_prop('LDSCVAR', l_annotEcm.ReasonCodeDim);
91 l_annotEcm.ReasonRel := get_prop('REASONREL', l_annEntry);
92 l_annotEcm.UserRel := get_prop('USERREL', l_annEntry);
93
94 return l_annotEcm;
95 end GET_ANNOTATION_ECM;
96
97 -------------------------------------------------------------------------------
98 -- GET_ALLOCATION_ECM
99 --
100 -------------------------------------------------------------------------------
101 function GET_ALLOCATION_ECM (p_aw in varchar2)
102 return ALLOC_ECM is
103 l_aw varchar2(30);
104 l_allocEcm alloc_ecm;
105 l_lineType varchar2(30);
106 l_global_ecm global_ecm;
107 begin
108 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
109 l_aw := p_aw;
110 else
111 l_aw := m_olapSchema||p_aw;
112 end if;
113 zpb_aw.execute ('aw attach '||l_aw);
114 l_global_ecm := get_global_ecm(p_aw);
115 l_aw := l_aw||'!';
116
117 l_lineType := l_aw||l_global_ecm.LineTypeDim;
118
119 l_allocEcm.AlcLtRel := get_prop ('ALCLTREL',l_lineType);
120 l_allocEcm.AlcLtBaseVar := get_prop ('ALCLTBASEVAR', l_lineType);
121 l_allocEcm.AlcLtdRel := get_prop ('ALCLTDREL', l_lineType);
122 l_allocEcm.AlcLtdBaseVar := get_prop ('ALCLTDBASEVAR', l_lineType);
123
124 return l_allocEcm;
125 end GET_ALLOCATION_ECM;
126
127 -------------------------------------------------------------------------------
128 -- GET_ATTR_ECM
129 --
130 -------------------------------------------------------------------------------
131 function GET_ATTR_ECM (p_attr in varchar2,
132 p_global_attr_ecm in global_attr_ecm,
133 p_aw in varchar2)
134 return ATTR_ECM is
135 l_attrDim varchar2(30);
136 l_attrEcm attr_ecm;
137 l_aw varchar2(30);
138 l_global_ecm global_ecm;
139 begin
140 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
141 l_aw := p_aw;
142 else
143 l_aw := m_olapSchema||p_aw;
144 end if;
145 zpb_aw.execute ('aw attach '||l_aw);
146 l_global_ecm := get_global_ecm(p_aw);
147 l_aw := l_aw||'!';
148
149 l_attrDim := l_aw||l_global_ecm.AttrDim;
150 l_attrEcm.AttrRelation :=
151 zpb_aw.interp('shw '||l_aw||p_global_attr_ecm.ExpObjVar|| '(' ||l_aw||
152 l_global_ecm.AttrDim||' '''||p_attr||''')');
153
154 l_attrEcm.LdscFrm := get_prop ('LDSCFRM', l_attrEcm.AttrRelation);
155
156 l_attrDim := l_global_ecm.AttrDim;
157 l_attrEcm.RangeDimension :=
158 zpb_aw.interp ('shw '||l_aw||p_global_attr_ecm.RangeDimRel|| '('||
159 l_aw||l_global_ecm.AttrDim||' '''||p_attr||''')');
160
161 l_attrEcm.DefOrderVSet := get_prop('DEFORDERVSET',l_attrEcm.RangeDimension);
162 l_attrEcm.LdscVar := get_prop('LDSCVAR',l_attrEcm.RangeDimension);
163
164 return l_attrEcm;
165 end GET_ATTR_ECM;
166
167 -------------------------------------------------------------------------------
168 -- GET_DIMENSION_DATA
169 --
170 -------------------------------------------------------------------------------
171 function GET_DIMENSION_DATA (p_dim in varchar2,
172 p_aw in varchar2)
173 return DIMENSION_DATA
174 is
175 l_dim_data DIMENSION_DATA;
176 l_aw varchar2(30);
177 l_global_ecm global_ecm;
178 begin
179 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
180 l_aw := p_aw;
181 else
182 l_aw := m_olapSchema||p_aw;
183 end if;
184 zpb_aw.execute ('aw attach '||l_aw);
185 l_global_ecm := get_global_ecm(p_aw);
186 l_aw := l_aw||'!';
187
188 zpb_aw.execute ('push '||l_aw||l_global_ecm.DimDim);
189 zpb_aw.execute ('limit '||l_aw||l_global_ecm.DimDim||' to '''||p_dim||'''');
190
191 l_dim_data.Ldsc := zpb_aw.interp ('shw '||l_aw||l_global_ecm.LdscVar);
192 l_dim_data.PlLdsc := zpb_aw.interp ('shw '||l_aw||l_global_ecm.PlLdscVar);
193 l_dim_data.PlSdsc := zpb_aw.interp ('shw '||l_aw||l_global_ecm.PlSdscVar);
194 l_dim_data.Sdsc := zpb_aw.interp ('shw '||l_aw||l_global_ecm.SdscVar);
195 l_dim_data.Type := zpb_aw.interp ('shw '||l_aw||l_global_ecm.DimTypeRel);
196 l_dim_data.ExpObj := zpb_aw.interp ('shw '||l_aw||l_global_ecm.ExpObjVar);
197 if (zpb_aw.interpbool ('shw '||l_aw||l_global_ecm.IsDataDimVar)) then
198 l_dim_data.IsDataDim := 'YES';
199 else
200 l_dim_data.IsDataDim := 'NO';
201 end if;
202 if (zpb_aw.interpbool ('shw SHARED!ISOWNERDIM (SHARED!'||
203 l_global_ecm.DimDim||' '''||p_dim||''')')) then
204 l_dim_data.IsOwnerDim := 'YES';
205 else
206 l_dim_data.IsOwnerDim := 'NO';
207 end if;
208
209 zpb_aw.execute ('pop '||l_aw||l_global_ecm.DimDim);
210 return l_dim_data;
211
212 end GET_DIMENSION_DATA;
213 -------------------------------------------------------------------------------
214 -- GET_DIMENSION_ECM
215 --
216 -- Builds a DIMENSION_ECM object, given the dimension. The dimension should
217 -- be the value stored in the DimDim ECM object. If the ECM data cannot be
218 -- found, null will be returned.
219 --
220 -- IN: p_dim (varchar2) - The name of the dimension in DimDim
221 -- l_global_ecm (GLOBAL_ECM) - The Global Ecm
222 -- p_aw (varchar2) - The aw to pull the info from. If null, then
223 -- the current AW is used.
224 -- OUT: DIMENSION_ECM
225 --
226 -------------------------------------------------------------------------------
227 function GET_DIMENSION_ECM (p_dim in varchar2,
228 p_aw in varchar2)
229 return DIMENSION_ECM
230 is
231 l_dim varchar2(60);
232 l_dim_ecm DIMENSION_ECM;
233 l_aw varchar2(30);
234 l_awQual varchar2(30);
235 l_hierDim varchar2(30);
236 l_levelDim varchar2(30);
237 l_global_ecm global_ecm;
238 begin
239 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
240 l_aw := p_aw;
241 else
242 l_aw := m_olapSchema||p_aw;
243 end if;
244 zpb_aw.execute ('aw attach '||l_aw);
245
246 begin
247 l_dim_ecm := m_dim_ecm_hash(l_aw||'***'||p_dim);
248 return l_dim_ecm;
249 exception
250 when no_data_found then
251 null;
252 end;
253
254 l_global_ecm := get_global_ecm(p_aw);
255 l_awQual := l_aw||'!';
256
257 l_dim := l_awQual||zpb_aw.interp('shw '||l_awQual||l_global_ecm.ExpObjVar||
258 ' ('||l_awQual||l_global_ecm.DimDim||' '''||
259 p_dim||''')');
260
261 l_dim_ecm.AncestorRel := get_prop('ANCESTORREL', l_dim);
262 l_dim_ecm.AnnDim := get_prop('ANNDIM', l_dim);
263 l_dim_ecm.DefaultMember := get_prop('DEFAULTMEMBER', l_dim);
264 l_dim_ecm.DefOrderVS := get_prop('DEFORDERVS', l_dim);
265 l_dim_ecm.DepthFrm := get_prop('DEPTHFRM', l_dim);
266 l_dim_ecm.DepthFrm1 := get_prop('DEPTHFRM1', l_dim);
267 l_dim_ecm.DfltLevelRel := get_prop('DFLTLEVELREL', l_dim);
268 l_dim_ecm.DimDrillDir := get_prop('DIMDRILLDIR', l_dim);
269 l_dim_ecm.DrillInfoFrm := get_prop('DRILLINFOFRM', l_dim);
270 l_dim_ecm.DrillInfoFrm := get_prop('DRILLINFOFRM', l_dim);
271 l_dim_ecm.FmtCatVar := get_prop('FMTCATVAR', l_dim);
272 l_dim_ecm.FmtFlagCatVar := get_prop('FMTFLAGCATVAR', l_dim);
273 l_dim_ecm.FmtStringVar := get_prop('FMTSTRINGVAR', l_dim);
274 l_dim_ecm.FullOrderVar := get_prop('FULLORDERVAR', l_dim);
275 l_dim_ecm.GID := get_prop('GID', l_dim);
276 l_dim_ecm.HierDefault := get_prop('HIERDEFAULT', l_dim);
277 l_dim_ecm.HierDim := get_prop('HIERDIM', l_dim);
278 l_dim_ecm.HierHeight := get_prop('HIERHEIGHT', l_dim);
279 l_dim_ecm.HOrderVs := get_prop('HORDERVS', l_dim);
280 l_dim_ecm.InHierVar := get_prop('INHIERVAR', l_dim);
281 l_dim_ecm.LastQueryVS := get_prop('LASTQUERYVS', l_dim);
282 l_dim_ecm.LdscVar := get_prop('LDSCVAR', l_dim);
283 l_dim_ecm.LevelDim := get_prop('LEVELDIM', l_dim);
284 l_dim_ecm.LevelRel := get_prop('LEVELREL', l_dim);
285 l_dim_ecm.LimitMapVar := get_prop('LIMITMAPVAR', l_dim);
286 l_dim_ecm.MdscVar := get_prop('MDSCVAR', l_dim);
287 l_dim_ecm.MemberTypeRel := get_prop('MEMBERTYPEREL', l_dim);
288 l_dim_ecm.MLevelLdscFrm := get_prop('MLEVELLDSCFRM', l_dim);
289 l_dim_ecm.MPLLevelLdscFrm := get_prop('MPLLEVELLDSCFRM', l_dim);
290 l_dim_ecm.NameFragment := get_prop('NAMEFRAGMENT', l_dim);
291 l_dim_ecm.ParentRel := get_prop('PARENTREL', l_dim);
292 l_dim_ecm.RootValSet := get_prop('ROOTVALSET', l_dim);
293 l_dim_ecm.SdscVar := get_prop('SDSCVAR', l_dim);
294 l_dim_ecm.SecOwnAccVar := get_prop('SECOWNACCVAR', l_dim);
295 l_dim_ecm.SecOwnDscVar := get_prop('SECOWNDSCVAR', l_dim);
296 l_dim_ecm.SecReadAccFrm := get_prop('SECREADACCFRM', l_dim);
297 l_dim_ecm.SecReadAccVar := get_prop('SECREADACCVAR', l_dim);
298 l_dim_ecm.SecWrtAccVar := get_prop('SECWRTACCVAR', l_dim);
299 l_dim_ecm.SibOrderVar := get_prop('SIBORDERVAR', l_dim);
300 l_dim_ecm.TempVS := get_prop('TEMPVS', l_dim);
301
302 if (l_dim_ecm.HierDim <> 'NA') then
303 l_hierDim := l_awQual||l_dim_ecm.HierDim;
304
305 l_dim_ecm.HierDimScpFrm := get_prop('HIERDIMSCPFRM', l_dim);
306 l_dim_ecm.HierFEMDefIDVar:= get_prop('FEMDEFIDVAR', l_hierDim);
307 l_dim_ecm.HierFEMIDVar := get_prop('FEMIDVAR', l_hierDim);
308 l_dim_ecm.HierLevelVS := get_prop('HIERLEVELVS', l_hierDim);
309 l_dim_ecm.HierLdscVar := get_prop('LDSCVAR', l_hierDim);
310 l_dim_ecm.HierVersLdscVar:= get_prop('VERSLDSCVAR', l_hierDim);
311 l_dim_ecm.HierLimitMapVar:= get_prop('LIMITMAPVAR', l_hierDim);
312 l_dim_ecm.HierTypeRel := get_prop('HIERTYPEREL', l_hierDim);
313 l_dim_ecm.LevelDepthVar := get_prop('LEVELDEPTHVAR', l_hierDim);
314 end if;
315 if (l_dim_ecm.LevelDim <> 'NA') then
316 l_levelDim := l_awQual||l_dim_ecm.LevelDim;
317 l_dim_ecm.LevelDimScpFrm := get_prop('LEVELDIMSCPFRM', l_dim);
318 l_dim_ecm.LevelLdscVar := get_prop('LDSCVAR', l_levelDim);
319 l_dim_ecm.LevelMdscVar := get_prop('MDSCVAR', l_levelDim);
320 l_dim_ecm.LevelSdscVar := get_prop('SDSCVAR', l_levelDim);
321 l_dim_ecm.LevelPersVar := get_prop('PERSONALVAR', l_levelDim);
322 l_dim_ecm.LevelPlLdscVar := get_prop('PLLDSCVAR', l_levelDim);
323 end if;
324 m_dim_ecm_hash(l_aw||'***'||p_dim) := l_dim_ecm;
325 return l_dim_ecm;
326
327 end GET_DIMENSION_ECM;
328
329 -------------------------------------------------------------------------------
330 -- GET_DIMENSION_LINE_ECM
331 --
332 -- Builds a DIMENSION_LINE_ECM object.
333 --
334 -- IN: p_dim (varchar2) - The Line Dimension
335 -- l_global_ecm (GLOBAL_ECM) - The Global Ecm
336 -- p_aw (varchar2) - The aw to pull the info from. If null, then
337 -- the current AW is used.
338 -- OUT: DIMENSION_LINE_ECM
339 --
340 -------------------------------------------------------------------------------
341 function GET_DIMENSION_LINE_ECM (p_dim in varchar2,
342 p_aw in varchar2)
343 return DIMENSION_LINE_ECM
344 is
345 l_dim varchar2(60);
346 l_line_ecm dimension_line_ecm;
347 l_aw varchar2(30);
348 l_global_ecm global_ecm;
349 begin
350 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
351 l_aw := p_aw;
352 else
353 l_aw := m_olapSchema||p_aw;
354 end if;
355 zpb_aw.execute ('aw attach '||l_aw);
356 l_global_ecm := get_global_ecm(p_aw);
357 l_aw := l_aw||'!';
358
359 l_dim :=l_aw||zpb_aw.interp('shw '||l_aw||l_global_ecm.ExpObjVar||'('||
360 l_aw||l_global_ecm.DimDim||' '''||p_dim||''')');
361
362 l_line_ecm.AggBaseFrm := get_prop('AGGBASEFRM', l_dim);
363 l_line_ecm.AggDefFrm := get_prop('AGGDEFFRM', l_dim);
364 l_line_ecm.AggLdBaseVar := get_prop('AGGLDBASEVAR', l_dim);
365 l_line_ecm.AggLdRel := get_prop('AGGLDREL', l_dim);
366 l_line_ecm.AggLineBaseVar := get_prop('AGGLINEBASEVAR', l_dim);
370 l_line_ecm.AlcDefBaseVar := get_prop('ALCDEFBASEVAR', l_dim);
367 l_line_ecm.AggLineRel := get_prop('AGGLINEREL', l_dim);
368 l_line_ecm.AggOrderVar := get_prop('AGGORDERVAR', l_dim);
369 l_line_ecm.AlcBaseFrm := get_prop('ALCBASEFRM', l_dim);
371 l_line_ecm.AlcDefFrm := get_prop('ALCDEFFRM', l_dim);
372 l_line_ecm.AlcLdRel := get_prop('ALCLDREL', l_dim);
373 l_line_ecm.AlcLineBaseVar := get_prop('ALCLINEBASEVAR', l_dim);
374 l_line_ecm.AlcLineRel := get_prop('ALCLINEREL', l_dim);
375 l_line_ecm.BetterWorseVar := get_prop('BETTERWORSEVAR', l_dim);
376 l_line_ecm.CumDataVar := get_prop('CUMDATAVAR', l_dim);
377 l_line_ecm.LineDimVar := get_prop('LINEDIMVAR', l_dim);
378 l_line_ecm.LineTypeRel := get_prop('LINETYPEREL', l_dim);
379 l_line_ecm.NatSignVar := get_prop('NATSIGNVAR', l_dim);
380
381 return l_line_ecm;
382
383 end GET_DIMENSION_LINE_ECM;
384
385 -------------------------------------------------------------------------------
386 -- GET_DIMENSION_TIME_ECM
387 --
388 -- Builds a DIMENSION_TIME_ECM object.
389 --
390 -- IN: p_dim (varchar2) - The Time Dimension
391 -- l_global_ecm (GLOBAL_ECM) - The Global Ecm
392 -- p_aw (varchar2) - The aw to pull the info from. If null, then
393 -- the current AW is used.
394 -- OUT: DIMENSION_TIME_ECM
395 --
396 -------------------------------------------------------------------------------
397 function GET_DIMENSION_TIME_ECM (p_dim in varchar2,
398 p_aw in varchar2)
399 return DIMENSION_TIME_ECM
400 is
401 l_dim varchar2(60);
402 l_time_ecm dimension_time_ecm;
403 l_aw varchar2(30);
404 l_global_ecm global_ecm;
405 begin
406 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
407 l_aw := p_aw;
408 else
409 l_aw := m_olapSchema||p_aw;
410 end if;
411 zpb_aw.execute ('aw attach '||l_aw);
412 l_global_ecm := get_global_ecm(p_aw);
413 l_aw := l_aw||'!';
414
415 l_dim :=l_aw||zpb_aw.interp('shw '||l_aw||l_global_ecm.ExpObjVar||'('||
416 l_aw||l_global_ecm.DimDim||' '''||p_dim||''')');
417
418 l_time_ecm.CalendarVar := get_prop('CALENDARVAR', l_dim);
419 l_time_ecm.EndDateVar := get_prop('ENDDATEVAR', l_dim);
420 l_time_ecm.LatestRel := get_prop('LATESTREL', l_dim);
421 l_time_ecm.LatestProcRel := get_prop('LATESTPROCREL', l_dim);
422 l_time_ecm.OffsetVar := get_prop('OFFSETVAR', l_dim);
423 l_time_ecm.PriorFrm := get_prop('PRIORFRM', l_dim);
424 l_time_ecm.RangeRel := get_prop('RANGEREL', l_dim);
425 --
426 -- Hardcoded, as it is not in ECM:
427 --
428 l_time_ecm.RangeLvlRel := 'RANGELVL.'||get_prop('NAMEFRAGMENT', l_dim);
429 l_time_ecm.ROffsetVar := get_prop('ROFFSETVAR', l_dim);
430 l_time_ecm.TimeSpanVar := get_prop('TIMESPANVAR', l_dim);
431 l_time_ecm.TLvlTypeRel := get_prop('TLVLTYPEREL', l_dim);
432 l_time_ecm.YardStickDim := get_prop('YARDSTICKDIM', l_dim);
433 l_time_ecm.YrAgoFrm := get_prop('YRAGOFRM', l_dim);
434
435 return l_time_ecm;
436
437 end GET_DIMENSION_TIME_ECM;
438
439 -------------------------------------------------------------------------------
440 -- GET_GLOBAL_ATTR_ECM
441 --
442 -- Builds a GLOBAL_ATTR_ECM object.
443 --
444 -- IN: l_global_ecm (GLOBAL_ECM) - The Global Ecm
445 -- p_aw (varchar2) - The aw to pull the info from. If null, then
446 -- the current AW is used.
447 -- OUT: GLOBAL_ATTR_ECM
448 --
449 -------------------------------------------------------------------------------
450 function GET_GLOBAL_ATTR_ECM (p_aw in varchar2)
451 return GLOBAL_ATTR_ECM is
452 l_attrDim varchar2(30);
453 l_attr_ecm global_attr_ecm;
454 l_aw varchar2(30);
455 l_global_ecm global_ecm;
456 begin
457 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
458 l_aw := p_aw;
459 else
460 l_aw := m_olapSchema||p_aw;
461 end if;
462 zpb_aw.execute ('aw attach '||l_aw);
463 l_global_ecm := get_global_ecm(p_aw);
464 l_aw := l_aw||'!';
465
466 l_attrDim := l_aw||l_global_ecm.AttrDim;
467
468 l_attr_ecm.AttrDisplayVar := get_prop('ATTRDISPLAYVAR', l_attrDim);
469 l_attr_ecm.DomainDimRel := get_prop('DOMAINDIMREL', l_attrDim);
470 l_attr_ecm.ExpObjVar := get_prop('EXPOBJVAR', l_attrDim);
471 l_attr_ecm.ExpTypeDim := get_prop('EXPTYPEDIM', l_attrDim);
472 l_attr_ecm.ExpTypeRel := get_prop('EXPTYPEREL', l_attrDim);
473 l_attr_ecm.LdscVar := get_prop('LDSCVAR', l_attrDim);
474 l_attr_ecm.NameFragVar := get_prop('NAMEFRAGVAR', l_attrDim);
475 l_attr_ecm.RangeDimRel := get_prop('RANGEDIMREL', l_attrDim);
476 l_attr_ecm.TypeDim := get_prop('TYPEDIM', l_attrDim);
477 l_attr_ecm.TypeRel := get_prop('TYPEREL', l_attrDim);
478
479 return l_attr_ecm;
480 end GET_GLOBAL_ATTR_ECM;
481
482 -------------------------------------------------------------------------------
483 -- GET_GLOBAL_ECM
484 --
485 -- Builds a GLOBAL_ECM object. If an ECMLocator cannot be found, or the AW
486 -- cannot be attached, it will return null.
487 --
488 -- IN: p_aw (varchar2) - The aw to pull the info from. If null, then
489 -- the current AW is used.
493 function GET_GLOBAL_ECM (p_aw in varchar2)
490 -- OUT: GLOBAL_ECM
491 --
492 -------------------------------------------------------------------------------
494 return GLOBAL_ECM
495 is
496 l_ecm GLOBAL_ECM;
497 l_aw varchar2(30);
498 l_awQual varchar2(30);
499 l_meas varchar2(30);
500 begin
501 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
502 l_aw := p_aw;
503 else
504 l_aw := m_olapSchema||p_aw;
505 end if;
506 zpb_aw.execute ('aw attach '||l_aw);
507
508 begin
509 l_ecm := m_global_ecm_hash(l_aw);
510 return l_ecm;
511 exception
512 when no_data_found then
513 null;
514 end;
515
516 l_awQual := l_aw||'!';
517
518 zpb_aw.execute ('call DB.POP.DMVARS ('''||l_aw||''')');
519 l_ecm.ECMLocator := zpb_aw.interp('shw DM.ECMLOCATOR');
520 if (l_ecm.ECMLocator = 'NA') then
521 l_ecm.ECMLocator := '';
522 return null;
523 end if;
524
525 l_ecm.AggTypeDim := get_prop('AGGTYPEDIM', l_ecm.ECMLocator);
526 l_ecm.AlcCondDim := get_prop('ALCCONDDIM', l_ecm.ECMLocator);
527 l_ecm.AlcTypeDim := get_prop('ALCTYPEDIM', l_ecm.ECMLocator);
528 l_ecm.AnnEntryDim := get_prop('ANNENTRYDIM', l_ecm.ECMLocator);
529 l_ecm.AnnPropDim := get_prop('ANNPROPDIM', l_ecm.ECMLocator);
530 l_ecm.AttrDim := get_prop('ATTRDIM', l_ecm.ECMLocator);
531 l_ecm.AttrDimScpFrm := get_prop('ATTRDIMSCPFRM', l_ecm.ECMLocator);
532 l_ecm.DataTypeDim := get_prop('DATATYPEDIM', l_ecm.ECMLocator);
533 l_ecm.DimDim := get_prop('DIMDIM', l_ecm.ECMLocator);
534 l_ecm.ShapeEntryDim := get_prop('SHAPEENTRYDIM', l_ecm.DimDim);
535 l_ecm.DimTypeRel := get_prop('DIMTYPEREL', l_ecm.DimDim);
536 l_ecm.ExpObjVar := get_prop('EXPOBJVAR', l_ecm.DimDim);
537 l_ecm.FmtBoolPropDim := get_prop('FMTBOOLPROPDIM', l_ecm.ECMLocator);
538 l_ecm.FmtTextPropDim := get_prop('FMTTEXTPROPDIM', l_ecm.ECMLocator);
539 l_ecm.IsMeasDimFrm := get_prop('ISMEASDIMFRM', l_ecm.ECMLocator);
540
541 l_meas := zpb_aw.interp('shw '||l_awQual||l_ecm.ExpObjVar||' ('||l_awQual||
542 l_ecm.DimDim||' lmt ('||l_awQual||l_ecm.DimDim||
543 ' to '||l_awQual||l_ecm.IsMeasDimFrm||' eq yes)');
544
545 l_ecm.IsCurrInstVar := get_prop('ISCURRINSTVAR', l_meas);
546 l_ecm.IsDataDimFrm := get_prop('ISDATADIMFRM', l_ecm.DimDim);
547 l_ecm.IsDataDimVar := get_prop('ISDATADIMVAR', l_ecm.DimDim);
548 l_ecm.IsOwnerDim := get_prop('ISOWNERDIM', l_ecm.DimDim);
549 l_ecm.LangDim := get_prop('LANGDIM', l_ecm.ECMLocator);
550 l_ecm.LastQueryDimsVS:= get_prop('LASTQUERYDIMSVS', l_ecm.DimDim);
551 l_ecm.LdscVar := get_prop('LDSCVAR', l_ecm.DimDim);
552 l_ecm.LineTypeDim := get_prop('LINETYPEDIM', l_ecm.ECMLocator);
553 l_ecm.MeasColVar := get_prop('MEASCOLVAR', l_meas);
554 l_ecm.MeasDimVar := get_prop('MEASDIMVAR', l_meas);
555 l_ecm.MeasExpObjVar := get_prop('EXPOBJVAR', l_meas);
556 l_ecm.MeasShapeRel := get_prop('MEASSHAPEREL', l_ecm.ShapeEntryDim);
557 l_ecm.ShapeDimVS := get_prop('SHAPEDIMVS', l_ecm.ShapeEntryDim);
558 l_ecm.MeasTypeRel := get_prop('MEASTYPEREL', l_meas);
559 l_ecm.MeasViewDim := get_prop('MEASVIEWDIM', l_meas);
560 l_ecm.MeasViewRel := get_prop('MEASVIEWREL', l_ecm.MeasViewDim);
561 l_ecm.NumAttrFrm := get_prop('NUMATTRFRM', l_ecm.DimDim);
562 l_ecm.NumHierFrm := get_prop('NUMHIERFRM', l_ecm.DimDim);
563 l_ecm.NumLevelFrm := get_prop('NUMLEVELFRM', l_ecm.DimDim);
564 l_ecm.PlLdscVar := get_prop('PLLDSCVAR', l_ecm.DimDim);
565 l_ecm.PlSdscVar := get_prop('PLSDSCVAR', l_ecm.DimDim);
566 l_ecm.SdscVar := get_prop('SDSCVAR', l_ecm.DimDim);
567 l_ecm.SecentityDim := get_prop('SECENTITYDIM', l_ecm.ECMLocator);
568 l_ecm.SecOwnerDim := get_prop('SECOWNERDIM', l_ecm.ECMLocator);
569 l_ecm.SecOwnerMapRel := get_prop('SECOWNERMAPREL', l_ecm.ECMLocator);
570 l_ecm.SecScopeFrm := get_prop('SECSCOPEFRM', l_ecm.ECMLocator);
571 l_ecm.SecUserDim := get_prop('SECUSERDIM', l_ecm.ECMLocator);
572 l_ecm.SecWrtAccFrm := get_prop('SECWRTACCFRM', l_ecm.ECMLocator);
573 l_ecm.SecWrtMapVar := get_prop('SECWRTMAPVAR', l_ecm.ECMLocator);
574 l_ecm.TLvlTypeDim := get_prop('TLVLTYPEDIM', l_ecm.ECMLocator);
575
576 --
577 -- Remove the AW qualifier:
578 --
579 l_ecm.ECMLocator := substr(l_ecm.ECMLocator,instr(l_ecm.ECMLocator, '!')+1);
580
581 m_global_ecm_hash(l_aw) := l_ecm;
582
583 return l_ecm;
584 end GET_GLOBAL_ECM;
585
586 -------------------------------------------------------------------------------
587 -- GET_LINE_TYPE_ECN
588 --
589 -- Builds a LINE_TYPE_ECM object.
590 --
591 -- IN: l_global_ecm (GLOBAL_ECM) - The Global Ecm
592 -- p_aw (varchar2) - The aw to pull the info from. If null, then
593 -- the current AW is used.
594 -- OUT: LINE_TYPE_ECM
595 --
596 -------------------------------------------------------------------------------
597 function GET_LINE_TYPE_ECM (p_aw in varchar2)
598 return LINE_TYPE_ECM is
599 l_lineType varchar2(30);
600 l_line_type_ecm line_type_ecm;
601 l_aw varchar2(30);
602 l_global_ecm global_ecm;
603 begin
607 l_aw := m_olapSchema||p_aw;
604 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
605 l_aw := p_aw;
606 else
608 end if;
609 zpb_aw.execute ('aw attach '||l_aw);
610 l_global_ecm := get_global_ecm(p_aw);
611 l_aw := l_aw||'!';
612
613 l_lineType := l_aw||l_global_ecm.LineTypeDim;
614
615 l_line_type_ecm.LdscVar := get_prop('LDSCVAR', l_lineType);
616 l_line_type_ecm.LineTypeCDataVar := get_prop('LINETYPECDATAVAR',l_lineType);
617 l_line_type_ecm.LTypeBWVar := get_prop('LTYPEBWVAR', l_lineType);
618 l_line_type_ecm.LTypeNatSignVar := get_prop('LTYPENATSIGNVAR', l_lineType);
619
620 return l_line_type_ecm;
621 end GET_LINE_TYPE_ECM;
622
623 -------------------------------------------------------------------------------
624 -- GET_SECURITY_ECM
625 --
626 -- Builds a SECURITY_ECM object.
627 --
628 -- IN: l_global_ecm (GLOBAL_ECM) - The Global Ecm
629 -- p_aw (varchar2) - The aw to pull the info from. If null, then
630 -- the current AW is used.
631 -- OUT: SECURITY_ECM
632 --
633 -------------------------------------------------------------------------------
634 function GET_SECURITY_ECM (p_aw in varchar2)
635 return SECURITY_ECM
636 is
637 l_secEntity varchar2(30);
638 l_security_ecm security_ecm;
639 l_aw varchar2(30);
640 l_global_ecm global_ecm;
641 begin
642 if (instr (p_aw,'.') > 0 or p_aw = 'SHARED' or p_aw = 'PERSONAL') then
643 l_aw := p_aw;
644 else
645 l_aw := m_olapSchema||p_aw;
646 end if;
647 zpb_aw.execute ('aw attach '||l_aw);
648 l_global_ecm := get_global_ecm(p_aw);
649 l_aw := l_aw||'!';
650
651 l_secEntity := l_aw||l_global_ecm.SecentityDim;
652
653 l_security_ecm.SecRInUseFrm := get_prop ('SECRINUSEFRM', l_secEntity);
654 l_security_ecm.SecRInUseVar := get_prop ('SECRINUSEVAR', l_secEntity);
655
656 return l_security_ecm;
657
658 end GET_SECURITY_ECM;
659
660 -------------------------------------------------------------------------------
661 -- TEST - Tests various functions. Internal/Dev use only
662 --
663 -------------------------------------------------------------------------------
664 procedure TEST
665 is
666 l_ecm global_ecm;
667 l_decm dimension_ecm;
668 l_data dimension_data;
669 begin
670 null;
671 /* l_ecm := GET_GLOBAL_ECM;
672
673 dbms_output.put_line ('ECM Locator: '||l_ecm.ECMLocator);
674 dbms_output.put_line ('DimDim: '||l_ecm.DimDim);
675 dbms_output.put_line ('ExpObjVar: '||l_ecm.ExpObjVar);
676 dbms_output.put_line ('LdscVar: '||l_ecm.LdscVar);
677 dbms_output.put_line ('PlLdscVar: '||l_ecm.PlLdscVar);
678 dbms_output.put_line ('MdscVar: '||l_ecm.MdscVar);
679 dbms_output.put_line ('SdscVar: '||l_ecm.SdscVar);
680 dbms_output.put_line ('DimTypeRel: '||l_ecm.DimTypeRel);
681 dbms_output.put_line ('IsDataDimFrm: '||l_ecm.IsDataDimFrm);
682
683 l_decm := GET_DIMENSION_ECM ('D1', l_ecm);
684 dbms_output.put_line ('ANCESTORREL: '||l_decm.AncestorRel);
685 dbms_output.put_line ('ANNDIM: '||l_decm.AnnDim);
686 dbms_output.put_line ('DEPTHFRM: '||l_decm.DepthFrm);
687 dbms_output.put_line ('DEPTHFRM1: '||l_decm.DepthFrm1);
688 dbms_output.put_line ('DFLTLEVELREL: '||l_decm.DfltLevelRel);
689 dbms_output.put_line ('DIMDRILLDIR: '||l_decm.DimDrillDir);
690 dbms_output.put_line ('DRILLINFOFRM: '||l_decm.DrillInfoFrm);
691 dbms_output.put_line ('FULLORDERVAR: '||l_decm.FullOrderVar);
692 dbms_output.put_line ('HIERDEFAULT: '||l_decm.HierDefault);
693 dbms_output.put_line ('HIERDIM: '||l_decm.HierDim);
694 dbms_output.put_line ('HORDERVS: '||l_decm.HOrderVs);
695 dbms_output.put_line ('INHIERVAR: '||l_decm.InHierVar);
696 dbms_output.put_line ('LDSCVAR: '||l_decm.LdscVar);
697 dbms_output.put_line ('LEVELDIM: '||l_decm.LevelDim);
698 dbms_output.put_line ('LEVELREL: '||l_decm.LevelRel);
699 dbms_output.put_line ('MDSCVAR: '||l_decm.MdscVar);
700 dbms_output.put_line ('MLEVELLDSCFRM: '||l_decm.MLevelLdscFrm);
701 dbms_output.put_line ('MPLLEVELLDSCFRM: '||l_decm.MPLLevelLdscFrm);
702 dbms_output.put_line ('NAMEFRAGMENT: '||l_decm.NameFragment);
703 dbms_output.put_line ('PARENTREL: '||l_decm.ParentRel);
704 dbms_output.put_line ('ROOTVALSET: '||l_decm.RootValSet);
705 dbms_output.put_line ('SDSCVAR: '||l_decm.SdscVar);
706 dbms_output.put_line ('SECREADACCVAR: '||l_decm.SecReadAccVar);
707 dbms_output.put_line ('SIBORDERVAR: '||l_decm.SibOrderVar);
708
709 l_data := GET_DIMENSION_DATA ('D1', l_ecm);
710 dbms_output.put_line ('Ldsc: '||l_data.Ldsc);
711 dbms_output.put_line ('PlLdsc: '||l_data.PlLdsc);
712 dbms_output.put_line ('Mdsc: '||l_data.Mdsc);
713 dbms_output.put_line ('Sdsc: '||l_data.Sdsc);
714 dbms_output.put_line ('Type: '||l_data.Type);
715 dbms_output.put_line ('ExpObj: '||l_data.ExpObj);
716 dbms_output.put_line ('IsDataDim: '||l_data.IsDataDim);
717 */
718 end TEST;
719
720 end ZPB_ECM;