--SO 20013403 ARtrans / Weekstat issue for customers ith double activities

CREATE SEQUENCE SEQ_INVOICELINEACTIVITY
START WITH 1
INCREMENT BY 1;

CREATE TABLE INVOICELINEACTIVITY
(
	INVOICELINEACTIVITY_ID NUMBER NOT NULL ,
	INVOICELINE_ID       NUMBER NOT NULL ,
	PROCESSINGBUSINESSUNIT_ID NUMBER NOT NULL ,
	YEAR                 NUMBER DEFAULT  0  NOT NULL ,
	WEEK                 NUMBER DEFAULT  0  NOT NULL  CONSTRAINT  WEEK_valid CHECK (WEEK BETWEEN 0 AND 53),
	BILLEDQTY            NUMBER DEFAULT  0  NOT NULL ,
	AMOUNT               NUMBER DEFAULT  0  NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

ALTER TABLE INVOICELINEACTIVITY
ADD CONSTRAINT  XPKINVOICELINEACTIVITY PRIMARY KEY (INVOICELINEACTIVITY_ID);

CREATE  INDEX XIF1INVOICELINEACTIVITY ON INVOICELINEACTIVITY
(INVOICELINE_ID   ASC);

ALTER TABLE INVOICELINEACTIVITY
ADD CONSTRAINT INVOICELINEACTIVITY_INVOICELIN FOREIGN KEY (INVOICELINE_ID) REFERENCES INVOICELINE (INVOICELINE_ID) ON DELETE CASCADE;

CREATE  INDEX XIF2INVOICELINEACTIVITY ON INVOICELINEACTIVITY
(PROCESSINGBUSINESSUNIT_ID   ASC);

ALTER TABLE INVOICELINEACTIVITY
ADD CONSTRAINT INVOICELINEACTIVITY_BU FOREIGN KEY (PROCESSINGBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE UNIQUE INDEX XAK1INVOICELINEACTIVITY ON INVOICELINEACTIVITY
(INVOICELINE_ID   ASC,PROCESSINGBUSINESSUNIT_ID   ASC,YEAR   ASC,WEEK   ASC);




