Sales Documents

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

← Alle Beispiel-SQLs

Abfrage

SELECT
salesDocType.name,
marktplace.name,
supportedMarketplace.name,
customerCountry.name,
customerCountry.code,
receiverCountry.name,
receiverCountry.code,
shipping.name,
carrier.short_name,
(CASE WHEN salesHead.receiver_addressline1 IS NULL THEN salesHead.customer_addressline1 ELSE salesHead.receiver_addressline1 END) as receiverAddressline1,
(CASE WHEN salesHead.receiver_addressline2 IS NULL THEN salesHead.customer_addressline2 ELSE salesHead.receiver_addressline2 END)  as receiverAddressline2,
(CASE WHEN salesHead.receiver_zip_street IS NULL THEN salesHead.customer_zip_street ELSE salesHead.receiver_zip_street END)  as receiverZipStreet,
(CASE WHEN salesHead.receiver_street1 IS NULL THEN salesHead.customer_street1 ELSE salesHead.receiver_street1 END) as receiverStreet1,
(CASE WHEN salesHead.receiver_street2 IS NULL THEN salesHead.customer_street2 ELSE salesHead.receiver_street2 END) as receiverStreet2,
(CASE WHEN receiverCountry.name IS NULL THEN customerCountry.name ELSE receiverCountry.name END) as receiverCountry,
(CASE WHEN receiverCountry.code IS NULL THEN customerCountry.code ELSE receiverCountry.code END) as receiverCountryCode,
(CASE WHEN receiverCountry.id IS NULL THEN customerCountry.id ELSE receiverCountry.id END) as receiverCountryId,
(CASE WHEN salesHead.receiver_city IS NULL THEN salesHead.customer_city ELSE salesHead.receiver_city END) as receiverCity,
(CASE WHEN salesHead.receiver_province IS NULL THEN salesHead.customer_province ELSE salesHead.receiver_province END) as receiverProvince,
(CASE WHEN salesHead.receiver_phone IS NULL THEN salesHead.customer_phone ELSE salesHead.receiver_phone END) as receiverPhone,
(CASE WHEN salesHead.receiver_fax IS NULL THEN salesHead.customer_fax ELSE salesHead.receiver_fax END) as receiverFax,
(sELECt province.code fROm lt_province province wHERe province.name = salesHead.customer_province) as customerProvinceCode,
(sELECt province.code fROm lt_province province wHERe province.name = salesHead.receiver_province) as receiverProvinceCode,
(sELECt province.code fROm lt_province province wHERe province.name = salesHead.invoice_province) as invoiceProvinceCode,
'' as receiverProvince2
FROM sales_head salesHead
JOIN ltt_sales_doc_type as salesDocType on salesDocType.id = salesHead.doc_type_id
LEFT JOIN ltt_marktplace as marktplace on marktplace.id = salesHead.marktplace_id
LEFT JOIN lt_supported_marktplace as supportedMarketplace on supportedMarketplace.id = marktplace.supported_marketplace_id
LEFT JOIN ltt_shipping as shipping on shipping.id = salesHead.shipping_id
LEFT JOIN ltt_shipping_carrier as carrier on carrier.id = salesHead.carrier_id
LEFT JOIN lt_country as customerCountry on customerCountry.id = salesHead.customer_country_id
LEFT JOIN lt_country as receiverCountry on receiverCountry.id = salesHead.receiver_country_id
WHERE salesHead.is_delete = 0
AND salesHead.tenant_id = :tenantId;