Directo palīdzība

Šī 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
 
DECLARE  @stat_vat12 DECIMAL  -- Declared a new variable for 12% VAT
SET  @stat_vat12  =  12
 
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'
    )
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_vat12 / 100)) AS DECIMAL(15, 2)  -- @stat_vat changed to @stat_vat12 
            )
        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_vat12 / 100) * (@stat_vat12 / 100) -- @stat_vat changed to @stat_vat12
                ) 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)
            )
            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')
    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
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