create or replace view ke014t3.bcd_ict_sales_view_May_2020 as
select 
Stock_Airline ,
Ticket_Date,
extract(month from ticket_date) Month,
extract(year from ticket_date) Year,
REPORING_AIRLINE Airline_Code,
Ticket_Number,
Ticket_Status,
Ticket_Location,
Replace(replace(replace(replace(replace(replace(Ticket_Pax_Name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') pax_name,
Replace(replace(replace(replace(replace(replace(Ticket_Routing,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') Routing,
TICKET_TRAVEL_DATE Travel_Date,
ARRIVAL_DATE Return_Date,
ticket_Booking_Clerk TC,
replace(replace(replace(replace(replace(trim((select upper( EXEC_NAME)  from ID_EXECUTIVE_MASTER where  EXEC_code=ticket_Booking_Clerk)),',',' '),chr(9),'-'),chr(10),'-'),chr(13),'-'),'.','-')  BCD_TC_Name,
Replace(replace(replace(replace(replace(replace(TICKET_CUSTOMER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-')  Customer_Name,
TICKET_CORPORATE_CODE Corporate_Code,
Replace(replace(replace(replace(replace(replace(Airline_Name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') Airline_Name,
Replace(replace(replace(replace(replace(replace(Supplier_Name,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') Supplier_Name,
TICKET_SELLING_FARE Sell_Fare,
TICKET_TOTAL_TAX Tax, TICKET_TAX_IPP IPP, TICKET_TOTAL_COMMISSION Commission,
TICKET_DOC_AMOUNT1 Invoice_Amount,
TICKET_DOC_NUMBER1 Invoice_Number,
ticket_ex_rate Exchange_Rate,
round((TICKET_DOC_AMOUNT1*ticket_ex_rate),2) Kes_Equivalent,
ticket_Currency CCY,
TICKET_NET_PAYABLE PAY_TO_SUPP,
TICKET_NET_RECEIVABLE RECEIVE_FROM_CUSTOMER,
TICKET_NET_EARNINGS Net_Earnings
--select *
from ke014t3.ticket_view_t3 where ticket_date>'31-DEC-2017'

