--ABS-119980

--PURCHASEORDERMESSAGE TABLE
CREATE TABLE PURCHASEORDERMESSAGE (
 PURCHASEORDERHEADER_ID NUMBER NOT NULL, 
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
 ALTER TABLE PURCHASEORDERMESSAGE ADD CONSTRAINT XPKPURCHASEORDERMESSAGE PRIMARY KEY (PURCHASEORDERHEADER_ID, MESSAGE_ID);

 
 CREATE INDEX XIF1PURCHASEORDERMESSAGE ON PURCHASEORDERMESSAGE (PURCHASEORDERHEADER_ID ASC);
 CREATE INDEX XIF2PURCHASEORDERMESSAGE ON PURCHASEORDERMESSAGE (MESSAGE_ID ASC);
 ALTER TABLE PURCHASEORDERMESSAGE ADD CONSTRAINT FK_PURCHMESSAGE_PURCHORDHEADER_ID FOREIGN KEY (PURCHASEORDERHEADER_ID) REFERENCES PURCHASEORDERHEADER (PURCHASEORDERHEADER_ID) ON DELETE CASCADE;
 ALTER TABLE PURCHASEORDERMESSAGE ADD CONSTRAINT FK_PURCHMESSAGE_MES_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;

--EMAILSETTINGAPP TABLE

CREATE TABLE EMAILSETTINGAPP (
       DOCUMENTTYPE NUMBER NOT NULL CONSTRAINT CHK_EMAILSETTINGAPP_DOCUMENTTYPE CHECK (DOCUMENTTYPE BETWEEN 1 AND 39),
       SENDINGAPPLICATION NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_EMAILSETTINGAPP_SENDINGAPPLICATION CHECK (SENDINGAPPLICATION IN (0,1)),
       SYSTEMUSER_ID NUMBER NOT NULL,
       TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN EMAILSETTINGAPP.DOCUMENTTYPE IS '1 to 39';
COMMENT ON COLUMN EMAILSETTINGAPP.SENDINGAPPLICATION IS '0 = default = Legacy ABSSolute 1 = ABSSolute';

ALTER TABLE EMAILSETTINGAPP
ADD CONSTRAINT XPKMAILSETTINGAPP PRIMARY KEY (DOCUMENTTYPE, SENDINGAPPLICATION);

--INSERT INTO EMAILSETTINGAPP TABLE
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (1, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (2, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (3, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (4, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (5, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (6, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (7, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (8, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (9, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (10, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (11, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (12, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (13, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (14, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (15, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (16, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (17, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (18, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (19, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (20, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (21, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (22, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (23, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (24, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (25, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (26, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (27, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (28, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (29, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (30, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (31, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (32, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (33, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (34, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (35, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (36, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (37, 0, 1, SYSDATE);
INSERT INTO EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION, SYSTEMUSER_ID, TIMESTAMP)
VALUES (38, 0, 1, SYSDATE);
commit;
       
--EMAILSETTING TABLE
ALTER TABLE EMAILSETTING ADD (SENDINGAPPLICATION NUMBER DEFAULT 0 NOT NULL CONSTRAINT CHK_EMAILSETTING_SENDINGAPPLICATION CHECK (SENDINGAPPLICATION IN (0, 1)));

COMMENT ON COLUMN EMAILSETTING.SENDINGAPPLICATION IS '0 = default = Legacy ABSSolute 1 = ABSSolute';
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 37 = Contact notification (web) 38 = Contact notification (internal) 39 = Credit Card Payemnt Receipt';

ALTER TABLE EMAILSETTING
ADD CONSTRAINT EMAILSETTING_SENDINGAPPLICATION FOREIGN KEY (DOCUMENTTYPE, SENDINGAPPLICATION) REFERENCES EMAILSETTINGAPP (DOCUMENTTYPE, SENDINGAPPLICATION);

--PACKINGNOTEMESSAGE TABLE

CREATE TABLE PACKINGNOTEMESSAGE (
 PACKINGNOTE_ID NUMBER NOT NULL,
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
 ALTER TABLE PACKINGNOTEMESSAGE ADD CONSTRAINT XPKPACKINGNOTEMESSAGE PRIMARY KEY (PACKINGNOTE_ID, MESSAGE_ID);
 
 
 CREATE INDEX XIF1PACKINGNOTEMESSAGE ON PACKINGNOTEMESSAGE (PACKINGNOTE_ID ASC);
 CREATE INDEX XIF2PACKINGNOTEMESSAGE ON PACKINGNOTEMESSAGE (MESSAGE_ID ASC);
 ALTER TABLE PACKINGNOTEMESSAGE ADD CONSTRAINT FK_PNMESSAGE_PACKINGNOTE_ID FOREIGN KEY (PACKINGNOTE_ID) REFERENCES PACKINGNOTE (PACKINGNOTE_ID) ON DELETE CASCADE;
 ALTER TABLE PACKINGNOTEMESSAGE ADD CONSTRAINT FK_PNMESSAGE_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;
 
 --CONSIGNMENTNOTEMESSAGE TABLE
 
 CREATE TABLE CONSIGNMENTNOTEMESSAGE (
  CONSIGNMENTNOTE_ID NUMBER NOT NULL,
  MESSAGE_ID NUMBER NOT NULL,
  SYSTEMUSER_ID NUMBER NOT NULL,
  TIMESTAMP DATE NOT NULL);

ALTER TABLE CONSIGNMENTNOTEMESSAGE ADD CONSTRAINT XPKCONSIGNMENTNOTEMESSAGE PRIMARY KEY (CONSIGNMENTNOTE_ID, MESSAGE_ID);

CREATE INDEX XIF1CONSIGNMENTNOTEMESSAGE ON CONSIGNMENTNOTEMESSAGE (CONSIGNMENTNOTE_ID ASC);
CREATE INDEX XIF2CONSIGNMENTNOTEMESSAGE ON CONSIGNMENTNOTEMESSAGE (MESSAGE_ID ASC);
ALTER TABLE CONSIGNMENTNOTEMESSAGE ADD CONSTRAINT FK_CONSIGNMSG_CONSIGNMENTNOTE_ID FOREIGN KEY (CONSIGNMENTNOTE_ID) REFERENCES CONSIGNMENTNOTE (CONSIGNMENTNOTE_ID) ON DELETE CASCADE;
ALTER TABLE CONSIGNMENTNOTEMESSAGE ADD CONSTRAINT FK_CONSIGNMSG_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;

--INVOICEHEADERMESSAGE TABLE 

CREATE TABLE INVOICEHEADERMESSAGE (
 INVOICEHEADER_ID NUMBER NOT NULL,
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
ALTER TABLE INVOICEHEADERMESSAGE ADD CONSTRAINT XPKINVOICEHEADERMESSAGE PRIMARY KEY (INVOICEHEADER_ID, MESSAGE_ID);


CREATE INDEX XIF1INVOICEHEADERMESSAGE ON INVOICEHEADERMESSAGE (INVOICEHEADER_ID ASC);
CREATE INDEX XIF2INVOICEHEADERMESSAGE ON INVOICEHEADERMESSAGE (MESSAGE_ID ASC);
ALTER TABLE INVOICEHEADERMESSAGE ADD CONSTRAINT FK_INVOICEHDRMSG_INVOICEHEADER_ID FOREIGN KEY (INVOICEHEADER_ID) REFERENCES INVOICEHEADER (INVOICEHEADER_ID) ON DELETE CASCADE;
ALTER TABLE INVOICEHEADERMESSAGE ADD CONSTRAINT FK_INVOICEHDRMSG_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;

--WORKORDERTABLE MESSAGE

CREATE TABLE WORKORDERMESSAGE (
 WORKORDER_ID NUMBER NOT NULL,
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
ALTER TABLE WORKORDERMESSAGE ADD CONSTRAINT XPKWORKORDERMESSAGE PRIMARY KEY (WORKORDER_ID, MESSAGE_ID);


CREATE INDEX XIF1WORKORDERMESSAGE ON WORKORDERMESSAGE (WORKORDER_ID ASC);
CREATE INDEX XIF2WORKORDERMESSAGE ON WORKORDERMESSAGE (MESSAGE_ID ASC);
ALTER TABLE WORKORDERMESSAGE ADD CONSTRAINT FK_WORKORDERMSG_WORKORDER_ID FOREIGN KEY (WORKORDER_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE CASCADE;
ALTER TABLE WORKORDERMESSAGE ADD CONSTRAINT FK_WORKORDERMSG_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;

--INTERNETORDERMESSAGE

CREATE TABLE INTERNETORDERMESSAGE (
 INTERNETORDER_ID NUMBER NOT NULL,
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
ALTER TABLE INTERNETORDERMESSAGE ADD CONSTRAINT XPKINTERNETORDERMESSAGE PRIMARY KEY (INTERNETORDER_ID, MESSAGE_ID);


CREATE INDEX XIF1INTERNETORDERMESSAGE ON INTERNETORDERMESSAGE (INTERNETORDER_ID ASC);
CREATE INDEX XIF2INTERNETORDERMESSAGE ON INTERNETORDERMESSAGE (MESSAGE_ID ASC);
ALTER TABLE INTERNETORDERMESSAGE ADD CONSTRAINT FK_INTERNETORDERMSG_INTERNETORDER_ID FOREIGN KEY (INTERNETORDER_ID) REFERENCES INTERNETORDER (INTERNETORDER_ID) ON DELETE CASCADE;
ALTER TABLE INTERNETORDERMESSAGE ADD CONSTRAINT FK_INTERNETORDERMSG_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;

--WEBORDERMESSAGE TABLE

CREATE TABLE WEBORDERMESSAGE (
 WEBORDER_ID NUMBER NOT NULL,
 MESSAGE_ID NUMBER NOT NULL,
 SYSTEMUSER_ID NUMBER NOT NULL,
 TIMESTAMP DATE NOT NULL);
 
ALTER TABLE WEBORDERMESSAGE ADD CONSTRAINT XPKWEBORDERMESSAGE PRIMARY KEY (WEBORDER_ID, MESSAGE_ID);
 
CREATE INDEX XIF1WEBORDERMESSAGE ON WEBORDERMESSAGE (WEBORDER_ID ASC);
CREATE INDEX XIF2WEBORDERMESSAGE ON WEBORDERMESSAGE (MESSAGE_ID ASC);
ALTER TABLE WEBORDERMESSAGE ADD CONSTRAINT FK_WEBORDERMSG_WEBORDER_ID FOREIGN KEY (WEBORDER_ID) REFERENCES WEBORDER (WEBORDER_ID) ON DELETE CASCADE;
ALTER TABLE WEBORDERMESSAGE ADD CONSTRAINT FK_WEBORDERMSG_MESSAGE_ID FOREIGN KEY (MESSAGE_ID) REFERENCES MESSAGE (MESSAGE_ID) ON DELETE CASCADE;
