select * from ftms_etravel.ftms_in where FTMS_ETRAVEL_DATE>'01-JAN-2020' and booking_status='C'
select * from ftms_etravel.ftms_in where  booking_status not in ('O','D','C','X','M')
update ftms_etravel.ftms_in set booking_status=substr(substr(FTMS_ETRAVEL_DATALINE,instr(FTMS_ETRAVEL_DATALINE,'|',-1,6),2),2,1)

update ftms_etravel.ftms_in set sales_date='' where (substr(FTMS_ETRAVEL_DATALINE,8,5))='NOW()'

select instr('|',substr(FTMS_ETRAVEL_DATALINE,length(FTMS_ETRAVEL_DATALINE)-1,-1)) from ftms_etravel.ftms_in 

select * from ftms_etravel.ftms_in where (substr(FTMS_ETRAVEL_DATALINE,8,5))='NOW()' and booking_status<>'O'
select * from ftms_etravel.ftms_in where currency not in ('KES','USD','GBP','EUR')
select * from ftms_etravel.ftms_in where substr(FTMS_ETRAVEL_DATALINE,-8,1)='M'
select FTMS_ETRAVEL_DATALINE,sales_date,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER') from ftms_etravel.ftms_in where instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')=43 not in (42,41,36)

|03|03|08-Jul-2019|KES|09-Jul-2019|113416|TRANSFER|STEPHEN  OGOLA|S0171|MN|MN|AVIS RENTAL|1400|Pickup Loc: MAMBOLEO, Drop Off Location : KISUMU AIRPORT, Car Type : Saloon Car|CREDIT|PO 308765||M|||||

|03|03|NOW()|KES|15-Aug-2018|84593|TRANSFER|ELIN HILWIG|||||1700|Pickup Loc: JKIA, Drop Off Location : Bellcrest gardens, Apt A1, Githunguri Rd, Kileleshwa , Car Type : |CREDIT|||O|||||

select ticket_number, ticket_flex_v_2 from id_ticket_details where ticket_number like 'M%' and length(TICKET_FLEX_V_2)>5

select FTMS_ETRAVEL_DATALINE,sales_date,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER'),substr(substr(FTMS_ETRAVEL_DATALINE,instr(FTMS_ETRAVEL_DATALINE,'|',-1,6),2),2,1) from ftms_etravel.ftms_in where substr(substr(FTMS_ETRAVEL_DATALINE,instr(FTMS_ETRAVEL_DATALINE,'|',-1,6),2),2,1) not in ('O','D','C','X','M')

--substr(substr(FTMS_ETRAVEL_DATALINE,instr(FTMS_ETRAVEL_DATALINE,'|',-1,6)
-- voucher update start
--select
--FTMS_ETRAVEL_DATALINE,sales_date,
--length((substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2))),
--(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2),'|',-1,1)),
update ftms_etravel.ftms_in set CONTROL_NUMBER=(
substr((substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2)),
(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2),'|',-1,1))+1,

length((substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2)))-
(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2),'|',-1,1))
))
--from ftms_etravel.ftms_in where FTMS_ETRAVEL_DATALINE like '%59436%'
---voucher update end
--INSTR( name, ' ', -1 , 2 ) + 1
|03|03|09-Jun-2017|KES|10-Jun-2017|45466|TRANSFER|Mr. SAMBULO MLAMBO|||||1600|Pickup Loc: PERGOLLA Est , Karen Plains near hillcrest school, Drop Off Location : JKIA, Car Type : Saloon Car|CREDIT|136752||C|||||
|03|03|07-Dec-2017|KES|12-Dec-2017|59436|TRANSFER|Mr. HABIB  NICOLAS|||||67500|Pickup Loc: It is Rosslyn Gardens Villa #4 Off Rosslyn Lone Tree.   Take the Road up the hill opposite Rosslyn Riviera Mall.  On top of the hill at the junction turn left towards the Canadian High Commissioner Residency, then turn right on the rough road that goes do, Drop Off Location : It is Rosslyn Gardens Villa #4 Off Rosslyn Lone Tree.   Take the Road up the hill opposite Rosslyn Riviera Mall.  On top of the hill

select
(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')-2),'|',-1,1)), -- Control Number start point pipe at
(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')),'|',-2,1)) ,-- where sales Type pipt at
(instr(substr(FTMS_ETRAVEL_DATALINE,1,instr(FTMS_ETRAVEL_DATALINE,'TRANSFER')),'|',-2,1)) -- where customer
from ftms_etravel.ftms_in
where FTMS_ETRAVEL_DATALINE like '%59436%'

select instr(FTMS_ETRAVEL_DATALINE,'TRANSFER') from ftms_etravel.ftms_in
where FTMS_ETRAVEL_DATALINE like '%59436%'

select * from ftms_etravel.ftms_in where employee_number='NOW()'

update  ftms_etravel.ftms_in set trip_purpose =
(select TICKET_DOC_NUMBER1 from ticket_view_t3 where ticket_flex_v_2=CONTROL_NUMBER and rownum=1) 
where 

select (instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 8)+1) from ftms_etravel.ftms_in

select ((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 10)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1)-1
))) from ftms_etravel.ftms_in where
((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 10)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1)-1
))) 
is not null

update ftms_etravel.ftms_in set sales_type='TRANSFER' 

update ftms_etravel.ftms_in set travel_date=to_date(substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 5)+1),1,11)) where employee_number='NOW()'


--pax_name
update ftms_etravel.ftms_in set pax_name=
((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 8)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 8)+1)-1
))
)

--customer code
update ftms_etravel.ftms_in set customer_1=
((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 10)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 9)+1)-1
)))

--TC & executive Update

update ftms_etravel.ftms_in set executive=
((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 10)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 11)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 10)+1)-1
)))

--agent code

update ftms_etravel.ftms_in set agent_code=
((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 12)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 12)+1)-1
)))


--selling fare

update ftms_etravel.ftms_in set selling_fare=
to_number(((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 14)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1)-1
))))

-- Remarks
update ftms_etravel.ftms_in set remarks=
substr((((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 14)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 15)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 14)+1)-1
)))),1,500)

--settlement_mode
update ftms_etravel.ftms_in set settlement_mode='CREDIT' 

-- LPO Number
update ftms_etravel.ftms_in set LPO_NUMBER=
substr(((((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 16)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 17)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 16)+1)-1
))))),1,50)

-- LPO date - Dates are wrong could not replace
update ftms_etravel.ftms_in set LPO_DATE=
(((((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 17)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 18)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 17)+1)-1
))))))


select
to_number(((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 14)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1)-1
)))) from ftms_etravel.ftms_in
where
(((substr(substr(FTMS_ETRAVEL_DATALINE, instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1),1,
(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 14)+1 )-(instr(FTMS_ETRAVEL_DATALINE, '|', 1, 13)+1)-1
))) )>0


|Pickup Loc: MAMBOLEO, Drop Off Location : KISUMU AIRPORT, Car Type : Saloon Car|

select * from ftms_etravel.ftms_in
where
customer_1 not in
(select ar_code from ke014t3.id_ar_master union all select ar_code from ke019t3.id_ar_master union all select ar_code from ke074t3.id_ar_master)
and FTMS_ETRAVEL_DATE>'31-DEC-2019' and booking_status='C'

select * from ftms_etravel.ftms_in
where
control_number=
(select ticket_flex_v_2 from
((select ticket_flex_v_2 from ke019t3.ticket_view_t3 where rownum=1
union all select ticket_flex_v_2 from ke014t3.ticket_view_t3 where rownum=1
union all select ticket_flex_v_2 from ke074t3.ticket_view_t3 where rownum=1)) where rownum=1 )
and customer_1 not in
(select ar_code from ke014t3.id_ar_master where rownum=1 union all
select ar_code from ke019t3.id_ar_master where rownum=1 union all
select ar_code from ke074t3.id_ar_master where rownum=1)
and FTMS_ETRAVEL_DATE>'31-DEC-2019' and booking_status='C'
select control_number,cust from
(select control_number,
(select ticket_flex_v_2 from id_ticket_details where ticket_flex_v_2=control_number and rownum=1),
(select ticket_customer_1 from id_ticket_details where ticket_flex_v_2=control_number and rownum=1) cust
from 
(select * from ftms_etravel.ftms_in
where
customer_1 not in
(select ar_code from ke014t3.id_ar_master union all select ar_code from ke019t3.id_ar_master union all select ar_code from ke074t3.id_ar_master)
and FTMS_ETRAVEL_DATE>'31-DEC-2019' and booking_status='C'))
where cust is not null
