INSERT INTO REPORT_SQL (COMPUTER, STR1, NUM1, NUM2, STR2, DATE1, STR3, NUM3, NUM4, STR4, NUM5, DATE2, DATE3, DATE4, NUM9, STR20)
SELECT 'SASA',
       '1',
       D.idpog,
       D.sfvp,
       V.krt || ' ISIN ' || V.isin,
       D.dan,
       N.Nzv,
       D.lot,
       case when D.IDDOG<-999999999 then ROUND(D.GlvTcj / (case D.TcjVlt when null then 1 
                                                                         when 0 then 1 
                                                                         else D.TcjVlt 
                                                           end), 4) 
            else ROUND(D.GlvTcj * DECODE(SIGN(NVL(D.TcjVlt, 0)), 0, 0, power(D.TcjVlt, DECODE(SIGN(D.Dan - TO_DATE('01/01/2007','mm/dd/yyyy')), -1 , 1 , -1 ))), 4) 
       end,
       T.Krt,
       case when V.SfGlvEnt='%' then (ROUND(((((D.Lot * case when D.IDDOG<-999999999 then (1/(case D.TcjVlt when null then 1 
                                                                                                            when 0 then 1 
                                                                                                            else D.TcjVlt 
                                                                                              end)) 
                                                             else DECODE(SIGN(NVL(D.TcjVlt, 0)), 0 , 0 , power(D.TcjVlt, DECODE(SIGN(D.Dan - TO_DATE('01/01/2007','mm/dd/yyyy')), -1 , 1 , -1 ))) 
                                                        end * D.GlvTcj * ((DECODE(K.Glv, null, 0, K.Glv))))/100 + D.Lot * ((DECODE(D.ObrVlt, null, 0, D.ObrVlt)) + (DECODE(D.RevVlt, null, 0, D.RevVlt)))))), 2)) 
            else ROUND((case when D.IDDOG<-999999999 then (D.Lot * D.GlvTcj / (case D.TcjVlt when null then 1 
                                                                                             when 0 then 1 
                                                                                             else D.TcjVlt 
                                                                               end)) 
                             else (D.Lot * D.GlvTcj * DECODE(SIGN(NVL(D.TcjVlt, 0)), 0, 0, power(D.TcjVlt, DECODE(SIGN(D.Dan - TO_DATE('01/01/2007','mm/dd/yyyy')), -1, 1, -1)))) 
                        end), 2) 
       end,
       '01.01.2018',
       '31.12.2018',
       ADD_MONTHS(LAST_DAY('31.12.2018'), 2),
       CASE WHEN D.sfpdg in('a','-','{','P','0','w','2','b') then 1 ELSE 0 END,
       CASE WHEN nvl(P.koda_jezika,'SLO') <> 'ENG' THEN 'SLO' ELSE 'ENG' END
FROM dogs D
JOIN SIFGRP G ON G.Sif = D.SfPdg AND G.Ssf = 'PDG' and G.SsfGrp = 'PDT'
JOIN SIF N ON N.Sif = G.SifGrp AND n.ssf='PDT'
JOIN sfvp V ON D.sfvp=V.sfvp 
JOIN vlt T ON D.SfGlvEnt=T.sfvlt
JOIN pog P ON D.idpog=P.idpog
LEFT OUTER JOIN KPN K on D.sfvp=k.sfvp and D.Dan>=K.PRVDAN and D.Dan<=K.ZDNDAN 
WHERE sim='DAVKI18' --|| TO_CHAR('31.12.2018', 'YY')
AND V.SfGlvEnt='%'
--and D.sfdjv='N'
order by D.idpog, v.sfvp, n.nzv, d.dan, d.lot;


select * from dogs where sim = 'DAVKI18' and sfdjv = 'N';
-- Transakcije za lastniške VP
-- STR1  oznaka tabele 1, 2 ali 3
-- NUM1  IdPog
-- NUM2  SfVp
-- STR2  Finančni instrument (krt + isin)
-- DATE1 Datum transakcije
-- STR3  Naziv transakcije
-- NUM3  Količin (loti)
-- NUM4  Cena
-- STR4  Valuta
-- NUM5  Vrednost v EUR
-- DATE2 Dan od
-- DATE3 Dan do
-- DATE4 Zadnji dan februarja naslednje leto
-- NUM9  Indikator prodaj
-- STR20 Jezik izpisa (SLO/ENG)
INSERT INTO REPORT_SQL (COMPUTER, STR1, NUM1, NUM2, STR2, DATE1, STR3, NUM3, NUM4, STR4, NUM5, DATE2, DATE3, DATE4, NUM9, STR20)
SELECT 'SASA',
       '1',
       D.idpog,
       D.sfvp,
       V.krt || ' ISIN ' || V.isin,
       D.dan,
       N.Nzv,
       D.lot,
       case when D.IDDOG<-999999999 then ROUND(D.GlvTcj / (case D.TcjVlt when null then 1 
                                                                         when 0 then 1 
                                                                         else D.TcjVlt 
                                                           end), 4) 
            else ROUND(D.GlvTcj * DECODE(SIGN(NVL(D.TcjVlt, 0)), 0, 0, power(D.TcjVlt, DECODE(SIGN(D.Dan - TO_DATE('01/01/2007','mm/dd/yyyy')), -1 , 1 , -1 ))), 4) 
       end,
       T.Krt,
       ROUND((case when D.IDDOG<-999999999 then (D.Lot * D.GlvTcj / (case D.TcjVlt when null then 1 
                                                                                             when 0 then 1 
                                                                                             else D.TcjVlt 
                                                                               end)) 
                             else (D.Lot * D.GlvTcj * DECODE(SIGN(NVL(D.TcjVlt, 0)), 0, 0, power(D.TcjVlt, DECODE(SIGN(D.Dan - TO_DATE('01/01/2007','mm/dd/yyyy')), -1, 1, -1)))) 
                        end), 2),
       '01.01.2018',
       '31.12.2018',
       ADD_MONTHS(LAST_DAY('31.12.2018'), 2),
       CASE WHEN D.sfpdg in('a','-','{','P','0','w','2','b') then 1 ELSE 0 END,
       CASE WHEN nvl(P.koda_jezika,'SLO') <> 'ENG' THEN 'SLO' ELSE 'ENG' END
FROM dogs D
JOIN SIFGRP G ON G.Sif = D.SfPdg AND G.Ssf = 'PDG' and G.SsfGrp = 'PDT'
JOIN SIF N ON N.Sif = G.SifGrp AND n.ssf='PDT'
JOIN sfvp V ON D.sfvp=V.sfvp 
JOIN vlt T ON D.SfGlvEnt=T.sfvlt
JOIN pog P ON D.idpog=P.idpog
WHERE sim='DAVKI18' 
AND V.SfGlvEnt<>'%'

order by D.idpog, v.sfvp, n.nzv, d.dan, d.lot
;

-- Stanje
-- STR1  oznaka tabele 1, 2 ali 3
-- NUM1  IdPog
-- NUM2  SfVp
-- STR2  Finančni instrument (krt + isin)
-- STR4  tekst 'lot'
-- NUM3  Količina (lot)
-- NUM4  Vrednost v EUR
-- DATE2 Dan od
-- DATE3 Dan do
-- DATE4 Zadnji dan februarja naslednje leto
-- STR20 Jezik izpisa (SLO/ENG)
INSERT INTO REPORT_SQL (COMPUTER, STR1, NUM1, NUM2, STR2, STR4, NUM3, NUM4, DATE2, DATE3, DATE4, STR20)
SELECT 'SASA',
       '2',
       vpshst.idpog,
       vpshst.sfvp,
       V.krt || ' ISIN ' || V.isin,
       'lot',
       vpshst.lot,
       vpshst.lot*(vpshst.glv+vpshst.obr),
       '01.01.2018',
       '31.12.2018',
       ADD_MONTHS(LAST_DAY('31.12.2018'), 2),
       CASE WHEN nvl(P.koda_jezika,'SLO') <> 'ENG' THEN 'SLO' ELSE 'ENG' END
FROM vpshst
LEFT OUTER JOIN pog P ON vpshst.idpog=P.idpog
LEFT OUTER JOIN sfvp V ON vpshst.sfvp=V.sfvp
WHERE vpshst.dan='31.12.2018'
AND vpshst.sfsvp='L'
AND vpshst.sflst <> 'G'
AND P.sfdjv in ('N')
AND (P.danprp IS NULL OR P.danprp > '31.12.2018')
--? AND P.idpog= {?Idpog}
order by p.idpog, v.krt
; 

-- Stroški / Donos
-- STR1  oznaka tabele 1, 2 ali 3
-- NUM1  IdPog
-- NUM10 Zač. vred. portfelja VSI VP
-- NUM11 Kon. vred. portfelja VSI VP
-- NUM23 Neto vplačila VSI VP
-- NUM24 Neto izplačila VSI VP
-- NUM12 Skupaj stroški VSI VP
-- NUM27 Donos brez str. VSI VP
-- NUM28 % Donos brez str. VSI VP
-- NUM25 Donos s str. VSI VP
-- NUM26 % Donos s str. VSI VP
-- DATE2 Dan od
-- DATE3 Dan do
-- DATE4 Zadnji dan februarja naslednje leto
-- STR20 Jezik izpisa (SLO/ENG)
insert into report_sql (COMPUTER, STR1, NUM1, NUM10, NUM11, NUM23, NUM24, NUM12, NUM27, NUM28, NUM25, NUM26, DATE2, DATE3, DATE4, STR20)
SELECT 'SASA',
       '3',
       idpog,
       zacVPstari,
       konVPstaro,
       promstari,
       vpmstari,
       str,
       doneVPbstari,
       CASE WHEN zacVPstari+promstari=0 THEN 0
            WHEN zacVPstari=0 THEN round(doneVPbstari/promstari*100,2)
                         ELSE round(doneVPbstari/(zacVPstari+promstari)*100,2)
       END, 
       doneVPstari,
       CASE WHEN zacVPstari+promstari=0 THEN 0
            WHEN zacVPstari=0 THEN round(doneVPstari/promstari*100,2)
                         ELSE round(doneVPstari/(zacVPstari+promstari)*100,2)
       END,
       '01.01.2018',--'01.01.2018', -- DATE2
       '31.12.2018',--'31.12.2018', -- DATE3
       ADD_MONTHS(LAST_DAY('31.12.2018'), 2),
       CASE WHEN nvl(koda_jezika,'SLO') <> 'ENG' THEN 'SLO' ELSE 'ENG' END
FROM (
SELECT idpog,
       str, 
       konVPstaro-(vpmstari-div)-promstari-zacVPstari-str doneVPstari, 
       konVPstaro-(vpmstari-div)-promstari-zacVPstari doneVPbstari, 
       zacVPstari,
       vpmstari-div vpmstari, 
       konVPstaro, 
       promstari,
       koda_jezika
FROM (
SELECT P.idpog, 
       p.nzv,
       nvl((SELECT round(nvl(SUM((CASE WHEN v.sfvp IN (0) THEN 0 ELSE v.lot END)*(v.glv+v.obr)),0),2) 
            FROM vpshst V WHERE V.idpog=P.idpog AND V.dan='01.01.2018' AND v.sfsvp='L' AND v.sflst<>'G'),0) zacVPstari,
       nvl((SELECT round(nvl(SUM((CASE WHEN v.sfvp in (0) THEN 0 ELSE v.lot END)*(v.glv+v.obr)),0),2) 
            FROM vpshst V WHERE V.idpog=P.idpog AND V.dan='31.12.2018' AND v.sfsvp='L' AND v.sflst<>'G'),0) konVPstaro,
       (nvl((SELECT SUM(D.lot) FROM dog D 
             WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' AND D.sfpdg='S' AND D.stn IS NULL),0)) +
            (nvl((SELECT SUM(D.lot) FROM dog D 
                  WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' AND D.sfpdg='$' AND D.brezsto='D'),0)) str,
       (nvl((SELECT SUM(round(d.lot*(D.GLVVLT+D.OBRVLT+D.REVVLT) / CASE WHEN (nvl(D.tcjvlt,0)=0 OR D.sfvlt='E') THEN 1 ELSE D.tcjvlt END,2))
             FROM dog D 
             WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' AND D.sfpdg='='),0)) -
            (nvl((SELECT SUM(D.lot) FROM dog D 
                  WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' 
                  AND D.iddog IN (SELECT iddogdetail FROM doglnk WHERE sflnk='R')),0)) div,
       nvl((SELECT round(nvl(SUM((CASE WHEN NVL(D.GLVTCJ,0)=0 AND nvl((SELECT nvl(p.breztecaja,'N') FROM psl p WHERE p.idpsl=d.idpsl),'N') <> 'D'
            THEN CASE WHEN D.sfglvent='%' THEN (CASE WHEN d.sfvp IN (0) 
            THEN 0 ELSE d.vp END)*(nvl(ibi_utils.GetTcn_dorsum(D.sfvp, d.dan),0)/100*(SELECT glv FROM kpn WHERE sfvp=D.sfvp AND 
            sfkpn=(SELECT max(sfkpn) FROM kpn WHERE sfvp=d.sfvp and prvdantrg <= D.Dan))+D.OBRVLT+D.REVVLT)
            ELSE (CASE WHEN d.sfvp in (0) THEN 0 ELSE d.vp END)*(nvl(ibi_utils.GetTcn_dorsum(D.sfvp, D.dan),0)) END
            ELSE (CASE WHEN d.sfvp in (0) THEN 0 ELSE d.vp END)*(D.GLVVLT+D.OBRVLT+D.REVVLT)
            END)/ CASE WHEN (nvl(D.tcjvlt,0)=0 OR D.sfvlt='E') THEN 1 ELSE D.tcjvlt END),0),2) 
            FROM dog D WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' AND D.vp > 0
            AND (d.sfpdg NOT IN ('2','3') OR d.sfpdg IN ('2','3') AND d.idnal NOT IN 
            (SELECT spltinfo.idbtc FROM spltinfo JOIN btc ON spltinfo.idbtc=btc.idbtc WHERE btc.sfbtc='R'))),0) promstari,
       nvl((SELECT round(nvl(SUM((CASE WHEN NVL(D.GLVTCJ,0)=0 AND nvl((SELECT nvl(p.breztecaja,'N') FROM psl p WHERE p.idpsl=d.idpsl),'N') <> 'D'
            THEN CASE WHEN D.sfglvent='%' THEN (CASE WHEN d.sfvp IN 
           (0) THEN 0 ELSE d.vp END)*(nvl(ibi_utils.GetTcn_dorsum(D.sfvp, d.dan),0)/100*
                 (SELECT glv FROM kpn WHERE sfvp=D.sfvp AND sfkpn=(SELECT MAX(sfkpn) FROM kpn WHERE sfvp=d.sfvp AND prvdantrg <= D.Dan))+D.OBRVLT+D.REVVLT)
            ELSE (CASE WHEN d.sfvp IN (0) THEN 0 ELSE d.vp END)*(nvl(ibi_utils.GetTcn_dorsum(D.sfvp, D.dan),0)) END
            ELSE (CASE WHEN d.sfvp IN (0) THEN 0 ELSE d.vp END)*(D.GLVVLT+D.OBRVLT+D.REVVLT)
            END)/ CASE WHEN (nvl(D.tcjvlt,0)=0 OR D.sfvlt='E') THEN 1 ELSE D.tcjvlt END),0),2) 
            FROM dog D WHERE D.idpog=P.idpog AND D.dan BETWEEN '01.01.2018' AND '31.12.2018' AND D.vp < 0
            AND (d.sfpdg NOT IN ('2','3') OR d.sfpdg IN ('2','3') AND d.idnal NOT IN 
            (SELECT spltinfo.idbtc FROM spltinfo JOIN btc ON spltinfo.idbtc=btc.idbtc WHERE btc.sfbtc='R'))),0) VPMstari,
       P.koda_jezika koda_jezika
FROM pog P
WHERE P.sfdjv in ('N')
AND (P.danprp IS NULL OR P.danprp > '31.12.2018')
--? AND P.idpog= {?Idpog}
GROUP BY P.idpog, p.nzv, P.koda_jezika
))
WHERE
abs(zacvpstari) + abs(promstari)+ abs(konvpstaro) + abs (vpmstari)+ abs(str) <> 0
;

vsi 4 inserti iz report_sql :

0 rows inserted.
7.748 rows inserted.
31.635 rows inserted.
19.033 rows inserted.


preverim še:

sselect * from report_sql r where r.computer = 'SASA' --58416

select num1 from report_sql r where r.computer = 'SASA' 
group by num1--19893 (= enako kot preko avtomatski izpisov)


--sedaj zgornjemu dodam še povezavo na pog in primerjam rezultat z rezultatom vseh paketov po 1500  za dejavnost N
select pog.idpog, pog.nzv, pog.krtkdd from 
(select num1 from report_sql r where r.computer = 'SASA' 
group by num1) a, pog
where a.num1=pog.idpog
order by pog.nzv,pog.idpog, pog.krtkdd

---vrne 19893 zapisov, ki jih kopiram v excel