Production Orders

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

← Alle Beispiel-SQLs

Abfrage

SELECT
    prodOrder.id AS prodOrderId,
    prodOrder.doc_number AS docNumber,
    prodOrder.prod_order_levels AS prodOrderLevels,
    prodOrder.status AS status,
    DATE_FORMAT(prodOrder.plan_start, '%Y-%m-%d') AS planStartDate,
    DATE_FORMAT(prodOrder.plan_end, '%Y-%m-%d') AS planEndDate,
    prodOrder.calc_start AS calcStart,
    prodOrder.calc_end AS calcEnd,
    prodOrder.quantity AS quantity,
    prodOrder.quantity_produced AS quantityProduced,
    prodOrder.variant_code AS variantCode,
    prodOrder.attribute_code AS attributeCode,
    prodOrder.batchnum AS batchNumber,
    prodOrder.quantity - prodOrder.quantity_produced AS quantityRemaining,
    prodOrder.order_description AS orderDescription,
    prodDocType.id AS docTypeId,
    prodDocType.name AS docTypeName,
    material.id AS materialId,
    material.mat_code AS materialCode,
    material.sku AS materialSku,
    material.name AS materialName,
    material.description AS materialDescription,
    salesHead8.order_description AS salesOrderDescription,
    salesHead8.connector_reference_number AS connectorReferenceNumber,
    salesHead12.order_description AS mainSalesOrderDescription,
    salesHead12.connector_reference_number AS mainConnectorReferenceNumber,
    GROUP_CONCAT(prodOrderParent.doc_number) AS parentProdOrderNumbers,
    GROUP_CONCAT(CONCAT(customer6.customer_no, ' - ', customer6.name)) AS customerList,
    GROUP_CONCAT(CONCAT(salesHead8.doc_number, ' - ', salesItem7.position)) AS salesItems,
    GROUP_CONCAT(CONCAT(salesHead12.doc_number, ' - ', salesItem11.position)) AS mainSalesItems,
    GROUP_CONCAT(CONCAT(customer13.customer_no, ' - ', customer13.name)) AS mainCustomerList,
    prodOrder.stock_status AS stockStatus,
    prodOrder.stock_status_message AS stockStatusMessage,
    (
        SELECT SUM(stockList.avail)
        FROM stock_list stockList
        WHERE material.id = stockList.material_id
          AND (
                prodOrder.variant_code = stockList.variant_code
                OR (prodOrder.variant_code IS NULL AND stockList.variant_code IS NULL)
          )
    ) AS availableStock,
	(
        SELECT GROUP_CONCAT(CONCAT(machine.machine_code, ' - ', machine.name))
        FROM prod_order_operation prodOrderOp
        INNER JOIN machine ON prodOrderOp.machine_id = machine.id
        WHERE prodOrderOp.prod_order_id = prodOrder.id
        GROUP BY prodOrderOp.prod_order_id
    ) AS machineList
FROM prod_order prodOrder
LEFT JOIN material ON prodOrder.material_id = material.id
LEFT JOIN ltt_prod_doc_type prodDocType ON prodOrder.doc_type_id = prodDocType.id
LEFT JOIN prod_order_relation relation3 ON prodOrder.id = relation3.prod_order_id
LEFT JOIN prod_order_component prodOrderComponent ON relation3.prod_order_component_id = prodOrderComponent.id
LEFT JOIN prod_order prodOrderParent ON prodOrderComponent.prod_order_id = prodOrderParent.id
LEFT JOIN customer customer6 ON relation3.customer_id = customer6.id
LEFT JOIN sales_item salesItem7 ON relation3.sales_item_id = salesItem7.id
LEFT JOIN sales_head salesHead8 ON salesItem7.sales_head_id = salesHead8.id
LEFT JOIN prod_order mainProdOrder ON prodOrder.main_prod_order_id = mainProdOrder.id
LEFT JOIN prod_order_relation relation10 ON mainProdOrder.id = relation10.prod_order_id
LEFT JOIN sales_item salesItem11 ON relation10.sales_item_id = salesItem11.id
LEFT JOIN sales_head salesHead12 ON salesItem11.sales_head_id = salesHead12.id
LEFT JOIN customer customer13 ON relation10.customer_id = customer13.id
WHERE prodOrder.tenant_id = :tenantId
AND prodOrder.is_delete = 0
GROUP BY prodOrder.id
ORDER BY prodOrder.id DESC;