CREATE VIEW bcd_ict_all_booking_view_d (
   year,
   month,
   bk_date,
   pr_tr_no,
   po_no,
   booker_pa,
   pax_name,
   bk_no,
   bk_status,
   sv_date,
   sv_no,
   sv_dtls,
   tc,
   tc_name,
   service,
   provider,
   prov_name,
   supplier,
   customer,
   cust_name,
   units,
   city,
   cid,
   cod,
   cost_rate,
   cost_currency,
   cost_ex_rate,
   cost_f_amount,
   cost_l_amount,
   invoice_currency,
   invoice_ex_rate,
   inv_f_amount,
   inv_l_amount,
   inv_date,
   invoice_number,
   bpv_number,
   receipt_number,
   receipt_date )
AS
select
extract(Year from b.tbk_date) Year,
extract(Month from b.tbk_date) Month,
b.tbk_date BK_Date,
replace(replace(replace(replace(replace(replace(b.tbk_flex_2,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') PR_TR_No,
replace(replace(replace(replace(replace(replace(b.tbk_flex_3,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') PO_No,
replace(replace(replace(replace(replace(replace(b.TBK_FLEX_4,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') Booker_PA,
replace(replace(replace(replace(replace(replace(b.TBK_TMP_CUST_NAME,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') Pax_Name,
b.tbk_booking_number BK_No,
'VOID' BK_Status,
a.TBSVH_DATE SV_Date,
a.TBSVH_SV_NUMBER SV_No,
replace(replace(replace(replace(replace(replace(a.TBSVH_VOUCHER_DESCRIPTION,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') SV_Dtls,
b.tbk_EXECUTIVE_code TC,
(select exec_name from ke014t3_support.id_executive_Master where exec_code=b.tbk_executive_code and rownum=1) TC_Name,
c.TBSVD_SERVICE Service,
c.TBSVD_PROVIDER Provider,
(select tbpm_name from ke014t3_support.id_tb_provider_master where tbpm_provider=c.TBSVD_PROVIDER and rownum=1) Prov_Name,
c.TBSVD_SUPPLIER supplier,
b.tbk_customer customer,
(select ar_name from ke014t3_support.id_ar_master where ar_code=b.tbk_customer and rownum=1) Cust_Name,
d.TBRD_NO_OF_UNITS units,
a.TBSVH_CITY city,
a.TBSVH_SERVICE_FROM_DATE CID,
a.TBSVH_SERVICE_TO_DATE COD,
-- Cost Elemets
round(d.TBRD_PROVIDER_RATE,2) cost_rate,
d.TBRD_PROVIDER_CURRENCY cost_currency ,
round(d.TBRD_PROVIDER_RATE,2) cost_ex_rate,
round(d.TBRD_COST_F_AMOUNT,2) cost_f_amount,
round(d.TBRD_COST_L_AMOUNT,2) cost_l_Amount,
-- Saelling
' ' invoice_currency,
0 invoice_ex_rate,
0 INV_F_AMOUNT,
0 INV_L_AMOUNT,
to_date('01-JAN-2000') inv_date,
' ' invoice_number,
(select bpv_number from ke014t3_support.bcd_audit_paid_sv_view_2 where a.tbsvh_sv_number=sv_number and rownum=1) bpv_number,
(select ARM_M_NUMBER from ke014t3_support.id_ar_match_details where arm_t_number='' and rownum=1) Receipt_Number,
(select RH_DOC_DATE from ke014t3_support.id_receipt_header where RH_DOC_NUMBER=(select ARM_M_NUMBER from ke014t3_support.id_ar_match_details where arm_t_number='' and rownum=1)) Receipt_date
from ke014t3_support.id_tb_service_voucher_header a, ke014t3_support.id_tb_booking_header b,
ke014t3_support.id_tb_service_voucher_details c , ke014t3_support.id_tb_booking_rate_details d
where
TBK_BOOKING_NUMBER=TBSVD_BOOKING_NUMBER
and TBSVD_SV_SERIAL=TBSVH_SERIAL
and TBK_REFERENCE=tbrd_reference
and tbrd_system=tbsvh_sv_number
and tbrd_status='V'
and TBRD_SYS_TYPE='SV'
and tbrd_system is not null
and tbk_date>'01_JUN-2017'
/