-- ABS-21785/24791

---SYSTEMUSER table---

ALTER TABLE SYSTEMUSER ADD (RATIFICATIONAUTHORIZATION NUMBER DEFAULT 0 NOT NULL
                            CONSTRAINT RATIFICATIONAUTHOR_VALID1_1 CHECK (RATIFICATIONAUTHORIZATION BETWEEN 0 AND 3)
                            );

COMMENT ON COLUMN SYSTEMUSER.RATIFICATIONAUTHORIZATION
  IS '0 = Not ratification used.
1 = User is only allowed to add new prices and to modifiy and delete own not ratified prices
2 = User is only allowed to ratify prices.
3 = Combination of 1 and 2, but prices are always ratified in a separate step.
';

---GENERALWASHPRICE table---

ALTER TABLE GENERALWASHPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                  CREATIONUSER_ID NUMBER NULL,
                                  CREATIONTIMESTAMP DATE NULL,
                                  RATIFICATIONUSER_ID NUMBER NULL,
                                  RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALWASHPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
			    CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALWASHPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                     CREATIONTIMESTAMP NOT NULL);

---GENERALSALESPRICE table---

ALTER TABLE GENERALSALESPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                   CREATIONUSER_ID NUMBER NULL,
                                   CREATIONTIMESTAMP DATE NULL,
                                   RATIFICATIONUSER_ID NUMBER NULL,
                                   RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALSALESPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                             CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALSALESPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                      CREATIONTIMESTAMP NOT NULL);

---GENERALRENTPRICE table---

ALTER TABLE GENERALRENTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                  CREATIONUSER_ID NUMBER NULL,
                                  CREATIONTIMESTAMP DATE NULL,
                                  RATIFICATIONUSER_ID NUMBER NULL,
                                  RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALRENTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
			    CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALRENTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                     CREATIONTIMESTAMP NOT NULL);

---GENERALREPLACEMENTPRICE table---

ALTER TABLE GENERALREPLACEMENTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                         CREATIONUSER_ID NUMBER NULL,
                                         CREATIONTIMESTAMP DATE NULL,
                                         RATIFICATIONUSER_ID NUMBER NULL,
                                         RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALREPLACEMENTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
				   CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALREPLACEMENTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                            CREATIONTIMESTAMP NOT NULL);

---GENERALINSURANCEPRICE table---

ALTER TABLE GENERALINSURANCEPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                       CREATIONUSER_ID NUMBER NULL,
                                       CREATIONTIMESTAMP DATE NULL,
                                       RATIFICATIONUSER_ID NUMBER NULL,
                                       RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALINSURANCEPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
				 CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALINSURANCEPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                          CREATIONTIMESTAMP NOT NULL);

---GENERALSPECIALCHARGEPRICE table---

ALTER TABLE GENERALSPECIALCHARGEPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                           CREATIONUSER_ID NUMBER NULL,
                                           CREATIONTIMESTAMP DATE NULL,
                                           RATIFICATIONUSER_ID NUMBER NULL,
                                           RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALSPECIALCHARGEPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
				     CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALSPECIALCHARGEPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                              CREATIONTIMESTAMP NOT NULL);

---GENERALEMBLEMPRICE table---

ALTER TABLE GENERALEMBLEMPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                    CREATIONUSER_ID NUMBER NULL,
                                    CREATIONTIMESTAMP DATE NULL,
                                    RATIFICATIONUSER_ID NUMBER NULL,
                                    RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALEMBLEMPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
			      CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALEMBLEMPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                       CREATIONTIMESTAMP NOT NULL);

---GENERALREPAIRPRICE table---

ALTER TABLE GENERALREPAIRPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                    CREATIONUSER_ID NUMBER NULL,
                                    CREATIONTIMESTAMP DATE NULL,
                                    RATIFICATIONUSER_ID NUMBER NULL,
                                    RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALREPAIRPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
			      CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALREPAIRPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                       CREATIONTIMESTAMP NOT NULL);

---GENERALWEIGHTPRICE table---

ALTER TABLE GENERALWEIGHTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                    CREATIONUSER_ID NUMBER NULL,
                                    CREATIONTIMESTAMP DATE NULL,
                                    RATIFICATIONUSER_ID NUMBER NULL,
                                    RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALWEIGHTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
			      CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALWEIGHTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                       CREATIONTIMESTAMP NOT NULL);

---CUSTOMERWASHPRICE table---

ALTER TABLE CUSTOMERWASHPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                   CREATIONUSER_ID NUMBER NULL,
                                   CREATIONTIMESTAMP DATE NULL,
                                   RATIFICATIONUSER_ID NUMBER NULL,
                                   RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERWASHPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
            		     CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERWASHPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                      CREATIONTIMESTAMP NOT NULL);

---CUSTOMERRENTPRICE table---

ALTER TABLE CUSTOMERRENTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                   CREATIONUSER_ID NUMBER NULL,
                                   CREATIONTIMESTAMP DATE NULL,
                                   RATIFICATIONUSER_ID NUMBER NULL,
                                   RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERRENTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                             CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERRENTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                      CREATIONTIMESTAMP NOT NULL);

---CUSTOMERREPAIRPRICE table---

ALTER TABLE CUSTOMERREPAIRPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                     CREATIONUSER_ID NUMBER NULL,
                                     CREATIONTIMESTAMP DATE NULL,
                                     RATIFICATIONUSER_ID NUMBER NULL,
                                     RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERREPAIRPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                               CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERREPAIRPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                        CREATIONTIMESTAMP NOT NULL);

---CUSTOMERSALESPRICE table---

ALTER TABLE CUSTOMERSALESPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                    CREATIONUSER_ID NUMBER NULL,
                                    CREATIONTIMESTAMP DATE NULL,
                                    RATIFICATIONUSER_ID NUMBER NULL,
                                    RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERSALESPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                              CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERSALESPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                       CREATIONTIMESTAMP NOT NULL);

---CUSTOMERREPLACEMENTPRICE table---

ALTER TABLE CUSTOMERREPLACEMENTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                          CREATIONUSER_ID NUMBER NULL,
                                          CREATIONTIMESTAMP DATE NULL,
                                          RATIFICATIONUSER_ID NUMBER NULL,
                                          RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERREPLACEMENTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                    CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERREPLACEMENTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                             CREATIONTIMESTAMP NOT NULL);

---CUSTOMERSPECIALCHARGEPRICE table---

ALTER TABLE CUSTOMERSPECIALCHARGEPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                            CREATIONUSER_ID NUMBER NULL,
                                            CREATIONTIMESTAMP DATE NULL,
                                            RATIFICATIONUSER_ID NUMBER NULL,
                                            RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERSPECIALCHARGEPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                      CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERSPECIALCHARGEPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                               CREATIONTIMESTAMP NOT NULL);

---CUSTOMERWEIGHTPRICE table---

ALTER TABLE CUSTOMERWEIGHTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                     CREATIONUSER_ID NUMBER NULL,
                                     CREATIONTIMESTAMP DATE NULL,
                                     RATIFICATIONUSER_ID NUMBER NULL,
                                     RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERWEIGHTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                               CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERWEIGHTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                        CREATIONTIMESTAMP NOT NULL);

---CUSTOMERRESIDENTIALRENTPRICE table---

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE ADD (RATIFICATIONSTARTDATE DATE NULL,
                                              CREATIONUSER_ID NUMBER NULL,
                                              CREATIONTIMESTAMP DATE NULL,
                                              RATIFICATIONUSER_ID NUMBER NULL,
                                              RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERRESIDENTIALRENTPRICE SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                        CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERRESIDENTIALRENTPRICE MODIFY (CREATIONUSER_ID NOT NULL,
                                                 CREATIONTIMESTAMP NOT NULL);

---GENERALRENTDISCOUNT table---

ALTER TABLE GENERALRENTDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                     CREATIONUSER_ID NUMBER NULL,
                                     CREATIONTIMESTAMP DATE NULL,
                                     RATIFICATIONUSER_ID NUMBER NULL,
                                     RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALRENTDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                               CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALRENTDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                        CREATIONTIMESTAMP NOT NULL);

---GENERALWASHDISCOUNT table---

ALTER TABLE GENERALWASHDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                     CREATIONUSER_ID NUMBER NULL,
                                     CREATIONTIMESTAMP DATE NULL,
                                     RATIFICATIONUSER_ID NUMBER NULL,
                                     RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALWASHDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                               CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALWASHDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                        CREATIONTIMESTAMP NOT NULL);

---GENERALWEIGHTDISCOUNT table---

ALTER TABLE GENERALWEIGHTDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                       CREATIONUSER_ID NUMBER NULL,
                                       CREATIONTIMESTAMP DATE NULL,
                                       RATIFICATIONUSER_ID NUMBER NULL,
                                       RATIFICATIONTIMESTAMP DATE NULL);

UPDATE GENERALWEIGHTDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                 CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE GENERALWEIGHTDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                          CREATIONTIMESTAMP NOT NULL);

---CUSTOMERRENTDISCOUNT table---

ALTER TABLE CUSTOMERRENTDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                      CREATIONUSER_ID NUMBER NULL,
                                      CREATIONTIMESTAMP DATE NULL,
                                      RATIFICATIONUSER_ID NUMBER NULL,
                                      RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERRENTDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERRENTDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                         CREATIONTIMESTAMP NOT NULL);

---CUSTOMERWASHDISCOUNT table---

ALTER TABLE CUSTOMERWASHDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                      CREATIONUSER_ID NUMBER NULL,
                                      CREATIONTIMESTAMP DATE NULL,
                                      RATIFICATIONUSER_ID NUMBER NULL,
                                      RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERWASHDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERWASHDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                         CREATIONTIMESTAMP NOT NULL);

---CUSTOMERWEIGHTDISCOUNT table---

ALTER TABLE CUSTOMERWEIGHTDISCOUNT ADD (RATIFICATIONSTARTDATE DATE NULL,
                                        CREATIONUSER_ID NUMBER NULL,
                                        CREATIONTIMESTAMP DATE NULL,
                                        RATIFICATIONUSER_ID NUMBER NULL,
                                        RATIFICATIONTIMESTAMP DATE NULL);

UPDATE CUSTOMERWEIGHTDISCOUNT SET CREATIONUSER_ID = SYSTEMUSER_ID,
                                  CREATIONTIMESTAMP = TIMESTAMP;
COMMIT;

ALTER TABLE CUSTOMERWEIGHTDISCOUNT MODIFY (CREATIONUSER_ID NOT NULL,
                                           CREATIONTIMESTAMP NOT NULL);


