--Order 20011112.20 (old order number 502898)
--create new LTX tables

------------------------LTXSORTINGDEVICE--------------------

CREATE SEQUENCE SEQ_LTXSORTINGDEVICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGDEVICE
(
	LTXSORTINGDEVICE_ID  NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	MAXLOCATIONS         NUMBER(3) NOT NULL  CONSTRAINT  MaxLocations_valid1 CHECK (MAXLOCATIONS BETWEEN 1 AND 999),
	IOTYPE               NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  IOType_valid1 CHECK (IOTYPE IN (0, 1, 2, 3, 4, 5, 6)),
	SERIALBAUDRATE       NUMBER DEFAULT  0  NOT NULL ,
	SERIALPARITY         VARCHAR2(1) NULL  CONSTRAINT  SerialParity_valid1 CHECK (SERIALPARITY IN ('E', 'N')),
	SERIALDATABITS       NUMBER(2) DEFAULT  0  NOT NULL ,
	SERIALSTOPBITS       NUMBER(1) DEFAULT  0  NOT NULL  CONSTRAINT  SerialStopBits_valid1 CHECK (SERIALSTOPBITS BETWEEN 0 AND 2),
	PRESORTALLOWED       VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_ValidationField35 CHECK (PRESORTALLOWED IN ('Y', 'N')),
	CHECKDIGIT           NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  CheckDigit_valid1 CHECK (CHECKDIGIT IN (0, 1)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;

COMMENT ON COLUMN LTXSORTINGDEVICE.IOTYPE IS '0 = XML
1 = CSV
2 = PC I/O Parallel (LPT)
3 = PC I/O Serial (COM)
4 = ADAM Parallel
5 = ADAM Serial
6 = USB';

COMMENT ON COLUMN LTXSORTINGDEVICE.SERIALBAUDRATE IS '0 = Not applicable';


COMMENT ON COLUMN LTXSORTINGDEVICE.SERIALPARITY IS '''E'' = Even
''N'' = Odd';


COMMENT ON COLUMN LTXSORTINGDEVICE.CHECKDIGIT IS '0 = No check digit
1 = Alba Sortingcabinet';

ALTER TABLE LTXSORTINGDEVICE
ADD CONSTRAINT  XPKLTXSORTINGDEVICE PRIMARY KEY (LTXSORTINGDEVICE_ID) USING INDEX TABLESPACE INDX;

CREATE UNIQUE INDEX XAK1LTXSORTINGDEVICE ON LTXSORTINGDEVICE
(CODE   ASC) TABLESPACE INDX;

------------------------LTXSORTINGDEVICEMESSAGE--------------------

CREATE SEQUENCE SEQ_LTXSORTINGDEVICEMESSAGE
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGDEVICEMESSAGE
(
  LTXSORTINGDEVICEMESSAGE_ID NUMBER NOT NULL ,
  LTXSORTINGDEVICE_ID  NUMBER NOT NULL ,
  MESSAGETYPE          NUMBER NOT NULL  CONSTRAINT  MessageType_valid1 CHECK (MESSAGETYPE IN (0, 1)),
  ACTION               NUMBER NOT NULL,
  SEQUENCENUMBER       NUMBER DEFAULT  0  NOT NULL ,
  MESSAGE              VARCHAR2(256) NOT NULL ,
  DELAYTIME            NUMBER(4) DEFAULT  0  NOT NULL  CONSTRAINT  DelayTime_valid1 CHECK (DELAYTIME 
BETWEEN 0 AND 9999),
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


COMMENT ON COLUMN LTXSORTINGDEVICEMESSAGE.MESSAGETYPE IS '0 = Command
1 = Control Message';


COMMENT ON COLUMN LTXSORTINGDEVICEMESSAGE.ACTION IS 'For commands:
0 = Reset / start new load session
1 = Go to row / position
2 = Go to row
3 = Go to position
4 = Display
5 = Start unload
6 = Go to row / position, wait until user confirmation
7 = Go to row, wait until user confirmation
8 = Go to position, wait until user confirmation
9 = Scan error

For controlmessages:

0 = Ignore message
1 = Confirmation (command is executed)
2 = Device is busy
3 = Device is ready
4 = Error
5 = Fatal error, abort
6 = User pressed Ready
';

CREATE  INDEX XIF1LTXSORTINGDEVICEMESSAGE ON LTXSORTINGDEVICEMESSAGE
(LTXSORTINGDEVICE_ID   ASC) TABLESPACE INDX;

ALTER TABLE LTXSORTINGDEVICEMESSAGE
ADD CONSTRAINT  XPKLTXSORTINGDEVICEMESSAGE PRIMARY KEY (LTXSORTINGDEVICEMESSAGE_ID) USING INDEX TABLESPACE INDX;

ALTER TABLE LTXSORTINGDEVICEMESSAGE
ADD CONSTRAINT LTXSORTINGDEVICEMESSAGE_LTXSOR FOREIGN KEY (LTXSORTINGDEVICE_ID) REFERENCES LTXSORTINGDEVICE
(LTXSORTINGDEVICE_ID) ON DELETE CASCADE;

ALTER TABLE LTXSORTINGDEVICEMESSAGE
ADD CONSTRAINT MessageType_Action CHECK ((MESSAGETYPE = 0 and ACTION in (0,1,2,3,4,5,6,7,8,9)) or (MESSAGETYPE = 1 and ACTION in (0,1,2,3,4,5,6)));

------------------------LTXSORTINGSTATION--------------------

CREATE SEQUENCE SEQ_LTXSORTINGSTATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGSTATION
(
	LTXSORTINGSTATION_ID NUMBER NOT NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	LTXSORTINGDEVICE_ID  NUMBER NOT NULL ,
	PORTNUMBER           NUMBER DEFAULT  0  NOT NULL ,
	FILEPATH             VARCHAR2(80) NULL ,
	WORKSTATION_ID       NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



COMMENT ON COLUMN LTXSORTINGSTATION.PORTNUMBER IS '0 = Not applicable (IOType = 0 or 1)
else this is the port number (LPT1, COM1, USB1 etc.)';



COMMENT ON COLUMN LTXSORTINGSTATION.FILEPATH IS 'For IOType = 0 or 1 used to specify the directory where the file is created.';


ALTER TABLE LTXSORTINGSTATION
ADD CONSTRAINT  XPKLTXSORTINGSTATION PRIMARY KEY (LTXSORTINGSTATION_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTINGSTATION ON LTXSORTINGSTATION
(BUSINESSUNIT_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2LTXSORTINGSTATION ON LTXSORTINGSTATION
(LTXSORTINGDEVICE_ID   ASC) TABLESPACE INDX;


CREATE INDEX XIE1LTXSORTINGSTATION ON LTXSORTINGSTATION
(BUSINESSUNIT_ID   ASC,LTXSORTINGDEVICE_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTINGSTATION ON LTXSORTINGSTATION
(WORKSTATION_ID   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTINGSTATION
ADD CONSTRAINT LTXSORTINGSTATION_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);



ALTER TABLE LTXSORTINGSTATION
ADD CONSTRAINT LTXSORTINGSTATION_LTXSORTINGDE FOREIGN KEY (LTXSORTINGDEVICE_ID) REFERENCES LTXSORTINGDEVICE (LTXSORTINGDEVICE_ID);



ALTER TABLE LTXSORTINGSTATION
ADD CONSTRAINT LTXSORTINGSTATION_WORKSTATION FOREIGN KEY (WORKSTATION_ID) REFERENCES WORKSTATION (WORKSTATION_ID);


------------------------LTXSORTINGSTATIONLOCATION--------------------

CREATE SEQUENCE SEQ_LTXSORTINGSTATIONLOCATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGSTATIONLOCATION
(
  LTXSORTINGSTATIONLOCATION_ID NUMBER NOT NULL ,
  LTXSORTINGSTATION_ID NUMBER NOT NULL ,
  ROWNR                NUMBER NOT NULL  CONSTRAINT  Row_valid1 CHECK (ROWNR BETWEEN 0 AND 999),
  POSITION             NUMBER NOT NULL  CONSTRAINT  Position_valid1 CHECK (POSITION BETWEEN 0 AND 999),
  ROWCODE              VARCHAR2(6) NULL ,
  POSITIONCODE         VARCHAR2(6) NOT NULL ,
  ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_ValidationField36 CHECK (ACTIVE IN ('Y', 'N')),
  SYSTEMUSER_ID        NUMBER NOT NULL ,
  TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



COMMENT ON COLUMN LTXSORTINGSTATIONLOCATION.ROWCODE IS 'The row as it is put in the command line ([ROW]).';


COMMENT ON COLUMN LTXSORTINGSTATIONLOCATION.POSITIONCODE IS 'The position as it is put in the command line ([POS]).';


COMMENT ON COLUMN LTXSORTINGSTATIONLOCATION.ACTIVE IS 'Can be used to mark a location as out of service.';


ALTER TABLE LTXSORTINGSTATIONLOCATION
ADD CONSTRAINT  XPKLTXSORTINGSTATIONLOCATION PRIMARY KEY (LTXSORTINGSTATIONLOCATION_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTINGSTATIONLOCATION ON LTXSORTINGSTATIONLOCATION
(LTXSORTINGSTATION_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTINGSTATIONLOCATION ON LTXSORTINGSTATIONLOCATION
(LTXSORTINGSTATION_ID   ASC,ROWNR   ASC,POSITION   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTINGSTATIONLOCATION
ADD CONSTRAINT LTXSORTINGSTATIONLOCATION_LTXS FOREIGN KEY (LTXSORTINGSTATION_ID) REFERENCES LTXSORTINGSTATION (LTXSORTINGSTATION_ID) ON DELETE CASCADE;


------------------------LTXSORTSESSION--------------------

CREATE SEQUENCE SEQ_LTXSORTSESSION
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTSESSION
(
	LTXSORTSESSION_ID    NUMBER NOT NULL,
	LTXSORTINGSTATION_ID NUMBER NOT NULL,
	SORTTYPE	NUMBER NOT NULL CONSTRAINT  SortType_valid1 CHECK (SORTTYPE IN (0, 1)),
	BATCH_ID	NUMBER NOT NULL,
	CUSTOMER_ID	NUMBER NOT NULL,
	SYSTEMUSER_ID	NUMBER NOT NULL,
	TIMESTAMP	DATE NOT NULL
)
TABLESPACE ABS_DATA;

COMMENT ON COLUMN LTXSORTSESSION.SORTTYPE IS '0 = Presort
1 = Postsort';

ALTER TABLE LTXSORTSESSION
ADD CONSTRAINT  XPKLTXSORTSESSION PRIMARY KEY (LTXSORTSESSION_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTSESSION ON LTXSORTSESSION
(LTXSORTINGSTATION_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2LTXSORTSESSION ON LTXSORTSESSION
(BATCH_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF3LTXSORTSESSION ON LTXSORTSESSION
(CUSTOMER_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTSESSION ON LTXSORTSESSION
(LTXSORTINGSTATION_ID   ASC,BATCH_ID   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTSESSION
ADD CONSTRAINT LTXSORTSESSION_LTXSORTINGST FOREIGN KEY (LTXSORTINGSTATION_ID) REFERENCES LTXSORTINGSTATION (LTXSORTINGSTATION_ID);


ALTER TABLE LTXSORTSESSION
ADD CONSTRAINT LTXSORTSESSION_BATCH FOREIGN KEY (BATCH_ID) REFERENCES BATCH (BATCH_ID);


ALTER TABLE LTXSORTSESSION
ADD CONSTRAINT LTXSORTSESSION_CUSTOMER FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER (CUSTOMER_ID);


------------------------LTXSORTSESSIONLOCATION--------------------

CREATE SEQUENCE SEQ_LTXSORTSESSIONLOCATION
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTSESSIONLOCATION
(
	LTXSORTSESSIONLOCATION_ID NUMBER NOT NULL ,
	LTXSORTSESSION_ID NUMBER NOT NULL ,
	LTXSORTINGSTATIONLOCATION_ID	NUMBER NOT NULL ,
	SORTSTRING	VARCHAR2(80) NULL ,
	ROWCODE         VARCHAR2(6) NULL ,
	POSITIONCODE     VARCHAR2(6) NOT NULL ,
	ITEMSINLOCATION     NUMBER DEFAULT 0 NOT NULL ,
	STATUS   NUMBER DEFAULT 0 NOT NULL CONSTRAINT Status_valid4 CHECK (STATUS IN (0, 1)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


COMMENT ON COLUMN LTXSORTSESSIONLOCATION.STATUS IS '0 = Available / used
1 = Full';


ALTER TABLE LTXSORTSESSIONLOCATION
ADD CONSTRAINT  XPKLTXSORTSESSIONLOCATION PRIMARY KEY (LTXSORTSESSIONLOCATION_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTSESSIONLOCATION ON LTXSORTSESSIONLOCATION
(LTXSORTSESSION_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTSESSIONLOCATION ON LTXSORTSESSIONLOCATION
(LTXSORTINGSTATIONLOCATION_ID) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK2LTXSORTSESSIONLOCATION ON LTXSORTSESSIONLOCATION
(LTXSORTSESSION_ID   ASC,ROWCODE   ASC,POSITIONCODE   ASC) TABLESPACE INDX;


CREATE INDEX XIE1LTXSORTSESSIONLOCATION ON LTXSORTSESSIONLOCATION
(LTXSORTSESSION_ID   ASC,SORTSTRING   ASC,ROWCODE   ASC,POSITIONCODE   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTSESSIONLOCATION
ADD CONSTRAINT LTXSORTSESSIONLOCATION_LTXSE FOREIGN KEY (LTXSORTSESSION_ID) REFERENCES LTXSORTSESSION (LTXSORTSESSION_ID) ON DELETE CASCADE;


ALTER TABLE LTXSORTSESSIONLOCATION
ADD CONSTRAINT LTXSORTSESSIONLOCATION_LTXST FOREIGN KEY (LTXSORTINGSTATIONLOCATION_ID) REFERENCES LTXSORTINGSTATIONLOCATION (LTXSORTINGSTATIONLOCATION_ID);


------------------------LTXSORTSESSIONLOCCONTENTS--------------------

CREATE SEQUENCE SEQ_LTXSORTSESSIONLOCCONTENTS
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTSESSIONLOCCONTENTS
(
	LTXSORTSESSIONLOCCONTENTS_ID NUMBER NOT NULL ,
	LTXSORTSESSIONLOCATION_ID NUMBER NOT NULL ,
	CLIENT_ID            NUMBER NOT NULL ,
	CUSTOMERNAME         VARCHAR2(40) NOT NULL ,
	CLIENTNUMBER         NUMBER NOT NULL ,
	CLIENTSEARCHNAME     VARCHAR2(20) NOT NULL ,
	DEPARTMENTNUMBER     NUMBER NOT NULL ,
	DEPARTMENTDESCRIPTION VARCHAR2(30) NOT NULL ,
	DELIVERYPOINTCODE    VARCHAR2(12) NOT NULL ,
	ROOM                 VARCHAR2(12) NOT NULL ,
	CLIENTDELIVERYNOTE_ID NUMBER NULL,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE LTXSORTSESSIONLOCCONTENTS
ADD CONSTRAINT  XPKLTXSORTSESSIONLOCCONTENTS PRIMARY KEY (LTXSORTSESSIONLOCCONTENTS_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTSESSIONLOCCONTENTS ON LTXSORTSESSIONLOCCONTENTS
(LTXSORTSESSIONLOCATION_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2LTXSORTSESSIONLOCCONTENTS ON LTXSORTSESSIONLOCCONTENTS
(CLIENT_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF3LTXSORTSESSIONLOCCONTENTS ON LTXSORTSESSIONLOCCONTENTS
(CLIENTDELIVERYNOTE_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTSESSIONLOCCONTENTS ON LTXSORTSESSIONLOCCONTENTS
(LTXSORTSESSIONLOCATION_ID   ASC,CLIENT_ID   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTSESSIONLOCCONTENTS
ADD CONSTRAINT LTXSORTSESSIONLOCCONTENTS_CLIE FOREIGN KEY (CLIENT_ID) REFERENCES CLIENT (CLIENT_ID);


ALTER TABLE LTXSORTSESSIONLOCCONTENTS
ADD CONSTRAINT LTXSORTSESSIONLOCCONTENTS_LTXS FOREIGN KEY (LTXSORTSESSIONLOCATION_ID) REFERENCES LTXSORTSESSIONLOCATION (LTXSORTSESSIONLOCATION_ID) ON DELETE CASCADE;


ALTER TABLE LTXSORTSESSIONLOCCONTENTS
ADD CONSTRAINT LTXSORTSESSIONLOCCONT_CLIENTD FOREIGN KEY (CLIENTDELIVERYNOTE_ID) REFERENCES CLIENTDELIVERYNOTE 
(CLIENTDELIVERYNOTE_ID);


------------------------LTXSORTINGSCHEME--------------------

CREATE SEQUENCE SEQ_LTXSORTINGSCHEME
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGSCHEME
(
	LTXSORTINGSCHEME_ID  NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE LTXSORTINGSCHEME
ADD CONSTRAINT  XPKLTXSORTING PRIMARY KEY (LTXSORTINGSCHEME_ID) USING INDEX TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTING ON LTXSORTINGSCHEME
(CODE   ASC) TABLESPACE INDX;


------------------------LTXSORTINGSCHEMEDETAIL--------------------

CREATE SEQUENCE SEQ_LTXSORTINGSCHEMEDETAIL
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGSCHEMEDETAIL
(
	LTXSORTINGSCHEMEDETAIL_ID NUMBER NOT NULL ,
	LTXSORTINGSCHEME_ID  NUMBER NOT NULL ,
	PRIORITY             NUMBER(1) NOT NULL  CONSTRAINT  Priority_valid1 CHECK (PRIORITY BETWEEN 0 AND 9),
	FIELDNUMBER          NUMBER(1) NOT NULL  CONSTRAINT  FieldNumber_valid2 CHECK (FIELDNUMBER IN (1, 2, 3, 4, 5)),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


COMMENT ON COLUMN LTXSORTINGSCHEMEDETAIL.FIELDNUMBER IS '1 = Department number
2 = Room number
3 = Client number
4 = Client search name
5 = Delivery point code';


ALTER TABLE LTXSORTINGSCHEMEDETAIL
ADD CONSTRAINT  XPKLTXSORTINGSCHEMEDETAIL PRIMARY KEY (LTXSORTINGSCHEMEDETAIL_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTINGSCHEMEDETAIL ON LTXSORTINGSCHEMEDETAIL
(LTXSORTINGSCHEME_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTINGSCHEMEDETAIL ON LTXSORTINGSCHEMEDETAIL
(LTXSORTINGSCHEME_ID   ASC,PRIORITY   ASC) TABLESPACE INDX;



CREATE UNIQUE INDEX XAK2LTXSORTINGSCHEMEDETAIL ON LTXSORTINGSCHEMEDETAIL
(LTXSORTINGSCHEME_ID   ASC,FIELDNUMBER   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTINGSCHEMEDETAIL
ADD CONSTRAINT LTXSORTINGSCHEMEDETAIL_LTXSORT FOREIGN KEY (LTXSORTINGSCHEME_ID) REFERENCES LTXSORTINGSCHEME (LTXSORTINGSCHEME_ID) ON DELETE CASCADE;


------------------------LTXSORTINGMETHOD--------------------

CREATE SEQUENCE SEQ_LTXSORTINGMETHOD
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGMETHOD
(
	LTXSORTINGMETHOD_ID  NUMBER NOT NULL ,
	CODE                 VARCHAR2(6) NOT NULL ,
	DESCRIPTION          VARCHAR2(30) NOT NULL ,
	SORTTYPE             NUMBER NOT NULL  CONSTRAINT  SortType_valid2 CHECK (SORTTYPE IN (0, 1)),
	LTXSORTINGSCHEME_ID  NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



COMMENT ON COLUMN LTXSORTINGMETHOD.SORTTYPE IS '0 = Pre-sort
1 = Post-sort';


ALTER TABLE LTXSORTINGMETHOD
ADD CONSTRAINT  XPKLTXSORTINGMETHOD PRIMARY KEY (LTXSORTINGMETHOD_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTINGMETHOD ON LTXSORTINGMETHOD
(LTXSORTINGSCHEME_ID   ASC) TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1LTXSORTINGMETHOD ON LTXSORTINGMETHOD
(CODE   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTINGMETHOD ADD CONSTRAINT CODE_UPPERCASE1
	CHECK (CODE = UPPER(CODE));


ALTER TABLE LTXSORTINGMETHOD
ADD CONSTRAINT LTXSORTINGMETHOD_LTXSORTINGSCH FOREIGN KEY (LTXSORTINGSCHEME_ID) REFERENCES LTXSORTINGSCHEME (LTXSORTINGSCHEME_ID);


------------------------LTXSORTINGMETHODDEVICE--------------------

CREATE SEQUENCE SEQ_LTXSORTINGMETHODDEVICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE LTXSORTINGMETHODDEVICE
(
	LTXSORTINGMETHODDEVICE_ID NUMBER NOT NULL,
	LTXSORTINGMETHOD_ID  NUMBER NOT NULL ,
	LTXSORTINGDEVICE_ID  NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;


ALTER TABLE LTXSORTINGMETHODDEVICE
ADD CONSTRAINT  XPKLTXSORTINGMETHODDEVICE PRIMARY KEY (LTXSORTINGMETHODDEVICE_ID) USING INDEX TABLESPACE INDX;


CREATE  INDEX XIF1LTXSORTINGMETHODDEVICE ON LTXSORTINGMETHODDEVICE
(LTXSORTINGMETHOD_ID   ASC) TABLESPACE INDX;


CREATE  INDEX XIF2LTXSORTINGMETHODDEVICE ON LTXSORTINGMETHODDEVICE
(LTXSORTINGDEVICE_ID   ASC) TABLESPACE INDX;


ALTER TABLE LTXSORTINGMETHODDEVICE
ADD CONSTRAINT LTXSORTINGMETHODDEV_LTXSORTME FOREIGN KEY (LTXSORTINGMETHOD_ID) REFERENCES LTXSORTINGMETHOD (LTXSORTINGMETHOD_ID) ON DELETE CASCADE;


ALTER TABLE LTXSORTINGMETHODDEVICE
ADD CONSTRAINT LTXSORTINGMETHODDEV_LTXSORTDE FOREIGN KEY (LTXSORTINGDEVICE_ID) REFERENCES LTXSORTINGDEVICE (LTXSORTINGDEVICE_ID);
