create or replace view ke014t3_support.bcd_ict_cust_sale_refun_coll as
(
-- ---------------------------------------------------------------------------------------- TICKET_SALES_CREDIT - No Credit Card Sales and no MISC Sales
select
TICKET_CUSTOMER_1 Cust_Code,Ticket_status Trans_Flag, 'TICKET_SALES_CREDIT' Trans_Type,
extract(year from ticket_date) Year,
extract(Month from ticket_date) Month,
round(sum((TICKET_net_receivable-TICKET_CARD_AMOUNT)*ticket_ex_rate),0) KES_VAL
 from ticket_view_t3
where
stock_airline not in ('MISC')
and ticket_status='S'
group by TICKET_CUSTOMER_1,ticket_status, 'TICKET_SALES_CREDIT' ,
extract(year from ticket_date),
extract(Month from ticket_date)
having (round(sum(TICKET_net_receivable*ticket_ex_rate),0))<>0
-- ------------------------------------------------------------------------ TICKET_SALES_CARD Only BSP Sales
union all
select
TICKET_CUSTOMER_1 Cust_Code,Ticket_status Trans_Flag, 'TICKET_SALES_CARD' Trans_Type,
extract(year from ticket_date) Year,
extract(Month from ticket_date) Month,
round(sum((TICKET_CARD_AMOUNT)*ticket_ex_rate),0) KES_VAL
 from ticket_view_t3
where
stock_airline in ('BSP')
and ticket_status='S'
group by TICKET_CUSTOMER_1, ticket_status,'TICKET_SALES_CARD' ,
extract(year from ticket_date),
extract(Month from ticket_date)
having (round(sum(TICKET_CARD_AMOUNT*ticket_ex_rate),0))<>0
-- --------------------------------------------------------------------------------- OTHER_SALES_CREDIT
union all
select
TICKET_CUSTOMER_1 Cust_Code,Ticket_status Trans_Flag, 'OTHER_SALES_CREDIT' Trans_Type,
extract(year from ticket_date) Year,
extract(Month from ticket_date) Month,
round(sum(ticket_net_receivable*ticket_ex_rate),0) KES_VAL
 from ticket_view_t3
where
stock_airline in ('MISC')
and ticket_status='S'
group by ticket_net_receivable, 'OTHER_SALES_CREDIT' ,
extract(year from ticket_date),
extract(Month from ticket_date)
having (round(sum(ticket_net_receivable*ticket_ex_rate),0))<>0
-- -------------------------------------------------------------------------- TICKET_REFUND_CREDIT
union all
select
TICKET_CUSTOMER_1 Cust_Code,Ticket_status, 'TICKET_REFUND_CREDIT' Trans_Type,
extract(year from ticket_date) Year,
extract(Month from ticket_date) Month,
round(sum((TICKET_net_receivable-TICKET_CARD_AMOUNT)*ticket_ex_rate),0) KES_VAL
from ticket_view_t3
where
stock_airline not in ('MISC')
and ticket_status='R'
group by TICKET_CUSTOMER_1,'TICKET_REFUND_CREDIT', 
extract(year from ticket_date),
extract(Month from ticket_date)
having (round(sum((TICKET_net_receivable-TICKET_CARD_AMOUNT)*ticket_ex_rate),0))<>0
union all
select
TICKET_CUSTOMER_1 Cust_Code,Ticket_status, 'OTHER_REFUND_CREDIT' Trans_Type,
extract(year from ticket_date) Year,
extract(Month from ticket_date) Month,
round(sum(TICKET_DOC_AMOUNT1*ticket_ex_rate),0) KES_VAL
from ticket_view_t3
where
stock_airline in ('MISC')
and ticket_status='R'
group by TICKET_CUSTOMER_1,'OTHER_REFUND_CREDIT', 
extract(year from ticket_date),
extract(Month from ticket_date)
having (round(sum(TICKET_DOC_AMOUNT1*ticket_ex_rate),0))<>0


union all
select
art_code Cust_Code,'C' Trans_Flag,
art_year Year, art_period Month,sum(art_local_amount) KES_VAL
from id_ar_trans 
where art_type in ('BRV')
group by 
art_Code,
'C',
art_year, art_period
having 
(sum(art_local_amount))<>0
)
order by Cust_Code,Trans_Type, Year, Month


select 
tbk_customer, 
extract(year from tbk_booking_date) Year, 
extract(Month from tbk_booking_date) Month, 
'S' Trans_Flag,'MICE_SALES' Trans_Type,
sum(TBK_INVOICE_TOTAL*
from
id_tb_booking_header

select * from id_tb_booking_header where tbk_date>'01-JAN-2020' and tbk_booking_number='OB20010022'
select * from id_tb_trans where TBT_DOC_DATE>'01-JAN-2020' and tbt_doc_type='TIN' and TBT_CURRENCY='USD'
select * from id_tb_booking_rate_details where tbrd_status<>'M' and TBRD_INV_TYPE is null and tbrd_reference=4708
select sum(TBRD_INVOICE_L_AMOUNT) from id_tb_booking_rate_details where tbrd_reference='40953'
select * from id_tb_booking_header where tbk_reference=40937
select * from id_tb_booking_rate_details where tbrd_inv_type is null and TBRD_INVOICE_L_AMOUNT>0 and TBRD_FROM_DATE>'01-JAN-2020' and tbrd_reference='40937'

select sum(TBRD_INVOICE_L_AMOUNT) from id_tb_booking_rate_details 

select distinct ART_BASE_TYPE from id_ar_trans where ART_BASE_TYPE not in ('INV') ,'RCT','RCT-REV')
OPEN - opening balance only as ARO
JV
DR
PAY
PAY-REV
CR

select distinct art_type from id_ar_trans where ART_BASE_TYPE in ('CR')
select * from ticket_view_t3 where ticket_mode=6 and ticket_date>'01-JAN-2020'
