--ABSN-1750

--CUSTOMEREXTRAINFO TABLE
ALTER TABLE CUSTOMEREXTRAINFO ADD (NUMBEROFPERIODS NUMBER DEFAULT 1 NOT NULL,
                                   REVENUECALCMETHOD NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_CUSTEXTRAINFO_REVENUECALCMETHOD CHECK (REVENUECALCMETHOD IN (0, 1)));

COMMENT ON COLUMN CUSTOMEREXTRAINFO.NUMBEROFPERIODS IS 'Number of periods revenue is used for the calculation process. Only used for module REVENUEBASEDPRICING';
 
COMMENT ON COLUMN CUSTOMEREXTRAINFO.REVENUECALCMETHOD IS 'Only used for module REVENUEBASEDPRICING.
                                                          0 = divide by the number of days in the period
                                                          1 = divide by the number of days deliveries were done in the period (so from packing note table)';

--GENERALPRICELISTRPBAND TABLE  
CREATE TABLE GENERALPRICELISTRPBAND(	
GENERALPRICELIST_ID	    NUMBER NOT NULL,
CODE					VARCHAR2(6) NOT NULL,
DESCRIPTION			    VARCHAR2(20) NOT NULL,
DAILYREVENUETHRESHOLD	NUMBER DEFAULT 0 NOT NULL,
ACTIVE					VARCHAR2(1) DEFAULT 'Y' NOT NULL,
TIMESTAMP				DATE NOT NULL,
SYSTEMUSER_ID			NUMBER NOT NULL);                                                   

ALTER TABLE GENERALPRICELISTRPBAND ADD CONSTRAINT XPKGENERALPRICELISTRPBAND PRIMARY KEY (GENERALPRICELIST_ID, CODE);
																  
CREATE INDEX XIF1GENERALPRICELISTRPBAND ON GENERALPRICELISTRPBAND (GENERALPRICELIST_ID ASC);

ALTER TABLE GENERALPRICELISTRPBAND
	ADD CONSTRAINT FK_GENPRICELSTRPBAND_GENPRICELST_ID FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;

ALTER TABLE GENERALPRICELISTRPBAND	
	ADD CONSTRAINT CHK_GENPRICELSTRPBAND_ACTIVE CHECK (ACTIVE IN ('Y', 'N'));


--RPBANDCALCULATIONHIST TABLE
CREATE SEQUENCE SEQ_RPBANDCALCULATIONHIST
START WITH 1
INCREMENT BY 1;

CREATE TABLE RPBANDCALCULATIONHIST(
RPBANDCALCULATIONHIST_ID NUMBER NOT NULL,
GENERALPRICELIST_ID		 NUMBER NOT NULL,
CUSTOMER_LINK_ID		 NUMBER NULL,
CALCULATIONDATE			 DATE NOT NULL,
AVERAGEREVENUEPERIOD1	 NUMBER NULL,
AVERAGEREVENUEPERIOD2	 NUMBER NULL,
AVERAGEREVENUEPERIOD3	 NUMBER NULL,
AVERAGEREVENUEPERIOD4	 NUMBER NULL,
AVERAGEREVENUEPERIOD5	 NUMBER NULL,
AVERAGEREVENUEPERIOD6    NUMBER NULL,
MANUALCHANGE			 VARCHAR2(1) DEFAULT 'N' NOT NULL,
CURRENTBANDCODE		     VARCHAR2(6) NULL,
LOCKEDUNTILLDATE	     DATE NULL,
NEWBANDCODE				 VARCHAR2(6) NULL,
NUMBEROFPERIODS          NUMBER NOT NULL,
TIMESTAMP				 DATE NOT NULL,
SYSTEMUSER_ID			 NUMBER NOT NULL);          

ALTER TABLE RPBANDCALCULATIONHIST ADD CONSTRAINT XPKRPBANDCALCULATIONHIST PRIMARY KEY (RPBANDCALCULATIONHIST_ID);

CREATE INDEX XIF1RPBANDCALCULATIONHIST ON RPBANDCALCULATIONHIST (GENERALPRICELIST_ID ASC);
CREATE INDEX XIF2RPBANDCALCULATIONHIST ON RPBANDCALCULATIONHIST (CUSTOMER_LINK_ID ASC);

ALTER TABLE RPBANDCALCULATIONHIST 
	ADD CONSTRAINT FK_RPBANDCALCHIST_GENPRICELST_ID FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID);

ALTER TABLE RPBANDCALCULATIONHIST 
	ADD CONSTRAINT FK_RPBANDCALCHIST_CUST_LINK_ID FOREIGN KEY (CUSTOMER_LINK_ID) REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

ALTER TABLE RPBANDCALCULATIONHIST	
	ADD CONSTRAINT CHK_RPBANDCALCULATIONHIST_MANCHANGE CHECK (MANUALCHANGE IN ('Y', 'N'));

COMMENT ON COLUMN RPBANDCALCULATIONHIST.CUSTOMER_LINK_ID IS 'Note this may be the master account or customer'; 
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD1 IS 'Average revenue one period ago.'; 
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD2 IS 'Average revenue 2 periods ago.';
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD3 IS 'Average revenue 3 periods ago.';
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD4 IS 'Average revenue 4 periods ago.';
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD5 IS 'Average revenue 5 periods ago.';
COMMENT ON COLUMN RPBANDCALCULATIONHIST.AVERAGEREVENUEPERIOD6 IS 'Average revenue 6 periods ago.';
COMMENT ON COLUMN RPBANDCALCULATIONHIST.NUMBEROFPERIODS IS 'Number of periods the revenue calculation is run for.';  


--RPBANDPRICE TABLE
CREATE SEQUENCE SEQ_RPBANDPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE RPBANDPRICE(
RPBANDPRICE_ID		  NUMBER NOT NULL,
GENERALPRICELIST_ID	  NUMBER NOT NULL,
CODE                  VARCHAR2(6) NOT NULL,
PRODUCT_ID			  NUMBER NOT NULL,
SIZEDEFINITION_ID	  NUMBER NOT NULL,
FINISHINGMETHOD_ID	  NUMBER NOT NULL,
PRICE				  NUMBER(15,6) NOT NULL,
STARTDATE			  DATE NOT NULL,
PRICETYPE			  NUMBER NOT NULL,
CREATIONUSER_ID	      NUMBER NOT NULL,
CREATIONTIMESTAMP     DATE NOT NULL,
SYSTEMUSER_ID		  NUMBER NOT NULL,
TIMESTAMP			  DATE NOT NULL);          

ALTER TABLE RPBANDPRICE ADD CONSTRAINT XPKRPBANDPRICE PRIMARY KEY (RPBANDPRICE_ID);

CREATE UNIQUE INDEX XAK1RPBANDPRICE ON RPBANDPRICE (GENERALPRICELIST_ID ASC, CODE ASC, PRODUCT_ID ASC, SIZEDEFINITION_ID ASC, FINISHINGMETHOD_ID ASC, PRICETYPE ASC, STARTDATE ASC);

CREATE INDEX XIF1RPBANDPRICE ON RPBANDPRICE (GENERALPRICELIST_ID ASC, CODE ASC);
CREATE INDEX XIF2RPBANDPRICE ON RPBANDPRICE (PRODUCT_ID ASC);
CREATE INDEX XIF3RPBANDPRICE ON RPBANDPRICE (SIZEDEFINITION_ID ASC);
CREATE INDEX XIF4RPBANDPRICE ON RPBANDPRICE (FINISHINGMETHOD_ID ASC);

ALTER TABLE RPBANDPRICE 
	ADD CONSTRAINT FK_RPBANDPRICE_GENPRCLISTRPBAND_ID FOREIGN KEY (GENERALPRICELIST_ID, CODE) REFERENCES GENERALPRICELISTRPBAND(GENERALPRICELIST_ID, CODE) ON DELETE CASCADE;

ALTER TABLE RPBANDPRICE 
	ADD CONSTRAINT FK_RPBANDPRICE_PRODUCT_ID FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCT(PRODUCT_ID);
  	
ALTER TABLE RPBANDPRICE 
	ADD CONSTRAINT FK_RPBANDPRICE_SIZEDEF_ID FOREIGN KEY (SIZEDEFINITION_ID) REFERENCES SIZEDEFINITION(SIZEDEFINITION_ID);

ALTER TABLE RPBANDPRICE 
	ADD CONSTRAINT FK_RPBANDPRICE_FINMETHOD_ID FOREIGN KEY (FINISHINGMETHOD_ID) REFERENCES FINISHINGMETHOD(FINISHINGMETHOD_ID);
	
COMMENT ON COLUMN RPBANDPRICE.PRICETYPE IS '1 = All items 2 = COG items only 4 = Split price 5 = Heavy Soil';


--ACTIVEREVENUEPRICEBAND TABLE
CREATE TABLE ACTIVEREVENUEPRICEBAND(
GENERALPRICELIST_ID NUMBER NOT NULL,
BANDCODE            VARCHAR2(6) NOT NULL,              
LOCKEDUNTIL         DATE NULL,
LASTBANDCHANGEDATE  DATE NOT NULL,
TIMESTAMP           DATE NOT NULL,
SYSTEMUSER_ID       NUMBER NOT NULL);

ALTER TABLE ACTIVEREVENUEPRICEBAND ADD CONSTRAINT XPKACTIVEREVENUEPRICEBAND PRIMARY KEY (GENERALPRICELIST_ID);

ALTER TABLE ACTIVEREVENUEPRICEBAND
  ADD CONSTRAINT FK_ACTIVEREVENUEPRICEBAND_GENPRICELST_ID FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID) ON DELETE CASCADE;

COMMENT ON COLUMN ACTIVEREVENUEPRICEBAND.BANDCODE IS 'Current band code for this pricelist';

--CUSTOMERACTIVERPBAND TABLE
CREATE TABLE CUSTOMERACTIVERPBAND(
GENERALPRICELIST_ID 	  NUMBER NOT NULL, 
LOCKEDUNTIL               DATE NULL,
LASTBANDCHANGEDATE        DATE NOT NULL,
TIMESTAMP                 DATE NOT NULL,
SYSTEMUSER_ID             NUMBER NOT NULL);

ALTER TABLE CUSTOMERACTIVERPBAND ADD CONSTRAINT XPKCUSTOMERACTIVERPBAND PRIMARY KEY (GENERALPRICELIST_ID);

ALTER TABLE CUSTOMERACTIVERPBAND
  ADD CONSTRAINT FK_CUSTOMERACTIVERPBAND_GENERALPRLIST FOREIGN KEY (GENERALPRICELIST_ID) REFERENCES GENERALPRICELIST (GENERALPRICELIST_ID);
