[Home] [Help]
PACKAGE BODY: APPS.BSC_UPDATE_VAL
Source
1 PACKAGE BODY BSC_UPDATE_VAL AS
2 /* $Header: BSCDVALB.pls 120.4 2006/01/31 13:37:14 meastmon noship $ */
3
4
5 /*===========================================================================+
6 | FUNCTION Delete_Invalid_Zero_Codes
7 +============================================================================*/
8 FUNCTION Delete_Invalid_Zero_Codes(
9 x_error_msg OUT NOCOPY VARCHAR2
10 ) RETURN BOOLEAN IS
11
12 CURSOR c_base_tables (p_db_transform VARCHAR2) IS
13 SELECT DISTINCT table_name
14 FROM bsc_db_tables_rels
15 WHERE source_table_name IN (
16 SELECT table_name
17 FROM bsc_db_tables
18 WHERE table_type = 0
19 )
20 START WITH table_name IN (
21 SELECT table_name
22 FROM bsc_kpi_data_tables t, bsc_kpi_properties p
23 WHERE t.indicator = p.indicator (+) AND
24 t.table_name is not null AND
25 p.property_code (+) = p_db_transform AND
26 nvl(p.property_value,1) <> 0
27 )
28 CONNECT BY table_name = PRIOR source_table_name;
29
30 CURSOR c_key_columns (p_table VARCHAR2, p_column_type VARCHAR2) IS
31 SELECT column_name
32 FROM bsc_db_tables_cols
33 WHERE table_name = p_table AND column_type = p_column_type;
34
35
36 h_db_transform VARCHAR2(20);
37 h_base_table VARCHAR2(30);
38 h_p VARCHAR2(1);
39 h_sql VARCHAR2(32000);
40 h_key_column VARCHAR2(100);
41 h_where_cond VARCHAR2(32000);
42
43 BEGIN
44 h_db_transform := 'DB_TRANSFORM';
45 h_p := 'P';
46
47 -- This cusror return all the base tables for indicators that are not precalculated.
48 OPEN c_base_tables(h_db_transform);
49 LOOP
50 FETCH c_base_tables INTO h_base_table;
51 EXIT WHEN c_base_tables%NOTFOUND;
52
53 h_where_cond := NULL;
54 OPEN c_key_columns(h_base_table, h_p);
55 LOOP
56 FETCH c_key_columns INTO h_key_column;
57 EXIT WHEN c_key_columns%NOTFOUND;
58
59 IF h_where_cond IS NOT NULL THEN
60 h_where_cond := h_where_cond||' OR ';
61 END IF;
62 h_where_cond := h_where_cond||h_key_column||'=''0''';
63 END LOOP;
64 CLOSE c_key_columns;
65
66 IF h_where_cond IS NOT NULL THEN
67 h_sql := 'DELETE FROM '||h_base_table||
68 ' WHERE '||h_where_cond;
69 EXECUTE IMMEDIATE h_sql;
70 commit;
71 END IF;
72 END LOOP;
73 CLOSE c_base_tables;
74
75 RETURN TRUE;
76
77 EXCEPTION
78 WHEN OTHERS THEN
79 x_error_msg := SQLERRM;
80 RETURN FALSE;
81 END Delete_Invalid_Zero_Codes;
82
83
84 /*===========================================================================+
85 | FUNCTION Is_Table_For_PreCalc_Kpi
86 +============================================================================*/
87 FUNCTION Is_Table_For_PreCalc_Kpi(
88 x_table IN VARCHAR2
89 ) RETURN BOOLEAN IS
90
91 h_count NUMBER;
92 h_db_transform VARCHAR2(20);
93
94 BEGIN
95 h_db_transform := 'DB_TRANSFORM';
96
97 SELECT count(*)
98 INTO h_count
99 FROM (
100 SELECT source_table_name
101 FROM bsc_db_tables_rels
102 START WITH table_name IN (
103 SELECT table_name
104 FROM bsc_kpi_data_tables t, bsc_kpi_properties p
105 WHERE t.indicator = p.indicator AND
106 t.table_name is not null AND
107 p.property_code = h_db_transform AND
108 p.property_value = 0
109 )
110 CONNECT BY table_name = PRIOR source_table_name
111 )
112 WHERE source_table_name = x_table;
113
114 IF h_count > 0 THEN
115 RETURN TRUE;
116 ELSE
117 RETURN FALSE;
118 END IF;
119
120 END Is_Table_For_PreCalc_Kpi;
121
122
123 /*===========================================================================+
124 | FUNCTION Validate_Codes
125 +============================================================================*/
126 FUNCTION Validate_Codes(
127 x_input_table IN VARCHAR2
128 ) RETURN BOOLEAN IS
129
130 e_unexpected_error EXCEPTION;
131
132 TYPE t_cursor IS REF CURSOR;
133
134 /* c_dim_cols t_cursor; -- x_input_table, h_column_type_p
135 c_dim_cols_sql VARCHAR2(2000) := 'SELECT t.column_name, d.level_view_name'||
136 ' FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d'||
137 ' WHERE t.table_name = :1 AND t.column_type = :2 AND'||
138 ' t.column_name = d.level_pk_col'; */
139
140 cursor c_dim_cols( pTableName varchar2, pColTYpe varchar) is
141 SELECT t.column_name, d.level_view_name, d.short_name, d.source
142 FROM bsc_db_tables_cols t, bsc_sys_dim_levels_b d
143 WHERE t.table_name = pTableName
144 AND t.column_type = pColType
145 AND t.column_name = d.level_pk_col ;
146
147 h_column_type_p VARCHAR2(1);
148
149 h_column_name bsc_db_tables_cols.column_name%TYPE;
150 h_level_table_name bsc_sys_dim_levels_b.level_view_name%TYPE;
151 h_level_short_name bsc_sys_dim_levels_b.short_name%TYPE;
152 h_level_source bsc_sys_dim_levels_b.short_name%TYPE;
153 h_dbi_dim_data BSC_UPDATE_DIM.t_dbi_dim_data;
154
155 h_sql VARCHAR2(2000);
156
157 h_periodicity NUMBER;
158 h_period_col_name VARCHAR2(15);
159 h_subperiod_col_name VARCHAR2(15);
160
161 h_db_calendar_col_name VARCHAR2(30);
162 h_invalid BOOLEAN;
163
164 h_edw_flag NUMBER;
165 h_target_flag NUMBER;
166 h_current_fy NUMBER;
167 h_calendar_id NUMBER;
168 h_yearly_flag NUMBER;
169 h_num_of_years NUMBER;
170 h_previous_years NUMBER;
171 h_start_year NUMBER;
172 h_end_year NUMBER;
173
174 h_periodicity_type NUMBER;
175
176 /* c_mn_rels t_cursor; -- 2, x_input_table, h_column_type_p, x_input_table, h_column_type_p
177 c_mn_rels_sql VARCHAR2(2000) := 'SELECT d1.level_view_name as p1_table, d1.level_pk_col as p1_pk_col,'||
178 ' d2.level_view_name as p2_table, d2.level_pk_col as p2_pk_col,'||
179 ' r.relation_col as rel_table'||
180 ' FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b d1,'||
181 ' bsc_sys_dim_levels_b d2'||
182 ' WHERE r.relation_type = :1 AND r.dim_level_id = ('||
183 ' SELECT min(r2.dim_level_id)'||
184 ' FROM bsc_sys_dim_level_rels r2'||
185 ' WHERE r.relation_col = r2.relation_col) AND'||
186 ' r.dim_level_id = d1.dim_level_id AND'||
187 ' r.parent_dim_level_id = d2.dim_level_id AND'||
188 ' d1.level_pk_col in ('||
189 ' SELECT column_name'||
190 ' FROM bsc_db_tables_cols'||
191 ' WHERE table_name = :2 AND column_type = :3) AND'||
192 ' d2.level_pk_col in ('||
193 ' SELECT column_name'||
194 ' FROM bsc_db_tables_cols'||
195 ' WHERE table_name = :4 AND column_type = :5)'; */
196
197 cursor c_mn_rels(pRelnType number,pTableName varchar2, pColType varchar2,pTableName2 varchar2, pColType2 varchar2) is
198 SELECT d1.level_view_name as p1_table, d1.level_pk_col as p1_pk_col,
199 d2.level_view_name as p2_table, d2.level_pk_col as p2_pk_col,
200 r.relation_col as rel_table
201 FROM bsc_sys_dim_level_rels r, bsc_sys_dim_levels_b d1,
202 bsc_sys_dim_levels_b d2
203 WHERE r.relation_type = pRelnType
204 AND r.dim_level_id = (SELECT min(r2.dim_level_id)
205 FROM bsc_sys_dim_level_rels r2
206 WHERE r.relation_col = r2.relation_col)
207 AND r.dim_level_id = d1.dim_level_id
208 AND r.parent_dim_level_id = d2.dim_level_id
209 AND d1.level_pk_col in ( SELECT column_name
210 FROM bsc_db_tables_cols
211 WHERE table_name = pTableName
212 AND column_type = pColType) AND
213 d2.level_pk_col in (
214 SELECT column_name
215 FROM bsc_db_tables_cols
216 WHERE table_name = pTableName2 AND column_type = pColType2);
217
218
219 h_p1_table bsc_sys_dim_levels_b.level_table_name%TYPE;
220 h_p1_pk_col bsc_db_tables_cols.column_name%TYPE;
221 h_p2_table bsc_sys_dim_levels_b.level_table_name%TYPE;
222 h_p2_pk_col bsc_db_tables_cols.column_name%TYPE;
223 h_rel_table bsc_sys_dim_levels_b.level_table_name%TYPE;
224
225
226 -- Bind Var. fix for POSCO
227 l_bind_vars_values BSC_UPDATE_UTIL.t_array_of_number;
228 l_num_bind_vars NUMBER;
229
230 h_num_rows NUMBER;
231
232 h_calendar_source VARCHAR2(20);
233
234 h_table_for_precalc_kpi BOOLEAN;
235
236 h_bind_vars_values BSC_UPDATE_UTIL.t_array_of_varchar2;
237 h_num_bind_vars NUMBER;
238
239 BEGIN
240
241 h_column_type_p := 'P';
242 l_num_bind_vars := 0;
243 h_calendar_source := NULL;
244 h_table_for_precalc_kpi := Is_Table_For_PreCalc_Kpi(x_input_table);
245
246 -- Get Target_Flag of the table
247 h_target_flag := BSC_UPDATE_UTIL.Get_Table_Target_Flag(x_input_table);
248
249 -- Delete the current invalid codes of input table
250 DELETE FROM bsc_db_validation
251 WHERE input_table_name = x_input_table;
252
253 -- Validate codes for each key column
254 -- OPEN c_dim_cols FOR c_dim_cols_sql USING x_input_table, h_column_type_p;
255
256 OPEN c_dim_cols (x_input_table, h_column_type_p) ;
257 FETCH c_dim_cols INTO h_column_name, h_level_table_name, h_level_short_name, h_level_source;
258 WHILE c_dim_cols%FOUND LOOP
259
260 -- BSC-BIS-DIMENSIONS: If the dimension is a DBI dimension and it is materialized and
261 -- the table exists then we validate against the table created in BSC to materialize
262 -- the DBI dimension. It has USER_CODE column. Note that is only in MV Architecture.
263
264 IF BSC_APPS.bsc_mv THEN
265 IF (h_level_source = 'PMF') AND (h_level_short_name IS NOT NULL) THEN
266 BSC_UPDATE_DIM.Get_Dbi_Dim_Data(h_level_short_name, h_dbi_dim_data);
267 IF (h_dbi_dim_data.short_name IS NOT NULL) AND
268 (h_dbi_dim_data.table_name IS NOT NULL) AND
269 (h_dbi_dim_data.materialized='YES') THEN
270 IF BSC_APPS.Table_Exists(h_dbi_dim_data.table_name) THEN
271 h_level_table_name := h_dbi_dim_data.table_name;
272 END IF;
273 END IF;
274 END IF;
275 END IF;
276
277 -- Insert the invalid codes for the key column h_coumn_name
278 -- into bsc_db_validation table
279 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
280 'SELECT DISTINCT '||
281 ':1, :2, t.'||h_column_name||' '||
282 'FROM '||x_input_table||' t, '||h_level_table_name||' d '||
283 'WHERE t.'||h_column_name||' = d.user_code (+) AND d.user_code IS NULL';
284 h_bind_vars_values.delete;
285 h_bind_vars_values(1) := x_input_table;
286 h_bind_vars_values(2) := h_column_name;
287 h_num_bind_vars := 2;
288 -- Validate for zero codes: If the input table is for non-precalculated indicator
289 -- and the corresponding code is '0' then it is an invalid code.
290 IF NOT h_table_for_precalc_kpi THEN
291 h_sql := h_sql||' UNION '||
292 'SELECT DISTINCT '||
293 ':3, :4, t.'||h_column_name||' '||
294 'FROM '||x_input_table||' t, '||h_level_table_name||' d '||
295 'WHERE t.'||h_column_name||' = d.user_code AND d.code = :5';
296 h_bind_vars_values(3) := x_input_table;
297 h_bind_vars_values(4) := h_column_name;
298 h_bind_vars_values(5) := '0';
299 h_num_bind_vars := 5;
300 END IF;
301 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,h_num_bind_vars);
302 commit;
303
304 FETCH c_dim_cols INTO h_column_name, h_level_table_name, h_level_short_name, h_level_source;
305 END LOOP;
306 CLOSE c_dim_cols;
307
308 -- Validate mn relations
309 -- OPEN c_mn_rels FOR c_mn_rels_sql USING 2, x_input_table, h_column_type_p, x_input_table, h_column_type_p;
310 OPEN c_mn_rels (2, x_input_table, h_column_type_p, x_input_table, h_column_type_p);
311 FETCH c_mn_rels INTO h_p1_table, h_p1_pk_col, h_p2_table, h_p2_pk_col, h_rel_table;
312 WHILE c_mn_rels%FOUND LOOP
313 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
314 ' SELECT DISTINCT :1, :2, '||
315 ' t.'||h_p1_pk_col||'||'', ''||t.'||h_p2_pk_col||' '||
316 ' FROM '||x_input_table||' t '||
317 ' WHERE ('||h_p1_pk_col||', '||h_p2_pk_col||') NOT IN ( '||
318 ' SELECT p1.user_code, p2.user_code '||
319 ' FROM '||h_p1_table||' p1, '||h_p2_table||' p2, '||h_rel_table||' r '||
320 ' WHERE r.'||h_p1_pk_col||' = p1.code AND r.'||h_p2_pk_col||' = p2.code)';
321 h_bind_vars_values.delete;
322 h_bind_vars_values(1) := x_input_table;
323 h_bind_vars_values(2) := h_p1_pk_col||', '||h_p2_pk_col;
324 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,2);
325
326 FETCH c_mn_rels INTO h_p1_table, h_p1_pk_col, h_p2_table, h_p2_pk_col, h_rel_table;
327 END LOOP;
328 CLOSE c_mn_rels;
329
330 -- Validate periods
331
332 -- BSC-BIS-DIMENSIONS: If the periodicity of the input table is from BIS, then the
333 -- input table has a column called TIME_FK instead of YEAR+PERIOD.
334
335 -- Get table periodicity
336 h_periodicity := BSC_UPDATE_UTIL.Get_Table_Periodicity(x_input_table);
337 IF h_periodicity IS NULL THEN
338 RAISE e_unexpected_error;
339 END IF;
340
341 h_calendar_id := BSC_UPDATE_UTIL.Get_Calendar_Id(h_periodicity);
342 h_calendar_source := BSC_UPDATE_UTIL.Get_Calendar_Source(h_calendar_id);
343 h_periodicity_type := BSC_UPDATE_UTIL.Get_Periodicity_Type(h_periodicity);
344
345 IF h_calendar_source = 'BSC' THEN
346 -- Get period column name and subperiod column name for that periodicity
347 IF NOT BSC_UPDATE_UTIL.Get_Period_Cols_Names(h_periodicity, h_period_col_name, h_subperiod_col_name) THEN
348 RAISE e_unexpected_error;
349 END IF;
350
351 -- Annually
352 IF h_periodicity_type = 1 THEN
353 l_bind_vars_values.delete;
354 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
355 'SELECT DISTINCT :1, :2, '||
356 't.year||'', ''||t.'||h_period_col_name||' '||
357 'FROM '||x_input_table||' t, bsc_db_calendar d '||
358 'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
359 ' AND (d.year IS NULL OR d.calendar_id IS NULL) '||
360 'UNION '||
361 'SELECT DISTINCT :4, :5, '||
362 't.year||'', ''||t.'||h_period_col_name||' '||
363 'FROM '||x_input_table||' t '||
364 'WHERE t.'||h_period_col_name||' <> :6';
365 h_bind_vars_values.delete;
366 h_bind_vars_values(1) := x_input_table;
367 h_bind_vars_values(2) := 'YEAR, '||h_period_col_name;
368 h_bind_vars_values(3) := h_calendar_id;
369 h_bind_vars_values(4) := x_input_table;
370 h_bind_vars_values(5) := 'YEAR, '||h_period_col_name;
371 h_bind_vars_values(6) := 0;
372 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,6);
373
374 -- Month Week
375 ELSIF h_periodicity_type = 11 THEN
376 l_bind_vars_values.delete;
377 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
378 'SELECT DISTINCT :1, :2, '||
379 't.year||'', ''||t.'||h_period_col_name||'||'', ''||t.'||h_subperiod_col_name||' '||
380 'FROM '||x_input_table||' t, bsc_db_week_maps d '||
381 'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
382 'AND t.'||h_period_col_name||' = d.month (+) '||
383 'AND t.'||h_subperiod_col_name||' = d.week (+) '||
384 'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.month IS NULL OR d.week IS NULL)';
385 h_bind_vars_values.delete;
386 h_bind_vars_values(1) := x_input_table;
387 h_bind_vars_values(2) := 'YEAR, '||h_period_col_name||', '||h_subperiod_col_name;
388 h_bind_vars_values(3) := h_calendar_id;
389 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
390
391 -- Month Day
392 ELSIF h_periodicity_type = 12 THEN
393 l_bind_vars_values.delete;
394 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
395 'SELECT DISTINCT :1, :2, '||
396 't.year||'', ''||t.'||h_period_col_name||'||'', ''||t.'||h_subperiod_col_name||' '||
397 'FROM '||x_input_table||' t, bsc_db_calendar d '||
398 'WHERE d.calendar_id (+) = :3'|| ' AND t.year = d.year (+) '||
399 'AND t.'||h_period_col_name||' = d.month (+) '||
400 'AND t.'||h_subperiod_col_name||' = d.day30 (+) '||
401 'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.month IS NULL OR d.day30 IS NULL)';
402 h_bind_vars_values.delete;
403 h_bind_vars_values(1) := x_input_table;
404 h_bind_vars_values(2) := 'YEAR, '||h_period_col_name||', '||h_subperiod_col_name;
405 h_bind_vars_values(3) := h_calendar_id;
406 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
407
408 -- Other periodicities: Monthly, Quarterly, Custom periodicities, etc
409 ELSE
410 l_bind_vars_values.delete;
411 h_db_calendar_col_name := BSC_UPDATE_UTIL.Get_Calendar_Table_Col_Name(h_periodicity);
412 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
413 'SELECT DISTINCT :1, :2, '||
414 't.year||'', ''||t.'||h_period_col_name||' '||
415 'FROM '||x_input_table||' t, bsc_db_calendar d '||
416 'WHERE d.calendar_id (+) = :3'||' AND t.year = d.year (+) '||
417 'AND t.'||h_period_col_name||' = d.'||h_db_calendar_col_name||' (+) '||
418 'AND (d.calendar_id IS NULL OR d.year IS NULL OR d.'||h_db_calendar_col_name||' IS NULL)';
419 h_bind_vars_values.delete;
420 h_bind_vars_values(1) := x_input_table;
421 h_bind_vars_values(2) := 'YEAR, '||h_period_col_name;
422 h_bind_vars_values(3) := h_calendar_id;
423 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
424 END IF;
425 ELSE
426 -- BIS Calendar
427 -- The input table has a column called TIME_FK. We need to validate against BSC_SYS_PERIODS.TIME_FK
428
429 IF h_periodicity_type = 9 THEN
430 -- If the periodicity is Daily the TIME_FK column is of type DATE.
431 -- The format in BSC_SYS_PERIODS.TIME_FK is always MM/DD/YYYY
432 l_bind_vars_values.delete;
433 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
434 'SELECT DISTINCT :1, :2, TO_CHAR(time_fk) '||
435 'FROM '||x_input_table||' '||
436 'WHERE TRUNC(time_fk) NOT IN ( '||
437 ' SELECT TRUNC(TO_DATE(time_fk,:3)) '||
438 ' FROM bsc_sys_periods '||
439 ' WHERE periodicity_id = :4 '||
440 ')';
441 h_bind_vars_values.delete;
442 h_bind_vars_values(1) := x_input_table;
443 h_bind_vars_values(2) := 'TIME_FK';
444 h_bind_vars_values(3) := 'MM/DD/YYYY';
445 h_bind_vars_values(4) := h_periodicity;
446 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,4);
447 ELSE
448 -- Other periodicity, TIME_FK is VARCHAR2
449 l_bind_vars_values.delete;
450 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
451 'SELECT DISTINCT :1, :2, i.time_fk '||
452 'FROM '||x_input_table||' i, bsc_sys_periods p '||
453 'WHERE p.periodicity_id (+) = :3 AND i.time_fk = p.time_fk (+) '||
454 'AND (p.periodicity_id IS NULL OR p.time_fk IS NULL)';
455 h_bind_vars_values.delete;
456 h_bind_vars_values(1) := x_input_table;
457 h_bind_vars_values(2) := 'TIME_FK';
458 h_bind_vars_values(3) := h_periodicity;
459 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
460 END IF;
461 END IF;
462
463 -- Validate type.
464 -- Type should be registered in BSC_SYS_BENCHMARKS_B.DATA_TYPE
465 -- Fix bug#4293829: Type 90 is used for user projection. We should allow this value
466 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
467 'SELECT DISTINCT :1, :2, TO_CHAR(TYPE) '||
468 'FROM '||x_input_table||' i, bsc_sys_benchmarks_b b '||
469 'WHERE i.type = b.data_type (+) and i.type <> :3 and b.data_type is null';
470 h_bind_vars_values.delete;
471 h_bind_vars_values(1) := x_input_table;
472 h_bind_vars_values(2) := 'TYPE';
473 h_bind_vars_values(3) := 90;
474 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
475
476 -- User should not enter actuals in target only tables
477 IF h_target_flag = 1 THEN
478 l_bind_vars_values.delete;
479 h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code) '||
480 'SELECT DISTINCT :1, :2, TO_CHAR(TYPE) '||
481 'FROM '||x_input_table||' '||
482 'WHERE TYPE = :3';
483 h_bind_vars_values.delete;
484 h_bind_vars_values(1) := x_input_table;
485 h_bind_vars_values(2) := 'TYPE';
486 h_bind_vars_values(3) := 0;
487 BSC_UPDATE_UTIL.Execute_Immediate(h_sql,h_bind_vars_values,3);
488 END IF;
489
490 COMMIT;
491
492 -- Check if there were invalid codes
493 SELECT COUNT(*)
494 INTO h_num_rows
495 FROM BSC_DB_VALIDATION
496 WHERE ROWNUM < 2 AND INPUT_TABLE_NAME = x_input_table;
497
498 IF h_num_rows > 0 THEN
499 h_invalid := TRUE;
500 ELSE
501 h_invalid := FALSE;
502 END IF;
503
504 RETURN NOT h_invalid;
505
506 EXCEPTION
507 WHEN e_unexpected_error THEN
508 BSC_MESSAGE.Add(x_message => BSC_UPDATE_UTIL.Get_Message('BSC_ITABLE_VALID_FAILED'),
509 x_source => 'BSC_UPDATE_VAL.Validate_Codes');
510 RETURN NULL;
511
512 WHEN OTHERS THEN
513 BSC_MESSAGE.Add(x_message => SQLERRM,
514 x_source => 'BSC_UPDATE_VAL.Validate_Codes');
515 RETURN NULL;
516 END Validate_Codes;
517
518 --LOCKING: new function
519 /*===========================================================================+
520 | FUNCTION Validate_Codes_AT
521 +============================================================================*/
522 FUNCTION Validate_Codes_AT(
523 x_input_table IN VARCHAR2
524 ) RETURN BOOLEAN IS
525 PRAGMA AUTONOMOUS_TRANSACTION;
526 h_b BOOLEAN;
527 BEGIN
528 h_b := Validate_Codes(x_input_table);
529 commit; -- all autonomous transaction needs to commit
530 RETURN h_b;
531 END Validate_Codes_AT;
532
533
534 END BSC_UPDATE_VAL;