-- ABS-49683
-------   COMPLIANCYGROUPATTRIBUTES   ------
ALTER TABLE COMPLIANCYGROUPATTRIBUTES ADD (SYSTEMUSER_ID NUMBER NOT NULL,
					  TIMESTAMP DATE NOT NULL);

create or replace procedure insert_compliancygroupattrib as
begin
  for x in (select c.contract_id, ca.contractattribute_id from contract c, contractattribute ca
    where c.category <> 4
    and ca.groupnumber in (10,11,12,13)
    and ca.sequencenumber > 0) loop
    begin 
      insert into compliancygroupattributes (contract_id, contractattribute_id, systemuser_id, timestamp)
      values (x.contract_id, x.contractattribute_id, getminsystemuser(), sysdate);
       commit;
    exception when others 
        then rollback;
    end;
        end loop;
        end insert_compliancygroupattrib;
        
execute insert_compliancygroupattrib;
drop procedure insert_compliancygroupattrib;

-------   CONTRACTPRODUCTBILLING   ------
ALTER TABLE CONTRACTPRODUCTBILLING ADD (COMPLIANCYRULEPRICING NUMBER NULL
					CONSTRAINT COMPLIANCYRULEPR_VALID1_1 CHECK ((COMPLIANCYRULEPRICING IN (0,1,2) AND COMPLIANCYRULEPRICING IS NOT NULL) OR (COMPLIANCYRULEPRICING IS NULL)));


-------   CONTRACTPRODUCTBGPROTECTION  ------
ALTER TABLE CONTRACTPRODUCTBGPROTECTION DROP CONSTRAINT MMSPECIFICPERCENTAGE_VALID1_1;

ALTER TABLE CONTRACTPRODUCTBGPROTECTION DROP COLUMN MMSPECIFICPERCENTAGE;

ALTER TABLE CONTRACTPRODUCTBGPROTECTION ADD (MMPSPECIFICPERCENTAGE NUMBER NULL CONSTRAINT MMPSPECIFICPERCENTAGE_VALID1_1 CHECK ((MMPSPECIFICPERCENTAGE BETWEEN 0.0 AND 100.0) OR (MMPSPECIFICPERCENTAGE IS NULL)));
