1 Package Body EDW_FACT_SV AS
2 /* $Header: EDWVFCTB.pls 120.0 2005/06/01 15:01:55 appldev noship $ */
3 l_directory Varchar2(30) := '/sqlcom/log';
4 g_flex_view_name varchar2(300) := null;
5 g_collection_view_name varchar2(300) := null;
6 g_generated_view_name varchar2(300) := null;
7 g_acct_flex_exists boolean := false;
8
9 newline varchar2(10) := '
10 ';
11 tabb varchar2(40) := ' ';
12
13 cid number;
14
15 TYPE t_view_text_table is table of varchar2(32760) index by binary_integer;
16 g_view_text_table t_view_text_table;
17 g_view_table_num number:=1;
18 g_long_stmt_flag boolean:=false;
19 /* ------------------------------------------------------------------------
20
21 given a fact, return the source view name
22
23 ------------------------------------------------------------------------ */
24
25 Procedure getViewNameForFact(fact_name in varchar2) IS
26 viewname VARCHAR2(300);
27
28 CURSOR c_getViewNamesForFact(p_fact_name in varchar2) IS
29 SELECT flex_view_name, generated_view_name, collection_view_name
30 FROM edw_source_views
31 WHERE upper(object_name) = p_fact_name
32 AND version =edw_gen_view.g_version;
33
34 BEGIN
35 IF (g_log) THEN
36 edw_gen_view.indentBegin;
37 edw_gen_view.writelog(newline);
38 edw_gen_view.writelog('Inside getViewNameForFact');
39 END IF;
40
41 OPEN c_getViewNamesForFact(fact_name);
42 FETCH c_getViewNamesForFact into g_flex_view_name,
43 g_generated_view_name, g_collection_view_name;
44 CLOSE c_getViewNamesForFact ;
45
46 IF (g_generated_view_name IS NULL OR g_flex_view_name IS NULL OR
47 g_collection_view_name IS NULL) THEN
48 edw_gen_view.g_success := false;
49 edw_gen_view.g_error := 'View Names not seeded for ' ||fact_name;
50 raise edw_gen_view.viewgen_exception;
51
52 END IF;
53
54 IF (g_log) THEN
55 edw_gen_view.writelog(' Flex view is:'||g_flex_view_name);
56 edw_gen_view.writelog(' Generated view is:'||g_generated_view_name);
57 edw_gen_view.writelog(' Collection view is:'||g_collection_view_name);
58 edw_gen_view.writelog('Completed getViewNameForFact');
59 edw_gen_view.indentEnd;
60
61 END IF;
62
63
64 END;
65
66
67 Procedure generateViewForFact(fact_name IN VARCHAR2) IS
68
69 srcview VARCHAR2(32760);
70 l_temp_stmt VARCHAR2(32760);
71 l_write_view_counter INTEGER:=0;
72 l_build_stmt_counter integer:=0;
73 v_col DBMS_SQL.VARCHAR2_TABLE;
74 v_colType DBMS_SQL.VARCHAR2_TABLE;
75 v_retCode INTEGER;
76 nColCount INTEGER := 0;
77 nOuterLoopCount INTEGER := 0;
78 nInnerLoopCount INTEGER := 0;
79 attColumns edw_gen_view.tab_att_maps;
80 multiAttList edw_gen_view.tab_multi_att_list;
81 flexColumns edw_gen_view.tab_flex_att_maps;
82 fkColumns edw_gen_view.tab_fact_flex_fk_maps;
83 bColumnMapped BOOLEAN := false;
84 curColumn VARCHAR2(300) := NULL;
85 curColType VARCHAR2(300) := NULL;
86 stmt VARCHAR2(10000) := NULL;
87 decodeClause VARCHAR2(30000) := NULL;
88
89 nLoopCounter INTEGER := 0;
90
91 Cursor C_Skip_Columns(p_object_short_name VARCHAR2) IS
92 select attribute_name, attribute_type
93 from edw_attribute_properties
94 where skip_flag = 'Y'
95 and object_short_name = p_object_short_name;
96
97 Skip_Columns C_Skip_Columns%ROWTYPE;
98
99 Type T_skip_columns_table is table of
100 C_Skip_Columns%rowtype
101 index by binary_integer;
102
103 skip_columns_table T_skip_columns_table;
104 l_count INTEGER := 0;
105
106 BEGIN
107
108 g_flex_view_name := null;
109 g_collection_view_name := null;
110 g_generated_view_name := null;
111 g_acct_flex_exists := false;
112 g_view_table_num :=1;
113 --alter session set global_names=false;
114 edw_misc_util.globalNamesOff;
115
116
117
118 IF (g_log) THEN
119 edw_gen_view.indentBegin;
120 edw_gen_view.writelog('Inside generateViewForFact');
121 END IF;
122
123
124 getViewNameForFact(upper(fact_name));
125
126
127 /* figure out which attributes are mapped */
128 srcview := 'CREATE OR REPLACE FORCE VIEW '||g_collection_view_name||' AS SELECT ';
129
130 nColCount := edw_gen_view.getColumnCountForView(g_collection_view_name);
131
132
133 IF (NOT edw_gen_view.g_success) THEN
134 return;
135 END IF;
136
137 IF (nColCount = 0 ) THEN
138 edw_gen_view.g_success := false;
139 edw_gen_view.g_error := 'Error! No. of columns for ' ||g_collection_view_name||
140 '@'||edw_gen_view.g_source_db_link||' is zero!!!';
141
142 IF (g_log) THEN
143 edw_gen_view.writelog('ERROR...'||edw_gen_view.g_error);
144 END IF;
145
146 raise edw_gen_view.viewgen_exception;
147 RETURN;
148 END IF;
149
150
151 stmt := ' SELECT distinct column_name , data_type FROM all_tab_columns@'||edw_gen_view.g_source_db_link;
152 stmt := stmt||' WHERE table_name = upper('''||g_collection_view_name
153 ||''') AND owner = '''||edw_gen_view.g_apps_schema||'''';
154
155
156 BEGIN
157 cid := DBMS_SQL.open_cursor;
158
159 DBMS_SQL.PARSE(cid, stmt, dbms_sql.native);
160 DBMS_SQL.DEFINE_ARRAY(cid, 1, v_col, nColCount, 1);
161 DBMS_SQL.DEFINE_ARRAY(cid, 2, v_colType, nColCount, 1);
162 v_retCode := DBMS_SQL.EXECUTE_AND_FETCH(cid);
163 DBMS_SQL.COLUMN_VALUE(cid, 1, v_col);
164 DBMS_SQL.COLUMN_VALUE(cid, 2, v_colType);
165 DBMS_SQL.CLOSE_CURSOR(cid);
166
167
168
169 edw_gen_view.getColumnMaps(fact_name, attColumns, multiAttList,
170 flexColumns, fkColumns);
171
172 IF (NOT edw_gen_view.g_success) THEN
173 return;
174 END IF;
175
176
177 -- build skip_columns_table here
178 l_count := 1;
179 open C_Skip_Columns(fact_name);
180 loop
181 fetch C_Skip_Columns into skip_columns;
182 exit when C_Skip_Columns%NOTFOUND;
183 skip_columns_table (l_count).attribute_name := skip_columns.attribute_name;
184 skip_columns_table (l_count).attribute_type := skip_columns.attribute_type;
185 l_count := l_count + 1;
186 end loop;
187
188 WHILE nOuterLoopCount < nColCount LOOP
189 nOuterLoopCount := nOuterLoopCount + 1;
190 curColumn := v_col(nOuterLoopCount);
191 curColType := v_colType(nOuterLoopCount);
192
193 /* check if current column is a skipped column */
194 IF (curColumn LIKE 'USER_ATTRIBUTE%' OR curColumn LIKE 'USER_FK%'
195 OR curColumn like 'USER_MEASURE%' OR curColumn LIKE 'GL_ACCT%_FK%') THEN
196 null;
197 ELSE
198 nInnerLoopCount := 1;
199 IF (skip_columns_table.count > 0) THEN
200
201 LOOP
202 IF (skip_columns_table(nInnerLoopCount).attribute_name = curColumn
203 AND skip_columns_table(nInnerLoopCount).attribute_type = 'A'
204 )
205 OR (skip_columns_table(nInnerLoopCount).attribute_name = curColumn || '_KEY'
206 AND skip_columns_table(nInnerLoopCount).attribute_type = 'F'
207 ) THEN
208
209 if (skip_columns_table(nInnerLoopCount).attribute_type = 'F') then
210
211 l_temp_stmt := ',' || newline || ' ''NA_EDW'' ' || curColumn ;
212 elsif (curColType = 'NUMBER') then
213 l_temp_stmt := ',' || newline || ' to_number(null) ' || curColumn;
214
215 elsif (skip_columns_table(nInnerLoopCount).attribute_type = 'A') then
216 l_temp_stmt := ',' || newline || ' null ' || curColumn;
217
218 end if;
219
220 if (length(srcview)+length(l_temp_stmt)> 32760) then
221 g_view_text_table(g_view_table_num):= srcview;
222 srcview:=null;
223 if g_log then
224 edw_gen_view.writelog('View text is longer than 32760.');
225 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
226 end if;
227 g_view_table_num:= g_view_table_num+1;
228 g_long_stmt_flag:=true;
229 end if;
230 srcview := srcview || l_temp_stmt;
231
232 bColumnMapped := true; -- to skip the part after nomatch
233 goto nomatch;
234 end if;
235 EXIT WHEN nInnerLoopCount = skip_columns_table.last;
236 nInnerLoopCount := nInnerLoopCount + 1;
237 end loop;
238 end if;
239 end if;
240
241 IF nOuterLoopCount >1 THEN
242 l_temp_stmt:= ', '||newline;
243
244 if (length(srcview)+length(l_temp_stmt)> 32760) then
245 g_view_text_table(g_view_table_num):= srcview;
246 srcview:=null;
247 if g_log then
248 edw_gen_view.writelog('View text is longer than 32760.');
249 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
250 end if;
251 g_view_table_num:= g_view_table_num+1;
252 g_long_stmt_flag:=true;
253 end if;
254 srcview := srcview || l_temp_stmt;
255 END IF;
256 IF (g_log) THEN
257 edw_gen_view.writelog('Processing column ' || curColumn);
258 END IF;
259 /* need to process only if its a user attribute or user fk */
260
261 IF (curColumn LIKE 'USER_ATTRIBUTE%' OR curColumn LIKE 'USER_FK%'
262 OR curColumn like 'USER_MEASURE%' OR curColumn LIKE 'GL_ACCT%_FK%') THEN
263 null;
264 ELSE
265
266 goto nomatch;
267 END IF;
268
269 /* see if columns have been mapped to a source attribute */
270 nInnerLoopCount := 1;
271 IF (attColumns.count > 0) THEN
272
273
274 LOOP
275
276 IF (attColumns(nInnerLoopCount).attribute_name = curColumn) THEN
277 bColumnMapped := true;
278 IF (g_log) THEN
279 edw_gen_view.writelog(' Attributes mapped to a single attribute exist');
280 edw_gen_view.writelog(' '||curColumn||' is of datatype '||attColumns(nInnerLoopCount).datatype);
281 END IF;
282 IF (attColumns(nInnerLoopCount).datatype <> 'DATE') THEN
283 IF (g_log) THEN
284 edw_gen_view.writelog(' not a date column ');
285 END IF;
286
287
288 l_temp_stmt:= 'a.'||attColumns(nInnerLoopCount).source_attribute||' '||curColumn ;
289 IF (g_log) THEN
290 edw_gen_view.writelog(' '||attColumns(nInnerLoopCount).source_attribute||' '||curColumn ||newline||newline);
291 END IF;
292 if (length(srcview)+length(l_temp_stmt)> 32760) then
293 g_view_text_table(g_view_table_num):= srcview;
294 srcview:=null;
295 if g_log then
296 edw_gen_view.writelog('View text is longer than 32760.');
297 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
298 end if;
299 g_view_table_num:= g_view_table_num+1;
300 g_long_stmt_flag:=true;
301 end if;
302 srcview := srcview || l_temp_stmt;
303 ELSE
304 IF (g_log) THEN
305 edw_gen_view.writelog( 'and so doing a to_char');
306 END IF;
307 l_temp_stmt :=' to_char(a.'||attColumns(nInnerLoopCount).source_attribute||' , ''mm/dd/yyyy hh24:mi:ss'') '||' '||curColumn ;
308 if (length(srcview)+length(l_temp_stmt)> 32760) then
309 g_view_text_table(g_view_table_num):= srcview;
310 srcview:=null;
311 if g_log then
312 edw_gen_view.writelog('View text is longer than 32760.');
313 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
314 end if;
315 g_view_table_num:= g_view_table_num+1;
316 g_long_stmt_flag:=true;
317 end if;
318 srcview := srcview || l_temp_stmt;
319 END IF;
320 goto nomatch;
321
322 END IF;
323 EXIT WHEN nInnerLoopCount = attColumns.last;
324 nInnerLoopCount := nInnerLoopCount + 1;
325 END LOOP;
326 END IF;
327
328 nInnerLoopCount := 1;
329 IF (multiAttList.count > 0) THEN
330 LOOP
331
332 IF (multiAttList(nInnerLoopCount).attribute_name = curColumn) THEN
333 bColumnMapped := true;
334 l_temp_stmt := edw_gen_view.getNvlClause(fact_name, null,
335 edw_gen_view.g_instance , curColumn)||' '||curColumn ;
336 IF (g_log) THEN
337 edw_gen_view.writelog('getNvlClause returned : '||l_temp_stmt);
338 END IF;
339 if (length(srcview)+length(l_temp_stmt)> 32760) then
340 g_view_text_table(g_view_table_num):= srcview;
341 srcview:=null;
342 if g_log then
343 edw_gen_view.writelog('View text is longer than 32760.');
344 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
345 end if;
346 g_view_table_num:= g_view_table_num+1;
347 g_long_stmt_flag:=true;
348 end if;
349 srcview := srcview || l_temp_stmt;
350 goto nomatch;
351 END IF;
352 IF (NOT edw_gen_view.g_success) THEN
353 return;
354 END IF;
355
356 EXIT WHEN nInnerLoopCount = multiAttList.last;
357 nInnerLoopCount := nInnerLoopCount + 1;
358 END LOOP;
359 END IF;
360
361 /* see if columns have been mapped to a source flex field */
362
363 nInnerLoopCount := 1;
364 IF (flexColumns.count > 0) THEN
365
366 LOOP
367
368 IF (flexColumns(nInnerLoopCount).attribute_name = curColumn) THEN
369 bColumnMapped := true;
370 -- decode flex
371 decodeClause := edw_gen_view.getDecodeClauseForFlexCol(
372 g_flex_view_name, curColumn,
373 flexColumns(nInnerLoopCount).id_flex_code,
374 flexColumns(nInnerLoopCount).flex_field_type);
375 l_temp_stmt := decodeClause||' '||curColumn;
376 if (length(srcview)+length(l_temp_stmt)> 32760) then
377 g_view_text_table(g_view_table_num):= srcview;
378 srcview:=null;
379 if g_log then
380 edw_gen_view.writelog('View text is longer than 32760.');
381 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
382 end if;
383 g_view_table_num:= g_view_table_num+1;
384 g_long_stmt_flag:=true;
385 end if;
386 srcview := srcview || l_temp_stmt;
387 goto nomatch;
388 END IF;
389 IF (NOT edw_gen_view.g_success) THEN
390 return;
391 END IF;
392
393 EXIT WHEN nInnerLoopCount= flexColumns.last;
394 nInnerLoopCount := nInnerLoopCount + 1;
395 END LOOP;
396 END IF;
397
401 IF (fkColumns.count > 0) THEN
398 /* see if fk columns have been mapped to a flex dimension */
399
400 nInnerLoopCount := 1;
402
403 LOOP
404 IF (fkColumns(nInnerLoopCount).fk_physical_name = curColumn OR fkColumns(nInnerLoopCount).fk_physical_name = curColumn||'_KEY') THEN
405 bColumnMapped := true;
406 decodeClause := getDecodeClauseForFlexFK(fact_name, curColumn);
407 l_temp_stmt:= decodeClause||' '||curColumn;
408 if (length(srcview)+length(l_temp_stmt)> 32760) then
409 g_view_text_table(g_view_table_num):= srcview;
410 srcview:=null;
411 if g_log then
412 edw_gen_view.writelog('View text is longer than 32760.');
413 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
414 end if;
415 g_view_table_num:= g_view_table_num+1;
416 g_long_stmt_flag:=true;
417 end if;
418 srcview := srcview || l_temp_stmt;
419 goto nomatch;
420 END IF;
421
422 IF (NOT edw_gen_view.g_success) THEN
423 return;
424 END IF;
425
426 EXIT WHEN nInnerLoopCount= fkColumns.last;
427 nInnerLoopCount := nInnerLoopCount + 1;
428 END LOOP;
429 END IF;
430 <<nomatch>>
431 IF (bColumnMapped = false) THEN
432 IF (curColumn like 'USER_FK%' or curColumn like 'GL_ACCT%FK%') THEN
433 l_temp_stmt:='''NA_EDW'''||' '||v_col(nOuterLoopCount);
434 if (length(srcview)+length(l_temp_stmt)> 32760) then
435 g_view_text_table(g_view_table_num):= srcview;
436 srcview:=null;
437 if g_log then
438 edw_gen_view.writelog('View text is longer than 32760.');
439 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
440 end if;
441 g_view_table_num:= g_view_table_num+1;
442 g_long_stmt_flag:=true;
443 end if;
444 srcview := srcview || l_temp_stmt;
445 END IF;
446 IF (curColumn like 'USER_ATTRIBUTE%' or curColumn like 'USER_MEASURE%') THEN
447 l_temp_stmt:='null '||v_col(nOuterLoopCount);
448 if (length(srcview)+length(l_temp_stmt)> 32760) then
449 g_view_text_table(g_view_table_num):= srcview;
450 srcview:=null;
451 if g_log then
452 edw_gen_view.writelog('View text is longer than 32760.');
453 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
454 end if;
455 g_view_table_num:= g_view_table_num+1;
456 g_long_stmt_flag:=true;
457 end if;
458 srcview := srcview || l_temp_stmt;
459 END IF;
460
461 IF (curColumn NOT like 'USER_ATTRIBUTE%' AND curColumn NOT like 'GL_ACCT%FK%' AND curColumn NOT like 'USER_MEASURE%' AND curColumn NOT like 'USER_FK%' ) THEN
462 l_temp_stmt:=' a.'||v_col(nOuterLoopCount);
463 if (length(srcview)+length(l_temp_stmt)> 32760) then
464 g_view_text_table(g_view_table_num):= srcview;
465 srcview:=null;
466 if g_log then
467 edw_gen_view.writelog('View text is longer than 32760.');
468 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
469 end if;
470 g_view_table_num:= g_view_table_num+1;
471 g_long_stmt_flag:=true;
472 end if;
473 srcview := srcview || l_temp_stmt;
474 END IF;
475 END IF;
476 bColumnMapped := false;
477 null;
478 END LOOP;
479
480
481 l_temp_stmt:= newline||' FROM '||g_generated_view_name||' a';
482 if (length(srcview)+length(l_temp_stmt)> 32760) then
483 g_view_text_table(g_view_table_num):= srcview;
484 srcview:=null;
485 if g_log then
486 edw_gen_view.writelog('View text is longer than 32760.');
487 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
488 end if;
489 g_view_table_num:= g_view_table_num+1;
490 g_long_stmt_flag:=true;
491 end if;
492 srcview := srcview || l_temp_stmt;
493
494
495 IF (g_acct_flex_exists) THEN
496
497 l_temp_stmt:= ', '||newline||' edw_local_set_of_books b, edw_local_equi_set_of_books c, edw_local_set_of_books d '|| newline||
498 ' where a.set_of_books_id = b.set_of_books_id (+) '||newline||
499 /*' and b.instance(+) = '||''''|| edw_gen_view.g_instance ||''''||newline||*/
500 ' and b.edw_set_of_books_id = c.edw_set_of_books_id (+) '||
504 g_view_text_table(g_view_table_num):= srcview;
501 ' and c.equi_set_of_books_id = d.edw_set_of_books_id (+)';
502
503 if (length(srcview)+length(l_temp_stmt)> 32760) then
505 srcview:=null;
506 if g_log then
507 edw_gen_view.writelog('View text is longer than 32760.');
508 edw_gen_view.writelog('View Text stored in the '|| g_view_table_num||'th element of the view text table.');
509 end if;
510 g_long_stmt_flag:=true;
511 end if;
512 srcview := srcview || l_temp_stmt;
513
514 END IF;
515
516 if g_log then
517
518 if (g_long_stmt_flag) then
519 for l_write_view_counter in 1 .. g_view_text_table.count loop
520 IF(g_log) THEN
521 edw_gen_view.writelog( g_view_text_table(l_write_view_counter));
522 edw_gen_view.writeoutline('/* Writing Part '||l_write_view_counter||' of the view */');
523 edw_gen_view.writeout( g_view_text_table(l_write_view_counter));
524 END IF;
525 end loop;
526 end if;
527
528 IF (g_log) THEN
529 edw_gen_view.writeoutline('/* Writing remaining piece of view */');
530 edw_gen_view.writeOutline(srcview);
531 edw_gen_view.writelog(newline||newline);
532 edw_gen_view.writelog('View formation complete.');
533 --edw_gen_view.writelog(srcview);
534 edw_gen_view.writeOutLine('/');
535 edw_gen_view.writeOutLine('EXIT;');
536 END IF;
537 end if;
538
539
540
541 IF (NOT edw_gen_view.g_success) THEN
542 return;
543 END IF;
544
545 if (not g_long_stmt_flag) then
546 IF (g_log) THEN
547 edw_gen_view.writelog('Short text. Call edw_gen_view.createView');
548 END IF;
549 edw_gen_view.createView(srcview, g_collection_view_name);
550 else
551 IF (g_log) THEN
552 edw_gen_view.writelog('Long text. Call edw_gen_view.createLongView');
553 END IF;
554
555 /*---------------------------------------------------------------------------
556 cut the view text into 256 chars chunks and call edw_gen_view.buildViewStmt
557 ----------------------------------------------------------------------------*/
558 l_build_stmt_counter:=0;
559 for l_write_view_counter in 1 .. g_view_text_table.count loop
560 l_temp_stmt:= g_view_text_table(l_write_view_counter);
561
562 while (length(l_temp_stmt) >256 ) loop
563 l_build_stmt_counter:= l_build_stmt_counter +1;
564 edw_gen_view.BuildViewStmt(substr(l_temp_stmt,1,256), l_build_stmt_counter);
565 l_temp_stmt:= substr(l_temp_stmt,257);
566 end loop;
567 l_build_stmt_counter:= l_build_stmt_counter +1;
568 edw_gen_view.BuildViewStmt(l_temp_stmt, l_build_stmt_counter);
569 end loop;
570 l_temp_stmt := srcview;
571 while (length(l_temp_stmt) >256 ) loop
572 l_build_stmt_counter:= l_build_stmt_counter +1;
573 edw_gen_view.BuildViewStmt(substr(l_temp_stmt,1,256), l_build_stmt_counter);
574 l_temp_stmt:= substr(l_temp_stmt,257);
575 end loop;
576 l_build_stmt_counter:= l_build_stmt_counter +1;
577 edw_gen_view.BuildViewStmt(l_temp_stmt, l_build_stmt_counter);
578
579 edw_gen_view.createLongView(g_collection_view_name, 1, l_build_stmt_counter);
580 end if;
581
582 IF (NOT edw_gen_view.g_success) THEN
583
584 return;
585 END IF;
586
587 IF (g_log) THEN
588 edw_gen_view.writelog('Completed generateViewForFact');
589 edw_gen_view.indentEnd;
590 END IF;
591 END;
592
593 END;
594
595
596 /* ------------------------------------------------------------------------
597
598 given a fact FK that is mapped, return the decode clause for this fk
599
600 ------------------------------------------------------------------------ */
601
602 FUNCTION getDecodeClauseForFlexFK( pFactName IN VARCHAR2, pAttributeName IN VARCHAR2) RETURN VARCHAR2 IS
603 sPrefix VARCHAR2(100) := NULL;
604 sDecodeClause VARCHAR2(30000) := NULL;
605 nCount NUMBER := 1;
606 l_gen_seg_name VARCHAR2(30):= NULL;
607 l_parent_seg_name VARCHAR2(30):= NULL;
608 l_parent_struct_num NUMBER := 0;
609 l_parent_struct_name VARCHAR2(30):= NULL;
610 cid NUMBER := 0;
611 l_dummy NUMBER := 0;
612
613 CURSOR c IS SELECT fk_physical_name, b.dimension_short_name dimension_short_name,
614 a.value_set_id value_set_id, segment_name, flex_field_type,
615 structure_num , value_set_type, instance_code, id_flex_code, structure_name, parent_value_set_id
616 FROM edw_flex_seg_mappings a, edw_fact_flex_fk_maps b
617 WHERE b.fact_short_name = pFactName
618 AND b.enabled_flag = 'Y'
619 AND b.dimension_short_name = a.dimension_short_name
620 AND a.instance_code = edw_gen_view.g_instance
621 AND (b.fk_physical_name = pAttributeName
622 or b.fk_physical_name = pAttributeName||'_KEY')
623 AND NOT EXISTS( select 1 from edw_flex_seg_mappings c
624 where c.parent_value_set_id = a.value_set_id
625 and a.instance_code = c.instance_code
626 and a.dimension_short_name = c.dimension_short_name
630 TYPE CurTyp IS REF CURSOR;
627 and a.structure_num = c.structure_num);
628 cRec c%ROWTYPE;
629 stmt varchar2(200);
631 cv CurTyp;
632
633 l_structure_col varchar2(100) := null;
634 dependantVSExists boolean := false;
635 sDecodePrefix varchar2(100) := null;
636 tempvar varchar2(300);
637 BEGIN
638
639 IF (g_log) THEN
640 edw_gen_view.indentBegin;
641
642 edw_gen_view.writelog('Inside getDecodeClauseForFlexFK');
643 edw_gen_view.writelog(' Parameter pFactname:'||pFactName);
644 edw_gen_view.writelog(' Parameter pAttributeName:'||pAttributeName);
645 END IF;
646
647 OPEN c;
648 FETCH c INTO cRec;
649
650 sPrefix := edw_gen_view.getFlexPrefix(g_flex_view_name, cRec.id_flex_code);
651 sDecodePrefix := ' DECODE('||sPrefix||'_CONTEXT, '||newline||' ';
652 --sDecodeClause := ' DECODE('||sPrefix||'_CONTEXT, '||newline||' ';
653
654 LOOP
655
656 EXIT WHEN c%NOTFOUND;
657 IF(nCount > 1) THEN
658 sDecodeClause := sDecodeClause ||','||newline||' ';
659 END IF;
660
661 ------------------------------------------------
662 l_gen_seg_name := cRec.segment_name;
663
664 IF (g_log) THEN
665 edw_gen_view.writelog('l_gen_seg_name is :'|| l_gen_seg_name);
666 edw_gen_view.writelog('calling formSegmentName');
667 END IF;
668
669 l_gen_seg_name := edw_gen_view.formSegmentName(sPrefix, cRec.segment_name, cRec.structure_num, pFactName, cRec.flex_field_type);
670
671 IF (upper(cRec.value_set_type) = 'D' ) THEN /* Dependant Value Set, need parent segment name also */
672 stmt := ' SELECT segment_name, structure_num FROM edw_flex_seg_mappings'||
673 ' WHERE value_set_id = :s1 AND ';
674
675 IF (cRec.flex_field_type = 'D') THEN /* Descr Flex */
676 stmt := stmt ||' structure_name = :s2 ';
677 open cv for stmt using cRec.parent_value_set_id, cRec.structure_name ;
678 fetch cv into l_parent_seg_name, l_parent_struct_name ;
679 close cv;
680 ELSIF /* we need to consider accounting flex field as well (bug 2245373)*/
681 (cRec.flex_field_type = 'K' OR cRec.flex_field_type = 'A') THEN /* Key Flex */
682 stmt := stmt ||' structure_num = :s2 ';
683 open cv for stmt using cRec.parent_value_set_id, cRec.structure_num;
684 fetch cv into l_parent_seg_name, l_parent_struct_num ;
685 close cv;
686 END IF;
687 l_parent_seg_name := edw_gen_view.formSegmentName(sPrefix, l_parent_seg_name, cRec.structure_num, pFactName, cRec.flex_field_type);
688
689 IF (g_log) THEN
690 edw_gen_view.writelog('Parent segment is :'|| l_parent_seg_name);
691 END IF;
692 END IF;
693
694 -------------------------------------------------
695 IF (g_log) THEN
696 edw_gen_view.writelog('dimension name is :'|| cRec.dimension_short_name);
697 END IF;
698
699
700 IF (cRec.dimension_short_name LIKE 'EDW_GL_ACCT%_M' ) THEN /* A/c Flex dim */
701 /* Different FK format, to support Phase 1 */
702
703 g_acct_flex_exists := true;
704 sDecodeClause :=sDecodeClause ||''''||cRec.structure_num||''','||
705 newline||' DECODE(b.set_of_books_id, null, ''NA_EDW'', '||
706 newline||' DECODE(c.edw_set_of_books_id, null, '||
707 newline||' DECODE("'||l_gen_seg_name||'", null, ''NA_EDW'','||
708 newline||' "'||l_gen_seg_name||'"';
709 sDecodeClause := sDecodeClause||'||'||
710 ''''||'-'||''''||'|| a.set_of_books_id ';
711 sDecodeClause := sDecodeClause||'||'||''''||'-'||''''||'||'
712 ||''''||edw_gen_view.g_instance||''''||'),';
713
714 sDecodeClause := sDecodeClause||
715 newline||' DECODE("'||l_gen_seg_name||'", null, ''NA_EDW'','||
716 newline||' "'||l_gen_seg_name||'"';
717 sDecodeClause := sDecodeClause||'||'||
718 ''''||'-'||''''||'|| d.set_of_books_id ';
719
720 sDecodeClause := sDecodeClause||'||'||''''||'-'||''''||'||'
721 ||'d.instance)'||newline||
722 ' )'||
723 ' )';
724
725
726 ELSE
727 IF (g_log) THEN
728 edw_gen_view.writelog('Mapped to a non a/c flex dimension ');
729 edw_gen_view.writelog('Structure name is : '||cRec.structure_name);
730 END IF;
731
732 IF (cRec.flex_field_type = 'D') THEN /* Descr Flex */
733 IF (g_log) THEN
734 edw_gen_view.writelog('struct is : '||cRec.structure_name);
735 END IF;
736 tempvar := replace(cRec.structure_name, '''', '''''');
737 IF (g_Log) THEN
738 edw_gen_view.writelog('struct after replace is : '||tempvar);
739 END IF;
740
741 IF (cRec.value_set_type = 'D') THEN /* Dependant VS, so different FK Structure */
742 sDecodeClause := sDecodeClause||'''';
743
744 sDecodeClause := sDecodeClause||tempvar||''''||
745 ', DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
746 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
747 '"'||l_parent_seg_name||'"'||'||'':''||"'||l_gen_seg_name||'"'||')';
748 dependantVSExists := true;
749 ELSE
750 sDecodeClause := sDecodeClause||''''||tempvar||''''||
754 END IF; /* value_set_type = 'D' */
751 ', DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
752 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
753 '"'||l_gen_seg_name||'"'||')';
755 ELSE
756 IF (cRec.value_set_type = 'D') THEN /* Dependant VS, so different FK Structure */
757 sDecodeClause := sDecodeClause||''''||cRec.structure_num||''''||
758 ', DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
759 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
760 '"'||l_parent_seg_name||'"'||'||'':''||"'||l_gen_seg_name||'"'||')';
761 dependantVSExists := true;
762 ELSE
763 sDecodeClause := sDecodeClause||''''||cRec.structure_num||''''||
764 ', DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
765 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
766 '"'||l_gen_seg_name||'"'||')';
767 END IF; /* value_set_type = 'D' */
768 END IF; /* cRec.flex_field_type = 'D' */
769 END IF; /* cRec.dimension_short_name LIKE 'EDW_GL_ACCT%_M' */
770 nCount := nCount + 1;
771 FETCH c INTO cRec;
772 END LOOP;
773 CLOSE c;
774
775 sDecodeClause := sDecodePrefix ||sDecodeClause||', ''NA_EDW'')';
776
777 IF (g_log) THEN
778 edw_gen_view.writelog('decode clause is : '||sDecodeClause);
779 END IF;
780
781 IF (cRec.flex_field_type = 'D') THEN
782 l_structure_col := edw_gen_view.getContextColForFlex(cRec.id_flex_code, 'D');
783 IF (g_log) THEN
784 edw_gen_view.writelog('Descr. flexfield structure column is : '||l_structure_col);
785 END IF;
786 l_structure_col:= replace(l_structure_col, '''', '''''') ;
787 IF (g_log) THEN
788 edw_gen_view.writelog('structure is : '||l_structure_col);
789 END IF;
790
791 IF ( upper(cRec.structure_name)='GLOBAL DATA ELEMENTS' OR l_structure_col IS NULL) THEN
792 IF (dependantVSExists ) THEN
793 sDecodeClause :='DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
794 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
795 '"'||l_parent_seg_name||'"'||'||'':''||"'||l_gen_seg_name||'"'||')';
796 ELSE
797 sDecodeClause := 'DECODE("'||l_gen_seg_name||'", null, ''NA_EDW'', '||
798 ''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
799 '"'||l_gen_seg_name||'")';
800 END IF;
801 END IF;
802 ELSE /* Key Flex */
803 l_structure_col := edw_gen_view.getContextColForFlex(cRec.id_flex_code, 'K');
804 IF (g_log) THEN
805 edw_gen_view.writelog('Key flexfield structure column is : '||l_structure_col);
806 END IF;
807
808 IF (l_structure_col IS NULL) THEN
809 IF (dependantVSExists ) THEN
810 sDecodeClause := 'DECODE("'||l_gen_seg_name||'",null, ''NA_EDW'','
811 ||''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
812 '"'||l_parent_seg_name||'"'||'||'':''||"'||l_gen_seg_name||'"'||')';
813 ELSE
814 sDecodeClause := 'DECODE("'||l_gen_seg_name||'", null, ''NA_EDW'', '||
815 ''''||cRec.instance_code||''''||'||'||''':'||cRec.value_set_id||':'''||'||'||
816 '"'||l_gen_seg_name||'")';
817 END IF;
818 END IF;
819 END IF;
820
821 /* DBMS_SQL.CLOSE_CURSOR(cid); */
822
823 IF (g_log) THEN
824 edw_gen_view.writelog('Completed getdecodeclauseforflexfk, returning '||sDecodeClause||newline||newline);
825 edw_gen_view.indentEnd;
826
827 END IF;
828
829 RETURN sDecodeClause;
830
831 END;
832
833
834
835
836 END EDW_FACT_SV;
[Home] [Help]