--Order 20010891.130

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE;

ALTER TABLE CUSTOMER
  ADD (DEFAULTINVOICEBUSINESSUNIT_ID NUMBER NULL);

 
COMMENT ON COLUMN CUSTOMER.DEFAULTINVOICEBUSINESSUNIT_ID IS 'Default invoicing business unit of this customer';


CREATE  INDEX XIF41CUSTOMER ON CUSTOMER
(DEFAULTINVOICEBUSINESSUNIT_ID   ASC) TABLESPACE INDX;

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUST_DEFINVBUSINESSUNIT_ID FOREIGN KEY (DEFAULTINVOICEBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

UPDATE CUSTOMER CU
SET CU.DEFAULTINVOICEBUSINESSUNIT_ID= CU.DEFAULTBUSINESSUNIT_ID;

COMMIT;

UPDATE CUSTOMER CU
   SET CU.DEFAULTINVOICEBUSINESSUNIT_ID = (SELECT DISTINCT CAI.INVOICEBUSINESSUNIT_ID
                                             FROM CUSTOMERACTIVITIES CAI
                                            WHERE CAI.CUSTOMER_ID = CU.CUSTOMER_ID
                                              AND CAI.INVOICEBUSINESSUNIT_ID != CU.DEFAULTBUSINESSUNIT_ID)
WHERE CU.DEFAULTINVOICEBUSINESSUNIT_ID = CU.DEFAULTBUSINESSUNIT_ID
AND NOT EXISTS 
	(SELECT 1
          FROM CUSTOMERACTIVITIES CA
         WHERE CA.CUSTOMER_ID = CU.CUSTOMER_ID
           AND CA.INVOICEBUSINESSUNIT_ID = CU.DEFAULTBUSINESSUNIT_ID)
   AND (SELECT COUNT(DISTINCT CA.invoicebusinessunit_id)
          FROM CUSTOMERACTIVITIES CA
         WHERE CA.CUSTOMER_ID = CU.CUSTOMER_ID
           AND CA.INVOICEBUSINESSUNIT_ID != CU.DEFAULTBUSINESSUNIT_ID) = 1;

COMMIT;

ALTER TABLE CUSTOMER
  MODIFY (DEFAULTINVOICEBUSINESSUNIT_ID NUMBER NOT NULL);

ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE;    
