--order 502528

CREATE SEQUENCE SEQ_CUSTOMERBILLINGLOG START WITH 1 INCREMENT BY 1;

CREATE TABLE CUSTOMERBILLINGLOG
(
	CUSTOMERBILLINGLOG_ID NUMBER NOT NULL,
	CUSTOMER_LINK_ID NUMBER NOT NULL,
	DEBTOR_LINK_ID NUMBER NOT NULL,
	BUSINESSUNIT_ID NUMBER NOT NULL,
	OLDRENTPERIOD	NUMBER NOT NULL,
	NEWRENTPERIOD	NUMBER NOT NULL,
	OLDINVOICEFREQUENCY_ID	NUMBER NOT NULL,
	NEWINVOICEFREQUENCY_ID	NUMBER NOT NULL,
	OLDCLUSTER_ID	NUMBER NULL,
	NEWCLUSTER_ID 	NUMBER NULL,
	CUTOFFDATE	DATE NULL,
	TIMESTAMP	DATE NOT NULL,
	SYSTEMUSER_ID	NUMBER NOT NULL
);

COMMENT ON COLUMN CUSTOMERBILLINGLOG.OLDRENTPERIOD IS '0 = In arrear
1 = In advance, correct additions only
2 = In advance, all corrections';

COMMENT ON COLUMN CUSTOMERBILLINGLOG.NEWRENTPERIOD IS '0 = In arrear
1 = In advance, correct additions only
2 = In advance, all corrections';

CREATE INDEX XIF1CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       CUSTOMER_LINK_ID                ASC
);

CREATE INDEX XIF2CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       DEBTOR_LINK_ID 		      ASC
);

CREATE INDEX XIF3CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       BUSINESSUNIT_ID                ASC
);

CREATE INDEX XIF4CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       OLDINVOICEFREQUENCY_ID	      ASC
);

CREATE INDEX XIF5CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       NEWINVOICEFREQUENCY_ID	      ASC
);

CREATE INDEX XIF6CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       OLDCLUSTER_ID	              ASC
);

CREATE INDEX XIF7CUSTOMERBILLINGLOG ON CUSTOMERBILLINGLOG
(
       NEWCLUSTER_ID                  ASC
);

ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT XPKCUSTOMERBILLINGLOG PRIMARY KEY (CUSTOMERBILLINGLOG_ID) ) ;

ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_NEWCLUSTER
              FOREIGN KEY (NEWCLUSTER_ID)
                             REFERENCES CUSTOMERCLUSTER
			     ON DELETE SET NULL ) ;


ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_OLDCLUSTER
              FOREIGN KEY (OLDCLUSTER_ID)
                             REFERENCES CUSTOMERCLUSTER
			     ON DELETE SET NULL ) ;


ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_NEWINVOICEF
              FOREIGN KEY (NEWINVOICEFREQUENCY_ID)
                             REFERENCES INVOICEFREQUENCY ) ;


ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_OLDINVOICEF
              FOREIGN KEY (OLDINVOICEFREQUENCY_ID)
                             REFERENCES INVOICEFREQUENCY ) ;


ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_BUSINESSUNI
              FOREIGN KEY (BUSINESSUNIT_ID)
                             REFERENCES BUSINESSUNIT ) ;



ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_DEBTOR_LINK
              FOREIGN KEY (DEBTOR_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;



ALTER TABLE CUSTOMERBILLINGLOG
       ADD  ( CONSTRAINT CUSTOMERBILLINGLOG_CUSTOMER_LI
              FOREIGN KEY (CUSTOMER_LINK_ID)
                             REFERENCES CUSTOMER_LINK ) ;

