mardi 21 février 2023

SAP Business One query to retrieve sales orders that have not been fully invoiced yet

 SELECT 

    T0.DocNum AS 'SO Number',

    T0.DocDate AS 'SO Date',

    T1.ItemCode AS 'Item Code',

    T1.Dscription AS 'Item Description',

    T1.Quantity AS 'Quantity Ordered',

    ISNULL(T2.Quantity, 0) AS 'Quantity Invoiced'

FROM 

    ORDR T0

    INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry

    LEFT JOIN (

        SELECT 

            BaseEntry, 

            BaseLine, 

            SUM(Quantity) AS Quantity 

        FROM 

            INV1 

        GROUP BY 

            BaseEntry, 

            BaseLine

    ) T2 ON T1.DocEntry = T2.BaseEntry AND T1.LineNum = T2.BaseLine

WHERE 

    T0.DocStatus = 'O'

    AND (T2.Quantity IS NULL OR T1.Quantity > T2.Quantity)


Aucun commentaire:

Enregistrer un commentaire