[Home] [Help]
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;
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 '||
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';
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 ( '||
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 (+) '||
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 (+) '||
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 (+) '||
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 (+) '||
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)) '||
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)';
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;
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;
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;