1 package body ZPB_METADATA_NAMES as
2 /* $Header: zpbmetanames.plb 120.8 2007/12/04 15:32:33 mbhat ship $ */
3
4 -------------------------------------------------------------------------------
5 -- CONVERT_ID - Converts an ID to replace . with _
6 --
7 -- IN: p_id - The ID to convert
8 -- OUT: The converted id
9 -------------------------------------------------------------------------------
10 function CONVERT_ID (p_id in varchar2)
11 return varchar2 is
12 l_ret varchar2(30);
13 i number;
14 j number;
15 begin
16 i := 1;
17 loop
18 j := instr(p_id, '.', i);
19 if (j=0) then
20 l_ret := l_ret||substr(p_id, i);
21 else
22 l_ret := l_ret||substr(p_id, i, j-i)||'_';
23 i := j+1;
24 end if;
25 exit when j=0;
26 end loop;
27
28 return l_ret;
29 end CONVERT_ID;
30
31 -------------------------------------------------------------------------------
32 -- GET_ALL_ANNOTATIONS_VIEW - Gets the all annotations view name
33 --
34 -- IN: p_aw - The AW
35 -- OUT: The view name for all annotations
36 -------------------------------------------------------------------------------
37 function GET_ALL_ANNOTATIONS_VIEW (p_aw in varchar2)
38 return varchar2 is
39 begin
40 return zpb_aw.get_aw_short_name(p_aw)||'_ANNOTATIONS_V';
41 end GET_ALL_ANNOTATIONS_VIEW;
42
43 -------------------------------------------------------------------------------
44 -- GET_ALL_ANNOT_PERS_VIEW - Gets the personal all annotations view name
45 --
46 -- IN: p_aw - The AW
47 -- OUT: The view name for all personal annotations
48 -------------------------------------------------------------------------------
49 function GET_ALL_ANNOT_PERS_VIEW (p_aw in varchar2)
50 return varchar2 is
51 begin
52 return zpb_aw.get_aw_short_name(p_aw)||'_PERS_ANNOTS_V';
53 end GET_ALL_ANNOT_PERS_VIEW;
54
55 -------------------------------------------------------------------------------
56 -- GET_ATTRIBUTE_COLUMN - Gets the column name for an attribute
57 --
58 -- IN: p_dimID - The Dimension ID in the DimDim
59 -- p_attribute - The Attribute ID in the AttrDim
60 -- OUT: The column name for the attribute
61 -------------------------------------------------------------------------------
62 function GET_ATTRIBUTE_COLUMN (p_dimID in varchar2,
63 p_attribute in varchar2)
64 return varchar2 is
65 begin
66 return p_dimID||'_'||p_attribute;
67 end GET_ATTRIBUTE_COLUMN;
68
69 -------------------------------------------------------------------------------
70 -- GET_ATTRIBUTE_CWM2_NAME - Gets the cwm2 name for an attribute
71 --
72 -- IN: p_aw - The AW which the contains the attribte
73 -- p_dimID - The Dimension ID in the DimDim
74 -- p_attribute - The Attribute ID in the AttrDim
75 -- OUT: The cwm2 name for the attribute
76 -------------------------------------------------------------------------------
77 function GET_ATTRIBUTE_CWM2_NAME (p_aw in varchar2,
78 p_dimID in varchar2,
79 p_attribute in varchar2)
80 return varchar2 is
81 begin
82 if (instr (p_aw, 'ZPBDATA') > 0) then
83 return p_attribute;
84 else
85 return 'PERSONAL_ATTR_'||p_dimID||'_'||p_attribute;
86 end if;
87 end GET_ATTRIBUTE_CWM2_NAME;
88
89 -------------------------------------------------------------------------------
90 -- GET_ATTRIBUTE_SCOPE_VIEW - Gets the attribute scope view name
91 --
92 -- IN: p_aw - The AW
93 -- OUT: The view name for attribute scoping
94 -------------------------------------------------------------------------------
95 function GET_ATTRIBUTE_SCOPE_VIEW (p_aw in varchar2)
96 return varchar2 is
97 begin
98 return zpb_aw.get_aw_short_name(p_aw)||'_ATTRSCOPE_V';
99 end GET_ATTRIBUTE_SCOPE_VIEW;
100
101 -------------------------------------------------------------------------------
102 -- GET_CATALOG_CWM2_NAME - Gets the cwm2 name for a catalog
103 --
104 -- IN: p_aw - The AW which the catalog contains
105 -- OUT: The column name for the catalog
106 -------------------------------------------------------------------------------
107 function GET_CATALOG_CWM2_NAME (p_aw in varchar2)
108 return varchar2 is
109 begin
110 if (instr (p_aw, 'ZPBDATA') > 0) then
111 return zpb_aw.get_aw_short_name(p_aw)||'_CAT';
112 else
113 return 'PERSONAL_CAT';
114 end if;
115 end GET_CATALOG_CWM2_NAME;
116
117 -------------------------------------------------------------------------------
118 -- GET_DATA_EXCEPTION_VIEW - Gets the data exception view name
119 --
120 -- IN: p_aw - The AW
121 --
122 -- OUT: The data exception view name
123 -------------------------------------------------------------------------------
124 function GET_DATA_EXCEPTION_VIEW (p_aw in varchar2)
125 return varchar2 is
126 begin
127 return zpb_aw.get_aw_short_name(p_aw)||'_DATAEXC_V';
128 end GET_DATA_EXCEPTION_VIEW;
129
130 -------------------------------------------------------------------------------
131 -- GET_DIMENSION_COLUMN - Gets the column name for a dimension
132 --
133 -- IN: p_dimID - The Dimension ID in the DimDim
134 -- OUT: The column name for the dimension
135 -------------------------------------------------------------------------------
136 function GET_DIMENSION_COLUMN (p_dimID in varchar2)
137 return varchar2 is
138 begin
139 return convert_ID(p_dimID)||'_MEMBER';
140 end GET_DIMENSION_COLUMN;
141
142 -------------------------------------------------------------------------------
143 -- GET_DIMENSION_CWM2_NAME - Gets the cwm2 name for a dimension
144 --
145 -- IN: p_aw - The AW where the dimension exists
146 -- p_dimID - The Dimension ID in the DimDim
147 -- OUT: The column name for the dimension
148 -------------------------------------------------------------------------------
149 function GET_DIMENSION_CWM2_NAME (p_aw in varchar2,
150 p_dimID in varchar2)
151 return varchar2 is
152 begin
153 if (instr (p_aw, 'ZPBDATA') > 0) then
154 return zpb_aw.get_aw_short_name(p_aw)||'_DIM_'||convert_ID(p_dimID);
155 else
156 return 'PERSONAL_DIM_'||convert_ID(p_dimID);
157 end if;
158 end GET_DIMENSION_CWM2_NAME;
159
160 -------------------------------------------------------------------------------
161 -- GET_DIMENSION_VIEW - Gets the cwm2 name for a dimension
162 --
163 -- IN: p_aw - The AW where the dimension exists
164 -- p_type - PERSONAL or SHARED, the AW type
165 -- p_dimID - The Dimension ID in the DimDim
166 -- p_hierarchyID - The hierarchy ID in the HierDim
167 -- OUT: The view name for the dimension/hierarchy
168 -------------------------------------------------------------------------------
169 function GET_DIMENSION_VIEW (p_aw in varchar2,
170 p_type in varchar2,
171 p_dimID in varchar2,
172 p_hierarchyID in varchar2)
173 return varchar2 is
174 l_aw varchar2(30);
175 begin
176 if (p_type = 'PERSONAL') then
177 l_aw := zpb_aw.get_aw_tiny_name(p_aw)||'PRS';
178 else
179 l_aw := zpb_aw.get_aw_tiny_name(p_aw);
180 end if;
181 if (instr(p_hierarchyID, 'V') > 0) then
182 return l_aw||'_'||convert_id(p_dimID)||'_HV'||
183 substr(p_hierarchyID, instr(p_hierarchyID, 'V')+1)||'_V';
184 else
185 return l_aw||'_'||convert_id(p_dimID)||'_H'||nvl(p_hierarchyID, 0)||'_V';
186 end if;
187
188 end GET_DIMENSION_VIEW;
189
190 -------------------------------------------------------------------------------
191 -- GET_DIM_CALENDAR_COLUMN - Gets the column name for the calendar id, used
192 -- for time dimension only
193 --
194 -- IN: p_dimID - The Dimension ID in the DimDim
195 -- OUT: The column name for the calendar id
196 -------------------------------------------------------------------------------
197 function GET_DIM_CALENDAR_COLUMN (p_dimID in varchar2)
198 return varchar2 is
199 begin
200 return p_dimID||'_CALENDAR';
201 end GET_DIM_CALENDAR_COLUMN;
202 -------------------------------------------------------------------------------
203 -- GET_DIM_CODE_COLUMN - Gets the column name for the dimension code
204 --
205 -- IN: p_dimID - The Dimension ID in the DimDim
206 -- OUT: The column name for the code
207 -------------------------------------------------------------------------------
208 function GET_DIM_CODE_COLUMN (p_dimID in varchar2)
209 return varchar2 is
210 begin
211 return p_dimID||'_CODE';
212 end GET_DIM_CODE_COLUMN;
213
214 -------------------------------------------------------------------------------
215 -- GET_DIM_ENDDATE_COLUMN - Gets the column name for the enddate attribute
216 -- for a dimension
217 --
218 -- IN: p_dimID - The Dimension ID in the DimDim
219 -- OUT: The column name for the enddate attribute
220 -------------------------------------------------------------------------------
221 function GET_DIM_ENDDATE_COLUMN (p_dimID in varchar2)
222 return varchar2 is
223 begin
224 return p_dimID||'_ENDDATE';
225 end GET_DIM_ENDDATE_COLUMN;
226
227 -------------------------------------------------------------------------------
228 -- GET_DIM_GID_COLUMN - Gets the column name for the GID attribute
229 -- for a dimension
230 --
231 -- IN: p_dimID - The Dimension ID in the DimDim
232 -- p_hierID - The Hierarchy ID, for measure views. Null if dim view
233 -- OUT: The column name for the GID attribute
234 -------------------------------------------------------------------------------
235 function GET_DIM_GID_COLUMN (p_dimID in varchar2,
236 p_hierID in varchar2)
237 return varchar2 is
238 begin
239 if (p_dimID is null) then
240 return 'NULL_GID';
241 elsif (p_hierID is null) then
242 return convert_ID(p_dimID)||'_GID';
243 else
244 return convert_ID(p_dimID)||'_H'||p_hierID||'_GID';
245 end if;
246 end GET_DIM_GID_COLUMN;
247
248 -------------------------------------------------------------------------------
249 -- GET_DIM_LONG_NAME_COLUMN - Gets the column name for the long name attribute
250 -- for a dimension
251 --
252 -- IN: p_dimID - The Dimension ID in the DimDim
253 -- OUT: The column name for the long name attribute
254 -------------------------------------------------------------------------------
255 function GET_DIM_LONG_NAME_COLUMN (p_dimID in varchar2)
256 return varchar2 is
257 begin
258 return convert_id(p_dimID)||'_LONG';
259 end GET_DIM_LONG_NAME_COLUMN;
260
261 -------------------------------------------------------------------------------
262 -- GET_DIM_LONG_NAME_CWM2 - Gets the cwm2 name for the long name attribute
263 -- for a dimension
264 --
265 -- IN: p_dimID - The Dimension ID in the DimDim
266 -- OUT: The cwm2 name for the long name attribute
267 -------------------------------------------------------------------------------
268 function GET_DIM_LONG_NAME_CWM2 (p_aw in varchar2,
269 p_dimID in varchar2)
270 return varchar2 is
271 begin
272 if (instr (p_aw, 'ZPBDATA') > 0) then
273 return 'LONG DESCRIPTION';
274 else
275 return zpb_aw.get_aw_short_name(p_aw)||'_'||convert_id(p_dimID)||'_LONG_NM';
276 end if;
277 end GET_DIM_LONG_NAME_CWM2;
278
279 -------------------------------------------------------------------------------
280 -- GET_DIM_ORDER_COLUMN - Gets the column name for the dimension order
281 --
282 -- IN: p_dimID - The Dimension ID in the DimDim
283 -- OUT: The column name for the order
284 -------------------------------------------------------------------------------
285 function GET_DIM_ORDER_COLUMN (p_dimID in varchar2)
286 return varchar2 is
287 begin
288 return convert_id(p_dimID)||'_ORDER';
289 end GET_DIM_ORDER_COLUMN;
290
291 -------------------------------------------------------------------------------
292 -- GET_DIM_PARENT_COLUMN - Gets the column name for the parent attribute
293 -- for a dimension
294 --
295 -- IN: p_dimID - The Dimension ID in the DimDim
296 -- OUT: The column name for the parent attribute
297 -------------------------------------------------------------------------------
298 function GET_DIM_PARENT_COLUMN (p_dimID in varchar2)
299 return varchar2 is
300 begin
301 return convert_ID(p_dimID)||'_PARENT';
302 end GET_DIM_PARENT_COLUMN;
303
304 -------------------------------------------------------------------------------
305 -- GET_DIM_PGID_COLUMN - Gets the column name for the PGID attribute
306 -- for a dimension
307 --
308 -- IN: p_dimID - The Dimension ID in the DimDim
309 -- OUT: The column name for the PGID attribute
310 -------------------------------------------------------------------------------
311 function GET_DIM_PGID_COLUMN (p_dimID in varchar2)
312 return varchar2 is
313 begin
314 return convert_ID(p_dimID)||'_PGID';
315 end GET_DIM_PGID_COLUMN;
316
317 -------------------------------------------------------------------------------
318 -- GET_DIM_SHORT_NAME_COLUMN - Gets the column name for the short name
319 -- attribute for a dimension
320 --
321 -- IN: p_dimID - The Dimension ID in the DimDim
322 -- OUT: The column name for the short name attribute
323 -------------------------------------------------------------------------------
324 function GET_DIM_SHORT_NAME_COLUMN (p_dimID in varchar2)
325 return varchar2 is
326 begin
327 return convert_id(p_dimID)||'_SHORT';
328 end GET_DIM_SHORT_NAME_COLUMN;
329
330 -------------------------------------------------------------------------------
331 -- GET_DIM_SHORT_NAME_CWM2 - Gets the cwm2 name for the short name
332 -- attribute for a dimension
333 --
334 -- IN: p_dimID - The Dimension ID in the DimDim
335 -- OUT: The cwm2 name for the short name attribute
336 -------------------------------------------------------------------------------
337 function GET_DIM_SHORT_NAME_CWM2 (p_aw in varchar2,
338 p_dimID in varchar2)
339 return varchar2 is
340 begin
341 if (instr (p_aw, 'ZPBDATA') > 0) then
342 return 'SHORT DESCRIPTION';
343 else
344 return zpb_aw.get_aw_short_name(p_aw)||'_'||
345 convert_id(p_dimID)||'_SHORT_NM';
346 end if;
347 end GET_DIM_SHORT_NAME_CWM2;
348
349 -------------------------------------------------------------------------------
350 -- GET_DIM_TIMESPAN_COLUMN - Gets the column name for the timespan attribute
351 -- for a dimension
352 --
353 -- IN: p_dimID - The Dimension ID in the DimDim
354 -- OUT: The column name for the timespan attribute
355 -------------------------------------------------------------------------------
356 function GET_DIM_TIMESPAN_COLUMN (p_dimID in varchar2)
357 return varchar2 is
358 begin
359 return p_dimID||'_TIMESPAN';
360 end GET_DIM_TIMESPAN_COLUMN;
361
362 -------------------------------------------------------------------------------
363 -- GET_EXCEPTION_CHECK_TBL - Gets the generic exception check table
364 --
365 -- IN: p_aw - The AW
366 --
367 -- OUT: The exception check table
368 -------------------------------------------------------------------------------
369 function GET_EXCEPTION_CHECK_TBL (p_aw in varchar2)
370 return varchar2 is
371 begin
372 return zpb_aw.get_aw_short_name(p_aw)||'_EXCPT_T';
373 end GET_EXCEPTION_CHECK_TBL;
374
375 -------------------------------------------------------------------------------
376 -- GET_EXCEPTION_COLUMN - Gets the generic exception check column
377 --
378 -- OUT: The exception check column
379 -------------------------------------------------------------------------------
380 function GET_EXCEPTION_COLUMN
381 return varchar2 is
382 begin
383 return 'EXCEPTION_COL';
384 end GET_EXCEPTION_COLUMN;
385
386 -------------------------------------------------------------------------------
387 -- GET_EXCEPTION_MEAS_CUBE - Gets the exception check measure cube name
388 --
389 -- IN: p_aw - The AW
390 -- p_instance - The Instance ID
391 --
392 -- OUT: The exception check measure cube name
393 -------------------------------------------------------------------------------
394 function GET_EXCEPTION_MEAS_CUBE (p_aw in varchar2,
395 p_instance in varchar2)
396 return varchar2 is
397 l_name varchar2(30);
398 begin
399 if (instr (p_aw, 'ZPBDATA') > 0) then
400 l_name := zpb_aw.get_aw_short_name(p_aw);
401 else
402 l_name := 'PERSONAL_';
403 end if;
404 return l_name||'_EXCEPT_'||p_instance||'_CB';
405 end GET_EXCEPTION_MEAS_CUBE;
406
407 -------------------------------------------------------------------------------
408 -- GET_EXCEPTION_MEAS_CWM2 - Gets the exception check measure cwm2 name
409 --
410 -- IN: p_aw - The AW
411 -- p_instance - The Instance ID
412 --
413 -- OUT: The exception check measure cwm2 name
414 -------------------------------------------------------------------------------
415 function GET_EXCEPTION_MEAS_CWM2 (p_aw in varchar2,
416 p_instance in varchar2)
417 return varchar2 is
418 l_name varchar2(30);
419 begin
420 if (instr (p_aw, 'ZPBDATA') > 0) then
421 l_name := zpb_aw.get_aw_short_name(p_aw);
422 else
423 l_name := 'PERSONAL_';
424 end if;
425 return l_name||'SHV'||p_instance||'_EXC_DF';
426 end GET_EXCEPTION_MEAS_CWM2;
427
428 ---------------------------------------------------------------------------
429 -- GET_EXCH_RATES_VIEW - Gets the exchange rates view name
430 --
431 -- IN: p_aw - The AW
432 --
433 -- OUT: The exchange rates view name
434 -------------------------------------------------------------------------------
435 function GET_EXCH_RATES_VIEW (p_aw in varchar2)
436 return varchar2 is
437 begin
438 return zpb_aw.get_aw_short_name(p_aw)||'_RATES_V';
439 end GET_EXCH_RATES_VIEW;
440
441 ---------------------------------------------------------------------------
442 -- GET_EXCH_SCENARIO_VIEW - Gets the exchange scenario view name
443 --
444 -- IN: p_aw - The AW
445 --
446 -- OUT: The exchange scenario view name
447 -------------------------------------------------------------------------------
448 function GET_EXCH_SCENARIO_VIEW (p_aw in varchar2)
449 return varchar2 is
450 begin
451 return zpb_aw.get_aw_short_name(p_aw)||'_SCENARIO_V';
452 end GET_EXCH_SCENARIO_VIEW;
453
454 -------------------------------------------------------------------------------
455 -- GET_FULL_SCOPE_COLUMN - Gets the full scope column
456 --
457 -- IN: p_aw - The AW
458 --
459 -- OUT: The full scope column
460 -------------------------------------------------------------------------------
461 function GET_FULL_SCOPE_COLUMN
462 return varchar2 is
463 begin
464 return 'SECFULLSCPVW_F';
465 end GET_FULL_SCOPE_COLUMN;
466
467 -------------------------------------------------------------------------------
468 -- GET_FULL_SCOPE_CWM2_NAME - Gets the full scope measure
469 --
470 -- IN: p_aw - The AW
471 --
472 -- OUT: The full scope cwm2 measure name
473 -------------------------------------------------------------------------------
474 function GET_FULL_SCOPE_CWM2_NAME (p_aw in varchar2)
475 return varchar2 is
476 l_aw VARCHAR2(60);
477 begin
478 l_aw := zpb_aw.get_aw_short_name(p_aw);
479 --
480 -- For backwards compatibility to rel 1. Bug 4507185
481 --
482 if (l_aw = 'ZPBDATA') then
483 return l_aw||'_MEAS_SECFULLSCPVW_F';
484 else
485 return l_aw||'_MS_SECFULLSCP';
486 end if;
487 end GET_FULL_SCOPE_CWM2_NAME;
488
489 -------------------------------------------------------------------------------
490 -- GET_HIERARCHY_CWM2_NAME - Gets the cwm2 hierarchy name
491 --
492 -- IN: p_dimID - The Dimension ID in the DimDim
493 -- p_hierarchyID - The Hierarchy ID in the HierarchyDim
494 -- OUT: The column name for the timespan attribute
495 -------------------------------------------------------------------------------
496 function GET_HIERARCHY_CWM2_NAME (p_aw in varchar2,
497 p_dimID in varchar2,
498 p_hierarchyID in varchar2)
499 return varchar2 is
500 begin
501 if (instr (p_aw, 'ZPBDATA') > 0) then
502 return zpb_aw.get_aw_short_name(p_aw)||'_'||p_dimID||'H_'||
503 nvl(p_hierarchyID, 0);
504 else
505 return 'PERSONAL_'||p_dimID||'H_'||nvl(p_hierarchyID, 0);
506 end if;
507 end GET_HIERARCHY_CWM2_NAME;
508
509 -------------------------------------------------------------------------------
510 -- GET_HIERARCHY_SCOPE_VIEW - Gets the view name for the hierarchy scoping view
511 --
512 -- IN: p_aw - The AW where the dimension exists
513 -- p_dimID - The Dimension ID in the DimDim
514 -- OUT: The view name for the dimension/hierarchy
515 -------------------------------------------------------------------------------
516 function GET_HIERARCHY_SCOPE_VIEW (p_aw in varchar2,
517 p_dimID in varchar2)
518 return varchar2 is
519 begin
520 return zpb_aw.get_aw_short_name(p_aw)||'_HIERSCP_'||p_dimID||'_V';
521 end GET_HIERARCHY_SCOPE_VIEW;
522
523 -------------------------------------------------------------------------------
524 -- GET_LEVEL_COLUMN - Gets the level column name
525 --
526 -- IN: p_dimID - The Dimension ID in the DimDim
527 -- p_levelID - The Level ID in the LevelDim
528 -- OUT: The column name for the level
529 -------------------------------------------------------------------------------
530 function GET_LEVEL_COLUMN (p_dimID in varchar2,
531 p_levelID in varchar2)
532 return varchar2 is
533 begin
534 return convert_ID(p_dimID)||'LV_'||nvl(p_levelID, 0);
535 end GET_LEVEL_COLUMN;
536
537 -------------------------------------------------------------------------------
538 -- GET_LEVELREL_COLUMN - Gets the levelRel column name
539 --
540 -- IN: p_dimID - The Dimension ID in the DimDim
541 -- OUT: The column name for the levelRel
542 -------------------------------------------------------------------------------
543 function GET_LEVELREL_COLUMN (p_dimID in varchar2)
544 return varchar2 is
545 begin
546 return convert_ID(p_dimID)||'_LVLREL';
547 end GET_LEVELREL_COLUMN;
548
549 -------------------------------------------------------------------------------
550 -- GET_LEVEL_CWM2_NAME - Gets the cwm2 level name
551 --
552 -- IN: p_dimID - The Dimension ID in the DimDim
553 -- p_levelID - The Level ID in the LevelDim
554 -- OUT: The level cwm2 name
555 -------------------------------------------------------------------------------
556 function GET_LEVEL_CWM2_NAME (p_aw in varchar2,
557 p_dimID in varchar2,
558 p_hierID in varchar2,
559 p_levelID in varchar2)
560 return varchar2 is
561 begin
562 if (instr (p_aw, 'ZPBDATA') > 0) then
563 return zpb_aw.get_aw_short_name(p_aw)||'_'||p_dimID||'H'||
564 nvl(p_hierID, 0)||'LV'||nvl(p_levelID, 0);
565 else
566 return 'PERSONAL_'||p_dimID||'H'||nvl(p_hierID, 0)||
567 'LV'||nvl(p_levelID, 0);
568 end if;
569 end GET_LEVEL_CWM2_NAME;
570
571 -------------------------------------------------------------------------------
572 -- GET_LEVEL_SCOPE_VIEW - Gets the name of the view for level scoping
573 --
574 -- IN: p_aw - The AW where the dimension exists
575 -- p_dimID - The Dimension ID in the DimDim
576 -- OUT: The ID for the level
577 -------------------------------------------------------------------------------
578 function GET_LEVEL_SCOPE_VIEW (p_aw in varchar2,
579 p_dimID in varchar2)
580 return varchar2 is
581 begin
582 return zpb_aw.get_aw_short_name(p_aw)||'_LVLSCP_'||p_dimID||'_V';
583 end GET_LEVEL_SCOPE_VIEW;
584
585 -------------------------------------------------------------------------------
586 -- GET_MEASURE_ANNOT_CWM2 - Gets the measure annotation cwm2 name
587 --
588 -- IN: p_aw - The AW
589 -- p_instance - The Instance ID
590 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
591 -- or 'APPROVAL')
592 -- OUT: The annotation measure cwm2 name
593 -------------------------------------------------------------------------------
594 function GET_MEASURE_ANNOT_CWM2 (p_aw in varchar2,
595 p_instance in varchar2,
596 p_type in varchar2,
597 p_template in varchar2,
598 p_approvee in varchar2,
599 p_currency in varchar2)
600 return varchar2 is
601 l_name varchar2(60);
602 begin
603 if (instr (p_aw, 'ZPBDATA') > 0) then
604 l_name := zpb_aw.get_aw_short_name(p_aw);
605 else
606 l_name := 'PERSONAL_';
607 end if;
608 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
609 if (p_template is not null) then
610 l_name := l_name||'T'||p_template;
611 end if;
612 if (p_approvee is not null) then
613 l_name := l_name||'A'||p_approvee;
614 end if;
615
616 --add translated/entered currency suffix
617 if(instr (p_type, 'ENTERED') > 0) then
618 l_name := l_name || '_ENT';
619 end if;
620
621 if(instr (p_type, 'TRANSLATED') > 0) then
622 l_name := l_name || '_T' || p_currency;
623 end if;
624
625 return l_name||'_AN';
626 end GET_MEASURE_ANNOT_CWM2;
627
628 -------------------------------------------------------------------------------
629 -- GET_MEASURE_CWM2 - Gets the measure cwm2 name
630 --
631 -- IN: p_aw - The AW
632 -- p_instance - The Instance ID
633 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
634 -- or 'APPROVAL')
635 -- OUT: The measure cwm2 name
636 -------------------------------------------------------------------------------
637 function GET_MEASURE_CWM2 (p_aw in varchar2,
638 p_instance in varchar2,
639 p_type in varchar2,
640 p_template in varchar2,
641 p_approvee in varchar2,
642 p_currency in varchar2)
643 return varchar2 is
644 l_name varchar2(60);
645 begin
646 if (instr (p_aw, 'ZPBDATA') > 0) then
647 l_name := zpb_aw.get_aw_short_name(p_aw);
648 else
649 l_name := 'PERSONAL_';
650 end if;
651 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
652 if (p_template is not null) then
653 l_name := l_name||'T'||p_template;
654 end if;
655 if (p_approvee is not null) then
656 l_name := l_name||'A'||p_approvee;
657 end if;
658
659 --add translated/entered currency suffix
660 if(instr (p_type, 'ENTERED') > 0) then
661 l_name := l_name || '_ENT';
662 end if;
663
664 if(instr (p_type, 'TRANSLATED') > 0) then
665 l_name := l_name || '_T' || p_currency;
666 end if;
667
668 return l_name||'_DF';
669
670 end GET_MEASURE_CWM2;
671
672 -------------------------------------------------------------------------------
673 -- GET_MEASURE_FORMAT_CWM2 - Gets the measure format cwm2 name
674 --
675 -- IN: p_aw - The AW
676 -- p_instance - The Instance ID
677 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
678 -- or 'APPROVAL')
679 -- OUT: The format measure cwm2 name
680 -------------------------------------------------------------------------------
681 function GET_MEASURE_FORMAT_CWM2 (p_aw in varchar2,
682 p_instance in varchar2,
683 p_type in varchar2,
684 p_template in varchar2,
685 p_approvee in varchar2,
686 p_currency in varchar2)
687 return varchar2 is
688 l_name varchar2(60);
689 begin
690 if (instr (p_aw, 'ZPBDATA') > 0) then
691 l_name := zpb_aw.get_aw_short_name(p_aw);
692 else
693 l_name := 'PERSONAL_';
694 end if;
695 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
696 if (p_template is not null) then
697 l_name := l_name||'T'||p_template;
698 end if;
699 if (p_approvee is not null) then
700 l_name := l_name||'A'||p_approvee;
701 end if;
702
703 --add translated/entered currency suffix
704 if(instr (p_type, 'ENTERED') > 0) then
705 l_name := l_name || '_ENT';
706 end if;
707
708 if(instr (p_type, 'TRANSLATED') > 0) then
709 l_name := l_name || '_T' || p_currency;
710 end if;
711
712 return l_name||'_FT';
713 end GET_MEASURE_FORMAT_CWM2;
714
715 -------------------------------------------------------------------------------
716 -- GET_MEASURE_INPUT_LVL_CWM2 - Gets the measure input level cwm2 name
717 --
718 -- IN: p_aw - The AW
719 -- p_instance - The Instance ID
720 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
721 -- or 'APPROVAL')
722 -- OUT: The input level measure cwm2 name
723 -------------------------------------------------------------------------------
724 function GET_MEASURE_INPUT_LVL_CWM2 (p_aw in varchar2,
725 p_instance in varchar2,
726 p_type in varchar2,
727 p_template in varchar2,
728 p_approvee in varchar2)
729 return varchar2 is
730 l_name varchar2(60);
731 begin
732 if (instr (p_aw, 'ZPBDATA') > 0) then
733 l_name := zpb_aw.get_aw_short_name(p_aw);
734 else
735 l_name := 'PERSONAL_';
736 end if;
737 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
738 return l_name||'_IL';
739 end GET_MEASURE_INPUT_LVL_CWM2;
740
741 -------------------------------------------------------------------------------
742 -- GET_MEASURE_TARGET_CWM2 - Gets the measure target cwm2 name
743 --
744 -- IN: p_aw - The AW
745 -- p_instance - The Instance ID
746 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
747 -- or 'APPROVAL')
748 -- OUT: The target measure cwm2 name
749 -------------------------------------------------------------------------------
750 function GET_MEASURE_TARGET_CWM2 (p_aw in varchar2,
751 p_instance in varchar2,
752 p_type in varchar2,
753 p_template in varchar2,
754 p_approvee in varchar2)
755 return varchar2 is
756 l_name varchar2(60);
757 begin
758 if (instr (p_aw, 'ZPBDATA') > 0) then
759 l_name := zpb_aw.get_aw_short_name(p_aw);
760 else
761 l_name := 'PERSONAL_';
762 end if;
763 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
764 if (p_template is not null) then
765 l_name := l_name||'T'||p_template;
766 end if;
767 if (p_approvee is not null) then
768 l_name := l_name||'A'||p_approvee;
769 end if;
770 return l_name||'_TG';
771 end GET_MEASURE_TARGET_CWM2;
772
773 -------------------------------------------------------------------------------
774 -- GET_MEASURE_TARG_TYPE_CWM2 - Gets the measure target type cwm2 name
775 --
776 -- IN: p_aw - The AW
777 -- p_instance - The Instance ID
778 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
779 -- or 'APPROVAL')
780 -- OUT: The target type measure cwm2 name
781 -------------------------------------------------------------------------------
782 function GET_MEASURE_TARG_TYPE_CWM2 (p_aw in varchar2,
783 p_instance in varchar2,
784 p_type in varchar2,
785 p_template in varchar2,
786 p_approvee in varchar2)
787 return varchar2 is
788 l_name varchar2(60);
789 begin
790 if (instr (p_aw, 'ZPBDATA') > 0) then
791 l_name := zpb_aw.get_aw_short_name(p_aw);
792 else
793 l_name := 'PERSONAL_';
794 end if;
795 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
796 if (p_template is not null) then
797 l_name := l_name||'T'||p_template;
798 end if;
799 if (p_approvee is not null) then
800 l_name := l_name||'A'||p_approvee;
801 end if;
802 return l_name||'_TT';
803 end GET_MEASURE_TARG_TYPE_CWM2;
804
805 -------------------------------------------------------------------------------
806 -- GET_MEASURE_TYPE_ABBREV - Gets the measuure type shortname
807 --
808 -- IN: p_type - The measure type
809 -- OUT: The measure type shortname
810 -------------------------------------------------------------------------------
811 function GET_MEASURE_TYPE_ABBREV (p_type in varchar2)
812 return varchar2 is
813 begin
814 if (instr (p_type, 'SHARED_VIEW') > 0) then
815 return 'SHV';
816 else
817 return substr(p_type, 1, 3);
818 end if;
819 end GET_MEASURE_TYPE_ABBREV;
820
821 -------------------------------------------------------------------------------
822 -- GET_MEASURE_WRITE_SEC_CWM2 - Gets the measure write security cwm2 name
823 --
824 -- IN: p_aw - The AW
825 -- p_instance - The Instance ID
826 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
827 -- or 'APPROVAL')
828 -- OUT: The write security measure cwm2 name
829 -------------------------------------------------------------------------------
830 function GET_MEASURE_WRITE_SEC_CWM2 (p_aw in varchar2,
831 p_instance in varchar2,
832 p_type in varchar2,
833 p_template in varchar2,
834 p_approvee in varchar2)
835 return varchar2 is
836 l_name varchar2(60);
837 begin
838 if (instr (p_aw, 'ZPBDATA') > 0) then
839 l_name := zpb_aw.get_aw_short_name(p_aw);
840 else
841 l_name := 'PERSONAL_';
842 end if;
843 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
844 return l_name||'_WS';
845 end GET_MEASURE_WRITE_SEC_CWM2;
846
847 -------------------------------------------------------------------------------
848 -- GET_MEASURE_CUR_WRITE_SEC_CWM2 - Gets the measure currency write security cwm2 name
849 --
850 -- IN: p_aw - The AW
851 -- p_instance - The Instance ID
852 -- p_type - The measure type, one of ('CWM', 'SHARED', 'PERSONAL',
853 -- or 'APPROVAL')
854 -- OUT: The write security measure cwm2 name
855 -------------------------------------------------------------------------------
856 function GET_MEASURE_CUR_WRITE_SEC_CWM2 (p_aw in varchar2,
857 p_instance in varchar2,
858 p_type in varchar2,
859 p_template in varchar2,
860 p_approvee in varchar2)
861 return varchar2 is
862 l_name varchar2(60);
863 begin
864 if (instr (p_aw, 'ZPBDATA') > 0) then
865 l_name := zpb_aw.get_aw_short_name(p_aw);
866 else
867 l_name := 'PERSONAL_';
868 end if;
869 l_name := l_name||get_measure_type_abbrev(p_type)||p_instance;
870 return l_name||'_WSE';
871 end GET_MEASURE_CUR_WRITE_SEC_CWM2;
872
873 -------------------------------------------------------------------------------
874 -- GET_OWNERMAP_COLUMN - Gets the ownermap column
875 --
876 -- IN: p_aw - The AW
877 --
878 -- OUT: The ownermap column
879 -------------------------------------------------------------------------------
880 function GET_OWNERMAP_COLUMN
881 return varchar2 is
882 begin
883 return 'OWNERMAP';
884 end GET_OWNERMAP_COLUMN;
885
886 -------------------------------------------------------------------------------
887 -- GET_OWNERMAP_CWM2_CUBE - Gets the ownermap cube
888 --
889 -- IN: p_aw - The AW
890 --
891 -- OUT: The ownermap cwm2 cube name
892 -------------------------------------------------------------------------------
893 function GET_OWNERMAP_CWM2_CUBE (p_aw in varchar2)
894 return varchar2 is
895 begin
896 return zpb_aw.get_aw_short_name(p_aw)||'_OWNERMAP_CB';
897 end GET_OWNERMAP_CWM2_CUBE;
898
899 -------------------------------------------------------------------------------
900 -- GET_OWNERMAP_CWM2_NAME - Gets the ownermap measure
901 --
902 -- IN: p_aw - The AW
903 --
904 -- OUT: The ownermap cwm2 measure name
905 -------------------------------------------------------------------------------
906 function GET_OWNERMAP_CWM2_NAME (p_aw in varchar2)
907 return varchar2 is
908 begin
909 return zpb_aw.get_aw_short_name(p_aw)||'_MEAS_OWNERMAP';
910 end GET_OWNERMAP_CWM2_NAME;
911
912 -------------------------------------------------------------------
913 -- GET_OWNERMAP_VIEW - Gets the ownermap view
914 --
915 -- IN: p_aw - The AW
916 --
917 -- OUT: The ownermap view name
918 -------------------------------------------------------------------------------
919 function GET_OWNERMAP_VIEW (p_aw in varchar2)
920 return varchar2 is
921 begin
922 return zpb_aw.get_aw_short_name(p_aw)||'_OWNERMAP_V';
923 end GET_OWNERMAP_VIEW;
924
925 -------------------------------------------------------------------------------
926 -- GET_SCOPE_STATUS_VIEW - Gets the scope view name
927 --
928 -- IN: p_aw - The AW
929 --
930 -- OUT: The scope status view name
931 -------------------------------------------------------------------------------
932 function GET_SCOPE_STATUS_VIEW (p_aw in varchar2)
933 return varchar2 is
934 begin
935 return zpb_aw.get_aw_short_name(p_aw)||'_SECSCPSTAT_V';
936 end GET_SCOPE_STATUS_VIEW;
937
938 ---------------------------------------------------------------------------
939 -- GET_SCOPING_VIEW - Gets the metadata scoping view
940 --
941 -- IN: p_aw - The AW
942 --
943 -- OUT: The metadata scoping view name
944 -------------------------------------------------------------------------------
945 function GET_SCOPING_VIEW (p_aw in varchar2)
946 return varchar2 is
947 begin
948 return zpb_aw.get_aw_short_name(p_aw)||'_SCOPING_V';
949 end GET_SCOPING_VIEW;
950
951 -------------------------------------------------------------------------------
952 -- GET_SECURITY_CWM2_CUBE - Gets the security (ownermap) cube
953 --
954 -- IN: p_aw - The AW
955 --
956 -- OUT: The security cwm2 cube name
957 -------------------------------------------------------------------------------
958 function GET_SECURITY_CWM2_CUBE (p_aw in varchar2)
959 return varchar2 is
960 begin
961 return zpb_aw.get_aw_short_name(p_aw)||'_SECURITY_CB';
962 end GET_SECURITY_CWM2_CUBE;
963
964 -------------------------------------------------------------------------------
965 -- GET_SECURITY_VIEW - Gets the security (ownermap) view
966 --
967 -- IN: p_aw - The AW
968 --
969 -- OUT: The security cwm2 cube name
970 -------------------------------------------------------------------------------
971 function GET_SECURITY_VIEW (p_aw in varchar2)
972 return varchar2 is
973 begin
974 return zpb_aw.get_aw_short_name(p_aw)||'_SECURITY_V';
975 end GET_SECURITY_VIEW;
976
977 ---------------------------------------------------------------------------
978 -- GET_SOLVE_LEVEL_TABLE - Gets the solve input/output level table name
979 --
980 -- IN: p_aw - The AW
981 --
982 -- OUT: The solve input/output level table name
983 -------------------------------------------------------------------------------
984 function GET_SOLVE_LEVEL_TABLE (p_aw in varchar2)
985 return varchar2 is
986 begin
987 return zpb_aw.get_aw_short_name(p_aw)||'_SOLVE_LEVEL_T';
988 end GET_SOLVE_LEVEL_TABLE;
989
990 ---------------------------------------------------------------------------
991 -- GET_TO_CURRENCY_VIEW - Gets the to currency view name
992 --
993 -- IN: p_aw - The AW
994 --
995 -- OUT: The to currency view name
996 -------------------------------------------------------------------------------
997 function GET_TO_CURRENCY_VIEW (p_aw in varchar2)
998 return varchar2 is
999 begin
1000 return zpb_aw.get_aw_short_name(p_aw)||'_TO_CURRENCY_V';
1001 end GET_TO_CURRENCY_VIEW;
1002
1003 -------------------------------------------------------------------------------
1004 -- GET_VIEW_OBJECT - Gets the measure view object name
1005 --
1006 -- IN: p_view - The Measure or Dimension view
1007 -- OUT: The view's object name
1008 -------------------------------------------------------------------------------
1009 function GET_VIEW_OBJECT(p_view in varchar2)
1010 return varchar2 is
1011 begin
1012 if (substr (p_view, length(p_view), 1) = 'V') then
1013 return substr(p_view, 1, length(p_view)-1)||'O';
1014 else
1015 return p_view||'_O';
1016 end if;
1017 end GET_VIEW_OBJECT;
1018
1019 -------------------------------------------------------------------------------
1020 -- GET_VIEW_TABLE - Gets the measure view table name
1021 --
1022 -- IN: p_view - The Measure or Dimension view
1023 -- OUT: The view's table name
1024 -------------------------------------------------------------------------------
1025 function GET_VIEW_TABLE(p_view in varchar2)
1026 return varchar2 is
1027 begin
1028 if (substr (p_view, length(p_view), 1) = 'V') then
1029 return substr(p_view, 1, length(p_view)-1)||'T';
1030 else
1031 return p_view||'_T';
1032 end if;
1033 end GET_VIEW_TABLE;
1034
1035 -------------------------------------------------------------------------------
1036 -- GET_DIM_AGGTIME_COLUMN - Gets the column name for the agg type by time attribute
1037 -- for a dimension
1038 --
1039 -- IN: p_dimID - The Dimension ID in the DimDim
1040 -- OUT: The column name for the agg type by time attribute
1041 -------------------------------------------------------------------------------
1042 function GET_DIM_AGGTIME_COLUMN (p_dimID in varchar2)
1043 return varchar2 is
1044 begin
1045 return p_dimID||'_AGGBYTIME';
1046 end;
1047
1048 -------------------------------------------------------------------------------
1049 -- GET_DIM_AGGOTHER_COLUMN - Gets the column name for the agg type, by dimensions
1050 -- other than time, attribute for a dimension
1051 --
1052 -- IN: p_dimID - The Dimension ID in the DimDim
1053 -- OUT: The column name for the agg type by other dims attribute
1054 -------------------------------------------------------------------------------
1055 function GET_DIM_AGGOTHER_COLUMN (p_dimID in varchar2)
1056 return varchar2 is
1057 begin
1058 return p_dimID||'_AGGBYOTHER';
1059 end;
1060
1061 -------------------------------------------------------------------------------
1062 -- UPGRADE_REPOS_NAME - Upgrades the repository name for a business area
1063 -- to the new name
1064 --
1065 -- IN: p_business_area - The Business Area ID
1066 -- p_old_name - The old name
1067 -- p_new_name - The new name
1068 -------------------------------------------------------------------------------
1069 procedure UPGRADE_REPOS_NAME(p_business_area IN VARCHAR2,
1070 p_old_name IN VARCHAR2,
1071 p_new_name IN VARCHAR2)
1072 is
1073 l_xml BISM_OBJECTS.XML%type;
1074 i NUMBER;
1075 l_count1 NUMBER;
1076 l_count2 NUMBER;
1077 cursor c_objects is
1078 select OBJECT_ID, XML
1079 from BISM_OBJECTS
1080 where XML like '%ZPBDATA'||p_business_area||'%'||p_old_name||'%';
1081 begin
1082 l_count1 := length(p_old_name);
1083 l_count2 := length(p_new_name);
1084 for each in c_objects loop
1085 l_xml := each.XML;
1086 i := 1;
1087 loop
1088 i := instr(l_xml, p_old_name, i);
1089 exit when i = 0;
1090
1091 l_xml := substr(l_xml, 1, i-1)||p_new_name||substr(l_xml, i+l_count1);
1092 i := i+l_count2;
1093
1094 update BISM_OBJECTS
1095 set XML = l_xml
1096 where OBJECT_ID = each.OBJECT_ID;
1097 end loop;
1098 end loop;
1099 end UPGRADE_REPOS_NAME;
1100
1101 end ZPB_METADATA_NAMES;
[Home] [Help]