If SO is closed without delivery or invoices. How to make this query. Please help me

[Former Member]

Hi Experts,

Kindly Tell me.

I need SALES ORDER By SALES EMPLOYEES sql query which derives from Sales Analysis Report.

I have written sql code. But  If Sales order is closed without delivery or invoices. How to write the sql query condition for applied amount.

Kindly find the attachment. If this code runs, If SO is closed, Sales amount and applied amount should be equal but it shows higher value.

Please tell me.

SELECT distinct  T0.[DocNum] 'Document',

'1 of 1'Instalment,

T1.[SlpName]'Sales Employee',

T0.[DocDate],

T0.[DocDueDate],

T2.[CardName]'Customer Name',

T0.[DocTotal] - T0.[Vatsum]

END As'Sales Amount', 

CASE

WHEN (T0.PaidToDate) = 0.000 THEN 0.000

  ELSE ((T0.PaidToDate - T0.Vatpaid))

  EnD As'Applied Amount',

(T0.[DocTotal] - T0.[Vatsum]) - (T0.PaidToDate - T0.Vatpaid) 'Open amount',

Isnull(A.[Open Gp],0)'Open Buying Price',

((T0.[DocTotal] - T0.[Vatsum]) - (T0.PaidToDate - T0.Vatpaid)) - Isnull(A.[Open Gp],0) 'Open GrossProfit',

T0.[Grosprofit],

CASE

WHEN (T0.DocTotal) = 0.000 THEN 0.000

ELSE

((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100 EnD as'GrossProfit %'

FROM [dbo].[ORDR]  T0 INNER JOIN [dbo].[OSLP]  T1 ON T0.[SlpCode] = T1.[SlpCode]

INNER JOIN [dbo].[OCRD]  T2 ON T0.[CardCode] = T2.[CardCode]

Inner Join rdr1 t3 on T0.docentry = T3.docentry

Left Join (Select T0.docentry,sum(T1.[GrossBuyPr] * T1.[OpenQty]) 'Open Gp' from Ordr t0 inner join rdr1 t1

on t0.docentry = t1.docentry and t1.linestatus = 'O'

group by

T0.docentry) A on A.DocEntry = T0.DocEntry

WHERE T0.[DocDate] between [%0] and [%1]

and T0.CANCELED = 'N'

Order By

T1.Slpname,T0.DocDate

[kothandaraman_nagarajan]

Hi,

Please try,

SELECT distinct  T0.[DocNum] 'Document',

'1 of 1'Instalment,

T1.[SlpName]'Sales Employee',

T0.[DocDate],

T0.[DocDueDate],

T2.[CardName]'Customer Name',

T0.[DocTotal] - T0.[Vatsum]

END As'Sales Amount',

CASE

WHEN (T0.PaidToDate) = 0.000 THEN 0.000

  ELSE ((T0.PaidToDate - T0.Vatpaid))

  EnD As'Applied Amount',

(T0.[DocTotal] - T0.[Vatsum]) - (T0.PaidToDate - T0.Vatpaid) 'Open amount',

Isnull(A.[Open Gp],0)'Open Buying Price',

((T0.[DocTotal] - T0.[Vatsum]) - (T0.PaidToDate - T0.Vatpaid)) - Isnull(A.[Open Gp],0) 'Open GrossProfit',

T0.[Grosprofit],

CASE

WHEN (T0.DocTotal) = 0.000 THEN 0.000

ELSE

((T0.GrosProfit) / ((T0.DocTotal - T0.VatSum) - T0.TotalExpns)) * 100 EnD as'GrossProfit %'

FROM [dbo].[ORDR]  T0 INNER JOIN [dbo].[OSLP]  T1 ON T0.[SlpCode] = T1.[SlpCode]

INNER JOIN [dbo].[OCRD]  T2 ON T0.[CardCode] = T2.[CardCode]

Inner Join rdr1 t3 on T0.docentry = T3.docentry

Left Join (Select T0.docentry,sum(T1.[GrossBuyPr] * T1.[OpenQty]) 'Open Gp' from Ordr t0 inner join rdr1 t1

on t0.docentry = t1.docentry and t1.linestatus = 'O'

group by

T0.docentry) A on A.DocEntry = T0.DocEntry

WHERE T0.[DocDate] between [%0] and [%1]

and T0.CANCELED = 'N' and T3.[TrgetEntry] is null

Order By

T1.Slpname,T0.DocDate

Thanks

[narayanis]

Hi,

Targettype field in RDR1 table will tell you whether the document was copied to the next level.

If the TargetType is 15 then SO is copied to Delivery.

If the TargetType is 13, then SO is copied to Invoice.

If the TargetType is -1, then SO is not copied.

By using the combination of TargetType and LineStatus you can derive the information

Regards

Narayani