Skip to Content
0
Former Member
Dec 22, 2010 at 10:08 AM

Credit Memo amount not deducted

54 Views

Hi All,

In This query credit memo amount is not reducing.

SELECT distinct T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T0.[NumAtCard], t4.[transcat],T0.[DocTotal] as 'Total Amount',

(select avg(taxrate) from pch4 where statype=4 and docentry =t0.docentry) as 'Cst Rate',

(select Sum(taxsum) from pch4 where statype=4 and docentry =t0.docentry) as 'Cst Amt',

(select avg(taxrate) from pch4 where statype=1 and docentry =t0.docentry) as 'Vat Rate',

(select Sum(taxsum) from pch4 where statype=1 and docentry =t0.docentry) as 'Vat Amt',

(select Sum(linetotal) from pch3 where docentry =t0.docentry) as 'Freight',

isnull((case when t2.Linetotal > '0' then (t0.doctotal - isnull((select Sum(taxsum) from pch4 where statype=1 and docentry =t0.docentry),0) - isnull((select Sum(taxsum) from pch4 where statype=4 and docentry =t0.docentry),0) - Isnull((select Sum(linetotal) from pch3 where docentry =t0.docentry),0))

else (t0.doctotal - (select Sum(taxsum) from pch4 where statype=4 and docentry =t0.docentry)) end ),0) as 'base Amount'

FROM [dbo].[OPCH] T0

INNER JOIN [dbo].[PCH1] T1 ON T0.DocEntry = T1.DocEntry

left outer JOIN PCH3 T2 ON T0.DocEntry = T2.DocEntry

left outer JOIN PCH4 T3 ON T0.DocEntry = T3.DocEntry

left outer JOIN PCH12 T4 ON T0.DocEntry = T4.DocEntry

WHERE t0.doctype = 'I' and T0.[CardCode] ='s00287' AND T0.[DocDate] >='20100401' AND T0.[DocDate] <='20101222'

Union all

SELECT distinct RP.[DocNum], RP.[DocDate], RP.[CardCode], RP.[CardName], RP.[NumAtCard], R5.[transcat],-(RP.[DocTotal]) as 'Total Amount',

-(select avg(taxrate) from RPC4 where statype=4 and docentry =RP.docentry) as 'Cst Rate',

-(select Sum(taxsum) from RPC4 where statype=4 and docentry =RP.docentry) as 'Cst Amt',

-(select avg(taxrate) from RPC4 where statype=1 and docentry =RP.docentry) as 'Vat Rate',

-(select Sum(taxsum) from RPC4 where statype=1 and docentry =RP.docentry) as 'Vat Amt',

-(select Sum(linetotal) from RPC3 where docentry =RP.docentry) as 'Freight',

-isnull((case when R3.Linetotal > '0' then (RP.doctotal - isnull((select Sum(taxsum) from RPC4 where statype=1 and docentry =RP.docentry),0) - isnull((select Sum(taxsum) from RPC4 where statype=4 and docentry =RP.docentry),0) - Isnull((select Sum(linetotal) from RPC3 where docentry =RP.docentry),0))

else (RP.doctotal - (select Sum(taxsum) from RPC4 where statype=4 and docentry =RP.docentry)) end ),0) as 'base Amount'

FROM [dbo].[ORPC] RP

inner join RPC1 R on R.docentry = RP.Docentry

left outer join RPC12 R5 on RP.docentry = R5.docentry

left outer join RPC4 R4 on RP.docentry = R4.docentry

left outer join RPC3 R3 on R.docentry = R4.docentry

WHERE RP.doctype = 'I' and RP.[CardCode] ='s00287' AND RP.[DocDate] >='20100401' AND RP.[DocDate] <='20101222'

please help.

Thanks and Regards

Piyush