--create or replace view ke019t3.bcd_ict_ipp_collections as
(
select Charged_Mode, Ticketed_Year, Ticketed_Month, Tickets_sold,IPP_Amount from
(SELECT
   'IPP' Charged_Mode,
   to_char( A.ticket_date, 'yyyy' ) Ticketed_Year,
   to_char( A.ticket_date, 'mm' ) Ticketed_Month,
   count(a.ticket_number) Tickets_Sold,
   sum(round((B.TT_TAX_AMOUNT*A.ticket_ex_rate),2)) IPP_Amount
FROM
   ke019t3_SUPPORT.ID_TICKET_DETAILS A
 , ke019t3_SUPPORT.ID_TICKET_TAXES B
WHERE
(B.TT_TICKET_REFERENCE = A.TICKET_REFERENCE)
 AND (B.TT_TAX_CODE ='IPP')
and to_char( A.ticket_date, 'yyyy' )>2017
group by to_char( A.ticket_date, 'yyyy' ), to_char( A.ticket_date, 'mm' )

union all

select 
'ADMP3' Charged_Mode,
to_char( A.ticket_date, 'yyyy' ) Ticketed_Year,
to_char( A.ticket_date, 'mm' ) Ticketed_Month, 
count(a.ticket_number) Tickets_Sold,
sum(round((B.TS_AMOUNT*A.ticket_ex_rate),2)) IPP_Amount
FROM
   ke019t3_SUPPORT.ID_TICKET_DETAILS A
 , ke019t3_SUPPORT.id_ticket_services B
WHERE
(B.TS_TICKET_REFERENCE = A.TICKET_REFERENCE)
 AND (B.TS_service ='ADMP3')
and to_char( A.ticket_date, 'yyyy' )>2017
group by to_char( A.ticket_date, 'yyyy' ), to_char( A.ticket_date, 'mm' )
)
)
order by Ticketed_year, Ticketed_Month


select sum() from id_ticket_taxes, ticket_taxes where TS_TICKET_REFERENCE = TICKET_REFERENCE
select  ticket_reference from id_ticket_details where ticket_date between '01-MAY-2020' and '31-MAY-2020' and ticket_status='S'
