Skip to Content
0
Former Member
Nov 04, 2011 at 07:41 AM

QUERY OINV

28 Views

Select T0.CardCode, T0.CardName, t1.ItemCode, T1.Dscription, Sum(T1.Quantity) 'Group Quantity', Sum(T1.LineTotal) AS 'BASE AMOUNT', Sum(T1.VatSum) 'TotalTax', Sum(T0.DocTotal) 'Total',

(isnull((SELECT SUM((case when upper(t4.STAType)=-90 then T4.TaxSum else 0 end))

FROM INV4 T4 Inner Join INV1 T5 On T4.DocEntry=T5.DocEntry WHERE T4.ObjectType=T0.ObjType and T5.Itemcode=T1.Itemcode),0)) 'BED',

(isnull((SELECT SUM((case when upper(t4.STAType)=-60 then T4.TaxSum else 0 end))

FROM INV4 T4 Inner Join INV1 T5 On T4.DocEntry=T5.DocEntry WHERE T4.ObjectType=T0.ObjType and T5.Itemcode=T1.Itemcode),0)) 'Cess',

(isnull((SELECT SUM((case when upper(t4.STAType)=-55 then T4.TaxSum else 0 end))

FROM INV4 T4 Inner Join INV1 T5 On T4.DocEntry=T5.DocEntry WHERE T4.ObjectType=T0.ObjType and T5.Itemcode=T1.Itemcode),0)) 'HSCESS',

(isnull((SELECT SUM((case when upper(t4.STAType)=4 then T4.TaxSum else 0 end))

FROM INV4 T4 Inner Join INV1 T5 On T4.DocEntry=T5.DocEntry WHERE T4.ObjectType=T0.ObjType and T5.Itemcode=T1.Itemcode),0)) 'CST',

(isnull((SELECT SUM((case when upper(t4.STAType)=1 then T4.TaxSum else 0 end))

FROM INV4 T4 Inner Join INV1 T5 On T4.DocEntry=T5.DocEntry WHERE T4.ObjectType=T0.ObjType and T5.Itemcode=T1.Itemcode),0)) 'VAT'From OINV T0 Inner Join INV1 T1 On T0.DocEntry=T1.DocEntry

Where (T0.DocDate>='[%0]' and T0.DocDate<='[%1]')

Group By t1.ItemCode, T1.Dscription, T0.ObjType, T0.CardCode, T0.CardName

Above this query BED, HscESS, CESS, VAT, CST all total amounts wrongly displayed,and amount value is wrong , how do i change pls give any modification and pls soon reply