-- ABS-64241 / ABS-72917
--- ROUTE ---
ALTER TABLE ROUTE ADD (ROUTECODE VARCHAR2(6) NULL);

COMMENT ON COLUMN ROUTE.ROUTECODE IS 'Used as an alpha route identifier';

--- ROUTECODEMASTER ---
CREATE SEQUENCE SEQ_ROUTECODEMASTER
START WITH 1
INCREMENT BY 1;

CREATE TABLE ROUTECODEMASTER
(
	ROUTECODEMASTER_ID NUMBER NOT NULL,
	ROUTECODE VARCHAR2(6) NOT NULL,
	ROUTENUMBER NUMBER NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE ROUTECODEMASTER ADD CONSTRAINT XPKROUTECODEMASTER PRIMARY KEY (ROUTECODEMASTER_ID);

CREATE UNIQUE INDEX XAK1ROUTECODEMASTER ON ROUTECODEMASTER (ROUTECODE ASC);
CREATE UNIQUE INDEX XAK2ROUTECODEMASTER ON ROUTECODEMASTER (ROUTENUMBER ASC);

--- ROUTESTOP ---
ALTER TABLE ROUTESTOP ADD (SORTSEQUENCE NUMBER DEFAULT 0 NOT NULL CONSTRAINT SORTSEQUENCE_VALID1 CHECK (SORTSEQUENCE BETWEEN 0 AND 99));

