select 
'HQ' Branch, ticket_date TKT_DATE, ticket_number TKT_NO, SUPPLIER_CODE TKT_SUPPLIER ,
ticket_booking_clerk TC,
TICKET_PAX_NAME PAX, TICKET_ROUTING ROUTING, 
TICKET_CUSTOMER_1 CUSTOMER, TICKET_DOC_NUMBER1 INVOICE,
ticket_currency CCY,TICKET_DOC_AMOUNT1 INV_AMT,
Ticket_net_payable NET_PAY, ticket_net_receivable Collect_Amt
from
ke014t3.ticket_view_t3 B
where
stock_airline='BSP' and ticket_status='S'
and SUPPLIER_CODE='BSP'
and ticket_date between trunc(sysdate-1,'YYYY') and -- let a decision be taken if the year is just started
                                                    -- to  take tickets for last 3 months
(case
    when extract(day from sysdate) between 1 and 8 then 
        to_date((to_char(sysdate,'YYYY'))  || (to_char(sysdate,'MON')) || 8, 'YYYYMMDD')
    when extract(day from sysdate) between 9 and 15 then
        to_date((to_char(sysdate,'YYYY'))  || (to_char(sysdate,'MON')) || 15, 'YYYYMMDD')    
    when extract(day from sysdate) between 16 and 23 then 
        to_date((to_char(sysdate,'YYYY'))  || (to_char(sysdate,'MON')) || 23, 'YYYYMMDD')    
    when extract(day from sysdate) >23 then
        to_date((to_char(sysdate,'YYYY'))  || (to_char(sysdate,'MON')) || (TRUNC(sysdate+30, 'MONTH')-1), 'YYYYMMDD')  
end)
and ticket_net_payable<>0 and ticket_mode<>3
--and  ticket_mode=6 and 
--ticket_card_amount<>(ticket_selling_fare+Ticket_total_tax-ticket_tax_ipp-ticket_total_commission)
and ticket_number not in 
(select ticket_number from ke014t3.id_ticket_details_bsp A
where A.ticket_number=B.Ticket_number and ticket_status='S' and ticket_payable<>0)

create table ke014t3_support.bcd_ict_bsp_report_periods
(BIBRPS_period_number number,
BIPRPS_Period_start_date date,
BIPRPS_period_end_date date,
BIPRPS_EXCEP_REP_YEAR number)


    --select * from id_ticket_details_bsp where ticket_admacm<>''
    -- select * from ticket_view_t3 where ticket_number='3709881594'
    
