-- SO 20014797


---PRODUCTLISTPERCUSTOMER table---


ALTER TABLE PRODUCTLISTPERCUSTOMER
  ADD (RENTBUSINESSUNIT_ID NUMBER NULL);


COMMENT ON COLUMN PRODUCTLISTPERCUSTOMER.RENTBUSINESSUNIT_ID IS 'Only used for module WEEKSTATISTICS, when there are multiple processing 

business units defined for the activity of the product for this customer.';

ALTER TABLE PRODUCTLISTPERCUSTOMER
ADD CONSTRAINT PRODUCTLISTPERCUSTOMER_RENTBU FOREIGN KEY (RENTBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;


CREATE INDEX XIF22PRODUCTLISTPERCUSTOMER ON PRODUCTLISTPERCUSTOMER
(RENTBUSINESSUNIT_ID  ASC);


---MISCELLANEOUSCHARGE table---


ALTER TABLE MISCELLANEOUSCHARGE
  ADD (BUSINESSUNIT_ID NUMBER NULL);


COMMENT ON COLUMN MISCELLANEOUSCHARGE.BUSINESSUNIT_ID IS 'Only used for module WEEKSTATISTICS.';

ALTER TABLE MISCELLANEOUSCHARGE
ADD CONSTRAINT MISCELLANEOUSCHARGE_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;


CREATE INDEX XIF7MISCELLANEOUSCHARGE ON MISCELLANEOUSCHARGE
(BUSINESSUNIT_ID  ASC);


---CLIENTINVOICECATEGORY table---


ALTER TABLE CLIENTINVOICECATEGORY
  ADD (BUSINESSUNIT_ID NUMBER NULL);


COMMENT ON COLUMN CLIENTINVOICECATEGORY.BUSINESSUNIT_ID IS 'Only used for module Weekstatistics for registering the minimum invoice amount.';

ALTER TABLE CLIENTINVOICECATEGORY
ADD CONSTRAINT CLIENTINVOICECATEGORY_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;


CREATE INDEX XIF3CLIENTINVOICECATEGORY ON CLIENTINVOICECATEGORY
(BUSINESSUNIT_ID  ASC);


---RESIDENTIALRENT table---


ALTER TABLE RESIDENTIALRENT
  ADD (BUSINESSUNIT_ID NUMBER NULL);

ALTER TABLE RESIDENTIALRENT
ADD CONSTRAINT RESIDENTIALRENT_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;

CREATE INDEX XIF5RESIDENTIALRENT ON RESIDENTIALRENT
(BUSINESSUNIT_ID  ASC);




---CUSTOMERSPECIALCHARGE table---


ALTER TABLE CUSTOMERSPECIALCHARGE
  ADD (BUSINESSUNIT_ID NUMBER NULL);

ALTER TABLE CUSTOMERSPECIALCHARGE
ADD CONSTRAINT CUSTOMERSPECIALCHARGE_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID) ON DELETE SET NULL;

CREATE INDEX XIF4CUSTOMERSPECIALCHARGE ON CUSTOMERSPECIALCHARGE
(BUSINESSUNIT_ID  ASC);


begin
  execute immediate ('update residentialrent rr
   set rr.businessunit_id = (case
                              when (select count(ca.processbusinessunit_id)
                                      from customeractivities ca,
                                           productgroup       pg,
                                           product            pro
                                     where pro.productgroup_id = pg.productgroup_id
                                       and ca.customer_id = rr.customer_id
                                       and rr.product_id = pro.product_id
                                       and ca.activity_id = pg.activity_id) > 1 then
                               null
                              else
                               (select ca.processbusinessunit_id
                                      from customeractivities ca,
                                           productgroup       pg,
                                           product            pro
                                     where pro.productgroup_id = pg.productgroup_id
                                       and ca.customer_id = rr.customer_id
                                       and rr.product_id = pro.product_id
                                       and ca.activity_id = pg.activity_id)
                            end)
');
exception
  when others then
    null;
end;
/
