create or replace procedure V8_UPD_GLOBALUSERLOGRIGHTS is
  cursor OOO is
    select distinct wusr_wegr_id from wusr where wusr_wegr_id is not null and wusr_name not like 'Miscellaneous{mainmiscellaneous}Global User Log';
  cooo OOO%rowtype;

begin

delete from wusr where WUSR_NAME like 'Miscellaneous{mainmiscellaneous}Global User Log';

  for cooo in ooo loop
    insert into wusr values (seq_wusr.nextval, null, cooo.wusr_wegr_id, 'Miscellaneous{mainmiscellaneous}Global User Log', 1); 
  end loop;

  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_GLOBALUSERLOGRIGHTS;
/

CREATE OR REPLACE function evaluateVAT(zdcoid number, ondate Date, grossamount number) return varchar2 is
  TYPE refCursor IS REF CURSOR;
  net number;
  totaltax number;
  perc number;
  formula varchar2(2000);
  rcursor refCursor;
  
  cursor deptcodetaxes is
    select ztcd_udf taxformula
    from zdco, zdtl, ztxc, ztcd
    where zdco_id=zdtl_zdco_id and zdtl_ztxc_id=ztxc_id and ztxc_id=ztcd_ztxc_id
          and zdco_id=zdcoid
          and zdtl_fromtime<=ondate and ondate<=zdtl_totime
    order by ztcd_order;      
  rowdeptcodetaxes deptcodetaxes%rowtype;          
begin
  formula := '';
  open deptcodetaxes;
  loop
    fetch deptcodetaxes into rowdeptcodetaxes;
    exit when deptcodetaxes%notfound;
    
    if formula <> ' ' then
      formula := formula||' + ';
    end if;
      
    formula := formula||rowdeptcodetaxes.taxformula; 
    
  end loop;  
  close deptcodetaxes;
  
  totaltax :=0;
  
  if formula <> ' ' then
    formula:=replace(formula, 'x', to_char(1));
    formula:='(1+'||formula||')';
    open rcursor for 'select '||trim(formula)||' val from dual';
    fetch rcursor into perc;
    close rcursor;

    --get the vat        
    formula:=to_char(grossamount, '999999999.9999')||'*(1 - 1/'||to_char(perc, '999.9999')||')';
    open rcursor for 'select '||trim(formula)||' val from dual';
    fetch rcursor into totaltax;
    close rcursor;
  end if;
  return trunc(totaltax, 4);
end;
/

CREATE OR REPLACE procedure UpdateYevmDisplayOrder as

  ybomid number;
  eventdate date;
  dorder number; 
  evorder number; 
  prevyevmid number;
  systemdate date;
  
  cursor ybomcursor(fromdate date) is
    select ybom_id
    from ybom
    where ybom_arrival >=fromdate;
  ybomrow ybomcursor%rowtype;
  
  cursor yevmdatescursor(ybomid number) is
    select distinct trunc(yevm_starttime) yevm_date
    from yevm
    where yevm_ybom_id=ybomid;
  yevmdatesrow yevmdatescursor%rowtype;  
  
  cursor yevmcursor(ybomid number, eventdate date) is
    select a.yevm_displorder, a.yevm_id, a.yevm_master_yevm_id, a.yevm_starttime, 
    (select count(1) from yevm b where b.yevm_master_yevm_id = a.yevm_id) cntsubevents, 
    b.yevm_displorder sub_yevm_displorder, b.yevm_id sub_yevm_id, b.yevm_starttime sub_yevm_starttime 
    from yevm a, (select * from yevm b where b.yevm_ybom_id=ybomid ) b 
    where a.yevm_ybom_id=ybomid and trunc(a.yevm_starttime)=eventdate  
    and nvl(a.yevm_master_yevm_id, 0) = 0 and a.yevm_id=b.yevm_master_yevm_id(+) 
    order by a.yevm_displorder, b.yevm_displorder;
  yevmrow yevmcursor%rowtype;  
    
    
begin
  select wgbs_datevalue
  into systemdate
  from fideliodate; 

  open ybomcursor(systemdate);
  loop
    fetch ybomcursor into ybomrow;
    exit when ybomcursor%notfound;
    ybomid := ybomrow.ybom_id;
    
    open yevmdatescursor(ybomid);
    loop
      fetch yevmdatescursor into yevmdatesrow;
      exit when yevmdatescursor%notfound;
    
      eventdate := yevmdatesrow.yevm_date;
      
      dorder := 1; 
      evorder := 0; 
      prevyevmid := 0;
      
      open yevmcursor(ybomid, eventdate);
      loop
        fetch yevmcursor into yevmrow;
        exit when yevmcursor%notfound;
        
        if (prevyevmid <> yevmrow.yevm_id)  then  --only once for each master event
          if (dorder <> yevmrow.yevm_displorder) then 
            update yevm set yevm_displorder=dorder where yevm_id=yevmrow.yevm_id;
          end if;  
          dorder := dorder + 1;
        end if;  
        
        if (yevmrow.sub_yevm_id > 0) then --it is a subevent
          if dorder <> yevmrow.sub_yevm_displorder then
            update yevm set yevm_displorder=dorder where yevm_id=yevmrow.sub_yevm_id;
          end if;  
          dorder := dorder + 1;
        end if;  
        
        prevyevmid := yevmrow.yevm_id;
  
      end loop;
      close yevmcursor;
      
    end loop;
    close yevmdatescursor;  
  
  end loop;
  close ybomcursor;
  
end;
/














CREATE OR REPLACE procedure V8_UPD_ADDHSKRIGHTS is
begin
  for hsk in (select distinct wusr_wegr_id from wusr where wusr_wegr_id is not null and wusr_name like 'Housekeeping%') loop
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, 1, 'HousekeepingHousekeeping Status', null, hsk.wusr_wegr_id); 
  end loop;
end V8_UPD_ADDHSKRIGHTS;
/

CREATE OR REPLACE procedure V8_UPD_FIXARTRANSACTIONRIGHTS is
  cursor OOO is
    select distinct wusr_wegr_id from wusr where wusr_wegr_id is not null;
  cooo OOO%rowtype;

begin
  for cooo in ooo loop
    insert into wusr values (seq_wusr.nextval, null, cooo.wusr_wegr_id, 'Accounts ReceivableTransactionsCompression of Account Transaction Postings', 1); 
    insert into wusr values (seq_wusr.nextval, null, cooo.wusr_wegr_id, 'Accounts ReceivableTransactionsCompression of CC Transaction Postings', 1); 
  end loop;

  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_FIXARTRANSACTIONRIGHTS;
/

CREATE OR REPLACE procedure V8_UPD_FIXMOVEPOSTINGRIGHTS is
begin

  for mp in (select * from wusr where wusr_name='CashieringMove Postings') loop
  
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'CashieringMove PostingsMove Postings To Another Folio', mp.wusr_xcms_id, mp.wusr_wegr_id); 
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'CashieringMove PostingsMove Postings On Same Folio', mp.wusr_xcms_id, mp.wusr_wegr_id);
 
  end loop;

  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_FIXMOVEPOSTINGRIGHTS;
/

CREATE OR REPLACE procedure V8_UPD_FIXXCMSEDITBYTYPE is
begin
  for mp in (select * from wusr where wusr_name='Customer ProfilesProfile DetailsEdit') loop
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditCompany', mp.wusr_xcms_id, mp.wusr_wegr_id);
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditTravel Agent', mp.wusr_xcms_id, mp.wusr_wegr_id);
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditSource', mp.wusr_xcms_id, mp.wusr_wegr_id);
                insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditIndividual', mp.wusr_xcms_id, mp.wusr_wegr_id); 
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditVendors', mp.wusr_xcms_id, mp.wusr_wegr_id);
                   insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsEditMeeting Planner', mp.wusr_xcms_id, mp.wusr_wegr_id);   
  end loop;
  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_FIXXCMSEDITBYTYPE;
/

CREATE OR REPLACE procedure V8_UPD_FIXXCMSINSERTBYTYPE is
begin
  for mp in (select * from wusr where wusr_name='Customer ProfilesProfile DetailsInsert') loop  
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertCompany', mp.wusr_xcms_id, mp.wusr_wegr_id);
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertTravel Agent', mp.wusr_xcms_id, mp.wusr_wegr_id);
                insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertSource', mp.wusr_xcms_id, mp.wusr_wegr_id);
                insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertIndividual', mp.wusr_xcms_id, mp.wusr_wegr_id);  
                insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertVendors', mp.wusr_xcms_id, mp.wusr_wegr_id);
                insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, mp.wusr_value, 'Customer ProfilesProfile DetailsInsertMeeting Planner', mp.wusr_xcms_id, mp.wusr_wegr_id); 
  end loop;
  exception
    when no_data_found then
      raise; --null;
    when others then
      raise; --null;  
end V8_UPD_FIXXCMSINSERTBYTYPE;
/
