1 package body EDW_DERIVED_FACT_COLLECT AS
2 /*$Header: EDWFCOLB.pls 120.1 2006/05/12 02:46:49 vsurendr ship $*/
3
4 /*
5 this api is used to collect a particular src fact to a derived fact
6 */
7 FUNCTION COLLECT_FACT(
8 p_fact_name varchar2,--derived fact
9 p_fact_id number,--derived fact
10 p_src_fact_name varchar2,
11 p_src_fact_id number,
12 p_map_id number,
13 p_conc_id in number,
14 p_conc_program_name in varchar2,
15 p_debug boolean,
16 p_collection_size number,
17 p_parallel number,
18 p_bis_owner varchar2,
19 p_table_owner varchar2,
20 p_ins_rows_processed out NOCOPY number,
21 p_ilog varchar2,
22 p_dlog varchar2,
23 p_forall_size number,
24 p_update_type varchar2,
25 p_fact_dlog varchar2,
26 p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
27 p_number_skip_cols number,
28 p_load_fk number,
29 p_fresh_restart boolean,
30 p_op_table_space varchar2,
31 p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
32 p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
33 p_number_bu_tables number,
34 p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan the actual src fact
35 p_load_mode varchar2,
36 p_rollback varchar2,
37 p_src_join_nl_percentage number,
38 p_thread_type varchar2,
39 p_max_threads number,
40 p_min_job_load_size number,
41 p_sleep_time number,
42 p_hash_area_size number,
43 p_sort_area_size number,
44 p_trace boolean,
45 p_read_cfig_options boolean
46 ) return boolean is
47 l_pre_hook varchar2(10);
48 l_post_hook varchar2(10);
49 Begin
50 g_debug:=p_debug;
51 g_fact_name:=p_fact_name;
52 g_fact_id:=p_fact_id;
53 g_number_mapping_ids:=1;--we are looking at this mapping only
54 g_mapping_ids(g_number_mapping_ids):=p_map_id;
55 g_src_objects(g_number_mapping_ids):=p_src_fact_name;
56 g_src_object_ids(g_number_mapping_ids):=p_src_fact_id;
57 g_collection_size :=p_collection_size;
58 g_parallel:=p_parallel;
59 g_bis_owner:=p_bis_owner;
60 g_table_owner:=p_table_owner;
61 g_ilog:=p_ilog;
62 g_dlog:=p_dlog;
63 g_forall_size:=p_forall_size;
64 g_fresh_restart:=p_fresh_restart;
65 g_thread_type:=p_thread_type;
66 write_to_log_file_n('In COLLECT_FACT where the src and derived facts are specified');
67 write_to_log_file('g_fact_name='||g_fact_name);
68 write_to_log_file('g_fact_id='||g_fact_id);
69 write_to_log_file('p_map_id='||p_map_id);
70 write_to_log_file('p_src_fact_name='||p_src_fact_name);
71 write_to_log_file('p_src_fact_id='||p_src_fact_id);
72 write_to_log_file('g_collection_size='||g_collection_size);
73 write_to_log_file('g_parallel='||g_parallel);
74 write_to_log_file('g_bis_owner='||g_bis_owner);
75 write_to_log_file('g_table_owner='||g_table_owner);
76 write_to_log_file('g_ilog='||g_ilog);
77 write_to_log_file('g_dlog='||g_dlog);
78 write_to_log_file('p_fact_dlog='||p_fact_dlog);
79 write_to_log_file('p_op_table_space='||p_op_table_space);
80 write_to_log_file('p_rollback='||p_rollback);
81 write_to_log_file('p_src_join_nl_percentage='||p_src_join_nl_percentage);
82 write_to_log_file('g_thread_type='||g_thread_type);
83 if g_fresh_restart then
84 write_to_log_file('g_fresh_restart is TRUE');
85 else
86 write_to_log_file('g_fresh_restart is FALSE');
87 end if;
88 g_ins_rows_processed:=0;
89 g_ins_rows_dangling :=0;
90 g_ins_rows_duplicate :=0;
91 g_ins_rows_error :=0;
92 p_ins_rows_processed:=0;
93 init_all; --sets the temp table and iv name
94 if get_fact_fks = false then --get all the fks of the derived fact
95 write_to_log_file_n('get_fact_fks returned with false');
96 return false;
97 end if;
98 l_pre_hook:='N';
99 l_post_hook:='N';
100 if EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(
101 g_fact_name,
102 g_fact_id,
103 g_mapping_ids(g_number_mapping_ids),
104 g_src_objects(g_number_mapping_ids),
105 g_src_object_ids(g_number_mapping_ids),
106 g_fact_fks,
107 g_higher_level,
108 g_parent_dim,
109 g_parent_level,
110 g_level_prefix,
111 g_level_pk,
112 g_level_pk_key,
113 g_dim_pk_key,
114 g_number_fact_fks,
115 p_conc_id,
116 p_conc_program_name,
117 g_debug,
118 g_collection_size,
119 g_parallel,
120 g_bis_owner,
121 g_table_owner,
122 p_ins_rows_processed,
123 false,
124 g_ilog,
125 g_dlog,
126 g_forall_size,
127 p_update_type,
128 p_fact_dlog,
129 p_skip_cols,
130 p_number_skip_cols,
131 p_load_fk,
132 g_fresh_restart,
133 p_op_table_space,
134 p_bu_tables,
135 p_bu_dimensions,
136 p_number_bu_tables,
137 p_bu_src_fact,
138 p_load_mode,
139 p_rollback,
140 p_src_join_nl_percentage,
141 l_pre_hook,
142 l_post_hook
143 ) = false then
144 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT returned with error '||get_time);
145 g_status_message:=EDW_DERIVED_FACT_FACT_COLLECT.get_status_message;
146 return false;
147 end if;
148 return true;
149 Exception when others then
150 g_status_message:=sqlerrm;
151 write_to_log_file_n('Error in COLLECT_FACT '||sqlerrm||get_time);
152 return false;
153 End;
154
155 /*
156 Given a Base fact, refresh all the derv facts
157 This has inputs
158 1. the src fact
159 Best design is where each base fact to derv fact refresh is a conc program
160 */
161 FUNCTION COLLECT_FACT_INC(
162 p_src_fact_name varchar2,
163 p_src_fact_id number,
164 p_derived_facts EDW_OWB_COLLECTION_UTIL.varcharTableType,
165 p_derived_fact_ids EDW_OWB_COLLECTION_UTIL.numberTableType,
166 p_map_ids EDW_OWB_COLLECTION_UTIL.numberTableType,
167 p_number_derived_facts number,
168 p_conc_id in number,
169 p_conc_program_name in varchar2,
170 p_debug boolean,
171 p_collection_size number,
172 p_parallel number,
173 p_bis_owner varchar2,
174 p_table_owner varchar2,--src fact owner
175 p_load_pk out nocopy EDW_OWB_COLLECTION_UTIL.numberTableType,
176 p_ins_rows_processed out NOCOPY EDW_OWB_COLLECTION_UTIL.numberTableType,
177 p_status out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
178 p_message out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
179 p_number_derv_facts out nocopy number,
180 p_forall_size number,
181 p_update_type varchar2,
182 p_fact_dlog varchar2,
183 p_fresh_restart boolean,
184 p_op_table_space varchar2,
185 p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,--before update tables.prop dim change to derv
186 p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
187 p_number_bu_tables number,
188 p_bu_src_fact varchar2,--what table to look at as the src fact. if null, scan the actual src fact
189 p_load_mode varchar2,
190 p_rollback varchar2,
191 p_src_join_nl_percentage number,
192 p_thread_type varchar2,
193 p_max_threads number,
194 p_min_job_load_size number,
195 p_sleep_time number,
196 p_hash_area_size number,
197 p_sort_area_size number,
198 p_trace boolean,
199 p_read_cfig_options boolean,
200 p_job_queue_processes number
201 )return boolean is
202 ll_derived_facts EDW_OWB_COLLECTION_UTIL.varcharTableType;
203 ll_derived_fact_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
204 ll_map_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
205 ll_number_derived_facts number;
206 l_derived_facts EDW_OWB_COLLECTION_UTIL.varcharTableType;
207 l_derived_fact_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
208 l_map_ids EDW_OWB_COLLECTION_UTIL.numberTableType;
209 l_number_derived_facts number;
210 i integer;
211 l_end integer;
212 l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
213 l_job_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
214 l_count number;
215 l_diff number;
216 l_pre_hook varchar2(10);
217 l_post_hook varchar2(10);
218 l_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
219 l_number_skip_cols number;
220 l_found boolean;
221 l_job_status_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
222 l_input_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
223 l_ilog EDW_OWB_COLLECTION_UTIL.varcharTableType;
224 l_dlog EDW_OWB_COLLECTION_UTIL.varcharTableType;
225 l_start_date date;
226 l_end_date date;
227 l_looked_at EDW_OWB_COLLECTION_UTIL.numberTableType;
228 l_number_looked_at number;
229 l_bool_flag boolean;
230 l_log_file varchar2(200);
231 l_number_jobs number;
232 l_max_main_jobs integer;
233 --------------------------------------
234 l_temp_conc_name varchar2(200);
235 l_temp_conc_short_name varchar2(200);
236 l_temp_exe_name varchar2(200);
237 l_bis_short_name varchar2(100);
238 l_flag boolean;
239 l_parallel_flag varchar2(10);
240 l_try_serial boolean;
241 -------------------------------------
242 l_errbuf varchar2(2000);
243 l_retcode varchar2(200);
244 -------------------------------------
245 Begin
246 g_debug:=p_debug;
247 if g_debug then
248 write_to_log_file_n('In COLLECT_FACT_INC where the src and derived facts are specified');
249 write_to_log_file('p_src_fact_name='||p_src_fact_name);
250 write_to_log_file('p_max_threads='||p_max_threads||', p_thread_type='||p_thread_type);
251 end if;
252 g_collection_size:=p_collection_size;
253 g_parallel:=p_parallel;
254 g_bis_owner:=p_bis_owner;
255 g_table_owner:=p_table_owner;
256 g_forall_size:=p_forall_size;
257 g_fresh_restart:=p_fresh_restart;
258 g_thread_type:=p_thread_type;
259 l_pre_hook:='Y';
260 l_post_hook:='Y';
261 l_number_looked_at:=0;
262 l_temp_conc_name:='Sub-Proc DFInc-'||p_src_fact_id;
263 l_temp_conc_short_name:='CONC_FINC_'||p_src_fact_id||'_CONC';
264 l_temp_exe_name:='EXE_FINC_'||p_src_fact_id||'_EXE';
265 l_bis_short_name:='BIS';
266 l_parallel_flag:='N';
267 --is derv facts are specified, use them. else use
268 if p_number_derived_facts=0 or p_number_derived_facts is null then
269 if EDW_OWB_COLLECTION_UTIL.get_all_derived_facts(
270 p_src_fact_name,
271 ll_derived_facts,
272 ll_derived_fact_ids,
273 ll_map_ids,--the map id of the derived fact and this source fact
274 ll_number_derived_facts)=false then
275 g_status_message:=EDW_OWB_COLLECTION_UTIL.g_status_message;
276 return false;
277 end if;
278 l_number_derived_facts:=0;
279 for i in 1..ll_number_derived_facts loop
280 if EDW_OWB_COLLECTION_UTIL.is_inc_refresh_implemented(ll_derived_facts(i))=true then
281 l_number_derived_facts:=l_number_derived_facts+1;
282 l_derived_facts(l_number_derived_facts):=ll_derived_facts(i);
283 l_derived_fact_ids(l_number_derived_facts):=ll_derived_fact_ids(i);
284 l_map_ids(l_number_derived_facts):=ll_map_ids(i);
285 end if;
286 end loop;
287 else
288 l_derived_facts:=p_derived_facts;
289 l_derived_fact_ids:=p_derived_fact_ids;
290 l_map_ids:=p_map_ids;
291 l_number_derived_facts:=p_number_derived_facts;
292 end if;
293 if g_debug then
294 write_to_log_file_n('The derived facts for inc update');
295 for i in 1..l_number_derived_facts loop
296 write_to_log_file(l_derived_facts(i)||' '||l_derived_fact_ids(i)||' '||l_map_ids(i));
297 end loop;
298 end if;
299 if l_number_derived_facts>0 then
300 if p_max_threads>1 then
301 if g_debug then
302 write_to_log_file_n('Multi threaded');
303 end if;
304 l_parallel_flag:='Y';
305 if g_thread_type='CONC' then
306 if create_conc_program(l_temp_conc_name,l_temp_conc_short_name,l_temp_exe_name,l_bis_short_name)=false then
307 g_thread_type:='JOB';
308 end if;
309 end if;
310 if g_debug then
311 if g_thread_type='CONC' then
312 write_to_log_file_n('Use Concurrent Requests');
313 else
314 write_to_log_file_n('DONT Use Concurrent Requests');
315 end if;
316 end if;
317 if g_thread_type='CONC' then
318 l_max_main_jobs:=p_max_threads;--launch as many as possible
319 else
320 l_max_main_jobs:=trunc(p_job_queue_processes/(p_max_threads+1))+
321 sign(mod(p_job_queue_processes,(p_max_threads+1)));
322 end if;
323 if l_max_main_jobs<=0 then
324 l_max_main_jobs:=1;
325 end if;
326 if g_debug then
327 write_to_log_file_n('l_max_main_jobs='||l_max_main_jobs);
328 if g_thread_type='JOB' then
329 write_to_log_file_n('Use Jobs, Parallel operation');
330 elsif g_thread_type='CONC' then
331 write_to_log_file_n('Use Concurrent Requests, Parallel operation');
332 else
333 write_to_log_file_n('Serial operation');
334 end if;
335 end if;
336 i:=0;
337 loop
338 if i<=l_number_derived_facts then
339 l_count:=0;
340 for j in 1..i loop
341 if l_job_status(j)='R' then
342 l_count:=l_count+1;
343 end if;
344 end loop;
345 l_diff:=l_max_main_jobs-l_count;--how many are active at each time
346 l_end:=i+l_diff;
347 if l_diff>0 then
348 --launch more threads
349 loop
350 i:=i+1;
351 if i>l_number_derived_facts then
352 exit;
353 end if;
354 if i>l_end then
355 i:=i-1;
356 exit;
357 end if;
358 g_fact_name:=l_derived_facts(i);
359 g_fact_id:=l_derived_fact_ids(i);
360 g_number_mapping_ids:=1;--we are looking at this mapping only
361 g_mapping_ids(g_number_mapping_ids):=l_map_ids(i);
362 g_src_objects(g_number_mapping_ids):=p_src_fact_name;
363 g_src_object_ids(g_number_mapping_ids):=p_src_fact_id;
364 g_number_fact_fks:=0;
365 if get_fact_fks = false then --get all the fks of the derived fact
366 return false;
367 end if;
368 p_load_pk(i):=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk ;
369 if p_load_pk(i) is null then
370 return false;
371 end if;
372 l_number_skip_cols:=0;
373 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(l_derived_facts(i),'DERIVED FACT',l_skip_cols,
374 l_number_skip_cols)=false then
375 return false;
376 end if;
377 l_start_date:=sysdate;
378 if EDW_OWB_COLLECTION_UTIL.log_collection_start(l_derived_facts(i),l_derived_fact_ids(i),
379 'FACT',l_start_date,p_conc_id,p_load_pk(i))=false then
380 return false;
381 end if;
382 p_ins_rows_processed(i):=0;
383 --l_ilog(i):=g_bis_owner||'.I_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
384 --l_dlog(i):=g_bis_owner||'.D_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
385 l_ilog(i):=edw_owb_collection_util.get_fact_dfact_ilog(g_bis_owner,p_src_fact_id,l_derived_fact_ids(i));
386 l_dlog(i):=edw_owb_collection_util.get_fact_dfact_dlog(g_bis_owner,p_src_fact_id,l_derived_fact_ids(i));
387 l_input_table(i):=g_bis_owner||'.INP_TAB_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
388 l_log_file:='LOG_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
392 l_input_table(i),
389 l_job_status_table(i):=g_bis_owner||'.JOB_STATUS_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
390 if EDW_OWB_COLLECTION_UTIL.create_derv_fact_inp_table(
391 l_derived_facts(i),
393 l_derived_fact_ids(i),
394 l_map_ids(i),
395 p_src_fact_name,
396 p_src_fact_id,
397 g_fact_fks,
398 g_higher_level,
399 g_parent_dim,
400 g_parent_level,
401 g_level_prefix,
402 g_level_pk,
403 g_level_pk_key,
404 g_dim_pk_key,
405 g_number_fact_fks,
406 p_conc_id,
407 p_conc_program_name,
408 g_debug,
409 g_collection_size,
410 g_parallel,
411 g_bis_owner,
412 g_table_owner,
413 false,--full refresh
414 g_forall_size,
415 p_update_type,
416 p_fact_dlog,
417 l_skip_cols,
418 l_number_skip_cols,
419 p_load_pk(i),
420 g_fresh_restart,
421 p_op_table_space,
422 p_bu_tables,
423 p_bu_dimensions,
424 p_number_bu_tables,
425 p_bu_src_fact,
426 p_load_mode,
427 p_rollback,
428 p_src_join_nl_percentage,
429 p_max_threads,
430 p_min_job_load_size,
431 p_sleep_time,
432 l_job_status_table(i),
433 p_hash_area_size,
434 p_sort_area_size,
435 p_trace,
436 p_read_cfig_options
437 )=false then
438 return false;
439 end if;
440 /*
441 Launch the thread
442 */
443 begin
444 l_try_serial:=false;
445 if g_thread_type='CONC' then
446 if g_debug then
447 write_to_log_file_n('Launch conc process '||l_temp_conc_name||' '||i);
448 end if;
449 if g_debug then
450 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD('''||g_fact_name||''','||
451 g_fact_id||','''||l_log_file||''','''||l_input_table(i)||''','''||l_ilog(i)||''','''||l_dlog(i)||''','''||
452 l_pre_hook||''','''||l_post_hook||''','''||g_thread_type||''');');
453 end if;
454 l_flag:=true;
455 l_job_status(i):='R';
456 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),-1)=false then
457 return false;
458 end if;
459 l_job_id(i):=FND_REQUEST.SUBMIT_REQUEST(
460 application=>l_bis_short_name,
461 program=>l_temp_conc_short_name,
462 argument1=>g_fact_name,
463 argument2=>g_fact_id,
464 argument3=>l_log_file,
465 argument4=>l_input_table(i),
466 argument5=>l_ilog(i),
467 argument6=>l_dlog(i),
468 argument7=>l_pre_hook,
469 argument8=>l_post_hook,
470 argument9=>g_thread_type);
471 if EDW_OWB_COLLECTION_UTIL.update_inp_table_concid(l_input_table(i),l_job_id(i))=false then
472 return false;
473 end if;
474 if l_job_id(i)<=0 then
475 l_try_serial:=true;
476 end if;
477 commit;--commit very imp, starts the conc request
478 else --here g_thread_type='JOB'
479 if g_debug then
480 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD('''||g_fact_name||''','||
481 g_fact_id||','''||l_log_file||''','''||l_input_table(i)||''','''||l_ilog(i)||''','''||l_dlog(i)||''','''||
482 l_pre_hook||''','''||l_post_hook||''','''||g_thread_type||''');');
483 end if;
484 DBMS_JOB.SUBMIT(l_job_id(i),'EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD('''||
485 g_fact_name||''','||g_fact_id||','''||l_log_file||''','''||l_input_table(i)||''','''||
486 l_ilog(i)||''','''||l_dlog(i)||''','''||l_pre_hook||''','''||l_post_hook||''','''||
487 g_thread_type||''');');
488 l_job_status(i):='R';
489 if g_debug then
490 write_to_log_file_n('Job '||l_job_id(i)||' launched for '||g_fact_name||' and '||
491 p_src_fact_name||get_time);
492 end if;
493 /*
494 update the inp table for the job id
495 */
496 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(i))=false then
497 return false;
498 end if;
499 if l_job_id(i)<=0 then
500 l_try_serial:=true;
504 exception when others then
501 end if;
502 commit;--commit only after updating the inp table
503 end if;
505 if g_debug then
506 write_to_log_file_n('Error '||sqlerrm||'. Attemting a serial load '||get_time);
507 end if;
508 l_try_serial:=true;
509 end;
510 if l_try_serial then
511 if g_debug then
512 write_to_log_file_n('Attempt serial load');
513 end if;
514 l_job_id(i):=0-i;--this is just a temp setting. There is really no job. we need this for log_collection_detail
515 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(i))=false then
516 return false;
517 end if;
518 commit;
519 l_job_status(i):='R';
520 EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD(
521 l_errbuf,
522 l_retcode,
523 g_fact_name,
524 g_fact_id,
525 l_log_file,
526 l_input_table(i),
527 l_ilog(i),
528 l_dlog(i),
529 l_pre_hook,
530 l_post_hook,
531 g_thread_type
532 );
533 l_job_status(i):='Y';
534 end if;
535 end loop;
536 end if;
537 else --here i>g_number_mapping_ids
538 --see if there are any threads still running. if yes, must wait. else exit.
539 if i>l_number_derived_facts then
540 l_number_jobs:=l_number_derived_facts;
541 else
542 l_number_jobs:=i;
543 end if;
544 l_found:=false;
545 for j in 1..l_number_jobs loop
546 if l_job_status(j)='R' then
547 l_found:=true;
548 end if;
549 end loop;
550 if l_found=false then
551 --all processes are done.
552 exit;
553 end if;
554 end if;
555 if i>l_number_derived_facts then
556 l_number_jobs:=l_number_derived_facts;
557 else
558 l_number_jobs:=i;
559 end if;
560 --wait for threads or conc requests
561 if wait_on_jobs(
562 l_job_id,
563 l_job_status,
564 l_number_jobs,
565 p_sleep_time,
566 g_thread_type)=false then
567 return false;
568 end if;
569 for j in 1..l_number_jobs loop
570 if l_job_status(j)<>'R' and EDW_OWB_COLLECTION_UTIL.value_in_table(l_looked_at,l_number_looked_at,
571 l_job_id(j))=false then
572 if get_temp_log_data(g_fact_name,'FACT',p_load_pk(j),p_ins_rows_processed(j))=false then
573 null;
574 end if;
575 if g_debug then
576 write_to_log_file_n('Job '||l_job_id(j)||' rows processed '||p_ins_rows_processed(j));
577 end if;
578 if get_child_job_status(l_job_status_table(j),p_status(j),p_message(j))=false then
579 null;
580 end if;
581 if p_status(j)='SUCCESS' then
582 p_message(j):='Processed '||p_ins_rows_processed(j)||' Rows';
583 end if;
584 l_end_date:=sysdate;
585 if log_collection_detail(
586 g_fact_name,
587 g_fact_id,
588 'FACT',
589 p_conc_id,
590 l_start_date,
591 l_end_date,
592 p_ins_rows_processed(j),
593 p_ins_rows_processed(j),
594 p_ins_rows_processed(j),
595 null,
596 null,
597 null,
598 p_message(j),
599 p_status(j),
600 p_load_pk(j)
601 )=false then
602 null;
603 end if;
604 l_number_looked_at:=l_number_looked_at+1;
605 l_looked_at(l_number_looked_at):=l_job_id(j);
606 end if;
607 end loop;
608 /*must calculate rows processed per thread
609 must also call something similar to return_with_success
610 must have delete_object_log_tables and inp tables etc
611 must terminate jobs if there is error
612 */
613 end loop;
614 --drop the inp tables and the status tables
615 for i in 1..l_number_derived_facts loop
616 if p_status(i)='SUCCESS' then
617 if drop_inp_status_table(l_input_table(i),l_job_status_table(i))=false then
618 null;
619 end if;
620 end if;
621 end loop;
622 --dont delete the conc pgm. we may need the conc request log file
623 /*if g_thread_type='CONC' then
624 --drop the conc programs
625 if EDW_OWB_COLLECTION_UTIL.delete_conc_program(
626 l_temp_conc_short_name,
627 l_temp_exe_name,
628 l_bis_short_name,
629 'SHORT')=false then
630 null;
631 end if;
632 end if;*/
633 else --single thread
637 g_number_mapping_ids:=1;--we are looking at this mapping only
634 for i in 1..l_number_derived_facts loop
635 g_fact_name:=l_derived_facts(i);
636 g_fact_id:=l_derived_fact_ids(i);
638 g_mapping_ids(g_number_mapping_ids):=l_map_ids(i);
639 g_src_objects(g_number_mapping_ids):=p_src_fact_name;
640 g_src_object_ids(g_number_mapping_ids):=p_src_fact_id;
641 if get_fact_fks = false then --get all the fks of the derived fact
642 return false;
643 end if;
644 p_load_pk(i):=EDW_OWB_COLLECTION_UTIL.inc_g_load_pk ;
645 if p_load_pk(i) is null then
646 return false;
647 end if;
648 if EDW_OWB_COLLECTION_UTIL.find_skip_attributes(l_derived_facts(i),'DERIVED FACT',l_skip_cols,
649 l_number_skip_cols)=false then
650 return false;
651 end if;
652 if EDW_OWB_COLLECTION_UTIL.log_collection_start(l_derived_facts(i),l_derived_fact_ids(i),
653 'FACT',sysdate,p_conc_id,p_load_pk(i))=false then
654 return false;
655 end if;
656 p_ins_rows_processed(i):=0;
657 l_start_date:=sysdate;
658 --l_ilog(i):=g_bis_owner||'.I_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
659 --l_dlog(i):=g_bis_owner||'.D_'||p_src_fact_id||'_'||l_derived_fact_ids(i);
660 l_ilog(i):=edw_owb_collection_util.get_fact_dfact_ilog(g_bis_owner,p_src_fact_id,l_derived_fact_ids(i));
661 l_dlog(i):=edw_owb_collection_util.get_fact_dfact_dlog(g_bis_owner,p_src_fact_id,l_derived_fact_ids(i));
662 l_bool_flag:=EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(
663 g_fact_name,
664 g_fact_id,
665 g_mapping_ids(g_number_mapping_ids),
666 g_src_objects(g_number_mapping_ids),
667 g_src_object_ids(g_number_mapping_ids),
668 g_fact_fks,
669 g_higher_level,
670 g_parent_dim,
671 g_parent_level,
672 g_level_prefix,
673 g_level_pk,
674 g_level_pk_key,
675 g_dim_pk_key,
676 g_number_fact_fks,
677 p_conc_id,
678 p_conc_program_name,
679 g_debug,
680 g_collection_size,
681 g_parallel,
682 g_bis_owner,
683 g_table_owner,
684 p_ins_rows_processed(i),
685 false,
686 l_ilog(i),
687 l_dlog(i),
688 g_forall_size,
689 p_update_type,
690 p_fact_dlog,
691 l_skip_cols,
692 l_number_skip_cols,
693 p_load_pk(i),
694 g_fresh_restart,
695 p_op_table_space,
696 p_bu_tables,
697 p_bu_dimensions,
698 p_number_bu_tables,
699 p_bu_src_fact,
700 p_load_mode,
701 p_rollback,
702 p_src_join_nl_percentage,
703 l_pre_hook,
704 l_post_hook
705 );
706 if l_bool_flag=false then
707 g_status_message:=EDW_DERIVED_FACT_FACT_COLLECT.get_status_message;
708 p_status(i):='ERROR';
709 p_message(i):=g_status_message;
710 else
711 p_status(i):='SUCCESS';
712 p_message(i):='Processed '||p_ins_rows_processed(i)||' Rows';
713 end if;
714 l_end_date:=sysdate;
715 if log_collection_detail(
716 g_fact_name,
717 g_fact_id,
718 'FACT',
719 p_conc_id,
720 l_start_date,
721 l_end_date,
722 p_ins_rows_processed(i),
723 p_ins_rows_processed(i),
724 p_ins_rows_processed(i),
725 null,
726 null,
727 null,
728 p_message(i),
729 p_status(i),
730 p_load_pk(i)
731 )=false then
732 null;
733 end if;
734 end loop;
735 end if;
736 else
737 if g_debug then
738 write_to_log_file_n('Not a source for any derived fact');
739 end if;
740 end if;
741 /*
742 drop the ilog and dlog after truncating the base fact snapshot log
743 this is to be done only if all loads are a sucsess
744 */
745 l_bool_flag:=true;
746 for i in 1..l_number_derived_facts loop
747 if p_status(i)='ERROR' then
748 l_bool_flag:=false;
749 exit;
750 end if;
751 end loop;
752 if l_bool_flag then
753 if delete_object_log_tables(
754 p_src_fact_name,
755 p_table_owner,
756 p_bis_owner,
757 p_fact_dlog,
758 l_ilog,
759 l_dlog,
760 l_number_derived_facts)=false then
761 return false;
762 end if;
763 end if;
764 return l_bool_flag;
765 Exception when others then
766 g_status_message:=sqlerrm;
767 write_to_log_file_n('Error in COLLECT_FACT_INC '||sqlerrm||get_time);
768 return false;
769 End;
770
771 /*
772 given a derv fact, do a full refresh
773 this api is for the case where only the derived fact is specified and it does collection of
774 all source facts to the derived fact. when this api is called, it must do a full refresh of the
775 derived fact
776 */
777 FUNCTION COLLECT_FACT(
778 p_fact_name varchar2,
779 p_conc_id in number,
780 p_conc_program_name in varchar2,
784 p_bis_owner varchar2,
781 p_debug boolean,
782 p_collection_size number,
783 p_parallel number,
785 p_table_owner varchar2,
786 p_ins_rows_processed out NOCOPY number,
787 p_forall_size number,
788 p_update_type varchar2,
789 p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
790 p_number_skip_cols number,
791 p_load_fk number,
792 p_fresh_restart boolean,
793 p_op_table_space varchar2,
794 p_rollback varchar2,
795 p_src_join_nl_percentage number,
796 p_thread_type varchar2,
797 p_max_threads number,
798 p_min_job_load_size number,
799 p_sleep_time number,
800 p_hash_area_size number,
801 p_sort_area_size number,
802 p_trace boolean,
803 p_read_cfig_options boolean
804 ) return boolean is
805 l_ins_rows_processed number;
806 l_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType;
807 l_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType;
808 l_number_bu_tables number:=0;
809 l_bu_src_fact varchar2(400):=null;
810 l_load_mode varchar2(400);
811 l_input_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
812 l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
813 l_job_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
814 l_number_jobs number;
815 l_count number;
816 l_diff number;
817 i integer;
818 l_end integer;
819 l_found boolean;
820 l_log_file varchar2(200);
821 l_pre_hook varchar2(10);
822 l_post_hook varchar2(10);
823 l_job_status_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
824 l_ilog EDW_OWB_COLLECTION_UTIL.varcharTableType;
825 l_dlog EDW_OWB_COLLECTION_UTIL.varcharTableType;
826 l_start_date date;
827 l_end_date date;
828 l_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
829 l_message EDW_OWB_COLLECTION_UTIL.varcharTableType;
830 -----------------------------------------
831 l_temp_conc_name varchar2(200);
832 l_temp_conc_short_name varchar2(200);
833 l_temp_exe_name varchar2(200);
834 l_bis_short_name varchar2(200);
835 l_try_serial boolean;
836 -----------------------------------------
837 l_errbuf varchar2(2000);
838 l_retcode varchar2(200);
839 -----------------------------------------
840 begin
841 --get the fks and the mapping details;
842 g_fact_name:=p_fact_name;
843 g_debug:=p_debug;
844 g_collection_size:=p_collection_size;
845 g_parallel:=p_parallel;
846 g_ins_rows_processed:=0;
847 g_ins_rows_dangling :=0;
848 g_ins_rows_duplicate :=0;
849 g_ins_rows_error :=0;
850 p_ins_rows_processed:=0;
851 g_bis_owner:=p_bis_owner;
852 g_table_owner:=p_table_owner;
853 g_forall_size:=p_forall_size;
854 g_fresh_restart:=p_fresh_restart;
855 g_thread_type:=p_thread_type;
856 l_number_jobs:=0;
857 write_to_log_file_n('In COLLECT_FACT where only the derived facts is specified, Full Refresh');
858 write_to_log_file('g_fact_name='||g_fact_name);
859 write_to_log_file('g_collection_size='||g_collection_size);
860 write_to_log_file('g_parallel='||g_parallel);
861 write_to_log_file('g_bis_owner='||g_bis_owner);
862 write_to_log_file('g_table_owner='||g_table_owner);
863 write_to_log_file('p_op_table_space='||p_op_table_space);
864 write_to_log_file('p_rollback='||p_rollback);
865 write_to_log_file('p_src_join_nl_percentage='||p_src_join_nl_percentage);
866 write_to_log_file('g_thread_type='||g_thread_type);
867 if g_fresh_restart then
868 write_to_log_file('g_fresh_restart is TRUE');
869 else
870 write_to_log_file('g_fresh_restart is FALSE');
871 end if;
872 init_all;
873 if get_fact_id = false then
874 return false;
875 end if;
876 if get_fact_fks = false then
877 return false;
878 end if;
879 if get_fact_mappings = false then
880 return false;
881 end if;
882 --first truncate the derived fact
883 if truncate_derived_fact=false then
884 write_to_log_file_n('truncate_derived_fact returned with error');
885 return false;
886 end if;
887 --pre hook and post hook only for inc refresh
888 l_pre_hook:='N';
889 l_post_hook:='N';
890 if p_max_threads>1 then
891 if g_debug then
892 write_to_log_file_n('Multi threaded');
893 end if;
894 l_number_jobs:=0;
895 i:=0;
896 l_temp_conc_name:='Sub-Proc DFFull-'||g_fact_id;
897 l_temp_conc_short_name:='CONC_FFULL_'||g_fact_id||'_CONC';
898 l_temp_exe_name:='EXE_FFULL_'||g_fact_id||'_EXE';
899 l_bis_short_name:='BIS';
900 if g_thread_type='CONC' then
901 --create the executable, conc program etc
902 if create_conc_program(l_temp_conc_name,l_temp_conc_short_name,l_temp_exe_name,l_bis_short_name)=false then
903 if g_debug then
904 write_to_log_file_n('Could not create seed data for conc programs. Trying jobs');
905 end if;
906 g_thread_type:='JOB';
907 end if;
908 end if;
909 loop
910 if i<=g_number_mapping_ids then
911 l_count:=0;
912 for j in 1..l_number_jobs loop
913 if l_job_status(j)='R' then
914 l_count:=l_count+1;
915 end if;
916 end loop;
917 l_diff:=p_max_threads-l_count;
918 l_end:=i+l_diff;
919 if l_diff>0 then
920 --launch more threads
921 loop
922 i:=i+1;
923 if i>g_number_mapping_ids then
924 exit;
925 end if;
926 if i>l_end then
927 i:=i-1;
928 exit;
932 --l_dlog(i):=g_bis_owner||'.D_'||g_fact_id||'_'||g_src_object_ids(i);
929 end if;
930 l_ins_rows_processed:=0;
931 --l_ilog(i):=g_bis_owner||'.I_'||g_fact_id||'_'||g_src_object_ids(i);
933 l_ilog(i):=edw_owb_collection_util.get_fact_dfact_ilog(g_bis_owner,g_fact_id,g_src_object_ids(i));
934 l_dlog(i):=edw_owb_collection_util.get_fact_dfact_dlog(g_bis_owner,g_fact_id,g_src_object_ids(i));
935 l_input_table(i):=g_bis_owner||'.INP_TAB_'||g_fact_id||'_'||g_src_object_ids(i);
936 l_log_file:='LOG_'||g_fact_id||'_'||g_src_object_ids(i);
937 l_job_status_table(i):=g_bis_owner||'.JOB_STATUS_'||g_fact_id||'_'||g_src_object_ids(i);
938 if EDW_OWB_COLLECTION_UTIL.create_derv_fact_inp_table(
939 g_fact_name,
940 l_input_table(i),
941 g_fact_id,
942 g_mapping_ids(i),
943 g_src_objects(i),
944 g_src_object_ids(i),
945 g_fact_fks,
946 g_higher_level,
947 g_parent_dim,
948 g_parent_level,
949 g_level_prefix,
950 g_level_pk,
951 g_level_pk_key,
952 g_dim_pk_key,
953 g_number_fact_fks,
954 p_conc_id,
955 p_conc_program_name,
956 g_debug,
957 g_collection_size,
958 g_parallel,
959 g_bis_owner,
960 g_table_owner,
961 true,--full refresh
962 g_forall_size,
963 p_update_type,
964 null,
965 p_skip_cols,
966 p_number_skip_cols,
967 p_load_fk,
968 g_fresh_restart,
969 p_op_table_space,
970 l_bu_tables,
971 l_bu_dimensions,
972 l_number_bu_tables,
973 l_bu_src_fact,
974 l_load_mode,
975 p_rollback,
976 p_src_join_nl_percentage,
977 p_max_threads,
978 p_min_job_load_size,
979 p_sleep_time,
980 l_job_status_table(i),
981 p_hash_area_size,
982 p_sort_area_size,
983 p_trace,
984 p_read_cfig_options
985 )=false then
986 return false;
987 end if;
988 /*
989 Launch the thread
990 */
991 l_number_jobs:=l_number_jobs+1;
992 if g_debug then
993 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD('''||g_fact_name||''','||
994 g_fact_id||','''||l_log_file||''','''||l_input_table(i)||''','''||l_ilog(i)||''','''||l_dlog(i)||''','''||
995 l_pre_hook||''','''||l_post_hook||''','''||g_thread_type||''');');
996 end if;
997 begin
998 l_try_serial:=false;
999 if g_thread_type='CONC' then
1000 l_job_id(l_number_jobs):=FND_REQUEST.SUBMIT_REQUEST(
1001 application=>l_bis_short_name,
1002 program=>l_temp_conc_short_name,
1003 argument1=>g_fact_name,
1004 argument2=>g_fact_id,
1005 argument3=>l_log_file,
1006 argument4=>l_input_table(i),
1007 argument5=>l_ilog(i),
1008 argument6=>l_dlog(i),
1009 argument7=>l_pre_hook,
1010 argument8=>l_post_hook,
1011 argument9=>g_thread_type);
1012 l_job_status(l_number_jobs):='R';
1013 if g_debug then
1014 write_to_log_file_n('Concurrent Request '||l_job_id(l_number_jobs)||' launched for '||g_fact_name||' and '||
1015 g_src_objects(i)||get_time);
1016 end if;
1017 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
1018 return false;
1019 end if;
1020 if l_job_id(l_number_jobs)<=0 then
1021 l_try_serial:=true;
1022 end if;
1023 commit;--commit only after updating the inp table
1024 else
1025 DBMS_JOB.SUBMIT(l_job_id(l_number_jobs),'EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD('''||
1026 g_fact_name||''','||g_fact_id||','''||l_log_file||''','''||l_input_table(i)||''','''||
1027 l_ilog(i)||''','''||l_dlog(i)||''','''||l_pre_hook||''','''||l_post_hook||''','''||g_thread_type||
1028 ''');');
1029 l_job_status(l_number_jobs):='R';
1030 if g_debug then
1031 write_to_log_file_n('Job '||l_job_id(l_number_jobs)||' launched for '||g_fact_name||' and '||
1032 g_src_objects(i)||get_time);
1033 end if;
1034 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
1035 return false;
1036 end if;
1037 if l_job_id(l_number_jobs)<=0 then
1038 l_try_serial:=true;
1039 end if;
1040 commit;--commit only after updating the inp table
1041 end if;
1042 exception when others then
1046 end if;
1043 if g_debug then
1044 write_to_log_file_n('Error launching dbms job '||sqlerrm||'. Attempting serial load'||
1045 get_time);
1047 l_try_serial:=true;
1048 end;
1049 if l_try_serial then
1050 if g_debug then
1051 write_to_log_file_n('Attempt serial load');
1052 end if;
1053 l_job_id(l_number_jobs):=0-i;--give negative ids
1054 if EDW_OWB_COLLECTION_UTIL.update_inp_table_jobid(l_input_table(i),l_job_id(l_number_jobs))=false then
1055 return false;
1056 end if;
1057 commit;--commit only after updating the inp table
1058 EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD(
1059 l_errbuf,
1060 l_retcode,
1061 g_fact_name,
1062 g_fact_id,
1063 l_log_file,
1064 l_input_table(i),
1065 l_ilog(i),
1066 l_dlog(i),
1067 l_pre_hook,
1068 l_post_hook,
1069 g_thread_type
1070 );
1071 l_job_status(l_number_jobs):='Y';
1072 end if;
1073 end loop;
1074 end if;
1075 else --here i>g_number_mapping_ids
1076 --see if there are any threads still running. if yes, must wait. else exit.
1077 l_found:=false;
1078 for j in 1..l_number_jobs loop
1079 if l_job_status(j)='R' then
1080 l_found:=true;
1081 end if;
1082 end loop;
1083 if l_found=false then
1084 --all processes are done.
1085 exit;
1086 end if;
1087 end if;
1088 --wait for threads
1089 if wait_on_jobs(
1090 l_job_id,
1091 l_job_status,
1092 l_number_jobs,
1093 p_sleep_time,
1094 g_thread_type)=false then
1095 return false;
1096 end if;
1097 end loop;
1098 for i in 1..g_number_mapping_ids loop
1099 if get_child_job_status(l_job_status_table(i),l_status(i),l_message(i))=false then
1100 null;
1101 end if;
1102 if g_debug then
1103 write_to_log_file_n('Job '||l_job_id(i)||' status '||l_status(i)||', message '||l_message(i));
1104 end if;
1105 if l_status(i)='ERROR' then
1106 g_status_message:=l_message(i);
1107 return false;
1108 end if;
1109 end loop;
1110 --drop the inp tables and the status tables
1111 for i in 1..g_number_mapping_ids loop
1112 if drop_inp_status_table(l_input_table(i),l_job_status_table(i))=false then
1113 null;
1114 end if;
1115 end loop;
1116 else
1117 if g_debug then
1118 write_to_log_file_n('Single thread');
1119 end if;
1120 for i in 1..g_number_mapping_ids loop
1121 --call the detailed fact to fact collection
1122 l_ins_rows_processed:=0;
1123 --l_ilog(i):=g_bis_owner||'.I_'||g_fact_id||'_'||g_src_object_ids(i);
1124 --l_dlog(i):=g_bis_owner||'.D_'||g_fact_id||'_'||g_src_object_ids(i);
1125 l_ilog(i):=edw_owb_collection_util.get_fact_dfact_ilog(g_bis_owner,g_fact_id,g_src_object_ids(i));
1126 l_dlog(i):=edw_owb_collection_util.get_fact_dfact_dlog(g_bis_owner,g_fact_id,g_src_object_ids(i));
1127 if g_debug then
1128 write_to_log_file_n('Going to collect mapping: id='||g_mapping_ids(i)||', src='||g_src_objects(i));
1129 end if;
1130 -- the last true is for yes to full refresh
1131 if EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT(
1132 g_fact_name,
1133 g_fact_id,
1134 g_mapping_ids(i),
1135 g_src_objects(i),
1136 g_src_object_ids(i),
1137 g_fact_fks,
1138 g_higher_level,
1139 g_parent_dim,
1140 g_parent_level,
1141 g_level_prefix,
1142 g_level_pk,
1143 g_level_pk_key,
1144 g_dim_pk_key,
1145 g_number_fact_fks,
1146 p_conc_id,
1147 p_conc_program_name,
1148 g_debug,
1149 g_collection_size,
1150 g_parallel,
1151 g_bis_owner,
1152 g_table_owner,
1153 l_ins_rows_processed,
1154 true,
1155 l_ilog(i),
1156 l_dlog(i),
1157 g_forall_size,
1158 p_update_type,
1159 null,
1160 p_skip_cols,
1161 p_number_skip_cols,
1162 p_load_fk,
1163 g_fresh_restart,
1164 p_op_table_space,
1165 l_bu_tables,
1166 l_bu_dimensions,
1167 l_number_bu_tables,
1168 l_bu_src_fact,
1169 l_load_mode,
1170 p_rollback,
1171 p_src_join_nl_percentage,
1172 l_pre_hook,
1173 l_post_hook) = false then
1174 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT returned with error '||get_time);
1175 g_status_message:=EDW_DERIVED_FACT_FACT_COLLECT.get_status_message;
1176 return false;
1177 else
1178 g_ins_rows_processed:=g_ins_rows_processed+l_ins_rows_processed;
1179 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT returned with success '||get_time);
1180 end if;
1181 end loop;
1182 end if;
1183 p_ins_rows_processed:=g_ins_rows_processed;
1184 --drop the ilog and dlog table
1185 for i in 1..g_number_mapping_ids loop
1189 end if;
1186 if EDW_OWB_COLLECTION_UTIL.drop_table(edw_owb_collection_util.get_fact_dfact_ilog(
1187 g_bis_owner,g_fact_id,g_src_object_ids(i)))= false then
1188 null;
1190 if EDW_OWB_COLLECTION_UTIL.drop_table(edw_owb_collection_util.get_fact_dfact_ilog(
1191 g_bis_owner,g_fact_id,g_src_object_ids(i))||'A') = false then
1192 null;
1193 end if;
1194 if EDW_OWB_COLLECTION_UTIL.drop_table(edw_owb_collection_util.get_fact_dfact_dlog(
1195 g_bis_owner,g_fact_id,g_src_object_ids(i))) = false then
1196 null;
1197 end if;
1198 if EDW_OWB_COLLECTION_UTIL.drop_table(edw_owb_collection_util.get_fact_dfact_dlog(
1199 g_bis_owner,g_fact_id,g_src_object_ids(i))||'A') = false then
1200 null;
1201 end if;
1202 end loop;
1203 if g_debug then
1204 write_to_log_file_n('EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT done. Total rows processed ='||g_ins_rows_processed);
1205 end if;
1206 return true;
1207 Exception when others then
1208 g_status_message:=sqlerrm;
1209 write_to_log_file_n('Error in COLLECT_FACT '||sqlerrm||get_time);
1210 return false;
1211 End;
1212
1213 function truncate_derived_fact return boolean is
1214 l_stmt varchar2(2000);
1215 l_snplog varchar2(400);
1216 Begin
1217 if g_debug then
1218 write_to_log_file_n('In truncate_derived_fact');
1219 end if;
1220 l_stmt:='truncate table '||g_table_owner||'.'||g_fact_name;
1221 if g_debug then
1222 write_to_log_file_n('Going to execute '||l_stmt);
1223 end if;
1224 execute immediate l_stmt;
1225 --truncate any snapshot log also
1226 l_snplog:=EDW_OWB_COLLECTION_UTIL.get_table_snapshot_log(g_fact_name);
1227 if l_snplog is not null then
1228 l_stmt:='truncate table '||g_table_owner||'.'||l_snplog;
1229 if g_debug then
1230 write_to_log_file_n('Going to execute '||l_stmt);
1231 end if;
1232 execute immediate l_stmt;
1233 end if;
1234 return true;
1235 Exception when others then
1236 g_status_message:=sqlerrm;
1237 write_to_log_file_n(g_status_message);
1238 return false;
1239 End;
1240
1241 function get_fact_id return boolean is
1242 TYPE CurTyp IS REF CURSOR;
1243 cv CurTyp;
1244 l_stmt varchar2(1000);
1245 begin
1246 if g_debug then
1247 write_to_log_file_n('Entered get_fact_id');
1248 end if;
1249 l_stmt:='select fact_id from edw_facts_md_v where fact_name=:s';
1250 open cv for l_stmt using g_fact_name;
1251 fetch cv into g_fact_id;
1252 close cv;
1253 if g_debug then
1254 write_to_log_file_n('Finished get_fact_id');
1255 end if;
1256 return true;
1257 Exception when others then
1258 begin
1259 close cv;
1260 exception when others then
1261 null;
1262 end;
1263 g_status_message:=sqlerrm;
1264 write_to_log_file_n('Error in get_fact_id '||sqlerrm||' '||get_time);
1265 return false;
1266 End;
1267
1268 function get_fact_fks return boolean is
1269 TYPE CurTyp IS REF CURSOR;
1270 cv CurTyp;
1271 l_stmt varchar2(10000);
1272 l_fk_cons EDW_OWB_COLLECTION_UTIL.varcharTableType;
1273 l_in_stmt varchar2(10000);
1274 l_lvl_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
1275 l_lvl_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType;
1276 l_lvl_dim EDW_OWB_COLLECTION_UTIL.varcharTableType;
1277 l_number_lvl number;
1278 l_prefix varchar2(100);
1279 l_dim_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
1280 l_prefix_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
1281 l_number_dim_fk number;
1282 begin
1283 if g_debug then
1284 write_to_log_file_n('Entered get_fact_fks');
1285 end if;
1286 l_stmt:='select fk_item.column_name, fk.foreign_key_name, dim.dim_name, '||
1287 'dim.dim_id,pk_item.column_name '||
1288 'from '||
1289 'edw_foreign_keys_md_v fk, '||
1290 'edw_pvt_key_columns_md_v isu, '||
1291 'edw_pvt_columns_md_v fk_item, '||
1292 'edw_unique_keys_md_v pk, '||
1293 'edw_pvt_key_columns_md_v pisu, '||
1294 'edw_pvt_columns_md_v pk_item, '||
1295 'edw_dimensions_md_v dim '||
1296 'where '||
1297 'fk.entity_id=:a '||
1298 'and isu.key_id=fk.foreign_key_id '||
1299 'and fk_item.column_id=isu.column_id '||
1300 'and fk.key_id=pk.key_id '||
1301 'and pisu.key_id=pk.key_id '||
1302 'and pk_item.column_id=pisu.column_id '||
1303 'and dim.dim_id=pk.entity_id ';
1304 if g_debug then
1305 write_to_log_file_n('Going to execute '||l_stmt);
1306 end if;
1307 open cv for l_stmt using g_fact_id;
1308 g_number_fact_fks:=1;
1309 loop
1310 fetch cv into
1311 g_fact_fks(g_number_fact_fks),
1312 l_fk_cons(g_number_fact_fks),
1313 g_parent_dim(g_number_fact_fks),
1314 g_parent_dim_id(g_number_fact_fks),
1315 g_dim_pk_key(g_number_fact_fks);
1316 exit when cv%notfound;
1317 g_number_fact_fks:=g_number_fact_fks+1;
1318 end loop;
1319 close cv;
1320 g_number_fact_fks:=g_number_fact_fks-1;
1321 for i in 1..g_number_fact_fks loop
1322 g_higher_level(i):=false;
1323 g_level_prefix(i):=null;
1324 g_parent_level(i):=null;
1325 g_level_pk_key(i):=null;
1326 g_level_pk(i):=null;
1327 end loop;
1328 if g_debug then
1329 write_to_log_file('Results');
1330 for i in 1..g_number_fact_fks loop
1331 write_to_log_file(g_fact_fks(i)||' '||l_fk_cons(i)||' '||g_parent_dim(i)||' '||g_dim_pk_key(i));
1332 end loop;
1333 end if;
1337 if i=1 then
1334 --get the dim level prefix
1335 l_in_stmt:=null;
1336 for i in 1..g_number_fact_fks loop
1338 l_in_stmt:=l_in_stmt||''''||g_parent_dim(i)||'''';
1339 else
1340 l_in_stmt:=l_in_stmt||','''||g_parent_dim(i)||'''';
1341 end if;
1342 end loop;
1343 l_stmt:='select lvl.level_name,lvl.level_prefix, dim.dim_name from edw_dimensions_md_v dim, '||
1344 'edw_levels_md_v lvl where dim.dim_name in ('||l_in_stmt||') and lvl.dim_id=dim.dim_id';
1345 if g_debug then
1346 write_to_log_file_n('Going to execute '||l_stmt);
1347 end if;
1348 l_number_lvl:=1;
1349 open cv for l_stmt;
1350 loop
1351 fetch cv into l_lvl_name(l_number_lvl),l_lvl_prefix(l_number_lvl),l_lvl_dim(l_number_lvl);
1352 exit when cv%notfound;
1353 l_number_lvl:=l_number_lvl+1;
1354 end loop;
1355 close cv;
1356 l_number_lvl:=l_number_lvl-1;
1357 --find if the fks are pointing to higher levels
1358 for i in 1..g_number_fact_fks loop
1359 l_prefix:=substr(l_fk_cons(i),instr(l_fk_cons(i),'_',-1)+1,length(l_fk_cons(i)));
1360 if l_prefix <> l_fk_cons(i) then
1361 for j in 1..l_number_lvl loop
1362 if l_prefix=l_lvl_prefix(j) and g_parent_dim(i)=l_lvl_dim(j) then
1363 g_higher_level(i):=true;
1364 g_parent_level(i):=l_lvl_name(j);
1365 g_level_prefix(i):=l_prefix;
1366 exit;
1367 end if;
1368 end loop;
1369 end if;
1370 end loop;
1371 if g_debug then
1372 write_to_log_file_n('The keys that point to higher levels');
1373 for i in 1..g_number_fact_fks loop
1374 if g_higher_level(i) then
1375 write_to_log_file(g_fact_fks(i)||' '||l_fk_cons(i)||' '||g_parent_level(i)||' '||g_parent_dim(i));
1376 end if;
1377 end loop;
1378 end if;
1379 --for the higer levels, find the level pk and pk_key
1380 for i in 1..g_number_fact_fks loop
1381 if g_higher_level(i) then
1382 l_stmt:='select pk_item.column_name, substr(pk_item.column_name,1,instr(pk_item.column_name,''_'',1)-1) '||
1383 'from edw_unique_keys_md_v pk,edw_pvt_key_columns_md_v isu, edw_pvt_columns_md_v pk_item '||
1384 'where pk.entity_id=:a and isu.key_id=pk.key_id and pk_item.column_id=isu.column_id';
1385 if g_debug then
1386 write_to_log_file_n('Going to execute '||l_stmt||' using '||g_parent_dim_id(i));
1387 end if;
1388 l_number_dim_fk:=1;
1389 open cv for l_stmt using g_parent_dim_id(i);
1390 loop
1391 fetch cv into l_dim_fk(l_number_dim_fk),l_prefix_fk(l_number_dim_fk);
1392 exit when cv%notfound;
1393 l_number_dim_fk:=l_number_dim_fk+1;
1394 end loop;
1395 close cv;
1396 l_number_dim_fk:=l_number_dim_fk-1;
1397 if g_debug then
1398 write_to_log_file('Results');
1399 for j in 1..l_number_dim_fk loop
1400 write_to_log_file(l_dim_fk(j)||' '||l_prefix_fk(j));
1401 end loop;
1402 end if;
1403 for j in 1..l_number_dim_fk loop
1404 if g_level_prefix(i)=l_prefix_fk(j) then
1405 g_level_pk_key(i):=l_dim_fk(j);
1406 g_level_pk(i):=EDW_OWB_COLLECTION_UTIL.get_user_key(l_dim_fk(j));
1407 if g_level_pk(i) is null then
1408 g_level_pk(i):=g_level_pk_key(i);
1409 g_level_pk_key(i):=g_level_pk(i)||'_KEY';
1410 end if;
1411 exit;
1412 end if;
1413 end loop;
1414 end if;
1415 end loop;
1416 if g_debug then
1417 write_to_log_file_n('The fks, levels and pks');
1418 for i in 1..g_number_fact_fks loop
1419 if g_higher_level(i) then
1420 write_to_log_file(g_fact_fks(i)||' '||g_parent_dim(i)||' '||g_level_pk_key(i));
1421 end if;
1422 end loop;
1423 end if;
1424 return true;
1425 Exception when others then
1426 g_status_message:=sqlerrm;
1427 write_to_log_file_n('Error in get_fact_fks '||sqlerrm||' '||get_time);
1428 return false;
1429 End;
1430
1431 function get_fact_mappings return boolean is
1432 TYPE CurTyp IS REF CURSOR;
1433 cv CurTyp;
1434 l_stmt varchar2(1000);
1435 begin
1436 if g_debug then
1437 write_to_log_file_n('Started get_fact_mappings');
1438 end if;
1439 --not checked
1440 l_stmt:='select map.mapping_id, src.relation_name, src.relation_id '||
1441 'from '||
1442 'edw_pvt_map_properties_md_v map, '||
1443 'edw_relations_md_v src '||
1444 'where map.primary_target=:s '||
1445 'and src.relation_id=map.primary_source';
1446 open cv for l_stmt using g_fact_id;
1447 g_number_mapping_ids:=1;
1448 loop
1449 fetch cv into g_mapping_ids(g_number_mapping_ids),
1450 g_src_objects(g_number_mapping_ids),
1451 g_src_object_ids(g_number_mapping_ids);
1452 exit when cv%notfound;
1453 g_number_mapping_ids:=g_number_mapping_ids+1;
1454 end loop;
1455 g_number_mapping_ids:=g_number_mapping_ids-1;
1456 if g_debug then
1457 write_to_log_file_n('The mapping ids, src objects, total number '||g_number_mapping_ids);
1458 for i in 1..g_number_mapping_ids loop
1459 write_to_log_file(g_mapping_ids(i)||' '||g_src_objects(i)||' '||g_src_object_ids(i));
1460 end loop;
1461 end if;
1462
1463 if g_debug then
1464 write_to_log_file_n('Finished get_fact_mappings');
1465 end if;
1466 return true;
1467 Exception when others then
1468 begin
1469 close cv;
1470 exception when others then
1471 null;
1472 end;
1473 g_status_message:=sqlerrm;
1477
1474 write_to_log_file_n('Error in get_fact_mappings '||sqlerrm||' '||get_time);
1475 return false;
1476 End;
1478 function get_status_message return varchar2 is
1479 begin
1480 return g_status_message;
1481 Exception when others then
1482 write_to_log_file_n('Error in get_status_message');
1483 return null;
1484 End;
1485
1486 procedure init_all is
1487 begin
1488 g_temp_fact_name:=g_fact_name||'_TEMP';
1489 g_fact_iv:=g_fact_name||'_IV';
1490 g_status_message:=' ';
1491 End;
1492
1493 function get_time return varchar2 is
1494 begin
1495 return ' '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS');
1496 Exception when others then
1497 write_to_log_file_n('Error in get_time '||sqlerrm);
1498 End;
1499
1500 procedure write_to_log_file(p_message varchar2) is
1501 begin
1502 EDW_OWB_COLLECTION_UTIL.write_to_log_file(p_message);
1503 Exception when others then
1504 null;
1505 End;
1506
1507 procedure write_to_log_file_n(p_message varchar2) is
1508 begin
1509 write_to_log_file(' ');
1510 write_to_log_file(p_message);
1511 Exception when others then
1512 null;
1513 End;
1514
1515 function wait_on_jobs(
1516 p_job_id EDW_OWB_COLLECTION_UTIL.numberTableType,
1517 p_job_status in out nocopy EDW_OWB_COLLECTION_UTIL.varcharTableType,
1518 p_number_jobs number,
1519 p_sleep_time number,
1520 p_mode varchar2
1521 ) return boolean is
1522 l_running boolean;
1523 l_changed boolean;
1524 l_status varchar2(10);
1525 Begin
1526 if g_debug then
1527 write_to_log_file_n('In wait_on_jobs p_sleep_time='||p_sleep_time||get_time);
1528 write_to_log_file('p_number_jobs='||p_number_jobs);
1529 for i in 1..p_number_jobs loop
1530 write_to_log_file(p_job_id(i)||' '||p_job_status(i));
1531 end loop;
1532 end if;
1533 l_changed:=false;
1534 l_running:=false;
1535 loop
1536 for i in 1..p_number_jobs loop
1537 if p_job_status(i)='R' then
1538 --the two api must be able to handle -ve p_job_id
1539 if p_mode='JOB' then
1540 l_status:=EDW_OWB_COLLECTION_UTIL.check_job_status(p_job_id(i));
1541 elsif p_mode='CONC' then
1542 l_status:=EDW_OWB_COLLECTION_UTIL.check_conc_process_status(p_job_id(i));
1543 end if;
1544 if l_status is null then
1545 return false;
1546 elsif l_status='N' then
1547 l_changed:=true;
1548 p_job_status(i):='Y';--complete
1549 if g_debug then
1550 write_to_log_file_n('Job '||p_job_id(i)||' Completed '||get_time);
1551 end if;
1552 else
1553 l_running:=true;--still running
1554 end if;
1555 end if;
1556 end loop;
1557 if l_changed then
1558 exit;
1559 elsif l_running then
1560 DBMS_LOCK.SLEEP(p_sleep_time);
1561 else
1562 exit;
1563 end if;
1564 end loop;
1565 if g_debug then
1566 write_to_log_file(get_time);
1567 end if;
1568 return true;
1569 Exception when others then
1570 g_status_message:=sqlerrm;
1571 write_to_log_file_n('Error in wait_on_jobs '||sqlerrm||' '||get_time);
1572 return false;
1573 End;
1574
1575 function log_collection_detail(
1576 p_object_name varchar2,
1577 p_object_id number,
1578 p_object_type varchar2,
1579 p_conc_program_id number,
1580 p_collection_start_date date,
1581 p_collection_end_date date,
1582 p_ins_rows_ready number,
1583 p_ins_rows_processed number,
1584 p_ins_rows_collected number,
1585 p_ins_rows_insert number,
1586 p_ins_rows_update number,
1587 p_ins_rows_delete number,
1588 p_message varchar2,
1589 p_status varchar2,
1590 p_load_pk number
1591 ) return boolean is
1592 Begin
1593 if EDW_OWB_COLLECTION_UTIL.write_to_collection_log(
1594 p_object_name,
1595 p_object_id,
1596 p_object_type,
1597 p_conc_program_id,
1598 p_collection_start_date,
1599 p_collection_end_date,
1600 p_ins_rows_ready,
1601 p_ins_rows_processed,
1602 p_ins_rows_collected,
1603 p_ins_rows_insert,
1604 p_ins_rows_update,
1605 p_ins_rows_delete,
1606 p_message,
1607 p_status,
1608 p_load_pk)= false then
1609 return false;
1610 end if;
1611 return true;
1612 Exception when others then
1613 g_status_message:=sqlerrm;
1614 write_to_log_file_n('Error in log_collection_detail '||sqlerrm||' '||get_time);
1615 return false;
1616 End;
1617
1618 function get_temp_log_data(
1619 p_object_name varchar2,
1620 p_object_type varchar2,
1621 p_load_pk number,
1622 p_rows_processed out nocopy number
1623 ) return boolean is
1624 l_rows_ready number;
1625 l_ins_rows_collected number;
1626 l_ins_rows_dangling number;
1627 l_ins_rows_duplicate number;
1628 l_ins_rows_error number;
1629 l_ins_rows_insert number;
1630 l_ins_rows_update number;
1631 l_ins_rows_delete number;
1632 l_ins_instance_name varchar2(80);
1633 l_ins_request_id_table varchar2(80);
1634 Begin
1635 if EDW_OWB_COLLECTION_UTIL.get_temp_log_data(
1636 p_object_name,
1637 p_object_type,
1638 p_load_pk,
1639 l_rows_ready,
1640 p_rows_processed,
1641 l_ins_rows_collected,
1642 l_ins_rows_dangling,
1643 l_ins_rows_duplicate,
1644 l_ins_rows_error,
1645 l_ins_rows_insert,
1646 l_ins_rows_update,
1650 null;
1647 l_ins_rows_delete,
1648 l_ins_instance_name,
1649 l_ins_request_id_table)=false then
1651 end if;
1652 return true;
1653 EXCEPTION when others then
1654 write_to_log_file_n('Error in get_temp_log_data '||sqlerrm);
1655 return false;
1656 End;
1657
1658 function get_child_job_status(
1659 p_job_status_table varchar2,
1660 p_status out nocopy varchar2,
1661 p_message out nocopy varchar2
1662 ) return boolean is
1663 l_id EDW_OWB_COLLECTION_UTIL.numberTableType;
1664 l_job_id EDW_OWB_COLLECTION_UTIL.numberTableType;
1665 l_status EDW_OWB_COLLECTION_UTIL.varcharTableType;
1666 l_message EDW_OWB_COLLECTION_UTIL.varcharTableType;
1667 l_number_jobs number;
1668 Begin
1669 if EDW_OWB_COLLECTION_UTIL.get_child_job_status(
1670 p_job_status_table,
1671 null,
1672 l_id,
1673 l_job_id,
1674 l_status,
1675 l_message,
1676 l_number_jobs)=false then
1677 null;
1678 end if;
1679 for i in 1..l_number_jobs loop
1680 if l_status(i)='ERROR' then
1681 p_status:='ERROR';
1682 p_message:=l_message(i);
1683 return true;
1684 end if;
1685 end loop;
1686 p_status:='SUCCESS';
1687 p_message:=null;
1688 return true;
1689 EXCEPTION when others then
1690 write_to_log_file_n('Error in get_child_job_status '||sqlerrm);
1691 return false;
1692 End;
1693
1694 function delete_object_log_tables(
1695 p_src_fact varchar2,
1696 p_table_owner varchar2,
1697 p_bis_owner varchar2,
1698 p_fact_dlog varchar2,
1699 p_ilog EDW_OWB_COLLECTION_UTIL.varcharTableType,
1700 p_dlog EDW_OWB_COLLECTION_UTIL.varcharTableType,
1701 p_number_derv_fact number
1702 )return boolean is
1703 l_snp_log varchar2(400);
1704 l_mv_fast_refresh number;
1705 Begin
1706 if g_debug then
1707 write_to_log_file_n('In delete_object_log_tables');
1708 end if;
1709 /*3529591
1710 before we truncate the mv log of the base fact, we need to check to see
1711 if this is a src for a fast refresh mv.
1712 */
1713 l_mv_fast_refresh:=edw_owb_collection_util.is_source_for_fast_refresh_mv(p_src_fact,p_table_owner);
1714 if l_mv_fast_refresh is null or l_mv_fast_refresh<>1 then
1715 l_snp_log:=EDW_OWB_COLLECTION_UTIL.get_table_snapshot_log(p_src_fact);
1716 if g_debug then
1717 write_to_log_file_n('l_snp_log is '||l_snp_log);
1718 end if;
1719 if l_snp_log is not null then
1720 if EDW_OWB_COLLECTION_UTIL.truncate_table(l_snp_log,p_table_owner) = false then
1721 return false;
1722 end if;
1723 end if;
1724 if p_fact_dlog is not null then
1725 if instr(p_fact_dlog,p_bis_owner||'.') <> 0 then
1726 if EDW_OWB_COLLECTION_UTIL.drop_table(p_fact_dlog)=false then
1727 return false;
1728 end if;
1729 else
1730 if EDW_OWB_COLLECTION_UTIL.truncate_table(p_fact_dlog,p_table_owner)=false then
1731 return false;
1732 end if;
1733 end if;
1734 end if;
1735 --drop the ilog and dlog tables
1736 for i in 1..p_number_derv_fact loop
1737 if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog(i))=false then
1738 null;
1739 end if;
1740 if EDW_OWB_COLLECTION_UTIL.drop_table(p_ilog(i)||'A')=false then
1741 null;
1742 end if;
1743 if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_ilog(i)||'_IL',null,p_bis_owner)=false then
1744 return false;
1745 end if;
1746 if EDW_OWB_COLLECTION_UTIL.drop_table(p_dlog(i))=false then
1747 null;
1748 end if;
1749 if EDW_OWB_COLLECTION_UTIL.drop_table(p_dlog(i)||'A')=false then
1750 null;
1751 end if;
1752 if EDW_OWB_COLLECTION_UTIL.drop_ilog_tables(p_dlog(i)||'_DL',null,p_bis_owner)=false then
1753 return false;
1754 end if;
1755 end loop;
1756 end if;--if l_mv_fast_refresh is null or l_mv_fast_refresh<>1 then
1757 return true;
1758 Exception when others then
1759 g_status_message:=sqlerrm;
1760 write_to_log_file_n('Error in delete_object_log_tables '||g_status_message);
1761 return false;
1762 End;
1763
1764 function drop_inp_status_table(
1765 p_input_table varchar2,
1766 p_job_status_table varchar2
1767 )return boolean is
1768 Begin
1769 if EDW_OWB_COLLECTION_UTIL.drop_table(p_input_table)=false then
1770 null;
1771 end if;
1772 if EDW_OWB_COLLECTION_UTIL.drop_table(p_job_status_table)=false then
1773 null;
1774 end if;
1775 if EDW_OWB_COLLECTION_UTIL.drop_table(p_input_table||'_FK')=false then
1776 null;
1777 end if;
1778 if EDW_OWB_COLLECTION_UTIL.drop_table(p_input_table||'_SK')=false then
1779 null;
1780 end if;
1781 if EDW_OWB_COLLECTION_UTIL.drop_table(p_input_table||'_BU')=false then
1782 null;
1783 end if;
1784 return true;
1785 Exception when others then
1786 g_status_message:=sqlerrm;
1787 write_to_log_file_n('Error in drop_inp_status_table '||g_status_message);
1788 return false;
1789 End;
1790
1791 function create_conc_program(
1792 p_temp_conc_name varchar2,
1793 p_temp_conc_short_name varchar2,
1794 p_temp_exe_name varchar2,
1795 p_bis_short_name varchar2
1796 ) return boolean is
1797 l_bis_long_name varchar2(240);
1798 l_parameter EDW_OWB_COLLECTION_UTIL.varcharTableType;
1799 l_parameter_value_set EDW_OWB_COLLECTION_UTIL.varcharTableType;
1800 l_number_parameters number;
1801 Begin
1802 if g_debug then
1803 write_to_log_file_n('In create_conc_program '||get_time);
1804 end if;
1805 l_parameter(1):='p_fact_name';
1806 l_parameter_value_set(1):='FND_CHAR240';
1807 l_parameter(2):='p_fact_id';
1808 l_parameter_value_set(2):='FND_NUMBER';
1809 l_parameter(3):='p_log_file';
1810 l_parameter_value_set(3):='FND_CHAR240';
1811 l_parameter(4):='p_input_table';
1812 l_parameter_value_set(4):='FND_CHAR240';
1813 l_parameter(5):='p_ilog';
1814 l_parameter_value_set(5):='FND_CHAR240';
1815 l_parameter(6):='p_dlog';
1816 l_parameter_value_set(6):='FND_CHAR240';
1817 l_parameter(7):='p_pre_hook';
1818 l_parameter_value_set(7):='FND_CHAR240';
1819 l_parameter(8):='p_post_hook';
1820 l_parameter_value_set(8):='FND_CHAR240';
1821 l_parameter(9):='p_thread_type';
1822 l_parameter_value_set(9):='FND_CHAR240';
1823 l_number_parameters:=9;
1824 if EDW_OWB_COLLECTION_UTIL.create_conc_program(
1825 p_temp_conc_name,
1826 p_temp_conc_short_name,
1827 p_temp_exe_name,
1828 'EDW_DERIVED_FACT_FACT_COLLECT.COLLECT_FACT_MULTI_THREAD',
1829 p_bis_short_name,
1830 l_parameter,
1831 l_parameter_value_set,
1832 l_number_parameters
1833 )=false then
1834 return false;
1835 end if;
1836 return true;
1837 Exception when others then
1838 g_status_message:=sqlerrm;
1839 write_to_log_file_n('Error in create_conc_program '||g_status_message);
1840 write_to_log_file_n('FND_PROGRAM.MESSAGE='||FND_PROGRAM.MESSAGE);
1841 return false;
1842 End;
1843
1844 END;
[Home] [Help]