Tengo la siguiente situacion
quiero listar las ordenes de venta | facturas que esten pendientes de entregar o que tengan lineas pendientes de entregar en un query
realize el siguiente query pero el orden me esta afectando el resultado cuando la entrega fue primero que la entrega o casos similares alguno tiene alguna idea?
-- Primera parte (Orden de venta > Entrega > Factura)
SELECT
ORDR."DocNum" AS "SalesOrderNumber",
ODLN."DocNum" AS "DeliveryDocNumber",
OINV."DocNum" AS "InvoiceNumber",
RDR1."ItemCode",
RDR1."Dscription" AS "ItemDescription",
RDR1."Quantity" AS "OrderedQuantity",
IFNULL(SUM(DLN1."Quantity"), 0) AS "DeliveredQuantity",
(RDR1."Quantity" - IFNULL(SUM(DLN1."Quantity"), 0)) AS "PendingDeliveryQuantity"
FROM ORDR
INNER JOIN RDR1 ON ORDR."DocEntry" = RDR1."DocEntry"
LEFT JOIN DLN1 ON RDR1."DocEntry" = DLN1."BaseEntry" AND RDR1."LineNum" = DLN1."BaseLine" AND DLN1."BaseType" = 17
LEFT JOIN ODLN ON DLN1."DocEntry" = ODLN."DocEntry"
LEFT JOIN INV1 ON ODLN."DocEntry" = INV1."BaseEntry" AND INV1."BaseType" = 15
LEFT JOIN OINV ON INV1."DocEntry" = OINV."DocEntry"
GROUP BY ORDR."DocNum", ODLN."DocNum", OINV."DocNum", RDR1."ItemCode", RDR1."Dscription", RDR1."Quantity"
HAVING (RDR1."Quantity" - IFNULL(SUM(DLN1."Quantity"), 0)) > 0
UNION ALL
-- Parte 2: Factura > Entrega (Corregida)
SELECT
'' AS "SalesOrderNumber", -- No hay número de orden de venta directamente relacionado
ODLN."DocNum" AS "DeliveryDocNumber",
OINV."DocNum" AS "InvoiceNumber",
INV1."ItemCode",
INV1."Quantity" AS "OriginalQuantity",
(INV1."Quantity" - IFNULL(SUM(DLN1."Quantity"), 0)) AS "PendingDeliveryQty"
FROM OINV
INNER JOIN INV1 ON OINV."DocEntry" = INV1."DocEntry"
LEFT JOIN DLN1 ON INV1."DocEntry" = DLN1."BaseEntry" AND INV1."BaseLine" = DLN1."BaseLine" AND DLN1."BaseType" = 15
LEFT JOIN ODLN ON DLN1."DocEntry" = ODLN."DocEntry"
GROUP BY ODLN."DocNum", OINV."DocNum", INV1."ItemCode", INV1."Quantity"
HAVING (INV1."Quantity" - IFNULL(SUM(DLN1."Quantity"), 0)) > 0