[Home] [Help]
PACKAGE BODY: APPS.DT_CHECKINT
Source
1 package body dt_checkint as
2 /* $Header: dtchkint.pkb 115.1 99/07/16 23:59:47 porting ship $ */
3 --
4 --
5 --
6 -- GLOBAL VARIABLES AND TYPES
7 --
8 g_output varchar2(80) := 'DBMS_OUTPUT' ;
9 g_schema varchar2(80) := user ;
10
11
12 --
13 -- A row is retrieved into the following record type
14 --
15 type dt_row is record ( id_value number ,
16 effective_start_date date,
17 effective_end_date date,
18 creation_date date,
19 last_update_date date ) ;
20
21 g_temp_row dt_row ;
22
23 --
24 -- The row is then stored in the following type
25 --
26 type tab_id_value is table of number index by binary_integer ;
27 type tab_effective_start_date is table of date index by binary_integer ;
28 type tab_effective_end_date is table of date index by binary_integer ;
29 type tab_creation_date is table of date index by binary_integer ;
30 type tab_message is table of varchar2(80) index by binary_integer ;
31
32 g_tab_id_value tab_id_value;
33 g_tab_effective_start_date tab_effective_start_date;
34 g_tab_effective_end_date tab_effective_start_date;
35 g_tab_creation_date tab_effective_start_date;
36 g_tab_last_update_date tab_effective_start_date;
37 g_tab_message tab_message;
38
39 g_empty_number tab_id_value ;
40 g_empty_date tab_effective_start_date ;
41 g_empty_message tab_message ;
42
43 TABLE_MAXSIZE constant number := 20 ; -- Maximum number of datetrack changes for a given id
44
45
46
47 g_db_type varchar2(30) ;
48 subtype db_col_type is g_db_type%type ; -- In later release of pl/sql can put a number here directly
49
50
51 type table_info is record ( tab_name varchar2(30),
52 schema_name varchar2(30),
53 has_who_cols boolean ) ;
54
55 --
56 -- PRIVATE ROUTINES
57 --
58 -- Name
59 -- initialize_output
60 -- Purpose
61 -- If DBMS_PIPE is specified then enable trace
62 -- Notes
63 procedure initialize_output is
64 begin
65
66 if ( g_output = 'DBMS_PIPE' ) then
67
68 hr_utility.trace_on('F') ;
69
70 end if;
71
72 end initialize_output ;
73
74 -- Name
75 -- output_line
76 -- Purpose
77 -- Outputs a text line.
78 -- Notes
79 procedure output_line ( p_text in varchar2 ) is
80 begin
81
82 if ( g_output = 'DBMS_OUTPUT' ) then
83
84 dbms_output.put_line ( p_text ) ;
85
86 else
87
88 hr_utility.trace( p_text ) ;
89
90
91 end if;
92
93 end output_line ;
94
95 -- Name
96 -- output_newline
97 -- Purpose
98 -- Outputs a blank line
99 -- Notes
100 procedure output_newline is
101 begin
102
103 output_line('');
104
105 end output_newline ;
106
107 -- Name
108 -- output_row
109 -- Purpose
110 -- Outputs a row
111 -- Notes
112 --
113 procedure output_row ( p_row in dt_row,
114 p_message in varchar2 ) is
115
116 l_eff_date_fmt varchar2(30) := 'DD-MON-YYYY' ;
117 l_who_col_fmt varchar2(30) := 'DD-MON-YYYY HH24:MI:SS' ;
118 begin
119
120 output_line ( ' '||lpad(to_char(p_row.ID_VALUE),8)||
121 ' '||to_char(p_row.EFFECTIVE_START_DATE , l_eff_date_fmt ) ||
122 ' '||to_char(p_row.EFFECTIVE_END_DATE , l_eff_date_fmt ) ||
123 ' '||to_char(p_row.CREATION_DATE ,l_who_col_fmt)||
124 ' ' ||to_char(p_row.LAST_UPDATE_DATE ,l_who_col_fmt)||' '|| p_message ) ;
125
126 end output_row ;
127 procedure output_row ( p_id_value in number,
128 p_effective_start_date in date,
129 p_effective_end_date in date,
130 p_creation_date in date,
131 p_last_update_date in date,
132 p_message in varchar2 ) is
133 l_dt_row dt_row ;
134 begin
135
136 l_dt_row.id_value := p_id_value ;
137 l_dt_row.effective_start_date := p_effective_start_date ;
138 l_dt_row.effective_end_date := p_effective_end_date ;
139 l_dt_row.creation_date := p_creation_date ;
140 l_dt_row.last_update_date := p_last_update_date ;
141
142 output_row ( l_dt_row , p_message ) ;
143
144 end output_row ;
145
146 -------------------------------------------------------------------------------------------
147
148 -- Name
149 -- build_sql
150 -- Purpose
151 -- Builds the cursor which is designed to check the table.
152 -- Notes
153 function build_sql ( p_table in out table_info ) return varchar2 is
154 l_template_cursor varchar2(2000) :=
155
156 'select :id_col , effective_start_date , effective_end_date :who_cols
157 from :table_name
158 order by 1 , 2 , 3 ' ;
159
160 function get_id_col ( p_table_name in varchar2 , p_schema in varchar2 ) return varchar2 is
161 cursor get_pk_col_name is
162 select ind.column_name
163 from all_ind_columns ind,
164 all_constraints cons
165 where cons.owner = p_schema
166 and cons.table_name = p_table_name
167 and cons.constraint_type = 'P'
168 and cons.constraint_name = ind.index_name
169 and ind.table_owner = p_schema
170 and ind.table_name = p_table_name
171 and ind.column_position = 1 ;
172
173 l_return_value db_col_type ;
174 begin
175
176
177 open get_pk_col_name;
178 fetch get_pk_col_name into l_return_value ;
179 close get_pk_col_name ;
180
181 return ( l_return_value ) ;
182
183 end get_id_col ;
184
185 -- Determines whether the given table has the standard who cols
186 function table_has_who_cols ( p_table_name in varchar2 , p_schema in varchar2 ) return boolean is
187 cursor get_creation_date_col is
188 select 1
189 from all_tab_columns col
190 where col.owner = p_schema
191 and col.table_name = p_table_name
192 and col.column_name = 'CREATION_DATE' ;
193 l_dummy number ;
194 l_return_status boolean ;
195 begin
196
197 open get_creation_date_col ;
198 fetch get_creation_date_col into l_dummy ;
199 l_return_status := get_creation_date_col%found ;
200 close get_creation_date_col ;
201
202 return ( l_return_status ) ;
203
204 end table_has_who_cols ;
205
206
207 begin
208
209 l_template_cursor := replace ( l_template_cursor , ':id_col' , get_id_col(p_table.tab_name, p_table.schema_name ) ) ;
210
211 p_table.has_who_cols := table_has_who_cols ( p_table.tab_name , p_table.schema_name ) ;
212
213 if ( p_table.has_who_cols )
214 then
215
216 l_template_cursor := replace ( l_template_cursor ,
217 ':who_cols',
218 ',CREATION_DATE, LAST_UPDATE_DATE ' ) ;
219
220 else
221
222 l_template_cursor := replace( l_template_cursor , ':who_cols' , null ) ;
223
224 end if;
225
226
227 l_template_cursor := replace ( l_template_cursor , ':table_name' , p_table.tab_name ) ;
228
229 return( l_template_cursor ) ;
230
231 end build_sql ;
232
233 -------------------------------------------------------------------------------------------
234
235
236 function prepare_cursor ( p_table in out table_info ) return integer is
237 l_theStatement varchar2(2000) ;
238 l_theCursor integer ;
239 begin
240
241 l_theCursor := dbms_sql.open_cursor ;
242 l_theStatement := build_sql( p_table ) ;
243
244 dbms_sql.parse( l_theCursor, l_theStatement, dbms_sql.v7 ) ;
245 dbms_sql.define_column( l_theCursor, 1 , g_temp_row.id_value) ;
246 dbms_sql.define_column( l_theCursor, 2 , g_temp_row.effective_start_date) ;
247 dbms_sql.define_column( l_theCursor, 3 , g_temp_row.effective_end_date) ;
248
249 if ( p_table.has_who_cols )
250 then
251
252 dbms_sql.define_column( l_theCursor, 4 , g_temp_row.creation_date) ;
253 dbms_sql.define_column( l_theCursor, 5 , g_temp_row.last_update_date) ;
254
255 end if;
256
257 return ( l_theCursor ) ;
258
259 end prepare_cursor ;
260
261 -------------------------------------------------------------------------------------------
262
263 function copy_to_row ( p_cursor in integer , p_table table_info ) return dt_row is
264 l_theRow dt_row ;
265 begin
266
267 dbms_sql.column_value( p_cursor, 1 , l_theRow.id_value) ;
268 dbms_sql.column_value( p_cursor, 2 , l_theRow.effective_start_date) ;
269 dbms_sql.column_value( p_cursor, 3 , l_theRow.effective_end_date) ;
270
271 if ( p_table.has_who_cols )
272 then
273
274 dbms_sql.column_value( p_cursor, 4 , l_theRow.creation_date) ;
275 dbms_sql.column_value( p_cursor, 5 , l_theRow.last_update_date) ;
276
277 end if;
278
279 return ( l_theRow ) ;
280
281 end copy_to_row ;
282
283 -------------------------------------------------------------------------------------------
284 procedure initialise_table(p_rowcount in number default null) is
285 begin
286
287 for i in 1..p_rowcount loop
288
289 g_tab_id_value(i) := null ;
290 g_tab_effective_start_date(i) := null ;
291 g_tab_effective_end_date(i) := null ;
292 g_tab_creation_date(i) := null ;
293 g_tab_last_update_date(i) := null ;
294 g_tab_message(i) := null ;
295
296 end loop ;
297
298 end initialise_table ;
299 -------------------------------------------------------------------------------------------
300 procedure save_to_table ( p_index number , p_row dt_row , p_message in varchar2 ) is
301 begin
302
303 g_tab_id_value(p_index) := p_row.id_value ;
304 g_tab_effective_start_date(p_index) := p_row.effective_start_date ;
305 g_tab_effective_end_date(p_index) := p_row.effective_end_date ;
306 g_tab_creation_date(p_index) := p_row.creation_date ;
307 g_tab_last_update_date(p_index) := p_row.last_update_date ;
308 g_tab_message(p_index) := p_message ;
309
310 end save_to_table ;
311 -------------------------------------------------------------------------------------------
312 procedure output_table( p_has_who_cols in boolean ) is
313
314 procedure output_header ( p_has_who_cols in boolean ) is
315 begin
316
317 output_line( ' The following set contains at least one invalid row.');
318
319 if ( not p_has_who_cols ) then
320 output_line(' Note - This table does not have WHO columns.');
321 end if;
322
323 output_newline;
324
325 output_line(' ID EFF. START EFF. END DATE CREATED LAST UPDATE ERROR');
326 output_line(' ------- ----------- ----------- -------------------- -------------------- --------------------------');
327
328 end output_header ;
329
330 begin
331
332 output_header(p_has_who_cols) ;
333
334 for i in 1..TABLE_MAXSIZE loop
335
336 exit when g_tab_id_value(i) is null ;
337
338 output_row ( g_tab_id_value(i),
339 g_tab_effective_start_date(i),
340 g_tab_effective_end_date(i),
341 g_tab_creation_date(i),
342 g_tab_last_update_date(i),
343 g_tab_message(i) ) ;
344
345 end loop ;
346
347 output_newline ;
348
349 end output_table ;
350 -------------------------------------------------------------------------------------------
351
352
353 -- Name
354 -- chk_no_time
355 -- Purpose
356 -- Checks that there is no time component in the effective start and end dates
357 -- Notes
358 function check_no_time ( p_therow dt_row ) return varchar2 is
359
360 begin
361
362 if ( ( p_therow.effective_start_date <> trunc(p_therow.effective_start_date) )
363 or ( p_therow.effective_end_date <> trunc(p_therow.effective_end_date ) )
364 ) then
365
369
366 return 'HAS TIME COMPONENT' ;
367
368 else
370 return null ;
371
372 end if ;
373
374 end check_no_time ;
375
376 -------------------------------------------------------------------------------------------
377
378 -- Name
379 -- check_start_before_end
380 -- Purpose
381 -- Checks that eff. start is before eff end
382 -- Notes
383 function check_start_before_end ( p_therow dt_row ) return varchar2 is
384 begin
385
386 if ( p_therow.effective_start_date > p_therow.effective_end_date ) then
387
388 return ('START DATE AFTER END DATE' ) ;
389
390 else
391
392 return null ;
393
394 end if;
395
396 end check_start_before_end ;
397
398 -- Name
399 -- check_for_gaps
400 -- Purpose
401 -- Compares two rows. If the ID columns have the same value then check the the start
402 -- date of the current row is a day after the end date of the previous row.
403 -- Also output an error if the last rows end date was end of time
404 -- Notes
405 function check_for_gaps ( p_current_row dt_row , p_last_row dt_row ) return varchar2 is
406 begin
407
408 if ( p_current_row.id_value = p_last_row.id_value ) then
409
410 if ( ( p_last_row.effective_end_date = hr_general.end_of_time )
411 OR ( p_current_row.effective_start_date < p_last_row.effective_end_date + 1 ) )
412 then
413
414 return( 'OVERLAPS PREVIOUS ROW' ) ;
415
416 elsif ( ( p_last_row.effective_end_date <> hr_general.end_of_time )
417 AND ( p_current_row.effective_start_date > p_last_row.effective_end_date + 1 ) ) then
418
419 return('GAP WITH PREVIOUS ROW') ;
420
421 end if;
422
423 end if;
424
425
426 return null ;
427
428
429 end check_for_gaps ;
430
431 -- PUBLIC PROCEDURES AND FUNCTIONS
432 -------------------------------------------------------------------------------------------
433
434 procedure set_options ( p_schema in varchar2,
435 p_output_dest in varchar2 default 'DBMS_OUTPUT' ) is
436 begin
437
438 g_schema := p_schema ;
439 g_output := p_output_dest ;
440
441 initialize_output ;
442
443 end set_options ;
444
445 procedure check_table ( p_table_name in varchar2,
446 p_max_errors in number default 1 ) is
447 l_cursor integer ;
448 l_ignore integer ;
449 l_current_row dt_row ;
450 l_last_row dt_row ;
451 l_thetable table_info ;
452
453 row_count binary_integer := 0 ;
454 errors_found boolean ;
455 error_text varchar2(2000) ; -- Error message for the current row
456
457 total_rows number ; -- Cumulative number of rows checked
458 total_keys number ; -- Cumulative number of distinct id values
459
460 begin
461
462
463
464 output_line ( 'Checking table '||p_table_name||'...') ;
465 output_newline ;
466
467 l_thetable.tab_name := p_table_name ;
468 l_thetable.schema_name := g_schema ;
469
470
471 l_cursor := prepare_cursor( l_thetable ) ;
472
473 l_ignore := dbms_sql.execute(l_cursor) ;
474
475
476 errors_found := false ;
477 row_count := 0 ;
478 initialise_table( TABLE_MAXSIZE ) ;
479
480 total_rows := 0 ;
481 total_keys := 0 ;
482
483 loop
484
485 if ( dbms_sql.fetch_rows ( l_cursor ) > 0 ) then
486
487
488 l_current_row := copy_to_row ( l_cursor , l_thetable ) ;
489 error_text := null ;
490
491 if ( l_current_row.id_value <> l_last_row.id_value ) then
492
493 if ( errors_found ) then
494
495 output_table(l_thetable.has_who_cols) ;
496
497 end if;
498
499 initialise_table(row_count);
500 row_count := 1 ;
501 errors_found := false ;
502
503 total_keys := total_keys + 1 ;
504
505 end if;
506
507
508 error_text := check_no_time ( l_current_row ) ;
509 error_text := error_text || check_start_before_end ( l_current_row ) ;
510 error_text := error_text || check_for_gaps( l_current_row , l_last_row ) ;
511
512
513 errors_found := ( error_text is not null ) ;
514
515
516 l_last_row := copy_to_row ( l_cursor , l_thetable ) ;
517
518 else
519
520 exit ;
521
522 end if;
523
524 save_to_table(row_count,l_current_row,error_text) ;
525 row_count := row_count + 1 ;
526
527 total_rows := total_rows + 1 ;
528
529
530 end loop ;
531
532
533 dbms_sql.close_cursor(l_cursor ) ;
534
535 if ( errors_found ) then
536
537 output_table(l_thetable.has_who_cols) ;
538
539 end if;
540
541 -- If there were rows then the total number of ids is off by one because
542 -- we only count the changes
543
544 if ( total_rows <> 0 ) then
545
546 total_keys := total_keys + 1 ;
547
548 end if;
549
550
551 output_line ( ' Total Number of Records Checked: '||to_char(total_rows)||' Distinct Key Values: '||to_char(total_keys));
552 output_newline ;
553
554
555 exception
556 when others then
557
558 hr_utility.trace('!!END TRACE');
562
559 raise ;
560
561 end check_table ;
563 -- Name
564 -- check_all_tables
565 -- Purpose
566 -- Checks All Datetracked tables for basic date rules
567 --
568 procedure check_all_tables is
569 begin
570
571 initialize_output ;
572
573 check_table('BEN_BENEFICIARIES_F');
574 check_table('BEN_BENEFIT_CONTRIBUTIONS_F');
575 check_table('BEN_COVERED_DEPENDENTS_F');
576 check_table('FF_COMPILED_INFO_F');
577 --check_table('FF_FDI_USAGES_F'); 4 cols in pk
578 check_table('FF_FORMULAS_F');
579 check_table('FF_GLOBALS_F');
580 check_table('PAY_ASSIGNMENT_LINK_USAGES_F');
581 check_table('PAY_BALANCE_FEEDS_F');
582 check_table('PAY_COST_ALLOCATIONS_F');
583 check_table('PAY_ELEMENT_ENTRIES_F');
584 check_table('PAY_ELEMENT_ENTRY_VALUES_F');
585 check_table('PAY_ELEMENT_LINKS_F');
586 check_table('PAY_ELEMENT_TYPES_F');
587 check_table('PAY_EXCHANGE_RATES_F');
588 check_table('PAY_FORMULA_RESULT_RULES_F');
589 check_table('PAY_GRADE_RULES_F');
590 check_table('PAY_INPUT_VALUES_F');
591 check_table('PAY_LINK_INPUT_VALUES_F');
592 check_table('PAY_ORG_PAYMENT_METHODS_F');
593 check_table('PAY_ORG_PAY_METHOD_USAGES_F');
594 check_table('PAY_ALL_PAYROLLS_F');
595 check_table('PAY_PERSONAL_PAYMENT_METHODS_F');
596 -- check_table('PAY_REPORT_FORMAT_MAPPINGS_F'); More than 3 cols in pk,no who
597 check_table('PAY_STATUS_PROCESSING_RULES_F');
598 check_table('PAY_SUB_CLASSIFICATION_RULES_F');
599 check_table('PAY_USER_COLUMN_INSTANCES_F');
600 check_table('PAY_USER_ROWS_F');
601 check_table('PAY_US_EMP_FED_TAX_RULES_F');
602 check_table('PAY_US_EMP_LOCAL_TAX_RULES_F');
603 check_table('PAY_US_EMP_STATE_TAX_RULES_F');
604 check_table('PAY_US_GARN_ARREARS_RULES_F');
605 check_table('PAY_US_GARN_EXEMPTION_RULES_F');
606 check_table('PAY_US_GARN_FEE_RULES_F');
607 check_table('PAY_US_GARN_LIMIT_RULES_F');
608 check_table('PER_ALL_ASSIGNMENTS_F');
609 check_table('PER_COBRA_COVERAGE_BENEFITS_F');
610 check_table('PER_COBRA_QFYING_EVENTS_F');
611 check_table('PER_GRADE_SPINES_F');
612 check_table('PER_ALL_PEOPLE_F');
613 check_table('PER_SPINAL_POINT_PLACEMENTS_F');
614 check_table('PER_SPINAL_POINT_STEPS_F');
615
616 end check_all_tables ;
617
618 end dt_checkint ;