Izvēlne
lv:procedures
Šī ir veca dokumenta versija!
ALTER PROCEDURE dbo.int_hooldus_klient_pvn @aeg1 datetime, @aeg2 datetime AS --static vars DECLARE @vat_declar_name nvarchar(200) SET @vat_declar_name = 'PVN' DECLARE @stat_vat DECIMAL SET @stat_vat = 21 DECLARE @lim DECIMAL SET @lim = ISNULL((SELECT TOP 1 CAST(ISNULL(kontod, 1000) AS DECIMAL) FROM fin_aru_kaive_read WITH (NOLOCK) WHERE NUMBER=9000 AND kood=@vat_declar_name AND tyyp = 3), 1000) DECLARE @account INT SET @account = 57211 DECLARE @i INT SET @i = 1 DECLARE @x nvarchar(10) SET @x = '1' DECLARE @y nvarchar(10) SET @y = '1' --numbers helper DECLARE @numbers TABLE (n INT) INSERT INTO @numbers SELECT TOP 1000 ROW_NUMBER() OVER(ORDER BY t1.number) FROM master..spt_values t1 CROSS JOIN master..spt_values t2 --vat summary DECLARE @vat_declar_sums TABLE ( row_nr INT, row_type INT, c_class INT, vat_code nvarchar(30), nr nvarchar(30), description nvarchar(200), range_formula nvarchar(200), doc_sum DECIMAL(15,2) ) INSERT INTO @vat_declar_sums SELECT ROW_NUMBER() OVER (ORDER BY kood), tyyp, kontoklass, kmkood, NUMBER, tekst, kontod, 0 FROM fin_aru_kaive_read WITH (NOLOCK) WHERE kood=@vat_declar_name AND (tyyp = 1 OR tyyp = 2 OR tyyp = 8 OR tyyp = 7) ORDER BY NUMBER --account range elements DECLARE @elements TABLE (nr nvarchar(30), e nvarchar(200)) INSERT INTO @elements SELECT nr, RTRIM(LTRIM(SUBSTRING('+' + range_formula + '+',n+1,CHARINDEX('+','+' + range_formula + '+',n+1)-n-1))) FROM @numbers, @vat_declar_sums WHERE n < LEN('+' + range_formula + '+') AND SUBSTRING('+' + range_formula + '+',n,1) = '+' AND range_formula LIKE '_%' AND (row_type = 1 OR row_type = 8 OR row_type = 7) --account ranges start-end DECLARE @element_ranges TABLE (row_nr INT, nr nvarchar(30), e_start nvarchar(200), e_end nvarchar(200)) INSERT INTO @element_ranges SELECT ROW_NUMBER() OVER (ORDER BY nr), nr, CASE WHEN CHARINDEX(':', e) > 0 THEN LEFT(e, CHARINDEX(':', e)-1) ELSE e END, CASE WHEN CHARINDEX(':', e) > 0 THEN SUBSTRING(e, CHARINDEX(':', e)+1, LEN(e)) ELSE e END FROM @elements --update vat declaration sums according to account ranges WHILE @i <= (SELECT COUNT(row_nr) FROM @element_ranges) BEGIN SET @x = (SELECT (CASE WHEN ISNUMERIC(er.e_start) = 1 THEN er.e_start ELSE '0' END) FROM @element_ranges er WHERE er.row_nr = @i) SET @y = (SELECT (CASE WHEN ISNUMERIC(er.e_end) = 1 THEN er.e_end ELSE '0' END) FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 1 AND c_class = 3 AND row_type=1 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1deebet, 0)) - SUM(ISNULL(baas1kreedit, 0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 0 AND c_class = 3 AND row_type=1 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) - SUM(ISNULL(baas1deebet, 0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 1 AND c_class = 4 AND row_type=1 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) - SUM(ISNULL(baas1deebet, 0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 0 AND c_class = 4 AND row_type=1 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) + SUM(isnull(baas1deebet * (-1),0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND (baas1kreedit > 0 OR baas1deebet < 0) ),0) WHERE ISNUMERIC(vat_code) = 0 AND c_class = 4 AND row_type=8 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) + SUM(isnull(baas1deebet * (-1),0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND (baas1kreedit > 0 OR baas1deebet < 0) AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 1 AND c_class = 4 AND row_type=8 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) + SUM(isnull(baas1deebet * (-1),0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND (baas1kreedit < 0 OR baas1deebet > 0) ),0) WHERE ISNUMERIC(vat_code) = 0 AND c_class = 3 AND row_type=7 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(ISNULL(baas1kreedit, 0)) + SUM(isnull(baas1deebet * (-1),0)) FROM fin_kanded_read WITH (NOLOCK) WHERE CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y AND (baas1kreedit < 0 OR baas1deebet > 0) AND kmkood = vat_code ),0) WHERE ISNUMERIC(vat_code) = 1 AND c_class = 3 AND row_type=7 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) UPDATE @vat_declar_sums SET doc_sum = doc_sum + ISNULL(( SELECT SUM(kaive/ISNULL(kurss_e,1)) FROM int_konto_ajalugu WITH (NOLOCK) WHERE CAST(aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto BETWEEN @x AND @y ),0) WHERE ISNUMERIC(vat_code) <> 1 AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) AND row_type=1 SET @i = @i + 1 END ----------------- ------ 1-1 ------ ----------------- SET nocount ON DECLARE @pvn13_vat_codes_1_1 TABLE ( code nvarchar(30), rate DECIMAL, proportion DECIMAL, description nvarchar(200), sales_account nvarchar(30), purchase_account nvarchar(30), info nvarchar(200), dar_veids nvarchar(5), do_limit VARCHAR(2) ) INSERT INTO @pvn13_vat_codes_1_1 SELECT kood, ilmakm, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_3' AND klass='kmk'), '41') AS nvarchar(10)) AS dar_veids, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS VARCHAR(2)) AS do_limit FROM fin_kmkoodid fk WHERE lisainfo LIKE '%pvn13%' DECLARE @pvn11_vat_codes TABLE ( code nvarchar(30), rate DECIMAL, reverse_rate DECIMAL, proportion DECIMAL, description nvarchar(200), sales_account nvarchar(30), purchase_account nvarchar(30), info nvarchar(200), dar_veids nvarchar(5), do_limit VARCHAR(2) ) INSERT INTO @pvn11_vat_codes SELECT kood, ilmakm, poord, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_1' AND klass='kmk'), 'I') AS nvarchar(10)) AS dar_veids, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS VARCHAR(2)) AS do_limit FROM fin_kmkoodid fk WHERE lisainfo LIKE '%pvn11%' DECLARE @pvn1_1_temp TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), directo_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_date nvarchar(30), vatc_prepayment DECIMAL(15,2) ) DECLARE @pvn1_1_r_rows TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), directo_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_date nvarchar(30), vatc_prepayment DECIMAL(15,2) ) DECLARE @pvn1_1_values_by_doc TABLE ( rn INT, dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), directo_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_date nvarchar(30), doc_sum_total DECIMAL(15,2), vatc_prepayment DECIMAL(15,2) ) DECLARE @pvn1_1_top_sums_supplier TABLE ( vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2) ) DECLARE @pvn1_1_sums_supplier TABLE ( vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2) ) DECLARE @pvn1_1_top_table TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), directo_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_date nvarchar(30), rn INT ) DECLARE @pvn1_1_top_r_table TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), directo_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_date nvarchar(30) ,rn INT ) DECLARE @pvn_1_1_v_totals TABLE ( dok_veids nvarchar(32), client_name nvarchar(30), vat_reg_nr nvarchar(32), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2) ) DECLARE @pvn_1_1_v_totals2 TABLE ( dok_veids nvarchar(32), client_name nvarchar(30), vat_reg_nr nvarchar(32), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2) ) DECLARE @pvn_1_1_t_totals TABLE ( doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), doc_nr nvarchar(255) ) DECLARE @pvn_1_1_totals TABLE ( doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2) ) INSERT @pvn1_1_temp --sales invoices SELECT (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS do_limit, CASE WHEN (isnull(ettemaks,0) = 0) THEN '1' ELSE '1' END AS dok_veids, mr_arved_read.number AS doc_nr, mr_arved_read.number AS doc_nr, kmk AS vat_code, CAST((klient_nimi) AS nvarchar(30)) AS client_name, CAST(CASE WHEN (kmregnumber IS NOT NULL OR kmregnumber!=' ') THEN kmregnumber ELSE (SELECT top 1 kmregnr FROM kliendid WHERE kood=mr_arved.klient_kood)END AS nvarchar(30)) AS vat_reg_nr, CAST((ROUND(ISNULL(summa, 0), 4)) AS DECIMAL(15,2)) AS doc_sum, isnull(CAST((ROUND(ISNULL(summa, 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS DECIMAL(15,2)),'0.00') AS doc_vat, REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date, (SELECT SUM(summa) FROM mr_arved_ettemaksud mae WHERE mae.arve=mr_arved_read.number AND mae.kmk=mr_arved_read.kmk) FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number WHERE CAST(aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (kinnitatud = 1) AND kmk IN (SELECT code FROM @pvn11_vat_codes) AND artikkel IS NOT NULL AND ((ISNULL(NULLIF(kredarve, ''), '') = '') OR (CAST((SELECT TOP 1 ma_inner.aeg FROM mr_arved ma_inner WHERE ma_inner.number = mr_arved.kredarve) AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE))) UNION ALL SELECT (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes_1_1 vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn13_vat_codes_1_1 vc WHERE vc.code = mr_arved_read.kmk) AS do_limit, CASE WHEN (isnull(ettemaks,0) = 0) THEN '4' ELSE 'Z' END AS dok_veids, mr_arved_read.number AS doc_nr, mr_arved_read.number AS doc_nr, kmk AS vat_code, CAST((klient_nimi) AS nvarchar(30)) AS client_name, CAST(CASE WHEN (kmregnumber IS NOT NULL OR kmregnumber!=' ') THEN kmregnumber ELSE (SELECT top 1 kmregnr FROM kliendid WHERE kood=mr_arved.klient_kood)END AS nvarchar(30)) AS vat_reg_nr, CAST((ROUND(ISNULL(summa * (-1), 0), 4)) AS DECIMAL(15,2)) AS doc_sum, isnull(CAST((ROUND(ISNULL(summa * (-1), 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes_1_1 WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS DECIMAL(15,2)),'0.00') AS doc_vat, REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date, (SELECT SUM(summa) * (-1) FROM mr_arved_ettemaksud mae WHERE mae.arve=mr_arved_read.number AND mae.kmk=mr_arved_read.kmk) FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number WHERE CAST(aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (kinnitatud = 1) AND kmk IN (SELECT code FROM @pvn13_vat_codes_1_1) AND artikkel IS NOT NULL AND (kredarve IS NOT NULL OR mr_arved.kokku <=0 ) --AND ((ISNULL(NULLIF(kredarve, ''), '') = '') OR (CAST((SELECT TOP 1 ma_inner.aeg FROM mr_arved ma_inner WHERE ma_inner.number = mr_arved.kredarve) AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date))) INSERT @pvn1_1_temp --purchase invoices SELECT isnull((SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood),'A') AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood) AS do_limit, '1', CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr, -- CAST(oa.number AS nvarchar(30)) AS client_doc_nr, CAST(oa.number AS nvarchar(30)) AS client_doc_nr, CAST(oar.kmkood AS nvarchar(30)) AS vat_code, CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood = oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr, --CAST(isnull(oar.a_summa,oar.summa) * ISNULL(oa.kurssbv1, 1) AS decimal(15,2)) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 AND oar.konto!=@account) THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1) AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 AND (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 AND oar.konto!=@account) THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1)AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 AND oar.konto!=@account) THEN CAST((oar.a_summa * ISNULL(oa.kurssbv1, 1)) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0) / 100) AS DECIMAL(15,4)) WHEN (oar.konto=@account) THEN 0 END) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 AND oar.konto!=@account) THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 AND (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) = 0 AND oar.konto!=@account) THEN --CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS decimal(15,4)) CAST(km AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) != 0 AND oar.konto!=@account) THEN CAST(oar.km * ISNULL(oa.kurssbv1, 1) AS DECIMAL(15,4)) /* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0)) / 100)) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), 0) / 100) */ WHEN (oar.konto=@account) THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1) AS DECIMAL(15,4)) END) AS doc_vat, REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-') AS doc_date, (SELECT SUM(summa) FROM or_arved_ettemaksud mae WHERE mae.arve=oar.number AND mae.kmk=oar.kmkood) FROM or_arved oa, or_arved_read oar WITH (NOLOCK) WHERE CAST(oa.kande_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (oa.kinnitatud = 'True') AND (oa.number = oar.number) -- AND NOT (oar.konto = CAST(@account AS nvarchar)) AND (oar.kmkood IN (SELECT code FROM @pvn11_vat_codes) OR oa.hankija_kood='859') --select * from @pvn1_1_temp INSERT @pvn1_1_temp --payments SELECT (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = otr.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = otr.kmk) AS do_limit, '3', CAST(ot.number AS nvarchar(30)) AS client_doc_nr, CAST(ot.number AS nvarchar(30)) AS client_doc_nr, CAST(otr.kmk AS nvarchar(30)) AS vat_code, CAST((SELECT CAST(nimi AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS client_name, CAST((SELECT CAST(kmregnr AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS vat_reg_nr, --CAST(otr.summa_p AS decimal(15,2)) AS doc_sum, --CAST((otr.summa_p * (@stat_vat / 100)) AS decimal(15,2)) AS doc_vat, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) != 0) THEN CAST(otr.summa_p AS DECIMAL(15,2)) ELSE CAST((otr.summa_p / (1 + @stat_vat / 100)) AS DECIMAL(15,2)) END) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) != 0) THEN CAST(otr.summa_p * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) / 100) AS DECIMAL(15,2)) ELSE CAST((otr.summa_p / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS DECIMAL(15,2)) END) AS doc_vat, REPLACE(CONVERT(VARCHAR, ot.aeg, 111), '/', '-') AS doc_date, 0 FROM or_tasumised ot, or_tasumised_read otr WITH (NOLOCK) WHERE CAST(ot.aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (ot.kinnitatud='True') AND (ot.number=otr.number) AND (EXISTS(SELECT 1 FROM @pvn11_vat_codes WHERE code = otr.kmk)) UNION SELECT (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = oae.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = oae.kmk) AS do_limit, '9', CAST((SELECT hankija_arve FROM or_arved WHERE NUMBER = oae.arve )AS nvarchar(30)), CAST((SELECT hankija_arve FROM or_arved WHERE NUMBER = oae.arve )AS nvarchar(30)), oae.kmk, CAST((SELECT nimi FROM hankijad h WHERE h.kood = (SELECT hankija_kood FROM or_arved oa WHERE oa.number=oae.arve)) AS nvarchar(30)), CAST((SELECT kmregnr FROM hankijad h WHERE h.kood = (SELECT hankija_kood FROM or_arved oa WHERE oa.number=oae.arve)) AS nvarchar(30)), (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) != 0) THEN CAST(oae.summa AS DECIMAL(15,2)) - isnull(CAST((SELECT SUM(summa) FROM or_arved_read oar WHERE oar.number=oae.arve AND oar.kmkood = oae.kmk) AS DECIMAL(15,2)),0) ELSE CAST((oae.summa / (1 + @stat_vat / 100)) AS DECIMAL(15,2)) - isnull(CAST((SELECT SUM(summa) FROM or_arved_read oar WHERE oar.number=oae.arve AND oar.kmkood = oae.kmk) AS DECIMAL(15,2)),0) END) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) != 0) THEN CAST(oae.summa * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), 0)) / 100) AS DECIMAL(15,2)) - isnull(CAST((SELECT SUM(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk),0)/100) FROM or_arved_read oar WHERE oar.number=oae.arve AND oar.kmkood = oae.kmk) AS DECIMAL(15,2)),0) ELSE isnull(CAST((oae.summa / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS DECIMAL(15,2)),0) - isnull(CAST((SELECT (SUM(summa) * (1 + @stat_vat / 100)) FROM or_arved_read oar WHERE oar.number=oae.arve AND oar.kmkood = oae.kmk) AS DECIMAL(15,2)),0) END) AS doc_vat, REPLACE(CONVERT(VARCHAR, (SELECT kande_aeg FROM or_arved WHERE NUMBER=oae.arve), 111), '/', '-') AS doc_date, 0 FROM or_arved_ettemaksud oae WHERE (SELECT kande_aeg FROM or_arved oa WHERE oa.number=oae.arve) BETWEEN @aeg1 AND @aeg2 AND kmk IN (SELECT code FROM @pvn11_vat_codes) INSERT INTO @pvn1_1_temp --expenses /*SELECT (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit, '9', CAST(fk.number AS nvarchar(30)) AS doc_nr, CAST(fkr.dokument AS nvarchar(30)) AS client_doc_nr, CAST(fkr.kmkood AS nvarchar(30)) AS vat_code, CAST(fkr.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=fkr.hankija_kood) AS nvarchar(30)) AS vat_reg_nr, CAST(ISNULL(fkr.summa, 0) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) AS doc_sum, (CASE WHEN (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code=fkr.kmkood),0) != 0) THEN cast(CAST(ISNULL(fkr.summa, 0) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood),0) / 100) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) * (-1) as decimal(15,2)) ELSE cast(CAST(ISNULL(fkr.reakm, 0) * ISNULL(fkr.r_kurss, 1) AS decimal(15,2)) * (-1) as decimal(15,2)) END) AS doc_vat, REPLACE(CONVERT(VARCHAR, fkr.aeg, 111), '/', '-') AS doc_date, 0 FROM fin_kulutused fk, fin_kulutused_read fkr WITH (NOLOCK) WHERE CAST(fk.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) AND (fk.kinnitatud='True') AND (fk.number=fkr.number) AND (EXISTS(SELECT 1 FROM @pvn11_vat_codes WHERE code = fkr.kmkood))*/ SELECT (SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit, '8', CAST(fkr.dokument AS nvarchar(30)) AS client_doc_nr, CAST(fkr.number AS nvarchar(30)) AS doc_nr, CAST(fkr.kmkood AS nvarchar(30)) AS vat_code, CAST(fkr.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT kmregnr FROM hankijad WHERE kood=fkr.hankija_kood) AS nvarchar(30)), (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0) THEN CAST(fkr.summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0 AND (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0) THEN CAST(fkr.summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0) THEN CAST((fkr.a_summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1))) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0) / 100) AS DECIMAL(15,4)) END) AS doc_sum_wo_vat, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0) THEN CAST( ((fkr.summa) * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1))) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) AS DECIMAL(15,4)) -- isnull(cast((select sum(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr2.kmk),0)/100) from fin_kulutused_read fkr2 where fkr.number=oae.arve and oar.kmkood = oae.kmk) as decimal(15,2)),0) WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0 AND (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) = 0) THEN --CAST((fkr.summa * isnull(fk.kurssbv1,1)) * ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) AS decimal(15,4)) CAST(reakm AS DECIMAL(15,4)) WHEN ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) != 0) THEN CAST((fkr.reakm *isnull(fkr.r_kurss,isnull(fk.kurssbv1,1))) /* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0)) / 100) * (ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), 0) / 100) */ AS DECIMAL(15,4)) END), CAST(isnull(fkr.aeg,fk.aeg) AS DATE), '0' FROM fin_kulutused_read fkr WITH(nolock) LEFT JOIN fin_kulutused fk ON fk.number=fkr.number WHERE CAST(isnull(fkr.aeg,fk.aeg) AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND fkr.kmkood IN (SELECT code FROM @pvn11_vat_codes) AND (kinnitatud='True') INSERT @pvn1_1_values_by_doc SELECT ROW_NUMBER() OVER(ORDER BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date,directo_nr,vatc_prepayment) nr , dar_veids , do_limit , CASE WHEN (dok_veids = '9' OR dok_veids = '8') THEN '3' ELSE dok_veids END , doc_nr , directo_nr , vat_code , client_name , isnull(vat_reg_nr,' ') , CASE WHEN (dok_veids!='9') THEN SUM(doc_sum) ELSE (CASE WHEN (SUM(doc_sum) < 0 ) THEN 0 ELSE SUM(doc_sum) * (-1) END) END - CASE WHEN (dok_veids!='3') THEN ( CASE WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)!=0) THEN CAST(ISNULL(vatc_prepayment,0) AS DECIMAL(15,4)) WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)=0) THEN isnull(CAST(vatc_prepayment / CAST(1 + (SELECT TOP 1 (ilmakm / 100) FROM fin_kmkoodid WHERE kood=a.vat_code) AS DECIMAL(15,4)) AS DECIMAL(15,4)),0) END ) ELSE 0 END , CASE WHEN (dok_veids!='9' OR dok_veids='8') THEN SUM(doc_vat) ELSE (CASE WHEN (SUM(doc_vat) < 0 ) THEN 0 ELSE SUM(doc_vat)*(-1) END) END - CASE WHEN (dok_veids!='3') THEN ( CASE WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)!=0) THEN isnull((CAST((vatc_prepayment * CAST((0 + (SELECT POORD / 100 FROM fin_kmkoodid WHERE kood=a.vat_code)) AS DECIMAL(15,4))) AS DECIMAL(15,4)) ),0) WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code),0)=0) THEN isnull((CAST(vatc_prepayment - (vatc_prepayment / CAST(1+ (SELECT ilmakm / 100 FROM fin_kmkoodid WHERE kood=a.vat_code) AS DECIMAL(15,4))) AS DECIMAL(15,4)) ),0) END ) ELSE 0 END , doc_date , CASE WHEN (a.dok_veids='1') THEN (SELECT SUM(doc_sum) FROM @pvn1_1_temp b WHERE a.directo_nr=b.directo_nr AND b.dok_veids=a.dok_veids) ELSE (SELECT SUM(doc_sum) FROM @pvn1_1_temp b WHERE a.directo_nr=b.directo_nr AND b.dok_veids=a.dok_veids AND a.doc_nr=b.doc_nr) END - CASE WHEN (a.dok_veids='1') THEN (isnull((SELECT SUM(summa) FROM or_arved_ettemaksud WHERE arve=a.directo_nr AND kmk IN (SELECT DISTINCT kmkood FROM or_arved_read WHERE NUMBER=a.directo_nr)),0)) ELSE 0 END , vatc_prepayment FROM @pvn1_1_temp a GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date,directo_nr, vatc_prepayment --update @pvn1_1_values_by_doc set dar_veids = 'N' where vat_reg_nr = ' ' --update @pvn1_1_values_by_doc set dok_veids = '4' where doc_sum < 0 and dok_veids = '1' INSERT @pvn_1_1_totals SELECT SUM(doc_sum), SUM(doc_vat) FROM @pvn1_1_values_by_doc INSERT @pvn1_1_sums_supplier SELECT isnull(vat_reg_nr,' ') , SUM(doc_sum) , SUM(doc_vat) FROM @pvn1_1_values_by_doc WHERE isnull(vat_reg_nr,' ')!='' GROUP BY vat_reg_nr INSERT @pvn1_1_top_table SELECT dar_veids , do_limit , dok_veids , doc_nr , directo_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , rn FROM @pvn1_1_values_by_doc WHERE (doc_sum_total >= @lim OR doc_sum_total <= (@lim * (-1))) GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat,directo_nr, rn DELETE FROM @pvn1_1_values_by_doc WHERE rn IN (SELECT rn FROM @pvn1_1_top_table) INSERT @pvn1_1_top_r_table SELECT dar_veids , do_limit , dok_veids , doc_nr , directo_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , rn FROM @pvn1_1_values_by_doc WHERE (dar_veids IN ('R1','R2','R3','R4','R5','R6','R7','R8','R9') OR dar_veids IN (SELECT dar_veids FROM @pvn11_vat_codes WHERE SUBSTRING(do_limit,1,1)='N' AND SUBSTRING(dar_veids,1,1)!='R')) AND directo_nr+'('+dar_veids+')'+'['+dok_veids+']' NOT IN (SELECT directo_nr+'('+dar_veids+')'+'['+dok_veids+']' FROM @pvn1_1_top_table) GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat,directo_nr,rn --select * from @pvn11_vat_codes DELETE FROM @pvn1_1_values_by_doc WHERE rn IN (SELECT rn FROM @pvn1_1_top_r_table) INSERT @pvn_1_1_v_totals SELECT 'V' , CAST((SELECT TOP 1 SUBSTRING(NIMI,1,30) FROM hankijad Y WHERE Y.KMREGNR=Z.vat_reg_nr) AS NVARCHAR(30)) , vat_reg_nr , CAST(doc_sum - CAST(isnull((SELECT SUM(doc_sum) FROM @pvn1_1_top_table x WHERE x.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) - CAST(isnull((SELECT SUM(doc_sum) FROM @pvn1_1_top_r_table u WHERE u.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) AS DECIMAL(15,4)) , CAST(CAST(doc_vat AS DECIMAL(15,2)) - CAST(isnull((SELECT SUM(doc_vat) FROM @pvn1_1_top_table x WHERE x.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,2)) - CAST(isnull((SELECT SUM(CAST(doc_vat AS DECIMAL(15,2))) FROM @pvn1_1_top_r_table u WHERE u.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,2)) AS DECIMAL(15,2)) FROM @pvn1_1_sums_supplier z WHERE (doc_sum >= @lim OR doc_sum <= (@lim * (-1))) AND CAST(doc_sum - isnull((SELECT SUM(doc_sum) FROM @pvn1_1_top_table x WHERE x.vat_reg_nr=z.vat_reg_nr),0) - isnull((SELECT SUM(doc_sum) FROM @pvn1_1_top_r_table u WHERE u.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) <> 0 AND vat_reg_nr!='' INSERT @pvn_1_1_v_totals2 SELECT 'V', client_name, vat_reg_nr, doc_sum, doc_vat FROM @pvn_1_1_v_totals WHERE (doc_sum >= @lim OR doc_sum <= (@lim * (-1))) DELETE FROM @pvn1_1_values_by_doc WHERE vat_reg_nr IN (SELECT vat_reg_nr FROM @pvn_1_1_v_totals2) INSERT @pvn_1_1_t_totals SELECT CAST(SUM(doc_sum) AS DECIMAL(15,4)) , CAST(SUM(doc_vat) AS DECIMAL(15,4)) ,'' FROM @pvn1_1_values_by_doc z /* select cast(sum(doc_sum * (-1)) as decimal(15,4)) , cast(sum(doc_vat * (-1)) as decimal(15,4)) from @pvn1_1_values_by_doc z where (doc_sum between (@lim * (-1)) and @lim) and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='') and dar_veids not in ('R1','R2','R3','R4','R5','R6','R7','R8','R9') UNION select cast(sum(doc_sum) as decimal(15,4)) , cast(sum(doc_vat) as decimal(15,4)) from @pvn1_1_values_by_doc z where (doc_sum between (@lim * (-1)) and @lim) and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='') UNION select cast(sum(doc_sum) as decimal(15,4)) , cast(sum(doc_vat) as decimal(15,4)) from @pvn1_1_values_by_doc z where (doc_sum between (@lim * (-1)) and @lim) and (vat_reg_nr in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')!='') and vat_reg_nr not in (select vat_reg_nr from @pvn_1_1_v_totals2 where isnull(vat_reg_nr,'')='')) and dar_veids not in ('R1','R2','R3','R4','R5','R6','R7','R8','R9') UNION select cast(sum(doc_sum) as decimal(15,4)) , cast(sum(doc_vat) as decimal(15,4)) from @pvn1_1_values_by_doc z where (doc_sum between (@lim * (-1)) and @lim) and vat_reg_nr not in (select vat_reg_nr from @pvn1_1_sums_supplier where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) and isnull(vat_reg_nr,'')='')*/ ----------------- ------ 1-2 ------ ----------------- DECLARE @pvn12_vat_codes TABLE ( code nvarchar(30), rate DECIMAL, reverse_rate DECIMAL, proportion DECIMAL, description nvarchar(200), sales_account nvarchar(30), purchase_account nvarchar(30), info nvarchar(200), dar_veids nvarchar(5), do_limit VARCHAR(2) ) INSERT INTO @pvn12_vat_codes SELECT kood, ilmakm, poord, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_2' AND klass='kmk'), 'G') AS nvarchar(10)) AS dar_veids, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS VARCHAR(2)) AS do_limit FROM fin_kmkoodid fk WHERE lisainfo LIKE '%pvn12%' DECLARE @pvn12_currencies TABLE (currency nvarchar(3)) INSERT INTO @pvn12_currencies (currency) SELECT DISTINCT kood FROM curr_rates --pvn12 main table DECLARE @pvn12_main_table TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), doc_nr nvarchar(30), client_doc_nr nvarchar(30), vat_code nvarchar(30), directo_table nvarchar(30), doc_type nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), country_code nvarchar(2), doc_sum DECIMAL(15,2), doc_vat DECIMAL(15,2), rate DECIMAL(28,18), currency nvarchar(30), alt_rate DECIMAL(28, 18), alt_currency nvarchar(30), alt_doc_currency_sum nvarchar(30), doc_date nvarchar(30) ) --pvn12 temp table DECLARE @pvn12_temp_table TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), doc_nr nvarchar(30), client_doc_nr nvarchar(30), vat_code nvarchar(30), directo_table nvarchar(30), doc_type nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), country_code nvarchar(2), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), rate DECIMAL(28,18), currency nvarchar(30), alt_rate DECIMAL(28, 18), alt_currency nvarchar(30), alt_doc_currency_sum nvarchar(30), doc_date nvarchar(30), vat_c_prepayment DECIMAL ) INSERT INTO @pvn12_temp_table --purchase invoices - temp SELECT (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code =oar.kmkood) AS dar_veids, '' AS do_limit, CAST(oa.number AS nvarchar(30)) AS doc_nr, CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr, CAST(oar.kmkood AS nvarchar(30)) AS vat_code, 'purchase_invoice' AS directo_table, CAST(oa.tyyp AS nvarchar(30)) AS doc_type, CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr, '--' AS country_code, CAST(((SELECT SUM(summa) FROM or_arved_read WHERE NUMBER=oar.number AND kmkood=oar.kmkood) * ISNULL(oa.kurssbv1,1)) AS DECIMAL(15,4)) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmkood), 0)) != 0) THEN CAST((SELECT SUM(summa) FROM or_arved_read WHERE NUMBER=oar.number AND kmkood=oar.kmkood) * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmkood), 0)) / 100) AS DECIMAL(15,4)) ELSE CAST((SELECT SUM(summa) FROM or_arved_read WHERE NUMBER=oar.number AND kmkood=oar.kmkood)*(@stat_vat/100)*ISNULL(oa.kurssbv1,1) AS DECIMAL(15,4)) END) AS doc_vat, CAST(ISNULL(oa.kurssbv1,1) AS DECIMAL(28,18)) AS rate, CAST(oa.valuuta AS nvarchar(30)) AS currency, 1 AS alt_rate, 'EUR' AS alt_currency, CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-') AS doc_date, (SELECT SUM(summa) FROM or_arved_ettemaksud oae WHERE oae.arve=oa.number AND oae.kmk=oar.kmkood) FROM or_arved_read oar, or_arved oa WITH (NOLOCK) WHERE CAST(oa.kande_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (kinnitatud='True') AND (oa.number=oar.number) AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = oar.kmkood)) UNION SELECT (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = otr.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = otr.kmk) AS do_limit, CAST(isnull((SELECT hankija_arve FROM or_arved WHERE NUMBER=otr.ostuarve),ot.number) AS nvarchar(30)) AS client_doc_nr, CAST(ot.number AS nvarchar(30)) AS client_doc_nr, CAST(otr.kmk AS nvarchar(30)) AS vat_code, 'payments' AS directo_table, '' doc_type, CAST((SELECT CAST(nimi AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS client_name, CAST((SELECT CAST(kmregnr AS nvarchar(30)) FROM hankijad WITH (NOLOCK) WHERE (hankijad.kood=otr.hankija_kood)) AS nvarchar(30)) AS vat_reg_nr, --CAST(otr.summa_p AS decimal(15,2)) AS doc_sum, --CAST((otr.summa_p * (@stat_vat / 100)) AS decimal(15,2)) AS doc_vat, '--' AS country_code, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) != 0) THEN CAST(otr.summa_p AS DECIMAL(15,2)) ELSE CAST((otr.summa_p / (1 + @stat_vat / 100)) AS DECIMAL(15,2)) END) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) != 0) THEN CAST(otr.summa_p * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) / 100) AS DECIMAL(15,2)) ELSE CAST((otr.summa_p / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS DECIMAL(15,2)) END) AS doc_vat, CAST(ISNULL(otr.kurss_a,1) AS DECIMAL(28,18)) AS rate, CAST(otr.valuuta_a AS nvarchar(30)) AS currency, 1 AS alt_rate, 'EUR' AS alt_currency, '',--CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, REPLACE(CONVERT(VARCHAR, ot.aeg, 111), '/', '-') AS doc_date, 0 FROM or_tasumised ot, or_tasumised_read otr WITH (NOLOCK) WHERE CAST(ot.aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (ot.kinnitatud='True') AND (ot.number=otr.number) AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = otr.kmk)) AND isnull(otr.ostuarve,'')='' UNION SELECT (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code =oar.kmk) AS dar_veids, '' AS do_limit, CAST(oa.number AS nvarchar(30)) AS doc_nr, CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr, CAST(oar.kmk AS nvarchar(30)) AS vat_code, 'prepayment' AS directo_table, CAST(oa.tyyp AS nvarchar(30)) AS doc_type, CAST(oa.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT TOP 1 kmregnr FROM hankijad WHERE hankijad.kood=oa.hankija_kood) AS nvarchar(30)) AS vat_reg_nr, '--' AS country_code, CAST((oar.summa)*ISNULL(oa.kurssbv1,1) AS DECIMAL(15,4)) AS doc_sum, (CASE WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmk), 0)) != 0) THEN CAST(oar.summa * ISNULL(oa.kurssbv1, 1) * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmk), 0)) / 100) AS DECIMAL(15,4)) ELSE CAST((oar.summa)*(@stat_vat/100)*ISNULL(oa.kurssbv1,1) AS DECIMAL(15,4)) END) AS doc_vat, CAST(ISNULL(oa.kurssbv1,1) AS DECIMAL(28,18)) AS rate, CAST(oa.valuuta AS nvarchar(30)) AS currency, 1 AS alt_rate, 'EUR' AS alt_currency, CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, REPLACE(CONVERT(VARCHAR, oa.aeg, 111), '/', '-') AS doc_date, 0 FROM or_arved_ettemaksud oar, or_arved oa WITH (NOLOCK) WHERE CAST(oa.kande_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (oa.kinnitatud='True') AND (oa.number=oar.arve) AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code = oar.kmk)) /* Select * from table a left join table b on a.blabla=b.blable Select * from table a , table b Where tablea.blabla = tableb.blabla */ INSERT INTO @pvn12_main_table --purchase invoices - main SELECT dar_veids , do_limit , doc_nr , client_doc_nr , '' AS vat_code , directo_table , doc_type , client_name , vat_reg_nr , country_code , CASE WHEN (directo_table='purchase_invoice') THEN SUM(doc_sum) - isnull((SELECT SUM(doc_sum) FROM @pvn12_temp_table WHERE directo_table='prepayment' AND doc_nr=b.doc_nr),0) ELSE SUM(doc_sum) END , CASE WHEN (directo_table='purchase_invoice') THEN SUM(doc_vat) - isnull((SELECT SUM(doc_vat) FROM @pvn12_temp_table WHERE directo_table='prepayment' AND doc_nr=b.doc_nr),0) ELSE SUM(doc_vat) END, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date FROM @pvn12_temp_table b WHERE --doc_sum > 0 or doc_sum < 0 or directo_table NOT IN ('prepayment') GROUP BY dar_veids, do_limit, doc_nr, client_doc_nr, directo_table, doc_type, client_name, vat_reg_nr, country_code, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date INSERT INTO @pvn12_main_table --expenses /* SELECT '' AS dar_veids, '' AS do_limit, CAST(number AS nvarchar(30)) AS doc_nr, CAST((SELECT TOP 1 dokument FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS client_doc_nr, CAST((SELECT TOP 1 kmkood FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS vat_code, 'expense' AS directo_table, 'Fakturrekins' AS doc_type, CAST((SELECT TOP 1 hankija_nimi FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number) AS nvarchar(30)) AS client_name, CAST((SELECT kmregnr FROM hankijad WHERE hankijad.kood=(SELECT TOP 1 hankija_kood FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS vat_reg_nr, '--' AS country_code, CAST(summa*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_sum, CAST(summa*(@stat_vat/100)*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_vat, CAST(ISNULL(kurssbv1,1) AS decimal(28,18)) AS rate, CAST(valuuta AS nvarchar(30)) AS currency, 1 AS alt_rate, 'EUR' AS alt_currency, CAST(lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date FROM fin_kulutused WITH (NOLOCK) WHERE CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) AND (kinnitatud='True') AND (EXISTS(SELECT 1 FROM @pvn12_vat_codes WHERE code IN (SELECT kmkood FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)))) */ SELECT (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = fkr.kmkood) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = fkr.kmkood) AS do_limit, CAST(fkr.number AS nvarchar(30)) AS doc_nr, CAST(fkr.dokument AS nvarchar(30)) AS client_doc_nr, CAST(fkr.kmkood AS nvarchar(30)) AS vat_code, 'expense' AS directo_table, 'Fakturrekins' AS doc_type, CAST(fkr.hankija_nimi AS nvarchar(30)) AS client_name, CAST((SELECT kmregnr FROM hankijad WHERE kood=fkr.hankija_kood) AS nvarchar(30)), '--', CAST(fkr.summa * isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS DECIMAL(15,2)), CAST((fkr.summa * (@stat_vat / 100)) * isnull(r_kurss,1) AS DECIMAL(15,2)), CAST(isnull(fkr.r_kurss,isnull(fk.kurssbv1,1)) AS DECIMAL(28,18)), CAST(isnull(fkr.r_valuuta,isnull(fk.valuuta,'EUR')) AS nvarchar(255)), 1, 'EUR', CAST(fk.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, REPLACE(CONVERT(VARCHAR, isnull(fkr.aeg,fk.aeg), 111), '/', '-') AS doc_date FROM fin_kulutused_read fkr WITH(nolock) LEFT JOIN fin_kulutused fk ON fk.number=fkr.number WHERE CAST(isnull(fkr.aeg,fk.aeg) AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND fkr.kmkood IN (SELECT code FROM @pvn12_vat_codes) AND (kinnitatud='True') UPDATE @pvn12_main_table SET country_code = SUBSTRING(vat_reg_nr, 1, 2) UPDATE @pvn12_main_table SET alt_currency = (CASE WHEN country_code = 'BG' THEN 'BGN' WHEN country_code = 'CZ' THEN 'CZK' WHEN country_code = 'DK' THEN 'DKK' WHEN country_code = 'GB' THEN 'GBP' WHEN country_code = 'HU' THEN 'HUF' WHEN country_code = 'LT' AND CAST(doc_date AS DATE) < CAST('2015.01.01' AS DATE) THEN 'LTL' WHEN country_code = 'PL' THEN 'PLN' WHEN country_code = 'RO' THEN 'RON' WHEN country_code = 'SE' THEN 'SEK' ELSE 'EUR' END) UPDATE @pvn12_main_table SET alt_rate = ISNULL((SELECT TOP 1 CAST(curr_rates.kurss1 AS DECIMAL(28,18)) FROM curr_rates WHERE curr_rates.kood = alt_currency AND CAST(curr_rates.aeg AS DATE) <= DATEADD(DAY, -1, CAST(doc_date AS DATE)) ORDER BY curr_rates.aeg DESC), 1) UPDATE @pvn12_main_table SET dar_veids = (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code = vat_code) WHERE dar_veids IS NULL UPDATE @pvn12_main_table SET do_limit = (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = vat_code) ----------------- ------ 1-3 ------ ----------------- --izveidojam PVN 13 kodu tabulu DECLARE @pvn13_vat_codes TABLE ( code nvarchar(30), rate DECIMAL, description nvarchar(200), sales_account nvarchar(30), purchase_account nvarchar(30), info nvarchar(200), dar_veids nvarchar(5), do_limit VARCHAR(2) ) INSERT INTO @pvn13_vat_codes SELECT kood, ilmakm, seletus, myykkonto, ostuKMkonto, lisainfo, CAST(ISNULL((SELECT TOP 1 REPLACE(sisu,'_','.') FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_3' AND klass='kmk'), '41') AS nvarchar(10)) AS dar_veids, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS VARCHAR(2)) AS do_limit FROM fin_kmkoodid fk WHERE lisainfo LIKE '%pvn13%' DECLARE @pvn1_3_temp TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), doc_date nvarchar(30), doc_sum_total DECIMAL(15,4), vatc_prepayment DECIMAL(15,4) ) DECLARE @pvn1_3_values_by_doc TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), doc_date nvarchar(30), doc_sum_total DECIMAL(15,4), vatc_prepayment DECIMAL(15,4), rn INT ) DECLARE @pvn1_3_top_table TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), doc_date nvarchar(30), doc_sum_total DECIMAL(15,4), vatc_prepayment DECIMAL(15,4), rn INT ) DECLARE @pvn1_3_top_tablex TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), doc_date nvarchar(30), doc_sum_total DECIMAL(15,4), vatc_prepayment DECIMAL(15,4), rn INT ) DECLARE @tdeals TABLE ( dar_veids nvarchar(5), do_limit VARCHAR(2), dok_veids nvarchar(1), doc_nr nvarchar(30), vat_code nvarchar(30), client_name nvarchar(30), vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4), doc_date nvarchar(30), doc_sum_total DECIMAL(15,4), vatc_prepayment DECIMAL(15,4), rn INT ) DECLARE @pvn1_3_top_sums_customer TABLE ( vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) DECLARE @pvn1_3_sums_customer TABLE ( vat_reg_nr nvarchar(30), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) DECLARE @v_totals TABLE ( dok_veids nvarchar(32), client_name nvarchar(30), vat_reg_nr nvarchar(34), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) DECLARE @x_totals TABLE ( dok_veids nvarchar(32), client_name nvarchar(30), vat_reg_nr nvarchar(32), doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) DECLARE @t_totals TABLE ( doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) --ielasam visas rēķina rindas kuras ir ar 1 - 3 atšifrējamajiem pvn kodiem INSERT @pvn1_3_temp SELECT (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn13_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS do_limit, CASE WHEN (isnull(ettemaks,0) = 0) THEN '1' ELSE '1' END AS dok_veids, mr_arved_read.number AS doc_nr, kmk AS vat_code, CAST((klient_nimi) AS nvarchar(30)) AS client_name, CASE WHEN (kmregnumber IS NOT NULL OR kmregnumber!=' ') THEN kmregnumber ELSE (SELECT top 1 kmregnr FROM kliendid WHERE kood=mr_arved.klient_kood)END AS vat_reg_nr, CAST((ROUND(ISNULL(summa, 0), 4)) AS DECIMAL(15,4)) AS doc_sum, isnull(CAST((ROUND(ISNULL(summa, 0), 4) * (NULLIF((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code = mr_arved_read.kmk), 0) / 100), 0))) AS DECIMAL(15,4)),'0.00') AS doc_vat, REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date ,(SELECT SUM(summa) FROM mr_arved_read mar1 WHERE mar1.number=mr_arved_read.number AND kmk IN (SELECT code FROM @pvn13_vat_codes)) ,(SELECT SUM(summa) FROM mr_arved_ettemaksud mae WHERE mae.arve=mr_arved_read.number AND mae.kmk=mr_arved_read.kmk) FROM mr_arved_read WITH (NOLOCK) INNER JOIN mr_arved ON mr_arved_read.number=mr_arved.number WHERE CAST(aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (kinnitatud = 1) AND kmk IN (SELECT code FROM @pvn13_vat_codes) AND artikkel IS NOT NULL AND mr_arved.kokku >=0 INSERT @pvn1_3_temp SELECT (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes vc WHERE vc.code = mlr.kmk) AS dar_veids, (SELECT TOP 1 do_limit FROM @pvn13_vat_codes vc WHERE vc.code = mlr.kmk) AS do_limit, '3' AS dok_veids, CAST(ml.number AS nvarchar(30)) AS doc_nr, CAST(mlr.kmk AS nvarchar(30)) AS vat_code, CAST((SELECT TOP 1 nimi FROM kliendid WITH (NOLOCK) WHERE (kliendid.kood=mlr.klient_kood)) AS nvarchar(30)) AS client_name, CAST((SELECT TOP 1 kmregnr FROM kliendid WITH (NOLOCK) WHERE (kliendid.kood=mlr.klient_kood)) AS nvarchar(30)) AS vat_reg_nr, CAST((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1)) AS DECIMAL(15,4)) AS doc_sum, CAST(((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1))*(ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)) AS DECIMAL(15,4)) AS doc_vat, REPLACE(CONVERT(VARCHAR, ml.aeg, 111), '/', '-') AS doc_date, CAST((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk),0)/100)+1)) AS DECIMAL(15,4)), 0 FROM mr_laekumised ml, mr_laekumised_read mlr WITH (NOLOCK) WHERE CAST(ml.aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND (ml.kinnitatud='True') AND (ml.number=mlr.number) AND mlr.kmk IN (SELECT code FROM @pvn13_vat_codes) AND ((ISNULL(NULLIF(mlr.ettemaks,0),0)!=0) OR ((ISNULL(NULLIF(mlr.arvenumber,0),0)=0))) INSERT @pvn1_3_values_by_doc SELECT dar_veids , do_limit , dok_veids , doc_nr , vat_code , client_name , isnull(vat_reg_nr,' ') , SUM(doc_sum)- CASE WHEN (dok_veids!='3') THEN (isnull((SELECT CAST(summa / CAST(1 + (SELECT ilmakm / 100 FROM fin_kmkoodid WHERE kood=mae.kmk) AS DECIMAL(15,4)) AS DECIMAL(15,4)) FROM mr_arved_ettemaksud mae WHERE mae.arve=pvn13temp.doc_nr AND mae.kmk=pvn13temp.vat_code),0)) ELSE 0 END , SUM(doc_vat) - CASE WHEN (dok_veids!='3') THEN (isnull((SELECT CAST(summa - (summa / CAST(1 + (SELECT ilmakm / 100 FROM fin_kmkoodid WHERE kood=mae.kmk) AS DECIMAL(15,4))) AS DECIMAL(15,4)) FROM mr_arved_ettemaksud mae WHERE mae.arve=pvn13temp.doc_nr AND mae.kmk=pvn13temp.vat_code),0)) ELSE 0 END , doc_date , doc_sum_total , vatc_prepayment , ROW_NUMBER() OVER(ORDER BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, vatc_prepayment) nr FROM @pvn1_3_temp pvn13temp GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, vatc_prepayment INSERT @pvn1_3_sums_customer SELECT isnull(vat_reg_nr,' ') , SUM(doc_sum) , SUM(doc_vat) FROM @pvn1_3_values_by_doc WHERE isnull(vat_reg_nr,'')!='' GROUP BY vat_reg_nr --delete from @pvn1_3_temp where dok_veids in ('1','Z') --only for anvol DECLARE @fin_kanded_read TABLE ( sum_wo_vat DECIMAL(15,4), vat_sum DECIMAL(15,4) ) INSERT @fin_kanded_read SELECT 0,SUM( CASE WHEN (baas1deebet < 0) THEN (baas1deebet * (-1)) WHEN (baas1kreedit > 0) THEN (baas1kreedit) END ) - SUM( CASE WHEN (baas1deebet > 0) THEN (baas1deebet) WHEN (baas1kreedit < 0) THEN (baas1kreedit * (-1)) END ) FROM fin_kanded_read WHERE tyyp='fin' AND CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND konto IN ('5721','57210') AND kmkood IN ('1','16') UNION SELECT SUM( CASE WHEN (baas1deebet < 0) THEN (baas1deebet * (-1)) WHEN (baas1kreedit > 0) THEN (baas1kreedit) END ) - SUM( CASE WHEN (baas1deebet > 0) THEN (baas1deebet) WHEN (baas1kreedit < 0) THEN (baas1kreedit * (-1)) END ),0 FROM fin_kanded_read WHERE tyyp='fin' AND CAST(r_aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND SUBSTRING(CONVERT(nvarchar(32),konto),1,1)='6' AND kmkood IN ('16','1') DECLARE @pvn1_3tots TABLE ( doc_sum DECIMAL(15,4), doc_vat DECIMAL(15,4) ) INSERT @pvn1_3tots SELECT CAST(SUM(doc_sum) + (SELECT SUM(sum_wo_vat) FROM @fin_kanded_read) AS DECIMAL(15,4)) AS sum_total, CAST(SUM(doc_vat) + (SELECT SUM(vat_sum) FROM @fin_kanded_read) AS DECIMAL(15,4)) AS vat_total FROM @pvn1_3_values_by_doc INSERT @pvn1_3_top_table SELECT dar_veids , do_limit , dok_veids , doc_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , doc_sum_total , vatc_prepayment , rn FROM @pvn1_3_values_by_doc WHERE (doc_sum_total >= @lim OR doc_sum_total <= (@lim * (-1))) AND doc_sum<>0 AND isnull(vat_reg_nr,'')!='' GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn DELETE FROM @pvn1_3_values_by_doc WHERE rn IN (SELECT rn FROM @pvn1_3_top_table) INSERT @pvn1_3_top_tablex SELECT dar_veids , do_limit , dok_veids , doc_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , doc_sum_total , vatc_prepayment , rn FROM @pvn1_3_values_by_doc WHERE (doc_sum_total >= @lim OR doc_sum_total <= (@lim * (-1))) AND isnull(vat_reg_nr,'')='' GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn DELETE FROM @pvn1_3_values_by_doc WHERE rn IN (SELECT rn FROM @pvn1_3_top_tablex) INSERT @pvn1_3_top_sums_customer SELECT vat_reg_nr , SUM(doc_sum) , SUM(doc_vat) FROM @pvn1_3_top_table GROUP BY vat_reg_nr INSERT @v_totals SELECT 'V' , CAST((SELECT TOP 1 SUBSTRING(NIMI,1,30) FROM KLIENDID Y WHERE Y.KMREGNR=Z.vat_reg_nr) AS NVARCHAR(30)) , vat_reg_nr , CAST(doc_sum - isnull((SELECT top 1 doc_sum FROM @pvn1_3_top_sums_customer x WHERE x.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) , CAST(doc_vat - isnull((SELECT top 1 doc_vat FROM @pvn1_3_top_sums_customer x WHERE x.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) FROM @pvn1_3_sums_customer z WHERE (doc_sum >= @lim OR doc_sum <= (@lim * (-1))) AND CAST(doc_sum - isnull((SELECT top 1 doc_sum FROM @pvn1_3_top_sums_customer x WHERE x.vat_reg_nr=z.vat_reg_nr),0) AS DECIMAL(15,4)) > 0 AND isnull(vat_reg_nr,'')!='' DELETE FROM @pvn1_3_values_by_doc WHERE vat_reg_nr IN (SELECT vat_reg_nr FROM @v_totals) INSERT @tdeals SELECT dar_veids , do_limit , dok_veids , doc_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , doc_sum_total , vatc_prepayment , rn FROM @pvn1_3_values_by_doc WHERE (doc_sum BETWEEN (@lim * (-1)) AND @lim) AND isnull(vat_reg_nr,'')!='' GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn UNION SELECT dar_veids , do_limit , dok_veids , doc_nr , vat_code , client_name , vat_reg_nr , doc_sum , doc_vat , doc_date , doc_sum_total , vatc_prepayment , rn FROM @pvn1_3_values_by_doc WHERE (doc_sum BETWEEN (@lim * (-1)) AND @lim) AND isnull(vat_reg_nr,'')='' GROUP BY dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_date, doc_sum_total, doc_sum, doc_vat, vatc_prepayment, rn DELETE FROM @pvn1_3_values_by_doc WHERE rn IN (SELECT rn FROM @tdeals) INSERT @t_totals SELECT CAST(SUM(doc_sum) AS DECIMAL(15,4)) , CAST(SUM(doc_vat) AS DECIMAL(15,4)) FROM @tdeals z INSERT @x_totals SELECT 'X' , '' , '' , CAST(SUM(doc_sum) AS DECIMAL(15,4)) , CAST(SUM(doc_vat) AS DECIMAL(15,4)) FROM @pvn1_3_top_tablex z --where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1))) and doc_sum<>0 and isnull(vat_reg_nr,'')='' ----------------- ------ 2-1 ------ ----------------- DECLARE @pvn21_vat_codes TABLE ( code nvarchar(30), rate DECIMAL, proportion DECIMAL, description nvarchar(200), sales_account nvarchar(30), purchase_account nvarchar(30), info nvarchar(200), dar_veids nvarchar(5), do_limit VARCHAR(2) ) INSERT INTO @pvn21_vat_codes SELECT kood, ilmakm, or_proportsioon, seletus, myykkonto, ostuKMkonto, lisainfo, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS_2' AND klass='kmk'), 'G') AS nvarchar(10)) AS dar_veids, CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' AND klass='kmk'), 'Ja') AS VARCHAR(2)) AS do_limit FROM fin_kmkoodid fk WHERE lisainfo LIKE '%pvn21%' DECLARE @invoices TABLE ( pazime nvarchar(5), do_limit VARCHAR(2), vat_reg_nr nvarchar(30), doc_nr nvarchar(30), vat_code nvarchar(30), article_sum DECIMAL(15,2) ) INSERT INTO @invoices /* SELECT '' AS pazime, '' AS do_limit, (SELECT TOP 1 kmregnr FROM kliendid, mr_arved WHERE (kliendid.kood=mr_arved.klient_kood) AND (mr_arved.number=mr_arved_read.number)) AS vat_reg_nr, number AS doc_nr, CAST(kmk AS nvarchar(30)) AS vat_code, ISNULL(summa,0)*(SELECT ISNULL(kurssbv1,1) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number) AS article_sum FROM mr_arved_read WITH (NOLOCK) WHERE (SELECT CAST(aeg AS date) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) AND ((SELECT kinnitatud FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mr_arved_read.number)=1) AND (EXISTS(SELECT 1 FROM @pvn21_vat_codes WHERE code = kmk)) */ SELECT DISTINCT (SELECT dar_veids FROM @pvn21_vat_codes WHERE code=kmk), (SELECT do_limit FROM @pvn21_vat_codes WHERE code=kmk), SUBSTRING(isnull((SELECT TOP 1 kmregnr FROM kliendid WHERE (kliendid.kood=(SELECT klient_kood FROM mr_arved WHERE mr_arved.number=mar.number))),''),1,30), mar.number, mar.kmk, CAST(SUM(summa) * (SELECT ISNULL(kurssbv1,1) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number) AS DECIMAL(15,4)) FROM mr_arved_read mar WHERE (SELECT CAST(aeg AS DATE) FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND ((SELECT kinnitatud FROM mr_arved WITH (NOLOCK) WHERE mr_arved.number=mar.number)=1) AND (EXISTS(SELECT 1 FROM @pvn21_vat_codes WHERE code = kmk)) AND kmk IN (SELECT code FROM @pvn21_vat_codes) GROUP BY NUMBER, kmk UNION SELECT (SELECT dar_veids FROM @pvn21_vat_codes WHERE code=mlr.kmk), (SELECT do_limit FROM @pvn21_vat_codes WHERE code=mlr.kmk), SUBSTRING(isnull((SELECT TOP 1 kmregnr FROM kliendid WHERE (kliendid.kood=mlr.klient_kood)),''),1,30), mlr.number, mlr.kmk, CAST(SUM(summa_p) * (ISNULL(kurss_p,1)) AS DECIMAL(15,4)) FROM mr_laekumised_read mlr LEFT JOIN mr_laekumised ml ON ml.number=mlr.number WHERE CAST(ml.aeg AS DATE) BETWEEN CAST(@aeg1 AS DATE) AND CAST(@aeg2 AS DATE) AND isnull(ml.kinnitatud,0)=1 AND mlr.kmk IN (SELECT code FROM @pvn21_vat_codes) GROUP BY mlr.number,mlr.kmk, mlr.klient_kood, mlr.summa_p,mlr.kurss_p --UPDATE @invoices SET pazime = (SELECT TOP 1 dar_veids FROM @pvn21_vat_codes vc WHERE vc.code = vat_code) --UPDATE @invoices SET do_limit = (SELECT TOP 1 do_limit FROM @pvn21_vat_codes vc WHERE vc.code = vat_code) DECLARE @invoices_grouped TABLE (pazime nvarchar(1), vat_reg_nr nvarchar(30), doc_nr nvarchar(30), article_sum DECIMAL(15,2)) INSERT INTO @invoices_grouped (pazime, vat_reg_nr, doc_nr, article_sum) SELECT pazime, vat_reg_nr, doc_nr, SUM(article_sum) FROM @invoices GROUP BY vat_reg_nr, pazime, doc_nr --xml out SELECT ( SELECT @aeg1 AS date1, @aeg2 AS date2, CURRENT_TIMESTAMP AS date_now FOR XML PATH('dates'), TYPE, ELEMENTS ), ( SELECT * FROM @pvn11_vat_codes FOR XML PATH('code'),TYPE, ELEMENTS ) AS [pvn11_vat_codes], ( SELECT * FROM @pvn12_vat_codes FOR XML PATH('code'),TYPE, ELEMENTS ) AS [pvn12_vat_codes], ( SELECT * FROM @pvn13_vat_codes FOR XML PATH('code'),TYPE, ELEMENTS ) AS [pvn13_vat_codes], ( SELECT * FROM @pvn21_vat_codes FOR XML PATH('code'),TYPE, ELEMENTS ) AS [pvn21_vat_codes], ( SELECT @vat_declar_name AS vat_declar_name, @stat_vat AS stat_vat, @lim AS lim, @account AS account FOR XML PATH('filters'),TYPE, ELEMENTS ), ( SELECT setting AS name, (SELECT setting FROM settings WHERE id='firma_kmnr') AS vat_reg_nr, (SELECT setting FROM settings WHERE id='firma_regnr') AS reg_nr, (SELECT setting FROM settings WHERE id='firma_telefon') AS tel, (SELECT setting FROM settings WHERE id='firma_faks') AS fax, (SELECT setting FROM settings WHERE id='firma_aadress') AS address1, (SELECT setting FROM settings WHERE id='firma_aadress2') AS address2, (SELECT setting FROM settings WHERE id='firma_aadress3') AS address3, (SELECT setting FROM settings WHERE id='firma_tegevusaadress') AS biz_address1, (SELECT setting FROM settings WHERE id='firma_tegevusaadress2') AS biz_address2, (SELECT setting FROM settings WHERE id='firma_tegevusaadress3') AS biz_address3, (SELECT setting FROM settings WHERE id='firma_pank') AS bank, (SELECT setting FROM settings WHERE id='firma_swift') AS bank_swift, (SELECT setting FROM settings WHERE id='firma_aa') AS account, (SELECT setting FROM settings WHERE id='firma_iban') AS iban, (SELECT setting FROM settings WHERE id='firma_juht') AS head FROM settings WHERE id='firma_nimi' FOR XML PATH('company_info'), TYPE, ELEMENTS ), ( SELECT * FROM @vat_declar_sums FOR XML PATH('vat_declar_sum'), TYPE, ELEMENTS ) AS [vat_declar_totals], ( --pvn11 SELECT dar_veids, do_limit, doc_nr, doc_nr AS client_doc_nr, vat_code, dok_veids AS TYPE, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat, doc_date AS DATE, rn FROM @pvn1_1_top_table WHERE (doc_sum <> 0 OR doc_vat <> 0) FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn11_docs_above], ( SELECT dar_veids, do_limit, doc_nr, doc_nr AS client_doc_nr, vat_code, dok_veids AS TYPE, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat, doc_date AS DATE FROM @pvn1_1_top_r_table WHERE (doc_sum <> 0 OR doc_vat <> 0) FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn11_docs_above], ( SELECT 'V' AS dar_veids, '' AS do_limit, '' AS doc_nr, '' AS client_doc_nr, '' AS vat_code, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat, '' AS doc_date FROM @pvn_1_1_v_totals2 FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn11_docs_above], ( SELECT CAST(SUM(doc_sum) AS DECIMAL(15,2)) AS sum_total, CAST(SUM(doc_vat) AS DECIMAL(15,2)) AS vat_total FROM @pvn_1_1_t_totals FOR XML PATH('pvn11_totals_below'), TYPE, ELEMENTS ), ( SELECT doc_sum AS sum_total,doc_vat AS vat_total FROM @pvn_1_1_totals FOR XML PATH('pvn11_totals'), TYPE, ELEMENTS ), /* ( SELECT CAST(SUM((doc_sum) * (-1)) AS decimal(15,2)) AS sum_total, CAST(SUM((doc_vat) * (-1)) AS decimal(15,2)) AS vat_total FROM @pvn13_main_table_11 FOR XML PATH('pvn11_totals'), TYPE, ELEMENTS ),--/pvn11 */ ( --pvn12 SELECT dar_veids, do_limit, doc_nr, client_doc_nr, vat_code, directo_table, doc_type AS TYPE, client_name, vat_reg_nr, country_code, doc_sum AS SUM, doc_vat AS vat, rate, currency, alt_rate, alt_currency, alt_doc_currency_sum, doc_date AS DATE FROM @pvn12_main_table WHERE (doc_sum <> 0) FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn12_docs], ( SELECT CAST(SUM(doc_sum) AS DECIMAL(15,2)) AS sum_total, CAST(SUM(doc_vat) AS DECIMAL(15,2)) AS vat_total FROM @pvn12_main_table FOR XML PATH('pvn12_totals'), TYPE, ELEMENTS ), ( -- rates SELECT currency, (SELECT TOP 1 CAST(kurss1 AS DECIMAL(28,18)) AS rate, aeg AS DATE FROM curr_rates WHERE kood=currency ORDER BY aeg DESC FOR XML PATH(''),TYPE) FROM @pvn12_currencies FOR XML PATH('rate'), TYPE, ELEMENTS ) AS [pvn12_currency_rates], --/pvn12 ( --pvn13 SELECT dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat, doc_date AS DATE FROM @pvn1_3_top_table FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn13_docs_above], ( SELECT dok_veids, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat FROM @v_totals FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn13_docs_above], ( SELECT dok_veids, client_name, vat_reg_nr, doc_sum AS SUM, doc_vat AS vat FROM @x_totals FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn13_docs_above], ( SELECT isnull(SUM(doc_sum),0) + (SELECT SUM(sum_wo_vat) FROM @fin_kanded_read) AS sum_total, isnull(SUM(doc_vat),0) + (SELECT SUM(vat_sum) FROM @fin_kanded_read) AS vat_total FROM @t_totals FOR XML PATH('pvn13_totals_below'), TYPE, ELEMENTS ), ( SELECT CAST(SUM(doc_sum) AS DECIMAL(15,2)) AS sum_total, CAST(SUM(doc_vat) AS DECIMAL(15,2)) AS vat_total FROM @pvn1_3tots FOR XML PATH('pvn13_totals'), TYPE, ELEMENTS ), --/pvn13 /* ( SELECT dok_veids, client_name, vat_reg_nr, sum(doc_sum) AS sum, sum(doc_vat) AS vat FROM @pvn13_sums_above_table where dok_veids!='T' group by dok_veids, client_name, vat_reg_nr FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn13_docs_above], ( SELECT CAST(SUM(doc_sum) AS decimal(15,2)) AS sum_total, CAST(SUM(doc_vat) AS decimal(15,2)) AS vat_total FROM @pvn13_sums_above_table m where dok_veids='T' FOR XML PATH('pvn13_totals_below'), TYPE, ELEMENTS ), ( SELECT CAST((SUM(toptabledoc)) AS decimal(15,2)) AS sum_total, CAST(SUM(toptablevat) AS decimal(15,2)) AS vat_total FROM @pvn13sums FOR XML PATH('pvn13_totals'), TYPE, ELEMENTS ),*/ --/pvn13 ( --pvn21 SELECT pazime, vat_reg_nr, CAST(SUM(article_sum) AS DECIMAL(15,2)) AS SUM FROM @invoices_grouped GROUP BY vat_reg_nr, pazime FOR XML PATH('doc'), TYPE, ELEMENTS ) AS [pvn21_docs], ( SELECT CAST(SUM(article_sum) AS DECIMAL(15,2)) AS sum_total FROM @invoices_grouped FOR XML PATH('pvn21_totals'), TYPE, ELEMENTS ) --/pvn21 FOR XML PATH('document'), TYPE, ELEMENTS
