-- ABS-101698 / ABS-101790

----- CUSTOMEREXTRAINFO ------
ALTER TABLE CUSTOMEREXTRAINFO ADD (RAONSITEDELIVERYSCAN VARCHAR2(1) DEFAULT 'N' NOT NULL CONSTRAINT CHK_CUSTEXTRAINFO_RADELSCAN CHECK (RAONSITEDELIVERYSCAN IN ('Y', 'N')));
COMMENT ON COLUMN CUSTOMEREXTRAINFO.RAONSITEDELIVERYSCAN IS 'Only used for the module ROUTEASSISTANTDELIVERYSCAN';

----- DELIVERYSCANEXTRAINFO -----
CREATE TABLE DELIVERYSCANEXTRAINFO (BUSINESSUNIT_ID NUMBER NOT NULL,
                                    UNIQUEITEM_LINK_ID NUMBER NOT NULL,
                                    SCANTIMESTAMP DATE NOT NULL,
                                    ONSITESCANTIMESTAMP DATE NOT NULL,
                                    MOBILEDEVICE_ID NUMBER NOT NULL,
                                    SYSTEMUSER_ID NUMBER NOT NULL,
                                    TIMESTAMP DATE NOT NULL);
COMMENT ON COLUMN DELIVERYSCANEXTRAINFO.UNIQUEITEM_LINK_ID IS 'UniqueItem_Link_ID = UniqueItem_ID or UniqueItem_Link_ID = RaggedItem_ID';                                   
COMMENT ON COLUMN DELIVERYSCANEXTRAINFO.SCANTIMESTAMP IS 'Date item was scanned out at laundry';
COMMENT ON COLUMN DELIVERYSCANEXTRAINFO.ONSITESCANTIMESTAMP IS 'Will only be filled when scanned onsite at customer via PDA';
COMMENT ON COLUMN DELIVERYSCANEXTRAINFO.MOBILEDEVICE_ID IS 'Will only be filled when scanned onsite at customer via PDA';

CREATE INDEX XIF1DELIVERYSCANEXTRAINFO ON DELIVERYSCANEXTRAINFO(MOBILEDEVICE_ID ASC);
                                     
ALTER TABLE DELIVERYSCANEXTRAINFO ADD CONSTRAINT XPKDELIVERYSCANEXTRAINFO PRIMARY KEY (BUSINESSUNIT_ID, UNIQUEITEM_LINK_ID, SCANTIMESTAMP);
ALTER TABLE DELIVERYSCANEXTRAINFO ADD CONSTRAINT FK_DELSCANEXTRAINFO_BUUISCANTS FOREIGN KEY (BUSINESSUNIT_ID,UNIQUEITEM_LINK_ID,SCANTIMESTAMP)
REFERENCES DELIVERYSCAN (BUSINESSUNIT_ID,UNIQUEITEM_LINK_ID,SCANTIMESTAMP);
ALTER TABLE DELIVERYSCANEXTRAINFO ADD CONSTRAINT FK_DELSCANEXTRAINFO_MOBDEV FOREIGN KEY (MOBILEDEVICE_ID)
REFERENCES MOBILEDEVICE (MOBILEDEVICE_ID);

  
----- EMAILSETTING -----
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 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 = (Successfully) Processed OR Packs
22 = Unattended UHF Reader Status Changed
23 = Future planned deliveries
24 = Client Invoice
25 = Overuse notification
26 = Stock reservation quantity check
27 = MyUniformAssistant new PIN
28 = MyUniformAssistant Password change
29 = MyUniformAssistant test email
30 = MyUniformAssistant Password reset warning
31 = MyUniformAssistant Welcome email
32 = MyUniformAssistant username change
33 = Import payment notification
34 = Customer purchase order exception
35 = Diagnostics
36 = Credit card payment confirmation';