eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.EDW_BSC_BRIDGE

Source


1 Package Body EDW_BSC_BRIDGE AS
2 /* $Header: EDWBSCB.pls 115.14 2004/02/13 05:01:13 smulye noship $ */
3 e_DynamicSqlStmtErr  EXCEPTION;
4 TYPE t_FactMeasure IS RECORD (
5   measure_name EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
6   measure_id   EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE
7 );
8 
9 TYPE FactDimColTable IS TABLE of
10   EDW_FACT_DIM_RELATIONS_MD_V.FACT_FK_COL_NAME%TYPE
11   INDEX BY BINARY_INTEGER;
12 
13 TYPE FactMeasureTable IS TABLE of
14   t_FactMeasure
15   INDEX BY BINARY_INTEGER;
16 
17 TYPE t_Fact IS RECORD (
18   fact_id     EDW_FACTS_MD_V.FACT_ID%TYPE,
19   fact_name   EDW_FACTS_MD_V.FACT_NAME%TYPE
20 );
21 
22 TYPE FactTable IS TABLE of
23   t_Fact
24   INDEX BY BINARY_INTEGER;
25 
26 
27 NOTVALID       constant VARCHAR2(30) := 'NOT_VALID';
28 VALID          constant VARCHAR2(30) := 'VALID';
29 NOSYNONYM      constant VARCHAR2(30) := 'VALID_WITHOUT_SYNONYM';
30 STARTED        constant VARCHAR2(30) := 'Started';
31 FAILED         constant VARCHAR2(30) := 'Failed';
32 PROCESSING     constant VARCHAR2(30) := 'Processing';
33 PROCESSED      constant VARCHAR2(30) := 'Processed';
34 DONE           constant VARCHAR2(30) := 'Done';
35 ABORTED        constant VARCHAR2(30) := 'Aborted';
36 SQLERR         constant VARCHAR2(30) := 'Sql Error';
37 EXECUTING      constant VARCHAR2(30) := 'Executing';
38 EXECUTED       constant VARCHAR2(30) := 'Executed';
39 SCRIPTING      constant VARCHAR2(30) := 'Scripting';
40 SCRIPTED       constant VARCHAR2(30) := 'Scripted';
41 DEBUG          constant VARCHAR2(30) := 'Debug';
42 -- Cache the EDW_TIME_M Dimsension ID.
43 v_time_dimension_id EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE  := NULL;
44 v_fact_id           EDW_FACTS_MD_V.FACT_ID%TYPE        := NULL;
45 v_fact_name         EDW_FACTS_MD_V.FACT_NAME%TYPE      := NULL;
46 
47 CURSOR dim_id(
48   p_object_name EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE
49 ) IS
50 SELECT DIM_ID
51 FROM EDW_DIMENSIONS_MD_V
52 WHERE DIM_NAME = p_object_name;
53 
54 CURSOR c_fact IS
55 SELECT
56      FACT_ID
57    , FACT_NAME
58 FROM EDW_FACTS_MD_V;
59 
60 CURSOR c_factid (
61   p_fact_name        EDW_FACTS_MD_V.FACT_NAME%TYPE
62 )IS
63 SELECT
64     FACT_ID
65 FROM EDW_FACTS_MD_V
66 WHERE FACT_NAME = p_fact_name;
67 
68 CURSOR c_time_hier_id(
69   p_hier_prefix       EDW_HIERARCHIES_MD_V.HIER_PREFIX%TYPE
70 ) IS
71 SELECT HIER_ID
72 FROM EDW_HIERARCHIES_MD_V
73 WHERE
74  DIM_ID = v_time_dimension_id
75 AND HIER_PREFIX = p_hier_prefix;
76 
77 --excuted once per fact, no need for tuning
78 CURSOR c_facttimedimcol(p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE) IS
79 SELECT
80   FACT_FK_COL_NAME
81 FROM EDW_FACT_DIM_RELATIONS_MD_V
82 WHERE
83     DIM_ID = v_time_dimension_id
84     -- use the cached id for better performance,
85     -- it is instanciated at the intialization.
86 AND FACT_NAME = p_fact_name;
87 
88 
89 --excuted once per fact, no need for tuning
90 CURSOR c_factnontimedimcol(p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE) IS
91 SELECT
92   FACT_FK_COL_NAME
93 FROM EDW_FACT_DIM_RELATIONS_MD_V
94 WHERE
95     DIM_ID <> v_time_dimension_id
96     -- use the cached id for better performance,
97     -- it is instanciated at the intialization.
98 AND FACT_NAME = p_fact_name;
99 
100 --excuted once per fact, no need for tuning
101 CURSOR c_factmeasure(p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE) IS
102 SELECT
103   ATTRIBUTE_ID ,
104   ATTRIBUTE_NAME
105 FROM EDW_FACT_ATTRIBUTES_MD_V
106 WHERE FACT_NAME = p_fact_name
107 AND ATTRIBUTE_TYPE = 'MEASURE';
108 
109 
110 --excuted once per fact, no need for tuning
111 CURSOR c_factmeasure_id(
112   p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
113   p_measure_name EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE
114 ) IS
115 SELECT
116   ATTRIBUTE_ID
117 FROM EDW_FACT_ATTRIBUTES_MD_V
118 WHERE
119     FACT_NAME = p_fact_name
120 AND ATTRIBUTE_NAME = p_measure_name
121 AND ATTRIBUTE_TYPE = 'MEASURE';
122 
123 
124 CURSOR c_factvalidity_t(
125   p_fact_name     EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
126 )IS
127 SELECT
128   object_name
129 FROM
130   user_objects
131 WHERE
132     object_name = UPPER(p_fact_name)
133 AND object_type = 'TABLE';
134 
135 CURSOR c_factvalidity_s(
136   p_fact_name     EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
137 )IS
138 SELECT
139   object_name
140 FROM
141   user_objects
142 WHERE
143     object_name = UPPER(p_fact_name)
144 AND object_type = 'SYNONYM';
145 
146 /*
147 CHANGES FOR BUG 3431744 BY AMITGUPT
148 */
149 CURSOR c_att_validity (
150   p_tbl_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE ,
151   p_col_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE) IS
152 SELECT
153   COUNT(TAB.COLUMN_NAME)
154 FROM
155   ALL_TAB_COLUMNS TAB, USER_SYNONYMS SYN
156 WHERE
157     TAB.TABLE_NAME = UPPER(p_tbl_name) AND
158     SYN.TABLE_NAME = TAB.TABLE_NAME  AND
159     SYN.TABLE_OWNER = TAB.OWNER AND
160     TAB.COLUMN_NAME = UPPER(p_col_name);
161 
162 
163 v_445_id            EDW_HIERARCHIES_MD_V.HIER_ID%TYPE := NULL;
164 v_pa_id             EDW_HIERARCHIES_MD_V.HIER_ID%TYPE := NULL;
165 v_gl_id             EDW_HIERARCHIES_MD_V.HIER_ID%TYPE := NULL;
166 v_enterprise_id     EDW_HIERARCHIES_MD_V.HIER_ID%TYPE := NULL;
167 v_gregerion_id      EDW_HIERARCHIES_MD_V.HIER_ID%TYPE := NULL;
168 
169 
170 
171 FUNCTION GET_DIM_ID(
172   p_object_name EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE
173 ) RETURN EDW_DIMENSIONS_MD_V.DIM_ID%TYPE;
174 
175 FUNCTION GET_TIME_ID RETURN EDW_DIMENSIONS_MD_V.DIM_ID%TYPE;
176 
177 PROCEDURE INITIALIZE;
178 
179 PROCEDURE FINALIZE;
180 
181 PROCEDURE LOG(
182     p_code              EDW_CALSUM4_BSC_LOG.CODE%TYPE
183   , p_text              EDW_CALSUM4_BSC_LOG.TEXT%TYPE
184   , p_info              EDW_CALSUM4_BSC_LOG.INFO%TYPE
185 );
186 
187 FUNCTION GET_FACT_ID(
188    p_fact_name IN EDW_FACTS_MD_V.FACT_NAME%TYPE
189 ) RETURN EDW_FACTS_MD_V.FACT_ID%TYPE;
190 
191 FUNCTION CHECK_VALIDILITY(
192   p_tbl_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE ,
193   p_col_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
194 RETURN BOOLEAN;
195 
196 FUNCTION CHECK_FACT_VALIDILITY(
197   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
198 ) RETURN VARCHAR2;
199 
200 FUNCTION GET_FACT_MEASURE(
201   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
202 )RETURN FactMeasureTable;
203 
204 FUNCTION GET_FACT_TIME_DIM_COL(
205   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
206 )RETURN FactDimColTable;
207 
208 FUNCTION GET_FACT_NONTIME_DIM_COL(
209   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
210 )RETURN FactDimColTable;
211 
212 FUNCTION GET_SQL_STMT(
213   p_fact_id                 IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
214   p_fact_name               IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
215   p_fact_time_dim_table     IN FactDimColTable,
216   p_fact_nontime_dim_table  IN FactDimColTable,
217   p_fact_measure            IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
218   p_fact_measure_id         IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE,
219   p_hier                    IN VARCHAR
220 )RETURN VARCHAR2;
221 
222 
223 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
224   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
225 );
226 
227 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
228   p_fact_name    IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
229   p_measure_name IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE
230 );
231 
232 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
233   p_fact_id   IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
234   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
235 );
236 
237 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
238   p_fact_id                 IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
239   p_fact_name               IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
240   p_fact_time_dim_table     IN FactDimColTable,
241   p_fact_nontime_dim_table  IN FactDimColTable,
242   p_fact_measure            IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
243   p_fact_measure_id         IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE
244 );
245 
246 
247 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
248   p_fact_id            IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
249   p_fact_name          IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
250   p_fact_measure_table IN FactMeasureTable
251 );
252 
253 FUNCTION GET_FACT_MEASURE_ID(
254   p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
255   p_measure_name EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE
256 )RETURN NUMBER;
257 
258 FUNCTION GET_HIER_ID(
259   p_hier_prefix          IN EDW_HIERARCHIES_MD_V.HIER_PREFIX%TYPE
260 ) RETURN NUMBER;
261 
262 FUNCTION GET_TIME_HIER_ID(
263   p_hier_prefix          IN EDW_HIERARCHIES_MD_V.HIER_PREFIX%TYPE
264 ) RETURN NUMBER;
265 
266 --Kernal Entries
267 -- #1.1
268 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
269    errbuf   OUT NOCOPY VARCHAR2,
270    retcode  OUT NOCOPY NUMBER
271 ) IS
272   l_fact_id     EDW_FACTS_MD_V.FACT_ID%TYPE;
273   l_fact        EDW_FACTS_MD_V.FACT_NAME%TYPE;
274   l_fact_tbl    FactTable;
275   l_fact_count  BINARY_INTEGER;
276   l_fact_index  BINARY_INTEGER;
277 BEGIN
278   INITIALIZE;
279   --cache the fact information and close the cursor!
280   l_fact_count := 0;
281   OPEN c_fact;
282   LOOP
283     FETCH c_fact INTO l_fact_id, l_fact;
284     EXIT WHEN c_fact%NOTFOUND;
285     l_fact_tbl(l_fact_count).fact_id := l_fact_id;
286     l_fact_tbl(l_fact_count).fact_name := l_fact;
287     l_fact_count := l_fact_count + 1;
288   END LOOP;
289   CLOSE c_fact;
290 
291   --start processing fact by fact
292   l_fact_index := l_fact_tbl.FIRST;
293   LOOP
294     POPULATE_EDW_CALSUM4_BSC(
295       l_fact_tbl(l_fact_index).fact_id,
296       l_fact_tbl(l_fact_index).fact_name);
297     EXIT WHEN l_fact_index = l_fact_tbl.LAST;
298     l_fact_index := l_fact_tbl.NEXT(l_fact_index);
299   END LOOP;
300 
301   FINALIZE;
302 
303   EXCEPTION
304     WHEN OTHERS THEN
305       CLOSE c_fact;
306       retcode := SQLCODE;
307       errbuf := SQLERRM;
308       LOG(retcode, SQLERR, errbuf);
309       LOG(NULL, ABORTED, 'Process aborted!!');
310 END POPULATE_EDW_CALSUM4_BSC;
311 
312 -- #1.2
313 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
314   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
315   errbuf   OUT NOCOPY VARCHAR2,
316   retcode  OUT NOCOPY NUMBER
317 ) IS
318 BEGIN
319   INITIALIZE;
320   POPULATE_EDW_CALSUM4_BSC(p_fact_name);
321   FINALIZE;
322   EXCEPTION
323     WHEN OTHERS THEN
324       retcode := SQLCODE;
325       errbuf := SQLERRM;
326       LOG(retcode, SQLERR, errbuf);
327       LOG(NULL, ABORTED, 'Process aborted!!');
328 END POPULATE_EDW_CALSUM4_BSC;
329 
330 -- #1.3
331 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
332   p_fact_name    IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
333   p_measure_name IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
334   errbuf   OUT NOCOPY VARCHAR2,
335   retcode  OUT NOCOPY NUMBER
336 ) IS
337 BEGIN
338   INITIALIZE;
339   POPULATE_EDW_CALSUM4_BSC(p_fact_name, p_measure_name);
340   FINALIZE;
341   EXCEPTION
342     WHEN OTHERS THEN
343       CLOSE c_fact;
344       retcode := SQLCODE;
345       errbuf := SQLERRM;
346       LOG(retcode, SQLERR, errbuf);
347       LOG(NULL, ABORTED, 'Process aborted!!');
348 END POPULATE_EDW_CALSUM4_BSC;
349 --Kernal Entries
350 
351 -- Second Level Procedures
352 -- #2.1 : Called by Entry #1.1
353 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
354   p_fact_id   IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
355   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
356 ) IS
357   l_fact_measure_table       FactMeasureTable;
358 
359 BEGIN
360   IF(CHECK_FACT_VALIDILITY(p_fact_name) <> VALID) THEN
361     RETURN ;
362   ELSE
363     l_fact_measure_table:= GET_FACT_MEASURE(p_fact_name);
364     POPULATE_EDW_CALSUM4_BSC(p_fact_id, p_fact_name, l_fact_measure_table);
365   END IF;
366 END POPULATE_EDW_CALSUM4_BSC;
367 
368 -- #2.2 : Called by Entry #1.2
369 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
370   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
371 ) IS
372   l_fact_measure_table       FactMeasureTable;
373   l_fact_id                  EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE;
374 BEGIN
375   IF(CHECK_FACT_VALIDILITY(p_fact_name) <> VALID) THEN
376     RETURN ;
377   ELSE
378     l_fact_id := GET_FACT_ID(p_fact_name);
379     l_fact_measure_table:= GET_FACT_MEASURE(p_fact_name);
380     POPULATE_EDW_CALSUM4_BSC(l_fact_id, p_fact_name, l_fact_measure_table);
381   END IF;
382 END POPULATE_EDW_CALSUM4_BSC;
383 
384 -- #2.3 : Called by Entry #1.3
385 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
386   p_fact_name    IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
387   p_measure_name IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE
388 ) IS
389   l_fact_id                  EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE;
390   l_measure_id               EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE;
391   l_fact_measure_table       FactMeasureTable;
392 BEGIN
393   IF( CHECK_FACT_VALIDILITY(p_fact_name) <> VALID) THEN
394     RETURN;
395   ELSIF( CHECK_VALIDILITY( p_fact_name, p_measure_name)) THEN
396     l_fact_id := GET_FACT_ID(p_fact_name);
397     l_measure_id := GET_FACT_MEASURE_ID(p_fact_name, p_measure_name);
398 
399     -- create a one item measure-table
400     l_fact_measure_table(0).measure_name := p_measure_name;
401     l_fact_measure_table(0).measure_id := l_measure_id;
402     POPULATE_EDW_CALSUM4_BSC(l_fact_id, p_fact_name, l_fact_measure_table);
403   ELSE
404     LOG(NULL, FAILED , '  Corrupted metadata definition, ' || p_measure_name || ', in ' || p_fact_name);
405     RETURN ;
406   END IF;
407 END POPULATE_EDW_CALSUM4_BSC;
408 
409 -- Third Level Procedures
410 -- #3.1 : called by #2.1, #2.2 & #2.3
411 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
412   p_fact_id             IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
413   p_fact_name           IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
414   p_fact_measure_table  IN FactMeasureTable
415 ) IS
416   l_fact_measure         EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE;
417   l_measure_index        BINARY_INTEGER;
418   l_fact_time_dim_table  FactDimColTable;
419   l_fact_nontime_dim_table  FactDimColTable;
420 BEGIN
421   l_fact_time_dim_table := GET_FACT_TIME_DIM_COL(p_fact_name);
422   l_fact_nontime_dim_table := GET_FACT_NONTIME_DIM_COL(p_fact_name);
423   -- Don't do anything if no time dimension or no measures
427     l_measure_index := p_fact_measure_table.FIRST;
424   -- Otherwise, the dynamic sql statement will be errered out
425   IF (    l_fact_time_dim_table.COUNT > 0
426       AND p_fact_measure_table.COUNT > 0)  THEN
428     LOOP
429       POPULATE_EDW_CALSUM4_BSC(
430         p_fact_id,
431         p_fact_name,
432         l_fact_time_dim_table,
433         l_fact_nontime_dim_table,
434         p_fact_measure_table(l_measure_index).measure_name,
435         p_fact_measure_table(l_measure_index).measure_id);
436       COMMIT;
437       EXIT WHEN l_measure_index = p_fact_measure_table.LAST;
438       l_measure_index := p_fact_measure_table.NEXT(l_measure_index);
439     END LOOP;
440   ELSE
441     LOG(NULL, FAILED, '  No measure or time dimension defined for the fact '|| p_fact_name);
442   END IF;
443 END POPULATE_EDW_CALSUM4_BSC;
444 
445 -- The Fourth Level Procedure
446 -- #4.1 : Called by #3.1
447 PROCEDURE POPULATE_EDW_CALSUM4_BSC(
448   p_fact_id                 IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
449   p_fact_name               IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
450   p_fact_time_dim_table     IN FactDimColTable,
451   p_fact_nontime_dim_table  IN FactDimColTable,
452   p_fact_measure            IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
453   p_fact_measure_id         IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE
454 ) IS
455    l_calsum_stmt     varchar2(30000) := NULL;
456    l_calsum          varchar2(100);
457    l_sumid           EDW_CALSUM4_BSC.CALSUM_ID%TYPE;
458    l_hier            varchar2(50);
459    l_hier_id         EDW_CALSUM4_BSC.HIERID%TYPE;
460    l_ErrorCode       EDW_CALSUM4_BSC_LOG.CODE%TYPE;
461    l_ErrorText       EDW_CALSUM4_BSC_LOG.TEXT%TYPE;
462    l_fact_dim_index  BINARY_INTEGER;
463    l_err_info        EDW_CALSUM4_BSC_LOG.INFO%TYPE;
464 BEGIN
465   LOG(NULL, PROCESSING, '->Fact: ' || p_fact_name || ', ' || 'Measure: ' || p_fact_measure);
466 
467   l_calsum_stmt := GET_SQL_STMT(p_fact_id, p_fact_name, p_fact_time_dim_table, p_fact_nontime_dim_table, p_fact_measure, p_fact_measure_id, '445');
468   LOG(NULL, EXECUTING, '445------------------------------------------>');
469   EXECUTE IMMEDIATE l_calsum_stmt;
470   LOG(NULL, EXECUTED, '445<------------------------------------------');
471 
472 
473   l_calsum_stmt := GET_SQL_STMT(p_fact_id, p_fact_name, p_fact_time_dim_table, p_fact_nontime_dim_table, p_fact_measure, p_fact_measure_id, 'PA');
474   LOG(NULL, EXECUTING, 'PA------------------------------------------>');
475   EXECUTE IMMEDIATE l_calsum_stmt;
476   LOG(NULL, EXECUTED, 'PA<------------------------------------------');
477 
478   l_calsum_stmt := GET_SQL_STMT(p_fact_id, p_fact_name, p_fact_time_dim_table, p_fact_nontime_dim_table, p_fact_measure, p_fact_measure_id, 'GL');
479   LOG(NULL, EXECUTING, 'GL------------------------------------------>');
480   EXECUTE IMMEDIATE l_calsum_stmt;
481   LOG(NULL, EXECUTED, 'GL<------------------------------------------');
482 
483   l_calsum_stmt := GET_SQL_STMT(p_fact_id, p_fact_name, p_fact_time_dim_table, p_fact_nontime_dim_table, p_fact_measure, p_fact_measure_id, 'GREGERION');
484   LOG(NULL, EXECUTING, 'GREGERION------------------------------------------>');
485   EXECUTE IMMEDIATE l_calsum_stmt;
486   LOG(NULL, EXECUTED, 'GREGERION<------------------------------------------');
487 
488   l_calsum_stmt := GET_SQL_STMT(p_fact_id, p_fact_name, p_fact_time_dim_table, p_fact_nontime_dim_table, p_fact_measure, p_fact_measure_id, 'ENTERPRISE');
489   LOG(NULL, EXECUTING, 'ENTERPRISE------------------------------------------>');
490   EXECUTE IMMEDIATE l_calsum_stmt;
491   LOG(NULL, EXECUTED, 'ENTERPRISE<------------------------------------------');
492 
493   LOG(NULL, PROCESSED, '<-Fact: ' || p_fact_name || ', ' || 'Measure: ' || p_fact_measure);
494   EXCEPTION
495     WHEN e_DynamicSqlStmtErr THEN
496       LOG(NULL, FAILED, '<-Fact: ' || p_fact_name || ', ' || 'Measure: ' || p_fact_measure);
497     WHEN OTHERS THEN
498       l_ErrorCode := SQLCODE;
499       l_ErrorText := SQLERRM;
500       l_err_info  := p_fact_measure;
501       l_fact_dim_index := p_fact_time_dim_table.FIRST;
502       LOOP
503         l_err_info := l_err_info || ', '|| p_fact_time_dim_table(l_fact_dim_index);
504         EXIT WHEN l_fact_dim_index = p_fact_time_dim_table.LAST;
505         l_fact_dim_index := p_fact_time_dim_table.NEXT(l_fact_dim_index);
506       END LOOP;
507       l_err_info := l_err_info || ' might not be defined/valid in ' || p_fact_name;
508       LOG(l_ErrorCode, l_ErrorText, l_err_info);
509       LOG(NULL, FAILED, '<-Fact: ' || p_fact_name || ', ' || 'Measure: ' || p_fact_measure);
510 END POPULATE_EDW_CALSUM4_BSC;
511 
512 FUNCTION GET_FACT_MEASURE_ID(
513   p_fact_name EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
514   p_measure_name EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE
515 )RETURN NUMBER IS
516   l_measure_id               EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE;
517 BEGIN
518   OPEN c_factmeasure_id(p_fact_name, p_measure_name);
519   FETCH c_factmeasure_id INTO l_measure_id;
520   CLOSE c_factmeasure_id;
521   RETURN l_measure_id;
522 END GET_FACT_MEASURE_ID;
523 
524 FUNCTION GET_FACT_MEASURE(
525   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
526 )RETURN FactMeasureTable IS
527   l_measure_id               EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE;
528   l_measure                  EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE;
529   l_fact_measure_table       FactMeasureTable;
530   l_fact_measure_table_count BINARY_INTEGER;
531 BEGIN
532   l_fact_measure_table_count := 0;
536     EXIT WHEN c_factmeasure%NOTFOUND;
533   OPEN c_factmeasure(p_fact_name);
534   LOOP
535     FETCH c_factmeasure INTO l_measure_id, l_measure;
537     IF (CHECK_VALIDILITY( p_fact_name, l_measure)) THEN
538       l_fact_measure_table(l_fact_measure_table_count).measure_name := l_measure;
539       l_fact_measure_table(l_fact_measure_table_count).measure_id := l_measure_id;
540       l_fact_measure_table_count := l_fact_measure_table_count + 1;
541     ELSE
542       LOG(NULL, FAILED, '  Corrupted metadata definition, ' || l_measure || ', in ' || p_fact_name);
543     END IF;
544   END LOOP;
545   CLOSE c_factmeasure;
546   RETURN l_fact_measure_table;
547 END GET_FACT_MEASURE;
548 
549 FUNCTION GET_FACT_TIME_DIM_COL(
550   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
551 )RETURN FactDimColTable IS
552   l_fact_time_dim              EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE;
553   l_fact_time_dim_table        FactDimColTable;
554   l_fact_time_dim_table_count  BINARY_INTEGER;
555 BEGIN
556   l_fact_time_dim_table_count := 0;
557   OPEN c_facttimedimcol(p_fact_name);
558   LOOP
559     FETCH c_facttimedimcol INTO l_fact_time_dim;
560     EXIT WHEN c_facttimedimcol%NOTFOUND;
561     IF (CHECK_VALIDILITY( p_fact_name, l_fact_time_dim)) THEN
562       l_fact_time_dim_table(l_fact_time_dim_table_count) := l_fact_time_dim;
563       l_fact_time_dim_table_count := l_fact_time_dim_table_count + 1;
564     ELSE
565       LOG(NULL, FAILED, '  Corrupted metadata definition, ' || l_fact_time_dim || ', in ' || p_fact_name);
566     END IF;
567   END LOOP;
568   CLOSE c_facttimedimcol;
569   RETURN l_fact_time_dim_table;
570 END GET_FACT_TIME_DIM_COL;
571 
572 
573 FUNCTION GET_FACT_NONTIME_DIM_COL(
574   p_fact_name IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
575 )RETURN FactDimColTable IS
576   l_fact_nontime_dim              EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE;
577   l_fact_nontime_dim_table        FactDimColTable;
578   l_fact_nontime_dim_table_count  BINARY_INTEGER;
579 BEGIN
580   l_fact_nontime_dim_table_count := 0;
581   OPEN c_factnontimedimcol(p_fact_name);
582   LOOP
583     FETCH c_factnontimedimcol INTO l_fact_nontime_dim;
584     EXIT WHEN c_factnontimedimcol%NOTFOUND;
585     IF (CHECK_VALIDILITY( p_fact_name, l_fact_nontime_dim)) THEN
586       l_fact_nontime_dim_table(l_fact_nontime_dim_table_count) := l_fact_nontime_dim;
587       l_fact_nontime_dim_table_count := l_fact_nontime_dim_table_count + 1;
588     ELSE
589       LOG(NULL, FAILED, '  Corrupted metadata definition, ' || l_fact_nontime_dim || ', in ' || p_fact_name);
590     END IF;
591   END LOOP;
592   CLOSE c_factnontimedimcol;
593   RETURN l_fact_nontime_dim_table;
594 END GET_FACT_NONTIME_DIM_COL;
595 
596 /*
597 FUNCTION GET_USER_INFO
598 RETURN VARCHAR2 IS
599   l_usrname   USER_USERS.USERNAME%TYPE;
600   CURSOR c_userinfo IS
601   SELECT
602     USERNAME
603    FROM USER_USERS;
604 BEGIN
605   OPEN c_userinfo;
606   FETCH c_userinfo INTO l_usrname;
607   CLOSE c_userinfo;
608   RETURN l_usrname;
609 END GET_USER_INFO;
610 */
611 
612 FUNCTION CHECK_FACT_VALIDILITY(
613   p_fact_name          IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE
614 ) RETURN VARCHAR2 IS
615   l_object              USER_OBJECTS.OBJECT_NAME%TYPE := NULL;
616 BEGIN
617   OPEN c_factvalidity_t(p_fact_name);
618   FETCH c_factvalidity_t INTO l_object;
619   CLOSE c_factvalidity_t;
620 
621   IF (l_object IS NULL) THEN
622     LOG(NULL, FAILED, '  Table ' || p_fact_name || ' is not accessible or not defined');
623     RETURN NOTVALID;
624   ELSE
625     l_object := null;
626     OPEN c_factvalidity_s(p_fact_name);
627     FETCH c_factvalidity_s INTO l_object;
628     CLOSE c_factvalidity_s;
629     IF(l_object IS NOT NULL  )THEN
630       RETURN VALID;
631     ELSE
632       LOG(NULL, FAILED, '  No synonym defined for Table ' || p_fact_name);
633       RETURN NOSYNONYM;
634     END IF;
635   END IF;
636 END;
637 
638 PROCEDURE LOG(
639     p_code              EDW_CALSUM4_BSC_LOG.CODE%TYPE
640   , p_text              EDW_CALSUM4_BSC_LOG.TEXT%TYPE
641   , p_info              EDW_CALSUM4_BSC_LOG.INFO%TYPE
642 ) IS
643 BEGIN
644   INSERT INTO EDW_CALSUM4_BSC_LOG(CODE, TEXT, INFO, LAST_UPDATE_DATE, CREATION_DATE)
645     VALUES(p_code, p_text, p_info, SYSDATE, SYSDATE);
646   COMMIT;
647 END LOG;
648 
649 
650 FUNCTION GET_SQL_STMT(
651   p_fact_id                IN EDW_FACT_ATTRIBUTES_MD_V.FACT_ID%TYPE,
652   p_fact_name              IN EDW_FACT_ATTRIBUTES_MD_V.FACT_NAME%TYPE,
653   p_fact_time_dim_table    IN FactDimColTable,
654   p_fact_nontime_dim_table IN FactDimColTable,
655   p_fact_measure           IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_NAME%TYPE,
656   p_fact_measure_id        IN EDW_FACT_ATTRIBUTES_MD_V.ATTRIBUTE_ID%TYPE,
657   p_hier                   IN VARCHAR
658 ) RETURN VARCHAR2 IS
659   l_calsum_stmt_psudo         VARCHAR2(1000)  := NULL;
660   l_calsum_stmt               VARCHAR2(30000) := NULL;
661   l_calsum_stmt_tmp           VARCHAR2(4000)  := NULL;
662   l_calsum_fromwhere_clause   VARCHAR2(30000) := NULL;
663   l_ErrorCode       EDW_CALSUM4_BSC_LOG.CODE%TYPE;
664   l_ErrorText       EDW_CALSUM4_BSC_LOG.TEXT%TYPE;
665   l_err_info        EDW_CALSUM4_BSC_LOG.INFO%TYPE;
669 
666   l_fact_dim_index  BINARY_INTEGER;
667   l_start           BINARY_INTEGER;
668 BEGIN
670   LOG(NULL, SCRIPTING, '-->Statement generating for ' || p_hier );
671   l_calsum_fromwhere_clause :=
672   ' FROM '|| p_fact_name  || ' FACT, '||
673   '  EDW_TIME_M DIM '||
674   'WHERE '||
675   'FACT.'||p_fact_measure || ' IS NOT NULL ' ||
676   'AND DIM.CDAY_CAL_DAY_PK_KEY != 0 ' ||
677   'AND ( DIM.CDAY_CAL_DAY_PK_KEY = FACT.';
678 
679   l_fact_dim_index := p_fact_time_dim_table.FIRST;
680   l_start := p_fact_time_dim_table.FIRST;
681   LOOP
682     IF(l_fact_dim_index = l_start) THEN
683       l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || p_fact_time_dim_table(l_fact_dim_index);
684     ELSE
685       l_calsum_fromwhere_clause := l_calsum_fromwhere_clause ||
686       ' OR DIM.CDAY_CAL_DAY_PK_KEY = FACT.' || p_fact_time_dim_table(l_fact_dim_index);
687     END IF;
688     EXIT WHEN l_fact_dim_index = p_fact_time_dim_table.LAST;
689     l_fact_dim_index := p_fact_time_dim_table.NEXT(l_fact_dim_index);
690   END LOOP;
691   l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || ' )';
692 
693   IF (p_fact_nontime_dim_table.COUNT > 0 ) THEN
694     l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || ' AND (';
695   END IF;
696 
697   l_fact_dim_index := p_fact_nontime_dim_table.FIRST;
698   l_start := p_fact_nontime_dim_table.FIRST;
699   LOOP
700     IF(l_fact_dim_index = l_start ) THEN
701       l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || ' FACT. ' || p_fact_nontime_dim_table(l_fact_dim_index) || ' <> 0 ';
702     ELSE
703       l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || ' OR FACT. ' || p_fact_nontime_dim_table(l_fact_dim_index) || ' <> 0 ';
704     END IF;
705     EXIT WHEN l_fact_dim_index = p_fact_nontime_dim_table.LAST;
706     l_fact_dim_index := p_fact_nontime_dim_table.NEXT(l_fact_dim_index);
707   END LOOP;
708   l_calsum_fromwhere_clause := l_calsum_fromwhere_clause || ' )';
709 
710   l_calsum_stmt_psudo := ' ''' || p_fact_name || ''', '  || p_fact_id || ', '''|| p_fact_measure || ''', ' || p_fact_measure_id || ', ';
711 
712   l_calsum_stmt_tmp :=
713     'INSERT INTO EDW_CALSUM4_BSC(FACT, FACT_ID, MEASURE, MEASURE_ID, TIMEHIER, HIERID, CALSUMMARY, CALSUM_ID, LAST_UPDATE_DATE, CREATION_DATE) ';
714   l_calsum_stmt := l_calsum_stmt_tmp;
715 
716   IF(p_hier = '445') THEN
717     l_calsum_stmt_tmp :=
718     'SELECT DISTINCT'
719     || l_calsum_stmt_psudo
720     || '''445'''        || ', '
721     || GET_TIME_HIER_ID('TIME45')
722     || ', DIM.P445_NAME '
723     || ', DIM.P445_PERIOD_445_PK_KEY, SYSDATE, SYSDATE'
724     || l_calsum_fromwhere_clause
725     || ' AND DIM.P445_PERIOD_445_PK_KEY IS NOT NULL'
726     || ' AND DIM.P445_PERIOD_445_PK_KEY != 0 '
727     || ' AND DIM.P445_PERIOD_445_PK_KEY != -1 ';
728     l_calsum_stmt := l_calsum_stmt || l_calsum_stmt_tmp;
729   ELSIF (p_hier = 'PA') THEN
730     l_calsum_stmt_tmp :=
731     'SELECT DISTINCT'
732     || l_calsum_stmt_psudo
733     || '''PA'''         || ', '
734     || GET_TIME_HIER_ID('TIMEPA')
735     || ', DIM.CNAM_CAL_NAME '
736     || ', DIM.CNAM_CAL_NAME_PK_KEY  , SYSDATE, SYSDATE'
737     || l_calsum_fromwhere_clause
738     || ' AND DIM.PPER_PA_PERIOD_PK_KEY IS NOT NULL'
739     || ' AND DIM.CNAM_CAL_NAME_PK_KEY IS NOT NULL'
740     || ' AND DIM.CNAM_CAL_NAME_PK_KEY != 0 '
741     || ' AND DIM.CNAM_CAL_NAME_PK_KEY != -1 ';
742     l_calsum_stmt := l_calsum_stmt || l_calsum_stmt_tmp;
743   ELSIF (p_hier = 'GL') THEN
744     l_calsum_stmt_tmp :=
745     'SELECT DISTINCT'
746     || l_calsum_stmt_psudo
747     || '''GL'''         || ', '
748     || GET_TIME_HIER_ID('TIMEGL')
749     || ', DIM.CNAM_CAL_NAME '
750     || ', DIM.CNAM_CAL_NAME_PK_KEY  , SYSDATE, SYSDATE'
751     || l_calsum_fromwhere_clause
752     || ' AND DIM.CNAM_CAL_NAME_PK_KEY IS NOT NULL'
753     || ' AND DIM.CNAM_CAL_NAME_PK_KEY != 0 '
754     || ' AND DIM.CNAM_CAL_NAME_PK_KEY != -1 ';
755     l_calsum_stmt := l_calsum_stmt || l_calsum_stmt_tmp;
756   ELSIF (p_hier = 'GREGERION') THEN
757     l_calsum_stmt_tmp :=
758     'SELECT DISTINCT'
759     || l_calsum_stmt_psudo
760     || '''GREGERION'''  || ', '
761     || GET_TIME_HIER_ID('TIMEGR')
762     || ', DIM.YEAR_NAME '
763     || ', DIM.YEAR_YEAR_PK_KEY , SYSDATE, SYSDATE'
764     || l_calsum_fromwhere_clause
765     || ' AND DIM.YEAR_YEAR_PK_KEY IS NOT NULL'
766     || ' AND DIM.YEAR_YEAR_PK_KEY != 0 '
767     || ' AND DIM.YEAR_YEAR_PK_KEY != -1 ';
768     l_calsum_stmt := l_calsum_stmt || l_calsum_stmt_tmp;
769   ELSIF (p_hier = 'ENTERPRISE') THEN
770     l_calsum_stmt_tmp :=
771     'SELECT DISTINCT'
772     || l_calsum_stmt_psudo
773     || '''ENTERPRISE''' || ', '
774     || GET_TIME_HIER_ID('TIMEEP')
775     || ', DIM.ECNM_CAL_NAME '
776     || ', DIM.ECNM_CAL_NAME_PK_KEY  , SYSDATE, SYSDATE'
777     || l_calsum_fromwhere_clause
778     || ' AND DIM.ECNM_CAL_NAME_PK_KEY IS NOT NULL'
779     || ' AND DIM.ECNM_CAL_NAME_PK_KEY != 0 '
780     || ' AND DIM.ECNM_CAL_NAME_PK_KEY != -1 ';
781     l_calsum_stmt := l_calsum_stmt || l_calsum_stmt_tmp;
782   END IF;
783   LOG(NULL, DEBUG, '---->' || l_calsum_stmt);
784   LOG(NULL, SCRIPTED, '<--Statement generated for ' || p_hier );
785   RETURN l_calsum_stmt;
786   EXCEPTION
787     WHEN OTHERS THEN
788       l_ErrorCode := SQLCODE;
792       LOOP
789       l_ErrorText := SQLERRM;
790       l_err_info  := 'Error happened when scripting for ' || p_fact_measure || ' and ' || p_hier ;
791       l_fact_dim_index := p_fact_time_dim_table.FIRST;
793         l_err_info := l_err_info || ', '|| p_fact_time_dim_table(l_fact_dim_index);
794         EXIT WHEN l_fact_dim_index = p_fact_time_dim_table.LAST;
795         l_fact_dim_index := p_fact_time_dim_table.NEXT(l_fact_dim_index);
796       END LOOP;
797       l_err_info := l_err_info || ' in ' || p_fact_name;
798       LOG(l_ErrorCode, l_ErrorText, l_err_info);
799       LOG(NULL, FAILED, '<-Statement generating failed for Fact: ' || p_fact_name || ', ' || ' Measure: ' || p_fact_measure || ', Hier: ' || p_hier);
800       RAISE e_DynamicSqlStmtErr;
801 END GET_SQL_STMT;
802 
803 
804 FUNCTION CHECK_VALIDILITY(
805   p_tbl_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE ,
806   p_col_name ALL_TAB_COLUMNS.COLUMN_NAME%TYPE)
807 RETURN BOOLEAN IS
808   l_count    NUMBER;
809 BEGIN
810   OPEN c_att_validity( p_tbl_name , p_col_name);
811   FETCH c_att_validity INTO l_count;
812   CLOSE c_att_validity;
813   IF(l_count =1 ) THEN
814     RETURN TRUE;
815   ELSE
816     RETURN FALSE;
817   END IF;
818 END CHECK_VALIDILITY;
819 
820 FUNCTION GET_DIM_ID(
821   p_object_name EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE
822 ) RETURN EDW_DIMENSIONS_MD_V.DIM_ID%TYPE
823 IS
824   l_id   EDW_DIMENSIONS_MD_V.DIM_ID%TYPE;
825 BEGIN
826   OPEN dim_id(p_object_name);
827   FETCH dim_id INTO l_id;
828   CLOSE dim_id;
829   RETURN l_id;
830 END GET_DIM_ID;
831 
832 FUNCTION GET_TIME_ID
833 RETURN EDW_DIMENSIONS_MD_V.DIM_ID%TYPE
834 IS
835 BEGIN
836   IF (v_time_dimension_id IS NULL) THEN
837     v_time_dimension_id := GET_DIM_ID('EDW_TIME_M');
838   END IF;
839   RETURN v_time_dimension_id;
840 END GET_TIME_ID;
841 
842 PROCEDURE INITIALIZE IS
843 BEGIN
844   LOG(NULL, STARTED, 'Initializing, EDW_TIME_ID: ' || GET_TIME_ID);
845 END;
846 
847 PROCEDURE FINALIZE IS
848 BEGIN
849   LOG(NULL, DONE, 'EDW_CALSUM4_BSC table was populated successfully!');
850 END;
851 
852 FUNCTION GET_FACT_ID(
853    p_fact_name IN EDW_FACTS_MD_V.FACT_NAME%TYPE
854 ) RETURN EDW_FACTS_MD_V.FACT_ID%TYPE IS
855 BEGIN
856   IF(v_fact_name IS NULL OR  v_fact_name <> p_fact_name) THEN
857     v_fact_name := p_fact_name;
858     OPEN c_factid(p_fact_name);
859     FETCH c_factid INTO v_fact_id;
860     CLOSE c_factid;
861   END IF;
862   RETURN v_fact_id;
863 END GET_FACT_ID;
864 
865 
866 FUNCTION GET_TIME_HIER_ID(
867   p_hier_prefix          IN EDW_HIERARCHIES_MD_V.HIER_PREFIX%TYPE
868 ) RETURN NUMBER IS
869 BEGIN
870   IF p_hier_prefix = 'TIME45' THEN
871     IF v_445_id IS NOT NULL THEN
872       RETURN v_445_id;
873     ELSE
874       v_445_id := GET_HIER_ID('TIME45');
875       RETURN v_445_id;
876     END IF;
877   ELSIF p_hier_prefix = 'TIMEGL' THEN
878     IF v_gl_id IS NOT NULL THEN
879       RETURN v_gl_id;
880     ELSE
881       v_gl_id := GET_HIER_ID('TIMEGL');
882       RETURN v_gl_id;
883     END IF;
884   ELSIF p_hier_prefix = 'TIMEPA' THEN
885     IF v_pa_id IS NOT NULL THEN
886       RETURN v_pa_id;
887     ELSE
888       v_pa_id := GET_HIER_ID('TIMEPA');
889       RETURN v_pa_id;
890     END IF;
891   ELSIF p_hier_prefix = 'TIMEEP' THEN
892     IF v_enterprise_id IS NOT NULL THEN
893       RETURN v_enterprise_id;
894     ELSE
895       v_enterprise_id := GET_HIER_ID('TIMEEP');
896       RETURN v_enterprise_id;
897     END IF;
898   ELSIF p_hier_prefix = 'TIMEGR' THEN
899     IF v_gregerion_id IS NOT NULL THEN
900       RETURN v_gregerion_id;
901     ELSE
902       v_gregerion_id := GET_HIER_ID('TIMEGR');
903       RETURN v_gregerion_id;
904     END IF;
905   END IF;
906 END GET_TIME_HIER_ID;
907 
908 
909 FUNCTION GET_HIER_ID(
910   p_hier_prefix          IN EDW_HIERARCHIES_MD_V.HIER_PREFIX%TYPE
911 ) RETURN NUMBER IS
912   l_hier_id    EDW_HIERARCHIES_MD_V.HIER_ID%TYPE;
913 BEGIN
914   OPEN c_time_hier_id(p_hier_prefix);
915   FETCH c_time_hier_id INTO l_hier_id;
916   CLOSE c_time_hier_id;
917   RETURN l_hier_id;
918 END GET_HIER_ID;
919 
920 FUNCTION GET_LOWEST_LEVEL(
921     p_dim  IN EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE
922 ) RETURN VARCHAR2 IS
923   l_level_name EDW_DIMENSIONS_MD_V.DIM_NAME%TYPE;
924 BEGIN
925   select level_name into l_level_name
926   from edw_levels_md_v lvl
927   where dim_name = p_dim
928   and not exists(
929   select 1 from
930     edw_hierarchy_level_md_v hier
931   where
932        hier.dim_name = lvl.dim_name
933   and  hier.parent_lvl_id = lvl.level_id);
934 
935   RETURN l_level_name;
936 END GET_LOWEST_LEVEL;
937 
938 FUNCTION GET_LOWEST_LEVEL(
939   p_dim_id IN NUMBER
940 ) RETURN VARCHAR2 IS
941   l_level_name EDW_LEVELS_MD_V.LEVEL_NAME%TYPE;
942 BEGIN
943   select level_name into l_level_name
944   from edw_levels_md_v lvl
945   where dim_id = p_dim_id
946   and not exists(
947   select 1 from
948     edw_hierarchy_level_md_v hier
949   where
950        hier.dim_name = lvl.dim_name
951   and  hier.parent_lvl_id = lvl.level_id);
952 
953   RETURN l_level_name;
954 END GET_LOWEST_LEVEL;
955 
956 
957 
958 
959 END EDW_BSC_BRIDGE;