Sales Item Turnovers

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

← Alle Beispiel-SQLs

Abfrage

SELECT
  material.mat_code,
  materialVariant.name,
  CASE
    WHEN materialVariant.sku IS NOT NULL THEN materialVariant.sku
    ELSE material.sku
  END AS materialVariant_sku,
  matGroup.name,
  salesItem.short_description,
  salesItem.price,
  salesItem.price_unit,
  salesItem.total_discount_amount ,
  ((salesItem.nett_amount + salesItem.total_discount_amount) * salesDocType.multiplier) as salesItem_nettAmount,
  salesItem.delivery_status,
  salesItem.total_amount,
  (salesItem.warehouse_unit_quantity * salesDocType.multiplier) as salesItem_quantity,
  ((salesItem.nett_amount + salesItem.total_discount_amount) / (CASE WHEN salesHead.exchange_rate IS NULL THEN 1 ELSE salesHead.exchange_rate END))* salesDocType.multiplier AS salesItem_nettAmountInHomeCurrency,
CASE
  WHEN (salesItem.warehouse_unit_exchange_ratio is null) THEN (salesItem.open_quantity* salesDocType.multiplier)
  ELSE ((salesItem.open_quantity * salesItem.warehouse_unit_exchange_ratio)* salesDocType.multiplier)
END AS salesItem_openQuantity,
salesItem.customer_mat_code,
salesItem.customer_mat_name,
salesItem.is_third_party,
salesItem.position,
salesDocType.name,
unit.name,
salesHead.doc_number,
salesHead.doc_date,
salesHead.due_date,
salesHead.end_date,
salesHead.exchange_rate,
salesHead.order_description,
salesHead.is_fba,
salesHead.order_email,
CASE
  WHEN (salesHeadStatusTrans.name is null) THEN salesHeadStatus.name
  ELSE salesHeadStatusTrans.name
END AS salesHead_status,
CASE
  WHEN salesHead.invoice_addressline1 IS NOT NULL THEN salesHead.invoice_addressline1
  ELSE salesHead.customer_addressline1
END AS salesHead_invoiceAddressline1,
CASE
  WHEN salesHead.invoice_addressline2 IS NOT NULL THEN salesHead.invoice_addressline2
  ELSE salesHead.customer_addressline2
END AS salesHead_invoiceAddressline2,
CASE
  WHEN salesHead.invoice_city IS NOT NULL THEN salesHead.invoice_city
  ELSE salesHead.customer_city
END AS salesHead_invoiceCity,
CASE
  WHEN salesHead.invoice_province IS NOT NULL THEN salesHead.invoice_province
  ELSE salesHead.customer_province
END AS salesHead_invoiceProvince,
CASE
  WHEN salesHead.invoice_phone IS NOT NULL THEN salesHead.invoice_phone
  ELSE salesHead.customer_phone
END AS salesHead_invoicePhone,
CASE
  WHEN salesHead.invoice_zip_street IS NOT NULL THEN salesHead.invoice_zip_street
  ELSE salesHead.customer_zip_street
END AS salesHead_invoiceZipStreet,
CASE
  WHEN salesHead.invoice_ustd_id IS NOT NULL THEN salesHead.invoice_ustd_id
  ELSE salesHead.customer_ustd_id
END AS salesHead_invoiceUstdId,
CASE
  WHEN salesHead.invoice_gln IS NOT NULL THEN salesHead.invoice_gln
  ELSE salesHead.customer_gln
END AS salesHead_invoiceGln,
customer.customer_no,
customer.name,
customer.datev_debitor_no,
customer.zipcode,
customer.email,
customer.search_keys,
currency.name,
currency.code,
centralCustomer.customer_no,
centralCustomer.name,
CASE
  WHEN (countryTranslation.name is null) THEN country.name
  ELSE countryTranslation.name
END AS country_name,
paymcond.name,
concat(salesPersonName.firstname,' ',salesPersonName.surname) as salesPersonName_name,
marktplace.name,
stockplace.name,
warehouse.name,
CASE
  WHEN salesHead.receiver_addressline1 IS NOT NULL THEN salesHead.receiver_addressline1
  ELSE salesHead.customer_addressline1
END AS salesHead_receiverAddressline1,
CASE
  WHEN salesHead.receiver_addressline2 IS NOT NULL THEN salesHead.receiver_addressline2
  ELSE salesHead.customer_addressline2
END AS salesHead_receiverAddressline2,
CASE
  WHEN salesHead.receiver_province IS NOT NULL THEN salesHead.receiver_province
  ELSE salesHead.customer_province
END AS salesHead_receiverProvince,
CASE
  WHEN salesHead.receiver_city IS NOT NULL THEN salesHead.receiver_city
  ELSE salesHead.customer_city
END AS salesHead_receiverCity,
CASE
  WHEN salesHead.receiver_phone IS NOT NULL THEN salesHead.receiver_phone
  ELSE salesHead.customer_phone
END AS salesHead_receiverPhone,
CASE
  WHEN salesHead.receiver_gln IS NOT NULL THEN salesHead.receiver_gln
  ELSE salesHead.customer_gln
END AS salesHead_receiverGln,
CASE
  WHEN salesHead.receiver_ustd_id IS NOT NULL THEN salesHead.receiver_ustd_id
  ELSE salesHead.customer_ustd_id
END AS salesHead_receiverUstdId,
CASE
  WHEN materialVariant.ean_code IS NOT NULL THEN materialVariant.ean_code
  ELSE material.gtin
END AS material_gtin,
freight.name,
salesItem.is_set_item,
salesItem.is_set_head,
material.pur_price,
salesHead.einvoice_number,
GROUP_CONCAT(serialNumber.number) as serialNumber_numbers,
vendor.id as material_vendorId,
concat(vendor.customer_no, ' - ', vendor.name) as material_vendorName,
salesItem.is_manually_closed as salesItem_isManuallyClosed
FROM sales_head as salesHead
LEFT JOIN sales_item as salesItem on salesItem.sales_head_id = salesHead.id
LEFT JOIN lt_sales_head_status as salesHeadStatus on salesHeadStatus.id = salesHead.sales_head_status_id
LEFT JOIN material as material on material.id = salesItem.material_id
LEFT JOIN customer as vendor on vendor.id = material.vendor_id
LEFT JOIN ltt_mat_group as matGroup on matGroup.id = material.mat_group_id
LEFT JOIN material_variant as materialVariant on materialVariant.id = salesItem.material_variant_id
LEFT JOIN ltt_unit as unit on unit.id = salesItem.warehouse_unit_id
LEFT JOIN ltt_stockplace as stockplace on stockplace.id = salesItem.stockplace_id
LEFT JOIN ltt_warehouse as warehouse on warehouse.id = salesItem.warehouse_id
LEFT JOIN lt_sales_item_delivery_status as salesItemDeliveryStatus ON salesItemDeliveryStatus.id = salesItem.delivery_status
LEFT JOIN lt_sales_item_delivery_status_trans as salesItemDeliveryStatusTranslation on salesItemDeliveryStatusTranslation.sales_item_delivery_status_id = salesItemDeliveryStatus.id AND salesItemDeliveryStatusTranslation.language = 'de'
LEFT JOIN lt_sales_head_status_trans AS salesHeadStatusTrans on salesHeadStatusTrans.sales_head_status_id = salesHeadStatus.id AND salesHeadStatusTrans.language = 'de'
JOIN ltt_sales_doc_type as salesDocType on salesDocType.id = salesHead.doc_type_id
LEFT JOIN ltt_currency as currency on currency.id = salesHead.currency_id
LEFT JOIN customer as customer on customer.id = salesHead.customer_id
LEFT JOIN customer_sales_person AS customerSalesPerson on customerSalesPerson.customer_id = customer.id AND customerSalesPerson.is_main = 1
LEFT JOIN user AS salesPersonName on salesPersonName.id = customerSalesPerson.sales_person_id
LEFT JOIN customer as centralCustomer on centralCustomer.id = customer.central_customer_id
LEFT JOIN lt_country as country on country.id = salesHead.customer_country_id
LEFT JOIN lt_country_trans as countryTranslation on countryTranslation.country_id = centralCustomer.id AND countryTranslation.language = 'de'
LEFT JOIN ltt_paymcond as paymcond on paymcond.id = salesHead.paymcond_id
LEFT JOIN ltt_marktplace as marktplace on marktplace.id = salesHead.marktplace_id
LEFT JOIN ltt_freight as freight on freight.id = salesHead.freight_id
LEFT JOIN sales_item_serial_number as serialNumber on serialNumber.sales_item_id = salesItem.id
WHERE salesHead.tenant_id = :tenantId
	AND salesHead.is_delete = 0
   AND salesItem.is_delete = 0
GROUP BY salesItem.id;