create or replace package pack_auditfunctionality is
  /*pack_auditfunctionality version 0.9*/

  procedure create_a_table(fp_tablename in varchar2, fp_audit_tablename in varchar2);
  procedure generate_a_trigger(fp_tablename in varchar2, fp_audit_tablename in varchar2);
  procedure install_audit_functionality;
  procedure check_audit_functionality;

end pack_auditfunctionality;
/
create or replace package body pack_auditfunctionality is
  ---internal procedure(s)
  procedure write_log(fp_message in varchar2, fp_priority in number) is
    gv_systemuserid systemuser.systemuser_id%type := null;
    gv_plantid      plant.plant_id%type := null;
  begin
    /* Logging: write message in ABSLOG with these priorities for fp_priority:
    1 = Message
    2 = Warning
    3 = Error */
    abslogwrite.abslog_write(5, 'pack_auditfunctionality', gv_plantid, fp_priority, fp_message,
                             gv_systemuserid);
    begin
      dbms_output.put_line(fp_message);
    exception
      when others then
        null;
    end;
  end write_log;

  ---external procedure(s)
  procedure create_a_table(fp_tablename in varchar2, fp_audit_tablename in varchar2) is
  begin
    --create a_table based on fp_tablename with name fp_audit_tablename
    execute immediate 'CREATE TABLE ' || upper(fp_audit_tablename) || ' AS SELECT * FROM ' ||
                      upper(fp_tablename);
    --add columns to the a-table
    execute immediate 'ALTER TABLE ' || upper(fp_audit_tablename) ||
                      ' ADD A_ACTION VARCHAR2(1) DEFAULT ''I'' NOT NULL';
    execute immediate 'ALTER TABLE ' || upper(fp_audit_tablename) ||
                      ' ADD A_TIMESTAMP DATE DEFAULT SYSDATE NOT NULL';
    execute immediate 'ALTER TABLE ' || upper(fp_audit_tablename) ||
                      ' ADD A_CLIENT_IDENTIFIER VARCHAR2(64)';
    --write log
    write_log('Created A_' || substr(upper(fp_tablename), 1, 24) || ' For ' || upper(fp_tablename),
              1);
  exception
    --exception handling
    when no_data_found then
      begin
        write_log('ERROR NO DATA FOUND; ' || upper(fp_tablename) || ' create_a_table', 3);
        raise;
      end;
    when others then
      begin
        write_log('ERROR OTHERS; ' || sqlerrm || ' ' || upper(fp_tablename) || ' create_a_table', 3);
        raise;
      end;
  end create_a_table;

  procedure generate_a_trigger(fp_tablename in varchar2, fp_audit_tablename in varchar2) is
    ---internal variables
    gv_columnlist   clob;
    gv_newlist      clob;
    gv_oldlist      clob;
    gv_counter      number := 0;
    gv_createscript clob;
  begin
    --generate_a_trigger on fp_tablename to fill fp_audit_tablename
    --loop columns for the table
    for x in (select t.column_name
              from   user_tab_columns t
              where  t.table_name = upper(upper(fp_tablename))
              order  by t.column_id) loop
      begin
        --build colrecent objects umn list
        gv_columnlist := gv_columnlist || ',' || x.column_name;
        gv_newlist    := gv_newlist || ',:NEW.' || x.column_name;
        gv_oldlist    := gv_oldlist || ',:OLD.' || x.column_name;
        gv_counter    := gv_counter + 1;
      end;
    end loop;
    if gv_counter = 0 then
      begin
        --wrong table name
        write_log('create a-trigger for table name ' || upper(fp_tablename) ||
                  ' no columns found!?', 2);
      end;
    else
      begin
        --remove first comma
        gv_columnlist := ltrim(gv_columnlist, ',');
        gv_newlist    := ltrim(gv_newlist, ',');
        gv_oldlist    := ltrim(gv_oldlist, ',');
        --build create trigger script
        gv_createscript := 'CREATE OR REPLACE TRIGGER TRG_A_' || substr(upper(fp_tablename), 1, 24) || '
  AFTER INSERT OR UPDATE OR DELETE ON ' || upper(fp_tablename) || '
  FOR EACH ROW
DECLARE
  LV_A_CLIENT_IDT VARCHAR2(64);
BEGIN
  SELECT NVL(SYS_CONTEXT(''USERENV'', ''CLIENT_IDENTIFIER''),SYS_CONTEXT(''USERENV'', ''HOST'')) INTO LV_A_CLIENT_IDT FROM DUAL;
  IF UPDATING THEN
    BEGIN
      INSERT INTO ' || upper(fp_audit_tablename) || '
        (' || gv_columnlist || ', A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (' || gv_newlist || ', ''U'', LV_A_CLIENT_IDT);
    END;
  ELSIF INSERTING THEN
    BEGIN
      INSERT INTO ' || upper(fp_audit_tablename) || '
        (' || gv_columnlist || ', A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (' || gv_newlist || ', ''I'', LV_A_CLIENT_IDT);
    END;
  ELSIF DELETING THEN
    BEGIN
      INSERT INTO ' || upper(fp_audit_tablename) || '
        (' || gv_columnlist || ', A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (' || gv_oldlist || ', ''D'', LV_A_CLIENT_IDT);
    END;
  END IF;
END TRG_A_' || substr(upper(fp_tablename), 1, 24) || ';';
        --create trigger
        execute immediate gv_createscript;
        --write log
        write_log('Created/updated TRG_A_' || substr(upper(fp_tablename), 1, 24) || ' for ' ||
                  upper(fp_tablename), 1);
      end;
    end if;
  exception
    --exception handling
    when no_data_found then
      begin
        write_log('Error No Data Found fp_tablename ' || upper(fp_tablename) ||
                  ' generate_a_trigger TRG_A_' || substr(upper(fp_tablename), 1, 24), 3);
        raise;
      end;
    when others then
      begin
        write_log('Error Others ' || sqlerrm || ' fp_tablename ' || upper(fp_tablename) ||
                  ' generate_a_trigger TRG_A_' || substr(upper(fp_tablename), 1, 24), 3);
        raise;
      end;
  end generate_a_trigger;

  procedure install_audit_functionality is
  begin
    --install_audit_functionality
    for x in (select a.tablename, a.a_tablename from a_tablelist a where a.processed = 0) loop
      begin
        --create_a_table
        pack_auditfunctionality.create_a_table(x.tablename, x.a_tablename);
        --generate_a_trigger
        pack_auditfunctionality.generate_a_trigger(x.tablename, x.a_tablename);
        --update a_tablelist
        update a_tablelist a set a.processed = 1 where a.tablename = x.tablename;
        --commit
        commit;
      exception
        when others then
          begin
            write_log('Table ' || x.tablename || ' Audit table ' || x.a_tablename || ' got error ' ||
                      sqlerrm || ' install_audit_functionality', 3);
          end;
      end;
    end loop; --x
    write_log('Ready', 1);
  exception
    --exception handling
    when others then
      begin
        rollback;
        write_log('Error Others ' || sqlerrm || ' install_audit_functionality', 3);
      end;
  end install_audit_functionality;

  procedure check_audit_functionality is
    lv_errorcount number := 0;
  
    procedure columns_added(fp_tablename in varchar2, fp_audit_tablename in varchar2) is
      procedure add_column(fp_tablename      in varchar2,
                           fp_column_name    in varchar2,
                           fp_data_type      in varchar2,
                           fp_data_length    in number,
                           fp_data_precision in number,
                           fp_data_scale     in number) is
      begin
        --add missing column fp_column_name to the table fp_tablename
        case fp_data_type
          when 'NUMBER' then
            begin
              if fp_data_precision is null and fp_data_scale is null then
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' ||
                                  fp_column_name || ' NUMBER';
              else
                if fp_data_scale is null then
                  execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' ||
                                    fp_column_name || ' NUMBER(' || fp_data_precision || ')';
                else
                  execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' ||
                                    fp_column_name || ' NUMBER(' || fp_data_precision || ',' ||
                                    fp_data_scale || ')';
                end if;
              end if;
            end;
          when 'DATE' then
            begin
              execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' || fp_column_name ||
                                ' DATE';
            end;
          else
            begin
              if instr(fp_data_type, 'CHAR') > 0 then
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' ||
                                  fp_column_name || ' ' || fp_data_type || '(' ||
                                  fp_data_length / 4 || ')';
              else
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' ADD ' ||
                                  fp_column_name || ' ' || fp_data_type;
              end if;
            end;
        end case;
        --write log that column is added
        write_log('Missing column ' || fp_column_name || ' type ' || fp_data_type ||
                  ' added to table ' || fp_tablename, 1);
      exception
        when others then
          begin
            --count errors
            lv_errorcount := lv_errorcount + 1;
            --Write in log the missing column or difference
            write_log('Error adding column ' || fp_column_name || ' to ' || fp_tablename || ' ' ||
                      sqlerrm, 3);
          end;
      end add_column;
    begin
      --columns_added
      for z in (select ut.column_name
                from   user_tab_columns ut
                where  ut.table_name = fp_tablename
                minus
                select uta.column_name
                from   user_tab_columns uta
                where  uta.table_name = fp_audit_tablename) loop
        begin
          for y in (select ut.column_name, ut.data_type, ut.data_length, ut.data_precision,
                           ut.data_scale
                    from   user_tab_columns ut
                    where  ut.table_name = fp_tablename
                    and    ut.column_name = z.column_name) loop
            begin
              --add column to table
              add_column(fp_audit_tablename, y.column_name, y.data_type, y.data_length,
                         y.data_precision, y.data_scale);
              begin
                --Recreate the trigger
                pack_auditfunctionality.generate_a_trigger(fp_tablename, fp_audit_tablename);
              exception
                when others then
                  begin
                    --Write in log error update trigger
                    write_log('Error updating TRG_A_' || substr(upper(fp_tablename), 1, 24) ||
                              ' for ' || upper(fp_tablename) || ' because of new column ' ||
                              y.column_name, 3);
                  end;
              end;
            end;
          end loop;
        end;
      end loop; --y
    end columns_added;
  
    procedure columns_lengths(fp_tablename in varchar2, fp_audit_tablename in varchar2) is
      procedure length_column(fp_tablename      in varchar2,
                              fp_column_name    in varchar2,
                              fp_data_type      in varchar2,
                              fp_data_length    in number,
                              fp_data_precision in number,
                              fp_data_scale     in number) is
      begin
        --add missing column fp_column_name to the table fp_tablename
        case fp_data_type
          when 'NUMBER' then
            begin
              if fp_data_precision is null and fp_data_scale is null then
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                  fp_column_name || ' NUMBER';
              else
                if fp_data_scale is null then
                  execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                    fp_column_name || ' NUMBER(' || fp_data_precision || ')';
                else
                  execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                    fp_column_name || ' NUMBER(' || fp_data_precision || ',' ||
                                    fp_data_scale || ')';
                end if;
              end if;
            end;
          when 'DATE' then
            begin
              execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                fp_column_name || ' DATE';
            end;
          else
            begin
              if instr(fp_data_type, 'CHAR') > 0 then
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                  fp_column_name || ' ' || fp_data_type || '(' ||
                                  fp_data_length / 4 || ')';
              else
                execute immediate 'ALTER TABLE ' || upper(fp_tablename) || ' MODIFY ' ||
                                  fp_column_name || ' ' || fp_data_type;
              end if;
            end;
        end case;
        --write log that column is modified
        if instr(fp_data_type, 'CHAR') > 0 then
          write_log('Different column length ' || fp_column_name || ' type ' || fp_data_type || '(' ||
                    fp_data_length / 4 || ') for table ' || fp_tablename, 1);
        else
          write_log('Different column length ' || fp_column_name || ' type ' || fp_data_type || '(' ||
                    fp_data_length || ') for table ' || fp_tablename, 1);
        end if;
      exception
        when others then
          begin
            --count errors
            lv_errorcount := lv_errorcount + 1;
            --Write in log the missing column or difference
            write_log('Error changing column length ' || fp_column_name || ' to ' || fp_tablename || ' ' ||
                      sqlerrm, 3);
          end;
      end length_column;
    begin
      --columns_lengths
      for y in (select ut.column_name, ut.data_type, ut.data_length, ut.data_precision, ut.data_scale
                from   user_tab_columns ut
                where  ut.table_name = fp_tablename
                minus
                select uta.column_name, uta.data_type, uta.data_length, uta.data_precision,
                       uta.data_scale
                from   user_tab_columns uta
                where  uta.table_name = fp_audit_tablename) loop
        begin
          --change length column
          length_column(fp_audit_tablename, y.column_name, y.data_type, y.data_length,
                        y.data_precision, y.data_scale);
        end;
      end loop; --y
    end columns_lengths;
  
    procedure columns_dropped(fp_tablename in varchar2, fp_audit_tablename in varchar2) is
      lv_difference number := 0;
      
      procedure make_null_allowed(fp_audit_tablename in varchar2) is
      begin
        for x in (select ut.column_name
                from   user_tab_columns ut
                where  ut.table_name = fp_audit_tablename
                and    ut.column_name not in ('A_ACTION', 'A_CLIENT_IDENTIFIER', 'A_TIMESTAMP')
                and    ut.NULLABLE = 'N'
                minus
                select uta.column_name
                from   user_tab_columns uta
                where  uta.table_name = fp_tablename
                and    uta.NULLABLE = 'N') loop
        begin
           execute immediate 'ALTER TABLE ' || upper(fp_audit_tablename) || ' MODIFY ' ||
                                  x.column_name || ' NULL';
        end;
        end loop;
      end make_null_allowed;
      
    begin
      --columns_dropped
      for z in (select ut.column_name, ut.data_type, ut.data_length, ut.data_precision, ut.data_scale
                from   user_tab_columns ut
                where  ut.table_name = fp_audit_tablename
                and    ut.column_name not in ('A_ACTION', 'A_CLIENT_IDENTIFIER', 'A_TIMESTAMP')
                minus
                select uta.column_name, uta.data_type, uta.data_length, uta.data_precision,
                       uta.data_scale
                from   user_tab_columns uta
                where  uta.table_name = fp_tablename) loop
        begin
          lv_difference := lv_difference + 1;     
          exit;
        end;
      end loop;
      if lv_difference > 0 then
        begin
          --Recreate the trigger, less columns in real table
          pack_auditfunctionality.generate_a_trigger(fp_tablename, fp_audit_tablename);
          --Set null allowed for dropped columns in real table
          make_null_allowed(fp_audit_tablename);       
        exception
          when others then
            begin
              --Write in log error update trigger
              write_log('Error updating TRG_A_' || substr(upper(fp_tablename), 1, 24) || ' for ' ||
                        upper(fp_tablename) || ' because of columns dropped from ' || fp_tablename,
                        3);
            end;
        end;
      end if;
    end columns_dropped;
  
    procedure finalize is
    begin
      --finalize
      if lv_errorcount = 0 then
        begin
          write_log('All Ok', 1);
        end;
      else
        begin
          write_log(lv_errorcount || ' errors, please check log!', 2);
        end;
      end if;
    end finalize;
  begin
    --check_audit_functionality
    for x in (select upper(a.tablename) tablename, upper(a.a_tablename) a_tablename
              from   a_tablelist a) loop
      begin
        --columns_added
        columns_added(x.tablename, x.a_tablename);
        --columns_lengths
        columns_lengths(x.tablename, x.a_tablename);
        --columns_dropped
        columns_dropped(x.tablename, x.a_tablename);
      end;
    end loop; --x
    finalize;
  exception
    --exception handling
    when others then
      begin
        rollback;
        write_log('Error Others ' || sqlerrm || ' check_audit_functionality', 3);
      end;
  end check_audit_functionality;

end pack_auditfunctionality;
/
