Verification Documents

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

← Alle Beispiel-SQLs

Abfrage

SELECT
    verificationHead.id AS verificationHeadId,
    CONCAT(COALESCE(user.firstname, ''), ' ', COALESCE(user.surname, '')) AS confirmingPersonName,
    CASE
        WHEN verificationDocTypeTrans.id IS NULL THEN verificationDocType.name
        ELSE verificationDocTypeTrans.name
    END AS verificationDocTypeName,
    verificationHead.doc_number AS docNumber,
    verificationHead.doc_date AS docDate,
    customer.id AS customerId,
    customer.customer_no AS customerNumber,
    customer.name AS customerName,
    verificationHead.post_date AS postDate,
    verificationHead.external_invoice_num AS externalInvoiceNumber,
    CASE
        WHEN verificationDocType.multiplier IS NULL THEN verificationHead.net_amount
        ELSE verificationHead.net_amount * verificationDocType.multiplier
    END AS netAmount,
    CASE
        WHEN verificationDocType.multiplier IS NULL THEN verificationHead.brut_amount
        ELSE verificationHead.brut_amount * verificationDocType.multiplier
    END AS brutAmount,
    currency.code AS currencyCode,
    CASE
        WHEN verificationStatusTrans.id IS NULL THEN verificationStatus.status_name
        ELSE verificationStatusTrans.name
    END AS verificationStatusName,
    CASE
        WHEN attachment.id IS NULL THEN 0
        ELSE 1
    END AS hasAttachment,
    verificationHead.is_delete AS isDeleted,
    verificationHead.notes AS notes,
    verificationDocType.id AS verificationDocTypeId,
    verificationStatus.id AS verificationStatusId,
    verificationHead.is_email_send AS isEmailSent,
    paymentCondition.id AS paymentConditionId,
    paymentCondition.name AS paymentConditionName,
    (
        SELECT COALESCE(MAX(finItem.is_datev_exported), 0)
        FROM fin_item finItem
        WHERE 17954 = finItem.tenant_id
          AND finItem.verification_head_id = verificationHead.id
    ) AS isDatevExported,
    GROUP_CONCAT(salesHead.doc_number) AS salesDocNumbers,
    GROUP_CONCAT(salesItem.doc_item_number) AS salesDocItemNumbers,
    GROUP_CONCAT(verificationCheckGroup.name) AS verificationCheckGroupNames,
    GROUP_CONCAT(verificationCheckGroup.color_code) AS verificationCheckGroupColors,
    paymentType.name AS paymentTypeName,
    profitCenter.name AS profitCenterName,
    verificationHead.due_date AS dueDate
FROM verification_head verificationHead
LEFT JOIN customer customer ON verificationHead.vendor_id = customer.id
LEFT JOIN ltt_currency currency ON verificationHead.currency_id = currency.id
LEFT JOIN ltt_verification_doc_type verificationDocType ON verificationHead.verification_doc_type_id = verificationDocType.id
LEFT JOIN verification_head_attachment verificationHeadAttachment ON verificationHead.id = verificationHeadAttachment.verification_head_attachments_id
LEFT JOIN attachment attachment ON verificationHeadAttachment.attachment_id = attachment.id
LEFT JOIN user user ON verificationHead.confirming_person_id = user.id
LEFT JOIN ltt_verification_doc_type_trans verificationDocTypeTrans ON verificationDocType.id = verificationDocTypeTrans.verification_doc_type_id
    AND verificationDocTypeTrans.language = 'en'
LEFT JOIN lt_verification_status verificationStatus ON verificationHead.verification_status_id = verificationStatus.id
LEFT JOIN ltt_paymcond paymentCondition ON verificationHead.payment_condition_id = paymentCondition.id
LEFT JOIN lt_verification_status_trans verificationStatusTrans ON verificationStatus.id = verificationStatusTrans.verification_status_id
    AND verificationStatusTrans.language = 'en'
LEFT JOIN verification_item verificationItem ON verificationHead.id = verificationItem.verification_head_id
    AND verificationItem.is_delete <> 1
LEFT JOIN pur_item purItem ON verificationItem.pur_item_id = purItem.id
LEFT JOIN sales_item salesItem ON purItem.sal_item_requirements_id = salesItem.id
LEFT JOIN sales_head salesHead ON salesItem.sales_head_id = salesHead.id
LEFT JOIN ltt_verification_check_group verificationCheckGroup ON verificationItem.check_group_id = verificationCheckGroup.id
LEFT JOIN ltt_profit_center profitCenter ON verificationHead.profit_center_id = profitCenter.id
LEFT JOIN ltt_payment_type paymentType ON verificationHead.payment_type_id = paymentType.id
WHERE verificationHead.id IS NOT NULL
    AND verificationHead.is_delete = '0'
GROUP BY verificationHead.id
ORDER BY verificationHead.doc_date DESC;