--Order 20011175.90

-------------------BUSINESSUNIT-------------------

ALTER TABLE BUSINESSUNIT
	ADD (WOREFERENCENUMBERPREFIX VARCHAR2(6) NULL);



ALTER TABLE BUSINESSUNIT
	ADD (WOREFERENCENUMBERLENGTH NUMBER NULL);



------------------WOREFERENCENUMBER----------------

CREATE SEQUENCE SEQ_WOREFERENCENUMBER
START WITH 1
INCREMENT BY 1;


CREATE TABLE WOREFERENCENUMBER
(
	WOREFERENCENUMBER_ID NUMBER NOT NULL ,
	BUSINESSUNIT_ID      NUMBER NOT NULL ,
	REFERENCENUMBER      NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(25) NOT NULL ,
	DATEINACTIVE         DATE DEFAULT  sysdate + 7  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
) 
TABLESPACE ABS_DATA;



COMMENT ON COLUMN WOREFERENCENUMBER.REFERENCENUMBER IS 'Number that uniquely identifies a reference for a work order. Will be generated based on the note number settings as defined for the business unit in which the reference will be added to the application. This generated reference number will be manually connected to one or more customers.';


ALTER TABLE WOREFERENCENUMBER
ADD CONSTRAINT  XPKWOREFERENCENUMBER PRIMARY KEY (WOREFERENCENUMBER_ID) USING INDEX TABLESPACE INDX;



CREATE UNIQUE INDEX XAK1WOREFERENCENUMBER ON WOREFERENCENUMBER
(BUSINESSUNIT_ID   ASC,REFERENCENUMBER   ASC) TABLESPACE INDX;



CREATE  INDEX XIF1WOREFERENCENUMBER ON WOREFERENCENUMBER
(BUSINESSUNIT_ID   ASC) TABLESPACE INDX;



ALTER TABLE WOREFERENCENUMBER
ADD CONSTRAINT WOREFERENCENUMBER_BUSINESSUNIT FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);


---------------WOREFERENCECUSTOMER------------------

CREATE SEQUENCE SEQ_WOREFERENCECUSTOMER
START WITH 1
INCREMENT BY 1;


CREATE TABLE WOREFERENCECUSTOMER
(
	WOREFERENCECUSTOMER_ID NUMBER NOT NULL ,
	WOREFERENCENUMBER_ID NUMBER NOT NULL ,
	CUSTOMER_ID          NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)
TABLESPACE ABS_DATA;



ALTER TABLE WOREFERENCECUSTOMER
ADD CONSTRAINT  XPKWOREFERENCECUSTOMER PRIMARY KEY (WOREFERENCECUSTOMER_ID) USING INDEX TABLESPACE INDX;



CREATE UNIQUE INDEX XAK1WOREFERENCECUSTOMER ON WOREFERENCECUSTOMER
(WOREFERENCENUMBER_ID   ASC,CUSTOMER_ID   ASC) TABLESPACE INDX;



CREATE  INDEX XIF1WOREFERENCECUSTOMER ON WOREFERENCECUSTOMER
(WOREFERENCENUMBER_ID   ASC) TABLESPACE INDX;



CREATE  INDEX XIF2WOREFERENCECUSTOMER ON WOREFERENCECUSTOMER
(CUSTOMER_ID   ASC) TABLESPACE INDX;



ALTER TABLE WOREFERENCECUSTOMER
ADD CONSTRAINT WOREFCUSTOMER_WOREFNUMBER FOREIGN KEY (WOREFERENCENUMBER_ID) REFERENCES WOREFERENCENUMBER (WOREFERENCENUMBER_ID);



ALTER TABLE WOREFERENCECUSTOMER
ADD CONSTRAINT WOREFCUSTOMER_CUSTOMER_LINK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);



-------------------WORKORDER-------------------

ALTER TABLE WORKORDER
	ADD (WOREFERENCENUMBER_ID NUMBER NULL);



CREATE  INDEX XIF24WORKORDER ON WORKORDER
(WOREFERENCENUMBER_ID   ASC) TABLESPACE INDX;



ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_WOREFNUMBER FOREIGN KEY (WOREFERENCENUMBER_ID) REFERENCES WOREFERENCENUMBER (WOREFERENCENUMBER_ID);






