create or replace package pack_startup is
  /***************************************************************************************\
  *  Author  : ABS                                                                        *
  *  Created : 25.06.2019  ABS-70085 / ABS-85742                                          *
  *  Purpose : This package is used for validating everything that is needed              *
  *            before starting an update                                                  *
  *  Version : 0.4 for Abssolute release 9.08.00                                          *
  *  Updated : 30.08.2019 ABS-88639 Extend pack_startup - add clean up of abslog table    *
  *  Updated : 16.07.2021 ABS-119409 Add function to enable/disable audit triggers        *
  *  Updated : 13.12.2021 ABS-127736 Prevent auto deployment of view erp_transaction_log  *
  *  Updated : 07.04.2022 ABS-133463 Make Correct sequences public in Pack_startup        *
  *  Updated : 06.11.2023 ABS-146355 Add pack audit mechanism                             *
  *  Updated : 20.12.2023 ABS-158875 Update BIC database to 9.07.00.RELEASE               *
  *  Updated : 08.03.2024 ABS-161456 Check pack_startup - invalid at update to 9.07.00    *
  \***************************************************************************************/
  procedure startup(message out varchar2);
  procedure toggle_audit_triggers(forenable in number, errcode out number);
  procedure correct_wrong_sequences;
  procedure remove_obsolete_objects;
end pack_startup;
/
create or replace package body PACK_STARTUP is

  procedure WRITELOG(FP_MESSAGE  in ABSLOG.MESSAGE%type,
                     FP_PRIORITY in ABSLOG.PRIORITY%type) is
  begin
    insert into abslog
      (abslog_id, logtype, logsource, plant_id, priority, timestamp, systemuser_id, message)
    values
      (seq_abslog.nextval, 7, 'PACK_STARTUP', null, FP_PRIORITY, sysdate, GETMINSYSTEMUSER, substr(FP_MESSAGE, 1, 1000));
    commit;
  exception
    when others then
      begin
        rollback;
        dbms_output.put_line('Error during WRITELOG ' || sqlerrm);
        raise;
      end;
  end WRITELOG;

   procedure CLEANLOGS is
    STMT varchar2(300);
    existsAbslogConstraint number;
    begin
      select count(1) into existsAbslogConstraint from user_constraints
       where upper(constraint_name) = 'ABSLOGSTACKTRACE_ABSLOG_ID' and upper(table_name) = 'ABSLOGSTACKTRACE';

      if (existsAbslogConstraint > 0) then
        STMT := 'TRUNCATE TABLE ABSLOG CASCADE';
        execute immediate STMT;
      else
        STMT := 'TRUNCATE TABLE ABSLOGSTACKTRACE';
        execute immediate STMT;
        STMT := 'TRUNCATE TABLE ABSLOG';
        execute immediate STMT;
        STMT := 'ALTER TABLE ABSLOGSTACKTRACE
                       ADD CONSTRAINT ABSLOGSTACKTRACE_ABSLOG_ID FOREIGN KEY (ABSLOG_ID)
                       REFERENCES ABSLOG (ABSLOG_ID) ON DELETE CASCADE';
        execute immediate STMT;
      end if;
    exception
      when others then
      begin
        WRITELOG('Running CLEANLOGS procedure failed. Error while executing: ' || STMT ||
                   CHR(10) || ' Error: ' || sqlerrm, 2);
      end ;
   end CLEANLOGS;

  procedure COMPILEANDLOGINVALIDOBJ is
      STMT varchar2(300);
  begin
    STMT := 'BEGIN DBMS_UTILITY.COMPILE_SCHEMA( USER, FALSE); END;';
    begin
      execute immediate STMT;
      exception
        when others then
        begin
          WRITELOG('Running Compile Invalid objectes failed while executing: ' || STMT ||
                   CHR(10) || ' Error: ' || sqlerrm,
                   2);
        end ;
     end;
    for INVALIDOBJECT in (select E.NAME,
                                 E.TYPE,
                                 E.LINE,
                                 E.POSITION,
                                 E.TEXT,
                                 COALESCE((select trim(SUBSTR(US.TEXT,
                                                             E.POSITION))
                                            from SYS.USER_SOURCE US
                                           where US.NAME = E.NAME
                                             and US.TYPE = E.TYPE
                                             and US.LINE = E.LINE),
                                          (select trim(SUBSTR(US.TEXT,
                                                              E.POSITION))
                                             from SYS.USER_SOURCE US
                                            where US.NAME = E.NAME
                                              and US.TYPE = E.TYPE
                                              and US.LINE =
                                                  E.LINE +
                                                  COALESCE((select min(USM.LINE)
                                                              from SYS.USER_SOURCE USM
                                                             where USM.NAME =
                                                                   E.NAME
                                                               and USM.TYPE =
                                                                   E.TYPE
                                                               and replace(USM.TEXT,
                                                                           'DECLARE') <>
                                                                   USM.TEXT) - 1,
                                                           0))) EXTRA_INFO
                            from SYS.USER_ERRORS E
                           where E.SEQUENCE = 1) loop
      begin
        WRITELOG('Invalid database objects exist before update!' ||
                 CHR(10) || 'Please check: ' || INVALIDOBJECT.NAME ||
                 ', database object type is ' || INVALIDOBJECT.TYPE ||
                 ', line ' || INVALIDOBJECT.LINE || ', position ' ||
                 INVALIDOBJECT.POSITION || CHR(10) || ' Error message ' ||
                 INVALIDOBJECT.TEXT || ' ' || INVALIDOBJECT.EXTRA_INFO,
                 2);
      end;
    end loop;
  end COMPILEANDLOGINVALIDOBJ;

  procedure PROCESS_SEQUENCE(SEQUENCENAME USER_SEQUENCES.SEQUENCE_NAME%type) is
    STMT           varchar2(300);
    VTABLE_NAME    varchar2(30);
    PK_COLUMN_NAME varchar2(50);
    MAX_VAL_PK     number;
    MAX_VAL_SEQ    number;
    I              number;
  begin
    VTABLE_NAME := SUBSTR(SEQUENCENAME, 5);
    begin
      select UCONSCOL.COLUMN_NAME
        into PK_COLUMN_NAME
        from USER_CONSTRAINTS UCONS
        join USER_CONS_COLUMNS UCONSCOL
          on UCONSCOL.TABLE_NAME = UCONS.TABLE_NAME
         and UCONSCOL.CONSTRAINT_NAME = UCONS.CONSTRAINT_NAME
         and UCONSCOL.OWNER = UCONS.OWNER
       where UCONS.CONSTRAINT_TYPE = 'P'
         and UCONS.TABLE_NAME = VTABLE_NAME
         and (select count(1)
                from USER_CONS_COLUMNS U
               where U.OWNER = UCONSCOL.OWNER
                 and U.CONSTRAINT_NAME = UCONSCOL.CONSTRAINT_NAME
                 and U.TABLE_NAME = UCONSCOL.TABLE_NAME) = 1;
    exception
      when others then
        raise NO_DATA_FOUND;
    end;

    STMT := 'select max(' || PK_COLUMN_NAME || ') from ' || VTABLE_NAME;
    begin
      execute immediate STMT
        into MAX_VAL_PK;
    exception
      when others then
        begin
          WRITELOG('Running process_wrong_sequence procedure... Failed to run statement: ' || STMT ||
                   CHR(10) || ' Error: ' || sqlerrm,
                   2);
          MAX_VAL_PK := null;
          raise NO_DATA_FOUND;
        end;

    end;

    STMT := 'select ' || SEQUENCENAME || '.nextval from dual';
    execute immediate STMT
      into MAX_VAL_SEQ;

    if (MAX_VAL_PK is not null and MAX_VAL_SEQ < MAX_VAL_PK) then
      begin
        I := MAX_VAL_PK - MAX_VAL_SEQ;

        STMT := 'alter sequence ' || SEQUENCENAME || ' increment by ' ||
                TO_CHAR(I);
        execute immediate STMT;

        STMT := 'select ' || SEQUENCENAME || '.nextval from dual';
        execute immediate STMT
          into I;

        STMT := 'alter sequence ' || SEQUENCENAME || ' increment by 1';
        execute immediate STMT;

        commit;
        WRITELOG('Wrong sequence on database was: ' || SEQUENCENAME ||
                 CHR(10) || 'Sequence is now corrected.',
                 2);
      end;
    end if;

  exception
    when NO_DATA_FOUND then
      null;
  end PROCESS_SEQUENCE;

  procedure CORRECT_WRONG_SEQUENCES is
  begin
    PROCESS_SEQUENCE('SEQ_ABSLOG');
    for SEQUENCE in (select US.SEQUENCE_NAME
                       from USER_SEQUENCES US
                      where US.SEQUENCE_NAME like 'SEQ\_%' escape
                      '\'
                        and US.SEQUENCE_NAME <> 'SEQ_ABSLOG') loop
      begin
        PROCESS_SEQUENCE(SEQUENCE.SEQUENCE_NAME);
      end;
    end loop;
  end CORRECT_WRONG_SEQUENCES;

  procedure CHECK_NLS_SEMANTICS(message out VARCHAR2) is
	LENGTHVALUE VARCHAR2(30);

  begin
    select value into LENGTHVALUE from nls_database_parameters where parameter = 'NLS_LENGTH_SEMANTICS';
		if (LENGTHVALUE != 'CHAR') then
		  message := 'NLS_LENGTH_SEMANTICS should be set to CHAR';
		  WRITELOG(message, 2);
		end if;
  end CHECK_NLS_SEMANTICS;

  procedure TOGGLE_AUDIT_TRIGGERS(forEnable NUMBER, errCode out NUMBER) is
    STMT VARCHAR2(300);
    ACTION VARCHAR2(10);
    trgName user_triggers.TRIGGER_NAME%type;
  begin
    errCode := 0;
    if (forEnable = 1) then
      ACTION := ' ENABLE';
    else
      ACTION := ' DISABLE';
    end if;
    for x in (select 'TRG_A_' || substr(upper(a.tablename), 1, 24) triggername from a_tablelist a where a.processed = 1) loop
      begin
        select t.TRIGGER_NAME into trgName from user_triggers t where t.TRIGGER_NAME = x.triggername;
        STMT := 'ALTER TRIGGER ' || x.triggername || ACTION;
        execute immediate STMT;
      exception
        when no_data_found then
          WRITELOG('Running TOGGLE_AUDIT_TRIGGERS procedure... Failed to ' || lower(ACTION) || ' trigger ' 
                 || x.triggername || '. Trigger does not exists.', 5);
        when others then
          errCode := 1;
          WRITELOG('Running TOGGLE_AUDIT_TRIGGERS procedure... Failed to run statement: ' || STMT ||
                 CHR(10) || ' Error: ' || sqlerrm, 2);
      end ;
    end loop;
  exception
    when others then
      errCode := 1;
      WRITELOG('Running TOGGLE_AUDIT_TRIGGERS procedure failed. Error: ' || sqlerrm, 2);
  end TOGGLE_AUDIT_TRIGGERS;

  procedure remove_obsolete_objects is
    procedure remove_trigger(fp_triggername in varchar2) is
    begin
      execute immediate 'drop trigger ' || upper(fp_triggername);
    exception
      when others then
        if sqlcode = -4080 then
          null; --suppress trigger does not exist exception
        else
          raise;
        end if;
    end remove_trigger;
  begin
    --remove_obsolete_objects
    remove_trigger('AFTER_UPDATE_SYSTEMSETTING');
  end remove_obsolete_objects;
   
  procedure STARTUP(message out VARCHAR2) is
  begin
    CLEANLOGS;
    remove_obsolete_objects;
    CORRECT_WRONG_SEQUENCES;
    COMPILEANDLOGINVALIDOBJ;
    CHECK_NLS_SEMANTICS(message);
   end STARTUP;
end PACK_STARTUP;
/
