Fin PS - stari nacin

delete tHE_AcctTransItem where acKey = '1840000000001'
delete tHE_AcctTransItem where acKey = '1749000000001'

alter table tHE_AcctTransItem disable trigger all

update tHE_AcctTransItem
set acKeyCloseAcctTrans = null
where acKey = '1749000000001'

------------------------------------------------------------------------

if object_id('tempdb..#t') is not null
drop table #t

create table #t
(acAcct char (13),
acSubject char (30),
acDept char (30),
acCostDrv char (16),
acDoc varchar (35),
acLinkDoc varchar (35),
acLnkKey char (13),
anFXRate decimal(12,6),
acVAT char (1),
acCurrency char (3),
SaldoKM money,
SaldoVal money)


-- analiticki
insert into #t
select p.acAcct, p.acSubject, '', '', 'PS', 'PS', '', 1, 'F', 'KM',
sum(p.anDebit-p.anCredit) as SaldoKM, sum(p.anDebit-p.anCredit) as SaldoVal
from tHE_AcctTrans g
inner join tHE_AcctTransItem p on g.acKey = p.acKey
where g.adDate between '2017-01-01' and '2017-12-31'
and p.acAcct in (select acAcct from tHE_AcctTransItem where acKey like '17400%' and acSubject not like '' group by acAcct)
and left(p.acAcct,1) not in ('5','6')
group by p.acAcct, p.acSubject
having sum(p.anDebit-p.anCredit) <> 0

--ostalo
insert into #t
select p.acAcct, '', '', '', 'PS', 'PS', '', 1, 'F', 'KM',
sum(p.anDebit-p.anCredit) as SaldoKM, sum(p.anDebit-p.anCredit) as SaldoVal
from tHE_AcctTrans g
inner join tHE_AcctTransItem p on g.acKey = p.acKey
where g.adDate between '2017-01-01' and '2017-12-31'
and p.acAcct not in (select acAcct from tHE_AcctTransItem where acKey like '17400%' and acSubject not like '' group by acAcct)
and left(p.acAcct,1) not in ('5','6')
group by p.acAcct
having sum(p.anDebit-p.anCredit) <> 0


alter table #t
add anDebit money default 0 not null,
anCredit money default 0 not null,
anValDebit money default 0 not null,
anValCredit money default 0 not null

update #t
set anDebit = SaldoKM
where SaldoKM > 0

update #t
set anCredit = SaldoKM * -1
where SaldoKM < 0

update #t
set anValDebit = SaldoVal
where SaldoVal > 0

update #t
set anValCredit = SaldoVal * -1
where SaldoVal < 0

alter table #t
add acKey char (13) default '1840000000001' not null,
anNo int identity (1,1) not null


insert into tHE_AcctTransItem (acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVATPayed, acCurrency, anDebit, anCredit, anValDebit, anValCredit, adDateDoc, adDateDue, adDateVAT)
select acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVAT, acCurrency, anDebit, anCredit, anValDebit, anValCredit, '2018-01-01', '2018-01-01', '2018-01-01'
from #t

update #t
set acKey = '1749000000001'

insert into tHE_AcctTransItem (acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVATPayed, acCurrency, anDebit, anCredit, anValDebit, anValCredit, adDateDoc, adDateDue, adDateVAT)
select acKey, anNo, acAcct, acSubject, acDept, acCostDrv, acDoc, acLinkDoc, acLnkKey, anFXRate, acVAT, acCurrency, anCredit, anDebit, anValCredit, anValDebit, '2017-12-31', '2017-12-31', '2017-12-31'
from #t