--- ABS-19906
----------------------   COSTCENTERGROUP   ----------------------------
CREATE SEQUENCE SEQ_COSTCENTERGROUP
START WITH 1
INCREMENT BY 1;

CREATE TABLE COSTCENTERGROUP
(
COSTCENTERGROUP_ID NUMBER NOT NULL,
CUSTOMER_ID NUMBER NOT NULL,
CODE VARCHAR2(12) NOT NULL,
DESCRIPTION VARCHAR2(30) NOT NULL,
INVOICEADDRESS_ID NUMBER NULL,
CONTACTPERSON_ID NUMBER NULL,
SYSTEMUSER_ID NUMBER NOT NULL,
TIMESTAMP DATE NOT NULL
);

ALTER TABLE COSTCENTERGROUP ADD CONSTRAINT XPKCOSTCENTERGROUP PRIMARY KEY (COSTCENTERGROUP_ID);

CREATE UNIQUE INDEX XAK1COSTCENTERGROUP ON COSTCENTERGROUP (CUSTOMER_ID ASC, CODE ASC);

CREATE INDEX XIF1COSTCENTERGROUP ON COSTCENTERGROUP (CUSTOMER_ID ASC);

CREATE INDEX XIF2COSTCENTERGROUP ON COSTCENTERGROUP (INVOICEADDRESS_ID ASC);

CREATE INDEX XIF3COSTCENTERGROUP ON COSTCENTERGROUP (CONTACTPERSON_ID ASC);

ALTER TABLE COSTCENTERGROUP ADD CONSTRAINT COSTCENTERGROUP_CUSTID FOREIGN KEY (CUSTOMER_ID)
REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE COSTCENTERGROUP ADD CONSTRAINT COSTCENTERGROUP_INVAD FOREIGN KEY (INVOICEADDRESS_ID)
REFERENCES ADDRESS (ADDRESS_ID);

ALTER TABLE COSTCENTERGROUP ADD CONSTRAINT COSTCENTERGROUP_CPID FOREIGN KEY (CONTACTPERSON_ID)
REFERENCES CONTACTPERSON (CONTACTPERSON_ID);

--------------------------------   CUSTOMER   -------------------------------------
ALTER TRIGGER AFTER_UPDATE_CUSTOMER DISABLE;

update customer c
set c.splitinvoices = 0
where c.splitinvoices not in (0,1,2,3);
COMMIT;

ALTER TRIGGER AFTER_UPDATE_CUSTOMER ENABLE;

COMMENT ON COLUMN CUSTOMER.SPLITINVOICES IS '0=No Split up
1=Per product grouping code
2=Per cost center
3=Rent / other services
4=Per Cost Center Group';

ALTER TABLE CUSTOMER DROP CONSTRAINT SPLIINVOICES_VALID1_1;
ALTER TABLE CUSTOMER ADD CONSTRAINT SPLIINVOICES_VALID1_1 CHECK (SPLITINVOICES BETWEEN 0 AND 4);


--------------------------------   COSTCENTER   -------------------------------------
ALTER TABLE COSTCENTER ADD (COSTCENTERGROUP_ID NUMBER NULL);

CREATE INDEX XIF1COSTCENTER ON COSTCENTER (COSTCENTERGROUP_ID ASC);

ALTER TABLE COSTCENTER ADD CONSTRAINT COSTCENTER_CCGROUPID FOREIGN KEY (COSTCENTERGROUP_ID)
REFERENCES COSTCENTERGROUP (COSTCENTERGROUP_ID);


