select templ.oper Oper, templ.fi_name FinInstitut, nvl(data.transactions, 0 ) transactions, nvl(data.Oborot, 0 ) Oborot,
templ.BINt BIN,templ.curr curr,
(case 
	when templ.curr like '810' then 'RUR'
	when templ.curr like '840' then 'USD'
	when templ.curr like '978' then 'EUR'		
end) currency
from 
(select oper.name oper,
fi.name fi_name, 
(case 
	when (t2.q='0' and t3.q='0' and t4.q='0' ) then 'electron'
	when (t2.q='1' and t3.q='0' and t4.q='0' ) then 'electronII'
	when (t2.q='0' and t3.q='1' and t4.q='0' ) then 'classic'
	when (t2.q='1' and t3.q='1' and t4.q='0' ) then 'goldVIPRN'		
	when (t2.q='0' and t3.q='0' and t4.q='1' ) then 'goldVIP'		
	when (t2.q='1' and t3.q='0' and t4.q='1' ) then 'gold'		
	when (t2.q='0' and t3.q='1' and t4.q='1' ) then 'business'
	when (t2.q='1' and t3.q='1' and t4.q='1' ) then 'electron_credit'
end) BINt,
fi.id fiid,t2.q ch2,t3.q ch3,t4.q ch4,t5.curr

 from f_i fi, 
(select  0  q from dual union select  1  q from dual) t2,
(select  0  q from dual union select  1  q from dual) t3,
(select  0  q from dual union select  1  q from dual) t4,
(select  cu.code curr , cu.name urr from CURRENCY cu 
where(cu.code='810'or cu.code='840'or cu.code='978')and cu.amnd_state='A') t5,
(select 'Communal' name from dual union select 'Telecommunication' name from dual
 union select 'SMS Fee' name from dual union select 'Перевод с карты на карту' name from dual 
 union select 'Vklad' name from dual) oper
where
	(/*Без бывших филиалов*/
	fi.id<> 110 
	and 
	fi.id<> 148 
	and 
	fi.id<> 538 
	and 
	fi.id<> 178 
	) 
and	
fi.amnd_state='A'
) templ,
(
select oper, FinInstitut, sum(transactions) transactions, sum(Oborot) Oborot,BIN,curr from 
(select 
	fi.name FinInstitut, 
	count(*) transactions, 
	sum(m.LOCAL_AMOUNT) Oborot,	
	c.curr curr,
	(case when d.source_spc in ('AK','MF','BW','DA','EA','E1','MT','NT','BE') then 'Telecommunication'
             when d.trans_type='948' then 'SMS Fee'
             when d.source_spc in ('D1','D2','D3','D4','D5','D6','O2','OG','ND','NE','NL','NG','NH','NV','O1','V0','V2','V3','V4','SB','SA','S1','S2','S3','S4','S5','S6','S7','S8','S9') then 'Communal'
             when d.source_spc='VK' then 'Vklad'
             when d.trans_type='700' then 'Перевод с карты на карту'             
        end) oper,
	(case 
		when ((d.source_number like '416084%' or d.target_number like '416084%') and sp.name like '%-CSP-%E-%') then 'electron'
		when ((d.source_number like '416084%' or d.target_number like '416084%') and sp.name like '%-CSP-%II-%') then 'electronII'
		when (d.source_number like '479789%' or d.target_number like '479789%' )then 'classic'
		when ((d.source_number like '479790%' or d.target_number like '479790%')  and sp.name like '%-CSP-%G-STVPO-%') then 'goldVIPRN'		
		when ((d.source_number like '479790%' or d.target_number like '479790%')  and sp.name like '%-CSP-%G-STVP-%') then 'goldVIP'		
		when ((d.source_number like '479790%' or d.target_number like '479790%')  and sp.name like '%-CSP-%G-%') then 'gold'		
		when (d.source_number like '479791%' or d.target_number like '479791%') then 'business'
       	when (d.source_number like '424536%' or d.target_number like '424536%') then 'electron_credit'		
       	else 'non'
	end)  BIN
from doc d, m_transaction m, ACNT_CONTRACT c, SERV_PACK sp, f_i fi
	where	
	d.AMND_DATE between to_date(:P_DATE_FROM,'DD.MM.YYYY')- 30  and to_date(:P_DATE_TO,'DD.MM.YYYY')/*Период*/ 
	and d.posting_date between to_date(:P_DATE_FROM,'DD.MM.YYYY') and to_date(:P_DATE_TO,'DD.MM.YYYY')/*Период*/
    --d.AMND_DATE between to_date('01.08.2010','DD.MM.YYYY') and to_date('30.08.2010','DD.MM.YYYY')/*Период*/ 
	--and d.posting_date between to_date('01.08.2010','DD.MM.YYYY') and to_date('30.08.2010','DD.MM.YYYY')/*Период*/
    and	d.is_authorization<>'Y'/*Не автаризации*/
    and	d.trans_amount<> 0 /*Не нулевые суммы*/
    and d.id=m.doc__oid	/*Связь d/m */
    and c.amnd_state='A' 
    and m.posting_status='P'/*Не ревёрсалы*/
    and /*Боремся с удвоением сумм*/
	(
		m.entry_type='B'
	 or m.entry_type='T'
	)
/*Фильтр - POS*/
	and	
((

	(
	d.trans_type='5'
	and 	
		(	
			d.sic_code='4814'/*сотовые*/
			or d.sic_code='4900'/*услуги*/
			)
	)
	and d.target_channel='v' /*Ниши карты*/
     and	/*Фильтр - Тип карты*/
	(
		d.target_number=c.contract_number
		and
		c.serv_pack__id=sp.id
	)
)
or	
(
	d.trans_type='182'
	and
	d.source_number=c.contract_number	
	and
	c.serv_pack__id=sp.id
	and d.target_channel='O' 

))	and/*Фильтр филиальских транзакций для m_transaction*/
    fi.id=c.f_i
group by d.source_spc, d.trans_type, fi.name, d.source_number,d.source_channel,d.target_number,sp.name,c.curr
) 
group by oper, FinInstitut, oper, BIN, curr
) data
where data.FinInstitut(+) = templ.fi_name
and data.Oper(+) = templ.oper
and data.BIN(+)=templ.BINt
and data.curr(+)=templ.curr
ORDER BY templ.curr, templ.fiid  ASC