create or replace view ke014t3.bcd_ict_ho_tour_sales_07_2020 as
select 'HO' Branch,
trim(rownum) SEQ ,
trim(EXTRACT(year FROM tbk_date)) year,
ke014t3_support.bcd_ict_get_quarter(tbk_date) Quarter,
trim(EXTRACT(month FROM tbk_date)) month,
e.BK_City, e.country, e.country_name, e.city_name,
TBK_CUSTOMER Cust ,
Replace(replace(replace(replace(replace(replace((SELECT AR_NAME FROM ID_AR_MASTER WHERE AR_CODE=TBK_CUSTOMER AND ROWNUM=1),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-')
 CUSTOMER_NAME,
trim(TBK_DATE) BK_date ,
trim(TBK_EXECUTIVE_CODE) TC,
Replace(replace(replace(replace(replace(replace((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where TBK_EXECUTIVE_CODE=EXEC_CODE),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') BCD_TC_Name,
trim(TBK_BOOKING_TYPE) IBOB,
trim(TBK_BOOKING_NUMBER) BKNO,
trim(TBSVH_SV_NUMBER) SVNO,
(case
when tbrd_supplier is not null then tbrd_supplier
else
e.tbrd_provider
end) Property,
Replace(Replace(Replace(Trim((select tbpm_name from id_tb_provider_master where tbpm_provider=
(case
when tbrd_supplier is not null then tbrd_supplier
else
e.tbrd_provider
end)
 and rownum=1)),',','-'),'.','-'),'/','-') PROV_NAME,
TBSVH_AP_CODE AP_CODE,
replace(trim(TBSVD_SERVICE),',',' ') BKSERVICE,
Replace(replace(replace(replace(replace(replace(replace(trim(TBSVD_SERVICE_DESCRIPTION),',',' '),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') SERVICEDESC,
trim(TBSVD_CURRENCY) CCY,
TBSVD_FOREIGN_AMOUNT SVFAMT,
TBSVD_LOCAL_AMOUNT SVLAMT,
trim(TBSVD_FROM_DATE) CID,
trim(TBSVD_TO_DATE) COD,
f.tbrd_invoice INV,
(case
when f.tbrd_invoice is not null then 'INVOICED'
else 'NOTINVOICED'
end) INVSTAT,

Replace(replace(replace(replace(replace(replace(replace(trim(TBK_TMP_CUST_NAME),',',' '),',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') PAXNAME,
decode(tbk_next_process_number,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced','Fully Invoiced') BKSTAT,
decode(tbsvh_status,'T','NOTPOSTED','POSTED') svstat,
e.PROV_CCY,e.FC_PROV_EX_RATE,e.EX_RATE,e.FC_MKUP,e.UNITS,e.NIGHTS,e.LC_INV_VAL,e.LC_COST_AMT,e.PRROFIT
from
ke014t3.id_tb_booking_header a,ke014t3.ID_TB_SERVICE_VOUCHER_DETAILS B,
ke014t3.id_tb_service_voucher_header c,ke014t3.id_ap_master d,
ke014t3.BCD_ICT_TBRD_SVS_2020 e, ke014t3.bcd_ict_tour_sv_and_invoices f
where TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and c.TBSVH_SV_NUMBER=e.sv
and c.TBSVH_SV_NUMBER=f.inv_sv
and tbk_status<>'F'
and TBSVH_AP_CODE=AP_CODE
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and a.tbk_date between '01-JAN-2017' and sysdate and TBSVH_STATUS in('P','T')


--select * from  ke014t3.BCD_ICT_TBRD_SVS_2020
-- select * from ke014t3.bcd_ict_tour_sv_and_invoices
-- select * from ke014t3.bcd_ict_ho_tour_sales_07_2020
