/*order 502045*/      
      /*
      ACTION is CREATE Table ABSLOG
      */

begin
execute immediate ('CREATE SEQUENCE SEQ_ABSLOG
START WITH 1
INCREMENT BY 1');
exception when others then null;
end;
/

begin
execute immediate ('
CREATE TABLE ABSLOG (
       ABSLOG_ID            NUMBER NOT NULL,
       LOGTYPE              NUMBER(2) NOT NULL,
       LOGSOURCE            VARCHAR2(30) NOT NULL,
       PLANT_ID             NUMBER NULL,
       PRIORITY             NUMBER(1) NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       MESSAGE              VARCHAR2(1000) NOT NULL,
       MESSAGE_EN           VARCHAR2(1000) NULL
)');
exception when others then null;
end;
/


COMMENT ON COLUMN ABSLOG.LOGTYPE IS '1 = Server
2 = Client
3 = Reportserver
4 = Tools
5 = Packages
6 = Conversion
7 = Scripts
8 = Miscellaneous';

COMMENT ON COLUMN ABSLOG.LOGSOURCE IS '1 = Servername
2 = Clientname
3 = Reportservername
4 = Utility name
5 = Package / procedure name
6 = ''Customer structure'' or ''Products''
7 = Script name
8 = (to be defined)';

COMMENT ON COLUMN ABSLOG.PRIORITY IS '1 = Message
2 = Warning
3 = Error
4 = Stack trace
5 = Debug info';

begin
execute immediate ('CREATE INDEX XIF1ABSLOG ON ABSLOG
(
       PLANT_ID                       ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIE1ABSLOG ON ABSLOG
(
       LOGTYPE                        ,
       LOGSOURCE                      ,
       TIMESTAMP                      
);');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIE2ABSLOG ON ABSLOG
(
       TIMESTAMP                      
)');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOG
       ADD  ( CONSTRAINT XPKABSLOG PRIMARY KEY (ABSLOG_ID) )');
exception when others then null;
end;
/
      
      /*
      ACTION is CREATE Table ABSLOGSTACKTRACE
      */

begin
execute immediate ('CREATE TABLE ABSLOGSTACKTRACE (
       ABSLOG_ID            NUMBER NOT NULL,
       STACKTRACE           CLOB NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
)');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGSTACKTRACE
       ADD  ( CONSTRAINT XPKABSLOGSTACKTRACE PRIMARY KEY (ABSLOG_ID) )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOG
       ADD  ( CONSTRAINT ABSLOG_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGSTACKTRACE
       ADD  ( CONSTRAINT ABSLOGSTACKTRACE_ABSLOG_ID
              FOREIGN KEY (ABSLOG_ID)
                             REFERENCES ABSLOG
                             ON DELETE CASCADE );');
exception when others then null;
end;
/

      /*
      ACTION is CREATE Table ABSLOGPROCESSJOB
      */

begin
execute immediate ('CREATE SEQUENCE SEQ_ABSLOGPROCESSJOB
START WITH 1
INCREMENT BY 1');
exception when others then null;
end;
/

begin
execute immediate ('CREATE TABLE ABSLOGPROCESSJOB (
       ABSLOGPROCESSJOB_ID  NUMBER NOT NULL,
       PLANT_ID             NUMBER NOT NULL,
       STARTTIMESTAMP       DATE NOT NULL,
       ENDTIMESTAMP         DATE NULL,
       WORKSTATION_ID       NUMBER NULL,
       DIALOG_ID            NUMBER NULL,
       PLANTPROCESS_ID      NUMBER NULL,
       PROCESSNAME          VARCHAR2(30) NULL,
       STATUSQUOHEADER_ID   NUMBER NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIF1ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       PLANT_ID                       ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIF2ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       WORKSTATION_ID                 ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIF3ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       DIALOG_ID                      ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIF4ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       PLANTPROCESS_ID                ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIF5ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       STATUSQUOHEADER_ID             ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('CREATE INDEX XIE1ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB
(
       PLANT_ID                       ,
       STARTTIMESTAMP                 
)');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT XPKABSLOGPROCESSJOB PRIMARY KEY (
              ABSLOGPROCESSJOB_ID) ) ');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT ABSLOGPROCESSJOB_STATUSQUOHEAD
              FOREIGN KEY (STATUSQUOHEADER_ID)
                             REFERENCES STATUSQUOHEADER
                             ON DELETE SET NULL )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT ABSLOGPROCESSJOB_PLANTPROCESS_
              FOREIGN KEY (PLANTPROCESS_ID)
                             REFERENCES PLANTPROCESS )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT ABSLOGPROCESSJOB_DIALOG_ID
              FOREIGN KEY (DIALOG_ID)
                             REFERENCES DIALOGS )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT ABSLOGPROCESSJOB_WORKSTATION_I
              FOREIGN KEY (WORKSTATION_ID)
                             REFERENCES WORKSTATION )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOB
       ADD  ( CONSTRAINT ABSLOGPROCESSJOB_PLANT_ID
              FOREIGN KEY (PLANT_ID)
                             REFERENCES PLANT )');
exception when others then null;
end;
/

      /*
      ACTION is CREATE Table ABSLOGPROCESSJOBLINE
      */

begin
execute immediate ('CREATE SEQUENCE SEQ_ABSLOGPROCESSJOBLINE
START WITH 1
INCREMENT BY 1');
exception when others then null;
end;
/

begin
execute immediate ('
CREATE TABLE ABSLOGPROCESSJOBLINE (
       ABSLOGPROCESSJOBLINE_ID NUMBER NOT NULL,
       ABSLOGPROCESSJOB_ID  NUMBER NOT NULL,
       PRIORITY             NUMBER NOT NULL,
       TIMESTAMP            DATE NOT NULL,
       MESSAGE              VARCHAR2(1000) NOT NULL,
       MESSAGE_EN           VARCHAR2(1000) NULL,
       SYSTEMUSER_ID        NUMBER NOT NULL
)');
exception when others then null;
end;
/

begin
execute immediate ('
CREATE UNIQUE INDEX XPKABSLOGPROCESSJOBLINE ON ABSLOGPROCESSJOBLINE
(
       ABSLOGPROCESSJOBLINE_ID        ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('
CREATE INDEX XIF1ABSLOGPROCESSJOBLINE ON ABSLOGPROCESSJOBLINE
(
       ABSLOGPROCESSJOB_ID            ASC
)');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOBLINE
       ADD  ( CONSTRAINT XPKABSLOGPROCESSJOBLINE 
		PRIMARY KEY (ABSLOGPROCESSJOBLINE_ID) )');
exception when others then null;
end;
/

begin
execute immediate ('ALTER TABLE ABSLOGPROCESSJOBLINE
       ADD  ( CONSTRAINT ABSLOGPROCESSJOBLINE_ABSLOGPRO
		FOREIGN KEY (ABSLOGPROCESSJOB_ID)
                             REFERENCES ABSLOGPROCESSJOB
                             ON DELETE CASCADE )');
exception when others then null;
end;
/

COMMENT ON COLUMN ABSLOGPROCESSJOBLINE.PRIORITY IS '1 = Message
2 = Warning
3 = Error
4 = (not used)
5 = Debug info';

      /*
      CHANGE REPORT for Table EMAILSETTING
          - Changing column definition of DOCUMENTTYPE
          - Adding column TOACCOUNT
      ACTION is ALTER or modify Table EMAILSETTING
      */

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS '1=Invoice
2=Statement
3=Purchase Order
4=E-service password reminder
5=Work order via web
6=Invoice job check list
7=Customer
8=Log message';


begin
execute immediate ('ALTER TABLE EMAILSETTING ADD (TOACCOUNT            VARCHAR2(80) NULL)');
exception when others then null;
end;
/


--insert new process
begin
execute immediate ('insert into process(process_id,description,active) values (''80'',''Clean up Log'',''Y'')');
exception when others then null;
end;
/


--insert into EmailSetting table
begin
execute immediate ('INSERT INTO EMAILSETTING
  (EMAILSETTING_ID,
   DOCUMENTTYPE,
   FROMACCOUNT,
   TOCC,
   TOBCC,
   SUBJECT,
   BODY,
   SYSTEMUSER_ID,
   TIMESTAMP,
   PLANT_ID,
   Toaccount)
  SELECT SEQ_EMAILSETTING.NEXTVAL,
         8,
         P.EMAIL,
         NULL,
         NULL,
         ''FW:Log message'',
         Null,
         getminsystemuser(),
         sysdate,
         P.PLANT_ID,
         ''Helpdesk@ABSLBS.com''
    FROM PLANT P
   WHERE P.EMAIL IS NOT NULL');
exception when others then null;
end;
/

