4.4 DATEV: Data sources

Data sources – part of the DATEV Nuclet documentation (export of CSV files for further processing in DATEV (accounting)).

On this page

This page was machine-translated from German as a first draft and is currently under review. The authoritative source remains the German original (see the language switch above).

4.4.1 Report data sources for the individual DATEV exports

For each DATEV export type ("invoice export", "customer export", "turnover export") a report data source template ("DATEV Client Billing Export", "DATEV Client Export", "DATEV Turnover Export") is provided in the DATEV Nuclet.

These data sources must be adapted per application:

on the one hand to the actually used business objects and database tables,
on the other hand to the column information desired or required in the CSV export.

Please note

The number, order and titles of the columns returned by the SELECT are arbitrary.
The column titles are written to the export CSV exactly as specified (provided the export is configured as "with header row")
The mandatory parameters "date_from" and "date_until" describe the period to be exported.
It is sensible to mark records that have already been exported so that they are not pulled again in a subsequent export.

The marking mentioned in point 4 is performed by the DATEV Nuclet by default. This uses the export date ("Export Date DATEV") in the respective reference objects (see the prerequisites in section 3).

/**
 * Anmerkungen:
 *
 * - "intid_reference" ist ein Pflichtfeld, das der Zuordnung der Referenzobjekte zum DATEV-Export dient
 * - "date_from" und "date_until" sind Pflichtparameter zur Kennzeichnung des zu exportierenden Zeitraumes
 * - "datexportdatumdatev" ist das Export-Datum zur Kennzeichnung bereits exportierter Datensätze (s.o.)
 *
 */

SELECT 
    buchungsposition.intid "intid_reference",
    buchungsposition.datdatum "Datum",
    buchungsposition.strgegenkonto "Gegenkonto",
    buchungsposition.strkonto "Konto",
    buchungsposition.strbelegfeld "Belegfeld 1",    
    buchungsposition.dblumsatz "Umsatz",
    buchungsposition.strwaehrung "Währung",
	buchungsposition.strbuchungstext "Buchungstext"
FROM 
    em65_buchungsposition buchungsposition
WHERE
    buchungsposition.datdatum BETWEEN '$date_from' AND '$date_until'
AND buchungsposition.datexportdatumdatev IS NULL

Source code 4.4.1.1: Example adjustment for the data source "DATEV Turnover"

SELECT 
	-- Diese Kopfzeile wurde folgendem offiziellen Beispiel nachgestellt:
	-- 
	-- https://developer.datev.de/de/file-format/details/datev-format/getting-started
	-- 
	-- Die Datenquelle ist den projekt- bzw. kundenspezifischen Anforderungen
	-- entsprechend anzupassen.
	--
	-- Bearbeiter- oder mitarbeiterbezogene Informationen lasseb sich über einen
	-- entsprechenden JOIN hinzufügen (siehe Kommentarzeilen im SQL unten).
	-- 
	0 "intid_reference",
	'EXTF' "A",  
	700 "B",
	21 "C",
	'Buchungsstapel' "D",
	13 "E",
	to_char(current_timestamp, 'yyyymmddhh24missms') "F",
	null "G",
	'RE' "H",
	'' "I",
	'' "J",
	29098 "K",
	55003 "L",
	to_char(export.datdatefrom, 'yyyymmdd') "M",
	4 "O",
	to_char(export.datdatefrom, 'yyyymmdd') "P",
	to_char(export.datdateuntil, 'yyyymmdd') "Q",
	'Buchungsstapel' "R",
	'WD' "S",
	1 "T",
	0 "U",
	0 "V",
	'EUR' "W",
	null "X",
	'' "Y",
	null "Z",
	null "AA",
	'03' "AB",
	null "AC",
	null "AD",
	'' "AE",
	'' "AF"
FROM 
	YY7M_DATEVEXPORT export--,
	--SD2M_MITARBEITER mitarbeiter 
	--INNER JOIN T_MD_USER benutzer ON mitarbeiter.struid_strbenutzer = benutzer.struid
WHERE
	export.intid = $intidDatevExport
--AND	benutzer.struser = '$username'

Source code 4.4.1.2: Example adjustment for the data source "DATEV Turnover (Header)"

4.4.2 Dynamic entities per export type

The layouts for the DATEV exports are configured per export type. The layouts differ in the dependent subform "References", in which the reference objects contained in an export (e.g. invoices, turnover, customers) can be displayed.

For each DATEV export type ("invoice export", "customer export", "turnover export") a dynamic business object ("DATEV Client Billing References", "DATEV Client References", "DATEV Turnover References") is provided for this purpose in the DATEV Nuclet.

These data sources must be adapted per application:

on the one hand to the actually used business objects and database tables,
on the other hand to the column information to be displayed in the application UI.

Please note

The number, order and titles of the columns returned by the SELECT are arbitrary.
The column titles are displayed by the application as defined in the SQL – but the display behaviour can also be adjusted in the respective layout.
The mandatory parameters "INTID_T_UD_GENERIC_OBJECT" and "INTID" must be present, as is usual for dynamic business objects (see the section "Dynamic business objects" in der Nuclos Wiki).

the context menu function "Show details..." is to be used for the references in DATEV exports, then the respective reference business object used must be selected in the dynamic entity.

/** 
 * Anmerkungen:
 * 
 * - "INTID_T_UD_GENERICOBJECT" und "INTID" sind Pflichtfelder für Dynamische Entitäten (s.o.)
 * - Join zwischen Referenzobjekt "Buchungsposition" und "DATEV Information" erfolgt über Pflichtfeld "DATEV Information" (siehe Abschnitt 3)
 *
 */

SELECT     
    datevinformation.intid "INTID_T_UD_GENERICOBJECT",
    buchungsposition.intid "INTID",
    'Umsatz' "Typ",
    buchungsposition.datdatum "Datum",
    buchungsposition.strbelegfeld "Referenz",
    buchungsposition.dblumsatz "Betrag",
    buchungsposition.strwaehrung "Währung"
FROM 
    def_buchungsposition buchungsposition,
    def_datevinformation datevinformation
WHERE
    datevinformation.intid = buchungsposition.intid_strdatevinformation

Related pages

Nuclet: DATEV (available in the trade Nuclet)

DATEV Nuclet data sheet

Open →

Interfaces

All integrations

Open →