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

1. @company - If you work with multiple companies, you have to export each company by it's own.

2. @dateFrom - Start date for the export

3. @dateUntil - End date for the export

Example Usages

 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