-- ABS-156002
-- ABSLOGPROCESSJOB TABLE
ALTER TABLE ABSLOGPROCESSJOB ADD (WEBSERVICELOG_ID NUMBER NULL);

CREATE INDEX XIF6ABSLOGPROCESSJOB ON ABSLOGPROCESSJOB (WEBSERVICELOG_ID ASC);

ALTER TABLE ABSLOGPROCESSJOB
  ADD CONSTRAINT ABSLOGPROCESSJOB_WEBSERVICELOG_ID FOREIGN KEY (WEBSERVICELOG_ID)
  REFERENCES WEBSERVICELOG (WEBSERVICELOG_ID) ON DELETE SET NULL;


-- WEBSERVICEPROCESS TABLE
CREATE SEQUENCE SEQ_WEBSERVICEPROCESS
START WITH 1
INCREMENT BY 1;

CREATE TABLE WEBSERVICEPROCESS
(
  WEBSERVICEPROCESS_ID NUMBER NOT NULL,
  DESCRIPTION          VARCHAR2(70) NOT NULL,
  ACTIVE               VARCHAR2(1) DEFAULT 'Y' NOT NULL
                       CONSTRAINT CHK_WEBSERVICEPROCESS_ACTIVE CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID		   NUMBER NOT NULL,
  TIMESTAMP			   DATE NOT NULL
)
;

ALTER TABLE WEBSERVICEPROCESS
  ADD CONSTRAINT XPKWEBSERVICEPROCESS PRIMARY KEY (WEBSERVICEPROCESS_ID);
  
-- WEBSERVICELOG TABLE
ALTER TABLE WEBSERVICELOG ADD (WEBSERVICEPROCESS_ID NUMBER NULL);

CREATE INDEX XIF736WEBSERVICELOG ON WEBSERVICELOG (WEBSERVICEPROCESS_ID ASC);

ALTER TABLE WEBSERVICELOG
  ADD CONSTRAINT FK_WEBSERVICELOG_WEBSERVICEPROCESS_ID FOREIGN KEY (WEBSERVICEPROCESS_ID)
  REFERENCES WEBSERVICEPROCESS (WEBSERVICEPROCESS_ID);

--ROUTECONTROLEXTRAINFO TABLE
ALTER TABLE ROUTECONTROLEXTRAINFO ADD (FINALIZATIONSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_RCEXTRAINFO_FINALIZATIONSTATUS CHECK (FINALIZATIONSTATUS IN (0, 1, 2)),
                                       FINALIZATIONCOMPLETETIMESTAMP DATE NULL);
COMMENT ON COLUMN ROUTECONTROLEXTRAINFO.FINALIZATIONSTATUS IS '0 (default) = Not started; 1 = Pending Taxation; 2 = Complete; only used by finalization process';
