1 package body EDW_ALL_COLLECT AS
2 /*$Header: EDWACOLB.pls 120.0 2005/06/01 16:43:59 appldev noship $*/
3 l_levels EDW_OWB_COLLECTION_UTIL.varcharTableType;
4 l_level_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
5 l_child_level_number EDW_OWB_COLLECTION_UTIL.numberTableType;
6 l_child_levels EDW_OWB_COLLECTION_UTIL.varcharTableType;
7 l_child_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
8 l_parent_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
9 l_mapping_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
10 l_primary_src EDW_OWB_COLLECTION_UTIL.numberTableType;
11 l_primary_target EDW_OWB_COLLECTION_UTIL.numberTableType;
12 l_number_levels integer;
13 l_rank EDW_OWB_COLLECTION_UTIL.numberTableType;
14 l_child_start EDW_OWB_COLLECTION_UTIL.numberTableType;--this stores where
15 --in the big array the child level for the parent start
16
17 g_exec_flag boolean;
18 g_dimension_collect boolean:=true;
19 g_debug boolean:=false;
20 g_all_level_found boolean :=true;
21
22 procedure Collect_Dimension(Errbuf out NOCOPY varchar2,
23 Retcode out NOCOPY varchar2,
24 p_dim_name in varchar2) IS
25
26 l_found boolean;
27 l_run number:=1;
28 l_var number;
29 l_load_pk number;
30 l_src_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
31 l_src_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
32 l_tgt_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
33 l_tgt_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
34 l_number_src_cols number;
35 --
36 l_exception exception;
37 --
38 Begin
39 g_collection_start_date:=sysdate;
40 g_collect_fact:=false;
41 g_collect_dim:=true;
42 g_status:=true;
43 g_conc_program_id:=0;
44 g_object_name:=upper(p_dim_name);
45 g_object_type:='DIMENSION';
46 g_conc_program_id:=FND_GLOBAL.Conc_request_id;--my conc id
47 g_resp_id:=FND_GLOBAL.RESP_ID;
48 g_conc_program_name :=p_dim_name||'_T'; --assume. no harm
49 retcode:='0';
50 g_logical_object_type:='DIMENSION';
51
52 --first set up the conc log
53 --EDW_OWB_COLLECTION_UTIL.setup_conc_program_log(p_dim_name);
54 EDW_OWB_COLLECTION_UTIL.init_all(p_dim_name,null,'bis.edw.loader_load_dimension');
55 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Collect Dimension for '||p_dim_name||get_time,FND_LOG.LEVEL_PROCEDURE);
56 g_load_pk:=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk;
57 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_load_pk='||g_load_pk,FND_LOG.LEVEL_STATEMENT);
58 if g_load_pk is null then
59 errbuf:=g_status_message;
60 retcode:='2';
61 return_with_error(g_load_pk,'LOG');
62 return;
63 end if;
64 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Concurrent id '||g_conc_program_id||', and conc prog name '||g_conc_program_name,
65 FND_LOG.LEVEL_PROCEDURE);
66 g_object_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_object_name);
67 if g_object_id=-1 then
68 errbuf:=EDW_OWB_COLLECTION_UTIL.g_status_message;
69 retcode:='2';
70 return_with_error(g_load_pk,'LOG');
71 return;
72 end if;
73
74 --first make sure that another coll for the same object is not running
75 --if there is none, it inserts a row into edw_coll_progress_log
76 EDW_OWB_COLLECTION_UTIL.set_debug(true);
77 l_var:= EDW_OWB_COLLECTION_UTIL.is_another_coll_running(g_object_name, g_object_type);
78 EDW_OWB_COLLECTION_UTIL.set_debug(false);
79
80 if l_var=2 then
81 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('All Clear to proceed',FND_LOG.LEVEL_STATEMENT);
82 end if;
83
84 if l_var=1 then
85 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NEW_COLLECTION_RUNNING');
86 errbuf:=g_status_message;
87 retcode:='2';
88 return_with_error(g_load_pk,'LOG');
89 return;
90 elsif l_var=0 then
91 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
92 errbuf:=g_status_message;
93 retcode:='2';
94 return_with_error(g_load_pk,'LOG');
95 return;
96 end if;
97
98 if EDW_OWB_COLLECTION_UTIL.log_collection_start(g_object_name,g_object_id,g_object_type,
99 g_collection_start_date,g_conc_program_id,g_load_pk) =false then
100 errbuf:=g_status_message;
101 retcode:='2';
102 return_with_error(g_load_pk,'LOG');
103 return;
104 end if;
105
106 init_all;
107
108 if g_status=false then
109 errbuf:=g_status_message;
110 retcode:='2';
111 return_with_error(g_load_pk,'LOG');
112 return;
113 end if;
114 if g_debug then
115 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('pre_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
116 end if;
117 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Coll Hook',sysdate,null,'PRE-LEVEL',
118 'PRE-COLL-HOOK',10,'I');
119 if EDW_COLLECTION_HOOK.pre_coll(g_object_name) = true then
120 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
121 else
122 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_coll with error'||get_time,FND_LOG.LEVEL_PROCEDURE);
123 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_FINISHED_PRECOLL_ERROR');
124 errbuf:=g_status_message;
125 retcode:='2';
126 return_with_error(g_load_pk,'LOG');
127 return;
128 end if;
129 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,10,'U');
130 if g_debug then
131 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('pre_dimension_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
132 end if;
133 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Dimension Coll Hook',sysdate,null,'PRE-LEVEL',
134 'PRE-DIM-COLL-HOOK',11,'I');
135 if EDW_COLLECTION_HOOK.pre_dimension_coll(g_object_name) = true then
136 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_dimension_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
137 else
138 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_dimension_coll with error'||get_time,FND_LOG.LEVEL_PROCEDURE);
139 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_FINISHED_PREDIM_ERROR');
140 errbuf:=g_status_message;
141 retcode:='2';
142 return_with_error(g_load_pk,'LOG');
143 return;
144 end if;
145 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,11,'U');
146
147 EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
148
149 --push naedw
150 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Load NA_EDW',sysdate,null,'PRE-LEVEL',
151 'NAEDW_LOAD',12,'I');
152
153 EDW_NAEDW_PUSH.PUSH(Errbuf,retcode,g_object_name,g_debug);
154 if retcode ='2' then
155 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('EDW_NAEDW_PUSH.PUSH returned with error',FND_LOG.LEVEL_ERROR);
156 return;
157 end if;
158 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,12,'U');
159
160 EDW_OWB_COLLECTION_UTIL.set_up(p_dim_name);
161 if g_debug then
162 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished EDW_OWB_COLLECTION_UTIL.setup , Time '||get_time,FND_LOG.LEVEL_PROCEDURE);
163 end if;
164
165 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Read Metadata',sysdate,null,'PRE-LEVEL',
166 'METADATA_READ',13,'I');
167
168 EDW_OWB_COLLECTION_UTIL.Get_Level_Relations(
169 l_levels,
170 l_level_status,
171 l_child_level_number,
172 l_child_levels,
173 l_child_fk,
174 l_parent_pk,
175 l_number_levels);
176 if g_debug then
177 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished EDW_OWB_COLLECTION_UTIL.Get_Level_Relations, Time '||get_time,
178 FND_LOG.LEVEL_PROCEDURE);
179 EDW_OWB_COLLECTION_UTIL.write_to_log_file('Results',FND_LOG.LEVEL_STATEMENT);
180 for i in 1..l_number_levels loop
181 EDW_OWB_COLLECTION_UTIL.write_to_log_file(l_levels(i)||'('||l_level_status(i)||') '||l_child_level_number(i),
182 FND_LOG.LEVEL_STATEMENT);
183 end loop;
184 l_run:=0;
185 for i in 1..l_number_levels loop
186 for j in 1..l_child_level_number(i) loop
187 l_run:=l_run+1;
188 EDW_OWB_COLLECTION_UTIL.write_to_log_file(l_child_levels(l_run)||' '||l_child_fk(l_run)||' '||l_parent_pk(l_run),
189 FND_LOG.LEVEL_STATEMENT);
190 end loop;
191 end loop;
192 end if;
193
194 if l_number_levels=0 then
195 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR: In EDW_OWB_COLLECTION_UTIL.Get_Level_Relations,No levels found..'||get_time
196 ,FND_LOG.LEVEL_PROCEDURE);
197 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
198 g_status:=false;
199 errbuf:=g_status_message;
200 retcode:='2';
201 return_with_error(g_load_pk,'LOG');
202 return;
203 end if;
204
205 EDW_OWB_COLLECTION_UTIL.Get_mapping_ids(
206 l_mapping_ids,
207 l_primary_src,
208 l_primary_target);
209
210 if g_debug then
211 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished EDW_OWB_COLLECTION_UTIL.Get_mapping_ids, Time '||get_time,
212 FND_LOG.LEVEL_PROCEDURE);
213 end if;
214
215 --find out NOCOPY any skipped levels
216 if EDW_OWB_COLLECTION_UTIL.get_col_col_in_map(null,p_dim_name,l_src_table,l_src_cols,l_tgt_table,
217 l_tgt_cols,l_number_src_cols)=false then
218 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
219 g_status:=false;
220 errbuf:=g_status_message;
221 retcode:='2';
222 return_with_error(g_load_pk,'LOG');
223 end if;
224 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(g_object_name,g_object_type,g_skip_cols,
225 g_number_skip_cols)=false then
226 errbuf:=g_status_message;
227 retcode:='2';
228 return_with_error(g_load_pk,'LOG');
229 return;
230 end if;
231
232 if g_number_skip_cols>0 then
233 --see if in any level, all the cols are skipped. then the whole level is not needed
234 declare
235 l_found boolean;
236 l_level_full_skip EDW_OWB_COLLECTION_UTIL.varcharTableType;
237 l_number_level_full_skip number:=0;
238 l_levels_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
239 l_level_status_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
240 l_child_level_number_copy EDW_OWB_COLLECTION_UTIL.numberTableType;
241 l_child_levels_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
242 l_child_fk_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
243 l_parent_pk_copy EDW_OWB_COLLECTION_UTIL.varcharTableType;
244 l_number_levels_copy number;
245 l_mapping_ids_copy EDW_OWB_COLLECTION_UTIL.numberTableType;
246 l_primary_src_copy EDW_OWB_COLLECTION_UTIL.numberTableType;
247 l_primary_target_copy EDW_OWB_COLLECTION_UTIL.numberTableType;
248 l_ptr number;
249 begin
250 for i in 1..l_number_levels loop
251 l_found:=false;
252 for j in 1..l_number_src_cols loop
253 if l_src_table(j)=l_levels(i) then
254 if EDW_OWB_COLLECTION_UTIL.value_in_table(g_skip_cols,g_number_skip_cols,l_tgt_cols(j))=false then
255 l_found:=true;
256 exit;
257 end if;
258 end if;
259 end loop;
260 if l_found=false then
261 l_number_level_full_skip:=l_number_level_full_skip+1;
262 l_level_full_skip(l_number_level_full_skip):=l_levels(i);
263 g_number_skip_levels:=g_number_skip_levels+1;
264 g_skip_levels(g_number_skip_levels):=l_levels(i);
265 end if;
266 end loop;
267 if g_debug then
268 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Number of levels fully turned off '||l_number_level_full_skip,
269 FND_LOG.LEVEL_STATEMENT);
270 for i in 1..l_number_level_full_skip loop
271 EDW_OWB_COLLECTION_UTIL.write_to_log_file(l_level_full_skip(i),FND_LOG.LEVEL_STATEMENT);
272 end loop;
273 end if;
274 if l_number_level_full_skip>0 then
275 l_levels_copy:=l_levels;
276 l_level_status_copy:=l_level_status;
277 l_child_level_number_copy:=l_child_level_number;
278 l_child_levels_copy:=l_child_levels;
279 l_child_fk_copy:=l_child_fk;
280 l_parent_pk_copy:=l_parent_pk;
281 l_number_levels_copy:=l_number_levels;
282 l_mapping_ids_copy:=l_mapping_ids;
283 l_primary_src_copy:=l_primary_src;
284 l_primary_target_copy:=l_primary_target;
285 l_run:=1;
286 for i in 1..l_number_levels loop
287 l_child_level_number(i):=0;
288 end loop;
289 l_number_levels:=0;
290 l_ptr:=0;
291 for i in 1..l_number_levels_copy loop
292 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_level_full_skip,l_number_level_full_skip,l_levels_copy(i))=false then
293 l_number_levels:=l_number_levels+1;
294 l_levels(l_number_levels):=l_levels_copy(i);
295 l_level_status(l_number_levels):=l_level_status_copy(i);
296 l_mapping_ids(l_number_levels):=l_mapping_ids_copy(i);
297 l_primary_src(l_number_levels):=l_primary_src_copy(i);
298 l_primary_target(l_number_levels):=l_primary_target_copy(i);
299 if l_child_level_number_copy(i)>0 then
300 for j in l_run..(l_run+l_child_level_number_copy(i)-1) loop
301 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_level_full_skip,l_number_level_full_skip,
302 l_child_levels_copy(j))=false then
303 l_child_level_number(l_number_levels):=l_child_level_number(l_number_levels)+1;
304 l_ptr:=l_ptr+1;
305 l_child_levels(l_ptr):=l_child_levels_copy(j);
306 l_child_fk(l_ptr):=l_child_fk_copy(j);
307 l_parent_pk(l_ptr):=l_parent_pk_copy(j);
308 end if;
309 end loop;
310 end if;
311 l_run:=l_run+l_child_level_number_copy(i);
312 --l_child_level_number(l_number_levels):=l_child_level_number_copy(i);
316 end loop;
313 else
314 l_run:=l_run+l_child_level_number_copy(i);
315 end if;
317 end if;--if l_number_level_full_skip>0 then
318 end;
319 --see how many cols are actually mapped
320 declare
321 l_count number;
322 begin
323 l_count:=0;
324 for j in 1..l_number_src_cols loop
325 if EDW_OWB_COLLECTION_UTIL.value_in_table(g_skip_cols,g_number_skip_cols,l_tgt_cols(j))=false then
326 l_count:=l_count+1;
327 end if;
328 end loop;
329 if l_count<=g_check_fk_change_number then
330 g_check_fk_change:=false;
331 if g_debug then
332 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The number of columns mapped is < '||g_check_fk_change_number||'. Turning OFF fk '||
333 'change check',FND_LOG.LEVEL_STATEMENT);
334 end if;
335 end if;
336 --4207268
337 /*
338 when smart update is false, turn off fk change check. this means when dim is loaded, it will pull in all the levels
339 in the select and from clause.
340 */
341 if g_smart_update=false then
342 g_check_fk_change:=false;
343 if g_debug then
344 write_to_log_file_n('Smart Update false. Turning OFF fk change check');
345 end if;
346 end if;
347 end;
348 end if;--if g_number_skip_cols>0 then
349
350 if l_number_levels=0 then
351 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_ALL_LEVELS_TURNED_OFF');
352 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
353 return_with_success('LOG',null,g_load_pk);
354 return;
355 end if;
356
357 if g_number_skip_cols>0 then
358 if g_debug then
359 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('After turning off levels that are not needed',FND_LOG.LEVEL_STATEMENT);
360 for i in 1..l_number_levels loop
361 EDW_OWB_COLLECTION_UTIL.write_to_log_file(l_levels(i)||'('||l_level_status(i)||') '||l_child_level_number(i)||' '||l_mapping_ids(i)
362 ||' '||l_primary_src(i)||' '||l_primary_target(i),FND_LOG.LEVEL_STATEMENT);
363 end loop;
364 l_run:=0;
365 for i in 1..l_number_levels loop
366 for j in 1..l_child_level_number(i) loop
367 l_run:=l_run+1;
368 EDW_OWB_COLLECTION_UTIL.write_to_log_file(l_child_levels(l_run)||' '||l_child_fk(l_run)||' '||l_parent_pk(l_run),
369 FND_LOG.LEVEL_STATEMENT);
370 end loop;
371 end loop;
372 end if;
373 end if;
374
375 --arrange according to the reqd order. implement the rank scheme
376 --init the rank
377 Set_Rank;
378 if g_status = false then
379 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Could not determine the ranks for the levels in the hierarchies, Time '||get_time,
380 FND_LOG.LEVEL_STATEMENT);
381 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_RANK_HIER');
382 errbuf:=g_status_message;
383 retcode:='2';
384 return_with_error(g_load_pk,'LOG');
385 return;
386 end if;
387
388 if g_debug then
389 for i in 1..l_number_levels loop
390 EDW_OWB_COLLECTION_UTIL.write_to_log_file('level '||l_levels(i)||', rank '||l_rank(i),FND_LOG.LEVEL_STATEMENT);
391 end loop;
392 end if;
393
394 --arrange in the order of the rank
395 Order_by_Rank;
396
397 if g_status = false then
398 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Could not order by ranks, Time '||get_time,FND_LOG.LEVEL_STATEMENT);
399 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NO_ORDER_BY_RANK');
400 errbuf:=g_status_message;
401 retcode:='2';
402 return_with_error(g_load_pk,'LOG');
403 return;
404 end if;
405
406 if g_debug then
407 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The ordered levels'||get_time,FND_LOG.LEVEL_STATEMENT);
408 for i in 1..l_number_levels loop
409 EDW_OWB_COLLECTION_UTIL.write_to_log_file('level '||g_level_order(i),FND_LOG.LEVEL_STATEMENT);
410 end loop;
411 end if;
412
413 if get_snapshot_log = false then
414 errbuf:=g_status_message;
415 retcode:='2';
416 return_with_error(g_load_pk,'LOG');
417 return;
418 end if;
419
420 if find_data_alignment_cols(g_object_name)=false then
421 errbuf:=g_status_message;
422 retcode:='2';
423 return_with_error(g_load_pk,'LOG');
424 return;
425 end if;
426 --see if the dim can have parallel drill down where a dbms job is launched after a level is
427 --loaded . this job will drill down the changes to the child levels
428 --this approach cannot be used if
429 --if error recovery
430 --or if there is na_edw update
431 --or if this is initial load
432 --or if there is push down
433 find_parallel_drill_down(g_level_order,l_number_levels);
434 if g_debug then
435 if g_parallel_drill_down then
436 write_to_log_file_n('Parallel drill down Enabled');
437 else
438 write_to_log_file_n('Parallel drill down Disabled');
439 end if;
440 end if;
441 if g_parallel_drill_down then
442 if EDW_OWB_COLLECTION_UTIL.create_dd_status_table(
443 g_dd_status_table,
444 g_level_order,
445 l_number_levels
446 )=false then
447 raise l_exception;
448 end if;
449 else
450 --create dummy g_dd_status_table table. this table is used to
451 --check for error recovery
455 null
452 if EDW_OWB_COLLECTION_UTIL.create_dd_status_table(
453 g_dd_status_table,
454 g_level_order,
456 )=false then
457 raise l_exception;
458 end if;
459 end if;
460 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,13,'U');
461
462 Collect_Each_Level;
463 if g_status=false then
464 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR: IN Collect_Each_Level '||g_status_message,FND_LOG.LEVEL_ERROR);
465 errbuf:='FINISH COLLECT EACH LEVEL WITH ERROR '||g_status_message;
466 retcode:='2';
467 return_with_error(g_load_pk,'LOG');
468 return;
469 end if;
470
471 insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Push Down Levels',sysdate,null,'LEVEL',
472 'DIMENSION','PD10','I');
473 --inside EDW_PUSH_DOWN_DIMS.push_down_all_levels we see which all levels need push down
474 if EDW_PUSH_DOWN_DIMS.push_down_all_levels(g_object_name,
475 l_levels,
476 l_child_level_number,
477 l_child_levels,
478 l_child_fk,
479 l_parent_pk,
480 l_number_levels,
481 g_level_order,
482 g_level_snapshot_logs,
483 g_debug,
484 g_parallel,
485 g_collection_size,
486 g_bis_owner,
487 g_table_owner,
488 false,
489 g_forall_size,
490 g_update_type,
491 g_load_pk,
492 g_op_table_space,
493 g_dim_push_down,
494 g_rollback,
495 g_thread_type,
496 g_max_threads,
497 g_min_job_load_size,
498 g_sleep_time,
499 g_hash_area_size,
500 g_sort_area_size,
501 g_trace,
502 g_read_cfig_options,
503 g_stg_join_nl
504 ) =false then
505 errbuf:='FINISH push_down_all_levels WITH ERROR '||EDW_PUSH_DOWN_DIMS.g_status_message;
506 retcode:='2';
507 insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'PD10','U');
508 return_with_error(g_load_pk,'LOG');
509 return;
510 end if;
511 insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'PD10','U');
512
513 if g_dimension_collect then
514 if g_debug then
515 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to call EDW_SUMMARY_COLLECT.collect_dimension',FND_LOG.LEVEL_PROCEDURE);
516 end if;
517 insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Dimension Collection',sysdate,null,'DIMENSION',
518 'DIMENSION','DC10','I');
519 EDW_SUMMARY_COLLECT.collect_dimension_main(
520 g_conc_program_id,
521 g_conc_program_name,
522 p_dim_name,
523 l_levels,
524 l_child_level_number,
525 l_child_levels,
526 l_child_fk,
527 l_parent_pk,
528 g_level_snapshot_logs,
529 l_number_levels,
530 g_debug,
531 g_exec_flag,
532 g_bis_owner,
533 g_parallel,
534 g_collection_size,
535 g_table_owner,
536 g_forall_size,
537 g_update_type,
538 g_level_order,
539 g_skip_cols,
540 g_number_skip_cols,
541 g_load_pk,
542 g_fresh_restart,
543 g_op_table_space,
544 g_rollback,
545 g_ltc_merge_use_nl,
546 g_dim_inc_refresh_derv,
547 g_check_fk_change,
548 g_ok_switch_update,
549 g_stg_join_nl,
550 g_thread_type,
551 g_max_threads,
552 g_min_job_load_size,
553 g_sleep_time,
554 g_job_status_table,
555 g_hash_area_size,
556 g_sort_area_size,
557 g_trace,
558 g_read_cfig_options,
559 g_max_fk_density,
560 g_analyze_frequency,
561 g_parallel_drill_down,
562 g_dd_status_table
563 );
564 insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'DC10','U');
565 if EDW_SUMMARY_COLLECT.check_error=false then
566 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR:EDW_SUMMARY_COLLECT.Collect_Dimension, '||
567 EDW_SUMMARY_COLLECT.get_status_message||' Time '||get_time,FND_LOG.LEVEL_ERROR);
568 g_status_message:=EDW_SUMMARY_COLLECT.get_status_message;
569 errbuf:=g_status_message;
570 retcode:='2';
571 return_with_error(g_load_pk,'LOG');
572 return;
573 else
574 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished EDW_SUMMARY_COLLECT.Collect_Dimension,Time '||get_time,
575 FND_LOG.LEVEL_PROCEDURE);
576 /*
577 get the record of the lowest level collection from the table edw_temp_collection_log
578 */
579 if get_temp_log_data(g_object_name, g_object_type)=false then
580 errbuf:=g_status_message;
581 retcode:='2';
582 return_with_error(g_load_pk,'NO-LOG');
583 return;
584 end if;
585 end if;
586
587 end if;--if g_dimension_collect then
588 --g_dd_status_table present in the database shows there was an error
589 if edw_owb_collection_util.drop_level_UL_tables(g_object_id,g_bis_owner)=false then
590 null;
591 end if;
592 if edw_owb_collection_util.drop_table(g_dd_status_table)=false then
593 null;
594 end if;
595 if g_debug then
596 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('post_dimension_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
597 end if;
598 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Post Dimension Coll Hook',sysdate,null,'POST-LEVEL',
599 'POST-DIM-COLL-HOOK',14,'I');
603 return_with_success('LOG',null,g_load_pk);
600 if EDW_COLLECTION_HOOK.post_dimension_coll(g_object_name) = true then
601 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_dimension_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
602 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,14,'U');
604 if g_status=false then
605 return;
606 end if;
607 --if there is a diamond issue then flag the user
608 if g_diamond_issue then
609 retcode:='1';
610 errbuf:=g_status_message;
611 end if;
612 else
613 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_dimension_coll with error'||get_time,FND_LOG.LEVEL_ERROR);
614 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_FINISHED_POSTDIM_ERROR');
615 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,14,'U');
616 errbuf:=g_status_message;
617 retcode:='2';
618 return_with_error(g_load_pk,'LOG');
619 return;
620 end if;
621 if g_dim_inc_refresh_derv then
622 if refresh_dim_derv_facts(g_object_name,l_load_pk)=false then
623 errbuf:=g_status_message;
624 retcode:='2';
625 return_with_error(l_load_pk,'NO-LOG');
626 return;
627 end if;
628 end if;
629 clean_up;--cleans up the progress log
630 /* this call is for workflow for now...*/
631 if g_debug then
632 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('post_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
633 end if;
634 if EDW_COLLECTION_HOOK.post_coll(g_object_name) = true then
635 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
636 else
637 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_coll with error'||get_time,FND_LOG.LEVEL_ERROR);
638 end if;
639 Exception
640 when l_exception then
641 errbuf:=g_status_message;
642 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
643 retcode:='2';
644 return_with_error(g_load_pk,'LOG');
645 when others then
646 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR In EDW_ALL_COLLECT.Collect_Dimension,
647 Error mesg:'||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
648 g_status_message:=sqlerrm;
649 errbuf:=g_status_message;
650 retcode:='2';
651 return_with_error(g_load_pk,'LOG');
652 End;--procedure Collect_Dimension(p_dim_name varchar2) IS
653
654 PROCEDURE Order_by_Rank IS
655 l_temp varchar2(400);
656 l_temp_rank number;
657 l_temp_map number;
658 l_temp_src number;
659 l_temp_target number;
660
661 --bubble sort?? :( for a few levels its more than enough!
662 Begin
663 if g_debug then
664 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In Order by Rank'||get_time,FND_LOG.LEVEL_STATEMENT);
665 end if;
666 g_level_order:=l_levels;
667 g_mapping_ids:=l_mapping_ids;
668 g_primary_src:=l_primary_src;
669 g_primary_target:=l_primary_target;
670
671 for i in 1..l_number_levels-1 loop
672 for j in i..l_number_levels-1 loop
673 if l_rank(j) < l_rank(j+1) then
674 l_temp:= g_level_order(j+1);
675 g_level_order(j+1):=g_level_order(j);
676 g_level_order(j):=l_temp;
677 l_temp_rank:=l_rank(j+1);
678 l_rank(j+1):=l_rank(j);
679 l_rank(j):=l_temp_rank;
680 l_temp_map:=g_mapping_ids(j+1);
681 g_mapping_ids(j+1):=g_mapping_ids(j);
682 g_mapping_ids(j):=l_temp_map;
683 l_temp_src:=g_primary_src(j+1);
684 g_primary_src(j+1):=g_primary_src(j);
685 g_primary_src(j):=l_temp_src;
686 l_temp_target:=g_primary_target(j+1);
687 g_primary_target(j+1):=g_primary_target(j);
688 g_primary_target(j):=l_temp_target;
689 end if;
690 end loop;
691 end loop;
692 if g_debug then
693 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished order by Rank'||get_time,FND_LOG.LEVEL_STATEMENT);
694 end if;
695 Exception when others then
696 g_status:=false;
697 g_status_message:=sqlerrm;
698 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in order by rank '||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
699 End;--PROCEDURE Order_by_Rank IS
700
701
702
703 PROCEDURE Set_Rank IS
704
705 l_run integer:=0;
706 Begin
707 if g_debug then
708 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In set rank'||get_time,FND_LOG.LEVEL_STATEMENT);
709 end if;
710 --init the rank
711 for i in 1..l_number_levels loop
712 l_rank(i):=0;
713 end loop;
714 Set_l_child_start;
715 Set_Rank_Recursive(l_levels(1),l_rank(1));
716 if g_debug then
717 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished set rank'||get_time,FND_LOG.LEVEL_STATEMENT);
718 end if;
719 Exception when others then
720 g_status:=false;
721 g_status_message:=sqlerrm;
722 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in set rank '||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
723 End;--PROCEDURE Set_Rank
724
725
726 PROCEDURE Set_l_child_start IS
727 l_run integer :=1;
728 Begin
729 if g_debug then
730 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In Set_l_child_start'||get_time,FND_LOG.LEVEL_STATEMENT);
731 end if;
732 for i in 1..l_number_levels loop
733 l_child_start(i):=l_run;
734 for j in 1..l_child_level_number(i) loop
735 l_run:=l_run+1;
736 end loop;
740 end if;
737 end loop;
738 if g_debug then
739 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished Set_l_child_start'||get_time,FND_LOG.LEVEL_STATEMENT);
741 Exception when others then
742 g_status:=false;
743 g_status_message:=sqlerrm;
744 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in Set_l_child_start '||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
745 End;--PROCEDURE Set_l_child_start IS
746
747
748 PROCEDURE Set_Rank_Recursive(p_level_in varchar2, p_rank number) IS
749 l_index integer;
750 Begin
751 --for a level , set the rank of all levels underneath to my rank-1;
752 --do this as a recursion
753 if g_debug then
754 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In Set_Rank_Recursive, params: '||p_level_in||','||p_rank||get_time,
755 FND_LOG.LEVEL_STATEMENT);
756 end if;
757 l_index:=Get_index(p_level_in);
758 if l_rank(l_index) >= p_rank then --only if this rank is greater...
759 Set_Level_Rank(l_index,p_rank);
760 for i in 1..l_child_level_number(l_index) loop
761 Set_Rank_Recursive(l_child_levels(l_child_start(l_index)+(i-1)),p_rank-1);
762 end loop;
763 end if;
764 if g_debug then
765 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished Set_Rank_Recursive'||get_time,FND_LOG.LEVEL_STATEMENT);
766 end if;
767 Exception when others then
768 g_status:=false;
769 g_status_message:=sqlerrm;
770 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in Set_Rank_Recursive '||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
771 End;--PROCEDURE Set_Rank_Recursive(p_level_in varchar2)
772
773
774
775 FUNCTION Get_index(p_level_in varchar2) RETURN NUMBER IS
776 Begin
777 if g_debug then
778 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In Get_index, params: '||p_level_in||get_time,FND_LOG.LEVEL_STATEMENT);
779 end if;
780 for i in 1..l_number_levels loop
781 if l_levels(i)=p_level_in then
782 return i;
783 end if;
784 end loop;
785 if g_debug then
786 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished Get_index'||get_time,FND_LOG.LEVEL_STATEMENT);
787 end if;
788 return 0;
789 End;--FUNCTION Get_index(p_level_in varchar2) RETURN NUMBER IS
790
791
792
793 PROCEDURE Set_Level_Rank(p_index_in integer, p_rank number) IS
794 Begin
795 l_rank(p_index_in):=p_rank;
796 End;
797
798
799 FUNCTION Get_Rank(p_level_in varchar2) RETURN NUMBER IS
800 Begin
801 for i in 1..l_number_levels loop
802 if l_levels(i)=p_level_in then
803 return l_rank(i);
804 end if;
805 end loop;
806 return 0;--should never come here
807 End;--FUNCTION Get_Rank(p_level_in varchar2) RETURN NUMBER
808
809 PROCEDURE Collect_Each_Level IS
810 l_status boolean:=true;
811 l_latest number:=0;
812 l_start_index number:=1;
813 l_object_name varchar2(400);
814 l_object_type varchar2(400);
815 l_temp_log_flag boolean:=false;
816 ----------skipping-----------------------------
817 l_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
818 l_number_skip_cols number;
819 l_src_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
820 l_tgt_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
821 l_numer_cols number;
822 -----------------------------------------------
823 -----------data alignment----------------------
824 l_da_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
825 l_number_da_cols number;
826 l_da_table varchar2(400);
827 l_pp_table varchar2(400);
828 -----------------------------------------------
829 -----------smart update---------------------
830 l_smart_update_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
831 l_number_smart_update_cols number;
832 ---------------------------------------------
833 l_ok_table varchar2(80);
834 --the number of elements in these array=g_max_threads
835 l_ok_low_end EDW_OWB_COLLECTION_UTIL.numberTableType;
836 l_ok_high_end EDW_OWB_COLLECTION_UTIL.numberTableType;
837 l_ok_end_count integer;
838 l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
839 l_number_jobs number;
840 Begin
841 if g_debug then
842 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In Collect_Each_Level'||get_time,FND_LOG.LEVEL_STATEMENT);
843 end if;
844 --for each level, get the mapping id and call the collection program
845 --why start from 2? because the top level need not be collected
846 if upper(g_level_order(1)) = substr(g_object_name,1,length(g_object_name)-2)||'_A_LTC' then
847 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The all level '||g_level_order(1)||' found',FND_LOG.LEVEL_STATEMENT);
848 g_all_level_found:=true;
849 l_start_index:=2;
850 else
851 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('All level '||substr(g_object_name,1,length(g_object_name)-2)||'_A_LTC not found'
852 ,FND_LOG.LEVEL_STATEMENT);
853 g_all_level_found:=false;
854 l_start_index:=1;
855 end if;
856 for i in l_start_index..l_number_levels loop
857 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to collect for '||g_level_order(i),FND_LOG.LEVEL_STATEMENT);
858 l_latest:=i;
859 --pass g_level_order(i)
860 if i=l_number_levels then --log into the temp log table only if this is the lowest level
861 l_temp_log_flag:=true;
862 end if;
863 l_numer_cols:=0;
864 l_number_skip_cols:=0;
865 if EDW_OWB_COLLECTION_UTIL.get_obj_obj_map_details(g_level_order(i),g_object_name,null,l_src_cols,l_tgt_cols,
866 l_numer_cols)=false then
870 EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_status_message,FND_LOG.LEVEL_STATEMENT);
867 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
868 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('EDW_OWB_COLLECTION_UTIL.get_obj_obj_map_details returned with error',
869 FND_LOG.LEVEL_STATEMENT);
871 g_status:=false;
872 return;
873 end if;
874 -------------skip columns------------------------
875 for j in 1..l_numer_cols loop
876 for k in 1..g_number_skip_cols loop
877 if l_tgt_cols(j)=g_skip_cols(k) then
878 l_number_skip_cols:=l_number_skip_cols+1;
879 l_skip_cols(l_number_skip_cols):=l_src_cols(j);
880 exit;
881 end if;
882 end loop;
883 end loop;
884 -------------get data alignment columns---------
885 l_number_da_cols:=0;
886 if g_number_da_cols>0 then
887 for j in 1..l_numer_cols loop
888 for k in 1..g_number_da_cols loop
889 if l_tgt_cols(j)=g_da_cols(k) then
890 l_number_da_cols:=l_number_da_cols+1;
891 l_da_cols(l_number_da_cols):=l_src_cols(j);
892 exit;
893 end if;
894 end loop;
895 end loop;
896 end if;
897 if i=l_number_levels then --this is the lowest level
898 l_da_table:=g_da_table;
899 l_pp_table:=g_pp_table;
900 else
901 l_da_table:=EDW_OWB_COLLECTION_UTIL.get_DA_table(g_level_order(i),g_table_owner);
902 l_pp_table:=EDW_OWB_COLLECTION_UTIL.get_PP_table(g_level_order(i),g_table_owner);
903 end if;
904 -------------smart update columns------------------
905 l_number_smart_update_cols:=0;
906 for k in 1..g_number_smart_update_cols loop
907 for j in 1..l_numer_cols loop
908 if g_smart_update_cols(k)=l_tgt_cols(j) then
909 l_number_smart_update_cols:=l_number_smart_update_cols+1;
910 l_smart_update_cols(l_number_smart_update_cols):=l_src_cols(j);
911 exit;
912 end if;
913 end loop;
914 end loop;
915 -------------------------------------------------
916 insert_into_load_progress_nd(g_load_pk,g_level_order(i),g_primary_target(i),'Collect Level',sysdate,null,'LEVEL',
917 'LEVEL-LOAD',100+i,'I');
918 EDW_MAPPING_COLLECT.COLLECT_MAIN(
919 g_object_name,
920 g_mapping_ids(i),
921 'LEVEL',
922 g_primary_src(i), --LSTG id
923 g_primary_target(i), --LTC id
924 g_level_order(i), --just the name of the LTC for logging
925 g_object_type,
926 g_conc_program_id,
927 g_conc_program_name,
928 l_status,
929 false, --fact audit
930 false, --net change
931 null, --fact audit name
932 null, --net change name
933 null, --fact audit is name
934 null, --net change is name
935 g_debug,
936 g_duplicate_collect,
937 g_exec_flag,
938 g_request_id,
939 g_collection_size,
940 g_parallel,
941 g_table_owner,
942 g_bis_owner,
943 l_temp_log_flag,
944 g_forall_size,
945 g_update_type,
946 g_mode,
947 g_explain_plan_check,
948 null,
949 g_key_set,
950 g_instance_type,
951 g_load_pk,
952 l_skip_cols,
953 l_number_skip_cols,
954 g_fresh_restart,
955 g_op_table_space,
956 l_da_cols,
957 l_number_da_cols,
958 l_da_table,
959 l_pp_table,
960 g_master_instance,
961 g_rollback,
962 g_skip_levels,
963 g_number_skip_levels,
964 g_smart_update,
965 g_fk_use_nl,
966 g_fact_smart_update,
967 g_auto_dang_table_extn,
968 g_auto_dang_recovery,--all the levels get the same flag
969 g_create_parent_table_records,
970 l_smart_update_cols,
971 l_number_smart_update_cols,
972 g_check_fk_change,
973 g_stg_join_nl,
974 g_ok_switch_update,
975 g_stg_make_copy_percentage,
976 g_hash_area_size,
977 g_sort_area_size,
978 g_trace,
979 g_read_cfig_options,
980 g_min_job_load_size,
981 g_sleep_time,
982 g_thread_type,
983 g_max_threads,
984 g_job_status_table,
985 g_analyze_frequency,
986 g_parallel_drill_down,
987 g_dd_status_table
988 );
989 insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,100+i,'U');
990 if l_status=true then
991 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Collect_Each_Level finish collect level '||g_level_order(i)||get_time
992 ,FND_LOG.LEVEL_PROCEDURE);
993 else
994 g_status_message:=EDW_MAPPING_COLLECT.get_status_message;
995 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR Collect_Each_Level finish collect level '||g_level_order(i)||
996 ' WITH ERROR '||g_status_message||get_time,FND_LOG.LEVEL_ERROR);
997 g_status:=false;
998 return;
999 end if;
1000 --commit is handled inside mapping_collect please see procedure collect_records
1001 end loop; --each ltc collection
1002 if g_debug then
1003 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished Collect_Each_Level'||get_time,FND_LOG.LEVEL_PROCEDURE);
1004 end if;
1005 Exception when others then
1006 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR:EDW_ALL_COLLECT.Collect_Each_Level, finish '||
1010 return;
1007 ' collect level '||g_primary_target(l_latest)||' WITH ERROR::'||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
1008 g_status:=false;
1009 g_status_message:=sqlerrm;
1011 End;--PROCEDURE Collect_Each_Level IS
1012
1013 function get_temp_log_data(g_object_name varchar2, g_object_type varchar2) return boolean is
1014 Begin
1015 g_number_ins_req_coll:=1;
1016 if EDW_OWB_COLLECTION_UTIL.get_temp_log_data(
1017 g_object_name,
1018 g_object_type,
1019 null,
1020 g_ins_rows_ready(1),
1021 g_ins_rows_processed(1),
1022 g_ins_rows_collected(1),
1023 g_ins_rows_dangling(1),
1024 g_ins_rows_duplicate(1),
1025 g_ins_rows_error(1),
1026 g_ins_rows_insert(1),
1027 g_ins_rows_update(1),
1028 g_ins_rows_delete(1),
1029 g_ins_instance_name(1),
1030 g_ins_request_id_table(1))=false then
1031 null;
1032 end if;
1033 return true;
1034 EXCEPTION when others then
1035 g_status:=false;
1036 g_status_message:=sqlerrm;
1037 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1038 return false;
1039 End;
1040
1041 function check_if_fact_exists(p_fact_name varchar2) return boolean is
1042 TYPE CurTyp IS REF CURSOR;
1043 cv CurTyp;
1044 l_stmt varchar2(2000);
1045 l_var number:=null;
1046 begin
1047 l_stmt:='select 1 from EDW_FACTS_MD_V where fact_name=:s';
1048 open cv for l_stmt using p_fact_name;
1049 fetch cv into l_var;
1050 close cv;
1051 if l_var is null then
1052 return false;
1053 end if;
1054 return true;
1055 EXCEPTION when others then
1056 begin
1057 close cv;
1058 exception when others then
1059 null;
1060 end;
1061 g_status:=false;
1062 g_status_message:=sqlerrm;
1063 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1064 return false;
1065 End;
1066
1067
1068 procedure Collect_Fact(Errbuf out NOCOPY varchar2,
1069 Retcode out NOCOPY varchar2,
1070 p_fact_name in varchar2) IS
1071
1072 l_status boolean:=true;
1073 l_fact_audit boolean:=true;
1074 l_fact_net_change boolean:=true;
1075 l_audit_name varchar2(400):=null;
1076 l_net_change_name varchar2(400):=null;
1077 l_audit_is_name varchar2(400):=null;
1078 l_net_change_is_name varchar2(400):=null;
1079 l_object_name varchar2(400);
1080 l_object_type varchar2(400);
1081 l_ins_rows_processed number;
1082 l_var number;
1083 Begin
1084 g_collection_start_date:=sysdate;
1085 g_collect_fact:=true;
1086 g_collect_dim:=false;
1087 g_status:=true;
1088 g_conc_program_id:=0;
1089 g_object_name:=p_fact_name;
1090 g_object_type:='FACT';
1091 l_audit_is_name:='FACT_AUDIT';
1092 l_net_change_is_name:='FACT_NET_CHANGE';
1093 g_logical_object_type:='FACT';
1094 g_conc_program_id:=FND_GLOBAL.Conc_request_id;--my conc id
1095 g_conc_program_name :=upper(p_fact_name)||'_T';
1096 retcode:='0';
1097 --EDW_OWB_COLLECTION_UTIL.setup_conc_program_log(p_fact_name);
1098 EDW_OWB_COLLECTION_UTIL.init_all(p_fact_name,null,'bis.edw.loader.load_fact');
1099 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Collect Fact for '||p_fact_name||get_time,FND_LOG.LEVEL_PROCEDURE);
1100 g_load_pk:=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk;
1101 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_load_pk='||g_load_pk,FND_LOG.LEVEL_STATEMENT);
1102 if g_load_pk is null then
1103 errbuf:=g_status_message;
1104 retcode:='2';
1105 return_with_error(g_load_pk,'LOG');
1106 return;
1107 end if;
1108 g_object_id:=EDW_OWB_COLLECTION_UTIL.get_object_id(g_object_name);
1109 if g_object_id=-1 then
1110 errbuf:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1111 retcode:='2';
1112 return_with_error(g_load_pk,'LOG');
1113 return;
1114 end if;
1115
1116 EDW_OWB_COLLECTION_UTIL.set_debug(true);
1117 l_var:= EDW_OWB_COLLECTION_UTIL.is_another_coll_running(g_object_name, g_object_type);
1118 EDW_OWB_COLLECTION_UTIL.set_debug(false);
1119
1120 if l_var=2 then
1121 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Ok to proceed',FND_LOG.LEVEL_STATEMENT);
1122 elsif l_var=1 then
1123 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_NEW_COLLECTION_RUNNING');
1124 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
1125 errbuf:=g_status_message;
1126 retcode:='2';
1127 return_with_error(g_load_pk,'LOG');
1128 return;
1129 elsif l_var=0 then
1130 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1131 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
1132 errbuf:=g_status_message;
1133 retcode:='2';
1134 return_with_error(g_load_pk,'LOG');
1135 return;
1136 end if;
1137 if EDW_OWB_COLLECTION_UTIL.log_collection_start(g_object_name,g_object_id,g_object_type,
1138 g_collection_start_date,g_conc_program_id,g_load_pk)=false then
1139 errbuf:=g_status_message;
1140 retcode:='2';
1141 return_with_error(g_load_pk,'LOG');
1142 return;
1143 end if;
1144
1145 if check_if_fact_exists(p_fact_name) = false then
1146 retcode:='2';
1147 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_FACT_NOT_FOUND');
1148 errbuf:=g_status_message;
1149 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The fact '||p_fact_name||' not found in the metadata ',FND_LOG.LEVEL_STATEMENT);
1150 return_with_error(g_load_pk,'LOG');
1151 return;
1152 end if;
1153 init_all;
1157 return_with_error(g_load_pk,'LOG');
1154 if g_status=false then
1155 errbuf:=g_status_message;
1156 retcode:='2';
1158 return;
1159 end if;
1160 if g_debug then
1161 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('pre_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
1162 end if;
1163 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Coll Hook',sysdate,null,'FACT',
1164 'PRE-COLL-HOOK',20,'I');
1165 if EDW_COLLECTION_HOOK.pre_coll(g_object_name) = true then
1166 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
1167 else
1168 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_coll with error'||get_time,FND_LOG.LEVEL_ERROR);
1169 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_FINISHED_PRECOLL_ERROR');
1170 errbuf:=g_status_message;
1171 retcode:='2';
1172 return_with_error(g_load_pk,'LOG');
1173 return;
1174 end if;
1175 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,20,'U');
1176 if g_debug then
1177 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('pre_fact_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
1178 end if;
1179 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Pre Fact Coll Hook',sysdate,null,'FACT',
1180 'PRE-FACT-COLL-HOOK',21,'I');
1181 if EDW_COLLECTION_HOOK.pre_fact_coll(g_object_name) = true then
1182 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_fact_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
1183 else
1184 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished pre_fact_coll with error '||get_time,FND_LOG.LEVEL_ERROR);
1185 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_PREFACT_COLL_ERROR');
1186 errbuf:=g_status_message;
1187 retcode:='2';
1188 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,21,'U');
1189 return_with_error(g_load_pk,'LOG');
1190 return;
1191 end if;
1192 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,21,'U');
1193 EDW_OWB_COLLECTION_UTIL.set_parallel(g_parallel);
1194 /*
1195 Find if the fact is derived fact or ordinary fact.
1196 */
1197 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(g_object_name,g_object_type,g_skip_cols,g_number_skip_cols)=false then
1198 errbuf:=g_status_message;
1199 retcode:='2';
1200 return_with_error(g_load_pk,'LOG');
1201 return;
1202 end if;
1203 if get_fact_dlog=false then
1204 errbuf:=g_status_message;
1205 retcode:='2';
1206 return_with_error(g_load_pk,'LOG');
1207 return;
1208 end if;
1209 if is_derived_fact(g_object_name)= true then
1210 g_logical_object_type:='DERIVED FACT';
1211 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Derived Fact ',FND_LOG.LEVEL_STATEMENT);
1212 --call the derived fact collection
1213 l_ins_rows_processed:=0;
1214 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Derived Fact Collect',sysdate,null,'FACT',
1215 'DERIVED-FACT-COLLECT',22,'I');
1216 if EDW_DERIVED_FACT_COLLECT.COLLECT_FACT(
1217 g_object_name,
1218 g_conc_program_id,
1219 g_conc_program_name,
1220 g_debug,
1221 g_collection_size,
1222 g_parallel,
1223 g_bis_owner,
1224 g_table_owner,
1225 l_ins_rows_processed,
1226 g_forall_size,
1227 g_update_type,
1228 g_skip_cols,
1229 g_number_skip_cols,
1230 g_load_pk,
1231 g_fresh_restart,
1232 g_op_table_space,
1233 g_rollback,
1234 g_stg_join_nl,
1235 g_thread_type,
1236 g_max_threads,
1237 g_min_job_load_size,
1238 g_sleep_time,
1239 g_hash_area_size,
1240 g_sort_area_size,
1241 g_trace,
1242 g_read_cfig_options
1243 ) = true then
1244 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('EDW_DERIVED_FACT_COLLECT.COLLECT_FACT returned with success '||get_time
1245 ,FND_LOG.LEVEL_PROCEDURE);
1246 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,22,'U');
1247 else
1248 g_status_message:=EDW_DERIVED_FACT_COLLECT.get_status_message;
1249 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
1250 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,22,'U');
1251 g_status:=false;
1252 errbuf:=g_status_message;
1253 retcode:='2';
1254 return_with_error(g_load_pk,'LOG');
1255 return;
1256 end if;
1257 if get_temp_log_data(g_object_name,g_object_type)=false then
1258 errbuf:=g_status_message;
1259 retcode:='2';
1260 return_with_error(g_load_pk,'NO-LOG');
1261 return;
1262 end if;
1263 else
1264 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Normal Fact',FND_LOG.LEVEL_STATEMENT);
1265 --first find out NOCOPY if the fact needs to be tracked or not
1266 begin
1267 select fact_name into l_audit_name from edw_facts_md_v where fact_name=p_fact_name||'_AU' ;
1268 Exception when others then
1269 l_audit_name:=null;
1270 end;
1271 if l_audit_name is null then
1272 l_fact_audit:=false;
1273 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('NO Auditing for fact '||p_fact_name,FND_LOG.LEVEL_STATEMENT);
1274 end if;
1275 if l_fact_audit then
1276 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Audit on for fact '||p_fact_name,FND_LOG.LEVEL_STATEMENT);
1277 end if;
1278 begin
1279 select fact_name into l_net_change_name from edw_facts_md_v where fact_name=p_fact_name||'_NC' ;
1283 if l_net_change_name is null then
1280 Exception when others then
1281 l_net_change_name:=null;
1282 end;
1284 l_fact_net_change:=false;
1285 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('NO Net Change for fact '||p_fact_name,FND_LOG.LEVEL_ERROR);
1286 end if;
1287 if l_fact_net_change then
1288 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Net Change on for fact '||p_fact_name,FND_LOG.LEVEL_STATEMENT);
1289 end if;
1290 --get the primary src and target and call collect
1291 EDW_OWB_COLLECTION_UTIL.Get_Fact_Ids(
1292 p_fact_name,
1293 g_fact_map_id,
1294 g_fact_src,
1295 g_fact_target) ;
1296 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished EDW_OWB_COLLECTION_UTIL.Get_Fact_Ids,
1297 Time '||get_time,FND_LOG.LEVEL_STATEMENT);
1298 -------
1299 /*
1300 3529591
1301 */
1302 if edw_owb_collection_util.clean_ilog_dlog_base_fact(g_object_name,g_table_owner,g_bis_owner,
1303 g_fact_target,g_fact_dlog)=false then
1304 errbuf:=edw_owb_collection_util.g_status_message;
1305 retcode:='2';
1306 return_with_error(g_load_pk,'LOG');
1307 return;
1308 end if;
1309 -------
1310 insert_into_load_progress_nd(g_load_pk,g_object_name,g_object_id,'Load Fact',sysdate,null,'FACT','FACT','LF','I');
1311 EDW_MAPPING_COLLECT.COLLECT_MAIN(
1312 g_object_name,
1313 g_fact_map_id,
1314 'FACT',
1315 g_fact_src,
1316 g_fact_target,
1317 p_fact_name,
1318 g_object_type,
1319 g_conc_program_id,
1320 g_conc_program_name,
1321 l_status,
1322 l_fact_audit,
1323 l_fact_net_change,
1324 l_audit_name,
1325 l_net_change_name,
1326 l_audit_is_name,
1327 l_net_change_is_name,
1328 g_debug,
1329 g_duplicate_collect,
1330 g_exec_flag,
1331 g_request_id,
1332 g_collection_size,
1333 g_parallel,
1334 g_table_owner,
1335 g_bis_owner,
1336 true,
1337 g_forall_size,
1338 g_update_type,--for facts, we need to log into temp log table
1339 g_mode,
1340 g_explain_plan_check,
1341 g_fact_dlog,
1342 g_key_set,
1343 g_instance_type,
1344 g_load_pk,
1345 g_skip_cols,
1346 g_number_skip_cols,
1347 g_fresh_restart,
1348 g_op_table_space,
1349 g_da_cols,
1350 g_number_da_cols,
1351 null,--g_da_table
1352 null,--g_pp_table
1353 g_master_instance,
1354 g_rollback,
1355 g_skip_levels,
1356 g_number_skip_levels,
1357 g_smart_update,
1358 g_fk_use_nl,
1359 g_fact_smart_update,
1360 g_auto_dang_table_extn,
1361 g_auto_dang_recovery,
1362 g_create_parent_table_records,
1363 g_smart_update_cols,
1364 g_number_smart_update_cols,
1365 g_check_fk_change,
1366 g_stg_join_nl,
1367 g_ok_switch_update,
1368 g_stg_make_copy_percentage,
1369 g_hash_area_size,
1370 g_sort_area_size,
1371 g_trace,
1372 g_read_cfig_options,
1373 g_min_job_load_size,
1374 g_sleep_time,
1375 g_thread_type,
1376 g_max_threads,
1377 g_job_status_table,
1378 g_analyze_frequency,
1379 false,
1380 null
1381 );
1382 insert_into_load_progress_nd(g_load_pk,null,null,null,null,sysdate,null,null,'LF','U');
1383 if l_status=true then
1384 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('FINISH : EDW_ALL_COLLECT.Collect_Fact, Time '||
1385 get_time,FND_LOG.LEVEL_PROCEDURE);
1386 else
1387 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('FINISH with ERROR: EDW_ALL_COLLECT.Collect_Fact '||
1388 EDW_MAPPING_COLLECT.get_status_message||' Time '||get_time,FND_LOG.LEVEL_PROCEDURE);
1389 g_status:=false;
1390 g_status_message:=EDW_MAPPING_COLLECT.get_status_message;
1391 errbuf:=g_status_message;
1392 retcode:='2';
1393 return_with_error(g_load_pk,'LOG');
1394 return;
1395 end if;
1396 if get_temp_log_data(g_object_name, g_object_type)=false then
1397 errbuf:=g_status_message;
1398 retcode:='2';
1399 return_with_error(g_load_pk,'NO-LOG');
1400 return;
1401 end if;
1402 end if; --if this is normal fact
1403 if g_debug then
1404 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('post_fact_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
1405 end if;
1406 insert_into_load_progress(g_load_pk,g_object_name,g_object_id,'Post Fact Coll Hook',sysdate,null,'POST-FACT',
1407 'POST-FACT',23,'I');
1408 if EDW_COLLECTION_HOOK.post_fact_coll(g_object_name) = true then
1409 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_fact_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
1410 else
1411 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_fact_coll with error'||get_time,FND_LOG.LEVEL_ERROR);
1412 g_status_message:=EDW_OWB_COLLECTION_UTIL.get_message('EDW_POSTFACT_COLL_ERROR');
1413 errbuf:=g_status_message;
1414 retcode:='2';
1415 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,23,'U');
1416 return_with_error(g_load_pk,'LOG');
1417 return;
1418 end if;
1419 insert_into_load_progress(g_load_pk,null,null,null,null,sysdate,null,null,23,'U');
1420 return_with_success('LOG',null,g_load_pk);--log into edw_collection_detail_log
1421 declare
1422 l_load_pk number:=null;
1423 /*
1427 begin
1424 delete_object_log_tables is contained in refresh_all_derived_facts
1425 In this the snp log of base fact is truncated
1426 */
1428 if g_logical_object_type='FACT' then
1429 if refresh_all_derived_facts=false then
1430 errbuf:=g_status_message;
1431 g_status:=false;
1432 retcode:='2';
1433 return;
1434 end if;
1435 end if;
1436 end;
1437 clean_up;--cleans up the progress log
1438 /***************************************************************************/
1439 /******************this call is for workflow for now...*********************/
1440 if g_debug then
1441 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('post_coll'||get_time,FND_LOG.LEVEL_PROCEDURE);
1442 end if;
1443 if EDW_COLLECTION_HOOK.post_coll(g_object_name) = true then
1444 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_coll with success'||get_time,FND_LOG.LEVEL_PROCEDURE);
1445 else
1446 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished post_coll with error'||get_time,FND_LOG.LEVEL_ERROR);
1447 end if;
1448 /*******************END WORKFLOW API*****************************************/
1449 Exception when others then
1450 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('ERROR:EDW_ALL_COLLECT.Collect_Fact,
1451 ERROR '||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
1452 g_status:=false;
1453 g_status_message:=sqlerrm;
1454 errbuf:=g_status_message;
1455 retcode:='2';
1456 return_with_error(g_load_pk,'LOG');
1457 return;
1458 End;--procedure Collect_Fact(p_fact_name varchar2) IS
1459
1460 procedure clean_up is
1461 Begin
1462 if EDW_OWB_COLLECTION_UTIL.record_coll_progress(
1463 g_object_name,
1464 g_object_type,
1465 null,
1466 null,
1467 'DELETE') = false then
1468 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1469 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
1470 g_status:=false;
1471 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Util.record_coll_progress returned with error for delete',
1472 FND_LOG.LEVEL_STATEMENT);
1473 end if;
1474 --if there is push down, drop the ilogs
1475 if g_dim_push_down then
1476 EDW_PUSH_DOWN_DIMS.drop_ilog;
1477 end if;
1478 Exception when others then
1479 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in clean up',FND_LOG.LEVEL_ERROR);
1480 End;
1481
1482 procedure init_all IS
1483 l_hash_area_size number;
1484 l_sort_area_size number;
1485 l_key_set varchar2(400);
1486 begin
1487 g_status:=true;
1488 g_status_message:=' ';
1489 g_number_rows_processed:=0;
1490 g_ok_switch_update:=5;--% when to swith to update for ok table
1491 g_stg_join_nl:=25; --% when to force using NL in staging table lookup
1492 g_stg_make_copy_percentage:=0; --% below which make a copy of stg to process 0 means turned off
1493 g_max_threads:=1; --increasing this also means that the db parameter job_queue_processes must be set
1494 --correctly. if g_max_threads=10, then job_queue_processes >=10
1495 g_min_job_load_size:=50000;
1496 g_sleep_time:=15; --15 seconds sleep time. Its used to sync multiple threads
1497 g_auto_dang_recovery:=false;
1498 if EDW_OWB_COLLECTION_UTIL.does_table_have_data('EDW_CFIG_OPTIONS')=2 then
1499 g_read_cfig_options:=true;
1500 else
1501 g_read_cfig_options:=false;
1502 end if;
1503 EDW_OWB_COLLECTION_UTIL.set_g_read_cfig_options(g_read_cfig_options);
1504 if g_read_cfig_options then
1505 if read_config_options=false then
1506 return;
1507 end if;
1508 else
1509 if read_profile_options=false then
1510 return;
1511 end if;
1512 end if;
1513 /*************************************************************************************/
1514 g_instance_type:='MULTIPLE';--SINGLE vs MULTIPLE
1515 g_exec_flag:=true;
1516 g_number_ins_req_coll:=0;
1517 g_diamond_issue:=false;
1518 g_number_derived_facts:=0;
1519 g_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_object_name);
1520 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The owner for table '||g_object_name||' is '||g_table_owner,
1521 FND_LOG.LEVEL_STATEMENT);
1522 g_forall_size:=5000;
1523 g_fact_dlog:=g_bis_owner||'.'||substr(g_object_name,1,26)||'DLG'; --delete log for facts
1524 g_analyze_frequency:=30;
1525 g_max_fk_density:=5; --5% see EDWSCOLB.pls
1526 g_number_tables_to_drop:=0;
1527 g_number_da_cols:=0;
1528 g_da_table:=EDW_OWB_COLLECTION_UTIL.get_DA_table(g_object_name,g_table_owner);
1529 g_pp_table:=EDW_OWB_COLLECTION_UTIL.get_PP_table(g_object_name,g_table_owner);
1530 g_master_instance:=EDW_OWB_COLLECTION_UTIL.get_master_instance(g_object_name);
1531 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Master Instance '||g_master_instance,FND_LOG.LEVEL_STATEMENT);
1532 g_number_skip_levels:=0;
1533 set_g_fact_smart_update;
1534 g_auto_dang_table_extn:='EDW_ADR';
1535 if g_object_type='DIMENSION' then
1536 if EDW_OWB_COLLECTION_UTIL.drop_table(g_bis_owner||'.'||substr(g_object_name,1,27)||
1537 g_auto_dang_table_extn)=false then
1538 null;
1539 end if;
1540 end if;
1541 g_check_fk_change:=true;
1542 g_check_fk_change_number:=60;
1543 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('MAX Threads='||g_max_threads,FND_LOG.LEVEL_STATEMENT);
1544 g_job_status_table:=g_bis_owner||'.MAIN_'||g_object_id||'_JOB_STATUS';
1548 g_thread_type:='JOB';
1545 g_job_queue_processes:=EDW_OWB_COLLECTION_UTIL.get_job_queue_processes;
1546 g_thread_type:=set_thread_type(g_max_threads,g_job_queue_processes);
1547 if g_thread_type is null then
1549 end if;
1550 if g_job_queue_processes is null then
1551 g_job_queue_processes:=g_max_threads;
1552 end if;
1553 if g_thread_type='JOB' then
1554 if g_max_threads>g_job_queue_processes then
1555 g_max_threads:=g_job_queue_processes;
1556 if g_debug then
1557 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('MAX Threads reset to max jobs ='||g_max_threads,FND_LOG.LEVEL_STATEMENT);
1558 end if;
1559 end if;
1560 end if;
1561 g_parallel_drill_down:=false;
1562 g_dd_status_table:=g_bis_owner||'.TAB_DD_'||g_object_id;--used in parallel drill down
1563 Exception when others then
1564 g_status_message:=sqlerrm;
1565 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1566 g_status:=false;
1567 End;--procedure init_all IS
1568
1569 procedure set_g_fact_smart_update is
1570 Begin
1571 g_fact_smart_update:=50;--number of columns below which there is smart update for facts
1572 if g_parallel is not null and g_parallel>1 then
1573 g_fact_smart_update:=150;
1574 if g_max_threads is not null and g_max_threads>1 then
1575 g_fact_smart_update:=g_fact_smart_update+(g_max_threads/2)*(g_fact_smart_update/2);
1576 end if;
1577 end if;
1578 if g_debug then
1579 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_fact_smart_update='||g_fact_smart_update,FND_LOG.LEVEL_STATEMENT);
1580 end if;
1581 Exception when others then
1582 g_fact_smart_update:=50;
1583 End;
1584
1585 function get_status_message return varchar2 is
1586 begin
1587 return g_status_message;
1588 End;--function get_status_message return varchar2 is
1589
1590
1591 procedure return_with_error(p_load_pk number,p_log varchar2) is
1592 l_status_message EDW_OWB_COLLECTION_UTIL.varcharTableType;
1593 begin
1594 if g_debug then
1595 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In return_with_error'||get_time,FND_LOG.LEVEL_ERROR);
1596 EDW_OWB_COLLECTION_UTIL.write_to_log_file('p_load_pk='||p_load_pk,FND_LOG.LEVEL_ERROR);
1597 end if;
1598 rollback;
1599 if g_debug then
1600 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('rollback',FND_LOG.LEVEL_ERROR);
1601 end if;
1602 if p_log<>'NO-LOG' then
1603 if get_temp_log_data(g_object_name, g_object_type)=false then
1604 null;
1605 end if;
1606 get_rows_processed;
1607 end if;
1608 for i in 1..g_number_ins_req_coll loop
1609 l_status_message(i):=g_status_message;
1610 end loop;
1611 write_to_collection_log(false,l_status_message,null, p_load_pk);
1612 commit;
1613 if g_debug then
1614 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('commit',FND_LOG.LEVEL_STATEMENT);
1615 end if;
1616 if g_debug then
1617 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished return_with_error'||get_time,FND_LOG.LEVEL_ERROR);
1618 end if;
1619 Exception when others then
1620 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in return_with_error '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1621 g_status_message:=sqlerrm;
1622 g_status:=false;
1623 End;--procedure return_with_error is
1624
1625 procedure return_with_success(p_command varchar2,p_start_date date, p_load_pk number) is
1626 l_status_message EDW_OWB_COLLECTION_UTIL.varcharTableType;
1627 begin
1628 if g_debug then
1629 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In return_with_success, p_command is '||p_command,FND_LOG.LEVEL_STATEMENT);
1630 EDW_OWB_COLLECTION_UTIL.write_to_log_file('p_start_date='||p_start_date||',p_load_pk='||p_load_pk,FND_LOG.LEVEL_STATEMENT);
1631 end if;
1632 if p_command='LOG' then
1633 if g_debug then
1634 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to Log',FND_LOG.LEVEL_STATEMENT);
1635 end if;
1636 get_rows_processed;
1637 if g_collect_dim then
1638 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Rows Processed into the dimension '||g_dim_rows_processed,
1639 FND_LOG.LEVEL_STATEMENT);
1640 end if;
1641 g_status_message:=make_collection_log_message(l_status_message);
1642 write_to_collection_log(true,l_status_message,p_start_date,p_load_pk);
1643 end if;
1644 commit;
1645 if g_debug then
1646 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('commit',FND_LOG.LEVEL_STATEMENT);
1647 end if;
1648 if g_debug then
1649 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Finished return_with_success'||get_time,FND_LOG.LEVEL_STATEMENT);
1650 end if;
1651 Exception when others then
1652 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in return_with_success '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1653 g_status_message:=sqlerrm;
1654 g_status:=false;
1655 End;--procedure return_with_success
1656
1657 function make_collection_log_message(l_status_message out NOCOPY EDW_OWB_COLLECTION_UTIL.varcharTableType)
1658 return varchar2 is
1659 l_status varchar2(2000);
1660 l_ready number:=0;
1661 l_processed number:=0;
1662 l_collected number:=0;
1663 l_dangling number:=0;
1664 l_duplicate number:=0;
1665 l_error number:=0;
1666 begin
1667 if g_debug then
1668 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In make_collection_log_message',FND_LOG.LEVEL_STATEMENT);
1669 end if;
1673 l_collected:=l_collected+g_ins_rows_collected(i);
1670 for i in 1..g_number_ins_req_coll loop
1671 l_ready:=l_ready+g_ins_rows_ready(i);
1672 l_processed:=l_processed+g_ins_rows_processed(i);
1674 l_dangling:=l_dangling+g_ins_rows_dangling(i);
1675 l_duplicate:=l_duplicate+g_ins_rows_duplicate(i);
1676 l_error:=l_error+g_ins_rows_error(i);
1677 FND_MESSAGE.SET_NAME('BIS','EDW_COLL_DETAIL_LOG_MESSAGE');
1678 FND_MESSAGE.SET_TOKEN('READY',g_ins_rows_ready(i));
1679 FND_MESSAGE.SET_TOKEN('PROCESSED',g_ins_rows_processed(i));
1680 FND_MESSAGE.SET_TOKEN('COLLECTED',g_ins_rows_collected(i));
1681 FND_MESSAGE.SET_TOKEN('DANGLING',g_ins_rows_dangling(i));
1682 FND_MESSAGE.SET_TOKEN('DUPLICATE',g_ins_rows_duplicate(i));
1683 FND_MESSAGE.SET_TOKEN('ERROR',g_ins_rows_error(i));
1684 l_status_message(i):=FND_MESSAGE.GET;
1685 end loop;
1686 l_status:=null;
1687 if l_collected > g_dim_rows_processed then
1688 --l_status:='WARNING! Rows processed into star table:'||g_dim_rows_processed||' and '||
1689 --' rows processed into lowest level level table is:'||l_collected||'. ';
1690 l_status:='Please make sure that the dimension table and lowest level LTC table are in sync';
1691 g_diamond_issue:=true;
1692 end if;
1693 l_status:=l_status||'Ready records '||l_ready||', Processed '||l_processed||', Actually Collected '||
1694 l_collected||', Dangling '||l_dangling||', Duplicate '||l_duplicate||', Error '||l_error;
1695 return l_status;
1696 Exception when others then
1697 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in make_collection_log_message '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1698 g_status_message:=sqlerrm;
1699 g_status:=false;
1700 return null;
1701 End;
1702
1703 procedure get_rows_processed is
1704 begin
1705 if g_debug then
1706 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In get_rows_processed'||get_time,FND_LOG.LEVEL_STATEMENT);
1707 end if;
1708 /*
1709 if g_collect_dim then
1710 g_dim_rows_processed:=EDW_SUMMARY_COLLECT.get_number_rows_processed;
1711 for i in 1..g_number_ins_req_coll loop
1712 g_ins_rows_collected(i):=g_dim_rows_processed;
1713 end loop;
1714 end if;*/
1715 Exception when others then
1716 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in get_rows_processed '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1717 g_status_message:=sqlerrm;
1718 g_status:=false;
1719 End;--procedure get_rows_processed is
1720
1721 procedure write_to_collection_log(p_flag boolean, p_message EDW_OWB_COLLECTION_UTIL.varcharTableType,
1722 p_collection_start_date date, p_load_pk number) is
1723 /*
1724 right now we go with logging into the table only if there is no error in the
1725 collection engine (retcode='2')
1726 */
1727 l_status varchar2(200);
1728 begin
1729 if g_debug then
1730 if p_flag then
1731 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In write_to_collection_log, status is OK',FND_LOG.LEVEL_STATEMENT);
1732 else
1733 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In write_to_collection_log, status is ERROR',FND_LOG.LEVEL_STATEMENT);
1734 end if;
1735 end if;
1736 g_collection_end_date:=sysdate;
1737 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The end time for collection '||g_collection_end_date,FND_LOG.LEVEL_PROCEDURE);
1738 for i in 1..g_number_ins_req_coll loop
1739 if p_flag then
1740 if g_ins_rows_collected(i)<g_ins_rows_ready(i) then
1741 l_status:='PARTIAL';
1742 else
1743 l_status:='SUCCESS';
1744 end if;
1745 else
1746 l_status:='ERROR';
1747 end if;
1748 if EDW_OWB_COLLECTION_UTIL.write_to_collection_log(
1749 g_object_name,
1750 g_object_id,
1751 g_object_type,
1752 g_conc_program_id,
1753 g_collection_start_date,
1754 g_collection_end_date,
1755 g_ins_rows_ready(i),
1756 g_ins_rows_processed(i),
1757 g_ins_rows_collected(i),
1758 g_ins_rows_insert(i),
1759 g_ins_rows_update(i),
1760 g_ins_rows_delete(i),
1761 p_message(i),
1762 l_status,
1763 p_load_pk)= false then
1764 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
1765 g_status:=false;
1766 return;
1767 end if;
1768 end loop;
1769 Exception when others then
1770 g_status_message:=sqlerrm;
1771 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1772 g_status:=false;
1773 End;
1774
1775 procedure write_to_error_log(p_message varchar2) is
1776 l_type varchar2(200);
1777 l_status varchar2(200);
1778 begin
1779 if g_debug then
1780 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In write_to_error_log, p_message is '||p_message,FND_LOG.LEVEL_PROCEDURE);
1781 end if;
1782 l_type:='COLLECTION'; --NLS?
1783 l_status:='ERROR';
1784 g_collection_end_date:=sysdate;
1785 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The end time for collection '||g_collection_end_date,FND_LOG.LEVEL_PROCEDURE);
1786 if EDW_OWB_COLLECTION_UTIL.write_to_error_log(
1787 g_object_name,
1788 g_object_type,
1789 l_type,
1790 g_conc_program_id,
1791 g_collection_start_date,
1792 g_collection_end_date,
1793 p_message,
1794 l_status,
1795 g_resp_id) = false then
1796 g_status_message:=sqlerrm;
1797 g_status:=false;
1801 g_status_message:=sqlerrm;
1798 return;
1799 end if;
1800 Exception when others then
1802 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1803 g_status:=false;
1804 End;
1805
1806 function is_derived_fact(p_fact varchar2) return boolean is
1807 l_stmt varchar2(2000);
1808 l_var number:=null;
1809 TYPE CurTyp IS REF CURSOR;
1810 cv CurTyp;
1811 begin
1812 if g_debug then
1813 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In is_derived_fact, params :'||p_fact||get_time,FND_LOG.LEVEL_STATEMENT);
1814 end if;
1815 --not checked
1816 l_stmt:='select 1 from edw_pvt_map_properties_md_v map, EDW_FACTS_MD_V tgt, EDW_FACTS_MD_V src '||
1817 ' where tgt.fact_name=:a and map.Primary_target=tgt.fact_id and map.Primary_source=src.fact_id and rownum=1';
1818 open cv for l_stmt using p_fact;
1819 fetch cv into l_var;
1820 close cv;
1821 if l_var =1 then
1822 return true;
1823 end if;
1824 return false;
1825 Exception when others then
1826 g_status_message:=sqlerrm;
1827 g_status:=false;
1828 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in is_derived_fact '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1829 return false;
1830 End;
1831
1832 function is_source_for_derived_fact return boolean is
1833 l_stmt varchar2(5000);
1834 TYPE CurTyp IS REF CURSOR;
1835 cv CurTyp;
1836 l_var number:=null;
1837 begin
1838 if g_debug then
1839 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In is_source_for_derived_fact',FND_LOG.LEVEL_STATEMENT);
1840 end if;
1841 --not checked
1842 l_stmt:='select 1 from edw_pvt_map_properties_md_v map, edw_facts_md_v tgt '||
1843 ' where map.primary_target=tgt.fact_id and map.primary_source=:a and rownum=1';
1844 open cv for l_stmt using g_fact_target;
1845 fetch cv into l_var;
1846 close cv;
1847 if l_var =1 then
1848 return true;
1849 else
1850 return false;
1851 end if;
1852 Exception when others then
1853 g_status_message:=sqlerrm;
1854 g_status:=false;
1855 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in is_source_for_derived_fact '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
1856 return false;
1857 End;
1858
1859 function refresh_all_derived_facts return boolean is
1860 l_load_pk EDW_OWB_COLLECTION_UTIL.numberTableType;
1861 l_ins_rows_processed EDW_OWB_COLLECTION_UTIL.numberTableType;
1862 l_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
1863 l_message EDW_OWB_COLLECTION_UTIL.varcharTableType;
1864 l_number_derv_facts number;
1865 l_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType;
1866 l_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType;
1867 l_number_bu_tables number:=0;
1868 l_bu_src_fact varchar2(400):=null;
1869 l_load_mode varchar2(400);
1870 l_date date;
1871 l_derived_facts EDW_OWB_COLLECTION_UTIL.varcharTableType;
1872 l_derived_fact_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
1873 l_map_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
1874 l_number_derived_facts number;
1875 Begin
1876 if g_debug then
1877 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In refresh_all_derived_facts',FND_LOG.LEVEL_STATEMENT);
1878 end if;
1879 if is_source_for_derived_fact = false then
1880 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('This fact is not a source for any derived fact',FND_LOG.LEVEL_STATEMENT);
1881 return true;
1882 end if;
1883 if g_status=false then
1884 return false;
1885 end if;
1886 --if this base fact is not analyzed, analye it
1887 l_date:=EDW_OWB_COLLECTION_UTIL.get_last_analyzed_date(g_object_name,g_table_owner);
1888 if g_debug then
1889 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Last analyzed date for '||g_object_name||' is '||l_date,FND_LOG.LEVEL_STATEMENT);
1890 end if;
1891 if l_date is null then
1892 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(g_object_name,g_table_owner,1);
1893 end if;
1894 l_number_derived_facts:=0;
1895 if EDW_DERIVED_FACT_COLLECT.COLLECT_FACT_INC(
1896 g_object_name,
1897 g_fact_target,
1898 l_derived_facts,--dummy
1899 l_derived_fact_ids,--dummy
1900 l_map_ids,--dummy
1901 l_number_derived_facts,--dummy
1902 g_conc_program_id,
1903 g_conc_program_name,
1904 g_debug,
1905 g_collection_size,
1906 g_parallel,
1907 g_bis_owner,
1908 g_table_owner,--src fact owner
1909 l_load_pk,
1910 l_ins_rows_processed,
1911 l_status,
1912 l_message,
1913 l_number_derv_facts,
1914 g_forall_size,
1915 g_update_type,
1916 g_fact_dlog,
1917 g_fresh_restart,
1918 g_op_table_space,
1919 l_bu_tables,--dummy
1920 l_bu_dimensions,--dummy
1921 l_number_bu_tables,--dummy
1922 l_bu_src_fact,--dummy
1923 l_load_mode,--dummy
1924 g_rollback,
1925 g_stg_join_nl,
1926 g_thread_type,
1927 g_max_threads,
1928 g_min_job_load_size,
1929 g_sleep_time,
1930 g_hash_area_size,
1931 g_sort_area_size,
1932 g_trace,
1933 g_read_cfig_options,
1934 g_job_queue_processes
1935 )=false then
1936 g_status_message:=EDW_DERIVED_FACT_COLLECT.get_status_message;
1937 g_status:=false;
1938 return false;
1939 end if;
1940 return true;
1941 Exception when others then
1942 g_status_message:=sqlerrm;
1943 g_status:=false;
1947
1944 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in refresh_all_derived_facts '||g_status_message,FND_LOG.LEVEL_ERROR);
1945 return false;
1946 End;
1948 function get_snapshot_log return boolean is
1949 Begin
1950 if g_debug then
1951 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In get_snapshot_log',FND_LOG.LEVEL_STATEMENT);
1952 end if;
1953 for i in 1..l_number_levels loop
1954 g_level_snapshot_logs(i):=EDW_OWB_COLLECTION_UTIL.get_table_snapshot_log(l_levels(i));
1955 end loop;
1956 return true;
1957 Exception when others then
1958 g_status:=false;
1959 g_status_message:=sqlerrm;
1960 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
1961 return false;
1962 End;
1963
1964
1965 function get_time return varchar2 is
1966 begin
1967 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1968 Exception when others then
1969 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in get_time '||sqlerrm,FND_LOG.LEVEL_ERROR);
1970 End;
1971
1972 procedure write_to_log_file(p_message varchar2) is
1973 begin
1974 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1975 Exception when others then
1976 null;
1977 End;
1978
1979 procedure write_to_log_file_n(p_message varchar2) is
1980 begin
1981 EDW_OWB_COLLECTION_UTIL.write_to_log_file(' ');
1982 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1983 Exception when others then
1984 null;
1985 End;
1986
1987 procedure Collect_Object(Errbuf out NOCOPY varchar2,
1988 Retcode out NOCOPY varchar2,
1989 p_object_name in varchar2) is
1990 l_stmt varchar2(20000);
1991 TYPE CurTyp IS REF CURSOR;
1992 cv CurTyp;
1993 l_object_name varchar2(400);
1994 Begin
1995 --p_object_name can be long name or short name
1996 Retcode:='0';
1997 l_stmt:='select dim_name from edw_dimensions_md_v where dim_name=:a or dim_long_name=:b';
1998 l_object_name:=null;
1999 open cv for l_stmt using p_object_name,p_object_name;
2000 fetch cv into l_object_name;
2001 close cv;
2002 if l_object_name is not null then
2003 Collect_Dimension(errbuf,retcode,l_object_name);
2004 return;
2005 end if;
2006 l_stmt:='select fact_name from edw_facts_md_v where fact_name=:a or fact_longname=:b';
2007 l_object_name:=null;
2008 open cv for l_stmt using p_object_name,p_object_name;
2009 fetch cv into l_object_name;
2010 close cv;
2011 if l_object_name is not null then
2012 Collect_Fact(errbuf,retcode,l_object_name);
2013 return;
2014 end if;
2015 Exception when others then
2016 g_status:=false;
2017 g_status_message:=sqlerrm;
2018 Errbuf:=g_status_message;
2019 Retcode:='2';
2020 End;
2021
2022 function get_fact_dlog return boolean is
2023 l_dlog varchar2(400):=null;
2024 l_stmt varchar2(4000);
2025 l_owner varchar2(400);
2026 Begin
2027 if g_debug then
2028 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In get_fact_dlog',FND_LOG.LEVEL_STATEMENT);
2029 end if;
2030 l_dlog:=EDW_OWB_COLLECTION_UTIL.get_log_for_table(g_object_name,'Delete Log');
2031 if l_dlog is null then
2032 l_dlog:=g_fact_dlog;
2033 if g_fresh_restart then
2034 if EDW_OWB_COLLECTION_UTIL.drop_table(l_dlog)=false then
2035 null;
2036 end if;
2037 end if;
2038 if EDW_OWB_COLLECTION_UTIL.check_table(l_dlog)=false then
2039 l_stmt:='create table '||l_dlog||' tablespace '||g_op_table_space||
2040 ' as select '||g_object_name||'.*,'||g_object_name||'.rowid row_id from '||
2041 g_object_name||' where 1=2';
2042 if g_debug then
2043 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to execute '||l_stmt,FND_LOG.LEVEL_STATEMENT);
2044 end if;
2045 execute immediate l_stmt;
2046 end if;
2047 else
2048 g_fact_dlog:=l_dlog;
2049 l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(g_fact_dlog);
2050 if g_fresh_restart then
2051 if EDW_OWB_COLLECTION_UTIL.truncate_table(g_fact_dlog,l_owner)=false then
2052 null;
2053 end if;
2054 end if;
2055 l_stmt:='alter table '||l_owner||'.'||g_fact_dlog||' add (row_id rowid)';
2056 begin
2057 if g_debug then
2058 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to execute '||l_stmt,FND_LOG.LEVEL_STATEMENT);
2059 end if;
2060 execute immediate l_stmt;
2061 exception when others then
2062 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Column already there',FND_LOG.LEVEL_STATEMENT);
2063 end;
2064 end if;
2065 return true;
2066 Exception when others then
2067 g_status_message:=sqlerrm;
2068 g_status:=false;
2069 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2070 return false;
2071 End;
2072
2073 procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_object_id number,p_load_progress varchar2,
2074 p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
2075 Begin
2076 EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,p_object_id,p_load_progress,p_start_date,
2077 p_end_date,p_category,p_operation,p_seq_id,p_flag,1);
2078 commit;
2079 Exception when others then
2080 g_status_message:=sqlerrm;
2084 --if debug is turned off
2081 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2082 End;
2083
2085 procedure insert_into_load_progress_nd(p_load_fk number,p_object_name varchar2,p_object_id number,p_load_progress varchar2,
2086 p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2) is
2087 Begin
2088 if g_debug=false then
2089 EDW_OWB_COLLECTION_UTIL.insert_into_load_progress(p_load_fk,p_object_name,p_object_id,p_load_progress,p_start_date,
2090 p_end_date,p_category,p_operation,p_seq_id,p_flag,1);
2091 commit;
2092 end if;
2093 Exception when others then
2094 g_status_message:=sqlerrm;
2095 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2096 End;
2097
2098 procedure reset_profiles is
2099 Begin
2100 if g_debug then
2101 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In reset_profiles'||get_time,FND_LOG.LEVEL_STATEMENT);
2102 end if;
2103 if g_read_cfig_options then
2104 if read_config_options=false then
2105 return;
2106 end if;
2107 else
2108 if read_profile_options=false then
2109 return;
2110 end if;
2111 end if;
2112 Exception when others then
2113 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in reset_profiles '||sqlerrm||get_time,FND_LOG.LEVEL_ERROR);
2114 End;
2115
2116 /*
2117 refresh all derv facts that have this dim in their map
2118 */
2119 function refresh_dim_derv_facts(p_dim_name varchar2,p_load_fk out NOCOPY number) return boolean is
2120 l_fact_name varchar2(400);
2121 l_src_fact_name varchar2(400);
2122 l_src_fact_id number;
2123 l_table_owner varchar2(400);
2124 l_ins_rows_processed number;
2125 l_ilog varchar2(400);
2126 l_dlog varchar2(400);
2127 l_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
2128 l_number_skip_cols number;
2129 l_df_load_pk EDW_OWB_COLLECTION_UTIL.numberTableType;
2130 l_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType;--before update tables.prop dim change to derv
2131 l_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType;
2132 l_number_bu_tables number;
2133 l_bu_src_fact varchar2(400);--what table to look at as the src fact. if null, scan the actual src fact
2134 l_load_mode varchar2(400);
2135 l_dim_id number;
2136 l_derv_bu_map_src_table varchar2(400);
2137 l_prot_delete varchar2(400);
2138 l_prot_update varchar2(400);
2139 l_looked_at EDW_OWB_COLLECTION_UTIL.numberTableType;
2140 l_number_looked_at number;
2141 l_inc_flag boolean;
2142 l_found boolean;
2143 L_DERV_MAPS EDW_OWB_COLLECTION_UTIL.numberTableType;
2144 l_number_derv_maps number;
2145 Begin
2146 --get an idea about which all maps to refresh. find out NOCOPY what cols belong to what maps.
2147 if g_debug then
2148 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In refresh_dim_derv_facts '||get_time,FND_LOG.LEVEL_STATEMENT);
2149 end if;
2150 g_dim_derv_map_id:=EDW_SUMMARY_COLLECT.g_dim_derv_map_id;
2151 g_derv_fact_id:=EDW_SUMMARY_COLLECT.g_derv_fact_id;
2152 g_dim_derv_map_refresh:=EDW_SUMMARY_COLLECT.g_dim_derv_map_refresh;
2153 g_dim_derv_map_full_refresh:=EDW_SUMMARY_COLLECT.g_dim_derv_map_full_refresh;
2154 g_number_dim_derv_map_id:=EDW_SUMMARY_COLLECT.g_number_dim_derv_map_id;
2155 g_before_update_table:=EDW_SUMMARY_COLLECT.g_before_update_table;--pl/sql table
2156 g_number_before_update_table:=EDW_SUMMARY_COLLECT.g_number_before_update_table;
2157 l_dim_id:=EDW_SUMMARY_COLLECT.g_dim_id;
2158 if g_before_update_table.count=0 then
2159 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('There are no update tables created to refresh the derived facts',
2160 FND_LOG.LEVEL_STATEMENT);
2161 return true;
2162 end if;
2163 if EDW_SUMMARY_COLLECT.g_dim_empty_flag then
2164 if g_debug then
2165 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('This dimension was fully refreshed. So no need to inc refresh derv/summ facts'
2166 ,FND_LOG.LEVEL_STATEMENT);
2167 return true;
2168 end if;
2169 end if;
2170 g_num_derv_fact_full_refresh:=0;
2171 if g_number_dim_derv_map_id=0 then
2172 if g_debug then
2173 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('There are no derv maps to inc refresh for this dimension',
2174 FND_LOG.LEVEL_STATEMENT);
2175 end if;
2176 return true;
2177 end if;
2178 --see if any revf facts need a full refresh
2179 l_number_looked_at:=0;
2180 for i in 1..g_number_dim_derv_map_id loop
2181 if EDW_OWB_COLLECTION_UTIL.value_in_table(l_looked_at,l_number_looked_at,g_derv_fact_id(i))=false then
2182 l_inc_flag:=false;
2183 for j in 1..g_number_dim_derv_map_id loop
2184 if g_derv_fact_id(j)=g_derv_fact_id(i) then
2185 if g_dim_derv_map_full_refresh(j)=false then
2186 l_inc_flag:=true;
2187 exit;
2188 end if;
2189 end if;
2190 end loop;
2191 if l_inc_flag=false then --all maps are for full refresh
2192 if EDW_OWB_COLLECTION_UTIL.get_all_maps_for_tgt(g_derv_fact_id(i),l_derv_maps,l_number_derv_maps)=false then
2193 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2194 g_status:=false;
2195 return false;
2196 end if;
2197 if g_debug then
2198 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('l_number_derv_maps='||l_number_derv_maps,FND_LOG.LEVEL_STATEMENT);
2199 for j in 1..l_number_derv_maps loop
2203 l_found:=false;
2200 EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_derv_maps='||l_derv_maps(j),FND_LOG.LEVEL_STATEMENT);
2201 end loop;
2202 end if;
2204 for j in 1..l_number_derv_maps loop
2205 if EDW_OWB_COLLECTION_UTIL.value_in_table(g_dim_derv_map_id,g_number_dim_derv_map_id,l_derv_maps(j))=
2206 false then
2207 l_found:=true;
2208 exit;
2209 end if;
2210 end loop;
2211 if l_found=false then --all maps are for full refresh and this der fact only has these maps.
2212 g_num_derv_fact_full_refresh:=g_num_derv_fact_full_refresh+1;
2213 g_derv_fact_full_refresh(g_num_derv_fact_full_refresh):=g_derv_fact_id(i);
2214 for j in 1..g_number_dim_derv_map_id loop
2215 if g_derv_fact_id(j)=g_derv_fact_full_refresh(g_num_derv_fact_full_refresh) then
2216 g_dim_derv_map_refresh(i):=false;
2217 end if;
2218 end loop;
2219 end if;
2220 end if;--if l_inc_flag=false then --all maps are for full refresh
2221 l_number_looked_at:=l_number_looked_at+1;
2222 l_looked_at(l_number_looked_at):=g_derv_fact_id(i);
2223 end if;--if EDW_OWB_COLLECTION_UTIL.value_in_table(l_looked_at,l_number_looked_at,g_derv_fact_id(i))=false then
2224 end loop;--for i in 1..g_number_dim_derv_map_id loop
2225 if g_debug then
2226 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The Derived/summary facts that are for full refresh',FND_LOG.LEVEL_STATEMENT);
2227 for i in 1..g_num_derv_fact_full_refresh loop
2228 EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_derv_fact_full_refresh(i),FND_LOG.LEVEL_STATEMENT);
2229 end loop;
2230 end if;
2231 for i in 1..g_number_dim_derv_map_id loop
2232 if g_dim_derv_map_refresh(i) then
2233 l_fact_name:=EDW_OWB_COLLECTION_UTIL.get_object_name(g_derv_fact_id(i));
2234 if get_map_properties(g_dim_derv_map_id(i),l_src_fact_name,l_src_fact_id)=false then
2235 return false;
2236 end if;
2237 l_table_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(l_fact_name);
2238 l_ilog:=g_bis_owner||'.I'||l_dim_id||'_'||g_dim_derv_map_id(i);--need to drop at the end
2239 l_dlog:=g_bis_owner||'.D'||l_dim_id||'_'||g_dim_derv_map_id(i);
2240 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2241 g_tables_to_drop(g_number_tables_to_drop):=l_ilog;
2242 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2243 g_tables_to_drop(g_number_tables_to_drop):=l_dlog;
2244 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2245 g_tables_to_drop(g_number_tables_to_drop):=l_ilog||'A';
2246 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2247 g_tables_to_drop(g_number_tables_to_drop):=l_dlog||'A';
2248 l_df_load_pk(i):=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk;
2249 p_load_fk:=l_df_load_pk(i);
2250 if g_debug then
2251 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('For object '||l_fact_name||', load pk='||l_df_load_pk(i),
2252 FND_LOG.LEVEL_STATEMENT);
2253 end if;
2254 if EDW_OWB_COLLECTION_UTIL.log_collection_start(l_fact_name,g_derv_fact_id(i),'FACT',sysdate,
2255 g_conc_program_id,l_df_load_pk(i)) =false then
2256 return false;
2257 end if;
2258 l_number_skip_cols:=0;
2259 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(l_fact_name,'DERIVED FACT',l_skip_cols,
2260 l_number_skip_cols)=false then
2261 return false;
2262 end if;
2263 for j in 1..g_number_before_update_table loop
2264 l_number_bu_tables:=1;
2265 l_bu_tables(1):=g_before_update_table(j);
2266 l_bu_dimensions(1):=p_dim_name;
2267 l_prot_delete:=g_bis_owner||'.PD'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;
2268 l_prot_update:=g_bis_owner||'.PU'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;
2269 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2270 g_tables_to_drop(g_number_tables_to_drop):=l_prot_delete;
2271 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2272 g_tables_to_drop(g_number_tables_to_drop):=l_prot_update;
2273 if g_dim_derv_map_full_refresh(i) then
2274 l_bu_src_fact:=null;
2275 else
2276 if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_delete)=false or
2277 EDW_OWB_COLLECTION_UTIL.check_table(l_prot_update)=false then
2278 l_derv_bu_map_src_table:=g_bis_owner||'.BUS'||l_dim_id||'_'||g_dim_derv_map_id(i)||'_'||j;
2279 if create_bu_src_fact(l_src_fact_name,l_src_fact_id,p_dim_name,l_dim_id,g_dim_derv_map_id(i),
2280 l_derv_bu_map_src_table,g_before_update_table(j),l_bu_src_fact)=false then
2281 return false;
2282 end if;
2283 g_number_tables_to_drop:=g_number_tables_to_drop+1;
2284 g_tables_to_drop(g_number_tables_to_drop):=l_derv_bu_map_src_table;
2285 end if;
2286 end if;
2287 for k in 1..2 loop
2288 if k=1 then
2289 l_load_mode:='BU-DELETE';
2290 if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_delete) then
2291 goto loopend;
2292 end if;
2293 else
2294 l_load_mode:='BU-UPDATE';
2295 l_number_bu_tables:=0;
2296 if EDW_OWB_COLLECTION_UTIL.check_table(l_prot_update) then
2297 goto loopend;
2298 end if;
2299 end if;
2300 --what about progress logging?
2304 EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_load_mode='||l_load_mode||',l_bu_src_fact='||l_bu_src_fact,
2301 if g_debug then
2302 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Load '||l_fact_name||' from '||l_src_fact_name,
2303 FND_LOG.LEVEL_STATEMENT);
2305 FND_LOG.LEVEL_STATEMENT);
2306 for z in 1..l_number_bu_tables loop
2307 EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_bu_tables='||l_bu_tables(z)||',l_bu_dimensions='||l_bu_dimensions(z),
2308 FND_LOG.LEVEL_STATEMENT);
2309 end loop;
2310 end if;
2311 if EDW_DERIVED_FACT_COLLECT.COLLECT_FACT(
2312 l_fact_name,
2313 g_derv_fact_id(i),
2314 l_src_fact_name,
2315 l_src_fact_id,
2316 g_dim_derv_map_id(i),
2317 g_conc_program_id,
2318 g_conc_program_name,
2319 g_debug,
2320 0,--g_collection_size
2321 g_parallel,
2322 g_bis_owner,
2323 l_table_owner,
2324 l_ins_rows_processed,--an out NOCOPY parameter
2325 l_ilog,
2326 l_dlog,
2327 g_forall_size,
2328 g_update_type,
2329 null,--the fact dlog
2330 l_skip_cols ,
2331 l_number_skip_cols ,
2332 l_df_load_pk(i),
2333 false,--fresh restart
2334 g_op_table_space,
2335 l_bu_tables ,--before update tables.prop dim change to derv
2336 l_bu_dimensions ,
2337 l_number_bu_tables ,
2338 l_bu_src_fact ,--what table to look at as the src fact. if null, scan the actual src fact
2339 l_load_mode,
2340 g_rollback,
2341 g_stg_join_nl,
2342 g_thread_type,
2343 g_max_threads,
2344 g_min_job_load_size,
2345 g_sleep_time,
2346 g_hash_area_size,
2347 g_sort_area_size,
2348 g_trace,
2349 g_read_cfig_options
2350 )=false then
2351 g_status_message:=EDW_DERIVED_FACT_COLLECT.g_status_message;
2352 g_status:=false;
2353 return false;
2354 end if;
2355 if l_load_mode='BU-DELETE' then
2356 if EDW_OWB_COLLECTION_UTIL.create_prot_table(l_prot_delete,g_op_table_space)=false then
2357 return false;
2358 end if;
2359 else
2360 if EDW_OWB_COLLECTION_UTIL.create_prot_table(l_prot_update,g_op_table_space)=false then
2361 return false;
2362 end if;
2363 end if;
2364 <<loopend>>
2365 null;
2366 end loop;--for k in 1..2 loop
2367 end loop;--for j in 1..g_number_before_update_table loop
2368 if get_temp_log_data(l_fact_name, 'FACT')=false then
2369 return_with_error(l_df_load_pk(i),'LOG');
2370 return false;
2371 end if;
2372 return_with_success('LOG',null,l_df_load_pk(i));
2373 end if;
2374 end loop;
2375 --full refresh of all the needed derv/summary facts
2376 for i in 1..g_num_derv_fact_full_refresh loop
2377 l_df_load_pk(i):=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk;
2378 p_load_fk:=l_df_load_pk(i);
2379 l_fact_name:=EDW_OWB_COLLECTION_UTIL.get_object_name(g_derv_fact_id(i));
2380 if g_debug then
2381 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to fully refresh '||l_fact_name||'('||g_derv_fact_full_refresh(i)||')'
2382 ,FND_LOG.LEVEL_STATEMENT);
2383 end if;
2384 if EDW_OWB_COLLECTION_UTIL.log_collection_start(l_fact_name,g_derv_fact_id(i),'FACT',sysdate,g_conc_program_id,
2385 l_df_load_pk(i)) =false then
2386 return false;
2387 end if;
2388 l_number_skip_cols:=0;
2389 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(l_fact_name,'DERIVED FACT',l_skip_cols,l_number_skip_cols)=false then
2390 return false;
2391 end if;
2392 if EDW_DERIVED_FACT_COLLECT.COLLECT_FACT(
2393 l_fact_name,
2394 g_conc_program_id,
2395 g_conc_program_name,
2396 g_debug,
2397 0,--g_collection_size,
2398 g_parallel,
2399 g_bis_owner,
2400 g_table_owner,
2401 l_ins_rows_processed,
2402 g_forall_size,
2403 g_update_type,
2404 l_skip_cols,
2405 l_number_skip_cols,
2406 l_df_load_pk(i),--p_load_pk
2407 false,--g_fresh_restart
2408 g_op_table_space,
2409 g_rollback,
2410 g_stg_join_nl,
2411 g_thread_type,
2412 g_max_threads,
2413 g_min_job_load_size,
2414 g_sleep_time,
2415 g_hash_area_size,
2416 g_sort_area_size,
2417 g_trace,
2418 g_read_cfig_options
2419 ) = false then
2420 g_status_message:=EDW_DERIVED_FACT_COLLECT.get_status_message;
2421 g_status:=false;
2422 return false;
2423 end if;
2424 if get_temp_log_data(l_fact_name, 'FACT')=false then
2425 return_with_error(l_df_load_pk(i),'LOG');
2426 return false;
2427 end if;
2428 return_with_success('LOG',null,l_df_load_pk(i));
2429 end loop;
2430 for i in 1..g_number_tables_to_drop loop
2431 if EDW_OWB_COLLECTION_UTIL.drop_table(g_tables_to_drop(i))=false then
2435 for i in 1..g_number_before_update_table loop
2432 null;
2433 end if;
2434 end loop;
2436 if EDW_OWB_COLLECTION_UTIL.drop_table(g_before_update_table(i))=false then
2437 null;
2438 end if;
2439 end loop;
2440 return true;
2441 Exception when others then
2442 g_status_message:=sqlerrm;
2443 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2444 g_status:=false;
2445 return false;
2446 End;
2447
2448 /*
2449 we need to decide if we need to create a copy of the src fact. look at the col stats and then decide.
2450 return null if there is no need to create a copy table
2451 this api is called per map
2452 */
2453 function create_bu_src_fact(p_src_fact varchar2,p_src_fact_id number,
2454 p_dim_name varchar2,p_dim_id number, p_map_id number,p_derv_bu_map_src_table varchar2,
2455 p_derv_before_update_table varchar2,p_bu_src_table out NOCOPY varchar2)
2456 return boolean is
2457 l_stmt varchar2(10000);
2458 TYPE CurTyp IS REF CURSOR;
2459 cv CurTyp;
2460 l_owner varchar2(200);
2461 l_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
2462 l_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
2463 l_number_fk number;
2464 l_count number;
2465 l_derv_bu_map_src_table varchar2(200);
2466 Begin
2467 if g_debug then
2468 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In create_bu_src_fact',FND_LOG.LEVEL_STATEMENT);
2469 end if;
2470 p_bu_src_table:=null;
2471 l_derv_bu_map_src_table:=p_derv_bu_map_src_table||'R';
2472 l_owner:=EDW_OWB_COLLECTION_UTIL.get_table_owner(p_src_fact);
2473 l_number_fk:=0;
2474 if EDW_OWB_COLLECTION_UTIL.get_fk_pk(p_src_fact_id,p_dim_id,p_map_id,l_fk,l_pk,l_number_fk)=false then
2475 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2476 g_status:=false;
2477 return false;
2478 end if;
2479 if EDW_OWB_COLLECTION_UTIL.check_table(p_derv_bu_map_src_table) then
2480 p_bu_src_table:=p_derv_bu_map_src_table;
2481 return true;
2482 else
2483 if l_number_fk=1 then
2484 l_stmt:='create table '||p_derv_bu_map_src_table||' tablespace '||g_op_table_space;--storage?
2485 if g_parallel is not null then
2486 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2487 end if;
2488 l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
2489 if g_parallel is not null then
2490 l_stmt:=l_stmt||' /*+PARALLEL('||p_src_fact||','||g_parallel||')*/ ';
2491 end if;
2492 l_stmt:=l_stmt||p_src_fact||'.* from '||p_derv_before_update_table||','||p_src_fact||' where '||
2493 p_derv_before_update_table||'.'||l_pk(1)||'='||p_src_fact||'.'||l_fk(1);
2494 else
2495 l_stmt:='create table '||l_derv_bu_map_src_table||' tablespace '||g_op_table_space;
2496 if g_parallel is not null then
2497 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2498 end if;
2499 l_stmt:=l_stmt||' as ';
2500 for i in 1..l_number_fk loop
2501 l_stmt:=l_stmt||' select /*+ORDERED*/ ';
2502 if g_parallel is not null then
2503 l_stmt:=l_stmt||' /*+PARALLEL('||p_src_fact||','||g_parallel||')*/ ';
2504 end if;
2505 l_stmt:=l_stmt||p_src_fact||'.rowid row_id from '||p_derv_before_update_table||','||p_src_fact||
2506 ' where '||p_derv_before_update_table||'.'||l_pk(i)||'='||p_src_fact||'.'||l_fk(i)||' UNION ';
2507 end loop;
2508 l_stmt:=substr(l_stmt,1,length(l_stmt)-6);
2509 if g_debug then
2510 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to execute '||l_stmt||get_time,FND_LOG.LEVEL_STATEMENT);
2511 end if;
2512 execute immediate l_stmt;
2513 l_count:=sql%rowcount;
2514 if g_debug then
2515 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Created '||l_derv_bu_map_src_table||' with '||l_count||' rows '||get_time
2516 ,FND_LOG.LEVEL_STATEMENT);
2517 end if;
2518 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(l_derv_bu_map_src_table,
2519 instr(l_derv_bu_map_src_table,'.')+1,length(l_derv_bu_map_src_table)),
2520 substr(l_derv_bu_map_src_table,1,instr(l_derv_bu_map_src_table,'.')-1));
2521 l_stmt:='create table '||p_derv_bu_map_src_table||' tablespace '||g_op_table_space;--storage?
2522 if g_parallel is not null then
2523 l_stmt:=l_stmt||' parallel (degree '||g_parallel||') ';
2524 end if;
2525 l_stmt:=l_stmt||' as select /*+ORDERED*/ ';
2526 if g_parallel is not null then
2527 l_stmt:=l_stmt||' /*+PARALLEL('||p_src_fact||','||g_parallel||')*/ ';
2528 end if;
2529 l_stmt:=l_stmt||p_src_fact||'.* from '||l_derv_bu_map_src_table||','||p_src_fact||' where '||
2530 l_derv_bu_map_src_table||'.row_id='||p_src_fact||'.rowid';
2531 end if;
2532 if g_debug then
2533 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to execute '||l_stmt||get_time,FND_LOG.LEVEL_STATEMENT);
2534 end if;
2535 execute immediate l_stmt;
2536 l_count:=sql%rowcount;
2537 if g_debug then
2538 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Created '||p_derv_bu_map_src_table||' with '||l_count||' rows '||get_time
2539 ,FND_LOG.LEVEL_STATEMENT);
2540 end if;
2541 p_bu_src_table:=p_derv_bu_map_src_table;
2542 EDW_OWB_COLLECTION_UTIL.analyze_table_stats(substr(p_derv_bu_map_src_table,
2543 instr(p_derv_bu_map_src_table,'.')+1,length(p_derv_bu_map_src_table)),
2544 substr(p_derv_bu_map_src_table,1,instr(p_derv_bu_map_src_table,'.')-1));
2548 end if;--else for if EDW_OWB_COLLECTION_UTIL.check_table(p_derv_bu_map_src_table) then
2545 if EDW_OWB_COLLECTION_UTIL.drop_table(l_derv_bu_map_src_table)=false then
2546 null;
2547 end if;
2549 return true;
2550 Exception when others then
2551 g_status_message:=sqlerrm;
2552 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2553 g_status:=false;
2554 return false;
2555 End;
2556
2557 function get_map_properties(p_map_id number,p_src_fact_name out NOCOPY varchar2,p_src_fact_id out NOCOPY number)
2558 return boolean is
2559 l_stmt varchar2(4000);
2560 TYPE CurTyp IS REF CURSOR;
2561 cv CurTyp;
2562 Begin
2563 --not checked
2564 l_stmt:='select rel.relation_name,rel.relation_id '||
2565 'from '||
2566 'edw_pvt_map_properties_md_v map, '||
2567 'edw_relations_md_v rel '||
2568 'where map.mapping_id=:a '||
2569 'and rel.relation_id=map.primary_source ';
2570 if g_debug then
2571 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Going to execute '||l_stmt||' using '||p_map_id,FND_LOG.LEVEL_STATEMENT);
2572 end if;
2573 open cv for l_stmt using p_map_id;
2574 fetch cv into p_src_fact_name,p_src_fact_id;
2575 close cv;
2576 return true;
2577 Exception when others then
2578 g_status_message:=sqlerrm;
2579 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2580 g_status:=false;
2581 return false;
2582 End;
2583
2584 function find_data_alignment_cols(p_object_name varchar2) return boolean is
2585 Begin
2586 g_number_da_cols:=0;
2587 if g_read_cfig_options then
2588 if edw_option.get_option_columns(p_object_name,null,'ALIGNMENT',g_da_cols,g_number_da_cols)=false then
2589 g_status_message:=edw_option.g_status_message;
2590 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
2591 g_status:=false;
2592 return false;
2593 end if;
2594 else
2595 if EDW_OWB_COLLECTION_UTIL.get_item_set_cols(g_da_cols,g_number_da_cols,p_object_name,'DATA_ALIGNMENT')=false then
2596 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
2597 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_STATEMENT);
2598 g_status:=false;
2599 return false;
2600 end if;
2601 end if;
2602 if g_debug then
2603 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The data alignment columns('||g_number_da_cols||')',FND_LOG.LEVEL_STATEMENT);
2604 for i in 1..g_number_da_cols loop
2605 EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_da_cols(i),FND_LOG.LEVEL_STATEMENT);
2606 end loop;
2607 end if;
2608 return true;
2609 Exception when others then
2610 g_status_message:=sqlerrm;
2611 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2612 g_status:=false;
2613 return false;
2614 End;
2615
2616 function read_config_options return boolean is
2617 l_option_value varchar2(200);
2618 l_hash_area_size number;
2619 l_sort_area_size number;
2620 l_num number;
2621 Begin
2622 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In read_config_options',FND_LOG.LEVEL_STATEMENT);
2623 l_option_value:=null;
2624 if edw_option.get_warehouse_option(null,g_object_id,'TRACE',l_option_value)=false then
2625 null;
2626 end if;
2627 if l_option_value='Y' then
2628 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Trace turned ON',FND_LOG.LEVEL_STATEMENT);
2629 g_trace:=true;
2630 else
2631 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Trace turned OFF',FND_LOG.LEVEL_STATEMENT);
2632 g_trace:=false;
2633 end if;
2634 if g_trace then
2635 EDW_OWB_COLLECTION_UTIL.alter_session('TRACE');
2636 end if;
2637 l_option_value:=null;
2638 if edw_option.get_warehouse_option(null,g_object_id,'DEBUG',l_option_value)=false then
2639 null;
2640 end if;
2641 if l_option_value='Y' or FND_LOG.G_CURRENT_RUNTIME_LEVEL=FND_LOG.LEVEL_STATEMENT then
2642 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Debug turned ON',FND_LOG.LEVEL_STATEMENT);
2643 g_debug:=true;
2644 else
2645 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Debug turned OFF',FND_LOG.LEVEL_STATEMENT);
2646 g_debug:=false;
2647 end if;
2648 EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
2649 l_option_value:=null;
2650 if edw_option.get_warehouse_option(null,g_object_id,'DUPLICATE',l_option_value)=false then
2651 null;
2652 end if;
2653 if l_option_value='Y' then
2654 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Duplicate Load turned ON',FND_LOG.LEVEL_STATEMENT);
2655 g_duplicate_collect:=true;
2656 else
2657 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Duplicate Load turned OFF',FND_LOG.LEVEL_STATEMENT);
2658 g_duplicate_collect:=false;
2659 end if;
2660 l_option_value:=null;
2661 if edw_option.get_warehouse_option(null,g_object_id,'COMMITSIZE',l_option_value)=false then
2662 null;
2663 end if;
2664 if l_option_value is not null then
2665 g_collection_size:=to_number(l_option_value);
2666 else
2667 g_collection_size:=0;
2668 end if;
2669 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Collection size= '||g_collection_size,FND_LOG.LEVEL_STATEMENT);
2670 g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
2671 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('BIS Owner is '||g_bis_owner,FND_LOG.LEVEL_STATEMENT);
2675 end if;
2672 l_option_value:=null;
2673 if edw_option.get_warehouse_option(null,g_object_id,'PARALLELISM',l_option_value)=false then
2674 null;
2676 if l_option_value is not null then
2677 g_parallel:=to_number(l_option_value);
2678 if g_parallel=0 then
2679 g_parallel:=null;
2680 end if;
2681 else
2682 g_parallel:=null;
2683 end if;
2684 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n ('Degree of parallelism (null is default)='||g_parallel,FND_LOG.LEVEL_STATEMENT);
2685 if g_parallel is not null then
2686 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
2687 commit;
2688 end if;
2689 l_option_value:=null;
2690 if g_object_type='FACT' then
2691 l_option_value:='Y';
2692 else
2693 if edw_option.get_warehouse_option(null,g_object_id,'AUTOKEYGEN',l_option_value)=false then
2694 null;
2695 end if;
2696 end if;
2697 if l_option_value='Y' then
2698 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Dangling Load turned ON',FND_LOG.LEVEL_STATEMENT);
2699 g_mode:='TEST';
2700 else
2701 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Dangling Load turned OFF',FND_LOG.LEVEL_STATEMENT);
2702 g_mode:='NORMAL';
2703 end if;
2704 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Mode is '||g_mode,FND_LOG.LEVEL_STATEMENT);
2705 g_explain_plan_check:=FALSE;
2706 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Explain plan check OFF',FND_LOG.LEVEL_STATEMENT);
2707 l_option_value:=null;
2708 if edw_option.get_warehouse_option(null,g_object_id,'HASHAREA',l_option_value)=false then
2709 null;
2710 end if;
2711 if l_option_value is not null then
2712 l_hash_area_size:=to_number(l_option_value);
2713 if l_hash_area_size=0 then
2714 l_hash_area_size:=null;
2715 end if;
2716 else
2717 l_hash_area_size:=null;
2718 end if;
2719 if l_hash_area_size is not null then
2720 execute immediate 'alter session set hash_area_size='||l_hash_area_size;
2721 end if;
2722 l_option_value:=null;
2723 if edw_option.get_warehouse_option(null,g_object_id,'SORTAREA',l_option_value)=false then
2724 null;
2725 end if;
2726 if l_option_value is not null then
2727 l_sort_area_size:=to_number(l_option_value);
2728 if l_sort_area_size=0 then
2729 l_sort_area_size:=null;
2730 end if;
2731 else
2732 l_sort_area_size:=null;
2733 end if;
2734 if l_sort_area_size is not null then
2735 execute immediate 'alter session set sort_area_size='||l_sort_area_size;
2736 end if;
2737 l_option_value:=null;
2738 if edw_option.get_warehouse_option(null,g_object_id,'KEYSETSIZE',l_option_value)=false then
2739 null;
2740 end if;
2741 if l_option_value is not null then
2742 g_key_set:=to_number(l_option_value);
2743 if g_key_set<2 then
2744 g_key_set:=2;
2745 end if;
2746 else
2747 g_key_set:=2;
2748 end if;
2749 if g_parallel>1 and g_key_set<10 then
2750 g_key_set:=10;
2751 end if;
2752 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_key_set='||g_key_set,FND_LOG.LEVEL_STATEMENT);
2753 l_option_value:=null;
2754 if edw_option.get_warehouse_option(null,g_object_id,'FRESHSTART',l_option_value)=false then
2755 null;
2756 end if;
2757 if l_option_value='Y' then
2758 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Fresh Restart TRUE',FND_LOG.LEVEL_STATEMENT);
2759 g_fresh_restart:=true;
2760 else
2761 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Fresh Restart FALSE',FND_LOG.LEVEL_STATEMENT);
2762 g_fresh_restart:=false;
2763 end if;
2764 l_option_value:=null;
2765 if edw_option.get_warehouse_option(null,g_object_id,'OPTABLESPACE',l_option_value)=false then
2766 null;
2767 end if;
2768 if l_option_value is not null then
2769 g_op_table_space:=l_option_value;
2770 else
2771 g_op_table_space:=EDW_OWB_COLLECTION_UTIL.get_table_space(g_bis_owner);
2772 end if;
2773 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Operation table space='||g_op_table_space,FND_LOG.LEVEL_STATEMENT);
2774 l_option_value:=null;
2775 if edw_option.get_warehouse_option(null,g_object_id,'ROLLBACK',l_option_value)=false then
2776 null;
2777 end if;
2778 g_rollback:=l_option_value;
2779 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Rollback Segment='||g_rollback,FND_LOG.LEVEL_STATEMENT);
2780 l_option_value:=null;
2781 if edw_option.get_warehouse_option(null,g_object_id,'UPDATETYPE',l_option_value)=false then
2782 null;
2783 end if;
2784 if l_option_value is not null then
2785 g_update_type:=l_option_value;
2786 else
2787 g_update_type:='MASS';
2788 end if;
2789 if g_update_type<>'MASS' and g_update_type<>'ROW-BY-ROW' then
2790 g_update_type:='MASS';
2791 end if;
2792 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Update Type='||g_update_type,FND_LOG.LEVEL_STATEMENT);
2793 l_option_value:=null;
2794 if edw_option.get_warehouse_option(null,g_object_id,'SMARTUPDATE',l_option_value)=false then
2795 null;
2796 end if;
2797 if l_option_value='Y' then
2798 g_smart_update:=true;
2799 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);
2800 else
2801 g_smart_update:=false;
2802 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update FALSE',FND_LOG.LEVEL_STATEMENT);
2803 end if;
2807 g_number_smart_update_cols)=false then
2804 g_number_smart_update_cols:=0;
2805 if g_smart_update then
2806 if edw_option.get_option_columns(null,g_object_id,'SMARTUPDATE',g_smart_update_cols,
2808 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in getting columns for smart update '||edw_option.g_status_message,
2809 FND_LOG.LEVEL_STATEMENT);
2810 g_number_smart_update_cols:=0;
2811 end if;
2812 if g_debug then
2813 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The smart update columns',FND_LOG.LEVEL_STATEMENT);
2814 for i in 1..g_number_smart_update_cols loop
2815 EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_smart_update_cols(i),FND_LOG.LEVEL_STATEMENT);
2816 end loop;
2817 end if;
2818 end if;
2819 l_option_value:=null;
2820 if edw_option.get_warehouse_option(null,g_object_id,'FK_USE_NL',l_option_value)=false then
2821 null;
2822 end if;
2823 if l_option_value is not null then
2824 g_fk_use_nl:=to_number(l_option_value);
2825 if g_fk_use_nl<0 then
2826 g_fk_use_nl:=0;
2827 end if;
2828 else
2829 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_fk_use_nl null. setting to 100,000',FND_LOG.LEVEL_STATEMENT);
2830 g_fk_use_nl:=100000;
2831 end if;
2832 l_option_value:=null;
2833 if edw_option.get_warehouse_option(null,g_object_id,'LTC_COPY_MERGE_NL',l_option_value)=false then
2834 null;
2835 end if;
2836 if l_option_value='Y' then
2837 g_ltc_merge_use_nl:=true;
2838 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('LTC Copy Merge TRUE',FND_LOG.LEVEL_STATEMENT);
2839 else
2840 g_ltc_merge_use_nl:=false;
2841 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('LTC Copy Merge FALSE',FND_LOG.LEVEL_STATEMENT);
2842 end if;
2843 l_option_value:=null;
2844 g_dim_inc_refresh_derv:=false;
2845 if g_object_type='DIMENSION' then
2846 if edw_option.get_warehouse_option(null,g_object_id,'INCREMENTAL',l_option_value)=false then
2847 null;
2848 end if;
2849 if l_option_value='Y' then
2850 g_dim_inc_refresh_derv:=true;--propogate dim changes to derv facts
2851 else
2852 g_dim_inc_refresh_derv:=false;
2853 end if;
2854 end if;
2855 if edw_option.get_warehouse_option(null,g_object_id,'STG_JOIN_NL',l_option_value)=false then
2856 null;
2857 end if;
2858 if l_option_value is not null then
2859 if l_option_value>0 and l_option_value<=100 then
2860 g_stg_join_nl:=to_number(l_option_value);
2861 end if;
2862 end if;
2863 if edw_option.get_warehouse_option(null,g_object_id,'OK_UPDATE',l_option_value)=false then
2864 null;
2865 end if;
2866 if l_option_value is not null then
2867 if l_option_value>0 and l_option_value<=100 then
2868 g_ok_switch_update:=to_number(l_option_value);
2869 end if;
2870 end if;
2871 if edw_option.get_warehouse_option(null,g_object_id,'STG_MAKE_COPY',l_option_value)=false then
2872 null;
2873 end if;
2874 if l_option_value is not null then
2875 if l_option_value>0 and l_option_value<=100 then
2876 g_stg_make_copy_percentage:=to_number(l_option_value);
2877 end if;
2878 end if;
2879 g_auto_dang_recovery:=false;
2880 l_option_value:=null;
2881 if edw_option.get_warehouse_option(null,g_object_id,'AUTODANG',l_option_value)=false then
2882 null;
2883 end if;
2884 if l_option_value='Y' then
2885 g_auto_dang_recovery:=true;
2886 end if;
2887 g_create_parent_table_records:=false;
2888 if g_auto_dang_recovery then
2889 g_create_parent_table_records:=true;
2890 end if;
2891 l_option_value:=null;
2892 if edw_option.get_warehouse_option(null,g_object_id,'MAX_THREADS',l_option_value)=false then
2893 null;
2894 end if;
2895 begin
2896 l_num:=to_number(l_option_value);
2897 exception when others then
2898 l_option_value:='AUTO';
2899 end;
2900 if l_option_value='AUTO' then
2901 l_num:=EDW_OWB_COLLECTION_UTIL.get_job_queue_processes;
2902 end if;
2903 if l_num is not null and l_num>0 then
2904 g_max_threads:=l_num;
2905 end if;
2906 l_option_value:=null;
2907 if edw_option.get_warehouse_option(null,g_object_id,'MIN_JOB_LOAD_SIZE',l_option_value)=false then
2908 null;
2909 end if;
2910 if l_option_value is not null and l_option_value>0 then
2911 g_min_job_load_size:=to_number(l_option_value);
2912 end if;
2913 l_option_value:=null;
2914 if edw_option.get_warehouse_option(null,g_object_id,'SLEEP_TIME',l_option_value)=false then
2915 null;
2916 end if;
2917 if l_option_value is not null and l_option_value>0 then
2918 g_sleep_time:=to_number(l_option_value);
2919 end if;
2920 return true;
2921 Exception when others then
2922 g_status_message:=sqlerrm;
2923 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2924 g_status:=false;
2925 return false;
2926 End;
2927
2928 function read_profile_options return boolean is
2929 l_hash_area_size number;
2930 l_sort_area_size number;
2931 l_key_set varchar2(400);
2932 l_num number;
2933 l_var varchar2(400);
2934 check_tspace_exist varchar(1);
2935 check_ts_mode varchar(1);
2936 physical_tspace_name varchar2(100);
2937
2938 Begin
2942 g_trace:=true;
2939 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('In read_profile_options',FND_LOG.LEVEL_STATEMENT);
2940 if fnd_profile.value('EDW_TRACE')='Y' then
2941 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Trace turned ON',FND_LOG.LEVEL_STATEMENT);
2943 else
2944 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Trace turned OFF',FND_LOG.LEVEL_STATEMENT);
2945 g_trace:=false;
2946 end if;
2947 commit;
2948 if g_trace then
2949 EDW_OWB_COLLECTION_UTIL.alter_session('TRACE');
2950 commit;
2951 end if;
2952 if fnd_profile.value('EDW_DEBUG')='Y' or FND_LOG.G_CURRENT_RUNTIME_LEVEL=FND_LOG.LEVEL_STATEMENT then
2953 g_debug:=true;--look at the profile value for this
2954 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Debug turned ON',FND_LOG.LEVEL_STATEMENT);
2955 else
2956 g_debug:=false;
2957 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Debug turned OFF',FND_LOG.LEVEL_STATEMENT);
2958 end if;
2959 EDW_OWB_COLLECTION_UTIL.set_debug(g_debug);
2960 if fnd_profile.value('EDW_DUPLICATE_COLLECT')='Y' then
2961 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Duplicate collect is turned ON',FND_LOG.LEVEL_STATEMENT);
2962 g_duplicate_collect:=true;--look at the profile value for this
2963 else
2964 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Duplicate collect is turned OFF',FND_LOG.LEVEL_STATEMENT);
2965 g_duplicate_collect:=false;
2966 end if;
2967 if g_object_type='DIMENSION' then
2968 g_dim_push_down:=EDW_OWB_COLLECTION_UTIL.is_push_down_implemented(g_object_name);
2969 if g_dim_push_down then
2970 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Dimension Push Down Implemented',FND_LOG.LEVEL_STATEMENT);
2971 else
2972 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Dimension Push Down NOT Implemented',FND_LOG.LEVEL_STATEMENT);
2973 end if;
2974 end if;
2975 g_collection_size:=fnd_profile.value('EDW_COLLECTION_SIZE');
2976 if g_collection_size is null then
2977 g_collection_size:=0;
2978 end if;
2979 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Collection size= '||g_collection_size,FND_LOG.LEVEL_STATEMENT);
2980 g_bis_owner:=EDW_OWB_COLLECTION_UTIL.get_db_user('BIS');
2981 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('BIS Owner is '||g_bis_owner,FND_LOG.LEVEL_STATEMENT);
2982 g_parallel:=fnd_profile.value('EDW_PARALLEL');
2983 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n ('Degree of parallelism (null is default)='||g_parallel,FND_LOG.LEVEL_STATEMENT);
2984 if g_parallel=0 then
2985 g_parallel:=null;
2986 end if;
2987 commit;
2988 if g_parallel is not null then
2989 EDW_OWB_COLLECTION_UTIL.alter_session('PARALLEL');
2990 commit;
2991 end if;
2992 if fnd_profile.value('EDW_TEST_MODE')='Y' then
2993 g_mode:='TEST';
2994 else
2995 g_mode:='NORMAL';
2996 end if;
2997 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Mode is '||g_mode,FND_LOG.LEVEL_STATEMENT);
2998 if fnd_profile.value('EDW_USE_EXP_PLAN')='Y' then
2999 g_explain_plan_check:=TRUE;
3000 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Explain plan check ON',FND_LOG.LEVEL_STATEMENT);
3001 else
3002 g_explain_plan_check:=FALSE;
3003 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Explain plan check OFF',FND_LOG.LEVEL_STATEMENT);
3004 end if;
3005 l_hash_area_size:=null;
3006 l_hash_area_size:=fnd_profile.value('EDW_HASH_AREA_SIZE');
3007 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('l_hash_area_size='||l_hash_area_size,FND_LOG.LEVEL_STATEMENT);
3008 if l_hash_area_size is not null then
3009 execute immediate 'alter session set hash_area_size='||l_hash_area_size;
3010 end if;
3011 l_sort_area_size:=null;
3012 l_sort_area_size:=fnd_profile.value('EDW_SORT_AREA_SIZE');
3013 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('l_sort_area_size='||l_sort_area_size,FND_LOG.LEVEL_STATEMENT);
3014 if l_sort_area_size is not null then
3015 execute immediate 'alter session set sort_area_size='||l_sort_area_size;
3016 end if;
3017 if fnd_profile.value('EDW_FRESH_RESTART')='Y' then
3018 g_fresh_restart:=true;
3019 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Fresh Restart TRUE',FND_LOG.LEVEL_STATEMENT);
3020 else
3021 g_fresh_restart:=false;
3022 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Fresh Restart FALSE',FND_LOG.LEVEL_STATEMENT);
3023 end if;
3024
3025 g_op_table_space:=fnd_profile.value('EDW_OP_TABLE_SPACE');
3026 if g_op_table_space is null then
3027 AD_TSPACE_UTIL.is_new_ts_mode (check_ts_mode);
3028 If check_ts_mode ='Y' then
3029 AD_TSPACE_UTIL.get_tablespace_name ('BIS', 'INTERFACE','Y',check_tspace_exist, physical_tspace_name);
3030 if check_tspace_exist='Y' and physical_tspace_name is not null then
3031 g_op_table_space := physical_tspace_name;
3032 end if;
3033 end if;
3034 end if;
3035 if g_op_table_space is null then
3036 g_op_table_space:=EDW_OWB_COLLECTION_UTIL.get_table_space(g_bis_owner);
3037 end if;
3038
3039 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Operation table space='||g_op_table_space,FND_LOG.LEVEL_STATEMENT);
3040 g_rollback:=fnd_profile.value('EDW_LOAD_ROLLBACK');
3041 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Rollback Segment='||g_rollback,FND_LOG.LEVEL_STATEMENT);
3042 g_update_type:=fnd_profile.value('EDW_UPDATE_TYPE');
3043 if g_update_type is null then
3044 g_update_type:='MASS';
3045 end if;
3046 if g_update_type<>'MASS' and g_update_type<>'ROW-BY-ROW' then
3047 g_update_type:='MASS';
3048 end if;
3052 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);
3049 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Update Type='||g_update_type,FND_LOG.LEVEL_STATEMENT);
3050 if fnd_profile.value('EDW_SMART_UPDATE')='Y' then
3051 g_smart_update:=true;
3053 else
3054 g_smart_update:=false;
3055 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update FALSE',FND_LOG.LEVEL_STATEMENT);
3056 end if;
3057 g_number_smart_update_cols:=0;
3058 if g_smart_update then
3059 if EDW_OWB_COLLECTION_UTIL.get_item_set_cols(g_smart_update_cols,g_number_smart_update_cols,g_object_name,
3060 'CHECK_COLUMNS_FOR_UPDATE')=false then
3061 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(EDW_OWB_COLLECTION_UTIL.g_status_message,FND_LOG.LEVEL_STATEMENT);
3062 end if;
3063 if g_debug then
3064 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('The smart update columns',FND_LOG.LEVEL_STATEMENT);
3065 for i in 1..g_number_smart_update_cols loop
3066 EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_smart_update_cols(i),FND_LOG.LEVEL_STATEMENT);
3067 end loop;
3068 end if;
3069 end if;
3070 begin
3071 g_fk_use_nl:=fnd_profile.value('EDW_FK_USE_NL');
3072 if g_fk_use_nl is null then
3073 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_fk_use_nl null. setting to 100,000',FND_LOG.LEVEL_STATEMENT);
3074 g_fk_use_nl:=100000;
3075 end if;
3076 exception when others then
3077 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('EDW_FK_USE_NL is still boolean. Setting to 100,000',FND_LOG.LEVEL_STATEMENT);
3078 g_fk_use_nl:=100000;
3079 end;
3080 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('FK Use NL ='||g_fk_use_nl,FND_LOG.LEVEL_STATEMENT);
3081 g_ltc_merge_use_nl:=false;
3082 if fnd_profile.value('EDW_LTC_COPY_MERGE_NL')='Y' then
3083 g_ltc_merge_use_nl:=true;
3084 end if;
3085 if g_ltc_merge_use_nl then
3086 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('LTC Copy Merge TRUE',FND_LOG.LEVEL_STATEMENT);
3087 else
3088 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('LTC Copy Merge FALSE',FND_LOG.LEVEL_STATEMENT);
3089 end if;
3090 g_dim_inc_refresh_derv:=false;
3091 if g_object_type='DIMENSION' then
3092 if EDW_OWB_COLLECTION_UTIL.is_itemset_implemented(g_object_name,'EDW_INC_REFRESH',null)='Y' then
3093 g_dim_inc_refresh_derv:=true; --propogate dim changes to derv facts
3094 else
3095 g_dim_inc_refresh_derv:=false;
3096 end if;
3097 end if;
3098 l_num:=fnd_profile.value('EDW_STG_JOIN_NL');
3099 if l_num is not null then
3100 if l_num>0 and l_num<101 then
3101 g_stg_join_nl:=l_num;
3102 end if;
3103 end if;
3104 l_num:=fnd_profile.value('EDW_OK_UPDATE');
3105 if l_num is not null then
3106 if l_num>0 and l_num<101 then
3107 g_ok_switch_update:=l_num;
3108 end if;
3109 end if;
3110 l_num:=fnd_profile.value('EDW_STG_MAKE_COPY');
3111 if l_num is not null then
3112 if l_num>0 and l_num<101 then
3113 g_stg_make_copy_percentage:=l_num;
3114 end if;
3115 end if;
3116 g_auto_dang_recovery:=false;
3117 if fnd_profile.value('EDW_AUTO_DANG_RECOVERY')='Y' then
3118 g_auto_dang_recovery:=true;
3119 end if;
3120 g_create_parent_table_records:=false;
3121 if g_auto_dang_recovery then
3122 g_create_parent_table_records:=true;
3123 end if;
3124 l_num:=null;
3125 l_var:=null;
3126 l_var:=fnd_profile.value('EDW_MAX_THREADS');
3127 begin
3128 l_num:=to_number(l_var);
3129 exception when others then
3130 l_var:='AUTO';
3131 end;
3132 if l_var='AUTO' then
3133 l_num:=EDW_OWB_COLLECTION_UTIL.get_job_queue_processes;
3134 end if;
3135 if l_num is not null and l_num>0 then
3136 g_max_threads:=l_num;
3137 end if;
3138 l_num:=null;
3139 l_num:=fnd_profile.value('EDW_MIN_JOB_LOAD_SIZE');
3140 if l_num is not null and l_num>0 then
3141 g_min_job_load_size:=l_num;
3142 end if;
3143 l_num:=null;
3144 l_num:=fnd_profile.value('EDW_SLEEP_TIME');
3145 if l_num is not null and l_num>0 then
3146 g_sleep_time:=l_num;
3147 end if;
3148 l_key_set:=fnd_profile.value('EDW_FK_SET_SIZE');
3149 if l_key_set is null then
3150 g_key_set:=5;
3151 else
3152 g_key_set:=to_number(l_key_set);
3153 if g_key_set<=1 then
3154 g_key_set:=2;
3155 end if;
3156 end if;
3157 if g_parallel>1 and g_key_set<10 then
3158 g_key_set:=10;
3159 end if;
3160 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('g_key_set='||g_key_set,FND_LOG.LEVEL_STATEMENT);
3161 return true;
3162 Exception when others then
3163 g_status_message:=sqlerrm;
3164 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
3165 g_status:=false;
3166 return false;
3167 End;
3168
3169 function set_thread_type(
3170 p_max_threads number,
3171 p_job_queue_processes number
3172 ) return varchar2 is
3173 l_thread_type varchar2(80);
3174 TYPE CurTyp IS REF CURSOR;
3175 cv CurTyp;
3176 l_stmt varchar2(2000);
3177 l_processes number;
3178 Begin
3179 if g_conc_program_id >0 then
3180 --this process is a conc program
3181 l_thread_type:='CONC';
3182 --this is if someone insists on having dbms jobs when launched through oracle apps
3183 if fnd_profile.value('EDW_FORCE_DBMS_JOB')='Y' then
3184 l_thread_type:='JOB';
3188 if g_debug then
3185 end if;
3186 begin
3187 l_stmt:='select running_processes from FND_CONCURRENT_QUEUES where concurrent_queue_name=''STANDARD''';
3189 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(l_stmt);
3190 end if;
3191 open cv for l_stmt;
3192 fetch cv into l_processes ;
3193 close cv;
3194 if g_debug then
3195 EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_processes='||l_processes);
3196 end if;
3197 if p_job_queue_processes>l_processes then
3198 if g_debug then
3199 EDW_OWB_COLLECTION_UTIL.write_to_log_file('p_job_queue_processes>l_processes. Using dbms jobs...');
3200 end if;
3201 l_thread_type:='JOB';
3202 end if;
3203 exception when others then
3204 null;
3205 end;
3206 else
3207 --not a conc program
3208 l_thread_type:='JOB';
3209 end if;
3210 if g_debug then
3211 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('l_thread_type='||l_thread_type,FND_LOG.LEVEL_STATEMENT);
3212 end if;
3213 return l_thread_type;
3214 Exception when others then
3215 g_status_message:='Error in set_thread_type '||sqlerrm;
3216 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
3217 return null;
3218 End;
3219
3220 --see if the dim can have parallel drill down where a dbms job is launched after a level is
3221 --loaded . this job will drill down the changes to the child levels
3222 --this approach cannot be used if
3223 --if error recovery
3224 --or if there is na_edw update
3225 --or if this is initial load
3226 --or if there is push down
3227 procedure find_parallel_drill_down(
3228 p_levels EDW_OWB_COLLECTION_UTIL.varcharTableType,
3229 p_num_levels number) is
3230 --
3231 l_ilog varchar2(40);
3232 l_job_queue_processes number;
3233 --
3234 Begin
3235 if g_debug then
3236 write_to_log_file_n('In find_parallel_drill_down');
3237 end if;
3238 g_parallel_drill_down:=true;
3239 if g_max_threads is null or g_max_threads<2 then
3240 if g_debug then
3241 write_to_log_file('g_max_threads is null or g_max_threads<2');
3242 end if;
3243 g_parallel_drill_down:=false;
3244 return;
3245 end if;
3246 --see if job_queue_processes > 0
3247 l_job_queue_processes:=edw_owb_collection_util.get_parameter_value('job_queue_processes');
3248 if l_job_queue_processes<1 or l_job_queue_processes<g_max_threads then
3249 if g_debug then
3250 write_to_log_file('job_queue_processes<1 or l_job_queue_processes<g_max_threads');
3251 end if;
3252 g_parallel_drill_down:=false;
3253 return;
3254 end if;
3255 --check for initial load
3256 if edw_owb_collection_util.does_table_have_data(g_object_name)<2 then --no data present
3257 if g_debug then
3258 write_to_log_file('initial load');
3259 end if;
3260 g_parallel_drill_down:=false;
3261 return;
3262 end if;
3263 --check for error recovery
3264 if edw_owb_collection_util.check_table(g_dd_status_table) then
3265 if g_debug then
3266 write_to_log_file(g_dd_status_table||' present');
3267 end if;
3268 g_parallel_drill_down:=false;
3269 return;
3270 end if;
3271 l_ilog:=g_bis_owner||'.'||g_object_name||'IL';
3272 if EDW_OWB_COLLECTION_UTIL.check_table(l_ilog) then
3273 if g_debug then
3274 write_to_log_file(l_ilog||' present');
3275 end if;
3276 g_parallel_drill_down:=false;
3277 return;
3278 end if;
3279 l_ilog:=g_bis_owner||'.'||g_object_name||'ILA';
3280 if EDW_OWB_COLLECTION_UTIL.check_table(l_ilog) then
3281 if g_debug then
3282 write_to_log_file(l_ilog||' present');
3283 end if;
3284 g_parallel_drill_down:=false;
3285 return;
3286 end if;
3287 --check for na_edw update
3288 --this is not reqd. in naedw there is no update
3289 --check for push down
3290 if edw_owb_collection_util.is_push_down_implemented(g_object_name)=true then
3291 if g_debug then
3292 write_to_log_file('push down implemented');
3293 end if;
3294 g_parallel_drill_down:=false;
3295 return;
3296 end if;
3297 --check for partiotions on ltc table. if the ltc are partitioned, we cannot use this process
3298 for i in 1..p_num_levels loop
3299 if edw_owb_collection_util.is_table_partitioned(p_levels(i),g_table_owner)<>'NO' then
3300 if g_debug then
3301 write_to_log_file('partitioned');
3302 end if;
3303 g_parallel_drill_down:=false;
3304 return;
3305 end if;
3306 end loop;
3307 --if there is only 1 level, turn this off
3308 if p_num_levels=1 then
3309 if g_debug then
3310 write_to_log_file('only 1 level');
3311 end if;
3312 g_parallel_drill_down:=false;
3313 return;
3314 end if;
3318 write_to_log_file('profile EDW_NO_PARALLEL_DD defined');
3315 --just in case we want to turn off parallel drill down
3316 if fnd_profile.value('EDW_NO_PARALLEL_DD')='Y' then
3317 if g_debug then
3319 end if;
3320 g_parallel_drill_down:=false;
3321 return;
3322 end if;
3323 Exception when others then
3324 g_status_message:=sqlerrm;
3325 EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Error in find_parallel_drill_down '||
3326 g_status_message,FND_LOG.LEVEL_ERROR);
3327 g_status:=false;
3328 raise;
3329 End;
3330
3331
3332 END EDW_ALL_COLLECT;
[Home] [Help]