Publikováno Od Petr Hnilica

Integrace a komunikace s Flexibee – 07. Sestavení účetního deníku

Aktualizováno: 25.02.2020 – Když Flexibee aktualizoval (2020.1.0.7), tak firma s přidanými tabulkami ve schématu public se neobnovila a nemohl jsem v ní aplikaci pracovat. Při smazaní přidáných tabulek se firma (její databáze) obnovila. Možná stačí jen vytvářet tabulky v jiném schématu, než public.

Aktualizováno: 22.01.2020 – Přidány řádky s datem a sumou uhrazení pro faktury.

Účetní deník se může vygenerovat ve Flexibee z menu Účetnictví/Účetní výstupy/ Účetní deník nebo je přístupný přes API. Můžou ale nastat situace, kdy je potřeba vygenerovaná data rozšířit. V tomto článku je popsán návod, jak si sestavit účetní deník vlastní. Takto se dá sestavit i rozšířený účetní deník o zálohy, nedoúčtované operace a jiné neúčetní položky.

Sestavený dotaz v google sheets lze otevřít zde na listu result a níže je vnořený soubor na ukázku, ale s takto velkým souborem v malém preview okénku není dobrá manipulace.

Je vytvořený z položek účetních dokladů, kde částka DPH z každé položky se nachází na vlastním řádku. Dotaz obsahuje tyto sloupce: ID, Základ v CZK, DPH v CZK, Základ (v cizí měně), DPH (v cizí měně), MD, Dal, MD, (DPH), Dal (DPH), Název firmy, Zúčtováno, Měna, Modul, Typ dokladu, Popis, Kód dokladu, Dat. vyst. Dokladu, Datum zaúčt., Datum splatnosti, Datum úhrady, Účetní. Množství sloupců se dá upravit na listu base.

Účetní deník (accjournal) v klasické podobě se nachází v červeně označené části. V zelené části má účetní deník (accjournalzkldph) sumu základu i DPH v jednom řádku. Po zkopírování je třeba odstranit uvozovky.

Tabulky

V listu base se sestavuje dotaz účetního deníku z databáze Flexibee. Výběr do tabulek proběhl analýzou databáze. Tabulky byly vyfiltrovány na základě obsahu sloupce ucetni, zuctovano a datucto z vygenerované dokumentace databáze. Uvedené sloupce obsahuje mnohem více tabulek. Do účetního deníku byly vybrány jen tabulky, které obsahují zaúčtované položky dokladů:

Tabulka dpolint obsahuje položky interních dokladů z modulů banka, pokladna a interní doklady. Tabulka dpolsklad obsahuje položky interních dokladů z modulu sklad. Leasingové náklady jsou v mdannakl. Tabulka msplatka obsahuje splátkový kalendář z leasingu. Účetní odpisy jsou v mucetniodpisy a daňové v mdanodpisy. V případě, kdy jsou daňové rovny účetním, tak mdanodpisy můžou být účetní. Zařazení, vyřazení a podobné události spjaté s majetkem jsou v tabulce mudalosti.

Pozor při sestavování dotazu. Některé id jsou skloňované např iducetodpisU v mucetodpisY a mmajetEK v idmajetKU!

Tabulka msplatka a mdanodpisy by podle definice neměli být nikdy zaúčtované, ale podle sloupců v databázi by to bylo možné. Tyto řádky se mohou vymazat.

Pozor při sestavování dotazu. Některé id jsou skloňované např iducetodpisU v mucetodpisY a mmajetEK v idmajetKU!

Sloupce

Výběr sloupce se dá vyřadit odkliknutím zahrnout do dotazu. Součástí buňky s názvem sloupce je i napojení na tabulku a aliasy.

Sloupec kod z tabulky uucty má alias zklmd a alias tabulky je uuctyzklmd. Sloupec je napojený přes LEFT JOIN, aby i v případě hodnoty null, byl řádek s položkou zahrnutý do výsledku. Část definice sloupce a napojení je oddělený symbolem &. Ten se využívá ve skrytých listech ke správnému sestavení dotazu.

=CONCATENATE("uuctyzklmd.kod AS zklmd & LEFT JOIN uucty AS uuctyzklmd ON (";A11;".idzklmducet = uuctyzklmd.iducet)")

Spojení (LEFT JOIN) je uvedeno jen u prvního sloupce. Pokud další sloupec používá stejné spojení, tedy stejné id na stejnou tabulku, tak se označí stejnou barvou. Na tabulka účtů (uucty) se položky spojují pře dvě nebo čtyři různá id. Proto vznikají dvě nebo čtyři různá spojení.

uuctyzklmd.kod AS zklmd & LEFT JOIN uucty AS uuctyzklmd ON (dpolfak.idzklmducet = uuctyzklmd.iducet)

uuctyzkldal.kod AS zkldal & LEFT JOIN uucty AS uuctyzkldal ON (dpolfak.idzkldalucet = uuctyzkldal.iducet)

uuctydphmd.kod AS dphmd & LEFT JOIN uucty AS uuctydphmd ON (dpolfak.iddphmducet = uuctydphmd.iducet)

uuctydphdal.kod AS dphdal & LEFT JOIN uucty AS uuctydphdal ON (dpolfak.iddphdalucet = uuctydphdal.iducet)

Tabulka s doklady se napojuje na položku jen přes jedno id, ale hned několik sloupců položky získává svoji hodnotu z dokladu, proto se spojení vytváří jen při definici sloupce s názvem firmy.

aadresar.nazev AS firma & LEFT JOIN ddoklfak ON (ddoklfak.iddoklfak = dpolfak.iddoklfak) LEFT JOIN aadresar ON (aadresar.idfirmy = ddoklfak.idfirmy) …

Podmínky

Na listu result se zadávají podmínky WHERE, jednotlivým příkazům SELECT na tabulky s položkami.

dpolfak.ucetni= true and dpolfak.storno = false and dpolfak.stornopol = false AND ddoklfak.zuctovano = true

A příkazem UNION ALL se spojují jednotlivé dotazy na tabulku do jednoho velkého dotazu na účetní deník.

Uživatelský dotaz

Výsledný dotaz se může uložit mezi uživatelské dotazy. Na demo účtu je dotaz nahraný pod jménem ACCJOURNAL. Používá parametr year, který omezuje výsledek jen na položky zaúčtované v daný rok. Defaultní hodnota je 2018. Uživatelský dotaz ve formátu xml připravený k importu zde.

Ověření

Jestli dotaz vytvoří účetní deník správně se dá ověřit. Tato ukázka demonstruje ověření v tabulkách Excelu. Bude k tomu potřeba vygenerovaný účetní deník z Flexibee ve formátu xlsx. Při tvorbě deníku se zaškrtne OK pro jen zaúčtované položky a rozsah se omezí jen na rok. V ukázce to je rok 2018.

Většina správců databází umí výsledek dotazu převést do čitelného formátu pro Excel. V této ukázce byl výsledek převeden do csv formátu pgAdminem (dále jako účetní deník z csv).

Ověření se dá provést například přes počet řádků, sum podle kódu dokladů, MD a dal účtů.

Počet řádků

Počet řádků se dá ověřit i snadněji než přes kontingenční tabulky. Třeba přes ctrl + šipka a dojít k poslednímu řádku pro deník z flexibee.

Pro kontrolu deníku z dotazu stačí jen zjistit počet řádků z dotazu.

V účetním deníku Flexibee se položky agregují podle dokladu (možná i jiných kritérié) a rozdělí na základ a dph. To sníží množství položek v deníku oproti tomuto dotazu. S dotazem se dá dále pracovat a detail se dá snížit. Hlavní je však, aby se rovnali výsledky ze sum podle kódu dokladů, MD a dal účtů.

Sumy

Otevřeme vygenerovaný účetní deník v Excelu. Pokud se do souboru nic nepřidávalo, tak se rozsah (range) určí správně.

Z vygenerovaného účetního deníku se vytvoří kontingenční tabulku. První se porovnají sumy podle kódu. Nově vytvořený list se pojmenuje pivot_kod. Do hodnot (Values) v kontingenční tabulce se vybere Částka v Kč a do řádků (rows) Int. čis. dokladu (kod).

To samé se provede se i s účetním deníkem z dotazu. V kontingenční tabulce se vybere sumczk (Values) a do řádků kod (rows).

Vzorec k porovnání výsledku mezi účetními deníky vypadá takto:

=IFERROR(VLOOKUP(A4,'[účetní deník z dotazu.csv]pivot_kod'!$A$4:$B$106,2,0),VLOOKUP(VALUE(A4),'[účetní deník z dotazu.csv]pivot_kod'!$A$4:$B$106,2,0))

 

VLOOKUP hledá řádek (A4) po řádku kód v porovnávaném souboru (‘[účetní deník z dotazu.csv]pivot_kod’!$A$4:$B$106) jestli existuje tam existuje stejný kód dokladu a vrací sumu (2) z tohoto dokladu. Funkce je zapouzdřená v IFERROR, protože míchají textové a číselné hodnoty. VALUE převádí textovou hodnotu na číselnou.

Pozor na rozdíl oddělování parametrů funkcí v češtině (;) a v US angličtině(,).

K porovnání stačí sloupec rozdílů nebo podmíněné formátování.

Pokud je rozdíl 0, tak je vše správně a všechny doklady (kódy) z účetního deníku mají stejnou hodnotu. Ještě by se mohlo stát, že účetní deník z dotazu bude obsahovat více dokladů a pak by mohl vykazovat i vyšší celkovou sumu. Pro odhalení tohoto rozdílu stačí vizuálně zkontrolovat celkovou sumu na konci a počet řádků.

Stejným způsobem se můžou zkontrolovat i sumy pro řádky účtů má dáti a dal.

Download

Budu rád za každou konstruktivní poznámku či připomínku do komentářů pod příspěvkem nebo mě přímo kontaktujte

 

Facebook Comments

Napište komentář.

This site uses Akismet to reduce spam. Learn how your comment data is processed.