[Home] [Help]
PACKAGE: APPS.EDW_CHECK_DATA_INTEGRITY
Source
1 PACKAGE edw_check_data_integrity AS
2 /*$Header: EDWCHDTS.pls 120.0 2005/06/01 17:28:36 appldev noship $*/
3 version CONSTANT VARCHAR (80)
4 := '$Header: EDWCHDTS.pls 120.0 2005/06/01 17:28:36 appldev noship $';
5
6 TYPE varchartabletypel IS TABLE OF VARCHAR2 (4000)
7 INDEX BY BINARY_INTEGER;
8
9 g_read_object_settings_failure EXCEPTION;
10 g_stg_tables_not_found EXCEPTION;
11 g_file UTL_FILE.file_type;
12 g_lstg_tables edw_owb_collection_util.varchartabletype;
13 g_lstg_table_long_name edw_owb_collection_util.varchartabletype;
14 g_lstg_tables_id edw_owb_collection_util.numbertabletype;
15 g_number_lstg_tables NUMBER;
16 g_lstg_instance_col edw_owb_collection_util.varchartabletype;
17 --the instance column
18 g_lstg_pk edw_owb_collection_util.varchartabletype;
19 g_lstg_pk_id edw_owb_collection_util.numbertabletype;
20 g_lstg_pk_long edw_owb_collection_util.varchartabletype;
21 g_lstg_total_records edw_owb_collection_util.numbertabletype;
22 g_detailed_check BOOLEAN;
23 g_parallel NUMBER;
24 g_fk_table VARCHAR2 (400);
25
26 --all fk variables..we store the staging table name again
27 --in g_lstg_fk_table to make queries...
28 --g_parent_ltc_fk_table is the parent ltc table
29 --g_hier is which hierarchy
30
31 g_main_lstg_fk_table edw_owb_collection_util.varchartabletype;
32 --if a lstg table has 3 keys, this will have 3 keys
33 g_main_lstg_fk_table_lstg edw_owb_collection_util.varchartabletype;
34 --which is the lstg table
35 g_number_main_lstg_fk_table NUMBER;
36 g_lstg_fk_table edw_owb_collection_util.varchartabletype;
37 --only points to g_main_lstg_fk_table
38 g_lstg_fk_table_id edw_owb_collection_util.numbertabletype;
39 g_parent_lstg_fk_table edw_owb_collection_util.varchartabletype;
40 g_parent_lstg_fk_table_id edw_owb_collection_util.numbertabletype;
41 g_parent_lstg_fk_table_pk edw_owb_collection_util.varchartabletype;
42 g_parent_ltc_fk_table edw_owb_collection_util.varchartabletype;
43 g_parent_ltc_fk_table_id edw_owb_collection_util.numbertabletype;
44 g_parent_ltc_fk_table_long edw_owb_collection_util.varchartabletype;
45 g_parent_ltc_fk_table_pk edw_owb_collection_util.varchartabletype;
46 g_parent_ltc_fk_table_pk_long edw_owb_collection_util.varchartabletype;
47 g_lstg_fk edw_owb_collection_util.varchartabletype;
48 g_lstg_fk_id edw_owb_collection_util.numbertabletype;
49 g_lstg_fk_long edw_owb_collection_util.varchartabletype;
50 g_lstg_fk_number NUMBER;
51 g_hier edw_owb_collection_util.varchartabletype;
52
53 --we need distinct list of hierarchies....
54 g_hier_distinct edw_owb_collection_util.varchartabletype;
55 g_number_hier_distinct NUMBER;
56 g_check_dimension BOOLEAN;
57 g_lstg_fk_position edw_owb_collection_util.numbertabletype;
58 --??
59 --g_lstg_fk_position will have g_number_lstg_tables entries
60 --each entry is an index into g_lstg_fk
61
62 g_ltc_tables edw_owb_collection_util.varchartabletype;
63 g_ltc_tables_long edw_owb_collection_util.varchartabletype;
64 g_ltc_tables_long_name edw_owb_collection_util.varchartabletype;
65 g_number_ltc_tables NUMBER;
66 g_ltc_pk edw_owb_collection_util.varchartabletype;
67 g_all_level_exists BOOLEAN;
68 g_all_level VARCHAR2 (400);
69 g_bottom_level VARCHAR2 (400);
70 g_bottom_records NUMBER;
71
72 /*
73 the sql statements
74 these are varchars instead of tables because we use binding
75 for high performance...like CBR929RR....
76 */
77 g_dup_stmt_num varchartabletypel;
78 g_dup_stmt_str varchartabletypel;
79 g_lstg_lstg_dangling_stmt_num varchartabletypel;
80 g_lstg_lstg_null_stmt_num varchartabletypel;
81 g_lstg_lstg_dangling_stmt_str varchartabletypel;
82 g_lstg_ltc_dangling_stmt_num varchartabletypel;
83 g_lstg_ltc_dangling_stmt_str varchartabletypel;
84 g_hier_stmt_num VARCHAR2 (30000);
85 g_collection_status_stmt_num varchartabletypel;
86 g_log_name VARCHAR2 (200);
87 g_debug BOOLEAN;
88 g_number_sample NUMBER;
89 --this is for logging into the output
90 g_number_max_sample NUMBER;
91 --this is for logging into the tables
92 g_check_against_ltc BOOLEAN;
93 g_check_hier BOOLEAN;
94 g_sample_on BOOLEAN;
95 g_names edw_owb_collection_util.varchartabletype;
96 g_names_long edw_owb_collection_util.varchartabletype;
97 g_ids edw_owb_collection_util.numbertabletype;
98 g_number_names NUMBER;
99 g_object_name VARCHAR2 (200);
100 g_object_type VARCHAR2 (40);
101 g_object_id NUMBER;
102 g_status_message VARCHAR2 (4000);
103 g_exec_flag BOOLEAN;
104 g_duplicate_check BOOLEAN;
105
106 /***********************************************
107 Fact Variables
108 ************************************************/
109 g_fstg_name VARCHAR2 (400);
110 g_fstg_id NUMBER;
111 g_fstg_name_long VARCHAR2 (400);
112 g_fstg_pk VARCHAR2 (400);
113 g_fstg_pk_id NUMBER;
114 g_fstg_pk_long VARCHAR2 (400);
115 g_fstg_fk edw_owb_collection_util.varchartabletype;
116 g_fstg_fk_id edw_owb_collection_util.numbertabletype;
117 g_fstg_fk_long edw_owb_collection_util.varchartabletype;
118 g_number_fstg_fk NUMBER;
119 g_fstg_instance_col VARCHAR2 (400);
120 --assume that this is a fk for now
121 g_fact_dims edw_owb_collection_util.varchartabletype;
122 g_fact_dims_id edw_owb_collection_util.numbertabletype;
123 g_fact_dims_long edw_owb_collection_util.varchartabletype;
124 g_fact_dims_pk edw_owb_collection_util.varchartabletype;
125 g_fact_dims_pk_long edw_owb_collection_util.varchartabletype;
126 g_fstg_total_records NUMBER;
127
128 /***********STATEMENTS*****************/
129 g_fact_dup_stmt_num VARCHAR2 (20000);
130 g_fact_dup_stmt_str VARCHAR2 (20000);
131 g_fact_dang_stmt_num varchartabletypel;
132 g_fact_dang_stmt_str varchartabletypel;
133 g_number_dang_stmt NUMBER;
134 g_fstg_makeit_stmt VARCHAR2 (30000);
135
136 --g_lstg_rowid_table EDW_OWB_COLLECTION_UTIL.varcharTableType;
137 g_lstg_pk_table edw_owb_collection_util.varchartabletype;
138 g_lstg_dup_pk_table edw_owb_collection_util.varchartabletype;
139 --holds the dup pk and count
140 /*g_parent_lstg EDW_OWB_COLLECTION_UTIL.varcharTableType;
141 g_child_lstg EDW_OWB_COLLECTION_UTIL.varcharTableType;
142 g_parent_lstg_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;
143 g_child_lstg_fk EDW_OWB_COLLECTION_UTIL.varcharTableType;
144 g_number_parent_lstg number;
145 g_parent_ltc EDW_OWB_COLLECTION_UTIL.varcharTableType;
146 g_parent_ltc_pk EDW_OWB_COLLECTION_UTIL.varcharTableType;*/
147 g_lstg_fk_hold_table edw_owb_collection_util.varchartabletype;
148 --will hold the fks from the lstg
149 g_number_lstg_fk_hold_table edw_owb_collection_util.numbertabletype;
150 g_lstg_ok_table edw_owb_collection_util.varchartabletype;
151 -- rowids of the staging table that are ok with lstg
152 g_lstg_dang_table edw_owb_collection_util.varchartabletype;
153 -- FKS and the count of the staging table that are dang with lstgs
154 g_lstg_dang_rowid_table edw_owb_collection_util.varchartabletype;
155 -- rowids of the staging table that are dang with lstgs
156 g_ltc_ok_table edw_owb_collection_util.varchartabletype;
157 -- rowids of the staging table that are ok with ltc
158 g_ltc_dang_table edw_owb_collection_util.varchartabletype;
159 -- rowids of the staging table that are dang with ltc
160 g_ltc_dang_rowid_table edw_owb_collection_util.varchartabletype;
161
162 --g_fact_rowid_table varchar2(400);
163 g_fact_pk_table VARCHAR2 (400);
164 g_fact_dup_pk_table VARCHAR2 (400);
165 g_fact_fk_table edw_owb_collection_util.varchartabletype;
166 g_number_fact_fk_table edw_owb_collection_util.numbertabletype;
167 g_fact_fk_ok_table edw_owb_collection_util.varchartabletype;
168 g_fact_fk_dang_rowid_table edw_owb_collection_util.varchartabletype;
169 g_bis_owner VARCHAR2 (400);
170 g_fk_to_check edw_owb_collection_util.varchartabletype;
171 g_number_fk_to_check NUMBER;
172 g_fk_check_flag edw_owb_collection_util.booleantabletype;
173 g_results_table_flag BOOLEAN;
174 g_results_table VARCHAR2 (200);
175 g_request_id NUMBER;
176 g_op_table_space VARCHAR2 (400);
177 g_dim_missing_keys_op VARCHAR2 (400);
178 g_process_dang_keys BOOLEAN;
179
180 PROCEDURE init_all (p_object_name IN VARCHAR2);
181
182 PROCEDURE close_all;
183
184 PROCEDURE write_to_log (p_message IN VARCHAR2);
185
186 PROCEDURE write_to_log_n (p_message IN VARCHAR2);
187
188 PROCEDURE write_to_out (p_message IN VARCHAR2);
189
190 PROCEDURE write_to_out_n (p_message IN VARCHAR2);
191
192 FUNCTION check_dimension (p_dim_name IN VARCHAR2)
193 RETURN BOOLEAN;
194
195 FUNCTION get_time
196 RETURN VARCHAR2;
197
198 FUNCTION get_lstg_ltc_keys (p_dim_name IN VARCHAR2)
199 RETURN BOOLEAN;
200
201 FUNCTION get_lstg_ltc_pk (p_dim_name IN VARCHAR2)
202 RETURN BOOLEAN;
203
204 FUNCTION get_lstg_ltc_fk (p_dim_name IN VARCHAR2)
205 RETURN BOOLEAN;
206
207 PROCEDURE check_dimensions_data (
208 errbuf OUT NOCOPY VARCHAR2,
209 retcode OUT NOCOPY VARCHAR2,
210 p_dim_string1 IN VARCHAR2,
211 p_check_against_ltc IN VARCHAR2,
212 p_check_tot_recs IN VARCHAR2,
213 p_detailed_check IN VARCHAR2,
214 p_sample_size IN NUMBER
215 );
216
217 FUNCTION parse_names (
218 p_dim_string1 IN VARCHAR2,
219 p_dim_string2 IN VARCHAR2,
220 p_dim_string3 IN VARCHAR2,
221 p_dim_string4 IN VARCHAR2,
222 p_dim_string5 IN VARCHAR2
223 )
224 RETURN BOOLEAN;
225
226 FUNCTION get_lstg_given_ltc (p_ltc IN VARCHAR2)
227 RETURN VARCHAR2;
228
229 FUNCTION get_lstg_fk_for_ltc (
230 p_lstg_child IN edw_owb_collection_util.varchartabletype,
231 p_lstg_child_fk IN edw_owb_collection_util.varchartabletype,
232 p_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
233 p_lstg_number IN NUMBER,
234 p_lstg_fk_table IN VARCHAR2,
235 p_parent_ltc_fk_table IN VARCHAR2,
236 p_lstg_fk_table_prev IN VARCHAR2,
237 p_parent_ltc_fk_table_prev IN VARCHAR2,
238 p_lstg_fk_prev IN VARCHAR2
239 )
240 RETURN VARCHAR2;
241
242 FUNCTION make_sql_statements
243 RETURN BOOLEAN;
244
245 FUNCTION make_hier_count_stmt
246 RETURN BOOLEAN;
247
248 FUNCTION execute_dim_check (p_dim_name IN VARCHAR2)
249 RETURN BOOLEAN;
250
251 FUNCTION execute_dim_duplicate_check (p_dim_name IN VARCHAR2)
252 RETURN BOOLEAN;
253
254 FUNCTION execute_dim_dangling_check (p_dim_name IN VARCHAR2)
255 RETURN BOOLEAN;
256
257 FUNCTION get_pk_for_lstg (p_lstg IN VARCHAR2)
258 RETURN VARCHAR2;
259
260 FUNCTION get_pk_for_ltc (
261 p_ltc IN VARCHAR2,
262 l_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
263 l_lstg_ltc_parent_pk IN edw_owb_collection_util.varchartabletype,
264 l_number_lstg IN NUMBER
265 )
266 RETURN VARCHAR2;
267
268 FUNCTION execute_dim_dang_check_lstg (p_dim_name IN VARCHAR2)
269 RETURN BOOLEAN;
270
271 FUNCTION execute_dim_dang_check_ltc (p_dim_name IN VARCHAR2)
272 RETURN BOOLEAN;
273
274 FUNCTION execute_dim_all_records (p_dim_name IN VARCHAR2)
275 RETURN BOOLEAN;
276
277 FUNCTION get_num_recs_lstg (p_lstg IN VARCHAR2)
278 RETURN NUMBER;
279
280 FUNCTION execute_hier_count (p_dim_name IN VARCHAR2)
281 RETURN BOOLEAN;
282
283 FUNCTION get_table_alias (p_table IN VARCHAR2)
284 RETURN VARCHAR2;
285
286 PROCEDURE write_to_out_log (p_message IN VARCHAR2);
287
288 PROCEDURE write_to_out_log_n (p_message IN VARCHAR2);
289
290 FUNCTION get_long_names
291 RETURN BOOLEAN;
292
293 FUNCTION get_long_for_short_name (p_name IN VARCHAR2)
294 RETURN VARCHAR2;
295
296 FUNCTION get_lstg_long_name (p_table IN VARCHAR2)
297 RETURN VARCHAR2;
298
299 FUNCTION get_fk_long (
300 p_fk IN VARCHAR2,
301 fk_table_long IN edw_owb_collection_util.varchartabletype,
302 fk_table IN edw_owb_collection_util.varchartabletype,
303 fk_table_number IN NUMBER
304 )
305 RETURN VARCHAR2;
306
307 FUNCTION get_parent_ltc_long (
308 p_ltc IN VARCHAR2,
309 p_lstg_ltc_parent IN edw_owb_collection_util.varchartabletype,
310 p_lstg_ltc_parent_long IN edw_owb_collection_util.varchartabletype,
311 p_number_lstg IN NUMBER
312 )
313 RETURN VARCHAR2;
314
315 FUNCTION get_ltc_pk_long (
316 p_parent_ltc_fk_table_pk IN VARCHAR2,
317 p_lstg_ltc_parent_pk IN edw_owb_collection_util.varchartabletype,
318 p_lstg_ltc_parent_pk_long IN edw_owb_collection_util.varchartabletype,
319 p_number_lstg IN NUMBER
320 )
321 RETURN VARCHAR2;
322
323 FUNCTION get_lstg_pk (p_table IN VARCHAR2)
324 RETURN VARCHAR2;
325
326
327 /*
328 -------------------------------------------------------
329 Procedures to check facts
330 -------------------------------------------------------
331 */
332 PROCEDURE check_facts_data (
333 errbuf OUT NOCOPY VARCHAR2,
337 p_detailed_check IN VARCHAR2,
334 retcode OUT NOCOPY VARCHAR2,
335 p_fact_string1 IN VARCHAR2,
336 p_check_tot_recs IN VARCHAR2,
338 p_sample_size IN NUMBER,
339 p_fk_to_check IN VARCHAR2 DEFAULT NULL
340 );
341
342 FUNCTION check_fact (p_fact_name IN VARCHAR2, p_fact_name_long IN VARCHAR2)
343 RETURN BOOLEAN;
344
345 FUNCTION get_fstg_dim_keys (p_fact_name IN VARCHAR2)
346 RETURN BOOLEAN;
347
348 FUNCTION execute_fact_check (p_fact_name IN VARCHAR2)
349 RETURN BOOLEAN;
350
351 FUNCTION make_sql_statements_fact (p_fact_name IN VARCHAR2)
352 RETURN BOOLEAN;
353
354 FUNCTION execute_fact_duplicate_check (p_fact_name IN VARCHAR2)
355 RETURN BOOLEAN;
356
357 FUNCTION execute_fact_dangling_check (p_fact_name IN VARCHAR2)
358 RETURN BOOLEAN;
359
360 FUNCTION execute_fact_total_records (p_fact_name IN VARCHAR2)
361 RETURN BOOLEAN;
362
363 FUNCTION execute_fstg_makeit_stmt (p_fact_name IN VARCHAR2)
364 RETURN BOOLEAN;
365
366 FUNCTION generate_fk_table (p_fact_name IN VARCHAR2)
367 RETURN BOOLEAN;
368
369 PROCEDURE clean_up;
370
371 FUNCTION drop_lstg_fk_tables
372 RETURN BOOLEAN;
373
374 FUNCTION create_lstg_fk_tables
375 RETURN BOOLEAN;
376
377 FUNCTION drop_lstg_pk_tables
378 RETURN BOOLEAN;
379
380 FUNCTION create_fstg_fk_tables (p_fact_name IN VARCHAR2)
381 RETURN BOOLEAN;
382
383 FUNCTION drop_fstg_fk_tables (p_fact_name IN VARCHAR2)
384 RETURN BOOLEAN;
385
386 FUNCTION drop_fstg_pk_table (p_fact_name IN VARCHAR2)
387 RETURN BOOLEAN;
388
389 FUNCTION drop_fk_table (p_fact_name IN VARCHAR2)
390 RETURN BOOLEAN;
391
392 FUNCTION create_main_lstg_fk_tables
393 RETURN BOOLEAN;
394
395 FUNCTION get_fk_to_check (p_fk_to_check IN VARCHAR2)
396 RETURN BOOLEAN;
397
398 FUNCTION log_into_cdi_results_table (
399 p_object IN VARCHAR2,
400 p_object_type IN VARCHAR2,
401 p_object_id IN NUMBER,
402 p_interface_table IN VARCHAR2,
403 p_interface_table_id IN NUMBER,
404 p_interface_table_pk IN VARCHAR2,
405 p_interface_table_pk_id IN NUMBER,
406 p_interface_table_fk IN VARCHAR2,
407 p_interface_table_fk_id IN NUMBER,
408 p_parent_table IN VARCHAR2,
409 p_parent_table_id IN NUMBER,
410 p_parent_table_pk IN VARCHAR2,
411 p_parent_table_pk_id IN NUMBER,
412 p_number_dangling IN NUMBER,
413 p_number_duplicate IN NUMBER,
414 p_number_error IN NUMBER,
415 p_total_records IN NUMBER,
416 p_error_type IN VARCHAR2
417 )
418 RETURN BOOLEAN;
419
420 FUNCTION delete_cdi_results_table (p_object_name IN VARCHAR2)
421 RETURN BOOLEAN;
422
423 FUNCTION log_into_cdi_dang_table (
424 p_key_id IN NUMBER,
425 p_table_id IN NUMBER,
426 p_parent_table_id IN NUMBER,
427 p_key_value IN VARCHAR2,
428 p_number_key_value IN NUMBER,
429 p_instance IN VARCHAR2
430 )
431 RETURN BOOLEAN;
432
433 FUNCTION create_g_dim_missing_keys_op (p_object_name IN VARCHAR2)
434 RETURN BOOLEAN;
435
436 FUNCTION process_dang_keys (p_fact IN VARCHAR2)
437 RETURN BOOLEAN;
438
439 FUNCTION find_missing_date_range (
440 p_fact IN VARCHAR2,
441 p_fact_id IN NUMBER,
442 p_dim IN VARCHAR2,
443 p_dim_id IN NUMBER,
444 p_instance IN VARCHAR2,
445 p_instance_link IN VARCHAR2
446 )
447 RETURN BOOLEAN;
448
449 FUNCTION find_missing_date_range (
450 p_fact IN VARCHAR2,
451 p_fact_id IN NUMBER,
452 p_dim IN VARCHAR2,
453 p_dim_id IN NUMBER,
454 p_instance IN VARCHAR2,
455 p_instance_link IN VARCHAR2,
456 p_view IN VARCHAR2,
457 p_min_date OUT NOCOPY DATE,
458 p_max_date OUT NOCOPY DATE
459 )
460 RETURN BOOLEAN;
461
462 FUNCTION create_bad_key_tables (
463 p_fact IN VARCHAR2,
464 p_fact_id IN NUMBER,
465 p_dang_dim IN VARCHAR2,
466 p_dang_dim_id IN NUMBER,
467 p_dang_dim_instance_id IN edw_owb_collection_util.numbertabletype,
468 p_dang_instances IN edw_owb_collection_util.varchartabletype,
469 p_number_dang_instances IN NUMBER
470 )
471 RETURN NUMBER;
472 END edw_check_data_integrity;