Query for outstanding order

[Former Member]

Hi all,

I'm using UDF to define qty(case) in nos, this is additional to the existing qty (Sqm). I need query to find the balance qty for any sales order. The problem is my report considers only the latest delivery note qty(case) instead of all the deliveries made against the single sales order. The query is,

SELECT T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[TaxDate], T1.[U_EA_Case], Sum(T2.[U_EA_Case]) as 'Delivered', case when Sum (T2.[U_EA_Case]) is null  then T1.[U_EA_Case] else T1.[U_EA_Case]- SUM(T2.[U_EA_Case]) end 

FROM ORDR T0 

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

left join DLN1 T2 on  T1.[ItemCode]=T2.[ItemCode]  and T1.[U_EA_Packgroup]=T2.[U_EA_Packgroup] 

where T1.[LineStatus]='O'

Group by T1.[ItemCode], T1.[Dscription], T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[NumAtCard], T0.[TaxDate], T1.[U_EA_Case], T1.[U_EA_Packgroup]

I require all deliveries made against single sales order to be shown as output.

Can anyone help me?

With Kind Regards

Hari

[Johan_H · Accepted solution]

Hi Hari,

You need to turn your FROM clause around. Start with DLN1 and join ORDR. Something like this:

FROM DLN1 T0

INNER JOIN RDR1 T1 ON T0.BaseEntry = T1.DocEntry AND T0.BaseLine = T1.LineNum

INNER JOIN ORDR T2 ON T1.DocEntry = T2.DocEntry

Regards,

Johan

[Former Member · Accepted solution]

Thanks your response,

This is the code I used, but still all the delivery orders are not considered

SELECT T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case], Sum(T0.[U_EA_Case]) as 'Delivered', case when Sum (T0.[U_EA_Case]) is null  then T1.[U_EA_Case] else T1.[U_EA_Case]- SUM(T0.[U_EA_Case]) end 

FROM DLN1 T0

INNER JOIN RDR1 T1 ON T0.BaseEntry = T1.DocEntry AND T0.BaseLine = T1.LineNum

INNER JOIN ORDR T2 ON T1.DocEntry = T2.DocEntry

and T1.[ItemCode]=T0.[ItemCode]  and T1.[U_EA_Packgroup]=T0.[U_EA_Packgroup]

Group by T1.[ItemCode], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case], T1.[U_EA_Packgroup]

[Johan_H · Accepted solution]

Hi Hari,

Please test your code again, but this time comment out and T1.[U_EA_Packgroup]=T0.[U_EA_Packgroup]

Regards,

Johan

[Former Member · Accepted solution]

Thanks for your reponse

SELECT T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case], sum(T0.[U_EA_Case]) as 'Delivered'

FROM DLN1 T0

INNER JOIN RDR1 T1 ON T0.BaseEntry = T1.DocEntry AND T0.BaseLine = T1.LineNum

INNER JOIN ORDR T2 ON T1.DocEntry = T2.DocEntry

Group by T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case]

Used, this code.

1) Now I not getting sales orders with none being delivered. The output of the query is list of items with atleast 1no(case) being delivered.

2) I need all Open Sales orders, I tried adding T2.[LineStatus] = 'O' before Group By but its not executing

3) Also, I need to bring abt the balance qty (Ordered cases - Delivered cases)

How to bring about?

[Former Member · Accepted solution]

Hi,

Try this first:

SELECT T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case], sum(ISNULL(T0.[U_EA_Case],0)) as 'Delivered'

FROM RDR1 T1

INNER JOIN ORDR T2 ON T1.DocEntry = T2.DocEntry

LEFT JOIN DLN1 T0 ON T0.BaseEntry = T1.DocEntry AND T0.BaseLine = T1.LineNum

Group by T1.[ItemCode], T1.[U_EA_Packgroup], T1.[Dscription], T2.[CardName], T2.[DocNum], T2.[DocDate], T2.[NumAtCard], T2.[TaxDate], T1.[U_EA_Case]

Thanks,

Gordon

[Former Member · Accepted solution]

Thank you!

I need to add the balance to it (Ordered - Delivered)

[Johan_H · Accepted solution]

Hi Hari,

Please use RDR1.OpenQty for open balance.

Regards,

Johan

[Former Member · Accepted solution]

I cannot use it, coz..

I'm using UDF to define qty(case) in nos, this is additional to the existing qty (Sqm). I need query to find the balance qty for any sales order.

[Johan_H · Accepted solution]

Ok, then use your UDF, and when you compare it to a delivered quantity (DLN1) make sure to use the ISNULL() function.