-- ABS-57743 / ABS-62264
-------   INVOICELINESUBTYPE   -----------
INSERT INTO INVOICELINESUBTYPE
  (INVOICELINESUBTYPE_ID,
   SUBTYPE,
   INVOICELINETYPE_ID,
   FACTORYDESCRIPTION,
   SYSTEMUSER_ID,
   TIMESTAMP)
  SELECT SEQ_INVOICELINESUBTYPE.NEXTVAL,
         47,
         2,
         'Wash overuse',
         GETMINSYSTEMUSER(),
         SYSDATE
    FROM DUAL;
COMMIT;

INSERT INTO INVOICELINESUBTYPE_DESC
      (INVOICELINESUBTYPE_ID,
       LANGUAGE_ID,
       DESCRIPTION,
       SYSTEMUSER_ID,
       TIMESTAMP)
      SELECT I.INVOICELINESUBTYPE_ID,
             L.LANGUAGE_ID,
             I.FACTORYDESCRIPTION,
             GETMINSYSTEMUSER(),
             SYSDATE
        FROM INVOICELINESUBTYPE I, LANGUAGES L
       WHERE L.TRANSLATEDESCRIPTION = 'Y' AND I.SUBTYPE = 47 AND I.INVOICELINETYPE_ID = 2;
COMMIT;

-------   PRODUCTLISTPERDISTRIBPOINT   -----------
ALTER TABLE PRODUCTLISTPERDISTRIBPOINT ADD (FREEQTYPERIOD NUMBER DEFAULT 0 NOT NULL
					    CONSTRAINT FREEQTYPERIOD_VALID1_1 CHECK (FREEQTYPERIOD IN (0,1)));

COMMENT ON COLUMN PRODUCTLISTPERDISTRIBPOINT.FREEQTYPERIOD IS '0 = Not applicable
1 = Per year
It can only have a different value when FreeQtyFrequency = 2';

COMMENT ON COLUMN PRODUCTLISTPERDISTRIBPOINT.FREEQTYFREQUENCY IS '0 = Per Week / Month / 4 Weeks
1 = Per Invoice
2 = Fixed Period';

ALTER TABLE PRODUCTLISTPERDISTRIBPOINT ADD CONSTRAINT FREEQTYFREQUENCY_VALID1_1 CHECK (FREEQTYFREQUENCY IN (0,1,2));

-------   EMAILDOCUMENT   -----------
CREATE SEQUENCE SEQ_EMAILDOCUMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE EMAILDOCUMENT
(
EMAILDOCUMENT_ID NUMBER NOT NULL,
PLANT_ID NUMBER NOT NULL,
TOADDRESS VARCHAR2(4000) NOT NULL,
CCADDRESS VARCHAR2(4000) NULL,
BCCADDRESS VARCHAR2(4000) NULL,
FROMADDRESS VARCHAR2(255) NULL,
STATUS NUMBER NOT NULL CONSTRAINT STATUS_VALID2_1 CHECK (STATUS IN (1,2,3)),
EMAILSETTING_ID NUMBER NULL,
SUBJECT VARCHAR2(100) NULL,
ATTACHMENT BLOB NULL,
BODY VARCHAR2(4000) NOT NULL,
SOURCE VARCHAR2(50) NOT NULL,
CUSTOMER_LINK_ID NUMBER NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN EMAILDOCUMENT.STATUS IS '1 = ready to send
2 = sent
3 = send failed';

COMMENT ON COLUMN EMAILDOCUMENT.BODY IS 'Fully generated by the source application including replacement of parameters with values';

COMMENT ON COLUMN EMAILDOCUMENT.SOURCE IS 'Text field written by the process that creates the email';

ALTER TABLE EMAILDOCUMENT ADD CONSTRAINT XPKEMAILDOCUMENT PRIMARY KEY (EMAILDOCUMENT_ID);

CREATE INDEX XIF1EMAILDOCUMENT ON EMAILDOCUMENT (PLANT_ID ASC);
CREATE INDEX XIF2EMAILDOCUMENT ON EMAILDOCUMENT (EMAILSETTING_ID ASC);
CREATE INDEX XIF3EMAILDOCUMENT ON EMAILDOCUMENT (CUSTOMER_LINK_ID ASC);

ALTER TABLE EMAILDOCUMENT ADD CONSTRAINT EMAILDOCUMENT_PID FOREIGN KEY (PLANT_ID)
REFERENCES PLANT (PLANT_ID) ON DELETE CASCADE;

ALTER TABLE EMAILDOCUMENT ADD CONSTRAINT EMAILDOCUMENT_ESID FOREIGN KEY (EMAILSETTING_ID)
REFERENCES EMAILSETTING (EMAILSETTING_ID) ON DELETE SET NULL;

ALTER TABLE EMAILDOCUMENT ADD CONSTRAINT EMAILDOCUMENT_CID FOREIGN KEY (CUSTOMER_LINK_ID)
REFERENCES CUSTOMER_LINK (CUSTOMER_LINK_ID);

----------   EMAILSETTINGS   -----------
ALTER TABLE EMAILSETTING DROP CONSTRAINT DOCUMENTTYPEEMAILSETT_VALID;

COMMENT ON COLUMN EMAILSETTING.DOCUMENTTYPE IS '1 = Invoice
2 = Statement
3 = Purchase order
4 = E-service password reminder
5 = Work order via web
6 = Invoice job checklist
7 = Customer
8 = Log Message
9 = Delivery Note / Consignment Note
10 = Stock Count / Order
11 = Wash List
12 = POD (Proof of delivery)
13 = On site soil scan list
14 = Shipment List
15 = Commission Report
16 = Wearer Web Login Credentials
17 = Recalculation Norm Stock Levels
18 = Recalculation Standard Deliveries
19 = Incidental Inventory
20 = Container Usage Report
21 = (Succesfully) Processed OR Packs
22 = Unattended UHF Reader Status Changed
23 = Future planned deliveries
24 = Client Invoice
25 = Overuse notification';

ALTER TABLE EMAILSETTING ADD CONSTRAINT DOCUMENTTYPE_VALID2_1 CHECK (DOCUMENTTYPE BETWEEN 1 AND 25);

----------   PROCESS   -----------
INSERT INTO PROCESS (PROCESS_ID, DESCRIPTION, ACTIVE)
VALUES (143, 'Automatic renewal of periodical free-of-charge adjustments', 'Y');
COMMIT;

INSERT INTO PROCESS (PROCESS_ID, DESCRIPTION, ACTIVE)
VALUES (144, 'Send document via email', 'Y');
COMMIT;

-------   FREEOFCHARGEADJUSTMENT   -----------
ALTER TABLE FREEOFCHARGEADJUSTMENT ADD (ADJUSTMENTTYPE NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT ADJUSTMENTTYPE_VALID1_1 CHECK (ADJUSTMENTTYPE IN (0,1,2,3,4)),
					EXPIRATIONDATE DATE NULL,
					RENEWONEXPIRATIONDATE NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT RENEWONEXPDATE_VALID1_1 CHECK (RENEWONEXPIRATIONDATE IN (0,1)),
					AUTOPROCESSED NUMBER DEFAULT 0 NOT NULL
					CONSTRAINT AUTOPROCESSED_VALID1_1 CHECK (AUTOPROCESSED IN (0,1)));

COMMENT ON COLUMN FREEOFCHARGEADJUSTMENT.ADJUSTMENTTYPE IS '0 = Standard
1 = Contract start
2 = Contract adjustment
3 = Contract end
4 = Contract renewal';

COMMENT ON COLUMN FREEOFCHARGEADJUSTMENT.EXPIRATIONDATE IS 'Will only be filled for AdjustmentType <> 0 ';

COMMENT ON COLUMN FREEOFCHARGEADJUSTMENT.RENEWONEXPIRATIONDATE IS '0 = Do not automatically renew
1 = Automatically renew';

COMMENT ON COLUMN FREEOFCHARGEADJUSTMENT.AUTOPROCESSED IS '0 = Not processed
1 = Processed';

CREATE INDEX XIE1FREEOFCHARGEADJUSTMENT ON FREEOFCHARGEADJUSTMENT (PRODUCTLISTPERDISTRIBPOINT_ID ASC, ADJUSTMENTTYPE ASC);
CREATE INDEX XIE2FREEOFCHARGEADJUSTMENT ON FREEOFCHARGEADJUSTMENT (PRODUCTLISTPERDISTRIBPOINT_ID ASC, EXPIRATIONDATE ASC, RENEWONEXPIRATIONDATE ASC, AUTOPROCESSED ASC);

-------   OVERUSEHISTORY   -----------
CREATE SEQUENCE SEQ_OVERUSEHISTORY
START WITH 1
INCREMENT BY 1;

CREATE TABLE OVERUSEHISTORY
(
OVERUSEHISTORY_ID NUMBER NOT NULL,
PRODUCTLISTPERDISTRIBPOINT_ID NUMBER NOT NULL,
CREATIONTIMESTAMP DATE NOT NULL,
DELIVERYDATE DATE NOT NULL,
YEAR NUMBER NOT NULL,
WEEK NUMBER NOT NULL,
CONTRACTQTY NUMBER DEFAULT 0 NOT NULL,
COMPARISONQTY NUMBER DEFAULT 0 NOT NULL,
DELIVEREDQTY NUMBER DEFAULT 0 NOT NULL,
OVERUSEQTY NUMBER DEFAULT 0 NOT NULL,
OVERUSETYPE NUMBER DEFAULT 0 NOT NULL CONSTRAINT OVERUSETYPE_VALID1_1 CHECK (OVERUSETYPE IN (0,1)),
PACKINGNOTELINE_ID NUMBER NULL,
INVOICEHEADER_ID NUMBER NULL,
SERVICEEMPLOYEE_ID NUMBER NULL,
EMAILDOCUMENT_ID NUMBER NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN OVERUSEHISTORY.CONTRACTQTY IS 'Total free-of-charge quantity at moment overuse is recorded';

COMMENT ON COLUMN OVERUSEHISTORY.COMPARISONQTY IS 'Free-of-charge quantity that is actually used to calculate the overuse until now';

COMMENT ON COLUMN OVERUSEHISTORY.DELIVEREDQTY IS 'Total number of not-free items that has been delivered until now';

COMMENT ON COLUMN OVERUSEHISTORY.OVERUSEQTY IS 'Total number of items that has been delivered more than allowed according to contract until now';

COMMENT ON COLUMN OVERUSEHISTORY.OVERUSETYPE IS '0 = Potential overuse
1 = Actual overuse ';

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT XPKOVERUSEHISTORY PRIMARY KEY (OVERUSEHISTORY_ID);

CREATE UNIQUE INDEX XAK1OVERUSEHISTORY ON OVERUSEHISTORY (PRODUCTLISTPERDISTRIBPOINT_ID ASC, CREATIONTIMESTAMP ASC);
CREATE INDEX XIE1OVERUSEHISTORY ON OVERUSEHISTORY (PRODUCTLISTPERDISTRIBPOINT_ID ASC, DELIVERYDATE ASC, OVERUSETYPE ASC, INVOICEHEADER_ID ASC);
CREATE INDEX XIE2OVERUSEHISTORY ON OVERUSEHISTORY (PRODUCTLISTPERDISTRIBPOINT_ID ASC, YEAR ASC, WEEK ASC, OVERUSETYPE ASC, INVOICEHEADER_ID ASC);

CREATE INDEX XIF1OVERUSEHISTORY ON OVERUSEHISTORY (PRODUCTLISTPERDISTRIBPOINT_ID ASC);
CREATE INDEX XIF2OVERUSEHISTORY ON OVERUSEHISTORY (PACKINGNOTELINE_ID ASC);
CREATE INDEX XIF3OVERUSEHISTORY ON OVERUSEHISTORY (INVOICEHEADER_ID ASC);
CREATE INDEX XIF4OVERUSEHISTORY ON OVERUSEHISTORY (SERVICEEMPLOYEE_ID ASC);
CREATE INDEX XIF5OVERUSEHISTORY ON OVERUSEHISTORY (EMAILDOCUMENT_ID ASC);

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT OVERUSEHISTORY_PLPD FOREIGN KEY (PRODUCTLISTPERDISTRIBPOINT_ID)
REFERENCES PRODUCTLISTPERDISTRIBPOINT (PRODUCTLISTPERDISTRIBPOINT_ID) ON DELETE CASCADE;

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT OVERUSEHISTORY_PNLID FOREIGN KEY (PACKINGNOTELINE_ID)
REFERENCES PACKINGNOTELINE (PACKINGNOTELINE_ID) ON DELETE SET NULL;

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT OVERUSEHISTORY_IHID FOREIGN KEY (INVOICEHEADER_ID)
REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE SET NULL;

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT OVERUSEHISTORY_SEMP FOREIGN KEY (SERVICEEMPLOYEE_ID)
REFERENCES EMPLOYEE (EMPLOYEE_ID) ON DELETE SET NULL;

ALTER TABLE OVERUSEHISTORY ADD CONSTRAINT OVERUSEHISTORY_EDID FOREIGN KEY (EMAILDOCUMENT_ID)
REFERENCES EMAILDOCUMENT (EMAILDOCUMENT_ID) ON DELETE SET NULL;
