projekt kivitendo

  my $gltaxkey_where = "(tk.pos_ustva>=59 AND tk.pos_ustva<=66)";

      $ARwhere .= " AND acc.transdate >= '$fromdate'"; 

    $APwhere .= " AND AP.transdate >= '$fromdate'";

             AND startdate <= COALESCE(ar.transdate)

             AND startdate <= COALESCE(ar.transdate)

     UNION -- Einnahmen direkter gl Buchungen erfassen

             --AND taxkey_id=ac.taxkey

             AND NOT $gltaxkey_where  

     UNION -- Ausgaben direkter gl Buchungen erfassen

       SELECT sum

         (ac.amount) AS amount, 

         tk.pos_ustva

       FROM acc_trans ac

       JOIN chart c ON (c.id = ac.chart_id)

       JOIN gl a ON (a.id = ac.trans_id)

       LEFT JOIN taxkeys tk ON (

         tk.id = (

           SELECT id FROM taxkeys 

           WHERE chart_id=ac.chart_id 

             --AND taxkey_id=ac.taxkey

             AND $gltaxkey_where 

             AND startdate <= COALESCE(ac.transdate)

           ORDER BY startdate DESC LIMIT 1

         )

       )

       $dpt_join

       WHERE 1 = 1

       $where

       $dpt_from

       $project

       GROUP BY tk.pos_ustva

-- @tag: ustva_setup_2007_update_chart_taxkeys_tax_add_missing_tax_accounts

-- @description: Aktualisierung des Kontenrahmens SKR03, einfuegen der fehlenden Steuerkonten in die Tabelle taxkeys

-- @depends: ustva_setup_2007_update_chart_taxkeys_tax

--#############################################################

--#

--# Anpassungen Tabelle taxkeys

--#

--#############################################################

INSERT INTO taxkeys (

  chart_id, tax_id, taxkey_id, pos_ustva, startdate)

  SELECT chart.id, '0', '0', '66', '1970-01-01'

  FROM chart

  LEFT JOIN tax ON (chart.id = tax.chart_id)

  WHERE chart.accno in  ('1571', '1575')

  AND

  EXISTS ( -- update only for SKR03

    SELECT coa FROM defaults 

    WHERE defaults.coa='Germany-DATEV-SKR03EU'

  )

;