[Home] [Help]
PACKAGE BODY: APPS.EDW_DIM_SV
Source
1 Package Body EDW_DIM_SV AS
2 /* $Header: EDWVDIMB.pls 120.1 2006/03/28 01:43:30 rkumar noship $ */
3 l_directory Varchar2(30) := '/sqlcom/log';
4
5
6 g_std_flex_view_name varchar2(40) := null;
7 g_std_collection_view_name varchar2(40) := null;
8 g_std_generated_view_name varchar2(40) := null;
9
10 g_creation_date_exists boolean := false;
11 g_description_exists boolean := false;
12 g_last_update_date_exists boolean := false;
13
14 newline varchar2(10) := '
15 ';
16 tabb varchar2(10) := ' ';
17
18
19 cid number;
20 flexdim_viewname1 varchar2(40);
21 flexdim_viewname2 varchar2(40);
22 g_noOfMappings number := 1;
23 g_noOfTableVS NUMBER := 0;
24 g_noOfDepVS NUMBER := 0;
25 g_noOfIndepVS NUMBER := 0;
26 g_noOfNoneVS NUMBER := 0;
27
28 Procedure getCountForVSTypes(dim_name in VARCHAR2) IS
29 CURSOR c_getCount IS
30 SELECT value_set_type type, count(*) count
31 FROM edw_flex_seg_mappings
32 WHERE dimension_short_name = dim_name
33 AND instance_code = edw_gen_view.g_instance
34 GROUP BY value_set_type;
35
36 BEGIN
37 IF (g_log) THEN
38 edw_gen_view.writelog('Inside getCountForVSTypes');
39 edw_gen_view.writelog('Parameter dim_name:'||dim_name);
40 END IF;
41
42 FOR r1 IN c_getCount LOOP
43 IF (r1.type = 'F') THEN
44 g_noOfTableVS := r1.count;
45 ELSIF (r1.type = 'D') THEN
46 g_noOfDepVS := r1.count;
47 ELSIF (r1.type = 'I') THEN
48 g_noOfIndepVS := r1.count;
49 ELSIF (r1.type = 'N') THEN
50 g_noOfNoneVS := r1.count;
51 END IF;
52 END LOOP;
53
54
55 IF (g_log) THEN
56 edw_gen_view.writelog('No. of table validated VS :'||g_noOfTableVS);
57 edw_gen_view.writelog('No. of dependant VS :'||g_noOfDepVS);
58 edw_gen_view.writelog('No. of independant VS :'||g_noOfIndepVS);
59 edw_gen_view.writelog('No. of none VS :'||g_noOfNoneVS);
60 END IF;
61
62 g_noOfMappings := g_noOfTableVS + g_noOfDepVS + g_noOfIndepVS + g_noOfNoneVS;
63 g_noOfIndepVS := g_noOfIndepVS - g_noOfDepVS;
64 IF (g_log) THEN
65 edw_gen_view.writelog('Completed getCountForVSTypes');
66 edw_gen_view.indentEnd;
67 END IF;
68
69 END;
70
71 Procedure getViewnameForFlexdim(dim_name in varchar2) IS
72
73 cursor c_viewForFlexdim(obj_short_name varchar2 ) is
74 select collection_view_name from edw_source_views
75 where object_name = obj_short_name
76 order by level_name;
77
78 BEGIN
79
80 IF (g_log) THEN
81 edw_gen_view.indentBegin;
82 edw_gen_view.writelog(newline);
83 edw_gen_view.writelog('Inside getViewnameForFlexdim');
84 edw_gen_view.writelog('Parameter dim_name:'||dim_name);
85 END IF;
86
87 open c_viewForFlexdim(dim_name);
88 /* there are only 2 levels that are supported. */
89
90 fetch c_viewForFlexdim into flexdim_viewname1;
91 fetch c_viewForFlexdim into flexdim_viewname2;
92
93 close c_viewForFlexdim;
94
95 IF (g_log) THEN
96 edw_gen_view.writelog('flexdim_viewname1 is '||flexdim_viewname1);
97 edw_gen_view.writelog('flexdim_viewname2 is '||flexdim_viewname2);
98
99 edw_gen_view.writelog('Completed getViewnameForFlexdim');
100
101 edw_gen_view.indentEnd;
102 END IF;
103
104 Exception when no_data_found then
105 edw_gen_view.g_success := false;
106 edw_gen_view.g_error := 'View Names not seeded for ' ||dim_name;
107 raise edw_gen_view.viewgen_exception;
108
109
110 END;
111
112 procedure checkColumnsPresent(p_table IN varchar2) IS
113
114 l_cursor_id number :=0;
115 l_column varchar2(40);
116 l_count number:=0;
117 stmt varchar2(3000);
118 l_table varchar2(100);
119 l_table_alias varchar2(100);
120
121 l_object_type varchar2(100);
122
123 TYPE CurTyp IS REF CURSOR;
124 cv CurTyp;
125
126 BEGIN
127
128 IF (g_log) THEN
129 edw_gen_view.indentBegin;
130 edw_gen_view.writelog('Inside checkColumnsPresent for table :'||p_table||':');
131 END IF;
132
133 g_last_update_date_exists := false;
134 g_creation_date_exists := false;
135 g_description_exists := false;
136
137 l_table := p_table;
138
139 /* Check if Object is a synonym, eg. mtl_system_items is a synonym
140 pointing to mtl_system_items_b */
141
142 Open cv for 'select object_type from user_objects where object_name=:s1' using l_table;
143 Fetch cv into l_object_type;
144 Close cv;
145
146 IF (l_object_type = 'SYNONYM') THEN
147
148 edw_gen_view.writelog(l_table||' is a synonym!!! Getting actual table name... ');
149 Open cv for 'select table_name from user_synonyms where synonym_name=:s1' using l_table;
150 Fetch cv into l_table;
151 Close cv;
152 END IF;
153
154 IF (l_table is null) THEN
155 IF (g_log) THEN
156 edw_gen_view.writelog('Completed checkColumnsPresent');
157 edw_gen_view.indentEnd;
158 END IF;
159 return;
160 END IF;
161
162 edw_gen_view.writelog('Table name is :'||l_table||':');
163
164
165
166 stmt := 'SELECT distinct column_name FROM all_tab_columns@'||edw_gen_view.g_source_db_link
167 ||' WHERE column_name in( ''LAST_UPDATE_DATE'', ''CREATION_DATE'', ''DESCRIPTION'' )'
168 ||' and table_name = :table_name ';
169
170
171 edw_gen_view.writelog('Querying for columns : '||stmt);
172
173 l_cursor_id:=DBMS_SQL.OPEN_CURSOR;
174 DBMS_SQL.parse(l_cursor_id, stmt, DBMS_SQL.V7);
175 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':table_name', l_table, 50);
176
177 DBMS_SQL.define_column(l_cursor_id, 1, l_column, 40);
178 l_count :=DBMS_SQL.execute(l_cursor_id);
179
180 loop
181 if DBMS_SQL.fetch_rows(l_cursor_id)=0 then
182 exit;
183 end if;
184
185 DBMS_SQL.column_value (l_cursor_id, 1, l_column);
186 IF (l_column = 'LAST_UPDATE_DATE') THEN
187 g_last_update_date_exists := true;
188 ELSIF (l_column = 'CREATION_DATE') THEN
189 g_creation_date_exists := true;
190 ELSIF (l_column = 'DESCRIPTION') THEN
191 g_description_exists := true;
192 END IF;
193 end loop;
194
195 DBMS_SQL.close_cursor(l_cursor_id);
196
197 IF (g_log) THEN
198 edw_gen_view.writelog('Completed checkColumnsPresent');
199 edw_gen_view.indentEnd;
200 END IF;
201
202 END;
203
204 procedure parseTable(p_tables in varchar2, p_value_column in varchar2,
205 p_alias OUT NOCOPY varchar2, p_final_tab OUT NOCOPY varchar2) IS
206
207 l_table varchar2(1000);
208
209 bAliasExists boolean := false;
210 bSingleTable boolean := false;
211
212 TYPE table_and_alias IS RECORD (
213 name varchar2(100),
214 alias varchar2(100));
215
216 TYPE t_table_alias IS TABLE OF table_and_alias
217 index by binary_integer;
218
219
220 l_row t_table_alias;
221 l_counter number := 0;
222 l_buffer varchar2(200);
223
224 bMatched boolean := false;
225
226 BEGIN
227
228 l_table := trim(p_tables);
229
230 IF (g_log) THEN
231 edw_gen_view.indentBegin;
232 edw_gen_view.writelog('Inside parseTable for '||p_tables||','||p_value_column);
233 END IF;
234
235
236
237 IF (instr(l_table, ',') = 0 ) THEN
238 bSingleTable := true;
239 ELSE
240 loop -- remove unnecessary spaces
241 l_table := replace (l_table, ' ,', ',');
242 l_table := replace (l_table, ' ', ' ');
243 exit when instr(l_table, ' ,')=0 AND instr(l_table, ' ')=0 ;
244 end loop;
245 END IF;
246
247 edw_gen_view.writelog('l_table is '||l_table);
248
249 IF (instr(p_value_column, '.') >0) THEN
250 bAliasExists := true;
251 p_alias := substr(p_value_column, 0, instr(p_value_column, '.')-1);
252 edw_gen_view.writelog('Alias is :'||p_alias||':');
253 ELSE
254 bAliasExists := false;
255 END IF;
256
257 edw_gen_view.writelog('l_table is :'||l_table||':');
258
259 /* Put Table/Alias combinations in a PL/SQL table */
260
261 loop
262 IF (bSingleTable) THEN
263 l_row(l_counter).name := substr(l_table, 0, instr(l_table, ' '));
264
265 IF (l_row(l_counter).name is NULL) THEN
266 l_row(l_counter).name := l_table;
267 ELSE
268 l_row(l_counter).alias := substr(l_table, instr(l_table, ' ')+1, length (l_table));
269 END IF;
270 p_final_tab := l_row(l_counter).name;
271 exit;
272 END IF;
273
274 /* Multiple Tables */
275 edw_gen_view.writelog('Multiple Tables... ');
276
277
278 -- Get Next table into buffer
279 IF (instr(l_table, ',') > 0) THEN
280 l_buffer := trim(substr (l_table, 0, instr(l_table, ',')-1));
281 ELSE
282 l_buffer := l_table;
283 l_table := null;
284 END IF;
285
286 edw_gen_view.writelog('l_buffer is :'||l_buffer||':');
287
288 -- Removed processed table from l_table
289 l_table := substr(l_table, instr(l_table, ',')+1, length(l_table));
290 edw_gen_view.writelog('l_table is :'||l_table||':');
291
292 IF (instr(l_buffer, ' ')>0) THEN -- alias exists
293 edw_gen_view.writelog('Alias exists');
294
295 l_row(l_counter).name := trim(substr(l_buffer, 0, instr(l_buffer, ' ')));
296 l_row(l_counter).alias := trim(substr(l_buffer, instr(l_buffer,' '), length(l_buffer)));
297
298 edw_gen_view.writelog('Name :'||l_row(l_counter).name||':');
299 edw_gen_view.writelog('Alias :'||l_row(l_counter).alias||':');
300
301 ELSE
302 edw_gen_view.writelog('No alias for the table');
303 l_row(l_counter).name := trim(l_buffer);
304 edw_gen_view.writelog('Name :'||l_row(l_counter).name||':');
305 END IF;
306
307 IF (bAliasExists) THEN
308 IF (l_row(l_counter).alias = p_alias ) THEN
309 p_final_tab := l_row(l_counter).name;
310 edw_gen_view.writelog('Alias matched...so table is '||p_final_tab);
311
312 bMatched := true;
313 END IF;
314 ELSE
315 p_final_tab := l_row(l_counter).name;
316 edw_gen_view.writelog('No value alias, returning first table '||p_final_tab);
317
318 bMatched := true;
319 END IF;
320
321 l_counter := l_counter + 1;
322 exit when trim(l_table) is null or bMatched or (l_counter > 100);
323
324 end loop;
325
326
327 edw_gen_view.writelog('Trimmed p_tab is :'||p_final_tab||':');
328
329 IF (g_log) THEN
330 edw_gen_view.writelog('Completed parseTable 2');
331 edw_gen_view.indentEnd;
332 END IF;
333
334 END;
335
336
337 Function getTableValClause(dim_name in varchar2) RETURN varchar2 IS
338 clause varchar2(32000) := null;
339 stmt1 varchar2(2000);
340 stmt2 varchar2(2000);
341 valueCol DBMS_SQL.VARCHAR2_TABLE;
342
343 l_singleClause varchar2(2000);
344 l_dummy_int number;
345 l_count number := 1;
346
347 TYPE CurTyp IS REF CURSOR;
348 cv CurTyp;
349 cvTable CurTyp;
350 l_table varchar2(240);
351 l_comma_begin number:=0 ;
352 l_CURSOR_id number :=0;
353 l_column varchar2(40);
354 l_columnlist varchar2(500);
355 l_temp varchar2(100);
356 l_unionFlag boolean := FALSE;
357
358 l_vsid number :=0;
359 where_Clause varchar2(30000);
360 l_WHERE_count number := 0;
361
362 l_final_table varchar2(100);
363 l_table_alias varchar2(100);
364
365 l_value_column_name varchar2(100);
366 l_meaning_column_name varchar2(100);
367 l_id_column_name varchar2(100);
368
369 l_flex_table varchar2(1000);
370
371 l_value_set_name varchar2(300);
372 l_segment_name varchar2(300);
373 l_structure_name varchar2(300);
374
375 BEGIN
376
377
378 IF (g_log) THEN
379 edw_gen_view.indentBegin;
380 edw_gen_view.writelog('Inside getTableValClause');
381 END IF;
382
383
384 stmt1 := 'SELECT distinct APPLICATION_TABLE_NAME, value_set_id, value_column_name, meaning_column_name,
385 id_column_name, value_set_name FROM edw_flex_seg_mappings a,'||
386 ' fnd_flex_validation_tables@' ||edw_gen_view.g_source_db_link
387 ||' b WHERE a.value_set_id = b.flex_value_set_id '
388 ||' AND a.dimension_short_name = :d1 AND A.VALUE_SET_TYPE = :d2';
389
390 edw_gen_view.writelog('Querying table name : '||stmt1||' with :d1='||dim_name);
391
392 OPEN cv for stmt1 using dim_name, 'F';
393 LOOP -- write this for debugging
394 FETCH cv into l_flex_table, l_vsid, l_value_column_name, l_meaning_column_name,
395 l_id_column_name, l_value_set_name;
396 edw_gen_view.writelog('Table name IS :' ||l_flex_table||' Value Set ID : '||l_vsid||' Value column = '||l_value_column_name||
397 ' Meaning column = '||l_meaning_column_name||' ID column = '||l_id_column_name);
398 EXIT WHEN cv%NOTFOUND;
399 END LOOP;
400 CLOSE Cv;
401
402
403 OPEN cv for stmt1 using dim_name, 'F';
404 l_count := 1;
405 LOOP
406
407 FETCH cv into l_flex_table, l_vsid, l_value_column_name, l_meaning_column_name,
408 l_id_column_name, l_value_set_name;
409 exit when cv%notfound;
410 l_table := l_flex_table;
411
412 edw_gen_view.writelog('Table name IS :' ||l_table||' Value Set ID : '||l_vsid||' Value column = '||l_value_column_name||' Meaning column = '||l_meaning_column_name||' ID column = '||l_id_column_name);
413 l_table := rtrim(ltrim(l_table));
414 parseTable(lower(l_table), lower(l_value_column_name), l_table_alias,l_final_table);
415 checkColumnsPresent(upper(l_final_table));
416
417 l_comma_begin := INSTR(l_table, ',') - 1;
418 IF (l_comma_begin = 0) THEN
419 l_table := substr(l_table, 1, l_comma_begin-1);
420 END IF;
421
422 IF (l_id_column_name IS NULL) THEN
423 stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| value_column_name ||'' L2_PK,
424 ''|| VALUE_COLUMN_NAME|| '' actual_value,
425 '' ||''''''';
426 ELSE
427 IF (l_value_column_name IS null) then
428 stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| id_column_name ||'' L2_PK,
429 ''|| id_COLUMN_NAME|| '' actual_value,'' ||''''''';
430 ELSE
431 stmt2 := 'SELECT DISTINCT ''SELECT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''''||''||''|| id_column_name ||'' L2_PK,
432 ''|| value_COLUMN_NAME|| '' actual_value,
433 '' ||''''''';
434 END IF;
435 END IF;
436
437 stmt2:= stmt2||edw_gen_view.g_instance||'''''''||'' instance, ';
438
439
440 IF (g_last_update_date_exists) THEN
441 edw_gen_view.writelog('Last Update date exists');
442
443 IF (l_table_alias IS NOT NULL) then
444 stmt2 := stmt2||' '||l_table_alias||'.last_update_date ';
445 ELSE
446 stmt2 := stmt2||' '||l_final_table||'.last_update_date ';
447 END IF;
448 stmt2 := stmt2 || ' last_update_date, ';
449 ELSE
450 stmt2 := stmt2 || ' to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') last_update_date, ';
451 END IF;
452
453 IF (g_creation_date_exists) THEN
454 edw_gen_view.writelog('Creation date exists');
455 IF (l_table_alias IS NOT NULL) then
459 END IF;
456 stmt2 := stmt2||' '||l_table_alias||'.creation_date ';
457 ELSE
458 stmt2 := stmt2||' '||l_final_table||'.creation_date ';
460 stmt2 := stmt2 || ' creation_date, ';
461 ELSE
462 stmt2 := stmt2 || ' to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') creation_date, ';
463 END IF;
464
465
466
467 IF (l_meaning_column_name IS not null) THEN
468 stmt2 := stmt2 || l_meaning_column_name||' description, ';
469 ELSE
470 IF (g_description_exists) THEN
471 edw_gen_view.writelog('Description exists');
472 IF (l_table_alias IS NOT NULL) then
473 stmt2 := stmt2||' '||l_table_alias||'.description ';
474 ELSE
475 stmt2 := stmt2||' '||l_final_table||'.description ';
476 END IF;
477 ELSE
478 stmt2 := stmt2||' null ';
479 END IF;
480 stmt2 := stmt2 || ' description, ';
481
482 END IF;
483
484 stmt2 := stmt2||' ''''NA_EDW'''' l2_fk '', '||' APPLICATION_TABLE_NAME FROM edw_flex_seg_mappings a,';
485 stmt2 := stmt2|| ' fnd_flex_validation_tables@' ||edw_gen_view.g_source_db_link ||
486 ' b WHERE a.value_set_id = b.flex_value_set_id AND a.dimension_short_name = :d1 ';
487 stmt2 := stmt2||' AND application_table_name = :d2';
488
489 IF (g_log) THEN
490 edw_gen_view.writelog( ' Query IS : '||stmt2);
491 END IF;
492
493 OPEN cvTable for stmt2 using dim_name, l_flex_table;
494
495
496 LOOP
497 edw_gen_view.writelog( ' l_Count IS : '||l_count);
498 FETCH cvTable into l_singleClause, l_table ;
499 exit when cvTable%notfound;
500
501 IF (clause IS NOT NULL) THEN
502 clause := clause||' UNION ALL '||newline;
503 END IF;
504 clause := clause|| l_singleClause ;
505 clause := clause ||newline|| ' FROM '|| l_table|| ' ';
506
507 IF (g_log) THEN
508 edw_gen_view.writelog( ' View clause IS : '||clause);
509 END IF;
510
511
512 edw_gen_view.g_where_clause.delete;
513
514 IF (not edw_gen_view.checkWhereClause(l_vsid, edw_gen_view.g_source_db_link)) THEN
515 edw_gen_view.g_success := FALSE;
516 fnd_message.set_name('BIS', 'EDW_BIND_VARIABLES_FOUND');
517 fnd_message.set_token('OBJ', dim_name);
518 fnd_message.set_token('TAB', l_value_set_name);
519 fnd_message.set_token('STRUCT', l_structure_name);
520 fnd_message.set_token('SEGMENT', l_segment_name);
521 edw_gen_view.g_error := fnd_message.get;
522 raise edw_gen_view.viewgen_exception;
523 END IF;
524
525
526 IF (edw_gen_view.g_where_clause.count > 0) THEN
527 clause := clause ||newline;
528 LOOP
529 edw_gen_view.writelog('Count IS : '||l_where_count);
530 clause := clause || edw_gen_view.g_where_clause(l_where_count);
531 edw_gen_view.writelog('Select Clause IS : '|| clause);
532 exit when l_where_count = edw_gen_view.g_where_clause.last;
533 l_where_count := l_where_count + 1;
534 END LOOP;
535 END IF;
536
537 l_count := l_count + 1;
538 END LOOP;
539 CLOSE cvTable;
540 END LOOP;
541 CLOSE cv;
542
543
544 IF (g_log) then
545 edw_gen_view.writelog('Completed getTableValClause');
546 edw_gen_view.indentEnd;
547 END IF;
548 RETURN clause;
549 END;
550
551
552
553 FUNCTION getIndepVSClause( p_dim_name in VARCHAR2, p_level in VARCHAR2) RETURN VARCHAR2 IS
554 src_view VARCHAR2(30000) := null;
555 singleClause varchar2(3000) := null;
556 l_count NUMBER := 0;
557
558 CURSOR c_getLowerLevelIndepValueSets IS
559 SELECT distinct a.value_set_type, a.value_set_id, a.parent_value_set_id
560 FROM edw_flex_seg_mappings a
561 WHERE
562 dimension_short_name = p_dim_name
563 and instance_code = edw_gen_view.g_instance
564 AND ( value_set_type = 'I' /* OR value_set_type = 'N' OR
565 value_set_type = 'F' */)
566 AND NOT EXISTS
567 (SELECT 1 FROM edw_flex_seg_mappings b
568 where b.parent_value_set_id = a.value_set_id
569 AND b.dimension_short_name = a.dimension_short_name
570 AND b.structure_num = a.structure_num);
571
572 CURSOR c_getHigherLevelIndepValueSets IS
573 SELECT parent.value_set_type, parent.value_set_id, parent.parent_value_set_id , parent.structure_num
574 FROM edw_flex_seg_mappings parent, edw_flex_seg_mappings child
575 WHERE
576 parent.dimension_short_name = child.dimension_short_name
577 AND parent.dimension_short_name = p_dim_name
578 AND parent.instance_code = edw_gen_view.g_instance
579 AND child.instance_code = edw_gen_view.g_instance
580 AND parent.value_set_type = 'I'
581 AND child.value_set_type = 'D'
582 AND child.parent_value_set_id = parent.value_set_id
583 AND parent.structure_num = child.structure_num;
584
585 BEGIN
586
587 IF (g_log) THEN
588 edw_gen_view.indentBegin;
589
590 edw_gen_view.writelog('Inside getIndepVSClause for '||p_level);
591 END IF;
592 l_count := 0;
593 IF (p_level = 'LOWER') THEN
597 newline||' creation_date, description, ''NA_EDW'' L2_FK '||
594
595 src_view := 'SELECT '||''''||edw_gen_view.g_instance||':''||'||'flex_value_set_id||'':''||flex_value L2_PK, flex_value actual_value, ' ||newline||
596 ' '''||edw_gen_view.g_instance||''''||' instance, last_update_date, '||
598 newline||' FROM fnd_flex_values_vl '||
599 newline||' WHERE flex_value_set_id IN ( ';
600
601 FOR r1 IN c_getLowerLevelIndepValueSets LOOP
602
603 IF (l_count >= 1) THEN
604 src_view := src_view||', ';
605 END IF;
606
607
608 src_view:=src_view||r1.value_set_id;
609 l_count := l_count + 1;
610 END LOOP;
611 ELSE
612 src_view := 'SELECT '||''''||edw_gen_view.g_instance||':''||'||'a.flex_value_set_id||'':''||a.flex_value L1_PK, ' ||newline||
613 ' flex_value actual_value, '||
614 ''''||edw_gen_view.g_instance||''''||' instance, '||newline||
615 ' a.last_update_date, a.creation_date, a.description, ''NA_EDW'' L1_FK '||
616 newline||' FROM fnd_flex_values_vl a ' ||
617 ' WHERE a.flex_value_set_id IN ( ';
618 FOR r1 IN c_getHigherLevelIndepValueSets LOOP
619
620 IF (l_count >= 1) THEN
621 src_view:=src_view||', ';
622 END IF;
623 src_view:=src_view||r1.value_set_id;
624 l_count := l_count + 1;
625 END LOOP;
626 END IF;
627 src_view := src_view || ')';
628
629 IF (g_log) THEN
630 edw_gen_view.writelog('Independant Value Set Clause is : '||src_view);
631 edw_gen_view.writelog('Completed getIndepVSClause');
632 edw_gen_view.indentEnd;
633 END IF;
634
635 return src_view;
636 END;
637
638 FUNCTION getDepVSClause(p_dim_name in varchar2) RETURN VARCHAR2 IS
639
640 src_view varchar2(30000) := null;
641 l_count number := 0;
642 final_view varchar2(32000) := null;
643 CURSOR c_getLowerLevelVSets IS
644 SELECT distinct a.value_set_type, a.value_set_id, a.parent_value_set_id
645 FROM edw_flex_seg_mappings a WHERE
646 dimension_short_name = p_dim_name
647 and a.instance_code = edw_gen_view.g_instance
648 AND a.parent_value_set_id <> 0 ; /* dependant value sets */
649
650 BEGIN
651
652 IF (g_log) THEN
653 edw_gen_view.indentBegin;
654
655 edw_gen_view.writelog('Inside getDepVSClause ');
656 edw_gen_view.writelog('Parameter p_dim_name:'||p_dim_name);
657 END IF;
658
659 l_count := 0;
660 src_view := ' SELECT '||''''||edw_gen_view.g_instance||':''||'||' childvl.flex_value_set_id||'':''||parentvl.flex_value|| '':''|| childvl.flex_value L2_PK, parentvl.flex_value||'':''||childvl.flex_value actual_value,';
661 src_view := src_view||''''||edw_gen_view.g_instance||''''||' instance, '||
662 newline||' childvl.last_update_date, childvl.creation_date, '||
663 newline||' childvl.description, '||''''||edw_gen_view.g_instance||':''||'||' parentvl.flex_value_set_id '|| '||'':''||'||' parentvl.flex_value L2_FK ';
664 src_view := src_view|| newline||' from fnd_flex_values_vl childvl, fnd_flex_value_sets child, fnd_flex_values_vl parentvl , fnd_flex_value_sets parent ';
665 src_view := src_view|| newline||' WHERE child.flex_value_set_id = childvl.flex_value_set_id
666 and child.parent_flex_value_set_id = parent.flex_value_set_id
667 and parent.flex_value_set_id = parentvl.flex_value_set_id
668 and childvl.parent_flex_value_low = parentvl.flex_value and child.flex_value_set_id in (';
669
670 final_view := src_View;
671
672 FOR r1 IN c_getLowerLevelVSets LOOP
673 IF (l_count >= 1) THEN
674 final_view := final_view ||', ';
675 END IF;
676
677 final_view := final_view||r1.value_set_id;
678 l_count := l_count + 1;
679
680 END LOOP;
681
682 final_view := final_view||') ';
683 IF (g_log) THEN
684
685 edw_gen_view.writelog('Dependant VS clause is :' ||final_view);
686 edw_gen_view.writelog('Completed getDepVSClause'||newline);
687
688 edw_gen_view.indentEnd;
689 END IF;
690
691 return final_view;
692
693 END;
694
695 FUNCTION getNoneVSClause(p_dim_name in varchar2) RETURN VARCHAR2 IS
696
697
698 finalClause varchar2(32000);
699 select1 varchar2(1000);
700 select2 varchar2(1000);
701 select3 varchar2(1000);
702 select4 varchar2(1000);
703 whereKeyFlex varchar2(1000);
704 whereDescFlex varchar2(1000);
705 finalStmt varchar2(10000);
706
707 singleClause varchar2(1000);
708 l_dummy_int number;
709 l_count number := 1;
710 l_table varchar2(100);
711 l_structure_col varchar2(100);
712
713 l_table_alias varchar2(100);
714 l_structure_id number;
715
716 l_flex_type varchar2(1);
717 l_struct_name VARCHAR2(30);
718
719 TYPE CurTyp IS REF CURSOR;
720 cv CurTyp;
721 cTable CurTyp;
722
723
724 BEGIN
725
726
727
728 IF (g_log) THEN
729 edw_gen_view.indentBegin;
730 edw_gen_view.writelog( 'inside getNoneVSClause');
731 END IF;
732
733 select1 := 'SELECT b.APPLICATION_TABLE_NAME , a.structure_num, a.STRUCTURE_NAME,'||newline;
734
738
735 whereKeyFlex := ' b.SET_DEFINING_COLUMN_NAME,''K'' FROM edw_flex_seg_mappings a, fnd_id_flexs@'||edw_gen_view.g_source_db_link||' b WHERE ( a.id_flex_code = b.id_flex_code ) and a.value_set_type =''N'' and a.application_id=b.application_id '||
736 ' and a.instance_code = :d1 '||
737 ' AND a.dimension_short_name = :d2 ';
739 whereDescFlex := ' b.CONTEXT_COLUMN_NAME, ''D'' FROM edw_flex_seg_mappings a, fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||
740 ' b WHERE ( a.id_flex_code = b.descriptive_flexfield_name ) and a.value_set_type = ''N'' and a.application_id=b.application_id '||
741 ' and a.instance_code = :d3 '||
742 ' AND a.dimension_short_name = :d4 ' ;
743
744 finalStmt := select1||whereKeyFlex||'UNION ALL ' ||newline||select1 ||whereDescFlex;
745
746 --execute immediate finalStmt;
747 open cv for finalStmt using edw_gen_view.g_instance, p_dim_name,
748 edw_gen_view.g_instance, p_dim_name;
749
750 l_count := 1;
751 LOOP
752 fetch cv into l_table, l_structure_id , l_struct_name, l_structure_col,l_flex_type ;
753 exit when cv%notfound;
754
755 checkColumnsPresent(rtrim(ltrim(upper(l_table))));
756
757 select1 := 'SELECT ''SELECT DISTINCT ''||''''''''|| a.instance_code ||'':''||a.value_set_id||'':''''||''||application_column_name ||'' L2_PK, '''||newline;
758 select2 := '|| application_COLUMN_NAME ||'' ACTUAL_VALUE,'' ||''''''''||a.instance_code||''''''''||';
759 select3 := ''' INSTANCE, ';
760
761 IF (g_last_update_date_exists) THEN
762 select3 := select3 || newline||' LAST_UPDATE_DATE, ';
763 ELSE
764 select3 := select3 || newline||' to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') last_update_date, ';
765 END IF;
766 IF (g_creation_date_exists) THEN
767 select3 := select3 || newline||' CREATION_DATE, ';
768 ELSE
769 select3 := select3 || newline||' to_date(null, ''''mm/dd/yyyy hh24:mi:ss'''') creation_date, ';
770 END IF;
771 IF (g_description_exists) THEN
772 select3 := select3 || newline||' DESCRIPTION, ';
773 ELSE
774 select3 := select3 || newline||' null DESCRIPTION, ';
775 END IF;
776
777 select3 := select3 ||'''''NA_EDW'''' L2_FK FROM ''';
778 select4 := ' || b.APPLICATION_TABLE_NAME||'' WHERE ''||application_column_name ||'' is not null ';
779
780 --for bug 3373544
781 /* IF (l_structure_col is not null AND l_structure_id <> -1) THEN
782 select4 := select4 ||' AND '||l_structure_col || ' = '''''||l_structure_id ||'''''';
783 END IF;*/
784
785 IF( l_flex_type = 'K') then
786 IF (l_structure_col is not null AND l_structure_id <> -1) THEN
787 select4 := select4 ||' AND '||l_structure_col || ' = '''''||l_structure_id ||'''''';
788 END IF;
789 END IF;
790
791 IF( l_flex_type = 'D') then
792 IF (l_structure_col is not null AND l_struct_name is not null) THEN
793 select4 := select4 ||' AND '||l_structure_col || ' = '''''|| l_struct_name ||'''''';
794 END IF;
795 END IF;
796
797 select4 := select4||''' FROM edw_flex_seg_mappings a, '||newline;
798
799 whereKeyFlex := 'fnd_id_flexs@'||edw_gen_view.g_source_db_link||' b WHERE ( a.id_flex_code = b.id_flex_code ) and a.value_set_type =''N'' and a.application_id=b.application_id '||
800 ' AND a.instance_code = :d1 '||
801 ' AND a.dimension_short_name = :d2 ' ||
802 ' AND b.application_table_name = :d3 AND a.structure_num = :d7'; -- 4905343- changed l_structure_id into bind variable d7
803
804 whereDescFlex := 'fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||' b WHERE ( a.id_flex_code = b.descriptive_flexfield_name ) and a.value_set_type = ''N'' and a.application_id=b.application_id '||
805 ' AND a.instance_code = :d4 '||
806 ' AND a.dimension_short_name = :d5 ' ||
807 ' AND b.application_table_name = :d6 '|| ' AND a.structure_num = :d8'; -- 4905343- changed l_structure_id into bind variable d8
808
809 finalStmt := select1||select2||select3||select4||whereKeyFlex||' UNION ALL ' ||newline||
810 select1||select2||select3||select4||whereDescFlex;
811
812
813 IF (g_log) THEN
814 edw_gen_view.writelog( newline||'Final SQL to query on None Value Set : '||newline);
815 edw_gen_view.writelog(finalStmt);
816 END IF;
817
818
819 open ctable for finalStmt using edw_gen_view.g_instance, p_dim_name, l_table,
820 edw_gen_view.g_instance, p_dim_name, l_table, l_structure_id,l_structure_id;
821
822
823 loop
824 fetch ctable into singleClause;
825 exit when ctable%notfound;
826 singleClause := replace (singleClause, 'FROM', newline||'FROM');
827 singleClause := replace (singleClause, 'WHERE', newline||'WHERE');
828 finalClause := finalClause||singleClause;
829 if (l_count = g_noOfNoneVS) then
830 null;
831 else
832 finalClause:= finalClause||newline||'UNION ALL '||newline;
833 end if;
834 l_count := l_count + 1;
835
836 end loop;
837 close ctable;
838 END LOOP;
839 close cv;
840 IF (g_log) THEN
841 edw_gen_view.indentEnd;
842 edw_gen_view.writelog('Completed getNoneVSClause');
843 END IF;
844
845 return finalClause;
846
847 END;
848
849 PROCEDURE getViewnamesForStdDim(dim_name IN VARCHAR2, level IN VARCHAR2) IS
850
851 view_name VARCHAR2(50) := null;
852
853 BEGIN
854 IF (g_log) THEN
858 edw_gen_view.writelog('Inside getViewnameForStdDim');
855
856 edw_gen_view.indentBegin;
857
859 END IF;
860
861 SELECT flex_view_name, generated_view_name, collection_view_name
862 INTO g_std_flex_view_name, g_std_generated_view_name, g_std_collection_view_name
863 FROM edw_source_views
864 WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;
865
866 IF (g_log) THEN
867
868
869 edw_gen_view.writelog( 'Flex View Name is :'||g_std_flex_view_name );
870 edw_gen_view.writelog( 'Gen View Name is :'||g_std_generated_view_name );
871 edw_gen_view.writelog( 'Coll Name is :'||g_std_collection_view_name );
872 edw_gen_view.writelog( 'Completed getViewnameForStdDim');
873 edw_gen_view.indentEnd;
874 END IF;
875
876
877 EXCEPTION WHEN no_data_found THEN
878 edw_gen_view.g_success := false;
879 edw_gen_view.g_error := 'View Names not seeded for ' ||dim_name;
880 raise edw_gen_view.viewgen_exception;
881 END;
882
883 FUNCTION getGeneratedViewnameForStdDim(dim_name IN VARCHAR2, level IN VARCHAR2) RETURN VARCHAR2 IS
884
885 view_name VARCHAR2(50) := null;
886
887 BEGIN
888 IF (g_log) THEN
889 edw_gen_view.indentBegin;
890
891
892 edw_gen_view.writelog('Inside getGeneratedViewnameForStdDim');
893 edw_gen_view.writelog('Parameter dim_name:'||dim_name);
894 edw_gen_view.writelog('Parameter level:'||level);
895 END IF;
896
897 SELECT generated_view_name INTO view_name
898 FROM edw_source_views
899 WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;
900
901 IF (g_log) THEN
902 edw_gen_view.writelog('View name for standard dimension '||dim_name || ':'||view_name);
903 edw_gen_view.writelog('Completed getGeneratedViewnameForStdDim');
904
905 edw_gen_view.indentEnd;
906 END IF;
907
908 return view_name;
909
910 EXCEPTION WHEN no_data_found THEN
911 RETURN null;
912 END;
913
914 Procedure generateStdLevel(dim_name IN VARCHAR2, level_name IN VARCHAR2) IS
915
916 attColumns edw_gen_view.tab_att_maps;
917 multiAttList edw_gen_view.tab_multi_att_list;
918 flexColumns edw_gen_view.tab_flex_att_maps;
919 fkColumns edw_gen_view.tab_fact_flex_fk_maps;
920 view_name VARCHAR2(50);
921 v_retCode INTEGER;
922 bColumnMapped BOOLEAN := false;
923 curColumn VARCHAR2(100) := NULL;
924 curColType VARCHAR2(300) := NULL;
925 stmt VARCHAR2(30000) := NULL;
926
927 nColCount INTEGER := 0;
928 nOuterLoopCount INTEGER := 0;
929 nInnerLoopCount INTEGER := 0;
930 srcview VARCHAR2(32000) := null;
931 l_temp_stmt VARCHAR2(32000) := null;
932 v_col DBMS_SQL.VARCHAR2_TABLE;
933 v_colType DBMS_SQL.VARCHAR2_TABLE;
934 decodeClause VARCHAR2(3000);
935
936 Cursor C_Skip_Columns(p_object_short_name VARCHAR2, p_level_name VARCHAR2) IS
937 select attribute_name, attribute_type
938 from edw_attribute_properties
939 where skip_flag = 'Y'
940 and object_short_name = p_object_short_name
941 and level_name = p_level_name;
942
943 Skip_Columns C_Skip_Columns%ROWTYPE;
944
945 Type T_skip_columns_table is table of
946 C_Skip_Columns%rowtype
947 index by binary_integer;
948
949 skip_columns_table T_skip_columns_table;
950 l_count INTEGER := 0;
951
952 BEGIN
953 IF (g_log) THEN
954 edw_gen_view.indentBegin;
955
956 edw_gen_view.writelog('Inside generateStdLevel');
957 edw_gen_view.writelog('Parameter dim_name:'||dim_name);
958 edw_gen_view.writelog('Parameter level_name:'||level_name);
959 END IF;
960
961
962 getViewnamesForStdDim(dim_name, level_name);
963
964 view_name := g_std_collection_view_name;
965
966 nColCount := edw_gen_view.getColumnCountForView(view_name);
967
968 IF (nColCount = 0 ) THEN
969 RETURN;
970 END IF;
971
972 stmt := 'SELECT distinct column_name, data_type FROM all_tab_columns@'||edw_gen_view.g_source_db_link;
973 stmt := stmt||' WHERE table_name = :view_name and owner = :owner';
974
975 cid := DBMS_SQL.open_cursor;
976
977 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
978 DBMS_SQL.BIND_VARIABLE(cid, ':view_name', upper(view_name), 50);
979 DBMS_SQL.BIND_VARIABLE(cid, ':owner', edw_gen_view.g_apps_schema, 50);
980
981 DBMS_SQL.DEFINE_ARRAY(cid, 1, v_col, nColCount, 1);
982 DBMS_SQL.DEFINE_ARRAY(cid, 2, v_colType, nColCount, 1);
983 v_retCode := DBMS_SQL.EXECUTE(cid);
984 v_retCode := DBMS_SQL.FETCH_ROWS(cid);
985 DBMS_SQL.COLUMN_VALUE(cid, 1, v_col);
986 DBMS_SQL.COLUMN_VALUE(cid, 2, v_colType);
987 DBMS_SQL.close_cursor(cid);
988
989 edw_gen_view.getColumnMaps(dim_name, attColumns, multiAttList,
990 flexColumns, fkColumns, level_name);
991
992 /* build skip_columns_table */
993 l_count := 1;
994 open C_Skip_Columns(dim_name, level_name);
995 loop
996 fetch C_Skip_Columns into skip_columns;
997 exit when C_Skip_Columns%NOTFOUND;
998 skip_columns_table (l_count).attribute_name := skip_columns.attribute_name;
999 skip_columns_table (l_count).attribute_type := skip_columns.attribute_type;
1000 l_count := l_count + 1;
1001 end loop;
1002
1003
1007 nOuterLoopCount := nOuterLoopCount + 1;
1004 srcview := ' CREATE OR REPLACE FORCE VIEW ' ||view_name || ' AS '||newline|| 'SELECT ';
1005
1006 WHILE nOuterLoopCount < nColCount LOOP
1008
1009 curColumn := v_col(nOuterLoopCount);
1010 curColType := v_colType(nOuterLoopCount);
1011
1012 /* check if current column is a skipped column */
1013 IF (curColumn LIKE 'USER_ATTRIBUTE%' OR curColumn like 'USER_MEASURE%') THEN
1014 null;
1015 ELSE
1016 nInnerLoopCount := 1;
1017 IF (skip_columns_table.count > 0) THEN
1018
1019 LOOP
1020 IF (skip_columns_table(nInnerLoopCount).attribute_name = curColumn) then
1021 if (curColType = 'NUMBER') then
1022 l_temp_stmt := ',' || newline || 'to_number(null) ' || curColumn;
1023 -- edw_gen_view.writelog('Number Column. Adding to_number');
1024 else
1025 l_temp_stmt := ',' || newline || 'null ' || curColumn;
1026 -- edw_gen_view.writelog('Skipped column, adding null');
1027 end if;
1028 srcview := srcview ||l_temp_stmt;
1029 bColumnMapped := true; -- to skip the part after nomatch
1030 goto nomatch;
1031 end if;
1032 EXIT WHEN nInnerLoopCount = skip_columns_table.last;
1033 nInnerLoopCount := nInnerLoopCount + 1;
1034 end loop;
1035 end if;
1036 END IF;
1037
1038
1039 IF nOuterLoopCount >1 THEN
1040 srcview := srcview || ', '||newline;
1041 END IF;
1042
1043 /* need to process only if its a user attribute */
1044
1045 IF (curColumn LIKE 'USER_ATTRIBUTE%' OR curColumn like 'USER_MEASURE%') THEN
1046 null;
1047 ELSE
1048 goto nomatch;
1049 END IF;
1050
1051 /* see if columns have been mapped to a source attribute */
1052 nInnerLoopCount := 1;
1053 IF (attColumns.count > 0) THEN
1054 LOOP
1055
1056 IF (attColumns(nInnerLoopCount).attribute_name = curColumn) THEN
1057 bColumnMapped := true;
1058 IF (g_log) THEN
1059 edw_gen_view.writelog('attr mapped to attr: '||curColumn);
1060 END IF;
1061
1062 IF (attColumns(nInnerLoopCount).datatype <> 'DATE') THEN
1063 srcview := srcview || attColumns(nInnerLoopCount).source_attribute
1064 ||' '||curColumn ;
1065 ELSE
1066 srcview := srcview || ' to_char('||attColumns(nInnerLoopCount).source_attribute||' , ''mm/dd/yyyy hh24:mi:ss'') '
1067 ||' '||curColumn ;
1068
1069 END IF;
1070 goto nomatch;
1071 END IF;
1072
1073 EXIT WHEN nInnerLoopCount = attColumns.last;
1074 nInnerLoopCount := nInnerLoopCount + 1;
1075 END LOOP;
1076 END IF;
1077
1078 /* see if columns have been mapped to a source flex field */
1079 nInnerLoopCount := 1;
1080 IF (flexColumns.count > 0) THEN
1081 LOOP
1082 IF (g_log) THEN
1083 edw_gen_view.writelog('Checking column '||curColumn||' for flex mapping');
1084 END IF;
1085
1086 IF (flexColumns(nInnerLoopCount).attribute_name = curColumn) THEN
1087 bColumnMapped := true;
1088
1089 IF (g_log) THEN
1090 edw_gen_view.writelog('attr mapped to flex: '||curColumn);
1091 END IF;
1092 -- decode flex
1093
1094 decodeClause := edw_gen_view.getDecodeClauseForFlexCol( g_std_flex_view_name, curColumn, flexColumns(nInnerLoopCount).id_flex_code, flexColumns(nInnerLoopCount).flex_field_type);
1095 srcview := srcview || decodeClause||' '||curColumn;
1096 goto nomatch;
1097 END IF;
1098 EXIT WHEN nInnerLoopCount= flexColumns.last;
1099 nInnerLoopCount := nInnerLoopCount + 1;
1100 END LOOP;
1101 END IF;
1102
1103 <<nomatch>>
1104
1105 IF (bColumnMapped = false) THEN
1106
1107 IF (curColumn like 'USER_ATTRIBUTE%' or curColumn like 'USER_MEASURE%') THEN
1108 srcview := srcview||'null ';
1109 END IF;
1110
1111 srcview := srcview || v_col(nOuterLoopCount);
1112
1113 END IF;
1114 bColumnMapped := false;
1115
1116
1117
1118 null;
1119 END LOOP;
1120 srcview := srcview|| ' FROM '||getGeneratedViewnameForStdDim(dim_name, level_name);
1121
1122 srcview := replace (srcview, 'SELECT ', 'SELECT '||newline);
1123
1124 IF (g_log) THEN
1125
1126 edw_gen_view.writelog( srcview);
1127 edw_gen_view.writelog('Standard level view is : '||newline||srcview);
1128 edw_gen_view.writeout(srcview|| newline);
1129 edw_gen_view.writeOutLine('/');
1130 edw_gen_view.writeOutLine('EXIT;');
1131
1132 END IF;
1133
1134 edw_gen_view.createView(srcview, view_name);
1135 IF (g_log) THEN
1136 edw_gen_view.writelog('Completed generateStdLevel');
1137 edw_gen_view.indentEnd;
1138
1139 END IF;
1140
1141 END;
1142
1143 Procedure generateStdDimension(dim_name IN VARCHAR2) IS
1144
1145
1146 CURSOR dimLevels IS
1147 SELECT DISTINCT level_name FROM edw_attribute_mappings
1148 WHERE object_short_name = dim_name;
1149
1150 BEGIN
1154 edw_gen_view.writelog('Inside generateStdDimension');
1151 IF (g_log) THEN
1152 edw_gen_view.indentBegin;
1153
1155 END IF;
1156
1157 FOR r1 in dimLevels LOOP
1158 generateStdLevel(dim_name, r1.level_name);
1159 END LOOP;
1160
1161 IF (g_log) THEN
1162 edw_gen_view.writelog('Completed generateStdDimension');
1163 edw_gen_view.indentEnd;
1164 END IF;
1165
1166 END;
1167
1168
1169 Procedure generateViewForDimension(dim_name in varchar2) IS
1170
1171 src_view varchar2(32000) := '';
1172 l_count number := 1;
1173
1174 l_unionFlag boolean := false;
1175 selectClause VARCHAR2(32000) := null;
1176
1177 l_buffer Varchar2(10000);
1178 l_applsys_schema Varchar2(32);
1179 dummy1 Varchar2(32);
1180 dummy2 Varchar2(32);
1181 l_retval Boolean;
1182 l_dummy INTEGER;
1183
1184 stmt1 varchar2(250);
1185 stmt2 varchar2(250);
1186 stmt3 varchar2(250);
1187
1188 apiClause VARCHAR2(32000);
1189
1190 cursor c_getMappingsForDim(obj_name varchar2) is
1191 select value_set_type, value_set_id, parent_value_set_id
1192 from edw_flex_seg_mappings where
1193 dimension_short_name = obj_name
1194 and instance_code = edw_gen_view.g_instance
1195 order by value_set_type;
1196
1197
1198 BEGIN
1199
1200 edw_misc_util.globalNamesOff;
1201
1202
1203 IF (g_log) THEN
1204 edw_gen_view.indentBegin;
1205
1206 edw_gen_view.writelog( 'Inside generateViewForDimension');
1207 edw_gen_view.writelog( 'Parameter dim_name:'||dim_name);
1208 END IF;
1209
1210 IF (dim_name LIKE 'EDW_FLEX_DIM%' OR dim_name LIKE 'EDW_GL_ACCT%') THEN
1211 null;
1212 ELSE
1213 generateStdDimension(dim_name);
1214 RETURN;
1215 END IF;
1216
1217 getViewnameForFlexdim(dim_name);
1218
1219 g_noOfMappings := 0;
1220 g_noOfTableVS := 0;
1221 g_noOfDepVS := 0;
1222 g_noOfIndepVS := 0;
1223 g_noOfNoneVS := 0;
1224
1225 getCountForVSTypes(dim_name);
1226
1227 IF (g_noOfMappings = 0 ) THEN
1228 IF (g_log) THEN
1229 edw_gen_view.writelog(newline);
1230 edw_gen_view.Writelog('!!!!! No Mappings !!!! returning...');
1231 END IF;
1232 RETURN;
1233 END IF;
1234
1235 src_view := ' CREATE OR REPLACE FORCE VIEW '|| flexdim_viewname2 ||' AS '||newline;
1236
1237
1238 IF (g_noOfDepVS > 0) THEN
1239 selectClause := getDepVSClause(dim_name);
1240 l_unionFlag := true;
1241 IF (g_log) THEN
1242 edw_gen_view.writelog(newline);
1243 END IF;
1244
1245 END IF;
1246
1247
1248
1249 IF (g_noOfTableVS > 0) THEN
1250 IF (g_log) THEN
1251 edw_gen_view.writelog('table validated vs exists');
1252 END IF;
1253
1254 IF (l_unionFlag = true) THEN
1255 selectClause := selectClause || newline||'UNION ALL '||newline;
1256 END IF;
1257 l_unionFlag := true;
1258 selectClause := selectClause || getTableValClause(dim_name);
1259 IF (g_log) THEN
1260 edw_gen_view.writelog(newline);
1261
1262 END IF;
1263 END IF;
1264
1265 IF (g_noOfIndepVS > 0) THEN
1266 IF (g_log) THEN
1267 edw_gen_view.writelog('Independant value set type exists');
1268 END IF;
1269 IF (l_unionFlag = true) THEN
1270 selectClause := selectClause || newline||'UNION ALL '||newline;
1271 END IF;
1272 l_unionFlag := true;
1273 selectClause := selectClause||getIndepVSClause(dim_name, 'LOWER');
1274
1275 IF (g_log) THEN
1276 edw_gen_view.writelog(newline);
1277 END IF;
1278
1279 END IF;
1280
1281 IF (g_noOfNoneVS > 0) THEN
1282 IF (g_log) THEN
1283 edw_gen_view.writelog('None value set type exists');
1284 END IF;
1285 IF (l_unionFlag = true) THEN
1286 selectClause := selectClause ||newline|| 'UNION ALL '||newline;
1287 END IF;
1288 l_unionFlag := true;
1289 selectClause := selectClause||getNoneVSClause(dim_name);
1290 IF (g_log) THEN
1291 edw_gen_view.writelog(newline);
1292 END IF;
1293
1294 END IF;
1295
1296 src_view := src_view || selectClause;
1297
1298 IF (g_log) THEN
1299 edw_gen_view.writelog( newline);
1300 edw_gen_view.writeout(src_view|| newline);
1301 edw_gen_view.writeOutLine('/');
1302
1303 END IF;
1304
1305 src_view := replace (src_view, 'SELECT ', 'SELECT '||newline);
1306 src_view := replace (src_view, ' FROM ', ' FROM '||newline );
1307 src_view := replace (src_view, ' WHERE ', ' WHERE '||newline );
1308 edw_gen_view.createView(src_view, flexdim_viewname2);
1309
1310
1311 IF (g_noOfDepVS > 0) THEN
1312 src_view := ' CREATE OR REPLACE FORCE VIEW '|| flexdim_viewname1 ||' AS ';
1313 src_view := src_view||newline||getIndepVSClause(dim_name, 'HIGHER');
1314 ELSE /* Create a default Upper level view */
1315
1316 src_view := 'CREATE OR REPLACE FORCE VIEW '||flexdim_viewname1 || ' as '||newline||'SELECT ''NA_EDW'' L1_PK, ''NA_EDW'' L1_FK, '||
1317 ''''||edw_gen_view.g_instance||''''||' instance, null actual_value, '||newline||
1318 ' null last_update_date, null description, null creation_date from dual where 1=2';
1319
1320 END IF;
1321
1322 edw_gen_view.createView(src_view, flexdim_viewname1);
1323
1324 IF (g_log) THEN
1325
1326 edw_gen_view.writeout(src_view|| newline);
1327 edw_gen_view.writeOutLine('/');
1328 edw_gen_view.writelog(newline);
1329 edw_gen_view.writelog( 'Completed generateViewForDimension!');
1330 edw_gen_view.writelog( '------------------------------------------------------------');
1331 edw_gen_view.indentEnd;
1332
1333 END IF;
1334 edw_gen_view.writeOutLine('EXIT;');
1335
1336 END;
1337 END EDW_DIM_SV;