Norādītais vecais variants no patreizējās lapas atšķiras ar:
Abās pusēs iepriekšējo versiju Iepriekšējā versija Nākamā versija | Iepriekšējā versija | ||
lv:procedures [2020/10/01 15:29] marija demo merks pvn |
lv:procedures [2020/10/27 19:06] (patlaban) marija ALG_rek_0610 |
||
---|---|---|---|
Rinda 1: | Rinda 1: | ||
- | <code sql> | + | <code> |
- | ALTER PROCEDURE dbo.int_hooldus_klient_pvn @aeg1 datetime, | + | <?xml version="1.0" encoding="utf-8"?> |
- | @aeg2 datetime AS --static vars | + | <xsl:stylesheet version="1.0" |
- | DECLARE @vat_declar_name nvarchar(200) | + | xmlns:xsl="http://www.w3.org/1999/XSL/Transform" |
- | SET | + | xmlns:fo="http://www.w3.org/1999/XSL/Format" |
- | @vat_declar_name = 'PVN' DECLARE @stat_vat decimal | + | xmlns:msxsl="urn:schemas-microsoft-com:xslt" |
- | SET | + | xmlns:js="urn:formulas" |
- | @stat_vat = 21 DECLARE @lim decimal | + | exclude-result-prefixes="msxsl js fo"> |
+ | <xsl:output method="html"/> | ||
+ | <xsl:decimal-format name="N" NaN="0" decimal-separator='.' grouping-separator=' ' /> | ||
+ | <xsl:template match="/"> | ||
+ | <xsl:variable name="item_per_page">25</xsl:variable> | ||
+ | <xsl:variable name="eiro"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/valuuta='LVL'"> | ||
+ | 1.42287181 | ||
+ | </xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta=''"> | ||
+ | 1.42287181 | ||
+ | </xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta='EUR'"> | ||
+ | 0.702804 | ||
+ | </xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta!='EUR' and /documents/document/valuuta!='LVL'"> | ||
+ | <xsl:value-of select="/documents/document/kurssbv1"/> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="nauda"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/valuuta='LVL'">LVL</xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta=''">LVL</xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'"> | ||
+ | <xsl:value-of select="/documents/document/valuuta"/> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="nauda2"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/valuuta='LVL'">EUR</xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta=''">EUR</xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta!='' and /documents/document!='LVL'">LVL</xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="rekina_summa"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL'"> | ||
+ | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) * $eiro, '0.00')" /> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="$nauda='EUR'"> | ||
+ | <xsl:value-of select="format-number(/documents/document/converted_tasuda div 100, '0.00')" /> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="$nauda!='LVL' or $nauda!='EUR'"> | ||
+ | <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="saldo"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> | ||
+ | <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="$nauda!='LVL' or $nauda!='EUR'"> | ||
+ | <xsl:value-of select="format-number(/documents/kontakt/saldo div /documents/document/kurssbv1, '0.00')"/> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | |||
+ | </xsl:variable> | ||
+ | <xsl:variable name="parads"> | ||
+ | <xsl:value-of select="format-number($saldo - $rekina_summa ,'0.00')"/> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="neapm_summa"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/valuuta!=''"> | ||
+ | <xsl:value-of select="format-number($parads, '0.00')"/> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="/documents/document/valuuta=''"> | ||
+ | <xsl:value-of select="$parads"/> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="atlaide"> | ||
+ | <xsl:choose><xsl:when test="sum(documents/document/rows/row[pross > 0]/pross) > 0">1</xsl:when> | ||
+ | <xsl:otherwise>0</xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:variable name="atlaides_summa"> | ||
+ | <xsl:for-each select="/documents/document/rows/row[pross > 0]"> | ||
+ | <a_summa> | ||
+ | <xsl:value-of select="format-number(hind*kogus*pross div 100, '0.0000')"/> | ||
+ | </a_summa> | ||
+ | </xsl:for-each> | ||
+ | </xsl:variable> | ||
+ | |||
+ | <script language="JavaScript" type="text/javascript"><![CDATA[ | ||
+ | if (document.getElementById('first')) { | ||
+ | document.write('<div class="pbreak"></div>'); | ||
+ | } else { | ||
+ | document.write('<div id="first"></div>'); | ||
+ | } | ||
+ | ]]></script> | ||
+ | |||
+ | <html> | ||
+ | <head> | ||
+ | <title><xsl:value-of select="/documents/language/arve_nr" /><!--Rēķins Nr.-->. <xsl:value-of select="/documents/document/number" /></title> | ||
+ | |||
+ | <style type="text/css"> | ||
+ | body { | ||
+ | text-align:center; | ||
+ | font-family:Century Gothic; | ||
+ | font-size:10px; | ||
+ | } | ||
+ | |||
+ | .container { | ||
+ | display:table; | ||
+ | text-align:left; | ||
+ | width:660px; | ||
+ | margin:auto; | ||
+ | } | ||
+ | |||
+ | .main_table { | ||
+ | width:660px; | ||
+ | border-collapse:collapse; | ||
+ | } | ||
+ | |||
+ | .main_table th { | ||
+ | font-size:14px; | ||
+ | font-family:Century Gothic; | ||
+ | font-weight:normal; | ||
+ | text-align:right; | ||
+ | padding:1px; | ||
+ | border:1px solid #B4B4B4; | ||
+ | } | ||
+ | |||
+ | .main_table td { | ||
+ | font-size:10px; | ||
+ | font-family:Century Gothic; | ||
+ | padding:1px; | ||
+ | border:1px solid #B4B4B4; | ||
+ | } | ||
+ | |||
+ | .main_table td.nos, .main_table td.kods, .main_table th.nos, .main_table th.kods { | ||
+ | text-align:left; | ||
+ | } | ||
+ | |||
+ | .main_table tr.cont { | ||
+ | height:16px; | ||
+ | } | ||
+ | |||
+ | .left { | ||
+ | float:left; | ||
+ | padding:30px 0px 0px 30px; | ||
+ | font-size:14px; | ||
+ | } | ||
+ | |||
+ | .right { | ||
+ | float:right; | ||
+ | text-align:right; | ||
+ | font-size:14px; | ||
+ | } | ||
+ | .left2 { | ||
+ | float:left; | ||
+ | text-align:left; | ||
+ | font-size:14px; | ||
+ | } | ||
+ | |||
+ | .right_small { | ||
+ | float:right; | ||
+ | text-align:right; | ||
+ | } | ||
+ | |||
+ | .spacer { | ||
+ | height:20px; | ||
+ | clear:both; | ||
+ | } | ||
+ | |||
+ | .divider { | ||
+ | clear:both; | ||
+ | } | ||
+ | |||
+ | .pbreak { | ||
+ | page-break-after:always; | ||
+ | } | ||
+ | |||
+ | h4.pb { | ||
+ | display:none; | ||
+ | } | ||
+ | </style> | ||
+ | </head> | ||
+ | <body> | ||
+ | <div class="container"> | ||
+ | <div class="spacer" style="height:0px;"></div> | ||
+ | <div style="float:left;" class="left2" valign="bottom"> | ||
+ | <IMG height="90" alt="Logo"><xsl:attribute name="SRC" ><xsl:value-of select="/documents/footer/firma_logo"/></xsl:attribute></IMG> | ||
+ | </div> | ||
+ | <div class="right"> | ||
+ | <br /> | ||
+ | <xsl:choose><!--Rēķins Nr.--> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel=''"><xsl:value-of select="/documents/document/maksetingimus_nimi" /> Nr. </xsl:when> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credit note no. </xsl:when> | ||
+ | <xsl:when test="/documents/document/keel=''">Rēķins Nr. </xsl:when> | ||
+ | <xsl:otherwise><xsl:value-of select="/documents/language/arve_nr" />.</xsl:otherwise></xsl:choose><xsl:value-of select="/documents/document/number" /><br /> | ||
+ | <xsl:choose><!--Kreditē rēķinu Nr.--> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel=''">Kreditē rēķinu Nr. <xsl:value-of select="/documents/document/kredarve" /><br /></xsl:when> | ||
+ | <xsl:when test="/documents/document/tingimus='K' and /documents/document/keel!=''">Credited invoice no. <xsl:value-of select="/documents/document/kredarve" /><br /></xsl:when> | ||
+ | </xsl:choose> | ||
+ | <xsl:choose><!--Datums--> | ||
+ | <xsl:when test="/documents/document/keel=''">Datums</xsl:when> | ||
+ | <xsl:otherwise><xsl:value-of select="/documents/language/aeg" /></xsl:otherwise></xsl:choose>:  | ||
+ | <xsl:variable name="day" select="substring(/documents/document/aeg_date,1,2)" /> | ||
+ | <xsl:variable name="month" select="substring(/documents/document/aeg_date,4,2)" /> | ||
+ | <xsl:variable name="year" select="substring(/documents/document/aeg_date,7,4)" /> | ||
+ | <xsl:variable name="menesis"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''"> | ||
+ | <xsl:if test="$month='01'">Janvāris</xsl:if> | ||
+ | <xsl:if test="$month='02'">Februāris</xsl:if> | ||
+ | <xsl:if test="$month='03'">Marts</xsl:if> | ||
+ | <xsl:if test="$month='04'">Aprīlis</xsl:if> | ||
+ | <xsl:if test="$month='05'">Maijs</xsl:if> | ||
+ | <xsl:if test="$month='06'">Jūnijs</xsl:if> | ||
+ | <xsl:if test="$month='07'">Jūlijs</xsl:if> | ||
+ | <xsl:if test="$month='08'">Augusts</xsl:if> | ||
+ | <xsl:if test="$month='09'">Septembris</xsl:if> | ||
+ | <xsl:if test="$month='10'">Oktobris</xsl:if> | ||
+ | <xsl:if test="$month='11'">Novembris</xsl:if> | ||
+ | <xsl:if test="$month='12'">Decembris</xsl:if> | ||
+ | </xsl:when> | ||
+ | <xsl:otherwise> | ||
+ | <xsl:if test="$month='01'">January</xsl:if> | ||
+ | <xsl:if test="$month='02'">February</xsl:if> | ||
+ | <xsl:if test="$month='03'">March</xsl:if> | ||
+ | <xsl:if test="$month='04'">April</xsl:if> | ||
+ | <xsl:if test="$month='05'">May</xsl:if> | ||
+ | <xsl:if test="$month='06'">June</xsl:if> | ||
+ | <xsl:if test="$month='07'">July</xsl:if> | ||
+ | <xsl:if test="$month='08'">August</xsl:if> | ||
+ | <xsl:if test="$month='09'">September</xsl:if> | ||
+ | <xsl:if test="$month='10'">October</xsl:if> | ||
+ | <xsl:if test="$month='11'">November</xsl:if> | ||
+ | <xsl:if test="$month='12'">December</xsl:if> | ||
+ | </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </xsl:variable> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''"><xsl:value-of select="concat($year,'. gada',' ',$day,'. ',$menesis)" /></xsl:when> | ||
+ | <xsl:otherwise><xsl:value-of select="concat($menesis,' ',$day,', ',$year)" /></xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | |||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/tellimusenr!=''"> | ||
+ | <br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Saskaņā ar priekšapmaksas rēķinu </xsl:when> | ||
+ | <xsl:otherwise>According to proforma invoice </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/document/tellimusenr" /> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | |||
+ | </div> | ||
+ | |||
+ | <div class="spacer"></div> | ||
+ | |||
+ | <div class="left2" valign="top"> | ||
+ | <xsl:if test="/documents/kontakt/kontakt!=''"><xsl:value-of select="/documents/kontakt/kontakt" /><br /></xsl:if> | ||
+ | <xsl:value-of select="/documents/document/klient_nimi" /><br /> | ||
+ | <xsl:if test="/documents/document/aadress1!=''"><xsl:value-of select="/documents/document/aadress1" /><br /></xsl:if> | ||
+ | <xsl:if test="/documents/document/aadress2!=''"><xsl:value-of select="/documents/document/aadress2" /></xsl:if> | ||
+ | <xsl:if test="/documents/document/aadress3!=''"> <xsl:value-of select="/documents/document/aadress3" /><br /></xsl:if> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Reģ. Nr.: </xsl:when> | ||
+ | <xsl:otherwise>Reg. No.: </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/kontakt/regnr" /><br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">PVN Reģ. Nr.: </xsl:when> | ||
+ | <xsl:otherwise>VAT Reg. No.: </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/kontakt/kmregnr" /><br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Banka: </xsl:when> | ||
+ | <xsl:otherwise>Bank: </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/kontakt/pank" /><br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Konta Nr.: </xsl:when> | ||
+ | <xsl:otherwise>Account No.: </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/kontakt/arveldusarve" /><br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/kontakt/lahaadress2!=''"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Piegādes adrese: </xsl:when> | ||
+ | <xsl:otherwise>Delivery address: </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | <xsl:value-of select="/documents/kontakt/lahaadress2" /><br /> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </div> | ||
+ | |||
+ | <div class="right"> | ||
+ | <xsl:value-of select="/documents/footer/firma_nimi" /><br /> | ||
+ | <xsl:if test="/documents/footer/firma_aadress!=''"><xsl:value-of select="/documents/footer/firma_aadress" /><br /></xsl:if> | ||
+ | <xsl:if test="/documents/footer/firma_aadress2!=''"><xsl:value-of select="/documents/footer/firma_aadress2" /></xsl:if> | ||
+ | <xsl:if test="/documents/footer/firma_aadress3!=''"> <xsl:value-of select="/documents/footer/firma_aadress3" /><br /></xsl:if> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Reģ. #: </xsl:when> | ||
+ | <xsl:otherwise>Reg. #: </xsl:otherwise> | ||
+ | </xsl:choose><!--Reģ. Nr--><xsl:value-of select="/documents/footer/firma_regnr" /><br /> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">PVN Reģ. #: </xsl:when> | ||
+ | <xsl:otherwise>VAT Reg. #: </xsl:otherwise> | ||
+ | </xsl:choose><!--PVN Reģ. Nr--><xsl:value-of select="/documents/footer/firma_kmnr" /><br /> | ||
+ | </div> | ||
+ | |||
+ | <div class="spacer" style="height:60px;"></div> | ||
+ | |||
+ | <table class="main_table" height="380" cellspacing="0" cellpadding="0"> | ||
+ | <tr class="cont"> | ||
+ | <th class="kods" width="100"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Kods</xsl:when> | ||
+ | <xsl:otherwise>Item</xsl:otherwise> | ||
+ | </xsl:choose><!--Kods--></th> | ||
+ | <th class="nos" width="300"><!--Nosaukums--> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Nosaukums</xsl:when> | ||
+ | <xsl:otherwise>Description</xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </th> | ||
+ | <th> <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Mērv.</xsl:when> | ||
+ | <xsl:otherwise>Unit</xsl:otherwise> | ||
+ | </xsl:choose><!--Mērvienība--></th> | ||
+ | <th> <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Daudz.</xsl:when> | ||
+ | <xsl:otherwise>Qty total</xsl:otherwise> | ||
+ | </xsl:choose><!--Daudz.--></th> | ||
+ | <xsl:if test="$atlaide!=0"> | ||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Atlaide</xsl:when> | ||
+ | <xsl:otherwise>Discount</xsl:otherwise> | ||
+ | </xsl:choose><!--Atlaide--></th> | ||
+ | </xsl:if> | ||
+ | |||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Cena <xsl:value-of select="$nauda"/></xsl:when> | ||
+ | <xsl:otherwise>Price <xsl:value-of select="$nauda"/></xsl:otherwise> | ||
+ | </xsl:choose></th> | ||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Summa <xsl:value-of select="$nauda"/></xsl:when> | ||
+ | <xsl:otherwise>Total <xsl:value-of select="$nauda"/></xsl:otherwise> | ||
+ | </xsl:choose></th> | ||
+ | </tr> | ||
+ | |||
+ | <xsl:for-each select="/documents/document/rows/row"> | ||
+ | |||
+ | <tr class="cont"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="artikkel!=''"> | ||
+ | <td class="kods" width="100"><xsl:value-of select="artikkel" /></td> | ||
+ | <td class="nos" width="250"><xsl:value-of disable-output-escaping="yes" select="keelne_seletus" /></td> | ||
+ | <td align="center"><xsl:value-of select="yhik" /></td> | ||
+ | <td align="center"><xsl:value-of select="kogus" /></td> | ||
+ | <xsl:if test="$atlaide!=0"> | ||
+ | <td class="atl"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="pross!=''"><xsl:value-of select="pross" /> %</xsl:when> | ||
+ | <xsl:otherwise> </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </td> | ||
+ | </xsl:if> | ||
+ | <td align="right"><xsl:value-of select="format-number((converted_hind) div 100,'0.00')"/></td> | ||
+ | <td align="right"><xsl:value-of select="format-number((converted_summa) div 100,'0.00')" /></td> | ||
+ | </xsl:when> | ||
+ | <xsl:otherwise> | ||
+ | <td> </td> | ||
+ | <td class="nos" width="250"><xsl:value-of disable-output-escaping="yes" select="keelne_seletus" /></td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <td> </td> | ||
+ | <xsl:if test="$atlaide!=0"> | ||
+ | <td> </td> | ||
+ | </xsl:if> | ||
+ | </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </tr> | ||
+ | |||
+ | <xsl:if test="((position() mod $item_per_page)=0) and (position()!=last())"> | ||
+ | <tr><td colspan="6" style="border-width:0px;"> </td></tr> | ||
+ | <xsl:text disable-output-escaping="yes"></table></xsl:text> | ||
+ | <div style="text-align:right;"><!--Turpinājums-->...</div> | ||
+ | <div style="text-align:center; margin-left:10px; margin-top:215px; width:35px; height:29px; #height:37px; border:1px solid #000000; padding:8px 0px 0px 0px;"> | ||
+ | <xsl:value-of select="floor((position()+1) div $item_per_page)" />  | ||
+ | </div> | ||
+ | <xsl:text disable-output-escaping="yes"></div></xsl:text> | ||
+ | |||
+ | <div class="pbreak"></div> | ||
+ | |||
+ | <xsl:text disable-output-escaping="yes"><div class="container"></xsl:text> | ||
+ | <div style="text-align:right; padding-bottom:5px;"> | ||
+ | <xsl:value-of select="floor((position()+1) div $item_per_page)+1" /> | ||
+ | <xsl:text>/</xsl:text> | ||
+ | <xsl:value-of select="ceiling(/documents/document/ridu div $item_per_page)" /> | ||
+ | </div> | ||
+ | <xsl:text disable-output-escaping="yes"><table class="main_table" height="740" cellspacing="0" cellpadding="0"></xsl:text> | ||
+ | <tr class="cont"> | ||
+ | <th class="kods" width="100"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Kods</xsl:when> | ||
+ | <xsl:otherwise>Item</xsl:otherwise> | ||
+ | </xsl:choose><!--Kods--></th> | ||
+ | <th class="nos" width="300"><!--Nosaukums--> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Nosaukums</xsl:when> | ||
+ | <xsl:otherwise>Description</xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </th> | ||
+ | <th> <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Mērv.</xsl:when> | ||
+ | <xsl:otherwise>Unit</xsl:otherwise> | ||
+ | </xsl:choose><!--Mērvienība--></th> | ||
+ | <th> <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Daudz.</xsl:when> | ||
+ | <xsl:otherwise>Qty total</xsl:otherwise> | ||
+ | </xsl:choose><!--Daudz.--></th> | ||
+ | <xsl:if test="$atlaide!=0"> | ||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Atlaide</xsl:when> | ||
+ | <xsl:otherwise>Discount</xsl:otherwise> | ||
+ | </xsl:choose><!--Atlaide--></th> | ||
+ | </xsl:if> | ||
+ | |||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Cena <xsl:value-of select="$nauda"/></xsl:when> | ||
+ | <xsl:otherwise>Price <xsl:value-of select="$nauda"/></xsl:otherwise> | ||
+ | </xsl:choose></th> | ||
+ | <th><xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Summa <xsl:value-of select="$nauda"/></xsl:when> | ||
+ | <xsl:otherwise>Total <xsl:value-of select="$nauda"/></xsl:otherwise> | ||
+ | </xsl:choose></th> | ||
+ | </tr> | ||
+ | </xsl:if> | ||
+ | |||
+ | <xsl:if test="position()=last()"> | ||
+ | <tr><td colspan="6" style="border-width:0px;"> </td></tr> | ||
+ | </xsl:if> | ||
+ | |||
+ | </xsl:for-each> | ||
+ | |||
+ | </table> | ||
- | DECLARE @stat_vat12 decimal -- Declared a new variable for 12% VAT | + | <table class="main_table" cellspacing="0" cellpadding="0"> |
- | SET @stat_vat12 = 12 | + | <tr><td style="text-align:left; border-width:0px;"><xsl:value-of select="documents/document/kommentaar"/></td></tr> |
+ | </table> | ||
- | SET | + | <table class="main_table" height="260" cellspacing="0" cellpadding="0"> |
- | @lim = ISNULL( | + | <tr style="height:220px;"> |
- | ( | + | <td style="text-align:left; border-width:0px;"> |
- | SELECT | + | |
- | TOP 1 CAST(ISNULL(kontod, 1000) AS decimal) | + | <div style="border:solid #B4B4B4; border-width:2px 0px 2px 0px; display:table; width:660px;"> |
- | 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 | + | <div style="float:left; width:350px;" valign="top" align="left"> |
- | Where tablea.blabla = tableb.blabla | + | <table width="100%" border="0" cellapdding="0" cellspacing="0" align="left"> |
+ | <tr> | ||
+ | <td align="left" style="border-width: 0px 0px 0px 0px;"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> | ||
+ | <xsl:value-of select="js:numText(string(format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00', 'N')),string($nauda))" /></xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:when> | ||
+ | <xsl:otherwise> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> | ||
+ | <xsl:value-of select="js:numText(string(format-number(/documents/document/converted_tasuda div 100, '0.00', 'N')),string($nauda))" /></xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="/documents/document/keel!=''"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> | ||
+ | <xsl:value-of select="js:numTextEng(string(format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00')),string($nauda))" /> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:when> | ||
+ | <xsl:otherwise> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="$nauda='LVL' or $nauda='EUR'"> | ||
+ | <xsl:value-of select="js:numTextEng(string(format-number((/documents/document/converted_tasuda div 100), '0.00')),string($nauda))" /></xsl:when> | ||
+ | </xsl:choose> | ||
+ | </xsl:otherwise> | ||
+ | </xsl:choose> | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </td> | ||
+ | </tr> | ||
+ | <xsl:for-each select="/documents/kmkoodid/kmkood"> | ||
+ | <tr> | ||
+ | <td align="left" style="border-width: 0px 0px 0px 0px;"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="km='9'"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel!=''">Article 138 (1) of the EU VAT Directive (2006/112/EC)</xsl:when> | ||
+ | <xsl:otherwise>Direktīvas 2006/112/EK 138. panta 1. punkts</xsl:otherwise> | ||
+ | </xsl:choose><!--Summa--><br /> | ||
+ | </xsl:when> | ||
+ | <xsl:when test="km!='9'"> | ||
+ |   | ||
+ | </xsl:when> | ||
+ | </xsl:choose> | ||
+ | </td> | ||
+ | </tr> | ||
+ | </xsl:for-each> | ||
+ | <tr> | ||
+ | <td align="left" style="border-width: 0px 0px 0px 0px;"> | ||
+ | <xsl:choose> | ||
+ | <xsl:when test="/documents/document/keel=''">Izrakstīja:</xsl:when> | ||
+ | <xsl:otherwise>Salesman:</xsl:otherwise> | ||
+ | </xsl:choose><!--Izrakstīja--> <xsl:value-of select="/documents/document/myyja_nimi" /> | ||
+ | </td> | ||
+ | </tr> | ||
+ | </table> | ||
+ | </div> | ||
- | */ | + | <div style="float:left; width:150px; text-align:right;"> |
- | INSERT INTO | + | <table width="100%" border="0" cellapdding="0" cellspacing="0"> |
- | @pvn12_main_table --purchase invoices - main | + | <xsl:if test="$atlaide!=0"> |
- | SELECT | + | <tr> |
- | dar_veids, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | do_limit, | + | <xsl:choose> |
- | doc_nr, | + | <xsl:when test="/documents/document/keel=''">Atlaides summa:</xsl:when> |
- | client_doc_nr, | + | <xsl:otherwise>Discount total:</xsl:otherwise> |
- | '' as vat_code, | + | </xsl:choose><!--Kopējā summa--> |
- | directo_table, | + | </td> |
- | doc_type, | + | </tr> |
- | client_name, | + | </xsl:if> |
- | vat_reg_nr, | + | <tr> |
- | country_code, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | case | + | <xsl:choose> |
- | when (directo_table = 'purchase_invoice') then SUM(doc_sum) - isnull( | + | <xsl:when test="/documents/document/keel=''">Summa bez PVN:</xsl:when> |
- | ( | + | <xsl:otherwise>Total:</xsl:otherwise> |
- | select | + | </xsl:choose><!--Summa--> |
- | sum(doc_sum) | + | </td> |
- | from | + | </tr> |
- | @pvn12_temp_table | + | <xsl:for-each select="/documents/kmkoodid/kmkood"> |
- | where | + | <tr> |
- | directo_table = 'prepayment' | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | and doc_nr = b.doc_nr | + | <xsl:choose> |
- | ), | + | <xsl:when test="/documents/document/keel=''">PVN: <xsl:value-of select="ilmakm" />%:</xsl:when> |
- | 0 | + | <xsl:otherwise>VAT <xsl:value-of select="ilmakm" />%:</xsl:otherwise> |
- | ) | + | </xsl:choose> |
- | else SUM(doc_sum) | + | </td> |
- | end, | + | </tr> |
- | case | + | </xsl:for-each> |
- | when (directo_table = 'purchase_invoice') then SUM(doc_vat) - isnull( | + | <tr> |
- | ( | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | select | + | <xsl:choose> |
- | sum(doc_vat) | + | <xsl:when test="/documents/document/keel=''">Kopā ar PVN:</xsl:when> |
- | from | + | <xsl:otherwise>Total with VAT:</xsl:otherwise> |
- | @pvn12_temp_table | + | </xsl:choose> |
- | where | + | </td> |
- | directo_table = 'prepayment' | + | </tr> |
- | and doc_nr = b.doc_nr | + | <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> |
- | ), | + | <tr> |
- | 0 | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | ) | + | <xsl:choose> |
- | else SUM(doc_vat) | + | <xsl:when test="/documents/document/keel=''">Priekšapmaksa:<xsl:value-of select="format-number(/documents/document/ettemaks div (/documents/document/converted_tasuda div 10000), '0.00')" /> %:</xsl:when> |
- | end, | + | <xsl:otherwise>Prepayment:<xsl:value-of select="format-number(/documents/document/ettemaks div (/documents/document/converted_tasuda div 10000), '0.00')" /> %:</xsl:otherwise> |
- | rate, | + | </xsl:choose> |
- | currency, | + | </td> |
- | alt_rate, | + | </tr> |
- | alt_currency, | + | </xsl:if> |
- | alt_doc_currency_sum, | + | <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> |
- | doc_date | + | <tr> |
- | FROM | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | @pvn12_temp_table b | + | <xsl:choose> |
- | where | + | <xsl:when test="/documents/document/keel=''">Summa apmaksai:</xsl:when> |
- | --doc_sum > 0 or doc_sum < 0 or | + | <xsl:otherwise>Sum To pay:</xsl:otherwise> |
- | directo_table not in ('prepayment') | + | </xsl:choose> |
- | GROUP BY | + | </td> |
- | dar_veids, | + | </tr> |
- | do_limit, | + | </xsl:if> |
- | doc_nr, | + | </table> |
- | client_doc_nr, | + | </div> |
- | directo_table, | + | |
- | doc_type, | + | <div class="right_small"> |
- | client_name, | + | <table width="100%" border="0" cellapdding="0" cellspacing="0"> |
- | vat_reg_nr, | + | <xsl:if test="$atlaide!=0"> |
- | country_code, | + | <tr> |
- | rate, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | currency, | + | <xsl:value-of select="format-number(sum(msxsl:node-set($atlaides_summa)/a_summa),'0.00')" /> <xsl:value-of select="$nauda" /> |
- | alt_rate, | + | </td> |
- | alt_currency, | + | </tr> |
- | alt_doc_currency_sum, | + | </xsl:if> |
- | doc_date | + | <tr> |
- | INSERT INTO | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | @pvn12_main_table --expenses | + | <xsl:value-of select="format-number(/documents/document/kokku,'0.00')" /> <xsl:value-of select="$nauda" /> |
- | /* | + | </td> |
- | SELECT | + | </tr> |
- | '' AS dar_veids, | + | <xsl:for-each select="/documents/kmkoodid/kmkood"> |
- | '' AS do_limit, | + | <tr> |
- | CAST(number AS nvarchar(30)) AS doc_nr, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | CAST((SELECT TOP 1 dokument FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS client_doc_nr, | + | <xsl:value-of select="format-number(summakm div 100,'0.00')" /> <xsl:value-of select="$nauda" /> |
- | CAST((SELECT TOP 1 kmkood FROM fin_kulutused_read WHERE (fin_kulutused_read.number=fin_kulutused.number)) AS nvarchar(30)) AS vat_code, | + | </td> |
- | 'expense' AS directo_table, | + | </tr> |
- | 'Fakturrekins' AS doc_type, | + | </xsl:for-each> |
- | CAST((SELECT TOP 1 hankija_nimi FROM fin_kulutused_read WHERE fin_kulutused_read.number=fin_kulutused.number) AS nvarchar(30)) AS client_name, | + | <tr> |
- | 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, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | '--' AS country_code, | + | <xsl:value-of select="format-number(/documents/document/converted_tasuda div 100,'0.00')" /> <xsl:value-of select="$nauda" /> |
- | CAST(summa*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_sum, | + | </td> |
- | CAST(summa*(@stat_vat/100)*ISNULL(kurssbv1,1) AS decimal(15,2)) AS doc_vat, | + | </tr> |
- | CAST(ISNULL(kurssbv1,1) AS decimal(28,18)) AS rate, | + | <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> |
- | CAST(valuuta AS nvarchar(30)) AS currency, | + | <tr> |
- | 1 AS alt_rate, | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | 'EUR' AS alt_currency, | + | <xsl:value-of select="format-number(/documents/document/ettemaks, '0.00')" /> <xsl:value-of select="$nauda" /> |
- | CAST(lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, | + | </td> |
- | REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date | + | </tr> |
- | FROM fin_kulutused WITH (NOLOCK) WHERE CAST(aeg AS date) BETWEEN CAST(@aeg1 AS date) AND CAST(@aeg2 AS date) | + | </xsl:if> |
- | AND (kinnitatud='True') | + | <xsl:if test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> |
- | 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)))) | + | <tr> |
- | */ | + | <td width="50%" align="right" style="border-width: 0px 0px 0px 0px;"> |
- | SELECT | + | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) - /documents/document/ettemaks, '0.00')" /> <xsl:value-of select="$nauda" /> |
- | ( | + | </td> |
- | SELECT | + | </tr> |
- | TOP 1 dar_veids | + | </xsl:if> |
- | FROM | + | </table> |
- | @pvn12_vat_codes vc | + | </div> |
- | WHERE | + | |
- | vc.code = fkr.kmkood | + | <div class="spacer"></div> |
- | ) AS dar_veids, | + | |
- | ( | + | <div style="float:left; width:350px;" > |
- | SELECT | + | <xsl:choose><xsl:when test="/documents/document/keel=''">Banka: </xsl:when><xsl:otherwise>Bank: </xsl:otherwise></xsl:choose><xsl:value-of select="/documents/footer/firma_pank" /><br /> |
- | TOP 1 do_limit | + | <xsl:choose><xsl:when test="/documents/document/keel=''">Bankas kods: </xsl:when><xsl:otherwise>Bank SWIFT: </xsl:otherwise></xsl:choose><xsl:value-of select="/documents/footer/firma_swift" /><br /> |
- | FROM | + | <xsl:choose> |
- | @pvn12_vat_codes vc | + | <xsl:when test="/documents/document/keel=''">Bankas konts:</xsl:when> |
- | WHERE | + | <xsl:otherwise>Bank account:</xsl:otherwise> |
- | vc.code = fkr.kmkood | + | </xsl:choose><!--Konts--> <xsl:value-of select="/documents/footer/firma_aa" /> |
- | ) AS do_limit, | + | </div> |
- | CAST(fkr.number AS nvarchar(30)) AS doc_nr, | + | |
- | cast(fkr.dokument as nvarchar(30)) AS client_doc_nr, | + | <div style="float:left; width:150px; text-align:right;"> |
- | cast(fkr.kmkood as nvarchar(30)) AS vat_code, | + | <!--<div style="padding-top:3px;"> |
- | 'expense' AS directo_table, | + | <div style="padding-top:3px;"></div> |
- | 'Fakturrekins' AS doc_type, | + | <xsl:choose> |
- | cast(fkr.hankija_nimi as nvarchar(30)) AS client_name, | + | <xsl:when test="/documents/document/keel=''">Kopā apmaksai:</xsl:when> |
- | cast( | + | <xsl:otherwise>Total:</xsl:otherwise> |
- | ( | + | </xsl:choose></div>--> |
- | select | + | <xsl:choose> |
- | kmregnr | + | <xsl:when test="/documents/document/keel=''">Apmaksas termiņš:</xsl:when> |
- | from | + | <xsl:otherwise>Due date:</xsl:otherwise> |
- | hankijad | + | </xsl:choose><!--Apmaksas termiņš--> |
- | where | + | </div> |
- | kood = fkr.hankija_kood | + | |
- | ) as nvarchar(30) | + | <div class="right_small" width="130px"> |
- | ), | + | <table width="100%" cellpadding="1" cellspacing="1"> |
- | '--', | + | <!-- <xsl:if test="$saldo > $rekina_summa"> |
- | cast( | + | <tr> |
- | fkr.summa * isnull(fkr.r_kurss, isnull(fk.kurssbv1, 1)) as decimal(15, 2) | + | <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%"> |
- | ), | + |   |
- | cast( | + | </td> |
- | (fkr.summa * (@stat_vat / 100)) * isnull(r_kurss, 1) as decimal(15, 2) | + | <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;"> |
- | ), | + | <xsl:value-of select="$neapm_summa" />  |
- | cast( | + | <xsl:value-of select="$nauda" /> |
- | isnull(fkr.r_kurss, isnull(fk.kurssbv1, 1)) as decimal(28, 18) | + | </td> |
- | ), | + | </tr> |
- | cast( | + | </xsl:if> |
- | isnull(fkr.r_valuuta, isnull(fk.valuuta, 'EUR')) as nvarchar(255) | + | <tr> |
- | ), | + | <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%"> |
- | 1, | + |   |
- | 'EUR', | + | </td> |
- | CAST(fk.lisa_field7 AS nvarchar(30)) AS alt_doc_currency_sum, | + | <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;"> |
- | REPLACE( | + | <xsl:choose> |
- | CONVERT(VARCHAR, isnull(fkr.aeg, fk.aeg), 111), | + | <xsl:when test="/documents/document/ettemaks!='' and /documents/document/ettemaks!=0"> |
- | '/', | + | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100)-(/documents/document/ettemaks), '0.00')" /> |
- | '-' | + |   |
- | ) AS doc_date | + | <xsl:value-of select="$nauda" /> |
- | from | + | </xsl:when> |
- | fin_kulutused_read fkr with(nolock) | + | <xsl:otherwise> |
- | left join fin_kulutused fk on fk.number = fkr.number | + | <xsl:value-of select="format-number((/documents/document/converted_tasuda div 100) + $parads, '0.00')" />  |
- | where | + | <xsl:value-of select="$nauda" /> |
- | cast(isnull(fkr.aeg, fk.aeg) as date) between cast(@aeg1 as date) | + | </xsl:otherwise> |
- | and cast(@aeg2 as date) | + | </xsl:choose> |
- | and fkr.kmkood in ( | + | </td> |
- | select | + | </tr>--> |
- | code | + | <tr> |
- | from | + | <td style="padding:2px; margin-bottom:5px; border:0px solid #000000;" width="50%"> |
- | @pvn12_vat_codes | + |   |
- | ) | + | </td> |
- | AND (kinnitatud = 'True') | + | <td style="padding:2px; margin-bottom:5px; border:2px solid #000000;" align="right"> |
- | UPDATE | + | <xsl:value-of select="/documents/document/aeg2_date" /> |
- | @pvn12_main_table | + | </td> |
- | SET | + | </tr> |
- | country_code = SUBSTRING(vat_reg_nr, 1, 2) | + | </table> |
- | UPDATE | + | |
- | @pvn12_main_table | + | </div> |
- | SET | + | |
- | alt_currency = ( | + | </div> |
- | CASE | + | <!--<xsl:if test="$neapm_summa>$rekina_summa"> |
- | WHEN country_code = 'BG' THEN 'BGN' | + | <div style="clear:both; padding:5px 0px 0px 0px; font-size:12px; font-weight:bold;"> |
- | WHEN country_code = 'CZ' THEN 'CZK' | + | <xsl:choose> |
- | WHEN country_code = 'DK' THEN 'DKK' | + | <xsl:when test="/documents/document/keel=''"> |
- | WHEN country_code = 'GB' THEN 'GBP' | + | <xsl:choose> |
- | WHEN country_code = 'HU' THEN 'HUF' | + | <xsl:when test="/documents/document/valuuta=''"> |
- | WHEN country_code = 'LT' | + | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> <xsl:value-of select="$nauda"/><br /> |
- | AND CAST(doc_date AS date) < CAST('2015.01.01' AS date) THEN 'LTL' | + | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <xsl:value-of select="$nauda2"/><br /> |
- | WHEN country_code = 'PL' THEN 'PLN' | + | </xsl:when> |
- | WHEN country_code = 'RO' THEN 'RON' | + | <xsl:when test="/documents/document/valuuta!=''"> |
- | WHEN country_code = 'SE' THEN 'SEK' | + | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> <xsl:value-of select="$nauda2"/><br /> |
- | ELSE 'EUR' | + | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <xsl:value-of select="$nauda"/><br /> |
- | END | + | </xsl:when> |
- | ) | + | </xsl:choose> |
- | UPDATE | + | Neapmaksāto rēķinu kopsumma: <xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> |
- | @pvn12_main_table | + | </xsl:when> |
- | SET | + | <xsl:otherwise> |
- | alt_rate = ISNULL( | + | <xsl:choose> |
- | ( | + | <xsl:when test="/documents/document/valuuta=''"> |
- | SELECT | + | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo, '0.00')"/> <xsl:value-of select="$nauda"/><br /> |
- | TOP 1 CAST(curr_rates.kurss1 AS decimal(28, 18)) | + | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <xsl:value-of select="$nauda2"/><br /> |
- | FROM | + | </xsl:when> |
- | curr_rates | + | <xsl:when test="/documents/document/valuuta!=''"> |
- | WHERE | + | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo , '0.00')"/> <xsl:value-of select="$nauda2"/><br /> |
- | curr_rates.kood = alt_currency | + | Unpaid invoices total:<xsl:value-of select="format-number(/documents/kontakt/saldo div $eiro, '0.00')"/> <xsl:value-of select="$nauda"/><br /> |
- | AND CAST(curr_rates.aeg AS date) <= DATEADD(day, -1, CAST(doc_date AS date)) | + | </xsl:when> |
- | ORDER BY | + | </xsl:choose> |
- | curr_rates.aeg DESC | + | </xsl:otherwise> |
- | ), | + | </xsl:choose> |
- | 1 | + | <br /> |
- | ) | + | |
- | UPDATE | + | </div> |
- | @pvn12_main_table | + | </xsl:if>--> |
- | SET | + | <div style="clear:both; padding:5px 0px 0px 0px; font-size:14px;"> |
- | dar_veids = ( | + | <xsl:choose> |
- | SELECT | + | <xsl:when test="/documents/document/keel=''">Dokuments ir sagatavots elektroniski un derīgs bez paraksta.</xsl:when> |
- | TOP 1 dar_veids | + | <xsl:otherwise>The invoice is produced electronically and valid without signature.</xsl:otherwise> |
- | FROM | + | </xsl:choose> |
- | @pvn12_vat_codes vc | + | </div> |
- | WHERE | + | </td> |
- | vc.code = vat_code | + | </tr> |
- | ) | + | <!--<tr> |
- | where | + | <td style="height:0px; line-height:0px; border-width:0px; font-size:0px;"> |
- | dar_veids is null | + | <img SRC="http://login.directo.ee/logos/files/nordcity_lv_3.png" width="660"/> |
- | UPDATE | + | </td> |
- | @pvn12_main_table | + | </tr>--> |
- | SET | + | <tr> |
- | do_limit = ( | + | <td style="text-align:left; border-width:0px;"> |
- | SELECT | + | |
- | TOP 1 do_limit | + | |
- | FROM | + | </td> |
- | @pvn12_vat_codes vc | + | </tr> |
- | WHERE | + | </table> |
- | vc.code = vat_code | + | |
- | ) ----------------- | + | </div> <!-- Konteinera beigas --> |
- | ------ 1-3 ------ | + | </body> |
- | ----------------- | + | |
- | --izveidojam PVN 13 kodu tabulu | + | </html> |
- | DECLARE @pvn13_vat_codes TABLE ( | + | </xsl:template> |
- | code nvarchar(30), | + | <msxsl:script language="JScript" implements-prefix="js"><![CDATA[ |
- | rate decimal, | + | var cipariEng=['','one ','two ','three ','four ','five ','six ','seven ','eight ','nine ']; |
- | description nvarchar(200), | + | var padsmitiEng=['ten ','eleven ','twelve ','thirteen ','fourteen ','fifteen ','sixteen ','seventeen ','eighteen ','nineteen ']; |
- | sales_account nvarchar(30), | + | var desmitiEng=['','','twenty ','thirty ','fourty ','fifty ','sixty ','seventy ','eighty ','ninety ']; |
- | purchase_account nvarchar(30), | + | |
- | info nvarchar(200), | + | function simtiEng(str) { |
- | dar_veids nvarchar(5), | + | res=''; |
- | do_limit varchar(2) | + | |
- | ) | + | // pirmais cipars no labaas |
- | INSERT INTO | + | if (str.charAt(1)=='1') // chekojam pirmspeedeejo ciparu - ja viens tad vai nu desmit vai padsmit |
- | @pvn13_vat_codes | + | res=padsmitiEng[parseInt(str.charAt(2))]; |
- | SELECT | + | else res=cipariEng[parseInt(str.charAt(2))]; |
- | kood, | + | |
- | ilmakm, | + | // otrais cipars no labaas |
- | seletus, | + | if (str.charAt(0)=='1') // ja treshais cipars no labaas ir 1 tad jaaraksta simts |
- | myykkonto, | + | res='hundred '+desmitiEng[parseInt(str.charAt(1))]+res; |
- | ostuKMkonto, | + | else if (str.charAt(0)!='0') // ja treshais cipars no labaas nav 1 vai 0 tad jaaraksta simti |
- | lisainfo, | + | res='hundred '+desmitiEng[parseInt(str.charAt(1))]+res; |
- | CAST( | + | else // ja nav simtu |
- | ISNULL( | + | res=desmitiEng[parseInt(str.charAt(1))]+res; |
- | ( | + | |
- | SELECT | + | // treshais cipars no labaas |
- | TOP 1 replace(sisu, '_', '.') | + | res=cipariEng[parseInt(str.charAt(0))]+res; |
- | FROM | + | return res; |
- | yld_data yd WITH (NOLOCK) | + | } |
- | WHERE | + | |
- | yd.kaart = fk.kood | + | function numTextEng(skaitlis,nauda) { |
- | AND kood = 'DARTIPS1_3' | + | fullStr='0000000000'; |
- | AND klass = 'kmk' | + | tempStr=''; |
- | ), | + | tempRes=''; |
- | '41' | + | resStr=''; |
- | ) AS nvarchar(10) | + | minus=''; |
- | ) AS dar_veids, | + | |
- | CAST( | + | punkts=skaitlis.indexOf('.'); |
- | ISNULL( | + | if (punkts==-1) { |
- | ( | + | pirmspStr=skaitlis; |
- | SELECT | + | aizpStr='0'; |
- | TOP 1 sisu | + | } else { |
- | FROM | + | pirmspStr=skaitlis.substring(0,punkts); |
- | yld_data yd WITH (NOLOCK) | + | aizpStr=skaitlis.substring(punkts+1); |
- | WHERE | + | } |
- | yd.kaart = fk.kood | + | |
- | AND kood = 'LIMITS' | + | if (pirmspStr.charAt(0)=='-') { // ja ar miinus ziimi |
- | AND klass = 'kmk' | + | pirmspStr=pirmspStr.substring(1); |
- | ), | + | minus='minus '; |
- | 'Ja' | + | } |
- | ) AS varchar(2) | + | |
- | ) AS do_limit | + | for (i=pirmspStr.length-1, j=9; i>=0; i--, j--) { // paarliekam uz fullstr, kas sastaav no desmit cipariem |
- | FROM | + | fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1); |
- | fin_kmkoodid fk | + | } |
- | WHERE | + | |
- | lisainfo LIKE '%pvn13%' Declare @pvn1_3_temp TABLE ( | + | if (fullStr.length>10) fullStr=fullStr.substring(fullStr.length-10); // ja vairaak par 10 cipariem, tad trim |
- | dar_veids nvarchar(5), | + | |
- | do_limit varchar(2), | + | if (nauda=='EUR') { |
- | dok_veids nvarchar(1), | + | if (parseInt(fullStr,10)==1) resStr+='euro'; // lats vai lati |
- | doc_nr nvarchar(30), | + | else if (parseInt(fullStr,10)!=0) resStr+='euros'; |
- | vat_code nvarchar(30), | + | else resStr+='Null euros'; |
- | client_name nvarchar(30), | + | } else if (parseInt(fullStr,10)!=0) { |
- | vat_reg_nr nvarchar(30), | + | resStr+=nauda; |
- | doc_sum decimal(15, 4), | + | } else resStr+='Null '+nauda; |
- | doc_vat decimal(15, 4), | + | |
- | doc_date nvarchar(30), | + | if (parseInt(fullStr.substring(7),10)>0) { |
- | doc_sum_total decimal(15, 4), | + | tempStr=fullStr.substring(7); // simti |
- | vatc_prepayment decimal(15, 4) | + | tempRes=simtiEng(tempStr); |
- | ) Declare @pvn1_3_values_by_doc TABLE ( | + | resStr=tempRes+resStr; |
- | dar_veids nvarchar(5), | + | } |
- | do_limit varchar(2), | + | |
- | dok_veids nvarchar(1), | + | if (parseInt(fullStr.substring(4,7),10)>0) { |
- | doc_nr nvarchar(30), | + | tempStr=fullStr.substring(4,7); // tūkstoši |
- | vat_code nvarchar(30), | + | tempRes=simtiEng(tempStr); |
- | client_name nvarchar(30), | + | if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='thousand '; |
- | vat_reg_nr nvarchar(30), | + | else if (parseInt(tempStr,10)!=0) tempRes+='thousand '; |
- | doc_sum decimal(15, 4), | + | resStr=tempRes+resStr; |
- | doc_vat decimal(15, 4), | + | } |
- | doc_date nvarchar(30), | + | |
- | doc_sum_total decimal(15, 4), | + | if (parseInt(fullStr.substring(1,4),10)>0) { |
- | vatc_prepayment decimal(15, 4), | + | tempStr=fullStr.substring(1,4); // miljoni |
- | rn int | + | tempRes=simtiEng(tempStr); |
- | ) Declare @pvn1_3_top_table TABLE ( | + | if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='million '; |
- | dar_veids nvarchar(5), | + | else if (parseInt(tempStr,10)!=0) tempRes+='million '; |
- | do_limit varchar(2), | + | resStr=tempRes+resStr; |
- | dok_veids nvarchar(1), | + | } |
- | doc_nr nvarchar(30), | + | |
- | vat_code nvarchar(30), | + | if (fullStr.charAt(0)!='0') { // miljardi |
- | client_name nvarchar(30), | + | if (fullStr.charAt(0)=='1') resStr='one billion '+resStr; |
- | vat_reg_nr nvarchar(30), | + | else resStr=cipariEng[parseInt(fullStr.charAt(0),10)]+'billion '+resStr; |
- | doc_sum decimal(15, 4), | + | } |
- | doc_vat decimal(15, 4), | + | |
- | doc_date nvarchar(30), | + | if (resStr!='') { // pirmais burts lielais |
- | doc_sum_total decimal(15, 4), | + | resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1); |
- | vatc_prepayment decimal(15, 4), | + | } |
- | rn int | + | |
- | ) Declare @pvn1_3_top_tablex TABLE ( | + | santimi=parseInt(aizpStr,10); //santimi |
- | dar_veids nvarchar(5), | + | if (santimi>0) { |
- | do_limit varchar(2), | + | resStr+=' '+santimi; |
- | dok_veids nvarchar(1), | + | if (santimi==1) { |
- | doc_nr nvarchar(30), | + | resStr+=' cent'; |
- | vat_code nvarchar(30), | + | } else { |
- | client_name nvarchar(30), | + | resStr+=' cents'; |
- | vat_reg_nr nvarchar(30), | + | } |
- | doc_sum decimal(15, 4), | + | } else { |
- | doc_vat decimal(15, 4), | + | resStr+=' 00 cents'; |
- | doc_date nvarchar(30), | + | } |
- | doc_sum_total decimal(15, 4), | + | |
- | vatc_prepayment decimal(15, 4), | + | return (minus+resStr); |
- | rn int | + | |
- | ) Declare @tdeals TABLE ( | + | } |
- | dar_veids nvarchar(5), | + | var cipari=['','viens ','divi ','trīs ','četri ','pieci ','seši ','septiņi ','astoņi ','deviņi ']; |
- | do_limit varchar(2), | + | var padsmiti=['desmit ','vienpadsmit ','divpadsmit ','trīspadsmit ','četrpadsmit ','piecpadsmit ','sešpadsmit ','septiņpadsmit ','astoņpadsmit ','deviņpadsmit ']; |
- | dok_veids nvarchar(1), | + | var desmiti=['','','divdesmit ','trīsdesmit ','četrdesmit ','piecdesmit ','sešdesmit ','septiņdesmit ','astoņdesmit ','deviņdesmit ']; |
- | doc_nr nvarchar(30), | + | |
- | vat_code nvarchar(30), | + | function simti(str) { |
- | client_name nvarchar(30), | + | res=''; |
- | vat_reg_nr nvarchar(30), | + | |
- | doc_sum decimal(15, 4), | + | // pirmais cipars no labaas |
- | doc_vat decimal(15, 4), | + | if (str.charAt(1)=='1') // chekojam pirmspeedeejo ciparu - ja viens tad vai nu desmit vai padsmit |
- | doc_date nvarchar(30), | + | res=padsmiti[parseInt(str.charAt(2))]; |
- | doc_sum_total decimal(15, 4), | + | else res=cipari[parseInt(str.charAt(2))]; |
- | vatc_prepayment decimal(15, 4), | + | |
- | rn int | + | // otrais cipars no labaas |
- | ) Declare @pvn1_3_top_sums_customer TABLE ( | + | if (str.charAt(0)=='1') // ja treshais cipars no labaas ir 1 tad jaaraksta simts |
- | vat_reg_nr nvarchar(30), | + | res='simts '+desmiti[parseInt(str.charAt(1))]+res; |
- | doc_sum decimal(15, 4), | + | else if (str.charAt(0)!='0') // ja treshais cipars no labaas nav 1 vai 0 tad jaaraksta simti |
- | doc_vat decimal(15, 4) | + | res='simti '+desmiti[parseInt(str.charAt(1))]+res; |
- | ) Declare @pvn1_3_sums_customer TABLE ( | + | else // ja nav simtu |
- | vat_reg_nr nvarchar(30), | + | res=desmiti[parseInt(str.charAt(1))]+res; |
- | doc_sum decimal(15, 4), | + | |
- | doc_vat decimal(15, 4) | + | // treshais cipars no labaas |
- | ) declare @v_totals TABLE ( | + | res=cipari[parseInt(str.charAt(0))]+res; |
- | dok_veids nvarchar(32), | + | return res; |
- | client_name nvarchar(30), | + | } |
- | vat_reg_nr nvarchar(34), | + | |
- | doc_sum decimal(15, 4), | + | function numText(skaitlis,nauda) { |
- | doc_vat decimal(15, 4) | + | fullStr='0000000000'; |
- | ) declare @x_totals TABLE ( | + | tempStr=''; |
- | dok_veids nvarchar(32), | + | tempRes=''; |
- | client_name nvarchar(30), | + | resStr=''; |
- | vat_reg_nr nvarchar(32), | + | minus=''; |
- | doc_sum decimal(15, 4), | + | |
- | doc_vat decimal(15, 4) | + | punkts=skaitlis.indexOf('.'); |
- | ) declare @t_totals TABLE ( | + | if (punkts==-1) { |
- | doc_sum decimal(15, 4), | + | pirmspStr=skaitlis; |
- | doc_vat decimal(15, 4) | + | aizpStr='0'; |
- | ) --ielasam visas rēķina rindas kuras ir ar 1 - 3 atšifrējamajiem pvn kodiem | + | } else { |
- | insert | + | pirmspStr=skaitlis.substring(0,punkts); |
- | @pvn1_3_temp | + | aizpStr=skaitlis.substring(punkts+1); |
- | SELECT | + | } |
- | ( | + | |
- | SELECT | + | if (pirmspStr.charAt(0)=='-') { // ja ar miinus ziimi |
- | TOP 1 dar_veids | + | pirmspStr=pirmspStr.substring(1); |
- | FROM | + | minus='mīnus '; |
- | @pvn13_vat_codes vc | + | } |
- | WHERE | + | |
- | vc.code = mr_arved_read.kmk | + | for (i=pirmspStr.length-1, j=9; i>=0; i--, j--) { // paarliekam uz fullstr, kas sastaav no desmit cipariem |
- | ) AS dar_veids, | + | fullStr=fullStr.substring(0,j) + pirmspStr.charAt(i) + fullStr.substring(j+1); |
- | ( | + | } |
- | SELECT | + | |
- | TOP 1 do_limit | + | if (fullStr.length>10) fullStr=fullStr.substring(fullStr.length-10); // ja vairaak par 10 cipariem, tad trim |
- | FROM | + | |
- | @pvn13_vat_codes vc | + | if (parseInt(fullStr,10)!=0) { |
- | WHERE | + | resStr+=nauda; |
- | vc.code = mr_arved_read.kmk | + | } else resStr+='Nulle '+nauda; |
- | ) AS do_limit, | + | |
- | case | + | if (parseInt(fullStr.substring(7),10)>0) { |
- | when (isnull(ettemaks, 0) = 0) then '1' | + | tempStr=fullStr.substring(7); // simti |
- | else '1' | + | tempRes=simti(tempStr); |
- | end AS dok_veids, | + | resStr=tempRes+resStr; |
- | mr_arved_read.number AS doc_nr, | + | } |
- | kmk as vat_code, | + | |
- | CAST((klient_nimi) AS nvarchar(30)) AS client_name, | + | if (parseInt(fullStr.substring(4,7),10)>0) { |
- | case | + | tempStr=fullStr.substring(4,7); // t�ksto�i |
- | when ( | + | tempRes=simti(tempStr); |
- | kmregnumber is not null | + | if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='tūkstotis '; |
- | or kmregnumber != ' ' | + | else if (parseInt(tempStr,10)!=0) tempRes+='tūkstoši '; |
- | ) then kmregnumber | + | resStr=tempRes+resStr; |
- | else ( | + | } |
- | select | + | |
- | top 1 kmregnr | + | if (parseInt(fullStr.substring(1,4),10)>0) { |
- | from | + | tempStr=fullStr.substring(1,4); // miljoni |
- | kliendid | + | tempRes=simti(tempStr); |
- | where | + | if ((tempStr.charAt(2)=='1') && (tempStr.charAt(1)!='1')) tempRes+='miljons '; |
- | kood = mr_arved.klient_kood | + | else if (parseInt(tempStr,10)!=0) tempRes+='miljoni '; |
- | ) | + | resStr=tempRes+resStr; |
- | end AS vat_reg_nr, | + | } |
- | CAST((ROUND(ISNULL(summa, 0), 4)) AS decimal(15, 4)) AS doc_sum, | + | |
- | isnull( | + | if (fullStr.charAt(0)!='0') { // miljardi |
- | cast( | + | if (fullStr.charAt(0)=='1') resStr='viens miljards '+resStr; |
- | ( | + | else resStr=cipari[parseInt(fullStr.charAt(0),10)]+'miljardi '+resStr; |
- | ROUND(ISNULL(summa, 0), 4) * ( | + | } |
- | NULLIF( | + | |
- | ( | + | if (resStr!='') { // pirmais burts lielais |
- | ISNULL( | + | resStr=resStr.charAt(0).toUpperCase()+resStr.substring(1); |
- | ( | + | } |
- | SELECT | + | |
- | TOP 1 rate | + | santimi=parseInt(aizpStr,10); //santimi |
- | FROM | + | if (santimi>0) { |
- | @pvn13_vat_codes | + | resStr+=' '+santimi; |
- | WHERE | + | tmpSant=santimi.toString(); |
- | code = mr_arved_read.kmk | + | if ((tmpSant.charAt(tmpSant.length-1)=='1') && (tmpSant.charAt(tmpSant.length-2)!='1')) { |
- | ), | + | resStr+=' cents'; |
- | 0 | + | } else { |
- | ) / 100 | + | resStr+=' centi'; |
- | ), | + | } |
- | 0 | + | } else { |
- | ) | + | resStr+=' 00 centi'; |
- | ) | + | } |
- | ) AS decimal(15, 4) | + | |
- | ), | + | return (minus+resStr); |
- | '0.00' | + | } |
- | ) AS doc_vat, | + | |
- | REPLACE(CONVERT(VARCHAR, aeg, 111), '/', '-') AS doc_date, | + | ]]></msxsl:script> |
- | ( | + | |
- | select | + | </xsl:stylesheet> |
- | 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 | + | |
</code> | </code> |