Datev Export

This function exports a Datev CSV file for a given timeframe. The function needs 3 customized SQL scripts to process the data.

As this function calls customized SQL scripts, it will add SQL parameters from the function call to the SQL calls.

• @company

• @dateFrom

• @dateUntil

Those parameters can be used within the SQL statements.

Needed output columns from SQL scripts

• sqlDataRead script

  • Auftragsnummer

  • Artikelnummer

  • ArtikelRecordId

  • Paragraph13b

  • NettoWert

  • BruttoWert

  • Debitorenkonto

  • Kreditorenkonto

  • Sachkonto

  • SteuerID

  • Buchungsschluessel

  • Buchungstext

  • Rechnungsdatum

  • Rechnungsnummer

  • Leistungsdatum

  • AttachementId

  • BuchungszaehlerTabelle (nvarchar that defines the tabel, where the booking counter is stored)

  • BuchungszaehlerSpalte (nvarchar that devines the column for the booking counter)

  • IstAutomatikKonto

  • IstInnerbetrieblich

  • BeleginfoInhalt1 (optional)

• sqlMainData script

  • Kundennummer

  • Konto

  • Firmenname

  • Nachname

  • Vorname

  • Anrede

  • Adresstyp

  • Strasse

  • Hausnummer

  • PLZ

  • Stadt

  • Zusatzinformation

• sqlGetCounter script

  • This script needs to return the actual booking counter as number.

SQL script examples

• sqlDataRead Example

• sqlMainData Example

• sqlGetCounter Example

Parameters

• exportFromDate: Beginning of the period for export.

• exportUntilDate: End of the period for export.

• consultantNumber: Number of your tax consultant.

• clientNumber: Your own number as a client.

• company: Id of your company used in Gebra Suite.

Optional:

• accountLength: Length of accounts, by default it is set to 4.

• startOfBusinessYear: Start of your business year. Format: YYYYMMDD, Example: "20240101".

• culture: Settings for the language settings and formatting of numbers. Examples: "de-DE" or "en-EN".

• exportFileName: Name of the generated zip file, automatically generated by default.

• noPdfExport: If no PDF attachments should be added to the zip file, then this parameter can be passed with "true".

• noPdfFolder: If you want to upload the zip file directly to DATEV, it must not contain any subfolders. With this parameter the PDFs are saved without folders. In this case the parameter should be passed with "true".

• testMode: Activates the test mode for the export. A maximum of 50 PDF attachments are included and no Datev export numbers are written back to the invoices.

Example Usages

Data from SQL function:

app.businessFunction({
  functionName: "DatevExport",
  sqlDataRead: "getDatevData",
  sqlMainData: "getDatevMainData",
  sqlGetCounter: "getDatevBookingCounter",
  exportFromDate: moment()
    .subtract(2, "months")
    .startOf("month")
    .format("YYYY-MM-DD"),
  exportUntilDate: moment()
    .subtract(2, "months")
    .endOf("month")
    .format("YYYY-MM-DD"),
  consultantNumber: 1234567,
  clientNumber: 12345,
  company: 1,
  culture: "de-DE"  
});

Data from grid:

app.businessBrixx({
    functionName:"DatevExport",
    sqlMainData:"getDatevMainDataCustomer",
    sqlGetCounter:"getDatevBookingCounter",
    exportFromDate: app.getFieldValue("dateStartServiceDate"),
    exportUntilDate: app.getFieldValue("dateEndServiceDate"),
    consultantNumber: 4711,
    clientNumber: 4712,
    accountLength: 7,
    company: 3,
    isBackground: true,
    exportFileName: "Verkauf",
    culture: 'de-DE',
    columnMapper : {
        auftragsnummer: "Auftragsnummer",
        artikelnummer: "Artikelbezeichnung",
        waehrung: "Currency",
        kurs: "Kurs",
        artikelRecordId: "LineId",
        attachementId: "AttatchementId",
        bruttoWert: "Bruttowert",
        buchungsschluessel: "Buchungsschlüssel",
        buchungstext: "Debitorenname",
        buchungszaehlerSpalte: "BuchungszählerSpalte",
        buchungszaehlerTabelle: "BuchungszählerTabelle",
        debitorenkonto: "Debitorenkonto",
        istAutomatikkonto: "AutomatikKonto",
        istInnerbetrieblich: "InterCompanyOrder",
        belegInfoInhalt1: "externeAuftragsnummer",
        leistungsdatum: "Leistungsdatum",
        generalUmkehr: "GeneralUmkehr",
        gutschrift: "Gutschrift",
        nettoWert: "Nettowert",
        paragraph13b: "§13b",
        rechnungsdatum: "Rechnungsdatum",
        rechnungsnummer: "Rechnungsnummer",
        sachkonto: "KontoNummer",
        steuerID: "UStID",
        basisumsatz: "Basisumsatz",
        basiswaehrung: "Basiswaehrung",
        sollHabenTausch: "SollHabenTausch",
        buchungstyp: "Buchungstyp"

    },
    datevData: selectedRowsFromGrdBooking
});