CREATE OR REPLACE VIEW ke014t3.bcd_ict_offline_sales_query AS
select
BRANCH,YEAR,MONTH,TKT_DATE,XONO,XCRNO,TKTNO,RE_ISSUED_NO,AIRLINE,SUP_NAME,STATUS,TRVL_DATE,PAX_NAME,ROUTE,CUSTCODE,CUST_NAME,INV_NO,
CCY,INV_AMT,PAY_AMT,USD_INV_AMT,KES_INV_AMT,USD_PAY_AMT,KES_PAY_AMT,PNR,
-- Adding of new columns - UN
(case
when branch='UN' then USD_INV_AMT
end) UN_USD_INV_AMT,
(case
when branch='UN' then KES_INV_AMT
end) UN_KES_INV_AMT,
(case
when branch='UN' then USD_PAY_AMT
end) UN_USD_PAY_AMT,
(case
when branch='UN' then KES_PAY_AMT
end) UN_KES_PAY_AMT,
-- end of nw column add
-- Adding of new columns - HO
(case
when branch='HO' then USD_INV_AMT
end) HO_USD_INV_AMT,
(case
when branch='HO' then KES_INV_AMT
end) HO_KES_INV_AMT,
(case
when branch='HO' then USD_PAY_AMT
end) HO_USD_PAY_AMT,
(case
when branch='HO' then KES_PAY_AMT
end) HO_KES_PAY_AMT,

-- end of nw column add
-- Adding of new columns - SAFCOM
(case
when branch='SAFCOM' then USD_INV_AMT
end) SAFCOM_USD_INV_AMT,
(case
when branch='SAFCOM' then KES_INV_AMT
end) SAFCOM_KES_INV_AMT,
(case
when branch='SAFCOM' then USD_PAY_AMT
end) SAFCOM_USD_PAY_AMT,
(case
when branch='SAFCOM' then KES_PAY_AMT
end) SAFCOM_KES_PAY_AMT,
-- new clumns added for more details tab

TC,TC_NAME,TRAVEL_START_DATE,TRAVELLER_RETURN_DATE,CLASS_OF_TRAVEL,AGE_OF_TRAVELLER,TKT_LPO,LPO_DATE,INVOICE_DATE,EX_RATE,SELL_FARE,
YR_TAX,IPP,OTHER_TAXES,TOTAL_TAX,TOT_COMMIS,TKT_REMARKS,SF,ADMP1,ADMP2,ADMP3,SF_BLOCK_TOTAL,NET_RECEIVABLE,NET_PAYABLE,NET_EARNINGS,
CORP_CODE,DAYEND_NO,TKT_CREATED_BY,TKT_CREATED_ON,TKT_MODIFIED_BY,TKT_MODIFIED_ON,TICKET_MODE,TICKET_DAYEND_STATUS

-- end of nw column add
--      -----------------------------------------------------------------------UN Branch Data
from
(
select 'UN' Branch,
extract(year from ticket_date) Year, extract(month from ticket_date) Month,
ticket_date TKT_DATE,ticket_xo_number XONO,
(case
when stock_airline='XOS' then TICKET_XO_PAY_NUMBER
when stock_airline<>'XOS' then
(select air_acn from ke014t3.bcd_ict_ticket_acn_details where air_branch='UN' and stock_airline=air_airline and ticket_number=air_ticket and rownum=1)
end) XCRNO,
TICKET_NUMBER TKTNO,TICKET_EXCHANGE_DOCNO RE_ISSUED_NO, REPORING_AIRLINE AIRLINE,
Replace(replace(replace(replace(replace(replace(SUPPLIER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') SUP_NAME,
ticket_status STATUS,TICKET_TRAVEL_DATE TRVL_DATE,
 Replace(replace(replace(replace(replace(replace(TICKET_PAX_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') PAX_NAME,
 TICKET_ROUTING ROUTE,
 TICKET_CUSTOMER_1 CUSTCODE,
Replace(replace(replace(replace(replace(replace( TICKET_CUSTOMER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') CUST_NAME,
 TICKET_DOC_NUMBER1 INV_NO,
 ticket_currency CCY,  TICKET_DOC_AMOUNT1 INV_AMT, ticket_net_payable PAY_AMT,
(case
when ticket_currency='USD' then TICKET_DOC_AMOUNT1
end) USD_INV_AMT,
(case
when ticket_currency='KES' then TICKET_DOC_AMOUNT1
end) KES_INV_AMT,
(case
when ticket_currency='USD' then ticket_net_payable
end) USD_PAY_AMT,
(case
when ticket_currency='KES' then ticket_net_payable
end) KES_PAY_AMT,
 ticket_flex_v_2 PNR,
 -- ----------------------------------------------------------- Adding of new columsn for more details tab on non bsp form
 ticket_booking_clerk TC,
 (select exec_name from id_executive_master where exec_code=ticket_booking_clerk and rownum=1) TC_NAME,
 TICKET_TRAVEL_DATE Travel_Start_date,
(select SECTOR_ARRIVAL_DATE from ke019t3.id_ticket_sectors where sector_ticket=ticket_number and sector_serial=
(select max(sector_serial) from ke019t3.id_ticket_sectors where sector_ticket=ticket_number ) and rownum=1)
 Traveller_Return_date,
 TICKET_CLASS Class_of_travel, TICKET_AGE Age_of_Traveller,
 TICKET_LPO Tkt_lpo, TICKET_LPO_DATE LPO_Date, TICKET_DOC_DATE1 Invoice_date, ticket_ex_rate ex_rate, 
 TICKET_SELLING_FARE SELL_FARE, TICKET_TAX_YR YR_TAX, TICKET_TAX_IPP IPP, (TICKET_TOTAL_TAX-TICKET_TAX_YR-TICKET_TAX_IPP) Other_taxes, 
 TICKET_TOTAL_TAX Total_tax, TICKET_TOTAL_COMMISSION tot_commis, TICKET_REMARKS tkt_remarks,
SERVICE_FEE SF, ADM_PAYABLES_1 ADMP1, ADM_PAYABLES_2 ADMP2, ADM_PAYABLES_3 ADMP3, 
 TICKET_PAY_AMOUNT_1 SF_Block_total, 
 TICKET_NET_RECEIVABLE NET_RECEIVABLE, TICKET_NET_PAYABLE NET_PAYABLE, TICKET_NET_EARNINGS NET_EARNINGS,
 TICKET_CORPORATE_CODE Corp_code,
 TICKET_DAYEND_REFERENCE DAYEND_NO, 
 HOTEL_CONFIRMATION_NO Tkt_Created_By,
 ARRIVAL_DATE Tkt_created_on,
 TICKET_CREATED_BY Tkt_Modified_by,
 TICKET_CREATED_ON Tkt_Modified_on,
 ticket_mode, TICKET_DAYEND_STATUS
 from ke019t3.ticket_view_t3
where STOCK_AIRLINE<>'BSP' and ticket_date>'31-DEC-2016'
union all
--  -------------------------------------------------------------------------------HO Data
select 'HO' Branch,
extract(year from ticket_date) Year, extract(month from ticket_date) Month,
ticket_date TKT_DATE,ticket_xo_number XONO,
(case
when stock_airline='XOS' then TICKET_XO_PAY_NUMBER
when stock_airline<>'XOS' then
(select air_acn from ke014t3.bcd_ict_ticket_acn_details where air_branch='HO' and stock_airline=air_airline and ticket_number=air_ticket and rownum=1)
end) XCRNO,
TICKET_NUMBER TKTNO,TICKET_EXCHANGE_DOCNO RE_ISSUED_NO, REPORING_AIRLINE AIRLINE,
Replace(replace(replace(replace(replace(replace(SUPPLIER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') SUP_NAME,
ticket_status STATUS,TICKET_TRAVEL_DATE TRVL_DATE,
 Replace(replace(replace(replace(replace(replace(TICKET_PAX_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') PAX_NAME,
 TICKET_ROUTING ROUTE,
 TICKET_CUSTOMER_1 CUSTCODE,
Replace(replace(replace(replace(replace(replace( TICKET_CUSTOMER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') CUST_NAME,
 TICKET_DOC_NUMBER1 INV_NO,ticket_currency CCY,  TICKET_DOC_AMOUNT1 INV_AMT,
 ticket_net_payable PAY_AMT,
(case
when ticket_currency='USD' then TICKET_DOC_AMOUNT1
end) USD_INV_AMT,
(case
when ticket_currency='KES' then TICKET_DOC_AMOUNT1
end) KES_INV_AMT,
(case
when ticket_currency='USD' then ticket_net_payable
end) USD_PAY_AMT,
(case
when ticket_currency='KES' then ticket_net_payable
end) KES_PAY_AMT,
 ticket_flex_v_2 PNR,
 -- ----------------------------------------------------------- Adding of new columsn for more details tab on non bsp form
 ticket_booking_clerk TC,
 (select exec_name from id_executive_master where exec_code=ticket_booking_clerk and rownum=1) TC_NAME,
 TICKET_TRAVEL_DATE Travel_Start_date,
return_date Traveller_Return_date,
 TICKET_CLASS Class_of_travel, TICKET_AGE Age_of_Traveller,
 TICKET_LPO Tkt_lpo, TICKET_LPO_DATE LPO_Date, TICKET_DOC_DATE1 Invoice_date, ticket_ex_rate ex_rate, 
 TICKET_SELLING_FARE SELL_FARE, TICKET_TAX_YR YR_TAX, TICKET_TAX_IPP IPP, (TICKET_TOTAL_TAX-TICKET_TAX_YR-TICKET_TAX_IPP) Other_taxes, 
 TICKET_TOTAL_TAX Total_tax, TICKET_TOTAL_COMMISSION tot_commis, TICKET_REMARKS tkt_remarks,
SERVICE_FEE SF, ADM_PAYABLES_1 ADMP1, ADM_PAYABLES_2 ADMP2, ADM_PAYABLES_3 ADMP3, 
 TICKET_PAY_AMOUNT_1 SF_Block_total, 
 TICKET_NET_RECEIVABLE NET_RECEIVABLE, TICKET_NET_PAYABLE NET_PAYABLE, TICKET_NET_EARNINGS NET_EARNINGS,
 TICKET_CORPORATE_CODE Corp_code,
 TICKET_DAYEND_REFERENCE DAYEND_NO, 
 HOTEL_CONFIRMATION_NO Tkt_Created_By,
 ARRIVAL_DATE Tkt_created_on,
 TICKET_CREATED_BY Tkt_Modified_by,
 TICKET_CREATED_ON Tkt_Modified_on,
 ticket_mode, TICKET_DAYEND_STATUS
 from ke014t3.ticket_view_t3
where STOCK_AIRLINE<>'BSP' and ticket_date>'31-DEC-2015'
union all
--   -------------------------------------------------------------------Safricom Branch Data
select 'SAFCOM' Branch,
extract(year from ticket_date) Year, extract(month from ticket_date) Month,
ticket_date TKT_DATE,ticket_xo_number XONO,
(case
when stock_airline='XOS' then TICKET_XO_PAY_NUMBER
when stock_airline<>'XOS' then
(select air_acn from ke014t3.bcd_ict_ticket_acn_details where air_branch='SAFCOM' and stock_airline=air_airline and ticket_number=air_ticket and rownum=1)
end) XCRNO,
TICKET_NUMBER TKTNO,TICKET_EXCHANGE_DOCNO RE_ISSUED_NO, REPORING_AIRLINE AIRLINE,
Replace(replace(replace(replace(replace(replace(SUPPLIER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') SUP_NAME,
ticket_status STATUS,TICKET_TRAVEL_DATE TRVL_DATE,
 Replace(replace(replace(replace(replace(replace(TICKET_PAX_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') PAX_NAME,
 TICKET_ROUTING ROUTE,
 TICKET_CUSTOMER_1 CUSTCODE,
Replace(replace(replace(replace(replace(replace( TICKET_CUSTOMER_NAME,',','-'),'/','-'),chr(9),'-'),chr(10),'-'),chr(13),'-') ,'.','-') CUST_NAME,
 TICKET_DOC_NUMBER1 INV_NO,ticket_currency CCY,  TICKET_DOC_AMOUNT1 INV_AMT,
 ticket_net_payable PAY_AMT,
(case
when ticket_currency='USD' then TICKET_DOC_AMOUNT1
end) USD_INV_AMT,
(case
when ticket_currency='KES' then TICKET_DOC_AMOUNT1
end) KES_INV_AMT,
(case
when ticket_currency='USD' then ticket_net_payable
end) USD_PAY_AMT,
(case
when ticket_currency='KES' then ticket_net_payable
end) KES_PAY_AMT,
 ticket_flex_v_2 PNR,
 -- ----------------------------------------------------------- Adding of new columsn for more details tab on non bsp form
 ticket_booking_clerk TC,
 (select exec_name from id_executive_master where exec_code=ticket_booking_clerk and rownum=1) TC_NAME,
 TICKET_TRAVEL_DATE Travel_Start_date,
return_date Traveller_Return_date,
 TICKET_CLASS Class_of_travel, TICKET_AGE Age_of_Traveller,
 TICKET_LPO Tkt_lpo, TICKET_LPO_DATE LPO_Date, TICKET_DOC_DATE1 Invoice_date, ticket_ex_rate ex_rate, 
 TICKET_SELLING_FARE SELL_FARE, TICKET_TAX_YR YR_TAX, TICKET_TAX_IPP IPP, (TICKET_TOTAL_TAX-TICKET_TAX_YR-TICKET_TAX_IPP) Other_taxes, 
 TICKET_TOTAL_TAX Total_tax, TICKET_TOTAL_COMMISSION tot_commis, TICKET_REMARKS tkt_remarks,
SERVICE_FEE SF, ADM_PAYABLES_1 ADMP1, ADM_PAYABLES_2 ADMP2, ADM_PAYABLES_3 ADMP3, 
 TICKET_PAY_AMOUNT_1 SF_Block_total, 
 TICKET_NET_RECEIVABLE NET_RECEIVABLE, TICKET_NET_PAYABLE NET_PAYABLE, TICKET_NET_EARNINGS NET_EARNINGS,
 TICKET_CORPORATE_CODE Corp_code,
 TICKET_DAYEND_REFERENCE DAYEND_NO, 
 HOTEL_CONFIRMATION_NO Tkt_Created_By,
 ARRIVAL_DATE Tkt_created_on,
 TICKET_CREATED_BY Tkt_Modified_by,
 TICKET_CREATED_ON Tkt_Modified_on,
 ticket_mode, TICKET_DAYEND_STATUS
 from ke074t3.ticket_view_t3
where STOCK_AIRLINE<>'BSP' and ticket_date>'31-DEC-2015')


