select DT,
CODE,
SUM(Saldo) as Saldo,
SUM(SaldoEkv) as SaldoEkv,
Sum(Tr_deb) as Tr_deb,
Sum(Tr_DebEkv) as Tr_DebEkv,
Sum(Tr_Cr) as Tr_Cred,
Sum(Tr_CrEkv) as Tr_CredEkv,
ID_FINSTR,
ID_BALANCE,
ID_GROUPBAL,
ID_CLASBAL,
ID_TREST,
ID_R013,
ID_Term,
ID_R011,
ID_R011D,
Id_Sub_Bal_Etl,
ID_BRANCH,
ID_DEP,
ID_SECTOR_EC,
id_ved,
id_pers,
id_rez
from
(
select
Acc_Saltr.DT as DT,
'SLD' as CODE,
ABS(Acc_Saltr.Saldo ) as Saldo,
ABS(Acc_Saltr.SaldoEkvMy ) as SaldoEkv,
Acc_Saltr.tr_deb as Tr_deb,
Acc_Saltr.tr_debekv as Tr_debEkv,
Acc_Saltr.tr_cr as Tr_cr,
Acc_Saltr.tr_crEkv as Tr_crEkv,
Acc_Saltr.Id_Finstr as ID_FINSTR,
ACC_BAL.Id_Balance as ID_BALANCE,
ACC_BAL.ID_GROUPBAL as ID_GROUPBAL,
ACC_BAL.ID_CLASBAL as ID_CLASBAL,
decode(sign(Acc_Saltr.saldo),-1,
(select id_trest
from det_trest
where code_trest='А'),1,
(select id_trest
from det_trest
where code_trest='П'),-1) as ID_TREST,
ACC_BAL.Id_R013 as ID_R013,
nvl((select distinct trm.id_term
from con_ass_contr_acc asac,
det_contracts cn,
det_term trm
where asac.id_account=acc_bal.id_account
and asac.id_contracts=cn.id_contracts
and cn.id_source=src.id_source
and (Date_END-cn.Date_Open) between trm.DAY_FROM
and trm.Day_To),-1) as ID_Term,
ACC_BAL.ID_R011 as ID_R011,
ACC_BAL.ID_R011D as ID_R011D,
ACC_BAL.id_subject_dep_etl as Id_Sub_Bal_Etl,
ACC_BAL.ID_BRANCH as ID_BRANCH,
ACC_BAL.id_Sub_bal_etl as ID_DEP,
ACC_BAL.ID_SECTOR_EC as ID_SECTOR_EC,
ACC_BAL.ID_VED as ID_VED,
ACC_BAL.ID_PERS as ID_PERS,
ACC_BAL.ID_REZ as ID_REZ
from (
select to_date('08122007','DDMMYYYY') as dt,
b.id_finstr,b.id_account,b.id_subsys,
b.Saldo as Saldo,a.saldo as SaldoEkvMy,
decode(to_date('08122007','DDMMYYYY'),b.dt_open,b.tr_deb,0) as tr_deb,
decode(to_date('08122007','DDMMYYYY'),a.dt_open,a.tr_debekv,0) as tr_debekv,
decode(to_date('08122007','DDMMYYYY'),b.dt_open,b.tr_cr,0) as tr_cr,
decode(to_date('08122007','DDMMYYYY'),a.dt_open,a.tr_crekv,0) as tr_crekv
from fct_acount_saltran a,con_ass_acc asacc,fct_acount_saltran b
where to_date('08122007','DDMMYYYY')
between a.dt_open and a.dt_close
and to_date('08122007','DDMMYYYY')
between b.dt_open and b.dt_close
and b.id_finstr not in (select id_finstr from det_finstr where code_finstr='944')
and asacc.id_role_assacc in (select id_role_assacc
from con_role_assacc rac
where rac.code_role_assacc ='1' )
and asacc.id_account_child=a.id_account and asacc.id_account_parent=b.id_account
and to_date('08122007','DDMMYYYY') between asacc.dt_open and asacc.dt_close
union all
select to_date('08122007','DDMMYYYY') as dt,
d.id_finstr,d.id_account,d.id_subsys,
d.saldo as Saldo,d.saldo as SaldoEkvMy,
decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_deb,0) as tr_deb,
decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_debekv,0) as tr_debekv,
decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_cr,0) as tr_cr,
decode(to_date('08122007','DDMMYYYY'),d.dt_open,d.tr_crekv,0) as tr_crekv
from fct_acount_saltran d
where to_date('08122007','DDMMYYYY')
between d.dt_open and d.dt_close
and d.id_finstr in (select id_finstr from det_finstr where code_finstr='944')
) Acc_Saltr,
(select * from det_source where code_source='GK') src,
(select ACC.*,
-1 as ID_GROUPBAL,
Bal.Id_R050 as ID_R050,
-1 as ID_CLASBAL
from
(select a.*, b.id_branch,b.id_sector_ec, b.id_ved, b.id_pers,b.id_rez
from det_account a,det_subject_etl b
where is_cover='0' and a.is_gk='1'
and a.id_chapter in(select b.id_chapter from det_chapter b
where b.code_chapter in ('1','3'))
and a.id_subject_cli_etl=b.id_subject_etl)
Acc,
det_balance Bal
)ACC_BAL
where Acc_BAL.Id_Account=Acc_Saltr.Id_Account and acc_bal.id_account<>-1
and dt=to_date('08122007','DDMMYYYY')
)
group by
DT,
CODE,
ID_FINSTR,
ID_BALANCE,
ID_GROUPBAL,
ID_CLASBAL,
ID_TREST,
ID_R013,
ID_Term,
ID_R011,
ID_R011D,
Id_Sub_Bal_Etl,
ID_Branch,
ID_DEP,
id_sector_ec,
id_ved,
id_pers,
id_rez
] 
Blog
Search
Help
Members
Register
Login
Home
