--Order 20013066.80
ALTER TABLE CLIENTRESIDENT
RENAME COLUMN ADMINNUMBER_OLD TO ADMINNUMBER;


--Order 20014210.80


---CUSTOMERINTERNETOPTIONS table---

ALTER TABLE CUSTOMERINTERNETOPTIONS
  ADD (ALLOWWEBWEARERS VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField229 CHECK (ALLOWWEBWEARERS IN 
('Y', 'N')));

ALTER TABLE CUSTOMERINTERNETOPTIONS
  ADD (WEBWEARERSEMAILADDRESS VARCHAR2(300) NULL);

ALTER TABLE CUSTOMERINTERNETOPTIONS
  ADD (WEBWEARERSPASSWORD VARCHAR2(80) NULL);

COMMENT ON COLUMN CUSTOMERINTERNETOPTIONS.WEBWEARERSEMAILADDRESS IS 'Can hold multiple e-mail addresses separated by 
semicolons.
Mandatory when AllowWebWearers = ‘Y’';

COMMENT ON COLUMN CUSTOMERINTERNETOPTIONS.WEBWEARERSPASSWORD IS 'Default password for web wearers 
Mandatory when AllowWebWearers = ‘Y’
';

ALTER TABLE CUSTOMERINTERNETOPTIONS ADD CONSTRAINT WEBWEARERSEMAILADDRESSFILLED
CHECK (((ALLOWWEBWEARERS = 'Y') AND (WEBWEARERSEMAILADDRESS IS NOT NULL)) OR
(ALLOWWEBWEARERS = 'N'));

ALTER TABLE CUSTOMERINTERNETOPTIONS ADD CONSTRAINT WEBWEARERSPASSWORDFILLED
CHECK (((ALLOWWEBWEARERS = 'Y') AND (WEBWEARERSPASSWORD IS NOT NULL)) OR
(ALLOWWEBWEARERS = 'N'));


---WORKORDER table---

ALTER TABLE WORKORDER
  ADD (APPROVEDBYCUSTOMER NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ApprovedByCustomer_valid1_1 CHECK (APPROVEDBYCUSTOMER 
BETWEEN 0 AND 2));

COMMENT ON COLUMN WORKORDER.APPROVEDBYCUSTOMER IS 'Indicates if this work order has been approved by the customer via the 
web portal. Possible values:
0 = Not applicable
1 = Not approved
2 = Approved
';


---WEBUSER table---

ALTER TABLE WEBUSER
  ADD (USEFORWEARERWEB NUMBER  DEFAULT 0 NOT NULL CONSTRAINT USEFORWEARERWEB_valid1_1 CHECK (USEFORWEARERWEB BETWEEN 0 
AND 1));

COMMENT ON COLUMN WEBUSER.USEFORWEARERWEB IS 'This attribute controls whether this web user will be linked to a wearer to 
log any transactions that have been made for this wearer via a wearer web portal. Possible values:
0 – Not applicable
1 – Use for wearer web portal
';


---WEARERFUNCTION table---

ALTER TABLE WEARERFUNCTION
  ADD (ALLOWORDERINGVIAWEB NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ALLOWORDERINGVIAWEB_valid1 CHECK (ALLOWORDERINGVIAWEB 
BETWEEN 0 AND 2));

ALTER TABLE WEARERFUNCTION
  ADD (ALLOWORDERINGVIAWEBMODE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ALLOWORDERINGVIAWEBMODE_valid1 CHECK 
(ALLOWORDERINGVIAWEBMODE BETWEEN 0 AND 1));

COMMENT ON COLUMN WEARERFUNCTION.ALLOWORDERINGVIAWEB IS 'Indicates if a wearer having his function may order via web portal. 
Possible values:
0 = Not allowed
1 = Allowed
2 = Allowed but approval needed on web
';

COMMENT ON COLUMN WEARERFUNCTION.ALLOWORDERINGVIAWEBMODE IS 'Indicates if value as defined for alloworderingviaweb at  
function level is fixed or may be overruled at wearer level for a wearer having this function. Possible values:
0 = Default
1 = Fixed
';


---WEARER table---
 
ALTER TABLE WEARER ADD (WEBWEARER VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT YN_VALIDATIONFIELD230
  CHECK (WEBWEARER IN ('Y','N')));
ALTER TABLE WEARER ADD (EMAILADDRESS VARCHAR2(300) NULL);
ALTER TABLE WEARER ADD (PASSWORD VARCHAR2(80) NULL);
ALTER TABLE WEARER ADD (CONVERTEDPASSWORD VARCHAR2(40) NULL);
ALTER TABLE WEARER ADD (LANGUAGE_ID NUMBER NULL);
ALTER TABLE WEARER ADD (ALLOWORDERINGVIAWEB NUMBER DEFAULT 0 NOT NULL CONSTRAINT ALLOWORDERINGVIAWEB_VALID1_1
  CHECK (ALLOWORDERINGVIAWEB BETWEEN 0 AND 2));
ALTER TABLE WEARER ADD (EMAILSTATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT EMAILSTATUS_VALID2_1
  CHECK (EMAILSTATUS BETWEEN 0 AND 2));
 
COMMENT ON COLUMN WEARER.WEBWEARER
  IS 'Indicates if wearer may have access to wearer web portal. ';
COMMENT ON COLUMN WEARER.EMAILADDRESS
  IS 'May hold only one single e-mail (not multiple addresses separated by semicolons)
Mandatory when WebWearer = ‘Y’
';
COMMENT ON COLUMN WEARER.PASSWORD
  IS 'Optional, so may be left empty. 
Mandatory when WebWearer = ‘Y’
';
COMMENT ON COLUMN WEARER.CONVERTEDPASSWORD
  IS 'Use for conversion of the password. The password itself is stored encrypted and can’t therefore be converted. When filled this field will be encrypted and stored as password in the application.
Optional, so may be left empty. 
';
COMMENT ON COLUMN WEARER.LANGUAGE_ID
  IS 'Foreign key to Language table. Optional, so may be left empy.
Mandatory when WebWearer = ‘Y’
';
COMMENT ON COLUMN WEARER.ALLOWORDERINGVIAWEB
  IS 'Indicates if  wearer  may order via web portal. Possible values:
0 = Not allowed
1 = Allowed
2 = Allowed but approval needed on web
';
COMMENT ON COLUMN WEARER.EMAILSTATUS
  IS 'Indicates if  email has been sent to wearer. Possible values:
0 = Not applicable
1 = Ready to be send
2 = Exported/sent
';

CREATE INDEX XAK2WEARER ON WEARER (EMAILADDRESS ASC);

CREATE INDEX XIF692WEARER ON WEARER (LANGUAGE_ID ASC);

ALTER TABLE WEARER
  ADD CONSTRAINT WEARER_LANGUAGE FOREIGN KEY (LANGUAGE_ID)
  REFERENCES LANGUAGES (LANGUAGE_ID);
  
ALTER TABLE WEARER
  ADD CONSTRAINT EMAILFILLED_VALIDATION
  CHECK (((WEBWEARER = 'Y' AND EMAILADDRESS IS NOT NULL) OR (WEBWEARER = 'N' AND EMAILADDRESS IS NULL)));
ALTER TABLE WEARER
  ADD CONSTRAINT PASSWORDFILLED_VALIDATION
  CHECK (((WEBWEARER = 'Y' AND PASSWORD IS NOT NULL) OR (WEBWEARER = 'N' AND PASSWORD IS NULL)));
ALTER TABLE WEARER
  ADD CONSTRAINT LANGUAGEFILLED_VALIDATION
  CHECK (((WEBWEARER = 'Y' AND LANGUAGE_ID IS NOT NULL) OR (WEBWEARER = 'N' AND LANGUAGE_ID IS NULL)));


---SUPPLIERDOCUMENT table---

CREATE SEQUENCE SEQ_SUPPLIERDOCUMENT
START WITH 1
INCREMENT BY 1;

CREATE TABLE SUPPLIERDOCUMENT
(
       SUPPLIERDOCUMENT_ID NUMBER NOT NULL,
       SUPPLIER_ID         NUMBER NOT NULL,
       DOCUMENTTYPE        NUMBER DEFAULT 1 NOT NULL,
       STARTDATE           DATE NOT NULL,
       DOCUMENTNAME        VARCHAR2(260) NOT NULL,
       DOCUMENT            BLOB NOT NULL,
       SYSTEMUSER_ID       NUMBER NOT NULL,
       TIMESTAMP           DATE NOT NULL
);

COMMENT ON COLUMN SUPPLIERDOCUMENT.DOCUMENTTYPE IS
'1 - Supplier Size Chart';

ALTER TABLE SUPPLIERDOCUMENT
ADD CONSTRAINT  XPKSUPPLIERDOCUMENT PRIMARY KEY (SUPPLIERDOCUMENT_ID);

CREATE UNIQUE INDEX XAK1SUPPLIERDOCUMENT ON SUPPLIERDOCUMENT
(SUPPLIER_ID   ASC,DOCUMENTTYPE   ASC,STARTDATE   ASC);

CREATE INDEX XIF1SUPPLIERDOCUMENT ON SUPPLIERDOCUMENT
(SUPPLIER_ID   ASC);

ALTER TABLE SUPPLIERDOCUMENT
ADD CONSTRAINT SUPPLIERDOCUMENT_SUPPLIER FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID) ON DELETE CASCADE;


---EMAILSETTING table---

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
16 = Wearer Web Login Credentials';