Payments for Sales Invoices

SQL-Vorlage aus der reybex-Standardbibliothek – für BAS23-Exportjobs und Ad-hoc-Tests.

← Alle Beispiel-SQLs

Abfrage

SELECT
docType.name AS docType_name,
docType.code AS docType_code ,
(CASE WHEN salesHead.doc_number IS NOT NULL
  THEN salesHead.doc_number ELSE (CASE WHEN finOpenItem.invoice_number IS NOT NULL THEN finOpenItem.invoice_number ELSE finOpenItem.fin_doc_number END) END) AS salesHead_docNumber
FROM fin_open_item AS finOpenItem
Left JOIN sales_head as salesHead on salesHead.id = finOpenItem.sales_head_id
Left JOIN customer as customer on customer.id = finOpenItem.customer_id
LEFT JOIN ltt_fin_doc_type as docType on docType.id = finOpenItem.fin_doc_type_id
LEFT JOIN account as offsetAccount on offsetAccount.id = finOpenItem.account_id
LEFT JOIN ltt_account_type AS accountType on accountType.id = offsetAccount.account_type_id
WHERE finOpenItem.tenant_id = :tenantId AND finOpenItem.is_canceled = 0
AND docType.doc_character = 3
ORDER BY finOpenItem.doc_date ASC;