--20010808.40

--------------------WEBORDER TABLE---------------------

CREATE TABLE WEBORDER
(
	WEBORDER_ID          NUMBER NOT NULL ,
	WEBORDERNUMBER       NUMBER NOT NULL ,
	PROCESSINGBUSINESSUNIT_ID NUMBER NOT NULL ,
	CUSTOMER_LINK_ID     NUMBER NOT NULL ,
	ORDERENTRYDATE       DATE DEFAULT  to_date('31/12/2099','dd/mm/yyyy')  NOT NULL ,
	DELIVERYDATE         DATE DEFAULT  to_date('31/12/2099','dd/mm/yyyy')  NOT NULL ,
	CUSTOMERREFERENCE    VARCHAR2(15) NULL ,
	OVERRULEDELSTD       VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  OVERRULEDELSTD_valid CHECK (OVERRULEDELSTD IN ('Y', 'N')),
	STATUS               NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  Status_valid6 CHECK (STATUS IN (0, 1)),
	CREATIONWEBUSER_ID   NUMBER NOT NULL ,
	WEBUSER_ID           NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)TABLESPACE ABS_DATA;


CREATE SEQUENCE SEQ_WEBORDER
START WITH 1
INCREMENT BY 1;

COMMENT ON COLUMN WEBORDER.STATUS IS '0 = Entry in progress
1 = Confirmed
';

ALTER TABLE WEBORDER
ADD CONSTRAINT  XPKWEBORDER PRIMARY KEY (WEBORDER_ID) USING INDEX TABLESPACE INDX;


CREATE UNIQUE INDEX XAK1WEBORDER ON WEBORDER
(WEBORDERNUMBER   ASC)TABLESPACE INDX;


CREATE  INDEX XIF1WEBORDER ON WEBORDER
(PROCESSINGBUSINESSUNIT_ID   ASC)TABLESPACE INDX;


CREATE  INDEX XIF2WEBORDER ON WEBORDER
(CUSTOMER_LINK_ID   ASC)TABLESPACE INDX;


ALTER TABLE WEBORDER
ADD CONSTRAINT WEBORDER_BUSINESSUNIT FOREIGN KEY (PROCESSINGBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);



ALTER TABLE WEBORDER
ADD CONSTRAINT WEBORDER_CUSTLINK FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);



---------------------------------WEBORDERLINE----------------------------

CREATE TABLE WEBORDERLINE
(
	WEBORDERLINE_ID      NUMBER NOT NULL ,
	WEBORDER_ID          NUMBER NOT NULL ,
	DISTRIBUTIONPOINT_LINK_ID NUMBER NOT NULL ,
	PRODUCT_LINK_ID      NUMBER NOT NULL ,
	SIZEDEFINITION_LINK_ID NUMBER NOT NULL ,
	FINISHINGMETHOD_ID   NUMBER NOT NULL ,
	QUANTITYORDERED      NUMBER NULL ,
	CREATIONWEBUSER_ID   NUMBER NOT NULL ,
	WEBUSER_ID           NUMBER NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
)TABLESPACE ABS_DATA;

CREATE SEQUENCE SEQ_WEBORDERLINE
START WITH 1
INCREMENT BY 1;



ALTER TABLE WEBORDERLINE
ADD CONSTRAINT  XPKWEBORDERLINE PRIMARY KEY (WEBORDERLINE_ID) USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF1WEBORDERLINE ON WEBORDERLINE
(WEBORDER_ID   ASC)TABLESPACE INDX;

CREATE  INDEX XIF2WEBORDERLINE ON WEBORDERLINE
(DISTRIBUTIONPOINT_LINK_ID   ASC)TABLESPACE INDX;

CREATE  INDEX XIF3WEBORDERLINE ON WEBORDERLINE
(PRODUCT_LINK_ID    ASC)TABLESPACE INDX;

CREATE  INDEX XIF4WEBORDERLINE ON WEBORDERLINE
(SIZEDEFINITION_LINK_ID   ASC)TABLESPACE INDX;

CREATE  INDEX XIF5WEBORDERLINE ON WEBORDERLINE
(FINISHINGMETHOD_ID  ASC)TABLESPACE INDX;

CREATE UNIQUE INDEX XAK1WEBORDERLINE ON WEBORDERLINE
(WEBORDER_ID   ASC,DISTRIBUTIONPOINT_LINK_ID   ASC,PRODUCT_LINK_ID   ASC,SIZEDEFINITION_LINK_ID   ASC,FINISHINGMETHOD_ID   ASC)TABLESPACE INDX;

ALTER TABLE WEBORDERLINE
ADD CONSTRAINT WEBORDERLINE_WEBORDER FOREIGN KEY (WEBORDER_ID) REFERENCES WEBORDER (WEBORDER_ID) ON DELETE CASCADE;

ALTER TABLE WEBORDERLINE
ADD CONSTRAINT WEBORDERLINE_DISTRPOINTLINK FOREIGN KEY (DISTRIBUTIONPOINT_LINK_ID) REFERENCES DISTRIBUTIONPOINT_LINK (DISTRIBUTIONPOINT_LINK_ID);

ALTER TABLE WEBORDERLINE
ADD CONSTRAINT WEBORDERLINE_PRODUCTLINK FOREIGN KEY (PRODUCT_LINK_ID) REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);

ALTER TABLE WEBORDERLINE
ADD CONSTRAINT WEBORDERLINE_SIZEDEFLINK FOREIGN KEY (SIZEDEFINITION_LINK_ID) REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

ALTER TABLE WEBORDERLINE
ADD CONSTRAINT WEBORDERLINE_FINISHMETH FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD (FINISHINGMETHOD_ID);




-----------------------------DELIVERYVARIATIONS TABLE--------------------------





ALTER TABLE DELIVERYVARIATIONS
	ADD (WEBORDERLINE_ID NUMBER);

DROP INDEX XAK2DELIVERYVARIATIONS;

CREATE UNIQUE INDEX XAK2DELIVERYVARIATIONS ON DELIVERYVARIATIONS
	(DISTRIBUTIONPOINT_ID   ASC,
	PRODUCT_ID   		ASC,
	SIZEDEFINITION_ID   	ASC,
	FINISHINGMETHOD_ID   	ASC,
	PACKINGNOTE_ID   	ASC,
	PROCESSED   		ASC,
	DELIVERYDATE   		ASC,
	ORIGIN   		ASC,
	CAUSENOTE_ID   		ASC,
	UPDATERENTQTY   	ASC,
	SOILCOUNTSUBTYPE   	ASC,
	WEBORDERLINE_ID   	ASC)TABLESPACE INDX;



CREATE  INDEX XIF9DELIVERYVARIATIONS ON DELIVERYVARIATIONS
(WEBORDERLINE_ID   ASC)TABLESPACE INDX;



ALTER TABLE DELIVERYVARIATIONS
ADD CONSTRAINT DELVAR_WEBORDERLINE FOREIGN KEY (WEBORDERLINE_ID) REFERENCES WEBORDERLINE (WEBORDERLINE_ID) ON DELETE SET NULL;


------------------------------------------WEBEVENTTYPE TABLE-----------------------------------

INSERT INTO WEBEVENTTYPE 
  SELECT 80, 'Add Order', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
INSERT INTO WEBEVENTTYPE 
  SELECT 81, 'Edit Order', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
INSERT INTO WEBEVENTTYPE 
  SELECT 82, 'Confirm Order', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
INSERT INTO WEBEVENTTYPE 
  SELECT 83, 'Revoke Order Confirmation', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;
INSERT INTO WEBEVENTTYPE 
  SELECT 84, 'Revoke Confirmation Failed', GETMINSYSTEMUSER(), SYSDATE FROM DUAL;




