--- ABS-119994 / ABS-123937

------------- SORTBATCHDEFINITION -------------

ALTER TABLE SORTBATCHDEFINITION ADD (DEFAULTSORTLINE NUMBER NULL,
                                     ACTIVABILITYRANGE NUMBER NULL CONSTRAINT CHK_SORTBATCHDEFINITION_ACTIVRANGE CHECK (ACTIVABILITYRANGE IN (1, 2, 3, 4, 5, 6, 7, 8)));
COMMENT ON COLUMN SORTBATCHDEFINITION.DEFAULTSORTLINE IS 'Will only be used when the module KLHCUSTOM is active';
COMMENT ON COLUMN SORTBATCHDEFINITION.ACTIVABILITYRANGE IS 'Field is only used for module KLHCUSTOM';


------------- SORTBATCHDEFINITIONDETAIL --------------

CREATE SEQUENCE SEQ_SORTBATCHDEFINITIONDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE SORTBATCHDEFINITIONDETAIL
(
SORTBATCHDEFINITIONDETAIL_ID NUMBER NOT NULL,
SORTBATCHDEFINITION_ID       NUMBER NOT NULL,
ROUTESTOP_ID                 NUMBER NOT NULL,
SEQUENCENUMBER               NUMBER DEFAULT 1 NOT NULL CONSTRAINT CHK_SORTBATCHDEFDETAIL_SQN CHECK (SEQUENCENUMBER BETWEEN 1 AND 9999),
SYSTEMUSER                   NUMBER NOT NULL,
TIMESTAMP                    DATE NOT NULL
);

ALTER TABLE SORTBATCHDEFINITIONDETAIL ADD CONSTRAINT XPKSORTBATCHDEFINITIONDETAIL PRIMARY KEY (SORTBATCHDEFINITIONDETAIL_ID);

CREATE UNIQUE INDEX XAK1SORTBATCHDEFINITIONDETAIL ON SORTBATCHDEFINITIONDETAIL (ROUTESTOP_ID ASC);
CREATE UNIQUE INDEX XAK2SORTBATCHDEFINITIONDETAIL ON SORTBATCHDEFINITIONDETAIL (SORTBATCHDEFINITION_ID ASC, SEQUENCENUMBER ASC); 

CREATE INDEX XIF1SORTBATCHDEFINITIONDETAIL ON SORTBATCHDEFINITIONDETAIL (SORTBATCHDEFINITION_ID ASC);

ALTER TABLE SORTBATCHDEFINITIONDETAIL 
      ADD CONSTRAINT FK_SORTBATCHDEFDET_SORTBATCHDEF FOREIGN KEY (SORTBATCHDEFINITION_ID)
      REFERENCES SORTBATCHDEFINITION (SORTBATCHDEFINITION_ID);
ALTER TABLE SORTBATCHDEFINITIONDETAIL 
      ADD CONSTRAINT FK_SORTBATCHDEFDET_ROUTESTOP FOREIGN KEY (ROUTESTOP_ID)
      REFERENCES ROUTESTOP (ROUTESTOP_ID);
