Example for a getDatevData script
This is the main script, which is responible for collection all the data, which needs to be exported.
@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
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