PS fin stari nacin

delete tHE_AcctTransItem where acKey = '1849000000001'
delete tHE_AcctTransItem where acKey = '1940000000001'

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)

-- po subjektima
insert into #t
select p.acAcct, p.acSubject, '', '', 'PS 2019', 'PS 2019', '', 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 '2018-01-01' and '2018-12-31'
and p.acAcct in (select acAcct from tDE_SetAccount where acSubject = 'T')
and left(p.acAcct,1) not in ('5','6')
group by p.acAcct, p.acSubject
having sum(p.anDebit-p.anCredit) <> 0

--Samo konta
insert into #t
select p.acAcct, '', '', '', 'PS 2019', 'PS 2019', '', 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 '2018-01-01' and '2018-12-31'
and p.acAcct in (select acAcct from tDE_SetAccount where acSubject = 'F')
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 '1940000000001' 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, '2019-01-01', '2019-01-01', '2019-01-01'
from #t

update #t
set acKey = '1849000000001'

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, '2018-12-31', '2018-12-31', '2018-12-31'
from #t

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

--Prvo otvoriti dokument zakljucka i otvaranja PS

insert into tHE_CloseOpenAcctTrans (acKey, anNo, anType, anTypeTrans, acKeyCloseAcctTrans, anStatus, acKeyOpen, anNoOpen)
values ('19ZAK00000001', 1, 128, 1, '1940000000001', 0, '19ZAK00000001', 1)