[Home] [Help]
PACKAGE BODY: APPS.EDW_UPDATE_ATTRIBUTES
Source
1 Package Body EDW_UPDATE_ATTRIBUTES AS
2 /* $Header: EDWVCONB.pls 120.0 2005/06/01 16:00:15 appldev noship $ */
3 ---+==========================================================================+
4 ---| Copyright (c) 1995 Oracle Corporation Belmont, California, USA |
5 ---| All rights reserved |
6 ---+==========================================================================+
7 ---| |
8 ---| FILENAME |
9 ---| EDWVCONB.pls |
10 ---| |
11 ---| DESCRIPTION |
12 ---| |
13 ---| PUBLIC PROCEDURES |
14 ---| |
15 ---| PUBLIC FUNCTIONS |
16 ---| |
17 ---| PRIVATE PROCEDURES |
18 ---| |
19 ---| PRIVATE FUNCTIONS |
20 ---| |
21 ---| HISTORY |
22 ---| 15-01-2000 Walid.Nasrallah started logging changes per management |
23 ---| directive. Removed restriction to 11i version to allow |
24 ---| back-porting. |
25 ---| |
26 ---| smulye 04-Sep-03 |
27 ---| Changed API update_stg to use profile option UTL_FILE_LOG |
28 ---| Bug 2903252 |
29 ---| smulye 17-Nov-03 |
30 ---| changed API update_stg to use AD APIs to get temporary tablespace name|
31 ---| Bug 2759144 |
32 ---| amitgupt 13-FEB-2004 |
33 ---| changed queries involiving data dictionary for Bug 3431744 |
34 ---| 14-12-04 Amitgupt bug fix 4080618 |
35 ---+==========================================================================*
36
37
38
39 g_where_clause dbms_sql.varchar2_table;
40
41 g_total_seconds number := 0;
42 g_time_start date;
43 g_time_end date;
44
45 g_separate_logging boolean := false;
46
47 TYPE tableInfo is RECORD(
48 column_name VARCHAR2(150),
49 table_name VARCHAR2(150));
50
51 TYPE tab_info is TABLE of tableInfo
52 INDEX BY BINARY_INTEGER;
53 g_counter number := 0;
54
55 Procedure writelog(p_msg IN VARCHAR2) IS
56
57 l_count number;
58 l_length number;
59
60 begin
61 IF (not g_separate_logging) THEN
62 edw_owb_collection_util.write_to_log_file(p_msg);
63 return;
64 END IF;
65
66 l_count := 0;
67 l_length := length(p_msg);
68 loop
69 utl_file.put_line(g_file, substr(p_msg,l_count * 255+1, 255 ));
70 l_count := l_count +1 ;
71 exit when ((l_count*255) > l_length) OR l_count > 4;
72 end loop;
73 utl_file.fflush(g_file);
74
75 end;
76
77 Procedure drop_temp_tables(l_table_list in dbms_sql.varchar2_table) IS
78
79 l_count number := 0;
80 BEGIN
81
82 IF (l_table_list.count = 0) THEN
83 return;
84 END IF;
85
86 l_count := l_table_list.first;
87
88 LOOP
89 BEGIN
90 execute immediate 'drop table '||l_table_list(l_count);
91 EXIT when l_count = l_table_list.last;
92 l_count := l_table_list.next(l_count);
93
94 EXCEPTION WHEN OTHERS THEN
95 null;
96 END;
97 END LOOP;
98
99 END;
100
101 FUNCTION mapping_exists(l_object_name IN VARCHAR2, l_level_name IN VARCHAR2) return BOOLEAN IS
102
103 l_map_count number := 0;
104 BEGIN
105
106 SELECT count (1) into l_map_count
107 FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
108 WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
109 AND ATTR.OBJECT_SHORT_NAME = l_object_name
110 AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(l_level_name, 'null')
111 AND FLEX.VALUE_SET_TYPE = 'F';
112
113 IF (l_map_count > 0) then
114 return true;
115 END IF;
116 return false;
117 END;
118
119
120 FUNCTION add_db_links_to_string(p_table IN VARCHAR2, p_link IN VARCHAR2) return VARCHAR2 IS
121
122 bSingleTable boolean := false;
123
124 l_pos1 number := null;
125 l_pos2 number := null;
126
127 --p_link varchar2(100) := 'apps_to_apps';
128 --p_table varchar2(1000) := 'per_people_f ppf , per_person_types ppt , hr_all_organization_units , hello_world hw';
129
130 l_current_table varchar2(200);
131 l_count number := 0;
132
133 l_input_table varchar2(3000);
134
135 l_tables dbms_sql.varchar2_table;
136 l_alias dbms_sql.varchar2_table;
137
138 l_output_table varchar2(3000);
139
140 BEGIN
141
142
143 l_input_table := p_table||',';
144 IF (instr(l_input_table, ',') = 0 ) THEN
145 bSingleTable := true;
146 ELSE
147
148 l_input_table := replace (l_input_table, ' ,', ',');
149 l_input_table := replace (l_input_table, ' ', ' ');
150 l_input_table := replace (l_input_table, ', ', ',');
151 l_pos1 := instr(l_input_table, ' ,');
152 l_pos2 := instr(l_input_table, ' ');
153
154 END IF;
155
156 l_count := 0;
157
158
159 loop
160
161 exit when l_input_table is null or l_count > 6;
162 l_count := l_count + 1;
163
164 l_current_table := substr(l_input_table, 0, instr(l_input_table, ','));
165 IF (l_current_table IS NULL) then -- last table
166 l_current_table := l_input_table;
167 EnD IF;
168
169 l_pos1 := instr(l_current_table, ' '); -- check if alias exists
170 l_pos2 := instr(l_current_table, ',');
171
172
173 IF (l_pos1 > 0) THEN -- alias exists
174 l_tables(l_count) := trim(substr(l_current_table, 0, instr(l_current_table, ' ')));
175 l_alias(l_count) := trim(substr(l_current_table, l_pos1, length(l_current_table)-l_pos1));
176 ELSE
177 l_tables(L_count) := trim(substr(l_current_table, 0, instr(l_current_Table, ',')-1));
178 l_alias(l_count) := null;
179 END IF;
180 l_input_table := trim(substr(l_input_table, l_pos2+1, length(l_input_table) - l_pos2));
181
182
183 IF (l_count > 1) THEN
184 l_output_table := l_output_table||', ';
185 END IF;
186 IF (l_tables(l_count)<>',') THEN
187 l_output_table := l_output_table||l_tables(l_count)||'@'||p_link||' '||l_alias(l_count);
188 END IF;
189 end loop;
190
191 return l_output_table;
192
193 END;
194
195
196
197
198
199 Function add_links_to_where(p_where IN VARCHAR2, p_link IN VARCHAR2) RETURN VARCHAR2 IS
200 l_newline varchar2(10):='
201 ';
202
203 l_temp varchar2(1000);
204 l_from number := 0;
205 l_open number := 0;
206 l_close number := 0;
207 l_where number := 0;
208
209 l_end number := 0;
210
211 l_buffer varchar2(32000);
212 l_input varchar2(30000);
213 l_output varchar2(32000):= null;
214 l_count number:=0;
215
216 l_attach varchar2(1000) := null;
217
218 l_file utl_file.file_type;
219
220 l_tables varchar2(1000);
221
222
223 BEGIN
224
225 l_input := p_where;
226 l_input := replace(l_input, l_newline, ' ');
227
228 loop
229 l_input := replace(l_input, ' ', ' ');
230 exit when instr(l_input, ' ')=0;
231 end loop;
232
233 l_output := null;
234 l_buffer := l_input;
235
236 LOOP
237
238 writelog( l_newline);
239 writelog('l_buffer0 = '||l_buffer);
240
241 l_from := instr(lower(l_buffer), ' from ');
242
243 writelog( 'l_buffer='||l_buffer);
244 writelog( 'l_from = '||l_from);
245
246 IF (l_from = 0 ) THEN
247 l_output := l_output||l_buffer;
248 exit;
249 END IF;
250
251
252 l_output := l_output||' '||substr(l_buffer, 0, l_from+5);
253
254 l_buffer := substr(l_buffer, l_from+6, length(l_buffer));
255
256 l_open := instr(l_buffer, '(');
257 l_close := instr (l_buffer, ')');
258 l_where := instr(l_buffer, ' where ');
259
260 IF (l_open = 0) THEN l_open := 1000000; END IF;
261 IF (l_close = 0) THEN l_close := 1000000; END IF;
262 IF (l_where = 0) THEN l_where := 1000000; END IF;
263
264
265
266 l_end := l_open;
267
268 IF (l_end > l_close) THEN l_end := l_close; END IF;
269 IF (l_end > l_where ) THEN l_end := l_where; END IF;
270
271
272 writelog( 'l_open='||l_open||', l_close='||l_close
273 ||', l_where='||l_where ||', l_end='||l_end);
274
275 l_tables := substr(l_buffer, 0, l_end-1);
276
277 writelog( 'l_tables = '||l_tables);
278 l_tables := edw_update_attributes.add_db_links_to_string(l_tables, p_link);
279 l_output := l_output||l_tables;
280
281 l_buffer := substr(l_buffer, l_end, length(l_buffer));
282
283 writelog('l_buffer at the end of loop = '||l_buffer);
284 writelog( 'Output String so far is '||l_newline||l_output);
285
286 l_count := l_count+1;
287 END LOOP;
288
289 writelog('Final Output = '||l_output);
290 return l_output;
291
292
293 END;
294
295
296 Function checkWhereClause(p_value_set_id in NUMBER, p_link in varchar2) return boolean IS
297
298 TYPE CurTyp IS REF CURSOR;
299 cv CurTyp;
300
301 l_cursor number;
302 l_chunk_size number:=30000;
303 l_chunk_size_returned number;
304 l_stmt varchar2(2000);
305 l_cur_pos number:=0;
306 l_rows number;
307 l_dummy number;
308 l_data_chunk varchar2(30000);
309 l_count number := 0;
310 l_newline varchar2(10):='
311 ';
312
313 l_temp varchar2(1000);
314 l_pos1 number := 0;
315 l_pos2 number := 0;
316
317 BEGIN
318
319
320 writelog('Inside checkWhereClause');
321 l_stmt := 'select additional_where_clause from fnd_flex_validation_tables@'||
322 p_link||' where flex_value_set_id = :vsid';
323
324 writelog(l_stmt);
325
326 l_cursor:=dbms_sql.open_cursor;
327 dbms_sql.parse(l_cursor, l_stmt, dbms_sql.native);
328 dbms_sql.bind_variable(l_cursor, ':vsid', p_value_set_id);
329 dbms_sql.define_column_long(l_cursor,1);
330
331 l_dummy:=dbms_sql.execute(l_cursor);
332 l_rows:=dbms_sql.fetch_rows(l_cursor);
333
334 loop
335 -- fetch 'chunks' of the long until we have got the lot
336
337
338 dbms_sql.column_value_long(l_cursor,1,l_chunk_size,l_cur_pos,l_data_chunk,l_chunk_size_returned);
339
340 IF (upper(l_data_chunk) like '%$FLEX$' OR l_data_chunk like '%$PROFILE$%') THEN
341 return false;
342 END IF;
343
344
345 l_cur_pos:=l_cur_pos+l_chunk_size;
346 exit when l_chunk_size_returned=0;
347 l_data_chunk := replace(l_data_chunk, l_newline, ' ');
348 l_data_chunk := replace(l_data_chunk, ' ', ' ');
349
350
351 -- from clause exists
352 IF (lower(l_data_chunk) like '% from %') THEN
353 writelog('From exists in the where clause');
354 l_data_chunk := add_links_to_where(l_data_chunk, p_link);
355 writelog('Modified data chunk is : '||l_data_chunk);
356 END IF;
357
358 g_where_clause(l_count):=l_data_chunk;
359 l_count:= l_count +1;
360 end loop;
361 g_where_clause(0) := ltrim(g_where_clause(0));
362
363
364 writelog('WHERE Clause is '||g_where_clause(0));
365
366 IF (lower(substr(g_where_clause(0), 0, 5) ) = 'where') THEN
367 null;
368 ELSIF (lower(substr(g_where_clause(0), 0, 8) ) = 'order by') THEN
369 --g_where_clause(0) := null;
370 null;
371 ELSE
372
373 g_where_clause(0) := ' where '||g_where_clause(0);
374 END IF;
375
376 dbms_sql.close_cursor(l_cursor);
377
378 return true;
379
380 END;
381
382
383
384
385
386 FUNCTION getDBLink(p_instance IN VARCHAR2) RETURN VARCHAR2 IS
387 l_dblink varchar2(100);
388 BEGIN
389
390 SELECT warehouse_to_instance_link INTO l_dblink
391 FROM edw_source_instances_vl
392 WHERE instance_code = p_instance;
393 return l_dblink;
394 END;
395
396 FUNCTION findColumn(p_object IN VARCHAR2, p_column_like IN VARCHAR2, p_link IN VARCHAR2)
397 RETURN VARCHAR2 IS
398 TYPE CurTyp IS REF CURSOR;
399 cv CurTyp;
400 l_stmt varchar2 (1000);
401 l_col varchar2(200);
402 BEGIN
403 writelog('');
404 writelog ('Inside findColumn');
405
406 l_stmt := 'select column_name from user_tab_columns';
407 IF (p_link is not null) then
408 l_stmt := l_stmt ||'@'||p_link;
409 END IF;
410
411 l_stmt := l_stmt || ' where table_name = :s1 and column_name like :s2';
412
413 OPEN CV for l_stmt using p_object, '%'||p_column_like;
414 FETCH cv into l_col;
415 CLOSE CV;
416
417 writelog('Returning column='||l_col);
418 writelog('');
419 return l_col;
420
421 END;
422
423
424 PROCEDURE drop_temp_table (p_schema IN VARCHAR2, p_table_name IN VARCHAR2) IS
425
426 BEGIN
427 execute immediate 'drop table '||p_schema||'.'||p_table_name;
428 exception when others then
429 null;
430
431 END;
432
433
434
435 PROCEDURE get_table_validated_clause(p_vsid IN VARCHAR2, p_link IN VARCHAR2, p_tab_clause OUT NOCOPY VARCHAR2, p_id_col_exists OUT NOCOPY boolean) IS
436 l_clause varchar2(4000);
437 l_stmt varchar2(4000);
438
439 TYPE CurTyp IS REF CURSOR;
440 cv CurTyp;
441 l_table_name varchar2(1000);
442 l_value_col varchar2(100);
443 l_meaning_col varchar2(100);
444 l_id_col varchar2(100);
445 l_where_clause varchar2(3000);
446 l_where_count number := 0;
447
448 BEGIN
449
450 writelog('');
451 writelog('Inside get_table_Validated_clause');
452 writelog('vsid = '||p_vsid||', p_link = '||p_link);
453 writelog('');
454 g_where_clause.delete;
455
456 l_stmt := 'SELECT APPLICATION_TABLE_NAME, value_column_name, '||
457 ' meaning_column_name, id_column_name FROM '||
458 ' fnd_flex_validation_tables@' ||p_link||
459 ' a WHERE a.flex_value_set_id = '||p_vsid;
460
461 --writelog('l_stmt is '||l_stmt);
462 open cv for l_stmt;
463 fetch cv into l_table_name, l_value_col, l_meaning_col, l_id_col;
464 close cv;
465
466 writelog('Calling add_db_links');
467
468 IF (upper(l_table_name) like 'SELECT %' OR upper(l_table_name) like '% SELECT %') THEN
469 writelog('Inline tables not supported. Tables defined in the value set need to be database objects');
470 END IF;
471
472 l_table_name := add_db_links_to_string(l_table_name, p_link);
473
474
475 l_clause := 'SELECT '||l_id_col||' id_column_name, '||
476 nvl(l_meaning_col, nvl(l_value_col, 'null'))
477 ||' meaning_column_name, '||
478 nvl(l_value_col, nvl(l_meaning_col, 'null')) ||
479 ' value_column_name from '||l_table_name ;
480
481 IF (l_id_col IS NULL) THEN
482 writelog('');
483 writelog('There is no ID column for this attribute... returning....');
484 writelog('');
485 p_id_col_exists := false;
486 return;
487 END IF;
488 p_id_col_exists := true;
489
490 writelog('Calling checkWhereClause');
491
492 IF (not checkWhereClause(p_vsid, p_link)) THEN
493 fnd_message.set_name('BIS', 'EDW_BIND_VARIABLES_FOUND');
494 raise flex_variables_exception;
495 END IF;
496
497
498 l_where_count := g_where_clause.first;
499
500 IF (g_where_clause.count > 0) THEN
501 l_clause := l_clause|| ' ';
502 loop
503 l_clause := l_clause || g_where_clause(l_where_count);
504 exit when l_where_count = g_where_clause.last ;
505 l_where_count := g_where_clause.next(l_where_count);
506
507 writelog('where count is '||l_where_count);
508 end loop;
509 END IF;
510 p_tab_clause := l_clause;
511 writelog('');
512 writelog('Table val. clause is :'||p_tab_clause);
513 writelog('');
514
515 END;
516
517
518 Function getAppsVersion(p_instance IN VARCHAR2) return VARCHAR2 is
519
520 l_count number;
521 l_dummy integer;
522 l_cid number;
523
524 stmt VARCHAR2(500) ;
525 TYPE CurTyp IS REF CURSOR;
526 cv CurTyp;
527
528 l_version varchar2(300) := '11i';
529 l_db_link varchar2(200);
530
531 BEGIN
532
533 SELECT warehouse_to_instance_link into l_db_link
534 from EDW_SOURCE_INSTANCES_VL
535 WHERE instance_code = p_instance;
536
537 stmt := 'select substr(RELEASE_NAME, 1,8) from fnd_product_groups@'|| l_db_link;
538 open cv for stmt;
539 fetch cv into l_version;
540 close cv;
541
542 IF (l_version like '10.7%') THEN
543 l_version := '10.7';
544 ELSIF (l_version like '11.0%') THEN
545 l_version := '11.0';
546 ELSE
547 l_version := '11i';
548 END IF;
549
550 writelog('Returning version = '||l_version);
551 return l_version;
552
553 END;
554
555 FUNCTION get_ignorable_attributes(p_object_name IN VARCHAR2, p_level_name IN VARCHAR2) RETURN VARCHAR2 IS
556
557
558 CURSOR c_value_sets IS
559 SELECT
560 distinct FLEX.instance_code,
561 FLEX.VALUE_SET_ID,
562 decode(flex.flex_field_type, 'K', to_char(FLEX.STRUCTURE_NUM), FLEX.STRUCTURE_NAME) struct
563 FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
564 WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
565 AND ATTR.OBJECT_SHORT_NAME = p_object_name
566 AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(p_level_name, 'null')
567 AND FLEX.VALUE_SET_TYPE = 'F';
568
569 c_row c_value_sets%ROWTYPE;
570 b_idFlag boolean := false;
571 l_tab_clause varchar2(4000):= null;
572
573 l_ignore_list varchar2(1000);
574
575 CURSOR c_ignore_attribute(vsid NUMBER) IS
576 SELECT
577 distinct attr.attribute_name
578 FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
579 WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
580 AND ATTR.OBJECT_SHORT_NAME = p_object_name
581 AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(p_level_name, 'null')
582 AND FLEX.VALUE_SET_TYPE = 'F'
583 AND FLEX.VALUE_SET_ID = vsid ;
584
585 l_attribute VARCHAR2(200);
586
587 BEGIN
588 l_ignore_list := null;
589 OPEN c_value_sets;
590
591 writelog('Inside get_ignorable_attributes');
592
593 LOOP
594 FETCH c_value_sets into c_row;
595 EXIT WHEN c_value_sets%NOTFOUND;
596
597 BEGIN
598 writelog('Checking vsid '||c_row.value_set_id);
599 get_table_validated_clause(c_row.value_set_id, getdblink(c_row.instance_code),
600 l_tab_clause, b_idFlag);
601
602 IF (not b_idFlag) THEN
603 writelog('id flag is false, so ignore');
604
605 OPEN c_ignore_attribute(c_row.value_set_id);
606
607 LOOP
608 FETCH c_ignore_attribute INTO l_attribute;
609 EXIT when c_ignore_attribute%NOTFOUND;
610 l_ignore_list := l_ignore_list||','||l_attribute;
611 END LOOP;
612 CLOSE c_ignore_attribute;
613
614 END IF;
615
616 Exception when flex_variables_exception THEN
617
618 OPEN c_ignore_attribute(c_row.value_set_id);
619 FETCH c_ignore_attribute INTO l_attribute;
620 CLOSE c_ignore_attribute;
621 l_ignore_list := l_ignore_list||','||l_attribute;
622
623
624 END;
625 END LOOP;
626 l_ignore_list := l_ignore_list||',';
627 writelog('Ignore list is '||l_ignore_list);
628 return l_ignore_list;
629 END;
630
631
632 FUNCTION create_first_level_tables (l_object_name IN VARCHAR2, l_level_name IN VARCHAR2)
633 return boolean IS
634 BEGIN
635 return true;
636 END;
637
638
639 /*---------------------------------------------------------------------
640
641 Convert Table validated value set IDs to Descriptions. Will be called
642 from the pre load hook of the Loader Engine.
643
644
645 -----------------------------------------------------------------------*/
646
647
648
649 FUNCTION update_stg(
650 p_object_name IN VARCHAR2,
651 p_start_mode IN VARCHAR2,
652 p_logfile_dir IN VARCHAR2 default null) return boolean is
653
654 TYPE CurTyp IS REF CURSOR;
655 cv CurTyp;
656
657 check_tspace_exist varchar(1);
658 check_ts_mode varchar(1);
659 physical_tspace_name varchar2(100);
660
661 CURSOR c_mapped_list(obj_name VARCHAR2, lvl_name VARCHAR2, attr_name VARCHAR2) IS
662 SELECT attr.instance_code, attr.attribute_name,
663 flex.value_set_id, assg.flex_field_prefix,
664 vws.generated_view_name, vws.interface_table_name
665 from
666 edw_attribute_mappings attr,
667 edw_flex_attribute_mappings flex,
668 edw_sv_flex_assignments assg,
669 edw_source_views vws
670 where
671 attr.attribute_name = attr_name and
672 attr.object_short_name = assg.object_name and
673 attr.attr_mapping_pk = flex.attr_mapping_fk and
674 attr.object_short_name= obj_name and
675 flex.id_flex_code = assg.flex_field_code and
676 assg.version = edw_update_attributes.getAppsVersion(attr.instance_code) and
677 attr.object_short_name = vws.object_name and
678 nvl(attr.level_name, 'xxx') = nvl(vws.level_name, 'xxx') and
679 nvl(attr.level_name, 'xxx') = nvl(lvl_name, 'xxx') and
680 assg.version = vws.version and
681 flex.value_set_type = 'F' and
682 attr.flex_flag = 'Y' ;
683
684 c_mapped_attr c_mapped_list%rowtype;
685
686 CURSOR c_attr_list (obj_name VARCHAR2, lvl_name VARCHAR2) IS
687 SELECT distinct attribute_name
688 from edw_attribute_mappings a, edw_flex_attribute_mappings b
689 where object_short_name = obj_name
690 and level_name = lvl_name
691 and a.attr_mapping_pk = b.attr_mapping_fk
692 and nvl(level_name, 'xxx') = nvl(lvl_name, 'xxx')
693 and b.value_set_type = 'F'
694 order by attribute_name ;
695
696 --c_mapped_attr_list c_attr_list%rowtype;
697
698 b_dim_flag boolean := false;
699 l_stg_pk varchar2(100):= null;
700 l_bg_pk varchar2(100) := null;
701 l_link varchar2(300);
702 l_object_name varchar2(300);
703 l_level_name varchar2(255);
704
705 l_attribute_name varchar2(100);
706 b_temp_table_created boolean := false;
707
708 l_bis_schema varchar2(100):= EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
709 l_op_table_space varchar2(100) := fnd_profile.value('EDW_OP_TABLE_SPACE');
710
711 CURSOR c_contexts (c_obj_name VARCHAR2, c_lvl VARCHAR2, c_attr VARCHAR2) IS
712 SELECT
713 FLEX.instance_code,
714 FLEX.VALUE_SET_ID,
715 decode(flex.flex_field_type, 'K', to_char(FLEX.STRUCTURE_NUM), FLEX.STRUCTURE_NAME) struct
716 FROM EDW_ATTRIBUTE_MAPPINGS ATTR, EDW_FLEX_ATTRIBUTE_MAPPINGS FLEX
717 WHERE ATTR.ATTR_MAPPING_PK = FLEX.ATTR_MAPPING_FK
718 AND ATTR.OBJECT_SHORT_NAME = c_obj_name
719 AND NVL(ATTR.LEVEL_NAME, 'null') = NVL(c_lvl, 'null')
720 AND ATTR.ATTRIBUTE_NAME = c_attr
721 AND FLEX.VALUE_SET_TYPE = 'F';
722
723
724 ctx_row c_contexts%ROWTYPE;
725
726 l_it_name varchar2(150);
727
728 l_current_table VARCHAR2(150);
729 l_current_col varchar2(150);
730 l_stmt varchar2(4000);
731
732 l_tables_created tab_info;
733 l_count number := 0;
734
735
736 l_set_clause varchar2(1000);
737 l_select_clause varchar2(1000);
738 l_from_clause varchar2(1000);
739 l_where_clause varchar2(1000);
740
741 l_ctr number := 0;
742 l_instance varchar2(100);
743
744 l_tab_clause varchar2(3000) := null;
745
746 l_dir varchar2(1000);
747
748 b_idFlag boolean := true;
749 l_ignorable_attributes varchar2(1000);
750
751 l_drop_these dbms_sql.varchar2_table;
752
753
754 l_object_id number := 0;
755
756 BEGIN
757
758 g_separate_logging := false;
759
760 IF (p_start_mode <> 'LOAD') then
761 g_separate_logging := true;
762 IF (p_logfile_dir IS NOT NULL ) THEN
763 g_file := utl_file.fopen(p_logfile_dir, 'EDW_'||p_object_name||'.log' ,'w');
764 ELSE
765 /*l_dir:=fnd_profile.value('EDW_LOGFILE_DIR');
766 IF (l_dir is null) then
767 l_dir := edw_gen_view.getUtlfiledir;
768 END IF;*/
769
770 l_dir := fnd_profile.value('UTL_FILE_LOG');
771 if l_dir is null then
772 l_dir := fnd_profile.value('EDW_LOGFILE_DIR');
773 if l_dir is null then
774 l_dir:= edw_gen_view.getUtlfiledir;
775 end if;
776 end if;
777 g_file := utl_file.fopen(l_dir, 'EDW_'||p_object_name||'.log' ,'w');
778 END IF;
779 END IF;
780
781 writelog('Start Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
782
783 if l_op_table_space is null then
784 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
785 If check_ts_mode ='Y' then
786 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
787 if check_tspace_exist='Y' and physical_tspace_name is not null then
788 l_op_table_space := physical_tspace_name;
789 end if;
790 end if;
791 end if;
792
793
794
795 IF (l_op_table_space is null) THEN
796 l_op_table_space := EDW_OWB_COLLECTION_UTIL.get_table_space(l_bis_schema);
797 END IF;
798
799 writelog('Object name is : '||p_object_name);
800 writelog('BIS Schema is : '||l_bis_schema);
801 writelog('Op table space is : '||l_op_table_space);
802 writelog('');
803
804 BEGIN
805 select dim_name into l_object_name
806 from edw_levels_md_v
807 where level_name||'_LTC' = p_object_name;
808
809 writelog('This is a dimension : '||l_object_name);
810
811 b_dim_flag := true;
812 l_level_name := substr(p_object_name, 1, instr(p_object_name, '_LTC')-1);
813
814 SELECT level_id INTO l_object_id
815 from EDW_LEVELS_MD_V
816 WHERE level_name||'_LTC' = p_object_name;
817
818 Exception when no_data_found then
819 b_dim_flag := false;
820 l_object_name := p_object_name;
821 writelog('Going to check if this is a fact');
822
823 BEGIN
824
825 SELECT fact_id INTO l_object_id
826 from EDW_FACTS_MD_V
827 WHERE fact_name = p_object_name;
828 writelog('This is a fact :' ||l_object_name);
829
830 Exception when no_data_found then
831 writelog('Object cannot be found');
832 return true;
833 END;
834 END;
835
836 /* Check if anything is mapped at all */
837
838 IF ( NOT mapping_exists(l_object_name, l_level_name)) THEN
839 return true;
840 END IF;
841
842
843 /* Create a table for each UA with ROW_ID, VALUE, CONTEXT and INSTANCE Information */
844
845 l_ignorable_attributes := get_ignorable_attributes (l_object_name, l_level_name );
846
847
848 writelog('Creating First level Table INT_USER_ATTRIBUTEx');
849
850 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
851
852 OPEN c_attr_list(l_object_name, l_level_name);
853
854 writelog('Opening c_attr_list for for l_obj_name='||l_object_name||', l_level_name='||l_level_name);
855
856 LOOP
857 FETCH c_attr_list into l_attribute_name;
858 EXIT when c_attr_list%notfound;
859
860
861 writelog('Attribute : '||l_attribute_name );
862 writelog('Instance : '||l_instance);
863
864 drop_temp_table(l_bis_schema, 'INT_'||l_object_id||'_'||l_attribute_name);
865 b_temp_table_created := false;
866 OPEN c_mapped_list (l_object_name, l_level_name, l_attribute_name);
867
868 LOOP /* Process UA1, then UA2 etc */
869
870 FETCH c_mapped_list INTO c_mapped_attr;
871 EXIT when c_mapped_list%NOTFOUND;
872
873 EXIT when instr(l_ignorable_attributes, c_mapped_attr.attribute_name||',')>0;
874
875 l_count:= l_count+1; --bug 4080618
876
877 /* Find DB Link for this Instance Code */
878 l_link := getDBLink(c_mapped_attr.instance_code);
879 writelog('DB Link is '||l_link);
880 /* Find the PK of the staging table */
881 l_stmt := 'select cols.column_name from edw_relations_md_v rel,
882 edw_unique_keys_md_v keys, edw_unique_key_columns_md_v cols
883 where
884 rel.relation_name = :s1
885 and rel.relation_id = keys.entity_id
886 and keys.key_id = cols.key_id';
887 OPEN cv for l_stmt using c_mapped_attr.interface_table_name;
888 FETCH cv into l_stg_pk;
889 CLOSE cv;
890
891 writelog('Stg PK is '||l_stg_pk);
892
893 /* Find PK of BG View */
894 l_bg_pk := findColumn(c_mapped_attr.generated_view_name, '_PK', l_link);
895
896 writelog('BG pk is '||l_bg_pk);
897
898 l_current_table := l_bis_schema||'.INT_'||l_object_id||'_'||C_mapped_attr.attribute_name;
899
900 IF (l_ignorable_attributes NOT like ','||C_mapped_attr.attribute_name||',') THEN
901
902 writelog('Current table is '|| l_current_table);
903 IF (b_temp_table_created ) THEN
904 writelog('Table already created... inserting');
905 execute immediate 'INSERT INTO '||l_current_table||
906 '(row_id, value, context,instance ) select a.rowid row_id, a.'||
907 c_mapped_attr.attribute_name||' value, b.'||c_mapped_attr.flex_field_prefix||
908 '_CONTEXT context, '||''''||c_mapped_attr.instance_code|| ''''||' FROM '||c_mapped_attr.interface_table_name || ' a, '||
909 c_mapped_attr.generated_view_name || '@'||l_link||' b WHERE a.'
910 ||c_mapped_attr.attribute_name ||' IS NOT NULL AND A.'||
911 l_stg_pk ||' = b.'||l_bg_pk ||' AND A.collection_status ='||''''||'READY'||'''';
912
913 ELSE
914 writelog('Table does not exist... create as select..');
915 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
916
917
918
919 l_stmt := ' create table '||l_current_table
920 ||' tablespace '||l_op_table_space||' as select a.rowid row_id, '||
921 c_mapped_attr.attribute_name||' value, b.'||c_mapped_attr.flex_field_prefix||
922 '_CONTEXT context, '||''''||c_mapped_attr.instance_code||''''
923 ||' instance FROM '||c_mapped_attr.interface_table_name || ' a, '||
924 c_mapped_attr.generated_view_name||'@'||l_link || ' b WHERE a.'
925 ||c_mapped_attr.attribute_name ||' IS NOT NULL AND A.'||
926 l_stg_pk ||' = b.'||l_bg_pk||' AND A.collection_status ='||''''||'READY'||'''';
927 writelog('Going to exec... '||l_stmt);
928 execute immediate l_stmt;
929 b_temp_table_created := true;
930
931 IF (l_drop_these.count >0 ) THEN
932 l_drop_these(l_drop_these.last+1) := l_current_table;
933 ELSE
934 l_drop_these(1) := l_current_table;
935 END IF;
936
937 writelog('Table created successfully');
938 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
939 END IF;
940 END IF;
941 END LOOP;
942 l_it_name := c_mapped_attr.interface_table_name;
943 close c_mapped_list;
944 writelog('Interface table is : '||l_it_name);
945 END LOOP;
946
947 close c_attr_list;
948
949
950 writelog('');
951 writelog('');
952 writelog('Created first level tables ');
953
954
955 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
956 writelog('####################################################');
957
958 --bug 4080618 -- If all attributes are ignoreable then return.
959 IF (l_count = 0) THEN
960 return true;
961 END IF;
962 l_count := 0;
963
964 writelog('');
965 writelog('Creating 2nd level table with ROWID and VALUE');
966 writelog('l_object_name is '||l_object_name||', l_level_name is '||l_level_name);
967
968 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
969 /* Create 2nd level table for each UA with ROWID and VALUE */
970
971 /* Get list of instance, contexts and the mapped value sets */
972
973 OPEN c_attr_list(l_object_name, l_level_name);
974 LOOP
975 FETCH c_attr_list into l_attribute_name;
976 writelog('Atribute name is :'||l_attribute_name);
977 EXIT WHEN c_attr_list%NOTFOUND;
978
979
980 OPEN c_contexts(l_object_name, l_level_name, l_attribute_name);
981
982 b_temp_table_created := false;
983
984 LOOP /* PROCESS UA1, then UA2 etc */
985
986 writelog('l_count is '||l_count);
987 FETCH c_contexts into ctx_row;
988 EXIT when c_contexts%notfound;
989
990 EXIT when instr(l_ignorable_attributes, l_attribute_name||',')>0;
991
992
993 l_link := getdblink (ctx_row.instance_code);
994 get_table_validated_clause(ctx_row.value_set_id, l_link, l_tab_clause, b_idFlag);
995
996 IF (NOT b_idFlag) THEN
997 goto skip_processing;
998 END IF;
999
1000 l_current_table := l_bis_schema
1001 ||'.INTR_'||l_object_id||'_'||l_attribute_name;
1002
1003 IF (b_temp_table_created) THEN
1004 writelog('');
1005 writelog('Going to insert into '||l_current_table);
1006 writelog('');
1007
1008 l_stmt := ' INSERT INTO '||l_current_table
1009 ||' (ROW_ID, VALUE) select a.row_id row_id, '||
1010 ' decode(b.value_column_name, null, a.value, b.value_column_name) '||
1011 ' FROM '||l_bis_schema||'.INT_'||l_object_id||'_'||l_attribute_name
1012 || ' a, ('||
1013 l_tab_clause || ') b WHERE a.value=to_char(b.id_column_name(+)) and '||
1014 ' a.instance = '||''''||ctx_row.instance_code||''''||
1015 ' and a.context = '||''''||ctx_row.struct||'''';
1016 writelog(l_stmt);
1017 execute immediate l_stmt;
1018 ELSE
1019
1020 writelog('Dropping table '||l_current_table);
1021 drop_temp_table(l_bis_schema, 'INTR_'||l_object_id||'_'||l_attribute_name);
1022
1023 writelog('Dropped table '||l_current_table);
1024 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1025
1026 l_stmt:= ' create table '||l_current_table
1027 ||' tablespace '||l_op_table_space||' as select a.row_id row_id, '||
1028 ' decode(b.value_column_name, null, a.value, b.value_column_name) VALUE '||
1029 ' FROM '|| l_bis_schema||'.INT_'||l_object_id||'_'||l_attribute_name
1030 || ' a, ('||
1031 l_tab_clause || ') b WHERE a.value=to_char(b.id_column_name(+)) and '||
1032 ' a.instance = '||''''||ctx_row.instance_code||''''||
1033 ' and a.context = '||''''||ctx_row.struct||'''';
1034 writelog(l_stmt);
1035 execute immediate l_stmt;
1036 writelog('Created table '||l_current_table);
1037 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1038
1039 b_temp_table_created := true;
1040 l_tables_created(l_count).column_name := l_attribute_name;
1041 l_tables_created(l_count).table_name := l_current_table;
1042
1043 IF (l_drop_these.count>0) THEN
1044 l_drop_these(l_drop_these.last+1) := l_current_table;
1045 ELSE
1046 l_drop_these(1) := l_current_table;
1047 END IF;
1048 l_count := l_count + 1;
1049 END IF;
1050 <<skip_processing>>
1051 null;
1052 END LOOP;
1053 close c_contexts;
1054 END LOOP;
1055 close c_attr_list;
1056
1057 writelog('');
1058 writelog('Completed 2nd level tables ');
1059 writelog('Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1060 writelog('Creating Operational table with just Row ids');
1061 writelog('');
1062
1063
1064
1065
1066 writelog('Dropping rowid table, time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1067 drop_temp_table(l_bis_schema, 'INT_ROWIDS_'||l_object_id);
1068
1069 l_current_table := l_bis_schema|| '.INT_ROWIDS_'||l_object_id;
1070
1071 /* Create operational table with just the rowids (this will be the driving table) */
1072 execute immediate 'create table '||l_current_table
1073 ||' tablespace '||l_op_table_space||' as select rowid row_id from '|| l_it_name||' where collection_status = '||''''||'READY'||'''';
1074
1075 l_drop_these(l_drop_these.last+1) := l_current_table;
1076 writelog('Rowid table created, time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1077
1078 execute immediate 'create unique index '||l_current_table||'_u1 on '||
1079 l_current_table||'(row_id)';
1080
1081
1082 writelog('Unique Index created, Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1083 edw_owb_collection_util.analyze_table_stats('INT_ROWIDS_'||l_object_id, l_bis_schema, 1);
1084
1085
1086 writelog('INT_ROWIDS_'||l_object_id||' analyzed, Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1087
1088 /* Now merge all these smaller tables into one big table needed for bulk update */
1089
1090 drop_temp_table (l_bis_schema, 'INT_USER_ATTRIBUTES_'||l_object_id);
1091 l_current_table := l_bis_schema||'.INT_USER_ATTRIBUTES_'||l_object_id;
1092 l_drop_these(l_drop_these.last+1) := l_current_table;
1093
1094 l_stmt := 'create table '||l_current_table||' tablespace '||
1095 l_op_Table_space ||' AS SELECT ';
1096
1097 l_ctr := 0;
1098
1099 l_select_clause := null;
1100 l_from_clause := ' FROM '||l_bis_schema||'.INT_ROWIDS_'||l_object_id ||' a, ';
1101 l_where_clause := ' WHERE ';
1102
1103
1104 l_count := l_tables_created.first;
1105
1106 l_select_clause := l_select_clause|| ' a.row_id, ';
1107 LOOP
1108 IF (l_ctr > 0) THEN
1109 l_select_clause := l_select_clause || ', ';
1110 l_from_clause := l_from_clause || ' , ';
1111 l_where_clause := l_where_clause || ' AND ';
1112 END IF;
1113
1114 l_current_table := l_tables_created(l_count).table_name;
1115 l_current_col := l_tables_created(l_count).column_name;
1116
1117 l_select_clause := l_select_clause||l_current_col||'.VALUE '||l_current_col;
1118 l_from_clause := l_from_clause ||' '||l_current_table|| ' '||l_current_col;
1119 l_where_clause := l_where_clause || ' a.row_id = '||l_current_col||'.row_id(+)';
1120
1121 EXIT WHEN l_count = l_tables_created.last;
1122 l_count := l_tables_created.next(l_count);
1123 l_ctr := l_ctr + 1;
1124
1125 END LOOP;
1126
1127 l_stmt := l_stmt || l_select_clause|| l_from_clause||l_where_clause;
1128
1129 writelog('Select clause is '||l_select_clause);
1130 writelog('');
1131 writelog('From clause is '||l_from_clause);
1132 writelog('');
1133 writelog('Where clause is '||l_where_clause);
1134 writelog('');
1135 writelog('L Statement is '||l_stmt);
1136 writelog('');
1137 writelog('Creating INT_USER_ATTRIBUTES, Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1138
1139
1140 execute immediate l_stmt;
1141 writelog('Created INT_USER_ATTRIBUTES, Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1142
1143
1144
1145 writelog('Going to create unique index on INT_USER_ATTRIBUTES, '||
1146 ' Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1147 l_current_table := l_bis_schema||'.INT_USER_ATTRIBUTES_'||l_object_id;
1148
1149 execute immediate 'create unique index '||l_current_table||'_u1 on '||
1150 l_current_table ||'(row_id)';
1151
1152 writelog('Create unique index on INT_USER_ATTRIBUTES, '||
1153 'Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1154
1155 edw_owb_collection_util.analyze_table_stats('INT_USER_ATTRIBUTES_'||l_object_id, l_bis_schema, 1);
1156 writelog('Analyzed INT_USER_ATTRIBUTES, Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1157
1158
1159
1160
1161 /* Now UPDATE the staging table using a single update */
1162
1163 l_stmt := 'UPDATE '||l_it_name|| ' stg SET ';
1164 l_select_clause := ' (SELECT ';
1165 l_from_clause := ' FROM '||l_current_table
1166 ||' A where a.row_id = stg.rowid ';
1167
1168 l_count := l_tables_created.first;
1169
1170 writelog('l_stmt = '||l_stmt);
1171 l_ctr := 0;
1172 LOOP
1173 writelog('l_ctr = '||l_ctr);
1174 IF (l_ctr > 0) THEN
1175 l_set_clause := l_set_clause || ', ';
1176 l_select_clause := l_select_clause||', ';
1177 END IF;
1178 l_set_clause := l_set_clause ||' '||l_tables_created(l_count).column_name;
1179 l_select_clause := l_select_clause||' A.'||
1180 l_tables_created(l_count).column_name;
1181
1182 EXIT WHEN l_count = l_tables_created.last;
1183 l_count := l_tables_created.next(l_count);
1184 l_ctr := l_ctr + 1;
1185 --if (l_ctr > 100 ) then exit; end if;
1186 END LOOP;
1187
1188
1189
1190 l_stmt := l_stmt|| '('||l_set_clause ||') = '||l_select_clause|| l_from_clause|| ' )';
1191 writelog('');
1192 writelog('');
1193 writelog('Final update stmt is : ');
1194 writelog(l_stmt);
1195
1196 writelog('End Time is :'||to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss'));
1197
1198 execute immediate l_stmt;
1199 drop_temp_tables(l_drop_these);
1200
1201 return true;
1202
1203 Exception when others then
1204 writelog('Inside Exception ');
1205 utl_file.fclose(g_file);
1206 raise;
1207 return false;
1208
1209
1210 END;
1211
1212 END EDW_UPDATE_ATTRIBUTES ;