--20010831.80

--changes on table invoiceline

ALTER TABLE INVOICELINE
	ADD (PROCESSINGBUSINESSUNIT_ID NUMBER NULL);

ALTER TABLE INVOICELINE
ADD CONSTRAINT INVOICELINE_PROCESSINGBU_ID FOREIGN KEY (PROCESSINGBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE  INDEX XIF6INVOICELINE ON INVOICELINE
(PROCESSINGBUSINESSUNIT_ID   ASC) TABLESPACE INDX;




--changes on table customer

ALTER TABLE CUSTOMER
	DROP CONSTRAINT CUSTOMERTYPE_VALID;

ALTER TABLE CUSTOMER
	ADD CONSTRAINT CUSTOMERTYPE_VALID  CHECK (CUSTOMERTYPE BETWEEN 0 AND 2);

ALTER TABLE CUSTOMER
	ADD (INTERNALBUSINESSUNIT_ID NUMBER NULL);

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_BUSINESSUNIT FOREIGN KEY (INTERNALBUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

CREATE  INDEX XIF39CUSTOMER ON CUSTOMER
(INTERNALBUSINESSUNIT_ID   ASC) TABLESPACE INDX;

COMMENT ON COLUMN CUSTOMER.CUSTOMERTYPE IS '0 = External
1 = Internal (stockroom)
2 = Internal (Business unit)';




--changes on table artransledgeraccounts
ALTER TABLE ARTRANSLEDGERACCOUNTS
	ADD (BUSINESSUNIT_ID NUMBER NULL);

UPDATE ARTRANSLEDGERACCOUNTS A
SET A.BUSINESSUNIT_ID = (SELECT ACT.BUSINESSUNIT_ID
  FROM ARTRANSACTION ACT
 WHERE A.ARTRANSACTION_ID = ACT.ARTRANSACTION_ID);
COMMIT;

ALTER TABLE ARTRANSLEDGERACCOUNTS
MODIFY BUSINESSUNIT_ID NOT NULL;

ALTER TABLE ARTRANSLEDGERACCOUNTS 
DROP CONSTRAINT XPKARTRANSLEDGERACCOUNTS;

DROP INDEX XPKARTRANSLEDGERACCOUNTS;

ALTER TABLE ARTRANSLEDGERACCOUNTS  
ADD CONSTRAINT XPKARTRANSLEDGERACCOUNTS PRIMARY KEY (ARTRANSACTION_ID, BUSINESSUNIT_ID, LEDGERACCOUNTS_ID)
USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF3ARTRANSLEDGERACCOUNTS ON ARTRANSLEDGERACCOUNTS
(BUSINESSUNIT_ID   ASC) TABLESPACE INDX;

ALTER TABLE ARTRANSLEDGERACCOUNTS
ADD CONSTRAINT ARTRANSLEDGERACCOUNTS_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);




-- changes on table artransactiondetail
ALTER TABLE ARTRANSACTIONDETAIL
	ADD (BUSINESSUNIT_ID NUMBER NULL);

/*UPDATE ARTRANSACTIONDETAIL AD
SET AD.BUSINESSUNIT_ID = (
SELECT AC.BUSINESSUNIT_ID
  FROM ARTRANSACTION AC
 WHERE Ad.ARTRANSACTION_ID = AC.ARTRANSACTION_ID);
COMMIT;*/

create or replace procedure UPDATE_BU_ARTRANSDETAIL is
    cursor vv is
      select distinct a.businessunit_id bu
        from artransaction a;
begin    
     for v in vv loop
      UPDATE ARTRANSACTIONDETAIL AD
         SET AD.BUSINESSUNIT_ID = v.bu
       where ad.artransaction_id in
             (SELECT Ac.ARTRANSACTION_ID
                FROM ARTRANSACTION AC
               WHERE Ac.BUSINESSUNIT_ID = v.bu);
      commit;        
     end loop;
end UPDATE_BU_ARTRANSDETAIL;
/

execute UPDATE_BU_ARTRANSDETAIL;

drop procedure UPDATE_BU_ARTRANSDETAIL;


ALTER TABLE ARTRANSACTIONDETAIL
MODIFY BUSINESSUNIT_ID NOT NULL;

ALTER TABLE ARTRANSACTIONDETAIL
DROP CONSTRAINT XPKARTRANSACTIONDETAIL;

DROP INDEX XPKARTRANSACTIONDETAIL;


ALTER TABLE ARTRANSACTIONDETAIL  
ADD CONSTRAINT XPKARTRANSACTIONDETAILS PRIMARY KEY (ARTRANSACTION_ID, TURNOVERGROUP_ID, BUSINESSUNIT_ID)
USING INDEX TABLESPACE INDX;

CREATE  INDEX XIF3ARTRANSACTIONDETAIL ON ARTRANSACTIONDETAIL
(BUSINESSUNIT_ID   ASC) TABLESPACE INDX;

ALTER TABLE ARTRANSACTIONDETAIL
ADD CONSTRAINT ARTRANSACTIONDETAIL_BU FOREIGN KEY (BUSINESSUNIT_ID) REFERENCES BUSINESSUNIT (BUSINESSUNIT_ID);

