lv:procedures
Atšķirības
Norādītais vecais variants no patreizējās lapas atšķiras ar.
| Abās pusēs iepriekšējo versijuIepriekšējā versijaNākamā versija | Iepriekšējā versija | ||
| lv:procedures [2020/10/01 15:30] – dentoflex pvn marija | lv:procedures [2020/10/27 19:06] (patlaban) – ALG_rek_0610 marija | ||
|---|---|---|---|
| Rinda 1: | Rinda 1: | ||
| - | < | + | < |
| - | ALTER PROCEDURE dbo.int_hooldus_klient_pvn @aeg1 datetime, @aeg2 datetime AS | + | <?xml version=" |
| - | + | < | |
| - | --static vars | + | xmlns: |
| - | DECLARE @vat_declar_name nvarchar(200) SET @vat_declar_name | + | xmlns: |
| - | DECLARE @stat_vat decimal SET @stat_vat | + | xmlns: |
| - | DECLARE @lim decimal | + | xmlns:js=" |
| - | DECLARE @account int SET @account | + | exclude-result-prefixes="msxsl js fo"> |
| - | DECLARE @i int SET @i = 1 | + | < |
| - | DECLARE @x nvarchar(10) SET @x = '1' | + | <xsl:decimal-format name=" |
| - | DECLARE @y nvarchar(10) SET @y = '1' | + | < |
| - | + | < | |
| - | --numbers helper | + | < |
| - | DECLARE @numbers TABLE (n int) INSERT INTO @numbers | + | < |
| - | | + | < |
| - | FROM master..spt_values t1 | + | 1.42287181 |
| - | | + | </ |
| - | --vat summary | + | < |
| - | DECLARE @vat_declar_sums TABLE | + | 1.42287181 |
| - | ( | + | </ |
| - | | + | < |
| - | | + | 0.702804 |
| - | | + | </ |
| - | | + | < |
| - | | + | < |
| - | | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | ) | + | < |
| - | INSERT INTO @vat_declar_sums | + | < |
| - | | + | < |
| - | | + | < |
| - | WHERE kood=@vat_declar_name | + | < |
| - | | + | < |
| - | ORDER BY number | + | </ |
| - | + | </ | |
| - | --account range elements | + | </ |
| - | DECLARE @elements TABLE (nr nvarchar(30), e nvarchar(200)) | + | < |
| - | INSERT INTO @elements | + | < |
| - | SELECT | + | < |
| - | | + | < |
| - | | + | < |
| - | FROM @numbers, @vat_declar_sums | + | </ |
| - | | + | </ |
| - | AND SUBSTRING('+' | + | < |
| - | AND range_formula LIKE '_%' | + | < |
| - | AND (row_type | + | < |
| - | + | | |
| - | --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 | + | |
| - | | + | </ |
| - | | + | < |
| - | nr, | + | < |
| - | CASE WHEN CHARINDEX(':' | + | </ |
| - | CASE WHEN CHARINDEX(':', | + | </ |
| - | | + | </ |
| - | + | < | |
| - | --update vat declaration sums according | + | < |
| - | WHILE @i <= (SELECT COUNT(row_nr) FROM @element_ranges) | + | < |
| - | | + | < |
| - | | + | </ |
| - | SET @y = (SELECT (CASE WHEN ISNUMERIC(er.e_end) | + | < |
| - | + | < | |
| - | | + | </ |
| - | | + | </ |
| - | | + | |
| - | 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 | + | </ |
| - | ),0) | + | < |
| - | | + | < |
| - | AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr | + | < |
| - | UPDATE @vat_declar_sums SET doc_sum | + | < |
| - | | + | </ |
| - | | + | < |
| - | 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 | + | </ |
| - | | + | < |
| - | WHERE ISNUMERIC(vat_code) | + | < |
| - | | + | < |
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | <xsl:for-each select="/ | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | | ||
| + | | ||
| + | } else { | ||
| + | | ||
| + | } | ||
| + | | ||
| + | |||
| + | < | ||
| + | < | ||
| + | < | ||
| + | |||
| + | < | ||
| + | body { | ||
| + | text-align: | ||
| + | font-family: | ||
| + | font-size: | ||
| + | } | ||
| + | |||
| + | .container { | ||
| + | display: | ||
| + | text-align: | ||
| + | width: | ||
| + | margin: | ||
| + | } | ||
| + | |||
| + | .main_table { | ||
| + | width: | ||
| + | border-collapse: | ||
| + | } | ||
| + | |||
| + | .main_table th { | ||
| + | font-size: | ||
| + | font-family: | ||
| + | font-weight: | ||
| + | text-align: | ||
| + | padding: | ||
| + | border:1px solid #B4B4B4; | ||
| + | } | ||
| + | |||
| + | .main_table td { | ||
| + | font-size: | ||
| + | font-family: | ||
| + | padding: | ||
| + | border:1px solid #B4B4B4; | ||
| + | } | ||
| + | |||
| + | .main_table td.nos, .main_table td.kods, .main_table th.nos, .main_table th.kods { | ||
| + | | ||
| + | } | ||
| + | |||
| + | .main_table tr.cont { | ||
| + | height: | ||
| + | } | ||
| + | |||
| + | .left { | ||
| + | float: | ||
| + | padding: | ||
| + | font-size: | ||
| + | } | ||
| + | |||
| + | .right { | ||
| + | float: | ||
| + | text-align: | ||
| + | font-size: | ||
| + | } | ||
| + | .left2 { | ||
| + | float: | ||
| + | text-align: | ||
| + | font-size: | ||
| + | } | ||
| + | |||
| + | .right_small { | ||
| + | float: | ||
| + | text-align: | ||
| + | } | ||
| + | |||
| + | .spacer { | ||
| + | height: | ||
| + | clear: | ||
| + | } | ||
| + | |||
| + | .divider { | ||
| + | clear: | ||
| + | } | ||
| + | |||
| + | .pbreak { | ||
| + | page-break-after: | ||
| + | } | ||
| + | |||
| + | h4.pb { | ||
| + | display: | ||
| + | } | ||
| + | </ | ||
| + | </head> | ||
| + | < | ||
| + | < | ||
| + | <div class=" | ||
| + | <div style=" | ||
| + | <IMG height=" | ||
| + | </ | ||
| + | <div class=" | ||
| + | <br /> | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | </ | ||
| + | < | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | <xsl:if test=" | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | < | ||
| + | <br /> | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | |||
| + | | ||
| + | |||
| + | <div class=" | ||
| + | |||
| + | <div class=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | | ||
| + | |||
| + | <div class=" | ||
| + | < | ||
| + | < | ||
| + | <xsl:if test="/ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | |||
| + | <div class=" | ||
| + | |||
| + | <table class=" | ||
| + | <tr class=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | <th> < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | <xsl:if test=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| | | ||
| - | + | < | |
| - | | + | < |
| - | | + | < |
| - | | + | </ |
| - | 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 | + | </ |
| - | ),0) | + | </ |
| - | WHERE ISNUMERIC(vat_code) = 1 AND c_class | + | |
| - | | + | < |
| + | |||
| + | < | ||
| + | < | ||
| + | < | ||
| + | <td class=" | ||
| + | <td class=" | ||
| + | <td align=" | ||
| + | <td align=" | ||
| + | <xsl:if test=" | ||
| + | < | ||
| + | | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | <td align=" | ||
| + | <td align=" | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | <td class=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | < | ||
| + | < | ||
| + | <div style=" | ||
| + | <div style=" | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | | ||
| + | <div class=" | ||
| + | |||
| + | < | ||
| + | <div style=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | <th class=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | <th> < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | <th> < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | <xsl:if test=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| | | ||
| - | UPDATE @vat_declar_sums SET doc_sum = doc_sum + | + | |
| - | ISNULL(( | + | <xsl:when test="/ |
| - | SELECT SUM(ISNULL(baas1kreedit, | + | <xsl: |
| - | FROM fin_kanded_read WITH (NOLOCK) | + | </xsl: |
| - | 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) | + | </xsl: |
| - | WHERE ISNUMERIC(vat_code) = 0 AND c_class = 4 and row_type=1 | + | </tr> |
| - | AND nr = (SELECT er.nr FROM @element_ranges er WHERE er.row_nr = @i) | + | </xsl:if> |
| - | + | | |
| - | UPDATE @vat_declar_sums SET doc_sum = doc_sum + | + | <xsl:if test=" |
| - | ISNULL(( | + | |
| - | SELECT SUM(ISNULL(baas1kreedit, | + | |
| - | FROM fin_kanded_read WITH (NOLOCK) | + | |
| - | WHERE CAST(r_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) | + | </xsl:for-each> |
| - | AND konto BETWEEN @x AND @y | + | |
| - | and (baas1kreedit > 0 or baas1deebet | + | </table> |
| - | ),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, | + | |
| - | 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 | + | |
| - | 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, | + | |
| - | 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 | + | |
| - | | + | |
| - | 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, | + | |
| - | 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 | + | |
| - | AND kmkood | + | |
| - | ),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, | + | |
| - | 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, | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_3' | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | |
| - | FROM fin_kmkoodid fk | + | |
| - | WHERE lisainfo LIKE ' | + | |
| - | + | ||
| - | 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, | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | |
| - | FROM fin_kmkoodid fk | + | |
| - | WHERE lisainfo LIKE ' | + | |
| - | + | ||
| - | + | ||
| - | 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, | + | |
| - | doc_vat decimal(15, | + | |
| - | doc_date nvarchar(30), | + | |
| - | vatc_prepayment decimal(15, | + | |
| - | ) | + | |
| - | + | ||
| - | 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, | + | |
| - | doc_vat decimal(15, | + | |
| - | doc_date nvarchar(30), | + | |
| - | vatc_prepayment decimal(15, | + | |
| - | ) | + | |
| - | + | ||
| - | 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, | + | |
| - | doc_vat decimal(15, | + | |
| - | doc_date nvarchar(30), | + | |
| - | doc_sum_total decimal(15, | + | |
| - | vatc_prepayment decimal(15, | + | |
| - | ) | + | |
| - | + | ||
| - | Declare @pvn1_1_top_sums_supplier TABLE | + | |
| - | ( | + | |
| - | + | ||
| - | vat_reg_nr nvarchar(30), | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | + | ||
| - | Declare @pvn1_1_sums_supplier TABLE | + | |
| - | ( | + | |
| - | + | ||
| - | vat_reg_nr nvarchar(30), | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | 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, | + | |
| - | doc_vat decimal(15, | + | |
| - | 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, | + | |
| - | doc_vat decimal(15, | + | |
| - | doc_date nvarchar(30) | + | |
| - | ,rn int | + | |
| - | ) | + | |
| - | | + | |
| - | ( | + | |
| - | dok_veids nvarchar(32), | + | |
| - | client_name nvarchar(30), | + | |
| - | vat_reg_nr nvarchar(32), | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | declare @pvn_1_1_v_totals2 TABLE | + | |
| - | ( | + | |
| - | dok_veids nvarchar(32), | + | |
| - | client_name nvarchar(30), | + | |
| - | vat_reg_nr nvarchar(32), | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | | + | |
| - | ( | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | doc_nr nvarchar(255) | + | |
| - | ) | + | |
| - | | + | |
| - | ( | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | insert @pvn1_1_temp | + | |
| - | | + | |
| - | (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, | + | |
| - | 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!=' | + | |
| - | CAST((ROUND(ISNULL(summa, | + | |
| - | isnull(cast((ROUND(ISNULL(summa, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | (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, | + | |
| - | 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, | + | |
| - | | + | |
| - | 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!=' | + | |
| - | CAST((ROUND(ISNULL(summa * (-1), 0), 4)) AS decimal(15, | + | |
| - | 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), | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | (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 | + | |
| - | | + | |
| - | + | ||
| - | insert @pvn1_1_temp --purchase invoices | + | |
| - | SELECT | + | |
| - | isnull((SELECT TOP 1 dar_veids FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood),' | + | |
| - | (SELECT TOP 1 do_limit FROM @pvn11_vat_codes vc WHERE vc.code = oar.kmkood) AS do_limit, | + | |
| - | ' | + | |
| - | 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, | + | |
| - | (CASE | + | |
| - | WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | THEN | + | |
| - | CAST(oar.summa * ISNULL(oa.kurssbv1, | + | |
| - | WHEN | + | |
| - | ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | THEN | + | |
| - | CAST(oar.summa * ISNULL(oa.kurssbv1, | + | |
| - | when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | then | + | |
| - | CAST((oar.a_summa * ISNULL(oa.kurssbv1, | + | |
| - | 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), | + | |
| - | THEN | + | |
| - | CAST(oar.summa * ISNULL(oa.kurssbv1, | + | |
| - | WHEN | + | |
| - | ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | THEN | + | |
| - | --CAST(oar.summa * ISNULL(oa.kurssbv1, | + | |
| - | cast(km as decimal(15, | + | |
| - | when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | then | + | |
| - | CAST(oar.km * ISNULL(oa.kurssbv1, | + | |
| - | /* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = oar.kmkood), | + | |
| - | when (oar.konto=@account) then CAST(oar.summa * ISNULL(oa.kurssbv1, | + | |
| - | END) AS doc_vat, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | (select sum(summa) from or_arved_ettemaksud mae where mae.arve=oar.number and mae.kmk=oar.kmkood) | + | |
| - | | + | |
| - | WHERE CAST(oa.kande_aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) | + | |
| - | AND (oa.kinnitatud = ' | + | |
| - | 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=' | + | |
| - | --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, | + | |
| - | ' | + | |
| - | 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, | + | |
| - | --CAST((otr.summa_p * (@stat_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, | + | |
| - | ELSE CAST((otr.summa_p | + | |
| - | END) AS doc_sum, | + | |
| - | (CASE | + | |
| - | | + | |
| - | THEN CAST(otr.summa_p * ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = otr.kmk), 0)) / 100) AS decimal(15, | + | |
| - | ELSE CAST((otr.summa_p | + | |
| - | END) AS doc_vat, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 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, | + | |
| - | ' | + | |
| - | 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, | + | |
| - | ELSE CAST((oae.summa | + | |
| - | 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, | + | |
| - | - | + | |
| - | isnull(cast((select sum(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = oae.kmk), | + | |
| - | ELSE isnull(CAST((oae.summa / (1 + @stat_vat / 100) * (@stat_vat / 100)) AS decimal(15, | + | |
| - | - | + | |
| - | 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, | + | |
| - | END) AS doc_vat, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 0 | + | |
| - | from or_arved_ettemaksud oae | + | |
| - | where (select | + | |
| - | 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, | + | |
| - | ' | + | |
| - | 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, | + | |
| - | (CASE WHEN (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code=fkr.kmkood), | + | |
| - | THEN | + | |
| - | cast(CAST(ISNULL(fkr.summa, | + | |
| - | | + | |
| - | cast(CAST(ISNULL(fkr.reakm, | + | |
| - | END) AS doc_vat, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 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=' | + | |
| - | 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, | + | |
| - | ' | + | |
| - | 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)), | + | |
| - | + | ||
| - | | + | |
| - | WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | THEN | + | |
| - | CAST(fkr.summa * isnull(fkr.r_kurss, | + | |
| - | WHEN | + | |
| - | ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | THEN | + | |
| - | CAST(fkr.summa * isnull(fkr.r_kurss, | + | |
| - | when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | then | + | |
| - | CAST((fkr.a_summa * isnull(fkr.r_kurss, | + | |
| - | END) as doc_sum_wo_vat, | + | |
| - | (CASE | + | |
| - | WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | THEN | + | |
| - | CAST( | + | |
| - | ((fkr.summa) * isnull(fkr.r_kurss, | + | |
| - | ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | -- isnull(cast((select sum(summa) * (ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr2.kmk), | + | |
| - | WHEN | + | |
| - | ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | THEN | + | |
| - | --CAST((fkr.summa * isnull(fk.kurssbv1, | + | |
| - | cast(reakm as decimal(15, | + | |
| - | when ((ISNULL((SELECT TOP 1 proportion FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | then | + | |
| - | CAST((fkr.reakm *isnull(fkr.r_kurss, | + | |
| - | /* ((ISNULL((SELECT TOP 1 rate FROM @pvn11_vat_codes WHERE code = fkr.kmkood), | + | |
| - | END), | + | |
| - | + | ||
| - | cast(isnull(fkr.aeg, | + | |
| - | ' | + | |
| - | from fin_kulutused_read fkr with(nolock) | + | |
| - | left join fin_kulutused fk on fk.number=fkr.number | + | |
| - | where | + | |
| - | | + | |
| - | and fkr.kmkood in (select code from @pvn11_vat_codes) | + | |
| - | AND (kinnitatud=' | + | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | insert @pvn1_1_values_by_doc | + | |
| - | select | + | |
| - | row_number() over(order by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | , | + | |
| - | , | + | |
| - | , case when (dok_veids | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , case | + | |
| - | when (dok_veids!=' | + | |
| - | then sum(doc_sum) | + | |
| - | else | + | |
| - | (case | + | |
| - | when | + | |
| - | (sum(doc_sum) | + | |
| - | then 0 | + | |
| - | else | + | |
| - | sum(doc_sum) * (-1) end) | + | |
| - | end - | + | |
| - | case when (dok_veids!=' | + | |
| - | then ( | + | |
| - | CASE | + | |
| - | WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code), | + | |
| - | THEN | + | |
| - | CAST(ISNULL(vatc_prepayment, | + | |
| - | WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code), | + | |
| - | THEN | + | |
| - | isnull(cast(vatc_prepayment | + | |
| - | END | + | |
| - | ) | + | |
| - | else | + | |
| - | 0 | + | |
| - | end | + | |
| - | , case | + | |
| - | when (dok_veids!=' | + | |
| - | then sum(doc_vat) | + | |
| - | else | + | |
| - | (case when (sum(doc_vat) | + | |
| - | end - | + | |
| - | case | + | |
| - | when (dok_veids!=' | + | |
| - | then ( | + | |
| - | + | ||
| - | CASE | + | |
| - | WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code), | + | |
| - | THEN | + | |
| - | isnull((cast((vatc_prepayment * cast((0 + (select POORD / 100 from fin_kmkoodid where kood=a.vat_code)) as decimal(15, | + | |
| - | WHEN (ISNULL((SELECT reverse_rate FROM @pvn11_vat_codes WHERE CODE=A.vat_code), | + | |
| - | THEN | + | |
| - | isnull((cast(vatc_prepayment - (vatc_prepayment | + | |
| - | | + | |
| - | + | ||
| - | ) | + | |
| - | else | + | |
| - | 0 | + | |
| - | end | + | |
| - | , | + | |
| - | , | + | |
| - | case | + | |
| - | when (a.dok_veids=' | + | |
| - | 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=' | + | |
| - | 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)), | + | |
| - | else | + | |
| - | 0 | + | |
| - | end | + | |
| - | , vatc_prepayment | + | |
| - | from @pvn1_1_temp a group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | + | ||
| - | --update @pvn1_1_values_by_doc set dar_veids = ' | + | |
| - | --update @pvn1_1_values_by_doc set dok_veids = ' | + | |
| - | + | ||
| - | insert @pvn_1_1_totals | + | |
| - | select sum(doc_sum), | + | |
| - | insert @pvn1_1_sums_supplier | + | |
| - | select | + | |
| - | isnull(vat_reg_nr,' | + | |
| - | , | + | |
| - | , | + | |
| - | from @pvn1_1_values_by_doc | + | |
| - | where isnull(vat_reg_nr,' | + | |
| - | GROUP BY vat_reg_nr | + | |
| - | + | ||
| - | insert @pvn1_1_top_table | + | |
| - | select | + | |
| - | dar_veids | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , rn | + | |
| - | from @pvn1_1_values_by_doc | + | |
| - | where (doc_sum_total | + | |
| - | group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | + | ||
| - | 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 | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , rn | + | |
| - | from @pvn1_1_values_by_doc | + | |
| - | where (dar_veids in (' | + | |
| - | and directo_nr+' | + | |
| - | group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | --select | + | <table class=" |
| - | delete from @pvn1_1_values_by_doc where rn in (select rn from @pvn1_1_top_r_table) | + | < |
| + | </ | ||
| - | insert @pvn_1_1_v_totals | + | <table class=" |
| - | select | + | < |
| - | ' | + | < |
| - | , | + | |
| | | ||
| - | , | + | <div style=" |
| - | , | + | |
| - | , | + | < |
| - | 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, | + | <table width=" |
| - | from @pvn1_1_sums_supplier z | + | |
| - | where (doc_sum | + | |
| - | and cast(doc_sum | + | < |
| - | and vat_reg_nr!='' | + | < |
| - | + | < | |
| - | + | < | |
| - | insert @pvn_1_1_v_totals2 | + | < |
| - | select 'V', client_name, | + | < |
| - | 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 | + | </ |
| - | , | + | < |
| - | ,'' | + | < |
| - | from @pvn1_1_values_by_doc z | + | |
| - | + | < | |
| - | /* | + | </ |
| - | select | + | </ |
| - | cast(sum(doc_sum * (-1)) as decimal(15, | + | </ |
| - | , | + | </ |
| - | from @pvn1_1_values_by_doc z | + | < |
| - | where (doc_sum between (@lim * (-1)) and @lim) | + | < |
| - | and vat_reg_nr not in (select | + | < |
| - | and dar_veids not in ('R1','R2','R3','R4',' | + | < |
| - | UNION | + | < |
| - | select | + | < |
| - | cast(sum(doc_sum) as decimal(15, | + | </ |
| - | , | + | </ |
| - | 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 | + | < |
| - | UNION | + | < |
| - | select | + | |
| - | cast(sum(doc_sum) as decimal(15, | + | </ |
| - | , | + | </ |
| - | 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 | + | </ |
| - | and dar_veids not in ('R1',' | + | </ |
| - | UNION | + | </ |
| - | select | + | < |
| - | cast(sum(doc_sum) as decimal(15,4)) | + | < |
| - | , | + | <td align=" |
| - | 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 | + | |
| - | + | < | |
| - | ----------------- | + | < |
| - | ------ 1-2 ------ | + | </ |
| - | ----------------- | + | |
| - | + | < | |
| - | DECLARE @pvn12_vat_codes TABLE | + |   |
| - | ( | + | </ |
| - | code nvarchar(30), | + | </xsl: |
| - | rate decimal, | + | </td> |
| - | | + | </ |
| - | | + | </ |
| - | | + | |
| - | | + | < |
| - | | + | < |
| - | info nvarchar(200), | + | < |
| - | | + | < |
| - | | + | </ |
| - | + | </ | |
| - | ) | + | </ |
| - | INSERT INTO @pvn12_vat_codes | + | </ |
| - | | + | </ |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='DARTIPS1_2' | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | < |
| - | FROM fin_kmkoodid fk | + | |
| - | WHERE lisainfo LIKE ' | + | <xsl:if test=" |
| - | + | < | |
| - | DECLARE @pvn12_currencies TABLE (currency nvarchar(3)) | + | <td width=" |
| - | 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), | + | < |
| - | | + | <td width=" |
| - | | + | < |
| - | | + | < |
| - | | + | < |
| - | | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | | + | < |
| - | | + | < |
| - | | + | <td width=" |
| - | rate decimal(28, | + | < |
| - | | + | < |
| - | | + | < |
| - | | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | ) | + | </ |
| - | + | < | |
| - | --pvn12 temp table | + | <td width=" |
| - | DECLARE @pvn12_temp_table TABLE | + | < |
| - | ( | + | < |
| - | | + | < |
| - | | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | vat_code nvarchar(30), | + | <xsl:if test="/ |
| - | | + | < |
| - | doc_type nvarchar(30), | + | <td width=" |
| - | | + | < |
| - | | + | < |
| - | | + | < |
| - | doc_sum decimal(15, | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | | + | </ |
| - | | + | <xsl:if test="/ |
| - | | + | < |
| - | | + | <td width=" |
| - | | + | < |
| - | | + | < |
| - | ) | + | < |
| - | + | </ | |
| - | INSERT INTO @pvn12_temp_table --purchase invoices - temp | + | </td> |
| - | SELECT | + | |
| - | (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code | + | </ |
| - | '' | + | </ |
| - | CAST(oa.number | + | </div> |
| - | CAST(oa.hankija_arve AS nvarchar(30)) AS client_doc_nr, | + | |
| - | CAST(oar.kmkood AS nvarchar(30)) AS vat_code, | + | < |
| - | ' | + | |
| - | CAST(oa.tyyp AS nvarchar(30)) AS doc_type, | + | <xsl:if test=" |
| - | 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, | + | <td width=" |
| - | '--' AS country_code, | + | < |
| - | CAST(((select | + | </ |
| - | (CASE | + | </ |
| - | WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = oar.kmkood), | + | </ |
| - | THEN CAST((select | + | < |
| - | ELSE CAST((select | + | <td width=" |
| - | END) AS doc_vat, | + | < |
| - | | + | </td> |
| - | | + | </ |
| - | 1 AS alt_rate, | + | < |
| - | ' | + | <tr> |
| - | | + | |
| - | | + | < |
| - | (select sum(summa) from or_arved_ettemaksud oae where oae.arve=oa.number and oae.kmk=oar.kmkood) | + | </td> |
| - | | + | </ |
| - | | + | </ |
| - | | + | <tr> |
| - | | + | <td width=" |
| - | | + | <xsl: |
| - | UNION | + | </ |
| - | SELECT | + | </ |
| - | | + | <xsl:if test="/ |
| - | (SELECT TOP 1 do_limit FROM @pvn12_vat_codes vc WHERE vc.code = otr.kmk) AS do_limit, | + | < |
| - | | + | <td width=" |
| - | | + | < |
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | <xsl:if test="/ | ||
| + | <tr> | ||
| + | <td width=" | ||
| + | < | ||
| + | | ||
| + | </tr> | ||
| + | </ | ||
| + | </table> | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | |||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | <!--<div style=" | ||
| + | <div style=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | |||
| + | < | ||
| + | <table width=" | ||
| + | <!-- <xsl:if test=" | ||
| + | < | ||
| + | <td style=" | ||
| + |   | ||
| + | </td> | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </td> | ||
| + | </tr> | ||
| + | </ | ||
| + | <tr> | ||
| + | <td style=" | ||
| + |   | ||
| + | </td> | ||
| + | <td style=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + |   | ||
| + | < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | </ | ||
| + | </td> | ||
| + | </ | ||
| + | <tr> | ||
| + | <td style=" | ||
| + |   | ||
| + | </td> | ||
| + | <td style=" | ||
| + | < | ||
| + | </td> | ||
| + | </tr> | ||
| + | </ | ||
| + | | ||
| + | </ | ||
| + | | ||
| + | </ | ||
| + | <!--<xsl:if test=" | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | Neapmaksāto rēķinu kopsumma: < | ||
| + | Neapmaksāto rēķinu kopsumma: < | ||
| + | </ | ||
| + | < | ||
| + | Neapmaksāto rēķinu kopsumma: < | ||
| + | Neapmaksāto rēķinu kopsumma: < | ||
| + | </ | ||
| + | </ | ||
| + | Neapmaksāto rēķinu kopsumma: < | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | | ||
| + | Unpaid invoices total:< | ||
| + | </ | ||
| + | < | ||
| + | Unpaid invoices total:< | ||
| + | Unpaid invoices total:< | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | </ | ||
| + | <br /> | ||
| + | | ||
| + | | ||
| + | </ | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | < | ||
| + | </ | ||
| + | | ||
| + | | ||
| + | | ||
| + | < | ||
| + | <td style=" | ||
| + | <img SRC=" | ||
| + | </ | ||
| + | </ | ||
| + | | ||
| + | | ||
| | | ||
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | --CAST(otr.summa_p AS decimal(15, | + | </ |
| - | | + | </body> |
| - | ' | + | |
| - | (CASE | + | </ |
| - | WHEN ((ISNULL((SELECT TOP 1 reverse_rate FROM @pvn12_vat_codes WHERE code = otr.kmk), 0)) != 0) | + | </xsl: |
| - | THEN CAST(otr.summa_p AS decimal(15, | + | < |
| - | ELSE CAST((otr.summa_p | + | var cipariEng=['' |
| - | END) AS doc_sum, | + | var padsmitiEng=['ten ',' |
| - | (CASE | + | var desmitiEng=['' |
| - | 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, | + | |
| - | | + | |
| - | | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 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, | + | |
| - | '' | + | |
| - | 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' | + | |
| - | 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, | + | |
| - | '--' | + | |
| - | 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, | + | |
| - | ELSE CAST((oar.summa)*(@stat_vat/ | + | |
| - | END) AS doc_vat, | + | |
| - | CAST(ISNULL(oa.kurssbv1, | + | |
| - | CAST(oa.valuuta AS nvarchar(30)) AS currency, | + | |
| - | 1 AS alt_rate, | + | |
| - | | + | |
| - | CAST(oa.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 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 | + | |
| + | |||
| + | // pirmais cipars no labaas | ||
| + | if (str.charAt(1)==' | ||
| + | | ||
| + | else res=cipariEng[parseInt(str.charAt(2))]; | ||
| + | |||
| + | // otrais cipars no labaas | ||
| + | if (str.charAt(0)==' | ||
| + | | ||
| + | else if (str.charAt(0)!=' | ||
| + | | ||
| + | else // ja nav simtu | ||
| + | | ||
| + | |||
| + | // treshais cipars no labaas | ||
| + | res=cipariEng[parseInt(str.charAt(0))]+res; | ||
| + | return res; | ||
| + | } | ||
| - | */ | + | function numTextEng(skaitlis,nauda) { |
| - | INSERT INTO @pvn12_main_table --purchase invoices - main | + | |
| - | SELECT | + | |
| - | dar_veids | + | |
| - | , do_limit | + | |
| - | , | + | |
| - | , | + | |
| - | , '' | + | |
| - | , directo_table | + | |
| - | , | + | pirmspStr=skaitlis; |
| - | , | + | aizpStr='0'; |
| - | , | + | } else { |
| - | , | + | pirmspStr=skaitlis.substring(0,punkts); |
| - | , case | + | aizpStr=skaitlis.substring(punkts+1); |
| - | when (directo_table='purchase_invoice') | + | } |
| - | then | + | |
| - | else | + | |
| - | SUM(doc_sum) | + | pirmspStr=pirmspStr.substring(1); |
| - | end | + | minus='minus '; |
| - | , case | + | } |
| - | when (directo_table='purchase_invoice') | + | |
| - | then | + | |
| - | else | + | fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1); |
| - | SUM(doc_vat) | + | } |
| - | end, rate, currency, alt_rate, alt_currency, | + | |
| - | | + | |
| - | | + | |
| - | --doc_sum > 0 or doc_sum < 0 or | + | |
| - | directo_table not in ('prepayment') | + | if (parseInt(fullStr,10)==1) resStr+=' |
| - | | + | else if (parseInt(fullStr,10)!=0) resStr+='euros'; |
| - | + | | |
| - | + | } else if (parseInt(fullStr,10)!=0) { | |
| - | + | resStr+=nauda; | |
| - | + | } else resStr+='Null '+nauda; | |
| - | + | ||
| - | + | | |
| - | + | tempStr=fullStr.substring(7); // simti | |
| - | + | tempRes=simtiEng(tempStr); | |
| - | + | resStr=tempRes+resStr; | |
| - | + | } | |
| - | INSERT INTO @pvn12_main_table | + | |
| - | /* | + | |
| - | | + | tempStr=fullStr.substring(4,7); // tūkstoši |
| - | | + | tempRes=simtiEng(tempStr); |
| - | '' | + | if ((tempStr.charAt(2)=='1' |
| - | | + | |
| - | CAST((SELECT TOP 1 dokument FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS client_doc_nr, | + | resStr=tempRes+resStr; |
| - | | + | } |
| - | ' | + | |
| - | ' | + | |
| - | CAST((SELECT TOP 1 hankija_nimi FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number) AS nvarchar(30)) AS client_name, | + | tempStr=fullStr.substring(1,4); // miljoni |
| - | 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, | + | tempRes=simtiEng(tempStr); |
| - | | + | if ((tempStr.charAt(2)=='1') && |
| - | CAST(summa*ISNULL(kurssbv1, | + | |
| - | CAST(summa*(@stat_vat/100)*ISNULL(kurssbv1, | + | resStr=tempRes+resStr; |
| - | CAST(ISNULL(kurssbv1, | + | } |
| - | | + | |
| - | 1 AS alt_rate, | + | |
| - | | + | if (fullStr.charAt(0)==' |
| - | | + | |
| - | | + | } |
| - | | + | |
| - | AND (kinnitatud=' | + | |
| - | AND (EXISTS(SELECT | + | resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1); |
| - | */ | + | } |
| - | SELECT | + | |
| - | (SELECT TOP 1 dar_veids FROM @pvn12_vat_codes vc WHERE vc.code | + | |
| - | | + | |
| - | CAST(fkr.number AS nvarchar(30)) AS doc_nr, | + | resStr+=' |
| - | cast(fkr.dokument as nvarchar(30)) AS client_doc_nr, | + | if (santimi==1) { |
| - | cast(fkr.kmkood as nvarchar(30)) AS vat_code, | + | |
| - | ' | + | } else { |
| - | ' | + | |
| - | cast(fkr.hankija_nimi as nvarchar(30)) AS client_name, | + | } |
| - | cast((select kmregnr from hankijad where kood=fkr.hankija_kood) as nvarchar(30)), | + | } else { |
| - | '--', | + | |
| - | cast(fkr.summa * isnull(fkr.r_kurss, | + | } |
| - | | + | |
| - | cast(isnull(fkr.r_kurss, | + | |
| - | cast(isnull(fkr.r_valuuta, | + | |
| - | 1, | + | |
| - | ' | + | |
| - | CAST(fk.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, | + | |
| - | REPLACE(CONVERT(VARCHAR, | + | |
| - | 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, | + | |
| - | + | ||
| - | UPDATE @pvn12_main_table | + | |
| - | SET alt_currency = | + | |
| - | | + | |
| - | WHEN country_code | + | |
| - | WHEN country_code = ' | + | |
| - | WHEN country_code = ' | + | |
| - | WHEN country_code = ' | + | |
| - | WHEN country_code = ' | + | |
| - | WHEN country_code = ' | + | |
| - | WHEN country_code | + | |
| - | WHEN country_code | + | |
| - | WHEN country_code | + | |
| - | ELSE ' | + | |
| - | + | ||
| - | UPDATE @pvn12_main_table | + | |
| - | SET alt_rate = | + | |
| - | | + | |
| - | FROM curr_rates | + | |
| - | WHERE curr_rates.kood | + | |
| - | 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 | + | |
| - | UPDATE @pvn12_main_table SET do_limit | + | |
| - | + | ||
| - | ----------------- | + | |
| - | ------ 1-3 ------ | + | |
| - | ----------------- | + | |
| - | --izveidojam PVN 13 kodu tabulu | + | |
| - | | + | |
| - | ( | + | |
| - | code nvarchar(30), | + | |
| - | rate decimal, | + | |
| - | description nvarchar(200), | + | |
| - | sales_account nvarchar(30), | + | |
| - | purchase_account nvarchar(30), | + | |
| - | info nvarchar(200), | + | |
| - | dar_veids nvarchar(5), | + | |
| - | | + | |
| - | ) | + | |
| - | INSERT INTO @pvn13_vat_codes | + | |
| - | SELECT kood, ilmakm, seletus, myykkonto, ostuKMkonto, | + | |
| - | CAST(ISNULL((SELECT TOP 1 replace(sisu,' | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood='LIMITS' | + | |
| - | FROM fin_kmkoodid fk | + | |
| - | WHERE lisainfo LIKE '%pvn13%' | + | |
| - | + | ||
| - | + | ||
| - | Declare @pvn1_3_temp TABLE | + | |
| - | ( | + | |
| - | | + | |
| - | do_limit varchar(2), | + | |
| - | dok_veids nvarchar(1), | + | |
| - | doc_nr nvarchar(30), | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | doc_vat decimal(15,4), | + | |
| - | doc_date nvarchar(30), | + | |
| - | | + | |
| - | | + | |
| - | ) | + | |
| - | 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_vat decimal(15,4), | + | |
| - | doc_date nvarchar(30), | + | |
| - | | + | |
| - | | + | |
| - | rn int | + | |
| - | ) | + | |
| - | Declare @pvn1_3_top_table TABLE | + | |
| - | ( | + | |
| - | dar_veids nvarchar(5), | + | |
| - | do_limit varchar(2), | + | |
| - | | + | |
| - | doc_nr nvarchar(30), | + | |
| - | vat_code nvarchar(30), | + | |
| - | | + | |
| - | vat_reg_nr nvarchar(30), | + | |
| - | doc_sum decimal(15, | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | rn int | + | |
| - | ) | + | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | Declare @pvn1_3_top_tablex TABLE | + | |
| - | ( | + | |
| - | dar_veids nvarchar(5), | + | |
| - | do_limit varchar(2), | + | |
| - | dok_veids nvarchar(1), | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | vat_reg_nr nvarchar(30), | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | doc_sum_total decimal(15, | + | |
| - | vatc_prepayment decimal(15, | + | |
| - | rn int | + | |
| - | ) | + | |
| - | Declare @tdeals TABLE | + | |
| - | ( | + | |
| - | dar_veids nvarchar(5), | + | |
| - | do_limit varchar(2), | + | |
| - | dok_veids nvarchar(1), | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | rn int | + | |
| - | ) | + | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | Declare @pvn1_3_top_sums_customer TABLE | + | |
| - | ( | + | |
| - | vat_reg_nr nvarchar(30), | + | } |
| - | | + | var cipari=['' |
| - | | + | var padsmiti=[' |
| - | ) | + | var desmiti=['','',' |
| - | Declare @pvn1_3_sums_customer TABLE | + | |
| - | ( | + | res=''; |
| | | ||
| - | vat_reg_nr nvarchar(30), | + | // pirmais cipars no labaas |
| - | | + | |
| - | doc_vat decimal(15,4) | + | res=padsmiti[parseInt(str.charAt(2))]; |
| - | ) | + | else res=cipari[parseInt(str.charAt(2))]; |
| - | declare @v_totals TABLE | + | |
| - | | + | |
| - | dok_veids nvarchar(32), | + | |
| - | client_name nvarchar(30), | + | res=' |
| - | | + | else if (str.charAt(0)!=' |
| - | doc_sum decimal(15,4), | + | res=' |
| - | doc_vat decimal(15, | + | else // ja nav simtu |
| - | | + | |
| - | | + | |
| - | ( | + | // treshais cipars no labaas |
| - | dok_veids nvarchar(32), | + | |
| - | | + | |
| - | | + | } |
| - | | + | |
| - | | + | |
| - | | + | |
| - | declare @t_totals TABLE | + | |
| - | ( | + | |
| - | doc_sum decimal(15,4), | + | |
| - | doc_vat decimal(15, | + | |
| - | | + | |
| - | --ielasam visas rēķina rindas kuras ir ar 1 - 3 atšifrējamajiem pvn kodiem | + | |
| - | insert @pvn1_3_temp | + | |
| - | | + | |
| - | | + | tempStr='' |
| - | (SELECT TOP 1 do_limit FROM @pvn13_vat_codes vc WHERE vc.code = mr_arved_read.kmk) AS do_limit, | + | |
| - | case when (isnull(ettemaks, | + | |
| - | | + | |
| - | 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, | + | |
| - | | + | pirmspStr=skaitlis; |
| - | isnull(cast((ROUND(ISNULL(summa, | + | aizpStr='0'; |
| - | | + | |
| - | , | + | pirmspStr=skaitlis.substring(0,punkts); |
| - | ,(select sum(summa) from mr_arved_ettemaksud mae where mae.arve=mr_arved_read.number and mae.kmk=mr_arved_read.kmk) | + | aizpStr=skaitlis.substring(punkts+1); |
| - | 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) | + | if (pirmspStr.charAt(0)==' |
| - | AND (kinnitatud | + | pirmspStr=pirmspStr.substring(1); |
| - | and kmk in (SELECT code FROM @pvn13_vat_codes) | + | minus='mīnus |
| - | and artikkel is not null | + | } |
| - | and mr_arved.kokku >=0 | + | |
| - | + | | |
| - | insert @pvn1_3_temp | + | fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1); |
| - | | + | } |
| - | (SELECT TOP 1 dar_veids FROM @pvn13_vat_codes vc WHERE vc.code | + | |
| - | | + | |
| - | ' | + | |
| - | CAST(ml.number AS nvarchar(30)) AS doc_nr, | + | |
| - | | + | |
| - | CAST((SELECT TOP 1 nimi FROM kliendid WITH (NOLOCK) WHERE (kliendid.kood=mlr.klient_kood)) AS nvarchar(30)) AS client_name, | + | |
| - | | + | |
| - | CAST((ROUND(mlr.tasuti, | + | |
| - | CAST(((ROUND(mlr.tasuti,2)/((ISNULL((SELECT TOP 1 rate FROM @pvn13_vat_codes WHERE code=mlr.kmk), | + | tempStr=fullStr.substring(7); // simti |
| - | REPLACE(CONVERT(VARCHAR, | + | tempRes=simti(tempStr); |
| - | CAST((ROUND(mlr.tasuti, | + | resStr=tempRes+resStr; |
| - | 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') | + | tempStr=fullStr.substring(4,7); // t�ksto�i |
| - | AND (ml.number=mlr.number) | + | tempRes=simti(tempStr); |
| - | and mlr.kmk in (SELECT code FROM @pvn13_vat_codes) | + | if ((tempStr.charAt(2)=='1' |
| - | AND ((ISNULL(NULLIF(mlr.ettemaks, | + | else if (parseInt(tempStr,10)!=0) tempRes+='tūkstoši |
| - | + | resStr=tempRes+resStr; | |
| - | insert @pvn1_3_values_by_doc | + | |
| - | select | + | |
| - | , | + | |
| - | , | + | tempStr=fullStr.substring(1,4); // miljoni |
| - | , | + | tempRes=simti(tempStr); |
| - | , | + | if ((tempStr.charAt(2)=='1') && |
| - | , | + | else if (parseInt(tempStr,10)!=0) tempRes+=' |
| - | , | + | resStr=tempRes+resStr; |
| - | , | + | } |
| - | | + | |
| - | , | + | |
| - | , | + | if (fullStr.charAt(0)=='1') resStr='viens miljards ' |
| - | , | + | else resStr=cipari[parseInt(fullStr.charAt(0),10)]+'miljardi |
| - | , | + | |
| - | from @pvn1_3_temp pvn13temp group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | insert @pvn1_3_sums_customer | + | |
| - | select | + | resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1); |
| - | isnull(vat_reg_nr,' | + | |
| - | , | + | |
| - | , | + | |
| - | from @pvn1_3_values_by_doc | + | |
| - | where isnull(vat_reg_nr,'' | + | resStr+=' '+santimi; |
| - | GROUP BY vat_reg_nr | + | tmpSant=santimi.toString(); |
| - | + | if ((tmpSant.charAt(tmpSant.length-1)=='1' | |
| - | --delete from @pvn1_3_temp where dok_veids in (' | + | |
| - | --only for anvol | + | } else { |
| - | declare @fin_kanded_read table | + | |
| - | ( | + | } |
| - | sum_wo_vat decimal(15, | + | |
| - | vat_sum decimal(15,4) | + | |
| - | ) | + | |
| - | insert @fin_kanded_read | + | |
| - | select | + | |
| - | 0,sum( | + | } |
| - | case | + | |
| - | when (baas1deebet < 0) then (baas1deebet | + | |
| - | when (baas1kreedit > 0) then (baas1kreedit) | + | |
| - | end | + | |
| - | ) - sum( | + | |
| - | case | + | |
| - | when (baas1deebet | + | |
| - | when (baas1kreedit < 0) then (baas1kreedit * (-1)) | + | |
| - | end | + | |
| - | ) from fin_kanded_read where tyyp=' | + | |
| - | union | + | |
| - | select | + | |
| - | sum( | + | |
| - | case | + | |
| - | when (baas1deebet < 0) then (baas1deebet | + | |
| - | 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' | + | |
| - | declare @pvn1_3tots table | + | |
| - | ( | + | |
| - | doc_sum decimal(15, | + | |
| - | doc_vat decimal(15, | + | |
| - | ) | + | |
| - | insert @pvn1_3tots | + | |
| - | SELECT CAST(SUM(doc_sum) | + | |
| - | | + | |
| - | insert @pvn1_3_top_table | + | |
| - | select | + | |
| - | dar_veids | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , 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, | + | |
| - | + | ||
| - | 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 | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , 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, | + | |
| - | 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 | + | |
| - | , | + | |
| - | , | + | |
| - | from @pvn1_3_top_table | + | |
| - | GROUP BY vat_reg_nr | + | |
| - | insert @v_totals | + | |
| - | select | + | |
| - | ' | + | |
| - | , | + | |
| - | + | ||
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | from @pvn1_3_sums_customer z | + | |
| - | where (doc_sum >= @lim or doc_sum <= (@lim * (-1))) | + | |
| - | and cast(doc_sum | + | |
| - | 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 | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , rn | + | |
| - | from @pvn1_3_values_by_doc | + | |
| - | where (doc_sum between | + | |
| - | group by dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | union | + | |
| - | select | + | |
| - | dar_veids | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , | + | |
| - | , 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, | + | |
| - | delete from @pvn1_3_values_by_doc where rn in (select rn from @tdeals) | + | |
| - | insert @t_totals | + | |
| - | select | + | |
| - | | + | |
| - | , | + | |
| - | from @tdeals z | + | |
| - | + | ||
| - | insert @x_totals | + | |
| - | select | + | |
| - | ' | + | |
| - | , '' | + | |
| - | , '' | + | |
| - | , | + | |
| - | , | + | |
| - | from @pvn1_3_top_tablex z | + | |
| - | --where (doc_sum_total >= @lim or doc_sum_total <= (@lim * (-1))) | + | |
| - | ----------------- | + | |
| - | ------ 2-1 ------ | + | |
| - | ----------------- | + | |
| - | + | ||
| - | DECLARE @pvn21_vat_codes TABLE | + | |
| - | ( | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | 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, | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | |
| - | CAST(ISNULL((SELECT TOP 1 sisu FROM yld_data yd WITH (NOLOCK) WHERE yd.kaart=fk.kood AND kood=' | + | |
| - | FROM fin_kmkoodid fk | + | |
| - | WHERE lisainfo LIKE ' | + | |
| - | + | ||
| - | 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, | + | |
| - | ) | + | |
| - | + | ||
| - | INSERT INTO @invoices | + | |
| - | /* | + | |
| - | | + | |
| - | '' | + | |
| - | '' | + | |
| - | | + | |
| - | number AS doc_nr, | + | |
| - | CAST(kmk AS nvarchar(30)) AS vat_code, | + | |
| - | | + | |
| - | 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) | + | |
| - | | + | |
| - | */ | + | |
| - | 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))), | + | |
| - | 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, | + | |
| - | 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)), | + | |
| - | mlr.number, | + | |
| - | mlr.kmk, | + | |
| - | cast(sum(summa_p) * (ISNULL(kurss_p, | + | |
| - | 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, | + | |
| - | and mlr.kmk in (select code from @pvn21_vat_codes) | + | |
| - | group by mlr.number, | + | |
| - | --UPDATE @invoices SET pazime | + | |
| - | --UPDATE @invoices SET do_limit | + | |
| - | + | ||
| - | DECLARE @invoices_grouped TABLE (pazime nvarchar(1), vat_reg_nr nvarchar(30), doc_nr nvarchar(30), | + | |
| - | 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 | + | |
| - | ), | + | |
| - | + | ||
| - | ( | + | |
| - | | + | |
| - | | + | |
| - | ) AS [pvn11_vat_codes], | + | |
| - | + | ||
| - | ( | + | |
| - | | + | |
| - | FOR XML PATH('code'),TYPE, ELEMENTS | + | |
| - | ) AS [pvn12_vat_codes], | + | |
| - | ( | + | |
| - | SELECT * FROM @pvn13_vat_codes | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn13_vat_codes], | + | |
| - | ( | + | |
| - | SELECT * FROM @pvn21_vat_codes | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn21_vat_codes], | + | |
| - | ( | + | |
| - | SELECT @vat_declar_name AS vat_declar_name, | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | (SELECT setting FROM settings WHERE id=' | + | |
| - | (SELECT setting FROM settings WHERE id=' | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | (SELECT setting FROM settings WHERE id=' | + | |
| - | | + | |
| - | | + | |
| - | (SELECT setting FROM settings WHERE id=' | + | |
| - | (SELECT setting FROM settings WHERE id='firma_aa' | + | |
| - | | + | |
| - | | + | |
| - | FROM settings WHERE id='firma_nimi' | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( | + | |
| - | 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, | + | |
| - | 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, | + | |
| - | | + | |
| - | where (doc_sum <> 0 or doc_vat <> 0) | + | |
| - | FOR XML PATH('doc'), TYPE, ELEMENTS | + | |
| - | ) AS [pvn11_docs_above], | + | |
| - | ( | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | ) AS [pvn11_docs_above], | + | |
| - | ( | + | |
| - | | + | |
| - | FROM @pvn_1_1_t_totals FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( | + | ]]></msxsl: |
| - | SELECT doc_sum AS sum_total, | + | |
| - | FROM @pvn_1_1_totals | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | /* | + | |
| - | ( | + | |
| - | SELECT CAST(SUM((doc_sum) * (-1)) AS decimal(15, | + | |
| - | FROM @pvn13_main_table_11 | + | |
| - | FOR XML PATH(' | + | |
| - | ),--/pvn11 */ | + | |
| - | ( --pvn12 | + | |
| - | SELECT dar_veids, do_limit, doc_nr, client_doc_nr, | + | |
| - | FROM @pvn12_main_table | + | |
| - | where (doc_sum <> 0) | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn12_docs], | + | |
| - | ( | + | |
| - | SELECT CAST(SUM(doc_sum) AS decimal(15, | + | |
| - | FROM @pvn12_main_table | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( -- rates | + | |
| - | SELECT currency, (SELECT TOP 1 CAST(kurss1 AS decimal(28, | + | |
| - | FROM @pvn12_currencies | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn12_currency_rates], --/pvn12 | + | |
| - | ( --pvn13 | + | |
| - | SELECT dar_veids, do_limit, dok_veids, doc_nr, vat_code, client_name, | + | |
| - | FROM @pvn1_3_top_table | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn13_docs_above], | + | |
| - | ( | + | </xsl: |
| - | SELECT dok_veids, client_name, | + | |
| - | FROM @v_totals | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn13_docs_above], | + | |
| - | ( | + | |
| - | SELECT dok_veids, client_name, | + | |
| - | FROM @x_totals | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn13_docs_above], | + | |
| - | ( | + | |
| - | SELECT isnull(sum(doc_sum), | + | |
| - | FROM @t_totals | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( | + | |
| - | SELECT CAST(SUM(doc_sum) AS decimal(15, | + | |
| - | FROM @pvn1_3tots | + | |
| - | FOR XML PATH(' | + | |
| - | ), --/pvn13 | + | |
| - | /* | + | |
| - | ( | + | |
| - | SELECT dok_veids, client_name, | + | |
| - | FROM @pvn13_sums_above_table where dok_veids!=' | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn13_docs_above], | + | |
| - | ( | + | |
| - | SELECT CAST(SUM(doc_sum) AS decimal(15, | + | |
| - | FROM @pvn13_sums_above_table m where dok_veids=' | + | |
| - | FOR XML PATH(' | + | |
| - | ), | + | |
| - | ( | + | |
| - | SELECT CAST((SUM(toptabledoc)) AS decimal(15, | + | |
| - | FROM @pvn13sums | + | |
| - | FOR XML PATH(' | + | |
| - | ),*/ --/pvn13 | + | |
| - | ( --pvn21 | + | |
| - | SELECT pazime, vat_reg_nr, CAST(SUM(article_sum) AS decimal(15, | + | |
| - | FROM @invoices_grouped | + | |
| - | GROUP BY vat_reg_nr, pazime | + | |
| - | FOR XML PATH(' | + | |
| - | ) AS [pvn21_docs], | + | |
| - | ( | + | |
| - | SELECT CAST(SUM(article_sum) AS decimal(15, | + | |
| - | FROM @invoices_grouped | + | |
| - | FOR XML PATH(' | + | |
| - | ) --/pvn21 | + | |
| - | FOR XML PATH(' | + | |
| </ | </ | ||
lv/procedures.1601555437.txt.gz · Labota: 2020/10/01 15:30 , labojis marija