-----------------------------------------------------------------
--                                                             --
-----------------------------------------------------------------
--
--      scramble_data_p.sql - 
--
--
CREATE OR REPLACE PROCEDURE SCRAMBLE_DATA (p_scramble_level IN number)
AS 

    --Local Variables
    l_count                 NUMBER       := 0 ;
    l_first_time            VARCHAR2(1) := 'Y';
    l_disable_triggers      VARCHAR2(1) := 'N';
    l_last_table            VARCHAR2(30);
    l_last_owner            VARCHAR2(30);
    l_program_location      VARCHAR2(200);
    l_new_value             VARCHAR2(200);
    l_sql_stmt              VARCHAR2(8000);
    l_disable_triggers_stmt VARCHAR2(8000) := '';
    l_enable_triggers_stmt  VARCHAR2(8000) := '';

    --Cursor to select columns to scramble with where clause
    CURSOR cur_scramble_cols_where (p_level IN number) IS
        SELECT table_owner, table_name, column_name, where_clause, new_value, update_type, disable_triggers
        FROM   hrapps.slc_pyinf_cols_to_scramble
	WHERE  scramble_level <= p_level
	 AND  (table_owner, table_name, column_name)
	      NOT IN (SELECT table_owner, table_name, column_name from scramble_log)
	 AND where_clause is not null
	 AND nvl(update_type,'UPDATE') != 'TRUNCATE'
	ORDER BY table_owner, table_name
        ;
    
    --Cursor to select columns to scramble no where clause
    CURSOR cur_scramble_cols (p_level IN number) IS
        SELECT table_owner, table_name, column_name, where_clause, new_value, update_type, disable_triggers
        FROM   hrapps.slc_pyinf_cols_to_scramble
	WHERE  scramble_level <= p_level
	 AND  (table_owner, table_name, column_name)
	      NOT IN (SELECT table_owner, table_name, column_name from scramble_log)
	 AND where_clause is null
	 AND nvl(update_type,'UPDATE') != 'TRUNCATE'
	ORDER BY table_owner, table_name
        ;
    
    --Cursor to select tables to truncate
    CURSOR cur_scramble_trunc (p_level IN number) IS
        SELECT distinct table_owner, table_name, update_type
        FROM   hrapps.slc_pyinf_cols_to_scramble
	WHERE  scramble_level <= p_level
	 AND  (table_owner, table_name)
	      NOT IN (SELECT table_owner, table_name from scramble_log WHERE column_name = 'TRUNCATE')
	 AND update_type = 'TRUNCATE'
	ORDER BY table_owner, table_name
        ;
    
BEGIN

    l_count := 0;
    l_program_location := 'UPDATE SCRAMBLE COLS';
    l_last_table := 'ZZZZZZZZZZZZZ';
    l_last_owner := 'ZZZZZZZZZZZZZ';

    FOR cur_scramble_trunc_rec IN cur_scramble_trunc (p_scramble_level) LOOP


        dbms_output.put_line ('Truncating: '||cur_scramble_trunc_rec.table_owner||
			      '.'||cur_scramble_trunc_rec.table_name);
        l_sql_stmt := 'TRUNCATE TABLE '||cur_scramble_trunc_rec.table_owner||'.'||cur_scramble_trunc_rec.table_name;

	EXECUTE IMMEDIATE l_sql_stmt;

	insert into scramble_log values (cur_scramble_trunc_rec.table_owner,
				  cur_scramble_trunc_rec.table_name,
				  'TRUNCATE',
				  sysdate);

	COMMIT;
        dbms_output.put_line ('Truncate Complete!');

    END LOOP;
    

    FOR cur_scramble_cols_rec IN cur_scramble_cols (p_scramble_level) LOOP

	IF (l_last_table != cur_scramble_cols_rec.table_name OR l_last_owner != cur_scramble_cols_rec.table_owner)
	THEN
	    IF l_first_time != 'Y' 
	    THEN
	        /******************************************************/
	        /* Check For Trigger Disable Flag Before Execute      */
	        /******************************************************/
		IF l_disable_triggers = 'Y'
		THEN
                   dbms_output.put_line ('Disable Triggers');
	           EXECUTE IMMEDIATE l_disable_triggers_stmt;
		END IF;

	        EXECUTE IMMEDIATE l_sql_stmt;
	        COMMIT;
                dbms_output.put_line ('Update Complete');

		IF l_disable_triggers = 'Y'
		THEN
                   dbms_output.put_line ('Enable Triggers');
	           EXECUTE IMMEDIATE l_enable_triggers_stmt;
		END IF;

	    ELSE
	       l_first_time := 'N';
	       l_disable_triggers := 'N';
               l_disable_triggers_stmt := '';
               l_enable_triggers_stmt := '';
	    END IF;

	    l_last_table := cur_scramble_cols_rec.table_name;
	    l_last_owner := cur_scramble_cols_rec.table_owner;

	    /******************************************************/
	    /* Check For Trigger Disable Flag                     */
	    /******************************************************/
	    IF cur_scramble_cols_rec.disable_triggers = 'Y'
	    THEN
	       l_disable_triggers := 'Y';
               l_disable_triggers_stmt := 'ALTER TABLE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		      ' DISABLE ALL TRIGGERS';
               l_enable_triggers_stmt := 'ALTER TABLE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		      ' ENABLE ALL TRIGGERS';
	    END IF;

	    /******************************************************/
	    /* Check For Special Unique Update                    */
	    /******************************************************/
	    IF cur_scramble_cols_rec.update_type = 'UNIQUE'
	    THEN
                 l_sql_stmt := 'UPDATE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		    ' SET '||cur_scramble_cols_rec.column_name||' = '''||cur_scramble_cols_rec.new_value||'''||to_char(rownum)';
	    ELSE
                 l_sql_stmt := 'UPDATE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		    ' SET '||cur_scramble_cols_rec.column_name||' = '''||cur_scramble_cols_rec.new_value||'''';
	    END IF;

	ELSE
	    IF cur_scramble_cols_rec.update_type = 'UNIQUE'
	    THEN
                 l_sql_stmt := l_sql_stmt||
		      ','||cur_scramble_cols_rec.column_name|| ' = '''||cur_scramble_cols_rec.new_value||'''||to_char(rownum)';
	    ELSE
                 l_sql_stmt := l_sql_stmt||
		      ','||cur_scramble_cols_rec.column_name|| ' = '''||cur_scramble_cols_rec.new_value||'''';
	    END IF;
	END IF;


        dbms_output.put_line ('Updating: '||cur_scramble_cols_rec.table_owner||
			      '.'||cur_scramble_cols_rec.table_name||'.'||
			      cur_scramble_cols_rec.column_name);

	insert into scramble_log values (cur_scramble_cols_rec.table_owner,
				  cur_scramble_cols_rec.table_name,
				  cur_scramble_cols_rec.column_name,
				  sysdate);
    END LOOP;

    IF l_first_time != 'Y' THEN

	IF l_disable_triggers = 'Y'
	THEN
           dbms_output.put_line ('Disable Triggers');
	   EXECUTE IMMEDIATE l_disable_triggers_stmt;
	END IF;

        EXECUTE IMMEDIATE l_sql_stmt;
        COMMIT;
        dbms_output.put_line ('Update Complete');

	IF l_disable_triggers = 'Y'
	THEN
           dbms_output.put_line ('Enable Triggers');
           EXECUTE IMMEDIATE l_enable_triggers_stmt;
	END IF;


    END IF;

    FOR cur_scramble_cols_rec IN cur_scramble_cols_where (p_scramble_level) LOOP


        dbms_output.put_line ('Updating: '||cur_scramble_cols_rec.table_owner||
			      '.'||cur_scramble_cols_rec.table_name||'.'||
			      cur_scramble_cols_rec.column_name);

	/******************************************************/
	/* Check For Trigger Disable Flag Before Execute      */
	/******************************************************/
	IF cur_scramble_cols_rec.disable_triggers = 'Y'
	    THEN
               dbms_output.put_line ('Disable Triggers');
               l_disable_triggers_stmt := 'ALTER TABLE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		      ' DISABLE ALL TRIGGERS';
	       EXECUTE IMMEDIATE l_disable_triggers_stmt;
	END IF;

	IF cur_scramble_cols_rec.update_type = 'UNIQUE'
	THEN
                 l_sql_stmt := 'UPDATE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		    ' SET '||cur_scramble_cols_rec.column_name||' = '''||cur_scramble_cols_rec.new_value||'''||to_char(rownum)';
	ELSE
                 l_sql_stmt := 'UPDATE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		    ' SET '||cur_scramble_cols_rec.column_name||' = '''||cur_scramble_cols_rec.new_value||'''';
        END IF;

	IF cur_scramble_cols_rec.where_clause is not null
	THEN
	   l_sql_stmt := l_sql_stmt|| ' WHERE '||cur_scramble_cols_rec.where_clause;
	END IF;

	EXECUTE IMMEDIATE l_sql_stmt;

	insert into scramble_log values (cur_scramble_cols_rec.table_owner,
				  cur_scramble_cols_rec.table_name,
				  cur_scramble_cols_rec.column_name,
				  sysdate);
	COMMIT;
        dbms_output.put_line ('Update Complete!');

	/******************************************************/
	/* Check If Re Enable Triggers is needed              */
	/******************************************************/
	IF cur_scramble_cols_rec.disable_triggers = 'Y'
	THEN
               dbms_output.put_line ('Enable Triggers');
               l_enable_triggers_stmt := 'ALTER TABLE '||cur_scramble_cols_rec.table_owner||'.'||cur_scramble_cols_rec.table_name||
		      ' ENABLE ALL TRIGGERS';

	       EXECUTE IMMEDIATE l_enable_triggers_stmt;
	END IF;

    END LOOP;
    
    
EXCEPTION
    WHEN others THEN
    dbms_output.put_line ('Error!! '||substr(SQLERRM,1,200)||' at location '||l_program_location);
    raise;
END;
/