-- ABS-121969 - ABS-149491
  
--add requested column
alter table CUSTOMER ADD(OWNBATCHLEVEL number default 0 not null constraint CHK_CUSTOMER_OWNBATCHLEVEL check(OWNBATCHLEVEL between 0 and 4)); 
comment on COLUMN CUSTOMER.OWNBATCHLEVEL is
 '0 = Customer can be mixed with other customer (no own batch)
                                             1 = Customer can be mixed with customers belonging to the same debtor,
                                             2 = Customer own batch
                                             3 = Department own batch
                                             4 = Consumption point own batch';

    
  -- Disable trigger and do the update
create or replace procedure check_trigger_status is
  lv_status varchar2(10);
begin
  select STATUS
    into lv_status
    from USER_TRIGGERS
   where TRIGGER_NAME = 'CUSTOMER_CHECK_CONTRACT_USAGE';
   
  if (lv_status = 'ENABLED') then
    execute immediate 'ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE DISABLE';
  end if;
   
  update CUSTOMER C
    set C.OWNBATCHLEVEL = (case
                              when C.OWNBATCH = 'Y' then
                               2
                              when C.OWNBATCH = 'N' then
                               0
                            end);
                            
   commit;
   
   if(lv_status = 'ENABLED') then                     
    execute immediate 'ALTER TRIGGER CUSTOMER_CHECK_CONTRACT_USAGE ENABLE';   
   end if; 

end check_trigger_status;
/

                                            
execute check_trigger_status;
drop procedure check_trigger_status;
