[Home] [Help]
4912: l_num_bind_vars := 0;
4913:
4914: -- Delete the current invalid codes of input table
4915: /*
4916: h_sql := 'DELETE FROM bsc_db_validation'||
4917: ' WHERE input_table_name = :1';
4918: EXECUTE IMMEDIATE h_sql USING x_input_table;
4919: */
4920: DELETE FROM bsc_db_validation
4916: h_sql := 'DELETE FROM bsc_db_validation'||
4917: ' WHERE input_table_name = :1';
4918: EXECUTE IMMEDIATE h_sql USING x_input_table;
4919: */
4920: DELETE FROM bsc_db_validation
4921: WHERE input_table_name = x_input_table;
4922:
4923: -- Get type of dimension table
4924: h_dim_table_type := Get_Dim_Table_Type(x_dim_table);
4951:
4952: -- BSC-BIS-DIMENSIONS: No need to change here. We only support to load input tables
4953: -- for BSC dimensions. They always have VARCHAR2 in USER_CODE.
4954:
4955: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4956: SELECT DISTINCT :1, ''USER_CODE'',
4957: NVL(USER_CODE,:2)
4958: FROM '||x_input_table||'
4959: WHERE NVL(USER_CODE, ''0'') = ''0''';
4963: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,2);
4964:
4965: -- Validate NAME
4966: -- Must be not null
4967: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4968: SELECT DISTINCT :1, ''NAME'', :2
4969: FROM '||x_input_table||'
4970: WHERE NAME IS NULL';
4971: l_bind_vars_values.delete;
4975:
4976: -- NAME should not be duplicated
4977: IF h_loading_mode = 1 THEN
4978: -- Overwrite
4979: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4980: SELECT DISTINCT :1, ''NAME'', name
4981: FROM '||x_input_table||'
4982: WHERE name IS NOT NULL
4983: GROUP BY name
4986: l_bind_vars_values(1) := x_input_table;
4987: BSC_UPDATE_UTIL.Execute_Immediate(h_sql,l_bind_vars_values,1);
4988: ELSE
4989: -- Add/Update
4990: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
4991: SELECT DISTINCT :1, :2, name
4992: FROM (SELECT user_code, name
4993: FROM '||x_input_table||'
4994: UNION
5016:
5017: -- BSC-BIS-DIMENSIONS Note: A BSC dimension could have a BIS dimension as parent.
5018: -- So the parent key can be NUMBER/VARCHAR2. The following query will support both cases.
5019:
5020: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
5021: SELECT DISTINCT :1, :2,
5022: NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), :3)
5023: FROM '||x_input_table||'
5024: WHERE NVL(TO_CHAR('||h_parent_keys(h_i)||'_USR), ''0'') NOT IN (
5034: IF h_dim_table_type = DIM_TABLE_TYPE_1N THEN
5035: h_num_aux_fields := Get_Aux_Fields_Dim_Table(x_dim_table, h_aux_fields);
5036: FOR h_i IN 1 .. h_num_aux_fields LOOP
5037: -- Must be not null
5038: h_sql := 'INSERT INTO bsc_db_validation (input_table_name, column_name, invalid_code)
5039: SELECT DISTINCT :1, :2, :3
5040: FROM '||x_input_table||'
5041: WHERE '||h_aux_fields(h_i)||' IS NULL';
5042: l_bind_vars_values.delete;
5048: END IF;
5049:
5050: -- Check if there were invalid codes
5051: /*
5052: h_sql := 'SELECT COUNT(*) FROM BSC_DB_VALIDATION'||
5053: ' WHERE ROWNUM < :1 AND INPUT_TABLE_NAME = :2';
5054: OPEN h_cursor FOR h_sql USING 2, x_input_table;
5055: FETCH h_cursor INTO h_num_rows;
5056: CLOSE h_cursor;
5056: CLOSE h_cursor;
5057: */
5058: SELECT COUNT(*)
5059: INTO h_num_rows
5060: FROM BSC_DB_VALIDATION
5061: WHERE ROWNUM < 2 AND INPUT_TABLE_NAME = x_input_table;
5062:
5063: IF h_num_rows > 0 THEN
5064: h_invalid := TRUE;