Dodatkowe kolumny na liście faktur KSeF w Comarch ERP Optima – JSON i MSSQL

Naszym zadaniem będzie wyciągnięcie dodatkowych kolumn na liście faktur z KSeF w systemie ERP na podstawie wizualizacji w MSSQL. Wykorzystamy fakt, że w Comarch ERP Optima wizualizacja faktury jest przechowywana w różnych formatach, w tym w formacie JSON.

Czego dowiesz się z tego artykułu:

• Analiza struktury e-faktury: Poznasz logiczny podział dokumentu JSON zastosowanego w bazie danych Comarch ERP Optima.

• Wyjaśnienie i gotowy kod SQL: Otrzymasz dokładne omówienie działania skryptów, które możesz bezpośrednio wdrożyć i uruchomić w swojej instalacji OPTIMA.

• Praktyczne przykłady: Zobaczysz krok po kroku, jak wyciągnąć na listy dokumentów termin płatności, numer rachunku bankowego oraz nazwę systemu ERP Twojego dostawcy.

• Informacje o szkoleniu: Na końcu artykułu znajdziesz szczegóły oraz zaproszenie na nadchodzące, praktyczne warsztaty z podstaw SQL, które pozwolą Ci na samodzielne tworzenie zaawansowanych filtrów i kolumn.

Wcześniej publikowałem już artykuł o formacie JSON i jego natywnym wsparciu w bazie MSSQL:
https://www.systemyit.pl/poznaj-jeisona-krola-danych-wladce-umyslow-ai-i-przyjaciela-twojego-erp/

Od wersji MSSQL 2016 format JSON jest wspierany natywnie. Dzięki temu możemy w uproszczony sposób dostać się do danych wewnątrz struktury faktury pobranej z KSeF.

Zadanie: Kolumna z terminem płatności na liście faktur z KSeF

W pierwszej kolejności zajmijmy się analizą struktury danych odpowiedzialnej za przechowywanie pobranych faktur z Krajowego Systemu e-Faktur.

Jako użytkownik systemu zapewne nie posiadasz pełnego opisu struktur tabel w MSSQL dla programu Comarch ERP Optima. Jednak producent oprogramowania, tworząc i rozwijając system, niezmiennie dba o spójność nazw tabel oraz kolumn.

Jako oficjalny Partner Comarch bardzo doceniamy fakt, że system jest rozwijany zgodnie z najwyższymi standardami technologicznymi.

Ma to dla nas kluczowe znaczenie, ponieważ wysoka kultura programistyczna twórców oprogramowania znacznie ułatwia namierzenie odpowiednich danych.

Analizując strukturę bazy danych Comarch ERP Optima, trafimy na tabelę o nazwie CDN.DokumentyKSeF – to jest właśnie miejsce, w którym zapisywane są dokumenty z KSeF. W tym zadaniu szczególnie interesować nas będzie kolumna DKF_Wartosc, która przechowuje pełny obraz faktury w formacie JSON.

Poniżej uproszczona wizualizacja struktury JSON faktury pobranej z KSEF:

Struktura danych KSeF w formacie JSON w COMARCH ERP OPTIMA

Struktura dokumentu JSON w bazie danych odzwierciedla logiczny podział e-Faktury na kluczowe sekcje biznesowe i techniczne. Zrozumienie tego układu pozwala na szybkie odnalezienie i wyciągnięcie potrzebnych informacji za pomocą zapytań SQL.

Oto najważniejsze obszary danych, które znajdziemy w strukturze:

• Nagłówek (Naglowek) – zawiera metadane techniczne faktury, m.in. wersję struktury logicznej (np. FA (3)), dokładną datę wytworzenia pliku oraz identyfikację systemu ERP, w którym dokument wystawiono.

• Podatnik (Podatnik / Podmiot1) – kompletny blok danych sprzedawcy. Znajduje się tu NIP, nazwa pełna i skrócona, adres rejestrowy firmy oraz dane kontaktowe, takie jak adres e-mail czy telefon.

• Nabywca (Nabywca / Podmiot2) – sekcja dedykowana danym kupującego, zawierająca jego NIP, nazwę, adres oraz ewentualne wewnętrzne identyfikatory kontrahenta.

• Główny blok faktury (Fa) – serce całego dokumentu, w którym zawarte są kluczowe informacje handlowe i finansowe. Dzieli się na kilka istotnych podsekcji:

  • Płatność (Platnosc) – przechowywane są tu tablice z terminami płatności (liczba dni, data zapłaty) oraz szczegóły dotyczące rachunków bankowych (nazwa banku, numery kont, kody SWIFT).

  • Metadane biznesowe – podstawowe parametry dokumentu: numer faktury (NumerFaktury), waluta (KodWaluty), data wystawienia oraz powiązane dokumenty (np. numery wydań magazynowych NumerWZ).

  • Podsumowanie finansowe – zagregowane wartości dokumentu: sumy netto i VAT w rozbiciu na poszczególne stawki podatkowe (podstawowa, obniżone) oraz końcowa kwota brutto (SumaBrutto).

  • Adnotacje (Adnotacje) – zestaw znaczników określających specyficzne procedury podatkowe (np. oznaczenie metody kasowej, samofakturowania czy podzielonej płatności / MPP).

  • Pozycje faktury (FaWiersze) – szczegółowa lista towarowa (FaWiersz). Każda pozycja zawiera m.in. liczbę porządkową, nazwę produktu, indeks, kod EAN, jednostkę miary, ilość, cenę jednostkową, wartość netto oraz stawkę VAT.

• Stopka (Stopka) – dodatkowe informacje o podmiocie, w tym numery rejestrowe takie jak REGON czy BDO.

Jak z tej struktury wyciągnąć np. termin płatności:

Aby wyciągnąć termin płatności jako osobną kolumnę na liście faktur KSeF, musimy użyć funkcji JSON_VALUE oraz precyzyjnie wskazać ścieżkę do interesującego nas elementu.

Treść kolumny COMARCH ERP OPTIMA zwracająca termin płatności z faktury w KSEF:

CAST(JSON_VALUE(CAST(DKF_Wartosc AS NVARCHAR(MAX)), '$.Fa.Platnosc.TerminyPlatnosci[0].TerminPlatnosci’) AS DATE)

Jeżeli po wklejeniu definicji kolumn masz błędy (serwer www może zamieniać znaki), poniżej plik TXT do pobrania:
Definicja SQL kolumn użytkownika KSEF OPTIMA

Anatomia zapytania – jak to działa?

• CAST(DKF_Wartosc AS NVARCHAR(MAX)) – Pole z JSON-em w bazie Optimy bezpieczniej jest jawnie przekonwertować na typ tekstowy, aby funkcje MS SQL do obsługi JSON działały bezbłędnie.

• $.Fa.Platnosc.TerminyPlatnosci[0] – Struktura e-Faktury zakłada, że terminów płatności może być kilka (stąd zapis w formie tablicy). Użycie indeksu [0] informuje SQL Server, że chcemy pobrać pierwszy element z tej listy.

• CAST(... AS DATE) – Domyślnie data w pliku JSON zapisana jest z pełnym znacznikiem czasu (np. 2026-04-29T00:00:00). Konwersja do typu DATE skraca ją do czytelnego formatu RRRR-MM-DD.

Numer rachunku bankowego z KSEF wskazany do przelewu – czy jest prawidłowy?:

JSON_VALUE(CAST(DKF_Wartosc AS NVARCHAR(MAX)), '$.Fa.Platnosc.RachunekBankowy[0].NrRachunkuPL’)

Posiadając numer bankowy z FAKTURY z KSEF możemy przeprowadzić już bardziej dokładną kontrolę – np. sprawdzić , czy wskazany rachunek znajduje się na liście rachunków klienta i białej liście.

Inne, przykładowe metody filtrowania dokumentów pod kątem kontroli umówiłem tu:
https://www.systemyit.pl/poziom-zaufania-dokumentu-w-ksef-jak-skutecznie-wyfiltrowac-bledne-faktury/

Jeżeli po wklejeniu definicji kolumn masz błędy (serwer www może zamieniać znaki), poniżej plik TXT do pobrania:
Definicja SQL kolumn użytkownika KSEF OPTIMA

Istnieje możliwość stworzenia np. filtrowania , które zwróci jedynie te faktury, które zawierają numery rachunków nie powiązane z naszymi klientami.

Ciekawość nie zna granic. Może chcesz się dowiedzieć na jakim systemie ERP pracuje Twój dostawca?

W strukturach KSEF przewidziano opcjonalne pole NazwaSystemuERP , nie jest ono obowiązkowe , więc musimy zabezpieczyć się przed jego brakiem.

Oto treść kolumny wyświetlająca nazwę systemu w którym została wystawiona faktura u Twojego dostawcy:

ISNULL(JSON_VALUE(CAST(DKF_Wartosc AS NVARCHAR(MAX)), '$.Naglowek.NazwaSystemuERP’), 'Brak danych’)

Instrukcję wklejamy w definicję kolumny w COMARCH ERP OPTIMA

Po poprawnym zdefiniowaniu kolumny ujrzymy listę systemów ERP naszych dostawców:

Struktury Comarch ERP Optima i JSON w MSSQL umożliwiają personalizację widoków oraz zaawansowany scoring dokumentów (już niedługo artykuły na ten temat).

Jeżeli po wklejeniu definicji kolumn masz błędy (serwer www może zamieniać znaki), poniżej plik TXT do pobrania: Definicja SQL kolumn użytkownika KSEF OPTIMA

Jeśli chcesz w pełni wykorzystać ten potencjał, skontaktuj się z nami! Pomożemy Ci zdefiniować dodatkowe kolumny, stworzyć dedykowane filtry oraz funkcje dopasowane do Twojego biznesu. Oferujemy profesjonalne wsparcie w codziennej pracy z Comarch ERP Optima. Zoptymalizujemy Twoje środowisko pracy, rozwiążemy bieżące problemy i zwiększymy wydajność systemu.

Zachęcamy również do śledzenia naszej strony na Facebooku! Na bieżąco publikujemy tam informacje o nowościach w KSeF oraz Comarch ERP Optima. Dzielimy się praktycznymi wskazówkami i technologiami AI, które skutecznie automatyzują codzienne procesy i usprawniają zarządzanie firmą. Dołącz do naszej społeczności, polub nasz profil i bądź na bieżąco z nowościami!

https://www.facebook.com/SystemyITpl

Chcesz wycisnąć z Comarch ERP Optima jeszcze więcej? Wkrótce ruszamy z praktycznym szkoleniem z tworzenia własnych kolumn, zaawansowanych filtrów i funkcji SQL. Liczba miejsc będzie ograniczona.

Zostaw swój e-mail poniżej, aby zapisać się na listę oczekujących – otrzymasz powiadomienie o starcie zapisów jako pierwszy!

Dodatkowe kolumny na liście faktur KSeF w Comarch ERP Optima – JSON i MSSQL