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.

APPS.EDW_DIM_SV dependencies on EDW_GEN_VIEW

Line 33: AND instance_code = edw_gen_view.g_instance

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

Line 38: edw_gen_view.writelog('Inside getCountForVSTypes');

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

Line 39: edw_gen_view.writelog('Parameter dim_name:'||dim_name);

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

Line 56: edw_gen_view.writelog('No. of table validated VS :'||g_noOfTableVS);

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;

Line 57: edw_gen_view.writelog('No. of dependant VS :'||g_noOfDepVS);

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:

Line 58: edw_gen_view.writelog('No. of independant VS :'||g_noOfIndepVS);

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;

Line 59: edw_gen_view.writelog('No. of none VS :'||g_noOfNoneVS);

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;

Line 65: edw_gen_view.writelog('Completed getCountForVSTypes');

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;

Line 66: edw_gen_view.indentEnd;

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:

Line 81: edw_gen_view.indentBegin;

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;

Line 82: edw_gen_view.writelog(newline);

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:

Line 83: edw_gen_view.writelog('Inside getViewnameForFlexdim');

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);

Line 84: edw_gen_view.writelog('Parameter dim_name:'||dim_name);

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. */

Line 96: edw_gen_view.writelog('flexdim_viewname1 is '||flexdim_viewname1);

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:

Line 97: edw_gen_view.writelog('flexdim_viewname2 is '||flexdim_viewname2);

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;

Line 99: edw_gen_view.writelog('Completed getViewnameForFlexdim');

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:

Line 101: edw_gen_view.indentEnd;

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;

Line 105: edw_gen_view.g_success := false;

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:

Line 106: edw_gen_view.g_error := 'View Names not seeded for ' ||dim_name;

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;

Line 107: raise edw_gen_view.viewgen_exception;

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:

Line 129: edw_gen_view.indentBegin;

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;

Line 130: edw_gen_view.writelog('Inside checkColumnsPresent for table :'||p_table||':');

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;

Line 148: edw_gen_view.writelog(l_table||' is a synonym!!! Getting actual table name... ');

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;

Line 156: edw_gen_view.writelog('Completed checkColumnsPresent');

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;

Line 157: edw_gen_view.indentEnd;

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:

Line 162: edw_gen_view.writelog('Table name is :'||l_table||':');

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

Line 166: stmt := 'SELECT distinct column_name FROM all_tab_columns@'||edw_gen_view.g_source_db_link

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:

Line 171: edw_gen_view.writelog('Querying for columns : '||stmt);

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);

Line 198: edw_gen_view.writelog('Completed checkColumnsPresent');

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;

Line 199: edw_gen_view.indentEnd;

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:

Line 231: edw_gen_view.indentBegin;

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:

Line 232: edw_gen_view.writelog('Inside parseTable for '||p_tables||','||p_value_column);

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:

Line 247: edw_gen_view.writelog('l_table is '||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);

Line 252: edw_gen_view.writelog('Alias is :'||p_alias||':');

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:

Line 257: edw_gen_view.writelog('l_table is :'||l_table||':');

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

Line 275: edw_gen_view.writelog('Multiple Tables... ');

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

Line 286: edw_gen_view.writelog('l_buffer is :'||l_buffer||':');

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||':');

Line 290: edw_gen_view.writelog('l_table is :'||l_table||':');

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:

Line 293: edw_gen_view.writelog('Alias exists');

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:

Line 298: edw_gen_view.writelog('Name :'||l_row(l_counter).name||':');

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');

Line 299: edw_gen_view.writelog('Alias :'||l_row(l_counter).alias||':');

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);

Line 302: edw_gen_view.writelog('No alias for the table');

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:

Line 304: edw_gen_view.writelog('Name :'||l_row(l_counter).name||':');

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

Line 310: edw_gen_view.writelog('Alias matched...so table is '||p_final_tab);

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

Line 316: edw_gen_view.writelog('No value alias, returning first table '||p_final_tab);

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:

Line 327: edw_gen_view.writelog('Trimmed p_tab is :'||p_final_tab||':');

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;

Line 330: edw_gen_view.writelog('Completed parseTable 2');

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;

Line 331: edw_gen_view.indentEnd;

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:

Line 379: edw_gen_view.indentBegin;

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:

Line 380: edw_gen_view.writelog('Inside getTableValClause');

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,

Line 386: ' fnd_flex_validation_tables@' ||edw_gen_view.g_source_db_link

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);

Line 390: edw_gen_view.writelog('Querying table name : '||stmt1||' with :d1='||dim_name);

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,

Line 396: edw_gen_view.writelog('Table name IS :' ||l_flex_table||' Value Set ID : '||l_vsid||' Value column = '||l_value_column_name||

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;

Line 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);

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:

Line 437: stmt2:= stmt2||edw_gen_view.g_instance||'''''''||'' instance, ';

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');

Line 441: edw_gen_view.writelog('Last Update date exists');

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

Line 454: edw_gen_view.writelog('Creation date exists');

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
456: stmt2 := stmt2||' '||l_table_alias||'.creation_date ';
457: ELSE
458: stmt2 := stmt2||' '||l_final_table||'.creation_date ';

Line 471: edw_gen_view.writelog('Description exists');

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 ';

Line 485: stmt2 := stmt2|| ' fnd_flex_validation_tables@' ||edw_gen_view.g_source_db_link ||

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

Line 490: edw_gen_view.writelog( ' Query IS : '||stmt2);

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:

Line 497: edw_gen_view.writelog( ' l_Count IS : '||l_count);

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

Line 508: edw_gen_view.writelog( ' View clause IS : '||clause);

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;

Line 512: edw_gen_view.g_where_clause.delete;

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');

Line 514: IF (not edw_gen_view.checkWhereClause(l_vsid, edw_gen_view.g_source_db_link)) THEN

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);

Line 515: edw_gen_view.g_success := FALSE;

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);

Line 521: edw_gen_view.g_error := fnd_message.get;

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:

Line 522: raise edw_gen_view.viewgen_exception;

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

Line 526: IF (edw_gen_view.g_where_clause.count > 0) THEN

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);

Line 529: edw_gen_view.writelog('Count IS : '||l_where_count);

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;

Line 530: clause := clause || edw_gen_view.g_where_clause(l_where_count);

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;

Line 531: edw_gen_view.writelog('Select Clause IS : '|| clause);

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;

Line 532: exit when l_where_count = edw_gen_view.g_where_clause.last;

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:

Line 545: edw_gen_view.writelog('Completed getTableValClause');

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;

Line 546: edw_gen_view.indentEnd;

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:

Line 563: and instance_code = edw_gen_view.g_instance

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

Line 578: AND parent.instance_code = edw_gen_view.g_instance

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

Line 579: AND child.instance_code = edw_gen_view.g_instance

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;

Line 588: edw_gen_view.indentBegin;

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;

Line 590: edw_gen_view.writelog('Inside getIndepVSClause for '||p_level);

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
594:

Line 595: src_view := 'SELECT '||''''||edw_gen_view.g_instance||':''||'||'flex_value_set_id||'':''||flex_value L2_PK, flex_value actual_value, ' ||newline||

591: END IF;
592: l_count := 0;
593: IF (p_level = 'LOWER') THEN
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, '||
597: newline||' creation_date, description, ''NA_EDW'' L2_FK '||
598: newline||' FROM fnd_flex_values_vl '||
599: newline||' WHERE flex_value_set_id IN ( ';

Line 596: ' '''||edw_gen_view.g_instance||''''||' instance, last_update_date, '||

592: l_count := 0;
593: IF (p_level = 'LOWER') THEN
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, '||
597: newline||' creation_date, description, ''NA_EDW'' L2_FK '||
598: newline||' FROM fnd_flex_values_vl '||
599: newline||' WHERE flex_value_set_id IN ( ';
600:

Line 612: src_view := 'SELECT '||''''||edw_gen_view.g_instance||':''||'||'a.flex_value_set_id||'':''||a.flex_value L1_PK, ' ||newline||

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 ' ||

Line 614: ''''||edw_gen_view.g_instance||''''||' instance, '||newline||

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

Line 630: edw_gen_view.writelog('Independant Value Set Clause is : '||src_view);

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:

Line 631: edw_gen_view.writelog('Completed getIndepVSClause');

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;

Line 632: edw_gen_view.indentEnd;

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;

Line 647: and a.instance_code = edw_gen_view.g_instance

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:

Line 653: edw_gen_view.indentBegin;

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;

Line 655: edw_gen_view.writelog('Inside getDepVSClause ');

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;

Line 656: edw_gen_view.writelog('Parameter p_dim_name:'||p_dim_name);

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,';

Line 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,';

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 ';

Line 661: src_view := src_view||''''||edw_gen_view.g_instance||''''||' instance, '||

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

Line 663: newline||' childvl.description, '||''''||edw_gen_view.g_instance||':''||'||' parentvl.flex_value_set_id '|| '||'':''||'||' parentvl.flex_value L2_FK ';

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

Line 685: edw_gen_view.writelog('Dependant VS clause is :' ||final_view);

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;

Line 686: edw_gen_view.writelog('Completed getDepVSClause'||newline);

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:

Line 688: edw_gen_view.indentEnd;

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:

Line 729: edw_gen_view.indentBegin;

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;

Line 730: edw_gen_view.writelog( 'inside getNoneVSClause');

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:

Line 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 '||

731: END IF;
732:
733: select1 := 'SELECT b.APPLICATION_TABLE_NAME , a.structure_num, a.STRUCTURE_NAME,'||newline;
734:
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 ';
738:
739: whereDescFlex := ' b.CONTEXT_COLUMN_NAME, ''D'' FROM edw_flex_seg_mappings a, fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||

Line 739: whereDescFlex := ' b.CONTEXT_COLUMN_NAME, ''D'' FROM edw_flex_seg_mappings a, fnd_descriptive_flexs_vl@'||edw_gen_view.g_source_db_link||

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 ';
738:
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:

Line 747: open cv for finalStmt using edw_gen_view.g_instance, p_dim_name,

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

Line 748: edw_gen_view.g_instance, p_dim_name;

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 ;

Line 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 '||

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:

Line 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 '||

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:

Line 814: edw_gen_view.writelog( newline||'Final SQL to query on None Value Set : '||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:

Line 815: edw_gen_view.writelog(finalStmt);

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,

Line 819: open ctable for finalStmt using edw_gen_view.g_instance, p_dim_name, l_table,

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

Line 820: edw_gen_view.g_instance, p_dim_name, l_table, l_structure_id,l_structure_id;

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;

Line 841: edw_gen_view.indentEnd;

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;

Line 842: edw_gen_view.writelog('Completed getNoneVSClause');

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:

Line 856: edw_gen_view.indentBegin;

852:
853: BEGIN
854: IF (g_log) THEN
855:
856: edw_gen_view.indentBegin;
857:
858: edw_gen_view.writelog('Inside getViewnameForStdDim');
859: END IF;
860:

Line 858: edw_gen_view.writelog('Inside getViewnameForStdDim');

854: IF (g_log) THEN
855:
856: edw_gen_view.indentBegin;
857:
858: edw_gen_view.writelog('Inside getViewnameForStdDim');
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

Line 864: WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;

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:

Line 869: edw_gen_view.writelog( 'Flex View Name is :'||g_std_flex_view_name );

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;

Line 870: edw_gen_view.writelog( 'Gen View Name is :'||g_std_generated_view_name );

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;

Line 871: edw_gen_view.writelog( 'Coll Name is :'||g_std_collection_view_name );

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:

Line 872: edw_gen_view.writelog( 'Completed getViewnameForStdDim');

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:

Line 873: edw_gen_view.indentEnd;

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

Line 878: edw_gen_view.g_success := false;

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:

Line 879: edw_gen_view.g_error := 'View Names not seeded for ' ||dim_name;

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

Line 880: raise edw_gen_view.viewgen_exception;

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:

Line 889: edw_gen_view.indentBegin;

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);

Line 892: edw_gen_view.writelog('Inside getGeneratedViewnameForStdDim');

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:

Line 893: edw_gen_view.writelog('Parameter dim_name:'||dim_name);

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

Line 894: edw_gen_view.writelog('Parameter level:'||level);

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

Line 899: WHERE object_name = dim_name AND level_name = level AND version =edw_gen_view.g_version;

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');

Line 902: edw_gen_view.writelog('View name for standard dimension '||dim_name || ':'||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;

Line 903: edw_gen_view.writelog('Completed getGeneratedViewnameForStdDim');

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:

Line 905: edw_gen_view.indentEnd;

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:

Line 916: attColumns edw_gen_view.tab_att_maps;

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);

Line 917: multiAttList edw_gen_view.tab_multi_att_list;

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;

Line 918: flexColumns edw_gen_view.tab_flex_att_maps;

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;

Line 919: fkColumns edw_gen_view.tab_fact_flex_fk_maps;

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;

Line 954: edw_gen_view.indentBegin;

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);

Line 956: edw_gen_view.writelog('Inside generateStdLevel');

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:

Line 957: edw_gen_view.writelog('Parameter dim_name:'||dim_name);

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:

Line 958: edw_gen_view.writelog('Parameter level_name:'||level_name);

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);

Line 966: nColCount := edw_gen_view.getColumnCountForView(view_name);

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;

Line 972: stmt := 'SELECT distinct column_name, data_type FROM all_tab_columns@'||edw_gen_view.g_source_db_link;

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:

Line 979: DBMS_SQL.BIND_VARIABLE(cid, ':owner', edw_gen_view.g_apps_schema, 50);

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);

Line 989: edw_gen_view.getColumnMaps(dim_name, attColumns, multiAttList,

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;

Line 1023: -- edw_gen_view.writelog('Number Column. Adding to_number');

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;

Line 1026: -- edw_gen_view.writelog('Skipped column, adding null');

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;

Line 1059: edw_gen_view.writelog('attr mapped to attr: '||curColumn);

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

Line 1083: edw_gen_view.writelog('Checking column '||curColumn||' for flex mapping');

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;

Line 1090: edw_gen_view.writelog('attr mapped to flex: '||curColumn);

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);

Line 1094: decodeClause := edw_gen_view.getDecodeClauseForFlexCol( g_std_flex_view_name, curColumn, flexColumns(nInnerLoopCount).id_flex_code, flexColumns(nInnerLoopCount).flex_field_type);

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;

Line 1126: edw_gen_view.writelog( srcview);

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;');

Line 1127: edw_gen_view.writelog('Standard level view is : '||newline||srcview);

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:

Line 1128: edw_gen_view.writeout(srcview|| newline);

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;

Line 1129: edw_gen_view.writeOutLine('/');

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:

Line 1130: edw_gen_view.writeOutLine('EXIT;');

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);

Line 1134: edw_gen_view.createView(srcview, view_name);

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:

Line 1136: edw_gen_view.writelog('Completed generateStdLevel');

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:

Line 1137: edw_gen_view.indentEnd;

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;

Line 1152: edw_gen_view.indentBegin;

1148: WHERE object_short_name = dim_name;
1149:
1150: BEGIN
1151: IF (g_log) THEN
1152: edw_gen_view.indentBegin;
1153:
1154: edw_gen_view.writelog('Inside generateStdDimension');
1155: END IF;
1156:

Line 1154: edw_gen_view.writelog('Inside generateStdDimension');

1150: BEGIN
1151: IF (g_log) THEN
1152: edw_gen_view.indentBegin;
1153:
1154: edw_gen_view.writelog('Inside generateStdDimension');
1155: END IF;
1156:
1157: FOR r1 in dimLevels LOOP
1158: generateStdLevel(dim_name, r1.level_name);

Line 1162: edw_gen_view.writelog('Completed generateStdDimension');

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;

Line 1163: edw_gen_view.indentEnd;

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:

Line 1194: and instance_code = edw_gen_view.g_instance

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

Line 1204: edw_gen_view.indentBegin;

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;

Line 1206: edw_gen_view.writelog( 'Inside generateViewForDimension');

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

Line 1207: edw_gen_view.writelog( 'Parameter dim_name:'||dim_name);

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;

Line 1229: edw_gen_view.writelog(newline);

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;

Line 1230: edw_gen_view.Writelog('!!!!! No Mappings !!!! returning...');

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:

Line 1242: edw_gen_view.writelog(newline);

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:

Line 1251: edw_gen_view.writelog('table validated vs exists');

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;

Line 1260: edw_gen_view.writelog(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:

Line 1267: edw_gen_view.writelog('Independant value set type exists');

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;

Line 1276: edw_gen_view.writelog(newline);

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:

Line 1283: edw_gen_view.writelog('None value set type exists');

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;

Line 1291: edw_gen_view.writelog(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:

Line 1299: edw_gen_view.writelog( newline);

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;

Line 1300: edw_gen_view.writeout(src_view|| newline);

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:

Line 1301: edw_gen_view.writeOutLine('/');

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);

Line 1308: edw_gen_view.createView(src_view, flexdim_viewname2);

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 ';

Line 1317: ''''||edw_gen_view.g_instance||''''||' instance, null actual_value, '||newline||

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:

Line 1322: edw_gen_view.createView(src_view, flexdim_viewname1);

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);

Line 1326: edw_gen_view.writeout(src_view|| newline);

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( '------------------------------------------------------------');

Line 1327: edw_gen_view.writeOutLine('/');

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;

Line 1328: edw_gen_view.writelog(newline);

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:

Line 1329: edw_gen_view.writelog( 'Completed generateViewForDimension!');

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;

Line 1330: edw_gen_view.writelog( '------------------------------------------------------------');

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;');

Line 1331: edw_gen_view.indentEnd;

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:

Line 1334: edw_gen_view.writeOutLine('EXIT;');

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;