select 

decode(d.is_authorization, 'Y'/*AUTH*/, d.trans_date, d.trans_date) as TRANS_DATE,
       substr(trim(ac.contract_number), 1, 4) || '********' ||
       substr(trim(ac.contract_number), 13, 4) as N_êàðòî÷êè,
       cu.name as Âàëþòà_êîíò,
       p.s,
       cu2.name as Âàëþòà_òðàíç,
       d.trans_amount as ñóììà_òðàíç,
       rc.resp_text,
       co.name as Ñòðàíà,
       d.trans_city as Ãîðîä,
       d.trans_details ,
       s.name as SICCODE,
       cl.short_name,
       ac.service_group as Êîä_ôèëèàëà,
       ct.name as Òèï_êëèåíòà,
       cl.client_number as ÈÈÍ,
       tt.name as Òèï_òðàíçàêöèè,
       round(d.trans_amount / 1000, 2) t_am,
       round((case d.settl_curr
               when '398' then
                d.settl_amount / fxr.cb_rate
               when '978' then
                d.settl_amount * fxeur.cb_rate / fxr.cb_rate
               else
                d.settl_amount
             end),
             2) Am_USD,
       round(sum(case d.settl_curr
                   when '398' then
                    d.settl_amount / fxr.cb_rate
                   when '978' then
                    d.settl_amount * fxeur.cb_rate / fxr.cb_rate
                   else
                    d.settl_amount
                 end) over(partition by ac.client__id),
             2) sum_USD
  from ows.acnt_contract ac
  join ows.client cl on ac.client__id = cl.id
                    and cl.amnd_state = 'A'
                    and cl.clt in 
  join ows.doc d on ac.contract_number = d.target_number
                and d.source_channel in ('v', 'V', 'O')
                and d.amnd_state = 'A'
                and d.trans_date >= to_date('29.06.2018', 'dd.mm.yyyy')
                and d.trans_date <= to_date('03.08.2018', 'dd.mm.yyyy')-- + 1
                and d.posting_status = 'P'
                and d.fx_settl_date > to_date('1990-01-01', 'yyyy-mm-dd')
                and d.id > 0
                and d.trans_type in (5, 11, 13, 50)
  join ows.acnt_contract acc on ac.acnt_contract__oid = acc.id
                            and acc.amnd_state = 'A'
  join ows.currency cu on cu.code = ac.curr
                      and cu.amnd_state = 'A'
  join ows.currency cu2 on cu2.code = d.trans_curr
                       and cu2.amnd_state = 'A'
  join ows.sic s on s.code = d.sic_code
                and s.amnd_state = 'A'
  join ows.resp_code rc on d.return_code = rc.resp_code
                       and rc.amnd_state = 'A'
  join ows.country co on co.code = d.trans_country
                     and co.name <> 'Kazakhstan'
                     and co.amnd_state = 'A'
  left join ows.CREDIT_HISTORY ch on ch.doc__id = d.id
  join ows.client_type ct on ct.id = cl.clt
                         and ct.amnd_state = 'A'
                         and ct.id in (6, 10)
  left join ows.trans_type tt on d.trans_type = tt.id
                             and tt.amnd_state = 'A'
  join ows.m_transaction mt on d.id = mt.doc__oid
                           and mt.posting_status = 'P'
                           and mt.id > 0
                           and mt.service_class = 'T'
  left join ows.fx_rate fxr on mt.fx_seqv_n = fxr.seqv_n
                           and fxr.code = 840
  left join ows.fx_rate fxeur on mt.fx_seqv_n = fxeur.seqv_n
                             and fxeur.code = 978
  left join (select abs(den.AMOUNT) s,
                    d1.doc__prev__id d,
                    den.ACNT_CONTRACT__ID num
               from ows.doc_entry_n den
               join ows.doc d1 on den.DOC__OID = d1.id
                              and d1.amnd_state = 'A') p on p.num = ac.id
                                                        and p.d = d.id
 where ac.amnd_state = 'A'
   and ac.con_cat = 'C'
   and (ac.service_group = '*' or '*' = '*')
 order by 1

 and (ac.service_group = '*' or '*' = '*')