CREATE VIEW bcd_ict_all_booking_view_b (
   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 booking_date) Year,
extract(Month from booking_date) Month,
BOOKING_DATE BK_Date,
replace(replace(replace(replace(replace(replace(PR_TR_No,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') PR_TR_No,
replace(replace(replace(replace(replace(replace(PO_No,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') PO_No,
replace(replace(replace(replace(replace(replace(Booker_PA,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') Booker_PA,
replace(replace(replace(replace(replace(replace(PAX_NAME,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') Pax_Name,
BOOKING_NUMBER BK_No,
decode(NEXT_PROCESS_NUMBER,3,'OPEN',4,'Partially Supplied',5,'Fully Supplied',6,'Partially Invoiced',7,'Fully Invoiced', 8, 'Closd',9,'Completed',0,'Cancelled') BK_Status,
sv_date SV_Date,
sv_number SV_No,
replace(replace(replace(replace(replace(replace(sv_description,',','-'),'/','-'),' ','-'),chr(13),'-'),chr(10),'-'),chr(9),'-') SV_Dtls,
EXECUTIVE TC,
(select exec_name from ke014t3_support.id_executive_Master where exec_code=executive and rownum=1) TC_Name,
service Service,
provider Provider,
(select tbpm_name from ke014t3_support.id_tb_provider_master where tbpm_provider=provider and rownum=1) Prov_Name,
supplier,
customer,
(select ar_name from ke074t3_support.id_ar_master where ar_code=customer and rownum=1) Cust_Name,
units,
city,
date_in CID,
Date_out COD,
-- Cost Elemets
round(cost_rate,2) cost_rate,
cost_currency,
round(cost_ex_rate,2) cost_ex_rate,
round(cost_f_amount,2) cost_f_amount,
round(cost_l_Amount,2) cost_l_Amount,
-- Saelling
invoice_currency,
round(invoice_ex_rate,2) invoice_ex_rate,
round(INV_F_AMOUNT,2) INV_F_AMOUNT,
round(INV_L_AMOUNT,2) INV_L_AMOUNT,
inv_date,
invoice_number,
(select bpv_number from ke014t3_support.bcd_audit_paid_sv_view_2 where a.sv_number=sv_number and rownum=1) bpv_number,
(select ARM_M_NUMBER from ke014t3_support.id_ar_match_details where arm_t_number=invoice_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=invoice_number and rownum=1)) Receipt_date
from ke014t3_support.BCD_ICT_tb_booking_mis_view a where booking_date>'01-JUN-2017'
/