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