--20010787.70

--changes on customer table

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

ALTER TABLE CUSTOMER
	ADD (PHASEDDELIVERYWO NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT PhasedDelWO_valid CHECK (PHASEDDELIVERYWO IN (0, 1, 2)),
	     PHASEDDELIVERYSALESNOTE NUMBER(1)  DEFAULT 0 NOT NULL CONSTRAINT PhasedDelSN_valid CHECK (PHASEDDELIVERYSALESNOTE IN (0, 1, 2)));

     
COMMENT ON COLUMN CUSTOMER.PHASEDDELIVERYWO IS '0 = Phased delivery allowed, default no phased delivery
1 = Phased delivery allowed and default
2 = Phased delivery never allowed';



COMMENT ON COLUMN CUSTOMER.PHASEDDELIVERYSALESNOTE IS '0 = Phased delivery allowed, default no phased delivery
1 = Phased delivery allowed and default
2 = Phased delivery never allowed';

--changes on workorder table

ALTER TABLE WORKORDER
	ADD CONSTRAINT Availability_valid  CHECK (AVAILABILITY IN (0, 1, 2, 3, 4, 5, 6));

ALTER TABLE WORKORDER
	ADD (AVAILABILITYTIMESTAMP DATE NULL);

COMMENT ON COLUMN WORKORDER.AVAILABILITY IS '0 = Not defined / not applicable
1 = Available
2 = Available with shared reservation
3 = Not used anymore
4 = Not available
5 = Partially available
6 = Partially available with shared reservation';

--added new table

CREATE TABLE WORKORDERSPLIT
(
	WORKORDERSPLIT_ID    NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL ,
	CHILDWORKORDER_ID    NUMBER NOT NULL ,
	PARENTWORKORDER_ID   NUMBER NOT NULL 
)TABLESPACE ABS_DATA;

CREATE SEQUENCE SEQ_WORKORDERSPLIT
START WITH 1 
INCREMENT BY 1;

ALTER TABLE WORKORDERSPLIT
ADD CONSTRAINT XPKWORKORDERSPLIT PRIMARY KEY (WORKORDERSPLIT_ID)
USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF2WORKORDERSPLIT ON WORKORDERSPLIT
(CHILDWORKORDER_ID   ASC) TABLESPACE INDX;

CREATE  INDEX XIF3WORKORDERSPLIT ON WORKORDERSPLIT
(PARENTWORKORDER_ID   ASC) TABLESPACE INDX;

ALTER TABLE WORKORDERSPLIT
ADD (CONSTRAINT WORKORDERSPLIT_CHILDWO FOREIGN KEY (CHILDWORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE);

ALTER TABLE WORKORDERSPLIT
ADD (CONSTRAINT WORKORDERSPLIT_PARENTWO FOREIGN KEY (PARENTWORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE);

--changes on workorderline table

ALTER TABLE WORKORDERLINE
	ADD (AVAILABILITY NUMBER  DEFAULT 0 NOT NULL CONSTRAINT Availability_valid_1 CHECK (AVAILABILITY IN (0, 1, 2, 3, 4, 5, 6)));

COMMENT ON COLUMN WORKORDERLINE.AVAILABILITY IS '0 = Not applicable / not calculated
1 = Available
2 = Available with shared reservation
3 = Not used (kept the same with workorder.availability)
4 = Not available
5 = Partially Available
6 = Partially available with shared reservation';

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;       
