CREATE OR REPLACE procedure V8_UPD_ADDGUESTCOMMENTRIGHTS is
begin
  for GST in (select distinct wusr_wegr_id from wusr where wusr_wegr_id is not null and wusr_name like 'Customer Profiles%') loop
    insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, 1, 'Customer ProfilesGuest CommentsView', null, gst.wusr_wegr_id); 
     insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, 1, 'Customer ProfilesGuest CommentsEdit', null, gst.wusr_wegr_id);
       insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, 1, 'Customer ProfilesGuest CommentsInsert', null, gst.wusr_wegr_id);
       insert into wusr (WUSR_ID, WUSR_VALUE, WUSR_NAME, WUSR_XCMS_ID, WUSR_WEGR_ID) values 
       (seq_wusr.nextval, 1, 'Customer ProfilesGuest CommentsDelete', null, gst.wusr_wegr_id);
  end loop;
end  V8_UPD_ADDGUESTCOMMENTRIGHTS;
/

create or replace function getYDetDiscountedAmountTMP(
   rateAmount number
  ,rateManual number
  ,discountAmount number
  ,discountPercentage number
  ,noDiscountPackage number
  ) 
return number
is
begin
  if rateamount is null then return null;
    --null value is used for non existing foreign amounts for local currency reservations.
  else return
      ( nvl(ratemanual,rateamount)
       +nvl(discountAmount,0)
       -nodiscountpackage
      )
     *((100-nvl(discountPercentage,0))/100)
     +nodiscountpackage;
  end if; 
end getYDetDiscountedAmountTMP;
/

--For all hotels: partners, mirrors and master.
--Be it new hc install, or updating existing databases.

----------------------------------------------------------------
--Fill default values
----------------------------------------------------------------

create or replace procedure v8CreateYpcfRowsForCreatedIn as
cursor c is 
 select xcms_id
 from xcms
 where not exists (select 1 from ypcf where ypcf_xcms_id=xcms_id);
r c%rowtype;   
begin
  open c;
  loop
    fetch c into r;
    exit when c%NOTFOUND;  
    insert into ypcf (ypcf_id,ypcf_xcms_id,ypcf_createdinhotel) values (seq_ypcf.nextval,r.xcms_id,(select wuss_value from wuss where wuss_name='Hotelid'));
    commit;
  end loop;
  close c;    
end v8CreateYpcfRowsForCreatedIn;
/

----------------------------------------------------------------
--Create autofill code
----------------------------------------------------------------

create or replace view v8_sys_xcms_custom_field as
select 
  xcms_id
 ,nvl(ypcf_createdInHotel,wuss_value) ypcf_createdInHotel 
from xcms,wuss,ypcf
where wuss_name(+)='Hotelid' 
and ypcf_xcms_id(+)=xcms_id;
/

create or replace procedure profileCustomFieldAutoFillProc(xcmsid integer) as
cursor cFields is 
  select /*+rule*/ cname
  from col c  
  where tname='V8_SYS_XCMS_CUSTOM_FIELD'
  and exists (select 1 from col cc where c.cname=cc.cname and cc.tname='YPCF');
rFields cFields%rowtype;
TYPE ct is REF CURSOR;
c ct;
queryStr varchar2(200);
dmlStr varchar2(200);  
value varchar2(200);
ypcfid number(30);
begin
  select count(1) into ypcfid from ypcf where ypcf_xcms_id=xcmsid;
  open cFields;
  loop
     fetch cFields into rFields;
     exit when cFields%notfound;
     queryStr:= 'select '||rFields.cname||' a from v8_sys_xcms_custom_field where xcms_id=:xcmsid';
     open c for queryStr using xcmsid; 
     fetch c into value;
     if c%found then
       if ypcfid>0 then 
          dmlStr:= 'update ypcf set '||rFields.cname||'=:value where ypcf_xcms_id=:xcmsid';
       else
          dmlStr:= 'insert into ypcf (ypcf_id,'||rFields.cname||',ypcf_xcms_id) values (seq_ypcf.nextval,:value,:xcmsid)';
       end if;
       execute immediate dmlStr using value,xcmsid;
     end if;
     close c;
  end loop;
  close cFields;
end profileCustomFieldAutoFillProc;
/ 

CREATE OR REPLACE PACKAGE xcmsCustomFieldPackage AS
  PROCEDURE xcms_row_change (xcmsid IN  xcms.xcms_id%TYPE);
  PROCEDURE xcms_statement_change;
end xcmsCustomFieldPackage; 
/

CREATE OR REPLACE PACKAGE BODY xcmsCustomFieldPackage AS

TYPE t_change_tab IS TABLE OF xcms.xcms_id%type;
g_change_tab  t_change_tab := t_change_tab();

PROCEDURE xcms_row_change (xcmsid IN xcms.xcms_id%TYPE) IS
BEGIN
  g_change_tab.extend;
  g_change_tab(g_change_tab.last):= xcmsid;
END xcms_row_change;

PROCEDURE xcms_statement_change IS
  l_count  NUMBER(10);
BEGIN
  if g_change_tab.last>0 then  
     FOR i IN g_change_tab.first .. g_change_tab.last LOOP
        profileCustomFieldAutoFillProc(g_change_tab(i));
     END LOOP;
  end if;
  g_change_tab.delete;
END xcms_statement_change;

end xcmsCustomFieldPackage;
/

create or replace trigger profileCustomFieldFill_Row after insert or update on xcms for each row
begin
  xcmsCustomFieldPackage.xcms_row_change(:new.xcms_id);
end;
/

create or replace trigger profileCustomFieldFill_Table after insert or update on xcms
begin
  xcmsCustomFieldPackage.xcms_statement_change;
end;
/

----------------------------------------------------------------

create or replace procedure V8_UPD_SelectRatesNotLinked is
  cursor OOO is
    select distinct wusr_wegr_id from wusr where wusr_wegr_id is not null and wusr_name not like 'RatesRate SelectionSelectRatesNotLinkedToCustomer';
  cooo OOO%rowtype;

begin

delete from wusr where WUSR_NAME like 'RatesRate SelectionSelectRatesNotLinkedToCustomer';

  for cooo in ooo loop
    insert into wusr values (seq_wusr.nextval, null, cooo.wusr_wegr_id, 'RatesRate SelectionSelectRatesNotLinkedToCustomer', 1); 
  end loop;

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