CREATE SEQUENCE seq_m_rl_log START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

CREATE TABLE m_rl_log
    (log_id                         NUMBER ,
    procedure_name                 VARCHAR2(100 BYTE),
    log_timestamp                  TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
    input_params                   VARCHAR2(4000 BYTE),
    error_code                     VARCHAR2(100 BYTE),
    error_message                  VARCHAR2(4000 BYTE),
    call_stack                     CLOB,
    created_by                     VARCHAR2(100 BYTE) DEFAULT USER)
  SEGMENT CREATION IMMEDIATE
  PCTFREE     10
  INITRANS    1
  MAXTRANS    255
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
  NOCACHE
  MONITORING
  LOB ("CALL_STACK") STORE AS SECUREFILE SYS_LOB0000115970C00007$$
  (
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     106496
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
   NOCACHE LOGGING
   CHUNK 8192
  )
  NOPARALLEL
  LOGGING
/




ALTER TABLE m_rl_log
ADD PRIMARY KEY (log_id)
USING INDEX
  PCTFREE     10
  INITRANS    2
  MAXTRANS    255
  TABLESPACE  v8main
  STORAGE   (
    INITIAL     65536
    NEXT        1048576
    MINEXTENTS  1
    MAXEXTENTS  2147483645
  )
/




CREATE OR REPLACE 
PROCEDURE print_clob_to_output (p_clob IN CLOB)
 IS
   l_offset     INT := 1;
 BEGIN
   dbms_output.put_line('Print CLOB');  
    loop
        exit when l_offset > dbms_lob.getlength(p_clob);
        dbms_output.put_line( dbms_lob.substr( p_clob, 255, l_offset ) );
        l_offset := l_offset + 255;
    END LOOP;
 END print_clob_to_output;
/


-- Start of DDL Script for Function V8LIVE.M_CRSTOFID
-- Generated 23.06.2023 5:51:34 from V8LIVE@V8
create or replace view m_avl as
select 
    tot.YRMS_YCAT_ID ycat_id
    , tot.WDAT_DATE wdat_date
    , tot.NOOFROOMS tot
    , yocc.defNOOFROOMS def
    , bocc.defNOOFROOMS bld
    , nvl(tot.noofrooms, 0) - nvl(yocc.defnoofrooms, 0) - nvl(bocc.defnoofrooms, 0) avl
from v8_sys_noofroomspercategory tot, v8_sys_ydet_occ_wb yocc, v8_sys_ybld_occ bocc
where
    yocc.YDET_YCAT_ID(+) = tot.yrms_ycat_id
    and yocc.YDET_DATE(+) = tot.wdat_date
    and bocc.YBLD_YCAT_ID(+) = tot.yrms_ycat_id
    and bocc.YBLD_DATE(+) = tot.wdat_date;


CREATE OR REPLACE 
FUNCTION m_getzcnvid
return number
as
r number;
begin
select zota.zota_zcnv_id into r from zota where zota_name like 'TravelLine';
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE 
function m_is_weekend(rid yrch.yrch_id%type, dat wdat.wdat_date%type) return number
as
r yrch%rowtype;
w number;
begin
w := 0;

select case when to_char(dat,'D')=1 and yrch_weekend_sun=1 then 1
                        when to_char(dat,'D')=2 and yrch_weekend_mon=1 then 1
                        when to_char(dat,'D')=3 and yrch_weekend_tue=1 then 1
                        when to_char(dat,'D')=4 and yrch_weekend_wed=1 then 1
                        when to_char(dat,'D')=5 and yrch_weekend_thu=1 then 1
                        when to_char(dat,'D')=6 and yrch_weekend_fri=1 then 1
                        when to_char(dat,'D')=7 and yrch_weekend_sat=1 then 1
                        else 0 end into w from yrch where yrch_id = rid;
                    
return w;                    
                        
end;

-- Start of DDL Script for Function V8LIVE.M_YRCH_PRICE_ANT_V1
-- Generated 27-aae-2022 16:36:53 from V8LIVE@V8
/




CREATE OR REPLACE 
FUNCTION m_crstofid(id varchar2)
return number
as
r number;
begin

select mcnv.mcnv_pms_code into r from mcnv where mcnv.mcnv_zcnv_id =
(select zota.zota_zcnv_id from zota where lower(zota.zota_requestorurl) like '%travelline%' and rownum = 1
)
--and mcnv.mcnv_type in ('RC', 'RT')
and mcnv.mcnv_crs_code = id;

return r;
end;
/


CREATE OR REPLACE 
FUNCTION m_fidtocrs(id number)
return varchar2
as
r varchar2(100);
begin
select mcnv.mcnv_crs_code into r from mcnv where mcnv.mcnv_zcnv_id = m_getzcnvid
and mcnv.mcnv_pms_code = id and rownum = 1;
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE 
function m_getrate(id ydet.ydet_id%type)
return ydet.ydet_rateamount%type
as
r ydet.ydet_ratemanual%type;
begin
    r := 0;
    select (decode(ydet.ydet_ratemanual,null,ydet.ydet_rateamount,ydet.ydet_ratemanual)+nvl(ydet.ydet_ratediff,0))
                     *((100-nvl(ydet.ydet_ratediscountperc,0))/100) into r
           from ydet
    where ydet_id = id;
    return r;
    exception when others then return null;
end;

-- Start of DDL Script for View V8LIVE.M_YDET_NOOFROOMS
-- Generated 01.10.2020 20:46:36 from V8LIVE@V8
/


CREATE OR REPLACE 
function m_getrateavxmlbydate(bdate varchar2, edate varchar2) return clob as

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';

cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date 
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;
open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    c := 0;
    rm := '<AvailStatusMessage>';
    rm := rm || '<StatusApplicationControl';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '" Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '"/>';
 
    rm := rm || '<RestrictionStatus Status="';
    select m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, 1, 0) into ratev from dual;
    if ratev = 0 then rm := rm || 'Closed';
        else rm := rm || 'Open';
    end if;
    
    rm := rm || '"/></AvailStatusMessage>';


    end loop;
    close gdat;
    
    q := q || rm;
    rm := '';

    end loop;
    close gt;
    end loop;
    close gd;
    

q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';


return q;


end;
/


CREATE OR REPLACE 
function m_getrateupxml(months number) return clob as
--declare
--months number;
cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date 
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin
--months := 1;
q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';

dbms_output.put_line('Opening details');

open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;

    dbms_output.put_line(to_char(rd.yrcd_id));
    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';        
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/>'; 
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';  
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';     

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;



CREATE OR REPLACE 
FUNCTION m_yrch_price_ant_v1(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ADULTS in number, CALCDAYTYPE in NUMBER)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
    pap number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;

select
        nvl(decode(m_is_weekend(YRCHID_FINAL, WDATDATE), 1, 
            yrcv_weekend_value, 0, yrcv_value, 0), 0) into RatePrice
from yrct, yrch, yrcd, yrcv
where 
    yrcd_id=yrcv_yrcd_id 
    and yrch_id=yrcd_yrch_id 
    and yrcv.yrcv_default = 0
    and yrcv_noofadults=ADULTS
    and yrcd_id=yrct_yrcd_id
    and yrch.yrch_id = YRCHID_FINAL
    and yrcd.yrcd_enddate >= WDATDATE
    and yrcd.yrcd_startdate <= WDATDATE
    and yrct_ycat_id=YCATID;       
        
        
        
   pap := 0;
   begin                 
select sign(rateprice) * nvl(sum(decode(ypap.ypap_type, 1, ypap_value * ADULTS, 2, ypap_value, 0)), 0) into pap
from ypac, ypat, ypap, yrch, yrcp
where yrch_id = yrcp.yrcp_yrch_id
and yrcp.yrcp_ypac_id = ypac.ypac_id
and ypac.ypac_id = ypat.ypat_ypac_id
and ypat.ypat_from <= WDATDATE
and ypat.ypat_until >= WDATDATE
and ypap.ypap_ypat_id = ypat.ypat_id
and yrch_id = YRCHID
and ypap_xcac_id = 0
and ypac_included = 0;                 
         exception when no_data_found then null;

end;
         
                    
SELECT NVL (MAX (yrdv_value), 0)
  INTO daytypeprice
  FROM (SELECT yrdv_value
          FROM yrdc,
               yrdt,
               yrdd,
               yrdy,
               yrde,
               yrdv,
               yrch
         WHERE     yrdc_yrdt_id = yrdt_id
               AND yrdt_id = yrdd_yrdt_id
               AND yrde_id = yrdy_yrde_id
               AND yrdd_id = yrde_yrdd_id
               AND yrdy_id = yrdv_yrdy_id
               AND yrdd_yrds_id = yrch_yrds_id
               AND yrdv_adultcount = 1
               AND wdatdate BETWEEN NVL (yrde_startdate, wdatdate)
                                AND NVL (yrde_enddate, wdatdate)
               AND yrdy_ycat_id = ycatid
               AND yrch_id = yrchid_final
               AND yrdc_date = wdatdate);
               
    if CALCDAYTYPE=1 then
        PRICE:=pap + RatePrice+DayTypePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*(RatePrice+DayTypePrice));
        else
        PRICE:=pap + RatePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*RatePrice);
    end if;
return round(PRICE,0);
END;
/


CREATE OR REPLACE 
FUNCTION m_yrch_price_dm(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ca yrcx.yrcx_id%type)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;
    select nvl(max(Rate_price),0) into RatePrice
        from
        (select wdat_date, yrcd_id, yrcd_yrch_id, yrct_ycat_id,
            nvl(
                (select
                    decode(m_is_weekend(YRCHID_FINAL, wdatdate), 1, yrcx_weekend_value, 0, yrcx.yrcx_value, 0)
                   from yrch, yrcd, yrcx, (select * from yrcv where yrcv.yrcv_default = 1) yrcv
                where yrcd_id=yrcv_yrcd_id and yrch_id=yrcd_yrch_id and yrcd.yrcd_id=y.yrcd_id and rownum=1
                                                and yrcv_id = yrcx.yrcx_yrcv_id and yrcx.yrcx_xcac_id = ca),0) as Rate_price
            from wdat,
                (select nvl(yrse_startdate,yrcd_startdate) st, nvl(yrse_enddate,yrcd_enddate) en, yrcd.* From yrcd, yrse
                    where yrcd_yrse_id=yrse_id(+)) y, yrct
                where wdat_date between st and en and yrcd_id=yrct_yrcd_id
                    and yrct_ycat_id=YCATID and wdat_date = WDATDATE and yrcd_yrch_id=YRCHID_FINAL);
return round(RATEPRICE,0);
END;
/


CREATE OR REPLACE 
function m_getrateupxmlbydate(bdate varchar2, edate varchar2) return clob as
--declare

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';
        --and mcnv.mcnv_crs_code = '286536'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        --and yrcd.yrcd_enddate >= v8_sys_fideliodate
        --and yrcd.yrcd_startdate <= to_date(edate, 'dd.mm.yyyy')
        --and yrcd.yrcd_enddate >= to_date(bdate, 'dd.mm.yyyy');

--cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
--        order by yrcv_default, yrcv_noofadults;
--cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
--    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
--    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date 
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');
cursor gn is select wnum.wnum_value from wnum where wnum_value >= 1 and wnum_value <= 6;
cursor gc is select * from xcac, mcnv where xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' 
    and nvl(mcnv.mcnv_inactive, 0) = 0 and mcnv.mcnv_zcnv_id = m_getzcnvid;


dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
--rx gx%rowtype;
rn number;
rc gc%rowtype;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><RateAmountMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;

    c := 0;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat;
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gn;
        loop fetch gn into rn;
        exit when gn%notfound;
                rma := '';        

                rma := rma || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                --rm := rm || chr(10) || '<BaseByGuestAmt AmountAfterTax="';
                select nvl(m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, rn, 1), 0) into ratev from dual;
                c := c + ratev;
                rma := rma || to_char(ratev);
                rma := rma || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rma := rma || to_char(rn);
                rma := rma || '"/>';
                if (ratev > 0) then rm := rm || rma; end if;
                rma := '';

        end loop;
        close gn;
        
        rm := rm || '</BaseByGuestAmts>';
        rms := '<AdditionalGuestAmounts>';
        rma := '';
        cd := 0;
        open gc;
        loop fetch gc into rc;
        exit when gc%notfound;
            rma := rma || '<AdditionalGuestAmount AgeQualifyingCode="';
            rma := rma || rc.mcnv_crs_code;
            rma := rma || '" Amount="';
            select nvl(m_yrch_price_dm(rd.yrch_id, rt.ycat_id, dat, rc.xcac_id), 0) into ratev from dual;
            rma := rma || to_char(ratev);
            cd := cd + ratev;
            rma := rma || '" CurrencyCode="RUB" TaxInclusive="true"/>';           
            
            if (ratev > 0) then rms := rms || rma; end if;
            rma := '';           
 
        end loop;
        close gc;    
    rms := rms || '</AdditionalGuestAmounts>';
    
    if cd > 0 then rm := rm || rms; end if;
    
    rm := rm || '</Rate>';  
    end loop;
    close gdat;
    
    rm := rm || '</Rates></RateAmountMessage>';     
    if c > 0 then q := q || rm; c := 0; end if;
    rm := '';


    end loop;
    close gt;
    end loop;
    close gd;
    

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('https://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);

return q;


end;
/


CREATE OR REPLACE 
function m_getrateupxmldt(months number)
return clob
as
--declare

cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date 
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';


open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;


    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';        
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/>'; 
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';  
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';     

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;




--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
--select * from yrcv
--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
/


CREATE OR REPLACE 
FUNCTION m_getzcnvid
return number
as
r number;
begin
select zota.zota_zcnv_id into r from zota where zota_name like 'TravelLine';
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE 
FUNCTION m_getroomavxmlbydate(bdate varchar2, edate varchar2) return clob as


cursor gt is select ycat.*, mcnv.* from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT';-- and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;

q clob;
rm clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    begin
        select avl into c from m_avl where wdat_date = dat and ycat_id = rt.ycat_id;
        rm := '<AvailStatusMessage BookingLimitMessageType="2" BookingLimit="' || to_char(c) || '">';
        rm := rm || '<StatusApplicationControl';
        
        rm := rm || ' InvTypeCode="';
        rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
        rm := rm || '" Start="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '" End="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '"/>';

        rm := rm || '</AvailStatusMessage>';
    exception when no_data_found then null;
    end;

    end loop;
    close gdat;

    q := q || rm;
    rm := '';

    end loop;
  
    close gt;

q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';

return q;

end;
/


CREATE OR REPLACE 
function m_is_weekend(rid yrch.yrch_id%type, dat wdat.wdat_date%type) return number
as
r yrch%rowtype;
w number;
begin
w := 0;

select case when to_char(dat,'D')=1 and yrch_weekend_sun=1 then 1
                        when to_char(dat,'D')=2 and yrch_weekend_mon=1 then 1
                        when to_char(dat,'D')=3 and yrch_weekend_tue=1 then 1
                        when to_char(dat,'D')=4 and yrch_weekend_wed=1 then 1
                        when to_char(dat,'D')=5 and yrch_weekend_thu=1 then 1
                        when to_char(dat,'D')=6 and yrch_weekend_fri=1 then 1
                        when to_char(dat,'D')=7 and yrch_weekend_sat=1 then 1
                        else 0 end into w from yrch where yrch_id = rid;
                    
return w;                    
                        
end;

-- Start of DDL Script for Function V8LIVE.M_YRCH_PRICE_ANT_V1
-- Generated 27-aae-2022 16:36:53 from V8LIVE@V8
/

CREATE OR REPLACE 
PROCEDURE m_rladdchild(
    thedate    IN DATE,
    crscat     IN VARCHAR2,
    crsrate    IN VARCHAR2,
    price      IN yrcv.yrcv_value%TYPE,
    crschild   IN VARCHAR2
) AS
    -- Variables
    rateid     NUMBER;
    catid      NUMBER;
    childcat   NUMBER;
    adno       NUMBER := 1; -- Assuming default of 1 adult if not specified
    df         NUMBER := 1; -- Assuming default value
    rv         yrcv%ROWTYPE;
    
    -- Logging variables
    v_input_params VARCHAR2(4000);
    
    -- Procedure to log errors
    PROCEDURE log_error(
        p_error_code    IN VARCHAR2,
        p_error_message IN VARCHAR2,
        p_call_stack    IN VARCHAR2
    ) IS
    BEGIN
        INSERT INTO m_rl_log (
            log_id,
            procedure_name,
            input_params,
            error_code,
            error_message,
            call_stack
        ) VALUES (
            seq_m_rl_log.NEXTVAL,
            'M_RLADDCHILD',
            v_input_params,
            p_error_code,
            p_error_message,
            p_call_stack
        );
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            -- If logging fails, write to server output (ensure DBMS_OUTPUT is enabled)
            DBMS_OUTPUT.PUT_LINE('Failed to log error: ' || SQLERRM);
    END log_error;
    
BEGIN
    -- Capture input parameters for logging
    v_input_params := 'thedate=' || TO_CHAR(thedate, 'YYYY-MM-DD') || 
                     ';crscat=' || crscat ||
                     ';crsrate=' || crsrate ||
                     ';price=' || TO_CHAR(price) ||
                     ';crschild=' || crschild;
    
    -- Initialize and validate main parameters
    BEGIN
        rateid := m_crstofid(crsrate);
        catid := m_crstofid(crscat);
        childcat := m_crstofid(crschild);
    EXCEPTION
        WHEN OTHERS THEN
            log_error(
                SQLCODE,
                'Error during parameter initialization: ' || SQLERRM,
                DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
            );
            RAISE;
    END;
    
    IF rateid IS NOT NULL AND catid IS NOT NULL THEN
        -- Fetch the relevant yrcv record
        BEGIN
            SELECT yrcv.*
            INTO rv
            FROM yrcv
            JOIN yrcd ON yrcv.yrcv_yrcd_id = yrcd.yrcd_id
            JOIN yrct ON yrcd.yrcd_id = yrct.yrct_yrcd_id
            WHERE yrcd.yrcd_startdate <= thedate
              AND yrcd.yrcd_enddate >= thedate
              AND yrcd.yrcd_yrch_id = rateid
              AND yrct.yrct_ycat_id = catid
              AND yrcv.yrcv_noofadults = adno
              AND yrcv.yrcv_default = df;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                -- Handle the case where no data is found
                log_error(
                    SQLCODE,
                    'No YRCV record found for the given parameters.',
                    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                );
                RAISE;
            WHEN OTHERS THEN
                -- Handle other exceptions
                log_error(
                    SQLCODE,
                    'Error fetching YRCV record: ' || SQLERRM,
                    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                );
                RAISE;
        END;
        
        -- Insert or Update into YRCX based on existence
        IF df = 1 THEN
            BEGIN
                DECLARE
                    v_count NUMBER;
                BEGIN
                    -- Count existing yrcx records for the given yrcv_id and xcac_id
                    SELECT COUNT(*)
                    INTO v_count
                    FROM yrcx
                    WHERE yrcx_yrcv_id = rv.yrcv_id
                      AND yrcx_xcac_id = childcat;
                    
                    IF v_count = 1 THEN
                        -- Update the existing record
                        UPDATE yrcx
                        SET yrcx_noofchilds = 1,
                            yrcx_value = price,
                            yrcx_weekend_value = price
                        WHERE yrcx_yrcv_id = rv.yrcv_id
                          AND yrcx_xcac_id = childcat;
                    
                    ELSIF v_count > 1 THEN
                        -- Handle multiple existing records
                        UPDATE yrcx
                        SET yrcx_noofchilds = 1,
                            yrcx_value = price,
                            yrcx_weekend_value = price
                        WHERE yrcx_yrcv_id = rv.yrcv_id
                          AND yrcx_xcac_id = childcat;
                          
                        -- Log that multiple records were updated
                        log_error(
                            'MULTIPLE_RECORDS',
                            'Multiple YRCX records found and updated for yrcv_id=' || rv.yrcv_id || 
                            ' and xcac_id=' || childcat,
                            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                        );
                    
                    ELSE
                        -- No existing record, perform insert
                        INSERT INTO yrcx (
                            yrcx_id, 
                            yrcx_xcac_id, 
                            yrcx_noofchilds, 
                            yrcx_value, 
                            yrcx_yrcv_id, 
                            yrcx_default, 
                            yrcx_weekend_value
                        ) VALUES (
                            seq_yrcx.NEXTVAL, 
                            childcat, 
                            1, 
                            price, 
                            rv.yrcv_id, 
                            df, 
                            price
                        );
                    END IF;
                END;
            EXCEPTION
                WHEN OTHERS THEN
                    log_error(
                        SQLCODE,
                        'Error handling YRCX upsert: ' || SQLERRM,
                        DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                    );
                    RAISE;
            END;
        END IF;
    END IF;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Handle the case where no data is found in the main block
        log_error(
            SQLCODE,
            'No data found for the provided parameters.',
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        );
        NULL; -- Or consider raising a custom exception
    WHEN OTHERS THEN
        -- Handle all other exceptions
        log_error(
            SQLCODE,
            'Main procedure error: ' || SQLERRM,
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        );
        RAISE;
END;
/

CREATE OR REPLACE 
PROCEDURE m_rlputrate (
    p_date      IN DATE,
    p_category  IN VARCHAR2,
    p_rate      IN VARCHAR2,
    p_price     IN yrcv.yrcv_value%TYPE,
    p_adult_no  IN NUMBER
) AS

    -- Cursors
    CURSOR cur_child_categories IS 
        SELECT * FROM xcac 
        WHERE NVL(xcac_inactive, 0) = 0;
        
    -- Variables with clear names
    v_child_category    xcac%ROWTYPE;
    v_rate_details      yrcd%ROWTYPE;
    v_room_type         yrct%ROWTYPE;
    v_adult_count       NUMBER;
    v_is_default        NUMBER;
    v_rate_id           NUMBER;
    v_category_id       NUMBER;
    v_board_type        NUMBER;
    
    -- Logging variables
    v_input_params      VARCHAR2(4000);

    -- Procedure to log errors
    PROCEDURE log_error(
        p_error_code    IN NUMBER, -- Changed to NUMBER
        p_error_message IN VARCHAR2,
        p_call_stack    IN VARCHAR2
    ) IS
    BEGIN
        INSERT INTO m_rl_log (
            log_id,
            procedure_name,
            input_params,
            error_code,
            error_message,
            call_stack
        ) VALUES (
            seq_m_rl_log.NEXTVAL,
            'M_RLPUTRATE',
            v_input_params,
            p_error_code,
            p_error_message,
            p_call_stack
        );
        -- Removed COMMIT; to manage transactions externally
    EXCEPTION
        WHEN OTHERS THEN
            -- If logging fails, write to server output (ensure DBMS_OUTPUT is enabled)
            DBMS_OUTPUT.PUT_LINE('Failed to log error: ' || SQLERRM);
    END log_error;

    -- Helper Procedure to Handle YRCV Records
    PROCEDURE handle_yrcv (
        p_yrcd_id       IN yrcv.yrcv_yrcd_id%TYPE,
        p_noofadults    IN yrcv.yrcv_noofadults%TYPE,
        p_is_default    IN yrcv.yrcv_default%TYPE,
        p_price         IN yrcv.yrcv_value%TYPE
    ) IS
        CURSOR c_yrcv IS
            SELECT yrcv_id 
            FROM yrcv 
            WHERE yrcv_yrcd_id = p_yrcd_id
              AND yrcv_noofadults = p_noofadults
              AND yrcv_default = p_is_default;
        
        TYPE yrcv_id_table IS TABLE OF yrcv.yrcv_id%TYPE;
        v_yrcv_ids yrcv_id_table;
    BEGIN
        BEGIN
            -- Attempt to select YRCV records
            SELECT yrcv_id
            BULK COLLECT INTO v_yrcv_ids
            FROM yrcv 
            WHERE yrcv_yrcd_id = p_yrcd_id
              AND yrcv_noofadults = p_noofadults
              AND yrcv_default = p_is_default;

            IF v_yrcv_ids.COUNT = 1 THEN
                -- Exactly one record found, perform update
                UPDATE yrcv 
                SET yrcv_value = p_price, 
                    yrcv_weekend_value = p_price
                WHERE yrcv_id = v_yrcv_ids(1);
            ELSE
                -- Either no records or multiple records
                -- Delete existing records and insert a new one
                DELETE FROM yrcv 
                WHERE yrcv_yrcd_id = p_yrcd_id
                  AND yrcv_noofadults = p_noofadults
                  AND yrcv_default = p_is_default;

                INSERT INTO yrcv (
                    yrcv_id,
                    yrcv_yrcd_id,
                    yrcv_noofadults,
                    yrcv_value,
                    yrcv_default,
                    yrcv_weekend_value,
                    yrcv_daynum
                ) VALUES (
                    seq_yrcv.NEXTVAL,
                    p_yrcd_id,
                    p_noofadults,
                    p_price,
                    p_is_default,
                    p_price,
                    0
                );
            END IF;

        EXCEPTION 
            WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
                -- Common handling: delete existing rows and insert a new one
                DELETE FROM yrcv 
                WHERE yrcv_yrcd_id = p_yrcd_id
                  AND yrcv_noofadults = p_noofadults
                  AND yrcv_default = p_is_default;

                INSERT INTO yrcv (
                    yrcv_id,
                    yrcv_yrcd_id,
                    yrcv_noofadults,
                    yrcv_value,
                    yrcv_default,
                    yrcv_weekend_value,
                    yrcv_daynum
                ) VALUES (
                    seq_yrcv.NEXTVAL,
                    p_yrcd_id,
                    p_noofadults,
                    p_price,
                    p_is_default,
                    p_price,
                    0
                );

            WHEN OTHERS THEN
                log_error(
                    SQLCODE,
                    'Error handling YRCV: ' || SQLERRM,
                    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                );
                RAISE;
        END;
    END handle_yrcv;

BEGIN
    -- Capture input parameters for logging
    v_input_params := 'p_date=' || TO_CHAR(p_date, 'YYYY-MM-DD') || 
                     ';p_category=' || p_category ||
                     ';p_rate=' || p_rate ||
                     ';p_price=' || TO_CHAR(p_price) ||
                     ';p_adult_no=' || TO_CHAR(p_adult_no);

    -- Initialize and validate main parameters
    BEGIN
        v_rate_id := m_crstofid(p_rate);
        v_category_id := m_crstofid(p_category);
        -- Get board type with simplified decode
        SELECT DECODE(NVL(yrch_boardtype, 0), 4, 3, NVL(yrch_boardtype, 0))
        INTO v_board_type 
        FROM yrch 
        WHERE yrch_id = v_rate_id;
    EXCEPTION
        WHEN OTHERS THEN
            log_error(
                SQLCODE,
                'Error during parameter initialization: ' || SQLERRM,
                DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
            );
            RAISE;
    END;

    IF v_rate_id IS NOT NULL AND v_category_id IS NOT NULL THEN
        -- Get or create rate details (YRCD)
        BEGIN
            SELECT yrcd_id, yrcd_yrch_id, yrcd_startdate, yrcd_enddate,
                   yrcd_yrse_id, yrcd_xcma_id, yrcd_pppn, yrcd_ppps,
                   yrcd_prpn, yrcd_prps, yrcd_yprm_id, yrcd_base_type,
                   yrcd_base_rounding, yrcd_base_value, yrcd_yras_id,
                   yrcd_base_type_we, yrcd_base_rounding_we, yrcd_base_value_we
            INTO v_rate_details.yrcd_id, v_rate_details.yrcd_yrch_id, v_rate_details.yrcd_startdate, v_rate_details.yrcd_enddate,
                 v_rate_details.yrcd_yrse_id, v_rate_details.yrcd_xcma_id, v_rate_details.yrcd_pppn, v_rate_details.yrcd_ppps,
                 v_rate_details.yrcd_prpn, v_rate_details.yrcd_prps, v_rate_details.yrcd_yprm_id, v_rate_details.yrcd_base_type,
                 v_rate_details.yrcd_base_rounding, v_rate_details.yrcd_base_value, v_rate_details.yrcd_yras_id,
                 v_rate_details.yrcd_base_type_we, v_rate_details.yrcd_base_rounding_we, v_rate_details.yrcd_base_value_we
            FROM yrcd
            JOIN yrct ON yrcd.yrcd_id = yrct.yrct_yrcd_id
            WHERE yrcd_startdate <= p_date
                AND yrcd_enddate >= p_date
                AND yrcd_yrch_id = v_rate_id
                AND yrct_ycat_id = v_category_id;
            
            -- Update YRCD dates
            UPDATE yrcd 
            SET yrcd_startdate = p_date, 
                yrcd_enddate = p_date
            WHERE yrcd_id = v_rate_details.yrcd_id;
        EXCEPTION 
            WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
                -- Common handling: delete existing YRCD records and insert a new one
                DELETE FROM yrcd
                WHERE yrcd_yrch_id = v_rate_id
                and yrcd.yrcd_startdate <= p_date
                and yrcd.yrcd_enddate >= p_date
                  AND EXISTS (
                      SELECT 1 FROM yrct 
                      WHERE yrct.yrct_yrcd_id = yrcd.yrcd_id
                        AND yrct_ycat_id = v_category_id
                  );
                select seq_yrcd.NEXTVAL into v_rate_details.yrcd_id from dual;
                INSERT INTO yrcd (
                    yrcd_id,
                    yrcd_yrch_id,
                    yrcd_startdate,
                    yrcd_enddate,
                    yrcd_yrse_id,
                    yrcd_xcma_id,
                    yrcd_pppn,
                    yrcd_ppps,
                    yrcd_prpn,
                    yrcd_prps,
                    yrcd_yprm_id,
                    yrcd_base_type,
                    yrcd_base_rounding,
                    yrcd_base_value,
                    yrcd_yras_id,
                    yrcd_base_type_we,
                    yrcd_base_rounding_we,
                    yrcd_base_value_we
                ) VALUES (
                    v_rate_details.yrcd_id,
                    v_rate_id,
                    p_date,
                    p_date,
                    0, -- yrcd_yrse_id
                    0, -- yrcd_xcma_id
                    0, -- yrcd_pppn
                    0, -- yrcd_ppps
                    0, -- yrcd_prpn
                    0, -- yrcd_prps
                    0, -- yrcd_yprm_id
                    0, -- yrcd_base_type
                    0, -- yrcd_base_rounding
                    0, -- yrcd_base_value
                    0, -- yrcd_yras_id
                    0, -- yrcd_base_type_we
                    0, -- yrcd_base_rounding_we
                    0  -- yrcd_base_value_we
                );

            WHEN OTHERS THEN
                log_error(
                    SQLCODE,
                    'Error handling YRCD: ' || SQLERRM,
                    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                );
                RAISE;
        END;

        -- Ensure YRCT exists
        BEGIN
            SELECT yrct_id
            INTO v_room_type.yrct_id
            FROM yrct 
            WHERE yrct_yrcd_id = v_rate_details.yrcd_id
                AND yrct_ycat_id = v_category_id;
        EXCEPTION 
            WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
                -- Common handling: delete existing YRCT records and insert a new one
                DELETE FROM yrct
                WHERE yrct_yrcd_id = v_rate_details.yrcd_id
                  AND yrct_ycat_id = v_category_id;
                select seq_yrct.NEXTVAL into v_room_type.yrct_id from dual;
                INSERT INTO yrct (
                    yrct_id,
                    yrct_yrcd_id,
                    yrct_ycat_id
                ) VALUES (
                    seq_yrct.NEXTVAL,
                    v_rate_details.yrcd_id,
                    v_category_id
                );
            WHEN OTHERS THEN
                log_error(
                    SQLCODE,
                    'Error handling YRCT: ' || SQLERRM,
                    DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
                );
                RAISE;
        END;

        -- Set adult count and default flag
        v_adult_count := NVL(p_adult_no, 1);
        v_is_default := CASE WHEN p_adult_no = 1 THEN 1 ELSE 0 END;

        -- Handle YRCV for the current default setting
        BEGIN
            handle_yrcv(
                p_yrcd_id    => v_rate_details.yrcd_id,
                p_noofadults => v_adult_count,
                p_is_default => v_is_default,
                p_price      => p_price
            );
        END;

        -- If setting default, handle YRCV entries where default = 0
        IF v_is_default = 1 THEN
            BEGIN
                handle_yrcv(
                    p_yrcd_id    => v_rate_details.yrcd_id,
                    p_noofadults => v_adult_count,
                    p_is_default => 0,
                    p_price      => p_price
                );
            END;
        END IF;         
    END IF;         
    
    -- Commit all changes if no exceptions were raised
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        -- Log the main procedure error
        log_error(
            SQLCODE,
            'Main procedure error: ' || SQLERRM,
            DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        );
        ROLLBACK; -- Rollback all changes due to error
        RAISE;
END;
/




create or replace function m_hlpricejson(pindate varchar2)
return clob
is
    cursor gd(thedate wdat.wdat_date%type) is 
        select

         '{ "date": "' || to_char(wdat_date, 'yyyy-mm-dd') || '", "category": "' || rt
        || '", "ratecode": "' || rc || '", "guests": ' || adno || ', "price": ' || price || ' }, ' val
          
        from    
            (select
                wdat_date
                , rt.mcnv_crs_code rt
                , rc.mcnv_crs_code rc
                , adno.val adno
                , m_yrch_price_ant_v1(YRCHID=>m_crstofid(rc.mcnv_crs_code), YCATID=>m_crstofid(rt.mcnv_crs_code),
                        WDATDATE=>wdat_date, ADULTS=>adno.val, CALCDAYTYPE=>1) price
            from
                (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RC' and nvl(mcnv.mcnv_inactive, 0) = 0) rc
                , (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0) rt
                , wdat
                , (select wnum.wnum_value val from wnum where wnum_value in (1, 2)) adno)
        where
            wdat_date = thedate
            and nvl(price, 0) > 0;
res clob;
onerow gd%rowtype;
indate date;
begin
    select  to_date(pindate, 'dd.mm.yyyy') into indate from dual;
    open gd(indate);
    loop fetch gd into onerow;
    exit when gd%notfound;
    
        res := res || onerow.val;
    
    end loop;
    close gd;
    res := '[ ' || rtrim(res, ', ') || ' ]';
return(res);
end;


create or replace function m_hlpricejsonft(fromdate date, untildate date)
return clob
is
    cursor gft(fd date, ud date) is
        select wdat_date from wdat where wdat_date >= fd and wdat_date <= ud;
        
    cursor gd(thedate wdat.wdat_date%type) is 
        select

         '{ "date": "' || to_char(wdat_date, 'yyyy-mm-dd') || '", "category": "' || rt
        || '", "ratecode": "' || rc || '", "guests": ' || adno || ', "price": ' || price || ' }, ' val
          
        from    
            (select
                wdat_date
                , rt.mcnv_crs_code rt
                , rc.mcnv_crs_code rc
                , adno.val adno
                , m_yrch_price_ant_v1(YRCHID=>m_crstofid(rc.mcnv_crs_code), YCATID=>m_crstofid(rt.mcnv_crs_code),
                        WDATDATE=>wdat_date, ADULTS=>adno.val, CALCDAYTYPE=>1) price
            from
                (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RC' and nvl(mcnv.mcnv_inactive, 0) = 0) rc
                , (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0) rt
                , wdat
                , (select wnum.wnum_value val from wnum where wnum_value in (1, 2)) adno)
        where
            wdat_date = thedate
            and nvl(price, 0) > 0;
res clob;
onerow gd%rowtype;
indate date;
begin
    open gft(fromdate, untildate);
    loop fetch gft into indate;
    exit when gft%notfound;
    
        open gd(indate);
        loop fetch gd into onerow;
        exit when gd%notfound;
        
            res := res || onerow.val;
        
        end loop;
        close gd;
    
    end loop;
    close gft;
    
    res := '[ ' || rtrim(res, ', ') || ' ]';
return(res);
end;


CREATE OR REPLACE 
FUNCTION m_crstofid(id varchar2)
return number
as
r number;
begin

select mcnv.mcnv_pms_code into r from mcnv where mcnv.mcnv_zcnv_id =
(select zota.zota_zcnv_id from zota where lower(zota.zota_requestorurl) like '%travelline%' and rownum = 1
)
--and mcnv.mcnv_type in ('RC', 'RT')
and mcnv.mcnv_crs_code = id and rownum = 1;

return r;
end;
/


CREATE OR REPLACE 
FUNCTION m_fidtocrs(id number)
return varchar2
as
r varchar2(100);
begin
select mcnv.mcnv_crs_code into r from mcnv where mcnv.mcnv_zcnv_id = m_getzcnvid
and mcnv.mcnv_pms_code = id and rownum = 1;
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE 
function m_getguestname(rid yres.yres_id%type) return VARCHAR2 as
res varchar2(400);
begin
    select initcap(trim(xcms_name1)) || ' ' || initcap(trim(xcms_name3)) into res
        from xcms, yres where xcms_id = yres_xcms_id and yres_id = rid;
    return res;
end;
/


CREATE OR REPLACE 
function m_getname(cid xcms.xcms_id%type)
    return varchar2
    as
    r varchar2(400);
    
    begin
        select trim(trim(xcms_name1 || ' ' || xcms_name2) || ' ' || xcms_name3) into r from xcms where xcms_id = cid;
        return r;
    end;
/


CREATE OR REPLACE 
function m_getrate(id ydet.ydet_id%type)
return ydet.ydet_rateamount%type
as
r ydet.ydet_ratemanual%type;
begin
    r := 0;
    select (decode(ydet.ydet_ratemanual,null,ydet.ydet_rateamount,ydet.ydet_ratemanual)+nvl(ydet.ydet_ratediff,0))
                     *((100-nvl(ydet.ydet_ratediscountperc,0))/100) into r
           from ydet
    where ydet_id = id;
    return r;
    exception when others then return null;
end;

-- Start of DDL Script for View V8LIVE.M_YDET_NOOFROOMS
-- Generated 01.10.2020 20:46:36 from V8LIVE@V8
/


CREATE OR REPLACE 
function m_getrateavxmlbydate(bdate varchar2, edate varchar2) return clob as

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';

cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date 
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;
open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    c := 0;
    rm := '<AvailStatusMessage>';
    rm := rm || '<StatusApplicationControl';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '" Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '"/>';
 
    rm := rm || '<RestrictionStatus Status="';
    select m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, 1, 0) into ratev from dual;
    if ratev = 0 then rm := rm || 'Closed';
        else rm := rm || 'Open';
    end if;
    
    rm := rm || '"/></AvailStatusMessage>';


    end loop;
    close gdat;
    
    q := q || rm;
    rm := '';

    end loop;
    close gt;
    end loop;
    close gd;
    

q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';


return q;


end;
/


CREATE OR REPLACE 
function m_getrateupxml(months number) return clob as
--declare
--months number;
cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date 
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin
--months := 1;
q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';

dbms_output.put_line('Opening details');

open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;

    dbms_output.put_line(to_char(rd.yrcd_id));
    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';        
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/>'; 
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';  
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';     

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;
/


CREATE OR REPLACE 
FUNCTION m_yrch_price_dm(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ca yrcx.yrcx_id%type)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;
    select nvl(max(Rate_price),0) into RatePrice
        from
        (select wdat_date, yrcd_id, yrcd_yrch_id, yrct_ycat_id,
            nvl(
                (select
                    decode(m_is_weekend(YRCHID_FINAL, wdatdate), 1, yrcx_weekend_value, 0, yrcx.yrcx_value, 0)
                   from yrch, yrcd, yrcx, (select * from yrcv where yrcv.yrcv_default = 1) yrcv
                where yrcd_id=yrcv_yrcd_id and yrch_id=yrcd_yrch_id and yrcd.yrcd_id=y.yrcd_id and rownum=1
                                                and yrcv_id = yrcx.yrcx_yrcv_id and yrcx.yrcx_xcac_id = ca),0) as Rate_price
            from wdat,
                (select nvl(yrse_startdate,yrcd_startdate) st, nvl(yrse_enddate,yrcd_enddate) en, yrcd.* From yrcd, yrse
                    where yrcd_yrse_id=yrse_id(+)) y, yrct
                where wdat_date between st and en and yrcd_id=yrct_yrcd_id
                    and yrct_ycat_id=YCATID and wdat_date = WDATDATE and yrcd_yrch_id=YRCHID_FINAL);
return round(RATEPRICE,0);
END;
/


CREATE OR REPLACE 
function m_getrateupxmlbydate(bdate varchar2, edate varchar2) return clob as
--declare

cursor gd is select yrch.*, mcnv.* from yrch, mcnv where yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC';
        --and mcnv.mcnv_crs_code = '286536'
        --and yrcd_id = 2662
        --and yrcd_yrch_id = 1562
        --and yrcd.yrcd_enddate >= v8_sys_fideliodate
        --and yrcd.yrcd_startdate <= to_date(edate, 'dd.mm.yyyy')
        --and yrcd.yrcd_enddate >= to_date(bdate, 'dd.mm.yyyy');

--cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
--        order by yrcv_default, yrcv_noofadults;
--cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
--    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
--    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt is select * from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date 
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');
cursor gn is select wnum.wnum_value from wnum where wnum_value >= 1 and wnum_value <= 6;
cursor gc is select * from xcac, mcnv where xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' 
    and nvl(mcnv.mcnv_inactive, 0) = 0 and mcnv.mcnv_zcnv_id = m_getzcnvid;


dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
rma clob;
rms clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
--rx gx%rowtype;
rn number;
rc gc%rowtype;
rh yrch%rowtype;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><RateAmountMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gd;
loop fetch gd into rd;
exit when gd%notfound;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

select * into rh from yrch where yrch_id = rd.mcnv_pms_code;

    c := 0;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat;
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gn;
        loop fetch gn into rn;
        exit when gn%notfound;
                rma := '';        

                rma := rma || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                --rm := rm || chr(10) || '<BaseByGuestAmt AmountAfterTax="';
                select nvl(m_yrch_price_ant_v1(rd.yrch_id, rt.ycat_id, dat, rn, 1), 0) into ratev from dual;
                c := c + ratev;
                rma := rma || to_char(ratev);
                rma := rma || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rma := rma || to_char(rn);
                rma := rma || '"/>';
                if (ratev > 0) then rm := rm || rma; end if;
                rma := '';

        end loop;
        close gn;
        
        rm := rm || '</BaseByGuestAmts>';
        rms := '<AdditionalGuestAmounts>';
        rma := '';
        cd := 0;
        open gc;
        loop fetch gc into rc;
        exit when gc%notfound;
            rma := rma || '<AdditionalGuestAmount AgeQualifyingCode="';
            rma := rma || rc.mcnv_crs_code;
            rma := rma || '" Amount="';
            select nvl(m_yrch_price_dm(rd.yrch_id, rt.ycat_id, dat, rc.xcac_id), 0) into ratev from dual;
            rma := rma || to_char(ratev);
            cd := cd + ratev;
            rma := rma || '" CurrencyCode="RUB" TaxInclusive="true"/>';           
            
            if (ratev > 0) then rms := rms || rma; end if;
            rma := '';           
 
        end loop;
        close gc;    
    rms := rms || '</AdditionalGuestAmounts>';
    
    if cd > 0 then rm := rm || rms; end if;
    
    rm := rm || '</Rate>';  
    end loop;
    close gdat;
    
    rm := rm || '</Rates></RateAmountMessage>';     
    if c > 0 then q := q || rm; c := 0; end if;
    rm := '';


    end loop;
    close gt;
    end loop;
    close gd;
    

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('https://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);

return q;


end;
/


CREATE OR REPLACE 
function m_getrateupxmldt(months number)
return clob
as
--declare

cursor gd is select yrcd.*, mcnv.* from yrcd, mcnv where yrcd_yrch_id = mcnv.mcnv_pms_code
        and mcnv.mcnv_zcnv_id = m_getzcnvid and nvl(mcnv.mcnv_inactive, 0) = 0
        and mcnv.mcnv_type = 'RC'
        --and mcnv.mcnv_crs_code = '153974'
        --and yrcd_id = 2662
        and yrcd_yrch_id = 1562
        and yrcd.yrcd_enddate >= v8_sys_fideliodate;

cursor gv(di yrcd.yrcd_id%type) is select * from yrcv where yrcv_yrcd_id = di
        order by yrcv_default, yrcv_noofadults;
cursor gx(vi yrcv.yrcv_id%type) is select yrcx.*, mcnv.* from yrcx, mcnv where yrcx.yrcx_yrcv_id = vi
    and yrcx.yrcx_xcac_id = mcnv.mcnv_pms_code and mcnv.mcnv_type = 'CA' and nvl(mcnv.mcnv_inactive, 0) = 0
    and mcnv.mcnv_zcnv_id = m_getzcnvid;
cursor gt(di yrcd.yrcd_id%type) is select * from yrct, mcnv where yrct.yrct_yrcd_id = di
    and mcnv_pms_code = yrct.yrct_ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat(mn wdat.wdat_date%type, mx wdat.wdat_date%type) is select wdat_date 
    from wdat where wdat_date >= v8_sys_fideliodate
    and wdat_date <= add_months(v8_sys_fideliodate, months)
    and wdat_date >= mn and wdat_date <= mx;

dat wdat.wdat_date%type;
zo zota%rowtype;

q clob;
rm clob;
url varchar2(1000);
rd gd%rowtype;
rv yrcv%rowtype;
rt gt%rowtype;
rx gx%rowtype;
rh yrch%rowtype;
begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelRateAmountNotifRQ TimeStamp="';
--q := q || '';
q := q || to_char(systimestamp,'yyyy-mm-dd"T"hh24:mi:ss.ff3tzh:tzm');
q := q || '" Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="';
select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '"/></Source></POS><RateAmountMessages HotelCode="';
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';


open gd;
loop fetch gd into rd;
exit when gd%notfound;
select * into rh from yrch where yrch_id = rd.yrcd_yrch_id;


    open gt(rd.yrcd_id);
    loop fetch gt into rt;
    exit when gt%notfound;
    rm := '<RateAmountMessage>';
    rm := rm || '<StatusApplicationControl IsRoom="true"';
    rm := rm || ' RatePlanCode="';
    rm := rm || rd.mcnv_crs_code;
    rm := rm || '" InvTypeCode="';
    rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
    rm := rm || '"/><Rates>';
    
    open gdat(rd.yrcd_startdate, rd.yrcd_enddate);
    loop fetch gdat into dat;
    exit when gdat%notfound;
    rm := rm || '<Rate';
    rm := rm || ' Start="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" End="';
    rm := rm || to_char(dat, 'yyyy-mm-dd');
    rm := rm || '" ';
    rm := rm || '>';
    rm := rm || '<BaseByGuestAmts>';
        open gv(rd.yrcd_id);
        loop fetch gv into rv;
        exit when gv%notfound;
    --rm := rm || '';        
            if rv.yrcv_default = 0 and rv.yrcv_noofadults > 0 then
                rm := rm || chr(10) || '<BaseByGuestAmt AgeQualifyingCode="10" AmountAfterTax="';
                rm := rm || to_char(m_yrch_price_ant_v1(rd.yrcd_yrch_id, rt.yrct_ycat_id, dat, rv.yrcv_noofadults, 1));
                rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                rm := rm || to_char(rv.yrcv_noofadults);
                rm := rm || '"/>';
            end if;
            if rv.yrcv_default = 1 then
                open gx(rv.yrcv_id);
                loop fetch gx into rx;
                exit when gx%notfound;
                    rm := rm || '<BaseByGuestAmt AgeQualifyingCode="';
                    rm := rm || rx.mcnv_crs_code;
                    rm := rm || '" AmountAfterTax="';
                    rm := rm || to_char(rx.yrcx_value);
                    rm := rm || '" CurrencyCode="RUB" NumberOfGuests="'; 
                    rm := rm || to_char(rv.yrcv_noofadults);
                    rm := rm || '"/>'; 
                end loop;
                close gx;
            end if;
        end loop;
        close gv;
    rm := rm || '</BaseByGuestAmts></Rate>';  
    end loop;
    close gdat;
    rm := rm || '</Rates></RateAmountMessage>';     

--select m_fidtocrs(1483) from dual

--select * from yrch
    end loop;
    close gt;

q := q || rm;
rm := '';
end loop;
close gd;

q := q || '</RateAmountMessages></OTA_HotelRateAmountNotifRQ>';

--print_clob_to_output(q);

--select utl_http.REQUEST('http://www.travelline.ru/Api/TLConnect/FS8'
--|| utl_url.escape(URL=>q, URL_CHARSET=>'UTF8')) into rm from dual;

--dbms_output.put_line(rm);
return q;
end;




--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
--select * from yrcv
--select * from mcnv where mcnv_zcnv_id = m_getzcnvid
/


CREATE OR REPLACE 
FUNCTION m_getzcnvid
return number
as
r number;
begin
select zota.zota_zcnv_id into r from zota where zota_name like 'TravelLine';
return r;
exception WHEN no_data_found then return null;
end;
/


CREATE OR REPLACE 
FUNCTION m_getroomavxmlbydate(bdate varchar2, edate varchar2) return clob as


cursor gt is select ycat.*, mcnv.* from ycat, mcnv where mcnv_pms_code = ycat_id and mcnv.mcnv_zcnv_id = m_getzcnvid
    and mcnv.mcnv_type = 'RT';-- and nvl(mcnv.mcnv_inactive, 0) = 0;
cursor gdat is select wdat_date
    from wdat where wdat_date >= to_date(bdate, 'dd.mm.yyyy') and wdat_date <= to_date(edate, 'dd.mm.yyyy');

dat wdat.wdat_date%type;

q clob;
rm clob;
ratev yrcv.yrcv_value%type;
c number;
cd number;
url varchar2(1000);
rv yrcv%rowtype;
rt gt%rowtype;
rn number;
lastcvid number;

begin

q := '<?xml version="1.0" encoding="UTF-8"?><OTA_HotelAvailNotifRQ Version="1.002" xmlns="http://www.opentravel.org/OTA/2003/05"><POS><Source AgentDutyCode="TL" AgentSine="TL">';
q := q || '<RequestorID ID="';
select q || zota.zota_requestorid into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" Type="';
select q || zota.zota_requestortype into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '" URL="https://www.travelline.ru/Api/TLConnect/FS8"/></Source></POS><AvailStatusMessages HotelCode="';
--select q || zota.zota_url into q from zota where zota.zota_zcnv_id = m_getzcnvid;
select q || zota.zota_hotelcode into q from zota where zota.zota_zcnv_id = m_getzcnvid;
q := q || '">';
select zota.zota_url into url from zota where zota.zota_zcnv_id = m_getzcnvid;

open gt;
loop fetch gt into rt;
exit when gt%notfound;

open gdat;
loop fetch gdat into dat;
exit when gdat%notfound;

    begin
        select avl into c from m_avl where wdat_date = dat and ycat_id = rt.ycat_id;
        rm := '<AvailStatusMessage BookingLimitMessageType="2" BookingLimit="' || to_char(c) || '">';
        rm := rm || '<StatusApplicationControl';
        
        rm := rm || ' InvTypeCode="';
        rm := rm || rt.mcnv_crs_code;--m_fidtocrs(rt.yrct_ycat_id);
        rm := rm || '" Start="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '" End="';
        rm := rm || to_char(dat, 'yyyy-mm-dd');
        rm := rm || '"/>';

        rm := rm || '</AvailStatusMessage>';
    exception when no_data_found then null;
    end;

    end loop;
    close gdat;

    q := q || rm;
    rm := '';

    end loop;
  
    close gt;

q := q || '</AvailStatusMessages></OTA_HotelAvailNotifRQ>';

return q;

end;
/


CREATE OR REPLACE 
FUNCTION m_getshortguestname(resid yres.yres_id%type) return varchar2
as
r varchar2(500);

begin

select
    initcap(xcms_name1) || chr(32) ||
    regexp_replace(initcap(xcms_name3),'(([a-z]|[а-я]| )+)', '.') into r
from xcms, yres where xcms_id = yres_xcms_id and yres_id = resid;

return r;
end;
/


CREATE OR REPLACE 
function m_hlpricejson(pindate varchar2)
return clob
is
    cursor gd(thedate wdat.wdat_date%type) is 
        select

         '{ "date": "' || to_char(wdat_date, 'yyyy-mm-dd') || '", "category": "' || rt
        || '", "ratecode": "' || rc || '", "guests": ' || adno || ', "price": ' || price || ' }, ' val
          
        from    
            (select
                wdat_date
                , rt.mcnv_crs_code rt
                , rc.mcnv_crs_code rc
                , adno.val adno
                , m_yrch_price_ant_v1(YRCHID=>m_crstofid(rc.mcnv_crs_code), YCATID=>m_crstofid(rt.mcnv_crs_code),
                        WDATDATE=>wdat_date, ADULTS=>adno.val, CALCDAYTYPE=>1) price
            from
                (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RC' and nvl(mcnv.mcnv_inactive, 0) = 0) rc
                , (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0) rt
                , wdat
                , (select wnum.wnum_value val from wnum where wnum_value in (1, 2)) adno)
        where
            wdat_date = thedate
            and nvl(price, 0) > 0;
res clob;
onerow gd%rowtype;
indate date;
begin
    select  to_date(pindate, 'dd.mm.yyyy') into indate from dual;
    open gd(indate);
    loop fetch gd into onerow;
    exit when gd%notfound;
    
        res := res || onerow.val;
    
    end loop;
    close gd;
    res := '[ ' || rtrim(res, ', ') || ' ]';
return(res);
end;
/


CREATE OR REPLACE 
FUNCTION m_yrch_price_ant_v1(YRCHID in NUMBER, YCATID in NUMBER, WDATDATE in DATE, ADULTS in number, CALCDAYTYPE in NUMBER)
return number is PRICE NUMBER;
    RatePrice number;
    YRCHID_FINAL number;
    DayTypePrice number;
    BASERATE_AMOUNT number;
    BASERATE_PERC number;
    pap number;
begin
    PRICE:=0;
    RatePrice:=0;
    DayTypePrice:=0;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,0,YRCH_BASE_VALUE,0)) into BASERATE_PERC from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,0,decode(YRCH_BASE_TYPE,1,YRCH_BASE_VALUE,0)) into BASERATE_AMOUNT from yrch where yrch_id=YRCHID;
    select decode(nvl(yrch_baserate,0),0,yrch_id,yrch_base_yrch_id) into YRCHID_FINAL from yrch where yrch_id=YRCHID;

select
        nvl(decode(m_is_weekend(YRCHID_FINAL, WDATDATE), 1, 
            yrcv_weekend_value, 0, yrcv_value, 0), 0) into RatePrice
from yrct, yrch, yrcd, yrcv
where 
    yrcd_id=yrcv_yrcd_id 
    and yrch_id=yrcd_yrch_id 
    and yrcv.yrcv_default = 0
    and yrcv_noofadults=ADULTS
    and yrcd_id=yrct_yrcd_id
    and yrch.yrch_id = YRCHID_FINAL
    and yrcd.yrcd_enddate >= WDATDATE
    and yrcd.yrcd_startdate <= WDATDATE
    and yrct_ycat_id=YCATID;       
        
        
        
   pap := 0;
   begin                 
select sign(rateprice) * nvl(sum(decode(ypap.ypap_type, 1, ypap_value * ADULTS, 2, ypap_value, 0)), 0) into pap
from ypac, ypat, ypap, yrch, yrcp
where yrch_id = yrcp.yrcp_yrch_id
and yrcp.yrcp_ypac_id = ypac.ypac_id
and ypac.ypac_id = ypat.ypat_ypac_id
and ypat.ypat_from <= WDATDATE
and ypat.ypat_until >= WDATDATE
and ypap.ypap_ypat_id = ypat.ypat_id
and yrch_id = YRCHID
and ypap_xcac_id = 0
and ypac_included = 0;                 
         exception when no_data_found then null;

end;
         
                    
SELECT NVL (MAX (yrdv_value), 0)
  INTO daytypeprice
  FROM (SELECT yrdv_value
          FROM yrdc,
               yrdt,
               yrdd,
               yrdy,
               yrde,
               yrdv,
               yrch
         WHERE     yrdc_yrdt_id = yrdt_id
               AND yrdt_id = yrdd_yrdt_id
               AND yrde_id = yrdy_yrde_id
               AND yrdd_id = yrde_yrdd_id
               AND yrdy_id = yrdv_yrdy_id
               AND yrdd_yrds_id = yrch_yrds_id
               AND yrdv_adultcount = 1
               AND wdatdate BETWEEN NVL (yrde_startdate, wdatdate)
                                AND NVL (yrde_enddate, wdatdate)
               AND yrdy_ycat_id = ycatid
               AND yrch_id = yrchid_final
               AND yrdc_date = wdatdate);
               
    if CALCDAYTYPE=1 then
        PRICE:=pap + RatePrice+DayTypePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*(RatePrice+DayTypePrice));
        else
        PRICE:=pap + RatePrice-BASERATE_AMOUNT-(BASERATE_PERC/100*RatePrice);
    end if;
return round(PRICE,0);
END;
/


CREATE OR REPLACE 
function m_hlpricejsonft(fromdate date, untildate date)
return clob
is
    cursor gft(fd date, ud date) is
        select wdat_date from wdat where wdat_date >= fd and wdat_date <= ud;
        
    cursor gd(thedate wdat.wdat_date%type) is 
        select

         '{ "date": "' || to_char(wdat_date, 'yyyy-mm-dd') || '", "category": "' || rt
        || '", "ratecode": "' || rc || '", "guests": ' || adno || ', "price": ' || price || ' }, ' val
          
        from    
            (select
                wdat_date
                , rt.mcnv_crs_code rt
                , rc.mcnv_crs_code rc
                , adno.val adno
                , m_yrch_price_ant_v1(YRCHID=>m_crstofid(rc.mcnv_crs_code), YCATID=>m_crstofid(rt.mcnv_crs_code),
                        WDATDATE=>wdat_date, ADULTS=>adno.val, CALCDAYTYPE=>1) price
            from
                (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RC' and nvl(mcnv.mcnv_inactive, 0) = 0) rc
                , (select mcnv.mcnv_crs_code, mcnv.mcnv_pms_code from mcnv where mcnv.mcnv_type = 'RT' and nvl(mcnv.mcnv_inactive, 0) = 0) rt
                , wdat
                , (select wnum.wnum_value val from wnum where wnum_value in (1, 2)) adno)
        where
            wdat_date = thedate
            and nvl(price, 0) > 0;
res clob;
onerow gd%rowtype;
indate date;
begin
    open gft(fromdate, untildate);
    loop fetch gft into indate;
    exit when gft%notfound;
    
        open gd(indate);
        loop fetch gd into onerow;
        exit when gd%notfound;
        
            res := res || onerow.val;
        
        end loop;
        close gd;
    
    end loop;
    close gft;
    
    res := '[ ' || rtrim(res, ', ') || ' ]';
return(res);
end;
/


CREATE OR REPLACE 
function m_is_weekend(rid yrch.yrch_id%type, dat wdat.wdat_date%type) return number
as
r yrch%rowtype;
w number;
begin
w := 0;

select case when to_char(dat,'D')=1 and yrch_weekend_sun=1 then 1
                        when to_char(dat,'D')=2 and yrch_weekend_mon=1 then 1
                        when to_char(dat,'D')=3 and yrch_weekend_tue=1 then 1
                        when to_char(dat,'D')=4 and yrch_weekend_wed=1 then 1
                        when to_char(dat,'D')=5 and yrch_weekend_thu=1 then 1
                        when to_char(dat,'D')=6 and yrch_weekend_fri=1 then 1
                        when to_char(dat,'D')=7 and yrch_weekend_sat=1 then 1
                        else 0 end into w from yrch where yrch_id = rid;
                    
return w;                    
                        
end;

-- Start of DDL Script for Function V8LIVE.M_YRCH_PRICE_ANT_V1
-- Generated 27-aae-2022 16:36:53 from V8LIVE@V8
/


