Fulfillment Orders

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

← Alle Beispiel-SQLs

Abfrage

SELECT
salesDocType.name,
salesDocType.is_order,
CASE
  WHEN salesHead.status = 0 THEN 'Offen'
  WHEN salesHead.status = 1 THEN 'Teilweise offen'
  WHEN salesHead.status = 2 THEN 'Geschlossen'
  WHEN salesHead.status = 3 THEN 'Zusammengefasst'
  ELSE ''
END AS salesHeadStatusDesc,
material.mat_code,
material.sku,
material.name,
unit.code,
unit.name,
warehouse.code,
warehouse.name,
warehouse.is_external,
warehouse.is_disabled,
stockplace.stockplace_code,
stockplace.name,
customerCountry.code,
customerCountry.name,
receiverCountry.name,
receiverCountry.code,
paymentStatus.name,
paymentStatus.type,
paymentStatusTrans.name,
CASE
  WHEN paymentStatusTrans.name IS NULL THEN paymentStatus.name
  ELSE paymentStatusTrans.name
END AS paymentStatusTranslatedName
FROM sales_head salesHead
JOIN ltt_sales_doc_type as salesDocType on salesDocType.id = salesHead.doc_type_id
LEFT JOIN sales_item as salesItem on salesItem.sales_head_id = salesHead.id
LEFT JOIN material as material on material.id = salesItem.material_id
LEFT JOIN ltt_unit as unit on unit.id = salesItem.unit_id
LEFT JOIN ltt_warehouse as warehouse on warehouse.id = salesItem.warehouse_id
LEFT JOIN ltt_stockplace as stockplace on stockplace.id = salesItem.stockplace_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
LEFT JOIN lt_payment_status as paymentStatus on paymentStatus.id = salesHead.payment_status_id
LEFT JOIN lt_payment_status_trans as paymentStatusTrans on paymentStatusTrans.payment_status_id = paymentStatus.id
  AND paymentStatusTrans.language = 'de'
WHERE salesHead.tenant_id = :tenantId AND salesHead.is_delete = 0 AND salesItem.is_delete = 0
AND salesHead.is_manually_closed = 0 AND salesHead.is_canceled = 0
ORDER BY salesHead.doc_number, salesItem.position;