-- ABS-16489  Trigger to be created for databasettings history on update
CREATE TABLE A_TABLELIST
(
  TABLENAME   VARCHAR2(30) NOT NULL,
  PROCESSED   NUMBER DEFAULT 0 NOT NULL,
  A_TABLENAME VARCHAR2(30)
);
COMMENT ON COLUMN A_TABLELIST.PROCESSED
  IS '0=NOT PROCESSED INTO A-TABLE, 1=PROCESSED INTO A-TABLE';
ALTER TABLE A_TABLELIST
  ADD CONSTRAINT PK_A_TABLELIST PRIMARY KEY (TABLENAME);
ALTER TABLE A_TABLELIST
  ADD CONSTRAINT INDX_A_TABLELIST_ANAME UNIQUE (A_TABLENAME);

insert into A_TABLELIST (tablename, processed, a_tablename)
values ('DATABASESETTINGS', 1, 'A_DATABASESETTINGS');
commit;

--remark table is normally created by PACKAGE PACK_AUDITFUNCTIONALITY
create table A_DATABASESETTINGS
(
  CODE                       VARCHAR2(3),
  LANCODE                    VARCHAR2(2),
  REFERENCEYEAR              NUMBER,
  REFERENCEWEEK              NUMBER,
  ABSHOMEPAGE                VARCHAR2(80),
  OVERALLUNIQUEGARMENTID     VARCHAR2(1 BYTE),
  LOCALE                     VARCHAR2(5),
  TIMEZONE                   VARCHAR2(40),
  MENUBASELANGUAGE           VARCHAR2(2) NOT NULL,
  DBLOGDIR                   VARCHAR2(60),
  PRINTLABELSDIRECTLY        VARCHAR2(1 BYTE) NOT NULL,
  LOGDBSESSIONAPPSERV        VARCHAR2(1 BYTE) NOT NULL,
  LOGDBSESSIONREPSERV        VARCHAR2(1 BYTE) NOT NULL,
  LOGSTACKTRACEAPPSERV       VARCHAR2(1 BYTE) NOT NULL,
  LOGSTACKTRACEREPSERV       VARCHAR2(1 BYTE) NOT NULL,
  ENVIRONMENT                VARCHAR2(15),
  MESSAGEATLOGIN             CLOB,
  TABLESPACENAME             VARCHAR2(40),
  DATATBLSPACENAME           VARCHAR2(40),
  OVERALLUNIQUERESIDENTIALID VARCHAR2(1) NOT NULL,
  NLS_TERRITORY              VARCHAR2(160),
  A_ACTION                   VARCHAR2(1) DEFAULT 'I' NOT NULL,
  A_TIMESTAMP                DATE DEFAULT SYSDATE NOT NULL,
  A_CLIENT_IDENTIFIER        VARCHAR2(64)
);

--remark table is normally filled by PACKAGE PACK_AUDITFUNCTIONALITY
INSERT INTO A_DATABASESETTINGS
  (CODE, LANCODE, REFERENCEYEAR, REFERENCEWEEK, ABSHOMEPAGE, OVERALLUNIQUEGARMENTID, LOCALE,
   TIMEZONE, MENUBASELANGUAGE, DBLOGDIR, PRINTLABELSDIRECTLY, LOGDBSESSIONAPPSERV,
   LOGDBSESSIONREPSERV, LOGSTACKTRACEAPPSERV, LOGSTACKTRACEREPSERV, ENVIRONMENT, MESSAGEATLOGIN,
   TABLESPACENAME, DATATBLSPACENAME, OVERALLUNIQUERESIDENTIALID, NLS_TERRITORY)
  SELECT CODE, LANCODE, REFERENCEYEAR, REFERENCEWEEK, ABSHOMEPAGE, OVERALLUNIQUEGARMENTID, LOCALE,
         TIMEZONE, MENUBASELANGUAGE, DBLOGDIR, PRINTLABELSDIRECTLY, LOGDBSESSIONAPPSERV,
         LOGDBSESSIONREPSERV, LOGSTACKTRACEAPPSERV, LOGSTACKTRACEREPSERV, ENVIRONMENT,
         MESSAGEATLOGIN, TABLESPACENAME, DATATBLSPACENAME, OVERALLUNIQUERESIDENTIALID, NLS_TERRITORY
  FROM   DATABASESETTINGS;
COMMIT;

--remark trigger is normally generated by PACKAGE PACK_AUDITFUNCTIONALITY
CREATE OR REPLACE TRIGGER TRG_A_DATABASESETTINGS
  AFTER INSERT OR UPDATE OR DELETE ON DATABASESETTINGS
  FOR EACH ROW
DECLARE
  LV_A_CLIENT_IDT VARCHAR2(64);
BEGIN
  SELECT NVL(SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER'),SYS_CONTEXT('USERENV', 'TERMINAL')) INTO LV_A_CLIENT_IDT FROM DUAL;
  IF UPDATING THEN
    BEGIN
      INSERT INTO A_DATABASESETTINGS
        (CODE,LANCODE,REFERENCEYEAR,REFERENCEWEEK,ABSHOMEPAGE,OVERALLUNIQUEGARMENTID,LOCALE,TIMEZONE,MENUBASELANGUAGE,DBLOGDIR,PRINTLABELSDIRECTLY,LOGDBSESSIONAPPSERV,LOGDBSESSIONREPSERV,LOGSTACKTRACEAPPSERV,LOGSTACKTRACEREPSERV,ENVIRONMENT,MESSAGEATLOGIN,TABLESPACENAME,DATATBLSPACENAME,OVERALLUNIQUERESIDENTIALID,NLS_TERRITORY, A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (:NEW.CODE,:NEW.LANCODE,:NEW.REFERENCEYEAR,:NEW.REFERENCEWEEK,:NEW.ABSHOMEPAGE,:NEW.OVERALLUNIQUEGARMENTID,:NEW.LOCALE,:NEW.TIMEZONE,:NEW.MENUBASELANGUAGE,:NEW.DBLOGDIR,:NEW.PRINTLABELSDIRECTLY,:NEW.LOGDBSESSIONAPPSERV,:NEW.LOGDBSESSIONREPSERV,:NEW.LOGSTACKTRACEAPPSERV,:NEW.LOGSTACKTRACEREPSERV,:NEW.ENVIRONMENT,:NEW.MESSAGEATLOGIN,:NEW.TABLESPACENAME,:NEW.DATATBLSPACENAME,:NEW.OVERALLUNIQUERESIDENTIALID,:NEW.NLS_TERRITORY, 'U', LV_A_CLIENT_IDT);
    END;
  ELSIF INSERTING THEN
    BEGIN
      INSERT INTO A_DATABASESETTINGS
        (CODE,LANCODE,REFERENCEYEAR,REFERENCEWEEK,ABSHOMEPAGE,OVERALLUNIQUEGARMENTID,LOCALE,TIMEZONE,MENUBASELANGUAGE,DBLOGDIR,PRINTLABELSDIRECTLY,LOGDBSESSIONAPPSERV,LOGDBSESSIONREPSERV,LOGSTACKTRACEAPPSERV,LOGSTACKTRACEREPSERV,ENVIRONMENT,MESSAGEATLOGIN,TABLESPACENAME,DATATBLSPACENAME,OVERALLUNIQUERESIDENTIALID,NLS_TERRITORY, A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (:NEW.CODE,:NEW.LANCODE,:NEW.REFERENCEYEAR,:NEW.REFERENCEWEEK,:NEW.ABSHOMEPAGE,:NEW.OVERALLUNIQUEGARMENTID,:NEW.LOCALE,:NEW.TIMEZONE,:NEW.MENUBASELANGUAGE,:NEW.DBLOGDIR,:NEW.PRINTLABELSDIRECTLY,:NEW.LOGDBSESSIONAPPSERV,:NEW.LOGDBSESSIONREPSERV,:NEW.LOGSTACKTRACEAPPSERV,:NEW.LOGSTACKTRACEREPSERV,:NEW.ENVIRONMENT,:NEW.MESSAGEATLOGIN,:NEW.TABLESPACENAME,:NEW.DATATBLSPACENAME,:NEW.OVERALLUNIQUERESIDENTIALID,:NEW.NLS_TERRITORY, 'I', LV_A_CLIENT_IDT);
    END;
  ELSIF DELETING THEN
    BEGIN
      INSERT INTO A_DATABASESETTINGS
        (CODE,LANCODE,REFERENCEYEAR,REFERENCEWEEK,ABSHOMEPAGE,OVERALLUNIQUEGARMENTID,LOCALE,TIMEZONE,MENUBASELANGUAGE,DBLOGDIR,PRINTLABELSDIRECTLY,LOGDBSESSIONAPPSERV,LOGDBSESSIONREPSERV,LOGSTACKTRACEAPPSERV,LOGSTACKTRACEREPSERV,ENVIRONMENT,MESSAGEATLOGIN,TABLESPACENAME,DATATBLSPACENAME,OVERALLUNIQUERESIDENTIALID,NLS_TERRITORY, A_ACTION, A_CLIENT_IDENTIFIER)
      VALUES
        (:OLD.CODE,:OLD.LANCODE,:OLD.REFERENCEYEAR,:OLD.REFERENCEWEEK,:OLD.ABSHOMEPAGE,:OLD.OVERALLUNIQUEGARMENTID,:OLD.LOCALE,:OLD.TIMEZONE,:OLD.MENUBASELANGUAGE,:OLD.DBLOGDIR,:OLD.PRINTLABELSDIRECTLY,:OLD.LOGDBSESSIONAPPSERV,:OLD.LOGDBSESSIONREPSERV,:OLD.LOGSTACKTRACEAPPSERV,:OLD.LOGSTACKTRACEREPSERV,:OLD.ENVIRONMENT,:OLD.MESSAGEATLOGIN,:OLD.TABLESPACENAME,:OLD.DATATBLSPACENAME,:OLD.OVERALLUNIQUERESIDENTIALID,:OLD.NLS_TERRITORY, 'D', LV_A_CLIENT_IDT);
    END;
  END IF;
END TRG_A_DATABASESETTINGS;
/
