1 of 4

Datev Export

This function exports a Datev CSV file for a given timeframe. The fuction 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
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

Example Usages

 brixxApi.businessBrixx({
    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,
    isDownload: true
 });

Datev Export SqlDataRead Example

Example for a getDatevData script

This is the main script, which is responible for collection all the data, which needs to be exported.

Available Parameters

@company - If you work with multiple companies, you have to export each company by it's own.
@dateFrom - Start date for the export
@dateUntil - End date for the export

Example Usages

Datev Export sqlMainData Example

This is the script, which is responsible for collection all the address data, which needs to be exported.

Available Parameters

@company - If you work with multiple companies, you have to export each company by it's own.
@dateFrom - Start date for the export
@dateUntil - End date for the export

Example Usages

Datev Export sqlGetCounter Example

This is the main script, which is responsible for collection all the data, which needs to be exported.

Available Parameters

@company - If you work with multiple companies, you have to export each company by it's own.
@dateFrom - Start date for the export
@dateUntil - End date for the export

Example Usages

Datev Export

This function exports a Datev CSV file for a given timeframe. The fuction 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
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

Example Usages

 brixxApi.businessBrixx({
    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,
    isDownload: true
 });

 SELECT 
    head.id AS Auftragsnummer,
    ol.cordlnItemId AS Artikelnummer,
    ol.id AS ArtikelRecordId,
    ol.cordlnParagraph13b AS Paragraph13b,
    ol.cordlnNetOrderValue AS NettoWert,
    ol.cordlnGrossOrderValue AS BruttoWert,
    adr.adrDebitor AS Debitorenkonto,
    0 AS Kreditorenkonto,
    cordlnSalesAccount AS Sachkonto,
    adr.adrTaxId AS SteuerID,
    cordlnTaxKey as Buchungsschluessel,
    '' AS Buchungstext,
    head.cordInvoiceDate AS Rechnungsdatum,
    head.cordInvoiceNumber AS Rechnungsnummer,
    head.cordShippingDate AS Leistungsdatum,
    atta.id AS AttachementId,
    'customerOrder' AS BuchungszaehlerTabelle,
    'cordDatevExportId' AS BuchungszaehlerSpalte,
    CASE WHEN fiac.finaccIsAccountSplitting = 1 THEN fiacsplit.facsIsAutomaticAccount ELSE fiac.finaccIsAutomaticAccount END AS IstAutomatikKonto,
    head.cordInterCompanyOrder as IstInnerbetrieblich
 
 FROM dbo.bbv_customerOrderLine AS ol
 LEFT JOIN [dbo].[customerOrder] AS head ON head.id = ol.cordlnOrderId
 LEFT JOIN address AS adr ON adr.id = head.cordAddressId
 LEFT JOIN brixx_Attachments AS atta ON atta.id in (select AttachmentId from brixx_AttachmentLinks where RecordId = head.id AND TableName = 'customerOrder' ) AND atta.DocumentTypeId = 2
 LEFT JOIN financialAccount AS fiac ON fiac.finaccAccountNumber = cordlnSalesAccount
 LEFT JOIN financialAccountSplitting AS fiacsplit ON fiacsplit.facsAccountId = fiac.id AND fiacsplit.facsSplitAccount = fiac.finaccAccountNumber
 WHERE ol.cordlnOrderId IN 
 (
    SELECT id FROM [dbo].[customerOrder] 
    WHERE cordShippingDate >= @dateFrom AND cordShippingDate <= @dateUntil
    AND cordCompanyId = @company
 )
 AND ol.cordlnNetOrderValue > 0
 AND head.cordStatusId >= 4
 
 UNION
 
 SELECT 
    head.id AS Auftragsnummer,
    ol.sordlnItemId AS Artikelnummer,
    ol.id AS ArtikelRecordId,
    ol.sordlnParagraph13b AS Paragraph13b,
    ol.sordlnNetOrderValue AS NettoWert,
    ol.sordlnGrossOrderValue AS BruttoWert,
    0 AS Debitorenkonto,
    adr.adrKreditor AS Kreditorenkonto,
    sordlnitmProcurementAccount AS Sachkonto,
    adr.adrTaxId AS SteuerID,
    sordlnTaxKey AS Buchungsschluessel,
    '' AS Buchungstext,
    head.sordGoodsReceiptDate AS Rechnungsdatum,
    head.sordInvoiceNumber AS Rechnungsnummer,
    head.sordGoodsReceiptDate AS Leistungsdatum,
    atta.id AS AttachementId,
    'supplierOrder' AS BuchungszaehlerTabelle,
    'sordDatevExportId' AS BuchungszaehlerSpalte,
    CASE WHEN fiac.finaccIsAccountSplitting = 1 THEN fiacsplit.facsIsAutomaticAccount ELSE fiac.finaccIsAutomaticAccount END AS IstAutomatikKonto,
    head.sordInterCompanyOrder as IstInnerbetrieblich
 
 FROM dbo.bbv_supplierOrderLine AS ol
 LEFT JOIN [dbo].[supplierOrder] AS head ON head.id = ol.sordlnOrderId
 LEFT JOIN address AS adr ON adr.id = head.sordAddressId
 LEFT JOIN item AS itm on itm.id = ol.sordlnItemId 
 LEFT JOIN brixx_Attachments AS atta ON atta.id in (select AttachmentId from brixx_AttachmentLinks where RecordId = head.id AND TableName = 'supplierOrder' )
 LEFT JOIN financialAccount AS fiac ON fiac.finaccAccountNumber = ol.sordlnitmProcurementAccount
 LEFT JOIN financialAccountSplitting AS fiacsplit ON fiacsplit.facsAccountId = fiac.id AND fiacsplit.facsSplitAccount = fiac.finaccAccountNumber
 WHERE ol.sordlnOrderId IN 
 (
    SELECT id FROM [dbo].[supplierOrder] 
    WHERE head.sordGoodsReceiptDate >= @dateFrom AND head.sordGoodsReceiptDate <= @dateUntil
    AND sordCompanyId = @company
 )
 AND ol.sordlnNetOrderValue > 0
 AND head.sordStatusId = 5