[Home] [Help]
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;
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');
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';
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');
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
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;
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
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;
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');
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:
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');
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);
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;
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
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:
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';
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,
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;
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;
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: /*
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:
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;
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
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;
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');
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
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;
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
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;
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(
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');
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
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');
600: if EDW_COLLECTION_HOOK.post_dimension_coll(g_object_name) = true then
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');
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');
603: return_with_success('LOG',null,g_load_pk);
604: if g_status=false then
605: return;
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';
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
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
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;
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,
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');
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;
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;
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:
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;
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
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;
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);
740: end if;
741: Exception when others then
742: g_status:=false;
743: g_status_message:=sqlerrm;
740: end if;
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
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);
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;
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:
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;
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:
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
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'
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
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;
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
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);
870: EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_status_message,FND_LOG.LEVEL_STATEMENT);
871: g_status:=false;
872: return;
873: end if;
866: l_numer_cols)=false then
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);
870: EDW_OWB_COLLECTION_UTIL.write_to_log_file(g_status_message,FND_LOG.LEVEL_STATEMENT);
871: g_status:=false;
872: return;
873: end if;
874: -------------skip columns------------------------
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);
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
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 '||
1007: ' collect level '||g_primary_target(l_latest)||' WITH ERROR::'||sqlerrm||' Time '||get_time,FND_LOG.LEVEL_ERROR);
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 '||
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;
1010: return;
1011: End;--PROCEDURE Collect_Each_Level IS
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
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:
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;
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');
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;
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;
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;
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');
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
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;
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
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;
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');
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');
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);
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';
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;
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' ;
1280: Exception when others then
1281: l_net_change_name:=null;
1282: end;
1283: if l_net_change_name is null then
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;
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: */
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;
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';
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
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;
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');
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
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*****************************************/
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,
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';
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;
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;
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;
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
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
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';
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
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;
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
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;
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
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);
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;
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;
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;
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
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);
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,
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;
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;
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;
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:
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;
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);
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;
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:
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;
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);
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)
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;
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,
1798: return;
1799: end if;
1800: Exception when others then
1801: g_status_message:=sqlerrm;
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
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';
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
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';
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
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;
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;
1940: return true;
1941: Exception when others then
1942: g_status_message:=sqlerrm;
1943: g_status:=false;
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;
1947:
1948: function get_snapshot_log return boolean is
1947:
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
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;
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
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);
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
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,
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;
2081: EDW_OWB_COLLECTION_UTIL.write_to_log_file_n(g_status_message,FND_LOG.LEVEL_ERROR);
2082: End;
2083:
2084: --if debug is turned off
2085: procedure insert_into_load_progress_nd(p_load_fk number,p_object_name varchar2,p_object_id number,p_load_progress varchar2,
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
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;
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
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;
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
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;
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
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
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;
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
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;
2203: l_found:=false;
2204: for j in 1..l_number_derv_maps loop
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;
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
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;
2299: end if;
2300: --what about progress logging?
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);
2304: EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_load_mode='||l_load_mode||',l_bu_src_fact='||l_bu_src_fact,
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),
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);
2304: EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_load_mode='||l_load_mode||',l_bu_src_fact='||l_bu_src_fact,
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;
2304: EDW_OWB_COLLECTION_UTIL.write_to_log_file('l_load_mode='||l_load_mode||',l_bu_src_fact='||l_bu_src_fact,
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,
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;
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:
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);
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
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));
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
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)),
2548: end if;--else for if EDW_OWB_COLLECTION_UTIL.check_table(p_derv_bu_map_src_table) then
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:
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;
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:
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
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;
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;
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
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:
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;
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;
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');
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);
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;
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;
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
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);
2672: l_option_value:=null;
2673: if edw_option.get_warehouse_option(null,g_object_id,'PARALLELISM',l_option_value)=false then
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);
2672: l_option_value:=null;
2673: if edw_option.get_warehouse_option(null,g_object_id,'PARALLELISM',l_option_value)=false then
2674: null;
2675: end if;
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;
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';
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;
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
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;
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;
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;
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
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;
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;
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;
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;
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;
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,
2805: if g_smart_update then
2806: if edw_option.get_option_columns(null,g_object_id,'SMARTUPDATE',g_smart_update_cols,
2807: g_number_smart_update_cols)=false then
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);
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;
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;
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;
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
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:
2935: check_ts_mode varchar(1);
2936: physical_tspace_name varchar2(100);
2937:
2938: Begin
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);
2942: g_trace:=true;
2943: else
2937:
2938: Begin
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);
2942: g_trace:=true;
2943: else
2944: EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Trace turned OFF',FND_LOG.LEVEL_STATEMENT);
2945: g_trace:=false;
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);
2942: g_trace:=true;
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
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;
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;
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);
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;
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);
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;
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
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);
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;
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;
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
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;
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);
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;
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
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;
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
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
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;
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;
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;
3052: EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);
3053: else
3048: end if;
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;
3052: EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);
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;
3051: g_smart_update:=true;
3052: EDW_OWB_COLLECTION_UTIL.write_to_log_file_n('Smart Update TRUE',FND_LOG.LEVEL_STATEMENT);
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,
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
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;
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
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);
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;
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;
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;
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
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);
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:
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;
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
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: