SAP B1 Open Sales Order Balance

[former_member566316]

Hi

I am trying to create a custom backorder report using standard and user-defined fields on the sales order entry through query generator- but I am having problems finding the current sales order value.

I can only bring up the original order total value [ORDR DocTotal]- even if a partial delivery has been made from the order. So I can get the original document total, just not the current document total. I really just need the current order total value to display (not line by line).

Any help would be appreciated. Thanks

[former_member355302 · Accepted solution]

Hello Lauren,

You can user sub query.

Calculate value from delivery based on sales order details and subtract value from sales order doc total.

[former_member566316 · Accepted solution]

Thanks Ashish,

Would this work if there have been multiple deliveries from the same sales order?

Lauren

[former_member355302 · Accepted solution]

Hello Luren,

yes it will work

[former_member566316 · Accepted solution]

Hi Ashish,

I have the below query:

SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[Comments], T0.[DocTotal] AS 'Original Sales Order Total', T1.[DocTotal] AS 'Delivered to Date', T0.[DocTotal] - T1.[DocTotal] AS 'Balance Remaining' FROM ORDR T0 LEFT JOIN ODLN T1 ON T0.NumAtCard = T1.NumAtCard WHERE T0.[DocStatus] = 'O' ORDER BY T0.[DocDueDate]

But if a job has had multiple delivery dockets, it is listing the sales order more than once. Ideally I need a way to total the delivery docket values (per sales order), to then deduct from the original sales order value, to give the balance remaining.

Alternatively, I tried taking the open quantity on sales order row multiplied by unit price to give open value. But again, it lists all the item rows, rather than a document total.

Any ideas?

[Former Member · Accepted solution]

Hi Lauren

As Ashish suggested, using sub queries is the best way to go. Using the NumAtCard to join the 2 tables is risky as the NumAtCard can be edited on most documents. I have worked on the basis of a tax inclusive value, but you could take the T2.[VatSum] out of the sub queries, and subtract T0.[VatSum] from T0.[DocTotal] to get exclusive values.

Try this:

SELECT DISTINCT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], T0.[Comments], T0.[DocTotal] AS 'Original Sales Order Total',

ISNULL((SELECT SUM(T2.[LineTotal] + T2.[VatSum]) FROM DLN1 T2 WHERE T2.BaseEntry = T0.DocEntry AND T2.BaseType = '17' GROUP BY T2.BaseEntry),0)  AS 'Delivered to Date',

(T0.[DocTotal] - ISNULL((SELECT SUM(T2.[LineTotal] + T2.[VatSum]) FROM DLN1 T2 WHERE T2.BaseEntry = T0.DocEntry AND T2.BaseType = '17' GROUP BY T2.BaseEntry),0)) AS 'Balance Remaining'

FROM ORDR T0

WHERE T0.[DocStatus] = 'O'

ORDER BY T0.DocNum, T0.[DocDueDate]

Kind regards

Peter Juby

[former_member566316 · Accepted solution]

Thanks Peter, I'll give that a go

[KennedyT21]

Post the query here to provide solution.

Regards

Kennedy