select
(select ticket_bsp_date from ke014t3.id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_DATE,
(select ticket_date from id_ticket_details where ticket_number=a.ticket_number and ticket_status='S' and rownum=1) Ticket_sold_date,
ticket_number TKT_NO,TICKET_REFUND_STATUS REF_STAT,
ticket_currency CCY,
(select ticket_currency from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_CCY,
TICKET_SELLING_FARE,
(select TICKET_MARKET_FARE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_FARE,
(TICKET_SELLING_FARE+(select TICKET_MARKET_FARE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1)) FARE_DIFF,
(TICKET_TOTAL_TAX-TICKET_TAX_IPP) WE_CHARGED_TAX,
(select TICKET_TOTAL_TAX from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_TOT_TAX,
((TICKET_TOTAL_TAX-TICKET_TAX_IPP)+(select TICKET_TOTAL_TAX from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1)) TAX_DIFF,
TICKET_TOTAL_COMMISSION COMMI,
(select TICKET_TOTAL_COMMISSION from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_COMMIS,
(TICKET_TOTAL_COMMISSION+(select TICKET_TOTAL_COMMISSION from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1)) COMMIS_DIFF,
(select TICKET_BSP_FROM_DATE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_REF_FROM_DATE,
(select TICKET_BSP_TO_DATE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_REF_2_DATE,
(select TICKET_PAYABLE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_REF_US,
(select TICKET_PAYABLE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='S' and rownum=1) WE_PAID_BSP,
SERVICE_FEE SFEE, ADM_PAYABLES_1 ADMP1, ADM_PAYABLES_2 ADMP2, ADM_PAYABLES_3 ADMP3,
TICKET_TAX_IPP IPP, REFUND_SERVICE_FEE Ref_SFEE, TICKET_AIR_RAF CP,
((select TICKET_PAYABLE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='S' and rownum=1)+
(select TICKET_PAYABLE from id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1))
BSP_CP,
(select ticket_date from id_ticket_details where ticket_number=a.ticket_number and ticket_status='S' and rownum=1) Ticket_sold_date,
(select TICKET_FILE_REFERENCE from ke014t3.id_ticket_details_bsp where ticket_number=a.ticket_number and ticket_status='R' and rownum=1) BSP_FILE,
TICKET_AGENT_RAF BCDFEE, 
(-TICKET_SELLING_FARE-TICKET_TOTAL_TAX+TICKET_AIR_RAF+REFUND_SERVICE_FEE+TICKET_TOTAL_COMMISSION+TICKET_TAX_IPP+ADM_PAYABLES_1) Ref_VAL, ticket_lpo,
(select arm_M_number from id_ar_match_details where arm_T_number=ticket_lpo and rownum=1) Receipt, ticket_doc_number1 TCN,HOTEL_CONFIRMATION_NO Modified_by, arrival_date Modified_on
from ke014t3.ticket_view_t3 a
where
a.ticket_status='R' and ticket_customer_1='A0226'
and a.ticket_number in 
--('3030223548','4795292558','4795318362','3709798307','3709870320','3709870325','3709870338','3709918180','3709918193','4795362121','4795382259')

('3746248822','3746248823','3746248824','3746248825','3746248826','3746248827','3746248828','3746248829','3746248830','3746248831','3746248832','3746248833','3746248834','3746248835','3746248836','3746248837','3746248838','3746248839','3746248840','3746248841','3746248842','3746248843','3746252947','3746252948','3746252949','3746252950','3746252951','3746252952','3746252953','3746252954','3746252955','3746252956','3746252957','3746252958','3746252959','3746252960','3746252965','3746252966','3746252967','3746252968','3746252969','3746252970','3746252971','3746252972','3746252973','3746252974','3746252975','3746252976','3746252977','3746252978','3746252981','3746252982','3746252983','3746252984','3746252985','3746252986','3746252987','3746252988','3746252989','3746252990','3746252991','3746252992','3746252993','3746252994','3746252995','3746252996')
order by TICKET_LPO, a.ticket_number


--select max(TICKET_BSP_FROM_DATE) from id_ticket_details_bsp 
--select * from id_ticket_details_bsp where ticket_date>'01-JAN-2020' and ticket_status='R'
