eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE: APPS.EDW_DERIVED_FACT_FACT_COLLECT

Source


1 package EDW_DERIVED_FACT_FACT_COLLECT AUTHID CURRENT_USER AS
2 /*$Header: EDWFFCLS.pls 115.25 2003/07/29 00:22:56 vsurendr ship $*/
3 --Type varcharTableType is Table of varchar2(400) index by binary_integer;
4 --Type numberTableType is Table of number index by binary_integer;
5 
6 g_thread_type varchar2(40);
7 g_pre_hook varchar2(10);
8 g_post_hook varchar2(10);
9 g_insert_lock_table varchar2(80);
10 g_log_file varchar2(200);
11 g_dbms_job_id number;
12 g_over boolean;
13 g_stmt varchar2(30000);
14 g_ilog_name varchar2(80);
15 g_dlog_name varchar2(80);
16 g_fact_name varchar2(400);
17 g_temp_fact_name varchar2(400);
18 g_temp_fact_name_temp varchar2(400);
19 g_summarize_temp2 varchar2(400);
20 g_summarize_temp3 varchar2(400);
21 g_fact_iv varchar2(400);
22 g_fact_id number;
23 g_mapping_id number;
24 g_fact_type varchar2(400);
25 g_src_object varchar2(400);
26 g_src_object_count number;
27 g_src_object_ilog varchar2(400);
28 g_src_object_dlog varchar2(400);
29 g_src_object_dlog_count number;
30 g_src_object_id number;
31 g_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType;
32 g_fact_fks_mapped EDW_OWB_COLLECTION_UTIL.booleanTableType;
33 g_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType;
34 g_higher_level_flag EDW_OWB_COLLECTION_UTIL.booleanTableType;
35 g_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType;
36 g_parent_dim_id EDW_OWB_COLLECTION_UTIL.numberTableType;
37 g_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType;
38 g_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType;
39 g_level_pk  EDW_OWB_COLLECTION_UTIL.varcharTableType;
40 g_level_pk_key  EDW_OWB_COLLECTION_UTIL.varcharTableType;
41 g_dim_pk_key  EDW_OWB_COLLECTION_UTIL.varcharTableType;
42 g_number_fact_fks number;
43 g_src_fks EDW_OWB_COLLECTION_UTIL.varcharTableType;
44 g_number_src_fks number;
45 g_filter_stmt varchar2(10000);
46 g_fact_dlog varchar2(400);
47 
48 g_conc_id number;
49 g_conc_program_name varchar2(400);
50 g_debug boolean;
51 g_exec_flag boolean;
52 g_groupby_stmt varchar2(10000);
53 g_update_type varchar2(400);--what scheme should update follow...mass, row by row, delete insert...
54 
55 g_input_params EDW_OWB_COLLECTION_UTIL.varcharTableType;
56 g_output_params EDW_OWB_COLLECTION_UTIL.varcharTableType;
57 g_input_params_is_fk EDW_OWB_COLLECTION_UTIL.booleanTableType;
58 g_group_by_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
59 g_output_group_by_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;--in the derived summary fact
60 g_number_group_by_cols number;
61 g_number_input_params number;
62 g_df_extra_fks  EDW_OWB_COLLECTION_UTIL.varcharTableType;
63 g_fk_flag  EDW_OWB_COLLECTION_UTIL.booleanTableType;--is the output col a fk?
64 g_fk_off_flag  EDW_OWB_COLLECTION_UTIL.booleanTableType;--turned off
65 g_groupby_col_flag  EDW_OWB_COLLECTION_UTIL.booleanTableType;--is the output col a group by col?
66 g_number_df_extra_fks number;
67 
68 g_full_refresh boolean; --full refresh or not
69 g_naedw_pk number; --for now, put this as 0
70 
71 /*************************************************************
72                 some temp holders
73 **************************************************************/
74 g_hold_relation EDW_OWB_COLLECTION_UTIL.varcharTableType;
75 g_hold_item EDW_OWB_COLLECTION_UTIL.varcharTableType;
76 g_hold_number number;
77 /*************************************************************/
78 
79 g_ins_rows_processed number;
80 g_status_message varchar2(20000);
81 g_status boolean;
82 
83 g_collection_size number;
84 g_parallel  number;
85 g_insert_rowid_table varchar2(400);
86 g_update_rowid_table varchar2(400);
87 g_delete_rowid_table varchar2(400);
88 g_insert_prot_log varchar2(400);
89 g_update_prot_log varchar2(400);
90 g_delete_prot_log varchar2(400);
91 
92 g_ilog varchar2(400);
93 g_dlog varchar2(400);
94 g_bis_owner  varchar2(400);
95 g_table_owner varchar2(400);
96 g_df_table_owner varchar2(400);
97 g_data_temp_stmt varchar2(30000);--to move data into the temp table
98 g_delete_data_temp_stmt varchar2(32000);
99 g_temp_update_stmt varchar2(32000);
100 g_insert_stmt  varchar2(32000);
101 g_update_stmt  varchar2(32000);
102 g_update_stmt_row  varchar2(32000);
103 g_delete_stmt  varchar2(32000);
104 g_delete_stmt_row varchar2(32000);
105 
106 g_insert_rowid_stmt  varchar2(32000);
107 g_update_rowid_stmt  varchar2(32000);
108 g_delete_rowid_stmt  varchar2(32000);
109 g_forall_size number;
110 
111 g_err_rec_flag boolean;
112 g_err_rec_flag_d  boolean;--for delete
113 
114 /**************Record the number of rows processed and errors*****/
115 g_ins_rows_ready EDW_OWB_COLLECTION_UTIL.numberTableType;
116 g_ins_rows_processed_tab EDW_OWB_COLLECTION_UTIL.numberTableType;
117 g_ins_rows_collected EDW_OWB_COLLECTION_UTIL.numberTableType;
118 g_ins_rows_dangling EDW_OWB_COLLECTION_UTIL.numberTableType;
119 g_ins_rows_duplicate EDW_OWB_COLLECTION_UTIL.numberTableType;
120 g_ins_rows_error EDW_OWB_COLLECTION_UTIL.numberTableType;
121 g_ins_instance_name EDW_OWB_COLLECTION_UTIL.varcharTableType;
122 g_ins_request_id_table  EDW_OWB_COLLECTION_UTIL.numberTableType;
123 g_number_ins_req_coll number;
124 g_total_insert number;
125 g_total_update number;
126 g_total_delete number;
127 /*******************************************************/
128 g_load_fk number;
129 g_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType;
130 g_number_skip_cols number;
131 g_skip_item EDW_OWB_COLLECTION_UTIL.booleanTableType;
132 g_fk_value_load EDW_OWB_COLLECTION_UTIL.booleanTableType;--if a value needs to be directly loaded like edwna
133 g_fk_load_value EDW_OWB_COLLECTION_UTIL.varcharTableType;--the value to load
134 --------------------sec sources-----------------------
135 g_sec_sources EDW_OWB_COLLECTION_UTIL.varcharTableType;
136 g_sec_sources_alias EDW_OWB_COLLECTION_UTIL.varcharTableType;
137 g_number_sec_sources number;
138 g_sec_sources_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
139 g_sec_sources_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
140 g_number_sec_key number;
141 --------------------sec sources-----------------------
142 g_mode varchar2(400);
143 g_skip_ilog boolean;--for performance
144 g_fresh_restart boolean;
145 g_op_table_space varchar2(400);
146 g_rollback  varchar2(400);
147 ----------propogate inc changes in dim to derv facts--------------------------------------
148 g_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType;--before update tables.prop dim change to derv fact.
149 g_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType;--which are the dims with the before update tables
150 g_number_bu_tables number;
151 g_bu_src_fact varchar2(400);
152 g_load_mode varchar2(400);
153 g_skip_ilog_update boolean;
154 g_skip_dlog_update boolean;
155 g_type_ilog_generation varchar2(400);
156 g_type_dlog_generation varchar2(400);
157 g_dlog_prev varchar2(400);
158 g_ilog_prev varchar2(400);
159 g_ilog_small varchar2(400);
160 g_dlog_small varchar2(400);
161 g_fact_next_extent number;
162 g_src_pk varchar2(400);
163 g_src_uk varchar2(400);
164 g_src_snplog_has_pk boolean;
165 g_src_join_nl_percentage number;
166 g_src_join_nl boolean;
167 /*
168 for g_bu_src_fact the mapping details are got from the metadata and in the place of the src fact,g_bu_src_fact is
169 substituted.
170 so g_bu_src_fact must have the same col names as the src fact
171 */
172 --------------------------------------------------------------------------------
173 g_max_threads number;
174 g_min_job_load_size number;
175 g_sleep_time number;
176 g_job_status_table varchar2(80);
177 g_hash_area_size number;
178 g_sort_area_size number;
179 g_trace boolean;
180 g_read_cfig_options boolean;
181 g_jobid_stmt varchar2(1000);
182 g_job_id number;
183 
184 function COLLECT_FACT(p_fact_name varchar2,
185                     p_fact_id number,
186                     p_mapping_id number,
187                     p_src_object varchar2,
188                     p_src_object_id number,
189                     p_fact_fks EDW_OWB_COLLECTION_UTIL.varcharTableType,
190                     p_higher_level EDW_OWB_COLLECTION_UTIL.booleanTableType,
191                     p_parent_dim EDW_OWB_COLLECTION_UTIL.varcharTableType,
192                     p_parent_level EDW_OWB_COLLECTION_UTIL.varcharTableType,
193                     p_level_prefix EDW_OWB_COLLECTION_UTIL.varcharTableType,
194                     p_level_pk EDW_OWB_COLLECTION_UTIL.varcharTableType,
195                     p_level_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
196                     p_dim_pk_key EDW_OWB_COLLECTION_UTIL.varcharTableType,
197                     p_number_fact_fks number,
198                     p_conc_id number,
199                     p_conc_program_name varchar2,
200                     p_debug boolean,
201                     p_collection_size number,
202                     p_parallel number,
203                     p_bis_owner varchar2,
204                     p_table_owner  varchar2,
205                     p_ins_rows_processed out NOCOPY number,
206                     p_full_refresh boolean,
207                     p_ilog varchar2,
208                     p_dlog varchar2,
209                     p_forall_size number,
210                     p_update_type varchar2,
211                     p_fact_dlog varchar2,
212                     p_skip_cols EDW_OWB_COLLECTION_UTIL.varcharTableType,
213                     p_number_skip_cols number,
214                     p_load_fk number,
215                     p_fresh_restart boolean,
216                     p_op_table_space varchar2,
217                     p_bu_tables EDW_OWB_COLLECTION_UTIL.varcharTableType,
218                     --before update tables.prop dim change to derv
219                     p_bu_dimensions EDW_OWB_COLLECTION_UTIL.varcharTableType,
220                     p_number_bu_tables number,
221                     p_bu_src_fact varchar2,
222                     --what table to look at as the src fact. if null, scan full the src fact
223                     p_load_mode varchar2,
224                     p_rollback varchar2,
225                     p_src_join_nl_percentage number,
226                     p_pre_hook varchar2,
227                     p_post_hook varchar2
228                     ) return boolean ;
229 function get_time return varchar2 ;
230 procedure write_to_log_file(p_message varchar2) ;
231 procedure write_to_log_file_n(p_message varchar2) ;
232 function get_mapping_details return boolean ;
233 function get_src_fks return boolean ;
234 function is_src_fk(p_fk varchar2) return boolean ;
235 function is_tgt_fk(p_fk varchar2) return boolean ;
236 function make_data_into_temp(p_use_ordered_hint boolean) return boolean ;
237 function make_delete_data_into_temp return boolean ;
238 function execute_data_into_temp return number ;
239 function execute_delete_data_into_temp return number ;
240 function insert_into_fact return boolean ;
241 function get_ilog_dlog return boolean ;
242 function get_status_message return varchar2 ;
243 function get_df_extra_fks return boolean ;
244 procedure init_all(p_job_id number) ;
245 function move_data_into_local_ilog(p_multi_thread boolean) return boolean ;
246 function move_data_into_local_dlog(p_multi_thread boolean) return boolean ;
247 function COLLECT_FACT(p_mode varchar2) return boolean ;
248 function move_data_into_derived_fact(p_count number) return boolean ;
249 function make_insert_into_fact return boolean ;
250 function set_gilog_status return number ;
251 function set_gdlog_status return number ;
252 procedure clean_up ;
253 function update_dlog_status_2 return boolean;
254 function update_ilog_status_2 return boolean;
255 function execute_data_into_rowid_table return boolean ;
256 function insert_rowid_table_stmt return boolean ;
257 function update_rowid_table_stmt return boolean ;
258 function create_index_rowid_table return boolean ;
259 function make_update_into_fact return boolean ;
260 function make_delete_into_fact  return boolean ;
261 function delete_rowid_table_stmt return boolean ;
262 function create_index_drowid_table return boolean ;
263 function move_ddata_into_derived_fact(p_count number) return boolean ;
264 function execute_ddata_into_rowid_table return boolean;
265 function update_into_fact return boolean ;
266 function delete_into_fact return boolean ;
267 function create_gilog_T(p_table varchar2,p_ilog_temp varchar2) return boolean ;
268 function make_is_fk_flag return boolean ;
269 function is_tgt_fk_mapped return boolean ;
270 function summarize_fact_data return boolean ;
271 function create_summarize_temp2 return boolean ;
272 function create_summarize_temp3 return boolean ;
273 function create_summarize_temp return boolean ;
274 function update_log_status_0(p_log varchar2) return boolean;
275 function make_is_groupby_col  return boolean ;
276 function is_groupby_col (p_col varchar2) return boolean;
277 function drop_prot_tables return boolean ;
278 function drop_d_prot_tables return boolean;
279 function make_insert_prot_log return boolean ;
280 function make_update_prot_log return boolean ;
281 function make_delete_prot_log return boolean ;
282 function recover_from_previous_error return boolean;
283 function is_input_groupby_col(p_col varchar2) return boolean;
284 procedure insert_into_temp_log(p_flag varchar2) ;
285 function drop_ilog_index return boolean;
286 function drop_dlog_index return boolean;
287 procedure insert_into_load_progress(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
288 p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2);
289 procedure insert_into_load_progress_d(p_load_fk number,p_object_name varchar2,p_load_progress varchar2,
290 p_start_date date,p_end_date date,p_category varchar2, p_operation varchar2,p_seq_id varchar2,p_flag varchar2);
291 function make_g_higher_level_flag return boolean;
292 procedure reset_profiles;
293 function get_base_fact_count return number ;
294 function create_temp_gilog return boolean;
295 function create_temp_gdlog return boolean ;
296 function check_src_fact_snplog return number ;
297 function load_new_update_data return boolean ;
298 function set_g_src_join_nl(p_load_size number, p_total_records number) return boolean ;
299 function recover_from_prot return boolean ;
300 function read_metadata return boolean;
301 function initialize(p_multi_thread boolean) return boolean ;
302 function read_options_table(p_table varchar2) return boolean ;
303 function COLLECT_FACT_MULTI_THREAD(
304 p_input_table varchar2
305 ) return boolean ;
306 procedure COLLECT_FACT_MULTI_THREAD(
307 p_fact_name varchar2,
308 p_fact_id number,
309 p_log_file varchar2,
310 p_input_table varchar2,
311 p_ilog varchar2,
312 p_dlog varchar2,
313 p_pre_hook varchar2,
314 p_post_hook varchar2,
315 p_thread_type varchar2
316 );
317 procedure COLLECT_FACT_MULTI_THREAD(
318 errbuf out nocopy varchar2,
319 retcode out nocopy varchar2,
320 p_fact_name varchar2,
321 p_fact_id number,
322 p_log_file varchar2,
323 p_input_table varchar2,
324 p_ilog varchar2,
325 p_dlog varchar2,
326 p_pre_hook varchar2,
327 p_post_hook varchar2,
328 p_thread_type varchar2
329 );
330 procedure COLLECT_FACT(
331 errbuf out nocopy varchar2,
332 retcode out nocopy varchar2,
333 p_mode varchar2,
334 p_fact_name varchar2,
335 p_input_table varchar2,
336 p_job_id number,
337 p_ilog_low_end number,
338 p_ilog_high_end number,
339 p_ilog varchar2,
340 p_dlog varchar2,
341 p_log_file varchar2,
342 p_thread_type varchar2
343 );
344 procedure COLLECT_FACT(
345 p_mode varchar2,
346 p_fact_name varchar2,
347 p_input_table varchar2,
348 p_job_id number,
349 p_ilog_low_end number,
350 p_ilog_high_end number,
351 p_ilog varchar2,
352 p_dlog varchar2,
353 p_log_file varchar2,
354 p_thread_type varchar2
355 );
356 function COLLECT_FACT(
357 p_mode varchar2,
358 p_input_table varchar2,
359 p_ilog_low_end number,
360 p_ilog_high_end number
361 ) return boolean ;
362 function initial_set_up(
363 p_input_table varchar2,
364 p_max_threads number,
365 p_ilog_table out nocopy varchar2,
366 p_dlog_table out nocopy varchar2
367 ) return boolean ;
368 function set_session_parameters return boolean ;
369 function make_ok_from_main_ok(
370 p_main_ok_table_name varchar2,
371 p_ilog_table varchar2,
372 p_low_end number,
373 p_high_end number,
374 p_mode varchar2
378 function create_insert_lock_table return boolean;
375 ) return boolean ;
376 function put_rownum_in_log_table return boolean ;
377 function drop_ilog_dlog_tables(p_ilog varchar2,p_dlog varchar2) return boolean ;
379 function drop_insert_lock_table return boolean;
380 function pre_fact_load_hook(p_derv_fact varchar2,p_src_fact varchar2) return boolean;
381 function post_fact_load_hook(p_derv_fact varchar2,p_src_fact varchar2) return boolean;
382 function create_conc_program(
383 p_temp_conc_name varchar2,
384 p_temp_conc_short_name varchar2,
385 p_temp_exe_name varchar2,
386 p_bis_short_name varchar2
387 ) return boolean;
388 END;