Querys SQL más comunes para la obtención de producción en Opera PMS

–Produccion por evento

SELECT a.room, a.resv_name_id, a.passerby_name, a.trx_code,a.trx_date, a.guest_account_debit

FROM financial_transactions_view a

WHERE to_char(a.trx_date,‘RRRR’) = 2019 and

         a.guest_account_debit <> 0.00 and

      a.trx_code IN (2580,2503)

 

–Habitaciones bloqueadas

SELECT distinct a.room,a.description, b.begin_date, b.end_date,

   b.repair_remarks,b.reason_code,a.room_status,b.completed_date

FROM room a,room_repairs b

WHERE substr(a.room_status,1,1)=‘O’

      and a.room=b.room

      and b.end_date>=TO_CHAR(pms_p.business_date)

      and b.begin_date<=TO_CHAR(pms_p.business_date)

      and b.completed_date is NULL

ORDER BY room,begin_date

 

–Habitaciones reservadas

SELECT distinct a.share_id, a.room, a.room_category_label, a.company_name, a.sguest_name, a.guest_country_desc,

       a.trunc_arrival, a.trunc_departure, a.currency_code, a.share_amount,

       a.market_desc, a.confirmation_no, a.market_code,a.effective_rate_amount,a.fixed_charge

FROM reservation_general_view a

WHERE substr(a.ARRIVAL,1,11)=TO_CHAR(SYSDATE)

       and (A.RESV_STATUS=‘RESERVED’OR A.RESV_STATUS=‘PROSPECT’)

       and a.share_amount<>0.00

 

–Ingresos por empresa y mercado

SELECT distinct a.share_id, a.room, a.room_category_label, a.company_name, a.sguest_name, a.guest_country_desc,

       a.trunc_arrival, a.trunc_departure, a.currency_code, a.share_amount,

       a.market_desc, a.confirmation_no, a.market_code,a.effective_rate_amount,a.fixed_charge

  FROM reservation_general_view_hld a

  WHERE a.resv_status=‘CHECKED IN’

        and a.share_amount>0.00

UNION ALL

SELECT distinct a.share_id, a.room, a.room_category_label, a.company_name, a.sguest_name, a.guest_country_desc,

       a.trunc_arrival, a.trunc_departure, a.currency_code, a.share_amount,

       a.market_desc, a.confirmation_no, a.market_code,a.effective_rate_amount,a.fixed_charge

  FROM reservation_general_view_hld a

  WHERE a.resv_status=‘CHECKED IN’

        and  substr(a.market_code,1,2)=’80’ and a.financially_responsible_yn is null

 

–Forecas de habitaciones por dia

SELECT a.considered_date,sum(a.no_rooms), sum(a.arrival_rooms),sum(a.departure_rooms),sum(a.complimentary_rooms),

       sum(a.net_room_revenue)

FROM rep_res_statistics_forecast a

WHERE (a.CONSIDERED_DATE BETWEEN TO_CHAR(SYSDATE TO_CHAR(sysdate1,‘DD’)) and TO_CHAR(SYSDATE+366) )  and a.ROOM_CATEGORY<>‘-1’

  AND ( (a.deduct_yn=‘N’ AND  a.market_code= ’50A’) or a.deduct_yn=‘Y’)

GROUP BY a.CONSIDERED_DATE

 

–Forecast por Marked Code Mensual

SELECT

    CONSIDERED_MONTH,

    CONSIDERED_YEAR,

    MARKET_GROUP,

    MARKET_GROUP_DESC,

    SUM(no_rooms) NO_ROOMS,

    SUM(room_revenue) ROOM_REVENUE,

    market_code

FROM(

        SELECT

            CONSIDERED_MONTH,

            CONSIDERED_YEAR,

            CONSIDERED_DATE,

            MARKET_GROUP,

            MARKET_GROUP_DESC,

            MARKET_CODE,

            SUM(no_rooms) NO_ROOMS,

            SUM(room_revenue) ROOM_REVENUE

        FROM(

           SELECT

                to_char(rsf.considered_date,‘MONTH’) CONSIDERED_MONTH,

                to_char(rsf.considered_date,‘RRRR’) CONSIDERED_YEAR,

                mc.parent_market_code MARKET_GROUP,

                mg.description MARKET_GROUP_DESC,

                rsf.market_code MARKET_CODE,

                rsf.considered_date CONSIDERED_DATE,

                decode(rsf.room_category,-1,0,rsf.no_rooms) NO_ROOMS,

                rsf.net_room_revenue ROOM_REVENUE

            FROM

                rep_res_statistics_forecast rsf, markets mc, market_groups mg

            WHERE

                (rsf.CONSIDERED_DATE BETWEEN TO_CHAR(SYSDATE TO_CHAR(sysdate1,‘DD’)) and TO_CHAR(SYSDATE+135) )

                AND (mc.parent_market_code = mg.market_group AND rsf.market_code(+) = mc.market_code )

                AND rsf.deduct_yn=‘Y’

        )

        GROUP BY

            CONSIDERED_MONTH,

            CONSIDERED_YEAR,

            CONSIDERED_DATE,

            MARKET_GROUP,

            MARKET_GROUP_DESC,

            MARKET_CODE

        ORDER BY

            CONSIDERED_MONTH,

            CONSIDERED_YEAR,

            CONSIDERED_DATE,

            MARKET_GROUP,

            MARKET_GROUP_DESC,

            MARKET_CODE

)

GROUP BY

    CONSIDERED_MONTH, CONSIDERED_YEAR, MARKET_GROUP, MARKET_GROUP_DESC, market_code

ORDER BY

    CONSIDERED_MONTH,

    CONSIDERED_YEAR,

    MARKET_GROUP,

    MARKET_Code

 

–Reservas por dia

SELECT a.business_date_created as Fch_Reserva, a.trunc_arrival as LLegada, a.trunc_departure as Salida,a.nights,

       a.room_category_label as Hab,a.share_amount as tarifa, a.rate_code,a.guarantee_code,

          a.sfirst_guest_name as nombres,a.guest_name as apellido,  a.company_name, a.travel_agent_name,

          b.srep_code as Ejecutivo,a.share_amount

FROM reservation_general_view a , name_view b

WHERE a.room_category_label<>‘PM’  and

     a.resv_status<>‘CANCELLED’ and

     a.share_amount > 0.00 and

     b.name_id (+)= decode(a.company_id,null,a.travel_agent_id,a.company_id) and

     a.business_date_created =TO_CHAR(pms_p.business_date)

ORDER BY a.trunc_arrival    

 

–Produccion por ejecutivo de ventas

    SELECT  a.room_revenue, a.share_amount,b.srep_code,a.rate_code,

        a.sfirst_guest_name || ‘ ‘ || a.guest_name as nombres,

        a.trunc_arrival, a.trunc_departure,a.company_name,a.origin_of_booking,a.guarantee_code

    FROM reservation_general_view a , name_view b

    WHERE a.guarantee_code not in (‘G01’, ‘G02’) and

          a.resv_status<>‘CANCELLED’ and

          a.share_amount > 0.00 and

          b.name_id = decode(a.company_id,null,a.travel_agent_id,a.company_id) and

         (a.trunc_departure >= TO_CHAR(SYSDATE))

UNION ALL

    SELECT a.available_revenue,a.avg_rate, a.owner_code,a.rate_code,

        ‘BLK ‘ || a.description,

        a.begin_date, a.end_date,a.acc_name,a.source,a.guarantee_code

    FROM   sc_busblock_info a     

    WHERE  a.status<>‘C’  and

           a.available_rooms>0 and

           a.avg_rate >0 and a.guarantee_code not in (‘G01’, ‘G02’)

 

–Produccion por habitacion

SELECT ‘F’ HF,to_char(a.trunc_arrival,‘DD’) DD,to_char(a.trunc_arrival,‘MM’) MM,to_char(a.trunc_arrival,‘YYYY’) AA,

       a.nights Noches, a.room_category CRoom , a.share_amount Rate,b.srep_code Owner,

       a.trunc_arrival Arrival, a.trunc_departure Departure,a.company_name Compania

       FROM  reservation_general_view a , name_view b

       WHERE a.resv_status<>‘CANCELLED’ and a.share_amount > 0.00 and

             b.name_id = decode(a.company_id,null,a.travel_agent_id,a.company_id) and

            (a.trunc_departure >= TO_CHAR(SYSDATE) )

UNION ALL

SELECT ‘F’,to_char(a.begin_date,‘DD’) DD,to_char(a.begin_date,‘MM’) MM,to_char(a.begin_date,‘YYYY’) AA,

       round(a.available_revenue/a.avg_rate,0),

       ‘BLOCK ‘ CRoom, a.avg_rate, a.owner_code,a.begin_date, a.end_date,a.acc_name

       FROM  sc_busblock_info a     

       WHERE a.status<>‘C’ and a.available_rooms>0 and a.avg_rate >0

UNION ALL

SELECT ‘H’,to_char(rsd.business_date,‘DD’) DD,to_char(rsd.business_date,‘MM’) MM,to_char(rsd.business_date,‘YYYY’) AA,

       1 noches,rsd.room_category CRoom,rsd.room_revenue ROOM_REVENUE,au.app_user CIA_OWNER,rsd.trunc_begin_date,

       rsd.business_date BUSINESS_DATE,n2.company

       FROM  reservation_stat_daily rsd , name_owner no, application$_user au, name n1, name n2

       WHERE no.name_id = rsd.company_id     and no.primary_yn = ‘Y’

             and au.person_name_id = no.user_id  and rsd.market_code<>’80C’

             and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

             and n1.name_id(+) = rsd.name_id     and n2.name_id(+) = rsd.company_id

             and rsd.room_revenue>0

UNION ALL

SELECT ‘H’,to_char(rsd.business_date,‘DD’) DD,to_char(rsd.business_date,‘MM’) MM,to_char(rsd.business_date,‘YYYY’) AA,

       1 noches,rsd.room_category CRoom,rsd.room_revenue ROOM_REVENUE,au.app_user CIA_OWNER,rsd.trunc_begin_date,

       rsd.business_date BUSINESS_DATE,n2.company

       FROM  reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

       WHERE rsd.company_id is null         and no.name_id = rsd.agent_id

             and no.primary_yn = ‘Y’             and au.person_name_id = no.user_id

             and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’   and rsd.resv_status <> ‘CHECKED OUT’)

             and n1.name_id(+) = rsd.name_id     and n2.name_id(+) = rsd.agent_id

             and rsd.room_revenue<>0

 ORDER BY AA,MM,DD,CRoom

 

–Produccion por ejecutivo de venta

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    initcap(n1.sname||‘,’||n1.sfirst) GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n2.company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    no.name_id = rsd.company_id

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and rsd.market_code<>’80C’

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

    and rsd.room_revenue>0

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    initcap(n1.sname||‘,’||n1.sfirst) GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n2.company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    no.name_id = rsd.company_id

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and rsd.market_code<>’80C’

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

    and rsd.room_revenue<0

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    initcap(n1.sname||‘,’||n1.sfirst) GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n2.company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    rsd.company_id is null

    and no.name_id = rsd.agent_id

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’ and rsd.resv_status <> ‘CHECKED OUT’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.agent_id

    and rsd.room_revenue<>0

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    ‘SUPERVISOR’ CIA_OWNER,

    rsd.rate_code RATE_CODE,

    ‘*** NO DISPONIBLE ***’ GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n1.display_name  company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd,name_view n1

WHERE

    n1.name_id(+) = rsd.name_id

    and rsd.market_main_group=‘900’

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and rsd.company_id is NULL

    and rsd.room_revenue>0

   

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    ‘SUPERVISOR’  CIA_OWNER,

    rsd.rate_code RATE_CODE,

    ‘*** NO DISPONIBLE ***’ GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n1.display_name  company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd,name_view n1

WHERE

    n1.name_id(+) = rsd.name_id

    and rsd.market_main_group=‘900’

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and rsd.company_id is NULL

    and rsd.room_revenue<0

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    initcap(n1.sname||‘,’||n1.sfirst) GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n2.company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    to_char(rsd.business_date,‘RRRR’) = 2018

    and to_char(rsd.business_date,‘MM’) = ’12’

    and no.name_id = rsd.company_id

    and no.primary_yn = ‘Y’

    and rsd.nights>0

    and au.person_name_id = no.user_id

    and substr(rsd.market_code,1,2)=’80’

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

    and rsd.room_revenue=0

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    rsd.room_revenue ROOM_REVENUE,

    alh.rms_owner_code CIA_OWNER,

    rsd.rate_code RATE_CODE,

    initcap(n1.sname||‘,’||n1.sfirst) GUEST_NAME,

    to_char(rsd.trunc_begin_date,‘DD-MM-RR’) ARRIVAL,

    to_char(rsd.trunc_end_date,‘DD-MM-RR’) DEPARTURE,

    n2.company,

    rsd.source_code,

    rsd.market_code,

    rsd.room,

    rsd.room_category

FROM

    reservation_stat_daily rsd, name n1, name n2, allotment$header alh

WHERE

    rsd.company_id is NULL

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and rsd.room_revenue>0

    and n1.name_id = rsd.name_id

    and alh.allotment_header_id = rsd.allotment_header_id

    and n2.name_id =  alh.company_name_id

 

ORDER BY arrival,departure

 

–Produccion por ejecutivo detallado

SELECT

    rsd.business_date BUSINESS_DATE,

    to_char(rsd.business_date,‘MM’) MONTH,

    to_char(rsd.business_date,‘RRRR’) YEAR,

    rsd.stay_rooms STAY_ROOMS,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname) CIA_NAME,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    rsd.room_revenue ROOM_REVENUE,

    n2.industry_code,

    rsd.source_code

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    rsd.business_date>=TO_CHAR(pms_p.business_date30)

    and no.name_id = rsd.company_id

    and rsd.room_revenue<>0

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    to_char(rsd.business_date,‘MM’) MONTH,

    to_char(rsd.business_date,‘RRRR’) YEAR,

    rsd.stay_rooms STAY_ROOMS,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname) CIA_NAME,

    au.app_user CIA_OWNER,

    rsd.rate_code RATE_CODE,

    rsd.room_revenue ROOM_REVENUE,

    n2.industry_code,

    rsd.source_code

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    rsd.business_date>=TO_CHAR(pms_p.business_date30)

    and  rsd.company_id is null

    and no.name_id = rsd.agent_id

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’ and rsd.resv_status <> ‘CHECKED OUT’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.agent_id

    and rsd.room_revenue<>0

 

–Produccion por ejecutivo agrupado

SELECT

  au.app_user ,sum(rsd.room_revenue)

FROM

  reservation_stat_daily rsd, name_owner no, application$_user au

WHERE

  no.name_id = rsd.company_id

  and no.primary_yn = ‘Y’

  and au.person_name_id = no.user_id

  and rsd.market_code<>’80C’

  and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

  and rsd.room_revenue<>0

  group by au.app_user

 

UNION ALL

 

SELECT

  au.app_user,sum(rsd.room_revenue)

  FROM

  reservation_stat_daily rsd, name_owner no, application$_user au

  WHERE

  rsd.company_id is null

  and no.name_id = rsd.agent_id

  and no.primary_yn = ‘Y’

  and au.person_name_id = no.user_id

  and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’ and rsd.resv_status <> ‘CHECKED OUT’)

  and rsd.room_revenue<>0

  group by au.app_user

 

–Forecast agrupado por fecha

  SELECT a.considered_date,sum(a.no_rooms), sum(a.arrival_rooms),sum(a.departure_rooms),sum(a.complimentary_rooms),

       sum(a.net_room_revenue)

  FROM rep_res_statistics_forecast a

  WHERE ( EXTRACT(YEAR FROM a.CONSIDERED_DATE) = 2019 )  and a.ROOM_CATEGORY IS NOT NULL

  GROUP BY a.CONSIDERED_DATE

 

UNION ALL

 

  SELECT a.considered_date,sum(a.no_rooms), sum(a.arrival_rooms),sum(a.departure_rooms),sum(a.complimentary_rooms),

       sum(a.net_room_revenue)

  FROM rep_res_statistics_forecast a

  WHERE ( EXTRACT(YEAR FROM a.CONSIDERED_DATE) = 2019 )  and a.ROOM_CATEGORY  IS NOT NULL

           and (a.group_yn=‘N’ or (a.group_yn=‘Y’ and a.deduct_yn=‘Y’))

  GROUP BY a.CONSIDERED_DATE

 

–Produccion por empresa y por tipo de habitacion

SELECT to_char(rsd.business_date,‘RRRR’),to_char(rsd.business_date,‘MM’),rsd.company_id,rsd.room,rsd.room_category,sum(rsd.room_revenue),sum(rsd.room_adults)

FROM

  reservation_stat_daily rsd

WHERE

  to_char(rsd.business_date,‘RRRR’) >= 2010

  AND Rsd.market_code<>’80C’

  and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

  and rsd.room_revenue<>0

  group by to_char(rsd.business_date,‘RRRR’),to_char(rsd.business_date,‘MM’),rsd.company_id,rsd.room,rsd.room_category

  order by to_char(rsd.business_date,‘RRRR’),to_char(rsd.business_date,‘MM’),rsd.company_id,rsd.room

 

–Produccion mensual por market y por ejecutivo

SELECT

    rsd.business_date BUSINESS_DATE,

    to_char(rsd.business_date,‘MM’) MONTH,

    to_char(rsd.business_date,‘RRRR’) YEAR,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname) CIA_NAME,

    rsd.market_main_group MARKET_GRP,

    rsd.market_code,

    n2.industry_code,

    au.app_user OWNER,

    rsd.rate_code RATE_CODE,

    sum(rsd.room_revenue) ROOM_REVENUE,sum(rsd.stay_rooms) ROOMS

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    rsd.business_date=TO_CHAR(pms_p.business_date2)

    and no.name_id = rsd.company_id

    and rsd.room_revenue<>0

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

GROUP BY

    rsd.business_date,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname),

    rsd.market_main_group,

    rsd.market_code,

    n2.industry_code,

    au.app_user,

    rsd.rate_code

 

UNION ALL

 

SELECT

    rsd.business_date BUSINESS_DATE,

    to_char(rsd.business_date,‘MM’) MONTH,

    to_char(rsd.business_date,‘RRRR’) YEAR,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname) CIA_NAME,

    rsd.market_main_group MARKET_GRP,

    rsd.market_code,

    n2.industry_code,

    au.app_user OWNER,

    rsd.rate_code RATE_CODE,

    sum(rsd.room_revenue) ROOM_REVENUE,sum(rsd.stay_rooms) ROOMS

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE

    rsd.business_date=TO_CHAR(pms_p.business_date2)

    and  rsd.company_id is null

    and no.name_id = rsd.agent_id

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’ and rsd.resv_status <> ‘CHECKED OUT’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.agent_id

    and rsd.room_revenue<>0

GROUP BY

    rsd.business_date,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname),

    rsd.market_main_group,

    rsd.market_code,

    n2.industry_code,

    au.app_user,

    rsd.rate_code

 

 

–Produccion por ejecutivo, habitacion y empresa

SELECT

    rsd.room,n1.last,

    rsd.business_date BUSINESS_DATE,

    to_char(rsd.business_date,‘MM’) MONTH,

    to_char(rsd.business_date,‘RRRR’) YEAR,

    rsd.stay_rooms STAY_ROOMS,

    decode(n2.sname,NULL,‘SIN COMPAÑIA’,n2.sname) CIA_NAME,

    au.app_user CIA_OWNER,

    rsd.room_revenue ROOM_REVENUE,

    rsd.source_code

FROM

    reservation_stat_daily rsd, name_owner no, application$_user au, name n1, name n2

WHERE no.name_id = rsd.company_id

    and rsd.room_revenue<>0

    and no.primary_yn = ‘Y’

    and au.person_name_id = no.user_id

    and (rsd.resv_status <> ‘CANCELLED’ and rsd.resv_status <> ‘NO SHOW’)

    and n1.name_id(+) = rsd.name_id

    and n2.name_id(+) = rsd.company_id

order by BUSINESS_DATE,room

 

–Forecast por Market Code Mensual Detallado

SELECT

    CONSIDERED_MONTH,

    CONSIDERED_YEAR,

    CONSIDERED_DATE,

    MARKET_GROUP,

    MARKET_GROUP_DESC,

    MARKET_CODE,

    NO_ROOMS,

    ROOM_REVENUE

FROM(

    SELECT

        to_char(rsf.considered_date,‘MONTH’) CONSIDERED_MONTH,

        to_char(rsf.considered_date,‘RRRR’) CONSIDERED_YEAR,

        mc.parent_market_code MARKET_GROUP,

        mg.description MARKET_GROUP_DESC,

        rsf.market_code MARKET_CODE,

        rsf.considered_date CONSIDERED_DATE,

        decode(rsf.room_category,-1,0,rsf.no_rooms) NO_ROOMS,

        rsf.net_room_revenue ROOM_REVENUE

    FROM

        rep_res_statistics_forecast rsf, markets mc, market_groups mg

    WHERE

        (year(rsf.CONSIDERED_DATE) =2019 )

        AND (mc.parent_market_code = mg.market_group AND rsf.market_code(+) = mc.market_code )

   

    )

 

 

–Activity Search – Notas por ejecutivo y huesped

SELECT a.salesrep,a.insert_date, a.act_type, a.purpose,a.acc_name, a.con_name, a.notes,a.problem_desc

  FROM activity_search a

  WHERE a.insert_date>= sysdate1

  ORDER BY a.insert_date

 

–Reservas canceladas

SELECT a.nights*a.share_amount as trevenue, a.nights,b.srep_code,a.rate_code,a.sfirst_guest_name || ‘ ‘ || a.guest_name as nombres, a.trunc_arrival, a.trunc_departure,

    b.name,a.cancellation_reason_desc

FROM reservation_general_view a, name_view b

WHERE a.resv_status=‘CANCELLED’

    and a.booked_room_category_label<>‘PM’

    and substr(a.cancellation_date,1,11)>=TO_CHAR(SYSDATE10)

    and b.name_id = decode(a.company_id,null,a.travel_agent_id,a.company_id)

ORDER BY a.trunc_arrival

 

–Contactos por empresa

SELECT a.name as Apellidos,a.first as Nombre,a.udfc10 ,a.udfd11 as sexo,a.title as Trato,

   a.business_title as Cargo,a.email,a.name_id as Key_con_Cia,a.company Razon_Social,a.udfc12 as Nbr_Comercial,

   a.address1 as Direccion,a.city as Distrito, a.udfc13 as Rubro, a.srep_code as Owner,a.phone_no as Telefono,

   a.mobile_phone_number as Celular,a.udfc14,a.udfc15,a.udfc16,a.udfc01 as DClub

FROM name_view a

WHERE a.tracecode=‘CON’ and a.active_yn=‘Y’ and a.country=‘PE’

ORDER BY a.name