-- ABS-47385/ ABS-48378

ALTER TABLE BUSINESSUNITINTERNALCALCINFO DROP COLUMN INVOICEBUSINESSUNIT_ID;

---BUINTERNALCALCINFO table---
CREATE SEQUENCE SEQ_BUINTERNALCALCINFO
START WITH 1
INCREMENT BY 1;

CREATE TABLE BUINTERNALCALCINFO
(
  BUINTERNALCALCINFO_ID      NUMBER NOT NULL,
  BUSINESSUNIT_ID            NUMBER NOT NULL,
  INTERNALPROCESSINGBU_ID    NUMBER NOT NULL,
  INVOICEBUSINESSUNIT_ID     NUMBER NULL,
  WASHINTERNCALCPERCENTLOG   NUMBER(4,1) DEFAULT 0 NOT NULL,
  WASHINTERNCALCPERCENTCOG   NUMBER(4,1) DEFAULT 0 NOT NULL,
  WASHCOSTPRICELIST_ID       NUMBER NULL,
  SPLITINTERNCALCPERCENTLOG  NUMBER(4,1) DEFAULT 0 NOT NULL,
  SPLITINTERNCALCPERCENTCOG  NUMBER(4,1) DEFAULT 0 NOT NULL,
  RENTCOSTPRICELIST_ID       NUMBER NULL,
  RENTPRICEFACTOR            NUMBER(3,1) DEFAULT 0 NOT NULL,
  RENTINTERNALCALCPERCENTAGE NUMBER(4,1) DEFAULT 0 NOT NULL,
  WASHINTERNCALCPERCENTRES   NUMBER(4,1) DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER NOT NULL,
  TIMESTAMP                  DATE NOT NULL
);

COMMENT ON COLUMN BUINTERNALCALCINFO.WASHINTERNCALCPERCENTLOG
  IS 'Internal calculation percentage for
LOG items, only relevant when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.WASHINTERNCALCPERCENTCOG
  IS 'Internal calculation percentage for
COG items, only relevant when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.SPLITINTERNCALCPERCENTLOG
  IS 'Internal calculation percentage for
LOG items, only relevant when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.SPLITINTERNCALCPERCENTCOG
  IS 'Internal calculation percentage for
COG items, only relevant when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.RENTPRICEFACTOR
  IS 'Price factor for rental items, only relevant
when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.RENTINTERNALCALCPERCENTAGE
  IS 'Internal calculation percentage for
rental items, only relevant when module INTERNALCALC is active.';

COMMENT ON COLUMN BUINTERNALCALCINFO.WASHINTERNCALCPERCENTRES
  IS 'Internal calculation percentage for 
residential items, only relevant when module INTERNALCALC is active.';
  
ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT XPKBUINTERNALCALCINFO PRIMARY KEY (BUINTERNALCALCINFO_ID);
  
CREATE UNIQUE INDEX XAK1BUINTERNALCALCINFO ON BUINTERNALCALCINFO (BUSINESSUNIT_ID ASC, INTERNALPROCESSINGBU_ID ASC, INVOICEBUSINESSUNIT_ID ASC);

CREATE INDEX XIF1BUINTERNALCALCINFO ON BUINTERNALCALCINFO (BUSINESSUNIT_ID ASC);
CREATE INDEX XIF2BUINTERNALCALCINFO ON BUINTERNALCALCINFO (INTERNALPROCESSINGBU_ID ASC);
CREATE INDEX XIF5BUINTERNALCALCINFO ON BUINTERNALCALCINFO (INVOICEBUSINESSUNIT_ID ASC);
CREATE INDEX XIF3BUINTERNALCALCINFO ON BUINTERNALCALCINFO (WASHCOSTPRICELIST_ID ASC);
CREATE INDEX XIF4BUINTERNALCALCINFO ON BUINTERNALCALCINFO (RENTCOSTPRICELIST_ID ASC);
  
ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT BUINTERNALCALCINFO_BU FOREIGN KEY (BUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
  
ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT BUINTERNALCALCINFO_INTERNALBU FOREIGN KEY (INTERNALPROCESSINGBU_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID); 
   
ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT BUINTERNALCALCINFO_INVBU FOREIGN KEY (INVOICEBUSINESSUNIT_ID)
  REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);
  
ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT BUINTERNALCALCIN_WASHCOSTPRICE FOREIGN KEY (WASHCOSTPRICELIST_ID)
  REFERENCES COSTPRICELIST (COSTPRICELIST_ID);

ALTER TABLE BUINTERNALCALCINFO
  ADD CONSTRAINT BUINTERNALCALCIN_RENTPRICELIST FOREIGN KEY (RENTCOSTPRICELIST_ID)
  REFERENCES COSTPRICELIST (COSTPRICELIST_ID);
  
insert into BUINTERNALCALCINFO
  select seq_buinternalcalcinfo.nextval,
         buint.businessunit_id,
         buint.internalprocessingbu_id,
         null,
         buint.washinterncalcpercentlog,
         buint.washinterncalcpercentcog,
         buint.washcostpricelist_id,
         buint.splitinterncalcpercentlog,
         buint.splitinterncalcpercentcog,
         buint.rentcostpricelist_id,
         buint.rentpricefactor,
         buint.rentinternalcalcpercentage,
         buint.washinterncalcpercentres,
         buint.systemuser_id,
         buint.timestamp
    from businessunitinternalcalcinfo buint;
commit;
